/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Movement_ID=" + _m_Movement_ID
+ ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID
+ ", M_Product_ID=" + _m_product_ID
+ ", M_Product_Category_ID=" + _m_Product_Category_ID
+ ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld);
_movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx());
if (_movement.Get_ID() == 0)
{
throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID);
}
if (_movement.IsProcessed())
{
throw new SystemException("@M_Movement_ID@ @Processed@");
}
//
String sqlQry = "";
if (_deleteOld)
{
sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' "
+ "AND M_Movement_ID=" + _m_Movement_ID;
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("doIt - Deleted #" + no);
}
// Create Null Storage records
if (_qtyRange != null && _qtyRange.Equals("="))
{
sqlQry = "INSERT INTO M_Storage "
+ "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,"
+ " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) "
+ "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0,"
+ " l.M_Locator_ID, p.M_Product_ID, 0,"
+ " 0,0,0,null "
+ "FROM M_Locator l"
+ " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetM_Warehouse_ID();
if (_m_Locator_ID != 0)
{
sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID;
}
sqlQry += " AND l.IsDefault='Y'"
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"
+ " AND NOT EXISTS (SELECT * FROM M_Storage s"
+ " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) "
+ "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID"
+ " AND s.M_Product_ID=p.M_Product_ID)";
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("'0' Inserted #" + no);
}
StringBuilder sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " s.qtyOnHand, p.M_AttributeSet_ID "
+ "FROM M_Product p"
+ " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)"
+ " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID()
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
//
if (_m_Locator_ID != 0)
{
sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID);
}
//
if (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID))
{
sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") ");
}
//
if (_m_Product_Category_ID != 0)
{
sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID);
}
// Do not overwrite existing records
if (!_deleteOld)
{
sql.Append(" AND NOT EXISTS (SELECT * FROM M_MovementLine il "
+ "WHERE il.M_Movement_ID=" + _m_Movement_ID
+ " AND il.M_Product_ID=s.M_Product_ID"
+ " AND il.M_Locator_ID=s.M_Locator_ID"
+ " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0))");
}
// + " AND il.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID)");
//
sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
//
int count = 0;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx());
while (idr.Read())
{
int M_Product_ID = Util.GetValueOfInt(idr[0]);
int M_Locator_ID = Util.GetValueOfInt(idr[1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(idr[2]);
Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]);
if (qtyOnHand == null)
{
qtyOnHand = Env.ZERO;
}
int M_AttributeSet_ID = Util.GetValueOfInt(idr[4]);
//
int compare = qtyOnHand.CompareTo(Env.ZERO);
if (_qtyRange == null ||
(_qtyRange.Equals(">") && compare > 0) ||
(_qtyRange.Equals("<") && compare < 0) ||
(_qtyRange.Equals("=") && compare == 0) ||
(_qtyRange.Equals("N") && compare != 0))
{
//Save data on Movement Line
_line = new MMovementLine(GetCtx(), 0, Get_Trx());
_line.SetAD_Client_ID(_movement.GetAD_Client_ID());
_line.SetAD_Org_ID(_movement.GetAD_Org_ID());
_line.SetM_Movement_ID(_m_Movement_ID);
_line.SetM_Locator_ID(_m_Locator_ID);
_line.SetM_LocatorTo_ID(_m_LocatorTo_ID);
_line.SetMovementQty(qtyOnHand);
_line.SetProcessed(false);
_line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
_line.SetM_Product_ID(M_Product_ID);
if (!_line.Save())
{
log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID);
}
else
{
count = count + 1;
}
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
//
return("@M_MovementLine_ID@ - #" + count);
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Movement_ID=" + _m_Movement_ID
+ ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID
+ ", M_Product_ID=" + _m_product_ID
+ ", M_Product_Category_ID=" + _m_Product_Category_ID
+ ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld);
_movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx());
if (_movement.Get_ID() == 0)
{
throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID);
}
if (_movement.IsProcessed())
{
throw new SystemException("@M_Movement_ID@ @Processed@");
}
// is used to check Container applicable into system
isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx());
//
String sqlQry = "";
if (_deleteOld)
{
sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' "
+ "AND M_Movement_ID=" + _m_Movement_ID;
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("doIt - Deleted #" + no);
}
// Create Null Storage records
if (_qtyRange != null && _qtyRange.Equals("="))
{
sqlQry = "INSERT INTO M_Storage "
+ "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,"
+ " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) "
+ "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0,"
+ " l.M_Locator_ID, p.M_Product_ID, 0,"
+ " 0,0,0,null "
+ "FROM M_Locator l"
+ " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetM_Warehouse_ID();
if (_m_Locator_ID != 0)
{
sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID;
}
sqlQry += " AND l.IsDefault='Y'"
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"
+ " AND NOT EXISTS (SELECT * FROM M_Storage s"
+ " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) "
+ "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID"
+ " AND s.M_Product_ID=p.M_Product_ID)";
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("'0' Inserted #" + no);
}
StringBuilder sql = null;
if (!isContainerApplicable)
{
sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " s.qtyOnHand, p.M_AttributeSet_ID, 0 AS M_ProductContainer_ID "
+ "FROM M_Product p"
+ " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)"
+ " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID()
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
else
{
sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " NVL(SUM(s.Qty) , 0) AS qtyOnHand , p.M_AttributeSet_ID, s.M_ProductContainer_ID "
+ "FROM M_Product p"
+ " INNER JOIN M_ContainerStorage s ON (s.M_Product_ID=p.M_Product_ID)"
+ " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID()
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
//
if (_m_Locator_ID != 0)
{
sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID);
}
//
if (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID))
{
sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") ");
}
//
if (_m_Product_Category_ID != 0)
{
sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID);
}
// Do not overwrite existing records
if (!_deleteOld)
{
sql.Append(" AND NOT EXISTS (SELECT * FROM M_MovementLine il "
+ "WHERE il.M_Movement_ID=" + _m_Movement_ID
+ " AND il.M_Product_ID=s.M_Product_ID"
+ " AND il.M_Locator_ID=s.M_Locator_ID"
+ " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0)");
if (!isContainerApplicable)
{
sql.Append(@" ) ");
}
else
{
sql.Append(@" AND COALESCE(il.M_ProductContainer_ID,0)=COALESCE(s.M_ProductContainer_ID,0) ) ");
}
}
//
if (!isContainerApplicable)
{
sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
}
else
{
sql.Append(@" GROUP BY s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID, s.M_ProductContainer_ID, s.Qty
ORDER BY s.M_Locator_ID, s.M_Product_ID, s.Qty DESC, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID,s.M_ProductContainer_ID"); // Locator/Product
}
//
int count = 0;
IDataReader idr = null;
DataTable dt = null;
MProduct product = null;
try
{
idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// while (idr.Read())
{
int M_Product_ID = Util.GetValueOfInt(dr[0]);
product = MProduct.Get(GetCtx(), M_Product_ID);
int M_Locator_ID = Util.GetValueOfInt(dr[1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(dr[2]);
Decimal qtyOnHand = Util.GetValueOfDecimal(dr[3]);
//if (qtyOnHand == null) commented by manjot Because Decimal is Never equals to Null
// qtyOnHand = Env.ZERO;
int M_AttributeSet_ID = Util.GetValueOfInt(dr[4]);
//container
int container_Id = Util.GetValueOfInt(dr[5]);
//
int compare = qtyOnHand.CompareTo(Env.ZERO);
if (_qtyRange == null ||
(_qtyRange.Equals(">") && compare > 0) ||
(_qtyRange.Equals("<") && compare < 0) ||
(_qtyRange.Equals("=") && compare == 0) ||
(_qtyRange.Equals("N") && compare != 0))
{
//Save data on Movement Line
_line = new MMovementLine(GetCtx(), 0, Get_Trx());
_line.SetAD_Client_ID(_movement.GetAD_Client_ID());
_line.SetAD_Org_ID(_movement.GetAD_Org_ID());
_line.SetM_Movement_ID(_m_Movement_ID);
_line.SetM_Locator_ID(_m_Locator_ID);
_line.SetM_LocatorTo_ID(_m_LocatorTo_ID);
_line.SetMovementQty(qtyOnHand);
_line.SetProcessed(false);
_line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
_line.SetM_Product_ID(M_Product_ID);
if (isContainerApplicable && _line.Get_ColumnIndex("M_ProductContainer_ID") > 0)
{
_line.SetM_ProductContainer_ID(container_Id);
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0 && product != null)
{
_line.SetC_UOM_ID(product.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.SetQtyEntered(qtyOnHand);
}
if (!_line.Save())
{
return(GetRetrievedError(_line, "Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID));
//log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID);
}
else
{
count = count + 1;
}
}
}
//idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
//
return("@M_MovementLine_ID@ - #" + count);
}