eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_FI_BILL_TRNSCTNS_PKG

Source


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