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