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.IGR_I_A_LINES_PKG

Source


1 PACKAGE BODY igr_i_a_lines_pkg AS
2 /* $Header: IGSRH17B.pls 120.0 2005/06/01 22:35:22 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igr_i_a_lines%ROWTYPE;
6   new_references igr_i_a_lines%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_person_id                         IN     NUMBER,
12     x_enquiry_appl_number               IN     NUMBER,
13     x_sales_lead_line_id                IN     NUMBER,
14     x_preference                        IN     NUMBER,
15     x_creation_date                     IN     DATE,
16     x_created_by                        IN     NUMBER,
17     x_last_update_date                  IN     DATE,
18     x_last_updated_by                   IN     NUMBER,
19     x_last_update_login                 IN     NUMBER
20   ) AS
21   /*
22   ||  Created By : hreddych
23   ||  Created On : 22-JAN-2003
24   ||  Purpose : Initialises the Old and New references for the columns of the table.
25   ||  Known limitations, enhancements or remarks :
26   ||  Change History :
27   ||  Who             When            What
28   ||  (reverse chronological order - newest change first)
29   */
30 
31     CURSOR cur_old_ref_values IS
32       SELECT   *
33       FROM     igr_i_a_lines
34       WHERE    rowid = x_rowid;
35 
36   BEGIN
37 
38     l_rowid := x_rowid;
39 
40     -- Code for setting the Old and New Reference Values.
41     -- Populate Old Values.
42     OPEN cur_old_ref_values;
43     FETCH cur_old_ref_values INTO old_references;
44     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45       CLOSE cur_old_ref_values;
46       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47       igs_ge_msg_stack.add;
48       app_exception.raise_exception;
49       RETURN;
50     END IF;
51     CLOSE cur_old_ref_values;
52 
53     -- Populate New Values.
54     new_references.person_id                         := x_person_id;
55     new_references.enquiry_appl_number               := x_enquiry_appl_number;
56     new_references.sales_lead_line_id                := x_sales_lead_line_id;
57     new_references.preference                        := x_preference;
58 
59     IF (p_action = 'UPDATE') THEN
60       new_references.creation_date                   := old_references.creation_date;
61       new_references.created_by                      := old_references.created_by;
62     ELSE
63       new_references.creation_date                   := x_creation_date;
64       new_references.created_by                      := x_created_by;
65     END IF;
66 
67     new_references.last_update_date                  := x_last_update_date;
68     new_references.last_updated_by                   := x_last_updated_by;
69     new_references.last_update_login                 := x_last_update_login;
70 
71   END set_column_values;
72 
73 
74   PROCEDURE check_parent_existance AS
75   /*
76   ||  Created By : hreddych
77   ||  Created On : 22-JAN-2003
78   ||  Purpose : Checks for the existance of Parent records.
79   ||  Known limitations, enhancements or remarks :
80   ||  Change History :
81   ||  Who             When            What
82   ||  (reverse chronological order - newest change first)
83   */
84   BEGIN
85 
86     IF (((old_references.person_id = new_references.person_id) AND
87          (old_references.enquiry_appl_number = new_references.enquiry_appl_number)) OR
88         ((new_references.person_id IS NULL) OR
89          (new_references.enquiry_appl_number IS NULL))) THEN
90       NULL;
91     ELSIF NOT igr_i_appl_pkg.get_pk_For_validation (
92                 new_references.person_id,
93                 new_references.enquiry_appl_number
94               ) THEN
95       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
96       igs_ge_msg_stack.add;
97       app_exception.raise_exception;
98     END IF;
99 
100   END check_parent_existance;
101 
102 
103   FUNCTION get_pk_for_validation (
104     x_sales_lead_line_id                IN     NUMBER
105   ) RETURN BOOLEAN AS
106   /*
107   ||  Created By : hreddych
108   ||  Created On : 22-JAN-2003
109   ||  Purpose : Validates the Primary Key of the table.
110   ||  Known limitations, enhancements or remarks :
111   ||  Change History :
112   ||  Who             When            What
113   ||  (reverse chronological order - newest change first)
114   */
115     CURSOR cur_rowid IS
116       SELECT   rowid
117       FROM     igr_i_a_lines
118       WHERE    sales_lead_line_id = x_sales_lead_line_id
119       FOR UPDATE NOWAIT;
120 
121     lv_rowid cur_rowid%RowType;
122 
123   BEGIN
124 
125     OPEN cur_rowid;
126     FETCH cur_rowid INTO lv_rowid;
127     IF (cur_rowid%FOUND) THEN
128       CLOSE cur_rowid;
129       RETURN(TRUE);
130     ELSE
131       CLOSE cur_rowid;
132       RETURN(FALSE);
133     END IF;
134 
135   END get_pk_for_validation;
136 
137 
138   PROCEDURE get_fk_igr_i_appl (
139     x_person_id                         IN     NUMBER,
140     x_enquiry_appl_number               IN     NUMBER
141   ) AS
142   /*
143   ||  Created By : hreddych
144   ||  Created On : 22-JAN-2003
145   ||  Purpose : Validates the Foreign Keys for the table.
146   ||  Known limitations, enhancements or remarks :
147   ||  Change History :
148   ||  Who             When            What
149   ||  (reverse chronological order - newest change first)
150   */
151     CURSOR cur_rowid IS
152       SELECT   rowid
153       FROM     igr_i_a_lines
154       WHERE   ((enquiry_appl_number = x_enquiry_appl_number) AND
155                (person_id = x_person_id));
156 
157     lv_rowid cur_rowid%RowType;
158 
159   BEGIN
160 
161     OPEN cur_rowid;
162     FETCH cur_rowid INTO lv_rowid;
163     IF (cur_rowid%FOUND) THEN
164       CLOSE cur_rowid;
165       fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
166       igs_ge_msg_stack.add;
167       app_exception.raise_exception;
168       RETURN;
169     END IF;
170     CLOSE cur_rowid;
171 
172   END get_fk_igr_i_appl;
173 
174 
175   PROCEDURE before_dml (
176     p_action                            IN     VARCHAR2,
177     x_rowid                             IN     VARCHAR2,
178     x_person_id                         IN     NUMBER,
179     x_enquiry_appl_number               IN     NUMBER,
180     x_sales_lead_line_id                IN     NUMBER,
181     x_preference                        IN     NUMBER,
182     x_creation_date                     IN     DATE,
183     x_created_by                        IN     NUMBER,
184     x_last_update_date                  IN     DATE,
185     x_last_updated_by                   IN     NUMBER,
186     x_last_update_login                 IN     NUMBER
187   ) AS
188   /*
189   ||  Created By : hreddych
190   ||  Created On : 22-JAN-2003
191   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
192   ||            Trigger Handlers for the table, before any DML operation.
193   ||  Known limitations, enhancements or remarks :
194   ||  Change History :
195   ||  Who             When            What
196   ||  (reverse chronological order - newest change first)
197   */
198   BEGIN
199 
200     set_column_values (
201       p_action,
202       x_rowid,
203       x_person_id,
204       x_enquiry_appl_number,
205       x_sales_lead_line_id,
206       x_preference,
207       x_creation_date,
208       x_created_by,
209       x_last_update_date,
210       x_last_updated_by,
211       x_last_update_login
212     );
213 
214     IF (p_action = 'INSERT') THEN
215       -- Call all the procedures related to Before Insert.
216       IF ( get_pk_for_validation(
217              new_references.sales_lead_line_id
218            )
219          ) THEN
220         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
221         igs_ge_msg_stack.add;
222         app_exception.raise_exception;
223       END IF;
224       check_parent_existance;
225     ELSIF (p_action = 'UPDATE') THEN
226       -- Call all the procedures related to Before Update.
227       check_parent_existance;
228     ELSIF (p_action = 'VALIDATE_INSERT') THEN
229       -- Call all the procedures related to Before Insert.
230       IF ( get_pk_for_validation (
231              new_references.sales_lead_line_id
232            )
233          ) THEN
234         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
235         igs_ge_msg_stack.add;
236         app_exception.raise_exception;
237       END IF;
238     END IF;
239 
240   END before_dml;
241 
242 
243   PROCEDURE insert_row (
244     x_rowid                             IN OUT NOCOPY VARCHAR2,
245     x_person_id                         IN     NUMBER,
246     x_enquiry_appl_number               IN     NUMBER,
247     x_sales_lead_line_id                IN     NUMBER,
248     x_preference                        IN     NUMBER,
249     x_mode                              IN     VARCHAR2
250   ) AS
251   /*
252   ||  Created By : hreddych
253   ||  Created On : 22-JAN-2003
254   ||  Purpose : Handles the INSERT DML logic for the table.
255   ||  Known limitations, enhancements or remarks :
256   ||  Change History :
257   ||  Who             When            What
258   ||  (reverse chronological order - newest change first)
259   */
260 
261     x_last_update_date           DATE;
262     x_last_updated_by            NUMBER;
263     x_last_update_login          NUMBER;
264     x_request_id                 NUMBER;
265     x_program_id                 NUMBER;
266     x_program_application_id     NUMBER;
267     x_program_update_date        DATE;
268 
269   BEGIN
270 
271     x_last_update_date := SYSDATE;
272     IF (x_mode = 'I') THEN
273       x_last_updated_by := 1;
274       x_last_update_login := 0;
275     ELSIF (x_mode = 'R') THEN
276       x_last_updated_by := fnd_global.user_id;
277       IF (x_last_updated_by IS NULL) THEN
278         x_last_updated_by := -1;
279       END IF;
280       x_last_update_login := fnd_global.login_id;
281       IF (x_last_update_login IS NULL) THEN
282         x_last_update_login := -1;
283       END IF;
284       x_request_id             := fnd_global.conc_request_id;
285       x_program_id             := fnd_global.conc_program_id;
286       x_program_application_id := fnd_global.prog_appl_id;
287 
288       IF (x_request_id = -1) THEN
289         x_request_id             := NULL;
290         x_program_id             := NULL;
291         x_program_application_id := NULL;
292         x_program_update_date    := NULL;
293       ELSE
294         x_program_update_date    := SYSDATE;
295       END IF;
296     ELSE
297       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
298       fnd_message.set_token ('ROUTINE', 'IGR_I_A_LINES_PKG.INSERT_ROW');
299       igs_ge_msg_stack.add;
300       app_exception.raise_exception;
301     END IF;
302 
303     before_dml(
304       p_action                            => 'INSERT',
305       x_rowid                             => x_rowid,
306       x_person_id                         => x_person_id,
307       x_enquiry_appl_number               => x_enquiry_appl_number,
308       x_sales_lead_line_id                => x_sales_lead_line_id,
309       x_preference                        => x_preference,
310       x_creation_date                     => x_last_update_date,
311       x_created_by                        => x_last_updated_by,
312       x_last_update_date                  => x_last_update_date,
313       x_last_updated_by                   => x_last_updated_by,
314       x_last_update_login                 => x_last_update_login
315     );
316 
317     INSERT INTO igr_i_a_lines (
318       person_id,
319       enquiry_appl_number,
320       sales_lead_line_id,
321       preference,
322       creation_date,
323       created_by,
324       last_update_date,
325       last_updated_by,
326       last_update_login,
327       request_id,
328       program_id,
329       program_application_id,
330       program_update_date
331     ) VALUES (
332       new_references.person_id,
333       new_references.enquiry_appl_number,
334       new_references.sales_lead_line_id,
335       new_references.preference,
336       x_last_update_date,
337       x_last_updated_by,
338       x_last_update_date,
339       x_last_updated_by,
340       x_last_update_login ,
341       x_request_id,
342       x_program_id,
343       x_program_application_id,
344       x_program_update_date
345     ) RETURNING ROWID INTO x_rowid;
346 
347   END insert_row;
348 
349 
350   PROCEDURE lock_row (
351     x_rowid                             IN     VARCHAR2,
352     x_person_id                         IN     NUMBER,
353     x_enquiry_appl_number               IN     NUMBER,
354     x_sales_lead_line_id                IN     NUMBER,
355     x_preference                        IN     NUMBER
356   ) AS
357   /*
358   ||  Created By : hreddych
359   ||  Created On : 22-JAN-2003
360   ||  Purpose : Handles the LOCK mechanism for the table.
361   ||  Known limitations, enhancements or remarks :
362   ||  Change History :
363   ||  Who             When            What
364   ||  (reverse chronological order - newest change first)
365   */
366     CURSOR c1 IS
367       SELECT
368         person_id,
369         enquiry_appl_number,
370         preference
371       FROM  igr_i_a_lines
372       WHERE rowid = x_rowid
373       FOR UPDATE NOWAIT;
374 
375     tlinfo c1%ROWTYPE;
376 
377   BEGIN
378 
379     OPEN c1;
380     FETCH c1 INTO tlinfo;
381     IF (c1%notfound) THEN
382       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
383       igs_ge_msg_stack.add;
384       CLOSE c1;
385       app_exception.raise_exception;
386       RETURN;
387     END IF;
388     CLOSE c1;
389 
390     IF (
391         (tlinfo.person_id = x_person_id)
392         AND (tlinfo.enquiry_appl_number = x_enquiry_appl_number)
393         AND ((tlinfo.preference = x_preference) OR ((tlinfo.preference IS NULL) AND (X_preference IS NULL)))
394        ) THEN
395       NULL;
396     ELSE
397       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
398       igs_ge_msg_stack.add;
399       app_exception.raise_exception;
400     END IF;
401 
402     RETURN;
403 
404   END lock_row;
405 
406 
407   PROCEDURE update_row (
408     x_rowid                             IN     VARCHAR2,
409     x_person_id                         IN     NUMBER,
410     x_enquiry_appl_number               IN     NUMBER,
411     x_sales_lead_line_id                IN     NUMBER,
412     x_preference                        IN     NUMBER,
413     x_mode                              IN     VARCHAR2
414   ) AS
415   /*
416   ||  Created By : hreddych
417   ||  Created On : 22-JAN-2003
418   ||  Purpose : Handles the UPDATE DML logic for the table.
419   ||  Known limitations, enhancements or remarks :
420   ||  Change History :
421   ||  Who             When            What
422   ||  (reverse chronological order - newest change first)
423   */
424     x_last_update_date           DATE ;
425     x_last_updated_by            NUMBER;
426     x_last_update_login          NUMBER;
427     x_request_id                 NUMBER;
428     x_program_id                 NUMBER;
429     x_program_application_id     NUMBER;
430     x_program_update_date        DATE;
431 
432   BEGIN
433 
434     x_last_update_date := SYSDATE;
435     IF (X_MODE = 'I') THEN
436       x_last_updated_by := 1;
437       x_last_update_login := 0;
438     ELSIF (x_mode = 'R') THEN
439       x_last_updated_by := fnd_global.user_id;
440       IF x_last_updated_by IS NULL THEN
441         x_last_updated_by := -1;
442       END IF;
443       x_last_update_login := fnd_global.login_id;
444       IF (x_last_update_login IS NULL) THEN
445         x_last_update_login := -1;
446       END IF;
447     ELSE
448       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
449       fnd_message.set_token ('ROUTINE', 'IGR_I_A_LINES_PKG.UPDATE_ROW');
450       igs_ge_msg_stack.add;
451       app_exception.raise_exception;
452     END IF;
453 
454     before_dml(
455       p_action                            => 'UPDATE',
456       x_rowid                             => x_rowid,
457       x_person_id                         => x_person_id,
458       x_enquiry_appl_number               => x_enquiry_appl_number,
459       x_sales_lead_line_id                => x_sales_lead_line_id,
460       x_preference                        => x_preference,
461       x_creation_date                     => x_last_update_date,
462       x_created_by                        => x_last_updated_by,
463       x_last_update_date                  => x_last_update_date,
464       x_last_updated_by                   => x_last_updated_by,
465       x_last_update_login                 => x_last_update_login
466     );
467 
468     IF (x_mode = 'R') THEN
469       x_request_id := fnd_global.conc_request_id;
470       x_program_id := fnd_global.conc_program_id;
471       x_program_application_id := fnd_global.prog_appl_id;
472       IF (x_request_id =  -1) THEN
473         x_request_id := old_references.request_id;
474         x_program_id := old_references.program_id;
475         x_program_application_id := old_references.program_application_id;
476         x_program_update_date := old_references.program_update_date;
477       ELSE
478         x_program_update_date := SYSDATE;
479       END IF;
480     END IF;
481 
482     UPDATE igr_i_a_lines
483       SET
484         person_id                         = new_references.person_id,
485         enquiry_appl_number               = new_references.enquiry_appl_number,
486         preference                        = new_references.preference,
487         last_update_date                  = x_last_update_date,
488         last_updated_by                   = x_last_updated_by,
489         last_update_login                 = x_last_update_login ,
490         request_id                        = x_request_id,
491         program_id                        = x_program_id,
492         program_application_id            = x_program_application_id,
493         program_update_date               = x_program_update_date
494       WHERE rowid = x_rowid;
495 
496     IF (SQL%NOTFOUND) THEN
497       RAISE NO_DATA_FOUND;
498     END IF;
499 
500   END update_row;
501 
502 
503   PROCEDURE add_row (
504     x_rowid                             IN OUT NOCOPY VARCHAR2,
505     x_person_id                         IN     NUMBER,
506     x_enquiry_appl_number               IN     NUMBER,
507     x_sales_lead_line_id                IN     NUMBER,
508     x_preference                        IN     NUMBER,
509     x_mode                              IN     VARCHAR2
510   ) AS
511   /*
512   ||  Created By : hreddych
513   ||  Created On : 22-JAN-2003
514   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
515   ||  Known limitations, enhancements or remarks :
516   ||  Change History :
517   ||  Who             When            What
518   ||  (reverse chronological order - newest change first)
519   */
520     CURSOR c1 IS
521       SELECT   rowid
522       FROM     igr_i_a_lines
523       WHERE    sales_lead_line_id                = x_sales_lead_line_id;
524 
525   BEGIN
526 
527     OPEN c1;
528     FETCH c1 INTO x_rowid;
529     IF (c1%NOTFOUND) THEN
530       CLOSE c1;
531 
532       insert_row (
533         x_rowid,
534         x_person_id,
535         x_enquiry_appl_number,
536         x_sales_lead_line_id,
537         x_preference,
538         x_mode
539       );
540       RETURN;
541     END IF;
542     CLOSE c1;
543 
544     update_row (
545       x_rowid,
546       x_person_id,
547       x_enquiry_appl_number,
548       x_sales_lead_line_id,
549       x_preference,
550       x_mode
551     );
552 
553   END add_row;
554 
555 
556   PROCEDURE delete_row (
557     x_rowid IN VARCHAR2
558   ) AS
559   /*
560   ||  Created By : hreddych
561   ||  Created On : 22-JAN-2003
562   ||  Purpose : Handles the DELETE DML logic for the table.
563   ||  Known limitations, enhancements or remarks :
564   ||  Change History :
565   ||  Who             When            What
566   ||  (reverse chronological order - newest change first)
567   */
568   BEGIN
569 
570     before_dml (
571       p_action => 'DELETE',
572       x_rowid => x_rowid
573     );
574 
575     DELETE FROM igr_i_a_lines
576     WHERE rowid = x_rowid;
577 
578     IF (SQL%NOTFOUND) THEN
579       RAISE NO_DATA_FOUND;
580     END IF;
581 
582   END delete_row;
583 
584 
585 END igr_i_a_lines_pkg;