Withholding Tax by Supplier Report

Payables

Withholding tax calculated and deducted per supplier — the taxable base, rate, and amount withheld by tax type — so AP can remit the right amount to the authorities and give suppliers accurate withholding certificates.

Sample build of the Withholding Tax by Supplier Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

P Acct Date From

2026-02-28

P Acct Date To

2026-02-28

P Business Unit

APAC

P Invoice Date From

2026-02-28

P Invoice Date To

2026-02-28

P Invoice Type

Standard

$310K

Withheld

Suppliers

Missing tax ID

Supplier	Tax Type	Taxable Base	Rate	Withheld Amount	Invoice	Period
Acme Industrial	Standard	Sample	Sample	$1,240,500.00	Sample	APR-26
Northwind Trading	Corporate	—	—	$842,150.75	—	MAR-26
Globex Holdings	Standard	Sample	Sample	$96,400.00	Sample	FEB-26
Initech LLC	Default	—	—	$1,005,233.10	—	JAN-26
Umbrella Corp	Standard	Sample	Sample	$58,720.40	Sample	DEC-25
Acme Industrial	Standard	Sample	Sample	$1,240,500.00	Sample	APR-26

AI Analyst · active

reading

The report reads the automatic-withholding distributions tied to each invoice and supplier, by tax type and rate.

flag

Seven suppliers had tax withheld but have no tax registration number on file — their withholding certificates and the authority filing will reject without it.

root cause & next step

Capture the tax IDs on those seven suppliers before the remittance run; missing supplier tax registration is the most common withholding-filing reject.

Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →

This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.

Data sources

AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_AWT_TEMP_DISTRIBUTIONS_ALL
POZ_SUPPLIERS
ZX_RATES_B

How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

--Withholding Tax by Supplier Report

  WITH SEC_TBL AS
(
select 
	distinct 
	HOU.organization_id
from 
	HR_ORGANIZATION_UNITS_F_TL HOU,
	fun_user_role_data_asgnmnts FURDA,
	per_roles_dn PRD,
	per_user_roles PUR,
	per_users PU
where 
	1=1
	AND FURDA.org_id 				= HOU.organization_id
	AND UPPER(FURDA.role_name) in ('XXC_ACCOUNTS_PAYABLE_ANALYST_JOB','XXC_ACCOUNTS_PAYABLES_MANAGER_JOB','ORA_AP_ACCOUNTS_PAYABLE_INVOICE_SUPERVISOR_JOB','XXC_AP_PAYMENTS_ANALYST_JOB','XXC_ACCOUNTS_PAYABLE_INQUIRY_JOB')
	AND UPPER(FURDA.active_flag) = 'Y'
	AND UPPER(FURDA.role_name) = UPPER(PRD.role_common_name)
	AND PRD.role_id = PUR.role_id
	AND PUR.user_id = PU.user_id
	AND upper(pu.username) = upper(:xdo_user_name)
	AND pu.user_guid = FURDA.user_guid
) 
 
 --Query to fetch STANDARD Invoices that have a withholding(AWT) invoice
