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