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