Oracle Apps R12 SQL to get GST TDS Tax Report

23 Nov 2020 oracleappsadmin

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