P2P 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 =