AP Discount Taken and Lost (Oracle Fusion SQL)

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

#oracle fusion sql

AP Discount Taken & Lost — Oracle Fusion SQL

Purpose: Retrieve invoice-level discount data from Oracle Fusion Payables — showing discounts taken, lost/expired discounts, and related invoice/payment metadata.

Typical Columns Returned:

  • Invoice number & invoice date, supplier name / supplier ID
  • Discount taken amount, discount lost/expired amount
  • Discount currency, invoice currency, payment currency (if different)
  • Discount status (taken, lost, expired), invoice status (paid/unpaid), payment date (if any)
  • Net invoice amount after discount, total payable amount, discount justification or code (if configured)

Use Cases:

  • Audit supplier invoices to check which discounts were successfully taken versus lost/expired
  • Track discount utilization rates — supplier-wise or period-wise
  • Reconcile payable ledger: ensure lost discounts are accounted for correctly
  • Report discounts as expense adjustments or cost-saving metrics in finance reviews

How to Use:

  • Run the SQL against your Fusion AP schema — join invoice tables with discount/credit-memo tables/payment schedule tables
  • Filter by invoice date, supplier, discount status or payment status as needed
  • Extend by adding more columns (e.g. supplier site, invoice lines, payment batches, cost-center allocations) if required

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- This query lists paid invoices with taken discounts by vendor and operating unit  
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
  PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_DATE,
  ACA.CHECK_DATE,
  AIA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY,
  AIA.VENDOR_ID SUPPLIER_ID,
  SUM(DECODE(FCV.MINIMUM_ACCOUNTABLE_UNIT,NULL,ROUND ((AIPA.AMOUNT / NVL (AIA.AMOUNT_PAID, 1) * AIA.INVOICE_AMOUNT ),FCV.PRECISION ),
          ROUND ((AIPA.AMOUNT / NVL (AIA.AMOUNT_PAID, 1) * AIA.INVOICE_AMOUNT) / FCV.MINIMUM_ACCOUNTABLE_UNIT) * FCV.MINIMUM_ACCOUNTABLE_UNIT)
  ) INVOICE_AMOUNT,
  SUM (
    DECODE (
      FCV.MINIMUM_ACCOUNTABLE_UNIT,
      NULL,
      ROUND (
        (
          NVL (AIPA.DISCOUNT_TAKEN, 0) / NVL (AIA.PAYMENT_CROSS_RATE, 1)
        ),
        FCV.PRECISION
      ),
      ROUND (
        (
          NVL (AIPA.DISCOUNT_TAKEN, 0) / NVL (AIA.PAYMENT_CROSS_RATE, 1)
        ) / FCV.MINIMUM_ACCOUNTABLE_UNIT
      ) * FCV.MINIMUM_ACCOUNTABLE_UNIT
    )
  ) DISCOUNT_AMT_TAKEN,  
  HAOU.ORGANIZATION_ID
FROM
  POZ_SUPPLIERS_V PSV,
  POZ_SUPPLIER_SITES_ALL_M PSSAM,
  AP_INVOICES_ALL AIA,
  AP_INVOICE_PAYMENTS_ALL AIPA,
  AP_CHECKS_ALL ACA,
  FND_CURRENCIES_VL FCV,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND PSV.VENDOR_ID = AIA.VENDOR_ID
  AND PSSAM.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID
  AND ACA.CHECK_ID = AIPA.CHECK_ID
  AND AIA.INVOICE_ID = AIPA.INVOICE_ID
  AND AIA.INVOICE_CURRENCY_CODE = FCV.CURRENCY_CODE
  AND ACA.VOID_DATE IS NULL
  AND AIA.PAYMENT_STATUS_FLAG != 'N'
  AND AIA.AMOUNT_PAID != 0
  AND(NVL (AIPA.DISCOUNT_TAKEN, 0) != 0 OR NVL (DISCOUNT_LOST, 0) != 0 )
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
GROUP BY
  HAOU.NAME,
  AIA.INVOICE_CURRENCY_CODE,
  PSV.VENDOR_TYPE_LOOKUP_CODE,
  AIA.INVOICE_NUM,
  AIA.INVOICE_DATE,
  ACA.CHECK_DATE,
  AIA.VENDOR_ID,
  PSV.VENDOR_NAME,
  PSSAM.VENDOR_SITE_CODE,
  HAOU.ORGANIZATION_ID