ZX_REP_TRX_RPT_CODE_ASSOC

Details

Schema: FUSION
Object owner: FND
Object type: VIEW

Columns

Name
DETAIL_TAX_LINE_ID REP_CONTEXT_ID TRX_ID TRX_NUMBER REQUEST_ID ENTITY_CODE ENTITY_NAME REPORTING_TYPE_CODE REPORTING_CODE_VALUE REPORTING_TYPE_ID REPORTING_CODE_ID

Name

DETAIL_TAX_LINE_ID

REP_CONTEXT_ID

TRX_ID

TRX_NUMBER

REQUEST_ID

ENTITY_CODE

ENTITY_NAME

REPORTING_TYPE_CODE

REPORTING_CODE_VALUE

REPORTING_TYPE_ID

REPORTING_CODE_ID

Query

SQL_Statement
SELECT DETAIL_TAX_LINE_ID, REP_CONTEXT_ID, TRX_ID, TRX_NUMBER, REQUEST_ID, ENTITY_CODE, ENTITY_NAME, REPORTING_TYPE_CODE, REPORTING_CODE_VALUE, REPORTING_TYPE_ID, REPORTING_CODE_ID FROM ((/QUERY FOR TAX RATES/ SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID, ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID, ZRTD.TRX_ID TRX_ID, ZRTD.TRX_NUMBER TRX_NUMBER, ZRTD.REQUEST_ID REQUEST_ID, ZRCA.ENTITY_CODE ENTITY_CODE, ZR.TAX_RATE_CODE ENTITY_NAME, ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE, TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE, 'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE, 'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE, 'DATE',ZRCV.REPORTING_CODE_DATE_VALUE, 'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE, ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID, ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID FROM ZX_REPORT_CODES_ASSOC ZRCA, ZX_REPORTING_TYPES_B ZRT, ZX_REPORTING_CODES_VL ZRCV, ZX_RATES_B ZR, ZX_REP_TRX_DETAIL_T ZRTD WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID AND ZRCA.ENTITY_ID = ZR.TAX_RATE_ID AND ZRCA.ENTITY_CODE = 'ZX_RATES' AND ZRTD.TAX_RATE_CODE = ZR.TAX_RATE_CODE AND ZRTD.TAX_RATE_ID = ZR.TAX_RATE_ID AND ZRT.HAS_REPORTING_CODES_FLAG='Y' AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)) UNION ALL (/QUERY FOR TAX STATUS/ SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID, ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID, ZRTD.TRX_ID TRX_ID, ZRTD.TRX_NUMBER TRX_NUMBER, ZRTD.REQUEST_ID REQUEST_ID, ZRCA.ENTITY_CODE ENTITY_CODE, ZS.TAX_STATUS_CODE ENTITY_NAME, ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE, TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE, 'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE, 'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE, 'DATE',ZRCV.REPORTING_CODE_DATE_VALUE, 'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE, ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID, ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID FROM ZX_REPORT_CODES_ASSOC ZRCA, ZX_REPORTING_TYPES_B ZRT, ZX_REPORTING_CODES_VL ZRCV, ZX_STATUS_B ZS, ZX_REP_TRX_DETAIL_T ZRTD WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID AND ZRCA.ENTITY_ID = ZS.TAX_STATUS_ID AND ZRCA.ENTITY_CODE = 'ZX_STATUS' AND ZRTD.TAX_STATUS_ID = ZS.TAX_STATUS_ID AND ZRT.HAS_REPORTING_CODES_FLAG='Y' AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)) UNION ALL (/QUERY FOR TAX STATUS RULE/ SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID, ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID, ZRTD.TRX_ID TRX_ID, ZRTD.TRX_NUMBER TRX_NUMBER, ZRTD.REQUEST_ID REQUEST_ID, ZRCA.ENTITY_CODE ENTITY_CODE, ZPR.RATE_RESULT ENTITY_NAME, ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE, TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE, 'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE, 'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE, 'DATE',ZRCV.REPORTING_CODE_DATE_VALUE, 'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE, ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID, ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID FROM ZX_REPORT_CODES_ASSOC ZRCA, ZX_REPORTING_TYPES_B ZRT, ZX_REPORTING_CODES_VL ZRCV, ZX_LINES ZL, ZX_REP_TRX_DETAIL_T ZRTD, ZX_PROCESS_RESULTS ZPR WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID AND ZRCA.ENTITY_ID = ZPR.RESULT_ID AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS' AND ZRTD.TRX_ID = ZL.TRX_ID AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID AND ZPR.RESULT_ID = ZL.STATUS_RESULT_ID AND ZRT.HAS_REPORTING_CODES_FLAG='Y' AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)) UNION ALL (/QUERY FOR DIRECT RATE DETERMINANT RULES/ SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID, ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID, ZRTD.TRX_ID TRX_ID, ZRTD.TRX_NUMBER TRX_NUMBER, ZRTD.REQUEST_ID REQUEST_ID, ZRCA.ENTITY_CODE ENTITY_CODE, ZPR.RATE_RESULT ENTITY_NAME, ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE, TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE, 'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE, 'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE, 'DATE',ZRCV.REPORTING_CODE_DATE_VALUE, 'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE, ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID, ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID FROM ZX_REPORT_CODES_ASSOC ZRCA, ZX_REPORTING_TYPES_B ZRT, ZX_REPORTING_CODES_VL ZRCV, ZX_LINES ZL, ZX_REP_TRX_DETAIL_T ZRTD, ZX_PROCESS_RESULTS ZPR WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID AND ZRCA.ENTITY_ID = ZPR.RESULT_ID AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS' AND ZRTD.TRX_ID = ZL.TRX_ID AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID AND ZPR.RESULT_ID = NVL(ZL.DIRECT_RATE_RESULT_ID, ZL.RATE_RESULT_ID) AND ZRT.HAS_REPORTING_CODES_FLAG='Y' AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)) UNION ALL (/QUERY FOR TAX REGISTRATION RULE/ SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID, ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID, ZRTD.TRX_ID TRX_ID, ZRTD.TRX_NUMBER TRX_NUMBER, ZRTD.REQUEST_ID REQUEST_ID, ZRCA.ENTITY_CODE ENTITY_CODE, ZPR.RATE_RESULT ENTITY_NAME, ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE, TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE, 'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE, 'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE, 'DATE',ZRCV.REPORTING_CODE_DATE_VALUE, 'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE, ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID, ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID FROM ZX_REPORT_CODES_ASSOC ZRCA, ZX_REPORTING_TYPES_B ZRT, ZX_REPORTING_CODES_VL ZRCV, ZX_LINES ZL, ZX_REP_TRX_DETAIL_T ZRTD, ZX_PROCESS_RESULTS ZPR WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID AND ZRCA.ENTITY_ID = ZPR.RESULT_ID AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS' AND ZRTD.TRX_ID = ZL.TRX_ID AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID AND ZPR.RESULT_ID = ZL.TAX_REG_NUM_DET_RESULT_ID AND ZRT.HAS_REPORTING_CODES_FLAG='Y' AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)) UNION ALL (/QUERY FOR TAX CERTIFICATES/ SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID, ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID, ZRTD.TRX_ID TRX_ID, ZRTD.TRX_NUMBER TRX_NUMBER, ZRTD.REQUEST_ID REQUEST_ID, ZRCA.ENTITY_CODE ENTITY_CODE, HP.PARTY_NUMBER ENTITY_NAME, ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE, TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE, 'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE, 'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE, 'DATE',ZRCV.REPORTING_CODE_DATE_VALUE, 'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE, ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID, ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID FROM ZX_REPORT_CODES_ASSOC ZRCA, ZX_REPORTING_TYPES_B ZRT, ZX_REPORTING_CODES_VL ZRCV, ZX_EXEMPTIONS ZE, ZX_REP_TRX_DETAIL_T ZRTD, ZX_LINES ZL, ZX_PARTY_TAX_PROFILE ZPTP, HZ_PARTIES HP WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID AND ZRCA.ENTITY_ID = ZE.TAX_EXEMPTION_ID AND ZRCA.ENTITY_CODE = 'ZX_EXEMPTIONS' AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID AND ZRTD.TRX_ID = ZL.TRX_ID AND ZRT.HAS_REPORTING_CODES_FLAG='Y' AND ZL.TAX_EXEMPTION_ID = ZE.TAX_EXEMPTION_ID AND ZPTP.PARTY_TAX_PROFILE_ID = ZE.PARTY_TAX_PROFILE_ID AND HP.PARTY_ID = ZPTP.PARTY_TAX_PROFILE_ID AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)))

SQL_Statement

SELECT DETAIL_TAX_LINE_ID,

REP_CONTEXT_ID,

TRX_ID,

TRX_NUMBER,

REQUEST_ID,

ENTITY_CODE,

ENTITY_NAME,

REPORTING_TYPE_CODE,

REPORTING_CODE_VALUE,

REPORTING_TYPE_ID,

REPORTING_CODE_ID

FROM ((/*QUERY FOR TAX RATES*/

SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID,

ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID,

ZRTD.TRX_ID TRX_ID,

ZRTD.TRX_NUMBER TRX_NUMBER,

ZRTD.REQUEST_ID REQUEST_ID,

ZRCA.ENTITY_CODE ENTITY_CODE,

ZR.TAX_RATE_CODE ENTITY_NAME,

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE,

'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE,

'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE,

'DATE',ZRCV.REPORTING_CODE_DATE_VALUE,

'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_VL ZRCV,

ZX_RATES_B ZR,

ZX_REP_TRX_DETAIL_T ZRTD

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZR.TAX_RATE_ID

AND ZRCA.ENTITY_CODE = 'ZX_RATES'

AND ZRTD.TAX_RATE_CODE = ZR.TAX_RATE_CODE

AND ZRTD.TAX_RATE_ID = ZR.TAX_RATE_ID

AND ZRT.HAS_REPORTING_CODES_FLAG='Y'

AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE))

UNION ALL

(/*QUERY FOR TAX STATUS*/

SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID,

ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID,

ZRTD.TRX_ID TRX_ID,

ZRTD.TRX_NUMBER TRX_NUMBER,

ZRTD.REQUEST_ID REQUEST_ID,

ZRCA.ENTITY_CODE ENTITY_CODE,

ZS.TAX_STATUS_CODE ENTITY_NAME,

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE,

'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE,

'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE,

'DATE',ZRCV.REPORTING_CODE_DATE_VALUE,

'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_VL ZRCV,

ZX_STATUS_B ZS,

ZX_REP_TRX_DETAIL_T ZRTD

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZS.TAX_STATUS_ID

AND ZRCA.ENTITY_CODE = 'ZX_STATUS'

AND ZRTD.TAX_STATUS_ID = ZS.TAX_STATUS_ID

AND ZRT.HAS_REPORTING_CODES_FLAG='Y'

AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE))

