[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_OFR_PAT_PKG
Source
1 package body IGS_PS_OFR_PAT_PKG AS
2 /* $Header: IGSPI25B.pls 115.7 2002/11/29 02:13:15 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_OFR_PAT%RowType;
6 new_references IGS_PS_OFR_PAT%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_location_cd IN VARCHAR2 DEFAULT NULL,
16 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
17 x_attendance_type IN VARCHAR2 DEFAULT NULL,
18 x_cop_id IN NUMBER DEFAULT NULL,
19 x_coo_id IN NUMBER DEFAULT NULL,
20 x_offered_ind IN VARCHAR2 DEFAULT NULL,
21 x_confirmed_offering_ind IN VARCHAR2 DEFAULT NULL,
22 x_entry_point_ind IN VARCHAR2 DEFAULT NULL,
23 x_pre_enrol_units_ind IN VARCHAR2 DEFAULT NULL,
24 x_enrollable_ind IN VARCHAR2 DEFAULT NULL,
25 x_ivrs_available_ind IN VARCHAR2 DEFAULT NULL,
26 x_min_entry_ass_score IN NUMBER DEFAULT NULL,
27 x_guaranteed_entry_ass_scr IN NUMBER DEFAULT NULL,
28 x_max_cross_faculty_cp IN NUMBER DEFAULT NULL,
29 x_max_cross_location_cp IN NUMBER DEFAULT NULL,
30 x_max_cross_mode_cp IN NUMBER DEFAULT NULL,
31 x_max_hist_cross_faculty_cp IN NUMBER DEFAULT NULL,
32 x_adm_ass_officer_person_id IN NUMBER DEFAULT NULL,
33 x_adm_contact_person_id IN NUMBER DEFAULT NULL,
34 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
35 x_gs_version_number IN NUMBER DEFAULT NULL,
36 x_creation_date IN DATE DEFAULT NULL,
37 x_created_by IN NUMBER DEFAULT NULL,
38 x_last_update_date IN DATE DEFAULT NULL,
39 x_last_updated_by IN NUMBER DEFAULT NULL,
40 x_last_update_login IN NUMBER DEFAULT NULL
41 ) AS
42
43 CURSOR cur_old_ref_values IS
44 SELECT *
45 FROM IGS_PS_OFR_PAT
46 WHERE rowid = x_rowid;
47
48 BEGIN
49
50 l_rowid := x_rowid;
51
52 -- Code for setting the Old and New Reference Values.
53 -- Populate Old Values.
54 Open cur_old_ref_values;
55 Fetch cur_old_ref_values INTO old_references;
56 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
57 Close cur_old_ref_values;
58 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61 Return;
62 END IF;
63 Close cur_old_ref_values;
64
65 -- Populate New Values.
66 new_references.course_cd := x_course_cd;
67 new_references.version_number := x_version_number;
68 new_references.cal_type := x_cal_type;
69 new_references.ci_sequence_number := x_ci_sequence_number;
70 new_references.location_cd := x_location_cd;
71 new_references.attendance_mode := x_attendance_mode;
72 new_references.attendance_type := x_attendance_type;
73 new_references.cop_id := x_cop_id;
74 new_references.coo_id := x_coo_id;
75 new_references.offered_ind := x_offered_ind;
76 new_references.confirmed_offering_ind := x_confirmed_offering_ind;
77 new_references.entry_point_ind := x_entry_point_ind;
78 new_references.pre_enrol_units_ind := x_pre_enrol_units_ind;
79 new_references.enrollable_ind := x_enrollable_ind;
80 new_references.ivrs_available_ind := x_ivrs_available_ind;
81 new_references.min_entry_ass_score := x_min_entry_ass_score;
82 new_references.guaranteed_entry_ass_scr := x_guaranteed_entry_ass_scr;
83 new_references.max_cross_faculty_cp := x_max_cross_faculty_cp;
84 new_references.max_cross_location_cp := x_max_cross_location_cp;
85 new_references.max_cross_mode_cp := x_max_cross_mode_cp;
86 new_references.max_hist_cross_faculty_cp := x_max_hist_cross_faculty_cp;
87 new_references.adm_ass_officer_person_id := x_adm_ass_officer_person_id;
88 new_references.adm_contact_person_id := x_adm_contact_person_id;
89 new_references.grading_schema_cd := x_grading_schema_cd;
90 new_references.gs_version_number := x_gs_version_number;
91 IF (p_action = 'UPDATE') THEN
92 new_references.creation_date := old_references.creation_date;
93 new_references.created_by := old_references.created_by;
94 ELSE
95 new_references.creation_date := x_creation_date;
96 new_references.created_by := x_created_by;
97 END IF;
98 new_references.last_update_date := x_last_update_date;
99 new_references.last_updated_by := x_last_updated_by;
100 new_references.last_update_login := x_last_update_login;
101
102 END Set_Column_Values;
103
104 -- Trigger description :-
105 -- "OSS_TST".TRG_COP_BR_IUD
106 -- BEFORE INSERT OR UPDATE OR DELETE ON IGS_PS_OFR_PAT
107 -- REFERENCING
108 -- NEW AS NEW
109 -- OLD AS OLD
110 -- FOR EACH ROW
111
112 PROCEDURE BeforeRowInsertUpdateDelete1(
113 p_inserting IN BOOLEAN DEFAULT FALSE,
114 p_updating IN BOOLEAN DEFAULT FALSE,
115 p_deleting IN BOOLEAN DEFAULT FALSE
116 ) AS
117 v_message_name VARCHAR2(30);
118 v_course_cd IGS_PS_VER.course_cd%TYPE;
119 v_version_number IGS_PS_VER.version_number%TYPE;
120 v_cal_type IGS_PS_OFR_PAT.cal_type%TYPE;
121 v_ci_sequence_number IGS_PS_OFR_PAT.ci_sequence_number%TYPE;
122 BEGIN
123
124 -- Set IGS_PS_OFR_OPT key.
125 IF p_inserting THEN
126 IGS_PS_GEN_003.CRSP_GET_COO_KEY (
127 new_references.coo_id,
128 new_references.course_cd,
129 new_references.version_number,
130 new_references.cal_type,
131 new_references.location_cd,
132 new_references.attendance_mode,
133 new_references.attendance_type);
134 END IF;
135 -- Set variables
136 IF p_inserting OR p_updating THEN
137 v_course_cd := new_references.course_cd;
138 v_version_number := new_references.version_number;
139 v_cal_type := new_references.cal_type;
140 v_ci_sequence_number := new_references.ci_sequence_number;
141 ELSE -- p_deleting
142 v_course_cd := old_references.course_cd;
143 v_version_number := old_references.version_number;
144 v_cal_type := old_references.cal_type;
145 v_ci_sequence_number := old_references.ci_sequence_number;
146 END IF;
147 -- Validate that IGS_PS_COURSE version is active
148 IF IGS_PS_VAL_CRS.CRSP_VAL_IUD_CRV_DTL(
149 v_course_cd,
150 v_version_number,
151 v_message_name) = FALSE THEN
152 Fnd_Message.Set_Name('IGS',v_message_name);
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156 -- Validate calendar instance is active
157 IF igs_as_val_uai.crsp_val_crs_ci (
158 v_cal_type,
159 v_ci_sequence_number,
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 -- Validate that inserts are allowed
166 IF p_inserting THEN
167 -- Validate IGS_PS_COURSE offering option
168 -- IGS_GE_NOTE:crsp_val_iud_crv_dtl called from this function
169 IF IGS_PS_VAL_COp.crsp_val_coo_inactiv (
170 new_references.course_cd,
171 new_references.version_number,
172 new_references.cal_type,
173 new_references.location_cd,
174 new_references.attendance_mode,
175 new_references.attendance_type,
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 END IF;
182 IF p_inserting OR p_updating THEN
183 -- Validate entry assessment scores.
184 IF IGS_PS_VAL_COI.crsp_val_ent_ass_scr(
185 new_references.min_entry_ass_score,
186 new_references.guaranteed_entry_ass_scr,
187 v_message_name) = FALSE THEN
188 Fnd_Message.Set_Name('IGS',v_message_name);
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193
194
195 END BeforeRowInsertUpdateDelete1;
196
197 PROCEDURE Check_Uniqueness
198 AS
199 BEGIN
200 IF Get_UK_For_Validation(
201 new_references.cop_id
202 )THEN
203 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
204 IGS_GE_MSG_STACK.ADD;
205 App_Exception.Raise_Exception;
206 END IF;
207 END Check_Uniqueness;
208
209 PROCEDURE Check_Constraints(
210 Column_Name IN VARCHAR2 DEFAULT NULL,
211 Column_Value IN VARCHAR2 DEFAULT NULL
212 ) AS
213 BEGIN
214 IF column_name is null THEN
215 NULL;
216 ELSIF upper(column_name) = 'ATTENDANCE_MODE' THEN
217 new_references.attendance_mode := column_value;
218 ELSIF upper(column_name) = 'ATTENDANCE_TYPE' THEN
219 new_references.attendance_type := column_value;
220 ELSIF upper(column_name) = 'CAL_TYPE' THEN
221 new_references.cal_type := column_value;
222 ELSIF upper(column_name) = 'CONFIRMED_OFFERING_IND' THEN
223 new_references.confirmed_offering_ind := column_value;
224 ELSIF upper(column_name) = 'COURSE_CD' THEN
225 new_references.course_cd := column_value;
226 ELSIF upper(column_name) = 'ENROLLABLE_IND' THEN
227 new_references.enrollable_ind := column_value;
228 ELSIF upper(column_name) = 'ENTRY_POINT_IND' THEN
229 new_references.entry_point_ind := column_value;
230 ELSIF upper(column_name) = 'GRADING_SCHEMA_CD' THEN
231 new_references.grading_schema_cd := column_value;
232 ELSIF upper(column_name) = 'IVRS_AVAILABLE_IND' THEN
233 new_references.ivrs_available_ind := column_value;
234 ELSIF upper(column_name) = 'LOCATION_CD' THEN
235 new_references.location_cd := column_value;
236 ELSIF upper(column_name) = 'OFFERED_IND' THEN
237 new_references.offered_ind := column_value;
238 ELSIF upper(column_name) = 'PRE_ENROL_UNITS_IND' THEN
239 new_references.pre_enrol_units_ind := column_value;
240 ELSIF upper(column_name) = 'MIN_ENTRY_ASS_SCORE' THEN
241 new_references.min_entry_ass_score := IGS_GE_NUMBER.TO_NUM(column_value);
242 ELSIF upper(column_name) = 'GUARANTEED_ENTRY_ASS_SCR' THEN
243 new_references.guaranteed_entry_ass_scr := IGS_GE_NUMBER.TO_NUM(column_value);
244 ELSIF upper(column_name) = 'MAX_CROSS_FACULTY_CP' THEN
245 new_references.max_cross_faculty_cp := IGS_GE_NUMBER.TO_NUM(column_value);
246 ELSIF upper(column_name) = 'MAX_CROSS_LOCATION_CP' THEN
247 new_references.max_cross_location_cp:= IGS_GE_NUMBER.TO_NUM(column_value);
248 ELSIF upper(column_name) = 'MAX_CROSS_MODE_CP' THEN
249 new_references.max_cross_mode_cp := IGS_GE_NUMBER.TO_NUM(column_value);
250 ELSIF upper(column_name) = 'MAX_HIST_CROSS_FACULTY_CP' THEN
251 new_references.max_hist_cross_faculty_cp := IGS_GE_NUMBER.TO_NUM(column_value);
252 END IF;
253
254 IF upper(column_name) = 'ATTENDANCE_MODE' OR
255 column_name is null THEN
256 IF new_references.attendance_mode <> UPPER(new_references.attendance_mode)
257 THEN
258 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 END IF;
262 END IF;
263
264 IF upper(column_name)= 'ATTENDANCE_TYPE' OR
265 column_name is null THEN
266 IF new_references.attendance_type <> UPPER(new_references.attendance_type)
267 THEN
268 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF;
272 END IF;
273
274 IF upper(column_name)= 'CAL_TYPE' OR
275 column_name is null THEN
276 IF new_references.cal_type <> UPPER(new_references.cal_type)
277 THEN
278 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 END IF;
283
284 IF upper(column_name)= 'COURSE_CD' OR
285 column_name is null THEN
286 IF new_references.course_cd <> UPPER(new_references.course_cd)
287 THEN
288 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END IF;
292 END IF;
293
294 IF upper(column_name)= 'GRADING_SCHEMA_CD' OR
295 column_name is null THEN
296 IF new_references.grading_schema_cd <> UPPER(new_references.grading_schema_cd)
297 THEN
298 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 END IF;
302 END IF;
303
304 IF upper(column_name)= 'LOCATION_CD' OR
305 column_name is null THEN
306 IF new_references.location_cd <> UPPER(new_references.location_cd)
307 THEN
308 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END IF;
312 END IF;
313
314 IF upper(column_name)= 'PRE_ENROL_UNITS_IND' OR
315 column_name is null THEN
316 IF new_references.pre_enrol_units_ind NOT IN ( 'Y' , 'N' )
317 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
325 IF upper(column_name)= 'ENROLLABLE_IND' OR
326 column_name is null THEN
327 IF new_references.enrollable_ind NOT IN ( 'Y' , 'N' )
328 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 IF upper(column_name)= 'IVRS_AVAILABLE_IND' OR
336 column_name is null THEN
337 IF new_references.ivrs_available_ind NOT IN ( 'Y' , 'N' )
338 THEN
339 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
340 IGS_GE_MSG_STACK.ADD;
341 App_Exception.Raise_Exception;
342 END IF;
343 END IF;
344
345 IF upper(column_name)= 'MIN_ENTRY_ASS_SCORE' OR
346 column_name is null THEN
347 IF new_references.min_entry_ass_score < 1 OR
348 new_references.min_entry_ass_score > 999
349 THEN
350 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
351 IGS_GE_MSG_STACK.ADD;
352 App_Exception.Raise_Exception;
353 END IF;
354 END IF;
355
356
357 IF column_name is null THEN
358 IF new_references.min_entry_ass_score > new_references.guaranteed_entry_ass_scr
359 THEN
360 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception;
363 END IF;
364 END IF;
365
366 IF upper(column_name)= 'GUARANTEED_ENTRY_ASS_SCR' OR
367 column_name is null THEN
368 IF new_references.guaranteed_entry_ass_scr <1 OR
369 new_references.guaranteed_entry_ass_scr > 999
370 THEN
371 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
372 IGS_GE_MSG_STACK.ADD;
373 App_Exception.Raise_Exception;
374 END IF;
375 END IF;
376
377 IF upper(column_name)= 'MAX_CROSS_FACULTY_CP' OR
378 column_name is null THEN
379 IF new_references.max_cross_faculty_cp <0.001 OR --Changes as per Bug# 2022150
380 new_references.max_cross_faculty_cp > 999.999
381 THEN
382 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
383 IGS_GE_MSG_STACK.ADD;
384 App_Exception.Raise_Exception;
385 END IF;
386 END IF;
387
388 IF upper(column_name)= 'MAX_CROSS_LOCATION_CP' OR
389 column_name is null THEN
390 IF new_references.max_cross_location_cp <0.001 OR
391 new_references.max_cross_location_cp > 999.999
392 THEN
393 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 END IF;
397 END IF;
398
399 IF upper(column_name)= 'MAX_CROSS_MODE_CP' OR
400 column_name is null THEN
401 IF new_references.max_cross_mode_cp < 0.001 OR
402 new_references.max_cross_mode_cp > 999.999
403 THEN
404 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
405 IGS_GE_MSG_STACK.ADD;
406 App_Exception.Raise_Exception;
407 END IF;
408 END IF;
409
410 IF upper(column_name)= 'MAX_HIST_CROSS_FACULTY_CP' OR
411 column_name is null THEN
412 IF new_references.max_hist_cross_faculty_cp < 0.001 OR
413 new_references.max_hist_cross_faculty_cp > 999.999
414 THEN
415 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 END IF;
419 END IF;
420
421 IF upper(column_name)= 'OFFERED_IND' OR
422 column_name is null THEN
423 IF new_references.offered_ind NOT IN ( 'Y' , 'N' )
424 THEN
425 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429 END IF;
430
431 IF upper(column_name)= 'CONFIRMED_OFFERING_IND' OR
432 column_name is null THEN
433 IF new_references.confirmed_offering_ind NOT IN ( 'Y' , 'N' )
434 THEN
435 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
436 IGS_GE_MSG_STACK.ADD;
437 App_Exception.Raise_Exception;
438 END IF;
439 END IF;
440
441 IF upper(column_name)= 'ENTRY_POINT_IND' OR
442 column_name is null THEN
443 IF new_references.entry_point_ind NOT IN ( 'Y' , 'N' )
444 THEN
445 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
446 IGS_GE_MSG_STACK.ADD;
447 App_Exception.Raise_Exception;
448 END IF;
449 END IF;
450
451 END Check_Constraints;
452
453 PROCEDURE Check_Parent_Existance AS
454 BEGIN
455
456 IF (((old_references.course_cd = new_references.course_cd) AND
457 (old_references.version_number = new_references.version_number) AND
458 (old_references.cal_type = new_references.cal_type) AND
459 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
460 ((new_references.course_cd IS NULL) OR
461 (new_references.version_number IS NULL) OR
462 (new_references.cal_type IS NULL) OR
463 (new_references.ci_sequence_number IS NULL))) THEN
464 NULL;
465 ELSE
466 IF NOT IGS_PS_OFR_INST_PKG.Get_PK_For_Validation(
467 new_references.course_cd,
468 new_references.version_number,
469 new_references.cal_type,
470 new_references.ci_sequence_number
471 )THEN
472 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
473 IGS_GE_MSG_STACK.ADD;
474 App_Exception.Raise_Exception;
475 END IF;
476 END IF;
477
478 IF (((old_references.course_cd = new_references.course_cd) AND
479 (old_references.version_number = new_references.version_number) AND
480 (old_references.cal_type = new_references.cal_type) AND
481 (old_references.location_cd = new_references.location_cd) AND
482 (old_references.attendance_mode = new_references.attendance_mode) AND
483 (old_references.attendance_type = new_references.attendance_type)) OR
484 ((new_references.course_cd IS NULL) OR
485 (new_references.version_number IS NULL) OR
486 (new_references.cal_type IS NULL) OR
487 (new_references.location_cd IS NULL) OR
488 (new_references.attendance_mode IS NULL) OR
489 (new_references.attendance_type IS NULL))) THEN
490 NULL;
491 ELSE
492 IF NOT IGS_PS_OFR_OPT_PKG.Get_PK_For_Validation (
493 new_references.course_cd,
494 new_references.version_number,
495 new_references.cal_type,
496 new_references.location_cd,
497 new_references.attendance_mode,
498 new_references.attendance_type
499 )THEN
500 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
501 IGS_GE_MSG_STACK.ADD;
502 App_Exception.Raise_Exception;
503 END IF;
504 END IF;
505
506 IF (((old_references.coo_id = new_references.coo_id)) OR
507 ((new_references.coo_id IS NULL))) THEN
508 NULL;
509 ELSE
510 IF NOT IGS_PS_OFR_OPT_PKG.Get_UK_For_Validation (
511 new_references.coo_id
512 )THEN
513 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
514 IGS_GE_MSG_STACK.ADD;
515 App_Exception.Raise_Exception;
516 END IF;
517 END IF;
518
519 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
520 (old_references.gs_version_number = new_references.gs_version_number)) OR
521 ((new_references.grading_schema_cd IS NULL) OR
522 (new_references.gs_version_number IS NULL))) THEN
523 NULL;
524 ELSE
525 IF NOT IGS_AS_GRD_SCHEMA_PKG.Get_PK_For_Validation (
526 new_references.grading_schema_cd,
527 new_references.gs_version_number
528 )THEN
529 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
530 IGS_GE_MSG_STACK.ADD;
531 App_Exception.Raise_Exception;
532 END IF;
533 END IF;
534
535 IF (((old_references.adm_ass_officer_person_id = new_references.adm_ass_officer_person_id)) OR
536 ((new_references.adm_ass_officer_person_id IS NULL))) THEN
537 NULL;
538 ELSE
539 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
540 new_references.adm_ass_officer_person_id
541 )THEN
542 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
543 IGS_GE_MSG_STACK.ADD;
544 App_Exception.Raise_Exception;
545 END IF;
546 END IF;
547
548 IF (((old_references.adm_contact_person_id = new_references.adm_contact_person_id)) OR
549 ((new_references.adm_contact_person_id IS NULL))) THEN
550 NULL;
551 ELSE
552 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
553 new_references.adm_contact_person_id
554 )THEN
555 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
556 IGS_GE_MSG_STACK.ADD;
557 App_Exception.Raise_Exception;
558 END IF;
559 END IF;
560
561 END Check_Parent_Existance;
562
563 PROCEDURE Check_Child_Existance AS
564 BEGIN
565
566 IGS_PS_OFR_PAT_NOTE_PKG.GET_UFK_IGS_PS_OFR_PAT(
567 old_references.cop_id
568 );
569
570 IGS_PS_OFR_PAT_NOTE_PKG.GET_FK_IGS_PS_OFR_PAT(
571 old_references.course_cd,
572 old_references.version_number,
573 old_references.cal_type,
574 old_references.ci_sequence_number,
575 old_references.location_cd,
576 old_references.attendance_mode,
577 old_references.attendance_type
578 );
579
580 END Check_Child_Existance;
581
582 PROCEDURE Check_UK_Child_Existance IS
583 BEGIN
584 IF (((old_references.cop_id = new_references.cop_id)) OR
585 ((old_references.cop_id IS NULL)))THEN
586 NULL;
587 ELSE
588 IGS_PS_OFR_PAT_NOTE_PKG.GET_UFK_IGS_PS_OFR_PAT(old_references.cop_id
589 );
590 END IF;
591 END Check_UK_Child_Existance;
592
593 FUNCTION Get_PK_For_Validation (
594 x_course_cd IN VARCHAR2,
595 x_version_number IN NUMBER,
596 x_cal_type IN VARCHAR2,
597 x_ci_sequence_number IN NUMBER,
598 x_location_cd IN VARCHAR2,
599 x_attendance_mode IN VARCHAR2,
600 x_attendance_type IN VARCHAR2
601 ) RETURN BOOLEAN AS
602
603 CURSOR cur_rowid IS
604 SELECT rowid
605 FROM IGS_PS_OFR_PAT
606 WHERE course_cd = x_course_cd
607 AND version_number = x_version_number
608 AND cal_type = x_cal_type
609 AND ci_sequence_number = x_ci_sequence_number
610 AND location_cd = x_location_cd
611 AND attendance_mode = x_attendance_mode
612 AND attendance_type = x_attendance_type
613 FOR UPDATE NOWAIT;
614
615 lv_rowid cur_rowid%RowType;
616
617 BEGIN
618
619 Open cur_rowid;
620 Fetch cur_rowid INTO lv_rowid;
621 IF (cur_rowid%FOUND) THEN
622 Close cur_rowid;
623 Return(TRUE);
624 ELSE
625 Close cur_rowid;
626 Return(FALSE);
627 END IF;
628
629 END Get_PK_For_Validation;
630
631 FUNCTION Get_UK_For_Validation (
632 x_cop_id IN NUMBER
633 ) RETURN BOOLEAN AS
634
635 CURSOR cur_rowid IS
636 SELECT rowid
637 FROM IGS_PS_OFR_PAT
638 WHERE cop_id = x_cop_id
639 AND (l_rowid IS NULL OR rowid <> l_rowid)
640
641 FOR UPDATE NOWAIT;
642
643 lv_rowid cur_rowid%RowType;
644
645 BEGIN
646
647 Open cur_rowid;
648 Fetch cur_rowid INTO lv_rowid;
649 IF (cur_rowid%FOUND) THEN
650 Close cur_rowid;
651 Return(TRUE);
652 ELSE
653 Close cur_rowid;
654 Return(FALSE);
655 END IF;
656
657 END Get_UK_For_Validation;
658
659
660 PROCEDURE GET_FK_IGS_PS_OFR_INST (
661 x_course_cd IN VARCHAR2,
662 x_version_number IN NUMBER,
663 x_cal_type IN VARCHAR2,
664 x_ci_sequence_number IN NUMBER
665 ) AS
666
667 CURSOR cur_rowid IS
668 SELECT rowid
669 FROM IGS_PS_OFR_PAT
670 WHERE course_cd = x_course_cd
671 AND version_number = x_version_number
672 AND cal_type = x_cal_type
673 AND ci_sequence_number = x_ci_sequence_number ;
674
675 lv_rowid cur_rowid%RowType;
676 BEGIN
677
678 Open cur_rowid;
679 Fetch cur_rowid INTO lv_rowid;
680 IF (cur_rowid%FOUND) THEN
681 Close cur_rowid;
682 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COP_COI_FK');
683 IGS_GE_MSG_STACK.ADD;
684 App_Exception.Raise_Exception;
685 Return;
686 END IF;
687 Close cur_rowid;
688
689 END GET_FK_IGS_PS_OFR_INST;
690
691 PROCEDURE GET_FK_IGS_PS_OFR_OPT (
692 x_course_cd IN VARCHAR2,
693 x_version_number IN NUMBER,
694 x_cal_type IN VARCHAR2,
695 x_location_cd IN VARCHAR2,
696 x_attendance_mode IN VARCHAR2,
697 x_attendance_type IN VARCHAR2
698 ) AS
699
700 CURSOR cur_rowid IS
701 SELECT rowid
702 FROM IGS_PS_OFR_PAT
703 WHERE course_cd = x_course_cd
704 AND version_number = x_version_number
705 AND cal_type = x_cal_type
706 AND location_cd = x_location_cd
707 AND attendance_mode = x_attendance_mode
708 AND attendance_type = x_attendance_type ;
709
710 lv_rowid cur_rowid%RowType;
711
712 BEGIN
713
714 Open cur_rowid;
715 Fetch cur_rowid INTO lv_rowid;
716 IF (cur_rowid%FOUND) THEN
717 Close cur_rowid;
718 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COP_COO_FK');
719 IGS_GE_MSG_STACK.ADD;
720 App_Exception.Raise_Exception;
721 Return;
722 END IF;
723 Close cur_rowid;
724
725 END GET_FK_IGS_PS_OFR_OPT;
726
727 PROCEDURE GET_UFK_IGS_PS_OFR_OPT (
728 x_coo_id IN NUMBER
729 ) AS
730
731 CURSOR cur_rowid IS
732 SELECT rowid
733 FROM IGS_PS_OFR_PAT
734 WHERE coo_id = x_coo_id ;
735
736 lv_rowid cur_rowid%RowType;
737
738 BEGIN
739
740 Open cur_rowid;
741 Fetch cur_rowid INTO lv_rowid;
742 IF (cur_rowid%FOUND) THEN
743 Close cur_rowid;
744 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COP_COO_FK');
745 IGS_GE_MSG_STACK.ADD;
746 App_Exception.Raise_Exception;
747 Return;
748 END IF;
749 Close cur_rowid;
750
751 END GET_UFK_IGS_PS_OFR_OPT;
752
753 PROCEDURE GET_FK_IGS_AS_GRD_SCHEMA (
754 x_grading_schema_cd IN VARCHAR2,
755 x_version_number IN NUMBER
756 ) AS
757
758 CURSOR cur_rowid IS
759 SELECT rowid
760 FROM IGS_PS_OFR_PAT
761 WHERE grading_schema_cd = x_grading_schema_cd
762 AND gs_version_number = x_version_number ;
763
764 lv_rowid cur_rowid%RowType;
765
766 BEGIN
767
768 Open cur_rowid;
769 Fetch cur_rowid INTO lv_rowid;
770 IF (cur_rowid%FOUND) THEN
771 Close cur_rowid;
772 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COP_GS_FK');
773 IGS_GE_MSG_STACK.ADD;
774 App_Exception.Raise_Exception;
775 Return;
776 END IF;
777 Close cur_rowid;
778
779 END GET_FK_IGS_AS_GRD_SCHEMA;
780
781 PROCEDURE GET_FK_IGS_PE_PERSON (
782 x_person_id IN NUMBER
783 ) AS
784
785 CURSOR cur_rowid IS
786 SELECT rowid
787 FROM IGS_PS_OFR_PAT
788 WHERE (adm_ass_officer_person_id = x_person_id )
789 OR (adm_contact_person_id = x_person_id );
790
791 lv_rowid cur_rowid%RowType;
792
793 BEGIN
794
795 Open cur_rowid;
796 Fetch cur_rowid INTO lv_rowid;
797 IF (cur_rowid%FOUND) THEN
798 Close cur_rowid;
799 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COP_PE_AAO_FK');
800 IGS_GE_MSG_STACK.ADD;
801 App_Exception.Raise_Exception;
802 Return;
803 END IF;
804 Close cur_rowid;
805
806 END GET_FK_IGS_PE_PERSON;
807
808 PROCEDURE Before_DML (
809 p_action IN VARCHAR2,
810 x_rowid IN VARCHAR2 DEFAULT NULL,
811 x_course_cd IN VARCHAR2 DEFAULT NULL,
812 x_version_number IN NUMBER DEFAULT NULL,
813 x_cal_type IN VARCHAR2 DEFAULT NULL,
814 x_ci_sequence_number IN NUMBER DEFAULT NULL,
815 x_location_cd IN VARCHAR2 DEFAULT NULL,
816 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
817 x_attendance_type IN VARCHAR2 DEFAULT NULL,
818 x_cop_id IN NUMBER DEFAULT NULL,
819 x_coo_id IN NUMBER DEFAULT NULL,
820 x_offered_ind IN VARCHAR2 DEFAULT NULL,
821 x_confirmed_offering_ind IN VARCHAR2 DEFAULT NULL,
822 x_entry_point_ind IN VARCHAR2 DEFAULT NULL,
823 x_pre_enrol_units_ind IN VARCHAR2 DEFAULT NULL,
824 x_enrollable_ind IN VARCHAR2 DEFAULT NULL,
825 x_ivrs_available_ind IN VARCHAR2 DEFAULT NULL,
826 x_min_entry_ass_score IN NUMBER DEFAULT NULL,
827 x_guaranteed_entry_ass_scr IN NUMBER DEFAULT NULL,
828 x_max_cross_faculty_cp IN NUMBER DEFAULT NULL,
829 x_max_cross_location_cp IN NUMBER DEFAULT NULL,
830 x_max_cross_mode_cp IN NUMBER DEFAULT NULL,
831 x_max_hist_cross_faculty_cp IN NUMBER DEFAULT NULL,
832 x_adm_ass_officer_person_id IN NUMBER DEFAULT NULL,
833 x_adm_contact_person_id IN NUMBER DEFAULT NULL,
834 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
835 x_gs_version_number IN NUMBER DEFAULT NULL,
836 x_creation_date IN DATE DEFAULT NULL,
837 x_created_by IN NUMBER DEFAULT NULL,
838 x_last_update_date IN DATE DEFAULT NULL,
839 x_last_updated_by IN NUMBER DEFAULT NULL,
840 x_last_update_login IN NUMBER DEFAULT NULL
841 ) AS
842 BEGIN
843
844 Set_Column_Values (
845 p_action,
846 x_rowid,
847 x_course_cd,
848 x_version_number,
849 x_cal_type,
850 x_ci_sequence_number,
851 x_location_cd,
852 x_attendance_mode,
853 x_attendance_type,
854 x_cop_id,
855 x_coo_id,
856 x_offered_ind,
857 x_confirmed_offering_ind,
858 x_entry_point_ind,
859 x_pre_enrol_units_ind,
860 x_enrollable_ind,
861 x_ivrs_available_ind,
862 x_min_entry_ass_score,
863 x_guaranteed_entry_ass_scr,
864 x_max_cross_faculty_cp,
865 x_max_cross_location_cp,
866 x_max_cross_mode_cp,
867 x_max_hist_cross_faculty_cp,
868 x_adm_ass_officer_person_id,
869 x_adm_contact_person_id,
870 x_grading_schema_cd,
871 x_gs_version_number,
872 x_creation_date,
873 x_created_by,
874 x_last_update_date,
875 x_last_updated_by,
876 x_last_update_login
877 );
878
879 IF (p_action = 'INSERT') THEN
880 -- Call all the procedures related to Before Insert.
881 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
882 IF Get_PK_For_Validation(
883 new_references.course_cd ,
884 new_references.version_number ,
885 new_references.cal_type ,
886 new_references.ci_sequence_number ,
887 new_references.location_cd ,
888 new_references.attendance_mode ,
889 new_references.attendance_type
890 ) THEN
891 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
892 IGS_GE_MSG_STACK.ADD;
893 App_Exception.Raise_Exception;
894 END IF;
895 Check_Uniqueness;
896 Check_Constraints;
897 Check_Parent_Existance;
898 ELSIF (p_action = 'UPDATE') THEN
899 -- Call all the procedures related to Before Update.
900 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
901 Check_Uniqueness;
902 Check_Constraints;
903 Check_Parent_Existance;
904 Check_UK_Child_Existance;
905 ELSIF (p_action = 'DELETE') THEN
906 -- Call all the procedures related to Before Delete.
907 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
908 Check_Child_Existance;
909 ELSIF (p_action = 'VALIDATE_INSERT') THEN
910 IF Get_PK_For_Validation(
911 new_references.course_cd ,
912 new_references.version_number ,
913 new_references.cal_type ,
914 new_references.ci_sequence_number ,
915 new_references.location_cd ,
916 new_references.attendance_mode ,
917 new_references.attendance_type
918 ) THEN
919 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
920 IGS_GE_MSG_STACK.ADD;
921 App_Exception.Raise_Exception;
922 END IF;
923 Check_Uniqueness;
924 Check_Constraints;
925 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
926 Check_Uniqueness;
927 Check_Constraints;
928 Check_UK_Child_Existance;
929 ELSIF (p_action = 'VALIDATE_DELETE') THEN
930 Check_Child_Existance;
931 END IF;
932 END Before_DML;
933
934 PROCEDURE After_DML (
935 p_action IN VARCHAR2,
936 x_rowid IN VARCHAR2
937 ) AS
938 BEGIN
939
940 l_rowid := x_rowid;
941
942 l_rowid:=NULL;
943 END After_DML;
944
945 procedure INSERT_ROW (
946 X_ROWID in out NOCOPY VARCHAR2,
947 X_COURSE_CD in VARCHAR2,
948 X_CI_SEQUENCE_NUMBER in NUMBER,
949 X_CAL_TYPE in VARCHAR2,
950 X_VERSION_NUMBER in NUMBER,
951 X_LOCATION_CD in VARCHAR2,
952 X_ATTENDANCE_TYPE in VARCHAR2,
953 X_ATTENDANCE_MODE in VARCHAR2,
954 X_COP_ID in NUMBER,
955 X_COO_ID in NUMBER,
956 X_OFFERED_IND in VARCHAR2,
957 X_CONFIRMED_OFFERING_IND in VARCHAR2,
958 X_ENTRY_POINT_IND in VARCHAR2,
959 X_PRE_ENROL_UNITS_IND in VARCHAR2,
960 X_ENROLLABLE_IND in VARCHAR2,
961 X_IVRS_AVAILABLE_IND in VARCHAR2,
962 X_MIN_ENTRY_ASS_SCORE in NUMBER,
963 X_GUARANTEED_ENTRY_ASS_SCR in NUMBER,
964 X_MAX_CROSS_FACULTY_CP in NUMBER,
965 X_MAX_CROSS_LOCATION_CP in NUMBER,
966 X_MAX_CROSS_MODE_CP in NUMBER,
967 X_MAX_HIST_CROSS_FACULTY_CP in NUMBER,
968 X_ADM_ASS_OFFICER_PERSON_ID in NUMBER,
969 X_ADM_CONTACT_PERSON_ID in NUMBER,
970 X_GRADING_SCHEMA_CD in VARCHAR2,
971 X_GS_VERSION_NUMBER in NUMBER,
972 X_MODE in VARCHAR2 default 'R'
973 ) AS
974 cursor C is select ROWID from IGS_PS_OFR_PAT
975 where COURSE_CD = X_COURSE_CD
976 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
977 and CAL_TYPE = X_CAL_TYPE
978 and VERSION_NUMBER = X_VERSION_NUMBER
979 and LOCATION_CD = X_LOCATION_CD
980 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
981 and ATTENDANCE_MODE = X_ATTENDANCE_MODE;
982 X_LAST_UPDATE_DATE DATE;
983 X_LAST_UPDATED_BY NUMBER;
984 X_LAST_UPDATE_LOGIN NUMBER;
985 X_REQUEST_ID NUMBER;
986 X_PROGRAM_ID NUMBER;
987 X_PROGRAM_APPLICATION_ID NUMBER;
988 X_PROGRAM_UPDATE_DATE DATE;
989
990 begin
991 X_LAST_UPDATE_DATE := SYSDATE;
992 if(X_MODE = 'I') then
993 X_LAST_UPDATED_BY := 1;
994 X_LAST_UPDATE_LOGIN := 0;
995 elsif (X_MODE = 'R') then
996 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
997 if X_LAST_UPDATED_BY is NULL then
998 X_LAST_UPDATED_BY := -1;
999 end if;
1000 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1001 if X_LAST_UPDATE_LOGIN is NULL then
1002 X_LAST_UPDATE_LOGIN := -1;
1003 end if;
1004 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1005 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1006 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1007 IF (X_REQUEST_ID = -1) THEN
1008 X_REQUEST_ID := NULL;
1009 X_PROGRAM_ID := NULL;
1010 X_PROGRAM_APPLICATION_ID := NULL;
1011 X_PROGRAM_UPDATE_DATE := NULL;
1012 ELSE
1013 X_PROGRAM_UPDATE_DATE := SYSDATE;
1014 END IF;
1015 else
1016 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1017 IGS_GE_MSG_STACK.ADD;
1018 app_exception.raise_exception;
1019 end if;
1020 Before_DML (
1021 p_action => 'INSERT',
1022 x_rowid => X_ROWID,
1023 x_course_cd => X_COURSE_CD,
1024 x_version_number => X_VERSION_NUMBER,
1025 x_cal_type => X_CAL_TYPE,
1026 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
1027 x_location_cd => X_LOCATION_CD,
1028 x_attendance_mode => X_ATTENDANCE_MODE,
1029 x_attendance_type => X_ATTENDANCE_TYPE,
1030 x_cop_id => X_COP_ID,
1031 x_coo_id => X_COO_ID,
1032 x_offered_ind => NVL(X_OFFERED_IND,'N'),
1033 x_confirmed_offering_ind => NVL(X_CONFIRMED_OFFERING_IND,'Y'),
1034 x_entry_point_ind => NVL(X_ENTRY_POINT_IND,'Y'),
1035 x_pre_enrol_units_ind => NVL(X_PRE_ENROL_UNITS_IND,'Y'),
1036 x_enrollable_ind => NVL(X_ENROLLABLE_IND,'Y'),
1037 x_ivrs_available_ind => NVL(X_IVRS_AVAILABLE_IND,'Y'),
1038 x_min_entry_ass_score => X_MIN_ENTRY_ASS_SCORE ,
1039 x_guaranteed_entry_ass_scr => X_GUARANTEED_ENTRY_ASS_SCR ,
1040 x_max_cross_faculty_cp => X_MAX_CROSS_FACULTY_CP ,
1041 x_max_cross_location_cp => X_MAX_CROSS_LOCATION_CP ,
1042 x_max_cross_mode_cp => X_MAX_CROSS_MODE_CP ,
1043 x_max_hist_cross_faculty_cp => X_MAX_HIST_CROSS_FACULTY_CP ,
1044 x_adm_ass_officer_person_id => X_ADM_ASS_OFFICER_PERSON_ID ,
1045 x_adm_contact_person_id => X_ADM_CONTACT_PERSON_ID ,
1046 x_grading_schema_cd => X_GRADING_SCHEMA_CD ,
1047 x_gs_version_number => X_GS_VERSION_NUMBER ,
1048 x_creation_date => X_LAST_UPDATE_DATE ,
1049 x_created_by => X_LAST_UPDATED_BY ,
1050 x_last_update_date => X_LAST_UPDATE_DATE ,
1051 x_last_updated_by => X_LAST_UPDATED_BY ,
1052 x_last_update_login => X_LAST_UPDATE_LOGIN
1053 );
1054
1055 insert into IGS_PS_OFR_PAT (
1056 COURSE_CD,
1057 VERSION_NUMBER,
1058 CAL_TYPE,
1059 CI_SEQUENCE_NUMBER,
1060 LOCATION_CD,
1061 ATTENDANCE_MODE,
1062 ATTENDANCE_TYPE,
1063 COP_ID,
1064 COO_ID,
1065 OFFERED_IND,
1066 CONFIRMED_OFFERING_IND,
1067 ENTRY_POINT_IND,
1068 PRE_ENROL_UNITS_IND,
1069 ENROLLABLE_IND,
1070 IVRS_AVAILABLE_IND,
1071 MIN_ENTRY_ASS_SCORE,
1072 GUARANTEED_ENTRY_ASS_SCR,
1073 MAX_CROSS_FACULTY_CP,
1074 MAX_CROSS_LOCATION_CP,
1075 MAX_CROSS_MODE_CP,
1076 MAX_HIST_CROSS_FACULTY_CP,
1077 ADM_ASS_OFFICER_PERSON_ID,
1078 ADM_CONTACT_PERSON_ID,
1079 GRADING_SCHEMA_CD,
1080 GS_VERSION_NUMBER,
1081 CREATION_DATE,
1082 CREATED_BY,
1083 LAST_UPDATE_DATE,
1084 LAST_UPDATED_BY,
1085 LAST_UPDATE_LOGIN,
1086 REQUEST_ID,
1087 PROGRAM_ID,
1088 PROGRAM_APPLICATION_ID,
1089 PROGRAM_UPDATE_DATE
1090 ) values (
1091 NEW_REFERENCES.COURSE_CD,
1092 NEW_REFERENCES.VERSION_NUMBER,
1093 NEW_REFERENCES.CAL_TYPE,
1094 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
1095 NEW_REFERENCES.LOCATION_CD,
1096 NEW_REFERENCES.ATTENDANCE_MODE,
1097 NEW_REFERENCES.ATTENDANCE_TYPE,
1098 NEW_REFERENCES.COP_ID,
1099 NEW_REFERENCES.COO_ID,
1100 NEW_REFERENCES.OFFERED_IND,
1101 NEW_REFERENCES.CONFIRMED_OFFERING_IND,
1102 NEW_REFERENCES.ENTRY_POINT_IND,
1103 NEW_REFERENCES.PRE_ENROL_UNITS_IND,
1104 NEW_REFERENCES.ENROLLABLE_IND,
1105 NEW_REFERENCES.IVRS_AVAILABLE_IND,
1106 NEW_REFERENCES.MIN_ENTRY_ASS_SCORE,
1107 NEW_REFERENCES.GUARANTEED_ENTRY_ASS_SCR,
1108 NEW_REFERENCES.MAX_CROSS_FACULTY_CP,
1109 NEW_REFERENCES.MAX_CROSS_LOCATION_CP,
1110 NEW_REFERENCES.MAX_CROSS_MODE_CP,
1111 NEW_REFERENCES.MAX_HIST_CROSS_FACULTY_CP,
1112 NEW_REFERENCES.ADM_ASS_OFFICER_PERSON_ID,
1113 NEW_REFERENCES.ADM_CONTACT_PERSON_ID,
1114 NEW_REFERENCES.GRADING_SCHEMA_CD,
1115 NEW_REFERENCES.GS_VERSION_NUMBER,
1116 X_LAST_UPDATE_DATE,
1117 X_LAST_UPDATED_BY,
1118 X_LAST_UPDATE_DATE,
1119 X_LAST_UPDATED_BY,
1120 X_LAST_UPDATE_LOGIN,
1121 X_REQUEST_ID,
1122 X_PROGRAM_ID,
1123 X_PROGRAM_APPLICATION_ID,
1124 X_PROGRAM_UPDATE_DATE
1125 );
1126
1127 open c;
1128 fetch c into X_ROWID;
1129 if (c%notfound) then
1130 close c;
1131 raise no_data_found;
1132 end if;
1133 close c;
1134 After_DML (
1135 p_action => 'INSERT',
1136 x_rowid => X_ROWID
1137 );
1138 end INSERT_ROW;
1139
1140 procedure LOCK_ROW (
1141 X_ROWID IN VARCHAR2,
1142 X_COURSE_CD in VARCHAR2,
1143 X_CI_SEQUENCE_NUMBER in NUMBER,
1144 X_CAL_TYPE in VARCHAR2,
1145 X_VERSION_NUMBER in NUMBER,
1146 X_LOCATION_CD in VARCHAR2,
1147 X_ATTENDANCE_TYPE in VARCHAR2,
1148 X_ATTENDANCE_MODE in VARCHAR2,
1149 X_COP_ID in NUMBER,
1150 X_COO_ID in NUMBER,
1151 X_OFFERED_IND in VARCHAR2,
1152 X_CONFIRMED_OFFERING_IND in VARCHAR2,
1153 X_ENTRY_POINT_IND in VARCHAR2,
1154 X_PRE_ENROL_UNITS_IND in VARCHAR2,
1155 X_ENROLLABLE_IND in VARCHAR2,
1156 X_IVRS_AVAILABLE_IND in VARCHAR2,
1157 X_MIN_ENTRY_ASS_SCORE in NUMBER,
1158 X_GUARANTEED_ENTRY_ASS_SCR in NUMBER,
1159 X_MAX_CROSS_FACULTY_CP in NUMBER,
1160 X_MAX_CROSS_LOCATION_CP in NUMBER,
1161 X_MAX_CROSS_MODE_CP in NUMBER,
1162 X_MAX_HIST_CROSS_FACULTY_CP in NUMBER,
1163 X_ADM_ASS_OFFICER_PERSON_ID in NUMBER,
1164 X_ADM_CONTACT_PERSON_ID in NUMBER,
1165 X_GRADING_SCHEMA_CD in VARCHAR2,
1166 X_GS_VERSION_NUMBER in NUMBER
1167 ) AS
1168 cursor c1 is select
1169 COP_ID,
1170 COO_ID,
1171 OFFERED_IND,
1172 CONFIRMED_OFFERING_IND,
1173 ENTRY_POINT_IND,
1174 PRE_ENROL_UNITS_IND,
1175 ENROLLABLE_IND,
1176 IVRS_AVAILABLE_IND,
1177 MIN_ENTRY_ASS_SCORE,
1178 GUARANTEED_ENTRY_ASS_SCR,
1179 MAX_CROSS_FACULTY_CP,
1180 MAX_CROSS_LOCATION_CP,
1181 MAX_CROSS_MODE_CP,
1182 MAX_HIST_CROSS_FACULTY_CP,
1183 ADM_ASS_OFFICER_PERSON_ID,
1184 ADM_CONTACT_PERSON_ID,
1185 GRADING_SCHEMA_CD,
1186 GS_VERSION_NUMBER
1187 from IGS_PS_OFR_PAT
1188 where ROWID = X_ROWID
1189 for update nowait;
1190 tlinfo c1%rowtype;
1191
1192 begin
1193 open c1;
1194 fetch c1 into tlinfo;
1195 if (c1%notfound) then
1196 close c1;
1197 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1198 IGS_GE_MSG_STACK.ADD;
1199 app_exception.raise_exception;
1200 return;
1201 end if;
1202 close c1;
1203
1204 if ( (tlinfo.COP_ID = X_COP_ID)
1205 AND (tlinfo.COO_ID = X_COO_ID)
1206 AND (tlinfo.OFFERED_IND = X_OFFERED_IND)
1207 AND (tlinfo.CONFIRMED_OFFERING_IND = X_CONFIRMED_OFFERING_IND)
1208 AND (tlinfo.ENTRY_POINT_IND = X_ENTRY_POINT_IND)
1209 AND (tlinfo.PRE_ENROL_UNITS_IND = X_PRE_ENROL_UNITS_IND)
1210 AND (tlinfo.ENROLLABLE_IND = X_ENROLLABLE_IND)
1211 AND (tlinfo.IVRS_AVAILABLE_IND = X_IVRS_AVAILABLE_IND)
1212 AND ((tlinfo.MIN_ENTRY_ASS_SCORE = X_MIN_ENTRY_ASS_SCORE)
1213 OR ((tlinfo.MIN_ENTRY_ASS_SCORE is null)
1214 AND (X_MIN_ENTRY_ASS_SCORE is null)))
1215 AND ((tlinfo.GUARANTEED_ENTRY_ASS_SCR = X_GUARANTEED_ENTRY_ASS_SCR)
1216 OR ((tlinfo.GUARANTEED_ENTRY_ASS_SCR is null)
1217 AND (X_GUARANTEED_ENTRY_ASS_SCR is null)))
1218 AND ((tlinfo.MAX_CROSS_FACULTY_CP = X_MAX_CROSS_FACULTY_CP)
1219 OR ((tlinfo.MAX_CROSS_FACULTY_CP is null)
1220 AND (X_MAX_CROSS_FACULTY_CP is null)))
1221 AND ((tlinfo.MAX_CROSS_LOCATION_CP = X_MAX_CROSS_LOCATION_CP)
1222 OR ((tlinfo.MAX_CROSS_LOCATION_CP is null)
1223 AND (X_MAX_CROSS_LOCATION_CP is null)))
1224 AND ((tlinfo.MAX_CROSS_MODE_CP = X_MAX_CROSS_MODE_CP)
1225 OR ((tlinfo.MAX_CROSS_MODE_CP is null)
1226 AND (X_MAX_CROSS_MODE_CP is null)))
1227 AND ((tlinfo.MAX_HIST_CROSS_FACULTY_CP = X_MAX_HIST_CROSS_FACULTY_CP)
1228 OR ((tlinfo.MAX_HIST_CROSS_FACULTY_CP is null)
1229 AND (X_MAX_HIST_CROSS_FACULTY_CP is null)))
1230 AND ((tlinfo.ADM_ASS_OFFICER_PERSON_ID = X_ADM_ASS_OFFICER_PERSON_ID)
1231 OR ((tlinfo.ADM_ASS_OFFICER_PERSON_ID is null)
1232 AND (X_ADM_ASS_OFFICER_PERSON_ID is null)))
1233 AND ((tlinfo.ADM_CONTACT_PERSON_ID = X_ADM_CONTACT_PERSON_ID)
1234 OR ((tlinfo.ADM_CONTACT_PERSON_ID is null)
1235 AND (X_ADM_CONTACT_PERSON_ID is null)))
1236 AND ((tlinfo.GRADING_SCHEMA_CD = X_GRADING_SCHEMA_CD)
1237 OR ((tlinfo.GRADING_SCHEMA_CD is null)
1238 AND (X_GRADING_SCHEMA_CD is null)))
1239 AND ((tlinfo.GS_VERSION_NUMBER = X_GS_VERSION_NUMBER)
1240 OR ((tlinfo.GS_VERSION_NUMBER is null)
1241 AND (X_GS_VERSION_NUMBER is null)))
1242 ) then
1243 null;
1244 else
1245 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1246 IGS_GE_MSG_STACK.ADD;
1247 app_exception.raise_exception;
1248 end if;
1249 return;
1250 end LOCK_ROW;
1251
1252 procedure UPDATE_ROW (
1253 X_ROWID IN VARCHAR2,
1254 X_COURSE_CD in VARCHAR2,
1255 X_CI_SEQUENCE_NUMBER in NUMBER,
1256 X_CAL_TYPE in VARCHAR2,
1257 X_VERSION_NUMBER in NUMBER,
1258 X_LOCATION_CD in VARCHAR2,
1259 X_ATTENDANCE_TYPE in VARCHAR2,
1260 X_ATTENDANCE_MODE in VARCHAR2,
1261 X_COP_ID in NUMBER,
1262 X_COO_ID in NUMBER,
1263 X_OFFERED_IND in VARCHAR2,
1264 X_CONFIRMED_OFFERING_IND in VARCHAR2,
1265 X_ENTRY_POINT_IND in VARCHAR2,
1266 X_PRE_ENROL_UNITS_IND in VARCHAR2,
1267 X_ENROLLABLE_IND in VARCHAR2,
1268 X_IVRS_AVAILABLE_IND in VARCHAR2,
1269 X_MIN_ENTRY_ASS_SCORE in NUMBER,
1270 X_GUARANTEED_ENTRY_ASS_SCR in NUMBER,
1271 X_MAX_CROSS_FACULTY_CP in NUMBER,
1272 X_MAX_CROSS_LOCATION_CP in NUMBER,
1273 X_MAX_CROSS_MODE_CP in NUMBER,
1274 X_MAX_HIST_CROSS_FACULTY_CP in NUMBER,
1275 X_ADM_ASS_OFFICER_PERSON_ID in NUMBER,
1276 X_ADM_CONTACT_PERSON_ID in NUMBER,
1277 X_GRADING_SCHEMA_CD in VARCHAR2,
1278 X_GS_VERSION_NUMBER in NUMBER,
1279 X_MODE in VARCHAR2 default 'R'
1280 ) AS
1281 X_LAST_UPDATE_DATE DATE;
1282 X_LAST_UPDATED_BY NUMBER;
1283 X_LAST_UPDATE_LOGIN NUMBER;
1284 X_REQUEST_ID NUMBER;
1285 X_PROGRAM_ID NUMBER;
1286 X_PROGRAM_APPLICATION_ID NUMBER;
1287 X_PROGRAM_UPDATE_DATE DATE;
1288 begin
1289 X_LAST_UPDATE_DATE := SYSDATE;
1290 if(X_MODE = 'I') then
1291 X_LAST_UPDATED_BY := 1;
1292 X_LAST_UPDATE_LOGIN := 0;
1293 elsif (X_MODE = 'R') then
1294 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1295 if X_LAST_UPDATED_BY is NULL then
1296 X_LAST_UPDATED_BY := -1;
1297 end if;
1298 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1299 if X_LAST_UPDATE_LOGIN is NULL then
1300 X_LAST_UPDATE_LOGIN := -1;
1301 end if;
1302 else
1303 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1304 IGS_GE_MSG_STACK.ADD;
1305 app_exception.raise_exception;
1306 end if;
1307
1308 Before_DML (
1309 p_action => 'UPDATE',
1310 x_rowid => X_ROWID,
1311 x_course_cd => X_COURSE_CD,
1312 x_version_number => X_VERSION_NUMBER,
1313 x_cal_type => X_CAL_TYPE,
1314 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
1315 x_location_cd => X_LOCATION_CD,
1316 x_attendance_mode => X_ATTENDANCE_MODE,
1317 x_attendance_type => X_ATTENDANCE_TYPE,
1318 x_cop_id => X_COP_ID,
1319 x_coo_id => X_COO_ID,
1320 x_offered_ind => X_OFFERED_IND,
1321 x_confirmed_offering_ind => X_CONFIRMED_OFFERING_IND ,
1322 x_entry_point_ind => X_ENTRY_POINT_IND ,
1323 x_pre_enrol_units_ind => X_PRE_ENROL_UNITS_IND ,
1324 x_enrollable_ind => X_ENROLLABLE_IND ,
1325 x_ivrs_available_ind => X_IVRS_AVAILABLE_IND ,
1326 x_min_entry_ass_score => X_MIN_ENTRY_ASS_SCORE ,
1327 x_guaranteed_entry_ass_scr => X_GUARANTEED_ENTRY_ASS_SCR ,
1328 x_max_cross_faculty_cp => X_MAX_CROSS_FACULTY_CP ,
1329 x_max_cross_location_cp => X_MAX_CROSS_LOCATION_CP ,
1330 x_max_cross_mode_cp => X_MAX_CROSS_MODE_CP ,
1331 x_max_hist_cross_faculty_cp => X_MAX_HIST_CROSS_FACULTY_CP ,
1332 x_adm_ass_officer_person_id => X_ADM_ASS_OFFICER_PERSON_ID ,
1333 x_adm_contact_person_id => X_ADM_CONTACT_PERSON_ID ,
1334 x_grading_schema_cd => X_GRADING_SCHEMA_CD ,
1335 x_gs_version_number => X_GS_VERSION_NUMBER ,
1336 x_creation_date => X_LAST_UPDATE_DATE ,
1337 x_created_by => X_LAST_UPDATED_BY ,
1338 x_last_update_date => X_LAST_UPDATE_DATE ,
1339 x_last_updated_by => X_LAST_UPDATED_BY ,
1340 x_last_update_login => X_LAST_UPDATE_LOGIN
1341 );
1342 if (X_MODE = 'R') then
1343 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1344 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1345 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1346 if (X_REQUEST_ID = -1) then
1347 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1348 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1349 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1350 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1351 else
1352 X_PROGRAM_UPDATE_DATE := SYSDATE;
1353 end if;
1354 end if;
1355
1356
1357 update IGS_PS_OFR_PAT set
1358 COP_ID = NEW_REFERENCES.COP_ID,
1359 COO_ID = NEW_REFERENCES.COO_ID,
1360 OFFERED_IND = NEW_REFERENCES.OFFERED_IND,
1361 CONFIRMED_OFFERING_IND = NEW_REFERENCES.CONFIRMED_OFFERING_IND,
1362 ENTRY_POINT_IND = NEW_REFERENCES.ENTRY_POINT_IND,
1363 PRE_ENROL_UNITS_IND = NEW_REFERENCES.PRE_ENROL_UNITS_IND,
1364 ENROLLABLE_IND = NEW_REFERENCES.ENROLLABLE_IND,
1365 IVRS_AVAILABLE_IND = NEW_REFERENCES.IVRS_AVAILABLE_IND,
1366 MIN_ENTRY_ASS_SCORE = NEW_REFERENCES.MIN_ENTRY_ASS_SCORE,
1367 GUARANTEED_ENTRY_ASS_SCR = NEW_REFERENCES.GUARANTEED_ENTRY_ASS_SCR,
1368 MAX_CROSS_FACULTY_CP = NEW_REFERENCES.MAX_CROSS_FACULTY_CP,
1369 MAX_CROSS_LOCATION_CP = NEW_REFERENCES.MAX_CROSS_LOCATION_CP,
1370 MAX_CROSS_MODE_CP = NEW_REFERENCES.MAX_CROSS_MODE_CP,
1371 MAX_HIST_CROSS_FACULTY_CP = NEW_REFERENCES.MAX_HIST_CROSS_FACULTY_CP,
1372 ADM_ASS_OFFICER_PERSON_ID = NEW_REFERENCES.ADM_ASS_OFFICER_PERSON_ID,
1373 ADM_CONTACT_PERSON_ID = NEW_REFERENCES.ADM_CONTACT_PERSON_ID,
1374 GRADING_SCHEMA_CD = NEW_REFERENCES.GRADING_SCHEMA_CD,
1375 GS_VERSION_NUMBER = NEW_REFERENCES.GS_VERSION_NUMBER,
1376 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1377 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1378 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1379 REQUEST_ID = X_REQUEST_ID,
1380 PROGRAM_ID = X_PROGRAM_ID,
1381 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1382 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1383 where ROWID = X_ROWID
1384 ;
1385 if (sql%notfound) then
1386 raise no_data_found;
1387 end if;
1388 After_DML (
1389 p_action => 'UPDATE',
1390 x_rowid => X_ROWID
1391 );
1392 end UPDATE_ROW;
1393
1394 procedure ADD_ROW (
1395 X_ROWID in out NOCOPY VARCHAR2,
1396 X_COURSE_CD in VARCHAR2,
1397 X_CI_SEQUENCE_NUMBER in NUMBER,
1398 X_CAL_TYPE in VARCHAR2,
1399 X_VERSION_NUMBER in NUMBER,
1400 X_LOCATION_CD in VARCHAR2,
1401 X_ATTENDANCE_TYPE in VARCHAR2,
1402 X_ATTENDANCE_MODE in VARCHAR2,
1403 X_COP_ID in NUMBER,
1404 X_COO_ID in NUMBER,
1405 X_OFFERED_IND in VARCHAR2,
1406 X_CONFIRMED_OFFERING_IND in VARCHAR2,
1407 X_ENTRY_POINT_IND in VARCHAR2,
1408 X_PRE_ENROL_UNITS_IND in VARCHAR2,
1409 X_ENROLLABLE_IND in VARCHAR2,
1410 X_IVRS_AVAILABLE_IND in VARCHAR2,
1411 X_MIN_ENTRY_ASS_SCORE in NUMBER,
1412 X_GUARANTEED_ENTRY_ASS_SCR in NUMBER,
1413 X_MAX_CROSS_FACULTY_CP in NUMBER,
1414 X_MAX_CROSS_LOCATION_CP in NUMBER,
1415 X_MAX_CROSS_MODE_CP in NUMBER,
1416 X_MAX_HIST_CROSS_FACULTY_CP in NUMBER,
1417 X_ADM_ASS_OFFICER_PERSON_ID in NUMBER,
1418 X_ADM_CONTACT_PERSON_ID in NUMBER,
1419 X_GRADING_SCHEMA_CD in VARCHAR2,
1420 X_GS_VERSION_NUMBER in NUMBER,
1421 X_MODE in VARCHAR2 default 'R'
1422 ) AS
1423 cursor c1 is select rowid from IGS_PS_OFR_PAT
1424 where COURSE_CD = X_COURSE_CD
1425 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
1426 and CAL_TYPE = X_CAL_TYPE
1427 and VERSION_NUMBER = X_VERSION_NUMBER
1428 and LOCATION_CD = X_LOCATION_CD
1429 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
1430 and ATTENDANCE_MODE = X_ATTENDANCE_MODE
1431 ;
1432 begin
1433 open c1;
1434 fetch c1 into X_ROWID;
1435 if (c1%notfound) then
1436 close c1;
1437 INSERT_ROW (
1438 X_ROWID,
1439 X_COURSE_CD,
1440 X_CI_SEQUENCE_NUMBER,
1441 X_CAL_TYPE,
1442 X_VERSION_NUMBER,
1443 X_LOCATION_CD,
1444 X_ATTENDANCE_TYPE,
1445 X_ATTENDANCE_MODE,
1446 X_COP_ID,
1447 X_COO_ID,
1448 X_OFFERED_IND,
1449 X_CONFIRMED_OFFERING_IND,
1450 X_ENTRY_POINT_IND,
1451 X_PRE_ENROL_UNITS_IND,
1452 X_ENROLLABLE_IND,
1453 X_IVRS_AVAILABLE_IND,
1454 X_MIN_ENTRY_ASS_SCORE,
1455 X_GUARANTEED_ENTRY_ASS_SCR,
1456 X_MAX_CROSS_FACULTY_CP,
1457 X_MAX_CROSS_LOCATION_CP,
1458 X_MAX_CROSS_MODE_CP,
1459 X_MAX_HIST_CROSS_FACULTY_CP,
1460 X_ADM_ASS_OFFICER_PERSON_ID,
1461 X_ADM_CONTACT_PERSON_ID,
1462 X_GRADING_SCHEMA_CD,
1463 X_GS_VERSION_NUMBER,
1464 X_MODE);
1465 return;
1466 end if;
1467 close c1;
1468 UPDATE_ROW (
1469 X_ROWID,
1470 X_COURSE_CD,
1471 X_CI_SEQUENCE_NUMBER,
1472 X_CAL_TYPE,
1473 X_VERSION_NUMBER,
1474 X_LOCATION_CD,
1475 X_ATTENDANCE_TYPE,
1476 X_ATTENDANCE_MODE,
1477 X_COP_ID,
1478 X_COO_ID,
1479 X_OFFERED_IND,
1480 X_CONFIRMED_OFFERING_IND,
1481 X_ENTRY_POINT_IND,
1482 X_PRE_ENROL_UNITS_IND,
1483 X_ENROLLABLE_IND,
1484 X_IVRS_AVAILABLE_IND,
1485 X_MIN_ENTRY_ASS_SCORE,
1486 X_GUARANTEED_ENTRY_ASS_SCR,
1487 X_MAX_CROSS_FACULTY_CP,
1488 X_MAX_CROSS_LOCATION_CP,
1489 X_MAX_CROSS_MODE_CP,
1490 X_MAX_HIST_CROSS_FACULTY_CP,
1491 X_ADM_ASS_OFFICER_PERSON_ID,
1492 X_ADM_CONTACT_PERSON_ID,
1493 X_GRADING_SCHEMA_CD,
1494 X_GS_VERSION_NUMBER,
1495 X_MODE);
1496 end ADD_ROW;
1497
1498 procedure DELETE_ROW (
1499 X_ROWID in VARCHAR2
1500 ) AS
1501 begin
1502 Before_DML (
1503 p_action => 'DELETE',
1504 x_rowid => X_ROWID
1505 );
1506 delete from IGS_PS_OFR_PAT
1507 where ROWID = X_ROWID;
1508 if (sql%notfound) then
1509 raise no_data_found;
1510 end if;
1511 After_DML (
1512 p_action => 'DELETE',
1513 x_rowid => X_ROWID
1514 );
1515 end DELETE_ROW;
1516
1517 end IGS_PS_OFR_PAT_PKG;