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_PE_HZ_LOCATIONS_PKG

Source


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