query in sap b1 for last transaction date for that particular part code.

[datta1990]

HI sir,

Please, could you create a report for me I need new column added with the last transaction date for that particular part code on below query?

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 and T1.[createDate] <= [%0] ORDER BY T1.[ItemCode]

Regards,

Datta P.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query,

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue], T0.[LastPurDat], T0.[LstSalDate] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 and T1.[createDate] <= [%0] ORDER BY T1.[ItemCode]

Regards,

Nagarajan

[datta1990 · Accepted solution]

Thanks sir,

I want warehouse to warehouse transfer last date (this requirement for the department for stock management)

regards,

Datta P.

[kothandaraman_nagarajan · Accepted solution]

Hi,

If you got answer for query with last date, close this discussion.

For warehouse transfer last date, please create new discussion.

Regards,

Nagarajan

[datta1990 · Accepted solution]

Hi Nagarajan ,

I need some help about tables for Stock with Transaction Date in SAP B1, I need to know the balance of stock and when was the entrance and exit of the material What tables I need extract?

Regards,

Datta Phulse

[datta1990 · Accepted solution]

Hi Nagarajan,

I have created report for the same please check and help for the top on the result in below 'last date used'

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue], (SELECT top 1 z.[DocDate] FROM ODLN z INNER JOIN DLN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM OINV z INNER JOIN INV1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM OPCH z INNER JOIN PCH1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM OPDN z INNER JOIN PDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORDN z INNER JOIN RDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORIN z INNER JOIN RIN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORPC z INNER JOIN RPC1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used', (SELECT top 1 z.[DocDate] FROM ORPD z INNER JOIN RPD1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC)'Date last used' FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 ORDER BY T1.[ItemCode]

i want output: top 1 'date last used'

[datta1990 · Accepted solution]

thanks sir

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue] , (SELECT MAX(x.DocDate) FROM ((SELECT top 1 z.[DocDate] FROM ODLN z INNER JOIN DLN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc) UNION ALL (SELECT top 1 z.[DocDate] FROM OINV z INNER JOIN INV1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM OPCH z INNER JOIN PCH1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM OPDN z INNER JOIN PDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORDN z INNER JOIN RDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORIN z INNER JOIN RIN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORPC z INNER JOIN RPC1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORPD z INNER JOIN RPD1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) ) x) 'Date last used' FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 ORDER BY T1.[ItemCode]