Reporting and Analytics for SCM

Get Involved. Join the Conversation.

Topic

    Olaoluwa Sakeye
    P2P report. PR-PO-Receipt-Invoice
    Topic posted May 16, 2019 by Olaoluwa SakeyeRed Ribbon: 250+ Points 
    241 Views, 17 Comments
    Title:
    P2P report. PR-PO-Receipt-Invoice
    Summary:
    I need a report on P2P process.
    Version:
    19A

    Comment

     

    • sai sudheer

      Hi Can you be more specific on your requirement!!

    • Sanoj Sudheera

      Hi,

      Check ERP Report Sharing Center 

      https://cloudcustomerconnect.oracle.com/pages/17668b0b6c#messages

      There are few reports which you can cover your requirement. Update as per your requirement.

      Regards,

      Sanoj

    • Farooq Haider Syed, PMP

      Hi,

      Please use the attached archived file for the requested report.

       

      Thanks

    • Farooq Haider Syed, PMP

      Please also see the attached one.

    • Olaoluwa Sakeye

      Thanks guys for your response.

      I need the columns below.

      Requisition no
      Requisition Amount
      Requisition Creation Date
      Requisition Created By
      Purchase Order Number
      Description
      Currency
      Ordered Amount
      Status
      Supplier Name
      PO Creation Date
      PO Approval Date
      Invoice Number
      Invoice Date
      Receipt Number
      Receipt Date
      Ordered Quantity
      Quantity Receive
      Invoice Amount
      Invoice Amount Paid
      Invoice Description
      Invoice Payment Date
      Quantity
      Delivered Quantity


      Thanks you.

    • Sudhakara Rao Kovuru

      Do you need SQL query for these columns?

    • Sudhakara Rao Kovuru

      Sure, will prepare and share you but might take little time so please stay here?

    • Olaoluwa Sakeye

      Alright, thanks.

    • Sudhakara Rao Kovuru
      --SQL for P2P Details - PR-> PO -> Receipt -> AP Invoice 
      SELECT bu.name, 
             hzp.party_name        supplier_name, 
             hzp.party_number      supplier_number, 
             poss.vendor_site_code supplier_site_name, 
             prh.requisition_number, 
             prh.creation_date     pr_creation_date, 
             poh.segment1          po_Number, 
             poh.creation_date     po_creation_date, 
             rsh.receipt_num       receipt_number, 
             aia.invoice_num       invoice_number, 
             aila.amount           Invoice_amount 
      FROM   por_requisition_headers_all prh, 
             por_requisition_lines_all prl, 
             po_headers_all poh, 
             po_lines_all pol, 
             poz_suppliers pos, 
             hz_parties hzp, 
             poz_supplier_sites_all_m poss, 
             hr_all_organization_units bu, 
         --FUN_ALL_BUSINESS_UNITS_V fbu,
             po_distributions_all pda, 
             gl_code_combinations gcc, 
             rcv_shipment_lines rsl, 
             rcv_shipment_headers rsh, 
             ap_invoice_lines_all aila, 
             ap_invoices_all aia, 
             ap_invoice_distributions_all aida 
      WHERE  prl.requisition_header_id = prh.requisition_header_id 
             --AND prh.req_bu_id=fbu.bu_id
             AND prl.po_header_id = poh.po_header_id 
             AND prl.po_line_id = pol.po_line_id 
             AND poh.po_header_id = pol.po_header_id 
             AND poh.vendor_id = pos.vendor_id 
             AND poh.vendor_site_id = poss.vendor_site_id 
             AND hzp.party_id = pos.party_id 
             AND pos.vendor_id = poss.vendor_id 
             AND bu.organization_id = poss.prc_bu_id 
             AND pda.po_header_id = pol.po_header_id 
             AND pda.po_line_id = pol.po_line_id 
             AND rsh.shipment_header_id(+) = rsl.shipment_header_id 
             AND rsl.po_line_id(+) = pol.po_line_id 
             AND aila.po_line_id(+) = pol.po_line_id 
             AND gcc.code_combination_id = aida.dist_code_combination_id 
             AND aia.invoice_id(+) = aila.invoice_id 
             AND aia.invoice_id = aida.invoice_id(+) 
             --AND prh.requisition_number = pr_requisition_number 
             --AND poh.segment1 = po_number 
    • Olaoluwa Sakeye

      Just resumed back to work today, i will check it out and revert. Thank you.

    • Olaoluwa Sakeye

      Hello Rao,

      Hope you're keeping well. I have ran the sequel code and it's fine. The lines are duplicating. I would love to share my skypeid with you if you don't mind.

    • Tessy van Engelen

      Hi Both,

      Did you manage to finish this query?

       

    • Olaoluwa Sakeye

      @Tessy : Not yet, i still need help on it.

    • Tessy van Engelen

      @Olaoluwa : you have multple lines on distribution level. If you don't want to report on this level you can make a sum on the amount and put the other columns i a group by. Then you should see one line per order. See below query.

      SELECT bu.name,
             hzp.party_name        supplier_name,
             hzp.party_number      supplier_number,
             poss.vendor_site_code supplier_site_name,
             prh.requisition_number,
             prh.creation_date     pr_creation_date,
             poh.segment1          po_Number,
             poh.creation_date     po_creation_date,
             rsh.receipt_num       receipt_number,
             aia.invoice_num       invoice_number,
             sum(aila.amount)           Invoice_amount
             
      FROM   por_requisition_headers_all prh,
             por_requisition_lines_all prl,
             po_headers_all poh,
             po_lines_all pol,
             poz_suppliers pos,
             hz_parties hzp,
             poz_supplier_sites_all_m poss,
             hr_all_organization_units bu,
             po_distributions_all pda,
             gl_code_combinations gcc,
             rcv_shipment_lines rsl,
             rcv_shipment_headers rsh,
             ap_invoice_lines_all aila,
             ap_invoices_all aia,
             ap_invoice_distributions_all aida
             
      WHERE  1=1
             AND prl.requisition_header_id = prh.requisition_header_id
             AND prl.po_header_id = poh.po_header_id
             AND prl.po_line_id = pol.po_line_id
             AND poh.po_header_id = pol.po_header_id
             AND poh.vendor_id = pos.vendor_id
             AND poh.vendor_site_id = poss.vendor_site_id
             AND hzp.party_id = pos.party_id
             AND pos.vendor_id = poss.vendor_id
             AND bu.organization_id = poss.prc_bu_id
             AND pda.po_header_id = pol.po_header_id
             AND pda.po_line_id = pol.po_line_id
             AND aida.po_distribution_id = pda.po_distribution_id
                    
             AND rsh.shipment_header_id(+) = rsl.shipment_header_id
             AND rsl.po_line_id(+) = pol.po_line_id
             AND aila.po_line_id(+) = pol.po_line_id
             AND gcc.code_combination_id = aida.dist_code_combination_id
             AND aia.invoice_id(+) = aila.invoice_id
             AND aia.invoice_id = aida.invoice_id(+)
                    
             group by bu.name,
             hzp.party_name        ,
             hzp.party_number      ,
             poss.vendor_site_code ,
             prh.requisition_number,
             prh.creation_date     ,
             poh.segment1          ,
             poh.creation_date     ,
             rsh.receipt_num       ,
             aia.invoice_num       
             
             order by poh.segment1 desc