/// <summary> /// Detail Constructor. /// Locator/Product/AttributeSetInstance must be unique /// </summary> /// <param name="inventory">parent</param> /// <param name="M_Locator_ID">locator</param> /// <param name="M_Product_ID">product</param> /// <param name="M_AttributeSetInstance_ID">instance</param> /// <param name="qtyBook">book value</param> /// <param name="qtyCount">count value</param> public MInventoryLine(MInventory inventory, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, Decimal qtyBook, Decimal qtyCount) : this(inventory.GetCtx(), 0, inventory.Get_TrxName()) { if (inventory.Get_ID() == 0) { throw new ArgumentException("Header not saved"); } _parent = inventory; SetM_Inventory_ID(inventory.GetM_Inventory_ID()); // Parent SetClientOrg(inventory.GetAD_Client_ID(), inventory.GetAD_Org_ID()); SetM_Locator_ID(M_Locator_ID); // FK SetM_Product_ID(M_Product_ID); // FK SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); // // if (qtyBook != null) SetQtyBook(qtyBook); if (qtyCount != null && Env.Signum(qtyCount) != 0) { SetQtyCount(qtyCount); } _isManualEntry = false; }
public bool SetProductQty1(int recordID, string keyColName, List <string> product, List <string> attribute, List <string> qty, List <string> oline_ID, List <string> locID, int LocToID, List <string> AssetID, int lineID, VAdvantage.Utility.Ctx ctx) { if (keyColName.ToUpper().Trim() == "M_MOVEMENT_ID") { MMovement inv = new MMovement(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MMovementLine invline = new MMovementLine(ctx, lineID, null); invline.SetAD_Client_ID(inv.GetAD_Client_ID()); invline.SetAD_Org_ID(inv.GetAD_Org_ID()); invline.SetM_Locator_ID(Util.GetValueOfInt(locID[i])); invline.SetM_LocatorTo_ID(LocToID); invline.SetM_Product_ID(Util.GetValueOfInt(product[i])); invline.SetMovementQty(Util.GetValueOfDecimal(qty[i])); invline.SetM_Movement_ID(recordID); if (Util.GetValueOfInt(AssetID[i]) > 0) { invline.SetA_Asset_ID(Util.GetValueOfInt(AssetID[i])); } if (Util.GetValueOfInt(oline_ID[i]) > 0) { invline.SetM_RequisitionLine_ID(Util.GetValueOfInt(oline_ID[i])); } if (Util.GetValueOfInt(attribute[i]) != 0) { invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } if (!invline.Save()) { } } } else if (keyColName.ToUpper().Trim() == "M_INVENTORY_ID") { MInventory inv = new MInventory(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MInventoryLine invline = new MInventoryLine(ctx, lineID, null); invline.SetAD_Client_ID(inv.GetAD_Client_ID()); invline.SetAD_Org_ID(inv.GetAD_Org_ID()); invline.SetM_Product_ID(Util.GetValueOfInt(product[i])); invline.SetQtyInternalUse(Util.GetValueOfDecimal(qty[i])); invline.SetC_Charge_ID(Util.GetValueOfInt(AssetID[i])); invline.SetM_Inventory_ID(recordID); invline.SetIsInternalUse(true); invline.SetM_RequisitionLine_ID(Util.GetValueOfInt(oline_ID[i])); if (Util.GetValueOfInt(attribute[i]) != 0) { invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } else { invline.SetM_AttributeSetInstance_ID(0); } if (!invline.Save()) { } } } return(true); }
public bool SetProductQty(int recordID, string keyColName, List <string> product, List <string> attribute, List <string> qty, List <string> qtybook, List <string> oline_ID, int ordID, List <string> locID, int lineID, VAdvantage.Utility.Ctx ctx) { if (keyColName.ToUpper().Trim() == "C_ORDER_ID") { MOrder ord = new MOrder(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MOrderLine oline = new MOrderLine(ctx, lineID, null); oline.SetAD_Client_ID(ord.GetAD_Client_ID()); oline.SetAD_Org_ID(ord.GetAD_Org_ID()); oline.SetM_Product_ID(Util.GetValueOfInt(product[i])); oline.SetQty(Util.GetValueOfDecimal(qty[i])); oline.SetC_Order_ID(recordID); if (Util.GetValueOfInt(attribute[i]) != 0) { oline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } if (!ord.IsSOTrx()) { MProduct pro = new MProduct(ctx, oline.GetM_Product_ID(), null); String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + oline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + ord.GetC_BPartner_ID(); int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom)); if (pro.GetC_UOM_ID() != 0) { if (pro.GetC_UOM_ID() != uom && uom != 0) { decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + oline.GetM_Product_ID() + " AND IsActive='Y'")); if (Res > 0) { oline.SetQtyEntered(oline.GetQtyEntered() * Res); //OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty); } else { decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'")); if (res > 0) { oline.SetQtyEntered(oline.GetQtyEntered() * res); //OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty); } } oline.SetC_UOM_ID(uom); } else { oline.SetC_UOM_ID(pro.GetC_UOM_ID()); } } } if (!oline.Save()) { } } } else if (keyColName.ToUpper().Trim() == "C_INVOICE_ID") { MInvoice inv = new MInvoice(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MInvoiceLine invline = new MInvoiceLine(ctx, lineID, null); invline.SetAD_Client_ID(inv.GetAD_Client_ID()); invline.SetAD_Org_ID(inv.GetAD_Org_ID()); invline.SetM_Product_ID(Util.GetValueOfInt(product[i])); invline.SetQty(Util.GetValueOfDecimal(qty[i])); invline.SetC_Invoice_ID(recordID); if (Util.GetValueOfInt(attribute[i]) != 0) { invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } if (!inv.IsSOTrx()) { MProduct pro = new MProduct(ctx, invline.GetM_Product_ID(), null); String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + invline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + inv.GetC_BPartner_ID(); int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom)); if (pro.GetC_UOM_ID() != 0) { if (pro.GetC_UOM_ID() != uom && uom != 0) { decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + invline.GetM_Product_ID() + " AND IsActive='Y'")); if (Res > 0) { invline.SetQtyEntered(invline.GetQtyEntered() * Res); //OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty); } else { decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'")); if (res > 0) { invline.SetQtyEntered(invline.GetQtyEntered() * res); //OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty); } } invline.SetC_UOM_ID(uom); } else { invline.SetC_UOM_ID(pro.GetC_UOM_ID()); } } } if (!invline.Save()) { } } } else if (keyColName.ToUpper().Trim() == "M_INOUT_ID") { MInOut inv = new MInOut(ctx, recordID, null); if (ordID > 0) { inv.SetC_Order_ID(ordID); } if (inv.Save()) { for (int i = 0; i < product.Count; i++) { MInOutLine ioline = new MInOutLine(ctx, lineID, null); ioline.SetAD_Client_ID(inv.GetAD_Client_ID()); ioline.SetAD_Org_ID(inv.GetAD_Org_ID()); ioline.SetM_Product_ID(Util.GetValueOfInt(product[i])); ioline.SetQty(Util.GetValueOfDecimal(qty[i])); ioline.SetM_InOut_ID(recordID); ioline.SetC_OrderLine_ID(Util.GetValueOfInt(oline_ID[i])); ioline.SetM_Locator_ID(Util.GetValueOfInt(locID[i])); if (Util.GetValueOfInt(attribute[i]) != 0) { ioline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } if (!inv.IsSOTrx()) { MProduct pro = new MProduct(ctx, ioline.GetM_Product_ID(), null); String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + ioline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + inv.GetC_BPartner_ID(); int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom)); if (pro.GetC_UOM_ID() != 0) { if (pro.GetC_UOM_ID() != uom && uom != 0) { decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + ioline.GetM_Product_ID() + " AND IsActive='Y'")); if (Res > 0) { ioline.SetQtyEntered(ioline.GetQtyEntered() * Res); //OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty); } else { decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'")); if (res > 0) { ioline.SetQtyEntered(ioline.GetQtyEntered() * res); //OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty); } } ioline.SetC_UOM_ID(uom); } else { ioline.SetC_UOM_ID(pro.GetC_UOM_ID()); } } } if (!ioline.Save()) { } } } } else if (keyColName.ToUpper().Trim() == "M_PACKAGE_ID") { MPackage pkg = new MPackage(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MPackageLine mline = new MPackageLine(ctx, lineID, null); mline.SetAD_Client_ID(pkg.GetAD_Client_ID()); mline.SetAD_Org_ID(pkg.GetAD_Org_ID()); mline.SetM_Product_ID(Util.GetValueOfInt(product[i])); mline.SetQty(Util.GetValueOfDecimal(qty[i])); if (Util.GetValueOfInt(oline_ID[i]) > 0) { mline.SetM_MovementLine_ID(Util.GetValueOfInt(oline_ID[i])); MMovementLine mov = new MMovementLine(ctx, Util.GetValueOfInt(oline_ID[i]), null); mline.SetDTD001_TotalQty(mov.GetMovementQty()); } if (Util.GetValueOfInt(attribute[i]) != 0) { mline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } mline.SetM_Package_ID(recordID); if (!mline.Save()) { } } } else if (keyColName.ToUpper().Trim() == "M_INVENTORY_ID") { MInventory inv = new MInventory(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MInventoryLine invline = new MInventoryLine(ctx, lineID, null); invline.SetAD_Client_ID(inv.GetAD_Client_ID()); invline.SetAD_Org_ID(inv.GetAD_Org_ID()); invline.SetM_Locator_ID(Util.GetValueOfInt(locID[i])); invline.SetM_Product_ID(Util.GetValueOfInt(product[i])); invline.SetQtyCount(Util.GetValueOfDecimal(qty[i])); invline.SetQtyBook(Util.GetValueOfDecimal(qtybook[i])); invline.SetAsOnDateCount(Util.GetValueOfDecimal(qty[i])); invline.SetOpeningStock(Util.GetValueOfDecimal(qtybook[i])); invline.SetM_Inventory_ID(recordID); if (Util.GetValueOfInt(attribute[i]) != 0) { invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } else { invline.SetM_AttributeSetInstance_ID(0); } if (!invline.Save()) { } } } return(true); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { 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); } // 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=" + _inventory.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 = DataBase.DB.ExecuteQuery(sqlQry, null, Get_TrxName()); // log.Fine("'0' Inserted #" + no); //} //string sqlstock = "SELECT Count(*) FROM user_procedures WHERE object_type='FUNCTION' AND OBJECT_NAME='GETSTOCKOFWAREHOUSE'"; //int countStock = Util.GetValueOfInt(DB.ExecuteScalar(sqlstock, null, null)); //if (countStock > 0) //{ sql = new StringBuilder( @"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty FROM (SELECT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, SUM(t.CurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) 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() + @" GROUP BY t.M_Product_ID,l.M_Warehouse_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID) GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID ) SELECT 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'"); // if (_m_Locator_ID != 0) { sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID); } //else // sql.Append(" AND s.m_Locator_Id IN (SELECT M_Locator_ID FROM M_Locator WHERE M_Warehouse_ID=" + _inventory.GetM_Warehouse_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))"); } // + " AND il.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID)"); // //sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product 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() + " )", 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 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]); //Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]); int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]); 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); } 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 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]); //Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]); int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]); 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); if (insertQry != "") { insertSql.Append(insertQry); } } ds.Dispose(); insertSql.Append(" END;"); int no = DB.ExecuteQuery(insertSql.ToString(), 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); } // } // else // { // 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=" + _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))"); // sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product // // // IDataReader idr = null; // try // { // idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); // while (idr.Read()) // { // decimal currentQty = 0; // string query = "", qry = ""; // int result = 0; // int M_Product_ID = Utility.Util.GetValueOfInt(idr[0]); // int M_Locator_ID = Utility.Util.GetValueOfInt(idr[1]); // int M_AttributeSetInstance_ID = Utility.Util.GetValueOfInt(idr[2]); // Decimal qtyOnHand = Utility.Util.GetValueOfDecimal(idr[3]); // int M_AttributeSet_ID = Utility.Util.GetValueOfInt(idr[4]); // 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)); // } // } // int compare = currentQty.CompareTo(Env.ZERO); // if (_qtyRange == null // || (_qtyRange.Equals(">") && compare > 0) // || (_qtyRange.Equals("<") && compare < 0) // || (_qtyRange.Equals("=") && compare == 0) // || (_qtyRange.Equals("N") && compare != 0)) // { // count += CreateInventoryLine(M_Locator_ID, M_Product_ID, // M_AttributeSetInstance_ID, currentQty, currentQty, M_AttributeSet_ID); // } // } // idr.Close(); // } // catch (Exception e) // { // if (idr != null) // { // idr.Close(); // } // 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 "@M_InventoryLine_ID@ - #" + count; return("Physical Inventory Created"); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { 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 " + "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 " + "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 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 t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID, SUM(t.CurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) 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() + @" GROUP BY t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_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 + " )", 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 + " <===== "); } } } 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 + noMA) + " --> @InventoryProductMultiple@"); } //return "@M_InventoryLine_ID@ - #" + (no + noMA); return("Physical Inventory Updated"); }
/// <summary> /// Create/Add to Inventory Line Query /// </summary> /// <param name="M_Locator_ID">locator</param> /// <param name="M_Product_ID">product</param> /// <param name="M_AttributeSetInstance_ID">asi</param> /// <param name="qtyOnHand">quantity</param> /// <param name="M_AttributeSet_ID">attribute set</param> /// <returns>lines added</returns> private string InsertInventoryLine(int M_Locator_ID, int M_Product_ID, int lineNo, int M_AttributeSetInstance_ID, Decimal qtyOnHand, int M_AttributeSet_ID, int container_ID) { MInventoryLine line = new MInventoryLine(GetCtx(), 0, Get_Trx()); int line_ID = DB.GetNextID(GetCtx(), "M_InventoryLine", Get_Trx()); string qry = "select m_warehouse_id from m_locator where m_locator_id=" + M_Locator_ID; int M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_Trx())); MWarehouse wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID); if (wh.IsDisallowNegativeInv() == true) { if (qtyOnHand < 0) { return(""); } } MProduct product = MProduct.Get(GetCtx(), M_Product_ID); if (product != null) { int precision = product.GetUOMPrecision(); if (Env.Signum(qtyOnHand) != 0) { qtyOnHand = Decimal.Round(qtyOnHand, precision, MidpointRounding.AwayFromZero); } } string sql = @"INSERT INTO M_InventoryLine (AD_Client_ID, AD_Org_ID,IsActive, Created, CreatedBy, Updated, UpdatedBy, Line, M_Inventory_ID, M_InventoryLine_ID, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, QtyBook, QtyCount, OpeningStock, AsOnDateCount, DifferenceQty, AdjustmentType"; if (line.Get_ColumnIndex("C_UOM_ID") > 0) { sql += ", QtyEntered, C_UOM_ID"; } if (line.Get_ColumnIndex("IsFromProcess") > 0) { sql += ",IsFromProcess"; } if (line.Get_ColumnIndex("M_ProductContainer_ID") > 0) { sql += ",M_ProductContainer_ID "; } sql += " ) VALUES ( " + _inventory.GetAD_Client_ID() + "," + _inventory.GetAD_Org_ID() + ",'Y'," + GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," + GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," + lineNo + "," + _m_Inventory_ID + "," + line_ID + "," + M_Locator_ID + "," + M_Product_ID + "," + M_AttributeSetInstance_ID + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + 0 + ",'A'"; if (line.Get_ColumnIndex("C_UOM_ID") > 0) { sql += "," + qtyOnHand + "," + product.GetC_UOM_ID(); } if (line.Get_ColumnIndex("IsFromProcess") > 0) { sql += ",'Y'"; } if (line.Get_ColumnIndex("M_ProductContainer_ID") > 0) { sql += "," + container_ID; } string insertQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + ")'); exception when others then null; END;"; return(insertQry); }
/// <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"); }