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