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