View: IGS_FI_F_TYP_CA_INST_LKP_V

View Text

SELECT FTCI.ROWID ROW_ID
, FTCI.FEE_TYPE FEE_TYPE
, FTCI.FEE_CAL_TYPE FEE_CAL_TYPE
, FTCI.FEE_CI_SEQUENCE_NUMBER FEE_CI_SEQUENCE_NUMBER
, FTCI.FEE_TYPE_CI_STATUS FEE_TYPE_CI_STATUS
, FTCI.START_DT_ALIAS START_DT_ALIAS
, FTCI.START_DAI_SEQUENCE_NUMBER START_DAI_SEQUENCE_NUMBER
, FTCI.END_DT_ALIAS END_DT_ALIAS
, FTCI.END_DAI_SEQUENCE_NUMBER END_DAI_SEQUENCE_NUMBER
, FTCI.RETRO_DT_ALIAS RETRO_DT_ALIAS
, FTCI.RETRO_DAI_SEQUENCE_NUMBER RETRO_DAI_SEQUENCE_NUMBER
, FTCI.S_CHG_METHOD_TYPE S_CHG_METHOD_TYPE
, FTCI.RUL_SEQUENCE_NUMBER RUL_SEQUENCE_NUMBER
, FTCI.SCOPE_RUL_SEQUENCE_NUM SCOPE_RUL_SEQUENCE_NUM
, FTCI.FIN_CAL_TYPE FIN_CAL_TYPE
, FTCI.FIN_CI_SEQUENCE_NUMBER FIN_CI_SEQUENCE_NUMBER
, FTCI.ACCOUNT_CD ACCOUNT_CD
, FTCI.PAYMENT_HIERARCHY_RANK PAYMENT_HIERARCHY_RANK
, FTCI.CREATED_BY CREATED_BY
, FTCI.CREATION_DATE CREATION_DATE
, FTCI.LAST_UPDATED_BY LAST_UPDATED_BY
, FTCI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, FTCI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, FTCI.REQUEST_ID REQUEST_ID
, FTCI.PROGRAM_ID PROGRAM_ID
, FTCI.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, FTCI.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, CI.START_DT START_DT
, CI.END_DT END_DT
, CI.CAL_STATUS CAL_STATUS
, CI.DESCRIPTION DESCRIPTION
, CAT.S_CAL_CAT S_CAL_CAT
, CS.S_CAL_STATUS S_CAL_STATUS
, LKP.MEANING MEANING
, RUD.RULE_DESCRIPTION RULE_DESCRIPTION
, NR.RULE_TEXT RULE_TEXT
, RUD1.RULE_DESCRIPTION SCOPE_RULE_DESCRIPTION
, NR1.RULE_TEXT SCOPE_RULE_TEXT
, FTCI.ELM_RNG_ORDER_NAME ELM_RNG_ORDER_NAME
, FEO.ELM_RNG_ORDER_DESC ELM_RNG_ORDER_DESC
, DAIV.CAL_TYPE STRT_DT_CAL_TYPE
, DAIV.CI_SEQUENCE_NUMBER STRT_DT_CI_SEQUENCE_NUMBER
, DAIV.ALIAS_VAL STRT_DT_ALIAS_VAL
, DA.DESCRIPTION STRT_DT_DESCRIPTION
, DAIV1.CAL_TYPE END_DT_CAL_TYPE
, DAIV1.CI_SEQUENCE_NUMBER END_DT_CI_SEQUENCE_NUMBER
, DAIV1.ALIAS_VAL END_DT_ALIAS_VAL
, DA1.DESCRIPTION END_DT_DESCRIPTION
, DAIV2.CAL_TYPE RETRO_DT_CAL_TYPE
, DAIV2.CI_SEQUENCE_NUMBER RETRO_DT_CI_SEQUENCE_NUMBER
, DAIV2.ALIAS_VAL RETRO_DT_ALIAS_VAL
, DA2.DESCRIPTION RETRO_DT_DESCRIPTION
, FSST.DESCRIPTION FSST_DESCRIPTION
, FSST.CLOSED_IND FSST_CLOSED_IND
, FTCI.SUBACCOUNT_ID SUBACCOUNT_ID
, NULL
, FTCI.INITIAL_DEFAULT_AMOUNT INITIAL_DEFAULT_AMOUNT
, FTCI.ACCT_HIER_ID ACCT_HIER_ID
, FTCI.REC_GL_CCID REC_GL_CCID
, FTCI.REV_ACCOUNT_CD REV_ACCOUNT_CD
, FTCI.REC_ACCOUNT_CD REC_ACCOUNT_CD
, ACH.NAME NAME
, FTCI.RET_GL_CCID RET_GL_CCID
, FTCI.RET_ACCOUNT_CD RET_ACCOUNT_CD
, FTCI.RETENTION_LEVEL_CODE RETENTION_LEVEL_CODE
, LKP2.MEANING RETENTION_LEVEL_MEANING
, FTCI.COMPLETE_RET_FLAG COMPLETE_RET_FLAG
, FTCI.NONZERO_BILLABLE_CP_FLAG NONZERO_BILLABLE_CP_FLAG
, FTCI.MAX_CHG_ELEMENTS MAX_CHG_ELEMENTS
, CI.ALTERNATE_CODE
FROM IGS_FI_F_TYP_CA_INST FTCI
, IGS_CA_INST CI
, IGS_CA_TYPE CAT
, IGS_CA_STAT CS
, IGS_LOOKUP_VALUES LKP
, IGS_RU_DESCRIPTION RUD
, IGS_RU_NAMED_RULE NR
, IGS_RU_DESCRIPTION RUD1
, IGS_RU_NAMED_RULE NR1
, IGS_FI_ELM_RNG_ORDS FEO
, IGS_CA_DA_INST_V DAIV
, IGS_CA_DA DA
, IGS_CA_DA_INST_V DAIV1
, IGS_CA_DA DA1
, IGS_CA_DA_INST_V DAIV2
, IGS_CA_DA DA2
, IGS_FI_FEE_STR_STAT FSST
, IGS_FI_HIER_ACCOUNTS ACH
, IGS_LOOKUP_VALUES LKP2
WHERE (CI.CAL_TYPE = FTCI.FEE_CAL_TYPE
AND CI.SEQUENCE_NUMBER = FTCI.FEE_CI_SEQUENCE_NUMBER
AND CAT.CAL_TYPE = CI.CAL_TYPE
AND CS.CAL_STATUS = CI.CAL_STATUS )
AND (LKP.LOOKUP_CODE (+) = FTCI.S_CHG_METHOD_TYPE
AND LKP.LOOKUP_TYPE (+) = 'CHG_METHOD')
AND NR.RUD_SEQUENCE_NUMBER = RUD.SEQUENCE_NUMBER(+)
AND NR.RUL_SEQUENCE_NUMBER(+) = FTCI.RUL_SEQUENCE_NUMBER AND (DAIV.DT_ALIAS = FTCI.START_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FTCI.START_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FTCI.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FTCI.FEE_CI_SEQUENCE_NUMBER
AND DA.DT_ALIAS = DAIV.DT_ALIAS)
AND (DAIV1.DT_ALIAS = FTCI.END_DT_ALIAS
AND DAIV1.SEQUENCE_NUMBER = FTCI.END_DAI_SEQUENCE_NUMBER
AND DAIV1.CAL_TYPE = FTCI.FEE_CAL_TYPE
AND DAIV1.CI_SEQUENCE_NUMBER = FTCI.FEE_CI_SEQUENCE_NUMBER
AND DA1.DT_ALIAS = DAIV1.DT_ALIAS)
AND (DAIV2.DT_ALIAS(+) = FTCI.RETRO_DT_ALIAS
AND DAIV2.SEQUENCE_NUMBER(+) = FTCI.RETRO_DAI_SEQUENCE_NUMBER
AND DA2.DT_ALIAS(+)= FTCI.RETRO_DT_ALIAS)
AND DAIV2.CAL_TYPE(+) = FTCI.FEE_CAL_TYPE
AND DAIV2.CI_SEQUENCE_NUMBER(+) = FTCI.FEE_CI_SEQUENCE_NUMBER
AND FSST.FEE_STRUCTURE_STATUS = FTCI.FEE_TYPE_CI_STATUS
AND ACH.ACCT_HIER_ID(+) = FTCI.ACCT_HIER_ID
AND (FTCI.RETENTION_LEVEL_CODE = LKP2.LOOKUP_CODE(+)
AND LKP2.LOOKUP_TYPE (+) = 'IGS_FI_RET_LEVEL')
AND NR1.RUD_SEQUENCE_NUMBER = RUD1.SEQUENCE_NUMBER(+)
AND NR1.RUL_SEQUENCE_NUMBER(+) = FTCI.SCOPE_RUL_SEQUENCE_NUM
AND FTCI.ELM_RNG_ORDER_NAME = FEO.ELM_RNG_ORDER_NAME (+)

Columns