VIEW: APPS.IGS_FI_FEE_AS_RATE_F_TYPE_V

Source

View Text - Preformatted

SELECT far.FEE_TYPE, far.fee_cal_type, far.fee_ci_sequence_number, far.s_relation_type, fcfl.FEE_CAT, far.rate_number, far.location_cd, far.ATTENDANCE_TYPE, far.ATTENDANCE_MODE, far.order_of_precedence, far.GOVT_HECS_PAYMENT_OPTION, far.govt_hecs_cntrbtn_band, far.chg_rate, far.unit_class, SUBSTR(IGS_FI_GEN_001.finp_get_currency(far.fee_cal_type, far.fee_ci_sequence_number, far.s_relation_type, far.FEE_TYPE, fcfl.FEE_CAT),1,15) currency_cd, far.created_by, far.creation_date, far.last_updated_by, far.last_update_date, far.last_update_login , far.residency_status_cd, far.course_cd, far.version_number, far.org_party_id, hp.party_number org_unit_cd, far.class_standing, far.UNIT_SET_CD UNIT_SET_CD, far.US_VERSION_NUMBER US_VERSION_NUMBER FROM IGS_FI_FEE_AS_RATE far, IGS_FI_F_CAT_FEE_LBL fcfl , HZ_PARTIES HP WHERE far.s_relation_type = 'FCFL' AND far.logical_delete_dt IS NULL AND fcfl.FEE_CAT = far.FEE_CAT AND fcfl.fee_cal_type = far.fee_cal_type AND fcfl.fee_ci_sequence_number = far.fee_ci_sequence_number AND fcfl.FEE_TYPE = far.FEE_TYPE AND far.org_party_id = hp.party_id (+) UNION ALL SELECT far.FEE_TYPE, far.fee_cal_type, far.fee_ci_sequence_number, far.s_relation_type, fcfl.FEE_CAT, far.rate_number, far.location_cd, far.ATTENDANCE_TYPE, far.ATTENDANCE_MODE, far.order_of_precedence, far.GOVT_HECS_PAYMENT_OPTION, far.govt_hecs_cntrbtn_band, far.chg_rate, far.unit_class, SUBSTR(IGS_FI_GEN_001.finp_get_currency(far.fee_cal_type, far.fee_ci_sequence_number, far.s_relation_type, far.FEE_TYPE, fcfl.FEE_CAT),1,15) currency_cd, far.created_by, far.creation_date, far.last_updated_by, far.last_update_date, far.last_update_login , far.residency_status_cd, far.course_cd, far.version_number, far.org_party_id, hp.party_number org_unit_cd, far.class_standing, far.UNIT_SET_CD UNIT_SET_CD, far.US_VERSION_NUMBER US_VERSION_NUMBER FROM IGS_FI_FEE_AS_RATE far, IGS_FI_F_CAT_FEE_LBL fcfl , HZ_PARTIES HP WHERE far.s_relation_type = 'FTCI' AND far.logical_delete_dt IS NULL AND fcfl.FEE_TYPE = far.FEE_TYPE AND fcfl.fee_cal_type = far.fee_cal_type AND fcfl.fee_ci_sequence_number = far.fee_ci_sequence_number AND NOT EXISTS (SELECT '1' FROM IGS_FI_FEE_AS_RATE far1 WHERE far1.FEE_TYPE = fcfl.FEE_TYPE AND far1.fee_cal_type = fcfl.fee_cal_type AND far1.fee_ci_sequence_number = fcfl.fee_ci_sequence_number AND far1.s_relation_type = 'FCFL' AND far1.FEE_CAT = fcfl.FEE_CAT AND far1.logical_delete_dt IS NULL) AND FAR.ORG_PARTY_ID = HP.PARTY_ID (+)

View Text - HTML Formatted

