1 PACKAGE BODY IGS_FI_GEN_003 AS
2 /* $Header: IGSFI03B.pls 120.5 2005/08/29 05:21:14 appldev ship $ */
3
4 /******************************************************************
5 Change History
6 svuppala 23-JUN-2005 Bug 3392088 Modifications as part of CPF build
7 Added 2 functions finp_del_fsert , finp_del_fser
8 rnirwani 05-May-02 removed reference to IGS_FI_DSBR_SPSHT
9 bug# 2329407
10 rnirwani 25-Apr-02 Obsoleted the procedures:
11 finp_ins_disb_jnl
12 FINP_DEL_DISB_JNL
13 Bug# 2329407
14 --
15 -- nalkumar 30-Nov-2001 Removed the funtion FINP_INS_PRSN_ENCMB from this package.
16 -- This is as per the SFCR015-HOLDS DLD. Bug:2126091
17 -- nalkumar 16-Jan-2002 Added 'SET VERIFY OFF' before whenever sqlerr... |
18 --
19 --
20 *************************************************************************/
21
22 PROCEDURE FINP_DEL_DISB_SNPSHT(
23 errbuf out NOCOPY varchar2,
24 retcode out NOCOPY NUMBER,
25 p_fee_period IN VARCHAR2,
26 p_fee_type VARCHAR2 ,
27 p_snapshot_create_dt_C VARCHAR2 ,
28 p_delete_ds_ind VARCHAR2 ,
29 p_delete_dsd_ind VARCHAR2 ,
30 p_delete_dda_ind VARCHAR2 ,
31 p_org_id NUMBER
32 ) AS
33 BEGIN
34 -- As per SFCR005, this Concurrent Program is obsolete and if the user is trying to
35 -- run the program then an error message should be written to the Log file that
36 -- the Concurrent Program is obsolete and this should not be run
37 FND_MESSAGE.Set_Name('IGS',
38 'IGS_GE_OBSOLETE_JOB');
39 FND_FILE.Put_Line(FND_FILE.Log,
40 FND_MESSAGE.Get);
41 retcode := 0;
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETCODE:=2;
46 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
47 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
48 END finp_del_disb_snpsht;
49 --
50 FUNCTION finp_del_err(
51 p_fee_type IN VARCHAR2 ,
52 p_fee_cal_type IN VARCHAR2 ,
53 p_fee_ci_sequence_number IN NUMBER ,
54 p_s_relation_type IN VARCHAR2 ,
55 p_fee_cat IN VARCHAR2 ,
56 p_range_number IN NUMBER ,
57 p_rate_number IN NUMBER ,
58 p_message_name OUT NOCOPY VARCHAR2 )
59 RETURN BOOLEAN AS
60 gv_other_detail VARCHAR2(255);
61 BEGIN -- finp_del_err
62 -- This routine will logically delete all IGS_FI_ELM_RANGE_RT records
63 -- associated with an IGS_FI_ELM_RANGE or IGS_FI_FEE_AS_RATE record which is
64 -- being logically deleted.
65 DECLARE
66 e_resource_busy_exception EXCEPTION;
67 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
68 CURSOR c_err IS
69 SELECT
70 ROWID,
71 err_id,
72 FEE_TYPE,
73 FEE_CAL_TYPE,
74 FEE_CI_SEQUENCE_NUMBER,
75 S_RELATION_TYPE,
76 RANGE_NUMBER,
77 RATE_NUMBER,
78 CREATE_DT,
79 FEE_CAT,
80 LOGICAL_DELETE_DT
81 FROM IGS_FI_ELM_RANGE_RT err
82 WHERE err.FEE_TYPE = p_fee_type AND
83 err.fee_cal_type = p_fee_cal_type AND
84 err.fee_ci_sequence_number = p_fee_ci_sequence_number AND
85 err.s_relation_type = p_s_relation_type AND
86 NVL(err.FEE_CAT, 'NULL') = NVL(p_fee_cat, 'NULL') AND
87 err.range_number = NVL(p_range_number, err.range_number) AND
88 err.rate_number = NVL(p_rate_number, err.rate_number) AND
89 err.logical_delete_dt IS NULL
90 FOR UPDATE OF err.logical_delete_dt NOWAIT;
91 BEGIN
92 -- Set the default message number
93 p_message_name := Null;
94 -- 1. Check parameters.
95 IF p_fee_type IS NULL OR
96 p_fee_cal_type IS NULL OR
97 p_fee_ci_sequence_number IS NULL OR
98 p_s_relation_type IS NULL OR
99 (p_range_number IS NULL AND
100 p_rate_number IS NULL) OR
101 (p_range_number IS NOT NULL AND
102 p_rate_number IS NOT NULL) THEN
103 RETURN TRUE;
104 END IF;
105 -- 2. Issue a save point for the module so that if locks exist,
106 -- a rollback can be performed.
107 SAVEPOINT sp_save_point;
108 -- Perform a logical delete of the associated IGS_FI_ELM_RANGE_RT items.
109 -- Update the appropriate records with NOWAIT option.
110 FOR v_err_rec IN c_err LOOP
111 IGS_FI_ELM_RANGE_RT_PKG.UPDATE_ROW(
112 X_ROWID => v_err_rec.ROWID,
113 X_ERR_ID => v_err_rec.ERR_ID,
114 X_FEE_TYPE => v_err_rec.FEE_TYPE,
115 X_FEE_CAL_TYPE => v_err_rec.FEE_CAL_TYPE,
116 X_FEE_CI_SEQUENCE_NUMBER => v_err_rec.FEE_CI_SEQUENCE_NUMBER,
117 X_S_RELATION_TYPE => v_err_rec.S_RELATION_TYPE,
118 X_RANGE_NUMBER => v_err_rec.RANGE_NUMBER,
119 X_RATE_NUMBER => v_err_rec.RATE_NUMBER,
120 X_CREATE_DT => v_err_rec.CREATE_DT,
121 X_FEE_CAT => v_err_rec.FEE_CAT,
122 X_LOGICAL_DELETE_DT => SYSDATE,
123 X_MODE => 'R');
124 END LOOP;
125 RETURN TRUE;
126 EXCEPTION
127 WHEN e_resource_busy_exception THEN
128 IF c_err%ISOPEN THEN
129 CLOSE c_err;
130 END IF;
131 ROLLBACK TO sp_save_point;
132 p_message_name := 'IGS_FI_UNABLE_LOGDEL_ELERNG';
133 RETURN FALSE;
134 WHEN OTHERS THEN
135 IF c_err%ISOPEN THEN
136 CLOSE c_err;
137 END IF;
138 RAISE;
139 END;
140 EXCEPTION
141 WHEN OTHERS THEN
142 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
143 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_003.FINP_DEL_ERR');
144 IGS_GE_MSG_STACK.ADD;
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END finp_del_err;
147 --
148 PROCEDURE finp_del_minor_debt(
149 errbuf out NOCOPY varchar2,
150 retcode out NOCOPY number,
151 P_FEE_ASSESSMENT_PERIOD IN VARCHAR2 ,
152 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
153 p_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
154 p_course_cd IN igs_ps_course.course_cd%type,
155 p_person_id IN HZ_PARTIES.PARTY_ID%type,
156 p_group_id IN IGS_PE_PERSID_GROUP_ALL.group_id%TYPE ,
157 p_min_days_overdue IN NUMBER,
158 p_max_outstanding IN IGS_FI_FEE_AS_ALL.transaction_amount%TYPE ,
159 p_comments IN igs_fi_fee_as_all.comments%type,
160 p_org_id NUMBER
161 ) AS
162
163 BEGIN
164 -- As per the CCR05 for Student Finance, this concurrent program is obsoleted
165 -- and if the user tries to run this program ,then a message should be logged in the
166 -- Error log that the Concurrent Program is obsolete and should not be run.
167 FND_MESSAGE.Set_Name('IGS',
168 'IGS_GE_OBSOLETE_JOB');
169 FND_FILE.Put_Line(FND_FILE.Log,
170 FND_MESSAGE.Get);
171 retcode := 0;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETCODE:=2;
176 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
177 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
178 END finp_del_minor_debt;
179 --
180 FUNCTION finp_ins_cfar(
181 p_person_id IGS_FI_FEE_AS_RT.person_id%TYPE ,
182 p_course_cd IGS_FI_FEE_AS_RT.course_cd%TYPE ,
183 p_fee_type IGS_FI_FEE_AS_RT.FEE_TYPE%TYPE ,
184 p_start_dt IGS_FI_FEE_AS_RT.start_dt%TYPE ,
185 p_end_dt IGS_FI_FEE_AS_RT.end_dt%TYPE ,
186 p_location_cd IGS_FI_FEE_AS_RT.location_cd%TYPE ,
187 p_attendance_type IGS_FI_FEE_AS_RT.ATTENDANCE_TYPE%TYPE ,
188 p_attendance_mode IGS_FI_FEE_AS_RT.ATTENDANCE_MODE%TYPE ,
189 p_chg_rate IGS_FI_FEE_AS_RT.chg_rate%TYPE ,
190 p_lower_nrml_rate_ovrd_ind IGS_FI_FEE_AS_RT.lower_nrml_rate_ovrd_ind%TYPE ,
191 p_message_name OUT NOCOPY VARCHAR2 )
192 RETURN BOOLEAN AS
193 gv_other_detail VARCHAR2(255);
194 BEGIN -- finp_ins_cfar
195 -- This routine is used to insert a contract fee assessment rate record.
196 DECLARE
197 X_ROWID VARCHAR2(25);
198 v_ret_val BOOLEAN DEFAULT TRUE;
199 BEGIN
200 p_message_name := Null;
201 IF NOT IGS_FI_VAL_CFAR.finp_val_ft_closed(
202 p_fee_type,
203 p_message_name) THEN
204 RETURN FALSE;
205 END IF;
206 IF NOT IGS_FI_VAL_CFAR.finp_val_cfar_ins(
207 p_person_id,
208 p_course_cd,
209 p_fee_type,
210 p_message_name) THEN
211 RETURN FALSE;
212 END IF;
213 IF NOT IGS_FI_VAL_CFAR.finp_val_att_closed(
214 p_attendance_type,
215 p_message_name) THEN
216 RETURN FALSE;
217 END IF;
218 IF NOT IGS_FI_VAL_CFAR.finp_val_am_closed(
219 p_attendance_mode,
220 p_message_name) THEN
221 RETURN FALSE;
222 END IF;
223 IF NOT IGS_FI_VAL_CFAR.finp_val_loc_closed(
224 p_location_cd,
225 p_message_name) THEN
226 RETURN FALSE;
227 END IF;
228 --Validate the IGS_FI_FEE_AS_RT (cfar) table to ensure that for
229 --records with the same person_id, course_cd and IGS_FI_FEE_TYPE, that only
230 --one record has a open end_dt.
231 IF p_end_dt IS NULL THEN
232 IF NOT IGS_FI_VAL_CFAR.finp_val_cfar_open (
233 p_person_id,
234 p_course_cd,
235 p_fee_type,
236 p_start_dt,
237 p_message_name) THEN
238 RETURN FALSE;
239 END IF;
240 END IF;
241 --Validate the IGS_FI_FEE_AS_RT (cfar) table to ensure that for
242 --records with the same person_id, course_cd and IGS_FI_FEE_TYPE, that the
243 --date ranges don't overlap.
244 IF NOT IGS_FI_VAL_CFAR.finp_val_cfar_ovrlp (
245 p_person_id,
246 p_course_cd,
247 p_fee_type,
248 p_start_dt,
249 p_end_dt,
250 p_message_name) THEN
251 RETURN FALSE;
252 END IF;
253 --Insert the contract fee assessment rate
254 IGS_FI_FEE_AS_RT_PKG.INSERT_ROW(
255 X_ROWID => X_ROWID,
256 X_person_id => p_person_id,
257 X_course_cd => p_course_cd,
258 X_FEE_TYPE => p_fee_type,
259 X_start_dt => p_start_dt,
260 X_end_dt => p_end_dt,
261 X_location_cd => p_location_cd,
262 X_ATTENDANCE_TYPE => p_attendance_type,
263 X_ATTENDANCE_MODE => p_attendance_mode,
264 X_chg_rate => p_chg_rate,
265 X_lower_nrml_rate_ovrd_ind => p_lower_nrml_rate_ovrd_ind,
266 X_MODE => 'R');
267 RETURN v_ret_val;
268 END;
269 EXCEPTION
270 WHEN OTHERS THEN
271 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
272 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_003.FINP_INS_CFAR');
273 IGS_GE_MSG_STACK.ADD;
274 APP_EXCEPTION.RAISE_EXCEPTION;
275
276 END finp_ins_cfar;
277
278 -- Function to delete FSERT block
279 FUNCTION finp_del_fsert(
280 p_sub_er_id NUMBER,
281 p_message_name OUT NOCOPY VARCHAR2 )
282 RETURN BOOLEAN AS
283 gv_other_detail VARCHAR2(255);
284 BEGIN
285 -- This routine will logically delete all IGS_FI_SUB_ER_RT records
286
287 DECLARE
288 e_resource_busy_exception EXCEPTION;
289 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
290 CURSOR c_fsert IS
291 SELECT
292 ROWID,
293 SUB_ERR_ID ,
294 SUB_ER_ID ,
295 FAR_ID ,
296 CREATE_DATE ,
297 LOGICAL_DELETE_DATE
298 FROM IGS_FI_SUB_ER_RT fsert
299 WHERE fsert.sub_er_id = p_sub_er_id AND
300 fsert.logical_delete_date IS NULL
301 FOR UPDATE OF fsert.logical_delete_date NOWAIT;
302 BEGIN
303 -- Set the default message number
304 p_message_name := Null;
305 -- 1. Check parameters.
306 IF (p_sub_er_id IS NULL)
307 THEN
308 RETURN TRUE;
309 END IF;
310 -- 2. Issue a save point for the module so that if locks exist,
311 -- a rollback can be performed.
312 SAVEPOINT sp_save_point;
313 -- Perform a logical delete of the associated IGS_FI_SUB_ER_RT items.
314 -- Update the appropriate records with NOWAIT option.
315 FOR v_fsert_rec IN c_fsert LOOP
316 igs_fi_sub_er_rt_pkg.update_row (
317 x_mode => 'R',
318 x_rowid => v_fsert_rec.ROWID,
319 x_sub_err_id => v_fsert_rec.SUB_ERR_ID,
320 x_sub_er_id => v_fsert_rec.SUB_ER_ID,
321 x_far_id => v_fsert_rec.FAR_ID,
322 x_create_date => v_fsert_rec.CREATE_DATE,
323 x_logical_delete_date => SYSDATE
324 );
325
326 END LOOP;
327 RETURN TRUE;
328 EXCEPTION
329 WHEN e_resource_busy_exception THEN
330 IF c_fsert%ISOPEN THEN
331 CLOSE c_fsert;
332 END IF;
333 ROLLBACK TO sp_save_point;
334 p_message_name := 'IGS_FI_UNABLE_LOGDEL_SUB_ER_RT';
335 RETURN FALSE;
336 WHEN OTHERS THEN
337 IF c_fsert%ISOPEN THEN
338 CLOSE c_fsert;
339 END IF;
340 RAISE;
341 END;
342 EXCEPTION
343 WHEN OTHERS THEN
344 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
345 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_003.FINP_DEL_FSERT');
346 IGS_GE_MSG_STACK.ADD;
347 APP_EXCEPTION.RAISE_EXCEPTION;
348 END finp_del_fsert;
349
350
351 FUNCTION finp_del_fser(
352 p_er_id NUMBER,
353 p_message_name OUT NOCOPY VARCHAR2 )
354 RETURN BOOLEAN AS
355 gv_other_detail VARCHAR2(255);
356 BEGIN
357 -- This routine will logically delete all IGS_FI_SUB_ER_RT records
358
359 DECLARE
360 e_resource_busy_exception EXCEPTION;
361 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
362 CURSOR c_fser IS
363 SELECT
364 ROWID,
365 SUB_ER_ID ,
366 ER_ID ,
367 sub_range_num,
368 sub_lower_range,
369 sub_upper_range,
370 sub_chg_method_code,
371 LOGICAL_DELETE_DATE
372 FROM IGS_FI_SUB_ELM_RNG fser
373 WHERE fser.er_id = p_er_id AND
374 fser.logical_delete_date IS NULL
375 FOR UPDATE OF fser.logical_delete_date NOWAIT;
376 v_message_name VARCHAR2(30);
377 v_message_icon varchar2(10);
378 BEGIN
379 -- Set the default message number
380 p_message_name := Null;
381 -- 1. Check parameters.
382 IF (p_er_id IS NULL)
383 THEN
384 RETURN TRUE;
385 END IF;
386 -- 2. Issue a save point for the module so that if locks exist,
387 -- a rollback can be performed.
388 SAVEPOINT sp_fser_save_point;
389 -- Perform a logical delete of the associated IGS_FI_SUB_ELM_RNG items.
390 -- Update the appropriate records with NOWAIT option.
391 FOR v_fser_rec IN c_fser LOOP
392
393 igs_fi_sub_elm_rng_pkg.update_row (
394 x_mode => 'R',
395 x_rowid => v_fser_rec.ROWID,
396 x_sub_er_id => v_fser_rec.SUB_ER_ID,
397 x_er_id => v_fser_rec.ER_ID,
398 x_sub_range_num => v_fser_rec.SUB_RANGE_NUM,
399 x_sub_lower_range => v_fser_rec.SUB_LOWER_RANGE,
400 x_sub_upper_range => v_fser_rec.SUB_UPPER_RANGE,
401 x_sub_chg_method_code => v_fser_rec.SUB_CHG_METHOD_CODE,
402 x_logical_delete_date => SYSDATE
403 );
404
405 IF IGS_FI_GEN_003.FINP_DEL_FSERT (v_fser_rec.SUB_ER_ID,
406 v_message_name) = FALSE THEN
407 fnd_message.set_name ('IGS', v_message_name);
408 FND_FILE.Put_Line(FND_FILE.Log,
409 FND_MESSAGE.Get);
410 END IF;
411
412
413 END LOOP;
414 RETURN TRUE;
415 EXCEPTION
416 WHEN e_resource_busy_exception THEN
417 IF c_fser%ISOPEN THEN
418 CLOSE c_fser;
419 END IF;
420 ROLLBACK TO sp_fser_save_point;
421 p_message_name := ': IGS_FI_UNABLE_LOGDEL_SUB_ELM_RNG';
422 RETURN FALSE;
423 WHEN OTHERS THEN
424 IF c_fser%ISOPEN THEN
425 CLOSE c_fser;
426 END IF;
427 RAISE;
428 END;
429 EXCEPTION
430 WHEN OTHERS THEN
431 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
432 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_003.FINP_DEL_FSER');
433 IGS_GE_MSG_STACK.ADD;
434 APP_EXCEPTION.RAISE_EXCEPTION;
435 END finp_del_fser;
436
437 -- Function to delete Rate in FSERT block
438 FUNCTION finp_del_sub_rt(
439 p_sub_err_id NUMBER,
440 p_message_name OUT NOCOPY VARCHAR2 )
441 RETURN BOOLEAN AS
442 gv_other_detail VARCHAR2(255);
443 BEGIN
444 -- This routine will logically delete particular record in IGS_FI_SUB_ER_RT
445
446 DECLARE
447 e_resource_busy_exception EXCEPTION;
448 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
449 CURSOR c_fsert IS
450 SELECT
451 ROWID,
452 SUB_ER_ID ,
453 FAR_ID ,
454 CREATE_DATE
455 FROM IGS_FI_SUB_ER_RT fsert
456 WHERE fsert.sub_err_id = p_sub_err_id AND
457 fsert.logical_delete_date IS NULL
458 FOR UPDATE OF fsert.logical_delete_date NOWAIT;
459
460 l_rowid ROWID;
461 l_sub_er_id igs_fi_sub_er_rt.sub_er_id%TYPE;
462 l_far_id igs_fi_sub_er_rt.far_id%TYPE;
463 l_create_date igs_fi_sub_er_rt.create_date%TYPE;
464
465 BEGIN
466 -- Set the default message number
467 p_message_name := Null;
468 -- 1. Check parameters.
469 IF (p_sub_err_id IS NULL)
470 THEN
471 RETURN TRUE;
472 END IF;
473 -- 2. Issue a save point for the module so that if locks exist,
474 -- a rollback can be performed.
475 SAVEPOINT sp_save_point;
476 -- Perform a logical delete of the associated IGS_FI_SUB_ER_RT items.
477 -- Update the appropriate records with NOWAIT option.
478 OPEN c_fsert;
479 FETCH c_fsert INTO l_rowid, l_sub_er_id,l_far_id,l_create_date;
480 IF c_fsert%FOUND THEN
481 CLOSE c_fsert;
482 igs_fi_sub_er_rt_pkg.update_row (
483 x_mode => 'R',
484 x_rowid => l_rowid,
485 x_sub_err_id => p_sub_err_id,
486 x_sub_er_id => l_sub_er_id,
487 x_far_id => l_far_id,
488 x_create_date => l_create_date,
489 x_logical_delete_date => SYSDATE
490 );
491 RETURN TRUE;
492 END IF;
493 CLOSE c_fsert;
494 EXCEPTION
495 WHEN e_resource_busy_exception THEN
496 IF c_fsert%ISOPEN THEN
497 CLOSE c_fsert;
498 END IF;
499 ROLLBACK TO sp_save_point;
500 p_message_name := 'IGS_FI_UNABLE_LOGDEL_SUB_ER_RT';
501 RETURN FALSE;
502 WHEN OTHERS THEN
503 IF c_fsert%ISOPEN THEN
504 CLOSE c_fsert;
505 END IF;
506 RAISE;
507 END;
508 EXCEPTION
509 WHEN OTHERS THEN
510 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
511 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_003.FINP_DEL_SUB_RT');
512 IGS_GE_MSG_STACK.ADD;
513 APP_EXCEPTION.RAISE_EXCEPTION;
514 END finp_del_sub_rt;
515 --
516 END IGS_FI_GEN_003;
[Home] [Help]