P2P 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