The blog provides Oracle Apps R12 GST Tax tables, Oracle Apps R12 SQL Query to get EBS Taxes with parameters as Operating Unit and GL code , Oracle Apps R12 SQL Query to get GST TDS Calculation Report.
The below given are the main localization Tax Tables in Oracle Apps R12
- JAI_AP_TDS_INVOICES
- AP_INVOICES_ALL
- JAI_TAX_CATEGORIES
- JAI_CMN_TAXES_ALL
- JAI_AP_TDS_VENDOR_HDRS
- JAI_TAX_RATES
- JAI_TAX_RATE_DETAILS
- JAI_PARTY_REGS
- JAI_PARTY_REG_LINES
- JAI_TAX_LINES_ALL
- JAI_TAX_CATEGORIES
- JAI_TAX_TYPES
- JAI_TAX_DET_FCT_LINES_V
Oracle Apps R12 SQL Query to retrieve EBS Taxes with OU and GL Code
SELECT HOU_TBL.ORGANIZATION_ID ORG_ID,
GL_LEDGERS_TBL.NAME GL_LEDGERS_TBLGER,
HOU_TBL.NAME OPERATING_UNIT,
ZX_RATES_VL_TBL.TAX_REGIME_CODE TAX_REGIME_CODE,
ZX_RATES_VL_TBL.TAX TAX_CODE,
ZX_RATES_VL_TBL.TAX_STATUS_CODE TAX_STATUS_CODE,
ZX_RATES_VL_TBL.TAX_RATE_CODE TAX_RATE_CODE,
ZX_RATES_VL_TBL.TAX_JURISDICTION_CODE TAX_JURISDICTION_CODE,
ZX_RATES_VL_TBL.RATE_TYPE_CODE RATE_TYPE_CODE,
ZX_RATES_VL_TBL.PERCENTAGE_RATE PERCENTAGE_RATE,
ZX_RATES_VL_TBL.EFFECTIVE_FROM RATE_EFFECTIVE_FROM,
ZX_RATES_VL_TBL.EFFECTIVE_TO RATE_EFFECTIVE_TO,
ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_CCID TAX_ZX_ACCOUNTS_TBLOUNT_CCID,
GCC_TBL.CONCATENATED_SEGMENTS TAX_ZX_ACCOUNTS_TBLOUNT
FROM
ZX_RATES_VL ZX_RATES_VL_TBL,
ZX_ZX_ACCOUNTS_TBLOUNTS ZX_ACCOUNTS_TBL,
HR_OPERATING_UNITS HOU_TBL,
GL_GL_LEDGERS_TBLGERS GL_LEDGERS_TBL,
GL_CODE_COMBINATIONS_KFV GCC_TBL
WHERE
1=1
AND ZX_RATES_VL_TBL.TAX_REGIME_CODE = 'GR VAT'
AND ZX_RATES_VL_TBL.TAX_RATE_CODE = 'GR_AR_DOM'
AND ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_ENTITY_CODE = 'RATES'
AND ZX_RATES_VL_TBL.ACTIVE_FLAG = 'Y'
AND TRUNC (SYSDATE) BETWEEN
TRUNC (ZX_RATES_VL_TBL.EFFECTIVE_FROM) AND
NVL (TRUNC (ZX_RATES_VL_TBL.EFFECTIVE_TO), TRUNC (SYSDATE) + 1)
AND GL_LEDGERS_TBL.GL_LEDGERS_TBLGER_ID = HOU_TBL.SET_OF_BOOKS_ID
AND GCC_TBL.CODE_COMBINATION_ID = ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_CCID
AND HOU_TBL.ORGANIZATION_ID = ZX_ACCOUNTS_TBL.INTERNAL_ORGANIZATION_ID
AND HOU_TBL.ORGANIZATION_ID=7890
AND ZX_ACCOUNTS_TBL.TAX_ZX_ACCOUNTS_TBLOUNT_ENTITY_ID = ZX_RATES_VL_TBL.TAX_RATE_ID ;
Oracle Apps R12 SQL Query to retrieve GST TDS Calculation Report
SELECT AP_INV_ALL.ORG_ID, POV.VENDOR_NAME, HP.ADDRESS1, HP.ADDRESS2,
HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE, HP.STATE,
POV.SEGMENT1, AP_INV_ALL.INVOICE_ID, AP_INV_ALL.INVOICE_NUM, AP_INV_ALL.INVOICE_DATE,
MAX (TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR')) GL_DATE,
AP_INV_ALL.INVOICE_AMOUNT,
BASE_AMT_TDS TAX_ON,
(SELECT ABS (SUM (AA.AMOUNT))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) PREPAY_ON,
MAX (AP_IDA.GLOBAL_ATTRIBUTE1) TAX_ID, TAX_TDS.INVOICE_NUM TDS_INV_NO,
TAX_TDS.GL_DATE TDS_INV_GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE TDS_VOUCHER_NO,
TAX_TDS.INVOICE_AMOUNT TDS_AMOUNT, TAX_TDS.BASE_AMT_TDS, JIT.TAX_NAME,
JIT.TAX_RATE, JATH.PAN_NO, JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE, JIT.SECTION_TYPE
FROM APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AP_IDA,
APPS.HZ_PARTIES HP,
APPS.FND_USER FU,
APPS.PO_VENDORS POV,
APPS.JAI_CMN_TAXES_ALL JIT,
APPS.JAI_AP_TDS_VENDOR_HDRS JATH,
(SELECT DISTINCT AP_INV_ALL.ATTRIBUTE1, AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.DOC_SEQUENCE_VALUE, AP_INV_ALL.INVOICE_AMOUNT,
AP_INV_ALL.GL_DATE, AP_INV_ALL.INVOICE_ID, JTI.TDS_TAX_ID,
JTI.INVOICE_AMOUNT BASE_AMT_TDS
FROM APPS.JAI_AP_TDS_INVOICES JTI, APPS.AP_INVOICES_ALL AP_INV_ALL
WHERE JTI.TDS_INVOICE_NUM(+) = AP_INV_ALL.INVOICE_NUM
AND AP_INV_ALL.ATTRIBUTE1 IS NOT NULL
AND VENDOR_ID = '2'
) TAX_TDS
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AP_IDA.INVOICE_ID
AND AP_IDA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AP_IDA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND TAX_TDS.ATTRIBUTE1 = TO_CHAR(AP_INV_ALL.INVOICE_ID)
AND NVL (TO_CHAR (TAX_TDS.TDS_TAX_ID), AP_IDA.GLOBAL_ATTRIBUTE1) =
AP_IDA.GLOBAL_ATTRIBUTE1
AND POV.VENDOR_ID = AP_INV_ALL.VENDOR_ID
AND FU.USER_ID = AP_INV_ALL.CREATED_BY
AND POV.PARTY_ID = HP.PARTY_ID
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND AP_INV_ALL.CANCELLED_DATE IS NULL
AND JATH.VENDOR_SITE_ID = AP_INV_ALL.VENDOR_SITE_ID
AND JIT.TAX_ID = AP_IDA.GLOBAL_ATTRIBUTE1
AND AP_IDA.MATCH_STATUS_FLAG = 'A'
AND UPPER (TAX_TDS.INVOICE_NUM) NOT LIKE '%RTN%'
GROUP BY AP_INV_ALL.INVOICE_ID,
AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.INVOICE_DATE,
TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR'),
AP_INV_ALL.INVOICE_AMOUNT,
AP_IDA.GLOBAL_ATTRIBUTE1,
TAX_TDS.INVOICE_NUM,
TAX_TDS.GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE,
TAX_TDS.INVOICE_AMOUNT,
TAX_TDS.BASE_AMT_TDS,
POV.VENDOR_NAME,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
POV.SEGMENT1,
JIT.TAX_NAME,
JIT.TAX_RATE,
JATH.PAN_NO,
JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE,
JIT.SECTION_TYPE
AP_INV_ALL.ORG_ID
UNION ALL
SELECT AP_INV_ALL.ORG_ID, POV.VENDOR_NAME, HP.ADDRESS1, HP.ADDRESS2,
HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE, HP.STATE,
POV.SEGMENT1, AP_INV_ALL.INVOICE_ID, AP_INV_ALL.INVOICE_NUM, AP_INV_ALL.INVOICE_DATE,
MAX (TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR')) GL_DATE,
AP_INV_ALL.INVOICE_AMOUNT,
BASE_AMT_TDS TAX_ON,
(SELECT ABS (SUM (AA.AMOUNT))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AA.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) PREPAY_ON,
MAX (AP_IDA.GLOBAL_ATTRIBUTE1) TAX_ID, TAX_TDS.INVOICE_NUM TDS_INV_NO,
TAX_TDS.GL_DATE TDS_INV_GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE TDS_VOUCHER_NO,
TAX_TDS.INVOICE_AMOUNT TDS_AMOUNT, TAX_TDS.BASE_AMT_TDS, NULL TAX_NAME,
TAX_TDS.TAX_RATE, JPL.REGISTRATION_NUMBER, TAX_TDS.SECTION_CODE,
(SELECT REPORTING_CODE FROM APPS.JAI_REPORTING_ASSOCIATIONS_V JRA,APPS.JAI_PARTY_REGS_V JPR
WHERE REGIME_CODE IN ('TDS','GST')
AND ENTITY_ID=PARTY_REG_ID
AND PARTY_ID=POV.VENDOR_ID
AND ORG_ID=AP_INV_ALL.ORG_ID
AND REPORTING_TYPE_NAME='VENDOR TYPE'
AND EFFECTIVE_TO IS NULL
AND PARTY_SITE_ID=AP_INV_ALL.VENDOR_SITE_ID
AND ROWNUM=1) TDS_VENDOR_TYPE_LOOKUP_CODE, NULL SECTION_CODE
FROM APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AP_IDA,
APPS.HZ_PARTIES HP,
APPS.FND_USER FU,
APPS.PO_VENDORS POV,
APPS.JAI_PARTY_REG_LINES JPL,
APPS.JAI_PARTY_REGS JPR,
(SELECT DISTINCT AP_INV_ALL.ATTRIBUTE1, AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.DOC_SEQUENCE_VALUE, AP_INV_ALL.INVOICE_AMOUNT,
AP_INV_ALL.GL_DATE, AP_INV_ALL.INVOICE_ID, JTI.TDS_TAX_ID,
JTI.INVOICE_AMOUNT BASE_AMT_TDS,
JTI.INVOICE_ID TDS_INVOICE_ID,
JTRL.TAX_RATE_PERCENTAGE TAX_RATE,
JTI.TDS_SECTION SECTION_CODE
FROM APPS.JAI_AP_TDS_INVOICES JTI,
APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.JAI_TAX_CATEGORIES JTC,
APPS.JAI_TAX_CATEGORY_LINES JTL,
APPS.JAI_TAX_RATES JTR,
APPS.JAI_TAX_RATE_DETAILS JTRL
WHERE JTI.TDS_INVOICE_NUM(+) = AP_INV_ALL.INVOICE_NUM
AND JTC.TAX_CATEGORY_ID = JTL.TAX_CATEGORY_ID
AND JTC.TAX_CATEGORY_ID = JTI.TAX_CATEGORY_ID
AND JTC.ORG_ID = AP_INV_ALL.ORG_ID
AND JTC.EFFECTIVE_TO IS NULL
AND JTRL.EFFECTIVE_TO IS NULL
AND JTRL.TAX_RATE_DETAIL_CODE = 'RATE_DETAILS'
AND JTL.TAX_RATE_ID = JTR.TAX_RATE_ID
AND JTL.TAX_TYPE_ID = JTR.TAX_TYPE_ID
AND JTR.TAX_RATE_ID = JTRL.TAX_RATE_ID
AND VENDOR_ID = '2'
) TAX_TDS
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AP_IDA.INVOICE_ID
AND AP_IDA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND TAX_TDS.TDS_INVOICE_ID = AP_INV_ALL.INVOICE_ID
AND POV.VENDOR_ID = AP_INV_ALL.VENDOR_ID
AND FU.USER_ID = AP_INV_ALL.CREATED_BY
AND POV.PARTY_ID = HP.PARTY_ID
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND AP_INV_ALL.CANCELLED_DATE IS NULL
AND JPR.PARTY_REG_ID = JPL.PARTY_REG_ID
AND AP_INV_ALL.ORG_ID = JPR.ORG_ID
AND JPL.REGISTRATION_TYPE_CODE = 'PAN'
AND JPL.EFFECTIVE_TO IS NULL
AND AP_INV_ALL.VENDOR_ID = JPR.PARTY_ID
AND AP_INV_ALL.VENDOR_SITE_ID = JPR.PARTY_SITE_ID
AND JPR.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND JPR.SUPPLIER_FLAG = 'Y'
AND AP_IDA.MATCH_STATUS_FLAG = 'A'
AND UPPER (TAX_TDS.INVOICE_NUM) NOT LIKE '%RTN%'
GROUP BY AP_INV_ALL.INVOICE_ID,
AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.INVOICE_DATE,
TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR'),
AP_INV_ALL.INVOICE_AMOUNT,
AP_IDA.GLOBAL_ATTRIBUTE1,
TAX_TDS.INVOICE_NUM,
TAX_TDS.GL_DATE,
TAX_TDS.DOC_SEQUENCE_VALUE,
TAX_TDS.INVOICE_AMOUNT,
TAX_TDS.BASE_AMT_TDS,
POV.VENDOR_NAME,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
POV.SEGMENT1,
TAX_TDS.TAX_RATE,
JPL.REGISTRATION_NUMBER,
TAX_TDS.SECTION_CODE,
AP_INV_ALL.ORG_ID,
POV.VENDOR_ID,
AP_INV_ALL.VENDOR_SITE_ID
UNION ALL
SELECT AP_INV_ALL.ORG_ID, POV.VENDOR_NAME, HP.ADDRESS1, HP.ADDRESS2,
HP.ADDRESS3, HP.ADDRESS4, HP.CITY, HP.POSTAL_CODE, HP.STATE,
POV.SEGMENT1, AP_INV_ALL.INVOICE_ID, AP_INV_ALL.INVOICE_NUM, AP_INV_ALL.INVOICE_DATE,
MAX (TO_DATE (AP_IDA.ACCOUNTING_DATE, 'DD-MON-RRRR')),
AP_INV_ALL.INVOICE_AMOUNT,
(SELECT SUM (AA.AMOUNT)
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) TAX_ON,
(SELECT ABS (SUM (AA.AMOUNT))
FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL AA
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AA.INVOICE_ID
AND AA.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AA.GLOBAL_ATTRIBUTE1 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AA.ORG_ID) PREPAY_ON,
MAX (AP_IDA.GLOBAL_ATTRIBUTE2) TAX_ID, WCT.INVOICE_NUM TDS_INV_NO,
WCT.GL_DATE TDS_INV_GL_DATE,
WCT.DOC_SEQUENCE_VALUE TDS_VOUCHER_NO,
WCT.INVOICE_AMOUNT TDS_AMOUNT, WCT.BASE_AMT_TDS, NULL TAX_NAME,
JIT.TAX_RATE, JATH.PAN_NO, JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE, JIT.SECTION_TYPE
FROM APPS.AP_INVOICES_ALL AP_INV_ALL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AP_IDA,
APPS.HZ_PARTIES HP,
APPS.FND_USER FU,
APPS.PO_VENDORS POV,
-- PO_VENDOR_SITES_ALL POVS,
APPS.JAI_CMN_TAXES_ALL JIT,
APPS.JAI_AP_TDS_VENDOR_HDRS JATH,
(SELECT DISTINCT AP_INV_ALL.ATTRIBUTE1, AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.DOC_SEQUENCE_VALUE, AP_INV_ALL.INVOICE_AMOUNT,
AP_INV_ALL.GL_DATE, AP_INV_ALL.INVOICE_ID, JTI.TDS_TAX_ID,
JTI.INVOICE_AMOUNT BASE_AMT_TDS
FROM APPS.JAI_AP_TDS_INVOICES JTI,APPS.AP_INVOICES_ALL AP_INV_ALL
WHERE JTI.TDS_INVOICE_NUM(+) = AP_INV_ALL.INVOICE_NUM
AND AP_INV_ALL.ATTRIBUTE1 IS NOT NULL
AND VENDOR_ID = '2'
) WCT
WHERE 1 = 1
AND AP_INV_ALL.INVOICE_ID = AP_IDA.INVOICE_ID
AND AP_IDA.LINE_TYPE_LOOKUP_CODE != 'PREPAY'
AND AP_IDA.GLOBAL_ATTRIBUTE2 IS NOT NULL
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND WCT.ATTRIBUTE1 = TO_CHAR(AP_INV_ALL.INVOICE_ID)
AND NVL (TO_CHAR (WCT.TDS_TAX_ID), AP_IDA.GLOBAL_ATTRIBUTE1) =
AP_IDA.GLOBAL_ATTRIBUTE1
AND POV.VENDOR_ID = AP_INV_ALL.VENDOR_ID
AND FU.USER_ID = AP_INV_ALL.CREATED_BY
AND POV.PARTY_ID = HP.PARTY_ID
AND AP_INV_ALL.ORG_ID = AP_IDA.ORG_ID
AND AP_INV_ALL.CANCELLED_DATE IS NULL
AND JATH.VENDOR_SITE_ID = AP_INV_ALL.VENDOR_SITE_ID
AND JIT.TAX_ID = AP_IDA.GLOBAL_ATTRIBUTE2
AND AP_IDA.MATCH_STATUS_FLAG = 'A'
AND UPPER (WCT.INVOICE_NUM) NOT LIKE '%RTN%'
GROUP BY AP_INV_ALL.INVOICE_ID,
AP_INV_ALL.INVOICE_NUM,
AP_INV_ALL.INVOICE_DATE,
AP_INV_ALL.INVOICE_AMOUNT,
AP_IDA.GLOBAL_ATTRIBUTE1,
WCT.INVOICE_NUM,
WCT.GL_DATE,
WCT.DOC_SEQUENCE_VALUE,
WCT.INVOICE_AMOUNT,
WCT.BASE_AMT_TDS,
POV.VENDOR_NAME,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
POV.SEGMENT1,
JIT.TAX_NAME,
JIT.TAX_RATE,
JATH.PAN_NO,
JIT.SECTION_CODE,
JATH.TDS_VENDOR_TYPE_LOOKUP_CODE,
JIT.SECTION_TYPE,
AP_INV_ALL.ORG_ID