[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_VER_PKG
Source
1 package body IGS_PS_UNIT_VER_PKG as
2 /* $Header: IGSPI92B.pls 120.6 2006/05/15 00:37:51 sarakshi ship $ */
3 -- Change History
4 -- Who When What
5 --sommukhe 28-Nov-2005 Bug#4760997, created a new procedure update_row_subtitle_id.
6 --sommukhe 02-sep-2005 bug#4257131, added code in after_dml for update operation.
7 --sarakshi 10-Aug-2005 Bug#4216517, removed get_fk_igs_ps_unit_type_lvl procedure
8 --jbaber 02-feb-05 Modified procedure Check_Child_Existance, to remove reference to obsoleted igs_he_st_uv_cc_all_pkg package for HE355 - Org Unit Cost Centre Link
9 --sarakshi 30-Apr-2004 Bug#3568858, Added columns ovrd_wkld_val_flag, workload_val_code
10 --sarakshi 01-sep-03 Enh#3052452,removed the reference of sub_unit_allowed_ind and sup_unit_allowed_ind
11 --sarakshi 04-Aug-03 Bug#3045069,modified check_constraint procedure
12 --smvk 27-Jun-2003 Bug # 3011578. Truncating start_date, end_date, expiry_date, approval_date and review date.
13 --sarakshi 09-Jun-2003 Enh#2858436,removed procedure get_fk_igs_ps_unit_level
14 --vvutukur 09-Jun-2003 Enh#2831572.Financial Accounting Build.Modified check_child_existance.
15 --svenkata 02-06-2003 Modified to remove references to TBH of pkg IGS_EN_ELGB_OVR_STEP_PKG. Instead , added
16 -- references to package IGS_EN_ELGB_OVR_UOO.Bug #2829272
17 --sarakshi 18-Apr-2003 Enh#2858431,modified procedure DFLT_UNIT_REF_CODE
18 -- pathipat 17-Feb-2003 Enh 2747325 - Locking Issues build - Removed proc get_fk_igs_fi_acc_all
19 -- sarakshi 27-Nov-2002 Enh#2649028,for all numeric value comparision, changed the AND condition with OR
20 -- to check the value between 0 and 999
21 --shtatiko 31-OCT-2002 Bug# 2636716. Modified procedures to incorporate addition of three new fields
22 -- auditable_ind, audit_permission_ind and max_auditors_allowed.
23 --sarakshi 16-Jun-2002 bug#2416978,added local procedure beforerowdelete,beforerowupdate and modified
24 -- get_pk_for_validation
25 --jbegum 17 April 02 As part of bug fix of bug #2322290 and bug#2250784
26 -- Removed the following 4 columns
27 -- BILLING_CREDIT_POINTS,BILLING_HRS,FIN_AID_CP,FIN_AID_HRS.
28 -- sbaliga 13-feb-2002 Modified call to before_dml in insert_row procedure as part of SWCR006 build.
29 -- SMADATHI 01-Feb-2002 Modified Check_Child_Existance Procedure . This is as per enhancement bug 2154941
30 -- jdeekoll 11-SEP-2001 Added one column claimable_hours
31 -- msrinivi 20-Jul-2001 Bug # 1882122 Added one column rev_account_cd
32 -- rgangara 09-Jul-2001 Added the Default 'N' to fields ss_enrol_ind and ivr_enrol_ind
33 -- SMADATHI 03-JUL-2001 Modified Check_Child_Existance Procedure . This is as per enhancement bug no. 1830175
34 -- SMADATHI 13-JUN-2001 Modified set column values procedure . Subtitle column in igs_ps_unit_ver_all was obsoleted and irrespective
35 -- of the values assigned to it , the column will be assigned value NULL . Also reference to this column
36 -- was removed from lock_row procedure .This is as per Enhancement Bug No. 1775394
37 /* SMADATHI 29-MAY-2001 removed foreign key references to IGS_PS_UNT_REPT_FMLY , IGS_PS_UNT_PRV_GRADE as per DLD. (Enhancement Bug No. 1775394)
38 SMADATHI 25-MAY-2001 removed foreign key references to IGS_PS_USEC_RPT_FMLY as per DLD (Enhancement Bug No. 1775394). Also added validation for Curriculum Id*/
39 -- rgangara 03-May-2001 added two Columns ss_enrol_ind and ivr_enrol_ind as per DLD Unit Section Enrollment Info.
40 -- cdcruz 31-Jan-2002 added Two new columns anon_unit_grading_ind/anon_assess_grading_ind added to base table
41 -- as per DLD Anonymous Grading Bug 2198374
42 -- smvk 16-Dec-2002 Function Call IGS_PS_VAL_US.crsp_val_ver_dt,IGS_PS_VAL_UV.crsp_val_uv_pnt_ovrd and IGS_PS_VAL_UV.crsp_val_uv_unit_sts
43 -- are modified with additional parameter value 'FALSE'. for Bug # 2696207
44 -- smvk 19-Dec-2002 Removed the OWNER_ORG_UNIT_CD checking for upper case
45 -- from check_constraints procedure. Bug # 2487149
46 -- smaddali 21-jan-04 Modified procedure Check_Child_Existance , to remove cursor c_hesa and call igs_he_st_unt_vs_all_pkg.get_fk_igs_ps_unit_ver_all
47 -- and igs_he_st_uv_cc_all_pkg.get_fk_igs_ps_unit_ver_all directly instead of thru execute immediate for bug#3306063
48
49 l_rowid VARCHAR2(25);
50 old_references IGS_PS_UNIT_VER_ALL%RowType;
51 new_references IGS_PS_UNIT_VER_ALL%RowType;
52
53
54 PROCEDURE beforerowdelete AS
55 ------------------------------------------------------------------
56 --Created by : sarakshi, Oracle India
57 --Date created: 16-Jun-2002
58 --
59 --Purpose: Only planned unit status are allowed for deletion
60 --
61 --
62 --Known limitations/enhancements and/or remarks:
63 --
64 --Change History:
65 --Who When What
66 -------------------------------------------------------------------
67 CURSOR cur_delete (cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
68 cp_version_number igs_ps_unit_ver_all.version_number%TYPE)
69 IS
70 SELECT 'x'
71 FROM igs_ps_unit_ver_all uv,
72 igs_ps_unit_stat us
73 WHERE uv.unit_status=us.unit_status
74 AND us.s_unit_status='PLANNED'
75 AND uv.unit_cd = cp_unit_cd
76 AND uv.version_number = cp_version_number;
77
78 l_check VARCHAR2(1);
79
80 BEGIN
81 -- Only planned unit status are allowed for deletion
82 OPEN cur_delete (old_references.unit_cd,old_references.version_number);
83 FETCH cur_delete INTO l_check;
84 IF cur_delete%NOTFOUND THEN
85 CLOSE cur_delete;
86 fnd_message.set_name('IGS','IGS_PS_UNIT_NO_DEL_ALLOWED');
87 igs_ge_msg_stack.add;
88 app_exception.raise_exception;
89 END IF;
90 CLOSE cur_delete;
91 END beforerowdelete;
92
93
94 PROCEDURE beforerowupdate AS
95 ------------------------------------------------------------------
96 --Created by : sarakshi, Oracle India
97 --Date created: 16-Jun-2002
98 --
99 --Purpose: Active/Inactive unit Status can not be changed to Planned Status
100 --
101 --
102 --Known limitations/enhancements and/or remarks:
103 --
104 --Change History:
105 --Who When What
106 --sarakshi 26-Jul-2004 bug#3793607,added code such that statsu cannot be changed to INACTIVE when enrolled/waitlisted student unit attempt exists
107 -------------------------------------------------------------------
108 CURSOR cur_get_status (cp_unit_status igs_ps_unit_stat.unit_status%TYPE)
109 IS
110 SELECT s_unit_status
111 FROM igs_ps_unit_stat
112 WHERE unit_status = cp_unit_status;
113 l_s_unit_status igs_ps_unit_stat.s_unit_status%TYPE;
114
115 CURSOR cur_check_update (cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
116 cp_version_number igs_ps_unit_ver_all.version_number%TYPE)
117 IS
118 SELECT 'x'
119 FROM igs_ps_unit_ver_all uv,
120 igs_ps_unit_stat us
121 WHERE uv.unit_status=us.unit_status
122 AND us.s_unit_status <> 'PLANNED'
123 AND uv.unit_cd = cp_unit_cd
124 AND uv.version_number = cp_version_number;
125
126 l_check VARCHAR2(1);
127
128 CURSOR c_enrollment_status (cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
129 cp_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
130 SELECT 'X'
131 FROM igs_en_su_attempt_all a,
132 igs_ps_unit_ofr_opt_all b
133 WHERE a.uoo_id = b.uoo_id
134 AND b.unit_cd = cp_unit_cd
135 AND b.version_number = cp_version_number
136 AND a.unit_attempt_status IN ('ENROLLED','WAITLISTED');
137 l_c_var VARCHAR2(1);
138
139 BEGIN
140 -- Active/Inactive unit Status can not be changed to Planned Status
141 OPEN cur_get_status(new_references.unit_status);
142 FETCH cur_get_status INTO l_s_unit_status;
143 IF cur_get_status%FOUND THEN
144 CLOSE cur_get_status;
145 IF (l_s_unit_status = 'PLANNED') THEN
146 OPEN cur_check_update(old_references.unit_cd,old_references.version_number);
147 FETCH cur_check_update INTO l_check;
148 IF cur_check_update%FOUND THEN
149 CLOSE cur_check_update;
150 fnd_message.set_name('IGS','IGS_PS_UNIT_NO_INACTIVE_PLN');
151 igs_ge_msg_stack.add;
152 app_exception.raise_exception;
153 END IF;
154 CLOSE cur_check_update;
155 END IF;
156 ELSE
157 -- If the unit status is not found then the record might have been deleted
158 CLOSE cur_get_status;
159 fnd_message.set_name('FND','FORM_RECORD_DELETED');
160 igs_ge_msg_stack.add;
161 app_exception.raise_exception;
162 END IF;
163
164
165 --Unit status cannot be made INACTIVE when there are Enrolled or waitlisted student unit attempt
166 IF new_references.unit_status <> old_references.unit_status AND l_s_unit_status = 'INACTIVE' THEN
167 OPEN c_enrollment_status (new_references.unit_cd,new_references.version_number);
168 FETCH c_enrollment_status INTO l_c_var;
169 IF c_enrollment_status%FOUND THEN
170 CLOSE c_enrollment_status;
171 fnd_message.set_name('IGS','IGS_PS_UNIT_STATUS_INACTIVE_NO');
172 igs_ge_msg_stack.add;
173 app_exception.raise_exception;
174 END IF;
175 CLOSE c_enrollment_status;
176 END IF;
177
178 END beforerowupdate;
179
180
181 PROCEDURE Set_Column_Values (
182 p_action IN VARCHAR2,
183 x_rowid IN VARCHAR2 ,
184 x_unit_cd in VARCHAR2 ,
185 x_version_number in NUMBER ,
186 x_start_dt in DATE ,
187 x_review_dt in DATE ,
188 x_expiry_dt in DATE ,
189 x_end_dt in DATE ,
190 x_unit_status in VARCHAR2 ,
191 x_title in VARCHAR2 ,
192 x_short_title in VARCHAR2 ,
193 x_title_override_ind in VARCHAR2 ,
194 x_abbreviation in VARCHAR2 ,
195 x_unit_level in VARCHAR2 ,
196 x_credit_point_descriptor in VARCHAR2 ,
197 x_enrolled_credit_points in NUMBER ,
198 x_points_override_ind in VARCHAR2 ,
199 x_supp_exam_permitted_ind in VARCHAR2 ,
200 x_coord_person_id in NUMBER ,
201 x_owner_org_unit_cd in VARCHAR2 ,
202 x_owner_ou_start_dt in DATE ,
203 x_award_course_only_ind in VARCHAR2 ,
204 x_research_unit_ind in VARCHAR2 ,
205 x_industrial_ind in VARCHAR2 ,
206 x_practical_ind in VARCHAR2 ,
207 x_repeatable_ind in VARCHAR2 ,
208 x_assessable_ind in VARCHAR2 ,
209 x_achievable_credit_points in NUMBER ,
210 x_points_increment in NUMBER ,
211 x_points_min in NUMBER ,
212 x_points_max in NUMBER ,
213 x_unit_int_course_level_cd in VARCHAR2 ,
214 x_subtitle in VARCHAR2 ,
215 x_subtitle_modifiable_flag in VARCHAR2 ,
216 x_approval_date in DATE ,
217 x_lecture_credit_points in NUMBER ,
218 x_lab_credit_points in NUMBER ,
219 x_other_credit_points in NUMBER ,
220 x_clock_hours in NUMBER ,
221 x_work_load_cp_lecture in NUMBER ,
222 x_work_load_cp_lab in NUMBER ,
223 x_continuing_education_units in NUMBER ,
224 x_enrollment_expected in NUMBER ,
225 x_enrollment_minimum in NUMBER ,
226 x_enrollment_maximum in NUMBER ,
227 x_advance_maximum in NUMBER ,
228 x_state_financial_aid in VARCHAR2 ,
229 x_federal_financial_aid in VARCHAR2 ,
230 x_institutional_financial_aid in VARCHAR2 ,
231 x_same_teaching_period in VARCHAR2 ,
232 x_max_repeats_for_credit in NUMBER ,
233 x_max_repeats_for_funding in NUMBER ,
234 x_max_repeat_credit_points in NUMBER ,
235 x_same_teach_period_repeats in NUMBER ,
236 x_same_teach_period_repeats_cp in NUMBER ,
237 x_attribute_category in VARCHAR2 ,
238 x_attribute1 in VARCHAR2 ,
239 x_attribute2 in VARCHAR2 ,
240 x_attribute3 in VARCHAR2 ,
241 x_attribute4 in VARCHAR2 ,
242 x_attribute5 in VARCHAR2 ,
243 x_attribute6 in VARCHAR2 ,
244 x_attribute7 in VARCHAR2 ,
245 x_attribute8 in VARCHAR2 ,
246 x_attribute9 in VARCHAR2 ,
247 x_attribute10 in VARCHAR2 ,
248 x_attribute11 in VARCHAR2 ,
249 x_attribute12 in VARCHAR2 ,
250 x_attribute13 in VARCHAR2 ,
251 x_attribute14 in VARCHAR2 ,
252 x_attribute15 in VARCHAR2 ,
253 x_attribute16 in VARCHAR2 ,
254 x_attribute17 in VARCHAR2 ,
255 x_attribute18 in VARCHAR2 ,
256 x_attribute19 in VARCHAR2 ,
257 x_attribute20 in VARCHAR2 ,
258 x_subtitle_id IN NUMBER ,
259 x_work_load_other IN NUMBER ,
260 x_contact_hrs_lecture IN NUMBER ,
261 x_contact_hrs_lab IN NUMBER ,
262 x_contact_hrs_other IN NUMBER ,
263 x_non_schd_required_hrs IN NUMBER ,
264 x_exclude_from_max_cp_limit IN VARCHAR2 ,
265 x_record_exclusion_flag IN VARCHAR2 ,
266 x_ss_display_ind IN VARCHAR2 ,
267 x_cal_type_enrol_load_cal IN VARCHAR2 ,
268 x_sequence_num_enrol_load_cal IN NUMBER ,
269 x_cal_type_offer_load_cal IN VARCHAR2 ,
270 x_sequence_num_offer_load_cal IN NUMBER ,
271 x_curriculum_id IN VARCHAR2 ,
272 x_override_enrollment_max IN NUMBER ,
273 x_rpt_fmly_id IN NUMBER ,
274 x_unit_type_id IN NUMBER ,
275 x_special_permission_ind IN VARCHAR2 ,
276 x_created_by in NUMBER ,
277 x_creation_date in DATE ,
278 x_last_updated_by in NUMBER ,
279 x_last_update_date in DATE ,
280 x_last_update_login in NUMBER ,
281 x_org_id in NUMBER ,
282 x_ss_enrol_ind in VARCHAR2 ,
283 x_ivr_enrol_ind in VARCHAR2 ,
284 x_rev_account_cd IN VARCHAR2 ,
285 x_claimable_hours IN NUMBER ,
286 x_anon_unit_grading_ind IN VARCHAR2 ,
287 x_anon_assess_grading_ind IN VARCHAR2 ,
288 x_auditable_ind IN VARCHAR2,
289 x_audit_permission_ind IN VARCHAR2 ,
290 x_max_auditors_allowed IN NUMBER ,
291 x_billing_credit_points IN NUMBER,
292 x_ovrd_wkld_val_flag IN VARCHAR2 ,
293 x_workload_val_code IN VARCHAR2 ,
294 x_billing_hrs IN NUMBER
295
296 ) AS
297 ------------------------------------------------------------------
298 --Purpose: As per enhancement bug no.1775394 , the column subtitle in
299 -- igs_ps_unit_ver_all is obsoleted . Irrespective of the value passed
300 -- to this column , it will always be assigned NULL .
301 --
302 --
303 --Known limitations/enhancements and/or remarks:
304 --
305 --Change History:
306 --Who When What
307 --smadathi 13-JUN-2001 refer purpose
308 --msrinivi 20-Jul-2001 Added new col : rev_account_Cd
309 -------------------------------------------------------------------
310 CURSOR cur_old_ref_values IS
311 SELECT *
312 FROM IGS_PS_UNIT_VER_ALL
313 WHERE rowid = x_rowid;
314
315 BEGIN
316
317 l_rowid := x_rowid;
318
319 -- Code for setting the Old and New Reference Values.
320 -- Populate Old Values.
321 Open cur_old_ref_values;
322 Fetch cur_old_ref_values INTO old_references;
323 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
324 Close cur_old_ref_values;
325 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
326 IGS_GE_MSG_STACK.ADD;
327 App_Exception.Raise_Exception;
328 Return;
329 END IF;
330 Close cur_old_ref_values;
331
332 -- Populate New Values.
333 new_references.unit_cd := x_unit_cd;
334 new_references.version_number := x_version_number;
335 new_references.start_dt := x_start_dt;
336 new_references.review_dt := x_review_dt;
337 new_references.expiry_dt := x_expiry_dt;
338 new_references.end_dt := x_end_dt;
339 new_references.unit_status := x_unit_status;
340 new_references.title := x_title;
341 new_references.short_title := x_short_title;
342 new_references.title_override_ind := x_title_override_ind;
343 new_references.abbreviation := x_abbreviation;
344 new_references.unit_level := x_unit_level;
345 new_references.credit_point_descriptor := x_credit_point_descriptor;
346 new_references.enrolled_credit_points := x_enrolled_credit_points;
347 new_references.points_override_ind := x_points_override_ind;
348 new_references.supp_exam_permitted_ind := x_supp_exam_permitted_ind;
349 new_references.coord_person_id := x_coord_person_id;
350 new_references.owner_org_unit_cd := x_owner_org_unit_cd;
351 new_references.owner_ou_start_dt := x_owner_ou_start_dt;
352 new_references.award_course_only_ind := x_award_course_only_ind;
353 new_references.research_unit_ind := x_research_unit_ind;
354 new_references.industrial_ind := x_industrial_ind;
355 new_references.practical_ind := x_practical_ind;
356 new_references.repeatable_ind := x_repeatable_ind;
357 new_references.assessable_ind := x_assessable_ind;
358 new_references.achievable_credit_points := x_achievable_credit_points;
359 new_references.points_increment := x_points_increment;
360 new_references.points_min := x_points_min;
361 new_references.points_max := x_points_max;
362 new_references.unit_int_course_level_cd := x_unit_int_course_level_cd;
363
364 -- subtitle value will be assigned as NULL irrespective of the value passed to it
365 new_references.subtitle := NULL ;
366
367 new_references.subtitle_modifiable_flag := x_subtitle_modifiable_flag;
368 new_references.approval_date := x_approval_date;
369 new_references.lecture_credit_points := x_lecture_credit_points;
370 new_references.lab_credit_points := x_lab_credit_points;
371 new_references.other_credit_points := x_other_credit_points;
372 new_references.clock_hours := x_clock_hours;
373 new_references.work_load_cp_lecture := x_work_load_cp_lecture;
374 new_references.work_load_cp_lab := x_work_load_cp_lab;
375 new_references.continuing_education_units := x_continuing_education_units;
376 new_references.enrollment_expected := x_enrollment_expected;
377 new_references.enrollment_minimum := x_enrollment_minimum;
378 new_references.enrollment_maximum := x_enrollment_maximum;
379 new_references.advance_maximum := x_advance_maximum;
380 new_references.state_financial_aid := x_state_financial_aid;
381 new_references.federal_financial_aid := x_federal_financial_aid;
382 new_references.institutional_financial_aid := x_institutional_financial_aid;
383 new_references.same_teaching_period := x_same_teaching_period;
384 new_references.max_repeats_for_credit := x_max_repeats_for_credit;
385 new_references.max_repeats_for_funding := x_max_repeats_for_funding;
386 new_references.max_repeat_credit_points := x_max_repeat_credit_points;
387 new_references.same_teach_period_repeats := x_same_teach_period_repeats;
388 new_references.same_teach_period_repeats_cp := x_same_teach_period_repeats_cp;
389 new_references.attribute_category := x_attribute_category;
390 new_references.attribute1 := x_attribute1;
391 new_references.attribute2 := x_attribute2;
392 new_references.attribute3 := x_attribute3;
393 new_references.attribute4 := x_attribute4;
394 new_references.attribute5 := x_attribute5;
395 new_references.attribute6 := x_attribute6;
396 new_references.attribute7 := x_attribute7;
397 new_references.attribute8 := x_attribute8;
398 new_references.attribute9 := x_attribute9;
399 new_references.attribute10 := x_attribute10;
400 new_references.attribute11 := x_attribute11;
401 new_references.attribute12 := x_attribute12;
402 new_references.attribute13 := x_attribute13;
403 new_references.attribute14 := x_attribute14;
404 new_references.attribute15 := x_attribute15;
405 new_references.attribute16 := x_attribute16;
406 new_references.attribute17 := x_attribute17;
407 new_references.attribute18 := x_attribute18;
408 new_references.attribute19 := x_attribute19;
409 new_references.attribute20 := x_attribute20;
410 new_references.subtitle_id := x_subtitle_id;
411 new_references.work_load_other := x_work_load_other;
412 new_references.contact_hrs_lecture := x_contact_hrs_lecture;
413 new_references.contact_hrs_lab := x_contact_hrs_lab;
414 new_references.contact_hrs_other := x_contact_hrs_other;
415 new_references.non_schd_required_hrs := x_non_schd_required_hrs;
416 new_references.exclude_from_max_cp_limit := x_exclude_from_max_cp_limit;
417 new_references.record_exclusion_flag := x_record_exclusion_flag;
418 new_references.ss_display_ind := x_ss_display_ind;
419 new_references.cal_type_enrol_load_cal := x_cal_type_enrol_load_cal;
420 new_references.sequence_num_enrol_load_cal := x_sequence_num_enrol_load_cal;
421 new_references.cal_type_offer_load_cal := x_cal_type_offer_load_cal;
422 new_references.sequence_num_offer_load_cal := x_sequence_num_offer_load_cal;
423 new_references.curriculum_id := x_curriculum_id;
424 new_references.override_enrollment_max := x_override_enrollment_max;
425 new_references.rpt_fmly_id := x_rpt_fmly_id;
426 new_references.unit_type_id := x_unit_type_id;
427 new_references.special_permission_ind := x_special_permission_ind;
428 new_references.org_id:=x_org_id;
429 new_references.ss_enrol_ind := x_ss_enrol_ind;
430 new_references.ivr_enrol_ind := x_ivr_enrol_ind;
431 new_references.rev_account_cd := x_rev_account_cd;
432 new_references.claimable_hours := x_claimable_hours;
433 new_references.anon_unit_grading_ind := x_anon_unit_grading_ind;
434 new_references.anon_assess_grading_ind := x_anon_assess_grading_ind;
435 new_references.auditable_ind := x_auditable_ind;
436 new_references.audit_permission_ind := x_audit_permission_ind;
437 new_references.max_auditors_allowed := x_max_auditors_allowed;
438 new_references.billing_credit_points := x_billing_credit_points;
439 new_references.ovrd_wkld_val_flag := x_ovrd_wkld_val_flag;
440 new_references.workload_val_code := x_workload_val_code;
441 new_references.billing_hrs := x_billing_hrs;
442 IF (p_action = 'UPDATE') THEN
443 new_references.creation_date := old_references.creation_date;
444 new_references.created_by := old_references.created_by;
445 ELSE
446 new_references.creation_date := x_creation_date;
447 new_references.created_by := x_created_by;
448 END IF;
449 new_references.last_update_date := x_last_update_date;
450 new_references.last_updated_by := x_last_updated_by;
451 new_references.last_update_login := x_last_update_login;
452
453 END Set_Column_Values;
454
455
456 PROCEDURE BeforeRowInsertUpdateDelete1(
457 p_inserting IN BOOLEAN,
458 p_updating IN BOOLEAN ,
459 p_deleting IN BOOLEAN
460 ) AS
461 --modified rgangara as per DLD Unit Section Enrollment on 03-May-2001 added two nwe Columns SS_enrol_ind and IVr_enrol_ind
462 v_message_name Varchar2(30);
463 v_return_type VARCHAR2(1);
464 -- Variables for history routine
465 v_start_dt IGS_PS_UNIT_VER_ALL.start_dt%TYPE;
466 v_expiry_dt IGS_PS_UNIT_VER_ALL.expiry_dt%TYPE;
467 v_review_dt IGS_PS_UNIT_VER_ALL.review_dt%TYPE;
468 v_end_dt IGS_PS_UNIT_VER_ALL.end_dt%TYPE;
469 v_unit_status IGS_PS_UNIT_VER_ALL.unit_status%TYPE;
470 v_title IGS_PS_UNIT_VER_ALL.title%TYPE;
471 v_short_title IGS_PS_UNIT_VER_ALL.short_title%TYPE;
472 v_title_override_ind IGS_PS_UNIT_VER_ALL.title_override_ind%TYPE;
473 v_abbreviation IGS_PS_UNIT_VER_ALL.abbreviation%TYPE;
474 v_unit_level IGS_PS_UNIT_VER_ALL.unit_level%TYPE;
475 v_credit_point_descriptor IGS_PS_UNIT_VER_ALL.credit_point_descriptor%TYPE;
476 v_achievable_credit_points IGS_PS_UNIT_VER_ALL.achievable_credit_points%TYPE;
477 v_enrolled_credit_points IGS_PS_UNIT_VER_ALL.enrolled_credit_points%TYPE;
478 v_supp_exam_permitted_ind IGS_PS_UNIT_VER_ALL.supp_exam_permitted_ind%TYPE;
479 v_points_increment IGS_PS_UNIT_VER_ALL.points_increment%TYPE;
480 v_points_min IGS_PS_UNIT_VER_ALL.points_min%TYPE;
481 v_points_max IGS_PS_UNIT_VER_ALL.points_max%TYPE;
482 v_points_override_ind IGS_PS_UNIT_VER_ALL.points_override_ind%TYPE;
483 v_coord_person_id IGS_PS_UNIT_VER_ALL.coord_person_id%TYPE;
484 v_owner_org_unit_cd IGS_PS_UNIT_VER_ALL.owner_org_unit_cd%TYPE;
485 v_owner_ou_start_dt IGS_PS_UNIT_VER_ALL.owner_ou_start_dt%TYPE;
486 v_award_course_only_ind IGS_PS_UNIT_VER_ALL.award_course_only_ind%TYPE;
487 v_research_unit_ind IGS_PS_UNIT_VER_ALL.research_unit_ind%TYPE;
488 v_industrial_ind IGS_PS_UNIT_VER_ALL.industrial_ind%TYPE;
489 v_practical_ind IGS_PS_UNIT_VER_ALL.practical_ind%TYPE;
490 v_repeatable_ind IGS_PS_UNIT_VER_ALL.repeatable_ind%TYPE;
491 v_assessable_ind IGS_PS_UNIT_VER_ALL.assessable_ind%TYPE;
492 v_unit_int_course_level_cd IGS_PS_UNIT_VER_ALL.unit_int_course_level_cd%TYPE;
493 cst_error VARCHAR2(1);
494 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
495 v_preferred_name IGS_PE_PERSON.preferred_given_name%TYPE;
496 v_ss_enrol_ind IGS_PS_UNIT_VER_ALL.ss_enrol_ind%TYPE;
497 v_ivr_enrol_ind IGS_PS_UNIT_VER_ALL.ivr_enrol_ind%TYPE;
498
499 -- Modified by rbezawad on 24-May-2001. Added following 47 fields as per PSP001-US DLD.
500
501 v_advance_maximum IGS_PS_UNIT_VER_ALL.advance_maximum%TYPE;
502 v_approval_date IGS_PS_UNIT_VER_ALL.approval_date%TYPE;
503 v_cal_type_enrol_load_cal IGS_PS_UNIT_VER_ALL.cal_type_enrol_load_cal%TYPE;
504 v_cal_type_offer_load_cal IGS_PS_UNIT_VER_ALL.cal_type_offer_load_cal%TYPE;
505 v_clock_hours IGS_PS_UNIT_VER_ALL.clock_hours%TYPE;
506 v_contact_hrs_lab IGS_PS_UNIT_VER_ALL.contact_hrs_lab%TYPE;
507 v_contact_hrs_lecture IGS_PS_UNIT_VER_ALL.contact_hrs_lecture%TYPE;
508 v_contact_hrs_other IGS_PS_UNIT_VER_ALL.contact_hrs_other%TYPE;
509 v_continuing_education_units IGS_PS_UNIT_VER_ALL.continuing_education_units%TYPE;
510 v_curriculum_id IGS_PS_UNIT_VER_ALL.curriculum_id%TYPE;
511 v_enrollment_expected IGS_PS_UNIT_VER_ALL.enrollment_expected%TYPE;
512 v_enrollment_maximum IGS_PS_UNIT_VER_ALL.enrollment_maximum%TYPE;
513 v_enrollment_minimum IGS_PS_UNIT_VER_ALL.enrollment_minimum%TYPE;
514 v_exclude_from_max_cp_limit IGS_PS_UNIT_VER_ALL.exclude_from_max_cp_limit%TYPE;
515 v_federal_financial_aid IGS_PS_UNIT_VER_ALL.federal_financial_aid%TYPE;
516 v_institutional_financial_aid IGS_PS_UNIT_VER_ALL.institutional_financial_aid%TYPE;
517 v_lab_credit_points IGS_PS_UNIT_VER_ALL.lab_credit_points%TYPE;
518 v_lecture_credit_points IGS_PS_UNIT_VER_ALL.lecture_credit_points%TYPE;
519 v_max_repeat_credit_points IGS_PS_UNIT_VER_ALL.max_repeat_credit_points%TYPE;
520 v_max_repeats_for_credit IGS_PS_UNIT_VER_ALL.max_repeats_for_credit%TYPE;
521 v_max_repeats_for_funding IGS_PS_UNIT_VER_ALL.max_repeats_for_funding%TYPE;
522 v_non_schd_required_hrs IGS_PS_UNIT_VER_ALL.non_schd_required_hrs%TYPE;
523 v_other_credit_points IGS_PS_UNIT_VER_ALL.other_credit_points%TYPE;
524 v_override_enrollment_max IGS_PS_UNIT_VER_ALL.override_enrollment_max%TYPE;
525 v_record_exclusion_flag IGS_PS_UNIT_VER_ALL.record_exclusion_flag%TYPE;
526 v_ss_display_ind IGS_PS_UNIT_VER_ALL.ss_display_ind%TYPE;
527 v_rpt_fmly_id IGS_PS_UNIT_VER_ALL.rpt_fmly_id%TYPE;
528 v_same_teach_period_repeats IGS_PS_UNIT_VER_ALL.same_teach_period_repeats%TYPE;
529 v_same_teach_period_repeats_cp IGS_PS_UNIT_VER_ALL.same_teach_period_repeats_cp%TYPE;
530 v_same_teaching_period IGS_PS_UNIT_VER_ALL.same_teaching_period%TYPE;
531 v_sequence_num_enrol_load_cal IGS_PS_UNIT_VER_ALL.sequence_num_enrol_load_cal%TYPE;
532 v_sequence_num_offer_load_cal IGS_PS_UNIT_VER_ALL.sequence_num_offer_load_cal%TYPE;
533 v_special_permission_ind IGS_PS_UNIT_VER_ALL.special_permission_ind%TYPE;
534 v_state_financial_aid IGS_PS_UNIT_VER_ALL.state_financial_aid%TYPE;
535 v_subtitle_id IGS_PS_UNIT_VER_ALL.subtitle_id%TYPE;
536 v_subtitle_modifiable_flag IGS_PS_UNIT_VER_ALL.subtitle_modifiable_flag%TYPE;
537 v_unit_type_id IGS_PS_UNIT_VER_ALL.unit_type_id%TYPE;
538 v_work_load_cp_lab IGS_PS_UNIT_VER_ALL.work_load_cp_lab%TYPE;
539 v_work_load_cp_lecture IGS_PS_UNIT_VER_ALL.work_load_cp_lecture%TYPE;
540 v_work_load_other IGS_PS_UNIT_VER_ALL.work_load_other%TYPE;
541 --msrinivi Added new column Bug :1882122
542 v_rev_account_cd IGS_PS_UNIT_VER_ALL.rev_account_cd%TYPE;
543 v_claimable_hours IGS_PS_UNIT_VER_ALL.claimable_hours%TYPE;
544 v_anon_unit_grading_ind IGS_PS_UNIT_VER_ALL.anon_unit_grading_ind%TYPE;
545 v_anon_assess_grading_ind IGS_PS_UNIT_VER_ALL.anon_assess_grading_ind%TYPE;
546 v_auditable_ind IGS_PS_UNIT_VER_ALL.auditable_ind%TYPE;
547 v_audit_permission_ind IGS_PS_UNIT_VER_ALL.audit_permission_ind%TYPE;
548 v_max_auditors_allowed IGS_PS_UNIT_VER_ALL.max_auditors_allowed%TYPE;
549 v_billing_credit_points IGS_PS_UNIT_VER_ALL.billing_credit_points%TYPE;
550 v_ovrd_wkld_val_flag IGS_PS_UNIT_VER_ALL.ovrd_wkld_val_flag%TYPE;
551 v_workload_val_code IGS_PS_UNIT_VER_ALL.workload_val_code%TYPE;
552 v_billing_hrs IGS_PS_UNIT_VER_ALL.billing_hrs%TYPE;
553
554 CURSOR c_get_s_unit_status IS
555 SELECT s_unit_status
556 FROM IGS_PS_UNIT_STAT
557 WHERE unit_status = new_references.unit_status ;
558 --AND unit_status = 'INACTIVE';
559
560 CURSOR SPUVH_CUR IS
561 SELECT Rowid
562 FROM IGS_PS_UNIT_VER_HIST
563 WHERE unit_cd = old_references.unit_cd AND
564 version_number = old_references.version_number;
565 -- cursor which picks all the unit codes whose curriculum id is closed
566
567 CURSOR c_igs_ps_unit_ver_all is
568 SELECT '1'
569 FROM igs_ps_unit_ver_v uv
570 WHERE unit_cd = new_references.unit_cd
571 AND version_number = new_references.version_number
572 AND curriculum_id = new_references.curriculum_id
573 AND EXISTS (SELECT '1'
574 FROM igs_ps_unt_crclm ucur
575 WHERE ucur.curriculum_id = uv.curriculum_id
576 AND ucur.closed_ind = 'Y' ) ;
577
578 l_c_igs_ps_unit_ver_all c_igs_ps_unit_ver_all%ROWTYPE ; -- cursor variable
579
580 BEGIN
581 cst_error := 'E';
582
583 -- Validate the IGS_PS_UNIT version fields cannot be updated if the IGS_PS_UNIT
584 -- version has a system status of 'INACTIVE'. IGS_GE_EXCEPTIONS are : IGS_PS_UNIT_STAT,
585 -- expiry_dt and end_dt.
586 -- updated by ssawhney 10-Nov-2000. Incorrect validations for ref start date and review date
587
588 IF p_updating THEN
589 IF old_references.curriculum_id <> new_references.curriculum_id THEN
590 -- checks if the curriculum attached to the unit is closed . If curriculum is closed , error out NOCOPY
591 -- Closed curriculum id should not allow any update to existing repeat fail of units .
592 -- added by smadathi on 25-MAY-2001 as per new DLD requirement
593 OPEN c_igs_ps_unit_ver_all ;
594 FETCH c_igs_ps_unit_ver_all INTO l_c_igs_ps_unit_ver_all ;
595 IF c_igs_ps_unit_ver_all%FOUND THEN
596 CLOSE c_igs_ps_unit_ver_all ;
597 FND_MESSAGE.SET_NAME('IGS','IGS_PS_CURRICULUM_CLOSED');
598 IGS_GE_MSG_STACK.ADD;
599 APP_EXCEPTION.RAISE_EXCEPTION ;
600 END IF;
601 CLOSE c_igs_ps_unit_ver_all ;
602 END IF;
603 OPEN c_get_s_unit_status;
604 FETCH c_get_s_unit_status
605 INTO v_s_unit_status;
606 IF c_get_s_unit_status%FOUND AND (v_s_unit_status ='INACTIVE') THEN
607 IF (NVL(old_references.start_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) <>
608 (NVL(new_references.start_dt,IGS_GE_DATE.IGSDATE('1900/01/01')))OR
609 (NVL(old_references.review_dt,IGS_GE_DATE.IGSDATE('1900/01/01')))<>
610 (NVL(new_references.review_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
611 (old_references.title <> new_references.title) OR
612 (old_references.short_title <> new_references.short_title) OR
613 (old_references.title_override_ind <> new_references.title_override_ind) OR
614 (old_references.abbreviation <> new_references.abbreviation) OR
615 (old_references.unit_level <> new_references.unit_level) OR
616 (old_references.credit_point_descriptor<> new_references.credit_point_descriptor) OR
617 (old_references.points_override_ind <> new_references.points_override_ind) OR
618 (old_references.supp_exam_permitted_ind <> new_references.supp_exam_permitted_ind) OR
619 (old_references.award_course_only_ind <> new_references.award_course_only_ind) OR
620 (old_references.research_unit_ind <> new_references.research_unit_ind) OR
621 (old_references.industrial_ind <> new_references.industrial_ind) OR
622 (old_references.owner_org_unit_cd <> new_references.owner_org_unit_cd) OR
623 (NVL(old_references.owner_ou_start_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
624 NVL(new_references.owner_ou_start_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
625 (old_references.practical_ind <>new_references.practical_ind) OR
626 (old_references.repeatable_ind <>new_references.repeatable_ind) OR
627 (old_references.assessable_ind <>new_references.assessable_ind) OR
628 (NVL(old_references.enrolled_credit_points,999999) <>
629 NVL(new_references.enrolled_credit_points,999999)) OR
630 (NVL(old_references.coord_person_id,999999) <>
631 NVL(new_references.coord_person_id,999999)) OR
632 (NVL(old_references.achievable_credit_points,999999) <>
633 NVL(new_references.achievable_credit_points,999999)) OR
634 (NVL(old_references.points_increment,999999) <> NVL(new_references.points_increment,999999)) OR
635 (NVL(old_references.claimable_hours,9999999) <> NVL(new_references.claimable_hours,9999999)) OR
636 (NVL(old_references.points_min,999999) <>
637 NVL(new_references.points_min,999999)) OR
638 (NVL(old_references.points_max,999999) <>
639 NVL(new_references.points_max,999999)) OR
640 (old_references.unit_int_course_level_cd <> new_references.unit_int_course_level_cd) OR
641 (NVL(old_references.billing_hrs,999999) <>
642 NVL(new_references.billing_hrs,999999)) OR
643 (NVL(old_references.rev_account_cd,'UNSET') <> NVL(new_references.rev_account_cd,'UNSET'))
644 THEN
645 CLOSE c_get_s_unit_status;
646 Fnd_Message.Set_Name('IGS','IGS_PS_NOCHG_UNITVER_DETAILS');
647 IGS_GE_MSG_STACK.ADD;
648 App_Exception.Raise_Exception;
649 END IF;
650 END IF; -- s_unit_status found.
651 CLOSE c_get_s_unit_status;
652 END IF;
653 -- Validate IGS_PS_UNIT internal IGS_PS_COURSE level.
654
655 IF p_inserting OR
656 (p_updating AND
657 (old_references.unit_int_course_level_cd <>
658 new_references.unit_int_course_level_cd)) THEN
659 IF IGS_PS_VAL_UV.crsp_val_uv_uicl (
660 new_references.unit_int_course_level_cd, v_message_name) = FALSE THEN
661 Fnd_Message.Set_Name('IGS',v_message_name);
662 IGS_GE_MSG_STACK.ADD;
663 App_Exception.Raise_Exception;
664 END IF;
665 END IF;
666 -- Validate IGS_PS_UNIT level.
667 IF p_inserting OR
668 (p_updating AND
669 (old_references.unit_level <> new_references.unit_level)) THEN
670 IF IGS_PS_VAL_UV.crsp_val_unit_lvl (
671 new_references.unit_level,v_message_name) = FALSE THEN
672 Fnd_Message.Set_Name('IGS',v_message_name);
673 IGS_GE_MSG_STACK.ADD;
674 App_Exception.Raise_Exception;
675 END IF;
676 END IF;
677 -- Validate credit point descriptor.
678 IF p_inserting OR
679 (p_updating AND
680 (old_references.credit_point_descriptor <>
681 new_references.credit_point_descriptor )) THEN
682 IF IGS_PS_VAL_UV.crsp_val_uv_cp_desc (
683 new_references.credit_point_descriptor,v_message_name) = FALSE THEN
684 Fnd_Message.Set_Name('IGS',v_message_name);
685 IGS_GE_MSG_STACK.ADD;
686 App_Exception.Raise_Exception;
687 END IF;
688 END IF;
689 -- Validate the version dates.
690 IF p_inserting OR
691 (p_updating AND
692 (old_references.start_dt <> new_references.start_dt)) OR
693 (p_updating AND
694 (NVL(old_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))<>
695 NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01')))) OR
696 (p_updating AND
697 NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
698 NVL(new_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
699 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UV.crsp_val_ver_dt (
700 IF IGS_PS_VAL_US.crsp_val_ver_dt (
701 new_references.start_dt,
702 new_references.end_dt,
703 new_references.expiry_dt,v_message_name,FALSE) = FALSE THEN
704 Fnd_Message.Set_Name('IGS',v_message_name);
705 IGS_GE_MSG_STACK.ADD;
706 App_Exception.Raise_Exception;
707 END IF;
708 END IF;
709 IF p_updating AND new_references.end_dt IS NOT NULL THEN
710 IF IGS_PS_VAL_UV.crsp_val_uv_end(new_references.unit_cd,
711 new_references.version_number,
712 v_return_type,
713 v_message_name) = FALSE THEN
714 IF v_return_type = cst_error THEN
715 Fnd_Message.Set_Name('IGS',v_message_name);
716 IGS_GE_MSG_STACK.ADD;
717 App_Exception.Raise_Exception;
718 END IF;
719 END IF;
720 END IF;
721 -- Validate the owner org IGS_PS_UNIT.
722 IF p_inserting OR
723 (p_updating AND
724 ((old_references.owner_org_unit_cd <> new_references.owner_org_unit_cd) OR
725 (old_references.owner_ou_start_dt <> new_references.owner_ou_start_dt))) THEN
726 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UV.crsp_val_ou_sys_sts
727 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts (
728 new_references.owner_org_unit_cd,
729 new_references.owner_ou_start_dt,v_message_name) = FALSE THEN
730 Fnd_Message.Set_Name('IGS',v_message_name);
731 IGS_GE_MSG_STACK.ADD;
732 App_Exception.Raise_Exception;
733 END IF;
734 END IF;
735 -- Validate IGS_PS_UNIT status and end date combination.
736 IF p_inserting OR
737 (p_updating AND
738 ((NVL(old_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
739 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
740 (old_references.unit_status <> new_references.unit_status))) THEN
741 IF IGS_PS_VAL_UV.crsp_val_uv_end_sts(
742 new_references.end_dt,
743 new_references.unit_status,v_message_name) = FALSE THEN
744 Fnd_Message.Set_Name('IGS',v_message_name);
745 IGS_GE_MSG_STACK.ADD;
746 App_Exception.Raise_Exception;
747 END IF;
748 END IF;
749 -- Validate IGS_PS_UNIT coordinator.
750 IF new_references.coord_person_id IS NOT NULL AND
751 (NVL(old_references.coord_person_id, 0) <> new_references.coord_person_id) THEN
752 IF IGS_GE_MNT_SDTT.pid_val_staff(
753 new_references.coord_person_id,v_preferred_name) = FALSE THEN
754 Fnd_Message.Set_Name('IGS','IGS_GE_COORD_NOT_STAFF_MEMBER');
755 IGS_GE_MSG_STACK.ADD;
756 App_Exception.Raise_Exception;
757 END IF;
758 END IF;
759 -- Validate the points override indicator.
760 IF p_inserting OR
761 (p_updating AND
762 ((old_references.points_override_ind <> new_references.points_override_ind) OR
763 (NVL(old_references.points_increment, 0) <> NVL(new_references.points_increment, 0)) OR
764 (NVL(old_references.points_min, 0) <> NVL(new_references.points_min, 0)) OR
765 (NVL(old_references.points_max, 0) <> NVL(new_references.points_max, 0)) OR
766 (NVL(old_references.enrolled_credit_points, 0) <>
767 NVL(new_references.enrolled_credit_points, 0)) OR
768 (NVL(old_references.achievable_credit_points, 0) <>
769 NVL(new_references.achievable_credit_points,0)))) THEN
770 IF IGS_PS_VAL_UV.crsp_val_uv_pnt_ovrd(
771 new_references.points_override_ind,
772 new_references.points_increment,
773 new_references.points_min,
774 new_references.points_max,
775 new_references.enrolled_credit_points,
776 new_references.achievable_credit_points,v_message_name,FALSE) = FALSE THEN
777 Fnd_Message.Set_Name('IGS',v_message_name);
778 IGS_GE_MSG_STACK.ADD;
779 App_Exception.Raise_Exception;
780 END IF;
781 END IF;
782 -- Validate the supplementary exams permitted indicator against the
783 -- assessable indicator.
784 IF p_inserting OR
785 (p_updating AND
786 ((old_references.supp_exam_permitted_ind <>
787 new_references.supp_exam_permitted_ind) OR
788 (old_references.assessable_ind <>
789 new_references.assessable_ind))) THEN
790 IF IGS_PS_VAL_UV.crsp_val_uv_sup_exam (
791 new_references.supp_exam_permitted_ind,
792 new_references.assessable_ind,v_message_name) = FALSE THEN
793 Fnd_Message.Set_Name('IGS',v_message_name);
794 IGS_GE_MSG_STACK.ADD;
795 App_Exception.Raise_Exception;
796 END IF;
797 END IF;
798
799
800 -- Code added as per DLD Unit Section Enrollment Information
801 -- Validating atleast one Enrollment method is checked
802 IF p_inserting OR p_updating THEN
803 IF (new_references.ss_enrol_ind = 'N' AND new_references.ivr_enrol_ind = 'N') THEN
804 Fnd_Message.Set_Name('IGS','IGS_PS_ONE_UNIT_ENR_MTHD');
805 IGS_GE_MSG_STACK.ADD;
806 App_Exception.Raise_Exception;
807 END IF;
808 END IF;
809
810 IF p_updating THEN
811 IF old_references.start_dt <> new_references.start_dt OR
812 old_references.unit_status <> new_references.unit_status OR
813 old_references.title <> new_references.title OR
814 old_references.short_title <> new_references.short_title OR
815 old_references.title_override_ind <> new_references.title_override_ind OR
816 old_references.abbreviation <> new_references.abbreviation OR
817 old_references.unit_level <> new_references.unit_level OR
818 old_references.credit_point_descriptor <> new_references.credit_point_descriptor OR
819 NVL(old_references.achievable_credit_points,999999) <>
820 NVL(new_references.achievable_credit_points,999999) OR
821 old_references.enrolled_credit_points <> new_references.enrolled_credit_points OR
822 old_references.supp_exam_permitted_ind <> new_references.supp_exam_permitted_ind OR
823 NVL(old_references.points_increment,999999) <>
824 NVL(new_references.points_increment,999999) OR
825 NVL(old_references.points_min,999999) <> NVL(new_references.points_min,999999) OR
826 NVL(old_references.points_max,999999) <> NVL(new_references.points_max,999999) OR
827 old_references.points_override_ind <> new_references.points_override_ind OR
828 old_references.coord_person_id <> new_references.coord_person_id OR
829 old_references.owner_org_unit_cd <> new_references.owner_org_unit_cd OR
830 old_references.owner_ou_start_dt <> new_references.owner_ou_start_dt OR
831 old_references.award_course_only_ind <> new_references.award_course_only_ind OR
832 old_references.research_unit_ind <> new_references.research_unit_ind OR
833 old_references.industrial_ind <> new_references.industrial_ind OR
834 old_references.practical_ind <> new_references.practical_ind OR
835 old_references.repeatable_ind <> new_references.repeatable_ind OR
836 old_references.assessable_ind <> new_references.assessable_ind OR
837 NVL(old_references.unit_int_course_leveL_cd,'null') <>
838 NVL(new_references.unit_int_course_level_cd,'null') OR
839 NVL(old_references.review_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
840 NVL(new_references.review_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) OR
841 NVL(old_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
842 NVL(new_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) OR
843 NVL(old_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
844 NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) OR
845
846 --ADded as per DLD Unit Section Enrollment 03-May-01
847 NVL(old_references.ss_enrol_ind,'N') <> NVL(new_references.ss_enrol_ind,'N') OR
848 NVL(new_references.ivr_enrol_ind,'N') <> NVL(new_references.ivr_enrol_ind,'N') OR
849 --
850 --Added as per DLD PSP001-US by rbezawad on 24-May-2001
851 NVL(old_references.advance_maximum,999999) <> NVL(new_references.advance_maximum,999999) OR
852 NVL(old_references.approval_date, IGS_GE_DATE.IGSDATE('1900/01/01'))
853 <> NVL(new_references.approval_date,IGS_GE_DATE.IGSDATE('1900/01/01')) OR
854 NVL(old_references.cal_type_enrol_load_cal,'null')
855 <> NVL(new_references.cal_type_enrol_load_cal,'null') OR
856 NVL(old_references.cal_type_offer_load_cal,'null')
857 <> NVL(new_references.cal_type_offer_load_cal,'null') OR
858 NVL(old_references.clock_hours,999999) <> NVL(new_references.clock_hours,999999) OR
859 NVL(old_references.contact_hrs_lab,999999) <> NVL(new_references.contact_hrs_lab,999999) OR
860 NVL(old_references.contact_hrs_lecture,999999)
861 <> NVL(new_references.contact_hrs_lecture,999999) OR
862 NVL(old_references.contact_hrs_other,999999) <> NVL(new_references.contact_hrs_other,999999) OR
863 NVL(old_references.continuing_education_units,999999)
864 <> NVL(new_references.continuing_education_units,999999) OR
865 NVL(old_references.curriculum_id,'null') <> NVL(new_references.curriculum_id,'null') OR
866 NVL(old_references.enrollment_expected,999999)
867 <> NVL(new_references.enrollment_expected,999999) OR
868 NVL(old_references.enrollment_maximum,999999) <> NVL(new_references.enrollment_maximum,999999) OR
869 NVL(old_references.enrollment_minimum,999999) <> NVL(new_references.enrollment_minimum,999999) OR
870 NVL(old_references.exclude_from_max_cp_limit,'N')
871 <> NVL(new_references.exclude_from_max_cp_limit,'N') OR
872 NVL(old_references.federal_financial_aid,'N') <> NVL(new_references.federal_financial_aid,'N') OR
873 NVL(old_references.institutional_financial_aid,'N')
874 <> NVL(new_references.institutional_financial_aid,'N') OR
875 NVL(old_references.lab_credit_points,999999) <> NVL(new_references.lab_credit_points,999999) OR
876 NVL(old_references.lecture_credit_points,999999)
877 <> NVL(new_references.lecture_credit_points,999999) OR
878 NVL(old_references.max_repeat_credit_points,999999)
879 <> NVL(new_references.max_repeat_credit_points,999999) OR
880 NVL(old_references.max_repeats_for_credit,999999)
881 <> NVL(new_references.max_repeats_for_credit,999999) OR
882 NVL(old_references.max_repeats_for_funding,999999)
883 <> NVL(new_references.max_repeats_for_funding,999999) OR
884 NVL(old_references.non_schd_required_hrs,999999)
885 <> NVL(new_references.non_schd_required_hrs,999999) OR
886 NVL(old_references.other_credit_points,999999)
887 <> NVL(new_references.other_credit_points,999999) OR
888 NVL(old_references.override_enrollment_max,999999)
889 <> NVL(new_references.override_enrollment_max,999999) OR
890 NVL(old_references.record_exclusion_flag,'N') <> NVL(new_references.record_exclusion_flag,'N') OR
891 NVL(old_references.ss_display_ind,'N')
892 <> NVL(new_references.ss_display_ind,'N') OR
893 NVL(old_references.rpt_fmly_id,999999) <> NVL(new_references.rpt_fmly_id,999999) OR
894 NVL(old_references.same_teach_period_repeats,999999)
895 <> NVL(new_references.same_teach_period_repeats,999999) OR
896 NVL(old_references.same_teach_period_repeats_cp,999999)
897 <> NVL(new_references.same_teach_period_repeats_cp,999999) OR
898 NVL(old_references.same_teaching_period,'N')
899 <> NVL(new_references.same_teaching_period,'N') OR
900 NVL(old_references.sequence_num_enrol_load_cal,999999)
901 <> NVL(new_references.sequence_num_enrol_load_cal,999999) OR
902 NVL(old_references.sequence_num_offer_load_cal,999999)
903 <> NVL(new_references.sequence_num_offer_load_cal,999999) OR
904 NVL(old_references.special_permission_ind,'N')
905 <> NVL(new_references.special_permission_ind,'N') OR
906 NVL(old_references.state_financial_aid,'N') <> NVL(new_references.state_financial_aid,'N') OR
907 NVL(old_references.subtitle_id,999999) <> NVL(new_references.subtitle_id,999999) OR
908 NVL(old_references.subtitle_modifiable_flag,'N') <> NVL(new_references.subtitle_modifiable_flag,'N') OR
909 NVL(old_references.unit_type_id,999999) <> NVL(new_references.unit_type_id,999999) OR
910 NVL(old_references.work_load_cp_lab,999999) <> NVL(new_references.work_load_cp_lab,999999) OR
911 NVL(old_references.work_load_cp_lecture,999999)
912 <> NVL(new_references.work_load_cp_lecture,999999) OR
913 NVL(old_references.work_load_other,999999) <> NVL(new_references.work_load_other,999999) OR
914 NVL(old_references.claimable_hours,9999999) <> NVL(new_references.claimable_hours,9999999) OR
915 NVL(old_references.rev_account_cd,'UNSET') <> NVL(new_references.rev_account_cd,'UNSET') OR
916 NVL(old_references.auditable_ind,'N')
917 <> NVL(new_references.auditable_ind,'N') OR
918 NVL(old_references.audit_permission_ind,'N')
919 <> NVL(new_references.audit_permission_ind,'N') OR
920 NVL(old_references.max_auditors_allowed, 999999)
921 <> NVL(new_references.max_auditors_allowed, 999999) OR
922 NVL(old_references.billing_credit_points, 999999)
923 <> NVL(new_references.billing_credit_points, 999999) OR
924 NVL(old_references.ovrd_wkld_val_flag,'N') <> NVL(new_references.ovrd_wkld_val_flag,'N') OR
925 NVL(old_references.billing_hrs, 999999)
926 <> NVL(new_references.billing_hrs, 999999) OR
927 NVL(old_references.workload_val_code,'UNSET') <> NVL(new_references.workload_val_code,'UNSET')
928 THEN
929 --
930
931 IF (NVL(old_references.review_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))<>
932 NVL(new_references.review_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
933 v_review_dt := old_references.review_dt;
934 END IF;
935 IF (NVL(old_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
936 NVL(new_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
937 v_expiry_dt := old_references.expiry_dt;
938 END IF;
939 IF (NVL(old_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
940 NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
941 v_end_dt := old_references.end_dt;
942 END IF;
943 --
944 IF NVL(old_references.ss_enrol_ind,'N') <> NVL(new_references.ss_enrol_ind,'N') THEN
945 v_ss_enrol_ind := old_references.ss_enrol_ind;
946 END IF;
947 IF NVL(old_references.ivr_enrol_ind,'N') <> NVL(new_references.ivr_enrol_ind,'N') THEN
948 v_ivr_enrol_ind := old_references.ivr_enrol_ind;
949 END IF;
950
951 -- Use decode to compare the new and old values, and if they have changed
952 -- put the old value in the variable to be passed to the history routine.
953 SELECT DECODE(old_references.start_dt,new_references.start_dt,
954 NULL,old_references.start_dt),
955 DECODE(old_references.unit_status,new_references.unit_status,
956 NULL,old_references.unit_status),
957 DECODE(old_references.title,new_references.title,
958 NULL,old_references.title),
959 DECODE(old_references.short_title,new_references.short_title,
960 NULL,old_references.short_title),
961 DECODE(old_references.title_override_ind,new_references.title_override_ind,
962 NULL,old_references.title_override_ind),
963 DECODE(old_references.abbreviation,new_references.abbreviation,NULL,
964 old_references.abbreviation),
965 DECODE(old_references.unit_level,new_references.unit_level,
966 NULL,old_references.unit_level),
967 DECODE(old_references.credit_point_descriptor,new_references.credit_point_descriptor,
968 NULL,old_references.credit_point_descriptor),
969 DECODE(NVL(old_references.achievable_credit_points,999999),
970 NVL(new_references.achievable_credit_points,999999),
971 NULL,old_references.achievable_credit_points),
972 DECODE(old_references.enrolled_credit_points,new_references.enrolled_credit_points,
973 NULL,old_references.enrolled_credit_points),
974 DECODE(old_references.supp_exam_permitted_ind,new_references.supp_exam_permitted_ind,
975 NULL,old_references.supp_exam_permitted_ind),
976 DECODE(NVL(old_references.points_increment,999999),
977 NVL(new_references.points_increment,999999),
978 NULL,old_references.points_increment),
979 DECODE(NVL(old_references.points_min,999999),NVL(new_references.points_min,999999),
980 NULL,old_references.points_min),
981 DECODE(NVL(old_references.points_max,999999),NVL(new_references.points_max,999999),
982 NULL,old_references.points_max),
983 DECODE(old_references.points_override_ind,new_references.points_override_ind,
984 NULL,old_references.points_override_ind),
985 DECODE(old_references.coord_person_id,new_references.coord_person_id,
986 NULL,old_references.coord_person_id),
987 DECODE(old_references.owner_org_unit_cd,new_references.owner_org_unit_cd,
988 NULL,old_references.owner_org_unit_cd),
989 DECODE(old_references.owner_ou_start_dt,new_references.owner_ou_start_dt,
990 NULL,old_references.owner_ou_start_dt),
991 DECODE(old_references.award_course_only_ind,new_references.award_course_only_ind,
992 NULL,old_references.award_course_only_ind),
993 DECODE(old_references.research_unit_ind,new_references.research_unit_ind,
994 NULL,old_references.research_unit_ind),
995 DECODE(old_references.industrial_ind,new_references.industrial_ind,
996 NULL,old_references.industrial_ind),
997 DECODE(old_references.practical_ind,new_references.practical_ind,
998 NULL,old_references.practical_ind),
999 DECODE(old_references.repeatable_ind,new_references.repeatable_ind,
1000 NULL,old_references.repeatable_ind),
1001 DECODE(old_references.assessable_ind,new_references.assessable_ind,
1002 NULL,old_references.assessable_ind),
1003 DECODE(NVL(old_references.unit_int_course_leveL_cd,'NULL'),
1004 NVL(new_references.unit_int_course_level_cd,'NULL'),
1005 NULL,old_references.unit_int_course_level_cd),
1006 -- Added by rbezawad as per DLD PSP001-US on 24-May-2001
1007 DECODE( NVL(old_references.advance_maximum,999999),
1008 NVL(new_references.advance_maximum,999999),
1009 NULL, old_references.advance_maximum),
1010 DECODE( NVL(old_references.approval_date, IGS_GE_DATE.IGSDATE('1900/01/01')),
1011 NVL(new_references.approval_date,IGS_GE_DATE.IGSDATE('1900/01/01')) ,
1012 NULL, old_references.approval_date),
1013 DECODE( NVL(old_references.cal_type_enrol_load_cal,'null'),
1014 NVL(new_references.cal_type_enrol_load_cal,'null') ,
1015 NULL, old_references.cal_type_enrol_load_cal),
1016 DECODE( NVL(old_references.cal_type_offer_load_cal,'null'),
1017 NVL(new_references.cal_type_offer_load_cal,'null') ,
1018 NULL, old_references.cal_type_offer_load_cal),
1019 DECODE( NVL(old_references.clock_hours,999999),
1020 NVL(new_references.clock_hours,999999) ,
1021 NULL, old_references.clock_hours),
1022 DECODE( NVL(old_references.contact_hrs_lab,999999),
1023 NVL(new_references.contact_hrs_lab,999999) ,
1024 NULL, old_references.contact_hrs_lab),
1025 DECODE( NVL(old_references.contact_hrs_lecture,999999),
1026 NVL(new_references.contact_hrs_lecture,999999) ,
1027 NULL, old_references.contact_hrs_lecture),
1028 DECODE( NVL(old_references.contact_hrs_other,999999),
1029 NVL(new_references.contact_hrs_other,999999) ,
1030 NULL, old_references.contact_hrs_other),
1031 DECODE( NVL(old_references.continuing_education_units,999999),
1032 NVL(new_references.continuing_education_units,999999) ,
1033 NULL, old_references.continuing_education_units),
1034 DECODE( NVL(old_references.curriculum_id,'null'),
1035 NVL(new_references.curriculum_id,'null') ,
1036 NULL, old_references.curriculum_id),
1037 DECODE( NVL(old_references.enrollment_expected,999999),
1038 NVL(new_references.enrollment_expected,999999) ,
1039 NULL, old_references.enrollment_expected),
1040 DECODE( NVL(old_references.enrollment_maximum,999999),
1041 NVL(new_references.enrollment_maximum,999999) ,
1042 NULL, old_references.enrollment_maximum),
1043 DECODE( NVL(old_references.enrollment_minimum,999999),
1044 NVL(new_references.enrollment_minimum,999999) ,
1045 NULL, old_references.enrollment_minimum),
1046 DECODE( NVL(old_references.exclude_from_max_cp_limit,'N'),
1047 NVL(new_references.exclude_from_max_cp_limit,'N') ,
1048 NULL, old_references.exclude_from_max_cp_limit),
1049 DECODE( NVL(old_references.federal_financial_aid,'N'),
1050 NVL(new_references.federal_financial_aid,'N') ,
1051 NULL, old_references.federal_financial_aid),
1052 DECODE( NVL(old_references.institutional_financial_aid,'N'),
1053 NVL(new_references.institutional_financial_aid,'N') ,
1054 NULL, old_references.institutional_financial_aid),
1055 DECODE( NVL(old_references.lab_credit_points,999999),
1056 NVL(new_references.lab_credit_points,999999) ,
1057 NULL, old_references.lab_credit_points),
1058 DECODE( NVL(old_references.lecture_credit_points,999999),
1059 NVL(new_references.lecture_credit_points,999999) ,
1060 NULL, old_references.lecture_credit_points),
1061 DECODE( NVL(old_references.max_repeat_credit_points,999999),
1062 NVL(new_references.max_repeat_credit_points,999999) ,
1063 NULL, old_references.max_repeat_credit_points),
1064 DECODE( NVL(old_references.max_repeats_for_credit,999999),
1065 NVL(new_references.max_repeats_for_credit,999999) ,
1066 NULL, old_references.max_repeats_for_credit),
1067 DECODE( NVL(old_references.max_repeats_for_funding,999999),
1068 NVL(new_references.max_repeats_for_funding,999999) ,
1069 NULL, old_references.max_repeats_for_funding),
1070 DECODE( NVL(old_references.non_schd_required_hrs,999999),
1071 NVL(new_references.non_schd_required_hrs,999999) ,
1072 NULL, old_references.non_schd_required_hrs),
1073 DECODE( NVL(old_references.other_credit_points,999999),
1074 NVL(new_references.other_credit_points,999999) ,
1075 NULL, old_references.other_credit_points),
1076 DECODE( NVL(old_references.override_enrollment_max,999999),
1077 NVL(new_references.override_enrollment_max,999999) ,
1078 NULL, old_references.override_enrollment_max),
1079 DECODE( NVL(old_references.record_exclusion_flag,'N'),
1080 NVL(new_references.record_exclusion_flag,'N') ,
1081 NULL, old_references.record_exclusion_flag),
1082 DECODE( NVL(old_references.ss_display_ind,'N'),
1083 NVL(new_references.ss_display_ind,'N') ,
1084 NULL, old_references.ss_display_ind ),
1085 DECODE( NVL(old_references.rpt_fmly_id,999999),
1086 NVL(new_references.rpt_fmly_id,999999),
1087 NULL, old_references.rpt_fmly_id),
1088 DECODE( NVL(old_references.same_teach_period_repeats,999999),
1089 NVL(new_references.same_teach_period_repeats,999999) ,
1090 NULL, old_references.same_teach_period_repeats),
1091 DECODE( NVL(old_references.same_teach_period_repeats_cp,999999),
1092 NVL(new_references.same_teach_period_repeats_cp,999999),
1093 NULL, old_references.same_teach_period_repeats_cp),
1094 DECODE( NVL(old_references.same_teaching_period,'N'),
1095 NVL(new_references.same_teaching_period,'N') ,
1096 NULL, old_references.same_teaching_period),
1097 DECODE( NVL(old_references.sequence_num_enrol_load_cal,999999),
1098 NVL(new_references.sequence_num_enrol_load_cal,999999) ,
1099 NULL, old_references.sequence_num_enrol_load_cal),
1100 DECODE( NVL(old_references.sequence_num_offer_load_cal,999999),
1101 NVL(new_references.sequence_num_offer_load_cal,999999) ,
1102 NULL, old_references.sequence_num_offer_load_cal),
1103 DECODE( NVL(old_references.special_permission_ind,'N'),
1104 NVL(new_references.special_permission_ind,'N') ,
1105 NULL, old_references.special_permission_ind),
1106 DECODE( NVL(old_references.state_financial_aid,'N'),
1107 NVL(new_references.state_financial_aid,'N') ,
1108 NULL, old_references.state_financial_aid),
1109 DECODE( NVL(old_references.subtitle_id,999999),
1110 NVL(new_references.subtitle_id,999999) ,
1111 NULL, old_references.subtitle_id),
1112 DECODE( NVL(old_references.subtitle_modifiable_flag,'N'),
1113 NVL(new_references.subtitle_modifiable_flag,'N') ,
1114 NULL, old_references.subtitle_modifiable_flag),
1115 DECODE( NVL(old_references.unit_type_id,999999),
1116 NVL(new_references.unit_type_id,999999) ,
1117 NULL, old_references.unit_type_id),
1118 DECODE( NVL(old_references.work_load_cp_lab,999999),
1119 NVL(new_references.work_load_cp_lab,999999) ,
1120 NULL, old_references.work_load_cp_lab),
1121 DECODE( NVL(old_references.work_load_cp_lecture,999999),
1122 NVL(new_references.work_load_cp_lecture,999999) ,
1123 NULL, old_references.work_load_cp_lecture),
1124 DECODE( NVL(old_references.work_load_other,999999),
1125 NVL(new_references.work_load_other,999999),
1126 NULL, old_references.work_load_other),
1127 DECODE( NVL(old_references.claimable_hours,9999999),
1128 NVL(new_references.claimable_hours,9999999),
1129 NULL, old_references.claimable_hours),
1130 DECODE( NVL(old_references.auditable_ind,'N'),
1131 NVL(new_references.auditable_ind,'N'),
1132 NULL, old_references.auditable_ind),
1133 DECODE( NVL(old_references.audit_permission_ind,'N'),
1134 NVL(new_references.audit_permission_ind,'N'),
1135 NULL, old_references.audit_permission_ind),
1136 DECODE( NVL(old_references.max_auditors_allowed,9999999),
1137 NVL(new_references.max_auditors_allowed,9999999),
1138 NULL, old_references.max_auditors_allowed),
1139 DECODE( NVL(old_references.billing_credit_points,9999999),
1140 NVL(new_references.billing_credit_points,9999999),
1141 NULL, old_references.billing_credit_points),
1142 DECODE( NVL(old_references.ovrd_wkld_val_flag,'N'),
1143 NVL(new_references.ovrd_wkld_val_flag,'N'),
1144 NULL, old_references.ovrd_wkld_val_flag),
1145 DECODE( NVL(old_references.workload_val_code,'NULL'),
1146 NVL(new_references.workload_val_code,'NULL'),
1147 NULL, old_references.workload_val_code),
1148 DECODE( NVL(old_references.billing_hrs,9999999),
1149 NVL(new_references.billing_hrs,9999999),
1150 NULL, old_references.billing_hrs)
1151 --
1152 INTO v_start_dt,
1153 v_unit_status,
1154 v_title,
1155 v_short_title,
1156 v_title_override_ind,
1157 v_abbreviation,
1158 v_unit_level,
1159 v_credit_point_descriptor,
1160 v_achievable_credit_points,
1161 v_enrolled_credit_points,
1162 v_supp_exam_permitted_ind,
1163 v_points_increment,
1164 v_points_min,
1165 v_points_max,
1166 v_points_override_ind,
1167 v_coord_person_id,
1168 v_owner_org_unit_cd,
1169 v_owner_ou_start_dt,
1170 v_award_course_only_ind,
1171 v_research_unit_ind,
1172 v_industrial_ind,
1173 v_practical_ind,
1174 v_repeatable_ind,
1175 v_assessable_ind,
1176 v_unit_int_course_level_cd,
1177 -- Added by rbezawad as per PSP001-US DLD on 24-May-2001
1178 v_advance_maximum,
1179 v_approval_date,
1180 v_cal_type_enrol_load_cal,
1181 v_cal_type_offer_load_cal,
1182 v_clock_hours,
1183 v_contact_hrs_lab,
1184 v_contact_hrs_lecture,
1185 v_contact_hrs_other,
1186 v_continuing_education_units,
1187 v_curriculum_id,
1188 v_enrollment_expected,
1189 v_enrollment_maximum,
1190 v_enrollment_minimum,
1191 v_exclude_from_max_cp_limit,
1192 v_federal_financial_aid,
1193 v_institutional_financial_aid,
1194 v_lab_credit_points,
1195 v_lecture_credit_points,
1196 v_max_repeat_credit_points,
1197 v_max_repeats_for_credit,
1198 v_max_repeats_for_funding,
1199 v_non_schd_required_hrs,
1200 v_other_credit_points,
1201 v_override_enrollment_max,
1202 v_record_exclusion_flag,
1203 v_ss_display_ind,
1204 v_rpt_fmly_id,
1205 v_same_teach_period_repeats,
1206 v_same_teach_period_repeats_cp,
1207 v_same_teaching_period,
1208 v_sequence_num_enrol_load_cal,
1209 v_sequence_num_offer_load_cal,
1210 v_special_permission_ind,
1211 v_state_financial_aid,
1212 v_subtitle_id,
1213 v_subtitle_modifiable_flag,
1214 v_unit_type_id,
1215 v_work_load_cp_lab,
1216 v_work_load_cp_lecture,
1217 v_work_load_other,
1218 v_claimable_hours,
1219 v_auditable_ind,
1220 v_audit_permission_ind,
1221 v_max_auditors_allowed,
1222 v_billing_credit_points,
1223 v_ovrd_wkld_val_flag,
1224 v_workload_val_code,
1225 v_billing_hrs
1226 --
1227 FROM dual;
1228 -- Create history record for update
1229 IGS_PS_GEN_006.CRSP_INS_UV_HIST(
1230 old_references.unit_cd,
1231 old_references.version_number,
1232 old_references.last_update_date,
1233 new_references.last_update_date,
1234 old_references.last_updated_by,
1235 v_start_dt,
1236 v_review_dt,
1237 v_expiry_dt,
1238 v_end_dt,
1239 v_unit_status,
1240 v_title,
1241 v_short_title,
1242 v_title_override_ind,
1243 v_abbreviation,
1244 v_unit_level,
1245 v_credit_point_descriptor,
1246 v_achievable_credit_points,
1247 v_enrolled_credit_points,
1248 v_supp_exam_permitted_ind,
1249 v_points_increment,
1250 v_points_min,
1251 v_points_max,
1252 v_points_override_ind,
1253 v_coord_person_id,
1254 v_owner_org_unit_cd,
1255 v_owner_ou_start_dt,
1256 v_award_course_only_ind,
1257 v_research_unit_ind,
1258 v_industrial_ind,
1259 v_practical_ind,
1260 v_repeatable_ind,
1261 v_assessable_ind,
1262 v_unit_int_course_level_cd,
1263 v_ss_enrol_ind,
1264 v_ivr_enrol_ind,
1265 -- added by rbezawad as per PSP001-US DLD ON 24-MAY-2001
1266 v_advance_maximum,
1267 v_approval_date,
1268 v_cal_type_enrol_load_cal,
1269 v_cal_type_offer_load_cal,
1270 v_clock_hours,
1271 v_contact_hrs_lab,
1272 v_contact_hrs_lecture,
1273 v_contact_hrs_other,
1274 v_continuing_education_units,
1275 v_curriculum_id,
1276 v_enrollment_expected,
1277 v_enrollment_maximum,
1278 v_enrollment_minimum,
1279 v_exclude_from_max_cp_limit,
1280 v_federal_financial_aid,
1281 v_institutional_financial_aid,
1282 v_lab_credit_points,
1283 v_lecture_credit_points,
1284 v_max_repeat_credit_points,
1285 v_max_repeats_for_credit,
1286 v_max_repeats_for_funding,
1287 v_non_schd_required_hrs,
1288 v_other_credit_points,
1289 v_override_enrollment_max,
1290 v_record_exclusion_flag,
1291 v_ss_display_ind,
1292 v_rpt_fmly_id,
1293 v_same_teach_period_repeats,
1294 v_same_teach_period_repeats_cp,
1295 v_same_teaching_period,
1296 v_sequence_num_enrol_load_cal,
1297 v_sequence_num_offer_load_cal,
1298 v_special_permission_ind,
1299 v_state_financial_aid,
1300 v_subtitle_id,
1301 v_subtitle_modifiable_flag,
1302 v_unit_type_id,
1303 v_work_load_cp_lab,
1304 v_work_load_cp_lecture,
1305 v_work_load_other,
1306 v_claimable_hours,
1307 v_auditable_ind,
1308 v_audit_permission_ind,
1309 v_max_auditors_allowed,
1310 v_billing_credit_points,
1311 v_ovrd_wkld_val_flag,
1312 v_workload_val_code
1313 ,v_billing_hrs
1314 );
1315 END IF;
1316 END IF;
1317 IF p_deleting THEN
1318
1319 BEGIN
1320 FOR SPUVH_Rec IN SPUVH_CUR
1321 Loop
1322 IGS_PS_UNIT_VER_HIST_PKG.Delete_Row(X_ROWID=>SPUVH_Rec.Rowid);
1323 End Loop;
1324 END;
1325
1326
1327 END IF;
1328 END BeforeRowInsertUpdateDelete1;
1329
1330 PROCEDURE AfterRowInsertUpdate2(
1331 p_inserting IN BOOLEAN ,
1332 p_updating IN BOOLEAN ,
1333 p_deleting IN BOOLEAN
1334 ) AS
1335
1336 cst_active VARCHAR2 (6) ;
1337 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
1338 CURSOR c_get_s_unit_status
1339 (cp_unit_status IGS_PS_UNIT_STAT.unit_status%TYPE) IS
1340 SELECT s_unit_status
1341 FROM IGS_PS_UNIT_STAT
1342 WHERE unit_status = cp_unit_status;
1343
1344 v_rowid_saved BOOLEAN := FALSE;
1345
1346 v_message_name Varchar2(30);
1347
1348 BEGIN
1349 cst_active := 'ACTIVE';
1350 -- Validate IGS_PS_UNIT status and expiry date.
1351 IF p_inserting OR
1352 (p_updating AND
1353 ((NVL(old_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
1354 NVL(new_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
1355 (old_references.unit_status <> new_references.unit_status))) THEN
1356
1357 v_rowid_saved:= TRUE;
1358
1359 END IF;
1360 -- Validate the IGS_PS_UNIT status.
1361 IF p_inserting OR
1362 (p_updating AND (old_references.unit_status <> new_references.unit_status)) THEN
1363 -- Save the rowid, old expiry date and old IGS_PS_UNIT status so
1364 -- the IGS_PS_UNIT status can be validated in the after statement
1365 -- trigger as calling IGS_PS_VAL_UV.crsp_val_uv_unit_sts from
1366 -- here will cause mutating table error. Also, the quality check
1367 -- may need to be performed if the status has been altered to
1368 -- active.
1369
1370 v_rowid_saved:= TRUE;
1371
1372 END IF;
1373
1374
1375 IF v_rowid_saved = TRUE Then
1376
1377 -- Validate IGS_PS_UNIT status and expiry date.
1378 IF p_inserting OR
1379 (p_updating AND
1380 ((NVL(old_references.expiry_dt,
1381 IGS_GE_DATE.IGSDATE('1900/01/01')) <>
1382 NVL(new_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
1383 (old_references.unit_status <> new_references.unit_status))) THEN
1384 IF IGS_PS_VAL_UV.crsp_val_uv_exp_sts (
1385 new_references.unit_cd,
1386 new_references.version_number,
1387 new_references.expiry_dt,
1388 new_references.unit_status,v_message_name) = FALSE THEN
1389 Fnd_Message.Set_Name('IGS',v_message_name);
1390 IGS_GE_MSG_STACK.ADD;
1391 App_Exception.Raise_Exception;
1392 END IF;
1393 END IF;
1394 -- Validate the IGS_PS_UNIT status
1395 IF p_inserting OR
1396 (p_updating AND
1397 (old_references.unit_status <> new_references.unit_status)) THEN
1398 IF IGS_PS_VAL_UV.crsp_val_uv_unit_sts (
1399 new_references.unit_cd,
1400 new_references.version_number,
1401 new_references.unit_status,
1402 old_references.unit_status,v_message_name,FALSE) = FALSE THEN
1403 Fnd_Message.Set_Name('IGS',v_message_name);
1404 IGS_GE_MSG_STACK.ADD;
1405 App_Exception.Raise_Exception;
1406 END IF;
1407 OPEN c_get_s_unit_status (new_references.unit_status);
1408 FETCH c_get_s_unit_status INTO v_s_unit_status;
1409 CLOSE c_get_s_unit_status;
1410 -- Perform a quality check if updating to a system status of ACTIVE.
1411 -- IGS_GE_NOTE: A IGS_PS_UNIT version can only be created with a status of PLANNED.
1412 -- Hence, only need to perform the check if updating.
1413 IF p_updating AND
1414 (v_s_unit_status = cst_active) THEN
1415 IF IGS_PS_VAL_UV.crsp_val_uv_quality (
1416 new_references.unit_cd,
1417 new_references.version_number,
1418 old_references.unit_status,v_message_name) = FALSE THEN
1419 Fnd_Message.Set_Name('IGS',v_message_name);
1420 IGS_GE_MSG_STACK.ADD;
1421 App_Exception.Raise_Exception;
1422 END IF;
1423 END IF;
1424 END IF;
1425 END IF;
1426
1427 END AfterRowInsertUpdate2;
1428
1429 PROCEDURE Check_Constraints(
1430 Column_Name IN VARCHAR2,
1431 Column_Value IN VARCHAR2 )
1432 AS
1433 --sarakshi 15-May-2006 Bug#3064563, modified the format mask(clock_hours,continuing_education_units,work_load_cp_lecture,work_load_cp_lab,contact_hrs_lab) as specified in the bug.
1434 --smvk 09-Jan-2003 Bug # 2702263, Checking the range of values for columns claimable_hours,lecture_credit_points,
1435 -- lab_credit_points,other_credit_points,clock_hours,continuing_education_units,
1436 -- advance_maximum,enrollment_expected,enrollment_minimum,enrollment_maximum,
1437 -- override_enrollment_max,max_auditors_allowed,work_load_cp_lecture,work_load_cp_lab,
1438 -- max_repeat_credit_points,same_teach_period_repeats_cp,work_load_other,contact_hrs_lecture,
1439 -- contact_hrs_lab,contact_hrs_other,non_schd_required_hrs,max_repeats_for_credit,
1440 -- max_repeats_for_funding and same_teach_period_repeats.
1441 --sarakshi 11-Dec-2002 Bug#2702240,checking values for state_financial_aid,federal_financial_aid,
1442 -- institutional_financial_aid,same_teaching_period,exclude_from_cp_limit,
1443 -- record_exclusion_flag,ss_display_ind,special_permission_ind,
1444 -- anon_unit_grading_ind,anon_assess_grading_ind
1445 -- sarakshi 27-Nov-2002 Enh#2649028,for all numeric value comparision, changed the AND condition with OR
1446 -- to check the value between 0 and 999.Also Practicle indicator check of Y/N added
1447 -- rgangara 03-May-2001 modified as per DLD Unit Section Enrollment
1448 BEGIN
1449
1450 IF Column_Name IS NULL Then
1451 NULL;
1452 ELSIF Upper(Column_Name)='ABBREVIATION' Then
1453 New_References.abbreviation := Column_Value;
1454 ELSIF Upper(Column_Name)='ASSESSABLE_IND' Then
1455 New_References.assessable_ind := Column_Value;
1456 ELSIF Upper(Column_Name)='AWARD_COURSE_ONLY_IND' Then
1457 New_References.award_course_only_ind := Column_Value;
1458 ELSIF Upper(Column_Name)='CREDIT_POINT_DESCRIPTOR' Then
1459 New_References.credit_point_descriptor := Column_Value;
1460 ELSIF Upper(Column_Name)='INDUSTRIAL_IND' Then
1461 New_References.industrial_ind := Column_Value;
1462 ELSIF Upper(Column_Name)='POINTS_OVERRIDE_IND' Then
1463 New_References.points_override_ind := Column_Value;
1464 ELSIF Upper(Column_Name)='PRACTICAL_IND' Then
1465 New_References.practical_ind := Column_Value;
1466 ELSIF Upper(Column_Name)='REPEATABLE_IND' Then
1467 New_References.repeatable_ind := Column_Value;
1468 ELSIF Upper(Column_Name)='RESEARCH_UNIT_IND' Then
1469 New_References.research_unit_ind := Column_Value;
1470 ELSIF Upper(Column_Name)='SHORT_TITLE' TheN
1471 New_References.Short_Title := Column_Value;
1472 ELSIF Upper(Column_Name)='SUPP_EXAM_PERMITTED_IND' Then
1473 New_References.supp_exam_permitted_ind := Column_Value;
1474 ELSIF Upper(Column_Name)='TITLE' Then
1475 New_References.title := Column_Value;
1476 ELSIF Upper(Column_Name)='TITLE_OVERRIDE_IND' Then
1477 New_References.title_override_ind := Column_Value;
1478 ELSIF Upper(Column_Name)='UNIT_CD' Then
1479 New_References.unit_cd := Column_Value;
1480 ELSIF Upper(Column_Name)='UNIT_INT_COURSE_LEVEL_CD' Then
1481 New_References.unit_int_course_level_cd := Column_Value;
1482 ELSIF Upper(Column_Name)='UNIT_LEVEL' Then
1483 New_References.unit_level:= Column_Value;
1484 ELSIF Upper(Column_Name)='UNIT_STATUS' Then
1485 New_References.unit_status:= Column_Value;
1486 ELSIF Upper(Column_Name)='ACHIEVABLE_CREDIT_POINTS' Then
1487 New_References.achievable_credit_points:= IGS_GE_NUMBER.to_num(Column_Value);
1488 ELSIF Upper(Column_Name)='POINTS_INCREMENT' Then
1489 New_References.points_increment:= IGS_GE_NUMBER.to_num(Column_Value);
1490 ELSIF Upper(Column_Name)='ENROLLED_CREDIT_POINTS' Then
1491 New_References.enrolled_credit_points:= IGS_GE_NUMBER.to_num(Column_Value);
1492 ELSIF Upper(Column_Name)='VERSION_NUMBER' Then
1493 New_References.version_number:= IGS_GE_NUMBER.to_num(Column_Value);
1494 ELSIF Upper(Column_Name)='POINTS_MIN' Then
1495 New_References.points_min:= IGS_GE_NUMBER.to_num(Column_Value);
1496 ELSIF Upper(Column_Name)='POINTS_MAX' Then
1497 New_References.points_min:= IGS_GE_NUMBER.to_num(Column_Value);
1498 ELSIF Upper(Column_Name)='STATE_FINANCIAL_AID' Then
1499 New_References.state_financial_aid := Column_Value;
1500 ELSIF Upper(Column_Name)='FEDERAL_FINANCIAL_AID' Then
1501 New_References.federal_financial_aid := Column_Value;
1502 ELSIF Upper(Column_Name)='INSTITUTIONAL_FINANCIAL_AID' Then
1503 New_References.institutional_financial_aid := Column_Value;
1504 ELSIF Upper(Column_Name)='SAME_TEACHING_PERIOD' Then
1505 New_References.same_teaching_period := Column_Value;
1506 ELSIF Upper(Column_Name)='EXCLUDE_FROM_MAX_CP_LIMIT' Then
1507 New_References.exclude_from_max_cp_limit := Column_Value;
1508 ELSIF Upper(Column_Name)='RECORD_EXCLUSION_FLAG' Then
1509 New_References.record_exclusion_flag := Column_Value;
1510 ELSIF Upper(Column_Name)='SS_DISPLAY_IND' Then
1511 New_References.ss_display_ind := Column_Value;
1512 ELSIF Upper(Column_Name)='SPECIAL_PERMISSION_IND' Then
1513 New_References.special_permission_ind := Column_Value;
1514 ELSIF Upper(Column_Name)='SS_ENROL_IND' Then
1515 New_References.ss_enrol_ind := Column_Value;
1516 ELSIF Upper(Column_Name)='IVR_ENROL_IND' Then
1517 New_References.ivr_enrol_ind := Column_Value;
1518 ELSIF Upper(Column_Name)='ANON_UNIT_GRADING_IND' Then
1519 New_References.anon_unit_grading_ind := Column_Value;
1520 ELSIF Upper(Column_Name)='ANON_ASSESS_GRADING_IND' Then
1521 New_References.anon_assess_grading_ind := Column_Value;
1522 ELSIF Upper(Column_Name)='AUDITABLE_IND' Then
1523 New_References.auditable_ind := Column_Value;
1524 ELSIF Upper(Column_Name)='AUDIT_PERMISSION_IND' Then
1525 New_References.audit_permission_ind := Column_Value;
1526 -- Added as a part of 2702263
1527 ELSIF Upper(Column_Name)='CLAIMABLE_HOURS' Then
1528 New_References.claimable_hours := Column_Value;
1529 ELSIF Upper(Column_Name)='LECTURE_CREDIT_POINTS' Then
1530 New_References.lecture_credit_points := Column_Value;
1531 ELSIF Upper(Column_Name)='LAB_CREDIT_POINTS' Then
1532 New_References.lab_credit_points := Column_Value;
1533 ELSIF Upper(Column_Name)='OTHER_CREDIT_POINTS' Then
1534 New_References.other_credit_points := Column_Value;
1535 ELSIF Upper(Column_Name)='CLOCK_HOURS' Then
1536 New_References.clock_hours := Column_Value;
1537 ELSIF Upper(Column_Name)='WORK_LOAD_CP_LECTURE' Then
1538 New_References.work_load_cp_lecture := Column_Value;
1539 ELSIF Upper(Column_Name)='WORK_LOAD_CP_LAB' Then
1540 New_References.work_load_cp_lab := Column_Value;
1541 ELSIF Upper(Column_Name)='CONTINUING_EDUCATION_UNITS' Then
1542 New_References.continuing_education_units := Column_Value;
1543 ELSIF Upper(Column_Name)='ADVANCE_MAXIMUM' Then
1544 New_References.advance_maximum := Column_Value;
1545 ELSIF Upper(Column_Name)='ENROLLMENT_EXPECTED' Then
1546 New_References.enrollment_expected := Column_Value;
1547 ELSIF Upper(Column_Name)='ENROLLMENT_MINIMUM' Then
1548 New_References.enrollment_minimum := Column_Value;
1549 ELSIF Upper(Column_Name)='ENROLLMENT_MAXIMUM' Then
1550 New_References.enrollment_maximum := Column_Value;
1551 ELSIF Upper(Column_Name)='OVERRIDE_ENROLLMENT_MAX' Then
1552 New_References.override_enrollment_max := Column_Value;
1553 ELSIF Upper(Column_Name)='MAX_AUDITORS_ALLOWED' Then
1554 New_References.max_auditors_allowed := Column_Value;
1555 ELSIF Upper(Column_Name)='MAX_REPEAT_CREDIT_POINTS' Then
1556 New_References.max_repeat_credit_points := Column_Value;
1557 ELSIF Upper(Column_Name)='SAME_TEACH_PERIOD_REPEATS_CP' Then
1558 New_References.same_teach_period_repeats_cp:= Column_Value;
1559 ELSIF Upper(Column_Name)='WORK_LOAD_OTHER' Then
1560 New_References.work_load_other := Column_Value;
1561 ELSIF Upper(Column_Name)='CONTACT_HRS_LECTURE' Then
1562 New_References.contact_hrs_lecture := Column_Value;
1563 ELSIF Upper(Column_Name)='CONTACT_HRS_LAB' Then
1564 New_References.contact_hrs_lab := Column_Value;
1565 ELSIF Upper(Column_Name)='CONTACT_HRS_OTHER' Then
1566 New_References.contact_hrs_other := Column_Value;
1567 ELSIF Upper(Column_Name)='NON_SCHD_REQUIRED_HRS' Then
1568 New_References.non_schd_required_hrs := Column_Value;
1569 ELSIF Upper(Column_Name)='MAX_REPEATS_FOR_CREDIT' Then
1570 New_References.max_repeats_for_credit := Column_Value;
1571 ELSIF Upper(Column_Name)='MAX_REPEATS_FOR_FUNDING' Then
1572 New_References.max_repeats_for_funding := Column_Value;
1573 ELSIF Upper(Column_Name)='SAME_TEACH_PERIOD_REPEATS' Then
1574 New_References.same_teach_period_repeats := Column_Value;
1575 ELSIF Upper(Column_Name)='BILLING_CREDIT_POINTS' Then
1576 New_References.billing_credit_points := Column_Value;
1577 ELSIF Upper(Column_Name)='OVRD_WKLD_VAL_FLAG' Then
1578 New_References.ovrd_wkld_val_flag := Column_Value;
1579 ELSIF Upper(Column_Name)='BILLING_HRS' Then
1580 New_References.billing_hrs := Column_Value;
1581 END IF;
1582
1583 IF Upper(Column_Name)='ABBREVIATION' OR Column_Name IS NULL Then
1584 IF New_References.abbreviation <> UPPER(New_References.abbreviation) Then
1585 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1586 IGS_GE_MSG_STACK.ADD;
1587 App_Exception.Raise_Exception;
1588 END IF;
1589 END IF;
1590
1591 IF Upper(Column_Name)='ASSESSABLE_IND' OR Column_Name IS NULL Then
1592 IF New_References.Assessable_Ind <> UPPER(New_References.Assessable_Ind) Then
1593 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1594 IGS_GE_MSG_STACK.ADD;
1595 App_Exception.Raise_Exception;
1596 END IF;
1597
1598 IF New_References.Assessable_Ind NOT IN ( 'Y' , 'N' ) Then
1599 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1600 IGS_GE_MSG_STACK.ADD;
1601 App_Exception.Raise_Exception;
1602 END IF;
1603
1604
1605 END IF;
1606
1607
1608 IF Upper(Column_Name)='AWARD_COURSE_ONLY_IND' OR Column_Name IS NULL Then
1609 IF New_References.Award_Course_Only_Ind <> UPPER(New_References.Award_Course_Only_Ind) Then
1610 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1611 IGS_GE_MSG_STACK.ADD;
1612 App_Exception.Raise_Exception;
1613 END IF;
1614
1615 IF New_References.Award_Course_Only_Ind NOT IN ( 'Y' , 'N' ) Then
1616 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1617 IGS_GE_MSG_STACK.ADD;
1618 App_Exception.Raise_Exception;
1619 END IF;
1620
1621
1622 END IF;
1623
1624 IF Upper(Column_Name)='CREDIT_POINT_DESCRIPTOR' OR Column_Name IS NULL Then
1625 IF New_References.Credit_Point_Descriptor <> UPPER(New_References.Credit_Point_Descriptor) Then
1626 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1627 IGS_GE_MSG_STACK.ADD;
1628 App_Exception.Raise_Exception;
1629 END IF;
1630 END IF;
1631
1632 IF Upper(Column_Name)='INDUSTRIAL_IND' OR Column_Name IS NULL Then
1633 IF New_References.Industrial_Ind <> UPPER(New_References.Industrial_Ind) Then
1634 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1635 IGS_GE_MSG_STACK.ADD;
1636 App_Exception.Raise_Exception;
1637 END IF;
1638
1639 IF New_References.Industrial_Ind NOT IN ( 'Y' , 'N' ) Then
1640 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1641 IGS_GE_MSG_STACK.ADD;
1642 App_Exception.Raise_Exception;
1643 END IF;
1644
1645 END IF;
1646
1647 IF Upper(Column_Name)='POINTS_OVERRIDE_IND' OR Column_Name IS NULL Then
1648 IF New_References.Points_Override_Ind <> UPPER(New_References.Points_Override_Ind) Then
1649 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1650 IGS_GE_MSG_STACK.ADD;
1651 App_Exception.Raise_Exception;
1652 END IF;
1653
1654 IF New_References.Points_Override_Ind NOT IN ( 'Y' , 'N' ) Then
1655 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1656 IGS_GE_MSG_STACK.ADD;
1657 App_Exception.Raise_Exception;
1658 END IF;
1659 END IF;
1660
1661 IF Upper(Column_Name)='PRACTICAL_IND' OR Column_Name IS NULL Then
1662 IF New_References.Practical_Ind <> UPPER(New_References.Practical_Ind) Then
1663 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1664 IGS_GE_MSG_STACK.ADD;
1665 App_Exception.Raise_Exception;
1666 END IF;
1667
1668 IF New_References.Practical_ind NOT IN ( 'Y' , 'N' ) Then
1669 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1670 IGS_GE_MSG_STACK.ADD;
1671 App_Exception.Raise_Exception;
1672 END IF;
1673 END IF;
1674
1675 IF Upper(Column_Name)='REPEATABLE_IND' OR Column_Name IS NULL Then
1676 IF New_References.Repeatable_Ind <> UPPER(New_References.Repeatable_Ind) Then
1677 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1678 IGS_GE_MSG_STACK.ADD;
1679 App_Exception.Raise_Exception;
1680 END IF;
1681
1682 IF New_References.Repeatable_Ind NOT IN ( 'Y' , 'N','X' ) Then
1683 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1684 IGS_GE_MSG_STACK.ADD;
1685 App_Exception.Raise_Exception;
1686 END IF;
1687 END IF;
1688
1689
1690 IF Upper(Column_Name)='RESEARCH_UNIT_IND' OR Column_Name IS NULL Then
1691 IF New_References.Research_Unit_Ind <> UPPER(New_References.Research_Unit_Ind) Then
1692 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1693 IGS_GE_MSG_STACK.ADD;
1694 App_Exception.Raise_Exception;
1695 END IF;
1696
1697 IF New_References.Research_Unit_Ind NOT IN ( 'Y' , 'N' ) Then
1698 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1699 IGS_GE_MSG_STACK.ADD;
1700 App_Exception.Raise_Exception;
1701 END IF;
1702 END IF;
1703
1704
1705 IF Upper(Column_Name)='SUPP_EXAM_PERMITTED_IND' OR Column_Name IS NULL Then
1706 IF New_References.Supp_Exam_Permitted_Ind <> UPPER(New_References.Supp_Exam_Permitted_Ind) Then
1707 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1708 IGS_GE_MSG_STACK.ADD;
1709 App_Exception.Raise_Exception;
1710 END IF;
1711
1712 IF New_References.Supp_Exam_Permitted_Ind NOT IN ( 'Y' , 'N' ) Then
1713 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1714 IGS_GE_MSG_STACK.ADD;
1715 App_Exception.Raise_Exception;
1716 END IF;
1717 END IF;
1718
1719
1720 IF Upper(Column_Name)='TITLE_OVERRIDE_IND' OR Column_Name IS NULL Then
1721 IF New_References.Title_Override_Ind <> UPPER(New_References.Title_Override_Ind) Then
1722 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1723 IGS_GE_MSG_STACK.ADD;
1724 App_Exception.Raise_Exception;
1725 END IF;
1726
1727 IF New_References.Title_Override_Ind NOT IN ( 'Y' , 'N' ) Then
1728 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1729 IGS_GE_MSG_STACK.ADD;
1730 App_Exception.Raise_Exception;
1731 END IF;
1732 END IF;
1733
1734 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
1735 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
1736 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1737 IGS_GE_MSG_STACK.ADD;
1738 App_Exception.Raise_Exception;
1739 END IF;
1740 END IF;
1741
1742 IF Upper(Column_Name)='UNIT_INT_COURSE_LEVEL_CD' OR Column_Name IS NULL Then
1743 IF New_References.Unit_Int_Course_Level_Cd <> UPPER(New_References.Unit_Int_Course_Level_Cd) Then
1744 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1745 IGS_GE_MSG_STACK.ADD;
1746 App_Exception.Raise_Exception;
1747 END IF;
1748 END IF;
1749
1750 IF Upper(Column_Name)='UNIT_LEVEL' OR Column_Name IS NULL Then
1751 IF New_References.Unit_Level <> UPPER(New_References.Unit_Level) Then
1752 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1753 IGS_GE_MSG_STACK.ADD;
1754 App_Exception.Raise_Exception;
1755 END IF;
1756 END IF;
1757
1758 IF Upper(Column_Name)='UNIT_STATUS' OR Column_Name IS NULL Then
1759 IF New_References.Unit_Status <> UPPER(New_References.Unit_Status) Then
1760 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1761 IGS_GE_MSG_STACK.ADD;
1762 App_Exception.Raise_Exception;
1763 END IF;
1764 END IF;
1765
1766
1767 IF Upper(Column_Name)='ACHIEVABLE_CREDIT_POINTS' OR Column_Name IS NULL Then
1768 IF New_References.Achievable_Credit_Points < 0 OR New_References.Achievable_Credit_Points > 999.999 Then
1769 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1770 IGS_GE_MSG_STACK.ADD;
1771 App_Exception.Raise_Exception;
1772 END IF;
1773 END IF;
1774
1775 IF Upper(Column_Name)='POINTS_INCREMENT' OR Column_Name IS NULL Then
1776 IF New_References.Points_Increment < 0 OR New_References.Points_Increment > 999.999 Then
1777 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1778 IGS_GE_MSG_STACK.ADD;
1779 App_Exception.Raise_Exception;
1780 END IF;
1781 END IF;
1782
1783 IF Upper(Column_Name)='ENROLLED_CREDIT_POINTS' OR Column_Name IS NULL Then
1784 IF New_References.Enrolled_Credit_Points < 0 OR New_References.Enrolled_Credit_Points > 999.999 Then
1785 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1786 IGS_GE_MSG_STACK.ADD;
1787 App_Exception.Raise_Exception;
1788 END IF;
1789 END IF;
1790
1791 IF Upper(Column_Name)='VERSION_NUMBER' OR Column_Name IS NULL Then
1792 IF New_References.Version_Number < 1 OR New_References.Version_Number > 999 Then
1793 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1794 IGS_GE_MSG_STACK.ADD;
1795 App_Exception.Raise_Exception;
1796 END IF;
1797 END IF;
1798
1799 IF Upper(Column_Name)='POINTS_MIN' OR Column_Name IS NULL Then
1800 IF New_References.Points_Min < 0 OR New_References.Points_Min > 999.999 Then
1801 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1802 IGS_GE_MSG_STACK.ADD;
1803 App_Exception.Raise_Exception;
1804 END IF;
1805 END IF;
1806
1807 IF Upper(Column_Name)='POINTS_MAX' OR Column_Name IS NULL Then
1808 IF New_References.Points_Max < 0 OR New_References.Points_Max > 999.999 Then
1809 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1810 IGS_GE_MSG_STACK.ADD;
1811 App_Exception.Raise_Exception;
1812 END IF;
1813 END IF;
1814
1815
1816 -- Code added as per DLD Unit Section Enrollment Information
1817 -- Validating atleast one Enrollment method is checked
1818 IF (Upper(Column_Name) = 'SS_ENROL_IND' OR Upper(Column_Name)= 'IVR_ENROL_IND') OR Column_Name IS NULL THEN
1819 IF (new_references.ss_enrol_ind = 'N' AND new_references.ivr_enrol_ind = 'N') THEN
1820 Fnd_Message.Set_Name('IGS','IGS_PS_ONE_UNIT_ENR_MTHD');
1821 IGS_GE_MSG_STACK.ADD;
1822 App_Exception.Raise_Exception;
1823 END IF;
1824 END IF;
1825
1826 -- Added as part of Bug# 2636716, EN Integration.
1827 IF Upper(Column_Name)='AUDITABLE_IND' OR Column_Name IS NULL Then
1828 IF New_References.auditable_Ind <> UPPER(New_References.auditable_Ind) Then
1829 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1830 IGS_GE_MSG_STACK.ADD;
1831 App_Exception.Raise_Exception;
1832 END IF;
1833
1834 IF New_References.auditable_Ind NOT IN ( 'Y' , 'N' ) Then
1835 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1836 IGS_GE_MSG_STACK.ADD;
1837 App_Exception.Raise_Exception;
1838 END IF;
1839 END IF;
1840
1841 IF Upper(Column_Name)='AUDIT_PERMISSION_IND' OR Column_Name IS NULL Then
1842 IF New_References.audit_permission_Ind <> UPPER(New_References.audit_permission_Ind) Then
1843 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1844 IGS_GE_MSG_STACK.ADD;
1845 App_Exception.Raise_Exception;
1846 END IF;
1847
1848 IF New_References.audit_permission_Ind NOT IN ( 'Y' , 'N' ) Then
1849 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1850 IGS_GE_MSG_STACK.ADD;
1851 App_Exception.Raise_Exception;
1852 END IF;
1853 END IF;
1854
1855 -- Added as part of Bug#2702240,following checks have been added
1856 IF Upper(Column_Name)='STATE_FINANCIAL_AID' OR Column_Name IS NULL Then
1857 IF New_References.state_financial_aid <> UPPER(New_References.state_financial_aid) Then
1858 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1859 IGS_GE_MSG_STACK.ADD;
1860 App_Exception.Raise_Exception;
1861 END IF;
1862
1863 IF New_References.state_financial_aid NOT IN ( 'Y' , 'N' ) Then
1864 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1865 IGS_GE_MSG_STACK.ADD;
1866 App_Exception.Raise_Exception;
1867 END IF;
1868 END IF;
1869
1870 IF Upper(Column_Name)='FEDERAL_FINANCIAL_AID' OR Column_Name IS NULL Then
1871 IF New_References.federal_financial_aid <> UPPER(New_References.federal_financial_aid) Then
1872 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1873 IGS_GE_MSG_STACK.ADD;
1874 App_Exception.Raise_Exception;
1875 END IF;
1876
1877 IF New_References.federal_financial_aid NOT IN ( 'Y' , 'N' ) Then
1878 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1879 IGS_GE_MSG_STACK.ADD;
1880 App_Exception.Raise_Exception;
1881 END IF;
1882 END IF;
1883
1884 IF Upper(Column_Name)='INSTITUTIONAL_FINANCIAL_AID' OR Column_Name IS NULL Then
1885 IF New_References.institutional_financial_aid <> UPPER(New_References.institutional_financial_aid) Then
1886 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1887 IGS_GE_MSG_STACK.ADD;
1888 App_Exception.Raise_Exception;
1889 END IF;
1890
1891 IF New_References.institutional_financial_aid NOT IN ( 'Y' , 'N' ) Then
1892 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1893 IGS_GE_MSG_STACK.ADD;
1894 App_Exception.Raise_Exception;
1895 END IF;
1896 END IF;
1897
1898 IF Upper(Column_Name)='SAME_TEACHING_PERIOD' OR Column_Name IS NULL Then
1899 IF New_References.same_teaching_period <> UPPER(New_References.same_teaching_period) Then
1900 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1901 IGS_GE_MSG_STACK.ADD;
1902 App_Exception.Raise_Exception;
1903 END IF;
1904
1905 IF New_References.same_teaching_period NOT IN ( 'Y' , 'N' ) Then
1906 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1907 IGS_GE_MSG_STACK.ADD;
1908 App_Exception.Raise_Exception;
1909 END IF;
1910 END IF;
1911
1912 IF Upper(Column_Name)='EXCLUDE_FROM_MAX_CP_LIMIT' OR Column_Name IS NULL Then
1913 IF New_References.exclude_from_max_cp_limit <> UPPER(New_References.exclude_from_max_cp_limit) Then
1914 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1915 IGS_GE_MSG_STACK.ADD;
1916 App_Exception.Raise_Exception;
1917 END IF;
1918
1919 IF New_References.exclude_from_max_cp_limit NOT IN ( 'Y' , 'N' ) Then
1920 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1921 IGS_GE_MSG_STACK.ADD;
1922 App_Exception.Raise_Exception;
1923 END IF;
1924 END IF;
1925
1926 IF Upper(Column_Name)='RECORD_EXCLUSION_FLAG' OR Column_Name IS NULL Then
1927 IF New_References.record_exclusion_flag <> UPPER(New_References.record_exclusion_flag) Then
1928 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1929 IGS_GE_MSG_STACK.ADD;
1930 App_Exception.Raise_Exception;
1931 END IF;
1932
1933 IF New_References.record_exclusion_flag NOT IN ( 'Y' , 'N' ) Then
1934 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1935 IGS_GE_MSG_STACK.ADD;
1936 App_Exception.Raise_Exception;
1937 END IF;
1938 END IF;
1939
1940 IF Upper(Column_Name)='SS_DISPLAY_IND' OR Column_Name IS NULL Then
1941 IF New_References.ss_display_ind <> UPPER(New_References.ss_display_ind) Then
1942 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1943 IGS_GE_MSG_STACK.ADD;
1944 App_Exception.Raise_Exception;
1945 END IF;
1946
1947 IF New_References.ss_display_ind NOT IN ( 'Y' , 'N' ) Then
1948 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1949 IGS_GE_MSG_STACK.ADD;
1950 App_Exception.Raise_Exception;
1951 END IF;
1952 END IF;
1953
1954 IF Upper(Column_Name)='SPECIAL_PERMISSION_IND' OR Column_Name IS NULL Then
1955 IF New_References.special_permission_ind <> UPPER(New_References.special_permission_ind) Then
1956 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1957 IGS_GE_MSG_STACK.ADD;
1958 App_Exception.Raise_Exception;
1959 END IF;
1960
1961 IF New_References.special_permission_ind NOT IN ( 'Y' , 'N' ) Then
1962 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1963 IGS_GE_MSG_STACK.ADD;
1964 App_Exception.Raise_Exception;
1965 END IF;
1966 END IF;
1967
1968 IF Upper(Column_Name)='ANON_UNIT_GRADING_IND' OR Column_Name IS NULL Then
1969 IF New_References.anon_unit_grading_ind <> UPPER(New_References.anon_unit_grading_ind) Then
1970 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1971 IGS_GE_MSG_STACK.ADD;
1972 App_Exception.Raise_Exception;
1973 END IF;
1974
1975 IF New_References.anon_unit_grading_ind NOT IN ( 'Y' , 'N' ) Then
1976 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1977 IGS_GE_MSG_STACK.ADD;
1978 App_Exception.Raise_Exception;
1979 END IF;
1980 END IF;
1981
1982 IF Upper(Column_Name)='ANON_ASSESS_GRADING_IND' OR Column_Name IS NULL Then
1983 IF New_References.anon_assess_grading_ind <> UPPER(New_References.anon_assess_grading_ind) Then
1984 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1985 IGS_GE_MSG_STACK.ADD;
1986 App_Exception.Raise_Exception;
1987 END IF;
1988
1989 IF New_References.anon_assess_grading_ind NOT IN ( 'Y' , 'N' ) Then
1990 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1991 IGS_GE_MSG_STACK.ADD;
1992 App_Exception.Raise_Exception;
1993 END IF;
1994 END IF;
1995
1996 -- Added as a part of 2702263
1997 IF Upper(Column_Name)='CLAIMABLE_HOURS' OR Column_Name IS NULL Then
1998 IF New_References.claimable_hours < 0 OR New_References.claimable_hours > 99999.99 Then
1999 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2000 IGS_GE_MSG_STACK.ADD;
2001 App_Exception.Raise_Exception;
2002 END IF;
2003 END IF;
2004
2005 IF Upper(Column_Name)='LECTURE_CREDIT_POINTS' OR Column_Name IS NULL Then
2006 IF New_References.lecture_credit_points < 0 OR New_References.lecture_credit_points > 999.999 Then
2007 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2008 IGS_GE_MSG_STACK.ADD;
2009 App_Exception.Raise_Exception;
2010 END IF;
2011 END IF;
2012
2013 IF Upper(Column_Name)='LAB_CREDIT_POINTS' OR Column_Name IS NULL Then
2014 IF New_References.lab_credit_points < 0 OR New_References.lab_credit_points > 999.999 Then
2015 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2016 IGS_GE_MSG_STACK.ADD;
2017 App_Exception.Raise_Exception;
2018 END IF;
2019 END IF;
2020
2021 IF Upper(Column_Name)='OTHER_CREDIT_POINTS' OR Column_Name IS NULL Then
2022 IF New_References.other_credit_points < 0 OR New_References.other_credit_points > 999.999 Then
2023 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2024 IGS_GE_MSG_STACK.ADD;
2025 App_Exception.Raise_Exception;
2026 END IF;
2027 END IF;
2028
2029 IF Upper(Column_Name)='CLOCK_HOURS' OR Column_Name IS NULL Then
2030 IF New_References.clock_hours < 0 OR New_References.clock_hours > 999.999 Then
2031 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2032 IGS_GE_MSG_STACK.ADD;
2033 App_Exception.Raise_Exception;
2034 END IF;
2035 END IF;
2036
2037 IF Upper(Column_Name)='CONTINUING_EDUCATION_UNITS' OR Column_Name IS NULL Then
2038 IF New_References.continuing_education_units < 0 OR New_References.continuing_education_units > 999.999 Then
2039 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2040 IGS_GE_MSG_STACK.ADD;
2041 App_Exception.Raise_Exception;
2042 END IF;
2043 END IF;
2044
2045 IF Upper(Column_Name)='ADVANCE_MAXIMUM' OR Column_Name IS NULL Then
2046 IF New_References.advance_maximum < 0 OR New_References.advance_maximum > 999999 Then
2047 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2048 IGS_GE_MSG_STACK.ADD;
2049 App_Exception.Raise_Exception;
2050 END IF;
2051 END IF;
2052
2053 IF Upper(Column_Name)='ENROLLMENT_EXPECTED' OR Column_Name IS NULL Then
2054 IF New_References.enrollment_expected < 0 OR New_References.enrollment_expected > 999999 Then
2055 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2056 IGS_GE_MSG_STACK.ADD;
2057 App_Exception.Raise_Exception;
2058 END IF;
2059 END IF;
2060
2061 IF Upper(Column_Name)='ENROLLMENT_MINIMUM' OR Column_Name IS NULL Then
2062 IF New_References.enrollment_minimum < 0 OR New_References.enrollment_minimum > 999999 Then
2063 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2064 IGS_GE_MSG_STACK.ADD;
2065 App_Exception.Raise_Exception;
2066 END IF;
2067 END IF;
2068
2069 IF Upper(Column_Name)='ENROLLMENT_MAXIMUM' OR Column_Name IS NULL Then
2070 IF New_References.enrollment_maximum < 0 OR New_References.enrollment_maximum > 999999 Then
2071 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2072 IGS_GE_MSG_STACK.ADD;
2073 App_Exception.Raise_Exception;
2074 END IF;
2075 END IF;
2076
2077 IF Upper(Column_Name)='OVERRIDE_ENROLLMENT_MAX' OR Column_Name IS NULL Then
2078 IF New_References.override_enrollment_max < 0 OR New_References.override_enrollment_max > 999999 Then
2079 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2080 IGS_GE_MSG_STACK.ADD;
2081 App_Exception.Raise_Exception;
2082 END IF;
2083 END IF;
2084
2085 IF Upper(Column_Name)='MAX_AUDITORS_ALLOWED' OR Column_Name IS NULL Then
2086 IF New_References.max_auditors_allowed < 1 OR New_References.max_auditors_allowed > 999999 Then
2087 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2088 IGS_GE_MSG_STACK.ADD;
2089 App_Exception.Raise_Exception;
2090 END IF;
2091 END IF;
2092
2093 IF Upper(Column_Name)='WORK_LOAD_CP_LECTURE' OR Column_Name IS NULL Then
2094 IF New_References.work_load_cp_lecture < 0 OR New_References.work_load_cp_lecture > 999.99 Then
2095 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2096 IGS_GE_MSG_STACK.ADD;
2097 App_Exception.Raise_Exception;
2098 END IF;
2099 END IF;
2100
2101 IF Upper(Column_Name)='WORK_LOAD_CP_LAB' OR Column_Name IS NULL Then
2102 IF New_References.work_load_cp_lab < 0 OR New_References.work_load_cp_lab > 999.99 Then
2103 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2104 IGS_GE_MSG_STACK.ADD;
2105 App_Exception.Raise_Exception;
2106 END IF;
2107 END IF;
2108
2109 IF Upper(Column_Name)='MAX_REPEAT_CREDIT_POINTS' OR Column_Name IS NULL Then
2110 IF New_References.max_repeat_credit_points < 0 OR New_References.max_repeat_credit_points > 999.999 Then
2111 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2112 IGS_GE_MSG_STACK.ADD;
2113 App_Exception.Raise_Exception;
2114 END IF;
2115 END IF;
2116
2117 IF Upper(Column_Name)='SAME_TEACH_PERIOD_REPEATS_CP' OR Column_Name IS NULL Then
2118 IF New_References.same_teach_period_repeats_cp < 0 OR New_References.same_teach_period_repeats_cp > 999.999 Then
2119 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2120 IGS_GE_MSG_STACK.ADD;
2121 App_Exception.Raise_Exception;
2122 END IF;
2123 END IF;
2124
2125 IF Upper(Column_Name)='WORK_LOAD_OTHER' OR Column_Name IS NULL Then
2126 IF New_References.work_load_other < 0 OR New_References.WORK_LOAD_OTHER > 999.99 Then
2127 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2128 IGS_GE_MSG_STACK.ADD;
2129 App_Exception.Raise_Exception;
2130 END IF;
2131 END IF;
2132
2133 IF Upper(Column_Name)='CONTACT_HRS_LECTURE' OR Column_Name IS NULL Then
2134 IF New_References.contact_hrs_lecture < 0 OR New_References.contact_hrs_lecture > 999.99 Then
2135 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2136 IGS_GE_MSG_STACK.ADD;
2137 App_Exception.Raise_Exception;
2138 END IF;
2139 END IF;
2140
2141 IF Upper(Column_Name)='CONTACT_HRS_LAB' OR Column_Name IS NULL Then
2142 IF New_References.contact_hrs_lab < 0 OR New_References.contact_hrs_lab > 999.99 Then
2143 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2144 IGS_GE_MSG_STACK.ADD;
2145 App_Exception.Raise_Exception;
2146 END IF;
2147 END IF;
2148
2149 IF Upper(Column_Name)='CONTACT_HRS_OTHER' OR Column_Name IS NULL Then
2150 IF New_References.contact_hrs_other < 0 OR New_References.contact_hrs_other > 999.99 Then
2151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2152 IGS_GE_MSG_STACK.ADD;
2153 App_Exception.Raise_Exception;
2154 END IF;
2155 END IF;
2156
2157 IF Upper(Column_Name)='NON_SCHD_REQUIRED_HRS' OR Column_Name IS NULL Then
2158 IF New_References.non_schd_required_hrs < 0 OR New_References.non_schd_required_hrs > 999.99 Then
2159 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2160 IGS_GE_MSG_STACK.ADD;
2161 App_Exception.Raise_Exception;
2162 END IF;
2163 END IF;
2164
2165 IF Upper(Column_Name)='MAX_REPEATS_FOR_CREDIT' OR Column_Name IS NULL Then
2166 IF New_References.max_repeats_for_credit < 0 OR New_References.max_repeats_for_credit > 999 Then
2167 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2168 IGS_GE_MSG_STACK.ADD;
2169 App_Exception.Raise_Exception;
2170 END IF;
2171 END IF;
2172
2173 IF Upper(Column_Name)='MAX_REPEATS_FOR_FUNDING' OR Column_Name IS NULL Then
2174 IF New_References.max_repeats_for_funding < 0 OR New_References.max_repeats_for_funding > 999 Then
2175 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2176 IGS_GE_MSG_STACK.ADD;
2177 App_Exception.Raise_Exception;
2178 END IF;
2179 END IF;
2180
2181 IF Upper(Column_Name)='SAME_TEACH_PERIOD_REPEATS' OR Column_Name IS NULL Then
2182 IF New_References.same_teach_period_repeats < 0 OR New_References.same_teach_period_repeats > 999 Then
2183 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2184 IGS_GE_MSG_STACK.ADD;
2185 App_Exception.Raise_Exception;
2186 END IF;
2187 END IF;
2188
2189 IF Upper(Column_Name)='BILLING_CREDIT_POINTS' OR Column_Name IS NULL Then
2190 IF New_References.billing_credit_points < 0 OR New_References.billing_credit_points > 999.999 Then
2191 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2192 IGS_GE_MSG_STACK.ADD;
2193 App_Exception.Raise_Exception;
2194 END IF;
2195 END IF;
2196
2197 IF Upper(Column_Name)='BILLING_HRS' OR Column_Name IS NULL Then
2198 IF New_References.billing_hrs < 0 OR New_References.billing_hrs > 999.999 Then
2199 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2200 IGS_GE_MSG_STACK.ADD;
2201 App_Exception.Raise_Exception;
2202 END IF;
2203 END IF;
2204
2205 IF Upper(Column_Name)='OVRD_WKLD_VAL_FLAG' OR Column_Name IS NULL Then
2206 IF New_References.ovrd_wkld_val_flag NOT IN ( 'Y' , 'N' ) Then
2207 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
2208 IGS_GE_MSG_STACK.ADD;
2209 App_Exception.Raise_Exception;
2210 END IF;
2211 END IF;
2212
2213
2214 END Check_Constraints;
2215 ----------------------------------------------------------------------
2216 PROCEDURE Check_Parent_Existance AS
2217 BEGIN
2218
2219 -- Merged with fnd Lookups
2220 /* IF (((old_references.credit_point_descriptor = new_references.credit_point_descriptor)) OR
2221 ((new_references.credit_point_descriptor IS NULL))) THEN
2222 NULL;
2223 ELSE
2224 IF NOT IGS_PS_CR_PT_DSCR_PKG.Get_PK_For_Validation (
2225 new_references.credit_point_descriptor) THEN
2226 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2227 IGS_GE_MSG_STACK.ADD;
2228 App_Exception.Raise_Exception;
2229 END IF;
2230
2231
2232 END IF; */
2233
2234 IF (((old_references.owner_org_unit_cd = new_references.owner_org_unit_cd) AND
2235 (old_references.owner_ou_start_dt = new_references.owner_ou_start_dt)) OR
2236 ((new_references.owner_org_unit_cd IS NULL) OR
2237 (new_references.owner_ou_start_dt IS NULL))) THEN
2238 NULL;
2239 ELSE
2240 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
2241 new_references.owner_org_unit_cd,
2242 new_references.owner_ou_start_dt) THEN
2243
2244 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2245 IGS_GE_MSG_STACK.ADD;
2246 App_Exception.Raise_Exception;
2247 END IF;
2248
2249
2250 END IF;
2251
2252 IF (((old_references.coord_person_id = new_references.coord_person_id)) OR
2253 ((new_references.coord_person_id IS NULL))) THEN
2254 NULL;
2255 ELSE
2256 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
2257 new_references.coord_person_id) THEN
2258
2259 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2260 IGS_GE_MSG_STACK.ADD;
2261 App_Exception.Raise_Exception;
2262 END IF;
2263
2264
2265 END IF;
2266
2267 IF (((old_references.unit_int_course_level_cd = new_references.unit_int_course_level_cd)) OR
2268 ((new_references.unit_int_course_level_cd IS NULL))) THEN
2269 NULL;
2270 ELSE
2271 IF NOT IGS_PS_UNIT_INT_LVL_PKG.Get_PK_For_Validation (
2272 new_references.unit_int_course_level_cd) THEN
2273
2274 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2275 IGS_GE_MSG_STACK.ADD;
2276 App_Exception.Raise_Exception;
2277 END IF;
2278
2279
2280 END IF;
2281
2282 IF (((old_references.unit_level = new_references.unit_level)) OR
2283 ((new_references.unit_level IS NULL))) THEN
2284 NULL;
2285 ELSE
2286 IF NOT IGS_PS_UNIT_LEVEL_PKG.Get_PK_For_Validation (
2287 new_references.unit_level) THEN
2288
2289 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2290 IGS_GE_MSG_STACK.ADD;
2291 App_Exception.Raise_Exception;
2292 END IF;
2293
2294
2295 END IF;
2296
2297 IF (((old_references.unit_cd = new_references.unit_cd)) OR
2298 ((new_references.unit_cd IS NULL))) THEN
2299 NULL;
2300 ELSE
2301 IF NOT IGS_PS_UNIT_PKG.Get_PK_For_Validation (
2302 new_references.unit_cd) THEN
2303
2304 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2305 IGS_GE_MSG_STACK.ADD;
2306 App_Exception.Raise_Exception;
2307 END IF;
2308
2309
2310 END IF;
2311
2312 IF (((old_references.unit_status = new_references.unit_status)) OR
2313 ((new_references.unit_status IS NULL))) THEN
2314 NULL;
2315 ELSE
2316 IF NOT IGS_PS_UNIT_STAT_PKG.Get_PK_For_Validation (
2317 new_references.unit_status) THEN
2318
2319 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2320 IGS_GE_MSG_STACK.ADD;
2321 App_Exception.Raise_Exception;
2322 END IF;
2323
2324
2325 END IF;
2326 IF (((old_references.curriculum_id = new_references.curriculum_id)) OR
2327 ((new_references.curriculum_id IS NULL))) THEN
2328 NULL;
2329 ELSIF NOT igs_ps_unt_crclm_pkg.get_pk_for_validation (
2330 new_references.curriculum_id
2331 ) THEN
2332 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
2333 igs_ge_msg_stack.add;
2334 app_exception.raise_exception;
2335 END IF;
2336
2337 IF (((old_references.subtitle_id = new_references.subtitle_id)) OR
2338 ((new_references.subtitle_id IS NULL))) THEN
2339 NULL;
2340 ELSIF NOT igs_ps_unit_subtitle_pkg.get_pk_for_validation (
2341 new_references.subtitle_id
2342 ) THEN
2343 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
2344 igs_ge_msg_stack.add;
2345 app_exception.raise_exception;
2346 END IF;
2347
2348 IF (((old_references.rpt_fmly_id = new_references.rpt_fmly_id)) OR
2349 ((new_references.rpt_fmly_id IS NULL))) THEN
2350 NULL;
2351 ELSIF NOT igs_ps_rpt_fmly_pkg.get_pk_for_validation (
2352 new_references.rpt_fmly_id
2353 ) THEN
2354 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
2355 igs_ge_msg_stack.add;
2356 app_exception.raise_exception;
2357 END IF;
2358
2359 IF (((old_references.unit_type_id = new_references.unit_type_id)) OR
2360 ((new_references.unit_type_id IS NULL))) THEN
2361 NULL;
2362 ELSIF NOT igs_ps_unit_type_lvl_pkg.get_pk_for_validation (
2363 new_references.unit_type_id
2364 ) THEN
2365 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
2366 igs_ge_msg_stack.add;
2367 app_exception.raise_exception;
2368 END IF;
2369
2370 IF (((old_references.cal_type_enrol_load_cal = new_references.cal_type_enrol_load_cal) AND
2371 (old_references.sequence_num_enrol_load_cal = new_references.sequence_num_enrol_load_cal)) OR
2372 ((new_references.cal_type_enrol_load_cal IS NULL) OR
2373 (new_references.sequence_num_enrol_load_cal IS NULL))) THEN
2374 NULL;
2375 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
2376 new_references.cal_type_enrol_load_cal,
2377 new_references.sequence_num_enrol_load_cal
2378 ) THEN
2379 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
2380 igs_ge_msg_stack.add;
2381 app_exception.raise_exception;
2382 END IF;
2383
2384 IF (((old_references.cal_type_offer_load_cal = new_references.cal_type_offer_load_cal) AND
2385 (old_references.sequence_num_offer_load_cal = new_references.sequence_num_offer_load_cal)) OR
2386 ((new_references.cal_type_offer_load_cal IS NULL) OR
2387 (new_references.sequence_num_offer_load_cal IS NULL))) THEN
2388 NULL;
2389 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
2390 new_references.cal_type_offer_load_cal,
2391 new_references.sequence_num_offer_load_cal
2392 ) THEN
2393 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
2394 igs_ge_msg_stack.add;
2395 app_exception.raise_exception;
2396 END IF;
2397
2398 IF ((old_references.rev_account_cd = new_references.rev_account_cd) OR
2399 (new_references.rev_account_cd IS NULL)) THEN
2400 NULL;
2401 ELSE
2402 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
2403 new_references.rev_account_cd
2404 ) THEN
2405 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2406 IGS_GE_MSG_STACK.ADD;
2407 App_Exception.Raise_Exception;
2408 END IF;
2409 END IF;
2410
2411 END Check_Parent_Existance;
2412
2413 PROCEDURE Check_Child_Existance AS
2414
2415 -- Who When What
2416 --vvutukur 09-Jun-2003 Enh#2831572.Financial Accounting Build.Added call to igs_fi_ftci_accts_pkg.get_fk_igs_ps_ver.
2417 -- smadathi 01-Feb-2002 Added igf_sp_unit.get_fk_igs_ps_unit_ver and
2418 -- igf_sp_std_unit.get_fk_igs_ps_unit_ver as per enhancement bug 2154941
2419 -- smadathi 03-JUL-2001 added igs_en_elgb_ovr_step_pkg.get_fk_igs_ps_unit_ver as per enhancement bug no. 1830175
2420 --svenkata 02-06-2003 Modified to remove references to TBH of pkg IGS_EN_ELGB_OVR_STEP_PKG. Instead , added
2421 -- references to package IGS_EN_ELGB_OVR_UOO.Bug #2829272
2422 -- SMADATHI 29-MAY-2001 removed foreign key references to IGS_PS_UNT_REPT_FMLY , IGS_PS_UNT_PRV_GRADE as per DLD
2423 -- SMADATHI 25-MAY-2001 removed foreign key references to IGS_PS_USEC_RPT_FMLY as per DLD
2424 -- pmarada 15-feb-2002 Addedcheckchaild exist for the HESA requirment .
2425
2426 BEGIN
2427
2428 igs_fi_ftci_accts_pkg.get_fk_igs_ps_unit_ver(
2429 old_references.unit_cd,
2430 old_references.version_number);
2431
2432 IGS_AD_PS_APLINSTUNT_PKG.GET_FK_IGS_PS_UNIT_VER (
2433 old_references.unit_cd,
2434 old_references.version_number
2435 );
2436
2437 IGS_AV_STND_ALT_UNIT_PKG.GET_FK_IGS_PS_UNIT_VER (
2438 old_references.unit_cd,
2439 old_references.version_number
2440 );
2441
2442 IGS_AV_STND_UNIT_PKG.GET_FK_IGS_PS_UNIT_VER (
2443 old_references.unit_cd,
2444 old_references.version_number
2445 );
2446
2447 IGS_CO_ITM_PKG.GET_FK_IGS_PS_UNIT_VER (
2448 old_references.unit_cd,
2449 old_references.version_number
2450 );
2451
2452 IGS_PS_ANL_LOAD_U_LN_PKG.GET_FK_IGS_PS_UNIT_VER (
2453 old_references.unit_cd,
2454 old_references.version_number
2455 );
2456
2457 IGS_PS_UNIT_LVL_PKG.GET_FK_IGS_PS_UNIT_VER (
2458 old_references.unit_cd,
2459 old_references.version_number
2460 );
2461
2462 IGS_PS_UNIT_LVL_HIST_PKG.GET_FK_IGS_PS_UNIT_VER (
2463 old_references.unit_cd,
2464 old_references.version_number
2465 );
2466
2467 IGS_CO_OU_CO_REF_PKG.GET_FK_IGS_PS_UNIT_VER (
2468 old_references.unit_cd,
2469 old_references.version_number
2470 );
2471
2472 IGS_EN_SU_ATTEMPT_PKG.GET_FK_IGS_PS_UNIT_VER (
2473 old_references.unit_cd,
2474 old_references.version_number
2475 );
2476
2477 IGS_EN_SU_ATTEMPT_PKG.GET_FK_IGS_PS_UNIT_VER (
2478 old_references.unit_cd,
2479 old_references.version_number
2480 );
2481
2482 IGS_EN_ELGB_OVR_UOO_PKG.GET_FK_IGS_PS_UNIT_VER (
2483 old_references.unit_cd,
2484 old_references.version_number
2485 );
2486
2487 IGS_PS_TCH_RESP_PKG.GET_FK_IGS_PS_UNIT_VER (
2488 old_references.unit_cd,
2489 old_references.version_number
2490 );
2491
2492 IGS_PS_TCH_RESP_HIST_PKG.GET_FK_IGS_PS_UNIT_VER (
2493 old_references.unit_cd,
2494 old_references.version_number
2495 );
2496
2497 IGS_PS_UNIT_CATEGORY_PKG.GET_FK_IGS_PS_UNIT_VER (
2498 old_references.unit_cd,
2499 old_references.version_number
2500 );
2501
2502 IGS_PS_UNIT_DSCP_PKG.GET_FK_IGS_PS_UNIT_VER (
2503 old_references.unit_cd,
2504 old_references.version_number
2505 );
2506
2507 IGS_PS_UNT_DSCP_HIST_PKG.GET_FK_IGS_PS_UNIT_VER (
2508 old_references.unit_cd,
2509 old_references.version_number
2510 );
2511
2512 IGS_FI_UNIT_FEE_TRG_PKG.GET_FK_IGS_PS_UNIT_VER (
2513 old_references.unit_cd,
2514 old_references.version_number
2515 );
2516
2517 IGS_PS_UNIT_OFR_PKG.GET_FK_IGS_PS_UNIT_VER (
2518 old_references.unit_cd,
2519 old_references.version_number
2520 );
2521
2522 IGS_PS_UNIT_REF_CD_PKG.GET_FK_IGS_PS_UNIT_VER (
2523 old_references.unit_cd,
2524 old_references.version_number
2525 );
2526
2527 IGS_PS_UNIT_REF_HIST_PKG.GET_FK_IGS_PS_UNIT_VER (
2528 old_references.unit_cd,
2529 old_references.version_number
2530 );
2531
2532 IGS_PS_UNIT_VER_NOTE_PKG.GET_FK_IGS_PS_UNIT_VER (
2533 old_references.unit_cd,
2534 old_references.version_number
2535 );
2536
2537 IGS_PS_UNIT_VER_RU_PKG.GET_FK_IGS_PS_UNIT_VER (
2538 old_references.unit_cd,
2539 old_references.version_number
2540 );
2541
2542 IGS_PS_UNIT_GRD_SCHM_PKG.get_fk_igs_ps_unit_ver(
2543 old_references.unit_cd,
2544 old_references.version_number
2545 );
2546
2547 IGS_PS_UNIT_OFR_MODE_PKG.get_fk_igs_ps_unit_ver(
2548 old_references.unit_cd,
2549 old_references.version_number);
2550
2551 IGS_PS_UNIT_FLD_STDY_PKG.get_fk_igs_ps_unit_ver(
2552 old_references.unit_cd,
2553 old_references.version_number);
2554
2555 IGS_PS_UNIT_LOCATION_PKG.get_fk_igs_ps_unit_ver(
2556 old_references.unit_cd,
2557 old_references.version_number);
2558
2559 IGS_PS_UNIT_FACILITY_PKG.get_fk_igs_ps_unit_ver(
2560 old_references.unit_cd,
2561 old_references.version_number);
2562
2563 IGS_PS_FACLTY_DISP_PKG.get_fk_igs_ps_unit_ver(
2564 old_references.unit_cd);
2565
2566 IGS_PS_UNIT_SUBTITLE_PKG.get_fk_igs_ps_unit_ver(
2567 old_references.unit_cd,
2568 old_references.version_number);
2569
2570 igs_ps_unit_x_grpmem_pkg.get_fk_igs_ps_unit_ver(
2571 old_references.unit_cd,
2572 old_references.version_number);
2573
2574 igs_ps_unit_accts_pkg.get_fk_igs_ps_unit_ver(
2575 old_references.unit_cd,
2576 old_references.version_number);
2577
2578
2579 igf_sp_unit_pkg.get_fk_igs_ps_unit_ver(
2580 old_references.unit_cd,
2581 old_references.version_number);
2582
2583 igf_sp_std_unit_pkg.get_fk_igs_ps_unit_ver(
2584 old_references.unit_cd,
2585 old_references.version_number);
2586
2587 -- Added the following check chaild existance for the HESA requirment, pmarada
2588 -- smaddali removed the Execute immediate statement and calling packages directly, bug#3306063
2589 igs_he_st_unt_vs_all_pkg.get_fk_igs_ps_unit_ver_all(x_unit_cd => old_references.unit_cd,
2590 x_version_number => old_references.version_number);
2591
2592 END Check_Child_Existance;
2593 --------------------------------------------------------------
2594 FUNCTION Get_PK_For_Validation (
2595 x_unit_cd IN VARCHAR2,
2596 x_version_number IN NUMBER
2597 ) RETURN BOOLEAN AS
2598
2599 -- Bug#2416978, Depending on the unit status lock on the table is acquired
2600 -- lock is required when the unit status is Planned since it is allowed to be delete
2601 -- lock is not required when the unit status is non planned so an explicit lock is not required
2602 -- opening different cursors depending on the unit status
2603
2604 CURSOR cur_get_status IS
2605 SELECT us.s_unit_status
2606 FROM igs_ps_unit_ver_all uv,
2607 igs_ps_unit_stat us
2608 WHERE uv.unit_status=us.unit_status
2609 AND uv.unit_cd = x_unit_cd
2610 AND uv.version_number = x_version_number;
2611 l_unit_status igs_ps_unit_stat.s_unit_status%TYPE;
2612
2613 CURSOR cur_rowid_planned IS
2614 SELECT ROWID
2615 FROM igs_ps_unit_ver_all
2616 WHERE unit_cd = x_unit_cd
2617 AND version_number = x_version_number
2618 FOR UPDATE NOWAIT;
2619
2620 CURSOR cur_rowid_non_planned IS
2621 SELECT ROWID
2622 FROM igs_ps_unit_ver_all
2623 WHERE unit_cd = x_unit_cd
2624 AND version_number = x_version_number;
2625
2626 lv_rowid cur_rowid_planned%ROWTYPE;
2627
2628 BEGIN
2629
2630 OPEN cur_get_status;
2631 FETCH cur_get_status INTO l_unit_status;
2632 IF cur_get_status%NOTFOUND THEN
2633 CLOSE cur_get_status;
2634 RETURN(FALSE);
2635 ELSE
2636 CLOSE cur_get_status;
2637 IF l_unit_status = 'PLANNED' THEN
2638 OPEN cur_rowid_planned;
2639 FETCH cur_rowid_planned INTO lv_rowid;
2640 IF cur_rowid_planned%FOUND THEN
2641 CLOSE cur_rowid_planned;
2642 RETURN (TRUE);
2643 ELSE
2644 CLOSE cur_rowid_planned;
2645 RETURN (FALSE);
2646 END IF;
2647 ELSE
2648 OPEN cur_rowid_non_planned;
2649 FETCH cur_rowid_non_planned INTO lv_rowid;
2650 IF cur_rowid_non_planned%FOUND THEN
2651 CLOSE cur_rowid_non_planned;
2652 RETURN (TRUE);
2653 ELSE
2654 CLOSE cur_rowid_non_planned;
2655 RETURN (FALSE);
2656 END IF;
2657 END IF;
2658 END IF;
2659
2660 END Get_PK_For_Validation;
2661 --------------------------------------------------------------------
2662 PROCEDURE GET_FK_IGS_PS_CR_PT_DSCR (
2663 x_credit_point_descriptor IN VARCHAR2
2664 ) AS
2665
2666 CURSOR cur_rowid IS
2667 SELECT rowid
2668 FROM IGS_PS_UNIT_VER_ALL
2669 WHERE credit_point_descriptor = x_credit_point_descriptor ;
2670
2671 lv_rowid cur_rowid%RowType;
2672
2673 BEGIN
2674
2675 Open cur_rowid;
2676 Fetch cur_rowid INTO lv_rowid;
2677 IF (cur_rowid%FOUND) THEN
2678 Close cur_rowid;
2679 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UV_CPD_FK');
2680 IGS_GE_MSG_STACK.ADD;
2681 App_Exception.Raise_Exception;
2682 Return;
2683 END IF;
2684 Close cur_rowid;
2685
2686 END GET_FK_IGS_PS_CR_PT_DSCR;
2687 ---------------------------------------------------------------
2688 PROCEDURE GET_FK_IGS_OR_UNIT (
2689 x_org_unit_cd IN VARCHAR2,
2690 x_start_dt IN DATE
2691 ) AS
2692
2693 CURSOR cur_rowid IS
2694 SELECT rowid
2695 FROM IGS_PS_UNIT_VER_ALL
2696 WHERE owner_org_unit_cd = x_org_unit_cd
2697 AND owner_ou_start_dt = x_start_dt ;
2698
2699 lv_rowid cur_rowid%RowType;
2700
2701 BEGIN
2702
2703 Open cur_rowid;
2704 Fetch cur_rowid INTO lv_rowid;
2705 IF (cur_rowid%FOUND) THEN
2706 Close cur_rowid;
2707 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UV_OU_FK');
2708 IGS_GE_MSG_STACK.ADD;
2709 App_Exception.Raise_Exception;
2710 Return;
2711 END IF;
2712 Close cur_rowid;
2713
2714 END GET_FK_IGS_OR_UNIT;
2715 ---------------------------------------------------------------
2716 PROCEDURE GET_FK_IGS_PE_PERSON (
2717 x_person_id IN NUMBER
2718 ) AS
2719
2720 CURSOR cur_rowid IS
2721 SELECT rowid
2722 FROM IGS_PS_UNIT_VER_ALL
2723 WHERE coord_person_id = x_person_id ;
2724
2725 lv_rowid cur_rowid%RowType;
2726
2727 BEGIN
2728
2729 Open cur_rowid;
2730 Fetch cur_rowid INTO lv_rowid;
2731 IF (cur_rowid%FOUND) THEN
2732 Close cur_rowid;
2733 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UV_PE_FK');
2734 IGS_GE_MSG_STACK.ADD;
2735 App_Exception.Raise_Exception;
2736 Return;
2737 END IF;
2738 Close cur_rowid;
2739
2740 END GET_FK_IGS_PE_PERSON;
2741 ---------------------------------------------------------------
2742 PROCEDURE GET_FK_IGS_PS_UNIT_INT_LVL (
2743 x_unit_int_course_level_cd IN VARCHAR2
2744 ) AS
2745
2746 CURSOR cur_rowid IS
2747 SELECT rowid
2748 FROM IGS_PS_UNIT_VER_ALL
2749 WHERE unit_int_course_level_cd = x_unit_int_course_level_cd ;
2750
2751 lv_rowid cur_rowid%RowType;
2752
2753 BEGIN
2754
2755 Open cur_rowid;
2756 Fetch cur_rowid INTO lv_rowid;
2757 IF (cur_rowid%FOUND) THEN
2758 Close cur_rowid;
2759 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UV_UICL_FK');
2760 IGS_GE_MSG_STACK.ADD;
2761 App_Exception.Raise_Exception;
2762 Return;
2763 END IF;
2764 Close cur_rowid;
2765
2766 END GET_FK_IGS_PS_UNIT_INT_LVL;
2767 ---------------------------------------------------------------
2768 PROCEDURE GET_FK_IGS_PS_UNIT (
2769 x_unit_cd IN VARCHAR2
2770 ) AS
2771
2772 CURSOR cur_rowid IS
2773 SELECT rowid
2774 FROM IGS_PS_UNIT_VER_ALL
2775 WHERE unit_cd = x_unit_cd ;
2776
2777 lv_rowid cur_rowid%RowType;
2778
2779 BEGIN
2780
2781 Open cur_rowid;
2782 Fetch cur_rowid INTO lv_rowid;
2783 IF (cur_rowid%FOUND) THEN
2784 Close cur_rowid;
2785 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UV_UN_FK');
2786 IGS_GE_MSG_STACK.ADD;
2787 App_Exception.Raise_Exception;
2788 Return;
2789 END IF;
2790 Close cur_rowid;
2791
2792 END GET_FK_IGS_PS_UNIT;
2793 ---------------------------------------------------------------
2794 PROCEDURE GET_FK_IGS_PS_UNIT_STAT (
2795 x_unit_status IN VARCHAR2
2796 ) AS
2797
2798 CURSOR cur_rowid IS
2799 SELECT rowid
2800 FROM IGS_PS_UNIT_VER_ALL
2801 WHERE unit_status = x_unit_status ;
2802
2803 lv_rowid cur_rowid%RowType;
2804
2805 BEGIN
2806
2807 Open cur_rowid;
2808 Fetch cur_rowid INTO lv_rowid;
2809 IF (cur_rowid%FOUND) THEN
2810 Close cur_rowid;
2811 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UV_UST_FK');
2812 IGS_GE_MSG_STACK.ADD;
2813 App_Exception.Raise_Exception;
2814 Return;
2815 END IF;
2816 Close cur_rowid;
2817
2818 END GET_FK_IGS_PS_UNIT_STAT;
2819 ---------------------------------------------------------------
2820 PROCEDURE get_fk_igs_ps_rpt_fmly_all (
2821 x_rpt_fmly_id IN NUMBER
2822 ) AS
2823 /*
2824 || Created By : apelleti
2825 || Created On : 17-MAY-2001
2826 || Purpose : Validates the Foreign Keys for the table.
2827 || Known limitations, enhancements or remarks :
2828 || Change History :
2829 || Who When What
2830 || (reverse chronological order - newest change first)
2831 */
2832 CURSOR cur_rowid IS
2833 SELECT rowid
2834 FROM igs_ps_unit_ver_all
2835 WHERE ((rpt_fmly_id = x_rpt_fmly_id));
2836
2837 lv_rowid cur_rowid%RowType;
2838
2839 BEGIN
2840
2841 OPEN cur_rowid;
2842 FETCH cur_rowid INTO lv_rowid;
2843 IF (cur_rowid%FOUND) THEN
2844 CLOSE cur_rowid;
2845 fnd_message.set_name ('IGS', 'IGS_PS_UV_UCUR_FK');
2846 igs_ge_msg_stack.add;
2847 app_exception.raise_exception;
2848 RETURN;
2849 END IF;
2850 CLOSE cur_rowid;
2851
2852 END get_fk_igs_ps_rpt_fmly_all;
2853 ---------------------------------------------------------------
2854 PROCEDURE get_fk_igs_ps_unit_subtitle (
2855 x_subtitle_id IN NUMBER
2856 ) AS
2857 /*
2858 || Created By : apelleti
2859 || Created On : 17-MAY-2001
2860 || Purpose : Validates the Foreign Keys for the table.
2861 || Known limitations, enhancements or remarks :
2862 || Change History :
2863 || Who When What
2864 || (reverse chronological order - newest change first)
2865 */
2866 CURSOR cur_rowid IS
2867 SELECT rowid
2868 FROM igs_ps_unit_ver_all
2869 WHERE ((subtitle_id = x_subtitle_id));
2870
2871 lv_rowid cur_rowid%RowType;
2872
2873 BEGIN
2874
2875 OPEN cur_rowid;
2876 FETCH cur_rowid INTO lv_rowid;
2877 IF (cur_rowid%FOUND) THEN
2878 CLOSE cur_rowid;
2879 fnd_message.set_name ('IGS', 'IGS_PS_UV_UVSB_FK');
2880 igs_ge_msg_stack.add;
2881 app_exception.raise_exception;
2882 RETURN;
2883 END IF;
2884 CLOSE cur_rowid;
2885
2886 END get_fk_igs_ps_unit_subtitle;
2887 ---------------------------------------------------------------
2888 PROCEDURE get_fk_igs_ps_unt_crclm_all (
2889 x_curriculum_id IN VARCHAR2
2890 ) AS
2891 /*
2892 || Created By : apelleti
2893 || Created On : 17-MAY-2001
2894 || Purpose : Validates the Foreign Keys for the table.
2895 || Known limitations, enhancements or remarks :
2896 || Change History :
2897 || Who When What
2898 || (reverse chronological order - newest change first)
2899 */
2900 CURSOR cur_rowid IS
2901 SELECT rowid
2902 FROM igs_ps_unit_ver_all
2903 WHERE ((curriculum_id = x_curriculum_id));
2904
2905 lv_rowid cur_rowid%RowType;
2906
2907 BEGIN
2908
2909 OPEN cur_rowid;
2910 FETCH cur_rowid INTO lv_rowid;
2911 IF (cur_rowid%FOUND) THEN
2912 CLOSE cur_rowid;
2913 fnd_message.set_name ('IGS', 'IGS_PS_UV_CRCLM_FK');
2914 igs_ge_msg_stack.add;
2915 app_exception.raise_exception;
2916 RETURN;
2917 END IF;
2918 CLOSE cur_rowid;
2919
2920 END get_fk_igs_ps_unt_crclm_all;
2921 ---------------------------------------------------------------
2922
2923 PROCEDURE get_fk_igs_ca_inst_all (
2924 x_cal_type IN VARCHAR2,
2925 x_sequence_number IN NUMBER
2926 ) AS
2927 /*
2928 || Created By : apelleti
2929 || Created On : 17-MAY-2001
2930 || Purpose : Validates the Foreign Keys for the table.
2931 || Known limitations, enhancements or remarks :
2932 || Change History :
2933 || Who When What
2934 || (reverse chronological order - newest change first)
2935 */
2936 CURSOR cur_rowid IS
2937 SELECT rowid
2938 FROM igs_ps_unit_ver_all
2939 WHERE (cal_type_enrol_load_cal = x_cal_type and
2940 sequence_num_enrol_load_cal = x_sequence_number);
2941
2942 lv_rowid cur_rowid%RowType;
2943
2944 BEGIN
2945
2946 OPEN cur_rowid;
2947 FETCH cur_rowid INTO lv_rowid;
2948 IF (cur_rowid%FOUND) THEN
2949 CLOSE cur_rowid;
2950 fnd_message.set_name ('IGS', 'IGS_PS_UV_CAI_FK');
2951 igs_ge_msg_stack.add;
2952 app_exception.raise_exception;
2953 RETURN;
2954 END IF;
2955 CLOSE cur_rowid;
2956
2957 END get_fk_igs_ca_inst_all;
2958
2959
2960 PROCEDURE get_fk_igs_ca_inst_all1 (
2961 x_cal_type IN VARCHAR2,
2962 x_sequence_number IN NUMBER
2963 ) AS
2964 /*
2965 || Created By : apelleti
2966 || Created On : 17-MAY-2001
2967 || Purpose : Validates the Foreign Keys for the table.
2968 || Known limitations, enhancements or remarks :
2969 || Change History :
2970 || Who When What
2971 || (reverse chronological order - newest change first)
2972 */
2973 CURSOR cur_rowid IS
2974 SELECT rowid
2975 FROM igs_ps_unit_ver_all
2976 WHERE (cal_type_offer_load_cal = x_cal_type and
2977 sequence_num_offer_load_cal = x_sequence_number);
2978
2979 lv_rowid cur_rowid%RowType;
2980
2981 BEGIN
2982
2983 OPEN cur_rowid;
2984 FETCH cur_rowid INTO lv_rowid;
2985 IF (cur_rowid%FOUND) THEN
2986 CLOSE cur_rowid;
2987 fnd_message.set_name ('IGS', 'IGS_PS_UV_CAI_FK');
2988 igs_ge_msg_stack.add;
2989 app_exception.raise_exception;
2990 RETURN;
2991 END IF;
2992 CLOSE cur_rowid;
2993
2994 END get_fk_igs_ca_inst_all1;
2995 ---------------------------------------------------------------
2996 PROCEDURE Before_DML (
2997 p_action IN VARCHAR2,
2998 x_rowid IN VARCHAR2 ,
2999 x_unit_cd in VARCHAR2 ,
3000 x_version_number in NUMBER ,
3001 x_start_dt in DATE ,
3002 x_review_dt in DATE ,
3003 x_expiry_dt in DATE ,
3004 x_end_dt in DATE ,
3005 x_unit_status in VARCHAR2 ,
3006 x_title in VARCHAR2 ,
3007 x_short_title in VARCHAR2 ,
3008 x_title_override_ind in VARCHAR2 ,
3009 x_abbreviation in VARCHAR2 ,
3010 x_unit_level in VARCHAR2 ,
3011 x_credit_point_descriptor in VARCHAR2 ,
3012 x_enrolled_credit_points in NUMBER ,
3013 x_points_override_ind in VARCHAR2 ,
3014 x_supp_exam_permitted_ind in VARCHAR2 ,
3015 x_coord_person_id in NUMBER ,
3016 x_owner_org_unit_cd in VARCHAR2 ,
3017 x_owner_ou_start_dt in DATE ,
3018 x_award_course_only_ind in VARCHAR2 ,
3019 x_research_unit_ind in VARCHAR2 ,
3020 x_industrial_ind in VARCHAR2 ,
3021 x_practical_ind in VARCHAR2 ,
3022 x_repeatable_ind in VARCHAR2 ,
3023 x_assessable_ind in VARCHAR2 ,
3024 x_achievable_credit_points in NUMBER ,
3025 x_points_increment in NUMBER ,
3026 x_points_min in NUMBER ,
3027 x_points_max in NUMBER ,
3028 x_unit_int_course_level_cd in VARCHAR2 ,
3029 x_subtitle in VARCHAR2 ,
3030 x_subtitle_modifiable_flag in VARCHAR2 ,
3031 x_approval_date in DATE ,
3032 x_lecture_credit_points in NUMBER ,
3033 x_lab_credit_points in NUMBER ,
3034 x_other_credit_points in NUMBER ,
3035 x_clock_hours in NUMBER ,
3036 x_work_load_cp_lecture in NUMBER ,
3037 x_work_load_cp_lab in NUMBER ,
3038 x_continuing_education_units in NUMBER ,
3039 x_enrollment_expected in NUMBER ,
3040 x_enrollment_minimum in NUMBER ,
3041 x_enrollment_maximum in NUMBER ,
3042 x_advance_maximum in NUMBER ,
3043 x_state_financial_aid in VARCHAR2 ,
3044 x_federal_financial_aid in VARCHAR2 ,
3045 x_institutional_financial_aid in VARCHAR2 ,
3046 x_same_teaching_period in VARCHAR2 ,
3047 x_max_repeats_for_credit in NUMBER ,
3048 x_max_repeats_for_funding in NUMBER ,
3049 x_max_repeat_credit_points in NUMBER ,
3050 x_same_teach_period_repeats in NUMBER ,
3051 x_same_teach_period_repeats_cp in NUMBER ,
3052 x_attribute_category in VARCHAR2 ,
3053 x_attribute1 in VARCHAR2 ,
3054 x_attribute2 in VARCHAR2 ,
3055 x_attribute3 in VARCHAR2 ,
3056 x_attribute4 in VARCHAR2 ,
3057 x_attribute5 in VARCHAR2 ,
3058 x_attribute6 in VARCHAR2 ,
3059 x_attribute7 in VARCHAR2 ,
3060 x_attribute8 in VARCHAR2 ,
3061 x_attribute9 in VARCHAR2 ,
3062 x_attribute10 in VARCHAR2 ,
3063 x_attribute11 in VARCHAR2 ,
3064 x_attribute12 in VARCHAR2 ,
3065 x_attribute13 in VARCHAR2 ,
3066 x_attribute14 in VARCHAR2 ,
3067 x_attribute15 in VARCHAR2 ,
3068 x_attribute16 in VARCHAR2 ,
3069 x_attribute17 in VARCHAR2 ,
3070 x_attribute18 in VARCHAR2 ,
3071 x_attribute19 in VARCHAR2 ,
3072 x_attribute20 in VARCHAR2 ,
3073 x_subtitle_id IN NUMBER ,
3074 x_work_load_other IN NUMBER ,
3075 x_contact_hrs_lecture IN NUMBER ,
3076 x_contact_hrs_lab IN NUMBER ,
3077 x_contact_hrs_other IN NUMBER ,
3078 x_non_schd_required_hrs IN NUMBER ,
3079 x_exclude_from_max_cp_limit IN VARCHAR2 ,
3080 x_record_exclusion_flag IN VARCHAR2 ,
3081 x_ss_display_ind IN VARCHAR2 ,
3082 x_cal_type_enrol_load_cal IN VARCHAR2 ,
3083 x_sequence_num_enrol_load_cal IN NUMBER ,
3084 x_cal_type_offer_load_cal IN VARCHAR2 ,
3085 x_sequence_num_offer_load_cal IN NUMBER ,
3086 x_curriculum_id IN VARCHAR2 ,
3087 x_override_enrollment_max IN NUMBER ,
3088 x_rpt_fmly_id IN NUMBER ,
3089 x_unit_type_id IN NUMBER ,
3090 x_special_permission_ind IN VARCHAR2 ,
3091 x_created_by in NUMBER ,
3092 x_creation_date in DATE ,
3093 x_last_updated_by in NUMBER ,
3094 x_last_update_date in DATE ,
3095 x_last_update_login in NUMBER ,
3096 x_org_id in NUMBER ,
3097 x_ss_enrol_ind in VARCHAR2 ,
3098 x_ivr_enrol_ind in VARCHAR2 ,
3099 x_rev_account_cd IN VARCHAR2 ,
3100 x_claimable_hours IN NUMBER ,
3101 x_anon_unit_grading_ind IN VARCHAR2 ,
3102 x_anon_assess_grading_ind IN VARCHAR2 ,
3103 x_auditable_ind IN VARCHAR2,
3104 x_audit_permission_ind IN VARCHAR2,
3105 x_max_auditors_allowed IN NUMBER,
3106 x_billing_credit_points IN NUMBER,
3107 x_ovrd_wkld_val_flag IN VARCHAR2 ,
3108 x_workload_val_code IN VARCHAR2 ,
3109 x_billing_hrs IN NUMBER
3110 ) AS
3111 BEGIN
3112 Set_Column_Values (
3113 p_action,
3114 x_rowid,
3115 x_unit_cd,
3116 x_version_number,
3117 x_start_dt,
3118 x_review_dt,
3119 x_expiry_dt,
3120 x_end_dt,
3121 x_unit_status,
3122 x_title,
3123 x_short_title,
3124 x_title_override_ind,
3125 x_abbreviation,
3126 x_unit_level,
3127 x_credit_point_descriptor,
3128 x_enrolled_credit_points,
3129 x_points_override_ind,
3130 x_supp_exam_permitted_ind,
3131 x_coord_person_id,
3132 x_owner_org_unit_cd,
3133 x_owner_ou_start_dt,
3134 x_award_course_only_ind,
3135 x_research_unit_ind,
3136 x_industrial_ind,
3137 x_practical_ind,
3138 x_repeatable_ind,
3139 x_assessable_ind,
3140 x_achievable_credit_points,
3141 x_points_increment,
3142 x_points_min,
3143 x_points_max,
3144 x_unit_int_course_level_cd,
3145 x_subtitle,
3146 x_subtitle_modifiable_flag,
3147 x_approval_date,
3148 x_lecture_credit_points,
3149 x_lab_credit_points,
3150 x_other_credit_points,
3151 x_clock_hours,
3152 x_work_load_cp_lecture,
3153 x_work_load_cp_lab,
3154 x_continuing_education_units,
3155 x_enrollment_expected,
3156 x_enrollment_minimum,
3157 x_enrollment_maximum,
3158 x_advance_maximum,
3159 x_state_financial_aid,
3160 x_federal_financial_aid,
3161 x_institutional_financial_aid,
3162 x_same_teaching_period,
3163 x_max_repeats_for_credit,
3164 x_max_repeats_for_funding,
3165 x_max_repeat_credit_points,
3166 x_same_teach_period_repeats,
3167 x_same_teach_period_repeats_cp,
3168 x_attribute_category,
3169 x_attribute1,
3170 x_attribute2,
3171 x_attribute3,
3172 x_attribute4,
3173 x_attribute5,
3174 x_attribute6,
3175 x_attribute7,
3176 x_attribute8,
3177 x_attribute9,
3178 x_attribute10,
3179 x_attribute11,
3180 x_attribute12,
3181 x_attribute13,
3182 x_attribute14,
3183 x_attribute15,
3184 x_attribute16,
3185 x_attribute17,
3186 x_attribute18,
3187 x_attribute19,
3188 x_attribute20,
3189 X_SUBTITLE_ID,
3190 x_work_load_other,
3191 x_contact_hrs_lecture,
3192 x_contact_hrs_lab,
3193 x_contact_hrs_other,
3194 x_non_schd_required_hrs,
3195 x_exclude_from_max_cp_limit,
3196 x_record_exclusion_flag,
3197 x_ss_display_ind,
3198 x_cal_type_enrol_load_cal,
3199 x_sequence_num_enrol_load_cal,
3200 x_cal_type_offer_load_cal,
3201 x_sequence_num_offer_load_cal,
3202 x_curriculum_id,
3203 x_override_enrollment_max,
3204 x_rpt_fmly_id,
3205 x_unit_type_id,
3206 x_special_permission_ind,
3207 x_created_by,
3208 x_creation_date,
3209 x_last_updated_by,
3210 x_last_update_date,
3211 x_last_update_login,
3212 x_org_id,
3213 x_ss_enrol_ind,
3214 x_ivr_enrol_ind,
3215 x_rev_account_cd,
3216 x_claimable_hours,
3217 x_anon_unit_grading_ind,
3218 x_anon_assess_grading_ind,
3219 x_auditable_ind,
3220 x_audit_permission_ind,
3221 x_max_auditors_allowed,
3222 x_billing_credit_points,
3223 x_ovrd_wkld_val_flag ,
3224 x_workload_val_code ,
3225 x_billing_hrs
3226 );
3227
3228 IF (p_action = 'INSERT') THEN
3229 -- Call all the procedures related to Before Insert.
3230 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
3231 p_deleting => FALSE,
3232 p_updating => FALSE );
3233
3234 IF Get_PK_For_Validation (New_References.unit_cd,
3235 New_References.version_number) THEN
3236 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
3237 IGS_GE_MSG_STACK.ADD;
3238 App_Exception.Raise_Exception;
3239 END IF;
3240 Check_Constraints;
3241 Check_Parent_Existance;
3242 ELSIF (p_action = 'UPDATE') THEN
3243 -- Call all the procedures related to Before Update.
3244 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE,
3245 p_inserting => FALSE,
3246 p_deleting => FALSE );
3247 --bug#2416978,check updation specific validation
3248 beforerowupdate;
3249 Check_Constraints;
3250 Check_Parent_Existance;
3251 ELSIF (p_action = 'DELETE') THEN
3252 -- Call all the procedures related to Before Delete.
3253 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE ,
3254 p_inserting => FALSE,
3255 p_updating => FALSE );
3256 --bug#2416978,check deletion specific validation
3257 beforerowdelete;
3258 Check_Child_Existance;
3259 ELSIF (p_action = 'VALIDATE_INSERT') THEN
3260 IF Get_PK_For_Validation (New_References.unit_cd, New_References.version_number) THEN
3261 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
3262 IGS_GE_MSG_STACK.ADD;
3263 App_Exception.Raise_Exception;
3264 END IF;
3265 Check_Constraints;
3266 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
3267 Check_Constraints;
3268 ELSIF (p_action = 'VALIDATE_DELETE') THEN
3269 Check_Child_Existance;
3270 END IF;
3271
3272 END Before_DML;
3273 ----------------------------------------------------------------------------
3274 PROCEDURE dflt_unit_ref_code (
3275 p_c_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
3276 p_n_version_number igs_ps_unit_ver.version_number%TYPE,
3277 p_c_message_name OUT NOCOPY VARCHAR2
3278 ) AS
3279 CURSOR c_igs_ge_ref_cd_type IS
3280 SELECT reference_cd_type
3281 FROM igs_ge_ref_cd_type
3282 WHERE mandatory_flag ='Y'
3283 AND unit_flag ='Y'
3284 AND restricted_flag ='Y'
3285 AND closed_ind = 'N';
3286
3287 CURSOR c_igs_ge_ref_cd ( cp_reference_cd_type igs_ge_ref_cd.reference_cd_type%TYPE ) IS
3288 SELECT reference_cd,
3289 description
3290 FROM igs_ge_ref_cd
3291 WHERE reference_cd_type = cp_reference_cd_type
3292 AND default_flag = 'Y';
3293
3294 l_c_rowid VARCHAR2(30);
3295
3296 BEGIN
3297 FOR cur_igs_ge_ref_cd_type IN c_igs_ge_ref_cd_type
3298 LOOP
3299 FOR cur_igs_ge_ref_cd IN c_igs_ge_ref_cd(cur_igs_ge_ref_cd_type.reference_cd_type)
3300 LOOP
3301 -- insert a value in igs_ps_unit_ref_cd for every value of unit_cd and version_number having
3302 -- a applicable unit defined as mandatory and a default reference code
3303 BEGIN
3304 l_c_rowid:=NULL;
3305 igs_ps_unit_ref_cd_pkg.insert_row(x_rowid => l_c_rowid,
3306 x_unit_cd => p_c_unit_cd,
3307 x_reference_cd_type => cur_igs_ge_ref_cd_type.reference_cd_type,
3308 x_version_number => p_n_version_number,
3309 x_reference_cd => cur_igs_ge_ref_cd.reference_cd,
3310 x_description => cur_igs_ge_ref_cd.description,
3311 x_mode => 'R');
3312 EXCEPTION
3313 -- The failure of insertion of reference code should not stop the creation of new unit.
3314 -- Hence any exception raised by the TBH is trapped and the current processing is allowed to proceed.
3315 WHEN OTHERS THEN
3316 NULL;
3317 END;
3318 END LOOP;
3319 END LOOP;
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 -- If an error occurs during insertion in igs_ps_ent_pt_ref_cd then raise an exception.
3323 p_c_message_name := FND_MESSAGE.GET;
3324
3325 END dflt_unit_ref_code;
3326 -------------------------------------------------------------
3327 PROCEDURE After_DML (
3328 p_action IN VARCHAR2,
3329 x_rowid IN VARCHAR2
3330 ) AS
3331 l_message_name VARCHAR2(30) ;
3332 CURSOR c_uv(cp_rowid igs_ps_unit_ver.row_id%TYPE) IS
3333 SELECT unit_cd, version_number
3334 FROM igs_ps_unit_ver
3335 WHERE ROWID = cp_rowid;
3336 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
3337 l_version_number igs_ps_unit_ver.version_number%TYPE;
3338
3339 CURSOR c_occurs(cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE , cp_version_number igs_ps_unit_ver.version_number%TYPE) IS
3340 SELECT uso.unit_section_occurrence_id
3341 FROM Igs_ps_usec_occurs_all uso,
3342 Igs_ps_unit_ofr_opt_all us,
3343 Igs_ca_inst_all ci
3344 WHERE uso.uoo_id=us.uoo_id
3345 AND us.cal_type=ci.cal_type
3346 AND us.ci_sequence_number = ci.sequence_number
3347 AND TRUNC (ci.end_dt) > TRUNC (SYSDATE)
3348 AND (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
3349 AND uso.no_set_day_ind ='N'
3350 AND us.unit_section_status <> 'NOT_OFFERED'
3351 AND us.unit_cd =cp_unit_cd
3352 AND us.version_number =cp_version_number
3353 AND NOT EXISTS (SELECT 'X' FROM igs_ps_usec_lim_wlst ulw WHERE ulw.uoo_id = us.uoo_id);
3354
3355 CURSOR c_title(cp_unit_cd VARCHAR2, cp_version_number NUMBER) IS
3356 SELECT a.*,a.rowid
3357 FROM igs_ps_usec_ref a ,
3358 igs_ps_unit_ofr_opt_all b,
3359 igs_ca_inst_all c
3360 WHERE a.uoo_id= b.uoo_id
3361 AND b.unit_cd= cp_unit_cd
3362 AND b.version_number = cp_version_number
3363 AND b.cal_type=c.cal_type
3364 AND b.ci_sequence_number=c.sequence_number;
3365
3366
3367
3368 CURSOR c_unit_subtitle(cp_unit_cd VARCHAR2, cp_version_number NUMBER,cp_subtitle VARCHAR2) IS
3369 SELECT subtitle_id
3370 FROM igs_ps_unit_subtitle_v
3371 WHERE unit_cd = cp_unit_cd AND
3372 version_number = cp_version_number AND
3373 subtitle =cp_subtitle;
3374
3375 update_flag BOOLEAN := FALSE;
3376 r_unit_subtitle igs_ps_unit_subtitle_v.subtitle_id%TYPE;
3377 v_title igs_ps_usec_ref.title%TYPE;
3378
3379
3380 BEGIN
3381 l_message_name := NULL;
3382 l_rowid := x_rowid;
3383
3384 IF (p_action = 'INSERT') THEN
3385 -- Call all the procedures related to After Insert.
3386 AfterRowInsertUpdate2 ( p_inserting => TRUE,
3387 p_updating => FALSE,
3388 p_deleting => FALSE);
3389
3390 OPEN c_uv(x_rowid);
3391 FETCH c_uv INTO l_unit_cd, l_version_number;
3392 IF (c_uv%FOUND) THEN
3393 dflt_unit_ref_code( p_c_unit_cd => l_unit_cd,
3394 p_n_version_number => l_version_number,
3395 p_c_message_name => l_message_name);
3396 IF l_message_name IS NOT NULL THEN
3397 app_exception.raise_exception;
3398 END IF;
3399 END IF;
3400 CLOSE c_uv;
3401 l_rowid := NULL;
3402 ELSIF (p_action = 'UPDATE') THEN
3403 -- Call all the procedures related to After Update.
3404 AfterRowInsertUpdate2 ( p_updating => TRUE ,
3405 p_inserting => FALSE,
3406 p_deleting => FALSE);
3407
3408 --After update, update the occurrence schedule status to Reschedule if changing any of the Enrollment Maximum, Override Enrollmnet
3409 -- Maximum and Enrollment Expected, condition being the corresponding unit section does not have enrollment limit record.
3410 IF (
3411 NVL(new_references.enrollment_maximum,-999) <> NVL(old_references.enrollment_maximum,-999) OR
3412 NVL(new_references.enrollment_expected,-999) <> NVL(old_references.enrollment_expected,-999) OR
3413 NVL(new_references.override_enrollment_max,-999) <> NVL(old_references.override_enrollment_max,-999)
3414 ) THEN
3415
3416 FOR l_occurs_rec IN c_occurs(new_references.unit_cd,new_references.version_number) LOOP
3417 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
3418 END LOOP;
3419 END IF;
3420
3421 --whenever there is a change in unit level title/subtitle then the section level data needs to be changed.
3422 IF (old_references.title <> new_references.title AND new_references.title_override_ind = 'N')
3423 OR (NVL(old_references.subtitle_id,-999) <> NVL(new_references.subtitle_id,-999) AND new_references.subtitle_modifiable_flag = 'N') THEN
3424 FOR c_title_rec IN c_title(new_references.unit_cd,new_references.version_number) LOOP
3425 update_flag := FALSE;
3426 v_title :=c_title_rec.title;
3427 r_unit_subtitle := c_title_rec.Subtitle_id;
3428
3429 IF new_references.title_override_ind = 'N' AND new_references.title <> NVL(c_title_rec.title,'NULL') THEN
3430 v_title := new_references.title;
3431 update_flag := TRUE;
3432 END IF;
3433
3434 IF new_references.subtitle_modifiable_flag = 'N' AND NVL(new_references.subtitle_id,-999) <> NVL(c_title_rec.subtitle_id,-999) THEN
3435 r_unit_subtitle :=new_references.subtitle_id;
3436 update_flag := TRUE;
3437 END IF;
3438
3439 IF update_flag THEN
3440 Igs_Ps_Usec_Ref_Pkg.Update_Row (
3441 X_Mode => 'R',
3442 X_rowid => c_title_rec.rowid,
3443 X_Unit_Section_Reference_Id => c_title_rec.unit_section_reference_id,
3444 X_Uoo_Id => c_title_rec.uoo_id,
3445 X_Short_Title => c_title_rec.Short_Title,
3446 X_Subtitle => NULL,
3447 X_Subtitle_ModIFiable_Flag => c_title_rec.subtitle_modifiable_flag,
3448 X_Class_Sched_Exclusion_Flag => c_title_rec.class_schedule_exclusion_flag,
3449 X_Registration_Exclusion_Flag => c_title_rec.registration_exclusion_flag,
3450 X_Attribute_Category => c_title_rec.attribute_category,
3451 X_Attribute1 => c_title_rec.Attribute1,
3452 X_Attribute2 => c_title_rec.Attribute2,
3453 X_Attribute3 => c_title_rec.Attribute3,
3454 X_Attribute4 => c_title_rec.Attribute4,
3455 X_Attribute5 => c_title_rec.Attribute5,
3456 X_Attribute6 => c_title_rec.Attribute6,
3457 X_Attribute7 => c_title_rec.Attribute7,
3458 X_Attribute8 => c_title_rec.Attribute8,
3459 X_Attribute9 => c_title_rec.Attribute9,
3460 X_Attribute10 => c_title_rec.Attribute10,
3461 X_Attribute11 => c_title_rec.Attribute11,
3462 X_Attribute12 => c_title_rec.Attribute12,
3463 X_Attribute13 => c_title_rec.Attribute13,
3464 X_Attribute14 => c_title_rec.Attribute14,
3465 X_Attribute15 => c_title_rec.Attribute15,
3466 X_Attribute16 => c_title_rec.Attribute16,
3467 X_Attribute17 => c_title_rec.Attribute17,
3468 X_Attribute18 => c_title_rec.Attribute18,
3469 X_Attribute19 => c_title_rec.Attribute19,
3470 X_Attribute20 => c_title_rec.Attribute20,
3471 X_Title => v_title,
3472 X_Subtitle_id => r_unit_subtitle,
3473 X_Record_Exclusion_Flag => c_title_rec.record_exclusion_flag
3474 );
3475 END IF;
3476 END LOOP;
3477 END IF;
3478
3479
3480 END IF;
3481
3482 END After_DML;
3483 -------------------------------------------------------------------
3484 PROCEDURE insert_row (
3485 X_ROWID in out NOCOPY VARCHAR2,
3486 X_UNIT_CD in VARCHAR2,
3487 X_VERSION_NUMBER in NUMBER,
3488 X_START_DT in DATE,
3489 X_REVIEW_DT in DATE,
3490 X_EXPIRY_DT in DATE,
3491 X_END_DT in DATE,
3492 X_UNIT_STATUS in VARCHAR2,
3493 X_TITLE in VARCHAR2,
3494 X_SHORT_TITLE in VARCHAR2,
3495 X_TITLE_OVERRIDE_IND in VARCHAR2,
3496 X_ABBREVIATION in VARCHAR2,
3497 X_UNIT_LEVEL in VARCHAR2,
3498 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
3499 X_ENROLLED_CREDIT_POINTS in NUMBER,
3500 X_POINTS_OVERRIDE_IND in VARCHAR2,
3501 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
3502 X_COORD_PERSON_ID in NUMBER,
3503 X_OWNER_ORG_UNIT_CD in VARCHAR2,
3504 X_OWNER_OU_START_DT in DATE,
3505 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
3506 X_RESEARCH_UNIT_IND in VARCHAR2,
3507 X_INDUSTRIAL_IND in VARCHAR2,
3508 X_PRACTICAL_IND in VARCHAR2,
3509 X_REPEATABLE_IND in VARCHAR2,
3510 X_ASSESSABLE_IND in VARCHAR2,
3511 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
3512 X_POINTS_INCREMENT in NUMBER,
3513 X_POINTS_MIN in NUMBER,
3514 X_POINTS_MAX in NUMBER,
3515 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
3516 X_SUBTITLE in VARCHAR2,
3517 X_SUBTITLE_MODIFIABLE_FLAG in VARCHAR2,
3518 X_APPROVAL_DATE in DATE,
3519 X_LECTURE_CREDIT_POINTS in NUMBER,
3520 X_LAB_CREDIT_POINTS in NUMBER,
3521 X_OTHER_CREDIT_POINTS in NUMBER,
3522 X_CLOCK_HOURS in NUMBER,
3523 X_WORK_LOAD_CP_LECTURE in NUMBER,
3524 X_WORK_LOAD_CP_LAB in NUMBER,
3525 X_CONTINUING_EDUCATION_UNITS in NUMBER,
3526 X_ENROLLMENT_EXPECTED in NUMBER,
3527 X_ENROLLMENT_MINIMUM in NUMBER,
3528 X_ENROLLMENT_MAXIMUM in NUMBER,
3529 X_ADVANCE_MAXIMUM in NUMBER,
3530 X_STATE_FINANCIAL_AID in VARCHAR2,
3531 X_FEDERAL_FINANCIAL_AID in VARCHAR2,
3532 X_INSTITUTIONAL_FINANCIAL_AID in VARCHAR2,
3533 X_SAME_TEACHING_PERIOD in VARCHAR2,
3534 X_MAX_REPEATS_FOR_CREDIT in NUMBER,
3535 X_MAX_REPEATS_FOR_FUNDING in NUMBER,
3536 X_MAX_REPEAT_CREDIT_POINTS in NUMBER,
3537 X_SAME_TEACH_PERIOD_REPEATS in NUMBER,
3538 X_SAME_TEACH_PERIOD_REPEATS_CP in NUMBER,
3539 X_ATTRIBUTE_CATEGORY in VARCHAR2,
3540 X_ATTRIBUTE1 in VARCHAR2,
3541 X_ATTRIBUTE2 in VARCHAR2,
3542 X_ATTRIBUTE3 in VARCHAR2,
3543 X_ATTRIBUTE4 in VARCHAR2,
3544 X_ATTRIBUTE5 in VARCHAR2,
3545 X_ATTRIBUTE6 in VARCHAR2,
3546 X_ATTRIBUTE7 in VARCHAR2,
3547 X_ATTRIBUTE8 in VARCHAR2,
3548 X_ATTRIBUTE9 in VARCHAR2,
3549 X_ATTRIBUTE10 in VARCHAR2,
3550 X_ATTRIBUTE11 in VARCHAR2,
3551 X_ATTRIBUTE12 in VARCHAR2,
3552 X_ATTRIBUTE13 in VARCHAR2,
3553 X_ATTRIBUTE14 in VARCHAR2,
3554 X_ATTRIBUTE15 in VARCHAR2,
3555 X_ATTRIBUTE16 in VARCHAR2,
3556 X_ATTRIBUTE17 in VARCHAR2,
3557 X_ATTRIBUTE18 in VARCHAR2,
3558 X_ATTRIBUTE19 in VARCHAR2,
3559 X_ATTRIBUTE20 in VARCHAR2,
3560 x_subtitle_id IN NUMBER ,
3561 x_work_load_other IN NUMBER ,
3562 x_contact_hrs_lecture IN NUMBER ,
3563 x_contact_hrs_lab IN NUMBER ,
3564 x_contact_hrs_other IN NUMBER ,
3565 x_non_schd_required_hrs IN NUMBER ,
3566 x_exclude_from_max_cp_limit IN VARCHAR2 ,
3567 x_record_exclusion_flag IN VARCHAR2 ,
3568 x_ss_display_ind IN VARCHAR2 ,
3569 x_cal_type_enrol_load_cal IN VARCHAR2 ,
3570 x_sequence_num_enrol_load_cal IN NUMBER ,
3571 x_cal_type_offer_load_cal IN VARCHAR2 ,
3572 x_sequence_num_offer_load_cal IN NUMBER ,
3573 x_curriculum_id IN VARCHAR2 ,
3574 x_override_enrollment_max IN NUMBER ,
3575 x_rpt_fmly_id IN NUMBER ,
3576 x_unit_type_id IN NUMBER ,
3577 x_special_permission_ind IN VARCHAR2 ,
3578 X_MODE in VARCHAR2 ,
3579 X_ORG_ID IN NUMBER,
3580 X_SS_ENROL_IND IN VARCHAR2 ,
3581 X_IVR_ENROL_IND IN VARCHAR2 ,
3582 x_claimable_hours IN NUMBER ,
3583 x_rev_account_cd IN VARCHAR2 ,
3584 x_anon_unit_grading_ind IN VARCHAR2 ,
3585 x_anon_assess_grading_ind IN VARCHAR2 ,
3586 x_auditable_ind IN VARCHAR2,
3587 x_audit_permission_ind IN VARCHAR2,
3588 x_max_auditors_allowed IN NUMBER,
3589 x_billing_credit_points IN NUMBER ,
3590 x_ovrd_wkld_val_flag IN VARCHAR2 ,
3591 x_workload_val_code IN VARCHAR2 ,
3592 x_billing_hrs IN NUMBER
3593 ) AS
3594 /**********************************************************************************************************************************
3595 sarakshi 14-oct-2003 Enh#3052452,removed the nvl clause from max_repeats_for_credits in the before_dml call.
3596 sbaliga 13-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id in call to before_dml
3597 as part of SWCR006 build.
3598 **************************************************************************************************************/
3599 cursor C is select ROWID from IGS_PS_UNIT_VER_ALL
3600 where UNIT_CD = X_UNIT_CD
3601 and VERSION_NUMBER = X_VERSION_NUMBER;
3602 X_LAST_UPDATE_DATE DATE;
3603 X_LAST_UPDATED_BY NUMBER;
3604 X_LAST_UPDATE_LOGIN NUMBER;
3605 begin
3606 X_LAST_UPDATE_DATE := SYSDATE;
3607 if(X_MODE = 'I') then
3608 X_LAST_UPDATED_BY := 1;
3609 X_LAST_UPDATE_LOGIN := 0;
3610 elsif (X_MODE = 'R') then
3611 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3612 if X_LAST_UPDATED_BY is NULL then
3613 X_LAST_UPDATED_BY := -1;
3614 end if;
3615 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
3616 if X_LAST_UPDATE_LOGIN is NULL then
3617 X_LAST_UPDATE_LOGIN := -1;
3618 end if;
3619 else
3620 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
3621 IGS_GE_MSG_STACK.ADD;
3622 app_exception.raise_exception;
3623 end if;
3624
3625 Before_DML(
3626 p_action => 'INSERT',
3627 x_rowid => X_ROWID,
3628 x_unit_cd => X_UNIT_CD,
3629 x_version_number => X_VERSION_NUMBER,
3630 x_start_dt => X_START_DT,
3631 x_review_dt => X_REVIEW_DT,
3632 x_expiry_dt => X_EXPIRY_DT,
3633 x_end_dt => X_END_DT,
3634 x_unit_status => X_UNIT_STATUS,
3635 x_title => X_TITLE,
3636 x_short_title => X_SHORT_TITLE,
3637 x_title_override_ind => NVL(X_TITLE_OVERRIDE_IND,'Y'),
3638 x_abbreviation => X_ABBREVIATION,
3639 x_unit_level => X_UNIT_LEVEL,
3640 x_credit_point_descriptor => X_CREDIT_POINT_DESCRIPTOR,
3641 x_enrolled_credit_points => X_ENROLLED_CREDIT_POINTS,
3642 x_points_override_ind => NVL(X_POINTS_OVERRIDE_IND,'Y'),
3643 x_supp_exam_permitted_ind => NVL(X_SUPP_EXAM_PERMITTED_IND,'Y'),
3644 x_coord_person_id => X_COORD_PERSON_ID,
3645 x_owner_org_unit_cd => X_OWNER_ORG_UNIT_CD,
3646 x_owner_ou_start_dt => X_OWNER_OU_START_DT,
3647 x_award_course_only_ind => NVL(X_AWARD_COURSE_ONLY_IND,'Y'),
3648 x_research_unit_ind => NVL(X_RESEARCH_UNIT_IND,'N'),
3649 x_industrial_ind => NVL(X_INDUSTRIAL_IND,'N'),
3650 x_practical_ind => NVL(X_PRACTICAL_IND,'N'),
3651 x_repeatable_ind => NVL(X_REPEATABLE_IND,'Y'),
3652 x_assessable_ind => NVL(X_ASSESSABLE_IND,'Y'),
3653 x_achievable_credit_points => X_ACHIEVABLE_CREDIT_POINTS,
3654 x_points_increment => X_POINTS_INCREMENT,
3655 x_points_min => X_POINTS_MIN,
3656 x_points_max => X_POINTS_MAX,
3657 x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
3658 x_subtitle => X_SUBTITLE,
3659 x_subtitle_modifiable_flag => X_SUBTITLE_MODIFIABLE_FLAG,
3660 x_approval_date => X_APPROVAL_DATE,
3661 x_lecture_credit_points => X_LECTURE_CREDIT_POINTS,
3662 x_lab_credit_points => X_LAB_CREDIT_POINTS,
3663 x_other_credit_points => X_OTHER_CREDIT_POINTS,
3664 x_clock_hours => X_CLOCK_HOURS,
3665 x_work_load_cp_lecture => X_WORK_LOAD_CP_LECTURE,
3666 x_work_load_cp_lab => X_WORK_LOAD_CP_LAB,
3667 x_continuing_education_units => X_CONTINUING_EDUCATION_UNITS,
3668 x_enrollment_expected => X_ENROLLMENT_EXPECTED,
3669 x_enrollment_minimum => X_ENROLLMENT_MINIMUM,
3670 x_enrollment_maximum => X_ENROLLMENT_MAXIMUM,
3671 x_advance_maximum => X_ADVANCE_MAXIMUM,
3672 x_state_financial_aid => NVL(X_STATE_FINANCIAL_AID, 'Y'),
3673 x_federal_financial_aid => NVL(X_FEDERAL_FINANCIAL_AID, 'Y'),
3674 x_institutional_financial_aid => NVL(X_INSTITUTIONAL_FINANCIAL_AID, 'Y'),
3675 x_same_teaching_period => X_SAME_TEACHING_PERIOD,
3676 x_max_repeats_for_credit => X_MAX_REPEATS_FOR_CREDIT,
3677 x_max_repeats_for_funding => X_MAX_REPEATS_FOR_FUNDING,
3678 x_max_repeat_credit_points => X_MAX_REPEAT_CREDIT_POINTS,
3679 x_same_teach_period_repeats => X_SAME_TEACH_PERIOD_REPEATS,
3680 x_same_teach_period_repeats_cp => X_SAME_TEACH_PERIOD_REPEATS_CP,
3681 x_attribute_category => X_ATTRIBUTE_CATEGORY,
3682 x_attribute1 => X_ATTRIBUTE1,
3683 x_attribute2 => X_ATTRIBUTE2,
3684 x_attribute3 => X_ATTRIBUTE3,
3685 x_attribute4 => X_ATTRIBUTE4,
3686 x_attribute5 => X_ATTRIBUTE5,
3687 x_attribute6 => X_ATTRIBUTE6,
3688 x_attribute7 => X_ATTRIBUTE7,
3689 x_attribute8 => X_ATTRIBUTE8,
3690 x_attribute9 => X_ATTRIBUTE9,
3691 x_attribute10 => X_ATTRIBUTE10,
3692 x_attribute11 => X_ATTRIBUTE11,
3693 x_attribute12 => X_ATTRIBUTE12,
3694 x_attribute13 => X_ATTRIBUTE13,
3695 x_attribute14 => X_ATTRIBUTE14,
3696 x_attribute15 => X_ATTRIBUTE15,
3697 x_attribute16 => X_ATTRIBUTE16,
3698 x_attribute17 => X_ATTRIBUTE17,
3699 x_attribute18 => X_ATTRIBUTE18,
3700 x_attribute19 => X_ATTRIBUTE19,
3701 x_attribute20 => X_ATTRIBUTE20,
3702 X_subtitle_id => X_SUBTITLE_ID,
3703 x_work_load_other => x_work_load_other,
3704 x_contact_hrs_lecture => x_contact_hrs_lecture,
3705 x_contact_hrs_lab => x_contact_hrs_lab,
3706 x_contact_hrs_other => x_contact_hrs_other,
3707 x_non_schd_required_hrs => x_non_schd_required_hrs,
3708 x_exclude_from_max_cp_limit => NVL(x_exclude_from_max_cp_limit,'N'),
3709 x_record_exclusion_flag => NVL(x_record_exclusion_flag,'N'),
3710 x_ss_display_ind => NVL(x_ss_display_ind,'N'),
3711 x_cal_type_enrol_load_cal => x_cal_type_enrol_load_cal,
3712 x_sequence_num_enrol_load_cal => x_sequence_num_enrol_load_cal,
3713 x_cal_type_offer_load_cal => x_cal_type_offer_load_cal,
3714 x_sequence_num_offer_load_cal => x_sequence_num_offer_load_cal,
3715 x_curriculum_id => x_curriculum_id,
3716 x_override_enrollment_max => x_override_enrollment_max,
3717 x_rpt_fmly_id => x_rpt_fmly_id,
3718 x_unit_type_id => x_unit_type_id,
3719 x_special_permission_ind => NVL(x_special_permission_ind,'N'),
3720 x_creation_date => X_LAST_UPDATE_DATE,
3721 x_created_by => X_LAST_UPDATED_BY,
3722 x_last_update_date => X_LAST_UPDATE_DATE,
3723 x_last_updated_by => X_LAST_UPDATED_BY,
3724 x_last_update_login => X_LAST_UPDATE_LOGIN,
3725 x_org_id => igs_ge_gen_003.get_org_id,
3726 x_ss_enrol_ind => X_SS_ENROL_IND,
3727 x_ivr_enrol_ind => X_IVR_ENROL_IND,
3728 x_rev_account_cd => x_rev_account_cd ,
3729 x_claimable_hours => x_claimable_hours ,
3730 x_anon_unit_grading_ind => x_anon_unit_grading_ind ,
3731 x_anon_assess_grading_ind => x_anon_assess_grading_ind ,
3732 x_auditable_ind => x_auditable_ind,
3733 x_audit_permission_ind => x_audit_permission_ind,
3734 x_max_auditors_allowed => x_max_auditors_allowed,
3735 x_billing_credit_points => x_billing_credit_points,
3736 x_ovrd_wkld_val_flag => x_ovrd_wkld_val_flag ,
3737 x_workload_val_code => x_workload_val_code ,
3738 x_billing_hrs => x_billing_hrs
3739 );
3740
3741
3742 INSERT INTO IGS_PS_UNIT_VER_ALL (
3743 UNIT_CD,
3744 VERSION_NUMBER,
3745 START_DT,
3746 REVIEW_DT,
3747 EXPIRY_DT,
3748 END_DT,
3749 UNIT_STATUS,
3750 TITLE,
3751 SHORT_TITLE,
3752 TITLE_OVERRIDE_IND,
3753 ABBREVIATION,
3754 UNIT_LEVEL,
3755 CREDIT_POINT_DESCRIPTOR,
3756 ENROLLED_CREDIT_POINTS,
3757 POINTS_OVERRIDE_IND,
3758 SUPP_EXAM_PERMITTED_IND,
3759 COORD_PERSON_ID,
3760 OWNER_ORG_UNIT_CD,
3761 OWNER_OU_START_DT,
3762 AWARD_COURSE_ONLY_IND,
3763 RESEARCH_UNIT_IND,
3764 INDUSTRIAL_IND,
3765 PRACTICAL_IND,
3766 REPEATABLE_IND,
3767 ASSESSABLE_IND,
3768 ACHIEVABLE_CREDIT_POINTS,
3769 POINTS_INCREMENT,
3770 POINTS_MIN,
3771 POINTS_MAX,
3772 UNIT_INT_COURSE_LEVEL_CD,
3773 SUBTITLE_MODIFIABLE_FLAG,
3774 APPROVAL_DATE,
3775 LECTURE_CREDIT_POINTS,
3776 LAB_CREDIT_POINTS,
3777 OTHER_CREDIT_POINTS,
3778 CLOCK_HOURS,
3779 WORK_LOAD_CP_LECTURE,
3780 WORK_LOAD_CP_LAB,
3781 CONTINUING_EDUCATION_UNITS,
3782 ENROLLMENT_EXPECTED,
3783 ENROLLMENT_MINIMUM,
3784 ENROLLMENT_MAXIMUM,
3785 ADVANCE_MAXIMUM,
3786 STATE_FINANCIAL_AID,
3787 FEDERAL_FINANCIAL_AID,
3788 INSTITUTIONAL_FINANCIAL_AID,
3789 SAME_TEACHING_PERIOD,
3790 MAX_REPEATS_FOR_CREDIT,
3791 MAX_REPEATS_FOR_FUNDING,
3792 MAX_REPEAT_CREDIT_POINTS,
3793 SAME_TEACH_PERIOD_REPEATS,
3794 SAME_TEACH_PERIOD_REPEATS_CP,
3795 ATTRIBUTE_CATEGORY,
3796 ATTRIBUTE1,
3797 ATTRIBUTE2,
3798 ATTRIBUTE3,
3799 ATTRIBUTE4,
3800 ATTRIBUTE5,
3801 ATTRIBUTE6,
3802 ATTRIBUTE7,
3803 ATTRIBUTE8,
3804 ATTRIBUTE9,
3805 ATTRIBUTE10,
3806 ATTRIBUTE11,
3807 ATTRIBUTE12,
3808 ATTRIBUTE13,
3809 ATTRIBUTE14,
3810 ATTRIBUTE15,
3811 ATTRIBUTE16,
3812 ATTRIBUTE17,
3813 ATTRIBUTE18,
3814 ATTRIBUTE19,
3815 ATTRIBUTE20,
3816 subtitle_id,
3817 work_load_other,
3818 contact_hrs_lecture,
3819 contact_hrs_lab,
3820 contact_hrs_other,
3821 non_schd_required_hrs,
3822 exclude_from_max_cp_limit,
3823 record_exclusion_flag,
3824 ss_display_ind,
3825 cal_type_enrol_load_cal,
3826 sequence_num_enrol_load_cal,
3827 cal_type_offer_load_cal,
3828 sequence_num_offer_load_cal,
3829 curriculum_id,
3830 override_enrollment_max,
3831 rpt_fmly_id,
3832 unit_type_id,
3833 special_permission_ind,
3834 CREATION_DATE,
3835 CREATED_BY,
3836 LAST_UPDATE_DATE,
3837 LAST_UPDATED_BY,
3838 LAST_UPDATE_LOGIN,
3839 ORG_ID,
3840 SS_ENROL_IND,
3841 IVR_ENROL_IND,
3842 REV_ACCOUNT_CD,
3843 CLAIMABLE_HOURS,
3844 ANON_UNIT_GRADING_IND,
3845 ANON_ASSESS_GRADING_IND,
3846 AUDITABLE_IND,
3847 AUDIT_PERMISSION_IND,
3848 MAX_AUDITORS_ALLOWED,
3849 BILLING_CREDIT_POINTS,
3850 OVRD_WKLD_VAL_FLAG,
3851 WORKLOAD_VAL_CODE,
3852 BILLING_HRS
3853 ) VALUES (
3854 NEW_REFERENCES.UNIT_CD,
3855 NEW_REFERENCES.VERSION_NUMBER,
3856 NEW_REFERENCES.START_DT,
3857 NEW_REFERENCES.REVIEW_DT,
3858 NEW_REFERENCES.EXPIRY_DT,
3859 NEW_REFERENCES.END_DT,
3860 NEW_REFERENCES.UNIT_STATUS,
3861 NEW_REFERENCES.TITLE,
3862 NEW_REFERENCES.SHORT_TITLE,
3863 NEW_REFERENCES.TITLE_OVERRIDE_IND,
3864 NEW_REFERENCES.ABBREVIATION,
3865 NEW_REFERENCES.UNIT_LEVEL,
3866 NEW_REFERENCES.CREDIT_POINT_DESCRIPTOR,
3867 NEW_REFERENCES.ENROLLED_CREDIT_POINTS,
3868 NEW_REFERENCES.POINTS_OVERRIDE_IND,
3869 NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
3870 NEW_REFERENCES.COORD_PERSON_ID,
3871 NEW_REFERENCES.OWNER_ORG_UNIT_CD,
3872 NEW_REFERENCES.OWNER_OU_START_DT,
3873 NEW_REFERENCES.AWARD_COURSE_ONLY_IND,
3874 NEW_REFERENCES.RESEARCH_UNIT_IND,
3875 NEW_REFERENCES.INDUSTRIAL_IND,
3876 NEW_REFERENCES.PRACTICAL_IND,
3877 NEW_REFERENCES.REPEATABLE_IND,
3878 NEW_REFERENCES.ASSESSABLE_IND,
3879 NEW_REFERENCES.ACHIEVABLE_CREDIT_POINTS,
3880 NEW_REFERENCES.POINTS_INCREMENT,
3881 NEW_REFERENCES.POINTS_MIN,
3882 NEW_REFERENCES.POINTS_MAX,
3883 NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
3884 NEW_REFERENCES.SUBTITLE_MODIFIABLE_FLAG,
3885 NEW_REFERENCES.APPROVAL_DATE,
3886 NEW_REFERENCES.LECTURE_CREDIT_POINTS,
3887 NEW_REFERENCES.LAB_CREDIT_POINTS,
3888 NEW_REFERENCES.OTHER_CREDIT_POINTS,
3889 NEW_REFERENCES.CLOCK_HOURS,
3890 NEW_REFERENCES.WORK_LOAD_CP_LECTURE,
3891 NEW_REFERENCES.WORK_LOAD_CP_LAB,
3892 NEW_REFERENCES.CONTINUING_EDUCATION_UNITS,
3893 NEW_REFERENCES.ENROLLMENT_EXPECTED,
3894 NEW_REFERENCES.ENROLLMENT_MINIMUM,
3895 NEW_REFERENCES.ENROLLMENT_MAXIMUM,
3896 NEW_REFERENCES.ADVANCE_MAXIMUM,
3897 NEW_REFERENCES.STATE_FINANCIAL_AID,
3898 NEW_REFERENCES.FEDERAL_FINANCIAL_AID,
3899 NEW_REFERENCES.INSTITUTIONAL_FINANCIAL_AID,
3900 NEW_REFERENCES.SAME_TEACHING_PERIOD,
3901 NEW_REFERENCES.MAX_REPEATS_FOR_CREDIT,
3902 NEW_REFERENCES.MAX_REPEATS_FOR_FUNDING,
3903 NEW_REFERENCES.MAX_REPEAT_CREDIT_POINTS,
3904 NEW_REFERENCES.SAME_TEACH_PERIOD_REPEATS,
3905 NEW_REFERENCES.SAME_TEACH_PERIOD_REPEATS_CP,
3906 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
3907 NEW_REFERENCES.ATTRIBUTE1,
3908 NEW_REFERENCES.ATTRIBUTE2,
3909 NEW_REFERENCES.ATTRIBUTE3,
3910 NEW_REFERENCES.ATTRIBUTE4,
3911 NEW_REFERENCES.ATTRIBUTE5,
3912 NEW_REFERENCES.ATTRIBUTE6,
3913 NEW_REFERENCES.ATTRIBUTE7,
3914 NEW_REFERENCES.ATTRIBUTE8,
3915 NEW_REFERENCES.ATTRIBUTE9,
3916 NEW_REFERENCES.ATTRIBUTE10,
3917 NEW_REFERENCES.ATTRIBUTE11,
3918 NEW_REFERENCES.ATTRIBUTE12,
3919 NEW_REFERENCES.ATTRIBUTE13,
3920 NEW_REFERENCES.ATTRIBUTE14,
3921 NEW_REFERENCES.ATTRIBUTE15,
3922 NEW_REFERENCES.ATTRIBUTE16,
3923 NEW_REFERENCES.ATTRIBUTE17,
3924 NEW_REFERENCES.ATTRIBUTE18,
3925 NEW_REFERENCES.ATTRIBUTE19,
3926 NEW_REFERENCES.ATTRIBUTE20,
3927 NEW_REFERENCES.subtitle_id,
3928 new_references.work_load_other,
3929 new_references.contact_hrs_lecture,
3930 new_references.contact_hrs_lab,
3931 new_references.contact_hrs_other,
3932 new_references.non_schd_required_hrs,
3933 new_references.exclude_from_max_cp_limit,
3934 new_references.record_exclusion_flag,
3935 new_references.ss_display_ind,
3936 new_references.cal_type_enrol_load_cal,
3937 new_references.sequence_num_enrol_load_cal,
3938 new_references.cal_type_offer_load_cal,
3939 new_references.sequence_num_offer_load_cal,
3940 new_references.curriculum_id,
3941 new_references.override_enrollment_max,
3942 new_references.rpt_fmly_id,
3943 new_references.unit_type_id,
3944 new_references.special_permission_ind,
3945 x_last_update_date,
3946 x_last_updated_by,
3947 x_last_update_date,
3948 x_last_updated_by,
3949 x_last_update_login,
3950 new_references.org_id,
3951 new_references.ss_enrol_ind,
3952 new_references.ivr_enrol_ind,
3953 new_references.rev_account_cd,
3954 new_references.claimable_hours,
3955 new_references.anon_unit_grading_ind,
3956 new_references.anon_assess_grading_ind,
3957 new_references.auditable_ind,
3958 new_references.audit_permission_ind,
3959 new_references.max_auditors_allowed,
3960 new_references.billing_credit_points,
3961 new_references.ovrd_wkld_val_flag,
3962 new_references.workload_val_code,
3963 new_references.billing_hrs
3964 );
3965
3966 open c;
3967 fetch c into X_ROWID;
3968 if (c%notfound) then
3969 close c;
3970 raise no_data_found;
3971 end if;
3972 close c;
3973 After_DML (
3974 p_action => 'INSERT',
3975 x_rowid => X_ROWID
3976 );
3977
3978 END insert_row;
3979
3980 PROCEDURE lock_row (
3981 X_ROWID in VARCHAR2,
3982 X_UNIT_CD in VARCHAR2,
3983 X_VERSION_NUMBER in NUMBER,
3984 X_START_DT in DATE,
3985 X_REVIEW_DT in DATE,
3986 X_EXPIRY_DT in DATE,
3987 X_END_DT in DATE,
3988 X_UNIT_STATUS in VARCHAR2,
3989 X_TITLE in VARCHAR2,
3990 X_SHORT_TITLE in VARCHAR2,
3991 X_TITLE_OVERRIDE_IND in VARCHAR2,
3992 X_ABBREVIATION in VARCHAR2,
3993 X_UNIT_LEVEL in VARCHAR2,
3994 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
3995 X_ENROLLED_CREDIT_POINTS in NUMBER,
3996 X_POINTS_OVERRIDE_IND in VARCHAR2,
3997 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
3998 X_COORD_PERSON_ID in NUMBER,
3999 X_OWNER_ORG_UNIT_CD in VARCHAR2,
4000 X_OWNER_OU_START_DT in DATE,
4001 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
4002 X_RESEARCH_UNIT_IND in VARCHAR2,
4003 X_INDUSTRIAL_IND in VARCHAR2,
4004 X_PRACTICAL_IND in VARCHAR2,
4005 X_REPEATABLE_IND in VARCHAR2,
4006 X_ASSESSABLE_IND in VARCHAR2,
4007 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
4008 X_POINTS_INCREMENT in NUMBER,
4009 X_POINTS_MIN in NUMBER,
4010 X_POINTS_MAX in NUMBER,
4011 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
4012 X_SUBTITLE in VARCHAR2,
4013 X_SUBTITLE_MODIFIABLE_FLAG in VARCHAR2,
4014 X_APPROVAL_DATE in DATE,
4015 X_LECTURE_CREDIT_POINTS in NUMBER,
4016 X_LAB_CREDIT_POINTS in NUMBER,
4017 X_OTHER_CREDIT_POINTS in NUMBER,
4018 X_CLOCK_HOURS in NUMBER,
4019 X_WORK_LOAD_CP_LECTURE in NUMBER,
4020 X_WORK_LOAD_CP_LAB in NUMBER,
4021 X_CONTINUING_EDUCATION_UNITS in NUMBER,
4022 X_ENROLLMENT_EXPECTED in NUMBER,
4023 X_ENROLLMENT_MINIMUM in NUMBER,
4024 X_ENROLLMENT_MAXIMUM in NUMBER,
4025 X_ADVANCE_MAXIMUM in NUMBER,
4026 X_STATE_FINANCIAL_AID in VARCHAR2,
4027 X_FEDERAL_FINANCIAL_AID in VARCHAR2,
4028 X_INSTITUTIONAL_FINANCIAL_AID in VARCHAR2,
4029 X_SAME_TEACHING_PERIOD in VARCHAR2,
4030 X_MAX_REPEATS_FOR_CREDIT in NUMBER,
4031 X_MAX_REPEATS_FOR_FUNDING in NUMBER,
4032 X_MAX_REPEAT_CREDIT_POINTS in NUMBER,
4033 X_SAME_TEACH_PERIOD_REPEATS in NUMBER,
4034 X_SAME_TEACH_PERIOD_REPEATS_CP in NUMBER,
4035 X_ATTRIBUTE_CATEGORY in VARCHAR2,
4036 X_ATTRIBUTE1 in VARCHAR2,
4037 X_ATTRIBUTE2 in VARCHAR2,
4038 X_ATTRIBUTE3 in VARCHAR2,
4039 X_ATTRIBUTE4 in VARCHAR2,
4040 X_ATTRIBUTE5 in VARCHAR2,
4041 X_ATTRIBUTE6 in VARCHAR2,
4042 X_ATTRIBUTE7 in VARCHAR2,
4043 X_ATTRIBUTE8 in VARCHAR2,
4044 X_ATTRIBUTE9 in VARCHAR2,
4045 X_ATTRIBUTE10 in VARCHAR2,
4046 X_ATTRIBUTE11 in VARCHAR2,
4047 X_ATTRIBUTE12 in VARCHAR2,
4048 X_ATTRIBUTE13 in VARCHAR2,
4049 X_ATTRIBUTE14 in VARCHAR2,
4050 X_ATTRIBUTE15 in VARCHAR2,
4051 X_ATTRIBUTE16 in VARCHAR2,
4052 X_ATTRIBUTE17 in VARCHAR2,
4053 X_ATTRIBUTE18 in VARCHAR2,
4054 X_ATTRIBUTE19 in VARCHAR2,
4055 X_ATTRIBUTE20 in VARCHAR2,
4056 x_subtitle_id IN NUMBER ,
4057 x_work_load_other IN NUMBER ,
4058 x_contact_hrs_lecture IN NUMBER ,
4059 x_contact_hrs_lab IN NUMBER ,
4060 x_contact_hrs_other IN NUMBER ,
4061 x_non_schd_required_hrs IN NUMBER ,
4062 x_exclude_from_max_cp_limit IN VARCHAR2 ,
4063 x_record_exclusion_flag IN VARCHAR2 ,
4064 x_ss_display_ind IN VARCHAR2 ,
4065 x_cal_type_enrol_load_cal IN VARCHAR2 ,
4066 x_sequence_num_enrol_load_cal IN NUMBER ,
4067 x_cal_type_offer_load_cal IN VARCHAR2 ,
4068 x_sequence_num_offer_load_cal IN NUMBER ,
4069 x_curriculum_id IN VARCHAR2 ,
4070 x_override_enrollment_max IN NUMBER ,
4071 x_rpt_fmly_id IN NUMBER ,
4072 x_unit_type_id IN NUMBER ,
4073 x_special_permission_ind IN VARCHAR2 ,
4074 X_SS_ENROL_IND in VARCHAR2 ,
4075 X_IVR_ENROL_IND in VARCHAR2 ,
4076 x_claimable_hours IN NUMBER ,
4077 x_rev_account_cd IN VARCHAR2 ,
4078 x_anon_unit_grading_ind IN VARCHAR2 ,
4079 x_anon_assess_grading_ind IN VARCHAR2 ,
4080 X_AUDITABLE_IND IN VARCHAR2,
4081 X_AUDIT_PERMISSION_IND IN VARCHAR2,
4082 X_MAX_AUDITORS_ALLOWED IN NUMBER,
4083 x_billing_credit_points IN NUMBER,
4084 x_ovrd_wkld_val_flag IN VARCHAR2 ,
4085 x_workload_val_code IN VARCHAR2 ,
4086 x_billing_hrs IN NUMBER
4087 ) AS
4088 ------------------------------------------------------------------
4089 --Purpose: As per enhancement bug no.1775394 , the column subtitle in
4090 -- igs_ps_unit_ver_all is obsoleted . The reference to it was
4091 -- removed .
4092 --
4093 --
4094 --Known limitations/enhancements and/or remarks:
4095 --
4096 --Change History:
4097 --Who When What
4098 --smvk 27-Jun-2003 Bug # 3011578. Truncating start_date, end_date, expiry_date, approval_date and review date.
4099 --smadathi 13-JUN-2001 refer purpose
4100 -------------------------------------------------------------------
4101 cursor c1 is select
4102 START_DT,
4103 REVIEW_DT,
4104 EXPIRY_DT,
4105 END_DT,
4106 UNIT_STATUS,
4107 TITLE,
4108 SHORT_TITLE,
4109 TITLE_OVERRIDE_IND,
4110 ABBREVIATION,
4111 UNIT_LEVEL,
4112 CREDIT_POINT_DESCRIPTOR,
4113 ENROLLED_CREDIT_POINTS,
4114 POINTS_OVERRIDE_IND,
4115 SUPP_EXAM_PERMITTED_IND,
4116 COORD_PERSON_ID,
4117 OWNER_ORG_UNIT_CD,
4118 OWNER_OU_START_DT,
4119 AWARD_COURSE_ONLY_IND,
4120 RESEARCH_UNIT_IND,
4121 INDUSTRIAL_IND,
4122 PRACTICAL_IND,
4123 REPEATABLE_IND,
4124 ASSESSABLE_IND,
4125 ACHIEVABLE_CREDIT_POINTS,
4126 POINTS_INCREMENT,
4127 POINTS_MIN,
4128 POINTS_MAX,
4129 UNIT_INT_COURSE_LEVEL_CD,
4130 SUBTITLE_MODIFIABLE_FLAG,
4131 APPROVAL_DATE,
4132 LECTURE_CREDIT_POINTS,
4133 LAB_CREDIT_POINTS,
4134 OTHER_CREDIT_POINTS,
4135 CLOCK_HOURS,
4136 WORK_LOAD_CP_LECTURE,
4137 WORK_LOAD_CP_LAB,
4138 CONTINUING_EDUCATION_UNITS,
4139 ENROLLMENT_EXPECTED,
4140 ENROLLMENT_MINIMUM,
4141 ENROLLMENT_MAXIMUM,
4142 ADVANCE_MAXIMUM,
4143 STATE_FINANCIAL_AID,
4144 FEDERAL_FINANCIAL_AID,
4145 INSTITUTIONAL_FINANCIAL_AID,
4146 SAME_TEACHING_PERIOD,
4147 MAX_REPEATS_FOR_CREDIT,
4148 MAX_REPEATS_FOR_FUNDING,
4149 MAX_REPEAT_CREDIT_POINTS,
4150 SAME_TEACH_PERIOD_REPEATS,
4151 SAME_TEACH_PERIOD_REPEATS_CP,
4152 ATTRIBUTE_CATEGORY,
4153 ATTRIBUTE1,
4154 ATTRIBUTE2,
4155 ATTRIBUTE3,
4156 ATTRIBUTE4,
4157 ATTRIBUTE5,
4158 ATTRIBUTE6,
4159 ATTRIBUTE7,
4160 ATTRIBUTE8,
4161 ATTRIBUTE9,
4162 ATTRIBUTE10,
4163 ATTRIBUTE11,
4164 ATTRIBUTE12,
4165 ATTRIBUTE13,
4166 ATTRIBUTE14,
4167 ATTRIBUTE15,
4168 ATTRIBUTE16,
4169 ATTRIBUTE17,
4170 ATTRIBUTE18,
4171 ATTRIBUTE19,
4172 ATTRIBUTE20,
4173 subtitle_id,
4174 work_load_other,
4175 contact_hrs_lecture,
4176 contact_hrs_lab,
4177 contact_hrs_other,
4178 non_schd_required_hrs,
4179 exclude_from_max_cp_limit,
4180 record_exclusion_flag,
4181 ss_display_ind,
4182 cal_type_enrol_load_cal,
4183 sequence_num_enrol_load_cal,
4184 cal_type_offer_load_cal,
4185 sequence_num_offer_load_cal,
4186 curriculum_id,
4187 override_enrollment_max,
4188 rpt_fmly_id,
4189 unit_type_id,
4190 special_permission_ind,
4191 SS_ENROL_IND,
4192 IVR_ENROL_IND,
4193 rev_account_cd,
4194 claimable_hours ,
4195 anon_unit_grading_ind,
4196 anon_assess_grading_ind,
4197 auditable_ind,
4198 audit_permission_ind,
4199 max_auditors_allowed,
4200 billing_credit_points,
4201 ovrd_wkld_val_flag,
4202 workload_val_code,
4203 billing_hrs
4204 FROM IGS_PS_UNIT_VER_ALL
4205 WHERE ROWID = X_ROWID for update nowait;
4206 tlinfo c1%rowtype;
4207
4208 begin
4209 open c1;
4210 fetch c1 into tlinfo;
4211 if (c1%notfound) then
4212 close c1;
4213 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
4214 IGS_GE_MSG_STACK.ADD;
4215 app_exception.raise_exception;
4216 return;
4217 end if;
4218 close c1;
4219
4220 if (
4221 (TRUNC(tlinfo.START_DT) = TRUNC(X_START_DT))
4222 AND ((TRUNC(tlinfo.REVIEW_DT) = TRUNC(X_REVIEW_DT))
4223 OR ((tlinfo.REVIEW_DT is null)
4224 AND (X_REVIEW_DT is null)))
4225 AND ((TRUNC(tlinfo.EXPIRY_DT) = TRUNC(X_EXPIRY_DT))
4226 OR ((tlinfo.EXPIRY_DT is null)
4227 AND (X_EXPIRY_DT is null)))
4228 AND ((TRUNC(tlinfo.END_DT) = TRUNC(X_END_DT))
4229 OR ((tlinfo.END_DT is null)
4230 AND (X_END_DT is null)))
4231 AND (tlinfo.UNIT_STATUS = X_UNIT_STATUS)
4232 AND (tlinfo.TITLE = X_TITLE)
4233 AND (tlinfo.SHORT_TITLE = X_SHORT_TITLE)
4234 AND (tlinfo.TITLE_OVERRIDE_IND = X_TITLE_OVERRIDE_IND)
4235 AND (tlinfo.ABBREVIATION = X_ABBREVIATION)
4236 AND (tlinfo.UNIT_LEVEL = X_UNIT_LEVEL)
4237 AND (tlinfo.CREDIT_POINT_DESCRIPTOR = X_CREDIT_POINT_DESCRIPTOR)
4238 AND (tlinfo.ENROLLED_CREDIT_POINTS = X_ENROLLED_CREDIT_POINTS)
4239 AND (tlinfo.POINTS_OVERRIDE_IND = X_POINTS_OVERRIDE_IND)
4240 AND (tlinfo.SUPP_EXAM_PERMITTED_IND = X_SUPP_EXAM_PERMITTED_IND)
4241 AND (tlinfo.COORD_PERSON_ID = X_COORD_PERSON_ID)
4242 AND (tlinfo.OWNER_ORG_UNIT_CD = X_OWNER_ORG_UNIT_CD)
4243 AND (tlinfo.OWNER_OU_START_DT = X_OWNER_OU_START_DT)
4244 AND (tlinfo.AWARD_COURSE_ONLY_IND = X_AWARD_COURSE_ONLY_IND)
4245 AND (tlinfo.RESEARCH_UNIT_IND = X_RESEARCH_UNIT_IND)
4246 AND (tlinfo.INDUSTRIAL_IND = X_INDUSTRIAL_IND)
4247 AND (tlinfo.PRACTICAL_IND = X_PRACTICAL_IND)
4248 AND (tlinfo.REPEATABLE_IND = X_REPEATABLE_IND)
4249 AND (tlinfo.ASSESSABLE_IND = X_ASSESSABLE_IND)
4250 AND ((tlinfo.ACHIEVABLE_CREDIT_POINTS = X_ACHIEVABLE_CREDIT_POINTS)
4251 OR ((tlinfo.ACHIEVABLE_CREDIT_POINTS is null)
4252 AND (X_ACHIEVABLE_CREDIT_POINTS is null)))
4253 AND ((tlinfo.POINTS_INCREMENT = X_POINTS_INCREMENT)
4254 OR ((tlinfo.POINTS_INCREMENT is null)
4255 AND (X_POINTS_INCREMENT is null)))
4256 AND ((tlinfo.POINTS_MIN = X_POINTS_MIN)
4257 OR ((tlinfo.POINTS_MIN is null)
4258 AND (X_POINTS_MIN is null)))
4259 AND ((tlinfo.POINTS_MAX = X_POINTS_MAX)
4260 OR ((tlinfo.POINTS_MAX is null)
4261 AND (X_POINTS_MAX is null)))
4262 AND ((tlinfo.UNIT_INT_COURSE_LEVEL_CD = X_UNIT_INT_COURSE_LEVEL_CD)
4263 OR ((tlinfo.UNIT_INT_COURSE_LEVEL_CD is null)
4264 AND (X_UNIT_INT_COURSE_LEVEL_CD is null)))
4265 AND ((tlinfo.SUBTITLE_MODIFIABLE_FLAG = X_SUBTITLE_MODIFIABLE_FLAG )
4266 OR ((tlinfo.SUBTITLE_MODIFIABLE_FLAG is null)
4267 AND (X_SUBTITLE_MODIFIABLE_FLAG is null)))
4268 AND ((TRUNC(tlinfo.APPROVAL_DATE) = TRUNC(X_APPROVAL_DATE) )
4269 OR ((tlinfo.APPROVAL_DATE is null)
4270 AND (X_APPROVAL_DATE is null)))
4271 AND ((tlinfo.LECTURE_CREDIT_POINTS = X_LECTURE_CREDIT_POINTS )
4272 OR ((tlinfo.LECTURE_CREDIT_POINTS is null)
4273 AND (X_LECTURE_CREDIT_POINTS is null)))
4274 AND ((tlinfo.LAB_CREDIT_POINTS = X_LAB_CREDIT_POINTS )
4275 OR ((tlinfo.LAB_CREDIT_POINTS is null)
4276 AND (X_LAB_CREDIT_POINTS is null)))
4277 AND ((tlinfo.OTHER_CREDIT_POINTS = X_OTHER_CREDIT_POINTS )
4278 OR ((tlinfo.OTHER_CREDIT_POINTS is null)
4279 AND (X_OTHER_CREDIT_POINTS is null)))
4280 AND ((tlinfo.CLOCK_HOURS = X_CLOCK_HOURS )
4281 OR ((tlinfo.CLOCK_HOURS is null)
4282 AND (X_CLOCK_HOURS is null)))
4283 AND ((tlinfo.WORK_LOAD_CP_LECTURE = X_WORK_LOAD_CP_LECTURE )
4284 OR ((tlinfo.WORK_LOAD_CP_LECTURE is null)
4285 AND (X_WORK_LOAD_CP_LECTURE is null)))
4286 AND ((tlinfo.WORK_LOAD_CP_LAB = X_WORK_LOAD_CP_LAB )
4287 OR ((tlinfo.WORK_LOAD_CP_LAB is null)
4288 AND (X_WORK_LOAD_CP_LAB is null)))
4289 AND ((tlinfo.CONTINUING_EDUCATION_UNITS = X_CONTINUING_EDUCATION_UNITS )
4290 OR ((tlinfo.CONTINUING_EDUCATION_UNITS is null)
4291 AND (X_CONTINUING_EDUCATION_UNITS is null)))
4292 AND ((tlinfo.ENROLLMENT_EXPECTED = X_ENROLLMENT_EXPECTED )
4293 OR ((tlinfo.ENROLLMENT_EXPECTED is null)
4294 AND (X_ENROLLMENT_EXPECTED is null)))
4295 AND ((tlinfo.ENROLLMENT_MINIMUM = X_ENROLLMENT_MINIMUM )
4296 OR ((tlinfo.ENROLLMENT_MINIMUM is null)
4297 AND (X_ENROLLMENT_MINIMUM is null)))
4298 AND ((tlinfo.ENROLLMENT_MAXIMUM = X_ENROLLMENT_MAXIMUM )
4299 OR ((tlinfo.ENROLLMENT_MAXIMUM is null)
4300 AND (X_ENROLLMENT_MAXIMUM is null)))
4301 AND ((tlinfo.ADVANCE_MAXIMUM = X_ADVANCE_MAXIMUM )
4302 OR ((tlinfo.ADVANCE_MAXIMUM is null)
4303 AND (X_ADVANCE_MAXIMUM is null)))
4304 AND ((tlinfo.STATE_FINANCIAL_AID = X_STATE_FINANCIAL_AID )
4305 OR ((tlinfo.STATE_FINANCIAL_AID is null)
4306 AND (X_STATE_FINANCIAL_AID is null)))
4307 AND ((tlinfo.FEDERAL_FINANCIAL_AID = X_FEDERAL_FINANCIAL_AID )
4308 OR ((tlinfo.FEDERAL_FINANCIAL_AID is null)
4309 AND (X_FEDERAL_FINANCIAL_AID is null)))
4310 AND ((tlinfo.INSTITUTIONAL_FINANCIAL_AID = X_INSTITUTIONAL_FINANCIAL_AID )
4311 OR ((tlinfo.INSTITUTIONAL_FINANCIAL_AID is null)
4312 AND (X_INSTITUTIONAL_FINANCIAL_AID is null)))
4313 AND ((tlinfo.SAME_TEACHING_PERIOD = X_SAME_TEACHING_PERIOD )
4314 OR ((tlinfo.SAME_TEACHING_PERIOD is null)
4315 AND (X_SAME_TEACHING_PERIOD is null)))
4316 AND ((tlinfo.MAX_REPEATS_FOR_CREDIT = X_MAX_REPEATS_FOR_CREDIT )
4317 OR ((tlinfo.MAX_REPEATS_FOR_CREDIT is null)
4318 AND (X_MAX_REPEATS_FOR_CREDIT is null)))
4319 AND ((tlinfo.MAX_REPEATS_FOR_FUNDING = X_MAX_REPEATS_FOR_FUNDING )
4320 OR ((tlinfo.MAX_REPEATS_FOR_FUNDING is null)
4321 AND (X_MAX_REPEATS_FOR_FUNDING is null)))
4322 AND ((tlinfo.MAX_REPEAT_CREDIT_POINTS = X_MAX_REPEAT_CREDIT_POINTS )
4323 OR ((tlinfo.MAX_REPEAT_CREDIT_POINTS is null)
4324 AND (X_MAX_REPEAT_CREDIT_POINTS is null)))
4325 AND ((tlinfo.SAME_TEACH_PERIOD_REPEATS = X_SAME_TEACH_PERIOD_REPEATS )
4326 OR ((tlinfo.SAME_TEACH_PERIOD_REPEATS is null)
4327 AND (X_SAME_TEACH_PERIOD_REPEATS is null)))
4328 AND ((tlinfo.SAME_TEACH_PERIOD_REPEATS_CP = X_SAME_TEACH_PERIOD_REPEATS_CP )
4329 OR ((tlinfo.SAME_TEACH_PERIOD_REPEATS_CP is null)
4330 AND (X_SAME_TEACH_PERIOD_REPEATS_CP is null)))
4331 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
4332 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
4333 AND (X_ATTRIBUTE_CATEGORY is null)))
4334 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
4335 OR ((tlinfo.ATTRIBUTE1 is null)
4336 AND (X_ATTRIBUTE1 is null)))
4337 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2 )
4338 OR ((tlinfo.ATTRIBUTE2 is null)
4339 AND (X_ATTRIBUTE2 is null)))
4340 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3 )
4341 OR ((tlinfo.ATTRIBUTE3 is null)
4342 AND (X_ATTRIBUTE3 is null)))
4343 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4 )
4344 OR ((tlinfo.ATTRIBUTE4 is null)
4345 AND (X_ATTRIBUTE4 is null)))
4346 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5 )
4347 OR ((tlinfo.ATTRIBUTE5 is null)
4348 AND (X_ATTRIBUTE5 is null)))
4349 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6 )
4350 OR ((tlinfo.ATTRIBUTE6 is null)
4351 AND (X_ATTRIBUTE6 is null)))
4352 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7 )
4353 OR ((tlinfo.ATTRIBUTE7 is null)
4354 AND (X_ATTRIBUTE7 is null)))
4355 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8 )
4356 OR ((tlinfo.ATTRIBUTE8 is null)
4357 AND (X_ATTRIBUTE8 is null)))
4358 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9 )
4359 OR ((tlinfo.ATTRIBUTE9 is null)
4360 AND (X_ATTRIBUTE9 is null)))
4361 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10 )
4362 OR ((tlinfo.ATTRIBUTE10 is null)
4363 AND (X_ATTRIBUTE10 is null)))
4364 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11 )
4365 OR ((tlinfo.ATTRIBUTE11 is null)
4366 AND (X_ATTRIBUTE11 is null)))
4367 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
4368 OR ((tlinfo.ATTRIBUTE12 is null)
4369 AND (X_ATTRIBUTE12 is null)))
4370 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13 )
4371 OR ((tlinfo.ATTRIBUTE13 is null)
4372 AND (X_ATTRIBUTE13 is null)))
4373 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14 )
4374 OR ((tlinfo.ATTRIBUTE14 is null)
4375 AND (X_ATTRIBUTE14 is null)))
4376 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15 )
4377 OR ((tlinfo.ATTRIBUTE15 is null)
4378 AND (X_ATTRIBUTE15 is null)))
4379 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16 )
4380 OR ((tlinfo.ATTRIBUTE16 is null)
4381 AND (X_ATTRIBUTE16 is null)))
4382 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17 )
4383 OR ((tlinfo.ATTRIBUTE17 is null)
4384 AND (X_ATTRIBUTE17 is null)))
4385 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18 )
4386 OR ((tlinfo.ATTRIBUTE18 is null)
4387 AND (X_ATTRIBUTE18 is null)))
4388 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19 )
4389 OR ((tlinfo.ATTRIBUTE19 is null)
4390 AND (X_ATTRIBUTE19 is null)))
4391 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20 )
4392 OR ((tlinfo.ATTRIBUTE20 is null)
4393 AND (X_ATTRIBUTE20 is null)))
4394 AND ((tlinfo.subtitle_id = x_subtitle_id)
4395 OR ((tlinfo.subtitle_id IS NULL)
4396 AND (X_subtitle_id IS NULL)))
4397 AND ((tlinfo.work_load_other = x_work_load_other)
4398 OR ((tlinfo.work_load_other IS NULL)
4399 AND (X_work_load_other IS NULL)))
4400 AND ((tlinfo.contact_hrs_lecture = x_contact_hrs_lecture)
4401 OR ((tlinfo.contact_hrs_lecture IS NULL)
4402 AND (X_contact_hrs_lecture IS NULL)))
4403 AND ((tlinfo.contact_hrs_lab = x_contact_hrs_lab)
4404 OR ((tlinfo.contact_hrs_lab IS NULL)
4405 AND (X_contact_hrs_lab IS NULL)))
4406 AND ((tlinfo.contact_hrs_other = x_contact_hrs_other)
4407 OR ((tlinfo.contact_hrs_other IS NULL)
4408 AND (X_contact_hrs_other IS NULL)))
4409 AND ((tlinfo.non_schd_required_hrs = x_non_schd_required_hrs)
4410 OR ((tlinfo.non_schd_required_hrs IS NULL)
4411 AND (X_non_schd_required_hrs IS NULL)))
4412 AND ((tlinfo.exclude_from_max_cp_limit = x_exclude_from_max_cp_limit)
4413 OR ((tlinfo.exclude_from_max_cp_limit IS NULL)
4414 AND (X_exclude_from_max_cp_limit IS NULL)))
4415 AND ((tlinfo.record_exclusion_flag = x_record_exclusion_flag)
4416 OR ((tlinfo.record_exclusion_flag IS NULL)
4417 AND (X_record_exclusion_flag IS NULL)))
4418 AND ((tlinfo.ss_display_ind = x_ss_display_ind)
4419 OR ((tlinfo.ss_display_ind IS NULL)
4420 AND (X_ss_display_ind IS NULL)))
4421 AND ((tlinfo.cal_type_enrol_load_cal = x_cal_type_enrol_load_cal)
4422 OR ((tlinfo.cal_type_enrol_load_cal IS NULL)
4423 AND (X_cal_type_enrol_load_cal IS NULL)))
4424 AND ((tlinfo.sequence_num_enrol_load_cal = x_sequence_num_enrol_load_cal)
4425 OR ((tlinfo.sequence_num_enrol_load_cal IS NULL)
4426 AND (X_sequence_num_enrol_load_cal IS NULL)))
4427 AND ((tlinfo.cal_type_offer_load_cal = x_cal_type_offer_load_cal)
4428 OR ((tlinfo.cal_type_offer_load_cal IS NULL)
4429 AND (X_cal_type_offer_load_cal IS NULL)))
4430 AND ((tlinfo.sequence_num_offer_load_cal = x_sequence_num_offer_load_cal)
4431 OR ((tlinfo.sequence_num_offer_load_cal IS NULL)
4432 AND (X_sequence_num_offer_load_cal IS NULL)))
4433 AND ((tlinfo.curriculum_id = x_curriculum_id)
4434 OR ((tlinfo.curriculum_id IS NULL)
4435 AND (X_curriculum_id IS NULL)))
4436 AND ((tlinfo.override_enrollment_max = x_override_enrollment_max)
4437 OR ((tlinfo.override_enrollment_max IS NULL)
4438 AND (X_override_enrollment_max IS NULL)))
4439 AND ((tlinfo.rpt_fmly_id = x_rpt_fmly_id)
4440 OR ((tlinfo.rpt_fmly_id IS NULL)
4441 AND (X_rpt_fmly_id IS NULL)))
4442 AND ((tlinfo.unit_type_id = x_unit_type_id)
4443 OR ((tlinfo.unit_type_id IS NULL)
4444 AND (X_unit_type_id IS NULL)))
4445 AND ((tlinfo.special_permission_ind = x_special_permission_ind)
4446 OR ((tlinfo.special_permission_ind IS NULL)
4447 AND (X_special_permission_ind IS NULL)))
4448 AND ((tlinfo.SS_ENROL_IND = X_SS_ENROL_IND)
4449 OR ((tlinfo.SS_ENROL_IND is null)
4450 AND (X_SS_ENROL_IND is null)))
4451 AND ((tlinfo.IVR_ENROL_IND = X_IVR_ENROL_IND)
4452 OR ((tlinfo.IVR_ENROL_IND is null)
4453 AND (X_IVR_ENROL_IND is null)))
4454 AND ((tlinfo.REV_ACCOUNT_CD = X_REV_ACCOUNT_CD)
4455 OR ((tlinfo.REV_ACCOUNT_CD is null)
4456 AND (X_REV_ACCOUNT_CD is null)))
4457 AND ((tlinfo.CLAIMABLE_HOURS= X_CLAIMABLE_HOURS)
4458 OR ((tlinfo.CLAIMABLE_HOURS is null)
4459 AND (X_CLAIMABLE_HOURS is null)))
4460 AND ((tlinfo.ANON_UNIT_GRADING_IND= X_ANON_UNIT_GRADING_IND)
4461 OR ((tlinfo.ANON_UNIT_GRADING_IND is null)
4462 AND (X_ANON_UNIT_GRADING_IND is null)))
4463 AND ((tlinfo.ANON_ASSESS_GRADING_IND= X_ANON_ASSESS_GRADING_IND)
4464 OR ((tlinfo.ANON_ASSESS_GRADING_IND is null)
4465 AND (X_ANON_ASSESS_GRADING_IND is null)))
4466 AND ((tlinfo.AUDITABLE_IND= X_AUDITABLE_IND)
4467 OR ((tlinfo.AUDITABLE_IND is null)
4468 AND (X_AUDITABLE_IND is null)))
4469 AND ((tlinfo.AUDIT_PERMISSION_IND= X_AUDIT_PERMISSION_IND)
4470 OR ((tlinfo.AUDIT_PERMISSION_IND is null)
4471 AND (X_AUDIT_PERMISSION_IND is null)))
4472 AND ((tlinfo.MAX_AUDITORS_ALLOWED= X_MAX_AUDITORS_ALLOWED)
4473 OR ((tlinfo.MAX_AUDITORS_ALLOWED is null)
4474 AND (X_MAX_AUDITORS_ALLOWED is null)))
4475 AND ((tlinfo.BILLING_CREDIT_POINTS= X_BILLING_CREDIT_POINTS)
4476 OR ((tlinfo.BILLING_CREDIT_POINTS IS NULL)
4477 AND (X_BILLING_CREDIT_POINTS IS NULL)))
4478 AND ((tlinfo.OVRD_WKLD_VAL_FLAG= X_OVRD_WKLD_VAL_FLAG)
4479 OR ((tlinfo.OVRD_WKLD_VAL_FLAG IS NULL)
4480 AND (X_OVRD_WKLD_VAL_FLAG IS NULL)))
4481 AND ((tlinfo.WORKLOAD_VAL_CODE= X_WORKLOAD_VAL_CODE)
4482 OR ((tlinfo.WORKLOAD_VAL_CODE IS NULL)
4483 AND (X_WORKLOAD_VAL_CODE IS NULL)))
4484 AND ((tlinfo.BILLING_HRS= X_BILLING_HRS)
4485 OR ((tlinfo.BILLING_HRS IS NULL)
4486 AND (X_BILLING_HRS IS NULL)))
4487 ) then
4488 null;
4489 else
4490 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
4491 IGS_GE_MSG_STACK.ADD;
4492 app_exception.raise_exception;
4493 end if;
4494 return;
4495 END lock_row;
4496 -----------------------------------------------------------------------------
4497 PROCEDURE update_row (
4498 X_ROWID in VARCHAR2,
4499 X_UNIT_CD in VARCHAR2,
4500 X_VERSION_NUMBER in NUMBER,
4501 X_START_DT in DATE,
4502 X_REVIEW_DT in DATE,
4503 X_EXPIRY_DT in DATE,
4504 X_END_DT in DATE,
4505 X_UNIT_STATUS in VARCHAR2,
4506 X_TITLE in VARCHAR2,
4507 X_SHORT_TITLE in VARCHAR2,
4508 X_TITLE_OVERRIDE_IND in VARCHAR2,
4509 X_ABBREVIATION in VARCHAR2,
4510 X_UNIT_LEVEL in VARCHAR2,
4511 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
4512 X_ENROLLED_CREDIT_POINTS in NUMBER,
4513 X_POINTS_OVERRIDE_IND in VARCHAR2,
4514 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
4515 X_COORD_PERSON_ID in NUMBER,
4516 X_OWNER_ORG_UNIT_CD in VARCHAR2,
4517 X_OWNER_OU_START_DT in DATE,
4518 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
4519 X_RESEARCH_UNIT_IND in VARCHAR2,
4520 X_INDUSTRIAL_IND in VARCHAR2,
4521 X_PRACTICAL_IND in VARCHAR2,
4522 X_REPEATABLE_IND in VARCHAR2,
4523 X_ASSESSABLE_IND in VARCHAR2,
4524 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
4525 X_POINTS_INCREMENT in NUMBER,
4526 X_POINTS_MIN in NUMBER,
4527 X_POINTS_MAX in NUMBER,
4528 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
4529 X_SUBTITLE in VARCHAR2,
4530 X_SUBTITLE_MODIFIABLE_FLAG in VARCHAR2,
4531 X_APPROVAL_DATE in DATE,
4532 X_LECTURE_CREDIT_POINTS in NUMBER,
4533 X_LAB_CREDIT_POINTS in NUMBER,
4534 X_OTHER_CREDIT_POINTS in NUMBER,
4535 X_CLOCK_HOURS in NUMBER,
4536 X_WORK_LOAD_CP_LECTURE in NUMBER,
4537 X_WORK_LOAD_CP_LAB in NUMBER,
4538 X_CONTINUING_EDUCATION_UNITS in NUMBER,
4539 X_ENROLLMENT_EXPECTED in NUMBER,
4540 X_ENROLLMENT_MINIMUM in NUMBER,
4541 X_ENROLLMENT_MAXIMUM in NUMBER,
4542 X_ADVANCE_MAXIMUM in NUMBER,
4543 X_STATE_FINANCIAL_AID in VARCHAR2,
4544 X_FEDERAL_FINANCIAL_AID in VARCHAR2,
4545 X_INSTITUTIONAL_FINANCIAL_AID in VARCHAR2,
4546 X_SAME_TEACHING_PERIOD in VARCHAR2,
4547 X_MAX_REPEATS_FOR_CREDIT in NUMBER,
4548 X_MAX_REPEATS_FOR_FUNDING in NUMBER,
4549 X_MAX_REPEAT_CREDIT_POINTS in NUMBER,
4550 X_SAME_TEACH_PERIOD_REPEATS in NUMBER,
4551 X_SAME_TEACH_PERIOD_REPEATS_CP in NUMBER,
4552 X_ATTRIBUTE_CATEGORY in VARCHAR2,
4553 X_ATTRIBUTE1 in VARCHAR2,
4554 X_ATTRIBUTE2 in VARCHAR2,
4555 X_ATTRIBUTE3 in VARCHAR2,
4556 X_ATTRIBUTE4 in VARCHAR2,
4557 X_ATTRIBUTE5 in VARCHAR2,
4558 X_ATTRIBUTE6 in VARCHAR2,
4559 X_ATTRIBUTE7 in VARCHAR2,
4560 X_ATTRIBUTE8 in VARCHAR2,
4561 X_ATTRIBUTE9 in VARCHAR2,
4562 X_ATTRIBUTE10 in VARCHAR2,
4563 X_ATTRIBUTE11 in VARCHAR2,
4564 X_ATTRIBUTE12 in VARCHAR2,
4565 X_ATTRIBUTE13 in VARCHAR2,
4566 X_ATTRIBUTE14 in VARCHAR2,
4567 X_ATTRIBUTE15 in VARCHAR2,
4568 X_ATTRIBUTE16 in VARCHAR2,
4569 X_ATTRIBUTE17 in VARCHAR2,
4570 X_ATTRIBUTE18 in VARCHAR2,
4571 X_ATTRIBUTE19 in VARCHAR2,
4572 X_ATTRIBUTE20 in VARCHAR2,
4573 x_subtitle_id IN NUMBER ,
4574 x_work_load_other IN NUMBER ,
4575 x_contact_hrs_lecture IN NUMBER ,
4576 x_contact_hrs_lab IN NUMBER ,
4577 x_contact_hrs_other IN NUMBER ,
4578 x_non_schd_required_hrs IN NUMBER ,
4579 x_exclude_from_max_cp_limit IN VARCHAR2 ,
4580 x_record_exclusion_flag IN VARCHAR2 ,
4581 x_ss_display_ind IN VARCHAR2 ,
4582 x_cal_type_enrol_load_cal IN VARCHAR2 ,
4583 x_sequence_num_enrol_load_cal IN NUMBER ,
4584 x_cal_type_offer_load_cal IN VARCHAR2 ,
4585 x_sequence_num_offer_load_cal IN NUMBER ,
4586 x_curriculum_id IN VARCHAR2 ,
4587 x_override_enrollment_max IN NUMBER ,
4588 x_rpt_fmly_id IN NUMBER ,
4589 x_unit_type_id IN NUMBER ,
4590 x_special_permission_ind IN VARCHAR2 ,
4591 X_MODE in VARCHAR2,
4592 X_SS_ENROL_IND in VARCHAR2 ,
4593 X_IVR_ENROL_IND in VARCHAR2 ,
4594 x_rev_account_cd IN VARCHAR2 ,
4595 x_claimable_hours IN NUMBER ,
4596 x_anon_unit_grading_ind IN VARCHAR2 ,
4597 x_anon_assess_grading_ind IN VARCHAR2 ,
4598 x_auditable_ind IN VARCHAR2,
4599 x_audit_permission_ind IN VARCHAR2,
4600 x_max_auditors_allowed IN NUMBER,
4601 x_billing_credit_points IN NUMBER,
4602 x_ovrd_wkld_val_flag IN VARCHAR2 ,
4603 x_workload_val_code IN VARCHAR2 ,
4604 x_billing_hrs IN NUMBER
4605 ) AS
4606 X_LAST_UPDATE_DATE DATE;
4607 X_LAST_UPDATED_BY NUMBER;
4608 X_LAST_UPDATE_LOGIN NUMBER;
4609 begin
4610 X_LAST_UPDATE_DATE := SYSDATE;
4611 if(X_MODE = 'I') then
4612 X_LAST_UPDATED_BY := 1;
4613 X_LAST_UPDATE_LOGIN := 0;
4614 elsif (X_MODE = 'R') then
4615 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4616 if X_LAST_UPDATED_BY is NULL then
4617 X_LAST_UPDATED_BY := -1;
4618 end if;
4619 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
4620 if X_LAST_UPDATE_LOGIN is NULL then
4621 X_LAST_UPDATE_LOGIN := -1;
4622 end if;
4623 else
4624 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
4625 IGS_GE_MSG_STACK.ADD;
4626 app_exception.raise_exception;
4627 end if;
4628
4629 Before_DML(
4630 p_action => 'UPDATE',
4631 x_rowid => X_ROWID,
4632 x_unit_cd => X_UNIT_CD,
4633 x_version_number => X_VERSION_NUMBER,
4634 x_start_dt => X_START_DT,
4635 x_review_dt => X_REVIEW_DT,
4636 x_expiry_dt => X_EXPIRY_DT,
4637 x_end_dt => X_END_DT,
4638 x_unit_status => X_UNIT_STATUS,
4639 x_title => X_TITLE,
4640 x_short_title => X_SHORT_TITLE,
4641 x_title_override_ind => X_TITLE_OVERRIDE_IND,
4642 x_abbreviation => X_ABBREVIATION,
4643 x_unit_level => X_UNIT_LEVEL,
4644 x_credit_point_descriptor => X_CREDIT_POINT_DESCRIPTOR,
4645 x_enrolled_credit_points => X_ENROLLED_CREDIT_POINTS,
4646 x_points_override_ind => X_POINTS_OVERRIDE_IND,
4647 x_supp_exam_permitted_ind => X_SUPP_EXAM_PERMITTED_IND,
4648 x_coord_person_id => X_COORD_PERSON_ID,
4649 x_owner_org_unit_cd => X_OWNER_ORG_UNIT_CD,
4650 x_owner_ou_start_dt => X_OWNER_OU_START_DT,
4651 x_award_course_only_ind => X_AWARD_COURSE_ONLY_IND,
4652 x_research_unit_ind => X_RESEARCH_UNIT_IND,
4653 x_industrial_ind => X_INDUSTRIAL_IND,
4654 x_practical_ind => X_PRACTICAL_IND,
4655 x_repeatable_ind => X_REPEATABLE_IND,
4656 x_assessable_ind => X_ASSESSABLE_IND,
4657 x_achievable_credit_points => X_ACHIEVABLE_CREDIT_POINTS,
4658 x_points_increment => X_POINTS_INCREMENT,
4659 x_points_min => X_POINTS_MIN,
4660 x_points_max => X_POINTS_MAX,
4661 x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
4662 x_subtitle_modifiable_flag => X_SUBTITLE_MODIFIABLE_FLAG,
4663 x_approval_date => X_APPROVAL_DATE,
4664 x_lecture_credit_points => X_LECTURE_CREDIT_POINTS,
4665 x_lab_credit_points => X_LAB_CREDIT_POINTS,
4666 x_other_credit_points => X_OTHER_CREDIT_POINTS,
4667 x_clock_hours => X_CLOCK_HOURS,
4668 x_work_load_cp_lecture => X_WORK_LOAD_CP_LECTURE,
4669 x_work_load_cp_lab => X_WORK_LOAD_CP_LAB,
4670 x_continuing_education_units => X_CONTINUING_EDUCATION_UNITS,
4671 x_enrollment_expected => X_ENROLLMENT_EXPECTED,
4672 x_enrollment_minimum => X_ENROLLMENT_MINIMUM,
4673 x_enrollment_maximum => X_ENROLLMENT_MAXIMUM,
4674 x_advance_maximum => X_ADVANCE_MAXIMUM,
4675 x_state_financial_aid => X_STATE_FINANCIAL_AID,
4676 x_federal_financial_aid => X_FEDERAL_FINANCIAL_AID,
4677 x_institutional_financial_aid => X_INSTITUTIONAL_FINANCIAL_AID,
4678 x_same_teaching_period => X_SAME_TEACHING_PERIOD,
4679 x_max_repeats_for_credit => X_MAX_REPEATS_FOR_CREDIT,
4680 x_max_repeats_for_funding => X_MAX_REPEATS_FOR_FUNDING,
4681 x_max_repeat_credit_points => X_MAX_REPEAT_CREDIT_POINTS,
4682 x_same_teach_period_repeats => X_SAME_TEACH_PERIOD_REPEATS,
4683 x_same_teach_period_repeats_cp => X_SAME_TEACH_PERIOD_REPEATS_CP,
4684 x_attribute_category => X_ATTRIBUTE_CATEGORY,
4685 x_attribute1 => X_ATTRIBUTE1,
4686 x_attribute2 => X_ATTRIBUTE2,
4687 x_attribute3 => X_ATTRIBUTE3,
4688 x_attribute4 => X_ATTRIBUTE4,
4689 x_attribute5 => X_ATTRIBUTE5,
4690 x_attribute6 => X_ATTRIBUTE6,
4691 x_attribute7 => X_ATTRIBUTE7,
4692 x_attribute8 => X_ATTRIBUTE8,
4693 x_attribute9 => X_ATTRIBUTE9,
4694 x_attribute10 => X_ATTRIBUTE10,
4695 x_attribute11 => X_ATTRIBUTE11,
4696 x_attribute12 => X_ATTRIBUTE12,
4697 x_attribute13 => X_ATTRIBUTE13,
4698 x_attribute14 => X_ATTRIBUTE14,
4699 x_attribute15 => X_ATTRIBUTE15,
4700 x_attribute16 => X_ATTRIBUTE16,
4701 x_attribute17 => X_ATTRIBUTE17,
4702 x_attribute18 => X_ATTRIBUTE18,
4703 x_attribute19 => X_ATTRIBUTE19,
4704 x_attribute20 => X_ATTRIBUTE20,
4705 x_subtitle_id => x_subtitle_id,
4706 x_work_load_other => x_work_load_other,
4707 x_contact_hrs_lecture => x_contact_hrs_lecture,
4708 x_contact_hrs_lab => x_contact_hrs_lab,
4709 x_contact_hrs_other => x_contact_hrs_other,
4710 x_non_schd_required_hrs => x_non_schd_required_hrs,
4711 x_exclude_from_max_cp_limit => x_exclude_from_max_cp_limit,
4712 x_record_exclusion_flag => x_record_exclusion_flag,
4713 x_ss_display_ind => x_ss_display_ind,
4714 x_cal_type_enrol_load_cal => x_cal_type_enrol_load_cal,
4715 x_sequence_num_enrol_load_cal => x_sequence_num_enrol_load_cal,
4716 x_cal_type_offer_load_cal => x_cal_type_offer_load_cal,
4717 x_sequence_num_offer_load_cal => x_sequence_num_offer_load_cal,
4718 x_curriculum_id => x_curriculum_id,
4719 x_override_enrollment_max => x_override_enrollment_max,
4720 x_rpt_fmly_id => x_rpt_fmly_id,
4721 x_unit_type_id => x_unit_type_id,
4722 x_special_permission_ind => x_special_permission_ind,
4723 x_creation_date => X_LAST_UPDATE_DATE,
4724 x_created_by => X_LAST_UPDATED_BY,
4725 x_last_update_date => X_LAST_UPDATE_DATE,
4726 x_last_updated_by => X_LAST_UPDATED_BY,
4727 x_last_update_login => X_LAST_UPDATE_LOGIN,
4728 x_ss_enrol_ind => X_SS_ENROL_IND,
4729 x_ivr_enrol_ind => X_IVR_ENROL_IND,
4730 x_rev_account_cd => x_rev_account_cd,
4731 x_claimable_hours => x_claimable_hours,
4732 x_anon_unit_grading_ind => x_anon_unit_grading_ind ,
4733 x_anon_assess_grading_ind => x_anon_assess_grading_ind,
4734 x_auditable_ind => x_auditable_ind,
4735 x_audit_permission_ind => x_audit_permission_ind,
4736 x_max_auditors_allowed => x_max_auditors_allowed,
4737 x_billing_credit_points => x_billing_credit_points,
4738 x_ovrd_wkld_val_flag => x_ovrd_wkld_val_flag ,
4739 x_workload_val_code => x_workload_val_code,
4740 x_billing_hrs => x_billing_hrs
4741 );
4742
4743 update IGS_PS_UNIT_VER_ALL set
4744 START_DT = NEW_REFERENCES.START_DT,
4745 REVIEW_DT = NEW_REFERENCES.REVIEW_DT,
4746 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
4747 END_DT = NEW_REFERENCES.END_DT,
4748 UNIT_STATUS = NEW_REFERENCES.UNIT_STATUS,
4749 TITLE = NEW_REFERENCES.TITLE,
4750 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
4751 TITLE_OVERRIDE_IND = NEW_REFERENCES.TITLE_OVERRIDE_IND,
4752 ABBREVIATION = NEW_REFERENCES.ABBREVIATION,
4753 UNIT_LEVEL = NEW_REFERENCES.UNIT_LEVEL,
4754 CREDIT_POINT_DESCRIPTOR = NEW_REFERENCES.CREDIT_POINT_DESCRIPTOR,
4755 ENROLLED_CREDIT_POINTS = NEW_REFERENCES.ENROLLED_CREDIT_POINTS,
4756 POINTS_OVERRIDE_IND = NEW_REFERENCES.POINTS_OVERRIDE_IND,
4757 SUPP_EXAM_PERMITTED_IND = NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
4758 COORD_PERSON_ID = NEW_REFERENCES.COORD_PERSON_ID,
4759 OWNER_ORG_UNIT_CD = NEW_REFERENCES.OWNER_ORG_UNIT_CD,
4760 OWNER_OU_START_DT = NEW_REFERENCES.OWNER_OU_START_DT,
4761 AWARD_COURSE_ONLY_IND = NEW_REFERENCES.AWARD_COURSE_ONLY_IND,
4762 RESEARCH_UNIT_IND = NEW_REFERENCES.RESEARCH_UNIT_IND,
4763 INDUSTRIAL_IND = NEW_REFERENCES.INDUSTRIAL_IND,
4764 PRACTICAL_IND = NEW_REFERENCES.PRACTICAL_IND,
4765 REPEATABLE_IND = NEW_REFERENCES.REPEATABLE_IND,
4766 ASSESSABLE_IND = NEW_REFERENCES.ASSESSABLE_IND,
4767 ACHIEVABLE_CREDIT_POINTS = NEW_REFERENCES.ACHIEVABLE_CREDIT_POINTS,
4768 POINTS_INCREMENT = NEW_REFERENCES.POINTS_INCREMENT,
4769 POINTS_MIN = NEW_REFERENCES.POINTS_MIN,
4770 POINTS_MAX = NEW_REFERENCES.POINTS_MAX,
4771 UNIT_INT_COURSE_LEVEL_CD = NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
4772 SUBTITLE_MODIFIABLE_FLAG = NEW_REFERENCES.SUBTITLE_MODIFIABLE_FLAG,
4773 APPROVAL_DATE = NEW_REFERENCES.APPROVAL_DATE,
4774 LECTURE_CREDIT_POINTS = NEW_REFERENCES.LECTURE_CREDIT_POINTS,
4775 LAB_CREDIT_POINTS = NEW_REFERENCES.LAB_CREDIT_POINTS,
4776 OTHER_CREDIT_POINTS = NEW_REFERENCES.OTHER_CREDIT_POINTS,
4777 CLOCK_HOURS = NEW_REFERENCES.CLOCK_HOURS,
4778 WORK_LOAD_CP_LECTURE = NEW_REFERENCES.WORK_LOAD_CP_LECTURE,
4779 WORK_LOAD_CP_LAB = NEW_REFERENCES.WORK_LOAD_CP_LAB,
4780 CONTINUING_EDUCATION_UNITS = NEW_REFERENCES.CONTINUING_EDUCATION_UNITS,
4781 ENROLLMENT_EXPECTED = NEW_REFERENCES.ENROLLMENT_EXPECTED,
4782 ENROLLMENT_MINIMUM = NEW_REFERENCES.ENROLLMENT_MINIMUM,
4783 ENROLLMENT_MAXIMUM = NEW_REFERENCES.ENROLLMENT_MAXIMUM,
4784 ADVANCE_MAXIMUM = NEW_REFERENCES.ADVANCE_MAXIMUM,
4785 STATE_FINANCIAL_AID = NEW_REFERENCES.STATE_FINANCIAL_AID,
4786 FEDERAL_FINANCIAL_AID = NEW_REFERENCES.FEDERAL_FINANCIAL_AID,
4787 INSTITUTIONAL_FINANCIAL_AID = NEW_REFERENCES.INSTITUTIONAL_FINANCIAL_AID,
4788 SAME_TEACHING_PERIOD = NEW_REFERENCES.SAME_TEACHING_PERIOD,
4789 MAX_REPEATS_FOR_CREDIT = NEW_REFERENCES.MAX_REPEATS_FOR_CREDIT,
4790 MAX_REPEATS_FOR_FUNDING = NEW_REFERENCES.MAX_REPEATS_FOR_FUNDING,
4791 MAX_REPEAT_CREDIT_POINTS = NEW_REFERENCES.MAX_REPEAT_CREDIT_POINTS,
4792 SAME_TEACH_PERIOD_REPEATS = NEW_REFERENCES.SAME_TEACH_PERIOD_REPEATS,
4793 SAME_TEACH_PERIOD_REPEATS_CP = NEW_REFERENCES.SAME_TEACH_PERIOD_REPEATS_CP,
4794 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
4795 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
4796 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
4797 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
4798 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
4799 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
4800 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
4801 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
4802 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
4803 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
4804 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
4805 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
4806 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
4807 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
4808 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
4809 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
4810 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
4811 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
4812 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
4813 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
4814 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
4815 subtitle_id = new_references.subtitle_id,
4816 work_load_other = new_references.work_load_other ,
4817 contact_hrs_lecture = new_references.contact_hrs_lecture,
4818 contact_hrs_lab = new_references.contact_hrs_lab,
4819 contact_hrs_other = new_references.contact_hrs_other,
4820 non_schd_required_hrs = new_references.non_schd_required_hrs,
4821 exclude_from_max_cp_limit = new_references.exclude_from_max_cp_limit,
4822 record_exclusion_flag = new_references.record_exclusion_flag,
4823 ss_display_ind = new_references.ss_display_ind,
4824 cal_type_enrol_load_cal = new_references.cal_type_enrol_load_cal,
4825 sequence_num_enrol_load_cal = new_references.sequence_num_enrol_load_cal,
4826 cal_type_offer_load_cal = new_references.cal_type_offer_load_cal,
4827 sequence_num_offer_load_cal = new_references.sequence_num_offer_load_cal,
4828 curriculum_id = new_references.curriculum_id,
4829 override_enrollment_max = new_references.override_enrollment_max,
4830 rpt_fmly_id = new_references.rpt_fmly_id,
4831 unit_type_id = new_references.unit_type_id,
4832 special_permission_ind = new_references.special_permission_ind,
4833 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
4834 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
4835 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
4836 SS_ENROL_IND = NEW_REFERENCES.SS_ENROL_IND,
4837 IVR_ENROL_IND = NEW_REFERENCES.IVR_ENROL_IND,
4838 rev_account_cd = new_references.rev_account_cd,
4839 claimable_hours = new_references.claimable_hours,
4840 anon_unit_grading_ind = new_references.anon_unit_grading_ind,
4841 anon_assess_grading_ind = new_references.anon_assess_grading_ind,
4842 auditable_ind = new_references.auditable_ind,
4843 audit_permission_ind = new_references.audit_permission_ind,
4844 max_auditors_allowed = new_references.max_auditors_allowed,
4845 billing_credit_points = new_references.billing_credit_points,
4846 ovrd_wkld_val_flag = new_references.ovrd_wkld_val_flag ,
4847 workload_val_code = new_references.workload_val_code,
4848 billing_hrs = new_references.billing_hrs
4849 WHERE ROWID = X_ROWID;
4850 IF (sql%notfound) THEN
4851 RAISE no_data_found;
4852 END IF;
4853 After_DML (
4854 p_action => 'UPDATE',
4855 x_rowid => X_ROWID
4856 );
4857
4858 END update_row;
4859 ------------------------------------------------------------------------------
4860 PROCEDURE add_row (
4861 X_ROWID in out NOCOPY VARCHAR2,
4862 X_UNIT_CD in VARCHAR2,
4863 X_VERSION_NUMBER in NUMBER,
4864 X_START_DT in DATE,
4865 X_REVIEW_DT in DATE,
4866 X_EXPIRY_DT in DATE,
4867 X_END_DT in DATE,
4868 X_UNIT_STATUS in VARCHAR2,
4869 X_TITLE in VARCHAR2,
4870 X_SHORT_TITLE in VARCHAR2,
4871 X_TITLE_OVERRIDE_IND in VARCHAR2,
4872 X_ABBREVIATION in VARCHAR2,
4873 X_UNIT_LEVEL in VARCHAR2,
4874 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
4875 X_ENROLLED_CREDIT_POINTS in NUMBER,
4876 X_POINTS_OVERRIDE_IND in VARCHAR2,
4877 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
4878 X_COORD_PERSON_ID in NUMBER,
4879 X_OWNER_ORG_UNIT_CD in VARCHAR2,
4880 X_OWNER_OU_START_DT in DATE,
4881 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
4882 X_RESEARCH_UNIT_IND in VARCHAR2,
4883 X_INDUSTRIAL_IND in VARCHAR2,
4884 X_PRACTICAL_IND in VARCHAR2,
4885 X_REPEATABLE_IND in VARCHAR2,
4886 X_ASSESSABLE_IND in VARCHAR2,
4887 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
4888 X_POINTS_INCREMENT in NUMBER,
4889 X_POINTS_MIN in NUMBER,
4890 X_POINTS_MAX in NUMBER,
4891 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
4892 X_SUBTITLE in VARCHAR2,
4893 X_SUBTITLE_MODIFIABLE_FLAG in VARCHAR2,
4894 X_APPROVAL_DATE in DATE,
4895 X_LECTURE_CREDIT_POINTS in NUMBER,
4896 X_LAB_CREDIT_POINTS in NUMBER,
4897 X_OTHER_CREDIT_POINTS in NUMBER,
4898 X_CLOCK_HOURS in NUMBER,
4899 X_WORK_LOAD_CP_LECTURE in NUMBER,
4900 X_WORK_LOAD_CP_LAB in NUMBER,
4901 X_CONTINUING_EDUCATION_UNITS in NUMBER,
4902 X_ENROLLMENT_EXPECTED in NUMBER,
4903 X_ENROLLMENT_MINIMUM in NUMBER,
4904 X_ENROLLMENT_MAXIMUM in NUMBER,
4905 X_ADVANCE_MAXIMUM in NUMBER,
4906 X_STATE_FINANCIAL_AID in VARCHAR2,
4907 X_FEDERAL_FINANCIAL_AID in VARCHAR2,
4908 X_INSTITUTIONAL_FINANCIAL_AID in VARCHAR2,
4909 X_SAME_TEACHING_PERIOD in VARCHAR2,
4910 X_MAX_REPEATS_FOR_CREDIT in NUMBER,
4911 X_MAX_REPEATS_FOR_FUNDING in NUMBER,
4912 X_MAX_REPEAT_CREDIT_POINTS in NUMBER,
4913 X_SAME_TEACH_PERIOD_REPEATS in NUMBER,
4914 X_SAME_TEACH_PERIOD_REPEATS_CP in NUMBER,
4915 X_ATTRIBUTE_CATEGORY in VARCHAR2,
4916 X_ATTRIBUTE1 in VARCHAR2,
4917 X_ATTRIBUTE2 in VARCHAR2,
4918 X_ATTRIBUTE3 in VARCHAR2,
4919 X_ATTRIBUTE4 in VARCHAR2,
4920 X_ATTRIBUTE5 in VARCHAR2,
4921 X_ATTRIBUTE6 in VARCHAR2,
4922 X_ATTRIBUTE7 in VARCHAR2,
4923 X_ATTRIBUTE8 in VARCHAR2,
4924 X_ATTRIBUTE9 in VARCHAR2,
4925 X_ATTRIBUTE10 in VARCHAR2,
4926 X_ATTRIBUTE11 in VARCHAR2,
4927 X_ATTRIBUTE12 in VARCHAR2,
4928 X_ATTRIBUTE13 in VARCHAR2,
4929 X_ATTRIBUTE14 in VARCHAR2,
4930 X_ATTRIBUTE15 in VARCHAR2,
4931 X_ATTRIBUTE16 in VARCHAR2,
4932 X_ATTRIBUTE17 in VARCHAR2,
4933 X_ATTRIBUTE18 in VARCHAR2,
4934 X_ATTRIBUTE19 in VARCHAR2,
4935 X_ATTRIBUTE20 in VARCHAR2,
4936 X_SUBTITLE_ID IN NUMBER ,
4937 x_work_load_other IN NUMBER ,
4938 x_contact_hrs_lecture IN NUMBER ,
4939 x_contact_hrs_lab IN NUMBER ,
4940 x_contact_hrs_other IN NUMBER ,
4941 x_non_schd_required_hrs IN NUMBER ,
4942 x_exclude_from_max_cp_limit IN VARCHAR2 ,
4943 x_record_exclusion_flag IN VARCHAR2 ,
4944 x_ss_display_ind IN VARCHAR2 ,
4945 x_cal_type_enrol_load_cal IN VARCHAR2 ,
4946 x_sequence_num_enrol_load_cal IN NUMBER ,
4947 x_cal_type_offer_load_cal IN VARCHAR2 ,
4948 x_sequence_num_offer_load_cal IN NUMBER ,
4949 x_curriculum_id IN VARCHAR2 ,
4950 x_override_enrollment_max IN NUMBER ,
4951 x_rpt_fmly_id IN NUMBER ,
4952 x_unit_type_id IN NUMBER ,
4953 x_special_permission_ind IN VARCHAR2 ,
4954 X_MODE in VARCHAR2 ,
4955 X_ORG_ID IN NUMBER,
4956 X_SS_ENROL_IND in VARCHAR2 ,
4957 X_IVR_ENROL_IND in VARCHAR2 ,
4958 x_claimable_hours IN NUMBER ,
4959 x_rev_account_cd IN VARCHAR2 ,
4960 x_anon_unit_grading_ind IN VARCHAR2 ,
4961 x_anon_assess_grading_ind IN VARCHAR2 ,
4962 x_auditable_ind IN VARCHAR2,
4963 x_audit_permission_ind IN VARCHAR2,
4964 x_max_auditors_allowed IN NUMBER,
4965 x_billing_credit_points IN NUMBER,
4966 x_ovrd_wkld_val_flag IN VARCHAR2 ,
4967 x_workload_val_code IN VARCHAR2 ,
4968 x_billing_hrs IN NUMBER
4969 ) AS
4970 cursor c1 is select rowid from IGS_PS_UNIT_VER_ALL
4971 where UNIT_CD = X_UNIT_CD
4972 and VERSION_NUMBER = X_VERSION_NUMBER;
4973 BEGIN
4974 open c1;
4975 fetch c1 into X_ROWID;
4976 if (c1%notfound) then
4977 close c1;
4978 INSERT_ROW(
4979 X_ROWID,
4980 X_UNIT_CD,
4981 X_VERSION_NUMBER,
4982 X_START_DT,
4983 X_REVIEW_DT,
4984 X_EXPIRY_DT,
4985 X_END_DT,
4986 X_UNIT_STATUS,
4987 X_TITLE,
4988 X_SHORT_TITLE,
4989 X_TITLE_OVERRIDE_IND,
4990 X_ABBREVIATION,
4991 X_UNIT_LEVEL,
4992 X_CREDIT_POINT_DESCRIPTOR,
4993 X_ENROLLED_CREDIT_POINTS,
4994 X_POINTS_OVERRIDE_IND,
4995 X_SUPP_EXAM_PERMITTED_IND,
4996 X_COORD_PERSON_ID,
4997 X_OWNER_ORG_UNIT_CD,
4998 X_OWNER_OU_START_DT,
4999 X_AWARD_COURSE_ONLY_IND,
5000 X_RESEARCH_UNIT_IND,
5001 X_INDUSTRIAL_IND,
5002 X_PRACTICAL_IND,
5003 X_REPEATABLE_IND,
5004 X_ASSESSABLE_IND,
5005 X_ACHIEVABLE_CREDIT_POINTS,
5006 X_POINTS_INCREMENT,
5007 X_POINTS_MIN,
5008 X_POINTS_MAX,
5009 X_UNIT_INT_COURSE_LEVEL_CD,
5010 X_SUBTITLE,
5011 X_SUBTITLE_MODIFIABLE_FLAG,
5012 X_APPROVAL_DATE,
5013 X_LECTURE_CREDIT_POINTS,
5014 X_LAB_CREDIT_POINTS,
5015 X_OTHER_CREDIT_POINTS,
5016 X_CLOCK_HOURS,
5017 X_WORK_LOAD_CP_LECTURE,
5018 X_WORK_LOAD_CP_LAB,
5019 X_CONTINUING_EDUCATION_UNITS,
5020 X_ENROLLMENT_EXPECTED,
5021 X_ENROLLMENT_MINIMUM,
5022 X_ENROLLMENT_MAXIMUM,
5023 X_ADVANCE_MAXIMUM,
5024 X_STATE_FINANCIAL_AID,
5025 X_FEDERAL_FINANCIAL_AID,
5026 X_INSTITUTIONAL_FINANCIAL_AID,
5027 X_SAME_TEACHING_PERIOD,
5028 X_MAX_REPEATS_FOR_CREDIT,
5029 X_MAX_REPEATS_FOR_FUNDING,
5030 X_MAX_REPEAT_CREDIT_POINTS,
5031 X_SAME_TEACH_PERIOD_REPEATS,
5032 X_SAME_TEACH_PERIOD_REPEATS_CP,
5033 X_ATTRIBUTE_CATEGORY,
5034 X_ATTRIBUTE1,
5035 X_ATTRIBUTE2,
5036 X_ATTRIBUTE3,
5037 X_ATTRIBUTE4,
5038 X_ATTRIBUTE5,
5039 X_ATTRIBUTE6,
5040 X_ATTRIBUTE7,
5041 X_ATTRIBUTE8,
5042 X_ATTRIBUTE9,
5043 X_ATTRIBUTE10,
5044 X_ATTRIBUTE11,
5045 X_ATTRIBUTE12,
5046 X_ATTRIBUTE13,
5047 X_ATTRIBUTE14,
5048 X_ATTRIBUTE15,
5049 X_ATTRIBUTE16,
5050 X_ATTRIBUTE17,
5051 X_ATTRIBUTE18,
5052 X_ATTRIBUTE19,
5053 X_ATTRIBUTE20,
5054 X_SUBTITLE_ID,
5055 x_work_load_other,
5056 x_contact_hrs_lecture,
5057 x_contact_hrs_lab,
5058 x_contact_hrs_other,
5059 x_non_schd_required_hrs,
5060 x_exclude_from_max_cp_limit,
5061 x_record_exclusion_flag,
5062 x_ss_display_ind,
5063 x_cal_type_enrol_load_cal,
5064 x_sequence_num_enrol_load_cal,
5065 x_cal_type_offer_load_cal,
5066 x_sequence_num_offer_load_cal,
5067 x_curriculum_id,
5068 x_override_enrollment_max,
5069 x_rpt_fmly_id,
5070 x_unit_type_id,
5071 x_special_permission_ind,
5072 X_MODE,
5073 X_ORG_ID,
5074 X_SS_ENROL_IND,
5075 X_IVR_ENROL_IND,
5076 x_rev_account_cd,
5077 x_claimable_hours,
5078 x_anon_unit_grading_ind,
5079 x_anon_assess_grading_ind,
5080 x_auditable_ind,
5081 x_audit_permission_ind,
5082 x_max_auditors_allowed,
5083 x_billing_credit_points,
5084 x_ovrd_wkld_val_flag,
5085 x_workload_val_code,
5086 x_billing_hrs
5087 );
5088 return;
5089 end if;
5090 close c1;
5091
5092 UPDATE_ROW (
5093 X_ROWID,
5094 X_UNIT_CD,
5095 X_VERSION_NUMBER,
5096 X_START_DT,
5097 X_REVIEW_DT,
5098 X_EXPIRY_DT,
5099 X_END_DT,
5100 X_UNIT_STATUS,
5101 X_TITLE,
5102 X_SHORT_TITLE,
5103 X_TITLE_OVERRIDE_IND,
5104 X_ABBREVIATION,
5105 X_UNIT_LEVEL,
5106 X_CREDIT_POINT_DESCRIPTOR,
5107 X_ENROLLED_CREDIT_POINTS,
5108 X_POINTS_OVERRIDE_IND,
5109 X_SUPP_EXAM_PERMITTED_IND,
5110 X_COORD_PERSON_ID,
5111 X_OWNER_ORG_UNIT_CD,
5112 X_OWNER_OU_START_DT,
5113 X_AWARD_COURSE_ONLY_IND,
5114 X_RESEARCH_UNIT_IND,
5115 X_INDUSTRIAL_IND,
5116 X_PRACTICAL_IND,
5117 X_REPEATABLE_IND,
5118 X_ASSESSABLE_IND,
5119 X_ACHIEVABLE_CREDIT_POINTS,
5120 X_POINTS_INCREMENT,
5121 X_POINTS_MIN,
5122 X_POINTS_MAX,
5123 X_UNIT_INT_COURSE_LEVEL_CD,
5124 X_SUBTITLE,
5125 X_SUBTITLE_MODIFIABLE_FLAG,
5126 X_APPROVAL_DATE,
5127 X_LECTURE_CREDIT_POINTS,
5128 X_LAB_CREDIT_POINTS,
5129 X_OTHER_CREDIT_POINTS,
5130 X_CLOCK_HOURS,
5131 X_WORK_LOAD_CP_LECTURE,
5132 X_WORK_LOAD_CP_LAB,
5133 X_CONTINUING_EDUCATION_UNITS,
5134 X_ENROLLMENT_EXPECTED,
5135 X_ENROLLMENT_MINIMUM,
5136 X_ENROLLMENT_MAXIMUM,
5137 X_ADVANCE_MAXIMUM,
5138 X_STATE_FINANCIAL_AID,
5139 X_FEDERAL_FINANCIAL_AID,
5140 X_INSTITUTIONAL_FINANCIAL_AID,
5141 X_SAME_TEACHING_PERIOD,
5142 X_MAX_REPEATS_FOR_CREDIT,
5143 X_MAX_REPEATS_FOR_FUNDING,
5144 X_MAX_REPEAT_CREDIT_POINTS,
5145 X_SAME_TEACH_PERIOD_REPEATS,
5146 X_SAME_TEACH_PERIOD_REPEATS_CP,
5147 X_ATTRIBUTE_CATEGORY,
5148 X_ATTRIBUTE1,
5149 X_ATTRIBUTE2,
5150 X_ATTRIBUTE3,
5151 X_ATTRIBUTE4,
5152 X_ATTRIBUTE5,
5153 X_ATTRIBUTE6,
5154 X_ATTRIBUTE7,
5155 X_ATTRIBUTE8,
5156 X_ATTRIBUTE9,
5157 X_ATTRIBUTE10,
5158 X_ATTRIBUTE11,
5159 X_ATTRIBUTE12,
5160 X_ATTRIBUTE13,
5161 X_ATTRIBUTE14,
5162 X_ATTRIBUTE15,
5163 X_ATTRIBUTE16,
5164 X_ATTRIBUTE17,
5165 X_ATTRIBUTE18,
5166 X_ATTRIBUTE19,
5167 X_ATTRIBUTE20,
5168 X_SUBTITLE_ID,
5169 x_work_load_other,
5170 x_contact_hrs_lecture,
5171 x_contact_hrs_lab,
5172 x_contact_hrs_other,
5173 x_non_schd_required_hrs,
5174 x_exclude_from_max_cp_limit,
5175 x_record_exclusion_flag,
5176 x_ss_display_ind,
5177 x_cal_type_enrol_load_cal,
5178 x_sequence_num_enrol_load_cal,
5179 x_cal_type_offer_load_cal,
5180 x_sequence_num_offer_load_cal,
5181 x_curriculum_id,
5182 x_override_enrollment_max,
5183 x_rpt_fmly_id,
5184 x_unit_type_id,
5185 x_special_permission_ind,
5186 X_MODE,
5187 X_SS_ENROL_IND,
5188 X_IVR_ENROL_IND,
5189 x_rev_account_cd,
5190 x_claimable_hours,
5191 x_anon_unit_grading_ind,
5192 x_anon_assess_grading_ind,
5193 x_auditable_ind,
5194 x_audit_permission_ind,
5195 x_max_auditors_allowed,
5196 x_billing_credit_points,
5197 x_ovrd_wkld_val_flag,
5198 x_workload_val_code,
5199 x_billing_hrs
5200 );
5201 END add_row;
5202
5203 PROCEDURE delete_row (
5204 X_ROWID in VARCHAR2
5205 ) AS
5206 BEGIN
5207 Before_DML (
5208 p_action => 'DELETE',
5209 x_rowid => X_ROWID
5210 );
5211
5212 delete from IGS_PS_UNIT_VER_ALL
5213 where ROWID = X_ROWID
5214 ;
5215 if (sql%notfound) then
5216 raise no_data_found;
5217 end if;
5218 After_DML (
5219 p_action => 'DELETE',
5220 x_rowid => X_ROWID
5221 );
5222
5223 END delete_row;
5224
5225 PROCEDURE update_row_subtitle_id(X_RowId IN VARCHAR2,X_Subtitle_Id IN NUMBER) AS
5226 BEGIN
5227
5228 UPDATE IGS_PS_UNIT_VER_ALL set
5229 subtitle_id = X_Subtitle_Id
5230 WHERE ROWID = X_ROWID;
5231
5232 END update_row_subtitle_id;
5233
5234 END igs_ps_unit_ver_pkg;