1 PACKAGE BODY igs_pe_typ_instances_pkg AS
2 /* $Header: IGSNI46B.pls 120.12 2006/07/12 12:17:22 vskumar ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_pe_typ_instances_all%RowType;
5 new_references igs_pe_typ_instances_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_person_id IN NUMBER,
11 x_course_cd IN VARCHAR2,
12 x_type_instance_id IN NUMBER ,
13 x_person_type_code IN VARCHAR2 ,
14 x_cc_version_number IN NUMBER ,
15 x_funnel_status IN VARCHAR2 ,
16 x_admission_appl_number IN NUMBER ,
17 x_nominated_course_cd IN VARCHAR2 ,
18 x_ncc_version_number IN NUMBER ,
19 x_sequence_number IN NUMBER,
20 x_start_date IN DATE ,
21 x_end_date IN DATE ,
22 x_create_method IN VARCHAR2 ,
23 x_ended_by IN NUMBER ,
24 x_end_method IN VARCHAR2 ,
25 x_creation_date IN DATE ,
26 x_created_by IN NUMBER ,
27 x_last_update_date IN DATE ,
28 x_last_updated_by IN NUMBER ,
29 x_last_update_login IN NUMBER,
30 x_org_id IN NUMBER ,
31 x_emplmnt_category_code IN VARCHAR2
32 ) AS
33
34 /*************************************************************
35 Created By :
36 Date Created By :
37 Purpose :
38 Know limitations, enhancements or remarks
39 Change History
40 Who When What
41
42 (reverse chronological order - newest change first)
43 ***************************************************************/
44
45 CURSOR cur_old_ref_values IS
46 SELECT *
47 FROM IGS_PE_TYP_INSTANCES_ALL
48 WHERE rowid = x_rowid;
49
50 BEGIN
51
52 l_rowid := x_rowid;
53
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 Open cur_old_ref_values;
57 Fetch cur_old_ref_values INTO old_references;
58 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
59 Close cur_old_ref_values;
60 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
61 IGS_GE_MSG_STACK.ADD;
62 App_Exception.Raise_Exception;
63 Return;
64 END IF;
65 Close cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.person_id := x_person_id;
69 new_references.course_cd := x_course_cd;
70 new_references.type_instance_id := x_type_instance_id;
71 new_references.person_type_code := x_person_type_code;
72 new_references.cc_version_number := x_cc_version_number;
73 new_references.funnel_status := x_funnel_status;
74 new_references.admission_appl_number := x_admission_appl_number;
75 new_references.nominated_course_cd := x_nominated_course_cd;
76 new_references.ncc_version_number := x_ncc_version_number;
77 new_references.sequence_number := x_sequence_number;
78 new_references.start_date := trunc(x_start_date);
79 new_references.end_date := trunc(x_end_date);
80 new_references.create_method := x_create_method;
81 new_references.ended_by := x_ended_by;
82 new_references.end_method := x_end_method;
83 IF (p_action = 'UPDATE') THEN
84 new_references.creation_date := old_references.creation_date;
85 new_references.created_by := old_references.created_by;
86 ELSE
87 new_references.creation_date := x_creation_date;
88 new_references.created_by := x_created_by;
89 END IF;
90 new_references.last_update_date := x_last_update_date;
91 new_references.last_updated_by := x_last_updated_by;
92 new_references.last_update_login := x_last_update_login;
93 new_references.org_id := x_org_id;
94 new_references.emplmnt_category_code := x_emplmnt_category_code;
95
96 END Set_Column_Values;
97
98 PROCEDURE After_Insert_update AS
99
100 /*************************************************************
101 Created By :
102 Date Created By :
103 Purpose :
104 Know limitations, enhancements or remarks
105 Change History
106 Who When What
107 pkpatel 25-APR-2003 Bug 2908851
108 Tuned the cursors c_get_others and c_get_active. Removed the unnecessary join with
109 igs_lookups_view and changed igs_pe_typ_instances to igs_pe_typ_instances_all
110 pkpatel 4-MAY-2003 Bug 2989307
111 Removed the existence of active records for person types other than OTHER
112 (reverse chronological order - newest change first)
113 ***************************************************************/
114
115 CURSOR c_get_others(cp_system_type igs_pe_person_types.system_type%TYPE) IS
116 SELECT pti.rowid
117 FROM igs_pe_typ_instances_all pti,
118 igs_pe_person_types pt
119 WHERE pt.system_type = cp_system_type --'OTHER'
120 AND pti.person_type_code = pt.person_type_code
121 AND pti.person_id = new_references.person_id;
122
123 CURSOR c_get_active(cp_system_type igs_pe_person_types.system_type%TYPE)IS
124 SELECT pti.type_instance_id
125 FROM igs_pe_typ_instances_all pti,
126 igs_pe_person_types pt
127 WHERE pt.system_type <> cp_system_type --'OTHER'
128 AND pti.person_type_code = pt.person_type_code
129 AND pti.person_id = new_references.person_id;
130
131 lv_rowid VARCHAR2(25);
132 lv_typ_inst_id IGS_PE_TYP_INSTANCES.TYPE_INSTANCE_ID%TYPE;
133
134 BEGIN
135
136 OPEN c_get_others('OTHER');
137 FETCH c_get_others into lv_rowid;
138 IF c_get_others%FOUND THEN
139
140 OPEN c_get_active('OTHER');
141 FETCH c_get_active into lv_typ_inst_id;
142 IF c_get_active%FOUND THEN
143 delete_row(lv_rowid);
144 END IF;
145 CLOSE c_get_active;
146
147 END IF;
148 CLOSE c_get_others;
149
150 END After_Insert_update;
151
152 PROCEDURE checkprospectevaluator(
153 p_person_id IN HZ_PARTIES.party_id%TYPE,
154 p_person_type_code IN igs_pe_typ_instances_all.person_type_code%TYPE
155 ) AS
156 /*************************************************************
157 Created By : Nilotpal Shee
158 Date Created By : 05-dec-2001
159 Purpose : see the comments above
160 Know limitations, enhancements or remarks
161 Change History
162 Who When What
163 pkpatel 20-sep-2004 Bug 3690856 (Tuned the cursors. Used a single cursor pros_eval_cur and referred the table instead of view)
164 mesriniv 18-FEB-2002 Modified the >= to > in cursors
165 c_prospect_exist,c_evaluator_exist
166 Bug:2203778 SWCR001 Person CCR
167
168 nshee 05-dec-2001 see comments below
169 -- This procedure has been added during Evaluate Applicant Qualifications
170 -- and make decision DLD (Bug#2097333). This checks whether a valid prospect/evaluator exists
171 -- for a Person and hence restricts insertion of person type of evaluator/prospect respectively.
172 -- This is called in Before_DML. On finding records it will throw up respective error
173 -- messages which will be trapped wherever this API is called from and the error handling
174 -- will be done depending on the error message returned
175 (reverse chronological order - newest change first)
176 ***************************************************************/
177 CURSOR c_prospect_evaluator(l_person_type_code igs_pe_typ_instances_all.person_type_code%TYPE) IS
178 SELECT ppt.system_type
179 FROM igs_pe_person_types ppt
180 WHERE ppt.person_type_code = l_person_type_code;
181 l_system_type IGS_PE_PERSON_TYPES.system_type%TYPE;
182
183 --This Cursor makes sure that a Person Can have a Person Type
184 --Evaluator created from FORM only if the existing Prospect Person Type is End Dated with Sysdate or
185 --less than Sysdate. And vice versa..
186 --Existing check for >= has been changed to > .SInce problem
187 --was found while testing IGSPE006.fmb (SWCR001 )
188 --Bug.No:2203778
189 CURSOR pros_eval_cur(cp_person_id igs_pe_typ_instances_all.person_id%TYPE,
190 cp_system_type igs_pe_person_types.system_type%TYPE)
191 IS
192 SELECT 'X'
193 FROM igs_pe_typ_instances_all pti, igs_pe_person_types ppt
194 WHERE pti.person_id = cp_person_id AND
195 pti.person_type_code = ppt.person_type_code AND
196 ppt.system_type = cp_system_type AND
197 (pti.end_date is NULL OR (pti.end_date IS NOT NULL AND trunc(pti.end_date) > trunc(SYSDATE)));
198
199 l_pros_eval_exist VARCHAR2(1);
200
201
202 BEGIN
203 OPEN c_prospect_evaluator(p_person_type_code);
204 FETCH c_prospect_evaluator INTO l_system_type;
205 CLOSE c_prospect_evaluator;
206
207 IF l_system_type = 'EVALUATOR' THEN
208
209 OPEN pros_eval_cur(p_person_id,'PROSPECT');
210 FETCH pros_eval_cur INTO l_pros_eval_exist;
211 IF pros_eval_cur%FOUND THEN
212 CLOSE pros_eval_cur;
213 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PROSPCT_XST_NO_EVAL');
214 IGS_GE_MSG_STACK.ADD;
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END IF;
217 CLOSE pros_eval_cur;
218
219 ELSIF l_system_type = 'PROSPECT' THEN
220
221 OPEN pros_eval_cur(p_person_id,'EVALUATOR');
222 FETCH pros_eval_cur INTO l_pros_eval_exist;
223 IF pros_eval_cur%FOUND THEN
224 CLOSE pros_eval_cur;
225 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EVAL_XST_NO_PROSPCT');
226 IGS_GE_MSG_STACK.ADD;
227 APP_EXCEPTION.RAISE_EXCEPTION;
228 END IF;
229 CLOSE pros_eval_cur;
230
231 END IF;
232
233 END checkprospectevaluator;
234
235 Procedure after_insertupdate2 AS
236 /*************************************************************
237 Created By :IDK
238 Date Created By :
239 Purpose :
240 Know limitations, enhancements or remarks
241 Change History
242 Who When What
243 pkpatel 20-sep-2004 Bug 3690856 (removed the cursor c_get_prospect since its not used. Removed igs_lookups_view join in th cursor c_get_applicant. Made cursors parameterized.
244 sykrishn IDOPA2 Commented part which prevents PROSPECT and STUDENT to coexist
245 (reverse chronological order - newest change first)
246 ***************************************************************/
247 CURSOR c_get_applicant(cp_system_type igs_pe_person_types.system_type%TYPE,
248 cp_person_id igs_pe_typ_instances_all.person_id%TYPE,
249 cp_course_cd igs_pe_typ_instances_all.nominated_course_cd%TYPE) IS
250 SELECT pti.rowid,pti.*
251 FROM igs_pe_typ_instances_all pti,
252 igs_pe_person_types pt
253 WHERE pt.system_type = cp_system_type --'APPLICANT'
254 AND pti.person_type_code = pt.person_type_code
255 AND pti.person_id = cp_person_id
256 AND pti.nominated_course_cd = cp_course_cd
257 AND pti.end_date IS NULL;
258
259 CURSOR c_get_sys_typ(cp_person_type_code igs_pe_person_types.person_type_code%TYPE) IS
260 SELECT pt.system_type
261 FROM igs_pe_person_types pt
262 WHERE pt.person_type_code = cp_person_type_code;
263
264 l_system_type igs_pe_person_types.system_type%TYPE;
265 l_flag varchar2(10) ;
266 l_end_method varchar2(30);
267
268 begin
269 l_flag :='FALSE';
270 open c_get_sys_typ(new_references.person_type_code);
271 fetch c_get_sys_typ into l_system_type;
272 close c_get_sys_typ;
273
274 IF l_system_type = 'STUDENT' then
275 for c_appl_rec in c_get_applicant('APPLICANT',new_references.person_id,new_references.course_cd)loop
276 l_flag := 'TRUE' ;
277 igs_pe_typ_instances_pkg.update_row(
278 X_ROWID => c_appl_rec.rowid,
279 X_PERSON_ID => c_appl_rec.PERSON_ID,
280 X_COURSE_CD => c_appl_rec.COURSE_CD,
281 X_TYPE_INSTANCE_ID => c_appl_rec.TYPE_INSTANCE_ID,
282 X_PERSON_TYPE_CODE => c_appl_rec.PERSON_TYPE_CODE,
283 X_CC_VERSION_NUMBER => c_appl_rec.CC_VERSION_NUMBER,
284 X_FUNNEL_STATUS => c_appl_rec.FUNNEL_STATUS,
285 X_ADMISSION_APPL_NUMBER => c_appl_rec.ADMISSION_APPL_NUMBER,
286 X_NOMINATED_COURSE_CD => c_appl_rec.NOMINATED_COURSE_CD,
287 X_NCC_VERSION_NUMBER => c_appl_rec.NCC_VERSION_NUMBER,
288 X_SEQUENCE_NUMBER => c_appl_rec.SEQUENCE_NUMBER,
289 X_START_DATE => c_appl_rec.START_DATE,
290 X_END_DATE => SYSDATE,
291 X_CREATE_METHOD => c_appl_rec.CREATE_METHOD,
292 X_ENDED_BY => c_appl_rec.ENDED_BY,
293 X_END_METHOD => 'CREATE_STUDENT',
294 X_MODE => 'R',
295 X_EMPLMNT_CATEGORY_CODE => c_appl_rec.EMPLMNT_CATEGORY_CODE);
296
297 end loop;
298 END IF;
299
300
301 END after_insertupdate2;
302
303
304 PROCEDURE before_insert AS
305
306 /*************************************************************
307 Created By : prabhat.patel
308 Date Created By :
309 Purpose :Bug No 2389552. The Person Type code should have the value that is not closed.
310 Since at many places the closed indicator is not checked, the Active person type code is being passed explicitly.
311 Know limitations, enhancements or remarks
312 Change History
313 Who When What
314 pathipat 17-JUL-2002 Added check for system person types with more than one mapping
315 for Bug No: 2464771
316 pkpatel 3-APR-2003 Bug No: 2859277
317 Closed the cursor person_type_cur in else condition.
318 asbala 12-SEP-03 Changed igs_lookups_view to igs_lookup_values in CURSOR meaning_cur
319 (reverse chronological order - newest change first)
320 ***************************************************************/
321
322 CURSOR system_type_cur IS
323 SELECT system_type
324 FROM igs_pe_person_types pt
325 WHERE pt.person_type_code = new_references.person_type_code;
326
327 CURSOR person_type_cur(cp_system_type igs_pe_person_types.system_type%TYPE,cp_closed_ind igs_pe_person_types.closed_ind%TYPE) IS
328 SELECT person_type_code
329 FROM igs_pe_person_types pt
330 WHERE pt.system_type = cp_system_type AND
331 pt.closed_ind = cp_closed_ind;
332
333 CURSOR meaning_cur(cp_system_type igs_lookup_values.lookup_code%TYPE,cp_lookup_type igs_lookups_view.lookup_type%TYPE) IS
334 SELECT meaning
335 FROM igs_lookup_values
336 WHERE lookup_code = cp_system_type AND
337 lookup_type = cp_lookup_type;
338
339 meaning_rec meaning_cur%ROWTYPE;
340 system_type_rec system_type_cur%ROWTYPE;
341 person_type_rec person_type_cur%ROWTYPE;
342 BEGIN
343 -- No need to show the System Type when code is passed as null.
344 IF new_references.person_type_code IS NULL THEN
345 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PERSON_TYPE_N_MAPPED');
346 FND_MESSAGE.SET_TOKEN('SYSTEM_TYPE',NULL);
347 IGS_GE_MSG_STACK.ADD;
348 APP_EXCEPTION.RAISE_EXCEPTION;
349 END IF;
350
351 OPEN system_type_cur;
352 FETCH system_type_cur INTO system_type_rec;
353 CLOSE system_type_cur;
354
355 -- Check added for system person types with more than one mapping (pathipat) Bug:2464771
356 IF system_type_rec.system_type NOT IN ('USER_DEFINED','SS_ENROLL_STAFF') THEN
357 OPEN person_type_cur(system_type_rec.system_type,'N');
358 FETCH person_type_cur INTO person_type_rec;
359
360 IF person_type_cur%NOTFOUND THEN
361 CLOSE person_type_cur;
362
363 OPEN meaning_cur(system_type_rec.system_type,'SYSTEM_PERSON_TYPES');
364 FETCH meaning_cur INTO meaning_rec;
365 CLOSE meaning_cur;
366
367 meaning_rec.meaning := ''''||meaning_rec.meaning||'''';
368
369 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PERSON_TYPE_N_MAPPED');
370 FND_MESSAGE.SET_TOKEN('SYSTEM_TYPE',meaning_rec.meaning);
371 IGS_GE_MSG_STACK.ADD;
372 APP_EXCEPTION.RAISE_EXCEPTION;
373 ELSE
374 CLOSE person_type_cur;
375 new_references.person_type_code := person_type_rec.person_type_code;
376 END IF;
377 END IF;
378
379 END before_insert;
380
381
382
383 PROCEDURE Check_Constraints (
384 Column_Name IN VARCHAR2 ,
385 Column_Value IN VARCHAR2 ) AS
386 /*************************************************************
387 Created By :
388 Date Created By :
389 Purpose :
390 Know limitations, enhancements or remarks
391 Change History
392 Who When What
393
394 (reverse chronological order - newest change first)
395 ***************************************************************/
396
397 BEGIN
398
399 IF column_name IS NULL THEN
400 NULL;
401 NULL;
402 END IF;
403
404 END Check_Constraints;
405
406 PROCEDURE Check_Parent_Existance AS
407 /*************************************************************
408 Created By :
409 Date Created By :
410 Purpose :
411 Know limitations, enhancements or remarks
412 Change History
413 Who When What
414
415 (reverse chronological order - newest change first)
416 ***************************************************************/
417
418 BEGIN
419
420 IF (((old_references.person_id = new_references.person_id) AND
421 (old_references.admission_appl_number = new_references.admission_appl_number) AND
422 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
423 (old_references.sequence_number = new_references.sequence_number)) OR
424 ((new_references.person_id IS NULL) OR
425 (new_references.admission_appl_number IS NULL) OR
426 (new_references.nominated_course_cd IS NULL) OR
427 (new_references.sequence_number IS NULL))) THEN
428 NULL;
429 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation (
430 new_references.person_id,
431 new_references.admission_appl_number,
432 new_references.nominated_course_cd,
433 new_references.sequence_number
434 ) THEN
435 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
436 IGS_GE_MSG_STACK.ADD;
437 App_Exception.Raise_Exception;
438 END IF;
439
440 IF (((old_references.person_type_code = new_references.person_type_code)) OR
441 ((new_references.person_type_code IS NULL))) THEN
442 NULL;
443 ELSIF NOT Igs_Pe_Person_Types_Pkg.Get_PK_For_Validation (
444 new_references.person_type_code
445 ) THEN
446 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
447 IGS_GE_MSG_STACK.ADD;
448 App_Exception.Raise_Exception;
449 END IF;
450
451 IF (((old_references.course_cd = new_references.course_cd) AND
452 (old_references.cc_version_number = new_references.cc_version_number)) OR
453 ((new_references.course_cd IS NULL) OR
454 (new_references.cc_version_number IS NULL))) THEN
455 NULL;
456 ELSIF NOT Igs_Ps_Ver_Pkg.Get_PK_For_Validation (
457 new_references.course_cd,
458 new_references.cc_version_number
459 ) THEN
460 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
461 IGS_GE_MSG_STACK.ADD;
462 App_Exception.Raise_Exception;
463 END IF;
464
465 IF (((old_references.person_id = new_references.person_id)) OR
466 ((new_references.person_id IS NULL))) THEN
467 NULL;
468 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
469 new_references.person_id
470 ) THEN
471 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475
476 END Check_Parent_Existance;
477
478 FUNCTION Get_PK_For_Validation (
479 x_type_instance_id IN NUMBER
480 ) RETURN BOOLEAN AS
481
482 /*************************************************************
483 Created By :
484 Date Created By :
485 Purpose :
486 Know limitations, enhancements or remarks
487 Change History
488 Who When What
489
490 (reverse chronological order - newest change first)
491 ***************************************************************/
492
493 CURSOR cur_rowid IS
494 SELECT rowid
495 FROM igs_pe_typ_instances_all
496 WHERE type_instance_id = x_type_instance_id
497 FOR UPDATE NOWAIT;
498
499 lv_rowid cur_rowid%RowType;
500
501 BEGIN
502
503 Open cur_rowid;
504 Fetch cur_rowid INTO lv_rowid;
505 IF (cur_rowid%FOUND) THEN
506 Close cur_rowid;
507 Return(TRUE);
508 ELSE
509 Close cur_rowid;
510 Return(FALSE);
511 END IF;
512 END Get_PK_For_Validation;
513
514 PROCEDURE Get_FK_Igs_Ad_Ps_Appl_Inst (
515 x_person_id IN NUMBER,
516 x_admission_appl_number IN NUMBER,
517 x_nominated_course_cd IN VARCHAR2,
518 x_sequence_number IN NUMBER
519 ) AS
520
521 /*************************************************************
522 Created By :
523 Date Created By :
524 Purpose :
525 Know limitations, enhancements or remarks
526 Change History
527 Who When What
528
529 (reverse chronological order - newest change first)
530 ***************************************************************/
531
532 CURSOR cur_rowid IS
533 SELECT rowid
534 FROM igs_pe_typ_instances_all
535 WHERE person_id = x_person_id
536 AND admission_appl_number = x_admission_appl_number
537 AND nominated_course_cd = x_nominated_course_cd
538 AND sequence_number = x_sequence_number ;
539
540 lv_rowid cur_rowid%RowType;
541
542 BEGIN
543
544 Open cur_rowid;
545 Fetch cur_rowid INTO lv_rowid;
546 IF (cur_rowid%FOUND) THEN
547 Close cur_rowid;
548 Fnd_Message.Set_Name ('IGS', 'IGS_PE_TYP_APPLINST');
549 IGS_GE_MSG_STACK.ADD;
550 App_Exception.Raise_Exception;
551 Return;
552 END IF;
553 Close cur_rowid;
554
555 END Get_FK_Igs_Ad_Ps_Appl_Inst;
556
557 PROCEDURE Get_FK_Igs_Pe_Person_Types (
558 x_person_type_code IN VARCHAR2
559 ) AS
560
561 /*************************************************************
562 Created By :
563 Date Created By :
564 Purpose :
565 Know limitations, enhancements or remarks
566 Change History
567 Who When What
568
569 (reverse chronological order - newest change first)
570 ***************************************************************/
571
572 CURSOR cur_rowid IS
573 SELECT rowid
574 FROM igs_pe_typ_instances_all
575 WHERE person_type_code = x_person_type_code ;
576
577 lv_rowid cur_rowid%RowType;
578
579 BEGIN
580
581 Open cur_rowid;
582 Fetch cur_rowid INTO lv_rowid;
583 IF (cur_rowid%FOUND) THEN
584 Close cur_rowid;
585 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PTI_PPT_FK');
586 IGS_GE_MSG_STACK.ADD;
587 App_Exception.Raise_Exception;
588 Return;
589 END IF;
590 Close cur_rowid;
591
592 END Get_FK_Igs_Pe_Person_Types;
593
594 PROCEDURE Get_FK_Igs_Ps_Ver (
595 x_course_cd IN VARCHAR2,
596 x_version_number IN NUMBER
597 ) AS
598
599 /*************************************************************
600 Created By :
601 Date Created By :
602 Purpose :
603 Know limitations, enhancements or remarks
604 Change History
605 Who When What
606
607 (reverse chronological order - newest change first)
608 ***************************************************************/
609
610 CURSOR cur_rowid IS
611 SELECT rowid
612 FROM igs_pe_typ_instances_all
613 WHERE course_cd = x_course_cd
614 AND cc_version_number = x_version_number ;
615
616 lv_rowid cur_rowid%RowType;
617
618 BEGIN
619
620 Open cur_rowid;
621 Fetch cur_rowid INTO lv_rowid;
622 IF (cur_rowid%FOUND) THEN
623 Close cur_rowid;
624 Fnd_Message.Set_Name ('IGS', 'IGS_PE_TYP_VER');
625 IGS_GE_MSG_STACK.ADD;
626 App_Exception.Raise_Exception;
627 Return;
628 END IF;
629 Close cur_rowid;
630
631 END Get_FK_Igs_Ps_Ver;
632
633 PROCEDURE Get_FK_Igs_Pe_Person (
634 x_person_id IN NUMBER
635 ) AS
636
637 /*************************************************************
638 Created By :
639 Date Created By :
640 Purpose :
641 Know limitations, enhancements or remarks
642 Change History
643 Who When What
644
645 (reverse chronological order - newest change first)
646 ***************************************************************/
647
648 CURSOR cur_rowid IS
649 SELECT rowid
650 FROM igs_pe_typ_instances_all
651 WHERE person_id = x_person_id ;
652
653 lv_rowid cur_rowid%RowType;
654
655 BEGIN
656
657 Open cur_rowid;
658 Fetch cur_rowid INTO lv_rowid;
659 IF (cur_rowid%FOUND) THEN
660 Close cur_rowid;
661 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PTI_PE_FK');
662 IGS_GE_MSG_STACK.ADD;
663 App_Exception.Raise_Exception;
664 Return;
665 END IF;
666 Close cur_rowid;
667
668
669 END Get_FK_Igs_Pe_Person;
670
671 PROCEDURE Check_Mand_Person_Type
672 (
673 p_person_type_code IN IGS_PE_PERSON_TYPES.person_type_code%TYPE,
674 p_person_id IN HZ_PARTIES.party_id%TYPE
675 )
676 IS
677 /*************************************************************
678 Created By :
679 Date Created By :
680 Purpose :
681 Know limitations, enhancements or remarks
682 Change History
683 Who When What
684 (reverse chronological order - newest change first)
685 ssaleem 21-NOV-2003 Bug No: 3039238
686 Removed UPPER check for person type code in Mand_Emt cursor
687 pkpatel 3-APR-2003 Bug No: 2859277
688 Added the DBMS_SQL.CLOSE_CURSOR for the dynamic SQL created.
689 asbala 12-SEP-03 Bug No:2667343 Replaced Hard coded strings populating lv_Data_Emt by
690 lookup_codes from a lookup_type
691 gmaheswa 07-JUL-05 Bug No: 4327807 Added condition to skip Person Type Mandatory data validation
692 incase of Self-Service applications.
693 vskumar 12-Jul-2006 Bug No: 4068301 & 4068322. Added a new cursor c_ar_lookups for address_types to display meaning
694 instead of lookup_code in the error message, when it is mendatory.
695 ***************************************************************/
696
697 CURSOR Mand_Emt IS
698 SELECT setup_data_element_id, person_type_code, data_element,
699 value, required_ind
700 FROM igs_pe_stup_data_emt
701 WHERE person_type_code = p_person_type_code
702 AND NVL(required_ind, 'S') IN ('M');
703
704 CURSOR Data_Emt (p_data_element IGS_PE_DATA_ELEMENT.data_element%TYPE) IS
705 SELECT table_name, column_name
706 FROM igs_pe_data_element
707 WHERE UPPER(data_element) = UPPER(p_data_element) ;
708
709 CURSOR c_lookup_meaning(cp_lookup_type VARCHAR2,
710 cp_lookup_code VARCHAR2) IS
711 SELECT meaning
712 FROM IGS_LOOKUP_VALUES
713 WHERE lookup_type=cp_lookup_type AND
714 lookup_code=cp_lookup_code;
715
716 CURSOR c_ar_lookups(cp_lookup_type VARCHAR2,
717 cp_lookup_code VARCHAR2) IS
718 SELECT meaning
719 FROM ar_lookups
720 WHERE lookup_type = cp_lookup_type
721 AND lookup_code = cp_lookup_code;
722
723 lvc_SQLStmt VARCHAR2(2000) ;
724 l_Ext_Cursor NUMBER;
725 lnRows NUMBER;
726 lv_DataEmt VARCHAR2(100);
727 l_lookup_type VARCHAR2(30);
728 l_lookup_code VARCHAR2(30);
729 lv_ar_description VARCHAR2(100);
730 BEGIN
731
732 IF IGS_PE_GEN_004.G_SKIP_MAND_DATA_VAL = 'Y' THEN
733 RETURN;
734 END IF;
735
736 FOR c_Mand_Emt IN Mand_Emt LOOP
737 l_lookup_type := 'PE_MAND_DATA_ELEMENT';
738 lvc_SQLStmt := 'SELECT 1 FROM ';
739 IF c_Mand_Emt.data_element IN ('PREFERRED_GIVEN_NAME', 'TITLE', 'DATE_OF_BIRTH',
740 'SEX', 'EMAIL_ADDR', 'ETHNIC_ORIGIN',
741 'INST_RES_STATUS', 'TEACH_PERIOD_RES_STATUS') THEN
742
743 FOR c_Data_Emt IN Data_Emt (c_Mand_Emt.data_element) LOOP
744 lvc_SQLStmt := lvc_SQLStmt || c_Data_Emt.table_name || ' WHERE person_id = ' || p_person_id
745 || ' AND ' || c_Data_Emt.column_name || ' IS NOT NULL ';
746
747 END LOOP;
748
749 ELSIF c_Mand_Emt.data_element = ('ADDRESS_TYPE') THEN
750 FOR c_Data_Emt IN Data_Emt (c_Mand_Emt.data_element) LOOP
751 lvc_SQLStmt := lvc_SQLStmt || c_Data_Emt.table_name || ' WHERE person_id = ' || p_person_id
752 || ' AND ' || c_Data_Emt.column_name || ' = ''' || c_Mand_Emt.value || '''';
753 END LOOP;
754
755 ELSIF c_Mand_Emt.data_element = ('PERSON_ID_TYPE') THEN
756 FOR c_Data_Emt IN Data_Emt (c_Mand_Emt.data_element) LOOP
757 lvc_SQLStmt := lvc_SQLStmt || c_Data_Emt.table_name || ' WHERE pe_person_id = ' || p_person_id
758 || ' AND ' || c_Data_Emt.column_name || ' = ''' || c_Mand_Emt.value || '''';
759 END LOOP;
760
761
762 ELSIF c_Mand_Emt.data_element IN ('PROOF_OF_INS', 'PROOF_OF_IMMU') THEN
763 FOR c_Data_Emt IN Data_Emt (c_Mand_Emt.data_element) LOOP
764 lvc_SQLStmt := lvc_SQLStmt || c_Data_Emt.table_name || ' WHERE person_id = ' || p_person_id
765 || ' AND NVL(' || c_Data_Emt.column_name || ', ''N'') = ''Y''';
766 END LOOP;
767 END IF;
768
769 IF c_Mand_Emt.data_element IN ('PREFERRED_GIVEN_NAME', 'TITLE', 'DATE_OF_BIRTH',
770 'ETHNIC_ORIGIN') THEN
771 lv_DataEmt := INITCAP(REPLACE(c_Mand_Emt.data_element, '_', ' '));
772 ELSIF c_Mand_Emt.data_element = 'SEX' THEN
773 l_lookup_type := 'PERSON_TYPE_MAND_DATA';
774 l_lookup_code := 'SEX';
775 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
776 FETCH c_lookup_meaning INTO lv_DataEmt;
777 CLOSE c_lookup_meaning;
778 ELSIF c_Mand_Emt.data_element = 'EMAIL_ADDR' THEN
779 l_lookup_code := 'EMAIL_ADDRESS';
780 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
781 FETCH c_lookup_meaning INTO lv_DataEmt;
782 CLOSE c_lookup_meaning;
783 ELSIF c_Mand_Emt.data_element = 'INST_RES_STATUS' THEN
784 l_lookup_code := 'INST_RES_STATUS';
785 l_lookup_type := 'PERSON_TYPE_MAND_DATA';
786 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
787 FETCH c_lookup_meaning INTO lv_DataEmt;
788 CLOSE c_lookup_meaning;
789 ELSIF c_Mand_Emt.data_element = 'TEACH_PERIOD_RES_STATUS' THEN
790 l_lookup_code := 'TEACH_PERIOD_RES_STATUS';
791 l_lookup_type := 'PERSON_TYPE_MAND_DATA';
792 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
793 FETCH c_lookup_meaning INTO lv_DataEmt;
794 CLOSE c_lookup_meaning;
795 ELSIF c_Mand_Emt.data_element = 'ADDRESS_TYPE' THEN
796 l_lookup_code := 'ADDRESS_USAGE';
797 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
798 FETCH c_lookup_meaning INTO lv_DataEmt;
799 CLOSE c_lookup_meaning;
800
801 OPEN c_ar_lookups('PARTY_SITE_USE_CODE', c_Mand_Emt.Value);
802 FETCH c_ar_lookups INTO lv_ar_description;
803 CLOSE c_ar_lookups;
804 lv_DataEmt := lv_DataEmt || ' ' || lv_ar_description;
805 ELSIF c_Mand_Emt.data_element = 'PERSON_ID_TYPE' THEN
806 l_lookup_code := 'PERSON_ID_TYP';
807 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
808 FETCH c_lookup_meaning INTO lv_DataEmt;
809 CLOSE c_lookup_meaning;
810 lv_DataEmt := lv_DataEmt || ' ' || c_Mand_Emt.Value;
811 ELSIF c_Mand_Emt.data_element = 'PROOF_OF_INS' THEN
812 l_lookup_code := 'PROOF_OF_INS';
813 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
814 FETCH c_lookup_meaning INTO lv_DataEmt;
815 CLOSE c_lookup_meaning;
816 ELSIF c_Mand_Emt.data_element = 'PROOF_OF_IMMU' THEN
817 l_lookup_code := 'PROOF_OF_IMMU';
818 OPEN c_lookup_meaning(l_lookup_type,l_lookup_code);
819 FETCH c_lookup_meaning INTO lv_DataEmt;
820 CLOSE c_lookup_meaning;
821 END IF;
822
823
824 l_Ext_Cursor := DBMS_SQL.OPEN_CURSOR;
825 DBMS_SQL.PARSE (l_Ext_Cursor, lvc_SQLStmt, DBMS_SQL.V7);
826
827 lnRows := DBMS_SQL.EXECUTE_AND_FETCH (l_Ext_Cursor);
828
829 IF lnRows = 0 THEN
830 -- Next line modified due to Bug no# 1496059
831 DBMS_SQL.CLOSE_CURSOR(l_Ext_Cursor);
832 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_CHECK_MAND_DATA');
833 FND_MESSAGE.SET_TOKEN ('Person_Type_Code', INITCAP(c_Mand_Emt.person_type_code));
834 FND_MESSAGE.SET_TOKEN ('DataElement', lv_DataEmt);
835 IGS_GE_MSG_STACK.ADD;
836 App_Exception.Raise_Exception;
837
838 END IF;
839
840 DBMS_SQL.CLOSE_CURSOR(l_Ext_Cursor);
841
842 END LOOP;
843
844 END Check_Mand_Person_Type;
845
846 PROCEDURE Before_DML (
847 p_action IN VARCHAR2,
848 x_rowid IN VARCHAR2 ,
849 x_person_id IN NUMBER ,
850 x_course_cd IN VARCHAR2 ,
851 x_type_instance_id IN NUMBER ,
852 x_person_type_code IN VARCHAR2 ,
853 x_cc_version_number IN NUMBER ,
854 x_funnel_status IN VARCHAR2 ,
855 x_admission_appl_number IN NUMBER ,
856 x_nominated_course_cd IN VARCHAR2 ,
857 x_ncc_version_number IN NUMBER ,
858 x_sequence_number IN NUMBER ,
859 x_start_date IN DATE ,
860 x_end_date IN DATE ,
861 x_create_method IN VARCHAR2 ,
862 x_ended_by IN NUMBER ,
863 x_end_method IN VARCHAR2 ,
864 x_creation_date IN DATE ,
865 x_created_by IN NUMBER ,
866 x_last_update_date IN DATE ,
867 x_last_updated_by IN NUMBER ,
868 x_last_update_login IN NUMBER ,
869 x_org_id IN NUMBER ,
870 x_emplmnt_category_code IN VARCHAR2
871 ) AS
872 /*************************************************************
873 Created By :
874 Date Created By :
875 Purpose :
876 Know limitations, enhancements or remarks
877 Change History
878 Who When What
879 pkpatel 5-JUL-2002 Bug No 2389552
880 Added the call to the procedure before_insert
881 rrengara 4-JAN-2002 Added code logic for the bug 2168915
882 (reverse chronological order - newest change first)
883 ***************************************************************/
884
885 BEGIN
886
887 Set_Column_Values (
888 p_action,
889 x_rowid,
890 x_person_id,
891 x_course_cd,
892 x_type_instance_id,
893 x_person_type_code,
894 x_cc_version_number,
895 x_funnel_status,
896 x_admission_appl_number,
897 x_nominated_course_cd,
898 x_ncc_version_number,
899 x_sequence_number,
900 x_start_date,
901 x_end_date,
902 x_create_method,
903 x_ended_by,
904 x_end_method,
905 x_creation_date,
906 x_created_by,
907 x_last_update_date,
908 x_last_updated_by,
909 x_last_update_login,
910 x_org_id,
911 x_emplmnt_category_code
912 );
913
914 IF (p_action = 'INSERT') THEN
915 -- Call all the procedures related to Before Insert.
916 Null;
917 IF Get_Pk_For_Validation(
918 new_references.type_instance_id) THEN
919 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
920 IGS_GE_MSG_STACK.ADD;
921 App_Exception.Raise_Exception;
922 END IF;
923 Check_Constraints;
924 Check_Parent_Existance;
925 -- takes the current active person type code for the system person type
926 before_insert;
927
928 Check_Mand_Person_Type (
929 new_references.person_type_code,
930 new_references.person_id);
931 -- Call to local procedure to check whether valid Prospect/Evaluator exists
932 Checkprospectevaluator(
933 new_references.person_id,
934 new_references.person_type_code);
935
936 ELSIF (p_action = 'UPDATE') THEN
937 -- Call all the procedures related to Before Update.
938 Null;
939 Check_Constraints;
940 Check_Parent_Existance;
941
942 -- Added the following code for the Bug 2168915
943
944 IF old_references.funnel_status = '300-INQUIRED' THEN
945 IF new_references.funnel_status <> '300-INQUIRED' THEN
946 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_INVALID_FUNSTAT');
947 IGS_GE_MSG_STACK.ADD;
948 APP_EXCEPTION.RAISE_EXCEPTION;
949 END IF;
950 ELSIF old_references.funnel_status = '200-CONTACTED' THEN
951 IF new_references.funnel_status = '100-IDENTIFIED' THEN
952 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_INVALID_FUNSTAT');
953 IGS_GE_MSG_STACK.ADD;
954 APP_EXCEPTION.RAISE_EXCEPTION;
955 END IF;
956 END IF;
957
958 Check_Mand_Person_Type (
959 new_references.person_type_code,
960 new_references.person_id);
961 -- Call to local procedure to check whether valid Prospect/Evaluator exists
962 IF (trunc(new_references.end_date) > trunc(sysdate) OR new_references.end_date IS NULL) THEN
963 Checkprospectevaluator(
964 new_references.person_id,
965 new_references.person_type_code);
966 END IF;
967 ELSIF (p_action = 'DELETE') THEN
968 Null;
969 ELSIF (p_action = 'VALIDATE_INSERT') THEN
970 -- Call all the procedures related to Before Insert.
971 IF Get_PK_For_Validation (
972 new_references.type_instance_id) THEN
973 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
974 IGS_GE_MSG_STACK.ADD;
975 App_Exception.Raise_Exception;
976 END IF;
977 Check_Constraints;
978 Check_Mand_Person_Type (
979 new_references.person_type_code,
980 new_references.person_id);
981 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
982 Check_Constraints;
983 Check_Mand_Person_Type (
984 new_references.person_type_code,
985 new_references.person_id);
986 ELSIF (p_action = 'VALIDATE_DELETE') THEN
987 NULL;
988 END IF;
989
990 END Before_DML;
991
992 PROCEDURE After_DML (
993 p_action IN VARCHAR2,
994 x_rowid IN VARCHAR2
995 ) IS
996 /*************************************************************
997 Created By :
998 Date Created By :
999 Purpose :
1000 Know limitations, enhancements or remarks
1001 Change History
1002 Who When What
1003 skpandey 18-AUG-2005 Bug#: 4378028
1004 Added Business Event logic for INSERT, UPDATE and DELETE cases respectively
1005 (reverse chronological order - newest change first)
1006 ***************************************************************/
1007
1008 CURSOR get_usr_id_cur(cp_person_id fnd_user.person_party_id%type) IS
1009 SELECT user_id
1010 FROM fnd_user
1011 WHERE person_party_id = cp_person_id;
1012
1013 CURSOR person_type_cur(cp_person_type igs_pe_person_types.person_type_code%TYPE) IS
1014 SELECT system_type
1015 FROM igs_pe_person_types pt
1016 WHERE pt.person_type_code = cp_person_type;
1017
1018
1019 l_system_type igs_pe_person_types.system_type%TYPE;
1020 l_usr_id fnd_user.user_id%type;
1021 l_usr_d_id fnd_user.user_id%type;
1022 l_person_type_w_other varchar2(30) ; -- to hold the person_type value during insert of a person type for the first time.
1023 l_person_id_w_other number; -- to hold the person_id value during insert of a person type for the first time.
1024
1025 ---- Check if the person has more than ONE ACTIVE assignment for the same System Person Type,
1026 ---- as the user PERSON TYPE passed. UPDATE CASE
1027 CURSOR get_active_inst_cur(cp_person_id hz_parties.party_id%type,
1028 cp_system_type igs_pe_person_types.system_type%type ,
1029 cp_rowid varchar2) IS
1030 SELECT MAX(NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD'))) FROM igs_pe_typ_instances_all pti
1031 WHERE pti.person_id = cp_person_id
1032 AND pti.rowid <> cp_rowid
1033 AND SYSDATE BETWEEN pti.start_date and NVL(pti.end_date, SYSDATE)
1034 AND pti.person_type_code IN
1035 (select person_type_code from igs_pe_person_types pt where system_type =cp_system_type) ;
1036 l_max_active_date DATE;
1037 l_person_end_date_other DATE;
1038 l_prog_label CONSTANT VARCHAR2(100) := 'igs.plsql.igs_pe_typ_instances_pkg.after_dml';
1039 l_label VARCHAR2(500);
1040 l_debug_str VARCHAR2(3200);
1041 l_old_end_date DATE;
1042 l_default_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
1043 BEGIN
1044
1045 l_rowid := x_rowid;
1046 -- Call all the procedures related to After Insert.
1047
1048 -- logic is, when new person type is added for a person for the firs time, the after_insert_update calls the delete_row with rowid, that sets the
1049 -- new_references to null. Hence when we raise the event, it bombs.
1050 -- so only for that particular loop we need to hold back the values of new_references into another variable.
1051 l_person_type_w_other := new_references.person_type_code;
1052 l_person_id_w_other := new_references.person_id;
1053 l_person_end_date_other:= new_references.end_date; -- 4612440,4612692
1054 l_old_end_date := old_references.end_date;
1055
1056 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1057 l_label := 'igs.plsql.igs_pe_typ_instances_pkg.after_dml.'||p_action;
1058 l_debug_str := 'Person Type Code : '||l_person_type_w_other ||'/'|| ' Person id : ' ||l_person_id_w_other || ' End Date ' ||'/' ||l_person_end_date_other;
1059 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1060 END IF;
1061
1062 -- cursor when action is Insert/Update
1063 OPEN get_usr_id_cur(l_person_id_w_other);
1064 FETCH get_usr_id_cur INTO l_usr_id;
1065 CLOSE get_usr_id_cur;
1066
1067
1068 OPEN person_type_cur(new_references.person_type_code);
1069 FETCH person_type_cur INTO l_system_type;
1070 CLOSE person_type_cur;
1071 -- raise the event only if the person is associated with fnd user.
1072 -- raise the event for update only if this person has NO other person type instances
1073 -- through which he can get the same set of RESP.
1074 IF (p_action = 'INSERT') THEN
1075
1076 After_Insert_Update;
1077 after_insertupdate2;
1078
1079 -- The Business event should be raised only if end date is greater than current date
1080 IF l_usr_id IS NOT NULL AND (NVL(l_person_end_date_other,SYSDATE+1) > SYSDATE)THEN
1081 igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(l_person_id_w_other, l_person_type_w_other, p_action, l_person_end_date_other);
1082 END IF;
1083
1084 ELSIF (p_action = 'UPDATE') THEN
1085
1086 -- Call all the procedures related to After Update.
1087 After_Insert_Update;
1088
1089 -- Business event should be raised is old and new end dates are different
1090 IF l_usr_id IS NOT NULL AND (NVL(l_old_end_date,l_default_date) <> NVL(l_person_end_date_other,l_default_date)) THEN
1091
1092
1093 OPEN get_active_inst_cur(new_references.person_id , l_system_type,l_rowid);
1094 FETCH get_active_inst_cur INTO l_max_active_date;
1095 CLOSE get_active_inst_cur;
1096
1097 --if setting end date of person type to NULL, then dont bother, just raise the event.
1098 --else raise the event only if no other end date person type instance for same person type exists and
1099 --the end date is greated than the Max end date of other records
1100 IF l_person_end_date_other IS NULL THEN
1101 igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(new_references.person_id , new_references.person_type_code, p_action, l_person_end_date_other);
1102 ELSE
1103 IF l_max_active_date IS NULL OR l_max_active_date < l_person_end_date_other THEN
1104 -- This case is specifically for Import process where start/end date can be less
1105 -- current date. So if end date is passed as less than the current date then pass it as current date
1106
1107 IF l_person_end_date_other < SYSDATE THEN
1108 l_person_end_date_other := TRUNC(SYSDATE);
1109 END IF;
1110 igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(new_references.person_id , new_references.person_type_code, p_action, l_person_end_date_other);
1111 END IF;
1112 END IF;
1113
1114
1115 END IF;
1116 ELSIF (p_action = 'DELETE') THEN
1117 --Call all the procedures related to After Delete.
1118 -- cursor when action is Delete
1119 OPEN get_usr_id_cur(old_references.person_id);
1120 FETCH get_usr_id_cur INTO l_usr_d_id;
1121 CLOSE get_usr_id_cur;
1122
1123 IF l_usr_d_id IS NOT NULL THEN
1124 igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(old_references.person_id, old_references.person_type_code, p_action);
1125 END IF;
1126 END IF;
1127 END After_DML;
1128
1129 procedure INSERT_ROW (
1130 X_ROWID in out NOCOPY VARCHAR2,
1131 x_PERSON_ID IN NUMBER,
1132 x_COURSE_CD IN VARCHAR2,
1133 x_TYPE_INSTANCE_ID IN OUT NOCOPY NUMBER,
1134 x_PERSON_TYPE_CODE IN VARCHAR2,
1135 x_CC_VERSION_NUMBER IN NUMBER,
1136 x_FUNNEL_STATUS IN VARCHAR2,
1137 x_ADMISSION_APPL_NUMBER IN NUMBER,
1138 x_NOMINATED_COURSE_CD IN VARCHAR2,
1139 x_NCC_VERSION_NUMBER IN NUMBER,
1140 x_SEQUENCE_NUMBER IN NUMBER,
1141 x_START_DATE IN DATE,
1142 x_END_DATE IN DATE,
1143 x_CREATE_METHOD IN VARCHAR2,
1144 x_ENDED_BY IN NUMBER,
1145 x_END_METHOD IN VARCHAR2,
1146 X_MODE in VARCHAR2 ,
1147 X_ORG_ID in NUMBER ,
1148 X_EMPLMNT_CATEGORY_CODE IN VARCHAR2
1149 )
1150 AS
1151 /*************************************************************
1152 Created By :
1153 Date Created By :
1154 Purpose :
1155 Know limitations, enhancements or remarks
1156 Change History
1157 Who When What
1158
1159 (reverse chronological order - newest change first)
1160 ***************************************************************/
1161
1162 cursor C is select ROWID from IGS_PE_TYP_INSTANCES_ALL
1163 where TYPE_INSTANCE_ID= X_TYPE_INSTANCE_ID;
1164 X_LAST_UPDATE_DATE DATE ;
1165 X_LAST_UPDATED_BY NUMBER ;
1166 X_LAST_UPDATE_LOGIN NUMBER ;
1167 begin
1168 X_LAST_UPDATE_DATE := SYSDATE;
1169 if(X_MODE = 'I') then
1170 X_LAST_UPDATED_BY := 1;
1171 X_LAST_UPDATE_LOGIN := 0;
1172 elsif (X_MODE IN ('R', 'S')) then
1173 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1174 if X_LAST_UPDATED_BY is NULL then
1175 X_LAST_UPDATED_BY := -1;
1176 end if;
1177 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1178 if X_LAST_UPDATE_LOGIN is NULL then
1179 X_LAST_UPDATE_LOGIN := -1;
1180 end if;
1181 else
1182 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1183 IGS_GE_MSG_STACK.ADD;
1184 app_exception.raise_exception;
1185 end if;
1186
1187 select IGS_PE_TYPE_INSTANCES_S.NEXTVAL INTO x_type_instance_id FROM DUAL;
1188 Before_DML(
1189 p_action=>'INSERT',
1190 x_rowid=>X_ROWID,
1191 x_person_id=>X_PERSON_ID,
1192 x_course_cd=>X_COURSE_CD,
1193 x_type_instance_id=>X_TYPE_INSTANCE_ID,
1194 x_person_type_code=>X_PERSON_TYPE_CODE,
1195 x_cc_version_number=>X_CC_VERSION_NUMBER,
1196 x_funnel_status=>X_FUNNEL_STATUS,
1197 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
1198 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
1199 x_ncc_version_number=>X_NCC_VERSION_NUMBER,
1200 x_sequence_number=>X_SEQUENCE_NUMBER,
1201 x_start_date=>X_START_DATE,
1202 x_end_date=>X_END_DATE,
1203 x_create_method=>X_CREATE_METHOD,
1204 x_ended_by=>X_ENDED_BY,
1205 x_end_method=>X_END_METHOD,
1206 x_creation_date=>X_LAST_UPDATE_DATE,
1207 x_created_by=>X_LAST_UPDATED_BY,
1208 x_last_update_date=>X_LAST_UPDATE_DATE,
1209 x_last_updated_by=>X_LAST_UPDATED_BY,
1210 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1211 x_org_id=>igs_ge_gen_003.get_org_id,
1212 x_emplmnt_category_code => x_emplmnt_category_code
1213 );
1214 IF (x_mode = 'S') THEN
1215 igs_sc_gen_001.set_ctx('R');
1216 END IF;
1217 insert into IGS_PE_TYP_INSTANCES_ALL (
1218 PERSON_ID
1219 ,COURSE_CD
1220 ,TYPE_INSTANCE_ID
1221 ,PERSON_TYPE_CODE
1222 ,CC_VERSION_NUMBER
1223 ,FUNNEL_STATUS
1224 ,ADMISSION_APPL_NUMBER
1225 ,NOMINATED_COURSE_CD
1226 ,NCC_VERSION_NUMBER
1227 ,SEQUENCE_NUMBER
1228 ,START_DATE
1229 ,END_DATE
1230 ,CREATE_METHOD
1231 ,ENDED_BY
1232 ,END_METHOD
1233 ,CREATION_DATE
1234 ,CREATED_BY
1235 ,LAST_UPDATE_DATE
1236 ,LAST_UPDATED_BY
1237 ,LAST_UPDATE_LOGIN
1238 ,ORG_ID
1239 ,EMPLMNT_CATEGORY_CODE
1240 ) values (
1241 NEW_REFERENCES.PERSON_ID
1242 ,NEW_REFERENCES.COURSE_CD
1243 ,NEW_REFERENCES.TYPE_INSTANCE_ID
1244 ,NEW_REFERENCES.PERSON_TYPE_CODE
1245 ,NEW_REFERENCES.CC_VERSION_NUMBER
1246 ,NEW_REFERENCES.FUNNEL_STATUS
1247 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
1248 ,NEW_REFERENCES.NOMINATED_COURSE_CD
1249 ,NEW_REFERENCES.NCC_VERSION_NUMBER
1250 ,NEW_REFERENCES.SEQUENCE_NUMBER
1251 ,NEW_REFERENCES.START_DATE
1252 ,NEW_REFERENCES.END_DATE
1253 ,NEW_REFERENCES.CREATE_METHOD
1254 ,NEW_REFERENCES.ENDED_BY
1255 ,NEW_REFERENCES.END_METHOD
1256 ,X_LAST_UPDATE_DATE
1257 ,X_LAST_UPDATED_BY
1258 ,X_LAST_UPDATE_DATE
1259 ,X_LAST_UPDATED_BY
1260 ,X_LAST_UPDATE_LOGIN
1261 ,NEW_REFERENCES.ORG_ID
1262 ,NEW_REFERENCES.EMPLMNT_CATEGORY_CODE
1263 );
1264 IF (x_mode = 'S') THEN
1265 igs_sc_gen_001.unset_ctx('R');
1266 END IF;
1267
1268 open c;
1269 fetch c into X_ROWID;
1270 if (c%notfound) then
1271 close c;
1272 raise no_data_found;
1273 end if;
1274 close c;
1275 After_DML (
1276 p_action => 'INSERT' ,
1277 x_rowid => X_ROWID );
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1281 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1282 fnd_message.set_token ('ERR_CD', SQLCODE);
1283 igs_ge_msg_stack.add;
1284 igs_sc_gen_001.unset_ctx('R');
1285 app_exception.raise_exception;
1286 ELSE
1287 igs_sc_gen_001.unset_ctx('R');
1288 RAISE;
1289 END IF;
1290
1291 end INSERT_ROW;
1292
1293
1294 procedure LOCK_ROW (
1295 X_ROWID in VARCHAR2,
1296 x_PERSON_ID IN NUMBER,
1297 x_COURSE_CD IN VARCHAR2,
1298 x_TYPE_INSTANCE_ID IN NUMBER,
1299 x_PERSON_TYPE_CODE IN VARCHAR2,
1300 x_CC_VERSION_NUMBER IN NUMBER,
1301 x_FUNNEL_STATUS IN VARCHAR2,
1302 x_ADMISSION_APPL_NUMBER IN NUMBER,
1303 x_NOMINATED_COURSE_CD IN VARCHAR2,
1304 x_NCC_VERSION_NUMBER IN NUMBER,
1305 x_SEQUENCE_NUMBER IN NUMBER,
1306 x_START_DATE IN DATE,
1307 x_END_DATE IN DATE,
1308 x_CREATE_METHOD IN VARCHAR2,
1309 x_ENDED_BY IN NUMBER,
1310 x_END_METHOD IN VARCHAR2,
1311 X_EMPLMNT_CATEGORY_CODE IN VARCHAR2
1312 )
1313 AS
1314 /*************************************************************
1315 Created By :
1316 Date Created By :
1317 Purpose :
1318 Know limitations, enhancements or remarks
1319 Change History
1320 Who When What
1321
1322 (reverse chronological order - newest change first)
1323 ***************************************************************/
1324
1325 cursor c1 is select
1326 PERSON_ID
1327 , COURSE_CD
1328 , PERSON_TYPE_CODE
1329 , CC_VERSION_NUMBER
1330 , FUNNEL_STATUS
1331 , ADMISSION_APPL_NUMBER
1332 , NOMINATED_COURSE_CD
1333 , NCC_VERSION_NUMBER
1334 , SEQUENCE_NUMBER
1335 , START_DATE
1336 , END_DATE
1337 , CREATE_METHOD
1338 , ENDED_BY
1339 , END_METHOD
1340 , EMPLMNT_CATEGORY_CODE
1341 from IGS_PE_TYP_INSTANCES_ALL
1342 where ROWID = X_ROWID
1343 for update nowait;
1344 tlinfo c1%rowtype;
1345 begin
1346 open c1;
1347 fetch c1 into tlinfo;
1348 if (c1%notfound) then
1349 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1350 IGS_GE_MSG_STACK.ADD;
1351 close c1;
1352 app_exception.raise_exception;
1353 return;
1354 end if;
1355 close c1;
1356 if ( ( (tlinfo.PERSON_ID <> X_PERSON_ID)
1357 OR ((tlinfo.PERSON_ID is null)
1358 AND (X_PERSON_ID is null)))
1359 AND ((tlinfo.COURSE_CD <> X_COURSE_CD)
1360 OR ((tlinfo.COURSE_CD is null)
1361 AND (X_COURSE_CD is null)))
1362 AND ((tlinfo.PERSON_TYPE_CODE <> X_PERSON_TYPE_CODE)
1363 OR ((tlinfo.PERSON_TYPE_CODE is null)
1364 AND (X_PERSON_TYPE_CODE is null)))
1365 AND ((tlinfo.CC_VERSION_NUMBER <> X_CC_VERSION_NUMBER)
1366 OR ((tlinfo.CC_VERSION_NUMBER is null)
1367 AND (X_CC_VERSION_NUMBER is null)))
1368 AND ((tlinfo.FUNNEL_STATUS <> X_FUNNEL_STATUS)
1369 OR ((tlinfo.FUNNEL_STATUS is null)
1370 AND (X_FUNNEL_STATUS is null)))
1371 AND ((tlinfo.ADMISSION_APPL_NUMBER <> X_ADMISSION_APPL_NUMBER)
1372 OR ((tlinfo.ADMISSION_APPL_NUMBER is null)
1373 AND (X_ADMISSION_APPL_NUMBER is null)))
1374 AND ((tlinfo.NOMINATED_COURSE_CD <> X_NOMINATED_COURSE_CD)
1375 OR ((tlinfo.NOMINATED_COURSE_CD is null)
1376 AND (X_NOMINATED_COURSE_CD is null)))
1377 AND ((tlinfo.NCC_VERSION_NUMBER <> X_NCC_VERSION_NUMBER)
1378 OR ((tlinfo.NCC_VERSION_NUMBER is null)
1379 AND (X_NCC_VERSION_NUMBER is null)))
1380 AND ((tlinfo.SEQUENCE_NUMBER <> X_SEQUENCE_NUMBER)
1381 OR ((tlinfo.SEQUENCE_NUMBER is null)
1382 AND (X_SEQUENCE_NUMBER is null)))
1383 AND ((tlinfo.START_DATE <> X_START_DATE)
1384 OR ((tlinfo.START_DATE is null)
1385 AND (X_START_DATE is null)))
1386 AND ((tlinfo.END_DATE <> X_END_DATE)
1387 OR ((tlinfo.END_DATE is null)
1388 AND (X_END_DATE is null)))
1389 AND ((tlinfo.CREATE_METHOD <> X_CREATE_METHOD)
1390 OR ((tlinfo.CREATE_METHOD is null)
1391 AND (X_CREATE_METHOD is null)))
1392 AND ((tlinfo.ENDED_BY <> X_ENDED_BY)
1393 OR ((tlinfo.ENDED_BY is null)
1394 AND (X_ENDED_BY is null)))
1395 AND ((tlinfo.END_METHOD <> X_END_METHOD)
1396 OR ((tlinfo.END_METHOD is null)
1397 AND (X_END_METHOD is null)))
1398 AND ((tlinfo.EMPLMNT_CATEGORY_CODE <> X_EMPLMNT_CATEGORY_CODE)
1399 OR ((tlinfo.EMPLMNT_CATEGORY_CODE is null)
1400 AND (X_EMPLMNT_CATEGORY_CODE is null)))
1401 ) then
1402 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1403 IGS_GE_MSG_STACK.ADD;
1404 app_exception.raise_exception;
1405 else
1406 null;
1407 end if;
1408 return;
1409 end LOCK_ROW;
1410
1411 procedure UPDATE_ROW (
1412 X_ROWID in VARCHAR2,
1413 x_PERSON_ID IN NUMBER,
1414 x_COURSE_CD IN VARCHAR2,
1415 x_TYPE_INSTANCE_ID IN NUMBER,
1416 x_PERSON_TYPE_CODE IN VARCHAR2,
1417 x_CC_VERSION_NUMBER IN NUMBER,
1418 x_FUNNEL_STATUS IN VARCHAR2,
1419 x_ADMISSION_APPL_NUMBER IN NUMBER,
1420 x_NOMINATED_COURSE_CD IN VARCHAR2,
1421 x_NCC_VERSION_NUMBER IN NUMBER,
1422 x_SEQUENCE_NUMBER IN NUMBER,
1423 x_START_DATE IN DATE,
1424 x_END_DATE IN DATE,
1425 x_CREATE_METHOD IN VARCHAR2,
1426 x_ENDED_BY IN NUMBER,
1427 x_END_METHOD IN VARCHAR2,
1428 X_MODE in VARCHAR2 ,
1429 X_EMPLMNT_CATEGORY_CODE IN VARCHAR2
1430 ) AS
1431 /*************************************************************
1432 Created By :
1433 Date Created By :
1434 Purpose :
1435 Know limitations, enhancements or remarks
1436 Change History
1437 Who When What
1438
1439 (reverse chronological order - newest change first)
1440 ***************************************************************/
1441
1442 X_LAST_UPDATE_DATE DATE ;
1443 X_LAST_UPDATED_BY NUMBER ;
1444 X_LAST_UPDATE_LOGIN NUMBER ;
1445 begin
1446 X_LAST_UPDATE_DATE := SYSDATE;
1447 if(X_MODE = 'I') then
1448 X_LAST_UPDATED_BY := 1;
1449 X_LAST_UPDATE_LOGIN := 0;
1450 elsif (X_MODE IN ('R', 'S')) then
1451 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1452 if X_LAST_UPDATED_BY is NULL then
1453 X_LAST_UPDATED_BY := -1;
1454 end if;
1455 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1456 if X_LAST_UPDATE_LOGIN is NULL then
1457 X_LAST_UPDATE_LOGIN := -1;
1458 end if;
1459 else
1460 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1461 IGS_GE_MSG_STACK.ADD;
1462 app_exception.raise_exception;
1463 end if;
1464 Before_DML(
1465 p_action=>'UPDATE',
1466 x_rowid=>X_ROWID,
1467 x_person_id=>X_PERSON_ID,
1468 x_course_cd=>X_COURSE_CD,
1469 x_type_instance_id=>X_TYPE_INSTANCE_ID,
1470 x_person_type_code=>X_PERSON_TYPE_CODE,
1471 x_cc_version_number=>X_CC_VERSION_NUMBER,
1472 x_funnel_status=>X_FUNNEL_STATUS,
1473 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
1474 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
1475 x_ncc_version_number=>X_NCC_VERSION_NUMBER,
1476 x_sequence_number=>X_SEQUENCE_NUMBER,
1477 x_start_date=>X_START_DATE,
1478 x_end_date=>X_END_DATE,
1479 x_create_method=>X_CREATE_METHOD,
1480 x_ended_by=>X_ENDED_BY,
1481 x_end_method=>X_END_METHOD,
1482 x_creation_date=>X_LAST_UPDATE_DATE,
1483 x_created_by=>X_LAST_UPDATED_BY,
1484 x_last_update_date=>X_LAST_UPDATE_DATE,
1485 x_last_updated_by=>X_LAST_UPDATED_BY,
1486 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1487 x_emplmnt_category_code => X_EMPLMNT_CATEGORY_CODE
1488 );
1489 IF (x_mode = 'S') THEN
1490 igs_sc_gen_001.set_ctx('R');
1491 END IF;
1492 update IGS_PE_TYP_INSTANCES_ALL set
1493 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1494 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1495 PERSON_TYPE_CODE = NEW_REFERENCES.PERSON_TYPE_CODE,
1496 CC_VERSION_NUMBER = NEW_REFERENCES.CC_VERSION_NUMBER,
1497 FUNNEL_STATUS = NEW_REFERENCES.FUNNEL_STATUS,
1498 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1499 NOMINATED_COURSE_CD = NEW_REFERENCES.NOMINATED_COURSE_CD,
1500 NCC_VERSION_NUMBER = NEW_REFERENCES.NCC_VERSION_NUMBER,
1501 SEQUENCE_NUMBER = NEW_REFERENCES.SEQUENCE_NUMBER,
1502 START_DATE = NEW_REFERENCES.START_DATE,
1503 END_DATE = NEW_REFERENCES.END_DATE,
1504 CREATE_METHOD = NEW_REFERENCES.CREATE_METHOD,
1505 ENDED_BY = NEW_REFERENCES.ENDED_BY,
1506 END_METHOD = NEW_REFERENCES.END_METHOD,
1507 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1508 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1509 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1510 EMPLMNT_CATEGORY_CODE = X_EMPLMNT_CATEGORY_CODE
1511 where ROWID = X_ROWID;
1512 if (sql%notfound) then
1513 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1514 igs_ge_msg_stack.add;
1515 igs_sc_gen_001.unset_ctx('R');
1516 app_exception.raise_exception;
1517 end if;
1518 IF (x_mode = 'S') THEN
1519 igs_sc_gen_001.unset_ctx('R');
1520 END IF;
1521
1522
1523 After_DML (
1524 p_action => 'UPDATE' ,
1525 x_rowid => X_ROWID
1526 );
1527 EXCEPTION
1528 WHEN OTHERS THEN
1529 IF (SQLCODE = (-28115)) THEN
1530 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1531 fnd_message.set_token ('ERR_CD', SQLCODE);
1532 igs_ge_msg_stack.add;
1533 igs_sc_gen_001.unset_ctx('R');
1534 app_exception.raise_exception;
1535 ELSE
1536 igs_sc_gen_001.unset_ctx('R');
1537 RAISE;
1538 END IF;
1539
1540 end UPDATE_ROW;
1541
1542
1543
1544 procedure ADD_ROW (
1545 X_ROWID in out NOCOPY VARCHAR2,
1546 x_PERSON_ID IN NUMBER,
1547 x_COURSE_CD IN VARCHAR2,
1548 x_TYPE_INSTANCE_ID IN OUT NOCOPY NUMBER,
1549 x_PERSON_TYPE_CODE IN VARCHAR2,
1550 x_CC_VERSION_NUMBER IN NUMBER,
1551 x_FUNNEL_STATUS IN VARCHAR2,
1552 x_ADMISSION_APPL_NUMBER IN NUMBER,
1553 x_NOMINATED_COURSE_CD IN VARCHAR2,
1554 x_NCC_VERSION_NUMBER IN NUMBER,
1555 x_SEQUENCE_NUMBER IN NUMBER,
1556 x_START_DATE IN DATE,
1557 x_END_DATE IN DATE,
1558 x_CREATE_METHOD IN VARCHAR2,
1559 x_ENDED_BY IN NUMBER,
1560 x_END_METHOD IN VARCHAR2,
1561 X_MODE in VARCHAR2 ,
1562 X_ORG_ID in NUMBER,
1563 X_EMPLMNT_CATEGORY_CODE IN VARCHAR2
1564 ) AS
1565 /*************************************************************
1566 Created By :
1567 Date Created By :
1568 Purpose :
1569 Know limitations, enhancements or remarks
1570 Change History
1571 Who When What
1572
1573 (reverse chronological order - newest change first)
1574 ***************************************************************/
1575
1576 cursor c1 is select ROWID from IGS_PE_TYP_INSTANCES_ALL
1577 where TYPE_INSTANCE_ID= X_TYPE_INSTANCE_ID
1578 ;
1579 begin
1580 open c1;
1581 fetch c1 into X_ROWID;
1582 if (c1%notfound) then
1583 close c1;
1584 INSERT_ROW (
1585 X_ROWID,
1586 X_PERSON_ID,
1587 X_COURSE_CD,
1588 X_TYPE_INSTANCE_ID,
1589 X_PERSON_TYPE_CODE,
1590 X_CC_VERSION_NUMBER,
1591 X_FUNNEL_STATUS,
1592 X_ADMISSION_APPL_NUMBER,
1593 X_NOMINATED_COURSE_CD,
1594 X_NCC_VERSION_NUMBER,
1595 X_SEQUENCE_NUMBER,
1596 X_START_DATE,
1597 X_END_DATE,
1598 X_CREATE_METHOD,
1599 X_ENDED_BY,
1600 X_END_METHOD,
1601 X_MODE,
1602 X_ORG_ID,
1603 X_EMPLMNT_CATEGORY_CODE);
1604 return;
1605 end if;
1606 close c1;
1607 UPDATE_ROW (
1608 X_ROWID,
1609 X_PERSON_ID,
1610 X_COURSE_CD,
1611 X_TYPE_INSTANCE_ID,
1612 X_PERSON_TYPE_CODE,
1613 X_CC_VERSION_NUMBER,
1614 X_FUNNEL_STATUS,
1615 X_ADMISSION_APPL_NUMBER,
1616 X_NOMINATED_COURSE_CD,
1617 X_NCC_VERSION_NUMBER,
1618 X_SEQUENCE_NUMBER,
1619 X_START_DATE,
1620 X_END_DATE,
1621 X_CREATE_METHOD,
1622 X_ENDED_BY,
1623 X_END_METHOD,
1624 X_MODE,
1625 X_EMPLMNT_CATEGORY_CODE);
1626 end ADD_ROW;
1627
1628
1629 procedure DELETE_ROW (
1630 X_ROWID in VARCHAR2,
1631 x_mode IN VARCHAR2
1632 ) AS
1633 /*************************************************************
1634 Created By :
1635 Date Created By :
1636 Purpose :
1637 Know limitations, enhancements or remarks
1638 Change History
1639 Who When What
1640
1641 (reverse chronological order - newest change first)
1642 ***************************************************************/
1643
1644 begin
1645 Before_DML (
1646 p_action => 'DELETE',
1647 x_rowid => X_ROWID
1648 );
1649 IF (x_mode = 'S') THEN
1650 igs_sc_gen_001.set_ctx('R');
1651 END IF;
1652 delete from IGS_PE_TYP_INSTANCES_ALL
1653 where ROWID = X_ROWID;
1654 if (sql%notfound) then
1655 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1656 igs_ge_msg_stack.add;
1657 igs_sc_gen_001.unset_ctx('R');
1658 app_exception.raise_exception;
1659 end if;
1660 IF (x_mode = 'S') THEN
1661 igs_sc_gen_001.unset_ctx('R');
1662 END IF;
1663
1664 After_DML (
1665 p_action => 'DELETE',
1666 x_rowid => X_ROWID
1667 );
1668 end DELETE_ROW;
1669 END igs_pe_typ_instances_pkg;
[Home] [Help]