Friday, November 29, 2013

PROCURE TO PAY CYCLE QUERY WITH RECEIPTS


SELECT 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

Best Blogger TipsGet Flower Effect