Example #1
0
        /// <summary>
        /// Create Material Allocations for new Instances
        /// </summary>
        /// <param name="updateQtyBooked"></param>
        public void CreateMA(bool updateQtyBooked)
        {
            VAdvantage.Model.MInventoryLine INVLine = new VAdvantage.Model.MInventoryLine(GetCtx(), GetM_InventoryLine_ID(), Get_TrxName());
            int delMA = MInventoryLineMA.DeleteInventoryLineMA(GetM_InventoryLine_ID(), Get_TrxName());

            log.Info("DeletedMA=" + delMA);

            MStorage[] storages = MStorage.GetAll(GetCtx(), GetM_Product_ID(),
                                                  GetM_Locator_ID(), Get_TrxName());
            bool allZeroASI = true;

            for (int i = 0; i < storages.Length; i++)
            {
                if (storages[i].GetM_AttributeSetInstance_ID() != 0)
                {
                    allZeroASI = false;
                    break;
                }
            }
            if (allZeroASI)
            {
                return;
            }

            MInventoryLineMA ma  = null;
            Decimal          sum = Env.ZERO;

            for (int i = 0; i < storages.Length; i++)
            {
                MStorage storage = storages[i];
                // nnayak - ignore negative layers
                if (Env.Signum(storage.GetQtyOnHand()) <= 0)
                {
                    continue;
                }
                if (ma != null &&
                    ma.GetM_AttributeSetInstance_ID() == storage.GetM_AttributeSetInstance_ID())
                {
                    ma.SetMovementQty(Decimal.Add(ma.GetMovementQty(), storage.GetQtyOnHand()));
                }
                else
                {
                    ma = new MInventoryLineMA(INVLine,
                                              storage.GetM_AttributeSetInstance_ID(), storage.GetQtyOnHand());
                }
                if (!ma.Save())
                {
                    ;
                }
                sum = Decimal.Add(sum, storage.GetQtyOnHand());
            }
            if (updateQtyBooked && sum.CompareTo(GetQtyBook()) != 0)
            {
                log.Warning("QtyBook=" + GetQtyBook() + " corrected to Sum of MA=" + sum);
                SetQtyBook(sum);
            }
        }
Example #2
0
        /// <summary>
        /// Update Inventory Lines With Material Allocation
        /// </summary>
        /// <returns>no update</returns>
        private int UpdateWithMA()
        {
            int no = 0;
            //
            String sql = "SELECT * FROM M_InventoryLine WHERE M_Inventory_ID=@iid AND COALESCE(M_AttributeSetInstance_ID,0)=0 ";

            try
            {
                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@iid", _m_Inventory_ID);
                DataSet ds = DataBase.DB.ExecuteDataset(sql, param, null);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MInventoryLine   il       = new MInventoryLine(GetCtx(), dr, Get_TrxName());
                    Decimal          onHand   = Env.ZERO;
                    MStorage[]       storages = MStorage.GetAll(GetCtx(), il.GetM_Product_ID(), il.GetM_Locator_ID(), Get_TrxName());
                    MInventoryLineMA ma       = null;
                    for (int i = 0; i < storages.Length; i++)
                    {
                        MStorage storage = storages[i];
                        if (Env.Signum(storage.GetQtyOnHand()) == 0)
                        {
                            continue;
                        }
                        onHand = Decimal.Add(onHand, storage.GetQtyOnHand());
                        //	No ASI
                        if (storage.GetM_AttributeSetInstance_ID() == 0 &&
                            storages.Length == 1)
                        {
                            continue;
                        }
                        //	Save ASI
                        ma = new MInventoryLineMA(il,
                                                  storage.GetM_AttributeSetInstance_ID(), storage.GetQtyOnHand());
                        if (!ma.Save())
                        {
                            ;
                        }
                    }
                    il.SetQtyBook(onHand);
                    il.SetQtyCount(onHand);
                    if (il.Save())
                    {
                        no++;
                    }
                }
            }
            catch (Exception e)
            {
                log.Log(Level.SEVERE, sql, e);
            }
            //
            log.Info("#" + no);
            return(no);
        }
