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;
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).
· 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'
END AS 'Cost Layer Status' ,
A.RCPTNMBR AS 'In Receipt Number' ,
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'
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'
END AS SOPTYPE ,
) AS C ON B.ORIGInDOCID = C.SOPNUMBE
AND B.ITEMNMBR = C.ITEMNMBR
AND B.TRXLOCTN = C.LOCNCODE
Mahmoud M. AlSaadi