[Home] [Help]
54: bannamal 31-MAR-2005 Bug 4224364 STUDENT FINANCE UPTAKE OF PROGRAM TRANSFER FUTURE DATED FLAG VALUE CHANGES
55: Modified the cursors c_get_term_recs, c_get_scas_recs to ignore the program attempts
56: with Future-Dated Transfer flag set to 'C'
57: svuppala 21-MAR-2005 Bug 4240402 Timezone impact; Truncating the time part in calling place of the table handlers
58: of the tables IGS_FI_FEE_AS_ALL, IGS_FI_FEE_AS_ITEMS.
59: Modified the sysdate entries as Trunc(Sysdate) and
60: p_effective_dt also modified as trunc(p_effective_dt) where ever required .
61: pathipat 18-Nov-2004 Bug 4017841 - Modified get_stdnt_res_status_cd() and finp_ins_enr_fee_ass()
62: Removed reference to res_dt_alias of table igs_fi_control_all and associated code
170: (reverse chronological order - newest change first)
171: ***************************************************************/
172: --
173: X_ROWID VARCHAR2(25);
174: v_fa_sequence_number igs_fi_fee_as_all.transaction_id%TYPE;
175: g_v_person_number hz_parties.party_number%TYPE;
176: l_v_lkp_all CONSTANT VARCHAR2(60) := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'ALL');
177:
178: TYPE inst_prog_details_rec_type IS RECORD (
208: -- Profile for determining whether Nominated or Derived values are used
209: g_v_att_profile CONSTANT fnd_lookup_values.lookup_code%TYPE := FND_PROFILE.VALUE('IGS_FI_NOM_DER_VALUES');
210: g_v_auto_calc_wav_prof CONSTANT VARCHAR2(30) := fnd_profile.VALUE('IGS_FI_AUTO_CALC_WAIVERS');
211:
212: g_n_org_id CONSTANT igs_fi_fee_as_all.org_id%TYPE := igs_ge_gen_003.get_org_id;
213:
214: -- Global variable to hold Currency Code - assigned a value in finp_ins_enr_fee_ass, cursor c_fi_control
215: g_v_currency_cd igs_fi_control_all.currency_cd%TYPE := NULL;
216:
339: END IF;
340:
341: END check_census_dt_setup;
342:
343: FUNCTION finpl_lock_records(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
344: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
345: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
346: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE) RETURN BOOLEAN;
347:
341: END check_census_dt_setup;
342:
343: FUNCTION finpl_lock_records(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
344: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
345: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
346: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE) RETURN BOOLEAN;
347:
348: FUNCTION finpl_get_derived_am_at (p_person_id IN hz_parties.party_id%TYPE,
349: p_course_cd IN igs_ps_course.course_cd%TYPE,
342:
343: FUNCTION finpl_lock_records(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
344: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
345: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
346: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE) RETURN BOOLEAN;
347:
348: FUNCTION finpl_get_derived_am_at (p_person_id IN hz_parties.party_id%TYPE,
349: p_course_cd IN igs_ps_course.course_cd%TYPE,
350: p_effective_dt IN DATE,
357: p_derived_attendance_type OUT NOCOPY igs_fi_fee_as_rate.attendance_type%TYPE,
358: p_derived_att_mode OUT NOCOPY igs_en_atd_mode_all.govt_attendance_mode%TYPE) RETURN BOOLEAN;
359:
360:
361: FUNCTION finpl_check_header_lines(p_n_person_id igs_fi_fee_as_all.person_id%TYPE,
362: p_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) RETURN BOOLEAN;
363:
364: PROCEDURE create_retention_charge( p_n_person_id IN igs_fi_inv_int_all.person_id%TYPE,
365: p_v_course_cd IN igs_fi_inv_int_all.course_cd%TYPE,
358: p_derived_att_mode OUT NOCOPY igs_en_atd_mode_all.govt_attendance_mode%TYPE) RETURN BOOLEAN;
359:
360:
361: FUNCTION finpl_check_header_lines(p_n_person_id igs_fi_fee_as_all.person_id%TYPE,
362: p_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) RETURN BOOLEAN;
363:
364: PROCEDURE create_retention_charge( p_n_person_id IN igs_fi_inv_int_all.person_id%TYPE,
365: p_v_course_cd IN igs_fi_inv_int_all.course_cd%TYPE,
366: p_v_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
392: p_attendance_type IN VARCHAR2 ,
393: p_attendance_mode IN VARCHAR2 ,
394: p_trace_on IN VARCHAR2 ,
395: p_creation_dt IN OUT NOCOPY DATE ,
396: p_charge_elements IN OUT NOCOPY igs_fi_fee_as_all.chg_elements%TYPE ,
397: p_fee_assessment IN OUT NOCOPY NUMBER ,
398: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
399: p_c_career IN igs_ps_ver_all.course_type%TYPE,
400: p_elm_rng_order_name IN igs_fi_f_typ_ca_inst_all.elm_rng_order_name%TYPE,
842: App_Exception.Raise_Exception;
843: END finpl_retn_sort_table;
844:
845:
846: PROCEDURE finpl_prc_teach_prd_retn_levl(p_person_id igs_fi_fee_as_all.person_id%TYPE,
847: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
848: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
849: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
850: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
843: END finpl_retn_sort_table;
844:
845:
846: PROCEDURE finpl_prc_teach_prd_retn_levl(p_person_id igs_fi_fee_as_all.person_id%TYPE,
847: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
848: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
849: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
850: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
851: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
844:
845:
846: PROCEDURE finpl_prc_teach_prd_retn_levl(p_person_id igs_fi_fee_as_all.person_id%TYPE,
847: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
848: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
849: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
850: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
851: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
852: p_n_uoo_id igs_fi_fai_dtls.uoo_id%TYPE,
845:
846: PROCEDURE finpl_prc_teach_prd_retn_levl(p_person_id igs_fi_fee_as_all.person_id%TYPE,
847: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
848: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
849: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
850: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
851: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
852: p_n_uoo_id igs_fi_fai_dtls.uoo_id%TYPE,
853: p_trace_on VARCHAR2,
846: PROCEDURE finpl_prc_teach_prd_retn_levl(p_person_id igs_fi_fee_as_all.person_id%TYPE,
847: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
848: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
849: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
850: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
851: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
852: p_n_uoo_id igs_fi_fai_dtls.uoo_id%TYPE,
853: p_trace_on VARCHAR2,
854: p_d_gl_date igs_fi_invln_int_all.gl_date%TYPE,
847: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
848: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
849: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
850: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
851: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
852: p_n_uoo_id igs_fi_fai_dtls.uoo_id%TYPE,
853: p_trace_on VARCHAR2,
854: p_d_gl_date igs_fi_invln_int_all.gl_date%TYPE,
855: p_n_diff_amount NUMBER,
1460: p_attendance_type IN VARCHAR2 ,
1461: p_attendance_mode IN VARCHAR2 ,
1462: p_trace_on IN VARCHAR2 ,
1463: p_creation_dt IN OUT NOCOPY DATE ,
1464: p_charge_elements IN OUT NOCOPY igs_fi_fee_as_all.chg_elements%TYPE ,
1465: p_fee_assessment IN OUT NOCOPY NUMBER,
1466: p_charge_rate OUT NOCOPY IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
1467: p_c_career IN igs_ps_ver_all.course_type%TYPE,
1468: p_elm_rng_order_name IN igs_fi_f_typ_ca_inst_all.elm_rng_order_name%TYPE,
4959: RETURN FALSE;
4960: END finpl_sum_fee_ass_item;
4961:
4962:
4963: PROCEDURE finpl_chk_debt_ret_sched(p_person_id igs_fi_fee_as_all.person_id%TYPE,
4964: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
4965: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
4966: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4960: END finpl_sum_fee_ass_item;
4961:
4962:
4963: PROCEDURE finpl_chk_debt_ret_sched(p_person_id igs_fi_fee_as_all.person_id%TYPE,
4964: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
4965: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
4966: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4961:
4962:
4963: PROCEDURE finpl_chk_debt_ret_sched(p_person_id igs_fi_fee_as_all.person_id%TYPE,
4964: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
4965: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
4966: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4969: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
4962:
4963: PROCEDURE finpl_chk_debt_ret_sched(p_person_id igs_fi_fee_as_all.person_id%TYPE,
4964: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
4965: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
4966: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4969: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
4970: p_course_attempt_status igs_fi_fee_as_items.course_attempt_status%TYPE,
4963: PROCEDURE finpl_chk_debt_ret_sched(p_person_id igs_fi_fee_as_all.person_id%TYPE,
4964: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
4965: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
4966: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4969: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
4970: p_course_attempt_status igs_fi_fee_as_items.course_attempt_status%TYPE,
4971: p_old_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4964: p_fee_cat igs_fi_fee_as_all.fee_cat%TYPE,
4965: p_fee_type igs_fi_fee_as_all.fee_type%TYPE,
4966: p_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4969: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
4970: p_course_attempt_status igs_fi_fee_as_items.course_attempt_status%TYPE,
4971: p_old_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4972: p_new_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4967: p_fee_ci_sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4969: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
4970: p_course_attempt_status igs_fi_fee_as_items.course_attempt_status%TYPE,
4971: p_old_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4972: p_new_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4973: p_effective_date DATE,
4974: p_trace_on VARCHAR2,
4975: p_d_gl_date igs_fi_invln_int_all.gl_date%TYPE,
4968: p_course_cd igs_fi_fee_as_all.course_cd%TYPE,
4969: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
4970: p_course_attempt_status igs_fi_fee_as_items.course_attempt_status%TYPE,
4971: p_old_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4972: p_new_ass_amount igs_fi_fee_as_all.transaction_amount%TYPE,
4973: p_effective_date DATE,
4974: p_trace_on VARCHAR2,
4975: p_d_gl_date igs_fi_invln_int_all.gl_date%TYPE,
4976: p_v_s_fee_type igs_fi_fee_type_all.s_fee_type%TYPE,
5657: p_crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
5658: p_course_attempt_status igs_fi_fee_as_items.course_attempt_status%TYPE,
5659: p_attendance_mode igs_fi_fee_as_items.attendance_mode%TYPE,
5660: p_attendance_type igs_fi_fee_as_items.attendance_type%TYPE,
5661: p_charge_elements IN OUT NOCOPY Igs_fi_fee_as_all.chg_elements%TYPE,
5662: p_fee_assessment IN OUT NOCOPY NUMBER,
5663: -- Added to initialize the new pl/sql table for Institution related records
5664: p_fcfl_status IN igs_fi_f_cat_fee_lbl_all.fee_liability_status%TYPE,
5665: p_n_rul_sequence_number IN igs_fi_fee_as_items.rul_sequence_number%TYPE,
5713: BEGIN
5714: DECLARE
5715: e_one_record_expected EXCEPTION;
5716:
5717: cst_assessment CONSTANT igs_fi_fee_as_all.s_transaction_type%TYPE := 'ASSESSMENT';
5718: v_chg_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE;
5719: v_chg_elements NUMBER;
5720: v_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
5721: v_message_name VARCHAR2(30);
5804: lv_as_record_ins boolean := FALSE;
5805: lv_fee_ass_item_id IGS_FI_FEE_AS_ITEMS.fee_ass_item_id%TYPE;
5806: lv_as_items_rowid VARCHAR2(25);
5807: lv_as_rowid VARCHAR2(25);
5808: lv_sum_old_amount igs_fi_fee_as_all.transaction_amount%TYPE := 0;
5809: lv_sum_new_amount igs_fi_fee_as_all.transaction_amount%TYPE := 0;
5810: l_v_fai_rowid VARCHAR2(25);
5811: l_v_fee_as_item_dtl_id NUMBER;
5812:
5805: lv_fee_ass_item_id IGS_FI_FEE_AS_ITEMS.fee_ass_item_id%TYPE;
5806: lv_as_items_rowid VARCHAR2(25);
5807: lv_as_rowid VARCHAR2(25);
5808: lv_sum_old_amount igs_fi_fee_as_all.transaction_amount%TYPE := 0;
5809: lv_sum_new_amount igs_fi_fee_as_all.transaction_amount%TYPE := 0;
5810: l_v_fai_rowid VARCHAR2(25);
5811: l_v_fee_as_item_dtl_id NUMBER;
5812:
5813: BEGIN
5942: END IF;
5943:
5944: IF lv_as_record_ins THEN
5945: DECLARE
5946: l_n_org_id igs_fi_fee_As_all.ORG_ID%TYPE := igs_ge_gen_003.get_org_id;
5947:
5948: BEGIN
5949: log_to_fnd( p_v_module => 'finpl_ins_fee_ass',
5950: p_v_string => 'Trying to insert records..');
6257: p_fee_ci_sequence_number IGS_FI_FEE_AS_ITEMS.fee_ci_sequence_number%TYPE,
6258: p_unit_attempt_status IGS_FI_FEE_AS_ITEMS.unit_attempt_status%TYPE,
6259: p_location_cd IGS_FI_FEE_AS_ITEMS.location_cd%TYPE,
6260: p_ci_sequence_number IGS_FI_FEE_AS_ITEMS.ci_sequence_number%TYPE,
6261: p_charge_elements IN OUT NOCOPY igs_fi_fee_as_all.chg_elements%TYPE,
6262: p_ret_true_flag OUT NOCOPY BOOLEAN,
6263: p_message_name OUT NOCOPY VARCHAR2,
6264: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
6265: p_uoo_id IGS_FI_FEE_AS_ITEMS.UOO_ID%TYPE,
6702: p_fee_ci_sequence_number igs_fi_f_cat_fee_lbl_all.fee_ci_sequence_number%TYPE,
6703: p_fee_type igs_fi_f_cat_fee_lbl_all.fee_type%TYPE,
6704: p_s_fee_type igs_fi_fee_type_all.s_fee_type%TYPE,
6705: p_s_fee_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
6706: p_charge_elements IN OUT NOCOPY igs_fi_fee_as_all.chg_elements%TYPE,
6707: p_message_name OUT NOCOPY VARCHAR2,
6708: p_location_cd IGS_FI_FEE_AS_ITEMS.LOCATION_CD%TYPE,
6709: p_c_career igs_ps_ver_all.course_type%TYPE,
6710: p_n_selection_rule IN igs_ru_rule.sequence_number%TYPE
6731: lv_param_values VARCHAR2(1080);
6732: BEGIN
6733: DECLARE
6734: v_version_number igs_en_stdnt_ps_att_all.version_number%TYPE;
6735: v_eftsu igs_fi_fee_as_all.chg_elements%TYPE;
6736: v_credit_points igs_fi_fee_as_all.chg_elements%TYPE;
6737: v_ret_true_flag BOOLEAN;
6738: v_trigger_fired fnd_lookup_values.lookup_code%TYPE;
6739: v_message_name VARCHAR2(30);
6732: BEGIN
6733: DECLARE
6734: v_version_number igs_en_stdnt_ps_att_all.version_number%TYPE;
6735: v_eftsu igs_fi_fee_as_all.chg_elements%TYPE;
6736: v_credit_points igs_fi_fee_as_all.chg_elements%TYPE;
6737: v_ret_true_flag BOOLEAN;
6738: v_trigger_fired fnd_lookup_values.lookup_code%TYPE;
6739: v_message_name VARCHAR2(30);
6740: v_derived_org_unit_cd IGS_FI_FEE_AS_ITEMS.org_unit_cd%TYPE;
7760: DECLARE
7761: cst_completed CONSTANT igs_en_stdnt_ps_att_all.course_attempt_status%TYPE := 'COMPLETED';
7762: v_next_fcfl_flag BOOLEAN;
7763: v_charge_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE;
7764: v_charge_elements igs_fi_fee_as_all.chg_elements%TYPE;
7765: v_fee_assessment NUMBER;
7766: v_fee_cat IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE;
7767: v_fee_cal_type IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE;
7768: v_fee_ci_sequence_number IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE;
7863: CURSOR c_fcflv (
7864: cp_fee_cat IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE,
7865: cp_fee_cal_type IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE,
7866: cp_fee_ci_sequence_number IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE,
7867: cp_v_s_transaction_type igs_fi_fee_as_all.s_transaction_type%TYPE ) IS
7868: SELECT fcflv.fee_cal_type,
7869: fcflv.fee_ci_sequence_number,
7870: fcflv.FEE_TYPE,
7871: fcflv.fee_liability_status,
7916: ast.elm_rng_order_name,
7917: ast.scope_rul_sequence_num,
7918: ast.max_chg_elements
7919: FROM igs_fi_fee_as_items ast,
7920: igs_fi_fee_as_all fas,
7921: igs_fi_fee_type_all ft,
7922: igs_ps_ver_all ps
7923: WHERE ast.person_id = p_person_id
7924: AND (p_fee_type IS NULL OR (p_fee_type IS NOT NULL AND ast.fee_type = p_fee_type))-- will reverse existing charges only when user has not provided Fee Type as input
8037: WHERE fee_type = cp_fee_type;
8038: v_fee_type_description igs_fi_fee_type_all.description%type;
8039: l_n_scope_rul_seq_num igs_fi_f_typ_ca_inst_all.scope_rul_sequence_num%TYPE;
8040:
8041: CURSOR cur_chg_method1(cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8042: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8043: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE) IS
8044: SELECT s_chg_method_type,
8045: scope_rul_sequence_num
8038: v_fee_type_description igs_fi_fee_type_all.description%type;
8039: l_n_scope_rul_seq_num igs_fi_f_typ_ca_inst_all.scope_rul_sequence_num%TYPE;
8040:
8041: CURSOR cur_chg_method1(cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8042: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8043: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE) IS
8044: SELECT s_chg_method_type,
8045: scope_rul_sequence_num
8046: FROM igs_fi_f_typ_ca_inst_all
8039: l_n_scope_rul_seq_num igs_fi_f_typ_ca_inst_all.scope_rul_sequence_num%TYPE;
8040:
8041: CURSOR cur_chg_method1(cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8042: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8043: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE) IS
8044: SELECT s_chg_method_type,
8045: scope_rul_sequence_num
8046: FROM igs_fi_f_typ_ca_inst_all
8047: WHERE fee_type = cp_fee_type
8055: cp_fee_ci_sequence_number igs_fi_fee_as_items.fee_ci_sequence_number%TYPE,
8056: cp_course_cd igs_fi_fee_as_items.course_cd%TYPE,
8057: cp_career igs_ps_ver_all.course_type%TYPE,
8058: cp_s_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
8059: cp_v_s_transaction_type1 igs_fi_fee_as_all.s_transaction_type%TYPE,
8060: cp_v_s_transaction_type2 igs_fi_fee_as_all.s_transaction_type%TYPE
8061: ) IS
8062: SELECT a.course_cd,
8063: a.crs_version_number,
8056: cp_course_cd igs_fi_fee_as_items.course_cd%TYPE,
8057: cp_career igs_ps_ver_all.course_type%TYPE,
8058: cp_s_trigger_cat igs_fi_fee_type_all.s_fee_trigger_cat%TYPE,
8059: cp_v_s_transaction_type1 igs_fi_fee_as_all.s_transaction_type%TYPE,
8060: cp_v_s_transaction_type2 igs_fi_fee_as_all.s_transaction_type%TYPE
8061: ) IS
8062: SELECT a.course_cd,
8063: a.crs_version_number,
8064: a.location_cd,
8092: )
8093: AND a.logical_delete_date IS NULL
8094: AND TRUNC(a.fee_effective_dt) <= TRUNC(p_effective_date)
8095: AND NOT EXISTS ( SELECT 'x'
8096: FROM igs_fi_fee_as_all b
8097: WHERE b.person_id = cp_person_id
8098: AND b.transaction_id = a.transaction_id
8099: AND b.fee_type = cp_fee_type
8100: AND b.fee_cal_type = cp_fee_cal_type
8126: cp_fee_type igs_fi_fee_as_items.fee_type%TYPE,
8127: cp_fee_cal_type igs_fi_fee_as_items.fee_cal_type%TYPE,
8128: cp_fee_ci_sequence_number igs_fi_fee_as_items.fee_ci_sequence_number%TYPE,
8129: cp_course_cd igs_fi_fee_as_items.course_cd%TYPE,
8130: cp_v_s_transaction_type1 igs_fi_fee_as_all.s_transaction_type%TYPE,
8131: cp_v_s_transaction_type2 igs_fi_fee_as_all.s_transaction_type%TYPE
8132: ) IS
8133:
8134: SELECT a.course_cd,
8127: cp_fee_cal_type igs_fi_fee_as_items.fee_cal_type%TYPE,
8128: cp_fee_ci_sequence_number igs_fi_fee_as_items.fee_ci_sequence_number%TYPE,
8129: cp_course_cd igs_fi_fee_as_items.course_cd%TYPE,
8130: cp_v_s_transaction_type1 igs_fi_fee_as_all.s_transaction_type%TYPE,
8131: cp_v_s_transaction_type2 igs_fi_fee_as_all.s_transaction_type%TYPE
8132: ) IS
8133:
8134: SELECT a.course_cd,
8135: a.crs_version_number,
8149: ((p_fee_cat IS NOT NULL AND a.fee_cat = p_fee_cat ) OR (a.fee_cat IS NULL OR p_fee_cat IS NULL)) AND
8150: a.logical_delete_date IS NULL AND
8151: TRUNC(a.fee_effective_dt) <= TRUNC(p_effective_date) AND
8152: NOT EXISTS ( SELECT 'X'
8153: FROM igs_fi_fee_as_all b
8154: WHERE b.person_id = cp_person_id
8155: AND b.transaction_id = a.transaction_id
8156: AND b.fee_type = cp_fee_type
8157: AND b.fee_cal_type = cp_fee_cal_type
8165: a.uoo_id,
8166: a.location_cd,
8167: a.org_unit_cd;
8168:
8169: CURSOR cur_chg_method(cp_person_id igs_fi_fee_as_all.person_id%TYPE,
8170: cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8171: cp_course_cd igs_fi_fee_as_all.course_cd%TYPE,
8172: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8173: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
8166: a.location_cd,
8167: a.org_unit_cd;
8168:
8169: CURSOR cur_chg_method(cp_person_id igs_fi_fee_as_all.person_id%TYPE,
8170: cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8171: cp_course_cd igs_fi_fee_as_all.course_cd%TYPE,
8172: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8173: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
8174: cp_career igs_ps_ver_all.course_type%TYPE) IS
8167: a.org_unit_cd;
8168:
8169: CURSOR cur_chg_method(cp_person_id igs_fi_fee_as_all.person_id%TYPE,
8170: cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8171: cp_course_cd igs_fi_fee_as_all.course_cd%TYPE,
8172: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8173: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
8174: cp_career igs_ps_ver_all.course_type%TYPE) IS
8175:
8168:
8169: CURSOR cur_chg_method(cp_person_id igs_fi_fee_as_all.person_id%TYPE,
8170: cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8171: cp_course_cd igs_fi_fee_as_all.course_cd%TYPE,
8172: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8173: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
8174: cp_career igs_ps_ver_all.course_type%TYPE) IS
8175:
8176: SELECT a.fee_ass_item_id upd,
8169: CURSOR cur_chg_method(cp_person_id igs_fi_fee_as_all.person_id%TYPE,
8170: cp_fee_type igs_fi_fee_as_all.fee_type%TYPE,
8171: cp_course_cd igs_fi_fee_as_all.course_cd%TYPE,
8172: cp_fee_cal_type igs_fi_fee_as_all.fee_cal_type%TYPE,
8173: cp_fee_ci_Sequence_number igs_fi_fee_as_all.fee_ci_sequence_number%TYPE,
8174: cp_career igs_ps_ver_all.course_type%TYPE) IS
8175:
8176: SELECT a.fee_ass_item_id upd,
8177: a.s_chg_method_type
10748: app_exception.raise_exception;
10749: END ;
10750: END finp_ins_enr_fee_ass;
10751:
10752: FUNCTION finpl_insert_record(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
10753: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
10754: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
10755: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
10756: RETURN BOOLEAN
10750: END finp_ins_enr_fee_ass;
10751:
10752: FUNCTION finpl_insert_record(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
10753: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
10754: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
10755: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
10756: RETURN BOOLEAN
10757: IS
10758: PRAGMA AUTONOMOUS_TRANSACTION;
10751:
10752: FUNCTION finpl_insert_record(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
10753: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
10754: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
10755: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
10756: RETURN BOOLEAN
10757: IS
10758: PRAGMA AUTONOMOUS_TRANSACTION;
10759: /*************************************************************
10804:
10805: END finpl_insert_record;
10806:
10807:
10808: FUNCTION finpl_lock_records(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
10809: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
10810: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
10811: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
10812: RETURN BOOLEAN IS
10806:
10807:
10808: FUNCTION finpl_lock_records(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
10809: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
10810: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
10811: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
10812: RETURN BOOLEAN IS
10813: /*************************************************************
10814: Created By : Priya Athipatla
10807:
10808: FUNCTION finpl_lock_records(p_n_person_id IN igs_fi_fee_as_all.person_id%TYPE,
10809: p_v_course_cd IN igs_ps_ver_all.course_cd%TYPE,
10810: p_v_fee_cal_type IN igs_fi_fee_as_all.fee_cal_type%TYPE,
10811: p_n_fee_ci_sequence_number IN igs_fi_fee_as_all.fee_ci_sequence_number%TYPE)
10812: RETURN BOOLEAN IS
10813: /*************************************************************
10814: Created By : Priya Athipatla
10815: Date Created By : 01-Jul-2004
10901:
10902: END finpl_lock_records;
10903:
10904:
10905: FUNCTION finpl_check_header_lines(p_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10906: p_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE)
10907: RETURN BOOLEAN IS
10908: /*************************************************************
10909: Created By : Priya Athipatla
10902: END finpl_lock_records;
10903:
10904:
10905: FUNCTION finpl_check_header_lines(p_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10906: p_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE)
10907: RETURN BOOLEAN IS
10908: /*************************************************************
10909: Created By : Priya Athipatla
10910: Date Created By : 01-Jul-2004
10915: Know limitations, enhancements or remarks
10916: Change History
10917: Who When What
10918: ***************************************************************/
10919: CURSOR cur_fee_as(cp_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10920: cp_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) IS
10921: SELECT SUM(transaction_amount)
10922: FROM igs_fi_fee_as_all
10923: WHERE person_id = cp_n_person_id
10916: Change History
10917: Who When What
10918: ***************************************************************/
10919: CURSOR cur_fee_as(cp_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10920: cp_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) IS
10921: SELECT SUM(transaction_amount)
10922: FROM igs_fi_fee_as_all
10923: WHERE person_id = cp_n_person_id
10924: AND transaction_id = cp_n_transaction_id;
10918: ***************************************************************/
10919: CURSOR cur_fee_as(cp_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10920: cp_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) IS
10921: SELECT SUM(transaction_amount)
10922: FROM igs_fi_fee_as_all
10923: WHERE person_id = cp_n_person_id
10924: AND transaction_id = cp_n_transaction_id;
10925:
10926: CURSOR cur_fee_as_items(cp_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10922: FROM igs_fi_fee_as_all
10923: WHERE person_id = cp_n_person_id
10924: AND transaction_id = cp_n_transaction_id;
10925:
10926: CURSOR cur_fee_as_items(cp_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10927: cp_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) IS
10928: SELECT amount
10929: FROM igs_fi_fee_as_items
10930: WHERE person_id = cp_n_person_id
10923: WHERE person_id = cp_n_person_id
10924: AND transaction_id = cp_n_transaction_id;
10925:
10926: CURSOR cur_fee_as_items(cp_n_person_id igs_fi_fee_as_all.person_id%TYPE,
10927: cp_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE) IS
10928: SELECT amount
10929: FROM igs_fi_fee_as_items
10930: WHERE person_id = cp_n_person_id
10931: AND transaction_id = cp_n_transaction_id;
11034:
11035: -- Cursor to fetch the Fee Type Description
11036:
11037: l_rowid ROWID := NULL;
11038: l_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE := NULL;
11039: l_v_fee_cat igs_fi_fee_as_all.fee_cat%TYPE := NULL;
11040: l_v_course_cd igs_fi_fee_as_all.course_cd%TYPE := NULL;
11041: l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE := NULL;
11042: l_n_fee_ass_item_id igs_fi_fee_as_items.fee_ass_item_id%TYPE := NULL;
11035: -- Cursor to fetch the Fee Type Description
11036:
11037: l_rowid ROWID := NULL;
11038: l_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE := NULL;
11039: l_v_fee_cat igs_fi_fee_as_all.fee_cat%TYPE := NULL;
11040: l_v_course_cd igs_fi_fee_as_all.course_cd%TYPE := NULL;
11041: l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE := NULL;
11042: l_n_fee_ass_item_id igs_fi_fee_as_items.fee_ass_item_id%TYPE := NULL;
11043:
11036:
11037: l_rowid ROWID := NULL;
11038: l_n_transaction_id igs_fi_fee_as_all.transaction_id%TYPE := NULL;
11039: l_v_fee_cat igs_fi_fee_as_all.fee_cat%TYPE := NULL;
11040: l_v_course_cd igs_fi_fee_as_all.course_cd%TYPE := NULL;
11041: l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE := NULL;
11042: l_n_fee_ass_item_id igs_fi_fee_as_items.fee_ass_item_id%TYPE := NULL;
11043:
11044: l_header_rec igs_fi_charges_api_pvt.header_rec_type;