VIEW: APPS.IGS_FI_F_CAT_FEE_LBL_SCA_V

Source

View Text - Preformatted

SELECT sca.person_id , sca.course_cd, sca.version_number, sca.CAL_TYPE, sca.location_cd, sca.ATTENDANCE_MODE, sca.ATTENDANCE_TYPE, IGS_FI_GEN_001.finp_get_hecs_pymnt_optn(sca.person_id, sca.course_cd, SYSDATE, fcflv.fee_cal_type, fcflv.fee_ci_sequence_number, fcflv.start_dt_alias, fcflv.start_dai_sequence_number, fcflv.end_dt_alias, fcflv.end_dai_sequence_number) HECS_PAYMENT_OPTION, sca.course_attempt_status, sca.commencement_dt, sca.discontinued_dt, scas.fee_ass_ind, sca.FEE_CAT, fcflv.fee_cal_type, fcflv.fee_ci_sequence_number, fcflv.FEE_TYPE, ft.s_fee_type, ft.s_fee_trigger_cat, fcflv.fee_liability_status, fcflv.start_dt_alias, fcflv.start_dai_sequence_number, IGS_CA_GEN_001.calp_get_alias_val(fcflv.start_dt_alias, fcflv.start_dai_sequence_number, fcflv.fee_cal_type, fcflv.fee_ci_sequence_number) START_DT, fcflv.end_dt_alias, fcflv.end_dai_sequence_number, IGS_CA_GEN_001.calp_get_alias_val(fcflv.end_dt_alias, fcflv.end_dai_sequence_number, fcflv.fee_cal_type, fcflv.fee_ci_sequence_number) END_DT, fcflv.retro_dt_alias, fcflv.retro_dai_sequence_number, IGS_CA_GEN_001.calp_get_alias_val(fcflv.retro_dt_alias, fcflv.retro_dai_sequence_number, fcflv.fee_cal_type, fcflv.fee_ci_sequence_number) RETRO_DT, fcflv.s_chg_method_type, fcflv.rul_sequence_number, fcflv.currency_cd, fcflv.CREATED_BY, fcflv.CREATION_DATE, fcflv.LAST_UPDATED_BY, fcflv.LAST_UPDATE_DATE, fcflv.LAST_UPDATE_LOGIN FROM IGS_EN_STDNT_PS_ATT sca, IGS_LOOKUPS_VIEW scas, IGS_FI_F_CAT_FEE_LBL_V fcflv, IGS_FI_FEE_TYPE ft WHERE SCAS.LOOKUP_TYPE = 'CRS_ATTEMPT_STATUS' and scas.LOOKUP_CODE = sca.course_attempt_status AND fcflv.FEE_CAT = sca.FEE_CAT AND NVL(sca.commencement_dt, IGS_EN_GEN_002.enrp_get_acad_comm( NULL, NULL, sca.person_id, sca.course_cd, sca.adm_admission_appl_number, sca.adm_nominated_course_cd, sca.adm_sequence_number, 'Y')) IS NOT NULL AND ( ( NVL(sca.commencement_dt, IGS_EN_GEN_002.enrp_get_acad_comm( NULL, NULL, sca.person_id, sca.course_cd, sca.adm_admission_appl_number, sca.adm_nominated_course_cd, sca.adm_sequence_number, 'Y')) > SYSDATE AND TRUNC(NVL(sca.commencement_dt, IGS_EN_GEN_002.enrp_get_acad_comm( NULL, NULL, sca.person_id, sca.course_cd, sca.adm_admission_appl_number, sca.adm_nominated_course_cd, sca.adm_sequence_number, 'Y'))) >= (SELECT TRUNC(daiv.alias_val) FROM IGS_CA_DA_INST_V daiv WHERE daiv.DT_ALIAS = fcflv.start_dt_alias AND daiv.sequence_number = fcflv.start_dai_sequence_number AND daiv.CAL_TYPE = fcflv.fee_cal_type AND daiv.ci_sequence_number = fcflv.fee_ci_sequence_number AND daiv.alias_val IS NOT NULL) AND TRUNC(NVL(sca.commencement_dt, IGS_EN_GEN_002.enrp_get_acad_comm( NULL, NULL, sca.person_id, sca.course_cd, sca.adm_admission_appl_number, sca.adm_nominated_course_cd, sca.adm_sequence_number, 'Y'))) <= (SELECT TRUNC(daiv.alias_val) FROM IGS_CA_DA_INST_V daiv WHERE daiv.DT_ALIAS = fcflv.end_dt_alias AND daiv.sequence_number = fcflv.end_dai_sequence_number AND daiv.CAL_TYPE = fcflv.fee_cal_type AND daiv.ci_sequence_number = fcflv.fee_ci_sequence_number AND daiv.alias_val IS NOT NULL) ) OR ( NVL(sca.commencement_dt, IGS_EN_GEN_002.enrp_get_acad_comm( NULL, NULL, sca.person_id, sca.course_cd, sca.adm_admission_appl_number, sca.adm_nominated_course_cd, sca.adm_sequence_number, 'Y')) < SYSDATE AND TRUNC(SYSDATE) <= (SELECT TRUNC(daiv.alias_val) FROM IGS_CA_DA_INST_V daiv WHERE daiv.DT_ALIAS = fcflv.end_dt_alias AND daiv.sequence_number = fcflv.end_dai_sequence_number AND daiv.CAL_TYPE = fcflv.fee_cal_type AND daiv.ci_sequence_number = fcflv.fee_ci_sequence_number AND daiv.alias_val IS NOT NULL) AND TRUNC(SYSDATE) >= (SELECT TRUNC(daiv.alias_val) FROM IGS_CA_DA_INST_V daiv WHERE daiv.DT_ALIAS = fcflv.start_dt_alias AND daiv.sequence_number = fcflv.start_dai_sequence_number AND daiv.CAL_TYPE = fcflv.fee_cal_type AND daiv.ci_sequence_number = fcflv.fee_ci_sequence_number AND daiv.alias_val IS NOT NULL) ) ) AND (sca.discontinued_dt IS NULL OR (TRUNC(sca.discontinued_dt) >= (SELECT TRUNC(daiv.alias_val) FROM IGS_CA_DA_INST_V daiv WHERE daiv.DT_ALIAS = fcflv.start_dt_alias AND daiv.sequence_number = fcflv.start_dai_sequence_number AND daiv.CAL_TYPE = fcflv.fee_cal_type AND daiv.ci_sequence_number = fcflv.fee_ci_sequence_number AND daiv.alias_val IS NOT NULL) AND TRUNC(sca.discontinued_dt) <= (SELECT TRUNC(daiv.alias_val) FROM IGS_CA_DA_INST_V daiv WHERE daiv.DT_ALIAS = fcflv.end_dt_alias AND daiv.sequence_number = fcflv.end_dai_sequence_number AND daiv.CAL_TYPE = fcflv.fee_cal_type AND daiv.ci_sequence_number = fcflv.fee_ci_sequence_number AND daiv.alias_val IS NOT NULL))) AND ft.FEE_TYPE = fcflv.FEE_TYPE AND (ft.s_fee_trigger_cat = 'INSTITUTN' OR IGS_FI_GEN_001.finp_get_fee_trigger( sca.FEE_CAT, fcflv.fee_cal_type, fcflv.fee_ci_sequence_number, fcflv.FEE_TYPE, ft.s_fee_trigger_cat, sca.person_id, sca.course_cd, sca.version_number, sca.CAL_TYPE, sca.location_cd, sca.ATTENDANCE_MODE, sca.ATTENDANCE_TYPE) IS NOT NULL)

View Text - HTML Formatted

SELECT SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, SCA.CAL_TYPE
, SCA.LOCATION_CD
, SCA.ATTENDANCE_MODE
, SCA.ATTENDANCE_TYPE
, IGS_FI_GEN_001.FINP_GET_HECS_PYMNT_OPTN(SCA.PERSON_ID
, SCA.COURSE_CD
, SYSDATE
, FCFLV.FEE_CAL_TYPE
, FCFLV.FEE_CI_SEQUENCE_NUMBER
, FCFLV.START_DT_ALIAS
, FCFLV.START_DAI_SEQUENCE_NUMBER
, FCFLV.END_DT_ALIAS
, FCFLV.END_DAI_SEQUENCE_NUMBER) HECS_PAYMENT_OPTION
, SCA.COURSE_ATTEMPT_STATUS
, SCA.COMMENCEMENT_DT
, SCA.DISCONTINUED_DT
, SCAS.FEE_ASS_IND
, SCA.FEE_CAT
, FCFLV.FEE_CAL_TYPE
, FCFLV.FEE_CI_SEQUENCE_NUMBER
, FCFLV.FEE_TYPE
, FT.S_FEE_TYPE
, FT.S_FEE_TRIGGER_CAT
, FCFLV.FEE_LIABILITY_STATUS
, FCFLV.START_DT_ALIAS
, FCFLV.START_DAI_SEQUENCE_NUMBER
, IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(FCFLV.START_DT_ALIAS
, FCFLV.START_DAI_SEQUENCE_NUMBER
, FCFLV.FEE_CAL_TYPE
, FCFLV.FEE_CI_SEQUENCE_NUMBER) START_DT
, FCFLV.END_DT_ALIAS
, FCFLV.END_DAI_SEQUENCE_NUMBER
, IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(FCFLV.END_DT_ALIAS
, FCFLV.END_DAI_SEQUENCE_NUMBER
, FCFLV.FEE_CAL_TYPE
, FCFLV.FEE_CI_SEQUENCE_NUMBER) END_DT
, FCFLV.RETRO_DT_ALIAS
, FCFLV.RETRO_DAI_SEQUENCE_NUMBER
, IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(FCFLV.RETRO_DT_ALIAS
, FCFLV.RETRO_DAI_SEQUENCE_NUMBER
, FCFLV.FEE_CAL_TYPE
, FCFLV.FEE_CI_SEQUENCE_NUMBER) RETRO_DT
, FCFLV.S_CHG_METHOD_TYPE
, FCFLV.RUL_SEQUENCE_NUMBER
, FCFLV.CURRENCY_CD
, FCFLV.CREATED_BY
, FCFLV.CREATION_DATE
, FCFLV.LAST_UPDATED_BY
, FCFLV.LAST_UPDATE_DATE
, FCFLV.LAST_UPDATE_LOGIN
FROM IGS_EN_STDNT_PS_ATT SCA
, IGS_LOOKUPS_VIEW SCAS
, IGS_FI_F_CAT_FEE_LBL_V FCFLV
, IGS_FI_FEE_TYPE FT
WHERE SCAS.LOOKUP_TYPE = 'CRS_ATTEMPT_STATUS'
AND SCAS.LOOKUP_CODE = SCA.COURSE_ATTEMPT_STATUS
AND FCFLV.FEE_CAT = SCA.FEE_CAT
AND NVL(SCA.COMMENCEMENT_DT
, IGS_EN_GEN_002.ENRP_GET_ACAD_COMM( NULL
, NULL
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.ADM_ADMISSION_APPL_NUMBER
, SCA.ADM_NOMINATED_COURSE_CD
, SCA.ADM_SEQUENCE_NUMBER
, 'Y')) IS NOT NULL
AND ( ( NVL(SCA.COMMENCEMENT_DT
, IGS_EN_GEN_002.ENRP_GET_ACAD_COMM( NULL
, NULL
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.ADM_ADMISSION_APPL_NUMBER
, SCA.ADM_NOMINATED_COURSE_CD
, SCA.ADM_SEQUENCE_NUMBER
, 'Y')) > SYSDATE
AND TRUNC(NVL(SCA.COMMENCEMENT_DT
, IGS_EN_GEN_002.ENRP_GET_ACAD_COMM( NULL
, NULL
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.ADM_ADMISSION_APPL_NUMBER
, SCA.ADM_NOMINATED_COURSE_CD
, SCA.ADM_SEQUENCE_NUMBER
, 'Y'))) >= (SELECT TRUNC(DAIV.ALIAS_VAL)
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.DT_ALIAS = FCFLV.START_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FCFLV.START_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FCFLV.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FCFLV.FEE_CI_SEQUENCE_NUMBER
AND DAIV.ALIAS_VAL IS NOT NULL)
AND TRUNC(NVL(SCA.COMMENCEMENT_DT
, IGS_EN_GEN_002.ENRP_GET_ACAD_COMM( NULL
, NULL
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.ADM_ADMISSION_APPL_NUMBER
, SCA.ADM_NOMINATED_COURSE_CD
, SCA.ADM_SEQUENCE_NUMBER
, 'Y'))) <= (SELECT TRUNC(DAIV.ALIAS_VAL)
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.DT_ALIAS = FCFLV.END_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FCFLV.END_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FCFLV.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FCFLV.FEE_CI_SEQUENCE_NUMBER
AND DAIV.ALIAS_VAL IS NOT NULL) ) OR ( NVL(SCA.COMMENCEMENT_DT
, IGS_EN_GEN_002.ENRP_GET_ACAD_COMM( NULL
, NULL
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.ADM_ADMISSION_APPL_NUMBER
, SCA.ADM_NOMINATED_COURSE_CD
, SCA.ADM_SEQUENCE_NUMBER
, 'Y')) < SYSDATE
AND TRUNC(SYSDATE) <= (SELECT TRUNC(DAIV.ALIAS_VAL)
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.DT_ALIAS = FCFLV.END_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FCFLV.END_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FCFLV.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FCFLV.FEE_CI_SEQUENCE_NUMBER
AND DAIV.ALIAS_VAL IS NOT NULL)
AND TRUNC(SYSDATE) >= (SELECT TRUNC(DAIV.ALIAS_VAL)
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.DT_ALIAS = FCFLV.START_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FCFLV.START_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FCFLV.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FCFLV.FEE_CI_SEQUENCE_NUMBER
AND DAIV.ALIAS_VAL IS NOT NULL) ) )
AND (SCA.DISCONTINUED_DT IS NULL OR (TRUNC(SCA.DISCONTINUED_DT) >= (SELECT TRUNC(DAIV.ALIAS_VAL)
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.DT_ALIAS = FCFLV.START_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FCFLV.START_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FCFLV.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FCFLV.FEE_CI_SEQUENCE_NUMBER
AND DAIV.ALIAS_VAL IS NOT NULL)
AND TRUNC(SCA.DISCONTINUED_DT) <= (SELECT TRUNC(DAIV.ALIAS_VAL)
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.DT_ALIAS = FCFLV.END_DT_ALIAS
AND DAIV.SEQUENCE_NUMBER = FCFLV.END_DAI_SEQUENCE_NUMBER
AND DAIV.CAL_TYPE = FCFLV.FEE_CAL_TYPE
AND DAIV.CI_SEQUENCE_NUMBER = FCFLV.FEE_CI_SEQUENCE_NUMBER
AND DAIV.ALIAS_VAL IS NOT NULL)))
AND FT.FEE_TYPE = FCFLV.FEE_TYPE
AND (FT.S_FEE_TRIGGER_CAT = 'INSTITUTN' OR IGS_FI_GEN_001.FINP_GET_FEE_TRIGGER( SCA.FEE_CAT
, FCFLV.FEE_CAL_TYPE
, FCFLV.FEE_CI_SEQUENCE_NUMBER
, FCFLV.FEE_TYPE
, FT.S_FEE_TRIGGER_CAT
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, SCA.CAL_TYPE
, SCA.LOCATION_CD
, SCA.ATTENDANCE_MODE
, SCA.ATTENDANCE_TYPE) IS NOT NULL)