AP Invoice Summary (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

This AP Invoice Summary query provides comprehensive invoice header details including supplier, currency, tax, payment, approval, and project information for tracking invoice lifecycle and payment status.

#sqlquery

SELECT
 -- SQL4Fusion (An Orbit Analytics Project) 
 -- This query provides comprehensive invoice header details
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  AIA.INVOICE_DATE,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_CURRENCY_CODE,
  AIA.INVOICE_AMOUNT,
  AIA.EXCHANGE_RATE,
  AIA.AMOUNT_PAID,
  AIA.TOTAL_TAX_AMOUNT,
  AIA.DISCOUNT_AMOUNT_TAKEN,
  AIA.GL_DATE,
  AIA.VALIDATED_TAX_AMOUNT,
  AIA.GOODS_RECEIVED_DATE,
  AIA.INVOICE_RECEIVED_DATE,
  AIA.DESCRIPTION INVOICE_DESCRIPTION,
  AIA.PAYMENT_CURRENCY_CODE,
  AIA.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_TYPE,
  DECODE (
    AIA.PAYMENT_STATUS_FLAG,
    'N',
    'NO',
    'Y',
    'YES',
    'P',
    'PARTIAL',
    AIA.PAYMENT_STATUS_FLAG
  ) INVOICE_PAYMENT_STATUS,
  AIA.APPROVAL_READY_FLAG,
  AIA.WFAPPROVAL_STATUS,
  AIA.PO_HEADER_ID,
  AT.NAME TERM_NAME,
  PPAB.SEGMENT1 PROJECT_NUMBER,
  GDCT.USER_CONVERSION_TYPE USER_RATE_TYPE, 
  AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
  AIA.SOURCE,
  AIA.INVOICE_ID,
  AIA.VENDOR_ID,
  AIA.SET_OF_BOOKS_ID LEDGER_ID,
  HAOU.ORGANIZATION_ID
FROM
  AP_INVOICES_ALL AIA,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  AP_TERMS AT,
  PJF_PROJECTS_ALL_B PPAB,
  POZ_SUPPLIERS_V PSV,
  GL_DAILY_CONVERSION_TYPES GDCT 
WHERE
  1 = 1
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
  AND AIA.TERMS_ID = AT.TERM_ID(+)
  AND AIA.PROJECT_ID = PPAB.PROJECT_ID(+)
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND AIA.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+)