1 PACKAGE igs_fi_prc_fee_ass AS
2 /* $Header: IGSFI09S.pls 120.6 2005/09/06 06:09:23 appldev ship $ */
3 /*************************************************************
4 Created By :
5 Date Created By :
6 Purpose :
7 Know limitations, enhancements or remarks
8 Change History
9 Who When What
10 pathipat 06-Sep-2005 Bug 4540295 - Fee assessment produce double fees after program version change
11 Added a column crs_version_number in r_s_fee_as_items_typ
12 bannamal 26-Aug-2005 Enh#3392095 Tuition Waiver Build. Added two new parameters in finp_ins_enr_fee_ass
13 bannamal 08-Jul-2005 Enh#3392088 Campus Privilege Fee. Added the plsql table tbl_fai_unit_dtls,
14 Added some columns in the existing plsql table t_fee_as_items.
15 bannamal 03-JUN-2005 Bug#3442712 Unit Level Fee Assessment Build. Added new columns in the
16 record type variable r_s_fee_as_items_typ.
17 bannamal 27-May-2005 Fee Calculation Performance Enhancement. Changes done as per TD.
18 shtatiko 23-JUL-2004 Enh# 3741400, Added finpl_clc_sua_cp.
19 shtatiko 24-DEC-2003 Enh# 3167098, Removed g_b_prg_chg_da_use, g_d_prg_chg_da_alias_val, g_n_total_load,
20 g_b_sca_inactive and g_b_sca_unconfirm. Added g_d_ld_census_val.
21 pathipat 05-Nov-2003 Enh 3117341 - Audit and Special Fees TD
22 Modifications according to TD, s1a
23 pathipat 12-Sep-2003 Enh 3108052 - Unit Sets in Rate Table build
24 Added unit_set_cd and us_version_number to plsql table t_fee_as_items_typ
25 vchappid 11-Nov-2002 Bug# 2584986, GL- Interface Build New Date parameter.
26 p_d_gl_date is added to the finp_ins_enr_fee_ass function specification
27 npalanis 23-OCT-2002 Bug : 2608360
28 references to residency_status_id is changed to residency_status_cd as all pe code
29 classes are moved to igs lookups.
30 vchappid 17-Oct-2002 Bug# 2595962, Removed parameter p_predictive_ass_ind from the function call,
31 Global variables are introduced.
32 vchappid 25-Jul-2002 Bug# 2237227 - added flag 'add_flag' with Default value 'N' into the Pl/SQL table t_fee_as_items
33 added to take care the duplicate SUA incase of Primary Career fee calculation Method
34 rnirwani 28-May-02 Bug# 2378804 - removed declaration of global variables for load cal inst
35 smadathi 02-May-2002 Bug 2261649. The function finp_get_additional_charge removed.
36 vchappid 02-Jan-02 Enh Bug#2162747, Key Program Implementation, Fin Cal Inst parameters
37 removed, new parameter p_c_career is added
38 (reverse chronological order - newest change first)
39 ***************************************************************/
40 --
41 -- Calculate and insert fee assessments as required
42
43 FUNCTION finp_ins_enr_fee_ass(
44 p_effective_dt IN DATE ,
45 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
46 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
47 p_fee_category IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
48 p_fee_cal_type IN IGS_CA_INST_ALL.CAL_TYPE%TYPE ,
49 p_fee_ci_sequence_num IN IGS_CA_INST_ALL.sequence_number%TYPE ,
50 p_fee_type IN IGS_FI_FEE_TYPE_ALL.FEE_TYPE%TYPE ,
51 p_trace_on IN VARCHAR2 ,
52 p_test_run IN VARCHAR2 ,
53 p_creation_dt IN OUT NOCOPY DATE ,
54 p_message_name OUT NOCOPY VARCHAR2,
55 p_process_mode IN VARCHAR2 DEFAULT 'ACTUAL',
56 p_c_career IN igs_ps_ver_all.course_type%TYPE DEFAULT NULL,
57 p_d_gl_date IN DATE DEFAULT NULL,
58 p_v_wav_calc_flag IN VARCHAR2 DEFAULT NULL,
59 p_n_waiver_amount OUT NOCOPY NUMBER
60 ) RETURN BOOLEAN;
61
62 gcst_planned CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE := 'PLANNED';
63 gcst_inactive CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE := 'INACTIVE';
64 gcst_active CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE := 'ACTIVE';
65 gcst_institutn CONSTANT IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE := 'INSTITUTN';
66 gcst_course CONSTANT IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE := 'COURSE';
67 gcst_flatrate CONSTANT IGS_FI_F_CAT_FEE_LBL_ALL.s_chg_method_type%TYPE := 'FLATRATE';
68 gcst_perunit CONSTANT IGS_FI_F_CAT_FEE_LBL_ALL.s_chg_method_type%TYPE := 'PERUNIT';
69 gcst_eftsu CONSTANT IGS_FI_F_CAT_FEE_LBL_ALL.s_chg_method_type%TYPE := 'EFTSU';
70 gcst_crpoint CONSTANT IGS_FI_F_CAT_FEE_LBL_ALL.s_chg_method_type%TYPE := 'CRPOINT';
71 gcst_tuition CONSTANT IGS_FI_FEE_TYPE_ALL.s_fee_type%TYPE := 'TUITION';
72 gcst_other CONSTANT IGS_FI_FEE_TYPE_ALL.s_fee_type%TYPE := 'OTHER';
73 gcst_tuition_other CONSTANT IGS_FI_FEE_TYPE_ALL.s_fee_type%TYPE := 'TUTNFEE';
74
75 g_v_audit CONSTANT igs_fi_fee_type.s_fee_type%TYPE := 'AUDIT';
76
77 g_v_include_audit VARCHAR2(1) := 'N';
78
79 TYPE r_s_fee_as_items_typ IS RECORD (
80 person_id IGS_FI_FEE_AS_ITEMS.person_id%TYPE,
81 status IGS_FI_FEE_AS_ITEMS.status%TYPE,
82 fee_type IGS_FI_FEE_AS_ITEMS.fee_type%TYPE,
83 fee_cat IGS_FI_FEE_AS_ITEMS.fee_cat%TYPE,
84 fee_cal_type IGS_FI_FEE_AS_ITEMS.fee_cal_type%TYPE,
85 fee_ci_sequence_number IGS_FI_FEE_AS_ITEMS.fee_ci_sequence_number%TYPE,
86 rul_sequence_number IGS_FI_FEE_AS_ITEMS.rul_sequence_number%TYPE,
87 course_cd IGS_FI_FEE_AS_ITEMS.course_cd%TYPE,
88 crs_version_number igs_fi_fee_as_items.crs_version_number%TYPE,
89 old_chg_method_type IGS_FI_FEE_AS_ITEMS.S_CHG_METHOD_TYPE%TYPE,
90 chg_method_type IGS_FI_FEE_AS_ITEMS.S_CHG_METHOD_TYPE%TYPE,
91 description IGS_FI_FEE_AS_ITEMS.description%TYPE,
92 old_chg_elements IGS_FI_FEE_AS_ITEMS.chg_elements%TYPE,
93 chg_elements IGS_FI_FEE_AS_ITEMS.chg_elements%TYPE,
94 old_amount IGS_FI_FEE_AS_ITEMS.amount%TYPE,
95 amount IGS_FI_FEE_AS_ITEMS.amount%TYPE,
96 unit_attempt_status IGS_FI_FEE_AS_ITEMS.unit_attempt_status%TYPE,
97 location_cd IGS_FI_FEE_AS_ITEMS.location_cd%TYPE,
98 old_eftsu IGS_FI_FEE_AS_ITEMS.eftsu%TYPE,
99 eftsu IGS_FI_FEE_AS_ITEMS.eftsu%TYPE,
100 old_credit_points IGS_FI_FEE_AS_ITEMS.credit_points%TYPE,
101 credit_points IGS_FI_FEE_AS_ITEMS.credit_points%TYPE,
102 chg_rate IGS_FI_FEE_AS_RATE.chg_rate%TYPE,
103 org_unit_cd IGS_FI_FEE_AS_ITEMS.org_unit_cd%TYPE,
104 class_standing IGS_FI_FEE_AS_ITEMS.class_standing%TYPE,
105 residency_status_cd IGS_FI_FEE_AS_ITEMS.residency_status_cd%TYPE,
106 uoo_id IGS_FI_FEE_AS_ITEMS.UOO_ID%TYPE,
107 add_flag VARCHAR2(1) DEFAULT 'N',
108 unit_set_cd igs_fi_fee_as_items.unit_set_cd%TYPE,
109 us_version_number igs_fi_fee_as_items.us_version_number%TYPE,
110 unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
111 unit_class igs_fi_fee_as_items.unit_class%TYPE,
112 unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
113 unit_cd igs_fi_fee_as_rate.unit_cd%TYPE,
114 unit_level igs_fi_fee_as_items.unit_level%TYPE,
115 unit_version_number igs_fi_fee_as_rate.unit_version_number%TYPE,
116 fee_ass_item_id igs_fi_fee_as_items.fee_ass_item_id%TYPE,
117 element_order NUMBER
118 );
119
120
121 TYPE t_fee_as_items_typ IS TABLE OF r_s_fee_as_items_typ INDEX BY BINARY_INTEGER;
122 t_fee_as_items t_fee_as_items_typ;
123
124 TYPE rec_fai_unit_dtls_typ IS RECORD (
125 fee_cat igs_fi_fai_dtls.fee_cat%TYPE,
126 course_cd igs_fi_fai_dtls.course_cd%TYPE,
127 crs_version_number igs_fi_fai_dtls.crs_version_number%TYPE,
128 unit_attempt_status igs_fi_fai_dtls.unit_attempt_status%TYPE,
129 org_unit_cd igs_fi_fai_dtls.org_unit_cd%TYPE,
130 class_standing igs_fi_fai_dtls.class_standing%TYPE,
131 location_cd igs_fi_fai_dtls.location_cd%TYPE,
132 uoo_id igs_fi_fai_dtls.uoo_id%TYPE,
133 unit_set_cd igs_fi_fai_dtls.unit_set_cd%TYPE,
134 us_version_number igs_fi_fai_dtls.us_version_number%TYPE,
135 chg_elements igs_fi_fee_as_items.chg_elements%TYPE,
136 unit_type_id igs_fi_fee_as_items.unit_type_id%TYPE,
137 unit_class igs_fi_fee_as_items.unit_class%TYPE,
138 unit_mode igs_fi_fee_as_items.unit_mode%TYPE,
139 unit_cd igs_fi_fee_as_items.unit_cd%TYPE,
140 unit_level igs_fi_fee_as_items.unit_level%TYPE,
141 unit_version_number igs_fi_fee_as_items.unit_version_number%TYPE
142 );
143
144 TYPE t_fai_unit_dtls_typ IS TABLE OF rec_fai_unit_dtls_typ INDEX BY BINARY_INTEGER;
145
146 tbl_fai_unit_dtls t_fai_unit_dtls_typ;
147
148 gv_as_item_cntr NUMBER;
149
150 t_dummy_fee_as_items t_fee_as_items_typ;
151
152 TYPE r_inst_fee_rec IS RECORD (
153 person_id IGS_FI_FEE_AS_ITEMS.person_id%TYPE,
154 fee_type IGS_FI_FEE_AS_ITEMS.fee_type%TYPE,
155 fee_cal_type IGS_FI_FEE_AS_ITEMS.fee_cal_type%TYPE,
156 fee_ci_sequence_number IGS_FI_FEE_AS_ITEMS.fee_ci_sequence_number%TYPE,
157 fcfl_status IGS_FI_F_CAT_FEE_LBL.FEE_LIABILITY_STATUS%TYPE);
158
159 TYPE t_inst_fee_rec_type IS TABLE OF r_inst_fee_rec INDEX BY BINARY_INTEGER;
160
161 l_inst_fee_rec t_inst_fee_rec_type;
162 l_inst_fee_rec_dummy t_inst_fee_rec_type;
163 g_inst_fee_rec_cntr NUMBER;
164
165 /* Enh# 2162747 Added new global parameters */
166 -- variable for Fee Calculation Method defined in the Recievables Control Form/ System Options Control Form
167 g_c_fee_calc_mthd igs_fi_control.fee_calc_mthd_code%TYPE;
168
169 -- variable for storing Key Program of a student
170 g_c_key_program igs_ps_ver.course_cd%TYPE;
171 g_n_key_version igs_ps_ver.version_number%TYPE;
172
173 -- Attendance type and Attendence Mode can be derived depending on the Load incurred by the Student or
174 -- the attendance type and attendance Mode for the program that is being assessed can be used
175 -- depending on the system Profile Value defined at the User level either Nominated or Derived values
176 -- can be used.
177 gcst_nominated CONSTANT igs_lookups_view.lookup_code%TYPE := 'NOMINATED';
178 gcst_derived CONSTANT igs_lookups_view.lookup_code%TYPE := 'DERIVED';
179
180 /* End Of Modifications for Enh# 2162747 */
181
182 -- global variable to check if the Predictive Fee Assessment is being done
183 -- when the process mode is passed as PREDICTIVE then this global variable will be set to 'Y'
184 g_c_predictive_ind VARCHAR2(1) := 'N';
185
186 g_d_ld_census_val DATE;
187
188 -- Variables to hold information of Load Period associated to Fee Period assessed.
189 g_v_load_cal_type igs_fi_f_cat_ca_inst.fee_cal_type%TYPE;
190 g_n_load_seq_num igs_fi_f_cat_ca_inst.fee_ci_sequence_number%TYPE;
191 g_v_load_alt_code igs_ca_inst_all.alternate_code%TYPE;
192
193 -- This function calls EN API to get values of different Credit Points: Enrolled, Billing and Audit
194 FUNCTION finpl_clc_sua_cp( p_v_unit_cd IN igs_en_su_attempt_all.unit_cd%TYPE,
195 p_n_version_number IN igs_en_su_attempt_all.version_number%TYPE,
196 p_v_cal_type IN igs_en_su_attempt_all.cal_type%TYPE,
197 p_n_ci_sequence_number IN igs_en_su_attempt_all.ci_sequence_number%TYPE,
198 p_v_load_cal_type IN igs_en_su_attempt_all.cal_type%TYPE,
199 p_n_load_ci_sequence_number IN igs_en_su_attempt_all.ci_sequence_number%TYPE,
200 p_n_override_enrolled_cp IN igs_en_su_attempt_all.override_enrolled_cp%TYPE,
201 p_n_override_eftsu IN igs_en_su_attempt_all.override_eftsu%TYPE,
202 p_n_uoo_id IN igs_en_su_attempt_all.uoo_id%TYPE,
203 p_v_include_audit IN igs_en_su_attempt_all.no_assessment_ind%TYPE ) RETURN NUMBER;
204
205 END igs_fi_prc_fee_ass;
[Home] [Help]