1 PACKAGE BODY IGS_FI_GEN_002 AS
2 /* $Header: IGSFI02B.pls 120.2 2005/07/08 06:05:10 appldev ship $ */
3 -- HISTORY
4 --Who When What
5 --svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
6 -- Unit Version and Unit Level
7 -- svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
8 -- Modifications to reflect the data model changes (NONZERO_BILLABLE_CP_FLAG) in
9 -- IGS_FI_FEE_TYPE_CI_H_ALL
10 --pathipat 09-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
11 -- Added 4 new params for unit_set_cd and us_version_number in procedure finp_ins_far_hist()
12 --shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag. As an impact modified finp_ins_ft_hist.
13 -- npalanis 23-OCT-2002 Bug : 2608360
14 -- p_new and p_old residency_status_id column is changed to p_residency_status_cd of
15 -- datatype residency_status_cd in igs_fi_fee_as_rate table.
16 --vvutukur 13-Sep-2002 Enh#2564643.Modifications done in procedure finp_ins_ft_hist.
17 --smvk 28-Aug-2002 Procedure finp_ins_fdf_hist is obsolete as part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
18 --vvutukur 28-Aug-2002 Bug#2531390.Removed the procedure finp_ins_fps_hist.
19 --jbegum 26-Aug-2002 As part of Enh Bug#2531390 the procedure finp_ins_pps_hist was removed.
20 --vvutukur 18-Jul-2002 Bug#2425767.Removed p_new_deduction_amount,p_old_deduction_amount parameters and
21 -- its references from procedure finp_ins_frtns_hist.Removed p_new_payment_hierarchy_rank,
22 -- p_old_payment_hierarchy_rank and its references from procedures finp_ins_fcfl_hist and
23 -- finp_ins_ftci_hist.
24 --vchappid 26-Apr-2002 Bug#2329407, removed the parameters fin_cal_type, fin_ci_sequence_number, account_cd from
25 -- the procedure finp_ins_ftci_hist
26 --rnirwani 18-Jan-2002 Obsolete procedure finp_ins_sma_hist (2187247)
27 --masehgal 16-Jan-2002 ENH # 2170429
28 -- Obsoletion of SPONSOR_CD,SPONSORED_AMOUNT
29 --vvutukur 11-Jan-2002 added new columns in finp_ins_ft_hist
30 -- procedure as part of Bug 2175865
31 /* Obseleted the procedure finp_ins_fe_hist( as part of bug 2126091 as this is no longer used -sykrishn 29112001 */
32 PROCEDURE finp_ins_cfar_hist(
33 p_person_id IN IGS_FI_FEE_AS_RT.person_id%TYPE ,
34 p_course_cd IN IGS_FI_FEE_AS_RT.course_cd%TYPE ,
35 p_fee_type IN IGS_FI_FEE_AS_RT.FEE_TYPE%TYPE ,
36 p_start_dt IN IGS_FI_FEE_AS_RT.start_dt%TYPE ,
37 p_new_end_dt IN IGS_FI_FEE_AS_RT.end_dt%TYPE ,
38 p_old_end_dt IN IGS_FI_FEE_AS_RT.end_dt%TYPE ,
39 p_new_location_cd IN IGS_FI_FEE_AS_RT.location_cd%TYPE ,
40 p_old_location_cd IN IGS_FI_FEE_AS_RT.location_cd%TYPE ,
41 p_new_attendance_type IN IGS_EN_ATD_TYPE_ALL.ATTENDANCE_TYPE%TYPE ,
42 p_old_attendance_type IN IGS_EN_ATD_TYPE_ALL.ATTENDANCE_TYPE%TYPE ,
43 p_new_attendance_mode IN IGS_EN_ATD_MODE_ALL.ATTENDANCE_MODE%TYPE ,
44 p_old_attendance_mode IN IGS_EN_ATD_MODE_ALL.ATTENDANCE_MODE%TYPE ,
45 p_new_chg_rate IN IGS_FI_FEE_AS_RT.chg_rate%TYPE ,
46 p_old_chg_rate IN IGS_FI_FEE_AS_RT.chg_rate%TYPE ,
47 p_new_lower_nrml_rate_ovrd_ind IN IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE ,
48 p_old_lower_nrml_rate_ovrd_ind IN IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE ,
49 p_new_last_updated_by IN IGS_FI_FEE_AS_RT.last_updated_by%TYPE ,
50 p_old_last_updated_by IN IGS_FI_FEE_AS_RT.last_updated_by%TYPE ,
51 p_new_last_update_date IN IGS_FI_FEE_AS_RT.last_update_date%TYPE ,
52 p_old_last_update_date IN IGS_FI_FEE_AS_RT.last_update_date%TYPE
53 )
54 AS
55 gv_other_detail VARCHAR2(255);
56 BEGIN
57 DECLARE
58 X_ROWID VARCHAR2(25);
59 r_cfarh IGS_FI_FEE_AS_RT_HT%ROWTYPE;
60 v_create_history BOOLEAN := FALSE;
61 l_n_org_id IGS_FI_FEE_AS_RT_HT.org_id%TYPE := igs_ge_gen_003.get_org_id;
62 BEGIN
63 -- Create a history for a IGS_FI_FEE_AS_RT record.
64 -- Check if any of the non-primary key fields have been changed
65 -- and set the flag v_create_history to indicate so.
66 IF NVL(p_new_end_dt, igs_ge_date.igsdate('1000/01/01')) <>
67 NVL(p_old_end_dt, igs_ge_date.igsdate('1000/01/01')) THEN
68 r_cfarh.end_dt := NVL(p_old_end_dt , NULL);
69 v_create_history := TRUE;
70 END IF;
71 IF p_new_chg_rate <> p_old_chg_rate THEN
72 r_cfarh.chg_rate := p_old_chg_rate;
73 v_create_history := TRUE;
74 END IF;
75 IF p_new_lower_nrml_rate_ovrd_ind <> p_old_lower_nrml_rate_ovrd_ind THEN
76 r_cfarh.lower_nrml_rate_ovrd_ind := p_old_lower_nrml_rate_ovrd_ind;
77 v_create_history := TRUE;
78 END IF;
79 IF NVL(p_new_location_cd, 'NULL') <> NVL(p_old_location_cd, 'NULL') THEN
80 r_cfarh.location_cd := p_old_location_cd;
81 v_create_history := TRUE;
82 END IF;
83 IF NVL(p_new_attendance_type, 'NULL') <>
84 NVL(p_old_attendance_type, 'NULL') THEN
85 r_cfarh.ATTENDANCE_TYPE := p_old_attendance_type;
86 v_create_history := TRUE;
87 END IF;
88 IF NVL(p_new_attendance_mode, 'NULL') <>
89 NVL(p_old_attendance_mode, 'NULL') THEN
90 r_cfarh.ATTENDANCE_MODE := p_old_attendance_mode;
91 v_create_history := TRUE;
92 END IF;
93 -- Create a history record if a column has changed value
94 IF v_create_history = TRUE THEN
95 r_cfarh.person_id := p_person_id;
96 r_cfarh.course_cd := p_course_cd;
97 r_cfarh.FEE_TYPE := p_fee_type;
98 r_cfarh.start_dt := p_start_dt;
99 r_cfarh.hist_start_dt := p_old_last_update_date;
100 r_cfarh.hist_end_dt := p_new_last_update_date;
101 r_cfarh.hist_who := p_old_last_updated_by;
102 IGS_FI_FEE_AS_RT_HT_PKG.INSERT_ROW(
103 X_ROWID => X_ROWID,
104 X_person_id => r_cfarh.person_id,
105 X_course_cd => r_cfarh.course_cd,
106 X_FEE_TYPE => r_cfarh.FEE_TYPE,
107 X_start_dt => r_cfarh.start_dt,
108 X_hist_start_dt => r_cfarh.hist_start_dt,
109 X_hist_end_dt => r_cfarh.hist_end_dt,
110 X_hist_who => r_cfarh.hist_who,
111 X_end_dt => r_cfarh.end_dt,
112 X_location_cd => r_cfarh.location_cd,
113 X_ATTENDANCE_TYPE => r_cfarh.ATTENDANCE_TYPE,
114 X_ATTENDANCE_MODE => r_cfarh.ATTENDANCE_MODE,
115 X_chg_rate => r_cfarh.chg_rate,
116 X_lower_nrml_rate_ovrd_ind => r_cfarh.lower_nrml_rate_ovrd_ind,
117 X_MODE => 'R',
118 x_org_id => l_n_org_id);
119 END IF;
120 END;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
125 FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GEN_002.FINP_INS_CFAR_HIST');
126 IGS_GE_MSG_STACK.ADD;
127 APP_EXCEPTION.RAISE_EXCEPTION;
128 END FINP_INS_CFAR_HIST;
129 --
130 PROCEDURE finp_ins_cft_hist(
131 p_fee_cat IN IGS_PS_FEE_TRG.FEE_CAT%TYPE ,
132 p_fee_cal_type IN IGS_PS_FEE_TRG.fee_cal_type%TYPE ,
133 p_fee_ci_sequence_number IN IGS_PS_FEE_TRG.fee_ci_sequence_number%TYPE ,
134 p_fee_type IN IGS_PS_FEE_TRG.FEE_TYPE%TYPE ,
135 p_course_cd IN IGS_PS_FEE_TRG.course_cd%TYPE ,
136 p_sequence_number IN IGS_PS_FEE_TRG.sequence_number%TYPE ,
137 p_new_version_number IN IGS_PS_FEE_TRG.version_number%TYPE ,
138 p_old_version_number IN IGS_PS_FEE_TRG.version_number%TYPE ,
139 p_new_cal_type IN IGS_PS_FEE_TRG.CAL_TYPE%TYPE ,
140 p_old_cal_type IN IGS_PS_FEE_TRG.CAL_TYPE%TYPE ,
141 p_new_location_cd IN IGS_PS_FEE_TRG.location_cd%TYPE ,
142 p_old_location_cd IN IGS_PS_FEE_TRG.location_cd%TYPE ,
143 p_new_attendance_mode IN IGS_PS_FEE_TRG.ATTENDANCE_MODE%TYPE ,
144 p_old_attendance_mode IN IGS_PS_FEE_TRG.ATTENDANCE_MODE%TYPE ,
145 p_new_attendance_type IN IGS_PS_FEE_TRG.ATTENDANCE_TYPE%TYPE ,
146 p_old_attendance_type IN IGS_PS_FEE_TRG.ATTENDANCE_TYPE%TYPE ,
147 p_new_create_dt IN IGS_PS_FEE_TRG.create_dt%TYPE ,
148 p_old_create_dt IN IGS_PS_FEE_TRG.create_dt%TYPE ,
149 p_new_fee_trigger_group_number IN IGS_PS_FEE_TRG.fee_trigger_group_number%TYPE,
150 p_old_fee_trigger_group_number IN IGS_PS_FEE_TRG.fee_trigger_group_number%TYPE,
151 p_new_last_updated_by IN IGS_PS_FEE_TRG.last_updated_by%TYPE ,
152 p_old_last_updated_by IN IGS_PS_FEE_TRG.last_updated_by%TYPE ,
153 p_new_last_update_date IN IGS_PS_FEE_TRG.last_update_date%TYPE ,
154 p_old_last_update_date IN IGS_PS_FEE_TRG.last_update_date%TYPE )
155 AS
156 gv_other_detail VARCHAR2(255);
157 BEGIN
158 DECLARE
159 X_ROWID VARCHAR2(25);
160 r_cfth IGS_PS_FEE_TRG_HIST%ROWTYPE;
161 v_create_history BOOLEAN := FALSE;
162 l_n_org_id IGS_PS_FEE_TRG_HIST.org_id%TYPE := igs_ge_gen_003.get_org_id;
163 BEGIN
164 -- Create a history for a IGS_PS_FEE_TRG record.
165 -- Check if any of the non-primary key fields have been changed
166 -- and set the flag v_create_history to indicate so.
167 IF NVL(p_new_version_number, 0) <> NVL(p_old_version_number, 0) THEN
168 r_cfth.version_number := p_old_version_number;
169 v_create_history := TRUE;
170 END IF;
171 IF NVL(p_new_cal_type, 'NULL') <> NVL(p_old_cal_type, 'NULL') THEN
172 r_cfth.CAL_TYPE := p_old_cal_type;
173 v_create_history := TRUE;
174 END IF;
175 IF NVL(p_new_location_cd, 'NULL') <> NVL(p_old_location_cd, 'NULL') THEN
176 r_cfth.location_cd := p_old_location_cd;
177 v_create_history := TRUE;
178 END IF;
179 IF NVL(p_new_attendance_mode, 'NULL') <>
180 NVL(p_old_attendance_mode, 'NULL') THEN
181 r_cfth.ATTENDANCE_MODE := p_old_attendance_mode;
182 v_create_history := TRUE;
183 END IF;
184 IF NVL(p_new_attendance_type, 'NULL') <>
185 NVL(p_old_attendance_type, 'NULL') THEN
186 r_cfth.ATTENDANCE_TYPE := p_old_attendance_type;
187 v_create_history := TRUE;
188 END IF;
189 IF p_new_create_dt <> p_old_create_dt THEN
190 r_cfth.create_dt := p_old_create_dt;
191 v_create_history := TRUE;
192 END IF;
193 IF NVL(p_new_fee_trigger_group_number, 0) <>
194 NVL(p_old_fee_trigger_group_number, 0) THEN
195 r_cfth.fee_trigger_group_number := p_old_fee_trigger_group_number;
196 v_create_history := TRUE;
197 END IF;
198 -- Create a history record if a column has changed value
199 IF v_create_history = TRUE THEN
200 r_cfth.FEE_CAT := p_fee_cat;
201 r_cfth.fee_cal_type := p_fee_cal_type;
202 r_cfth.fee_ci_sequence_number := p_fee_ci_sequence_number;
203 r_cfth.FEE_TYPE := p_fee_type;
204 r_cfth.course_cd := p_course_cd;
205 r_cfth.sequence_number := p_sequence_number;
206 r_cfth.hist_start_dt := p_old_last_update_date;
207 r_cfth.hist_end_dt := p_new_last_update_date;
208 r_cfth.hist_who := p_old_last_updated_by;
209 IGS_PS_FEE_TRG_HIST_PKG.INSERT_ROW(
210 X_ROWID => X_ROWID,
211 X_FEE_CAT => r_cfth.FEE_CAT,
212 X_fee_cal_type => r_cfth.fee_cal_type,
213 X_fee_ci_sequence_number => r_cfth.fee_ci_sequence_number,
214 X_FEE_TYPE => r_cfth.FEE_TYPE,
215 X_course_cd => r_cfth.course_cd,
216 X_sequence_number => r_cfth.sequence_number,
217 X_hist_start_dt => r_cfth.hist_start_dt,
218 X_hist_end_dt => r_cfth.hist_end_dt,
219 X_hist_who => r_cfth.hist_who,
220 X_version_number => r_cfth.version_number,
221 X_CAL_TYPE => r_cfth.CAL_TYPE,
222 X_location_cd => r_cfth.location_cd,
223 X_ATTENDANCE_MODE => r_cfth.ATTENDANCE_MODE,
224 X_ATTENDANCE_TYPE => r_cfth.ATTENDANCE_TYPE,
225 X_create_dt => r_cfth.create_dt,
226 X_fee_trigger_group_number => r_cfth.fee_trigger_group_number,
227 X_MODE => 'R',
228 x_org_id => l_n_org_id);
229 END IF;
230 END;
231 EXCEPTION
232 WHEN OTHERS THEN
233 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
234 FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GEN_002.FINP_INS_CFT_HIST');
235 IGS_GE_MSG_STACK.ADD;
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 END finp_ins_cft_hist;
238 --
239 -- PROCEDURE finp_ins_cma_hist has been obsoleted since table igs_fi_chg_mth_app was being made obsolte
240 -- this procedure invoked the TBH for the history table which was also being obsolete
241 -- bug# 2187247 (rnirwani)
242 --
243 PROCEDURE finp_ins_er_hist(
244 p_fee_type IN IGS_FI_ELM_RANGE.FEE_TYPE%TYPE ,
245 p_fee_cal_type IN IGS_FI_ELM_RANGE.fee_cal_type%TYPE ,
246 p_fee_ci_sequence_number IN IGS_FI_ELM_RANGE.fee_ci_sequence_number%TYPE ,
247 p_s_relation_type IN IGS_FI_ELM_RANGE.s_relation_type%TYPE ,
248 p_range_number IN IGS_FI_ELM_RANGE.range_number%TYPE ,
249 p_new_fee_cat IN IGS_FI_ELM_RANGE.FEE_CAT%TYPE ,
250 p_old_fee_cat IN IGS_FI_ELM_RANGE.FEE_CAT%TYPE ,
251 p_new_lower_range IN IGS_FI_ELM_RANGE.lower_range%TYPE ,
252 p_old_lower_range IN IGS_FI_ELM_RANGE.lower_range%TYPE ,
253 p_new_upper_range IN IGS_FI_ELM_RANGE.upper_range%TYPE ,
254 p_old_upper_range IN IGS_FI_ELM_RANGE.upper_range%TYPE ,
255 p_new_s_chg_method_type IN IGS_FI_ELM_RANGE.s_chg_method_type%TYPE ,
256 p_old_s_chg_method_type IN IGS_FI_ELM_RANGE.s_chg_method_type%TYPE ,
257 p_new_last_updated_by IN IGS_FI_ELM_RANGE.last_updated_by%TYPE ,
258 p_old_last_updated_by IN IGS_FI_ELM_RANGE.last_updated_by%TYPE ,
259 p_new_last_update_date IN IGS_FI_ELM_RANGE.last_update_date%TYPE ,
260 p_old_last_update_date IN IGS_FI_ELM_RANGE.last_update_date%TYPE )
261 AS
262 gv_other_detail VARCHAR2(255);
263 BEGIN
264 DECLARE
265 X_ROWID VARCHAR2(25);
266 r_erh IGS_FI_ELM_RANGE_H%ROWTYPE;
267 v_create_history BOOLEAN := FALSE;
268 l_n_org_id IGS_FI_ELM_RANGE_H.org_id%TYPE := igs_ge_gen_003.get_org_id;
269 BEGIN
270 -- Create a history for a IGS_FI_ELM_RANGE record.
271 -- Check if any of the non-primary key fields have been changed
272 -- and set the flag v_create_history to indicate so.
273 IF NVL(p_new_fee_cat, 'NULL') <> NVL(p_old_fee_cat, 'NULL') THEN
274 r_erh.FEE_CAT := p_old_fee_cat;
275 v_create_history := TRUE;
276 END IF;
277 IF NVL(p_new_lower_range, 0) <> NVL(p_old_lower_range, 0) THEN
278 r_erh.lower_range := p_old_lower_range;
279 v_create_history := TRUE;
280 END IF;
281 IF NVL(p_new_upper_range, 0) <> NVL(p_old_upper_range, 0) THEN
282 r_erh.upper_range := p_old_upper_range;
283 v_create_history := TRUE;
284 END IF;
285 IF NVL(p_new_s_chg_method_type, 'NULL') <>
286 NVL(p_old_s_chg_method_type, 'NULL') THEN
287 r_erh.s_chg_method_type := p_old_s_chg_method_type;
288 v_create_history := TRUE;
289 END IF;
290 -- Create a history record if a column has changed value
291 IF v_create_history = TRUE THEN
292 r_erh.FEE_TYPE := p_fee_type;
293 r_erh.fee_cal_type := p_fee_cal_type;
294 r_erh.fee_ci_sequence_number := p_fee_ci_sequence_number;
295 r_erh.s_relation_type := p_s_relation_type;
296 r_erh.range_number := p_range_number;
297 r_erh.hist_start_dt := p_old_last_update_date;
298 r_erh.hist_end_dt := p_new_last_update_date;
299 r_erh.hist_who := p_old_last_updated_by;
300 IGS_FI_ELM_RANGE_H_PKG.INSERT_ROW(
301 X_ROWID => X_ROWID,
302 X_FEE_TYPE => r_erh.FEE_TYPE,
303 X_fee_cal_type => r_erh.fee_cal_type,
304 X_fee_ci_sequence_number => r_erh.fee_ci_sequence_number,
305 X_s_relation_type => r_erh.s_relation_type,
306 X_range_number => r_erh.range_number,
307 X_hist_start_dt => r_erh.hist_start_dt,
308 X_hist_end_dt => r_erh.hist_end_dt,
309 X_hist_who => r_erh.hist_who,
310 X_FEE_CAT => r_erh.FEE_CAT,
311 X_lower_range => r_erh.lower_range,
312 X_upper_range => r_erh.upper_range,
313 X_s_chg_method_type => r_erh.s_chg_method_type,
314 X_MODE => 'R',
315 x_org_id => l_n_org_id);
316 END IF;
317 END;
318 EXCEPTION
319 WHEN OTHERS THEN
320 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
321 FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GEN_002.FINP_INS_ER_HIST');
322 IGS_GE_MSG_STACK.ADD;
323 APP_EXCEPTION.RAISE_EXCEPTION;
324 END finp_ins_er_hist;
325 --
326 PROCEDURE finp_ins_far_hist(
327 p_fee_type IN IGS_FI_FEE_AS_RATE.FEE_TYPE%TYPE ,
328 p_fee_cal_type IN IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE ,
329 p_fee_ci_sequence_number IN IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE ,
330 p_s_relation_type IN IGS_FI_FEE_AS_RATE.s_relation_type%TYPE ,
331 p_rate_number IN IGS_FI_FEE_AS_RATE.rate_number%TYPE ,
332 p_new_fee_cat IN IGS_FI_FEE_AS_RATE.FEE_CAT%TYPE ,
333 p_old_fee_cat IN IGS_FI_FEE_AS_RATE.FEE_CAT%TYPE ,
334 p_new_location_cd IN IGS_FI_FEE_AS_RATE.location_cd%TYPE ,
335 p_old_location_cd IN IGS_FI_FEE_AS_RATE.location_cd%TYPE ,
336 p_new_attendance_type IN IGS_FI_FEE_AS_RATE.ATTENDANCE_TYPE%TYPE ,
337 p_old_attendance_type IN IGS_FI_FEE_AS_RATE.ATTENDANCE_TYPE%TYPE ,
338 p_new_attendance_mode IN IGS_FI_FEE_AS_RATE.ATTENDANCE_MODE%TYPE ,
339 p_old_attendance_mode IN IGS_FI_FEE_AS_RATE.ATTENDANCE_MODE%TYPE ,
340 p_new_order_of_precedence IN IGS_FI_FEE_AS_RATE.order_of_precedence%TYPE ,
341 p_old_order_of_precedence IN IGS_FI_FEE_AS_RATE.order_of_precedence%TYPE ,
342 p_new_govt_hecs_payment_option IN IGS_FI_FEE_AS_RATE.GOVT_HECS_PAYMENT_OPTION%TYPE ,
343 p_old_govt_hecs_payment_option IN IGS_FI_FEE_AS_RATE.GOVT_HECS_PAYMENT_OPTION%TYPE ,
344 p_new_govt_hecs_cntrbtn_band IN IGS_FI_FEE_AS_RATE.govt_hecs_cntrbtn_band%TYPE ,
345 p_old_govt_hecs_cntrbtn_band IN IGS_FI_FEE_AS_RATE.govt_hecs_cntrbtn_band%TYPE ,
346 p_new_chg_rate IN IGS_FI_FEE_AS_RATE.chg_rate%TYPE ,
347 p_old_chg_rate IN IGS_FI_FEE_AS_RATE.chg_rate%TYPE ,
348 p_new_unit_class IN IGS_FI_FEE_AS_RATE.unit_class%TYPE ,
349 p_old_unit_class IN IGS_FI_FEE_AS_RATE.unit_class%TYPE ,
350 p_new_residency_status_cd IN IGS_FI_FEE_AS_RATE.residency_status_cd%TYPE,
351 p_old_residency_status_cd IN IGS_FI_FEE_AS_RATE.residency_status_cd%TYPE,
352 p_new_course_cd IN IGS_FI_FEE_AS_RATE.course_cd%TYPE,
353 p_old_course_cd IN IGS_FI_FEE_AS_RATE.course_cd%TYPE,
354 p_new_version_number IN IGS_FI_FEE_AS_RATE.version_number%TYPE,
355 p_old_version_number IN IGS_FI_FEE_AS_RATE.version_number%TYPE,
356 p_new_org_party_id IN IGS_FI_FEE_AS_RATE.org_party_id%TYPE,
357 p_old_org_party_id IN IGS_FI_FEE_AS_RATE.org_party_id%TYPE,
358 p_new_class_standing IN IGS_FI_FEE_AS_RATE.class_standing%TYPE,
359 p_old_class_standing IN IGS_FI_FEE_AS_RATE.class_standing%TYPE,
360 p_new_last_updated_by IN IGS_FI_FEE_AS_RATE.last_updated_by%TYPE,
361 p_old_last_updated_by IN IGS_FI_FEE_AS_RATE.last_updated_by%TYPE,
362 p_new_last_update_date IN IGS_FI_FEE_AS_RATE.last_update_date%TYPE,
363 p_old_last_update_date IN IGS_FI_FEE_AS_RATE.last_update_date%TYPE,
364 p_new_unit_set_cd IN igs_fi_fee_as_rate.unit_set_cd%TYPE,
365 p_old_unit_set_cd IN igs_fi_fee_as_rate.unit_set_cd%TYPE,
366 p_new_us_version_number IN igs_fi_fee_as_rate.us_version_number%TYPE,
367 p_old_us_version_number IN igs_fi_fee_as_rate.us_version_number%TYPE,
368 p_new_unit_cd IN igs_fi_fee_as_rate.unit_cd%TYPE DEFAULT NULL,
369 p_old_unit_cd IN igs_fi_fee_as_rate.unit_cd%TYPE DEFAULT NULL,
370 p_new_unit_version_number IN igs_fi_fee_as_rate.unit_version_number%TYPE DEFAULT NULL,
371 p_old_unit_version_number IN igs_fi_fee_as_rate.unit_version_number%TYPE DEFAULT NULL,
372 p_new_unit_level IN igs_fi_fee_as_rate.unit_level%TYPE DEFAULT NULL,
373 p_old_unit_level IN igs_fi_fee_as_rate.unit_level%TYPE DEFAULT NULL,
374 p_new_unit_type_id IN igs_fi_fee_as_rate.unit_type_id%TYPE DEFAULT NULL,
375 p_old_unit_type_id IN igs_fi_fee_as_rate.unit_type_id%TYPE DEFAULT NULL,
376 p_new_unit_mode IN igs_fi_fee_as_rate.unit_mode%TYPE DEFAULT NULL,
377 p_old_unit_mode IN igs_fi_fee_as_rate.unit_mode%TYPE DEFAULT NULL
378 ) AS
379 /*******************************************************************************************/
380 -- HISTORY
381 --Who When What
382 --svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
383 -- Unit Version and Unit Level
384 --pathipat 09-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
385 -- Added 4 new params for unit_set_cd and us_version_number
386 /*******************************************************************************************/
387 gv_other_detail VARCHAR2(255);
388 BEGIN
389 DECLARE
390 x_rowid VARCHAR2(25);
391 r_farh IGS_FI_FEE_AS_RT_H_ALL%ROWTYPE;
392 v_create_history BOOLEAN := FALSE;
393 l_n_org_id IGS_FI_FEE_AS_RT_H_ALL.org_id%TYPE := igs_ge_gen_003.get_org_id;
394 BEGIN
395 -- Create a history for a IGS_FI_FEE_AS_RATE record.
396 -- Check if any of the non-primary key fields have been changed
397 -- and set the flag v_create_history to indicate so.
398 IF NVL(p_new_fee_cat, 'NULL') <> NVL(p_old_fee_cat, 'NULL') THEN
399 r_farh.FEE_CAT := p_old_fee_cat;
400 v_create_history := TRUE;
401 END IF;
402 IF NVL(p_new_location_cd, 'NULL') <> NVL(p_old_location_cd, 'NULL') THEN
403 r_farh.location_cd := p_old_location_cd;
404 v_create_history := TRUE;
405 END IF;
406 IF NVL(p_new_attendance_type, 'NULL') <>
407 NVL(p_old_attendance_type, 'NULL') THEN
408 r_farh.ATTENDANCE_TYPE := p_old_attendance_type;
409 v_create_history := TRUE;
410 END IF;
411 IF NVL(p_new_attendance_mode, 'NULL') <>
412 NVL(p_old_attendance_mode, 'NULL') THEN
413 r_farh.ATTENDANCE_MODE := p_old_attendance_mode;
414 v_create_history := TRUE;
415 END IF;
416 IF NVL(p_new_order_of_precedence, 0) <> NVL(p_old_order_of_precedence, 0) THEN
417 r_farh.order_of_precedence := p_old_order_of_precedence;
418 v_create_history := TRUE;
419 END IF;
420 IF NVL(p_new_govt_hecs_payment_option, 'NULL') <>
421 NVL(p_old_govt_hecs_payment_option, 'NULL') THEN
422 r_farh.GOVT_HECS_PAYMENT_OPTION := p_old_govt_hecs_payment_option;
423 v_create_history := TRUE;
424 END IF;
425 IF NVL(p_new_govt_hecs_cntrbtn_band, 0) <>
426 NVL(p_old_govt_hecs_cntrbtn_band, 0) THEN
427 r_farh.govt_hecs_cntrbtn_band := p_old_govt_hecs_cntrbtn_band;
428 v_create_history := TRUE;
429 END IF;
430 IF p_new_chg_rate <> p_old_chg_rate THEN
431 r_farh.chg_rate := p_old_chg_rate;
432 v_create_history := TRUE;
433 END IF;
434
435 -- Code added for New Field Unit Class added to the IGS_FI_FEE_AS_RATE table
436 IF ((p_new_unit_class <> p_old_unit_class) OR
437 ((p_new_unit_class IS NOT NULL AND p_old_unit_class IS NULL) OR
438 (p_new_unit_class IS NULL AND p_old_unit_class IS NOT NULL))) THEN
439 r_farh.unit_class := p_old_unit_class;
440 v_create_history := TRUE;
441 END IF;
442 -- Code added for five New Fields added to the IGS_FI_FEE_AS_RATE table
443 IF NVL(p_new_residency_status_cd,0) <> NVL(p_old_residency_status_cd,0) THEN
444 r_farh.residency_status_cd := p_old_residency_status_cd;
445 v_create_history := TRUE;
446 END IF;
447 IF NVL(p_new_course_cd,'NULL') <> NVL(p_old_course_cd,'NULL') THEN
448 r_farh.course_cd := p_old_course_cd;
449 v_create_history := TRUE;
450 END IF;
451 IF NVL(p_new_version_number,0) <> NVL(p_old_version_number,0) THEN
452 r_farh.version_number := p_old_version_number;
453 v_create_history := TRUE;
454 END IF;
455 IF NVL(p_new_org_party_id,0) <> NVL(p_old_org_party_id,0) THEN
456 r_farh.org_party_id := p_old_org_party_id;
457 v_create_history := TRUE;
458 END IF;
459 IF NVL(p_new_class_standing,'NULL') <> NVL(p_old_class_standing,'NULL') THEN
460 r_farh.class_standing := p_old_class_standing;
461 v_create_history := TRUE;
462 END IF;
463 IF ((p_new_unit_set_cd <> p_old_unit_set_cd)
464 OR ( (p_new_unit_set_cd IS NOT NULL AND p_old_unit_set_cd IS NULL) OR
465 (p_new_unit_set_cd IS NULL AND p_old_unit_set_cd IS NOT NULL)
466 )
467 ) THEN
468 r_farh.unit_set_cd := p_old_unit_set_cd;
469 v_create_history := TRUE;
470 END IF;
471 IF NVL(p_new_us_version_number,0) <> NVL(p_old_us_version_number,0) THEN
472 r_farh.us_version_number := p_old_us_version_number;
473 v_create_history := TRUE;
474 END IF;
475
476 IF ((p_new_unit_cd <> p_old_unit_cd) OR
477 ((p_new_unit_cd IS NOT NULL AND p_old_unit_cd IS NULL) OR
478 (p_new_unit_cd IS NULL AND p_old_unit_cd IS NOT NULL))) THEN
479 r_farh.unit_cd := p_old_unit_cd;
480 v_create_history := TRUE;
481 END IF;
482 IF NVL(p_new_unit_version_number,0) <> NVL(p_old_unit_version_number,0) THEN
483 r_farh.unit_version_number := p_old_unit_version_number;
484 v_create_history := TRUE;
485 END IF;
486
487 IF ((p_new_unit_level <> p_old_unit_level) OR
488 ((p_new_unit_level IS NOT NULL AND p_old_unit_level IS NULL) OR
489 (p_new_unit_level IS NULL AND p_old_unit_level IS NOT NULL))) THEN
490 r_farh.unit_level := p_old_unit_level;
491 v_create_history := TRUE;
492 END IF;
493 IF ((p_new_unit_mode <> p_old_unit_mode) OR
494 ((p_new_unit_mode IS NOT NULL AND p_old_unit_mode IS NULL) OR
495 (p_new_unit_mode IS NULL AND p_old_unit_mode IS NOT NULL))) THEN
496 r_farh.unit_mode := p_old_unit_mode;
497 v_create_history := TRUE;
498 END IF;
499
500 IF NVL(p_new_unit_type_id,0) <> NVL(p_old_unit_type_id,0) THEN
501 r_farh.unit_type_id := p_old_unit_type_id;
502 v_create_history := TRUE;
503 END IF;
504
505 -- Create a history record if a column has changed value
506 IF v_create_history = TRUE THEN
507 r_farh.fee_type := p_fee_type;
508 r_farh.fee_cal_type := p_fee_cal_type;
509 r_farh.fee_ci_sequence_number := p_fee_ci_sequence_number;
510 r_farh.s_relation_type := p_s_relation_type;
511 r_farh.rate_number := p_rate_number;
512 r_farh.hist_start_dt := p_old_last_update_date;
513 r_farh.hist_end_dt := p_new_last_update_date;
514 r_farh.hist_who := p_old_last_updated_by;
515
516 igs_fi_fee_as_rt_h_pkg.insert_row(
517 x_rowid => x_rowid,
518 x_fee_type => r_farh.fee_type,
519 x_fee_cal_type => r_farh.fee_cal_type,
520 x_fee_ci_sequence_number => r_farh.fee_ci_sequence_number,
521 x_s_relation_type => r_farh.s_relation_type,
522 x_rate_number => r_farh.rate_number,
523 x_hist_start_dt => r_farh.hist_start_dt,
524 x_hist_end_dt => r_farh.hist_end_dt,
525 x_hist_who => r_farh.hist_who,
526 x_fee_cat => r_farh.fee_cat,
527 x_location_cd => r_farh.location_cd,
528 x_attendance_type => r_farh.attendance_type,
529 x_attendance_mode => r_farh.attendance_mode,
530 x_order_of_precedence => r_farh.order_of_precedence,
531 x_govt_hecs_payment_option => r_farh.govt_hecs_payment_option,
532 x_govt_hecs_cntrbtn_band => r_farh.govt_hecs_cntrbtn_band,
533 x_chg_rate => r_farh.chg_rate,
534 x_unit_class => r_farh.unit_class,
535 -- added by nishikant , to include the following five new fields for enhancement bug#1851586
536 x_residency_status_cd => r_farh.residency_status_cd,
537 x_course_cd => r_farh.course_cd,
538 x_version_number => r_farh.version_number,
539 x_org_party_id => r_farh.org_party_id,
540 x_class_standing => r_farh.class_standing,
541 x_org_id => l_n_org_id,
542 x_mode => 'R',
543 x_unit_set_cd => r_farh.unit_set_cd,
544 x_us_version_number => r_farh.us_version_number,
545 --svuppala Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code, Unit Version and Unit Level
546 x_unit_cd => r_farh.unit_cd,
547 x_unit_version_number => r_farh.unit_version_number,
548 x_unit_level => r_farh.unit_level ,
549 x_unit_type_id => r_farh.unit_type_id,
550 x_unit_mode => r_farh.unit_mode
551 );
552 END IF;
553 END;
554 EXCEPTION
555 WHEN OTHERS THEN
556 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
557 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_FAR_HIST');
558 igs_ge_msg_stack.add;
559 app_exception.raise_exception;
560 END finp_ins_far_hist;
561 --
562 PROCEDURE finp_ins_fcci_hist(
563 p_fee_cat IN IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE ,
564 p_fee_cal_type IN IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE ,
565 p_fee_ci_sequence_number IN IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE ,
566 p_new_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
567 p_old_fee_cat_ci_status IN IGS_FI_F_CAT_CA_INST.fee_cat_ci_status%TYPE ,
568 p_new_start_dt_alias IN IGS_FI_F_CAT_CA_INST.start_dt_alias%TYPE ,
569 p_old_start_dt_alias IN IGS_FI_F_CAT_CA_INST.start_dt_alias%TYPE ,
570 p_new_start_dai_sequence_num IN IGS_FI_F_CAT_CA_INST.start_dai_sequence_number%TYPE ,
571 p_old_start_dai_sequence_num IN IGS_FI_F_CAT_CA_INST.start_dai_sequence_number%TYPE ,
572 p_new_end_dt_alias IN IGS_FI_F_CAT_CA_INST.end_dt_alias%TYPE ,
573 p_old_end_dt_alias IN IGS_FI_F_CAT_CA_INST.end_dt_alias%TYPE ,
574 p_new_end_dai_sequence_num IN IGS_FI_F_CAT_CA_INST.end_dai_sequence_number%TYPE ,
575 p_old_end_dai_sequence_num IN IGS_FI_F_CAT_CA_INST.end_dai_sequence_number%TYPE ,
576 p_new_retro_dt_alias IN IGS_FI_F_CAT_CA_INST.retro_dt_alias%TYPE ,
577 p_old_retro_dt_alias IN IGS_FI_F_CAT_CA_INST.retro_dt_alias%TYPE ,
578 p_new_retro_dai_sequence_num IN IGS_FI_F_CAT_CA_INST.retro_dai_sequence_number%TYPE ,
579 p_old_retro_dai_sequence_num IN IGS_FI_F_CAT_CA_INST.retro_dai_sequence_number%TYPE ,
580 p_new_last_updated_by IN IGS_FI_F_CAT_CA_INST.last_updated_by%TYPE ,
581 p_old_last_updated_by IN IGS_FI_F_CAT_CA_INST.last_updated_by%TYPE ,
582 p_new_last_update_date IN IGS_FI_F_CAT_CA_INST.last_update_date%TYPE ,
583 p_old_last_update_date IN IGS_FI_F_CAT_CA_INST.last_update_date%TYPE )
584 AS
585 gv_other_detail VARCHAR2(255);
586 BEGIN
587 DECLARE
588 X_ROWID VARCHAR2(25);
589 r_fccih IGS_FI_FEE_CAT_CI_HT%ROWTYPE;
590 v_create_history BOOLEAN := FALSE;
591 l_n_org_id IGS_FI_FEE_CAT_CI_HT.org_id%TYPE := igs_ge_gen_003.get_org_id;
592 BEGIN
593 -- Create a history for the IGS_FI_F_CAT_CA_INST table.
594 -- Check if any of the non-primary key fields have been changed
595 -- and set the flag v_create_history to indicate so.
596 IF p_new_fee_cat_ci_status <> p_old_fee_cat_ci_status THEN
597 r_fccih.fee_cat_ci_status := p_old_fee_cat_ci_status;
598 v_create_history := TRUE;
599 END IF;
600 IF p_new_start_dt_alias <> p_old_start_dt_alias THEN
601 r_fccih.start_dt_alias := p_old_start_dt_alias ;
602 v_create_history := TRUE;
603 END IF;
604 IF p_new_start_dai_sequence_num <> p_old_start_dai_sequence_num THEN
605 r_fccih.start_dai_sequence_number := p_old_start_dai_sequence_num;
606 v_create_history := TRUE;
607 END IF;
608 IF p_new_end_dt_alias <> p_old_end_dt_alias THEN
609 r_fccih.end_dt_alias := p_old_end_dt_alias;
610 v_create_history := TRUE;
611 END IF;
612 IF p_new_end_dai_sequence_num <> p_old_end_dai_sequence_num THEN
613 r_fccih.end_dai_sequence_number := p_old_end_dai_sequence_num;
614 v_create_history := TRUE;
615 END IF;
616 IF NVL(p_new_retro_dt_alias, 'NULL') <> NVL(p_old_retro_dt_alias, 'NULL') THEN
617 r_fccih.retro_dt_alias := p_old_retro_dt_alias;
618 v_create_history := TRUE;
619 END IF;
620 IF NVL(p_new_retro_dai_sequence_num, 0) <>
621 NVL(p_old_retro_dai_sequence_num, 0) THEN
622 r_fccih.retro_dai_sequence_number := p_old_retro_dai_sequence_num;
623 v_create_history := TRUE;
624 END IF;
625 -- Create a history record if a column has changed value
626 IF v_create_history = TRUE THEN
627 r_fccih.FEE_CAT := p_fee_cat;
628 r_fccih.fee_cal_type := p_fee_cal_type;
629 r_fccih.fee_ci_sequence_number := p_fee_ci_sequence_number;
630 r_fccih.hist_start_dt := p_old_last_update_date;
631 r_fccih.hist_end_dt := p_new_last_update_date;
632 r_fccih.hist_who := p_old_last_updated_by;
633 IGS_FI_FEE_CAT_CI_HT_PKG.INSERT_ROW(
634 X_ROWID => X_ROWID,
635 X_FEE_CAT => r_fccih.FEE_CAT,
636 X_fee_cal_type => r_fccih.fee_cal_type,
637 X_fee_ci_sequence_number => r_fccih.fee_ci_sequence_number,
638 X_hist_start_dt => r_fccih.hist_start_dt,
639 X_hist_end_dt => r_fccih.hist_end_dt,
640 X_hist_who => r_fccih.hist_who,
641 X_fee_cat_ci_status => r_fccih.fee_cat_ci_status,
642 X_start_dt_alias => r_fccih.start_dt_alias,
643 X_start_dai_sequence_number => r_fccih.start_dai_sequence_number,
644 X_end_dt_alias => r_fccih.end_dt_alias,
645 X_end_dai_sequence_number => r_fccih.end_dai_sequence_number,
646 X_retro_dt_alias => r_fccih.retro_dt_alias,
647 X_retro_dai_sequence_number => r_fccih.retro_dai_sequence_number,
648 X_MODE => 'R',
649 x_org_id => l_n_org_id);
650 END IF;
651 END;
652 EXCEPTION
653 WHEN OTHERS THEN
654 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
655 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_FCCI_HIST');
656 IGS_GE_MSG_STACK.ADD;
657 APP_EXCEPTION.RAISE_EXCEPTION;
658 END finp_ins_fcci_hist;
659 --
660 PROCEDURE finp_ins_fcfl_hist(
661 p_fee_cat IN IGS_FI_FEE_CAT_ALL.FEE_CAT%TYPE ,
662 p_fee_cal_type IN IGS_CA_TYPE.CAL_TYPE%TYPE ,
663 p_fee_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
664 p_fee_type IN IGS_FI_FEE_TYPE_ALL.FEE_TYPE%TYPE ,
665 p_new_fee_liability_status IN VARCHAR2 ,
666 p_old_fee_liability_status IN VARCHAR2 ,
667 p_new_start_dt_alias IN IGS_CA_DA.DT_ALIAS%TYPE ,
668 p_old_start_dt_alias IN IGS_CA_DA.DT_ALIAS%TYPE ,
669 p_new_start_dai_sequence_num IN IGS_CA_DA_INST.sequence_number%TYPE ,
670 p_old_start_dai_sequence_num IN IGS_CA_DA_INST.sequence_number%TYPE ,
671 p_new_s_chg_method_type IN FND_LOOKUP_VALUES.lookup_code%TYPE ,
672 p_old_s_chg_method_type IN FND_LOOKUP_VALUES.lookup_code%TYPE ,
673 p_new_rul_sequence_number IN IGS_RU_RULE.sequence_number%TYPE ,
674 p_old_rul_sequence_number IN IGS_RU_RULE.sequence_number%TYPE ,
675 p_new_last_updated_by IN IGS_FI_F_CAT_FEE_LBL_ALL.last_updated_by%TYPE ,
676 p_old_last_updated_by IN IGS_FI_F_CAT_FEE_LBL_ALL.last_updated_by%TYPE ,
677 p_new_last_update_date IN IGS_FI_F_CAT_FEE_LBL_ALL.last_update_date%TYPE ,
678 p_old_last_update_date IN IGS_FI_F_CAT_FEE_LBL_ALL.last_update_date%TYPE )
679 AS
680 /*----------------------------------------------------------------------------
681 || Created By :
682 || Created On :
683 || Purpose :
684 || Known limitations, enhancements or remarks :
685 || Change History :
686 || Who When What
687 || (reverse chronological order - newest change first)
688 || vvutukur 19-Jul-2002 Bug#2425767.Removed parameters p_new_payment_hierarchy_rank,
689 || p_old_payment_hierarchy_rank and its references(from if condition and
690 || from call IGS_FI_F_CAT_F_LBL_H_PKG.INSERT_ROW).
691 ----------------------------------------------------------------------------*/
692
693 gv_other_detail VARCHAR2(255);
694 BEGIN
695 DECLARE
696 X_ROWID VARCHAR2(25);
697 r_fcflh IGS_FI_F_CAT_F_LBL_H%ROWTYPE;
698 v_create_history BOOLEAN := FALSE;
699 l_n_org_id IGS_FI_F_CAT_F_LBL_H.org_id%TYPE := igs_ge_gen_003.get_org_id;
700 BEGIN
701 -- Create a history for a IGS_FI_F_CAT_FEE_LBL record.
702 -- Check if any of the non-primary key fields have been changed
703 -- and set the flag v_create_history to indicate so.
704 IF p_new_fee_liability_status <> p_old_fee_liability_status THEN
705 r_fcflh.fee_liability_status := p_old_fee_liability_status;
706 v_create_history := TRUE;
707 END IF;
708 IF NVL(p_new_start_dt_alias, 'NULL') <> NVL(p_old_start_dt_alias, 'NULL') THEN
709 r_fcflh.start_dt_alias := p_old_start_dt_alias;
710 v_create_history := TRUE;
711 END IF;
712 IF NVL(p_new_start_dai_sequence_num, 0) <>
713 NVL(p_old_start_dai_sequence_num, 0) THEN
714 r_fcflh.start_dai_sequence_number := p_old_start_dai_sequence_num;
715 v_create_history := TRUE;
716 END IF;
717 IF NVL(p_new_s_chg_method_type, 'NULL') <>
718 NVL(p_old_s_chg_method_type, 'NULL') THEN
719 r_fcflh.s_chg_method_type := p_old_s_chg_method_type;
720 v_create_history := TRUE;
721 END IF;
722 IF NVL(p_new_rul_sequence_number, 0) <>
723 NVL(p_old_rul_sequence_number, 0) THEN
724 r_fcflh.rul_sequence_number := p_old_rul_sequence_number;
725 v_create_history := TRUE;
726 END IF;
727 -- Create a history record if a column has changed value
728 IF v_create_history = TRUE THEN
729 r_fcflh.FEE_CAT := p_fee_cat;
730 r_fcflh.fee_cal_type := p_fee_cal_type;
731 r_fcflh.fee_ci_sequence_number := p_fee_ci_sequence_number;
732 r_fcflh.FEE_TYPE := p_fee_type;
733 r_fcflh.hist_start_dt := p_old_last_update_date;
734 r_fcflh.hist_end_dt := p_new_last_update_date;
735 r_fcflh.hist_who := p_old_last_updated_by;
736 IGS_FI_F_CAT_F_LBL_H_PKG.INSERT_ROW(
737 X_ROWID => X_ROWID,
738 X_FEE_CAT => r_fcflh.FEE_CAT,
739 X_fee_cal_type => r_fcflh.fee_cal_type,
740 X_fee_ci_sequence_number => r_fcflh.fee_ci_sequence_number,
741 X_FEE_TYPE => r_fcflh.FEE_TYPE,
742 X_hist_start_dt => r_fcflh.hist_start_dt,
743 X_hist_end_dt => r_fcflh.hist_end_dt,
744 X_hist_who => r_fcflh.hist_who,
745 X_fee_liability_status => r_fcflh.fee_liability_status,
746 X_start_dt_alias => r_fcflh.start_dt_alias,
747 X_start_dai_sequence_number => r_fcflh.start_dai_sequence_number,
748 X_s_chg_method_type => r_fcflh.s_chg_method_type,
749 X_rul_sequence_number => r_fcflh.rul_sequence_number,
750 X_MODE => 'R',
751 x_org_id => l_n_org_id);
752 END IF;
753 END;
754 EXCEPTION
755 WHEN OTHERS THEN
756 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
757 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_FCFL_HIST');
758 IGS_GE_MSG_STACK.ADD;
759 APP_EXCEPTION.RAISE_EXCEPTION;
760 END finp_ins_fcfl_hist;
761 --
762 /* PROCEDURE finp_ins_fdf_hist() is obsolete as part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
763 --
764 --
765 /* Obseleted the procedure finp_ins_fe_hist( as part of bug 2126091 as this is no longer used */
766 --
767 --Removed the procedure finp_ins_fps_hist as part of SFCR005 Clean Up Build. Bug#2531390.
768 --
769 PROCEDURE finp_ins_frtns_hist(
770 p_fee_cal_type IN IGS_FI_FEE_RET_SCHD.fee_cal_type%TYPE ,
771 p_fee_ci_sequence_number IN IGS_FI_FEE_RET_SCHD.fee_ci_sequence_number%TYPE ,
772 p_s_relation_type IN IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE ,
773 p_sequence_number IN IGS_FI_FEE_RET_SCHD.sequence_number%TYPE ,
774 p_new_fee_type IN IGS_FI_FEE_RET_SCHD.FEE_TYPE%TYPE ,
775 p_old_fee_type IN IGS_FI_FEE_RET_SCHD.FEE_TYPE%TYPE ,
776 p_new_fee_cat IN IGS_FI_FEE_RET_SCHD.FEE_CAT%TYPE ,
777 p_old_fee_cat IN IGS_FI_FEE_RET_SCHD.FEE_CAT%TYPE ,
778 p_new_schedule_number IN IGS_FI_FEE_RET_SCHD.schedule_number%TYPE ,
779 p_old_schedule_number IN IGS_FI_FEE_RET_SCHD.schedule_number%TYPE ,
780 p_new_dt_alias IN IGS_FI_FEE_RET_SCHD.DT_ALIAS%TYPE ,
781 p_old_dt_alias IN IGS_FI_FEE_RET_SCHD.DT_ALIAS%TYPE ,
782 p_new_dai_sequence_number IN IGS_FI_FEE_RET_SCHD.dai_sequence_number%TYPE ,
783 p_old_dai_sequence_number IN IGS_FI_FEE_RET_SCHD.dai_sequence_number%TYPE ,
784 p_new_retention_percentage IN IGS_FI_FEE_RET_SCHD.retention_percentage%TYPE ,
785 p_old_retention_percentage IN IGS_FI_FEE_RET_SCHD.retention_percentage%TYPE ,
786 p_new_retention_amount IN IGS_FI_FEE_RET_SCHD.retention_amount%TYPE ,
787 p_old_retention_amount IN IGS_FI_FEE_RET_SCHD.retention_amount%TYPE ,
788 p_new_last_updated_by IN IGS_FI_FEE_RET_SCHD.last_updated_by%TYPE ,
789 p_old_last_updated_by IN IGS_FI_FEE_RET_SCHD.last_updated_by%TYPE ,
790 p_new_last_update_date IN IGS_FI_FEE_RET_SCHD.last_update_date%TYPE ,
791 p_old_last_update_date IN IGS_FI_FEE_RET_SCHD.last_update_date%TYPE )
792 AS
793 /*----------------------------------------------------------------------------
794 || Created By :
795 || Created On :
796 || Purpose :
797 || Known limitations, enhancements or remarks :
798 || Change History :
799 || Who When What
800 || (reverse chronological order - newest change first)
801 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter p_new_deduction_amount,p_old_deduction_amount
802 || and its references(if condition and from call IGS_FI_F_RET_SCHD_HT_PKG.INSERT_ROW).
803 ----------------------------------------------------------------------------*/
804 gv_other_detail VARCHAR2(255);
805 BEGIN
806 DECLARE
807 X_ROWID VARCHAR2(25);
808 r_frtnsh IGS_FI_F_RET_SCHD_HT%ROWTYPE;
809 v_create_history BOOLEAN := FALSE;
810 l_n_org_id IGS_FI_F_RET_SCHD_HT.org_id%TYPE := igs_ge_gen_003.get_org_id;
811 BEGIN
812 -- Create a history for a IGS_FI_FEE_RET_SCHD record.
813 -- Check if any of the non-primary key fields have been changed
814 -- and set the flag v_create_history to indicate so.
815 IF NVL(p_new_fee_type, 'NULL') <> NVL(p_old_fee_type, 'NULL') THEN
816 r_frtnsh.FEE_TYPE := p_old_fee_type;
817 v_create_history := TRUE;
818 END IF;
819 IF NVL(p_new_fee_cat, 'NULL') <> NVL(p_old_fee_cat, 'NULL') THEN
820 r_frtnsh.FEE_CAT := p_old_fee_cat;
821 v_create_history := TRUE;
822 END IF;
823
824 --Commented the underline code while solving bug 1534058.As schedule number never changes on updating the existing
825 --exist records so the p_old_schedule_number should be passed as such without any validation in IF-END FI loop
826 --If included in the loop the condition will never be satisfied and as a result shecudule will always be passed as a null.
827 -- Due to which it will result in unique key voilation and record will not get insert into history table.
828
829
830 --IF p_new_schedule_number <> p_old_schedule_number THEN
831 r_frtnsh.schedule_number := p_old_schedule_number;
832 -- v_create_history := TRUE;
833 -- END IF;
834 IF NVL(p_new_dt_alias, 'NULL') <> NVL(p_old_dt_alias, 'NULL') THEN
835 r_frtnsh.DT_ALIAS := p_old_dt_alias;
836 v_create_history := TRUE;
837 END IF;
838 IF NVL(p_new_dai_sequence_number, 0) <> NVL(p_old_dai_sequence_number, 0) THEN
839 r_frtnsh.dai_sequence_number := p_old_dai_sequence_number;
840 v_create_history := TRUE;
841 END IF;
842 IF NVL(p_new_retention_percentage, 0) <>
843 NVL(p_old_retention_percentage, 0) THEN
844 r_frtnsh.retention_percentage := p_old_retention_percentage;
845 v_create_history := TRUE;
846 END IF;
847 IF NVL(p_new_retention_amount, 0) <> NVL(p_old_retention_amount, 0) THEN
848 r_frtnsh.retention_amount := p_old_retention_amount;
849 v_create_history := TRUE;
850 END IF;
851 -- Create a history record if a column has changed value
852 IF v_create_history = TRUE THEN
853 r_frtnsh.fee_cal_type := p_fee_cal_type;
854 r_frtnsh.fee_ci_sequence_number := p_fee_ci_sequence_number;
855 r_frtnsh.s_relation_type := p_s_relation_type;
856 r_frtnsh.sequence_number := p_sequence_number;
857 r_frtnsh.hist_start_dt := p_old_last_update_date;
858 r_frtnsh.hist_end_dt := p_new_last_update_date;
859 r_frtnsh.hist_who := p_old_last_updated_by;
860 IGS_FI_F_RET_SCHD_HT_PKG.INSERT_ROW(
861 X_ROWID => X_ROWID,
862 X_fee_cal_type => r_frtnsh.fee_cal_type,
863 X_fee_ci_sequence_number => r_frtnsh.fee_ci_sequence_number,
864 X_s_relation_type => r_frtnsh.s_relation_type,
865 X_sequence_number => r_frtnsh.sequence_number,
866 X_hist_start_dt => r_frtnsh.hist_start_dt,
867 X_hist_end_dt => r_frtnsh.hist_end_dt,
868 X_hist_who => r_frtnsh.hist_who,
869 X_FEE_TYPE => r_frtnsh.FEE_TYPE,
870 X_FEE_CAT => r_frtnsh.FEE_CAT,
871 X_schedule_number => r_frtnsh.schedule_number,
872 X_DT_ALIAS => r_frtnsh.DT_ALIAS,
873 X_dai_sequence_number => r_frtnsh.dai_sequence_number,
874 X_retention_percentage => r_frtnsh.retention_percentage,
875 X_retention_amount => r_frtnsh.retention_amount,
876 X_MODE => 'R',
877 x_org_id => l_n_org_id);
878 END IF;
879 END;
880 EXCEPTION
881 WHEN OTHERS THEN
882 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
883 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_FRTNS_HIST');
884 IGS_GE_MSG_STACK.ADD;
885 APP_EXCEPTION.RAISE_EXCEPTION;
886 END finp_ins_frtns_hist;
887 --
888 PROCEDURE finp_ins_ftci_hist(
889 p_fee_type IN IGS_FI_F_TYP_CA_INST_ALL.FEE_TYPE%TYPE ,
890 p_fee_cal_type IN IGS_FI_F_TYP_CA_INST_ALL.fee_cal_type%TYPE ,
891 p_fee_ci_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.fee_ci_sequence_number%TYPE ,
892 p_new_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST_ALL.fee_type_ci_status%TYPE ,
893 p_old_fee_type_ci_status IN IGS_FI_F_TYP_CA_INST_ALL.fee_type_ci_status%TYPE ,
894 p_new_start_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.start_dt_alias%TYPE ,
895 p_old_start_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.start_dt_alias%TYPE ,
896 p_new_start_dai_sequence_num IN IGS_FI_F_TYP_CA_INST_ALL.start_dai_sequence_number%TYPE ,
897 p_old_start_dai_sequence_num IN IGS_FI_F_TYP_CA_INST_ALL.start_dai_sequence_number%TYPE ,
898 p_new_end_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.end_dt_alias%TYPE ,
899 p_old_end_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.end_dt_alias%TYPE ,
900 p_new_end_dai_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.end_dai_sequence_number%TYPE ,
901 p_old_end_dai_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.end_dai_sequence_number%TYPE ,
902 p_new_retro_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.retro_dt_alias%TYPE ,
903 p_old_retro_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.retro_dt_alias%TYPE ,
904 p_new_retro_dai_sequence_num IN IGS_FI_F_TYP_CA_INST_ALL.retro_dai_sequence_number%TYPE ,
905 p_old_retro_dai_sequence_num IN IGS_FI_F_TYP_CA_INST_ALL.retro_dai_sequence_number%TYPE ,
906 p_new_s_chg_method_type IN IGS_FI_F_TYP_CA_INST_ALL.s_chg_method_type%TYPE ,
907 p_old_s_chg_method_type IN IGS_FI_F_TYP_CA_INST_ALL.s_chg_method_type%TYPE ,
908 p_new_rul_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.rul_sequence_number%TYPE ,
909 p_old_rul_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.rul_sequence_number%TYPE ,
910 p_new_last_updated_by IN IGS_FI_F_TYP_CA_INST_ALL.last_updated_by%TYPE ,
911 p_old_last_updated_by IN IGS_FI_F_TYP_CA_INST_ALL.last_updated_by%TYPE ,
912 p_new_last_update_date IN IGS_FI_F_TYP_CA_INST_ALL.last_update_date%TYPE ,
913 p_old_last_update_date IN IGS_FI_F_TYP_CA_INST_ALL.last_update_date%TYPE,
914 -- Added by Nishikant , to include the following new fields for enhancement bug#1851586
915 p_new_initial_default_amount IN IGS_FI_F_TYP_CA_INST_ALL.initial_default_amount%TYPE,
916 p_old_initial_default_amount IN IGS_FI_F_TYP_CA_INST_ALL.initial_default_amount%TYPE,
917 --Added by svuppala,as a part of bug:4295379 New field "nonzero_billable_cp_flag"
918 p_new_nonzero_billable_cp_flag IN IGS_FI_F_TYP_CA_INST_ALL.nonzero_billable_cp_flag%TYPE,
919 p_old_nonzero_billable_cp_flag IN IGS_FI_F_TYP_CA_INST_ALL.nonzero_billable_cp_flag%TYPE)
920
921 AS
922 /*----------------------------------------------------------------------------
923 || Created By :
924 || Created On :
925 || Purpose :
926 || Known limitations, enhancements or remarks :
927 || Change History :
928 || Who When What
929 || (reverse chronological order - newest change first)
930 || svuppala 13-Apr-2005 Bug 4297359 - ER REGISTRATION FEE ISSUE - ASSESSED TO STUDENTS WITH NO LOAD
931 || Added new field NONZERO_BILLABLE_CP_FLAG in IGS_FI_FEE_TYPE_CI_H_ALL
932 || vvutukur 19-Jul-2002 Bug#2425767.Removed parameters p_new_payment_hierarchy_rank,
933 || p_old_payment_hierarchy_rank and its references(from if condition and
934 || from call to IGS_FI_FEE_TYPE_CI_H_PKG.INSERT_ROW.
935 ----------------------------------------------------------------------------*/
936 gv_other_detail VARCHAR2(255);
937 BEGIN
938 DECLARE
939 X_ROWID VARCHAR2(25);
940 r_ftcih IGS_FI_FEE_TYPE_CI_H%ROWTYPE;
941 v_create_history BOOLEAN := FALSE;
942 l_n_org_id IGS_FI_FEE_TYPE_CI_H.org_id%TYPE := igs_ge_gen_003.get_org_id;
943 BEGIN
944 -- Check if any of the non-primary key fields have been changed
945 -- and set the flag v_create_history to indicate so.
946 IF p_new_fee_type_ci_status <> p_old_fee_type_ci_status THEN
947 r_ftcih.fee_type_ci_status := p_old_fee_type_ci_status;
948 v_create_history := TRUE;
949 END IF;
950 IF p_new_start_dt_alias <> p_old_start_dt_alias THEN
951 r_ftcih.start_dt_alias := p_old_start_dt_alias;
952 v_create_history := TRUE;
953 END IF;
954 IF p_new_start_dai_sequence_num <> p_old_start_dai_sequence_num THEN
955 r_ftcih.start_dai_sequence_number := p_old_start_dai_sequence_num;
956 v_create_history := TRUE;
957 END IF;
958 IF p_new_end_dt_alias <> p_old_end_dt_alias THEN
959 r_ftcih.end_dt_alias := p_old_end_dt_alias;
960 v_create_history := TRUE;
961 END IF;
962 IF p_new_end_dai_sequence_number <> p_old_end_dai_sequence_number THEN
963 r_ftcih.end_dai_sequence_number := p_old_end_dai_sequence_number;
964 v_create_history := TRUE;
965 END IF;
966 IF NVL(p_new_retro_dt_alias, 'NULL') <> NVL(p_old_retro_dt_alias, 'NULL') THEN
967 r_ftcih.retro_dt_alias := p_old_retro_dt_alias;
968 v_create_history := TRUE;
969 END IF;
970 IF NVL(p_new_retro_dai_sequence_num, 0) <>
971 NVL(p_old_retro_dai_sequence_num, 0) THEN
972 r_ftcih.retro_dai_sequence_number := p_old_retro_dai_sequence_num;
973 v_create_history := TRUE;
974 END IF;
975 IF NVL(p_new_s_chg_method_type, 'NULL') <>
976 NVL(p_old_s_chg_method_type, 'NULL') THEN
977 r_ftcih.s_chg_method_type := p_old_s_chg_method_type;
978 v_create_history := TRUE;
979 END IF;
980 IF NVL(p_new_rul_sequence_number, 0) <> NVL(p_old_rul_sequence_number, 0) THEN
981 r_ftcih.rul_sequence_number := p_old_rul_sequence_number;
982 v_create_history := TRUE;
983 END IF;
984 IF NVL(p_new_initial_default_amount, 0) <>
985 NVL(p_old_initial_default_amount, 0) THEN
986 r_ftcih.initial_default_amount := p_old_initial_default_amount;
987 v_create_history := TRUE;
988 END IF;
989 IF NVL(p_new_nonzero_billable_cp_flag, 0) <>
990 NVL(p_old_nonzero_billable_cp_flag, 0) THEN
991 r_ftcih.nonzero_billable_cp_flag := p_old_nonzero_billable_cp_flag;
992 v_create_history := TRUE;
993 END IF;
994
995 -- Create a history record if a column has changed value
996 IF v_create_history = TRUE THEN
997 r_ftcih.FEE_TYPE := p_fee_type;
998 r_ftcih.fee_cal_type := p_fee_cal_type;
999 r_ftcih.fee_ci_sequence_number := p_fee_ci_sequence_number;
1000 r_ftcih.hist_start_dt := p_old_last_update_date;
1001 r_ftcih.hist_end_dt := p_new_last_update_date;
1002 r_ftcih.hist_who := p_old_last_updated_by;
1003 r_ftcih.fee_type_ci_status := p_old_fee_type_ci_status;
1004 IGS_FI_FEE_TYPE_CI_H_PKG.INSERT_ROW(
1005 X_ROWID => X_ROWID,
1006 X_FEE_TYPE => r_ftcih.FEE_TYPE,
1007 X_fee_cal_type => r_ftcih.fee_cal_type,
1008 X_fee_ci_sequence_number => r_ftcih.fee_ci_sequence_number,
1009 X_hist_start_dt => r_ftcih.hist_start_dt,
1010 X_hist_end_dt => r_ftcih.hist_end_dt,
1011 X_hist_who => r_ftcih.hist_who,
1012 X_fee_type_ci_status => r_ftcih.fee_type_ci_status,
1013 X_start_dt_alias => r_ftcih.start_dt_alias,
1014 X_start_dai_sequence_number => r_ftcih.start_dai_sequence_number,
1015 X_end_dt_alias => r_ftcih.retro_dt_alias,
1016 X_end_dai_sequence_number => r_ftcih.retro_dai_sequence_number,
1017 X_retro_dt_alias => r_ftcih.retro_dt_alias,
1018 X_retro_dai_sequence_number => r_ftcih.retro_dai_sequence_number,
1019 X_s_chg_method_type => r_ftcih.s_chg_method_type,
1020 X_rul_sequence_number => r_ftcih.rul_sequence_number,
1021 -- Added by Nishikant , to include the following new field for enhancement bug#1851586
1022 X_initial_default_amount => r_ftcih.initial_default_amount,
1023 X_MODE => 'R',
1024 x_org_id => l_n_org_id,
1025 --Added by svuppala,as a part of bug:4295379 New field "nonzero_billable_cp_flag"
1026 x_nonzero_billable_cp_flag => r_ftcih.nonzero_billable_cp_flag);
1027 END IF;
1028 END;
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1032 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_FTCI_HIST');
1033 IGS_GE_MSG_STACK.ADD;
1034 APP_EXCEPTION.RAISE_EXCEPTION;
1035 END finp_ins_ftci_hist;
1036 --
1037 PROCEDURE finp_ins_ft_hist(
1038 p_fee_type IN IGS_FI_FEE_TYPE_ALL.FEE_TYPE%TYPE ,
1039 p_new_s_fee_type IN IGS_FI_FEE_TYPE_ALL.s_fee_type%TYPE ,
1040 p_old_s_fee_type IN IGS_FI_FEE_TYPE_ALL.s_fee_type%TYPE ,
1041 p_new_s_fee_trigger_cat IN IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE ,
1042 p_old_s_fee_trigger_cat IN IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE ,
1043 p_new_description IN IGS_FI_FEE_TYPE_ALL.description%TYPE ,
1044 p_old_description IN IGS_FI_FEE_TYPE_ALL.description%TYPE ,
1045 p_new_optional_payment_ind IN IGS_FI_FEE_TYPE_ALL.optional_payment_ind%TYPE ,
1046 p_old_optional_payment_ind IN IGS_FI_FEE_TYPE_ALL.optional_payment_ind%TYPE ,
1047 p_new_closed_ind IN IGS_FI_FEE_TYPE_ALL.closed_ind%TYPE ,
1048 p_old_closed_ind IN IGS_FI_FEE_TYPE_ALL.closed_ind%TYPE ,
1049 p_new_fee_class IN IGS_FI_FEE_TYPE_ALL.fee_class%TYPE, --for Bug 2175865
1050 p_old_fee_class IN IGS_FI_FEE_TYPE_ALL.fee_class%TYPE, --for Bug 2175865
1051 p_new_designated_payment_flag IN igs_fi_fee_type_all.designated_payment_flag%TYPE,
1052 p_old_designated_payment_flag IN igs_fi_fee_type_all.designated_payment_flag%TYPE,
1053 p_new_last_updated_by IN IGS_FI_FEE_TYPE_ALL.last_updated_by%TYPE ,
1054 p_old_last_updated_by IN IGS_FI_FEE_TYPE_ALL.last_updated_by%TYPE ,
1055 p_new_last_update_date IN IGS_FI_FEE_TYPE_ALL.last_update_date%TYPE ,
1056 p_old_last_update_date IN IGS_FI_FEE_TYPE_ALL.last_update_date%TYPE ,
1057 p_new_comments IN IGS_FI_FEE_TYPE_ALL.comments%TYPE ,
1058 p_old_comments IN IGS_FI_FEE_TYPE_ALL.comments%TYPE )
1059 AS
1060 /*----------------------------------------------------------------------------
1061 || Created By :
1062 || Created On :
1063 || Purpose :
1064 || Known limitations, enhancements or remarks :
1065 || Change History :
1066 || Who When What
1067 || (reverse chronological order - newest change first)
1068 || shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
1069 || vvutukur 13-Sep-2002 Enh#2564643.Removed parameters p_new_subaccount_id,p_old_subaccount_id.
1070 || and related validation,also removed the parameter P_SUBACCOUNT_ID
1071 || from the call to IGS_FI_FEE_TYPE_HIST_PKG.INSERT_ROW.
1072 ----------------------------------------------------------------------------*/
1073
1074 gv_other_detail VARCHAR2(255);
1075 BEGIN
1076 DECLARE
1077 X_ROWID VARCHAR2(25) := NULL;
1078 r_fth IGS_FI_FEE_TYPE_HIST%ROWTYPE;
1079 v_create_history BOOLEAN := FALSE;
1080 l_n_org_id IGS_FI_FEE_TYPE_HIST.org_id%TYPE := igs_ge_gen_003.get_org_id;
1081 BEGIN
1082 -- Create a history for the IGS_FI_FEE_TYPE table.
1083 -- Check if any of the non-primary key fields have been changed
1084 -- and set the flag v_create_history to indicate so.
1085 IF NVL(p_new_s_fee_type, 'NULL') <> NVL(p_old_s_fee_type, 'NULL') THEN
1086 r_fth.s_fee_type := p_old_s_fee_type;
1087 v_create_history := TRUE;
1088 END IF;
1089 IF NVL(p_new_s_fee_trigger_cat, 'NULL') <>
1090 NVL(p_old_s_fee_trigger_cat, 'NULL') THEN
1091 r_fth.s_fee_trigger_cat := p_old_s_fee_trigger_cat;
1092 v_create_history := TRUE;
1093 END IF;
1094 IF NVL(p_new_description, 'NULL') <> NVL(p_old_description, 'NULL') THEN
1095 r_fth.description := p_old_description;
1096 v_create_history := TRUE;
1097 END IF;
1098 IF NVL(p_new_optional_payment_ind, 'NULL') <>
1099 NVL(p_old_optional_payment_ind, 'NULL') THEN
1100 r_fth.optional_payment_ind := p_old_optional_payment_ind;
1101 v_create_history := TRUE;
1102 END IF;
1103 IF NVL(p_new_closed_ind, 'NULL') <> NVL(p_old_closed_ind, 'NULL') THEN
1104 r_fth.closed_ind := p_old_closed_ind;
1105 v_create_history := TRUE;
1106 END IF;
1107 IF NVL(p_new_comments, 'NULL') <> NVL(p_old_comments, 'NULL') THEN
1108 r_fth.comments := p_old_comments;
1109 v_create_history := TRUE;
1110 END IF;
1111 --for Bug 2175865 as part of SFCR017/SFCR022
1112 IF NVL(p_new_fee_class, 'NULL') <>
1113 NVL(p_old_fee_class, 'NULL') THEN
1114 r_fth.fee_class := p_old_fee_class;
1115 v_create_history :=TRUE;
1116 END IF;
1117
1118 -- Added designated_payment_flag as part of Enh# 2831582, Lockbox Build
1119 IF NVL(p_new_designated_payment_flag, 'NULL') <> NVL(p_old_designated_payment_flag, 'NULL') THEN
1120 r_fth.designated_payment_flag := p_old_designated_payment_flag;
1121 v_create_history :=TRUE;
1122 END IF;
1123
1124 -- Create a history record if a column has changed value
1125 IF v_create_history = TRUE THEN
1126 r_fth.FEE_TYPE := p_fee_type;
1127 r_fth.hist_start_dt := p_old_last_update_date;
1128 r_fth.hist_end_dt := p_new_last_update_date;
1129 r_fth.hist_who := p_old_last_updated_by;
1130 IGS_FI_FEE_TYPE_HIST_PKG.INSERT_ROW(
1131 X_ROWID => X_ROWID,
1132 X_FEE_TYPE => r_fth.FEE_TYPE,
1133 X_hist_start_dt => r_fth.hist_start_dt,
1134 X_hist_end_dt => r_fth.hist_end_dt,
1135 X_hist_who => r_fth.hist_who,
1136 X_s_fee_type => r_fth.s_fee_type,
1137 X_s_fee_trigger_cat => r_fth.s_fee_trigger_cat,
1138 X_description => r_fth.description,
1139 X_optional_payment_ind => r_fth.optional_payment_ind,
1140 X_closed_ind => r_fth.closed_ind,
1141 X_comments => r_fth.comments,
1142 X_Fee_class => r_fth.fee_class,
1143 x_designated_payment_flag => r_fth.designated_payment_flag,
1144 X_MODE => 'R',
1145 x_org_id => l_n_org_id);
1146 END IF;
1147 END;
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1151 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_FT_HIST'||sqlerrm);
1152 IGS_GE_MSG_STACK.ADD;
1153 APP_EXCEPTION.RAISE_EXCEPTION;
1154 END finp_ins_ft_hist;
1155
1156 --
1157 --ENH # 2170429 by masehgal on 16-Jan-2002
1158 --Obsoletion of SPONSOR_CD,SPONSORED_AMOUNT from finp_ins_pps_hist
1159 --
1160 -- As part of Enh Bug#2531390 the procedure finp_ins_pps_hist was removed
1161 --
1162
1163 PROCEDURE finp_ins_uft_hist(
1164 p_fee_cat IN IGS_FI_UNIT_FEE_TRG.FEE_CAT%TYPE ,
1165 p_fee_cal_type IN IGS_FI_UNIT_FEE_TRG.fee_cal_type%TYPE ,
1166 p_fee_ci_sequence_number IN IGS_FI_UNIT_FEE_TRG.fee_ci_sequence_number%TYPE ,
1167 p_fee_type IN IGS_FI_UNIT_FEE_TRG.FEE_TYPE%TYPE ,
1168 p_unit_cd IN IGS_FI_UNIT_FEE_TRG.unit_cd%TYPE ,
1169 p_sequence_number IN IGS_FI_UNIT_FEE_TRG.sequence_number%TYPE ,
1170 p_new_version_number IN IGS_FI_UNIT_FEE_TRG.version_number%TYPE ,
1171 p_old_version_number IN IGS_FI_UNIT_FEE_TRG.version_number%TYPE ,
1172 p_new_cal_type IN IGS_FI_UNIT_FEE_TRG.CAL_TYPE%TYPE ,
1173 p_old_cal_type IN IGS_FI_UNIT_FEE_TRG.CAL_TYPE%TYPE ,
1174 p_new_ci_sequence_number IN IGS_FI_UNIT_FEE_TRG.ci_sequence_number%TYPE ,
1175 p_old_ci_sequence_number IN IGS_FI_UNIT_FEE_TRG.ci_sequence_number%TYPE ,
1176 p_new_location_cd IN IGS_FI_UNIT_FEE_TRG.location_cd%TYPE ,
1177 p_old_location_cd IN IGS_FI_UNIT_FEE_TRG.location_cd%TYPE ,
1178 p_new_unit_class IN IGS_FI_UNIT_FEE_TRG.UNIT_CLASS%TYPE ,
1179 p_old_unit_class IN IGS_FI_UNIT_FEE_TRG.UNIT_CLASS%TYPE ,
1180 p_new_create_dt IN IGS_FI_UNIT_FEE_TRG.create_dt%TYPE ,
1181 p_old_create_dt IN IGS_FI_UNIT_FEE_TRG.create_dt%TYPE ,
1182 p_new_fee_trigger_group_number IN IGS_FI_UNIT_FEE_TRG.fee_trigger_group_number%TYPE ,
1183 p_old_fee_trigger_group_number IN IGS_FI_UNIT_FEE_TRG.fee_trigger_group_number%TYPE ,
1184 p_new_last_updated_by IN IGS_FI_UNIT_FEE_TRG.last_updated_by%TYPE ,
1185 p_old_last_updated_by IN IGS_FI_UNIT_FEE_TRG.last_updated_by%TYPE ,
1186 p_new_last_update_date IN IGS_FI_UNIT_FEE_TRG.last_update_date%TYPE ,
1187 p_old_last_update_date IN IGS_FI_UNIT_FEE_TRG.last_update_date%TYPE )
1188 AS
1189 gv_other_detail VARCHAR2(255);
1190 BEGIN
1191 DECLARE
1192 X_ROWID VARCHAR2(25);
1193 r_ufth IGS_FI_UNIT_FEE_TR_H%ROWTYPE;
1194 v_create_history BOOLEAN := FALSE;
1195 l_n_org_id IGS_FI_UNIT_FEE_TR_H.org_id%TYPE := igs_ge_gen_003.get_org_id;
1196 BEGIN
1197 -- Create a history for a IGS_FI_UNIT_FEE_TRG record.
1198 -- Check if any of the non-primary key fields have been changed
1199 -- and set the flag v_create_history to indicate so.
1200 IF NVL(p_new_version_number, 0) <> NVL(p_old_version_number, 0) THEN
1201 r_ufth.version_number := p_old_version_number;
1202 v_create_history := TRUE;
1203 END IF;
1204 IF NVL(p_new_cal_type, 'NULL') <> NVL(p_old_cal_type, 'NULL') THEN
1205 r_ufth.CAL_TYPE := p_old_cal_type;
1206 v_create_history := TRUE;
1207 END IF;
1208 IF NVL(p_new_ci_sequence_number, 0) <> NVL(p_old_ci_sequence_number, 0) THEN
1209 r_ufth.ci_sequence_number := p_old_ci_sequence_number;
1210 v_create_history := TRUE;
1211 END IF;
1212 IF NVL(p_new_location_cd, 'NULL') <> NVL(p_old_location_cd, 'NULL') THEN
1213 r_ufth.location_cd := p_old_location_cd;
1214 v_create_history := TRUE;
1215 END IF;
1216 IF NVL(p_new_unit_class, 'NULL') <> NVL(p_old_unit_class, 'NULL') THEN
1217 r_ufth.UNIT_CLASS := p_old_unit_class;
1218 v_create_history := TRUE;
1219 END IF;
1220 IF p_new_create_dt <> p_old_create_dt THEN
1221 r_ufth.create_dt := p_old_create_dt;
1222 v_create_history := TRUE;
1223 END IF;
1224 IF NVL(p_new_fee_trigger_group_number, 0) <>
1225 NVL(p_old_fee_trigger_group_number, 0) THEN
1226 r_ufth.fee_trigger_group_number := p_old_fee_trigger_group_number;
1227 v_create_history := TRUE;
1228 END IF;
1229 -- Create a history record if a column has changed value
1230 IF v_create_history = TRUE THEN
1231 r_ufth.FEE_CAT := p_fee_cat;
1232 r_ufth.fee_cal_type := p_fee_cal_type;
1233 r_ufth.fee_ci_sequence_number := p_fee_ci_sequence_number;
1234 r_ufth.FEE_TYPE := p_fee_type;
1235 r_ufth.unit_cd := p_unit_cd;
1236 r_ufth.sequence_number := p_sequence_number;
1237 r_ufth.hist_start_dt := p_old_last_update_date;
1238 r_ufth.hist_end_dt := p_new_last_update_date;
1239 r_ufth.hist_who := p_old_last_updated_by;
1240 IGS_FI_UNIT_FEE_TR_H_PKG.INSERT_ROW(
1241 X_ROWID => X_ROWID,
1242 X_FEE_CAT => r_ufth.FEE_CAT,
1243 X_fee_cal_type => r_ufth.fee_cal_type,
1244 X_fee_ci_sequence_number => r_ufth.fee_ci_sequence_number,
1245 X_FEE_TYPE => r_ufth.FEE_TYPE,
1246 X_unit_cd => r_ufth.unit_cd,
1247 X_sequence_number => r_ufth.sequence_number,
1248 X_hist_start_dt => r_ufth.hist_start_dt,
1249 X_hist_end_dt => r_ufth.hist_end_dt,
1250 X_hist_who => r_ufth.hist_who,
1251 X_version_number => r_ufth.version_number,
1252 X_CAL_TYPE => r_ufth.CAL_TYPE,
1253 X_ci_sequence_number => r_ufth.ci_sequence_number,
1254 X_location_cd => r_ufth.location_cd,
1255 X_UNIT_CLASS => r_ufth.UNIT_CLASS,
1256 X_create_dt => r_ufth.create_dt,
1257 X_fee_trigger_group_number => r_ufth.fee_trigger_group_number,
1258 X_MODE => 'R',
1259 x_org_id => l_n_org_id);
1260 END IF;
1261 END;
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1265 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_002.FINP_INS_UFT_HIST');
1266 IGS_GE_MSG_STACK.ADD;
1267 APP_EXCEPTION.RAISE_EXCEPTION;
1268 END finp_ins_uft_hist;
1269
1270 -- New function as part of Bug 2324088 - SYkrishn
1271 FUNCTION finp_validate_ccid(p_ccid IN igs_fi_cr_activities.dr_gl_ccid%TYPE) RETURN BOOLEAN AS
1272
1273 /***********************************************************************************************
1274 Created By: sykrishn
1275 Date Created By: 19-APR-2002
1276 Purpose: This generic function this function validates whether
1277 the ccid exists in the GL_CODE_COMBINATIONS table for the chart of account
1278 identified by the Set of Books. The function call igs_fi_gen_007.get_coa_id
1279 is called to get the Chart of Accounts for the Set of Books setup
1280 in the IGS_FI_CONTROL. - Bug 2324088
1281 Known limitations,enhancements,remarks:
1282 Change History
1283 Who When What
1284 ********************************************************************************************** */
1285 --Cursor to check existence of the ccid with the combination of the chart of accounts
1286
1287 CURSOR cur_ccid_exist (cp_ccid IN igs_fi_cr_activities.dr_gl_ccid%TYPE) IS
1288 SELECT 'X'
1289 FROM gl_code_combinations
1290 WHERE code_combination_id = cp_ccid
1291 AND chart_of_accounts_id = igs_fi_gen_007.get_coa_id;
1292
1293 l_ccid_exist VARCHAR2(1);
1294 BEGIN
1295 OPEN cur_ccid_exist (p_ccid);
1296 FETCH cur_ccid_exist INTO l_ccid_exist;
1297 IF cur_ccid_exist%FOUND THEN
1298 CLOSE cur_ccid_exist;
1299 RETURN TRUE;
1300 ELSE
1301 CLOSE cur_ccid_exist;
1302 RETURN FALSE;
1303 END IF;
1304 END finp_validate_ccid;
1305 -- End of New function added as part of Bug 2324088 - SYkrishn
1306 END IGS_FI_GEN_002;
[Home] [Help]