How can I match OPDN, PDN1, OIPF and IPF1 tables in SAP B1 query?

[former_member183402]

Hi all, I have a problem in SAP B1 tables I want to do query which will use OPDN, PDN1, OIPF and IPF1 tables of SAP B1 but these tables do not match, I tried to do their relationship but I got multiple rows of some landed cost items, the following is my sample query.

SELECT T3.[DocNum] as 'Doc Num Landed Cost',T1.[BaseEntry] as 'Doc Num GRPo', T2.[DocDate] as 'Purchasing Date', T1.[Dscription], T1.[Quantity], T0.[TotalSumSy] as 'Purchase Price',T1.[TtlExpndSC] as 'Landed Cost', (T0.[TotalSumSy])+ (T1.[TtlExpndSC]) as 'Total Value', ((T0.[TotalSumSy])+ (T1.[TtlExpndSC])) / T1.[Quantity] as 'Unit Price' FROM PDN1 T0 inner join IPF1 T1 on T0.[DocEntry]=T1.[BaseEntry] INNER JOIN OPDN T2 ON T0.[DocEntry] = T2.[DocEntry] INNER JOIN OIPF T3 ON T1.[DocEntry] = T3.[DocEntry] WHERE T2.[DocDate] >=[%0] AND T2.[DocDate] <=[%1] and T1.[Dscription] =[%3] and T1.[TtlExpndSC]<>0

I tried to do research I got this link Trying to link Goods Receipt Purchase Order (OPDN) to Landed Cost (OIPF)? but relationship of tables I got in that link does not display all items listed in landed cost, it displays only one item per landed cost.

How can I match OPDN, PDN1, OIPF and IPF1 tables in order to display all items listed in landed cost?

Please anyone can help me.

[former_member227598]

Hi Rarangwa,

Please find the below sample query & add the fields as per your requirments

SELECT * FROM

OIPF A INNER JOIN IPF1 B ON A.DocEntry = B.DocEntry

left join PDN1 C on B.[BaseEntry] = C.docentry and B.[LineNum] = C.[BaseLine]

INNER JOIN OPDN D ON C.DocEntry = D.DocEntry

Rgds,

Kamlesh Naware

[former_member183402]

Hi Kamlesh,

As I answered Nagarajan if you verify the following query it brings few items from landed cost instead of to display many

SELECT D.[DocDate] , A.[DocNum] as 'Doc Num Landed Cost'
FROM
OIPF A INNER JOIN IPF1 B ON A.DocEntry = B.DocEntry left join PDN1 C on B.[BaseEntry] = C.docentry and B.[LineNum] = C.[BaseLine] INNER JOIN OPDN D ON C.DocEntry = D.DocEntry
WHERE D.[DocDate] >=[%0] AND  D.[DocDate] <=[%1] and B.[Dscription] =[%3] and B.[TtlExpndSC]<>0

Please verify I do know why it does not display all items listed in landed cost.

[kothandaraman_nagarajan]

Hi,

Please check this thread https://archive.sap.com/discussions/thread/3535477

Regards,

Nagarajan

[former_member183402]

Hi Nagarajan,

That's good, but I tried to use your query i don't get all items listed in landed cost, you can run the following query and verify it brings few items instead of many.

SELECT T3.[DocDate] , T0.[DocNum] as 'Doc Num Landed Cost',T1.[BaseEntry] as 'Doc Num GRPo', T3.[DocDate] as 'Purchasing Date', T1.[Dscription], T1.[Quantity], T2.[TotalSumSy] as 'Purchase Price',T1.[TtlExpndSC] as 'Landed Cost', (T2.[TotalSumSy])+ (T1.[TtlExpndSC]) as 'Total Value', ((T2.[TotalSumSy])+ (T1.[TtlExpndSC])) / T1.[Quantity] as 'Unit Price'
FROM OIPF T0  INNER JOIN IPF1 T1 ON T0.DocEntry = T1.DocEntry left join  PDN1 T2 on  T1.[BaseEntry]  = t2.docentry and  T1.[LineNum]  =  T2.[BaseLine] INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry
WHERE T3.[DocDate] >=[%0] AND  T3.[DocDate] <=[%1] and T1.[Dscription] =[%3] and T1.[TtlExpndSC]<>0

But I think the cause is and T1.[LineNum] = T2.[BaseLine] INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

Please help me.

[kothandaraman_nagarajan]

Hi,

Try this,

SELECT T3.[DocDate] , T0.[DocNum] as 'Doc Num Landed Cost',T1.[BaseEntry] as 'Doc Num GRPo', T3.[DocDate] as 'Purchasing Date', T1.Itemcode,T1.[Dscription], T1.[Quantity], T2.[TotalSumSy] as 'Purchase Price',T1.[TtlExpndSC] as 'Landed Cost', (T2.[TotalSumSy])+ (T1.[TtlExpndSC]) as 'Total Value', ((T2.[TotalSumSy])+ (T1.[TtlExpndSC])) / T1.[Quantity] as 'Unit Price' FROM OIPF T0 INNER JOIN IPF1 T1 ON T0.DocEntry = T1.DocEntry left join PDN1 T2 on T1.[BaseEntry] = t2.docentry and T1.[LineNum] = T2.[BaseLine] INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry WHERE T3.[DocDate] >=[%0] AND T3.[DocDate] <=[%1] and T1.[TtlExpndSC]<>0