Oracle Purchasing / Inventory / Payables / GL Query

SELECT DISTINCT A.ORG_ID "ORG ID",
(select organization_name from apps.org_organization_definitions where operating_unit=A.ORG_ID and organization_code='ORG_NAME') org_name,
(SELECT r.segment1 "Req No"
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO REQ NUMBER",
(SELECT TO_CHAR(TRUNC(D.CREATION_DATE)) "Req Approved Date"
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO REQ APPROVED DATE",
D.SEGMENT1 "PO NUMBER",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
G.line_num "PO LINE NUMBER",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO LINE AMOUNT",
A.INVOICE_NUM "INVOICE NUMBER",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "Vendor Type",
E.VENDOR_NAME "Vendor Name",
F.VENDOR_SITE_CODE "Vendor Site",
F.ADDRESS_LINE1 "Address",
F.CITY "City",
F.COUNTRY "Country",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO Date ",
D.SEGMENT1 "PO Number ",
(SELECT r.segment1 "Req No"
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO Req Number ",
(SELECT TO_CHAR(TRUNC(D.CREATION_DATE)) "Req Approved Date "
FROM po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE c.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
) "PO Req Approved Date ",
D.TYPE_LOOKUP_CODE "PO Type",
G.line_num "PO Line Num ",
C.QUANTITY_ORDERED "Qty Ordered",
C.QUANTITY_CANCELLED "QTY Cancelled",
G.ITEM_DESCRIPTION "Item Description",
G.UNIT_PRICE "Unit Price",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount ",
(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7||'.'||SEGMENT8 PO_Accrual_Account FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID=c.accrual_account_id) "PO Accrual Account",
(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7||'.'||SEGMENT8 PO_variance_Account FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID=c.variance_account_id) "PO Variance Account",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO Status",
A.INVOICE_TYPE_LOOKUP_CODE "Invoice Type",
A.INVOICE_AMOUNT "Invoice Amount ",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "Invoice Date ",
A.INVOICE_NUM "Invoice Number ",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID "Amount Paid",
H.AMOUNT "Amount",
I.CHECK_NUMBER "Cheque Number",
TO_CHAR(TRUNC(I.CHECK_DATE)) "Payment Date"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
Order by 3,5,9,10