1 PACKAGE BODY igf_db_disb_holds_pkg AS
2 /* $Header: IGFDI09B.pls 120.1 2006/08/10 15:42:17 museshad noship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGF_DB_DISB_HOLDS_PKG
11 | |
12 | NOTES |
13 | |
14 | This package has a flag on the end of some of the procedures called |
15 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
16 | This will control how the who columns are filled in; If you are |
17 | running in runtime mode, they are taken from the profiles, whereas in |
18 | install-time mode they get defaulted with special values to indicate |
19 | that they were inserted by datamerge. |
20 | |
21 | The ADD_ROW routine will see whether a row exists by selecting |
22 | based on the primary key, and updates the row if it exists, |
23 | or inserts the row if it doesn't already exist. |
24 | |
25 | This module is called by AutoInstall (afplss.drv) on install and |
26 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
27 | |
28 | HISTORY |
29 | museshad 10-Aug-2006 5337555. Build FA 163.TBH Impact changes|
30 | veramach July 2004 FA 151 HR integration (bug # 3709292) |
31 | Impact of obsoleting columns from |
32 | igf_aw_awd_disb_pkg |
33 | Bug No :- 2154941 |
34 | Desc :- Disbursement and Sponsership Build for Jul 2002 FACCR004 |
35 | WHO WHEN WHAT
36
37 --
38 -- Bug ID 2544864
39 -- sjadhav Oct.07.2002 Gscc fix of removing the Default Keyword
40 --
41
42 --
43 -- Bug 2255279
44 -- sjadhav, set elig_status = 'O' [ OVERAWARD ]
45 -- and elig_status_date = systdate in case of a overaward hold
46 --
47
48 | mesriniv 31-JAN-2002 Made the call to check uniqueness in
49 | update only when the new and old values are diff
50 | mesriniv 8-JAN-2002 Created this Table Handler
51 | Added a procedure check_uniqueness for Business
52 | whenever record is inserted or updated
53 | from form or package |
54 *=======================================================================*/
55
56 l_rowid VARCHAR2(25);
57 old_references igf_db_disb_holds_all%ROWTYPE;
58 new_references igf_db_disb_holds_all%ROWTYPE;
59
60 PROCEDURE set_column_values (
61 p_action IN VARCHAR2,
62 x_rowid IN VARCHAR2,
63 x_hold_id IN NUMBER ,
64 x_award_id IN NUMBER ,
65 x_disb_num IN NUMBER ,
66 x_hold IN VARCHAR2,
67 x_hold_date IN VARCHAR2,
68 x_hold_type IN VARCHAR2,
69 x_release_date IN DATE ,
70 x_release_flag IN VARCHAR2,
71 x_release_reason IN VARCHAR2,
72 x_creation_date IN DATE ,
73 x_created_by IN NUMBER ,
74 x_last_update_date IN DATE ,
75 x_last_updated_by IN NUMBER ,
76 x_last_update_login IN NUMBER
77 ) AS
78 /*
79 || Created By : mesriniv
80 || Created On : 08-JAN-2002
81 || Purpose : Initialises the Old and New references for the columns of the table.
82 || Known limitations, enhancements or remarks :
83 || Change History :
84 || Who When What
85 || (reverse chronological order - newest change first)
86 */
87
88 CURSOR cur_old_ref_values IS
89 SELECT *
90 FROM igf_db_disb_holds_all
91 WHERE rowid = x_rowid;
92
93 BEGIN
94
95 l_rowid := x_rowid;
96
97 -- Code for setting the Old and New Reference Values.
98 -- Populate Old Values.
99 OPEN cur_old_ref_values;
100 FETCH cur_old_ref_values INTO old_references;
101 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
102 CLOSE cur_old_ref_values;
103 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
104 igs_ge_msg_stack.add;
105 app_exception.raise_exception;
106 RETURN;
107 END IF;
108 CLOSE cur_old_ref_values;
109
110 -- Populate New Values.
111 new_references.hold_id := x_hold_id;
112 new_references.award_id := x_award_id;
113 new_references.disb_num := x_disb_num;
114 new_references.hold := x_hold;
115 new_references.hold_date := x_hold_date;
116 new_references.hold_type := x_hold_type;
117 new_references.release_date := x_release_date;
118 new_references.release_flag := x_release_flag;
119 new_references.release_reason := x_release_reason;
120
121 IF (p_action = 'UPDATE') THEN
122 new_references.creation_date := old_references.creation_date;
123 new_references.created_by := old_references.created_by;
124 ELSE
125 new_references.creation_date := x_creation_date;
126 new_references.created_by := x_created_by;
127 END IF;
128
129 new_references.last_update_date := x_last_update_date;
130 new_references.last_updated_by := x_last_updated_by;
131 new_references.last_update_login := x_last_update_login;
132
133 END set_column_values;
134
135 PROCEDURE check_uniqueness(x_award_id NUMBER ,x_disb_num NUMBER,x_hold VARCHAR2) AS
136 /*
137 || Created By : mesriniv
138 || Created On : 05-JAN-2002
139 || Purpose : Handles the Unique Constraint logic.Please note that
140 || this table does not have unique constraints defined
141 || but a specific validation has been added
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147
148
149 --Cursor to find if there is already a Hold existing of the same as the one being inserted
150 --and for which the release flg is N
151 CURSOR cur_get_hold IS
152 SELECT COUNT(HOLD_ID)
153 FROM igf_db_disb_holds
154 WHERE award_id = x_award_id
155 AND disb_num = x_disb_num
156 AND hold = x_hold
157 AND release_flag ='N'
158 AND ROWNUM <= 1;
159
160 l_count NUMBER(1);
161
162 BEGIN
163
164
165
166 l_count:=0;
167 --Fetch the count of the DIsbursment Hold
168 OPEN cur_get_hold;
169 FETCH cur_get_hold INTO l_count;
170 CLOSE cur_get_hold;
171
172
173 --Even if one Hold of same kind exists then we need to stop from Inserting
174 --a duplicate one
175 IF (NVL(l_count,0) = 1) THEN
176
177 fnd_message.set_name('IGF','IGF_DB_HOLD_EXISTS');
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END IF;
181
182
183 END check_uniqueness;
184
185
186
187 PROCEDURE check_parent_existance AS
188 /*
189 || Created By : mesriniv
190 || Created On : 08-JAN-2002
191 || Purpose : Checks for the existance of Parent records.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 BEGIN
198
199 IF (((old_references.award_id = new_references.award_id) AND
200 (old_references.disb_num = new_references.disb_num)) OR
201 ((new_references.award_id IS NULL) OR
202 (new_references.disb_num IS NULL))) THEN
203 NULL;
204 ELSIF NOT igf_aw_awd_disb_pkg.get_pk_for_validation (
205 new_references.award_id,
206 new_references.disb_num
207 ) THEN
208 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
209 igs_ge_msg_stack.add;
210 app_exception.raise_exception;
211 END IF;
212
213 END check_parent_existance;
214
215
216 FUNCTION get_pk_for_validation (
217 x_hold_id IN NUMBER
218 ) RETURN BOOLEAN AS
219 /*
220 || Created By : mesriniv
221 || Created On : 08-JAN-2002
222 || Purpose : Validates the Primary Key of the table.
223 || Known limitations, enhancements or remarks :
224 || Change History :
225 || Who When What
226 || (reverse chronological order - newest change first)
227 */
228 CURSOR cur_rowid IS
229 SELECT rowid
230 FROM igf_db_disb_holds_all
231 WHERE hold_id = x_hold_id
232 FOR UPDATE NOWAIT;
233
234 lv_rowid cur_rowid%RowType;
235
236 BEGIN
237
238 OPEN cur_rowid;
239 FETCH cur_rowid INTO lv_rowid;
240 IF (cur_rowid%FOUND) THEN
241 CLOSE cur_rowid;
242 RETURN(TRUE);
243 ELSE
244 CLOSE cur_rowid;
245 RETURN(FALSE);
246 END IF;
247
248 END get_pk_for_validation;
249
250
251 PROCEDURE get_fk_igf_aw_awd_disb (
252 x_award_id IN NUMBER,
253 x_disb_num IN NUMBER
254 ) AS
255 /*
256 || Created By : mesriniv
257 || Created On : 08-JAN-2002
258 || Purpose : Validates the Foreign Keys for the table.
259 || Known limitations, enhancements or remarks :
260 || Change History :
261 || Who When What
262 || (reverse chronological order - newest change first)
263 */
264 CURSOR cur_rowid IS
265 SELECT rowid
266 FROM igf_db_disb_holds_all
267 WHERE ((award_id = x_award_id) AND
268 (disb_num = x_disb_num));
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 OPEN cur_rowid;
275 FETCH cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 CLOSE cur_rowid;
278 fnd_message.set_name ('IGF', 'IGF_DB_HOLD_ADISB_FK');
279 igs_ge_msg_stack.add;
280 app_exception.raise_exception;
281 RETURN;
282 END IF;
283 CLOSE cur_rowid;
284
285 END get_fk_igf_aw_awd_disb;
286
287
288 PROCEDURE before_dml (
289 p_action IN VARCHAR2,
290 x_rowid IN VARCHAR2,
291 x_hold_id IN NUMBER ,
292 x_award_id IN NUMBER ,
293 x_disb_num IN NUMBER ,
294 x_hold IN VARCHAR2,
295 x_hold_date IN VARCHAR2,
296 x_hold_type IN VARCHAR2,
297 x_release_date IN DATE ,
298 x_release_flag IN VARCHAR2,
299 x_release_reason IN VARCHAR2,
300 x_creation_date IN DATE ,
301 x_created_by IN NUMBER ,
302 x_last_update_date IN DATE ,
303 x_last_updated_by IN NUMBER ,
304 x_last_update_login IN NUMBER
305 ) AS
306 /*
307 || Created By : mesriniv
308 || Created On : 08-JAN-2002
309 || Purpose : Initialises the columns, Checks Constraints, Calls the
310 || Trigger Handlers for the table, before any DML operation.
311 || Known limitations, enhancements or remarks :
312 || Change History :
313 || Who When What
314 || (reverse chronological order - newest change first)
315 */
316 BEGIN
317
318 set_column_values (
319 p_action,
320 x_rowid,
321 x_hold_id,
322 x_award_id,
323 x_disb_num,
324 x_hold,
325 x_hold_date,
326 x_hold_type,
327 x_release_date,
328 x_release_flag,
329 x_release_reason,
330 x_creation_date,
331 x_created_by,
332 x_last_update_date,
333 x_last_updated_by,
334 x_last_update_login
335 );
336
337 IF (p_action = 'INSERT') THEN
338 -- Call all the procedures related to Before Insert.
339 IF ( get_pk_for_validation(
340 new_references.hold_id
341 )
342 ) THEN
343 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
344 igs_ge_msg_stack.add;
345 app_exception.raise_exception;
346 END IF;
347 --Call this Check Uniqueness only for a release flag of N
348 IF x_release_flag='N' THEN
349 check_uniqueness(x_award_id,x_disb_num,x_hold);
350 END IF;
351 check_parent_existance;
352 ELSIF (p_action = 'UPDATE') THEN
353 -- Call all the procedures related to Before Update.
354 --Call this Check Uniqueness only for a release flag of N
355 IF x_release_flag='N' AND new_references.hold <> old_references.hold THEN
356 check_uniqueness(x_award_id,x_disb_num,x_hold);
357 END IF;
358 check_parent_existance;
359 ELSIF (p_action = 'VALIDATE_INSERT') THEN
360 -- Call all the procedures related to Before Insert.
361 IF ( get_pk_for_validation (
362 new_references.hold_id
363 )
364 ) THEN
365 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
366 igs_ge_msg_stack.add;
367 app_exception.raise_exception;
368 END IF;
369 --Call this Check Uniqueness only for a release flag of N
370 IF x_release_flag='N' THEN
371 check_uniqueness(x_award_id,x_disb_num,x_hold);
372 END IF;
373
374 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
375 --Call this Check Uniqueness only for a release flag of N
376 IF x_release_flag='N' AND new_references.hold <> old_references.hold THEN
377 check_uniqueness(x_award_id,x_disb_num,x_hold);
378 END IF;
379 END IF;
380
381 END before_dml;
382
383
384 PROCEDURE insert_row (
385 x_rowid IN OUT NOCOPY VARCHAR2,
386 x_hold_id IN OUT NOCOPY NUMBER,
387 x_award_id IN NUMBER,
388 x_disb_num IN NUMBER,
389 x_hold IN VARCHAR2,
390 x_hold_date IN VARCHAR2,
391 x_hold_type IN VARCHAR2,
392 x_release_date IN DATE,
393 x_release_flag IN VARCHAR2,
394 x_release_reason IN VARCHAR2,
395 x_mode IN VARCHAR2
396 ) AS
397 /*
398 || Created By : mesriniv
399 || Created On : 08-JAN-2002
400 || Purpose : Handles the INSERT DML logic for the table.
401 || Known limitations, enhancements or remarks :
402 || Change History :
403 || Who When What
404 || (reverse chronological order - newest change first)
405 */
406 CURSOR c IS
407 SELECT rowid
408 FROM igf_db_disb_holds_all
409 WHERE hold_id = x_hold_id;
410
411 --Cursor to get the Disbursment to Update the Manual Hold Ind
412 CURSOR cur_get_manualHold IS
413 SELECT *
414 FROM igf_aw_awd_disb
415 WHERE award_id =x_award_id
416 AND disb_num =x_disb_num
417 FOR UPDATE OF manual_hold_ind NOWAIT;
418
419
420
421 x_last_update_date DATE;
422 x_last_updated_by NUMBER;
423 x_last_update_login NUMBER;
424 l_disb_rec igf_aw_awd_disb%ROWTYPE;
425
426 BEGIN
427
428
429 x_last_update_date := SYSDATE;
430 IF (x_mode = 'I') THEN
431 x_last_updated_by := 1;
432 x_last_update_login := 0;
433 ELSIF (x_mode = 'R') THEN
434 x_last_updated_by := fnd_global.user_id;
435 IF (x_last_updated_by IS NULL) THEN
436 x_last_updated_by := -1;
437 END IF;
438 x_last_update_login := fnd_global.login_id;
439 IF (x_last_update_login IS NULL) THEN
440 x_last_update_login := -1;
441 END IF;
442 ELSE
443 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
444 igs_ge_msg_stack.add;
445 app_exception.raise_exception;
446 END IF;
447
448 SELECT igf_db_disb_holds_s.NEXTVAL
449 INTO x_hold_id
450 FROM dual;
451
452 new_references.org_id := igs_ge_gen_003.get_org_id;
453
454
455
456 before_dml(
457 p_action => 'INSERT',
458 x_rowid => x_rowid,
459 x_hold_id => x_hold_id,
460 x_award_id => x_award_id,
461 x_disb_num => x_disb_num,
462 x_hold => x_hold,
463 x_hold_date => x_hold_date,
464 x_hold_type => x_hold_type,
465 x_release_date => x_release_date,
466 x_release_flag => x_release_flag,
467 x_release_reason => x_release_reason,
468 x_creation_date => x_last_update_date,
469 x_created_by => x_last_updated_by,
470 x_last_update_date => x_last_update_date,
471 x_last_updated_by => x_last_updated_by,
472 x_last_update_login => x_last_update_login
473 );
474
475 INSERT INTO igf_db_disb_holds_all (
476 hold_id,
477 award_id,
478 disb_num,
479 hold,
480 hold_date,
481 hold_type,
482 release_date,
483 release_flag,
484 release_reason,
485 org_id,
486 creation_date,
487 created_by,
488 last_update_date,
489 last_updated_by,
490 last_update_login
491 ) VALUES (
492 new_references.hold_id,
493 new_references.award_id,
494 new_references.disb_num,
495 new_references.hold,
496 new_references.hold_date,
497 new_references.hold_type,
498 new_references.release_date,
499 new_references.release_flag,
500 new_references.release_reason,
501 new_references.org_id,
502 x_last_update_date,
503 x_last_updated_by,
504 x_last_update_date,
505 x_last_updated_by,
506 x_last_update_login
507 );
508
509 OPEN c;
510 FETCH c INTO x_rowid;
511 IF (c%NOTFOUND) THEN
512 CLOSE c;
513 RAISE NO_DATA_FOUND;
514 END IF;
515 CLOSE c;
516
517 --Update the Manual Hold Indicator as 'Y' anytime a New MANUAL Hold is Inserted by the User
518 --Through the FORM IGFDB002.fmx
519 l_disb_rec :=NULL;
520 IF new_references.hold_type in ('MANUAL','SYSTEM') THEN
521
522 --Fetch the Cursor Record
523 OPEN cur_get_ManualHold;
524 FETCH cur_get_ManualHold INTO l_disb_rec;
525 CLOSE cur_get_ManualHold;
526
527 IF new_references.hold = 'OVERAWARD' THEN
528 l_disb_rec.elig_status := 'O';
529 l_disb_rec.elig_status_date := TRUNC(SYSDATE);
530 END IF;
531
532 --Call the Update row the Award Disbursement Table
533 igf_aw_awd_disb_pkg.update_row(
534 x_rowid => l_disb_rec.row_id,
535 x_award_id => l_disb_rec.award_id,
536 x_disb_num => l_disb_rec.disb_num,
537 x_tp_cal_type => l_disb_rec.tp_cal_type,
538 x_tp_sequence_number => l_disb_rec.tp_sequence_number,
539 x_disb_gross_amt => l_disb_rec.disb_gross_amt,
540 x_fee_1 => l_disb_rec.fee_1,
541 x_fee_2 => l_disb_rec.fee_2,
542 x_disb_net_amt => l_disb_rec.disb_net_amt,
543 x_disb_date => l_disb_rec.disb_date,
544 x_trans_type => l_disb_rec.trans_type,
545 x_elig_status => l_disb_rec.elig_status,
546 x_elig_status_date => l_disb_rec.elig_status_date,
547 x_affirm_flag => l_disb_rec.affirm_flag,
548 x_hold_rel_ind => l_disb_rec.hold_rel_ind,
549 x_manual_hold_ind => 'Y',
550 x_disb_status => l_disb_rec.disb_status,
551 x_disb_status_date => l_disb_rec.disb_status_date,
552 x_late_disb_ind => l_disb_rec.late_disb_ind,
553 x_fund_dist_mthd => l_disb_rec.fund_dist_mthd,
554 x_prev_reported_ind => l_disb_rec.prev_reported_ind,
555 x_fund_release_date => l_disb_rec.fund_release_date,
556 x_fund_status => l_disb_rec.fund_status,
557 x_fund_status_date => l_disb_rec.fund_status_date,
558 x_fee_paid_1 => l_disb_rec.fee_paid_1,
559 x_fee_paid_2 => l_disb_rec. fee_paid_2,
560 x_cheque_number => l_disb_rec.cheque_number,
561 x_ld_cal_type => l_disb_rec.ld_cal_type,
562 x_ld_sequence_number => l_disb_rec.ld_sequence_number,
563 x_disb_accepted_amt => l_disb_rec.disb_accepted_amt,
564 x_disb_paid_amt => l_disb_rec.disb_paid_amt,
565 x_rvsn_id => l_disb_rec.rvsn_id,
566 x_int_rebate_amt => l_disb_rec.int_rebate_amt,
567 x_force_disb => l_disb_rec.force_disb,
568 x_min_credit_pts => l_disb_rec.min_credit_pts,
569 x_disb_exp_dt => l_disb_rec.disb_exp_dt,
570 x_verf_enfr_dt => l_disb_rec.verf_enfr_dt,
571 x_fee_class => l_disb_rec. fee_class,
572 x_show_on_bill => l_disb_rec.show_on_bill,
573 x_attendance_type_code => l_disb_rec.attendance_type_code,
574 x_base_attendance_type_code => l_disb_rec.base_attendance_type_code,
575 x_payment_prd_st_date => l_disb_rec.payment_prd_st_date,
576 x_change_type_code => l_disb_rec.change_type_code,
577 x_fund_return_mthd_code => l_disb_rec.fund_return_mthd_code,
578 x_direct_to_borr_flag => l_disb_rec.direct_to_borr_flag,
579 x_mode => 'R');
580
581 END IF; --check for Manual Hold
582
583
584 END insert_row;
585
586
587 PROCEDURE lock_row (
588 x_rowid IN VARCHAR2,
589 x_hold_id IN NUMBER,
590 x_award_id IN NUMBER,
591 x_disb_num IN NUMBER,
592 x_hold IN VARCHAR2,
593 x_hold_date IN VARCHAR2,
594 x_hold_type IN VARCHAR2,
595 x_release_date IN DATE,
596 x_release_flag IN VARCHAR2,
597 x_release_reason IN VARCHAR2
598 ) AS
599 /*
600 || Created By : mesriniv
601 || Created On : 08-JAN-2002
602 || Purpose : Handles the LOCK mechanism for the table.
603 || Known limitations, enhancements or remarks :
604 || Change History :
605 || Who When What
606 || (reverse chronological order - newest change first)
607 */
608 CURSOR c1 IS
609 SELECT
610 award_id,
611 disb_num,
612 hold,
613 hold_date,
614 hold_type,
615 release_date,
616 release_flag,
617 release_reason
618 FROM igf_db_disb_holds_all
619 WHERE rowid = x_rowid
620 FOR UPDATE NOWAIT;
621
622 tlinfo c1%ROWTYPE;
623
624 BEGIN
625
626 OPEN c1;
627 FETCH c1 INTO tlinfo;
628 IF (c1%notfound) THEN
629 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
630 igs_ge_msg_stack.add;
631 CLOSE c1;
632 app_exception.raise_exception;
633 RETURN;
634 END IF;
635 CLOSE c1;
636
637 IF (
638 (tlinfo.award_id = x_award_id)
639 AND (tlinfo.disb_num = x_disb_num)
640 AND (tlinfo.hold = x_hold)
641 AND (tlinfo.hold_date = x_hold_date)
642 AND (tlinfo.hold_type = x_hold_type)
643 AND ((tlinfo.release_date = x_release_date) OR ((tlinfo.release_date IS NULL) AND (X_release_date IS NULL)))
644 AND ((tlinfo.release_flag = x_release_flag) OR ((tlinfo.release_flag IS NULL) AND (X_release_flag IS NULL)))
645 AND ((tlinfo.release_reason = x_release_reason) OR ((tlinfo.release_reason IS NULL) AND (X_release_reason IS NULL)))
646 ) THEN
647 NULL;
648 ELSE
649 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
650 igs_ge_msg_stack.add;
651 app_exception.raise_exception;
652 END IF;
653
654 RETURN;
655
656 END lock_row;
657
658
659 PROCEDURE update_row (
660 x_rowid IN VARCHAR2,
661 x_hold_id IN NUMBER,
662 x_award_id IN NUMBER,
663 x_disb_num IN NUMBER,
664 x_hold IN VARCHAR2,
665 x_hold_date IN VARCHAR2,
666 x_hold_type IN VARCHAR2,
667 x_release_date IN DATE,
668 x_release_flag IN VARCHAR2,
669 x_release_reason IN VARCHAR2,
670 x_mode IN VARCHAR2
671 ) AS
672 /*
673 || Created By : mesriniv
674 || Created On : 08-JAN-2002
675 || Purpose : Handles the UPDATE DML logic for the table.
676 || Known limitations, enhancements or remarks :
677 || Change History :
678 || Who When What
679 || (reverse chronological order - newest change first)
680 */
681 x_last_update_date DATE ;
682 x_last_updated_by NUMBER;
683 x_last_update_login NUMBER;
684 l_hold_id igf_db_disb_holds.hold_id%TYPE;
685 l_disb_rec igf_aw_awd_disb%ROWTYPE;
686
687 --Cursor to check if all the Holds has been released for the disbursement
688 CURSOR cur_get_Holds IS
689 SELECT hold_id
690 FROM igf_db_disb_holds
691 WHERE disb_num =x_disb_num
692 AND award_id = x_award_id
693 AND release_flag ='N';
694
695
696 --Cursor to fetch the Disbursement
697 CURSOR cur_get_disb IS
698 SELECT * FROM igf_aw_awd_disb
699 WHERE award_id=x_award_id
700 AND disb_num =x_disb_num
701 FOR UPDATE OF disb_num NOWAIT;
702
703
704 BEGIN
705
706 x_last_update_date := SYSDATE;
707 IF (X_MODE = 'I') THEN
708 x_last_updated_by := 1;
709 x_last_update_login := 0;
710 ELSIF (x_mode = 'R') THEN
711 x_last_updated_by := fnd_global.user_id;
712 IF x_last_updated_by IS NULL THEN
713 x_last_updated_by := -1;
714 END IF;
715 x_last_update_login := fnd_global.login_id;
716 IF (x_last_update_login IS NULL) THEN
717 x_last_update_login := -1;
718 END IF;
719 ELSE
720 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
721 igs_ge_msg_stack.add;
722 app_exception.raise_exception;
723 END IF;
724
725 before_dml(
726 p_action => 'UPDATE',
727 x_rowid => x_rowid,
728 x_hold_id => x_hold_id,
729 x_award_id => x_award_id,
730 x_disb_num => x_disb_num,
731 x_hold => x_hold,
732 x_hold_date => x_hold_date,
733 x_hold_type => x_hold_type,
734 x_release_date => x_release_date,
735 x_release_flag => x_release_flag,
736 x_release_reason => x_release_reason,
737 x_creation_date => x_last_update_date,
738 x_created_by => x_last_updated_by,
739 x_last_update_date => x_last_update_date,
740 x_last_updated_by => x_last_updated_by,
741 x_last_update_login => x_last_update_login
742 );
743
744 UPDATE igf_db_disb_holds_all
745 SET
746 award_id = new_references.award_id,
747 disb_num = new_references.disb_num,
748 hold = new_references.hold,
749 hold_date = new_references.hold_date,
750 hold_type = new_references.hold_type,
751 release_date = new_references.release_date,
752 release_flag = new_references.release_flag,
753 release_reason = new_references.release_reason,
754 last_update_date = x_last_update_date,
755 last_updated_by = x_last_updated_by,
756 last_update_login = x_last_update_login
757 WHERE rowid = x_rowid;
758
759 IF (SQL%NOTFOUND) THEN
760 RAISE NO_DATA_FOUND;
761 END IF;
762
763 --Whenever the Rlease Flag is Set To N from anywhere ,we need to check if all the Holds have been
764 --released.If all the Holds are released then we need to Update the Release Hold Ind and the
765 --Manual Hold Ind for the disbursement in the award disbursements as Y.
766 --To indicate that there are no Holds for this Disbursement
767
768 --Do this only when a release is made
769 IF ( new_references.release_flag ='Y' ) THEN
770
771 --Check if all the Holds have been released.
772 --Even if there is one Hold then do not Update the Disbursement Table.
773 --Only if not found do we update the disbursement table
774 --To indicate that all the Holds for this disbursement are released
775
776 OPEN cur_get_holds;
777 FETCH cur_get_holds INTO l_hold_id;
778 IF cur_get_holds%NOTFOUND THEN
779
780 --Fetch the Cursor Record
781 OPEN cur_get_disb ;
782 FETCH cur_get_disb INTO l_disb_rec;
783 CLOSE cur_get_disb;
784
785 --Call the Update row the Award Disbursement Table
786 igf_aw_awd_disb_pkg.update_row(
787 x_rowid => l_disb_rec.row_id,
788 x_award_id => l_disb_rec.award_id,
789 x_disb_num => l_disb_rec.disb_num,
790 x_tp_cal_type => l_disb_rec.tp_cal_type,
791 x_tp_sequence_number => l_disb_rec.tp_sequence_number,
792 x_disb_gross_amt => l_disb_rec.disb_gross_amt,
793 x_fee_1 => l_disb_rec.fee_1,
794 x_fee_2 => l_disb_rec.fee_2,
795 x_disb_net_amt => l_disb_rec.disb_net_amt,
796 x_disb_date => l_disb_rec.disb_date,
797 x_trans_type => l_disb_rec.trans_type,
798 x_elig_status => l_disb_rec.elig_status,
799 x_elig_status_date => l_disb_rec.elig_status_date,
800 x_affirm_flag => l_disb_rec.affirm_flag,
801 x_hold_rel_ind => l_disb_rec.hold_rel_ind,
802 x_manual_hold_ind => 'N',
803 x_disb_status => l_disb_rec.disb_status,
804 x_disb_status_date => l_disb_rec.disb_status_date,
805 x_late_disb_ind => l_disb_rec.late_disb_ind,
806 x_fund_dist_mthd => l_disb_rec.fund_dist_mthd,
807 x_prev_reported_ind => l_disb_rec.prev_reported_ind,
808 x_fund_release_date => l_disb_rec.fund_release_date,
809 x_fund_status => l_disb_rec.fund_status,
810 x_fund_status_date => l_disb_rec.fund_status_date,
811 x_fee_paid_1 => l_disb_rec.fee_paid_1,
812 x_fee_paid_2 => l_disb_rec. fee_paid_2,
813 x_cheque_number => l_disb_rec.cheque_number,
814 x_ld_cal_type => l_disb_rec.ld_cal_type,
815 x_ld_sequence_number => l_disb_rec.ld_sequence_number,
816 x_disb_accepted_amt => l_disb_rec.disb_accepted_amt,
817 x_disb_paid_amt => l_disb_rec.disb_paid_amt,
818 x_rvsn_id => l_disb_rec.rvsn_id,
819 x_int_rebate_amt => l_disb_rec.int_rebate_amt,
820 x_force_disb => l_disb_rec.force_disb,
821 x_min_credit_pts => l_disb_rec.min_credit_pts,
822 x_disb_exp_dt => l_disb_rec.disb_exp_dt,
823 x_verf_enfr_dt => l_disb_rec.verf_enfr_dt,
824 x_fee_class => l_disb_rec. fee_class,
825 x_show_on_bill => l_disb_rec.show_on_bill,
826 x_attendance_type_code => l_disb_rec.attendance_type_code,
827 x_base_attendance_type_code => l_disb_rec.base_attendance_type_code,
828 x_payment_prd_st_date => l_disb_rec.payment_prd_st_date,
829 x_change_type_code => l_disb_rec.change_type_code,
830 x_fund_return_mthd_code => l_disb_rec.fund_return_mthd_code,
831 x_direct_to_borr_flag => l_disb_rec.direct_to_borr_flag,
832 x_mode => 'R'
833 );
834
835
836
837 CLOSE cur_get_holds;
838 END IF; --End of cursor found check
839 END IF; --end of check if a hold is released
840
841
842 END update_row;
843
844
845 PROCEDURE add_row (
846 x_rowid IN OUT NOCOPY VARCHAR2,
847 x_hold_id IN OUT NOCOPY NUMBER,
848 x_award_id IN NUMBER,
849 x_disb_num IN NUMBER,
850 x_hold IN VARCHAR2,
851 x_hold_date IN VARCHAR2,
852 x_hold_type IN VARCHAR2,
853 x_release_date IN DATE,
854 x_release_flag IN VARCHAR2,
855 x_release_reason IN VARCHAR2,
856 x_mode IN VARCHAR2
857 ) AS
858 /*
859 || Created By : mesriniv
860 || Created On : 08-JAN-2002
861 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
862 || Known limitations, enhancements or remarks :
863 || Change History :
864 || Who When What
865 || (reverse chronological order - newest change first)
866 */
867 CURSOR c1 IS
868 SELECT rowid
869 FROM igf_db_disb_holds_all
870 WHERE hold_id = x_hold_id;
871
872 BEGIN
873
874 OPEN c1;
875 FETCH c1 INTO x_rowid;
876 IF (c1%NOTFOUND) THEN
877 CLOSE c1;
878
879 insert_row (
880 x_rowid,
881 x_hold_id,
882 x_award_id,
883 x_disb_num,
884 x_hold,
885 x_hold_date,
886 x_hold_type,
887 x_release_date,
888 x_release_flag,
889 x_release_reason,
890 x_mode
891 );
892 RETURN;
893 END IF;
894 CLOSE c1;
895
896 update_row (
897 x_rowid,
898 x_hold_id,
899 x_award_id,
900 x_disb_num,
901 x_hold,
902 x_hold_date,
903 x_hold_type,
904 x_release_date,
905 x_release_flag,
906 x_release_reason,
907 x_mode
908 );
909
910 END add_row;
911
912
913 PROCEDURE delete_row (
914 x_rowid IN VARCHAR2
915 ) AS
916 /*
917 || Created By : mesriniv
918 || Created On : 08-JAN-2002
919 || Purpose : Handles the DELETE DML logic for the table.
920 || Known limitations, enhancements or remarks :
921 || Change History :
922 || Who When What
923 || (reverse chronological order - newest change first)
924 */
925 BEGIN
926
927 before_dml (
928 p_action => 'DELETE',
929 x_rowid => x_rowid
930 );
931
932 DELETE FROM igf_db_disb_holds_all
933 WHERE rowid = x_rowid;
934
935 IF (SQL%NOTFOUND) THEN
936 RAISE NO_DATA_FOUND;
937 END IF;
938
939 END delete_row;
940
941
942 END igf_db_disb_holds_pkg;
[Home] [Help]