JE_IT_TP_DECL_TYPE_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, MIN(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, decode( ZPTP.party_type_code, 'THIRD_PARTY','20', 'THIRD_PARTY_SITE','10' ) 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_PARTY_TAX_PROFILE ZPTP, 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 = zptp.party_tax_profile_id AND ZRCA.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE' AND ZPTP.party_type_code in ('THIRD_PARTY', 'THIRD_PARTY_SITE') AND ZPTP.PARTY_TAX_PROFILE_ID = decode(ZPTP.PARTY_TYPE_CODE ,'THIRD_PARTY',decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.BILL_FROM_PARTY_TAX_PROF_ID,'O2C', ZRTDT.BILL_TO_PARTY_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.BILL_FROM_PARTY_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.BILL_TO_PARTY_TAX_PROF_ID)) ,'THIRD_PARTY_SITE', decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.BILL_FROM_SITE_TAX_PROF_ID,'O2C', ZRTDT.BILL_TO_SITE_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.BILL_FROM_SITE_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.BILL_TO_SITE_TAX_PROF_ID))) AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION TYPES' 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( ZPTP.party_type_code, 'THIRD_PARTY','21', 'THIRD_PARTY_SITE','11' ) 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_PARTY_TAX_PROFILE ZPTP, 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 = zptp.party_tax_profile_id AND ZRCA.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE' AND ZPTP.party_type_code in ('THIRD_PARTY', 'THIRD_PARTY_SITE') AND ZPTP.PARTY_TAX_PROFILE_ID = decode(ZPTP.PARTY_TYPE_CODE ,'THIRD_PARTY',decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.SHIP_FROM_PARTY_TAX_PROF_ID,'O2C', ZRTDT.SHIP_TO_PARTY_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.SHIP_FROM_PARTY_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.SHIP_TO_PARTY_TAX_PROF_ID)) ,'THIRD_PARTY_SITE', decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.SHIP_FROM_SITE_TAX_PROF_ID,'O2C', ZRTDT.SHIP_TO_SITE_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.SHIP_FROM_SITE_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.SHIP_TO_SITE_TAX_PROF_ID))) AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION TYPES' 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( ZPTP.party_type_code, 'THIRD_PARTY','22', 'THIRD_PARTY_SITE','12' ) 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_PARTY_TAX_PROFILE ZPTP, 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 = zptp.party_tax_profile_id AND ZRCA.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE' AND ZPTP.party_type_code in ('THIRD_PARTY', 'THIRD_PARTY_SITE') AND ZPTP.PARTY_TAX_PROFILE_ID = decode(ZPTP.PARTY_TYPE_CODE ,'THIRD_PARTY',decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.BILL_TO_PARTY_TAX_PROF_ID,'O2C', ZRTDT.BILL_FROM_PARTY_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.BILL_TO_PARTY_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.BILL_FROM_PARTY_TAX_PROF_ID)) ,'THIRD_PARTY_SITE', decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.BILL_TO_SITE_TAX_PROF_ID,'O2C', ZRTDT.BILL_FROM_SITE_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.BILL_TO_SITE_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.BILL_FROM_SITE_TAX_PROF_ID))) AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION TYPES' 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( ZPTP.party_type_code, 'THIRD_PARTY','23', 'THIRD_PARTY_SITE','13' ) 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_PARTY_TAX_PROFILE ZPTP, 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 = zptp.party_tax_profile_id AND ZRCA.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE' AND ZPTP.party_type_code in ('THIRD_PARTY', 'THIRD_PARTY_SITE') AND ZPTP.PARTY_TAX_PROFILE_ID = decode(ZPTP.PARTY_TYPE_CODE ,'THIRD_PARTY',decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.SHIP_TO_PARTY_TAX_PROF_ID,'O2C', ZRTDT.SHIP_FROM_PARTY_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.SHIP_TO_PARTY_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.SHIP_FROM_PARTY_TAX_PROF_ID)) ,'THIRD_PARTY_SITE', decode(ZRTDT.EXTRACT_SOURCE_LEDGER,'P2P', ZRTDT.SHIP_TO_SITE_TAX_PROF_ID,'O2C', ZRTDT.SHIP_FROM_SITE_TAX_PROF_ID,'ZX',decode(ZRTDT.EVENT_CLASS_CODE,'PURCHASE_TRANSACTION',ZRTDT.SHIP_TO_SITE_TAX_PROF_ID,'SALES_TRANSACTION',ZRTDT.SHIP_FROM_SITE_TAX_PROF_ID))) AND ZRT.REPORTING_TYPE_CODE = 'ORA_IT DECLARATION TYPES' 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_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 TYPES' 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, '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_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 TYPES' 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', '70', 'DET_TAX_STATUS', '50', 'DET_TAX_RATE', '30') 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 TYPES'AND ZRTDT.TAX_LINE_ID = ZL.TAX_LINE_ID ) decl_type_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 |