[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Added new functions fisp_lock_records , fisp_insert_record
abshriva 17-May-2006 Bug 5113295 - Modified assess_fees_pvt: Added invocation of function chk_unit_prg_transfer
abshriva 12-MAy-2006 Bug 5217319 Amount precision change in assess_fees_pvt
abshriva 5 May-2006 Bug 5178077: Modification done in assess_fees
uudayapr 14-Sep-2005 Bug 4609164 - Modfied call_charges_api to passes the Unit level values
svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
Impact of Charges API version Number change
Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
svuppala 29-MAR-05 Bug 4240402 Timezone impact; Truncating the time part in calling place of the table handlers
SELECT description
FROM igs_fi_fee_type
WHERE fee_type = cp_v_fee_type;
SELECT org_unit_cd ,location_cd
FROM igs_en_su_attempt su
WHERE su.person_id = cp_person_id
AND su.course_cd = cp_course_cd
AND su.uoo_id = cp_uoo_id;
SELECT owner_org_unit_cd
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo_id = cp_uoo_id;
SELECT uv.unit_type_id,
asuc.unit_class,
asuc.unit_mode,
uv.unit_level
FROM igs_ps_unit_ver uv,
igs_ps_unit_ofr_opt_all uoo,
igs_as_unit_class asuc
WHERE uv.unit_cd = uoo.unit_cd
AND uv.version_number = uoo.version_number
AND asuc.unit_class = uoo.unit_class
AND uoo.uoo_id = cp_uoo_id;
Modified cursor cur_sua_status - selected dcnt_reason_cd
abshriva 12-May-2006 Bug 5217319:- Amount Precision change, added API call to allow correct precison into DB
rmaddipa 20-Sep-04 Enh#3880438 Retention Enhancement. Modified to incorporate
teaching period level, unit section level, Complete withdrawal retention rules.
rmaddipa 26-July-04 Enh#3787816
Modified to prevent re-assessment of manually
reversed charges.
Obsoleted the cursor CUR_FEE_DECLINED
******************************************************************/
TYPE sp_fees_rec_type IS RECORD( person_id hz_parties.party_id%TYPE,
course_cd igs_ps_ver.course_cd%TYPE,
uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
fee_type igs_fi_fee_type.fee_type%TYPE,
fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
old_amount igs_fi_special_fees.fee_amt%TYPE,
new_amount igs_fi_special_fees.fee_amt%TYPE,
invoice_id igs_fi_inv_int.invoice_id%TYPE);
SELECT fee_type,
invoice_id,
SUM(fee_amt) fee_amt
FROM igs_fi_special_fees
WHERE person_id = cp_n_person_id
AND course_cd = cp_v_course_cd
AND uoo_id = cp_n_uoo_id
AND fee_cal_type = cp_v_fee_cal_type
AND fee_ci_sequence_number = cp_n_fee_ci_sequence_number
AND s_transaction_type_code <> cp_v_transaction_type_cd
GROUP BY fee_type, invoice_id;
SELECT sua.cal_type,
sua.ci_sequence_number, sua.discontinued_dt,
sua.administrative_unit_status, sua.unit_attempt_status,
sua.no_assessment_ind
FROM igs_en_su_attempt sua,
igs_lookups_view lkp
WHERE sua.person_id = cp_n_person_id
AND sua.course_cd = cp_v_course_cd
AND sua.uoo_id = cp_n_uoo_id
AND lkp.lookup_type = cp_v_unit_att_status
AND lkp.fee_ass_ind = cp_v_fee_ass_ind
AND sua.unit_attempt_status = lkp.lookup_code;
SELECT sua.unit_attempt_status,
sua.discontinued_dt,
sua.dcnt_reason_cd
FROM igs_en_su_attempt sua
WHERE sua.person_id = cp_n_person_id
AND sua.uoo_id = cp_n_uoo_id
AND sua.course_cd = cp_v_course_cd;
SELECT sp_fee_amt,
fee_type
FROM igs_ps_usec_sp_fees
WHERE uoo_id = cp_n_uoo_id
AND closed_flag = g_v_no;
SELECT optional_payment_ind
FROM igs_fi_fee_type
WHERE fee_type = cp_fee_type;
igs_fi_special_fees_pkg.insert_row ( x_rowid => l_rowid,
x_special_fee_id => l_n_special_fee_id,
x_person_id => l_sp_fees_tbl(l_n_tbl_cnt).person_id,
x_course_cd => l_sp_fees_tbl(l_n_tbl_cnt).course_cd,
x_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
x_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
x_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
x_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
x_fee_amt => igs_fi_gen_gl.get_formatted_amount(l_n_retention_amt),
x_transaction_date => TRUNC(SYSDATE),
x_s_transaction_type_code => g_v_retention,
x_invoice_id => l_n_invoice_id,
x_mode => 'R'
);
igs_fi_special_fees_pkg.insert_row ( x_rowid => l_rowid,
x_special_fee_id => l_n_special_fee_id,
x_person_id => l_sp_fees_tbl(l_n_tbl_cnt).person_id,
x_course_cd => l_sp_fees_tbl(l_n_tbl_cnt).course_cd,
x_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
x_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
x_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
x_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
x_fee_amt => igs_fi_gen_gl.get_formatted_amount(l_n_net_amount),
x_transaction_date => TRUNC(SYSDATE),
x_s_transaction_type_code => g_v_special,
x_invoice_id => NVL(l_n_invoice_id,l_n_source_invoice_id),
x_mode => 'R'
);
SELECT person_number
FROM IGS_PE_PERSON_BASE_V
WHERE person_id = cp_person_id;
SELECT 'X'
FROM igs_pe_persid_group_v
WHERE group_id = cp_person_grp_id
AND closed_ind = 'N';
SELECT alternate_code, description
FROM igs_ca_inst
WHERE cal_type = cp_v_cal_type
AND sequence_number = cp_n_sequence_number;
SELECT group_cd
FROM igs_pe_persid_group_v
WHERE group_id = c_group_id;
SELECT igs_pe_std_todo.rowid , igs_pe_std_todo.*
FROM igs_pe_std_todo
WHERE (person_id = cp_n_person_id OR cp_n_person_id IS NULL)
AND s_student_todo_type = cp_v_todo_type
AND logical_delete_dt is NULL;
SELECT tref.rowid , tref.*
FROM igs_pe_std_todo_ref tref
WHERE tref.person_id = cp_n_person_id
AND tref.sequence_number = cp_n_sequence_number
AND tref.s_student_todo_type = cp_v_todo_type
AND tref.logical_delete_dt IS NULL
AND (
(tref.cal_type = cp_v_ld_cal_type)
OR
(cp_v_ld_cal_type IS NULL)
)
AND
( tref.ci_sequence_number = cp_n_ld_seq_number
OR
(cp_n_ld_seq_number IS NULL)
);
SELECT alternate_code,description
FROM igs_ca_inst
WHERE cal_type = cp_v_cal_type
AND sequence_number = cp_n_sequence_number;
SELECT 'X'
FROM igs_pe_std_todo_ref tref
WHERE tref.person_id = cp_n_person_id
AND tref.s_student_todo_type = cp_v_todo_type
AND tref.sequence_number = cp_n_sequence_number
AND logical_delete_dt is NULL;
igs_pe_std_todo_ref_pkg.update_row (x_rowid =>l_c_get_todo_ref_recs.rowid,
x_person_id =>l_c_get_todo_ref_recs.person_id,
x_s_student_todo_type =>l_c_get_todo_ref_recs.s_student_todo_type,
x_sequence_number =>l_c_get_todo_ref_recs.sequence_number,
x_reference_number =>l_c_get_todo_ref_recs.reference_number,
x_cal_type =>l_c_get_todo_ref_recs.cal_type,
x_ci_sequence_number =>l_c_get_todo_ref_recs.ci_sequence_number,
x_course_cd =>l_c_get_todo_ref_recs.course_cd,
x_unit_cd =>l_c_get_todo_ref_recs.unit_cd,
x_other_reference =>l_c_get_todo_ref_recs.other_reference,
x_logical_delete_dt =>SYSDATE,
x_uoo_id =>l_c_get_todo_ref_recs.uoo_id );
igs_pe_std_todo_pkg.update_row(x_rowid =>l_c_get_todo_recs.rowid,
x_person_id =>l_c_get_todo_recs.person_id,
x_s_student_todo_type =>l_c_get_todo_recs.s_student_todo_type,
x_sequence_number =>l_c_get_todo_recs.sequence_number,
x_todo_dt =>l_c_get_todo_recs.todo_dt ,
x_logical_delete_dt =>SYSDATE );
FUNCTION fisp_insert_record(p_n_person_id IN igs_fi_spa_fee_prds.person_id%TYPE,
p_v_course_cd IN igs_fi_spa_fee_prds.course_cd%TYPE,
p_v_fee_cal_type IN igs_fi_spa_fee_prds.fee_cal_type%TYPE,
p_n_fee_ci_sequence_number IN igs_fi_spa_fee_prds.fee_ci_sequence_number%TYPE)
RETURN BOOLEAN
IS
PRAGMA AUTONOMOUS_TRANSACTION;
igs_fi_spa_fee_prds_pkg.insert_row ( x_rowid => l_rowid,
x_person_id => p_n_person_id,
x_course_cd => p_v_course_cd,
x_fee_cal_type => p_v_fee_cal_type,
x_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
x_mode => 'R',
x_transaction_type => 'SPECIAL'
);
fnd_log.string(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_fi_prc_sp_fees.fisp_insert_record',SQLERRM);
END fisp_insert_record;
SELECT 'x'
FROM igs_fi_spa_fee_prds
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_sequence_number
AND transaction_type = cp_transaction_type
FOR UPDATE NOWAIT;
l_v_dummy VARCHAR2(2) := NULL; -- Dummy variable to hold the value selected in cur_fee_spa
IF fisp_insert_record(p_n_person_id,
p_v_course_cd,
p_v_fee_cal_type,
p_n_fee_ci_sequence_number) THEN
-- After insertion (if insertion was successful), lock the record
OPEN cur_fee_spa(p_n_person_id,
p_v_course_cd,
p_v_fee_cal_type,
p_n_fee_ci_sequence_number,
'SPECIAL');