cancel
Showing results for 
Search instead for 
Did you mean: 

Inventory Aging Data, Duplication in SAP B1 8.81

Former Member
0 Kudos

Hello Everyone,

I have been working on Inventory Aging Report creation and has been facing with a problem. The data seems to get duplicated when i execute the

report. I have attached the screen shot below for your reference.

I have taken the following tables OPDN, PDN1, OITM and OITW for my report, and below is the query.

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode], T3.[OnHand] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode WHERE T3.[OnHand]  > 0

Please help me.. Thank You.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

Try:

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode], T3.[OnHand] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode and T1.WhsCode = T3.WhsCode and T3.[OnHand]  > 0

Thanks,

Gordon

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode], sum(T3.[OnHand]) FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode WHERE T3.[OnHand]  > 0 group by T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode]

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan,

Thank You for your reply..

But I am still facing the duplication, before it was duplicating three times, now two times it is getting replicated.

Attached the new screenshot for your reference..

Kindly view the total stock values in report and SAP....Appreciate your help..

Thank you.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

The above query is not correct. Because there is no SUM function. Please run my query and check your result.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hello.

I tried with the SUM function, its displaying the total sum.. i want individual item stock details .. so only tried without SUM function..

Thanks

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this :

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode], T2.[OnHand] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode WHERE T3.[OnHand]  > 0 group by T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription],

T1.[WhsCode],T2.[OnHand]

Thanks & Regards,

Nagarajan