Example #3
0
        /// <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");
        }
Example #4
0
        /// <summary>
        /// Process
        /// </summary>
        /// <returns>info</returns>
        protected override String DoIt()
        {
            log.Info("M_Inventory_ID=" + _m_Inventory_ID);
            MInventory 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())
                    {
                    }
                }

                //sql = "UPDATE M_InventoryLine "
                //    + "SET QtyCount = QtyBook - NVL(DifferenceQty,0),OpeningStock = " + currentQty
                //    + "WHERE M_Inventory_ID=" + _m_Inventory_ID;
                //no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
                //log.Info("Inventory Adjustment =" + no);
            }

            inventory.SetIsAdjusted(true);
            if (!inventory.Save())
            {
            }
            if (multiple > 0)
            {
                return("@M_InventoryLine_ID@ - #" + (no + noMA) + " --> @InventoryProductMultiple@");
            }

            return("@M_InventoryLine_ID@ - #" + (no + noMA));
        }
Example #5
0
        /// <summary>
        /// Create/Add to Inventory Line
        /// </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 int CreateInventoryLine(int M_Locator_ID, int M_Product_ID,
                                        int M_AttributeSetInstance_ID, Decimal qtyOnHand, Decimal currentQty, int M_AttributeSet_ID)
        {
            Boolean oneLinePerASI = false;

            if (M_AttributeSet_ID != 0)
            {
                MAttributeSet mas = MAttributeSet.Get(GetCtx(), M_AttributeSet_ID);
                oneLinePerASI = mas.IsInstanceAttribute();
            }
            if (oneLinePerASI)
            {
                MInventoryLine line = new MInventoryLine(_inventory, M_Locator_ID,
                                                         M_Product_ID, M_AttributeSetInstance_ID,
                                                         qtyOnHand, qtyOnHand); //	book/count
                line.SetOpeningStock(currentQty);
                if (line.Save())
                {
                    return(1);
                }
                return(0);
            }

            if (Env.Signum(qtyOnHand) == 0)
            {
                M_AttributeSetInstance_ID = 0;
            }

            if (_line != null &&
                _line.GetM_Locator_ID() == M_Locator_ID &&
                _line.GetM_Product_ID() == M_Product_ID)
            {
                if (Env.Signum(qtyOnHand) == 0)
                {
                    return(0);
                }
                //	Same ASI (usually 0)
                if (_line.GetM_AttributeSetInstance_ID() == M_AttributeSetInstance_ID)
                {
                    _line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
                    _line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
                    _line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
                    _line.Save();
                    return(0);
                }
                //	Save Old Line info
                else if (_line.GetM_AttributeSetInstance_ID() != 0)
                {
                    MInventoryLineMA ma = new MInventoryLineMA(_line,
                                                               _line.GetM_AttributeSetInstance_ID(), _line.GetQtyBook());
                    if (!ma.Save())
                    {
                        ;
                    }
                }
                _line.SetM_AttributeSetInstance_ID(0);
                _line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
                _line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
                _line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
                _line.Save();
                //
                MInventoryLineMA ma1 = new MInventoryLineMA(_line, M_AttributeSetInstance_ID, qtyOnHand);
                if (!ma1.Save())
                {
                    ;
                }
                return(0);
            }
            //	new line
            _line = new MInventoryLine(_inventory, M_Locator_ID, M_Product_ID,
                                       M_AttributeSetInstance_ID, qtyOnHand, qtyOnHand); //	book/count
            _line.SetOpeningStock(currentQty);
            if (_line.Save())
            {
                return(1);
            }
            return(0);
        }
