/// <summary>
/// Get Inventory Line with parameters
/// </summary>
/// <param name="inventory">inventory</param>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">asi</param>
/// <returns>line or null</returns>
public static MInventoryLine Get(MInventory inventory, int M_Locator_ID,
int M_Product_ID, int M_AttributeSetInstance_ID)
{
MInventoryLine retValue = null;
String sql = "SELECT * FROM M_InventoryLine "
+ "WHERE M_Inventory_ID=@invenid AND M_Locator_ID=@locid"
+ " AND M_Product_ID=@prodid AND M_AttributeSetInstance_ID=@asiid";
try
{
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@invenid", inventory.GetM_Inventory_ID());
param[1] = new SqlParameter("@locid", M_Locator_ID);
param[2] = new SqlParameter("@prodid", M_Product_ID);
param[3] = new SqlParameter("@asiid", M_AttributeSetInstance_ID);
DataSet ds = DataBase.DB.ExecuteDataset(sql, param);
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
retValue = new MInventoryLine(inventory.GetCtx(), dr, inventory.Get_TrxName());
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
return(retValue);
}
/**
* Parent Constructor
* @param parent parent
* @param M_AttributeSetInstance_ID asi
* @param MovementQty qty
*/
public MInventoryLineMA(MInventoryLine parent, int M_AttributeSetInstance_ID, Decimal MovementQty)
: this(parent.GetCtx(), 0, parent.Get_TrxName())
{
SetClientOrg(parent);
SetM_InventoryLine_ID(parent.GetM_InventoryLine_ID());
//
SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
SetMovementQty(MovementQty);
}
/// <summary>
/// Create Material Allocations for new Instances
/// </summary>
/// <param name="updateQtyBooked"></param>
public void CreateMA(bool updateQtyBooked)
{
VAdvantage.Model.MInventoryLine INVLine = new VAdvantage.Model.MInventoryLine(GetCtx(), GetM_InventoryLine_ID(), Get_TrxName());
int delMA = MInventoryLineMA.DeleteInventoryLineMA(GetM_InventoryLine_ID(), Get_TrxName());
log.Info("DeletedMA=" + delMA);
MStorage[] storages = MStorage.GetAll(GetCtx(), GetM_Product_ID(),
GetM_Locator_ID(), Get_TrxName());
bool allZeroASI = true;
for (int i = 0; i < storages.Length; i++)
{
if (storages[i].GetM_AttributeSetInstance_ID() != 0)
{
allZeroASI = false;
break;
}
}
if (allZeroASI)
{
return;
}
MInventoryLineMA ma = null;
Decimal sum = Env.ZERO;
for (int i = 0; i < storages.Length; i++)
{
MStorage storage = storages[i];
// nnayak - ignore negative layers
if (Env.Signum(storage.GetQtyOnHand()) <= 0)
{
continue;
}
if (ma != null &&
ma.GetM_AttributeSetInstance_ID() == storage.GetM_AttributeSetInstance_ID())
{
ma.SetMovementQty(Decimal.Add(ma.GetMovementQty(), storage.GetQtyOnHand()));
}
else
{
ma = new MInventoryLineMA(INVLine,
storage.GetM_AttributeSetInstance_ID(), storage.GetQtyOnHand());
}
if (!ma.Save())
{
;
}
sum = Decimal.Add(sum, storage.GetQtyOnHand());
}
if (updateQtyBooked && sum.CompareTo(GetQtyBook()) != 0)
{
log.Warning("QtyBook=" + GetQtyBook() + " corrected to Sum of MA=" + sum);
SetQtyBook(sum);
}
}
/// <summary>
/// Is Used to Get or Create Instance of MInventoryLineMA (Attribute)
/// </summary>
/// <param name="line"></param>
/// <param name="M_AttributeSetInstance_ID"></param>
/// <param name="MovementQty"></param>
/// <param name="DateMaterialPolicy"></param>
/// <returns></returns>
public static MInventoryLineMA GetOrCreate(MInventoryLine line, int M_AttributeSetInstance_ID, Decimal MovementQty, DateTime?DateMaterialPolicy)
{
MInventoryLineMA retValue = null;
String sql = "SELECT * FROM M_InventoryLineMA " +
@" WHERE M_InventoryLine_ID = " + line.GetM_InventoryLine_ID() +
@" AND MMPolicyDate = " + GlobalVariable.TO_DATE(DateMaterialPolicy, true) + @" AND ";
if (M_AttributeSetInstance_ID == 0)
{
sql += "(M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID + " OR M_AttributeSetInstance_ID IS NULL)";
}
else
{
sql += "M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID;
}
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, line.Get_Trx());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
retValue = new MInventoryLineMA(line.GetCtx(), dr, line.Get_Trx());
}
}
catch (Exception ex)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, ex);
}
finally
{
if (idr != null)
{
idr.Close();
}
dt = null;
}
if (retValue == null)
{
retValue = new MInventoryLineMA(line, M_AttributeSetInstance_ID, MovementQty, DateMaterialPolicy);
}
else
{
retValue.SetMovementQty(Decimal.Add(retValue.GetMovementQty(), MovementQty));
}
return(retValue);
}
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="parent"></param>
/// <param name="M_AttributeSetInstance_ID"></param>
/// <param name="movementQty"></param>
/// <param name="MMPloicyDate"></param>
public MInventoryLineMA(MInventoryLine parent, int M_AttributeSetInstance_ID, Decimal movementQty, DateTime?MMPloicyDate)
: this(parent.GetCtx(), 0, parent.Get_TrxName())
{
SetClientOrg(parent);
SetM_InventoryLine_ID(parent.GetM_InventoryLine_ID());
//
SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
SetMovementQty(movementQty);
if (MMPloicyDate == null)
{
MMPloicyDate = parent.GetParent().GetMovementDate();
}
SetMMPolicyDate(MMPloicyDate);
}
/// <summary>
/// Create Difference Document.
/// Creates one or two inventory lines
/// </summary>
/// <param name="move">movement</param>
/// <param name="confirm">confirm line</param>
/// <returns>true if created</returns>
private Boolean CreateDifferenceDoc(MMovement move, MMovementLineConfirm confirm)
{
string query = "";
int result = 0;
decimal currentQty = 0;
MMovementLine mLine = confirm.GetLine();
// Difference - Create Inventory Difference for Source Location
if (Env.ZERO.CompareTo(confirm.GetDifferenceQty()) != 0)
{
// Get Warehouse for Source
MLocator loc = MLocator.Get(GetCtx(), mLine.GetM_Locator_ID());
if (_inventoryFrom != null &&
_inventoryFrom.GetM_Warehouse_ID() != loc.GetM_Warehouse_ID())
{
_inventoryFrom = null;
}
if (_inventoryFrom == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), loc.GetM_Warehouse_ID());
_inventoryFrom = new MInventory(wh);
_inventoryFrom.SetDescription(Msg.Translate(GetCtx(), "M_MovementConfirm_ID") + " " + GetDocumentNo());
if (!_inventoryFrom.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
// First Inventory
if (GetM_Inventory_ID() == 0)
{
SetM_Inventory_ID(_inventoryFrom.GetM_Inventory_ID());
_inventoryInfo = _inventoryFrom.GetDocumentNo();
}
else
{
_inventoryInfo += "," + _inventoryFrom.GetDocumentNo();
}
}
log.Info("createDifferenceDoc - Difference=" + confirm.GetDifferenceQty());
MInventoryLine line = new MInventoryLine(_inventoryFrom,
mLine.GetM_Locator_ID(), mLine.GetM_Product_ID(), mLine.GetM_AttributeSetInstance_ID(),
confirm.GetDifferenceQty(), Env.ZERO);
//Added By amit 11-jun-2015
//Opening Stock , Qunatity Book => CurrentQty From Transaction of MovementDate
//As On Date Count = Opening Stock - Diff Qty
//Qty Count = Qty Book - Diff Qty
query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate = " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
result = Util.GetValueOfInt(DB.ExecuteScalar(query));
if (result > 0)
{
query = @"SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID =
(SELECT MAX(M_Transaction_ID) FROM M_Transaction
WHERE movementdate = (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate <= " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(query));
}
else
{
query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
result = Util.GetValueOfInt(DB.ExecuteScalar(query));
if (result > 0)
{
query = @"SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID =
(SELECT MAX(M_Transaction_ID) FROM M_Transaction
WHERE movementdate = (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(query));
}
}
//End
line.SetAdjustmentType("D");
line.SetDifferenceQty(Util.GetValueOfDecimal(confirm.GetDifferenceQty()));
line.SetQtyBook(currentQty);
line.SetOpeningStock(currentQty);
line.SetAsOnDateCount(Decimal.Subtract(Util.GetValueOfDecimal(line.GetOpeningStock()), Util.GetValueOfDecimal(confirm.GetDifferenceQty())));
line.SetQtyCount(Decimal.Subtract(Util.GetValueOfDecimal(line.GetQtyBook()), Util.GetValueOfDecimal(confirm.GetDifferenceQty())));
line.SetDescription(Msg.Translate(GetCtx(), "DifferenceQty"));
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
} // Difference
// Scrapped - Create Inventory Difference for TarGet Location
if (Env.ZERO.CompareTo(confirm.GetScrappedQty()) != 0)
{
// Get Warehouse for TarGet
MLocator loc = MLocator.Get(GetCtx(), mLine.GetM_LocatorTo_ID());
if (_inventoryTo != null &&
_inventoryTo.GetM_Warehouse_ID() != loc.GetM_Warehouse_ID())
{
_inventoryTo = null;
}
if (_inventoryTo == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), loc.GetM_Warehouse_ID());
_inventoryTo = new MInventory(wh);
_inventoryTo.SetDescription(Msg.Translate(GetCtx(), "M_MovementConfirm_ID") + " " + GetDocumentNo());
if (!_inventoryTo.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
// First Inventory
if (GetM_Inventory_ID() == 0)
{
SetM_Inventory_ID(_inventoryTo.GetM_Inventory_ID());
_inventoryInfo = _inventoryTo.GetDocumentNo();
}
else
{
_inventoryInfo += "," + _inventoryTo.GetDocumentNo();
}
}
log.Info("CreateDifferenceDoc - Scrapped=" + confirm.GetScrappedQty());
MInventoryLine line = new MInventoryLine(_inventoryTo,
mLine.GetM_LocatorTo_ID(), mLine.GetM_Product_ID(), mLine.GetM_AttributeSetInstance_ID(),
confirm.GetScrappedQty(), Env.ZERO);
line.SetDescription(Msg.Translate(GetCtx(), "ScrappedQty"));
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
} // Scrapped
return(true);
}
/**
* 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());
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);
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);
}
