/// <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);
}
} // 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
