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