[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_VER_PKG
Source
1 PACKAGE BODY igs_ps_ver_pkg AS
2 /* $Header: IGSPI42B.pls 120.2 2005/08/11 03:35:57 appldev ship $ */
3 /************************************************************************
4 Created By :
5 Date Created By :
6 Purpose :
7 Known limitations, enhancements or remarks:
8 Change History :
9 Who When What
10 msrinivi 08-Aug-2001 Added new col rev_Account_cd bug # 1882122
11 ayedubat 25-MAY-2001 Changed for the DLD,PSPS001-US
12 smadathi 16-MAY-2001 Changed For New DLD Version
13 sbeerell 10-MAY-2000 Changed for new DLD version 2
14 (reverse chronological order - newest change first)
15 *************************************************************************/
16 l_rowid VARCHAR2(25);
17 old_references igs_ps_ver_all%ROWTYPE;
18 new_references igs_ps_ver_all%ROWTYPE;
19
20
21 PROCEDURE beforerowdelete AS
22 ------------------------------------------------------------------
23 --Created by : sarakshi, Oracle India
24 --Date created: 16-Jun-2002
25 --
26 --Purpose: Only planned course status are allowed for deletion
27 --
28 --
29 --Known limitations/enhancements and/or remarks:
30 --
31 --Change History:
32 --Who When What
33 -------------------------------------------------------------------
34 CURSOR cur_delete (cp_course_cd igs_ps_ver_all.course_cd%TYPE,
35 cp_version_number igs_ps_ver_all.version_number%TYPE)
36 IS
37 SELECT 'x'
38 FROM igs_ps_ver_all uv,
39 igs_ps_stat us
40 WHERE uv.course_status=us.course_status
41 AND us.s_course_status='PLANNED'
42 AND uv.course_cd = cp_course_cd
43 AND uv.version_number = cp_version_number;
44
45 l_check VARCHAR2(1);
46
47 BEGIN
48 -- Only planned course status are allowed for deletion
49 OPEN cur_delete (old_references.course_cd,old_references.version_number);
50 FETCH cur_delete INTO l_check;
51 IF cur_delete%NOTFOUND THEN
52 CLOSE cur_delete;
53 fnd_message.set_name('IGS','IGS_PS_NO_DEL_ALLOWED');
54 igs_ge_msg_stack.add;
55 app_exception.raise_exception;
56 END IF;
57 CLOSE cur_delete;
58 END beforerowdelete;
59
60
61 PROCEDURE beforerowupdate AS
62 ------------------------------------------------------------------
63 --Created by : sarakshi, Oracle India
64 --Date created: 16-Jun-2002
65 --
66 --Purpose: Active/Inactive program Status can not be changed to Planned Status
67 --
68 --
69 --Known limitations/enhancements and/or remarks:
70 --
71 --Change History:
72 --Who When What
73 -------------------------------------------------------------------
74 CURSOR cur_get_status (cp_course_status igs_ps_stat.course_status%TYPE)
75 IS
76 SELECT s_course_status
77 FROM igs_ps_stat
78 WHERE course_status = cp_course_status;
79 l_s_course_status igs_ps_stat.s_course_status%TYPE;
80
81 CURSOR cur_check_update (cp_course_cd igs_ps_ver_all.course_cd%TYPE,
82 cp_version_number igs_ps_ver_all.version_number%TYPE)
83 IS
84 SELECT 'x'
85 FROM igs_ps_ver_all uv,
86 igs_ps_stat us
87 WHERE uv.course_status=us.course_status
88 AND us.s_course_status <> 'PLANNED'
89 AND uv.course_cd = cp_course_cd
90 AND uv.version_number = cp_version_number;
91
92 l_check VARCHAR2(1);
93 BEGIN
94 -- Active/Inactive program Status can not be changed to Planned Status
95 OPEN cur_get_status(new_references.course_status);
96 FETCH cur_get_status INTO l_s_course_status;
97 IF cur_get_status%FOUND THEN
98 CLOSE cur_get_status;
99 IF (l_s_course_status = 'PLANNED') THEN
100 OPEN cur_check_update(old_references.course_cd,old_references.version_number);
101 FETCH cur_check_update INTO l_check;
102 IF cur_check_update%FOUND THEN
103 CLOSE cur_check_update;
104 fnd_message.set_name('IGS','IGS_PS_NO_INACTIVE_PLN');
105 igs_ge_msg_stack.add;
106 app_exception.raise_exception;
107 END IF;
108 CLOSE cur_check_update;
109 END IF;
110 ELSE
111 -- If the program status is not found then the record might have been deleted
112 CLOSE cur_get_status;
113 fnd_message.set_name('FND','FORM_RECORD_DELETED');
114 igs_ge_msg_stack.add;
115 app_exception.raise_exception;
116 END IF;
117 END beforerowupdate;
118
119 PROCEDURE set_column_values (
120 p_action IN VARCHAR2,
121 x_rowid IN VARCHAR2,
122 x_course_cd IN VARCHAR2 ,
123 x_version_number IN NUMBER ,
124 x_start_dt IN DATE ,
125 x_review_dt IN DATE ,
126 x_expiry_dt IN DATE ,
127 x_end_dt IN DATE ,
128 x_course_status IN VARCHAR2 ,
129 x_title IN VARCHAR2 ,
130 x_short_title IN VARCHAR2 ,
131 x_abbreviation IN VARCHAR2 ,
132 x_supp_exam_permitted_ind IN VARCHAR2 ,
133 x_generic_course_ind IN VARCHAR2 ,
134 x_graduate_students_ind IN VARCHAR2 ,
135 x_count_intrmsn_in_time_ind IN VARCHAR2 ,
136 x_intrmsn_allowed_ind IN VARCHAR2 ,
137 x_course_type IN VARCHAR2 ,
138 x_responsible_org_unit_cd IN VARCHAR2 ,
139 x_responsible_ou_start_dt IN DATE ,
140 x_govt_special_course_type IN VARCHAR2 ,
141 x_qualification_recency IN NUMBER ,
142 x_external_adv_stnd_limit IN NUMBER ,
143 x_internal_adv_stnd_limit IN NUMBER ,
144 x_contact_hours IN NUMBER ,
145 x_credit_points_required IN NUMBER ,
146 x_govt_course_load IN NUMBER ,
147 x_std_annual_load IN NUMBER ,
148 x_course_total_eftsu IN NUMBER ,
149 x_max_intrmsn_duration IN NUMBER ,
150 x_num_of_units_before_intrmsn IN NUMBER ,
151 x_min_sbmsn_percentage IN NUMBER ,
152 x_max_cp_per_teaching_period IN NUMBER ,
153 x_approval_date IN DATE ,
154 x_external_approval_date IN DATE ,
155 x_residency_cp_required IN NUMBER ,
156 x_state_financial_aid IN VARCHAR2 ,
157 x_federal_financial_aid IN VARCHAR2 ,
158 x_institutional_financial_aid IN VARCHAR2 ,
159 x_attribute_category IN VARCHAR2 ,
160 x_attribute1 IN VARCHAR2 ,
161 x_attribute2 IN VARCHAR2 ,
162 x_attribute3 IN VARCHAR2 ,
163 x_attribute4 IN VARCHAR2 ,
164 x_attribute5 IN VARCHAR2 ,
165 x_attribute6 IN VARCHAR2 ,
166 x_attribute7 IN VARCHAR2 ,
167 x_attribute8 IN VARCHAR2 ,
168 x_attribute9 IN VARCHAR2 ,
169 x_attribute10 IN VARCHAR2 ,
170 x_attribute11 IN VARCHAR2 ,
171 x_attribute12 IN VARCHAR2 ,
172 x_attribute13 IN VARCHAR2 ,
173 x_attribute14 IN VARCHAR2 ,
174 x_attribute15 IN VARCHAR2 ,
175 x_attribute16 IN VARCHAR2 ,
176 x_attribute17 IN VARCHAR2 ,
177 x_attribute18 IN VARCHAR2 ,
178 x_attribute19 IN VARCHAR2 ,
179 x_attribute20 IN VARCHAR2 ,
180 x_min_cp_per_calendar IN NUMBER ,
181 x_creation_date IN DATE ,
182 x_created_by IN NUMBER ,
183 x_last_update_date IN DATE ,
184 x_last_updated_by IN NUMBER ,
185 x_last_update_login IN NUMBER ,
186 x_org_id IN NUMBER ,
187 x_rev_account_cd IN VARCHAR2 ,
188 x_primary_program_rank IN NUMBER,
189 x_max_wlst_per_stud IN NUMBER,
190 x_annual_instruction_time IN NUMBER
191 ) AS
192 /************************************************************************
193 Created By :
194 Date Created By :
195 Purpose :
196 Known limitations, enhancements or remarks:
197 Change History :
198 Who When What
199 vvutukur 18-Oct-2002 Enh#2608227.Removed references to obsoleted columns std_ft_completion_time,
200 std_pt_completion_time.Also removed DEFAULT keyword to avoid gscc File.Pkg.22 warning.
201 msrinivi 08-Aug-2001 Added new col rev_Account_cd bug # 1882122
202 smadathi 26-MAY-2001 Changed for new DLD Version
203 sbeerell 10-MAY-2000 Changed for new DLD version 2
204 (reverse chronological order - newest change first)
205 *************************************************************************/
206
207 CURSOR cur_old_ref_values IS
208 SELECT *
209 FROM igs_ps_ver_all
210 WHERE ROWID = x_rowid;
211
212 BEGIN
213
214 l_rowid := x_rowid;
215
216 -- Code for setting the Old and New Reference Values.
217 -- Populate Old Values.
218 OPEN cur_old_ref_values;
219 FETCH cur_old_ref_values INTO old_references;
220 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
221 CLOSE cur_old_ref_values;
222 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 RETURN;
226 END IF;
227 CLOSE cur_old_ref_values;
228
229 -- Populate New Values.
230 new_references.min_sbmsn_percentage := x_min_sbmsn_percentage;
231 new_references.course_cd := x_course_cd;
232 new_references.version_number := x_version_number;
233 new_references.start_dt := x_start_dt;
234 new_references.review_dt := x_review_dt;
235 new_references.expiry_dt := x_expiry_dt;
236 new_references.end_dt := x_end_dt;
237 new_references.course_status := x_course_status;
238 new_references.title := x_title;
239 new_references.short_title := x_short_title;
240 new_references.abbreviation := x_abbreviation;
241 new_references.supp_exam_permitted_ind := x_supp_exam_permitted_ind;
242 new_references.generic_course_ind := x_generic_course_ind;
243 new_references.graduate_students_ind := x_graduate_students_ind;
244 new_references.count_intrmsn_in_time_ind := x_count_intrmsn_in_time_ind;
245 new_references.intrmsn_allowed_ind := x_intrmsn_allowed_ind;
246 new_references.course_type := x_course_type;
247 new_references.responsible_org_unit_cd := x_responsible_org_unit_cd;
248 new_references.responsible_ou_start_dt := x_responsible_ou_start_dt;
249 new_references.govt_special_course_type := x_govt_special_course_type;
250 new_references.qualification_recency := x_qualification_recency;
251 new_references.external_adv_stnd_limit := x_external_adv_stnd_limit;
252 new_references.internal_adv_stnd_limit := x_internal_adv_stnd_limit;
253 new_references.contact_hours := x_contact_hours;
254 new_references.credit_points_required := x_credit_points_required;
255 new_references.govt_course_load := x_govt_course_load;
256 new_references.std_annual_load := x_std_annual_load;
257 new_references.course_total_eftsu := x_course_total_eftsu;
258 new_references.max_intrmsn_duration := x_max_intrmsn_duration;
259 new_references.num_of_units_before_intrmsn := x_num_of_units_before_intrmsn;
260 new_references.max_cp_per_teaching_period := x_max_cp_per_teaching_period;
261 new_references.approval_date := x_approval_date;
262 new_references.external_approval_date := x_external_approval_date;
263 new_references.residency_cp_required := x_residency_cp_required;
264 new_references.state_financial_aid := x_state_financial_aid;
265 new_references.federal_financial_aid := x_federal_financial_aid;
266 new_references.institutional_financial_aid := x_institutional_financial_aid;
267 new_references.attribute_category := x_attribute_category;
268 new_references.attribute1 := x_attribute1;
269 new_references.attribute2 := x_attribute2;
270 new_references.attribute3 := x_attribute3;
271 new_references.attribute4 := x_attribute4;
272 new_references.attribute5 := x_attribute5;
273 new_references.attribute6 := x_attribute6;
274 new_references.attribute7 := x_attribute7;
275 new_references.attribute8 := x_attribute8;
276 new_references.attribute9 := x_attribute9;
277 new_references.attribute10 := x_attribute10;
278 new_references.attribute11 := x_attribute11;
279 new_references.attribute12 := x_attribute12;
280 new_references.attribute13 := x_attribute13;
281 new_references.attribute14 := x_attribute14;
282 new_references.attribute15 := x_attribute15;
283 new_references.attribute16 := x_attribute16;
284 new_references.attribute17 := x_attribute17;
285 new_references.attribute18 := x_attribute18;
286 new_references.attribute19 := x_attribute19;
287 new_references.attribute20 := x_attribute20;
288 new_references.min_cp_per_calendar := x_min_cp_per_calendar;
289 new_references.org_id := x_org_id;
290 new_references.rev_account_cd := x_rev_account_cd;
291 new_references.primary_program_rank := x_primary_program_rank;
292 new_references.max_wlst_per_stud := x_max_wlst_per_stud;
293 new_references.annual_instruction_time := x_annual_instruction_time;
294
295 IF (p_action = 'UPDATE') THEN
296 new_references.creation_date := old_references.creation_date;
297 new_references.created_by := old_references.created_by;
298 ELSE
299 new_references.creation_date := x_creation_date;
300 new_references.created_by := x_created_by;
301 END IF;
302 new_references.last_update_date := x_last_update_date;
303 new_references.last_updated_by := x_last_updated_by;
304 new_references.last_update_login := x_last_update_login;
305
306 END set_column_values;
307
308 PROCEDURE rowvalmutation(
309 p_inserting IN BOOLEAN ,
310 p_updating IN BOOLEAN,
311 p_deleting IN BOOLEAN
312 ) AS
313 /************************************************************************
314 Created By :
315 Date Created By :
316 Purpose :
317 Known limitations, enhancements or remarks:
318 Change History :
319 Who When What
320 sbeerell 10-MAY-2000 Changed for new DLD version 2
321 (reverse chronological order - newest change first)
322 *************************************************************************/
323 v_message_name VARCHAR2(30);
324 cst_active VARCHAR2 (6) := 'ACTIVE';
325 cst_inactive VARCHAR2 (8) := 'INACTIVE';
326 v_s_course_status igs_ps_stat.s_course_status%TYPE;
327 CURSOR c_get_s_course_status
328 (cp_course_status igs_ps_stat.course_status%TYPE) IS
329 SELECT s_course_status
330 FROM igs_ps_stat
331 WHERE course_status = cp_course_status;
332
333 BEGIN
334 -- Validate mutating rows.
335 -- Validate IGS_PS_COURSE status and expiry date.
336 IF p_inserting OR
337 (p_updating AND
338 ((NVL(old_references.expiry_dt,
339 igs_ge_date.igsdate('1900/01/01')) <>
340 NVL(new_references.expiry_dt, igs_ge_date.igsdate('1900/01/01'))) OR
341 (old_references.course_status <> new_references.course_status))) THEN
342 IF igs_ps_val_crv.crsp_val_crv_exp_sts (
343 new_references.course_cd,
344 new_references.version_number,
345 new_references.expiry_dt,
346 new_references.course_status,
347 v_message_name) = FALSE THEN
348 fnd_message.set_name('IGS',v_message_name);
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352 END IF;
353 IF p_inserting OR
354 (p_updating AND
355 (old_references.course_status <> new_references.course_status)) THEN
356 OPEN c_get_s_course_status (new_references.course_status);
357 FETCH c_get_s_course_status INTO v_s_course_status;
358 CLOSE c_get_s_course_status;
359 -- Perform a quality check if updating to a system status of ACTIVE.
360 -- IGS_GE_NOTE: A IGS_PS_COURSE version can only be created with a status of PLANNED.
361 -- Hence, only need to perform the check if updating.
362 IF p_updating AND
363 (v_s_course_status = cst_active) THEN
364 IF igs_ps_val_crv.crsp_val_crv_quality(
365 new_references.course_cd,
366 new_references.version_number,
367 old_references.course_status,
368 v_message_name) = FALSE THEN
369 fnd_message.set_name('IGS',v_message_name);
370 igs_ge_msg_stack.add;
371 app_exception.raise_exception;
372 END IF;
373 END IF;
374 END IF;
375
376 END rowvalmutation;
377
378 PROCEDURE beforerowinsertupdatedelete1(
379 p_inserting IN BOOLEAN,
380 p_updating IN BOOLEAN,
381 p_deleting IN BOOLEAN
382 ) AS
383 /************************************************************************
384 Created By :
385 Date Created By :
386 Purpose :
387 Known limitations, enhancements or remarks:
388 Change History :
389 Who When What
390 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
391 as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.pkg.22 warning.
392 sbeerell 10-MAY-2000 Changed for new DLD version 2
393 ayedubat 25-MAY-2001 Changed for the DLD,PSP001-US.
394 (reverse chronological order - newest change first)
395 *************************************************************************/
396 v_message_name VARCHAR2(30);
397 v_return_type VARCHAR2(1);
398 v_start_dt igs_ps_ver_all.start_dt%TYPE;
399 v_review_dt igs_ps_ver_all.review_dt%TYPE;
400 v_expiry_dt igs_ps_ver_all.expiry_dt%TYPE;
401 v_end_dt igs_ps_ver_all.end_dt%TYPE;
402 v_course_status igs_ps_ver_all.course_status%TYPE;
403 v_title igs_ps_ver_all.title%TYPE;
404 v_short_title igs_ps_ver_all.short_title%TYPE;
405 v_abbreviation igs_ps_ver_all.abbreviation%TYPE;
406 v_supp_exam_permitted_ind igs_ps_ver_all.supp_exam_permitted_ind%TYPE;
407 v_generic_course_ind igs_ps_ver_all.generic_course_ind%TYPE;
408 v_graduate_students_ind igs_ps_ver_all.graduate_students_ind%TYPE;
409 v_count_intrmsn_in_time_ind igs_ps_ver_all.count_intrmsn_in_time_ind%TYPE;
410 v_intrmsn_allowed_ind igs_ps_ver_all.intrmsn_allowed_ind%TYPE;
411 v_course_type igs_ps_ver_all.course_type%TYPE;
412 v_responsible_org_unit_cd igs_ps_ver_all.responsible_org_unit_cd%TYPE;
413 v_responsible_ou_start_dt igs_ps_ver_all.responsible_ou_start_dt%TYPE;
414 v_govt_special_course_type igs_ps_ver_all.govt_special_course_type%TYPE;
415 v_qualification_recency igs_ps_ver_all.qualification_recency%TYPE;
416 v_external_adv_stnd_limit igs_ps_ver_all.external_adv_stnd_limit%TYPE;
417 v_internal_adv_stnd_limit igs_ps_ver_all.internal_adv_stnd_limit%TYPE;
418 v_contact_hours igs_ps_ver_all.contact_hours%TYPE;
419 v_credit_points_required igs_ps_ver_all.credit_points_required%TYPE;
420 v_govt_course_load igs_ps_ver_all.govt_course_load%TYPE;
421 v_std_annual_load igs_ps_ver_all.std_annual_load%TYPE;
422 v_course_total_eftsu igs_ps_ver_all.course_total_eftsu%TYPE;
423 v_max_intrmsn_duration igs_ps_ver_all.max_intrmsn_duration%TYPE;
424 v_num_of_units_before_intrmsn igs_ps_ver_all.num_of_units_before_intrmsn%TYPE;
425 v_min_sbmsn_percentage igs_ps_ver_all.min_sbmsn_percentage%TYPE;
426 v_min_cp_per_calendar igs_ps_ver_all.min_cp_per_calendar%TYPE;
427 v_approval_date igs_ps_ver_all.approval_date%TYPE;
428 v_external_approval_date igs_ps_ver_all.external_approval_date%TYPE;
429 v_federal_financial_aid igs_ps_ver_all.federal_financial_aid%TYPE;
430 v_institutional_financial_aid igs_ps_ver_all.institutional_financial_aid%TYPE;
431 v_max_cp_per_teaching_period igs_ps_ver_all.max_cp_per_teaching_period%TYPE;
432 v_residency_cp_required igs_ps_ver_all.residency_cp_required%TYPE;
433 v_state_financial_aid igs_ps_ver_all.state_financial_aid%TYPE;
434 l_rev_account_cd igs_ps_unit_ver_all.rev_account_cd%TYPE;
435 l_primary_program_rank igs_ps_ver_all.primary_program_rank%TYPE;
436 l_n_max_wlst_per_stud igs_ps_ver_all.max_wlst_per_stud%TYPE;
437 l_n_annual_instruction_time igs_ps_ver_all.annual_instruction_time%TYPE;
438
439 cst_active VARCHAR2 (6) := 'ACTIVE';
440 cst_inactive VARCHAR2 (8) := 'INACTIVE';
441 cst_error VARCHAR2 (1) := 'E';
442 v_s_course_status igs_ps_stat.s_course_status%TYPE;
443
444 CURSOR c_get_s_course_status IS
445 SELECT s_course_status
446 FROM igs_ps_stat
447 WHERE course_status = new_references.course_status;
448
449 CURSOR spvh_cur IS
450 SELECT ROWID
451 FROM igs_ps_ver_hist
452 WHERE course_cd = old_references.course_cd AND
453 version_number = old_references.version_number;
454
455 BEGIN
456 -- Validate the IGS_PS_COURSE version fields cannot be updated if the IGS_PS_COURSE
457 -- version has a system status of 'INACTIVE'. IGS_GE_EXCEPTIONS are : IGS_PS_STAT,
458 -- expiry_dt and end_dt.
459 IF p_updating THEN
460 OPEN c_get_s_course_status;
461 FETCH c_get_s_course_status
462 INTO v_s_course_status;
463 IF c_get_s_course_status%FOUND AND
464 (v_s_course_status = cst_inactive) THEN
465 IF (NVL(old_references.start_dt, igs_ge_date.igsdate('1900/01/01')) <>
466 NVL(new_references.start_dt, igs_ge_date.igsdate('1900/01/01'))) OR
467 (NVL(old_references.review_dt, igs_ge_date.igsdate('1900/01/01')) <>
468 NVL(new_references.review_dt, igs_ge_date.igsdate('1900/01/01'))) OR
469 (old_references.title <> new_references.title) OR
470 (old_references.short_title <> new_references.short_title) OR
471 (old_references.abbreviation <> new_references.abbreviation) OR
472 (old_references.supp_exam_permitted_ind <> new_references.supp_exam_permitted_ind) OR
473 (old_references.generic_course_ind <> new_references.generic_course_ind) OR
474 (old_references.graduate_students_ind <> new_references.graduate_students_ind) OR
475 (old_references.count_intrmsn_in_time_ind <> new_references.count_intrmsn_in_time_ind) OR
476 (old_references.intrmsn_allowed_ind <> new_references.intrmsn_allowed_ind) OR
477 (old_references.course_type <> new_references.course_type) OR
478 (old_references.responsible_org_unit_cd <> new_references.responsible_org_unit_cd) OR
479 (NVL(old_references.responsible_ou_start_dt, igs_ge_date.igsdate('1900/01/01')) <>
480 NVL(new_references.responsible_ou_start_dt, igs_ge_date.igsdate('1900/01/01'))) OR
481 (old_references.govt_special_course_type <>new_references.govt_special_course_type) OR
482 (NVL(old_references.qualification_recency,999999) <>
483 NVL(new_references.qualification_recency,999999)) OR
484 (NVL(old_references.external_adv_stnd_limit,999999) <>
485 NVL(new_references.external_adv_stnd_limit,999999)) OR
486 (NVL(old_references.internal_adv_stnd_limit,999999) <>
487 NVL(new_references.internal_adv_stnd_limit,999999)) OR
488 (NVL(old_references.contact_hours,999999) <> NVL(new_references.contact_hours,999999)) OR
489 (NVL(old_references.credit_points_required,999999) <>
490 NVL(new_references.credit_points_required,999999)) OR
491 (NVL(old_references.govt_course_load,999999) <>
492 NVL(new_references.govt_course_load,999999)) OR
493 (NVL(old_references.std_annual_load,999999) <> NVL(new_references.std_annual_load,999999)) OR
494 (NVL(old_references.course_total_eftsu,999999) <>
495 NVL(new_references.course_total_eftsu,999999)) OR
496 (NVL(old_references.max_intrmsn_duration,999999) <>
497 NVL(new_references.max_intrmsn_duration,999999)) OR
498 (NVL(old_references.num_of_units_before_intrmsn,999999) <>
499 NVL(new_references.num_of_units_before_intrmsn,999999)) OR
500 (NVL(old_references.rev_account_cd,'N') <>
501 NVL(new_references.rev_account_cd,'N')) OR
502 (NVL(old_references.primary_program_rank,99999) <>
503 NVL(new_references.primary_program_rank,99999)) OR
504 (NVL(old_references.max_wlst_per_stud,99999) <>
505 NVL(new_references.max_wlst_per_stud,99999)) OR
506 (NVL(old_references.annual_instruction_time,99999) <>
507 NVL(new_references.annual_instruction_time,99999))
508
509 THEN
510 CLOSE c_get_s_course_status;
511 fnd_message.set_name('IGS','IGS_PS_CHG_CANNOT_BEMADE_PRG');
512 igs_ge_msg_stack.add;
513 app_exception.raise_exception;
514 END IF;
515 END IF; -- s_course_status found.
516 CLOSE c_get_s_course_status;
517 END IF;
518 -- Validate the IGS_PS_COURSE status.
519 IF p_inserting OR
520 (p_updating AND (old_references.course_status <> new_references.course_status)) THEN
521 IF igs_ps_val_crv.crsp_val_crv_status (
522 new_references.course_status,
523 old_references.course_status,
524 v_message_name) = FALSE THEN
525 fnd_message.set_name('IGS',v_message_name);
526 igs_ge_msg_stack.add;
527 app_exception.raise_exception;
528 END IF;
529 -- Perform a quality check if p_updating to a system status of ACTIVE.
530 -- IGS_GE_NOTE: A IGS_PS_COURSE version can only be created with a status of PLANNED.
531 -- Hence, only need to perform the check if p_updating.
532 IF p_updating AND
533 (v_s_course_status = cst_active) THEN
534 -- Save the rowid, old expiry_dt and old IGS_PS_STAT,
535 -- and process in the "after statement" trigger
536 -- as function will cause a mutating table error if called here.
537 rowvalmutation(
538 p_inserting => p_inserting,
539 p_updating => p_updating,
540 p_deleting => p_deleting
541 );
542 END IF;
543 END IF;
544 -- Validate the IGS_PS_COURSE type.
545 IF p_inserting OR
546 (p_updating AND (old_references.course_type <> new_references.course_type)) THEN
547 IF igs_ps_val_crv.crsp_val_crv_type (
548 new_references.course_cd,
549 new_references.version_number,
550 new_references.course_type,
551 v_message_name) = FALSE THEN
552 fnd_message.set_name('IGS',v_message_name);
553 igs_ge_msg_stack.add;
554 app_exception.raise_exception;
555 END IF;
556 END IF;
557 -- Validate the GOVT special IGS_PS_COURSE type.
558 IF p_inserting OR
559 (p_updating AND
560 (old_references.govt_special_course_type<> new_references.govt_special_course_type)) THEN
561 IF igs_ps_val_crv.crsp_val_crv_gsct (
562 new_references.govt_special_course_type,
563 v_message_name) = FALSE THEN
564 fnd_message.set_name('IGS',v_message_name);
565 igs_ge_msg_stack.add;
566 app_exception.raise_exception;
567 END IF;
568 END IF;
569 -- Validate the version dates.
570 IF p_inserting OR
571 (p_updating AND (old_references.start_dt <> new_references.start_dt)) OR
572 (p_updating AND
573 (NVL(old_references.end_dt, igs_ge_date.igsdate('1900/01/01')) <>
574 NVL(new_references.end_dt,igs_ge_date.igsdate('1900/01/01')))) OR
575 (p_updating AND
576 (NVL(old_references.expiry_dt, igs_ge_date.igsdate('1900/01/01')) <>
577 NVL(new_references.expiry_dt,igs_ge_date.igsdate('1900/01/01')))) THEN
578 /* As part of bug# 1956374 changed the following call from igs_ps_val_crv.crsp_val_ver_dt*/
579 IF igs_ps_val_us.crsp_val_ver_dt (
580 new_references.start_dt,
581 new_references.end_dt,
582 new_references.expiry_dt,
583 v_message_name,FALSE) = FALSE THEN
584 fnd_message.set_name('IGS',v_message_name);
585 igs_ge_msg_stack.add;
586 app_exception.raise_exception;
587 END IF;
588 END IF;
589 IF p_updating AND new_references.end_dt IS NOT NULL THEN
590 IF igs_ps_val_crv.crsp_val_crv_end(new_references.course_cd,
591 new_references.version_number,
592 v_return_type,
593 v_message_name) = FALSE THEN
594 IF v_return_type = cst_error THEN
595 fnd_message.set_name('IGS',v_message_name);
596 igs_ge_msg_stack.add;
597 app_exception.raise_exception;
598 END IF;
599 END IF;
600 END IF;
601 -- Validate the responsible org IGS_PS_UNIT.
602 IF p_inserting OR
603 (p_updating AND
604 ((old_references.responsible_org_unit_cd <> new_references.responsible_org_unit_cd) OR
605 (NVL(old_references.responsible_ou_start_dt,igs_ge_date.igsdate('1900/01/01')) <>
606 NVL(new_references.responsible_ou_start_dt, igs_ge_date.igsdate('1900/01/01')))))THEN
607 IF igs_ps_val_crv.crsp_val_ou_sys_sts (
608 new_references.responsible_org_unit_cd,
609 new_references.responsible_ou_start_dt,
610 v_message_name) = FALSE THEN
611 fnd_message.set_name('IGS',v_message_name);
612 igs_ge_msg_stack.add;
613 app_exception.raise_exception;
614 END IF;
615 END IF;
616 -- Validate IGS_PS_COURSE status and end date combination.
617 IF p_inserting OR
618 (p_updating AND
619 ((NVL(old_references.end_dt, igs_ge_date.igsdate('1900/01/01')) <>
620 NVL(new_references.end_dt,igs_ge_date.igsdate('1900/01/01'))) OR
621 (old_references.course_status <> new_references.course_status))) THEN
622 IF igs_ps_val_crv.crsp_val_crv_end_sts(
623 new_references.end_dt,
624 new_references.course_status,
625 v_message_name) = FALSE THEN
626 fnd_message.set_name('IGS',v_message_name);
627 igs_ge_msg_stack.add;
628 app_exception.raise_exception;
629 END IF;
630 END IF;
631
632 -- Store IGS_PS_COURSE Version History
633 IF p_updating THEN
634 IF old_references.start_dt <> new_references.start_dt OR
635 old_references.course_status <> new_references.course_status OR
636 old_references.title <> new_references.title OR
637 old_references.short_title <> new_references.short_title OR
638 old_references.abbreviation <> new_references.abbreviation OR
639 old_references.supp_exam_permitted_ind <> new_references.supp_exam_permitted_ind OR
640 old_references.generic_course_ind <> new_references.generic_course_ind OR
641 old_references.graduate_students_ind <> new_references.graduate_students_ind OR
642 old_references.count_intrmsn_in_time_ind <> new_references.count_intrmsn_in_time_ind OR
643 old_references.intrmsn_allowed_ind <> new_references.intrmsn_allowed_ind OR
644 old_references.course_type <> new_references.course_type OR
645 old_references.responsible_org_unit_cd <> new_references.responsible_org_unit_cd OR
646 old_references.responsible_ou_start_dt <> new_references.responsible_ou_start_dt OR
647 old_references.govt_special_course_type <> new_references.govt_special_course_type OR
648 NVL(old_references.qualification_recency,999999) <>
649 NVL(new_references.qualification_recency,999999) OR
650 NVL(old_references.external_adv_stnd_limit,999999) <>
651 NVL(new_references.external_adv_stnd_limit,999999) OR
652 NVL(old_references.internal_adv_stnd_limit,999999) <>
653 NVL(new_references.internal_adv_stnd_limit,999999) OR
654 NVL(old_references.contact_hours,999999) <> NVL(new_references.contact_hours,999999) OR
655 NVL(old_references.credit_points_required,999999) <>
656 NVL(new_references.credit_points_required,999999) OR
657 NVL(old_references.govt_course_load,999999) <>
658 NVL(new_references.govt_course_load,999999) OR
659 NVL(old_references.std_annual_load,999999) <>
660 NVL(new_references.std_annual_load,999999) OR
661 NVL(old_references.course_total_eftsu,999999) <>
662 NVL(new_references.course_total_eftsu,999999) OR
663 NVL(old_references.max_intrmsn_duration,999999) <>
664 NVL(new_references.max_intrmsn_duration,999999) OR
665 NVL(old_references.num_of_units_before_intrmsn,999999) <>
666 NVL(new_references.num_of_units_before_intrmsn,999999) OR
667 NVL(old_references.min_sbmsn_percentage,999999) <>
668 NVL(new_references.min_sbmsn_percentage,999999) OR
669 NVL(old_references.review_dt, igs_ge_date.igsdate('1900/01/01')) <>
670 NVL(new_references.review_dt,igs_ge_date.igsdate('1900/01/01')) OR
671 NVL(old_references.expiry_dt, igs_ge_date.igsdate('1900/01/01')) <>
672 NVL(new_references.expiry_dt, igs_ge_date.igsdate('1900/01/01')) OR
673 NVL(old_references.end_dt, igs_ge_date.igsdate('1900/01/01')) <>
674 NVL(new_references.end_dt, igs_ge_date.igsdate('1900/01/01')) OR
675 (NVL(old_references.min_cp_per_calendar,999999) <>
676 NVL(new_references.min_cp_per_calendar,999999)) OR
677 (NVL(old_references.approval_date, igs_ge_date.igsdate('1900/01/01')) <>
678 NVL(new_references.approval_date,igs_ge_date.igsdate('1900/01/01'))) OR
679 (NVL(old_references.external_approval_date, igs_ge_date.igsdate('1900/01/01')) <>
680 NVL(new_references.external_approval_date, igs_ge_date.igsdate('1900/01/01'))) OR
681 (old_references.federal_financial_aid <> new_references.federal_financial_aid) OR
682 (old_references.institutional_financial_aid <> new_references.institutional_financial_aid) OR
683 (NVL(old_references.max_cp_per_teaching_period,999999) <>
684 NVL(new_references.max_cp_per_teaching_period,999999)) OR
685 (NVL(old_references.residency_cp_required,999999) <>
686 NVL(new_references.residency_cp_required,999999)) OR
687 (NVL(old_references.primary_program_rank,99999) <>
688 NVL(new_references.primary_program_rank,99999)) OR
689 (old_references.state_financial_aid <> new_references.state_financial_aid) OR
690 NVL(old_references.max_wlst_per_stud,999999) <>
691 NVL(new_references.max_wlst_per_stud,999999) OR
692 NVL(old_references.annual_instruction_time,999999) <>
693 NVL(new_references.annual_instruction_time,999999)
694 THEN
695 IF (NVL(old_references.review_dt, igs_ge_date.igsdate('1900/01/01')) <>
696 NVL(new_references.review_dt,igs_ge_date.igsdate('1900/01/01'))) THEN
697 v_review_dt := old_references.review_dt;
698 END IF;
699 IF (NVL(old_references.expiry_dt, igs_ge_date.igsdate('1900/01/01')) <>
700 NVL(new_references.expiry_dt, igs_ge_date.igsdate('1900/01/01'))) THEN
701 v_expiry_dt := old_references.expiry_dt;
702 END IF;
703 IF (NVL(old_references.end_dt, igs_ge_date.igsdate('1900/01/01')) <>
704 NVL(new_references.end_dt, igs_ge_date.igsdate('1900/01/01'))) THEN
705 v_end_dt := old_references.end_dt;
706 END IF;
707 -- Use decode to compare the new and old values, and if they have changed
708 -- put the old value in the variable to be passed to the history routine.
709 SELECT decode(old_references.start_dt,new_references.start_dt,
710 NULL,old_references.start_dt),
711 decode(old_references.course_status,new_references.course_status,
712 NULL,old_references.course_status),
713 decode(old_references.title,new_references.title,
714 NULL,old_references.title),
715 decode(old_references.short_title,new_references.short_title,
716 NULL,old_references.short_title),
717 decode(old_references.abbreviation,new_references.abbreviation,NULL,
718 old_references.abbreviation),
719 decode(old_references.supp_exam_permitted_ind,new_references.supp_exam_permitted_ind,
720 NULL,old_references.supp_exam_permitted_ind),
721 decode(old_references.generic_course_ind,new_references.generic_course_ind,
722 NULL,old_references.generic_course_ind),
723 decode(old_references.graduate_students_ind,new_references.graduate_students_ind,
724 NULL,old_references.graduate_students_ind),
725 decode(old_references.count_intrmsn_in_time_ind,new_references.count_intrmsn_in_time_ind,
726 NULL,old_references.count_intrmsn_in_time_ind),
727 decode(old_references.intrmsn_allowed_ind,new_references.intrmsn_allowed_ind,
728 NULL,old_references.intrmsn_allowed_ind),
729 decode(old_references.course_type,new_references.course_type,
730 NULL,old_references.course_type),
731 decode(old_references.responsible_org_unit_cd,new_references.responsible_org_unit_cd,
732 NULL,old_references.responsible_org_unit_cd),
733 decode(old_references.govt_special_course_type,new_references.govt_special_course_type,
734 NULL,old_references.govt_special_course_type),
735 decode(NVL(old_references.qualification_recency,999999),
736 NVL(new_references.qualification_recency,999999),
737 NULL,old_references.qualification_recency),
738 decode(NVL(old_references.external_adv_stnd_limit,999999),
739 NVL(new_references.external_adv_stnd_limit,999999),
740 NULL,old_references.external_adv_stnd_limit),
741 decode(NVL(old_references.internal_adv_stnd_limit,999999),
742 NVL(new_references.internal_adv_stnd_limit,999999),
743 NULL,old_references.internal_adv_stnd_limit),
744 decode(NVL(old_references.contact_hours,999999),NVL(new_references.contact_hours,999999),
745 NULL,old_references.contact_hours),
746 decode(NVL(old_references.credit_points_required,999999),
747 NVL(new_references.credit_points_required,999999),
748 NULL,old_references.credit_points_required),
749 decode(NVL(old_references.govt_course_load,999999),
750 NVL(new_references.govt_course_load,999999),
751 NULL,old_references.govt_course_load),
752 decode(NVL(old_references.std_annual_load,999999),NVL(new_references.std_annual_load,999999),
753 NULL,old_references.std_annual_load),
754 decode(NVL(old_references.course_total_eftsu,999999),
755 NVL(new_references.course_total_eftsu,999999),
756 NULL,old_references.course_total_eftsu),
757 decode(NVL(old_references.max_intrmsn_duration,999999),
758 NVL(new_references.max_intrmsn_duration,999999),
759 NULL,old_references.max_intrmsn_duration),
760 decode(NVL(old_references.num_of_units_before_intrmsn,999999),
761 NVL(new_references.num_of_units_before_intrmsn,999999),
762 NULL,old_references.num_of_units_before_intrmsn),
763 decode(NVL(old_references.min_sbmsn_percentage,999999),
764 NVL(new_references.min_sbmsn_percentage,999999),
765 NULL,old_references.min_sbmsn_percentage),
766 decode(NVL(old_references.min_cp_per_calendar,999999),
767 NVL(new_references.min_cp_per_calendar,999999),
768 NULL,old_references.min_cp_per_calendar),
769 decode(old_references.approval_date,new_references.approval_date,
770 NULL,old_references.approval_date),
771 decode(old_references.external_approval_date,new_references.external_approval_date,
772 NULL,old_references.external_approval_date),
773 decode(old_references.federal_financial_aid,new_references.federal_financial_aid,
774 NULL,old_references.federal_financial_aid),
775 decode(old_references.institutional_financial_aid,new_references.institutional_financial_aid,
776 NULL,old_references.institutional_financial_aid),
777 decode(NVL(old_references.max_cp_per_teaching_period,999999),
778 NVL(new_references.max_cp_per_teaching_period,999999),
779 NULL,old_references.max_cp_per_teaching_period),
780 decode(NVL(old_references.residency_cp_required,999999),
781 NVL(new_references.residency_cp_required,999999),
782 NULL,old_references.residency_cp_required),
783 decode(old_references.state_financial_aid,new_references.state_financial_aid,
784 NULL,old_references.state_financial_aid),
785 decode(NVL(old_references.primary_program_rank,99999),
786 NVL(new_references.primary_program_rank,99999),
787 NULL,old_references.primary_program_rank),
788 decode(NVL(old_references.max_wlst_per_stud,99999),
789 NVL(new_references.max_wlst_per_stud,99999),
790 NULL,old_references.max_wlst_per_stud),
791 decode(NVL(old_references.annual_instruction_time,99999),
792 NVL(new_references.annual_instruction_time,99999),
793 NULL,old_references.annual_instruction_time)
794
795 INTO v_start_dt,
796 v_course_status,
797 v_title,
798 v_short_title,
799 v_abbreviation,
800 v_supp_exam_permitted_ind,
801 v_generic_course_ind,
802 v_graduate_students_ind,
803 v_count_intrmsn_in_time_ind,
804 v_intrmsn_allowed_ind,
805 v_course_type,
806 v_responsible_org_unit_cd,
807 v_govt_special_course_type,
808 v_qualification_recency,
809 v_external_adv_stnd_limit,
810 v_internal_adv_stnd_limit,
811 v_contact_hours,
812 v_credit_points_required,
813 v_govt_course_load,
814 v_std_annual_load,
815 v_course_total_eftsu,
816 v_max_intrmsn_duration,
817 v_num_of_units_before_intrmsn,
818 v_min_sbmsn_percentage,
819 v_min_cp_per_calendar,
820 v_approval_date,
821 v_external_approval_date,
822 v_federal_financial_aid,
823 v_institutional_financial_aid,
824 v_max_cp_per_teaching_period,
825 v_residency_cp_required,
826 v_state_financial_aid,
827 l_primary_program_rank,
828 l_n_max_wlst_per_stud,
829 l_n_annual_instruction_time
830 FROM dual;
831
832 IF old_references.responsible_org_unit_cd <> new_references.responsible_org_unit_cd THEN
833 v_responsible_ou_start_dt := new_references.responsible_ou_start_dt;
834 END IF;
835 -- Create history record for update
836 igs_ps_gen_002.crsp_ins_cv_hist(
837 old_references.course_cd,
838 old_references.version_number,
839 old_references.last_update_date,
840 new_references.last_update_date,
841 old_references.last_updated_by,
842 v_start_dt,
843 v_review_dt,
844 v_expiry_dt,
845 v_end_dt,
846 v_course_status,
847 v_title,
848 v_short_title,
849 v_abbreviation,
850 v_supp_exam_permitted_ind,
851 v_generic_course_ind,
852 v_graduate_students_ind,
853 v_count_intrmsn_in_time_ind,
854 v_intrmsn_allowed_ind,
855 v_course_type,
856 v_responsible_org_unit_cd,
857 v_responsible_ou_start_dt,
858 v_govt_special_course_type,
859 v_qualification_recency,
860 v_external_adv_stnd_limit,
861 v_internal_adv_stnd_limit,
862 v_contact_hours,
863 v_credit_points_required,
864 v_govt_course_load,
865 v_std_annual_load,
866 v_course_total_eftsu,
867 v_max_intrmsn_duration,
868 v_num_of_units_before_intrmsn,
869 v_min_sbmsn_percentage,
870 v_min_cp_per_calendar,
871 v_approval_date,
872 v_external_approval_date,
873 v_federal_financial_aid,
874 v_institutional_financial_aid,
875 v_max_cp_per_teaching_period,
876 v_residency_cp_required,
877 v_state_financial_aid,
878 l_primary_program_rank,
879 l_n_max_wlst_per_stud,
880 l_n_annual_instruction_time);
881
882 END IF;
883 END IF;
884 IF p_deleting THEN
885
886 BEGIN
887 FOR spvh_rec IN spvh_cur
888 LOOP
889 igs_ps_ver_hist_pkg.delete_row(x_rowid => spvh_rec.rowid);
890 END LOOP;
891 END;
892
893 END IF;
894
895 END beforerowinsertupdatedelete1;
896
897 PROCEDURE afterrowinsertupdate2(
898 p_inserting IN BOOLEAN,
899 p_updating IN BOOLEAN,
900 p_deleting IN BOOLEAN
901 ) AS
902 /************************************************************************
903 Created By :
904 Date Created By :
905 Purpose :
906 Known limitations, enhancements or remarks:
907 Change History :
908 Who When What
909 sarakshi 11-Aug-2005 Bug#4112225, replaced user defined course status with system defined course status
910 vvutukur 19-Oct-2002 Enh#2608227.Removed DEFAULT keyword to avoid gscc File.Pkg.22 warning.
911 sbeerell 10-MAY-2000 Changed for new DLD version 2
912 nalkumar 16-Dec-2003 Modified the code to fix Bug# 3310718
913 (reverse chronological order - newest change first)
914 *************************************************************************/
915 CURSOR cur_igs_ps_unit_lvl IS
916 SELECT ulvl.*
917 FROM igs_ps_unit_lvl_all ulvl
918 WHERE course_cd = new_references.course_cd;
919 l_rowid VARCHAR2(25);
920
921 CURSOR cur_get_status(cp_course_status igs_ps_stat.course_status%TYPE) IS
922 SELECT s_course_status
923 FROM igs_ps_stat
924 WHERE course_status=cp_course_status;
925
926 l_old_course_status igs_ps_stat.s_course_status%TYPE;
927 l_new_course_status igs_ps_stat.s_course_status%TYPE;
928
929 BEGIN
930 --
931 -- Copy the New Program Version details to the Unit Level when the New Program Version is created.
932 -- Start of new code added to fix Bug# 3310718.
933 --
934 IF p_updating THEN
935 OPEN cur_get_status(old_references.course_status);
936 FETCH cur_get_status INTO l_old_course_status;
937 CLOSE cur_get_status;
938
939 OPEN cur_get_status(new_references.course_status);
940 FETCH cur_get_status INTO l_new_course_status;
941 CLOSE cur_get_status;
942
943 IF l_old_course_status = 'PLANNED' AND l_new_course_status = 'ACTIVE' THEN
944 FOR rec_igs_ps_unit_lvl IN cur_igs_ps_unit_lvl LOOP
945 l_rowid := NULL;
946 igs_ps_unit_lvl_pkg.insert_row(
947 X_ROWID => l_rowid ,
948 X_UNIT_CD => rec_igs_ps_unit_lvl.unit_cd ,
949 X_VERSION_NUMBER => rec_igs_ps_unit_lvl.version_number,
950 X_COURSE_TYPE => rec_igs_ps_unit_lvl.course_type ,
951 X_UNIT_LEVEL => rec_igs_ps_unit_lvl.unit_level ,
952 X_WAM_WEIGHTING => rec_igs_ps_unit_lvl.wam_weighting ,
953 X_MODE => 'R' ,
954 X_ORG_ID => rec_igs_ps_unit_lvl.org_id ,
955 X_COURSE_CD => rec_igs_ps_unit_lvl.course_cd ,
956 X_COURSE_VERSION_NUMBER => new_references.version_number
957 );
958 END LOOP;
959 END IF;
960 END IF;
961 --
962 -- End of new code added to fix Bug# 3310718.
963 --
964
965 -- Validate IGS_PS_COURSE status and expiry date.
966 IF p_inserting OR
967 (p_updating AND
968 ((NVL(old_references.expiry_dt, igs_ge_date.igsdate('1900/01/01')) <>
969 NVL(new_references.expiry_dt, igs_ge_date.igsdate('1900/01/01'))) OR
970 (old_references.course_status <> new_references.course_status))) THEN
971 -- Cannot call crsp_val_crv_exp_sts because trigger will be mutating.
972 -- Save the rowid of the current row.
973 rowvalmutation(
974 p_inserting => p_inserting,
975 p_updating => p_updating,
976 p_deleting => p_deleting
977 );
978 END IF;
979
980
981 END afterrowinsertupdate2;
982
983 PROCEDURE check_constraints (
984 column_name IN VARCHAR2 ,
985 column_value IN VARCHAR2
986 ) IS
987 /************************************************************************
988 Created By :
989 Date Created By :
990 Purpose :
991 Known limitations, enhancements or remarks:
992 Change History :
993 Who When What
994 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
995 as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22 warning.
996 sbeerell 10-MAY-2000 Changed for new DLD version 2
997 smvk 20-Dec-2002 Removed the responsible_org_unit_cd should be in upper case checking. Bug # 2487149
998 (reverse chronological order - newest change first)
999 *************************************************************************/
1000 BEGIN
1001 IF column_name IS NULL THEN
1002 NULL;
1003 ELSIF UPPER(column_name) = 'MAX_INTRMSN_DURATION' THEN
1004 new_references.max_intrmsn_duration :=igs_ge_number.to_num(column_value);
1005 ELSIF UPPER(column_name) = 'COURSE_TOTAL_EFTSU' THEN
1006 new_references.course_total_eftsu := igs_ge_number.to_num(column_value);
1007 ELSIF UPPER(column_name) = 'ABBREVIATION' THEN
1008 new_references.abbreviation := column_value;
1009 ELSIF UPPER(column_name) = 'COUNT_INTRMSN_IN_TIME_IND' THEN
1010 new_references.count_intrmsn_in_time_ind := column_value;
1011 ELSIF UPPER(column_name) = 'COURSE_CD' THEN
1012 new_references.course_cd := column_value;
1013 ELSIF UPPER(column_name) = 'COURSE_STATUS' THEN
1014 new_references.course_status := column_value;
1015 ELSIF UPPER(column_name) = 'COURSE_TYPE' THEN
1016 new_references.course_type := column_value;
1017 ELSIF UPPER(column_name) = 'GENERIC_COURSE_IND' THEN
1018 new_references.generic_course_ind := column_value;
1019 ELSIF UPPER(column_name) = 'GOVT_SPECIAL_COURSE_TYPE' THEN
1020 new_references.govt_special_course_type := column_value;
1021 ELSIF UPPER(column_name) = 'GRADUATE_STUDENTS_IND' THEN
1022 new_references.graduate_students_ind:= column_value;
1023 ELSIF UPPER(column_name) = 'INTRMSN_ALLOWED_IND' THEN
1024 new_references.intrmsn_allowed_ind := column_value;
1025 ELSIF UPPER(column_name) = 'SUPP_EXAM_PERMITTED_IND' THEN
1026 new_references.supp_exam_permitted_ind := column_value;
1027 ELSIF UPPER(column_name) = 'STD_ANNUAL_LOAD' THEN
1028 new_references.std_annual_load := igs_ge_number.to_num(column_value);
1029 ELSIF UPPER(column_name) = 'GOVT_COURSE_LOAD' THEN
1030 new_references.govt_course_load := igs_ge_number.to_num(column_value);
1031 ELSIF UPPER(column_name) = 'CREDIT_POINTS_REQUIRED' THEN
1032 new_references.credit_points_required := igs_ge_number.to_num(column_value);
1033 ELSIF UPPER(column_name) = 'CONTACT_HOURS' THEN
1034 new_references.contact_hours := igs_ge_number.to_num(column_value);
1035 ELSIF UPPER(column_name) = 'INTERNAL_ADV_STND_LIMIT' THEN
1036 new_references.internal_adv_stnd_limit := igs_ge_number.to_num(column_value);
1037 ELSIF UPPER(column_name) = 'EXTERNAL_ADV_STND_LIMIT' THEN
1038 new_references.external_adv_stnd_limit := igs_ge_number.to_num(column_value);
1039 ELSIF UPPER(column_name) = 'QUALIFICATION_RECENCY' THEN
1040 new_references.qualification_recency := igs_ge_number.to_num(column_value);
1041 ELSIF UPPER(column_name) = 'VERSION_NUMBER' THEN
1042 new_references.version_number := igs_ge_number.to_num(column_value);
1043 ELSIF UPPER(column_name) = 'MIN_SBMSN_PERCENTAGE' THEN
1044 new_references.min_sbmsn_percentage := igs_ge_number.to_num(column_value);
1045 ELSIF UPPER(column_name) = 'NUM_OF_UNITS_BEFORE_INTRMSN' THEN
1046 new_references.num_of_units_before_intrmsn := igs_ge_number.to_num(column_value);
1047 ELSIF UPPER(column_name) = 'STATE_FINANCIAL_AID' THEN
1048 new_references.state_financial_aid := column_value;
1049 ELSIF UPPER(column_name) = 'FEDERAL_FINANCIAL_AID' THEN
1050 new_references.federal_financial_aid := column_value;
1051 ELSIF UPPER(column_name) = 'INSTITUTIONAL_FINANCIAL_AID' THEN
1052 new_references.institutional_financial_aid := column_value;
1053 ELSIF UPPER(column_name) = 'MAX_WLST_PER_STUD' THEN
1054 new_references.max_wlst_per_stud := column_value;
1055 ELSIF UPPER(column_name) = 'ANNUAL_INSTRUCTION_TIME' THEN
1056 new_references.annual_instruction_time := column_value;
1057 END IF;
1058
1059 IF UPPER(column_name)= 'ABBREVIATION' OR
1060 column_name IS NULL THEN
1061 IF new_references.abbreviation<> UPPER(new_references.abbreviation)
1062 THEN
1063 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1064 igs_ge_msg_stack.add;
1065 app_exception.raise_exception;
1066 END IF;
1067 END IF;
1068
1069 IF UPPER(column_name)= 'COURSE_CD' OR
1070 column_name IS NULL THEN
1071 IF new_references.course_cd <> UPPER(new_references.course_cd )
1072 THEN
1073 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1074 igs_ge_msg_stack.add;
1075 app_exception.raise_exception;
1076 END IF;
1077 END IF;
1078
1079 IF UPPER(column_name)= 'COURSE_STATUS' OR
1080 column_name IS NULL THEN
1081 IF new_references.course_status <> UPPER(new_references.course_status)
1082 THEN
1083 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1084 igs_ge_msg_stack.add;
1085 app_exception.raise_exception;
1086 END IF;
1087 END IF;
1088 IF UPPER(column_name)= 'COURSE_TYPE' OR
1089 column_name IS NULL THEN
1090 IF new_references.course_type <> UPPER(new_references.course_type)
1091 THEN
1092 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1093 igs_ge_msg_stack.add;
1094 app_exception.raise_exception;
1095 END IF;
1096 END IF;
1097
1098 IF UPPER(column_name)= 'GOVT_SPECIAL_COURSE_TYPE' OR
1099 column_name IS NULL THEN
1100 IF new_references.govt_special_course_type <> UPPER(new_references.govt_special_course_type )
1101 THEN
1102 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1103 igs_ge_msg_stack.add;
1104 app_exception.raise_exception;
1105 END IF;
1106 END IF;
1107
1108 IF UPPER(column_name)= 'MAX_INTRMSN_DURATION' OR
1109 column_name IS NULL THEN
1110 IF new_references.max_intrmsn_duration <0 OR
1111 new_references.max_intrmsn_duration > 999
1112 THEN
1113 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1114 igs_ge_msg_stack.add;
1115 app_exception.raise_exception;
1116 END IF;
1117 END IF;
1118 IF UPPER(column_name)= 'COURSE_TOTAL_EFTSU' OR
1119 column_name IS NULL THEN
1120 IF new_references.course_total_eftsu < 0 OR
1121 new_references.course_total_eftsu > 99.999
1122 THEN
1123 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1124 igs_ge_msg_stack.add;
1125 app_exception.raise_exception;
1126 END IF;
1127 END IF;
1128 IF UPPER(column_name)= 'STD_ANNUAL_LOAD' OR
1129 column_name IS NULL THEN
1130 IF new_references.std_annual_load < 0 OR
1131 new_references.std_annual_load > 9999.999
1132 THEN
1133 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1134 igs_ge_msg_stack.add;
1135 app_exception.raise_exception;
1136 END IF;
1137 END IF;
1138 IF UPPER(column_name)= 'GOVT_COURSE_LOAD' OR
1139 column_name IS NULL THEN
1140 IF new_references.govt_course_load < 0 OR
1141 new_references.govt_course_load > 99
1142 THEN
1143 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1144 igs_ge_msg_stack.add;
1145 app_exception.raise_exception;
1146 END IF;
1147 END IF;
1148 IF UPPER(column_name)= 'CREDIT_POINTS_REQUIRED' OR
1149 column_name IS NULL THEN
1150 IF new_references.credit_points_required < 0 OR
1151 new_references.credit_points_required > 999.999
1152 THEN
1153 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1154 igs_ge_msg_stack.add;
1155 app_exception.raise_exception;
1156 END IF;
1157 END IF;
1158 IF UPPER(column_name)= 'CONTACT_HOURS' OR
1159 column_name IS NULL THEN
1160 IF new_references.contact_hours < 0 OR
1161 new_references.contact_hours > 9999.999
1162 THEN
1163 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1164 igs_ge_msg_stack.add;
1165 app_exception.raise_exception;
1166 END IF;
1167 END IF;
1168 IF UPPER(column_name)= 'INTERNAL_ADV_STND_LIMIT' OR
1169 column_name IS NULL THEN
1170 IF new_references.internal_adv_stnd_limit < 0 OR
1171 new_references.internal_adv_stnd_limit > 9999.999
1172 THEN
1173 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1174 igs_ge_msg_stack.add;
1175 app_exception.raise_exception;
1176 END IF;
1177 END IF;
1178 IF UPPER(column_name)= 'EXTERNAL_ADV_STND_LIMIT' OR
1179 column_name IS NULL THEN
1180 IF new_references.external_adv_stnd_limit < 0 OR
1181 new_references.external_adv_stnd_limit > 9999.999
1182 THEN
1183 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1184 igs_ge_msg_stack.add;
1185 app_exception.raise_exception;
1186 END IF;
1187 END IF;
1188 IF UPPER(column_name)= 'QUALIFICATION_RECENCY' OR
1189 column_name IS NULL THEN
1190 IF new_references.qualification_recency < 0 OR
1191 new_references.qualification_recency > 99
1192 THEN
1193 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1194 igs_ge_msg_stack.add;
1195 app_exception.raise_exception;
1196 END IF;
1197 END IF;
1198 IF UPPER(column_name)= 'INTRMSN_ALLOWED_IND' OR
1199 column_name IS NULL THEN
1200 IF new_references.intrmsn_allowed_ind NOT IN ( 'Y' , 'N' )
1201 THEN
1202 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1203 igs_ge_msg_stack.add;
1204 app_exception.raise_exception;
1205 END IF;
1206 END IF;
1207 IF UPPER(column_name)= 'COUNT_INTRMSN_IN_TIME_IND' OR
1208 column_name IS NULL THEN
1209 IF new_references.count_intrmsn_in_time_ind NOT IN ( 'Y' , 'N' )
1210 THEN
1211 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1212 igs_ge_msg_stack.add;
1213 app_exception.raise_exception;
1214 END IF;
1215 END IF;
1216 IF UPPER(column_name)= 'GRADUATE_STUDENTS_IND' OR
1217 column_name IS NULL THEN
1218 IF new_references.graduate_students_ind NOT IN ( 'Y' , 'N' )
1219 THEN
1220 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1221 igs_ge_msg_stack.add;
1222 app_exception.raise_exception;
1223 END IF;
1224 END IF;
1225 IF UPPER(column_name)= 'GENERIC_COURSE_IND' OR
1226 column_name IS NULL THEN
1227 IF new_references.generic_course_ind NOT IN ( 'Y' , 'N' )
1228 THEN
1229 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1230 igs_ge_msg_stack.add;
1231 app_exception.raise_exception;
1232 END IF;
1233 END IF;
1234 IF UPPER(column_name)= 'SUPP_EXAM_PERMITTED_IND' OR
1235 column_name IS NULL THEN
1236 IF new_references.supp_exam_permitted_ind NOT IN ( 'Y' , 'N' )
1237 THEN
1238 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1239 igs_ge_msg_stack.add;
1240 app_exception.raise_exception;
1241 END IF;
1242 END IF;
1243 IF UPPER(column_name)= 'VERSION_NUMBER' OR
1244 column_name IS NULL THEN
1245 IF new_references.version_number < 1 OR
1246 new_references.version_number > 999
1247 THEN
1248 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1249 igs_ge_msg_stack.add;
1250 app_exception.raise_exception;
1251 END IF;
1252 END IF;
1253 IF UPPER(column_name)= 'MIN_SBMSN_PERCENTAGE' OR
1254 column_name IS NULL THEN
1255 IF new_references.min_sbmsn_percentage < 0 OR
1256 new_references.min_sbmsn_percentage > 100.00
1257 THEN
1258 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1259 igs_ge_msg_stack.add;
1260 app_exception.raise_exception;
1261 END IF;
1262 END IF;
1263 IF UPPER(column_name)= 'NUM_OF_UNITS_BEFORE_INTRMSN' OR
1264 column_name IS NULL THEN
1265 IF new_references.num_of_units_before_intrmsn < 0 OR
1266 new_references.num_of_units_before_intrmsn > 999
1267 THEN
1268 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1269 igs_ge_msg_stack.add;
1270 app_exception.raise_exception;
1271 END IF;
1272 END IF;
1273
1274 -- The following code checks for check constraints on the Columns.
1275 IF UPPER(column_name) = 'STATE_FINANCIAL_AID' OR
1276 column_name IS NULL THEN
1277 IF NOT (new_references.state_financial_aid IN ('Y', 'N')) THEN
1278 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1279 igs_ge_msg_stack.add;
1280 app_exception.raise_exception;
1281 END IF;
1282 END IF;
1283
1284 -- The following code checks for check constraints on the Columns.
1285 IF UPPER(column_name) = 'FEDERAL_FINANCIAL_AID' OR
1286 column_name IS NULL THEN
1287 IF NOT (new_references.federal_financial_aid IN ('Y', 'N')) THEN
1288 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1289 igs_ge_msg_stack.add;
1290 app_exception.raise_exception;
1291 END IF;
1292 END IF;
1293
1294 -- The following code checks for check constraints on the Columns.
1295 IF UPPER(column_name) = 'INSTITUTIONAL_FINANCIAL_AID' OR
1296 column_name IS NULL THEN
1297 IF NOT (new_references.institutional_financial_aid IN ('Y', 'N')) THEN
1298 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1299 igs_ge_msg_stack.add;
1300 app_exception.raise_exception;
1301 END IF;
1302 END IF;
1303
1304 IF UPPER(column_name)= 'MAX_WLST_PER_STUD' OR column_name IS NULL THEN
1305 IF new_references.max_wlst_per_stud < 0 OR new_references.max_wlst_per_stud > 9999 THEN
1306 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1307 igs_ge_msg_stack.add;
1308 app_exception.raise_exception;
1309 END IF;
1310 END IF;
1311
1312 IF UPPER(column_name)= 'ANNUAL_INSTRUCTION_TIME' OR column_name IS NULL THEN
1313 IF new_references.annual_instruction_time < 1 OR new_references.annual_instruction_time > 99.99 THEN
1314 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1315 igs_ge_msg_stack.add;
1316 app_exception.raise_exception;
1317 END IF;
1318 END IF;
1319
1320 END check_constraints;
1321
1322 PROCEDURE check_parent_existance AS
1323 /************************************************************************
1324 Created By :
1325 Date Created By :
1326 Purpose :
1327 Known limitations, enhancements or remarks:
1328 Change History :
1329 Who When What
1330 sbeerell 10-MAY-2000 Changed for new DLD version 2
1331 (reverse chronological order - newest change first)
1332 *************************************************************************/
1333 BEGIN
1334
1335 IF ((old_references.rev_account_cd = new_references.rev_account_cd) OR
1336 (new_references.rev_account_cd IS NULL)) THEN
1337 NULL;
1338 ELSE
1339 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
1340 new_references.rev_account_cd
1341 ) THEN
1342 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1343 IGS_GE_MSG_STACK.ADD;
1344 App_Exception.Raise_Exception;
1345 END IF;
1346 END IF;
1347
1348 IF (((old_references.course_cd = new_references.course_cd)) OR
1349 ((new_references.course_cd IS NULL))) THEN
1350 NULL;
1351 ELSE
1352 IF NOT igs_ps_course_pkg.get_pk_for_validation (
1353 new_references.course_cd
1354 )THEN
1355 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1356 igs_ge_msg_stack.add;
1357 app_exception.raise_exception;
1358 END IF;
1359 END IF;
1360 IF (((old_references.course_type = new_references.course_type)) OR
1361 ((new_references.course_type IS NULL))) THEN
1362 NULL;
1363 ELSE
1364 IF NOT igs_ps_type_pkg.get_pk_for_validation (
1365 new_references.course_type
1366 )THEN
1367 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1368 igs_ge_msg_stack.add;
1369 app_exception.raise_exception;
1370 END IF;
1371 END IF;
1372 IF (((old_references.govt_special_course_type = new_references.govt_special_course_type)) OR
1373 ((new_references.govt_special_course_type IS NULL))) THEN
1374 NULL;
1375 ELSE
1376 IF NOT igs_ps_govt_spl_type_pkg.get_pk_for_validation (
1377 new_references.govt_special_course_type
1378 )THEN
1379 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1380 igs_ge_msg_stack.add;
1381 app_exception.raise_exception;
1382 END IF;
1383
1384 END IF;
1385
1386 IF (((old_references.responsible_org_unit_cd = new_references.responsible_org_unit_cd) AND
1387 (old_references.responsible_ou_start_dt = new_references.responsible_ou_start_dt)) OR
1388 ((new_references.responsible_org_unit_cd IS NULL) OR
1389 (new_references.responsible_ou_start_dt IS NULL))) THEN
1390 NULL;
1391 ELSE
1392 IF NOT igs_or_unit_pkg.get_pk_for_validation (
1393 new_references.responsible_org_unit_cd,
1394 new_references.responsible_ou_start_dt
1395 )THEN
1396 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1397 igs_ge_msg_stack.add;
1398 app_exception.raise_exception;
1399 END IF;
1400
1401 END IF;
1402
1403 IF (((old_references.course_status = new_references.course_status)) OR
1404 ((new_references.course_status IS NULL))) THEN
1405 NULL;
1406 ELSE
1407 IF NOT igs_ps_stat_pkg.get_pk_for_validation (
1408 new_references.course_status
1409 )THEN
1410 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1411 igs_ge_msg_stack.add;
1412 app_exception.raise_exception;
1413 END IF;
1414
1415 END IF;
1416
1417 END check_parent_existance;
1418
1419
1420 PROCEDURE check_child_existance AS
1421 /************************************************************************
1422 Created By :
1423 Date Created By :
1424 Purpose :
1425 Known limitations, enhancements or remarks:
1426 Change History :
1427 Who When What
1428 sarakshi 02-Apr-2004 Bug#3345205, added child existance for IGS_EN_PSV_TERM_IT
1429 ugummall 27-NOV-2003 Bug 3252832. FA 131 - COD Updates.
1430 Added igf_gr_pell_setup_pkg.get_fk_igs_ps_ver for child existance.
1431 ijeddy 05-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment FD.
1432 vvutukur 09-Jun-2003 Enh#2831572.Financial Accounting Build.Added call to igs_fi_ftci_accts_pkg.get_fk_igs_ps_ver.
1433 rghosh 28-oct-2002 Added the get fk call to the table igs_ad_deplvl_prg for bug#2602077
1434 pmarada 15-feb-2002 Added IGS_HE_ST_PROG_ALL_PKG.GET_FK_IGS_PS_VER_ALL for hesa requirment.
1435 smadathi 01-Feb-2002 Added igf_sp_prg_pkg.get_fk_igs_ps_ver and
1436 igf_sp_std_prg_pkg.get_fk_igs_ps_ver calls.
1437 Bug 2154941
1438 sbeerell 10-MAY-2000 Changed for new DLD version 2
1439 (reverse chronological order - newest change first)
1440 *************************************************************************/
1441
1442 CURSOR c_hesa IS
1443 SELECT 1 FROM USER_OBJECTS
1444 WHERE OBJECT_NAME = 'IGS_HE_ST_PROG_ALL_PKG'
1445 AND object_type = 'PACKAGE BODY';
1446
1447 l_hesa VARCHAR2(1);
1448
1449 BEGIN
1450
1451 IGS_PS_UNIT_LVL_PKG.get_fk_igs_ps_ver(
1452 old_references.course_cd,
1453 old_references.version_number
1454 );
1455
1456 igs_pr_ul_mark_cnfg_pkg.get_fk_igs_ps_ver(
1457 old_references.course_cd,
1458 old_references.version_number
1459 );
1460
1461 igs_ad_ps_appl_inst_pkg.get_fk_igs_ps_ver (
1462 old_references.course_cd,
1463 old_references.version_number
1464 );
1465
1466 igs_av_adv_standing_pkg.get_fk_igs_ps_ver (
1467 old_references.course_cd,
1468 old_references.version_number
1469 );
1470
1471 igs_pe_alternatv_ext_pkg.get_fk_igs_ps_ver (
1472 old_references.course_cd,
1473 old_references.version_number
1474 );
1475
1476 igs_co_itm_pkg.get_fk_igs_ps_ver (
1477 old_references.course_cd,
1478 old_references.version_number
1479 );
1480
1481 igs_ps_anl_load_pkg.get_fk_igs_ps_ver (
1482 old_references.course_cd,
1483 old_references.version_number
1484 );
1485
1486 igs_ps_award_pkg.get_fk_igs_ps_ver (
1487 old_references.course_cd,
1488 old_references.version_number
1489 );
1490
1491 igs_ps_categorise_pkg.get_fk_igs_ps_ver (
1492 old_references.course_cd,
1493 old_references.version_number
1494 );
1495
1496 igs_ps_field_study_pkg.get_fk_igs_ps_ver (
1497 old_references.course_cd,
1498 old_references.version_number
1499 );
1500
1501 igs_ps_fld_std_hist_pkg.get_fk_igs_ps_ver (
1502 old_references.course_cd,
1503 old_references.version_number
1504 );
1505
1506 igs_ps_grp_mbr_pkg.get_fk_igs_ps_ver (
1507 old_references.course_cd,
1508 old_references.version_number
1509 );
1510
1511 igs_ps_ofr_pkg.get_fk_igs_ps_ver (
1512 old_references.course_cd,
1513 old_references.version_number
1514 );
1515
1516 igs_ps_own_pkg.get_fk_igs_ps_ver (
1517 old_references.course_cd,
1518 old_references.version_number
1519 );
1520
1521 igs_ps_own_hist_pkg.get_fk_igs_ps_ver (
1522 old_references.course_cd,
1523 old_references.version_number
1524 );
1525 igs_ps_occup_titles_pkg.get_fk_igs_ps_ver (
1526 old_references.course_cd
1527 );
1528
1529 igs_ps_ref_cd_pkg.get_fk_igs_ps_ver (
1530 old_references.course_cd,
1531 old_references.version_number
1532 );
1533
1534 igs_ps_ref_cd_hist_pkg.get_fk_igs_ps_ver (
1535 old_references.course_cd,
1536 old_references.version_number
1537 );
1538
1539 igs_ps_stage_pkg.get_fk_igs_ps_ver (
1540 old_references.course_cd,
1541 old_references.version_number
1542 );
1543
1544 igs_ps_ver_note_pkg.get_fk_igs_ps_ver (
1545 old_references.course_cd,
1546 old_references.version_number
1547 );
1548
1549 igs_ps_ver_ru_pkg.get_fk_igs_ps_ver (
1550 old_references.course_cd,
1551 old_references.version_number
1552 );
1553
1554 igs_re_dflt_ms_set_pkg.get_fk_igs_ps_ver (
1555 old_references.course_cd,
1556 old_references.version_number
1557 );
1558
1559
1560 igs_fi_fnd_src_rstn_pkg.get_fk_igs_ps_ver (
1561 old_references.course_cd,
1562 old_references.version_number
1563 );
1564
1565 igs_fi_fd_src_rstn_h_pkg.get_fk_igs_ps_ver (
1566 old_references.course_cd,
1567 old_references.version_number
1568 );
1569
1570 igs_co_ou_co_ref_pkg.get_fk_igs_ps_ver (
1571 old_references.course_cd,
1572 old_references.version_number
1573 );
1574
1575 igs_pr_ru_appl_pkg.get_fk_igs_ps_ver (
1576 old_references.course_cd,
1577 old_references.version_number
1578 );
1579
1580 igs_ad_sbm_ps_fntrgt_pkg.get_fk_igs_ps_ver (
1581 old_references.course_cd,
1582 old_references.version_number
1583 );
1584
1585 igs_en_stdnt_ps_att_pkg.get_fk_igs_ps_ver (
1586 old_references.course_cd,
1587 old_references.version_number
1588 );
1589
1590 igs_ps_stdnt_apv_alt_pkg.get_fk_igs_ps_ver (
1591 old_references.course_cd,
1592 old_references.version_number
1593 );
1594
1595
1596 igs_pr_s_crv_prg_con_pkg.get_fk_igs_ps_ver (
1597 old_references.course_cd,
1598 old_references.version_number
1599 );
1600
1601 igs_ps_rsv_orgun_prf_pkg.get_fk_igs_ps_ver_all(
1602 old_references.course_cd,
1603 old_references.version_number);
1604 igs_ps_rsv_uop_prf_pkg.get_fk_igs_ps_ver_all(
1605 old_references.course_cd,
1606 old_references.version_number);
1607 igs_ps_rsv_usec_prf_pkg.get_fk_igs_ps_ver_all(
1608 old_references.course_cd,
1609 old_references.version_number);
1610 -- msrinivi : 1882122 Commented due to leap frog
1611 -- msrinivi : 1882122 Uncommented the following lines after leapfrog
1612 -- Added by Nishikant for enhancement bug#1851586
1613 igs_fi_fee_as_rate_pkg.get_fk_igs_ps_ver(
1614 old_references.course_cd,
1615 old_references.version_number);
1616
1617 igs_ps_accts_pkg.get_fk_igs_ps_ver(
1618 old_references.course_cd,
1619 old_references.version_number);
1620
1621 igf_sp_prg_pkg.get_fk_igs_ps_ver (
1622 old_references.course_cd,
1623 old_references.version_number
1624 );
1625
1626 igf_sp_std_prg_pkg.get_fk_igs_ps_ver(
1627 old_references.course_cd,
1628 old_references.version_number
1629 );
1630
1631 igs_en_config_enr_cp_pkg.get_fk_igs_ps_ver(
1632 old_references.course_cd,
1633 old_references.version_number
1634 );
1635 -- added by rghosh for bug# 2602077
1636 igs_ad_deplvl_prg_pkg.get_fk_igs_ps_ver(
1637 old_references.course_cd,
1638 old_references.version_number
1639 );
1640
1641 -- added by ugummall for build FA 131 COD Updates. Bug 3252832.
1642 igf_gr_pell_setup_pkg.get_fk_igs_ps_ver(
1643 old_references.course_cd,
1644 old_references.version_number
1645 );
1646
1647 -- Added the following check chaild existance for the HESA requirment, pmarada
1648 OPEN c_hesa;
1649 FETCH c_hesa INTO l_hesa;
1650 IF c_hesa%FOUND THEN
1651 EXECUTE IMMEDIATE
1652 'BEGIN IGS_HE_ST_PROG_ALL_PKG.GET_FK_IGS_PS_VER_ALL(:1,:2); END;'
1653 USING
1654 old_references.course_cd,
1655 old_references.version_number;
1656 CLOSE c_hesa;
1657 ELSE
1658 CLOSE c_hesa;
1659 END IF;
1660
1661 igs_fi_ftci_accts_pkg.get_fk_igs_ps_ver(
1662 old_references.course_cd,
1663 old_references.version_number);
1664
1665 igs_en_psv_term_it_pkg.get_fk_igs_ps_ver(
1666 old_references.course_cd,
1667 old_references.version_number);
1668
1669
1670 END check_child_existance;
1671
1672 FUNCTION get_pk_for_validation (
1673 x_course_cd IN VARCHAR2,
1674 x_version_number IN NUMBER
1675 ) RETURN BOOLEAN AS
1676 /************************************************************************
1677 Created By :
1678 Date Created By :
1679 Purpose :
1680 Known limitations, enhancements or remarks:
1681 Change History :
1682 Who When What
1683 sarakshi 16-jun-2002 bug#2416973,locking to be done if course status is planned else not
1684 sbeerell 10-MAY-2000 Changed for new DLD version 2
1685 (reverse chronological order - newest change first)
1686 *************************************************************************/
1687
1688 -- Bug#2416978, Depending on the course status lock on the table is acquired
1689 -- lock is required when the course status is Planned since it is allowed to be delete
1690 -- lock is not required when the course status is non planned so an explicit lock is not required
1691 -- opening different cursors depending on the course status
1692 CURSOR cur_get_status IS
1693 SELECT st.s_course_status
1694 FROM igs_ps_ver_all v ,
1695 igs_ps_stat st
1696 WHERE v.course_status=st.course_status
1697 AND v.course_cd = x_course_cd
1698 AND v.version_number = x_version_number;
1699 l_course_status igs_ps_stat.s_course_status%TYPE;
1700
1701 CURSOR cur_rowid_planned IS
1702 SELECT ROWID
1703 FROM igs_ps_ver_all
1704 WHERE course_cd = x_course_cd
1705 AND version_number = x_version_number
1706 FOR UPDATE NOWAIT;
1707
1708 CURSOR cur_rowid_non_planned IS
1709 SELECT ROWID
1710 FROM igs_ps_ver_all
1711 WHERE course_cd = x_course_cd
1712 AND version_number = x_version_number;
1713
1714 lv_rowid cur_rowid_planned%ROWTYPE;
1715
1716 BEGIN
1717
1718 OPEN cur_get_status;
1719 FETCH cur_get_status INTO l_course_status;
1720 IF cur_get_status%NOTFOUND THEN
1721 CLOSE cur_get_status;
1722 RETURN(FALSE);
1723 ELSE
1724 CLOSE cur_get_status;
1725 IF l_course_status = 'PLANNED' THEN
1726 OPEN cur_rowid_planned;
1727 FETCH cur_rowid_planned INTO lv_rowid;
1728 IF cur_rowid_planned%FOUND THEN
1729 CLOSE cur_rowid_planned;
1730 RETURN (TRUE);
1731 ELSE
1732 CLOSE cur_rowid_planned;
1733 RETURN (FALSE);
1734 END IF;
1735 ELSE
1736 OPEN cur_rowid_non_planned;
1737 FETCH cur_rowid_non_planned INTO lv_rowid;
1738 IF cur_rowid_non_planned%FOUND THEN
1739 CLOSE cur_rowid_non_planned;
1740 RETURN (TRUE);
1741 ELSE
1742 CLOSE cur_rowid_non_planned;
1743 RETURN (FALSE);
1744 END IF;
1745 END IF;
1746 END IF;
1747
1748 END Get_PK_For_Validation;
1749
1750 PROCEDURE get_fk_igs_ps_course (
1751 x_course_cd IN VARCHAR2
1752 ) AS
1753 /************************************************************************
1754 Created By :
1755 Date Created By :
1756 Purpose :
1757 Known limitations, enhancements or remarks:
1758 Change History :
1759 Who When What
1760 sbeerell 10-MAY-2000 Changed for new DLD version 2
1761 (reverse chronological order - newest change first)
1762 *************************************************************************/
1763 CURSOR cur_rowid IS
1764 SELECT ROWID
1765 FROM igs_ps_ver_all
1766 WHERE course_cd = x_course_cd ;
1767
1768 lv_rowid cur_rowid%ROWTYPE;
1769
1770 BEGIN
1771
1772 OPEN cur_rowid;
1773 FETCH cur_rowid INTO lv_rowid;
1774 IF (cur_rowid%FOUND) THEN
1775 CLOSE cur_rowid;
1776 fnd_message.set_name ('IGS', 'IGS_PS_CRV_CRS_FK');
1777 igs_ge_msg_stack.add;
1778 app_exception.raise_exception;
1779 RETURN;
1780 END IF;
1781 CLOSE cur_rowid;
1782
1783 END get_fk_igs_ps_course;
1784
1785
1786 PROCEDURE get_fk_igs_ps_govt_spl_type (
1787 x_govt_special_course_type IN VARCHAR2
1788 ) AS
1789 /************************************************************************
1790 Created By :
1791 Date Created By :
1792 Purpose :
1793 Known limitations, enhancements or remarks:
1794 Change History :
1795 Who When What
1796 sbeerell 10-MAY-2000 Changed for new DLD version 2
1797 (reverse chronological order - newest change first)
1798 ************************************************************************/
1799 CURSOR cur_rowid IS
1800 SELECT ROWID
1801 FROM igs_ps_ver_all
1802 WHERE govt_special_course_type = x_govt_special_course_type ;
1803
1804 lv_rowid cur_rowid%ROWTYPE;
1805
1806 BEGIN
1807
1808 OPEN cur_rowid;
1809 FETCH cur_rowid INTO lv_rowid;
1810 IF (cur_rowid%FOUND) THEN
1811 CLOSE cur_rowid;
1812 fnd_message.set_name ('IGS', 'IGS_PS_CRV_GSCT_FK');
1813 igs_ge_msg_stack.add;
1814 app_exception.raise_exception;
1815 RETURN;
1816 END IF;
1817 CLOSE cur_rowid;
1818
1819 END get_fk_igs_ps_govt_spl_type;
1820
1821 PROCEDURE get_fk_igs_or_unit (
1822 x_org_unit_cd IN VARCHAR2,
1823 x_start_dt IN DATE
1824 ) AS
1825 /************************************************************************
1826 Created By :
1827 Date Created By :
1828 Purpose :
1829 Known limitations, enhancements or remarks:
1830 Change History :
1831 Who When What
1832 sbeerell 10-MAY-2000 Changed for new DLD version 2
1833 (reverse chronological order - newest change first)
1834 *************************************************************************/
1835
1836 CURSOR cur_rowid IS
1837 SELECT ROWID
1838 FROM igs_ps_ver_all
1839 WHERE responsible_org_unit_cd = x_org_unit_cd
1840 AND responsible_ou_start_dt = x_start_dt ;
1841
1842 lv_rowid cur_rowid%ROWTYPE;
1843
1844 BEGIN
1845
1846 OPEN cur_rowid;
1847 FETCH cur_rowid INTO lv_rowid;
1848 IF (cur_rowid%FOUND) THEN
1849 CLOSE cur_rowid;
1850 fnd_message.set_name ('IGS', 'IGS_PS_CRV_OU_FK');
1851 igs_ge_msg_stack.add;
1852 app_exception.raise_exception;
1853 RETURN;
1854 END IF;
1855 CLOSE cur_rowid;
1856
1857 END get_fk_igs_or_unit;
1858
1859 PROCEDURE get_fk_igs_ps_stat (
1860 x_course_status IN VARCHAR2
1861 ) AS
1862 /************************************************************************
1863 Created By :
1864 Date Created By :
1865 Purpose :
1866 Known limitations, enhancements or remarks:
1867 Change History :
1868 Who When What
1869 sbeerell 10-MAY-2000 Changed for new DLD version 2
1870 (reverse chronological order - newest change first)
1871 *************************************************************************/
1872
1873 CURSOR cur_rowid IS
1874 SELECT ROWID
1875 FROM igs_ps_ver_all
1876 WHERE course_status = x_course_status ;
1877
1878 lv_rowid cur_rowid%ROWTYPE;
1879
1880 BEGIN
1881
1882 OPEN cur_rowid;
1883 FETCH cur_rowid INTO lv_rowid;
1884 IF (cur_rowid%FOUND) THEN
1885 CLOSE cur_rowid;
1886 fnd_message.set_name ('IGS', 'IGS_PS_CRV_CRST_FK');
1887 igs_ge_msg_stack.add;
1888 app_exception.raise_exception;
1889 RETURN;
1890 END IF;
1891 CLOSE cur_rowid;
1892
1893 END get_fk_igs_ps_stat;
1894
1895 PROCEDURE before_dml (
1896 p_action IN VARCHAR2,
1897 x_rowid IN VARCHAR2 ,
1898 x_course_cd IN VARCHAR2 ,
1899 x_version_number IN NUMBER ,
1900 x_start_dt IN DATE ,
1901 x_review_dt IN DATE ,
1902 x_expiry_dt IN DATE ,
1903 x_end_dt IN DATE ,
1904 x_course_status IN VARCHAR2 ,
1905 x_title IN VARCHAR2 ,
1906 x_short_title IN VARCHAR2 ,
1907 x_abbreviation IN VARCHAR2 ,
1908 x_supp_exam_permitted_ind IN VARCHAR2 ,
1909 x_generic_course_ind IN VARCHAR2 ,
1910 x_graduate_students_ind IN VARCHAR2 ,
1911 x_count_intrmsn_in_time_ind IN VARCHAR2 ,
1912 x_intrmsn_allowed_ind IN VARCHAR2 ,
1913 x_course_type IN VARCHAR2 ,
1914 x_responsible_org_unit_cd IN VARCHAR2 ,
1915 x_responsible_ou_start_dt IN DATE ,
1916 x_govt_special_course_type IN VARCHAR2 ,
1917 x_qualification_recency IN NUMBER ,
1918 x_external_adv_stnd_limit IN NUMBER ,
1919 x_internal_adv_stnd_limit IN NUMBER ,
1920 x_contact_hours IN NUMBER ,
1921 x_credit_points_required IN NUMBER ,
1922 x_govt_course_load IN NUMBER ,
1923 x_std_annual_load IN NUMBER ,
1924 x_course_total_eftsu IN NUMBER ,
1925 x_max_intrmsn_duration IN NUMBER ,
1926 x_num_of_units_before_intrmsn IN NUMBER ,
1927 x_min_sbmsn_percentage IN NUMBER ,
1928 x_max_cp_per_teaching_period IN NUMBER ,
1929 x_approval_date IN DATE ,
1930 x_external_approval_date IN DATE ,
1931 x_residency_cp_required IN NUMBER ,
1932 x_state_financial_aid IN VARCHAR2 ,
1933 x_federal_financial_aid IN VARCHAR2 ,
1934 x_institutional_financial_aid IN VARCHAR2 ,
1935 x_attribute_category IN VARCHAR2 ,
1936 x_attribute1 IN VARCHAR2 ,
1937 x_attribute2 IN VARCHAR2 ,
1938 x_attribute3 IN VARCHAR2 ,
1939 x_attribute4 IN VARCHAR2 ,
1940 x_attribute5 IN VARCHAR2 ,
1941 x_attribute6 IN VARCHAR2 ,
1942 x_attribute7 IN VARCHAR2 ,
1943 x_attribute8 IN VARCHAR2 ,
1944 x_attribute9 IN VARCHAR2 ,
1945 x_attribute10 IN VARCHAR2 ,
1946 x_attribute11 IN VARCHAR2 ,
1947 x_attribute12 IN VARCHAR2 ,
1948 x_attribute13 IN VARCHAR2 ,
1949 x_attribute14 IN VARCHAR2 ,
1950 x_attribute15 IN VARCHAR2 ,
1951 x_attribute16 IN VARCHAR2 ,
1952 x_attribute17 IN VARCHAR2 ,
1953 x_attribute18 IN VARCHAR2 ,
1954 x_attribute19 IN VARCHAR2 ,
1955 x_attribute20 IN VARCHAR2 ,
1956 x_min_cp_per_calendar IN NUMBER ,
1957 x_creation_date IN DATE ,
1958 x_created_by IN NUMBER ,
1959 x_last_update_date IN DATE ,
1960 x_last_updated_by IN NUMBER ,
1961 x_last_update_login IN NUMBER ,
1962 x_org_id IN NUMBER ,
1963 x_rev_account_cd IN VARCHAR2 ,
1964 x_primary_program_rank NUMBER,
1965 x_max_wlst_per_stud IN NUMBER,
1966 x_annual_instruction_time IN NUMBER
1967 ) AS
1968 /************************************************************************
1969 Created By :
1970 Date Created By :
1971 Purpose :
1972 Known limitations, enhancements or remarks:
1973 Change History :
1974 Who When What
1975 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
1976 as these columns are obsolete.Also modified call to beforerowinsertupdatedelete1 to pass
1977 all the parameters as DEFAULT value is removed from the procedure definition.
1978 sarakshi 16-jun-2002 bug#2416973,added call to beforerowupdate,beforerowdelete
1979 smadathi 10-MAY-2001 Changed for New DLD Version
1980 sbeerell 10-MAY-2000 Changed for new DLD version 2
1981 (reverse chronological order - newest change first)
1982 *************************************************************************/
1983 BEGIN
1984 set_column_values (
1985 p_action,
1986 x_rowid,
1987 x_course_cd,
1988 x_version_number,
1989 x_start_dt,
1990 x_review_dt,
1991 x_expiry_dt,
1992 x_end_dt,
1993 x_course_status,
1994 x_title,
1995 x_short_title,
1996 x_abbreviation,
1997 x_supp_exam_permitted_ind,
1998 x_generic_course_ind,
1999 x_graduate_students_ind,
2000 x_count_intrmsn_in_time_ind,
2001 x_intrmsn_allowed_ind,
2002 x_course_type,
2003 x_responsible_org_unit_cd,
2004 x_responsible_ou_start_dt,
2005 x_govt_special_course_type,
2006 x_qualification_recency,
2007 x_external_adv_stnd_limit,
2008 x_internal_adv_stnd_limit,
2009 x_contact_hours,
2010 x_credit_points_required,
2011 x_govt_course_load,
2012 x_std_annual_load,
2013 x_course_total_eftsu,
2014 x_max_intrmsn_duration,
2015 x_num_of_units_before_intrmsn,
2016 x_min_sbmsn_percentage,
2017 x_max_cp_per_teaching_period,
2018 x_approval_date,
2019 x_external_approval_date,
2020 x_residency_cp_required,
2021 x_state_financial_aid,
2022 x_federal_financial_aid,
2023 x_institutional_financial_aid,
2024 x_attribute_category,
2025 x_attribute1,
2026 x_attribute2,
2027 x_attribute3,
2028 x_attribute4,
2029 x_attribute5,
2030 x_attribute6,
2031 x_attribute7,
2032 x_attribute8,
2033 x_attribute9,
2034 x_attribute10,
2035 x_attribute11,
2036 x_attribute12,
2037 x_attribute13,
2038 x_attribute14,
2039 x_attribute15,
2040 x_attribute16,
2041 x_attribute17,
2042 x_attribute18,
2043 x_attribute19,
2044 x_attribute20,
2045 x_min_cp_per_calendar,
2046 x_creation_date,
2047 x_created_by,
2048 x_last_update_date,
2049 x_last_updated_by,
2050 x_last_update_login,
2051 x_org_id,
2052 x_rev_account_cd,
2053 x_primary_program_rank,
2054 x_max_wlst_per_stud,
2055 x_annual_instruction_time
2056 );
2057
2058 IF (p_action = 'INSERT') THEN
2059 -- Call all the PROCEDUREs related to Before Insert.
2060 beforerowinsertupdatedelete1 ( p_inserting => TRUE,
2061 p_updating => FALSE,
2062 p_deleting => FALSE);
2063 IF get_pk_for_validation(
2064 new_references.course_cd ,
2065 new_references.version_number
2066 ) THEN
2067 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
2068 igs_ge_msg_stack.add;
2069 app_exception.raise_exception;
2070 END IF;
2071 check_constraints;
2072 check_parent_existance;
2073 ELSIF (p_action = 'UPDATE') THEN
2074 -- Call all the PROCEDUREs related to Before Update.
2075 beforerowinsertupdatedelete1 ( p_inserting => FALSE,
2076 p_updating => TRUE,
2077 p_deleting => FALSE);
2078 --bug#2416973,check updation specific validation
2079 beforerowupdate;
2080 check_constraints;
2081 check_parent_existance;
2082 ELSIF (p_action = 'DELETE') THEN
2083 -- Call all the PROCEDUREs related to Before Delete.
2084 beforerowinsertupdatedelete1 ( p_inserting => FALSE,
2085 p_updating => FALSE,
2086 p_deleting => TRUE );
2087 --bug#2416973,check deletion specific validation
2088 beforerowdelete;
2089 check_child_existance;
2090 ELSIF (p_action = 'VALIDATE_INSERT') THEN
2091 IF get_pk_for_validation(
2092 new_references.course_cd ,
2093 new_references.version_number
2094 ) THEN
2095 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
2096 igs_ge_msg_stack.add;
2097 app_exception.raise_exception;
2098 END IF;
2099 check_constraints;
2100 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
2101 check_constraints;
2102 ELSIF (p_action = 'VALIDATE_DELETE') THEN
2103 check_child_existance;
2104 END IF;
2105 END before_dml;
2106
2107 Procedure dflt_prgm_ref_code ( p_course_cd IGS_PS_VER_ALL.COURSE_CD%TYPE,
2108 p_version_number IGS_PS_VER_ALL.VERSION_NUMBER%TYPE
2109 )AS
2110
2111 /************************************************************************
2112 Created By : Aiyer
2113 Date Created By : 12/06/2001
2114 Purpose : Insertion into table IGS_PS_REF_CD for mandatory ref code types and default ref codes
2115 Known limitations, enhancements or remarks:
2116 Change History :
2117 Who When What
2118 *************************************************************************/
2119 CURSOR c_igs_ge_ref_cd_type_all
2120 IS
2121 SELECT
2122 reference_cd_type,
2123 mandatory_flag,
2124 program_flag,
2125 closed_ind
2126 FROM
2127 igs_ge_ref_cd_type_all
2128 WHERE
2129 mandatory_flag ='Y'
2130 AND
2131 program_flag ='Y'
2132 AND
2133 restricted_flag='Y'
2134 AND
2135 closed_ind = 'N';
2136
2137 CURSOR c_igs_ge_ref_cd (p_c_reference_cd_type IGS_PS_REF_CD.REFERENCE_CD_TYPE%TYPE)
2138 IS
2139 SELECT
2140 reference_cd,
2141 description
2142 FROM
2143 igs_ge_ref_cd
2144 WHERE
2145 reference_cd_type = p_c_reference_cd_type
2146 AND default_flag = 'Y';
2147 l_c_rowid VARCHAR2(25) := NULL;
2148 BEGIN
2149 FOR cur_igs_ge_ref_cd_type_all IN c_igs_ge_ref_cd_type_all
2150 LOOP
2151 FOR cur_igs_ge_ref_cd IN c_igs_ge_ref_cd(cur_igs_ge_ref_cd_type_all.reference_cd_type)
2152 LOOP
2153 -- insert a value in igs_ps_ref_cd for every value of course_cd and version_number having
2154 -- a applicable program level defined as mandatory and a default reference code
2155 BEGIN
2156 l_c_rowid:=NULL;
2157 igs_ps_ref_cd_pkg.INSERT_ROW (
2158 X_ROWID => l_c_rowid,
2159 X_COURSE_CD => p_course_cd,
2160 X_VERSION_NUMBER => p_version_number,
2161 X_REFERENCE_CD_TYPE => cur_igs_ge_ref_cd_type_all.reference_cd_type ,
2162 X_REFERENCE_CD => cur_igs_ge_ref_cd.reference_cd ,
2163 X_DESCRIPTION => cur_igs_ge_ref_cd.description ,
2164 X_MODE => 'R'
2165 );
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 -- The failure of insertion of reference code should not stop the creation for a program.Hence any exception
2169 --raised by the TBH is trapped and the current processing is allowed to proceed
2170 NULL;
2171 END;
2172 END LOOP;
2173 END LOOP;
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 -- If an error occurs during insertion in igs_ps_ref_cd then raise an exception.
2177 igs_ge_msg_stack.add;
2178 app_exception.raise_exception;
2179 RETURN;
2180 END dflt_prgm_ref_code;
2181
2182 PROCEDURE after_dml (
2183 p_action IN VARCHAR2,
2184 x_rowid IN VARCHAR2
2185 ) AS
2186 /************************************************************************
2187 Created By :
2188 Date Created By :
2189 Purpose :
2190 Known limitations, enhancements or remarks:
2191 Change History :
2192 Who When What
2193 sbeerell 10-MAY-2000 Changed for new DLD version 2
2194 (reverse chronological order - newest change first)
2195 *************************************************************************/
2196 CURSOR c_fetch_cou_ver
2197 IS
2198 SELECT
2199 course_cd,
2200 version_number
2201 FROM
2202 igs_ps_ver pv
2203 WHERE
2204 row_id = x_rowid;
2205
2206 cur_fetch_cou_ver c_fetch_cou_ver%rowtype;
2207 BEGIN
2208 l_rowid := x_rowid;
2209 IF (p_action = 'INSERT') THEN
2210 -- Call all the PROCEDUREs related to After Insert.
2211 afterrowinsertupdate2 ( p_inserting => TRUE,
2212 p_updating => FALSE,
2213 p_deleting => FALSE);
2214 -- This code has been added by aiyer to insert rows into table igs_ps_ref_cd for corresponding course_cd and version_number
2215 OPEN c_fetch_cou_ver;
2216 FETCH c_fetch_cou_ver INTO cur_fetch_cou_ver;
2217 dflt_prgm_ref_code ( p_course_cd => cur_fetch_cou_ver.course_cd,
2218 p_version_number => cur_fetch_cou_ver.version_number
2219 );
2220 CLOSE c_fetch_cou_ver;
2221 ELSIF (p_action = 'UPDATE') THEN
2222 -- Call all the PROCEDUREs related to After Update.
2223 afterrowinsertupdate2 ( p_inserting => FALSE,
2224 p_updating => TRUE,
2225 p_deleting => FALSE);
2226 check_constraints;
2227
2228 END IF;
2229 END after_dml;
2230
2231 PROCEDURE insert_row (
2232 x_rowid IN OUT NOCOPY VARCHAR2,
2233 x_course_cd IN VARCHAR2,
2234 x_version_number IN NUMBER,
2235 x_start_dt IN DATE,
2236 x_review_dt IN DATE,
2237 x_expiry_dt IN DATE,
2238 x_end_dt IN DATE,
2239 x_course_status IN VARCHAR2,
2240 x_title IN VARCHAR2,
2241 x_short_title IN VARCHAR2,
2242 x_abbreviation IN VARCHAR2,
2243 x_supp_exam_permitted_ind IN VARCHAR2,
2244 x_generic_course_ind IN VARCHAR2,
2245 x_graduate_students_ind IN VARCHAR2,
2246 x_count_intrmsn_in_time_ind IN VARCHAR2,
2247 x_intrmsn_allowed_ind IN VARCHAR2,
2248 x_course_type IN VARCHAR2,
2249 x_responsible_org_unit_cd IN VARCHAR2,
2250 x_responsible_ou_start_dt IN DATE,
2251 x_govt_special_course_type IN VARCHAR2,
2252 x_qualification_recency IN NUMBER,
2253 x_external_adv_stnd_limit IN NUMBER,
2254 x_internal_adv_stnd_limit IN NUMBER,
2255 x_contact_hours IN NUMBER,
2256 x_credit_points_required IN NUMBER,
2257 x_govt_course_load IN NUMBER,
2258 x_std_annual_load IN NUMBER,
2259 x_course_total_eftsu IN NUMBER,
2260 x_max_intrmsn_duration IN NUMBER,
2261 x_num_of_units_before_intrmsn IN NUMBER,
2262 x_min_sbmsn_percentage IN NUMBER,
2263 x_max_cp_per_teaching_period IN NUMBER,
2264 x_approval_date IN DATE,
2265 x_external_approval_date IN DATE,
2266 x_residency_cp_required IN NUMBER,
2267 x_state_financial_aid IN VARCHAR2,
2268 x_federal_financial_aid IN VARCHAR2,
2269 x_institutional_financial_aid IN VARCHAR2,
2270 x_attribute_category IN VARCHAR2,
2271 x_attribute1 IN VARCHAR2,
2272 x_attribute2 IN VARCHAR2,
2273 x_attribute3 IN VARCHAR2,
2274 x_attribute4 IN VARCHAR2,
2275 x_attribute5 IN VARCHAR2,
2276 x_attribute6 IN VARCHAR2,
2277 x_attribute7 IN VARCHAR2,
2278 x_attribute8 IN VARCHAR2,
2279 x_attribute9 IN VARCHAR2,
2280 x_attribute10 IN VARCHAR2,
2281 x_attribute11 IN VARCHAR2,
2282 x_attribute12 IN VARCHAR2,
2283 x_attribute13 IN VARCHAR2,
2284 x_attribute14 IN VARCHAR2,
2285 x_attribute15 IN VARCHAR2,
2286 x_attribute16 IN VARCHAR2,
2287 x_attribute17 IN VARCHAR2,
2288 x_attribute18 IN VARCHAR2,
2289 x_attribute19 IN VARCHAR2,
2290 x_attribute20 IN VARCHAR2,
2291 x_mode IN VARCHAR2,
2292 x_org_id IN NUMBER,
2293 x_min_cp_per_calendar IN NUMBER,
2294 x_rev_account_cd IN VARCHAR2 ,
2295 x_primary_program_rank IN NUMBER,
2296 x_max_wlst_per_stud IN NUMBER,
2297 x_annual_instruction_time IN NUMBER
2298 ) AS
2299 /************************************************************************
2300 Created By :
2301 Date Created By :
2302 Purpose :
2303 Known limitations, enhancements or remarks:
2304 Change History :
2305 Who When What
2306 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
2307 as these columns are obsolete.
2308 msrinivi 08-Aug-2001 Added new col rev_Account_cd bug # 1882122
2309 smadathi 10-MAY-2001 Changed for New DLD Version
2310 sbeerell 10-MAY-2000 Modifiaction for DLD Version 2
2311 (reverse chronological order - newest change first)
2312 *************************************************************************/
2313
2314 CURSOR c IS SELECT ROWID FROM igs_ps_ver_all
2315 WHERE course_cd = x_course_cd
2316 AND version_number = x_version_number;
2317 x_last_update_date DATE;
2318 x_last_updated_by NUMBER;
2319 x_last_update_login NUMBER;
2320 BEGIN
2321 x_last_update_date := SYSDATE;
2322 IF(x_mode = 'I') THEN
2323 x_last_updated_by := 1;
2324 x_last_update_login := 0;
2325 ELSIF (x_mode = 'R') THEN
2326 x_last_updated_by := fnd_global.user_id;
2327 IF x_last_updated_by IS NULL THEN
2328 x_last_updated_by := -1;
2329 END IF;
2330 x_last_update_login :=fnd_global.login_id;
2331 IF x_last_update_login IS NULL THEN
2332 x_last_update_login := -1;
2333 END IF;
2334 ELSE
2335 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
2336 igs_ge_msg_stack.add;
2337 app_exception.raise_exception;
2338 END IF;
2339
2340 before_dml( p_action => 'INSERT',
2341 x_rowid => x_rowid,
2342 x_course_cd => x_course_cd,
2343 x_version_number => x_version_number,
2344 x_start_dt => x_start_dt,
2345 x_review_dt => x_review_dt,
2346 x_expiry_dt => x_expiry_dt,
2347 x_end_dt => x_end_dt,
2348 x_course_status => x_course_status,
2349 x_title => x_title,
2350 x_short_title => x_short_title,
2351 x_abbreviation => x_abbreviation,
2352 x_supp_exam_permitted_ind => NVL(x_supp_exam_permitted_ind,'Y'),
2353 x_generic_course_ind => NVL(x_generic_course_ind,'N'),
2354 x_graduate_students_ind => NVL(x_graduate_students_ind,'Y'),
2355 x_count_intrmsn_in_time_ind => NVL(x_count_intrmsn_in_time_ind,'Y'),
2356 x_intrmsn_allowed_ind => NVL(x_intrmsn_allowed_ind,'Y'),
2357 x_course_type => x_course_type,
2358 x_responsible_org_unit_cd => x_responsible_org_unit_cd,
2359 x_responsible_ou_start_dt => x_responsible_ou_start_dt,
2360 x_govt_special_course_type => x_govt_special_course_type,
2361 x_qualification_recency => x_qualification_recency,
2362 x_external_adv_stnd_limit => x_external_adv_stnd_limit,
2363 x_internal_adv_stnd_limit => x_internal_adv_stnd_limit,
2364 x_contact_hours => x_contact_hours,
2365 x_credit_points_required => x_credit_points_required,
2366 x_govt_course_load => x_govt_course_load,
2367 x_std_annual_load => x_std_annual_load,
2368 x_course_total_eftsu => x_course_total_eftsu,
2369 x_max_intrmsn_duration => x_max_intrmsn_duration,
2370 x_num_of_units_before_intrmsn => x_num_of_units_before_intrmsn,
2371 x_min_sbmsn_percentage=>x_min_sbmsn_percentage,
2372 x_max_cp_per_teaching_period=>x_max_cp_per_teaching_period,
2373 x_approval_date=>x_approval_date,
2374 x_external_approval_date=>x_external_approval_date,
2375 x_residency_cp_required=>x_residency_cp_required,
2376 x_state_financial_aid=>x_state_financial_aid,
2377 x_federal_financial_aid=>x_federal_financial_aid,
2378 x_institutional_financial_aid=>x_institutional_financial_aid,
2379 x_attribute_category=>x_attribute_category,
2380 x_attribute1=>x_attribute1,
2381 x_attribute2=>x_attribute2,
2382 x_attribute3=>x_attribute3,
2383 x_attribute4=>x_attribute4,
2384 x_attribute5=>x_attribute5,
2385 x_attribute6=>x_attribute6,
2386 x_attribute7=>x_attribute7,
2387 x_attribute8=>x_attribute8,
2388 x_attribute9=>x_attribute9,
2389 x_attribute10=>x_attribute10,
2390 x_attribute11=>x_attribute11,
2391 x_attribute12=>x_attribute12,
2392 x_attribute13=>x_attribute13,
2393 x_attribute14=>x_attribute14,
2394 x_attribute15=>x_attribute15,
2395 x_attribute16=>x_attribute16,
2396 x_attribute17=>x_attribute17,
2397 x_attribute18=>x_attribute18,
2398 x_attribute19=>x_attribute19,
2399 x_attribute20=>x_attribute20,
2400 x_min_cp_per_calendar=>x_min_cp_per_calendar,
2401 x_creation_date => x_last_update_date,
2402 x_created_by => x_last_updated_by,
2403 x_last_update_date => x_last_update_date,
2404 x_last_updated_by => x_last_updated_by,
2405 x_last_update_login => x_last_update_login,
2406 x_org_id=>igs_ge_gen_003.get_org_id,
2407 x_rev_account_cd =>x_rev_account_cd,
2408 x_primary_program_rank => x_primary_program_rank,
2409 x_max_wlst_per_stud => x_max_wlst_per_stud,
2410 x_annual_instruction_time => x_annual_instruction_time
2411 );
2412
2413 INSERT INTO igs_ps_ver_all (
2414 graduate_students_ind,
2415 count_intrmsn_in_time_ind,
2416 intrmsn_allowed_ind,
2417 course_type,
2418 responsible_org_unit_cd,
2419 responsible_ou_start_dt,
2420 govt_special_course_type,
2421 qualification_recency,
2422 external_adv_stnd_limit,
2423 internal_adv_stnd_limit,
2424 contact_hours,
2425 credit_points_required,
2426 govt_course_load,
2427 std_annual_load,
2428 course_total_eftsu,
2429 max_intrmsn_duration,
2430 num_of_units_before_intrmsn,
2431 min_sbmsn_percentage,
2432 course_cd,
2433 version_number,
2434 start_dt,
2435 review_dt,
2436 expiry_dt,
2437 end_dt,
2438 course_status,
2439 title,
2440 short_title,
2441 abbreviation,
2442 supp_exam_permitted_ind,
2443 generic_course_ind,
2444 max_cp_per_teaching_period,
2445 approval_date,
2446 external_approval_date,
2447 residency_cp_required,
2448 state_financial_aid,
2449 federal_financial_aid,
2450 institutional_financial_aid,
2451 attribute_category,
2452 attribute1,
2453 attribute2,
2454 attribute3,
2455 attribute4,
2456 attribute5,
2457 attribute6,
2458 attribute7,
2459 attribute8,
2460 attribute9,
2461 attribute10,
2462 attribute11,
2463 attribute12,
2464 attribute13,
2465 attribute14,
2466 attribute15,
2467 attribute16,
2468 attribute17,
2469 attribute18,
2470 attribute19,
2471 attribute20,
2472 creation_date,
2473 created_by,
2474 last_update_date,
2475 last_updated_by,
2476 last_update_login,
2477 org_id,
2478 min_cp_per_calendar,
2479 rev_account_cd,
2480 primary_program_rank,
2481 max_wlst_per_stud,
2482 annual_instruction_time
2483 ) VALUES (
2484 new_references.graduate_students_ind,
2485 new_references.count_intrmsn_in_time_ind,
2486 new_references.intrmsn_allowed_ind,
2487 new_references.course_type,
2488 new_references.responsible_org_unit_cd,
2489 new_references.responsible_ou_start_dt,
2490 new_references.govt_special_course_type,
2491 new_references.qualification_recency,
2492 new_references.external_adv_stnd_limit,
2493 new_references.internal_adv_stnd_limit,
2494 new_references.contact_hours,
2495 new_references.credit_points_required,
2496 new_references.govt_course_load,
2497 new_references.std_annual_load,
2498 new_references.course_total_eftsu,
2499 new_references.max_intrmsn_duration,
2500 new_references.num_of_units_before_intrmsn,
2501 new_references.min_sbmsn_percentage,
2502 new_references.course_cd,
2503 new_references.version_number,
2504 new_references.start_dt,
2505 new_references.review_dt,
2506 new_references.expiry_dt,
2507 new_references.end_dt,
2508 new_references.course_status,
2509 new_references.title,
2510 new_references.short_title,
2511 new_references.abbreviation,
2512 new_references.supp_exam_permitted_ind,
2513 new_references.generic_course_ind,
2514 new_references.max_cp_per_teaching_period,
2515 new_references.approval_date,
2516 new_references.external_approval_date,
2517 new_references.residency_cp_required,
2518 new_references.state_financial_aid,
2519 new_references.federal_financial_aid,
2520 new_references.institutional_financial_aid,
2521 new_references.attribute_category,
2522 new_references.attribute1,
2523 new_references.attribute2,
2524 new_references.attribute3,
2525 new_references.attribute4,
2526 new_references.attribute5,
2527 new_references.attribute6,
2528 new_references.attribute7,
2529 new_references.attribute8,
2530 new_references.attribute9,
2531 new_references.attribute10,
2532 new_references.attribute11,
2533 new_references.attribute12,
2534 new_references.attribute13,
2535 new_references.attribute14,
2536 new_references.attribute15,
2537 new_references.attribute16,
2538 new_references.attribute17,
2539 new_references.attribute18,
2540 new_references.attribute19,
2541 new_references.attribute20,
2542 x_last_update_date,
2543 x_last_updated_by,
2544 x_last_update_date,
2545 x_last_updated_by,
2546 x_last_update_login,
2547 new_references.org_id,
2548 new_references.min_cp_per_calendar,
2549 new_references.rev_account_cd,
2550 new_references.primary_program_rank,
2551 new_references.max_wlst_per_stud,
2552 new_references.annual_instruction_time
2553 );
2554
2555 OPEN c;
2556 FETCH c INTO x_rowid;
2557 IF (c%NOTFOUND) THEN
2558 CLOSE c;
2559 RAISE NO_DATA_FOUND;
2560 END IF;
2561 CLOSE c;
2562 after_dml(
2563 p_action => 'INSERT',
2564 x_rowid => x_rowid
2565 );
2566 END insert_row;
2567
2568 PROCEDURE lock_row (
2569 x_rowid IN VARCHAR2,
2570 x_course_cd IN VARCHAR2,
2571 x_version_number IN NUMBER,
2572 x_start_dt IN DATE,
2573 x_review_dt IN DATE,
2574 x_expiry_dt IN DATE,
2575 x_end_dt IN DATE,
2576 x_course_status IN VARCHAR2,
2577 x_title IN VARCHAR2,
2578 x_short_title IN VARCHAR2,
2579 x_abbreviation IN VARCHAR2,
2580 x_supp_exam_permitted_ind IN VARCHAR2,
2581 x_generic_course_ind IN VARCHAR2,
2582 x_graduate_students_ind IN VARCHAR2,
2583 x_count_intrmsn_in_time_ind IN VARCHAR2,
2584 x_intrmsn_allowed_ind IN VARCHAR2,
2585 x_course_type IN VARCHAR2,
2586 x_responsible_org_unit_cd IN VARCHAR2,
2587 x_responsible_ou_start_dt IN DATE,
2588 x_govt_special_course_type IN VARCHAR2,
2589 x_qualification_recency IN NUMBER,
2590 x_external_adv_stnd_limit IN NUMBER,
2591 x_internal_adv_stnd_limit IN NUMBER,
2592 x_contact_hours IN NUMBER,
2593 x_credit_points_required IN NUMBER,
2594 x_govt_course_load IN NUMBER,
2595 x_std_annual_load IN NUMBER,
2596 x_course_total_eftsu IN NUMBER,
2597 x_max_intrmsn_duration IN NUMBER,
2598 x_num_of_units_before_intrmsn IN NUMBER,
2599 x_min_sbmsn_percentage IN NUMBER,
2600 x_max_cp_per_teaching_period IN NUMBER,
2601 x_approval_date IN DATE,
2602 x_external_approval_date IN DATE,
2603 x_residency_cp_required IN NUMBER,
2604 x_state_financial_aid IN VARCHAR2,
2605 x_federal_financial_aid IN VARCHAR2,
2606 x_institutional_financial_aid IN VARCHAR2,
2607 x_attribute_category IN VARCHAR2,
2608 x_attribute1 IN VARCHAR2,
2609 x_attribute2 IN VARCHAR2,
2610 x_attribute3 IN VARCHAR2,
2611 x_attribute4 IN VARCHAR2,
2612 x_attribute5 IN VARCHAR2,
2613 x_attribute6 IN VARCHAR2,
2614 x_attribute7 IN VARCHAR2,
2615 x_attribute8 IN VARCHAR2,
2616 x_attribute9 IN VARCHAR2,
2617 x_attribute10 IN VARCHAR2,
2618 x_attribute11 IN VARCHAR2,
2619 x_attribute12 IN VARCHAR2,
2620 x_attribute13 IN VARCHAR2,
2621 x_attribute14 IN VARCHAR2,
2622 x_attribute15 IN VARCHAR2,
2623 x_attribute16 IN VARCHAR2,
2624 x_attribute17 IN VARCHAR2,
2625 x_attribute18 IN VARCHAR2,
2626 x_attribute19 IN VARCHAR2,
2627 x_attribute20 IN VARCHAR2,
2628 x_min_cp_per_calendar IN NUMBER,
2629 x_rev_account_cd IN VARCHAR2 ,
2630 x_primary_program_rank IN NUMBER,
2631 x_max_wlst_per_stud IN NUMBER,
2632 x_annual_instruction_time IN NUMBER
2633 ) AS
2634
2635 /************************************************************************
2636 Created By :
2637 Date Created By :
2638 Purpose :
2639 Known limitations, enhancements or remarks:
2640 Change History :
2641 Who When What
2642 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
2643 as these columns are obsolete.
2644 smadathi 10-MAY-2001 Changed for New DLD Version
2645 sbeerell 10-MAY-2000 Changed for new DLD version 2
2646 (reverse chronological order - newest change first)
2647 *************************************************************************/
2648 CURSOR c1 IS SELECT
2649 graduate_students_ind,
2650 count_intrmsn_in_time_ind,
2651 intrmsn_allowed_ind,
2652 course_type,
2653 responsible_org_unit_cd,
2654 responsible_ou_start_dt,
2655 govt_special_course_type,
2656 qualification_recency,
2657 external_adv_stnd_limit,
2658 internal_adv_stnd_limit,
2659 contact_hours,
2660 credit_points_required,
2661 govt_course_load,
2662 std_annual_load,
2663 course_total_eftsu,
2664 max_intrmsn_duration,
2665 num_of_units_before_intrmsn,
2666 min_sbmsn_percentage,
2667 start_dt,
2668 review_dt,
2669 expiry_dt,
2670 end_dt,
2671 course_status,
2672 title,
2673 short_title,
2674 abbreviation,
2675 supp_exam_permitted_ind,
2676 generic_course_ind,
2677 max_cp_per_teaching_period,
2678 approval_date,
2679 external_approval_date,
2680 residency_cp_required,
2681 state_financial_aid,
2682 federal_financial_aid,
2683 institutional_financial_aid,
2684 attribute_category,
2685 attribute1,
2686 attribute2,
2687 attribute3,
2688 attribute4,
2689 attribute5,
2690 attribute6,
2691 attribute7,
2692 attribute8,
2693 attribute9,
2694 attribute10,
2695 attribute11,
2696 attribute12,
2697 attribute13,
2698 attribute14,
2699 attribute15,
2700 attribute16,
2701 attribute17,
2702 attribute18,
2703 attribute19,
2704 attribute20,
2705 min_cp_per_calendar,
2706 rev_account_cd,
2707 primary_program_rank,
2708 max_wlst_per_stud,
2709 annual_instruction_time
2710 FROM igs_ps_ver_all
2711 WHERE ROWID = x_rowid FOR UPDATE NOWAIT;
2712 tlinfo c1%ROWTYPE;
2713
2714 BEGIN
2715 OPEN c1;
2716 FETCH c1 INTO tlinfo;
2717 IF (c1%NOTFOUND) THEN
2718 CLOSE c1;
2719 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2720 app_exception.raise_exception;
2721 RETURN;
2722 END IF;
2723 CLOSE c1;
2724
2725 IF( (tlinfo.graduate_students_ind = x_graduate_students_ind)
2726 AND (tlinfo.count_intrmsn_in_time_ind = x_count_intrmsn_in_time_ind)
2727 AND (tlinfo.intrmsn_allowed_ind = x_intrmsn_allowed_ind)
2728 AND (tlinfo.course_type = x_course_type)
2729 AND (tlinfo.responsible_org_unit_cd = x_responsible_org_unit_cd)
2730 AND (tlinfo.responsible_ou_start_dt = x_responsible_ou_start_dt)
2731 AND (tlinfo.govt_special_course_type = x_govt_special_course_type)
2732 AND ((tlinfo.qualification_recency = x_qualification_recency)
2733 OR ((tlinfo.qualification_recency IS NULL)
2734 AND (x_qualification_recency IS NULL)))
2735 AND ((tlinfo.external_adv_stnd_limit = x_external_adv_stnd_limit)
2736 OR ((tlinfo.external_adv_stnd_limit IS NULL)
2737 AND (x_external_adv_stnd_limit IS NULL)))
2738 AND ((tlinfo.internal_adv_stnd_limit = x_internal_adv_stnd_limit)
2739 OR ((tlinfo.internal_adv_stnd_limit IS NULL)
2740 AND (x_internal_adv_stnd_limit IS NULL)))
2741 AND ((tlinfo.contact_hours = x_contact_hours)
2742 OR ((tlinfo.contact_hours IS NULL)
2743 AND (x_contact_hours IS NULL)))
2744 AND ((tlinfo.credit_points_required = x_credit_points_required)
2745 OR ((tlinfo.credit_points_required IS NULL)
2746 AND (x_credit_points_required IS NULL)))
2747 AND ((tlinfo.govt_course_load = x_govt_course_load)
2748 OR ((tlinfo.govt_course_load IS NULL)
2749 AND (x_govt_course_load IS NULL)))
2750 AND ((tlinfo.std_annual_load = x_std_annual_load)
2751 OR ((tlinfo.std_annual_load IS NULL)
2752 AND (x_std_annual_load IS NULL)))
2753 AND ((tlinfo.course_total_eftsu = x_course_total_eftsu)
2754 OR ((tlinfo.course_total_eftsu IS NULL)
2755 AND (x_course_total_eftsu IS NULL)))
2756 AND ((tlinfo.max_intrmsn_duration = x_max_intrmsn_duration)
2757 OR ((tlinfo.max_intrmsn_duration IS NULL)
2758 AND (x_max_intrmsn_duration IS NULL)))
2759 AND ((tlinfo.num_of_units_before_intrmsn = x_num_of_units_before_intrmsn)
2760 OR ((tlinfo.num_of_units_before_intrmsn IS NULL)
2761 AND (x_num_of_units_before_intrmsn IS NULL)))
2762 AND ((tlinfo.min_sbmsn_percentage = x_min_sbmsn_percentage)
2763 OR ((tlinfo.min_sbmsn_percentage IS NULL)
2764 AND (x_min_sbmsn_percentage IS NULL)))
2765 AND (tlinfo.start_dt = x_start_dt)
2766 AND ((tlinfo.review_dt = x_review_dt)
2767 OR ((tlinfo.review_dt IS NULL)
2768 AND (x_review_dt IS NULL)))
2769 AND ((tlinfo.expiry_dt = x_expiry_dt)
2770 OR ((tlinfo.expiry_dt IS NULL)
2771 AND (x_expiry_dt IS NULL)))
2772 AND ((tlinfo.end_dt = x_end_dt)
2773 OR ((tlinfo.end_dt IS NULL)
2774 AND (x_end_dt IS NULL)))
2775 AND (tlinfo.course_status = x_course_status)
2776 AND (tlinfo.title = x_title)
2777 AND (tlinfo.short_title = x_short_title)
2778 AND (tlinfo.abbreviation = x_abbreviation)
2779 AND (tlinfo.supp_exam_permitted_ind = x_supp_exam_permitted_ind)
2780 AND (tlinfo.generic_course_ind = x_generic_course_ind)
2781 AND ((tlinfo.max_cp_per_teaching_period = x_max_cp_per_teaching_period)
2782 OR ((tlinfo.max_cp_per_teaching_period IS NULL)
2783 AND (x_max_cp_per_teaching_period IS NULL)))
2784 AND ((tlinfo.approval_date = x_approval_date)
2785 OR ((tlinfo.approval_date IS NULL)
2786 AND (x_approval_date IS NULL)))
2787 AND ((tlinfo.external_approval_date = x_external_approval_date)
2788 OR ((tlinfo.external_approval_date IS NULL)
2789 AND (x_external_approval_date IS NULL)))
2790 AND ((tlinfo.residency_cp_required = x_residency_cp_required)
2791 OR ((tlinfo.residency_cp_required IS NULL)
2792 AND (x_residency_cp_required IS NULL)))
2793 AND ((tlinfo.state_financial_aid = x_state_financial_aid)
2794 OR ((tlinfo.state_financial_aid IS NULL)
2795 AND (x_state_financial_aid IS NULL)))
2796 AND ((tlinfo.federal_financial_aid = x_federal_financial_aid)
2797 OR ((tlinfo.federal_financial_aid IS NULL)
2798 AND (x_federal_financial_aid IS NULL)))
2799 AND ((tlinfo.institutional_financial_aid = x_institutional_financial_aid)
2800 OR ((tlinfo.institutional_financial_aid IS NULL)
2801 AND (x_institutional_financial_aid IS NULL)))
2802 AND ((tlinfo.attribute_category = x_attribute_category)
2803 OR ((tlinfo.attribute_category IS NULL)
2804 AND (x_attribute_category IS NULL)))
2805 AND ((tlinfo.attribute1 = x_attribute1)
2806 OR ((tlinfo.attribute1 IS NULL)
2807 AND (x_attribute1 IS NULL)))
2808 AND ((tlinfo.attribute2 = x_attribute2)
2809 OR ((tlinfo.attribute2 IS NULL)
2810 AND (x_attribute2 IS NULL)))
2811 AND ((tlinfo.attribute3 = x_attribute3)
2812 OR ((tlinfo.attribute3 IS NULL)
2813 AND (x_attribute3 IS NULL)))
2814 AND ((tlinfo.attribute4 = x_attribute4)
2815 OR ((tlinfo.attribute4 IS NULL)
2816 AND (x_attribute4 IS NULL)))
2817 AND ((tlinfo.attribute5 = x_attribute5)
2818 OR ((tlinfo.attribute5 IS NULL)
2819 AND (x_attribute5 IS NULL)))
2820 AND ((tlinfo.attribute6 = x_attribute6)
2821 OR ((tlinfo.attribute6 IS NULL)
2822 AND (x_attribute6 IS NULL)))
2823 AND ((tlinfo.attribute7 = x_attribute7)
2824 OR ((tlinfo.attribute7 IS NULL)
2825 AND (x_attribute7 IS NULL)))
2826 AND ((tlinfo.attribute8 = x_attribute8)
2827 OR ((tlinfo.attribute8 IS NULL)
2828 AND (x_attribute8 IS NULL)))
2829 AND ((tlinfo.attribute9 = x_attribute9)
2830 OR ((tlinfo.attribute9 IS NULL)
2831 AND (x_attribute9 IS NULL)))
2832 AND ((tlinfo.attribute10 = x_attribute10)
2833 OR ((tlinfo.attribute10 IS NULL)
2834 AND (x_attribute10 IS NULL)))
2835 AND ((tlinfo.attribute11 = x_attribute11)
2836 OR ((tlinfo.attribute11 IS NULL)
2837 AND (x_attribute11 IS NULL)))
2838 AND ((tlinfo.attribute12 = x_attribute12)
2839 OR ((tlinfo.attribute12 IS NULL)
2840 AND (x_attribute12 IS NULL)))
2841 AND ((tlinfo.attribute13 = x_attribute13)
2842 OR ((tlinfo.attribute13 IS NULL)
2843 AND (x_attribute13 IS NULL)))
2844 AND ((tlinfo.attribute14 = x_attribute14)
2845 OR ((tlinfo.attribute14 IS NULL)
2846 AND (x_attribute14 IS NULL)))
2847 AND ((tlinfo.attribute15 = x_attribute15)
2848 OR ((tlinfo.attribute15 IS NULL)
2849 AND (x_attribute15 IS NULL)))
2850 AND ((tlinfo.attribute16 = x_attribute16)
2851 OR ((tlinfo.attribute16 IS NULL)
2852 AND (x_attribute16 IS NULL)))
2853 AND ((tlinfo.attribute17 = x_attribute17)
2854 OR ((tlinfo.attribute17 IS NULL)
2855 AND (x_attribute17 IS NULL)))
2856 AND ((tlinfo.attribute18 = x_attribute18)
2857 OR ((tlinfo.attribute18 IS NULL)
2858 AND (x_attribute18 IS NULL)))
2859 AND ((tlinfo.attribute19 = x_attribute19)
2860 OR ((tlinfo.attribute19 IS NULL)
2861 AND (x_attribute19 IS NULL)))
2862 AND ((tlinfo.attribute20 = x_attribute20)
2863 OR ((tlinfo.attribute20 IS NULL)
2864 AND (x_attribute20 IS NULL)))
2865 AND ((tlinfo.min_cp_per_calendar = x_min_cp_per_calendar)
2866 OR ((tlinfo.min_cp_per_calendar IS NULL)
2867 AND (x_min_cp_per_calendar IS NULL)))
2868 AND ((tlinfo.rev_account_cd = x_rev_account_cd)
2869 OR ((tlinfo.rev_account_cd IS NULL)
2870 AND (x_rev_account_cd IS NULL)))
2871 AND ((tlinfo.primary_program_rank = x_primary_program_rank)
2872 OR ((tlinfo.primary_program_rank IS NULL)
2873 AND (x_primary_program_rank IS NULL)))
2874 AND ((tlinfo.max_wlst_per_stud = x_max_wlst_per_stud)
2875 OR ((tlinfo.max_wlst_per_stud IS NULL)
2876 AND (x_max_wlst_per_stud IS NULL)))
2877 AND ((tlinfo.annual_instruction_time = x_annual_instruction_time)
2878 OR ((tlinfo.annual_instruction_time IS NULL)
2879 AND (x_annual_instruction_time IS NULL)))
2880 ) THEN
2881 NULL;
2882 ELSE
2883 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2884 app_exception.raise_exception;
2885 END IF;
2886 RETURN;
2887 END lock_row;
2888
2889 PROCEDURE update_row (
2890 x_rowid IN VARCHAR2,
2891 x_course_cd IN VARCHAR2,
2892 x_version_number IN NUMBER,
2893 x_start_dt IN DATE,
2894 x_review_dt IN DATE,
2895 x_expiry_dt IN DATE,
2896 x_end_dt IN DATE,
2897 x_course_status IN VARCHAR2,
2898 x_title IN VARCHAR2,
2899 x_short_title IN VARCHAR2,
2900 x_abbreviation IN VARCHAR2,
2901 x_supp_exam_permitted_ind IN VARCHAR2,
2902 x_generic_course_ind IN VARCHAR2,
2903 x_graduate_students_ind IN VARCHAR2,
2904 x_count_intrmsn_in_time_ind IN VARCHAR2,
2905 x_intrmsn_allowed_ind IN VARCHAR2,
2906 x_course_type IN VARCHAR2,
2907 x_responsible_org_unit_cd IN VARCHAR2,
2908 x_responsible_ou_start_dt IN DATE,
2909 x_govt_special_course_type IN VARCHAR2,
2910 x_qualification_recency IN NUMBER,
2911 x_external_adv_stnd_limit IN NUMBER,
2912 x_internal_adv_stnd_limit IN NUMBER,
2913 x_contact_hours IN NUMBER,
2914 x_credit_points_required IN NUMBER,
2915 x_govt_course_load IN NUMBER,
2916 x_std_annual_load IN NUMBER,
2917 x_course_total_eftsu IN NUMBER,
2918 x_max_intrmsn_duration IN NUMBER,
2919 x_num_of_units_before_intrmsn IN NUMBER,
2920 x_min_sbmsn_percentage IN NUMBER,
2921 x_max_cp_per_teaching_period IN NUMBER,
2922 x_approval_date IN DATE,
2923 x_external_approval_date IN DATE,
2924 x_residency_cp_required IN NUMBER,
2925 x_state_financial_aid IN VARCHAR2,
2926 x_federal_financial_aid IN VARCHAR2,
2927 x_institutional_financial_aid IN VARCHAR2,
2928 x_attribute_category IN VARCHAR2,
2929 x_attribute1 IN VARCHAR2,
2930 x_attribute2 IN VARCHAR2,
2931 x_attribute3 IN VARCHAR2,
2932 x_attribute4 IN VARCHAR2,
2933 x_attribute5 IN VARCHAR2,
2934 x_attribute6 IN VARCHAR2,
2935 x_attribute7 IN VARCHAR2,
2936 x_attribute8 IN VARCHAR2,
2937 x_attribute9 IN VARCHAR2,
2938 x_attribute10 IN VARCHAR2,
2939 x_attribute11 IN VARCHAR2,
2940 x_attribute12 IN VARCHAR2,
2941 x_attribute13 IN VARCHAR2,
2942 x_attribute14 IN VARCHAR2,
2943 x_attribute15 IN VARCHAR2,
2944 x_attribute16 IN VARCHAR2,
2945 x_attribute17 IN VARCHAR2,
2946 x_attribute18 IN VARCHAR2,
2947 x_attribute19 IN VARCHAR2,
2948 x_attribute20 IN VARCHAR2,
2949 x_min_cp_per_calendar IN NUMBER,
2950 x_mode IN VARCHAR2,
2951 x_rev_account_cd IN VARCHAR2 ,
2952 x_primary_program_rank IN NUMBER,
2953 x_max_wlst_per_stud IN NUMBER,
2954 x_annual_instruction_time IN NUMBER
2955 ) AS
2956 /************************************************************************
2957 Created By :
2958 Date Created By :
2959 Purpose :
2960 Known limitations, enhancements or remarks:
2961 Change History :
2962 Who When What
2963 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
2964 as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22 warning.
2965 smadathi 10-MAY-2001 Changed for New DLD Version
2966 sbeerell 10-MAY-2000 Changed for new DLD version 2
2967 (reverse chronological order - newest change first)
2968 *************************************************************************/
2969 x_last_update_date DATE;
2970 x_last_updated_by NUMBER;
2971 x_last_update_login NUMBER;
2972 BEGIN
2973 x_last_update_date := SYSDATE;
2974 IF(x_mode = 'I') THEN
2975 x_last_updated_by := 1;
2976 x_last_update_login := 0;
2977 ELSIF (x_mode = 'R') THEN
2978 x_last_updated_by := fnd_global.user_id;
2979 IF x_last_updated_by IS NULL THEN
2980 x_last_updated_by := -1;
2981 END IF;
2982 x_last_update_login :=fnd_global.login_id;
2983 IF x_last_update_login IS NULL THEN
2984 x_last_update_login := -1;
2985 END IF;
2986 ELSE
2987 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
2988 igs_ge_msg_stack.add;
2989 app_exception.raise_exception;
2990 END IF;
2991
2992 before_dml( p_action => 'UPDATE',
2993 x_rowid => x_rowid,
2994 x_course_cd => x_course_cd,
2995 x_version_number => x_version_number,
2996 x_start_dt => x_start_dt,
2997 x_review_dt => x_review_dt,
2998 x_expiry_dt => x_expiry_dt,
2999 x_end_dt => x_end_dt,
3000 x_course_status => x_course_status,
3001 x_title => x_title,
3002 x_short_title => x_short_title,
3003 x_abbreviation => x_abbreviation,
3004 x_supp_exam_permitted_ind => x_supp_exam_permitted_ind,
3005 x_generic_course_ind => x_generic_course_ind,
3006 x_graduate_students_ind => x_graduate_students_ind,
3007 x_count_intrmsn_in_time_ind => x_count_intrmsn_in_time_ind,
3008 x_intrmsn_allowed_ind => x_intrmsn_allowed_ind,
3009 x_course_type => x_course_type,
3010 x_responsible_org_unit_cd => x_responsible_org_unit_cd,
3011 x_responsible_ou_start_dt => x_responsible_ou_start_dt,
3012 x_govt_special_course_type => x_govt_special_course_type,
3013 x_qualification_recency => x_qualification_recency,
3014 x_external_adv_stnd_limit => x_external_adv_stnd_limit,
3015 x_internal_adv_stnd_limit => x_internal_adv_stnd_limit,
3016 x_contact_hours => x_contact_hours,
3017 x_credit_points_required => x_credit_points_required,
3018 x_govt_course_load => x_govt_course_load,
3019 x_std_annual_load => x_std_annual_load,
3020 x_course_total_eftsu => x_course_total_eftsu,
3021 x_max_intrmsn_duration => x_max_intrmsn_duration,
3022 x_num_of_units_before_intrmsn => x_num_of_units_before_intrmsn,
3023 x_min_sbmsn_percentage=>x_min_sbmsn_percentage,
3024 x_max_cp_per_teaching_period=>x_max_cp_per_teaching_period,
3025 x_approval_date=>x_approval_date,
3026 x_external_approval_date=>x_external_approval_date,
3027 x_residency_cp_required=>x_residency_cp_required,
3028 x_state_financial_aid=>x_state_financial_aid,
3029 x_federal_financial_aid=>x_federal_financial_aid,
3030 x_institutional_financial_aid=>x_institutional_financial_aid,
3031 x_attribute_category=>x_attribute_category,
3032 x_attribute1=>x_attribute1,
3033 x_attribute2=>x_attribute2,
3034 x_attribute3=>x_attribute3,
3035 x_attribute4=>x_attribute4,
3036 x_attribute5=>x_attribute5,
3037 x_attribute6=>x_attribute6,
3038 x_attribute7=>x_attribute7,
3039 x_attribute8=>x_attribute8,
3040 x_attribute9=>x_attribute9,
3041 x_attribute10=>x_attribute10,
3042 x_attribute11=>x_attribute11,
3043 x_attribute12=>x_attribute12,
3044 x_attribute13=>x_attribute13,
3045 x_attribute14=>x_attribute14,
3046 x_attribute15=>x_attribute15,
3047 x_attribute16=>x_attribute16,
3048 x_attribute17=>x_attribute17,
3049 x_attribute18=>x_attribute18,
3050 x_attribute19=>x_attribute19,
3051 x_attribute20=>x_attribute20,
3052 x_min_cp_per_calendar=>x_min_cp_per_calendar,
3053 x_creation_date => x_last_update_date,
3054 x_created_by => x_last_updated_by,
3055 x_last_update_date => x_last_update_date,
3056 x_last_updated_by => x_last_updated_by,
3057 x_last_update_login => x_last_update_login,
3058 x_rev_account_cd => x_rev_account_cd,
3059 x_primary_program_rank => x_primary_program_rank,
3060 x_max_wlst_per_stud => x_max_wlst_per_stud,
3061 x_annual_instruction_time => x_annual_instruction_time
3062 );
3063 UPDATE igs_ps_ver_all SET
3064 graduate_students_ind = new_references.graduate_students_ind,
3065 count_intrmsn_in_time_ind = new_references.count_intrmsn_in_time_ind,
3066 intrmsn_allowed_ind = new_references.intrmsn_allowed_ind,
3067 course_type = new_references.course_type,
3068 responsible_org_unit_cd = new_references.responsible_org_unit_cd,
3069 responsible_ou_start_dt = new_references.responsible_ou_start_dt,
3070 govt_special_course_type = new_references.govt_special_course_type,
3071 qualification_recency = new_references.qualification_recency,
3072 external_adv_stnd_limit = new_references.external_adv_stnd_limit,
3073 internal_adv_stnd_limit = new_references.internal_adv_stnd_limit,
3074 contact_hours = new_references.contact_hours,
3075 credit_points_required = new_references.credit_points_required,
3076 govt_course_load = new_references.govt_course_load,
3077 std_annual_load = new_references.std_annual_load,
3078 course_total_eftsu = new_references.course_total_eftsu,
3079 max_intrmsn_duration = new_references.max_intrmsn_duration,
3080 num_of_units_before_intrmsn = new_references.num_of_units_before_intrmsn,
3081 min_sbmsn_percentage = new_references.min_sbmsn_percentage,
3082 start_dt = new_references.start_dt,
3083 review_dt = new_references.review_dt,
3084 expiry_dt = new_references.expiry_dt,
3085 end_dt = new_references.end_dt,
3086 course_status = new_references.course_status,
3087 title = new_references.title,
3088 short_title = new_references.short_title,
3089 abbreviation = new_references.abbreviation,
3090 supp_exam_permitted_ind = new_references.supp_exam_permitted_ind,
3091 generic_course_ind = new_references.generic_course_ind,
3092 max_cp_per_teaching_period = new_references.max_cp_per_teaching_period,
3093 approval_date = new_references.approval_date,
3094 external_approval_date = new_references.external_approval_date,
3095 residency_cp_required = new_references.residency_cp_required,
3096 state_financial_aid = new_references.state_financial_aid,
3097 federal_financial_aid = new_references.federal_financial_aid,
3098 institutional_financial_aid = new_references.institutional_financial_aid,
3099 attribute_category = new_references.attribute_category,
3100 attribute1 = new_references.attribute1,
3101 attribute2 = new_references.attribute2,
3102 attribute3 = new_references.attribute3,
3103 attribute4 = new_references.attribute4,
3104 attribute5 = new_references.attribute5,
3105 attribute6 = new_references.attribute6,
3106 attribute7 = new_references.attribute7,
3107 attribute8 = new_references.attribute8,
3108 attribute9 = new_references.attribute9,
3109 attribute10 = new_references.attribute10,
3110 attribute11 = new_references.attribute11,
3111 attribute12 = new_references.attribute12,
3112 attribute13 = new_references.attribute13,
3113 attribute14 = new_references.attribute14,
3114 attribute15 = new_references.attribute15,
3115 attribute16 = new_references.attribute16,
3116 attribute17 = new_references.attribute17,
3117 attribute18 = new_references.attribute18,
3118 attribute19 = new_references.attribute19,
3119 attribute20 = new_references.attribute20,
3120 min_cp_per_calendar = new_references.min_cp_per_calendar,
3121 last_update_date = x_last_update_date,
3122 last_updated_by = x_last_updated_by,
3123 last_update_login = x_last_update_login,
3124 rev_account_cd = x_rev_account_cd,
3125 primary_program_rank = new_references.primary_program_rank,
3126 max_wlst_per_stud = new_references.max_wlst_per_stud,
3127 annual_instruction_time = new_references.annual_instruction_time
3128
3129 WHERE ROWID = x_rowid
3130 ;
3131 IF (SQL%NOTFOUND) THEN
3132 RAISE NO_DATA_FOUND;
3133 END IF;
3134 after_dml(
3135 p_action => 'UPDATE',
3136 x_rowid => x_rowid
3137 );
3138 END update_row;
3139
3140 PROCEDURE add_row (
3141 x_rowid IN OUT NOCOPY VARCHAR2,
3142 x_course_cd IN VARCHAR2,
3143 x_version_number IN NUMBER,
3144 x_start_dt IN DATE,
3145 x_review_dt IN DATE,
3146 x_expiry_dt IN DATE,
3147 x_end_dt IN DATE,
3148 x_course_status IN VARCHAR2,
3149 x_title IN VARCHAR2,
3150 x_short_title IN VARCHAR2,
3151 x_abbreviation IN VARCHAR2,
3152 x_supp_exam_permitted_ind IN VARCHAR2,
3153 x_generic_course_ind IN VARCHAR2,
3154 x_graduate_students_ind IN VARCHAR2,
3155 x_count_intrmsn_in_time_ind IN VARCHAR2,
3156 x_intrmsn_allowed_ind IN VARCHAR2,
3157 x_course_type IN VARCHAR2,
3158 x_responsible_org_unit_cd IN VARCHAR2,
3159 x_responsible_ou_start_dt IN DATE,
3160 x_govt_special_course_type IN VARCHAR2,
3161 x_qualification_recency IN NUMBER,
3162 x_external_adv_stnd_limit IN NUMBER,
3163 x_internal_adv_stnd_limit IN NUMBER,
3164 x_contact_hours IN NUMBER,
3165 x_credit_points_required IN NUMBER,
3166 x_govt_course_load IN NUMBER,
3167 x_std_annual_load IN NUMBER,
3168 x_course_total_eftsu IN NUMBER,
3169 x_max_intrmsn_duration IN NUMBER,
3170 x_num_of_units_before_intrmsn IN NUMBER,
3171 x_min_sbmsn_percentage IN NUMBER,
3172 x_max_cp_per_teaching_period IN NUMBER,
3173 x_approval_date IN DATE,
3174 x_external_approval_date IN DATE,
3175 x_residency_cp_required IN NUMBER,
3176 x_state_financial_aid IN VARCHAR2,
3177 x_federal_financial_aid IN VARCHAR2,
3178 x_institutional_financial_aid IN VARCHAR2,
3179 x_attribute_category IN VARCHAR2,
3180 x_attribute1 IN VARCHAR2,
3181 x_attribute2 IN VARCHAR2,
3182 x_attribute3 IN VARCHAR2,
3183 x_attribute4 IN VARCHAR2,
3184 x_attribute5 IN VARCHAR2,
3185 x_attribute6 IN VARCHAR2,
3186 x_attribute7 IN VARCHAR2,
3187 x_attribute8 IN VARCHAR2,
3188 x_attribute9 IN VARCHAR2,
3189 x_attribute10 IN VARCHAR2,
3190 x_attribute11 IN VARCHAR2,
3191 x_attribute12 IN VARCHAR2,
3192 x_attribute13 IN VARCHAR2,
3193 x_attribute14 IN VARCHAR2,
3194 x_attribute15 IN VARCHAR2,
3195 x_attribute16 IN VARCHAR2,
3196 x_attribute17 IN VARCHAR2,
3197 x_attribute18 IN VARCHAR2,
3198 x_attribute19 IN VARCHAR2,
3199 x_attribute20 IN VARCHAR2,
3200 x_min_cp_per_calendar IN NUMBER,
3201 x_mode IN VARCHAR2,
3202 x_org_id IN NUMBER,
3203 x_rev_account_cd IN VARCHAR2 ,
3204 x_primary_program_rank IN NUMBER,
3205 x_max_wlst_per_stud IN NUMBER,
3206 x_annual_instruction_time IN NUMBER
3207 ) AS
3208 /************************************************************************
3209 Created By :
3210 Date Created By :
3211 Purpose :
3212 Known limitations, enhancements or remarks:
3213 Change History :
3214 Who When What
3215 vvutukur 18-Oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time,
3216 as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22 warning.
3217 smadathi 10-MAY-2001 Changed for New DLD Version
3218 sbeerell 10-MAY-2000 Changed for new DLD version 2
3219 (reverse chronological order - newest change first)
3220 *************************************************************************/
3221 CURSOR c1 IS SELECT ROWID FROM igs_ps_ver_all
3222 WHERE course_cd = x_course_cd
3223 AND version_number = x_version_number ;
3224 BEGIN
3225 OPEN c1;
3226 FETCH c1 INTO x_rowid;
3227 IF (c1%NOTFOUND) THEN
3228 CLOSE c1;
3229 insert_row (
3230 x_rowid,
3231 x_course_cd,
3232 x_version_number,
3233 x_start_dt,
3234 x_review_dt,
3235 x_expiry_dt,
3236 x_end_dt,
3237 x_course_status,
3238 x_title,
3239 x_short_title,
3240 x_abbreviation,
3241 x_supp_exam_permitted_ind,
3242 x_generic_course_ind,
3243 x_graduate_students_ind,
3244 x_count_intrmsn_in_time_ind,
3245 x_intrmsn_allowed_ind,
3246 x_course_type,
3247 x_responsible_org_unit_cd,
3248 x_responsible_ou_start_dt,
3249 x_govt_special_course_type,
3250 x_qualification_recency,
3251 x_external_adv_stnd_limit,
3252 x_internal_adv_stnd_limit,
3253 x_contact_hours,
3254 x_credit_points_required,
3255 x_govt_course_load,
3256 x_std_annual_load,
3257 x_course_total_eftsu,
3258 x_max_intrmsn_duration,
3259 x_num_of_units_before_intrmsn,
3260 x_min_sbmsn_percentage,
3261 x_max_cp_per_teaching_period,
3262 x_approval_date,
3263 x_external_approval_date,
3264 x_residency_cp_required,
3265 x_state_financial_aid,
3266 x_federal_financial_aid,
3267 x_institutional_financial_aid,
3268 x_attribute_category,
3269 x_attribute1,
3270 x_attribute2,
3271 x_attribute3,
3272 x_attribute4,
3273 x_attribute5,
3274 x_attribute6,
3275 x_attribute7,
3276 x_attribute8,
3277 x_attribute9,
3278 x_attribute10,
3279 x_attribute11,
3280 x_attribute12,
3281 x_attribute13,
3282 x_attribute14,
3283 x_attribute15,
3284 x_attribute16,
3285 x_attribute17,
3286 x_attribute18,
3287 x_attribute19,
3288 x_attribute20,
3289 x_min_cp_per_calendar,
3290 x_mode,
3291 x_org_id,
3292 x_rev_account_cd,
3293 x_primary_program_rank,
3294 x_max_wlst_per_stud,
3295 x_annual_instruction_time);
3296 RETURN;
3297 END IF;
3298 CLOSE c1;
3299 update_row (
3300 x_rowid,
3301 x_course_cd,
3302 x_version_number,
3303 x_start_dt,
3304 x_review_dt,
3305 x_expiry_dt,
3306 x_end_dt,
3307 x_course_status,
3308 x_title,
3309 x_short_title,
3310 x_abbreviation,
3311 x_supp_exam_permitted_ind,
3312 x_generic_course_ind,
3313 x_graduate_students_ind,
3314 x_count_intrmsn_in_time_ind,
3315 x_intrmsn_allowed_ind,
3316 x_course_type,
3317 x_responsible_org_unit_cd,
3318 x_responsible_ou_start_dt,
3319 x_govt_special_course_type,
3320 x_qualification_recency,
3321 x_external_adv_stnd_limit,
3322 x_internal_adv_stnd_limit,
3323 x_contact_hours,
3324 x_credit_points_required,
3325 x_govt_course_load,
3326 x_std_annual_load,
3327 x_course_total_eftsu,
3328 x_max_intrmsn_duration,
3329 x_num_of_units_before_intrmsn,
3330 x_min_sbmsn_percentage,
3331 x_max_cp_per_teaching_period,
3332 x_approval_date,
3333 x_external_approval_date,
3334 x_residency_cp_required,
3335 x_state_financial_aid,
3336 x_federal_financial_aid,
3337 x_institutional_financial_aid,
3338 x_attribute_category,
3339 x_attribute1,
3340 x_attribute2,
3341 x_attribute3,
3342 x_attribute4,
3343 x_attribute5,
3344 x_attribute6,
3345 x_attribute7,
3346 x_attribute8,
3347 x_attribute9,
3348 x_attribute10,
3349 x_attribute11,
3350 x_attribute12,
3351 x_attribute13,
3352 x_attribute14,
3353 x_attribute15,
3354 x_attribute16,
3355 x_attribute17,
3356 x_attribute18,
3357 x_attribute19,
3358 x_attribute20,
3359 x_min_cp_per_calendar,
3360 x_mode,
3361 x_rev_account_cd,
3362 x_primary_program_rank,
3363 x_max_wlst_per_stud,
3364 x_annual_instruction_time
3365 );
3366 END add_row;
3367
3368 PROCEDURE delete_row (
3369 x_rowid IN VARCHAR2
3370 ) AS
3371 /************************************************************************
3372 Created By :
3373 Date Created By :
3374 Purpose :
3375 Known limitations, enhancements or remarks:
3376 Change History :
3377 Who When What
3378 sbeerell 10-MAY-2000 Changed for new DLD version 2
3379 (reverse chronological order - newest change first)
3380 *************************************************************************/
3381 BEGIN
3382 before_dml( p_action => 'DELETE',
3383 x_rowid => x_rowid
3384 );
3385 DELETE FROM igs_ps_ver_all
3386 WHERE ROWID = x_rowid;
3387 IF (SQL%NOTFOUND) THEN
3388 RAISE NO_DATA_FOUND;
3389 END IF;
3390 after_dml(
3391 p_action => 'DELETE',
3392 x_rowid => x_rowid
3393 );
3394 END delete_row;
3395 END igs_ps_ver_pkg;