SELECT 
	AIA.invoice_num  SUPPLIER_INVOICE_NUMBER
	,AIA.DESCRIPTION SUPPLIER_INVOICE_DESC
	,AIA.invoice_type_lookup_code INVOICE_TYPE
	,AIA.invoice_date INVOICE_DATE
	,TO_CHAR(APS.DUE_DATE ,'MM-DD-YYYY') Invoice_Due_date
	,AIA.gl_date ACCT_DATE
	,PSV.vendor_name  SUPPLIER_NAME
	,PSV.segment1  SUPPLIER_NUMBER 
	,PSSV.vendor_site_code SUPPLIER_SITE
	,PSAV.address1||', '||PSAV.address2||', '||PSAV.city||', '||PSAV.postal_Code 
	SUPPLIER_ADDRESS
	,HOUFTL.name BUSINESS_UNIT
	,XEP.name LEGAL_ENTITY
	,(SELECT listagg(DISTINCT GCC.segment4,',') within group (order by 1)
	FROM gl_code_combinations GCC,
	ap_invoice_distributions_all AIDA 
	WHERE 1=1
	AND AIDA.invoice_id = AIA.invoice_id
	AND AIDA.dist_code_combination_id = GCC.code_combination_Id
	AND AIDA.awt_invoice_id IS NOT NULL ) NATURAL_ACCOUNT
	,(SELECT listagg(DISTINCT GCC.segment4,',') within group ( order by 1)
	FROM gl_code_combinations GCC,
	ap_invoice_distributions_all AIDA 
	WHERE 1=1
	AND AIDA.invoice_id = AIA.invoice_id
	AND AIDA.dist_code_combination_id = GCC.code_combination_Id
	AND AIDA.awt_invoice_id IS NULL
	AND AIDA.line_type_lookup_code = 'ITEM') EXPENSE_ACCOUNT
	,AIA.invoice_currency_code INVOICE_CURRENCY
	,AIA.invoice_amount TOTAL_INVOICE_AMOUNT
	,(SELECT distinct AIDA.EXCHANGE_RATE 
	FROM ap_invoice_distributions_all AIDA
	WHERE AIDA.invoice_id = AIA.invoice_id) FX_RATE
	,ACA.check_number PAYMENT_NUM
	,ACA.check_date PAYMENT_DATE
	,AIPA.discount_taken DISCOUNT_AMOUNT
	,ACA.amount PAYMENT_AMOUNT
	,(AIA.invoice_amount-AIA.total_tax_amount) AMOUNT_SUBJECT_TO_WITHHOLDING
	,WHT.AMOUNT WITHHOLDING_AMOUNT
	,WHT.TAX TAX_NAME
	,WHT.TAX_RATE_CODE TAX_CODE
	,WHT.tax_rate WITHHOLDING_RATE 
	,(SELECT DISTINCT ABA.batch_name
	FROM ap_batches_all ABA
	WHERE ABA.batch_id = AIA.batch_id) INVOICE_GROUP,
	(SELECT DISTINCT ZPTP.rep_registration_number 
	FROM zx_party_tax_profile ZPTP
	WHERE ZPTP.party_id = PSAV.party_site_id) TAX_FILE_NUMBER
	,PSP.income_tax_id TAXPAYER_ID
	--, ALINES.line_type_lookup_code
	
