Stock on Hand using 2LIS_03_BF

[Afshin_IR]

Hi There,

I have a requirement to calculate the following , stock on hand for a given day using a SQL given we have the following extractors enabled  2LIS_03_BF (MSEG) & 2LIS_03_UM

• Inventory QTY
• Inventory unit cost
• Inventory Value
• In network in transit Qty
• In network in transit Value

Any suggestions or  any help would be much appreciated.  The client does not want to use MATDOC and MATDOC_EXTRACT for their reporting but would like to use the above two extractors.

Thanks

Nick

[Savio_Dmello · Accepted solution]

To calculate stock on hand for a specific date, you need to aggregate the relevant quantities and values up to that date.

sample sql below which could have some syntax error. Pls Use it as pseudo logic .

For Inventory Quantity:

• Sum the quantities from 2LIS_03_BF (MENGE) for relevant movement types (like 101 for receipts, 201 for issues).
• Ensure you consider the sign (positive for receipts, negative for issues).
• Group by Material Number (MATNR), Plant (WERKS), and Storage Location (LGORT).

SELECT MATNR, WERKS, LGORT, SUM(CASE WHEN BWART IN ('101', '102', '561') THEN MENGE
WHEN BWART IN ('201', '202', '261') THEN -MENGE
ELSE 0 END) AS INVENTORY_QTY
FROM 2LIS_03_BF
WHERE BUDAT <= @input_date
GROUP BY MATNR, WERKS, LGORT;

For Inventory Unit Cost and Inventory Value:

• The 2LIS_03_UM extractor will provide revaluation data, which can be used to calculate the inventory value and cost.
• Combine this data with inventory quantities from 2LIS_03_BF.

SELECT A.MATNR, A.WERKS, A.LGORT,
SUM(A.INVENTORY_QTY * B.DMBTR / B.MENGE) AS INVENTORY_VALUE,
SUM(B.DMBTR / B.MENGE) AS INVENTORY_UNIT_COST
FROM
(
SELECT MATNR, WERKS, LGORT,
SUM(CASE WHEN BWART IN ('101', '102', '561') THEN MENGE
WHEN BWART IN ('201', '202', '261') THEN -MENGE
ELSE 0 END) AS INVENTORY_QTY
FROM 2LIS_03_BF
WHERE BUDAT <= @input_date
GROUP BY MATNR, WERKS, LGORT
) A
JOIN
(
SELECT MATNR, BWKEY, BWTAR, DMBTR, MENGE
FROM 2LIS_03_UM
WHERE BUDAT <= @input_date
) B
ON A.MATNR = B.MATNR;

For In-Network In-Transit Qty and Value:

• To calculate in-transit quantities and values, filter on the movement types that represent in-transit movements.
• Sum the relevant quantities and values from 2LIS_03_BF.

SELECT MATNR, WERKS, LGORT,
SUM(CASE WHEN BWART = '641' THEN MENGE ELSE 0 END) AS IN_TRANSIT_QTY,
SUM(CASE WHEN BWART = '641' THEN MENGE * UNIT_COST ELSE 0 END) AS IN_TRANSIT_VALUE
FROM
(
SELECT MATNR, WERKS, LGORT,
BWART, MENGE,
(SELECT SUM(DMBTR) FROM 2LIS_03_UM WHERE MATNR = M.MATNR AND BUDAT <= @input_date) /
(SELECT SUM(MENGE) FROM 2LIS_03_BF WHERE MATNR = M.MATNR AND BUDAT <= @input_date) AS UNIT_COST
FROM 2LIS_03_BF M
WHERE BUDAT <= @input_date
) X
GROUP BY MATNR, WERKS, LGORT;