1 PACKAGE BODY IGS_EN_DASHBOARD AS
2 /* $Header: IGSENB2B.pls 120.10 2006/03/13 23:06:48 smaddali noship $ */
3
4 -- Function to get the message text for the given message name
5 FUNCTION get_message(p_c_msg IN VARCHAR2) RETURN VARCHAR2 ;
6
7 -- contains the translated message text as Schedule
8 g_c_schedule_txt CONSTANT VARCHAR2(500) := get_message('IGS_EN_SCHEDULE');
9
10 -- contains the translated message text as Planning Sheet
11 g_c_planning_txt CONSTANT VARCHAR2(500) := get_message ('IGS_EN_PLANNING_SHEET') ;
12
13 -- contains the translated message text for View Only
14 g_c_view_only_txt CONSTANT VARCHAR2(500) := get_message('IGS_EN_VIEW_ONLY');
15
16 -- contains the translated message text for Enrollment Now Open
17 g_c_enr_open_txt CONSTANT VARCHAR2(500) := get_message('IGS_EN_SCHEDULE_OPEN') ;
18
19 -- Function to get the message text for the given message name
20 FUNCTION get_message(p_c_msg IN VARCHAR2) RETURN VARCHAR2 IS
21 ------------------------------------------------------------------------------------
22 --Created by : Somasekar ( Oracle IDC)
23 --Date created: 17-MAY-2005
24 --
25 --Purpose: this function returns translated message
26 --
27 --Known limitations/enhancements and/or remarks:
28 --
29 --Change History:
30 --Who When What
31 --------------------------------------------------------------------------------------
32 BEGIN
33 Fnd_message.set_name('IGS', p_c_msg);
34 RETURN Fnd_message.get;
35 END get_message;
36
37 PROCEDURE student_api ( p_n_person_id IN NUMBER,
38 p_c_person_type IN VARCHAR2,
39 p_text_tbl OUT NOCOPY LINK_TEXT_TYPE,
40 p_cal_tbl OUT NOCOPY CAL_TYPE,
41 p_seq_tbl OUT NOCOPY SEQ_NUM_TYPE,
42 p_car_tbl OUT NOCOPY PRG_CAR_TYPE,
43 p_typ_tbl OUT NOCOPY PLAN_SCHED_TYPE,
44 p_sch_allow OUT NOCOPY VARCHAR2) IS
45 ------------------------------------------------------------------------------------
46 --Created by : Somasekar ( Oracle IDC)
47 --Date created: 17-MAY-2005
48 --
49 --Purpose: this function returns the table for the student
50 -- to render the links in the home page.
51 --
52 --Known limitations/enhancements and/or remarks:
53 --
54 --Change History:
55 --Who When What
56 --stutta 26-Oct-2005 Plan sheet link should not be displayed only when ther
57 -- schedule is available and there are no units in plan.
58 -- bug #4665592
59 --------------------------------------------------------------------------------------
60
61 -- Cursor to get career related information.
62 -- Used in career mode.
63 CURSOR c_career (cp_n_person_id IN NUMBER) IS
64 SELECT distinct pv.course_type career,
65 sca.course_cd program_cd,
66 sca.version_number program_version,
67 ci.cal_type LoadCal,
68 ci.sequence_number sequence_number,
69 ci.description description,
70 ci.start_dt start_dt,
71 ci.planning_flag planning_flag,
72 ci.schedule_flag schedule_flag
73 FROM igs_en_stdnt_ps_att_all sca,
74 igs_ca_inst_all ci,
75 igs_ca_type ca,
76 igs_ca_stat cs,
77 igs_ca_inst_rel car,
78 igs_ps_ver_all pv
79 WHERE sca.person_id = cp_n_person_id
80 AND sca.course_cd = pv.course_cd
81 AND sca.version_number = pv.version_number
82 AND sca.course_attempt_status IN ('ENROLLED','INACTIVE', 'INTERMIT')
83 AND sca.cal_type = car.sup_cal_type
84 AND car.sub_cal_type = ci.cal_type
85 AND car.sub_ci_sequence_number = ci.sequence_number
86 AND ci.cal_type = ca.cal_type
87 AND ca.s_cal_cat = 'LOAD'
88 AND ci.cal_status = cs.cal_status
89 AND ( ci.schedule_flag ='Y' OR ci.planning_flag ='Y')
90 AND cs.s_cal_status = 'ACTIVE'
91 AND ca.closed_ind = 'N'
92 AND igs_en_spa_terms_api.get_spat_primary_prg(sca.person_id, sca.course_cd, ci.cal_type,ci.sequence_number)='PRIMARY'
93 ORDER BY ci.start_dt desc;
94
95 -- Cursor to get program related information
96 -- Used in program mode.
97 CURSOR c_program (cp_n_person_id IN NUMBER) IS
98 SELECT distinct sca.course_cd program_cd,
99 sca.version_number program_version,
100 cai.cal_type LoadCal,
101 cai.sequence_number sequence_number,
102 cai.description description,
103 cai.start_dt start_dt,
104 cai.planning_flag planning_flag,
105 cai.schedule_flag schedule_flag
106 FROM IGS_EN_STDNT_PS_ATT_ALL sca,
107 IGS_CA_INST_REL car,
108 IGS_CA_INST_ALL cai,
109 IGS_CA_TYPE ca,
110 IGS_CA_STAT cs
111 WHERE sca.person_id = cp_n_person_id
112 AND sca.course_attempt_status IN ('ENROLLED','INACTIVE', 'INTERMIT')
113 AND sca.cal_type =car.sup_cal_type
114 AND cai.cal_type = car.sub_cal_type
115 AND cai.SEQUENCE_NUMBER = car.sub_ci_sequence_number
116 AND cai.cal_type = ca.cal_type
117 AND ca.s_cal_cat = 'LOAD'
118 AND ( cai.planning_flag ='Y' OR cai.schedule_flag ='Y')
119 AND ca.closed_ind = 'N'
120 AND cai.cal_status = cs.cal_status
121 AND cs.s_cal_status = 'ACTIVE'
122 ORDER BY cai.start_dt;
123
124 -- Cursor to determine whether finds unit section to be rendered or not.
125 CURSOR c_srch_allwd IS
126 SELECT 1
127 FROM IGS_CA_INST_ALL ci,
128 IGS_CA_TYPE ca,
129 IGS_CA_STAT cs
130 WHERE ci.ss_displayed = 'Y'
131 AND ci.cal_type = ca.cal_type
132 AND ca.s_cal_cat = 'LOAD'
133 AND ci.cal_status = cs.cal_status
134 AND ca.closed_ind = 'N'
135 AND cs.s_cal_status = 'ACTIVE' AND ROWNUM <2;
136
137 -- Cursor to get the status (active/submitted/skipped) of the planning sheet
138 CURSOR c_plan_status (cp_n_person_id in NUMBER,
139 cp_c_program IN VARCHAR2,
140 cp_c_cal_type in VARCHAR2,
141 cp_n_seq_num IN NUMBER) IS
142 SELECT PLAN_SHT_STATUS
143 FROM igs_en_spa_terms
144 WHERE person_id = cp_n_person_id
145 AND program_cd = cp_c_program
146 AND term_cal_type = cp_c_cal_type
147 AND term_sequence_number = cp_n_seq_num;
148
149 -- Cursor to check whether student has unit section in planning sheet for term and career / program
150 CURSOR c_plan_exists (cp_n_person_id IN NUMBER,
151 cp_c_program_cd IN VARCHAR2,
152 cp_c_cal_type IN VARCHAR2,
153 cp_n_seq_num IN NUMBER) IS
154 SELECT 1
155 FROM IGS_EN_PLAN_UNITS plan
156 WHERE plan.person_id = cp_n_person_id
157 AND plan.course_cd = cp_c_program_cd
158 AND plan.term_cal_type = cp_c_cal_type
159 AND plan.term_ci_sequence_number = cp_n_seq_num
160 AND cart_error_flag='N'
161 AND ROWNUM <2;
162
163 CURSOR c_cal_conf IS
164 SELECT planning_open_dt_alias, schedule_open_dt_alias
165 FROM igs_en_cal_conf
166 WHERE s_control_num = 1;
167 l_plan_dalias igs_en_cal_conf.planning_open_dt_alias %TYPE;
168 l_sch_dalias igs_en_cal_conf.schedule_open_dt_alias%TYPE;
169
170 i NUMBER;
171 l_n_temp NUMBER;
172 l_d_alias_val DATE;
173 l_c_plan_status igs_en_spa_terms.PLAN_SHT_STATUS%TYPE;
174 l_schedule_available BOOLEAN := FALSE;
175 l_schedule_units_exists BOOLEAN := FALSE;
176 l_plan_exists BOOLEAN := FALSE;
177
178 BEGIN
179 -- Decide whether search is allowed or not.
180 -- Even if search is allowed for a term then the find unit section link should be rendered.
181
182 OPEN c_srch_allwd;
183 FETCH c_srch_allwd INTO l_n_temp;
184 IF c_srch_allwd%FOUND THEN
185 p_sch_allow := 'Y' ; -- Search is allowed
186 ELSE
187 p_sch_allow := 'N'; -- Search is not allowed
188 END IF;
189 CLOSE c_srch_allwd;
190
191 -- Initialize the local variables
192 i:= 0;
193
194 -- get the planning sheet and schedule alias
195 OPEN c_cal_conf;
196 FETCH c_cal_conf INTO l_plan_dalias, l_sch_dalias;
197 CLOSE c_cal_conf;
198
199 --Deciding career mode or program mode
200 IF( fnd_profile.value('CAREER_MODEL_ENABLED') = 'Y') THEN
201 -- Career mode
202 -- Loop thru all the distinct term career combinations
203 FOR rec_career IN c_career(p_n_person_id)
204 LOOP
205 l_schedule_available := FALSE;
206 IF rec_career.schedule_flag = 'Y' THEN
207 -- get the schedule alias value for the current term.
208 l_d_alias_val := igs_ss_enr_details.get_alias_val(rec_career.loadcal, rec_career.sequence_number, l_sch_dalias);
209 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
210 IF l_d_alias_val IS NOT NULL AND TRUNC (l_d_alias_val) <= TRUNC (SYSDATE) THEN
211 l_schedule_available := TRUE;
212 END IF;
213 END IF;
214 -- Check whether the planning sheet profile is ON and planning is allowed for the current term
215 IF rec_career.planning_flag = 'Y' AND
216 NVL(fnd_profile.value('IGS_EN_USE_PLAN'),'OFF') = 'ON' THEN
217
218 l_d_alias_val := igs_ss_enr_details.get_alias_val( rec_career.loadcal, rec_career.sequence_number, l_plan_dalias);
219
220 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
221 IF l_d_alias_val IS NOT NULL AND TRUNC (l_d_alias_val) <= TRUNC (SYSDATE) THEN
222 -- Add the calendar instance, career and Planning (P)/Schedule (S).
223 i:= i+1;
224 p_cal_tbl(i) := rec_career.loadcal;
225 p_seq_tbl(i) := rec_career.sequence_number;
226 p_car_tbl(i) := rec_career.career;
227 p_typ_tbl(i) := 'P'; -- active planning sheet.
228
229 -- Check whether the planning sheet exists for this person, and if exists check
230 -- whether it is submitted or skipped based on the value in the column PLAN_SHT_FLAG
231
232 OPEN c_plan_status (p_n_person_id, rec_career.program_cd, rec_career.loadcal,
233 rec_career.sequence_number);
234 FETCH c_plan_status INTO l_c_plan_status;
235 IF c_plan_status%NOTFOUND THEN
236 l_c_plan_status := 'PLAN';
237 END IF;
238 CLOSE c_plan_status;
239
240 OPEN c_plan_exists(p_n_person_id, rec_career.program_cd, rec_career.loadcal, rec_career.sequence_number);
241 FETCH c_plan_exists INTO l_n_temp;
242 IF c_plan_exists%FOUND THEN
243 l_plan_exists := TRUE;
244 ELSE
245 l_plan_exists := FALSE;
246 END IF;
247 CLOSE c_plan_exists;
248
249 l_schedule_units_exists := Schedule_Units_Exists(p_n_person_id, rec_career.program_cd, rec_career.loadcal, rec_career.sequence_number);
250
251 IF l_schedule_available THEN
252 IF l_schedule_units_exists THEN
253 IF l_plan_exists THEN
254 p_text_tbl(i) := g_c_planning_txt || ' ' || rec_career.description || ' - ' || rec_career.career || ' - ' || g_c_view_only_txt;
255 p_typ_tbl(i) := 'V'; -- planning sheet is view only.
256 ELSE
257 i:= i - 1;
258 END IF;
259 ELSE
260 IF l_plan_exists THEN
261 IF l_c_plan_status IN ('PLAN','NONE') THEN
262 p_text_tbl(i) := g_c_planning_txt || ' ' || rec_career.description || ' - ' || rec_career.career;
263 ELSE
264 p_text_tbl(i) := g_c_planning_txt || ' ' || rec_career.description || ' - ' || rec_career.career || ' - ' || g_c_view_only_txt;
265 p_typ_tbl(i) := 'V'; -- planning sheet is view only.
266 END IF;
267 ELSE
268 i:= i - 1;
269 END IF;
270 END IF;
271 ELSE
272 IF l_schedule_units_exists THEN
273 p_text_tbl(i) := g_c_planning_txt || ' ' || rec_career.description || ' - ' || rec_career.career || ' - ' || g_c_view_only_txt;
274 p_typ_tbl(i) := 'V'; -- planning sheet is view only.
275 ELSE
276 IF l_c_plan_status IN ('PLAN','NONE') THEN
277 p_text_tbl(i) := g_c_planning_txt || ' ' || rec_career.description || ' - ' || rec_career.career;
278 ELSE
279 p_text_tbl(i) := g_c_planning_txt || ' ' || rec_career.description || ' - ' || rec_career.career || ' - ' || g_c_view_only_txt;
280 p_typ_tbl(i) := 'V'; -- planning sheet is view only.
281 END IF;
282 END IF;
283 END IF;
284
285 END IF; -- end of date alias validation
286 END IF; -- end of planning sheet allowed validation
287
288 -- Check whether the schedule is allowed for the current term
289 IF l_schedule_available THEN
290 -- Check whether student has timeslot and
291 IF igs_ss_enr_details.stu_timeslot_open (p_n_person_id, p_c_person_type,
292 rec_career.program_cd, rec_career.loadcal, rec_career.sequence_number) THEN
293 -- Add the calendar instance, career and Planning (P)/Schedule (S).
294 i := i+1;
295 P_cal_tbl(i) := rec_career.loadcal;
296 P_seq_tbl(i) := rec_career.sequence_number;
297 p_car_tbl(i) := rec_career.career;
298 p_typ_tbl(i) := 'S';
299 P_text_tbl(i) := g_c_schedule_txt || ' ' || rec_career.description || ' - ' || rec_career.career || ' - ' || g_c_enr_open_txt;
300 END IF;
301 END IF;
302 END LOOP;
303 ELSE
304 -- Program mode is enabled.
305 FOR rec_program in c_program (p_n_person_id)
306 LOOP
307 l_schedule_available := FALSE;
308 IF rec_program.schedule_flag = 'Y' THEN
309 -- get the schedule alias value for the current term.
310 l_d_alias_val := igs_ss_enr_details.get_alias_val(rec_program.loadcal, rec_program.sequence_number, l_sch_dalias);
311 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
312 IF l_d_alias_val IS NOT NULL AND TRUNC(l_d_alias_val) <= TRUNC(SYSDATE) THEN
313 l_schedule_available := TRUE;
314 END IF;
315 END IF;
316 -- Check whether the planning sheet profile is ON and planning is allowed for the current term
317 IF rec_program.planning_flag = 'Y' AND NVL(fnd_profile.value('IGS_EN_USE_PLAN'),'OFF') = 'ON'
318 THEN
319 -- get the planning sheet alias value for the current term.
320 l_d_alias_val := igs_ss_enr_details.get_alias_val(rec_program.loadcal, rec_program.sequence_number, l_plan_dalias);
321 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
322 IF l_d_alias_val IS NOT NULL AND TRUNC (l_d_alias_val) <= TRUNC (SYSDATE) THEN
323 -- Add the calendar instance, career and Planning (P)/Schedule (S).
324 i:= i+1;
325 p_cal_tbl(i) := rec_program.loadcal;
326 p_seq_tbl(i) := rec_program.sequence_number;
327 p_car_tbl(i) := rec_program.program_cd;
328 p_typ_tbl(i) := 'P';
329
330 -- Check whether the planning sheet exists for this person, and if exists check
331 -- whether it is submitted or skipped based on the value in the column PLAN_SHT_FLAG
332 OPEN c_plan_status (p_n_person_id, rec_program.program_cd, rec_program.loadcal, rec_program.sequence_number);
333 FETCH c_plan_status INTO l_c_plan_status;
334 IF c_plan_status%NOTFOUND THEN
335 l_c_plan_status := 'PLAN';
336 END IF;
337 CLOSE c_plan_status;
338
339 OPEN c_plan_exists (p_n_person_id, rec_program.program_cd, rec_program.loadcal, rec_program.sequence_number);
340 FETCH c_plan_exists INTO l_n_temp;
341 IF c_plan_exists%FOUND THEN
342 l_plan_exists := TRUE;
343 ELSE
344 l_plan_exists := FALSE;
345 END IF;
346 CLOSE c_plan_exists;
347
348 l_schedule_units_exists := Schedule_Units_Exists(p_n_person_id, rec_program.program_cd, rec_program.loadcal, rec_program.sequence_number);
349
350 IF l_schedule_available THEN
351 IF l_schedule_units_exists THEN
352 IF l_plan_exists THEN
353 P_text_tbl(i) := g_c_planning_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd || ' - ' || g_c_view_only_txt;
354 P_typ_tbl(i) := 'V'; -- Denotes planning sheet is view only.
355 ELSE
356 i:= i - 1;
357 END IF;
358 ELSE
362 ELSE
359 IF l_plan_exists THEN
360 IF l_c_plan_status IN ('PLAN','NONE') THEN
361 P_text_tbl(i) := g_c_planning_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd;
363 P_text_tbl(i) := g_c_planning_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd || ' - ' || g_c_view_only_txt;
364 P_typ_tbl(i) := 'V'; -- Denotes planning sheet is view only.
365 END IF;
366 ELSE
367 i:= i - 1;
368 END IF;
369 END IF;
370 ELSE
371 IF l_schedule_units_exists THEN
372 P_text_tbl(i) := g_c_planning_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd || ' - ' || g_c_view_only_txt;
373 P_typ_tbl(i) := 'V'; -- Denotes planning sheet is view only.
374 ELSE
375 IF l_c_plan_status IN ('PLAN','NONE') THEN
376 P_text_tbl(i) := g_c_planning_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd;
377 ELSE
378 P_text_tbl(i) := g_c_planning_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd || ' - ' || g_c_view_only_txt;
379 P_typ_tbl(i) := 'V'; -- Denotes planning sheet is view only.
380 END IF;
381 END IF;
382 END IF;
383
384 END IF;
385 END IF;
386 -- Check whether the schedule is allowed for the current term
387 IF l_schedule_available THEN
388 -- Check whether student has timeslot and
389 IF igs_ss_enr_details.stu_timeslot_open (p_n_person_id, p_c_person_type,
390 rec_program.program_cd, rec_program.loadcal, rec_program.sequence_number) THEN
391 -- Add the calendar instance, career and Planning (P)/Schedule (S).
392 i := i+1;
393 p_cal_tbl(i) := rec_program.loadcal;
394 P_seq_tbl(i) := rec_program.sequence_number;
395 P_car_tbl(i) := rec_program.program_cd;
396 p_typ_tbl(i) := 'S';
397 P_text_tbl(i) := g_c_schedule_txt || ' ' || rec_program.description || ' - ' || rec_program.program_cd || ' - ' || g_c_enr_open_txt;
398 END IF;
399 END IF;
400 END LOOP;
401 END IF;
402
403 END student_api;
404
405
406 FUNCTION Schedule_Units_Exists ( cp_n_person_id IN NUMBER,
407 cp_c_program_cd IN VARCHAR2,
408 cp_c_cal_type IN VARCHAR2,
409 cp_n_seq_num IN NUMBER ) RETURN BOOLEAN AS
410 ------------------------------------------------------------------------------------
411 --Created by : jnalam ( Oracle IDC)
412 --Date created: 18-Nov-2005
413 --
414 --Purpose: this function returns true/false depending upon whether there are
415 -- any units in the schedule or not. Bug #4742735
416 --
417 --Known limitations/enhancements and/or remarks:
418 --
419 --Change History:
420 --Who When What
421 -- smaddali 8-mar-06 removed cursor c_sch_pending_units_exists for bug5091853
422 --------------------------------------------------------------------------------------
423
424 -- Cursor to check whether student has enrolled unit sections in schedule for term and career / program
425 CURSOR c_sch_enr_units_exists IS
426 SELECT
427 sua.person_id,
428 sca.course_cd,
429 tt11.load_cal_type term_cal_type,
430 tt11.load_ci_sequence_number Term_Sequence_Number,
431 sua.unit_attempt_status
432 FROM igs_en_su_attempt sua,
433 igs_en_stdnt_ps_att sca,
434 igs_ca_teach_to_load_v tt11
435 WHERE
436 sca.person_id = sua.person_id
437 AND sca.course_cd = sua.course_cd
438 AND sua.unit_attempt_status NOT IN ('UNCONFIRM')
439 AND tt11.teach_cal_type = sua.cal_type
440 AND tt11.teach_ci_sequence_number = sua.ci_sequence_number
441 AND NOT EXISTS (SELECT * FROM igs_ps_usec_ref usr WHERE usr.uoo_id = sua.uoo_id AND NVL(CLASS_SCHEDULE_EXCLUSION_FLAG,'N') = 'Y')
442 AND sua.person_id = cp_n_person_id
443 AND sca.course_cd = cp_c_program_cd
444 AND tt11.load_cal_type = cp_c_cal_type
445 AND tt11.load_ci_sequence_number = cp_n_seq_num
446 AND sua.unit_attempt_status IN ('ENROLLED','DISCONTIN','INVALID', 'COMPLETED', 'WAITLISTED');
447
448
449 enr_rowid c_sch_enr_units_exists%ROWTYPE;
450
451 BEGIN
452 OPEN c_sch_enr_units_exists;
453 FETCH c_sch_enr_units_exists INTO enr_rowid;
454 IF (c_sch_enr_units_exists%FOUND) THEN
455 CLOSE c_sch_enr_units_exists;
456 RETURN (TRUE);
457 ELSE
458 CLOSE c_sch_enr_units_exists;
459 RETURN (FALSE);
460 END IF;
461
462 END Schedule_Units_Exists;
463
464 END igs_en_dashboard;
465
[Home] [Help]