/**
* api to generate component transaction lines in M_WorkOrderTransactionLine. Skips optional operations unless they are either part of starting operation sequence number or ending operation sequence number
* uses Default Locator/ highest priority Locator of Warehouse of the WorkOrder
* @param ctx
* @param M_WorkOrderTransaction_ID Work Order Transaction header
* @param Qty Number of Work Order Product Assemblies for which to generate components
* @param OperationFrom Starting operation sequence number
* @param OperationTo Ending operation sequence number
* @param SupplyType Component supply type. Valid values are P (Push), O (Operation Pull), A (Assembly Pull)
* @param trx
* @return MVAMFGMWrkOdrTrnsctionLine
*/
public ViennaAdvantage.Model.MVAMFGMWrkOdrTrnsctionLine[] GenerateComponentTxnLine(Ctx ctx, int M_WorkOrderTransaction_ID,
Decimal?Qty, Decimal?OperationFrom, Decimal?OperationTo, String SupplyType, Trx trx)
{
ViennaAdvantage.Model.MVAMFGMWrkOdrTransaction wot = new ViennaAdvantage.Model.MVAMFGMWrkOdrTransaction(ctx, M_WorkOrderTransaction_ID, trx);
if (!((wot.GetDocStatus().Equals(X_VAMFG_M_WrkOdrTransaction.DOCSTATUS_Drafted) || wot.GetDocStatus().Equals(X_VAMFG_M_WrkOdrTransaction.DOCSTATUS_InProgress)) &&
(wot.GetVAMFG_WorkOrderTxnType().Equals(X_VAMFG_M_WrkOdrTransaction.VAMFG_WORKORDERTXNTYPE_1_ComponentIssueToWorkOrder) ||
wot.GetVAMFG_WorkOrderTxnType().Equals(X_VAMFG_M_WrkOdrTransaction.VAMFG_WORKORDERTXNTYPE_ComponentReturnFromWorkOrder))))
{
log.Severe("Invalid Work Order Txn DocStatus.");
return(null);
}
int locatorID = wot.GetM_Locator_ID();
if (0 == locatorID)
{
ViennaAdvantage.Model.MVAMFGMWorkOrder wo = new ViennaAdvantage.Model.MVAMFGMWorkOrder(ctx, wot.GetVAMFG_M_WorkOrder_ID(), trx);
if (!wo.GetDocStatus().Equals(X_VAMFG_M_WorkOrder.DOCSTATUS_InProgress))
{
log.Severe("Invalid Work Order DocStatus.");
return(null);
}
locatorID = (MWarehouse.Get(ctx, wo.GetM_Warehouse_ID())).GetDefaultM_Locator_ID();
}
return(GenerateComponentTxnLine(ctx, M_WorkOrderTransaction_ID,
Qty, OperationFrom, OperationTo, SupplyType, locatorID, trx));
}
/// <summary>
/// is used to save Product container
/// </summary>
/// <param name="warehouseId">Warehouse where we create container</param>
/// <param name="locatorId">Locator - in which locator we place container</param>
/// <param name="value">Search key of the container</param>
/// <param name="name">name of teh container</param>
/// <param name="height">height of the container</param>
/// <param name="width">width of the container</param>
/// <param name="parentContainerId">Parent of the nw container</param>
/// <returns>Save Or Not Saved message</returns>
/// <writer>Amit Bansal</writer>
public string SaveProductContainer(int warehouseId, int locatorId, string value, string name, Decimal height, Decimal width, int parentContainerId)
{
MLocator m_locator = null;
MWarehouse m_warehouse = null;
// when warehouse ID is ZERO, then extract it from Locator
if (warehouseId == 0 && locatorId > 0)
{
m_locator = MLocator.Get(_ctx, locatorId);
warehouseId = m_locator.GetM_Warehouse_ID();
}
// when locator ID is ZERO, then extract it either from Parent Conatiner or from Warehouse
else if (warehouseId > 0 && locatorId == 0)
{
if (parentContainerId == 0)
{
m_warehouse = MWarehouse.Get(_ctx, warehouseId);
locatorId = m_warehouse.GetDefaultM_Locator_ID();
}
else
{
locatorId = Util.GetValueOfInt(DB.ExecuteScalar("SELECT M_Locator_ID FROM M_ProductContainer WHERE M_ProductContainer_ID = " + parentContainerId, null, null));
}
}
// need to check warehouse and locator shoyld be active during ceation of Product Container
m_warehouse = MWarehouse.Get(_ctx, warehouseId);
m_locator = MLocator.Get(_ctx, locatorId);
if (!m_warehouse.IsActive())
{
return(Msg.GetMsg(_ctx, "VIS_WarehouseNotActive"));
}
else if (!m_locator.IsActive())
{
return(Msg.GetMsg(_ctx, "VIS_LocatorNotActive"));
}
// Create Product Container in Locator Organization
//MProductContainer container = new MProductContainer(_ctx, 0, null);
//container.SetAD_Org_ID(m_locator.GetAD_Org_ID());
//container.SetValue(value);
//container.SetName(name);
//container.SetM_Warehouse_ID(warehouseId);
//container.SetM_Locator_ID(locatorId);
//container.SetHeight(height);
//container.SetWidth(width);
//container.SetRef_M_Container_ID(parentContainerId);
//if (!container.Save())
//{
// ValueNamePair pp = VLogger.RetrieveError();
// return Msg.GetMsg(_ctx, "VIS_ContainernotSaved") + " " + (pp != null ? pp.GetName() : "");
//}
//else
//{
// return "";
//}
return("");
}
} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>Message </returns>
protected override String DoIt()
{
log.Info("M_Warehouse_ID=" + _M_Warehouse_ID
+ ", C_BPartner_ID=" + _C_BPartner_ID
+ " - ReplenishmentCreate=" + _ReplenishmentCreate
+ ", C_DocType_ID=" + _C_DocType_ID);
if (_ReplenishmentCreate != null && _C_DocType_ID == 0 || _C_DocType_ID == -1)
{
throw new Exception("@FillMandatory@ @C_DocType_ID@");
}
MWarehouse wh = MWarehouse.Get(GetCtx(), _M_Warehouse_ID);
if (wh.Get_ID() == 0)
{
throw new Exception("@FillMandatory@ @M_Warehouse_ID@");
}
if (wh.GetM_WarehouseSource_ID() > 0)
{
_M_WareSource = "M_WarehouseSource_ID = " + Util.GetValueOfString(wh.GetM_WarehouseSource_ID());
}
else
{
_M_WareSource = null;
}
//
PrepareTable();
FillTable(wh);
//
if (_ReplenishmentCreate == null)
{
return("OK");
}
//
MDocType dt = MDocType.Get(GetCtx(), _C_DocType_ID);
if (!dt.GetDocBaseType().Equals(_ReplenishmentCreate))
{
throw new Exception("@C_DocType_ID@=" + dt.GetName() + " <> " + _ReplenishmentCreate);
}
//
if (_ReplenishmentCreate.Equals("POO"))
{
CreatePO();
}
else if (_ReplenishmentCreate.Equals("POR"))
{
CreateRequisition();
}
else if (_ReplenishmentCreate.Equals("MMM"))
{
CreateMovements();
}
return(_info);
} // doIt
/// <summary>
/// Create/Add to Inventory Line Query
/// </summary>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">asi</param>
/// <param name="qtyOnHand">quantity</param>
/// <param name="M_AttributeSet_ID">attribute set</param>
/// <returns>lines added</returns>
private string InsertInventoryLine(int M_Locator_ID, int M_Product_ID, int lineNo,
int M_AttributeSetInstance_ID, Decimal qtyOnHand, int M_AttributeSet_ID)
{
MInventoryLine line = new MInventoryLine(GetCtx(), 0, Get_Trx());
int line_ID = DB.GetNextID(GetCtx(), "M_InventoryLine", Get_Trx());
string qry = "select m_warehouse_id from m_locator where m_locator_id=" + M_Locator_ID;
int M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_Trx()));
MWarehouse wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID);
if (wh.IsDisallowNegativeInv() == true)
{
if (qtyOnHand < 0)
{
return("");
}
}
MProduct product = MProduct.Get(GetCtx(), M_Product_ID);
if (product != null)
{
int precision = product.GetUOMPrecision();
if (Env.Signum(qtyOnHand) != 0)
{
qtyOnHand = Decimal.Round(qtyOnHand, precision, MidpointRounding.AwayFromZero);
}
}
string sql = @"INSERT INTO M_InventoryLine (AD_Client_ID, AD_Org_ID,IsActive, Created, CreatedBy, Updated, UpdatedBy, Line, M_Inventory_ID, M_InventoryLine_ID,
M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, QtyBook, QtyCount, OpeningStock, AsOnDateCount, DifferenceQty, AdjustmentType";
if (line.Get_ColumnIndex("C_UOM_ID") > 0)
{
sql += ", QtyEntered, C_UOM_ID";
}
if (line.Get_ColumnIndex("IsFromProcess") > 0)
{
sql += ",IsFromProcess";
}
sql += " ) VALUES ( " + _inventory.GetAD_Client_ID() + "," + _inventory.GetAD_Org_ID() + ",'Y'," + GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," +
GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," + lineNo + "," + _m_Inventory_ID + "," + line_ID + "," + M_Locator_ID + "," + M_Product_ID + "," +
M_AttributeSetInstance_ID + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + 0 + ",'A'";
if (line.Get_ColumnIndex("C_UOM_ID") > 0)
{
sql += "," + qtyOnHand + "," + product.GetC_UOM_ID();
}
if (line.Get_ColumnIndex("IsFromProcess") > 0)
{
sql += ",'Y'";
}
string insertQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + ")'); exception when others then null; END;";
return(insertQry);
}
/// <summary>
/// Get Default Locator ID
/// </summary>
/// <param name="fields">Warehouse ID</param>
/// <returns>Default Locator ID</returns>
/// <writer>Amit</writer>
public JsonResult GetDefaultLocatorID(string fields)
{
string retJSON = "";
if (Session["ctx"] != null)
{
VAdvantage.Utility.Ctx ctx = Session["ctx"] as Ctx;
retJSON = JsonConvert.SerializeObject(MWarehouse.Get(ctx, Util.GetValueOfInt(fields)).GetDefaultM_Locator_ID());
}
return(Json(retJSON, JsonRequestBehavior.AllowGet));
}
} // createPO
/// <summary>
/// Create Requisition
/// </summary>
private void CreateRequisition()
{
int noReqs = 0;
String info = "";
//
MRequisition requisition = null;
MWarehouse wh = null;
X_T_Replenish[] replenishs = GetReplenish(_M_WareSource);
for (int i = 0; i < replenishs.Length; i++)
{
X_T_Replenish replenish = replenishs[i];
if (wh == null || wh.GetM_Warehouse_ID() != replenish.GetM_Warehouse_ID())
{
wh = MWarehouse.Get(GetCtx(), replenish.GetM_Warehouse_ID());
}
//
if (requisition == null ||
requisition.GetM_Warehouse_ID() != replenish.GetM_Warehouse_ID())
{
requisition = new MRequisition(GetCtx(), 0, Get_TrxName());
requisition.SetAD_User_ID(GetAD_User_ID());
requisition.SetC_DocType_ID(_C_DocType_ID);
requisition.SetDescription(Msg.GetMsg(GetCtx(), "Replenishment"));
// Set Org/WH
int _CountDTD001 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='DTD001_'"));
if (_CountDTD001 > 0)
{
requisition.SetDTD001_MWarehouseSource_ID(wh.GetM_WarehouseSource_ID());
}
requisition.SetAD_Org_ID(wh.GetAD_Org_ID());
requisition.SetM_Warehouse_ID(wh.GetM_Warehouse_ID());
if (!requisition.Save())
{
return;
}
_DocNo = requisition.GetDocumentNo() + ","; //dtd
log.Fine(requisition.ToString());
noReqs++;
info += " - " + requisition.GetDocumentNo();
}
//
MRequisitionLine line = new MRequisitionLine(requisition);
line.SetM_Product_ID(replenish.GetM_Product_ID());
line.SetC_BPartner_ID(replenish.GetC_BPartner_ID());
line.SetQty(replenish.GetQtyToOrder());
line.SetPrice();
line.Save();
}
_DocNo = _DocNo.Substring(0, _DocNo.Length - 1);//dtd
_info = "#" + noReqs + info;
log.Info(_info);
} // createRequisition
/**
* Creates a Work Order Transaction header for a given Work Order assumes locator is same locator as on Work Order
* @param ctx
* @param VAMFG_M_WorkOrder_ID Work Order
* @param TxnType valid values are CI (Component Issue), CR (Component Return), RU (Resource Usage)
* @param trx
* @return MVAMFGMWrkOdrTransaction on success, null otherwise
*/
public ViennaAdvantage.Model.MVAMFGMWrkOdrTransaction CreateWOTxn(Ctx ctx, int VAMFG_M_WorkOrder_ID, String TxnType, Trx trx)
{
ViennaAdvantage.Model.MVAMFGMWorkOrder wo = new ViennaAdvantage.Model.MVAMFGMWorkOrder(ctx, VAMFG_M_WorkOrder_ID, trx);
if (wo == null || !wo.GetDocStatus().Equals(ViennaAdvantage.Model.MVAMFGMWorkOrder.DOCSTATUS_InProgress))
{
log.Severe("Work Order number not valid for transactions.");
return(null);
}
log.Info("Getting Default Locator of Work Order Warehouse.");
MLocator loc = MWarehouse.Get(ctx, wo.GetM_Warehouse_ID()).GetDefaultLocator();
return(createWOTxn(ctx, VAMFG_M_WorkOrder_ID, TxnType, 0, loc.GetM_Locator_ID(), Decimal.Zero, trx));
}
/// <summary>
/// Create PO's
/// </summary>
private void CreatePO()
{
int noOrders = 0;
String info = "";
//
MOrder order = null;
MWarehouse wh = null;
X_T_Replenish[] replenishs = GetReplenish(_M_WareSource);
for (int i = 0; i < replenishs.Length; i++)
{
X_T_Replenish replenish = replenishs[i];
if (wh == null || wh.GetM_Warehouse_ID() != replenish.GetM_Warehouse_ID())
{
wh = MWarehouse.Get(GetCtx(), replenish.GetM_Warehouse_ID());
}
//
if (order == null ||
order.GetC_BPartner_ID() != replenish.GetC_BPartner_ID() ||
order.GetM_Warehouse_ID() != replenish.GetM_Warehouse_ID())
{
order = new MOrder(GetCtx(), 0, Get_TrxName());
order.SetIsSOTrx(false);
order.SetC_DocTypeTarget_ID(_C_DocType_ID);
MBPartner bp = new MBPartner(GetCtx(), replenish.GetC_BPartner_ID(), Get_TrxName());
order.SetBPartner(bp);
order.SetSalesRep_ID(GetAD_User_ID());
order.SetDescription(Msg.GetMsg(GetCtx(), "Replenishment"));
// Set Org/WH
order.SetAD_Org_ID(wh.GetAD_Org_ID());
order.SetM_Warehouse_ID(wh.GetM_Warehouse_ID());
if (!order.Save())
{
return;
}
log.Fine(order.ToString());
noOrders++;
info += " - " + order.GetDocumentNo();
}
MOrderLine line = new MOrderLine(order);
line.SetM_Product_ID(replenish.GetM_Product_ID());
line.SetQty(replenish.GetQtyToOrder());
line.SetPrice();
line.Save();
}
_info = "#" + noOrders + info;
log.Info(_info);
} // createPO
/// <summary>
/// GetWarehouse
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Dictionary <string, string> GetWarehouse(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int M_Warehouse_ID;
//Assign parameter value
M_Warehouse_ID = Util.GetValueOfInt(paramValue[0].ToString());
//Assign parameter value
MWarehouse wh = MWarehouse.Get(ctx, M_Warehouse_ID);
Dictionary <string, string> result = new Dictionary <string, string>();
result["M_Locator_ID"] = wh.GetDefaultM_Locator_ID().ToString();
return(result);
}
/// <summary>
/// Check Qty Availablity in warehouse when Disallow Negative Inventory = true
/// </summary>
/// <param name="ctx">context</param>
/// <param name="M_Warehouse_ID">warehouse refernce - for checking disallow negative inventory or not</param>
/// <param name="M_Locator_ID">locator ref - in which locator we have to check stock</param>
/// <param name="M_Product_ID">product ref -- to which product, need to check stock</param>
/// <param name="M_AttributeSetInstance_ID">AttributeSetInstance -- to which ASI, need to check stock</param>
/// <param name="MovementQty">qty to be impacted</param>
/// <param name="trxName">system transaction</param>
/// <returns>TRUE/False</returns>
private bool CheckQtyAvailablity(Ctx ctx, int M_Warehouse_ID, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, Decimal?MovementQty, Trx trxName)
{
MWarehouse wh = MWarehouse.Get(ctx, M_Warehouse_ID);
MProduct product = null;
if (wh.IsDisallowNegativeInv() && M_Product_ID > 0)
{
product = MProduct.Get(ctx, M_Product_ID);
string qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)),0) AS QtyOnHand FROM M_Storage where m_locator_id=" + M_Locator_ID + @" and m_product_id=" + M_Product_ID;
qry += " AND NVL(M_AttributeSetInstance_ID, 0) =" + M_AttributeSetInstance_ID;
Decimal?OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry, null, trxName));
if (OnHandQty + MovementQty < 0)
{
log.SaveError("Info", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
return(false);
}
}
return(true);
}
/// <summary>
/// Create/Add to Inventory Line Query
/// </summary>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">asi</param>
/// <param name="currentQty">quantity</param>
/// <param name="M_AttributeSet_ID">attribute set</param>
/// <returns>lines added</returns>
private string UpdateInventoryLine(int M_InventoryLine_ID, int M_Product_ID, int M_Locator_ID, Decimal currentQty, string AdjustType, Decimal AsOnDateCount, Decimal DiffQty)
{
string qry = "select m_warehouse_id from m_locator where m_locator_id=" + M_Locator_ID;
int M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_Trx()));
MWarehouse wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID);
if (wh.IsDisallowNegativeInv() == true)
{
if (currentQty < 0)
{
return("");
}
}
MProduct product = MProduct.Get(GetCtx(), M_Product_ID);
if (product != null)
{
int precision = product.GetUOMPrecision();
if (Env.Signum(currentQty) != 0)
{
currentQty = Decimal.Round(currentQty, precision, MidpointRounding.AwayFromZero);
}
}
if (AdjustType == "A")
{
DiffQty = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, AsOnDateCount));
}
else if (AdjustType == "D")
{
AsOnDateCount = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, DiffQty));
}
Decimal QtyCount = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, DiffQty));
// Changes by Bharat on 02 Aug 2017 as issue given by Ravikant
string sql = @"UPDATE M_InventoryLine SET QtyBook = " + currentQty + ",QtyCount = " + QtyCount + ",OpeningStock = " + currentQty + ",AsOnDateCount = " + AsOnDateCount +
",DifferenceQty = " + DiffQty + " WHERE M_InventoryLine_ID = " + M_InventoryLine_ID;
string updateQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + "'); exception when others then null; END;";
return(updateQry);
}
/// <summary>
/// Perform Process.
/// - Fill Table with QtyOnHand for Warehouse and Valuation Date
/// - Perform Price Calculations
/// </summary>
/// <returns>message</returns>
protected override String DoIt()
{
log.Info("M_Warehouse_ID=" + _M_Warehouse_ID
+ ",C_Currency_ID=" + _C_Currency_ID
+ ",DateValue=" + _DateValue
+ ",M_PriceList_Version_ID=" + _M_PriceList_Version_ID
+ ",M_CostElement_ID=" + _M_CostElement_ID);
MWarehouse wh = MWarehouse.Get(GetCtx(), _M_Warehouse_ID);
MClient c = MClient.Get(GetCtx(), wh.GetAD_Client_ID());
MAcctSchema mas = c.GetAcctSchema();
// Delete (just to be sure)
StringBuilder sql = new StringBuilder("DELETE FROM T_InventoryValue WHERE AD_PInstance_ID=");
sql.Append(GetAD_PInstance_ID());
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
MPInstance instance = new MPInstance(GetCtx(), GetAD_PInstance_ID(), null);
DateTime Createddate = instance.GetCreated();
Createddate = Createddate.AddHours(-1);
string qry = "select MAX(AD_PINSTANCE_ID) from AD_PINSTANCE WHERE AD_Process_ID=" + instance.GetAD_Process_ID() + " AND created< TO_Date('" + Createddate.ToString("MM/dd/yyyy HH:mm:ss") + "', 'MM-DD-YYYY HH24:MI:SS')";
int MaxInstance_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, null));
int no1 = DB.ExecuteQuery("DELETE FROM T_InventoryValue WHERE AD_PInstance_ID <=" + MaxInstance_ID);
// Insert Standard Costs
sql = new StringBuilder("INSERT INTO T_InventoryValue "
+ "(AD_PInstance_ID, M_Warehouse_ID, M_Product_ID, M_AttributeSetInstance_ID,"
+ " AD_Client_ID, AD_Org_ID, CostStandard) "
+ "SELECT ").Append(GetAD_PInstance_ID())
.Append(", w.M_Warehouse_ID, c.M_Product_ID, c.M_AttributeSetInstance_ID,"
+ " w.AD_Client_ID, w.AD_Org_ID, c.CurrentCostPrice "
+ "FROM M_Warehouse w"
+ " INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)"
+ " INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)"
+ " INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID))"
+ " INNER JOIN M_CostElement ce ON (c.M_CostElement_ID=ce.M_CostElement_ID AND ce.CostingMethod='S' AND ce.CostElementType='M') "
+ "WHERE w.M_Warehouse_ID=").Append(_M_Warehouse_ID);
int noInsertStd = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Inserted Std=" + noInsertStd);
if (noInsertStd == 0)
{
return("No Standard Costs found");
}
// Insert addl Costs
int noInsertCost = 0;
if (_M_CostElement_ID != 0)
{
sql = new StringBuilder("INSERT INTO T_InventoryValue "
+ "(AD_PInstance_ID, M_Warehouse_ID, M_Product_ID, M_AttributeSetInstance_ID,"
+ " AD_Client_ID, AD_Org_ID, CostStandard, Cost, M_CostElement_ID) "
+ "SELECT ").Append(GetAD_PInstance_ID())
.Append(", w.M_Warehouse_ID, c.M_Product_ID, c.M_AttributeSetInstance_ID,"
+ " w.AD_Client_ID, w.AD_Org_ID, 0, c.CurrentCostPrice, c.M_CostElement_ID "
+ "FROM M_Warehouse w"
+ " INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)"
+ " INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)"
+ " INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID)) "
+ "WHERE w.M_Warehouse_ID=").Append(_M_Warehouse_ID)
.Append(" AND c.M_CostElement_ID=").Append(_M_CostElement_ID)
.Append(" AND NOT EXISTS (SELECT * FROM T_InventoryValue iv "
+ "WHERE iv.AD_PInstance_ID=").Append(GetAD_PInstance_ID())
.Append(" AND iv.M_Warehouse_ID=w.M_Warehouse_ID"
+ " AND iv.M_Product_ID=c.M_Product_ID"
+ " AND iv.M_AttributeSetInstance_ID=c.M_AttributeSetInstance_ID)");
noInsertCost = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Inserted Cost=" + noInsertCost);
// Update Std Cost Records
sql = new StringBuilder("UPDATE T_InventoryValue iv "
+ "SET (Cost, M_CostElement_ID)="
+ "(SELECT c.CurrentCostPrice, c.M_CostElement_ID "
+ "FROM M_Warehouse w"
+ " INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)"
+ " INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)"
+ " INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID"
+ " AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID)) "
+ "WHERE c.M_CostElement_ID=" + _M_CostElement_ID
+ " AND w.M_Warehouse_ID=iv.M_Warehouse_ID"
+ " AND c.M_Product_ID=iv.M_Product_ID"
+ " AND c.M_AttributeSetInstance_ID=iv.M_AttributeSetInstance_ID AND rownum=1 AND w.m_warehouse_ID=" + _M_Warehouse_ID + ") "
+ "WHERE EXISTS (SELECT * FROM T_InventoryValue ivv "
+ "WHERE ivv.AD_PInstance_ID=" + GetAD_PInstance_ID()
+ " AND ivv.M_CostElement_ID IS NULL) AND iv.AD_PInstance_ID =" + GetAD_PInstance_ID());
int noUpdatedCost = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Updated Cost=" + noUpdatedCost);
}
if ((noInsertStd + noInsertCost) == 0)
{
return("No Costs found");
}
// Update Constants
// YYYY-MM-DD HH24:MI:SS.mmmm JDBC Timestamp format
// String myDate = _DateValue.ToString();
sql = new StringBuilder("UPDATE T_InventoryValue SET ")
//.Append("DateValue=To_Date('").Append(myDate.Substring(0,10))
//.Append("23:59:59','MM-DD-YYYY HH24:MI:SS'),")
.Append("DateValue=").Append(GlobalVariable.TO_DATE(_DateValue, true)).Append(",")
.Append("M_PriceList_Version_ID=").Append(_M_PriceList_Version_ID).Append(",")
.Append("C_Currency_ID=").Append(_C_Currency_ID);
if (_M_CostElement_ID != 0)
{
sql.Append(",").Append("M_CostElement_ID=").Append(_M_CostElement_ID);
}
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Constants=" + no);
// Get current QtyOnHand with ASI
sql = new StringBuilder("UPDATE T_InventoryValue iv SET QtyOnHand = "
+ "(SELECT SUM(QtyOnHand) FROM M_Storage s"
+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID) "
+ "WHERE iv.M_Product_ID=s.M_Product_ID"
+ " AND iv.M_Warehouse_ID=l.M_Warehouse_ID"
+ " AND iv.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID) "
+ "WHERE AD_PInstance_ID=").Append(GetAD_PInstance_ID())
.Append(" AND iv.M_AttributeSetInstance_ID<>0");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("QtHand with ASI=" + no);
// Get current QtyOnHand without ASI
sql = new StringBuilder("UPDATE T_InventoryValue iv SET QtyOnHand = "
+ "(SELECT SUM(QtyOnHand) FROM M_Storage s"
+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID) "
+ "WHERE iv.M_Product_ID=s.M_Product_ID"
+ " AND iv.M_Warehouse_ID=l.M_Warehouse_ID) "
+ "WHERE AD_PInstance_ID=").Append(GetAD_PInstance_ID())
.Append(" AND iv.M_AttributeSetInstance_ID=0");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("QtHand w/o ASI=" + no);
// Adjust for Valuation Date
sql = new StringBuilder("UPDATE T_InventoryValue iv "
+ "SET QtyOnHand="
+ "(SELECT iv.QtyOnHand - NVL(SUM(t.MovementQty), 0) "
+ "FROM M_Transaction t"
+ " INNER JOIN M_Locator l ON (t.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE t.M_Product_ID=iv.M_Product_ID"
+ " AND t.M_AttributeSetInstance_ID=iv.M_AttributeSetInstance_ID"
+ " AND t.MovementDate > iv.DateValue"
+ " AND l.M_Warehouse_ID=iv.M_Warehouse_ID) "
+ "WHERE iv.M_AttributeSetInstance_ID<>0");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Update with ASI=" + no);
//
sql = new StringBuilder("UPDATE T_InventoryValue iv "
+ "SET QtyOnHand="
+ "(SELECT iv.QtyOnHand - NVL(SUM(t.MovementQty), 0) "
+ "FROM M_Transaction t"
+ " INNER JOIN M_Locator l ON (t.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE t.M_Product_ID=iv.M_Product_ID"
+ " AND t.MovementDate > iv.DateValue"
+ " AND l.M_Warehouse_ID=iv.M_Warehouse_ID) "
+ "WHERE iv.M_AttributeSetInstance_ID=0");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Update w/o ASI=" + no);
// Delete Records w/o OnHand Qty
sql = new StringBuilder("DELETE FROM T_InventoryValue "
+ "WHERE (QtyOnHand=0 OR QtyOnHand IS NULL) AND AD_PInstance_ID=").Append(GetAD_PInstance_ID());
int noQty = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("NoQty Deleted=" + noQty);
// Update Prices
no = DataBase.DB.ExecuteQuery("UPDATE T_InventoryValue iv "
+ "SET PricePO = "
+ "(SELECT MAX(currencyConvert (po.PriceList,po.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, po.AD_Client_ID,po.AD_Org_ID))"
+ " FROM M_Product_PO po WHERE po.M_Product_ID=iv.M_Product_ID"
+ " AND po.IsCurrentVendor='Y'), "
+ "PriceList = "
+ "(SELECT currencyConvert(pp.PriceList,pl.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pl.AD_Client_ID,pl.AD_Org_ID)"
+ " FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp"
+ " WHERE pp.M_Product_ID=iv.M_Product_ID AND pp.M_PriceList_Version_ID=iv.M_PriceList_Version_ID"
+ " AND pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID"
+ " AND plv.M_PriceList_ID=pl.M_PriceList_ID), "
+ "PriceStd = "
+ "(SELECT currencyConvert(pp.PriceStd,pl.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pl.AD_Client_ID,pl.AD_Org_ID)"
+ " FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp"
+ " WHERE pp.M_Product_ID=iv.M_Product_ID AND pp.M_PriceList_Version_ID=iv.M_PriceList_Version_ID"
+ " AND pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID"
+ " AND plv.M_PriceList_ID=pl.M_PriceList_ID), "
+ "PriceLimit = "
+ "(SELECT currencyConvert(pp.PriceLimit,pl.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, pl.AD_Client_ID,pl.AD_Org_ID)"
+ " FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp"
+ " WHERE pp.M_Product_ID=iv.M_Product_ID AND pp.M_PriceList_Version_ID=iv.M_PriceList_Version_ID"
+ " AND pp.M_PriceList_Version_ID=plv.M_PriceList_Version_ID"
+ " AND plv.M_PriceList_ID=pl.M_PriceList_ID)"
, null, Get_TrxName());
String msg = "";
if (no == 0)
{
msg = "No Prices";
}
// Convert if different Currency
if (mas.GetC_Currency_ID() != _C_Currency_ID)
{
sql = new StringBuilder("UPDATE T_InventoryValue iv "
+ "SET CostStandard= "
+ "(SELECT currencyConvert(iv.CostStandard,acs.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, iv.AD_Client_ID,iv.AD_Org_ID) "
+ "FROM C_AcctSchema acs WHERE acs.C_AcctSchema_ID=" + mas.GetC_AcctSchema_ID() + "),"
+ " Cost= "
+ "(SELECT currencyConvert(iv.Cost,acs.C_Currency_ID,iv.C_Currency_ID,iv.DateValue,null, iv.AD_Client_ID,iv.AD_Org_ID) "
+ "FROM C_AcctSchema acs WHERE acs.C_AcctSchema_ID=" + mas.GetC_AcctSchema_ID() + ") "
+ "WHERE AD_PInstance_ID=" + GetAD_PInstance_ID());
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Convered=" + no);
}
// Update Values
no = DataBase.DB.ExecuteQuery("UPDATE T_InventoryValue SET "
+ "PricePOAmt = QtyOnHand * PricePO, "
+ "PriceListAmt = QtyOnHand * PriceList, "
+ "PriceStdAmt = QtyOnHand * PriceStd, "
+ "PriceLimitAmt = QtyOnHand * PriceLimit, "
+ "CostStandardAmt = QtyOnHand * CostStandard, "
+ "CostAmt = QtyOnHand * Cost "
+ "WHERE AD_PInstance_ID=" + GetAD_PInstance_ID(), null, Get_TrxName());
log.Fine("Calculation=" + no);
//
return(msg);
}
/**
* Create Difference Document
* @param inout shipment/receipt
* @param confirm confirm line
* @return true if created
*/
private bool CreateDifferenceDoc(MInOut inout, MInOutLineConfirm confirm)
{
if (_processMsg == null)
{
_processMsg = "";
}
else if (_processMsg.Length > 0)
{
_processMsg += "; ";
}
// Credit Memo if linked Document
if (Env.Signum(confirm.GetDifferenceQty()) != 0 &&
!inout.IsSOTrx() && !inout.IsReturnTrx() && inout.GetRef_InOut_ID() != 0)
{
log.Info("Difference=" + confirm.GetDifferenceQty());
if (_creditMemo == null)
{
_creditMemo = new MInvoice(inout, null);
_creditMemo.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
_creditMemo.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APCREDITMEMO);
if (!_creditMemo.Save(Get_TrxName()))
{
_processMsg += "Credit Memo not created";
return(false);
}
SetC_Invoice_ID(_creditMemo.GetC_Invoice_ID());
}
MInvoiceLine line = new MInvoiceLine(_creditMemo);
line.SetShipLine(confirm.GetLine());
line.SetQty(confirm.GetDifferenceQty()); // Entered/Invoiced
if (!line.Save(Get_TrxName()))
{
_processMsg += "Credit Memo Line not created";
return(false);
}
confirm.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
}
// Create Inventory Difference
if (Env.Signum(confirm.GetScrappedQty()) != 0)
{
log.Info("Scrapped=" + confirm.GetScrappedQty());
if (_inventory == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), inout.GetM_Warehouse_ID());
_inventory = new MInventory(wh);
_inventory.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
//vikas new 13 jan 2016 1
_inventory.SetIsInternalUse(true);
if (_inventory.GetC_DocType_ID() == 0)
{
MDocType[] types = MDocType.GetOfDocBaseType(GetCtx(), MDocBaseType.DOCBASETYPE_MATERIALPHYSICALINVENTORY);
if (types.Length > 0)
{
// Get Internal Use Inv Doc Type
for (int i = 0; i < types.Length; i++)
{
int _count = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Count(*) FROM C_DocType WHERE IsActive='Y' AND IsInternalUse='Y' AND C_DocType_ID=" + types[i].GetC_DocType_ID()));
if (_count > 0)
{
_inventory.SetC_DocType_ID(types[i].GetC_DocType_ID());
break;
}
}
}
else
{
log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "@NotFound@ @C_DocType_ID@"));
return(false);
}
}
// 13 jan End
if (!_inventory.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
SetM_Inventory_ID(_inventory.GetM_Inventory_ID());
}
MInOutLine ioLine = confirm.GetLine();
MInventoryLine line = new MInventoryLine(_inventory,
ioLine.GetM_Locator_ID(), ioLine.GetM_Product_ID(),
ioLine.GetM_AttributeSetInstance_ID(),
confirm.GetScrappedQty(), Env.ZERO);
//new 15 jan
line.SetQtyInternalUse(line.GetQtyBook());
line.SetQtyBook(0);
line.SetIsInternalUse(true);
Tuple <String, String, String> mInfo = null;
if (Env.HasModulePrefix("DTD001_", out mInfo))
{
int _charge = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Charge_ID FROM C_Charge WHERE isactive='Y' AND DTD001_ChargeType='INV'"));
line.SetC_Charge_ID(_charge);
}
// End
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
}
//
if (!confirm.Save(Get_TrxName()))
{
_processMsg += "Confirmation Line not saved";
return(false);
}
return(true);
}
} // createRequisition
/// <summary>
/// Create Inventory Movements
/// </summary>
private void CreateMovements()
{
int noMoves = 0;
String info = "";
//
MClient client = null;
MMovement move = null;
int M_Warehouse_ID = 0;
int M_WarehouseSource_ID = 0;
MWarehouse whSource = null;
MWarehouse whTarget = null;
string param = "";
if (_M_WareSource != null)
{
param = _M_WareSource;
}
else
{
param = "M_WarehouseSource_ID IS NOT NULL";
}
X_T_Replenish[] replenishs = GetReplenish(param);;
for (int i = 0; i < replenishs.Length; i++)
{
X_T_Replenish replenish = replenishs[i];
if (whSource == null || whSource.GetM_WarehouseSource_ID() != replenish.GetM_WarehouseSource_ID())
{
whSource = MWarehouse.Get(GetCtx(), replenish.GetM_WarehouseSource_ID());
}
if (whTarget == null || whTarget.GetM_Warehouse_ID() != replenish.GetM_Warehouse_ID())
{
whTarget = MWarehouse.Get(GetCtx(), replenish.GetM_Warehouse_ID());
}
if (client == null || client.GetAD_Client_ID() != whSource.GetAD_Client_ID())
{
client = MClient.Get(GetCtx(), whSource.GetAD_Client_ID());
}
//
if (move == null ||
M_WarehouseSource_ID != replenish.GetM_WarehouseSource_ID() ||
M_Warehouse_ID != replenish.GetM_Warehouse_ID())
{
M_WarehouseSource_ID = replenish.GetM_WarehouseSource_ID();
M_Warehouse_ID = replenish.GetM_Warehouse_ID();
move = new MMovement(GetCtx(), 0, Get_TrxName());
move.SetC_DocType_ID(_C_DocType_ID);
move.SetDescription(Msg.GetMsg(GetCtx(), "Replenishment")
+ ": " + whSource.GetName() + "->" + whTarget.GetName());
// Set Org
move.SetAD_Org_ID(whSource.GetAD_Org_ID());
if (!move.Save())
{
return;
}
log.Fine(move.ToString());
noMoves++;
info += " - " + move.GetDocumentNo();
}
MProduct product = MProduct.Get(GetCtx(), replenish.GetM_Product_ID());
// To
int M_LocatorTo_ID = GetLocator_ID(product, whTarget);
// From: Look-up Storage
MProductCategory pc = MProductCategory.Get(GetCtx(), product.GetM_Product_Category_ID());
String MMPolicy = pc.GetMMPolicy();
if (MMPolicy == null || MMPolicy.Length == 0)
{
MMPolicy = client.GetMMPolicy();
}
//
MStorage[] storages = MStorage.GetWarehouse(GetCtx(),
whSource.GetM_Warehouse_ID(), replenish.GetM_Product_ID(), 0, 0,
true, null,
MClient.MMPOLICY_FiFo.Equals(MMPolicy), Get_TrxName());
if (storages == null || storages.Length == 0)
{
AddLog("No Inventory in " + whSource.GetName()
+ " for " + product.GetName());
continue;
}
//
Decimal target = replenish.GetQtyToOrder();
for (int j = 0; j < storages.Length; j++)
{
MStorage storage = storages[j];
//if (storage.GetQtyOnHand().signum() <= 0)
if (Env.Signum(storage.GetQtyOnHand()) <= 0)
{
continue;
}
Decimal moveQty = target;
if (storage.GetQtyOnHand().CompareTo(moveQty) < 0)
{
moveQty = storage.GetQtyOnHand();
}
//
MMovementLine line = new MMovementLine(move);
line.SetM_Product_ID(replenish.GetM_Product_ID());
line.SetMovementQty(moveQty);
if (replenish.GetQtyToOrder().CompareTo(moveQty) != 0)
{
line.SetDescription("Total: " + replenish.GetQtyToOrder());
}
line.SetM_Locator_ID(storage.GetM_Locator_ID()); // from
line.SetM_AttributeSetInstance_ID(storage.GetM_AttributeSetInstance_ID());
line.SetM_LocatorTo_ID(M_LocatorTo_ID); // to
line.SetM_AttributeSetInstanceTo_ID(storage.GetM_AttributeSetInstance_ID());
line.Save();
//
//target = target.subtract(moveQty);
target = Decimal.Subtract(target, moveQty);
//if (target.signum() == 0)
if (Env.Signum(target) == 0)
{
break;
}
}
if (Env.Signum(target) != 0)
{
AddLog("Insufficient Inventory in " + whSource.GetName()
+ " for " + product.GetName() + " Qty=" + target);
}
}
if (replenishs.Length == 0)
{
_info = "No Source Warehouse";
log.Warning(_info);
}
else
{
_info = "#" + noMoves + info;
log.Info(_info);
}
} // createRequisition
protected override bool BeforeSave(bool newRecord)
{
// Movement Quantity can not be less than 0 when planned is greater than 0
if (GetPlannedQty() > 0 && GetMovementQty() < 0)
{
log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbelesszero"));
return(false);
}
// Movement Quantity can not be greater than 0 when planned is less than 0
if (GetPlannedQty() < 0 && GetMovementQty() > 0)
{
log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbegrtzero"));
return(false);
}
MProduct product = new MProduct(GetCtx(), GetM_Product_ID(), Get_TrxName());
if (newRecord && product != null)
{
if (Util.GetValueOfString(product.Get_Value("IsSlopeOil")) == "True" && GetMovementQty() < 0)
{
log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbelesszero"));
return(false);
}
if (Util.GetValueOfString(product.Get_Value("IsSlopeOil")) == "False" && GetMovementQty() > 0)
{
log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbegrtzero"));
return(false);
}
}
X_M_ProductionPlan plan = new X_M_ProductionPlan(GetCtx(), GetM_ProductionPlan_ID(), Get_TrxName());
if (Util.GetValueOfDecimal(plan.Get_Value("GOM01_ComponentLimit")) > 0 && GetPlannedQty() < 0)
{
decimal ComputedQty = (GetPlannedQty() * Util.GetValueOfDecimal(plan.Get_Value("GOM01_ComponentLimit"))) / 100;
ComputedQty = decimal.Negate(GetPlannedQty() + ComputedQty);
if (decimal.Negate(GetMovementQty()) > ComputedQty)
{
log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "GOM01_QtyConsumption"));
return(false);
}
}
// when warehouse disallow negative inventory is false then on hand qty can't be in negative
wh = MWarehouse.Get(GetCtx(), GetM_Warehouse_ID());
if (wh.IsDisallowNegativeInv() && GetM_Product_ID() > 0)
{
product = MProduct.Get(GetCtx(), GetM_Product_ID());
string qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)),0) AS QtyOnHand FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID();
//if (GetM_AttributeSetInstance_ID() != 0)
//{
qry += " AND NVL(M_AttributeSetInstance_ID , 0) =" + GetM_AttributeSetInstance_ID();
//}
Decimal?OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry));
qry = @"SELECT NVL(SUM(MovementQty) , 0) FROM M_ProductionLine WHERE IsActive = 'Y' AND M_Locator_ID=" + GetM_Locator_ID() + @" AND m_product_id=" + GetM_Product_ID() +
@" AND NVL(M_AttributeSetInstance_ID , 0) =" + GetM_AttributeSetInstance_ID() + @" AND M_Production_ID = " + GetM_Production_ID();
if (!newRecord)
{
qry += @" AND M_ProductionLine_ID <> " + GetM_ProductionLine_ID();
}
Decimal?moveQty = Convert.ToDecimal(DB.ExecuteScalar(qry));
if ((OnHandQty + GetMovementQty() + moveQty) < 0)
{
log.SaveError("", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
return(false);
}
}
return(true);
}
/// <summary>
/// Before Save
/// </summary>
/// <param name="newRecord">new</param>
/// <returns>true if can be saved</returns>
protected override bool BeforeSave(bool newRecord)
{
Decimal VA024_ProvisionPrice = 0;
MInventory inventory = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
MProduct product = MProduct.Get(GetCtx(), GetM_Product_ID());
if (newRecord && _isManualEntry)
{
// Product requires ASI
if (GetM_AttributeSetInstance_ID() == 0)
{
if (product.GetM_AttributeSet_ID() != 0)
{
MAttributeSet mas = MAttributeSet.Get(GetCtx(), product.GetM_AttributeSet_ID());
//uncomment by Amit on behalf of Mandeep 7-3-2016
//if (mas.IsInstanceAttribute()
// && (mas.IsMandatory() || mas.IsMandatoryAlways()))
if (mas.IsMandatory() || mas.IsMandatoryAlways())
{
log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "M_AttributeSetInstance_ID"));
return(false);
}
}
} // No ASI
} // new or manual
// not to create Internal use Inventory with -ve qty -- but during reversal system will create record with -ve qty
// duing reversal -- ReversalDoc_ID contain refernce o orignal record id
if (IsInternalUse() && Get_ColumnIndex("ReversalDoc_ID") > 0 && GetReversalDoc_ID() == 0 && GetQtyInternalUse() < 0)
{
log.SaveError("", Msg.GetMsg(GetCtx(), "VIS_CantbeNegative"));
return(false);
}
// Set Line No
if (GetLine() == 0)
{
String sql = "SELECT COALESCE(MAX(Line),0)+10 AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + GetM_Inventory_ID();
int ii = DB.GetSQLValue(Get_TrxName(), sql);
SetLine(ii);
}
// SI_0644.1 : Enforce UOM Precision - Rounding Quantities
if (newRecord || Is_ValueChanged("QtyInternalUse"))
{
SetQtyInternalUse(GetQtyInternalUse());
}
// change to set Converted Quantity in Internal Use Qty and AsonDateQty and difference qty if there is differnce in UOM of Base Product and UOM Selected on line
if (newRecord || Is_ValueChanged("QtyEntered") || Is_ValueChanged("C_UOM_ID"))
{
Decimal?qty = Util.GetValueOfDecimal(Get_Value("QtyEntered"));
if (product.GetC_UOM_ID() != Util.GetValueOfInt(Get_Value("C_UOM_ID")))
{
qty = MUOMConversion.ConvertProductFrom(GetCtx(), GetM_Product_ID(), Util.GetValueOfInt(Get_Value("C_UOM_ID")), Util.GetValueOfDecimal(Get_Value("QtyEntered")));
if (IsInternalUse())
{
SetQtyInternalUse(qty);
}
else
{
SetAsOnDateCount(qty);
SetDifferenceQty(qty);
}
}
}
// SI_0644 - As on date and difference should be according to the precision of UOM attached.
if (newRecord || Is_ValueChanged("AsOnDateCount"))
{
if (product != null)
{
int precision = product.GetUOMPrecision();
SetAsOnDateCount(Decimal.Round(GetAsOnDateCount(), precision, MidpointRounding.AwayFromZero));
}
}
// SI_0644 - As on date and difference should be according to the precision of UOM attached.
if (newRecord || Is_ValueChanged("DifferenceQty"))
{
if (product != null)
{
int precision = product.GetUOMPrecision();
SetDifferenceQty(Decimal.Round(GetDifferenceQty(), precision, MidpointRounding.AwayFromZero));
}
}
// SI_0682_1 Need to update the reserved qty on requisition line by internal use line save aslo and should work as work in inventory move.
if (Env.IsModuleInstalled("DTD001_") && IsInternalUse())
{
qtyReserved = Util.GetValueOfDecimal(Get_ValueOld("QtyInternalUse"));
}
int M_Warehouse_ID = 0; MWarehouse wh = null;
string qry = "select m_warehouse_id from m_locator where m_locator_id=" + GetM_Locator_ID();
M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_TrxName()));
wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID);
qry = "SELECT QtyOnHand FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID();
if (GetM_AttributeSetInstance_ID() != 0)
{
qry += " AND M_AttributeSetInstance_ID=" + GetM_AttributeSetInstance_ID();
}
OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry, null, Get_TrxName()));
// when record is in completed & closed stage - then no need to check qty availablity in warehouse
if (wh.IsDisallowNegativeInv() == true &&
(!(inventory.GetDocStatus() == "CO" || inventory.GetDocStatus() == "CL" ||
inventory.GetDocStatus() == "RE" || inventory.GetDocStatus() == "VO" || inventory.GetDocStatus() == "IP")))
{
#region DisallowNegativeInv = True
if (!IsInternalUse() && GetDifferenceQty() > 0)
{
if ((OnHandQty - GetDifferenceQty()) < 0)
{
log.SaveError("Info", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
return(false);
}
}
else if (IsInternalUse())
{
if ((OnHandQty - GetQtyInternalUse()) < 0)
{
log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
return(false);
}
}
#endregion
}
// Enforce Qty UOM
if (newRecord || Is_ValueChanged("QtyCount"))
{
SetQtyCount(GetQtyCount());
}
// InternalUse Inventory
if (IsInternalUse() && Env.Signum(GetQtyInternalUse()) == 0)
{
log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "QtyInternalUse"));
return(false);
}
if (Env.Signum(GetQtyInternalUse()) != 0)
{
if (!INVENTORYTYPE_ChargeAccount.Equals(GetInventoryType()))
{
SetInventoryType(INVENTORYTYPE_ChargeAccount);
}
//
if (GetC_Charge_ID() == 0)
{
log.SaveError("Error", Msg.GetMsg(GetCtx(), "InternalUseNeedsCharge"));
return(false);
}
}
else if (INVENTORYTYPE_ChargeAccount.Equals(GetInventoryType()))
{
if (GetC_Charge_ID() == 0)
{
log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "C_Charge_ID"));
return(false);
}
}
else if (GetC_Charge_ID() != 0)
{
SetC_Charge_ID(0);
}
// Set AD_Org to parent if not charge
if (GetC_Charge_ID() == 0)
{
SetAD_Org_ID(GetParent().GetAD_Org_ID());
}
// By Amit for Obsolete Inventory - 25-May-2016
if (Env.IsModuleInstalled("VA024_"))
{
//MInventory inventory = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
//shipment and Return to vendor
if (inventory.IsInternalUse() || (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0))
{
try
{
string qry1 = @"SELECT SUM(o.VA024_UnitPrice) FROM VA024_t_ObsoleteInventory o
WHERE o.IsActive = 'Y' AND o.M_Product_ID = " + GetM_Product_ID() + @" and
( o.M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID() + @" OR o.M_AttributeSetInstance_ID IS NULL )" +
" AND o.AD_Org_ID = " + GetAD_Org_ID();
//+" AND M_Warehouse_ID = " + inventory.GetM_Warehouse_ID();
VA024_ProvisionPrice = Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx()));
if (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0)
{
SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * (GetQtyBook() - GetQtyCount())));
}
else
{
SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * GetQtyInternalUse()));
}
if (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0)
{
qry1 = @"SELECT (ct.currentcostprice - " + VA024_ProvisionPrice + ") * " + (GetQtyBook() - GetQtyCount());
}
else
{
qry1 = @"SELECT (ct.currentcostprice - " + VA024_ProvisionPrice + ") * " + GetQtyInternalUse();
}
qry1 += @" FROM m_product p INNER JOIN va024_t_obsoleteinventory oi ON p.m_product_id = oi.M_product_ID
INNER JOIN m_product_category pc ON pc.m_product_category_ID = p.m_product_category_ID
INNER JOIN AD_client c ON c.AD_Client_ID = p.Ad_Client_ID INNER JOIN AD_ClientInfo ci ON c.AD_Client_ID = ci.Ad_Client_ID
INNER JOIN m_cost ct ON ( p.M_Product_ID = ct.M_Product_ID AND ci.C_AcctSchema1_ID = ct.C_AcctSchema_ID )
INNER JOIN c_acctschema asch ON (asch.C_AcctSchema_ID = ci.C_AcctSchema1_ID)
INNER JOIN va024_obsoleteinvline oil ON oil.va024_obsoleteinvline_ID = oi.va024_obsoleteinvline_ID ";
qry1 += @" WHERE ct.AD_Org_ID =
CASE WHEN ( pc.costinglevel IS NOT NULL AND pc.costinglevel = 'O') THEN " + GetAD_Org_ID() + @"
WHEN ( pc.costinglevel IS NOT NULL AND (pc.costinglevel = 'C' OR pc.costinglevel = 'B')) THEN 0
WHEN (pc.costinglevel IS NULL AND asch.costinglevel = 'O') THEN " + GetAD_Org_ID() + @"
WHEN ( pc.costinglevel IS NULL AND (asch.costinglevel = 'C' OR asch.costinglevel = 'B')) THEN 0 END
AND ct.m_costelement_id =
CASE WHEN ( pc.costingmethod IS NOT NULL AND pc.costingmethod != 'C') THEN (SELECT MIN(m_costelement_id) FROM m_costelement
WHERE m_costelement.costingmethod =pc.costingmethod AND m_costelement.Ad_Client_ID = oi.ad_client_id )
WHEN ( pc.costingmethod IS NOT NULL AND pc.costingmethod = 'C' ) THEN pc.m_costelement_id
WHEN ( pc.costingmethod IS NULL AND asch.costingmethod != 'C') THEN (SELECT MIN(m_costelement_id) FROM m_costelement
WHERE m_costelement.costingmethod = asch.costingmethod AND m_costelement.Ad_Client_ID = oi.ad_client_id )
WHEN ( pc.costingmethod IS NULL AND asch.costingmethod = 'C') THEN asch.m_costelement_id END
AND NVL(ct.M_Attributesetinstance_ID , 0) =
CASE WHEN ( pc.costinglevel IS NOT NULL AND pc.costinglevel = 'B') THEN " + GetM_AttributeSetInstance_ID() + @"
WHEN ( pc.costinglevel IS NOT NULL AND (pc.costinglevel = 'C' OR pc.costinglevel = 'O')) THEN 0
WHEN ( pc.costinglevel IS NULL AND asch.costinglevel = 'B') THEN " + GetM_AttributeSetInstance_ID() + @"
WHEN ( pc.costinglevel IS NULL AND (asch.costinglevel = 'C' OR asch.costinglevel = 'O')) THEN 0 END
AND p.M_Product_ID = " + GetM_Product_ID();
SetVA024_CostPrice(Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx())));
}
catch { }
}
}
return(true);
}