SELECT FAR.FEE_TYPE
, FAR.FEE_CAL_TYPE
, FAR.FEE_CI_SEQUENCE_NUMBER
, FAR.S_RELATION_TYPE
, FCFL.FEE_CAT
, FAR.RATE_NUMBER
, FAR.LOCATION_CD
, FAR.ATTENDANCE_TYPE
, FAR.ATTENDANCE_MODE
, FAR.ORDER_OF_PRECEDENCE
, FAR.GOVT_HECS_PAYMENT_OPTION
, FAR.GOVT_HECS_CNTRBTN_BAND
, FAR.CHG_RATE
, FAR.UNIT_CLASS
, SUBSTR(IGS_FI_GEN_001.FINP_GET_CURRENCY(FAR.FEE_CAL_TYPE
, FAR.FEE_CI_SEQUENCE_NUMBER
, FAR.S_RELATION_TYPE
, FAR.FEE_TYPE
, FCFL.FEE_CAT)
, 1
, 15) CURRENCY_CD
, FAR.CREATED_BY
, FAR.CREATION_DATE
, FAR.LAST_UPDATED_BY
, FAR.LAST_UPDATE_DATE
, FAR.LAST_UPDATE_LOGIN
, FAR.RESIDENCY_STATUS_CD
, FAR.COURSE_CD
, FAR.VERSION_NUMBER
, FAR.ORG_PARTY_ID
, HP.PARTY_NUMBER ORG_UNIT_CD
, FAR.CLASS_STANDING
, FAR.UNIT_SET_CD UNIT_SET_CD
, FAR.US_VERSION_NUMBER US_VERSION_NUMBER
FROM IGS_FI_FEE_AS_RATE FAR
, IGS_FI_F_CAT_FEE_LBL FCFL
, HZ_PARTIES HP
WHERE FAR.S_RELATION_TYPE = 'FCFL'
AND FAR.LOGICAL_DELETE_DT IS NULL
AND FCFL.FEE_CAT = FAR.FEE_CAT
AND FCFL.FEE_CAL_TYPE = FAR.FEE_CAL_TYPE
AND FCFL.FEE_CI_SEQUENCE_NUMBER = FAR.FEE_CI_SEQUENCE_NUMBER
AND FCFL.FEE_TYPE = FAR.FEE_TYPE
AND FAR.ORG_PARTY_ID = HP.PARTY_ID (+) UNION ALL SELECT FAR.FEE_TYPE
, FAR.FEE_CAL_TYPE
, FAR.FEE_CI_SEQUENCE_NUMBER
, FAR.S_RELATION_TYPE
, FCFL.FEE_CAT
, FAR.RATE_NUMBER
, FAR.LOCATION_CD
, FAR.ATTENDANCE_TYPE
, FAR.ATTENDANCE_MODE
, FAR.ORDER_OF_PRECEDENCE
, FAR.GOVT_HECS_PAYMENT_OPTION
, FAR.GOVT_HECS_CNTRBTN_BAND
, FAR.CHG_RATE
, FAR.UNIT_CLASS
, SUBSTR(IGS_FI_GEN_001.FINP_GET_CURRENCY(FAR.FEE_CAL_TYPE
, FAR.FEE_CI_SEQUENCE_NUMBER
, FAR.S_RELATION_TYPE
, FAR.FEE_TYPE
, FCFL.FEE_CAT)
, 1
, 15) CURRENCY_CD
, FAR.CREATED_BY
, FAR.CREATION_DATE
, FAR.LAST_UPDATED_BY
, FAR.LAST_UPDATE_DATE
, FAR.LAST_UPDATE_LOGIN
, FAR.RESIDENCY_STATUS_CD
, FAR.COURSE_CD
, FAR.VERSION_NUMBER
, FAR.ORG_PARTY_ID
, HP.PARTY_NUMBER ORG_UNIT_CD
, FAR.CLASS_STANDING
, FAR.UNIT_SET_CD UNIT_SET_CD
, FAR.US_VERSION_NUMBER US_VERSION_NUMBER
FROM IGS_FI_FEE_AS_RATE FAR
, IGS_FI_F_CAT_FEE_LBL FCFL
, HZ_PARTIES HP
WHERE FAR.S_RELATION_TYPE = 'FTCI'
AND FAR.LOGICAL_DELETE_DT IS NULL
AND FCFL.FEE_TYPE = FAR.FEE_TYPE
AND FCFL.FEE_CAL_TYPE = FAR.FEE_CAL_TYPE
AND FCFL.FEE_CI_SEQUENCE_NUMBER = FAR.FEE_CI_SEQUENCE_NUMBER
AND NOT EXISTS (SELECT '1'
FROM IGS_FI_FEE_AS_RATE FAR1
WHERE FAR1.FEE_TYPE = FCFL.FEE_TYPE
AND FAR1.FEE_CAL_TYPE = FCFL.FEE_CAL_TYPE
AND FAR1.FEE_CI_SEQUENCE_NUMBER = FCFL.FEE_CI_SEQUENCE_NUMBER
AND FAR1.S_RELATION_TYPE = 'FCFL'
AND FAR1.FEE_CAT = FCFL.FEE_CAT
AND FAR1.LOGICAL_DELETE_DT IS NULL)
AND FAR.ORG_PARTY_ID = HP.PARTY_ID (+)