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_AD_APCTR_RU_PKG

Source


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