FROM 
	ap_invoices_all AIA,
	poz_suppliers_v PSV,
	poz_suppliers PS,
	poz_supplier_sites_v PSSV,
	poz_supplier_address_v PSAV,
	xle_entity_profiles XEP,
	ap_invoice_payments_all AIPA,
	ap_checks_all ACA,
	POZ_SUPPLIERS_PII PSP,
	AP_PAYMENT_SCHEDULES_ALL aps,
	--SEC_TBL ST,
	hr_organization_units_f_tl HOUFTL,
	(SELECT 
	 WHT_LINE.INVOICE_ID
	,WHT_LINE.AMOUNT
	,WHT_LINE.tax
	,WHT_LINE.TAX_RATE_CODE
	,WHT_LINE.tax_rate 
	FROM 
	ap_invoice_lines_all WHT_LINE
	WHERE 1=1
	AND WHT_LINE.LINE_TYPE_LOOKUP_CODE = 'AWT') WHT
	
	WHERE 1=1
	AND AIA.INVOICE_ID 					= WHT.invoice_id
	AND AIA.vendor_id 					= PSV.vendor_id
	AND PSP.vendor_id(+) 				= PS.vendor_id
	AND PSV.vendor_id 					= PSSV.vendor_id
	AND PSSV.vendor_id 					= PSAV.vendor_id
	AND AIA.PARTY_SITE_ID 				= PSAV.PARTY_SITE_ID
	AND AIA.VENDOR_SITE_ID 				= PSSV.VENDOR_SITE_ID
	AND AIA.ORG_ID 						= HOUFTL.organization_id
	AND aia.legal_entity_id 			= xep.legal_entity_id 
	AND PS.vendor_id 					= PSV.vendor_Id
	AND AIA.invoice_id 					= AIPA.invoice_id(+) 
	AND AIPA.check_id 					= ACA.check_Id(+) 
	AND aia.INVOICE_ID					= aps.INVOICE_ID
        AND ACA.VOID_DATE 					IS NULL --added to remove invoices with voided payments
	AND PSSV.inactive_date 				is null
	AND ap_invoices_utility_pkg.get_posting_status(AIA.invoice_id) = 'Y'
	AND AIA.APPROVAL_STATUS				= 'APPROVED'
	--AND ST.organization_id 				= HOUFTL.organization_id
	AND HOUFTL.LANGUAGE 				= USERENV('LANG')
	AND AIA.invoice_id 					IN (SELECT CAST(SUBSTR(INVOICE_NUM, INSTR(INVOICE_NUM, '-',1,1)+1, INSTR(INVOICE_NUM, '-',1,2) - INSTR(INVOICE_NUM, '-',1,1)-1) AS NUMERIC) AS INV_ID
											FROM AP_INVOICES_ALL
											WHERE invoice_type_lookup_code 	= 'AWT')
	AND SYSDATE 						BETWEEN HOUFTL.effective_start_date AND HOUFTL.effective_end_date
	
	AND (HOUFTL.name IN (:p_business_unit) OR 'All' IN ('All'||:p_business_unit))
	AND (XEP.name IN (:p_legal_entity) OR 'All' IN ('All'||:p_legal_entity))
	AND (PSV.segment1 IN (:p_vendor_number) OR 'All' IN ('All'||:p_vendor_number))
	AND (PSV.vendor_name IN (:p_supplier_name) OR 'All' IN ('All'||:p_supplier_name))
	AND (AIA.invoice_type_lookup_code IN (:p_invoice_type) OR 'All' IN ('All'||:p_invoice_type))
	AND trunc(AIA.invoice_date) BETWEEN (:p_invoice_date_from) AND (:p_invoice_date_to )
	AND trunc(AIA.gl_date) BETWEEN (:p_acct_date_from) AND (:p_acct_date_to)
	/*AND (:p_invoice_date_from IS NULL OR trunc(AIA.invoice_date) > :p_invoice_date_from )
	AND (:p_invoice_date_to IS NULL OR trunc(AIA.invoice_date) < :p_invoice_date_to )
	AND	(:p_acct_date_from IS NULL OR trunc(AIA.gl_date) > :p_acct_date_from )
	AND (:p_acct_date_to IS NULL OR trunc(AIA.gl_date) < :p_acct_date_to )*/

	
