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