[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_PERSON_HOLDS_PKG
Source
1 PACKAGE BODY igs_fi_person_holds_pkg AS
2 /* $Header: IGSSIB2B.pls 115.14 2003/09/19 12:31:18 smadathi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_person_holds%ROWTYPE;
6 new_references igs_fi_person_holds%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_person_id IN NUMBER ,
12 x_hold_plan_name IN VARCHAR2 ,
13 x_hold_type IN VARCHAR2 ,
14 x_hold_start_dt IN DATE ,
15 x_process_start_dt IN DATE ,
16 x_process_end_dt IN DATE ,
17 x_offset_days IN NUMBER ,
18 x_past_due_amount IN NUMBER ,
19 x_fee_cal_type IN VARCHAR2 ,
20 x_fee_ci_sequence_number IN NUMBER ,
21 x_fee_type_invoice_amount IN NUMBER ,
22 x_creation_date IN DATE ,
23 x_created_by IN NUMBER ,
24 x_last_update_date IN DATE ,
25 x_last_updated_by IN NUMBER ,
26 x_last_update_login IN NUMBER ,
27 x_release_credit_id IN NUMBER ,
28 x_student_plan_id IN NUMBER ,
29 x_last_instlmnt_due_date IN DATE
30 ) AS
31 /*
32 || Created By : [email protected]
33 || Created On : 29-NOV-2001
34 || Purpose : Initialises the Old and New references for the columns of the table.
35 || Known limitations, enhancements or remarks :
36 || Change History :
37 || Who When What
38 || (reverse chronological order - newest change first)
39 */
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM igs_fi_person_holds
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 OPEN cur_old_ref_values;
53 FETCH cur_old_ref_values INTO old_references;
54 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55 CLOSE cur_old_ref_values;
56 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57 igs_ge_msg_stack.add;
58 app_exception.raise_exception;
59 RETURN;
60 END IF;
61 CLOSE cur_old_ref_values;
62
63 -- Populate New Values.
64 -- Removed balance_amount, Enh Bug: 2562745
65 new_references.person_id := x_person_id;
66 new_references.hold_plan_name := x_hold_plan_name;
67 new_references.hold_type := x_hold_type;
68 new_references.hold_start_dt := x_hold_start_dt;
69 new_references.process_start_dt := x_process_start_dt;
70 new_references.process_end_dt := x_process_end_dt;
71 new_references.offset_days := x_offset_days;
72 new_references.past_due_amount := x_past_due_amount;
73 new_references.fee_cal_type := x_fee_cal_type;
74 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
75 new_references.fee_type_invoice_amount := x_fee_type_invoice_amount;
76 new_references.release_credit_id := x_release_credit_id;
77 new_references.student_plan_id := x_student_plan_id;
78 new_references.last_instlmnt_due_date := x_last_instlmnt_due_date;
79
80 IF (p_action = 'UPDATE') THEN
81 new_references.creation_date := old_references.creation_date;
82 new_references.created_by := old_references.created_by;
83 ELSE
84 new_references.creation_date := x_creation_date;
85 new_references.created_by := x_created_by;
86 END IF;
87
88 new_references.last_update_date := x_last_update_date;
89 new_references.last_updated_by := x_last_updated_by;
90 new_references.last_update_login := x_last_update_login;
91
92 END set_column_values;
93
94
95 PROCEDURE check_parent_existance AS
96 /*
97 || Created By : [email protected]
98 || Created On : 29-NOV-2001
99 || Purpose : Checks for the existance of Parent records.
100 || Known limitations, enhancements or remarks :
101 || Change History :
102 || Who When What
103 || (reverse chronological order - newest change first)
104 || pathipat 11-Aug-2003 Enh 3076768 - Auto Release of Holds
105 || Added check for release_credit_id
106 */
107 BEGIN
108
109 IF (((old_references.person_id = new_references.person_id)) OR
110 ((new_references.person_id IS NULL))) THEN
111 NULL;
112 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
113 new_references.person_id
114 ) THEN
115 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119
120 IF (((old_references.hold_plan_name = new_references.hold_plan_name)) OR
121 ((new_references.hold_plan_name IS NULL))) THEN
122 NULL;
123 ELSIF NOT igs_fi_hold_plan_pkg.get_pk_for_validation (
124 new_references.hold_plan_name
125 ) THEN
126 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
127 igs_ge_msg_stack.add;
128 app_exception.raise_exception;
129 END IF;
130
131 IF (((old_references.hold_type = new_references.hold_type)) OR
132 ((new_references.hold_type IS NULL))) THEN
133 NULL;
134 ELSIF NOT igs_fi_encmb_type_pkg.get_pk_for_validation (
135 new_references.hold_type
136 ) THEN
137 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
138 igs_ge_msg_stack.add;
139 app_exception.raise_exception;
140 END IF;
141
142 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
143 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
144 ((new_references.fee_cal_type IS NULL) OR
145 (new_references.fee_ci_sequence_number IS NULL))) THEN
146 NULL;
147 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
148 new_references.fee_cal_type,
149 new_references.fee_ci_sequence_number
150 ) THEN
151 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
152 igs_ge_msg_stack.add;
153 app_exception.raise_exception;
154 END IF;
155
156 IF (((old_references.release_credit_id = new_references.release_credit_id)) OR
157 ((new_references.release_credit_id IS NULL))) THEN
158 NULL;
159 ELSIF NOT igs_fi_credits_pkg.get_pk_for_validation (
160 new_references.release_credit_id
161 ) THEN
162 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
163 igs_ge_msg_stack.add;
164 app_exception.raise_exception;
165 END IF;
166
167 END check_parent_existance;
168
169
170 FUNCTION get_pk_for_validation (
171 x_person_id IN NUMBER,
172 x_hold_type IN VARCHAR2,
173 x_hold_start_dt IN DATE
174 ) RETURN BOOLEAN AS
175 /*
176 || Created By : [email protected]
177 || Created On : 29-NOV-2001
178 || Purpose : Validates the Primary Key of the table.
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184 CURSOR cur_rowid IS
185 SELECT rowid
186 FROM igs_fi_person_holds
187 WHERE person_id = x_person_id
188 AND hold_type = x_hold_type
189 AND hold_start_dt = x_hold_start_dt
190 FOR UPDATE NOWAIT;
191
192 lv_rowid cur_rowid%RowType;
193
194 BEGIN
195
196 OPEN cur_rowid;
197 FETCH cur_rowid INTO lv_rowid;
198 IF (cur_rowid%FOUND) THEN
199 CLOSE cur_rowid;
200 RETURN(TRUE);
201 ELSE
202 CLOSE cur_rowid;
203 RETURN(FALSE);
204 END IF;
205
206 END get_pk_for_validation;
207
208 PROCEDURE get_fk_igs_fi_hold_plan (
209 x_hold_plan_name IN VARCHAR2
210 ) AS
211 /*
212 || Created By : [email protected]
213 || Created On : 29-NOV-2001
214 || Purpose : Validates the Foreign Keys for the table.
215 || Known limitations, enhancements or remarks :
216 || Change History :
217 || Who When What
218 || (reverse chronological order - newest change first)
219 */
220 CURSOR cur_rowid IS
221 SELECT rowid
222 FROM igs_fi_person_holds
223 WHERE ((hold_plan_name = x_hold_plan_name));
224
225 lv_rowid cur_rowid%RowType;
226
227 BEGIN
228
229 OPEN cur_rowid;
230 FETCH cur_rowid INTO lv_rowid;
231 IF (cur_rowid%FOUND) THEN
232 CLOSE cur_rowid;
233 fnd_message.set_name ('IGS', 'IGS_FI_FPHL_FIHP_FK');
234 igs_ge_msg_stack.add;
235 app_exception.raise_exception;
236 RETURN;
237 END IF;
238 CLOSE cur_rowid;
239
240 END get_fk_igs_fi_hold_plan;
241
242
243 PROCEDURE get_fk_igs_fi_encmb_type (
244 x_encumbrance_type IN VARCHAR2
245 ) AS
246 /*
247 || Created By : [email protected]
248 || Created On : 29-NOV-2001
249 || Purpose : Validates the Foreign Keys for the table.
250 || Known limitations, enhancements or remarks :
251 || Change History :
252 || Who When What
253 || (reverse chronological order - newest change first)
254 */
255 CURSOR cur_rowid IS
256 SELECT rowid
257 FROM igs_fi_person_holds
258 WHERE ((hold_type = x_encumbrance_type));
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 OPEN cur_rowid;
265 FETCH cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 CLOSE cur_rowid;
268 fnd_message.set_name ('IGS', 'IGS_FI_FPHL_ET_FK');
269 igs_ge_msg_stack.add;
270 app_exception.raise_exception;
271 RETURN;
272 END IF;
273 CLOSE cur_rowid;
274
275 END get_fk_igs_fi_encmb_type;
276
277
278 PROCEDURE get_fk_igs_fi_credits_all (
279 x_release_credit_id IN NUMBER
280 ) AS
281 /*
282 || Created By : Priya Athipatla
283 || Created On : 11-Aug-2003
284 || Purpose : Validates the Foreign Keys for the table.
285 || Known limitations, enhancements or remarks :
286 || Change History :
287 || Who When What
288 || (reverse chronological order - newest change first)
289 */
290 CURSOR cur_rowid IS
291 SELECT rowid
292 FROM igs_fi_credits_all
293 WHERE credit_id = x_release_credit_id;
294
295 lv_rowid cur_rowid%ROWTYPE;
296
297 BEGIN
298
299 OPEN cur_rowid;
300 FETCH cur_rowid INTO lv_rowid;
301 IF (cur_rowid%FOUND) THEN
302 CLOSE cur_rowid;
303 fnd_message.set_name ('IGS', 'IGS_FI_FPHL_CRDT_FK');
304 igs_ge_msg_stack.add;
305 app_exception.raise_exception;
306 RETURN;
307 END IF;
308 CLOSE cur_rowid;
309
310 END get_fk_igs_fi_credits_all;
311
312
313 PROCEDURE get_fk_igs_ca_inst (
314 x_cal_type IN VARCHAR2,
315 x_sequence_number IN NUMBER
316 ) AS
317 /*
318 || Created By : [email protected]
319 || Created On : 29-NOV-2001
320 || Purpose : Validates the Foreign Keys for the table.
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || (reverse chronological order - newest change first)
325 */
326 CURSOR cur_rowid IS
327 SELECT rowid
328 FROM igs_fi_person_holds
329 WHERE ((fee_cal_type = x_cal_type) AND
330 (fee_ci_sequence_number = x_sequence_number));
331
332 lv_rowid cur_rowid%RowType;
333
334 BEGIN
335
336 OPEN cur_rowid;
337 FETCH cur_rowid INTO lv_rowid;
338 IF (cur_rowid%FOUND) THEN
339 CLOSE cur_rowid;
340 fnd_message.set_name ('IGS', 'IGS_FI_FPHL_CI_FK');
341 igs_ge_msg_stack.add;
342 app_exception.raise_exception;
343 RETURN;
344 END IF;
345 CLOSE cur_rowid;
346
347 END get_fk_igs_ca_inst;
348
349 PROCEDURE BeforeInsertUpdate(p_inserting BOOLEAN , p_updating BOOLEAN) AS
350 p_message_name VARCHAR2(30);
351 BEGIN
352 IF ( p_inserting = TRUE OR (p_updating = TRUE AND new_references.hold_type <> old_references.hold_type) ) THEN
353 IF NOT igs_en_val_etde.enrp_val_et_closed(new_references.hold_type,p_message_name) THEN
354 Fnd_Message.Set_Name('IGS', p_message_name);
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358 END IF;
359 END BeforeInsertUpdate;
360
361 PROCEDURE before_dml (
362 p_action IN VARCHAR2,
363 x_rowid IN VARCHAR2,
364 x_person_id IN NUMBER ,
365 x_hold_plan_name IN VARCHAR2,
366 x_hold_type IN VARCHAR2,
367 x_hold_start_dt IN DATE ,
368 x_process_start_dt IN DATE ,
369 x_process_end_dt IN DATE ,
370 x_offset_days IN NUMBER ,
371 x_past_due_amount IN NUMBER ,
372 x_fee_cal_type IN VARCHAR2,
373 x_fee_ci_sequence_number IN NUMBER ,
374 x_fee_type_invoice_amount IN NUMBER ,
375 x_creation_date IN DATE ,
376 x_created_by IN NUMBER ,
377 x_last_update_date IN DATE ,
378 x_last_updated_by IN NUMBER ,
379 x_last_update_login IN NUMBER ,
380 x_release_credit_id IN NUMBER ,
381 x_student_plan_id IN NUMBER ,
382 x_last_instlmnt_due_date IN DATE
383 ) AS
384 /*
385 || Created By : [email protected]
386 || Created On : 29-NOV-2001
387 || Purpose : Initialises the columns, Checks Constraints, Calls the
388 || Trigger Handlers for the table, before any DML operation.
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || (reverse chronological order - newest change first)
393 || vvutukur 17-May-2002 Removed calls to check_constraints procedure
394 || as this procedure is removed from body and
395 || spec.also. BUG#2344826.
396 */
397 BEGIN
398
399 set_column_values (
400 p_action,
401 x_rowid,
402 x_person_id,
403 x_hold_plan_name,
404 x_hold_type,
405 x_hold_start_dt,
406 x_process_start_dt,
407 x_process_end_dt,
408 x_offset_days,
409 x_past_due_amount,
410 x_fee_cal_type,
411 x_fee_ci_sequence_number,
412 x_fee_type_invoice_amount,
413 x_creation_date,
414 x_created_by,
415 x_last_update_date,
416 x_last_updated_by,
417 x_last_update_login,
418 x_release_credit_id,
419 x_student_plan_id,
420 x_last_instlmnt_due_date
421 );
422
423 IF (p_action = 'INSERT') THEN
424 BeforeInsertUpdate(TRUE,FALSE);
425 -- Call all the procedures related to Before Insert.
426 IF ( get_pk_for_validation(
427 new_references.person_id,
428 new_references.hold_type,
429 new_references.hold_start_dt
430 )
431 ) THEN
432 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
433 igs_ge_msg_stack.add;
434 app_exception.raise_exception;
435 END IF;
436 check_parent_existance;
437 ELSIF (p_action = 'UPDATE') THEN
438 BeforeInsertUpdate(FALSE,TRUE);
439 -- Call all the procedures related to Before Update.
440 check_parent_existance;
441 ELSIF (p_action = 'VALIDATE_INSERT') THEN
442 -- Call all the procedures related to Before Insert.
443 IF ( get_pk_for_validation (
444 new_references.person_id,
445 new_references.hold_type,
446 new_references.hold_start_dt
447 )
448 ) THEN
449 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
450 igs_ge_msg_stack.add;
451 app_exception.raise_exception;
452 END IF;
453 END IF;
454
455 END before_dml;
456
457
458 PROCEDURE insert_row (
459 x_rowid IN OUT NOCOPY VARCHAR2,
460 x_person_id IN NUMBER,
461 x_hold_plan_name IN VARCHAR2,
462 x_hold_type IN VARCHAR2,
463 x_hold_start_dt IN DATE,
464 x_process_start_dt IN DATE,
465 x_process_end_dt IN DATE,
466 x_offset_days IN NUMBER,
467 x_past_due_amount IN NUMBER,
468 x_fee_cal_type IN VARCHAR2,
469 x_fee_ci_sequence_number IN NUMBER,
470 x_fee_type_invoice_amount IN NUMBER,
471 x_mode IN VARCHAR2,
472 x_release_credit_id IN NUMBER,
473 x_student_plan_id IN NUMBER,
474 x_last_instlmnt_due_date IN DATE
475 ) AS
476 /*
477 || Created By : [email protected]
478 || Created On : 29-NOV-2001
479 || Purpose : Handles the INSERT DML logic for the table.
480 || Known limitations, enhancements or remarks :
481 || Change History :
482 || Who When What
483 || (reverse chronological order - newest change first)
484 */
485 CURSOR c IS
486 SELECT rowid
487 FROM igs_fi_person_holds
488 WHERE person_id = x_person_id
489 AND hold_type = x_hold_type
490 AND hold_start_dt = x_hold_start_dt;
491
492 x_last_update_date DATE;
493 x_last_updated_by NUMBER;
494 x_last_update_login NUMBER;
495 x_request_id NUMBER;
496 x_program_id NUMBER;
497 x_program_application_id NUMBER;
498 x_program_update_date DATE;
499
500 BEGIN
501
502 x_last_update_date := SYSDATE;
503 IF (x_mode = 'I') THEN
504 x_last_updated_by := 1;
505 x_last_update_login := 0;
506 ELSIF (x_mode = 'R') THEN
507 x_last_updated_by := fnd_global.user_id;
508 IF (x_last_updated_by IS NULL) THEN
509 x_last_updated_by := -1;
510 END IF;
511 x_last_update_login := fnd_global.login_id;
512 IF (x_last_update_login IS NULL) THEN
513 x_last_update_login := -1;
514 END IF;
515 x_request_id := fnd_global.conc_request_id;
516 x_program_id := fnd_global.conc_program_id;
517 x_program_application_id := fnd_global.prog_appl_id;
518
519 IF (x_request_id = -1) THEN
520 x_request_id := NULL;
521 x_program_id := NULL;
522 x_program_application_id := NULL;
523 x_program_update_date := NULL;
524 ELSE
525 x_program_update_date := SYSDATE;
526 END IF;
527 ELSE
528 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
529 igs_ge_msg_stack.add;
530 app_exception.raise_exception;
531 END IF;
532
533 before_dml(
534 p_action => 'INSERT',
535 x_rowid => x_rowid,
536 x_person_id => x_person_id,
537 x_hold_plan_name => x_hold_plan_name,
538 x_hold_type => x_hold_type,
539 x_hold_start_dt => x_hold_start_dt,
540 x_process_start_dt => x_process_start_dt,
541 x_process_end_dt => x_process_end_dt,
542 x_offset_days => x_offset_days,
543 x_past_due_amount => x_past_due_amount,
544 x_fee_cal_type => x_fee_cal_type,
545 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
546 x_fee_type_invoice_amount => x_fee_type_invoice_amount,
547 x_creation_date => x_last_update_date,
548 x_created_by => x_last_updated_by,
549 x_last_update_date => x_last_update_date,
550 x_last_updated_by => x_last_updated_by,
551 x_last_update_login => x_last_update_login,
552 x_release_credit_id => x_release_credit_id,
553 x_student_plan_id => x_student_plan_id,
554 x_last_instlmnt_due_date => x_last_instlmnt_due_date
555 );
556
557 INSERT INTO igs_fi_person_holds (
558 person_id,
559 hold_plan_name,
560 hold_type,
561 hold_start_dt,
562 process_start_dt,
563 process_end_dt,
564 offset_days,
565 past_due_amount,
566 fee_cal_type,
567 fee_ci_sequence_number,
568 fee_type_invoice_amount,
569 creation_date,
570 created_by,
571 last_update_date,
572 last_updated_by,
573 last_update_login,
574 request_id,
575 program_id,
576 program_application_id,
577 program_update_date,
578 release_credit_id,
579 student_plan_id ,
580 last_instlmnt_due_date
581 ) VALUES (
582 new_references.person_id,
583 new_references.hold_plan_name,
584 new_references.hold_type,
585 new_references.hold_start_dt,
586 new_references.process_start_dt,
587 new_references.process_end_dt,
588 new_references.offset_days,
589 new_references.past_due_amount,
590 new_references.fee_cal_type,
591 new_references.fee_ci_sequence_number,
592 new_references.fee_type_invoice_amount,
593 x_last_update_date,
594 x_last_updated_by,
595 x_last_update_date,
596 x_last_updated_by,
597 x_last_update_login ,
598 x_request_id,
599 x_program_id,
600 x_program_application_id,
601 x_program_update_date,
602 new_references.release_credit_id,
603 new_references.student_plan_id ,
604 new_references.last_instlmnt_due_date
605 );
606
607 OPEN c;
608 FETCH c INTO x_rowid;
609 IF (c%NOTFOUND) THEN
610 CLOSE c;
611 RAISE NO_DATA_FOUND;
612 END IF;
613 CLOSE c;
614
615 END insert_row;
616
617
618 PROCEDURE lock_row (
619 x_rowid IN VARCHAR2,
620 x_person_id IN NUMBER,
621 x_hold_plan_name IN VARCHAR2,
622 x_hold_type IN VARCHAR2,
623 x_hold_start_dt IN DATE,
624 x_process_start_dt IN DATE,
625 x_process_end_dt IN DATE,
626 x_offset_days IN NUMBER,
627 x_past_due_amount IN NUMBER,
628 x_fee_cal_type IN VARCHAR2,
629 x_fee_ci_sequence_number IN NUMBER,
630 x_fee_type_invoice_amount IN NUMBER,
631 x_release_credit_id IN NUMBER,
632 x_student_plan_id IN NUMBER,
633 x_last_instlmnt_due_date IN DATE
634 ) AS
635 /*
636 || Created By : [email protected]
637 || Created On : 29-NOV-2001
638 || Purpose : Handles the LOCK mechanism for the table.
639 || Known limitations, enhancements or remarks :
640 || Change History :
641 || Who When What
642 || (reverse chronological order - newest change first)
643 */
644 CURSOR c1 IS
645 SELECT
646 hold_plan_name,
647 process_start_dt,
648 process_end_dt,
649 offset_days,
650 past_due_amount,
651 fee_cal_type,
652 fee_ci_sequence_number,
653 fee_type_invoice_amount,
654 release_credit_id,
655 student_plan_id ,
656 last_instlmnt_due_date
657 FROM igs_fi_person_holds
658 WHERE rowid = x_rowid
659 FOR UPDATE NOWAIT;
660
661 tlinfo c1%ROWTYPE;
662
663 BEGIN
664
665 OPEN c1;
666 FETCH c1 INTO tlinfo;
667 IF (c1%notfound) THEN
668 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
669 igs_ge_msg_stack.add;
670 CLOSE c1;
671 app_exception.raise_exception;
672 RETURN;
673 END IF;
674 CLOSE c1;
675
676 IF (
677 (tlinfo.hold_plan_name = x_hold_plan_name)
678 AND (tlinfo.process_start_dt = x_process_start_dt)
679 AND (tlinfo.process_end_dt = x_process_end_dt)
680 AND ((tlinfo.offset_days = x_offset_days) OR ((tlinfo.offset_days IS NULL) AND (X_offset_days IS NULL)))
681 AND (tlinfo.past_due_amount = x_past_due_amount)
682 AND ((tlinfo.fee_cal_type = x_fee_cal_type) OR ((tlinfo.fee_cal_type IS NULL) AND (X_fee_cal_type IS NULL)))
683 AND ((tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number) OR ((tlinfo.fee_ci_sequence_number IS NULL) AND (X_fee_ci_sequence_number IS NULL)))
684 AND ((tlinfo.fee_type_invoice_amount = x_fee_type_invoice_amount) OR ((tlinfo.fee_type_invoice_amount IS NULL) AND (X_fee_type_invoice_amount IS NULL)))
685 AND ((tlinfo.release_credit_id = x_release_credit_id) OR ((tlinfo.release_credit_id IS NULL) AND (x_release_credit_id IS NULL)))
686 AND ((tlinfo.student_plan_id = x_student_plan_id) OR ((tlinfo.student_plan_id IS NULL) AND (x_student_plan_id IS NULL)))
687 AND ((tlinfo.last_instlmnt_due_date = x_last_instlmnt_due_date) OR ((tlinfo.last_instlmnt_due_date IS NULL) AND (x_last_instlmnt_due_date IS NULL)))
688 ) THEN
689 NULL;
690 ELSE
691 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
692 igs_ge_msg_stack.add;
693 app_exception.raise_exception;
694 END IF;
695
696 RETURN;
697
698 END lock_row;
699
700
701 PROCEDURE update_row (
702 x_rowid IN VARCHAR2,
703 x_person_id IN NUMBER,
704 x_hold_plan_name IN VARCHAR2,
705 x_hold_type IN VARCHAR2,
706 x_hold_start_dt IN DATE,
707 x_process_start_dt IN DATE,
708 x_process_end_dt IN DATE,
709 x_offset_days IN NUMBER,
710 x_past_due_amount IN NUMBER,
711 x_fee_cal_type IN VARCHAR2,
712 x_fee_ci_sequence_number IN NUMBER,
713 x_fee_type_invoice_amount IN NUMBER,
714 x_mode IN VARCHAR2,
715 x_release_credit_id IN NUMBER,
716 x_student_plan_id IN NUMBER,
717 x_last_instlmnt_due_date IN DATE
718 ) AS
719 /*
720 || Created By : [email protected]
721 || Created On : 29-NOV-2001
722 || Purpose : Handles the UPDATE DML logic for the table.
723 || Known limitations, enhancements or remarks :
724 || Change History :
725 || Who When What
726 || (reverse chronological order - newest change first)
727 */
728 x_last_update_date DATE ;
729 x_last_updated_by NUMBER;
730 x_last_update_login NUMBER;
731 x_request_id NUMBER;
732 x_program_id NUMBER;
733 x_program_application_id NUMBER;
734 x_program_update_date DATE;
735
736 BEGIN
737
738 x_last_update_date := SYSDATE;
739 IF (X_MODE = 'I') THEN
740 x_last_updated_by := 1;
741 x_last_update_login := 0;
742 ELSIF (x_mode = 'R') THEN
743 x_last_updated_by := fnd_global.user_id;
744 IF x_last_updated_by IS NULL THEN
745 x_last_updated_by := -1;
746 END IF;
747 x_last_update_login := fnd_global.login_id;
748 IF (x_last_update_login IS NULL) THEN
749 x_last_update_login := -1;
750 END IF;
751 ELSE
752 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
753 igs_ge_msg_stack.add;
754 app_exception.raise_exception;
755 END IF;
756
757 before_dml(
758 p_action => 'UPDATE',
759 x_rowid => x_rowid,
760 x_person_id => x_person_id,
761 x_hold_plan_name => x_hold_plan_name,
762 x_hold_type => x_hold_type,
763 x_hold_start_dt => x_hold_start_dt,
764 x_process_start_dt => x_process_start_dt,
765 x_process_end_dt => x_process_end_dt,
766 x_offset_days => x_offset_days,
767 x_past_due_amount => x_past_due_amount,
768 x_fee_cal_type => x_fee_cal_type,
769 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
770 x_fee_type_invoice_amount => x_fee_type_invoice_amount,
771 x_creation_date => x_last_update_date,
772 x_created_by => x_last_updated_by,
773 x_last_update_date => x_last_update_date,
774 x_last_updated_by => x_last_updated_by,
775 x_last_update_login => x_last_update_login,
776 x_release_credit_id => x_release_credit_id,
777 x_student_plan_id => x_student_plan_id,
778 x_last_instlmnt_due_date => x_last_instlmnt_due_date
779 );
780
781 IF (x_mode = 'R') THEN
782 x_request_id := fnd_global.conc_request_id;
783 x_program_id := fnd_global.conc_program_id;
784 x_program_application_id := fnd_global.prog_appl_id;
785 IF (x_request_id = -1) THEN
786 x_request_id := old_references.request_id;
787 x_program_id := old_references.program_id;
788 x_program_application_id := old_references.program_application_id;
789 x_program_update_date := old_references.program_update_date;
790 ELSE
791 x_program_update_date := SYSDATE;
792 END IF;
793 END IF;
794
795 UPDATE igs_fi_person_holds
796 SET
797 hold_plan_name = new_references.hold_plan_name,
798 process_start_dt = new_references.process_start_dt,
799 process_end_dt = new_references.process_end_dt,
800 offset_days = new_references.offset_days,
801 past_due_amount = new_references.past_due_amount,
802 fee_cal_type = new_references.fee_cal_type,
803 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
804 fee_type_invoice_amount = new_references.fee_type_invoice_amount,
805 last_update_date = x_last_update_date,
806 last_updated_by = x_last_updated_by,
807 last_update_login = x_last_update_login ,
808 request_id = x_request_id,
809 program_id = x_program_id,
810 program_application_id = x_program_application_id,
811 program_update_date = x_program_update_date,
812 release_credit_id = new_references.release_credit_id,
813 student_plan_id = new_references.student_plan_id,
814 last_instlmnt_due_date = new_references.last_instlmnt_due_date
815 WHERE rowid = x_rowid;
816
817 IF (SQL%NOTFOUND) THEN
818 RAISE NO_DATA_FOUND;
819 END IF;
820
821 END update_row;
822
823
824 PROCEDURE add_row (
825 x_rowid IN OUT NOCOPY VARCHAR2,
826 x_person_id IN NUMBER,
827 x_hold_plan_name IN VARCHAR2,
828 x_hold_type IN VARCHAR2,
829 x_hold_start_dt IN DATE,
830 x_process_start_dt IN DATE,
831 x_process_end_dt IN DATE,
832 x_offset_days IN NUMBER,
833 x_past_due_amount IN NUMBER,
834 x_fee_cal_type IN VARCHAR2,
835 x_fee_ci_sequence_number IN NUMBER,
836 x_fee_type_invoice_amount IN NUMBER,
837 x_mode IN VARCHAR2,
838 x_release_credit_id IN NUMBER,
839 x_student_plan_id IN NUMBER,
840 x_last_instlmnt_due_date IN DATE
841 ) AS
842 /*
843 || Created By : [email protected]
844 || Created On : 29-NOV-2001
845 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
846 || Known limitations, enhancements or remarks :
847 || Change History :
848 || Who When What
849 || (reverse chronological order - newest change first)
850 */
851 CURSOR c1 IS
852 SELECT rowid
853 FROM igs_fi_person_holds
854 WHERE person_id = x_person_id
855 AND hold_type = x_hold_type
856 AND hold_start_dt = x_hold_start_dt;
857
858 BEGIN
859
860 OPEN c1;
861 FETCH c1 INTO x_rowid;
862 IF (c1%NOTFOUND) THEN
863 CLOSE c1;
864
865 insert_row (
866 x_rowid,
867 x_person_id,
868 x_hold_plan_name,
869 x_hold_type,
870 x_hold_start_dt,
871 x_process_start_dt,
872 x_process_end_dt,
873 x_offset_days,
874 x_past_due_amount,
875 x_fee_cal_type,
876 x_fee_ci_sequence_number,
877 x_fee_type_invoice_amount,
878 x_mode,
879 x_release_credit_id,
880 x_student_plan_id ,
881 x_last_instlmnt_due_date
882 );
883 RETURN;
884 END IF;
885 CLOSE c1;
886
887 update_row (
888 x_rowid,
889 x_person_id,
890 x_hold_plan_name,
891 x_hold_type,
892 x_hold_start_dt,
893 x_process_start_dt,
894 x_process_end_dt,
895 x_offset_days,
896 x_past_due_amount,
897 x_fee_cal_type,
898 x_fee_ci_sequence_number,
899 x_fee_type_invoice_amount,
900 x_mode,
901 x_release_credit_id,
902 x_student_plan_id ,
903 x_last_instlmnt_due_date
904 );
905
906 END add_row;
907
908
909 PROCEDURE delete_row (
910 x_rowid IN VARCHAR2
911 ) AS
912 /*
913 || Created By : [email protected]
914 || Created On : 29-NOV-2001
915 || Purpose : Handles the DELETE DML logic for the table.
916 || Known limitations, enhancements or remarks :
917 || Change History :
918 || Who When What
919 || (reverse chronological order - newest change first)
920 */
921 BEGIN
922
923 before_dml (
924 p_action => 'DELETE',
925 x_rowid => x_rowid
926 );
927
928 DELETE FROM igs_fi_person_holds
929 WHERE rowid = x_rowid;
930
931 IF (SQL%NOTFOUND) THEN
932 RAISE NO_DATA_FOUND;
933 END IF;
934
935 END delete_row;
936
937
938 END igs_fi_person_holds_pkg;