UNION ALL
--Query to fetch approved and accounted AWT invoices
SELECT 
	 AIA.INVOICE_NUM  SUPPLIER_INVOICE_NUMBER
	,AIA.DESCRIPTION SUPPLIER_INVOICE_DESC
	,AIA.invoice_type_lookup_code INVOICE_TYPE
	,AIA.invoice_date INVOICE_DATE
	,TO_CHAR(APS.DUE_DATE ,'MM-DD-YYYY') Invoice_Due_date
	,AIA.gl_date ACCT_DATE
	,PSV.vendor_name  SUPPLIER_NAME
	,PSV.segment1  SUPPLIER_NUMBER 
	,PSSV.vendor_site_code SUPPLIER_SITE
	,PSAV.address1||', '||PSAV.address2||', '||PSAV.city||', '||PSAV.postal_Code 
	SUPPLIER_ADDRESS
	,HOUFTL.name BUSINESS_UNIT
	,XEP.name LEGAL_ENTITY
	,(SELECT listagg(DISTINCT GCC.segment4,',') within group (order by 1)
	FROM gl_code_combinations GCC,
	ap_invoice_distributions_all AIDA 
	WHERE 1=1
	AND AIDA.invoice_id = AIA.invoice_id
	AND AIDA.dist_code_combination_id = GCC.code_combination_Id
	AND AIDA.awt_invoice_id IS NOT NULL ) NATURAL_ACCOUNT
	,(SELECT listagg(DISTINCT GCC.segment4,',') within group ( order by 1)
	FROM gl_code_combinations GCC,
	ap_invoice_distributions_all AIDA 
	WHERE 1=1
	AND AIDA.invoice_id = AIA.invoice_id
	AND AIDA.dist_code_combination_id = GCC.code_combination_Id
	AND AIDA.awt_invoice_id IS NULL
	AND AIDA.line_type_lookup_code = 'ITEM') EXPENSE_ACCOUNT
	,AIA.invoice_currency_code INVOICE_CURRENCY
	,AIA.invoice_amount TOTAL_INVOICE_AMOUNT
	,(SELECT distinct AIDA.EXCHANGE_RATE 
	FROM ap_invoice_distributions_all AIDA
	WHERE AIDA.invoice_id = AIA.invoice_id) FX_RATE
	,ACA.check_number PAYMENT_NUM
	,ACA.check_date PAYMENT_DATE
	,AIPA.discount_taken DISCOUNT_AMOUNT
	,ACA.amount PAYMENT_AMOUNT
	,(AIA.invoice_amount-AIA.total_tax_amount) AMOUNT_SUBJECT_TO_WITHHOLDING
	,(CASE WHEN AIA.INVOICE_TYPE_LOOKUP_CODE = 'AWT' THEN AIA.INVOICE_AMOUNT 
	WHEN AIA.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD' THEN	
	(SELECT SUM(AILA.AMOUNT)
	FROM ap_invoice_lines_all AILA
	WHERE AILA.invoice_id = AIA.invoice_id
	AND AILA.line_type_lookup_code  = 'AWT') END) WITHHOLDING_AMOUNT
	,(SELECT AILA.tax
	FROM ap_invoice_lines_all AILA
	WHERE AILA.invoice_id = AIA.invoice_id
	AND AILA.line_type_lookup_code  = 'AWT') TAX_NAME
	,(SELECT  AILA.TAX_RATE_CODE
	FROM ap_invoice_lines_all AILA
	WHERE AILA.invoice_id = AIA.invoice_id
	AND AILA.line_type_lookup_code  = 'AWT') TAX_CODE
	,(SELECT  AILA.tax_rate
	FROM ap_invoice_lines_all AILA
	WHERE AILA.invoice_id = AIA.invoice_id
	AND AILA.line_type_lookup_code  = 'AWT') WITHHOLDING_RATE 
	,(SELECT DISTINCT ABA.batch_name
	FROM ap_batches_all ABA
	WHERE ABA.batch_id = AIA.batch_id) INVOICE_GROUP,
	(SELECT DISTINCT ZPTP.rep_registration_number 
	FROM zx_party_tax_profile ZPTP
	WHERE ZPTP.party_id = PSAV.party_site_id) TAX_FILE_NUMBER
	,PSP.income_tax_id TAXPAYER_ID
	
FROM 
	ap_invoices_all AIA,
	poz_suppliers_v PSV,
	poz_suppliers PS,
	poz_supplier_sites_v PSSV,
	poz_supplier_address_v PSAV,
	xle_entity_profiles XEP,
	ap_invoice_payments_all AIPA,
	ap_checks_all ACA,
	POZ_SUPPLIERS_PII PSP,
	AP_PAYMENT_SCHEDULES_ALL aps,
	--SEC_TBL ST,
	hr_organization_units_f_tl HOUFTL
