This AP Withholding Tax by Payment Report query is used to review the detailed invoice withholding tax information for a supplier, including invoice number, amount subject to withholding, and tax amounts withheld.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query generates withholding tax (AWT) report for supplier invoices
HAOU.NAME ORGANIZATION_NAME,
AATRA.TAX_NAME WITHHOLDING_TAX_NAME,
ATCA.DESCRIPTION WITHHOLDING_TAX_DESCRIPTION,
AATRA.TAX_RATE WITHHOLDING_TAX_RATE,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
(
SELECT
ZPTP.REP_REGISTRATION_NUMBER
FROM
ZX_PARTY_TAX_PROFILE ZPTP
WHERE
ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY'
AND ZPTP.PARTY_ID = AIA.PARTY_ID
) AS VAT_REGISTRATION_NUMBER,
PSSV.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
PSSV.ADDRESS_LINE1 || PSSV.CITY || PSSV.STATE || ', ' || PSSV.ZIP SITE_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,
AATRA.CERTIFICATE_TYPE,
AATRA.CERTIFICATE_NUMBER,
AATRA.RATE_TYPE CERTIFICATE_RATE_TYPE,
AATRA.START_DATE CERTIFICATE_START_DATE,
AATRA.END_DATE CERTIFICATE_END_DATE,
AATRA.PRIORITY CERTIFICATE_PRIORITY,
ATCA.NAME TAX_CODE_NAME,
AIA.EXCHANGE_RATE INVOICE_EXCHANGE_RATE,
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.INVOICE_DATE,
AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
FCV.NAME INVOICE_CURRENCY,
AIA.INVOICE_AMOUNT,
AIA.BASE_AMOUNT INVOICE_BASE_AMOUNT,
AIA.DESCRIPTION INVOICE_DESCRIPTION,
FDS.NAME DOCUMENT_SEQUENCE_NAME,
AIA.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_VALUE,
AIDA.ACCOUNTING_DATE,
AIDA.AMOUNT WITHHOLDING_TAX_AMOUNT,
AIDA.BASE_AMOUNT TAX_BASE_AMOUNT,
AIDA.DESCRIPTION DISTRIBUTION_LINE_DESCRIPTION,
AIDA.AWT_FLAG,
AIPA.AMOUNT PAYMENT_AMOUNT,
ACA.CHECK_DATE PAYMENT_DATE,
ACA.CHECK_NUMBER PAYMENT_NUMBER,
AIPA.DISCOUNT_TAKEN DISCOUNT_AMOUNT_TAKEN
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_V PSSV,
AP_AWT_TAX_RATES_ALL AATRA,
AP_TAX_CODES_ALL ATCA,
FND_DOCUMENT_SEQUENCES FDS,
FND_CURRENCIES_VL FCV,
GL_LEDGERS GL,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = PSV.VENDOR_ID
AND AIA.VENDOR_ID = PSSV.VENDOR_ID
AND AIA.VENDOR_SITE_ID = PSSV.VENDOR_SITE_ID
AND AIDA.AWT_TAX_RATE_ID = AATRA.TAX_RATE_ID(+)
AND AIDA.WITHHOLDING_TAX_CODE_ID = ATCA.TAX_ID(+)
AND AIA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
AND AIA.INVOICE_CURRENCY_CODE = FCV.CURRENCY_CODE
AND AIDA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'AWT'
AND AIA.INVOICE_ID =AIPA.INVOICE_ID
AND AIPA.CHECK_ID= ACA.CHECK_ID
AND AIDA.AWT_INVOICE_PAYMENT_ID IS NOT NULL