[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
knaraset 12-May-2003 Modified cursor c_get_todo_ref_csr to select uoo_id in procedure finp_prc_fa_ref_todo,
also added uoo_id in TBH call to todo_ref, as part of MUS build bug 2829262
shtatiko 06-MAY-2003 Enh# 2831569, Modified finp_prc_enr_fee_ass and finp_prc_enr_fa_todo.
shtatiko 30-JAN-2003 Bug# 2765239, Replaced IGS_GE_INVALID_VALUE parameter with more meaningful messages.
Affected procedures are finp_prc_enr_fa_todo, finp_prc_enr_fee_ass and finp_prc_cfar
vchappid 21-Jan-2003 Bug#2711202, in the procedure finp_prc_enr_fa_todo,for the in-out variable for p_creation_dt
parameter in the fee assessment call should be passed as v_creation_dt which is defined in the
Main Procedure Call instead of the local variable defined in the local procedure finp_prc_fa_ref_todo
vchappid 06-Jan-2003 Bug# 2660155, In procedure finp_prc_enr_fee_ass, Modified code to identify distinct persons when user don't
provide Person Id or Person ID Group as an input value to the request.
vchappid 02-Jan-2003 Bug# 2727402, Unhandled Exception should not occur when GL Date passed is invalid
vchappid 11-Nov-02 Bug# 2584986, GL- Interface Build New Date parameter.
p_d_gl_date is added to the finp_prc_enr_fa_todo, finp_prc_enr_fee_ass
procedure specification
In the procedure finp_prc_sca_unconf, in the local procedure finpl_prc_reverse_fee_assess
a new gl_date parameter is passed as system date to the procedure igs_fi_prc_fee_ass.finp_ins_enr_fee_ass call
vchappid 17-Oct-02 Enh bug#2595962.Modified procedures finp_prc_fa_ref_todo,
finp_prc_enr_fa_todo,finp_prc_enr_fee_ass and
finpl_prc_reverse_fee_assess.
jbegum 06-jun-02 As part of bug fix of bug #2318488 the local procedure finp_prc_hecs_pymnt_optn is being
obsoleted
vchappid 24-May-2002 Bug#2228743, in the local procedure finpl_prc_reverse_fee_assess of finp_prc_sca_unconf
fee assessment call has been changed to pass course cd in case of program approach, course type
incase of the career approach and will pass null in the case of primary_career approach
vchappid 21-May-2002 Bug#2374754, removed the clause 'for update NOWAIT' form the cursor c_get_todo_ref_csr
in the local procedure finp_prc_fa_ref_todo, removed the commented code,
In the fee assessment call process mode parameter is incorrectly passed as NULL,
'ACTUAL' is passed instead of NULL, re-initialized the process next record variable
to FASLE, it might have been set to TRUE while processing the previously fetched record
Log messages format is changed
rnirwani 05-May-02 Bug#2329407 removeed reference to IGS_FI_DSBR_SPSHT
rnirwani 25-Apr-02 Bug# 2329407 Modified Procedure: finp_prc_enr_fa_todo
decalaration of cursor variable of cursor c_fee_cal_instance
was pointed to fee calendar from fin calendar
Modified Procedure: finp_prc_enr_fee_ass
decalaration of cursor variable of cursor c_fee_cal_instance
was pointed to fee calendar from fin calendar
Modified the procedure finpl_prc_reverse_fee_assess
Removed the parameters : p_fin_cal_type, p_fin_ci_sequence_number.
Modified the procedure finp_prc_sca_unconf
Altered cursor c_fasdv to not select fin calendar caloumns
Removed passage of fin calendar to invocation of procedure
finpl_prc_reverse_fee_assess
schodava 01-APR-2002 Enh # 2280971
Modified procedure finp_prc_enr_fa_todo
New local procedure finpl_prc_fa_ref_todo
added.
smadathi 03-JAN-2002 Bug 2170429 : removed the private procedure
finpl_prc_end_fee_sponsorship and all the references to it.
vchappid 02-Jan-2002 Enh # 2162747 : Removed the reference to parameter p_fin_cal,
p_c_career parameter is introduced in the fee Assessment routine
schodava 28-NOV-2001 Enh # 2122257 : Implements the CR for 'Fee Category Change'
Change in Procedure finp_prc_sca_unconf
*/
g_d_sysdate CONSTANT DATE := TRUNC(SYSDATE);
SELECT fcflv.fee_type fee_type,
far.chg_rate chg_rate,
fcflv.fee_cal_type fee_cal_type,
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,
far.location_cd chg_rate_location_cd,
far.attendance_type chg_rate_attendance_type,
far.attendance_mode chg_rate_attendance_mode
FROM igs_en_stdnt_ps_att spa,
igs_fi_f_cat_fee_lbl_v fcflv,
igs_fi_fee_str_stat fsst,
igs_fi_fee_as_rate far,
igs_fi_fee_type ft,
igs_fi_fee_as_rt cfar
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND spa.fee_cat = fcflv.fee_cat
AND fcflv.fee_liability_status = fsst.fee_structure_status
AND fsst.s_fee_structure_status = cp_v_s_fee_structure_status
AND fcflv.fee_type = ft.fee_type
AND ft.closed_ind = cp_v_closed_ind
AND ft.s_fee_trigger_cat <> cp_v_s_fee_trigger_cat
AND ft.s_fee_type in (cp_v_s_fee_type1, cp_v_s_fee_type2, cp_v_s_fee_type3)
AND igs_fi_gen_001.check_stdnt_prg_att_liable (spa.person_id,
spa.course_cd,
spa.version_number,
spa.fee_cat,
fcflv.fee_type,
ft.s_fee_trigger_cat,
fcflv.fee_cal_type,
fcflv.fee_ci_sequence_number,
spa.adm_admission_appl_number,
spa.adm_nominated_course_cd,
spa.adm_sequence_number,
spa.commencement_dt,
spa.discontinued_dt,
spa.cal_type,
spa.location_cd,
spa.attendance_mode,
spa.attendance_type) = cp_v_chk_spa_liable
AND far.fee_type = fcflv.fee_type
AND far.fee_cal_type = fcflv.fee_cal_type
AND far.fee_ci_sequence_number = fcflv.fee_ci_sequence_number
AND (
(far.fee_cat = fcflv.fee_cat AND far.s_relation_type = cp_v_s_relation_type1)
OR
(far.fee_cat is NULL and far.s_relation_type = cp_v_s_relation_type2)
)
AND far.logical_delete_dt is NULL
AND NVL(far.location_cd (+), spa.location_cd) = spa.location_cd
AND NVL(far.attendance_type (+),spa.attendance_type) = spa.attendance_type
AND NVL(far.attendance_mode (+),spa.attendance_mode) = spa.attendance_mode
AND NVL(far.course_cd (+), spa.course_cd) = spa.course_cd
AND (
(cfar.person_id = spa.person_id AND cfar.course_cd = spa.course_cd AND cfar.fee_type <> fcflv.fee_type)
OR
(cfar.person_id <> spa.person_id AND cfar.course_cd <> spa.course_cd)
)
UNION
SELECT fcflv.fee_type fee_type,
far.chg_rate chg_rate,
fcflv.fee_cal_type fee_cal_type,
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,
far.location_cd chg_rate_location_cd,
far.attendance_type chg_rate_attendance_type,
far.attendance_mode chg_rate_attendance_mode
FROM igs_en_spa_terms spt,
igs_en_stdnt_ps_att spa,
igs_fi_f_cat_fee_lbl_v fcflv,
igs_fi_fee_str_stat fsst,
igs_fi_fee_as_rate far,
igs_fi_fee_type ft,
igs_fi_fee_as_rt cfar
WHERE spt.person_id = p_person_id
AND spt.program_cd = p_course_cd
AND spt.person_id = spa.person_id
AND spt.program_cd = spa.course_cd
AND spt.fee_cat = fcflv.fee_cat
AND fcflv.fee_liability_status = fsst.fee_structure_status
AND fsst.s_fee_structure_status = cp_v_s_fee_structure_status
AND fcflv.fee_type = ft.fee_type
AND ft.closed_ind = cp_v_closed_ind
AND ft.s_fee_trigger_cat <> cp_v_s_fee_trigger_cat
AND ft.s_fee_type in (cp_v_s_fee_type1, cp_v_s_fee_type2, cp_v_s_fee_type3)
AND igs_fi_gen_001.check_stdnt_prg_att_liable (spt.person_id,
spt.program_cd,
spt.program_version,
spt.fee_cat,
fcflv.fee_type,
ft.s_fee_trigger_cat,
fcflv.fee_cal_type,
fcflv.fee_ci_sequence_number,
spa.adm_admission_appl_number,
spa.adm_nominated_course_cd,
spa.adm_sequence_number,
spa.commencement_dt,
spa.discontinued_dt,
spa.cal_type,
spt.location_cd,
spt.attendance_mode,
spt.attendance_type) = cp_v_chk_spa_liable
AND far.fee_type = fcflv.fee_type
AND far.fee_cal_type = fcflv.fee_cal_type
AND far.fee_ci_sequence_number = fcflv.fee_ci_sequence_number
AND (
(far.fee_cat = fcflv.fee_cat and far.s_relation_type = cp_v_s_relation_type1)
OR
(far.fee_cat is NULL and far.s_relation_type = cp_v_s_relation_type2)
)
AND far.logical_delete_dt is NULL
AND NVL(far.location_cd (+), spt.location_cd) = spt.location_cd
AND NVL(far.attendance_type (+),spt.attendance_type) = spt.attendance_type
AND NVL(far.attendance_mode (+),spt.attendance_mode) = spt.attendance_mode
AND NVL(far.course_cd (+), spt.program_cd) = spt.program_cd
AND (
(cfar.person_id = spt.person_id AND cfar.course_cd = spt.program_cd AND cfar.fee_type <> fcflv.fee_type)
OR
(cfar.person_id <> spt.person_id AND cfar.course_cd <> spt.program_cd)
)
ORDER BY 1,4;
shtatiko 25-NOV-2003 Bug# 3230754, Modified finp_prc_fa_ref_todo. Added check for logical delete date is
added in cursor c_get_todo_ref_csr's where clause.
shtatiko 18-NOV-2003 Enh# 3117341, Added check for profile 'IGS: Charge tuition for Audited Student Attempt'
shtatiko 08-MAY-2003 Enh# 2831569, Added Check for Manage Accounts System Option before running the process.
shtatiko 30-JAN-2003 Bug# 2765239, Replaced IGS_GE_INVALID_VALUE with more meaningful messages.
vchappid 21-Jan-2003 Bug#2711202, for the in-out variable for p_creation_dt parameter in the fee assessment call
should be passed as v_creation_dt which is defined in the Main Procedure Call instead of the
local variable defined in the local procedure finp_prc_fa_ref_todo
vchappid 02-Jan-2003 Bug#2727402, Unhandled Exception should not occur when GL Date passed is invalid
vchappid 21-May-2002 Bug#2374754, removed the clause 'for update NOWAIT' form the cursor c_get_todo_ref_csr
in the local procedure finp_prc_fa_ref_todo, removed the commented code,
In the fee assessment call process mode parameter is incorrectly passed as NULL,
'ACTUAL' is passed instead of NULL, re-initialized the process next record variable
to FASLE, it might have been set to TRUE while processing the previously fetched record
*/
p_fee_cal_type igs_ca_inst.cal_type%TYPE ;
SELECT 'x'
FROM IGS_FI_F_TYP_CA_INST ftci
WHERE ftci.fee_cal_type = cp_fee_cal_type AND
ftci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
SELECT std.rowid,
std.person_id,
std.s_student_todo_type,
std.sequence_number,
std.todo_dt
FROM IGS_PE_STD_TODO std
WHERE std.s_student_todo_type = cst_fee_recalc AND
std.logical_delete_dt IS NULL
ORDER BY std.person_id;
SELECT 'x'
FROM igs_pe_std_todo_ref
WHERE person_id = cp_n_person_id
AND s_student_todo_type = cp_v_todo_type
AND sequence_number = cp_n_seq_num
AND logical_delete_dt IS NULL;
SELECT rowid,
person_id,
s_student_todo_type,
sequence_number,
reference_number,
cal_type,
ci_sequence_number,
course_cd,
unit_cd,
other_reference,
logical_delete_dt,
uoo_id
FROM igs_pe_std_todo_ref
WHERE person_id = cp_person_id
AND sequence_number = cp_sequence_number
AND s_student_todo_type = cst_fee_recalc
AND (cal_type = cp_v_load_cal_type OR cp_v_load_cal_type IS NULL)
AND (ci_sequence_number = cp_n_load_ci_seq_num OR cp_n_load_ci_seq_num IS NULL)
AND logical_delete_dt IS NULL -- Added this as part of Bug# 3230754
ORDER BY cal_type, ci_sequence_number;
igs_pe_std_todo_ref_pkg.update_row(
x_rowid => lp_todo_ref_rec.rowid,
x_person_id => lp_todo_ref_rec.person_id ,
x_s_student_todo_type => lp_todo_ref_rec.s_student_todo_type ,
x_sequence_number => lp_todo_ref_rec.sequence_number ,
x_reference_number => lp_todo_ref_rec.reference_number,
x_cal_type => lp_todo_ref_rec.cal_type,
x_ci_sequence_number => lp_todo_ref_rec.ci_sequence_number,
x_course_cd => lp_todo_ref_rec.course_cd,
x_unit_cd => lp_todo_ref_rec.unit_cd,
x_other_reference => lp_todo_ref_rec.other_reference,
x_logical_delete_dt => SYSDATE,
x_mode => 'R',
x_uoo_id => lp_todo_ref_rec.uoo_id
);
igs_pe_std_todo_pkg.update_row(
x_rowid => v_student_todo_rec.rowid,
x_person_id => v_student_todo_rec.person_id ,
x_s_student_todo_type => v_student_todo_rec.s_student_todo_type ,
x_sequence_number => v_student_todo_rec.sequence_number ,
x_todo_dt => v_student_todo_rec.todo_dt ,
x_logical_delete_dt => SYSDATE,
x_mode => 'R');
uudayapr 15-dec-2003 Bug#3080983 Modified the Cursor c_fee_ass_debt to select data from IGS_FI_FEE_AS instead of
IGS_FI_FEE_ASS_DEBT_V .
shtatiko 18-NOV-2003 Enh# 3117341, Added check for profile 'IGS: Charge tuition for Audited Student Attempt'
pathipat 04-Nov-2003 Bug: 3151102 - Removed conditions before setting p_create_dt to v_create_dt
pathipat 17-Oct-2003 Bug: 3151102 - Added begin-end block for exception handling
pathipat 07-Oct-2003 Bug 3122652 - Logged messg IGF_AP_INVALID_QUERY if any error occurs
while obtaining the dynamic sql for the person id group, added validation for
person id group
pathipat 23-Sep-2003 Bug: 3151102 - Called finp_ins_enr_fee_ass in a begin-end block
with exception handling when called for a Person ID Group.
pathipat 09-Sep-2003 Bug 3122652: Replaced call to igf_ap_ss_pkg.get_pid() with
call to igs_pe_dynamic_persid_group.igs_get_dynamic_sql()
Increased length of l_dynamic_sql to 32767 from 2000
shtatiko 28-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option.
Implemeted Dynamic Person Group feature for group id parameter.
shtatiko 30-JAN-2003 Bug# 2765239, Replaced IGS_GE_INVALID_VALUE message with more meaningful messages
vchappid 06-Jan-2003 Bug# 2660155, Modified code to identify distinct persons when user don't provide
Person Id or Person ID Group as an input value to the request.
*/
-- prameters process mode , init process prior calendar instance
-- and person id group have been added as a part
-- of the build for fee calc undertaken in July 2001.
-- Bug# 1851586
BEGIN -- finp_prc_enr_fee_ass
-- Module to control processing fee assessments
DECLARE
v_message_name VARCHAR2(30);
SELECT 'x'
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 'x'
FROM igs_pe_persid_group_all
WHERE group_id = cp_n_pers_grp_id
AND TRUNC(create_dt) <= g_d_sysdate
AND NVL(closed_ind, g_v_ind_no) = g_v_ind_no;
SELECT waiver_calc_flag
FROM igs_fi_f_cat_fee_lbl_all
WHERE fee_cat = cp_v_fee_cat OR cp_v_fee_cat IS NULL
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_seq_num
AND fee_type = cp_v_fee_type;
SELECT fee_calc_mthd_code
FROM igs_fi_control;
p_delete_sca_ind OUT NOCOPY VARCHAR2 )
AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
shtatiko 24-DEC-2003 Enh# 3167098, Modified finpl_prc_this_crs_liable and finpl_prc_another_crs_liable
uudayapr 15-dec-2003 Bug#3080983 Modified the cursor c_fasdv to fetch Data from IGS_FI_FEE_AS instead of IGS_FI_FEE_ASS_DEBT_V.
vchappid 24-May-2002 Bug#2228743, in the local procedure finpl_prc_reverse_fee_assess of finp_prc_sca_unconf
fee assessment call has been changed to pass course cd in case of program approach, course type
incase of the career approach and will pass null in the case of primary_career approach
SCHODAVA 28-NOV-2001 Enh # 2122257
(SFCR015 : Change In Fee Category)
Modified local procedures FINPL_PRC_THIS_CRS_LIABLE
and FINPL_PRC_ANOTHER_CRS_LIABLE
(reverse chronological order - newest change first)
***************************************************************/
BEGIN -- finp_prc_sca_unconf
-- Process finance details for unconfirmed student IGS_PS_COURSE attempts. This
-- routine is called from ADMP_DEL_SCA_UNCONF when deleting unconfirmed
-- student IGS_PS_COURSE attempts.
-- IGS_GE_NOTE: The call to IGS_FI_PRC_FEE_ASS.finp_ins_enr_fee performs a commit,
-- this means that all outstanding transactions will be committed.
DECLARE
cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
v_delete_sca_ind VARCHAR2(1) := 'Y';
SELECT DISTINCT fas.course_cd
FROM IGS_FI_FEE_AS fas
WHERE fas.person_id = p_person_id;
SELECT fasdv.course_cd,
fasdv.fee_cal_type,
fasdv.fee_ci_sequence_number,
fasdv.FEE_TYPE,
fasdv.FEE_CAT,
SUM(fasdv.transaction_amount) assessment_amount
FROM IGS_FI_FEE_AS fasdv
WHERE fasdv.person_id = p_person_id
AND (fasdv.course_cd = cp_fee_ass_course_cd
OR
(fasdv.course_cd IS NULL AND cp_fee_ass_course_cd IS NULL)
)
AND fasdv.logical_delete_dt IS NULL
GROUP BY fasdv.course_cd,
fasdv.fee_cal_type,
fasdv.fee_ci_sequence_number,
fasdv.FEE_TYPE,
fasdv.FEE_CAT ;
p_sca_deleted_ind VARCHAR2)
AS
BEGIN -- finpl_prc_ins_log_entry
-- create a log entry
DECLARE
cst_del_un_sca CONSTANT VARCHAR2(10) := 'DEL-UN-SCA';
p_sca_deleted_ind || '|' || p_key,
p_message_name,
NULL);
SELECT
cfar.ROWID,
cfar.PERSON_ID,
cfar.COURSE_CD,
cfar.FEE_TYPE,
cfar.START_DT,
cfar.END_DT,
cfar.LOCATION_CD,
cfar.ATTENDANCE_TYPE,
cfar.ATTENDANCE_MODE,
cfar.CHG_RATE,
cfar.LOWER_NRML_RATE_OVRD_IND
FROM IGS_FI_FEE_AS_RT cfar
WHERE cfar.person_id = p_person_id AND
cfar.course_cd = p_course_cd
FOR UPDATE OF cfar.end_dt NOWAIT;
IGS_FI_FEE_AS_RT_PKG.UPDATE_ROW(
X_ROWID => v_cfar_rec.ROWID ,
X_PERSON_ID => v_cfar_rec.PERSON_ID ,
X_COURSE_CD => v_cfar_rec.COURSE_CD,
X_FEE_TYPE => v_cfar_rec.FEE_TYPE ,
X_START_DT => v_cfar_rec.START_DT ,
X_END_DT => v_cfar_rec.start_dt,
X_LOCATION_CD => v_cfar_rec.LOCATION_CD,
X_ATTENDANCE_TYPE => v_cfar_rec.ATTENDANCE_TYPE,
X_ATTENDANCE_MODE => v_cfar_rec.ATTENDANCE_MODE,
X_CHG_RATE => v_cfar_rec.CHG_RATE ,
X_LOWER_NRML_RATE_OVRD_IND => v_cfar_rec.LOWER_NRML_RATE_OVRD_IND ,
X_MODE => 'R');
SELECT fee_calc_mthd_code
FROM igs_fi_control;
SELECT course_type
FROM igs_ps_ver
WHERE course_cd = cp_course_cd;
SELECT 'X'
FROM IGS_FI_F_CAT_FEE_LBL_SCA_V sfv
WHERE sfv.person_id = p_person_id
AND sfv.course_cd = p_course_cd
AND sfv.FEE_CAT = p_fee_cat
AND sfv.FEE_TYPE = p_fee_type
AND sfv.fee_cal_type = p_fee_cal_type
AND sfv.fee_ci_sequence_number = p_fee_ci_sequence_number;
SELECT 'X'
FROM IGS_FI_F_CAT_FEE_LBL_SCA_V sfv
WHERE sfv.person_id = p_person_id
AND sfv.course_cd <> p_course_cd
AND sfv.FEE_TYPE = p_fee_type
AND sfv.fee_cal_type = p_fee_cal_type
AND sfv.fee_ci_sequence_number = p_fee_ci_sequence_number
AND (sfv.fee_ass_ind = 'Y'
OR sfv.course_attempt_status = cst_unconfirm);
PROCEDURE finpl_prc_delete_fee_contract (
p_person_id IGS_FI_FEE_AS_RT.person_id%TYPE,
p_course_cd IGS_FI_FEE_AS_RT.course_cd%TYPE)
AS
BEGIN -- finpl_prc_delete_fee_contract
-- Delete the fee contract
DECLARE
CURSOR c_cfar IS
SELECT cfar.person_id , cfar.ROWID
FROM IGS_FI_FEE_AS_RT cfar
WHERE cfar.person_id = p_person_id AND
cfar.course_cd = p_course_cd
FOR UPDATE OF cfar.person_id NOWAIT;
IGS_FI_FEE_AS_RT_PKG.DELETE_ROW(X_ROWID =>v_cfar_rec.ROWID);
v_delete_sca_ind := 'N';
Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINPL_PRC_DELETE_FEE_CONTRACT');
END finpl_prc_delete_fee_contract;
p_delete_sca_ind := 'Y';
v_delete_sca_ind := 'N';
v_delete_sca_ind := 'N';
IF v_delete_sca_ind = 'Y' THEN
finpl_prc_delete_fee_contract (
p_person_id,
p_course_cd);
IF v_delete_sca_ind = 'N' AND
v_log_entry_ind = 'N' THEN
finpl_prc_ins_log_entry(
4741,
'N');
p_delete_sca_ind := v_delete_sca_ind;