1. Tables and Views for Financials
  2. JE_IT_TP_DECL_TYPE_V

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