Saturday, June 4, 2016

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'

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect