[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
When selecting from view IGS_FI_FEE_TRG_GRP_V, replacing column trigger_type with trigger_type_code.
This change has been made in the procedure finpl_val_trig_group
rnirwani 25-Apr-02 Obsoleting the procedure finp_get_dj_totals,
since this is not being used.
Bug# 2329407
SYkrishn 02-APR-2002 Bug 2293676
Added functions finp_get_planned_credits_ind and
finp_get_total_planned_credits
schodava 21-Jan-2002 Enh # 2187247
Added functions FINP_GET_LFCI_RELN
and FINP_CHK_LFCI_RELN
Modified functions finp_get_hecs_amt_pd,
finp_get_hecs_fee, finp_get_tuition_fee
******************************************************************/
FUNCTION check_stdnt_prg_att_liable(
p_n_person_id IN PLS_INTEGER,
p_v_course_cd IN VARCHAR2,
p_n_course_version IN PLS_INTEGER,
p_v_fee_cat IN VARCHAR2,
p_v_fee_type IN VARCHAR2,
p_v_s_fee_trigger_cat IN VARCHAR2,
p_v_fee_cal_type IN VARCHAR2,
p_n_fee_ci_seq_number IN PLS_INTEGER,
p_n_adm_appl_number IN NUMBER,
p_v_adm_nom_course_cd IN VARCHAR2,
p_n_adm_seq_number IN NUMBER,
p_d_commencement_dt IN DATE,
p_d_disc_dt IN DATE,
p_v_cal_type IN VARCHAR2,
p_v_location_cd IN VARCHAR2,
p_v_attendance_mode IN VARCHAR2,
p_v_attendance_type IN VARCHAR2
) RETURN VARCHAR2 AS
/*----------------------------------------------------------------------------
|| Created By : UMESH UDAYAPRAKASH
|| Created On : 06-JAN-2004
|| Purpose :Function To Identify Whther A Student Program Attempt Is Liable
|| For A Fee Category Fee Liability
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
----------------------------------------------------------------------------*/
CURSOR c_fcfldate IS
SELECT TRUNC(da1.alias_val) start_dt_alias_val,
TRUNC(da2.alias_val) end_dt_alias_val
FROM igs_fi_f_cat_fee_lbl_v fcflv,
igs_ca_da_inst_v da1,
igs_ca_da_inst_v da2
WHERE da1.dt_alias = fcflv.start_dt_alias
AND da1.sequence_number = fcflv.start_dai_sequence_number
AND da1.cal_type = fcflv.fee_cal_type
AND da1.ci_sequence_number = fcflv.fee_ci_sequence_number
AND da1.alias_val IS NOT NULL
AND da2.dt_alias = fcflv.end_dt_alias
AND da2.sequence_number = fcflv.end_dai_sequence_number
AND da2.cal_type = fcflv.fee_cal_type
AND da2.ci_sequence_number = fcflv.fee_ci_sequence_number
AND da2.alias_val IS NOT NULL
AND fcflv.fee_cat = p_v_fee_cat
AND fcflv.fee_type = p_v_fee_type
AND fcflv.fee_cal_type = p_v_fee_cal_type
AND fcflv.fee_ci_sequence_number = p_n_fee_ci_seq_number;
SELECT currency_cd
FROM igs_fi_control;
SELECT currency_cd
FROM IGS_FI_FEE_CAT fc
WHERE FEE_CAT = cp_fee_cat;
SELECT DISTINCT
s_relation_type
FROM IGS_FI_FEE_AS_RATE
WHERE FEE_TYPE = cp_fee_type AND
fee_cal_type = cp_fee_cal_type AND
fee_ci_sequence_number = cp_fee_ci_seq_num AND
NVL(FEE_CAT, cp_fee_cat) = cp_fee_cat;
SELECT fas.s_transaction_type
FROM IGS_FI_FEE_AS fas,
IGS_LOOKUPS_view strty
WHERE fas.person_id = cp_person_id AND
fas.fee_type = cp_fee_type AND
fas.fee_cal_type = cp_fee_cal_type AND
NVL(fas.FEE_CAT, ' ') = NVL(cp_fee_cat, ' ') AND
NVL(fas.course_cd, ' ') = NVL(cp_course_cd, ' ') AND
fas.logical_delete_dt IS NULL AND
fas.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
strty.lookup_code = fas.s_transaction_type AND
strty.lookup_type = 'TRANSACTION_TYPE' AND
strty.transaction_cat = cp_transaction_cat AND
strty.system_generated_ind = 'N';
SELECT DECODE(cp_dt_alias_column_name,
'START_DT_ALIAS', ftci.start_dt_alias,
'END_DT_ALIAS', ftci.end_dt_alias,
'RETRO_DT_ALIAS', ftci.retro_dt_alias),
DECODE(cp_dai_seq_num_column_name,
'START_DAI_SEQUENCE_NUMBER', ftci.start_dai_sequence_number,
'END_DAI_SEQUENCE_NUMBER', ftci.end_dai_sequence_number,
'RETRO_DAI_SEQUENCE_NUMBER', ftci.retro_dai_sequence_number)
FROM IGS_FI_F_TYP_CA_INST ftci
WHERE ftci.FEE_TYPE = cp_fee_type AND
ftci.fee_cal_type = cp_fee_cal_type AND
ftci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
SELECT DECODE(cp_dt_alias_column_name,
'START_DT_ALIAS', fcci.start_dt_alias,
'END_DT_ALIAS', fcci.end_dt_alias,
'RETRO_DT_ALIAS', fcci.retro_dt_alias),
DECODE(cp_dai_seq_num_column_name,
'START_DAI_SEQUENCE_NUMBER', fcci.start_dai_sequence_number,
'END_DAI_SEQUENCE_NUMBER', fcci.end_dai_sequence_number,
'RETRO_DAI_SEQUENCE_NUMBER', fcci.retro_dai_sequence_number)
FROM IGS_FI_F_CAT_CA_INST fcci
WHERE fcci.FEE_CAT = cp_fee_cat AND
fcci.fee_cal_type = cp_fee_cal_type AND
fcci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
SELECT ft.s_fee_trigger_cat
FROM IGS_FI_FEE_TYPE ft
WHERE ft.FEE_TYPE = p_fee_type;
SELECT ctft.COURSE_TYPE
FROM IGS_PS_TYPE_FEE_TRG ctft,
IGS_PS_VER cv
WHERE ctft.FEE_CAT = p_fee_cat AND
ctft.fee_cal_type = p_fee_cal_type AND
ctft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
ctft.FEE_TYPE = p_fee_type AND
cv.course_cd = p_course_cd AND
cv.version_number = p_version_number AND
cv.COURSE_TYPE = ctft.COURSE_TYPE AND
ctft.logical_delete_dt IS NULL;
SELECT cgft.course_group_cd
FROM IGS_PS_GRP_FEE_TRG cgft,
IGS_PS_GRP_MBR cgm
WHERE cgft.FEE_CAT = p_fee_cat AND
cgft.fee_cal_type = p_fee_cal_type AND
cgft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
cgft.FEE_TYPE = p_fee_type AND
cgm.course_cd = p_course_cd AND
cgm.version_number = p_version_number AND
cgm.course_group_cd = cgft.course_group_cd AND
cgft.logical_delete_dt IS NULL;
SELECT cft.fee_trigger_group_number
FROM IGS_PS_FEE_TRG cft
WHERE cft.FEE_CAT = p_fee_cat AND
cft.fee_cal_type = p_fee_cal_type AND
cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
cft.FEE_TYPE = p_fee_type AND
cft.course_cd = p_course_cd AND
(cft.version_number IS NULL OR
cft.version_number = p_version_number) AND
p_cal_type LIKE NVL(cft.CAL_TYPE, '%') AND
p_location_cd LIKE NVL(cft.location_cd, '%') AND
p_attendance_mode LIKE NVL(cft.ATTENDANCE_MODE, '%') AND
p_attendance_type LIKE NVL(cft.ATTENDANCE_TYPE, '%') AND
cft.logical_delete_dt IS NULL;
SELECT uft.unit_cd,
uft.version_number,
uft.CAL_TYPE,
uft.ci_sequence_number,
uft.location_cd,
uft.UNIT_CLASS,
uft.fee_trigger_group_number
FROM IGS_FI_UNIT_FEE_TRG uft
WHERE uft.FEE_CAT = p_fee_cat AND
uft.fee_cal_type = p_fee_cal_type AND
uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
uft.FEE_TYPE = p_fee_type AND
uft.logical_delete_dt IS NULL;
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua,
IGS_LOOKUPS_view suas
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_cd = cp_unit_cd AND
(cp_version_number IS NULL OR
sua.version_number = cp_version_number) AND
(cp_cal_type IS NULL OR
sua.CAL_TYPE = cp_cal_type) AND
(cp_ci_sequence_number IS NULL OR
sua.ci_sequence_number = cp_ci_sequence_number) AND
(cp_location_cd IS NULL OR
sua.location_cd = cp_location_cd) AND
(cp_unit_class IS NULL OR
sua.UNIT_CLASS = cp_unit_class) AND
suas.lookup_code = sua.unit_attempt_status AND
suas.lookup_type = 'UNIT_ATTEMPT_STATUS' AND
suas.fee_ass_ind = 'Y';
SELECT usft.unit_set_cd,
usft.version_number,
usft.fee_trigger_group_number
FROM IGS_EN_UNITSETFEETRG usft
WHERE usft.FEE_CAT = p_fee_cat AND
usft.fee_cal_type = p_fee_cal_type AND
usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
usft.FEE_TYPE = p_fee_type AND
usft.logical_delete_dt IS NULL;
SELECT 'X'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = cp_unit_set_cd AND
susa.us_version_number = cp_version_number AND
susa.student_confirmed_ind = 'Y' AND
(susa.selection_dt IS NOT NULL AND
TRUNC(SYSDATE) >= TRUNC(susa.selection_dt)) AND
(susa.end_dt IS NULL OR
TRUNC(SYSDATE) <= TRUNC(susa.end_dt)) AND
(susa.rqrmnts_complete_dt IS NULL OR
TRUNC(SYSDATE) <= TRUNC(susa.rqrmnts_complete_dt));
SELECT lkp.lookup_code trigger_type_code,
cft.course_cd code,
cft.version_number
FROM IGS_PS_FEE_TRG cft,
IGS_PS_VER crv,
IGS_LOOKUP_VALUES lkp
WHERE cft.FEE_CAT = p_fee_cat AND
cft.fee_cal_type = p_fee_cal_type AND
cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
cft.FEE_TYPE = p_fee_type AND
cft.fee_trigger_group_number = p_fee_trigger_group_number AND
lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
lkp.lookup_code = 'COURSE' AND
cft.fee_trigger_group_number IS NOT NULL AND
cft.logical_delete_dt IS NULL AND
cft.course_cd = crv.course_cd AND
(cft.version_number = crv.version_number OR
(cft.version_number IS NULL AND
crv.version_number = ( SELECT MAX(crv2.version_number)
FROM IGS_PS_VER crv2
WHERE crv2.course_cd = crv.course_cd)));
SELECT lkp.lookup_code trigger_type_code,
uft.unit_cd code,
uft.version_number
FROM IGS_FI_UNIT_FEE_TRG uft,
IGS_PS_UNIT_VER uv,
IGS_LOOKUP_VALUES lkp
WHERE uft.FEE_CAT = p_fee_cat AND
uft.fee_cal_type = p_fee_cal_type AND
uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
uft.FEE_TYPE = p_fee_type AND
uft.fee_trigger_group_number = p_fee_trigger_group_number AND
lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
lkp.lookup_code = 'UNIT' AND
uft.fee_trigger_group_number IS NOT NULL AND
uft.logical_delete_dt IS NULL AND
uft.unit_cd = uv.unit_cd AND
(uft.version_number = uv.version_number OR
(uft.version_number IS NULL AND
uv.version_number = ( SELECT MAX(uv2.version_number)
FROM IGS_PS_UNIT_VER uv2
WHERE uv2.unit_cd = uv.unit_cd)));
SELECT usft.unit_set_cd code, usft.version_number,
lkp.lookup_code trigger_type_code
FROM IGS_EN_UNITSETFEETRG usft,
IGS_EN_UNIT_SET us,
IGS_LOOKUP_VALUES lkp
WHERE usft.FEE_CAT = p_fee_cat AND
usft.fee_cal_type = p_fee_cal_type AND
usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
usft.FEE_TYPE = p_fee_type AND
usft.fee_trigger_group_number = p_fee_trigger_group_number AND
lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
lkp.lookup_code = 'UNITSET' AND
usft.fee_trigger_group_number IS NOT NULL AND
usft.logical_delete_dt IS NULL AND
usft.unit_set_cd = us.unit_set_cd AND
usft.version_number = us.version_number;
SELECT uft.unit_cd,
uft.version_number,
uft.CAL_TYPE,
uft.ci_sequence_number,
uft.location_cd,
uft.UNIT_CLASS,
uft.fee_trigger_group_number
FROM IGS_FI_UNIT_FEE_TRG uft
WHERE uft.FEE_CAT = p_fee_cat AND
uft.fee_cal_type = p_fee_cal_type AND
uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
uft.FEE_TYPE = p_fee_type AND
uft.unit_cd = cp_unit_cd AND
(uft.version_number IS NULL OR
uft.version_number = cp_version_number) AND
uft.logical_delete_dt IS NULL;
SELECT usft.unit_set_cd,
usft.version_number,
usft.fee_trigger_group_number
FROM IGS_EN_UNITSETFEETRG usft
WHERE usft.FEE_CAT = p_fee_cat AND
usft.fee_cal_type = p_fee_cal_type AND
usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
usft.FEE_TYPE = p_fee_type AND
usft.unit_set_cd = cp_unit_set_cd AND
usft.version_number = cp_version_number AND
usft.logical_delete_dt IS NULL;
SELECT daiv.alias_val
FROM IGS_FI_FEE_RET_SCHD frtns,
IGS_CA_DA_INST_V daiv
WHERE frtns.fee_cal_type = cp_fee_cal_type AND
frtns.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
frtns.s_relation_type = cp_s_relation_type AND
NVL(frtns.FEE_TYPE,'NULL') = NVL(cp_fee_type,'NULL') AND
NVL(frtns.FEE_CAT,'NULL') = NVL(cp_fee_cat,'NULL') AND
daiv.DT_ALIAS =frtns.DT_ALIAS AND
daiv.sequence_number = frtns.dai_sequence_number AND
daiv.CAL_TYPE = frtns.fee_cal_type AND
daiv.ci_sequence_number =frtns.fee_ci_sequence_number AND
daiv.alias_val > cp_alias_val
ORDER BY daiv.alias_val ASC;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_FI_F_TYP_CA_INST ftci
WHERE ftci.FEE_TYPE = cp_fee_type AND
ftci.fee_cal_type = cp_fee_cal_type AND
ftci.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
daiv.DT_ALIAS = ftci.end_dt_alias AND
daiv.sequence_number = ftci.end_dai_sequence_number AND
daiv.CAL_TYPE = ftci.fee_cal_type AND
daiv.ci_sequence_number = ftci.fee_ci_sequence_number;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_FI_F_CAT_CA_INST fcci
WHERE fcci.FEE_CAT = cp_fee_cat AND
fcci.fee_cal_type = cp_fee_cal_type AND
fcci.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
daiv.DT_ALIAS = fcci.end_dt_alias AND
daiv.sequence_number = fcci.end_dai_sequence_number AND
daiv.CAL_TYPE = fcci.fee_cal_type AND
daiv.ci_sequence_number = fcci.fee_ci_sequence_number;
SELECT SUM(fas.transaction_amount/fas.exchange_rate)
FROM IGS_FI_FEE_TYPE ft,
IGS_FI_FEE_AS fas
WHERE ft.s_fee_type = cst_hecs AND
fas.person_id = p_person_id AND
fas.course_cd = p_course_cd AND
fas.FEE_TYPE = ft.FEE_TYPE AND
fas.S_TRANSACTION_TYPE in
(SELECT strty.lookup_code
FROM IGS_LOOKUPS_view strty
WHERE strty.lookup_code = fas.S_TRANSACTION_TYPE AND
strty.lookup_type = 'TRANSACTION_TYPE' AND
strty.transaction_cat = cst_payment) AND
fas.S_TRANSACTION_TYPE <> cst_discount AND
fas.logical_delete_dt IS NULL AND
fas.fee_cal_type = cp_fee_cal_type AND
fas.fee_ci_sequence_number = cp_fee_ci_sequence_number;
SELECT SUM(fadv.transaction_amount/fadv.exchange_rate) local_assessment_amount
FROM IGS_FI_FEE_TYPE ft,
IGS_FI_FEE_AS fadv
WHERE ft.s_fee_type = cst_hecs
AND fadv.person_id = p_person_id
AND ((fadv.course_cd = p_course_cd) OR ( fadv.course_cd IS NULL AND p_course_cd IS NULL))
AND fadv.FEE_TYPE = ft.FEE_TYPE
AND fadv.fee_cal_type = cp_fee_cal_type
AND fadv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND fadv.logical_delete_dt is NULL;
SELECT HECS_PAYMENT_OPTION
FROM IGS_EN_STDNTPSHECSOP
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
TRUNC(cp_effective_dt) >= TRUNC(start_dt) AND
TRUNC(cp_effective_dt) <= TRUNC(NVL(end_dt, cp_effective_dt));
SELECT SUM(fadv.transaction_amount/fadv.exchange_rate) local_assessment_amount
FROM IGS_FI_FEE_TYPE ft,
IGS_FI_FEE_AS fadv
WHERE ft.s_fee_type = cst_tuition
AND fadv.person_id = p_person_id
AND ((fadv.course_cd = p_course_cd ) OR (fadv.course_cd IS NULL AND p_course_cd IS NULL))
AND fadv.FEE_TYPE = ft.FEE_TYPE
AND fadv.fee_cal_type = cp_fee_cal_type
AND fadv.fee_ci_sequence_number = cp_fee_ci_sequence_number
AND fadv.logical_delete_dt is NULL;
SELECT sub_cal_type,
sub_ci_sequence_number,
sup_cal_type,
sup_ci_sequence_number
FROM igs_ca_inst_rel cir,
igs_ca_type ct1,
igs_ca_type ct2
WHERE cir.sub_cal_type = ct1.cal_type
AND cir.sup_cal_type = ct2.cal_type
AND ct1.s_cal_cat IN ('LOAD','FEE')
AND ct2.s_cal_cat IN ('LOAD','FEE')
AND ((sub_cal_type = p_cal_type
AND sub_ci_sequence_number = p_ci_sequence_number)
OR (sup_cal_type = p_cal_type
AND sup_ci_sequence_number = p_ci_sequence_number));
SELECT planned_credits_ind
FROM igs_fi_control;
SELECT person_id
FROM igs_fi_parties_v
WHERE person_id = p_person_id;
SELECT
SUM(disb.disb_net_amt )
FROM
igf_aw_awd_disb disb,
igf_aw_award awd,
igf_aw_fund_mast fmast,
igf_ap_fa_base_rec base,
igf_ap_batch_aw_map bm
WHERE disb.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND awd.base_id = base.base_id
AND fmast.ci_cal_type = bm.ci_cal_type
AND fmast.ci_sequence_number = bm.ci_sequence_number
AND awd.award_status ='ACCEPTED'
AND disb.trans_type = 'P'
AND disb.show_on_bill = 'Y'
AND base.person_id = p_person_id
AND TRUNC(disb.disb_date) BETWEEN TRUNC(p_start_date) and TRUNC(p_end_date)
AND bm.award_year_status_code = 'O';
SELECT
SUM(disb.disb_net_amt )
FROM
igf_aw_awd_disb disb,
igf_aw_award awd,
igf_aw_fund_mast fmast,
igf_ap_fa_base_rec base,
igf_ap_batch_aw_map bm
WHERE disb.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND awd.base_id = base.base_id
AND fmast.ci_cal_type = bm.ci_cal_type
AND fmast.ci_sequence_number = bm.ci_sequence_number
AND awd.award_status ='ACCEPTED'
AND disb.trans_type = 'P'
AND disb.show_on_bill = 'Y'
AND base.person_id = p_person_id
AND TRUNC(disb.disb_date) <= TRUNC(p_end_date)
AND bm.award_year_status_code = 'O';