UNION ALL

(/*QUERY FOR TAX STATUS RULE*/

SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID,

ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID,

ZRTD.TRX_ID TRX_ID,

ZRTD.TRX_NUMBER TRX_NUMBER,

ZRTD.REQUEST_ID REQUEST_ID,

ZRCA.ENTITY_CODE ENTITY_CODE,

ZPR.RATE_RESULT ENTITY_NAME,

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE,

'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE,

'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE,

'DATE',ZRCV.REPORTING_CODE_DATE_VALUE,

'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_VL ZRCV,

ZX_LINES ZL,

ZX_REP_TRX_DETAIL_T ZRTD,

ZX_PROCESS_RESULTS ZPR

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZPR.RESULT_ID

AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS'

AND ZRTD.TRX_ID = ZL.TRX_ID

AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID

AND ZPR.RESULT_ID = ZL.STATUS_RESULT_ID

AND ZRT.HAS_REPORTING_CODES_FLAG='Y'

AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE))

UNION ALL

(/*QUERY FOR DIRECT RATE DETERMINANT RULES*/

SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID,

ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID,

ZRTD.TRX_ID TRX_ID,

ZRTD.TRX_NUMBER TRX_NUMBER,

ZRTD.REQUEST_ID REQUEST_ID,

ZRCA.ENTITY_CODE ENTITY_CODE,

ZPR.RATE_RESULT ENTITY_NAME,

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE,

'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE,

'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE,

'DATE',ZRCV.REPORTING_CODE_DATE_VALUE,

'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_VL ZRCV,

ZX_LINES ZL,

ZX_REP_TRX_DETAIL_T ZRTD,

ZX_PROCESS_RESULTS ZPR

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZPR.RESULT_ID

AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS'

AND ZRTD.TRX_ID = ZL.TRX_ID

AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID

AND ZPR.RESULT_ID = NVL(ZL.DIRECT_RATE_RESULT_ID, ZL.RATE_RESULT_ID)

AND ZRT.HAS_REPORTING_CODES_FLAG='Y'

AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE))

