1. Tables and Views for Financials
  2. JE_IT_TP_REP_GROUPS_V

JE_IT_TP_REP_GROUPS_V

Details

  • Schema: FUSION

  • Object owner: JE

  • Object type: VIEW

Columns

Name

REPORTING_TYPE_CODE

REPORTING_CODE_VALUE

REPORTING_TYPE_ID

REPORTING_CODE_ID

PRIORITY

APPLICATION_ID

ENTITY_CODE

EVENT_CLASS_CODE

TRX_ID

DETAIL_TAX_LINE_ID

REQUEST_ID

Query

SQL_Statement

SELECT

REPORTING_TYPE_CODE, REPORTING_CODE_VALUE, REPORTING_TYPE_ID, REPORTING_CODE_ID, MAX(PRIORITY) PRIORITY, APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, TRX_ID, DETAIL_TAX_LINE_ID, REQUEST_ID

FROM (

SELECT

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

ZRC.REPORTING_CODE_CHAR_VALUE REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID,

'40' priority,

ZRTDT.APPLICATION_ID,

ZRTDT.ENTITY_CODE,

ZRTDT.EVENT_CLASS_CODE,

ZRTDT.TRX_ID,

ZRTDT.DETAIL_TAX_LINE_ID,

ZRTDT.REQUEST_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_B ZRC,

ZX_STATUS_B ZS,

ZX_REP_TRX_DETAIL_T ZRTDT

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRC.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZS.TAX_STATUS_ID

AND ZRCA.ENTITY_CODE = 'ZX_STATUS'

AND ZRTDT.TAX_STATUS_ID = ZS.TAX_STATUS_ID

AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION GROUPS'

UNION ALL

SELECT

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

ZRC.REPORTING_CODE_CHAR_VALUE REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID,

'60' priority,

ZRTDT.APPLICATION_ID,

ZRTDT.ENTITY_CODE,

ZRTDT.EVENT_CLASS_CODE,

ZRTDT.TRX_ID,

ZRTDT.DETAIL_TAX_LINE_ID,

ZRTDT.REQUEST_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_B ZRC,

ZX_RATES_B ZR,

ZX_REP_TRX_DETAIL_T ZRTDT

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRC.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZR.TAX_RATE_ID

AND ZRCA.ENTITY_CODE = 'ZX_RATES'

AND ZRTDT.TAX_RATE_ID = ZR.TAX_RATE_ID

AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION GROUPS'

UNION ALL

SELECT

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

ZRC.REPORTING_CODE_CHAR_VALUE REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID,

decode(ZRL.SERVICE_TYPE_CODE,

'DET_TAX_REGISTRATION', '30',

'DET_TAX_STATUS', '50',

'DET_TAX_RATE', '70') priority,

ZL.APPLICATION_ID,

ZL.ENTITY_CODE,

ZL.EVENT_CLASS_CODE,

ZL.TRX_ID,

ZRTDT.DETAIL_TAX_LINE_ID,

ZRTDT.REQUEST_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_B ZRC,

ZX_RULES_B ZRL,

ZX_PROCESS_RESULTS ZPR,

ZX_LINES ZL,

ZX_REP_TRX_DETAIL_T ZRTDT

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRC.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZPR.RESULT_ID

AND zpr.tax_rule_id = zrl.tax_rule_id

AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS'

AND ZRL.SERVICE_TYPE_CODE in ('DET_TAX_REGISTRATION','DET_TAX_STATUS','DET_TAX_RATE')

AND ZPR.RESULT_ID = decode(ZRL.SERVICE_TYPE_CODE,

'DET_TAX_REGISTRATION', ZL.TAX_REG_NUM_DET_RESULT_ID,

'DET_TAX_STATUS', ZL.STATUS_RESULT_ID,

'DET_TAX_RATE', NVL(ZL.DIRECT_RATE_RESULT_ID, ZL.RATE_RESULT_ID))

AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION GROUPS' AND ZRTDT.TAX_LINE_ID = ZL.TAX_LINE_ID) decl_grp_view

GROUP BY TRX_ID, REPORTING_TYPE_CODE, REPORTING_CODE_VALUE, REPORTING_TYPE_ID, REPORTING_CODE_ID, APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, DETAIL_TAX_LINE_ID, REQUEST_ID