[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PRD_PS_OF_OPT_PKG
Source
1 package body IGS_AD_PRD_PS_OF_OPT_PKG AS
2 /* $Header: IGSAI31B.pls 115.5 2003/10/30 13:19:48 rghosh ship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PRD_PS_OF_OPT%RowType;
5 new_references IGS_AD_PRD_PS_OF_OPT%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_admission_cat IN VARCHAR2 DEFAULT NULL,
13 x_s_admission_process_type IN VARCHAR2 DEFAULT NULL,
14 x_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_version_number IN NUMBER DEFAULT NULL,
16 x_acad_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_sequence_number IN NUMBER DEFAULT NULL,
18 x_location_cd IN VARCHAR2 DEFAULT NULL,
19 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
20 x_attendance_type IN VARCHAR2 DEFAULT NULL,
21 x_rollover_inclusion_ind IN VARCHAR2 DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL
27 ) AS
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_AD_PRD_PS_OF_OPT
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
43 Close cur_old_ref_values;
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.adm_cal_type := x_adm_cal_type;
53 new_references.adm_ci_sequence_number := x_adm_ci_sequence_number;
54 new_references.admission_cat := x_admission_cat;
55 new_references.s_admission_process_type := x_s_admission_process_type;
56 new_references.course_cd := x_course_cd;
57 new_references.version_number := x_version_number;
58 new_references.acad_cal_type := x_acad_cal_type;
59 new_references.sequence_number := x_sequence_number;
60 new_references.location_cd := x_location_cd;
61 new_references.attendance_mode := x_attendance_mode;
62 new_references.attendance_type := x_attendance_type;
63 new_references.rollover_inclusion_ind := x_rollover_inclusion_ind;
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END Set_Column_Values;
76
77 PROCEDURE BeforeRowInsertUpdateDelete1(
78 p_inserting IN BOOLEAN DEFAULT FALSE,
79 p_updating IN BOOLEAN DEFAULT FALSE,
80 p_deleting IN BOOLEAN DEFAULT FALSE
81 ) AS
82 v_message_name varchar2(30);
83 BEGIN
84 IF p_inserting THEN
85 -- Validate course offering
86 IF IGS_AD_VAL_APCOO.admp_val_apcoo_co(
87 new_references.course_cd,
88 new_references.version_number,
89 new_references.acad_cal_type,
90 new_references.admission_cat,
91 new_references.s_admission_process_type,
92 new_references.adm_cal_type,
93 new_references.adm_ci_sequence_number,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS',v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END IF;
99 END IF;
100 IF p_inserting OR p_updating THEN
101 -- Validate admission period course offering option components
102 IF IGS_AD_VAL_APCOO.admp_val_apcoo_opt(
103 new_references.course_cd,
104 new_references.version_number,
105 new_references.acad_cal_type,
106 new_references.location_cd,
107 new_references.attendance_mode,
108 new_references.attendance_type,
109 new_references.adm_cal_type,
110 new_references.adm_ci_sequence_number,
111 new_references.admission_cat,
112 new_references.s_admission_process_type,
113 v_message_name) = FALSE THEN
114 Fnd_Message.Set_Name('IGS',v_message_name);
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 END IF;
119
120 END BeforeRowInsertUpdateDelete1;
121
122 PROCEDURE AfterRowInsertUpdate2(
123 p_inserting IN BOOLEAN DEFAULT FALSE,
124 p_updating IN BOOLEAN DEFAULT FALSE,
125 p_deleting IN BOOLEAN DEFAULT FALSE
126 ) AS
127 v_message_name VARCHAR2(30);
128 BEGIN
129 -- Cannot call admp_val_apcoo_links because insert sequence number
130 -- is required.
131 -- Save the rowid of the current row.
132 IF p_inserting OR p_updating THEN
133 -- Validate the admission period course offering option
134 IF IGS_AD_VAL_APCOO.admp_val_apcoo_links (
135 new_references.adm_cal_type,
136 new_references.adm_ci_sequence_number,
137 new_references.admission_cat,
138 new_references.s_admission_process_type,
139 new_references.course_cd,
140 new_references.version_number,
141 new_references.acad_cal_type,
142 new_references.sequence_number,
143 new_references.location_cd,
144 new_references.attendance_mode,
145 new_references.attendance_type,
146 v_message_name) = FALSE THEN
147 Fnd_Message.Set_Name('IGS',v_message_name);
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152 END AfterRowInsertUpdate2;
153
154 PROCEDURE Check_Constraints (
155 Column_Name IN VARCHAR2 DEFAULT NULL,
156 Column_Value IN VARCHAR2 DEFAULT NULL
157 )
158 AS
159 BEGIN
160 IF column_name is null then
161 NULL;
162 ELSIF upper(Column_name) = 'ADM_CI_SEQUENCE_NUMBER' then
163 new_references.adm_ci_sequence_number := igs_ge_number.to_num(column_value);
164 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
165 new_references.version_number := igs_ge_number.to_num(column_value);
166 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
167 new_references.sequence_number := igs_ge_number.to_num(column_value);
168 ELSIF upper(Column_name) = 'ROLLOVER_INCLUSION_IND' then
169 new_references.ROLLOVER_INCLUSION_IND := column_value;
170 ELSIF upper(Column_name) = 'ADM_CAL_TYPE' then
171 new_references.ADM_CAL_TYPE := column_value;
172 ELSIF upper(Column_name) = 'ADMISSION_CAT' then
173 new_references.ADMISSION_CAT := column_value;
174 ELSIF upper(Column_name) = 'S_ADMISSION_PROCESS_TYPE' then
175 new_references.S_ADMISSION_PROCESS_TYPE := column_value;
176 ELSIF upper(Column_name) = 'COURSE_CD' then
177 new_references.COURSE_CD := column_value;
178 ELSIF upper(Column_name) = 'ACAD_CAL_TYPE' then
179 new_references.ACAD_CAL_TYPE := column_value;
180 ELSIF upper(Column_name) = 'LOCATION_CD' then
181 new_references.LOCATION_CD := column_value;
182 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
183 new_references.ATTENDANCE_MODE := column_value;
184 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
185 new_references.ATTENDANCE_TYPE := column_value;
186 END IF;
187
188 IF upper(column_name) = 'ADM_CI_SEQUENCE_NUMBER' OR
189 column_name is null Then
190 IF new_references.adm_ci_sequence_number < 1 OR
191 new_references.adm_ci_sequence_number > 999999 Then
192 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197
198 IF upper(column_name) = 'VERSION_NUMBER' OR
199 column_name is null Then
200 IF new_references.version_number < 1 OR
201 new_references.version_number > 999 Then
202 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END IF;
206 END IF;
207
208 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
209 column_name is null Then
210 IF new_references.sequence_number < 1 OR
211 new_references.sequence_number > 999999 Then
212 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
213 IGS_GE_MSG_STACK.ADD;
214 App_Exception.Raise_Exception;
215 END IF;
216 END IF;
217
218 IF upper(column_name) = 'ROLLOVER_INCLUSION_IND' OR
219 column_name is null Then
220 IF new_references.rollover_inclusion_ind NOT IN ( 'Y','N' ) Then
221 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226
227 IF upper(column_name) = 'ADM_CAL_TYPE' OR
228 column_name is null Then
229 IF new_references.adm_cal_type <>
230 UPPER(new_references.adm_cal_type) Then
231 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235 END IF;
236
237 IF upper(column_name) = 'ADMISSION_CAT' OR
238 column_name is null Then
239 IF new_references.admission_cat <>
240 UPPER(new_references.admission_cat) Then
241 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF;
245 END IF;
246
247 IF upper(column_name) = 'S_ADMISSION_PROCESS_TYPE' OR
248 column_name is null Then
249 IF new_references.s_admission_process_type <>
250 UPPER(new_references.s_admission_process_type) Then
251 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
252 IGS_GE_MSG_STACK.ADD;
253 App_Exception.Raise_Exception;
254 END IF;
255 END IF;
256
257 IF upper(column_name) = 'COURSE_CD' OR
258 column_name is null Then
259 IF new_references.course_cd <>
260 UPPER(new_references.course_cd) Then
261 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
262 IGS_GE_MSG_STACK.ADD;
263 App_Exception.Raise_Exception;
264 END IF;
265 END IF;
266
267 IF upper(column_name) = 'ACAD_CAL_TYPE' OR
268 column_name is null Then
269 IF new_references.acad_cal_type <>
270 UPPER(new_references.acad_cal_type) Then
271 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275 END IF;
276
277 IF upper(column_name) = 'LOCATION_CD' OR
278 column_name is null Then
279 IF new_references.location_cd <>
280 UPPER(new_references.location_cd) Then
281 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 END IF;
286
287 IF upper(column_name) = 'ATTENDANCE_MODE' OR
288 column_name is null Then
289 IF new_references.attendance_mode <>
290 UPPER(new_references.attendance_mode) Then
291 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
292 IGS_GE_MSG_STACK.ADD;
293 App_Exception.Raise_Exception;
294 END IF;
295 END IF;
296
297 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
298 column_name is null Then
299 IF new_references.attendance_type <>
300 UPPER(new_references.attendance_type) Then
301 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
302 IGS_GE_MSG_STACK.ADD;
303 App_Exception.Raise_Exception;
304 END IF;
305 END IF;
306 END Check_Constraints;
307
308 PROCEDURE Check_Parent_Existance AS
309 BEGIN
310
311 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
312 ((new_references.attendance_mode IS NULL))) THEN
313 NULL;
314 ELSE
315 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
316 new_references.attendance_mode
317 ) THEN
318 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 END IF;
322 END IF;
323
324 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
325 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
326 (old_references.admission_cat = new_references.admission_cat) AND
327 (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
328 ((new_references.adm_cal_type IS NULL) OR
329 (new_references.adm_ci_sequence_number IS NULL) OR
330 (new_references.admission_cat IS NULL) OR
331 (new_references.s_admission_process_type IS NULL))) THEN
332 NULL;
333 ELSE
334 IF NOT IGS_AD_PRD_AD_PRC_CA_PKG.Get_PK_For_Validation (
335 new_references.adm_cal_type,
336 new_references.adm_ci_sequence_number,
337 new_references.admission_cat,
338 new_references.s_admission_process_type ,
339 'N'
340 ) THEN
341 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END IF;
345 END IF;
346
347 IF (((old_references.attendance_type = new_references.attendance_type)) OR
348 ((new_references.attendance_type IS NULL))) THEN
349 NULL;
350 ELSE
351 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
352 new_references.attendance_type
353 ) THEN
354 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358 END IF;
359
360 IF (((old_references.course_cd = new_references.course_cd) AND
361 (old_references.version_number = new_references.version_number) AND
362 (old_references.acad_cal_type = new_references.acad_cal_type)) OR
363 ((new_references.course_cd IS NULL) OR
364 (new_references.version_number IS NULL) OR
365 (new_references.acad_cal_type IS NULL))) THEN
366 NULL;
367 ELSE
368 IF NOT IGS_PS_OFR_PKG.Get_PK_For_Validation (
369 new_references.course_cd,
370 new_references.version_number,
371 new_references.acad_cal_type
372 ) THEN
373 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 END IF;
378
379 IF (((old_references.location_cd = new_references.location_cd)) OR
380 ((new_references.location_cd IS NULL))) THEN
381 NULL;
382 ELSE
383 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
384 new_references.location_cd , 'N'
385 ) THEN
386 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389 END IF;
390 END IF;
391
392 END Check_Parent_Existance;
393
394 FUNCTION Get_PK_For_Validation (
395 x_adm_cal_type IN VARCHAR2,
396 x_adm_ci_sequence_number IN NUMBER,
397 x_admission_cat IN VARCHAR2,
398 x_s_admission_process_type IN VARCHAR2,
399 x_course_cd IN VARCHAR2,
400 x_version_number IN NUMBER,
401 x_acad_cal_type IN VARCHAR2,
402 x_sequence_number IN NUMBER
403 )
404 RETURN BOOLEAN
405 AS
406 CURSOR cur_rowid IS
407 SELECT rowid
408 FROM IGS_AD_PRD_PS_OF_OPT
409 WHERE adm_cal_type = x_adm_cal_type
410 AND adm_ci_sequence_number = x_adm_ci_sequence_number
411 AND admission_cat = x_admission_cat
412 AND s_admission_process_type = x_s_admission_process_type
413 AND course_cd = x_course_cd
414 AND version_number = x_version_number
415 AND acad_cal_type = x_acad_cal_type
416 AND sequence_number = x_sequence_number
417 FOR UPDATE NOWAIT;
418
419 lv_rowid cur_rowid%RowType;
420
421 BEGIN
422
423 Open cur_rowid;
424 Fetch cur_rowid INTO lv_rowid;
425 IF (cur_rowid%FOUND) THEN
426 Close cur_rowid;
427 Return (TRUE);
428 ELSE
429 Close cur_rowid;
430 Return (FALSE);
431 END IF;
432 END Get_PK_For_Validation;
433
434 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
435 x_attendance_mode IN VARCHAR2
436 ) AS
437
438 CURSOR cur_rowid IS
439 SELECT rowid
440 FROM IGS_AD_PRD_PS_OF_OPT
441 WHERE attendance_mode = x_attendance_mode ;
442
443 lv_rowid cur_rowid%RowType;
444
445 BEGIN
446
447 Open cur_rowid;
448 Fetch cur_rowid INTO lv_rowid;
449 IF (cur_rowid%FOUND) THEN
450 Close cur_rowid;
451 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOO_AM_FK');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 Return;
455 END IF;
456 Close cur_rowid;
457
458 END GET_FK_IGS_EN_ATD_MODE;
459
460 PROCEDURE GET_FK_IGS_AD_PRD_AD_PRC_CA (
461 x_adm_cal_type IN VARCHAR2,
462 x_adm_ci_sequence_number IN NUMBER,
463 x_admission_cat IN VARCHAR2,
464 x_s_admission_process_type IN VARCHAR2
465 ) AS
466
467 CURSOR cur_rowid IS
468 SELECT rowid
469 FROM IGS_AD_PRD_PS_OF_OPT
470 WHERE adm_cal_type = x_adm_cal_type
471 AND adm_ci_sequence_number = x_adm_ci_sequence_number
472 AND admission_cat = x_admission_cat
473 AND s_admission_process_type = x_s_admission_process_type ;
474
475 lv_rowid cur_rowid%RowType;
476
477 BEGIN
478
479 Open cur_rowid;
480 Fetch cur_rowid INTO lv_rowid;
481 IF (cur_rowid%FOUND) THEN
482 Close cur_rowid;
483 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOO_APAPC_FK');
484 IGS_GE_MSG_STACK.ADD;
485 App_Exception.Raise_Exception;
486 Return;
487 END IF;
488 Close cur_rowid;
489
490 END GET_FK_IGS_AD_PRD_AD_PRC_CA;
491
492 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
493 x_attendance_type IN VARCHAR2
494 ) AS
495
496 CURSOR cur_rowid IS
497 SELECT rowid
498 FROM IGS_AD_PRD_PS_OF_OPT
499 WHERE attendance_type = x_attendance_type ;
500
501 lv_rowid cur_rowid%RowType;
502
503 BEGIN
504
505 Open cur_rowid;
506 Fetch cur_rowid INTO lv_rowid;
507 IF (cur_rowid%FOUND) THEN
508 Close cur_rowid;
509 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOO_ATT_FK');
510 IGS_GE_MSG_STACK.ADD;
511 App_Exception.Raise_Exception;
512 Return;
513 END IF;
514 Close cur_rowid;
515
516 END GET_FK_IGS_EN_ATD_TYPE;
517
518 PROCEDURE GET_FK_IGS_PS_OFR (
519 x_course_cd IN VARCHAR2,
520 x_version_number IN NUMBER,
521 x_cal_type IN VARCHAR2
522 ) AS
523
524 CURSOR cur_rowid IS
525 SELECT rowid
526 FROM IGS_AD_PRD_PS_OF_OPT
527 WHERE course_cd = x_course_cd
528 AND version_number = x_version_number
529 AND acad_cal_type = x_cal_type ;
530
531 lv_rowid cur_rowid%RowType;
532
533 BEGIN
534
535 Open cur_rowid;
536 Fetch cur_rowid INTO lv_rowid;
537 IF (cur_rowid%FOUND) THEN
538 Close cur_rowid;
539 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOO_CO_FK');
540 IGS_GE_MSG_STACK.ADD;
541 App_Exception.Raise_Exception;
542 Return;
543 END IF;
544 Close cur_rowid;
545
546 END GET_FK_IGS_PS_OFR;
547
548 PROCEDURE GET_FK_IGS_AD_LOCATION (
549 x_location_cd IN VARCHAR2
550 ) AS
551
552 CURSOR cur_rowid IS
553 SELECT rowid
554 FROM IGS_AD_PRD_PS_OF_OPT
555 WHERE location_cd = x_location_cd ;
556
557 lv_rowid cur_rowid%RowType;
558
559 BEGIN
560
561 Open cur_rowid;
562 Fetch cur_rowid INTO lv_rowid;
563 IF (cur_rowid%FOUND) THEN
564 Close cur_rowid;
565 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOO_LOC_FK');
566 IGS_GE_MSG_STACK.ADD;
567 App_Exception.Raise_Exception;
568 Return;
569 END IF;
570 Close cur_rowid;
571
572 END GET_FK_IGS_AD_LOCATION;
573
574 PROCEDURE Before_DML (
575 p_action IN VARCHAR2,
576 x_rowid IN VARCHAR2 DEFAULT NULL,
577 x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
578 x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
579 x_admission_cat IN VARCHAR2 DEFAULT NULL,
580 x_s_admission_process_type IN VARCHAR2 DEFAULT NULL,
581 x_course_cd IN VARCHAR2 DEFAULT NULL,
582 x_version_number IN NUMBER DEFAULT NULL,
583 x_acad_cal_type IN VARCHAR2 DEFAULT NULL,
584 x_sequence_number IN NUMBER DEFAULT NULL,
585 x_location_cd IN VARCHAR2 DEFAULT NULL,
586 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
587 x_attendance_type IN VARCHAR2 DEFAULT NULL,
588 x_rollover_inclusion_ind IN VARCHAR2 DEFAULT NULL,
589 x_creation_date IN DATE DEFAULT NULL,
590 x_created_by IN NUMBER DEFAULT NULL,
591 x_last_update_date IN DATE DEFAULT NULL,
592 x_last_updated_by IN NUMBER DEFAULT NULL,
593 x_last_update_login IN NUMBER DEFAULT NULL
594 ) AS
595 BEGIN
596
597 Set_Column_Values (
598 p_action,
599 x_rowid,
600 x_adm_cal_type,
601 x_adm_ci_sequence_number,
602 x_admission_cat,
603 x_s_admission_process_type,
604 x_course_cd,
605 x_version_number,
606 x_acad_cal_type,
607 x_sequence_number,
608 x_location_cd,
609 x_attendance_mode,
610 x_attendance_type,
611 x_rollover_inclusion_ind,
612 x_creation_date,
613 x_created_by,
614 x_last_update_date,
615 x_last_updated_by,
616 x_last_update_login
617 );
618
619 IF (p_action = 'INSERT') THEN
620 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
621 IF Get_PK_For_Validation (
622 new_references.adm_cal_type,
623 new_references.adm_ci_sequence_number,
624 new_references.admission_cat,
625 new_references.s_admission_process_type,
626 new_references.course_cd,
627 new_references.version_number,
628 new_references.acad_cal_type,
629 new_references.sequence_number
630 ) THEN
631 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
632 IGS_GE_MSG_STACK.ADD;
633 App_Exception.Raise_Exception;
634 END IF;
635 Check_Constraints;
636 Check_Parent_Existance;
637 ELSIF (p_action = 'UPDATE') THEN
638 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
639 Check_Constraints;
640 Check_Parent_Existance;
641 ELSIF (p_action = 'DELETE') THEN
642 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
643 ELSIF (p_action = 'VALIDATE_INSERT') THEN
644 IF Get_PK_For_Validation (
645 new_references.adm_cal_type,
646 new_references.adm_ci_sequence_number,
647 new_references.admission_cat,
648 new_references.s_admission_process_type,
649 new_references.course_cd,
650 new_references.version_number,
651 new_references.acad_cal_type,
652 new_references.sequence_number
653 ) THEN
654 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
655 IGS_GE_MSG_STACK.ADD;
656 App_Exception.Raise_Exception;
657 END IF;
658 Check_Constraints;
659 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
660 Check_Constraints;
661 END IF;
662 END Before_DML;
663
664 PROCEDURE After_DML (
665 p_action IN VARCHAR2,
666 x_rowid IN VARCHAR2
667 ) AS
668 BEGIN
669
670 l_rowid := x_rowid;
671
672 IF (p_action = 'INSERT') THEN
673 AfterRowInsertUpdate2 ( p_inserting => TRUE );
674 ELSIF (p_action = 'UPDATE') THEN
675 AfterRowInsertUpdate2 ( p_updating => TRUE );
676 END IF;
677
678 END After_DML;
679
680 procedure INSERT_ROW (
681 X_ROWID in out NOCOPY VARCHAR2,
682 X_ADM_CAL_TYPE in VARCHAR2,
683 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
684 X_ADMISSION_CAT in VARCHAR2,
685 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
686 X_COURSE_CD in VARCHAR2,
687 X_VERSION_NUMBER in NUMBER,
688 X_ACAD_CAL_TYPE in VARCHAR2,
689 X_SEQUENCE_NUMBER in NUMBER,
690 X_LOCATION_CD in VARCHAR2,
691 X_ATTENDANCE_MODE in VARCHAR2,
692 X_ATTENDANCE_TYPE in VARCHAR2,
693 X_ROLLOVER_INCLUSION_IND in VARCHAR2,
694 X_MODE in VARCHAR2 default 'R'
695 ) AS
696 cursor C is select ROWID from IGS_AD_PRD_PS_OF_OPT
697 where ADM_CAL_TYPE = X_ADM_CAL_TYPE
698 and ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER
699 and ADMISSION_CAT = X_ADMISSION_CAT
700 and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
701 and COURSE_CD = X_COURSE_CD
702 and VERSION_NUMBER = X_VERSION_NUMBER
703 and ACAD_CAL_TYPE = X_ACAD_CAL_TYPE
704 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
705 X_LAST_UPDATE_DATE DATE;
706 X_LAST_UPDATED_BY NUMBER;
707 X_LAST_UPDATE_LOGIN NUMBER;
708 X_REQUEST_ID NUMBER;
709 X_PROGRAM_ID NUMBER;
710 X_PROGRAM_APPLICATION_ID NUMBER;
711 X_PROGRAM_UPDATE_DATE DATE;
712 begin
713 X_LAST_UPDATE_DATE := SYSDATE;
714 if(X_MODE = 'I') then
715 X_LAST_UPDATED_BY := 1;
716 X_LAST_UPDATE_LOGIN := 0;
717 elsif (X_MODE = 'R') then
718 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
719 if X_LAST_UPDATED_BY is NULL then
720 X_LAST_UPDATED_BY := -1;
721 end if;
722 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
723 if X_LAST_UPDATE_LOGIN is NULL then
724 X_LAST_UPDATE_LOGIN := -1;
725 end if;
726 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
727 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
728 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
729 if (X_REQUEST_ID = -1) then
730 X_REQUEST_ID := NULL;
731 X_PROGRAM_ID := NULL;
732 X_PROGRAM_APPLICATION_ID := NULL;
733 X_PROGRAM_UPDATE_DATE := NULL;
734 else
735 X_PROGRAM_UPDATE_DATE := SYSDATE;
736 end if;
737 else
738 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
739 IGS_GE_MSG_STACK.ADD;
740 app_exception.raise_exception;
741 end if;
742
743 Before_DML(p_action =>'INSERT',
744 x_rowid =>X_ROWID,
745 x_adm_cal_type => X_ADM_CAL_TYPE,
746 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
747 x_admission_cat => X_ADMISSION_CAT,
748 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
749 x_course_cd =>X_COURSE_CD,
750 x_version_number=> X_VERSION_NUMBER,
751 x_acad_cal_type => X_ACAD_CAL_TYPE ,
752 x_sequence_number=> X_SEQUENCE_NUMBER,
753 x_location_cd => X_LOCATION_CD,
754 x_attendance_mode=> X_ATTENDANCE_MODE,
755 x_attendance_type => X_ATTENDANCE_TYPE ,
756 x_rollover_inclusion_ind=> NVL(X_ROLLOVER_INCLUSION_IND,'Y'),
757 x_creation_date => X_LAST_UPDATE_DATE,
758 x_created_by => X_LAST_UPDATED_BY,
759 x_last_update_date => X_LAST_UPDATE_DATE,
760 x_last_updated_by => X_LAST_UPDATED_BY,
761 x_last_update_login => X_LAST_UPDATE_LOGIN
762 );
763
764 insert into IGS_AD_PRD_PS_OF_OPT (
765 ADM_CAL_TYPE,
766 ADM_CI_SEQUENCE_NUMBER,
767 ADMISSION_CAT,
768 S_ADMISSION_PROCESS_TYPE,
769 COURSE_CD,
770 VERSION_NUMBER,
771 ACAD_CAL_TYPE,
772 SEQUENCE_NUMBER,
773 LOCATION_CD,
774 ATTENDANCE_MODE,
775 ATTENDANCE_TYPE,
776 ROLLOVER_INCLUSION_IND,
777 CREATION_DATE,
778 CREATED_BY,
779 LAST_UPDATE_DATE,
780 LAST_UPDATED_BY,
781 LAST_UPDATE_LOGIN,
782 REQUEST_ID,
783 PROGRAM_ID,
784 PROGRAM_APPLICATION_ID,
785 PROGRAM_UPDATE_DATE
786 ) values (
787 NEW_REFERENCES.ADM_CAL_TYPE,
788 NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
789 NEW_REFERENCES.ADMISSION_CAT,
790 NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE,
791 NEW_REFERENCES.COURSE_CD,
792 NEW_REFERENCES.VERSION_NUMBER,
793 NEW_REFERENCES.ACAD_CAL_TYPE,
794 NEW_REFERENCES.SEQUENCE_NUMBER,
795 NEW_REFERENCES.LOCATION_CD,
796 NEW_REFERENCES.ATTENDANCE_MODE,
797 NEW_REFERENCES.ATTENDANCE_TYPE,
798 NEW_REFERENCES.ROLLOVER_INCLUSION_IND,
799 X_LAST_UPDATE_DATE,
800 X_LAST_UPDATED_BY,
801 X_LAST_UPDATE_DATE,
802 X_LAST_UPDATED_BY,
803 X_LAST_UPDATE_LOGIN,
804 X_REQUEST_ID,
805 X_PROGRAM_ID,
806 X_PROGRAM_APPLICATION_ID,
807 X_PROGRAM_UPDATE_DATE
808 );
809
810 open c;
811 fetch c into X_ROWID;
812 if (c%notfound) then
813 close c;
814 raise no_data_found;
815 end if;
816 close c;
817
818 After_DML(
819 p_action =>'INSERT',
820 x_rowid => X_ROWID
821 );
822 end INSERT_ROW;
823
824 procedure LOCK_ROW (
825 X_ROWID in VARCHAR2,
826 X_ADM_CAL_TYPE in VARCHAR2,
827 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
828 X_ADMISSION_CAT in VARCHAR2,
829 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
830 X_COURSE_CD in VARCHAR2,
831 X_VERSION_NUMBER in NUMBER,
832 X_ACAD_CAL_TYPE in VARCHAR2,
833 X_SEQUENCE_NUMBER in NUMBER,
834 X_LOCATION_CD in VARCHAR2,
835 X_ATTENDANCE_MODE in VARCHAR2,
836 X_ATTENDANCE_TYPE in VARCHAR2,
837 X_ROLLOVER_INCLUSION_IND in VARCHAR2
838 ) AS
839 cursor c1 is select
840 LOCATION_CD,
841 ATTENDANCE_MODE,
842 ATTENDANCE_TYPE,
843 ROLLOVER_INCLUSION_IND
844 from IGS_AD_PRD_PS_OF_OPT
845 where ROWID = X_ROWID for update nowait;
846 tlinfo c1%rowtype;
847
848 begin
849 open c1;
850 fetch c1 into tlinfo;
851 if (c1%notfound) then
852 close c1;
853 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
854 IGS_GE_MSG_STACK.ADD;
855 app_exception.raise_exception;
856 return;
857 end if;
858 close c1;
859
860 if ( ((tlinfo.LOCATION_CD = X_LOCATION_CD)
861 OR ((tlinfo.LOCATION_CD is null)
862 AND (X_LOCATION_CD is null)))
863 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
864 OR ((tlinfo.ATTENDANCE_MODE is null)
865 AND (X_ATTENDANCE_MODE is null)))
866 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
867 OR ((tlinfo.ATTENDANCE_TYPE is null)
868 AND (X_ATTENDANCE_TYPE is null)))
869 AND (tlinfo.ROLLOVER_INCLUSION_IND = X_ROLLOVER_INCLUSION_IND)
870 ) then
871 null;
872 else
873 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
874 IGS_GE_MSG_STACK.ADD;
875 app_exception.raise_exception;
876 end if;
877 return;
878 end LOCK_ROW;
879
880 procedure UPDATE_ROW (
881 X_ROWID in VARCHAR2,
882 X_ADM_CAL_TYPE in VARCHAR2,
883 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
884 X_ADMISSION_CAT in VARCHAR2,
885 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
886 X_COURSE_CD in VARCHAR2,
887 X_VERSION_NUMBER in NUMBER,
888 X_ACAD_CAL_TYPE in VARCHAR2,
889 X_SEQUENCE_NUMBER in NUMBER,
890 X_LOCATION_CD in VARCHAR2,
891 X_ATTENDANCE_MODE in VARCHAR2,
892 X_ATTENDANCE_TYPE in VARCHAR2,
893 X_ROLLOVER_INCLUSION_IND in VARCHAR2,
894 X_MODE in VARCHAR2 default 'R'
895 ) AS
896 X_LAST_UPDATE_DATE DATE;
897 X_LAST_UPDATED_BY NUMBER;
898 X_LAST_UPDATE_LOGIN NUMBER;
899 X_REQUEST_ID NUMBER;
900 X_PROGRAM_ID NUMBER;
901 X_PROGRAM_APPLICATION_ID NUMBER;
902 X_PROGRAM_UPDATE_DATE DATE;
903 begin
904 X_LAST_UPDATE_DATE := SYSDATE;
905 if(X_MODE = 'I') then
906 X_LAST_UPDATED_BY := 1;
907 X_LAST_UPDATE_LOGIN := 0;
908 elsif (X_MODE = 'R') then
909 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
910 if X_LAST_UPDATED_BY is NULL then
911 X_LAST_UPDATED_BY := -1;
912 end if;
913 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
914 if X_LAST_UPDATE_LOGIN is NULL then
915 X_LAST_UPDATE_LOGIN := -1;
916 end if;
917 else
918 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
919 IGS_GE_MSG_STACK.ADD;
920 app_exception.raise_exception;
921 end if;
922
923 Before_DML(p_action =>'UPDATE',
924 x_rowid =>X_ROWID,
925 x_adm_cal_type => X_ADM_CAL_TYPE,
926 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
927 x_admission_cat => X_ADMISSION_CAT,
928 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
929 x_course_cd =>X_COURSE_CD ,
930 x_version_number=> X_VERSION_NUMBER,
931 x_acad_cal_type => X_ACAD_CAL_TYPE ,
932 x_sequence_number=> X_SEQUENCE_NUMBER,
933 x_location_cd => X_LOCATION_CD ,
934 x_attendance_mode=> X_ATTENDANCE_MODE,
935 x_attendance_type => X_ATTENDANCE_TYPE ,
936 x_rollover_inclusion_ind=> X_ROLLOVER_INCLUSION_IND,
937 x_creation_date => X_LAST_UPDATE_DATE,
938 x_created_by => X_LAST_UPDATED_BY,
939 x_last_update_date => X_LAST_UPDATE_DATE,
940 x_last_updated_by => X_LAST_UPDATED_BY,
941 x_last_update_login => X_LAST_UPDATE_LOGIN
942 );
943
944 if (X_MODE = 'R') then
945 X_REQUEST_ID :=FND_GLOBAL.CONC_REQUEST_ID;
946 X_PROGRAM_ID :=FND_GLOBAL.CONC_PROGRAM_ID;
947 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
948 if (X_REQUEST_ID = -1) then
949 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
950 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
951 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
952 else
953 X_PROGRAM_UPDATE_DATE := SYSDATE;
954 end if;
955 end if;
956 update IGS_AD_PRD_PS_OF_OPT set
957 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
958 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
959 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
960 ROLLOVER_INCLUSION_IND = NEW_REFERENCES.ROLLOVER_INCLUSION_IND,
961 LAST_UPDATE_DATE = NEW_REFERENCES.LAST_UPDATE_DATE,
962 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
963 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
964 REQUEST_ID = X_REQUEST_ID,
965 PROGRAM_ID = X_PROGRAM_ID,
966 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
967 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
968 where ROWID = X_ROWID
969 ;
970 if (sql%notfound) then
971 raise no_data_found;
972 end if;
973
974 After_DML(
975 p_action =>'UPDATE',
976 x_rowid => X_ROWID
977 );
978
979 end UPDATE_ROW;
980
981 procedure ADD_ROW (
982 X_ROWID in out NOCOPY VARCHAR2,
983 X_ADM_CAL_TYPE in VARCHAR2,
984 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
985 X_ADMISSION_CAT in VARCHAR2,
986 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
987 X_COURSE_CD in VARCHAR2,
988 X_VERSION_NUMBER in NUMBER,
989 X_ACAD_CAL_TYPE in VARCHAR2,
990 X_SEQUENCE_NUMBER in NUMBER,
991 X_LOCATION_CD in VARCHAR2,
992 X_ATTENDANCE_MODE in VARCHAR2,
993 X_ATTENDANCE_TYPE in VARCHAR2,
994 X_ROLLOVER_INCLUSION_IND in VARCHAR2,
995 X_MODE in VARCHAR2 default 'R'
996 ) AS
997 cursor c1 is select rowid from IGS_AD_PRD_PS_OF_OPT
998 where ADM_CAL_TYPE = X_ADM_CAL_TYPE
999 and ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER
1000 and ADMISSION_CAT = X_ADMISSION_CAT
1001 and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
1002 and COURSE_CD = X_COURSE_CD
1003 and VERSION_NUMBER = X_VERSION_NUMBER
1004 and ACAD_CAL_TYPE = X_ACAD_CAL_TYPE
1005 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1006 ;
1007 begin
1008 open c1;
1009 fetch c1 into X_ROWID;
1010 if (c1%notfound) then
1011 close c1;
1012 INSERT_ROW (
1013 X_ROWID,
1014 X_ADM_CAL_TYPE,
1015 X_ADM_CI_SEQUENCE_NUMBER,
1016 X_ADMISSION_CAT,
1017 X_S_ADMISSION_PROCESS_TYPE,
1018 X_COURSE_CD,
1019 X_VERSION_NUMBER,
1020 X_ACAD_CAL_TYPE,
1021 X_SEQUENCE_NUMBER,
1022 X_LOCATION_CD,
1023 X_ATTENDANCE_MODE,
1024 X_ATTENDANCE_TYPE,
1025 X_ROLLOVER_INCLUSION_IND,
1026 X_MODE);
1027 return;
1028 end if;
1029 close c1;
1030 UPDATE_ROW (
1031 X_ROWID,
1032 X_ADM_CAL_TYPE,
1033 X_ADM_CI_SEQUENCE_NUMBER,
1034 X_ADMISSION_CAT,
1035 X_S_ADMISSION_PROCESS_TYPE,
1036 X_COURSE_CD,
1037 X_VERSION_NUMBER,
1038 X_ACAD_CAL_TYPE,
1039 X_SEQUENCE_NUMBER,
1040 X_LOCATION_CD,
1041 X_ATTENDANCE_MODE,
1042 X_ATTENDANCE_TYPE,
1043 X_ROLLOVER_INCLUSION_IND,
1044 X_MODE);
1045 end ADD_ROW;
1046
1047 procedure DELETE_ROW (
1048 X_ROWID in VARCHAR2
1049 ) AS
1050 begin
1051 Before_DML(
1052 p_action =>'DELETE',
1053 x_rowid => X_ROWID
1054 );
1055 delete from IGS_AD_PRD_PS_OF_OPT
1056 where ROWID = X_ROWID;
1057 if (sql%notfound) then
1058 raise no_data_found;
1059 end if;
1060 After_DML(
1061 p_action =>'DELETE',
1062 x_rowid => X_ROWID
1063 );
1064 end DELETE_ROW;
1065
1066 end IGS_AD_PRD_PS_OF_OPT_PKG;