UNION ALL

(/*QUERY FOR TAX REGISTRATION RULE*/

SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID,

ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID,

ZRTD.TRX_ID TRX_ID,

ZRTD.TRX_NUMBER TRX_NUMBER,

ZRTD.REQUEST_ID REQUEST_ID,

ZRCA.ENTITY_CODE ENTITY_CODE,

ZPR.RATE_RESULT ENTITY_NAME,

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE,

'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE,

'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE,

'DATE',ZRCV.REPORTING_CODE_DATE_VALUE,

'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_VL ZRCV,

ZX_LINES ZL,

ZX_REP_TRX_DETAIL_T ZRTD,

ZX_PROCESS_RESULTS ZPR

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZPR.RESULT_ID

AND ZRCA.ENTITY_CODE = 'ZX_PROCESS_RESULTS'

AND ZRTD.TRX_ID = ZL.TRX_ID

AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID

AND ZPR.RESULT_ID = ZL.TAX_REG_NUM_DET_RESULT_ID

AND ZRT.HAS_REPORTING_CODES_FLAG='Y'

AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE))

UNION ALL

(/*QUERY FOR TAX CERTIFICATES*/

SELECT ZRTD.DETAIL_TAX_LINE_ID DETAIL_TAX_LINE_ID,

ZRTD.REP_CONTEXT_ID REP_CONTEXT_ID,

ZRTD.TRX_ID TRX_ID,

ZRTD.TRX_NUMBER TRX_NUMBER,

ZRTD.REQUEST_ID REQUEST_ID,

ZRCA.ENTITY_CODE ENTITY_CODE,

HP.PARTY_NUMBER ENTITY_NAME,

ZRT.REPORTING_TYPE_CODE REPORTING_TYPE_CODE,

TO_CHAR(DECODE(ZRT.REPORTING_TYPE_DATATYPE_CODE,

'TEXT',ZRCV.REPORTING_CODE_CHAR_VALUE,

'YES_NO',ZRCV.REPORTING_CODE_CHAR_VALUE,

'DATE',ZRCV.REPORTING_CODE_DATE_VALUE,

'NUMBER',ZRCV.REPORTING_CODE_NUM_VALUE)) REPORTING_CODE_VALUE,

ZRCA.REPORTING_TYPE_ID REPORTING_TYPE_ID,

ZRCA.REPORTING_CODE_ID REPORTING_CODE_ID

FROM ZX_REPORT_CODES_ASSOC ZRCA,

ZX_REPORTING_TYPES_B ZRT,

ZX_REPORTING_CODES_VL ZRCV,

ZX_EXEMPTIONS ZE,

ZX_REP_TRX_DETAIL_T ZRTD,

ZX_LINES ZL,

ZX_PARTY_TAX_PROFILE ZPTP,

HZ_PARTIES HP

WHERE ZRT.REPORTING_TYPE_ID = ZRCA.REPORTING_TYPE_ID

AND ZRCA.REPORTING_CODE_ID = ZRCV.REPORTING_CODE_ID

AND ZRCA.ENTITY_ID = ZE.TAX_EXEMPTION_ID

AND ZRCA.ENTITY_CODE = 'ZX_EXEMPTIONS'

AND ZRTD.TAX_LINE_ID = ZL.TAX_LINE_ID

AND ZRTD.TRX_ID = ZL.TRX_ID

AND ZRT.HAS_REPORTING_CODES_FLAG='Y'

AND ZL.TAX_EXEMPTION_ID = ZE.TAX_EXEMPTION_ID

AND ZPTP.PARTY_TAX_PROFILE_ID = ZE.PARTY_TAX_PROFILE_ID

AND HP.PARTY_ID = ZPTP.PARTY_TAX_PROFILE_ID

AND ZRTD.GL_DATE BETWEEN ZRCA.EFFECTIVE_FROM AND NVL(ZRCA.EFFECTIVE_TO, ZRTD.GL_DATE)))