VIEW: APPS.IGS_FI_ELM_RANGE_RT_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)