AP Withholding Tax by Invoice Details (Oracle Fusion SQL)

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

#oracle fusion sql

AP Withholding Tax by Invoice — Oracle Fusion SQL

Purpose: List invoice-level withholding tax (WHT) details from Oracle Fusion Payables for reporting, audit or data analysis.

Columns returned (example):

  • Invoice number, invoice date, supplier name / supplier ID
  • Withholding tax code, WHT amount (transactional & functional), WHT currency
  • Taxable amount, invoice amount, invoice currency
  • WHT rate, WHT status (held / released), payment method or WHT payment account (optional)

Use cases:

  • Review and audit all invoices with withholding tax applied
  • Export WHT-specific invoice data for tax reporting or compliance
  • Reconcile WHT held vs. WHT released / remitted
  • Generate supplier-wise WHT summaries or regulatory reports

How to use:

  • Run the SQL against your Fusion AP schema — join invoice tables with WHT tables
  • Filter by date range, supplier, WHT code/status, or currency as needed
  • Customize to add extra fields (e.g., supplier site, invoice lines, tax jurisdiction)

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project) 
  -- This query retrieves withholding tax (AWT) invoice details for suppliers
  PSV.VENDOR_NAME SUPPLIER_NAME,
  AIA1.INVOICE_AMOUNT,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  (
    SELECT
      ZPTP.REP_REGISTRATION_NUMBER
    FROM
      ZX_PARTY_TAX_PROFILE ZPTP
    WHERE
      ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY'
      AND 1 = 1
      AND ZPTP.PARTY_ID = AIA1.PARTY_ID
  ) VAT_REGISTRATION_NUM,
  PSSV.VENDOR_SITE_CODE,
  PSSV.ADDRESS_LINE1 || PSSV.CITY || PSSV.STATE || ', ' || PSSV.ZIP ADDRESS,
  PSSV.ADDRESS_LINE1 SITE_ADDRESS_LINE1,
  PSSV.ADDRESS_LINE2 SITE_ADDRESS_LINE2,
  PSSV.ADDRESS_LINE3 SITE_ADDRESS_LINE3,
  PSSV.CITY SITE_CITY,
  PSSV.STATE SITE_STATE,
  PSSV.ZIP SITE_ZIP,
  PSSV.PROVINCE SITE_PROVINCE,
  PSSV.COUNTRY SITE_COUNTRY,
  DECODE (
    ALC.DISPLAYED_FIELD,
    'ORIGINAL',
    AIA1.INVOICE_CURRENCY_CODE,
    FCV.CURRENCY_CODE
  ) ACTUAL_CURRENCY_CODE,
  DECODE ('AWT1', 'AWT5', AATR.TAX_NAME, NULL) TAX_NAME,
  DECODE ('AWT1', 'AWT5', ATC.DESCRIPTION, NULL) TAX_DESCRIPTION,
  AIA1.INVOICE_DATE INVOICE_DATE,
  AIA1.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
  AIA1.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE,
  AIA1.EXCHANGE_RATE INVOICE_EXCHANGE_RATE,
  FCV.NAME INVOICE_CURRENCY_NAME,
  AIA1.INVOICE_NUM INVOICE_NUMBER,
  (
    SELECT
      SUM (AIDA.AMOUNT) AMOUNT
    FROM
      AP_INVOICE_DISTRIBUTIONS_ALL AIDA
    WHERE
      (AIDA.INVOICE_ID = AIA1.INVOICE_ID)
      AND (AIDA.LINE_TYPE_LOOKUP_CODE != 'AWT')
      AND (AIDA.AWT_GROUP_ID IS NULL)
  ) AMOUNT_EXEMPT,
  AIA1.BASE_AMOUNT INVOICE_BASE_AMOUNT,
  AIA1.DESCRIPTION INVOICE_DESCRIPTION,
  FDS.NAME INVOICE_DOC_SEQUENCE_NAME,
  AIA1.DOC_SEQUENCE_VALUE INVOICE_DOC_SEQUENCE_VALUE,
  AIDA1.ACCOUNTING_DATE,
  (
    SELECT
      SUM (AIP.AMOUNT / AIA.PAYMENT_CROSS_RATE) AMOUNT
    FROM
      AP_INVOICE_PAYMENTS_ALL AIP,
      AP_INVOICES_ALL AIA
    WHERE
      AIA.INVOICE_ID = AIA1.INVOICE_ID
      AND (AIA.INVOICE_ID = AIP.INVOICE_ID)
  ) PAYMENT_AMOUNT,
  (
    SELECT
      SUM (AMOUNT)
    FROM
      AP_INVOICE_DISTRIBUTIONS_ALL AID
    WHERE
      AID.INVOICE_DISTRIBUTION_ID = AIDA1.AWT_RELATED_ID
  ) AMOUNT_SUBJECT_TO_TAX,
  - (AIDA1.AMOUNT) WITHHOLDING_TAX_AMOUNT,
  AIDA1.BASE_AMOUNT TAX_BASE_AMOUNT,
  AIDA1.DESCRIPTION LINE_DESCRIPTION,
  AIDA1.AWT_FLAG,
  ATC.NAME DIST_TAX_NAME,
  AATR.TAX_RATE DIST_TAX_RATE,
  AATR.RATE_TYPE DIST_RATE_TYPE,
  AATR.CERTIFICATE_NUMBER DIST_CERTIFICATE_NUMBER,
  AATR.CERTIFICATE_TYPE DIST_CERTIFICATE_TYPE,
  AATR.START_DATE DIST_CERT_START_DATE,
  AATR.END_DATE DIST_CERT_END_DATE,
  AATR.PRIORITY DIST_CERT_PRIORITY,
  (
    SELECT
      ACA.CHECK_DATE PAYMENT_DATE
    FROM
      AP_CHECKS_ALL ACA,
      AP_INVOICE_PAYMENTS_ALL AIP
    WHERE
      (ACA.CHECK_ID = AIP.CHECK_ID)
      AND (AIP.INVOICE_ID = AIA1.INVOICE_ID)
  ) DATE_PAID,
  (
    (
      SELECT
        SUM (AMOUNT)
      FROM
        AP_INVOICE_DISTRIBUTIONS_ALL AIDA
      WHERE
        AIDA.INVOICE_DISTRIBUTION_ID = AIDA1.AWT_RELATED_ID
    ) * (AIA1.EXCHANGE_RATE)
  ) GROSS_AMOUNT,
  HAOU.NAME ORGANIZATION_NAME,
  HAOU.ORGANIZATION_ID,
  PSV.VENDOR_ID SUPPLIER_ID,
  AIDA1.AWT_INVOICE_PAYMENT_ID,
  AIA1.INVOICE_ID,
  PSSV.VENDOR_SITE_ID
