R12: Purchasing to Payment Query in Oracle Apps (P2P)

Give 'PO Number' as an input to the following script.

It will provide all required PO details associated Receipt details, Invoice details, Payment details.

Purchase Order -> Receipts -> Invoices -> Payments

SELECT pha.segment1 po_number,

       pla.line_num,

       plla.shipment_num,

       msi.segment1 ordered_item,

       msi.description ordered_item_description,

       pla.unit_price po_unit_price,

       rt.transaction_type,

       rt.transaction_date,

       rt.subinventory,

       plla.quantity quantity_ordered,

       plla.quantity_received,

       pda.quantity_delivered,

       plla.quantity_Billed,

       rsh.receipt_num,

       aia.invoice_num,

       ail.line_number inv_line_number,

       aid.distribution_line_number inv_dist_number,

       ail.line_type_lookup_code Line_type,

       aid.line_type_lookup_code Dist_line_type,

       aid.amount,

       aid.quantity_invoiced,

       ac.check_number,

       aip.payment_num

FROM apps.rcv_transactions rt,

     apps.po_headers_all pha,

     apps.po_line_locations_all plla,

     apps.po_distributions_all pda,

     apps.po_lines_all pla,

     apps.mtl_system_items msi,

     apps.rcv_shipment_headers rsh,

     apps.ap_invoices_all aia,

     apps.ap_invoice_lines_all ail,

     apps.ap_invoice_distributions_all aid,

     apps.ap_invoice_payments_all aip,

     apps.ap_checks_all ac

WHERE rt.po_header_id = pha.po_header_id

      AND pha.po_header_id = pla.po_header_id

      AND pla.po_line_id = plla.po_line_id

      AND plla.line_location_id = pda.line_location_id

      AND rt.po_line_location_id = plla.line_location_id

      AND pla.item_id = msi.inventory_item_id

      AND rt.po_line_id = pla.po_line_id

      AND rt.organization_id = msi.organization_id

      AND rsh.shipment_header_id = rt.shipment_header_id

      AND aip.check_id = ac.check_id

      AND aia.invoice_id = aip.invoice_id

      AND aia.invoice_id = ail.invoice_id

      AND aia.invoice_id = aid.invoice_id

      AND pda.po_distribution_id(+) = aid.po_distribution_id

      AND rt.transaction_type = 'DELIVER'

      AND ail.line_type_lookup_code = 'ITEM'

      AND aid.line_type_lookup_code = 'ACCRUAL'

      AND pha.segment1 = '&PO_Number'