1 PACKAGE BODY igs_pe_per_type_map_pkg AS
2 /* $Header: IGSNIA4B.pls 120.1 2006/01/18 22:44:10 skpandey noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_per_type_map%ROWTYPE;
6 new_references igs_pe_per_type_map%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_person_type_code IN VARCHAR2,
12 x_system_type IN VARCHAR2,
13 x_per_person_type_id IN NUMBER,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) AS
20 /*
21 || Created By : prabhat.patel
22 || Created On : 05-NOV-2002
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 igs_pe_per_type_map
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.person_type_code := x_person_type_code;
54 new_references.system_type := x_system_type;
55 new_references.per_person_type_id := x_per_person_type_id;
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 PROCEDURE check_parent_existance AS
72 /*
73 || Created By : prabhat.patel
74 || Created On : 05-NOV-2002
75 || Purpose : Checks for the existance of Parent records.
76 || Known limitations, enhancements or remarks :
77 || Change History :
78 || Who When What
79 || (reverse chronological order - newest change first)
80 */
81
82 CURSOR person_type_id_cur IS
83 SELECT 'X'
84 FROM per_person_types
85 WHERE person_type_id = new_references.per_person_type_id;
86
87 l_exists VARCHAR2(1);
88 BEGIN
89
90 IF (((old_references.person_type_code = new_references.person_type_code)) OR
91 ((new_references.person_type_code IS NULL))) THEN
92 NULL;
93 ELSIF NOT igs_pe_person_types_pkg.get_pk_for_validation (
94 new_references.person_type_code
95 ) THEN
96 FND_MESSAGE.SET_NAME ('FND','FORM_RECORD_DELETED');
97 IGS_GE_MSG_STACK.ADD;
98 APP_EXCEPTION.RAISE_EXCEPTION;
99 END IF;
100
101 IF (((old_references.per_person_type_id = new_references.per_person_type_id)) OR
102 ((new_references.per_person_type_id IS NULL))) THEN
103 NULL;
104 ELSE
105 OPEN person_type_id_cur;
106 FETCH person_type_id_cur INTO l_exists;
107 IF person_type_id_cur%NOTFOUND THEN
108 CLOSE person_type_id_cur;
109 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
110 IGS_GE_MSG_STACK.ADD;
111 APP_EXCEPTION.RAISE_EXCEPTION;
112 END IF;
113 CLOSE person_type_id_cur;
114 END IF;
115
116 IF (((old_references.system_type = new_references.system_type)) OR
117 ((new_references.system_type IS NULL))) THEN
118 NULL;
119 ELSE
120 IF NOT igs_lookups_view_pkg.get_pk_for_validation('SYSTEM_PERSON_TYPES',new_references.system_type) THEN
121 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
122 IGS_GE_MSG_STACK.ADD;
123 APP_EXCEPTION.RAISE_EXCEPTION;
124 END IF;
125 END IF;
126
127 END check_parent_existance;
128
129 FUNCTION get_pk_for_validation (
130 x_person_type_code IN VARCHAR2,
131 x_per_person_type_id IN NUMBER
132 ) RETURN BOOLEAN AS
133 /*
134 || Created By : prabhat.patel
135 || Created On : 05-NOV-2002
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_per_type_map
145 WHERE person_type_code = x_person_type_code
146 AND per_person_type_id = x_per_person_type_id
147 FOR UPDATE NOWAIT;
148
149 lv_rowid cur_rowid%RowType;
150
151 BEGIN
152
153 OPEN cur_rowid;
154 FETCH cur_rowid INTO lv_rowid;
155 IF (cur_rowid%FOUND) THEN
156 CLOSE cur_rowid;
157 RETURN(TRUE);
158 ELSE
159 CLOSE cur_rowid;
160 RETURN(FALSE);
161 END IF;
162
163 END get_pk_for_validation;
164
165
166 PROCEDURE before_dml (
167 p_action IN VARCHAR2,
168 x_rowid IN VARCHAR2,
169 x_person_type_code IN VARCHAR2,
170 x_system_type IN VARCHAR2,
171 x_per_person_type_id IN NUMBER,
172 x_creation_date IN DATE,
173 x_created_by IN NUMBER,
174 x_last_update_date IN DATE,
175 x_last_updated_by IN NUMBER,
176 x_last_update_login IN NUMBER
177 ) AS
178 /*
179 || Created By : prabhat.patel
180 || Created On : 05-NOV-2002
181 || Purpose : Initialises the columns, Checks Constraints, Calls the
182 || Trigger Handlers for the table, before any DML operation.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 */
188 BEGIN
189
190 set_column_values (
191 p_action,
192 x_rowid,
193 x_person_type_code,
194 x_system_type,
195 x_per_person_type_id,
196 x_creation_date,
197 x_created_by,
198 x_last_update_date,
199 x_last_updated_by,
200 x_last_update_login
201 );
202
203 IF (p_action = 'INSERT') THEN
204 -- Call all the procedures related to Before Insert.
205
206 IF ( get_pk_for_validation(
207 new_references.person_type_code,
208 new_references.per_person_type_id
209 )
210 ) THEN
211 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
212 igs_ge_msg_stack.add;
213 app_exception.raise_exception;
214 END IF;
215
216 check_parent_existance;
217
218 ELSIF (p_action = 'VALIDATE_INSERT') THEN
219 -- Call all the procedures related to Before Insert.
220 IF ( get_pk_for_validation (
221 new_references.person_type_code,
222 new_references.per_person_type_id
223 )
224 ) THEN
225 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
226 igs_ge_msg_stack.add;
227 app_exception.raise_exception;
228 END IF;
229
230 check_parent_existance;
231
232 END IF;
233
234 END before_dml;
235
236
237 PROCEDURE afterinsert (
238 x_system_type IN VARCHAR2 )
239 AS
240 /*
241 || Created By : ssawhney
242 || Created On : 29nov
243 || Purpose : Handles the After insert logic, we need to end date person type instances in OSS after the mapping is done.
244 || Known limitations, enhancements or remarks :
245 || Change History :
246 || Who When What
247 || (reverse chronological order - newest change first)
248 */
249
250 CURSOR person_type_cur(cp_system_type igs_pe_person_types.system_type%TYPE) IS
251 SELECT ti.rowid,ti.*
252 FROM igs_pe_typ_instances_all ti,
253 igs_pe_person_types pt,
254 per_all_people_f ppf
255 WHERE ppf.party_id = ti.person_id AND
256 ti.person_type_code = pt.person_type_code AND
257 pt.system_type = cp_system_type AND
258 (ti.end_date IS NULL OR (SYSDATE BETWEEN ti.start_date AND ti.end_date))
259 ;
260 --skpandey, Bug#4937960: Changed staff_exist cursor definition to optimize query
261 CURSOR staff_exist (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
262 SELECT '1'
263 FROM igs_pe_typ_instances
264 WHERE person_id = cp_person_id
265 AND system_type = 'STAFF'
266 AND (end_date IS NULL OR (SYSDATE BETWEEN start_date AND end_date));
267
268
269 CURSOR ss_cur (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
270 SELECT ti.rowid,ti.*
271 FROM igs_pe_typ_instances_all ti,
272 igs_pe_person_types pt
273 WHERE ti.person_type_code = pt.person_type_code AND
274 pt.system_type = 'SS_ENROLL_STAFF' AND
275 ti.person_id = cp_person_id;
276
277 exist_rec staff_exist%ROWTYPE;
278 ss_rec ss_cur%ROWTYPE;
279 person_type_rec person_type_cur%ROWTYPE;
280 l_staff NUMBER := 0;
281
282 BEGIN
283
284
285 OPEN person_type_cur(x_system_type);
286 LOOP
287 FETCH person_type_cur into person_type_rec;
288 EXIT WHEN person_type_cur%NOTFOUND;
289
290 igs_pe_typ_instances_pkg.update_row
291 (
292 x_rowid => person_type_rec.rowid,
293 x_person_id => person_type_rec.person_id,
294 x_course_cd => person_type_rec.course_cd,
295 x_type_instance_id => person_type_rec.type_instance_id,
296 x_person_type_code => person_type_rec.person_type_code,
297 x_cc_version_number => person_type_rec.cc_version_number,
298 x_funnel_status => person_type_rec.funnel_status,
299 x_admission_appl_number=> person_type_rec.admission_appl_number,
300 x_nominated_course_cd => person_type_rec.nominated_course_cd,
301 x_ncc_version_number => person_type_rec.ncc_version_number,
302 x_sequence_number => person_type_rec.sequence_number,
303 x_start_date => person_type_rec.start_date,
304 x_end_date => TRUNC(sysdate),
305 x_create_method => person_type_rec.create_method,
306 x_ended_by => person_type_rec.ended_by,
307 x_end_method => 'END_MANUAL',
308 x_emplmnt_category_code=> person_type_rec.emplmnt_category_code);
309
310 IF x_system_type ='STAFF' THEN
311 -- if the system tye is now not a staff, then we need to validate and close the SS staff type instance IF present also.
312
313 OPEN staff_exist(person_type_rec.person_id);
314 FETCH staff_exist INTO exist_rec;
315 IF staff_exist%NOTFOUND THEN
316
317 OPEN ss_cur (person_type_rec.person_id);
318
319 -- there can be more than one mapping;
320 LOOP
321
322 FETCH ss_cur INTO ss_rec;
323 EXIT WHEN ss_cur%NOTFOUND;
324
325 igs_pe_typ_instances_pkg.update_row
326 (
327 x_rowid => ss_rec.rowid,
328 x_person_id => ss_rec.person_id,
329 x_course_cd => ss_rec.course_cd,
330 x_type_instance_id => ss_rec.type_instance_id,
331 x_person_type_code => ss_rec.person_type_code,
332 x_cc_version_number => ss_rec.cc_version_number,
333 x_funnel_status => ss_rec.funnel_status,
334 x_admission_appl_number=> ss_rec.admission_appl_number,
335 x_nominated_course_cd => ss_rec.nominated_course_cd,
336 x_ncc_version_number => ss_rec.ncc_version_number,
337 x_sequence_number => ss_rec.sequence_number,
338 x_start_date => ss_rec.start_date,
339 x_end_date => TRUNC(sysdate),
340 x_create_method => ss_rec.create_method,
341 x_ended_by => ss_rec.ended_by,
342 x_end_method => 'END_MANUAL',
343 x_emplmnt_category_code=> ss_rec.emplmnt_category_code);
344
345 END LOOP;
346 IF ss_cur%ISOPEN THEN
347 CLOSE ss_cur;
348 END IF;
349
350 END IF;
351 IF staff_exist%ISOPEN THEN
352 CLOSE staff_exist;
353 END IF;
354 END IF; -- staff
355 END LOOP;
356 IF person_type_cur%ISOPEN THEN
357 CLOSE person_type_cur;
358 END IF;
359
360 END afterinsert;
361
362 PROCEDURE after_dml (
363 p_action IN VARCHAR2,
364 x_rowid IN VARCHAR2
365 ) IS
366 /*************************************************************
367 Created By : ssawhney
368 Date Created By : 2000/13/05
369 Purpose : To validate the fields after doing the DML operation.
370 Know limitations, enhancements or remarks
371 Change History
372 Who When What
373
374 (reverse chronological order - newest change first)
375 ***************************************************************/
376
377 BEGIN
378
379 l_rowid := x_rowid;
380
381 IF (p_action = 'INSERT') THEN
382 -- Call all the procedures related to After Insert.
383 afterinsert(new_references.system_type);
384 ELSIF (p_action = 'UPDATE') THEN
385 -- Call all the procedures related to After Update.
386 Null;
387 ELSIF (p_action = 'DELETE') THEN
388 -- Call all the procedures related to After Delete.
389 Null;
390 END IF;
391
392 END after_dml;
393
394 PROCEDURE insert_row (
395 x_rowid IN OUT NOCOPY VARCHAR2,
396 x_person_type_code IN VARCHAR2,
397 x_system_type IN VARCHAR2,
398 x_per_person_type_id IN NUMBER,
399 x_mode IN VARCHAR2
400 ) AS
401 /*
402 || Created By : prabhat.patel
403 || Created On : 05-NOV-2002
404 || Purpose : Handles the INSERT DML logic for the table.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410
411 x_last_update_date DATE;
412 x_last_updated_by NUMBER;
413 x_last_update_login NUMBER;
414
415 BEGIN
416
417 x_last_update_date := SYSDATE;
418 IF (x_mode = 'I') THEN
419 x_last_updated_by := 1;
420 x_last_update_login := 0;
421 ELSIF (x_mode = 'R') THEN
422 x_last_updated_by := fnd_global.user_id;
423 IF (x_last_updated_by IS NULL) THEN
424 x_last_updated_by := -1;
425 END IF;
426 x_last_update_login := fnd_global.login_id;
427 IF (x_last_update_login IS NULL) THEN
428 x_last_update_login := -1;
429 END IF;
430 ELSE
431 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
432 igs_ge_msg_stack.add;
433 app_exception.raise_exception;
434 END IF;
435
436 before_dml(
437 p_action => 'INSERT',
438 x_rowid => x_rowid,
439 x_person_type_code => x_person_type_code,
440 x_system_type => x_system_type,
441 x_per_person_type_id => x_per_person_type_id,
442 x_creation_date => x_last_update_date,
443 x_created_by => x_last_updated_by,
444 x_last_update_date => x_last_update_date,
445 x_last_updated_by => x_last_updated_by,
446 x_last_update_login => x_last_update_login
447 );
448
449 INSERT INTO igs_pe_per_type_map (
450 person_type_code,
451 system_type,
452 per_person_type_id,
453 creation_date,
454 created_by,
455 last_update_date,
456 last_updated_by,
457 last_update_login
458 ) VALUES (
459 new_references.person_type_code,
460 new_references.system_type,
461 new_references.per_person_type_id,
462 x_last_update_date,
463 x_last_updated_by,
464 x_last_update_date,
465 x_last_updated_by,
466 x_last_update_login
467 ) RETURNING ROWID INTO x_rowid;
468
469
470 After_DML (
471 p_action => 'INSERT' ,
472 x_rowid => X_ROWID );
473
474 END insert_row;
475
476
477 PROCEDURE lock_row (
478 x_rowid IN VARCHAR2,
479 x_person_type_code IN VARCHAR2,
480 x_system_type IN VARCHAR2,
481 x_per_person_type_id IN NUMBER
482 ) AS
483 /*
484 || Created By : prabhat.patel
485 || Created On : 05-NOV-2002
486 || Purpose : Handles the LOCK mechanism for the table.
487 || Known limitations, enhancements or remarks :
488 || Change History :
489 || Who When What
490 || (reverse chronological order - newest change first)
491 */
492 CURSOR c1 IS
493 SELECT
494 system_type
495 FROM igs_pe_per_type_map
496 WHERE rowid = x_rowid
497 FOR UPDATE NOWAIT;
498
499 tlinfo c1%ROWTYPE;
500
501 BEGIN
502
503 OPEN c1;
504 FETCH c1 INTO tlinfo;
505 IF (c1%notfound) THEN
506 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
507 igs_ge_msg_stack.add;
508 CLOSE c1;
509 app_exception.raise_exception;
510 RETURN;
511 END IF;
512 CLOSE c1;
513
514 IF (
515 (tlinfo.system_type = x_system_type)
516 ) THEN
517 NULL;
518 ELSE
519 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
520 igs_ge_msg_stack.add;
521 app_exception.raise_exception;
522 END IF;
523
524 RETURN;
525
526 END lock_row;
527
528
529 PROCEDURE update_row (
530 x_rowid IN VARCHAR2,
531 x_person_type_code IN VARCHAR2,
532 x_system_type IN VARCHAR2,
533 x_per_person_type_id IN NUMBER,
534 x_mode IN VARCHAR2
535 ) AS
536 /*
537 || Created By : prabhat.patel
538 || Created On : 05-NOV-2002
539 || Purpose : Handles the UPDATE DML logic for the table.
540 || Known limitations, enhancements or remarks :
541 || Change History :
542 || Who When What
543 || (reverse chronological order - newest change first)
544 */
545 x_last_update_date DATE ;
546 x_last_updated_by NUMBER;
547 x_last_update_login NUMBER;
548
549 BEGIN
550
551 x_last_update_date := SYSDATE;
552 IF (X_MODE = 'I') THEN
553 x_last_updated_by := 1;
554 x_last_update_login := 0;
555 ELSIF (x_mode = 'R') THEN
556 x_last_updated_by := fnd_global.user_id;
557 IF x_last_updated_by IS NULL THEN
558 x_last_updated_by := -1;
559 END IF;
560 x_last_update_login := fnd_global.login_id;
561 IF (x_last_update_login IS NULL) THEN
562 x_last_update_login := -1;
563 END IF;
564 ELSE
565 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
566 igs_ge_msg_stack.add;
567 app_exception.raise_exception;
568 END IF;
569
570 before_dml(
571 p_action => 'UPDATE',
572 x_rowid => x_rowid,
573 x_person_type_code => x_person_type_code,
574 x_system_type => x_system_type,
575 x_per_person_type_id => x_per_person_type_id,
576 x_creation_date => x_last_update_date,
577 x_created_by => x_last_updated_by,
578 x_last_update_date => x_last_update_date,
579 x_last_updated_by => x_last_updated_by,
580 x_last_update_login => x_last_update_login
581 );
582
583 UPDATE igs_pe_per_type_map
584 SET
585 system_type = new_references.system_type,
586 last_update_date = x_last_update_date,
587 last_updated_by = x_last_updated_by,
588 last_update_login = x_last_update_login
589 WHERE rowid = x_rowid;
590
591 IF (SQL%NOTFOUND) THEN
592 RAISE NO_DATA_FOUND;
593 END IF;
594
595 END update_row;
596
597
598 PROCEDURE add_row (
599 x_rowid IN OUT NOCOPY VARCHAR2,
600 x_person_type_code IN VARCHAR2,
601 x_system_type IN VARCHAR2,
602 x_per_person_type_id IN NUMBER,
603 x_mode IN VARCHAR2
604 ) AS
605 /*
606 || Created By : prabhat.patel
607 || Created On : 05-NOV-2002
608 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
609 || Known limitations, enhancements or remarks :
610 || Change History :
611 || Who When What
612 || (reverse chronological order - newest change first)
613 */
614 CURSOR c1 IS
615 SELECT rowid
616 FROM igs_pe_per_type_map
617 WHERE person_type_code = x_person_type_code
618 AND per_person_type_id = x_per_person_type_id;
619
620 BEGIN
621
622 OPEN c1;
623 FETCH c1 INTO x_rowid;
624 IF (c1%NOTFOUND) THEN
625 CLOSE c1;
626
627 insert_row (
628 x_rowid,
629 x_person_type_code,
630 x_system_type,
631 x_per_person_type_id,
632 x_mode
633 );
634 RETURN;
635 END IF;
636 CLOSE c1;
637
638 update_row (
639 x_rowid,
640 x_person_type_code,
641 x_system_type,
642 x_per_person_type_id,
643 x_mode
644 );
645
646 END add_row;
647
648
649 PROCEDURE delete_row (
650 x_rowid IN VARCHAR2
651 ) AS
652 /*
653 || Created By : prabhat.patel
654 || Created On : 05-NOV-2002
655 || Purpose : Handles the DELETE DML logic for the table.
656 || Known limitations, enhancements or remarks :
657 || Change History :
658 || Who When What
659 || (reverse chronological order - newest change first)
660 */
661 BEGIN
662
663 before_dml (
664 p_action => 'DELETE',
665 x_rowid => x_rowid
666 );
667
668 DELETE FROM igs_pe_per_type_map
669 WHERE rowid = x_rowid;
670
671 IF (SQL%NOTFOUND) THEN
672 RAISE NO_DATA_FOUND;
673 END IF;
674
675 END delete_row;
676
677
678 END igs_pe_per_type_map_pkg;
[Home] [Help]