[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_VER_HIST_PKG
Source
1 package body IGS_PS_UNIT_VER_HIST_PKG as
2 /* $Header: IGSPI93B.pls 120.1 2006/08/10 14:05:07 pkpatel noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_UNIT_VER_HIST_ALL%RowType;
5 new_references IGS_PS_UNIT_VER_HIST_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_unit_cd IN VARCHAR2 ,
11 x_version_number IN NUMBER ,
12 x_hist_start_dt IN DATE ,
13 x_hist_end_dt IN DATE ,
14 x_hist_who IN NUMBER ,
15 x_start_dt IN DATE ,
16 x_review_dt IN DATE ,
17 x_expiry_dt IN DATE ,
18 x_end_dt IN DATE ,
19 x_unit_status IN VARCHAR2 ,
20 x_title IN VARCHAR2 ,
21 x_short_title IN VARCHAR2 ,
22 x_title_override_ind IN VARCHAR2 ,
23 x_abbreviation IN VARCHAR2 ,
24 x_unit_level IN VARCHAR2 ,
25 x_ul_description IN VARCHAR2 ,
26 x_credit_point_descriptor IN VARCHAR2 ,
27 x_enrolled_credit_points IN NUMBER ,
28 x_points_override_ind IN VARCHAR2 ,
29 x_supp_exam_permitted_ind IN VARCHAR2 ,
30 x_coord_person_id IN NUMBER ,
31 x_owner_org_unit_cd IN VARCHAR2 ,
32 x_owner_ou_start_dt IN DATE ,
33 x_ou_description IN VARCHAR2 ,
34 x_award_course_only_ind IN VARCHAR2 ,
35 x_research_unit_ind IN VARCHAR2 ,
36 x_industrial_ind IN VARCHAR2 ,
37 x_practical_ind IN VARCHAR2 ,
38 x_repeatable_ind IN VARCHAR2 ,
39 x_assessable_ind IN VARCHAR2 ,
40 x_achievable_credit_points IN NUMBER ,
41 x_points_increment IN NUMBER ,
42 x_points_min IN NUMBER ,
43 x_points_max IN NUMBER ,
44 x_unit_int_course_level_cd IN VARCHAR2 ,
45 x_uicl_description IN VARCHAR2 ,
46 x_subtitle_id IN NUMBER ,
47 x_work_load_other IN NUMBER ,
48 x_contact_hrs_lecture IN NUMBER ,
49 x_contact_hrs_lab IN NUMBER ,
50 x_contact_hrs_other IN NUMBER ,
51 x_non_schd_required_hrs IN NUMBER ,
52 x_exclude_from_max_cp_limit IN VARCHAR2 ,
53 x_record_exclusion_flag IN VARCHAR2 ,
54 x_ss_display_ind IN VARCHAR2 ,
55 x_cal_type_enrol_load_cal IN VARCHAR2 ,
56 x_sequence_num_enrol_load_cal IN NUMBER ,
57 x_cal_type_offer_load_cal IN VARCHAR2 ,
58 x_sequence_num_offer_load_cal IN NUMBER ,
59 x_curriculum_id IN VARCHAR2 ,
60 x_override_enrollment_max IN NUMBER ,
61 x_rpt_fmly_id IN NUMBER ,
62 x_repeat_code IN VARCHAR2 ,
63 x_unit_type_id IN NUMBER ,
64 x_level_code IN VARCHAR2 ,
65 x_advance_maximum IN NUMBER ,
66 x_approval_date IN DATE ,
67 x_continuing_education_units IN NUMBER ,
68 x_enrollment_expected IN NUMBER ,
69 x_enrollment_maximum IN NUMBER ,
70 x_enrollment_minimum IN NUMBER ,
71 x_federal_financial_aid IN VARCHAR2 ,
72 x_institutional_financial_aid IN VARCHAR2 ,
73 x_lab_credit_points IN NUMBER ,
74 x_lecture_credit_points IN NUMBER ,
75 x_max_repeats_for_credit IN NUMBER ,
76 x_max_repeats_for_funding IN NUMBER ,
77 x_max_repeat_credit_points IN NUMBER ,
78 x_clock_hours IN NUMBER ,
79 x_other_credit_points IN NUMBER ,
80 x_same_teaching_period IN VARCHAR2 ,
81 x_same_teach_period_repeats IN NUMBER ,
82 x_same_teach_period_repeats_cp IN NUMBER ,
83 x_state_financial_aid IN VARCHAR2 ,
84 x_work_load_cp_lab IN NUMBER ,
85 x_work_load_cp_lecture IN NUMBER ,
86 x_subtitle_modifiable_flag IN VARCHAR2 ,
87 x_subtitle IN VARCHAR2 ,
88 x_special_permission_ind IN VARCHAR2 ,
89 x_creation_date IN DATE ,
90 x_created_by IN NUMBER ,
91 x_last_update_date IN DATE ,
92 x_last_updated_by IN NUMBER ,
93 x_last_update_login IN NUMBER ,
94 x_org_id IN NUMBER ,
95 x_ss_enrol_ind IN VARCHAR2 ,
96 x_ivr_enrol_ind IN VARCHAR2 ,
97 x_claimable_hours IN NUMBER ,
98 x_auditable_ind IN VARCHAR2 ,
99 x_audit_permission_ind IN VARCHAR2 ,
100 x_max_auditors_allowed IN NUMBER ,
101 x_billing_credit_points IN NUMBER ,
102 x_ovrd_wkld_val_flag IN VARCHAR2 ,
103 x_workload_val_code IN VARCHAR2 ,
104 x_billing_hrs IN NUMBER
105 ) AS
106
107 CURSOR cur_old_ref_values IS
108 SELECT *
109 FROM IGS_PS_UNIT_VER_HIST_ALL
110 WHERE rowid = x_rowid;
111
112 BEGIN
113
114 l_rowid := x_rowid;
115
116 -- Code for setting the Old and New Reference Values.
117 -- Populate Old Values.
118 Open cur_old_ref_values;
119 Fetch cur_old_ref_values INTO old_references;
120 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
121 Close cur_old_ref_values;
122 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 Return;
126 END IF;
127 Close cur_old_ref_values;
128
129 -- Populate New Values.
130 new_references.unit_cd := x_unit_cd;
131 new_references.version_number := x_version_number;
132 new_references.hist_start_dt := x_hist_start_dt;
133 new_references.hist_end_dt := x_hist_end_dt;
134 new_references.hist_who := x_hist_who;
135 new_references.start_dt := x_start_dt;
136 new_references.review_dt := x_review_dt;
137 new_references.expiry_dt := x_expiry_dt;
138 new_references.end_dt := x_end_dt;
139 new_references.unit_status := x_unit_status;
140 new_references.title := x_title;
141 new_references.short_title := x_short_title;
142 new_references.title_override_ind := x_title_override_ind;
143 new_references.abbreviation := x_abbreviation;
144 new_references.unit_level := x_unit_level;
145 new_references.ul_description := x_ul_description;
146 new_references.credit_point_descriptor:= x_credit_point_descriptor;
147 new_references.enrolled_credit_points := x_enrolled_credit_points;
148 new_references.points_override_ind := x_points_override_ind;
149 new_references.supp_exam_permitted_ind := x_supp_exam_permitted_ind;
150 new_references.coord_person_id := x_coord_person_id;
151 new_references.owner_org_unit_cd := x_owner_org_unit_cd;
152 new_references.owner_ou_start_dt := x_owner_ou_start_dt;
153 new_references.ou_description := x_ou_description;
154 new_references.award_course_only_ind := x_award_course_only_ind;
155 new_references.research_unit_ind := x_research_unit_ind;
156 new_references.industrial_ind := x_industrial_ind;
157 new_references.practical_ind := x_practical_ind;
158 new_references.repeatable_ind := x_repeatable_ind;
159 new_references.assessable_ind := x_assessable_ind;
160 new_references.achievable_credit_points := x_achievable_credit_points;
161 new_references.points_increment := x_points_increment;
162 new_references.points_min := x_points_min;
163 new_references.points_max := x_points_max;
164 new_references.unit_int_course_level_cd := x_unit_int_course_level_cd;
165 new_references.uicl_description := x_uicl_description;
166 new_references.subtitle_id := x_subtitle_id;
167 new_references.work_load_other := x_work_load_other;
168 new_references.contact_hrs_lecture := x_contact_hrs_lecture;
169 new_references.contact_hrs_lab := x_contact_hrs_lab;
170 new_references.contact_hrs_other := x_contact_hrs_other;
171 new_references.non_schd_required_hrs := x_non_schd_required_hrs;
172 new_references.exclude_from_max_cp_limit := x_exclude_from_max_cp_limit;
173 new_references.record_exclusion_flag := x_record_exclusion_flag ;
174 new_references.ss_display_ind := x_ss_display_ind;
175 new_references.cal_type_enrol_load_cal := x_cal_type_enrol_load_cal;
176 new_references.sequence_num_enrol_load_cal := x_sequence_num_enrol_load_cal;
177 new_references.cal_type_offer_load_cal := x_cal_type_offer_load_cal;
178 new_references.sequence_num_offer_load_cal := x_sequence_num_offer_load_cal;
179 new_references.curriculum_id := x_curriculum_id;
180 new_references.override_enrollment_max := x_override_enrollment_max;
181 new_references.rpt_fmly_id := x_rpt_fmly_id;
182 new_references.repeat_code := x_repeat_code;
183 new_references.unit_type_id := x_unit_type_id;
184 new_references.level_code := x_level_code;
185 new_references.advance_maximum := x_advance_maximum;
186 new_references.approval_date := x_approval_date;
187 new_references.continuing_education_units := x_continuing_education_units;
188 new_references.enrollment_expected := x_enrollment_expected;
189 new_references.enrollment_maximum := x_enrollment_maximum;
190 new_references.enrollment_minimum := x_enrollment_minimum ;
191 new_references.federal_financial_aid := x_federal_financial_aid;
192 new_references.institutional_financial_aid := x_institutional_financial_aid;
193 new_references.lab_credit_points := x_lab_credit_points;
194 new_references.lecture_credit_points := x_lecture_credit_points;
195 new_references.max_repeats_for_credit := x_max_repeats_for_credit;
196 new_references.max_repeats_for_funding := x_max_repeats_for_funding;
197 new_references.max_repeat_credit_points := x_max_repeat_credit_points;
198 new_references.clock_hours := x_clock_hours ;
199 new_references.other_credit_points := x_other_credit_points;
200 new_references.same_teaching_period := x_same_teaching_period;
201 new_references.same_teach_period_repeats := x_same_teach_period_repeats;
202 new_references.same_teach_period_repeats_cp := x_same_teach_period_repeats_cp;
203 new_references.state_financial_aid := x_state_financial_aid;
204 new_references.work_load_cp_lab := x_work_load_cp_lab;
205 new_references.work_load_cp_lecture := x_work_load_cp_lecture;
206 new_references.subtitle_modifiable_flag := x_subtitle_modifiable_flag;
207 new_references.subtitle := x_subtitle;
208 new_references.special_permission_ind := x_special_permission_ind;
209 new_references.claimable_hours := x_claimable_hours;
210 new_references.auditable_ind := x_auditable_ind;
211 new_references.audit_permission_ind := x_audit_permission_ind;
212 new_references.max_auditors_allowed := x_max_auditors_allowed;
213 new_references.billing_credit_points := x_billing_credit_points;
214 new_references.ovrd_wkld_val_flag := x_ovrd_wkld_val_flag;
215 new_references.workload_val_code := x_workload_val_code;
216 new_references.billing_hrs := x_billing_hrs;
217
218 IF (p_action = 'UPDATE') THEN
219 new_references.creation_date := old_references.creation_date;
220 new_references.created_by := old_references.created_by;
221 ELSE
222 new_references.creation_date := x_creation_date;
223 new_references.created_by := x_created_by;
224 END IF;
225 new_references.last_update_date := x_last_update_date;
226 new_references.last_updated_by := x_last_updated_by;
227 new_references.last_update_login := x_last_update_login;
228 new_references.org_id := x_org_id;
229 new_references.ss_enrol_ind := x_ss_enrol_ind;
230 new_references.ivr_enrol_ind := x_ivr_enrol_ind;
231
232
233 END Set_Column_Values;
234
235 PROCEDURE Check_Constraints(
236 Column_Name IN VARCHAR2 ,
237 Column_Value IN VARCHAR2 )
238 AS
239 BEGIN
240
241 IF Column_Name IS NULL Then
242 NULL;
243 ELSIF Upper(Column_Name)='ABBREVIATION' Then
244 New_References.abbreviation := Column_Value;
245 ELSIF Upper(Column_Name)='ASSESSABLE_IND' Then
246 New_References.assessable_ind := Column_Value;
247 ELSIF Upper(Column_Name)='AUDITABLE_IND' Then
248 New_References.auditable_ind := Column_Value;
249 ELSIF Upper(Column_Name)='AUDIT_PERMISSION_IND' Then
250 New_References.audit_permission_ind := Column_Value;
251 ELSIF Upper(Column_Name)='AWARD_COURSE_ONLY_IND' Then
252 New_References.award_course_only_ind := Column_Value;
253 ELSIF Upper(Column_Name)='CREDIT_POINT_DESCRIPTOR' Then
254 New_References.credit_point_descriptor := Column_Value;
255 ELSIF Upper(Column_Name)='INDUSTRIAL_IND' Then
256 New_References.industrial_ind := Column_Value;
257 ELSIF Upper(Column_Name)='POINTS_OVERRIDE_IND' Then
258 New_References.points_override_ind := Column_Value;
259 ELSIF Upper(Column_Name)='PRACTICAL_IND' Then
260 New_References.practical_ind := Column_Value;
261 ELSIF Upper(Column_Name)='REPEATABLE_IND' Then
262 New_References.repeatable_ind := Column_Value;
263 ELSIF Upper(Column_Name)='RESEARCH_UNIT_IND' Then
264 New_References.research_unit_ind := Column_Value;
265 ELSIF Upper(Column_Name)='SUPP_EXAM_PERMITTED_IND' Then
266 New_References.supp_exam_permitted_ind := Column_Value;
267 ELSIF Upper(Column_Name)='TITLE_OVERRIDE_IND' Then
268 New_References.title_override_ind := Column_Value;
269 ELSIF Upper(Column_Name)='UNIT_CD' Then
270 New_References.unit_cd := Column_Value;
271 ELSIF Upper(Column_Name)='UNIT_INT_COURSE_LEVEL_CD' Then
272 New_References.unit_int_course_level_cd := Column_Value;
273 ELSIF Upper(Column_Name)='UNIT_LEVEL' Then
274 New_References.unit_level:= Column_Value;
275 ELSIF Upper(Column_Name)='UNIT_STATUS' Then
276 New_References.unit_status:= Column_Value;
277 ELSIF UPPER(Column_Name)='SS_ENROL_IND' THEN
278 New_References.ss_enrol_ind := column_value;
279 ELSIF UPPER(Column_Name)='IVR_ENROL_IND' THEN
280 New_References.ivr_enrol_ind := column_value;
281 ELSIF UPPER(Column_Name)='BILLING_HRS' THEN
282 New_References.billing_hrs := column_value;
283 END IF;
284
285 IF Upper(Column_Name)='ABBREVIATION' OR Column_Name IS NULL Then
286 IF New_References.abbreviation <> UPPER(New_References.abbreviation) Then
287 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 END IF;
291 END IF;
292
293 IF Upper(Column_Name)='ASSESSABLE_IND' OR Column_Name IS NULL Then
294 IF New_References.Assessable_Ind NOT IN ( 'Y' , 'N' ) Then
295 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
296 IGS_GE_MSG_STACK.ADD;
297 App_Exception.Raise_Exception;
298 END IF;
299 END IF;
300
301 IF Upper(Column_Name)='AWARD_COURSE_ONLY_IND' OR Column_Name IS NULL Then
302 IF New_References.Award_Course_Only_Ind NOT IN ( 'Y' , 'N' ) Then
303 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 END IF;
307 END IF;
308
309 IF Upper(Column_Name)='CREDIT_POINT_DESCRIPTOR' OR Column_Name IS NULL Then
310 IF New_References.Credit_Point_Descriptor <> UPPER(New_References.Credit_Point_Descriptor) Then
311 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END IF;
315 END IF;
316
317 IF Upper(Column_Name)='INDUSTRIAL_IND' OR Column_Name IS NULL Then
318 IF New_References.Industrial_Ind NOT IN ( 'Y' , 'N' ) Then
319 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323
324 END IF;
325
326 IF Upper(Column_Name)='POINTS_OVERRIDE_IND' OR Column_Name IS NULL Then
327
328 IF New_References.Points_Override_Ind NOT IN ( 'Y' , 'N' ) Then
329 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 END IF;
333
334
335 END IF;
336
337 IF Upper(Column_Name)='PRACTICAL_IND' OR Column_Name IS NULL Then
338 IF New_References.Practical_Ind <> UPPER(New_References.Practical_Ind) Then
339 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
340 IGS_GE_MSG_STACK.ADD;
341 App_Exception.Raise_Exception;
342 END IF;
343 END IF;
344
345 IF Upper(Column_Name)='REPEATABLE_IND' OR Column_Name IS NULL Then
346
347 IF New_References.Repeatable_Ind NOT IN ( 'Y' , 'N','X' ) Then
348 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
349 IGS_GE_MSG_STACK.ADD;
350 App_Exception.Raise_Exception;
351 END IF;
352
353 END IF;
354
355
356 IF Upper(Column_Name)='RESEARCH_UNIT_IND' OR Column_Name IS NULL Then
357
358 IF New_References.Research_Unit_Ind NOT IN ( 'Y' , 'N' ) Then
359 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
360 IGS_GE_MSG_STACK.ADD;
361 App_Exception.Raise_Exception;
362 END IF;
363
364
365 END IF;
366
367
368 IF Upper(Column_Name)='SUPP_EXAM_PERMITTED_IND' OR Column_Name IS NULL Then
369
370 IF New_References.Supp_Exam_Permitted_Ind NOT IN ( 'Y' , 'N' ) Then
371 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
372 IGS_GE_MSG_STACK.ADD;
373 App_Exception.Raise_Exception;
374 END IF;
375
376
377 END IF;
378
379
380 IF Upper(Column_Name)='TITLE_OVERRIDE_IND' OR Column_Name IS NULL Then
381
382 IF New_References.Title_Override_Ind NOT IN ( 'Y' , 'N' ) Then
383 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386 END IF;
387
388
389 END IF;
390
391 IF Upper(Column_Name)='AUDITABLE_IND' OR Column_Name IS NULL Then
392 IF New_References.auditable_Ind NOT IN ( 'Y' , 'N' ) Then
393 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 END IF;
397 END IF;
398
399 IF Upper(Column_Name)='AUDIT_PERMISSION_IND' OR Column_Name IS NULL Then
400 IF New_References.audit_permission_Ind NOT IN ( 'Y' , 'N' ) Then
401 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
402 IGS_GE_MSG_STACK.ADD;
403 App_Exception.Raise_Exception;
404 END IF;
405 END IF;
406
407 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
408 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
409 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
410 IGS_GE_MSG_STACK.ADD;
411 App_Exception.Raise_Exception;
412 END IF;
413 END IF;
414
415 IF Upper(Column_Name)='UNIT_INT_COURSE_LEVEL_CD' OR Column_Name IS NULL Then
416 IF New_References.Unit_Int_Course_Level_Cd <> UPPER(New_References.Unit_Int_Course_Level_Cd) Then
417 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
418 IGS_GE_MSG_STACK.ADD;
419 App_Exception.Raise_Exception;
420 END IF;
421 END IF;
422
423 IF Upper(Column_Name)='UNIT_LEVEL' OR Column_Name IS NULL Then
424 IF New_References.Unit_Level <> UPPER(New_References.Unit_Level) Then
425 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429 END IF;
430
431 IF Upper(Column_Name)='UNIT_STATUS' OR Column_Name IS NULL Then
432 IF New_References.Unit_Status <> UPPER(New_References.Unit_Status) Then
433 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
434 IGS_GE_MSG_STACK.ADD;
435 App_Exception.Raise_Exception;
436 END IF;
437 END IF;
438
439 -- check that atleast one enrollemnt method is selected
440 IF (Upper(Column_name)='SS_ENROL_IND' OR Upper(Column_name)='IVR_ENROL_IND') OR Column_name IS NULL THEN
441 IF NOT (New_References.ss_enrol_ind = 'Y' OR New_references.ivr_enrol_ind = 'Y') THEN
442 Fnd_Message.Set_Name('IGS', 'IGS_PS_ONE_UNIT_ENR_MTHD');
443 IGS_GE_MSG_STACK.ADD;
444 App_Exception.Raise_Exception;
445 END IF;
446 END IF;
447
448 IF Upper(Column_Name)='BILLING_HRS' OR Column_Name IS NULL Then
449 IF New_References.billing_hrs < 0 OR New_References.billing_hrs > 999.999 Then
450 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
451 IGS_GE_MSG_STACK.ADD;
452 App_Exception.Raise_Exception;
453 END IF;
454 END IF;
455
456
457 END Check_Constraints;
458
459
460 FUNCTION Get_PK_For_Validation (
461 x_unit_cd IN VARCHAR2,
462 x_version_number IN NUMBER,
463 x_hist_start_dt IN DATE
464 ) RETURN BOOLEAN AS
465
466 CURSOR cur_rowid IS
467 SELECT rowid
468 FROM IGS_PS_UNIT_VER_HIST_ALL
469 WHERE unit_cd = x_unit_cd
470 AND version_number = x_version_number
471 AND hist_start_dt = x_hist_start_dt
472 FOR UPDATE NOWAIT;
473
474 lv_rowid cur_rowid%RowType;
475
476 BEGIN
477
478 Open cur_rowid;
479 Fetch cur_rowid INTO lv_rowid;
480 IF (cur_rowid%FOUND) THEN
481 Close cur_rowid;
482 Return(TRUE);
483 ELSE
484 Close cur_rowid;
485 Return(FALSE);
486 END IF;
487
488 END Get_PK_For_Validation;
489
490 PROCEDURE Before_DML (
491 p_action IN VARCHAR2,
492 x_rowid IN VARCHAR2 ,
493 x_unit_cd IN VARCHAR2 ,
494 x_version_number IN NUMBER ,
495 x_hist_start_dt IN DATE ,
496 x_hist_end_dt IN DATE ,
497 x_hist_who IN NUMBER ,
498 x_start_dt IN DATE ,
499 x_review_dt IN DATE ,
500 x_expiry_dt IN DATE ,
501 x_end_dt IN DATE ,
502 x_unit_status IN VARCHAR2 ,
503 x_title IN VARCHAR2 ,
504 x_short_title IN VARCHAR2 ,
505 x_title_override_ind IN VARCHAR2 ,
506 x_abbreviation IN VARCHAR2 ,
507 x_unit_level IN VARCHAR2 ,
508 x_ul_description IN VARCHAR2 ,
509 x_credit_point_descriptor IN VARCHAR2 ,
510 x_enrolled_credit_points IN NUMBER ,
511 x_points_override_ind IN VARCHAR2 ,
512 x_supp_exam_permitted_ind IN VARCHAR2 ,
513 x_coord_person_id IN NUMBER ,
514 x_owner_org_unit_cd IN VARCHAR2 ,
515 x_owner_ou_start_dt IN DATE ,
516 x_ou_description IN VARCHAR2 ,
517 x_award_course_only_ind IN VARCHAR2 ,
518 x_research_unit_ind IN VARCHAR2 ,
519 x_industrial_ind IN VARCHAR2 ,
520 x_practical_ind IN VARCHAR2 ,
521 x_repeatable_ind IN VARCHAR2 ,
522 x_assessable_ind IN VARCHAR2 ,
523 x_achievable_credit_points IN NUMBER ,
524 x_points_increment IN NUMBER ,
525 x_points_min IN NUMBER ,
526 x_points_max IN NUMBER ,
527 x_unit_int_course_level_cd IN VARCHAR2 ,
528 x_uicl_description IN VARCHAR2 ,
529 x_subtitle_id IN NUMBER ,
530 x_work_load_other IN NUMBER ,
531 x_contact_hrs_lecture IN NUMBER ,
532 x_contact_hrs_lab IN NUMBER ,
533 x_contact_hrs_other IN NUMBER ,
534 x_non_schd_required_hrs IN NUMBER ,
535 x_exclude_from_max_cp_limit IN VARCHAR2 ,
536 x_record_exclusion_flag IN VARCHAR2 ,
537 x_ss_display_ind IN VARCHAR2 ,
538 x_cal_type_enrol_load_cal IN VARCHAR2 ,
539 x_sequence_num_enrol_load_cal IN NUMBER ,
540 x_cal_type_offer_load_cal IN VARCHAR2 ,
541 x_sequence_num_offer_load_cal IN NUMBER ,
542 x_curriculum_id IN VARCHAR2 ,
543 x_override_enrollment_max IN NUMBER ,
544 x_rpt_fmly_id IN NUMBER ,
545 x_repeat_code IN VARCHAR2 ,
546 x_unit_type_id IN NUMBER ,
547 x_level_code IN VARCHAR2 ,
548 x_advance_maximum IN NUMBER ,
549 x_approval_date IN DATE ,
550 x_continuing_education_units IN NUMBER ,
551 x_enrollment_expected IN NUMBER ,
552 x_enrollment_maximum IN NUMBER ,
553 x_enrollment_minimum IN NUMBER ,
554 x_federal_financial_aid IN VARCHAR2 ,
555 x_institutional_financial_aid IN VARCHAR2 ,
556 x_lab_credit_points IN NUMBER ,
557 x_lecture_credit_points IN NUMBER ,
558 x_max_repeats_for_credit IN NUMBER ,
559 x_max_repeats_for_funding IN NUMBER ,
560 x_max_repeat_credit_points IN NUMBER ,
561 x_clock_hours IN NUMBER ,
562 x_other_credit_points IN NUMBER ,
563 x_same_teaching_period IN VARCHAR2 ,
564 x_same_teach_period_repeats IN NUMBER ,
565 x_same_teach_period_repeats_cp IN NUMBER ,
566 x_state_financial_aid IN VARCHAR2 ,
567 x_work_load_cp_lab IN NUMBER ,
568 x_work_load_cp_lecture IN NUMBER ,
569 x_subtitle_modifiable_flag IN VARCHAR2 ,
570 x_subtitle IN VARCHAR2 ,
571 x_special_permission_ind IN VARCHAR2 ,
572 x_creation_date IN DATE ,
573 x_created_by IN NUMBER ,
574 x_last_update_date IN DATE ,
575 x_last_updated_by IN NUMBER ,
576 x_last_update_login IN NUMBER ,
577 x_org_id IN NUMBER ,
578 x_ss_enrol_ind IN VARCHAR2 ,
579 x_ivr_enrol_ind IN VARCHAR2 ,
580 x_claimable_hours IN NUMBER ,
581 x_auditable_ind IN VARCHAR2 ,
582 x_audit_permission_ind IN VARCHAR2 ,
583 x_max_auditors_allowed IN NUMBER ,
584 x_billing_credit_points IN NUMBER ,
585 x_ovrd_wkld_val_flag IN VARCHAR2 ,
586 x_workload_val_code IN VARCHAR2 ,
587 x_billing_hrs IN NUMBER
588 ) AS
589
590 l_count NUMBER;
591 l_dummy VARCHAR2(1);
592 CURSOR unique_check_cur (cp_unit_cd igs_ps_unit_ver_hist.unit_cd%TYPE,
593 cp_version_number igs_ps_unit_ver_hist.version_number%TYPE,
594 cp_hist_start_dt igs_ps_unit_ver_hist.hist_start_dt%TYPE) IS
595 SELECT 'Y'
596 FROM IGS_PS_UNIT_VER_HIST
597 WHERE unit_cd = cp_unit_cd
598 AND version_number = cp_version_number
599 AND hist_start_dt = cp_hist_start_dt;
600 BEGIN
601
602 Set_Column_Values (
603 p_action,
604 x_rowid,
605 x_unit_cd,
606 x_version_number,
607 x_hist_start_dt,
608 x_hist_end_dt,
609 x_hist_who,
610 x_start_dt,
611 x_review_dt,
612 x_expiry_dt,
613 x_end_dt,
614 x_unit_status,
615 x_title,
616 x_short_title,
617 x_title_override_ind,
618 x_abbreviation,
619 x_unit_level,
620 x_ul_description,
621 x_credit_point_descriptor,
622 x_enrolled_credit_points,
623 x_points_override_ind,
624 x_supp_exam_permitted_ind,
625 x_coord_person_id,
626 x_owner_org_unit_cd,
627 x_owner_ou_start_dt,
628 x_ou_description,
629 x_award_course_only_ind,
630 x_research_unit_ind,
631 x_industrial_ind,
632 x_practical_ind,
633 x_repeatable_ind,
634 x_assessable_ind,
635 x_achievable_credit_points,
636 x_points_increment,
637 x_points_min,
638 x_points_max,
639 x_unit_int_course_level_cd,
640 x_uicl_description,
641 x_subtitle_id,
642 x_work_load_other,
643 x_contact_hrs_lecture,
644 x_contact_hrs_lab,
645 x_contact_hrs_other,
646 x_non_schd_required_hrs,
647 x_exclude_from_max_cp_limit,
648 x_record_exclusion_flag,
649 x_ss_display_ind,
650 x_cal_type_enrol_load_cal,
651 x_sequence_num_enrol_load_cal,
652 x_cal_type_offer_load_cal,
653 x_sequence_num_offer_load_cal,
654 x_curriculum_id,
655 x_override_enrollment_max,
656 x_rpt_fmly_id,
657 x_repeat_code,
658 x_unit_type_id,
659 x_level_code,
660 x_advance_maximum ,
661 x_approval_date,
662 x_continuing_education_units,
663 x_enrollment_expected,
664 x_enrollment_maximum,
665 x_enrollment_minimum ,
666 x_federal_financial_aid,
667 x_institutional_financial_aid,
668 x_lab_credit_points ,
669 x_lecture_credit_points,
670 x_max_repeats_for_credit,
671 x_max_repeats_for_funding,
672 x_max_repeat_credit_points,
673 x_clock_hours ,
674 x_other_credit_points,
675 x_same_teaching_period,
676 x_same_teach_period_repeats ,
677 x_same_teach_period_repeats_cp,
678 x_state_financial_aid,
679 x_work_load_cp_lab,
680 x_work_load_cp_lecture,
681 x_subtitle_modifiable_flag,
682 x_subtitle,
683 x_special_permission_ind ,
684 x_creation_date,
685 x_created_by,
686 x_last_update_date,
687 x_last_updated_by,
688 x_last_update_login,
689 x_org_id ,
690 x_ss_enrol_ind,
691 x_ivr_enrol_ind,
692 x_claimable_hours,
693 x_auditable_ind,
694 x_audit_permission_ind,
695 x_max_auditors_allowed,
696 x_billing_credit_points,
697 x_ovrd_wkld_val_flag,
698 x_workload_val_code,
699 x_billing_hrs
700 );
701
702 IF (p_action = 'INSERT') THEN
703 -- Call all the procedures related to Before Insert.
704 IF Get_PK_For_Validation (New_References.unit_cd,
705 New_References.version_number,
706 New_References.hist_start_dt) THEN
707
708 -- Bug 5455027, When updating the Unit Record repetedly the Unique Key fails due to
709 -- hist_start_date becomes same as last update date of the Unit Record
710 -- Here increment the histroy start date by 1 second till unique combination is found.
711 l_count := 1;
712 WHILE l_count > 0 LOOP
713
714 New_References.hist_start_dt := New_References.hist_start_dt + 1/(60*24*60);
715
716 OPEN unique_check_cur(New_References.unit_cd,
717 New_References.version_number,
718 New_References.hist_start_dt);
719 FETCH unique_check_cur INTO l_dummy;
720 IF unique_check_cur%NOTFOUND THEN
721 l_count := 0;
722 ELSE
723 l_count := 1;
724 END IF;
725 CLOSE unique_check_cur;
726 END LOOP;
727
728 END IF;
729 Check_Constraints;
730 ELSIF (p_action = 'UPDATE') THEN
731 -- Call all the procedures related to Before Update.
732 Check_Constraints;
733
734 ELSIF (p_action = 'VALIDATE_INSERT') THEN
735 IF Get_PK_For_Validation (New_References.unit_cd,
736 New_References.version_number,
737 New_References.hist_start_dt) THEN
738 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
739 IGS_GE_MSG_STACK.ADD;
740 App_Exception.Raise_Exception;
741 END IF;
742 Check_Constraints;
743 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
744 Check_Constraints;
745
746 END IF;
747
748 END Before_DML;
749
750 PROCEDURE After_DML (
751 p_action IN VARCHAR2,
752 x_rowid IN VARCHAR2
753 ) AS
754 BEGIN
755
756 l_rowid := x_rowid;
757
758
759 END After_DML;
760
761
762 PROCEDURE INSERT_ROW (
763 X_ROWID in out NOCOPY VARCHAR2,
764 X_UNIT_CD in VARCHAR2,
765 X_VERSION_NUMBER in NUMBER,
766 X_HIST_START_DT in DATE,
767 X_HIST_END_DT in DATE,
768 X_HIST_WHO in NUMBER,
769 X_START_DT in DATE,
770 X_REVIEW_DT in DATE,
771 X_EXPIRY_DT in DATE,
772 X_END_DT in DATE,
773 X_UNIT_STATUS in VARCHAR2,
774 X_TITLE in VARCHAR2,
775 X_SHORT_TITLE in VARCHAR2,
776 X_TITLE_OVERRIDE_IND in VARCHAR2,
777 X_ABBREVIATION in VARCHAR2,
778 X_UNIT_LEVEL in VARCHAR2,
779 X_UL_DESCRIPTION in VARCHAR2,
780 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
781 X_ENROLLED_CREDIT_POINTS in NUMBER,
782 X_POINTS_OVERRIDE_IND in VARCHAR2,
783 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
784 X_COORD_PERSON_ID in NUMBER,
785 X_OWNER_ORG_UNIT_CD in VARCHAR2,
786 X_OWNER_OU_START_DT in DATE,
787 X_OU_DESCRIPTION in VARCHAR2,
788 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
789 X_RESEARCH_UNIT_IND in VARCHAR2,
790 X_INDUSTRIAL_IND in VARCHAR2,
791 X_PRACTICAL_IND in VARCHAR2,
792 X_REPEATABLE_IND in VARCHAR2,
793 X_ASSESSABLE_IND in VARCHAR2,
794 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
795 X_POINTS_INCREMENT in NUMBER,
796 X_POINTS_MIN in NUMBER,
797 X_POINTS_MAX in NUMBER,
798 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
799 X_UICL_DESCRIPTION in VARCHAR2,
800 x_subtitle_id IN NUMBER ,
801 x_work_load_other IN NUMBER ,
802 x_contact_hrs_lecture IN NUMBER ,
803 x_contact_hrs_lab IN NUMBER ,
804 x_contact_hrs_other IN NUMBER ,
805 x_non_schd_required_hrs IN NUMBER ,
806 x_exclude_from_max_cp_limit IN VARCHAR2 ,
807 x_record_exclusion_flag IN VARCHAR2 ,
808 x_ss_display_ind IN VARCHAR2 ,
809 x_cal_type_enrol_load_cal IN VARCHAR2 ,
810 x_sequence_num_enrol_load_cal IN NUMBER ,
811 x_cal_type_offer_load_cal IN VARCHAR2 ,
812 x_sequence_num_offer_load_cal IN NUMBER ,
813 x_curriculum_id IN VARCHAR2 ,
814 x_override_enrollment_max IN NUMBER ,
815 x_rpt_fmly_id IN NUMBER ,
816 x_repeat_code IN VARCHAR2 ,
817 x_unit_type_id IN NUMBER ,
818 x_level_code IN VARCHAR2 ,
819 x_advance_maximum IN NUMBER ,
820 x_approval_date IN DATE ,
821 x_continuing_education_units IN NUMBER ,
822 x_enrollment_expected IN NUMBER ,
823 x_enrollment_maximum IN NUMBER ,
824 x_enrollment_minimum IN NUMBER ,
825 x_federal_financial_aid IN VARCHAR2 ,
826 x_institutional_financial_aid IN VARCHAR2 ,
827 x_lab_credit_points IN NUMBER ,
828 x_lecture_credit_points IN NUMBER ,
829 x_max_repeats_for_credit IN NUMBER ,
830 x_max_repeats_for_funding IN NUMBER ,
831 x_max_repeat_credit_points IN NUMBER ,
832 x_clock_hours IN NUMBER ,
833 x_other_credit_points IN NUMBER ,
834 x_same_teaching_period IN VARCHAR2 ,
835 x_same_teach_period_repeats IN NUMBER ,
836 x_same_teach_period_repeats_cp IN NUMBER ,
837 x_state_financial_aid IN VARCHAR2 ,
838 x_work_load_cp_lab IN NUMBER ,
839 x_work_load_cp_lecture IN NUMBER ,
840 x_subtitle_modifiable_flag IN VARCHAR2 ,
841 x_subtitle IN VARCHAR2 ,
842 x_special_permission_ind IN VARCHAR2 ,
843 X_MODE IN VARCHAR2 ,
844 X_ORG_ID IN NUMBER,
845 X_SS_ENROL_IND IN VARCHAR2 ,
846 X_IVR_ENROL_IND IN VARCHAR2 ,
847 x_claimable_hours IN NUMBER ,
848 x_auditable_ind IN VARCHAR2 ,
849 x_audit_permission_ind IN VARCHAR2 ,
850 x_max_auditors_allowed IN NUMBER ,
851 x_billing_credit_points IN NUMBER ,
852 x_ovrd_wkld_val_flag IN VARCHAR2 ,
853 x_workload_val_code IN VARCHAR2 ,
854 x_billing_hrs IN NUMBER
855 ) AS
856 cursor C is select ROWID from IGS_PS_UNIT_VER_HIST_ALL
857 where UNIT_CD = X_UNIT_CD
858 and VERSION_NUMBER = X_VERSION_NUMBER
859 and HIST_START_DT = X_HIST_START_DT;
860 X_LAST_UPDATE_DATE DATE;
861 X_LAST_UPDATED_BY NUMBER;
862 X_LAST_UPDATE_LOGIN NUMBER;
863 begin
864 X_LAST_UPDATE_DATE := SYSDATE;
865 if(X_MODE = 'I') then
866 X_LAST_UPDATED_BY := 1;
867 X_LAST_UPDATE_LOGIN := 0;
868 elsif (X_MODE = 'R') then
869 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
870 if X_LAST_UPDATED_BY is NULL then
871 X_LAST_UPDATED_BY := -1;
872 end if;
873 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
874 if X_LAST_UPDATE_LOGIN is NULL then
875 X_LAST_UPDATE_LOGIN := -1;
876 end if;
877 else
878 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
879 IGS_GE_MSG_STACK.ADD;
880 app_exception.raise_exception;
881 end if;
882
883 Before_DML(
884 p_action => 'INSERT',
885 x_rowid => X_ROWID,
886 x_unit_cd => X_UNIT_CD,
887 x_version_number => X_VERSION_NUMBER,
888 x_hist_start_dt => X_HIST_START_DT,
889 x_hist_end_dt => X_HIST_END_DT,
890 x_hist_who => X_HIST_WHO,
891 x_start_dt => X_START_DT,
892 x_review_dt => X_REVIEW_DT,
893 x_expiry_dt => X_EXPIRY_DT,
894 x_end_dt => X_END_DT,
895 x_unit_status => X_UNIT_STATUS,
896 x_title => X_TITLE,
897 x_short_title => X_SHORT_TITLE,
898 x_title_override_ind => X_TITLE_OVERRIDE_IND,
899 x_abbreviation => X_ABBREVIATION,
900 x_unit_level => X_UNIT_LEVEL,
901 x_ul_description => X_UL_DESCRIPTION,
902 x_credit_point_descriptor => X_CREDIT_POINT_DESCRIPTOR,
903 x_enrolled_credit_points => X_ENROLLED_CREDIT_POINTS,
904 x_points_override_ind => X_POINTS_OVERRIDE_IND,
905 x_supp_exam_permitted_ind => NVL(X_SUPP_EXAM_PERMITTED_IND,'Y'),
906 x_coord_person_id => X_COORD_PERSON_ID,
907 x_owner_org_unit_cd => X_OWNER_ORG_UNIT_CD,
908 x_owner_ou_start_dt => X_OWNER_OU_START_DT,
909 x_ou_description => X_OU_DESCRIPTION,
910 x_award_course_only_ind => X_AWARD_COURSE_ONLY_IND,
911 x_research_unit_ind => X_RESEARCH_UNIT_IND,
912 x_industrial_ind => X_INDUSTRIAL_IND,
913 x_practical_ind => X_PRACTICAL_IND,
914 x_repeatable_ind => X_REPEATABLE_IND,
915 x_assessable_ind => X_ASSESSABLE_IND,
916 x_achievable_credit_points => X_ACHIEVABLE_CREDIT_POINTS,
917 x_points_increment => X_POINTS_INCREMENT,
918 x_points_min => X_POINTS_MIN,
919 x_points_max => X_POINTS_MAX,
920 x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
921 x_uicl_description => X_UICL_DESCRIPTION,
922 x_subtitle_id => x_subtitle_id,
923 x_work_load_other => x_work_load_other,
924 x_contact_hrs_lecture => x_contact_hrs_lecture,
925 x_contact_hrs_lab => x_contact_hrs_lab,
926 x_contact_hrs_other => x_contact_hrs_other,
927 x_non_schd_required_hrs => x_non_schd_required_hrs,
928 x_exclude_from_max_cp_limit => x_exclude_from_max_cp_limit,
929 x_record_exclusion_flag => x_record_exclusion_flag,
930 x_ss_display_ind => x_ss_display_ind,
931 x_cal_type_enrol_load_cal => x_cal_type_enrol_load_cal,
932 x_sequence_num_enrol_load_cal => x_sequence_num_enrol_load_cal,
933 x_cal_type_offer_load_cal => x_cal_type_offer_load_cal,
934 x_sequence_num_offer_load_cal => x_sequence_num_offer_load_cal,
935 x_curriculum_id => x_curriculum_id,
936 x_override_enrollment_max => x_override_enrollment_max,
937 x_rpt_fmly_id => x_rpt_fmly_id,
938 x_repeat_code => x_repeat_code,
939 x_unit_type_id => x_unit_type_id,
940 x_level_code => x_level_code,
941 x_advance_maximum => x_advance_maximum,
942 x_approval_date => x_approval_date,
943 x_continuing_education_units => x_continuing_education_units,
944 x_enrollment_expected => x_enrollment_expected,
945 x_enrollment_maximum => x_enrollment_maximum,
946 x_enrollment_minimum => x_enrollment_minimum,
947 x_federal_financial_aid => x_federal_financial_aid,
948 x_institutional_financial_aid => x_institutional_financial_aid,
949 x_lab_credit_points => x_lab_credit_points,
950 x_lecture_credit_points => x_lecture_credit_points,
951 x_max_repeats_for_credit => x_max_repeats_for_credit,
952 x_max_repeats_for_funding => x_max_repeats_for_funding,
953 x_max_repeat_credit_points => x_max_repeat_credit_points,
954 x_clock_hours => x_clock_hours,
955 x_other_credit_points => x_other_credit_points,
956 x_same_teaching_period => x_same_teaching_period,
957 x_same_teach_period_repeats => x_same_teach_period_repeats,
958 x_same_teach_period_repeats_cp => x_same_teach_period_repeats_cp,
959 x_state_financial_aid => x_state_financial_aid,
960 x_work_load_cp_lab => x_work_load_cp_lab,
961 x_work_load_cp_lecture => x_work_load_cp_lecture,
962 x_subtitle_modifiable_flag => x_subtitle_modifiable_flag,
963 x_subtitle => x_subtitle,
964 x_special_permission_ind => x_special_permission_ind,
965 x_creation_date => X_LAST_UPDATE_DATE,
966 x_created_by => X_LAST_UPDATED_BY,
967 x_last_update_date => X_LAST_UPDATE_DATE,
968 x_last_updated_by => X_LAST_UPDATED_BY,
969 x_last_update_login => X_LAST_UPDATE_LOGIN,
970 x_org_id => igs_ge_gen_003.get_org_id,
971 x_ss_enrol_ind => X_SS_ENROL_IND,
972 x_ivr_enrol_ind => X_IVR_ENROL_IND,
973 x_claimable_hours => x_claimable_hours,
974 x_auditable_ind => x_auditable_ind,
975 x_audit_permission_ind => x_audit_permission_ind,
976 x_max_auditors_allowed => x_max_auditors_allowed,
977 x_billing_credit_points => x_billing_credit_points,
978 x_ovrd_wkld_val_flag => x_ovrd_wkld_val_flag,
979 x_workload_val_code => x_workload_val_code,
980 x_billing_hrs => x_billing_hrs
981 );
982
983 INSERT INTO IGS_PS_UNIT_VER_HIST_ALL (
984 UNIT_CD,
985 VERSION_NUMBER,
986 HIST_START_DT,
987 HIST_END_DT,
988 HIST_WHO,
989 START_DT,
990 REVIEW_DT,
991 EXPIRY_DT,
992 END_DT,
993 UNIT_STATUS,
994 TITLE,
995 SHORT_TITLE,
996 TITLE_OVERRIDE_IND,
997 ABBREVIATION,
998 UNIT_LEVEL,
999 UL_DESCRIPTION,
1000 CREDIT_POINT_DESCRIPTOR,
1001 ENROLLED_CREDIT_POINTS,
1002 POINTS_OVERRIDE_IND,
1003 SUPP_EXAM_PERMITTED_IND,
1004 COORD_PERSON_ID,
1005 OWNER_ORG_UNIT_CD,
1006 OWNER_OU_START_DT,
1007 OU_DESCRIPTION,
1008 AWARD_COURSE_ONLY_IND,
1009 RESEARCH_UNIT_IND,
1010 INDUSTRIAL_IND,
1011 PRACTICAL_IND,
1012 REPEATABLE_IND,
1013 ASSESSABLE_IND,
1014 ACHIEVABLE_CREDIT_POINTS,
1015 POINTS_INCREMENT,
1016 POINTS_MIN,
1017 POINTS_MAX,
1018 UNIT_INT_COURSE_LEVEL_CD,
1019 UICL_DESCRIPTION,
1020 subtitle_id,
1021 work_load_other,
1022 contact_hrs_lecture,
1023 contact_hrs_lab,
1024 contact_hrs_other,
1025 non_schd_required_hrs,
1026 exclude_from_max_cp_limit,
1027 record_exclusion_flag,
1028 ss_display_ind,
1029 cal_type_enrol_load_cal,
1030 sequence_num_enrol_load_cal,
1031 cal_type_offer_load_cal,
1032 sequence_num_offer_load_cal,
1033 curriculum_id,
1034 override_enrollment_max,
1035 rpt_fmly_id,
1036 repeat_code,
1037 unit_type_id,
1038 level_code,
1039 advance_maximum,
1040 approval_date,
1041 continuing_education_units,
1042 enrollment_expected,
1043 enrollment_maximum,
1044 enrollment_minimum,
1045 federal_financial_aid,
1046 institutional_financial_aid,
1047 lab_credit_points,
1048 lecture_credit_points,
1049 max_repeats_for_credit,
1050 max_repeats_for_funding,
1051 max_repeat_credit_points,
1052 clock_hours,
1053 other_credit_points,
1054 same_teaching_period,
1055 same_teach_period_repeats,
1056 same_teach_period_repeats_cp,
1057 state_financial_aid,
1058 work_load_cp_lab,
1059 work_load_cp_lecture,
1060 subtitle_modifiable_flag,
1061 subtitle,
1062 special_permission_ind,
1063 CREATION_DATE,
1064 CREATED_BY,
1065 LAST_UPDATE_DATE,
1066 LAST_UPDATED_BY,
1067 LAST_UPDATE_LOGIN,
1068 ORG_ID,
1069 SS_ENROL_IND,
1070 IVR_ENROL_IND,
1071 claimable_hours,
1072 auditable_ind,
1073 audit_permission_ind,
1074 max_auditors_allowed,
1075 billing_credit_points,
1076 ovrd_wkld_val_flag,
1077 workload_val_code,
1078 billing_hrs
1079 ) values (
1080 NEW_REFERENCES.UNIT_CD,
1081 NEW_REFERENCES.VERSION_NUMBER,
1082 NEW_REFERENCES.HIST_START_DT,
1083 NEW_REFERENCES.HIST_END_DT,
1084 NEW_REFERENCES.HIST_WHO,
1085 NEW_REFERENCES.START_DT,
1086 NEW_REFERENCES.REVIEW_DT,
1087 NEW_REFERENCES.EXPIRY_DT,
1088 NEW_REFERENCES.END_DT,
1089 NEW_REFERENCES.UNIT_STATUS,
1090 NEW_REFERENCES.TITLE,
1091 NEW_REFERENCES.SHORT_TITLE,
1092 NEW_REFERENCES.TITLE_OVERRIDE_IND,
1093 NEW_REFERENCES.ABBREVIATION,
1094 NEW_REFERENCES.UNIT_LEVEL,
1095 NEW_REFERENCES.UL_DESCRIPTION,
1096 NEW_REFERENCES.CREDIT_POINT_DESCRIPTOR,
1097 NEW_REFERENCES.ENROLLED_CREDIT_POINTS,
1098 NEW_REFERENCES.POINTS_OVERRIDE_IND,
1099 NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
1100 NEW_REFERENCES.COORD_PERSON_ID,
1101 NEW_REFERENCES.OWNER_ORG_UNIT_CD,
1102 NEW_REFERENCES.OWNER_OU_START_DT,
1103 NEW_REFERENCES.OU_DESCRIPTION,
1104 NEW_REFERENCES.AWARD_COURSE_ONLY_IND,
1105 NEW_REFERENCES.RESEARCH_UNIT_IND,
1106 NEW_REFERENCES.INDUSTRIAL_IND,
1107 NEW_REFERENCES.PRACTICAL_IND,
1108 NEW_REFERENCES.REPEATABLE_IND,
1109 NEW_REFERENCES.ASSESSABLE_IND,
1110 NEW_REFERENCES.ACHIEVABLE_CREDIT_POINTS,
1111 NEW_REFERENCES.POINTS_INCREMENT,
1112 NEW_REFERENCES.POINTS_MIN,
1113 NEW_REFERENCES.POINTS_MAX,
1114 NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
1115 NEW_REFERENCES.UICL_DESCRIPTION,
1116 new_references.subtitle_id,
1117 new_references.work_load_other,
1118 new_references.contact_hrs_lecture,
1119 new_references.contact_hrs_lab,
1120 new_references.contact_hrs_other,
1121 new_references.non_schd_required_hrs,
1122 new_references.exclude_from_max_cp_limit,
1123 new_references.record_exclusion_flag,
1124 new_references.ss_display_ind,
1125 new_references.cal_type_enrol_load_cal,
1126 new_references.sequence_num_enrol_load_cal,
1127 new_references.cal_type_offer_load_cal,
1128 new_references.sequence_num_offer_load_cal,
1129 new_references.curriculum_id,
1130 new_references.override_enrollment_max,
1131 new_references.rpt_fmly_id,
1132 new_references.repeat_code,
1133 new_references.unit_type_id,
1134 new_references.level_code,
1135 new_references.advance_maximum,
1136 new_references.approval_date,
1137 new_references.continuing_education_units,
1138 new_references.enrollment_expected,
1139 new_references.enrollment_maximum,
1140 new_references.enrollment_minimum,
1141 new_references.federal_financial_aid,
1142 new_references.institutional_financial_aid,
1143 new_references.lab_credit_points,
1144 new_references.lecture_credit_points,
1145 new_references.max_repeats_for_credit,
1146 new_references.max_repeats_for_funding,
1147 new_references.max_repeat_credit_points,
1148 new_references.clock_hours,
1149 new_references.other_credit_points,
1150 new_references.same_teaching_period,
1151 new_references.same_teach_period_repeats,
1152 new_references.same_teach_period_repeats_cp,
1153 new_references.state_financial_aid,
1154 new_references.work_load_cp_lab,
1155 new_references.work_load_cp_lecture,
1156 new_references.subtitle_modifiable_flag,
1157 new_references.subtitle,
1158 new_references.special_permission_ind,
1159 X_LAST_UPDATE_DATE,
1160 X_LAST_UPDATED_BY,
1161 X_LAST_UPDATE_DATE,
1162 X_LAST_UPDATED_BY,
1163 X_LAST_UPDATE_LOGIN,
1164 NEW_REFERENCES.ORG_ID,
1165 NEW_REFERENCES.SS_ENROL_IND,
1166 NEW_REFERENCES.IVR_ENROL_IND,
1167 new_references.claimable_hours,
1168 new_references.auditable_ind,
1169 new_references.audit_permission_ind,
1170 new_references.max_auditors_allowed,
1171 new_references.billing_credit_points,
1172 new_references.ovrd_wkld_val_flag,
1173 new_references.workload_val_code,
1174 new_references.billing_hrs
1175 );
1176
1177 open c;
1178 fetch c into X_ROWID;
1179 if (c%notfound) then
1180 close c;
1181 raise no_data_found;
1182 end if;
1183 close c;
1184 After_DML (
1185 p_action => 'INSERT',
1186 x_rowid => X_ROWID
1187 );
1188
1189 END INSERT_ROW;
1190
1191 PROCEDURE LOCK_ROW (
1192 X_ROWID in VARCHAR2,
1193 X_UNIT_CD in VARCHAR2,
1194 X_VERSION_NUMBER in NUMBER,
1195 X_HIST_START_DT in DATE,
1196 X_HIST_END_DT in DATE,
1197 X_HIST_WHO in NUMBER,
1198 X_START_DT in DATE,
1199 X_REVIEW_DT in DATE,
1200 X_EXPIRY_DT in DATE,
1201 X_END_DT in DATE,
1202 X_UNIT_STATUS in VARCHAR2,
1203 X_TITLE in VARCHAR2,
1204 X_SHORT_TITLE in VARCHAR2,
1205 X_TITLE_OVERRIDE_IND in VARCHAR2,
1206 X_ABBREVIATION in VARCHAR2,
1207 X_UNIT_LEVEL in VARCHAR2,
1208 X_UL_DESCRIPTION in VARCHAR2,
1209 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
1210 X_ENROLLED_CREDIT_POINTS in NUMBER,
1211 X_POINTS_OVERRIDE_IND in VARCHAR2,
1212 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
1213 X_COORD_PERSON_ID in NUMBER,
1214 X_OWNER_ORG_UNIT_CD in VARCHAR2,
1215 X_OWNER_OU_START_DT in DATE,
1216 X_OU_DESCRIPTION in VARCHAR2,
1217 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
1218 X_RESEARCH_UNIT_IND in VARCHAR2,
1219 X_INDUSTRIAL_IND in VARCHAR2,
1220 X_PRACTICAL_IND in VARCHAR2,
1221 X_REPEATABLE_IND in VARCHAR2,
1222 X_ASSESSABLE_IND in VARCHAR2,
1223 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
1224 X_POINTS_INCREMENT in NUMBER,
1225 X_POINTS_MIN in NUMBER,
1226 X_POINTS_MAX in NUMBER,
1227 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
1228 X_UICL_DESCRIPTION in VARCHAR2,
1229 x_subtitle_id IN NUMBER ,
1230 x_work_load_other IN NUMBER ,
1231 x_contact_hrs_lecture IN NUMBER ,
1232 x_contact_hrs_lab IN NUMBER ,
1233 x_contact_hrs_other IN NUMBER ,
1234 x_non_schd_required_hrs IN NUMBER ,
1235 x_exclude_from_max_cp_limit IN VARCHAR2 ,
1236 x_record_exclusion_flag IN VARCHAR2 ,
1237 x_ss_display_ind IN VARCHAR2 ,
1238 x_cal_type_enrol_load_cal IN VARCHAR2 ,
1239 x_sequence_num_enrol_load_cal IN NUMBER ,
1240 x_cal_type_offer_load_cal IN VARCHAR2 ,
1241 x_sequence_num_offer_load_cal IN NUMBER ,
1242 x_curriculum_id IN VARCHAR2 ,
1243 x_override_enrollment_max IN NUMBER ,
1244 x_rpt_fmly_id IN NUMBER ,
1245 x_repeat_code IN VARCHAR2 ,
1246 x_unit_type_id IN NUMBER ,
1247 x_level_code IN VARCHAR2 ,
1248 x_advance_maximum IN NUMBER ,
1249 x_approval_date IN DATE ,
1250 x_continuing_education_units IN NUMBER ,
1251 x_enrollment_expected IN NUMBER ,
1252 x_enrollment_maximum IN NUMBER ,
1253 x_enrollment_minimum IN NUMBER ,
1254 x_federal_financial_aid IN VARCHAR2 ,
1255 x_institutional_financial_aid IN VARCHAR2 ,
1256 x_lab_credit_points IN NUMBER ,
1257 x_lecture_credit_points IN NUMBER ,
1258 x_max_repeats_for_credit IN NUMBER ,
1259 x_max_repeats_for_funding IN NUMBER ,
1260 x_max_repeat_credit_points IN NUMBER ,
1261 x_clock_hours IN NUMBER ,
1262 x_other_credit_points IN NUMBER ,
1263 x_same_teaching_period IN VARCHAR2 ,
1264 x_same_teach_period_repeats IN NUMBER ,
1265 x_same_teach_period_repeats_cp IN NUMBER ,
1266 x_state_financial_aid IN VARCHAR2 ,
1267 x_work_load_cp_lab IN NUMBER ,
1268 x_work_load_cp_lecture IN NUMBER ,
1269 x_subtitle_modifiable_flag IN VARCHAR2 ,
1270 x_subtitle IN VARCHAR2 ,
1271 x_special_permission_ind IN VARCHAR2 ,
1272 X_SS_ENROL_IND IN VARCHAR2,
1273 X_IVR_ENROL_IND IN VARCHAR2,
1274 x_claimable_hours IN NUMBER ,
1275 x_auditable_ind IN VARCHAR2 ,
1276 x_audit_permission_ind IN VARCHAR2 ,
1277 x_max_auditors_allowed IN NUMBER ,
1278 x_billing_credit_points IN NUMBER ,
1279 x_ovrd_wkld_val_flag IN VARCHAR2 ,
1280 x_workload_val_code IN VARCHAR2 ,
1281 x_billing_hrs IN NUMBER
1282 ) AS
1283 cursor c1 is select
1284 HIST_END_DT,
1285 HIST_WHO,
1286 START_DT,
1287 REVIEW_DT,
1288 EXPIRY_DT,
1289 END_DT,
1290 UNIT_STATUS,
1291 TITLE,
1292 SHORT_TITLE,
1293 TITLE_OVERRIDE_IND,
1294 ABBREVIATION,
1295 UNIT_LEVEL,
1296 UL_DESCRIPTION,
1297 CREDIT_POINT_DESCRIPTOR,
1298 ENROLLED_CREDIT_POINTS,
1299 POINTS_OVERRIDE_IND,
1300 SUPP_EXAM_PERMITTED_IND,
1301 COORD_PERSON_ID,
1302 OWNER_ORG_UNIT_CD,
1303 OWNER_OU_START_DT,
1304 OU_DESCRIPTION,
1305 AWARD_COURSE_ONLY_IND,
1306 RESEARCH_UNIT_IND,
1307 INDUSTRIAL_IND,
1308 PRACTICAL_IND,
1309 REPEATABLE_IND,
1310 ASSESSABLE_IND,
1311 ACHIEVABLE_CREDIT_POINTS,
1312 POINTS_INCREMENT,
1313 POINTS_MIN,
1314 POINTS_MAX,
1315 UNIT_INT_COURSE_LEVEL_CD,
1316 UICL_DESCRIPTION,
1317 subtitle_id,
1318 work_load_other,
1319 contact_hrs_lecture,
1320 contact_hrs_lab,
1321 contact_hrs_other,
1322 non_schd_required_hrs,
1323 exclude_from_max_cp_limit,
1324 record_exclusion_flag,
1325 ss_display_ind,
1326 cal_type_enrol_load_cal,
1327 sequence_num_enrol_load_cal,
1328 cal_type_offer_load_cal,
1329 sequence_num_offer_load_cal,
1330 curriculum_id,
1331 override_enrollment_max,
1332 rpt_fmly_id,
1333 repeat_code,
1334 unit_type_id,
1335 level_code,
1336 advance_maximum,
1337 approval_date,
1338 continuing_education_units,
1339 enrollment_expected,
1340 enrollment_maximum,
1341 enrollment_minimum,
1342 federal_financial_aid,
1343 institutional_financial_aid,
1344 lab_credit_points,
1345 lecture_credit_points,
1346 max_repeats_for_credit,
1347 max_repeats_for_funding,
1348 max_repeat_credit_points,
1349 clock_hours,
1350 other_credit_points,
1351 same_teaching_period,
1352 same_teach_period_repeats,
1353 same_teach_period_repeats_cp,
1354 state_financial_aid,
1355 work_load_cp_lab,
1356 work_load_cp_lecture,
1357 subtitle_modifiable_flag,
1358 subtitle,
1359 special_permission_ind,
1360 SS_ENROL_IND,
1361 IVR_ENROL_IND,
1362 claimable_hours,
1363 auditable_ind,
1364 audit_permission_ind,
1365 max_auditors_allowed,
1366 billing_credit_points,
1367 ovrd_wkld_val_flag,
1368 workload_val_code,
1369 billing_hrs
1370 from IGS_PS_UNIT_VER_HIST_ALL
1371 where ROWID = X_ROWID for update nowait;
1372 tlinfo c1%rowtype;
1373
1374 begin
1375 open c1;
1376 fetch c1 into tlinfo;
1377 if (c1%notfound) then
1378 close c1;
1379 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1380 IGS_GE_MSG_STACK.ADD;
1381 app_exception.raise_exception;
1382 return;
1383 end if;
1384 close c1;
1385
1386 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
1387 AND (tlinfo.HIST_WHO = X_HIST_WHO)
1388 AND ((tlinfo.START_DT = X_START_DT)
1389 OR ((tlinfo.START_DT is null)
1390 AND (X_START_DT is null)))
1391 AND ((tlinfo.REVIEW_DT = X_REVIEW_DT)
1392 OR ((tlinfo.REVIEW_DT is null)
1393 AND (X_REVIEW_DT is null)))
1394 AND ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
1395 OR ((tlinfo.EXPIRY_DT is null)
1396 AND (X_EXPIRY_DT is null)))
1397 AND ((tlinfo.END_DT = X_END_DT)
1398 OR ((tlinfo.END_DT is null)
1399 AND (X_END_DT is null)))
1400 AND ((tlinfo.UNIT_STATUS = X_UNIT_STATUS)
1401 OR ((tlinfo.UNIT_STATUS is null)
1402 AND (X_UNIT_STATUS is null)))
1403 AND ((tlinfo.TITLE = X_TITLE)
1404 OR ((tlinfo.TITLE is null)
1405 AND (X_TITLE is null)))
1406 AND ((tlinfo.SHORT_TITLE = X_SHORT_TITLE)
1407 OR ((tlinfo.SHORT_TITLE is null)
1408 AND (X_SHORT_TITLE is null)))
1409 AND ((tlinfo.TITLE_OVERRIDE_IND = X_TITLE_OVERRIDE_IND)
1410 OR ((tlinfo.TITLE_OVERRIDE_IND is null)
1411 AND (X_TITLE_OVERRIDE_IND is null)))
1412 AND ((tlinfo.ABBREVIATION = X_ABBREVIATION)
1413 OR ((tlinfo.ABBREVIATION is null)
1414 AND (X_ABBREVIATION is null)))
1415 AND ((tlinfo.UNIT_LEVEL = X_UNIT_LEVEL)
1416 OR ((tlinfo.UNIT_LEVEL is null)
1417 AND (X_UNIT_LEVEL is null)))
1418 AND ((tlinfo.UL_DESCRIPTION = X_UL_DESCRIPTION)
1419 OR ((tlinfo.UL_DESCRIPTION is null)
1420 AND (X_UL_DESCRIPTION is null)))
1421 AND ((tlinfo.CREDIT_POINT_DESCRIPTOR = X_CREDIT_POINT_DESCRIPTOR)
1422 OR ((tlinfo.CREDIT_POINT_DESCRIPTOR is null)
1423 AND (X_CREDIT_POINT_DESCRIPTOR is null)))
1424 AND ((tlinfo.ENROLLED_CREDIT_POINTS = X_ENROLLED_CREDIT_POINTS)
1425 OR ((tlinfo.ENROLLED_CREDIT_POINTS is null)
1426 AND (X_ENROLLED_CREDIT_POINTS is null)))
1427 AND ((tlinfo.POINTS_OVERRIDE_IND = X_POINTS_OVERRIDE_IND)
1428 OR ((tlinfo.POINTS_OVERRIDE_IND is null)
1429 AND (X_POINTS_OVERRIDE_IND is null)))
1430 AND ((tlinfo.SUPP_EXAM_PERMITTED_IND = X_SUPP_EXAM_PERMITTED_IND)
1431 OR ((tlinfo.SUPP_EXAM_PERMITTED_IND is null)
1432 AND (X_SUPP_EXAM_PERMITTED_IND is null)))
1433 AND ((tlinfo.COORD_PERSON_ID = X_COORD_PERSON_ID)
1434 OR ((tlinfo.COORD_PERSON_ID is null)
1435 AND (X_COORD_PERSON_ID is null)))
1436 AND ((tlinfo.OWNER_ORG_UNIT_CD = X_OWNER_ORG_UNIT_CD)
1437 OR ((tlinfo.OWNER_ORG_UNIT_CD is null)
1438 AND (X_OWNER_ORG_UNIT_CD is null)))
1439 AND ((tlinfo.OWNER_OU_START_DT = X_OWNER_OU_START_DT)
1440 OR ((tlinfo.OWNER_OU_START_DT is null)
1441 AND (X_OWNER_OU_START_DT is null)))
1442 AND ((tlinfo.OU_DESCRIPTION = X_OU_DESCRIPTION)
1443 OR ((tlinfo.OU_DESCRIPTION is null)
1444 AND (X_OU_DESCRIPTION is null)))
1445 AND ((tlinfo.AWARD_COURSE_ONLY_IND = X_AWARD_COURSE_ONLY_IND)
1446 OR ((tlinfo.AWARD_COURSE_ONLY_IND is null)
1447 AND (X_AWARD_COURSE_ONLY_IND is null)))
1448 AND ((tlinfo.RESEARCH_UNIT_IND = X_RESEARCH_UNIT_IND)
1449 OR ((tlinfo.RESEARCH_UNIT_IND is null)
1450 AND (X_RESEARCH_UNIT_IND is null)))
1451 AND ((tlinfo.INDUSTRIAL_IND = X_INDUSTRIAL_IND)
1452 OR ((tlinfo.INDUSTRIAL_IND is null)
1453 AND (X_INDUSTRIAL_IND is null)))
1454 AND ((tlinfo.PRACTICAL_IND = X_PRACTICAL_IND)
1455 OR ((tlinfo.PRACTICAL_IND is null)
1456 AND (X_PRACTICAL_IND is null)))
1457 AND ((tlinfo.REPEATABLE_IND = X_REPEATABLE_IND)
1458 OR ((tlinfo.REPEATABLE_IND is null)
1459 AND (X_REPEATABLE_IND is null)))
1460 AND ((tlinfo.ASSESSABLE_IND = X_ASSESSABLE_IND)
1461 OR ((tlinfo.ASSESSABLE_IND is null)
1462 AND (X_ASSESSABLE_IND is null)))
1463 AND ((tlinfo.ACHIEVABLE_CREDIT_POINTS = X_ACHIEVABLE_CREDIT_POINTS)
1464 OR ((tlinfo.ACHIEVABLE_CREDIT_POINTS is null)
1465 AND (X_ACHIEVABLE_CREDIT_POINTS is null)))
1466 AND ((tlinfo.POINTS_INCREMENT = X_POINTS_INCREMENT)
1467 OR ((tlinfo.POINTS_INCREMENT is null)
1468 AND (X_POINTS_INCREMENT is null)))
1469 AND ((tlinfo.POINTS_MIN = X_POINTS_MIN)
1470 OR ((tlinfo.POINTS_MIN is null)
1471 AND (X_POINTS_MIN is null)))
1472 AND ((tlinfo.POINTS_MAX = X_POINTS_MAX)
1473 OR ((tlinfo.POINTS_MAX is null)
1474 AND (X_POINTS_MAX is null)))
1475 AND ((tlinfo.UNIT_INT_COURSE_LEVEL_CD = X_UNIT_INT_COURSE_LEVEL_CD)
1476 OR ((tlinfo.UNIT_INT_COURSE_LEVEL_CD is null)
1477 AND (X_UNIT_INT_COURSE_LEVEL_CD is null)))
1478 AND ((tlinfo.UICL_DESCRIPTION = X_UICL_DESCRIPTION)
1479 OR ((tlinfo.UICL_DESCRIPTION is null)
1480 AND (X_SS_ENROL_IND is null)))
1481 AND ((tlinfo.subtitle_id = x_subtitle_id)
1482 OR ((tlinfo.subtitle_id IS NULL)
1483 AND (X_subtitle_id IS NULL)))
1484 AND ((tlinfo.work_load_other = x_work_load_other)
1485 OR ((tlinfo.work_load_other IS NULL)
1486 AND (X_work_load_other IS NULL)))
1487 AND ((tlinfo.contact_hrs_lecture = x_contact_hrs_lecture)
1488 OR ((tlinfo.contact_hrs_lecture IS NULL)
1489 AND (X_contact_hrs_lecture IS NULL)))
1490 AND ((tlinfo.contact_hrs_lab = x_contact_hrs_lab)
1491 OR ((tlinfo.contact_hrs_lab IS NULL)
1492 AND (X_contact_hrs_lab IS NULL)))
1493 AND ((tlinfo.contact_hrs_other = x_contact_hrs_other)
1494 OR ((tlinfo.contact_hrs_other IS NULL)
1495 AND (X_contact_hrs_other IS NULL)))
1496 AND ((tlinfo.non_schd_required_hrs = x_non_schd_required_hrs)
1497 OR ((tlinfo.non_schd_required_hrs IS NULL)
1498 AND (X_non_schd_required_hrs IS NULL)))
1499 AND ((tlinfo.exclude_from_max_cp_limit = x_exclude_from_max_cp_limit)
1500 OR ((tlinfo.exclude_from_max_cp_limit IS NULL)
1501 AND (X_exclude_from_max_cp_limit IS NULL)))
1502 AND ((tlinfo.record_exclusion_flag = x_record_exclusion_flag)
1503 OR ((tlinfo.record_exclusion_flag IS NULL)
1504 AND (X_record_exclusion_flag IS NULL)))
1505 AND ((tlinfo.ss_display_ind = x_ss_display_ind)
1506 OR ((tlinfo.ss_display_ind IS NULL) AND (X_ss_display_ind IS NULL)))
1507 AND ((tlinfo.cal_type_enrol_load_cal = x_cal_type_enrol_load_cal)
1508 OR ((tlinfo.cal_type_enrol_load_cal IS NULL)
1509 AND (X_cal_type_enrol_load_cal IS NULL)))
1510 AND ((tlinfo.sequence_num_enrol_load_cal = x_sequence_num_enrol_load_cal)
1511 OR ((tlinfo.sequence_num_enrol_load_cal IS NULL)
1512 AND (X_sequence_num_enrol_load_cal IS NULL)))
1513 AND ((tlinfo.cal_type_offer_load_cal = x_cal_type_offer_load_cal)
1514 OR ((tlinfo.cal_type_offer_load_cal IS NULL)
1515 AND (X_cal_type_offer_load_cal IS NULL)))
1516 AND ((tlinfo.sequence_num_offer_load_cal = x_sequence_num_offer_load_cal)
1517 OR ((tlinfo.sequence_num_offer_load_cal IS NULL)
1518 AND (X_sequence_num_offer_load_cal IS NULL)))
1519 AND ((tlinfo.curriculum_id = x_curriculum_id)
1520 OR ((tlinfo.curriculum_id IS NULL)
1521 AND (X_curriculum_id IS NULL)))
1522 AND ((tlinfo.override_enrollment_max = x_override_enrollment_max)
1523 OR ((tlinfo.override_enrollment_max IS NULL)
1524 AND (X_override_enrollment_max IS NULL)))
1525 AND ((tlinfo.rpt_fmly_id = x_rpt_fmly_id)
1526 OR ((tlinfo.rpt_fmly_id IS NULL)
1527 AND (X_rpt_fmly_id IS NULL)))
1528 AND ((tlinfo.repeat_code = x_repeat_code)
1529 OR ((tlinfo.repeat_code IS NULL)
1530 AND (X_repeat_code IS NULL)))
1531 AND ((tlinfo.unit_type_id = x_unit_type_id)
1532 OR ((tlinfo.unit_type_id IS NULL)
1533 AND (X_unit_type_id IS NULL)))
1534 AND ((tlinfo.level_code = x_level_code)
1535 OR ((tlinfo.level_code IS NULL)
1536 AND (X_level_code IS NULL)))
1537 AND ((tlinfo.advance_maximum = x_advance_maximum)
1538 OR ((tlinfo.advance_maximum IS NULL)
1539 AND (X_advance_maximum IS NULL)))
1540 AND ((tlinfo.approval_date = x_approval_date)
1541 OR ((tlinfo.approval_date IS NULL)
1542 AND (X_approval_date IS NULL)))
1543 AND ((tlinfo.continuing_education_units = x_continuing_education_units)
1544 OR ((tlinfo.continuing_education_units IS NULL)
1545 AND (X_continuing_education_units IS NULL)))
1546 AND ((tlinfo.enrollment_expected = x_enrollment_expected)
1547 OR ((tlinfo.enrollment_expected IS NULL)
1548 AND (X_enrollment_expected IS NULL)))
1549 AND ((tlinfo.enrollment_maximum = x_enrollment_maximum)
1550 OR ((tlinfo.enrollment_maximum IS NULL)
1551 AND (X_enrollment_maximum IS NULL)))
1552 AND ((tlinfo.enrollment_minimum = x_enrollment_minimum)
1553 OR ((tlinfo.enrollment_minimum IS NULL)
1554 AND (X_enrollment_minimum IS NULL)))
1555 AND ((tlinfo.federal_financial_aid = x_federal_financial_aid)
1556 OR ((tlinfo.federal_financial_aid IS NULL)
1557 AND (X_federal_financial_aid IS NULL)))
1558 AND ((tlinfo.institutional_financial_aid = x_institutional_financial_aid)
1559 OR ((tlinfo.institutional_financial_aid IS NULL)
1560 AND (X_institutional_financial_aid IS NULL)))
1561 AND ((tlinfo.lab_credit_points = x_lab_credit_points)
1562 OR ((tlinfo.lab_credit_points IS NULL)
1563 AND (X_lab_credit_points IS NULL)))
1564 AND ((tlinfo.lecture_credit_points = x_lecture_credit_points)
1565 OR ((tlinfo.lecture_credit_points IS NULL)
1566 AND (X_lecture_credit_points IS NULL)))
1567 AND ((tlinfo.max_repeats_for_credit = x_max_repeats_for_credit)
1568 OR ((tlinfo.max_repeats_for_credit IS NULL)
1569 AND (X_max_repeats_for_credit IS NULL)))
1570 AND ((tlinfo.max_repeats_for_funding = x_max_repeats_for_funding)
1571 OR ((tlinfo.max_repeats_for_funding IS NULL)
1572 AND (X_max_repeats_for_funding IS NULL)))
1573 AND ((tlinfo.max_repeat_credit_points = x_max_repeat_credit_points)
1574 OR ((tlinfo.max_repeat_credit_points IS NULL)
1575 AND (X_max_repeat_credit_points IS NULL)))
1576 AND ((tlinfo.clock_hours = x_clock_hours)
1577 OR ((tlinfo.clock_hours IS NULL)
1578 AND (X_clock_hours IS NULL)))
1579 AND ((tlinfo.other_credit_points = x_other_credit_points)
1580 OR ((tlinfo.other_credit_points IS NULL)
1581 AND (X_other_credit_points IS NULL)))
1582 AND ((tlinfo.same_teaching_period = x_same_teaching_period)
1583 OR ((tlinfo.same_teaching_period IS NULL)
1584 AND (X_same_teaching_period IS NULL)))
1585 AND ((tlinfo.same_teach_period_repeats = x_same_teach_period_repeats)
1586 OR ((tlinfo.same_teach_period_repeats IS NULL)
1587 AND (X_same_teach_period_repeats IS NULL)))
1588 AND ((tlinfo.same_teach_period_repeats_cp = x_same_teach_period_repeats_cp)
1589 OR ((tlinfo.same_teach_period_repeats_cp IS NULL)
1590 AND (X_same_teach_period_repeats_cp IS NULL)))
1591 AND ((tlinfo.state_financial_aid = x_state_financial_aid)
1592 OR ((tlinfo.state_financial_aid IS NULL)
1593 AND (X_state_financial_aid IS NULL)))
1594 AND ((tlinfo.work_load_cp_lab = x_work_load_cp_lab)
1595 OR ((tlinfo.work_load_cp_lab IS NULL)
1596 AND (X_work_load_cp_lab IS NULL)))
1597 AND ((tlinfo.work_load_cp_lecture = x_work_load_cp_lecture)
1598 OR ((tlinfo.work_load_cp_lecture IS NULL)
1599 AND (X_work_load_cp_lecture IS NULL)))
1600 AND ((tlinfo.subtitle_modifiable_flag = x_subtitle_modifiable_flag)
1601 OR ((tlinfo.subtitle_modifiable_flag IS NULL)
1602 AND (X_subtitle_modifiable_flag IS NULL)))
1603 AND ((tlinfo.subtitle = x_subtitle)
1604 OR ((tlinfo.subtitle IS NULL)
1605 AND (X_subtitle IS NULL)))
1606 AND ((tlinfo.special_permission_ind = x_special_permission_ind)
1607 OR ((tlinfo.special_permission_ind IS NULL)
1608 AND (X_special_permission_ind IS NULL)))
1609 AND ((tlinfo.SS_ENROL_IND = X_SS_ENROL_IND )
1610 OR ((tlinfo.SS_ENROL_IND is null)
1611 AND (X_SS_ENROL_IND is null)))
1612 AND ((tlinfo.IVR_ENROL_IND = X_IVR_ENROL_IND )
1613 OR ((tlinfo.IVR_ENROL_IND is null)
1614 AND (X_IVR_ENROL_IND is null)))
1615 AND ((tlinfo.claimable_hours= x_claimable_hours)
1616 OR ((tlinfo.claimable_hours is null)
1617 AND (x_claimable_hours is null)))
1618 AND ((tlinfo.auditable_ind = x_auditable_ind)
1619 OR ((tlinfo.auditable_ind IS NULL)
1620 AND (X_auditable_ind IS NULL)))
1621 AND ((tlinfo.audit_permission_ind = x_audit_permission_ind)
1622 OR ((tlinfo.audit_permission_ind IS NULL)
1623 AND (X_audit_permission_ind IS NULL)))
1624 AND ((tlinfo.max_auditors_allowed = x_max_auditors_allowed)
1625 OR ((tlinfo.max_auditors_allowed IS NULL)
1626 AND (X_max_auditors_allowed IS NULL)))
1627 AND ((tlinfo.billing_credit_points = x_billing_credit_points)
1628 OR ((tlinfo.billing_credit_points IS NULL)
1629 AND (X_billing_credit_points IS NULL)))
1630 AND ((tlinfo.OVRD_WKLD_VAL_FLAG= X_OVRD_WKLD_VAL_FLAG)
1631 OR ((tlinfo.OVRD_WKLD_VAL_FLAG IS NULL)
1632 AND (X_OVRD_WKLD_VAL_FLAG IS NULL)))
1633 AND ((tlinfo.WORKLOAD_VAL_CODE= X_WORKLOAD_VAL_CODE)
1634 OR ((tlinfo.WORKLOAD_VAL_CODE IS NULL)
1635 AND (X_WORKLOAD_VAL_CODE IS NULL)))
1636 AND ((tlinfo.BILLING_HRS= X_BILLING_HRS)
1637 OR ((tlinfo.BILLING_HRS IS NULL)
1638 AND (X_BILLING_HRS IS NULL)))
1639 ) THEN
1640 NULL;
1641 else
1642 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1643 IGS_GE_MSG_STACK.ADD;
1644 app_exception.raise_exception;
1645 end if;
1646 return;
1647 END LOCK_ROW;
1648
1649 PROCEDURE UPDATE_ROW (
1650 X_ROWID in VARCHAR2,
1651 X_UNIT_CD in VARCHAR2,
1652 X_VERSION_NUMBER in NUMBER,
1653 X_HIST_START_DT in DATE,
1654 X_HIST_END_DT in DATE,
1655 X_HIST_WHO in NUMBER,
1656 X_START_DT in DATE,
1657 X_REVIEW_DT in DATE,
1658 X_EXPIRY_DT in DATE,
1659 X_END_DT in DATE,
1660 X_UNIT_STATUS in VARCHAR2,
1661 X_TITLE in VARCHAR2,
1662 X_SHORT_TITLE in VARCHAR2,
1663 X_TITLE_OVERRIDE_IND in VARCHAR2,
1664 X_ABBREVIATION in VARCHAR2,
1665 X_UNIT_LEVEL in VARCHAR2,
1666 X_UL_DESCRIPTION in VARCHAR2,
1667 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
1668 X_ENROLLED_CREDIT_POINTS in NUMBER,
1669 X_POINTS_OVERRIDE_IND in VARCHAR2,
1670 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
1671 X_COORD_PERSON_ID in NUMBER,
1672 X_OWNER_ORG_UNIT_CD in VARCHAR2,
1673 X_OWNER_OU_START_DT in DATE,
1674 X_OU_DESCRIPTION in VARCHAR2,
1675 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
1676 X_RESEARCH_UNIT_IND in VARCHAR2,
1677 X_INDUSTRIAL_IND in VARCHAR2,
1678 X_PRACTICAL_IND in VARCHAR2,
1679 X_REPEATABLE_IND in VARCHAR2,
1680 X_ASSESSABLE_IND in VARCHAR2,
1681 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
1682 X_POINTS_INCREMENT in NUMBER,
1683 X_POINTS_MIN in NUMBER,
1684 X_POINTS_MAX in NUMBER,
1685 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
1686 X_UICL_DESCRIPTION in VARCHAR2,
1687 x_subtitle_id IN NUMBER ,
1688 x_work_load_other IN NUMBER ,
1689 x_contact_hrs_lecture IN NUMBER ,
1690 x_contact_hrs_lab IN NUMBER ,
1691 x_contact_hrs_other IN NUMBER ,
1692 x_non_schd_required_hrs IN NUMBER ,
1693 x_exclude_from_max_cp_limit IN VARCHAR2 ,
1694 x_record_exclusion_flag IN VARCHAR2 ,
1695 x_ss_display_ind IN VARCHAR2 ,
1696 x_cal_type_enrol_load_cal IN VARCHAR2 ,
1697 x_sequence_num_enrol_load_cal IN NUMBER ,
1698 x_cal_type_offer_load_cal IN VARCHAR2 ,
1699 x_sequence_num_offer_load_cal IN NUMBER ,
1700 x_curriculum_id IN VARCHAR2 ,
1701 x_override_enrollment_max IN NUMBER ,
1702 x_rpt_fmly_id IN NUMBER ,
1703 x_repeat_code IN VARCHAR2 ,
1704 x_unit_type_id IN NUMBER ,
1705 x_level_code IN VARCHAR2 ,
1706 x_advance_maximum IN NUMBER ,
1707 x_approval_date IN DATE ,
1708 x_continuing_education_units IN NUMBER ,
1709 x_enrollment_expected IN NUMBER ,
1710 x_enrollment_maximum IN NUMBER ,
1711 x_enrollment_minimum IN NUMBER ,
1712 x_federal_financial_aid IN VARCHAR2 ,
1713 x_institutional_financial_aid IN VARCHAR2 ,
1714 x_lab_credit_points IN NUMBER ,
1715 x_lecture_credit_points IN NUMBER ,
1716 x_max_repeats_for_credit IN NUMBER ,
1717 x_max_repeats_for_funding IN NUMBER ,
1718 x_max_repeat_credit_points IN NUMBER ,
1719 x_clock_hours IN NUMBER ,
1720 x_other_credit_points IN NUMBER ,
1721 x_same_teaching_period IN VARCHAR2 ,
1722 x_same_teach_period_repeats IN NUMBER ,
1723 x_same_teach_period_repeats_cp IN NUMBER ,
1724 x_state_financial_aid IN VARCHAR2 ,
1725 x_work_load_cp_lab IN NUMBER ,
1726 x_work_load_cp_lecture IN NUMBER ,
1727 x_subtitle_modifiable_flag IN VARCHAR2 ,
1728 x_subtitle IN VARCHAR2 ,
1729 x_special_permission_ind IN VARCHAR2 ,
1730 X_MODE IN VARCHAR2 ,
1731 X_SS_ENROL_IND IN VARCHAR2 ,
1732 X_IVR_ENROL_IND IN VARCHAR2 ,
1733 x_claimable_hours IN NUMBER ,
1734 x_auditable_ind IN VARCHAR2 ,
1735 x_audit_permission_ind IN VARCHAR2 ,
1736 x_max_auditors_allowed IN NUMBER,
1737 x_billing_credit_points IN NUMBER ,
1738 x_ovrd_wkld_val_flag IN VARCHAR2 ,
1739 x_workload_val_code IN VARCHAR2 ,
1740 x_billing_hrs IN NUMBER
1741 ) AS
1742 X_LAST_UPDATE_DATE DATE;
1743 X_LAST_UPDATED_BY NUMBER;
1744 X_LAST_UPDATE_LOGIN NUMBER;
1745 begin
1746 X_LAST_UPDATE_DATE := SYSDATE;
1747 if(X_MODE = 'I') then
1748 X_LAST_UPDATED_BY := 1;
1749 X_LAST_UPDATE_LOGIN := 0;
1750 elsif (X_MODE = 'R') then
1751 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1752 if X_LAST_UPDATED_BY is NULL then
1753 X_LAST_UPDATED_BY := -1;
1754 end if;
1755 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1756 if X_LAST_UPDATE_LOGIN is NULL then
1757 X_LAST_UPDATE_LOGIN := -1;
1758 end if;
1759 else
1760 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1761 IGS_GE_MSG_STACK.ADD;
1762 app_exception.raise_exception;
1763 end if;
1764 Before_DML(
1765 p_action => 'UPDATE',
1766 x_rowid => X_ROWID,
1767 x_unit_cd => X_UNIT_CD,
1768 x_version_number => X_VERSION_NUMBER,
1769 x_hist_start_dt => X_HIST_START_DT,
1770 x_hist_end_dt => X_HIST_END_DT,
1771 x_hist_who => X_HIST_WHO,
1772 x_start_dt => X_START_DT,
1773 x_review_dt => X_REVIEW_DT,
1774 x_expiry_dt => X_EXPIRY_DT,
1775 x_end_dt => X_END_DT,
1776 x_unit_status => X_UNIT_STATUS,
1777 x_title => X_TITLE,
1778 x_short_title => X_SHORT_TITLE,
1779 x_title_override_ind => X_TITLE_OVERRIDE_IND,
1780 x_abbreviation => X_ABBREVIATION,
1781 x_unit_level => X_UNIT_LEVEL,
1782 x_ul_description => X_UL_DESCRIPTION,
1783 x_credit_point_descriptor => X_CREDIT_POINT_DESCRIPTOR,
1784 x_enrolled_credit_points => X_ENROLLED_CREDIT_POINTS,
1785 x_points_override_ind => X_POINTS_OVERRIDE_IND,
1786 x_supp_exam_permitted_ind => X_SUPP_EXAM_PERMITTED_IND,
1787 x_coord_person_id => X_COORD_PERSON_ID,
1788 x_owner_org_unit_cd => X_OWNER_ORG_UNIT_CD,
1789 x_owner_ou_start_dt => X_OWNER_OU_START_DT,
1790 x_ou_description => X_OU_DESCRIPTION,
1791 x_award_course_only_ind => X_AWARD_COURSE_ONLY_IND,
1792 x_research_unit_ind => X_RESEARCH_UNIT_IND,
1793 x_industrial_ind => X_INDUSTRIAL_IND,
1794 x_practical_ind => X_PRACTICAL_IND,
1795 x_repeatable_ind => X_REPEATABLE_IND,
1796 x_assessable_ind => X_ASSESSABLE_IND,
1797 x_achievable_credit_points => X_ACHIEVABLE_CREDIT_POINTS,
1798 x_points_increment => X_POINTS_INCREMENT,
1799 x_points_min => X_POINTS_MIN,
1800 x_points_max => X_POINTS_MAX,
1801 x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
1802 x_uicl_description => X_UICL_DESCRIPTION,
1803 x_subtitle_id => x_subtitle_id,
1804 x_work_load_other => x_work_load_other,
1805 x_contact_hrs_lecture => x_contact_hrs_lecture,
1806 x_contact_hrs_lab => x_contact_hrs_lab,
1807 x_contact_hrs_other => x_contact_hrs_other,
1808 x_non_schd_required_hrs => x_non_schd_required_hrs,
1809 x_exclude_from_max_cp_limit => x_exclude_from_max_cp_limit,
1810 x_record_exclusion_flag => x_record_exclusion_flag,
1811 x_ss_display_ind => x_ss_display_ind,
1812 x_cal_type_enrol_load_cal => x_cal_type_enrol_load_cal,
1813 x_sequence_num_enrol_load_cal => x_sequence_num_enrol_load_cal,
1814 x_cal_type_offer_load_cal => x_cal_type_offer_load_cal,
1815 x_sequence_num_offer_load_cal => x_sequence_num_offer_load_cal,
1816 x_curriculum_id => x_curriculum_id,
1817 x_override_enrollment_max => x_override_enrollment_max,
1818 x_rpt_fmly_id => x_rpt_fmly_id,
1819 x_repeat_code => x_repeat_code,
1820 x_unit_type_id => x_unit_type_id,
1821 x_level_code => x_level_code,
1822 x_advance_maximum => x_advance_maximum,
1823 x_approval_date => x_approval_date,
1824 x_continuing_education_units => x_continuing_education_units,
1825 x_enrollment_expected => x_enrollment_expected,
1826 x_enrollment_maximum => x_enrollment_maximum,
1827 x_enrollment_minimum => x_enrollment_minimum,
1828 x_federal_financial_aid => x_federal_financial_aid,
1829 x_institutional_financial_aid => x_institutional_financial_aid,
1830 x_lab_credit_points => x_lab_credit_points,
1831 x_lecture_credit_points => x_lecture_credit_points,
1832 x_max_repeats_for_credit => x_max_repeats_for_credit,
1833 x_max_repeats_for_funding => x_max_repeats_for_funding,
1834 x_max_repeat_credit_points => x_max_repeat_credit_points,
1835 x_clock_hours => x_clock_hours,
1836 x_other_credit_points => x_other_credit_points,
1837 x_same_teaching_period => x_same_teaching_period,
1838 x_same_teach_period_repeats => x_same_teach_period_repeats,
1839 x_same_teach_period_repeats_cp => x_same_teach_period_repeats_cp,
1840 x_state_financial_aid => x_state_financial_aid,
1841 x_work_load_cp_lab => x_work_load_cp_lab,
1842 x_work_load_cp_lecture => x_work_load_cp_lecture,
1843 x_subtitle_modifiable_flag => x_subtitle_modifiable_flag,
1844 x_subtitle => x_subtitle,
1845 x_special_permission_ind => x_special_permission_ind,
1846 x_creation_date => X_LAST_UPDATE_DATE,
1847 x_created_by => X_LAST_UPDATED_BY,
1848 x_last_update_date => X_LAST_UPDATE_DATE,
1849 x_last_updated_by => X_LAST_UPDATED_BY,
1850 x_last_update_login => X_LAST_UPDATE_LOGIN,
1851 x_ss_enrol_ind => X_SS_ENROL_IND,
1852 x_ivr_enrol_ind => X_IVR_ENROL_IND,
1853 x_claimable_hours => x_claimable_hours,
1854 x_auditable_ind => x_auditable_ind,
1855 x_audit_permission_ind => x_audit_permission_ind,
1856 x_max_auditors_allowed => x_max_auditors_allowed,
1857 x_billing_credit_points => x_billing_credit_points,
1858 x_ovrd_wkld_val_flag => x_ovrd_wkld_val_flag ,
1859 x_workload_val_code => x_workload_val_code,
1860 x_billing_hrs => x_billing_hrs
1861 );
1862
1863 UPDATE IGS_PS_UNIT_VER_HIST_ALL SET
1864 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
1865 HIST_WHO = NEW_REFERENCES.HIST_WHO,
1866 START_DT = NEW_REFERENCES.START_DT,
1867 REVIEW_DT = NEW_REFERENCES.REVIEW_DT,
1868 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
1869 END_DT = NEW_REFERENCES.END_DT,
1870 UNIT_STATUS = NEW_REFERENCES.UNIT_STATUS,
1871 TITLE = NEW_REFERENCES.TITLE,
1872 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
1873 TITLE_OVERRIDE_IND = NEW_REFERENCES.TITLE_OVERRIDE_IND,
1874 ABBREVIATION = NEW_REFERENCES.ABBREVIATION,
1875 UNIT_LEVEL = NEW_REFERENCES.UNIT_LEVEL,
1876 UL_DESCRIPTION = NEW_REFERENCES.UL_DESCRIPTION,
1877 CREDIT_POINT_DESCRIPTOR = NEW_REFERENCES.CREDIT_POINT_DESCRIPTOR,
1878 ENROLLED_CREDIT_POINTS = NEW_REFERENCES.ENROLLED_CREDIT_POINTS,
1879 POINTS_OVERRIDE_IND = NEW_REFERENCES.POINTS_OVERRIDE_IND,
1880 SUPP_EXAM_PERMITTED_IND = NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
1881 COORD_PERSON_ID = NEW_REFERENCES.COORD_PERSON_ID,
1882 OWNER_ORG_UNIT_CD = NEW_REFERENCES.OWNER_ORG_UNIT_CD,
1883 OWNER_OU_START_DT = NEW_REFERENCES.OWNER_OU_START_DT,
1884 OU_DESCRIPTION = NEW_REFERENCES.OU_DESCRIPTION,
1885 AWARD_COURSE_ONLY_IND = NEW_REFERENCES.AWARD_COURSE_ONLY_IND,
1886 RESEARCH_UNIT_IND = NEW_REFERENCES.RESEARCH_UNIT_IND,
1887 INDUSTRIAL_IND = NEW_REFERENCES.INDUSTRIAL_IND,
1888 PRACTICAL_IND = NEW_REFERENCES.PRACTICAL_IND,
1889 REPEATABLE_IND = NEW_REFERENCES.REPEATABLE_IND,
1890 ASSESSABLE_IND = NEW_REFERENCES.ASSESSABLE_IND,
1891 ACHIEVABLE_CREDIT_POINTS = NEW_REFERENCES.ACHIEVABLE_CREDIT_POINTS,
1892 POINTS_INCREMENT = NEW_REFERENCES.POINTS_INCREMENT,
1893 POINTS_MIN = NEW_REFERENCES.POINTS_MIN,
1894 POINTS_MAX = NEW_REFERENCES.POINTS_MAX,
1895 UNIT_INT_COURSE_LEVEL_CD = NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
1896 UICL_DESCRIPTION = NEW_REFERENCES.UICL_DESCRIPTION,
1897 subtitle_id = new_references.subtitle_id,
1898 work_load_other = new_references.work_load_other,
1899 contact_hrs_lecture = new_references.contact_hrs_lecture,
1900 contact_hrs_lab = new_references.contact_hrs_lab,
1901 contact_hrs_other = new_references.contact_hrs_other,
1902 non_schd_required_hrs = new_references.non_schd_required_hrs,
1903 exclude_from_max_cp_limit = new_references.exclude_from_max_cp_limit,
1904 record_exclusion_flag = new_references.record_exclusion_flag,
1905 ss_display_ind = new_references.ss_display_ind,
1906 cal_type_enrol_load_cal = new_references.cal_type_enrol_load_cal,
1907 sequence_num_enrol_load_cal = new_references.sequence_num_enrol_load_cal,
1908 cal_type_offer_load_cal = new_references.cal_type_offer_load_cal,
1909 sequence_num_offer_load_cal = new_references.sequence_num_offer_load_cal,
1910 curriculum_id = new_references.curriculum_id,
1911 override_enrollment_max = new_references.override_enrollment_max,
1912 rpt_fmly_id = new_references.rpt_fmly_id,
1913 repeat_code = new_references.repeat_code,
1914 unit_type_id = new_references.unit_type_id,
1915 level_code = new_references.level_code,
1916 advance_maximum = new_references.advance_maximum,
1917 approval_date = new_references.approval_date,
1918 continuing_education_units = new_references.continuing_education_units,
1919 enrollment_expected = new_references.enrollment_expected,
1920 enrollment_maximum = new_references.enrollment_maximum,
1921 enrollment_minimum = new_references.enrollment_minimum,
1922 federal_financial_aid = new_references.federal_financial_aid,
1923 institutional_financial_aid = new_references.institutional_financial_aid,
1924 lab_credit_points = new_references.lab_credit_points,
1925 lecture_credit_points = new_references.lecture_credit_points,
1926 max_repeats_for_credit = new_references.max_repeats_for_credit,
1927 max_repeats_for_funding = new_references.max_repeats_for_funding,
1928 max_repeat_credit_points = new_references.max_repeat_credit_points,
1929 clock_hours = new_references.clock_hours,
1930 other_credit_points = new_references.other_credit_points,
1931 same_teaching_period = new_references.same_teaching_period,
1932 same_teach_period_repeats = new_references.same_teach_period_repeats,
1933 same_teach_period_repeats_cp = new_references.same_teach_period_repeats_cp,
1934 state_financial_aid = new_references.state_financial_aid,
1935 work_load_cp_lab = new_references.work_load_cp_lab,
1936 work_load_cp_lecture = new_references.work_load_cp_lecture,
1937 subtitle_modifiable_flag = new_references.subtitle_modifiable_flag,
1938 subtitle = new_references.subtitle,
1939 special_permission_ind = new_references.special_permission_ind,
1940 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1941 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1942 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1943 SS_ENROL_IND = X_SS_ENROL_IND,
1944 IVR_ENROL_IND = IVR_ENROL_IND,
1945 claimable_hours = new_references.claimable_hours,
1946 auditable_ind = new_references.auditable_ind,
1947 audit_permission_ind = new_references.audit_permission_ind,
1948 max_auditors_allowed = new_references.max_auditors_allowed,
1949 billing_credit_points = new_references.billing_credit_points,
1950 ovrd_wkld_val_flag = new_references.ovrd_wkld_val_flag,
1951 workload_val_code = new_references.workload_val_code ,
1952 billing_hrs = new_references.billing_hrs
1953 where ROWID = X_ROWID
1954 ;
1955 if (sql%notfound) then
1956 raise no_data_found;
1957 end if;
1958 After_DML (
1959 p_action => 'UPDATE',
1960 x_rowid => X_ROWID
1961 );
1962
1963 END UPDATE_ROW;
1964
1965 PROCEDURE ADD_ROW (
1966 X_ROWID in out NOCOPY VARCHAR2,
1967 X_UNIT_CD in VARCHAR2,
1968 X_VERSION_NUMBER in NUMBER,
1969 X_HIST_START_DT in DATE,
1970 X_HIST_END_DT in DATE,
1971 X_HIST_WHO in NUMBER,
1972 X_START_DT in DATE,
1973 X_REVIEW_DT in DATE,
1974 X_EXPIRY_DT in DATE,
1975 X_END_DT in DATE,
1976 X_UNIT_STATUS in VARCHAR2,
1977 X_TITLE in VARCHAR2,
1978 X_SHORT_TITLE in VARCHAR2,
1979 X_TITLE_OVERRIDE_IND in VARCHAR2,
1980 X_ABBREVIATION in VARCHAR2,
1981 X_UNIT_LEVEL in VARCHAR2,
1982 X_UL_DESCRIPTION in VARCHAR2,
1983 X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
1984 X_ENROLLED_CREDIT_POINTS in NUMBER,
1985 X_POINTS_OVERRIDE_IND in VARCHAR2,
1986 X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
1987 X_COORD_PERSON_ID in NUMBER,
1988 X_OWNER_ORG_UNIT_CD in VARCHAR2,
1989 X_OWNER_OU_START_DT in DATE,
1990 X_OU_DESCRIPTION in VARCHAR2,
1991 X_AWARD_COURSE_ONLY_IND in VARCHAR2,
1992 X_RESEARCH_UNIT_IND in VARCHAR2,
1993 X_INDUSTRIAL_IND in VARCHAR2,
1994 X_PRACTICAL_IND in VARCHAR2,
1995 X_REPEATABLE_IND in VARCHAR2,
1996 X_ASSESSABLE_IND in VARCHAR2,
1997 X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
1998 X_POINTS_INCREMENT in NUMBER,
1999 X_POINTS_MIN in NUMBER,
2000 X_POINTS_MAX in NUMBER,
2001 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
2002 X_UICL_DESCRIPTION in VARCHAR2,
2003 x_subtitle_id IN NUMBER ,
2004 x_work_load_other IN NUMBER ,
2005 x_contact_hrs_lecture IN NUMBER ,
2006 x_contact_hrs_lab IN NUMBER ,
2007 x_contact_hrs_other IN NUMBER ,
2008 x_non_schd_required_hrs IN NUMBER ,
2009 x_exclude_from_max_cp_limit IN VARCHAR2 ,
2010 x_record_exclusion_flag IN VARCHAR2 ,
2011 x_ss_display_ind IN VARCHAR2 ,
2012 x_cal_type_enrol_load_cal IN VARCHAR2 ,
2013 x_sequence_num_enrol_load_cal IN NUMBER ,
2014 x_cal_type_offer_load_cal IN VARCHAR2 ,
2015 x_sequence_num_offer_load_cal IN NUMBER ,
2016 x_curriculum_id IN VARCHAR2 ,
2017 x_override_enrollment_max IN NUMBER ,
2018 x_rpt_fmly_id IN NUMBER ,
2019 x_repeat_code IN VARCHAR2 ,
2020 x_unit_type_id IN NUMBER ,
2021 x_level_code IN VARCHAR2 ,
2022 x_advance_maximum IN NUMBER ,
2023 x_approval_date IN DATE ,
2024 x_continuing_education_units IN NUMBER ,
2025 x_enrollment_expected IN NUMBER ,
2026 x_enrollment_maximum IN NUMBER ,
2027 x_enrollment_minimum IN NUMBER ,
2028 x_federal_financial_aid IN VARCHAR2 ,
2029 x_institutional_financial_aid IN VARCHAR2 ,
2030 x_lab_credit_points IN NUMBER ,
2031 x_lecture_credit_points IN NUMBER ,
2032 x_max_repeats_for_credit IN NUMBER ,
2033 x_max_repeats_for_funding IN NUMBER ,
2034 x_max_repeat_credit_points IN NUMBER ,
2035 x_clock_hours IN NUMBER ,
2036 x_other_credit_points IN NUMBER ,
2037 x_same_teaching_period IN VARCHAR2 ,
2038 x_same_teach_period_repeats IN NUMBER ,
2039 x_same_teach_period_repeats_cp IN NUMBER ,
2040 x_state_financial_aid IN VARCHAR2 ,
2041 x_work_load_cp_lab IN NUMBER ,
2042 x_work_load_cp_lecture IN NUMBER ,
2043 x_subtitle_modifiable_flag IN VARCHAR2 ,
2044 x_subtitle IN VARCHAR2 ,
2045 x_special_permission_ind IN VARCHAR2 ,
2046 X_MODE IN VARCHAR2 ,
2047 X_ORG_ID IN NUMBER,
2048 X_SS_ENROL_IND IN VARCHAR2,
2049 X_IVR_ENROL_IND IN VARCHAR2,
2050 x_claimable_hours IN NUMBER ,
2051 x_auditable_ind IN VARCHAR2 ,
2052 x_audit_permission_ind IN VARCHAR2 ,
2053 x_max_auditors_allowed IN NUMBER ,
2054 x_billing_credit_points IN NUMBER ,
2055 x_ovrd_wkld_val_flag IN VARCHAR2,
2056 x_workload_val_code IN VARCHAR2 ,
2057 x_billing_hrs IN NUMBER
2058 ) AS
2059 cursor c1 is select rowid from IGS_PS_UNIT_VER_HIST_ALL
2060 where UNIT_CD = X_UNIT_CD
2061 and VERSION_NUMBER = X_VERSION_NUMBER
2062 and HIST_START_DT = X_HIST_START_DT
2063 ;
2064 begin
2065 open c1;
2066 fetch c1 into X_ROWID;
2067 if (c1%notfound) then
2068 close c1;
2069 INSERT_ROW (
2070 X_ROWID,
2071 X_UNIT_CD,
2072 X_VERSION_NUMBER,
2073 X_HIST_START_DT,
2074 X_HIST_END_DT,
2075 X_HIST_WHO,
2076 X_START_DT,
2077 X_REVIEW_DT,
2078 X_EXPIRY_DT,
2079 X_END_DT,
2080 X_UNIT_STATUS,
2081 X_TITLE,
2082 X_SHORT_TITLE,
2083 X_TITLE_OVERRIDE_IND,
2084 X_ABBREVIATION,
2085 X_UNIT_LEVEL,
2086 X_UL_DESCRIPTION,
2087 X_CREDIT_POINT_DESCRIPTOR,
2088 X_ENROLLED_CREDIT_POINTS,
2089 X_POINTS_OVERRIDE_IND,
2090 X_SUPP_EXAM_PERMITTED_IND,
2091 X_COORD_PERSON_ID,
2092 X_OWNER_ORG_UNIT_CD,
2093 X_OWNER_OU_START_DT,
2094 X_OU_DESCRIPTION,
2095 X_AWARD_COURSE_ONLY_IND,
2096 X_RESEARCH_UNIT_IND,
2097 X_INDUSTRIAL_IND,
2098 X_PRACTICAL_IND,
2099 X_REPEATABLE_IND,
2100 X_ASSESSABLE_IND,
2101 X_ACHIEVABLE_CREDIT_POINTS,
2102 X_POINTS_INCREMENT,
2103 X_POINTS_MIN,
2104 X_POINTS_MAX,
2105 X_UNIT_INT_COURSE_LEVEL_CD,
2106 X_UICL_DESCRIPTION,
2107 x_subtitle_id,
2108 x_work_load_other,
2109 x_contact_hrs_lecture,
2110 x_contact_hrs_lab,
2111 x_contact_hrs_other,
2112 x_non_schd_required_hrs,
2113 x_exclude_from_max_cp_limit,
2114 x_record_exclusion_flag,
2115 x_ss_display_ind,
2116 x_cal_type_enrol_load_cal,
2117 x_sequence_num_enrol_load_cal,
2118 x_cal_type_offer_load_cal,
2119 x_sequence_num_offer_load_cal,
2120 x_curriculum_id,
2121 x_override_enrollment_max,
2122 x_rpt_fmly_id,
2123 x_repeat_code,
2124 x_unit_type_id,
2125 x_level_code,
2126 x_advance_maximum,
2127 x_approval_date,
2128 x_continuing_education_units,
2129 x_enrollment_expected,
2130 x_enrollment_maximum,
2131 x_enrollment_minimum,
2132 x_federal_financial_aid,
2133 x_institutional_financial_aid,
2134 x_lab_credit_points,
2135 x_lecture_credit_points,
2136 x_max_repeats_for_credit,
2137 x_max_repeats_for_funding,
2138 x_max_repeat_credit_points,
2139 x_clock_hours,
2140 x_other_credit_points,
2141 x_same_teaching_period,
2142 x_same_teach_period_repeats,
2143 x_same_teach_period_repeats_cp,
2144 x_state_financial_aid,
2145 x_work_load_cp_lab,
2146 x_work_load_cp_lecture,
2147 x_subtitle_modifiable_flag,
2148 x_subtitle,
2149 x_special_permission_ind,
2150 X_MODE,
2151 X_ORG_ID,
2152 X_SS_ENROL_IND,
2153 X_IVR_ENROL_IND,
2154 x_claimable_hours,
2155 x_auditable_ind,
2156 x_audit_permission_ind,
2157 x_max_auditors_allowed,
2158 x_billing_credit_points,
2159 x_ovrd_wkld_val_flag,
2160 x_workload_val_code,
2161 x_billing_hrs
2162 );
2163 return;
2164 end if;
2165 close c1;
2166 UPDATE_ROW (
2167 X_ROWID,
2168 X_UNIT_CD,
2169 X_VERSION_NUMBER,
2170 X_HIST_START_DT,
2171 X_HIST_END_DT,
2172 X_HIST_WHO,
2173 X_START_DT,
2174 X_REVIEW_DT,
2175 X_EXPIRY_DT,
2176 X_END_DT,
2177 X_UNIT_STATUS,
2178 X_TITLE,
2179 X_SHORT_TITLE,
2180 X_TITLE_OVERRIDE_IND,
2181 X_ABBREVIATION,
2182 X_UNIT_LEVEL,
2183 X_UL_DESCRIPTION,
2184 X_CREDIT_POINT_DESCRIPTOR,
2185 X_ENROLLED_CREDIT_POINTS,
2186 X_POINTS_OVERRIDE_IND,
2187 X_SUPP_EXAM_PERMITTED_IND,
2188 X_COORD_PERSON_ID,
2189 X_OWNER_ORG_UNIT_CD,
2190 X_OWNER_OU_START_DT,
2191 X_OU_DESCRIPTION,
2192 X_AWARD_COURSE_ONLY_IND,
2193 X_RESEARCH_UNIT_IND,
2194 X_INDUSTRIAL_IND,
2195 X_PRACTICAL_IND,
2196 X_REPEATABLE_IND,
2197 X_ASSESSABLE_IND,
2198 X_ACHIEVABLE_CREDIT_POINTS,
2199 X_POINTS_INCREMENT,
2200 X_POINTS_MIN,
2201 X_POINTS_MAX,
2202 X_UNIT_INT_COURSE_LEVEL_CD,
2203 X_UICL_DESCRIPTION,
2204 x_subtitle_id,
2205 x_work_load_other,
2206 x_contact_hrs_lecture,
2207 x_contact_hrs_lab,
2208 x_contact_hrs_other,
2209 x_non_schd_required_hrs,
2210 x_exclude_from_max_cp_limit,
2211 x_record_exclusion_flag,
2212 x_ss_display_ind,
2213 x_cal_type_enrol_load_cal,
2214 x_sequence_num_enrol_load_cal,
2215 x_cal_type_offer_load_cal,
2216 x_sequence_num_offer_load_cal,
2217 x_curriculum_id,
2218 x_override_enrollment_max,
2219 x_rpt_fmly_id,
2220 x_repeat_code,
2221 x_unit_type_id,
2222 x_level_code,
2223 x_advance_maximum,
2224 x_approval_date,
2225 x_continuing_education_units,
2226 x_enrollment_expected,
2227 x_enrollment_maximum,
2228 x_enrollment_minimum,
2229 x_federal_financial_aid,
2230 x_institutional_financial_aid,
2231 x_lab_credit_points,
2232 x_lecture_credit_points,
2233 x_max_repeats_for_credit,
2234 x_max_repeats_for_funding,
2235 x_max_repeat_credit_points,
2236 x_clock_hours,
2237 x_other_credit_points,
2238 x_same_teaching_period,
2239 x_same_teach_period_repeats,
2240 x_same_teach_period_repeats_cp,
2241 x_state_financial_aid,
2242 x_work_load_cp_lab,
2243 x_work_load_cp_lecture,
2244 x_subtitle_modifiable_flag,
2245 x_subtitle,
2246 x_special_permission_ind,
2247 X_MODE,
2248 X_SS_ENROL_IND,
2249 X_IVR_ENROL_IND,
2250 x_claimable_hours,
2251 x_auditable_ind,
2252 x_audit_permission_ind,
2253 x_max_auditors_allowed,
2254 x_billing_credit_points,
2255 x_ovrd_wkld_val_flag,
2256 x_workload_val_code,
2257 x_billing_hrs
2258 );
2259 END ADD_ROW;
2260
2261 PROCEDURE DELETE_ROW (
2262 X_ROWID in VARCHAR2
2263 ) AS
2264
2265
2266 BEGIN
2267 Before_DML (
2268 p_action => 'DELETE',
2269 x_rowid => X_ROWID
2270 );
2271 delete from IGS_PS_UNIT_VER_HIST_ALL
2272 where ROWID = X_ROWID;
2273 if (sql%notfound) then
2274 raise no_data_found;
2275 end if;
2276 After_DML (
2277 p_action => 'DELETE',
2278 x_rowid => X_ROWID
2279 );
2280
2281 END DELETE_ROW;
2282
2283 END IGS_PS_UNIT_VER_HIST_PKG;