View: IGS_FI_ELM_RANGE_RT_V

View Text

SELECT ER.FEE_TYPE
, ER.FEE_CAL_TYPE
, ER.FEE_CI_SEQUENCE_NUMBER
, ER.S_RELATION_TYPE
, FCFL.FEE_CAT
, ER.LOWER_RANGE
, ER.UPPER_RANGE
, ER.S_CHG_METHOD_TYPE OVERRIDE_CHG_METHOD_TYPE
, ER.RANGE_NUMBER
, ERR.RATE_NUMBER
, FTFARV.LOCATION_CD
, FTFARV.ATTENDANCE_TYPE
, FTFARV.ATTENDANCE_MODE
, FTFARV.RESIDENCY_STATUS_CD
, FTFARV.COURSE_CD
, FTFARV.VERSION_NUMBER
, FTFARV.ORG_UNIT_CD
, FTFARV.CLASS_STANDING
, FTFARV.ORDER_OF_PRECEDENCE
, FTFARV.GOVT_HECS_PAYMENT_OPTION
, FTFARV.GOVT_HECS_CNTRBTN_BAND
, FTFARV.CHG_RATE
, SUBSTR(IGS_FI_GEN_001.FINP_GET_CURRENCY(ER.FEE_CAL_TYPE
, ER.FEE_CI_SEQUENCE_NUMBER
, ER.S_RELATION_TYPE
, ER.FEE_TYPE
, FCFL.FEE_CAT)
, 1
, 15) CURRENCY_CD
, ERR.CREATED_BY
, ERR.CREATION_DATE
, ERR.LAST_UPDATED_BY
, ERR.LAST_UPDATE_DATE
, ERR.LAST_UPDATE_LOGIN
, FTFARV.UNIT_SET_CD
, FTFARV.US_VERSION_NUMBER
FROM IGS_FI_ELM_RANGE ER
, IGS_FI_F_CAT_FEE_LBL FCFL
, IGS_FI_ELM_RANGE_RT ERR
, IGS_FI_FEE_AS_RATE_F_TYPE_V FTFARV
WHERE ER.S_RELATION_TYPE = 'FCFL'
AND ER.LOGICAL_DELETE_DT IS NULL
AND FCFL.FEE_CAT = ER.FEE_CAT
AND FCFL.FEE_CAL_TYPE = ER.FEE_CAL_TYPE
AND FCFL.FEE_CI_SEQUENCE_NUMBER = ER.FEE_CI_SEQUENCE_NUMBER
AND FCFL.FEE_TYPE = ER.FEE_TYPE
AND ERR.FEE_TYPE = ER.FEE_TYPE
AND ERR.FEE_CAL_TYPE = ER.FEE_CAL_TYPE
AND ERR.FEE_CI_SEQUENCE_NUMBER = ER.FEE_CI_SEQUENCE_NUMBER
AND ERR.S_RELATION_TYPE = ER.S_RELATION_TYPE
AND ERR.RANGE_NUMBER = ER.RANGE_NUMBER
AND ERR.FEE_CAT = ER.FEE_CAT
AND ERR.LOGICAL_DELETE_DT IS NULL
AND FTFARV.FEE_TYPE = ERR.FEE_TYPE
AND FTFARV.FEE_CAL_TYPE = ERR.FEE_CAL_TYPE
AND FTFARV.FEE_CI_SEQUENCE_NUMBER = ERR.FEE_CI_SEQUENCE_NUMBER
AND FTFARV.S_RELATION_TYPE = ERR.S_RELATION_TYPE
AND FTFARV.RATE_NUMBER = ERR.RATE_NUMBER
AND FTFARV.FEE_CAT = ERR.FEE_CAT UNION ALL SELECT ER.FEE_TYPE
, ER.FEE_CAL_TYPE
, ER.FEE_CI_SEQUENCE_NUMBER
, ER.S_RELATION_TYPE
, FCFL.FEE_CAT
, ER.LOWER_RANGE
, ER.UPPER_RANGE
, ER.S_CHG_METHOD_TYPE OVERRIDE_CHG_METHOD_TYPE
, ER.RANGE_NUMBER
, ERR.RATE_NUMBER
, FTFARV.LOCATION_CD
, FTFARV.ATTENDANCE_TYPE
, FTFARV.ATTENDANCE_MODE
, FTFARV.RESIDENCY_STATUS_CD
, FTFARV.COURSE_CD
, FTFARV.VERSION_NUMBER
, FTFARV.ORG_UNIT_CD
, FTFARV.CLASS_STANDING
, FTFARV.ORDER_OF_PRECEDENCE
, FTFARV.GOVT_HECS_PAYMENT_OPTION
, FTFARV.GOVT_HECS_CNTRBTN_BAND
, FTFARV.CHG_RATE
, SUBSTR(IGS_FI_GEN_001.FINP_GET_CURRENCY(ER.FEE_CAL_TYPE
, ER.FEE_CI_SEQUENCE_NUMBER
, ER.S_RELATION_TYPE
, ER.FEE_TYPE
, FCFL.FEE_CAT)
, 1
, 15) CURRENCY_CD
, ERR.CREATED_BY
, ERR.CREATION_DATE
, ERR.LAST_UPDATED_BY
, ERR.LAST_UPDATE_DATE
, ERR.LAST_UPDATE_LOGIN
, FTFARV.UNIT_SET_CD
, FTFARV.US_VERSION_NUMBER FROM IGS_FI_ELM_RANGE ER
, IGS_FI_ELM_RANGE_RT ERR
, IGS_FI_FEE_AS_RATE_F_TYPE_V FTFARV
, IGS_FI_F_CAT_FEE_LBL FCFL WHERE ER.S_RELATION_TYPE = 'FTCI'
AND ER.LOGICAL_DELETE_DT IS NULL
AND ERR.FEE_TYPE = ER.FEE_TYPE
AND ERR.FEE_CAL_TYPE = ER.FEE_CAL_TYPE
AND ERR.FEE_CI_SEQUENCE_NUMBER = ER.FEE_CI_SEQUENCE_NUMBER
AND ERR.S_RELATION_TYPE = ER.S_RELATION_TYPE
AND ERR.RANGE_NUMBER = ER.RANGE_NUMBER
AND ERR.LOGICAL_DELETE_DT IS NULL
AND FTFARV.FEE_TYPE = ERR.FEE_TYPE
AND FTFARV.FEE_CAL_TYPE = ERR.FEE_CAL_TYPE
AND FTFARV.FEE_CI_SEQUENCE_NUMBER = ERR.FEE_CI_SEQUENCE_NUMBER
AND FTFARV.S_RELATION_TYPE = ERR.S_RELATION_TYPE
AND FTFARV.RATE_NUMBER = ERR.RATE_NUMBER
AND FCFL.FEE_TYPE = ER.FEE_TYPE
AND FCFL.FEE_CAL_TYPE = ER.FEE_CAL_TYPE
AND FCFL.FEE_CI_SEQUENCE_NUMBER = ER.FEE_CI_SEQUENCE_NUMBER
AND NOT EXISTS (SELECT '1' FROM IGS_FI_ELM_RANGE ER1 WHERE ER1.FEE_TYPE = FCFL.FEE_TYPE
AND ER1.FEE_CAL_TYPE = FCFL.FEE_CAL_TYPE
AND ER1.FEE_CI_SEQUENCE_NUMBER = FCFL.FEE_CI_SEQUENCE_NUMBER
AND ER1.S_RELATION_TYPE = 'FCFL'
AND ER1.FEE_CAT = FCFL.FEE_CAT
AND ER1.LOGICAL_DELETE_DT IS NULL)

Columns