PROCURE TO PAY CYCLE QUERY WITH RECEIPTS

SELECT DISTINCT reqh.segment1 req_num, reqh.authorization_status req_status,              

                poh.segment1 po_num, pol.line_num,

                poh.authorization_status po_status, rcvh.receipt_num,

                rcv.inspection_status_code,

                i.invoice_num, i.invoice_amount,

                i.amount_paid, i.vendor_id,

                c.check_number, h.gl_transfer_flag, h.period_name

           FROM ap_invoices_all i,

                ap_invoice_distributions_all invd,

                po_headers_all poh,

                po_lines_all pol,

                po_distributions_all pod,

                po_vendors v,

                po_requisition_headers_all reqh,

                po_requisition_lines_all reql,

                po_req_distributions_all reqd,

                rcv_transactions rcv,

                rcv_shipment_headers rcvh,

                rcv_shipment_lines rcvl,

                ap_invoice_payments_all p,

                ap_checks_all c,

                ap_ae_headers_all h,

                ap_ae_lines_all l

          WHERE 1 = 1

            AND i.vendor_id = v.vendor_id

            AND c.check_id = p.check_id

            AND p.invoice_id = i.invoice_id

            AND poh.po_header_id = pol.po_header_id

            AND reqh.requisition_header_id = reql.requisition_header_id

            AND reqd.requisition_line_id = reql.requisition_line_id

            AND pod.req_distribution_id = reqd.distribution_id

            AND pod.po_header_id = poh.po_header_id

            AND rcvh.shipment_header_id = rcv.shipment_header_id(+)

            AND pod.po_distribution_id = invd.po_distribution_id

            AND invd.invoice_id = i.invoice_id

            AND h.ae_header_id = l.ae_header_id

            AND l.source_table = 'AP_INVOICES'

            AND l.source_id = i.invoice_id

            AND reqh.segment1 = ‘123126’