[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_OFR_OPT_PKG
Source
1 PACKAGE BODY igs_ps_ofr_opt_pkg AS
2 /* $Header: IGSPI23B.pls 120.0 2005/06/01 14:01:40 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_OFR_OPT_ALL%RowType;
5 new_references IGS_PS_OFR_OPT_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_course_cd IN VARCHAR2 ,
11 x_version_number IN NUMBER ,
12 x_cal_type IN VARCHAR2 ,
13 x_location_cd IN VARCHAR2 ,
14 x_attendance_mode IN VARCHAR2 ,
15 x_attendance_type IN VARCHAR2 ,
16 x_coo_id IN NUMBER ,
17 x_forced_location_ind IN VARCHAR2 ,
18 x_forced_att_mode_ind IN VARCHAR2 ,
19 x_forced_att_type_ind IN VARCHAR2 ,
20 x_time_limitation IN NUMBER ,
21 x_enr_officer_person_id IN NUMBER ,
22 x_attribute_category in VARCHAR2 ,
23 x_attribute1 in VARCHAR2 ,
24 x_attribute2 in VARCHAR2 ,
25 x_attribute3 in VARCHAR2 ,
26 x_attribute4 in VARCHAR2 ,
27 x_attribute5 in VARCHAR2 ,
28 x_attribute6 in VARCHAR2 ,
29 x_attribute7 in VARCHAR2 ,
30 x_attribute8 in VARCHAR2 ,
31 x_attribute9 in VARCHAR2 ,
32 x_attribute10 in VARCHAR2 ,
33 x_attribute11 in VARCHAR2 ,
34 x_attribute12 in VARCHAR2 ,
35 x_attribute13 in VARCHAR2 ,
36 x_attribute14 in VARCHAR2 ,
37 x_attribute15 in VARCHAR2 ,
38 x_attribute16 in VARCHAR2 ,
39 x_attribute17 in VARCHAR2 ,
40 x_attribute18 in VARCHAR2 ,
41 x_attribute19 in VARCHAR2 ,
42 x_attribute20 in VARCHAR2 ,
43 x_creation_date IN DATE ,
44 x_created_by IN NUMBER ,
45 x_last_update_date IN DATE ,
46 x_last_updated_by IN NUMBER ,
47 x_last_update_login IN NUMBER ,
48 x_org_id IN NUMBER ,
49 x_program_length IN NUMBER ,
50 x_program_length_measurement IN VARCHAR2
51 ) AS
52
53 CURSOR cur_old_ref_values IS
54 SELECT *
55 FROM IGS_PS_OFR_OPT_ALL
56 WHERE rowid = x_rowid;
57
58 BEGIN
59
60 l_rowid := x_rowid;
61
62 -- Code for setting the Old and New Reference Values.
63 -- Populate Old Values.
64 Open cur_old_ref_values;
65 Fetch cur_old_ref_values INTO old_references;
66 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
67 Close cur_old_ref_values;
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69 IGS_GE_MSG_STACK.ADD;
70 App_Exception.Raise_Exception;
71 Return;
72 END IF;
73 Close cur_old_ref_values;
74
75 -- Populate New Values.
76 new_references.course_cd := x_course_cd;
77 new_references.version_number := x_version_number;
78 new_references.cal_type := x_cal_type;
79 new_references.location_cd := x_location_cd;
80 new_references.attendance_mode := x_attendance_mode;
81 new_references.attendance_type := x_attendance_type;
82 new_references.coo_id := x_coo_id;
83 new_references.forced_location_ind := x_forced_location_ind;
84 new_references.forced_att_mode_ind := x_forced_att_mode_ind;
85 new_references.forced_att_type_ind := x_forced_att_type_ind;
86 new_references.time_limitation := x_time_limitation;
87 new_references.enr_officer_person_id := x_enr_officer_person_id;
88 new_references.attribute_category := x_attribute_category;
89 new_references.attribute1 := x_attribute1;
90 new_references.attribute2 := x_attribute2;
91 new_references.attribute3 := x_attribute3;
92 new_references.attribute4 := x_attribute4;
93 new_references.attribute5 := x_attribute5;
94 new_references.attribute6 := x_attribute6;
95 new_references.attribute7 := x_attribute7;
96 new_references.attribute8 := x_attribute8;
97 new_references.attribute9 := x_attribute9;
98 new_references.attribute10 := x_attribute10;
99 new_references.attribute11 := x_attribute11;
100 new_references.attribute12 := x_attribute12;
101 new_references.attribute13 := x_attribute13;
102 new_references.attribute14 := x_attribute14;
103 new_references.attribute15 := x_attribute15;
104 new_references.attribute16 := x_attribute16;
105 new_references.attribute17 := x_attribute17;
106 new_references.attribute18 := x_attribute18;
107 new_references.attribute19 := x_attribute19;
108 new_references.attribute20 := x_attribute20;
109 new_references.org_id := x_org_id;
110 new_references.program_length := x_program_length;
111 new_references.program_length_measurement := x_program_length_measurement;
112
113
114 IF (p_action = 'UPDATE') THEN
115 new_references.creation_date := old_references.creation_date;
116 new_references.created_by := old_references.created_by;
117 ELSE
118 new_references.creation_date := x_creation_date;
119 new_references.created_by := x_created_by;
120 END IF;
121 new_references.last_update_date := x_last_update_date;
122 new_references.last_updated_by := x_last_updated_by;
123 new_references.last_update_login := x_last_update_login;
124
125 END Set_Column_Values;
126
127 -- Trigger description :-
128 -- "OSS_TST".TRG_COO_BR_IUD
129 -- BEFORE INSERT OR UPDATE OR DELETE ON IGS_PS_OFR_OPT_ALL
130 -- REFERENCING
131 -- NEW AS NEW
132 -- OLD AS OLD
133 -- FOR EACH ROW
134
135 PROCEDURE BeforeRowInsertUpdateDelete1(
136 p_inserting IN BOOLEAN ,
137 p_updating IN BOOLEAN ,
138 p_deleting IN BOOLEAN
139 ) AS
140 v_message_name VARCHAR2(30);
141 v_course_cd IGS_PS_VER.course_cd%TYPE;
142 v_version_number IGS_PS_VER.version_number%TYPE;
143 v_preferred_name IGS_PE_PERSON.preferred_given_name%TYPE;
144 BEGIN
145
146 -- Set variables
147 IF p_inserting OR p_updating THEN
148 v_course_cd := new_references.course_cd;
149 v_version_number := new_references.version_number;
150 ELSE -- p_deleting
151 v_course_cd := old_references.course_cd;
152 v_version_number := old_references.version_number;
153 END IF;
154 -- Validate that updates are allowed
155 IF IGS_PS_VAL_CRS.CRSP_VAL_IUD_CRV_DTL(v_course_cd,
156 v_version_number,
157 v_message_name) = FALSE THEN
158 Fnd_Message.Set_Name('IGS',v_message_name);
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 IF p_inserting THEN
163 -- Validate calendar type
164 IF IGS_PS_VAL_CO.crsp_val_co_cal_type(
165 new_references.cal_type,
166 v_message_name) = FALSE THEN
167 Fnd_Message.Set_Name('IGS',v_message_name);
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 -- Validate IGS_AD_LOCATION code
172
173 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_COO.crsp_val_loc_cd
174 IF IGS_PS_VAL_UOO.crsp_val_loc_cd(
175 new_references.location_cd,
176 v_message_name) = FALSE THEN
177 Fnd_Message.Set_Name('IGS',v_message_name);
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception;
180 END IF;
181 -- Validate attendance mode
182 IF IGS_PS_VAL_COo.crsp_val_coo_am (
183 new_references.attendance_mode,
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 -- Validate attendance type
190 IF IGS_PS_VAL_COo.crsp_val_coo_att (
191 new_references.attendance_type,
192 v_message_name) = FALSE THEN
193 Fnd_Message.Set_Name('IGS',v_message_name);
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198 IF p_inserting OR p_updating THEN
199 IF new_references.enr_officer_person_id IS NOT NULL THEN
200 -- Validate enrolment officer IGS_PE_PERSON id
201 IF IGS_GE_MNT_SDTT.PID_VAL_STAFF (
202 new_references.enr_officer_person_id,
203 v_preferred_name) = FALSE THEN
204 Fnd_Message.Set_Name('IGS','IGS_GE_NOT_STAFF_MEMBER');
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208 END IF;
209
210 -- If program length is provided then program length measurement is also required and vice versa, exception being when
211 -- progrma length measuremnt is 'Not Applicable'
212 IF (new_references.program_length_measurement IS NULL AND new_references.program_length IS NOT NULL ) OR
213 (new_references.program_length_measurement IS NOT NULL AND new_references.program_length IS NULL
214 AND new_references.program_length_measurement <> 'NOT_APPLICABLE' ) THEN
215 fnd_message.set_name('IGS','IGS_PS_PRG_LENGTH_INCLUSIVE');
216 IGS_GE_MSG_STACK.ADD;
217 App_exception.raise_exception;
218 END IF;
219
220 END IF;
221
222
223 END BeforeRowInsertUpdateDelete1; -- updated by ssawhney on 09/08/2k for TCA changes
224
225 PROCEDURE Check_Constraints (
226 Column_Name IN VARCHAR2 ,
227 Column_Value IN VARCHAR2
228 )
229 AS
230 BEGIN
231
232 IF column_name is null then
233 NULL;
234 ELSIF upper(Column_name) = 'FORCED_ATT_TYPE_IND' then
235 new_references.forced_att_type_ind := column_value;
236 ELSIF upper(Column_name) = 'TIME_LIMITATION' then
237 new_references.time_limitation := IGS_GE_NUMBER.TO_NUM(column_value);
238 ELSIF upper(Column_name) = 'FORCED_LOCATION_IND' then
239 new_references.forced_location_ind := column_value;
240 ELSIF upper(Column_name) = 'FORCED_ATT_MODE_IND' then
241 new_references.forced_att_mode_ind := column_value;
242 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
243 new_references.attendance_mode := column_value;
244 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
245 new_references.attendance_type := column_value;
246 ELSIF upper(Column_name) = 'CAL_TYPE' then
247 new_references.cal_type := column_value;
248 ELSIF upper(Column_name) = 'COURSE_CD' then
249 new_references.course_cd := column_value;
250 ELSIF upper(Column_name) = 'LOCATION_CD' then
251 new_references.location_cd := column_value;
252 END IF;
253
254 IF upper(column_name) = 'FORCED_ATT_TYPE_IND' OR
255 column_name is null Then
256 IF ( new_references.forced_att_type_ind NOT IN ( 'Y' , 'N' ) ) Then
257 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END IF;
261 END IF;
262
263 IF upper(column_name) = 'TIME_LIMITATION' OR
264 column_name is null Then
265 IF ( new_references.time_limitation < 0.01 OR new_references.time_limitation > 99.99 ) Then
266 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
267 IGS_GE_MSG_STACK.ADD;
268 App_Exception.Raise_Exception;
269 END IF;
270 END IF;
271
272 IF upper(column_name) = 'FORCED_LOCATION_IND' OR
273 column_name is null Then
274 IF ( new_references.forced_location_ind NOT IN ( 'Y' , 'N' ) ) Then
275 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
276 IGS_GE_MSG_STACK.ADD;
277 App_Exception.Raise_Exception;
278 END IF;
279 END IF;
280
281 IF upper(column_name) = 'FORCED_ATT_MODE_IND' OR
282 column_name is null Then
283 IF ( new_references.forced_att_mode_ind NOT IN ( 'Y' , 'N' ) ) Then
284 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
285 IGS_GE_MSG_STACK.ADD;
286 App_Exception.Raise_Exception;
287 END IF;
288 END IF;
289
290 IF upper(column_name) = 'ATTENDANCE_MODE' OR
291 column_name is null Then
292 IF ( new_references.attendance_mode <> UPPER(new_references.attendance_mode) ) Then
293 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297 END IF;
298
299 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
300 column_name is null Then
301 IF ( new_references.attendance_type <> UPPER(new_references.attendance_type) ) Then
302 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END IF;
306 END IF;
307
308 IF upper(column_name) = 'CAL_TYPE' OR
309 column_name is null Then
310 IF ( new_references.cal_type <> UPPER(new_references.cal_type) ) Then
311 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END IF;
315 END IF;
316
317 IF upper(column_name) = 'COURSE_CD' OR
318 column_name is null Then
319 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
320 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
321 IGS_GE_MSG_STACK.ADD;
322 App_Exception.Raise_Exception;
323 END IF;
324 END IF;
325
326 IF upper(column_name) = 'LOCATION_CD' OR
327 column_name is null Then
328 IF ( new_references.location_cd <> UPPER(new_references.location_cd) ) Then
329 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 END IF;
333 END IF;
334
335 END Check_Constraints;
336
337 PROCEDURE Check_Uniqueness AS
338 BEGIN
339
340 IF Get_UK_For_Validation (
341 new_references.coo_id ) THEN
342 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 END Check_Uniqueness ;
347
348 PROCEDURE Check_Parent_Existance AS
349 BEGIN
350
351 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
352 ((new_references.attendance_mode IS NULL))) THEN
353 NULL;
354 ELSE
355 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
356 new_references.attendance_mode
357 ) THEN
358 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
359 IGS_GE_MSG_STACK.ADD;
360 App_Exception.Raise_Exception;
361 END IF;
362 END IF;
363
364 IF (((old_references.attendance_type = new_references.attendance_type)) OR
365 ((new_references.attendance_type IS NULL))) THEN
366 NULL;
367 ELSE
368 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
369 new_references.attendance_type
370 ) THEN
371 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
372 IGS_GE_MSG_STACK.ADD;
373 App_Exception.Raise_Exception;
374 END IF;
375 END IF;
376
377 IF (((old_references.course_cd = new_references.course_cd) AND
378 (old_references.version_number = new_references.version_number) AND
379 (old_references.cal_type = new_references.cal_type)) OR
380 ((new_references.course_cd IS NULL) OR
381 (new_references.version_number IS NULL) OR
382 (new_references.cal_type IS NULL))) THEN
383 NULL;
384 ELSE
385 IF NOT IGS_PS_OFR_PKG.Get_PK_For_Validation (
386 new_references.course_cd,
387 new_references.version_number,
388 new_references.cal_type
389 ) THEN
390 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
391 IGS_GE_MSG_STACK.ADD;
392 App_Exception.Raise_Exception;
393 END IF;
394 END IF;
395
396 IF (((old_references.location_cd = new_references.location_cd)) OR
397 ((new_references.location_cd IS NULL))) THEN
398 NULL;
399 ELSE
400 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
401 new_references.location_cd ,
402 'N'
403 ) THEN
404 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
405 IGS_GE_MSG_STACK.ADD;
406 App_Exception.Raise_Exception;
407 END IF;
408 END IF;
409
410 IF (((old_references.enr_officer_person_id = new_references.enr_officer_person_id)) OR
411 ((new_references.enr_officer_person_id IS NULL))) THEN
412 NULL;
413 ELSE
414 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
415 new_references.enr_officer_person_id
416 ) THEN
417 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
418 IGS_GE_MSG_STACK.ADD;
419 App_Exception.Raise_Exception;
420 END IF;
421 END IF;
422
423 END Check_Parent_Existance;
424
425 PROCEDURE Check_Child_Existance AS
426
427 CURSOR c_hesa IS
428 SELECT '1' FROM USER_OBJECTS
429 WHERE OBJECT_NAME = 'IGS_HE_POOUS_ALL_PKG'
430 AND object_type = 'PACKAGE BODY';
431 l_hesa VARCHAR2(1);
432
433 /*
434 Who When What
435
436 --sbaliga 9-May-2002 Added 2 more calls to check for child existence
437 as aprt of #2330002
438 --ckasu 04-Dec-2003 Added IGS_EN_SPA_TERMS_PKG.GET_UFK_IGS_PS_OFR_OPT
439 for Term Records Build
440
441 */
442
443 BEGIN
444
445 IGS_PS_ENT_PT_REF_CD_PKG.GET_FK_IGS_PS_OFR_OPT (
446 old_references.course_cd,
447 old_references.version_number,
448 old_references.cal_type,
449 old_references.location_cd,
450 old_references.attendance_mode,
451 old_references.attendance_type
452 );
453
454 IGS_PS_ENT_PT_REF_CD_PKG.GET_UFK_IGS_PS_OFR_OPT (
455 old_references.coo_id
456 );
457
458 IGS_PS_OFR_OPT_NOTE_PKG.GET_FK_IGS_PS_OFR_OPT (
459 old_references.course_cd,
460 old_references.version_number,
461 old_references.cal_type,
462 old_references.location_cd,
463 old_references.attendance_mode,
464 old_references.attendance_type
465 );
466
467 IGS_PS_OFR_OPT_NOTE_PKG.GET_UFK_IGS_PS_OFR_OPT (
468 old_references.coo_id
469 );
470
471 IGS_PS_OFR_PAT_PKG.GET_FK_IGS_PS_OFR_OPT (
472 old_references.course_cd,
473 old_references.version_number,
474 old_references.cal_type,
475 old_references.location_cd,
476 old_references.attendance_mode,
477 old_references.attendance_type
478 );
479
480
481 IGS_PS_OFR_PAT_PKG.GET_UFK_IGS_PS_OFR_OPT (
482 old_references.coo_id
483 );
484
485 IGS_EN_SPA_TERMS_PKG.GET_UFK_IGS_PS_OFR_OPT (
486 old_references.coo_id
487 );
488
489 IGS_PS_OF_OPT_AD_CAT_PKG.GET_FK_IGS_PS_OFR_OPT (
490 old_references.course_cd,
491 old_references.version_number,
492 old_references.cal_type,
493 old_references.location_cd,
494 old_references.attendance_mode,
495 old_references.attendance_type
496 );
497
498 IGS_PS_OF_OPT_AD_CAT_PKG.GET_UFK_IGS_PS_OFR_OPT (
499 old_references.coo_id
500 );
501
502 IGS_PS_OF_OPT_UNT_ST_PKG.GET_FK_IGS_PS_OFR_OPT (
503 old_references.course_cd,
504 old_references.version_number,
505 old_references.cal_type,
506 old_references.location_cd,
507 old_references.attendance_mode,
508 old_references.attendance_type
509 );
510
511
512 IGS_PS_OF_OPT_UNT_ST_PKG.GET_UFK_IGS_PS_OFR_OPT (
513 old_references.coo_id
514 );
515
516 IGS_EN_STDNT_PS_ATT_PKG.GET_FK_IGS_PS_OFR_OPT (
517 old_references.course_cd,
518 old_references.version_number,
519 old_references.cal_type,
520 old_references.location_cd,
521 old_references.attendance_mode,
522 old_references.attendance_type
523 );
524
525 IGS_EN_STDNT_PS_ATT_PKG.GET_UFK_IGS_PS_OFR_OPT (
526 old_references.coo_id
527 );
528
529
530 --This procedure call was added as part of #2330002 by sbaliga
531 IGS_HE_POOUS_OU_ALL_PKG.GET_FK_IGS_PS_OFR_OPT_ALL
532 ( old_references.course_cd,
533 old_references.version_number,
534 old_references.cal_type,
535 old_references.location_cd,
536 old_references.attendance_mode,
537 old_references.attendance_type
538 );
539
540
541 -- Added the following check chaild existance for the HESA requirment, pmarada
542 OPEN c_hesa;
543 FETCH c_hesa INTO l_hesa;
544 IF c_hesa%FOUND THEN
545
546 EXECUTE IMMEDIATE
547 'BEGIN IGS_HE_POOUS_ALL_PKG.GET_FK_IGS_PS_OFR_OPT_ALL(:1,:2,:3,:4,:5,:6); END;'
548 USING
549 old_references.course_cd,
550 old_references.version_number,
551 old_references.cal_type,
552 old_references.location_cd,
553 old_references.attendance_mode,
554 old_references.attendance_type;
555 CLOSE c_hesa;
556 ELSE
557 CLOSE c_hesa;
558 END IF;
559
560 END Check_Child_Existance;
561
562
563 PROCEDURE Check_UK_Child_Existance AS
564
565 Begin
566
567 IF (old_references.coo_id = new_references.coo_id) OR (old_references.coo_id IS NULL) THEN
568 NULL;
569 ELSE
570 IGS_PS_ENT_PT_REF_CD_PKG.GET_UFK_IGS_PS_OFR_OPT(old_references.coo_id);
571 IGS_PS_OFR_OPT_NOTE_PKG.GET_UFK_IGS_PS_OFR_OPT(old_references.coo_id);
572 IGS_PS_OFR_PAT_PKG.GET_UFK_IGS_PS_OFR_OPT(old_references.coo_id);
573 IGS_PS_OF_OPT_UNT_ST_PKG.GET_UFK_IGS_PS_OFR_OPT(old_references.coo_id);
574 IGS_EN_STDNT_PS_ATT_PKG.GET_UFK_IGS_PS_OFR_OPT(old_references.coo_id);
575 IGS_PS_OF_OPT_AD_CAT_PKG.GET_UFK_IGS_PS_OFR_OPT(old_references.coo_id);
576 END IF;
577
578 END Check_UK_Child_Existance;
579
580
581 FUNCTION Get_PK_For_Validation (
582 x_course_cd IN VARCHAR2,
583 x_version_number IN NUMBER,
584 x_cal_type IN VARCHAR2,
585 x_location_cd IN VARCHAR2,
586 x_attendance_mode IN VARCHAR2,
587 x_attendance_type IN VARCHAR2
588 )
589 RETURN BOOLEAN AS
590
591 CURSOR cur_rowid IS
592 SELECT rowid
593 FROM IGS_PS_OFR_OPT_ALL
594 WHERE course_cd = x_course_cd
595 AND version_number = x_version_number
596 AND cal_type = x_cal_type
597 AND location_cd = x_location_cd
598 AND attendance_mode = x_attendance_mode
599 AND attendance_type = x_attendance_type
600 AND DELETE_FLAG = 'N';
601
602 lv_rowid cur_rowid%RowType;
603
604 BEGIN
605
606 Open cur_rowid;
607 Fetch cur_rowid INTO lv_rowid;
608 IF (cur_rowid%FOUND) THEN
609 Close cur_rowid;
610 Return (TRUE);
611 ELSE
612 Close cur_rowid;
613 Return (FALSE);
614 END IF;
615
616 END Get_PK_For_Validation;
617
618 FUNCTION Get_UK_For_Validation (
619 x_coo_id IN NUMBER
620 )
621 RETURN BOOLEAN AS
622
623 CURSOR cur_rowid IS
624 SELECT rowid
625 FROM IGS_PS_OFR_OPT_ALL
626 WHERE coo_id = x_coo_id
627 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
628 AND DELETE_FLAG = 'N';
629
630 lv_rowid cur_rowid%RowType;
631
632 BEGIN
633
634 Open cur_rowid;
635 Fetch cur_rowid INTO lv_rowid;
636 IF (cur_rowid%FOUND) THEN
637 Close cur_rowid;
638 Return (TRUE);
639 ELSE
640 Close cur_rowid;
641 Return (FALSE);
642 END IF;
643 END Get_UK_For_Validation;
644
645
646 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
647 x_attendance_mode IN VARCHAR2
648 ) AS
649
650 CURSOR cur_rowid IS
651 SELECT rowid
652 FROM IGS_PS_OFR_OPT_ALL
653 WHERE attendance_mode = x_attendance_mode
654 AND delete_flag = 'N';
655
656 lv_rowid cur_rowid%RowType;
657
658 BEGIN
659
660 Open cur_rowid;
661 Fetch cur_rowid INTO lv_rowid;
662 IF (cur_rowid%FOUND) THEN
663 Close cur_rowid;
664 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COO_AM_FK');
665 IGS_GE_MSG_STACK.ADD;
666 App_Exception.Raise_Exception;
667 Return;
668 END IF;
669 Close cur_rowid;
670
671 END GET_FK_IGS_EN_ATD_MODE;
672
673 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
674 x_attendance_type IN VARCHAR2
675 ) AS
676
677 CURSOR cur_rowid IS
678 SELECT rowid
679 FROM IGS_PS_OFR_OPT_ALL
680 WHERE attendance_type = x_attendance_type
681 AND delete_flag = 'N';
682
683 lv_rowid cur_rowid%RowType;
684
685 BEGIN
686
687 Open cur_rowid;
688 Fetch cur_rowid INTO lv_rowid;
689 IF (cur_rowid%FOUND) THEN
690 Close cur_rowid;
691 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COO_ATT_FK');
692 IGS_GE_MSG_STACK.ADD;
693 App_Exception.Raise_Exception;
694 Return;
695 END IF;
696 Close cur_rowid;
697
698 END GET_FK_IGS_EN_ATD_TYPE;
699
700 PROCEDURE GET_FK_IGS_PS_OFR (
701 x_course_cd IN VARCHAR2,
702 x_version_number IN NUMBER,
703 x_cal_type IN VARCHAR2
704 ) AS
705
706 CURSOR cur_rowid IS
707 SELECT rowid
708 FROM IGS_PS_OFR_OPT_ALL
709 WHERE course_cd = x_course_cd
710 AND version_number = x_version_number
711 AND cal_type = x_cal_type
712 AND delete_flag = 'N';
713
714 lv_rowid cur_rowid%RowType;
715
716 BEGIN
717
718 Open cur_rowid;
719 Fetch cur_rowid INTO lv_rowid;
720 IF (cur_rowid%FOUND) THEN
721 Close cur_rowid;
722 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COO_CO_FK');
723 IGS_GE_MSG_STACK.ADD;
724 App_Exception.Raise_Exception;
725 Return;
726 END IF;
727 Close cur_rowid;
728
729 END GET_FK_IGS_PS_OFR;
730
731 PROCEDURE GET_FK_IGS_AD_LOCATION (
732 x_location_cd IN VARCHAR2
733 ) AS
734
735 CURSOR cur_rowid IS
736 SELECT rowid
737 FROM IGS_PS_OFR_OPT_ALL
738 WHERE location_cd = x_location_cd
739 AND delete_flag = 'N';
740
741 lv_rowid cur_rowid%RowType;
742
743 BEGIN
744
745 Open cur_rowid;
746 Fetch cur_rowid INTO lv_rowid;
747 IF (cur_rowid%FOUND) THEN
748 Close cur_rowid;
749 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COO_LOC_FK');
750 IGS_GE_MSG_STACK.ADD;
751 App_Exception.Raise_Exception;
752 Return;
753 END IF;
754 Close cur_rowid;
755
756 END GET_FK_IGS_AD_LOCATION;
757
758 PROCEDURE GET_FK_IGS_PE_PERSON (
759 x_person_id IN VARCHAR2 --ssawhney
760 ) AS
761
762 CURSOR cur_rowid IS
763 SELECT rowid
764 FROM IGS_PS_OFR_OPT_ALL
765 WHERE enr_officer_person_id = x_person_id
766 AND delete_flag = 'N';
767
768 lv_rowid cur_rowid%RowType;
769
770 BEGIN
771
772 Open cur_rowid;
773 Fetch cur_rowid INTO lv_rowid;
774 IF (cur_rowid%FOUND) THEN
775 Close cur_rowid;
776 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COO_PE_FK');
777 IGS_GE_MSG_STACK.ADD;
778 App_Exception.Raise_Exception;
779 Return;
780 END IF;
781 Close cur_rowid;
782
783 END GET_FK_IGS_PE_PERSON;
784
785 PROCEDURE Before_DML (
786 p_action IN VARCHAR2,
787 x_rowid IN VARCHAR2 ,
788 x_course_cd IN VARCHAR2 ,
789 x_version_number IN NUMBER ,
790 x_cal_type IN VARCHAR2 ,
791 x_location_cd IN VARCHAR2 ,
792 x_attendance_mode IN VARCHAR2 ,
793 x_attendance_type IN VARCHAR2 ,
794 x_coo_id IN NUMBER ,
795 x_forced_location_ind IN VARCHAR2 ,
796 x_forced_att_mode_ind IN VARCHAR2 ,
797 x_forced_att_type_ind IN VARCHAR2 ,
798 x_time_limitation IN NUMBER ,
799 x_enr_officer_person_id IN NUMBER ,
800 x_attribute_category in VARCHAR2 ,
801 x_attribute1 in VARCHAR2 ,
802 x_attribute2 in VARCHAR2 ,
803 x_attribute3 in VARCHAR2 ,
804 x_attribute4 in VARCHAR2 ,
805 x_attribute5 in VARCHAR2 ,
806 x_attribute6 in VARCHAR2 ,
807 x_attribute7 in VARCHAR2 ,
808 x_attribute8 in VARCHAR2 ,
809 x_attribute9 in VARCHAR2 ,
810 x_attribute10 in VARCHAR2 ,
811 x_attribute11 in VARCHAR2 ,
812 x_attribute12 in VARCHAR2 ,
813 x_attribute13 in VARCHAR2 ,
814 x_attribute14 in VARCHAR2 ,
815 x_attribute15 in VARCHAR2 ,
816 x_attribute16 in VARCHAR2 ,
817 x_attribute17 in VARCHAR2 ,
818 x_attribute18 in VARCHAR2 ,
819 x_attribute19 in VARCHAR2 ,
820 x_attribute20 in VARCHAR2 ,
821 x_creation_date IN DATE ,
822 x_created_by IN NUMBER ,
823 x_last_update_date IN DATE ,
824 x_last_updated_by IN NUMBER ,
825 x_last_update_login IN NUMBER ,
826 x_org_id IN NUMBER ,
827 x_program_length IN NUMBER ,
828 x_program_length_measurement IN VARCHAR2 ,
829 X_DELETE_FLAG IN VARCHAR2
830 ) AS
831 BEGIN
832
833 Set_Column_Values (
834 p_action,
835 x_rowid,
836 x_course_cd,
837 x_version_number,
838 x_cal_type,
839 x_location_cd,
840 x_attendance_mode,
841 x_attendance_type,
842 x_coo_id,
843 x_forced_location_ind,
844 x_forced_att_mode_ind,
845 x_forced_att_type_ind,
846 x_time_limitation,
847 x_enr_officer_person_id,
848 x_attribute_category,
849 x_attribute1,
850 x_attribute2,
851 x_attribute3,
852 x_attribute4,
853 x_attribute5,
854 x_attribute6,
855 x_attribute7,
856 x_attribute8,
857 x_attribute9,
858 x_attribute10,
859 x_attribute11,
860 x_attribute12,
861 x_attribute13,
862 x_attribute14,
863 x_attribute15,
864 x_attribute16,
865 x_attribute17,
866 x_attribute18,
867 x_attribute19,
868 x_attribute20,
869 x_creation_date,
870 x_created_by,
871 x_last_update_date,
872 x_last_updated_by,
873 x_last_update_login ,
874 x_org_id,
875 x_program_length,
876 x_program_length_measurement
877 );
878
879 IF (p_action = 'INSERT') THEN
880 -- Call all the procedures related to Before Insert.
881
882 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE , p_updating => FALSE, p_deleting => FALSE );
883 IF Get_PK_For_Validation (
884 new_references.course_cd,
885 new_references.version_number,
886 new_references.cal_type,
887 new_references.location_cd,
888 new_references.attendance_mode,
889 new_references.attendance_type ) THEN
890 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
891 IGS_GE_MSG_STACK.ADD;
892 App_Exception.Raise_Exception;
893 END IF;
894 Check_Constraints;
895 Check_Parent_Existance;
896 Check_Uniqueness;
897 ELSIF (p_action = 'UPDATE') THEN
898 -- Call all the procedures related to Before Update.
899 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE, p_inserting => FALSE, p_deleting=> FALSE );
900 Check_Constraints;
901 Check_Parent_Existance;
902 Check_Uniqueness;
903 Check_UK_Child_Existance;
904 ELSIF (p_action = 'DELETE') THEN
905 -- Call all the procedures related to Before Delete.
906 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE, p_updating => FALSE, p_inserting => FALSE );
907 Check_Child_Existance;
908 ELSIF (p_action = 'VALIDATE_INSERT') THEN
909 IF Get_PK_For_Validation (
910 new_references.course_cd,
911 new_references.version_number,
912 new_references.cal_type,
913 new_references.location_cd,
914 new_references.attendance_mode,
915 new_references.attendance_type ) THEN
916 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
917 IGS_GE_MSG_STACK.ADD;
918 App_Exception.Raise_Exception;
919 END IF;
920 Check_Constraints;
921 Check_Uniqueness;
922 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
923 Check_Uniqueness;
924 Check_Constraints;
925 ELSIF (p_action = 'VALIDATE_DELETE') THEN
926 Check_Child_Existance;
927 END IF;
928 END Before_DML;
929
930 PROCEDURE dflt_prg_ofropt_ref_code(
931 p_n_coo_id IN igs_ps_ofr_opt.coo_id%TYPE,
932 p_message_name OUT NOCOPY VARCHAR2
933 ) AS
934 /************************************************************************
935 Created By : rbezawad
936 Date Created By : 12/06/2001
937 Purpose : Insertion into table IGS_PS_PT_REF_CD for mandatory ref code types and default ref codes
938 Known limitations, enhancements or remarks:
939 Change History :
940 Who When What
941 *************************************************************************/
942 CURSOR c_ofr_opt (cp_coo_id igs_ps_ofr_opt.coo_id%TYPE) IS
943 SELECT course_cd,
944 version_number,
945 cal_type,
946 location_cd,
947 attendance_type,
948 attendance_mode
949 FROM igs_ps_ofr_opt
950 WHERE coo_id = cp_coo_id;
951
952 l_ofr_opt c_ofr_opt%ROWTYPE;
953
954 CURSOR c_igs_ge_ref_cd_type_all IS
955 SELECT reference_cd_type
956 FROM igs_ge_ref_cd_type_all
957 WHERE mandatory_flag ='Y'
958 AND program_offering_option_flag ='Y'
959 AND restricted_flag='Y'
960 AND closed_ind = 'N';
961
962 CURSOR c_igs_ge_ref_cd ( cp_reference_cd_type igs_ge_ref_cd.reference_cd_type%TYPE ) IS
963 SELECT reference_cd,
964 description
965 FROM igs_ge_ref_cd
966 WHERE reference_cd_type = cp_reference_cd_type
967 AND default_flag = 'Y';
968
969 CURSOR c_seq_no IS
970 SELECT igs_ps_ent_pt_ref_cd_seq_num_s.NEXTVAL
971 FROM dual;
972
973 l_n_sequence_number NUMBER;
974 l_c_rowid VARCHAR2(30);
975
976 BEGIN
977 OPEN c_ofr_opt( p_n_coo_id );
978 FETCH c_ofr_opt INTO l_ofr_opt;
979 CLOSE c_ofr_opt;
980
981 FOR cur_igs_ge_ref_cd_type_all IN c_igs_ge_ref_cd_type_all
982 LOOP
983 FOR cur_igs_ge_ref_cd IN c_igs_ge_ref_cd(cur_igs_ge_ref_cd_type_all.reference_cd_type)
984 LOOP
985 OPEN c_seq_no;
986 FETCH c_seq_no INTO l_n_sequence_number;
987 CLOSE c_seq_no;
988 -- insert a value in igs_ps_ent_pt_ref_cd for every value of course_cd and version_number having
989 -- a applicable program offering option defined as mandatory and a default reference code
990 BEGIN
991 l_c_rowid:=NULL;
992 igs_ps_ent_pt_ref_cd_pkg.insert_row( x_rowid => l_c_rowid,
993 x_course_cd => l_ofr_opt.course_cd,
994 x_sequence_number => l_n_sequence_number,
995 x_reference_cd_type => cur_igs_ge_ref_cd_type_all.reference_cd_type,
996 x_attendance_type => l_ofr_opt.attendance_type,
997 x_cal_type => l_ofr_opt.cal_type,
998 x_location_cd => l_ofr_opt.location_cd,
999 x_version_number => l_ofr_opt.version_number,
1000 x_attendance_mode => l_ofr_opt.attendance_mode,
1001 x_coo_id => p_n_coo_id,
1002 x_unit_set_cd => NULL,
1003 x_us_version_number => NULL,
1004 x_reference_cd => cur_igs_ge_ref_cd.reference_cd,
1005 x_description => cur_igs_ge_ref_cd.description,
1006 x_mode => 'R' );
1007 EXCEPTION
1008 -- The failure of insertion of reference code should not stop the creation of new program offering option.
1009 -- Hence any exception raised by the TBH is trapped and the current processing is allowed to proceed.
1010 WHEN OTHERS THEN
1011 NULL;
1012 END ;
1013 END LOOP;
1014 END LOOP;
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 -- If an error occurs during insertion in igs_ps_ent_pt_ref_cd then raise an exception.
1018 p_message_name := FND_MESSAGE.GET;
1019
1020 END dflt_prg_ofropt_ref_code;
1021
1022
1023
1024 PROCEDURE After_DML (
1025 p_action IN VARCHAR2,
1026 x_rowid IN VARCHAR2
1027 ) AS
1028 l_message_name VARCHAR2(30) ;
1029 CURSOR c_coo(cp_rowid igs_ps_ofr_opt.row_id%TYPE) IS
1030 SELECT coo_id
1031 FROM igs_ps_ofr_opt
1032 WHERE ROWID = cp_rowid;
1033 l_coo_id igs_ps_ofr_opt.coo_id%TYPE;
1034 BEGIN
1035
1036 l_rowid := x_rowid;
1037
1038 IF ( p_action = 'INSERT') THEN
1039 OPEN c_coo(x_rowid);
1040 FETCH c_coo INTO l_coo_id;
1041 IF (c_coo%FOUND) THEN
1042 l_rowid := NULL;
1043 dflt_prg_ofropt_ref_code( l_coo_id,l_message_name );
1044 IF l_message_name IS NOT NULL THEN
1045 app_exception.raise_exception;
1046 END IF;
1047 END IF;
1048 CLOSE c_coo;
1049 END IF;
1050
1051 l_rowid:=NULL;
1052
1053 END After_DML;
1054
1055 procedure INSERT_ROW (
1056 X_ROWID in out NOCOPY VARCHAR2,
1057 X_COURSE_CD in VARCHAR2,
1058 X_VERSION_NUMBER in NUMBER,
1059 X_CAL_TYPE in VARCHAR2,
1060 X_ATTENDANCE_MODE in VARCHAR2,
1061 X_ATTENDANCE_TYPE in VARCHAR2,
1062 X_LOCATION_CD in VARCHAR2,
1063 X_COO_ID in NUMBER,
1064 X_FORCED_LOCATION_IND in VARCHAR2,
1065 X_FORCED_ATT_MODE_IND in VARCHAR2,
1066 X_FORCED_ATT_TYPE_IND in VARCHAR2,
1067 X_TIME_LIMITATION in NUMBER,
1068 X_ENR_OFFICER_PERSON_ID in NUMBER,
1069 x_attribute_category in VARCHAR2 ,
1070 x_attribute1 in VARCHAR2 ,
1071 x_attribute2 in VARCHAR2 ,
1072 x_attribute3 in VARCHAR2 ,
1073 x_attribute4 in VARCHAR2 ,
1074 x_attribute5 in VARCHAR2 ,
1075 x_attribute6 in VARCHAR2 ,
1076 x_attribute7 in VARCHAR2 ,
1077 x_attribute8 in VARCHAR2 ,
1078 x_attribute9 in VARCHAR2 ,
1079 x_attribute10 in VARCHAR2 ,
1080 x_attribute11 in VARCHAR2 ,
1081 x_attribute12 in VARCHAR2 ,
1082 x_attribute13 in VARCHAR2 ,
1083 x_attribute14 in VARCHAR2 ,
1084 x_attribute15 in VARCHAR2 ,
1085 x_attribute16 in VARCHAR2 ,
1086 x_attribute17 in VARCHAR2 ,
1087 x_attribute18 in VARCHAR2 ,
1088 x_attribute19 in VARCHAR2 ,
1089 x_attribute20 in VARCHAR2 ,
1090 X_MODE in VARCHAR2 ,
1091 X_ORG_ID in NUMBER,
1092 x_program_length IN NUMBER ,
1093 x_program_length_measurement IN VARCHAR2 ,
1094 X_DELETE_FLAG IN VARCHAR2
1095 ) AS
1096
1097 --This cursor is added as a part of locking build
1098 CURSOR cur_check_for_deleted_rec(cp_course_cd igs_ps_ofr_opt_all.course_cd%TYPE,
1099 cp_version_number igs_ps_ofr_opt_all.version_number%TYPE,
1100 cp_cal_type igs_ps_ofr_opt_all.cal_type%TYPE,
1101 cp_location_cd igs_ps_ofr_opt_all.location_cd%TYPE,
1102 cp_attendance_mode igs_ps_ofr_opt_all.attendance_mode%TYPE,
1103 cp_attendance_type igs_ps_ofr_opt_all.attendance_type%TYPE) IS
1104 SELECT rowid
1105 FROM igs_ps_ofr_opt_all
1106 WHERE course_cd=cp_course_cd
1107 AND version_number=cp_version_number
1108 AND cal_type =cp_cal_type
1109 AND location_cd = cp_location_cd
1110 AND attendance_type= cp_attendance_type
1111 AND attendance_mode = cp_attendance_mode
1112 AND delete_flag = 'Y';
1113 l_rowid VARCHAR2(25);
1114
1115
1116
1117 cursor C is select ROWID from IGS_PS_OFR_OPT_ALL
1118 where COURSE_CD = X_COURSE_CD
1119 and VERSION_NUMBER = X_VERSION_NUMBER
1120 and CAL_TYPE = X_CAL_TYPE
1121 and ATTENDANCE_MODE = X_ATTENDANCE_MODE
1122 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
1123 and LOCATION_CD = X_LOCATION_CD;
1124 X_LAST_UPDATE_DATE DATE;
1125 X_LAST_UPDATED_BY NUMBER;
1126 X_LAST_UPDATE_LOGIN NUMBER;
1127 begin
1128 X_LAST_UPDATE_DATE := SYSDATE;
1129 if(X_MODE = 'I') then
1130 X_LAST_UPDATED_BY := 1;
1131 X_LAST_UPDATE_LOGIN := 0;
1132 elsif (X_MODE = 'R') then
1133 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1134 if X_LAST_UPDATED_BY is NULL then
1135 X_LAST_UPDATED_BY := -1;
1136 end if;
1137 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1138 if X_LAST_UPDATE_LOGIN is NULL then
1139 X_LAST_UPDATE_LOGIN := -1;
1140 end if;
1141 else
1142 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1143 IGS_GE_MSG_STACK.ADD;
1144 app_exception.raise_exception;
1145 end if;
1146 Before_DML (
1147 p_action => 'INSERT',
1148 x_rowid => X_ROWID,
1149 x_course_cd => X_COURSE_CD,
1150 x_version_number => X_VERSION_NUMBER,
1151 x_cal_type => X_CAL_TYPE,
1152 x_location_cd => X_LOCATION_CD,
1153 x_attendance_mode => X_ATTENDANCE_MODE,
1154 x_attendance_type => X_ATTENDANCE_TYPE,
1155 x_coo_id => X_COO_ID,
1156 x_forced_location_ind =>NVL(X_FORCED_LOCATION_IND,'N'),
1157 x_forced_att_mode_ind => NVL(X_FORCED_ATT_MODE_IND,'N'),
1158 x_forced_att_type_ind => NVL(X_FORCED_ATT_TYPE_IND,'N'),
1159 x_time_limitation => X_TIME_LIMITATION ,
1160 x_enr_officer_person_id => X_ENR_OFFICER_PERSON_ID ,
1161 x_attribute_category => X_ATTRIBUTE_CATEGORY,
1162 x_attribute1 => X_ATTRIBUTE1,
1163 x_attribute2 => X_ATTRIBUTE2,
1164 x_attribute3 => X_ATTRIBUTE3,
1165 x_attribute4 => X_ATTRIBUTE4,
1166 x_attribute5 => X_ATTRIBUTE5,
1167 x_attribute6 => X_ATTRIBUTE6,
1168 x_attribute7 => X_ATTRIBUTE7,
1169 x_attribute8 => X_ATTRIBUTE8,
1170 x_attribute9 => X_ATTRIBUTE9,
1171 x_attribute10 => X_ATTRIBUTE10,
1172 x_attribute11 => X_ATTRIBUTE11,
1173 x_attribute12 => X_ATTRIBUTE12,
1174 x_attribute13 => X_ATTRIBUTE13,
1175 x_attribute14 => X_ATTRIBUTE14,
1176 x_attribute15 => X_ATTRIBUTE15,
1177 x_attribute16 => X_ATTRIBUTE16,
1178 x_attribute17 => X_ATTRIBUTE17,
1179 x_attribute18 => X_ATTRIBUTE18,
1180 x_attribute19 => X_ATTRIBUTE19,
1181 x_attribute20 => X_ATTRIBUTE20,
1182 x_creation_date => X_LAST_UPDATE_DATE ,
1183 x_created_by => X_LAST_UPDATED_BY ,
1184 x_last_update_date => X_LAST_UPDATE_DATE ,
1185 x_last_updated_by => X_LAST_UPDATED_BY ,
1186 x_last_update_login => X_LAST_UPDATE_LOGIN ,
1187 x_org_id => igs_ge_gen_003.get_org_id,
1188 x_program_length => x_program_length,
1189 x_program_length_measurement => x_program_length_measurement
1190 );
1191
1192 --** THis check has been intensionally for locking issue build(bug#2797116)
1193 --If a logically deleted record is there then update the delete_flag to N with rest of the parameter as passed
1194 --else insert the record with delete_flag as N
1195
1196 OPEN cur_check_for_deleted_rec(x_course_cd,x_version_number,x_cal_type,x_location_cd,x_attendance_mode,
1197 x_attendance_type);
1198 FETCH cur_check_for_deleted_rec INTO l_rowid;
1199 IF cur_check_for_deleted_rec%NOTFOUND THEN
1200 CLOSE cur_check_for_deleted_rec;
1201
1202 insert into IGS_PS_OFR_OPT_ALL (
1203 COURSE_CD,
1204 VERSION_NUMBER,
1205 CAL_TYPE,
1206 LOCATION_CD,
1207 ATTENDANCE_MODE,
1208 ATTENDANCE_TYPE,
1209 COO_ID,
1210 FORCED_LOCATION_IND,
1211 FORCED_ATT_MODE_IND,
1212 FORCED_ATT_TYPE_IND,
1213 TIME_LIMITATION,
1214 ENR_OFFICER_PERSON_ID,
1215 attribute_category,
1216 attribute1,
1217 attribute2,
1218 attribute3,
1219 attribute4,
1220 attribute5,
1221 attribute6,
1222 attribute7,
1223 attribute8,
1224 attribute9,
1225 attribute10,
1226 attribute11,
1227 attribute12,
1228 attribute13,
1229 attribute14,
1230 attribute15,
1231 attribute16,
1232 attribute17,
1233 attribute18,
1234 attribute19,
1235 attribute20,
1236 CREATION_DATE,
1237 CREATED_BY,
1238 LAST_UPDATE_DATE,
1239 LAST_UPDATED_BY,
1240 LAST_UPDATE_LOGIN,
1241 ORG_ID,
1242 PROGRAM_LENGTH,
1243 PROGRAM_LENGTH_MEASUREMENT,
1244 DELETE_FLAG
1245 ) values (
1246 NEW_REFERENCES.COURSE_CD,
1247 NEW_REFERENCES.VERSION_NUMBER,
1248 NEW_REFERENCES.CAL_TYPE,
1249 NEW_REFERENCES.LOCATION_CD,
1250 NEW_REFERENCES.ATTENDANCE_MODE,
1251 NEW_REFERENCES.ATTENDANCE_TYPE,
1252 NEW_REFERENCES.COO_ID,
1253 NEW_REFERENCES.FORCED_LOCATION_IND,
1254 NEW_REFERENCES.FORCED_ATT_MODE_IND,
1255 NEW_REFERENCES.FORCED_ATT_TYPE_IND,
1256 NEW_REFERENCES.TIME_LIMITATION,
1257 NEW_REFERENCES.ENR_OFFICER_PERSON_ID,
1258 NEW_REFERENCES.attribute_category,
1259 NEW_REFERENCES.attribute1,
1260 NEW_REFERENCES.attribute2,
1261 NEW_REFERENCES.attribute3,
1262 NEW_REFERENCES.attribute4,
1263 NEW_REFERENCES.attribute5,
1264 NEW_REFERENCES.attribute6,
1265 NEW_REFERENCES.attribute7,
1266 NEW_REFERENCES.attribute8,
1267 NEW_REFERENCES.attribute9,
1268 NEW_REFERENCES.attribute10,
1269 NEW_REFERENCES.attribute11,
1270 NEW_REFERENCES.attribute12,
1271 NEW_REFERENCES.attribute13,
1272 NEW_REFERENCES.attribute14,
1273 NEW_REFERENCES.attribute15,
1274 NEW_REFERENCES.attribute16,
1275 NEW_REFERENCES.attribute17,
1276 NEW_REFERENCES.attribute18,
1277 NEW_REFERENCES.attribute19,
1278 NEW_REFERENCES.attribute20,
1279 X_LAST_UPDATE_DATE,
1280 X_LAST_UPDATED_BY,
1281 X_LAST_UPDATE_DATE,
1282 X_LAST_UPDATED_BY,
1283 X_LAST_UPDATE_LOGIN,
1284 NEW_REFERENCES.ORG_ID,
1285 NEW_REFERENCES.PROGRAM_LENGTH,
1286 NEW_REFERENCES.PROGRAM_LENGTH_MEASUREMENT,
1287 'N'
1288 );
1289 ELSE
1290 CLOSE cur_check_for_deleted_rec;
1291
1292 UPDATE IGS_PS_OFR_OPT_ALL
1293 SET
1294 FORCED_LOCATION_IND=NEW_REFERENCES.FORCED_LOCATION_IND,
1295 FORCED_ATT_MODE_IND=NEW_REFERENCES.FORCED_ATT_MODE_IND,
1296 FORCED_ATT_TYPE_IND=NEW_REFERENCES.FORCED_ATT_TYPE_IND,
1297 TIME_LIMITATION=NEW_REFERENCES.TIME_LIMITATION,
1298 ENR_OFFICER_PERSON_ID=NEW_REFERENCES.ENR_OFFICER_PERSON_ID,
1299 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1300 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1301 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1302 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1303 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1304 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1305 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1306 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1307 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1308 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1309 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1310 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1311 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1312 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1313 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1314 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1315 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1316 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1317 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1318 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1319 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1320 CREATION_DATE = X_LAST_UPDATE_DATE,
1321 CREATED_BY = X_LAST_UPDATED_BY,
1322 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1323 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1324 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1325 ORG_ID = NEW_REFERENCES.ORG_ID,
1326 PROGRAM_LENGTH = NEW_REFERENCES.PROGRAM_LENGTH,
1327 PROGRAM_LENGTH_MEASUREMENT = NEW_REFERENCES.PROGRAM_LENGTH_MEASUREMENT,
1328 DELETE_FLAG = 'N'
1329 WHERE ROWID = l_rowid;
1330
1331 END IF;
1332
1333 open c;
1334 fetch c into X_ROWID;
1335 if (c%notfound) then
1336 close c;
1337 raise no_data_found;
1338 end if;
1339 close c;
1340
1341 --This modification is done as part of locking build
1342 IF l_rowid IS NULL THEN
1343 After_DML (
1344 p_action => 'INSERT',
1345 x_rowid => X_ROWID
1346 );
1347 ELSE
1348 After_DML (
1349 p_action => 'UPDATE',
1350 x_rowid => X_ROWID
1351 );
1352 END IF;
1353
1354 end INSERT_ROW;
1355
1356 procedure LOCK_ROW (
1357 X_ROWID IN VARCHAR2,
1358 X_COURSE_CD in VARCHAR2,
1359 X_VERSION_NUMBER in NUMBER,
1360 X_CAL_TYPE in VARCHAR2,
1361 X_ATTENDANCE_MODE in VARCHAR2,
1362 X_ATTENDANCE_TYPE in VARCHAR2,
1363 X_LOCATION_CD in VARCHAR2,
1364 X_COO_ID in NUMBER,
1365 X_FORCED_LOCATION_IND in VARCHAR2,
1366 X_FORCED_ATT_MODE_IND in VARCHAR2,
1367 X_FORCED_ATT_TYPE_IND in VARCHAR2,
1368 X_TIME_LIMITATION in NUMBER,
1369 X_ENR_OFFICER_PERSON_ID in NUMBER,
1370 X_ATTRIBUTE_CATEGORY in VARCHAR2 ,
1371 X_ATTRIBUTE1 in VARCHAR2 ,
1372 X_ATTRIBUTE2 in VARCHAR2 ,
1373 X_ATTRIBUTE3 in VARCHAR2 ,
1374 X_ATTRIBUTE4 in VARCHAR2 ,
1375 X_ATTRIBUTE5 in VARCHAR2 ,
1376 X_ATTRIBUTE6 in VARCHAR2 ,
1377 X_ATTRIBUTE7 in VARCHAR2 ,
1378 X_ATTRIBUTE8 in VARCHAR2 ,
1379 X_ATTRIBUTE9 in VARCHAR2 ,
1380 X_ATTRIBUTE10 in VARCHAR2 ,
1381 X_ATTRIBUTE11 in VARCHAR2 ,
1382 X_ATTRIBUTE12 in VARCHAR2 ,
1383 X_ATTRIBUTE13 in VARCHAR2 ,
1384 X_ATTRIBUTE14 in VARCHAR2 ,
1385 X_ATTRIBUTE15 in VARCHAR2 ,
1386 X_ATTRIBUTE16 in VARCHAR2 ,
1387 X_ATTRIBUTE17 in VARCHAR2 ,
1388 X_ATTRIBUTE18 in VARCHAR2 ,
1389 X_ATTRIBUTE19 in VARCHAR2 ,
1390 X_ATTRIBUTE20 in VARCHAR2 ,
1391 x_program_length IN NUMBER ,
1392 x_program_length_measurement IN VARCHAR2 ,
1393 X_DELETE_FLAG IN VARCHAR2
1394 ) AS
1395 cursor c1 is select
1396 COO_ID,
1397 FORCED_LOCATION_IND,
1398 FORCED_ATT_MODE_IND,
1399 FORCED_ATT_TYPE_IND,
1400 TIME_LIMITATION,
1401 ENR_OFFICER_PERSON_ID
1402 from IGS_PS_OFR_OPT_ALL
1403 where ROWID = X_ROWID
1404 for update nowait;
1405 tlinfo c1%rowtype;
1406
1407 begin
1408 open c1;
1409 fetch c1 into tlinfo;
1410 if (c1%notfound) then
1411 close c1;
1412 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1413 IGS_GE_MSG_STACK.ADD;
1414 app_exception.raise_exception;
1415 return;
1416 end if;
1417 close c1;
1418
1419 if ( (tlinfo.COO_ID = X_COO_ID)
1420 AND (tlinfo.FORCED_LOCATION_IND = X_FORCED_LOCATION_IND)
1421 AND (tlinfo.FORCED_ATT_MODE_IND = X_FORCED_ATT_MODE_IND)
1422 AND (tlinfo.FORCED_ATT_TYPE_IND = X_FORCED_ATT_TYPE_IND)
1423 AND ((tlinfo.TIME_LIMITATION = X_TIME_LIMITATION)
1424 OR ((tlinfo.TIME_LIMITATION is null)
1425 AND (X_TIME_LIMITATION is null)))
1426 AND ((tlinfo.ENR_OFFICER_PERSON_ID = X_ENR_OFFICER_PERSON_ID)
1427 OR ((tlinfo.ENR_OFFICER_PERSON_ID is null)
1428 AND (X_ENR_OFFICER_PERSON_ID is null)))
1429 ) then
1430 null;
1431 else
1432 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1433 IGS_GE_MSG_STACK.ADD;
1434 app_exception.raise_exception;
1435 end if;
1436 return;
1437 end LOCK_ROW;
1438
1439 procedure UPDATE_ROW (
1440 X_ROWID IN VARCHAR2,
1441 X_COURSE_CD in VARCHAR2,
1442 X_VERSION_NUMBER in NUMBER,
1443 X_CAL_TYPE in VARCHAR2,
1444 X_ATTENDANCE_MODE in VARCHAR2,
1445 X_ATTENDANCE_TYPE in VARCHAR2,
1446 X_LOCATION_CD in VARCHAR2,
1447 X_COO_ID in NUMBER,
1448 X_FORCED_LOCATION_IND in VARCHAR2,
1449 X_FORCED_ATT_MODE_IND in VARCHAR2,
1450 X_FORCED_ATT_TYPE_IND in VARCHAR2,
1451 X_TIME_LIMITATION in NUMBER,
1452 X_ENR_OFFICER_PERSON_ID in NUMBER,
1453 X_ATTRIBUTE_CATEGORY in VARCHAR2 ,
1454 X_ATTRIBUTE1 in VARCHAR2 ,
1455 X_ATTRIBUTE2 in VARCHAR2 ,
1456 X_ATTRIBUTE3 in VARCHAR2 ,
1457 X_ATTRIBUTE4 in VARCHAR2 ,
1458 X_ATTRIBUTE5 in VARCHAR2 ,
1459 X_ATTRIBUTE6 in VARCHAR2 ,
1460 X_ATTRIBUTE7 in VARCHAR2 ,
1461 X_ATTRIBUTE8 in VARCHAR2 ,
1462 X_ATTRIBUTE9 in VARCHAR2 ,
1463 X_ATTRIBUTE10 in VARCHAR2 ,
1464 X_ATTRIBUTE11 in VARCHAR2 ,
1465 X_ATTRIBUTE12 in VARCHAR2 ,
1466 X_ATTRIBUTE13 in VARCHAR2 ,
1467 X_ATTRIBUTE14 in VARCHAR2 ,
1468 X_ATTRIBUTE15 in VARCHAR2 ,
1469 X_ATTRIBUTE16 in VARCHAR2 ,
1470 X_ATTRIBUTE17 in VARCHAR2 ,
1471 X_ATTRIBUTE18 in VARCHAR2 ,
1472 X_ATTRIBUTE19 in VARCHAR2 ,
1473 X_ATTRIBUTE20 in VARCHAR2 ,
1474 X_MODE in VARCHAR2,
1475 x_program_length IN NUMBER ,
1476 x_program_length_measurement IN VARCHAR2 ,
1477 x_delete_flag IN VARCHAR2
1478 ) AS
1479 X_LAST_UPDATE_DATE DATE;
1480 X_LAST_UPDATED_BY NUMBER;
1481 X_LAST_UPDATE_LOGIN NUMBER;
1482 begin
1483 X_LAST_UPDATE_DATE := SYSDATE;
1484 if(X_MODE = 'I') then
1485 X_LAST_UPDATED_BY := 1;
1486 X_LAST_UPDATE_LOGIN := 0;
1487 elsif (X_MODE = 'R') then
1488 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1489 if X_LAST_UPDATED_BY is NULL then
1490 X_LAST_UPDATED_BY := -1;
1491 end if;
1492 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1493 if X_LAST_UPDATE_LOGIN is NULL then
1494 X_LAST_UPDATE_LOGIN := -1;
1495 end if;
1496 else
1497 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1498 IGS_GE_MSG_STACK.ADD;
1499 app_exception.raise_exception;
1500 end if;
1501 Before_DML (
1502 p_action => 'UPDATE',
1503 x_rowid => X_ROWID,
1504 x_course_cd => X_COURSE_CD,
1505 x_version_number => X_VERSION_NUMBER,
1506 x_cal_type => X_CAL_TYPE,
1507 x_location_cd => X_LOCATION_CD,
1508 x_attendance_mode => X_ATTENDANCE_MODE,
1509 x_attendance_type => X_ATTENDANCE_TYPE,
1510 x_coo_id => X_COO_ID,
1511 x_forced_location_ind => X_FORCED_LOCATION_IND ,
1512 x_forced_att_mode_ind => X_FORCED_ATT_MODE_IND,
1513 x_forced_att_type_ind => X_FORCED_ATT_TYPE_IND,
1514 x_time_limitation => X_TIME_LIMITATION ,
1515 x_enr_officer_person_id => X_ENR_OFFICER_PERSON_ID ,
1516 x_attribute_category => X_ATTRIBUTE_CATEGORY,
1517 x_attribute1 => X_ATTRIBUTE1,
1518 x_attribute2 => X_ATTRIBUTE2,
1519 x_attribute3 => X_ATTRIBUTE3,
1520 x_attribute4 => X_ATTRIBUTE4,
1521 x_attribute5 => X_ATTRIBUTE5,
1522 x_attribute6 => X_ATTRIBUTE6,
1523 x_attribute7 => X_ATTRIBUTE7,
1524 x_attribute8 => X_ATTRIBUTE8,
1525 x_attribute9 => X_ATTRIBUTE9,
1526 x_attribute10 => X_ATTRIBUTE10,
1527 x_attribute11 => X_ATTRIBUTE11,
1528 x_attribute12 => X_ATTRIBUTE12,
1529 x_attribute13 => X_ATTRIBUTE13,
1530 x_attribute14 => X_ATTRIBUTE14,
1531 x_attribute15 => X_ATTRIBUTE15,
1532 x_attribute16 => X_ATTRIBUTE16,
1533 x_attribute17 => X_ATTRIBUTE17,
1534 x_attribute18 => X_ATTRIBUTE18,
1535 x_attribute19 => X_ATTRIBUTE19,
1536 x_attribute20 => X_ATTRIBUTE20,
1537 x_creation_date => X_LAST_UPDATE_DATE ,
1538 x_created_by => X_LAST_UPDATED_BY ,
1539 x_last_update_date => X_LAST_UPDATE_DATE ,
1540 x_last_updated_by => X_LAST_UPDATED_BY ,
1541 x_last_update_login => X_LAST_UPDATE_LOGIN ,
1542 x_program_length => X_program_length,
1543 x_program_length_measurement => X_program_length_measurement
1544 );
1545
1546 --Locking issue build(bug#2797116),this check child existance has been put deliberately
1547 IF NVL(x_delete_flag,'N') = 'Y' THEN
1548 Check_Child_Existance;
1549 END IF;
1550
1551 update IGS_PS_OFR_OPT_ALL set
1552 COO_ID = NEW_REFERENCES.COO_ID,
1553 FORCED_LOCATION_IND = NEW_REFERENCES.FORCED_LOCATION_IND,
1554 FORCED_ATT_MODE_IND = NEW_REFERENCES.FORCED_ATT_MODE_IND,
1555 FORCED_ATT_TYPE_IND = NEW_REFERENCES.FORCED_ATT_TYPE_IND,
1556 TIME_LIMITATION = NEW_REFERENCES.TIME_LIMITATION,
1557 ENR_OFFICER_PERSON_ID = NEW_REFERENCES.ENR_OFFICER_PERSON_ID,
1558 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1559 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1560 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1561 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1562 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1563 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1564 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1565 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1566 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1567 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1568 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1569 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1570 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1571 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1572 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1573 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1574 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1575 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1576 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1577 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1578 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1579 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1580 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1581 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1582 PROGRAM_LENGTH = NEW_REFERENCES.PROGRAM_LENGTH,
1583 PROGRAM_LENGTH_MEASUREMENT = NEW_REFERENCES.PROGRAM_LENGTH_MEASUREMENT,
1584 DELETE_FLAG = NVL(X_DELETE_FLAG,'N')
1585 where ROWID = X_ROWID
1586 ;
1587 if (sql%notfound) then
1588 raise no_data_found;
1589 end if;
1590 After_DML (
1591 p_action => 'UPDATE',
1592 x_rowid => X_ROWID
1593 );
1594 end UPDATE_ROW;
1595
1596 procedure ADD_ROW (
1597 X_ROWID in out NOCOPY VARCHAR2,
1598 X_COURSE_CD in VARCHAR2,
1599 X_VERSION_NUMBER in NUMBER,
1600 X_CAL_TYPE in VARCHAR2,
1601 X_ATTENDANCE_MODE in VARCHAR2,
1602 X_ATTENDANCE_TYPE in VARCHAR2,
1603 X_LOCATION_CD in VARCHAR2,
1604 X_COO_ID in NUMBER,
1605 X_FORCED_LOCATION_IND in VARCHAR2,
1606 X_FORCED_ATT_MODE_IND in VARCHAR2,
1607 X_FORCED_ATT_TYPE_IND in VARCHAR2,
1608 X_TIME_LIMITATION in NUMBER,
1609 X_ENR_OFFICER_PERSON_ID in NUMBER,
1610 X_ATTRIBUTE_CATEGORY in VARCHAR2 ,
1611 X_ATTRIBUTE1 in VARCHAR2 ,
1612 X_ATTRIBUTE2 in VARCHAR2 ,
1613 X_ATTRIBUTE3 in VARCHAR2 ,
1614 X_ATTRIBUTE4 in VARCHAR2 ,
1615 X_ATTRIBUTE5 in VARCHAR2 ,
1616 X_ATTRIBUTE6 in VARCHAR2 ,
1617 X_ATTRIBUTE7 in VARCHAR2 ,
1618 X_ATTRIBUTE8 in VARCHAR2 ,
1619 X_ATTRIBUTE9 in VARCHAR2 ,
1620 X_ATTRIBUTE10 in VARCHAR2 ,
1621 X_ATTRIBUTE11 in VARCHAR2 ,
1622 X_ATTRIBUTE12 in VARCHAR2 ,
1623 X_ATTRIBUTE13 in VARCHAR2 ,
1624 X_ATTRIBUTE14 in VARCHAR2 ,
1625 X_ATTRIBUTE15 in VARCHAR2 ,
1626 X_ATTRIBUTE16 in VARCHAR2 ,
1627 X_ATTRIBUTE17 in VARCHAR2 ,
1628 X_ATTRIBUTE18 in VARCHAR2 ,
1629 X_ATTRIBUTE19 in VARCHAR2 ,
1630 X_ATTRIBUTE20 in VARCHAR2 ,
1631 X_MODE in VARCHAR2,
1632 X_ORG_ID IN NUMBER,
1633 X_PROGRAM_LENGTH IN NUMBER ,
1634 X_PROGRAM_LENGTH_MEASUREMENT IN VARCHAR2 ,
1635 X_DELETE_FLAG IN VARCHAR2
1636 ) AS
1637 cursor c1 is select rowid from IGS_PS_OFR_OPT_ALL
1638 where COURSE_CD = X_COURSE_CD
1639 and VERSION_NUMBER = X_VERSION_NUMBER
1640 and CAL_TYPE = X_CAL_TYPE
1641 and ATTENDANCE_MODE = X_ATTENDANCE_MODE
1642 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
1643 and LOCATION_CD = X_LOCATION_CD
1644 ;
1645 begin
1646 open c1;
1647 fetch c1 into X_ROWID;
1648 if (c1%notfound) then
1649 close c1;
1650 INSERT_ROW (
1651 X_ROWID,
1652 X_COURSE_CD,
1653 X_VERSION_NUMBER,
1654 X_CAL_TYPE,
1655 X_ATTENDANCE_MODE,
1656 X_ATTENDANCE_TYPE,
1657 X_LOCATION_CD,
1658 X_COO_ID,
1659 X_FORCED_LOCATION_IND,
1660 X_FORCED_ATT_MODE_IND,
1661 X_FORCED_ATT_TYPE_IND,
1662 X_TIME_LIMITATION,
1663 X_ENR_OFFICER_PERSON_ID,
1664 X_ATTRIBUTE_CATEGORY,
1665 X_ATTRIBUTE1,
1666 X_ATTRIBUTE2,
1667 X_ATTRIBUTE3,
1668 X_ATTRIBUTE4,
1669 X_ATTRIBUTE5,
1670 X_ATTRIBUTE6,
1671 X_ATTRIBUTE7,
1672 X_ATTRIBUTE8,
1673 X_ATTRIBUTE9,
1674 X_ATTRIBUTE10,
1675 X_ATTRIBUTE11,
1676 X_ATTRIBUTE12,
1677 X_ATTRIBUTE13,
1678 X_ATTRIBUTE14,
1679 X_ATTRIBUTE15,
1680 X_ATTRIBUTE16,
1681 X_ATTRIBUTE17,
1682 X_ATTRIBUTE18,
1683 X_ATTRIBUTE19,
1684 X_ATTRIBUTE20,
1685 X_MODE,
1686 x_org_id,
1687 X_PROGRAM_LENGTH,
1688 X_PROGRAM_LENGTH_MEASUREMENT);
1689 return;
1690 end if;
1691 close c1;
1692 UPDATE_ROW (
1693 X_ROWID,
1694 X_COURSE_CD,
1695 X_VERSION_NUMBER,
1696 X_CAL_TYPE,
1697 X_ATTENDANCE_MODE,
1698 X_ATTENDANCE_TYPE,
1699 X_LOCATION_CD,
1700 X_COO_ID,
1701 X_FORCED_LOCATION_IND,
1702 X_FORCED_ATT_MODE_IND,
1703 X_FORCED_ATT_TYPE_IND,
1704 X_TIME_LIMITATION,
1705 X_ENR_OFFICER_PERSON_ID,
1706 X_ATTRIBUTE_CATEGORY,
1707 X_ATTRIBUTE1,
1708 X_ATTRIBUTE2,
1709 X_ATTRIBUTE3,
1710 X_ATTRIBUTE4,
1711 X_ATTRIBUTE5,
1712 X_ATTRIBUTE6,
1713 X_ATTRIBUTE7,
1714 X_ATTRIBUTE8,
1715 X_ATTRIBUTE9,
1716 X_ATTRIBUTE10,
1717 X_ATTRIBUTE11,
1718 X_ATTRIBUTE12,
1719 X_ATTRIBUTE13,
1720 X_ATTRIBUTE14,
1721 X_ATTRIBUTE15,
1722 X_ATTRIBUTE16,
1723 X_ATTRIBUTE17,
1724 X_ATTRIBUTE18,
1725 X_ATTRIBUTE19,
1726 X_ATTRIBUTE20,
1727 X_MODE ,
1728 X_PROGRAM_LENGTH,
1729 X_PROGRAM_LENGTH_MEASUREMENT);
1730 end ADD_ROW;
1731
1732 procedure DELETE_ROW (
1733 X_ROWID in VARCHAR2
1734 ) AS
1735 begin
1736 Before_DML (
1737 p_action => 'DELETE',
1738 x_rowid => X_ROWID
1739 );
1740 delete from IGS_PS_OFR_OPT_ALL
1741 where ROWID = X_ROWID;
1742 if (sql%notfound) then
1743 raise no_data_found;
1744 end if;
1745 After_DML (
1746 p_action => 'DELETE',
1747 x_rowid => X_ROWID
1748 );
1749 end DELETE_ROW;
1750
1751 end IGS_PS_OFR_OPT_PKG;