Example #1
0
        /// <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)
        {
            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";
            }
            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'";
            }
            string insertQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + ")'); exception when others then null; END;";

            return(insertQry);
        }
Example #2
0
        /// <summary>
        /// Check Qty Availablity in warehouse when Disallow Negative Inventory = true
        /// </summary>
        /// <param name="ctx">context</param>
        /// <param name="M_Warehouse_ID">warehouse refernce - for checking disallow negative inventory or not</param>
        /// <param name="M_Locator_ID">locator ref - in which locator we have to check stock</param>
        /// <param name="M_Product_ID">product ref -- to which product, need to check stock</param>
        /// <param name="M_AttributeSetInstance_ID">AttributeSetInstance -- to which ASI, need to check stock</param>
        /// <param name="MovementQty">qty to be impacted</param>
        /// <param name="trxName">system transaction</param>
        /// <returns>TRUE/False</returns>
        private bool CheckQtyAvailablity(Ctx ctx, int M_Warehouse_ID, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, Decimal?MovementQty, Trx trxName)
        {
            MWarehouse wh      = MWarehouse.Get(ctx, M_Warehouse_ID);
            MProduct   product = null;

            if (wh.IsDisallowNegativeInv() && M_Product_ID > 0)
            {
                product = MProduct.Get(ctx, M_Product_ID);
                string qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)),0) AS QtyOnHand FROM M_Storage where m_locator_id=" + M_Locator_ID + @" and m_product_id=" + M_Product_ID;
                qry += " AND NVL(M_AttributeSetInstance_ID, 0) =" + M_AttributeSetInstance_ID;
                Decimal?OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry, null, trxName));
                if (OnHandQty + MovementQty < 0)
                {
                    log.SaveError("Info", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
                    return(false);
                }
            }
            return(true);
        }
Example #3
0
        /// <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="currentQty">quantity</param>
        /// <param name="M_AttributeSet_ID">attribute set</param>
        /// <returns>lines added</returns>
        private string UpdateInventoryLine(int M_InventoryLine_ID, int M_Product_ID, int M_Locator_ID, Decimal currentQty, string AdjustType, Decimal AsOnDateCount, Decimal DiffQty)
        {
            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 (currentQty < 0)
                {
                    return("");
                }
            }
            MProduct product = MProduct.Get(GetCtx(), M_Product_ID);

            if (product != null)
            {
                int precision = product.GetUOMPrecision();
                if (Env.Signum(currentQty) != 0)
                {
                    currentQty = Decimal.Round(currentQty, precision, MidpointRounding.AwayFromZero);
                }
            }
            if (AdjustType == "A")
            {
                DiffQty = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, AsOnDateCount));
            }
            else if (AdjustType == "D")
            {
                AsOnDateCount = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, DiffQty));
            }
            Decimal QtyCount = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, DiffQty));

            // Changes by Bharat on 02 Aug 2017 as issue given by Ravikant
            string sql = @"UPDATE M_InventoryLine SET QtyBook = " + currentQty + ",QtyCount = " + QtyCount + ",OpeningStock = " + currentQty + ",AsOnDateCount = " + AsOnDateCount +
                         ",DifferenceQty = " + DiffQty + " WHERE M_InventoryLine_ID = " + M_InventoryLine_ID;

            string updateQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + "'); exception when others then null; END;";

            return(updateQry);
        }
        protected override bool BeforeSave(bool newRecord)
        {
            // Movement Quantity can not be less than 0 when planned is greater than 0
            if (GetPlannedQty() > 0 && GetMovementQty() < 0)
            {
                log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbelesszero"));
                return(false);
            }

            // Movement Quantity can not be greater than 0 when planned is less than 0
            if (GetPlannedQty() < 0 && GetMovementQty() > 0)
            {
                log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbegrtzero"));
                return(false);
            }

            MProduct product = new MProduct(GetCtx(), GetM_Product_ID(), Get_TrxName());

            if (newRecord && product != null)
            {
                if (Util.GetValueOfString(product.Get_Value("IsSlopeOil")) == "True" && GetMovementQty() < 0)
                {
                    log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbelesszero"));
                    return(false);
                }
                if (Util.GetValueOfString(product.Get_Value("IsSlopeOil")) == "False" && GetMovementQty() > 0)
                {
                    log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "MovementQtyCantbegrtzero"));
                    return(false);
                }
            }

            X_M_ProductionPlan plan = new X_M_ProductionPlan(GetCtx(), GetM_ProductionPlan_ID(), Get_TrxName());

            if (Util.GetValueOfDecimal(plan.Get_Value("GOM01_ComponentLimit")) > 0 && GetPlannedQty() < 0)
            {
                decimal ComputedQty = (GetPlannedQty() * Util.GetValueOfDecimal(plan.Get_Value("GOM01_ComponentLimit"))) / 100;

                ComputedQty = decimal.Negate(GetPlannedQty() + ComputedQty);

                if (decimal.Negate(GetMovementQty()) > ComputedQty)
                {
                    log.SaveWarning("Warning", Msg.GetMsg(GetCtx(), "GOM01_QtyConsumption"));
                    return(false);
                }
            }

            // when warehouse disallow negative inventory is false then on hand qty can't be in negative
            wh = MWarehouse.Get(GetCtx(), GetM_Warehouse_ID());
            if (wh.IsDisallowNegativeInv() && GetM_Product_ID() > 0)
            {
                product = MProduct.Get(GetCtx(), GetM_Product_ID());
                string qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)),0) AS QtyOnHand FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID();
                //if (GetM_AttributeSetInstance_ID() != 0)
                //{
                qry += " AND NVL(M_AttributeSetInstance_ID , 0) =" + GetM_AttributeSetInstance_ID();
                //}
                Decimal?OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry));

                qry = @"SELECT NVL(SUM(MovementQty) , 0) FROM M_ProductionLine WHERE IsActive = 'Y' AND  M_Locator_ID=" + GetM_Locator_ID() + @" AND m_product_id=" + GetM_Product_ID() +
                      @" AND NVL(M_AttributeSetInstance_ID , 0) =" + GetM_AttributeSetInstance_ID() + @" AND M_Production_ID = " + GetM_Production_ID();
                if (!newRecord)
                {
                    qry += @" AND M_ProductionLine_ID <> " + GetM_ProductionLine_ID();
                }
                Decimal?moveQty = Convert.ToDecimal(DB.ExecuteScalar(qry));
                if ((OnHandQty + GetMovementQty() + moveQty) < 0)
                {
                    log.SaveError("", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
                    return(false);
                }
            }

            return(true);
        }
