Reporting and Analytics for SCM Cloud

Get Involved. Join the Conversation.

Topic

    Olaoluwa Sakeye
    P2P report. PR-PO-Receipt-Invoice
    Topic posted May 16, 2019 by Olaoluwa Sakeye 
    103 Views, 11 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