1 PACKAGE BODY igs_ad_unit_sets_pkg AS
2 /* $Header: IGSAI98B.pls 120.3 2006/05/30 11:42:19 pbondugu ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_unit_sets%RowType;
6 new_references igs_ad_unit_sets%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_unit_set_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_admission_appl_number IN NUMBER DEFAULT NULL,
14 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_sequence_number IN NUMBER DEFAULT NULL,
16 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
17 x_version_number IN NUMBER DEFAULT NULL,
18 x_rank IN NUMBER DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL
24 ) AS
25
26 /*************************************************************
27 Created By :
28 Date Created By :
29 Purpose :
30 Know limitations, enhancements or remarks
31 Change History
32 Who When What
33
34 (reverse chronological order - newest change first)
35 ***************************************************************/
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_AD_UNIT_SETS
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 Open cur_old_ref_values;
49 Fetch cur_old_ref_values INTO old_references;
50 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
51 Close cur_old_ref_values;
52 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
53 IGS_GE_MSG_STACK.ADD;
54 App_Exception.Raise_Exception;
55 Return;
56 END IF;
57 Close cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.unit_set_id := x_unit_set_id;
61 new_references.person_id := x_person_id;
62 new_references.admission_appl_number := x_admission_appl_number;
63 new_references.nominated_course_cd := x_nominated_course_cd;
64 new_references.sequence_number := x_sequence_number;
65 new_references.unit_set_cd := x_unit_set_cd;
66 new_references.version_number := x_version_number;
67 new_references.rank := x_rank;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78
79 END Set_Column_Values;
80
81 PROCEDURE Check_Constraints (
82 Column_Name IN VARCHAR2 DEFAULT NULL,
83 Column_Value IN VARCHAR2 DEFAULT NULL
84 ) AS
85 /*************************************************************
86 Created By :
87 Date Created By :
88 Purpose :
89 Know limitations, enhancements or remarks
90 Change History
91 Who When What
92
93 (reverse chronological order - newest change first)
94 ***************************************************************/
95 BEGIN
96 IF column_name IS NULL THEN
97 NULL;
98 ELSIF UPPER(column_name) = 'RANK' THEN
99 new_references.rank := IGS_GE_NUMBER.TO_NUM(column_value);
100 NULL;
101 END IF;
102
103 -- The following code checks for check constraints on the Columns.
104 IF Upper(Column_Name) = 'RANK' OR
105 Column_Name IS NULL THEN
106 IF NOT (new_references.rank > 0) THEN
107 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
108 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_SS_DSRD_RANK_NONEGATE'));
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113 END Check_Constraints;
114
115 PROCEDURE Check_Uniqueness AS
116 /*************************************************************
117 Created By :
118 Date Created By :
119 Purpose :
120 Know limitations, enhancements or remarks
121 Change History
122 Who When What
123
124 (reverse chronological order - newest change first)
125 ***************************************************************/
126 begin
127 IF Get_Uk_For_Validation (
128 new_references.sequence_number
129 ,new_references.unit_set_cd
130 ,new_references.version_number
131 ,new_references.admission_appl_number
132 ,new_references.nominated_course_cd
133 ,new_references.person_id
134 ) THEN
135 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
136 IGS_GE_MSG_STACK.ADD;
137 app_exception.raise_exception;
138 END IF;
139 END Check_Uniqueness ;
140
141 PROCEDURE Check_Parent_Existance AS
142 /*************************************************************
143 Created By :
144 Date Created By :
145 Purpose :
146 Know limitations, enhancements or remarks
147 Change History
148 Who When What
149
150 (reverse chronological order - newest change first)
151 ***************************************************************/
152
153 BEGIN
154
155 IF (((old_references.person_id = new_references.person_id) AND
156 (old_references.admission_appl_number = new_references.admission_appl_number) AND
157 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
158 (old_references.sequence_number = new_references.sequence_number)) OR
159 ((new_references.person_id IS NULL) OR
160 (new_references.admission_appl_number IS NULL) OR
161 (new_references.nominated_course_cd IS NULL) OR
162 (new_references.sequence_number IS NULL))) THEN
163 NULL;
164 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation (
165 new_references.person_id,
166 new_references.admission_appl_number,
167 new_references.nominated_course_cd,
168 new_references.sequence_number
169 ) THEN
170 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
171 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_APPL'));
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174 END IF;
175
176 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
177 (old_references.version_number = new_references.version_number)) OR
178 ((new_references.unit_set_cd IS NULL) OR
179 (new_references.version_number IS NULL))) THEN
180 NULL;
181 ELSIF NOT Igs_En_Unit_Set_Pkg.Get_PK_For_Validation (
182 new_references.unit_set_cd,
183 new_references.version_number
184 ) THEN
185 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
186 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_PS_UNIT_SET'));
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190
191 END Check_Parent_Existance;
192
193 FUNCTION Get_PK_For_Validation (
194 x_unit_set_id IN NUMBER
195 ) RETURN BOOLEAN AS
196
197 /*************************************************************
198 Created By :
199 Date Created By :
200 Purpose :
201 Know limitations, enhancements or remarks
202 Change History
203 Who When What
204
205 (reverse chronological order - newest change first)
206 ***************************************************************/
207
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM igs_ad_unit_sets
211 WHERE unit_set_id = x_unit_set_id
212 FOR UPDATE NOWAIT;
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 Open cur_rowid;
219 Fetch cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 Close cur_rowid;
222 Return(TRUE);
223 ELSE
224 Close cur_rowid;
225 Return(FALSE);
226 END IF;
227 END Get_PK_For_Validation;
228
229 FUNCTION Get_UK_For_Validation (
230 x_sequence_number IN NUMBER,
231 x_unit_set_cd IN VARCHAR2,
232 x_version_number IN NUMBER,
233 x_admission_appl_number IN NUMBER,
234 x_nominated_course_cd IN VARCHAR2,
235 x_person_id IN NUMBER
236 ) RETURN BOOLEAN AS
237
238 /*************************************************************
239 Created By :
240 Date Created By :
241 Purpose :
242 Know limitations, enhancements or remarks
243 Change History
244 Who When What
245
246 (reverse chronological order - newest change first)
247 ***************************************************************/
248
249 CURSOR cur_rowid IS
250 SELECT rowid
251 FROM igs_ad_unit_sets
252 WHERE sequence_number = x_sequence_number
253 AND unit_set_cd = x_unit_set_cd
254 AND version_number = x_version_number
255 AND admission_appl_number = x_admission_appl_number
256 AND nominated_course_cd = x_nominated_course_cd
257 AND person_id = x_person_id and ((l_rowid is null) or (rowid <> l_rowid))
258
259 ;
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 Open cur_rowid;
265 Fetch cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 Close cur_rowid;
268 return (true);
269 ELSE
270 close cur_rowid;
271 return(false);
272 END IF;
273 END Get_UK_For_Validation ;
274
275 PROCEDURE Get_FK_Igs_Ad_Ps_Appl_Inst (
276 x_person_id IN NUMBER,
277 x_admission_appl_number IN NUMBER,
278 x_nominated_course_cd IN VARCHAR2,
279 x_sequence_number IN NUMBER
280 ) AS
281
282 /*************************************************************
283 Created By :
284 Date Created By :
285 Purpose :
286 Know limitations, enhancements or remarks
287 Change History
288 Who When What
289
290 (reverse chronological order - newest change first)
291 ***************************************************************/
292
293 CURSOR cur_rowid IS
294 SELECT rowid
295 FROM igs_ad_unit_sets
296 WHERE person_id = x_person_id
297 AND admission_appl_number = x_admission_appl_number
298 AND nominated_course_cd = x_nominated_course_cd
299 AND sequence_number = x_sequence_number ;
300
301 lv_rowid cur_rowid%RowType;
302
303 BEGIN
304
305 Open cur_rowid;
306 Fetch cur_rowid INTO lv_rowid;
307 IF (cur_rowid%FOUND) THEN
308 Close cur_rowid;
309 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUTS_ACAI_FK');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 Return;
313 END IF;
314 Close cur_rowid;
315
316 END Get_FK_Igs_Ad_Ps_Appl_Inst;
317
318 PROCEDURE Get_FK_Igs_En_Unit_Set (
319 x_unit_set_cd IN VARCHAR2,
320 x_version_number IN NUMBER
321 ) AS
322 /*************************************************************
323 Created By :
324 Date Created By :
325 Purpose :
326 Know limitations, enhancements or remarks
327 Change History
328 Who When What
329
330 (reverse chronological order - newest change first)
331 ***************************************************************/
332 CURSOR cur_rowid IS
333 SELECT rowid
334 FROM igs_ad_unit_sets
335 WHERE unit_set_cd = x_unit_set_cd
336 AND version_number = x_version_number ;
337
338 lv_rowid cur_rowid%RowType;
339
340 BEGIN
341 Open cur_rowid;
342 Fetch cur_rowid INTO lv_rowid;
343 IF (cur_rowid%FOUND) THEN
344 Close cur_rowid;
345 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUTS_EUS_FK');
346 IGS_GE_MSG_STACK.ADD;
347 App_Exception.Raise_Exception;
348 Return;
349 END IF;
350 Close cur_rowid;
351
352 END Get_FK_Igs_En_Unit_Set;
353 -- begin oxford unit set code bug 5194658
354 PROCEDURE GET_FK_IGS_PS_OFR_UNIT_SET (
355 x_unit_set_cd IN VARCHAR2,
356 x_version_number IN NUMBER,
357 x_course_cd VARCHAR2,
358 x_crv_version_number NUMBER,
359 x_acad_cal_type VARCHAR2
360 ) AS
361 /*************************************************************
362 Created By :
363 Date Created By :
364 Purpose :
365 Know limitations, enhancements or remarks
366 Change History
367 Who When What
368
369 (reverse chronological order - newest change first)
370 ***************************************************************/
371 CURSOR cur_rowid IS
372 SELECT aus.rowid
373 FROM igs_ad_unit_sets aus, igs_ad_appl_all apl, igs_ad_ps_appl_inst_all inst
374 WHERE aus.person_id = apl.person_id
375 AND aus.admission_appl_number = apl.admission_appl_number
376 AND aus.person_id = inst.person_id
377 AND aus.admission_appl_number = inst.admission_appl_number
378 AND aus.nominated_course_cd = inst.nominated_course_cd
379 AND aus.sequence_number = inst.sequence_number
380 AND aus.unit_set_cd = x_unit_set_cd
381 AND aus.version_number = x_version_number
382 AND aus.nominated_course_cd = x_course_cd
383 AND inst.crv_version_number = x_crv_version_number
384 AND apl.acad_cal_type = x_acad_cal_type ;
385
386 lv_rowid cur_rowid%RowType;
387
388 BEGIN
389 Open cur_rowid;
390 Fetch cur_rowid INTO lv_rowid;
391 IF (cur_rowid%FOUND) THEN
392 Close cur_rowid;
393 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUTS_EUS_FK');
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 Return;
397 END IF;
398 Close cur_rowid;
399
400 END GET_FK_IGS_PS_OFR_UNIT_SET;
401 -- end oxford unit set code bug 5194658
402
403 FUNCTION Validate_Unit_Set(p_version_number igs_ad_unit_sets.version_number%TYPE
404 ,p_unit_set_cd igs_ad_unit_sets.unit_set_cd%TYPE
405 ,p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE
406 ,p_crv_version_number igs_ad_ps_appl_inst_all.crv_version_number%TYPE
407 ,p_admission_cat igs_ad_appl_all.admission_cat%TYPE
408 ,p_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE
409 ,p_location_cd igs_ad_ps_appl_inst_all.location_cd%TYPE
410 ,p_attendance_mode igs_ad_ps_appl_inst_all.attendance_mode%TYPE
411 ,p_attendance_type igs_ad_ps_appl_inst_all.attendance_type%TYPE)
412 RETURN BOOLEAN
413 IS
414 CURSOR cur_unit_sets IS
415 SELECT '1'
416 FROM IGS_PS_OFR_OPT_UNIT_SET_V psusv
417 WHERE psusv.unit_set_cd = p_unit_set_cd -- extra condition, when compared to unit set lov query on IGSAD097.pld, IGSAD046.pld
418 AND psusv.us_version_number = p_version_number -- extra condition, when compared to unit set lov query on IGSAD097.pld, IGSAD046.pld
419 AND psusv.course_cd = p_nominated_course_cd
420 AND psusv.crv_version_number = p_crv_version_number
421 AND psusv.cal_type = p_acad_cal_type
422 AND psusv.location_cd = NVL(p_location_cd , psusv.location_cd)
423 AND psusv.attendance_mode = NVL(p_attendance_mode, psusv.attendance_mode)
424 AND psusv.attendance_type = NVL(p_attendance_type, psusv.attendance_type)
425 AND NOT EXISTS
426 (SELECT 1
427 FROM igs_ps_coo_ad_unit_s psus
428 WHERE psus.course_cd = psusv.course_cd
429 AND psus.crv_version_number = psusv.crv_version_number
430 AND psus.cal_type = psusv.cal_type
431 AND psus.location_cd = psusv.location_cd
432 AND psus.attendance_mode = psusv.attendance_mode
433 AND psus.attendance_type = psusv.attendance_type
434 AND psus.admission_cat = p_admission_cat
435 )
436 AND psusv.UNIT_SET_STATUS IN
437 (SELECT unit_set_status
438 FROM igs_en_unit_set_stat uss
439 WHERE psusv.unit_set_status = uss.unit_set_status
440 AND uss.s_unit_set_status <> 'INACTIVE'
441 )
442 AND psusv.unit_set_cat IN
443 (SELECT usc.unit_set_cat
444 FROM igs_en_unit_set_cat usc
445 WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
446 OR usc.s_unit_set_cat = 'PRENRL_YR'))
447 )
448 AND psusv.expiry_dt IS NULL
449 UNION
450 SELECT '1'
451 FROM igs_ps_coo_ad_unit_s psus,
452 igs_en_unit_set us
453 WHERE psus.unit_set_cd = p_unit_set_cd -- extra condition, when compared to unit set lov query on IGSAD097.pld, IGSAD046.pld
454 AND psus.us_version_number = p_version_number -- extra condition, when compared to unit set lov query on IGSAD097.pld, IGSAD046.pld
455 AND us.unit_set_cd = psus.unit_set_cd
456 AND us.version_number = psus.us_version_number
457 AND psus.course_cd = p_nominated_course_cd
458 AND psus.crv_version_number = p_crv_version_number
459 AND psus.cal_type = p_acad_cal_type
460 AND psus.location_cd = nvl(p_location_cd , psus.location_cd)
461 AND psus.attendance_mode = nvl(p_attendance_mode, psus.attendance_mode)
462 AND psus.attendance_type = nvl(p_attendance_type , psus.attendance_type)
463 AND psus.admission_cat = p_admission_cat
464 AND us.unit_set_status IN
465 (SELECT unit_set_status
466 FROM igs_en_unit_set_stat uss
467 WHERE us.unit_set_status = uss.unit_set_status
468 AND uss.s_unit_set_status <> 'INACTIVE'
469 )
470 AND us.unit_set_cat IN
471 (SELECT usc.unit_set_cat
472 FROM igs_en_unit_set_cat usc
473 WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
474 OR usc.s_unit_set_cat = 'PRENRL_YR'))
475 )
476 AND us.expiry_dt IS NULL;
477
478 l_var VARCHAR2(1);
479 BEGIN
480
481 OPEN cur_unit_sets;
482 FETCH cur_unit_sets INTO l_var;
483 IF cur_unit_sets%NOTFOUND THEN
484 CLOSE cur_unit_sets;
485 RETURN FALSE;
486 END IF;
487 CLOSE cur_unit_sets;
488 RETURN TRUE;
489 END;
490
491
492 PROCEDURE Before_DML (
493 p_action IN VARCHAR2,
494 x_rowid IN VARCHAR2 DEFAULT NULL,
495 x_unit_set_id IN NUMBER DEFAULT NULL,
496 x_person_id IN NUMBER DEFAULT NULL,
497 x_admission_appl_number IN NUMBER DEFAULT NULL,
498 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
499 x_sequence_number IN NUMBER DEFAULT NULL,
500 x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
501 x_version_number IN NUMBER DEFAULT NULL,
502 x_rank IN NUMBER DEFAULT NULL,
503 x_creation_date IN DATE DEFAULT NULL,
504 x_created_by IN NUMBER DEFAULT NULL,
505 x_last_update_date IN DATE DEFAULT NULL,
506 x_last_updated_by IN NUMBER DEFAULT NULL,
507 x_last_update_login IN NUMBER DEFAULT NULL
508 ) AS
509 /*************************************************************
510 Created By :
511 Date Created By :
512 Purpose :
513 Know limitations, enhancements or remarks
514 Change History
515 Who When What
516
517 (reverse chronological order - newest change first)
518 ***************************************************************/
519 CURSOR c_appl IS
520 SELECT appinsti.crv_version_number
521 ,apli.admission_cat
522 ,apli.acad_cal_type
523 ,appinsti.attendance_type
524 ,appinsti.attendance_mode
525 ,appinsti.location_cd
526 FROM igs_ad_appl_all apli
527 ,igs_ad_ps_appl_inst_all appinsti
528 WHERE appinsti.person_id = x_person_id
529 AND appinsti.nominated_course_cd =x_nominated_course_cd
530 AND appinsti.admission_appl_number= x_admission_appl_number
531 AND appinsti.sequence_number = x_sequence_number
532 AND apli.person_id = appinsti.person_id
533 AND apli.admission_appl_number = appinsti.admission_appl_number;
534 c_appl_rec c_appl%ROWTYPE;
535
536
537
538
539
540 BEGIN
541
542 Set_Column_Values (
543 p_action,
544 x_rowid,
545 x_unit_set_id,
546 x_person_id,
547 x_admission_appl_number,
548 x_nominated_course_cd,
549 x_sequence_number,
550 x_unit_set_cd,
551 x_version_number,
552 x_rank,
553 x_creation_date,
554 x_created_by,
555 x_last_update_date,
556 x_last_updated_by,
557 x_last_update_login
558 );
559
560 igs_ad_gen_002.check_adm_appl_inst_stat(
561 nvl(x_person_id,old_references.person_id),
562 nvl(x_admission_appl_number,old_references.admission_appl_number),
563 nvl(x_nominated_course_cd,old_references.nominated_course_cd),
564 nvl(x_sequence_number,old_references.sequence_number)
565 );
566
567 OPEN c_appl;
568 FETCH c_appl INTO c_appl_rec;
569 CLOSE c_appl;
570
571 IF (p_action = 'INSERT') THEN
572 -- Call all the procedures related to Before Insert.
573 IF Get_Pk_For_Validation(
574 new_references.unit_set_id) THEN
575 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
576 IGS_GE_MSG_STACK.ADD;
577 App_Exception.Raise_Exception;
578 END IF;
579
580 IF NOT Validate_Unit_Set(
581 new_references.version_number
582 ,new_references.unit_set_cd
583 ,new_references.nominated_course_cd
584 ,c_appl_rec.crv_version_number
585 ,c_appl_rec.admission_cat
586 ,c_appl_rec.acad_cal_type
587 ,c_appl_rec.location_cd
588 ,c_appl_rec.attendance_mode
589 ,c_appl_rec.attendance_type
590 ) THEN
591 Fnd_Message.Set_name('IGS','IGS_AD_PRGOFOP_NOT_VALID');
592 IGS_GE_MSG_STACK.ADD;
593 App_Exception.Raise_Exception;
594 END IF;
595
596 Check_Uniqueness;
597 Check_Constraints;
598 Check_Parent_Existance;
599 ELSIF (p_action = 'UPDATE') THEN
600 -- Call all the procedures related to Before Update.
601 IF NOT Validate_Unit_Set(
602 new_references.version_number
603 ,new_references.unit_set_cd
604 ,new_references.nominated_course_cd
605 ,c_appl_rec.crv_version_number
606 ,c_appl_rec.admission_cat
607 ,c_appl_rec.acad_cal_type
608 ,c_appl_rec.location_cd
609 ,c_appl_rec.attendance_mode
610 ,c_appl_rec.attendance_type
611 ) THEN
612 Fnd_Message.Set_name('IGS','IGS_AD_PRGOFOP_NOT_VALID');
613 IGS_GE_MSG_STACK.ADD;
614 App_Exception.Raise_Exception;
615 END IF;
616 Check_Uniqueness;
617 Check_Constraints;
618 Check_Parent_Existance;
619 ELSIF (p_action = 'DELETE') THEN
620 -- Call all the procedures related to Before Delete.
621 Null;
622 ELSIF (p_action = 'VALIDATE_INSERT') THEN
623 -- Call all the procedures related to Before Insert.
624 IF Get_PK_For_Validation (
625 new_references.unit_set_id) THEN
626 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
627 IGS_GE_MSG_STACK.ADD;
628 App_Exception.Raise_Exception;
629 END IF;
630
631 Check_Uniqueness;
632 Check_Constraints;
633
634 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
635
636 Check_Uniqueness;
637 Check_Constraints;
638 ELSIF (p_action = 'VALIDATE_DELETE') THEN
639 Null;
640 END IF;
641 l_rowid := NULL; --Bug:2863832
642 END Before_DML;
643
644 PROCEDURE After_DML (
645 p_action IN VARCHAR2,
646 x_rowid IN VARCHAR2
647 ) IS
648 /*************************************************************
649 Created By :
650 Date Created By :
651 Purpose :
652 Know limitations, enhancements or remarks
653 Change History
654 Who When What
655
656 (reverse chronological order - newest change first)
657 ***************************************************************/
658
659 BEGIN
660
661 l_rowid := x_rowid;
662
663 IF (p_action = 'INSERT') THEN
664 -- Call all the procedures related to After Insert.
665 Null;
666 ELSIF (p_action = 'UPDATE') THEN
667 -- Call all the procedures related to After Update.
668 Null;
669 ELSIF (p_action = 'DELETE') THEN
670 -- Call all the procedures related to After Delete.
671 Null;
672 END IF;
673
674 l_rowid:=NULL;
675 END After_DML;
676
677 procedure INSERT_ROW (
678 X_ROWID in out NOCOPY VARCHAR2,
679 x_UNIT_SET_ID IN OUT NOCOPY NUMBER,
680 x_PERSON_ID IN NUMBER,
681 x_ADMISSION_APPL_NUMBER IN NUMBER,
682 x_NOMINATED_COURSE_CD IN VARCHAR2,
683 x_SEQUENCE_NUMBER IN NUMBER,
684 x_UNIT_SET_CD IN VARCHAR2,
685 x_VERSION_NUMBER IN NUMBER,
686 x_RANK IN NUMBER,
687 X_MODE in VARCHAR2
688 ) AS
689 /*************************************************************
690 Created By :
691 Date Created By :
692 Purpose :
693 Know limitations, enhancements or remarks
694 Change History
695 Who When What
696 ravishar 5/30/2005 Security related changes
697
698 (reverse chronological order - newest change first)
699 ***************************************************************/
700
701 cursor C is select ROWID from IGS_AD_UNIT_SETS
702 where UNIT_SET_ID= X_UNIT_SET_ID
703 ;
704 X_LAST_UPDATE_DATE DATE ;
705 X_LAST_UPDATED_BY NUMBER ;
706 X_LAST_UPDATE_LOGIN NUMBER ;
707 X_REQUEST_ID NUMBER;
708 X_PROGRAM_ID NUMBER;
709 X_PROGRAM_APPLICATION_ID NUMBER;
710 X_PROGRAM_UPDATE_DATE DATE;
711 begin
712 X_LAST_UPDATE_DATE := SYSDATE;
713 if(X_MODE = 'I') then
714 X_LAST_UPDATED_BY := 1;
715 X_LAST_UPDATE_LOGIN := 0;
716 elsif (X_MODE IN ('R', 'S')) then
717 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
718 if X_LAST_UPDATED_BY is NULL then
719 X_LAST_UPDATED_BY := -1;
720 end if;
721 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
722 if X_LAST_UPDATE_LOGIN is NULL then
723 X_LAST_UPDATE_LOGIN := -1;
724 end if;
725 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
726 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
727 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
728 if (X_REQUEST_ID = -1) then
729 X_REQUEST_ID := NULL;
730 X_PROGRAM_ID := NULL;
731 X_PROGRAM_APPLICATION_ID := NULL;
732 X_PROGRAM_UPDATE_DATE := NULL;
733 else
734 X_PROGRAM_UPDATE_DATE := SYSDATE;
735 end if;
736 else
737 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
738 IGS_GE_MSG_STACK.ADD;
739 app_exception.raise_exception;
740 end if;
741
742 X_UNIT_SET_ID := -1;
743 Before_DML(
744 p_action=>'INSERT',
745 x_rowid=>X_ROWID,
746 x_unit_set_id=>X_UNIT_SET_ID,
747 x_person_id=>X_PERSON_ID,
748 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
749 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
750 x_sequence_number=>X_SEQUENCE_NUMBER,
751 x_unit_set_cd=>X_UNIT_SET_CD,
752 x_version_number=>X_VERSION_NUMBER,
753 x_rank=>X_RANK,
754 x_creation_date=>X_LAST_UPDATE_DATE,
755 x_created_by=>X_LAST_UPDATED_BY,
756 x_last_update_date=>X_LAST_UPDATE_DATE,
757 x_last_updated_by=>X_LAST_UPDATED_BY,
758 x_last_update_login=>X_LAST_UPDATE_LOGIN);
759 IF (x_mode = 'S') THEN
760 igs_sc_gen_001.set_ctx('R');
761 END IF;
762 insert into IGS_AD_UNIT_SETS (
763 UNIT_SET_ID
764 ,PERSON_ID
765 ,ADMISSION_APPL_NUMBER
766 ,NOMINATED_COURSE_CD
767 ,SEQUENCE_NUMBER
768 ,UNIT_SET_CD
769 ,VERSION_NUMBER
770 ,RANK
771 ,CREATION_DATE
772 ,CREATED_BY
773 ,LAST_UPDATE_DATE
774 ,LAST_UPDATED_BY
775 ,LAST_UPDATE_LOGIN
776 ,REQUEST_ID
777 ,PROGRAM_ID
778 ,PROGRAM_APPLICATION_ID
779 ,PROGRAM_UPDATE_DATE
780 ) values (
781 IGS_AD_UNIT_SETS_S.NEXTVAL
782 ,NEW_REFERENCES.PERSON_ID
783 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
784 ,NEW_REFERENCES.NOMINATED_COURSE_CD
785 ,NEW_REFERENCES.SEQUENCE_NUMBER
786 ,NEW_REFERENCES.UNIT_SET_CD
787 ,NEW_REFERENCES.VERSION_NUMBER
788 ,NEW_REFERENCES.RANK
789 ,X_LAST_UPDATE_DATE
790 ,X_LAST_UPDATED_BY
791 ,X_LAST_UPDATE_DATE
792 ,X_LAST_UPDATED_BY
793 ,X_LAST_UPDATE_LOGIN
794 ,X_REQUEST_ID
795 ,X_PROGRAM_ID
796 ,X_PROGRAM_APPLICATION_ID
797 ,X_PROGRAM_UPDATE_DATE
798 )RETURNING UNIT_SET_ID INTO X_UNIT_SET_ID;
799 IF (x_mode = 'S') THEN
800 igs_sc_gen_001.unset_ctx('R');
801 END IF;
802
803 open c;
804 fetch c into X_ROWID;
805 if (c%notfound) then
806 close c;
807 raise no_data_found;
808 end if;
809 close c;
810 After_DML (
811 p_action => 'INSERT' ,
812 x_rowid => X_ROWID );
813 EXCEPTION
814 WHEN OTHERS THEN
815 IF (x_mode = 'S') THEN
816 igs_sc_gen_001.unset_ctx('R');
817 END IF;
818 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
819 -- Code to handle Security Policy error raised
820 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
821 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
822 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
823 -- that the ownerof policy function does not have privilege to access.
824 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
825 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
826 IGS_GE_MSG_STACK.ADD;
827 app_exception.raise_exception;
828 ELSE
829 RAISE;
830 END IF;
831 end INSERT_ROW;
832 procedure LOCK_ROW (
833 X_ROWID in VARCHAR2,
834 x_UNIT_SET_ID IN NUMBER,
835 x_PERSON_ID IN NUMBER,
836 x_ADMISSION_APPL_NUMBER IN NUMBER,
837 x_NOMINATED_COURSE_CD IN VARCHAR2,
838 x_SEQUENCE_NUMBER IN NUMBER,
839 x_UNIT_SET_CD IN VARCHAR2,
840 x_VERSION_NUMBER IN NUMBER,
841 x_RANK IN NUMBER ) AS
842 /*************************************************************
843 Created By :
844 Date Created By :
845 Purpose :
846 Know limitations, enhancements or remarks
847 Change History
848 Who When What
849
850 (reverse chronological order - newest change first)
851 ***************************************************************/
852
853 cursor c1 is select
854 PERSON_ID
855 , ADMISSION_APPL_NUMBER
856 , NOMINATED_COURSE_CD
857 , SEQUENCE_NUMBER
858 , UNIT_SET_CD
859 , VERSION_NUMBER
860 , RANK
861 from IGS_AD_UNIT_SETS
862 where ROWID = X_ROWID
863 for update nowait;
864 tlinfo c1%rowtype;
865 begin
866 open c1;
867 fetch c1 into tlinfo;
868 if (c1%notfound) then
869 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
870 IGS_GE_MSG_STACK.ADD;
871 close c1;
872 app_exception.raise_exception;
873 return;
874 end if;
875 close c1;
876 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
877 AND (tlinfo.ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER)
878 AND (tlinfo.NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD)
879 AND (tlinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
880 AND (tlinfo.UNIT_SET_CD = X_UNIT_SET_CD)
881 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
882 AND (tlinfo.RANK = X_RANK)
883 ) then
884 null;
885 else
886 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
887 IGS_GE_MSG_STACK.ADD;
888 app_exception.raise_exception;
889 end if;
890 return;
891 end LOCK_ROW;
892 Procedure UPDATE_ROW (
893 X_ROWID in VARCHAR2,
894 x_UNIT_SET_ID IN NUMBER,
895 x_PERSON_ID IN NUMBER,
896 x_ADMISSION_APPL_NUMBER IN NUMBER,
897 x_NOMINATED_COURSE_CD IN VARCHAR2,
898 x_SEQUENCE_NUMBER IN NUMBER,
899 x_UNIT_SET_CD IN VARCHAR2,
900 x_VERSION_NUMBER IN NUMBER,
901 x_RANK IN NUMBER,
902 X_MODE in VARCHAR2
903 ) AS
904 /*************************************************************
905 Created By :
906 Date Created By :
907 Purpose :
908 Know limitations, enhancements or remarks
909 Change History
910 Who When What
911 ravishar 5/30/2005 Security related changes
912
913 (reverse chronological order - newest change first)
914 ***************************************************************/
915
916 X_LAST_UPDATE_DATE DATE ;
917 X_LAST_UPDATED_BY NUMBER ;
918 X_LAST_UPDATE_LOGIN NUMBER ;
919 X_REQUEST_ID NUMBER;
920 X_PROGRAM_ID NUMBER;
921 X_PROGRAM_APPLICATION_ID NUMBER;
922 X_PROGRAM_UPDATE_DATE DATE;
923 begin
924 X_LAST_UPDATE_DATE := SYSDATE;
925 if(X_MODE = 'I') then
926 X_LAST_UPDATED_BY := 1;
927 X_LAST_UPDATE_LOGIN := 0;
928 elsif (X_MODE IN ('R', 'S')) then
929 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
930 if X_LAST_UPDATED_BY is NULL then
931 X_LAST_UPDATED_BY := -1;
932 end if;
933 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
934 if X_LAST_UPDATE_LOGIN is NULL then
935 X_LAST_UPDATE_LOGIN := -1;
936 end if;
937 else
938 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
939 IGS_GE_MSG_STACK.ADD;
940 app_exception.raise_exception;
941 end if;
942 Before_DML(
943 p_action=>'UPDATE',
944 x_rowid=>X_ROWID,
945 x_unit_set_id=>X_UNIT_SET_ID,
946 x_person_id=>X_PERSON_ID,
947 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
948 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
949 x_sequence_number=>X_SEQUENCE_NUMBER,
950 x_unit_set_cd=>X_UNIT_SET_CD,
951 x_version_number=>X_VERSION_NUMBER,
952 x_rank=>X_RANK,
953 x_creation_date=>X_LAST_UPDATE_DATE,
954 x_created_by=>X_LAST_UPDATED_BY,
955 x_last_update_date=>X_LAST_UPDATE_DATE,
956 x_last_updated_by=>X_LAST_UPDATED_BY,
957 x_last_update_login=>X_LAST_UPDATE_LOGIN);
958
959 if (X_MODE IN ('R', 'S')) then
960 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
961 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
962 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
963 if (X_REQUEST_ID = -1) then
964 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
965 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
966 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
967 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
968 else
969 X_PROGRAM_UPDATE_DATE := SYSDATE;
970 end if;
971 end if;
972
973 IF (x_mode = 'S') THEN
974 igs_sc_gen_001.set_ctx('R');
975 END IF;
976 update IGS_AD_UNIT_SETS set
977 PERSON_ID = NEW_REFERENCES.PERSON_ID,
978 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
979 NOMINATED_COURSE_CD = NEW_REFERENCES.NOMINATED_COURSE_CD,
980 SEQUENCE_NUMBER = NEW_REFERENCES.SEQUENCE_NUMBER,
981 UNIT_SET_CD = NEW_REFERENCES.UNIT_SET_CD,
982 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
983 RANK = NEW_REFERENCES.RANK,
984 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
985 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
986 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
987 , REQUEST_ID = X_REQUEST_ID,
988 PROGRAM_ID = X_PROGRAM_ID,
989 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
990 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
991 where ROWID = X_ROWID;
992 if (sql%notfound) then
993 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
994 igs_ge_msg_stack.add;
995 IF (x_mode = 'S') THEN
996 igs_sc_gen_001.unset_ctx('R');
997 END IF;
998 app_exception.raise_exception;
999 end if;
1000 IF (x_mode = 'S') THEN
1001 igs_sc_gen_001.unset_ctx('R');
1002 END IF;
1003
1004
1005 After_DML (
1006 p_action => 'UPDATE' ,
1007 x_rowid => X_ROWID
1008 );
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 IF (x_mode = 'S') THEN
1012 igs_sc_gen_001.unset_ctx('R');
1013 END IF;
1014 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1015 -- Code to handle Security Policy error raised
1016 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1017 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1018 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1019 -- that the ownerof policy function does not have privilege to access.
1020 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1021 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1022 IGS_GE_MSG_STACK.ADD;
1023 app_exception.raise_exception;
1024 ELSE
1025 RAISE;
1026 END IF;
1027 end UPDATE_ROW;
1028 procedure ADD_ROW (
1029 X_ROWID in out NOCOPY VARCHAR2,
1030 x_UNIT_SET_ID IN OUT NOCOPY NUMBER,
1031 x_PERSON_ID IN NUMBER,
1032 x_ADMISSION_APPL_NUMBER IN NUMBER,
1033 x_NOMINATED_COURSE_CD IN VARCHAR2,
1034 x_SEQUENCE_NUMBER IN NUMBER,
1035 x_UNIT_SET_CD IN VARCHAR2,
1036 x_VERSION_NUMBER IN NUMBER,
1037 x_RANK IN NUMBER,
1038 X_MODE in VARCHAR2
1039 ) AS
1040 /*************************************************************
1041 Created By :
1042 Date Created By :
1043 Purpose :
1044 Know limitations, enhancements or remarks
1045 Change History
1046 Who When What
1047
1048 (reverse chronological order - newest change first)
1049 ***************************************************************/
1050
1051 cursor c1 is select ROWID from IGS_AD_UNIT_SETS
1052 where UNIT_SET_ID= X_UNIT_SET_ID
1053 ;
1054 begin
1055 open c1;
1056 fetch c1 into X_ROWID;
1057 if (c1%notfound) then
1058 close c1;
1059 INSERT_ROW (
1060 X_ROWID,
1061 X_UNIT_SET_ID,
1062 X_PERSON_ID,
1063 X_ADMISSION_APPL_NUMBER,
1064 X_NOMINATED_COURSE_CD,
1065 X_SEQUENCE_NUMBER,
1066 X_UNIT_SET_CD,
1067 X_VERSION_NUMBER,
1068 X_RANK,
1069 X_MODE );
1070 return;
1071 end if;
1072 close c1;
1073 UPDATE_ROW (
1074 X_ROWID,
1075 X_UNIT_SET_ID,
1076 X_PERSON_ID,
1077 X_ADMISSION_APPL_NUMBER,
1078 X_NOMINATED_COURSE_CD,
1079 X_SEQUENCE_NUMBER,
1080 X_UNIT_SET_CD,
1081 X_VERSION_NUMBER,
1082 X_RANK,
1083 X_MODE );
1084 end ADD_ROW;
1085 procedure DELETE_ROW (
1086 X_ROWID in VARCHAR2,
1087 x_mode IN VARCHAR2
1088 ) AS
1089 /*************************************************************
1090 Created By :
1091 Date Created By :
1092 Purpose :
1093 Know limitations, enhancements or remarks
1094 Change History
1095 Who When What
1096 ravishar 5/30/2005 Security related changes
1097
1098 (reverse chronological order - newest change first)
1099 ***************************************************************/
1100
1101 begin
1102 Before_DML (
1103 p_action => 'DELETE',
1104 x_rowid => X_ROWID
1105 );
1106 IF (x_mode = 'S') THEN
1107 igs_sc_gen_001.set_ctx('R');
1108 END IF;
1109 delete from IGS_AD_UNIT_SETS
1110 where ROWID = X_ROWID;
1111 if (sql%notfound) then
1112 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1113 igs_ge_msg_stack.add;
1114 IF (x_mode = 'S') THEN
1115 igs_sc_gen_001.unset_ctx('R');
1116 END IF;
1117 app_exception.raise_exception;
1118 end if;
1119 IF (x_mode = 'S') THEN
1120 igs_sc_gen_001.unset_ctx('R');
1121 END IF;
1122
1123 After_DML (
1124 p_action => 'DELETE',
1125 x_rowid => X_ROWID
1126 );
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 IF (x_mode = 'S') THEN
1130 igs_sc_gen_001.unset_ctx('R');
1131 END IF;
1132 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1133 -- Code to handle Security Policy error raised
1134 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1135 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1136 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1137 -- that the ownerof policy function does not have privilege to access.
1138 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1139 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1140 IGS_GE_MSG_STACK.ADD;
1141 app_exception.raise_exception;
1142 ELSE
1143 RAISE;
1144 END IF;
1145 end DELETE_ROW;
1146
1147 END igs_ad_unit_sets_pkg;
[Home] [Help]