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