[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