Understanding how cost layers are sorted out according to the valuation method configured is important, it helps anticipating the cost to be withdrawn. In addition, when this is combined with the sales details such as unit price, several indicators can be figured out.

In this post, I am providing an SQL script which sorts out all the inventory purchase receipts (In documents) and associated details (Out documents). In case the out document is a sales invoice, associated sales invoice details are provided. This is illustrated below;

1

Cost layer status can be either one of the following:

  • Closed: All the quantity received have been consumed
  • Open: Partial or all of the quantity received is available for future consumption

Helping Note !

The prefix (In) is used to distinguish details retrieved from purchase receipt work (IV10200). While the prefix (Out) is used to distinguish data retrieved from purchase receipt work details (IV10201). Additionally, (SLS) is used to distinguish data retrieved from (SOP30300).

2

 

 

 

 

Tables Included:

·         IV10200  | Purchase Receipt Work

·         IV10200  | Purchase Receipt Work Details

·         SOP30300 | Sales Transactions Amounts History

 

 
SELECT  CASE A.RCPTSOLD
          WHEN 1 THEN 'Closed'
          WHEN 0 THEN 'Open'
          ELSE 'NA'
        END AS 'Cost Layer Status' ,
        A.RCPTNMBR AS 'In Receipt Number' ,
        CASE A.PCHSRCTY
          WHEN 1 THEN 'Adjustment'
          WHEN 2 THEN 'Variance'
          WHEN 3 THEN 'Transfer'
          WHEN 4 THEN 'Override'
          WHEN 5 THEN 'Receipt'
          WHEN 6 THEN 'Return'
          WHEN 7 THEN 'Assembly'
          WHEN 8 THEN 'In-Transit'
          ELSE 'NA'
        END AS 'In Transaction Type' ,
        A.DATERECD AS 'In Date Received' ,
        A.ITEMNMBR AS 'In Item Number' ,
        A.TRXLOCTN AS 'In Transaction Location' ,
        A.QTYRECVD AS 'In Quantity Received' ,
        A.QTYSOLD AS 'In Quantity Sold' ,
        A.UNITCOST AS 'In Unit Cost' ,
        A.RCTSEQNM AS 'In Receipt Sequence Number' ,
        ISNULL(B.ORIGInDOCID, ' ') AS 'Out Document Number' ,
        ISNULL(B.DOCDATE, ' ') AS 'Out Document Date' ,
        ISNULL(B.ITEMNMBR, ' ') AS 'Out Item Number' ,
        ISNULL(B.TRXLOCTN, ' ') AS 'Out Transaction Location' ,
        ISNULL(B.QTYSOLD, 0) AS 'Out Quantity Sold' ,
        ISNULL(B.UNITCOST, 0) AS 'Out Unit Cost' ,
        ISNULL(B.SRCRCTSEQNM, ' ') AS 'Out Source Receipt Sequence Number' ,
        ISNULL(C.SOPTYPE, ' ') AS 'SLS SOP Type' ,
        ISNULL(C.SOPNUMBE, ' ') AS 'SLS SOP Number' ,
        ISNULL(C.UNITPRCE, 0) AS 'SLS SOP Unit Price'
FROM    IV10200 AS A
        LEFT OUTER JOIN IV10201 AS B ON A.ITEMNMBR = B.ITEMNMBR
                                        AND A.TRXLOCTN = B.TRXLOCTN
                                        AND A.RCTSEQNM = B.SRCRCTSEQNM
        LEFT OUTER JOIN ( SELECT    CASE SOPTYPE
                                      WHEN 1 THEN 'Quote'
                                      WHEN 2 THEN 'Order'
                                      WHEN 3 THEN 'Invoice'
                                      WHEN 4 THEN 'Return'
                                      WHEN 5 THEN 'Back Order'
                                      WHEN 6 THEN 'Fulfillment Order'
                                      ELSE 'NA'
                                    END AS SOPTYPE ,
                                    SOPNUMBE ,
                                    ITEMNMBR ,
                                    LOCNCODE ,
                                    UNITCOST ,
                                    UNITPRCE
                          FROM      SOP30300
                        ) AS C ON B.ORIGInDOCID = C.SOPNUMBE
                                  AND B.ITEMNMBR = C.ITEMNMBR
                                  AND B.TRXLOCTN = C.LOCNCODE
 

 

Best Regards,
Mahmoud M. AlSaadi