/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx()); log.Info("M_Inventory_ID=" + _m_Inventory_ID); inventory = new MInventory(GetCtx(), _m_Inventory_ID, Get_TrxName()); if (inventory.Get_ID() == 0) { throw new SystemException("Not found: M_Inventory_ID=" + _m_Inventory_ID); } // Multiple Lines for one item //jz simple the SQL so that Derby also like it. To avoid testing Oracle by now, leave no change for Oracle String sql = null; if (DataBase.DB.IsOracle()) { sql = "UPDATE M_InventoryLine SET IsActive='N' " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND (M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID) IN " + "(SELECT M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID " + "FROM M_InventoryLine " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " GROUP BY M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID " + (isContainerApplicable ? " , M_ProductContainer_ID" : "") + "HAVING COUNT(*) > 1)"; } else { sql = "UPDATE M_InventoryLine SET IsActive='N' " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND EXISTS " + "(SELECT COUNT(*) " + "FROM M_InventoryLine " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " GROUP BY M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID " + (isContainerApplicable ? " , M_ProductContainer_ID" : "") + "HAVING COUNT(*) > 1)"; } int multiple = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); log.Info("Multiple=" + multiple); int delMA = MInventoryLineMA.DeleteInventoryMA(_m_Inventory_ID, Get_TrxName()); log.Info("DeletedMA=" + delMA); // ASI sql = "UPDATE M_InventoryLine l " + "SET (QtyBook,QtyCount) = " + "(SELECT QtyOnHand,QtyOnHand FROM M_Storage s " + "WHERE s.M_Product_ID=l.M_Product_ID AND s.M_Locator_ID=l.M_Locator_ID" + " AND s.M_AttributeSetInstance_ID=l.M_AttributeSetInstance_ID)," + " Updated=SysDate," + " UpdatedBy=" + GetAD_User_ID() // + " WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND EXISTS (SELECT * FROM M_Storage s " + "WHERE s.M_Product_ID=l.M_Product_ID AND s.M_Locator_ID=l.M_Locator_ID" + " AND s.M_AttributeSetInstance_ID=l.M_AttributeSetInstance_ID)"; int no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); log.Info("Update with ASI =" + no); // No ASI //int noMA = UpdateWithMA(); // Set Count to Zero if (_inventoryCountSetZero) { sql = "UPDATE M_InventoryLine l " + "SET QtyCount=0 " + "WHERE M_Inventory_ID=" + _m_Inventory_ID; no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); log.Info("Set Count to Zero =" + no); } if (_AdjustinventoryCount) { // MInventoryLine[] lines = inventory.GetLines(true); // for (int i = 0; i < lines.Length; i++) // { // decimal currentQty = 0; // string query = "", qry = ""; // int result = 0; // MInventoryLine iLine = lines[i]; // int M_Product_ID = Utility.Util.GetValueOfInt(iLine.GetM_Product_ID()); // int M_Locator_ID = Utility.Util.GetValueOfInt(iLine.GetM_Locator_ID()); // int M_AttributeSetInstance_ID = Util.GetValueOfInt(iLine.GetM_AttributeSetInstance_ID()); // query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate = " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // result = Util.GetValueOfInt(DB.ExecuteScalar(query)); // if (result > 0) // { // qry = @"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(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry)); // } // else // { // query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // result = Util.GetValueOfInt(DB.ExecuteScalar(query)); // if (result > 0) // { // qry = @"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(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry)); // } // } // iLine.SetQtyBook(currentQty); // iLine.SetOpeningStock(currentQty); // if (iLine.GetAdjustmentType() == "A") // { // iLine.SetDifferenceQty(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetAsOnDateCount())); // } // else if (iLine.GetAdjustmentType() == "D") // { // iLine.SetAsOnDateCount(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); // } // iLine.SetQtyCount(Util.GetValueOfDecimal(iLine.GetQtyBook()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); // if (!iLine.Save()) // { // } // } // Work done by Bharat on 26/12/2016 for optimization if (isContainerApplicable) { sql = @"SELECT m.M_InventoryLine_ID, m.M_Locator_ID, m.M_Product_ID, m.M_AttributeSetInstance_ID, m.AdjustmentType, m.AsOnDateCount, m.DifferenceQty, nvl(mt.CurrentQty, 0) as CurrentQty FROM M_InventoryLine m LEFT JOIN (SELECT DISTINCT t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_ProductContainer_ID, FIRST_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID, NVL(t.M_ProductContainer_ID, 0) ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM M_Transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + " AND t.AD_Client_ID = " + inventory.GetAD_Client_ID() + @") mt ON m.M_Product_ID = mt.M_Product_ID AND nvl(m.M_AttributeSetInstance_ID, 0) = nvl(mt.M_AttributeSetInstance_ID, 0) AND m.M_Locator_ID = mt.M_Locator_ID AND nvl(m.M_ProductContainer_ID, 0) = nvl(mt.M_ProductContainer_ID, 0) WHERE m.M_Inventory_ID = " + _m_Inventory_ID + " ORDER BY m.Line"; } else { sql = @"SELECT m.M_InventoryLine_ID, m.M_Locator_ID, m.M_Product_ID, m.M_AttributeSetInstance_ID, m.AdjustmentType, m.AsOnDateCount, m.DifferenceQty, nvl(mt.CurrentQty, 0) as CurrentQty FROM M_InventoryLine m LEFT JOIN (SELECT DISTINCT t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID, FIRST_VALUE(t.CurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM M_Transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + " AND t.AD_Client_ID = " + inventory.GetAD_Client_ID() + @") mt ON m.M_Product_ID = mt.M_Product_ID AND nvl(m.M_AttributeSetInstance_ID, 0) = nvl(mt.M_AttributeSetInstance_ID, 0) AND m.M_Locator_ID = mt.M_Locator_ID WHERE m.M_Inventory_ID = " + _m_Inventory_ID + " ORDER BY m.Line"; } int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_InventoryLine_ID) FROM ( " + sql + " ) t", null, null)); int pageSize = 500; int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1); int count = 0; DataSet ds = null; StringBuilder updateSql = new StringBuilder(); try { if (totalRec > 0) { log.Info(" =====> Physical Inventory update process Started at " + DateTime.Now.ToString()); if (!_SkipBL) { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { ds = DB.GetDatabase().ExecuteDatasetPaging(sql, pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { decimal currentQty = 0; int line_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][7]); MInventoryLine iLine = new MInventoryLine(GetCtx(), line_ID, Get_TrxName()); iLine.SetQtyBook(currentQty); iLine.SetOpeningStock(currentQty); if (iLine.GetAdjustmentType() == "A") { iLine.SetDifferenceQty(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetAsOnDateCount())); } else if (iLine.GetAdjustmentType() == "D") { iLine.SetAsOnDateCount(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); } iLine.SetQtyCount(Util.GetValueOfDecimal(iLine.GetQtyBook()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); if (!iLine.Save()) { } else { count++; } } ds.Dispose(); log.Info(" =====> records updated at " + DateTime.Now.ToString() + " are = " + count + " <===== "); } } } else { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { //updateSql.Clear(); ds = DB.GetDatabase().ExecuteDatasetPaging(sql, pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { //updateSql.Append("BEGIN "); //for (int j = 0; j < ds.Tables[0].Rows.Count; j++) //{ // int line_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); // int locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]); // int product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]); // string AdjustType = Util.GetValueOfString(ds.Tables[0].Rows[j][4]); // decimal AsonDateCount = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][5]); // decimal DiffQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][6]); // decimal currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][7]); // string updateQry = UpdateInventoryLine(line_ID, product_ID, locator_ID, currentQty, AdjustType, AsonDateCount, DiffQty); // if (updateQry != "") // { // updateSql.Append(updateQry); // } //} //ds.Dispose(); //updateSql.Append(" END;"); //int cnt = DB.ExecuteQuery(updateSql.ToString(), null, Get_Trx()); //log.Info(" =====> records updated at " + DateTime.Now.ToString() + " are = " + count + " <===== "); string updateQry = DBFunctionCollection.UpdateInventoryLine(GetCtx(), ds, Get_Trx()); ds.Dispose(); int cnt = DB.ExecuteQuery(updateQry, null, Get_Trx()); } } } log.Info(" =====> Physical Inventory update process end at " + DateTime.Now.ToString()); } } catch (Exception e) { if (ds != null) { ds.Dispose(); } log.Log(Level.SEVERE, sql.ToString(), e); } } inventory.SetIsAdjusted(true); if (!inventory.Save()) { } if (multiple > 0) { return("@M_InventoryLine_ID@ - #" + no + " --> @InventoryProductMultiple@"); } //return "@M_InventoryLine_ID@ - #" + (no + noMA); return("Physical Inventory Updated"); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { // is used to check Container applicable into system isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx()); log.Info("M_Inventory_ID=" + _m_Inventory_ID + ", M_Locator_ID=" + _m_Locator_ID + ", LocatorValue=" + _locatorValue + ", ProductValue=" + _productValue + ", M_Product_Category_ID=" + _m_Product_Category_ID + ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld); _inventory = new MInventory(GetCtx(), _m_Inventory_ID, Get_TrxName()); if (_inventory.Get_ID() == 0) { throw new SystemException("Not found: M_Inventory_ID=" + _m_Inventory_ID); } if (_inventory.IsProcessed()) { throw new SystemException("@M_Inventory_ID@ @Processed@"); } // String sqlQry = ""; StringBuilder sql = null; int count = 0; if (_deleteOld) { sqlQry = "DELETE FROM M_InventoryLine WHERE Processed='N' " + "AND M_Inventory_ID=" + _m_Inventory_ID; int no = DataBase.DB.ExecuteQuery(sqlQry, null, Get_TrxName()); log.Fine("doIt - Deleted #" + no); } if (!isContainerApplicable) { sql = new StringBuilder( @"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty FROM (SELECT DISTINCT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, FIRST_VALUE(t.CurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) + @" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() + @" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @") t GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID ) SELECT DISTINCT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, 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) JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0)) WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } else { sql = new StringBuilder(@"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty, M_ProductContainer_ID FROM (SELECT DISTINCT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, NVL(t.M_ProductContainer_ID , 0) AS M_ProductContainer_ID, FIRST_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID, NVL(t.M_ProductContainer_ID, 0) ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) + @" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() + @" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @") t GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, M_ProductContainer_ID ) SELECT DISTINCT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, mt.M_ProductContainer_ID, p.M_AttributeSet_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) JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0) AND mt.M_ProductContainer_ID = NVL(s.M_ProductContainer_ID , 0)) WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_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 (_locatorValue != null && (_locatorValue.Trim().Length == 0 || _locatorValue.Equals("%"))) { _locatorValue = null; } if (_locatorValue != null) { sql.Append(" AND UPPER(l.Value) LIKE '" + _locatorValue.ToUpper() + "'"); } // if (_productValue != null && (_productValue.Trim().Length == 0 || _productValue.Equals("%"))) { _productValue = null; } if (_productValue != null) { sql.Append(" AND UPPER(p.Value) LIKE '" + _productValue.ToUpper() + "'"); } 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_InventoryLine il " + "WHERE il.M_Inventory_ID=" + _m_Inventory_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 (_qtyRange == "N") { sql.Append(" AND mt.currentqty != 0"); } /*SI_0631 : System is giving error ORA-00920: invalid relational operator if User does not select any value from Inventory Quantity list. * Physical Inventory Window -- button -- Create inventory count list*/ else if (!String.IsNullOrEmpty(_qtyRange)) { sql.Append(" AND mt.currentqty " + _qtyRange + " 0"); } int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_Product_ID) FROM ( " + sql.ToString() + " ) t", null, null)); int pageSize = 500; int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1); StringBuilder insertSql = new StringBuilder(); DataSet ds = null; try { if (totalRec > 0) { log.Info(" =====> Physical Inventory process Started at " + DateTime.Now.ToString()); if (!_SkipBL) { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { decimal currentQty = 0; int container_ID = 0; int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]); int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]); int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]); if (isContainerApplicable) { container_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["M_ProductContainer_ID"]); } currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]); count += CreateInventoryLine(M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, currentQty, currentQty, M_AttributeSet_ID, container_ID); } ds.Dispose(); log.Info(" =====> records inserted at " + DateTime.Now.ToString() + " are = " + count + " <===== "); } } } else { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { //insertSql.Clear(); ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { sqlQry = "SELECT COALESCE(MAX(Line),0) AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + _m_Inventory_ID; int lineNo = DB.GetSQLValue(Get_Trx(), sqlQry); //insertSql.Append("BEGIN "); //for (int j = 0; j < ds.Tables[0].Rows.Count; j++) //{ // decimal currentQty = 0; // int container_ID = 0; // int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); // int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]); // int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]); // int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]); // if (isContainerApplicable) // { // container_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["M_ProductContainer_ID"]); // } // currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]); // lineNo = lineNo + 10; // string insertQry = InsertInventoryLine(M_Locator_ID, M_Product_ID, lineNo, M_AttributeSetInstance_ID, currentQty, M_AttributeSet_ID, container_ID); // if (insertQry != "") // { // insertSql.Append(insertQry); // } //} //ds.Dispose(); //insertSql.Append(" END;"); string insertQry = DBFunctionCollection.InsertInventoryLine(GetCtx(), _inventory, ds, lineNo, isContainerApplicable, Get_Trx()); ds.Dispose(); int no = DB.ExecuteQuery(insertQry, null, Get_Trx()); } } } log.Info(" =====> Physical Inventory process end at " + DateTime.Now.ToString()); } } catch (Exception e) { if (ds != null) { ds.Dispose(); } log.Log(Level.SEVERE, sql.ToString(), e); } // Set Count to Zero if (_inventoryCountSetZero) { String sql1 = ""; MInventoryLine inv = new MInventoryLine(GetCtx(), 0, null); if (inv.Get_ColumnIndex("IsFromProcess") >= 0) { sql1 = "UPDATE M_InventoryLine l " + "SET QtyCount=0,AsOnDateCount=0 " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND IsFromProcess = 'Y'"; } else { sql1 = "UPDATE M_InventoryLine l " + "SET QtyCount=0,AsOnDateCount=0 " + "WHERE M_Inventory_ID=" + _m_Inventory_ID; } int no = DataBase.DB.ExecuteQuery(sql1, null, Get_TrxName()); log.Info("Set Cont to Zero=" + no); } return("Physical Inventory Created"); }
/// <summary> /// Is used to do a reverse entry of "Production Record" into the system /// </summary> /// <returns>message successfuly created or not</returns> protected override string DoIt() { if (M_Production_ID > 0) { //Copy Production Header X_M_Production production = new X_M_Production(GetCtx(), M_Production_ID, Get_Trx()); //check production is Reversed or not, if Reversed then not to do anything if (production.IsReversed()) { return(Msg.GetMsg(GetCtx(), "AlreadyReversed")); } //Get data from Production Plan dsProductionPlan = DB.ExecuteDataset(@"SELECT AD_CLIENT_ID , AD_ORG_ID , DESCRIPTION , LINE , M_LOCATOR_ID , M_PRODUCT_ID , M_PRODUCTIONPLAN_ID , M_PRODUCTION_ID , PROCESSED , PRODUCTIONQTY M_WAREHOUSE_ID FROM M_ProductionPlan WHERE IsActive = 'Y' AND M_PRODUCTION_ID = " + M_Production_ID, null, Get_Trx()); //get data from production Line dsProductionLine = DB.ExecuteDataset(@"SELECT AD_CLIENT_ID , AD_ORG_ID , DESCRIPTION , LINE , M_ATTRIBUTESETINSTANCE_ID , M_LOCATOR_ID , M_PRODUCT_ID , M_PRODUCTIONLINE_ID, M_PRODUCTIONPLAN_ID , M_PRODUCTION_ID , PROCESSED , MOVEMENTQTY , C_UOM_ID , PLANNEDQTY , M_WAREHOUSE_ID FROM M_ProductionLine WHERE IsActive = 'Y' AND M_PRODUCTION_ID = " + M_Production_ID, null, Get_Trx()); // Create New record of Production Header with Reverse Entry X_M_Production productionTo = new X_M_Production(production.GetCtx(), 0, production.Get_Trx()); try { productionTo.Set_TrxName(production.Get_Trx()); PO.CopyValues(production, productionTo, production.GetAD_Client_ID(), production.GetAD_Org_ID()); productionTo.SetName("{->" + productionTo.GetName() + ")"); if (production.Get_ColumnIndex("DocumentNo") > 0) { productionTo.Set_Value("DocumentNo", ("{->" + production.Get_Value("DocumentNo") + ")")); } productionTo.SetMovementDate(production.GetMovementDate()); //SI_0662 : not to create reverse record in current date, it should be created with the same date. productionTo.SetProcessed(false); if (!productionTo.Save(production.Get_Trx())) { production.Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); _log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); throw new Exception("Could not create Production reverse entry"); } else { #region create new record of Production Plan if (dsProductionPlan != null && dsProductionPlan.Tables.Count > 0 && dsProductionPlan.Tables[0].Rows.Count > 0) { for (int i = 0; i < dsProductionPlan.Tables[0].Rows.Count; i++) { //Original Line fromProdPlan = new X_M_ProductionPlan(GetCtx(), Util.GetValueOfInt(dsProductionPlan.Tables[0].Rows[i]["M_PRODUCTIONPLAN_ID"]), Get_Trx()); // Create New record of Production Plan with Reverse Entry toProdPlan = new X_M_ProductionPlan(production.GetCtx(), 0, production.Get_Trx()); try { toProdPlan.Set_TrxName(production.Get_Trx()); PO.CopyValues(fromProdPlan, toProdPlan, fromProdPlan.GetAD_Client_ID(), fromProdPlan.GetAD_Org_ID()); toProdPlan.SetProductionQty(Decimal.Negate(toProdPlan.GetProductionQty())); toProdPlan.SetM_Production_ID(productionTo.GetM_Production_ID()); toProdPlan.SetProcessed(false); if (!toProdPlan.Save(production.Get_Trx())) { production.Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); _log.Log(Level.SEVERE, "Could Not create Production Plan reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); throw new Exception("Could not create Production Plan reverse entry"); } else { #region check record exist on production line if (dsProductionLine != null && dsProductionLine.Tables.Count > 0 && dsProductionLine.Tables[0].Rows.Count > 0) { //check record exist on production line against production plan drProductionLine = dsProductionLine.Tables[0].Select("M_ProductionPlan_ID = " + fromProdPlan.GetM_ProductionPlan_ID()); if (drProductionLine.Length > 0) { for (int j = 0; j < drProductionLine.Length; j++) { //Original Line fromProdline = new X_M_ProductionLine(GetCtx(), Util.GetValueOfInt(drProductionLine[j]["M_PRODUCTIONLINE_ID"]), Get_Trx()); // Create New record of Production line with Reverse Entry toProdline = new X_M_ProductionLine(production.GetCtx(), 0, production.Get_Trx()); try { toProdline.Set_TrxName(production.Get_Trx()); PO.CopyValues(fromProdline, toProdline, fromProdPlan.GetAD_Client_ID(), fromProdPlan.GetAD_Org_ID()); toProdline.SetMovementQty(Decimal.Negate(toProdline.GetMovementQty())); toProdline.SetPlannedQty(Decimal.Negate(toProdline.GetPlannedQty())); toProdline.SetM_Production_ID(productionTo.GetM_Production_ID()); toProdline.SetM_ProductionPlan_ID(toProdPlan.GetM_ProductionPlan_ID()); toProdline.SetM_ProductContainer_ID(fromProdline.GetM_ProductContainer_ID()); // bcz not a copy record toProdline.SetReversalDoc_ID(fromProdline.GetM_ProductionLine_ID()); //maintain refernce of Orignal record on reversed record toProdline.SetProcessed(false); if (!CheckQtyAvailablity(GetCtx(), toProdline.GetM_Warehouse_ID(), toProdline.GetM_Locator_ID(), toProdline.GetM_ProductContainer_ID(), toProdline.GetM_Product_ID(), toProdline.GetM_AttributeSetInstance_ID(), toProdline.GetMovementQty(), Get_Trx())) { production.Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); if (!string.IsNullOrEmpty(pp.GetName())) { throw new Exception("Could not create Production line reverse entry, " + pp.GetName()); } else { throw new Exception("Could not create Production line reverse entry"); } } if (!toProdline.Save(production.Get_Trx())) { production.Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); _log.Log(Level.SEVERE, "Could Not create Production Line reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); throw new Exception("Could not create Production line reverse entry"); } else { // Create New record of Production line Policy (Material Policy) with Reverse Entry sql.Clear(); sql.Append(@"INSERT INTO M_ProductionLineMA ( AD_CLIENT_ID, AD_ORG_ID , CREATED , CREATEDBY , ISACTIVE , UPDATED , UPDATEDBY , M_PRODUCTIONLINE_ID , M_ATTRIBUTESETINSTANCE_ID , MMPOLICYDATE , M_PRODUCTCONTAINER_ID, MOVEMENTQTY ) (SELECT AD_CLIENT_ID, AD_ORG_ID , sysdate , CREATEDBY , ISACTIVE , sysdate , UPDATEDBY , " + toProdline.GetM_ProductionLine_ID() + @" , M_ATTRIBUTESETINSTANCE_ID , MMPOLICYDATE , M_PRODUCTCONTAINER_ID, -1 * MOVEMENTQTY FROM M_ProductionLineMA WHERE M_ProductionLine_ID = " + fromProdline.GetM_ProductionLine_ID() + @" ) "); int no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx()); _log.Info("No of records saved on Meterial Policy against Production line ID : " + toProdline.GetM_ProductionLine_ID() + " are : " + no); } } catch (Exception ex) { _log.Info("Error Occured during Production Reverse " + ex.ToString()); if (dsProductionLine != null) { dsProductionLine.Dispose(); } if (dsProductionPlan != null) { dsProductionPlan.Dispose(); } return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result)); } } } } #endregion } } catch (Exception ex) { _log.Info("Error Occured during Production Reverse " + ex.ToString()); if (dsProductionLine != null) { dsProductionLine.Dispose(); } if (dsProductionPlan != null) { dsProductionPlan.Dispose(); } return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result)); } } } #endregion result = productionTo.GetName(); } // To check weather future date records are available in Transaction window // this check implement after "SetCompletedDocumentNo" function, because this function overwrit movement date string _processMsg = MTransaction.CheckFutureDateRecord(productionTo.GetMovementDate(), productionTo.Get_TableName(), productionTo.GetM_Production_ID(), production.Get_Trx()); if (!string.IsNullOrEmpty(_processMsg)) { production.Get_Trx().Rollback(); _log.Log(Level.SEVERE, "Could Not create Production reverse entry. Future Date record Exists - " + _processMsg); throw new Exception("Could not create Production reverse entry"); } //set Reversed as True productionTo.SetIsReversed(true); productionTo.SetIsCreated(true); if (!productionTo.Save(production.Get_Trx())) { production.Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); _log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); throw new Exception("Could not create Production reverse entry"); } //Set reversed as true, Reverse Refernce on Orignal Document production.SetIsReversed(true); production.SetM_Ref_Production(productionTo.GetM_Production_ID()); if (!production.Save(production.Get_Trx())) { production.Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); _log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); throw new Exception("Could not create Production reverse entry"); } } catch (Exception ex) { _log.Info("Error Occured during Production Reverse " + ex.ToString()); if (dsProductionLine != null) { dsProductionLine.Dispose(); } if (dsProductionPlan != null) { dsProductionPlan.Dispose(); } return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result)); } } return(Msg.GetMsg(GetCtx(), "DocumentReversedSuccessfully" + result)); }
/// <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); }