[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_VER_HIST_PKG
Source
1 package body IGS_PS_VER_HIST_PKG AS
2 /* $Header: IGSPI43B.pls 120.0 2005/06/01 16:14:29 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_VER_HIST_ALL%RowType;
6 new_references IGS_PS_VER_HIST_ALL%RowType;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_course_cd IN VARCHAR2 ,
12 x_version_number IN NUMBER ,
13 x_hist_start_dt IN DATE ,
14 x_hist_end_dt IN DATE ,
15 x_hist_who IN NUMBER ,
16 x_start_dt IN DATE ,
17 x_review_dt IN DATE ,
18 x_expiry_dt IN DATE ,
19 x_end_dt IN DATE ,
20 x_course_status IN VARCHAR2 ,
21 x_title IN VARCHAR2 ,
22 x_short_title IN VARCHAR2 ,
23 x_abbreviation IN VARCHAR2 ,
24 x_supp_exam_permitted_ind IN VARCHAR2 ,
25 x_generic_course_ind IN VARCHAR2 ,
26 x_graduate_students_ind IN VARCHAR2 ,
27 x_count_intrmsn_in_time_ind IN VARCHAR2 ,
28 x_intrmsn_allowed_ind IN VARCHAR2 ,
29 x_course_type IN VARCHAR2 ,
30 x_ct_description IN VARCHAR2 ,
31 x_responsible_org_unit_cd IN VARCHAR2 ,
32 x_responsible_ou_start_dt IN DATE ,
33 x_ou_description IN VARCHAR2 ,
34 x_govt_special_course_type IN VARCHAR2 ,
35 x_gsct_description IN VARCHAR2 ,
36 x_qualification_recency IN NUMBER ,
37 x_external_adv_stnd_limit IN NUMBER ,
38 x_internal_adv_stnd_limit IN NUMBER ,
39 x_contact_hours IN NUMBER ,
40 x_credit_points_required IN NUMBER ,
41 x_govt_course_load IN NUMBER ,
42 x_std_annual_load IN NUMBER ,
43 x_course_total_eftsu IN NUMBER ,
44 x_max_intrmsn_duration IN NUMBER ,
45 x_num_of_units_before_intrmsn IN NUMBER ,
46 x_min_sbmsn_percentage IN NUMBER ,
47 x_min_cp_per_calendar IN NUMBER ,
48 x_approval_date IN DATE ,
49 x_external_approval_date IN DATE ,
50 x_federal_financial_aid IN VARCHAR2 ,
51 x_institutional_financial_aid IN VARCHAR2 ,
52 x_max_cp_per_teaching_period IN NUMBER ,
53 x_residency_cp_required IN NUMBER ,
54 x_state_financial_aid IN VARCHAR2 ,
55 x_primary_program_rank IN NUMBER ,
56 x_max_wlst_per_stud IN NUMBER,
57 x_creation_date IN DATE ,
58 x_created_by IN NUMBER ,
59 x_last_update_date IN DATE ,
60 x_last_updated_by IN NUMBER ,
61 x_last_update_login IN NUMBER ,
62 x_org_id IN NUMBER ,
63 x_annual_instruction_time IN NUMBER
64 ) AS
65 /*----------------------------------------------------------------------------
66 || Created By :
67 || Created On :
68 || Purpose :
69 || Known limitations, enhancements or remarks :
70 || Change History :
71 || Who When What
72 || (reverse chronological order - newest change first)
73 || vvutukur 19_oct-2002 Enh#2608227.Removed references to std_ft_completion_time,std_pt_completion_time
74 || as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22
75 || warning.
76 ----------------------------------------------------------------------------*/
77
78 CURSOR cur_old_ref_values IS
79 SELECT *
80 FROM IGS_PS_VER_HIST_ALL
81 WHERE rowid = x_rowid;
82
83 BEGIN
84
85 l_rowid := x_rowid;
86
87 -- Code for setting the Old and New Reference Values.
88 -- Populate Old Values.
89 Open cur_old_ref_values;
90 Fetch cur_old_ref_values INTO old_references;
91 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
92 Close cur_old_ref_values;
93 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96 Return;
97 END IF;
98 Close cur_old_ref_values;
99
100 -- Populate New Values.
101 new_references.course_cd := x_course_cd;
102 new_references.version_number := x_version_number;
103 new_references.hist_start_dt := x_hist_start_dt;
104 new_references.hist_end_dt := x_hist_end_dt;
105 new_references.hist_who := x_hist_who;
106 new_references.start_dt := x_start_dt;
107 new_references.review_dt := x_review_dt;
108 new_references.expiry_dt := x_expiry_dt;
109 new_references.end_dt := x_end_dt;
110 new_references.course_status := x_course_status;
111 new_references.title := x_title;
112 new_references.short_title := x_short_title;
113 new_references.abbreviation := x_abbreviation;
114 new_references.supp_exam_permitted_ind := x_supp_exam_permitted_ind;
115 new_references.generic_course_ind := x_generic_course_ind;
116 new_references.graduate_students_ind := x_graduate_students_ind;
117 new_references.count_intrmsn_in_time_ind := x_count_intrmsn_in_time_ind;
118 new_references.intrmsn_allowed_ind := x_intrmsn_allowed_ind;
119 new_references.course_type := x_course_type;
120 new_references.ct_description := x_ct_description;
121 new_references.responsible_org_unit_cd := x_responsible_org_unit_cd;
122 new_references.responsible_ou_start_dt := x_responsible_ou_start_dt;
123 new_references.ou_description := x_ou_description;
124 new_references.govt_special_course_type := x_govt_special_course_type;
125 new_references.gsct_description := x_gsct_description;
126 new_references.qualification_recency := x_qualification_recency;
127 new_references.external_adv_stnd_limit := x_external_adv_stnd_limit;
128 new_references.internal_adv_stnd_limit := x_internal_adv_stnd_limit;
129 new_references.contact_hours := x_contact_hours;
130 new_references.credit_points_required := x_credit_points_required;
131 new_references.govt_course_load := x_govt_course_load;
132 new_references.std_annual_load := x_std_annual_load;
133 new_references.course_total_eftsu := x_course_total_eftsu;
134 new_references.max_intrmsn_duration := x_max_intrmsn_duration;
135 new_references.num_of_units_before_intrmsn := x_num_of_units_before_intrmsn;
136 new_references.min_sbmsn_percentage := x_min_sbmsn_percentage;
137 new_references.min_cp_per_calendar := x_min_cp_per_calendar;
138 new_references.approval_date := x_approval_date;
139 new_references.external_approval_date := x_external_approval_date;
140 new_references.federal_financial_aid := x_federal_financial_aid;
141 new_references.institutional_financial_aid := x_institutional_financial_aid;
142 new_references.max_cp_per_teaching_period := x_max_cp_per_teaching_period;
143 new_references.residency_cp_required := x_residency_cp_required;
144 new_references.state_financial_aid := x_state_financial_aid;
145 new_references.primary_program_rank := x_primary_program_rank;
146 new_references.max_wlst_per_stud := x_max_wlst_per_stud;
147 new_references.annual_instruction_time := x_annual_instruction_time;
148 new_references.org_id:=x_org_id;
149 IF (p_action = 'UPDATE') THEN
150 new_references.creation_date := old_references.creation_date;
151 new_references.created_by := old_references.created_by;
152 ELSE
153 new_references.creation_date := x_creation_date;
154 new_references.created_by := x_created_by;
155 END IF;
156 new_references.last_update_date := x_last_update_date;
157 new_references.last_updated_by := x_last_updated_by;
158 new_references.last_update_login := x_last_update_login;
159
160 END set_column_values;
161
162 PROCEDURE check_constraints (
163 column_name IN VARCHAR2 ,
164 column_value IN VARCHAR2
165 ) IS
166 BEGIN
167 IF column_name is NULL THEN
168 NULL;
169 ELSIF upper(column_name) = 'GENERIC_COURSE_IND' THEN
170 new_references.generic_course_ind := column_value;
171 ELSIF upper(column_name) = 'EXTERNAL_ADV_STND_LIMIT' THEN
172 new_references.external_adv_stnd_limit := igs_ge_number.to_num(column_value);
173 ELSIF upper(column_name) = 'STD_ANNUAL_LOAD' THEN
174 new_references.std_annual_load := igs_ge_number.to_num(column_value);
175 ELSIF upper(column_name) = 'INTERNAL_ADV_STND_LIMIT' THEN
176 new_references.internal_adv_stnd_limit := igs_ge_number.to_num(column_value);
177 ELSIF upper(column_name) = 'INTRMSN_ALLOWED_IND' THEN
178 new_references.intrmsn_allowed_ind := column_value;
179 ELSIF upper(column_name) = 'GRADUATE_STUDENTS_IND' THEN
180 new_references.graduate_students_ind:= column_value;
181 ELSIF upper(column_name) = 'ABBREVIATION' THEN
182 new_references.abbreviation := column_value;
183 ELSIF upper(column_name) = 'COUNT_INTRMSN_IN_TIME_IND' THEN
184 new_references.count_intrmsn_in_time_ind := column_value;
185 ELSIF upper(column_name) = 'COURSE_CD' THEN
186 new_references.course_cd := column_value;
187 ELSIF upper(column_name) = 'COURSE_STATUS' THEN
188 new_references.course_status := column_value;
189 ELSIF upper(column_name) = 'COURSE_TYPE' THEN
190 new_references.course_type := column_value;
191 ELSIF upper(column_name) = 'GOVT_SPECIAL_COURSE_TYPE' THEN
192 new_references.govt_special_course_type := column_value;
193 ELSIF upper(column_name) = 'SUPP_EXAM_PERMITTED_IND' THEN
194 new_references.supp_exam_permitted_ind := column_value;
195 ELSIF UPPER(column_name) = 'MAX_WLST_PER_STUD' THEN
196 new_references.max_wlst_per_stud := column_value;
197 ELSIF UPPER(column_name) = 'ANNUAL_INSTRUCTION_TIME' THEN
198 new_references.annual_instruction_time := column_value;
199 END IF;
200 IF upper(column_name)= 'GENERIC_COURSE_IND' OR
201 column_name is null THEN
202 IF new_references.generic_course_ind NOT IN ( 'Y' , 'N' )
203 THEN
204 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208 END IF;
209 IF upper(column_name)= 'COUNT_INTRMSN_IN_TIME_IND' OR
210 column_name is null THEN
211 IF new_references.count_intrmsn_in_time_ind NOT IN ( 'Y' , 'N' )
212 THEN
213 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
214 IGS_GE_MSG_STACK.ADD;
215 App_Exception.Raise_Exception;
216 END IF;
217 END IF;
218 IF upper(column_name)= 'EXTERNAL_ADV_STND_LIMIT' OR
219 column_name is null THEN
220 IF new_references.external_adv_stnd_limit < 0 OR
221 new_references.external_adv_stnd_limit > 9999.999
222 THEN
223 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
224 IGS_GE_MSG_STACK.ADD;
225 App_Exception.Raise_Exception;
226 END IF;
227 END IF;
228 IF upper(column_name)= 'STD_ANNUAL_LOAD' OR
229 column_name is null THEN
230 IF new_references.std_annual_load < 0 OR
231 new_references.std_annual_load > 9999.999
232 THEN
233 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IF;
237 END IF;
238 IF upper(column_name)= 'INTERNAL_ADV_STND_LIMIT' OR
239 column_name is null THEN
240 IF new_references.internal_adv_stnd_limit < 0 OR
241 new_references.internal_adv_stnd_limit > 9999.999
242 THEN
243 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248 IF upper(column_name)= 'INTRMSN_ALLOWED_IND' OR
249 column_name is null THEN
250 IF new_references.intrmsn_allowed_ind NOT IN ( 'Y' , 'N' )
251 THEN
252 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256 END IF;
257 IF upper(column_name)= 'GRADUATE_STUDENTS_IND' OR
258 column_name is null THEN
259 IF new_references.graduate_students_ind NOT IN ( 'Y' , 'N' )
260 THEN
261 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
262 IGS_GE_MSG_STACK.ADD;
263 App_Exception.Raise_Exception;
264 END IF;
265 END IF;
266 IF upper(column_name)= 'ABBREVIATION' OR
267 column_name is null THEN
268 IF new_references.abbreviation<> UPPER(new_references.abbreviation)
269 THEN
270 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END IF;
274 END IF;
275
276 IF upper(column_name)= 'COURSE_CD' OR
277 column_name is null THEN
278 IF new_references.course_cd <> UPPER(new_references.course_cd )
279 THEN
280 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END IF;
284 END IF;
285
286 IF upper(column_name)= 'COURSE_STATUS' OR
287 column_name is null THEN
288 IF new_references.course_status <> UPPER(new_references.course_status)
289 THEN
290 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 END IF;
294 END IF;
295
296 IF upper(column_name)= 'COURSE_TYPE' OR
297 column_name is null THEN
298 IF new_references.course_type <> UPPER(new_references.course_type)
299 THEN
300 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 END IF;
304 END IF;
305 IF upper(column_name)= 'GOVT_SPECIAL_COURSE_TYPE' OR
306 column_name is null THEN
307 IF new_references.govt_special_course_type <> UPPER(new_references.govt_special_course_type )
308 THEN
309 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 END IF;
313 END IF;
314
315 IF upper(column_name)= 'SUPP_EXAM_PERMITTED_IND' OR
316 column_name is null THEN
317 IF new_references.supp_exam_permitted_ind <> UPPER(new_references.supp_exam_permitted_ind )
318 THEN
319 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323 END IF;
324
325 IF upper(column_name)= 'MAX_WLST_PER_STUD' OR column_name is null THEN
326 IF new_references.max_wlst_per_stud < 0 OR
327 new_references.max_wlst_per_stud > 9999 THEN
328 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333
334 IF upper(column_name)= 'ANNUAL_INSTRUCTION_TIME' OR column_name is null THEN
335 IF new_references.annual_instruction_time < 1 OR
336 new_references.annual_instruction_time > 99.99 THEN
337 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
338 IGS_GE_MSG_STACK.ADD;
339 App_Exception.Raise_Exception;
340 END IF;
341 END IF;
342
343
344 END check_constraints;
345
346 FUNCTION get_pk_for_validation (
347 x_course_cd IN VARCHAR2,
348 x_version_number IN NUMBER,
349 x_hist_start_dt IN DATE
350 ) RETURN BOOLEAN AS
351
352 CURSOR cur_rowid IS
353 SELECT rowid
354 FROM IGS_PS_VER_HIST_ALL
355 WHERE course_cd = x_course_cd
356 AND version_number = x_version_number
357 AND hist_start_dt = x_hist_start_dt
358 FOR UPDATE NOWAIT;
359
360 lv_rowid cur_rowid%RowType;
361
362 BEGIN
363
364 OPEN cur_rowid;
365 FETCH cur_rowid INTO lv_rowid;
366 IF (cur_rowid%FOUND) THEN
367 Close cur_rowid;
368 Return(TRUE);
369 ELSE
370 Close cur_rowid;
371 Return(FALSE);
372 END IF;
373
374 END get_pk_for_validation;
375
376 PROCEDURE before_dml (
377 p_action IN VARCHAR2,
378 x_rowid IN VARCHAR2 ,
379 x_course_cd IN VARCHAR2 ,
380 x_version_number IN NUMBER ,
381 x_hist_start_dt IN DATE ,
382 x_hist_end_dt IN DATE ,
383 x_hist_who IN NUMBER ,
384 x_start_dt IN DATE ,
385 x_review_dt IN DATE ,
386 x_expiry_dt IN DATE ,
387 x_end_dt IN DATE ,
388 x_course_status IN VARCHAR2 ,
389 x_title IN VARCHAR2 ,
390 x_short_title IN VARCHAR2 ,
391 x_abbreviation IN VARCHAR2 ,
392 x_supp_exam_permitted_ind IN VARCHAR2 ,
393 x_generic_course_ind IN VARCHAR2 ,
394 x_graduate_students_ind IN VARCHAR2 ,
395 x_count_intrmsn_in_time_ind IN VARCHAR2 ,
396 x_intrmsn_allowed_ind IN VARCHAR2 ,
397 x_course_type IN VARCHAR2 ,
398 x_ct_description IN VARCHAR2 ,
399 x_responsible_org_unit_cd IN VARCHAR2 ,
400 x_responsible_ou_start_dt IN DATE ,
401 x_ou_description IN VARCHAR2 ,
402 x_govt_special_course_type IN VARCHAR2 ,
403 x_gsct_description IN VARCHAR2 ,
404 x_qualification_recency IN NUMBER ,
405 x_external_adv_stnd_limit IN NUMBER ,
406 x_internal_adv_stnd_limit IN NUMBER ,
407 x_contact_hours IN NUMBER ,
408 x_credit_points_required IN NUMBER ,
409 x_govt_course_load IN NUMBER ,
410 x_std_annual_load IN NUMBER ,
411 x_course_total_eftsu IN NUMBER ,
412 x_max_intrmsn_duration IN NUMBER ,
413 x_num_of_units_before_intrmsn IN NUMBER ,
414 x_min_sbmsn_percentage IN NUMBER ,
415 x_min_cp_per_calendar IN NUMBER ,
416 x_approval_date IN DATE ,
417 x_external_approval_date IN DATE ,
418 x_federal_financial_aid IN VARCHAR2 ,
419 x_institutional_financial_aid IN VARCHAR2 ,
420 x_max_cp_per_teaching_period IN NUMBER ,
421 x_residency_cp_required IN NUMBER ,
422 x_state_financial_aid IN VARCHAR2 ,
423 x_primary_program_rank IN NUMBER ,
424 x_max_wlst_per_stud IN NUMBER,
425 x_creation_date IN DATE ,
426 x_created_by IN NUMBER ,
427 x_last_update_date IN DATE ,
428 x_last_updated_by IN NUMBER ,
429 x_last_update_login IN NUMBER ,
430 x_org_id IN NUMBER ,
431 x_annual_instruction_time IN NUMBER
432 ) AS
433 /*----------------------------------------------------------------------------
434 || Created By :
435 || Created On :
436 || Purpose :
437 || Known limitations, enhancements or remarks :
438 || Change History :
439 || Who When What
440 || (reverse chronological order - newest change first)
441 || vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
442 || as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22
443 || warnings.
444 ----------------------------------------------------------------------------*/
445 BEGIN
446
447 set_column_values (
448 p_action,
449 x_rowid,
450 x_course_cd,
451 x_version_number,
452 x_hist_start_dt,
453 x_hist_end_dt,
454 x_hist_who,
455 x_start_dt,
456 x_review_dt,
457 x_expiry_dt,
458 x_end_dt,
459 x_course_status,
460 x_title,
461 x_short_title,
462 x_abbreviation,
463 x_supp_exam_permitted_ind,
464 x_generic_course_ind,
465 x_graduate_students_ind,
466 x_count_intrmsn_in_time_ind,
467 x_intrmsn_allowed_ind,
468 x_course_type,
469 x_ct_description,
470 x_responsible_org_unit_cd,
471 x_responsible_ou_start_dt,
472 x_ou_description,
473 x_govt_special_course_type,
474 x_gsct_description,
475 x_qualification_recency,
476 x_external_adv_stnd_limit,
477 x_internal_adv_stnd_limit,
478 x_contact_hours,
479 x_credit_points_required,
480 x_govt_course_load,
481 x_std_annual_load,
482 x_course_total_eftsu,
483 x_max_intrmsn_duration,
484 x_num_of_units_before_intrmsn,
485 x_min_sbmsn_percentage,
486 x_min_cp_per_calendar ,
487 x_approval_date ,
488 x_external_approval_date ,
489 x_federal_financial_aid,
490 x_institutional_financial_aid,
491 x_max_cp_per_teaching_period ,
492 x_residency_cp_required ,
493 x_state_financial_aid ,
494 x_primary_program_rank,
495 x_max_wlst_per_stud,
496 x_creation_date,
497 x_created_by,
498 x_last_update_date,
499 x_last_updated_by,
500 x_last_update_login,
501 x_org_id,
502 x_annual_instruction_time
503 );
504
505 IF (p_action = 'INSERT') THEN
506 -- Call all the procedures related to Before Insert.
507
508 IF Get_PK_For_Validation(
509 new_references.course_cd ,
510 new_references.version_number,
511 new_references.hist_start_dt
512 ) THEN
513 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
514 IGS_GE_MSG_STACK.ADD;
515 App_Exception.Raise_Exception;
516 END IF;
517 Check_Constraints;
518 ELSIF (p_action = 'UPDATE') THEN
519 -- Call all the procedures related to Before Update.
520
521 Check_Constraints;
522
523 ELSIF (p_action = 'VALIDATE_INSERT') THEN
524 IF Get_PK_For_Validation(
525 new_references.course_cd ,
526 new_references.version_number,
527 new_references.hist_start_dt
528 ) THEN
529 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
530 IGS_GE_MSG_STACK.ADD;
531 App_Exception.Raise_Exception;
532 END IF;
533 Check_Constraints;
534 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
535 Check_Constraints;
536
537 END IF;
538 END before_dml;
539
540 PROCEDURE after_dml (
541 p_action IN VARCHAR2,
542 x_rowid IN VARCHAR2
543 ) AS
544 BEGIN
545
546 l_rowid := x_rowid;
547
548
549 END after_dml;
550
551 PROCEDURE insert_row (
552 x_rowid IN OUT NOCOPY VARCHAR2,
553 x_course_cd IN VARCHAR2,
554 x_version_number IN NUMBER,
555 x_hist_start_dt IN DATE,
556 x_hist_end_dt IN DATE,
557 x_hist_who IN NUMBER,
558 x_start_dt IN DATE,
559 x_review_dt IN DATE,
560 x_expiry_dt IN DATE,
561 x_end_dt IN DATE,
562 x_course_status IN VARCHAR2,
563 x_title IN VARCHAR2,
564 x_short_title IN VARCHAR2,
565 x_abbreviation IN VARCHAR2,
566 x_supp_exam_permitted_ind IN VARCHAR2,
567 x_generic_course_ind IN VARCHAR2,
568 x_graduate_students_ind IN VARCHAR2,
569 x_count_intrmsn_in_time_ind IN VARCHAR2,
570 x_intrmsn_allowed_ind IN VARCHAR2,
571 x_course_type IN VARCHAR2,
572 x_ct_description IN VARCHAR2,
573 x_responsible_org_unit_cd IN VARCHAR2,
574 x_responsible_ou_start_dt IN DATE,
575 x_ou_description IN VARCHAR2,
576 x_govt_special_course_type IN VARCHAR2,
577 x_gsct_description IN VARCHAR2,
578 x_qualification_recency IN NUMBER,
579 x_external_adv_stnd_limit IN NUMBER,
580 x_internal_adv_stnd_limit IN NUMBER,
581 x_contact_hours IN NUMBER,
582 x_credit_points_required IN NUMBER,
583 x_govt_course_load IN NUMBER,
584 x_std_annual_load IN NUMBER,
585 x_course_total_eftsu IN NUMBER,
586 x_max_intrmsn_duration IN NUMBER,
587 x_num_of_units_before_intrmsn IN NUMBER,
588 x_min_sbmsn_percentage IN NUMBER,
589 x_min_cp_per_calendar IN NUMBER ,
590 x_approval_date IN DATE ,
591 x_external_approval_date IN DATE ,
592 x_federal_financial_aid IN VARCHAR2 ,
593 x_institutional_financial_aid IN VARCHAR2 ,
594 x_max_cp_per_teaching_period IN NUMBER ,
595 x_residency_cp_required IN NUMBER ,
596 x_state_financial_aid IN VARCHAR2 ,
597 x_primary_program_rank IN NUMBER ,
598 x_max_wlst_per_stud IN NUMBER,
599 x_mode IN VARCHAR2,
600 x_org_id IN NUMBER,
601 x_annual_instruction_time IN NUMBER
602 ) AS
603 /*----------------------------------------------------------------------------
604 || Created By :
605 || Created On :
606 || Purpose :
607 || Known limitations, enhancements or remarks :
608 || Change History :
609 || Who When What
610 || (reverse chronological order - newest change first)
611 || vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
612 || as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22
613 || warnings.
614 ----------------------------------------------------------------------------*/
615 CURSOR C IS SELECT ROWID FROM IGS_PS_VER_HIST_ALL
616 WHERE COURSE_CD = X_COURSE_CD
617 AND VERSION_NUMBER = X_VERSION_NUMBER
618 AND HIST_START_DT = X_HIST_START_DT;
619 X_LAST_UPDATE_DATE DATE;
620 X_LAST_UPDATED_BY NUMBER;
621 X_LAST_UPDATE_LOGIN NUMBER;
622 BEGIN
623 X_LAST_UPDATE_DATE := SYSDATE;
624 if(X_MODE = 'I') then
625 X_LAST_UPDATED_BY := 1;
626 X_LAST_UPDATE_LOGIN := 0;
627 elsif (X_MODE = 'R') then
628 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
629 if X_LAST_UPDATED_BY is NULL then
630 X_LAST_UPDATED_BY := -1;
631 end if;
632 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
633 if X_LAST_UPDATE_LOGIN is NULL then
634 X_LAST_UPDATE_LOGIN := -1;
635 end if;
636 else
637 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
638 IGS_GE_MSG_STACK.ADD;
639 app_exception.raise_exception;
640 end if;
641 before_dml( p_action => 'INSERT',
642 x_rowid => X_ROWID,
643 x_course_cd => X_COURSE_CD,
644 x_version_number => X_VERSION_NUMBER,
645 x_hist_start_dt => X_HIST_START_DT,
646 x_hist_end_dt => X_HIST_END_DT,
647 x_hist_who => X_HIST_WHO,
648 x_start_dt => X_START_DT,
649 x_review_dt => X_REVIEW_DT,
650 x_expiry_dt => X_EXPIRY_DT,
651 x_end_dt => X_END_DT,
652 x_course_status => X_COURSE_STATUS,
653 x_title => X_TITLE,
654 x_short_title => X_SHORT_TITLE,
655 x_abbreviation => X_ABBREVIATION,
656 x_supp_exam_permitted_ind => X_SUPP_EXAM_PERMITTED_IND,
657 x_generic_course_ind => NVL(X_GENERIC_COURSE_IND,'N'),
658 x_graduate_students_ind => NVL(X_GRADUATE_STUDENTS_IND,'Y'),
659 x_count_intrmsn_in_time_ind => X_COUNT_INTRMSN_IN_TIME_IND,
660 x_intrmsn_allowed_ind => X_INTRMSN_ALLOWED_IND,
661 x_course_type => X_COURSE_TYPE,
662 x_ct_description => X_CT_DESCRIPTION,
663 x_responsible_org_unit_cd => X_RESPONSIBLE_ORG_UNIT_CD,
664 x_responsible_ou_start_dt => X_RESPONSIBLE_OU_START_DT,
665 x_ou_description => X_OU_DESCRIPTION,
666 x_govt_special_course_type => X_GOVT_SPECIAL_COURSE_TYPE,
667 x_gsct_description =>X_GSCT_DESCRIPTION,
668 x_qualification_recency => X_QUALIFICATION_RECENCY,
669 x_external_adv_stnd_limit => X_EXTERNAL_ADV_STND_LIMIT,
670 x_internal_adv_stnd_limit => X_INTERNAL_ADV_STND_LIMIT,
671 x_contact_hours => X_CONTACT_HOURS,
672 x_credit_points_required => X_CREDIT_POINTS_REQUIRED,
673 x_govt_course_load => X_GOVT_COURSE_LOAD,
674 x_std_annual_load => X_STD_ANNUAL_LOAD,
675 x_course_total_eftsu => X_COURSE_TOTAL_EFTSU,
676 x_max_intrmsn_duration => X_MAX_INTRMSN_DURATION,
677 x_num_of_units_before_intrmsn => X_NUM_OF_UNITS_BEFORE_INTRMSN,
678 x_min_sbmsn_percentage => X_MIN_SBMSN_PERCENTAGE,
679 x_min_cp_per_calendar => X_MIN_CP_PER_CALENDAR,
680 x_approval_date => X_APPROVAL_DATE,
681 x_external_approval_date => X_EXTERNAL_APPROVAL_DATE,
682 x_federal_financial_aid => X_FEDERAL_FINANCIAL_AID,
683 x_institutional_financial_aid => X_INSTITUTIONAL_FINANCIAL_AID,
684 x_max_cp_per_teaching_period =>X_MAX_CP_PER_TEACHING_PERIOD,
685 x_residency_cp_required =>X_RESIDENCY_CP_REQUIRED,
686 x_state_financial_aid =>X_STATE_FINANCIAL_AID,
687 x_primary_program_rank =>X_PRIMARY_PROGRAM_RANK,
688 x_max_wlst_per_stud => x_max_wlst_per_stud,
689 x_creation_date => X_LAST_UPDATE_DATE,
690 x_created_by => X_LAST_UPDATED_BY,
691 x_last_update_date => X_LAST_UPDATE_DATE,
692 x_last_updated_by => X_LAST_UPDATED_BY,
693 x_last_update_login => X_LAST_UPDATE_LOGIN,
694 x_org_id => igs_ge_gen_003.get_org_id,
695 x_annual_instruction_time => x_annual_instruction_time
696 );
697
698 INSERT INTO IGS_PS_VER_HIST_ALL (
699 course_cd,
700 version_number,
701 hist_start_dt,
702 hist_end_dt,
703 hist_who,
704 start_dt,
705 review_dt,
706 expiry_dt,
707 end_dt,
708 course_status,
709 title,
710 short_title,
711 abbreviation,
712 supp_exam_permitted_ind,
713 generic_course_ind,
714 graduate_students_ind,
715 count_intrmsn_in_time_ind,
716 intrmsn_allowed_ind,
717 course_type,
718 ct_description,
719 responsible_org_unit_cd,
720 responsible_ou_start_dt,
721 ou_description,
722 govt_special_course_type,
723 gsct_description,
724 qualification_recency,
725 external_adv_stnd_limit,
726 internal_adv_stnd_limit,
727 contact_hours,
728 credit_points_required,
729 govt_course_load,
730 std_annual_load,
731 course_total_eftsu,
732 max_intrmsn_duration,
733 num_of_units_before_intrmsn,
734 min_sbmsn_percentage,
735 min_cp_per_calendar,
736 approval_date,
737 external_approval_date,
738 federal_financial_aid,
739 institutional_financial_aid,
740 max_cp_per_teaching_period,
741 residency_cp_required,
742 state_financial_aid,
743 primary_program_rank,
744 max_wlst_per_stud,
745 creation_date,
746 created_by,
747 last_update_date,
748 last_updated_by,
749 last_update_login,
750 org_id,
751 annual_instruction_time
752 ) values (
753 new_references.course_cd,
754 new_references.version_number,
755 new_references.hist_start_dt,
756 new_references.hist_end_dt,
757 new_references.hist_who,
758 new_references.start_dt,
759 new_references.review_dt,
760 new_references.expiry_dt,
761 new_references.end_dt,
762 new_references.course_status,
763 new_references.title,
764 new_references.short_title,
765 new_references.abbreviation,
766 new_references.supp_exam_permitted_ind,
767 new_references.generic_course_ind,
768 new_references.graduate_students_ind,
769 new_references.count_intrmsn_in_time_ind,
770 new_references.intrmsn_allowed_ind,
771 new_references.course_type,
772 new_references.ct_description,
773 new_references.responsible_org_unit_cd,
774 new_references.responsible_ou_start_dt,
775 new_references.ou_description,
776 new_references.govt_special_course_type,
777 new_references.gsct_description,
778 new_references.qualification_recency,
779 new_references.external_adv_stnd_limit,
780 new_references.internal_adv_stnd_limit,
781 new_references.contact_hours,
782 new_references.credit_points_required,
783 new_references.govt_course_load,
784 new_references.std_annual_load,
785 new_references.course_total_eftsu,
786 new_references.max_intrmsn_duration,
787 new_references.num_of_units_before_intrmsn,
788 new_references.min_sbmsn_percentage,
789 new_references.min_cp_per_calendar,
790 new_references.approval_date,
791 new_references.external_approval_date,
792 new_references.federal_financial_aid,
793 new_references.institutional_financial_aid,
794 new_references.max_cp_per_teaching_period,
795 new_references.residency_cp_required,
796 new_references.state_financial_aid,
797 new_references.primary_program_rank,
798 new_references.max_wlst_per_stud,
799 x_last_update_date,
800 x_last_updated_by,
801 x_last_update_date,
802 x_last_updated_by,
803 x_last_update_login,
804 new_references.org_id,
805 new_references.annual_instruction_time
806 );
807
808 OPEN c;
809 FETCH c INTO X_ROWID;
810 IF (c%notfound) THEN
811 close c;
812 raise no_data_found;
813 END IF;
814 CLOSE c;
815 after_dml(
816 p_action => 'INSERT',
817 x_rowid => x_rowid
818 );
819
820 END insert_row;
821
822 PROCEDURE lock_row (
823 x_rowid IN VARCHAR2,
824 x_course_cd IN VARCHAR2,
825 x_version_number IN NUMBER,
826 x_hist_start_dt IN DATE,
827 x_hist_end_dt IN DATE,
828 x_hist_who IN NUMBER,
829 x_start_dt IN DATE,
830 x_review_dt IN DATE,
831 x_expiry_dt IN DATE,
832 x_end_dt IN DATE,
833 x_course_status IN VARCHAR2,
834 x_title IN VARCHAR2,
835 x_short_title IN VARCHAR2,
836 x_abbreviation IN VARCHAR2,
837 x_supp_exam_permitted_ind IN VARCHAR2,
838 x_generic_course_ind IN VARCHAR2,
839 x_graduate_students_ind IN VARCHAR2,
840 x_count_intrmsn_in_time_ind IN VARCHAR2,
841 x_intrmsn_allowed_ind IN VARCHAR2,
842 x_course_type IN VARCHAR2,
843 x_ct_description IN VARCHAR2,
844 x_responsible_org_unit_cd IN VARCHAR2,
845 x_responsible_ou_start_dt IN DATE,
846 x_ou_description IN VARCHAR2,
847 x_govt_special_course_type IN VARCHAR2,
848 x_gsct_description IN VARCHAR2,
849 x_qualification_recency IN NUMBER,
850 x_external_adv_stnd_limit IN NUMBER,
851 x_internal_adv_stnd_limit IN NUMBER,
852 x_contact_hours IN NUMBER,
853 x_credit_points_required IN NUMBER,
854 x_govt_course_load IN NUMBER,
855 x_std_annual_load IN NUMBER,
856 x_course_total_eftsu IN NUMBER,
857 x_max_intrmsn_duration IN NUMBER,
858 x_num_of_units_before_intrmsn IN NUMBER,
859 x_min_sbmsn_percentage IN NUMBER,
860 x_min_cp_per_calendar IN NUMBER ,
861 x_approval_date IN DATE ,
862 x_external_approval_date IN DATE ,
863 x_federal_financial_aid IN VARCHAR2 ,
864 x_institutional_financial_aid IN VARCHAR2 ,
865 x_max_cp_per_teaching_period IN NUMBER ,
866 x_residency_cp_required IN NUMBER ,
867 x_state_financial_aid IN VARCHAR2 ,
868 x_primary_program_rank IN NUMBER,
869 x_max_wlst_per_stud IN NUMBER,
870 x_annual_instruction_time IN NUMBER
871 )AS
872 /*----------------------------------------------------------------------------
873 || Created By :
874 || Created On :
875 || Purpose :
876 || Known limitations, enhancements or remarks :
877 || Change History :
878 || Who When What
879 || (reverse chronological order - newest change first)
880 || vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
881 || as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22
882 || warnings.
883 ----------------------------------------------------------------------------*/
884 CURSOR c1 IS SELECT
885 hist_end_dt,
886 hist_who,
887 start_dt,
888 review_dt,
889 expiry_dt,
890 end_dt,
891 course_status,
892 title,
893 short_title,
894 abbreviation,
895 supp_exam_permitted_ind,
896 generic_course_ind,
897 graduate_students_ind,
898 count_intrmsn_in_time_ind,
899 intrmsn_allowed_ind,
900 course_type,
901 ct_description,
902 responsible_org_unit_cd,
903 responsible_ou_start_dt,
904 ou_description,
905 govt_special_course_type,
906 gsct_description,
907 qualification_recency,
908 external_adv_stnd_limit,
909 internal_adv_stnd_limit,
910 contact_hours,
911 credit_points_required,
912 govt_course_load,
913 std_annual_load,
914 course_total_eftsu,
915 max_intrmsn_duration,
916 num_of_units_before_intrmsn,
917 min_sbmsn_percentage,
918 min_cp_per_calendar,
919 approval_date ,
920 external_approval_date ,
921 federal_financial_aid ,
922 institutional_financial_aid ,
923 max_cp_per_teaching_period,
924 residency_cp_required ,
925 state_financial_aid,
926 primary_program_rank,
927 max_wlst_per_stud,
928 annual_instruction_time
929 FROM IGS_PS_VER_HIST_ALL
930 WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
931 tlinfo c1%rowtype;
932
933 BEGIN
934 OPEN c1;
935 FETCH c1 INTO tlinfo;
936 IF (c1%notfound) THEN
937 CLOSE c1;
938 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
939 IGS_GE_MSG_STACK.ADD;
940 app_exception.raise_exception;
941 RETURN;
942 END IF;
943 CLOSE c1;
944
945 IF ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
946 AND (tlinfo.HIST_WHO = X_HIST_WHO)
947 AND ((tlinfo.START_DT = X_START_DT)
948 OR ((tlinfo.START_DT is null)
949 AND (X_START_DT is null)))
950 AND ((tlinfo.REVIEW_DT = X_REVIEW_DT)
951 OR ((tlinfo.REVIEW_DT is null)
952 AND (X_REVIEW_DT is null)))
953 AND ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
954 OR ((tlinfo.EXPIRY_DT is null)
955 AND (X_EXPIRY_DT is null)))
956 AND ((tlinfo.END_DT = X_END_DT)
957 OR ((tlinfo.END_DT is null)
958 AND (X_END_DT is null)))
959 AND ((tlinfo.COURSE_STATUS = X_COURSE_STATUS)
960 OR ((tlinfo.COURSE_STATUS is null)
961 AND (X_COURSE_STATUS is null)))
962 AND ((tlinfo.TITLE = X_TITLE)
963 OR ((tlinfo.TITLE is null)
964 AND (X_TITLE is null)))
965 AND ((tlinfo.SHORT_TITLE = X_SHORT_TITLE)
966 OR ((tlinfo.SHORT_TITLE is null)
967 AND (X_SHORT_TITLE is null)))
968 AND ((tlinfo.ABBREVIATION = X_ABBREVIATION)
969 OR ((tlinfo.ABBREVIATION is null)
970 AND (X_ABBREVIATION is null)))
971 AND ((tlinfo.SUPP_EXAM_PERMITTED_IND = X_SUPP_EXAM_PERMITTED_IND)
972 OR ((tlinfo.SUPP_EXAM_PERMITTED_IND is null)
973 AND (X_SUPP_EXAM_PERMITTED_IND is null)))
974 AND ((tlinfo.GENERIC_COURSE_IND = X_GENERIC_COURSE_IND)
975 OR ((tlinfo.GENERIC_COURSE_IND is null)
976 AND (X_GENERIC_COURSE_IND is null)))
977 AND ((tlinfo.GRADUATE_STUDENTS_IND = X_GRADUATE_STUDENTS_IND)
978 OR ((tlinfo.GRADUATE_STUDENTS_IND is null)
979 AND (X_GRADUATE_STUDENTS_IND is null)))
980 AND ((tlinfo.COUNT_INTRMSN_IN_TIME_IND = X_COUNT_INTRMSN_IN_TIME_IND)
981 OR ((tlinfo.COUNT_INTRMSN_IN_TIME_IND is null)
982 AND (X_COUNT_INTRMSN_IN_TIME_IND is null)))
983 AND ((tlinfo.INTRMSN_ALLOWED_IND = X_INTRMSN_ALLOWED_IND)
984 OR ((tlinfo.INTRMSN_ALLOWED_IND is null)
985 AND (X_INTRMSN_ALLOWED_IND is null)))
986 AND ((tlinfo.COURSE_TYPE = X_COURSE_TYPE)
987 OR ((tlinfo.COURSE_TYPE is null)
988 AND (X_COURSE_TYPE is null)))
989 AND ((tlinfo.CT_DESCRIPTION = X_CT_DESCRIPTION)
990 OR ((tlinfo.CT_DESCRIPTION is null)
991 AND (X_CT_DESCRIPTION is null)))
992 AND ((tlinfo.RESPONSIBLE_ORG_UNIT_CD = X_RESPONSIBLE_ORG_UNIT_CD)
993 OR ((tlinfo.RESPONSIBLE_ORG_UNIT_CD is null)
994 AND (X_RESPONSIBLE_ORG_UNIT_CD is null)))
995 AND ((tlinfo.RESPONSIBLE_OU_START_DT = X_RESPONSIBLE_OU_START_DT)
996 OR ((tlinfo.RESPONSIBLE_OU_START_DT is null)
997 AND (X_RESPONSIBLE_OU_START_DT is null)))
998 AND ((tlinfo.OU_DESCRIPTION = X_OU_DESCRIPTION)
999 OR ((tlinfo.OU_DESCRIPTION is null)
1000 AND (X_OU_DESCRIPTION is null)))
1001 AND ((tlinfo.GOVT_SPECIAL_COURSE_TYPE = X_GOVT_SPECIAL_COURSE_TYPE)
1002 OR ((tlinfo.GOVT_SPECIAL_COURSE_TYPE is null)
1003 AND (X_GOVT_SPECIAL_COURSE_TYPE is null)))
1004 AND ((tlinfo.GSCT_DESCRIPTION = X_GSCT_DESCRIPTION)
1005 OR ((tlinfo.GSCT_DESCRIPTION is null)
1006 AND (X_GSCT_DESCRIPTION is null)))
1007 AND ((tlinfo.QUALIFICATION_RECENCY = X_QUALIFICATION_RECENCY)
1008 OR ((tlinfo.QUALIFICATION_RECENCY is null)
1009 AND (X_QUALIFICATION_RECENCY is null)))
1010 AND ((tlinfo.EXTERNAL_ADV_STND_LIMIT = X_EXTERNAL_ADV_STND_LIMIT)
1011 OR ((tlinfo.EXTERNAL_ADV_STND_LIMIT is null)
1012 AND (X_EXTERNAL_ADV_STND_LIMIT is null)))
1013 AND ((tlinfo.INTERNAL_ADV_STND_LIMIT = X_INTERNAL_ADV_STND_LIMIT)
1014 OR ((tlinfo.INTERNAL_ADV_STND_LIMIT is null)
1015 AND (X_INTERNAL_ADV_STND_LIMIT is null)))
1016 AND ((tlinfo.CONTACT_HOURS = X_CONTACT_HOURS)
1017 OR ((tlinfo.CONTACT_HOURS is null)
1018 AND (X_CONTACT_HOURS is null)))
1019 AND ((tlinfo.CREDIT_POINTS_REQUIRED = X_CREDIT_POINTS_REQUIRED)
1020 OR ((tlinfo.CREDIT_POINTS_REQUIRED is null)
1021 AND (X_CREDIT_POINTS_REQUIRED is null)))
1022 AND ((tlinfo.GOVT_COURSE_LOAD = X_GOVT_COURSE_LOAD)
1023 OR ((tlinfo.GOVT_COURSE_LOAD is null)
1024 AND (X_GOVT_COURSE_LOAD is null)))
1025 AND ((tlinfo.STD_ANNUAL_LOAD = X_STD_ANNUAL_LOAD)
1026 OR ((tlinfo.STD_ANNUAL_LOAD is null)
1027 AND (X_STD_ANNUAL_LOAD is null)))
1028 AND ((tlinfo.COURSE_TOTAL_EFTSU = X_COURSE_TOTAL_EFTSU)
1029 OR ((tlinfo.COURSE_TOTAL_EFTSU is null)
1030 AND (X_COURSE_TOTAL_EFTSU is null)))
1031 AND ((tlinfo.MAX_INTRMSN_DURATION = X_MAX_INTRMSN_DURATION)
1032 OR ((tlinfo.MAX_INTRMSN_DURATION is null)
1033 AND (X_MAX_INTRMSN_DURATION is null)))
1034 AND ((tlinfo.NUM_OF_UNITS_BEFORE_INTRMSN = X_NUM_OF_UNITS_BEFORE_INTRMSN)
1035 OR ((tlinfo.NUM_OF_UNITS_BEFORE_INTRMSN is null)
1036 AND (X_NUM_OF_UNITS_BEFORE_INTRMSN is null)))
1037 AND ((tlinfo.MIN_SBMSN_PERCENTAGE = X_MIN_SBMSN_PERCENTAGE)
1038 OR ((tlinfo.MIN_SBMSN_PERCENTAGE is null)
1039 AND (X_MIN_SBMSN_PERCENTAGE is null)))
1040 AND ((tlinfo.min_cp_per_calendar = x_min_cp_per_calendar)
1041 OR ((tlinfo.min_cp_per_calendar IS NULL)
1042 AND (X_min_cp_per_calendar IS NULL)))
1043 AND ((tlinfo.approval_date = x_approval_date)
1044 OR ((tlinfo.approval_date IS NULL)
1045 AND (X_approval_date IS NULL)))
1046 AND ((tlinfo.external_approval_date = x_external_approval_date)
1047 OR ((tlinfo.external_approval_date IS NULL) AND (X_external_approval_date IS NULL)))
1048 AND ((tlinfo.federal_financial_aid = x_federal_financial_aid)
1049 OR ((tlinfo.federal_financial_aid IS NULL)
1050 AND (X_federal_financial_aid IS NULL)))
1051 AND ((tlinfo.institutional_financial_aid = x_institutional_financial_aid)
1052 OR ((tlinfo.institutional_financial_aid IS NULL)
1053 AND (X_institutional_financial_aid IS NULL)))
1054 AND ((tlinfo.max_cp_per_teaching_period = x_max_cp_per_teaching_period)
1055 OR ((tlinfo.max_cp_per_teaching_period IS NULL)
1056 AND (X_max_cp_per_teaching_period IS NULL)))
1057 AND ((tlinfo.residency_cp_required = x_residency_cp_required)
1058 OR ((tlinfo.residency_cp_required IS NULL)
1059 AND (X_residency_cp_required IS NULL)))
1060 AND ((tlinfo.state_financial_aid = x_state_financial_aid)
1061 OR ((tlinfo.state_financial_aid IS NULL)
1062 AND (X_state_financial_aid IS NULL)))
1063 AND ((tlinfo.primary_program_rank= x_primary_program_rank)
1064 OR ((tlinfo.primary_program_rank IS NULL)
1065 AND (X_primary_program_rank IS NULL)))
1066 AND ((tlinfo.max_wlst_per_stud = x_max_wlst_per_stud)
1067 OR ((tlinfo.max_wlst_per_stud IS NULL)
1068 AND (x_max_wlst_per_stud IS NULL)))
1069 AND ((tlinfo.annual_instruction_time = x_annual_instruction_time)
1070 OR ((tlinfo.annual_instruction_time IS NULL)
1071 AND (x_annual_instruction_time IS NULL)))
1072 ) THEN
1073 NULL;
1074 ELSE
1075 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1076 IGS_GE_MSG_STACK.ADD;
1077 app_exception.raise_exception;
1078 END IF;
1079 RETURN;
1080 END lock_row;
1081
1082 PROCEDURE update_row (
1083 x_rowid IN VARCHAR2,
1084 x_course_cd IN VARCHAR2,
1085 x_version_number IN NUMBER,
1086 x_hist_start_dt IN DATE,
1087 x_hist_end_dt IN DATE,
1088 x_hist_who IN NUMBER,
1089 x_start_dt IN DATE,
1090 x_review_dt IN DATE,
1091 x_expiry_dt IN DATE,
1092 x_end_dt IN DATE,
1093 x_course_status IN VARCHAR2,
1094 x_title IN VARCHAR2,
1095 x_short_title IN VARCHAR2,
1096 x_abbreviation IN VARCHAR2,
1097 x_supp_exam_permitted_ind IN VARCHAR2,
1098 x_generic_course_ind IN VARCHAR2,
1099 x_graduate_students_ind IN VARCHAR2,
1100 x_count_intrmsn_in_time_ind IN VARCHAR2,
1101 x_intrmsn_allowed_ind IN VARCHAR2,
1102 x_course_type IN VARCHAR2,
1103 x_ct_description IN VARCHAR2,
1104 x_responsible_org_unit_cd IN VARCHAR2,
1105 x_responsible_ou_start_dt IN DATE,
1106 x_ou_description IN VARCHAR2,
1107 x_govt_special_course_type IN VARCHAR2,
1108 x_gsct_description IN VARCHAR2,
1109 x_qualification_recency IN NUMBER,
1110 x_external_adv_stnd_limit IN NUMBER,
1111 x_internal_adv_stnd_limit IN NUMBER,
1112 x_contact_hours IN NUMBER,
1113 x_credit_points_required IN NUMBER,
1114 x_govt_course_load IN NUMBER,
1115 x_std_annual_load IN NUMBER,
1116 x_course_total_eftsu IN NUMBER,
1117 x_max_intrmsn_duration IN NUMBER,
1118 x_num_of_units_before_intrmsn IN NUMBER,
1119 x_min_sbmsn_percentage IN NUMBER,
1120 x_min_cp_per_calendar IN NUMBER ,
1121 x_approval_date IN DATE ,
1122 x_external_approval_date IN DATE ,
1123 x_federal_financial_aid IN VARCHAR2 ,
1124 x_institutional_financial_aid IN VARCHAR2 ,
1125 x_max_cp_per_teaching_period IN NUMBER ,
1126 x_residency_cp_required IN NUMBER ,
1127 x_state_financial_aid IN VARCHAR2 ,
1128 x_primary_program_rank IN NUMBER ,
1129 x_max_wlst_per_stud IN NUMBER,
1130 x_mode IN VARCHAR2,
1131 x_annual_instruction_time IN NUMBER
1132 ) AS
1133 /*----------------------------------------------------------------------------
1134 || Created By :
1135 || Created On :
1136 || Purpose :
1137 || Known limitations, enhancements or remarks :
1138 || Change History :
1139 || Who When What
1140 || (reverse chronological order - newest change first)
1141 || vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
1142 || as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22
1143 || warnings.
1144 ----------------------------------------------------------------------------*/
1145 X_LAST_UPDATE_DATE DATE;
1146 X_LAST_UPDATED_BY NUMBER;
1147 X_LAST_UPDATE_LOGIN NUMBER;
1148 BEGIN
1149 X_LAST_UPDATE_DATE := SYSDATE;
1150 IF(X_MODE = 'I') THEN
1151 X_LAST_UPDATED_BY := 1;
1152 X_LAST_UPDATE_LOGIN := 0;
1153 ELSIF (X_MODE = 'R') THEN
1154 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1155 IF X_LAST_UPDATED_BY is NULL THEN
1156 X_LAST_UPDATED_BY := -1;
1157 END IF;
1158 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1159 IF X_LAST_UPDATE_LOGIN IS NULL THEN
1160 X_LAST_UPDATE_LOGIN := -1;
1161 END IF;
1162 ELSE
1163 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1164 IGS_GE_MSG_STACK.ADD;
1165 app_exception.raise_exception;
1166 END IF;
1167 before_dml( p_action => 'UPDATE',
1168 x_rowid => X_ROWID,
1169 x_course_cd => X_COURSE_CD,
1170 x_version_number => X_VERSION_NUMBER,
1171 x_hist_start_dt => X_HIST_START_DT,
1172 x_hist_end_dt => X_HIST_END_DT,
1173 x_hist_who => X_HIST_WHO,
1174 x_start_dt => X_START_DT,
1175 x_review_dt => X_REVIEW_DT,
1176 x_expiry_dt => X_EXPIRY_DT,
1177 x_end_dt => X_END_DT,
1178 x_course_status => X_COURSE_STATUS,
1179 x_title => X_TITLE,
1180 x_short_title => X_SHORT_TITLE,
1181 x_abbreviation => X_ABBREVIATION,
1182 x_supp_exam_permitted_ind => X_SUPP_EXAM_PERMITTED_IND,
1183 x_generic_course_ind => X_GENERIC_COURSE_IND,
1184 x_graduate_students_ind => X_GRADUATE_STUDENTS_IND,
1185 x_count_intrmsn_in_time_ind => X_COUNT_INTRMSN_IN_TIME_IND,
1186 x_intrmsn_allowed_ind => X_INTRMSN_ALLOWED_IND,
1187 x_course_type => X_COURSE_TYPE,
1188 x_ct_description => X_CT_DESCRIPTION,
1189 x_responsible_org_unit_cd => X_RESPONSIBLE_ORG_UNIT_CD,
1190 x_responsible_ou_start_dt => X_RESPONSIBLE_OU_START_DT,
1191 x_ou_description => X_OU_DESCRIPTION,
1192 x_govt_special_course_type => X_GOVT_SPECIAL_COURSE_TYPE,
1193 x_gsct_description =>X_GSCT_DESCRIPTION,
1194 x_qualification_recency => X_QUALIFICATION_RECENCY,
1195 x_external_adv_stnd_limit => X_EXTERNAL_ADV_STND_LIMIT,
1196 x_internal_adv_stnd_limit => X_INTERNAL_ADV_STND_LIMIT,
1197 x_contact_hours => X_CONTACT_HOURS,
1198 x_credit_points_required => X_CREDIT_POINTS_REQUIRED,
1199 x_govt_course_load => X_GOVT_COURSE_LOAD,
1200 x_std_annual_load => X_STD_ANNUAL_LOAD,
1201 x_course_total_eftsu => X_COURSE_TOTAL_EFTSU,
1202 x_max_intrmsn_duration => X_MAX_INTRMSN_DURATION,
1203 x_num_of_units_before_intrmsn => X_NUM_OF_UNITS_BEFORE_INTRMSN,
1204 x_min_sbmsn_percentage => X_MIN_SBMSN_PERCENTAGE,
1205 x_min_cp_per_calendar => X_MIN_CP_PER_CALENDAR,
1206 x_approval_date => X_APPROVAL_DATE,
1207 x_external_approval_date => X_EXTERNAL_APPROVAL_DATE,
1208 x_federal_financial_aid => X_FEDERAL_FINANCIAL_AID,
1209 x_institutional_financial_aid => X_INSTITUTIONAL_FINANCIAL_AID,
1210 x_max_cp_per_teaching_period =>X_MAX_CP_PER_TEACHING_PERIOD,
1211 x_residency_cp_required =>X_RESIDENCY_CP_REQUIRED,
1212 x_state_financial_aid =>X_STATE_FINANCIAL_AID,
1213 x_primary_program_rank=>X_PRIMARY_PROGRAM_RANK,
1214 x_max_wlst_per_stud => x_max_wlst_per_stud,
1215 x_creation_date => X_LAST_UPDATE_DATE,
1216 x_created_by => X_LAST_UPDATED_BY,
1217 x_last_update_date => X_LAST_UPDATE_DATE,
1218 x_last_updated_by => X_LAST_UPDATED_BY,
1219 x_last_update_login => X_LAST_UPDATE_LOGIN,
1220 x_annual_instruction_time => x_annual_instruction_time
1221 );
1222
1223 UPDATE IGS_PS_VER_HIST_ALL SET
1224 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
1225 HIST_WHO = NEW_REFERENCES.HIST_WHO,
1226 START_DT = NEW_REFERENCES.START_DT,
1227 REVIEW_DT = NEW_REFERENCES.REVIEW_DT,
1228 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
1229 END_DT = NEW_REFERENCES.END_DT,
1230 COURSE_STATUS = NEW_REFERENCES.COURSE_STATUS,
1231 TITLE = NEW_REFERENCES.TITLE,
1232 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
1233 ABBREVIATION = NEW_REFERENCES.ABBREVIATION,
1234 SUPP_EXAM_PERMITTED_IND = NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
1235 GENERIC_COURSE_IND = NEW_REFERENCES.GENERIC_COURSE_IND,
1236 GRADUATE_STUDENTS_IND = NEW_REFERENCES.GRADUATE_STUDENTS_IND,
1237 COUNT_INTRMSN_IN_TIME_IND = NEW_REFERENCES.COUNT_INTRMSN_IN_TIME_IND,
1238 INTRMSN_ALLOWED_IND = NEW_REFERENCES.INTRMSN_ALLOWED_IND,
1239 COURSE_TYPE = NEW_REFERENCES.COURSE_TYPE,
1240 CT_DESCRIPTION = NEW_REFERENCES.CT_DESCRIPTION,
1241 RESPONSIBLE_ORG_UNIT_CD = NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
1242 RESPONSIBLE_OU_START_DT = NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
1243 OU_DESCRIPTION = NEW_REFERENCES.OU_DESCRIPTION,
1244 GOVT_SPECIAL_COURSE_TYPE = NEW_REFERENCES.GOVT_SPECIAL_COURSE_TYPE,
1245 GSCT_DESCRIPTION = NEW_REFERENCES.GSCT_DESCRIPTION,
1246 QUALIFICATION_RECENCY = NEW_REFERENCES.QUALIFICATION_RECENCY,
1247 EXTERNAL_ADV_STND_LIMIT = NEW_REFERENCES.EXTERNAL_ADV_STND_LIMIT,
1248 INTERNAL_ADV_STND_LIMIT = NEW_REFERENCES.INTERNAL_ADV_STND_LIMIT,
1249 CONTACT_HOURS = NEW_REFERENCES.CONTACT_HOURS,
1250 CREDIT_POINTS_REQUIRED = NEW_REFERENCES.CREDIT_POINTS_REQUIRED,
1251 GOVT_COURSE_LOAD = NEW_REFERENCES.GOVT_COURSE_LOAD,
1252 STD_ANNUAL_LOAD = NEW_REFERENCES.STD_ANNUAL_LOAD,
1253 COURSE_TOTAL_EFTSU = NEW_REFERENCES.COURSE_TOTAL_EFTSU,
1254 MAX_INTRMSN_DURATION = NEW_REFERENCES.MAX_INTRMSN_DURATION,
1255 NUM_OF_UNITS_BEFORE_INTRMSN = NEW_REFERENCES.NUM_OF_UNITS_BEFORE_INTRMSN,
1256 MIN_SBMSN_PERCENTAGE = NEW_REFERENCES.MIN_SBMSN_PERCENTAGE,
1257 min_cp_per_calendar = NEW_REFERENCES.MIN_CP_PER_CALENDAR,
1258 approval_date = NEW_REFERENCES.APPROVAL_DATE,
1259 external_approval_date = NEW_REFERENCES.EXTERNAL_APPROVAL_DATE,
1260 federal_financial_aid = NEW_REFERENCES.FEDERAL_FINANCIAL_AID,
1261 institutional_financial_aid = NEW_REFERENCES.INSTITUTIONAL_FINANCIAL_AID,
1262 max_cp_per_teaching_period = NEW_REFERENCES.MAX_CP_PER_TEACHING_PERIOD,
1263 residency_cp_required = NEW_REFERENCES.RESIDENCY_CP_REQUIRED,
1264 state_financial_aid =NEW_REFERENCES.STATE_FINANCIAL_AID,
1265 primary_program_rank = NEW_REFERENCES.PRIMARY_PROGRAM_RANK,
1266 max_wlst_per_stud = NEW_REFERENCES.max_wlst_per_stud,
1267 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1268 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1269 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1270 annual_instruction_time = x_annual_instruction_time
1271
1272 WHERE ROWID = x_rowid
1273 ;
1274 IF (sql%notfound) THEN
1275 RAISE no_data_found;
1276 END IF;
1277 after_dml(
1278 p_action => 'UPDATE',
1279 x_rowid => X_ROWID
1280 );
1281
1282 END update_row;
1283
1284 PROCEDURE add_row (
1285 x_rowid IN OUT NOCOPY VARCHAR2,
1286 x_course_cd IN VARCHAR2,
1287 x_version_number IN NUMBER,
1288 x_hist_start_dt IN DATE,
1289 x_hist_end_dt IN DATE,
1290 x_hist_who IN NUMBER,
1291 x_start_dt IN DATE,
1292 x_review_dt IN DATE,
1293 x_expiry_dt IN DATE,
1294 x_end_dt IN DATE,
1295 x_course_status IN VARCHAR2,
1296 x_title IN VARCHAR2,
1297 x_short_title IN VARCHAR2,
1298 x_abbreviation IN VARCHAR2,
1299 x_supp_exam_permitted_ind IN VARCHAR2,
1300 x_generic_course_ind IN VARCHAR2,
1301 x_graduate_students_ind IN VARCHAR2,
1302 x_count_intrmsn_in_time_ind IN VARCHAR2,
1303 x_intrmsn_allowed_ind IN VARCHAR2,
1304 x_course_type IN VARCHAR2,
1305 x_ct_description IN VARCHAR2,
1306 x_responsible_org_unit_cd IN VARCHAR2,
1307 x_responsible_ou_start_dt IN DATE,
1308 x_ou_description IN VARCHAR2,
1309 x_govt_special_course_type IN VARCHAR2,
1310 x_gsct_description IN VARCHAR2,
1311 x_qualification_recency IN NUMBER,
1312 x_external_adv_stnd_limit IN NUMBER,
1313 x_internal_adv_stnd_limit IN NUMBER,
1314 x_contact_hours IN NUMBER,
1315 x_credit_points_required IN NUMBER,
1316 x_govt_course_load IN NUMBER,
1317 x_std_annual_load IN NUMBER,
1318 x_course_total_eftsu IN NUMBER,
1319 x_max_intrmsn_duration IN NUMBER,
1320 x_num_of_units_before_intrmsn IN NUMBER,
1321 x_min_sbmsn_percentage IN NUMBER,
1322 x_min_cp_per_calendar IN NUMBER ,
1323 x_approval_date IN DATE ,
1324 x_external_approval_date IN DATE ,
1325 x_federal_financial_aid IN VARCHAR2 ,
1326 x_institutional_financial_aid IN VARCHAR2 ,
1327 x_max_cp_per_teaching_period IN NUMBER ,
1328 x_residency_cp_required IN NUMBER ,
1329 x_state_financial_aid IN VARCHAR2 ,
1330 x_primary_program_rank IN NUMBER ,
1331 x_max_wlst_per_stud IN NUMBER,
1332 x_mode IN VARCHAR2,
1333 x_org_id IN NUMBER,
1334 x_annual_instruction_time IN NUMBER
1335 ) AS
1336 /*----------------------------------------------------------------------------
1337 || Created By :
1338 || Created On :
1339 || Purpose :
1340 || Known limitations, enhancements or remarks :
1341 || Change History :
1342 || Who When What
1343 || (reverse chronological order - newest change first)
1344 || vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
1345 || as these columns are obsolete.Also removed DEFAULT keyword to avoid gscc File.Pkg.22
1346 || warnings.
1347 ----------------------------------------------------------------------------*/
1348 CURSOR C1 IS SELECT rowid FROM IGS_PS_VER_HIST_ALL
1349 WHERE COURSE_CD = X_COURSE_CD
1350 AND VERSION_NUMBER = X_VERSION_NUMBER
1351 AND HIST_START_DT = X_HIST_START_DT
1352 ;
1353 BEGIN
1354 OPEN c1;
1355 FETCH c1 INTO X_ROWID;
1356 IF (c1%notfound) THEN
1357 CLOSE c1;
1358 INSERT_ROW (
1359 x_rowid,
1360 x_course_cd,
1361 x_version_number,
1362 x_hist_start_dt,
1363 x_hist_end_dt,
1364 x_hist_who,
1365 x_start_dt,
1366 x_review_dt,
1367 x_expiry_dt,
1368 x_end_dt,
1369 x_course_status,
1370 x_title,
1371 x_short_title,
1372 x_abbreviation,
1373 x_supp_exam_permitted_ind,
1374 x_generic_course_ind,
1375 x_graduate_students_ind,
1376 x_count_intrmsn_in_time_ind,
1377 x_intrmsn_allowed_ind,
1378 x_course_type,
1379 x_ct_description,
1380 x_responsible_org_unit_cd,
1381 x_responsible_ou_start_dt,
1382 x_ou_description,
1383 x_govt_special_course_type,
1384 x_gsct_description,
1385 x_qualification_recency,
1386 x_external_adv_stnd_limit,
1387 x_internal_adv_stnd_limit,
1388 x_contact_hours,
1389 x_credit_points_required,
1390 x_govt_course_load,
1391 x_std_annual_load,
1392 x_course_total_eftsu,
1393 x_max_intrmsn_duration,
1394 x_num_of_units_before_intrmsn,
1395 x_min_sbmsn_percentage,
1396 x_min_cp_per_calendar,
1397 x_approval_date ,
1398 x_external_approval_date ,
1399 x_federal_financial_aid ,
1400 x_institutional_financial_aid ,
1401 x_max_cp_per_teaching_period,
1402 x_residency_cp_required ,
1403 x_state_financial_aid ,
1404 x_primary_program_rank,
1405 x_max_wlst_per_stud,
1406 x_mode,
1407 x_org_id,
1408 x_annual_instruction_time);
1409 RETURN;
1410 END IF;
1411 CLOSE c1;
1412 UPDATE_ROW (
1413 x_rowid,
1414 x_course_cd,
1415 x_version_number,
1416 x_hist_start_dt,
1417 x_hist_end_dt,
1418 x_hist_who,
1419 x_start_dt,
1420 x_review_dt,
1421 x_expiry_dt,
1422 x_end_dt,
1423 x_course_status,
1424 x_title,
1425 x_short_title,
1426 x_abbreviation,
1427 x_supp_exam_permitted_ind,
1428 x_generic_course_ind,
1429 x_graduate_students_ind,
1430 x_count_intrmsn_in_time_ind,
1431 x_intrmsn_allowed_ind,
1432 x_course_type,
1433 x_ct_description,
1434 x_responsible_org_unit_cd,
1435 x_responsible_ou_start_dt,
1436 x_ou_description,
1437 x_govt_special_course_type,
1438 x_gsct_description,
1439 x_qualification_recency,
1440 x_external_adv_stnd_limit,
1441 x_internal_adv_stnd_limit,
1442 x_contact_hours,
1443 x_credit_points_required,
1444 x_govt_course_load,
1445 x_std_annual_load,
1446 x_course_total_eftsu,
1447 x_max_intrmsn_duration,
1448 x_num_of_units_before_intrmsn,
1449 x_min_sbmsn_percentage,
1450 x_min_cp_per_calendar,
1451 x_approval_date ,
1452 x_external_approval_date ,
1453 x_federal_financial_aid ,
1454 x_institutional_financial_aid ,
1455 x_max_cp_per_teaching_period,
1456 x_residency_cp_required ,
1457 x_state_financial_aid ,
1458 x_primary_program_rank,
1459 x_max_wlst_per_stud,
1460 x_mode,
1461 x_annual_instruction_time
1462 );
1463 END add_row;
1464
1465 PROCEDURE delete_row (
1466 x_rowid in VARCHAR2
1467 ) AS
1468 BEGIN
1469 before_dml( p_action => 'DELETE',
1470 x_rowid => x_rowid
1471 );
1472
1473 DELETE FROM IGS_PS_VER_HIST_ALL
1474 WHERE ROWID = X_ROWID;
1475 if (sql%notfound) then
1476 raise no_data_found;
1477 end if;
1478 after_dml(
1479 p_action => 'DELETE',
1480 x_rowid => X_ROWID
1481 );
1482
1483 END delete_row;
1484
1485 END igs_ps_ver_hist_pkg;