Where is the SQL code for the 'Inventory Audit Report' located?

[Former Member]

Hi Everyone,

I would like to have a look at the SQL code that constitutes the 'Inventory Audit Report', as accessible by right clicking on the 'Item Master Data' screen and making the appropriate selection (from the list).

Any help here will be greatly appreciated.

Kind Regards,

David

[Former Member · Accepted solution]

Hi,

This report is based on OINM table/view. Not all values is available directly such as accumulated values. You may run a Profiler to find out what script is executed.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

Can you please shed some light on how the various notations (illustrated below), such as SI & SU relate to the TransType attribute in the Warehouse Journal (OINM) table?

Given the list of TransTypes below (taken from the SAP docs) -

I am particularly confused about the TransTypes 20 & 59 which according to the document are essentially the same; both have the same description of 'Goods Receipt'.

I have managed to ascertain the following abbreviations (from the Inventory Audit Report) and their related tables -

Any further help here will be greatly appreciated.

Kind Regards,

David

[Former Member · Accepted solution]

20 is OPDN and 59 is OIGN.

There are quite a few threads listed these codes. Such as: SAP B1 SQL B-FN General Ledger Report with Account Code and Date Range as Selection Criteria - Busin...

[Former Member · Accepted solution]

Thank you

[kothandaraman_nagarajan]

Hi,

Object code 20 refers to Good receipt PO under purchasing module.

Object code 59 refers to Good receipt under inventory module

Thanks & Regards,

Nagarajan

[Former Member]

Thanks

[kothandaraman_nagarajan]

Welcome. Do you have further questions?

[Former Member]

No thankyou.

[kothandaraman_nagarajan]

Please close this thread by marking correct answer.

[kothandaraman_nagarajan]

Hi,

There is no function/option to see SQL code for all reports.

Thanks & Regards,

Nagarajan

[pvsbprasad]

Hi,

i think we cant view the code..but can get the report by uing the query

select T.ItmsGrpNam, T.ItemCode, T.ItemName, sum(T.OpeningQty) c1, sum(T.OpeningVal) c2,

sum(T.InQty) c3, sum(T.InVal) c4, sum(T.OutQty) c5, sum(T.OutVal) c6, sum(T.ClosingQty) c7,

sum(T.ClosingVal) c8 from ((SELECT T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName, sum(T0.[InQty] - T0.[OutQty]) OpeningQty,

sum(case when T0.[OutQty]>0 then T0.[TransValue] else T0.[TransValue] end) OpeningVal, 0.00 InQty, 0.00 InVal, 0.00 OutQty, 0.00 OutVal,

0.00 ClosingQty, 0.00 ClosingVal FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

where T0.DocDate < {?P1} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL')  GROUP BY T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName)union

all(SELECT T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName, 0.00, 0.00, sum(T0.[InQty]), sum(case when T0.[OutQty]>0 then 0.00

else T0.[TransValue] end), 0.00, 0.00, 0.00, 0.00 FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB

T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL')

GROUP BY T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName)union all(SELECT T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00,

sum(T0.[OutQty]), sum(case when T0.[OutQty]>0 then -1*T0.[TransValue] else 0.00 end), 0.00, 0.00 FROM OINM T0  INNER JOIN OITM T1 ON

T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and

(T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.[ItmsGrpNam], T1.ItemCode, T1.ItemName)union all(SELECT T2.[ItmsGrpNam],

T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, sum(T0.[InQty] - T0.[OutQty]), sum(case when T0.[OutQty]>0 then

T0.[TransValue] else T0.[TransValue] end) FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON

T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.[ItmsGrpNam],

T1.ItemCode, T1.ItemName)) T group by T.ItmsGrpNam, T.ItemCode, T.ItemName order by T.ItmsGrpNam, T.ItemCode, T.ItemName

i got my query

hope it can help .

select T.ItmsGrpNam, T.ItemCode, T.ItemName, sum(T.OpeningQty) c1, sum(T.OpeningVal) c2,

sum(T.InQty) c3, sum(T.InVal) c4, sum(T.OutQty) c5, sum(T.OutVal) c6, sum(T.ClosingQty) c7,

sum(T.ClosingVal) c8 from ((SELECT T2.ItmsGrpNam, T1.ItemCode, T1.ItemName, sum(T0.InQty - T0.OutQty) OpeningQty,

sum(case when T0.OutQty>0 then T0.TransValue else T0.TransValue end) OpeningVal, 0.00 InQty, 0.00 InVal, 0.00 OutQty, 0.00 OutVal,

0.00 ClosingQty, 0.00 ClosingVal FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

where T0.DocDate < {?P1} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.ItmsGrpNam, T1.ItemCode, T1.ItemName)union

all(SELECT T2.ItmsGrpNam, T1.ItemCode, T1.ItemName, 0.00, 0.00, sum(T0.InQty), sum(case when T0.OutQty>0 then 0.00

else T0.TransValue end), 0.00, 0.00, 0.00, 0.00 FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB

T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL')

GROUP BY T2.ItmsGrpNam, T1.ItemCode, T1.ItemName)union all(SELECT T2.ItmsGrpNam, T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00,

sum(T0.OutQty), sum(case when T0.OutQty>0 then -1*T0.TransValue else 0.00 end), 0.00, 0.00 FROM OINM T0 INNER JOIN OITM T1 ON

T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate >= {?P1} and T0.DocDate <= {?P2} and

(T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.ItmsGrpNam, T1.ItemCode, T1.ItemName)union all(SELECT T2.ItmsGrpNam,

T1.ItemCode, T1.ItemName, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, sum(T0.InQty - T0.OutQty), sum(case when T0.OutQty>0 then

T0.TransValue else T0.TransValue end) FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON

T1.ItmsGrpCod = T2.ItmsGrpCod where T0.DocDate <= {?P2} and (T0.Warehouse = '{?P4}' or '{?P4}' = 'ALL') GROUP BY T2.ItmsGrpNam,

T1.ItemCode, T1.ItemName)) T group by T.ItmsGrpNam, T.ItemCode, T.ItemName order by T.ItmsGrpNam, T.ItemCode, T.ItemName

Regards,

Prasad

[Former Member]

Hi Prasad,

Thank you for your contribution however I don't think that your code works.

Kind Regards,

David