Procure to Pay Cycle Query

    -- WITH OUT RECEIPTS

    -- procure to pay cycle query

    select distinct

           reqh.segment1 REQ_NUM,

           reqh.AUTHORIZATION_STATUS REQ_STATUS,

    --       poh.po_header_id,

           poh.segment1 PO_NUM,

           pol.line_num,

           poh.AUTHORIZATION_STATUS PO_STATUS,

    --       i.invoice_id,

           i.invoice_num,

           i.invoice_amount,

           i.amount_paid,

            i.vendor_id,

    --       v.vendor_name,

    --       p.check_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,    

         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 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 poh.segment1 = 4033816 -- PO NUMBER

    and reqh.segment1 = '501'   -- REQ NUMBER

    --and i.invoice_num = 3114     -- INVOICE NUMBER

    --and c.check_number =     -- CHECK NUMBER

        --and vendor_id =          -- VENDOR ID  

Click here to copy script to clipboard

-- PROCURE TO PAY CYCLE QUERY WITH RECEIPTS

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

      --       POH.PO_HEADER_ID,

                poh.segment1 po_num, pol.line_num,

                poh.authorization_status po_status, rcvh.receipt_num,

                rcv.inspection_status_code,

      --       I.INVOICE_ID,

                i.invoice_num, i.invoice_amount,

                i.amount_paid, i.vendor_id,

      --       V.VENDOR_NAME,

      --       P.CHECK_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 POH.PO_HEADER_ID = RCV.PO_HEADER_ID

            AND rcvh.shipment_header_id = rcv.shipment_header_id(+)

            --AND RCVH.SHIPMENT_HEADER_ID = RCVL.SHIPMENT_HEADER_ID

            --AND RCV.TRANSACTION_TYPE = 'RECEIVE'

            --AND RCV.SOURCE_DOCUMENT_CODE = 'PO'

            --AND POL.PO_LINE_ID = RCV.PO_LINE_ID

            --AND POD.PO_DISTRIBUTION_ID = RCV.PO_DISTRIBUTION_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 POH.SEGMENT1 = 36420 -- PO NUMBER

            AND reqh.segment1 = '501'  -- REQ NUMBER

            --AND I.INVOICE_NUM = 3114     -- INVOICE NUMBER

            --AND C.CHECK_NUMBER =     -- CHECK NUMBER

            --AND VENDOR_ID =          -- VENDOR ID

            --AND RECEIPT_NUM = 692237

Click here to copy script to clipboard