Query for Tax Type Form Data Extraction

SELECT

JR.REGIME_CODE "TAX REGIME CODE",

JR.REGIME_NAME "TAX REGIME NAME",

JTT.TAX_TYPE_CODE "TAX TYPE CODE",

JTT.TAX_TYPE_NAME "TAX TYPE NAME",

TO_CHAR(JTT.EFFECTIVE_FROM, 'DD-MON-RRRR') "START DATE",

TO_CHAR(JTT.EFFECTIVE_TO, 'DD-MON-RRRR') "END DATE",

JTT.RECOVERABLE_FLAG "RECOVERABLE",

JTT.ALLOW_ABATEMENT "ALLOW ABATEMENT",

JTT.OFFSET_FLAG "APPLICABLE FOR OFSET",

JTT.SELF_ASSESSED_FLAG "SELF ASSESSED / REVERSE CHARGE",

JTT.REPORTING_ONLY_FLAG "SET TAX TYPE FOR REPORTING PURPOSE ONLY",

JTT.UPDATE_VENDOR_ON_TRANSACTION "UPDATE VENDOR ON TRANSACTION",

JTT.WTHLD_TRX_APPLICABLE_FLAG "WITHHOLDING TAX APPLICABLE",

JTT.TAX_POINT_BASIS "TAX POINT BASIS",

GLL.NAME "LEDGER",

HOU.NAME "OPERATING UNIT",

HOU1.NAME "INVENTORY ORGANIZATION",

HRL.LOCATION_CODE "LOCATION",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.INTERIM_RECOVERY_CCID) "INTERIM RECOVERY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.RECOVERY_CCID) "RECOVERY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.INTERIM_LIABILITY_CCID) "INTERIM LIABILITY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.LIABILITY_CCID) "LIABILITY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.EXPENSE_CCID) "EXPENSE ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.ROUNDING_CCID) "ROUNDING ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.SUSPENSE_CCID) "SUSPENSE ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.ADVRCPT_SUSPENSE_CCID) "ADVANCE SUSPENSE ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.ISO_SUSPENSE_CCID) "ISO SUSPENSE ACCOUNT"

FROM

APPS.JAI_TAX_TYPES JTT,

APPS.JAI_REGIMES JR,

APPS.JAI_TAX_ACCOUNTS JTA,

HR_ALL_ORGANIZATION_UNITS HOU,

HR_ALL_ORGANIZATION_UNITS HOU1,

HR_LOCATIONS HRL,

GL_LEDGERS GLL

WHERE

JR.REGIME_ID=JTT.REGIME_ID

AND

JTT.TAX_TYPE_ID=JTA.TAX_ACCOUNT_ENTITY_ID

AND

JTA.ORG_ID=HOU.ORGANIZATION_ID(+)

AND

JTA.ORGANIZATION_ID=HOU1.ORGANIZATION_ID(+)

AND

JTA.LOCATION_ID=HRL.LOCATION_ID(+)

AND

JTA.LEDGER_ID=GLL.LEDGER_ID(+)

AND

JTA.LEDGER_ID=114                           -------(Pass Ledger ID)

ORDER BY JR.REGIME_CODE, JTT.TAX_TYPE_CODE;

#oraclebsr12 #br100 #taxtypeform #queryextraction #oraclefinancials #ofi