CE AP Discount Invoice Details (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Cash Management (CE-Fusion-SQL's)
1

This CE AP Discount Invoice details Report query displays detailed invoice discount information for accounts payable payments, including the amounts and applicable dates for up to three discount periods, along with payment numbers, due dates, currency codes, payment priorities, vendor types, and hold statuses. The data supports thorough tracking of invoice payment statuses and discount application across different payment schedules and invoice types.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --Extracts invoice discount details for AP payments, including applicable dates and amounts
  AIA.INVOICE_NUM INVOICE_NUMBER,
  APSA.PAYMENT_NUM PAYMENT_NUMBER,
  APSA.DUE_DATE,
  DECODE (
    AIA.INVOICE_TYPE_LOOKUP_CODE,
    'PREPAYMENT',
    DECODE (
      AIA.AMOUNT_PAID,
      0,
      0,
      - AIA.AMOUNT_PAID
    ),
    NVL (APSA.AMOUNT_REMAINING, 0) - NVL (
      APSA.DISCOUNT_AMOUNT_AVAILABLE,
      0
    )
  ) FIRST_DISCOUNTED_AMOUNT,
  NVL (APSA.DISCOUNT_DATE, DUE_DATE) FIRST_DISCOUNT_DATE,
  DECODE (
    AIA.INVOICE_TYPE_LOOKUP_CODE,
    'PREPAYMENT',
    DECODE (
      AIA.AMOUNT_PAID,
      0,
      0,
      - AIA.AMOUNT_PAID
    ),
    NVL (APSA.AMOUNT_REMAINING, 0) - NVL (
      APSA.SECOND_DISC_AMT_AVAILABLE,
      0
    )
  ) SECOND_DISCOUNTED_AMOUNT,
  NVL (
    APSA.SECOND_DISCOUNT_DATE,
    DUE_DATE
  ) SECOND_DISCOUNT_DATE,
  DECODE (
    AIA.INVOICE_TYPE_LOOKUP_CODE,
    'PREPAYMENT',
    DECODE (
      AIA.AMOUNT_PAID,
      0,
      0,
      - AIA.AMOUNT_PAID
    ),
    NVL (APSA.AMOUNT_REMAINING, 0) - NVL (
      APSA.THIRD_DISC_AMT_AVAILABLE,
      0
    )
  ) THIRD_DISCOUNTED_AMOUNT,
  NVL (
    APSA.THIRD_DISCOUNT_DATE,
    DUE_DATE
  ) THIRD_DISCOUNT_DATE,
  AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
  NVL (APSA.PAYMENT_PRIORITY, 99) PAYMENT_PRIORITY,
  NVL (PS.VENDOR_TYPE_LOOKUP_CODE, '-1') VENDOR_TYPE,
  NVL (AIA.PAY_GROUP_LOOKUP_CODE, '-1') PAYGROUP,
  NVL (APSA.HOLD_FLAG, 'N') ON_HOLD
FROM
  AP_PAYMENT_SCHEDULES_ALL APSA,
  AP_INVOICES_ALL AIA,
  POZ_SUPPLIER_SITES_ALL_M PSSAM,
  POZ_SUPPLIERS PS,
  AP_SYSTEM_PARAMETERS_ALL ASPA,
  HR_ALL_ORGANIZATION_UNITS HAOU
where
  1 = 1
  AND APSA.INVOICE_ID = AIA.INVOICE_ID
  AND AIA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
  AND PSSAM.VENDOR_ID = PS.VENDOR_ID
  AND APSA.PAYMENT_NUM = DECODE (
    AIA.INVOICE_TYPE_LOOKUP_CODE,
    'PREPAYMENT',
    1,
    APSA.PAYMENT_NUM
  )
  AND (
    APSA.PAYMENT_STATUS_FLAG != 'Y'
    OR (
      APSA.PAYMENT_STATUS_FLAG = 'Y'
      AND AIA.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
      AND AIA.AMOUNT_PAID != 0
    )
  )
  AND ASPA.ORG_ID = HAOU.ORGANIZATION_ID