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