DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_SC_PER_ATTR_VALS_PKG

Source


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