13 Replies Latest reply: Oct 9, 2014 1:44 AM by Helen Betty RSS

    How to get the ending inventory

      Hi,

      I have a problem in getting the ending inventory report could you pleas help me on what to do. See below sample for reference.

      BWART as MovementType,
      if(BWART = '561', 'BegBal') as Beg_Balance,
      if(BWART = '562', 'BegBalReversal') as Beg_Bal_Reversal,
      if(BWART = '351', 'GoodIssuePlant') as Good_Issue_Plant,
      if(BWART = '301', 'GoodsIssue') as Goods_Issue,
      if(BWART = '302', 'GIReversal') as GI_Reversal,
      if(BWART = '901', 'GIStockTrfPlant') as GI_Transfer_Plant,
      if(BWART = '902', 'GIStockTrfPlantReversal') as GI_TPlant_Reversal,
      if(BWART = '101', 'GoodsReceipt') as Goods_Receipt,
      if(BWART = '102', 'GRReversal') as GR_Reversal,
      if(BWART = '951', 'Sales') as Sales,

      MENGE as QTY,
      if(BWART = '561', MENGE, 0) as BegInv,
      if(BWART = '101', MENGE, if(BWART = '302', MENGE, 0)) as Receipt,
      if(BWART = '351', MENGE, if(BWART = '301', MENGE,
      if(BWART = '901', MENGE, if(BWART = '102', MENGE,
      if(BWART = '951', MENGE, 0))))) as Issuance,


      This will be the correct output for Summary Report :
      BegInventroy Receipt Issuance EndInventory

      5 2 2 5
      10 6 4 12
      20 10 10 20

       

       

      Thanks,

      Jhon

        • How to get the ending inventory
          s j

          Standard Logic is

          Opening Stock + Receipt Qty - Issue Qty = As On Date Stock

          You are doing this in MSEG

          to built this logic in SAP you have to cumulate receipe and issue based on requirement .

          and you have to avoid 309 movement.

           

            • How to get the ending inventory

              Hi Sunil,

              I am using this script (<BegInv> + <Receipt> - <Issuance>) as EndInv, to get the ending inventory but unfortunately I have encounter an error.I hope u could help me to resolve my problem.

               

               

              Thanks,

              Jhon

                • How to get the ending inventory
                  s j

                  Can you post your data in Excel.?.

                    • How to get the ending inventory

                      Hi Sunil,

                      Here is my sample data. I hope you could help me to get the ending inventory.

                       

                      Thanks,

                      Jhon

                       

                      Branch_CodeMat_NumberMovement_TypeStockQTYBegInvBeg_BalanceBeg_Bal_ReversalReceiptGoods_ReceiptGR_ReversalGoods_IssueGI_ReversalIssuanceSalesShould be EndInventory
                      5200058BegBal11BegBal001
                      5200058BegBal1313BegBal0013
                      5200058GoodsReceipt24024GoodsReceipt240
                      5200058Sales1001Sales0
                      6200058BegBal1919BegBal0019
                      6200058GoodsReceipt24024GoodsReceipt240
                      6200058GoodsReceipt48048GoodsReceipt480
                      6200058Sales1001Sales0
                      27200058BegBal1212BegBal00
                      27200058Sales1001Sales
                      31200058BegBal1717BegBal00
                      31200058GoodsReceipt24024GoodsReceipt24
                      31200058Sales1001Sales
                      221200058GoodsReceipt24024GoodsReceipt24
                      221200058Sales1001Sales
                      222200058GoodsReceipt24024GoodsReceipt24
                      222200058Sales1001Sales
                      2222000581000
                      223200058BegBal11BegBal00
                      223200058BegBal33BegBal00
                      223200058BegBalReversal30BegBalReversal00


                       

                        • How to get the ending inventory

                          Hi Sunil,

                          Please download attachedment. I hope u would find the way to help me.

                           

                          Thanks,

                          Jhon

                            • How to get the ending inventory
                              s j

                               

                              Following is the logic of FIFO Inventory Method.

                               

                              Stock_Summary:
                              Load distinct

                              MATERIAL,
                              PLANT,
                              Storage_Location,
                              M_Date ,
                              SUM(Quantity_Received_Into_Valuated_Stock - Quantity_Issued_From_Valuated_Stock) as Rec_Issue_Valuated_Stock,
                              SUM(Receipt_Quantity_Blocked_Stock - Issue_Quantity_Blocked_Stock) as Rec_Issue_Blocked_Stock,
                              SUM(Quantity_Received_Into_Consignment_Stock - Quantity_Issued_From_Consignment_Stock) as Rec_Issue_Consignment_Stock,
                              SUM(Receipt_Quantity_Stock_In_Quality_Inspection - Issue_Quantity_Stock_In_Quality_Inspection) as Rec_Issue_Quality_Inspection_Stock,
                              //SUM(Issue_Quantity_Stock_In_Transit- Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock,
                              SUM(Value_Received_Into_Valuated_Stock - Value_Issued_From_Valuated_Stock) AS Rec_Issue_Value_Valuated_Stock ,
                              SUM(Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock

                              resident Stock_Master
                              group by PLANT,Storage_Location, MATERIAL,M_Date ;
                              drop table Stock_Master;

                               

                              Stock_Summary_Index:
                              load
                              *,
                              recno() as d
                              resident Stock_Summary order by PLANT,Storage_Location, MATERIAL, M_Date;
                              drop table Stock_Summary;

                               

                              Material_Stock:
                              Load
                              MATERIAL,
                              PLANT,
                              Storage_Location,
                              M_Date ,
                              Rec_Issue_Valuated_Stock,
                              Rec_Issue_Intransit_Stock,
                              Rec_Issue_Blocked_Stock,
                              Rec_Issue_Consignment_Stock,
                              if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)+Rec_Issue_Valuated_Stock,Rec_Issue_Valuated_Stock) as Total_Valuated_Stock,
                              if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)) as Opening_Valuated_Stock,
                              if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Blocked_Stock)+Rec_Issue_Blocked_Stock, Rec_Issue_Blocked_Stock) as Total_Blocked_Stock,
                              if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Consignment_Stock)+Rec_Issue_Consignment_Stock, Rec_Issue_Consignment_Stock) as Total_Consignment_Stock,
                              if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Rec_Issue_Intransit_Stock) as Total_Intransit_Stock,

                              if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Value_Valuated_Stock)+Rec_Issue_Value_Valuated_Stock, Rec_Issue_Value_Valuated_Stock) as Total_Value_Valuated_Stock

                              resident Stock_Summary_Index order by d;
                              drop field d;
                              drop table Stock_Summary_Index;

                              store Material_Stock into ..\Data\Modelled QVD\Material_Stock.qvd;
                              Drop Table Material_Stock;


                              //```````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
                              Stock_Summary:
                              LOAD
                              MATERIAL,
                              PLANT,
                              Storage_Location,
                              M_Date ,
                              Rec_Issue_Valuated_Stock,
                              Rec_Issue_Intransit_Stock,
                              Rec_Issue_Blocked_Stock,
                              Rec_Issue_Consignment_Stock,
                              Total_Valuated_Stock,
                              Opening_Valuated_Stock,
                              Total_Blocked_Stock,
                              Total_Consignment_Stock,
                              Total_Intransit_Stock,
                              Total_Value_Valuated_Stock
                              FROM
                              [..\Data\Modelled QVD\Material_Stock.qvd]
                              (qvd);

                               

                              RIGHT JOIN (Stock_Summary) LOAD
                              *,
                              if( previous(PLANT) = PLANT AND // If the same Product,
                              previous(Storage_Location) = Storage_Location AND
                              previous(MATERIAL) = MATERIAL ,
                              date(previous(M_Date)-1) // Assign the prior day
                              , '' ) as EndDate // Otherwise assign infinity
                              RESIDENT Stock_Summary
                              // The ORDER is important. We must group by Product, and within Product, we must read Descending -- most recent Price first.
                              ORDER BY PLANT,Storage_Location,MATERIAL, M_Date DESC ;

                               


                              /*
                              Determine the lowest date in the entire transaction set.
                              We will use this to generate the range of dates.
                              */
                              MinDate:
                              LOAD
                              num(min(M_Date)) as minAllDate
                              RESIDENT Stock_Summary
                              ;
                              LET vStartDate = fieldValue('minAllDate',1)-1; // Put the mindate in a variable for later loop
                              DROP TABLE MinDate; // No longer need the table


                              /*
                              Generate a table of Dates from the minDate up through and including today.
                              */
                              LET vEndDate = num(today(1)); // Generate dates through today


                              //-----------------------------------------------------------------------------------------------------------
                              StockMaster: // This will be the new pricing table

                              LOAD *, month(Date) as Month // Add a Month dimension to ease navigation
                              ;
                              LOAD
                              date($(vStartDate) + IterNo(),'DD.MM.YYYY') as Date
                              AUTOGENERATE 1
                              WHILE $(vStartDate) + IterNo() <= $(vEndDate); //OK

                               

                              /*
                              Use IntervalMatch to JOIN PriceMaster and PriceTransaction by StartDate/EndDate range.
                              */

                              LEFT JOIN (StockMaster) IntervalMatch(Date)
                              LOAD
                              DISTINCT M_Date,
                              EndDate
                              RESIDENT Stock_Summary; //OK


                              /*
                              JOIN the Price field to PriceMaster to allow us to drop PriceMaster and
                              avoid the synthetic key.
                              */
                              LEFT JOIN (StockMaster) LOAD * RESIDENT Stock_Summary; //OK


                              //------------------------------------------------------------------------------------------------------------


                              //store StockMaster into ..\Data\Modelled QVD\Summarize_Stock_Master.qvd; //
                              DROP TABLE Stock_Summary;


                              // We don't need the StartDate & EndDate fields anymore

                    • Re: How to get the ending inventory
                      vikas mahajan

                      PFA sample hope this may help you

                      vikas