[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_GEN_001
Source
1 PACKAGE BODY IGS_PS_GEN_001 AS
2 /* $Header: IGSPS01B.pls 120.11 2006/05/15 03:16:40 sarakshi ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --sarakshi 23-Jan-2004 Enh#3345205, created new procedure crsp_ins_term_instr_time and invoked the same from the main body
7 --schodava 17-Sep-2003 Bug # 2520994 PSP Inheritance Build
8 -- Modified procedure crsp_ins_unit_section
9 --vvutukur 04-Aug-2003 Enh#3045069.PSP Enh Build. Modified crsp_ins_unit_section,change_unit_section_status.
10 --jdeekoll 28-July-2003 Bug#3060697 Modified the local procedure crsp_ins_cal_rec
11 --jbegum 23-July-2003 Bug#3060693 Modified the local procedure crsp_ins_ca_rec
12 --jbegum 27-jun-2003 Bug#2930935 Added the columns ACHIEVABLE_CREDIT_POINTS,ENROLLED_CREDIT_POINTS
13 -- in the call to igs_ps_usec_cps_pkg.insert_row
14 --jbegum 16-Jun-2003 Bug#2983445 .Obsoleted the column award_title from igs_ps_award table.
15 --smvk 09-Jun-2003 Bug # 2858436. Modified the procedure crsp_ins_crs_ver.
16 --shtatiko 03-JUN-2003 Enh# 2831572, Modified crsp_ins_unit_section and crsp_ins_crs_ver
17 --Nalin Kumar 26-May-2003 Modified the call to the igs_ps_unitass_item_pkg.insert row;
18 -- Passed the correct value to the newly added parameter (x_descroption) of the insert_row;
19 -- This is as per Assessment Item Build. Bug# 2829291;
20 --
21 --sarakshi 24-Apr-2003 Enh#2858431,added procedure change_unit_section_status ,also modified the call to igs_ps_usec_ocur_ref_pkg.insert_row
22 --jbegum 21-Apr-2003 Enh bug#2833850 added columns preferred_region_code and no_set_day_ind to the call of
23 -- igs_ps_usec_occurs_pkg.insert_row
24 --sarakshi 23-Feb-2003 Enh#2797116,modified cursor gc_coo_rec in crsp_ins_crs_ver procedure.Also modified
25 -- cursor c_coo_new in crsp_ins_coi_rec procedure.
26 --vvutukur 01-Nov-2002 Enh#2636716.Modifications done in crsp_ins_unit_section.
27 --vvutukur 28-Oct-2002 Enh#2613933.Modifications done in crsp_ins_unit_section.
28 --shtatiko 21-OCT-2002 Added two parameters, program_length and program_length_measurement, to insert_row call of IGS_PS_OFR_OPT_PKG as per bug# 2608227.
29 --amuthu 24-Sep-02 added core_ind column to the cursor c_posu and also
30 -- added it to the insert row call of igs_ps_pat_study_unt_pkg
31 --jbegum 11-Sep-02 1)As part of bug#2563596 modified CURSOR us_req_refcd.
32 -- 2)Also added a for loop which inserts into the igs_ps_usec_ref_cd
33 -- all reference code records from old unit section to new unit section.
34 -- 3)Added an IF condition that checks if the function igs_ru_gen_002.rulp_ins_parser
35 -- returns a TRUE or FALSE.
36 -- 4)Modified the values being passed to the parameters in call to igs_ru_gen_002.rulp_ins_parser
37 --sarakshi 5-Jun-2002 bug#2332807, changes are mentioned in detail in the code, procedure crsp_ins_unit_section.
38 --smadathi 02-May-2002 Bug 2261649. The procedure crsp_ins_unit_section modified.
39 --jbegum 19 April 02 As part of bug fix of bug #2322290 and bug#2250784
40 -- Removed the following 4 columns
41 -- BILLING_CREDIT_POINTS,BILLING_HRS,FIN_AID_CP,FIN_AID_HRS
42 -- from igs_ps_usec_cps_pkg.insert_row call
43 --prraj 14-Feb-2002 Parameter ACHIEVABLE_CREDIT_POINTS removed from call to
44 -- tbh IGS_PS_USEC_CPS_PKG Bug# 2224366
45 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_ps_val_cop.genp_val_staff_prsn
46 -- is changed to igs_ad_val_acai.genp_val_staff_prsn
47 --bayadav 19-Nov-2001 Bug no:2115430.Added column acad_perd_unit_set column in insert_orw call to IGS_PAT_OF_STUDY table
48 -- Nalin Kumar 20-Nov-2001 Added 'DEFAULT_IND' parameter to call igs_ps_award_pkg.insert_row.
49 -- The changes are as per the UK Award Aims DLD. Bug ID: 1366899
50 --Nalin Kumar 28-Jan-2002 Modified Procedure 'crsp_ins_crs_ver'
51 -- as pert of the HESA Intregation DLD (ENCR019). Bug# 2201753.
52 --ijeddy, Dec 3, 2003 Grade Book Enh build, bug no 3201661
53 -------------------------------------------------------------------------------------------
54 -- Bug No. 1956374 Procedure assp_val_gs_cur_fut reference is changed
55
56 PROCEDURE CRSP_INS_CRS_VER(
57 p_old_course_cd IN VARCHAR2 ,
58 p_old_version_number IN NUMBER ,
59 p_new_course_cd IN VARCHAR2 ,
60 p_new_version_number IN NUMBER ,
61 p_message_name OUT NOCOPY VARCHAR2
62 )
63 AS
64 -------------------------------------------------------------------------------------------
65 --Change History:
66 --Who When What
67 --smvk 09-Jun-2003 Bug # 2858436. Modified the cursor gc_ca_rec to select open program awards.
68 --shtatiko 23-MAY-2003 Enh# 2831572, Removed procedure crsp_ins_revseg_rec and removed
69 -- cursor gc_revseg_rec.
70 -------------------------------------------------------------------------------------------
71 cst_ret_message_name CONSTANT VARCHAR2(30) := 'IGS_PS_FAIL_COPY_PRGVER_DETAI';
72 cst_max_error_range CONSTANT NUMBER := -20999;
73 cst_min_error_range CONSTANT NUMBER := -20000;
74 gv_cv_rec IGS_PS_VER%ROWTYPE;
75 gv_con_rec IGS_PS_OFR_NOTE%ROWTYPE;
76 gv_coon_rec IGS_PS_OFR_OPT_NOTE%ROWTYPE;
77 gv_co_rec IGS_PS_OFR%ROWTYPE;
78 gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79 gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80 gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
81 gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
82 gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
83 gv_copn_rec IGS_PS_OFR_PAT_NOTE%ROWTYPE;
84 gv_ref_num IGS_GE_NOTE.reference_number%TYPE;
85 gv_coo_seq_num IGS_PS_OFR_OPT.coo_id%TYPE;
86 gv_cop_seq_num IGS_PS_OFR_PAT.cop_id%TYPE;
87 gv_ca_rec IGS_PS_AWARD%ROWTYPE;
88 gv_cao_rec IGS_PS_AWD_OWN%ROWTYPE;
89 gv_cow_rec IGS_PS_OWN%ROWTYPE;
90 gv_cvn_rec IGS_PS_VER_NOTE%ROWTYPE;
91 gv_ae_rec IGS_PE_ALTERNATV_EXT%ROWTYPE;
92 gv_cgm_rec IGS_PS_GRP_MBR%ROWTYPE;
93 gv_fsr_rec IGS_FI_FND_SRC_RSTN%ROWTYPE;
94 gv_cfos_rec IGS_PS_FIELD_STUDY%ROWTYPE;
95 gv_ccat_rec IGS_PS_CATEGORISE%ROWTYPE;
96 gv_crcd_rec IGS_PS_REF_CD%ROWTYPE;
97 gv_cal_rec IGS_PS_ANL_LOAD%ROWTYPE;
98 gv_revseg_rec IGS_PS_ACCTS%ROWTYPE;
99 gv_err_msg_proc VARCHAR2(255);
100 gv_err_msg_proc1 VARCHAR2(255);
101 gv_err_msg_proc2 VARCHAR2(255);
102 gv_err_msg_proc3 VARCHAR2(255);
103 gv_err_msg_proc4 VARCHAR2(255);
104 gv_err_msg_proc5 VARCHAR2(255);
105 gv_err_msg_proc6 VARCHAR2(255);
106 gv_err_msg_proc7 VARCHAR2(255);
107 gv_err_msg_proc8 VARCHAR2(255);
108 x_rowid VARCHAR2(25);
109
110 --Next cursor added as per the HESA DLD Build. ENCR019 Bug# 2201753.
111 CURSOR cur_obj_exists IS
112 SELECT 1
113 FROM user_objects
114 WHERE object_name = 'IGS_HE_PS_PKG';
115 l_status NUMBER(3);
116 l_cur_obj_exists cur_obj_exists%ROWTYPE;
117
118
119 CURSOR gc_cv_old_rec IS
120 SELECT *
121 FROM IGS_PS_VER
122 WHERE course_cd = p_old_course_cd AND
123 version_number = p_old_version_number;
124 CURSOR gc_cv_new_rec IS
125 SELECT *
126 FROM IGS_PS_VER
127 WHERE course_cd = p_new_course_cd AND
128 version_number = p_new_version_number;
129 CURSOR gc_ca_rec IS
130 SELECT *
131 FROM IGS_PS_AWARD
132 WHERE course_cd = p_old_course_cd AND
133 version_number = p_old_version_number AND
134 CLOSED_IND = 'N';
135 CURSOR gc_cao_rec IS
136 SELECT *
137 FROM IGS_PS_AWD_OWN
138 WHERE course_cd = p_old_course_cd AND
139 version_number = p_old_version_number AND
140 award_cd = gv_ca_rec.award_cd;
141 CURSOR gc_cow_rec IS
142 SELECT *
143 FROM IGS_PS_OWN
144 WHERE course_cd = p_old_course_cd AND
145 version_number = p_old_version_number;
146 -- Only interested in notes that are not OLE type as
147 -- currently unable to copy long raw field within PL/SQL
148 CURSOR gc_cvn_rec IS
149 SELECT *
150 FROM IGS_PS_VER_NOTE cvn
151 WHERE cvn.course_cd = p_old_course_cd AND
152 cvn.version_number = p_old_version_number AND
153 EXISTS (SELECT 1
154 FROM IGS_GE_NOTE nte
155 WHERE nte.reference_number = cvn.reference_number AND
156 nte.note_text IS NOT NULL);
157 CURSOR gc_ae_rec IS
158 SELECT *
159 FROM IGS_PE_ALTERNATV_EXT
160 WHERE course_cd = p_old_course_cd AND
161 version_number = p_old_version_number;
162 CURSOR gc_cgm_rec IS
163 SELECT *
164 FROM IGS_PS_GRP_MBR
165 WHERE course_cd = p_old_course_cd AND
166 version_number = p_old_version_number;
167 CURSOR gc_fsr_rec IS
168 SELECT *
169 FROM IGS_FI_FND_SRC_RSTN
170 WHERE course_cd = p_old_course_cd AND
171 version_number = p_old_version_number;
172 CURSOR gc_cfos_rec IS
173 SELECT *
174 FROM IGS_PS_FIELD_STUDY
175 WHERE course_cd = p_old_course_cd AND
176 version_number = p_old_version_number;
177 CURSOR gc_ccat_rec IS
178 SELECT *
179 FROM IGS_PS_CATEGORISE
180 WHERE course_cd = p_old_course_cd AND
181 version_number = p_old_version_number;
182 CURSOR gc_crcd_rec IS
183 SELECT *
184 FROM IGS_PS_REF_CD
185 WHERE course_cd = p_old_course_cd AND
186 version_number = p_old_version_number;
187 CURSOR gc_cal_rec IS
188 SELECT *
189 FROM IGS_PS_ANL_LOAD
190 WHERE course_cd = p_old_course_cd AND
191 version_number = p_old_version_number;
192 CURSOR gc_calulink_rec IS
193 SELECT *
194 FROM IGS_PS_ANL_LOAD_U_LN
195 WHERE course_cd = p_old_course_cd AND
196 crv_version_number = p_old_version_number AND
197 yr_num = gv_cal_rec.yr_num AND
198 effective_start_dt = gv_cal_rec.effective_start_dt;
199 CURSOR c_co_rec IS
200 SELECT *
201 FROM IGS_PS_OFR
202 WHERE course_cd = p_old_course_cd AND
203 version_number = p_old_version_number;
204 CURSOR gc_coo_rec IS
205 SELECT *
206 FROM IGS_PS_OFR_OPT
207 WHERE course_cd = p_old_course_cd AND
208 version_number = p_old_version_number AND
209 cal_type = gv_co_rec.cal_type
210 AND delete_flag = 'N';
211
212 CURSOR gc_ceprcd_rec IS
213 SELECT *
214 FROM IGS_PS_ENT_PT_REF_CD
215 WHERE course_cd = p_old_course_cd AND
216 version_number = p_old_version_number AND
217 cal_type = gv_coo_rec.cal_type AND
218 location_cd = gv_coo_rec.location_cd AND
219 attendance_mode = gv_coo_rec.attendance_mode AND
220 attendance_type = gv_coo_rec.attendance_type;
221 -- Only interested in notes that are not OLE type as
222 -- currently unable to copy long raw field within PL/SQL
223 CURSOR gc_con_rec IS
224 SELECT *
225 FROM IGS_PS_OFR_NOTE con
226 WHERE con.course_cd = p_old_course_cd AND
227 con.version_number = p_old_version_number AND
228 cal_type = gv_co_rec.cal_type AND
229 EXISTS (SELECT 1
230 FROM IGS_GE_NOTE nte
231 WHERE nte.reference_number = con.reference_number AND
232 nte.note_text IS NOT NULL);
233 -- Only interested in notes that are not OLE type as
234 -- currently unable to copy long raw field within PL/SQL
235 CURSOR gc_coon_rec IS
236 SELECT *
237 FROM IGS_PS_OFR_OPT_NOTE coon
238 WHERE coon.course_cd = p_old_course_cd AND
239 coon.version_number = p_old_version_number AND
240 coon.cal_type = gv_coo_rec.cal_type AND
241 coon.location_cd = gv_coo_rec.location_cd AND
242 coon.attendance_mode = gv_coo_rec.attendance_mode AND
243 coon.attendance_type = gv_coo_rec.attendance_type AND
244 EXISTS (SELECT 1
245 FROM IGS_GE_NOTE nte
246 WHERE nte.reference_number = coon.reference_number AND
247 nte.note_text IS NOT NULL);
248 -- Find the latest IGS_CA_INST of IGS_PS_OFR_INST
249 -- which returns the list in descending order, and the first record
250 -- (ie. the latest instance) will be selected
251 CURSOR gc_coi_rec IS
252 SELECT *
253 FROM IGS_PS_OFR_INST coi
254 WHERE coi.course_cd = p_old_course_cd AND
255 coi.version_number = p_old_version_number AND
256 coi.cal_type = gv_co_rec.cal_type
257 ORDER BY coi.ci_end_dt DESC, coi.ci_start_dt DESC;
258 CURSOR gc_cop_rec IS
259 SELECT *
260 FROM IGS_PS_OFR_PAT
261 WHERE course_cd = p_old_course_cd AND
262 version_number = p_old_version_number AND
263 cal_type = gv_coi_rec.cal_type AND
264 ci_sequence_number = gv_coi_rec.ci_sequence_number;
265 -- Only interested in notes that are not OLE type as
266 -- currently unable to copy long raw field within PL/SQL
267 CURSOR gc_copn_rec IS
268 SELECT *
269 FROM IGS_PS_OFR_PAT_NOTE cop
270 WHERE cop.cop_id = gv_cop_rec.cop_id AND
271 EXISTS (SELECT 1
272 FROM IGS_GE_NOTE nte
273 WHERE nte.reference_number = cop.reference_number AND
274 nte.note_text IS NOT NULL);
275
276 -- Removed cursor gc_revseg_rec as part of Enh# 2831572.
277
278 CURSOR gc_ref_num IS
279 SELECT IGS_GE_NOTE_RF_NUM_S.NEXTVAL
280 FROM DUAL;
281 CURSOR gc_coo_seq_num IS
282 SELECT IGS_PS_OFR_OPT_COO_ID_S.NEXTVAL
283 FROM DUAL;
284
285 -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_OPT_NOTE records
286 PROCEDURE crsp_ins_coon_rec (
287 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
288 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
289
290 CURSOR Cur_SGN IS
291 SELECT rowid,IGS_GE_NOTE.*
292 FROM IGS_GE_NOTE
293 WHERE reference_number = gv_coon_rec.reference_number;
294 BEGIN
295 OPEN gc_ref_num;
296 FETCH gc_ref_num INTO gv_ref_num;
297 CLOSE gc_ref_num;
298 -- inserting IGS_GE_NOTE record with this next reference_number
299 -- Currently unable to copy Long Raw columns in PL/SQL.
300
301 For SGN_Rec in CUR_SGN
302 Loop
303 x_rowid := NULL;
304 IGS_GE_NOTE_PKG.INSERT_ROW(
305 X_ROWID => X_ROWID,
306 X_REFERENCE_NUMBER =>gv_ref_num,
307 X_S_NOTE_FORMAT_TYPE =>SGN_Rec.s_note_format_type,
308 X_NOTE_TEXT =>SGN_Rec.note_text,
309 X_MODE =>'R');
310 End Loop;
311
312
313 -- inserting IGS_PS_OFR_OPT_NOTE records
314 x_rowid := NULL;
315 IGS_PS_OFR_OPT_NOTE_PKG.INSERT_ROW(
316 X_ROWID => X_ROWID,
317 X_COURSE_CD => p_new_course_cd,
318 X_VERSION_NUMBER => p_new_version_number,
319 X_CAL_TYPE => gv_coon_rec.cal_type,
320 X_ATTENDANCE_MODE => gv_coon_rec.attendance_mode,
321 X_REFERENCE_NUMBER => gv_ref_num,
322 X_ATTENDANCE_TYPE => gv_coon_rec.attendance_type,
323 X_LOCATION_CD => gv_coon_rec.location_cd,
324 X_COO_ID => gv_coo_seq_num,
325 X_CRS_NOTE_TYPE => gv_coon_rec.crs_note_type,
326 X_MODE => 'R'
327 );
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
332 p_message_name := cst_ret_message_name;
333 ELSE
334 App_Exception.Raise_Exception;
335 END IF;
336 END crsp_ins_coon_rec;
337 -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_PAT_NOTE records
338
339 PROCEDURE crsp_ins_copn_rec (
340 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
341 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
342 CURSOR Cur_SGN
343 IS
344 SELECT
345 rowid , IGS_GE_NOTE.*
346 FROM
347 IGS_GE_NOTE
348 WHERE
349 reference_number = gv_copn_rec.reference_number;
350 BEGIN
351 -- select the next reference_number from the system
352 OPEN gc_ref_num;
353 FETCH gc_ref_num INTO gv_ref_num;
354 CLOSE gc_ref_num;
355 -- inserting IGS_GE_NOTE record with this next reference_number
356 -- Currently unable to copy Long Raw columns in PL/SQL.
357 FOR Rec_SGN IN Cur_SGN LOOP
358 x_rowid := NULL;
359 IGS_GE_NOTE_PKG.INSERT_ROW(
360 X_ROWID => x_rowid,
361 X_REFERENCE_NUMBER => gv_ref_num,
362 X_S_NOTE_FORMAT_TYPE => Rec_SGN.s_note_format_type,
363 X_NOTE_TEXT => Rec_SGN.note_text,
364 X_MODE => 'R'
365 );
366 END LOOP;
367
368 -- inserting IGS_PS_OFR_PAT_NOTE records
369 x_rowid := NULL;
370 IGS_PS_OFR_PAT_NOTE_PKG.INSERT_ROW(
371 X_ROWID => x_rowid,
372 X_COURSE_CD => p_new_course_cd,
373 X_CI_SEQUENCE_NUMBER => gv_copn_rec.ci_sequence_number,
374 X_CAL_TYPE => gv_copn_rec.cal_type,
375 X_VERSION_NUMBER => p_new_version_number,
376 X_LOCATION_CD => gv_copn_rec.location_cd,
377 X_ATTENDANCE_TYPE => gv_copn_rec.attendance_type,
378 X_REFERENCE_NUMBER => gv_ref_num,
379 X_ATTENDANCE_MODE => gv_copn_rec.attendance_mode,
380 X_COP_ID => gv_cop_seq_num,
381 X_CRS_NOTE_TYPE => gv_copn_rec.crs_note_type,
382 X_MODE => 'R'
383 );
384 EXCEPTION
385 WHEN OTHERS THEN
386 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
387 p_message_name := cst_ret_message_name;
388 ELSE
389 App_Exception.Raise_Exception;
390 END IF;
391 END crsp_ins_copn_rec;
392 -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_PAT records
393
394 PROCEDURE crsp_ins_cop_rec (
395 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
396 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
397 BEGIN
398 DECLARE
399 v_gs_version_number IGS_AS_GRD_SCHEMA.version_number%TYPE;
400 v_message_name VARCHAR2(30);
401 CURSOR c_latest_gs_version (
402 cp_gs_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE) IS
403 SELECT MAX(gs.version_number)
404 FROM IGS_AS_GRD_SCHEMA gs
405 WHERE gs.grading_schema_cd = cp_gs_cd;
406 BEGIN
407 -- select the next sequence_number from the system
408 SELECT IGS_PS_OFR_PAT_COP_ID_S.NEXTVAL
409 INTO gv_cop_seq_num
410 FROM DUAL;
411 -- get the latest grading schema version number
412 OPEN c_latest_gs_version (
413 gv_cop_rec.grading_schema_cd);
414 FETCH c_latest_gs_version INTO v_gs_version_number;
415 CLOSE c_latest_gs_version;
416 IF IGS_AS_VAL_GSG.assp_val_gs_cur_fut (
417 gv_cop_rec.grading_schema_cd,
418 v_gs_version_number,
419 v_message_name) = FALSE THEN
420 -- The latest grading schema fails the current or vuture valildation
421 gv_cop_rec.grading_schema_cd := NULL;
422 gv_cop_rec.gs_version_number := NULL;
423 ELSE
424 gv_cop_rec.gs_version_number := v_gs_version_number;
425 END IF;
426 -- check if a IGS_PE_PERSON fails the staff IGS_PE_PERSON validation
427 IF igs_ad_val_acai.genp_val_staff_prsn (
428 gv_cop_rec.adm_ass_officer_person_id,
429 v_message_name) = FALSE THEN
430 gv_cop_rec.adm_ass_officer_person_id := NULL;
431 END IF;
432 IF igs_ad_val_acai.genp_val_staff_prsn (
433 gv_cop_rec.adm_contact_person_id,
434 v_message_name) = FALSE THEN
435 gv_cop_rec.adm_contact_person_id := NULL;
436 END IF;
437 -- inserting IGS_PS_OFR_PAT records with this next sequence_number
438 x_rowid := NULL;
439 IGS_PS_OFR_PAT_PKG.INSERT_ROW(
440 X_ROWID => X_ROWID,
441 X_COURSE_CD => p_new_course_cd,
442 X_CI_SEQUENCE_NUMBER => gv_cop_rec.ci_sequence_number,
443 X_CAL_TYPE => gv_cop_rec.cal_type,
444 X_VERSION_NUMBER => p_new_version_number,
445 X_LOCATION_CD => gv_cop_rec.location_cd,
446 X_ATTENDANCE_TYPE => gv_cop_rec.attendance_type,
447 X_ATTENDANCE_MODE => gv_cop_rec.attendance_mode,
448 X_COP_ID => gv_cop_seq_num,
449 X_COO_ID => gv_coo_seq_num,
450 X_OFFERED_IND => gv_cop_rec.offered_ind,
451 X_CONFIRMED_OFFERING_IND => gv_cop_rec.confirmed_offering_ind,
452 X_ENTRY_POINT_IND => gv_cop_rec.entry_point_ind,
453 X_PRE_ENROL_UNITS_IND => gv_cop_rec.pre_enrol_units_ind,
454 X_ENROLLABLE_IND => gv_cop_rec.enrollable_ind,
455 X_IVRS_AVAILABLE_IND => gv_cop_rec.ivrs_available_ind,
456 X_MIN_ENTRY_ASS_SCORE => NULL,
457 X_GUARANTEED_ENTRY_ASS_SCR => NULL,
458 X_MAX_CROSS_FACULTY_CP => NULL,
459 X_MAX_CROSS_LOCATION_CP => NULL,
460 X_MAX_CROSS_MODE_CP => NULL,
461 X_MAX_HIST_CROSS_FACULTY_CP => NULL,
462 X_ADM_ASS_OFFICER_PERSON_ID => gv_cop_rec.adm_ass_officer_person_id,
463 X_ADM_CONTACT_PERSON_ID => gv_cop_rec.adm_contact_person_id,
464 X_GRADING_SCHEMA_CD => gv_cop_rec.grading_schema_cd,
465 X_GS_VERSION_NUMBER => gv_cop_rec.gs_version_number,
466 X_MODE => 'R'
467 );
468 -- calling procedure to insert IGS_PS_OFR_PAT_NOTE records
469 -- associated with each IGS_PS_OFR_PAT record
470 OPEN gc_copn_rec;
471 LOOP
472 FETCH gc_copn_rec INTO gv_copn_rec;
473 IF gc_copn_rec%FOUND THEN
474 crsp_ins_copn_rec(p_new_course_cd, p_new_version_number);
475 ELSE
476 EXIT;
477 END IF;
478 END LOOP;
479 CLOSE gc_copn_rec;
480 EXCEPTION
481 WHEN OTHERS THEN
482 IF c_latest_gs_version%ISOPEN THEN
483 CLOSE c_latest_gs_version;
484 END IF;
485 IF gc_copn_rec%ISOPEN THEN
486 CLOSE gc_copn_rec;
487 END IF;
488 App_Exception.Raise_Exception;
489 END;
490 EXCEPTION
491 WHEN OTHERS THEN
492 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
493 p_message_name := cst_ret_message_name;
494 ELSE
495 App_Exception.Raise_Exception;
496 END IF;
497 END crsp_ins_cop_rec;
498 -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_NOTE records
499 PROCEDURE crsp_ins_con_rec (
500 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
501 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
502 CURSOR
503 Cur_SGN
504 IS
505 SELECT
506 rowid,IGS_GE_NOTE.*
507 FROM
508 IGS_GE_NOTE
509 WHERE
510 reference_number = gv_con_rec.reference_number;
511
512 BEGIN
513 -- select the next reference_number from the system
514 OPEN gc_ref_num;
515 FETCH gc_ref_num INTO gv_ref_num;
516 CLOSE gc_ref_num;
517 -- inserting IGS_GE_NOTE record with this next reference_number
518 -- Currently unable to copy Long Raw columns in PL/SQL.
519 For Rec_SGN IN Cur_SGN LOOP
520 x_rowid := NULL;
521 IGS_GE_NOTE_PKG.INSERT_ROW(
522 X_ROWID => x_rowid,
523 X_REFERENCE_NUMBER => gv_ref_num,
524 X_S_NOTE_FORMAT_TYPE => Rec_SGN.s_note_format_type,
525 X_NOTE_TEXT => Rec_SGN.note_text,
526 X_MODE => 'R'
527 );
528 END LOOP;
529 -- inserting IGS_PS_OFR_NOTE records
530 x_rowid := NULL;
531 IGS_PS_OFR_NOTE_PKG.INSERT_ROW (
532 X_ROWID => x_rowid,
533 X_COURSE_CD => p_new_course_cd,
534 X_REFERENCE_NUMBER => gv_ref_num,
535 X_CAL_TYPE => gv_con_rec.cal_type,
536 X_VERSION_NUMBER => p_new_version_number,
537 X_CRS_NOTE_TYPE => gv_con_rec.crs_note_type,
538 X_MODE => 'R'
539 );
540 EXCEPTION
541 WHEN OTHERS THEN
542 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
543 p_message_name := cst_ret_message_name;
544 ELSE
545 App_Exception.Raise_Exception;
546 END IF;
547 END crsp_ins_con_rec;
548 -- procedure for inserting new IGS_PS_ENT_PT_REF_CD records
549 PROCEDURE crsp_ins_ceprcd_rec (
550 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
551 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
552 BEGIN
553 x_rowid := NULL;
554 IGS_PS_ENT_PT_REF_CD_PKG.INSERT_ROW(
555 X_ROWID => x_rowid,
556 X_COURSE_CD => p_new_course_cd,
557 X_SEQUENCE_NUMBER => gv_ceprcd_rec.sequence_number,
558 X_REFERENCE_CD_TYPE => gv_ceprcd_rec.reference_cd_type,
559 X_ATTENDANCE_TYPE => gv_ceprcd_rec.attendance_type,
560 X_CAL_TYPE => gv_ceprcd_rec.cal_type,
561 X_LOCATION_CD => gv_ceprcd_rec.location_cd,
562 X_VERSION_NUMBER => p_new_version_number,
563 X_ATTENDANCE_MODE => gv_ceprcd_rec.attendance_mode,
564 X_COO_ID => gv_coo_seq_num,
565 X_UNIT_SET_CD => gv_ceprcd_rec.unit_set_cd,
566 X_US_VERSION_NUMBER => gv_ceprcd_rec.us_version_number,
567 X_REFERENCE_CD => gv_ceprcd_rec.reference_cd,
568 X_DESCRIPTION => gv_ceprcd_rec.description,
569 X_MODE => 'R'
570 );
571 EXCEPTION
572 WHEN OTHERS THEN
573 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
574 p_message_name := cst_ret_message_name;
575 ELSE
576 App_Exception.Raise_Exception;
577 END IF;
578 END crsp_ins_ceprcd_rec;
579 PROCEDURE crspl_ins_cooac_rec
580 IS
581 BEGIN
582 DECLARE
583 CURSOR c_cooac IS
584 SELECT cooac.cal_type,
585 cooac.location_cd,
586 cooac.attendance_mode,
587 cooac.attendance_type,
588 cooac.admission_cat,
589 cooac.system_default_ind
590 FROM IGS_PS_OF_OPT_AD_CAT cooac
591 WHERE cooac.course_cd = p_old_course_cd AND
592 cooac.version_number = p_old_version_number AND
593 cooac.cal_type = gv_coo_rec.cal_type AND
594 cooac.location_cd = gv_coo_rec.location_cd AND
595 cooac.attendance_mode = gv_coo_rec.attendance_mode AND
596 cooac.attendance_type = gv_coo_rec.attendance_type;
597 CURSOR c_cacus (
598 cp_cooac_cal_type IGS_PS_OF_OPT_AD_CAT.cal_type%TYPE,
599 cp_cooac_location_cd IGS_PS_OF_OPT_AD_CAT.location_cd%TYPE,
600 cp_cooac_attendance_mode IGS_PS_OF_OPT_AD_CAT.attendance_mode%TYPE,
601 cp_cooac_attendance_type IGS_PS_OF_OPT_AD_CAT.attendance_type%TYPE,
602 cp_cooac_admission_cat IGS_PS_OF_OPT_AD_CAT.admission_cat%TYPE) IS
603 SELECT cacus.cal_type,
604 cacus.location_cd,
605 cacus.attendance_mode,
606 cacus.attendance_type,
607 cacus.admission_cat,
608 cacus.unit_set_cd,
609 cacus.us_version_number
610 FROM IGS_PS_COO_AD_UNIT_S cacus
611 WHERE cacus.course_cd = p_old_course_cd AND
612 cacus.crv_version_number = p_old_version_number AND
613 cacus.cal_type = cp_cooac_cal_type AND
614 cacus.location_cd = cp_cooac_location_cd AND
615 cacus.attendance_mode = cp_cooac_attendance_mode AND
616 cacus.attendance_type = cp_cooac_attendance_type AND
617 cacus.admission_cat = cp_cooac_admission_cat;
618 BEGIN
619 FOR v_cooac_rec IN c_cooac LOOP
620 BEGIN
621 x_rowid := NULL;
622 IGS_PS_OF_OPT_AD_CAT_PKG.INSERT_ROW(
623 X_ROWID => x_rowid,
624 X_COURSE_CD => p_new_course_cd,
625 X_VERSION_NUMBER => p_new_version_number,
626 X_CAL_TYPE => v_cooac_rec.cal_type,
627 X_LOCATION_CD => v_cooac_rec.location_cd,
628 X_ATTENDANCE_TYPE => v_cooac_rec.attendance_type,
629 X_ATTENDANCE_MODE => v_cooac_rec.attendance_mode,
630 X_ADMISSION_CAT => v_cooac_rec.admission_cat,
631 X_COO_ID => gv_coo_seq_num,
632 X_SYSTEM_DEFAULT_IND => v_cooac_rec.system_default_ind,
633 X_MODE => 'R'
634 );
635 FOR v_cacus_rec IN c_cacus(
636 v_cooac_rec.cal_type,
637 v_cooac_rec.location_cd,
638 v_cooac_rec.attendance_mode,
639 v_cooac_rec.attendance_type,
640 v_cooac_rec.admission_cat) LOOP
641
642 x_rowid := NULL;
643 IGS_PS_COO_AD_UNIT_S_PKG.INSERT_ROW(
644 X_ROWID => x_rowid,
645 X_COURSE_CD => p_new_course_cd,
646 X_CRV_VERSION_NUMBER => p_new_version_number,
647 X_CAL_TYPE => v_cacus_rec.cal_type,
648 X_LOCATION_CD => v_cacus_rec.location_cd,
649 X_ATTENDANCE_MODE => v_cacus_rec.attendance_mode,
650 X_ATTENDANCE_TYPE => v_cacus_rec.attendance_type,
651 X_ADMISSION_CAT => v_cacus_rec.admission_cat,
652 X_UNIT_SET_CD => v_cacus_rec.unit_set_cd,
653 X_US_VERSION_NUMBER => v_cacus_rec.us_version_number,
654 X_MODE => 'R'
655 );
656
657 END LOOP;
658 EXCEPTION
659 WHEN OTHERS THEN
660 IF SQLCODE >= cst_max_error_range AND
661 SQLCODE <= cst_min_error_range THEN
662 p_message_name := cst_ret_message_name;
663 ELSE
664 App_Exception.Raise_Exception;
665 END IF;
666 END;
667 END LOOP;
668 EXCEPTION
669 WHEN OTHERS THEN
670 IF (c_cooac%ISOPEN) THEN
671 CLOSE c_cooac;
672 END IF;
673 IF (c_cacus%ISOPEN) THEN
674 CLOSE c_cacus;
675 END IF;
676 App_Exception.Raise_Exception;
677 END;
678 EXCEPTION
679 WHEN OTHERS THEN
680 IF SQLCODE >= cst_max_error_range AND
681 SQLCODE <= cst_min_error_range THEN
682 p_message_name := cst_ret_message_name;
683 ELSE
684 App_Exception.Raise_Exception;
685 END IF;
686 END crspl_ins_cooac_rec;
687 -- procedure for inserting new IGS_PS_OFR_OPT records
688 --modified by shtatiko on 21-OCT-2002 to incorporate the addition of two new columns to IGS_PS_OFR_OPT viz. program_length, program_length_measurement.
689 --this has been done as per bug# 2608227.
690 PROCEDURE crsp_ins_coo_rec(
691 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
692 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
693 BEGIN
694 DECLARE
695 CURSOR c_coous IS
696 SELECT coous.cal_type,
697 coous.location_cd,
698 coous.attendance_mode,
699 coous.attendance_type,
700 coous.unit_set_cd,
701 coous.us_version_number
702 FROM IGS_PS_OF_OPT_UNT_ST coous
703 WHERE coous.course_cd = p_old_course_cd AND
704 coous.crv_version_number = p_old_version_number AND
705 coous.cal_type = gv_coo_rec.cal_type AND
706 coous.location_cd = gv_coo_rec.location_cd AND
707 coous.attendance_mode = gv_coo_rec.attendance_mode AND
708 coous.attendance_type = gv_coo_rec.attendance_type AND
709 EXISTS (SELECT 'X'
710 FROM IGS_PS_OFR_UNIT_SET cous
711 WHERE cous.course_cd = p_new_course_cd AND
712 cous.crv_version_number = p_new_version_number AND
713 cous.cal_type = coous.cal_type AND
714 cous.unit_set_cd = coous.unit_set_cd AND
715 cous.us_version_number = coous.us_version_number);
716 l_org_id NUMBER(15);
717 BEGIN
718 -- select the next IGS_PS_OFR_OPT reference_number
719 -- from the system
720 OPEN gc_coo_seq_num;
721 FETCH gc_coo_seq_num INTO gv_coo_seq_num;
722 CLOSE gc_coo_seq_num;
723 x_rowid := NULL;
724 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
725 IGS_PS_OFR_OPT_PKG.INSERT_ROW(
726 X_ROWID => x_rowid,
727 X_COURSE_CD => p_new_course_cd,
728 X_VERSION_NUMBER => p_new_version_number,
729 X_CAL_TYPE => gv_coo_rec.cal_type,
730 X_ATTENDANCE_MODE => gv_coo_rec.attendance_mode,
731 X_ATTENDANCE_TYPE => gv_coo_rec.attendance_type,
732 X_LOCATION_CD => gv_coo_rec.location_cd,
733 X_COO_ID => gv_coo_seq_num,
734 X_FORCED_LOCATION_IND => gv_coo_rec.forced_location_ind,
735 X_FORCED_ATT_MODE_IND => gv_coo_rec.forced_att_mode_ind,
736 X_FORCED_ATT_TYPE_IND => gv_coo_rec.forced_att_type_ind,
737 X_TIME_LIMITATION => gv_coo_rec.time_limitation,
738 X_ENR_OFFICER_PERSON_ID => gv_coo_rec.enr_officer_person_id,
739 X_ATTRIBUTE_CATEGORY => gv_coo_rec.attribute_category,
740 X_ATTRIBUTE1 => gv_coo_rec.attribute1,
741 X_ATTRIBUTE2 => gv_coo_rec.attribute2,
742 X_ATTRIBUTE3 => gv_coo_rec.attribute3,
743 X_ATTRIBUTE4 => gv_coo_rec.attribute4,
744 X_ATTRIBUTE5 => gv_coo_rec.attribute5,
745 X_ATTRIBUTE6 => gv_coo_rec.attribute6,
746 X_ATTRIBUTE7 => gv_coo_rec.attribute7,
747 X_ATTRIBUTE8 => gv_coo_rec.attribute8,
748 X_ATTRIBUTE9 => gv_coo_rec.attribute9,
749 X_ATTRIBUTE10 => gv_coo_rec.attribute10,
750 X_ATTRIBUTE11 => gv_coo_rec.attribute11,
751 X_ATTRIBUTE12 => gv_coo_rec.attribute12,
752 X_ATTRIBUTE13 => gv_coo_rec.attribute13,
753 X_ATTRIBUTE14 => gv_coo_rec.attribute14,
754 X_ATTRIBUTE15 => gv_coo_rec.attribute15,
755 X_ATTRIBUTE16 => gv_coo_rec.attribute16,
756 X_ATTRIBUTE17 => gv_coo_rec.attribute17,
757 X_ATTRIBUTE18 => gv_coo_rec.attribute18,
758 X_ATTRIBUTE19 => gv_coo_rec.attribute19,
759 X_ATTRIBUTE20 => gv_coo_rec.attribute20,
760 X_MODE => 'R',
761 X_ORG_ID => l_org_id ,
762 x_program_length => gv_coo_rec.program_length, --added as per bug# 2608227
763 x_program_length_measurement => gv_coo_rec.program_length_measurement --added as per bug# 2608227
764 );
765 -- calling procedure to insert IGS_PS_OFR_OPT_NOTE records
766 -- associated with each IGS_PS_OFR_OPT record
767 OPEN gc_coon_rec;
768 LOOP
769 FETCH gc_coon_rec INTO gv_coon_rec;
770 IF gc_coon_rec%FOUND THEN
771 crsp_ins_coon_rec(p_new_course_cd, p_new_version_number);
772 ELSE
773 EXIT;
774 END IF;
775 END LOOP;
776 CLOSE gc_coon_rec;
777 -- calling procedure to insert IGS_PS_ENT_PT_REF_CD records
778 -- associated with each IGS_PS_OFR instance record
779 OPEN gc_ceprcd_rec;
780 LOOP
781 FETCH gc_ceprcd_rec INTO gv_ceprcd_rec;
782 IF gc_ceprcd_rec%FOUND THEN
783 crsp_ins_ceprcd_rec(p_new_course_cd, p_new_version_number);
784 ELSE
785 EXIT;
786 END IF;
787 END LOOP;
788 CLOSE gc_ceprcd_rec;
789 FOR v_coous_rec IN c_coous LOOP
790 x_rowid := NULL;
791 IGS_PS_OF_OPT_UNT_ST_PKG.INSERT_ROW(
792 X_ROWID => x_rowid,
793 X_COURSE_CD => p_new_course_cd,
794 X_LOCATION_CD => v_coous_rec.location_cd,
795 X_ATTENDANCE_MODE => v_coous_rec.attendance_mode,
796 X_CAL_TYPE => v_coous_rec.cal_type,
797 X_CRV_VERSION_NUMBER => p_new_version_number,
798 X_ATTENDANCE_TYPE => v_coous_rec.attendance_type,
799 X_US_VERSION_NUMBER => v_coous_rec.us_version_number,
800 X_UNIT_SET_CD => v_coous_rec.unit_set_cd,
801 X_COO_ID => gv_coo_seq_num,
802 X_MODE => 'R'
803 );
804 END LOOP; -- coous
805 -- calling procedure to insert IGS_PS_OF_OPT_AD_CAT records
806 -- and its child table IGS_PS_COO_AD_UNIT_S
807 crspl_ins_cooac_rec;
808 EXCEPTION
809 WHEN OTHERS THEN
810 IF ( gc_coo_seq_num%ISOPEN) THEN
811 CLOSE gc_coo_seq_num;
812 END IF;
813 IF ( gc_ceprcd_rec%ISOPEN) THEN
814 CLOSE gc_ceprcd_rec;
815 END IF;
816 IF ( gc_ceprcd_rec%ISOPEN) THEN
817 CLOSE gc_ceprcd_rec;
818 END IF;
819 IF (c_coous%ISOPEN) THEN
820 CLOSE c_coous;
821 END IF;
822 App_Exception.Raise_Exception;
823 END;
824 EXCEPTION
825 WHEN OTHERS THEN
826 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
827 p_message_name := cst_ret_message_name;
828 ELSE
829 App_Exception.Raise_Exception;
830 END IF;
831 END crsp_ins_coo_rec;
832 -- procedure for inserting new IGS_PS_OFR_INST records
833 PROCEDURE crsp_ins_coi_rec (
834 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
835 p_new_version_number IGS_PS_VER.version_number%TYPE)
836 IS
837 BEGIN
838 DECLARE
839 CURSOR c_coo_new (
840 cp_cal_type IGS_PS_OFR_OPT.cal_type%TYPE,
841 cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
842 cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
843 cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE) IS
844 SELECT 'x'
845 FROM IGS_PS_OFR_OPT coo
846 WHERE coo.course_cd = p_new_course_cd AND
847 coo.version_number = p_new_version_number AND
848 coo.cal_type = cp_cal_type AND
849 coo.location_cd = cp_location_cd AND
850 coo.attendance_mode = cp_attendance_mode AND
851 coo.attendance_type = cp_attendance_type AND
852 coo.delete_flag = 'N';
853 v_dummy VARCHAR2(1);
854 BEGIN
855 x_rowid := NULL;
856 IGS_PS_OFR_INST_PKG.INSERT_ROW(
857 X_ROWID => x_rowid,
858 X_COURSE_CD => p_new_course_cd,
859 X_VERSION_NUMBER => p_new_version_number,
860 X_CAL_TYPE => gv_coi_rec.cal_type,
861 X_CI_SEQUENCE_NUMBER => gv_coi_rec.ci_sequence_number,
862 X_CI_START_DT => gv_coi_rec.ci_start_dt,
863 X_CI_END_DT => gv_coi_rec.ci_end_dt,
864 X_MIN_ENTRY_ASS_SCORE => gv_coi_rec.min_entry_ass_score,
865 X_GUARANTEED_ENTRY_ASS_SCR => gv_coi_rec.guaranteed_entry_ass_scr,
866 X_MODE => 'R' );
867 -- calling procedure to insert IGS_PS_OFR_PAT records
868 -- associated with each IGS_PS_OFR instance record
869 OPEN gc_cop_rec;
870 LOOP
871 FETCH gc_cop_rec INTO gv_cop_rec;
872 IF gc_cop_rec%FOUND THEN
873 -- check COO paraents exists
874 OPEN c_coo_new (
875 gv_cop_rec.cal_type,
876 gv_cop_rec.location_cd,
877 gv_cop_rec.attendance_mode,
878 gv_cop_rec.attendance_type);
879 FETCH c_coo_new INTO v_dummy;
880 IF c_coo_new%FOUND THEN
881 -- coo paraents exists then process this record
882 CLOSE c_coo_new;
883 crsp_ins_cop_rec(
884 p_new_course_cd,
885 p_new_version_number);
886 ELSE
887 p_message_name := cst_ret_message_name;
888 CLOSE c_coo_new;
889 END IF;
890 ELSE
891 EXIT;
892 END IF;
893 END LOOP;
894 CLOSE gc_cop_rec;
895 EXCEPTION
896 WHEN OTHERS THEN
897 IF c_coo_new%ISOPEN THEN
898 CLOSE c_coo_new;
899 END IF;
900 App_Exception.Raise_Exception;
901 END;
902 EXCEPTION
903 WHEN OTHERS THEN
904 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
905 p_message_name := cst_ret_message_name;
906 ELSE
907 App_Exception.Raise_Exception;
908 END IF;
909 END crsp_ins_coi_rec;
910 -- inserts into IGS_PS_PAT_OF_STUDY and its child tables including
911 -- IGS_PS_PAT_STUDY_PRD and IGS_PS_PAT_STUDY_UNT
912 PROCEDURE crspl_ins_pos_rec
913 IS
914 --WHo When WHAT
915 --sarakshi 15-May-2006 Bug#3460640,modified the call to IGS_PS_PAT_STUDY_PRD_PKG.INSERT_ROW with correct values
916 BEGIN
917 DECLARE
918 v_pos_seq_num IGS_PS_PAT_OF_STUDY.sequence_number%TYPE;
919 v_posp_seq_num IGS_PS_PAT_STUDY_PRD.sequence_number%TYPE;
920 v_posu_seq_num IGS_PS_PAT_STUDY_UNT.sequence_number%TYPE;
921 CURSOR c_pos_seq_num IS
922 SELECT IGS_PS_PAT_OF_STUDY_POS_NUM_S.NEXTVAL
923 FROM DUAL;
924 CURSOR c_posp_seq_num IS
925 SELECT IGS_PS_PAT_STUDY_UNT_POSPSEQ_S.NEXTVAL
926 FROM DUAL;
927 CURSOR c_posu_seq_num IS
928 SELECT IGS_PS_PAT_STUDY_UNT_SEQ_NUM_S.NEXTVAL
929 FROM DUAL;
930 CURSOR c_pos IS
931 SELECT pos.cal_type,
932 pos.sequence_number,
933 pos.location_cd,
934 pos.attendance_mode,
935 pos.attendance_type,
936 pos.unit_set_cd,
937 pos.admission_cal_type,
938 pos.admission_cat,
939 pos.aprvd_ci_sequence_number,
940 pos.number_of_periods,
941 pos.always_pre_enrol_ind,
942 pos.acad_perd_unit_set
943 FROM IGS_PS_PAT_OF_STUDY pos
944 WHERE pos.course_cd = p_old_course_cd AND
945 pos.version_number = p_old_version_number AND
946 pos.cal_type = gv_co_rec.cal_type;
947 CURSOR c_posp (
948 cp_pos_seq_num IGS_PS_PAT_OF_STUDY.sequence_number%TYPE) IS
949 SELECT posp.cal_type,
950 posp.pos_sequence_number,
951 posp.sequence_number,
952 posp.acad_period_num,
953 posp.teach_cal_type,
954 posp.description
955 FROM IGS_PS_PAT_STUDY_PRD posp
956 WHERE posp.course_cd = p_old_course_cd AND
957 posp.version_number = p_old_version_number AND
958 posp.cal_type = gv_co_rec.cal_type AND
959 posp.pos_sequence_number = cp_pos_seq_num;
960 CURSOR c_posu (
961 cp_pos_seq_num IGS_PS_PAT_OF_STUDY.sequence_number%TYPE,
962 cp_posp_seq_num IGS_PS_PAT_STUDY_PRD.sequence_number%TYPE) IS
963 SELECT posu.cal_type,
964 posu.pos_sequence_number,
965 posu.posp_sequence_number,
966 posu.sequence_number,
967 posu.unit_cd,
968 posu.unit_location_cd,
969 posu.unit_class,
970 posu.description,
971 posu.core_ind
972 FROM IGS_PS_PAT_STUDY_UNT posu
973 WHERE posu.course_cd = p_old_course_cd AND
974 posu.version_number = p_old_version_number AND
975 posu.cal_type = gv_co_rec.cal_type AND
976 posu.pos_sequence_number = cp_pos_seq_num AND
977 posu.posp_sequence_number = cp_posp_seq_num;
978 BEGIN
979 FOR v_pos_rec IN c_pos LOOP
980 BEGIN
981 OPEN c_pos_seq_num;
982 FETCH c_pos_seq_num INTO v_pos_seq_num;
983 CLOSE c_pos_seq_num;
984 X_ROWID := NULL;
985 IGS_PS_PAT_OF_STUDY_PKG.INSERT_ROW(
986 X_ROWID => x_rowid,
987 X_COURSE_CD => p_new_course_cd,
988 X_CAL_TYPE => v_pos_rec.cal_type,
989 X_VERSION_NUMBER => p_new_version_number,
990 X_SEQUENCE_NUMBER => v_pos_seq_num,
991 X_LOCATION_CD => v_pos_rec.location_cd,
992 X_ATTENDANCE_MODE => v_pos_rec.attendance_mode,
993 X_ATTENDANCE_TYPE => v_pos_rec.attendance_type,
994 X_UNIT_SET_CD => v_pos_rec.unit_set_cd,
995 X_ADMISSION_CAL_TYPE => v_pos_rec.admission_cal_type,
996 X_ADMISSION_CAT => v_pos_rec.admission_cat,
997 X_APRVD_CI_SEQUENCE_NUMBER => v_pos_rec.aprvd_ci_sequence_number,
998 X_NUMBER_OF_PERIODS => v_pos_rec.number_of_periods,
999 X_ALWAYS_PRE_ENROL_IND => v_pos_rec.always_pre_enrol_ind,
1000 X_acad_perd_unit_set => v_pos_rec.acad_perd_unit_set,
1001 X_MODE => 'R'
1002 );
1003 -- inserts into pattern_of_study_period table
1004 FOR v_posp_rec IN c_posp (
1005 v_pos_rec.sequence_number) LOOP
1006 OPEN c_posp_seq_num;
1007 FETCH c_posp_seq_num INTO v_posp_seq_num;
1008 CLOSE c_posp_seq_num;
1009 x_rowid := NULL;
1010 IGS_PS_PAT_STUDY_PRD_PKG.INSERT_ROW(
1011 X_ROWID => x_rowid,
1012 X_COURSE_CD => p_new_course_cd,
1013 X_VERSION_NUMBER => p_new_version_number,
1014 X_POS_SEQUENCE_NUMBER => v_pos_seq_num,--this is the FK
1015 X_SEQUENCE_NUMBER => v_posp_seq_num,--this is the PK
1016 X_CAL_TYPE => v_posp_rec.cal_type,
1017 X_ACAD_PERIOD_NUM => v_posp_rec.acad_period_num,
1018 X_TEACH_CAL_TYPE => v_posp_rec.teach_cal_type,
1019 X_DESCRIPTION => v_posp_rec.description,
1020 X_MODE => 'R'
1021 );
1022 -- inserts into IGS_PS_PAT_STUDY_UNT table
1023 FOR v_posu_rec IN c_posu (
1024 v_pos_rec.sequence_number,
1025 v_posp_rec.sequence_number) LOOP
1026 OPEN c_posu_seq_num;
1027 FETCH c_posu_seq_num INTO v_posu_seq_num;
1028 CLOSE c_posu_seq_num;
1029 x_rowid := NULL;
1030 IGS_PS_PAT_STUDY_UNT_PKG.INSERT_ROW(
1031 X_ROWID => x_rowid,
1032 X_COURSE_CD => p_new_course_cd,
1033 X_VERSION_NUMBER => p_new_version_number,
1034 X_POS_SEQUENCE_NUMBER => v_pos_seq_num,
1035 X_SEQUENCE_NUMBER => v_posu_seq_num,
1036 X_POSP_SEQUENCE_NUMBER => v_posp_seq_num,
1037 X_CAL_TYPE => v_posu_rec.cal_type,
1038 X_UNIT_CD => v_posu_rec.unit_cd,
1039 X_UNIT_LOCATION_CD => v_posu_rec.unit_location_cd,
1040 X_UNIT_CLASS => v_posu_rec.unit_class,
1041 X_DESCRIPTION => v_posu_rec.description,
1042 X_MODE => 'R',
1043 X_CORE_IND => v_posu_rec.core_ind
1044 );
1045 END LOOP; -- c_posu_rec
1046 END LOOP; -- c_posp_rec
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 IF SQLCODE >= cst_max_error_range AND
1050 SQLCODE <= cst_min_error_range THEN
1051 p_message_name := cst_ret_message_name;
1052 ELSE
1053 App_Exception.Raise_Exception;
1054 END IF;
1055 END;
1056 END LOOP; -- c_pos_rec
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 IF (c_pos%ISOPEN) THEN
1060 CLOSE c_pos;
1061 END IF;
1062 IF (c_posp%ISOPEN) THEN
1063 CLOSE c_posp;
1064 END IF;
1065 IF (c_posu%ISOPEN) THEN
1066 CLOSE c_posu;
1067 END IF;
1068 IF (c_pos_seq_num%ISOPEN) THEN
1069 CLOSE c_pos_seq_num;
1070 END IF;
1071 IF (c_posp_seq_num%ISOPEN) THEN
1072 CLOSE c_posp_seq_num;
1073 END IF;
1074 IF (c_posu_seq_num%ISOPEN) THEN
1075 CLOSE c_posu_seq_num;
1076 END IF;
1077 App_Exception.Raise_Exception;
1078 END;
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 IF SQLCODE >= cst_max_error_range AND
1082 SQLCODE <= cst_min_error_range THEN
1083 p_message_name := cst_ret_message_name;
1084 ELSE
1085 App_Exception.Raise_Exception;
1086 END IF;
1087 END crspl_ins_pos_rec;
1088
1089 PROCEDURE crspl_ins_cous_rec
1090 AS
1091 BEGIN
1092 DECLARE
1093 CURSOR c_cous IS
1094 SELECT cous.cal_type,
1095 cous.unit_set_cd,
1096 cous.us_version_number,
1097 cous.override_title,
1098 cous.only_as_sub_ind,
1099 cous.show_on_official_ntfctn_ind
1100 FROM IGS_PS_OFR_UNIT_SET cous
1101 WHERE cous.course_cd = p_old_course_cd AND
1102 cous.crv_version_number = p_old_version_number AND
1103 cous.cal_type = gv_co_rec.cal_type;
1104 CURSOR c_cousr IS
1105 SELECT cousr.cal_type,
1106 cousr.sup_unit_set_cd,
1107 cousr.sup_us_version_number,
1108 cousr.sub_unit_set_cd,
1109 cousr.sub_us_version_number
1110 FROM IGS_PS_OF_UNT_SET_RL cousr,
1111 IGS_PS_OFR_UNIT_SET cous_sup,
1112 IGS_PS_OFR_UNIT_SET cous_sub
1113 WHERE cousr.course_cd = p_old_course_cd AND
1114 cousr.crv_version_number = p_old_version_number AND
1115 cousr.cal_type = gv_co_rec.cal_type AND
1116 cousr.cal_type = cous_sub.cal_type AND
1117 cousr.sub_unit_set_cd = cous_sub.unit_set_cd AND
1118 cousr.sub_us_version_number = cous_sub.us_version_number AND
1119 cousr.course_cd = cous_sub.course_cd AND
1120 cousr.crv_version_number = cous_sub.crv_version_number AND
1121 cousr.sup_unit_set_cd = cous_sup.unit_set_cd AND
1122 cousr.sup_us_version_number = cous_sup.us_version_number AND
1123 cousr.course_cd = cous_sup.course_cd AND
1124 cousr.crv_version_number = cous_sup.crv_version_number AND
1125 cousr.cal_type = cous_sup.cal_type AND
1126 EXISTS (SELECT 'X'
1127 FROM IGS_PS_OFR_UNIT_SET cous
1128 WHERE cous.course_cd = p_new_course_cd AND
1129 cous.crv_version_number = p_new_version_number AND
1130 cous.cal_type = cous_sup.cal_type AND
1131 cous.unit_set_cd = cous_sup.unit_set_cd AND
1132 cous.us_version_number = cous_sup.us_version_number) AND
1133 EXISTS (SELECT 'X'
1134 FROM IGS_PS_OFR_UNIT_SET cous
1135 WHERE cous.course_cd = p_new_course_cd AND
1136 cous.crv_version_number = p_new_version_number AND
1137 cous.cal_type = cous_sub.cal_type AND
1138 cous.unit_set_cd = cous_sub.unit_set_cd AND
1139 cous.us_version_number = cous_sub.us_version_number);
1140 l_org_id NUMBER(15);
1141 BEGIN
1142 FOR v_cous_rec IN c_cous LOOP
1143 BEGIN
1144 x_rowid := NULL;
1145 IGS_PS_OFR_UNIT_SET_PKG.INSERT_ROW(
1146 X_ROWID => x_rowid,
1147 X_COURSE_CD => p_new_course_cd,
1148 X_CRV_VERSION_NUMBER => p_new_version_number,
1149 X_CAL_TYPE => v_cous_rec.cal_type,
1150 X_UNIT_SET_CD => v_cous_rec.unit_set_cd,
1151 X_US_VERSION_NUMBER => v_cous_rec.us_version_number,
1152 X_OVERRIDE_TITLE => v_cous_rec.override_title,
1153 X_ONLY_AS_SUB_IND => v_cous_rec.only_as_sub_ind,
1154 X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_cous_rec.show_on_official_ntfctn_ind,
1155 X_MODE => 'R'
1156 );
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 IF SQLCODE >= cst_max_error_range AND
1160 SQLCODE <= cst_min_error_range THEN
1161 p_message_name := cst_ret_message_name;
1162 ELSE
1163 App_Exception.Raise_Exception;
1164 END IF;
1165 END;
1166 END LOOP; -- c_cous
1167 FOR v_cousr_rec IN c_cousr LOOP
1168 X_ROWID := NULL;
1169 IGS_PS_OF_UNT_SET_RL_PKG.INSERT_ROW(
1170 X_ROWID => x_rowid,
1171 X_COURSE_CD => p_new_course_cd,
1172 X_CRV_VERSION_NUMBER => p_new_version_number,
1173 X_SUP_US_VERSION_NUMBER => v_cousr_rec.sup_us_version_number,
1174 X_SUB_UNIT_SET_CD => v_cousr_rec.sub_unit_set_cd,
1175 X_SUP_UNIT_SET_CD => v_cousr_rec.sup_unit_set_cd,
1176 X_CAL_TYPE => v_cousr_rec.cal_type,
1177 X_SUB_US_VERSION_NUMBER => v_cousr_rec.sub_us_version_number,
1178 X_MODE => 'R'
1179 );
1180 END LOOP; -- cousr_sub
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 IF (c_cous%ISOPEN) THEN
1184 CLOSE c_cous;
1185 END IF;
1186 IF (c_cousr%ISOPEN) THEN
1187 CLOSE c_cousr;
1188 END IF;
1189 App_Exception.Raise_Exception;
1190 END;
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 IF SQLCODE >= cst_max_error_range AND
1194 SQLCODE <= cst_min_error_range THEN
1195 p_message_name := cst_ret_message_name;
1196 ELSE
1197 App_Exception.Raise_Exception;
1198 END IF;
1199 END crspl_ins_cous_rec;
1200 -- procedure for inserting new IGS_PS_OFR records
1201 PROCEDURE crsp_ins_co_rec (
1202 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1203 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1204
1205 l_org_id NUMBER(15);
1206 BEGIN
1207 X_ROWID := NULL;
1208 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
1209 IGS_PS_OFR_PKG.INSERT_ROW(
1210 X_ROWID => x_rowid,
1211 X_COURSE_CD => p_new_course_cd,
1212 X_VERSION_NUMBER => p_new_version_number,
1213 X_CAL_TYPE => gv_co_rec.cal_type,
1214 X_ATTRIBUTE_CATEGORY => gv_co_rec.attribute_category,
1215 X_ATTRIBUTE1 => gv_co_rec.attribute1,
1216 X_ATTRIBUTE2 => gv_co_rec.attribute2,
1217 X_ATTRIBUTE3 => gv_co_rec.attribute3,
1218 X_ATTRIBUTE4 => gv_co_rec.attribute4,
1219 X_ATTRIBUTE5 => gv_co_rec.attribute5,
1220 X_ATTRIBUTE6 => gv_co_rec.attribute6,
1221 X_ATTRIBUTE7 => gv_co_rec.attribute7,
1222 X_ATTRIBUTE8 => gv_co_rec.attribute8,
1223 X_ATTRIBUTE9 => gv_co_rec.attribute9,
1224 X_ATTRIBUTE10 => gv_co_rec.attribute10,
1225 X_ATTRIBUTE11 => gv_co_rec.attribute11,
1226 X_ATTRIBUTE12 => gv_co_rec.attribute12,
1227 X_ATTRIBUTE13 => gv_co_rec.attribute13,
1228 X_ATTRIBUTE14 => gv_co_rec.attribute14,
1229 X_ATTRIBUTE15 => gv_co_rec.attribute15,
1230 X_ATTRIBUTE16 => gv_co_rec.attribute16,
1231 X_ATTRIBUTE17 => gv_co_rec.attribute17,
1232 X_ATTRIBUTE18 => gv_co_rec.attribute18,
1233 X_ATTRIBUTE19 => gv_co_rec.attribute19,
1234 X_ATTRIBUTE20 => gv_co_rec.attribute20,
1235 X_MODE => 'R' ,
1236 X_ORG_ID => l_org_id
1237 );
1238 -- calling procedure to insert IGS_PS_OFR_UNIT_SET records
1239 -- and its child tables IGS_PS_OF_OPT_UNT_ST and
1240 -- IGS_PS_OF_UNT_SET_RL
1241 crspl_ins_cous_rec;
1242 -- calling procedure to insert IGS_PS_OFR_OPT records
1243 -- associated with each IGS_PS_OFR record
1244 OPEN gc_coo_rec;
1245 LOOP
1246 FETCH gc_coo_rec INTO gv_coo_rec;
1247 IF gc_coo_rec%FOUND THEN
1248 crsp_ins_coo_rec(p_new_course_cd, p_new_version_number);
1249 ELSE
1250 EXIT;
1251 END IF;
1252 END LOOP;
1253 CLOSE gc_coo_rec;
1254 -- calling procedure to insert IGS_PS_OFR_INST records
1255 -- associated with each IGS_PS_OFR record
1256 OPEN gc_coi_rec;
1257 FETCH gc_coi_rec INTO gv_coi_rec;
1258 IF gc_coi_rec%FOUND THEN
1259 -- if found, only the latest one needs to be copied over.
1260 crsp_ins_coi_rec(p_new_course_cd, p_new_version_number);
1261 END IF;
1262 CLOSE gc_coi_rec;
1263 -- calling procedure to insert IGS_PS_OFR_NOTE records
1264 -- associated with each IGS_PS_OFR record
1265 OPEN gc_con_rec;
1266 LOOP
1267 FETCH gc_con_rec INTO gv_con_rec;
1268 IF gc_con_rec%FOUND THEN
1269 crsp_ins_con_rec(p_new_course_cd, p_new_version_number);
1270 ELSE
1271 EXIT;
1272 END IF;
1273 END LOOP;
1274 CLOSE gc_con_rec;
1275 -- calling procedure to insert IGS_PS_PAT_OF_STUDY records
1276 crspl_ins_pos_rec;
1277 EXCEPTION
1278 WHEN OTHERS THEN
1279 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1280 p_message_name := cst_ret_message_name;
1281 ELSE
1282 App_Exception.Raise_Exception;
1283 END IF;
1284 END crsp_ins_co_rec;
1285 -- procedure for inserting new IGS_PS_AWD_OWN records
1286 PROCEDURE crsp_ins_cao_rec (
1287 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1288 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1289 BEGIN
1290 X_ROWID := NULL ;
1291 IGS_PS_AWD_OWN_PKG.INSERT_ROW(
1292 X_ROWID => x_rowid,
1293 X_COURSE_CD => p_new_course_cd,
1294 X_ORG_UNIT_CD => gv_cao_rec.org_unit_cd,
1295 X_OU_START_DT => gv_cao_rec.ou_start_dt,
1296 X_AWARD_CD => gv_cao_rec.award_cd,
1297 X_VERSION_NUMBER => p_new_version_number,
1298 X_PERCENTAGE => gv_cao_rec.percentage,
1299 X_MODE => 'R'
1300 );
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1304 p_message_name := cst_ret_message_name;
1305 ELSE
1306 App_Exception.Raise_Exception;
1307 END IF;
1308 END crsp_ins_cao_rec;
1309 -- procedure for inserting new IGS_PS_ANL_LOAD_U_LN records
1310 PROCEDURE crsp_ins_calulink_rec (
1311 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1312 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1313 BEGIN
1314 x_rowid := NULL;
1315 IGS_PS_ANL_LOAD_U_LN_PKG.INSERT_ROW(
1316 X_ROWID => x_rowid,
1317 X_COURSE_CD => p_new_course_cd,
1318 X_CRV_VERSION_NUMBER => p_new_version_number,
1319 X_EFFECTIVE_START_DT => gv_calulink_rec.effective_start_dt,
1320 X_YR_NUM => gv_calulink_rec.yr_num,
1321 X_UV_VERSION_NUMBER => gv_calulink_rec.uv_version_number,
1322 X_UNIT_CD => gv_calulink_rec.unit_cd,
1323 X_MODE => 'R'
1324 );
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1328 p_message_name := cst_ret_message_name;
1329 ELSE
1330 App_Exception.Raise_Exception;
1331 END IF;
1332 END crsp_ins_calulink_rec;
1333 -- procedure for inserting new IGS_PS_AWARD records
1334 PROCEDURE crsp_ins_ca_rec (
1335 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1336 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1337 BEGIN
1338 -- Bug#2983445 Removed the column award_title from call to igs_ps_award_pkg.insert_row.
1339 -- inserting IGS_PS_AWARD records
1340
1341 -- Bug#3060693 Modified the call to IGS_PS_AWARD_PKG.INSERT_ROW.
1342 -- Replaced the string 'Y' with gv_ca_rec.default_ind as the parameter being passed to X_DEFAULT_IND
1343
1344
1345 x_rowid := NULL;
1346 IGS_PS_AWARD_PKG.INSERT_ROW(
1347 X_ROWID => x_rowid,
1348 X_COURSE_CD => p_new_course_cd,
1349 X_AWARD_CD => gv_ca_rec.award_cd,
1350 X_VERSION_NUMBER => p_new_version_number,
1351 X_MODE => 'R',
1352 X_DEFAULT_IND => gv_ca_rec.default_ind,
1353 X_CLOSED_IND => gv_ca_rec.closed_ind
1354 );
1355 -- calling procedure to insert IGS_PS_AWD_OWN records
1356 -- associated with each IGS_PS_AWARD record
1357 OPEN gc_cao_rec;
1358 LOOP
1359 FETCH gc_cao_rec INTO gv_cao_rec;
1360 IF gc_cao_rec%FOUND THEN
1361 crsp_ins_cao_rec(p_new_course_cd, p_new_version_number);
1362 ELSE
1363 EXIT;
1364 END IF;
1365 END LOOP;
1366 CLOSE gc_cao_rec;
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1370 p_message_name := cst_ret_message_name;
1371 ELSE
1372 App_Exception.Raise_Exception;
1373 END IF;
1374 END crsp_ins_ca_rec;
1375 -- procedure for inserting new IGS_PS_OWN records
1376 PROCEDURE crsp_ins_cow_rec (
1377 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1378 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1379 BEGIN
1380 X_ROWID := NULL ;
1381 IGS_PS_OWN_PKG.INSERT_ROW(
1382 X_ROWID => x_rowid,
1383 X_COURSE_CD => p_new_course_cd,
1384 X_OU_START_DT => gv_cow_rec.ou_start_dt,
1385 X_ORG_UNIT_CD => gv_cow_rec.org_unit_cd,
1386 X_VERSION_NUMBER => p_new_version_number,
1387 X_PERCENTAGE => gv_cow_rec.percentage,
1388 X_MODE => 'R'
1389 );
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1393 p_message_name := cst_ret_message_name;
1394 ELSE
1395 App_Exception.Raise_Exception;
1396 END IF;
1397 END crsp_ins_cow_rec;
1398 -- procedure for inserting new IGS_PE_ALTERNATV_EXT records
1399 PROCEDURE crsp_ins_ae_rec (
1400 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1401 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1402 BEGIN
1403 X_ROWID := NULL;
1404 IGS_PE_ALTERNATV_EXT_PKG.INSERT_ROW(
1405 X_ROWID => X_ROWID,
1406 X_COURSE_CD => p_new_course_cd,
1407 X_VERSION_NUMBER => p_new_version_number,
1408 X_EXIT_COURSE_CD => gv_ae_rec.exit_course_cd,
1409 X_EXIT_VERSION_SET => gv_ae_rec.exit_version_set,
1410 X_MODE => 'R'
1411 );
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1415 p_message_name := cst_ret_message_name;
1416 ELSE
1417 App_Exception.Raise_Exception;
1418 END IF;
1419 END crsp_ins_ae_rec;
1420 -- procedure for inserting new IGS_PS_GRP_MBR records
1421 PROCEDURE crsp_ins_cgm_rec (
1422 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1423 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1424 BEGIN
1425 x_rowid := NULL;
1426 IGS_PS_GRP_MBR_PKG.INSERT_ROW(
1427 X_ROWID => x_rowid,
1428 X_COURSE_CD => p_new_course_cd,
1429 X_COURSE_GROUP_CD => gv_cgm_rec.course_group_cd,
1430 X_VERSION_NUMBER => p_new_version_number,
1431 X_MODE => 'R'
1432 );
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1436 p_message_name := cst_ret_message_name;
1437 ELSE
1438 App_Exception.Raise_Exception;
1439 END IF;
1440 END crsp_ins_cgm_rec;
1441
1442 -- procedure for inserting new IGS_FI_FND_SRC_RSTN records
1443
1444 -- Bug#3060697 Modified the call to IGS_FI_FND_SRC_RSTN_PKG.INSERT_ROW.
1445 -- Replaced the string 'Y' with gv_fsr_rec.restricted_ind as the parameter being passed to X_RESTRICTED_IND
1446
1447
1448 PROCEDURE crsp_ins_fsr_rec (
1449 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1450 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1451 BEGIN
1452 x_rowid := NULL;
1453 IGS_FI_FND_SRC_RSTN_PKG.INSERT_ROW(
1454 X_ROWID => x_rowid,
1455 X_COURSE_CD => p_new_course_cd,
1456 X_FUNDING_SOURCE => gv_fsr_rec.funding_source,
1457 X_VERSION_NUMBER => p_new_version_number,
1458 X_DFLT_IND => gv_fsr_rec.dflt_ind,
1459 X_RESTRICTED_IND => gv_fsr_rec.restricted_ind,
1460 X_MODE => 'R'
1461 );
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1465 p_message_name := cst_ret_message_name;
1466 ELSE
1467 App_Exception.Raise_Exception;
1468 END IF;
1469 END crsp_ins_fsr_rec;
1470 -- procedure for inserting new IGS_PS_FIELD_STUDY records
1471 PROCEDURE crsp_ins_cfos_rec (
1472 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1473 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1474 BEGIN
1475 x_rowid := NULL;
1476 IGS_PS_FIELD_STUDY_PKG.INSERT_ROW(
1477 X_ROWID => x_rowid,
1478 X_COURSE_CD => p_new_course_cd,
1479 X_FIELD_OF_STUDY => gv_cfos_rec.field_of_study,
1480 X_VERSION_NUMBER => p_new_version_number,
1481 X_MAJOR_FIELD_IND => gv_cfos_rec.major_field_ind,
1482 X_PERCENTAGE => gv_cfos_rec.percentage,
1483 X_MODE => 'R'
1484 );
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1488 p_message_name := cst_ret_message_name;
1489 ELSE
1490 App_Exception.Raise_Exception;
1491 END IF;
1492 END crsp_ins_cfos_rec;
1493 -- procedure for inserting new IGS_PS_CATEGORISE records
1494 PROCEDURE crsp_ins_ccat_rec (
1495 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1496 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1497
1498 l_org_id NUMBER(15);
1499 BEGIN
1500 x_rowid := NULL;
1501 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
1502 IGS_PS_CATEGORISE_PKG.INSERT_ROW(
1503 X_ROWID => x_rowid,
1504 X_COURSE_CD => p_new_course_cd,
1505 X_VERSION_NUMBER => p_new_version_number,
1506 X_COURSE_CAT => gv_ccat_rec.course_cat,
1507 X_MODE => 'R',
1508 X_ORG_ID => l_org_id
1509 );
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1513 p_message_name := cst_ret_message_name;
1514 ELSE
1515 App_Exception.Raise_Exception;
1516 END IF;
1517 END crsp_ins_ccat_rec;
1518 -- procedure for inserting new IGS_PS_REF_CD records
1519 PROCEDURE crsp_ins_crcd_rec (
1520 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1521 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1522 BEGIN
1523 x_rowid := NULL;
1524 IGS_PS_REF_CD_PKG.INSERT_ROW(
1525 X_ROWID => x_rowid,
1526 X_COURSE_CD => p_new_course_cd,
1527 X_VERSION_NUMBER => p_new_version_number,
1528 X_REFERENCE_CD => gv_crcd_rec.reference_cd,
1529 X_REFERENCE_CD_TYPE => gv_crcd_rec.reference_cd_type,
1530 X_DESCRIPTION => gv_crcd_rec.description,
1531 X_MODE => 'R'
1532 );
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1536 p_message_name := cst_ret_message_name;
1537 ELSE
1538 App_Exception.Raise_Exception;
1539 END IF;
1540 END crsp_ins_crcd_rec;
1541 -- procedure for inserting new IGS_PS_ANL_LOAD records
1542 PROCEDURE crsp_ins_cal_rec (
1543 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1544 p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1545 BEGIN
1546 x_rowid := NULL;
1547 IGS_PS_ANL_LOAD_PKG.INSERT_ROW(
1548 X_ROWID => x_rowid,
1549 X_VERSION_NUMBER => p_new_version_number,
1550 X_COURSE_CD => p_new_course_cd,
1551 X_YR_NUM => gv_cal_rec.yr_num,
1552 X_EFFECTIVE_START_DT => gv_cal_rec.effective_start_dt,
1553 X_EFFECTIVE_END_DT => gv_cal_rec.effective_end_dt,
1554 X_ANNUAL_LOAD_VAL => gv_cal_rec.annual_load_val,
1555 X_MODE => 'R'
1556 );
1557 -- calling procedure to insert IGS_PS_ANL_LOAD_U_LN records
1558 -- associated with each IGS_PS_ANL_LOAD record
1559 OPEN gc_calulink_rec;
1560 LOOP
1561 FETCH gc_calulink_rec INTO gv_calulink_rec;
1562 IF gc_calulink_rec%FOUND THEN
1563 crsp_ins_calulink_rec(p_new_course_cd, p_new_version_number);
1564 ELSE
1565 EXIT;
1566 END IF;
1567 END LOOP;
1568 CLOSE gc_calulink_rec;
1569 EXCEPTION
1570 WHEN OTHERS THEN
1571 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1572 p_message_name := cst_ret_message_name;
1573 ELSE
1574 App_Exception.Raise_Exception;
1575 END IF;
1576 END crsp_ins_cal_rec;
1577 -- inserting new IGS_GE_NOTE and IGS_PS_VER_NOTE records
1578 PROCEDURE crsp_ins_cvn_rec (
1579 p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1580 p_new_version_number IGS_PS_VER.version_number%TYPE)
1581 AS
1582 CURSOR Cur_SGN IS
1583 SELECT rowid,IGS_GE_NOTE.*
1584 FROM IGS_GE_NOTE
1585 WHERE reference_number = gv_cvn_rec.reference_number;
1586 BEGIN
1587 -- select the next reference_number from the system
1588 OPEN gc_ref_num;
1589 FETCH gc_ref_num INTO gv_ref_num;
1590 CLOSE gc_ref_num;
1591 -- inserting IGS_GE_NOTE record with this next reference_number
1592 -- Currently unable to copy Long Raw columns in PL/SQL.
1593
1594
1595 FOR Rec_SGN IN Cur_SGN LOOP
1596 x_rowid := NULL;
1597 IGS_GE_NOTE_PKG.INSERT_ROW(
1598 X_ROWID => X_ROWID,
1599 X_REFERENCE_NUMBER =>gv_ref_num,
1600 X_S_NOTE_FORMAT_TYPE => Rec_SGN.s_note_format_type,
1601 X_NOTE_TEXT =>Rec_SGN.note_text,
1602 X_MODE =>'R');
1603 END LOOP;
1604 -- inserting new IGS_PS_VER_NOTE records
1605 x_rowid := NULL;
1606 IGS_PS_VER_NOTE_PKG.INSERT_ROW(
1607 X_ROWID => x_rowid,
1608 X_COURSE_CD => p_new_course_cd,
1609 X_VERSION_NUMBER => p_new_version_number,
1610 X_REFERENCE_NUMBER => gv_ref_num,
1611 X_CRS_NOTE_TYPE => gv_cvn_rec.crs_note_type,
1612 X_MODE => 'R'
1613 );
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
1617 p_message_name := cst_ret_message_name;
1618 ELSE
1619 App_Exception.Raise_Exception;
1620 END IF;
1621 END crsp_ins_cvn_rec;
1622 -- inserts into IGS_PS_STAGE and its child table IGS_PS_STAGE_RU table
1623 PROCEDURE crspl_ins_cst_rec
1624 AS
1625 BEGIN
1626 DECLARE
1627 v_cst_seq_num IGS_PS_STAGE.sequence_number%TYPE;
1628 v_new_rul_seq_num IGS_PS_STAGE_RU.rul_sequence_number%TYPE;
1629 CURSOR c_cst_seq_num IS
1630 SELECT IGS_PS_STAGE_SEQ_NUM_S.NEXTVAL
1631 FROM DUAL;
1632 CURSOR c_cst IS
1633 SELECT cst.sequence_number,
1634 cst.course_stage_type,
1635 cst.description,
1636 cst.comments
1637 FROM IGS_PS_STAGE cst
1638 WHERE cst.course_cd = p_old_course_cd AND
1639 cst.version_number = p_old_version_number;
1640 CURSOR c_csr (
1641 cp_cst_seq_num IGS_PS_STAGE.sequence_number%TYPE) IS
1642 SELECT csr.cst_sequence_number,
1643 csr.s_rule_call_cd,
1644 csr.rul_sequence_number
1645 FROM IGS_PS_STAGE_RU csr
1646 WHERE csr.course_cd = p_old_course_cd AND
1647 csr.version_number = p_old_version_number AND
1648 csr.cst_sequence_number = cp_cst_seq_num;
1649 BEGIN
1650 FOR v_cst_rec IN c_cst LOOP
1651 BEGIN
1652 OPEN c_cst_seq_num;
1653 FETCH c_cst_seq_num INTO v_cst_seq_num;
1654 CLOSE c_cst_seq_num;
1655 x_rowid := NULL;
1656 IGS_PS_STAGE_PKG.INSERT_ROW(
1657 X_ROWID => x_rowid,
1658 X_COURSE_CD => p_new_course_cd,
1659 X_VERSION_NUMBER => p_new_version_number,
1660 X_SEQUENCE_NUMBER => v_cst_seq_num,
1661 X_COURSE_STAGE_TYPE => v_cst_rec.course_stage_type,
1662 X_DESCRIPTION => v_cst_rec.description,
1663 X_COMMENTS => v_cst_rec.comments,
1664 X_MODE => 'R'
1665 );
1666 FOR v_csr_rec IN c_csr (
1667 v_cst_rec.sequence_number) LOOP
1668 v_new_rul_seq_num := IGS_RU_GEN_003.rulp_ins_copy_rule(
1669 v_csr_rec.s_rule_call_cd,
1670 v_csr_rec.rul_sequence_number);
1671 X_ROWID := NULL;
1672 IGS_PS_STAGE_RU_PKG.INSERT_ROW(
1673 X_ROWID => x_rowid,
1674 X_COURSE_CD => p_new_course_cd,
1675 X_VERSION_NUMBER => p_new_version_number,
1676 X_S_RULE_CALL_CD => v_csr_rec.s_rule_call_cd,
1677 X_CST_SEQUENCE_NUMBER => v_cst_seq_num,
1678 X_RUL_SEQUENCE_NUMBER => v_new_rul_seq_num,
1679 X_MODE => 'R'
1680 );
1681 END LOOP;
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 IF SQLCODE >= cst_max_error_range AND
1685 SQLCODE <= cst_min_error_range THEN
1686 p_message_name := cst_ret_message_name;
1687 ELSE
1688 App_Exception.Raise_Exception;
1689 END IF;
1690 END;
1691 END LOOP;
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 IF (c_cst%ISOPEN) THEN
1695 CLOSE c_cst;
1696 END IF;
1697 IF (c_csr%ISOPEN) THEN
1698 CLOSE c_csr;
1699 END IF;
1700 IF (c_cst_seq_num%ISOPEN) THEN
1701 CLOSE c_cst_seq_num;
1702 END IF;
1703 App_Exception.Raise_Exception;
1704 END;
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707 IF SQLCODE >= cst_max_error_range AND
1708 SQLCODE <= cst_min_error_range THEN
1709 p_message_name := cst_ret_message_name;
1710 ELSE
1711 App_Exception.Raise_Exception;
1712 END IF;
1713 END crspl_ins_cst_rec;
1714 --------------------------------------------------------------------
1715 PROCEDURE crspl_ins_cvr_rec
1716 AS
1717 BEGIN
1718 DECLARE
1719 v_new_rul_seq_number IGS_PS_VER_RU.rul_sequence_number%TYPE;
1720 CURSOR c_cvr IS
1721 SELECT cvr.s_rule_call_cd,
1722 cvr.rul_sequence_number
1723 FROM IGS_PS_VER_RU cvr
1724 WHERE cvr.course_cd = p_old_course_cd AND
1725 cvr.version_number = p_old_version_number;
1726 BEGIN
1727 FOR v_cvr_rec IN c_cvr LOOP
1728 BEGIN
1729 v_new_rul_seq_number := IGS_RU_GEN_003.rulp_ins_copy_rule(
1730 v_cvr_rec.s_rule_call_cd,
1731 v_cvr_rec.rul_sequence_number);
1732 x_rowid := NULL;
1733 IGS_PS_VER_RU_PKG.INSERT_ROW(
1734 X_ROWID => x_rowid,
1735 X_COURSE_CD => p_new_course_cd,
1736 X_VERSION_NUMBER => p_new_version_number,
1737 X_S_RULE_CALL_CD => v_cvr_rec.s_rule_call_cd,
1738 X_RUL_SEQUENCE_NUMBER => v_new_rul_seq_number,
1739 X_MODE => 'R'
1740 );
1741 EXCEPTION
1742 WHEN OTHERS THEN
1743 IF SQLCODE >= cst_max_error_range AND
1744 SQLCODE <= cst_min_error_range THEN
1745 p_message_name := cst_ret_message_name;
1746 ELSE
1747 App_Exception.Raise_Exception;
1748 END IF;
1749 END;
1750 END LOOP;
1751 EXCEPTION
1752 WHEN OTHERS THEN
1753 IF (c_cvr%ISOPEN) THEN
1754 CLOSE c_cvr;
1755 END IF;
1756 App_Exception.Raise_Exception;
1757 END;
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 IF SQLCODE >= cst_max_error_range AND
1761 SQLCODE <= cst_min_error_range THEN
1762 p_message_name := cst_ret_message_name;
1763 ELSE
1764 App_Exception.Raise_Exception;
1765 END IF;
1766 END crspl_ins_cvr_rec;
1767 -----------------------------------------------------------
1768 PROCEDURE crspl_ins_dms_rec
1769 AS
1770
1771 BEGIN
1772 DECLARE
1773 v_seq_num IGS_RE_DFLT_MS_SET.sequence_number%TYPE;
1774 l_org_id NUMBER(15);
1775 CURSOR c_dms IS
1776 SELECT dms.milestone_type,
1777 dms.attendance_type,
1778 dms.attendance_mode,
1779 dms.offset_days,
1780 dms.comments
1781 FROM IGS_RE_DFLT_MS_SET dms
1782 WHERE dms.course_cd = p_old_course_cd AND
1783 dms.version_number = p_old_version_number;
1784 CURSOR c_dms_seq IS
1785 SELECT IGS_RE_DFLT_MS_SET_SEQ_NUM_S.NEXTVAL
1786 FROM DUAL;
1787 BEGIN
1788 FOR v_dms_rec IN c_dms LOOP
1789 BEGIN
1790 OPEN c_dms_seq;
1791 FETCH c_dms_seq INTO v_seq_num;
1792 CLOSE c_dms_seq;
1793 x_rowid := NULL;
1794 l_org_id := IGS_GE_GEN_003.get_org_id;
1795 IGS_RE_DFLT_MS_SET_PKG.INSERT_ROW(
1796 X_ROWID => x_rowid,
1797 X_COURSE_CD => p_new_course_cd,
1798 X_VERSION_NUMBER => p_new_version_number,
1799 X_MILESTONE_TYPE => v_dms_rec.milestone_type,
1800 X_ATTENDANCE_TYPE => v_dms_rec.attendance_type,
1801 X_ATTENDANCE_MODE => v_dms_rec.attendance_mode,
1802 X_SEQUENCE_NUMBER => v_seq_num,
1803 X_OFFSET_DAYS => v_dms_rec.offset_days,
1804 X_COMMENTS => v_dms_rec.comments,
1805 X_MODE => 'R' ,
1806 X_ORG_ID => l_org_id
1807 );
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 IF SQLCODE >= cst_max_error_range AND
1811 SQLCODE <= cst_min_error_range THEN
1812 p_message_name := cst_ret_message_name;
1813 ELSE
1814 App_Exception.Raise_Exception;
1815 END IF;
1816 END;
1817 END LOOP;
1818 EXCEPTION
1819 WHEN OTHERS THEN
1820 IF c_dms%ISOPEN THEN
1821 CLOSE c_dms;
1822 END IF;
1823 IF c_dms_seq%ISOPEN THEN
1824 CLOSE c_dms_seq;
1825 END IF;
1826 App_Exception.Raise_Exception;
1827 END;
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1831 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_001.crspl_ins_dms_rec');
1832 IGS_GE_MSG_STACK.ADD;
1833 App_Exception.Raise_Exception;
1834 END crspl_ins_dms_rec;
1835
1836 PROCEDURE crsp_ins_term_instr_time AS
1837 CURSOR c_psv_term IS
1838 SELECT *
1839 FROM igs_en_psv_term_it
1840 WHERE course_cd = p_old_course_cd
1841 AND version_number = p_old_version_number;
1842 l_rowid VARCHAR2(25);
1843
1844 BEGIN
1845 --Rolllover the term instruction time from one version of program to another when duplicate record is done
1846 FOR l_psv_term_rec IN c_psv_term LOOP
1847 BEGIN
1848 l_rowid := NULL;
1849 igs_en_psv_term_it_pkg.insert_row(x_rowid => l_rowid,
1850 x_cal_type => l_psv_term_rec.cal_type,
1851 x_sequence_number => l_psv_term_rec.sequence_number,
1852 x_course_cd => p_new_course_cd,
1853 x_version_number => p_new_version_number,
1854 x_term_instruction_time => l_psv_term_rec.term_instruction_time);
1855 EXCEPTION
1856 WHEN OTHERS THEN
1857 IF SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range THEN
1858 p_message_name := cst_ret_message_name;
1859 ELSE
1860 app_exception.raise_exception;
1861 END IF;
1862 END;
1863 END LOOP;
1864
1865 END crsp_ins_term_instr_time;
1866
1867 -- Removed procedure crsp_ins_revseg_rec as part of Enh# 2831572.
1868 -- As per this Enh#, Revenue Account Segments are not rolled over to new program version.
1869
1870 ----------------------------------------MAIN------------------------------
1871 BEGIN -- main procedure
1872 -- This procedure is responsible for transferring all the details of one
1873 -- IGS_PS_COURSE version to another. It gets records (old version_number) and
1874 -- transfers (duplicates) them all over to a new version_number. An
1875 -- exception handler is raised when an error number is found to be in the
1876 -- range -20000 to -20999 (which indicates that the exception is user
1877 -- defined - one of the validation routines within the system). If not
1878 -- within this range, it will be raised by standard exception handling.
1879 -- If the insertion of a IGS_PS_COURSE IGS_GE_NOTE fails, the associated IGS_GE_NOTE is removed.
1880 -- IGS_GE_NOTE: If any tables to be added, be careful to ensure relational integrity
1881 -- is preserved. Please check that all foreign keys are catered for.
1882 -- (For example, IGS_PS_OF_OPT_UNT_ST relies on
1883 -- IGS_PS_OFR_UNIT_SET and IGS_PS_OFR_OPT populated first.
1884 -- Only then can the records be created for the new version provided
1885 -- the parent records exist.)
1886 -- This checks if the specified new IGS_PS_COURSE version exists
1887
1888 l_status := NULL;
1889
1890 OPEN gc_cv_new_rec;
1891 FETCH gc_cv_new_rec INTO gv_cv_rec;
1892 IF gc_cv_new_rec%NOTFOUND THEN
1893 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1894 CLOSE gc_cv_new_rec;
1895 RETURN;
1896 END IF;
1897 CLOSE gc_cv_new_rec;
1898 -- This checks if the specified old IGS_PS_COURSE version exists
1899 OPEN gc_cv_old_rec;
1900 FETCH gc_cv_old_rec INTO gv_cv_rec;
1901 IF gc_cv_old_rec%NOTFOUND THEN
1902 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1903 CLOSE gc_cv_old_rec;
1904 RETURN;
1905 END IF;
1906 CLOSE gc_cv_old_rec;
1907 -- if validation is successful
1908 p_message_name := 'IGS_PS_SUCCESS_COPY_PRGVER';
1909 -- calling procedure to insert IGS_PS_AWARD records
1910 OPEN gc_ca_rec;
1911 LOOP
1912 FETCH gc_ca_rec INTO gv_ca_rec;
1913 IF gc_ca_rec%FOUND THEN
1914 crsp_ins_ca_rec(p_new_course_cd, p_new_version_number);
1915 ELSE
1916 EXIT;
1917 END IF;
1918 END LOOP;
1919 CLOSE gc_ca_rec;
1920 -- calling procedure to insert IGS_PS_OWN records
1921 OPEN gc_cow_rec;
1922 LOOP
1923 FETCH gc_cow_rec INTO gv_cow_rec;
1924 IF gc_cow_rec%FOUND THEN
1925 crsp_ins_cow_rec(p_new_course_cd, p_new_version_number);
1926 ELSE
1927 EXIT;
1928 END IF;
1929 END LOOP;
1930 CLOSE gc_cow_rec;
1931 -- calling procedure to insert IGS_PE_ALTERNATV_EXT records
1932 OPEN gc_ae_rec;
1933 LOOP
1934 FETCH gc_ae_rec INTO gv_ae_rec;
1935 IF gc_ae_rec%FOUND THEN
1936 crsp_ins_ae_rec(p_new_course_cd, p_new_version_number);
1937 ELSE
1938 EXIT;
1939 END IF;
1940 END LOOP;
1941 CLOSE gc_ae_rec;
1942 -- calling procedure to insert IGS_FI_FND_SRC_RSTN records
1943 OPEN gc_fsr_rec;
1944 LOOP
1945 FETCH gc_fsr_rec INTO gv_fsr_rec;
1946 IF gc_fsr_rec%FOUND THEN
1947 crsp_ins_fsr_rec(p_new_course_cd, p_new_version_number);
1948 ELSE
1949 EXIT;
1950 END IF;
1951 END LOOP;
1952 CLOSE gc_fsr_rec;
1953 -- calling procedure to insert IGS_PS_FIELD_STUDY records
1954 OPEN gc_cfos_rec;
1955 LOOP
1956 FETCH gc_cfos_rec INTO gv_cfos_rec;
1957 IF gc_cfos_rec%FOUND THEN
1958 crsp_ins_cfos_rec(p_new_course_cd, p_new_version_number);
1959 ELSE
1960 EXIT;
1961 END IF;
1962 END LOOP;
1963 CLOSE gc_cfos_rec;
1964 -- calling procedure to insert IGS_PS_GRP_MBR records
1965 OPEN gc_cgm_rec;
1966 LOOP
1967 FETCH gc_cgm_rec INTO gv_cgm_rec;
1968 IF gc_cgm_rec%FOUND THEN
1969 crsp_ins_cgm_rec(p_new_course_cd, p_new_version_number);
1970 ELSE
1971 EXIT;
1972 END IF;
1973 END LOOP;
1974 CLOSE gc_cgm_rec;
1975 -- calling procedure to insert IGS_PS_CATEGORISE records
1976 OPEN gc_ccat_rec;
1977 LOOP
1978 FETCH gc_ccat_rec INTO gv_ccat_rec;
1979 IF gc_ccat_rec%FOUND THEN
1980 crsp_ins_ccat_rec(p_new_course_cd, p_new_version_number);
1981 ELSE
1982 EXIT;
1983 END IF;
1984 END LOOP;
1985 CLOSE gc_ccat_rec;
1986 -- calling procedure to insert IGS_PS_REF_CD records
1987 OPEN gc_crcd_rec;
1988 LOOP
1989 FETCH gc_crcd_rec INTO gv_crcd_rec;
1990 IF gc_crcd_rec%FOUND THEN
1991 crsp_ins_crcd_rec(p_new_course_cd, p_new_version_number);
1992 ELSE
1993 EXIT;
1994 END IF;
1995 END LOOP;
1996 CLOSE gc_crcd_rec;
1997 -- calling procedure to insert IGS_PS_ANL_LOAD records
1998 OPEN gc_cal_rec;
1999 LOOP
2000 FETCH gc_cal_rec INTO gv_cal_rec;
2001 IF gc_cal_rec%FOUND THEN
2002 crsp_ins_cal_rec(p_new_course_cd, p_new_version_number);
2003 ELSE
2004 EXIT;
2005 END IF;
2006 END LOOP;
2007 CLOSE gc_cal_rec;
2008 -- calling procedure to insert IGS_PS_VER_NOTE records
2009 OPEN gc_cvn_rec;
2010 LOOP
2011 FETCH gc_cvn_rec INTO gv_cvn_rec;
2012 IF gc_cvn_rec%FOUND THEN
2013 crsp_ins_cvn_rec(p_new_course_cd, p_new_version_number);
2014 ELSE
2015 EXIT;
2016 END IF;
2017 END LOOP;
2018 CLOSE gc_cvn_rec;
2019 -- calling procedure to insert IGS_PS_OFR records
2020 OPEN c_co_rec;
2021 LOOP
2022 FETCH c_co_rec INTO gv_co_rec;
2023 IF c_co_rec%FOUND THEN
2024 crsp_ins_co_rec(p_new_course_cd, p_new_version_number);
2025 ELSE
2026 EXIT;
2027 END IF;
2028 END LOOP;
2029 CLOSE c_co_rec;
2030
2031 -- Removed code to insert records into igs_ps_accounts table as part of Enh# 2831572
2032
2033 -- calling procedure to insert IGS_PS_STAGE records and its
2034 -- child records
2035 crspl_ins_cst_rec;
2036 -- calling procedure to insert IGS_PS_VER_RU records
2037 crspl_ins_cvr_rec;
2038 -- calling procedure to insert IGS_RE_DFLT_MS_SET records
2039 crspl_ins_dms_rec;
2040
2041 --Enh#3345205, calling procedure to insert IGS_EN_PSV_TERM_IT records
2042 crsp_ins_term_instr_time;
2043
2044 --
2045 -- Start of new code as per the HESA DLD Build. ENCR019 Bug# 2201753.
2046 --
2047
2048 --
2049 --Get the OSS_COUNTRY_CODE
2050 --
2051 IF fnd_profile.value('OSS_COUNTRY_CODE') = 'GB' THEN
2052 OPEN cur_obj_exists;
2053 FETCH cur_obj_exists INTO l_cur_obj_exists;
2054 IF cur_obj_exists%FOUND THEN
2055 CLOSE cur_obj_exists;
2056 l_status := 0;
2057 EXECUTE IMMEDIATE
2058 'BEGIN
2059 IGS_HE_PS_PKG.COPY_PROG_VERSION(:1, :2, :3, :4, :5, :6);
2060 END;'
2061 USING p_old_course_cd,
2062 p_old_version_number,
2063 p_new_course_cd,
2064 p_new_version_number,
2065 out p_message_name,
2066 out l_status;
2067
2068 IF NVL(l_Status,0) = 2 THEN -- ie. The procedure call has resulted in error.
2069 Fnd_Message.Set_Name('IGS', p_message_name);
2070 IGS_GE_MSG_STACK.ADD;
2071 App_Exception.Raise_Exception;
2072 END IF;
2073 ELSE
2074 CLOSE cur_obj_exists;
2075 END IF;
2076 END IF;
2077 --
2078 -- End of new code added as per the HESA DLD Build. ENCR019 Bug# 2201753.
2079 --
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2083 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_001.CRSP_INS_CRS_VER');
2084 IGS_GE_MSG_STACK.ADD;
2085 App_Exception.Raise_Exception;
2086 END crsp_ins_crs_ver; -- main procedure
2087
2088
2089 /******************* UNIT SECTION DETAILS ROLLOVER *******************************************************/
2090
2091
2092 -- Procedure to duplicate child records of unit section.
2093 PROCEDURE crsp_ins_unit_section(
2094 p_old_uoo_id IN NUMBER,
2095 p_new_uoo_id IN NUMBER,
2096 p_message_name OUT NOCOPY VARCHAR2 ,
2097 p_log_creation_date DATE )
2098 AS
2099 -------------------------------------------------------------------------------------------------------------------
2100 --Change History:
2101 --Who When What
2102 --sommukhe 08-JAN-2006 Bug#3305881,modified cursor c_ou by replacing IGS_OR_UNIT with IGS_OR_INST_ORG_BASE_V
2103 --sarakshi 12-Jan-2006 BUg#4926548,modified cursor c_fee_type_exists and c_fee_type_cal_exists to address performance issues, created pl-sql table.
2104 --sommukhe 23-NOV-2005 Bug#4726560, added cursors cur_usec_tchr_lead,cur_usec_tchr1, c_tch_ins and cur_usec_gs_df.
2105 --sarakshi 17-Oct-2005 Bug#4657596, added fnd logging
2106 --sarakshi 12-Sep-2005 Bug#45772, placed flexfield attributes in the call to the occurrence insert row with the local variables.
2107 --sommukhe 01-SEP-2005 Bug# 4538540 , Added cursor cur_ass_item .
2108 --sarakshi 14-Sep-2004 Enh#3888835, added cursor c_rtn_us,c_rtn_us_dtl and c_fee_type_cal_exists and it's related logic (of retention rollover).
2109 --sarakshi 12-Jul-2004 Bug#3729462, Added the column DELETE_FLAG in the where clause of the cursor cur_waitlist_chk .
2110 --sarakshi 02-Jun-2004 Bug#3658126,modified cursor usec_unitass and its usage, also added cursor cur_unitassgrp_new and its usage
2111 --sarakshi 04-Nov-2003 Enh#3116171,added field billing_credit_points in igs_ps_usec_cps insert_row.Also coded logic for rolling over igs_ps_usec_sp_fees data.
2112 --sarakshi 21-oct-2003 Enh# 3052452, removed the validation related to max_auditors_allowed
2113 --schodava 17-Sep-2003 Bug # 2520994 PSP Inheritance Build
2114 -- Modified cursor wlst_pri and cursor cur_wlst_pri_new, to obsolete column UNIT_SECTION_LIMIT_WAITLIST_ID
2115 -- and use column UOO_ID instead.
2116 --sarakshi 29-Aug-2003 Bug#3076021,before inserting the records checking if the record already exists for all the usec details .
2117 -- Also max auditors field can be rolled over if auditable checkbox is chhecked.Also changed the length of the variable lv_rule_unprocessed
2118 --vvutukur 04-Aug-2003 Enh#3045069.PSP Enh Build. Removed cursor usec_rptc and related code as this cursor
2119 -- refers to table igs_ps_usec_rpt_cond which is obsoleted as part of this design.
2120 --smvk 25-jun-2003 Enh bug#2918094. Added column cancel_flag.
2121 --shtatiko 23-MAY-2003 Enh# 2831572, Removed cursor cur_usec_accts and also removed code to rollover Revenue Account
2122 -- Code into igs_ps_usec_accts
2123 --sarakshi 25-Apr-2003 Enh#2858431,modified call to igs_ps_us_req_ref_cd_pkg.insert_row
2124 --vvutukur 01-Nov-2002 Enh#2636716.Added new column max_auditors_allowd in the tbh call to
2125 -- igs_ps_usec_lim_wlst_pkg.insert_row.
2126 --vvutukur 28-Oct-2002 Enh#2613933.Modified cursor usec_x_grpmem to select 2 new columns max_enr_group,
2127 -- max_ovr_group.Also modified the tbh call to igs_ps_usec_x_grp_pkg.insert_row.
2128 --smadathi 02-May-2002 Bug 2261649. The procedure crsp_ins_unit_section contains reference to table IGS_PS_USEC_CHARGE.
2129 -- The table became obsolete. The references to the same have been removed. The declarartion of cursor
2130 -- usec_add_chrg and asscociated section of opening of cursor and calling the TBH of the above tables
2131 -- removed.
2132 --smadathi 01-JUN-2001 The procedure crsp_ins_unit_section contains reference to tables IGS_PS_USEC_RPT_FMLY and
2133 -- IGS_PS_USEC_PRV_GRAD . These tables became obsolete . The references to the same have been
2134 -- removed . The declarartion of cursors usec_rfmly and usec_pgrad removed and asscociated section
2135 -- of opening of cursor and calling the TBH of the above tables removed .The changes are as per DLD
2136 --skoppula 09-AUG-2001 Added code to rollover unit sectin account segment values.This comes as 27th cursor
2137 ---------------------------------------------------------------------------------------------------------------------
2138 gv_new_usec_rec igs_ps_unit_ofr_opt%ROWTYPE;
2139 gv_old_usec_rec igs_ps_unit_ofr_opt%ROWTYPE;
2140
2141 -- get the old unit section values
2142 CURSOR gc_usec_old_rec IS
2143 SELECT *
2144 FROM igs_ps_unit_ofr_opt
2145 WHERE uoo_id = p_old_uoo_id ;
2146
2147 -- get the new unit section values
2148 CURSOR gc_usec_new_rec IS
2149 SELECT *
2150 FROM igs_ps_unit_ofr_opt
2151 WHERE uoo_id = p_new_uoo_id ;
2152
2153 --1
2154 CURSOR usec_occurs( p_uoo_id NUMBER ) IS
2155 SELECT *
2156 FROM igs_ps_usec_occurs
2157 WHERE uoo_id = p_uoo_id;
2158
2159 --2
2160 CURSOR usec_occurs_refcd( p_usec_id NUMBER ) IS
2161 SELECT *
2162 FROM igs_ps_usec_ocur_ref
2163 WHERE unit_section_occurrence_id = p_usec_id;
2164
2165 --3
2166 --Enhancement bug no 1800179 , pmarada, added this cursor for unit section instructors
2167 CURSOR usec_instr(cp_Unit_Section_occurrence_id NUMBER) IS
2168 SELECT *
2169 FROM igs_ps_uso_instrctrs
2170 WHERE unit_section_occurrence_id = cp_Unit_Section_occurrence_id;
2171
2172 --4
2173 CURSOR wlst_limit( p_uoo_id NUMBER) IS
2174 SELECT *
2175 FROM igs_ps_usec_lim_wlst
2176 WHERE uoo_id = p_uoo_id;
2177
2178
2179 --5
2180 CURSOR wlst_pri(p_uoo_id NUMBER) IS
2181 SELECT *
2182 FROM igs_ps_usec_wlst_pri
2183 WHERE uoo_id = p_uoo_id;
2184
2185 --6
2186 CURSOR wlst_prf(p_usec_pri_id NUMBER) IS
2187 SELECT *
2188 FROM igs_ps_usec_wlst_prf
2189 WHERE unit_sec_waitlist_priority_id = p_usec_pri_id;
2190
2191 --7
2192 CURSOR usec_cps( p_uoo_id NUMBER) IS
2193 SELECT *
2194 FROM igs_ps_usec_cps
2195 WHERE uoo_id = p_uoo_id;
2196
2197 --8
2198 CURSOR usec_x_grp (cp_grp_name VARCHAR2, cp_cal_type VARCHAR2, cp_seq_no NUMBER) IS
2199 SELECT *
2200 FROM igs_ps_usec_x_grp
2201 WHERE usec_x_listed_group_name = cp_grp_name
2202 AND cal_type = cp_cal_type
2203 AND ci_sequence_number = cp_seq_no ;
2204
2205 --9
2206 CURSOR usec_x_grpmem (cp_uoo_id NUMBER) IS
2207 SELECT grpmem.*, grp.usec_x_listed_group_name , grp.location_inheritance,
2208 grp.max_enr_group,max_ovr_group
2209 FROM igs_ps_usec_x_grpmem grpmem, igs_ps_usec_x_grp grp
2210 WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
2211 AND grpmem.uoo_id = cp_uoo_id;
2212
2213 -- 10
2214 CURSOR usec_spn ( p_uoo_id NUMBER) IS
2215 SELECT *
2216 FROM igs_ps_usec_spnsrshp
2217 WHERE uoo_id = p_uoo_id;
2218
2219 --11
2220 CURSOR usec_tchr ( p_uoo_id NUMBER) IS
2221 SELECT *
2222 FROM igs_ps_usec_tch_resp
2223 WHERE uoo_id = p_uoo_id;
2224
2225 --12
2226 CURSOR usec_as ( p_uoo_id NUMBER) IS
2227 SELECT *
2228 FROM igs_ps_usec_as
2229 WHERE uoo_id = p_uoo_id;
2230
2231 --13.1
2232 CURSOR usec_unitassgrp ( p_uoo_id NUMBER) IS
2233 SELECT *
2234 FROM igs_as_us_ai_group
2235 WHERE uoo_id = p_uoo_id;
2236
2237 --13.2
2238 CURSOR usec_unitass ( cp_uoo_id igs_ps_unitass_item.uoo_id%TYPE,
2239 cp_us_ass_item_group_id igs_ps_unitass_item.us_ass_item_group_id%TYPE) IS
2240 SELECT *
2241 FROM igs_ps_unitass_item
2242 WHERE uoo_id = cp_uoo_id
2243 AND us_ass_item_group_id=cp_us_ass_item_group_id;
2244
2245 --14
2246 CURSOR usec_ref ( p_uoo_id NUMBER) IS
2247 SELECT *
2248 FROM igs_ps_usec_ref
2249 WHERE uoo_id = p_uoo_id;
2250
2251 --15
2252 CURSOR usec_refcd( p_usec_ref_id NUMBER ) IS
2253 SELECT *
2254 FROM igs_ps_usec_ref_cd
2255 WHERE unit_section_reference_id = p_usec_ref_id;
2256
2257 --Modified CURSOR us_req_refcd as part of bug#2563596.In the where condition the column unit_section_req_ref_cd_id
2258 --was replaced with column unit_section_reference_id.This was causing no unit section requirements reference codes
2259 --records to be selected for rollover
2260 --Enhancement bug no 1800179 , pmarada
2261
2262 --16
2263 CURSOR us_req_refcd ( cp_usec_ref_id NUMBER) IS
2264 SELECT *
2265 FROM igs_ps_us_req_ref_cd
2266 WHERE unit_section_reference_id = cp_usec_ref_id;
2267
2268 --17
2269 CURSOR usec_grdsch ( p_uoo_id NUMBER ) IS
2270 SELECT *
2271 FROM igs_ps_usec_grd_schm
2272 WHERE uoo_id = p_uoo_id;
2273
2274 -- 18
2275 CURSOR c_unt_ofr_opt_n (cp_uoo_id NUMBER) IS
2276 SELECT snote.*, genote.s_note_format_type, genote.note_text
2277 FROM igs_ps_unt_ofr_opt_n snote, igs_ge_note genote
2278 WHERE uoo_id = cp_uoo_id
2279 AND snote.reference_number = geNote.reference_number;
2280
2281 -- 19
2282 CURSOR usec_pre_co_req_rule (cp_uoo_id NUMBER) IS
2283 SELECT *
2284 FROM igs_ps_usec_ru_v
2285 WHERE uoo_id = cp_uoo_id;
2286
2287 -- 20
2288 CURSOR usec_cat (cp_uoo_id NUMBER) IS
2289 SELECT *
2290 FROM igs_ps_usec_category
2291 WHERE uoo_id = cp_uoo_id;
2292
2293 -- 21
2294 CURSOR usec_plushr (cp_uoo_id NUMBER) IS
2295 SELECT *
2296 FROM igs_ps_us_unsched_cl
2297 WHERE uoo_id = cp_uoo_id;
2298
2299 --22
2300 CURSOR usec_tro (cp_uoo_id NUMBER) IS
2301 SELECT *
2302 FROM igs_ps_tch_resp_ovrd
2303 WHERE uoo_id = cp_uoo_id;
2304
2305 --23
2306 CURSOR c_usec_spl_fees (cp_uoo_id NUMBER) IS
2307 SELECT *
2308 FROM igs_ps_usec_sp_fees
2309 WHERE uoo_id = cp_uoo_id;
2310
2311 CURSOR c_rtn_us IS
2312 SELECT a.*
2313 FROM igs_ps_nsus_rtn a,
2314 igs_ps_unit_ofr_opt_all b
2315 WHERE a.uoo_id = p_old_uoo_id
2316 AND a.uoo_id = b.uoo_id
2317 AND b.non_std_usec_ind = 'Y'
2318 AND a.definition_code IN ('UNIT_SECTION','UNIT_SECTION_FEE_TYPE');
2319
2320 CURSOR c_rtn_us_dtl(cp_non_std_usec_rtn_id igs_ps_nsus_rtn_dtl.non_std_usec_rtn_id%TYPE) IS
2321 SELECT *
2322 FROM igs_ps_nsus_rtn_dtl
2323 WHERE non_std_usec_rtn_id = cp_non_std_usec_rtn_id;
2324
2325 CURSOR usec_fac(cp_unit_section_occurrence_id igs_ps_uso_facility.unit_section_occurrence_id%TYPE) IS
2326 SELECT *
2327 FROM igs_ps_uso_facility
2328 WHERE unit_section_occurrence_id = cp_unit_section_occurrence_id;
2329
2330 CURSOR c_fee_type_cal_exists(cp_c_source_fee_type igs_fi_fee_type.fee_type%TYPE) IS
2331 SELECT ci.cal_type,ci.sequence_number
2332 FROM igs_fi_fee_type ft,
2333 igs_fi_f_typ_ca_inst ftci,
2334 igs_ca_inst ci,
2335 igs_ca_type ct,
2336 igs_ca_stat cs
2337 WHERE ft.s_fee_type IN ('TUTNFEE', 'OTHER', 'SPECIAL', 'AUDIT')
2338 AND ft.closed_ind = 'N'
2339 AND ft.fee_type = ftci.fee_type
2340 AND ft.fee_type = cp_c_source_fee_type
2341 AND ftci.fee_cal_type = ci.cal_type
2342 AND ftci.fee_ci_sequence_number = ci.sequence_number
2343 AND ci.cal_type = ct.cal_type
2344 AND ct.s_cal_cat = 'FEE'
2345 AND ci.cal_status = cs.cal_status
2346 AND cs.s_cal_status = 'ACTIVE';
2347
2348 CURSOR c_teach_date(cp_cal_type igs_ca_inst_all.cal_type%TYPE ,cp_seq_num igs_ca_inst_all.sequence_number%TYPE) IS
2349 SELECT start_dt,end_dt
2350 FROM igs_ca_inst_all
2351 WHERE cal_type = cp_cal_type
2352 AND sequence_number = cp_seq_num;
2353
2354 l_d_src_teach_cal_start_dt DATE;
2355 l_d_src_teach_cal_end_dt DATE;
2356 l_d_dst_teach_cal_start_dt DATE;
2357 l_d_dst_teach_cal_end_dt DATE;
2358
2359 CURSOR cur_config IS
2360 SELECT *
2361 FROM igs_ps_sch_ocr_cfig;
2362 l_rec_config cur_config%ROWTYPE;
2363 l_config_rec_found BOOLEAN;
2364
2365 CURSOR cur_ass_item(cp_uoo_id IN NUMBER, cp_us_ass_item_group_id IN NUMBER) IS
2366 SELECT 'X'
2367 FROM igs_ps_unitass_item
2368 WHERE uoo_id = cp_uoo_id
2369 AND us_ass_item_group_id = cp_us_ass_item_group_id;
2370 l_c_var VARCHAR2(1);
2371
2372 --To find if there is any lead instructor in the Destination
2373 CURSOR cur_usec_tchr_lead(cp_uoo_id igs_ps_usec_tch_resp.uoo_id%TYPE) IS
2374 SELECT instructor_id
2375 FROM igs_ps_usec_tch_resp
2376 WHERE uoo_id = cp_uoo_id
2377 AND lead_instructor_flag = 'Y';
2378 cur_usec_tchr_lead_rec cur_usec_tchr_lead%ROWTYPE;
2379
2380 --To find if the occurrence instructor passed is a lead in the source.
2381 CURSOR cur_usec_tchr1(cp_uoo_id igs_ps_usec_tch_resp.uoo_id%TYPE,cp_ins igs_ps_usec_tch_resp.instructor_id%TYPE) IS
2382 SELECT instructor_id
2383 FROM igs_ps_usec_tch_resp
2384 WHERE uoo_id = cp_uoo_id
2385 AND lead_instructor_flag = 'Y'
2386 AND instructor_id = cp_ins;
2387 cur_usec_tchr1_rec cur_usec_tchr1%ROWTYPE;
2388
2389
2390 l_resp_flag BOOLEAN ;
2391 l_dest_lead_inst_id igs_ps_usec_tch_resp.instructor_id%TYPE;
2392
2393 ---------------------------
2394 CURSOR cur_ftci(cp_c_cal_type igs_ca_teach_to_load_v.teach_cal_type%TYPE,
2395 cp_n_sequence_number igs_ca_teach_to_load_v.teach_ci_sequence_number%TYPE) IS
2396 SELECT sup_cal_type cal_type, sup_ci_sequence_number sequence_number
2397 FROM igs_ca_inst_rel
2398 WHERE (sub_cal_type,sub_ci_sequence_number) IN (SELECT load_cal_type, load_ci_sequence_number
2399 FROM igs_ca_teach_to_load_v
2400 WHERE teach_cal_type = cp_c_cal_type
2401 AND teach_ci_sequence_number = cp_n_sequence_number);
2402 TYPE teach_cal_rec IS RECORD(
2403 cal_type igs_ca_inst_all.cal_type%TYPE,
2404 sequence_number igs_ca_inst_all.sequence_number%TYPE
2405 );
2406 TYPE teachCalendar IS TABLE OF teach_cal_rec INDEX BY BINARY_INTEGER;
2407 teachCalendar_tbl teachCalendar;
2408 l_n_counter NUMBER(10);
2409 l_c_proceed BOOLEAN ;
2410
2411
2412
2413 BEGIN
2414 -- This PROCEDURE IS responsible FOR transferring ALL the details OF one unit offering OPTION TO
2415 -- another.It gets records (OLD location_cd AND unit class) AND transfers (duplicates) them ALL
2416 -- over TO a NEW unit offering option. An EXCEPTION handler IS raised WHEN an error occurs.
2417
2418 -- This checks IF the specified NEW unit offering OPTION exists
2419 OPEN gc_usec_new_rec;
2420 FETCH gc_usec_new_rec INTO gv_new_usec_rec;
2421 IF gc_usec_new_rec%NOTFOUND THEN
2422 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
2423 CLOSE gc_usec_new_rec;
2424 RETURN;
2425 END IF;
2426 CLOSE gc_usec_new_rec;
2427
2428 -- This checks IF the specified OLD unit offering OPTION NOT exists
2429 OPEN gc_usec_old_rec;
2430 FETCH gc_usec_old_rec INTO gv_old_usec_rec;
2431 IF gc_usec_old_rec%NOTFOUND THEN
2432 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
2433 CLOSE gc_usec_old_rec;
2434 RETURN;
2435 END IF;
2436 CLOSE gc_usec_old_rec;
2437
2438 -- Validation SUCCESSFUL
2439
2440 -- Rollover of unit section occurrence records
2441
2442 OPEN c_teach_date(gv_old_usec_rec.cal_type,gv_old_usec_rec.ci_sequence_number);
2443 FETCH c_teach_date INTO l_d_src_teach_cal_start_dt,l_d_src_teach_cal_end_dt;
2444 CLOSE c_teach_date;
2445
2446 OPEN c_teach_date(gv_new_usec_rec.cal_type,gv_new_usec_rec.ci_sequence_number);
2447 FETCH c_teach_date INTO l_d_dst_teach_cal_start_dt,l_d_dst_teach_cal_end_dt;
2448 CLOSE c_teach_date;
2449
2450 OPEN cur_config;
2451 FETCH cur_config INTO l_rec_config;
2452 IF cur_config%NOTFOUND THEN
2453 l_config_rec_found:= FALSE;
2454 ELSE
2455 l_config_rec_found:= TRUE;
2456 END IF;
2457 CLOSE cur_config;
2458
2459 FOR usec_occurs_rec IN usec_occurs (p_old_uoo_Id ) LOOP
2460 DECLARE
2461
2462 CURSOR cur_occur_new (cp_uoo_id igs_ps_usec_occurs_all.uoo_id%TYPE,
2463 cp_occurrence_identifier igs_ps_usec_occurs_all.occurrence_identifier%TYPE) IS
2464 SELECT 'X'
2465 FROM igs_ps_usec_occurs_all
2466 WHERE uoo_id = cp_uoo_id
2467 AND occurrence_identifier=cp_occurrence_identifier
2468 AND ROWNUM = 1 ;
2469 l_cur_occur_new cur_occur_new%ROWTYPE;
2470
2471 lv_rowid VARCHAR2(25);
2472 l_usec_id NUMBER;
2473 l_org_id NUMBER(15);
2474 l_d_uso_dest_start_dt DATE;
2475 l_d_uso_dest_end_dt DATE;
2476 l_n_num_st_days NUMBER;
2477 l_n_num_end_days NUMBER;
2478
2479
2480 l_c_monday igs_ps_usec_occurs_all.monday%TYPE;
2481 l_c_tuesday igs_ps_usec_occurs_all.tuesday%TYPE;
2482 l_c_wednesday igs_ps_usec_occurs_all.wednesday%TYPE;
2483 l_c_thursday igs_ps_usec_occurs_all.thursday%TYPE;
2484 l_c_friday igs_ps_usec_occurs_all.friday%TYPE;
2485 l_c_saturday igs_ps_usec_occurs_all.saturday%TYPE;
2486 l_c_sunday igs_ps_usec_occurs_all.sunday%TYPE;
2487 l_d_start_time igs_ps_usec_occurs_all.start_time%TYPE;
2488 l_d_end_time igs_ps_usec_occurs_all.end_time%TYPE;
2489 l_c_building_code igs_ps_usec_occurs_all.building_code%TYPE;
2490 l_c_room_code igs_ps_usec_occurs_all.room_code%TYPE;
2491 l_c_dedicated_building_code igs_ps_usec_occurs_all.dedicated_building_code%TYPE;
2492 l_c_dedicated_room_code igs_ps_usec_occurs_all.dedicated_room_code%TYPE;
2493 l_c_preferred_building_code igs_ps_usec_occurs_all.preferred_building_code%TYPE;
2494 l_c_preferred_room_code igs_ps_usec_occurs_all.preferred_room_code%TYPE;
2495 l_c_preferred_region_code igs_ps_usec_occurs_all.preferred_region_code%TYPE;
2496 l_c_inst_change_notify igs_ps_usec_occurs_all.inst_notify_ind%TYPE;
2497 l_c_attribute_category igs_ps_usec_occurs_all.attribute_category%TYPE;
2498 l_c_attribute1 igs_ps_usec_occurs_all.attribute1%TYPE;
2499 l_c_attribute2 igs_ps_usec_occurs_all.attribute2%TYPE;
2500 l_c_attribute3 igs_ps_usec_occurs_all.attribute3%TYPE;
2501 l_c_attribute4 igs_ps_usec_occurs_all.attribute4%TYPE;
2502 l_c_attribute5 igs_ps_usec_occurs_all.attribute5%TYPE;
2503 l_c_attribute6 igs_ps_usec_occurs_all.attribute6%TYPE;
2504 l_c_attribute7 igs_ps_usec_occurs_all.attribute7%TYPE;
2505 l_c_attribute8 igs_ps_usec_occurs_all.attribute8%TYPE;
2506 l_c_attribute9 igs_ps_usec_occurs_all.attribute9%TYPE;
2507 l_c_attribute10 igs_ps_usec_occurs_all.attribute10%TYPE;
2508 l_c_attribute11 igs_ps_usec_occurs_all.attribute11%TYPE;
2509 l_c_attribute12 igs_ps_usec_occurs_all.attribute12%TYPE;
2510 l_c_attribute13 igs_ps_usec_occurs_all.attribute13%TYPE;
2511 l_c_attribute14 igs_ps_usec_occurs_all.attribute14%TYPE;
2512 l_c_attribute15 igs_ps_usec_occurs_all.attribute15%TYPE;
2513 l_c_attribute16 igs_ps_usec_occurs_all.attribute16%TYPE;
2514 l_c_attribute17 igs_ps_usec_occurs_all.attribute17%TYPE;
2515 l_c_attribute18 igs_ps_usec_occurs_all.attribute18%TYPE;
2516 l_c_attribute19 igs_ps_usec_occurs_all.attribute19%TYPE;
2517 l_c_attribute20 igs_ps_usec_occurs_all.attribute20%TYPE;
2518 l_occur_roll_allowed BOOLEAN;
2519 BEGIN
2520 l_org_id := igs_ge_gen_003.get_org_id;
2521
2522 IF usec_occurs_rec.start_date IS NOT NULL THEN
2523 l_n_num_st_days := usec_occurs_rec.start_date - NVL(gv_old_usec_rec.unit_section_start_date,l_d_src_teach_cal_start_dt);
2524 l_d_uso_dest_start_dt := NVL(gv_new_usec_rec.unit_section_start_date,l_d_dst_teach_cal_start_dt) + l_n_num_st_days;
2525 ELSE
2526 l_d_uso_dest_start_dt := NULL;
2527 END IF;
2528
2529 IF usec_occurs_rec.end_date IS NOT NULL THEN
2530 l_n_num_end_days := NVL(gv_old_usec_rec.unit_section_end_date,l_d_src_teach_cal_end_dt) - usec_occurs_rec.end_date ;
2531 l_d_uso_dest_end_dt := NVL(gv_new_usec_rec.unit_section_end_date,l_d_dst_teach_cal_end_dt) - l_n_num_end_days;
2532 ELSE
2533 l_d_uso_dest_end_dt := NULL;
2534 END IF;
2535
2536 --Unit section occurrence start date must not be greater than the unit section end date if exists ,
2537 --if does not exists then must not be greater than teaching calendar end date
2538 IF l_d_uso_dest_start_dt IS NOT NULL THEN
2539 IF l_d_uso_dest_start_dt > NVL(l_d_uso_dest_end_dt,NVL(gv_new_usec_rec.unit_section_end_date,l_d_dst_teach_cal_end_dt)) THEN
2540 l_d_uso_dest_start_dt := NVL(gv_new_usec_rec.unit_section_start_date,l_d_dst_teach_cal_start_dt);
2541 END IF;
2542 END IF;
2543 --Unit section occurrence end date must not be less than the unit section start date if exists ,
2544 --if does not exists then must not be less than teaching calendar start date
2545 IF l_d_uso_dest_end_dt IS NOT NULL THEN
2546 IF l_d_uso_dest_end_dt < NVL(l_d_uso_dest_start_dt,NVL(gv_new_usec_rec.unit_section_start_date,l_d_dst_teach_cal_start_dt)) THEN
2547 l_d_uso_dest_end_dt := NVL(gv_new_usec_rec.unit_section_end_date,l_d_dst_teach_cal_end_dt);
2548 END IF;
2549 END IF;
2550
2551 -- As part of bug#2833850 added columns preferred_region_code and no_set_day_ind to the call of
2552 -- igs_ps_usec_occurs_pkg.insert_row
2553
2554 --Added as a part of scheduling Enhancement IGS.M
2555 l_occur_roll_allowed := TRUE;
2556
2557 IF l_config_rec_found = FALSE THEN
2558 l_c_monday:= usec_occurs_rec.monday;
2559 l_c_tuesday:= usec_occurs_rec.tuesday;
2560 l_c_wednesday:= usec_occurs_rec.wednesday;
2561 l_c_thursday:= usec_occurs_rec.thursday;
2562 l_c_friday:= usec_occurs_rec.friday;
2563 l_c_saturday:= usec_occurs_rec.saturday;
2564 l_c_sunday:= usec_occurs_rec.sunday;
2565 l_d_start_time:= usec_occurs_rec.start_time;
2566 l_d_end_time:= usec_occurs_rec.end_time;
2567 l_c_building_code:= usec_occurs_rec.building_code;
2568 l_c_room_code:= usec_occurs_rec.room_code;
2569 l_c_dedicated_building_code:= usec_occurs_rec.dedicated_building_code;
2570 l_c_dedicated_room_code:= usec_occurs_rec.dedicated_room_code;
2571 l_c_preferred_building_code:= usec_occurs_rec.preferred_building_code;
2572 l_c_preferred_room_code:= usec_occurs_rec.preferred_room_code;
2573 l_c_preferred_region_code:= usec_occurs_rec.preferred_region_code;
2574 l_c_inst_change_notify:= usec_occurs_rec.inst_notify_ind;
2575 l_c_attribute_category :=usec_occurs_rec.attribute_category;
2576 l_c_attribute1 := usec_occurs_rec.attribute1;
2577 l_c_attribute2 := usec_occurs_rec.attribute2;
2578 l_c_attribute3 := usec_occurs_rec.attribute3;
2579 l_c_attribute4 := usec_occurs_rec.attribute4;
2580 l_c_attribute5 := usec_occurs_rec.attribute5;
2581 l_c_attribute6 := usec_occurs_rec.attribute6;
2582 l_c_attribute7 := usec_occurs_rec.attribute7;
2583 l_c_attribute8 := usec_occurs_rec.attribute8;
2584 l_c_attribute9 := usec_occurs_rec.attribute9;
2585 l_c_attribute10 := usec_occurs_rec.attribute10;
2586 l_c_attribute11 := usec_occurs_rec.attribute11;
2587 l_c_attribute12 := usec_occurs_rec.attribute12;
2588 l_c_attribute13 := usec_occurs_rec.attribute13;
2589 l_c_attribute14 := usec_occurs_rec.attribute14;
2590 l_c_attribute15 := usec_occurs_rec.attribute15;
2591 l_c_attribute16 := usec_occurs_rec.attribute16;
2592 l_c_attribute17 := usec_occurs_rec.attribute17;
2593 l_c_attribute18 := usec_occurs_rec.attribute18;
2594 l_c_attribute19 := usec_occurs_rec.attribute19;
2595 l_c_attribute20 := usec_occurs_rec.attribute20;
2596
2597 ELSE
2598 --If scheduling Not required/TBA checkbox is unchecked in configuration form then do not rollover Not required/TBA occurrences
2599 IF (l_rec_config.to_be_announced_roll_flag = 'N' AND usec_occurs_rec.to_be_announced='Y') OR (l_rec_config.schd_not_rqd_roll_flag = 'N' AND usec_occurs_rec.no_set_day_ind ='Y') THEN
2600 l_occur_roll_allowed := FALSE;
2601 END IF;
2602
2603 IF l_rec_config.day_roll_flag ='Y' THEN
2604 l_c_monday:= usec_occurs_rec.monday;
2605 l_c_tuesday:= usec_occurs_rec.tuesday;
2606 l_c_wednesday:= usec_occurs_rec.wednesday;
2607 l_c_thursday:= usec_occurs_rec.thursday;
2608 l_c_friday:= usec_occurs_rec.friday;
2609 l_c_saturday:= usec_occurs_rec.saturday;
2610 l_c_sunday:= usec_occurs_rec.sunday;
2611 ELSE
2612 IF l_rec_config.schd_not_rqd_roll_flag = 'Y' THEN
2613 l_c_monday:= 'N';
2614 l_c_tuesday:= 'N';
2615 l_c_wednesday:= 'N';
2616 l_c_thursday:= 'N';
2617 l_c_friday:= 'N';
2618 l_c_saturday:= 'N';
2619 l_c_sunday:= 'N';
2620 ELSE
2621 l_c_monday:= usec_occurs_rec.monday;
2622 l_c_tuesday:= usec_occurs_rec.tuesday;
2623 l_c_wednesday:= usec_occurs_rec.wednesday;
2624 l_c_thursday:= usec_occurs_rec.thursday;
2625 l_c_friday:= usec_occurs_rec.friday;
2626 l_c_saturday:= usec_occurs_rec.saturday;
2627 l_c_sunday:= usec_occurs_rec.sunday;
2628 END IF;
2629 END IF;
2630
2631 IF l_rec_config.time_roll_flag = 'Y' THEN
2632 l_d_start_time:= usec_occurs_rec.start_time;
2633 l_d_end_time:= usec_occurs_rec.end_time;
2634 ELSE
2635 l_d_start_time:= NULL;
2636 l_d_end_time:= NULL;
2637 END IF;
2638
2639 IF l_rec_config.scheduled_bld_roll_flag = 'Y' THEN
2640 l_c_building_code:= usec_occurs_rec.building_code;
2641 ELSE
2642 l_c_building_code:= NULL;
2643 END IF;
2644
2645 IF l_rec_config.scheduled_room_roll_flag = 'Y' THEN
2646 l_c_room_code:= usec_occurs_rec.room_code;
2647 ELSE
2648 l_c_room_code:= NULL;
2649 END IF;
2650
2651 IF l_rec_config.dedicated_bld_roll_flag = 'Y' THEN
2652 l_c_dedicated_building_code:= usec_occurs_rec.dedicated_building_code;
2653 ELSE
2654 l_c_dedicated_building_code:= NULL;
2655 END IF;
2656
2657 IF l_rec_config.dedicated_room_roll_flag = 'Y' THEN
2658 l_c_dedicated_room_code:= usec_occurs_rec.dedicated_room_code;
2659 ELSE
2660 l_c_dedicated_room_code:= NULL;
2661 END IF;
2662
2663 IF l_rec_config.preferred_bld_roll_flag = 'Y' THEN
2664 l_c_preferred_building_code:= usec_occurs_rec.preferred_building_code;
2665 ELSE
2666 l_c_preferred_building_code:= NULL;
2667 END IF;
2668
2669 IF l_rec_config.preferred_room_roll_flag = 'Y' THEN
2670 l_c_preferred_room_code:= usec_occurs_rec.preferred_room_code;
2671 ELSE
2672 l_c_preferred_room_code:= NULL;
2673 END IF;
2674
2675 IF l_rec_config.preferred_region_roll_flag = 'Y' THEN
2676 l_c_preferred_region_code:= usec_occurs_rec.preferred_region_code;
2677 ELSE
2678 l_c_preferred_region_code:= NULL;
2679 END IF;
2680
2681 IF l_rec_config.inc_ins_change_notfy_roll_flag = 'Y' THEN
2682 l_c_inst_change_notify:= usec_occurs_rec.inst_notify_ind;
2683 ELSE
2684 l_c_inst_change_notify:= NULL;
2685 END IF;
2686
2687 IF l_rec_config.occur_flexfield_roll_flag = 'Y' THEN
2688 l_c_attribute_category :=usec_occurs_rec.attribute_category;
2689 l_c_attribute1 := usec_occurs_rec.attribute1;
2690 l_c_attribute2 := usec_occurs_rec.attribute2;
2691 l_c_attribute3 := usec_occurs_rec.attribute3;
2692 l_c_attribute4 := usec_occurs_rec.attribute4;
2693 l_c_attribute5 := usec_occurs_rec.attribute5;
2694 l_c_attribute6 := usec_occurs_rec.attribute6;
2695 l_c_attribute7 := usec_occurs_rec.attribute7;
2696 l_c_attribute8 := usec_occurs_rec.attribute8;
2697 l_c_attribute9 := usec_occurs_rec.attribute9;
2698 l_c_attribute10 := usec_occurs_rec.attribute10;
2699 l_c_attribute11 := usec_occurs_rec.attribute11;
2700 l_c_attribute12 := usec_occurs_rec.attribute12;
2701 l_c_attribute13 := usec_occurs_rec.attribute13;
2702 l_c_attribute14 := usec_occurs_rec.attribute14;
2703 l_c_attribute15 := usec_occurs_rec.attribute15;
2704 l_c_attribute16 := usec_occurs_rec.attribute16;
2705 l_c_attribute17 := usec_occurs_rec.attribute17;
2706 l_c_attribute18 := usec_occurs_rec.attribute18;
2707 l_c_attribute19 := usec_occurs_rec.attribute19;
2708 l_c_attribute20 := usec_occurs_rec.attribute20;
2709 ELSE
2710 l_c_attribute_category :=NULL;
2711 l_c_attribute1 := NULL;
2712 l_c_attribute2 := NULL;
2713 l_c_attribute3 := NULL;
2714 l_c_attribute4 := NULL;
2715 l_c_attribute5 := NULL;
2716 l_c_attribute6 := NULL;
2717 l_c_attribute7 := NULL;
2718 l_c_attribute8 := NULL;
2719 l_c_attribute9 := NULL;
2720 l_c_attribute10 := NULL;
2721 l_c_attribute11 := NULL;
2722 l_c_attribute12 := NULL;
2723 l_c_attribute13 := NULL;
2724 l_c_attribute14 := NULL;
2725 l_c_attribute15 := NULL;
2726 l_c_attribute16 := NULL;
2727 l_c_attribute17 := NULL;
2728 l_c_attribute18 := NULL;
2729 l_c_attribute19 := NULL;
2730 l_c_attribute20 := NULL;
2731 END IF;
2732
2733
2734 END IF;
2735
2736 IF l_occur_roll_allowed = TRUE THEN
2737 OPEN cur_occur_new(p_new_uoo_id,usec_occurs_rec.occurrence_identifier);
2738 FETCH cur_occur_new INTO l_cur_occur_new;
2739 IF cur_occur_new%NOTFOUND THEN
2740
2741 igs_ps_usec_occurs_pkg.insert_row (
2742 x_rowid => lv_rowid,
2743 x_unit_section_occurrence_id=> l_usec_id,
2744 x_uoo_id => p_new_uoo_id ,
2745 x_monday => l_c_monday,
2746 x_tuesday => l_c_tuesday,
2747 x_wednesday => l_c_wednesday,
2748 x_thursday => l_c_thursday,
2749 x_friday => l_c_friday,
2750 x_saturday => l_c_saturday,
2751 x_sunday => l_c_sunday,
2752 x_start_time => l_d_start_time,
2753 x_end_time => l_d_end_time,
2754 x_building_code => l_c_building_code,
2755 x_room_code => l_c_room_code,
2756 x_schedule_status => NULL,
2757 x_status_last_updated => NULL,
2758 x_instructor_id => usec_occurs_rec.instructor_id ,
2759 x_error_text => NULL,
2760 x_mode => 'R' ,
2761 x_org_id => l_org_id,
2762 x_start_date => l_d_uso_dest_start_dt,
2763 x_end_date => l_d_uso_dest_end_dt,
2764 x_to_be_announced => usec_occurs_rec.to_be_announced,
2765 x_attribute_category => l_c_attribute_category,
2766 x_attribute1 => l_c_attribute1,
2767 x_attribute2 => l_c_attribute2,
2768 x_attribute3 => l_c_attribute3,
2769 x_attribute4 => l_c_attribute4,
2770 x_attribute5 => l_c_attribute5,
2771 x_attribute6 => l_c_attribute6,
2772 x_attribute7 => l_c_attribute7,
2773 x_attribute8 => l_c_attribute8,
2774 x_attribute9 => l_c_attribute9,
2775 x_attribute10 => l_c_attribute10,
2776 x_attribute11 => l_c_attribute11,
2777 x_attribute12 => l_c_attribute12,
2778 x_attribute13 => l_c_attribute13,
2779 x_attribute14 => l_c_attribute14,
2780 x_attribute15 => l_c_attribute15,
2781 x_attribute16 => l_c_attribute16,
2782 x_attribute17 => l_c_attribute17,
2783 x_attribute18 => l_c_attribute18,
2784 x_attribute19 => l_c_attribute19,
2785 x_attribute20 => l_c_attribute20,
2786 x_inst_notify_ind => l_c_inst_change_notify,
2787 x_notify_status => 'NEW',
2788 x_dedicated_building_code => l_c_dedicated_building_code,
2789 x_dedicated_room_code => l_c_dedicated_room_code,
2790 x_preferred_building_code => l_c_preferred_building_code,
2791 x_preferred_room_code => l_c_preferred_room_code,
2792 x_preferred_region_code => l_c_preferred_region_code,
2793 x_no_set_day_ind => usec_occurs_rec.no_set_day_ind,
2794 x_cancel_flag => 'N',
2795 x_occurrence_identifier => usec_occurs_rec.occurrence_identifier,
2796 x_abort_flag => 'N');
2797
2798 -- Rollover of unit section occurrence reference code records
2799 --Added this if condition as a part of scheduling Enhancement IGS.M
2800 IF l_config_rec_found = FALSE OR l_rec_config.ref_cd_roll_flag ='Y' THEN
2801 FOR usec_occurs_refcd_rec IN usec_occurs_refcd(usec_occurs_rec.unit_section_occurrence_id ) LOOP
2802 DECLARE
2803 lv_rowid VARCHAR2(25);
2804 l_usec_ref_id NUMBER;
2805 BEGIN
2806 igs_ps_usec_ocur_ref_pkg.insert_row(
2807 x_rowid => lv_rowid,
2808 x_unit_sec_occur_reference_id => l_usec_ref_id,
2809 x_unit_section_occurrence_id => l_usec_id,
2810 x_reference_code_type => usec_occurs_refcd_rec.reference_code_type,
2811 x_reference_code => usec_occurs_refcd_rec.reference_code,
2812 x_mode => 'R',
2813 x_reference_code_desc => usec_occurs_refcd_rec.reference_code_desc);
2814 EXCEPTION
2815 WHEN OTHERS THEN
2816 --Fnd log implementation
2817 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2818 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
2819 SUBSTRB('Unit Section Occurrence Id:'||usec_occurs_refcd_rec.unit_section_occurrence_id||' '||'Reference Code Type:'||usec_occurs_refcd_rec.reference_code_type||' '||
2820 'Reference Code:'||usec_occurs_refcd_rec.reference_code||' '||NVL(fnd_message.get,SQLERRM),1,4000));
2821 END IF;
2822 END;
2823 END LOOP;
2824 END IF;
2825
2826 --Enhancement bug no 1800179 , pmarada
2827 -- Rollover of unit section occurrence Instructor records
2828 --Added this if condition as a part of scheduling Enhancement IGS.M
2829
2830
2831 IF l_config_rec_found = FALSE OR l_rec_config.instructor_roll_flag ='Y' THEN
2832 OPEN cur_usec_tchr_lead(p_new_uoo_id);
2833 FETCH cur_usec_tchr_lead INTO l_dest_lead_inst_id;
2834 CLOSE cur_usec_tchr_lead;
2835
2836 FOR usec_instr_rec IN usec_instr (usec_occurs_rec.unit_section_occurrence_id) Loop
2837 DECLARE
2838 lv_rowid VARCHAR2(25);
2839 l_uso_instructor_id NUMBER;
2840 BEGIN
2841
2842 OPEN cur_usec_tchr1(p_old_uoo_Id,usec_instr_rec.instructor_id);
2843 FETCH cur_usec_tchr1 INTO cur_usec_tchr1_rec;
2844 IF cur_usec_tchr1%FOUND AND l_dest_lead_inst_id IS NOT NULL THEN
2845 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2846 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_PS_GEN_001.crsp_ins_unit_section.instructor_not_rolled_over_one_lead_already_in_destination_unit_section',
2847 'Not rolling over Lead Instructo:'||usec_instr_rec.instructor_id||' '||'from Source UOO:'||p_old_uoo_Id||' '||
2848 'to Destination UOO:'||p_new_uoo_id||' '||'as there already exists a different Lead Instructor:'||cur_usec_tchr_lead_rec.instructor_id||' '||
2849 'in the Destination');
2850 END IF;
2851 CLOSE cur_usec_tchr1;
2852 ELSE
2853 igs_ps_uso_instrctrs_pkg.insert_row (
2854 x_rowid => lv_rowid,
2855 x_uso_instructor_id => l_uso_instructor_id,
2856 x_unit_section_occurrence_id => l_usec_id,
2857 x_instructor_id => usec_instr_rec.instructor_id,
2858 x_mode => 'R' );
2859 CLOSE cur_usec_tchr1;
2860 END IF;
2861 EXCEPTION
2862 WHEN OTHERS THEN
2863 --Fnd log implementation
2864 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2865 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
2866 SUBSTRB('Unit Section Occurrence Id:'||usec_instr_rec.unit_section_occurrence_id||' '||'Instructor Id:'||usec_instr_rec.instructor_id||' '||
2867 NVL(fnd_message.get,SQLERRM),1,4000));
2868 END IF;
2869 END;
2870 END LOOP;
2871 END IF;
2872
2873 -- Rollover of unit section occurrence facilities records
2874 --Added this as a part of scheduling Enhancement IGS.M
2875 IF l_config_rec_found = FALSE OR l_rec_config.facility_roll_flag ='Y' THEN
2876 FOR usec_fac_rec IN usec_fac (usec_occurs_rec.unit_section_occurrence_id) LOOP
2877 DECLARE
2878 lv_rowid VARCHAR2(25);
2879 l_uso_facility_id NUMBER;
2880 BEGIN
2881 igs_ps_uso_facility_pkg.insert_row (
2882 x_rowid => lv_rowid,
2883 x_uso_facility_id => l_uso_facility_id,
2884 x_unit_section_occurrence_id => l_usec_id,
2885 x_facility_code => usec_fac_rec.facility_code,
2886 x_mode => 'R' );
2887 EXCEPTION
2888 WHEN OTHERS THEN
2889 --Fnd log implementation
2890 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2891 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
2892 SUBSTRB('Unit Section Occurrence Id:'||usec_fac_rec.unit_section_occurrence_id||' '||'Facility Code:'||usec_fac_rec.facility_code||' '||
2893 NVL(fnd_message.get,SQLERRM),1,4000));
2894 END IF;
2895 END;
2896 END LOOP;
2897 END IF;
2898
2899 END IF; --end of cur_occur_new
2900 CLOSE cur_occur_new;
2901
2902 END IF; --Roll allowed
2903
2904 EXCEPTION
2905 WHEN OTHERS THEN
2906 --Fnd log implementation
2907 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2908 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
2909 SUBSTRB('Unit Section Occurrence Id:'||usec_occurs_rec.unit_section_occurrence_id||' '||'Occurrence Identifier:'||usec_occurs_rec.occurrence_identifier||' '||
2910 NVL(fnd_message.get,SQLERRM),1,4000));
2911 END IF;
2912 END;
2913
2914 END LOOP;
2915
2916
2917 --Enhancement bug no 1800179 , pmarada
2918 -- Rollover of unit section enrollment limit records
2919 FOR wlst_limit_rec IN wlst_limit (p_old_uoo_Id ) LOOP
2920 DECLARE
2921 lv_rowid VARCHAR2(25);
2922 l_usec_lim_id NUMBER;
2923
2924 --following two cursors are added by sarakshi,bug#2332807
2925 CURSOR cur_get_parameters IS
2926 SELECT unit_cd,version_number,cal_type,ci_sequence_number
2927 FROM igs_ps_unit_ofr_opt
2928 WHERE uoo_id=p_new_uoo_id;
2929 l_cur_get_parameters cur_get_parameters%ROWTYPE;
2930
2931 CURSOR cur_waitlist_chk(cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
2932 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
2933 cp_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE,
2934 cp_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE) IS
2935 SELECT waitlist_allowed
2936 FROM igs_ps_unit_ofr_pat
2937 WHERE unit_cd=cp_unit_cd
2938 AND version_number=cp_version_number
2939 AND cal_type=cp_cal_type
2940 AND ci_sequence_number=cp_ci_sequence_number
2941 AND delete_flag = 'N';
2942 l_cur_waitlist cur_waitlist_chk%ROWTYPE;
2943
2944 CURSOR cur_wlst_limit_new (cp_uoo_id igs_ps_usec_lim_wlst.uoo_id%TYPE) IS
2945 SELECT unit_section_limit_waitlist_id ,waitlist_allowed
2946 FROM igs_ps_usec_lim_wlst
2947 WHERE uoo_id = cp_uoo_id
2948 AND ROWNUM = 1 ;
2949 l_cur_wlst_limit_new cur_wlst_limit_new%ROWTYPE;
2950
2951 BEGIN
2952
2953 OPEN cur_wlst_limit_new(p_new_uoo_id);
2954 FETCH cur_wlst_limit_new INTO l_cur_wlst_limit_new;
2955 IF cur_wlst_limit_new%NOTFOUND THEN
2956 --following validation is added by sarakshi,bug#2332807
2957 --if waitlist allowed is set to N at unit pattern level then in igs_ps_usec_lim_wlst we should pass N only
2958 --and not allow any insert in priority and preferences table.
2959 OPEN cur_get_parameters;
2960 FETCH cur_get_parameters INTO l_cur_get_parameters;
2961 CLOSE cur_get_parameters;
2962
2963 OPEN cur_waitlist_chk(l_cur_get_parameters.unit_cd,l_cur_get_parameters.version_number,
2964 l_cur_get_parameters.cal_type,l_cur_get_parameters.ci_sequence_number);
2965 FETCH cur_waitlist_chk INTO l_cur_waitlist;
2966 CLOSE cur_waitlist_chk;
2967 IF NVL(l_cur_waitlist.waitlist_allowed,'N') = 'N' THEN
2968 wlst_limit_rec.waitlist_allowed:='N';
2969 wlst_limit_rec.max_students_per_waitlist:=0;
2970 END IF;
2971
2972
2973 igs_ps_usec_lim_wlst_pkg.insert_row(
2974 x_rowid => lv_rowid,
2975 x_unit_section_limit_wlst_id => l_usec_lim_id,
2976 x_uoo_id => p_new_uoo_id,
2977 x_enrollment_expected => wlst_limit_rec.enrollment_expected ,
2978 x_enrollment_minimum => wlst_limit_rec.enrollment_minimum ,
2979 x_enrollment_maximum => wlst_limit_rec.enrollment_maximum ,
2980 x_advance_maximum => wlst_limit_rec.advance_maximum,
2981 x_override_enrollment_max => wlst_limit_rec.override_enrollment_max,
2982 x_waitlist_allowed => wlst_limit_rec.waitlist_allowed ,
2983 x_max_students_per_waitlist => wlst_limit_rec.max_students_per_waitlist,
2984 x_max_auditors_allowed => wlst_limit_rec.max_auditors_allowed,
2985 x_mode => 'R'
2986 );
2987 END IF;
2988 CLOSE cur_wlst_limit_new;
2989 IF NVL(l_cur_waitlist.waitlist_allowed,l_cur_wlst_limit_new.waitlist_allowed) = 'Y' THEN
2990 -- Rollover of unit section waitlist priority records
2991 FOR wlst_pri_rec IN wlst_pri (p_old_uoo_Id) LOOP
2992 DECLARE
2993 CURSOR cur_wlst_pri_new(cp_uoo_id igs_ps_usec_wlst_pri.uoo_id%TYPE,
2994 cp_priority_number igs_ps_usec_wlst_pri.priority_number%TYPE,
2995 cp_priority_value igs_ps_usec_wlst_pri.priority_value%TYPE) IS
2996 SELECT unit_sec_waitlist_priority_id
2997 FROM igs_ps_usec_wlst_pri
2998 WHERE uoo_id = cp_uoo_id
2999 AND priority_number = cp_priority_number
3000 AND priority_value = cp_priority_value
3001 AND ROWNUM = 1 ;
3002 l_cur_wlst_pri_new cur_wlst_pri_new%ROWTYPE;
3003
3004 lv_rowid VARCHAR2(25);
3005 l_usec_pri_id NUMBER;
3006 BEGIN
3007 OPEN cur_wlst_pri_new(p_new_uoo_id, wlst_pri_rec.priority_number, wlst_pri_rec.priority_value);
3008 FETCH cur_wlst_pri_new INTO l_cur_wlst_pri_new;
3009 IF cur_wlst_pri_new%NOTFOUND THEN
3010 igs_ps_usec_wlst_pri_pkg.insert_row(
3011 x_rowid => lv_rowid,
3012 x_unit_sec_wlst_priority_id => l_usec_pri_id,
3013 x_priority_number => wlst_pri_rec.priority_number ,
3014 x_priority_value => wlst_pri_rec.priority_value ,
3015 x_uoo_id => p_new_uoo_Id,
3016 x_mode => 'R'
3017 );
3018 END IF;
3019 CLOSE cur_wlst_pri_new;
3020
3021 -- Rollover of unit section waitlist preference records
3022 FOR wlst_prf_rec IN wlst_prf (wlst_pri_rec.unit_sec_waitlist_priority_id ) LOOP
3023 DECLARE
3024 CURSOR cur_wlst_prf_new(cp_unit_sec_wlst_priority_id igs_ps_usec_wlst_prf.unit_sec_waitlist_priority_id%TYPE,
3025 cp_preference_code igs_ps_usec_wlst_prf.preference_code%TYPE,
3026 cp_preference_version igs_ps_usec_wlst_prf.preference_version%TYPE) IS
3027 SELECT 'X'
3028 FROM igs_ps_usec_wlst_prf
3029 WHERE unit_sec_waitlist_priority_id = cp_unit_sec_wlst_priority_id
3030 AND preference_code = cp_preference_code
3031 AND preference_version = cp_preference_version
3032 AND ROWNUM = 1 ;
3033 l_cur_wlst_prf_new cur_wlst_prf_new%ROWTYPE;
3034
3035 lv_rowid VARCHAR2(25);
3036 l_usec_prf_id NUMBER;
3037 BEGIN
3038
3039 OPEN cur_wlst_prf_new(NVL(l_usec_pri_id,l_cur_wlst_pri_new.unit_sec_waitlist_priority_id), wlst_prf_rec.preference_code, wlst_prf_rec.preference_version);
3040 FETCH cur_wlst_prf_new INTO l_cur_wlst_prf_new;
3041 IF cur_wlst_prf_new%NOTFOUND THEN
3042 igs_ps_usec_wlst_prf_pkg.insert_row(
3043 x_rowid => lv_rowid,
3044 x_unit_sec_waitlist_pref_id => l_usec_prf_id,
3045 x_unit_sec_wlst_priority_id => NVL(l_usec_pri_id,l_cur_wlst_pri_new.unit_sec_waitlist_priority_id),
3046 x_preference_order => wlst_prf_rec.preference_order ,
3047 x_preference_code => wlst_prf_rec.preference_code ,
3048 x_preference_version => wlst_prf_rec.preference_version ,
3049 x_mode => 'R');
3050 END IF;
3051 CLOSE cur_wlst_prf_new;
3052
3053 EXCEPTION
3054 WHEN OTHERS THEN
3055 --Fnd log implementation
3056 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3057 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3058 SUBSTRB('Unit Sec Waitlist Pref Id:'||wlst_prf_rec.unit_sec_waitlist_pref_id||' '||
3059 NVL(fnd_message.get,SQLERRM),1,4000));
3060 END IF;
3061 END;
3062 END LOOP;
3063
3064 EXCEPTION
3065 WHEN OTHERS THEN
3066 --Fnd log implementation
3067 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3068 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3069 SUBSTRB('Unit Sec Wait Priority Id:'||wlst_pri_rec.unit_sec_waitlist_priority_id||' '||
3070 NVL(fnd_message.get,SQLERRM),1,4000));
3071 END IF;
3072 END;
3073 END LOOP;
3074 END IF;
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 --Fnd log implementation
3078 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3079 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3080 SUBSTRB('unit section limit wlst id:'||wlst_limit_rec.unit_section_limit_waitlist_id||' '||
3081 NVL(fnd_message.get,SQLERRM),1,4000));
3082 END IF;
3083 END;
3084 END LOOP;
3085
3086 -- Rollover of unit section credit points records
3087 FOR usec_cps_rec IN usec_cps(p_old_uoo_Id ) LOOP
3088 DECLARE
3089 CURSOR cur_usec_cps_new (cp_uoo_id igs_ps_usec_cps.uoo_id%TYPE) IS
3090 SELECT 'X'
3091 FROM igs_ps_usec_cps
3092 WHERE uoo_id = cp_uoo_id
3093 AND ROWNUM = 1;
3094 l_cur_usec_cps_new cur_usec_cps_new%ROWTYPE;
3095
3096 lv_rowid VARCHAR2(25);
3097 l_usec_cps_id NUMBER;
3098 BEGIN
3099
3100 OPEN cur_usec_cps_new(p_new_uoo_id);
3101 FETCH cur_usec_cps_new INTO l_cur_usec_cps_new;
3102 IF cur_usec_cps_new%NOTFOUND THEN
3103 igs_ps_usec_cps_pkg.insert_row(
3104 x_rowid => lv_rowid,
3105 x_unit_sec_credit_points_id => l_usec_cps_id,
3106 x_uoo_id => p_new_uoo_id,
3107 x_minimum_credit_points => usec_cps_rec.minimum_credit_points ,
3108 x_maximum_credit_points => usec_cps_rec.maximum_credit_points ,
3109 x_variable_increment => usec_cps_rec.variable_increment ,
3110 x_lecture_credit_points => usec_cps_rec.lecture_credit_points ,
3111 x_lab_credit_points => usec_cps_rec.lab_credit_points ,
3112 x_other_credit_points => usec_cps_rec.other_credit_points ,
3113 x_clock_hours => usec_cps_rec.clock_hours ,
3114 x_work_load_cp_lecture => usec_cps_rec.work_load_cp_lecture ,
3115 x_work_load_cp_lab => usec_cps_rec.work_load_cp_lab ,
3116 x_continuing_education_units => usec_cps_rec.continuing_education_units ,
3117 x_work_load_other => usec_cps_rec.work_load_other,
3118 x_contact_hrs_lecture => usec_cps_rec.contact_hrs_lecture ,
3119 x_contact_hrs_lab => usec_cps_rec.contact_hrs_lab,
3120 x_contact_hrs_other => usec_cps_rec.contact_hrs_other,
3121 x_non_schd_required_hrs => usec_cps_rec.non_schd_required_hrs,
3122 x_exclude_from_max_cp_limit => usec_cps_rec.exclude_from_max_cp_limit,
3123 x_mode => 'R',
3124 x_claimable_hours => usec_cps_rec.claimable_hours,
3125 x_achievable_credit_points => usec_cps_rec.achievable_credit_points,
3126 x_enrolled_credit_points => usec_cps_rec.enrolled_credit_points,
3127 x_billing_credit_points => usec_cps_rec.billing_credit_points,
3128 x_billing_hrs => usec_cps_rec.billing_hrs
3129 );
3130 END IF;
3131 CLOSE cur_usec_cps_new;
3132
3133 EXCEPTION
3134 WHEN OTHERS THEN
3135 --Fnd log implementation
3136 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3137 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3138 SUBSTRB('Unit Sec Credit Points Id:'||usec_cps_rec.unit_sec_credit_points_id||' '||
3139 NVL(fnd_message.get,SQLERRM),1,4000));
3140 END IF;
3141 END;
3142 END LOOP;
3143
3144 -- Enhancement bug no 1800179, pmarada
3145 -- removed unit section cross listed units and unit section cross list unit sections
3146 -- added the unit section cross list group and unit section group member.
3147 -- Unit SECTION cross list GROUP
3148 DECLARE
3149 CURSOR cur_usec_x_grpmem_new (cp_uoo_id igs_ps_usec_x_grpmem.uoo_id%TYPE) IS
3150 SELECT 'X'
3151 FROM igs_ps_usec_x_grpmem
3152 WHERE uoo_id = cp_uoo_id
3153 AND ROWNUM = 1;
3154 l_cur_usec_x_grpmem_new cur_usec_x_grpmem_new%ROWTYPE;
3155
3156 lnusec_x_listed_group_mem_id NUMBER;
3157 lv_GrpMemrowid VARCHAR2(25);
3158 lnUsec_X_Grp_Id NUMBER;
3159 usec_x_grp_rec usec_x_grp%ROWTYPE;
3160 lvGRp_Row_ID VARCHAR2(25);
3161 --added var l_parent and its usage inside this block,bug#2563596
3162 l_parent VARCHAR2(1);
3163 -- See IF the GROUP name OF which the OLD unit SECTION IS a member already exists
3164
3165 BEGIN
3166 FOR usec_x_grpmem_rec IN usec_x_grpmem(p_old_uoo_Id) LOOP
3167 OPEN usec_x_grp(usec_x_grpmem_rec.usec_x_listed_group_name, gv_new_usec_rec.cal_type, gv_new_usec_rec.ci_sequence_number);
3168 FETCH usec_x_grp INTO usec_x_grp_rec;
3169
3170 IF usec_x_grp%FOUND THEN
3171 lnUsec_X_Grp_Id := usec_x_grp_rec.usec_x_listed_group_id;
3172 CLOSE usec_x_grp;
3173 l_parent:='N';
3174
3175 ELSE
3176 CLOSE usec_x_grp;
3177 igs_ps_usec_x_grp_pkg.insert_row (
3178 x_rowid => lvGRp_Row_ID,
3179 x_usec_x_listed_group_id => lnUsec_X_Grp_Id,
3180 x_usec_x_listed_group_name => usec_x_grpmem_rec.usec_x_listed_group_name,
3181 x_location_inheritance => usec_x_grpmem_rec.location_inheritance,
3182 x_cal_type => gv_new_usec_rec.cal_type,
3183 x_ci_sequence_number => gv_new_usec_rec.ci_sequence_number,
3184 x_max_enr_group => usec_x_grpmem_rec.max_enr_group,
3185 x_max_ovr_group => usec_x_grpmem_rec.max_ovr_group,
3186 x_mode => 'R' );
3187
3188 l_parent:='Y';
3189 END IF;
3190
3191 OPEN cur_usec_x_grpmem_new(p_new_uoo_id);
3192 FETCH cur_usec_x_grpmem_new INTO l_cur_usec_x_grpmem_new;
3193 IF cur_usec_x_grpmem_new%NOTFOUND THEN
3194 -- Rollover of unit section in cross listed group
3195 igs_ps_usec_x_grpmem_pkg.insert_row (
3196 x_rowid => lv_GrpMemrowid,
3197 x_usec_x_listed_group_mem_id => lnusec_x_listed_group_mem_id,
3198 x_usec_x_listed_group_id => lnUsec_X_Grp_Id,
3199 x_uoo_id => p_new_uoo_id,
3200 x_parent => l_parent,
3201 x_mode => 'R' );
3202 END IF;
3203 CLOSE cur_usec_x_grpmem_new;
3204
3205 END LOOP;
3206 EXCEPTION
3207 WHEN OTHERS THEN
3208 --Fnd log implementation
3209 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3210 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3211 SUBSTRB('Unit Section-CrossListed:'||p_old_uoo_id||' '||
3212 NVL(fnd_message.get,SQLERRM),1,4000));
3213 END IF;
3214 END;
3215
3216
3217 --Enhancement bug no 1800179, pmarada
3218 -- Rollover of unit section sponsorship records
3219 FOR usec_spn_rec IN usec_spn (p_old_uoo_Id ) LOOP
3220 DECLARE
3221 CURSOR cur_usec_spn_new (cp_uoo_id igs_ps_usec_spnsrshp.uoo_id%TYPE,
3222 cp_organization_code igs_ps_usec_spnsrshp.organization_code%TYPE) IS
3223 SELECT 'X'
3224 FROM igs_ps_usec_spnsrshp
3225 WHERE uoo_id = cp_uoo_id
3226 AND organization_code = cp_organization_code
3227 AND ROWNUM = 1;
3228 l_cur_usec_spn_new cur_usec_spn_new%ROWTYPE;
3229
3230 lv_rowid VARCHAR2(25);
3231 l_usec_spn_id NUMBER;
3232 BEGIN
3233
3234 OPEN cur_usec_spn_new(p_new_uoo_id,usec_spn_rec.organization_code);
3235 FETCH cur_usec_spn_new INTO l_cur_usec_spn_new;
3236 IF cur_usec_spn_new%NOTFOUND THEN
3237 igs_ps_usec_spnsrshp_pkg.insert_row (
3238 x_rowid => lv_rowid,
3239 x_unit_section_sponsorship_id => l_usec_spn_id,
3240 x_uoo_id => p_new_uoo_id,
3241 x_organization_code => usec_spn_rec.organization_code ,
3242 x_sponsorship_percentage => usec_spn_rec.sponsorship_percentage ,
3243 x_mode => 'R'
3244 );
3245 END IF;
3246 CLOSE cur_usec_spn_new;
3247
3248 EXCEPTION
3249 WHEN OTHERS THEN
3250 --Fnd log implementation
3251 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3252 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3253 SUBSTRB('Unit Section Sponsorship Id:'||usec_spn_rec.unit_section_sponsorship_id||' '||
3254 NVL(fnd_message.get,SQLERRM),1,4000));
3255 END IF;
3256 END;
3257 END LOOP;
3258
3259
3260 -- Rollover of unit section teaching responsibility records
3261 FOR usec_tchr_rec IN usec_tchr(p_old_uoo_Id ) LOOP
3262 DECLARE
3263 CURSOR cur_usec_tchr_new (cp_uoo_id igs_ps_usec_tch_resp.uoo_id%TYPE,
3264 cp_instructor_id igs_ps_usec_tch_resp.instructor_id%TYPE) IS
3265 SELECT 'X'
3266 FROM igs_ps_usec_tch_resp
3267 WHERE uoo_id = cp_uoo_id
3268 AND instructor_id = cp_instructor_id
3269 AND ROWNUM = 1;
3270 l_cur_usec_tchr_new cur_usec_tchr_new%ROWTYPE;
3271
3272 CURSOR c_tch_ins(cp_uoo_id igs_ps_usec_occurs_all.uoo_id%TYPE,cp_ins_id igs_ps_uso_instrctrs.instructor_id%TYPE) IS
3273 SELECT 'X'
3274 FROM igs_ps_usec_occurs_all a,igs_ps_uso_instrctrs b
3275 WHERE a.UNIT_SECTION_OCCURRENCE_ID = b.UNIT_SECTION_OCCURRENCE_ID
3276 AND a.uoo_id = cp_uoo_id
3277 AND b.instructor_id= cp_ins_id;
3278 c_tch_ins_rec c_tch_ins%ROWTYPE;
3279
3280 l_ins_exists BOOLEAN := TRUE;
3281 lv_rowid VARCHAR2(25);
3282 l_usec_tchr_id NUMBER;
3283 BEGIN
3284
3285 OPEN c_tch_ins(p_new_uoo_id, usec_tchr_rec.instructor_id);
3286 FETCH c_tch_ins INTO c_tch_ins_rec;
3287 IF c_tch_ins%NOTFOUND THEN
3288 l_ins_exists := FALSE;
3289 END IF;
3290 CLOSE c_tch_ins;
3291 OPEN cur_usec_tchr_new(p_new_uoo_id, usec_tchr_rec.instructor_id);
3292 FETCH cur_usec_tchr_new INTO l_cur_usec_tchr_new;
3293 IF cur_usec_tchr_new%NOTFOUND AND l_ins_exists THEN
3294 igs_ps_usec_tch_resp_pkg.insert_row(
3295 x_rowid => lv_rowid,
3296 x_unit_section_teach_resp_id => l_usec_tchr_id,
3297 x_instructor_id => usec_tchr_rec.instructor_id ,
3298 x_confirmed_flag => usec_tchr_rec.confirmed_flag ,
3299 x_percentage_allocation => usec_tchr_rec.percentage_allocation ,
3300 x_instructional_load => usec_tchr_rec.instructional_load ,
3301 x_lead_instructor_flag => usec_tchr_rec.lead_instructor_flag ,
3302 x_uoo_id => p_new_uoo_id,
3303 x_instructional_load_lab => usec_tchr_rec.instructional_load_lab,
3304 x_instructional_load_lecture => usec_tchr_rec.instructional_load_lecture,
3305 x_mode => 'R'
3306 );
3307 END IF;
3308 CLOSE cur_usec_tchr_new;
3309
3310 EXCEPTION
3311 WHEN OTHERS THEN
3312 --Fnd log implementation
3313 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3314 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3315 SUBSTRB('unit section teach resp id:'||usec_tchr_rec.unit_section_teach_resp_id||' '||
3316 NVL(fnd_message.get,SQLERRM),1,4000));
3317 END IF;
3318 END;
3319 END LOOP;
3320
3321 -- Rollover of unit section assessment records
3322 FOR usec_as_rec IN usec_as (p_old_uoo_Id ) LOOP
3323 DECLARE
3324 CURSOR cur_usec_as_new (cp_uoo_id igs_ps_usec_as.uoo_id%TYPE) IS
3325 SELECT 'X'
3326 FROM igs_ps_usec_as
3327 WHERE uoo_id = cp_uoo_id
3328 AND ROWNUM = 1;
3329 l_cur_usec_as_new cur_usec_as_new%ROWTYPE;
3330
3331 lv_rowid VARCHAR2(25);
3332 l_usec_ass_id NUMBER;
3333 BEGIN
3334
3335 OPEN cur_usec_as_new(p_new_uoo_id);
3336 FETCH cur_usec_as_new INTO l_cur_usec_as_new;
3337 IF cur_usec_as_new%NOTFOUND THEN
3338 igs_ps_usec_as_pkg.insert_row(
3339 x_rowid => lv_rowid,
3340 x_unit_section_assessment_id => l_usec_ass_id,
3341 x_uoo_id => p_new_uoo_id,
3342 x_final_exam_date => usec_as_rec.final_exam_date ,
3343 x_exam_start_time => usec_as_rec.exam_start_time ,
3344 x_exam_end_time => usec_as_rec.exam_end_time ,
3345 x_location_cd => usec_as_rec.location_cd ,
3346 x_building_code => usec_as_rec.building_code ,
3347 x_room_code => usec_as_rec.room_code ,
3348 x_mode => 'R'
3349 );
3350 END IF;
3351 CLOSE cur_usec_as_new;
3352
3353 EXCEPTION
3354 WHEN OTHERS THEN
3355 --Fnd log implementation
3356 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3357 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3358 SUBSTRB('Unit Section Assessment Id-Exam:'||usec_as_rec.unit_section_assessment_id||' '||
3359 NVL(fnd_message.get,SQLERRM),1,4000));
3360 END IF;
3361 END;
3362 END LOOP;
3363
3364 --ijeddy, Grade Book Enh build, bug no 3201661, Dec 3, 2003.
3365 FOR usec_unitassgrp_rec IN usec_unitassgrp (p_old_uoo_id) LOOP
3366 DECLARE
3367 CURSOR cur_unitassgrp_new (cp_uoo_id igs_as_us_ai_group.uoo_id%TYPE,
3368 cp_group_name igs_as_us_ai_group.group_name%TYPE) IS
3369 SELECT us_ass_item_group_id
3370 FROM igs_as_us_ai_group
3371 WHERE uoo_id = cp_uoo_id
3372 AND group_name = cp_group_name;
3373 l_rowid VARCHAR2(25);
3374 l_us_ass_item_group_id NUMBER;
3375 BEGIN
3376 l_rowid := NULL;
3377 l_us_ass_item_group_id := NULL;
3378 OPEN cur_unitassgrp_new(p_new_uoo_id,usec_unitassgrp_rec.group_name);
3379 FETCH cur_unitassgrp_new INTO l_us_ass_item_group_id;
3380 IF cur_unitassgrp_new%NOTFOUND THEN
3381 igs_as_us_ai_group_pkg.insert_row (
3382 x_rowid => l_rowid,
3383 x_us_ass_item_group_id => l_us_ass_item_group_id,
3384 x_uoo_id => p_new_uoo_id,
3385 x_group_name => usec_unitassgrp_rec.group_name,
3386 x_midterm_formula_code => usec_unitassgrp_rec.midterm_formula_code,
3387 x_midterm_formula_qty => usec_unitassgrp_rec.midterm_formula_qty,
3388 x_midterm_weight_qty => usec_unitassgrp_rec.midterm_weight_qty,
3389 x_final_formula_code => usec_unitassgrp_rec.final_formula_code,
3390 x_final_formula_qty => usec_unitassgrp_rec.final_formula_qty,
3391 x_final_weight_qty => usec_unitassgrp_rec.final_weight_qty,
3392 x_mode => 'R'
3393 );
3394 END IF;
3395 CLOSE cur_unitassgrp_new;
3396
3397 -- Rollover of unit section assessment item records
3398 --Rollover asssessmnet items if there is none for the section and group combination
3399 OPEN cur_ass_item(p_new_uoo_id,l_us_ass_item_group_id);
3400 FETCH cur_ass_item INTO l_c_var;
3401 IF cur_ass_item%NOTFOUND THEN
3402 FOR usec_unitass_rec IN usec_unitass (p_old_uoo_Id,usec_unitassgrp_rec.us_ass_item_group_id ) LOOP
3403 DECLARE
3404 lv_rowid VARCHAR2(25);
3405 l_usec_assitem_id NUMBER;
3406 l_sequence_number NUMBER;
3407 l_ci_start_dt DATE;
3408 l_ci_end_dt DATE;
3409 BEGIN
3410 -- Modification of the procedure call is done by DDEY as a part of Bug #2162831
3411 igs_ps_unitass_item_pkg.insert_row(
3412 x_rowid => lv_rowid,
3413 x_unit_section_ass_item_id => l_usec_assitem_id,
3414 x_uoo_id => p_new_uoo_id,
3415 x_ass_id => usec_unitass_rec.ass_id ,
3416 x_sequence_number => l_sequence_number,
3417 x_ci_start_dt => l_ci_start_dt,
3418 x_ci_end_dt => l_ci_end_dt,
3419 x_due_dt => usec_unitass_rec.due_dt ,
3420 x_reference => usec_unitass_rec.reference ,
3421 x_dflt_item_ind => usec_unitass_rec.dflt_item_ind ,
3422 x_logical_delete_dt => usec_unitass_rec.logical_delete_dt ,
3423 x_action_dt => usec_unitass_rec.action_dt ,
3424 x_exam_cal_type => usec_unitass_rec.exam_cal_type ,
3425 x_exam_ci_sequence_number => usec_unitass_rec.exam_ci_sequence_number ,
3426 x_mode => 'R' ,
3427 x_grading_schema_cd => usec_unitass_rec.grading_schema_cd,
3428 x_gs_version_number => usec_unitass_rec.gs_version_number,
3429 x_release_date => usec_unitass_rec.release_date,
3430 x_description => usec_unitass_rec.description,
3431 x_us_ass_item_group_id => l_us_ass_item_group_id,
3432 x_midterm_mandatory_type_code => usec_unitass_rec.midterm_mandatory_type_code,
3433 x_midterm_weight_qty => usec_unitass_rec.midterm_weight_qty,
3434 x_final_mandatory_type_code => usec_unitass_rec.final_mandatory_type_code,
3435 x_final_weight_qty => usec_unitass_rec.final_weight_qty
3436 );
3437 EXCEPTION
3438 WHEN OTHERS THEN
3439 --Fnd log implementation
3440 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3441 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3442 SUBSTRB('Unit Section Ass Item Id:'||usec_unitass_rec.unit_section_ass_item_id||' '||
3443 NVL(fnd_message.get,SQLERRM),1,4000));
3444 END IF;
3445 END;
3446 END LOOP;
3447 END IF;
3448 CLOSE cur_ass_item;
3449
3450 EXCEPTION
3451 WHEN OTHERS THEN
3452 --Fnd log implementation
3453 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3454 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3455 SUBSTRB('Unit Section Ass Item Group Id:'||usec_unitassgrp_rec.us_ass_item_group_id||' '||
3456 NVL(fnd_message.get,SQLERRM),1,4000));
3457 END IF;
3458 END;
3459
3460 END LOOP;
3461
3462 -- Rollover of unit section reference records
3463 FOR usec_ref_rec IN usec_ref (p_old_uoo_Id ) LOOP
3464 DECLARE
3465 CURSOR usec_reference ( p_uoo_id NUMBER) IS
3466 SELECT *
3467 FROM igs_ps_usec_ref
3468 WHERE uoo_id = p_uoo_id;
3469 l_usec_ref usec_reference%ROWTYPE;
3470 BEGIN
3471 --added by sarakshi, bug#2332807
3472 --Unit section TBH automatically inserts in igs_ps_usec_ref and igs_ps_usec_ref_cd, so removed those inserts
3473 --only igs_ps_us_req_ref_cd insertion is required
3474
3475 OPEN usec_reference(p_new_uoo_id);
3476 FETCH usec_reference INTO l_usec_ref;
3477 CLOSE usec_reference;
3478
3479 --Added the following For loop as part of bug#2563596
3480 --The Unit section TBH automatically inserts in igs_ps_usec_ref ,but inserts into igs_ps_usec_ref_cd only
3481 --if the reference codes are of mandatory ref code types.Hence an explicit insert into igs_ps_usec_ref_cd
3482 --table has been added here for rolling over all unit section reference code records of old unit section
3483 --into the new unit section
3484
3485 -- Rollover of unit section reference code records
3486 FOR usec_refcd_rec IN usec_refcd (usec_ref_rec.unit_section_reference_id ) LOOP
3487 DECLARE
3488 CURSOR cur_usec_refcd_new (cp_unit_section_reference_id igs_ps_usec_ref_cd.unit_section_reference_id%TYPE,
3489 cp_reference_code_type igs_ps_usec_ref_cd.reference_code_type%TYPE,
3490 cp_reference_code igs_ps_usec_ref_cd.reference_code%TYPE) IS
3491 SELECT 'X'
3492 FROM igs_ps_usec_ref_cd
3493 WHERE unit_section_reference_id = cp_unit_section_reference_id
3494 AND reference_code_type = cp_reference_code_type
3495 AND reference_code = cp_reference_code
3496 AND ROWNUM = 1;
3497 l_cur_usec_refcd_new cur_usec_refcd_new%ROWTYPE;
3498
3499 lv_rowid VARCHAR2(25);
3500 l_unit_section_reference_cd_id NUMBER;
3501 BEGIN
3502
3503 OPEN cur_usec_refcd_new(l_usec_ref.unit_section_reference_id, usec_refcd_rec.reference_code_type, usec_refcd_rec.reference_code);
3504 FETCH cur_usec_refcd_new INTO l_cur_usec_refcd_new;
3505 IF cur_usec_refcd_new%NOTFOUND THEN
3506 igs_ps_usec_ref_cd_pkg.insert_row (
3507 x_rowid => lv_rowid,
3508 x_unit_section_reference_cd_id => l_unit_section_reference_cd_id,
3509 x_unit_section_reference_id => l_usec_ref.unit_section_reference_id,
3510 x_mode => 'R',
3511 x_reference_code_type => usec_refcd_rec.reference_code_type,
3512 x_reference_code => usec_refcd_rec.reference_code,
3513 x_reference_code_desc => usec_refcd_rec.reference_code_desc
3514 );
3515 END IF;
3516 CLOSE cur_usec_refcd_new;
3517
3518 EXCEPTION
3519 WHEN OTHERS THEN
3520 --Fnd log implementation
3521 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3522 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3523 SUBSTRB('Unit Section Reference Id:'||usec_refcd_rec.unit_section_reference_id||' '||'Reference Code Type:'||usec_refcd_rec.reference_code_type||' '||'Reference Code:'||usec_refcd_rec.reference_code||' '||
3524 NVL(fnd_message.get,SQLERRM),1,4000));
3525 END IF;
3526 END;
3527 END LOOP;
3528
3529 --Enhancement bug no 1800179 , pmarada
3530 -- Rollover of unit section requirement reference code records
3531 FOR us_req_refcd_rec IN us_req_refcd (usec_ref_rec.unit_section_reference_id ) LOOP
3532 DECLARE
3533 CURSOR cur_us_req_refcd_new (cp_unit_section_reference_id igs_ps_us_req_ref_cd.unit_section_reference_id%TYPE,
3534 cp_reference_cd_type igs_ps_us_req_ref_cd.reference_cd_type%TYPE,
3535 cp_reference_code igs_ps_us_req_ref_cd.reference_code%TYPE) IS
3536 SELECT 'X'
3537 FROM igs_ps_us_req_ref_cd
3538 WHERE unit_section_reference_id = cp_unit_section_reference_id
3539 AND reference_cd_type = cp_reference_cd_type
3540 AND reference_code = cp_reference_code
3541 AND ROWNUM = 1;
3542 l_cur_us_req_refcd_new cur_us_req_refcd_new%ROWTYPE;
3543
3544 lv_rowid VARCHAR2(25);
3545 l_unit_section_req_ref_cd_id NUMBER;
3546 BEGIN
3547
3548 OPEN cur_us_req_refcd_new(l_usec_ref.unit_section_reference_id,us_req_refcd_rec.reference_cd_type, us_req_refcd_rec.reference_code);
3549 FETCH cur_us_req_refcd_new INTO l_cur_us_req_refcd_new;
3550 IF cur_us_req_refcd_new%NOTFOUND THEN
3551 igs_ps_us_req_ref_cd_pkg.insert_row (
3552 x_rowid => lv_rowid,
3553 x_unit_section_req_ref_cd_id => l_unit_section_req_ref_cd_id,
3554 x_unit_section_reference_id => l_usec_ref.unit_section_reference_id,
3555 x_reference_cd_type => us_req_refcd_rec.reference_cd_type,
3556 x_mode => 'R',
3557 x_reference_code => us_req_refcd_rec.reference_code,
3558 x_reference_code_desc => us_req_refcd_rec.reference_code_desc
3559 );
3560 END IF;
3561 CLOSE cur_us_req_refcd_new;
3562
3563 EXCEPTION
3564 WHEN OTHERS THEN
3565 --Fnd log implementation
3566 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3567 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3568 SUBSTRB('Unit Section Reference Id:'||us_req_refcd_rec.unit_section_reference_id||' '||'Reference Cd Type:'||us_req_refcd_rec.reference_cd_type||' '||'Reference Cd:'||us_req_refcd_rec.reference_code||' '||
3569 NVL(fnd_message.get,SQLERRM),1,4000));
3570 END IF;
3571 END;
3572 END LOOP;
3573
3574 END;
3575 END LOOP;
3576
3577 -- Rollover of unit section grading schema records
3578 FOR usec_grdsch_rec IN usec_grdsch (p_old_uoo_Id )LOOP
3579 DECLARE
3580 CURSOR cur_usec_grdsch_new (cp_uoo_id igs_ps_usec_grd_schm.uoo_id%TYPE,
3581 cp_grading_schema_code igs_ps_usec_grd_schm.grading_schema_code%TYPE,
3582 cp_grd_schm_version_number igs_ps_usec_grd_schm.grd_schm_version_number%TYPE) IS
3583 SELECT 'X'
3584 FROM igs_ps_usec_grd_schm
3585 WHERE uoo_id = cp_uoo_id
3586 AND grading_schema_code = cp_grading_schema_code
3587 AND grd_schm_version_number = cp_grd_schm_version_number
3588 AND ROWNUM = 1;
3589 l_cur_usec_grdsch_new cur_usec_grdsch_new%ROWTYPE;
3590
3591 CURSOR cur_usec_gs_df(cp_uoo_id igs_ps_usec_grd_schm.uoo_id%TYPE) IS
3592 SELECT grading_schema_code,grd_schm_version_number
3593 FROM igs_ps_usec_grd_schm
3594 WHERE uoo_id = cp_uoo_id
3595 AND default_flag = 'Y';
3596 cur_usec_gs_df_rec cur_usec_gs_df%ROWTYPE;
3597
3598 l_default_flag BOOLEAN := TRUE;
3599 lv_rowid VARCHAR2(25);
3600 l_usec_grdsch_id NUMBER;
3601 BEGIN
3602
3603 OPEN cur_usec_gs_df(p_new_uoo_id);
3604 FETCH cur_usec_gs_df INTO cur_usec_gs_df_rec;
3605 IF cur_usec_gs_df%FOUND AND usec_grdsch_rec.default_flag ='Y' THEN
3606 l_default_flag := FALSE;
3607 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3608 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_PS_GEN_001.crsp_ins_unit_section.usec_grading_schema_not_rolled_over_one_default_already_in_destination_unit_section',
3609 'Not rolling over Default Grading Schema code'||usec_grdsch_rec.grading_schema_code||' '||
3610 'version number:'||usec_grdsch_rec.grd_schm_version_number||' '||'from Source UOO'||p_old_uoo_Id||' '||
3611 'to Destination UOO:'||p_new_uoo_id||' '||'as there already exists a different Default Grading Schema code'||cur_usec_gs_df_rec.grading_schema_code||' '||
3612 'version number'||cur_usec_gs_df_rec.grd_schm_version_number||' '||'in the destination');
3613 END IF;
3614 END IF;
3615 CLOSE cur_usec_gs_df;
3616 OPEN cur_usec_grdsch_new(p_new_uoo_id,usec_grdsch_rec.grading_schema_code,usec_grdsch_rec.grd_schm_version_number );
3617 FETCH cur_usec_grdsch_new INTO l_cur_usec_grdsch_new;
3618 IF cur_usec_grdsch_new%NOTFOUND AND l_default_flag THEN
3619 igs_ps_usec_grd_schm_pkg.insert_row(
3620 x_rowid => lv_rowid,
3621 x_unit_section_grad_schema_id => l_usec_grdsch_id,
3622 x_uoo_id => p_new_uoo_id,
3623 x_grading_schema_code => usec_grdsch_rec.grading_schema_code ,
3624 x_grd_schm_version_number => usec_grdsch_rec.grd_schm_version_number ,
3625 x_default_flag => usec_grdsch_rec.default_flag ,
3626 x_mode => 'R'
3627 );
3628 END IF;
3629 CLOSE cur_usec_grdsch_new;
3630
3631 EXCEPTION
3632 WHEN OTHERS THEN
3633 --Fnd log implementation
3634 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3635 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3636 SUBSTRB('Unit Section Grad Schema Id:'||usec_grdsch_rec.unit_section_grading_schema_id||' '||
3637 NVL(fnd_message.get,SQLERRM),1,4000));
3638 END IF;
3639 END;
3640 END LOOP;
3641
3642 -- Enhancement bug no 1800179 , pmarada
3643 -- Rollover of unit section note records
3644 FOR c_unt_ofr_opt_n_rec IN c_unt_ofr_opt_n( p_old_uoo_id ) LOOP
3645 DECLARE
3646 CURSOR cur_c_unt_ofr_opt_n_new (cp_uoo_id igs_ps_unt_ofr_opt_n.uoo_id%TYPE,
3647 cp_crs_note_type igs_ps_unt_ofr_opt_n.crs_note_type%TYPE) IS
3648 SELECT 'X'
3649 FROM igs_ps_unt_ofr_opt_n
3650 WHERE uoo_id = cp_uoo_id
3651 AND crs_note_type = cp_crs_note_type
3652 AND ROWNUM = 1;
3653 l_cur_c_unt_ofr_opt_n_new cur_c_unt_ofr_opt_n_new%ROWTYPE;
3654
3655 lv_usrowid VARCHAR2(25);
3656 lv_gerowid VARCHAR2(25);
3657 lnnote_ref_number NUMBER;
3658 BEGIN
3659
3660 OPEN cur_c_unt_ofr_opt_n_new(p_new_uoo_id, c_unt_ofr_opt_n_rec.crs_note_type);
3661 FETCH cur_c_unt_ofr_opt_n_new INTO l_cur_c_unt_ofr_opt_n_new;
3662 IF cur_c_unt_ofr_opt_n_new%NOTFOUND THEN
3663 --insert a record in the igs_ge_note table corresponding to the reference number create
3664 -- for unit section note.
3665
3666 SELECT IGS_GE_NOTE_RF_NUM_S.NEXTVAL INTO lnnote_ref_number FROM dual;
3667
3668 igs_ge_note_pkg.insert_row(
3669 x_rowid => lv_gerowid,
3670 x_reference_number => lnnote_ref_number ,
3671 x_s_note_format_type => c_unt_ofr_opt_n_rec.s_note_format_type,
3672 x_note_text => c_unt_ofr_opt_n_rec.Note_text,
3673 x_mode => 'R' );
3674
3675 igs_ps_unt_ofr_opt_n_pkg.insert_row(
3676 x_rowid => lv_usrowid,
3677 x_unit_cd => gv_new_usec_rec.unit_cd,
3678 x_version_number => gv_new_usec_rec.version_number,
3679 x_ci_sequence_number => gv_new_usec_rec.ci_sequence_number,
3680 x_unit_class => gv_new_usec_rec.unit_class,
3681 x_reference_number => lnnote_ref_number,
3682 x_location_cd => gv_new_usec_rec.location_cd,
3683 x_cal_type => gv_new_usec_rec.cal_type,
3684 x_uoo_id => gv_new_usec_rec.uoo_id,
3685 x_crs_note_type => c_unt_ofr_opt_n_rec.crs_note_type,
3686 x_mode =>'R' );
3687 END IF;
3688 CLOSE cur_c_unt_ofr_opt_n_new;
3689
3690 EXCEPTION
3691 WHEN OTHERS THEN
3692 --Fnd log implementation
3693 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3694 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3695 SUBSTRB('Crs Note Type:'||c_unt_ofr_opt_n_rec.crs_note_type||' '||'Unit Section:'||p_old_uoo_id||' '||
3696 NVL(fnd_message.get,SQLERRM),1,4000));
3697 END IF;
3698 END;
3699 END LOOP;
3700
3701 -- Rollover of unit section rule records
3702 FOR usec_pre_co_req_rule_rec IN usec_pre_co_req_rule (p_old_uoo_id ) LOOP
3703 DECLARE
3704 CURSOR cur_usec_pre_co_req_rule_new (cp_uoo_id igs_ps_usec_ru.uoo_id%TYPE,
3705 cp_s_rule_call_cd igs_ps_usec_ru.s_rule_call_cd%TYPE) IS
3706 SELECT 'X'
3707 FROM igs_ps_usec_ru
3708 WHERE uoo_id = cp_uoo_id
3709 AND s_rule_call_cd = cp_s_rule_call_cd
3710 AND ROWNUM = 1;
3711 l_cur_usec_pre_co_req_rule_new cur_usec_pre_co_req_rule_new%ROWTYPE;
3712
3713 lv_rowid VARCHAR2(25);
3714 l_usecru_id NUMBER;
3715 lv_rule_unprocessed VARCHAR2(4500);
3716 ln_rule_number igs_ps_usec_ru.rul_sequence_number%TYPE;
3717 ln_lov_number NUMBER;
3718 x BOOLEAN;
3719 BEGIN
3720
3721 OPEN cur_usec_pre_co_req_rule_new(p_new_uoo_id,usec_pre_co_req_rule_rec.s_rule_call_cd);
3722 FETCH cur_usec_pre_co_req_rule_new INTO l_cur_usec_pre_co_req_rule_new;
3723 IF cur_usec_pre_co_req_rule_new%NOTFOUND THEN
3724 -- CREATE the relevant data IN rule SCHEMA TABLE AND returns the rule SEQUENCE NUMBER
3725 -- TO be used FOR the rule created FOR NEW unit section.
3726
3727 lv_rule_unprocessed := NULL;
3728 ln_lov_number := NULL;
3729 -- As part of bug fix bug#2563596
3730 -- Modified the values being passed to the parameters in call to igs_ru_gen_002.rulp_ins_parser
3731 -- Passing value usec_pre_co_req_rule_rec.rule_text to parameter p_rule_processed instead of
3732 -- local variable lv_rule_processed.
3733
3734
3735 x := igs_ru_gen_002.rulp_ins_parser (
3736 p_group => usec_pre_co_req_rule_rec.select_group,
3737 p_return_type => usec_pre_co_req_rule_rec.s_return_type,
3738 p_rule_description => usec_pre_co_req_rule_rec.rule_description,
3739 p_rule_processed => usec_pre_co_req_rule_rec.rule_text,
3740 p_rule_unprocessed => lv_rule_unprocessed,
3741 p_generate_rule => TRUE,
3742 p_rule_number => ln_rule_number,
3743 p_lov_number => ln_lov_number );
3744
3745
3746 -- USE the returned SEQUENCE NUMBER FOR inserting the NEW RECORD FOR the
3747 -- NEW Unit section.
3748
3749 -- As part of bug fix bug#2563596
3750 -- Added an IF condition that checks if the function igs_ru_gen_002.rulp_ins_parser
3751 -- returns a TRUE or FALSE.Only if it returns TRUE then insert into igs_ps_usec_ru
3752
3753 IF x THEN
3754 igs_ps_usec_ru_pkg.insert_row (
3755 x_rowid => lv_rowid,
3756 x_usecru_id => l_usecru_id,
3757 x_uoo_id => p_new_uoo_id,
3758 x_s_rule_call_cd => usec_pre_co_req_rule_rec.s_rule_call_cd,
3759 x_rul_sequence_number => ln_rule_number,
3760 x_mode => 'R' );
3761 END IF;
3762 END IF;
3763 CLOSE cur_usec_pre_co_req_rule_new;
3764
3765 EXCEPTION
3766 WHEN OTHERS THEN
3767 --Fnd log implementation
3768 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3769 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3770 SUBSTRB('S Rule Call Cd:'||usec_pre_co_req_rule_rec.s_rule_call_cd||' '||'Unit Section:'||p_old_uoo_id||' '||
3771 NVL(fnd_message.get,SQLERRM),1,4000));
3772 END IF;
3773 END;
3774 END LOOP;
3775
3776 -- Rollover of unit section categories record
3777 FOR usec_cat_rec IN usec_cat (p_old_uoo_id ) LOOP
3778 DECLARE
3779 CURSOR cur_usec_cat_new (cp_uoo_id igs_ps_usec_category.uoo_id%TYPE,
3780 cp_unit_cat igs_ps_usec_category.unit_cat%TYPE) IS
3781 SELECT 'X'
3782 FROM igs_ps_usec_category
3783 WHERE uoo_id = cp_uoo_id
3784 AND unit_cat = cp_unit_cat
3785 AND ROWNUM = 1;
3786 l_cur_usec_cat_new cur_usec_cat_new%ROWTYPE;
3787
3788 lv_rowid VARCHAR2(25);
3789 l_usec_cat_id NUMBER;
3790 BEGIN
3791
3792 OPEN cur_usec_cat_new(p_new_uoo_id,usec_cat_rec.unit_cat);
3793 FETCH cur_usec_cat_new INTO l_cur_usec_cat_new;
3794 IF cur_usec_cat_new%NOTFOUND THEN
3795 igs_ps_usec_category_pkg.insert_row (
3796 x_rowid => lv_rowid,
3797 x_usec_cat_id => l_usec_cat_id,
3798 x_uoo_id => p_new_uoo_id,
3799 x_unit_cat => usec_cat_rec.unit_cat,
3800 x_mode => 'R' );
3801 END IF;
3802 CLOSE cur_usec_cat_new;
3803
3804 EXCEPTION
3805 WHEN OTHERS THEN
3806 --Fnd log implementation
3807 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3808 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3809 SUBSTRB('unit Cat:'||usec_cat_rec.unit_cat||' '||'Unit Section:'||p_old_uoo_id||' '||
3810 NVL(fnd_message.get,SQLERRM),1,4000));
3811 END IF;
3812 END;
3813 END LOOP;
3814
3815 -- Rollover of unit section plus hour records
3816 FOR usec_plushr_rec IN usec_plushr (p_old_uoo_id ) LOOP
3817 DECLARE
3818 CURSOR cur_usec_plushr_new (cp_uoo_id igs_ps_us_unsched_cl.uoo_id%TYPE,
3819 cp_activity_type_id igs_ps_us_unsched_cl.activity_type_id%TYPE,
3820 cp_location_cd igs_ps_us_unsched_cl.location_cd%TYPE,
3821 cp_building_id igs_ps_us_unsched_cl.building_id%TYPE,
3822 cp_room_id igs_ps_us_unsched_cl.room_id%TYPE) IS
3823 SELECT 'X'
3824 FROM igs_ps_us_unsched_cl
3825 WHERE uoo_id = cp_uoo_id
3826 AND activity_type_id = cp_activity_type_id
3827 AND location_cd = cp_location_cd
3828 AND building_id = cp_building_id
3829 AND room_id = cp_room_id
3830 AND ROWNUM = 1;
3831 l_cur_usec_plushr_new cur_usec_plushr_new%ROWTYPE;
3832
3833 lv_rowid VARCHAR2(25);
3834 l_us_unscheduled_cl_id NUMBER;
3835 BEGIN
3836
3837 OPEN cur_usec_plushr_new(p_new_uoo_id,usec_plushr_rec.activity_type_id,usec_plushr_rec.location_cd,usec_plushr_rec.building_id,usec_plushr_rec.room_id);
3838 FETCH cur_usec_plushr_new INTO l_cur_usec_plushr_new;
3839 IF cur_usec_plushr_new%NOTFOUND THEN
3840 igs_ps_us_unsched_cl_pkg.insert_row (
3841 x_rowid => lv_rowid,
3842 x_us_unscheduled_cl_id => l_us_unscheduled_cl_id,
3843 x_uoo_id => p_new_uoo_id,
3844 x_activity_type_id => usec_plushr_rec.activity_type_id,
3845 x_location_cd => usec_plushr_rec.location_cd,
3846 x_building_id => usec_plushr_rec.building_id,
3847 x_room_id => usec_plushr_rec.room_id,
3848 x_number_of_students => usec_plushr_rec.number_of_students,
3849 x_hours_per_student => usec_plushr_rec.hours_per_student,
3850 x_hours_per_faculty => usec_plushr_rec.hours_per_faculty,
3851 x_instructor_id => usec_plushr_rec.instructor_id,
3852 x_mode => 'R' );
3853 END IF;
3854 CLOSE cur_usec_plushr_new;
3855
3856 EXCEPTION
3857 WHEN OTHERS THEN
3858 --Fnd log implementation
3859 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3860 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3861 SUBSTRB('Us Unscheduled Cl Id:'||usec_plushr_rec.us_unscheduled_cl_id||' '||
3862 NVL(fnd_message.get,SQLERRM),1,4000));
3863 END IF;
3864 END;
3865 END LOOP;
3866
3867
3868 -- Rollover of unit section teaching responsibility override records
3869 FOR usec_tro_rec IN usec_tro (p_old_uoo_id ) LOOP
3870 DECLARE
3871 cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
3872 l_ou_exists VARCHAR2(1);
3873 l_rowid VARCHAR2(25);
3874
3875 CURSOR cur_usec_tro_new (cp_unit_cd igs_ps_tch_resp_ovrd.unit_cd%TYPE,
3876 cp_version_number igs_ps_tch_resp_ovrd.version_number%TYPE,
3877 cp_cal_type igs_ps_tch_resp_ovrd.cal_type%TYPE,
3878 cp_ci_sequence_number igs_ps_tch_resp_ovrd.ci_sequence_number%TYPE,
3879 cp_location_cd igs_ps_tch_resp_ovrd.location_cd%TYPE,
3880 cp_unit_class igs_ps_tch_resp_ovrd.unit_class%TYPE,
3881 cp_org_unit_cd igs_ps_tch_resp_ovrd.org_unit_cd%TYPE,
3882 cp_ou_start_dt igs_ps_tch_resp_ovrd.ou_start_dt%TYPE) IS
3883 SELECT 'X'
3884 FROM igs_ps_tch_resp_ovrd
3885 WHERE unit_cd = cp_unit_cd
3886 AND version_number = cp_version_number
3887 AND cal_type = cp_cal_type
3888 AND ci_sequence_number = cp_ci_sequence_number
3889 AND location_cd = cp_location_cd
3890 AND unit_class = cp_unit_class
3891 AND org_unit_cd = cp_org_unit_cd
3892 AND ou_start_dt = cp_ou_start_dt
3893 AND ROWNUM = 1;
3894 l_cur_usec_tro_new cur_usec_tro_new%ROWTYPE;
3895
3896
3897 CURSOR c_ou( cp_org_unit_cd igs_or_unit.org_unit_cd%TYPE,
3898 cp_start_dt igs_or_unit.start_dt%TYPE) IS
3899 SELECT 'x'
3900 FROM igs_or_inst_org_base_v ou,
3901 igs_or_status os
3902 WHERE ou.party_number = cp_org_unit_cd
3903 AND ou.start_dt = cp_start_dt
3904 AND os.org_status = ou.org_status
3905 AND os.s_org_status = cst_active;
3906
3907 l_org_id NUMBER(15);
3908
3909 BEGIN
3910
3911 OPEN cur_usec_tro_new(gv_new_usec_rec.unit_cd,gv_new_usec_rec.version_number,gv_new_usec_rec.cal_type,
3912 gv_new_usec_rec.ci_sequence_number,gv_new_usec_rec.location_cd,gv_new_usec_rec.unit_class,
3913 usec_tro_rec.org_unit_cd, usec_tro_rec.ou_start_dt );
3914 FETCH cur_usec_tro_new INTO l_cur_usec_tro_new;
3915 IF cur_usec_tro_new%NOTFOUND THEN
3916
3917 -- Validate the status of the org IGS_PS_UNIT.
3918 -- If the org IGS_PS_UNIT code is not 'ACTIVE' then do not perform the insert else perform the insert
3919
3920 OPEN c_ou( usec_tro_rec.org_unit_cd,
3921 usec_tro_rec.ou_start_dt);
3922 FETCH c_ou INTO l_ou_exists;
3923 IF c_ou%FOUND THEN
3924 l_org_id := igs_ge_gen_003.get_org_id;
3925
3926 igs_ps_tch_resp_ovrd_pkg.insert_row(
3927 x_rowid => l_rowid,
3928 x_unit_cd => gv_new_usec_rec.unit_cd,
3929 x_version_number => gv_new_usec_rec.version_number,
3930 x_cal_type => gv_new_usec_rec.cal_type,
3931 x_ci_sequence_number => gv_new_usec_rec.ci_sequence_number,
3932 x_location_cd => gv_new_usec_rec.location_cd,
3933 x_unit_class => gv_new_usec_rec.unit_class,
3934 x_ou_start_dt => usec_tro_rec.ou_start_dt,
3935 x_org_unit_cd => usec_tro_rec.org_unit_cd,
3936 x_uoo_id => p_new_uoo_id,
3937 x_percentage => usec_tro_rec.percentage,
3938 x_mode => 'R',
3939 x_org_id => l_org_id);
3940 END IF;
3941 CLOSE c_ou;
3942 END IF;
3943 CLOSE cur_usec_tro_new;
3944
3945 EXCEPTION
3946 WHEN OTHERS THEN
3947 --Fnd log implementation
3948 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3949 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
3950 SUBSTRB('Org Unit Cd:'||usec_tro_rec.org_unit_cd||' '||'Unit Section:'||p_old_uoo_id||' '||
3951 NVL(fnd_message.get,SQLERRM),1,4000));
3952 END IF;
3953 END;
3954 END LOOP;
3955
3956
3957 --Create a pl-sql table which will hold the ftci calendar instances for the new teaching calendar instances.
3958 l_n_counter :=1;
3959 FOR cur_ftci_rec IN cur_ftci(gv_new_usec_rec.cal_type,gv_new_usec_rec.ci_sequence_number) LOOP
3960 teachCalendar_tbl(l_n_counter).cal_type :=cur_ftci_rec.cal_type;
3961 teachCalendar_tbl(l_n_counter).sequence_number :=cur_ftci_rec.sequence_number;
3962 l_n_counter:=l_n_counter+1;
3963 END LOOP;
3964
3965 -- Rollover of unit section special fees records
3966 IF teachCalendar_tbl.EXISTS(1) THEN
3967 FOR usec_spl_fees_rec IN c_usec_spl_fees (p_old_uoo_id ) LOOP
3968 DECLARE
3969 CURSOR cur_usec_spl_fees_new(cp_n_uoo_id igs_ps_usec_sp_fees.uoo_id%TYPE,
3970 cp_c_fee_type igs_ps_usec_sp_fees.fee_type%TYPE) IS
3971 SELECT 'x'
3972 FROM igs_ps_usec_sp_fees
3973 WHERE uoo_id = cp_n_uoo_id
3974 AND fee_type = cp_c_fee_type
3975 AND ROWNUM = 1;
3976 l_cur_usec_spl_fees_new cur_usec_spl_fees_new%ROWTYPE;
3977
3978 CURSOR c_fee_type_exists(cp_source_fee_type igs_fi_fee_type.fee_type%TYPE) IS
3979 SELECT ci.cal_type,ci.sequence_number
3980 FROM igs_fi_fee_type ft,
3981 igs_fi_f_typ_ca_inst ftci,
3982 igs_ca_inst ci,
3983 igs_ca_type ct,
3984 igs_ca_stat cs
3985 WHERE ft.s_fee_type = 'SPECIAL'
3986 AND ft.closed_ind = 'N'
3987 AND ft.fee_type = ftci.fee_type
3988 AND ft.fee_type = cp_source_fee_type
3989 AND ftci.fee_cal_type = ci.cal_type
3990 AND ftci.fee_ci_sequence_number = ci.sequence_number
3991 AND ci.cal_type = ct.cal_type
3992 AND ct.s_cal_cat = 'FEE'
3993 AND ci.cal_status = cs.cal_status
3994 AND cs.s_cal_status = 'ACTIVE' ;
3995
3996 lv_rowid VARCHAR2(25);
3997 l_usec_sp_fees_id NUMBER;
3998 l_c_var VARCHAR2(1);
3999 BEGIN
4000
4001 OPEN cur_usec_spl_fees_new(p_new_uoo_id,usec_spl_fees_rec.fee_type);
4002 FETCH cur_usec_spl_fees_new INTO l_cur_usec_spl_fees_new;
4003 IF cur_usec_spl_fees_new%NOTFOUND THEN
4004 l_c_proceed:= FALSE;
4005 FOR c_fee_type_exists_rec IN c_fee_type_exists(usec_spl_fees_rec.fee_type) LOOP
4006
4007 FOR i IN 1..teachCalendar_tbl.last LOOP
4008 IF c_fee_type_exists_rec.cal_type=teachCalendar_tbl(i).cal_type AND
4009 c_fee_type_exists_rec.sequence_number=teachCalendar_tbl(i).sequence_number THEN
4010 l_c_proceed:= TRUE;
4011 EXIT;
4012 END IF;
4013 END LOOP;
4014 IF l_c_proceed THEN
4015 EXIT;
4016 END IF;
4017
4018 END LOOP;
4019
4020 IF l_c_proceed THEN
4021 igs_ps_usec_sp_fees_pkg.insert_row (
4022 x_rowid => lv_rowid,
4023 x_usec_sp_fees_id => l_usec_sp_fees_id,
4024 x_uoo_id => p_new_uoo_id,
4025 x_fee_type => usec_spl_fees_rec.fee_type,
4026 x_sp_fee_amt => usec_spl_fees_rec.sp_fee_amt,
4027 x_closed_flag => usec_spl_fees_rec.closed_flag,
4028 x_mode => 'R' );
4029 END IF;
4030 END IF;
4031 CLOSE cur_usec_spl_fees_new;
4032
4033 EXCEPTION
4034 WHEN OTHERS THEN
4035 --Fnd log implementation
4036 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
4037 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
4038 SUBSTRB('Fee Type-Special Fees:'||usec_spl_fees_rec.fee_type||' '||'Unit Section:'||p_old_uoo_id||' '||
4039 NVL(fnd_message.get,SQLERRM),1,4000));
4040 END IF;
4041 END;
4042 END LOOP;
4043
4044 END IF;
4045
4046 IF gv_new_usec_rec.non_std_usec_ind = 'Y' THEN
4047 --Rollover of unit section retention
4048 FOR c_rtn_us_rec IN c_rtn_us LOOP
4049 DECLARE
4050 CURSOR c_rtn_us_new(cp_uoo_id igs_ps_nsus_rtn.uoo_id%TYPE,
4051 cp_fee_type igs_ps_nsus_rtn.fee_type%TYPE) IS
4052 SELECT non_std_usec_rtn_id
4053 FROM igs_ps_nsus_rtn
4054 WHERE uoo_id = cp_uoo_id
4055 AND fee_type = cp_fee_type;
4056
4057 CURSOR c_rtn_us_new_1(cp_uoo_id igs_ps_nsus_rtn.uoo_id%TYPE) IS
4058 SELECT non_std_usec_rtn_id
4059 FROM igs_ps_nsus_rtn
4060 WHERE uoo_id = cp_uoo_id
4061 AND definition_code ='UNIT_SECTION';
4062
4063
4064 lv_rowid VARCHAR2(25);
4065 l_non_std_usec_rtn_id igs_ps_nsus_rtn.non_std_usec_rtn_id%TYPE;
4066 l_b_fee_exists BOOLEAN ;
4067 l_c_var VARCHAR2(1);
4068 BEGIN
4069 l_b_fee_exists := TRUE;
4070 IF c_rtn_us_rec.fee_type IS NOT NULL THEN
4071
4072 l_c_proceed:= FALSE;
4073 FOR c_fee_type_cal_exists_rec IN c_fee_type_cal_exists(c_rtn_us_rec.fee_type) LOOP
4074
4075 FOR i IN 1..teachCalendar_tbl.last LOOP
4076 IF c_fee_type_cal_exists_rec.cal_type=teachCalendar_tbl(i).cal_type AND
4077 c_fee_type_cal_exists_rec.sequence_number=teachCalendar_tbl(i).sequence_number THEN
4078 l_c_proceed:= TRUE;
4079 EXIT;
4080 END IF;
4081 END LOOP;
4082 IF l_c_proceed THEN
4083 EXIT;
4084 END IF;
4085
4086 END LOOP;
4087
4088 IF l_c_proceed = FALSE THEN
4089 l_b_fee_exists := FALSE;
4090 END IF;
4091
4092 END IF;
4093
4094 IF l_b_fee_exists THEN
4095
4096 IF c_rtn_us_rec.definition_code = 'UNIT_SECTION_FEE_TYPE' THEN
4097 OPEN c_rtn_us_new(p_new_uoo_id,c_rtn_us_rec.fee_type);
4098 FETCH c_rtn_us_new INTO l_non_std_usec_rtn_id;
4099 IF c_rtn_us_new%NOTFOUND THEN
4100 l_non_std_usec_rtn_id := NULL;
4101 END IF;
4102 CLOSE c_rtn_us_new;
4103 ELSIF c_rtn_us_rec.definition_code = 'UNIT_SECTION' THEN
4104 OPEN c_rtn_us_new_1(p_new_uoo_id);
4105 FETCH c_rtn_us_new_1 INTO l_non_std_usec_rtn_id;
4106 IF c_rtn_us_new_1%NOTFOUND THEN
4107 l_non_std_usec_rtn_id := NULL;
4108 END IF;
4109 CLOSE c_rtn_us_new_1;
4110 END IF;
4111
4112 IF l_non_std_usec_rtn_id IS NULL THEN
4113
4114 igs_ps_nsus_rtn_pkg.insert_row(
4115 x_rowid => lv_rowid,
4116 x_non_std_usec_rtn_id => l_non_std_usec_rtn_id,
4117 x_uoo_id => p_new_uoo_id,
4118 x_fee_type => c_rtn_us_rec.fee_type,
4119 x_definition_code => c_rtn_us_rec.definition_code,
4120 x_formula_method => c_rtn_us_rec.formula_method,
4121 x_round_method => c_rtn_us_rec.round_method,
4122 x_incl_wkend_duration_flag => c_rtn_us_rec.incl_wkend_duration_flag,
4123 x_mode => 'R'
4124 );
4125 END IF;
4126
4127 FOR c_rtn_us_dtl_rec IN c_rtn_us_dtl (c_rtn_us_rec.non_std_usec_rtn_id) LOOP
4128 DECLARE
4129 CURSOR c_rtn_us_dtl_new(cp_non_std_usec_rtn_id igs_ps_nsus_rtn_dtl.non_std_usec_rtn_id%TYPE,
4130 cp_offset_value igs_ps_nsus_rtn_dtl.offset_value%TYPE) IS
4131 SELECT 'X'
4132 FROM igs_ps_nsus_rtn_dtl
4133 WHERE non_std_usec_rtn_id = cp_non_std_usec_rtn_id
4134 AND offset_value = cp_offset_value;
4135
4136 l_c_var VARCHAR2(1);
4137 lv_rowid VARCHAR2(25);
4138 l_non_std_usec_rtn_dtl_id igs_ps_nsus_rtn_dtl.non_std_usec_rtn_dtl_id%TYPE;
4139 l_offset_date DATE;
4140 BEGIN
4141 OPEN c_rtn_us_new(l_non_std_usec_rtn_id,c_rtn_us_dtl_rec.offset_value);
4142 FETCH c_rtn_us_new INTO l_c_var;
4143 IF c_rtn_us_new%NOTFOUND THEN
4144
4145 l_offset_date := igs_ps_gen_004.f_retention_offset_date(
4146 p_n_uoo_id => p_new_uoo_id,
4147 p_c_formula_method => c_rtn_us_rec.formula_method,
4148 p_c_round_method => c_rtn_us_rec.round_method,
4149 p_c_incl_wkend_duration => c_rtn_us_rec.incl_wkend_duration_flag,
4150 p_n_offset_value => c_rtn_us_dtl_rec.offset_value);
4151
4152 igs_ps_nsus_rtn_dtl_pkg.insert_row(
4153 x_rowid => lv_rowid,
4154 x_non_std_usec_rtn_dtl_id => l_non_std_usec_rtn_dtl_id,
4155 x_non_std_usec_rtn_id => l_non_std_usec_rtn_id,
4156 x_offset_value => c_rtn_us_dtl_rec.offset_value,
4157 x_retention_percent => c_rtn_us_dtl_rec.retention_percent,
4158 x_retention_amount => c_rtn_us_dtl_rec.retention_amount,
4159 x_offset_date => l_offset_date,
4160 x_override_date_flag => 'N', -- calculating the dates while rolling over. So if in the source if it is overriden here it is calculated, to keep the data in the correct calendar.
4161 x_mode => 'R'
4162 );
4163 END IF;
4164 CLOSE c_rtn_us_new;
4165
4166 EXCEPTION
4167 WHEN OTHERS THEN
4168 --Fnd log implementation
4169 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
4170 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
4171 SUBSTRB('Non Std Usec Rtn Dtl Id:'||c_rtn_us_dtl_rec.non_std_usec_rtn_dtl_id||' '||
4172 NVL(fnd_message.get,SQLERRM),1,4000));
4173 END IF;
4174 END;
4175 END LOOP;
4176 END IF;
4177
4178 EXCEPTION
4179 WHEN OTHERS THEN
4180 --Fnd log implementation
4181 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
4182 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_001.crsp_ins_unit_section.in_exception_section_OTHERS.err_msg',
4183 SUBSTRB('Non Std Usec Rtn Id:'||c_rtn_us_rec.non_std_usec_rtn_id||' '||
4184 NVL(fnd_message.get,SQLERRM),1,4000));
4185 END IF;
4186 END;
4187 END LOOP;
4188 END IF;
4189
4190 IF teachCalendar_tbl.EXISTS(1) THEN
4191 teachCalendar_tbl.DELETE;
4192 END IF;
4193
4194 --SUCCESSFUL condition.
4195 p_message_name := 'IGS_PS_USEC_DUP_TRN_SUCC';
4196
4197 END crsp_ins_unit_section;
4198
4199
4200 PROCEDURE change_unit_section_status(p_c_old_cal_status IN VARCHAR2,
4201 p_c_new_cal_status IN VARCHAR2,
4202 p_c_cal_type IN VARCHAR2,
4203 p_n_ci_sequence_number IN NUMBER,
4204 p_b_ret_status OUT NOCOPY BOOLEAN,
4205 p_c_message_name OUT NOCOPY VARCHAR2) IS
4206 /*
4207 || Created By : sarakshi
4208 || Created On : 24-Apr-2003
4209 || Purpose:Updates unit section status to 'Not Offered' if the calendar status is changes to INACTIVE
4210 || for those unit section of the calendar instance which are not having any enrollment
4211 || activity,also changes the status to OPEN when calendar status is updated from INACTIVE
4212 || to ACTIVE.This procedure returns status(p_b_ret_status), if this is false then p_c_message
4213 || _name will contain the error message name which needs to be shown to the calling env and
4214 || process needs to be stopped.
4215 || Known limitations, enhancements or remarks :
4216 || Change History :
4217 || Who When What
4218 || (reverse chronological order - newest change first)
4219 || sarakshi 21-oct-2003 Enh#3052452,used function igs_ps_gen_003.enrollment_for_uoo_check for checking the
4220 || existance of record in igs_en_su_attempt rather than explicitly coding a cursor locally.
4221 || sarakshi 18-sep-2003 Enh#3052452, added new parameters relation_type,sup_uoo_id,default_enroll_flag to the
4222 || call of igs_ps_unit_ofr_opt_pkg.update_row .
4223 || vvutukur 04-Aug-2003 Enh#3045069.PSP Enh Build.Modified the calls to igs_ps_unit_ofr_opt_pkg.update_row to add
4224 || new parameter not_multiple_section_flag.
4225 */
4226
4227 CURSOR c_uoo_id IS
4228 SELECT usec.* ,usec.rowid
4229 FROM igs_ps_unit_ofr_opt_all usec
4230 WHERE cal_type=p_c_cal_type
4231 AND ci_sequence_number=p_n_ci_sequence_number;
4232
4233 l_b_found BOOLEAN := FALSE;
4234 BEGIN
4235 p_c_message_name:=NULL;
4236 p_b_ret_status:=TRUE;
4237
4238 --If old calendar status and new status is same then do nothing
4239 IF p_c_old_cal_status = p_c_new_cal_status THEN
4240 p_b_ret_status:=TRUE;
4241 RETURN;
4242 END IF;
4243
4244 --If the new calendar status is INACTIVE then check if any enrollment activity exist for the
4245 --unit sections for the calendar instance, if exists then return false with error message name else
4246 --return true by updating the unit section statuses of all the unit section under this calendar
4247 --with value 'NOT_OFFERED'
4248
4249 IF p_c_new_cal_status = 'INACTIVE' THEN
4250
4251 FOR l_uoo_id IN c_uoo_id LOOP
4252
4253 IF igs_ps_gen_003.enrollment_for_uoo_check(l_uoo_id.uoo_id) THEN
4254 l_b_found:=TRUE;
4255 EXIT;
4256 END IF;
4257
4258 END LOOP;
4259
4260 IF l_b_found THEN
4261 p_b_ret_status:=FALSE;
4262 p_c_message_name:='IGS_PS_STATUS_NOT_UPD_ALLOWED';
4263 RETURN;
4264 ELSE
4265 FOR l_uoo_id IN c_uoo_id LOOP
4266 igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_uoo_id.rowid,
4267 x_unit_cd =>l_uoo_id.unit_cd,
4268 x_version_number =>l_uoo_id.version_number,
4269 x_cal_type =>l_uoo_id.cal_type,
4270 x_ci_sequence_number =>l_uoo_id.ci_sequence_number,
4271 x_location_cd =>l_uoo_id.location_cd,
4272 x_unit_class =>l_uoo_id.unit_class,
4273 x_uoo_id =>l_uoo_id.uoo_id,
4274 x_ivrs_available_ind =>l_uoo_id.ivrs_available_ind,
4275 x_call_number =>l_uoo_id.call_number,
4276 x_unit_section_status => 'NOT_OFFERED',
4277 x_unit_section_start_date =>l_uoo_id.unit_section_start_date,
4278 x_unit_section_end_date =>l_uoo_id.unit_section_end_date,
4279 x_enrollment_actual =>l_uoo_id.enrollment_actual,
4280 x_waitlist_actual =>l_uoo_id.waitlist_actual,
4281 x_offered_ind =>l_uoo_id.offered_ind,
4282 x_state_financial_aid =>l_uoo_id.state_financial_aid,
4283 x_grading_schema_prcdnce_ind =>l_uoo_id.grading_schema_prcdnce_ind,
4284 x_federal_financial_aid =>l_uoo_id.federal_financial_aid,
4285 x_unit_quota =>l_uoo_id.unit_quota,
4286 x_unit_quota_reserved_places =>l_uoo_id.unit_quota_reserved_places,
4287 x_institutional_financial_aid =>l_uoo_id.institutional_financial_aid,
4288 x_unit_contact =>l_uoo_id.unit_contact,
4289 x_grading_schema_cd =>l_uoo_id.grading_schema_cd,
4290 x_gs_version_number =>l_uoo_id.gs_version_number,
4291 x_owner_org_unit_cd =>l_uoo_id.owner_org_unit_cd,
4292 x_attendance_required_ind =>l_uoo_id.attendance_required_ind,
4293 x_reserved_seating_allowed =>l_uoo_id.reserved_seating_allowed,
4294 x_special_permission_ind =>l_uoo_id.special_permission_ind,
4295 x_ss_display_ind =>l_uoo_id.ss_display_ind,
4296 x_mode =>'R',
4297 x_ss_enrol_ind =>l_uoo_id.ss_enrol_ind,
4298 x_dir_enrollment =>l_uoo_id.dir_enrollment,
4299 x_enr_from_wlst =>l_uoo_id.enr_from_wlst,
4300 x_inq_not_wlst =>l_uoo_id.inq_not_wlst,
4301 x_rev_account_cd =>l_uoo_id.rev_account_cd,
4302 x_anon_unit_grading_ind =>l_uoo_id.anon_unit_grading_ind,
4303 x_anon_assess_grading_ind =>l_uoo_id.anon_assess_grading_ind,
4304 x_non_std_usec_ind =>l_uoo_id.non_std_usec_ind,
4305 x_auditable_ind =>l_uoo_id.auditable_ind,
4306 x_audit_permission_ind =>l_uoo_id.audit_permission_ind,
4307 x_not_multiple_section_flag =>l_uoo_id.not_multiple_section_flag,
4308 x_sup_uoo_id =>l_uoo_id.sup_uoo_id,
4309 x_relation_type =>l_uoo_id.relation_type,
4310 x_default_enroll_flag =>l_uoo_id.default_enroll_flag ,
4311 x_abort_flag =>l_uoo_id.abort_flag
4312 );
4313 END LOOP;
4314 p_b_ret_status:=TRUE;
4315 RETURN;
4316 END IF;
4317
4318 --If the new calendar status is ACTIVE and old status is INACTIVE then update the unit section status
4319 --to OPEN for all the unit section under the calendar instance
4320
4321 ELSIF p_c_new_cal_status = 'ACTIVE' AND p_c_old_cal_status ='INACTIVE' THEN
4322 FOR l_uoo_id IN c_uoo_id LOOP
4323 igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_uoo_id.rowid,
4324 x_unit_cd =>l_uoo_id.unit_cd,
4325 x_version_number =>l_uoo_id.version_number,
4326 x_cal_type =>l_uoo_id.cal_type,
4327 x_ci_sequence_number =>l_uoo_id.ci_sequence_number,
4328 x_location_cd =>l_uoo_id.location_cd,
4329 x_unit_class =>l_uoo_id.unit_class,
4330 x_uoo_id =>l_uoo_id.uoo_id,
4331 x_ivrs_available_ind =>l_uoo_id.ivrs_available_ind,
4332 x_call_number =>l_uoo_id.call_number,
4333 x_unit_section_status => 'OPEN',
4334 x_unit_section_start_date =>l_uoo_id.unit_section_start_date,
4335 x_unit_section_end_date =>l_uoo_id.unit_section_end_date,
4336 x_enrollment_actual =>l_uoo_id.enrollment_actual,
4337 x_waitlist_actual =>l_uoo_id.waitlist_actual,
4338 x_offered_ind =>l_uoo_id.offered_ind,
4339 x_state_financial_aid =>l_uoo_id.state_financial_aid,
4340 x_grading_schema_prcdnce_ind =>l_uoo_id.grading_schema_prcdnce_ind,
4341 x_federal_financial_aid =>l_uoo_id.federal_financial_aid,
4342 x_unit_quota =>l_uoo_id.unit_quota,
4343 x_unit_quota_reserved_places =>l_uoo_id.unit_quota_reserved_places,
4344 x_institutional_financial_aid =>l_uoo_id.institutional_financial_aid,
4345 x_unit_contact =>l_uoo_id.unit_contact,
4346 x_grading_schema_cd =>l_uoo_id.grading_schema_cd,
4347 x_gs_version_number =>l_uoo_id.gs_version_number,
4348 x_owner_org_unit_cd =>l_uoo_id.owner_org_unit_cd,
4349 x_attendance_required_ind =>l_uoo_id.attendance_required_ind,
4350 x_reserved_seating_allowed =>l_uoo_id.reserved_seating_allowed,
4351 x_special_permission_ind =>l_uoo_id.special_permission_ind,
4352 x_ss_display_ind =>l_uoo_id.ss_display_ind,
4353 x_mode =>'R',
4354 x_ss_enrol_ind =>l_uoo_id.ss_enrol_ind,
4355 x_dir_enrollment =>l_uoo_id.dir_enrollment,
4356 x_enr_from_wlst =>l_uoo_id.enr_from_wlst,
4357 x_inq_not_wlst =>l_uoo_id.inq_not_wlst,
4358 x_rev_account_cd =>l_uoo_id.rev_account_cd,
4359 x_anon_unit_grading_ind =>l_uoo_id.anon_unit_grading_ind,
4360 x_anon_assess_grading_ind =>l_uoo_id.anon_assess_grading_ind,
4361 x_non_std_usec_ind =>l_uoo_id.non_std_usec_ind,
4362 x_auditable_ind =>l_uoo_id.auditable_ind,
4363 x_audit_permission_ind =>l_uoo_id.audit_permission_ind,
4364 x_not_multiple_section_flag =>l_uoo_id.not_multiple_section_flag,
4365 x_sup_uoo_id =>l_uoo_id.sup_uoo_id,
4366 x_relation_type =>l_uoo_id.relation_type,
4367 x_default_enroll_flag =>l_uoo_id.default_enroll_flag,
4368 x_abort_flag =>l_uoo_id.abort_flag
4369 );
4370 END LOOP;
4371 p_b_ret_status:=TRUE;
4372 RETURN;
4373
4374 END IF;
4375
4376 END change_unit_section_status;
4377
4378 FUNCTION fac_exceed_exp_wl(
4379 p_c_cal_type IN VARCHAR2,
4380 p_n_cal_seq_num IN NUMBER,
4381 p_n_person_id IN NUMBER,
4382 p_n_curr_wl IN NUMBER,
4383 p_n_tot_fac_wl OUT NOCOPY NUMBER,
4384 p_n_exp_wl OUT NOCOPY NUMBER) RETURN BOOLEAN
4385 ------------------------------------------------------------------------------------
4386 --Created by : jdeekoll ( Oracle IDC)
4387 --Date created: 06-May-2003
4388 --
4389 --Purpose: HR Integration build(# 2833853) - This function will be called when the passed calendar type is Load/Academic
4390 --
4391 --Known limitations/enhancements and/or remarks:
4392 --
4393 --Change History:
4394 --Who When What
4395 --sommukhe 09-Aug-2005 Bug#4318183, modified type for variable l_n_fac_assign_wl
4396 -------------------------------------------------------------------------------------
4397 AS
4398
4399 /* Cursor to get the Calendar Category */
4400 CURSOR c_cal_cat IS
4401 SELECT calendar_cat
4402 FROM igs_ps_exp_wl;
4403
4404 l_c_cal_cat_code igs_ps_exp_wl.calendar_cat%TYPE;
4405
4406 l_c_emp_cat_code igs_ps_emp_cats_wl.emp_cat_code%TYPE;
4407
4408 /* Cursor to get the expected workload for the respective employment category */
4409 CURSOR c_exp_wl(cp_c_emp_cat_code igs_ps_emp_cats_wl.emp_cat_code%TYPE,cp_n_cal_cat_code igs_ps_exp_wl.calendar_cat%TYPE) IS
4410 SELECT ecw.expected_wl_num
4411 FROM igs_ps_emp_cats_wl ecw
4412 WHERE ecw.emp_cat_code = cp_c_emp_cat_code AND
4413 cal_cat_code = cp_n_cal_cat_code ORDER BY ecw.last_update_date desc;
4414
4415 l_n_exp_wl igs_ps_emp_cats_wl.expected_wl_num%TYPE:=0;
4416
4417 /* Cursor to get the override expected workload */
4418 CURSOR c_fac_ovr_wl(cp_n_person_id hz_parties.party_id%TYPE,
4419 cp_c_cal_type igs_ca_inst.cal_type%TYPE,
4420 cp_n_cal_seq_num igs_ca_inst.sequence_number%TYPE
4421 ) IS
4422 SELECT NVL(fow.new_exp_wl,0)
4423 FROM igs_ps_fac_wl fw,igs_ps_fac_ovr_wl fow
4424 WHERE fw.person_id = cp_n_person_id AND
4425 fow.fac_wl_id = fw.fac_wl_id AND
4426 fw.cal_type = cp_c_cal_type AND
4427 fw.ci_sequence_number=cp_n_cal_seq_num AND
4428 SYSDATE BETWEEN fow.start_date AND NVL(fow.end_date,SYSDATE);
4429
4430 l_n_fac_ovr_wl igs_ps_fac_ovr_wl.new_exp_wl%TYPE:=0;
4431
4432 /* Cursor to get sum of workload from assigned workload */
4433 CURSOR c_fac_assign_wl(cp_n_person_id hz_parties.party_id%TYPE,
4434 cp_c_cal_type igs_ca_inst.cal_type%TYPE,
4435 cp_n_cal_seq_num igs_ca_inst.sequence_number%TYPE
4436 ) IS
4437 SELECT NVL(SUM(NVL(fat.default_wl,0)),0)
4438 FROM igs_ps_fac_wl fw,igs_ps_fac_asg_task fat
4439 WHERE fw.person_id = cp_n_person_id AND
4440 fat.fac_wl_id = fw.fac_wl_id AND
4441 fw.cal_type = cp_c_cal_type AND
4442 fw.ci_sequence_number=cp_n_cal_seq_num AND
4443 fat.confirmed_ind = 'Y';
4444
4445 l_n_fac_assign_wl NUMBER:=0;
4446
4447 /* Cursor to get sum of workload from Unit Section Teaching Responsibility */
4448 CURSOR c_tch_resp_wl(cp_n_person_id hz_parties.party_id%TYPE,
4449 cp_c_cal_type igs_ca_inst.cal_type%TYPE,
4450 cp_n_cal_seq_num igs_ca_inst.sequence_number%TYPE
4451 )IS
4452 SELECT NVL(utr.INSTRUCTIONAL_LOAD_LAB,0) +
4453 NVL(utr.INSTRUCTIONAL_LOAD_LECTURE,0) +
4454 NVL(utr.INSTRUCTIONAL_LOAD,0) teach_wl
4455 FROM igs_ps_usec_tch_resp utr,igs_ps_unit_ofr_opt_all opt
4456 WHERE utr.instructor_id = cp_n_person_id AND
4457 utr.uoo_id = opt.uoo_id AND
4458 opt.cal_type = cp_c_cal_type AND
4459 utr.confirmed_flag = 'Y' AND
4460 opt.ci_sequence_number = cp_n_cal_seq_num AND
4461 opt.unit_section_status NOT IN ('CANCELLED','NOT_OFFERED');
4462
4463 l_n_wl NUMBER:=0;
4464 l_n_tch_resp_wl NUMBER:=0;
4465 l_c_s_cal_cat igs_ca_type.s_cal_cat%TYPE;
4466 l_c_tch_cal_type igs_ca_inst.cal_type%TYPE;
4467 l_n_tch_cal_seq_num igs_ca_inst.sequence_number%TYPE;
4468
4469 TYPE ref_cur_get_teach_cal IS REF CURSOR;
4470 c_get_tch_cal ref_cur_get_teach_cal;
4471
4472
4473 BEGIN
4474 --Added this code as gscc warning was comming for file.sql.35
4475 l_c_emp_cat_code :=igs_pe_gen_002.get_active_emp_cat(p_n_person_id);
4476 l_c_s_cal_cat :='TEACHING';
4477
4478 /* Calendar category cursor*/
4479 OPEN c_cal_cat;
4480 FETCH c_cal_cat INTO l_c_cal_cat_code;
4481 CLOSE c_cal_cat;
4482
4483 /* employment category cursor*/
4484 OPEN c_exp_wl(l_c_emp_cat_code,l_c_cal_cat_code);
4485 FETCH c_exp_wl INTO l_n_exp_wl;
4486 CLOSE c_exp_wl;
4487
4488 /* Override expected workload cursor */
4489 OPEN c_fac_ovr_wl(p_n_person_id,p_c_cal_type,p_n_cal_seq_num);
4490 FETCH c_fac_ovr_wl INTO l_n_fac_ovr_wl;
4491 CLOSE c_fac_ovr_wl;
4492
4493 /* Assigned workload cursor*/
4494 OPEN c_fac_assign_wl(p_n_person_id,p_c_cal_type,p_n_cal_seq_num);
4495 FETCH c_fac_assign_wl INTO l_n_fac_assign_wl;
4496 CLOSE c_fac_assign_wl;
4497
4498 /* Load To Teaching calendar cursor */
4499
4500 IF l_c_cal_cat_code = 'LOAD' THEN
4501
4502 /* Cursor to get the teaching calendars for the respective load calendar */
4503
4504 OPEN c_get_tch_cal FOR 'SELECT teach_cal_type,teach_ci_sequence_number'||
4505 ' FROM igs_ca_load_to_teach_v WHERE load_cal_type = :1 AND'||
4506 ' load_ci_sequence_number = :2 ' USING p_c_cal_type,p_n_cal_seq_num;
4507
4508 ELSIF l_c_cal_cat_code = 'ACADEMIC' THEN
4509
4510 /* Cursor to get teaching calendars for respective Academic calendars */
4511
4512 OPEN c_get_tch_cal FOR 'SELECT cir.sub_cal_type,cir.sub_ci_sequence_number'||
4513 ' FROM igs_ca_inst_rel cir, igs_ca_type ct'||
4514 ' WHERE cir.sub_cal_type = ct.cal_type AND'||
4515 ' ct.s_cal_cat = :1 AND'||
4516 ' sup_cal_type = :2 AND'||
4517 ' sup_ci_sequence_number = :3 ' USING l_c_s_cal_cat,p_c_cal_type,p_n_cal_seq_num;
4518 END IF;
4519
4520 IF l_c_cal_cat_code IN ('LOAD','ACADEMIC') THEN
4521 LOOP
4522 FETCH c_get_tch_cal INTO l_c_tch_cal_type,l_n_tch_cal_seq_num;
4523 EXIT WHEN c_get_tch_cal%NOTFOUND;
4524 l_n_wl :=0;
4525
4526 /* Unit Section Teaching Responsibility workload cursor */
4527
4528 FOR c_tch_resp_wl_rec IN c_tch_resp_wl(p_n_person_id,l_c_tch_cal_type,l_n_tch_cal_seq_num)
4529 LOOP
4530 l_n_wl := l_n_wl + NVL(c_tch_resp_wl_rec.teach_wl,0);
4531 END LOOP;
4532
4533 l_n_tch_resp_wl := l_n_tch_resp_wl + l_n_wl;
4534
4535 END LOOP;
4536 /* Close the cursor */
4537 CLOSE c_get_tch_cal;
4538 END IF;
4539
4540 /* Total Faculty assigned workload, including the current workload passed (at present this is only from Unit Section Teaching Responsibility */
4541
4542 p_n_tot_fac_wl := NVL(l_n_fac_assign_wl,0) + NVL(l_n_tch_resp_wl,0)+NVL(p_n_curr_wl,0);
4543
4544 /* If override workload > 0 then, the total faculty assigned workload should be compared with override expected workload, else expected workload */
4545
4546 IF NVL(l_n_fac_ovr_wl,0) > 0 THEN
4547 p_n_exp_wl := NVL(l_n_fac_ovr_wl,0);
4548 ELSE
4549 p_n_exp_wl := NVL(l_n_exp_wl,0);
4550 END IF;
4551
4552 IF NVL(p_n_tot_fac_wl,0) > p_n_exp_wl THEN
4553 RETURN TRUE;
4554 ELSE
4555 RETURN FALSE;
4556 END IF;
4557
4558 END fac_exceed_exp_wl;
4559
4560 FUNCTION teach_fac_wl(
4561 p_c_cal_type IN VARCHAR2,
4562 p_n_cal_seq_num IN NUMBER,
4563 p_n_person_id IN NUMBER,
4564 p_n_curr_wl IN NUMBER,
4565 p_n_tot_fac_wl OUT NOCOPY NUMBER,
4566 p_n_exp_wl OUT NOCOPY NUMBER) RETURN BOOLEAN
4567 ------------------------------------------------------------------------------------
4568 --Created by : jdeekoll ( Oracle IDC)
4569 --Date created: 11-May-2003
4570 --
4571 --Purpose: HR Integration build - This function will be called when the passed calendar type is teaching
4572 --
4573 --Known limitations/enhancements and/or remarks:
4574 --
4575 --Change History:
4576 --Who When What
4577 -------------------------------------------------------------------------------------
4578 AS
4579
4580 /* Cursor to get the Calendar Category */
4581 CURSOR c_cal_cat IS
4582 SELECT calendar_cat
4583 FROM igs_ps_exp_wl;
4584
4585 l_c_cal_cat_code igs_ps_exp_wl.calendar_cat%TYPE;
4586 l_c_s_cal_cat igs_ca_type.s_cal_cat%TYPE;
4587 l_c_cal_type igs_ca_inst.cal_type%TYPE;
4588 l_n_cal_seq_num igs_ca_inst.sequence_number%TYPE;
4589
4590
4591 TYPE ref_cur_get_cal IS REF CURSOR;
4592 c_get_cal ref_cur_get_cal;
4593
4594
4595 BEGIN
4596 --Added as it was giving file.sql.35 warning
4597 l_c_s_cal_cat :='ACADEMIC';
4598
4599 /* Calendar category cursor*/
4600 OPEN c_cal_cat;
4601 FETCH c_cal_cat INTO l_c_cal_cat_code;
4602 CLOSE c_cal_cat;
4603
4604 IF l_c_cal_cat_code = 'LOAD' THEN
4605
4606 /* Cursor for to get Load calendar when Teach cal is passed */
4607
4608 OPEN c_get_cal FOR 'SELECT load_cal_type,load_ci_sequence_number'||
4609 ' FROM igs_ca_teach_to_load_v WHERE teach_cal_type = :1 AND'||
4610 ' teach_ci_sequence_number = :2 ' USING p_c_cal_type,p_n_cal_seq_num;
4611
4612 ELSIF l_c_cal_cat_code = 'ACADEMIC' THEN
4613
4614 /* Cursor for to get Academic calendar when Teach cal is passed */
4615
4616 OPEN c_get_cal FOR 'SELECT cir.sup_cal_type,cir.sup_ci_sequence_number'||
4617 ' FROM igs_ca_inst_rel cir, igs_ca_type ct'||
4618 ' WHERE cir.sup_cal_type = ct.cal_type AND'||
4619 ' ct.s_cal_cat = :1 AND'||
4620 ' sub_cal_type = :2 AND'||
4621 ' sub_ci_sequence_number = :3 ' USING l_c_s_cal_cat,p_c_cal_type,p_n_cal_seq_num;
4622 END IF;
4623
4624 IF l_c_cal_cat_code IN ('LOAD','ACADEMIC') THEN
4625 LOOP
4626
4627 FETCH c_get_cal INTO l_c_cal_type,l_n_cal_seq_num;
4628 EXIT WHEN c_get_cal%NOTFOUND;
4629
4630 /* If any one of the worklaod is exceeded with in particular Load calendar, then exist, else continue */
4631
4632 IF igs_ps_gen_001.fac_exceed_exp_wl(
4633 l_c_cal_type,
4634 l_n_cal_seq_num,
4635 p_n_person_id,
4636 p_n_curr_wl,
4637 p_n_tot_fac_wl,
4638 p_n_exp_wl
4639 ) THEN
4640 RETURN TRUE;
4641 END IF;
4642 END LOOP;
4643 /* Close the cursor */
4644 CLOSE c_get_cal;
4645 END IF;
4646
4647 /* It will come to this step, only if the staff has not exceeded in any of the load calendars */
4648
4649 RETURN FALSE;
4650
4651 END teach_fac_wl;
4652
4653 END IGS_PS_GEN_001;