This AP Invoice Approval Report query displays invoice approval history details, including approver actions, approval status, amounts, vendor information and approval context across operating units.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query displays invoice approval history details
XEP.NAME LEGAL_ENTITY,
HP.PARTY_NAME SUPPLIER_NAME,
ALC.DISPLAYED_FIELD STATUS,
HP.CATEGORY_CODE,
NVL (AIAHA.AMOUNT_APPROVED, 0) APPROVED_AMOUNT,
AIAHA.APPROVER_ID,
AIAHA.ITERATION,
AIAHA.LAST_UPDATE_DATE ACTION_DATE,
AIAHA.RESPONSE ACTION_CODE,
AIA.WFAPPROVAL_STATUS APPROVAL_STATUS_CODE,
AIA.INVOICE_CURRENCY_CODE,
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.INVOICE_DATE INVOICE_DATE,
NVL (AIA.INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
AIA.VENDOR_ID,
AIA.VENDOR_SITE_ID,
AIA.ORG_ID,
HAOU.NAME ORGANIZATION_NAME,
AIA.SET_OF_BOOKS_ID,
ALC3.DISPLAYED_FIELD APPROVAL_CONTEXT,
AIA.INVOICE_ID
FROM
AP_INV_APRVL_HIST_ALL AIAHA,
AP_INVOICES_ALL AIA,
HZ_PARTIES HP,
AP_LOOKUP_CODES ALC,
AP_LOOKUP_CODES ALC2,
AP_LOOKUP_CODES ALC3,
XLE_ENTITY_PROFILES XEP,
HR_ALL_ORGANIZATION_UNITS HAOU,
POZ_SUPPLIER_SITES_ALL_X PSSAX
WHERE
1 = 1
AND AIA.INVOICE_ID = AIAHA.INVOICE_ID
AND HP.PARTY_ID = AIA.PARTY_ID
AND ALC.LOOKUP_CODE = AIA.WFAPPROVAL_STATUS
AND ALC.LOOKUP_TYPE = 'AP_WFAPPROVAL_STATUS'
AND ALC2.LOOKUP_CODE(+) = AIAHA.RESPONSE
AND ALC2.LOOKUP_TYPE(+) = 'AP_WFAPPROVAL_STATUS'
AND ALC3.LOOKUP_TYPE(+) = 'AP_WFAPPROVAL_CONTEXT'
AND ALC3.LOOKUP_CODE(+) = AIAHA.HISTORY_TYPE
AND AIAHA.HISTORY_TYPE = 'DOCUMENTAPPROVAL'
AND XEP.LEGAL_ENTITY_ID(+) = AIA.LEGAL_ENTITY_ID
AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
AND PSSAX.VENDOR_SITE_ID(+) = AIA.VENDOR_SITE_ID