The blog provides the Oracle Apps Query to get AP Invoice Supplier Tax Registration Number, query to get supplier tax details in oracle apps r12
SELECT
AIA.INVOICE_NUM,
AP_INVOICES_ALL_TBL.INVOICE_CURRENCY_CODE,
AIL.PERIOD_NAME,
AP_INVOICES_ALL_TBL.INVOICE_ID,
PO_VENDORS_TBL.VENDOR_NAME,
PO_VENDORS_TBL.SEGMENT1 VENDOR_NUM,
PO_VENDOR_SITES_ALL_TBL.VENDOR_SITE_CODE,
AP_INVOICES_ALL_TBL.DESCRIPTION,
AP_INVOICES_ALL_TBL.GL_DATE,
AP_INVOICES_ALL_TBL.INVOICE_DATE,
ROUND( (ZLV.UNROUNDED_TAXABLE_AMT *NVL(ZLV.CURRENCY_CONVERSION_RATE,1) ),2) AMOUNT,
1 QUANTITY,
ZLV.TAX_RATE_CODE,
AP_INVOICES_PKG.GET_POSTING_STATUS (AP_INVOICES_ALL_TBL.INVOICE_ID) POSTING_FLAG,
(SELECT VAT_REGISTRATION_NUM FROM AP_SUPPLIERS WHERE VENDOR_ID = PO_VENDORS_TBL.VENDOR_ID) SUPPLIER_TRN,
CASE
WHEN AP_INVOICES_ALL_TBL.INVOICE_CURRENCY_CODE = 'AED' THEN ROUND (ZLV.TAX_AMT, 2)
ELSE ROUND ( (ZLV.TAX_AMT) * AP_INVOICES_ALL_TBL.EXCHANGE_RATE, 2)
END
TAX_AMT
FROM AP_INVOICES_ALL AP_INVOICES_ALL_TBL,
AP_INVOICE_LINES_ALL AIL,
PO_VENDORS PO_VENDORS_TBL,
PO_VENDOR_SITES_ALL PO_VENDOR_SITES_ALL_TBL,
ZX_LINES_V ZLV
WHERE AP_INVOICES_ALL_TBL.INVOICE_ID = AIL.INVOICE_ID
AND AIL.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX')
AND AP_INVOICES_ALL_TBL.INVOICE_TYPE_LOOKUP_CODE NOT IN
('CREDIT', 'DEBIT' )
AND AP_INVOICES_ALL_TBL.VENDOR_ID = PO_VENDOR_SITES_ALL_TBL.VENDOR_ID
AND PO_VENDORS_TBL.VENDOR_ID = PO_VENDOR_SITES_ALL_TBL.VENDOR_ID
AND AP_INVOICES_ALL_TBL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL_TBL.VENDOR_SITE_ID
AND AP_INVOICES_ALL_TBL.INVOICE_ID = ZLV.TRX_ID
AND ZLV.TAX_RATE_CODE = '<TAX-RATE-CODE>'
AND ZLV.SELF_ASSESSED_FLAG <> 'Y'
AND ZLV.CANCEL_FLAG = 'N'
AND AP_INVOICES_PKG.GET_POSTING_STATUS (AP_INVOICES_ALL_TBL.INVOICE_ID) <> 'N'
AND AIL.CANCELLED_FLAG = 'N'
AND ZLV.TAX_AMT_INCLUDED_FLAG='N'
AND NVL(PO_VENDOR_SITES_ALL_TBL.COUNTRY,'<COUNTRY-CODE>')='<COUNTRY-CODE>'
AND NOT EXISTS
(SELECT 'Y'
FROM AP_INVOICE_PAYMENTS_ALL AIP, AP_CHECKS_ALL AC
WHERE AIP.INVOICE_ID = AP_INVOICES_ALL_TBL.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID
AND (AIP.BANK_ACCOUNT_NUM = '<BANK_ACC_NUM>'
OR AC.BANK_ACCOUNT_NAME =
'<BANK-ACC-NAME>'))
AND AIL.LINE_NUMBER = NVL (ZLV.TRX_LINE_NUMBER, AIL.LINE_NUMBER)
AND AP_INVOICES_ALL_TBL.ORG_ID=:P_ORG_ID
AND TRUNC (AP_INVOICES_ALL_TBL.GL_DATE) BETWEEN NVL ('01-'||:P_FROM_DATE,
TRUNC (AP_INVOICES_ALL_TBL.GL_DATE))
AND NVL (LAST_DAY('01-'||:P_TO_DATE),
TRUNC (AP_INVOICES_ALL_TBL.GL_DATE))
ORDER BY AP_INVOICES_ALL_TBL.GL_DATE;