WHERE 1=1 
	
	AND AIA.vendor_id 					= PSV.vendor_id
	AND PSP.vendor_id(+) 				= PS.vendor_id
	AND PSV.vendor_id 					= PSSV.vendor_id
	AND PSSV.vendor_id 					= PSAV.vendor_id
	AND AIA.PARTY_SITE_ID 				= PSAV.PARTY_SITE_ID
	AND AIA.VENDOR_SITE_ID 				= PSSV.VENDOR_SITE_ID
	AND AIA.ORG_ID 						= HOUFTL.organization_id
	AND aia.legal_entity_id 			= xep.legal_entity_id 
	AND PS.vendor_id 					= PSV.vendor_Id
	AND AIA.invoice_id 					= AIPA.invoice_id(+) 
	AND AIPA.check_id 					= ACA.check_Id(+)   
	AND aia.INVOICE_ID                  = aps.INVOICE_ID
    AND ACA.VOID_DATE 					IS NULL --added to remove invoices with voided payments
	AND PSSV.inactive_date 				is null
	AND ap_invoices_utility_pkg.get_posting_status(AIA.invoice_id) = 'Y'
	AND AIA.APPROVAL_STATUS				= 'APPROVED'
	AND AIA.invoice_type_lookup_code 	= 'AWT'
	--AND ST.organization_id 				= HOUFTL.organization_id
	AND HOUFTL.LANGUAGE 				= USERENV('LANG')
	AND SYSDATE 						BETWEEN HOUFTL.effective_start_date AND HOUFTL.effective_end_date
	AND (HOUFTL.name IN (:p_business_unit) OR 'All' IN ('All'||:p_business_unit))
	AND (XEP.name IN (:p_legal_entity) OR 'All' IN ('All'||:p_legal_entity))
	AND (PSV.segment1 IN (:p_vendor_number) OR 'All' IN ('All'||:p_vendor_number))
	AND (PSV.vendor_name IN (:p_supplier_name) OR 'All' IN ('All'||:p_supplier_name))
	AND (AIA.invoice_type_lookup_code IN (:p_invoice_type) OR 'All' IN ('All'||:p_invoice_type))
	AND trunc(AIA.invoice_date) BETWEEN (:p_invoice_date_from) AND (:p_invoice_date_to )
	AND trunc(AIA.gl_date) BETWEEN (:p_acct_date_from) AND (:p_acct_date_to)
	/*AND (:p_invoice_date_from IS NULL OR trunc(AIA.invoice_date) > :p_invoice_date_from )
	AND (:p_invoice_date_to IS NULL OR trunc(AIA.invoice_date) < :p_invoice_date_to )
	AND	(:p_acct_date_from IS NULL OR trunc(AIA.gl_date) > :p_acct_date_from )
	AND (:p_acct_date_to IS NULL OR trunc(AIA.gl_date) < :p_acct_date_to )*/
	
ORDER BY 
BUSINESS_UNIT
,LEGAL_ENTITY
,SUPPLIER_NAME
,SUPPLIER_INVOICE_NUMBER
,INVOICE_DATE

:p_acct_date_from :p_acct_date_to :p_business_unit :p_invoice_date_from :p_invoice_date_to :p_invoice_type :p_legal_entity :p_supplier_name :p_vendor_number :xdo_user_name

The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.

●— fact → dimension join

Element	Type	Definition
AP_INVOICE_DISTRIBUTIONS_ALL	dimension	dimension
AP_AWT_TEMP_DISTRIBUTIONS_ALL	dimension	dimension
POZ_SUPPLIERS	dimension	dimension
ZX_RATES_B	dimension	dimension
Withheld Amount	measure	measure

Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.

How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →

See the complete model

How this report's fact and dimensions fit the full picture, via conformed keys.

Payables data model →Enterprise model →

Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.

Table	Reporting columns	Subject areas
AP_INVOICES_ALL	63	15
AP_INVOICE_DISTRIBUTIONS_ALL	59	11
AP_AWT_TEMP_DISTRIBUTIONS_ALL	Setup / configuration table — joined for reference, not exposed for analytics
POZ_SUPPLIERS	145	75
ZX_RATES_B	Setup / configuration table — joined for reference, not exposed for analytics

Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.