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_TR_STP_CTL_TYPE_PKG

Source


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