/// <summary>
/// Get Storage Info for Container
/// </summary>
/// <param name="Ctx">context</param>
/// <param name="M_Warehouse_ID">warehouse</param>
/// <param name="M_Locator_ID">optional locator id</param>
/// <param name="M_ProductContainer_ID"></param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">M_AttributeSetInstance_ID instance</param>
/// <param name="minGuaranteeDate">optional minimum guarantee date if all attribute instances</param>
/// <param name="FiFo">first in-first-out</param>
/// <param name="trxName">transaction</param>
/// <returns>existing - ordered by location priority (desc) and/or guarantee date</returns>
public static X_M_ContainerStorage[] GetContainerStorageNegative(Ctx Ctx, int M_Warehouse_ID, int M_Locator_ID, int M_ProductContainer_ID,
int M_Product_ID, int M_AttributeSetInstance_ID, DateTime?minGuaranteeDate, bool FiFo, Trx trxName)
{
if ((M_Warehouse_ID == 0 && M_Locator_ID == 0) || M_Product_ID == 0)
{
return(new X_M_ContainerStorage[0]);
}
List <X_M_ContainerStorage> list = new List <X_M_ContainerStorage>();
String sql = "SELECT s.M_Locator_ID,s.M_ProductContainer_ID,s.M_Product_ID,s.M_AttributeSetInstance_ID,"
+ "s.AD_Client_ID,s.AD_Org_ID,s.IsActive,s.Created,s.CreatedBy,s.Updated,s.UpdatedBy,"
+ "s.Qty,s.MMPolicyDate "
+ "FROM M_ContainerStorage s"
+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID)"
+ " LEFT OUTER JOIN M_AttributeSetInstance asi ON (s.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID) ";
if (M_Locator_ID > 0)
{
sql += "WHERE l.M_Locator_ID = " + M_Locator_ID;
}
else
{
sql += "WHERE l.M_Warehouse_ID= " + M_Warehouse_ID;
}
if (M_ProductContainer_ID > 0)
{
sql += " AND s.M_ProductContainer_ID = " + M_ProductContainer_ID;
}
sql += " AND s.M_Product_ID=" + M_Product_ID + " AND s.Qty < 0 ";
// PT-225, PT-224 : get record specific attribte wise which is to be selected on document
if (M_AttributeSetInstance_ID > 0)
{
sql += "AND s.M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID;
}
else if (M_AttributeSetInstance_ID == 0)
{
sql += "AND (s.M_AttributeSetInstance_ID=0 OR s.M_AttributeSetInstance_ID IS NULL) ";
}
if (minGuaranteeDate != null)
{
sql += "AND (asi.GuaranteeDate IS NULL OR asi.GuaranteeDate>" + GlobalVariable.TO_DATE(minGuaranteeDate, true) + ")";
sql += "ORDER BY l.PriorityNo DESC, asi.GuaranteeDate, s.M_AttributeSetInstance_ID"; // Has Prior over Locator
if (!FiFo)
{
sql += " DESC";
}
//sql += ", l.PriorityNo DESC";
sql += ", s.MMPolicyDate ";
if (!FiFo)
{
sql += " DESC";
}
}
else
{
sql += "ORDER BY l.PriorityNo DESC, l.M_Locator_ID, s.MMPolicyDate";
if (!FiFo)
{
sql += " DESC , s.M_AttributeSetInstance_ID DESC";
}
else
{
sql += ", s.M_AttributeSetInstance_ID ";
}
}
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, trxName);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
list.Add(new X_M_ContainerStorage(Ctx, dr, trxName));
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally
{
if (idr != null)
{
idr.Close();
}
dt = null;
}
X_M_ContainerStorage[] retValue = new X_M_ContainerStorage[list.Count];
retValue = list.ToArray();
return(retValue);
}
/// <summary>
/// Get Storage Info for Container
/// </summary>
/// <param name="Ctx">context</param>
/// <param name="M_Warehouse_ID">Warehouse ID</param>
/// <param name="M_Locator_ID">optional locator id</param>
/// <param name="M_ProductContainer_ID">Product Container</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">M_AttributeSetInstance_ID instance</param>
/// <param name="M_AttributeSet_ID">attribute set</param>
/// <param name="allAttributeInstances">if true, all attribute set instances</param>
/// <param name="minGuaranteeDate">optional minimum guarantee date if all attribute instances</param>
/// <param name="FiFo">first in-first-out</param>
/// <param name="greater"></param>
/// <param name="trxName">transaction</param>
/// <returns>existing - ordered by location priority (desc) and/or guarantee date</returns>
public static X_M_ContainerStorage[] GetContainerStorage(Ctx Ctx, int M_Warehouse_ID, int M_Locator_ID, int M_ProductContainer_ID,
int M_Product_ID, int M_AttributeSetInstance_ID, int M_AttributeSet_ID,
bool allAttributeInstances, DateTime?minGuaranteeDate, bool FiFo, bool greater, Trx trxName, bool isContainerConsider)
{
if ((M_Warehouse_ID == 0 && M_Locator_ID == 0) || M_Product_ID == 0)
{
return(new X_M_ContainerStorage[0]);
}
if (M_AttributeSet_ID == 0)
{
allAttributeInstances = true;
}
else
{
MAttributeSet mas = MAttributeSet.Get(Ctx, M_AttributeSet_ID);
if (!mas.IsInstanceAttribute())
{
allAttributeInstances = true;
}
}
List <X_M_ContainerStorage> list = new List <X_M_ContainerStorage>();
// Specific Attribute Set Instance
String sql = "SELECT s.M_Locator_ID,s.M_ProductContainer_ID,s.M_Product_ID,s.M_AttributeSetInstance_ID,"
+ "s.AD_Client_ID,s.AD_Org_ID,s.IsActive,s.Created,s.CreatedBy,s.Updated,s.UpdatedBy,"
+ "s.Qty,s.MMPolicyDate "
+ "FROM M_ContainerStorage s"
+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID) ";
if (M_Locator_ID > 0)
{
sql += "WHERE l.M_Locator_ID = " + M_Locator_ID;
}
else
{
sql += "WHERE l.M_Warehouse_ID= " + M_Warehouse_ID;
}
sql += " AND s.M_Product_ID=" + M_Product_ID
+ " AND COALESCE(s.M_AttributeSetInstance_ID,0)= " + M_AttributeSetInstance_ID;
// consider Container
if (isContainerConsider)
{
sql += "AND NVL(s.M_ProductContainer_ID , 0) = " + M_ProductContainer_ID;
}
if (!FiFo && minGuaranteeDate != null)
{
sql += " AND MMPolicyDate " + (greater ? ">" : "<=") + GlobalVariable.TO_DATE(minGuaranteeDate, true);
}
sql += " ORDER BY l.PriorityNo DESC, s.MMPolicyDate";
if (!FiFo)
{
//sql += " DESC";
sql += (greater ? " ASC" : " DESC");
}
// All Attribute Set Instances
if (allAttributeInstances)
{
sql = "SELECT s.M_Locator_ID,s.M_ProductContainer_ID,s.M_Product_ID,s.M_AttributeSetInstance_ID,"
+ "s.AD_Client_ID,s.AD_Org_ID,s.IsActive,s.Created,s.CreatedBy,s.Updated,s.UpdatedBy,"
+ "s.Qty,s.MMPolicyDate "
+ "FROM M_ContainerStorage s"
+ " INNER JOIN M_Locator l ON (l.M_Locator_ID=s.M_Locator_ID)"
+ " LEFT OUTER JOIN M_AttributeSetInstance asi ON (s.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID) ";
if (M_Locator_ID > 0)
{
sql += "WHERE l.M_Locator_ID = " + M_Locator_ID;
}
else
{
sql += "WHERE l.M_Warehouse_ID= " + M_Warehouse_ID;
}
// consider Container
if (isContainerConsider)
{
sql += " AND NVL(s.M_ProductContainer_ID, 0) = " + M_ProductContainer_ID;
}
// product
sql += " AND s.M_Product_ID=" + M_Product_ID;
// consider Attribute Set Instance
if (M_AttributeSetInstance_ID > 0)
{
sql += " AND COALESCE(s.M_AttributeSetInstance_ID,0)= " + M_AttributeSetInstance_ID;
}
if (!FiFo && minGuaranteeDate != null)
{
sql += " AND MMPolicyDate " + (greater ? ">" : "<=") + GlobalVariable.TO_DATE(minGuaranteeDate, true);
}
if (minGuaranteeDate != null)
{
// when gurantee date is null then record to be filtered based on material Policy date
sql += "AND (asi.GuaranteeDate IS NULL OR asi.GuaranteeDate>" + GlobalVariable.TO_DATE(minGuaranteeDate, true) + ")"
+ @" ORDER BY l.PriorityNo DESC , asi.GuaranteeDate, CASE WHEN asi.GuaranteeDate IS NULL THEN s.MMPolicyDate ELSE asi.GuaranteeDate END ";
if (!FiFo)
{
sql += (greater ? " ASC" : " DESC");
}
sql += " , NVL(s.M_AttributeSetInstance_ID , 0)"; // Has Prior over Locator
if (!FiFo)
{
sql += " DESC";
}
sql += ", s.MMPolicyDate ";
if (!FiFo)
{
//sql += " DESC";
sql += (greater ? " ASC" : " DESC");
}
}
else
{
sql += "ORDER BY l.PriorityNo DESC, l.M_Locator_ID, s.MMPolicyDate";
if (!FiFo)
{
//sql += " DESC";
sql += (greater ? " ASC" : " DESC");
}
}
}
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, trxName);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
list.Add(new X_M_ContainerStorage(Ctx, dr, trxName));
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally
{
if (idr != null)
{
idr.Close();
}
dt = null;
}
X_M_ContainerStorage[] retValue = new X_M_ContainerStorage[list.Count];
retValue = list.ToArray();
return(retValue);
}
