Friday, November 2, 2018

Procure to Pay Cycle Query

Procure to Pay Cycle Query

Includes two scripts to fetch all the transactions information related with in a procure to pay cycle.
Two scripts are provided to use one with receipts and other when receipts are not created.
Few important fields that were included in the script are Requisition Number, Purchase Order Number, Invoice Number, Customer Number, Invoice Amount, GL Transfer flag e.t.c
–Procure to Pay query without 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,
    --     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
— 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

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...