Example #6
0
        /// <summary>
        /// Create/Add to Inventory Line
        /// </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 int CreateInventoryLine(int M_Locator_ID, int M_Product_ID,
                                        int M_AttributeSetInstance_ID, Decimal qtyOnHand, Decimal currentQty, int M_AttributeSet_ID)
        {
            Boolean oneLinePerASI = false;

            if (M_AttributeSet_ID != 0)
            {
                MAttributeSet mas = MAttributeSet.Get(GetCtx(), M_AttributeSet_ID);
                oneLinePerASI = mas.IsInstanceAttribute();
            }
            if (oneLinePerASI)
            {
                MInventoryLine line = new MInventoryLine(_inventory, M_Locator_ID,
                                                         M_Product_ID, M_AttributeSetInstance_ID,
                                                         qtyOnHand, qtyOnHand); //	book/count
                line.SetOpeningStock(currentQty);
                line.SetAsOnDateCount(line.GetQtyCount());

                // JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
                if (line.Get_ColumnIndex("QtyEntered") > 0)
                {
                    line.Set_Value("QtyEntered", line.GetQtyCount());
                }
                if (line.Get_ColumnIndex("C_UOM_ID") > 0)
                {
                    MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
                    line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
                }

                if (line.Get_ColumnIndex("IsFromProcess") >= 0)
                {
                    line.SetIsFromProcess(true);
                }
                if (line.Save())
                {
                    return(1);
                }
                return(0);
            }

            if (Env.Signum(qtyOnHand) == 0)
            {
                M_AttributeSetInstance_ID = 0;
            }

            if (_line != null &&
                _line.GetM_Locator_ID() == M_Locator_ID &&
                _line.GetM_Product_ID() == M_Product_ID)
            {
                if (Env.Signum(qtyOnHand) == 0)
                {
                    return(0);
                }
                //	Same ASI (usually 0)
                if (_line.GetM_AttributeSetInstance_ID() == M_AttributeSetInstance_ID)
                {
                    _line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
                    _line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
                    _line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
                    _line.SetAsOnDateCount(_line.GetQtyCount());

                    // JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
                    if (_line.Get_ColumnIndex("QtyEntered") > 0)
                    {
                        _line.Set_Value("QtyEntered", _line.GetQtyCount());
                    }
                    if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
                    {
                        MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
                        _line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
                    }

                    if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
                    {
                        _line.SetIsFromProcess(true);
                    }
                    _line.Save();
                    return(0);
                }
                //	Save Old Line info
                else if (_line.GetM_AttributeSetInstance_ID() != 0)
                {
                    MInventoryLineMA ma = new MInventoryLineMA(_line,
                                                               _line.GetM_AttributeSetInstance_ID(), _line.GetQtyBook());
                    if (!ma.Save())
                    {
                        ;
                    }
                }
                _line.SetM_AttributeSetInstance_ID(0);
                _line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
                _line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
                _line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
                _line.SetAsOnDateCount(_line.GetQtyCount());

                // JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
                if (_line.Get_ColumnIndex("QtyEntered") > 0)
                {
                    _line.Set_Value("QtyEntered", _line.GetQtyCount());
                }
                if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
                {
                    MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
                    _line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
                }

                if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
                {
                    _line.SetIsFromProcess(true);
                }
                _line.Save();
                //
                MInventoryLineMA ma1 = new MInventoryLineMA(_line, M_AttributeSetInstance_ID, qtyOnHand);
                if (!ma1.Save())
                {
                    ;
                }
                return(0);
            }
            //	new line
            _line = new MInventoryLine(_inventory, M_Locator_ID, M_Product_ID,
                                       M_AttributeSetInstance_ID, qtyOnHand, qtyOnHand); //	book/count
            _line.SetOpeningStock(currentQty);
            _line.SetAsOnDateCount(_line.GetQtyCount());

            // JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
            if (_line.Get_ColumnIndex("QtyEntered") > 0)
            {
                _line.Set_Value("QtyEntered", _line.GetQtyCount());
            }
            if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
            {
                MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
                _line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
            }

            if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
            {
                _line.SetIsFromProcess(true);
            }
            if (_line.Save())
            {
                return(1);
            }
            return(0);
        }