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 |