custom report

[Former Member]

Hi Experts,

Could you please share to me the custom report of inventory audit report generated using SQL query.

The report is expected simialr with inventory audit report. In the query, we need that the item group is also displayed.

I see that I can not display the item group name in the SBO std inventory audit report. We use SAP B1 2007A SP01 PL10. Please help me. Thanks

John Ed

[former_member312729]

Hi John Eduard,

Try this

SELECT  dISTINCT T0.[TransNum], T0.[DocDate], T0.[CardName], T0.[ItemCode], T0.[Dscription], T0.Warehouse, (T0.InQty-T0.OutQty) AS"QUANTITY", T0.Price, T0.TransValue FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod
WHERE T1.ItemCode LIKE '[%0]%' AND T0.InQty != T0.OutQty and T2.ItmsGrpNam = '[%1]'

Regards:

Balaji.S

Edited by: Balaji Selvaraj on Dec 23, 2010 11:53 AM

[Former Member]

Hi John,

Try this:

SELECT T0.ItemCode, T0.ItemName, T1.ItmsGrpNam, T2.CreateDate, T2.DocDate, T2.TransType,

T2.Ref1, T2.Warehouse, (T2.InQty-T2.OutQty) QTY, T2.Price, T2.TransValue

FROM dbo.OITM T0

INNER JOIN dbo.OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod

INNER JOIN dbo.OINM T2 ON T0.ItemCode = T2.ItemCode

WHERE T0.ItemCode LIKE '[%0\]%' AND T2.InQty != T2.OutQty

Thanks,

Gordon