Skip to Content

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 28, 2017 at 08:52 AM

    Hi,

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

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jun 28, 2017 at 10:20 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.