Query to retrieve the AP invoices from Oracle Expenses and other sources.

SELECT DISTINCT
APIA.INVOICE_ID,
HROU.NAME as "Business Unit", --Business Unit
APIA.PRODUCT_TABLE,
APIA.INVOICE_NUM,
APIA.INVOICE_AMOUNT,
APIA.INVOICE_DATE,
HP.PARTY_NAME "Supplier Name", --Supplier
APIA.VENDOR_ID,
PSS.VENDOR_SITE_CODE "Supplier Site Name", -- Site Name
APIA.INVOICE_CURRENCY_CODE,
APIA.PAYMENT_CURRENCY_CODE,
APIA.DESCRIPTION,
APIA.INVOICE_TYPE_LOOKUP_CODE,
APT.NAME "Payment Terms",
APIA.TERMS_DATE,
APIA.GL_DATE,
APIA.PAYMENT_METHOD_CODE,
APIA.PAY_GROUP_LOOKUP_CODE,
APIA.MRC_EXCHANGE_RATE,
APIA.MRC_EXCHANGE_RATE_TYPE,
APIA.MRC_EXCHANGE_DATE,
APIA.ACCTS_PAY_CODE_COMBINATION_ID,
APIA.SETTLEMENT_PRIORITY,
APIA.PAYMENT_STATUS_FLAG,
GLCC.SEGMENT1||' - '||GLCC.SEGMENT2 ||' - ' ||GLCC.SEGMENT3||' - ' ||GLCC.SEGMENT4||' - ' ||GLCC.SEGMENT5||' - ' ||GLCC.SEGMENT6||' - ' ||GLCC.SEGMENT7 "Liability Code Combination",
APILA.LINE_NUMBER,
APILA.LINE_TYPE_LOOKUP_CODE,
APILA.AMOUNT "Line Amount",
APILA.QUANTITY_INVOICED,
APILA.UNIT_MEAS_LOOKUP_CODE,
APILA.DESCRIPTION "Line Description",
APILA.ITEM_DESCRIPTION

FROM
AP_INVOICES_ALL APIA,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
HR_ORGANIZATION_UNITS HROU,
POZ_SUPPLIER_SITES_ALL_M PSS,
AP_TERMS_TL APT,
GL_CODE_COMBINATIONS GLCC,
AP_INVOICE_LINES_ALL APILA

WHERE
APIA.VENDOR_ID>=0
AND PS.VENDOR_ID=APIA.VENDOR_ID
AND PS.PARTY_ID=HP.PARTY_ID
AND HROU.ORGANIZATION_ID=APIA.ORG_ID
AND APIA.VENDOR_SITE_ID=PSS.VENDOR_SITE_ID
AND APT.TERM_ID=APIA.TERMS_ID
AND GLCC.CODE_COMBINATION_ID=APIA.ACCTS_PAY_CODE_COMBINATION_ID
AND APIA.PAYMENT_STATUS_FLAG <> 'Y'
AND APIA.INVOICE_ID=APILA.INVOICE_ID
AND APIA.APPROVAL_STATUS <> 'CANCELLED'
--AND APIA.INVOICE_NUM like 'XXXXXXX'
--APIA.INVOICE_NUM like 'XXXXXX'

UNION

SELECT DISTINCT
APIA.INVOICE_ID,
HROU.NAME as "Business Unit", --Business Unit
APIA.PRODUCT_TABLE,
APIA.INVOICE_NUM,
APIA.INVOICE_AMOUNT,
APIA.INVOICE_DATE,
HP.PARTY_NAME "Supplier Name", --Supplier
APIA.VENDOR_ID,
NULL "Supplier Site Name", -- Site Name
APIA.INVOICE_CURRENCY_CODE,
APIA.PAYMENT_CURRENCY_CODE,
APIA.DESCRIPTION,
APIA.INVOICE_TYPE_LOOKUP_CODE,
APT.NAME "Payment Terms",
APIA.TERMS_DATE,
APIA.GL_DATE,
APIA.PAYMENT_METHOD_CODE,
APIA.PAY_GROUP_LOOKUP_CODE,
APIA.MRC_EXCHANGE_RATE,
APIA.MRC_EXCHANGE_RATE_TYPE,
APIA.MRC_EXCHANGE_DATE,
APIA.ACCTS_PAY_CODE_COMBINATION_ID,
APIA.SETTLEMENT_PRIORITY,
APIA.PAYMENT_STATUS_FLAG,
GLCC.SEGMENT1||' - '||GLCC.SEGMENT2 ||' - ' ||GLCC.SEGMENT3||' - ' ||GLCC.SEGMENT4||' - ' ||GLCC.SEGMENT5||' - ' ||GLCC.SEGMENT6||' - ' ||GLCC.SEGMENT7 "Liability Code Combination",
APILA.LINE_NUMBER,
APILA.LINE_TYPE_LOOKUP_CODE,
APILA.AMOUNT "Line Amount",
APILA.QUANTITY_INVOICED,
APILA.UNIT_MEAS_LOOKUP_CODE,
APILA.DESCRIPTION "Line Description",
APILA.ITEM_DESCRIPTION

FROM
AP_INVOICES_ALL APIA,
HZ_PARTIES HP,
HR_ORGANIZATION_UNITS HROU,
AP_TERMS_TL APT,
GL_CODE_COMBINATIONS GLCC,
AP_INVOICE_LINES_ALL APILA

WHERE
APIA.VENDOR_ID<0
AND APIA.PARTY_ID=HP.PARTY_ID
AND HROU.ORGANIZATION_ID=APIA.ORG_ID
AND APT.TERM_ID=APIA.TERMS_ID
AND GLCC.CODE_COMBINATION_ID=APIA.ACCTS_PAY_CODE_COMBINATION_ID
AND APIA.PAYMENT_STATUS_FLAG <> 'Y'
AND APIA.INVOICE_ID=APILA.INVOICE_ID
AND APIA.APPROVAL_STATUS <> 'CANCELLED'
--AND APIA.INVOICE_NUM like 'XXXXXX'
--AND APIA.PRODUCT_TABLE is null