Example #5
0
        /// <summary>
        /// Before Save
        /// </summary>
        /// <param name="newRecord">new</param>
        /// <returns>true if can be saved</returns>
        protected override bool BeforeSave(bool newRecord)
        {
            Decimal    VA024_ProvisionPrice = 0;
            MInventory inventory            = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
            MProduct   product = MProduct.Get(GetCtx(), GetM_Product_ID());

            if (newRecord && _isManualEntry)
            {
                //	Product requires ASI
                if (GetM_AttributeSetInstance_ID() == 0)
                {
                    if (product.GetM_AttributeSet_ID() != 0)
                    {
                        MAttributeSet mas = MAttributeSet.Get(GetCtx(), product.GetM_AttributeSet_ID());
                        //uncomment by Amit on behalf of Mandeep 7-3-2016
                        //if (mas.IsInstanceAttribute()
                        //    && (mas.IsMandatory() || mas.IsMandatoryAlways()))
                        if (mas.IsMandatory() || mas.IsMandatoryAlways())
                        {
                            log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "M_AttributeSetInstance_ID"));
                            return(false);
                        }
                    }
                } //	No ASI
            }     //	new or manual

            // not to create Internal use Inventory with -ve qty -- but during reversal system will create record with -ve qty
            // duing reversal -- ReversalDoc_ID contain refernce o  orignal record id
            if (IsInternalUse() && Get_ColumnIndex("ReversalDoc_ID") > 0 && GetReversalDoc_ID() == 0 && GetQtyInternalUse() < 0)
            {
                log.SaveError("", Msg.GetMsg(GetCtx(), "VIS_CantbeNegative"));
                return(false);
            }

            //	Set Line No
            if (GetLine() == 0)
            {
                String sql = "SELECT COALESCE(MAX(Line),0)+10 AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + GetM_Inventory_ID();
                int    ii  = DB.GetSQLValue(Get_TrxName(), sql);
                SetLine(ii);
            }

            // SI_0644.1 : Enforce UOM Precision - Rounding Quantities
            if (newRecord || Is_ValueChanged("QtyInternalUse"))
            {
                SetQtyInternalUse(GetQtyInternalUse());
            }

            // change to set Converted Quantity in Internal Use Qty and AsonDateQty and difference qty if there is differnce in UOM of Base Product and UOM Selected on line
            if (newRecord || Is_ValueChanged("QtyEntered") || Is_ValueChanged("C_UOM_ID"))
            {
                Decimal?qty = Util.GetValueOfDecimal(Get_Value("QtyEntered"));
                if (product.GetC_UOM_ID() != Util.GetValueOfInt(Get_Value("C_UOM_ID")))
                {
                    qty = MUOMConversion.ConvertProductFrom(GetCtx(), GetM_Product_ID(), Util.GetValueOfInt(Get_Value("C_UOM_ID")), Util.GetValueOfDecimal(Get_Value("QtyEntered")));
                    if (IsInternalUse())
                    {
                        SetQtyInternalUse(qty);
                    }
                    else
                    {
                        SetAsOnDateCount(qty);
                        SetDifferenceQty(qty);
                    }
                }
            }

            // SI_0644 - As on date and difference should be according to the precision of UOM attached.
            if (newRecord || Is_ValueChanged("AsOnDateCount"))
            {
                if (product != null)
                {
                    int precision = product.GetUOMPrecision();
                    SetAsOnDateCount(Decimal.Round(GetAsOnDateCount(), precision, MidpointRounding.AwayFromZero));
                }
            }

            // SI_0644 - As on date and difference should be according to the precision of UOM attached.
            if (newRecord || Is_ValueChanged("DifferenceQty"))
            {
                if (product != null)
                {
                    int precision = product.GetUOMPrecision();
                    SetDifferenceQty(Decimal.Round(GetDifferenceQty(), precision, MidpointRounding.AwayFromZero));
                }
            }

            // SI_0682_1 Need to update the reserved qty on requisition line by internal use line save aslo and should work as work in inventory move.
            if (Env.IsModuleInstalled("DTD001_") && IsInternalUse())
            {
                qtyReserved = Util.GetValueOfDecimal(Get_ValueOld("QtyInternalUse"));
            }

            int    M_Warehouse_ID = 0; MWarehouse wh = null;
            string qry = "select m_warehouse_id from m_locator where m_locator_id=" + GetM_Locator_ID();

            M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_TrxName()));

            wh  = MWarehouse.Get(GetCtx(), M_Warehouse_ID);
            qry = "SELECT QtyOnHand FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID();
            if (GetM_AttributeSetInstance_ID() != 0)
            {
                qry += " AND M_AttributeSetInstance_ID=" + GetM_AttributeSetInstance_ID();
            }
            OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry, null, Get_TrxName()));
            // when record is in completed & closed stage - then no need to check qty availablity in warehouse
            if (wh.IsDisallowNegativeInv() == true &&
                (!(inventory.GetDocStatus() == "CO" || inventory.GetDocStatus() == "CL" ||
                   inventory.GetDocStatus() == "RE" || inventory.GetDocStatus() == "VO" || inventory.GetDocStatus() == "IP")))
            {
                #region DisallowNegativeInv = True
                if (!IsInternalUse() && GetDifferenceQty() > 0)
                {
                    if ((OnHandQty - GetDifferenceQty()) < 0)
                    {
                        log.SaveError("Info", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
                        return(false);
                    }
                }
                else if (IsInternalUse())
                {
                    if ((OnHandQty - GetQtyInternalUse()) < 0)
                    {
                        log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
                        return(false);
                    }
                }
                #endregion
            }
            //	Enforce Qty UOM
            if (newRecord || Is_ValueChanged("QtyCount"))
            {
                SetQtyCount(GetQtyCount());
            }

            //	InternalUse Inventory
            if (IsInternalUse() && Env.Signum(GetQtyInternalUse()) == 0)
            {
                log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "QtyInternalUse"));
                return(false);
            }
            if (Env.Signum(GetQtyInternalUse()) != 0)
            {
                if (!INVENTORYTYPE_ChargeAccount.Equals(GetInventoryType()))
                {
                    SetInventoryType(INVENTORYTYPE_ChargeAccount);
                }
                //
                if (GetC_Charge_ID() == 0)
                {
                    log.SaveError("Error", Msg.GetMsg(GetCtx(), "InternalUseNeedsCharge"));
                    return(false);
                }
            }
            else if (INVENTORYTYPE_ChargeAccount.Equals(GetInventoryType()))
            {
                if (GetC_Charge_ID() == 0)
                {
                    log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "C_Charge_ID"));
                    return(false);
                }
            }
            else if (GetC_Charge_ID() != 0)
            {
                SetC_Charge_ID(0);
            }

            //	Set AD_Org to parent if not charge
            if (GetC_Charge_ID() == 0)
            {
                SetAD_Org_ID(GetParent().GetAD_Org_ID());
            }

            // By Amit for Obsolete Inventory - 25-May-2016
            if (Env.IsModuleInstalled("VA024_"))
            {
                //MInventory inventory = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
                //shipment and Return to vendor
                if (inventory.IsInternalUse() || (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0))
                {
                    try
                    {
                        string qry1 = @"SELECT  SUM(o.VA024_UnitPrice)   FROM VA024_t_ObsoleteInventory o 
                                  WHERE o.IsActive = 'Y' AND  o.M_Product_ID = " + GetM_Product_ID() + @" and 
                                  ( o.M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID() + @" OR o.M_AttributeSetInstance_ID IS NULL )" +
                                      " AND o.AD_Org_ID = " + GetAD_Org_ID();
                        //+" AND M_Warehouse_ID = " + inventory.GetM_Warehouse_ID();
                        VA024_ProvisionPrice = Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx()));
                        if (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0)
                        {
                            SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * (GetQtyBook() - GetQtyCount())));
                        }
                        else
                        {
                            SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * GetQtyInternalUse()));
                        }

                        if (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0)
                        {
                            qry1 = @"SELECT (ct.currentcostprice - " + VA024_ProvisionPrice + ") * " + (GetQtyBook() - GetQtyCount());
                        }
                        else
                        {
                            qry1 = @"SELECT (ct.currentcostprice - " + VA024_ProvisionPrice + ") * " + GetQtyInternalUse();
                        }
                        qry1 += @" FROM m_product p  INNER JOIN va024_t_obsoleteinventory oi ON p.m_product_id = oi.M_product_ID
                                 INNER JOIN m_product_category pc ON pc.m_product_category_ID = p.m_product_category_ID
                                 INNER JOIN AD_client c ON c.AD_Client_ID = p.Ad_Client_ID   INNER JOIN AD_ClientInfo ci  ON c.AD_Client_ID = ci.Ad_Client_ID
                                 INNER JOIN m_cost ct ON ( p.M_Product_ID     = ct.M_Product_ID  AND ci.C_AcctSchema1_ID = ct.C_AcctSchema_ID )
                                 INNER JOIN c_acctschema asch  ON (asch.C_AcctSchema_ID = ci.C_AcctSchema1_ID)
                                 INNER JOIN va024_obsoleteinvline oil ON oil.va024_obsoleteinvline_ID = oi.va024_obsoleteinvline_ID ";
                        qry1 += @"    WHERE ct.AD_Org_ID =  
                          CASE WHEN ( pc.costinglevel IS NOT NULL AND pc.costinglevel = 'O') THEN " + GetAD_Org_ID() + @" 
                               WHEN ( pc.costinglevel IS NOT NULL AND (pc.costinglevel  = 'C' OR pc.costinglevel = 'B')) THEN 0 
                               WHEN (pc.costinglevel IS NULL AND asch.costinglevel  = 'O') THEN " + GetAD_Org_ID() + @" 
                               WHEN ( pc.costinglevel IS NULL AND (asch.costinglevel  = 'C' OR asch.costinglevel   = 'B')) THEN 0  END
                          AND ct.m_costelement_id =  
                          CASE WHEN ( pc.costingmethod IS NOT NULL AND pc.costingmethod  != 'C') THEN  (SELECT MIN(m_costelement_id)  FROM m_costelement  
                                     WHERE m_costelement.costingmethod =pc.costingmethod  AND m_costelement.Ad_Client_ID  = oi.ad_client_id  ) 
                                WHEN ( pc.costingmethod IS NOT NULL AND pc.costingmethod = 'C' ) THEN  pc.m_costelement_id 
                                WHEN ( pc.costingmethod IS NULL AND asch.costingmethod  != 'C') THEN  (SELECT MIN(m_costelement_id)  FROM m_costelement 
                                     WHERE m_costelement.costingmethod = asch.costingmethod  AND m_costelement.Ad_Client_ID  = oi.ad_client_id  )
                                WHEN ( pc.costingmethod IS NULL AND asch.costingmethod  = 'C') THEN asch.m_costelement_id  END 
                         AND NVL(ct.M_Attributesetinstance_ID , 0) =  
                         CASE WHEN ( pc.costinglevel IS NOT NULL AND pc.costinglevel = 'B') THEN " + GetM_AttributeSetInstance_ID() + @" 
                              WHEN ( pc.costinglevel IS NOT NULL AND (pc.costinglevel  = 'C' OR pc.costinglevel = 'O')) THEN 0 
                              WHEN ( pc.costinglevel IS NULL AND asch.costinglevel  = 'B') THEN " + GetM_AttributeSetInstance_ID() + @"
                              WHEN ( pc.costinglevel IS NULL AND (asch.costinglevel  = 'C' OR asch.costinglevel   = 'O')) THEN 0  END 
                         AND p.M_Product_ID = " + GetM_Product_ID();
                        SetVA024_CostPrice(Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx())));
                    }
                    catch { }
                }
            }

            return(true);
        }