[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_NOTIFY_PKG
Source
1 PACKAGE BODY igs_as_notify_pkg as
2 /* $Header: IGSAS40B.pls 120.1 2006/01/18 22:55:57 swaghmar noship $ */
3 /*
4 || Created By : nmankodi
5 || Created On : 04-FEB-2002
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || (reverse chronological order - newest change first)
11 */
12 FUNCTION get_dt_alias_val (lp_dt_alias IN igs_ca_da_inst.dt_alias%TYPE,
13 lp_teach_cal_type IN igs_ca_inst_all.cal_type%TYPE,
14 lp_teach_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE)
15 RETURN DATE IS
16 /*
17 || Created By : nmankodi
18 || Created On : 04-FEB-2002
19 || Purpose : Get the maximum date alias instance value for a given date alias and teaching calendar instance
20 || Known limitations, enhancements or remarks :
21 || Change History :
22 || Who When What
23 || (reverse chronological order - newest change first)
24 */
25 v_alias_val DATE;
26 BEGIN
27
28 SELECT MAX(daiv.alias_val) INTO v_alias_val
29 FROM igs_ca_da_inst_v daiv
30 WHERE daiv.dt_alias = lp_dt_alias
31 AND daiv.cal_type = lp_teach_cal_type
32 AND daiv.ci_sequence_number = lp_teach_ci_sequence_number;
33
34 RETURN v_alias_val;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
39 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_NOTIFY_PKG.GET_DT_ALIAS_VAL');
40 IGS_GE_MSG_STACK.ADD;
41 APP_EXCEPTION.RAISE_EXCEPTION;
42
43 END get_dt_alias_val; -- get_dt_alias_val
44
45 FUNCTION check_grading_cohort (lp_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
46 lp_grading_period_cd IN igs_as_grd_schema.grading_schema_cd%TYPE,
47 lp_load_cal_type IN igs_ca_inst_all.cal_type%TYPE,
48 lp_load_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE)
49 RETURN BOOLEAN IS
50 /*
51 || Created By : nmankodi
52 || Created On : 04-FEB-2002
53 || Purpose : Check if there are any students in the Unit Section which match the Grading Period Cohort.
54 || Known limitations, enhancements or remarks : The Class Standing check is done last as it is the most costly.
55 || Change History :
56 || Who When What
57 || (reverse chronological order - newest change first)
58 */
59
60 CURSOR c_sua
61 IS
62 SELECT 'X'
63 FROM igs_en_su_attempt sua,
64 igs_en_stdnt_ps_att spa
65 WHERE sua.uoo_id = lp_uoo_id
66 AND sua.person_id = spa.person_id
67 AND sua.course_cd = spa.course_cd
68 AND (EXISTS (SELECT 'X'
69 FROM igs_as_gpc_programs gpr
70 WHERE gpr.course_cd = spa.course_cd
71 AND gpr.course_version_number = spa.version_number
72 AND gpr.grading_period_cd = lp_grading_period_cd)
73 OR EXISTS (SELECT 'X'
74 FROM igs_as_gpc_aca_stndg gas
75 WHERE spa.progression_status = gas.progression_status
76 AND gas.grading_period_cd = lp_grading_period_cd)
77 OR EXISTS (SELECT 'X'
78 FROM igs_pe_prsid_grp_mem pigm,
79 igs_as_gpc_pe_id_grp gpg
80 WHERE spa.person_id = pigm.person_id
81 AND pigm.group_id = gpg.group_id
82 AND gpg.grading_period_cd = lp_grading_period_cd)
83 OR EXISTS (SELECT 'X'
84 FROM igs_as_su_setatmpt susa,
85 igs_as_gpc_unit_sets gus
86 WHERE susa.person_id = spa.person_id
87 AND susa.course_cd = spa.course_cd
88 AND (susa.end_dt is NULL
89 OR susa.rqrmnts_complete_ind = 'Y')
90 AND susa.unit_set_cd = gus.unit_set_cd
91 AND gus.grading_period_cd = lp_grading_period_cd));
92
93 CURSOR c_sua_cs
94 IS
95 SELECT 'X'
96 FROM igs_en_su_attempt sua,
97 igs_en_stdnt_ps_att spa
98 WHERE sua.uoo_id = lp_uoo_id
99 AND sua.person_id = spa.person_id
100 AND sua.course_cd = spa.course_cd
101 AND EXISTS (SELECT 'X'
102 FROM igs_as_gpc_cls_stndg gcs
103 WHERE gcs.grading_period_cd = lp_grading_period_cd
104 AND gcs.class_standing = IGS_PR_GET_CLASS_STD.Get_Class_Standing(spa.person_id,spa.course_cd,'N',SYSDATE,lp_load_cal_type,lp_load_ci_sequence_number));
105
106 v_dummy VARCHAR2(1);
107
108 BEGIN
109
110 IF (lp_uoo_id IS NOT NULL AND lp_grading_period_cd IS NOT NULL) THEN
111 -- Check for any students matching the first four Grading Period Cohorts
112 OPEN c_sua ;
113 FETCH c_sua INTO v_dummy;
114 IF c_sua%FOUND THEN
115 CLOSE c_sua;
116 RETURN TRUE;
117 END IF;
118 CLOSE c_sua;
119
120
121 -- Check for any students matching the Class Standing Grading Period Cohort
122
123 OPEN c_sua_cs;
124 FETCH c_sua_cs INTO v_dummy;
125 IF c_sua_cs%FOUND THEN
126 CLOSE c_sua_cs;
127 RETURN TRUE;
128 END IF;
129 CLOSE c_sua_cs;
130
131 END IF;
132 RETURN FALSE;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
137 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_NOTIFY_PKG.CHECK_GRADING_COHORT');
138 IGS_GE_MSG_STACK.ADD;
139 APP_EXCEPTION.RAISE_EXCEPTION;
140
141 END check_grading_cohort; --check_grading_cohort
142
143
144 PROCEDURE raise_business_event (
145 lp_internal_name IN VARCHAR2,
146 lp_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
147 lp_teach_description IN igs_ca_inst_all.description%TYPE,
148 lp_load_description IN igs_ca_inst_all.description%TYPE,
149 lp_grading_period_start_dt IN DATE,
150 lp_grading_period_end_dt IN DATE,
151 lp_unit_cd IN igs_ps_unit_ofr_opt_all.unit_cd%TYPE,
152 lp_unit_class IN igs_ps_unit_ofr_opt.unit_class%TYPE,
153 lp_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
154 lp_location_description IN igs_ad_location.description%TYPE,
155 lp_title IN igs_ps_unit_ver_all.title%TYPE,
156 lp_short_title IN igs_ps_unit_ver_all.short_title%TYPE,
157 lp_instructor_id IN igs_ps_usec_tch_resp.instructor_id%TYPE )
158 IS
159
160 /*
161 || Created By : nmankodi
162 || Created On : 04-FEB-2002
163 || Purpose : Check if the Business Event has already been raised
164 || and if it has not raise a new event and record it in the Assessment Notification Business Events table.
165 || Known limitations, enhancements or remarks : The Class Standing check is done last as it is the most costly.
166 || Change History :
167 || Who When What
168 || (reverse chronological order - newest change first)
169 */
170
171
172 l_wf_event_t WF_EVENT_T;
173 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
174 l_key NUMBER;
175 v_dummy VARCHAR2(1);
176 l_rowid ROWID;
177 CURSOR c_nbe
178 IS
179 SELECT 'X'
180 FROM igs_as_notify_be nbe
181 WHERE nbe.uoo_id = lp_uoo_id
182 AND nbe.internal_name = lp_internal_name;
183
184 BEGIN
185 IF (lp_uoo_id IS NOT NULL AND lp_internal_name IS NOT NULL) THEN
186 -- Check if the Businees Event has already been raised
187 OPEN c_nbe;
188 FETCH c_nbe INTO v_dummy;
189 IF c_nbe%FOUND THEN
190 CLOSE c_nbe;
191 RETURN;
192 END IF;
193 CLOSE c_nbe;
194 END IF;
195
196 -- Initialize the wf_event_t object
197 WF_EVENT_T.Initialize(l_wf_event_t);
198
199 -- Set the event name
200 l_wf_event_t.setEventName(pEventName => lp_internal_name);
201
202 -- Set the event key
203 l_wf_event_t.setEventKey (pEventKey => lp_internal_name || lp_uoo_id);
204
205 -- Set the parameter list
206 l_wf_event_t.setParameterList (pParameterList => l_wf_parameter_list_t);
207
208 -- Add the parameters to the parameter list
209 wf_event.AddParameterToList (p_Name => 'UOO_ID', p_Value => lp_uoo_id, p_parameterlist => l_wf_parameter_list_t);
210 wf_event.AddParameterToList (p_Name => 'TEACH_DESCRIPTION', p_Value => lp_teach_description,p_parameterlist => l_wf_parameter_list_t);
211 wf_event.AddParameterToList (p_Name => 'LOAD_DESCRIPTION', p_Value => lp_load_description,p_parameterlist => l_wf_parameter_list_t);
212 wf_event.AddParameterToList (p_Name => 'GRADING_PERIOD_START_DT', p_Value => lp_grading_period_start_dt,p_parameterlist => l_wf_parameter_list_t);
213 wf_event.AddParameterToList (p_Name => 'GRADING_PERIOD_END_DT', p_Value => lp_grading_period_end_dt,p_parameterlist => l_wf_parameter_list_t);
214 wf_event.AddParameterToList (p_Name => 'UNIT_CD', p_Value => lp_unit_cd,p_parameterlist => l_wf_parameter_list_t);
215 wf_event.AddParameterToList (p_Name => 'UNIT_CLASS', p_Value => lp_unit_class,p_parameterlist => l_wf_parameter_list_t);
216 wf_event.AddParameterToList (p_Name => 'LOCATION_CD', p_Value => lp_location_cd,p_parameterlist => l_wf_parameter_list_t);
217 wf_event.AddParameterToList (p_Name => 'LOCATION_DESCRIPTION', p_Value => lp_location_description,p_parameterlist => l_wf_parameter_list_t);
218 wf_event.AddParameterToList (p_Name => 'TITLE', p_Value => lp_title,p_parameterlist => l_wf_parameter_list_t);
219 wf_event.AddParameterToList (p_Name => 'SHORT_TITLE', p_Value => lp_short_title,p_parameterlist => l_wf_parameter_list_t);
220 wf_event.AddParameterToList (p_Name => 'INSTRUCTOR_ID', p_Value => lp_instructor_id,p_parameterlist => l_wf_parameter_list_t);
221
222 -- Raise the Business Event
223 WF_EVENT.RAISE (p_event_name => lp_internal_name,
224 p_event_key => lp_internal_name || lp_uoo_id,
225 p_event_data => NULL,
226 p_parameters => l_wf_parameter_list_t);
227
228 -- Record that the business event was created
229 igs_as_notify_be_pkg.insert_row (x_rowid =>l_rowid,
230 x_uoo_id =>lp_uoo_id,
231 x_internal_name => lp_internal_name,
232 x_mode =>'R'
233 );
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
238 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_NOTIFY_PKG.RAISE_BUSINESS_EVENT');
239 IGS_GE_MSG_STACK.ADD;
240 APP_EXCEPTION.RAISE_EXCEPTION;
241
242 END raise_business_event; --raise_business_event
243
244
245 PROCEDURE gen_as_notifications (
246 errbuf OUT NOCOPY VARCHAR2,
247 retcode OUT NOCOPY NUMBER,
248 p_load_calendar IN VARCHAR2,
249 p_attend_advance_offset IN NUMBER,
250 p_attend_start_offset IN NUMBER,
251 p_attend_end_offset IN NUMBER,
252 p_midterm_advance_offset IN NUMBER,
253 p_midterm_start_offset IN NUMBER,
254 p_midterm_end_offset IN NUMBER,
255 p_earlyfinal_advance_offset IN NUMBER,
256 p_earlyfinal_start_offset IN NUMBER,
257 p_earlyfinal_end_offset IN NUMBER,
258 p_final_advance_offset IN NUMBER,
259 p_final_start_offset IN NUMBER,
260 p_final_end_offset IN NUMBER
261 )
262 IS
263
264 /*
265 || Created By : nmankodi
266 || Created On : 04-FEB-2002
267 || Purpose : Check if the Business Event has already been raised
268 || and if it has not raise a new event and record it in the Assessment Notification Business Events table.
269 || Known limitations, enhancements or remarks : The Class Standing check is done last as it is the most costly.
270 || Change History :
271 || Who When What
272 || swaghmar 16-Jan-2006 Bug# 4951054 - Check for disabling UI's
273 || (reverse chronological order - newest change first)
274 */
275 l_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
276 l_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
277
278 CURSOR c_ttl
279 IS
280 SELECT ttl.teach_cal_type,
281 ttl.teach_ci_sequence_number,
282 ttl.teach_description,
283 ttl.load_description
284 FROM igs_ca_teach_to_load_v ttl,
285 igs_ca_inst ci,
286 igs_ca_stat cs
287 WHERE ttl.load_cal_type = l_ld_cal_type
288 AND ttl.load_ci_sequence_number = l_ld_sequence_number
289 AND ttl.teach_cal_type = ci.cal_type
290 AND ttl.teach_ci_sequence_number = ci.sequence_number
291 AND ci.cal_status = cs.cal_status
292 AND cs.s_cal_status = 'ACTIVE';
293
294
295 CURSOR c_uoo (cp_teach_cal_type igs_ca_inst_all.cal_type%TYPE,cp_teach_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
296 IS
297 SELECT uoo.uoo_id,
298 uoo.unit_cd,
299 uoo.unit_class,
300 uoo.location_cd,
301 loc.description location_description,
302 uoo.call_number,
303 uoo.unit_section_start_date,
304 uoo.unit_section_end_date,
305 uoo.attendance_required_ind,
306 uv.title,
307 uv.short_title,
308 utr.instructor_id
309 FROM igs_ps_unit_ofr_opt uoo,
310 igs_ps_unit_ver uv,
311 igs_ps_usec_tch_resp utr,
312 igs_ad_location loc
313 WHERE uoo.cal_type = cp_teach_cal_type
314 AND uoo.ci_sequence_number = cp_teach_ci_sequence_number
315 AND uoo.unit_cd = uv.unit_cd
316 AND uoo.version_number = uv.version_number
317 AND uoo.location_cd = loc.location_cd
318 AND uoo.uoo_id = utr.uoo_id (+)
319 AND utr.lead_instructor_flag (+) = 'Y' ;
320
321 CURSOR c_attend_gash (cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
322 SELECT 'X'
323 FROM igs_as_gaa_sub_hist gash
324 WHERE gash.uoo_id = cp_uoo_id
325 AND gash.submission_type = 'ATTENDANCE'
326 AND gash.submission_status = 'COMPLETE'
327 AND gash.submitted_date = (
328 SELECT MAX(gash2.submitted_date)
329 FROM igs_as_gaa_sub_hist gash2
330 WHERE gash2.uoo_id = cp_uoo_id
331 AND gash2.submission_type = 'ATTENDANCE');
332
333 CURSOR c_midterm_gash (cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE)IS
334 SELECT 'X'
335 FROM igs_as_gaa_sub_hist gash
336 WHERE gash.uoo_id = cp_uoo_id
337 AND gash.submission_type = 'GRADE'
338 AND gash.grading_period_cd = 'MIDTERM'
339 AND gash.submission_status = 'COMPLETE'
340 AND gash.submitted_date = (
341 SELECT MAX(gash2.submitted_date)
342 FROM igs_as_gaa_sub_hist gash2
343 WHERE gash2.uoo_id = cp_uoo_id
344 AND gash2.submission_type = 'GRADE'
345 AND gash2.grading_period_cd = 'MIDTERM');
346
347 CURSOR c_earlyfinal_gash (cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
348 SELECT 'X'
349 FROM igs_as_gaa_sub_hist gash
350 WHERE gash.uoo_id = cp_uoo_id
351 AND gash.submission_type = 'GRADE'
352 AND gash.grading_period_cd = 'EARLY_FINAL'
353 AND gash.submission_status = 'COMPLETE'
354 AND gash.submitted_date = (
355 SELECT MAX(gash2.submitted_date)
356 FROM igs_as_gaa_sub_hist gash2
357 WHERE gash2.uoo_id = cp_uoo_id
358 AND gash2.submission_type = 'GRADE'
359 AND gash2.grading_period_cd = 'EARLY_FINAL');
360
361 CURSOR c_final_gash (cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
362 SELECT 'X'
363 FROM igs_as_gaa_sub_hist gash
364 WHERE gash.uoo_id = cp_uoo_id
365 AND gash.submission_type = 'GRADE'
366 AND gash.grading_period_cd = 'FINAL'
367 AND gash.submission_status = 'COMPLETE'
368 AND gash.submitted_date = (
369 SELECT MAX(gash2.submitted_date)
370 FROM igs_as_gaa_sub_hist gash2
371 WHERE gash2.uoo_id = cp_uoo_id
372 AND gash2.submission_type = 'GRADE'
373 AND gash2.grading_period_cd = 'FINAL');
374
375
376
377 v_mid_mgs_start_dt_alias igs_as_cal_conf.mid_mgs_start_dt_alias%TYPE;
378 v_mid_mgs_end_dt_alias igs_as_cal_conf.mid_mgs_end_dt_alias%TYPE;
379 v_efinal_mgs_start_dt_alias igs_as_cal_conf.efinal_mgs_start_dt_alias%TYPE;
380 v_efinal_mgs_end_dt_alias igs_as_cal_conf.efinal_mgs_end_dt_alias%TYPE;
381 v_final_mgs_start_dt_alias igs_as_cal_conf.final_mgs_start_dt_alias%TYPE;
382 v_final_mgs_end_dt_alias igs_as_cal_conf.final_mgs_end_dt_alias%TYPE;
383 v_midterm_start_dt DATE;
384 v_midterm_end_dt DATE;
385 v_earlyfinal_start_dt DATE;
389 v_attend_advance BOOLEAN;
386 v_earlyfinal_end_dt DATE;
387 v_final_start_dt DATE;
388 v_final_end_dt DATE;
390 v_attend_start BOOLEAN;
391 v_attend_end BOOLEAN;
392 v_midterm_advance BOOLEAN;
393 v_midterm_start BOOLEAN;
394 v_midterm_end BOOLEAN;
395 v_earlyfinal_advance BOOLEAN;
396 v_earlyfinal_start BOOLEAN;
397 v_earlyfinal_end BOOLEAN;
398 v_final_advance BOOLEAN;
399 v_final_start BOOLEAN;
400 v_final_end BOOLEAN;
401 v_dummy VARCHAR2(1);
402
403 BEGIN -- Main
404
405
406 retcode := 0;
407 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
408
409
410 l_ld_cal_type := LTRIM(RTRIM(SUBSTR(p_load_calendar,1,10)));
411 l_ld_sequence_number := TO_NUMBER(SUBSTR(p_load_calendar,-6));
412
413 /* Print the Parameters Passed */
414
415 FND_FILE.PUT_LINE(FND_FILE.LOG,'+-------------------------Parameters Passed---------------------------------+');
416 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
417 FND_FILE.PUT_LINE(FND_FILE.LOG,'Load Calendar Type : ' || l_ld_cal_type);
418 FND_FILE.PUT_LINE(FND_FILE.LOG,'Load Calendar Sequence no. : ' || to_char(l_ld_sequence_number));
419 FND_FILE.PUT_LINE(FND_FILE.LOG,'Attendance Advance Offset : ' || TO_CHAR(p_attend_advance_offset));
420 FND_FILE.PUT_LINE(FND_FILE.LOG,'Attendance Start Offset : ' || TO_CHAR(p_attend_start_offset)) ;
421 FND_FILE.PUT_LINE(FND_FILE.LOG,'Attendance End Offset : ' || TO_CHAR(p_attend_end_offset));
422 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Mid Term Advance Offset : ' || TO_CHAR(p_midterm_advance_offset));
423 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Mid Term Start Offset : ' || TO_CHAR(p_midterm_start_offset));
424 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Mid Term End Offset : ' || TO_CHAR(p_midterm_end_offset));
425 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Early Final Advance Offset : ' || TO_CHAR(p_earlyfinal_advance_offset));
426 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Early Final Start Offset : ' || TO_CHAR(p_earlyfinal_start_offset));
427 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Early Final End Offset : ' || TO_CHAR(p_earlyfinal_end_offset));
428 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Final Advance Offset : ' || TO_CHAR(p_final_advance_offset));
429 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Final Start Offset : ' || TO_CHAR(p_final_start_offset));
430 FND_FILE.PUT_LINE(FND_FILE.LOG,'Grade-Final End Offset : ' || TO_CHAR(p_final_end_offset));
431 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
432
433
434 -- Get grading date aliases
435 SELECT acc.mid_mgs_start_dt_alias,
436 acc.mid_mgs_end_dt_alias,
437 acc.efinal_mgs_start_dt_alias,
438 acc.efinal_mgs_end_dt_alias,
439 acc.final_mgs_start_dt_alias,
440 acc.final_mgs_end_dt_alias
441 INTO
442 v_mid_mgs_start_dt_alias,
443 v_mid_mgs_end_dt_alias,
444 v_efinal_mgs_start_dt_alias,
445 v_efinal_mgs_end_dt_alias,
446 v_final_mgs_start_dt_alias,
447 v_final_mgs_end_dt_alias
448 FROM igs_as_cal_conf acc
449 WHERE s_control_num = 1;
450
451
452 FOR v_ttl_rec IN c_ttl LOOP
453 FND_FILE.PUT_LINE(FND_FILE.LOG,'+-------------------------Processing for Teaching Calendar'||':'||v_ttl_rec.teach_description||'---------------------------------+');
454 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
455 -- Get the date alias values for each of the grading date aliases
456 v_midterm_start_dt := get_dt_alias_val (lp_dt_alias => v_mid_mgs_start_dt_alias,
457 lp_teach_cal_type => v_ttl_rec.teach_cal_type,
458 lp_teach_ci_sequence_number => v_ttl_rec.teach_ci_sequence_number);
459 v_midterm_end_dt := get_dt_alias_val (lp_dt_alias => v_mid_mgs_end_dt_alias,
460 lp_teach_cal_type => v_ttl_rec.teach_cal_type,
461 lp_teach_ci_sequence_number => v_ttl_rec.teach_ci_sequence_number);
462 v_earlyfinal_start_dt := get_dt_alias_val (lp_dt_alias => v_efinal_mgs_start_dt_alias,
463 lp_teach_cal_type => v_ttl_rec.teach_cal_type,
464 lp_teach_ci_sequence_number => v_ttl_rec.teach_ci_sequence_number);
465 v_earlyfinal_end_dt := get_dt_alias_val (lp_dt_alias => v_efinal_mgs_end_dt_alias,
466 lp_teach_cal_type => v_ttl_rec.teach_cal_type,
467 lp_teach_ci_sequence_number => v_ttl_rec.teach_ci_sequence_number);
468 v_final_start_dt := get_dt_alias_val (lp_dt_alias => v_final_mgs_start_dt_alias,
469 lp_teach_cal_type => v_ttl_rec.teach_cal_type,
470 lp_teach_ci_sequence_number => v_ttl_rec.teach_ci_sequence_number);
471 v_final_end_dt := get_dt_alias_val (lp_dt_alias => v_final_mgs_end_dt_alias,
472 lp_teach_cal_type => v_ttl_rec.teach_cal_type,
476 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
473 lp_teach_ci_sequence_number => v_ttl_rec.teach_ci_sequence_number);
474
475 FND_FILE.PUT_LINE(FND_FILE.LOG,'+-------------------------Alias Date Values Derived---------------------------------+');
477 -- Attendance Notifications
478
479 -- Check if the Attendance Advanced Notification date
480 -- has been reached. Offset from final grading start date.
481 IF (p_attend_advance_offset IS NOT NULL AND
482 v_final_start_dt IS NOT NULL AND
483 TRUNC(v_final_start_dt - p_attend_advance_offset) <= TRUNC(SYSDATE)) THEN
484 v_attend_advance := TRUE;
485 ELSE
486
487 v_attend_advance := FALSE;
488 END IF;
489
490
491 -- Check if the Attendance Start Notification date has
492 -- been reached. Offset from final grading start date.
493 IF (p_attend_start_offset IS NOT NULL AND
494 v_final_start_dt IS NOT NULL AND
495 TRUNC(v_final_start_dt - p_attend_start_offset) <= TRUNC(SYSDATE)) THEN
496
497 v_attend_start := TRUE;
498 ELSE
499
500 v_attend_start := FALSE;
501 END IF;
502
503 -- Check if the Attendance End Notification date has
504 -- been reached. Offset from final grading end date.
505 IF (p_attend_end_offset IS NOT NULL AND
506 v_final_end_dt IS NOT NULL AND
507 TRUNC(v_final_end_dt - p_attend_end_offset)<= TRUNC(SYSDATE)) THEN
508
509 v_attend_end := TRUE;
510 ELSE
511
512 v_attend_end := FALSE;
513 END IF;
514
515
516 -- Mid Term Grading Notifications
517
518 -- Check if the Mid Term Grading Advanced Notification date has
519 -- been reached. Offset from mid term grading start date.
520 IF (p_midterm_advance_offset IS NOT NULL AND
521 v_midterm_start_dt IS NOT NULL AND
522 TRUNC(v_midterm_start_dt - p_midterm_advance_offset) <= TRUNC(SYSDATE)) THEN
523
524 v_midterm_advance := TRUE;
525 ELSE
526
527 v_midterm_advance := FALSE;
528 END IF;
529
530
531 -- Check if the Mid Term Grading Start Notification date has
532 -- been reached. Offset from mid term grading start date.
533 IF (p_midterm_start_offset IS NOT NULL AND
534 v_midterm_start_dt IS NOT NULL AND
535 TRUNC(v_midterm_start_dt - p_midterm_start_offset) <= TRUNC(SYSDATE)) THEN
536
537 v_midterm_start := TRUE;
538 ELSE
539
540 v_midterm_start := FALSE;
541 END IF;
542
543 -- Check if the Mid Term Grading End Notification date has
544 -- been reached. Offset from mid term grading end date.
545 IF (p_midterm_end_offset IS NOT NULL AND
546 v_midterm_end_dt IS NOT NULL AND
547 TRUNC(v_midterm_end_dt - p_midterm_end_offset) <= TRUNC(SYSDATE)) THEN
548
549 v_midterm_end := TRUE;
550 ELSE
551
552 v_midterm_end := FALSE;
553 END IF;
554
555
556 -- Early Final Grading Notifications
557
558 -- Check if the Early Final Grading Advanced Notification date has
559 -- been reached. Offset from early final grading start date.
560 IF (p_earlyfinal_advance_offset IS NOT NULL AND
561 v_earlyfinal_start_dt IS NOT NULL AND
562 TRUNC(v_earlyfinal_start_dt - p_earlyfinal_advance_offset) <= TRUNC(SYSDATE)) THEN
563
564 v_earlyfinal_advance := TRUE;
565 ELSE
566
567 v_earlyfinal_advance := FALSE;
568 END IF;
569
570
571 -- Check if the Early Final Grading Start Notification date has
572 -- been reached. Offset from early final grading start date.
573 IF (p_earlyfinal_start_offset IS NOT NULL AND
574 v_earlyfinal_start_dt IS NOT NULL AND
575 TRUNC(v_earlyfinal_start_dt - p_earlyfinal_start_offset) <= TRUNC(SYSDATE)) THEN
576
577 v_earlyfinal_start := TRUE;
578 ELSE
579
580 v_earlyfinal_start := FALSE;
581 END IF;
582
583 -- Check if the Early Final Grading End Notification date has
584 -- been reached. Offset from early final grading end date.
585 IF (p_earlyfinal_end_offset IS NOT NULL AND
586 v_earlyfinal_end_dt IS NOT NULL AND
587 TRUNC(v_earlyfinal_end_dt - p_earlyfinal_end_offset) <= TRUNC(SYSDATE)) THEN
588
589 v_earlyfinal_end := TRUE;
590 ELSE
591
592 v_earlyfinal_end := FALSE;
593 END IF;
594
595
596 -- Final Grading Notifications
597
598 -- Check if the Final Grading Advanced Notification date has
599 -- been reached. Offset from final grading start date.
600 IF (p_final_advance_offset IS NOT NULL AND
601 v_final_start_dt IS NOT NULL AND
602 TRUNC(v_final_start_dt - p_final_advance_offset) <= TRUNC(SYSDATE)) THEN
603
604 v_final_advance := TRUE;
605 ELSE
606
607 v_final_advance := FALSE;
608 END IF;
609
610
611 -- Check if the Final Grading Start Notification date has
612 -- been reached. Offset from final grading start date.
613 IF (p_final_start_offset IS NOT NULL AND
614 v_final_start_dt IS NOT NULL AND
615 TRUNC(v_final_start_dt - p_final_start_offset) <= TRUNC(SYSDATE)) THEN
616
617 v_final_start := TRUE;
618 ELSE
619
620 v_final_start := FALSE;
621 END IF;
622
623 -- Check if the Final Grading End Notification date has
624 -- been reached. Offset from final grading end date.
625 IF (p_final_end_offset IS NOT NULL AND
626 v_final_end_dt IS NOT NULL AND
630 ELSE
627 TRUNC(v_final_end_dt - p_final_end_offset) <= TRUNC(SYSDATE)) THEN
628
629 v_final_end := TRUE;
631
632 v_final_end := FALSE;
633 END IF;
634
635
636 -- If any of the notification dates have been met loop
637 -- through the Unit Sections and create notification business
638 -- event where applicable.
639 IF (v_attend_advance OR
640 v_attend_start OR
641 v_attend_end OR
642 v_midterm_advance OR
643 v_midterm_start OR
644 v_midterm_end OR
645 v_earlyfinal_advance OR
646 v_earlyfinal_start OR
647 v_earlyfinal_end OR
648 v_final_advance OR
649 v_final_start OR
650 v_final_end )THEN
651
652 FOR v_uoo_rec IN c_uoo(v_ttl_rec.teach_cal_type,v_ttl_rec.teach_ci_sequence_number) LOOP
653
654 FND_FILE.PUT_LINE(FND_FILE.LOG,'+----Processing for Unit Section'||':'||to_char(v_uoo_rec.uoo_id)||':'||v_uoo_rec.unit_cd||':'||v_uoo_rec.unit_class||'----+');
655 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
656 -- Attendance Notifications
657 IF (v_uoo_rec.attendance_required_ind = 'Y' AND
658 (v_attend_advance OR
659 v_attend_start OR
660 v_attend_end)) THEN
661
662 -- Check if Attendance has already been completed
663 OPEN c_attend_gash(v_uoo_rec.uoo_id);
664 FETCH c_attend_gash INTO v_dummy;
665
666 IF (c_attend_gash%NOTFOUND) THEN
667 CLOSE c_attend_gash;
668 IF (v_attend_advance) THEN
669
670 raise_business_event(
671 lp_internal_name => 'oracle.apps.igs.as.attend.notify_advanced',
672 lp_uoo_id => v_uoo_rec.uoo_id,
673 lp_teach_description => v_ttl_rec.teach_description,
674 lp_load_description => v_ttl_rec.load_description,
675 lp_grading_period_start_dt => v_final_start_dt,
676 lp_grading_period_end_dt => v_final_end_dt,
677 lp_unit_cd => v_uoo_rec.unit_cd,
678 lp_unit_class => v_uoo_rec.unit_class,
679 lp_location_cd => v_uoo_rec.location_cd,
680 lp_location_description => v_uoo_rec.location_description,
681 lp_title => v_uoo_rec.title,
682 lp_short_title => v_uoo_rec.short_title,
683 lp_instructor_id => v_uoo_rec.instructor_id);
684
685 END IF;
686 IF (v_attend_start) THEN
687
688 raise_business_event(
689 lp_internal_name => 'oracle.apps.igs.as.attend.notify_start',
690 lp_uoo_id => v_uoo_rec.uoo_id,
691 lp_teach_description => v_ttl_rec.teach_description,
692 lp_load_description => v_ttl_rec.load_description,
693 lp_grading_period_start_dt => v_final_start_dt,
694 lp_grading_period_end_dt => v_final_end_dt,
695 lp_unit_cd => v_uoo_rec.unit_cd,
696 lp_unit_class => v_uoo_rec.unit_class,
697 lp_location_cd => v_uoo_rec.location_cd,
698 lp_location_description => v_uoo_rec.location_description,
699 lp_title => v_uoo_rec.title,
700 lp_short_title => v_uoo_rec.short_title,
701 lp_instructor_id => v_uoo_rec.instructor_id);
702
703
704 END IF;
705 IF (v_attend_end) THEN
706
707 raise_business_event(
708 lp_internal_name => 'oracle.apps.igs.as.attend.notify_end',
709 lp_uoo_id => v_uoo_rec.uoo_id,
710 lp_teach_description => v_ttl_rec.teach_description,
711 lp_load_description => v_ttl_rec.load_description,
712 lp_grading_period_start_dt => v_final_start_dt,
713 lp_grading_period_end_dt => v_final_end_dt,
714 lp_unit_cd => v_uoo_rec.unit_cd,
715 lp_unit_class => v_uoo_rec.unit_class,
716 lp_location_cd => v_uoo_rec.location_cd,
717 lp_location_description => v_uoo_rec.location_description,
718 lp_title => v_uoo_rec.title,
719 lp_short_title => v_uoo_rec.short_title,
720 lp_instructor_id => v_uoo_rec.instructor_id);
721
722
723 END IF;
724 ELSE
725 CLOSE c_attend_gash;
726 END IF;
727
728 END IF;
729
730
731 -- Mid Term Grading Notifications
732 IF (v_midterm_advance OR
733 v_midterm_start OR
734 v_midterm_end) THEN
735 -- Check if Mid Term Grading has been completed
736 OPEN c_midterm_gash(v_uoo_rec.uoo_id);
737 FETCH c_midterm_gash INTO v_dummy;
738 IF (c_midterm_gash%NOTFOUND AND
739 check_grading_cohort(v_uoo_rec.uoo_id, 'MIDTERM',l_ld_cal_type,l_ld_sequence_number)) THEN
740 CLOSE c_midterm_gash;
741 IF (v_midterm_advance) THEN
742
743 raise_business_event(
744 lp_internal_name => 'oracle.apps.igs.as.midterm.notify_advanced',
745 lp_uoo_id => v_uoo_rec.uoo_id,
746 lp_teach_description => v_ttl_rec.teach_description,
747 lp_load_description => v_ttl_rec.load_description,
751 lp_unit_class => v_uoo_rec.unit_class,
748 lp_grading_period_start_dt => v_midterm_start_dt,
749 lp_grading_period_end_dt => v_midterm_end_dt,
750 lp_unit_cd => v_uoo_rec.unit_cd,
752 lp_location_cd => v_uoo_rec.location_cd,
753 lp_location_description => v_uoo_rec.location_description,
754 lp_title => v_uoo_rec.title,
755 lp_short_title => v_uoo_rec.short_title,
756 lp_instructor_id => v_uoo_rec.instructor_id);
757
758
759 END IF;
760 IF (v_midterm_start) THEN
761
762 raise_business_event(
763 lp_internal_name => 'oracle.apps.igs.as.midterm.notify_start',
764 lp_uoo_id => v_uoo_rec.uoo_id,
765 lp_teach_description => v_ttl_rec.teach_description,
766 lp_load_description => v_ttl_rec.load_description,
767 lp_grading_period_start_dt => v_midterm_start_dt,
768 lp_grading_period_end_dt => v_midterm_end_dt,
769 lp_unit_cd => v_uoo_rec.unit_cd,
770 lp_unit_class => v_uoo_rec.unit_class,
771 lp_location_cd => v_uoo_rec.location_cd,
772 lp_location_description => v_uoo_rec.location_description,
773 lp_title => v_uoo_rec.title,
774 lp_short_title => v_uoo_rec.short_title,
775 lp_instructor_id => v_uoo_rec.instructor_id);
776 END IF;
777 IF (v_midterm_end) THEN
778
779 raise_business_event(
780 lp_internal_name => 'oracle.apps.igs.as.midterm.notify_end',
781 lp_uoo_id => v_uoo_rec.uoo_id,
782 lp_teach_description => v_ttl_rec.teach_description,
783 lp_load_description => v_ttl_rec.load_description,
784 lp_grading_period_start_dt => v_midterm_start_dt,
785 lp_grading_period_end_dt => v_midterm_end_dt,
786 lp_unit_cd => v_uoo_rec.unit_cd,
787 lp_unit_class => v_uoo_rec.unit_class,
788 lp_location_cd => v_uoo_rec.location_cd,
789 lp_location_description => v_uoo_rec.location_description,
790 lp_title => v_uoo_rec.title,
791 lp_short_title => v_uoo_rec.short_title,
792 lp_instructor_id => v_uoo_rec.instructor_id);
793
794 END IF;
795 ELSE
796 CLOSE c_midterm_gash;
797 END IF;
798
799 END IF;
800
801 -- Early Final Grading Notifications
802 IF (v_earlyfinal_advance OR
803 v_earlyfinal_start OR
804 v_earlyfinal_end) THEN
805 -- Check if Early Final Grading has been completed
806 OPEN c_earlyfinal_gash(v_uoo_rec.uoo_id);
807 FETCH c_earlyfinal_gash INTO v_dummy;
808
809 IF (c_earlyfinal_gash%NOTFOUND AND
810 check_grading_cohort (v_uoo_rec.uoo_id, 'EARLY_FINAL',l_ld_cal_type,l_ld_sequence_number)) THEN
811 CLOSE c_earlyfinal_gash;
812 IF v_earlyfinal_advance THEN
813
814 raise_business_event(
815 lp_internal_name => 'oracle.apps.igs.as.earlyfinal.notify_advanced',
816 lp_uoo_id => v_uoo_rec.uoo_id,
817 lp_teach_description => v_ttl_rec.teach_description,
818 lp_load_description => v_ttl_rec.load_description,
819 lp_grading_period_start_dt => v_earlyfinal_start_dt,
820 lp_grading_period_end_dt => v_earlyfinal_end_dt,
821 lp_unit_cd => v_uoo_rec.unit_cd,
822 lp_unit_class => v_uoo_rec.unit_class,
823 lp_location_cd => v_uoo_rec.location_cd,
824 lp_location_description => v_uoo_rec.location_description,
825 lp_title => v_uoo_rec.title,
826 lp_short_title => v_uoo_rec.short_title,
827 lp_instructor_id => v_uoo_rec.instructor_id);
828
829 END IF;
830 IF v_earlyfinal_start THEN
831
832 raise_business_event(
833 lp_internal_name => 'oracle.apps.igs.as.earlyfinal.notify_start',
834 lp_uoo_id => v_uoo_rec.uoo_id,
835 lp_teach_description => v_ttl_rec.teach_description,
836 lp_load_description => v_ttl_rec.load_description,
837 lp_grading_period_start_dt => v_earlyfinal_start_dt,
838 lp_grading_period_end_dt => v_earlyfinal_end_dt,
839 lp_unit_cd => v_uoo_rec.unit_cd,
840 lp_unit_class => v_uoo_rec.unit_class,
841 lp_location_cd => v_uoo_rec.location_cd,
842 lp_location_description => v_uoo_rec.location_description,
843 lp_title => v_uoo_rec.title,
844 lp_short_title => v_uoo_rec.short_title,
845 lp_instructor_id => v_uoo_rec.instructor_id);
846
847 END IF;
848 IF v_earlyfinal_end THEN
849
850 raise_business_event(
851 lp_internal_name => 'oracle.apps.igs.as.earlyfinal.notify_end',
852 lp_uoo_id => v_uoo_rec.uoo_id,
853 lp_teach_description => v_ttl_rec.teach_description,
857 lp_unit_cd => v_uoo_rec.unit_cd,
854 lp_load_description => v_ttl_rec.load_description,
855 lp_grading_period_start_dt => v_earlyfinal_start_dt,
856 lp_grading_period_end_dt => v_earlyfinal_end_dt,
858 lp_unit_class => v_uoo_rec.unit_class,
859 lp_location_cd => v_uoo_rec.location_cd,
860 lp_location_description => v_uoo_rec.location_description,
861 lp_title => v_uoo_rec.title,
862 lp_short_title => v_uoo_rec.short_title,
863 lp_instructor_id => v_uoo_rec.instructor_id);
864
865 END IF;
866 ELSE
867 CLOSE c_earlyfinal_gash;
868 END IF;
869
870 END IF;
871
872
873 -- Final Grading Notifications
874 IF (v_final_advance OR
875 v_final_start OR
876 v_final_end )THEN
877 -- Check if Early Final Grading has been completed
878 OPEN c_final_gash(v_uoo_rec.uoo_id);
879 FETCH c_final_gash INTO v_dummy;
880
881 IF c_final_gash%NOTFOUND THEN
882 CLOSE c_final_gash;
883 IF v_final_advance THEN
884 raise_business_event(
885 lp_internal_name => 'oracle.apps.igs.as.final.notify_advanced',
886 lp_uoo_id => v_uoo_rec.uoo_id,
887 lp_teach_description => v_ttl_rec.teach_description,
888 lp_load_description => v_ttl_rec.load_description,
889 lp_grading_period_start_dt => v_final_start_dt,
890 lp_grading_period_end_dt => v_final_end_dt,
891 lp_unit_cd => v_uoo_rec.unit_cd,
892 lp_unit_class => v_uoo_rec.unit_class,
893 lp_location_cd => v_uoo_rec.location_cd,
894 lp_location_description => v_uoo_rec.location_description,
895 lp_title => v_uoo_rec.title,
896 lp_short_title => v_uoo_rec.short_title,
897 lp_instructor_id => v_uoo_rec.instructor_id);
898
899 END IF;
900 IF v_final_start THEN
901 raise_business_event(
902 lp_internal_name => 'oracle.apps.igs.as.final.notify_start',
903 lp_uoo_id => v_uoo_rec.uoo_id,
904 lp_teach_description => v_ttl_rec.teach_description,
905 lp_load_description => v_ttl_rec.load_description,
906 lp_grading_period_start_dt => v_final_start_dt,
907 lp_grading_period_end_dt => v_final_end_dt,
908 lp_unit_cd => v_uoo_rec.unit_cd,
909 lp_unit_class => v_uoo_rec.unit_class,
910 lp_location_cd => v_uoo_rec.location_cd,
911 lp_location_description => v_uoo_rec.location_description,
912 lp_title => v_uoo_rec.title,
913 lp_short_title => v_uoo_rec.short_title,
914 lp_instructor_id => v_uoo_rec.instructor_id);
915
916 END IF;
917 IF v_final_end THEN
918 raise_business_event(
919 lp_internal_name => 'oracle.apps.igs.as.final.notify_end',
920 lp_uoo_id => v_uoo_rec.uoo_id,
921 lp_teach_description => v_ttl_rec.teach_description,
922 lp_load_description => v_ttl_rec.load_description,
923 lp_grading_period_start_dt => v_final_start_dt,
924 lp_grading_period_end_dt => v_final_end_dt,
925 lp_unit_cd => v_uoo_rec.unit_cd,
926 lp_unit_class => v_uoo_rec.unit_class,
927 lp_location_cd => v_uoo_rec.location_cd,
928 lp_location_description => v_uoo_rec.location_description,
929 lp_title => v_uoo_rec.title,
930 lp_short_title => v_uoo_rec.short_title,
931 lp_instructor_id => v_uoo_rec.instructor_id);
932 END IF;
933 ELSE
934 CLOSE c_final_gash;
935 END IF;
936
937 END IF;
938 END LOOP; -- c_uoo
939
940 END IF;
941 END LOOP; -- c_ttl
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
946 retcode := 2;
947 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
948 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_NOTIFY_PKG.GEN_AS_NOTIFICATIONS');
949 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
950 IGS_GE_MSG_STACK.ADD;
951 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
952
953
954 END gen_as_notifications; --gen_as_notifications
955
956 PROCEDURE raise_sua_ref_cd_be(P_AUTH_PERSON_ID IN NUMBER,
957 P_PERSON_ID IN NUMBER,
958 P_SUAR_ID IN NUMBER,
959 P_ACTION IN VARCHAR2 ) IS
960
961 CURSOR c_seq_num IS
962 SELECT IGS_AS_WF_BESUAREFCDS_S.nextval
963 FROM DUAL;
964 ln_seq_val NUMBER;
965 l_event_t wf_event_t;
966 l_parameter_list_t wf_parameter_list_t;
967 BEGIN
968
969 -- initialize the parameter list.
970 wf_event_t.Initialize(l_event_t);
971
972 -- set the parameters.
973 wf_event.AddParameterToList ( p_name => 'AUTH_PERSON_ID' , p_value => P_AUTH_PERSON_ID , p_parameterlist => l_parameter_list_t);
974 wf_event.AddParameterToList ( p_name => 'PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist => l_parameter_list_t);
975 wf_event.AddParameterToList ( p_Name => 'SUAR_ID' , p_Value => P_SUAR_ID , p_ParameterList => l_parameter_list_t);
976 wf_event.AddParameterToList ( p_Name => 'ACTION' , p_Value => p_action , p_ParameterList => l_parameter_list_t);
977
978 -- get the sequence value to be added to EVENT KEY to make it unique.
979 OPEN c_seq_num;
980 FETCH c_seq_num INTO ln_seq_val ;
981 CLOSE c_seq_num ;
982 -- raise event
983 if (P_ACTION = 'UPDATE') then
984 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.as.SUArefcd.update',
985 p_event_key => 'SUA_REF'||ln_seq_val,
986 p_parameters => l_parameter_list_t);
987 end if ;
988 if (P_ACTION = 'INSERT') then
989 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.as.SUArefcd.insert',
990 p_event_key => 'SUA_REF'||ln_seq_val,
991 p_parameters => l_parameter_list_t);
992 end if ;
993 END raise_sua_ref_cd_be;
994 END igs_as_notify_pkg;