[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_CAL_RSR_VAL
Source
1 PACKAGE BODY igs_ps_cal_rsr_val as
2 /* $Header: IGSPS78B.pls 120.2 2006/01/24 00:37:47 sarakshi ship $ */
3
4 ------------------------------------------------------------------
5 --Created by : pradhakr, Oracle IDC
6 --Date created:
7 --
8 --Purpose: Package Body contains code for procedures/Functions defined in
9 -- package specification . Also body includes Functions/Procedures
10 -- private to it .
11 --
12 --
13 --Known limitations/enhancements and/or remarks:
14 --
15 --Change History:
16 -- Who When What
17 --sarakshi 25-Nov-2003 Bug#3191862, created local procedure update_unit_version and modified procedure update_enroll_offer_unit
18 --sarakshi 02-sep-2003 Enh#3052452,removed the reference of the columnsup_unit_allowed_ind and sub_unit_allowed_ind
19 --vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified del_reserved_seating.
20 -- sarakshi 10-Apr-2003 Bug#2550388, modified procedure del_reserved_seating to replace hard coded log messages with lookup values
21 -- shtatiko 30-OCT-2002 Modified calles to igs_ps_unit_ver_pkg.update_row to incorporate addition of
22 -- auditable_ind, audit_permission_ind, max_auditors_allowed columns.
23 -- Added auditable_ind, audit_permission_ind to update_row call of igs_ps_unit_ofr_opt_pkg.
24 -- This has been done as part of Bug# 2636716.
25 -- jbegum 17 April 02 As part of bug fix of bug #2322290 and bug#2250784
26 -- Removed the following 4 columns
27 -- BILLING_CREDIT_POINTS,BILLING_HRS,FIN_AID_CP,FIN_AID_HRS
28 -- from calls to IGS_PS_UNIT_VER_PKG.
29 -- prraj 14-Feb-2002 Added column NON_STD_USEC_IND to the tbh calls for
30 -- pkg IGS_PS_UNIT_OFR_OPT_PKG (Bug# 2224366)
31 -- ddey 01-FEB-2002 Added columns anon_unit_grading_ind and anon_assess_grading_ind in the calls
32 -- for the package IGS_PS_UNIT_OFR_OPT_PKG and IGS_PS_UNIT_VER_PKG
33 -- ayedubat 20/6/2001 Added one new procedure ,update_enroll_offer_unit
34 -- msrinivi 16 Aug,2001 Added new col rev_account_cd to igs_ps_unit_ver_pkg,igs_ps_unit_ofr_opt_pkg
35 -- TBH calls
36 -------------------------------------------------------------------
37
38 PROCEDURE log_messages ( p_msg_name VARCHAR2 ,
39 p_msg_val VARCHAR2
40 ) IS
41 ------------------------------------------------------------------
42 --Created by : pradhakr, Oracle IDC
43 --Date created: 23/May/2001
44 --
45 --Purpose: This procedure is private to this package body .
46 -- The procedure logs all the parameter values ,
47 -- table values
48 --
49 --
50 --Known limitations/enhancements and/or remarks:
51 --
52 --Change History:
53 --Who When What
54 -------------------------------------------------------------------
55 BEGIN
56 FND_MESSAGE.SET_NAME('IGS','IGS_PS_DEL_PRIORITY_LOG');
57 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
58 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
59 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
60 END log_messages ;
61
62 PROCEDURE del_reserved_seating ( errbuf OUT NOCOPY VARCHAR2,
63 retcode OUT NOCOPY NUMBER,
64 p_teach_prd IN VARCHAR2,
65 p_org_unit_cd IN VARCHAR2,
66 p_unit_cd IN igs_ps_unit_ofr_opt.unit_cd%TYPE,
67 p_version_number IN igs_ps_unit_ofr_opt.version_number%TYPE,
68 p_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
69 p_unit_class IN igs_ps_unit_ofr_opt.unit_class%TYPE,
70 p_unit_mode IN igs_ps_unit_ofr_opt_v.unit_mode%TYPE,
71 p_org_id IN NUMBER
72 ) IS
73 ------------------------------------------------------------------
74 --Created by : pradhakr, Oracle IDC
75 --Date created: 23/May/2001
76 --
77 --Purpose:
78 --
79 --Known limitations/enhancements and/or remarks:
80 --
81 --Change History:
82 --Who When What
83 --sarakshi 12-Jan-2006 Bug#4926548, modified cursor cur_uoo1 from performance perspective.
84 --sommukhe 27-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter abort_flag.
85 --sarakshi 10-Sep-2004 Enh#3882537, modifed cursor cur_uoo1 to exclude INACTIVE units and rearranged the cursor.
86 --sarakshi 22-sep-2003 Enh#3052452,added column relation-type,sup_uoo_id,default_enroll_flag in the call of to igs_ps_unit_ofr_opt_pkg.update_row
87 --vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include
88 -- new parameter not_multiple_section_flag.
89 --sarakshi 10_apr-2003 Bug#2550388, modified procedure to replace hard coded log message with
90 -- lookups values
91 --Pradhakr 23-Jul-2001 Added three new columns in he TBH call
92 -- as part of Enrollment Build process.
93 -- (Enh Bug# 1832130)
94 -------------------------------------------------------------------
95
96 l_calendar_type igs_ps_unit_ofr_opt.cal_type%TYPE;
97 l_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE;
98
99 CURSOR cur_uoo1( p_cal_type VARCHAR2,
100 p_ci_sequence_number NUMBER) IS
101 SELECT uoov.*,uoov.rowid row_id,uc.unit_mode,loc.description location_description
102 FROM igs_ps_unit_ofr_opt_all uoov,igs_as_unit_class_all uc, igs_ad_location_all loc,
103 igs_ps_unit_ver_all uv,
104 igs_ps_unit_stat st
105 WHERE uoov.cal_type = p_cal_type AND
106 uoov.ci_sequence_number = p_ci_sequence_number AND
107 (uoov.owner_org_unit_cd = p_org_unit_cd OR p_org_unit_cd IS NULL) AND
108 (uoov.unit_cd = p_unit_cd OR p_unit_cd IS NULL) AND
109 (uoov.location_cd = p_location_cd OR p_location_cd IS NULL) AND
110 (uoov.unit_class = p_unit_class OR p_unit_class IS NULL) AND
111 (uc.unit_mode = p_unit_mode OR p_unit_mode IS NULL) AND
112 (uoov.version_number = p_version_number OR p_version_number IS NULL) AND
113 uoov.unit_section_status IN ('PLANNED','CANCELLED','NOT_OFFERED') AND
114 uoov.location_cd=loc.location_cd AND
115 uoov.unit_class=uc.unit_class AND
116 uoov.unit_cd=uv.unit_cd AND
117 uoov.version_number=uv.version_number AND
118 uv.unit_status=st.unit_status AND
119 st.s_unit_status <>'INACTIVE';
120
121 CURSOR cur_check_enr (cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
122 SELECT 'X'
123 FROM igs_en_su_attempt_all
124 WHERE uoo_id= cp_uoo_id
125 AND ROWNUM <2;
126 l_c_var VARCHAR2(1);
127
128 my_continue EXCEPTION;
129
130 CURSOR cur_priority (l_uoo_id NUMBER) IS
131 SELECT * FROM igs_ps_rsv_usec_pri_v
132 WHERE uoo_id = l_uoo_id;
133
134 CURSOR cur_preference(l_rsv_usec_pri_id igs_ps_rsv_usec_pri_v.rsv_usec_pri_id%TYPE) IS
135 SELECT a.rowid FROM igs_ps_rsv_usec_prf a
136 WHERE a.rsv_usec_pri_id = l_rsv_usec_pri_id;
137
138 l_cur_priority_row cur_priority%ROWTYPE;
139 l_cur_preference_row cur_preference%ROWTYPE;
140
141 BEGIN
142 -- Set the multiorg id
143 igs_ge_gen_003.set_org_id(p_org_id);
144
145 -- logs all the parameters
146 log_messages(igs_fi_gen_gl.get_lkp_meaning('IGS_PS_LOG_PARAMETERS','TEACHING_PERIOD')||' : ',p_teach_prd);
147 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','ORG_UNIT_CD')||' : ',p_org_unit_cd);
148 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','UNIT_CD')||' : ',p_unit_cd);
149 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','UNIT_VER_NUM')||' : ',p_version_number);
150 log_messages(igs_fi_gen_gl.get_lkp_meaning('ORG_STRUCTURE_TYPE','LOCATION')||' : ',p_location_cd);
151 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','UNIT_CLASS')||' : ',p_unit_class);
152 log_messages(igs_fi_gen_gl.get_lkp_meaning('IGS_PS_LOG_PARAMETERS','UNIT_MODE')||' : ',p_unit_mode);
153 FND_FILE.PUT_LINE(FND_FILE.LOG,NULL);
154 FND_FILE.PUT_LINE(FND_FILE.LOG,NULL);
155
156 -- Checking whether Organisation unit code or Unit code is present or not.
157 -- If both the values are not available then it will terminate the process.
158
159 IF ((p_org_unit_cd IS NULL) AND (p_unit_cd IS NULL) ) THEN
160 FND_MESSAGE.SET_NAME('IGS','IGS_PS_ORG_OR_UNIT_MUST');
161 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
162 IGS_GE_MSG_STACK.ADD;
163 APP_EXCEPTION.RAISE_EXCEPTION ;
164
165 ELSE
166 -- Extracting the values of Calendar type and sequence number
167 l_calendar_type := RTRIM(SUBSTR(p_teach_prd, 101, 10));
168 l_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_teach_prd, 113, 8)));
169
170
171 FOR r_cur_uoo IN cur_uoo1(l_calendar_type, l_sequence_number)
172 LOOP
173 BEGIN
174
175 OPEN cur_check_enr(r_cur_uoo.uoo_id);
176 FETCH cur_check_enr INTO l_c_var;
177 IF cur_check_enr%FOUND THEN
178 CLOSE cur_check_enr;
179 RAISE my_continue;
180 END IF;
181 CLOSE cur_check_enr;
182
183 -- Writing into the log file
184 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','UNIT_CD')||' : ',r_cur_uoo.unit_cd);
185 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','UNIT_VER_NUM')||' : ',r_cur_uoo.version_number);
186 log_messages(igs_fi_gen_gl.get_lkp_meaning('ORG_STRUCTURE_TYPE','LOCATION')||' : ',r_cur_uoo.location_description);
187 log_messages(igs_fi_gen_gl.get_lkp_meaning('LEGACY_TOKENS','UNIT_CLASS')||' : ',r_cur_uoo.unit_class);
188 log_messages(igs_fi_gen_gl.get_lkp_meaning('IGS_PS_LOG_PARAMETERS','UNIT_MODE')||' : ',r_cur_uoo.unit_mode);
189 FND_FILE.PUT_LINE(FND_FILE.LOG,NULL);
190
191
192 -- Updating Reserved Seating Allowed value to 'N'
193 -- Added auditable_ind and audit_permission_ind to the following call as part of Bug# 2636716 by shtatiko
194 igs_ps_unit_ofr_opt_pkg.update_row(x_rowid => r_cur_uoo.row_id,
195 x_unit_cd => r_cur_uoo.unit_cd,
196 x_version_number => r_cur_uoo.version_number,
197 x_cal_type => r_cur_uoo.cal_type,
198 x_ci_sequence_number => r_cur_uoo.ci_sequence_number,
199 x_location_cd => r_cur_uoo.location_cd,
200 x_unit_class => r_cur_uoo.unit_class,
201 x_uoo_id => r_cur_uoo.uoo_id,
202 x_ivrs_available_ind => r_cur_uoo.ivrs_available_ind,
203 x_call_number => r_cur_uoo.call_number,
204 x_unit_section_status => r_cur_uoo.unit_section_status,
205 x_unit_section_start_date => r_cur_uoo.unit_section_start_date,
206 x_unit_section_end_date => r_cur_uoo.unit_section_end_date,
207 x_enrollment_actual => r_cur_uoo.enrollment_actual,
208 x_waitlist_actual => r_cur_uoo.waitlist_actual,
209 x_offered_ind => r_cur_uoo.offered_ind,
210 x_state_financial_aid => r_cur_uoo.state_financial_aid,
211 x_grading_schema_prcdnce_ind => r_cur_uoo.grading_schema_prcdnce_ind,
212 x_federal_financial_aid => r_cur_uoo.federal_financial_aid,
213 x_unit_quota => r_cur_uoo.unit_quota,
214 x_unit_quota_reserved_places => r_cur_uoo.unit_quota_reserved_places,
215 x_institutional_financial_aid => r_cur_uoo.institutional_financial_aid,
216 x_grading_schema_cd => r_cur_uoo.grading_schema_cd,
217 x_gs_version_number => r_cur_uoo.gs_version_number,
218 x_unit_contact => r_cur_uoo.unit_contact,
219 x_mode => 'R',
220 x_ss_enrol_ind => r_cur_uoo.ss_enrol_ind,
221 x_owner_org_unit_cd => r_cur_uoo.owner_org_unit_cd,
222 x_attendance_required_ind => r_cur_uoo.attendance_required_ind,
223 x_reserved_seating_allowed => 'N',
224 x_ss_display_ind => r_cur_uoo.ss_display_ind,
225 x_special_permission_ind => r_cur_uoo.special_permission_ind,
226 x_dir_enrollment => r_cur_uoo.dir_enrollment,
227 x_enr_from_wlst => r_cur_uoo.enr_from_wlst,
228 x_inq_not_wlst => r_cur_uoo.inq_not_wlst,
229 x_rev_account_cd => r_cur_uoo.rev_account_cd,
230 x_anon_unit_grading_ind => r_cur_uoo.anon_unit_grading_ind,
231 x_anon_assess_grading_ind => r_cur_uoo.anon_assess_grading_ind,
232 x_non_std_usec_ind => r_cur_uoo.non_std_usec_ind,
233 x_auditable_ind => r_cur_uoo.auditable_ind,
234 x_audit_permission_ind => r_cur_uoo.audit_permission_ind,
235 x_not_multiple_section_flag => r_cur_uoo.not_multiple_section_flag,
236 x_sup_uoo_id => r_cur_uoo.sup_uoo_id,
237 x_relation_type => r_cur_uoo.relation_type,
238 x_default_enroll_flag => r_cur_uoo.default_enroll_flag,
239 x_abort_flag => r_cur_uoo.abort_flag
240 );
241
242 -- Deleting all the Priority and Preference
243
244 FOR pri IN cur_priority(r_cur_uoo.uoo_id) LOOP
245 FOR pref IN cur_preference(pri.rsv_usec_pri_id) LOOP
246 igs_ps_rsv_usec_prf_pkg.delete_row(
247 x_rowid=>pref.rowid
248 );
249 END LOOP;
250 igs_ps_rsv_usec_pri_pkg.delete_row(
251 x_rowid=>pri.row_id
252 );
253 END LOOP;
254
255 EXCEPTION
256 WHEN my_continue THEN
257 NULL; -- continue with the next record
258 END;
259 END LOOP;
260 END IF;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 retcode := 2;
265 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
266 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
267
268 END del_reserved_seating;
269
270 PROCEDURE update_unit_version ( p_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE ,
271 p_version_number igs_ps_unit_ver_all.version_number%TYPE ,
272 p_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE ,
273 p_sequence_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE,
274 p_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE,
275 p_sequence_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE
276 ) IS
277 ------------------------------------------------------------------
278 --Created by : sarakshi, Oracle IDC
279 --Date created: 25-Nov-2003
280 --
281 --Purpose: To update the last enrolled and offered calendar for a unit version.
282 --
283 --
284 --
285 --Known limitations/enhancements and/or remarks:
286 --
287 --Change History:
288 --Who When What
289 --sarakshi 30-Apr-2004 Bug#3568858, Added columns ovrd_wkld_val_flag, workload_val_code in the TBH call igs_ps_unit_ver_pkg.update_row
290 -------------------------------------------------------------------
291 CURSOR cur_unit_ver IS
292 SELECT *
293 FROM igs_ps_unit_ver
294 WHERE unit_cd = p_unit_cd
295 AND version_number = p_version_number;
296 cur_unit_ver_rec cur_unit_ver%ROWTYPE;
297
298 BEGIN
299
300 --Update the unit version record
301 OPEN cur_unit_ver;
302 FETCH cur_unit_ver INTO cur_unit_ver_rec;
303
304 igs_ps_unit_ver_pkg.update_row (
305 X_Mode => 'R' ,
306 X_RowId => cur_unit_ver_rec.Row_Id ,
307 X_Unit_Cd => cur_unit_ver_rec.Unit_Cd ,
308 X_Version_Number => cur_unit_ver_rec.Version_Number ,
309 X_Start_Dt => cur_unit_ver_rec.Start_Dt ,
310 X_Review_Dt => cur_unit_ver_rec.Review_Dt ,
311 X_Expiry_Dt => cur_unit_ver_rec.Expiry_Dt ,
312 X_End_Dt => cur_unit_ver_rec.End_Dt ,
313 X_Unit_Status => cur_unit_ver_rec.Unit_Status ,
314 X_Title => cur_unit_ver_rec.Title ,
315 X_Short_Title => cur_unit_ver_rec.Short_Title ,
316 X_Title_Override_Ind => cur_unit_ver_rec.Title_Override_Ind ,
317 X_Abbreviation => cur_unit_ver_rec.Abbreviation ,
318 X_Unit_Level => cur_unit_ver_rec.Unit_Level ,
319 X_Credit_Point_Descriptor => cur_unit_ver_rec.Credit_Point_Descriptor ,
320 X_Enrolled_Credit_Points => cur_unit_ver_rec.Enrolled_Credit_Points ,
321 X_Points_Override_Ind => cur_unit_ver_rec.Points_Override_Ind ,
322 X_Supp_Exam_Permitted_Ind => cur_unit_ver_rec.Supp_Exam_Permitted_Ind ,
323 X_Coord_Person_Id => cur_unit_ver_rec.Coord_Person_Id ,
324 X_Owner_Org_Unit_Cd => cur_unit_ver_rec.Owner_Org_Unit_Cd ,
325 X_Owner_Ou_Start_Dt => cur_unit_ver_rec.Owner_Ou_Start_Dt ,
326 X_Award_Course_Only_Ind => cur_unit_ver_rec.Award_Course_Only_Ind ,
327 X_Research_Unit_Ind => cur_unit_ver_rec.Research_Unit_Ind ,
328 X_Industrial_Ind => cur_unit_ver_rec.Industrial_Ind ,
329 X_Practical_Ind => cur_unit_ver_rec.Practical_Ind ,
330 X_Repeatable_Ind => cur_unit_ver_rec.Repeatable_Ind ,
331 X_Assessable_Ind => cur_unit_ver_rec.Assessable_Ind ,
332 X_Achievable_Credit_Points => cur_unit_ver_rec.Achievable_Credit_Points ,
333 X_Points_Increment => cur_unit_ver_rec.Points_Increment ,
334 X_Points_Min => cur_unit_ver_rec.Points_Min ,
335 X_Points_Max => cur_unit_ver_rec.Points_Max ,
336 X_Unit_Int_Course_Level_Cd => cur_unit_ver_rec.Unit_Int_Course_Level_Cd ,
337 X_Subtitle => NULL ,
338 X_Subtitle_Modifiable_Flag => cur_unit_ver_rec.Subtitle_Modifiable_Flag ,
339 X_Approval_Date => cur_unit_ver_rec.Approval_Date ,
340 X_Lecture_Credit_Points => cur_unit_ver_rec.Lecture_Credit_Points ,
341 X_Lab_Credit_Points => cur_unit_ver_rec.Lab_Credit_Points ,
342 X_Other_Credit_Points => cur_unit_ver_rec.Other_Credit_Points ,
343 X_Clock_Hours => cur_unit_ver_rec.Clock_Hours ,
344 X_Work_Load_Cp_Lecture => cur_unit_ver_rec.Work_Load_Cp_Lecture ,
345 X_Work_Load_Cp_Lab => cur_unit_ver_rec.Work_Load_Cp_Lab ,
346 X_Continuing_Education_Units => cur_unit_ver_rec.Continuing_Education_Units ,
347 X_Enrollment_Expected => cur_unit_ver_rec.Enrollment_Expected ,
348 X_Enrollment_Minimum => cur_unit_ver_rec.Enrollment_Minimum ,
349 X_Enrollment_Maximum => cur_unit_ver_rec.Enrollment_Maximum ,
350 X_Advance_Maximum => cur_unit_ver_rec.Advance_Maximum ,
351 X_State_Financial_Aid => cur_unit_ver_rec.State_Financial_Aid ,
352 X_Federal_Financial_Aid => cur_unit_ver_rec.Federal_Financial_Aid ,
353 X_Institutional_Financial_Aid => cur_unit_ver_rec.Institutional_Financial_Aid ,
354 X_Same_Teaching_Period => cur_unit_ver_rec.Same_Teaching_Period ,
355 X_Max_Repeats_For_Credit => cur_unit_ver_rec.Max_Repeats_For_Credit ,
356 X_Max_Repeats_For_Funding => cur_unit_ver_rec.Max_Repeats_For_Funding ,
357 X_Max_Repeat_Credit_Points => cur_unit_ver_rec.Max_Repeat_Credit_Points ,
358 X_Same_Teach_Period_Repeats_Cp => cur_unit_ver_rec.Same_Teach_Period_Repeats_Cp,
359 X_Same_Teach_Period_Repeats => cur_unit_ver_rec.Same_Teach_Period_Repeats ,
360 X_Attribute_Category => cur_unit_ver_rec.Attribute_Category ,
361 X_Attribute1 => cur_unit_ver_rec.Attribute1 ,
362 X_Attribute2 => cur_unit_ver_rec.Attribute2 ,
363 X_Attribute3 => cur_unit_ver_rec.Attribute3 ,
364 X_Attribute4 => cur_unit_ver_rec.Attribute4 ,
365 X_Attribute5 => cur_unit_ver_rec.Attribute5 ,
366 X_Attribute6 => cur_unit_ver_rec.Attribute6 ,
367 X_Attribute7 => cur_unit_ver_rec.Attribute7 ,
368 X_Attribute8 => cur_unit_ver_rec.Attribute8 ,
369 X_Attribute9 => cur_unit_ver_rec.Attribute9 ,
370 X_Attribute10 => cur_unit_ver_rec.Attribute10 ,
371 X_Attribute11 => cur_unit_ver_rec.Attribute11 ,
372 X_Attribute12 => cur_unit_ver_rec.Attribute12 ,
373 X_Attribute13 => cur_unit_ver_rec.Attribute13 ,
374 X_Attribute14 => cur_unit_ver_rec.Attribute14 ,
375 X_Attribute15 => cur_unit_ver_rec.Attribute15 ,
376 X_Attribute16 => cur_unit_ver_rec.Attribute16 ,
377 X_Attribute17 => cur_unit_ver_rec.Attribute17 ,
378 X_Attribute18 => cur_unit_ver_rec.Attribute18 ,
379 X_Attribute19 => cur_unit_ver_rec.Attribute19 ,
380 X_Attribute20 => cur_unit_ver_rec.Attribute20 ,
381 X_Subtitle_Id => cur_unit_ver_rec.Subtitle_Id ,
382 X_Work_Load_Other => cur_unit_ver_rec.Work_Load_Other ,
383 X_Contact_Hrs_Lecture => cur_unit_ver_rec.Contact_Hrs_Lecture ,
384 X_Contact_Hrs_Lab => cur_unit_ver_rec.Contact_Hrs_Lab ,
385 X_Contact_Hrs_Other => cur_unit_ver_rec.Contact_Hrs_Other ,
386 X_Non_Schd_Required_Hrs => cur_unit_ver_rec.Non_Schd_Required_Hrs ,
387 X_Exclude_From_Max_Cp_Limit => cur_unit_ver_rec.Exclude_From_Max_Cp_Limit ,
388 X_Record_Exclusion_Flag => cur_unit_ver_rec.Record_Exclusion_Flag ,
389 X_Ss_Display_Ind => cur_unit_ver_rec.Ss_Display_Ind ,
390 X_Cal_Type_Enrol_Load_Cal => NVL(p_cal_type_enrol_load_cal,cur_unit_ver_rec.cal_type_enrol_load_cal) ,
391 X_Sequence_Num_Enrol_Load_Cal => NVL(p_sequence_num_enrol_load_cal,cur_unit_ver_rec.sequence_num_enrol_load_cal) ,
392 X_Cal_Type_Offer_Load_Cal => NVL(p_cal_type_offer_load_cal,cur_unit_ver_rec.cal_type_offer_load_cal) ,
393 X_Sequence_Num_Offer_Load_Cal => NVL(p_sequence_num_offer_load_cal,cur_unit_ver_rec.sequence_num_offer_load_cal) ,
394 X_Curriculum_Id => cur_unit_ver_rec.Curriculum_Id ,
395 X_Override_Enrollment_Max => cur_unit_ver_rec.Override_Enrollment_Max ,
396 X_Rpt_Fmly_Id => cur_unit_ver_rec.Rpt_Fmly_Id ,
397 X_Unit_Type_Id => cur_unit_ver_rec.Unit_Type_Id ,
398 X_Special_Permission_Ind => cur_unit_ver_rec.Special_Permission_Ind ,
399 x_ivr_enrol_ind => cur_unit_ver_rec.ivr_enrol_ind ,
400 x_ss_enrol_ind => cur_unit_ver_rec.ss_enrol_ind,
401 x_rev_account_cd => cur_unit_ver_rec.rev_account_cd,
402 x_claimable_hours => cur_unit_ver_rec.claimable_hours ,
403 x_anon_unit_grading_ind => cur_unit_ver_rec.anon_unit_grading_ind ,
404 x_anon_assess_grading_ind => cur_unit_ver_rec.anon_assess_grading_ind ,
405 x_auditable_ind => cur_unit_ver_rec.auditable_ind ,
406 x_audit_permission_ind => cur_unit_ver_rec.audit_permission_ind ,
407 x_max_auditors_allowed => cur_unit_ver_rec.max_auditors_allowed ,
408 x_billing_credit_points => cur_unit_ver_rec.billing_credit_points,
409 x_ovrd_wkld_val_flag => cur_unit_ver_rec.ovrd_wkld_val_flag,
410 x_workload_val_code => cur_unit_ver_rec.workload_val_code,
411 x_billing_hrs => cur_unit_ver_rec.billing_hrs
412 );
413
414 CLOSE cur_unit_ver;
415
416 END update_unit_version;
417
418 PROCEDURE update_enroll_offer_unit(errbuf OUT NOCOPY VARCHAR2,
419 retcode OUT NOCOPY NUMBER,
420 p_org_id IN IGS_PS_UNIT_VER.ORG_ID%TYPE,
421 p_load_calendar IN VARCHAR2) IS
422 ------------------------------------------------------------------
423 --Created by : ayedubat, Oracle IDC
424 --Date created: 12/6/2001
425 --
426 --Purpose: To update the last enrolled and offered calendar for unit.
427 --
428 --
429 --
430 --Known limitations/enhancements and/or remarks:
431 --
432 --Change History:
433 --Who When What
434 --sarakshi 12-Jul-2004 Bug#3729462, Added the predicate DELETE_FLAG to cursor cur_units
435 --sarakshi 25-Nov-2003 Bug#3191862, Modified the cursors cur_units,cur_teach_to_load also moved the
436 -- updation in a local procedure and calling only once for a unit using local variable.
437 --sarakshi 06-Nov-2003 Enh#3116171, Added billing_credit_points for igs_ps_unit_ver_pkg.update_row
438 -------------------------------------------------------------------
439 l_input_load_cal_type IGS_CA_INST_ALL.CAL_TYPE%TYPE;
440 l_input_load_sequence_number IGS_CA_INST_ALL.SEQUENCE_NUMBER%TYPE;
441
442 l_recent_teach_start_date IGS_CA_INST_ALL.START_DT%TYPE;
443
444 CURSOR cur_load_to_teach( p_input_load_cal_type IGS_CA_INST_ALL.CAL_TYPE%TYPE,
445 p_input_load_sequence_number IGS_CA_INST_ALL.SEQUENCE_NUMBER%TYPE
446 ) IS
447 SELECT teach_cal_type,
448 teach_ci_sequence_number,
449 teach_start_dt
450 FROM igs_ca_load_to_teach_v
451 WHERE load_cal_type = p_input_load_cal_type AND
452 load_ci_sequence_number = p_input_load_sequence_number
453 ORDER BY teach_start_dt DESC;
454 cur_load_to_teach_rec cur_load_to_teach%ROWTYPE;
455
456 CURSOR cur_units( p_input_load_cal_type IGS_CA_INST_ALL.CAL_TYPE%TYPE,
457 p_input_load_sequence_number IGS_CA_INST_ALL.SEQUENCE_NUMBER%TYPE ) IS
458 SELECT DISTINCT uop.unit_cd,
459 uop.version_number
460 FROM igs_ps_unit_ofr_pat uop , igs_ca_load_to_teach_v lteach ,igs_ps_unit_ver_all uv, igs_ps_unit_stat us
461 WHERE lteach.load_cal_type = p_input_load_cal_type AND
462 lteach.load_ci_sequence_number = p_input_load_sequence_number AND
463 uop.cal_type = lteach.teach_cal_type AND
464 uop.ci_sequence_number = lteach.teach_ci_sequence_number AND
465 uop.unit_cd = uv.unit_cd AND
466 uop.version_number = uv.version_number AND
467 uv.unit_status = us.unit_status AND
468 us.s_unit_status <> 'INACTIVE' AND
469 uop.delete_flag='N';
470
471 CURSOR cur_ps_unit_ofr_opt_enroll( p_unit_cd IGS_PS_UNIT_OFR_OPT_ALL.unit_cd%TYPE,
472 p_version_number IGS_PS_UNIT_OFR_OPT_ALL.version_number%TYPE,
473 p_start_date IGS_CA_INST_ALL.start_dt%TYPE ) IS
474 SELECT uoov.cal_type,
475 uoov.ci_sequence_number,
476 ci.start_dt
477 FROM igs_ps_unit_ofr_opt uoov, igs_ca_inst ci
478 WHERE uoov.cal_type = ci.cal_type AND
479 uoov.ci_sequence_number = ci.sequence_number AND
480 uoov.enrollment_actual > 0 AND
481 ci.start_dt <= p_start_date AND
482 uoov.unit_cd = p_unit_cd AND
483 uoov.version_number = p_version_number
484 ORDER BY ci.start_dt DESC;
485 cur_ps_unit_ofr_opt_enroll_rec cur_ps_unit_ofr_opt_enroll%ROWTYPE;
486
487 CURSOR cur_ps_unit_ofr_opt_offer( p_unit_cd IGS_PS_UNIT_OFR_OPT_ALL.unit_cd%TYPE,
488 p_version_number IGS_PS_UNIT_OFR_OPT_ALL.version_number%TYPE,
489 p_start_date IGS_CA_INST_ALL.start_dt%TYPE ) IS
490 SELECT uoov.cal_type,
491 uoov.ci_sequence_number,
492 ci.start_dt
493 FROM igs_ps_unit_ofr_opt uoov, igs_ca_inst ci
494 WHERE uoov.cal_type = ci.cal_type AND
495 uoov.ci_sequence_number = ci.sequence_number AND
496 uoov.unit_section_status NOT IN ('PLANNED') AND
497 uoov.offered_ind = 'Y' AND
498 ci.start_dt <= p_start_date AND
499 uoov.unit_cd = p_unit_cd AND
500 uoov.version_number = p_version_number
501 ORDER BY ci.start_dt DESC;
502 cur_ps_unit_ofr_opt_offer_rec cur_ps_unit_ofr_opt_offer%ROWTYPE;
503
504 CURSOR cur_teach_to_load( p_teach_cal_type IGS_CA_INST_ALL.CAL_TYPE%TYPE,
505 p_teach_sequence_number IGS_CA_INST_ALL.SEQUENCE_NUMBER%TYPE ) IS
506 SELECT load_cal_type,
507 load_ci_sequence_number,
508 load_start_dt
509 FROM igs_ca_teach_to_load_v
510 WHERE teach_cal_type = p_teach_cal_type AND
511 teach_ci_sequence_number = p_teach_sequence_number
512 ORDER BY load_start_dt DESC;
513 cur_teach_to_load_rec cur_teach_to_load%ROWTYPE;
514
515 CURSOR cur_unit_ver_enroll(p_unit_cd IGS_PS_UNIT_OFR_OPT_ALL.unit_cd%TYPE,
516 p_version_number IGS_PS_UNIT_OFR_OPT_ALL.version_number%TYPE) IS
517 SELECT ci.start_dt start_date
518 FROM igs_ps_unit_ver uv,igs_ca_inst ci
519 WHERE uv.unit_cd = p_unit_cd AND
520 uv.version_number = p_version_number AND
521 uv.cal_type_enrol_load_cal = ci.cal_type AND
522 uv.sequence_num_enrol_load_cal = ci.sequence_number;
523 cur_unit_ver_enroll_rec cur_unit_ver_enroll%ROWTYPE;
524
525 CURSOR cur_unit_ver_offer(p_unit_cd IGS_PS_UNIT_OFR_OPT_ALL.unit_cd%TYPE,
526 p_version_number IGS_PS_UNIT_OFR_OPT_ALL.version_number%TYPE) IS
527 SELECT ci.start_dt start_date
528 FROM igs_ps_unit_ver uv,igs_ca_inst ci
529 WHERE uv.unit_cd = p_unit_cd AND
530 uv.version_number = p_version_number AND
531 uv.cal_type_offer_load_cal = ci.cal_type AND
532 uv.sequence_num_offer_load_cal = ci.sequence_number;
533 cur_unit_ver_offer_rec cur_unit_ver_offer%ROWTYPE;
534
535
536 l_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE ;
537 l_sequence_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
538 l_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE;
539 l_sequence_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE;
540
541
542 BEGIN
543
544 -- Set the multiorg id
545 igs_ge_gen_003.set_org_id(p_org_id);
546
547 -- Initialize the RetCode
548 retcode := 0;
549
550 -- Extracting the Calendar Type , Sequence Number , Start Date and End date from the input parameter,p_load_calendar.
551 l_input_load_cal_type := RTRIM(SUBSTR(p_load_calendar,101,10));
552 l_input_load_sequence_number := RTRIM(SUBSTR(p_load_calendar,112,6));
553
554 -- Find all the teaching calendar instances for the given load calendar instance
555 OPEN cur_load_to_teach(l_input_load_cal_type,l_input_load_sequence_number);
556 FETCH cur_load_to_teach INTO cur_load_to_teach_rec;
557
558 -- If teaching calendars found for the given Load Calendar instance then proces the units ,
559 -- otherwise terminate the process
560 IF cur_load_to_teach%FOUND THEN
561
562 -- store the start date of the most recent teaching calendar instance.
563 l_recent_teach_start_date := cur_load_to_teach_rec.teach_start_dt;
564
565 -- Process all the units for all the teaching calendar instances of the given load calendar instance
566 FOR cur_units_rec IN cur_units(l_input_load_cal_type,l_input_load_sequence_number) LOOP
567
568 --Update the Last Enrolled Calendar of an Unit.
569
570 --Initialise the local variables inside the loop
571 l_cal_type_enrol_load_cal := NULL;
572 l_sequence_num_enrol_load_cal := NULL;
573 l_cal_type_offer_load_cal := NULL;
574 l_sequence_num_offer_load_cal := NULL;
575
576 --Find the most recent teaching calendar instance of the unit.
577 OPEN cur_ps_unit_ofr_opt_enroll(cur_units_rec.unit_cd,cur_units_rec.version_number,l_recent_teach_start_date);
578 FETCH cur_ps_unit_ofr_opt_enroll INTO cur_ps_unit_ofr_opt_enroll_rec;
579
580 IF (cur_ps_unit_ofr_opt_enroll%FOUND) THEN
581
582 -- Fectch the Load Calendar Instance for the teaching calendar instance of a unit.
583 OPEN cur_teach_to_load(cur_ps_unit_ofr_opt_enroll_rec.cal_type,cur_ps_unit_ofr_opt_enroll_rec.ci_sequence_number);
584 FETCH cur_teach_to_load INTO cur_teach_to_load_rec;
585
586 --Fetch the Enrolled Calendar Instance for the unit from IGS_PS_UNIT_VER.
587 OPEN cur_unit_ver_enroll(cur_units_rec.unit_cd,cur_units_rec.version_number);
588 FETCH cur_unit_ver_enroll INTO cur_unit_ver_enroll_rec;
589
590 -- If Load Calendar Instance is greater than the Enrolled Calendar Instance or there is no calendar instance defined for the unit
591 -- then Update the table,IGS_PS_UNIT_VER
592
593 IF ( cur_unit_ver_enroll%NOTFOUND OR
594 TRUNC(cur_unit_ver_enroll_rec.start_date) < TRUNC(cur_teach_to_load_rec.load_start_dt) ) THEN
595
596 -- Set the value of cal_type_enrol_load_cal and sequence_num_enrol_load_cal of IGS_PS_UNIT_VER table.
597 l_cal_type_enrol_load_cal := cur_teach_to_load_rec.load_cal_type;
598 l_sequence_num_enrol_load_cal := cur_teach_to_load_rec.load_ci_sequence_number;
599
600 END IF;
601 CLOSE cur_unit_ver_enroll;
602 CLOSE cur_teach_to_load;
603 END IF;
604 CLOSE cur_ps_unit_ofr_opt_enroll;
605
606 -- Update the Last Offered Calendar of an Unit
607
608 -- Find the most recent teaching calendar instance of the unit.
609 OPEN cur_ps_unit_ofr_opt_offer(cur_units_rec.unit_cd,cur_units_rec.version_number,l_recent_teach_start_date);
610 FETCH cur_ps_unit_ofr_opt_offer INTO cur_ps_unit_ofr_opt_offer_rec;
611
612 IF (cur_ps_unit_ofr_opt_offer%FOUND) THEN
613
614 -- Fectch the Load Calendar Instance for the teaching calendar instance of a unit.
615 OPEN cur_teach_to_load(cur_ps_unit_ofr_opt_offer_rec.cal_type,cur_ps_unit_ofr_opt_offer_rec.ci_sequence_number);
616 FETCH cur_teach_to_load INTO cur_teach_to_load_rec;
617
618 --Fetch the Offered Calendar Instance for the unit from IGS_PS_UNIT_VER.
619 OPEN cur_unit_ver_offer(cur_units_rec.unit_cd,cur_units_rec.version_number);
620 FETCH cur_unit_ver_offer INTO cur_unit_ver_offer_rec;
621
622 -- If Load Calendar Instance is greater than the Enrolled Calendar Instance then Update the table,IGS_PS_UNIT_VER.
623 IF ( cur_unit_ver_offer%NOTFOUND OR
624 TRUNC(cur_unit_ver_offer_rec.start_date) < TRUNC(cur_teach_to_load_rec.load_start_dt) ) THEN
625
626 -- Set the value of cal_type_offer_load_cal and sequence_num_offer_load_cal of IGS_PS_UNIT_VER table.
627 l_cal_type_offer_load_cal := cur_teach_to_load_rec.load_cal_type ;
628 l_sequence_num_offer_load_cal := cur_teach_to_load_rec.load_ci_sequence_number;
629
630 END IF; -- End of Updation
631 CLOSE cur_unit_ver_offer;
632 CLOSE cur_teach_to_load;
633
634 END IF;
635 CLOSE cur_ps_unit_ofr_opt_offer;
636
637 --Perform the updation of the unit version record
638 IF l_sequence_num_enrol_load_cal IS NOT NULL OR l_sequence_num_offer_load_cal IS NOT NULL THEN
639 update_unit_version ( p_unit_cd => cur_units_rec.unit_cd,
640 p_version_number => cur_units_rec.version_number,
641 p_cal_type_enrol_load_cal => l_cal_type_enrol_load_cal,
642 p_sequence_num_enrol_load_cal => l_sequence_num_enrol_load_cal,
643 p_cal_type_offer_load_cal => l_cal_type_offer_load_cal ,
644 p_sequence_num_offer_load_cal => l_sequence_num_offer_load_cal
645 ) ;
646 END IF;
647
648 END LOOP; -- all units are processed.
649 CLOSE cur_load_to_teach;
650
651 ELSE
652
653 FND_MESSAGE.SET_NAME('IGS','IGS_PS_TEACH_CAL_NOT_FOUND');
654 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
655
656 END IF;
657 EXCEPTION
658 WHEN OTHERS THEN
659 retcode := 2;
660 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
661 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
662
663 END update_enroll_offer_unit;
664
665 END igs_ps_cal_rsr_val;