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