1 PACKAGE BODY igs_ps_gen_008 AS
2 /* $Header: IGSPS08B.pls 120.7 2006/01/31 02:33:55 sommukhe ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 -- Who When What
6 --ijeddy Dec 3, 2003 Grade Book Enh build, bug no 3201661
7 -- ijeddy 03-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment TD.
8 --sarakshi 09-sep-2003 Enh#3052452,removed the local function crspl_ins_sub_unit_rel and its call
9 --vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified crspl_ins_unit_off_opt,CRSP_INS_UOP_UOO.
10 --vvutukur 24-May-2003 Enh#2831572.Financial Accounting Build. Modified procedure crsp_ins_unit_ver.
11 --
12 -- nalkumar 19-May-2003 Bug# 2829291; Modified the call of IGS_AS_UNITASS_ITEM_PKG.INSERT_ROW;
13 -- Modifications are as per 'Assessment Item Description' FD;
14 --
15 -- sarakshi 18-Apr-2003 Bug#2910695,modified procedure crspl_ins_unit_off_opt and CRSP_INS_UOP_UOO
16 -- sarakshi 05-Mar-2003 Bug#2768783, modified procedure crsp_ins_uop_uoo and crspl_ins_unit_off_opt,
17 -- removed local function check_call_number
18 -- shtatiko 06-NOV-2002 Added auditable_ind and audit_permission_ind parameters to
19 -- insert_row calls of igs_ps_unit_ofr_opt_pkg as part of
20 -- bug# 2636716, EN Integration.
21 -- jbegum 11-Sep-2002 As part of bug#2563596
22 -- 1) The logic of FUNCTION CRSP_INS_UOP_UOO has been modified.
23 -- 2) The PROCEDURE handle_excp has been modified.
24 -- 3) The exception handling of FUNCTION CRSP_INS_UOP_UOO has been modified
25 -- sarakshi 5-Jun-2002 bug#2332807, changes are mentioned in detail in the code, procedure crsp_ins_uop_uoo.
26 -- prraj 14-Feb-2002 Added column NON_STD_USEC_IND to tbh calls for
27 -- pkg IGS_PS_UNIT_OFR_OPT_PKG (Bug# 2224366)
28 -- ayedubat 30-JAN-2002 Changed the crsp_ins_unit_ver procedure to add the HESA functionality
29 -- as per the HESA Integration DLD ( Bug # 2201753)
30 -- ddey 01-FEB-2002 Added columns anon_unit_grading_ind and anon_assess_grading_ind in the calls
31 -- for the package IGS_PS_UNIT_OFR_OPT_PKG
32 -- smadathi 28-AUG-2001 Bug No. 1956374 .The call to igs_ps_val_uoo.genp_val_staff_prsn
33 -- is changed to igs_ad_val_acai.genp_val_staff_prsn
34 -- nalkumar 02-Jan-2002 Modified the crspl_ins_unit_assmnt_item procedure as per the
35 -- Calculation of Results Part-1 DLD. Bug# 2162831
36 -- ayedubat 14-Jan-2003 Removed the cursor, cur_uv_obj_exist which checks for the HESA Package existence
37 -- Also removed the execute immediate and calling the package directly for bug, 3305858
38 -------------------------------------------------------------------------------------------
39
40 x_rowid VARCHAR2(25);
41
42 PROCEDURE crsp_ins_unit_set(
43 p_old_unit_set_cd IN VARCHAR2 ,
44 p_old_version_number IN NUMBER ,
45 p_new_unit_set_cd IN VARCHAR2 ,
46 p_new_version_number IN NUMBER ,
47 p_message_name OUT NOCOPY VARCHAR2 )
48 AS
49 cst_upper_limit_err NUMBER;
50 cst_lower_limit_err NUMBER;
51 gv_err_msg_proc_part1 VARCHAR2(255);
52 gv_err_msg_proc_part2 VARCHAR2(255);
53 gv_err_msg_proc1 VARCHAR2(60);
54 gv_err_msg_proc2 VARCHAR2(60);
55 gv_err_msg_proc3 VARCHAR2(60);
56 gv_err_msg_proc4 VARCHAR2(60);
57 gv_err_msg_proc5 VARCHAR2(60);
58 gv_err_msg_proc6 VARCHAR2(60);
59 gv_unit_version_exist VARCHAR2(1);
60
61
62 BEGIN -- crsp_ins_unit_set
63 -- This module is the procedure responsible for transferring all of
64 -- the details for a nominated IGS_PS_UNIT set over into another IGS_PS_UNIT set
65 -- The logic consists of getting the records from the appropriate
66 -- record types, which are children of the "old" IGS_EN_UNIT_SET and making
67 -- duplicates of them under the "new" IGS_EN_UNIT_SET. Prior to the routine
68 -- being called a new version of the IGS_EN_UNIT_SET record will have been created
69
70 -- Assigning initial values to local variables which were being initialised using DEFAULT
71 -- clause.Done as part of bug #2563596 to remove GSCC warning.
72 cst_upper_limit_err := -20000;
73 cst_lower_limit_err := -20999;
74
75 DECLARE
76 v_message_name varchar2(30);
77 --------------SUB-PROCEDURE 1---------------
78 FUNCTION crspl_ins_duplicate_note (
79 p_existing_ref_number IGS_GE_NOTE.reference_number%TYPE,
80 p_new_ref_number OUT NOCOPY IGS_GE_NOTE.reference_number%TYPE )
81 RETURN BOOLEAN
82 AS
83 BEGIN
84 DECLARE
85 CURSOR c_note_seq IS
86 SELECT IGS_GE_NOTE_RF_NUM_S.nextval
87 FROM dual;
88 CURSOR c_note IS
89 SELECT IGS_GE_NOTE.s_note_format_type,
90 IGS_GE_NOTE.note_text
91 FROM IGS_GE_NOTE IGS_GE_NOTE
92 WHERE IGS_GE_NOTE.reference_number = p_existing_ref_number;
93 v_note_rec c_note%ROWTYPE;
94 v_note_seq IGS_GE_NOTE.reference_number%TYPE;
95
96 BEGIN
97 -- get next val of reference_number of IGS_GE_NOTE
98 OPEN c_note_seq;
99 FETCH c_note_seq INTO v_note_seq;
100 CLOSE c_note_seq;
101 --- Get related IGS_GE_NOTE and insert under new reference number
102 OPEN c_note;
103 FETCH c_note INTO v_note_rec;
104 IF c_note%FOUND THEN
105 x_rowid := NULL;
106 IGS_GE_NOTE_PKG.Insert_Row(
107 X_ROWID => x_rowid,
108 X_REFERENCE_NUMBER => v_note_seq,
109 X_S_NOTE_FORMAT_TYPE => v_note_rec.s_note_format_type,
110 X_NOTE_TEXT => v_note_rec.note_text,
111 X_MODE => 'R');
112
113 p_new_ref_number := v_note_seq;
114 CLOSE c_note;
115 RETURN TRUE;
116 END IF;
117 CLOSE c_note;
118 RETURN FALSE;
119 EXCEPTION
120 WHEN OTHERS THEN
121 IF c_note%ISOPEN THEN
122 CLOSE c_note;
123 END IF;
124 IF c_note_seq%ISOPEN THEN
125 CLOSE c_note_seq;
126 END IF;
127 App_Exception.Raise_Exception;
128 END;
129 EXCEPTION
130 WHEN OTHERS THEN
131 IF SQLCODE >= cst_lower_limit_err AND
132 SQLCODE <= cst_upper_limit_err THEN
133 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
134 ELSE
135 App_Exception.Raise_Exception;
136 END IF;
137 END crspl_ins_duplicate_note;
138 --------------SUB-PROCEDURE 2--------------------
139 PROCEDURE crspl_unit_set_note
140 AS
141 BEGIN
142 DECLARE
143 v_new_reference_number IGS_GE_NOTE.reference_number%TYPE;
144 v_new_ref_number IGS_GE_NOTE.reference_number%TYPE;
145 v_new_ref_no IGS_GE_NOTE.reference_number%TYPE;
146 -- The following cursor excludes notes records with NULL
147 -- values in the note_text field as this implies that it
148 -- contains data in the note_ole field which cannot be
149 -- copied with the current product limitations.
150 -- i.e. must be copied manually
151 CURSOR c_usn IS
152 SELECT usn.reference_number,
153 usn.crs_note_type
154 FROM IGS_EN_UNIT_SET_NOTE usn
155 WHERE usn.unit_set_cd = p_old_unit_set_cd AND
156 usn.version_number = p_old_version_number AND
157 EXISTS (
158 SELECT 'x'
159 FROM IGS_GE_NOTE nte
160 WHERE nte.reference_number =
161 usn.reference_number AND
162 nte.note_text IS NOT NULL);
163
164 BEGIN
165 FOR v_usn_rec IN c_usn LOOP
166 IF crspl_ins_duplicate_note(
167 v_usn_rec.reference_number,
168 v_new_ref_number) = TRUE THEN
169 v_new_ref_no := v_new_ref_number;
170 END IF;
171 BEGIN
172 x_rowid := NULL;
173 IGS_EN_UNIT_SET_NOTE_PKG.Insert_Row(
174 X_ROWID => x_rowid,
175 X_UNIT_SET_CD => p_new_unit_set_cd,
176 X_VERSION_NUMBER => p_new_version_number,
177 X_REFERENCE_NUMBER => v_new_ref_no,
178 X_CRS_NOTE_TYPE => v_usn_rec.crs_note_type,
179 X_MODE => 'R');
180
181 EXCEPTION
182 WHEN OTHERS THEN
183 IF SQLCODE >= cst_lower_limit_err AND
184 SQLCODE <= cst_upper_limit_err THEN
185 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
186 ELSE
187 App_Exception.Raise_Exception;
188 END IF;
189 END;
190 END LOOP;
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF (c_usn%ISOPEN) THEN
194 CLOSE c_usn;
195 END IF;
196 App_Exception.Raise_Exception;
197 END;
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF SQLCODE >= cst_lower_limit_err AND
201 SQLCODE <= cst_upper_limit_err THEN
202 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
203 ELSE
204 App_Exception.Raise_Exception;
205 END IF;
206 END crspl_unit_set_note;
207 ------------SUB-PROCEDURE 3-----------------------
208 PROCEDURE crspl_unit_set_crs_type
209 AS
210 BEGIN
211 DECLARE
212 CURSOR c_usct IS
213 SELECT usct.course_type
214 FROM IGS_EN_UNITSETPSTYPE usct
215 WHERE usct.unit_set_cd = p_old_unit_set_cd AND
216 usct.version_number = p_old_version_number;
217 BEGIN
218 FOR v_usct_rec IN c_usct LOOP
219 BEGIN
220 x_rowid := NULL;
221 IGS_EN_UNITSETPSTYPE_PKG.Insert_Row(
222 X_ROWID => x_rowid,
223 X_UNIT_SET_CD => p_new_unit_set_cd,
224 X_VERSION_NUMBER => p_new_version_number,
225 X_COURSE_TYPE => v_usct_rec.course_type,
226 X_MODE => 'R');
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 IF SQLCODE >= cst_lower_limit_err AND
231 SQLCODE <= cst_upper_limit_err THEN
232 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
233 ELSE
234 App_Exception.Raise_Exception;
235 END IF;
236 END;
237 END LOOP;
238 EXCEPTION
239 WHEN OTHERS THEN
240 IF (c_usct%ISOPEN) THEN
241 CLOSE c_usct;
242 END IF;
243 App_Exception.Raise_Exception;
244 END;
245 EXCEPTION
246 WHEN OTHERS THEN
247 IF SQLCODE >= cst_lower_limit_err AND
248 SQLCODE <= cst_upper_limit_err THEN
249 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
250 ELSE
251 App_Exception.Raise_Exception;
252 END IF;
253 END crspl_unit_set_crs_type;
254 -----------------SUB-PROCEDURE 4-----------------
255 PROCEDURE crspl_crs_off_unit_set
256 AS
257 BEGIN
258 DECLARE
259 CURSOR c_cous IS
260 SELECT cous.course_cd,
261 cous.crv_version_number,
262 cous.cal_type,
263 cous.override_title,
264 cous.only_as_sub_ind,
265 cous.show_on_official_ntfctn_ind
266 FROM IGS_PS_OFR_UNIT_SET cous
267 WHERE cous.unit_set_cd = p_old_unit_set_cd AND
268 cous.us_version_number = p_old_version_number;
269 CURSOR c_cousr_sup (
270 cp_cous_course_cd IGS_PS_OFR_UNIT_SET.course_cd%TYPE,
271 cp_cous_cal_type IGS_PS_OFR_UNIT_SET.cal_type%TYPE,
272 cp_cous_crv_ver_num IGS_PS_OFR_UNIT_SET.crv_version_number%TYPE) IS
273 SELECT cousr.course_cd,
274 cousr.crv_version_number,
275 cousr.cal_type,
276 cousr.sub_unit_set_cd,
277 cousr.sub_us_version_number
278 FROM IGS_PS_OF_UNT_SET_RL cousr
279 WHERE cousr.sup_unit_set_cd = p_old_unit_set_cd AND
280 cousr.sup_us_version_number = p_old_version_number AND
281 cousr.course_cd = cp_cous_course_cd AND
282 cousr.cal_type = cp_cous_cal_type AND
283 cousr.crv_version_number = cp_cous_crv_ver_num;
284 CURSOR c_cousr_sub (
285 cp_cous_course_cd IGS_PS_OFR_UNIT_SET.course_cd%TYPE,
286 cp_cous_cal_type IGS_PS_OFR_UNIT_SET.cal_type%TYPE,
287 cp_cous_crv_ver_num IGS_PS_OFR_UNIT_SET.crv_version_number%TYPE) IS
288 SELECT cousr.course_cd,
289 cousr.crv_version_number,
290 cousr.cal_type,
291 cousr.sup_unit_set_cd,
292 cousr.sup_us_version_number
293 FROM IGS_PS_OF_UNT_SET_RL cousr
294 WHERE cousr.sub_unit_set_cd = p_old_unit_set_cd AND
295 cousr.sub_us_version_number = p_old_version_number AND
296 cousr.course_cd = cp_cous_course_cd AND
297 cousr.cal_type = cp_cous_cal_type AND
298 cousr.crv_version_number = cp_cous_crv_ver_num;
299 CURSOR c_coous (
300 cp_cous_course_cd IGS_PS_OFR_UNIT_SET.course_cd%TYPE,
301 cp_cous_version_number IGS_PS_OFR_UNIT_SET.crv_version_number%TYPE,
302 cp_cous_cal_type IGS_PS_OFR_UNIT_SET.cal_type%TYPE) IS
303 SELECT coous.course_cd,
304 coous.crv_version_number,
305 coous.cal_type,
306 coous.location_cd,
307 coous.attendance_mode,
308 coous.attendance_type,
309 coous.coo_id
310 FROM IGS_PS_OF_OPT_UNT_ST coous
311 WHERE coous.unit_set_cd = p_old_unit_set_cd AND
312 coous.us_version_number = p_old_version_number AND
313 coous.course_cd = cp_cous_course_cd AND
314 coous.crv_version_number = cp_cous_version_number AND
315 coous.cal_type = cp_cous_cal_type;
316
317 BEGIN
318 FOR v_cous_rec IN c_cous LOOP
319 BEGIN
320 x_rowid := NULL;
321 IGS_PS_OFR_UNIT_SET_PKG.Insert_Row(
322 X_ROWID => x_rowid,
323 X_COURSE_CD => v_cous_rec.course_cd,
324 X_CRV_VERSION_NUMBER => v_cous_rec.crv_version_number,
325 X_CAL_TYPE => v_cous_rec.cal_type,
326 X_UNIT_SET_CD => p_new_unit_set_cd,
327 X_US_VERSION_NUMBER => p_new_version_number,
328 X_OVERRIDE_TITLE => v_cous_rec.override_title,
329 X_ONLY_AS_SUB_IND => v_cous_rec.only_as_sub_ind,
330 X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_cous_rec.show_on_official_ntfctn_ind,
331 X_MODE => 'R');
332
333 FOR v_cousr_sub_rec IN c_cousr_sub (
334 v_cous_rec.course_cd,
335 v_cous_rec.cal_type,
336 v_cous_rec.crv_version_number) LOOP
337 x_rowid := NULL;
338 IGS_PS_OF_UNT_SET_RL_PKG.Insert_Row(
339 X_ROWID => x_rowid,
340 X_COURSE_CD => v_cousr_sub_rec.course_cd,
341 X_CRV_VERSION_NUMBER => v_cousr_sub_rec.crv_version_number,
342 X_SUP_US_VERSION_NUMBER => v_cousr_sub_rec.sup_us_version_number,
343 X_SUB_UNIT_SET_CD => p_new_unit_set_cd,
344 X_SUP_UNIT_SET_CD => v_cousr_sub_rec.sup_unit_set_cd,
345 X_CAL_TYPE => v_cousr_sub_rec.cal_type,
346 X_SUB_US_VERSION_NUMBER => p_new_version_number,
347 X_MODE => 'R');
348
349
350 END LOOP; -- cousr
351 FOR v_cousr_sup_rec IN c_cousr_sup (
352 v_cous_rec.course_cd,
353 v_cous_rec.cal_type,
354 v_cous_rec.crv_version_number) LOOP
355 x_rowid := NULL;
356 IGS_PS_OF_UNT_SET_RL_PKG.Insert_Row(
357 X_ROWID => x_rowid,
358 X_COURSE_CD => v_cousr_sup_rec.course_cd,
359 X_CRV_VERSION_NUMBER => v_cousr_sup_rec.crv_version_number,
360 X_SUP_US_VERSION_NUMBER => p_new_version_number,
361 X_SUB_UNIT_SET_CD => v_cousr_sup_rec.sub_unit_set_cd,
362 X_SUP_UNIT_SET_CD => p_new_unit_set_cd,
363 X_CAL_TYPE => v_cousr_sup_rec.cal_type,
364 X_SUB_US_VERSION_NUMBER => v_cousr_sup_rec.sub_us_version_number,
365 X_MODE => 'R');
366
367 END LOOP; -- cousr
368 FOR v_coous_rec IN c_coous (
369 v_cous_rec.course_cd,
370 v_cous_rec.crv_version_number,
371 v_cous_rec.cal_type) LOOP
372 x_rowid := NULL;
373 IGS_PS_OF_OPT_UNT_ST_PKG.Insert_Row(
374 X_ROWID => x_rowid,
375 X_COURSE_CD => v_coous_rec.course_cd,
376 X_LOCATION_CD => v_coous_rec.location_cd,
377 X_ATTENDANCE_MODE => v_coous_rec.attendance_mode,
378 X_CAL_TYPE => v_coous_rec.cal_type,
379 X_CRV_VERSION_NUMBER => v_coous_rec.crv_version_number,
380 X_ATTENDANCE_TYPE => v_coous_rec.attendance_type,
381 X_US_VERSION_NUMBER => p_new_version_number,
382 X_UNIT_SET_CD => p_new_unit_set_cd,
383 X_COO_ID => v_coous_rec.coo_id,
384 X_MODE => 'R');
385
386 END LOOP; -- coous
387 EXCEPTION
388 WHEN OTHERS THEN
389 IF SQLCODE >= cst_lower_limit_err AND
390 SQLCODE <= cst_upper_limit_err THEN
391 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
392 ELSE
393 App_Exception.Raise_Exception;
394 END IF;
395 END;
396 END LOOP; --cous
397 EXCEPTION
398
399 WHEN OTHERS THEN
400 IF (c_cous%ISOPEN) THEN
401 CLOSE c_cous;
402 END IF;
403 IF (c_cousr_sup%ISOPEN) THEN
404 CLOSE c_cousr_sup;
405 END IF;
406 IF (c_cousr_sub%ISOPEN) THEN
407 CLOSE c_cousr_sub;
408 END IF;
409 IF (c_coous%ISOPEN) THEN
410 CLOSE c_cousr_sup;
411 END IF;
412 App_Exception.Raise_Exception;
413 END;
414 EXCEPTION
415 WHEN OTHERS THEN
416 IF SQLCODE >= cst_lower_limit_err AND
417 SQLCODE <= cst_upper_limit_err THEN
418 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
419 ELSE
420 App_Exception.Raise_Exception;
421 END IF;
422 END crspl_crs_off_unit_set;
423 -----------------SUB-PROCEDURE 5-------------------------
424 PROCEDURE crspl_unit_set_rule
425 AS
426 BEGIN
427 DECLARE
428 v_new_rul_seq_number NUMBER;
429 CURSOR c_usr IS
430 SELECT usr.s_rule_call_cd,
431 usr.rul_sequence_number
432 FROM IGS_EN_UNIT_SET_RULE usr
433 WHERE usr.unit_set_cd = p_old_unit_set_cd AND
434 usr.version_number = p_old_version_number;
435 BEGIN
436 FOR v_usr_rec IN c_usr LOOP
437 BEGIN
438 v_new_rul_seq_number := IGS_RU_GEN_003.rulp_ins_copy_rule(
439 v_usr_rec.s_rule_call_cd,
440 v_usr_rec.rul_sequence_number);
441 x_rowid := NULL;
442 IGS_EN_UNIT_SET_RULE_PKG.Insert_Row(
443 X_ROWID => x_rowid,
444 X_UNIT_SET_CD => p_new_unit_set_cd,
445 X_VERSION_NUMBER => p_new_version_number,
446 X_S_RULE_CALL_CD => v_usr_rec.s_rule_call_cd,
447 X_RUL_SEQUENCE_NUMBER => v_new_rul_seq_number,
448 X_MODE => 'R');
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 IF SQLCODE >= cst_lower_limit_err AND
453 SQLCODE <= cst_upper_limit_err THEN
454 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
455 ELSE
456 App_Exception.Raise_Exception;
457 END IF;
458 END;
459 END LOOP;
460 EXCEPTION
461 WHEN OTHERS THEN
462 IF (c_usr%ISOPEN) THEN
463 CLOSE c_usr;
464 END IF;
465 App_Exception.Raise_Exception;
466 END;
467 EXCEPTION
468 WHEN OTHERS THEN
469 IF SQLCODE >= cst_lower_limit_err AND
470 SQLCODE <= cst_upper_limit_err THEN
471 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
472 ELSE
473 App_Exception.Raise_Exception;
474 END IF;
475 END crspl_unit_set_rule;
476 ----------------SUB-PROCEDURE 6---------------------
477 PROCEDURE crspl_coo_adm_cat_unit_set
478 AS
479 BEGIN
480 DECLARE
481 CURSOR c_cacus IS
482 SELECT cacus.course_cd,
483 cacus.crv_version_number,
484 cacus.cal_type,
485 cacus.location_cd,
486 cacus.attendance_mode,
487 cacus.attendance_type,
488 cacus.admission_cat
489 FROM IGS_PS_COO_AD_UNIT_S cacus
490 WHERE cacus.unit_set_cd = p_old_unit_set_cd AND
491 cacus.us_version_number = p_old_version_number;
492
493 BEGIN
494 FOR v_cacus_rec IN c_cacus LOOP
495 BEGIN
496 x_rowid := NULL;
497 IGS_PS_COO_AD_UNIT_S_PKG.Insert_Row(
498 X_ROWID => x_rowid,
499 X_COURSE_CD => v_cacus_rec.course_cd,
500 X_CRV_VERSION_NUMBER => v_cacus_rec.crv_version_number,
501 X_CAL_TYPE => v_cacus_rec.cal_type,
502 X_LOCATION_CD => v_cacus_rec.location_cd,
503 X_ATTENDANCE_MODE => v_cacus_rec.attendance_mode,
504 X_ATTENDANCE_TYPE => v_cacus_rec.attendance_type,
505 X_ADMISSION_CAT => v_cacus_rec.admission_cat,
506 X_UNIT_SET_CD => p_new_unit_set_cd,
507 X_US_VERSION_NUMBER => p_new_version_number,
508 X_MODE => 'R');
509
510 EXCEPTION
511 WHEN OTHERS THEN
512 IF SQLCODE >= cst_lower_limit_err AND
513 SQLCODE <= cst_upper_limit_err THEN
514 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
515 ELSE
516 App_Exception.Raise_Exception;
517 END IF;
518 END;
519 END LOOP;
520 EXCEPTION
521 WHEN OTHERS THEN
522 IF (c_cacus%ISOPEN) THEN
523 CLOSE c_cacus;
524 END IF;
525 App_Exception.Raise_Exception;
526 END;
527 EXCEPTION
528 WHEN OTHERS THEN
529 IF SQLCODE >= cst_lower_limit_err AND
530 SQLCODE <= cst_upper_limit_err THEN
531 p_message_name := 'IGS_PS_FAIL_COPY_UNIT_SET';
532 ELSE
533 App_Exception.Raise_Exception;
534 END IF;
535 END crspl_coo_adm_cat_unit_set;
536 ----------------- MAIN-------------------------
537 BEGIN
538 -- initialise msg_no to default indicating that insert was
539 -- successful
540 p_message_name := 'IGS_PS_SUCCESS_COPY_UNIT_SET';
541 -- 1. Validate new IGS_EN_UNIT_SET exists using
542 -- IGS_EN_VAL_PUSE.crsp_val_us_exists and
543 -- the 'new' parameters passed in.
544 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_US.crsp_val_us_exists
545 IF IGS_EN_VAL_PUSE.crsp_val_us_exists(
546 p_new_unit_set_cd,
547 p_new_version_number,
548 v_message_name) = FALSE THEN
549 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
550 RETURN;
551 END IF;
552 -- 2. Validate old IGS_EN_UNIT_SET exists using
553 -- IGS_EN_VAL_PUSE.crsp_val_us_exists and
554 -- the 'old' parameters passed in.
555 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_US.crsp_val_us_exists
556 IF IGS_EN_VAL_PUSE.crsp_val_us_exists(
557 p_old_unit_set_cd,
558 p_old_version_number,
559 v_message_name) = FALSE THEN
560 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
561 RETURN;
562 END IF;
563 -- 3. For each of the subordinate tables find records using the
564 -- unit_set_cd and version_number as for the values of the
565 -- 'old' parameters passed in and insert records,
566 -- substituting values for unit_set_cd and version_number
567 -- with the values of the 'new' parameters passed in.
568 -- IGS_GE_NOTE : An exception handler is raised when an error number is
569 -- found to be in the range -20000 to -20999 (which
570 -- indicates that the exception is user defined
571 -- one of the validation routines within the system).
572 -- If not within this range, it will be raised by
573 -- standard exception handling.
574 -- Check if the IGS_EN_UNIT_SET_NOTE record exists for the old IGS_PS_UNIT
575 -- code and version number. If so, create the new record
576 -- with the substituted values.
577 crspl_unit_set_note;
578 --- Check if the IGS_EN_UNITSETPSTYPE record exists for
579 -- the old IGS_PS_UNIT code and version number. If so, create
580 -- the new record with the substituted values.
581 crspl_unit_set_crs_type;
582 -- Check if the IGS_PS_OFR_UNIT_SET record exists
583 -- for the old IGS_PS_UNIT code and version number. If so,
584 -- create the new record with the substituted values.
585 -- Create new records for child records of cous
586 -- i.e. cousr and coous
587 crspl_crs_off_unit_set;
588 --- Check if the IGS_EN_UNIT_SET_RULE record exists for the old IGS_PS_UNIT code and
589 --- version number. If so, create the new record with the substituted values.
590 crspl_unit_set_rule;
591 --- Check if the record exists for the old IGS_PS_UNIT code and
592 --- version number. If so, create the new record with the substituted values.
593 crspl_coo_adm_cat_unit_set;
594 END;
595 EXCEPTION
596 WHEN OTHERS THEN
597 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
598 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_008.crsp_ins_unit_set');
599 IGS_GE_MSG_STACK.ADD;
600 App_Exception.Raise_Exception;
601 END crsp_ins_unit_set;
602
603 FUNCTION get_section_status (p_c_src_usec_status igs_ps_unit_ofr_opt_all.unit_section_status%TYPE
604 ) RETURN VARCHAR2 AS
605 /*************************************************************
606 Created By : sarakshi
607 Date Created By :14-Oct-2004
608 Purpose :To get the destination unit section status
609 Know limitations, enhancements or remarks
610 Change History
611 Who When What
612
613 (reverse chronological order - newest change first)
614 ***************************************************************/
615 l_c_usec_status igs_ps_unit_ofr_opt_all.unit_section_status%TYPE;
616 BEGIN
617 IF p_c_src_usec_status = 'OPEN' THEN
618 l_c_usec_status := 'OPEN';
619 ELSIF p_c_src_usec_status = 'PLANNED' THEN
620 l_c_usec_status := 'PLANNED';
621 ELSIF p_c_src_usec_status = 'CANCELLED' THEN
622 l_c_usec_status := 'CANCELLED';
623 ELSIF p_c_src_usec_status = 'NOT_OFFERED' THEN
624 l_c_usec_status := 'NOT_OFFERED';
625 ELSIF p_c_src_usec_status = 'CLOSED' THEN
626 l_c_usec_status := 'OPEN';
627 ELSIF p_c_src_usec_status = 'FULLWAITOK' THEN
628 l_c_usec_status := 'OPEN';
629 ELSIF p_c_src_usec_status = 'HOLD' THEN
630 l_c_usec_status := 'OPEN';
631 END IF;
632
633 RETURN l_c_usec_status;
634
635 END get_section_status;
636
637
638 --Private procedure for updating the sup_uo_id and relation_type value of IGS_PS_UNIT_OFR_OPT_ALL
639 PROCEDURE update_usec_record (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
640 p_relation_type igs_ps_unit_ofr_opt.relation_type%TYPE,
641 p_sup_uoo_id igs_ps_unit_ofr_opt.sup_uoo_id%TYPE,
642 p_default_enroll_flag igs_ps_unit_ofr_opt.default_enroll_flag%TYPE) IS
643 /*----------------------------------------------------------------------------
644 || Created By :sarakshi
645 || Created On :17-oct-2003
646 || Purpose :For updating the sup_uo_id and relation_type value of IGS_PS_UNIT_OFR_OPT_ALL from multiple places in this package
647 || Known limitations, enhancements or remarks :
648 || Change History :
649 || Who When What
650 || (reverse chronological order - newest change first)
651 || sarakshi 17-Nov-2005 Bug#4726940,changed the signature by adding p_default_enroll_flag
652 ----------------------------------------------------------------------------*/
653
654 CURSOR cur_usec(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
655 SELECT *
656 FROM igs_ps_unit_ofr_opt
657 WHERE uoo_id=cp_uoo_id;
658 l_cur_usec cur_usec%ROWTYPE;
659
660 BEGIN
661 OPEN cur_usec(p_uoo_id);
662 FETCH cur_usec INTO l_cur_usec;
663 CLOSE cur_usec;
664
665 igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_cur_usec.row_id,
666 x_unit_cd =>l_cur_usec.unit_cd,
667 x_version_number =>l_cur_usec.version_number,
668 x_cal_type =>l_cur_usec.cal_type,
669 x_ci_sequence_number =>l_cur_usec.ci_sequence_number,
670 x_location_cd =>l_cur_usec.location_cd,
671 x_unit_class =>l_cur_usec.unit_class,
672 x_uoo_id =>l_cur_usec.uoo_id,
673 x_ivrs_available_ind =>l_cur_usec.ivrs_available_ind,
674 x_call_number =>l_cur_usec.call_number,
675 x_unit_section_status =>l_cur_usec.unit_section_status,
676 x_unit_section_start_date =>l_cur_usec.unit_section_start_date,
677 x_unit_section_end_date =>l_cur_usec.unit_section_end_date,
678 x_enrollment_actual =>l_cur_usec.enrollment_actual,
679 x_waitlist_actual =>l_cur_usec.waitlist_actual,
680 x_offered_ind =>l_cur_usec.offered_ind,
681 x_state_financial_aid =>l_cur_usec.state_financial_aid,
682 x_grading_schema_prcdnce_ind =>l_cur_usec.grading_schema_prcdnce_ind,
683 x_federal_financial_aid =>l_cur_usec.federal_financial_aid,
684 x_unit_quota =>l_cur_usec.unit_quota,
685 x_unit_quota_reserved_places =>l_cur_usec.unit_quota_reserved_places,
686 x_institutional_financial_aid =>l_cur_usec.institutional_financial_aid,
687 x_grading_schema_cd =>l_cur_usec.grading_schema_cd,
688 x_gs_version_number =>l_cur_usec.gs_version_number,
689 x_unit_contact =>l_cur_usec.unit_contact,
690 x_mode =>'R',
691 x_ss_enrol_ind => l_cur_usec.ss_enrol_ind,
692 x_owner_org_unit_cd => l_cur_usec.owner_org_unit_cd,
693 x_attendance_required_ind => l_cur_usec.attendance_required_ind,
694 x_reserved_seating_allowed => l_cur_usec.reserved_seating_allowed,
695 x_ss_display_ind => l_cur_usec.ss_display_ind,
696 x_special_permission_ind => l_cur_usec.special_permission_ind,
697 x_rev_account_cd => l_cur_usec.rev_account_cd ,
698 x_anon_unit_grading_ind => l_cur_usec.anon_unit_grading_ind,
699 x_anon_assess_grading_ind => l_cur_usec.anon_assess_grading_ind ,
700 x_non_std_usec_ind => l_cur_usec.non_std_usec_ind,
701 x_auditable_ind => l_cur_usec.auditable_ind,
702 x_audit_permission_ind => l_cur_usec.audit_permission_ind,
703 x_not_multiple_section_flag => l_cur_usec.not_multiple_section_flag,
704 x_sup_uoo_id => p_sup_uoo_id,
705 x_relation_type => p_relation_type,
706 x_default_enroll_flag => NVL(p_default_enroll_flag,l_cur_usec.default_enroll_flag),
707 x_abort_flag => l_cur_usec.abort_flag
708 );
709
710 END update_usec_record;
711
712
713 PROCEDURE crsp_ins_unit_ver(
714 p_old_unit_cd IN VARCHAR2 ,
715 p_old_version_number IN NUMBER ,
716 p_new_unit_cd IN VARCHAR2 ,
717 p_new_version_number IN NUMBER ,
718 p_message_name OUT NOCOPY VARCHAR2,
719 p_c_message_superior OUT NOCOPY VARCHAR2) AS
720 /*----------------------------------------------------------------------------
721 || Created By :
722 || Created On :
723 || Purpose :
724 || Known limitations, enhancements or remarks :
725 || Change History :
726 || Who When What
727 || (reverse chronological order - newest change first)
728 || sarakshi 03-Jun-2004 Bug#3655650, modified procedure crspl_ins_unit_assmnt_item to rollover the unit assessment items group records.Also modified the procedure crspl_uofr_wlist_details
729 || sarakshi 17-oct-2003 Enh#3168650,Added procedure crspl_upd_usec_relation
730 || vvutukur 24-May-2003 Enh#2831572.Financial Accounting Build. Removed the local procedure CRSP_INS_UNIT_REVSEG and its related call.
731 ----------------------------------------------------------------------------*/
732
733 cst_upper_limit_err NUMBER;
734 cst_lower_limit_err NUMBER;
735 gv_unit_version_exist VARCHAR2(1);
736 PROCEDURE crspl_ins_duplicate_note (
737 p_existing_ref_number IGS_GE_NOTE.reference_number%TYPE,
738 p_new_ref_number OUT NOCOPY IGS_GE_NOTE.reference_number%TYPE )
739 AS
740 CURSOR SGN_CUR IS
741 SELECT *
742 FROM IGS_GE_NOTE
743 WHERE reference_number = p_existing_ref_number;
744 BEGIN
745 --- Get new reference number
746 SELECT IGS_GE_NOTE_RF_NUM_S.nextval
747 INTO p_new_ref_number
748 FROM dual;
749 --- Get related IGS_GE_NOTE and insert under new reference number
750
751 For SGN_Rec In SGN_CUR
752 Loop
753 x_rowid := NULL;
754 IGS_GE_NOTE_PKG.Insert_Row(
755 X_ROWID => x_rowid,
756 X_REFERENCE_NUMBER => p_new_ref_number,
757 X_S_NOTE_FORMAT_TYPE => SGN_Rec.s_note_format_type,
758 X_NOTE_TEXT => SGN_Rec.Note_Text,
759 X_MODE => 'R');
760 End Loop;
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 IF SQLCODE >= cst_lower_limit_err AND
765 SQLCODE <= cst_upper_limit_err THEN
766 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
767 ELSE
768 App_Exception.Raise_Exception;
769 END IF;
770 END crspl_ins_duplicate_note;
771
772 PROCEDURE crspl_ins_unit_ver_note (
773 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
774 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
775 AS
776 v_unit_ver_note_rec IGS_PS_UNIT_VER_NOTE%ROWTYPE;
777 v_new_ref_number IGS_GE_NOTE.reference_number%TYPE;
778 --- The following cursor excludes notes records with NULL values in the
779 --- note_text field as this implies that it contains data in the note_ole
780 --- field which cannot be copied with the current product limitations.
781 CURSOR c_unit_ver_note_rec IS
782 SELECT *
783 FROM IGS_PS_UNIT_VER_NOTE uvn
784 WHERE uvn.unit_cd = p_unit_cd AND
785 uvn.version_number = p_version_number AND
786 EXISTS (
787 SELECT 'x'
788 FROM IGS_GE_NOTE nte
789 WHERE nte.reference_number = uvn.reference_number AND
790 nte.note_text IS NOT NULL );
791 BEGIN
792 FOR v_unit_ver_note_rec IN c_unit_ver_note_rec LOOP
793 crspl_ins_duplicate_note(
794 v_unit_ver_note_rec.reference_number,
795 v_new_ref_number);
796 BEGIN
797 x_rowid := NULL;
798 IGS_PS_UNIT_VER_NOTE_PKG.Insert_Row(
799 X_ROWID => x_rowid,
800 X_UNIT_CD => p_new_unit_cd,
801 X_REFERENCE_NUMBER => v_new_ref_number,
802 X_VERSION_NUMBER => p_new_version_number,
803 X_CRS_NOTE_TYPE => v_unit_ver_note_rec.crs_note_type,
804 X_MODE => 'R');
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 IF SQLCODE >= cst_lower_limit_err AND
809 SQLCODE <= cst_upper_limit_err THEN
810 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
811 ELSE
812 App_Exception.Raise_Exception;
813 END IF;
814 END;
815 END LOOP;
816 EXCEPTION
817 WHEN OTHERS THEN
818 IF SQLCODE >= cst_lower_limit_err OR SQLCODE <= cst_upper_limit_err THEN
819 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
820 ELSE
821 App_Exception.Raise_Exception;
822 END IF;
823 END crspl_ins_unit_ver_note;
824 PROCEDURE crspl_ins_unit_offer_note (
825 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
826 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
827 AS
828 v_unit_offer_note_rec IGS_PS_UNIT_OFR_NOTE%ROWTYPE;
829 v_new_ref_number IGS_GE_NOTE.reference_number%TYPE;
830 --- The following cursor excludes notes records with NULL values in the
831 --- note_text field as this implies that it contains data in the note_ole
832 --- field which cannot be copied with the current product limitations.
833 CURSOR c_unit_offer_note_rec IS
834 SELECT *
835 FROM IGS_PS_UNIT_OFR_NOTE uon
836 WHERE uon.unit_cd = p_unit_cd AND
837 uon.version_number = p_version_number AND
838 EXISTS (
839 SELECT 'x'
840 FROM IGS_GE_NOTE nte
841 WHERE nte.reference_number = uon.reference_number AND
842 nte.note_text IS NOT NULL );
843
844 BEGIN
845 FOR v_unit_offer_note_rec IN c_unit_offer_note_rec LOOP
846 crspl_ins_duplicate_note(
847 v_unit_offer_note_rec.reference_number,
848 v_new_ref_number);
849 BEGIN
850 x_rowid := NULL;
851 IGS_PS_UNIT_OFR_NOTE_PKG.Insert_Row(
852 X_ROWID => x_rowid,
853 X_UNIT_CD => p_new_unit_cd,
854 X_VERSION_NUMBER => p_new_version_number,
855 X_CAL_TYPE => v_unit_offer_note_rec.cal_type,
856 X_REFERENCE_NUMBER => v_new_ref_number,
857 X_CRS_NOTE_TYPE => v_unit_offer_note_rec.crs_note_type,
858 X_MODE => 'R');
859 EXCEPTION
860 WHEN OTHERS THEN
861 IF SQLCODE >= cst_lower_limit_err AND
862 SQLCODE <= cst_upper_limit_err THEN
863 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
864 ELSE
865 App_Exception.Raise_Exception;
866 END IF;
867 END;
868 END LOOP;
869 EXCEPTION
870 WHEN OTHERS THEN
871 IF SQLCODE >= cst_lower_limit_err AND
872 SQLCODE <= cst_upper_limit_err THEN
873 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
874 ELSE
875 App_Exception.Raise_Exception;
876 END IF;
877 END crspl_ins_unit_offer_note;
878 PROCEDURE crspl_ins_teach_resp (
879 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
880 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
881 AS
882 v_teach_resp_rec IGS_PS_TCH_RESP%ROWTYPE;
883 CURSOR c_teach_resp_rec IS
884 SELECT *
885 FROM IGS_PS_TCH_RESP
886 WHERE unit_cd = p_unit_cd AND
887 version_number = p_version_number;
888
889 BEGIN
890 FOR v_teach_resp_rec IN c_teach_resp_rec LOOP
891 BEGIN
892 x_rowid := NULL;
893 IGS_PS_TCH_RESP_PKG.Insert_Row(
894 X_ROWID => x_rowid,
895 X_UNIT_CD => p_new_unit_cd,
896 X_VERSION_NUMBER => p_new_version_number,
897 X_OU_START_DT => v_teach_resp_rec.ou_start_dt,
898 X_ORG_UNIT_CD => v_teach_resp_rec.org_unit_cd,
899 X_PERCENTAGE => v_teach_resp_rec.percentage,
900 X_MODE => 'R');
901 EXCEPTION
902 WHEN OTHERS THEN
903 IF SQLCODE >= cst_lower_limit_err AND
904 SQLCODE <= cst_upper_limit_err THEN
905 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
906 ELSE
907 App_Exception.Raise_Exception;
908 END IF;
909 END;
910 END LOOP;
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF SQLCODE >= cst_lower_limit_err AND
914 SQLCODE <= cst_upper_limit_err THEN
915 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
916 ELSE
917 App_Exception.Raise_Exception;
918 END IF;
919 END crspl_ins_teach_resp;
920 PROCEDURE crspl_ins_unit_discipline (
921 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
922 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
923 AS
924 v_unit_discipline_rec IGS_PS_UNIT_DSCP%ROWTYPE;
925 CURSOR c_unit_discipline_rec IS
926 SELECT *
927 FROM IGS_PS_UNIT_DSCP
928 WHERE unit_cd = p_unit_cd AND
929 version_number = p_version_number;
930
931 BEGIN
932 FOR v_unit_discipline_rec IN c_unit_discipline_rec LOOP
933 BEGIN
934 x_rowid := NULL;
935 IGS_PS_UNIT_DSCP_PKG.Insert_Row(
936 X_ROWID => x_rowid,
937 X_UNIT_CD => p_new_unit_cd,
938 X_VERSION_NUMBER => p_new_version_number,
939 X_DISCIPLINE_GROUP_CD => v_unit_discipline_rec.discipline_group_cd,
940 X_PERCENTAGE => v_unit_discipline_rec.percentage,
941 X_MODE => 'R');
942 EXCEPTION
943 WHEN OTHERS THEN
944 IF SQLCODE >= cst_lower_limit_err AND
945 SQLCODE <= cst_upper_limit_err THEN
946 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
947 ELSE
948 App_Exception.Raise_Exception;
949 END IF;
950 END;
951 END LOOP;
952 EXCEPTION
953 WHEN OTHERS THEN
954 IF SQLCODE >= cst_lower_limit_err AND
955 SQLCODE <= cst_upper_limit_err THEN
956 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
957 ELSE
958 App_Exception.Raise_Exception;
959 END IF;
960 END crspl_ins_unit_discipline;
961 PROCEDURE crspl_ins_unit_categorisation (
962 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
963 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
964 AS
965 v_unit_cat_rec IGS_PS_UNIT_CATEGORY%ROWTYPE;
966 CURSOR c_unit_cat_rec IS
967 SELECT *
968 FROM IGS_PS_UNIT_CATEGORY
969 WHERE unit_cd = p_unit_cd AND
970 version_number = p_version_number;
971
972 l_org_id NUMBER(15);
973
974 BEGIN
975 FOR v_unit_cat_rec IN c_unit_cat_rec LOOP
976 BEGIN
977 x_rowid := NULL;
978 l_org_id := igs_ge_gen_003.get_org_id;
979
980 IGS_PS_UNIT_CATEGORY_PKG.Insert_Row(
981 X_ROWID => x_rowid,
982 X_UNIT_CD => p_new_unit_cd,
983 X_VERSION_NUMBER => p_new_version_number,
984 X_UNIT_CAT => v_unit_cat_rec.unit_cat,
985 X_MODE => 'R',
986 X_ORG_ID => l_org_id);
987
988 EXCEPTION
989 WHEN OTHERS THEN
990 IF SQLCODE >= cst_lower_limit_err AND
991 SQLCODE <= cst_upper_limit_err THEN
992 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
993 ELSE
994 App_Exception.Raise_Exception;
995 END IF;
996 END;
997 END LOOP;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 IF SQLCODE >= cst_lower_limit_err AND
1001 SQLCODE <= cst_upper_limit_err THEN
1002 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1003 ELSE
1004 App_Exception.Raise_Exception;
1005 END IF;
1006 END crspl_ins_unit_categorisation;
1007 PROCEDURE crspl_ins_crs_unit_lvl (
1008 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
1009 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
1010 AS
1011 v_crs_unit_lvl_rec IGS_PS_UNIT_LVL%ROWTYPE;
1012 CURSOR c_crs_unit_lvl_rec IS
1013 SELECT *
1014 FROM IGS_PS_UNIT_LVL
1015 WHERE unit_cd = p_unit_cd AND
1016 version_number = p_version_number;
1017
1018 l_org_id NUMBER(15);
1019
1020 BEGIN
1021 FOR v_crs_unit_lvl_rec IN c_crs_unit_lvl_rec LOOP
1022 BEGIN
1023 x_rowid := NULL;
1024
1025 l_org_id := igs_ge_gen_003.get_org_id;
1026 -- ijeddy, Bug# 3181938 removed course_type from the parameters.
1027 IGS_PS_UNIT_LVL_PKG.Insert_Row(
1028 X_ROWID => x_rowid,
1029 X_UNIT_CD => p_new_unit_cd,
1030 X_VERSION_NUMBER => p_new_version_number,
1031 X_UNIT_LEVEL => v_crs_unit_lvl_rec.unit_level,
1032 X_WAM_WEIGHTING => v_crs_unit_lvl_rec.wam_weighting,
1033 X_MODE => 'R',
1034 X_ORG_ID => l_org_id,
1035 X_COURSE_CD => v_crs_unit_lvl_rec.course_cd,
1036 X_COURSE_VERSION_NUMBER => v_crs_unit_lvl_rec.course_version_number
1037 );
1038
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 IF SQLCODE >= cst_lower_limit_err AND
1042 SQLCODE <= cst_upper_limit_err THEN
1043 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1044 ELSE
1045 App_Exception.Raise_Exception;
1046 END IF;
1047 END;
1048 END LOOP;
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 IF SQLCODE >= cst_lower_limit_err AND
1052 SQLCODE <= cst_upper_limit_err THEN
1053 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1054 ELSE
1055 App_Exception.Raise_Exception;
1056 END IF;
1057 END crspl_ins_crs_unit_lvl;
1058 PROCEDURE crspl_ins_unit_ref_cd (
1059 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
1060 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
1061 AS
1062 v_unit_ref_cd_rec IGS_PS_UNIT_REF_CD%ROWTYPE;
1063 CURSOR c_unit_ref_cd_rec IS
1064 SELECT *
1065 FROM IGS_PS_UNIT_REF_CD
1066 WHERE unit_cd = p_unit_cd AND
1067 version_number = p_version_number;
1068 BEGIN
1069 FOR v_unit_ref_cd_rec IN c_unit_ref_cd_rec LOOP
1070 BEGIN
1071 x_rowid :=NULL;
1072 IGS_PS_UNIT_REF_CD_PKG.Insert_Row(
1073 X_ROWID => x_rowid,
1074 X_UNIT_CD => p_new_unit_cd,
1075 X_VERSION_NUMBER => p_new_version_number,
1076 X_REFERENCE_CD_TYPE => v_unit_ref_cd_rec.reference_cd_type,
1077 X_REFERENCE_CD => v_unit_ref_cd_rec.reference_cd,
1078 X_DESCRIPTION => v_unit_ref_cd_rec.description,
1079 X_MODE => 'R');
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 IF SQLCODE >= cst_lower_limit_err AND
1084 SQLCODE <= cst_upper_limit_err THEN
1085 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1086 ELSE
1087 App_Exception.Raise_Exception;
1088 END IF;
1089 END;
1090 END LOOP;
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 IF SQLCODE >= cst_lower_limit_err AND
1094 SQLCODE <= cst_upper_limit_err THEN
1095 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1096 ELSE
1097 App_Exception.Raise_Exception;
1098 END IF;
1099 END crspl_ins_unit_ref_cd;
1100 PROCEDURE crspl_ins_unit_off_opt_note (
1101 p_exist_uoo_id IN IGS_PS_UNT_OFR_OPT_N.uoo_id%TYPE,
1102 p_new_uoo_id IN IGS_PS_UNT_OFR_OPT_N.uoo_id%TYPE )
1103 AS
1104 v_unit_offer_opt_note_rec IGS_PS_UNT_OFR_OPT_N%ROWTYPE;
1105 v_new_ref_number IGS_PS_UNT_OFR_OPT_N.reference_number%TYPE;
1106 v_uoo_id IGS_PS_UNT_OFR_OPT_N.uoo_id%TYPE;
1107 --- The following cursor excludes notes records with NULL values in the
1108 --- note_text field as this implies that it contains data in the note_ole
1109 --- field which cannot be copied with the current product limitations.
1110 CURSOR c_unit_off_opt_note_rec IS
1111 SELECT *
1112 FROM IGS_PS_UNT_OFR_OPT_N uoon
1113 WHERE uoon.uoo_id = p_exist_uoo_id AND
1114 EXISTS (
1115 SELECT 'x'
1116 FROM IGS_GE_NOTE nte
1117 WHERE nte.reference_number = uoon.reference_number AND
1118 nte.note_text IS NOT NULL );
1119 BEGIN
1120 FOR v_unit_offer_opt_note_rec IN c_unit_off_opt_note_rec LOOP
1121 crspl_ins_duplicate_note(
1122 v_unit_offer_opt_note_rec.reference_number,
1123 v_new_ref_number);
1124 BEGIN
1125 x_rowid := NULL;
1126 v_uoo_id := p_new_uoo_id;
1127 IGS_PS_UNT_OFR_OPT_N_PKG.INSERT_ROW(
1128 X_ROWID => x_rowid,
1129 X_UNIT_CD => p_new_unit_cd,
1130 X_VERSION_NUMBER => p_new_version_number,
1131 X_CI_SEQUENCE_NUMBER => v_unit_offer_opt_note_rec.ci_sequence_number,
1132 X_UNIT_CLASS => v_unit_offer_opt_note_rec.unit_class,
1133 X_REFERENCE_NUMBER => v_new_ref_number,
1134 X_LOCATION_CD => v_unit_offer_opt_note_rec.location_cd,
1135 X_CAL_TYPE => v_unit_offer_opt_note_rec.cal_type,
1136 X_UOO_ID => v_uoo_id,
1137 X_CRS_NOTE_TYPE => v_unit_offer_opt_note_rec.crs_note_type,
1138 X_MODE => 'R'
1139 );
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 IF SQLCODE >= cst_lower_limit_err AND
1143 SQLCODE <= cst_upper_limit_err THEN
1144 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1145 ELSE
1146 App_Exception.Raise_Exception;
1147 END IF;
1148 END;
1149 END LOOP;
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 IF SQLCODE >= cst_lower_limit_err AND
1153 SQLCODE <= cst_upper_limit_err THEN
1154 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1155 ELSE
1156 App_Exception.Raise_Exception;
1157 END IF;
1158 END crspl_ins_unit_off_opt_note;
1159 PROCEDURE crspl_ins_teach_resp_ovrd(
1160 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
1161 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE,
1162 p_cal_type IN IGS_PS_UNIT_OFR_OPT.cal_type%TYPE,
1163 p_ci_sequence_number IN IGS_PS_UNIT_OFR_OPT.ci_sequence_number%TYPE,
1164 p_location_cd IN IGS_PS_UNIT_OFR_OPT.location_cd%TYPE,
1165 p_unit_class IN IGS_PS_UNIT_OFR_OPT.unit_class%TYPE,
1166 p_new_uoo_id IN IGS_PS_UNT_OFR_OPT_N.uoo_id%TYPE)
1167 AS
1168 CURSOR c_tro IS
1169 SELECT tro.location_cd,
1170 tro.unit_class,
1171 tro.org_unit_cd,
1172 tro.ou_start_dt,
1173 tro.percentage
1174 FROM IGS_PS_TCH_RESP_OVRD tro
1175 WHERE tro.unit_cd = p_unit_cd AND
1176 tro.version_number = p_version_number AND
1177 tro.cal_type = p_cal_type AND
1178 tro.ci_sequence_number = p_ci_sequence_number AND
1179 tro.location_cd = p_location_cd AND
1180 tro.unit_class = p_unit_class;
1181
1182 l_org_id NUMBER(15);
1183 BEGIN
1184 FOR c_tro_rec in c_tro LOOP
1185 -- copy old IGS_PS_UNIT_VER IGS_PS_TCH_RESP_OVRD details to
1186 -- new IGS_PS_UNIT-version
1187 BEGIN
1188 x_rowid := NULL;
1189
1190 l_org_id := igs_ge_gen_003.get_org_id;
1191
1192 IGS_PS_TCH_RESP_OVRD_PKG.INSERT_ROW(
1193 X_ROWID => x_rowid,
1194 X_UNIT_CD => p_new_unit_cd,
1195 X_VERSION_NUMBER => p_new_version_number,
1196 X_LOCATION_CD => c_tro_rec.location_cd,
1197 X_CI_SEQUENCE_NUMBER => p_ci_sequence_number,
1198 X_CAL_TYPE => p_cal_type,
1199 X_UNIT_CLASS => c_tro_rec.unit_class,
1200 X_OU_START_DT => c_tro_rec.ou_start_dt,
1201 X_ORG_UNIT_CD => c_tro_rec.org_unit_cd,
1202 X_UOO_ID => p_new_uoo_id,
1203 X_PERCENTAGE => c_tro_rec.percentage,
1204 X_MODE => 'R',
1205 X_ORG_ID => l_org_id);
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 IF SQLCODE >= cst_lower_limit_err AND
1209 SQLCODE <= cst_upper_limit_err THEN
1210 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1211 ELSE
1212 App_Exception.Raise_Exception;
1213 END IF;
1214 END;
1215 END LOOP;
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 IF SQLCODE >= cst_lower_limit_err AND
1219 SQLCODE <= cst_upper_limit_err THEN
1220 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1221 ELSE
1222 App_Exception.Raise_Exception;
1223 END IF;
1224 END crspl_ins_teach_resp_ovrd;
1225
1226
1227 PROCEDURE crspl_upd_usec_relation( p_old_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
1228 p_new_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) AS
1229 /*----------------------------------------------------------------------------
1230 || Created By :sarakshi
1231 || Created On :17-oct-2003
1232 || Purpose :For Rolling over the unit section relationship
1233 || Known limitations, enhancements or remarks :
1234 || Change History :
1235 || Who When What
1236 || (reverse chronological order - newest change first)
1237 || sarakshi 13-Jan-2006 Bug#4926548, modified cursor c_new_sub and c_new_sup performance issue
1238 || sarakshi 17-Nov-2005 Bug#4726940, impact of change of signature of the update_usec_record
1239 ----------------------------------------------------------------------------*/
1240 l_c_none VARCHAR2(10);
1241 l_c_superior VARCHAR2(10);
1242 l_c_subordinate VARCHAR2(15);
1243 l_c_active VARCHAR2(10);
1244 l_c_planned VARCHAR2(10);
1245
1246 CURSOR c_old_sub IS
1247 SELECT *
1248 FROM igs_ps_unit_ofr_opt
1249 WHERE relation_type = l_c_subordinate
1250 AND sup_uoo_id = p_old_uoo_id;
1251
1252 CURSOR c_old_sup IS
1253 SELECT *
1254 FROM igs_ps_unit_ofr_opt
1255 WHERE relation_type = l_c_superior
1256 AND uoo_id = (SELECT sup_uoo_id
1257 FROM igs_ps_unit_ofr_opt
1258 WHERE uoo_id = p_old_uoo_id
1259 );
1260
1261 CURSOR c_new_sub(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,
1262 cp_ci_sequence_number igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE,
1263 cp_location_cd igs_ps_unit_ofr_opt_all.location_cd%TYPE,
1264 cp_unit_class igs_ps_unit_ofr_opt_all.unit_class%TYPE,
1265 cp_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE,
1266 cp_version_number igs_ps_unit_ofr_opt_all.version_number%TYPE
1267 )IS
1268 SELECT uoo.*
1269 FROM igs_ps_unit_ofr_opt_all uoo,igs_ps_unit_ver_all uv, igs_ps_unit_stat us
1270 WHERE uoo.cal_type = cp_cal_type
1271 AND uoo.ci_sequence_number = cp_ci_sequence_number
1272 AND uoo.location_cd = cp_location_cd
1273 AND uoo.unit_class = cp_unit_class
1274 AND uoo.unit_cd = cp_unit_cd
1275 AND uoo.version_number > cp_version_number
1276 AND uoo.unit_cd=uv.unit_cd
1277 AND uoo.version_number=uv.version_number
1278 AND uv.unit_status = us.unit_status
1279 AND us.s_unit_status IN (l_c_active,l_c_planned)
1280 AND uoo.relation_type = l_c_none
1281 AND uoo_id NOT IN (SELECT uoo_id FROM igs_en_su_attempt)
1282 ORDER BY uoo.unit_cd,uoo.version_number ASC;
1283
1284 CURSOR c_new_sup(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,
1285 cp_ci_sequence_number igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE,
1286 cp_location_cd igs_ps_unit_ofr_opt_all.location_cd%TYPE,
1287 cp_unit_class igs_ps_unit_ofr_opt_all.unit_class%TYPE,
1288 cp_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE,
1289 cp_version_number igs_ps_unit_ofr_opt_all.version_number%TYPE
1290 )IS
1291 SELECT uoo.*
1292 FROM igs_ps_unit_ofr_opt_all uoo,igs_ps_unit_ver_all uv, igs_ps_unit_stat us
1293 WHERE uoo.cal_type = cp_cal_type
1294 AND uoo.ci_sequence_number = cp_ci_sequence_number
1295 AND uoo.location_cd = cp_location_cd
1296 AND uoo.unit_class = cp_unit_class
1297 AND uoo.unit_cd = cp_unit_cd
1298 AND uoo.version_number > cp_version_number
1299 AND uoo.unit_cd=uv.unit_cd
1300 AND uoo.version_number=uv.version_number
1301 AND uv.unit_status = us.unit_status
1302 AND us.s_unit_status IN (l_c_active,l_c_planned)
1303 AND uoo_id NOT IN (SELECT uoo_id FROM igs_en_su_attempt)
1304 AND uoo.relation_type IN (l_c_superior,l_c_none)
1305 ORDER BY uoo.unit_cd,uoo.version_number ASC;
1306
1307 l_c_new_sub c_new_sub%ROWTYPE;
1308 l_c_new_sup c_new_sup%ROWTYPE;
1309
1310 BEGIN
1311 --Initilizing this as it was giving gscc warning File.Sql.35
1312 l_c_none := 'NONE';
1313 l_c_superior := 'SUPERIOR';
1314 l_c_subordinate := 'SUBORDINATE';
1315 l_c_active := 'ACTIVE';
1316 l_c_planned := 'PLANNED';
1317
1318 --Process all subordinates that is fetched from above cursor c_old_sub
1319 FOR l_old_sub_rec IN c_old_sub LOOP
1320
1321 --A cursor to get the new version of subordinate record
1322 OPEN c_new_sub(l_old_sub_rec.cal_type,l_old_sub_rec.ci_sequence_number,l_old_sub_rec.location_cd,l_old_sub_rec.unit_class,l_old_sub_rec.unit_cd,l_old_sub_rec.version_number);
1323 FETCH c_new_sub INTO l_c_new_sub;
1324 IF c_new_sub%FOUND THEN
1325 CLOSE c_new_sub;
1326
1327 --Update the new subordinate unit section record
1328 update_usec_record (p_uoo_id => l_c_new_sub.uoo_id,
1329 p_relation_type => l_c_subordinate,
1330 p_sup_uoo_id => p_new_uoo_id,
1331 p_default_enroll_flag => l_old_sub_rec.default_enroll_flag);
1332
1333 --Update the new superior unit section record
1334 update_usec_record (p_uoo_id => p_new_uoo_id,
1335 p_relation_type => l_c_superior,
1336 p_sup_uoo_id => NULL,
1337 p_default_enroll_flag => NULL);
1338 ELSE
1339 CLOSE c_new_sub;
1340 --Add the unsuccessful unit section's unit to the out variable to display it in the form
1341 IF p_c_message_superior IS NOT NULL THEN
1342 p_c_message_superior := p_c_message_superior ||','||l_old_sub_rec.unit_cd;
1343 ELSE
1344 p_c_message_superior := l_old_sub_rec.unit_cd;
1345 END IF;
1346 END IF;
1347
1348 END LOOP;
1349
1350 --Process all superior that is fetched from above cursor c_old_sup
1351 FOR l_old_sup_rec IN c_old_sup LOOP
1352
1353 --A cursor to get the new version of subordinate record
1354 OPEN c_new_sup(l_old_sup_rec.cal_type,l_old_sup_rec.ci_sequence_number,l_old_sup_rec.location_cd,l_old_sup_rec.unit_class,l_old_sup_rec.unit_cd,l_old_sup_rec.version_number);
1355 FETCH c_new_sup INTO l_c_new_sup;
1356 IF c_new_sup%FOUND THEN
1357 CLOSE c_new_sup;
1358
1359 --Update the new superior unit section record
1360 update_usec_record (p_uoo_id => l_c_new_sup.uoo_id,
1361 p_relation_type => l_c_superior,
1362 p_sup_uoo_id => NULL ,
1363 p_default_enroll_flag => NULL);
1364
1365 --Update the new subordinate unit section record
1366 update_usec_record (p_uoo_id => p_new_uoo_id,
1367 p_relation_type => l_c_subordinate,
1368 p_sup_uoo_id => l_c_new_sup.uoo_id,
1369 p_default_enroll_flag => NULL );
1370 ELSE
1371 CLOSE c_new_sup;
1372 --Add the unsuccessful unit section's unit to the out variable to display it in the form
1373 IF p_c_message_superior IS NOT NULL THEN
1374 p_c_message_superior := p_c_message_superior ||','||l_old_sup_rec.unit_cd;
1375 ELSE
1376 p_c_message_superior := l_old_sup_rec.unit_cd;
1377 END IF;
1378 END IF;
1379
1380 END LOOP;
1381
1382 END crspl_upd_usec_relation;
1383
1384
1385 PROCEDURE crspl_ins_unit_off_opt (
1386 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
1387 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE,
1388 p_cal_type IN IGS_PS_UNIT_OFR_OPT.cal_type%TYPE,
1389 p_ci_sequence_number IN IGS_PS_UNIT_OFR_OPT.ci_sequence_number%TYPE )
1390 AS
1391 /*
1392 WHO WHEN WHAT
1393 sarakshi 14-oct-2004 Bug#3945817, passsing unit section status as mentioned in the bug.
1394 sarakshi 31-AUG-2004 Bug#3864738,passed unit_section_status as OPEN in the insert row call of IGS_PS_UNIT_OFR_OPT
1395 sarakshi 13-Apr-2004 Bug#3555871, removed the logic of getting the call number for AUTO profile option.
1396 sarakshi 17-oct-2003 Enh#3168650,Added call to the procedure crspl_upd_usec_relation
1397 sarakshi 23-sep-2003 Enh#3052452,Added column sup_uoo_id,relation_type,default_enroll_flag to the call of igs_ps_unit_ofr_opt_pkg.insert_row
1398 vvutukur 05-aug-2003 Enh#3045069.PSP Enh Build. Modified the call to igs_ps_unit_ofr_opt_pkg.insert_row to added new column not_multiple_section_flag.
1399 sarakshi 18-Apr-2003 Bug#2910695,passed actual_enrollment and actual_waitlist null in the table IGS_PS_OFR_OPT_ALL
1400 sarakshi 05-Mar-2003 bug#2768783,added logic for checking/generating the call number
1401 */
1402 v_unit_offer_opt_rec IGS_PS_UNIT_OFR_OPT%ROWTYPE;
1403 v_new_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE;
1404 v_latest_gs_version IGS_PS_UNIT_OFR_OPT.gs_version_number%TYPE;
1405 CURSOR c_unit_offer_opt_rec IS
1406 SELECT *
1407 FROM IGS_PS_UNIT_OFR_OPT
1408 WHERE unit_cd = p_unit_cd AND
1409 version_number = p_version_number AND
1410 cal_type = p_cal_type AND
1411 ci_sequence_number = p_ci_sequence_number;
1412 CURSOR c_latest_gs_version (
1413 cp_gs_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE) IS
1414 SELECT MAX(gs.version_number)
1415 FROM IGS_AS_GRD_SCHEMA gs
1416 WHERE gs.grading_schema_cd = cp_gs_cd;
1417
1418 l_org_id NUMBER(15);
1419 l_c_usec_status igs_ps_unit_ofr_opt_all.unit_section_status%TYPE;
1420 BEGIN
1421 FOR v_unit_offer_opt_rec IN c_unit_offer_opt_rec LOOP
1422 -- get the last version number for grading schema cd
1423 OPEN c_latest_gs_version (
1424 v_unit_offer_opt_rec.grading_schema_cd);
1425 FETCH c_latest_gs_version INTO v_latest_gs_version;
1426 CLOSE c_latest_gs_version;
1427 BEGIN
1428 SELECT IGS_PS_UNIT_OFR_OPT_UOO_ID_S.nextval
1429 INTO v_new_uoo_id
1430 FROM dual;
1431 x_rowid := NULL;
1432
1433 l_org_id := igs_ge_gen_003.get_org_id;
1434 -- Added auditable_ind, audit_permission_ind parameters to the following call to insert_row
1435 -- as part of Bug# 2636716, EN Integration by shtatiko.
1436
1437 --bug#2768783, added the validate/generate call number logic
1438
1439 -- Validate/generate Call Number
1440 IF fnd_profile.value('IGS_PS_CALL_NUMBER') IN ('AUTO','NONE') THEN
1441 v_unit_offer_opt_rec.call_number:=NULL;
1442 ELSIF ( fnd_profile.value('IGS_PS_CALL_NUMBER') = 'USER_DEFINED' ) THEN
1443
1444 IF v_unit_offer_opt_rec.call_number IS NOT NULL THEN
1445 IF NOT igs_ps_unit_ofr_opt_pkg.check_call_number ( p_teach_cal_type => v_unit_offer_opt_rec.cal_type,
1446 p_teach_sequence_num => v_unit_offer_opt_rec.ci_sequence_number,
1447 p_call_number => v_unit_offer_opt_rec.call_number,
1448 p_rowid => x_rowid ) THEN
1449 v_unit_offer_opt_rec.call_number:=NULL;
1450 END IF;
1451 END IF;
1452
1453 END IF;
1454
1455 l_c_usec_status := get_section_status(v_unit_offer_opt_rec.unit_section_status);
1456
1457 IGS_PS_UNIT_OFR_OPT_PKG.INSERT_ROW(
1458 X_ROWID => x_rowid,
1459 X_UNIT_CD => p_new_unit_cd,
1460 X_VERSION_NUMBER => P_new_version_number,
1461 X_CAL_TYPE => v_unit_offer_opt_rec.cal_type,
1462 X_CI_SEQUENCE_NUMBER => v_unit_offer_opt_rec.ci_sequence_number,
1463 X_LOCATION_CD => v_unit_offer_opt_rec.location_cd,
1464 X_UNIT_CLASS => v_unit_offer_opt_rec.unit_class,
1465 X_UOO_ID => v_new_uoo_id,
1466 X_IVRS_AVAILABLE_IND => v_unit_offer_opt_rec.ivrs_available_ind,
1467 X_CALL_NUMBER => v_unit_offer_opt_rec.call_number,
1468 X_UNIT_SECTION_STATUS => l_c_usec_status,
1469 X_UNIT_SECTION_START_DATE => v_unit_offer_opt_rec.unit_section_start_date,
1470 X_UNIT_SECTION_END_DATE => v_unit_offer_opt_rec.unit_section_end_date,
1471 X_ENROLLMENT_ACTUAL => NULL,
1472 X_WAITLIST_ACTUAL => NULL,
1473 X_OFFERED_IND => v_unit_offer_opt_rec.offered_ind,
1474 X_STATE_FINANCIAL_AID => v_unit_offer_opt_rec.state_financial_aid,
1475 X_GRADING_SCHEMA_PRCDNCE_IND => v_unit_offer_opt_rec.grading_schema_prcdnce_ind,
1476 X_FEDERAL_FINANCIAL_AID => v_unit_offer_opt_rec.federal_financial_aid,
1477 X_UNIT_QUOTA => v_unit_offer_opt_rec.unit_quota,
1478 X_UNIT_QUOTA_RESERVED_PLACES => v_unit_offer_opt_rec.unit_quota_reserved_places,
1479 X_INSTITUTIONAL_FINANCIAL_AID => v_unit_offer_opt_rec.institutional_financial_aid,
1480 X_GRADING_SCHEMA_CD => v_unit_offer_opt_rec.grading_schema_cd,
1481 X_GS_VERSION_NUMBER => v_latest_gs_version,
1482 X_UNIT_CONTACT => v_unit_offer_opt_rec.unit_contact,
1483 X_MODE => 'R',
1484 X_ORG_ID => l_org_id,
1485 x_ss_enrol_ind => v_unit_offer_opt_rec.ss_enrol_ind,
1486 x_ss_display_ind => v_unit_offer_opt_rec.ss_display_ind, --Added by apelleti as per the DLD PSP001-US
1487 X_OWNER_ORG_UNIT_CD => v_unit_offer_opt_rec.owner_org_unit_cd, -- Added By Pradhakr as per DLD PSP001-US
1488 X_ATTENDANCE_REQUIRED_IND => v_unit_offer_opt_rec.attendance_required_ind,
1489 X_RESERVED_SEATING_ALLOWED => v_unit_offer_opt_rec.reserved_seating_allowed,
1490 X_SPECIAL_PERMISSION_IND => v_unit_offer_opt_rec.special_permission_ind,
1491 X_DIR_ENROLLMENT => v_unit_offer_opt_rec.dir_enrollment, -- The following three fields were added by Pradhakr
1492 X_ENR_FROM_WLST => v_unit_offer_opt_rec.enr_from_wlst, -- as part of Enrollment Build process (Enh.Bug# 1832130)
1493 X_INQ_NOT_WLST => v_unit_offer_opt_rec.inq_not_wlst,
1494 -- msrinivi 16 Aug,2001 : Added the following col according to bug 1882122
1495 x_rev_account_cd => v_unit_offer_opt_rec.rev_account_cd ,
1496 x_anon_unit_grading_ind => v_unit_offer_opt_rec.anon_unit_grading_ind ,
1497 x_anon_assess_grading_ind => v_unit_offer_opt_rec.anon_assess_grading_ind ,
1498 x_non_std_usec_ind => v_unit_offer_opt_rec.non_std_usec_ind,
1499 x_auditable_ind => v_unit_offer_opt_rec.auditable_ind,
1500 x_audit_permission_ind => v_unit_offer_opt_rec.audit_permission_ind,
1501 x_not_multiple_section_flag => v_unit_offer_opt_rec.not_multiple_section_flag,
1502 x_sup_uoo_id => NULL,
1503 x_relation_type => 'NONE',
1504 x_default_enroll_flag => v_unit_offer_opt_rec.default_enroll_flag,
1505 x_abort_flag => 'N'
1506 );
1507
1508 crspl_upd_usec_relation(v_unit_offer_opt_rec.uoo_id,v_new_uoo_id);
1509
1510 crspl_ins_unit_off_opt_note(
1511 v_unit_offer_opt_rec.uoo_id,
1512 v_new_uoo_id);
1513 crspl_ins_teach_resp_ovrd(
1514 v_unit_offer_opt_rec.unit_cd,
1515 v_unit_offer_opt_rec.version_number,
1516 v_unit_offer_opt_rec.cal_type,
1517 v_unit_offer_opt_rec.ci_sequence_number,
1518 v_unit_offer_opt_rec.location_cd,
1519 v_unit_offer_opt_rec.unit_class,
1520 v_new_uoo_id);
1521 EXCEPTION
1522 WHEN OTHERS THEN
1523 IF SQLCODE >= cst_lower_limit_err AND
1524 SQLCODE <= cst_upper_limit_err THEN
1525 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1526 ELSE
1527 IF (c_latest_gs_version%ISOPEN) THEN
1528 CLOSE c_latest_gs_version;
1529 END IF;
1530 App_Exception.Raise_Exception;
1531 END IF;
1532 END;
1533 END LOOP;
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 IF SQLCODE >= cst_lower_limit_err AND
1537 SQLCODE <= cst_upper_limit_err THEN
1538 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1539 ELSE
1540 IF (c_latest_gs_version%ISOPEN) THEN
1541 CLOSE c_latest_gs_version;
1542 END IF;
1543 App_Exception.Raise_Exception;
1544 END IF;
1545 END crspl_ins_unit_off_opt;
1546
1547 PROCEDURE crspl_ins_unit_assmnt_item (
1548 p_unit_cd IN IGS_AS_UNITASS_ITEM.unit_cd%TYPE,
1549 p_version_number IN IGS_AS_UNITASS_ITEM.version_number%TYPE,
1550 p_cal_type IN IGS_AS_UNITASS_ITEM.cal_type%TYPE,
1551 p_ci_sequence_number IN IGS_AS_UNITASS_ITEM.ci_sequence_number%TYPE )
1552 AS
1553 v_assessment_unit_rec IGS_AS_UNITASS_ITEM%ROWTYPE;
1554 v_new_sequence_number IGS_AS_UNITASS_ITEM.sequence_number%TYPE;
1555
1556 CURSOR cur_unit_ass_group(cp_unit_cd igs_as_unit_ai_grp.unit_cd%TYPE,
1557 cp_version_number igs_as_unit_ai_grp.version_number%TYPE,
1558 cp_cal_type igs_as_unit_ai_grp.cal_type%TYPE,
1559 cp_ci_sequence_number igs_as_unit_ai_grp.ci_sequence_number%TYPE) IS
1560 SELECT *
1561 FROM igs_as_unit_ai_grp
1562 WHERE unit_cd=cp_unit_cd
1563 AND version_number=cp_version_number
1564 AND cal_type = cp_cal_type
1565 AND ci_sequence_number=cp_ci_sequence_number;
1566
1567 CURSOR c_unit_assessment_item(cp_unit_ass_item_group_id igs_as_unitass_item.unit_ass_item_group_id%TYPE) IS
1568 SELECT *
1569 FROM IGS_AS_UNITASS_ITEM uai
1570 WHERE uai.unit_cd = p_unit_cd AND
1571 uai.version_number = p_version_number AND
1572 uai.cal_type = p_cal_type AND
1573 uai.ci_sequence_number = p_ci_sequence_number AND
1574 uai.unit_ass_item_group_id = cp_unit_ass_item_group_id AND
1575 uai.logical_delete_dt IS NULL;
1576 CURSOR cur_latest_gs_ver (cp_grad_schema_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE) IS
1577 SELECT max(gs.version_number) maxm
1578 FROM igs_as_grd_schema gs
1579 WHERE gs.grading_schema_cd = cp_grad_schema_cd;
1580
1581 l_message_name fnd_new_messages.message_name%TYPE;
1582 v_latest_gs_ver cur_latest_gs_ver%ROWTYPE;
1583 l_unit_ass_item_id igs_as_unitass_item_all.unit_ass_item_id%TYPE;
1584
1585 BEGIN
1586 -- Assigning initial values to local variables which were being initialised using DEFAULT
1587 -- clause.Done as part of bug #2563596 to remove GSCC warning.
1588 l_message_name := NULL;
1589
1590 FOR cur_unit_ass_group_rec IN cur_unit_ass_group(p_unit_cd,p_version_number,p_cal_type,p_ci_sequence_number) LOOP
1591 DECLARE
1592 l_rowid VARCHAR2(25);
1593 l_unit_ass_item_group_id NUMBER;
1594 BEGIN
1595 l_rowid :=NULL;
1596 l_unit_ass_item_group_id := NULL;
1597
1598 igs_as_unit_ai_grp_pkg.insert_row(
1599 x_rowid => l_rowid,
1600 x_unit_ass_item_group_id => l_unit_ass_item_group_id,
1601 x_unit_cd => p_new_unit_cd,
1602 x_version_number => p_new_version_number,
1603 x_cal_type => cur_unit_ass_group_rec.cal_type,
1604 x_ci_sequence_number => cur_unit_ass_group_rec.ci_sequence_number,
1605 x_group_name => cur_unit_ass_group_rec.group_name,
1606 x_midterm_formula_code => cur_unit_ass_group_rec.midterm_formula_code,
1607 x_midterm_formula_qty => cur_unit_ass_group_rec.midterm_formula_qty,
1608 x_midterm_weight_qty => cur_unit_ass_group_rec.midterm_weight_qty,
1609 x_final_formula_code => cur_unit_ass_group_rec.final_formula_code,
1610 x_final_formula_qty => cur_unit_ass_group_rec.final_formula_qty,
1611 x_final_weight_qty => cur_unit_ass_group_rec.final_weight_qty
1612 );
1613
1614
1615 FOR v_unit_assessment_item_rec IN c_unit_assessment_item(cur_unit_ass_group_rec.unit_ass_item_group_id) LOOP
1616 BEGIN
1617 --
1618 -- If grading schema is in the current or future,
1619 -- continue validation
1620 --
1621 l_message_name := NULL;
1622 OPEN cur_latest_gs_ver(v_unit_assessment_item_rec.grading_schema_cd);
1623 FETCH cur_latest_gs_ver INTO v_latest_gs_ver;
1624 CLOSE cur_latest_gs_ver;
1625 IF (IGS_AS_VAL_GSG.assp_val_gs_cur_fut(
1626 v_unit_assessment_item_rec.grading_schema_cd,
1627 v_latest_gs_ver.maxm,
1628 l_message_name) = TRUE) THEN
1629 --
1630 -- End of the latest version check of Grading Schema.
1631 --
1632 SELECT IGS_AS_UNITASS_ITEM_SEQ_NUM_S.nextval
1633 INTO v_new_sequence_number
1634 FROM dual;
1635 x_rowid := NULL;
1636 l_unit_ass_item_id := NULL;
1637
1638 IGS_AS_UNITASS_ITEM_PKG.INSERT_ROW(
1639 X_ROWID => x_rowid,
1640 X_UNIT_CD => p_new_unit_cd,
1641 X_VERSION_NUMBER => p_new_version_number,
1642 X_CAL_TYPE => v_unit_assessment_item_rec.cal_type,
1643 X_CI_SEQUENCE_NUMBER => v_unit_assessment_item_rec.ci_sequence_number,
1644 X_ASS_ID => v_unit_assessment_item_rec.ass_id,
1645 X_SEQUENCE_NUMBER => v_new_sequence_number,
1646 X_CI_START_DT => v_unit_assessment_item_rec.ci_start_dt,
1647 X_CI_END_DT => v_unit_assessment_item_rec.ci_end_dt,
1648 X_UNIT_CLASS => v_unit_assessment_item_rec.unit_class,
1649 X_UNIT_MODE => v_unit_assessment_item_rec.unit_mode,
1650 X_LOCATION_CD => v_unit_assessment_item_rec.location_cd,
1651 X_DUE_DT => v_unit_assessment_item_rec.due_dt,
1652 X_REFERENCE => v_unit_assessment_item_rec.reference,
1653 X_DFLT_ITEM_IND => v_unit_assessment_item_rec.dflt_item_ind,
1654 X_LOGICAL_DELETE_DT => v_unit_assessment_item_rec.logical_delete_dt,
1655 X_ACTION_DT => v_unit_assessment_item_rec.action_dt,
1656 X_EXAM_CAL_TYPE => v_unit_assessment_item_rec.exam_cal_type,
1657 X_EXAM_CI_SEQUENCE_NUMBER => v_unit_assessment_item_rec.exam_ci_sequence_number,
1658 X_MODE => 'R',
1659 X_ORG_ID => igs_ge_gen_003.get_org_id,
1660 X_GRADING_SCHEMA_CD => v_unit_assessment_item_rec.grading_schema_cd,
1661 X_GS_VERSION_NUMBER => v_unit_assessment_item_rec.gs_version_number,
1662 X_RELEASE_DATE => v_unit_assessment_item_rec.release_date,
1663 X_UNIT_ASS_ITEM_ID => l_unit_ass_item_id, --out parameter
1664 X_DESCRIPTION => v_unit_assessment_item_rec.description,
1665 x_unit_ass_item_group_id => l_unit_ass_item_group_id,
1666 x_midterm_mandatory_type_code => v_unit_assessment_item_rec.midterm_mandatory_type_code,
1667 x_midterm_weight_qty => v_unit_assessment_item_rec.midterm_weight_qty,
1668 x_final_mandatory_type_code => v_unit_assessment_item_rec.final_mandatory_type_code,
1669 x_final_weight_qty => v_unit_assessment_item_rec.final_weight_qty
1670 );
1671 END IF;
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 IF cur_latest_gs_ver%ISOPEN THEN
1675 CLOSE cur_latest_gs_ver;
1676 ELSIF SQLCODE >= cst_lower_limit_err AND
1677 SQLCODE <= cst_upper_limit_err THEN
1678 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1679 ELSE
1680 App_Exception.Raise_Exception;
1681 END IF;
1682 END;
1683 END LOOP;
1684
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 IF SQLCODE >= cst_lower_limit_err AND
1688 SQLCODE <= cst_upper_limit_err THEN
1689 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1690 ELSE
1691 App_Exception.Raise_Exception;
1692 END IF;
1693 END;
1694 END LOOP;
1695
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 IF SQLCODE >= cst_lower_limit_err AND
1700 SQLCODE <= cst_upper_limit_err THEN
1701 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1702 ELSE
1703 App_Exception.Raise_Exception;
1704 END IF;
1705 END crspl_ins_unit_assmnt_item;
1706 PROCEDURE crspl_ins_unit_off_pat_note(
1707 p_unit_cd IN IGS_PS_UNT_OFR_PAT_N.unit_cd%TYPE,
1708 p_version_number IN IGS_PS_UNT_OFR_PAT_N.version_number%TYPE,
1709 p_cal_type IN IGS_PS_UNT_OFR_PAT_N.cal_type%TYPE,
1710 p_ci_sequence_number IN IGS_PS_UNT_OFR_PAT_N.ci_sequence_number%TYPE )
1711 AS
1712 v_unit_offer_pat_note_rec IGS_PS_UNT_OFR_PAT_N%ROWTYPE;
1713 v_new_ref_number IGS_PS_UNT_OFR_PAT_N.reference_number%TYPE;
1714 --- The following cursor excludes notes records with NULL values in the
1715 --- note_text field as this implies that it contains data in the note_ole
1716 --- field which cannot be copied with the current product limitations.
1717 CURSOR c_unit_off_pat_note_rec IS
1718 SELECT *
1719 FROM IGS_PS_UNT_OFR_PAT_N uopn
1720 WHERE uopn.unit_cd = p_unit_cd AND
1721 uopn.version_number = p_version_number AND
1722 uopn.cal_type = p_cal_type AND
1723 uopn.ci_sequence_number = p_ci_sequence_number AND
1724 EXISTS (
1725 SELECT 'x'
1726 FROM IGS_GE_NOTE nte
1727 WHERE nte.reference_number = uopn.reference_number AND
1728 nte.note_text IS NOT NULL );
1729 BEGIN
1730 FOR v_unit_offer_pat_note_rec IN c_unit_off_pat_note_rec LOOP
1731 crspl_ins_duplicate_note(
1732 v_unit_offer_pat_note_rec.reference_number,
1733 v_new_ref_number);
1734 BEGIN
1735 x_rowid := NULL ;
1736 IGS_PS_UNT_OFR_PAT_N_PKG.INSERT_ROW(
1737 X_ROWID => x_rowid,
1738 X_UNIT_CD => p_new_unit_cd,
1739 X_REFERENCE_NUMBER => v_new_ref_number,
1740 X_VERSION_NUMBER => p_new_version_number,
1741 X_CAL_TYPE => v_unit_offer_pat_note_rec.cal_type,
1742 X_CI_SEQUENCE_NUMBER => v_unit_offer_pat_note_rec.ci_sequence_number,
1743 X_CRS_NOTE_TYPE => v_unit_offer_pat_note_rec.crs_note_type,
1744 X_MODE => 'R'
1745 );
1746 EXCEPTION
1747 WHEN OTHERS THEN
1748 IF SQLCODE >= cst_lower_limit_err AND
1749 SQLCODE <= cst_upper_limit_err THEN
1750 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1751 ELSE
1752 App_Exception.Raise_Exception;
1753 END IF;
1754 END;
1755 END LOOP;
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 IF SQLCODE >= cst_lower_limit_err AND
1759 SQLCODE <= cst_upper_limit_err THEN
1760 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1761 ELSE
1762 App_Exception.Raise_Exception;
1763 END IF;
1764 END crspl_ins_unit_off_pat_note;
1765
1766 /* Procedure : crspl_uofr_wlst_details
1767 * Purpose : To copy the waitlist details of the previous unit version offering to the new unit version that is being
1768 * created via the duplication record method.
1769 * Creation Date : 25 Aug 2000
1770 * Created By : Sreenivas.Bonam
1771 */
1772 PROCEDURE crspl_uofr_wlist_details(
1773 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
1774 p_version_number IN igs_ps_unit_ver.version_number%TYPE,
1775 p_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
1776 p_ci_sequence_number IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE)
1777 AS
1778
1779 CURSOR c_uofr_wlst_pri_det IS
1780 SELECT *
1781 FROM igs_ps_uofr_wlst_pri
1782 WHERE unit_cd = p_unit_cd
1783 AND version_number = p_version_number
1784 AND calender_type = p_cal_type
1785 AND ci_sequence_number= p_ci_sequence_number;
1786
1787 v_unit_ofr_wlist_pri_id igs_ps_uofr_wlst_pri.unit_ofr_waitlist_priority_id%TYPE;
1788
1789 CURSOR c_uofr_wlst_prf_det(cp_unit_ofr_wlst_priority_id igs_ps_uofr_wlst_prf.unit_ofr_waitlist_priority_id%TYPE) IS
1790 SELECT *
1791 FROM igs_ps_uofr_wlst_prf
1792 WHERE unit_ofr_waitlist_priority_id = cp_unit_ofr_wlst_priority_id;
1793 v_unit_ofr_wlist_prf_id igs_ps_uofr_wlst_prf.unit_ofr_waitlist_pref_id%TYPE;
1794
1795
1796 BEGIN
1797
1798 FOR c_uofr_wlst_pri_rec IN c_uofr_wlst_pri_det
1799 LOOP
1800 x_rowid := NULL;
1801 v_unit_ofr_wlist_pri_id := NULL;
1802 igs_ps_uofr_wlst_pri_pkg.Insert_Row(
1803 x_rowid => x_rowid,
1804 x_unit_ofr_wl_priority_id => v_unit_ofr_wlist_pri_id,
1805 x_unit_cd => p_new_unit_cd,
1806 x_version_number => p_new_version_number,
1807 x_calender_type => c_uofr_wlst_pri_rec.calender_type,
1808 x_ci_sequence_number => c_uofr_wlst_pri_rec.ci_sequence_number,
1809 x_priority_number => c_uofr_wlst_pri_rec.priority_number,
1810 x_priority_value => c_uofr_wlst_pri_rec.priority_value,
1811 X_MODE => 'R');
1812 FOR c_uofr_wlst_prf_rec IN c_uofr_wlst_prf_det(c_uofr_wlst_pri_rec.unit_ofr_waitlist_priority_id)
1813 LOOP
1814 x_rowid := NULL;
1815 v_unit_ofr_wlist_prf_id := NULL;
1816 igs_ps_uofr_wlst_prf_pkg.Insert_Row(
1817 x_rowid => x_rowid,
1818 x_unit_ofr_wl_pref_id => v_unit_ofr_wlist_prf_id,
1819 x_unit_ofr_wl_priority_id => v_unit_ofr_wlist_pri_id,
1820 x_preference_order => c_uofr_wlst_prf_rec.preference_order,
1821 x_preference_code => c_uofr_wlst_prf_rec.preference_code,
1822 x_preference_version => c_uofr_wlst_prf_rec.preference_version,
1823 X_MODE => 'R');
1824 END LOOP;
1825 END LOOP;
1826
1827 EXCEPTION
1828 WHEN OTHERS THEN
1829 IF SQLCODE >= cst_lower_limit_err AND
1830 SQLCODE <= cst_upper_limit_err THEN
1831 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1832 ELSE
1833 App_Exception.Raise_Exception;
1834 END IF;
1835 END crspl_uofr_wlist_details;
1836
1837 PROCEDURE crspl_ins_unit_off_pat (
1838 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
1839 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE,
1840 p_cal_type IN IGS_PS_UNIT_OFR.cal_type%TYPE )
1841 AS
1842 v_unit_offer_pat_rec IGS_PS_UNIT_OFR_PAT%ROWTYPE;
1843 --- This cursor is used to select the IGS_PS_UNIT offering pattern with the latest
1844 --- calendar instance (the join between the two tables is based on having
1845 --- similar IGS_CA_TYPE).
1846 CURSOR c_unit_offer_pat_rec IS
1847 SELECT *
1848 FROM IGS_PS_UNIT_OFR_PAT UOP
1849 WHERE UOP.unit_cd = p_unit_cd AND
1850 UOP.version_number = p_version_number AND
1851 UOP.cal_type = p_cal_type AND
1852 UOP.delete_flag = 'N' AND
1853 UOP.ci_end_dt = (
1854 SELECT MAX(ci_end_dt)
1855 FROM IGS_PS_UNIT_OFR_PAT UOP2
1856 WHERE UOP2.unit_cd = UOP.unit_cd AND
1857 UOP2.version_number = UOP.version_number AND
1858 UOP2.delete_flag = 'N' AND
1859 UOP2.cal_type = UOP.cal_type)
1860 ORDER BY UOP.ci_end_dt DESC, UOP.ci_start_dt DESC;
1861
1862 l_org_id NUMBER(15);
1863
1864 BEGIN
1865 OPEN c_unit_offer_pat_rec;
1866 FETCH c_unit_offer_pat_rec INTO v_unit_offer_pat_rec;
1867 --- If the record cannot be found, then exit.
1868 IF c_unit_offer_pat_rec%NOTFOUND THEN
1869 CLOSE c_unit_offer_pat_rec;
1870 RETURN;
1871 END IF;
1872 CLOSE c_unit_offer_pat_rec;
1873 x_rowid := NULL;
1874
1875 l_org_id := igs_ge_gen_003.get_org_id;
1876
1877 IGS_PS_UNIT_OFR_PAT_PKG.INSERT_ROW(
1878 X_ROWID => x_rowid,
1879 X_UNIT_CD => p_new_unit_cd,
1880 X_VERSION_NUMBER => p_new_version_number,
1881 X_CI_SEQUENCE_NUMBER => v_unit_offer_pat_rec.ci_sequence_number,
1882 X_CAL_TYPE => v_unit_offer_pat_rec.cal_type,
1883 X_CI_START_DT => v_unit_offer_pat_rec.ci_start_dt,
1884 X_CI_END_DT => v_unit_offer_pat_rec.ci_end_dt,
1885 X_WAITLIST_ALLOWED => v_unit_offer_pat_rec.waitlist_allowed,
1886 X_MAX_STUDENTS_PER_WAITLIST => v_unit_offer_pat_rec.max_students_per_waitlist,
1887 X_MODE => 'R',
1888 X_ORG_ID => l_org_id,
1889 X_DELETE_FLAG => v_unit_offer_pat_rec.delete_flag ,
1890 x_abort_flag => 'N'
1891 );
1892 --- Create the relevant notes for this IGS_PS_UNIT offering pattern.
1893 crspl_ins_unit_off_pat_note(
1894 p_unit_cd,
1895 p_version_number,
1896 v_unit_offer_pat_rec.cal_type,
1897 v_unit_offer_pat_rec.ci_sequence_number );
1898 --- Check if the IGS_PS_UNIT_OFR_OPT exists for the old IGS_PS_UNIT code and
1899 --- version number. If it does exist, create the new record with the
1900 --- substituted values.
1901 crspl_ins_unit_off_opt(
1902 p_unit_cd,
1903 p_version_number,
1904 v_unit_offer_pat_rec.cal_type,
1905 v_unit_offer_pat_rec.ci_sequence_number );
1906 --- Check if the unit_asessment_item exists for the old IGS_PS_UNIT code and
1907 --- version number. If it does exist, create the new record with the
1908 --- substituted values.
1909 crspl_ins_unit_assmnt_item(
1910 p_unit_cd,
1911 p_version_number,
1912 v_unit_offer_pat_rec.cal_type,
1913 v_unit_offer_pat_rec.ci_sequence_number );
1914 --Check if the unit offering pattern waitlist exists then roll the data
1915 crspl_uofr_wlist_details( p_unit_cd,
1916 p_version_number,
1917 v_unit_offer_pat_rec.cal_type,
1918 v_unit_offer_pat_rec.ci_sequence_number);
1919
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922 IF SQLCODE >= cst_lower_limit_err AND
1923 SQLCODE <= cst_upper_limit_err THEN
1924 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1925 ELSE
1926 IF (c_unit_offer_pat_rec%ISOPEN) THEN
1927 CLOSE c_unit_offer_pat_rec;
1928 END IF;
1929 App_Exception.Raise_Exception;
1930 END IF;
1931 END crspl_ins_unit_off_pat;
1932 PROCEDURE crspl_ins_unit_offer (
1933 p_unit_cd IN IGS_PS_UNIT_VER.unit_cd%TYPE,
1934 p_version_number IN IGS_PS_UNIT_VER.version_number%TYPE )
1935 AS
1936 v_unit_offer_rec IGS_PS_UNIT_OFR%ROWTYPE;
1937 CURSOR c_unit_offer_rec IS
1938 SELECT *
1939 FROM IGS_PS_UNIT_OFR
1940 WHERE unit_cd = p_unit_cd AND
1941 version_number = p_version_number;
1942 BEGIN
1943 FOR v_unit_offer_rec IN c_unit_offer_rec LOOP
1944 BEGIN
1945 x_rowid := NULL;
1946 IGS_PS_UNIT_OFR_PKG.INSERT_ROW(
1947 X_ROWID => x_rowid,
1948 X_UNIT_CD => p_new_unit_cd,
1949 X_VERSION_NUMBER => p_new_version_number,
1950 X_CAL_TYPE => v_unit_offer_rec.cal_type,
1951 X_MODE => 'R'
1952 );
1953 --- Check if the IGS_PS_UNIT_OFR_PAT exists for the old IGS_PS_UNIT code and
1954 --- version number. If it does exist, create the new record with the
1955 --- substituted values.
1956 crspl_ins_unit_off_pat(
1957 p_unit_cd,
1958 p_version_number,
1959 v_unit_offer_rec.cal_type);
1960 EXCEPTION
1961 WHEN OTHERS THEN
1962 IF SQLCODE >= cst_lower_limit_err AND
1963 SQLCODE <= cst_upper_limit_err THEN
1964 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1965 ELSE
1966 App_Exception.Raise_Exception;
1967 END IF;
1968 END;
1969 END LOOP;
1970 EXCEPTION
1971 WHEN OTHERS THEN
1972 IF SQLCODE >= cst_lower_limit_err AND
1973 SQLCODE <= cst_upper_limit_err THEN
1974 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
1975 ELSE
1976 App_Exception.Raise_Exception;
1977 END IF;
1978 END crspl_ins_unit_offer;
1979
1980
1981 /* Procedure : crspl_ins_location_details
1982 * Purpose : To copy the location details of the previous unit version to the new unit version that is being
1983 * created via the duplication record method.
1984 * Creation Date : 25 Aug 2000
1985 * Created By : Sreenivas.Bonam
1986 */
1987 PROCEDURE crspl_ins_location_details(
1988 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
1989 p_version_number IN igs_ps_unit_ver.version_number%TYPE )
1990 AS
1991 CURSOR c_unit_loc_det IS
1992 SELECT *
1993 FROM igs_ps_unit_location
1994 WHERE unit_code = p_unit_cd
1995 AND unit_version_number = p_version_number;
1996 v_unit_location_id igs_ps_unit_location.unit_location_id%TYPE;
1997 BEGIN
1998
1999 FOR c_unit_loc_rec IN c_unit_loc_det
2000 LOOP
2001 x_rowid := NULL;
2002 igs_ps_unit_location_pkg.Insert_Row(
2003 x_rowid => x_rowid,
2004 x_unit_location_id => v_unit_location_id,
2005 x_unit_code => p_new_unit_cd,
2006 x_unit_version_number => p_new_version_number,
2007 x_location_code => c_unit_loc_rec.location_code,
2008 x_building_id => c_unit_loc_rec.building_id,
2009 x_room_id => c_unit_loc_rec.room_id,
2010 X_MODE => 'R');
2011 END LOOP;
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 IF SQLCODE >= cst_lower_limit_err AND
2016 SQLCODE <= cst_upper_limit_err THEN
2017 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
2018 ELSE
2019 App_Exception.Raise_Exception;
2020 END IF;
2021 END crspl_ins_location_details;
2022
2023 /* Procedure : crspl_ins_facility_details
2024 * Purpose : To copy the facility details of the previous unit version to the new unit version that is being
2025 * created via the duplication record method.
2026 * Creation Date : 25 Aug 2000
2027 * Created By : Sreenivas.Bonam
2028 */
2029 PROCEDURE crspl_ins_facility_details(
2030 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
2031 p_version_number IN igs_ps_unit_ver.version_number%TYPE )
2032 AS
2033 CURSOR c_unit_fac_det IS
2034 SELECT *
2035 FROM igs_ps_unit_facility
2036 WHERE unit_code = p_unit_cd
2037 AND unit_version_number = p_version_number;
2038 v_unit_media_id igs_ps_unit_facility.unit_media_id%TYPE;
2039 BEGIN
2040
2041 FOR c_unit_fac_rec IN c_unit_fac_det
2042 LOOP
2043 x_rowid := NULL;
2044 igs_ps_unit_facility_pkg.Insert_Row(
2045 x_rowid => x_rowid,
2046 x_unit_media_id => v_unit_media_id,
2047 x_unit_code => p_new_unit_cd,
2048 x_unit_version_number => p_new_version_number,
2049 x_media_code => c_unit_fac_rec.media_code,
2050 X_MODE => 'R');
2051 END LOOP;
2052
2053 EXCEPTION
2054 WHEN OTHERS THEN
2055 IF SQLCODE >= cst_lower_limit_err AND
2056 SQLCODE <= cst_upper_limit_err THEN
2057 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
2058 ELSE
2059 App_Exception.Raise_Exception;
2060 END IF;
2061 END crspl_ins_facility_details;
2062
2063 /* Procedure : crspl_ins_cros_ref_details
2064 * Purpose : To copy the cros reference details of the previous unit version to the new unit version that is being
2065 * created via the duplication record method.
2066 * Creation Date : 25 Aug 2000
2067 * Created By : Sreenivas.Bonam
2068 */
2069 PROCEDURE crspl_ins_cros_ref_details(
2070 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
2071 p_version_number IN igs_ps_unit_ver.version_number%TYPE )
2072 AS
2073 CURSOR c_unit_cros_ref_det IS
2074 SELECT *
2075 FROM igs_ps_unit_cros_ref
2076 WHERE parent_unit_code = p_unit_cd
2077 AND parent_unit_version_number = p_version_number;
2078 v_unit_cross_reference_id igs_ps_unit_cros_ref.unit_cross_reference_id%TYPE;
2079 BEGIN
2080
2081 FOR c_unit_cros_ref_rec IN c_unit_cros_ref_det
2082 LOOP
2083 x_rowid := NULL;
2084 igs_ps_unit_cros_ref_pkg.Insert_Row(
2085 x_rowid => x_rowid,
2086 x_unit_cross_reference_id => v_unit_cross_reference_id,
2087 x_parent_unit_code => p_new_unit_cd,
2088 x_parent_unit_version_number => p_new_version_number,
2089 x_child_unit_code => c_unit_cros_ref_rec.child_unit_code,
2090 x_child_unit_version_number => c_unit_cros_ref_rec.child_unit_version_number,
2091 X_MODE => 'R');
2092 END LOOP;
2093
2094 EXCEPTION
2095 WHEN OTHERS THEN
2096 IF SQLCODE >= cst_lower_limit_err AND
2097 SQLCODE <= cst_upper_limit_err THEN
2098 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
2099 ELSE
2100 App_Exception.Raise_Exception;
2101 END IF;
2102 END crspl_ins_cros_ref_details;
2103
2104
2105 /* Procedure : crspl_ins_grd_schm_details
2106 * Purpose : To copy the grading schema details of the previous unit version to the new unit version that is being
2107 * created via the duplication record method.
2108 * Creation Date : 25 Aug 2000
2109 * Created By : Sreenivas.Bonam
2110 */
2111 PROCEDURE crspl_ins_grd_schm_details(
2112 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
2113 p_version_number IN igs_ps_unit_ver.version_number%TYPE )
2114 AS
2115 CURSOR c_unit_grd_schm_det IS
2116 SELECT *
2117 FROM igs_ps_unit_grd_schm
2118 WHERE unit_code = p_unit_cd
2119 AND unit_version_number = p_version_number;
2120 v_unit_grading_schema_id igs_ps_unit_grd_schm.unit_grading_schema_id%TYPE;
2121 BEGIN
2122
2123 FOR c_unit_grd_schm_rec IN c_unit_grd_schm_det
2124 LOOP
2125 x_rowid := NULL;
2126 igs_ps_unit_grd_schm_pkg.Insert_Row(
2127 x_rowid => x_rowid,
2128 x_unit_grading_schema_id => v_unit_grading_schema_id,
2129 x_unit_code => p_new_unit_cd,
2130 x_unit_version_number => p_new_version_number,
2131 x_grading_schema_code => c_unit_grd_schm_rec.grading_schema_code,
2132 x_grd_schm_version_number => c_unit_grd_schm_rec.grd_schm_version_number,
2133 x_default_flag => c_unit_grd_schm_rec.default_flag,
2134 X_MODE => 'R');
2135 END LOOP;
2136
2137 EXCEPTION
2138 WHEN OTHERS THEN
2139 IF SQLCODE >= cst_lower_limit_err AND
2140 SQLCODE <= cst_upper_limit_err THEN
2141 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
2142 ELSE
2143 App_Exception.Raise_Exception;
2144 END IF;
2145 END crspl_ins_grd_schm_details;
2146
2147
2148 /* Procedure : crspl_ins_unt_fld_details
2149 * Purpose : To copy the field of Study details of the previous unit version to the new unit version that is being
2150 * created via the duplication record method.
2151 * Creation Date : 25 Aug 2000
2152 * Created By : Sreenivas.Bonam
2153 */
2154 PROCEDURE crspl_ins_unt_fld_details(
2155 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
2156 p_version_number IN igs_ps_unit_ver.version_number%TYPE )
2157 AS
2158 CURSOR c_unit_fld_stdy_det IS
2159 SELECT *
2160 FROM igs_ps_unit_fld_stdy
2161 WHERE unit_code = p_unit_cd
2162 AND version_number = p_version_number;
2163 v_unit_field_of_study_id igs_ps_unit_fld_stdy.unit_field_of_study_id%TYPE;
2164 BEGIN
2165
2166 FOR c_unit_fld_stdy_rec IN c_unit_fld_stdy_det
2167 LOOP
2168 x_rowid := NULL;
2169 igs_ps_unit_fld_stdy_pkg.Insert_Row(
2170 x_rowid => x_rowid,
2171 x_unit_field_of_study_id => v_unit_field_of_study_id,
2172 x_unit_code => p_new_unit_cd,
2173 x_version_number => p_new_version_number,
2174 x_field_of_study => c_unit_fld_stdy_rec.field_of_study,
2175 X_MODE => 'R');
2176 END LOOP;
2177
2178 EXCEPTION
2179 WHEN OTHERS THEN
2180 IF SQLCODE >= cst_lower_limit_err AND
2181 SQLCODE <= cst_upper_limit_err THEN
2182 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
2183 ELSE
2184 App_Exception.Raise_Exception;
2185 END IF;
2186 END crspl_ins_unt_fld_details;
2187
2188 --removed the procedure CRSP_INS_UNIT_REVSEG by vvutukur as part of enh#2831572.
2189
2190 /* Procedure : crsp_ins_appr_ass_itm_grd
2191 * Purpose : To roll the Approved Assessment Item Grading Schemas
2192 * for the Unit from its existing unit version number
2193 * to next new version number.
2194 *
2195 * Creation Date : 03 Jan 2002
2196 * Created By : Nishikant
2197 */
2198 PROCEDURE crsp_ins_appr_ass_itm_grd(
2199 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
2200 p_version_number IN igs_ps_unit_ver.version_number%TYPE
2201 )
2202 AS
2203 CURSOR c_appr_grd_sch IS
2204 SELECT *
2205 FROM igs_as_appr_grd_sch
2206 WHERE unit_cd = p_unit_cd AND
2207 version_number = p_version_number AND
2208 closed_ind = 'N';
2209 CURSOR c_max_ver_grd_sch(
2210 p_grading_schema_cd igs_as_appr_grd_sch.grading_schema_cd%TYPE)
2211 IS
2212 SELECT MAX(version_number)
2213 FROM igs_as_grd_schema
2214 WHERE grading_schema_cd = p_grading_schema_cd;
2215 l_appr_grd_sch igs_as_appr_grd_sch%ROWTYPE;
2216 l_max_ver_grd_sch igs_as_grd_schema.version_number%TYPE;
2217 l_message_name fnd_new_messages.message_name%TYPE;
2218 l_rowid VARCHAR(30);
2219
2220 BEGIN
2221 FOR l_appr_grd_sch IN c_appr_grd_sch
2222 LOOP
2223 OPEN c_max_ver_grd_sch(l_appr_grd_sch.grading_schema_cd);
2224 FETCH c_max_ver_grd_sch INTO l_max_ver_grd_sch;
2225 CLOSE c_max_ver_grd_sch;
2226 -- The function assp_val_gs_cur_fut checks for the new version of
2227 -- the Grading Schema is current or future and retrns a boolean value.
2228 -- When call to the following function returns TRUE it inserts the
2229 -- Approved Grading Schema with the new Unit Version and new Grading
2230 -- Schema Version if available. Otherwise it should not insert the
2231 -- Grading Schema for the Unit Version.
2232 IF igs_as_val_gsg.assp_val_gs_cur_fut(
2233 l_appr_grd_sch.grading_schema_cd,
2234 l_max_ver_grd_sch,
2235 l_message_name) THEN
2236 l_rowid := NULL;
2237 igs_as_appr_grd_sch_pkg.insert_row(
2238 x_rowid => l_rowid,
2239 x_unit_cd => p_new_unit_cd,
2240 x_version_number => p_new_version_number,
2241 x_assessment_type => l_appr_grd_sch.assessment_type,
2242 x_grading_schema_cd => l_appr_grd_sch.grading_schema_cd,
2243 x_gs_version_number => l_max_ver_grd_sch,
2244 x_default_ind => l_appr_grd_sch.default_ind,
2245 x_closed_ind => 'N',
2246 x_mode => 'R' );
2247 END IF;
2248 END LOOP;
2249 EXCEPTION
2250 WHEN OTHERS THEN
2251 IF SQLCODE >= cst_lower_limit_err AND
2252 SQLCODE <= cst_upper_limit_err THEN
2253 p_message_name := 'IGS_PS_FAIL_COPY_PRGVERDETAIL';
2254 ELSE
2255 App_Exception.Raise_Exception;
2256 END IF;
2257 END crsp_ins_appr_ass_itm_grd;
2258
2259 BEGIN
2260 --- The purpose of this procedure is to perform a rollover function by
2261 --- duplicating the details from one IGS_PS_UNIT code / version number combination
2262 --- to a new IGS_PS_UNIT code / version number combination. Each local procedure
2263 --- handles the duplication of data for a table (or group of related tables).
2264 --- Each local procedure contains an anonymous block around the insert
2265 --- statement. This is to trap 'acceptable' errors within the defined range
2266 --- and to continue. This is also the case for the exception handling area
2267 --- for the whole procedure. Errors outside of this are handled in the usual
2268 --- way by GENP_LOG_ERROR().
2269
2270 -- Assigning initial values to local variables which were being initialised using DEFAULT
2271 -- clause.Done as part of bug #2563596 to remove GSCC warning.
2272 cst_upper_limit_err := -20000;
2273 cst_lower_limit_err := -20999;
2274
2275 DECLARE
2276
2277 CURSOR c_new_unit_vers_rec (
2278 cp_new_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
2279 cp_new_version_number IGS_PS_UNIT_VER.version_number%TYPE ) IS
2280 SELECT 'x'
2281 FROM IGS_PS_UNIT_VER
2282 WHERE unit_cd = cp_new_unit_cd AND
2283 version_number = cp_new_version_number;
2284 CURSOR c_old_unit_vers_rec (
2285 cp_old_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
2286 cp_old_version_number IGS_PS_UNIT_VER.version_number%TYPE ) IS
2287 SELECT 'x'
2288 FROM IGS_PS_UNIT_VER
2289 WHERE unit_cd = cp_old_unit_cd AND
2290 version_number = cp_old_version_number;
2291 l_status NUMBER;
2292
2293 BEGIN
2294 --- Set default message number
2295 p_message_name := 'IGS_PS_SUCCESS_COPY_PRG_VER';
2296 -- Check if the new IGS_PS_UNIT version exists
2297 OPEN c_new_unit_vers_rec(
2298 p_new_unit_cd,
2299 p_new_version_number);
2300 FETCH c_new_unit_vers_rec INTO gv_unit_version_exist;
2301 IF c_new_unit_vers_rec%NOTFOUND THEN
2302 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
2303 CLOSE c_new_unit_vers_rec;
2304 RETURN;
2305 END IF;
2306 CLOSE c_new_unit_vers_rec;
2307 --- Check if the old IGS_PS_UNIT version exists
2308 OPEN c_old_unit_vers_rec(
2309 p_old_unit_cd,
2310 p_old_version_number);
2311 FETCH c_old_unit_vers_rec INTO gv_unit_version_exist;
2312 IF c_old_unit_vers_rec%NOTFOUND THEN
2313 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
2314 CLOSE c_old_unit_vers_rec;
2315 RETURN;
2316 END IF;
2317 CLOSE c_old_unit_vers_rec;
2318 --- Check if the IGS_PS_UNIT_VER_NOTE record exists for the old IGS_PS_UNIT code and
2319 --- version number. If it does exist, create the new record with the
2320 --- substituted values. A new IGS_GE_NOTE record must be created as well.
2321 crspl_ins_unit_ver_note(
2322 p_old_unit_cd,
2323 p_old_version_number);
2324 --- Check if the IGS_PS_TCH_RESP record exists for the old IGS_PS_UNIT code
2325 --- and version number. If it does exist, create the new record with the
2326 --- substituted values.
2327 crspl_ins_teach_resp(
2328 p_old_unit_cd,
2329 p_old_version_number);
2330 --- Check if the IGS_PS_UNIT_DSCP record exists for the old IGS_PS_UNIT code and
2331 --- version number. If it does exist, create the new record with the
2332 --- substituted values.
2333 crspl_ins_unit_discipline(
2334 p_old_unit_cd,
2335 p_old_version_number);
2336 --- Check if the IGS_PS_UNIT_CATEGORY record exists for the old IGS_PS_UNIT code and
2337 --- version number. If it does exist, create the new record with the
2338 --- substituted values.
2339 crspl_ins_unit_categorisation(
2340 p_old_unit_cd,
2341 p_old_version_number);
2342 --- Check if the IGS_PS_UNIT_LVL record exists for the old IGS_PS_UNIT code and
2343 --- version number. If it does exist, create the new record with the
2344 --- substituted values.
2345 crspl_ins_crs_unit_lvl(
2346 p_old_unit_cd,
2347 p_old_version_number);
2348 --- Check if the IGS_PS_UNIT_REF_CD exists for the old IGS_PS_UNIT code and version
2349 --- number. If it does exist, create the new record with the substituted
2350 --- values.
2351 crspl_ins_unit_ref_cd(
2352 p_old_unit_cd,
2353 p_old_version_number);
2354 --- Check if the IGS_PS_UNIT_OFR exists for the old IGS_PS_UNIT code and version
2355 --- number. If it does exist, create the new record with the substituted
2356 --- values.
2357 crspl_ins_unit_offer(
2358 p_old_unit_cd,
2359 p_old_version_number);
2360 --- Check if the IGS_PS_UNIT_OFR_NOTE record exists for the old IGS_PS_UNIT code and
2361 --- version number. If it does exist, create the new record with the
2362 --- substituted values. A new IGS_GE_NOTE record must be created as well.
2363 crspl_ins_unit_offer_note(
2364 p_old_unit_cd,
2365 p_old_version_number);
2366
2367 -- The following calls have been added to copy details related to new forms added in 11.5
2368 -- The code for each of these procedures has been declared above locally
2369 crspl_ins_location_details(
2370 p_old_unit_cd,
2371 p_old_version_number);
2372 crspl_ins_facility_details(
2373 p_old_unit_cd,
2374 p_old_version_number);
2375 crspl_ins_cros_ref_details(
2376 p_old_unit_cd,
2377 p_old_version_number);
2378 crspl_ins_grd_schm_details(
2379 p_old_unit_cd,
2380 p_old_version_number);
2381 crspl_ins_unt_fld_details(
2382 p_old_unit_cd,
2383 p_old_version_number);
2384 --Added by manu according to bug # 1882122, 1 Aug, 2001
2385 --removed the call to crsp_ins_unit_revseg by vvutukur as part of enh#2831572.
2386 -- Added by Nishikant due to the enhancement bug#2162831
2387 crsp_ins_appr_ass_itm_grd(
2388 p_old_unit_cd,
2389 p_old_version_number);
2390
2391 -- Added by Ayedubat as part of the HESA Integration DLD ( Eh Bug # 2201753)
2392
2393 -- Check the Profile value of Country Code for OSS.
2394
2395 IF fnd_profile.value('OSS_COUNTRY_CODE') = 'GB' THEN
2396
2397 -- Call the Procedure to copy the HESA related information old Unit to the New Unit
2398 IGS_HE_UV_PKG.COPY_UNIT_VERSION
2399 (p_old_unit_cd,
2400 p_old_version_number,
2401 p_new_unit_cd,
2402 p_new_version_number,
2403 l_status,
2404 p_message_name) ;
2405
2406 -- If the Procedure has returned an error , then display the error message and abort the process
2407 IF l_status = 2 THEN -- ie. The procedure call has resulted in error.
2408
2409 fnd_message.set_name('IGS', p_message_name);
2410 IGS_GE_MSG_STACK.ADD;
2411 app_exception.raise_exception;
2412
2413 END IF;
2414
2415 END IF;
2416 -- End of the code added by ayedubat
2417
2418 EXCEPTION
2419 WHEN OTHERS THEN
2420 IF (c_new_unit_vers_rec%ISOPEN) THEN
2421 CLOSE c_new_unit_vers_rec;
2422 END IF;
2423 IF (c_old_unit_vers_rec%ISOPEN) THEN
2424 CLOSE c_old_unit_vers_rec;
2425 END IF;
2426 App_Exception.Raise_Exception;
2427 END;
2428 EXCEPTION
2429 WHEN OTHERS THEN
2430 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2431 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_008.crsp_ins_unit_ver');
2432 IGS_GE_MSG_STACK.ADD;
2433 App_Exception.Raise_Exception;
2434 END crsp_ins_unit_ver;
2435
2436
2437
2438 FUNCTION CRSP_INS_UOP_UOO(
2439 p_unit_cd IN VARCHAR2 ,
2440 p_version_number IN NUMBER ,
2441 p_cal_type IN VARCHAR2 ,
2442 p_source_ci_sequence_number IN NUMBER ,
2443 p_dest_ci_sequence_number IN NUMBER ,
2444 p_source_cal_type IN VARCHAR2,
2445 p_message_name OUT NOCOPY VARCHAR2,
2446 p_log_creation_date DATE )
2447 RETURN BOOLEAN AS
2448
2449 -------------------------------------------------------------------------------------------------------------------------------------
2450 --Change History:
2451 --Who When What
2452 --sarakshi 17-Oct-2005 Bug#4657596, added fnd logging
2453 --sarakshi 14-oct-2004 Bug#3945817, passsing unit section status as mentioned in the bug.
2454 --sarakshi 14-Sep-2004 Enh#3888835, added cursor c_teach_date and it's related logic.
2455 --sarakshi 31-AUG-2004 Bug#3864738,passed unit_section_status as OPEN in the insert row call of IGS_PS_UNIT_OFR_OPT
2456 --sarakshi 12-Jul-2004 Bug#3729462, Added the predicate DELETE_FLAG='N' to the cursor c_uop_dest_rec
2457 --sarakshi 02-Jun-2004 Bug#3655650,modified cursor c_uai_source_rec and its usage, also added logic to rollover the unit assessment items groups
2458 --sarakshi 13-Apr-2004 Bug#3555871, removed the logic of getting the call number for AUTO profile option.
2459 --sarakshi 23-sep-2003 ENh#3052452,created local procedure crspl_upd_usec_relation and the call of the same .Also added column sup_uoo_id,relation_type,default_enroll_flag to the call of igs_ps_unit_ofr_opt_pkg.insert_row
2460 --sarakhsi 29-Aug-2003 Bug#3076021,shifted teh local function crspl_ins_teach_resp_ovrd to IGSPS01B.pls(only logic),also set the value of unit_section_status,unit section start_dt and unit_section_end_dt appropriately
2461 --vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified call to igs_ps_unit_ofr_opt_pkg.insert_row to include new parameter not_multiple_section_flag.
2462 --sarakshi 18-Apr-2003 Bug#2910695,passed null to actual_enrollment,actual_waitlist to IGS_PS_UNIT_OFR_OPT_ALL
2463 --sarakshi 05-Mar-2003 Bug#2768783,replaced the local function call check_call_number with
2464 -- igs_ps_unit_ofr_opt_pkg.check_call_number.Also coded logic for validating/generating call_number
2465 --sarakshi 18-sep-2002 changed the variable name p_message_name to l_message_name,bug#2563596
2466 --sarakshi 06-Jun-2002 Local procedure to handle the exception condition ,bug#2332807
2467 -------------------------------------------------------------------------------------------------------------------------------------
2468
2469
2470 --This cursor added by sarakshi,bug#2332807
2471 CURSOR cur_check(cp_creation_dt igs_ge_s_log_entry.creation_dt%TYPE) IS
2472 SELECT 'X'
2473 FROM igs_ge_s_log_entry
2474 WHERE s_log_type='USEC-ROLL'
2475 AND creation_dt=cp_creation_dt;
2476
2477
2478 --This cursor added by jbegum for Bug#2563596
2479 CURSOR cur_check_log(cp_creation_dt igs_ge_s_log.creation_dt%TYPE) IS
2480 SELECT rowid
2481 FROM igs_ge_s_log
2482 WHERE s_log_type='USEC-ROLL'
2483 AND creation_dt=cp_creation_dt;
2484
2485 l_var VARCHAR2(1);
2486 l_var_log cur_check_log%ROWTYPE;
2487 l_rowid VARCHAR2(25);
2488 x_rowid VARCHAR2(25);
2489 l_old_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
2490 v_new_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
2491
2492
2493 BEGIN -- crsp_ins_uop_uoo
2494 -- Copy the IGS_PS_UNIT offering options and IGS_PS_UNIT assessment patterns from
2495 -- the source IGS_PS_UNIT offering pattern to the destination IGS_PS_UNIT offering pattern.
2496 DECLARE
2497 cst_exam CONSTANT VARCHAR2(4) := 'EXAM';
2498 v_uoo_inserted_cnt NUMBER(4);
2499 v_uai_inserted_cnt NUMBER(4);
2500 v_uai_fetched_cnt NUMBER(4);
2501 v_message_name VARCHAR2(30);
2502 v_uop_exists VARCHAR2(1);
2503 v_uoo_error BOOLEAN ;
2504 v_uai_error BOOLEAN ;
2505 v_tro_recs_skipped BOOLEAN ;
2506 v_uai_continue BOOLEAN ;
2507 v_ret_val BOOLEAN ;
2508 v_successful_pattern_mbr BOOLEAN ;
2509 v_exam_cal_type igs_ca_inst.cal_type%TYPE;
2510 v_exam_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
2511 v_latest_gs_version igs_as_grd_schema.version_number%TYPE;
2512 v_unit_contact igs_ps_unit_ofr_opt.unit_contact%TYPE;
2513 v_assessment_type igs_as_assessmnt_typ.assessment_type%TYPE;
2514 v_reference igs_as_unitass_item.reference%TYPE;
2515 v_uai_seq_num igs_as_unitass_item.sequence_number%TYPE;
2516 l_org_id NUMBER(15);
2517 l_unit_ass_item_id igs_as_unitass_item_all.unit_ass_item_id%TYPE;
2518
2519
2520 CURSOR c_uop_dest_rec IS
2521 SELECT 'x'
2522 FROM igs_ps_unit_ofr_pat uop
2523 WHERE uop.unit_cd = p_unit_cd
2524 AND uop.version_number = p_version_number
2525 AND uop.cal_type = p_cal_type
2526 AND uop.ci_sequence_number = p_dest_ci_sequence_number
2527 AND uop.delete_flag = 'N';
2528
2529 CURSOR c_uoo_source_rec IS
2530 SELECT *
2531 FROM igs_ps_unit_ofr_opt uoo
2532 WHERE uoo.unit_cd = p_unit_cd
2533 AND uoo.version_number = p_version_number
2534 AND uoo.cal_type = p_source_cal_type
2535 AND uoo.ci_sequence_number = p_source_ci_sequence_number;
2536 v_uoo_rec c_uoo_source_rec%ROWTYPE;
2537
2538 CURSOR c_latest_gs_version (cp_grad_schema_cd igs_as_grd_schema.grading_schema_cd%TYPE) IS
2539 SELECT MAX(gs.version_number)
2540 FROM igs_as_grd_schema gs
2541 WHERE gs.grading_schema_cd = cp_grad_schema_cd;
2542
2543
2544 CURSOR cur_unit_ass_group(cp_unit_cd igs_as_unit_ai_grp.unit_cd%TYPE,
2545 cp_version_number igs_as_unit_ai_grp.version_number%TYPE,
2546 cp_cal_type igs_as_unit_ai_grp.cal_type%TYPE,
2547 cp_ci_sequence_number igs_as_unit_ai_grp.ci_sequence_number%TYPE) IS
2548 SELECT *
2549 FROM igs_as_unit_ai_grp
2550 WHERE unit_cd=cp_unit_cd
2551 AND version_number=cp_version_number
2552 AND cal_type = cp_cal_type
2553 AND ci_sequence_number=cp_ci_sequence_number;
2554
2555 -- Only undeleted record to be selected
2556 CURSOR c_uai_source_rec(
2557 cp_unit_cd igs_ps_unit_ofr_pat.unit_cd%TYPE,
2558 cp_version_number igs_ps_unit_ofr_pat.version_number%TYPE,
2559 cp_source_cal_type igs_ps_unit_ofr_pat.cal_type%TYPE,
2560 cp_source_ci_sequence_number igs_ps_unit_ofr_pat.ci_sequence_number%TYPE,
2561 cp_unit_ass_item_group_id igs_as_unitass_item.unit_ass_item_group_id%TYPE) IS
2562 SELECT *
2563 FROM igs_as_unitass_item uai
2564 WHERE uai.logical_delete_dt IS NULL
2565 AND uai.unit_cd = cp_unit_cd
2566 AND uai.version_number = cp_version_number
2567 AND uai.cal_type = cp_source_cal_type
2568 AND uai.ci_sequence_number = cp_source_ci_sequence_number
2569 AND uai.unit_ass_item_group_id = cp_unit_ass_item_group_id
2570 ORDER BY uai.exam_cal_type, uai.exam_ci_sequence_number;
2571 v_uai_rec c_uai_source_rec%ROWTYPE;
2572
2573 CURSOR c_uoo_rec_exists(
2574 cp_unit_cd IGS_PS_UNIT_OFR_PAT.unit_cd%TYPE,
2575 cp_version_number IGS_PS_UNIT_OFR_PAT.version_number%TYPE,
2576 cp_cal_type IGS_PS_UNIT_OFR_PAT.cal_type%TYPE,
2577 cp_dest_ci_sequence_number IGS_PS_UNIT_OFR_PAT.ci_sequence_number%TYPE,
2578 cp_location_cd IGS_PS_UNIT_OFR_OPT.location_cd%TYPE,
2579 cp_unit_class IGS_PS_UNIT_OFR_PAT.unit_cd%TYPE) IS
2580 SELECT *
2581 FROM igs_ps_unit_ofr_opt uoo
2582 WHERE uoo.unit_cd = cp_unit_cd
2583 AND uoo.version_number = cp_version_number
2584 AND uoo.cal_type = cp_cal_type
2585 AND uoo.ci_sequence_number = cp_dest_ci_sequence_number
2586 AND uoo.location_cd = cp_location_cd
2587 AND uoo.unit_class = cp_unit_class;
2588 v_uoo_rec_exists c_uoo_rec_exists%ROWTYPE;
2589
2590 CURSOR c_uoo_seq_num IS
2591 SELECT IGS_PS_UNIT_OFR_OPT_UOO_ID_S.NEXTVAL
2592 FROM DUAL;
2593
2594 CURSOR c_uai_seq_num IS
2595 SELECT IGS_AS_UNITASS_ITEM_SEQ_NUM_S.NEXTVAL
2596 FROM DUAL;
2597
2598 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
2599 SELECT start_dt,end_dt
2600 FROM igs_ca_inst_all
2601 WHERE cal_type = cp_cal_type
2602 AND sequence_number = cp_seq_num;
2603 l_d_src_teach_cal_start_dt DATE;
2604 l_d_src_teach_cal_end_dt DATE;
2605 l_d_dst_teach_cal_start_dt DATE;
2606 l_d_dst_teach_cal_end_dt DATE;
2607 l_n_num_st_days NUMBER;
2608 l_n_num_end_days NUMBER;
2609 l_d_us_dest_start_dt DATE;
2610 l_d_us_dest_end_dt DATE;
2611
2612 l_c_usec_status igs_ps_unit_ofr_opt_all.unit_section_status%TYPE;
2613
2614 -- jbegum As part of bug#2563596 the call to IGS_GE_GEN_003.genp_ins_log_entry was modified .
2615 -- The concatenated string being passed to parameter p_key has the substring FND_MESSAGE.GET_STRING('IGS', l_message_name)
2616 -- removed as this was causing the TBH procedure IGS_GE_S_LOG_ENTRY_PKG.INSERT_ROW to throw up an invalid value error,which
2617 -- was in turn causing function IGS_PS_GEN_008.crsp_ins_uop_uoo to throw up an unhandled exception.
2618 -- Also the concatenated string being passed to parameter p_text has only l_message_name concatenated to it instead of
2619 -- FND_MESSAGE.GET_STRING('IGS', l_message_name)
2620
2621
2622
2623 PROCEDURE handle_excp( p_old_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE ) AS
2624 -------------------------------------------------------------------------------------------
2625 --Change History:
2626 --Who When What
2627 --
2628 -------------------------------------------------------------------------------------------
2629 CURSOR cur_org_unit (cp_uoo_id IN NUMBER) IS
2630 SELECT owner_org_unit_cd
2631 FROM igs_ps_unit_ofr_opt
2632 WHERE uoo_id = cp_uoo_id;
2633 lcur_org_unit cur_org_unit%rowtype;
2634 l_message_name fnd_new_messages.message_text%TYPE;
2635 BEGIN
2636 --l_message_name should contain error messasges from tbh while insertion, if any other error is occured
2637 --that is fetched by using sqlerrm and stored in l_message_name
2638 l_message_name:=fnd_message.get;
2639 IF l_message_name IS NULL THEN
2640 l_message_name:=sqlerrm;
2641 END IF;
2642
2643 OPEN cur_org_unit (p_old_uoo_id);
2644 FETCH cur_org_unit INTO lcur_org_unit;
2645 CLOSE cur_org_unit;
2646
2647 igs_ge_gen_003.genp_ins_log_entry (
2648 'USEC-ROLL' , --This s_log_type
2649 p_log_creation_date, -- This will be accepted AS parameter AND defaulted TO NULL;
2650 lcur_org_unit.owner_org_unit_cd || ',' || p_old_uoo_id || ',' || p_source_cal_type ||
2651 ',' || p_source_ci_sequence_number, --This is the key
2652 NULL, --This is message name
2653 --This is the message text
2654 lcur_org_unit.owner_org_unit_cd || ',' || p_old_uoo_id || ',' || p_source_cal_type || ',' ||p_source_ci_sequence_number || ',' || l_message_name);
2655
2656 END handle_excp;
2657
2658
2659 PROCEDURE crspl_upd_usec_relation (p_src_cal_type igs_ca_inst.cal_type%TYPE,
2660 p_src_sequence_num igs_ca_inst.sequence_number%TYPE,
2661 p_dst_cal_type igs_ca_inst.cal_type%TYPE,
2662 p_dst_sequence_num igs_ca_inst.sequence_number%TYPE
2663 ) AS
2664 /*************************************************************
2665 Created By : sarakshi
2666 Date Created By :23-Sep-2003
2667 Purpose :To create relationship between the unit section that has been rolled over.
2668 Know limitations, enhancements or remarks
2669 Change History
2670 Who When What
2671 sarakshi 18-Jan-2006 Bug#4926548, modified cursor c_new_sub_us and c_new_sup_us1 to address the performance issue. Created local procedures and functions.
2672 sarakshi 17-Nov-2005 Bug#4726940, impact of change of signature of the update_usec_record, passing the default enroll flag
2673 value from the source to the destination for the subordinate section. Also removed variable l_usec_roll
2674 as relationship logic needs to be called irrespective of whether a single unit ssection has been rolled or not
2675 (reverse chronological order - newest change first)
2676 ***************************************************************/
2677
2678 l_c_none VARCHAR2(10) := 'NONE';
2679 l_c_superior VARCHAR2(10) := 'SUPERIOR';
2680 l_c_subordinate VARCHAR2(15) := 'SUBORDINATE';
2681 l_c_notoffered VARCHAR2(15) := 'NOT_OFFERED';
2682 l_c_inactive VARCHAR2(10) := 'INACTIVE';
2683
2684 --Cursor to get list the subordinate unit sections whose superior unit sections have been rolled over
2685 CURSOR c_get_sub_us_list IS
2686 SELECT *
2687 FROM igs_ps_unit_ofr_opt
2688 WHERE sup_uoo_id IN ( SELECT uoo_id
2689 FROM igs_ps_unit_ofr_opt
2690 WHERE cal_type = p_src_cal_type
2691 AND ci_sequence_number = p_src_sequence_num
2692 AND unit_cd = p_unit_cd
2693 AND version_number = p_version_number
2694 );
2695
2696 --Cursor to get unit_cd,version_number, location_code and unit_class for SUP_UOO_ID
2697 CURSOR c_get_old_uoo_det(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2698 SELECT unit_cd,version_number,location_cd,unit_class
2699 FROM igs_ps_unit_ofr_opt
2700 WHERE uoo_id = cp_uoo_id;
2701
2702 --Cursor to get the new UOO_ID for the old SUP_UOO_ID
2703 CURSOR c_new_sup_uoo_id(cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
2704 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
2705 cp_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
2706 cp_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE) IS
2707 SELECT uoo_id
2708 FROM igs_ps_unit_ofr_opt
2709 WHERE unit_cd = cp_unit_cd
2710 AND version_number = cp_version_number
2711 AND cal_type = p_dst_cal_type
2712 AND ci_sequence_number = p_dst_sequence_num
2713 AND location_cd = cp_location_cd
2714 AND unit_class = cp_unit_class;
2715
2716 --Cursor to get the new subordinate unit sections
2717 CURSOR c_new_sub_us (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
2718 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
2719 cp_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
2720 cp_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE) IS
2721 SELECT uoo.*
2722 FROM igs_ps_unit_ofr_opt uoo,
2723 igs_ps_unit_ver_all uv,
2724 igs_ps_unit_stat us
2725 WHERE uoo.unit_cd = cp_unit_cd
2726 AND uoo.version_number = cp_version_number
2727 AND uoo.location_cd = cp_location_cd
2728 AND uoo.unit_class = cp_unit_class
2729 AND uoo.relation_type = l_c_none
2730 AND uoo.unit_section_status <> l_c_notoffered
2731 AND uoo.unit_cd = uv.unit_cd
2732 AND uoo.version_number = uv.version_number
2733 AND uv.unit_status = us.unit_status
2734 AND us.s_unit_status <> l_c_inactive
2735 AND uoo.uoo_id NOT IN (SELECT uoo_id FROM igs_ps_usec_x_grpmem);
2736
2737
2738 --Cursor to get list the subordiante unit sections have been rolled over
2739 CURSOR c_get_sub_us_list1 IS
2740 SELECT *
2741 FROM igs_ps_unit_ofr_opt
2742 WHERE cal_type = p_src_cal_type
2743 AND ci_sequence_number = p_src_sequence_num
2744 AND unit_cd = p_unit_cd
2745 AND version_number = p_version_number
2746 AND relation_type = l_c_subordinate;
2747
2748 --Cursor to get from the new subordinate unit section details
2749 CURSOR c_get_new_sub (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
2750 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
2751 cp_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
2752 cp_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE) IS
2753 SELECT *
2754 FROM igs_ps_unit_ofr_opt
2755 WHERE unit_cd = cp_unit_cd
2756 AND version_number = cp_version_number
2757 AND location_cd = cp_location_cd
2758 AND unit_class = cp_unit_class
2759 AND cal_type = p_dst_cal_type
2760 AND ci_sequence_number = p_dst_sequence_num;
2761
2762 --Cursor to get the old superior unit section details
2763 CURSOR c_get_old_sup_det (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2764 SELECT *
2765 FROM igs_ps_unit_ofr_opt
2766 WHERE uoo_id = cp_uoo_id;
2767
2768
2769 --Cursor to get the new superior unit sections
2770 CURSOR c_new_sup_us(cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
2771 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
2772 cp_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
2773 cp_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE) IS
2774 SELECT uoo.uoo_id,uoo.sup_uoo_id
2775 FROM igs_ps_unit_ofr_opt uoo,
2776 igs_ps_unit_ver_all uv,
2777 igs_ps_unit_stat us
2778 WHERE uoo.unit_cd = cp_unit_cd
2779 AND uoo.version_number = cp_version_number
2780 AND uoo.location_cd = cp_location_cd
2781 AND uoo.unit_class = cp_unit_class
2782 AND uoo.relation_type IN (l_c_superior,l_c_none)
2783 AND uoo.cal_type = p_dst_cal_type
2784 AND uoo.ci_sequence_number = p_dst_sequence_num
2785 AND uoo.unit_cd = uv.unit_cd
2786 AND uoo.version_number = uv.version_number
2787 AND uv.unit_status = us.unit_status
2788 AND us.s_unit_status <> l_c_inactive
2789 AND uoo.uoo_id NOT IN (SELECT uoo_id FROM igs_ps_usec_x_grpmem)
2790 AND uoo.unit_section_status <> l_c_notoffered;
2791
2792 CURSOR c_new_sup_us1 (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
2793 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
2794 cp_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
2795 cp_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE) IS
2796 SELECT uoo_id,sup_uoo_id,cal_type,ci_sequence_number
2797 FROM igs_ps_unit_ofr_opt
2798 WHERE unit_cd = cp_unit_cd
2799 AND version_number = cp_version_number
2800 AND location_cd = cp_location_cd
2801 AND unit_class = cp_unit_class
2802 AND relation_type IN (l_c_superior,l_c_none)
2803 AND uoo_id NOT IN (SELECT uoo_id FROM igs_ps_usec_x_grpmem)
2804 AND unit_section_Status <> l_c_notoffered;
2805
2806 l_c_get_old_uoo_det c_get_old_uoo_det%ROWTYPE;
2807 l_c_new_sup_uoo_id c_new_sup_uoo_id%ROWTYPE;
2808 l_c_get_new_sub c_get_new_sub%ROWTYPE;
2809 l_c_get_old_sup_det c_get_old_sup_det%ROWTYPE;
2810 l_c_new_sup_us c_new_sup_us%ROWTYPE;
2811 l_c_new_sup_us1 c_new_sup_us1%ROWTYPE;
2812 l_b_var1 BOOLEAN := FALSE;
2813
2814 --- added as a part of performance activity ---
2815 TYPE teach_cal_rec IS RECORD(
2816 cal_type igs_ca_inst_all.cal_type%TYPE,
2817 sequence_number igs_ca_inst_all.sequence_number%TYPE
2818 );
2819 TYPE teachCalendar IS TABLE OF teach_cal_rec INDEX BY BINARY_INTEGER;
2820 teachCalendar_tbl teachCalendar;
2821 l_n_counter NUMBER(10);
2822 l_c_proceed BOOLEAN ;
2823
2824
2825 PROCEDURE createCalendar IS
2826
2827 CURSOR cur_cal_teach(cp_load_cal igs_ca_teach_to_load_v.load_cal_type%TYPE,
2828 cp_load_seq igs_ca_teach_to_load_v.load_ci_sequence_number%TYPE) IS
2829 SELECT teach_cal_type,teach_ci_sequence_number
2830 FROM igs_ca_teach_to_load_v
2831 WHERE load_cal_type = cp_load_cal
2832 AND load_ci_sequence_number = cp_load_seq;
2833
2834 CURSOR cur_cal_load IS
2835 SELECT load_cal_type,load_ci_sequence_number
2836 FROM igs_ca_load_to_teach_v
2837 WHERE teach_cal_type=p_dst_cal_type
2838 AND teach_ci_sequence_number=p_dst_sequence_num;
2839
2840 BEGIN
2841 --populate the pl-sql table with the teaching calendar's by mapping the load calendars.
2842 l_n_counter :=1;
2843 FOR rec_cur_cal_load IN cur_cal_load LOOP
2844 FOR rec_cur_cal_teach IN cur_cal_teach(rec_cur_cal_load.load_cal_type ,rec_cur_cal_load.load_ci_sequence_number) LOOP
2845 teachCalendar_tbl(l_n_counter).cal_type :=rec_cur_cal_teach.teach_cal_type;
2846 teachCalendar_tbl(l_n_counter).sequence_number :=rec_cur_cal_teach.teach_ci_sequence_number;
2847 l_n_counter:=l_n_counter+1;
2848 END LOOP;
2849 END LOOP;
2850
2851 END createCalendar;
2852
2853 FUNCTION testCalendar(cp_cal_type igs_ca_inst_all.cal_type%TYPE,
2854 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE) RETURN BOOLEAN AS
2855 BEGIN
2856 IF teachCalendar_tbl.EXISTS(1) THEN
2857 FOR i IN 1..teachCalendar_tbl.last LOOP
2858 IF cp_cal_type=teachCalendar_tbl(i).cal_type AND
2859 cp_sequence_number=teachCalendar_tbl(i).sequence_number THEN
2860 RETURN TRUE;
2861 END IF;
2862 END LOOP;
2863 END IF;
2864 RETURN FALSE;
2865 END testCalendar;
2866
2867 --- added as a part of performance activity ---
2868
2869 BEGIN
2870 --Store the teaching calendars in a pl-sql tables for the input teaching calendars
2871 createCalendar;
2872 --Fetch all the superior unit section from the source calendar instance
2873 FOR rec_get_sub_us_list IN c_get_sub_us_list LOOP
2874
2875 l_b_var1 :=FALSE;
2876 --This cursor capture the details of old sup_uoo_id
2877 OPEN c_get_old_uoo_det (rec_get_sub_us_list.sup_uoo_id);
2878 FETCH c_get_old_uoo_det INTO l_c_get_old_uoo_det;
2879 CLOSE c_get_old_uoo_det;
2880
2881
2882 --This cursor fetches the new sup_uoo_id record
2883 OPEN c_new_sup_uoo_id (l_c_get_old_uoo_det.unit_cd,l_c_get_old_uoo_det.version_number,l_c_get_old_uoo_det.location_cd,l_c_get_old_uoo_det.unit_class);
2884 FETCH c_new_sup_uoo_id INTO l_c_new_sup_uoo_id;
2885 CLOSE c_new_sup_uoo_id;
2886
2887 --This cursor fetches the new uoo_id for the old uoo_id
2888
2889 FOR rec_new_sub_us IN c_new_sub_us (rec_get_sub_us_list.unit_cd,rec_get_sub_us_list.version_number,rec_get_sub_us_list.location_cd,rec_get_sub_us_list.unit_class) LOOP
2890 IF testCalendar(rec_new_sub_us.cal_type ,rec_new_sub_us.ci_sequence_number ) THEN
2891 --update the new unit sections record, relation_type and sup_uoo_id column value
2892 update_usec_record(rec_new_sub_us.uoo_id,l_c_subordinate,l_c_new_sup_uoo_id.uoo_id,rec_get_sub_us_list.default_enroll_flag);
2893 l_b_var1 :=TRUE;
2894 END IF;
2895 END LOOP;
2896
2897 --update the new unit sections relation_type for the superior record.
2898 IF l_b_var1 THEN
2899 update_usec_record(l_c_new_sup_uoo_id.uoo_id,l_c_superior,NULL,NULL);
2900 END IF;
2901 END LOOP;
2902
2903 --Fetch all the records from the below cursor and process
2904 FOR rec_get_sub_us_list1 IN c_get_sub_us_list1 LOOP
2905
2906 --capture the new uoo_id
2907 OPEN c_get_new_sub (rec_get_sub_us_list1.unit_cd,rec_get_sub_us_list1.version_number,rec_get_sub_us_list1.location_cd,rec_get_sub_us_list1.unit_class);
2908 FETCH c_get_new_sub INTO l_c_get_new_sub;
2909 CLOSE c_get_new_sub;
2910
2911 --capture the details of sup_uoo_id
2912 OPEN c_get_old_sup_det (rec_get_sub_us_list1.sup_uoo_id);
2913 FETCH c_get_old_sup_det INTO l_c_get_old_sup_det;
2914 CLOSE c_get_old_sup_det;
2915
2916 OPEN c_new_sup_us (l_c_get_old_sup_det.unit_cd,l_c_get_old_sup_det.version_number,l_c_get_old_sup_det.location_cd,l_c_get_old_sup_det.unit_class);
2917 FETCH c_new_sup_us INTO l_c_new_sup_us;
2918 IF c_new_sup_us%FOUND THEN
2919 --update the new unit sections relation_type and sup_uoo_id column value
2920 update_usec_record(l_c_get_new_sub.uoo_id,l_c_subordinate,l_c_new_sup_us.uoo_id,rec_get_sub_us_list1.default_enroll_flag);
2921 CLOSE c_new_sup_us;
2922 --update the new unit sections relation_type
2923 update_usec_record(l_c_new_sup_us.uoo_id,l_c_superior,NULL,NULL);
2924 ELSE
2925 FOR l_c_new_sup_us1 IN c_new_sup_us1(l_c_get_old_sup_det.unit_cd,l_c_get_old_sup_det.version_number,l_c_get_old_sup_det.location_cd,l_c_get_old_sup_det.unit_class) LOOP
2926 IF testCalendar(l_c_new_sup_us1.cal_type ,l_c_new_sup_us1.ci_sequence_number ) THEN
2927 --update the new unit sections relation_type and sup_uoo_id column value
2928 update_usec_record(l_c_get_new_sub.uoo_id,l_c_subordinate,l_c_new_sup_us1.uoo_id,rec_get_sub_us_list1.default_enroll_flag);
2929
2930 --update the new unit sections relation_type
2931 update_usec_record(l_c_new_sup_us1.uoo_id,l_c_superior,NULL,NULL);
2932 EXIT;
2933 END IF;
2934 END LOOP;
2935 END IF;
2936
2937 END LOOP;
2938
2939 IF teachCalendar_tbl.EXISTS(1) THEN
2940 teachCalendar_tbl.DELETE;
2941 END IF;
2942
2943 END crspl_upd_usec_relation;
2944
2945
2946 PROCEDURE crspl_get_new_exam_cal (
2947 p_old_exam_cal_type igs_ca_inst.cal_type%TYPE,
2948 p_old_exam_seq_num igs_ca_inst.sequence_number%TYPE,
2949 p_new_exam_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
2950 p_new_exam_seq_num OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
2951 p_uoo_id NUMBER) AS
2952 -------------------------------------------------------------------------------------------
2953 --Change History:
2954 --Who When What
2955 --sommukhe 01-SEP-2005 Bug#4538540,Added cursor cur_ass_item .
2956 --sarakshi 24-dec-2002 Bug#2689625,removed the exception section
2957 -------------------------------------------------------------------------------------------
2958 v_old_specific_occurrence NUMBER(4) ;
2959 v_old_total_occurrence NUMBER(4) ;
2960 v_new_total_occurrence NUMBER(4) ;
2961
2962 CURSOR c_exam_cal_type (
2963 cp_exam_cal_type igs_ca_inst.cal_type%TYPE,
2964 cp_teach_cal_type igs_ca_inst.cal_type%TYPE,
2965 cp_teach_seq_num igs_ca_inst.sequence_number%TYPE) IS
2966 SELECT ci.cal_type,
2967 ci.sequence_number
2968 FROM igs_ca_type ct,
2969 igs_ca_inst ci,
2970 igs_ca_inst_rel cir
2971 WHERE cir.sup_ci_sequence_number = ci.sequence_number
2972 AND cir.sup_cal_type = ci.cal_type
2973 AND ct.cal_type = ci.cal_type
2974 AND ct.s_cal_cat = cst_exam
2975 AND cir.sub_cal_type = cp_teach_cal_type
2976 AND cir.sub_ci_sequence_number = cp_teach_seq_num
2977 AND ci.cal_type = cp_exam_cal_type
2978 ORDER BY ci.start_dt;
2979 v_exam_cal_rec c_exam_cal_type%ROWTYPE;
2980
2981 CURSOR c_exam_cal_count (
2982 cp_exam_cal_type IGS_CA_INST.cal_type%TYPE,
2983 cp_teach_cal_type IGS_CA_INST.cal_type%TYPE,
2984 cp_teach_seq_num IGS_CA_INST.sequence_number%TYPE) IS
2985 SELECT COUNT(ci.cal_type)
2986 FROM IGS_CA_TYPE ct,
2987 IGS_CA_INST ci,
2988 IGS_CA_INST_REL cir
2989 WHERE cir.sup_ci_sequence_number = ci.sequence_number
2990 AND cir.sup_cal_type = ci.cal_type
2991 AND ct.cal_type = ci.cal_type
2992 AND ct.s_cal_cat = cst_exam
2993 AND cir.sub_cal_type = cp_teach_cal_type
2994 AND cir.sub_ci_sequence_number = cp_teach_seq_num
2995 AND ci.cal_type = cp_exam_cal_type;
2996
2997 BEGIN
2998
2999 -- Assigning initial values to local variables which were being initialised using DEFAULT
3000 -- clause.Done as part of bug #2563596 to remove GSCC warning.
3001
3002 v_old_specific_occurrence := 0;
3003 v_old_total_occurrence := 0;
3004 v_new_total_occurrence := 0;
3005
3006 IF p_old_exam_cal_type IS NOT NULL THEN
3007 IF p_old_exam_seq_num IS NULL THEN
3008 -- check for all exam periods for the new teaching calendar instance.
3009 OPEN c_exam_cal_type(
3010 p_old_exam_cal_type,
3011 p_cal_type,
3012 p_dest_ci_sequence_number);
3013 FETCH c_exam_cal_type INTO v_exam_cal_rec;
3014 IF c_exam_cal_type%FOUND THEN
3015 p_new_exam_cal_type := p_old_exam_cal_type;
3016 p_new_exam_seq_num := NULL;
3017 ELSE
3018 p_new_exam_cal_type := NULL;
3019 p_new_exam_seq_num := NULL;
3020 END IF;
3021 CLOSE c_exam_cal_type;
3022 ELSE
3023 -- p_old_exam_seq_num is NOT NULL
3024 -- get the total occurrence of the exam period for this particular
3025 -- IGS_PS_UNIT assessment item for the old teaching calendar instance.
3026 OPEN c_exam_cal_count(
3027 p_old_exam_cal_type,
3028 p_source_cal_type,
3029 p_source_ci_sequence_number);
3030 FETCH c_exam_cal_count INTO v_old_total_occurrence;
3031 CLOSE c_exam_cal_count;
3032 -- get the total occurrence of the exam period for this particular
3033 -- IGS_PS_UNIT assessment item for the new teaching calendar instance.
3034 OPEN c_exam_cal_count(
3035 p_old_exam_cal_type,
3036 p_cal_type,
3037 p_dest_ci_sequence_number);
3038 FETCH c_exam_cal_count INTO v_new_total_occurrence;
3039 CLOSE c_exam_cal_count;
3040 IF v_new_total_occurrence >0 AND
3041 v_new_total_occurrence = v_old_total_occurrence THEN
3042 -- get the occurrence of the exam period for this particular
3043 -- IGS_PS_UNIT assessment item for the old teaching calendar instance.
3044 FOR v_old_exam_cal_rec IN c_exam_cal_type(
3045 p_old_exam_cal_type,
3046 p_source_cal_type,
3047 p_source_ci_sequence_number) LOOP
3048 IF v_old_exam_cal_rec.sequence_number = p_old_exam_seq_num THEN
3049 v_old_specific_occurrence := c_exam_cal_type%ROWCOUNT;
3050 EXIT;
3051 END IF;
3052 END LOOP;
3053 IF v_old_specific_occurrence>0 THEN
3054 -- get the sequence number, which has the same occurrence as the
3055 -- old exam period, for this particular IGS_PS_UNIT assessment item for
3056 -- the new teaching calendar instance.
3057 FOR v_new_exam_cal_rec IN c_exam_cal_type(
3058 p_old_exam_cal_type,
3059 p_cal_type,
3060 p_dest_ci_sequence_number) LOOP
3061 IF c_exam_cal_type%ROWCOUNT = v_old_specific_occurrence THEN
3062 p_new_exam_seq_num := v_new_exam_cal_rec.sequence_number;
3063 EXIT;
3064 END IF;
3065 END LOOP;
3066 p_new_exam_cal_type := p_old_exam_cal_type;
3067 ELSE
3068 p_new_exam_cal_type := NULL;
3069 p_new_exam_seq_num := NULL;
3070 END IF;
3071 ELSE -- total occurrence for old and new teaching calendar are not equal
3072 p_new_exam_cal_type := NULL;
3073 p_new_exam_seq_num := NULL;
3074 END IF;
3075 END IF; -- p_old_exam_seq_number is NULL
3076 ELSE -- p_old_exam_cal_type is NULL
3077 p_new_exam_cal_type := NULL;
3078 p_new_exam_seq_num := NULL;
3079 END IF; -- p_old_exam_cal_type is NOT NULL
3080
3081 END crspl_get_new_exam_cal;
3082 BEGIN
3083 -- Main unit section rollover procedure
3084 -- Assigning initial values to local variables which were being initialised using DEFAULT
3085 -- clause.Done as part of bug #2563596 to remove GSCC warning.
3086
3087 v_uoo_inserted_cnt := 0;
3088 v_uai_inserted_cnt := 0;
3089 v_uai_fetched_cnt := 0;
3090 v_uoo_error := FALSE;
3091 v_uai_error := FALSE;
3092 v_tro_recs_skipped := FALSE;
3093 v_uai_continue := FALSE;
3094 v_ret_val := TRUE;
3095 l_d_src_teach_cal_start_dt := NULL;
3096
3097 -- Set default value
3098 p_message_name := NULL;
3099 v_message_name := NULL;
3100 v_successful_pattern_mbr := TRUE;
3101
3102 -- Validate the destination parent record(uop) exists.
3103 OPEN c_uop_dest_rec;
3104 FETCH c_uop_dest_rec INTO v_uop_exists;
3105 IF (c_uop_dest_rec%NOTFOUND) THEN
3106 p_message_name := 'IGS_PS_DEST_UOP_NOT_EXIST';
3107 CLOSE c_uop_dest_rec;
3108 RETURN FALSE;
3109 END IF;
3110 CLOSE c_uop_dest_rec;
3111
3112 -- Added by jbegum as part of bug #2563596
3113 -- The package IGS_PS_GEN_006 has a call to IGS_PS_GEN_008.crsp_ins_uop_uoo.Before this call in the package IGS_PS_GEN_006
3114 -- an insert is happening into igs_ge_s_log table thru a call to IGS_GE_GEN_003.genp_ins_log.The same creation date is being
3115 -- passed to the call of IGS_PS_GEN_008.crsp_ins_uop_uoo.Hence added code here to check for existence of the record in
3116 -- igs_ge_s_log table before inserting into it.Thus preventing the error 'Record already exists' being thrown up as unhandled
3117 -- exception by the procedure IGS_PS_GEN_006.crsp_ins_ci_uop_uoo
3118
3119
3120 OPEN cur_check_log(p_log_creation_date);
3121 FETCH cur_check_log INTO l_var_log;
3122 IF cur_check_log%NOTFOUND THEN
3123
3124 --Added by sarakshi, as a part of bug#2332807
3125 --If any error condition occurs , from the exception handlers we are inserting to igs_ge_s_log_entry table
3126 --which is child table so here inserting in the parent table first which is to be deleted if no child
3127 --entries are found
3128 igs_ge_s_log_pkg.insert_row( x_rowid => l_rowid ,
3129 x_s_log_type => 'USEC-ROLL',
3130 x_creation_dt =>p_log_creation_date,
3131 x_key =>NULL,
3132 x_mode => 'R' );
3133 ELSE
3134 l_rowid := l_var_log.rowid;
3135 END IF;
3136 CLOSE cur_check_log;
3137
3138
3139 -- Select the source IGS_PS_UNIT_OFR_OPT records.
3140 OPEN c_uoo_source_rec;
3141 LOOP
3142 BEGIN
3143 FETCH c_uoo_source_rec INTO v_uoo_rec;
3144 EXIT WHEN c_uoo_source_rec%NOTFOUND;
3145 -- all of the below conditions must be true for the
3146 -- insert to proceed, else don't insert this particular
3147 -- IGS_PS_UNIT_OFR_OPT record
3148 -- get the max version number for grading schema
3149
3150 -- lpriyadh for enhancement bug# 1516959
3151 l_old_uoo_id := v_uoo_rec.uoo_id;
3152
3153
3154 OPEN c_latest_gs_version (v_uoo_rec.grading_schema_cd);
3155 FETCH c_latest_gs_version INTO v_latest_gs_version;
3156 CLOSE c_latest_gs_version;
3157
3158 IF -- if location_cd is open or = 'CAMPUS', continue validation
3159 (igs_ps_val_uoo.crsp_val_loc_cd (
3160 v_uoo_rec.location_cd,
3161 p_message_name) = TRUE
3162 ) AND
3163 -- if IGS_AS_UNIT_CLASS is open, continue validation
3164 (igs_ps_val_uoo.crsp_val_uoo_uc (
3165 v_uoo_rec.unit_class,
3166 p_message_name) = TRUE
3167 ) AND
3168 -- if grading schema is in the current or future,
3169 -- continue validation
3170 (igs_as_val_gsg.assp_val_gs_cur_fut(
3171 v_uoo_rec.grading_schema_cd,
3172 v_latest_gs_version,
3173 p_message_name) = TRUE) THEN
3174
3175 -- Validate IGS_PS_UNIT contact is a staff member
3176 IF igs_ad_val_acai.genp_val_staff_prsn(v_uoo_rec.unit_contact,
3177 v_message_name) = TRUE THEN
3178 v_unit_contact := v_uoo_rec.unit_contact;
3179 ELSE
3180 v_unit_contact := NULL;
3181 END IF;
3182
3183 OPEN c_uoo_rec_exists(p_unit_cd,
3184 p_version_number,
3185 p_cal_type,
3186 p_dest_ci_sequence_number,
3187 v_uoo_rec.location_cd,
3188 v_uoo_rec.unit_class);
3189 FETCH c_uoo_rec_exists INTO v_uoo_rec_exists;
3190 -- checking that no other IGS_PS_UNIT_OFR_OPT record exists
3191 IF (c_uoo_rec_exists%NOTFOUND) THEN
3192 CLOSE c_uoo_rec_exists;
3193
3194 -- get the next IGS_PS_UNIT_OFR_OPT_UOO_ID_S from the system
3195 OPEN c_uoo_seq_num;
3196 FETCH c_uoo_seq_num INTO v_new_uoo_id;
3197 CLOSE c_uoo_seq_num;
3198
3199 -- insert the IGS_PS_UNIT_OFR_OPT record, with this next uoo_id
3200 x_rowid := NULL;
3201 l_org_id := igs_ge_gen_003.get_org_id;
3202
3203
3204 --bug#2768783, added the validate/generate call number logic
3205
3206 -- Validate/generate Call Number
3207 IF fnd_profile.value('IGS_PS_CALL_NUMBER') IN ('AUTO','NONE') THEN
3208 v_uoo_rec.call_number:=NULL;
3209 ELSIF ( fnd_profile.value('IGS_PS_CALL_NUMBER') = 'USER_DEFINED' ) THEN
3210
3211 IF v_uoo_rec.call_number IS NOT NULL THEN
3212 IF NOT igs_ps_unit_ofr_opt_pkg.check_call_number ( p_teach_cal_type => p_cal_type,
3213 p_teach_sequence_num => p_dest_ci_sequence_number,
3214 p_call_number => v_uoo_rec.call_number,
3215 p_rowid => x_rowid ) THEN
3216 v_uoo_rec.call_number:=NULL;
3217 END IF;
3218 END IF;
3219
3220 END IF;
3221
3222 IF l_d_src_teach_cal_start_dt IS NULL THEN
3223 OPEN c_teach_date(p_source_cal_type,p_source_ci_sequence_number);
3224 FETCH c_teach_date INTO l_d_src_teach_cal_start_dt,l_d_src_teach_cal_end_dt;
3225 CLOSE c_teach_date;
3226
3227 OPEN c_teach_date(p_cal_type,p_dest_ci_sequence_number);
3228 FETCH c_teach_date INTO l_d_dst_teach_cal_start_dt,l_d_dst_teach_cal_end_dt;
3229 CLOSE c_teach_date;
3230 END IF;
3231
3232 IF v_uoo_rec.unit_section_start_date IS NOT NULL THEN
3233 l_n_num_st_days := v_uoo_rec.unit_section_start_date - l_d_src_teach_cal_start_dt;
3234 l_d_us_dest_start_dt := l_d_dst_teach_cal_start_dt + l_n_num_st_days;
3235 ELSE
3236 l_d_us_dest_start_dt := NULL;
3237 END IF;
3238
3239
3240 IF v_uoo_rec.unit_section_end_date IS NOT NULL THEN
3241 l_n_num_end_days := v_uoo_rec.unit_section_end_date - l_d_src_teach_cal_end_dt;
3242 l_d_us_dest_end_dt := l_d_dst_teach_cal_end_dt + l_n_num_end_days;
3243 ELSE
3244 l_d_us_dest_end_dt := NULL;
3245 END IF;
3246
3247 --Unit section start date must not be gretaer than the teaching calendar end date
3248 IF l_d_us_dest_start_dt IS NOT NULL AND l_d_us_dest_start_dt > NVL(l_d_us_dest_end_dt, l_d_dst_teach_cal_end_dt) THEN
3249 l_d_us_dest_start_dt := l_d_dst_teach_cal_start_dt;
3250 END IF;
3251
3252 --Unit section end date must not be less than the teaching calendar start date
3253 IF l_d_us_dest_end_dt IS NOT NULL AND l_d_us_dest_end_dt < NVL(l_d_us_dest_start_dt,l_d_dst_teach_cal_start_dt) THEN
3254 l_d_us_dest_end_dt := l_d_dst_teach_cal_end_dt;
3255 END IF;
3256
3257
3258 l_c_usec_status := get_section_status(v_uoo_rec.unit_section_status);
3259
3260 -- Added auditable_ind and audit_permission_ind parameters to the following insert_row call
3261 -- as part of Bug# 2636716, EN Integration by shtatiko
3262 igs_ps_unit_ofr_opt_pkg.insert_row(
3263 x_rowid => x_rowid,
3264 x_unit_cd => v_uoo_rec.unit_cd,
3265 x_version_number => v_uoo_rec.version_number,
3266 x_cal_type => p_cal_type,
3267 x_ci_sequence_number => p_dest_ci_sequence_number,
3268 x_location_cd => v_uoo_rec.location_cd,
3269 x_unit_class => v_uoo_rec.unit_class,
3270 x_uoo_id => v_new_uoo_id,
3271 x_ivrs_available_ind => v_uoo_rec.ivrs_available_ind,
3272 x_call_number => v_uoo_rec.call_number,
3273 x_unit_section_status => l_c_usec_status,
3274 x_unit_section_start_date => l_d_us_dest_start_dt,
3275 x_unit_section_end_date => l_d_us_dest_end_dt,
3276 x_enrollment_actual => NULL,
3277 x_waitlist_actual => NULL,
3278 x_offered_ind => v_uoo_rec.offered_ind,
3279 x_state_financial_aid => v_uoo_rec.state_financial_aid,
3280 x_grading_schema_prcdnce_ind => v_uoo_rec.grading_schema_prcdnce_ind,
3281 x_federal_financial_aid => v_uoo_rec.federal_financial_aid,
3282 x_unit_quota => v_uoo_rec.unit_quota,
3283 x_unit_quota_reserved_places => v_uoo_rec.unit_quota_reserved_places,
3284 x_institutional_financial_aid => v_uoo_rec.institutional_financial_aid,
3285 x_grading_schema_cd => v_uoo_rec.grading_schema_cd,
3286 x_gs_version_number => v_latest_gs_version,
3287 x_unit_contact => v_unit_contact,
3288 x_mode => 'R',
3289 x_org_id => l_org_id,
3290 x_ss_enrol_ind => v_uoo_rec.SS_ENROL_ind,
3291 x_ss_display_ind => v_uoo_rec.ss_display_ind, --Added by apelleti as per DLD PSP001-US on 14-JUN-01
3292 x_owner_org_unit_cd => v_uoo_rec.owner_org_unit_cd,
3293 x_attendance_required_ind => v_uoo_rec.attendance_required_ind,
3294 x_reserved_seating_allowed => v_uoo_rec.reserved_seating_allowed,
3295 x_special_permission_ind => v_uoo_rec.special_permission_ind,
3296 x_dir_enrollment => v_uoo_rec.dir_enrollment, --The following three fields were added by Pradhakr
3297 x_enr_from_wlst => v_uoo_rec.enr_from_wlst, -- as part of Enrollment Build process (Enh.Bug# 1832130)
3298 x_inq_not_wlst => v_uoo_rec.inq_not_wlst,
3299 --Added the following col according to bug 1882122
3300 x_rev_account_cd => v_uoo_rec.rev_account_cd , -- lpriyadh for enhacement bug # 1516959
3301 x_anon_unit_grading_ind => v_uoo_rec.anon_unit_grading_ind ,
3302 x_anon_assess_grading_ind => v_uoo_rec.anon_assess_grading_ind,
3303 x_non_std_usec_ind => v_uoo_rec.non_std_usec_ind,
3304 x_auditable_ind => v_uoo_rec.auditable_ind,
3305 x_audit_permission_ind => v_uoo_rec.audit_permission_ind,
3306 x_not_multiple_section_flag => v_uoo_rec.not_multiple_section_flag,
3307 x_sup_uoo_id => NULL,
3308 x_relation_type => 'NONE',
3309 x_default_enroll_flag => v_uoo_rec.default_enroll_flag,
3310 x_abort_flag => 'N'
3311 );
3312
3313
3314 --Procedure to insert the unit section detail records
3315 igs_ps_gen_001.crsp_ins_unit_section(l_old_uoo_id ,
3316 v_new_uoo_id ,
3317 p_message_name,
3318 p_log_creation_date );
3319
3320 -- passing p_log_creation_date parameter enhancement bug 1800179 pmarada
3321
3322 -- increment count to reflect that a successful insert of a IGS_PS_UNIT_OFR_OPT record occurred
3323 v_uoo_inserted_cnt := v_uoo_inserted_cnt + 1;
3324
3325 ELSE
3326 -- if uoo record already exists not perform insert.
3327 -- lpriyadh enhancement bug# 1516959
3328
3329 v_new_uoo_id := v_uoo_rec_exists.uoo_id;
3330
3331 igs_ps_gen_001.crsp_ins_unit_section(l_old_uoo_id ,
3332 v_new_uoo_id ,
3333 p_message_name ,
3334 p_log_creation_date);
3335 CLOSE c_uoo_rec_exists;
3336 END IF; -- checking no other destination record exists with this dest_ci_sequence_number.
3337
3338 END IF; -- if location_cd, IGS_AS_UNIT_CLASS, grading schema validation is TRUE
3339 EXCEPTION
3340 WHEN OTHERS THEN
3341 --Fnd log implementation
3342 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3343 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_008.crsp_ins_uop_uoo.in_exception_section_OTHERS.err_msg',
3344 SUBSTRB('Unit Section:'||v_uoo_rec.uoo_id||' '||
3345 NVL(fnd_message.get,SQLERRM),1,4000));
3346 END IF;
3347 --This exception handler has been added as a part of bug#2332807
3348 handle_excp(l_old_uoo_id);
3349 END;
3350 END LOOP; -- c_uoo_source_rec
3351
3352 BEGIN
3353
3354 --Call the below procedure for creating the unit section relationship.
3355 crspl_upd_usec_relation (p_src_cal_type => p_source_cal_type ,
3356 p_src_sequence_num => p_source_ci_sequence_number,
3357 p_dst_cal_type => p_cal_type,
3358 p_dst_sequence_num => p_dest_ci_sequence_number );
3359
3360 EXCEPTION
3361 WHEN OTHERS THEN
3362 NULL;
3363 END;
3364
3365
3366
3367 /**** Roll Over the Unit Assessment items ******/
3368 --select the unit assessment items group
3369 FOR cur_unit_ass_group_rec IN cur_unit_ass_group(p_unit_cd,p_version_number,p_source_cal_type,p_source_ci_sequence_number) LOOP
3370 DECLARE
3371 CURSOR cur_unitassgrp_new (cp_unit_cd igs_as_unit_ai_grp.unit_cd%TYPE,
3372 cp_version_number igs_as_unit_ai_grp.version_number%TYPE,
3373 cp_cal_type igs_as_unit_ai_grp.cal_type%TYPE,
3374 cp_ci_sequence_number igs_as_unit_ai_grp.ci_sequence_number%TYPE,
3375 cp_group_name igs_as_unit_ai_grp.group_name%TYPE) IS
3376 SELECT unit_ass_item_group_id
3377 FROM igs_as_unit_ai_grp
3378 WHERE unit_cd = cp_unit_cd
3379 AND version_number = cp_version_number
3380 AND cal_type = cp_cal_type
3381 AND ci_sequence_number= cp_ci_sequence_number
3382 AND group_name = cp_group_name;
3383 l_rowid1 VARCHAR2(25);
3384 l_unit_ass_item_group_id NUMBER;
3385
3386 CURSOR cur_ass_item(cp_unit_cd igs_as_unitass_item_all.unit_cd%TYPE,
3387 cp_version_number igs_as_unitass_item_all.version_number%TYPE,
3388 cp_cal_type igs_as_unitass_item_all.cal_type%TYPE,
3389 cp_ci_sequence_number igs_as_unitass_item_all.ci_sequence_number%TYPE,
3390 cp_unit_ass_item_group_id igs_as_unitass_item_all.unit_ass_item_group_id%TYPE) IS
3391 SELECT 'X'
3392 FROM igs_as_unitass_item_all
3393 WHERE unit_cd = cp_unit_cd
3394 AND version_number = cp_version_number
3395 AND cal_type = cp_cal_type
3396 AND ci_sequence_number = cp_ci_sequence_number
3397 AND unit_ass_item_group_id = cp_unit_ass_item_group_id;
3398 l_c_var VARCHAR2(1);
3399 BEGIN
3400 l_rowid1 :=NULL;
3401 l_unit_ass_item_group_id := NULL;
3402
3403 OPEN cur_unitassgrp_new(p_unit_cd,p_version_number,p_cal_type,p_dest_ci_sequence_number,cur_unit_ass_group_rec.group_name);
3404 FETCH cur_unitassgrp_new INTO l_unit_ass_item_group_id;
3405 IF cur_unitassgrp_new%NOTFOUND THEN
3406 igs_as_unit_ai_grp_pkg.insert_row(
3407 x_rowid => l_rowid1,
3408 x_unit_ass_item_group_id => l_unit_ass_item_group_id,
3409 x_unit_cd => p_unit_cd,
3410 x_version_number => p_version_number,
3411 x_cal_type => p_cal_type,
3412 x_ci_sequence_number => p_dest_ci_sequence_number,
3413 x_group_name => cur_unit_ass_group_rec.group_name,
3414 x_midterm_formula_code => cur_unit_ass_group_rec.midterm_formula_code,
3415 x_midterm_formula_qty => cur_unit_ass_group_rec.midterm_formula_qty,
3416 x_midterm_weight_qty => cur_unit_ass_group_rec.midterm_weight_qty,
3417 x_final_formula_code => cur_unit_ass_group_rec.final_formula_code,
3418 x_final_formula_qty => cur_unit_ass_group_rec.final_formula_qty,
3419 x_final_weight_qty => cur_unit_ass_group_rec.final_weight_qty
3420 );
3421 END IF;
3422 CLOSE cur_unitassgrp_new;
3423
3424 --Rollover asssessmnet items if there is none for the section and group combination
3425 OPEN cur_ass_item(p_unit_cd,p_version_number,p_cal_type,p_dest_ci_sequence_number,l_unit_ass_item_group_id);
3426 FETCH cur_ass_item INTO l_c_var;
3427 IF cur_ass_item%NOTFOUND THEN
3428 FOR v_uai_rec IN c_uai_source_rec(p_unit_cd,p_version_number,p_source_cal_type,p_source_ci_sequence_number,cur_unit_ass_group_rec.unit_ass_item_group_id) LOOP
3429 BEGIN
3430 v_uai_fetched_cnt := v_uai_fetched_cnt +1;
3431 -- all of the below conditions must be true for the
3432 -- insert to proceed, else don't insert this particular
3433 -- IGS_AS_UNITASS_ITEM record
3434
3435 -- Validate reference is valid in destination pattern's UAI records
3436 v_reference := v_uai_rec.reference;
3437
3438 -- This call fetches the assessment type for the assessment Id
3439 v_assessment_type := igs_as_gen_001.assp_get_ai_a_type(v_uai_rec.ass_id);
3440
3441 -- check if the assessment item is examinable
3442 IF igs_as_gen_002.assp_get_atyp_exmnbl(v_assessment_type) = 'Y' THEN
3443 -- Validate reference number is unique for examinable items
3444 IF igs_as_val_uai.assp_val_uai_uniqref(
3445 v_uai_rec.unit_cd,
3446 v_uai_rec.version_number,
3447 v_uai_rec.cal_type,
3448 p_dest_ci_sequence_number,
3449 v_uai_rec.sequence_number,
3450 v_reference,
3451 v_uai_rec.ass_id,
3452 v_message_name) = TRUE THEN
3453 v_uai_continue := TRUE;
3454 ELSE
3455 v_uai_continue := FALSE;
3456 v_uai_error := TRUE;
3457 END IF;
3458 ELSE
3459 v_uai_continue := TRUE;
3460 -- Validate reference number is unique for non-examinable items
3461 IF igs_as_val_uai.assp_val_uai_opt_ref(
3462 v_uai_rec.unit_cd,
3463 v_uai_rec.version_number,
3464 v_uai_rec.cal_type,
3465 p_dest_ci_sequence_number,
3466 v_uai_rec.sequence_number,
3467 v_reference,
3468 v_uai_rec.ass_id,
3469 v_assessment_type,
3470 v_message_name) = FALSE THEN
3471
3472 IF igs_as_gen_002.assp_get_ai_s_type ( v_uai_rec.ass_id) <> 'ASSIGNMENT' THEN
3473 v_uai_continue := TRUE;
3474 v_reference := NULL;
3475 ELSE
3476 v_uai_continue := FALSE;
3477 v_uai_error := TRUE;
3478 END IF;
3479
3480 ELSE
3481 IF NVL(v_reference, 'NULL') = 'NULL' AND
3482 igs_as_gen_002.assp_get_ai_s_type ( v_uai_rec.ass_id) = 'ASSIGNMENT' THEN
3483 v_uai_continue := FALSE;
3484 v_uai_error := TRUE;
3485 END IF;
3486 END IF;
3487 END IF; -- if examinable
3488
3489
3490 IF v_uai_continue = TRUE THEN
3491 crspl_get_new_exam_cal(
3492 v_uai_rec.exam_cal_type,
3493 v_uai_rec.exam_ci_sequence_number,
3494 v_exam_cal_type,
3495 v_exam_ci_sequence_number,l_old_uoo_id);
3496 OPEN c_uai_seq_num;
3497 FETCH c_uai_seq_num INTO v_uai_seq_num;
3498 CLOSE c_uai_seq_num;
3499
3500 -- Perform insert uai record
3501 x_rowid := NULL;
3502 l_unit_ass_item_id := NULL;
3503
3504 igs_as_unitass_item_pkg.insert_row(
3505 x_rowid => x_rowid,
3506 x_unit_cd => p_unit_cd,
3507 x_version_number => p_version_number,
3508 x_cal_type => p_cal_type,
3509 x_ci_sequence_number => p_dest_ci_sequence_number,
3510 x_ass_id => v_uai_rec.ass_id,
3511 x_sequence_number => v_uai_seq_num,
3512 x_ci_start_dt => v_uai_rec.ci_start_dt,
3513 x_ci_end_dt => v_uai_rec.ci_end_dt,
3514 x_unit_class => v_uai_rec.unit_class,
3515 x_unit_mode => v_uai_rec.unit_mode,
3516 x_location_cd => v_uai_rec.location_cd,
3517 x_due_dt => NULL,
3518 x_reference => v_reference,
3519 x_dflt_item_ind => v_uai_rec.dflt_item_ind,
3520 x_logical_delete_dt => NULL,
3521 x_action_dt => NULL,
3522 x_exam_cal_type => v_exam_cal_type,
3523 x_exam_ci_sequence_number => v_exam_ci_sequence_number,
3524 x_mode => 'R',
3525 x_org_id => igs_ge_gen_003.get_org_id,
3526 x_grading_schema_cd => v_uai_rec.grading_schema_cd,
3527 x_gs_version_number => v_uai_rec.gs_version_number,
3528 x_release_date => v_uai_rec.release_date,
3529 x_unit_ass_item_id => l_unit_ass_item_id, --out parameter
3530 x_description => v_uai_rec.description,
3531 x_unit_ass_item_group_id => l_unit_ass_item_group_id,
3532 x_midterm_mandatory_type_code => v_uai_rec.midterm_mandatory_type_code,
3533 x_midterm_weight_qty => v_uai_rec.midterm_weight_qty,
3534 x_final_mandatory_type_code => v_uai_rec.final_mandatory_type_code,
3535 x_final_weight_qty => v_uai_rec.final_weight_qty
3536 );
3537
3538 v_uai_inserted_cnt := v_uai_inserted_cnt + 1;
3539 END IF; -- v_uai_continue
3540
3541 EXCEPTION
3542 WHEN OTHERS THEN
3543 --Fnd log implementation
3544 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3545 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_008.crsp_ins_uop_uoo.in_exception_section_OTHERS.err_msg',
3546 SUBSTRB('unit ass item id-Pattern:'||v_uai_rec.unit_ass_item_id||' '||
3547 NVL(fnd_message.get,SQLERRM),1,4000));
3548 END IF;
3549 END;
3550 END LOOP; -- c_uai_source_rec
3551 END IF;
3552 CLOSE cur_ass_item;
3553
3554
3555 EXCEPTION
3556 WHEN OTHERS THEN
3557 --Fnd log implementation
3558 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3559 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_008.crsp_ins_uop_uoo.in_exception_section_OTHERS.err_msg',
3560 SUBSTRB('Unit Ass Item Group Id-Pattern:'||cur_unit_ass_group_rec.unit_ass_item_group_id||' '||
3561 NVL(fnd_message.get,SQLERRM),1,4000));
3562 END IF;
3563
3564 IF cur_unitassgrp_new%ISOPEN THEN
3565 CLOSE cur_unitassgrp_new;
3566 END IF;
3567 END;
3568 END LOOP; -- cur_unit_ass_group
3569
3570
3571 -- The following block of code covers all possible cases described
3572 -- in the "outcome grid" of the specification document.
3573 -- ie, (6 rows * 6 cols) = 36 cases. From Left->Right; Top->Down;
3574 -- ref spec: row 1 in the outcome grid.
3575 -- no uoo records were selected
3576
3577 IF (c_uoo_source_rec%ROWCOUNT = 0) THEN
3578 -- no uai records were selected
3579 IF (v_uai_fetched_cnt = 0) THEN
3580 p_message_name := 'IGS_PS_NO_UOO_AND_UAI_ROLLED';
3581 v_ret_val := TRUE;
3582 -- no uai inserted, uai error NOT flagged
3583 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
3584 p_message_name := 'IGS_PS_UOO_NO_UOO_TOBE_ROLLED';
3585 v_ret_val := TRUE;
3586 -- no uai inserted, uai error flagged
3587 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
3588 p_message_name := 'IGS_PS_INV_NO_UOO_ROLLED';
3589 v_ret_val := FALSE;
3590 -- partial uai inserted, uai error NOT flagged
3591 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
3592 IF v_successful_pattern_mbr = FALSE THEN
3593 p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
3594 ELSE
3595 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3596 END IF;
3597 v_ret_val := TRUE;
3598 -- partial uai inserted, uai error flagged
3599 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
3600 IF v_successful_pattern_mbr = FALSE THEN
3601 p_message_name := 'IGS_PS_PARTILROLL_USI';
3602 ELSE
3603 p_message_name := 'IGS_PS_PRINV_NO_UOO_ROLLED';
3604 END IF;
3605 v_ret_val := FALSE;
3606 -- all uai inserted
3607 ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
3608 IF v_successful_pattern_mbr = FALSE THEN
3609 p_message_name := 'IGS_PS_SUCCESS_ROLL_UOO_UAI';
3610 ELSE
3611 p_message_name := 'IGS_PS_SUCCESSROLL_UOO_UAI';
3612 END IF;
3613 v_ret_val := TRUE;
3614 END IF;
3615 -- ref spec: row 2 in the outcome grid.
3616 -- no uoo inserted, uoo error NOT flagged
3617 ELSIF ( v_uoo_inserted_cnt = 0 AND v_uoo_error = FALSE) THEN
3618 -- no uai records were selected
3619 IF (v_uai_fetched_cnt = 0) THEN
3620 p_message_name := 'IGS_PS_UOO_NO_UAI_TOBE_ROLLED';
3621 v_ret_val := TRUE;
3622 -- no uai inserted, uai error NOT flagged
3623 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
3624 p_message_name := 'IGS_PS_NO_UOO_UAI_ROLLED';
3625 v_ret_val := TRUE;
3626 -- no uai inserted, uai error flagged
3627 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
3628 p_message_name := 'IGS_PS_INV_UOO_ROLLED';
3629 v_ret_val := FALSE;
3630 -- partial uai inserted, uai error NOT flagged
3631 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
3632 IF v_successful_pattern_mbr = FALSE THEN
3633 p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
3634 ELSE
3635 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3636 END IF;
3637 v_ret_val := TRUE;
3638 -- partial uai inserted, uai error flagged
3639 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
3640 IF v_successful_pattern_mbr = FALSE THEN
3641 p_message_name := 'IGS_PS_PARROLL_UAI_INVLD_DATA';
3642 ELSE
3643 p_message_name := 'IGS_PS_PRINV_NO_UOO_OBS_DATA';
3644 END IF;
3645 v_ret_val := FALSE;
3646 -- all uai inserted
3647 ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
3648 IF v_successful_pattern_mbr = FALSE THEN
3649 p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
3650 ELSE
3651 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3652 END IF;
3653 v_ret_val := TRUE;
3654 END IF;
3655 -- ref spec: row 3 in the outcome grid.
3656 -- no uoo inserted, uoo error flagged
3657 ELSIF ( v_uoo_inserted_cnt = 0 AND v_uoo_error = TRUE) THEN
3658 -- no uai records were selected
3659 IF (v_uai_fetched_cnt = 0) THEN
3660 p_message_name := 'IGS_PS_INV_NO_UAI_TOBE_ROLLED';
3661 v_ret_val := FALSE;
3662 -- no uai inserted, uai error NOT flagged
3663 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
3664 p_message_name := 'IGS_PS_INV_NO_UAI_OBS_DATA';
3665 v_ret_val := FALSE;
3666 -- no uai inserted, uai error flagged
3667 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
3668 p_message_name := 'IGS_PS_INV_NO_UAI_OBS_DATA';
3669 v_ret_val := FALSE;
3670 -- partial uai inserted, uai error NOT flagged
3671 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
3672 IF v_successful_pattern_mbr = FALSE THEN
3673 p_message_name := 'IGS_PS_INVALID_DATA';
3674 ELSE
3675 p_message_name := 'IGS_PS_INV_UAI_PAR_ROLL';
3676 END IF;
3677 v_ret_val := FALSE;
3678 -- partial uai inserted, uai error flagged
3679 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
3680 IF v_successful_pattern_mbr = FALSE THEN
3681 p_message_name := 'IGS_PS_PARROLL_USI_INVALID';
3682 ELSE
3683 p_message_name := 'IGS_PS_PRINV_NO_UOO_INVALID';
3684 END IF;
3685 v_ret_val := FALSE;
3686 -- all uai inserted
3687 ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
3688 IF v_successful_pattern_mbr = FALSE THEN
3689 p_message_name := 'IGS_PS_NOTROLLED_INVALID_DATA';
3690 ELSE
3691 p_message_name := 'IGS_PS_INV_ALL_UAI_ROLLED';
3692 END IF;
3693 v_ret_val := FALSE;
3694 END IF;
3695 -- ref spec: row 4 in the outcome grid.
3696 -- partial uoo inserted, uoo error NOT flagged
3697 ELSIF ((v_uoo_inserted_cnt < c_uoo_source_rec%ROWCOUNT OR v_tro_recs_skipped = TRUE) AND v_uoo_error = FALSE) THEN
3698 -- no uai records were selected
3699 IF (v_uai_fetched_cnt = 0) THEN
3700 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3701 v_ret_val := TRUE;
3702 -- no uai inserted, uai error NOT flagged
3703 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
3704 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3705 v_ret_val := TRUE;
3706 -- no uai inserted, uai error flagged
3707 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
3708 p_message_name := 'IGS_PS_INV_PARROLL_UOO_OBSDAT';
3709 v_ret_val := FALSE;
3710 -- partial uai inserted, uai error NOT flagged
3711 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
3712 IF v_successful_pattern_mbr = FALSE THEN
3713 p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
3714 ELSE
3715 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3716 END IF;
3717 v_ret_val := TRUE;
3718 -- partial uai inserted, uai error flagged
3719 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
3720 IF v_successful_pattern_mbr = FALSE THEN
3721 p_message_name := 'IGS_PS_PARTIALROLL_UAI';
3722 ELSE
3723 p_message_name := 'IGS_PS_PRINV_PARROL_UOO_OBS';
3724 END IF;
3725 v_ret_val := FALSE;
3726 -- all uai inserted
3727 ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
3728 IF v_successful_pattern_mbr = FALSE THEN
3729 p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
3730 ELSE
3731 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3732 END IF;
3733 v_ret_val := TRUE;
3734 END IF;
3735 -- ref spec: row 5 in the outcome grid.
3736 -- partial uoo inserted, uoo error flagged
3737 ELSIF ((v_uoo_inserted_cnt < c_uoo_source_rec%ROWCOUNT OR v_tro_recs_skipped = TRUE) AND v_uoo_error = TRUE) THEN
3738 -- no uai records were selected
3739 IF (v_uai_fetched_cnt = 0) THEN
3740 p_message_name := 'IGS_PS_PRINV_PARROL_UOO_OBS';
3741 v_ret_val := FALSE;
3742 -- no uai inserted, uai error NOT flagged
3743 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
3744 p_message_name := 'IGS_PS_PRINV_NO_UAI_ROL_OBS';
3745 v_ret_val := FALSE;
3746 -- no uai inserted, uai error flagged
3747 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
3748 p_message_name := 'IGS_PS_PRINV_NO_UAI_INVALID';
3749 v_ret_val := FALSE;
3750 -- partial uai inserted, uai error NOT flagged
3751 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
3752 IF v_successful_pattern_mbr = FALSE THEN
3753 p_message_name := 'IGS_PS_PARTIALROLL_UOO_INVALI';
3754 ELSE
3755 p_message_name := 'IGS_PS_PRINV_PARROLL_UAI';
3756 END IF;
3757 v_ret_val := FALSE;
3758 -- partial uai inserted, uai error flagged
3759 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
3760 IF v_successful_pattern_mbr = FALSE THEN
3761 p_message_name := 'IGS_PS_PARROLL_UOO_AND_UAI';
3762 ELSE
3763 p_message_name := 'IGS_PS_PRINV_UOO_UAI';
3764 END IF;
3765 v_ret_val := FALSE;
3766 -- all uai inserted
3767 ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
3768 IF v_successful_pattern_mbr = FALSE THEN
3769 p_message_name := 'IGS_PS_PARTIALROLL_UOO_INVDAT';
3770 ELSE
3771 p_message_name := 'IGS_PS_PRINV_ALL_UAI_ROLLED';
3772 END IF;
3773 v_ret_val := FALSE;
3774 END IF;
3775 -- ref spec: row 6 in the outcome grid.
3776 -- all uoo inserted
3777 ELSIF (v_uoo_inserted_cnt = c_uoo_source_rec%ROWCOUNT) THEN
3778 -- no uai records were selected
3779 IF (v_uai_fetched_cnt = 0) THEN
3780 p_message_name := 'IGS_PS_SUCCESSROLL_UOO_UAI';
3781 v_ret_val := TRUE;
3782 -- no uai inserted, uai error NOT flagged
3783 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = FALSE) THEN
3784 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3785 v_ret_val := TRUE;
3786 -- no uai inserted, uai error flagged
3787 ELSIF (v_uai_inserted_cnt = 0 AND v_uai_error = TRUE) THEN
3788 p_message_name := 'IGS_PS_INV_ALL_UOO_ROLLED';
3789 v_ret_val := FALSE;
3790 -- partial uai inserted, uai error NOT flagged
3791 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = FALSE) THEN
3792 IF v_successful_pattern_mbr = FALSE THEN
3793 p_message_name := 'IGS_PS_PARTIALROLL_UOO_USI';
3794 ELSE
3795 p_message_name := 'IGS_PS_PARROLL_UOO_UAI';
3796 END IF;
3797 v_ret_val := TRUE;
3798 -- partial uai inserted, uai error flagged
3799 ELSIF (v_uai_inserted_cnt < v_uai_fetched_cnt AND v_uai_error = TRUE) THEN
3800 IF v_successful_pattern_mbr = FALSE THEN
3801 p_message_name := 'IGS_PS_PARTIALROLL_UAI_UAIINV';
3802 ELSE
3803 p_message_name := 'IGS_PS_PRINV_ALL_UOO_ROLLED';
3804 END IF;
3805 v_ret_val := FALSE;
3806 -- all uai inserted
3807 ELSIF (v_uai_inserted_cnt = v_uai_fetched_cnt) THEN
3808 IF v_successful_pattern_mbr = FALSE THEN
3809 p_message_name := 'IGS_PS_SUCCESS_ROLL_UOO_UAI';
3810 ELSE
3811 p_message_name := 'IGS_PS_SUCCESSROLL_UOO_UAI';
3812 END IF;
3813 v_ret_val := TRUE;
3814 END IF;
3815 END IF;
3816
3817 IF c_uoo_source_rec%ISOPEN THEN
3818 CLOSE c_uoo_source_rec;
3819 END IF;
3820
3821 --Added by sarakshi, as a part of bug#2332807
3822 --If no record has been entered in details table igs_ge_s_loog_entry
3823 --then delete the parent record.
3824 OPEN cur_check(p_log_creation_date);
3825 FETCH cur_check INTO l_var;
3826 IF cur_check%NOTFOUND THEN
3827 igs_ge_s_log_pkg.delete_row(x_rowid=>l_rowid);
3828 END IF;
3829 CLOSE cur_check;
3830
3831 RETURN v_ret_val;
3832
3833 EXCEPTION
3834 WHEN OTHERS THEN
3835 IF c_uoo_source_rec%ISOPEN THEN
3836 CLOSE c_uoo_source_rec;
3837 END IF;
3838 IF c_uop_dest_rec%ISOPEN THEN
3839 CLOSE c_uop_dest_rec;
3840 END IF;
3841 IF c_uai_source_rec%ISOPEN THEN
3842 CLOSE c_uai_source_rec;
3843 END IF;
3844 IF c_uoo_rec_exists%ISOPEN THEN
3845 CLOSE c_uoo_rec_exists;
3846 END IF;
3847 IF c_latest_gs_version%ISOPEN THEN
3848 CLOSE c_latest_gs_version;
3849 END IF;
3850 IF c_uoo_seq_num%ISOPEN THEN
3851 CLOSE c_uoo_seq_num;
3852 END IF;
3853 App_Exception.Raise_Exception;
3854 END;
3855
3856 -- jbegum As part of bug#2563596 the call to IGS_GE_GEN_003.genp_ins_log_entry was modified .
3857 -- The concatenated string being passed to parameter p_key has the substring FND_MESSAGE.GET_STRING('IGS', p_message_name)
3858 -- removed as this was causing the TBH procedure IGS_GE_S_LOG_ENTRY_PKG.INSERT_ROW to throw up an invalid value error,which
3859 -- was in turn causing function IGS_PS_GEN_008.crsp_ins_uop_uoo to throw up an unhandled exception.
3860 -- Also the concatenated string being passed to parameter p_text has only p_message_name concatenated to it instead of
3861 -- FND_MESSAGE.GET_STRING('IGS', p_message_name)
3862
3863 --Enhancement bug 1800179, pmarada
3864 -- insert record into log entry table
3865
3866 EXCEPTION
3867 WHEN OTHERS THEN
3868 DECLARE
3869 CURSOR cur_org_unit (cp_uoo_id IN NUMBER) IS
3870 SELECT owner_org_unit_cd
3871 FROM igs_ps_unit_ofr_opt
3872 WHERE uoo_id = cp_uoo_id;
3873 lcur_org_unit cur_org_unit%rowtype;
3874 BEGIN
3875 --Fnd log implementation
3876 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3877 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_008.crsp_ins_uop_uoo.in_exception_section_OTHERS.err_msg',
3878 SUBSTRB('From the Main Exception of crsp_ins_uop_uoo'||' '||
3879 NVL(fnd_message.get,SQLERRM),1,4000));
3880 END IF;
3881
3882 OPEN cur_org_unit (l_old_uoo_id);
3883 FETCH cur_org_unit INTO lcur_org_unit;
3884
3885 IGS_GE_GEN_003.genp_ins_log_entry (
3886 'USEC-ROLL' ,
3887 p_log_creation_date, -- This will be accepted AS parameter AND defaulted TO NULL;
3888 lcur_org_unit.owner_org_unit_cd || ',' || l_old_uoo_id || ',' || p_source_cal_type ||
3889 ',' || p_source_ci_sequence_number,
3890 NULL,
3891 lcur_org_unit.owner_org_unit_cd || ',' || l_old_uoo_id || ',' || p_source_cal_type ||
3892 ',' || p_source_ci_sequence_number || ',' || p_message_name);
3893
3894 CLOSE cur_org_unit;
3895 END;
3896 END crsp_ins_uop_uoo;
3897
3898 END igs_ps_gen_008;
[Home] [Help]