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_APPLICATIONS_PKG

Source


1 PACKAGE BODY igs_fi_applications_pkg AS
2 /* $Header: IGSSI94B.pls 115.12 2003/02/17 08:46:58 pathipat ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_fi_applications%ROWTYPE;
6   new_references igs_fi_applications%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    ,
11     x_application_id                    IN     NUMBER      ,
12     x_application_type                  IN     VARCHAR2    ,
13     x_invoice_id                        IN     NUMBER      ,
14     x_credit_id                         IN     NUMBER      ,
15     x_credit_activity_id                IN     NUMBER      ,
16     x_amount_applied                    IN     NUMBER      ,
17     x_apply_date                        IN     DATE        ,
18     x_link_application_id               IN     NUMBER      ,
19     x_dr_account_cd                     IN     VARCHAR2    ,
20     x_cr_account_cd                     IN     VARCHAR2    ,
21     x_dr_gl_code_ccid                   IN     NUMBER      ,
22     x_cr_gl_code_ccid                   IN     NUMBER      ,
23     x_applied_invoice_lines_id          IN     NUMBER      ,
24     x_appl_hierarchy_id                 IN     NUMBER      ,
25     x_posting_id                        IN     NUMBER      ,
26     x_creation_date                     IN     DATE        ,
27     x_created_by                        IN     NUMBER      ,
28     x_last_update_date                  IN     DATE        ,
29     x_last_updated_by                   IN     NUMBER      ,
30     x_last_update_login                 IN     NUMBER      ,
31     x_gl_date                           IN     DATE        ,
32     x_gl_posted_date                    IN     DATE        ,
33     x_posting_control_id                IN     NUMBER
34   ) AS
35   /*
36   ||  Created By : BDEVARAK
37   ||  Created On : 26-APR-2001
38   ||  Purpose : Initialises the Old and New references for the columns of the table.
39   ||  Known limitations, enhancements or remarks :
40   ||  Change History :
41   ||  Who             When            What
42   ||smadathi     01-Nov-2002        Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
43   ||                                POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
44   ||  (reverse chronological order - newest change first)
45   */
46 
47     CURSOR cur_old_ref_values IS
48       SELECT   *
49       FROM     IGS_FI_APPLICATIONS
50       WHERE    rowid = x_rowid;
51 
52   BEGIN
53 
54     l_rowid := x_rowid;
55 
56     -- Code for setting the Old and New Reference Values.
57     -- Populate Old Values.
58     OPEN cur_old_ref_values;
59     FETCH cur_old_ref_values INTO old_references;
60     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
61       CLOSE cur_old_ref_values;
62       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
63       igs_ge_msg_stack.add;
64       app_exception.raise_exception;
65       RETURN;
66     END IF;
67     CLOSE cur_old_ref_values;
68 
69     -- Populate New Values.
70     new_references.application_id                    := x_application_id;
71     new_references.application_type                  := x_application_type;
72     new_references.invoice_id                        := x_invoice_id;
73     new_references.credit_id                         := x_credit_id;
74     new_references.credit_activity_id                := x_credit_activity_id;
75     new_references.amount_applied                    := x_amount_applied;
76     new_references.apply_date                        := x_apply_date;
77     new_references.link_application_id               := x_link_application_id;
78     new_references.dr_account_cd                     := x_dr_account_cd;
79     new_references.cr_account_cd                     := x_cr_account_cd;
80     new_references.dr_gl_code_ccid                   := x_dr_gl_code_ccid;
81     new_references.cr_gl_code_ccid                   := x_cr_gl_code_ccid;
82     new_references.applied_invoice_lines_id          := x_applied_invoice_lines_id;
83     new_references.appl_hierarchy_id                 := x_appl_hierarchy_id;
84     new_references.posting_id                        := x_posting_id;
85     new_references.gl_date                           := TRUNC(x_gl_date);
86     new_references.gl_posted_date                    := x_gl_posted_date;
87     new_references.posting_control_id                := x_posting_control_id;
88 
89     IF (p_action = 'UPDATE') THEN
90       new_references.creation_date                   := old_references.creation_date;
91       new_references.created_by                      := old_references.created_by;
92     ELSE
93       new_references.creation_date                   := x_creation_date;
94       new_references.created_by                      := x_created_by;
95     END IF;
96 
97     new_references.last_update_date                  := x_last_update_date;
98     new_references.last_updated_by                   := x_last_updated_by;
99     new_references.last_update_login                 := x_last_update_login;
100 
101   END set_column_values;
102 
103 
104   PROCEDURE check_parent_existance AS
105   /*
106   ||  Created By : BDEVARAK
107   ||  Created On : 26-APR-2001
108   ||  Purpose : Checks for the existance of Parent records.
109   ||  Known limitations, enhancements or remarks :
110   ||  Change History :
111   ||  Who             When            What
112   ||  (reverse chronological order - newest change first)
113   */
114   BEGIN
115 
116     IF ((old_references.cr_account_cd = new_references.cr_account_cd) OR
117          (new_references.cr_account_cd IS NULL)) THEN
118       NULL;
119     ELSE
120       IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
121                new_references.cr_account_cd
122                ) THEN
123         Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
124         IGS_GE_MSG_STACK.ADD;
125         App_Exception.Raise_Exception;
126       END IF;
127     END IF;
128     IF ((old_references.dr_account_cd = new_references.dr_account_cd) OR
129          (new_references.dr_account_cd IS NULL)) THEN
130       NULL;
131     ELSE
132       IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
133                new_references.dr_account_cd
134                ) THEN
135         Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
136         IGS_GE_MSG_STACK.ADD;
137         App_Exception.Raise_Exception;
138       END IF;
139     END IF;
140 
141     IF (((old_references.application_type = new_references.application_type)) OR
142         ((new_references.application_type IS NULL))) THEN
143              NULL;
144     ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
145           'IGS_FI_APPLICATION_TYPE',
146           new_references.application_type
147           )THEN
148             Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
149             IGS_GE_MSG_STACK.ADD;
150             App_Exception.Raise_Exception;
151     END IF;
152 
153     IF (((old_references.credit_id = new_references.credit_id)) OR
154         ((new_references.credit_id IS NULL))) THEN
155       NULL;
156     ELSIF NOT igs_fi_credits_pkg.get_pk_for_validation (
157                 new_references.credit_id
158               ) THEN
159       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
160       igs_ge_msg_stack.add;
161       app_exception.raise_exception;
162     END IF;
163 
164     IF (((old_references.credit_activity_id = new_references.credit_activity_id)) OR
165         ((new_references.credit_activity_id IS NULL))) THEN
166       NULL;
167     ELSIF NOT igs_fi_cr_activities_pkg.get_pk_for_validation (
168                 new_references.credit_activity_id
169               ) THEN
170       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
171       igs_ge_msg_stack.add;
172       app_exception.raise_exception;
173     END IF;
174 
175     IF (((old_references.posting_id = new_references.posting_id)) OR
176         ((new_references.posting_id IS NULL))) THEN
177       NULL;
178     ELSIF NOT igs_fi_posting_int_pkg.get_pk_for_validation (
179                 new_references.posting_id
180               ) THEN
181       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
182       igs_ge_msg_stack.add;
183       app_exception.raise_exception;
184     END IF;
185 
186     IF (((old_references.appl_hierarchy_id = new_references.appl_hierarchy_id)) OR
187         ((new_references.appl_hierarchy_id IS NULL))) THEN
188       NULL;
189     ELSIF NOT igs_fi_a_hierarchies_pkg.get_pk_for_validation (
190                 new_references.appl_hierarchy_id
191               ) THEN
192       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
193       igs_ge_msg_stack.add;
194       app_exception.raise_exception;
195     END IF;
196 
197     IF (((old_references.invoice_id = new_references.invoice_id)) OR
198         ((new_references.invoice_id IS NULL))) THEN
199       NULL;
200     ELSIF NOT igs_fi_inv_int_pkg.get_pk_for_validation (
201                 new_references.invoice_id
202               ) THEN
203       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
204       igs_ge_msg_stack.add;
205       app_exception.raise_exception;
206     END IF;
207 
208     IF (((old_references.applied_invoice_lines_id = new_references.applied_invoice_lines_id)) OR
209         ((new_references.applied_invoice_lines_id IS NULL))) THEN
210       NULL;
211     ELSIF NOT igs_fi_invln_int_pkg.get_pk_for_validation (
212                 new_references.applied_invoice_lines_id
213               ) THEN
214       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
215       igs_ge_msg_stack.add;
216       app_exception.raise_exception;
217     END IF;
218 
219   END check_parent_existance;
220 
221 
222   FUNCTION get_pk_for_validation (
223     x_application_id                    IN     NUMBER
224   ) RETURN BOOLEAN AS
225   /*
226   ||  Created By : BDEVARAK
227   ||  Created On : 26-APR-2001
228   ||  Purpose : Validates the Primary Key of the table.
229   ||  Known limitations, enhancements or remarks :
230   ||  Change History :
231   ||  Who             When            What
232   ||  (reverse chronological order - newest change first)
233   */
234     CURSOR cur_rowid IS
235       SELECT   rowid
236       FROM     igs_fi_applications
237       WHERE    application_id = x_application_id
238       FOR UPDATE NOWAIT;
239 
240     lv_rowid cur_rowid%RowType;
241 
242   BEGIN
243 
244     OPEN cur_rowid;
245     FETCH cur_rowid INTO lv_rowid;
246     IF (cur_rowid%FOUND) THEN
247       CLOSE cur_rowid;
248       RETURN(TRUE);
249     ELSE
250       CLOSE cur_rowid;
251       RETURN(FALSE);
252     END IF;
253 
254   END get_pk_for_validation;
255 
256 
257   PROCEDURE get_fk_igs_fi_credits_all (
258     x_credit_id                         IN     NUMBER
259   ) AS
260   /*
261   ||  Created By : BDEVARAK
262   ||  Created On : 26-APR-2001
263   ||  Purpose : Validates the Foreign Keys for the table.
264   ||  Known limitations, enhancements or remarks :
265   ||  Change History :
266   ||  Who             When            What
267   ||  (reverse chronological order - newest change first)
268   */
269     CURSOR cur_rowid IS
270       SELECT   rowid
271       FROM     igs_fi_applications
272       WHERE   ((credit_id = x_credit_id));
273 
274     lv_rowid cur_rowid%RowType;
275 
276   BEGIN
277 
278     OPEN cur_rowid;
279     FETCH cur_rowid INTO lv_rowid;
280     IF (cur_rowid%FOUND) THEN
281       CLOSE cur_rowid;
282       fnd_message.set_name ('IGS', 'IGS_FI_APPL_CRDT_FK');
283       igs_ge_msg_stack.add;
284       app_exception.raise_exception;
285       RETURN;
286     END IF;
287     CLOSE cur_rowid;
288 
289   END get_fk_igs_fi_credits_all;
290 
291 
292   PROCEDURE get_fk_igs_fi_cr_activities (
293     x_credit_activity_id                IN     NUMBER
294   ) AS
295   /*
296   ||  Created By : BDEVARAK
297   ||  Created On : 26-APR-2001
298   ||  Purpose : Validates the Foreign Keys for the table.
299   ||  Known limitations, enhancements or remarks :
300   ||  Change History :
301   ||  Who             When            What
302   ||  (reverse chronological order - newest change first)
303   */
304     CURSOR cur_rowid IS
305       SELECT   rowid
306       FROM     igs_fi_applications
307       WHERE   ((credit_activity_id = x_credit_activity_id));
308 
309     lv_rowid cur_rowid%RowType;
310 
311   BEGIN
312 
313     OPEN cur_rowid;
314     FETCH cur_rowid INTO lv_rowid;
315     IF (cur_rowid%FOUND) THEN
316       CLOSE cur_rowid;
317       fnd_message.set_name ('IGS', 'IGS_FI_APPL_CRAC_FK');
318       igs_ge_msg_stack.add;
319       app_exception.raise_exception;
320       RETURN;
321     END IF;
322     CLOSE cur_rowid;
323 
324   END get_fk_igs_fi_cr_activities;
325 
326 
327   PROCEDURE get_fk_igs_fi_posting_int_all (
328     x_posting_id                        IN     NUMBER
329   ) AS
330   /*
331   ||  Created By : BDEVARAK
332   ||  Created On : 26-APR-2001
333   ||  Purpose : Validates the Foreign Keys for the table.
334   ||  Known limitations, enhancements or remarks :
335   ||  Change History :
336   ||  Who             When            What
337   ||  (reverse chronological order - newest change first)
338   */
339     CURSOR cur_rowid IS
340       SELECT   rowid
341       FROM     igs_fi_applications
342       WHERE   ((posting_id = x_posting_id));
343 
344     lv_rowid cur_rowid%RowType;
345 
346   BEGIN
347 
348     OPEN cur_rowid;
349     FETCH cur_rowid INTO lv_rowid;
350     IF (cur_rowid%FOUND) THEN
351       CLOSE cur_rowid;
352       fnd_message.set_name ('IGS', 'IGS_FI_APPL_PINT_FK');
353       igs_ge_msg_stack.add;
354       app_exception.raise_exception;
355       RETURN;
356     END IF;
357     CLOSE cur_rowid;
358 
359   END get_fk_igs_fi_posting_int_all;
360 
361 
362   PROCEDURE get_fk_igs_fi_a_hierarchies (
363     x_appl_hierarchy_id                 IN     NUMBER
364   ) AS
365   /*
366   ||  Created By : BDEVARAK
367   ||  Created On : 26-APR-2001
368   ||  Purpose : Validates the Foreign Keys for the table.
369   ||  Known limitations, enhancements or remarks :
370   ||  Change History :
371   ||  Who             When            What
372   ||  (reverse chronological order - newest change first)
373   */
374     CURSOR cur_rowid IS
375       SELECT   rowid
376       FROM     igs_fi_applications
377       WHERE   ((appl_hierarchy_id = x_appl_hierarchy_id));
378 
379     lv_rowid cur_rowid%RowType;
380 
381   BEGIN
382 
383     OPEN cur_rowid;
384     FETCH cur_rowid INTO lv_rowid;
385     IF (cur_rowid%FOUND) THEN
386       CLOSE cur_rowid;
387       fnd_message.set_name ('IGS', 'IGS_FI_APPL_APHR_FK');
388       igs_ge_msg_stack.add;
389       app_exception.raise_exception;
390       RETURN;
391     END IF;
392     CLOSE cur_rowid;
393 
394   END get_fk_igs_fi_a_hierarchies;
395 
396 
397   PROCEDURE get_fk_igs_fi_inv_int_all (
398     x_invoice_id                        IN     NUMBER
399   ) AS
400   /*
401   ||  Created By : BDEVARAK
402   ||  Created On : 26-APR-2001
403   ||  Purpose : Validates the Foreign Keys for the table.
404   ||  Known limitations, enhancements or remarks :
405   ||  Change History :
406   ||  Who             When            What
407   ||  (reverse chronological order - newest change first)
408   */
409     CURSOR cur_rowid IS
410       SELECT   rowid
411       FROM     igs_fi_applications
412       WHERE   ((invoice_id = x_invoice_id));
413 
414     lv_rowid cur_rowid%RowType;
415 
416   BEGIN
417 
418     OPEN cur_rowid;
419     FETCH cur_rowid INTO lv_rowid;
420     IF (cur_rowid%FOUND) THEN
421       CLOSE cur_rowid;
422       fnd_message.set_name ('IGS', 'IGS_FI_APPL_INVI_FK');
423       igs_ge_msg_stack.add;
424       app_exception.raise_exception;
425       RETURN;
426     END IF;
427     CLOSE cur_rowid;
428 
429   END get_fk_igs_fi_inv_int_all;
430 
431 
432   PROCEDURE get_fk_igs_fi_invln_int_all (
433     x_invoice_lines_id                  IN     NUMBER
434   ) AS
435   /*
436   ||  Created By : BDEVARAK
437   ||  Created On : 26-APR-2001
438   ||  Purpose : Validates the Foreign Keys for the table.
439   ||  Known limitations, enhancements or remarks :
440   ||  Change History :
441   ||  Who             When            What
442   ||  (reverse chronological order - newest change first)
443   */
444     CURSOR cur_rowid IS
445       SELECT   rowid
446       FROM     igs_fi_applications
447       WHERE   ((applied_invoice_lines_id = x_invoice_lines_id));
448 
449     lv_rowid cur_rowid%RowType;
450 
451   BEGIN
452 
453     OPEN cur_rowid;
454     FETCH cur_rowid INTO lv_rowid;
455     IF (cur_rowid%FOUND) THEN
456       CLOSE cur_rowid;
457       fnd_message.set_name ('IGS', 'IGS_FI_APPL_INLI_FK');
458       igs_ge_msg_stack.add;
459       app_exception.raise_exception;
460       RETURN;
461     END IF;
462     CLOSE cur_rowid;
463 
464   END get_fk_igs_fi_invln_int_all;
465 
466 
467   PROCEDURE before_dml (
468     p_action                            IN     VARCHAR2,
469     x_rowid                             IN     VARCHAR2    ,
470     x_application_id                    IN     NUMBER      ,
471     x_application_type                  IN     VARCHAR2    ,
472     x_invoice_id                        IN     NUMBER      ,
473     x_credit_id                         IN     NUMBER      ,
474     x_credit_activity_id                IN     NUMBER      ,
475     x_amount_applied                    IN     NUMBER      ,
476     x_apply_date                        IN     DATE        ,
477     x_link_application_id               IN     NUMBER      ,
478     x_dr_account_cd                     IN     VARCHAR2    ,
479     x_cr_account_cd                     IN     VARCHAR2    ,
480     x_dr_gl_code_ccid                   IN     NUMBER      ,
481     x_cr_gl_code_ccid                   IN     NUMBER      ,
482     x_applied_invoice_lines_id          IN     NUMBER      ,
483     x_appl_hierarchy_id                 IN     NUMBER      ,
484     x_posting_id                        IN     NUMBER      ,
485     x_creation_date                     IN     DATE        ,
486     x_created_by                        IN     NUMBER      ,
487     x_last_update_date                  IN     DATE        ,
488     x_last_updated_by                   IN     NUMBER      ,
489     x_last_update_login                 IN     NUMBER      ,
490     x_gl_date                           IN     DATE        ,
491     x_gl_posted_date                    IN     DATE        ,
492     x_posting_control_id                IN     NUMBER
493 
494   ) AS
495   /*
496   ||  Created By : BDEVARAK
497   ||  Created On : 26-APR-2001
498   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
499   ||            Trigger Handlers for the table, before any DML operation.
500   ||  Known limitations, enhancements or remarks :
501   ||  Change History :
502   ||  Who             When            What
503   ||  smadathi     01-Nov-2002        Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
504   ||                                  POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
505   ||  (reverse chronological order - newest change first)
506   */
507   BEGIN
508 
509     set_column_values (
510       p_action,
511       x_rowid,
512       x_application_id,
513       x_application_type,
514       x_invoice_id,
515       x_credit_id,
516       x_credit_activity_id,
517       x_amount_applied,
518       x_apply_date,
519       x_link_application_id,
520       x_dr_account_cd,
521       x_cr_account_cd,
522       x_dr_gl_code_ccid,
523       x_cr_gl_code_ccid,
524       x_applied_invoice_lines_id,
525       x_appl_hierarchy_id,
526       x_posting_id,
527       x_creation_date,
528       x_created_by,
529       x_last_update_date,
530       x_last_updated_by,
531       x_last_update_login,
532       x_gl_date,
533       x_gl_posted_date,
534       x_posting_control_id
535     );
536 
537     IF (p_action = 'INSERT') THEN
538       -- Call all the procedures related to Before Insert.
539       IF ( get_pk_for_validation(
540              new_references.application_id
541            )
542          ) THEN
543         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
544         igs_ge_msg_stack.add;
545         app_exception.raise_exception;
546       END IF;
547       check_parent_existance;
548     ELSIF (p_action = 'UPDATE') THEN
549       -- Call all the procedures related to Before Update.
550       check_parent_existance;
551     ELSIF (p_action = 'VALIDATE_INSERT') THEN
552       -- Call all the procedures related to Before Insert.
553       IF ( get_pk_for_validation (
554              new_references.application_id
555            )
556          ) THEN
557         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
558         igs_ge_msg_stack.add;
559         app_exception.raise_exception;
560       END IF;
561     END IF;
562 
563   END before_dml;
564 
565 
566   PROCEDURE insert_row (
567     x_rowid                             IN OUT NOCOPY VARCHAR2,
568     x_application_id                    IN OUT NOCOPY NUMBER,
569     x_application_type                  IN     VARCHAR2,
570     x_invoice_id                        IN     NUMBER,
571     x_credit_id                         IN     NUMBER,
572     x_credit_activity_id                IN     NUMBER,
573     x_amount_applied                    IN     NUMBER,
574     x_apply_date                        IN     DATE,
575     x_link_application_id               IN     NUMBER,
576     x_dr_account_cd                     IN     VARCHAR2,
577     x_cr_account_cd                     IN     VARCHAR2,
578     x_dr_gl_code_ccid                   IN     NUMBER,
579     x_cr_gl_code_ccid                   IN     NUMBER,
580     x_applied_invoice_lines_id          IN     NUMBER,
581     x_appl_hierarchy_id                 IN     NUMBER,
582     x_posting_id                        IN     NUMBER,
583     x_mode                              IN     VARCHAR2,
584     x_gl_date                           IN     DATE    ,
585     x_gl_posted_date                    IN     DATE    ,
586     x_posting_control_id                IN     NUMBER
587   ) AS
588   /*
589   ||  Created By : BDEVARAK
590   ||  Created On : 26-APR-2001
591   ||  Purpose : Handles the INSERT DML logic for the table.
592   ||  Known limitations, enhancements or remarks :
593   ||  Change History :
594   ||  Who             When            What
595   ||  smadathi     01-Nov-2002        Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
596   ||                                  POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
597   ||  (reverse chronological order - newest change first)
598   */
599     CURSOR c IS
600       SELECT   rowid
601       FROM     igs_fi_applications
602       WHERE    application_id                    = x_application_id;
603 
604     x_last_update_date           DATE;
605     x_last_updated_by            NUMBER;
606     x_last_update_login          NUMBER;
607     x_request_id                 NUMBER;
608     x_program_id                 NUMBER;
609     x_program_application_id     NUMBER;
610     x_program_update_date        DATE;
611 
612   BEGIN
613 
614     x_last_update_date := SYSDATE;
615     IF (x_mode = 'I') THEN
616       x_last_updated_by := 1;
617       x_last_update_login := 0;
618     ELSIF (x_mode = 'R') THEN
619       x_last_updated_by := fnd_global.user_id;
620       IF (x_last_updated_by IS NULL) THEN
621         x_last_updated_by := -1;
622       END IF;
623       x_last_update_login := fnd_global.login_id;
624       IF (x_last_update_login IS NULL) THEN
625         x_last_update_login := -1;
626       END IF;
627       x_request_id             := fnd_global.conc_request_id;
628       x_program_id             := fnd_global.conc_program_id;
629       x_program_application_id := fnd_global.prog_appl_id;
630 
631       IF (x_request_id = -1) THEN
632         x_request_id             := NULL;
633         x_program_id             := NULL;
634         x_program_application_id := NULL;
635         x_program_update_date    := NULL;
636       ELSE
637         x_program_update_date    := SYSDATE;
638       END IF;
639     ELSE
640       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
641       igs_ge_msg_stack.add;
642       app_exception.raise_exception;
643     END IF;
644 
645     SELECT    igs_fi_applications_s.NEXTVAL
646     INTO      x_application_id
647     FROM      dual;
648 
649     before_dml(
650       p_action                            => 'INSERT',
651       x_rowid                             => x_rowid,
652       x_application_id                    => x_application_id,
653       x_application_type                  => x_application_type,
654       x_invoice_id                        => x_invoice_id,
655       x_credit_id                         => x_credit_id,
656       x_credit_activity_id                => x_credit_activity_id,
657       x_amount_applied                    => x_amount_applied,
658       x_apply_date                        => x_apply_date,
659       x_link_application_id               => x_link_application_id,
660       x_dr_account_cd                     => x_dr_account_cd,
661       x_cr_account_cd                     => x_cr_account_cd,
662       x_dr_gl_code_ccid                   => x_dr_gl_code_ccid,
663       x_cr_gl_code_ccid                   => x_cr_gl_code_ccid,
664       x_applied_invoice_lines_id          => x_applied_invoice_lines_id,
665       x_appl_hierarchy_id                 => x_appl_hierarchy_id,
666       x_posting_id                        => x_posting_id,
667       x_creation_date                     => x_last_update_date,
668       x_created_by                        => x_last_updated_by,
669       x_last_update_date                  => x_last_update_date,
670       x_last_updated_by                   => x_last_updated_by,
671       x_last_update_login                 => x_last_update_login ,
672       x_gl_date                           => x_gl_date,
673       x_gl_posted_date                    => x_gl_posted_date,
674       x_posting_control_id                => x_posting_control_id
675     );
676 
677     INSERT INTO igs_fi_applications (
678       application_id,
679       application_type,
680       invoice_id,
681       credit_id,
682       credit_activity_id,
683       amount_applied,
684       apply_date,
685       link_application_id,
686       dr_account_cd,
687       cr_account_cd,
688       dr_gl_code_ccid,
689       cr_gl_code_ccid,
690       applied_invoice_lines_id,
691       appl_hierarchy_id,
692       posting_id,
693       creation_date,
694       created_by,
695       last_update_date,
696       last_updated_by,
697       last_update_login,
698       request_id,
699       program_id,
700       program_application_id,
701       program_update_date ,
702       gl_date,
703       gl_posted_date,
704       posting_control_id
705     ) VALUES (
706       new_references.application_id,
707       new_references.application_type,
708       new_references.invoice_id,
709       new_references.credit_id,
710       new_references.credit_activity_id,
711       new_references.amount_applied,
712       new_references.apply_date,
713       new_references.link_application_id,
714       new_references.dr_account_cd,
715       new_references.cr_account_cd,
716       new_references.dr_gl_code_ccid,
717       new_references.cr_gl_code_ccid,
718       new_references.applied_invoice_lines_id,
719       new_references.appl_hierarchy_id,
720       new_references.posting_id,
721       x_last_update_date,
722       x_last_updated_by,
723       x_last_update_date,
724       x_last_updated_by,
725       x_last_update_login ,
726       x_request_id,
727       x_program_id,
728       x_program_application_id,
729       x_program_update_date ,
730       new_references.gl_date,
731       new_references.gl_posted_date,
732       new_references.posting_control_id
733     );
734 
735     OPEN c;
736     FETCH c INTO x_rowid;
737     IF (c%NOTFOUND) THEN
738       CLOSE c;
739       RAISE NO_DATA_FOUND;
740     END IF;
741     CLOSE c;
742 
743   END insert_row;
744 
745 
746   PROCEDURE lock_row (
747     x_rowid                             IN     VARCHAR2,
748     x_application_id                    IN     NUMBER,
749     x_application_type                  IN     VARCHAR2,
750     x_invoice_id                        IN     NUMBER,
751     x_credit_id                         IN     NUMBER,
752     x_credit_activity_id                IN     NUMBER,
753     x_amount_applied                    IN     NUMBER,
754     x_apply_date                        IN     DATE,
755     x_link_application_id               IN     NUMBER,
756     x_dr_account_cd                     IN     VARCHAR2,
757     x_cr_account_cd                     IN     VARCHAR2,
758     x_dr_gl_code_ccid                   IN     NUMBER,
759     x_cr_gl_code_ccid                   IN     NUMBER,
760     x_applied_invoice_lines_id          IN     NUMBER,
761     x_appl_hierarchy_id                 IN     NUMBER,
762     x_posting_id                        IN     NUMBER,
763     x_gl_date                           IN     DATE,
764     x_gl_posted_date                    IN     DATE,
765     x_posting_control_id                IN     NUMBER
766   ) AS
767   /*
768   ||  Created By : BDEVARAK
769   ||  Created On : 26-APR-2001
770   ||  Purpose : Handles the LOCK mechanism for the table.
771   ||  Known limitations, enhancements or remarks :
772   ||  Change History :
773   ||  Who             When            What
774   ||  smadathi     01-Nov-2002        Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
775   ||                                  POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
776   ||  (reverse chronological order - newest change first)
777   */
778     CURSOR c1 IS
779       SELECT
780         application_type,
781         invoice_id,
782         credit_id,
783         credit_activity_id,
784         amount_applied,
785         apply_date,
786         link_application_id,
787         dr_account_cd,
788         cr_account_cd,
789         dr_gl_code_ccid,
790         cr_gl_code_ccid,
791         applied_invoice_lines_id,
792         appl_hierarchy_id,
793         posting_id,
794         gl_date,
795         gl_posted_date,
796         posting_control_id
797       FROM  igs_fi_applications
798       WHERE rowid = x_rowid
799       FOR UPDATE NOWAIT;
800 
801     tlinfo c1%ROWTYPE;
802 
803   BEGIN
804 
805     OPEN c1;
806     FETCH c1 INTO tlinfo;
807     IF (c1%notfound) THEN
808       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
809       igs_ge_msg_stack.add;
810       CLOSE c1;
811       app_exception.raise_exception;
812       RETURN;
813     END IF;
814     CLOSE c1;
815 
816     IF (
817         (tlinfo.application_type = x_application_type)
818         AND (tlinfo.invoice_id = x_invoice_id)
819         AND (tlinfo.credit_id = x_credit_id)
820         AND (tlinfo.credit_activity_id = x_credit_activity_id)
821         AND (tlinfo.amount_applied = x_amount_applied)
822         AND (trunc(tlinfo.apply_date) = trunc(x_apply_date))
823         AND ((tlinfo.link_application_id = x_link_application_id) OR ((tlinfo.link_application_id IS NULL) AND (X_link_application_id IS NULL)))
824         AND ((tlinfo.dr_account_cd = x_dr_account_cd) OR ((tlinfo.dr_account_cd IS NULL) AND (X_dr_account_cd IS NULL)))
825         AND ((tlinfo.cr_account_cd = x_cr_account_cd) OR ((tlinfo.cr_account_cd IS NULL) AND (X_cr_account_cd IS NULL)))
826         AND ((tlinfo.dr_gl_code_ccid = x_dr_gl_code_ccid) OR ((tlinfo.dr_gl_code_ccid IS NULL) AND (X_dr_gl_code_ccid IS NULL)))
827         AND ((tlinfo.cr_gl_code_ccid = x_cr_gl_code_ccid) OR ((tlinfo.cr_gl_code_ccid IS NULL) AND (X_cr_gl_code_ccid IS NULL)))
828         AND ((tlinfo.applied_invoice_lines_id = x_applied_invoice_lines_id) OR ((tlinfo.applied_invoice_lines_id IS NULL) AND (X_applied_invoice_lines_id IS NULL)))
829         AND ((tlinfo.appl_hierarchy_id = x_appl_hierarchy_id) OR ((tlinfo.appl_hierarchy_id IS NULL) AND (X_appl_hierarchy_id IS NULL)))
830         AND ((tlinfo.posting_id = x_posting_id) OR ((tlinfo.posting_id IS NULL) AND (X_posting_id IS NULL)))
831         AND ((TRUNC(tlinfo.gl_date) = TRUNC(x_gl_date)) OR ((tlinfo.gl_date IS NULL) AND (X_gl_date IS NULL)))
832         AND ((tlinfo.gl_posted_date = x_gl_posted_date) OR ((tlinfo.gl_posted_date IS NULL) AND (X_gl_posted_date IS NULL)))
833         AND ((tlinfo.posting_control_id = x_posting_control_id) OR ((tlinfo.posting_control_id IS NULL) AND (X_posting_control_id IS NULL)))
834        ) THEN
835       NULL;
836     ELSE
837       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
838       igs_ge_msg_stack.add;
839       app_exception.raise_exception;
840     END IF;
841 
842     RETURN;
843 
844   END lock_row;
845 
846 
847   PROCEDURE update_row (
848     x_rowid                             IN     VARCHAR2,
849     x_application_id                    IN     NUMBER,
850     x_application_type                  IN     VARCHAR2,
851     x_invoice_id                        IN     NUMBER,
852     x_credit_id                         IN     NUMBER,
853     x_credit_activity_id                IN     NUMBER,
854     x_amount_applied                    IN     NUMBER,
855     x_apply_date                        IN     DATE,
856     x_link_application_id               IN     NUMBER,
857     x_dr_account_cd                     IN     VARCHAR2,
858     x_cr_account_cd                     IN     VARCHAR2,
859     x_dr_gl_code_ccid                   IN     NUMBER,
860     x_cr_gl_code_ccid                   IN     NUMBER,
861     x_applied_invoice_lines_id          IN     NUMBER,
862     x_appl_hierarchy_id                 IN     NUMBER,
863     x_posting_id                        IN     NUMBER,
864     x_mode                              IN     VARCHAR2,
865     x_gl_date                           IN     DATE,
866     x_gl_posted_date                    IN     DATE,
867     x_posting_control_id                IN     NUMBER
868   ) AS
869   /*
870   ||  Created By : BDEVARAK
871   ||  Created On : 26-APR-2001
872   ||  Purpose : Handles the UPDATE DML logic for the table.
873   ||  Known limitations, enhancements or remarks :
874   ||  Change History :
875   ||  Who             When            What
876   ||  smadathi     01-Nov-2002        Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
877   ||                                  POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
878   ||  (reverse chronological order - newest change first)
879   */
880     x_last_update_date           DATE ;
881     x_last_updated_by            NUMBER;
882     x_last_update_login          NUMBER;
883     x_request_id                 NUMBER;
884     x_program_id                 NUMBER;
885     x_program_application_id     NUMBER;
886     x_program_update_date        DATE;
887 
888   BEGIN
889 
890     x_last_update_date := SYSDATE;
891     IF (X_MODE = 'I') THEN
892       x_last_updated_by := 1;
893       x_last_update_login := 0;
894     ELSIF (x_mode = 'R') THEN
895       x_last_updated_by := fnd_global.user_id;
896       IF x_last_updated_by IS NULL THEN
897         x_last_updated_by := -1;
898       END IF;
899       x_last_update_login := fnd_global.login_id;
900       IF (x_last_update_login IS NULL) THEN
901         x_last_update_login := -1;
902       END IF;
903     ELSE
904       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
905       igs_ge_msg_stack.add;
906       app_exception.raise_exception;
907     END IF;
908 
909     before_dml(
910       p_action                            => 'UPDATE',
911       x_rowid                             => x_rowid,
912       x_application_id                    => x_application_id,
913       x_application_type                  => x_application_type,
914       x_invoice_id                        => x_invoice_id,
915       x_credit_id                         => x_credit_id,
916       x_credit_activity_id                => x_credit_activity_id,
917       x_amount_applied                    => x_amount_applied,
918       x_apply_date                        => x_apply_date,
919       x_link_application_id               => x_link_application_id,
920       x_dr_account_cd                     => x_dr_account_cd,
921       x_cr_account_cd                     => x_cr_account_cd,
922       x_dr_gl_code_ccid                   => x_dr_gl_code_ccid,
923       x_cr_gl_code_ccid                   => x_cr_gl_code_ccid,
924       x_applied_invoice_lines_id          => x_applied_invoice_lines_id,
925       x_appl_hierarchy_id                 => x_appl_hierarchy_id,
926       x_posting_id                        => x_posting_id,
927       x_creation_date                     => x_last_update_date,
928       x_created_by                        => x_last_updated_by,
929       x_last_update_date                  => x_last_update_date,
930       x_last_updated_by                   => x_last_updated_by,
931       x_last_update_login                 => x_last_update_login ,
932       x_gl_date                           => x_gl_date,
933       x_gl_posted_date                    => x_gl_posted_date,
934       x_posting_control_id                => x_posting_control_id
935     );
936 
937     IF (x_mode = 'R') THEN
938       x_request_id := fnd_global.conc_request_id;
939       x_program_id := fnd_global.conc_program_id;
940       x_program_application_id := fnd_global.prog_appl_id;
941       IF (x_request_id =  -1) THEN
942         x_request_id := old_references.request_id;
943         x_program_id := old_references.program_id;
944         x_program_application_id := old_references.program_application_id;
945         x_program_update_date := old_references.program_update_date;
946       ELSE
947         x_program_update_date := SYSDATE;
948       END IF;
949     END IF;
950 
951     UPDATE igs_fi_applications
952       SET
953         application_type                  = new_references.application_type,
954         invoice_id                        = new_references.invoice_id,
955         credit_id                         = new_references.credit_id,
956         credit_activity_id                = new_references.credit_activity_id,
957         amount_applied                    = new_references.amount_applied,
958         apply_date                        = new_references.apply_date,
959         link_application_id               = new_references.link_application_id,
960         dr_account_cd                     = new_references.dr_account_cd,
961         cr_account_cd                     = new_references.cr_account_cd,
962         dr_gl_code_ccid                   = new_references.dr_gl_code_ccid,
963         cr_gl_code_ccid                   = new_references.cr_gl_code_ccid,
964         applied_invoice_lines_id          = new_references.applied_invoice_lines_id,
965         appl_hierarchy_id                 = new_references.appl_hierarchy_id,
966         posting_id                        = new_references.posting_id,
967         last_update_date                  = x_last_update_date,
968         last_updated_by                   = x_last_updated_by,
969         last_update_login                 = x_last_update_login ,
970         request_id                        = x_request_id,
971         program_id                        = x_program_id,
972         program_application_id            = x_program_application_id,
973         program_update_date               = x_program_update_date ,
974         gl_date                           = new_references.gl_date,
975         gl_posted_date                    = new_references.gl_posted_date,
976         posting_control_id                = new_references.posting_control_id
977       WHERE rowid = x_rowid;
978 
979     IF (SQL%NOTFOUND) THEN
980       RAISE NO_DATA_FOUND;
981     END IF;
982 
983   END update_row;
984 
985 
986   PROCEDURE add_row (
987     x_rowid                             IN OUT NOCOPY VARCHAR2,
988     x_application_id                    IN OUT NOCOPY NUMBER,
989     x_application_type                  IN     VARCHAR2,
990     x_invoice_id                        IN     NUMBER,
991     x_credit_id                         IN     NUMBER,
992     x_credit_activity_id                IN     NUMBER,
993     x_amount_applied                    IN     NUMBER,
994     x_apply_date                        IN     DATE,
995     x_link_application_id               IN     NUMBER,
996     x_dr_account_cd                     IN     VARCHAR2,
997     x_cr_account_cd                     IN     VARCHAR2,
998     x_dr_gl_code_ccid                   IN     NUMBER,
999     x_cr_gl_code_ccid                   IN     NUMBER,
1000     x_applied_invoice_lines_id          IN     NUMBER,
1001     x_appl_hierarchy_id                 IN     NUMBER,
1002     x_posting_id                        IN     NUMBER,
1003     x_mode                              IN     VARCHAR2,
1004     x_gl_date                           IN     DATE,
1005     x_gl_posted_date                    IN     DATE,
1006     x_posting_control_id                IN     NUMBER
1007   ) AS
1008   /*
1009   ||  Created By : BDEVARAK
1010   ||  Created On : 26-APR-2001
1011   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1012   ||  Known limitations, enhancements or remarks :
1013   ||  Change History :
1014   ||  Who             When            What
1015   ||  smadathi     01-Nov-2002        Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
1016   ||                                  POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
1017   ||  (reverse chronological order - newest change first)
1018   */
1019     CURSOR c1 IS
1020       SELECT   rowid
1021       FROM     igs_fi_applications
1022       WHERE    application_id                    = x_application_id;
1023 
1024   BEGIN
1025 
1026     OPEN c1;
1027     FETCH c1 INTO x_rowid;
1028     IF (c1%NOTFOUND) THEN
1029       CLOSE c1;
1030 
1031       insert_row (
1032         x_rowid,
1033         x_application_id,
1034         x_application_type,
1035         x_invoice_id,
1036         x_credit_id,
1037         x_credit_activity_id,
1038         x_amount_applied,
1039         x_apply_date,
1040         x_link_application_id,
1041         x_dr_account_cd,
1042         x_cr_account_cd,
1043         x_dr_gl_code_ccid,
1044         x_cr_gl_code_ccid,
1045         x_applied_invoice_lines_id,
1046         x_appl_hierarchy_id,
1047         x_posting_id,
1048         x_mode ,
1049         x_gl_date,
1050         x_gl_posted_date,
1051         x_posting_control_id
1052       );
1053       RETURN;
1054     END IF;
1055     CLOSE c1;
1056 
1057     update_row (
1058       x_rowid,
1059       x_application_id,
1060       x_application_type,
1061       x_invoice_id,
1062       x_credit_id,
1063       x_credit_activity_id,
1064       x_amount_applied,
1065       x_apply_date,
1066       x_link_application_id,
1067       x_dr_account_cd,
1068       x_cr_account_cd,
1069       x_dr_gl_code_ccid,
1070       x_cr_gl_code_ccid,
1071       x_applied_invoice_lines_id,
1072       x_appl_hierarchy_id,
1073       x_posting_id,
1074       x_mode,
1075       x_gl_date,
1076       x_gl_posted_date,
1077       x_posting_control_id
1078     );
1079 
1080   END add_row;
1081 
1082 
1083   PROCEDURE delete_row (
1084     x_rowid IN VARCHAR2
1085   ) AS
1086   /*
1087   ||  Created By : BDEVARAK
1088   ||  Created On : 26-APR-2001
1089   ||  Purpose : Handles the DELETE DML logic for the table.
1090   ||  Known limitations, enhancements or remarks :
1091   ||  Change History :
1092   ||  Who             When            What
1093   ||  (reverse chronological order - newest change first)
1094   */
1095   BEGIN
1096 
1097     before_dml (
1098       p_action => 'DELETE',
1099       x_rowid => x_rowid
1100     );
1101 
1102     DELETE FROM igs_fi_applications
1103     WHERE rowid = x_rowid;
1104 
1105     IF (SQL%NOTFOUND) THEN
1106       RAISE NO_DATA_FOUND;
1107     END IF;
1108 
1109   END delete_row;
1110 
1111 
1112 END igs_fi_applications_pkg;