FROM
  AP_INVOICE_DISTRIBUTIONS_ALL AIDA1,
  AP_INVOICES_ALL AIA1,
  POZ_SUPPLIERS_V PSV,
  POZ_SUPPLIER_SITES_V PSSV,
  AP_AWT_TAX_RATES_ALL AATR,
  AP_TAX_CODES_ALL ATC,
  GL_LEDGERS GL,
  AP_LOOKUP_CODES ALC,
  FND_CURRENCIES_VL FCV,
  FND_DOCUMENT_SEQUENCES FDS,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND AIDA1.INVOICE_ID = AIA1.INVOICE_ID
  AND AIA1.VENDOR_ID = PSSV.VENDOR_ID
  AND AIA1.VENDOR_SITE_ID = PSSV.VENDOR_SITE_ID
  AND AIA1.VENDOR_ID = PSV.VENDOR_ID
  AND AIA1.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
  AND AIA1.INVOICE_CURRENCY_CODE = FCV.CURRENCY_CODE
  AND AIDA1.AWT_TAX_RATE_ID = AATR.TAX_RATE_ID(+)
  AND AIDA1.WITHHOLDING_TAX_CODE_ID = ATC.TAX_ID(+)
  AND AIDA1.LINE_TYPE_LOOKUP_CODE = 'AWT'
  AND AIDA1.SET_OF_BOOKS_ID = GL.LEDGER_ID
  AND HAOU.ORGANIZATION_ID = AIA1.ORG_ID
  AND ALC.LOOKUP_TYPE = 'SRS REPORT CURRENCY'
  AND ALC.LOOKUP_CODE = 'ORIGINAL'