[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_PS_PKG
Source
1 PACKAGE BODY igs_he_ps_pkg AS
2 /* $Header: IGSHE15B.pls 120.1 2006/02/07 14:52:47 jbaber noship $ */
3
4 -----------------------------------------------------------------------------
5 -- Change History
6 -- Who When What
7 --sbaliga 8-Apr-02 Modified package to reflect datamodel changes in
8 -- IGS_HE_ST_PROG_ALL and IGS_HE_POOUS_ALL tables
9 -- as part of #2278825.
10 -- rshergil 03-Jan-02 Created Package to rollover Program
11 -- Version information relating to
12 -- 1.UK Statistics - Program details
13 -- 2.Program Offering Option Unit Set HESA Details
14 -- smaddali 20-Aug-03 Modified procedure for hefd208 bug#2717751 to add funding_source column to igs_he_poous_all tbh call
15 -- ayedubat 28-Aug-03 Created a new procedure, create_prg_cc_rec to copy the Hesa
16 -- Statistic Cost Center Details for HE207 Enhancement bug, 2717753
17 -- jbaber 25-Jan-05 Modified for HE355 - Org Unit Cost Center Link
18 -- jbaber 24-Nov-05 Included exclude_flag column for HE305
19 -----------------------------------------------------------------------------
20
21 PROCEDURE copy_prog_version(
22 p_old_course_cd IN VARCHAR2,
23 p_old_version_number IN NUMBER,
24 p_new_course_cd IN VARCHAR2,
25 p_new_version_number IN NUMBER,
26 p_message_name OUT NOCOPY VARCHAR2,
27 p_status OUT NOCOPY NUMBER)
28 AS
29 cst_max_error_range CONSTANT NUMBER := -20999;
30 cst_min_error_range CONSTANT NUMBER := -20000;
31 cst_ret_message_name CONSTANT VARCHAR2(240) := 'IGS_HE_FAIL_COPYPRGVER_DET';
32
33
34 CURSOR gc_hsp_old_rec IS
35 SELECT *
36 FROM igs_he_st_prog_all
37 WHERE course_cd = p_old_course_cd
38 AND version_number = p_old_version_number;
39 gv_hsp_old_rec gc_hsp_old_rec%ROWTYPE;
40
41
42 CURSOR gc_hsp_new_rec IS
43 SELECT hesa_st_prog_id
44 FROM igs_he_st_prog_all
45 WHERE course_cd = p_new_course_cd
46 AND version_number = p_new_version_number;
47 gv_hsp_new_rec gc_hsp_new_rec%ROWTYPE;
48
49 CURSOR gc_hpus_old_rec IS
50 SELECT *
51 FROM igs_he_poous_all
52 WHERE course_cd = p_old_course_cd
53 AND crv_version_number = p_old_version_number;
54 gv_hpus_old_rec gc_hpus_old_rec%ROWTYPE;
55
56 CURSOR gc_hpus_new_rec (p_cal_type igs_he_poous.cal_type%TYPE,
57 p_location_cd igs_he_poous.location_cd%TYPE,
58 p_attendance_mode igs_he_poous.attendance_mode%TYPE,
59 p_attendance_type igs_he_poous.attendance_type%TYPE,
60 p_unit_set_cd igs_he_poous.unit_set_cd%TYPE,
61 p_us_version_number igs_he_poous.us_version_number%TYPE)
62 IS
63 SELECT hesa_poous_id
64 FROM igs_he_poous_all
65 WHERE course_cd = p_new_course_cd
66 AND crv_version_number = p_new_version_number
67 AND cal_type = p_cal_type
68 AND location_cd = p_location_cd
69 AND attendance_mode = p_attendance_mode
70 AND attendance_type = p_attendance_type
71 AND unit_set_cd = p_unit_set_cd
72 AND us_version_number = p_us_version_number;
73 gv_hpus_new_rec gc_hpus_new_rec%ROWTYPE;
74
75 CURSOR gc_hpo_old_rec
76 IS SELECT *
77 FROM igs_he_poous_ou_all
78 WHERE course_cd = p_old_course_cd
79 AND crv_version_number = p_old_version_number ;
80 gv_hpo_old_rec gc_hpo_old_rec%ROWTYPE;
81
82 CURSOR gc_hpo_new_rec (p_cal_type igs_he_poous_ou.cal_type%TYPE,
83 p_location_cd igs_he_poous_ou.location_cd%TYPE,
84 p_attendance_mode igs_he_poous_ou.attendance_mode%TYPE,
85 p_attendance_type igs_he_poous_ou.attendance_type%TYPE,
86 p_unit_set_cd igs_he_poous_ou.unit_set_cd%TYPE,
87 p_us_version_number igs_he_poous_ou.us_version_number%TYPE,
88 p_organization_unit igs_he_poous_ou.organization_unit%TYPE)
89 IS SELECT hesa_poous_ou_id
90 FROM igs_he_poous_ou_all
91 WHERE course_cd = p_new_course_cd
92 AND crv_version_number = p_new_version_number
93 AND cal_type = p_cal_type
94 AND location_cd = p_location_cd
95 AND attendance_mode = p_attendance_mode
96 AND attendance_type = p_attendance_type
97 AND unit_set_cd = p_unit_set_cd
98 AND us_version_number = p_us_version_number
99 AND organization_unit = p_organization_unit;
100 gv_hpo_new_rec gc_hpo_new_rec%ROWTYPE;
101
102 --Procedure inserts into IGS_HE_ST_PROG_ALL table
103
104 PROCEDURE cr_he_st_pr_rec ( p_new_course_cd igs_he_st_prog.course_cd%TYPE,
105 p_new_version_number igs_he_st_prog.version_number%TYPE) IS
106
107 BEGIN
108
109 DECLARE
110 v_hesa_seq_num igs_he_st_prog.hesa_st_prog_id%TYPE;
111
112 CURSOR c_hesa_seq_num IS
113 SELECT igs_he_st_prog_all_s.NEXTVAL
114 FROM dual;
115
116 x_rowid VARCHAR2(250);
117 l_org_id NUMBER(15);
118
119 BEGIN
120
121 OPEN c_hesa_seq_num;
122 FETCH c_hesa_seq_num INTO v_hesa_seq_num;
123 CLOSE c_hesa_seq_num;
124
125 l_org_id := igs_ge_gen_003.get_org_id;
126 x_rowid := NULL;
127
128 igs_he_st_prog_all_pkg.insert_row(
129 X_ROWID => x_rowid,
130 X_HESA_ST_PROG_ID => v_hesa_seq_num,
131 X_ORG_ID => l_org_id,
132 X_COURSE_CD => p_new_course_cd,
133 X_VERSION_NUMBER => p_new_version_number,
134 X_TEACHER_TRAIN_PROG_ID => gv_hsp_old_rec.teacher_train_prog_id,
135 X_ITT_PHASE => gv_hsp_old_rec.itt_phase,
136 X_BILINGUAL_ITT_MARKER => gv_hsp_old_rec.bilingual_itt_marker,
137 X_TEACHING_QUAL_SOUGHT_SECTOR => gv_hsp_old_rec.teaching_qual_sought_sector,
138 X_TEACHING_QUAL_SOUGHT_SUBJ1 => gv_hsp_old_rec.teaching_qual_sought_subj1,
139 X_TEACHING_QUAL_SOUGHT_SUBJ2 => gv_hsp_old_rec.teaching_qual_sought_subj2,
140 X_TEACHING_QUAL_SOUGHT_SUBJ3 => gv_hsp_old_rec.teaching_qual_sought_subj3,
141 X_LOCATION_OF_STUDY => gv_hsp_old_rec.location_of_study,
142 X_OTHER_INST_PROV_TEACHING1 => gv_hsp_old_rec.other_inst_prov_teaching1,
143 X_OTHER_INST_PROV_TEACHING2 => gv_hsp_old_rec.other_inst_prov_teaching2,
144 X_PROP_TEACHING_IN_WELSH => gv_hsp_old_rec.prop_teaching_in_welsh,
145 X_PROP_NOT_TAUGHT => gv_hsp_old_rec.prop_not_taught,
146 X_CREDIT_TRANSFER_SCHEME => gv_hsp_old_rec.credit_transfer_scheme,
147 X_RETURN_TYPE => gv_hsp_old_rec.return_type,
148 X_DEFAULT_AWARD => gv_hsp_old_rec.default_award,
149 X_PROGRAM_CALC => gv_hsp_old_rec.program_calc,
150 X_LEVEL_APPLICABLE_TO_FUNDING => gv_hsp_old_rec.level_applicable_to_funding,
151 X_FRANCHISING_ACTIVITY => gv_hsp_old_rec.franchising_activity,
152 X_NHS_FUNDING_SOURCE => gv_hsp_old_rec.nhs_funding_source,
153 X_FE_PROGRAM_MARKER => gv_hsp_old_rec.fe_program_marker,
154 X_FEE_BAND => gv_hsp_old_rec.fee_band,
155 X_FUNDABILITY => gv_hsp_old_rec.fundability,
156 X_FTE_INTENSITY => gv_hsp_old_rec.fte_intensity,
157 X_TEACH_PERIOD_START_DT => gv_hsp_old_rec.teach_period_start_dt,
158 X_TEACH_PERIOD_END_DT => gv_hsp_old_rec.teach_period_end_dt,
159 X_MODE => 'R',
160 X_IMPLIED_FUND_RATE => gv_hsp_old_rec.implied_fund_rate,
161 X_GOV_INITIATIVES_CD => gv_hsp_old_rec.gov_initiatives_cd,
162 X_UNITS_FOR_QUAL => gv_hsp_old_rec.units_for_qual,
163 X_DISADV_UPLIFT_ELIG_CD => gv_hsp_old_rec.disadv_uplift_elig_cd,
164 X_FRANCH_PARTNER_CD => gv_hsp_old_rec.franch_partner_cd,
165 X_FRANCH_OUT_ARR_CD => gv_hsp_old_rec.franch_out_arr_cd,
166 X_EXCLUDE_FLAG => gv_hsp_old_rec.exclude_flag );
167
168 END;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 p_status := 2;
173 IF(SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
174 p_message_name := cst_ret_message_name;
175 ELSE
176 app_exception.raise_exception;
177 END IF;
178 END cr_he_st_pr_rec;
179
180
181 -- Procedure inserts a new record into IGS_HE_PROG_OU_CC table
182 PROCEDURE create_prg_cc_rec (
183 p_old_course_cd igs_he_prog_ou_cc.course_cd%TYPE,
184 p_old_version_number igs_he_prog_ou_cc.version_number%TYPE,
185 p_new_course_cd igs_he_prog_ou_cc.course_cd%TYPE,
186 p_new_version_number igs_he_prog_ou_cc.version_number%TYPE) IS
187 /******************************************************************
188 Created By : AYEDUBAT
189 Date Created By : 13-JUN-2003
190 Purpose : To copy the Hesa Statistic Cost Center Details defined at Program Version Level
191
192 Change History
193 WHO WHEN WHAT
194 ayedubat 28-Aug-03 Created the new procedure for HE207FD bug, 2717753
195 jbaber 25-Jan-05 Modified to use IGS_HE_PROG_OU_CC for HE355 - Org Unit Cost Centre Link
196 ***************************************************************** */
197
198 l_rowid VARCHAR2(25) := NULL;
199 l_hesa_prog_cc_id igs_he_prog_ou_cc.hesa_prog_cc_id%TYPE := NULL ;
200 l_dummy VARCHAR2(1);
201
202 -- Fetch the Cost Centers of the Old version of the Program
203 CURSOR old_prg_cc_dtls_cur( cp_course_cd IGS_HE_PROG_OU_CC.course_cd%TYPE,
204 cp_version_number IGS_HE_PROG_OU_CC.version_number%TYPE) IS
205 SELECT spc.*
206 FROM IGS_HE_PROG_OU_CC spc
207 WHERE spc.course_cd = cp_course_cd
208 AND spc.version_number = cp_version_number;
209
210 -- Check whether the Cost Center record already exist in the new program version
211 CURSOR new_prg_cc_dtls_cur( cp_course_cd IGS_HE_PROG_OU_CC.course_cd%TYPE,
212 cp_version_number IGS_HE_PROG_OU_CC.version_number%TYPE,
213 cp_org_unit_cd IGS_HE_PROG_OU_CC.org_unit_cd%TYPE,
214 cp_cost_centre IGS_HE_PROG_OU_CC.cost_centre%TYPE,
215 cp_subject IGS_HE_PROG_OU_CC.subject%TYPE) IS
216 SELECT 'X'
217 FROM IGS_HE_PROG_OU_CC spc
218 WHERE spc.course_cd = cp_course_cd
219 AND spc.version_number = cp_version_number
220 AND spc.org_unit_cd = cp_org_unit_cd
221 AND spc.cost_centre = cp_cost_centre
222 AND spc.subject = cp_subject;
223
224 BEGIN
225
226 -- Loop through all the records in IGS_HE_PROG_OU_CC table for old version of the program
227 -- and insert if the record does not exist for new course code and version
228 FOR old_prg_cc_dtls_rec IN old_prg_cc_dtls_cur( p_old_course_cd, p_old_version_number) LOOP
229
230 OPEN new_prg_cc_dtls_cur(p_new_course_cd, p_new_version_number, old_prg_cc_dtls_rec.org_unit_cd,
231 old_prg_cc_dtls_rec.cost_centre, old_prg_cc_dtls_rec.subject );
232 FETCH new_prg_cc_dtls_cur INTO l_dummy;
233 IF new_prg_cc_dtls_cur%NOTFOUND THEN
234
235 -- create the new program version cost centre record
236 igs_he_prog_ou_cc_pkg.insert_row (
237 x_rowid => l_rowid,
238 x_hesa_prog_cc_id => l_hesa_prog_cc_id,
239 x_course_cd => p_new_course_cd,
240 x_version_number => p_new_version_number,
241 x_org_unit_cd => old_prg_cc_dtls_rec.org_unit_cd,
242 x_cost_centre => old_prg_cc_dtls_rec.cost_centre,
243 x_subject => old_prg_cc_dtls_rec.subject,
244 x_proportion => old_prg_cc_dtls_rec.proportion,
245 x_mode => 'R' );
246
247 END IF;
248 CLOSE new_prg_cc_dtls_cur;
249 END LOOP;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 p_status := 2;
254 IF(SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
255 p_message_name := cst_ret_message_name;
256 ELSE
257 app_exception.raise_exception;
258 END IF;
259
260 END create_prg_cc_rec;
261
262
263 --Procedure inserts into IGS_HE_POOUS_ALL table
264
265 PROCEDURE cr_he_poo_us_rec (p_new_course_cd igs_he_poous.course_cd%TYPE,
266 p_new_version_number igs_he_poous.crv_version_number%TYPE) IS
267
268 BEGIN
269
270 DECLARE
271 v_poous_seq_num IGS_HE_POOUS.hesa_poous_id%TYPE;
272
273 CURSOR c_poous_seq_num IS
274 SELECT igs_he_poous_all_s.NEXTVAL
275 FROM dual;
276
277 l_org_id NUMBER(15);
278 x_rowid VARCHAR2(250);
279
280 BEGIN
281
282 OPEN c_poous_seq_num;
283 FETCH c_poous_seq_num INTO v_poous_seq_num;
284 CLOSE c_poous_seq_num;
285
286 x_rowid := NULL;
287 l_org_id := igs_ge_gen_003.get_org_id;
288
289 igs_he_poous_all_pkg.insert_row(
290 X_ROWID => x_rowid,
291 X_ORG_ID => l_org_id,
292 X_HESA_POOUS_ID => v_poous_seq_num,
293 X_COURSE_CD => p_new_course_cd,
294 X_CRV_VERSION_NUMBER => p_new_version_number,
295 X_CAL_TYPE => gv_hpus_old_rec.cal_type,
296 X_LOCATION_CD => gv_hpus_old_rec.location_cd,
297 X_ATTENDANCE_MODE => gv_hpus_old_rec.attendance_mode,
298 X_ATTENDANCE_TYPE => gv_hpus_old_rec.attendance_type,
299 X_UNIT_SET_CD => gv_hpus_old_rec.unit_set_cd,
300 X_US_VERSION_NUMBER => gv_hpus_old_rec.us_version_number,
301 X_LOCATION_OF_STUDY => gv_hpus_old_rec.location_of_study,
302 X_MODE_OF_STUDY => gv_hpus_old_rec.mode_of_study,
303 X_UFI_PLACE => gv_hpus_old_rec.ufi_place,
304 X_FRANCHISING_ACTIVITY => gv_hpus_old_rec.franchising_activity,
305 X_TYPE_OF_YEAR => gv_hpus_old_rec.type_of_year,
306 X_LENG_CURRENT_YEAR => gv_hpus_old_rec.leng_current_year,
307 X_GRADING_SCHEMA_CD => gv_hpus_old_rec.grading_schema_cd,
308 X_GS_VERSION_NUMBER => gv_hpus_old_rec.gs_version_number,
309 X_CREDIT_VALUE_YOP1 => gv_hpus_old_rec.credit_value_yop1,
310 X_LEVEL_CREDIT1 => gv_hpus_old_rec.level_credit1,
311 X_CREDIT_VALUE_YOP2 => gv_hpus_old_rec.credit_value_yop2,
312 X_LEVEL_CREDIT2 => gv_hpus_old_rec.level_credit2,
313 X_CREDIT_VALUE_YOP3 => gv_hpus_old_rec.credit_value_yop3,
314 X_LEVEL_CREDIT3 => gv_hpus_old_rec.level_credit3,
315 X_CREDIT_VALUE_YOP4 => gv_hpus_old_rec.credit_value_yop4,
316 X_LEVEL_CREDIT4 => gv_hpus_old_rec.level_credit4,
317 X_FTE_INTENSITY => gv_hpus_old_rec.fte_intensity,
318 X_FTE_CALC_TYPE => gv_hpus_old_rec.fte_calc_type,
319 X_TEACH_PERIOD_START_DT => gv_hpus_old_rec.teach_period_start_dt,
320 X_TEACH_PERIOD_END_DT => gv_hpus_old_rec.teach_period_end_dt,
321 X_OTHER_INSTIT_TEACH1 => gv_hpus_old_rec.other_instit_teach1,
322 X_OTHER_INSTIT_TEACH2 => gv_hpus_old_rec.other_instit_teach2,
323 X_PROP_NOT_TAUGHT => gv_hpus_old_rec.prop_not_taught,
324 X_FUNDABILITY_CD => gv_hpus_old_rec.fundability_cd,
325 X_FEE_BAND => gv_hpus_old_rec.fee_band,
326 X_LEVEL_APPLICABLE_TO_FUNDING => gv_hpus_old_rec.level_applicable_to_funding,
327 X_MODE => 'R',
328 X_FUNDING_SOURCE => gv_hpus_old_rec.funding_source);
329
330 END;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 p_status := 2;
335 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range)
336 THEN
337 p_message_name := cst_ret_message_name;
338 ELSE
339 app_exception.raise_exception;
340 END IF;
341
342 END cr_he_poo_us_rec;
343
344
345 PROCEDURE create_poous_cc_rec (
346 p_old_course_cd igs_he_prog_ou_cc.course_cd%TYPE,
347 p_old_version_number igs_he_prog_ou_cc.version_number%TYPE,
348 p_new_course_cd igs_he_prog_ou_cc.course_cd%TYPE,
349 p_new_version_number igs_he_prog_ou_cc.version_number%TYPE) IS
350 /******************************************************************
351 Created By : jbaber
352 Date Created By : 25-Jan-2005
353 Purpose : To copy the Hesa Statistic Cost Center Details defined at POOUS Level
354
355 Change History
356 WHO WHEN WHAT
357 jbaber 25-Jan-2005 Created the new procedure for HE355 Org Unit Cost Center Link
358 ***************************************************************** */
359
360 CURSOR get_old_poous_cc_dtls_cur IS
361 SELECT
362 poo.cal_type,
363 poo.location_cd,
364 poo.attendance_mode,
365 poo.attendance_type,
366 poo.unit_set_cd,
367 poo.us_version_number,
368 poo.organization_unit,
369 pcc.cost_centre,
370 pcc.subject,
371 pcc.proportion
372 FROM IGS_HE_POOUS_OU_CC pcc,
373 IGS_HE_POOUS_OU_ALL poo
374 WHERE
375 pcc.hesa_poous_ou_id = poo.hesa_poous_ou_id
376 AND poo.course_cd = p_old_course_cd
377 AND poo.crv_version_number = p_old_version_number;
378
379
380 CURSOR get_new_poous_ou_id (cp_cal_type igs_he_poous_ou.cal_type%TYPE,
381 cp_location_cd igs_he_poous_ou.location_cd%TYPE,
382 cp_attendance_mode igs_he_poous_ou.attendance_mode%TYPE,
383 cp_attendance_type igs_he_poous_ou.attendance_type%TYPE,
384 cp_unit_set_cd igs_he_poous_ou.unit_set_cd%TYPE,
385 cp_us_version_number igs_he_poous_ou.us_version_number%TYPE,
386 cp_organization_unit igs_he_poous_ou.organization_unit%TYPE) IS
387 SELECT hesa_poous_ou_id
388 FROM IGS_HE_POOUS_OU_ALL
389 WHERE
390 course_cd = p_new_course_cd
391 AND crv_version_number = p_new_version_number
392 AND cal_type = cp_cal_type
393 AND location_cd = cp_location_cd
394 AND attendance_mode = cp_attendance_mode
395 AND attendance_type = cp_attendance_type
396 AND unit_set_cd = cp_unit_set_cd
397 AND us_version_number = cp_us_version_number
398 AND organization_unit = cp_organization_unit;
399
400
401
402 CURSOR get_new_poous_cc_dtls_cur (cp_poous_ou_id igs_he_poous_ou_cc.hesa_poous_ou_id%TYPE,
403 cp_cost_centre igs_he_poous_ou_cc.cost_centre%TYPE,
404 cp_subject igs_he_poous_ou_cc.subject%TYPE) IS
405 SELECT 'X'
406 FROM IGS_HE_POOUS_OU_CC
407 WHERE
408 hesa_poous_ou_id = cp_poous_ou_id
409 AND cost_centre = cp_cost_centre
410 AND subject = cp_subject;
411
412 l_new_poous_cc_dtls_rec get_new_poous_cc_dtls_cur%ROWTYPE;
413 l_new_poous_ou_id igs_he_poous_ou_all.hesa_poous_ou_id%TYPE;
414
415 x_rowid VARCHAR2(250);
416 x_hesa_poous_cc_id igs_he_poous_ou_cc.hesa_poous_cc_id%TYPE;
417
418 BEGIN
419
420 FOR l_old_poous_cc_dtls_rec IN get_old_poous_cc_dtls_cur
421 LOOP
422
423 OPEN get_new_poous_ou_id ( l_old_poous_cc_dtls_rec.cal_type,
424 l_old_poous_cc_dtls_rec.location_cd,
425 l_old_poous_cc_dtls_rec.attendance_mode,
426 l_old_poous_cc_dtls_rec.attendance_type,
427 l_old_poous_cc_dtls_rec.unit_set_cd,
428 l_old_poous_cc_dtls_rec.us_version_number,
429 l_old_poous_cc_dtls_rec.organization_unit);
430 FETCH get_new_poous_ou_id INTO l_new_poous_ou_id;
431
432 IF get_new_poous_ou_id%FOUND THEN
433
434 OPEN get_new_poous_cc_dtls_cur(l_new_poous_ou_id, l_old_poous_cc_dtls_rec.cost_centre, l_old_poous_cc_dtls_rec.subject);
435 FETCH get_new_poous_cc_dtls_cur INTO l_new_poous_cc_dtls_rec;
436 IF get_new_poous_cc_dtls_cur%NOTFOUND THEN
437
438 x_rowid := NULL;
439 x_hesa_poous_cc_id := NULL;
440
441 igs_he_poous_ou_cc_pkg.insert_row(
442 X_ROWID => x_rowid,
443 X_HESA_POOUS_CC_ID => x_hesa_poous_cc_id,
444 X_HESA_POOUS_OU_ID => l_new_poous_ou_id,
445 X_COST_CENTRE => l_old_poous_cc_dtls_rec.cost_centre,
446 X_SUBJECT => l_old_poous_cc_dtls_rec.subject,
447 X_PROPORTION => l_old_poous_cc_dtls_rec.proportion,
448 X_MODE => 'R');
449
450
451 END IF;
452 CLOSE get_new_poous_cc_dtls_cur;
453
454 END IF;
455 CLOSE get_new_poous_ou_id;
456
457 END LOOP;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 p_status := 2;
462 IF(SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range) THEN
463 p_message_name := cst_ret_message_name;
464 ELSE
465 app_exception.raise_exception;
466 END IF;
467
468 END create_poous_cc_rec;
469
470
471 --Procedure inserts into IGS_HE_POOUS_OU_ALL table
472
473 PROCEDURE cr_he_poo_ou_rec (p_new_course_cd igs_he_poous_ou.course_cd%TYPE,
474 p_new_version_number igs_he_poous_ou.crv_version_number%TYPE) IS
475
476 BEGIN
477
478 DECLARE
479
480 v_pooou_seq_num igs_he_poous_ou.hesa_poous_ou_id%TYPE;
481
482
483 CURSOR c_pooou_seq_num IS
484 SELECT igs_he_poous_ou_all_s.NEXTVAL
485 FROM dual;
486
487 l_org_id NUMBER;
488 x_rowid VARCHAR2(250);
489
490 BEGIN
491
492 OPEN c_pooou_seq_num;
493 FETCH c_pooou_seq_num INTO v_pooou_seq_num;
494 CLOSE c_pooou_seq_num;
495
496 l_org_id := igs_ge_gen_003.get_org_id;
497 x_rowid := NULL;
498
499
500 igs_he_poous_ou_all_pkg.insert_row(
501 X_ROWID => x_rowid,
502 X_HESA_POOUS_OU_ID => v_pooou_seq_num,
503 X_ORG_ID => l_org_id,
504 X_COURSE_CD => p_new_course_cd,
505 X_CRV_VERSION_NUMBER=> p_new_version_number,
506 X_CAL_TYPE => gv_hpo_old_rec.cal_type,
507 X_LOCATION_CD => gv_hpo_old_rec.location_cd,
508 X_ATTENDANCE_MODE => gv_hpo_old_rec.attendance_mode,
509 X_ATTENDANCE_TYPE => gv_hpo_old_rec.attendance_type,
510 X_UNIT_SET_CD => gv_hpo_old_rec.unit_set_cd,
511 X_US_VERSION_NUMBER => gv_hpo_old_rec.us_version_number,
512 X_ORGANIZATION_UNIT => gv_hpo_old_rec.organization_unit,
513 X_PROPORTION => gv_hpo_old_rec.proportion,
514 X_MODE => 'R');
515
516 END;
517
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 p_status := 2;
522 IF (SQLCODE >= cst_max_error_range AND SQLCODE <= cst_min_error_range)
523 THEN
524 p_message_name := cst_ret_message_name;
525 ELSE
526 app_exception.raise_exception;
527 END IF;
528
529
530 END cr_he_poo_ou_rec;
531
532
533 BEGIN
534
535 p_status := 0;
536
537 -- Check Parameter values passed in correctly
538
539 IF p_old_course_cd is NULL OR
540 p_old_version_number is NULL OR
541 p_new_course_cd is NULL OR
542 p_new_version_number is NULL
543 THEN
544 p_status := 2;
545 p_message_name := 'IGS_HE_INV_PARAMS';
546 RETURN;
547 END IF;
548
549
550 -- This checks to see if the specified old IGS_HE_ST_PROG record exists, and if so then need to copy,
551 -- if does not exist in IGS_HE_ST_PROG for new course code and version then insert
552 OPEN gc_hsp_old_rec;
553 FETCH gc_hsp_old_rec into gv_hsp_old_rec;
554 IF gc_hsp_old_rec%FOUND THEN
555 OPEN gc_hsp_new_rec;
556 FETCH gc_hsp_new_rec into gv_hsp_new_rec;
557 IF gc_hsp_new_rec%NOTFOUND THEN
558 cr_he_st_pr_rec(p_new_course_cd,p_new_version_number);
559 END IF;
560 CLOSE gc_hsp_new_rec;
561 END IF;
562 CLOSE gc_hsp_old_rec;
563
564 -- Call the local procedure to copy the Hesa Statistic Cost Center Details
565 -- defined at Program Version Level
566 create_prg_cc_rec(p_old_course_cd, p_old_version_number, p_new_course_cd, p_new_version_number );
567
568
569 -- If IGS_HE_POOUS record does not already exist for new course code and version then call
570 -- the procedure to insert IGS_HE_POOUS records
571 OPEN gc_hpus_old_rec;
572 LOOP
573 FETCH gc_hpus_old_rec INTO gv_hpus_old_rec;
574 EXIT WHEN gc_hpus_old_rec%NOTFOUND;
575 OPEN gc_hpus_new_rec(
576 gv_hpus_old_rec.cal_type,
577 gv_hpus_old_rec.location_cd,
578 gv_hpus_old_rec.attendance_mode,
579 gv_hpus_old_rec.attendance_type,
580 gv_hpus_old_rec.unit_set_cd,
581 gv_hpus_old_rec.us_version_number);
582 FETCH gc_hpus_new_rec INTO gv_hpus_new_rec;
583 IF gc_hpus_new_rec%NOTFOUND THEN
584 cr_he_poo_us_rec(p_new_course_cd, p_new_version_number);
585 END IF;
586 CLOSE gc_hpus_new_rec;
587 END LOOP;
588 CLOSE gc_hpus_old_rec;
589
590
591 -- if IGS_HE_POOUS_OU record does not exist for new course code and version then call the procedure to insert IGS_HE_POOUS_OU records
592
593 OPEN gc_hpo_old_rec;
594 LOOP
595 FETCH gc_hpo_old_rec INTO gv_hpo_old_rec;
596 EXIT WHEN gc_hpo_old_rec%NOTFOUND;
597 OPEN gc_hpo_new_rec(
598 gv_hpo_old_rec.cal_type,
599 gv_hpo_old_rec.location_cd,
600 gv_hpo_old_rec.attendance_mode,
601 gv_hpo_old_rec.attendance_type,
602 gv_hpo_old_rec.unit_set_cd,
603 gv_hpo_old_rec.us_version_number,
604 gv_hpo_old_rec.organization_unit);
605 FETCH gc_hpo_new_rec INTO gv_hpo_new_rec;
606 IF gc_hpo_new_rec%NOTFOUND THEN
607 cr_he_poo_ou_rec(p_new_course_cd, p_new_version_number);
608 END IF;
609 CLOSE gc_hpo_new_rec;
610
611 END LOOP;
612 CLOSE gc_hpo_old_rec;
613
614 -- Call the local procedure to copy the Hesa Statistic Cost Center Details
615 -- defined at POOUS Level
616 -- must be called AFTER POOUS_OU records have been updated!
617 create_poous_cc_rec(p_old_course_cd, p_old_version_number, p_new_course_cd, p_new_version_number );
618
619 EXCEPTION
620
621 WHEN OTHERS THEN
622 p_status := 2;
623 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
624 fnd_message.set_token('NAME','IGS_HE_PS.COPY_PROG_VERSION');
625 igs_ge_msg_stack.add;
626 app_exception.raise_exception;
627
628
629 END copy_prog_version;
630
631
632 END igs_he_ps_pkg;
633