1 PACKAGE BODY IGS_AS_PRC_TRANSCRPT AS
2 /* $Header: IGSAS08B.pls 120.1 2006/02/13 02:40:57 amanohar noship $ */
3 /* Change History :
4 Who When What
5
6 ckasu 19-APR-2004 BUG#3553220 - Modified declaration of v_out_string
7 variable in procedure assp_get_trn_res_dtl
8 jbegum 25-Jun-2003 BUG#2930935
9 Modified local functions ASSP_GET_TRN_SUT_DTL,
10 ASSP_GET_TRN_SUA_DTL.
11 */
12 -- Retrieves graduation details for display on transcript
13 FUNCTION assp_get_trn_grd_dtl(
14 p_person_id IN NUMBER ,
15 p_course_cd IN VARCHAR2 ,
16 p_s_letter_parameter_type IN VARCHAR2 ,
17 p_acad_cal_type IN VARCHAR2 ,
18 p_acad_ci_sequence_number IN NUMBER ,
19 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
20 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
21 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
22 p_exclude_unit_category IN VARCHAR2 ,
23 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
24 p_record_number IN NUMBER )
25 RETURN VARCHAR2 IS
26
27 BEGIN -- assp_get_trn_grd_dtl
28 -- This module retrieves student graduation details for use in the
29 -- Correspondence Letter Facility for such correspondence as the Academic
30 -- Transcript.
31 --
32 -- The elements retrieved by this module are:
33 -- Gradiation Statement.
34 DECLARE
35 v_index BINARY_INTEGER;
36 v_out_string VARCHAR2(300);
37 CURSOR c_grd IS
38 SELECT sca.person_id,
39 sca.course_cd,
40 sca.course_rqrmnts_complete_dt,
41 SYSDATE,
42 ci.alternate_code,
43 gr.s_graduand_type,
44 gr.conferral_dt,
45 gst.s_graduand_status,
46 aw.award_title
47 FROM IGS_EN_STDNT_PS_ATT sca,
48 IGS_GR_GRADUAND gr,
49 IGS_GR_STAT gst,
50 IGS_CA_INST ci,
51 IGS_PS_AWD aw
52 WHERE sca.person_id = p_person_id AND
53 sca.course_cd = p_course_cd AND
54 sca.course_rqrmnt_complete_ind = 'Y' AND
55 sca.person_id = gr.person_id AND
56 sca.course_cd = gr.course_cd AND
57 gr.GRADUAND_STATUS = gst.GRADUAND_STATUS AND
58 gst.s_graduand_status in ('ELIGIBLE','GRADUATED') AND
59 gr.award_cd = aw.award_cd AND
60 ci.CAL_TYPE = p_acad_cal_type AND
61 ci.sequence_number = p_acad_ci_sequence_number AND
62 ((sca.course_rqrmnts_complete_dt BETWEEN ci.start_dt AND ci.end_dt) OR
63 (sca.course_rqrmnts_complete_dt >
64 ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
65 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
66 p_person_id,
67 p_course_cd,
68 p_include_fail_grade_ind,
69 p_enrolled_units_ind,
70 p_exclude_research_units_ind,
71 p_exclude_unit_category,
72 p_include_related_crs_ind)))
73 UNION
74 SELECT sca.person_id,
75 sca.course_cd,
76 sca.course_rqrmnts_complete_dt,
77 SYSDATE,
78 ci.alternate_code,
79 NULL,
80 SYSDATE,
81 NULL,
82 NULL
83 FROM IGS_EN_STDNT_PS_ATT sca,
84 IGS_CA_INST ci
85 WHERE sca.person_id = p_person_id AND
86 sca.course_cd = p_course_cd AND
87 sca.course_rqrmnt_complete_ind = 'Y' AND
88 ci.CAL_TYPE = p_acad_cal_type AND
89 ci.sequence_number = p_acad_ci_sequence_number AND
90 ((sca.course_rqrmnts_complete_dt BETWEEN ci.start_dt AND ci.end_dt) OR
91 (sca.course_rqrmnts_complete_dt >
92 ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
93 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
94 p_person_id,
95 p_course_cd,
96 p_include_fail_grade_ind,
97 p_enrolled_units_ind,
98 p_exclude_research_units_ind,
99 p_exclude_unit_category,
100 p_include_related_crs_ind))) AND
101 NOT EXISTS (select '1' from IGS_GR_GRADUAND gr
102 where gr.person_id = sca.person_id
103 and gr.course_cd = sca.course_cd)
104 ORDER BY 3;
105 BEGIN
106 -- Determine if this is the first time the procedure has been called for the
107 -- PERSON.
108 -- If so, then populate the PL/SQL table that will be used to retrieve the rest
109 -- of the records returned from the query.
110 IF p_record_number = 1 THEN
111 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index := 0;
112 FOR v_grd_rec IN c_grd LOOP
113 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index := c_grd%ROWCOUNT;
114 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
115 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_acad_alternate_code :=
116 v_grd_rec.alternate_code;
117 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
118 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_course_cd := v_grd_rec.course_cd;
119 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
120 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_completion_dt :=
121 v_grd_rec.course_rqrmnts_complete_dt;
122 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
123 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_conferral_dt :=
124 v_grd_rec.conferral_dt;
125 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
126 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_award_title :=
127 v_grd_rec.award_title;
128 IF v_grd_rec.s_graduand_type = 'ARTICULATE' THEN
129 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
130 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_type := 'COMP_NOAWD';
131 ELSIF v_grd_rec.s_graduand_status = 'GRADUATED' THEN
132 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
133 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_type := 'COMP_GRAD';
134 ELSIF v_grd_rec.s_graduand_status = 'ELIGIBLE' THEN
135 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
136 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_type := 'COMP_ELIG';
137 ELSE
138 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
139 IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index).v_type := 'COMPLETED';
140 END IF;
141 END LOOP;
142 END IF;
143 -- Create the output string based on the p_s_letter_parameter_type and the
144 -- p_record_number
145 v_index := p_record_number;
146 v_out_string := NULL;
147 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_grd_dtl_index THEN
148 IF p_record_number = 1 THEN -- first time through, do a page throw.
149 IF p_s_letter_parameter_type = 'TRN_GRD_LN' THEN
150 IF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
151 = 'COMP_GRAD' THEN
152 v_out_string := fnd_global.local_chr(10) || 'COURSE REQUIREMENTS COMPLETED ON ' ||
153 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
154 v_index).v_completion_dt) || '. AWARD OF ' ||
155 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
156 v_index).v_award_title
157 ||' CONFERRED ON ' ||
158 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
159 v_index).v_conferral_dt);
160 ELSIF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
161 = 'COMP_ELIG' THEN
162 v_out_string := fnd_global.local_chr(10) || 'COURSE REQUIREMENTS COMPLETED ON ' ||
163 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
164 v_index).v_completion_dt) || '. AWARD OF ' ||
165 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
166 v_index).v_award_title ||
167 ' TO BE CONFERRED AT A FORTHCOMING GRADUATION CEREMONY.';
168 ELSIF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
169 = 'COMP_NOAWD' THEN
170 v_out_string := fnd_global.local_chr(10) || 'COURSE REQUIREMENTS COMPLETED ON ' ||
171 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
172 v_index).v_completion_dt) || '. PROCEEDING TO A HIGHER AWARD.';
173 ELSIF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
174 = 'COMPLETED' THEN
175 v_out_string := fnd_global.local_chr(10) || 'COURSE REQUIREMENTS COMPLETED ON ' ||
176 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
177 v_index).v_completion_dt);
178 END IF;
179 ELSE
180 v_out_string := NULL;
181 END IF;
182 ELSE
183 IF p_s_letter_parameter_type = 'TRN_GRD_LN' THEN
184 IF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
185 = 'COMP_GRAD' THEN
186 v_out_string := 'COURSE REQUIREMENTS COMPLETED ON ' ||
187 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
188 v_index).v_completion_dt) || '. AWARD OF ' ||
189 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
190 v_index).v_award_title
191 ||' CONFERRED ON ' ||
192 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
193 v_index).v_conferral_dt);
194 ELSIF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
195 = 'COMP_ELIG' THEN
196 v_out_string := 'COURSE REQUIREMENTS COMPLETED ON ' ||
197 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
198 v_index).v_completion_dt) || '. AWARD OF ' ||
199 IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
200 v_index).v_award_title ||
201 ' TO BE CONFERRED AT A FORTHCOMING GRADUATION CEREMONY.';
202 ELSIF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
203 = 'COMP_NOAWD' THEN
204 v_out_string := 'COURSE REQUIREMENTS COMPLETED ON ' ||
205 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
206 v_index).v_completion_dt) || '. PROCEEDING TO A HIGHER AWARD.';
207 ELSIF IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(v_index).v_type
208 = 'COMPLETED' THEN
209 v_out_string := 'COURSE REQUIREMENTS COMPLETED ON ' ||
210 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_grd_dtl_table(
211 v_index).v_completion_dt);
212 END IF;
213 ELSE
214 v_out_string := NULL;
215 END IF;
216 END IF;
217 END IF;
218 RETURN v_out_string;
219 EXCEPTION
220 WHEN OTHERS THEN
221 IF c_grd%ISOPEN THEN
222 CLOSE c_grd;
223 END IF;
224 RAISE;
225 END;
226 END assp_get_trn_grd_dtl;
227 --
228 -- To get one component of a string which is delimited.
229 FUNCTION ASSP_GET_TRN_DESC(
230 p_extract_course_cd IN VARCHAR2 )
231 RETURN VARCHAR2 IS
232
233 BEGIN -- assp_get_trn_desc
234 -- Parse the p_input_str, return the p_element_num_th
235 -- of the string delimited by p_delimiter.
236 DECLARE
237 v_ret_val VARCHAR2(30);
238 BEGIN
239 -- Validate input parameter
240 IF p_extract_course_cd IS NULL THEN
241 RETURN NULL;
242 ELSE
243 v_ret_val := 'EXTRACT OF ACADEMIC RECORD';
244 RETURN v_ret_val;
245 END IF;
246 END;
247 END assp_get_trn_desc;
248 --
249 -- Retrieves research details for display on transcript.
250 FUNCTION assp_get_trn_res_dtl(
251 p_person_id IN NUMBER ,
252 p_course_cd IN VARCHAR2 ,
253 p_s_letter_parameter_type IN VARCHAR2 ,
254 p_acad_cal_type IN VARCHAR2 ,
255 p_acad_ci_sequence_number IN NUMBER ,
256 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
257 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
258 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
259 p_exclude_unit_category IN VARCHAR2 ,
260 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
261 p_record_number IN NUMBER )
262 RETURN VARCHAR2 IS
263
264 BEGIN -- assp_get_trn_res_dtl
265 -- Description.:This function determines if the PERSON has undertaken any
266 -- research for the nominated COURSE code. It returns the THESIS TITLE
267 -- for use in the Correspondence Letter Facility for such correspondence
268 -- as the Academic Transcript.
269 --Who When What
270 --jbegum 25-Jun-2003 BUG#2930935 - Modified cursor c_sua.
271 --ckasu 19-APR-2004 BUG#3553220 - Modified declaration of
272 -- v_out_string variable
273
274 DECLARE
275 v_index BINARY_INTEGER;
276 v_out_string VARCHAR2(2100);
277 CURSOR c_res IS
278 SELECT ca.person_id,
279 ca.sca_course_cd,
280 th.TITLE,
281 th.final_title_ind,
282 ci.alternate_code
283 FROM IGS_RE_CANDIDATURE ca,
284 IGS_RE_THESIS th,
285 IGS_EN_STDNT_PS_ATT sca,
286 IGS_CA_INST ci
287 WHERE ca.person_id = p_person_id AND
288 ca.sca_course_cd = p_course_cd AND
289 ca.person_id = th.person_id AND
290 ca.sequence_number = th.ca_sequence_number AND
291 ca.person_id = sca.person_id AND
292 ca.sca_course_cd = sca.course_cd AND
293 ci.CAL_TYPE = p_acad_cal_type AND
294 ci.sequence_number = p_acad_ci_sequence_number AND
295 ((sca.commencement_dt BETWEEN ci.start_dt AND ci.end_dt) OR
296 (sca.commencement_dt >
297 ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
298 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
299 p_person_id,
300 p_course_cd,
301 p_include_fail_grade_ind,
302 p_enrolled_units_ind,
303 p_exclude_research_units_ind,
304 p_exclude_unit_category,
305 p_include_related_crs_ind)))
306 ORDER BY
307 sca.commencement_dt;
308 BEGIN
309 IF p_person_id IS NULL OR
310 p_course_cd IS NULL OR
311 p_s_letter_parameter_type IS NULL OR
312 p_acad_cal_type IS NULL OR
313 p_acad_ci_sequence_number IS NULL OR
314 p_record_number IS NULL THEN
315 RETURN NULL;
316 END IF;
317 IF p_record_number = 1 THEN
318 IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index := 0;
319 FOR v_res_rec IN c_res LOOP
320 IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index := c_res%ROWCOUNT;
321 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(
322 IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index).v_teach_alternate_code :=
323 v_res_rec.alternate_code;
324 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(
325 IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index).v_course_cd :=
326 v_res_rec.sca_course_cd;
327 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(
328 IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index).v_title :=
329 v_res_rec.TITLE;
330 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(
331 IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index).v_final_title_ind :=
332 v_res_rec.final_title_ind;
333 END LOOP;
334 END IF;
335 v_index := p_record_number;
336 v_out_string := NULL;
337 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_res_dtl_index THEN
338 IF p_s_letter_parameter_type = 'TRN_RES_LN' THEN
339 IF p_record_number = 1 THEN
340 IF IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(v_index).v_final_title_ind
341 = 'Y' THEN
342 v_out_string := fnd_global.local_chr(10) || 'THESIS TITLE: ' ||
343 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(v_index).v_title;
344 ELSE
345 v_out_string := fnd_global.local_chr(10) || 'WORKING THESIS TITLE: ' ||
346 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(v_index).v_title;
347 END IF;
348 ELSE
349 IF IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(v_index).v_final_title_ind
350 = 'Y' THEN
351 v_out_string := 'THESIS TITLE: ' ||
352 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(v_index).v_title;
353 ELSE
354 v_out_string := 'WORKING THESIS TITLE: ' ||
355 IGS_AS_PRC_TRANSCRPT.gt_res_dtl_table(v_index).v_title;
356 END IF;
357 END IF;
358 END IF;
359 END IF;
360 RETURN v_out_string;
361 EXCEPTION
362 WHEN OTHERS THEN
363 IF (c_res%ISOPEN) THEN
364 CLOSE c_res;
365 END IF;
366 RAISE;
367 END;
368 END assp_get_trn_res_dtl;
369 --
370 -- Retrieves UNIT set attempt details for display on transcript.
371 FUNCTION assp_get_trn_us_dtl(
372 p_person_id IN NUMBER ,
373 p_course_cd IN VARCHAR2 ,
374 p_s_letter_parameter_type IN VARCHAR2 ,
375 p_acad_cal_type IN VARCHAR2 ,
376 p_acad_ci_sequence_number IN NUMBER ,
377 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
378 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
379 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
380 p_exclude_unit_category IN VARCHAR2 ,
381 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
382 p_record_number IN NUMBER )
383 RETURN VARCHAR2 IS
384
385 BEGIN -- assp_get_trn_us_dtl
386 -- This module retrieves student UNIT set attempt deatails for use in the
387 -- Correspondence Letter Facility for such correspondence as the
388 -- Academic Transcript.
389 --
390 -- The elements retrieved by this module are:
391 -- UNIT Set Code,
392 -- UNIT Set TITLE,
393 -- UNIT Set Category,
394 -- Selection Date,
395 -- Completion Date,
396 -- Primary Set Indicator
397 DECLARE
398 v_index BINARY_INTEGER;
399 v_out_string VARCHAR2(300);
400 cst_trn_usc_al CONSTANT VARCHAR2(15) := 'TRN_USC_AL';
401 cst_trn_uss_al CONSTANT VARCHAR2(15) := 'TRN_USS_AL';
402 cst_trn_usc_ps CONSTANT VARCHAR2(15) := 'TRN_USC_PS';
403 cst_trn_uss_ps CONSTANT VARCHAR2(15) := 'TRN_USS_PS';
404 cst_trn_us_usc CONSTANT VARCHAR2(15) := 'TRN_US_USC';
405 cst_trn_us_tl CONSTANT VARCHAR2(15) := 'TRN_US_TL';
406 cst_trn_us_cd CONSTANT VARCHAR2(15) := 'TRN_US_CD';
407 CURSOR c_susa IS
408 SELECT susa.person_id,
409 susa.unit_set_cd,
410 NVL(susa.override_title, us.title) unit_set_title,
411 us.UNIT_SET_CAT,
412 usc.description,
413 susa.selection_dt,
414 susa.end_dt,
415 susa.rqrmnts_complete_dt,
416 susa.primary_set_ind
417 FROM IGS_AS_SU_SETATMPT susa,
418 IGS_EN_UNIT_SET us,
419 IGS_EN_UNIT_SET_CAT usc,
420 IGS_CA_INST ci
421 WHERE susa.person_id = p_person_id AND
422 susa.course_cd = p_course_cd AND
423 susa.student_confirmed_ind = 'Y' AND
424 susa.rqrmnts_complete_ind = 'Y' AND
425 susa.end_dt IS NULL AND
426 susa.unit_set_cd = us.unit_set_cd AND
427 susa.us_version_number = us.version_number AND
428 us.UNIT_SET_CAT = usc.UNIT_SET_CAT AND
429 ci.CAL_TYPE = p_acad_cal_type AND
430 ci.sequence_number = p_acad_ci_sequence_number AND
431 (((susa.selection_dt BETWEEN ci.start_dt AND ci.end_dt) OR
432 (susa.rqrmnts_complete_dt BETWEEN ci.start_dt AND ci.end_dt)) OR
433 (susa.selection_dt >
434 ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
435 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
436 p_person_id,
437 p_course_cd,
438 p_include_fail_grade_ind,
439 p_enrolled_units_ind,
440 p_exclude_research_units_ind,
441 p_exclude_unit_category,
442 p_include_related_crs_ind)) OR
443 (susa.rqrmnts_complete_dt >
444 ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
445 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
446 p_person_id,
447 p_course_cd,
448 p_include_fail_grade_ind,
449 p_enrolled_units_ind,
450 p_exclude_research_units_ind,
451 p_exclude_unit_category,
452 p_include_related_crs_ind)))
453 ORDER BY
454 susa.unit_set_cd,
455 susa.selection_dt,
456 susa.rqrmnts_complete_dt;
457 BEGIN
458 -- Determine if this is the first time the procedure has been called for the
459 -- PERSON
460 -- (p_record_number = 1).
461 IF p_record_number = 1 THEN
462 -- Intialise the counter for the PL/SQL table.
463 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index := 0;
464 -- Populate the PL/SQL table.
465 FOR v_susa_rec IN c_susa LOOP
466 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index := c_susa%ROWCOUNT;
467 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
468 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_unit_set_cd :=
469 v_susa_rec.unit_set_cd;
470 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
471 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_title :=
472 v_susa_rec.unit_set_title;
473 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
474 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_unit_set_cat :=
475 v_susa_rec.UNIT_SET_CAT;
476 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
477 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_unit_set_cat_desc :=
478 v_susa_rec.description;
479 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
480 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_selection_dt :=
481 v_susa_rec.selection_dt;
482 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
483 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_primary_set_ind :=
484 v_susa_rec.primary_set_ind;
485 IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
486 IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_completion_dt :=
487 v_susa_rec.rqrmnts_complete_dt;
488 END LOOP;
489 END IF;
490 -- Create thee output string based on the p_s_letter_parameter_type and the
491 -- p_record_number.
492 v_index := p_record_number;
493 v_out_string := NULL;
494 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index THEN
495 IF p_s_letter_parameter_type = cst_trn_usc_al THEN
496 -- all completed UNIT sets
497 IF IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
498 v_index).v_completion_dt IS NOT NULL THEN
499 IF p_record_number = 1 THEN
500 v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
501 v_index).v_title,'-') || ' COMPLETED ON ' ||
502 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
503 v_index).v_completion_dt);
504 ELSE
505 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
506 v_index).v_title,'-') || ' COMPLETED ON ' ||
507 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
508 v_index).v_completion_dt);
509 END IF;
510 END IF;
511 ELSIF p_s_letter_parameter_type = cst_trn_uss_al THEN
512 -- all selected UNIT sets.
513 IF IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
514 v_index).v_selection_dt IS NOT NULL THEN
515 IF p_record_number = 1 THEN
516 v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
517 v_index).v_title,'-')
518 || ' SELECTED ON ' ||
519 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
520 v_index).v_selection_dt);
521 ELSE
522 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
523 v_index).v_title,'-')
524 || ' SELECTED ON ' ||
525 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
526 v_index).v_selection_dt);
527 END IF;
528 END IF;
529 ELSIF p_s_letter_parameter_type = cst_trn_usc_ps THEN
530 -- only completed primary UNIT sets.
531 IF IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
532 v_index).v_primary_set_ind = 'Y' THEN
533 IF IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
534 v_index).v_completion_dt IS NOT NULL THEN
535 IF p_record_number = 1 THEN
536 v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
537 v_index).v_title,'-')
538 || ' COMPLETED ON ' ||
539 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(v_index).v_completion_dt);
540 ELSE
541 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
542 v_index).v_title,'-')
543 || ' COMPLETED ON ' ||
544 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
545 v_index).v_completion_dt);
546 END IF;
547 END IF;
548 END IF;
549 ELSIF p_s_letter_parameter_type = cst_trn_uss_ps THEN
550 -- only selected primary UNIT sets.
551 IF IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
552 v_index).v_primary_set_ind = 'Y' THEN
553 IF IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
554 v_index).v_selection_dt IS NOT NULL THEN
555 IF p_record_number = 1 THEN
556 v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
557 v_index).v_title,'-')
558 || ' SELECTED ON ' ||
559 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
560 v_index).v_selection_dt);
561 ELSE
562 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
563 v_index).v_title,'-')
564 || ' SELECTED ON ' ||
565 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
566 v_index).v_selection_dt);
567 END IF;
568 END IF;
569 END IF;
570 ELSIF p_s_letter_parameter_type = cst_trn_us_usc THEN
571 v_out_string := RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
572 v_index).v_unit_set_cat,'-'),10);
573 ELSIF p_s_letter_parameter_type = cst_trn_us_tl THEN
574 v_out_string := RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
575 v_index).v_title,'-'),90);
576 ELSIF p_s_letter_parameter_type = cst_trn_us_cd THEN
577 v_out_string := RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
578 v_index).v_unit_set_cd,'-'),10);
579 END IF;
580 END IF;
581 RETURN v_out_string;
582 EXCEPTION
583 WHEN OTHERS THEN
584 IF c_susa%ISOPEN THEN
585 CLOSE c_susa;
586 END IF;
587 RAISE;
588 END;
589
590 END assp_get_trn_us_dtl;
591 --
592 -- Retrieves UNIT transfer details for display on transcript
593 FUNCTION assp_get_trn_sut_dtl(
594 p_person_id IN NUMBER ,
595 p_to_course_cd IN VARCHAR2 ,
596 p_s_letter_parameter_type IN VARCHAR2 ,
597 p_acad_cal_type IN VARCHAR2 ,
598 p_acad_ci_sequence_number IN NUMBER ,
599 p_record_number IN NUMBER )
600 RETURN VARCHAR2 IS
601
602 BEGIN -- assp_get_trn_sut_dtl
603 -- This module retrieves transferred UNIT details for use in the
604 -- Correspondence Letter Facility for such correspondence as the
605 -- Academic Transcript.
606 --
607 -- The elements retrieved by this module are:
608 -- UNIT Code,
609 -- UNIT TITLE,
610 -- Credit POintsa Achievable,
611 -- UNIT Level,
612 -- Mark,
613 -- Grade.
614 DECLARE
615 v_index BINARY_INTEGER;
616 v_out_string VARCHAR2(300);
617 v_s_result VARCHAR2(20);
618 v_outcome_dt DATE;
619 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
620 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
621 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
622 v_mark IGS_AS_GRD_SCH_GRADE.lower_mark_range%TYPE;
623 v_origin_course_cd IGS_PS_VER.course_cd%TYPE;
624 cst_trn_sut_ln CONSTANT VARCHAR2(15) := 'TRN_SUT_LN';
625 cst_trn_sut_cd CONSTANT VARCHAR2(15) := 'TRN_SUT_CD';
626 cst_trn_sut_ul CONSTANT VARCHAR2(15) := 'TRN_SUT_UL';
627 cst_trn_sut_tl CONSTANT VARCHAR2(15) := 'TRN_SUT_TL';
628 cst_trn_sut_pd CONSTANT VARCHAR2(15) := 'TRN_SUT_PD';
629 cst_trn_sut_yr CONSTANT VARCHAR2(15) := 'TRN_SUT_YR';
630 cst_trn_sut_mk CONSTANT VARCHAR2(15) := 'TRN_SUT_MK';
631 cst_trn_sut_gd CONSTANT VARCHAR2(15) := 'TRN_SUT_GD';
632 cst_trn_sut_gs CONSTANT VARCHAR2(15) := 'TRN_SUT_GS';
633 cst_trn_sut_gv CONSTANT VARCHAR2(15) := 'TRN_SUT_GV';
634 cst_trn_sut_cp CONSTANT VARCHAR2(15) := 'TRN_SUT_CP';
635
636 --Who When What
637 --jbegum 25-Jun-2003 BUG#2930935 - Modified cursor c_sut.
638 CURSOR c_sut IS
639 SELECT sut.person_id, sut.course_cd, sut.unit_cd, sut.cal_type,
640 sut.ci_sequence_number, sut.uoo_id, uv.short_title, uv.title,
641 NVL (cps.achievable_credit_points, uv.achievable_credit_points),
642 uv.unit_level,
643 igs_en_gen_014.enrs_get_acad_alt_cd
644 (sut.cal_type,
645 sut.ci_sequence_number
646 ) acad_alternate_code,
647 igs_ca_gen_001.calp_get_alt_cd
648 (sut.cal_type,
649 sut.ci_sequence_number
650 ) teach_alternate_code,
651 ci.alternate_code,
652 NVL (suav.override_achievable_cp,
653 NVL (cps.achievable_credit_points, uv.achievable_credit_points)
654 ) v_cp_achieved,
655 NVL (suav.override_enrolled_cp,
656 NVL (cps.enrolled_credit_points, uv.enrolled_credit_points)
657 ) v_cp_achievable
658 FROM igs_ps_stdnt_unt_trn sut,
659 igs_ps_stdnt_trn sct,
660 igs_en_su_attempt_all suav,
661 igs_ps_usec_cps cps,
662 igs_ps_unit_ver uv,
663 igs_ca_inst ci
664 WHERE sut.person_id = p_person_id
665 AND sut.course_cd = p_to_course_cd
666 AND sut.person_id = sct.person_id
667 AND sut.course_cd = sct.course_cd
668 AND sut.transfer_course_cd = sct.transfer_course_cd
669 AND sut.unit_cd = uv.unit_cd
670 AND suav.person_id = sut.person_id
671 AND suav.course_cd = sut.transfer_course_cd
672 AND suav.version_number = uv.version_number
673 AND suav.uoo_id = sut.uoo_id
674 AND suav.uoo_id = cps.uoo_id(+)
675 AND ci.cal_type = p_acad_cal_type
676 AND ci.sequence_number = p_acad_ci_sequence_number
677 AND (sut.transfer_dt BETWEEN ci.start_dt AND ci.end_dt)
678 ORDER BY sut.unit_cd;
679
680 BEGIN
681 -- Determine if this is the first time the procedure has been run for the
682 -- PERSON.
683 IF p_record_number = 1 THEN
684 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index := 0;
685 FOR v_sut_rec IN c_sut LOOP
686 v_s_result := IGS_AS_GEN_003.ASSP_GET_SUA_OUTCOME(
687 v_sut_rec.person_id,
688 v_sut_rec.course_cd,
689 v_sut_rec.unit_cd,
690 v_sut_rec.CAL_TYPE,
691 v_sut_rec.ci_sequence_number,
692 'DUPLICATE',
693 'Y',
694 v_outcome_dt,
695 v_grading_schema_cd,
696 v_gs_version_number,
697 v_grade,
698 v_mark,
699 v_origin_course_cd,
700 -- anilk, 22-Apr-2003, Bug# 2829262
701 v_sut_rec.uoo_id,
702 ---added by LKAKI----
703 'N');
704 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index := c_sut%ROWCOUNT;
705 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
706 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_acad_alternate_code :=
707 v_sut_rec.acad_alternate_code;
708 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
709 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_teach_alternate_code :=
710 v_sut_rec.teach_alternate_code;
711 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
712 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_unit_cd :=
713 v_sut_rec.unit_cd;
714 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
715 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_short_title :=
716 v_sut_rec.short_title;
717 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
718 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_title :=
719 v_sut_rec.TITLE;
720 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
721 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_cp_achievable :=
722 v_sut_rec.v_cp_achievable;
723 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
724 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_cp_achieved :=
725 v_sut_rec.v_cp_achieved;
726 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
727 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_unit_level :=
728 v_sut_rec.UNIT_LEVEL;
729 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
730 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_mark := v_mark;
731 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
732 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_grade := v_grade;
733 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
734 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_grading_schema_cd :=
735 v_grading_schema_cd;
736 IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
737 IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index).v_gs_version_number :=
738 v_gs_version_number;
739 END LOOP;
740 END IF;
741 -- Create the output string based on the p_s_letter_parameter_type and the
742 -- p_record_number
743 v_index := p_record_number;
744 v_out_string := NULL;
745 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_sut_dtl_index THEN
746 IF p_s_letter_parameter_type = cst_trn_sut_ln THEN
747 v_out_string := RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
748 v_index).v_teach_alternate_code,'-'),10)
749 || fnd_global.local_chr(09) ||
750 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
751 v_index).v_unit_cd,'-'),10)
752 || fnd_global.local_chr(09) ||
753 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
754 v_index).v_title,'-'),40);
755 ELSIF p_s_letter_parameter_type = cst_trn_sut_cd THEN
756 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
757 v_index).v_unit_cd,'-');
758 ELSIF p_s_letter_parameter_type = cst_trn_sut_tl THEN
759 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
760 v_index).v_short_title,'-');
761 ELSIF p_s_letter_parameter_type = cst_trn_sut_pd THEN
762 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
763 v_index).v_teach_alternate_code,'-');
764 ELSIF p_s_letter_parameter_type = cst_trn_sut_yr THEN
765 v_out_string := '(' || NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
766 v_index).v_acad_alternate_code,'-') || ')';
767 ELSIF p_s_letter_parameter_type = cst_trn_sut_ul THEN
768 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
769 v_index).v_unit_level,'-');
770 ELSIF p_s_letter_parameter_type = cst_trn_sut_mk THEN
771 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
772 v_index).v_mark,'990'),'-');
773 ELSIF p_s_letter_parameter_type = cst_trn_sut_gd THEN
774 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
775 v_index).v_grade,'-');
776 ELSIF p_s_letter_parameter_type = cst_trn_sut_gs THEN
777 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
778 v_index).v_grading_schema_cd,'-');
779 ELSIF p_s_letter_parameter_type = cst_trn_sut_gv THEN
780 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
781 v_index).v_gs_version_number),'-');
782 ELSIF p_s_letter_parameter_type = cst_trn_sut_cp THEN
783 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sut_dtl_table(
784 v_index).v_cp_achievable,'990D99'),'-');
785 END IF;
786 END IF;
787 RETURN v_out_string;
788 EXCEPTION
789 WHEN OTHERS THEN
790 IF c_sut%ISOPEN THEN
791 CLOSE c_sut;
792 END IF;
793 RAISE;
794 END;
795 EXCEPTION WHEN OTHERS THEN
796 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
797 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_sut_dtl');
798 --IGS_GE_MSG_STACK.ADD;
799 --APP_EXCEPTION.RAISE_EXCEPTION;
800 END assp_get_trn_sut_dtl;
801 --
802 -- Retrieves COURSE transfer details for display on transcript
803 FUNCTION assp_get_trn_sct_dtl(
804 p_person_id IN NUMBER ,
805 p_course_cd IN VARCHAR2 ,
806 p_s_letter_parameter_type IN VARCHAR2 ,
807 p_acad_cal_type IN VARCHAR2 ,
808 p_acad_ci_sequence_number IN NUMBER ,
809 p_record_number IN NUMBER )
810 RETURN VARCHAR2 IS
811
812 BEGIN -- assp_get_trn_sct_dtl
813 -- This module retrieves student COURSE transfer details for use in the
814 -- Correspondence Letter Facility for such correspondence as the
815 -- Academic Transcript.
816 --
817 -- The elements retrieved by this module are:
818 -- COURSE Code,
819 -- Transfer COURSE Code,
820 -- Transfer Date
821 DECLARE
822 v_index BINARY_INTEGER;
823 v_out_string VARCHAR2(300);
824 v_sut_course_cd IGS_PS_STDNT_UNT_TRN.transfer_course_cd%TYPE;
825 CURSOR c_sct IS
826 SELECT sct.person_id,
827 sct.transfer_course_cd,
828 sct.course_cd,
829 sct.transfer_dt,
830 ci.alternate_code
831 FROM IGS_PS_STDNT_TRN sct,
832 IGS_CA_INST ci
833 WHERE sct.person_id = p_person_id AND
834 sct.course_cd = p_course_cd AND
835 ci.CAL_TYPE = p_acad_cal_type AND
836 ci.sequence_number = p_acad_ci_sequence_number AND
837 (sct.transfer_Dt BETWEEN ci.start_dt AND ci.end_dt)
838 ORDER BY
839 sct.transfer_dt;
840 CURSOR c_sut (
841 cp_from_course_cd IGS_PS_STDNT_UNT_TRN.transfer_course_cd%TYPE) IS
842 SELECT sut.unit_cd
843 FROM IGS_PS_STDNT_UNT_TRN sut,
844 IGS_CA_INST ci
845 WHERE sut.person_id = p_person_id AND
846 sut.course_cd = p_course_cd AND
847 sut.transfer_course_cd = cp_from_course_cd AND
848 ci.CAL_TYPE = p_acad_cal_type AND
849 ci.sequence_number = p_acad_ci_sequence_number AND
850 (sut.transfer_dt BETWEEN ci.start_dt AND ci.end_dt);
851 BEGIN
852 -- Determine if this is the first time the procedure has been run for
853 -- the PERSON.
854 IF p_record_number = 1 THEN
855 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index := 0;
856 FOR v_sct_rec IN c_sct LOOP
857 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index := c_sct%ROWCOUNT;
858 IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
859 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index).v_teach_alternate_code :=
860 v_sct_rec.alternate_code;
861 IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
862 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index).v_from_course :=
863 v_sct_rec.transfer_course_cd;
864 IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
865 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index).v_to_course :=
866 v_sct_rec.course_cd;
867 IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
868 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index).v_transfer_dt :=
869 v_sct_rec.transfer_dt;
870 -- Determine if any units were transferred with the COURSE.
871 OPEN c_sut(v_sct_rec.transfer_course_cd);
872 FETCH c_sut INTO v_sut_course_cd;
873 IF (c_sut%NOTFOUND) THEN
874 IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
875 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index).v_unit_ind := 'N';
876 CLOSE c_sut;
877 ELSE
878 IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
879 IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index).v_unit_ind := 'Y';
880 CLOSE c_sut;
881 END IF;
882 END LOOP;
883 END IF;
884 -- Create the output string based on the p_s_letter_parameter_type and the
885 -- p_record_number.
886 v_index := p_record_number;
887 v_out_string := NULL;
888 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_sct_dtl_index THEN
889 IF p_s_letter_parameter_type = 'TRN_SCT' THEN
890 IF p_record_number = 1 THEN
891 IF IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(v_index).v_unit_ind = 'Y' THEN
892 v_out_string := fnd_global.local_chr(10) || 'TRANSFERRED FROM COURSE ' ||
893 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
894 v_index).v_from_course,'-') || ' TO COURSE ' ||
895 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
896 v_index).v_to_course,'-') || ' ON ' ||
897 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
898 v_index).v_transfer_dt) ||
899 '. UNIT ATTEMPTS TRANSFERRED:' || fnd_global.local_chr(10);
900 ELSE
901 v_out_string := fnd_global.local_chr(10) || 'TRANSFERRED FROM COURSE ' ||
902 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
903 v_index).v_from_course,'-') || ' TO COURSE ' ||
904 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
905 v_index).v_to_course,'-') || ' ON ' ||
906 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
907 v_index).v_transfer_dt) || '.' || fnd_global.local_chr(10);
908 END IF;
909 ELSE
910 IF IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(v_index).v_unit_ind = 'Y' THEN
911 v_out_string := 'TRANSFERRED FROM COURSE ' ||
912 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
913 v_index).v_from_course,'-') || ' TO COURSE ' ||
914 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
915 v_index).v_to_course,'-') || ' ON ' ||
916 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
917 v_index).v_transfer_dt) ||
918 '. UNIT ATTEMPTS TRANSFERRED:' || fnd_global.local_chr(10);
919 ELSE
920 v_out_string := 'TRANSFERRED FROM COURSE ' ||
921 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
922 v_index).v_from_course,'-') || ' TO COURSE ' ||
923 NVL(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
924 v_index).v_to_course,'-') || ' ON ' ||
925 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_sct_dtl_table(
926 v_index).v_transfer_dt) || '.' || fnd_global.local_chr(10);
927 END IF;
928 END IF;
929 ELSE
930 v_out_string := NULL;
931 END IF;
932 END IF;
933 RETURN v_out_string;
934 EXCEPTION
935 WHEN OTHERS THEN
936 IF c_sct%ISOPEN THEN
937 CLOSE c_sct;
938 END IF;
939 RAISE;
940 END;
941 EXCEPTION WHEN OTHERS THEN
942 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
943 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_sct_dtl');
944 --IGS_GE_MSG_STACK.ADD;
945 --APP_EXCEPTION.RAISE_EXCEPTION;
946 END assp_get_trn_sct_dtl;
947 --
948 -- Retrieves COURSE standing details for display on transcript
949 FUNCTION assp_get_trn_crs_std(
950 p_person_id IN NUMBER ,
951 p_course_cd IN VARCHAR2 ,
952 p_s_letter_parameter_type IN VARCHAR2 ,
953 p_acad_cal_type IN VARCHAR2 ,
954 p_acad_ci_sequence_number IN NUMBER ,
955 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
956 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
957 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
958 p_exclude_unit_category IN VARCHAR2 ,
959 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
960 p_record_number IN NUMBER )
961 RETURN VARCHAR2 IS
962
963 BEGIN -- assp_get_trn_crs_std
964 -- This module retrieves student COURSE standing details for use in the
965 -- Correspondence Letter Facility for such correspondence as the Academic
966 -- Transcript.
967 --
968 -- The elements retrieved by this module are:
969 -- COURSE Standing Statement.
970 DECLARE
971 v_index BINARY_INTEGER;
972 v_out_string VARCHAR2(300);
973 CURSOR c_sci IS
974 SELECT 'INTERMIT' rec_type,
975 sci.person_id,
976 sci.course_cd,
977 sci.start_dt start_dt,
978 sci.end_dt end_dt,
979 ci.alternate_code,
980 NULL n1,
981 SYSDATE ,
982 NULL n2,
983 NULL n3
984 FROM IGS_EN_STDNT_PS_INTM sci,
985 IGS_CA_INST ci
986 WHERE sci.person_id = p_person_id AND
987 sci.course_cd = p_course_cd AND
988 sci.end_dt IS NOT NULL AND
989 ci.CAL_TYPE = p_acad_cal_type AND
990 ci.sequence_number = p_acad_ci_sequence_number AND
991 ((sci.start_dt BETWEEN ci.start_dt AND ci.end_dt) OR
992 (sci.start_dt > ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
993 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
994 p_person_id,
995 p_course_cd,
996 p_include_fail_grade_ind,
997 p_enrolled_units_ind,
998 p_exclude_research_units_ind,
999 p_exclude_unit_category,
1000 p_include_related_crs_ind)))
1001 UNION
1002 SELECT 'LAPSED' rec_type,
1003 sca.person_id,
1004 sca.course_cd,
1005 sca.lapsed_dt,
1006 SYSDATE,
1007 ci.alternate_code,
1008 NULL n4,
1009 SYSDATE,
1010 NULL n5,
1011 NULL n6
1012 FROM IGS_EN_STDNT_PS_ATT sca,
1013 IGS_CA_INST ci
1014 WHERE sca.person_id = p_person_id AND
1015 sca.course_cd = p_course_cd AND
1016 sca.lapsed_dt IS NOT NULL AND
1017 ci.CAL_TYPE = p_acad_cal_type AND
1018 ci.sequence_number = p_acad_ci_sequence_number AND
1019 ((sca.lapsed_dt BETWEEN ci.start_dt AND ci.end_dt) OR
1020 (sca.lapsed_dt > ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
1021 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
1022 p_person_id,
1023 p_course_cd,
1024 p_include_fail_grade_ind,
1025 p_enrolled_units_ind,
1026 p_exclude_research_units_ind,
1027 p_exclude_unit_category,
1028 p_include_related_crs_ind)))
1029 UNION
1030 SELECT 'DISCONTIN' rec_type,
1031 sca.person_id,
1032 sca.course_cd,
1033 sca.discontinued_dt,
1034 SYSDATE,
1035 ci.alternate_code,
1036 NULL n7,
1037 SYSDATE,
1038 NULL n8,
1039 NULL n9
1040 FROM IGS_EN_STDNT_PS_ATT sca,
1041 IGS_CA_INST ci
1042 WHERE sca.person_id = p_person_id AND
1043 sca.course_cd = p_course_cd AND
1044 sca.discontinued_dt IS NOT NULL AND
1045 ci.CAL_TYPE = p_acad_cal_type AND
1046 ci.sequence_number = p_acad_ci_sequence_number AND
1047 ((sca.discontinued_dt BETWEEN ci.start_dt AND ci.end_dt) OR
1048 (sca.discontinued_dt > ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
1049 IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
1050 p_person_id,
1051 p_course_cd,
1052 p_include_fail_grade_ind,
1053 p_enrolled_units_ind,
1054 p_exclude_research_units_ind,
1055 p_exclude_unit_category,
1056 p_include_related_crs_ind)))
1057 ORDER BY 4;
1058 BEGIN
1059 -- Determine if this is the first time the procedure has been called for the
1060 -- PERSON.
1061 -- If so, then populate the PL/SQL table that will be used to retrieve the rest
1062 -- of the records returned from the query.
1063 IF p_record_number = 1 THEN
1064 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index := 0;
1065 FOR v_sci_rec IN c_sci LOOP
1066 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index := c_sci%ROWCOUNT;
1067 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1068 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_acad_alternate_code :=
1069 v_sci_rec.alternate_code;
1070 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1071 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_course_cd := v_sci_rec.course_cd;
1072 IF v_sci_rec.rec_type = 'INTERMIT' THEN
1073 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1074 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_int_start_dt :=
1075 v_sci_rec.start_dt;
1076 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1077 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_int_end_dt := v_sci_rec.end_dt;
1078 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1079 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_type := 'INTERMIT';
1080 END IF;
1081 IF v_sci_rec.rec_type = 'LAPSED' THEN
1082 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1083 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_lapsed_dt :=
1084 v_sci_rec.start_dt;
1085 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1086 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_type := 'LAPSED';
1087 END IF;
1088 IF v_sci_rec.rec_type = 'DISCONTIN' THEN
1089 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1090 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_discontinued_dt :=
1091 v_sci_rec.start_dt;
1092 IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1093 IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index).v_type := 'DISCONTIN';
1094 END IF;
1095 END LOOP;
1096 END IF;
1097 -- Create the output string based on the p_s_letter_parameter_type and the
1098 -- p_record_number
1099 v_index := p_record_number;
1100 v_out_string := NULL;
1101 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_stdg_dtl_index THEN
1102 IF p_record_number = 1 THEN -- first time through, do a page throw.
1103 IF p_s_letter_parameter_type = 'TRN_STDG' THEN
1104 IF IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_type = 'INTERMIT' THEN
1105 v_out_string := fnd_global.local_chr(10) || 'COURSE ENROLMENT INTERMITTED FROM ' ||
1106 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_int_start_dt
1107 ) || ' TO ' ||
1108 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_int_end_dt);
1109 ELSIF IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_type = 'LAPSED' THEN
1110 v_out_string := fnd_global.local_chr(10) || 'ENROLMENT LAPSED ON ' ||
1111 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_lapsed_dt);
1112 ELSIF IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_type
1113 = 'DISCONTIN' THEN
1114 v_out_string := fnd_global.local_chr(10) || 'COURSE ENROLMENT DISCONTINUED ON ' ||
1115 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1116 v_index).v_discontinued_dt);
1117 END IF;
1118 ELSE
1119 v_out_string := NULL;
1120 END IF;
1121 ELSE
1122 IF p_s_letter_parameter_type = 'TRN_STDG' THEN
1123 IF IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_type = 'INTERMIT' THEN
1124 v_out_string := 'COURSE ENROLMENT INTERMITTED FROM ' ||
1125 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_int_start_dt
1126 ) || ' TO ' ||
1127 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_int_end_dt
1128 );
1129 ELSIF IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_type = 'LAPSED' THEN
1130 v_out_string := 'ENROLEMENT LAPSED ON ' ||
1131 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_lapsed_dt
1132 );
1133 ELSIF IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(v_index).v_type
1134 = 'DISCONTIN' THEN
1135 v_out_string := 'COURSE ENROLMENT DISCONTINUED ON ' ||
1136 FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_stdg_dtl_table(
1137 v_index).v_discontinued_dt);
1138 END IF;
1139 ELSE
1140 v_out_string := NULL;
1141 END IF;
1142 END IF;
1143 END IF;
1144 RETURN v_out_string;
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 IF c_sci%ISOPEN THEN
1148 CLOSE c_sci;
1149 END IF;
1150 RAISE;
1151 END;
1152 EXCEPTION WHEN OTHERS THEN
1153 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1154 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_crs_std');
1155 --IGS_GE_MSG_STACK.ADD;
1156 --APP_EXCEPTION.RAISE_EXCEPTION;
1157 END assp_get_trn_crs_std;
1158 --
1159 -- Retrieves basic UNIT details for display on transcript
1160 FUNCTION assp_get_trn_sua_dtl(
1161 p_person_id IN NUMBER ,
1162 p_course_cd IN VARCHAR2 ,
1163 p_s_letter_parameter_type IN VARCHAR2 ,
1164 p_acad_cal_type IN VARCHAR2 ,
1165 p_acad_ci_sequence_number IN NUMBER ,
1166 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
1167 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
1168 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
1169 p_exclude_unit_category IN VARCHAR2 ,
1170 p_record_number IN NUMBER )
1171 RETURN VARCHAR2 IS
1172
1173 BEGIN -- assp_get_trn_sua_dtl
1174 -- This module retrieves student UNIT attempt details for use in the
1175 -- Correspondence Letter Facility for such correspondece as the
1176 -- Academic Transcript.
1177 --
1178 -- The elements retrieved by this module are:
1179 -- Teaching Period Alternate Code,
1180 -- UNIT Code,
1181 -- UNIT TITLE,
1182 -- Credit Points Achievable,
1183 -- Credit POints Achieved,
1184 -- UNIT Level,
1185 -- Mark,
1186 -- Grade,
1187 -- Grading Schema Code,
1188 -- Grading Schema Version Number.
1189 DECLARE
1190 cst_trn_sua_ln CONSTANT VARCHAR2(15) := 'TRN_UNIT';
1191 cst_trn_sua_cd CONSTANT VARCHAR2(15) := 'TRN_SUA_CD';
1192 cst_trn_sua_tl CONSTANT VARCHAR2(15) := 'TRN_SUA_TL';
1193 cst_trn_sua_pd CONSTANT VARCHAR2(15) := 'TRN_SUA_PD';
1194 cst_trn_sua_ul CONSTANT VARCHAR2(15) := 'TRN_SUA_UL';
1195 cst_trn_sua_mk CONSTANT VARCHAR2(15) := 'TRN_SUA_MK';
1196 cst_trn_sua_gd CONSTANT VARCHAR2(15) := 'TRN_SUA_GD';
1197 cst_trn_sua_gs CONSTANT VARCHAR2(15) := 'TRN_SUA_GS';
1198 cst_trn_ach_cp CONSTANT VARCHAR2(15) := 'TRN_ACH_CP';
1199 cst_trn_enr_cp CONSTANT VARCHAR2(15) := 'TRN_ENR_CP';
1200 cst_trn_sua_cp CONSTANT VARCHAR2(15) := 'TRN_SUA_CP';
1201 cst_trn_sua_gv CONSTANT VARCHAR2(15) := 'TRN_SUA_GV';
1202 v_index BINARY_INTEGER;
1203 v_outcome_dt DATE;
1204 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1205 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1206 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1207 v_mark IGS_AS_GRD_SCH_GRADE.lower_mark_range%TYPE;
1208 v_origin_course_cd IGS_PS_VER.course_cd%TYPE;
1209 v_s_result_type VARCHAR2(20);
1210 v_out_string VARCHAR2(200);
1211
1212 --Who When What
1213 --jbegum 25-Jun-2003 BUG#2930935 - Modified cursor c_sua.
1214
1215 CURSOR c_sua IS
1216 SELECT suav.person_id,
1217 igs_ca_gen_001.calp_get_alt_cd
1218 (suav.cal_type,
1219 suav.ci_sequence_number
1220 ) teach_alternate_code,
1221 suav.unit_cd, NVL (suav.alternative_title, uv.title) v_unit_title,
1222 NVL (suav.alternative_title, uv.short_title) v_unit_short_title,
1223 uv.unit_level, suav.cal_type, suav.ci_sequence_number, suav.uoo_id,
1224 suav.ci_start_dt,
1225 NVL (suav.override_achievable_cp,
1226 NVL (cps.achievable_credit_points, uv.achievable_credit_points)
1227 ) v_cp_achieved,
1228 NVL (suav.override_enrolled_cp,
1229 NVL (cps.enrolled_credit_points, uv.enrolled_credit_points)
1230 ) v_cp_achievable,
1231 suav.unit_attempt_status, suav.administrative_unit_status
1232 FROM igs_en_su_attempt_all suav,
1233 igs_ps_usec_cps cps,
1234 igs_ps_unit_ver uv,
1235 igs_en_stdnt_ps_att sca
1236 WHERE suav.person_id = p_person_id
1237 AND suav.course_cd = p_course_cd
1238 AND sca.person_id = suav.person_id
1239 AND sca.course_cd = suav.course_cd
1240 AND uv.unit_cd = suav.unit_cd
1241 AND uv.version_number = suav.version_number
1242 AND suav.uoo_id = cps.uoo_id(+)
1243 AND suav.unit_attempt_status NOT IN ('UNCONFIRM', 'DUPLICATE')
1244 AND igs_as_gen_001.assp_val_sua_display (suav.person_id,
1245 suav.course_cd,
1246 sca.version_number,
1247 suav.unit_cd,
1248 suav.cal_type,
1249 suav.ci_sequence_number,
1250 suav.unit_attempt_status,
1251 suav.administrative_unit_status,
1252 'Y',
1253 p_include_fail_grade_ind,
1254 p_enrolled_units_ind,
1255 p_exclude_research_units_ind,
1256 p_exclude_unit_category,
1257 suav.uoo_id
1258 ) = 'Y'
1259 AND igs_en_gen_014.enrs_get_within_ci (p_acad_cal_type,
1260 p_acad_ci_sequence_number,
1261 suav.cal_type,
1262 suav.ci_sequence_number,
1263 'Y'
1264 ) = 'Y'
1265 ORDER BY suav.ci_start_dt, suav.unit_cd;
1266
1267 ---------------------------------------- Local Function ------------------------
1268 FUNCTION asspl_val_dsp_unit(
1269 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1270 p_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1271 p_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1272 p_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1273 p_sequence_number IGS_CA_INST.sequence_number%TYPE,
1274 -- anilk, 22-Apr-2003, Bug# 2829262
1275 p_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE )
1276 RETURN BOOLEAN IS
1277
1278 BEGIN
1279 DECLARE
1280 v_dummy VARCHAR2(1);
1281 CURSOR c_cv IS
1282 SELECT 'x'
1283 FROM IGS_EN_STDNT_PS_ATT sca,
1284 IGS_PS_VER cv
1285 WHERE sca.person_id = p_person_id AND
1286 sca.course_cd = p_course_cd AND
1287 cv.course_cd = sca.course_cd AND
1288 cv.version_number = sca.version_number AND
1289 cv.generic_course_ind = 'N';
1290 CURSOR c_sut IS
1291 SELECT 'x'
1292 FROM IGS_PS_STDNT_UNT_TRN sut
1293 WHERE sut.person_id = p_person_id AND
1294 sut.transfer_course_cd = p_course_cd AND
1295 -- anilk, 22-Apr-2003, Bug# 2829262
1296 sut.uoo_id = p_uoo_id;
1297 BEGIN
1298 OPEN c_cv;
1299 FETCH c_cv INTO v_dummy;
1300 IF c_cv%FOUND THEN
1301 CLOSE c_cv;
1302 RETURN TRUE;
1303 END IF;
1304 CLOSE c_cv;
1305 -- If the COURSE is a generic COURSE, determine if the UNIT has
1306 -- been transfered to another COURSE for the academic period.
1307 -- If so, the we do not want to display it.
1308 OPEN c_sut;
1309 FETCH c_sut INTO v_dummy;
1310 IF c_sut%FOUND THEN
1311 -- The UNIT exists in the generic COURSE and has been
1312 -- transfered to another COURSE. This uni is not to
1313 -- be displayed.
1314 CLOSE c_sut;
1315 RETURN FALSE;
1316 END IF;
1317 -- The UNIT within the generic COURSE has not been transfered,
1318 -- display this UNIT.
1319 CLOSE c_sut;
1320 RETURN TRUE;
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 IF c_cv%ISOPEN THEN
1324 CLOSE c_cv;
1325 END IF;
1326 IF c_sut%ISOPEN THEN
1327 CLOSE c_sut;
1328 END IF;
1329 RAISE;
1330 END;
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1334 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.asspl_val_dsp_unit');
1335 --IGS_GE_MSG_STACK.ADD;
1336 --APP_EXCEPTION.RAISE_EXCEPTION;
1337 END asspl_val_dsp_unit;
1338 -------------------------------------------- Main Program ----------------------
1339 BEGIN
1340 IF p_record_number = 1 THEN
1341 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index := 0;
1342 FOR v_sua_rec IN c_sua LOOP
1343 IF asspl_val_dsp_unit(
1344 v_sua_rec.person_id,
1345 p_course_cd,
1346 v_sua_rec.unit_cd,
1347 v_sua_rec.CAL_TYPE,
1348 v_sua_rec.ci_sequence_number,
1349 -- anilk, 22-Apr-2003, Bug# 2829262
1350 v_sua_rec.uoo_id ) THEN
1351 v_s_result_type := IGS_AS_GEN_003.ASSP_GET_SUA_OUTCOME(
1352 v_sua_rec.person_id,
1353 p_course_cd,
1354 v_sua_rec.unit_cd,
1355 v_sua_rec.CAL_TYPE,
1356 v_sua_rec.ci_sequence_number,
1357 v_sua_rec.unit_attempt_status,
1358 'Y',
1359 v_outcome_dt, -- output
1360 v_grading_schema_cd, -- output
1361 v_gs_version_number, -- output
1362 v_grade, -- output
1363 v_mark, -- output
1364 v_origin_course_cd,
1365 -- anilk, 22-Apr-2003, Bug# 2829262
1366 v_sua_rec.uoo_id,
1367 ---added by LKAKI---
1368 'N');
1369 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index := c_sua%ROWCOUNT;
1370 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1371 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_teach_alternate_code :=
1372 v_sua_rec.teach_alternate_code;
1373 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1374 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_unit_cd := v_sua_rec.unit_cd;
1375 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1376 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_title := v_sua_rec.v_unit_title;
1377 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1378 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_short_title :=
1379 v_sua_rec.v_unit_short_title;
1380 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1381 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_unit_level :=
1382 v_sua_rec.UNIT_LEVEL;
1383 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1384 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_cp_achievable :=
1385 v_sua_rec.v_cp_achievable;
1386 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1387 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_cp_achieved :=
1388 v_sua_rec.v_cp_achieved;
1389 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1390 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_mark := v_mark;
1391 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1392 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_grade := v_grade;
1393 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1394 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_grading_schema_cd :=
1395 v_grading_schema_cd;
1396 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1397 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_gs_version_number :=
1398 v_gs_version_number;
1399 IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1400 IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index).v_s_result := v_s_result_type;
1401 END IF;
1402 END LOOP;
1403 END IF;
1404 -- Create the output string based on the p_s_letter_parameter_type and the
1405 -- p_record_number.
1406 v_index := p_record_number;
1407 v_out_string := NULL;
1408 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_sua_dtl_index THEN
1409 IF p_s_letter_parameter_type = cst_trn_sua_ln THEN
1410 v_out_string := RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1411 v_index).v_teach_alternate_code,'-'),10)
1412 || fnd_global.local_chr(09) ||
1413 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1414 v_index).v_unit_cd,'-'),10)
1415 || fnd_global.local_chr(09) ||
1416 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1417 v_index).v_title,'-'),40);
1418 ELSIF p_s_letter_parameter_type = cst_trn_sua_cd THEN
1419 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1420 v_index).v_unit_cd,'-');
1421 ELSIF p_s_letter_parameter_type = cst_trn_sua_tl THEN
1422 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1423 v_index).v_short_title,'-');
1424 ELSIF p_s_letter_parameter_type = cst_trn_sua_pd THEN
1425 IF p_record_number = 1 THEN
1426 v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1427 v_index).v_teach_alternate_code,'-');
1428 ELSE
1429 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1430 v_index).v_teach_alternate_code,'-');
1431 END IF;
1432 ELSIF p_s_letter_parameter_type = cst_trn_sua_ul THEN
1433 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1434 v_index).v_unit_level,'-');
1435 ELSIF p_s_letter_parameter_type = cst_trn_sua_mk THEN
1436 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1437 v_index).v_mark,990),'-');
1438 ELSIF p_s_letter_parameter_type = cst_trn_sua_gd THEN
1439 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1440 v_index).v_grade,'-');
1441 ELSIF p_s_letter_parameter_type = cst_trn_sua_gs THEN
1442 v_out_string := NVL(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1443 v_index).v_grading_schema_cd,'-');
1444 ELSIF p_s_letter_parameter_type = cst_trn_ach_cp THEN
1445 v_out_string := RPAD(NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1446 v_index).v_cp_achieved,'990D99'),'-'),7);
1447 ELSIF p_s_letter_parameter_type = cst_trn_enr_cp THEN
1448 v_out_string := RPAD(NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1449 v_index).v_cp_achievable,'990D99'),'-'),7);
1450 ELSIF p_s_letter_parameter_type = cst_trn_sua_cp THEN
1451 IF IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(v_index).v_s_result <> 'PASS' THEN
1452 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1453 v_index).v_cp_achievable,'990D99'),'-');
1454 ELSE
1455 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1456 v_index).v_cp_achieved,'990D99'),'-');
1457 END IF;
1458 ELSIF p_s_letter_parameter_type = cst_trn_sua_gv THEN
1459 v_out_string := NVL(TO_CHAR(IGS_AS_PRC_TRANSCRPT.gt_sua_dtl_table(
1460 v_index).v_gs_version_number),'-');
1461 ELSE
1462 v_out_string := NULL;
1463 END IF;
1464 END IF;
1465 RETURN v_out_string;
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 IF c_sua%ISOPEN THEN
1469 CLOSE c_sua;
1470 END IF;
1471 RAISE;
1472 END;
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1476 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_sua_dtl');
1477 --IGS_GE_MSG_STACK.ADD;
1478 --APP_EXCEPTION.RAISE_EXCEPTION;
1479 END assp_get_trn_sua_dtl;
1480 --
1481 -- Retrieves adv standing UNIT level details for display on transcript
1482 FUNCTION assp_get_trn_asl_dtl(
1483 p_person_id IN NUMBER ,
1484 p_course_cd IN VARCHAR2 ,
1485 p_s_letter_parameter_type IN VARCHAR2 ,
1486 p_acad_cal_type IN VARCHAR2 ,
1487 p_acad_ci_sequence_number IN NUMBER ,
1488 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
1489 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
1490 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
1491 p_exclude_unit_category IN VARCHAR2 ,
1492 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
1493 p_record_number IN NUMBER )
1494 RETURN VARCHAR2 IS
1495
1496 BEGIN -- assp_get_trn_asl_dtl
1497 -- This module retrieves student advanced standing UNIT levels for use in the
1498 -- Correspondence Letter Facility for such correspondence as the Academic
1499 -- Transcript.
1500 -- The elements retrieved by this module are:
1501 -- UNIT Level,
1502 -- Credit Points Granted,
1503 -- Description
1504 DECLARE
1505 v_index BINARY_INTEGER;
1506 v_out_string VARCHAR2(500);
1507 v_dummy VARCHAR2(1);
1508 v_current_yr VARCHAR2(10);
1509 cst_trn_asl_ln CONSTANT VARCHAR2(20) := 'TRN_ASL_LN';
1510 cst_trn_asl_ul CONSTANT VARCHAR2(20) := 'TRN_ASL_UL';
1511 cst_trn_asl_cp CONSTANT VARCHAR2(20) := 'TRN_ASL_CP';
1512 cst_trn_asl_ds CONSTANT VARCHAR2(20) := 'TRN_ASL_DS';
1513 CURSOR c_asule IS
1514 SELECT asule.person_id,
1515 asule.UNIT_LEVEL,
1516 asule.credit_points
1517 FROM IGS_AV_STND_UNIT_LVL asule
1518 WHERE asule.person_id = p_person_id AND
1519 asule.as_course_cd = p_course_cd AND
1520 asule.s_adv_stnd_granting_status = 'GRANTED'
1521 ORDER BY
1522 asule.UNIT_LEVEL;
1523 CURSOR c_current_yr IS
1524 SELECT SUBSTR(IGS_GE_DATE.IGSCHAR(start_dt),1,4)
1525 FROM IGS_CA_INST
1526 WHERE CAL_TYPE = p_acad_cal_type AND
1527 sequence_number = p_acad_ci_sequence_number;
1528 CURSOR c_check_yr (cp_year IGS_CA_INST.ALTERNATE_CODE%TYPE) IS
1529 SELECT 'x'
1530 FROM dual
1531 WHERE cp_year <= IGS_AS_GEN_005.ASSP_VAL_SCA_COMM (
1532 p_person_id,
1533 p_course_cd,
1534 p_include_fail_grade_ind,
1535 p_enrolled_units_ind,
1536 p_exclude_research_units_ind,
1537 p_exclude_unit_category,
1538 p_include_related_crs_ind);
1539 BEGIN
1540 -- Determine if advanced standing has already been displayed in an earlier
1541 -- academic period for the COURSE. (Unlike UNIT attempt details, advanced
1542 -- standing is only shown under the first occurrence of a COURSE regardless
1543 -- of when it was actually granted).
1544 OPEN c_current_yr;
1545 FETCH c_current_yr INTO v_current_yr;
1546 IF c_current_yr%NOTFOUND THEN
1547 CLOSE c_current_yr;
1548 RETURN NULL;
1549 END IF;
1550 CLOSE c_current_yr;
1551 OPEN c_check_yr (v_current_yr);
1552 FETCH c_check_yr INTO v_dummy;
1553 IF c_check_yr%NOTFOUND THEN
1554 CLOSE c_check_yr;
1555 RETURN NULL;
1556 END IF;
1557 CLOSE c_check_yr;
1558 -- Determine if this is the first time the procedure has been called for
1559 -- the PERSON (p_record_number = 1). If so, then populate the PL/SQL table
1560 -- that will be used to retrieve the rest of the records returned from the
1561 -- query.
1562 IF p_record_number = 1 THEN
1563 -- Initialise the counter for the PL/SQL table.
1564 IGS_AS_PRC_TRANSCRPT.gv_asule_dtl_index := 0;
1565 FOR v_asule_rec IN c_asule LOOP
1566 -- Store the UNIT in the PL/SQL table.
1567 -- Increment the counter.
1568 IGS_AS_PRC_TRANSCRPT.gv_asule_dtl_index := c_asule%ROWCOUNT;
1569 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(
1570 IGS_AS_PRC_TRANSCRPT.gv_asule_dtl_index).v_unit_level
1571 := v_asule_rec.UNIT_LEVEL;
1572 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(
1573 IGS_AS_PRC_TRANSCRPT.gv_asule_dtl_index).v_cp_granted
1574 := v_asule_rec.credit_points;
1575 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(
1576 IGS_AS_PRC_TRANSCRPT.gv_asule_dtl_index).v_description
1577 := 'UNSPECIFIED CREDIT';
1578 END LOOP;
1579 END IF;
1580 -- Create the output string based on the p_s_letter_paramater_type and the
1581 -- p_record_number.
1582 v_index := p_record_number;
1583 v_out_string := NULL;
1584 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_asule_dtl_index THEN
1585 IF p_s_letter_parameter_type = cst_trn_asl_ln THEN
1586 v_out_string := RPAD(NVL(
1587 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(v_index).v_description,
1588 '-'),50) || fnd_global.local_chr(09) ||
1589 RPAD(NVL(TO_CHAR(
1590 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(v_index).v_cp_granted
1591 ,'990D99'),'-'),7) || fnd_global.local_chr(09) ||
1592 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(v_index).v_unit_level,
1593 '-'),1);
1594 ELSIF p_s_letter_parameter_type = cst_trn_asl_ds THEN
1595 v_out_string := NVL(
1596 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(v_index).v_description,'-');
1597 ELSIF p_s_letter_parameter_type = cst_trn_asl_ul THEN
1598 v_out_string := NVL(
1599 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(v_index).v_unit_level,'-');
1600 ELSIF p_s_letter_parameter_type = cst_trn_asl_cp THEN
1601 v_out_string := NVL(TO_CHAR(
1602 IGS_AS_PRC_TRANSCRPT.gt_asule_dtl_table(v_index).v_cp_granted
1603 ,'990D99'),'-');
1604 ELSE
1605 v_out_string := NULL;
1606 END IF;
1607 END IF;
1608 RETURN v_out_string;
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611 IF c_asule%ISOPEN THEN
1612 CLOSE c_asule;
1613 END IF;
1614 IF c_current_yr%ISOPEN THEN
1615 CLOSE c_current_yr;
1616 END IF;
1617 IF c_check_yr%ISOPEN THEN
1618 CLOSE c_check_yr;
1619 END IF;
1620 RAISE;
1621 END;
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1625 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_asl_dtl');
1626 IGS_GE_MSG_STACK.ADD;
1627 --APP_EXCEPTION.RAISE_EXCEPTION;
1628 END assp_get_trn_asl_dtl;
1629 --
1630 -- Retrieves advanced standing UNIT details for display on transcript
1631 FUNCTION assp_get_trn_asu_dtl(
1632 p_person_id IN NUMBER ,
1633 p_course_cd IN VARCHAR2 ,
1634 p_s_letter_parameter_type IN VARCHAR2 ,
1635 p_acad_cal_type IN VARCHAR2 ,
1636 p_acad_ci_sequence_number IN NUMBER ,
1637 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
1638 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
1639 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
1640 p_exclude_unit_category IN VARCHAR2 ,
1641 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
1642 p_record_number IN NUMBER )
1643 RETURN VARCHAR2 IS
1644
1645 BEGIN -- assp_get_trn_asu_dtl
1646 -- This module retrieves student advanced standing UNIT details for use in the
1647 -- Correspondence Letter Facility for such correspondence as the Academic
1648 -- Transcript.
1649 -- The elements retrieved by this module are:
1650 -- UNIT Code,
1651 -- UNIT TITLE,
1652 -- Credit Points Achievable,
1653 -- UNIT Level
1654 DECLARE
1655 v_index BINARY_INTEGER;
1656 v_out_string VARCHAR2(500);
1657 v_dummy VARCHAR2(1);
1658 l_advgrant BOOLEAN; --added as part of academic records maitenance DLD
1659 l_credits NUMBER; --added as part of academic records maitenance DLD
1660 l_s_adv_atnd_granting_status igs_av_stnd_unit_all.s_adv_stnd_granting_status%TYPE; --added as part of academic records maitenance DLD
1661 l_message VARCHAR2(2000); --added as part of academic records maitenance DLD
1662 v_current_yr VARCHAR2(10);
1663 cst_trn_asu_ln CONSTANT VARCHAR2(20) := 'TRN_ASU';
1664 cst_trn_asu_cd CONSTANT VARCHAR2(20) := 'TRN_ASU_CD';
1665 cst_trn_asu_tl CONSTANT VARCHAR2(20) := 'TRN_ASU_TL';
1666 cst_trn_asu_ul CONSTANT VARCHAR2(20) := 'TRN_ASU_UL';
1667 cst_trn_asu_cp CONSTANT VARCHAR2(20) := 'TRN_ASU_CP';
1668
1669 CURSOR c_asu IS
1670 SELECT asu.person_id,as_course_cd,as_version_number,
1671 asu.unit_cd,asu.version_number,
1672 uv.short_title,
1673 uv.TITLE,
1674 uv.UNIT_LEVEL
1675 FROM IGS_AV_STND_UNIT asu,
1676 IGS_PS_UNIT_VER uv
1677 WHERE asu.person_id = p_person_id AND
1678 asu.as_course_cd = p_course_cd AND
1679 asu.s_adv_stnd_granting_status = 'GRANTED' AND
1680 asu.s_adv_stnd_recognition_type = 'CREDIT' AND
1681 (igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,
1682 asu.unit_cd,asu.version_number,'GRANTED',NULL) ='TRUE') AND
1683 asu.unit_cd = uv.unit_cd AND
1684 asu.version_number = uv.version_number
1685 GROUP BY person_id,as_course_cd,as_version_number,asu.unit_cd,asu.version_number,uv.short_title,
1686 uv.title,uv.unit_level
1687 ORDER BY
1688 asu.unit_cd,
1689 asu.version_number;
1690 CURSOR c_current_yr IS
1691 SELECT SUBSTR(IGS_GE_DATE.IGSCHAR(start_dt),1,4)
1692 FROM IGS_CA_INST
1693 WHERE CAL_TYPE = p_acad_cal_type AND
1694 sequence_number = p_acad_ci_sequence_number;
1695 CURSOR c_check_yr (cp_year IGS_CA_INST.ALTERNATE_CODE%TYPE) IS
1696 SELECT 'x'
1697 FROM dual
1698 WHERE cp_year <= IGS_AS_GEN_005.ASSP_VAL_SCA_COMM (
1699 p_person_id,
1700 p_course_cd,
1701 p_include_fail_grade_ind,
1702 p_enrolled_units_ind,
1703 p_exclude_research_units_ind,
1704 p_exclude_unit_category,
1705 p_include_related_crs_ind);
1706 BEGIN
1707 -- Determine if advanced standing has already been displayed in an earlier
1708 -- academic
1709 -- period for the COURSE. (Unlike UNIT attempt details, advanced standing is
1710 -- only shown under the first occurrence of a COURSE regardless of when it
1711 -- was actually granted).
1712 OPEN c_current_yr;
1713 FETCH c_current_yr INTO v_current_yr;
1714 IF c_current_yr%NOTFOUND THEN
1715 CLOSE c_current_yr;
1716 RETURN NULL;
1717 END IF;
1718 CLOSE c_current_yr;
1719 OPEN c_check_yr (v_current_yr);
1720 FETCH c_check_yr INTO v_dummy;
1721 IF c_check_yr%NOTFOUND THEN
1722 CLOSE c_check_yr;
1723 RETURN NULL;
1724 END IF;
1725 CLOSE c_check_yr;
1726 -- Determine if this is the first time the procedure has been called for
1727 -- the PERSON (p_record_number = 1). If so, then populate the PL/SQL table
1728 -- that will be used to retrieve the rest of the records returned from the
1729 -- query.
1730 IF p_record_number = 1 THEN
1731 -- Initialise the counter for the PL/SQL table.
1732 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index := 0;
1733 FOR v_asu_rec IN c_asu LOOP
1734 -- Store the UNIT in the PL/SQL table.
1735 -- Increment the counter.
1736
1737 l_advgrant := igs_av_val_asu.adv_Credit_pts(p_person_id,p_course_cd,v_Asu_rec.as_version_number,
1738 v_asu_rec.unit_cd,v_asu_rec.version_number,
1739 'GRANTED',NULL,l_credits,l_s_adv_atnd_granting_status,l_message);-- academic records maint. DLD
1740 IF NOT l_advgrant THEN
1741 l_credits := 0;
1742 END IF;
1743
1744 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index := c_asu%ROWCOUNT;
1745 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(
1746 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index).v_unit_cd
1747 := v_asu_rec.unit_cd;
1748 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(
1749 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index).v_short_title
1750 := v_asu_rec.short_title;
1751 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(
1752 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index).v_title
1753 := v_asu_rec.TITLE;
1754 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(
1755 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index).v_cp_achievable
1756 := l_credits; -- academic records maintenance DLD
1757 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(
1758 IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index).v_unit_level
1759 := v_asu_rec.UNIT_LEVEL;
1760 END LOOP;
1761 END IF;
1762 -- Create the output string based on the p_s_letter_paramater_type and the
1763 -- p_record_number.
1764 v_index := p_record_number;
1765 v_out_string := NULL;
1766 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_asu_dtl_index THEN
1767 IF p_s_letter_parameter_type = cst_trn_asu_ln THEN
1768 v_out_string := RPAD(NVL(
1769 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_unit_cd,
1770 '-'),10) || fnd_global.local_chr(09) ||
1771 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_title,
1772 '-'),40) || fnd_global.local_chr(09) ||
1773 RPAD(NVL(TO_CHAR(
1774 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_cp_achievable
1775 ,'990D99'),'-'),7) || fnd_global.local_chr(09) ||
1776 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_unit_level,
1777 '-'),1);
1778 ELSIF p_s_letter_parameter_type = cst_trn_asu_cd THEN
1779 v_out_string := NVL(
1780 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_unit_cd,'-');
1781 ELSIF p_s_letter_parameter_type = cst_trn_asu_tl THEN
1782 v_out_string := NVL(
1783 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_short_title,'-');
1784 ELSIF p_s_letter_parameter_type = cst_trn_asu_ul THEN
1785 v_out_string := NVL(
1786 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_unit_level,'-');
1787 ELSIF p_s_letter_parameter_type = cst_trn_asu_cp THEN
1788 v_out_string := NVL(TO_CHAR(
1789 IGS_AS_PRC_TRANSCRPT.gt_asu_dtl_table(v_index).v_cp_achievable
1790 ,'990D99'),'-');
1791 ELSE
1792 v_out_string := NULL;
1793 END IF;
1794 END IF;
1795 RETURN v_out_string;
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 IF c_asu%ISOPEN THEN
1799 CLOSE c_asu;
1800 END IF;
1801 IF c_current_yr%ISOPEN THEN
1802 CLOSE c_current_yr;
1803 END IF;
1804 IF c_check_yr%ISOPEN THEN
1805 CLOSE c_check_yr;
1806 END IF;
1807 RAISE;
1808 END;
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1812 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_asu_dtl');
1813 IGS_GE_MSG_STACK.ADD;
1814 --APP_EXCEPTION.RAISE_EXCEPTION;
1815 END assp_get_trn_asu_dtl;
1816 --
1817 -- Retrieves basic advanced standing details for display on transcript
1818 FUNCTION assp_get_trn_adv_dtl(
1819 p_person_id IN NUMBER ,
1820 p_course_cd IN VARCHAR2 ,
1821 p_s_letter_parameter_type IN VARCHAR2 ,
1822 p_acad_cal_type IN VARCHAR2 ,
1823 p_acad_ci_sequence_number IN NUMBER ,
1824 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
1825 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
1826 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
1827 p_exclude_unit_category IN VARCHAR2 ,
1828 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
1829 p_record_number IN NUMBER )
1830 RETURN VARCHAR2 IS
1831
1832 BEGIN -- assp_get_trn_adv_dtl
1833 -- This function determines if the PERSON has been granted any advanced
1834 -- standing for the nominated COURSE code in the nominated academic period.
1835 -- It returns a descriptive string for use in the Correspondence Letter
1836 -- Facility for such correspondence as the Academic Transcript.
1837 --
1838 -- NOTE : This function will be extended in the future to include the basis on
1839 -- which the advanced standing has been granted. This is not possible
1840 -- until the application component of advanced standing is built.
1841 --
1842 -- CHANGE : As part of academic records maintenance DLD the existing credit percentage
1843 -- logic in the c_adv cursor is modified to look into igs_av_val_Asu.granted_Adv_standing
1844 DECLARE
1845 v_out_string VARCHAR2(255);
1846 v_index BINARY_INTEGER;
1847 v_dummy VARCHAR2(1);
1848 v_current_yr VARCHAR2(10);
1849 cst_trn_adv_ln CONSTANT VARCHAR2(20) := 'TRN_ADV';
1850 CURSOR c_adv IS
1851 SELECT adv.total_exmptn_granted
1852 FROM IGS_AV_ADV_STANDING adv
1853 WHERE adv.person_id = p_person_id AND
1854 adv.course_cd = p_course_cd AND
1855 adv.total_exmptn_granted > 0 AND
1856 (EXISTS (SELECT 'x'
1857 FROM IGS_AV_STND_UNIT asu
1858 WHERE asu.person_id = adv.person_id AND
1859 asu.as_course_cd = adv.course_cd AND
1860 asu.s_adv_stnd_granting_status = 'GRANTED' AND
1861 asu.s_adv_stnd_recognition_type = 'CREDIT' AND
1862 (igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,
1863 unit_cd,version_number,'GRANTED',NULL) ='TRUE')) OR
1864 EXISTS (SELECT 'x'
1865 FROM IGS_AV_STND_UNIT_LVL asule
1866 WHERE asule.person_id = adv.person_id AND
1867 asule.as_course_cd = adv.course_cd AND
1868 asule.s_adv_stnd_granting_status = 'GRANTED'));
1869 CURSOR c_current_yr IS
1870 SELECT SUBSTR(IGS_GE_DATE.IGSCHAR(start_dt),1,4)
1871 FROM IGS_CA_INST
1872 WHERE CAL_TYPE = p_acad_cal_type AND
1873 sequence_number = p_acad_ci_sequence_number;
1874 CURSOR c_check_yr (cp_year IGS_CA_INST.ALTERNATE_CODE%TYPE) IS
1875 SELECT 'x'
1876 FROM dual
1877 WHERE cp_year <= IGS_AS_GEN_005.ASSP_VAL_SCA_COMM (
1878 p_person_id,
1879 p_course_cd,
1880 p_include_fail_grade_ind,
1881 p_enrolled_units_ind,
1882 p_exclude_research_units_ind,
1883 p_exclude_unit_category,
1884 p_include_related_crs_ind);
1885 BEGIN
1886 -- Validate parameters
1887 IF p_person_id IS NULL OR
1888 p_course_cd IS NULL OR
1889 p_acad_cal_type IS NULL OR
1890 p_acad_ci_sequence_number IS NULL OR
1891 p_s_letter_parameter_type IS NULL OR
1892 p_record_number IS NULL THEN
1893 RETURN NULL;
1894 END IF;
1895 -- Determine if advanced standing has already been displayed in an earlier
1896 -- academic period for the COURSE. (Unlike UNIT details, advanced standing
1897 -- is only shown under the first occurrence of a COURSE regardless of when
1898 -- it was actually granted).
1899 OPEN c_current_yr;
1900 FETCH c_current_yr INTO v_current_yr;
1901 IF c_current_yr%NOTFOUND THEN
1902 CLOSE c_current_yr;
1903 RETURN NULL;
1904 END IF;
1905 CLOSE c_current_yr;
1906 OPEN c_check_yr (v_current_yr);
1907 FETCH c_check_yr INTO v_dummy;
1908 IF c_check_yr%NOTFOUND THEN
1909 CLOSE c_check_yr;
1910 RETURN NULL;
1911 END IF;
1912 CLOSE c_check_yr;
1913 -- Determine if this is the first time the procedure has been called for
1914 -- the PERSON (p_record_number = 1). If so, then populate the PL/SQL table
1915 -- that will be used to retrieve the rest of the records returned from the
1916 -- query.
1917 IF p_record_number = 1 THEN
1918 -- Initialise the counter for the PL/SQL table.
1919 IGS_AS_PRC_TRANSCRPT.gv_adv_dtl_index := 0;
1920 FOR v_adv_rec IN c_adv LOOP
1921 IGS_AS_PRC_TRANSCRPT.gv_adv_dtl_index := c_adv%ROWCOUNT;
1922 IGS_AS_PRC_TRANSCRPT.gt_adv_dtl_table(
1923 IGS_AS_PRC_TRANSCRPT.gv_adv_dtl_index).v_title :=
1924 'ADVANCED STANDING GRANTED :';
1925 END LOOP;
1926 END IF;
1927 -- Create the output string based on the p_s_letter_parameter_type
1928 -- and the p_record_number.
1929 v_index := p_record_number;
1930 v_out_string := NULL;
1931 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_adv_dtl_index THEN
1932 IF p_s_letter_parameter_type = cst_trn_adv_ln THEN
1933 IF p_record_number = 1 THEN
1934 v_out_string := fnd_global.local_chr(10) || RPAD(NVL(
1935 IGS_AS_PRC_TRANSCRPT.gt_adv_dtl_table(v_index).v_title,'-'),50) || fnd_global.local_chr(10);
1936 ELSE
1937 v_out_string := RPAD(NVL(
1938 IGS_AS_PRC_TRANSCRPT.gt_adv_dtl_table(v_index).v_title,'-'),50) || fnd_global.local_chr(10);
1939 END IF;
1940 END IF;
1941 END IF;
1942 RETURN v_out_string;
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 IF c_adv%ISOPEN THEN
1946 CLOSE c_adv;
1947 END IF;
1948 IF c_current_yr%ISOPEN THEN
1949 CLOSE c_current_yr;
1950 END IF;
1951 IF c_check_yr%ISOPEN THEN
1952 CLOSE c_check_yr;
1953 END IF;
1954 RAISE;
1955 END;
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1959 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_adv_dtl');
1960 IGS_GE_MSG_STACK.ADD;
1961 --APP_EXCEPTION.RAISE_EXCEPTION;
1962 END assp_get_trn_adv_dtl;
1963 --
1964 -- Retrieves basic COURSE details for display on transcript
1965 FUNCTION assp_get_trn_sca_dtl(
1966 p_person_id IN NUMBER ,
1967 p_course_cd IN VARCHAR2 ,
1968 p_s_letter_parameter_type IN VARCHAR2 ,
1969 p_order_by IN VARCHAR2 DEFAULT 'YEAR',
1970 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
1971 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
1972 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
1973 p_exclude_unit_category IN VARCHAR2 ,
1974 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
1975 p_record_number IN NUMBER ,
1976 p_extra_context OUT NOCOPY VARCHAR2 )
1977 RETURN VARCHAR2 IS
1978
1979 BEGIN -- assp_get_trn_sca_dtl
1980 -- This module retrieves COURSE details for use in the Correspondence Letter
1981 -- Facility for such correspondence as the Academic Transcript.
1982 -- The elements retrieved by this module are:
1983 -- Academic Year,
1984 -- COURSE Code,
1985 -- COURSE TITLE,
1986 -- COURSE Commencement Date,
1987 -- COURSE Attendance Type,
1988 -- COURSE IGS_AD_LOCATION
1989 -- COURSE Stage.
1990 DECLARE
1991 v_index BINARY_INTEGER;
1992 v_out_string VARCHAR2(500);
1993 v_orderby VARCHAR2(10);
1994 cst_trn_course CONSTANT VARCHAR2(20) := 'TRN_COURSE';
1995 cst_trn_crs_cd CONSTANT VARCHAR2(20) := 'TRN_CRS_CD';
1996 cst_trn_crs_dt CONSTANT VARCHAR2(20) := 'TRN_CRS_DT';
1997 cst_trn_crs_at CONSTANT VARCHAR2(20) := 'TRN_CRS_AT';
1998 cst_trn_crs_lc CONSTANT VARCHAR2(20) := 'TRN_CRS_LC';
1999 cst_trn_crs_t CONSTANT VARCHAR2(20) := 'TRN_CRS_T';
2000 CURSOR c_sca IS
2001 SELECT sca.person_id,
2002 suav.acad_alternate_code,
2003 sca.course_cd,
2004 cv.TITLE,
2005 ci.CAL_TYPE,
2006 ci.sequence_number,
2007 ci.start_dt,
2008 cop.attendance_type,
2009 cop.location_cd,
2010 sca.commencement_dt,
2011 cv.generic_course_ind,
2012 -- anilk, 22-Apr-2003, Bug# 2829262
2013 suav.uoo_id
2014 FROM IGS_EN_STDNT_PS_ATT sca,
2015 IGS_EN_SUA_V suav,
2016 IGS_PS_VER cv,
2017 IGS_PS_OFR_PAT cop,
2018 IGS_CA_INST ci
2019 WHERE sca.person_id = p_person_id AND
2020 sca.person_id = suav.person_id AND
2021 sca.course_cd = suav.course_cd AND
2022 (sca.course_cd = NVL(p_course_cd, suav.course_cd) OR
2023 (p_course_cd IS NULL OR
2024 (p_course_cd IS NOT NULL AND
2025 p_include_related_crs_ind = 'N' AND
2026 sca.course_cd = p_course_cd) OR
2027 (p_course_cd IS NOT NULL AND
2028 p_include_related_crs_ind = 'Y' AND
2029 sca.course_cd IN (SELECT cgm.course_cd
2030 FROM IGS_PS_GRP_MBR cgm,
2031 IGS_PS_GRP cg,
2032 IGS_PS_GRP_TYPE cgt
2033 WHERE cgm.course_group_cd = cg.course_group_cd
2034 AND cg.course_group_type = cgt.course_group_type
2035 AND cgt.s_course_group_type = 'RELATED'
2036 AND p_course_cd IN (SELECT cgm1.course_cd
2037 FROM IGS_PS_GRP_MBR cgm1,
2038 IGS_PS_GRP cg1,
2039 IGS_PS_GRP_TYPE cgt1
2040 WHERE cgm1.course_group_cd = cg1.course_group_cd
2041 AND cg1.course_group_type = cgt1.course_group_type
2042 AND cgt1.s_course_group_type = 'RELATED'))))) AND
2043 EXISTS( SELECT 'X'
2044 FROM IGS_EN_SU_ATTEMPT sua
2045 WHERE sua.person_id = suav.person_id AND
2046 sua.course_cd = suav.course_cd AND
2047 -- anilk, 22-Apr-2003, Bug# 2829262
2048 sua.uoo_id = suav.uoo_id AND
2049 IGS_AS_GEN_001.ASSP_VAL_SUA_DISPLAY(
2050 sua.person_id,
2051 sua.course_cd,
2052 sca.version_number,
2053 sua.unit_cd,
2054 sua.CAL_TYPE,
2055 sua.ci_sequence_number,
2056 sua.unit_attempt_status,
2057 sua.administrative_unit_status,
2058 'Y',
2059 p_include_fail_grade_ind,
2060 p_enrolled_units_ind,
2061 p_exclude_research_units_ind,
2062 p_exclude_unit_category,
2063 -- anilk, 22-Apr-2003, Bug# 2829262
2064 sua.uoo_id ) = 'Y') AND
2065 cv.course_cd = sca.course_cd AND
2066 cv.version_number = sca.version_number AND
2067 sca.coo_id = cop.coo_id AND
2068 sca.location_cd = cop.location_cd AND
2069 sca.attendance_mode = cop.attendance_mode AND
2070 sca.attendance_type = cop.attendance_type AND
2071 cop.CAL_TYPE = ci.CAL_TYPE AND
2072 cop.ci_sequence_number = ci.sequence_number AND
2073 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2074 cop.CAL_TYPE,
2075 cop.ci_sequence_number,
2076 suav.CAL_TYPE,
2077 suav.ci_sequence_number,
2078 'Y') = 'Y'
2079 GROUP BY
2080 sca.person_id,
2081 suav.acad_alternate_code,
2082 sca.course_cd,
2083 cv.TITLE,
2084 ci.CAL_TYPE,
2085 ci.sequence_number,
2086 ci.start_dt,
2087 cop.attendance_type,
2088 cop.location_cd,
2089 sca.commencement_dt,
2090 cv.generic_course_ind
2091 ORDER BY
2092 sca.course_cd,
2093 ci.start_dt;
2094 CURSOR c_sca2 IS
2095 SELECT sca.person_id,
2096 suav.acad_alternate_code,
2097 sca.course_cd,
2098 cv.TITLE,
2099 ci.CAL_TYPE,
2100 ci.sequence_number,
2101 ci.start_dt,
2102 cop.attendance_type,
2103 cop.location_cd,
2104 sca.commencement_dt,
2105 cv.generic_course_ind,
2106 -- anilk, 22-Apr-2003, Bug# 2829262
2107 suav.uoo_id
2108 FROM IGS_EN_STDNT_PS_ATT sca,
2109 IGS_EN_SUA_V suav,
2110 IGS_PS_VER cv,
2111 IGS_PS_OFR_PAT cop,
2112 IGS_CA_INST ci
2113 WHERE sca.person_id = p_person_id AND
2114 sca.person_id = suav.person_id AND
2115 -- sca.course_cd = NVL(p_course_cd, suav.course_cd) AND
2116 sca.course_cd = suav.course_cd AND
2117 (sca.course_cd = NVL(p_course_cd, suav.course_cd) OR
2118 (p_course_cd IS NULL OR
2119 (p_course_cd IS NOT NULL AND
2120 p_include_related_crs_ind = 'N' AND
2121 sca.course_cd = p_course_cd) OR
2122 (p_course_cd IS NOT NULL AND
2123 p_include_related_crs_ind = 'Y' AND
2124 sca.course_cd IN (SELECT cgm.course_cd
2125 FROM IGS_PS_GRP_MBR cgm,
2126 IGS_PS_GRP cg,
2127 IGS_PS_GRP_TYPE cgt
2128 WHERE cgm.course_group_cd = cg.course_group_cd
2129 AND cg.course_group_type = cgt.course_group_type
2130 AND cgt.s_course_group_type = 'RELATED'
2131 AND p_course_cd IN (SELECT cgm1.course_cd
2132 FROM IGS_PS_GRP_MBR cgm1,
2133 IGS_PS_GRP cg1,
2134 IGS_PS_GRP_TYPE cgt1
2135 WHERE cgm1.course_group_cd = cg1.course_group_cd
2136 AND cg1.course_group_type = cgt1.course_group_type
2137 AND cgt1.s_course_group_type = 'RELATED'))))) AND
2138 EXISTS( SELECT 'X'
2139 FROM IGS_EN_SU_ATTEMPT sua
2140 WHERE sua.person_id = suav.person_id AND
2141 sua.course_cd = suav.course_cd AND
2142 -- anilk, 22-Apr-2003, Bug# 2829262
2143 sua.uoo_id = suav.uoo_id AND
2144 IGS_AS_GEN_001.ASSP_VAL_SUA_DISPLAY(
2145 sua.person_id,
2146 sua.course_cd,
2147 sca.version_number,
2148 sua.unit_cd,
2149 sua.CAL_TYPE,
2150 sua.ci_sequence_number,
2151 sua.unit_attempt_status,
2152 sua.administrative_unit_status,
2153 'Y', -- finalised indicator
2154 p_include_fail_grade_ind,
2155 p_enrolled_units_ind,
2156 p_exclude_research_units_ind,
2157 p_exclude_unit_category,
2158 -- anilk, 22-Apr-2003, Bug# 2829262
2159 sua.uoo_id ) = 'Y') AND
2160 cv.course_cd = sca.course_cd AND
2161 cv.version_number = sca.version_number AND
2162 sca.coo_id = cop.coo_id AND
2163 sca.location_cd = cop.location_cd AND
2164 sca.attendance_mode = cop.attendance_mode AND
2165 sca.attendance_type = cop.attendance_type AND
2166 cop.CAL_TYPE = ci.CAL_TYPE AND
2167 cop.ci_sequence_number = ci.sequence_number AND
2168 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
2169 cop.CAL_TYPE,
2170 cop.ci_sequence_number,
2171 suav.CAL_TYPE,
2172 suav.ci_sequence_number,
2173 'Y') = 'Y'
2174 GROUP BY
2175 sca.person_id,
2176 suav.acad_alternate_code,
2177 sca.course_cd,
2178 cv.TITLE,
2179 ci.CAL_TYPE,
2180 ci.sequence_number,
2181 ci.start_dt,
2182 cop.attendance_type,
2183 cop.location_cd,
2184 sca.commencement_dt,
2185 cv.generic_course_ind
2186 ORDER BY
2187 ci.start_dt,
2188 sca.course_cd;
2189 ---------------------------------------asspl_val_dsp_g_crs----------------------
2190 FUNCTION asspl_val_dsp_g_crs(
2191 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
2192 p_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
2193 p_generic_course_ind IGS_PS_VER.generic_course_ind%TYPE,
2194 p_acad_cal_type IGS_EN_SU_ATTEMPT.CAL_TYPE%TYPE,
2195 p_acad_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE)
2196 RETURN BOOLEAN IS
2197
2198 BEGIN -- asspl_val_dsp_g_crs
2199 -- local function to determine if a generic COURSE is displayed.
2200 DECLARE
2201 v_dummy VARCHAR2(1);
2202 CURSOR c_sua IS
2203 SELECT 'x'
2204 FROM IGS_EN_SU_ATTEMPT sua
2205 WHERE sua.person_id = p_person_id AND
2206 sua.course_cd = p_course_cd AND
2207 (sua.unit_attempt_status IN ( 'ENROLLED',
2208 'COMPLETED',
2209 'DUPLICATE') OR
2210 (sua.unit_attempt_status = 'DISCONTIN' AND
2211 IGS_AS_GEN_003.ASSP_GET_TRN_SUA_OUT(
2212 p_person_id,
2213 p_course_cd,
2214 sua.unit_cd,
2215 sua.CAL_TYPE,
2216 sua.ci_sequence_number,
2217 sua.unit_attempt_status,
2218 'Y',
2219 -- anilk, 22-Apr-2003, Bug# 2829262
2220 sua.uoo_id) = 'FAIL')) AND
2221 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
2222 p_acad_cal_type,
2223 p_acad_ci_sequence_number,
2224 sua.CAL_TYPE,
2225 sua.ci_sequence_number,
2226 'Y') = 'Y' AND
2227 NOT EXISTS ( SELECT 'x'
2228 FROM IGS_PS_STDNT_UNT_TRN sut
2229 WHERE sut.person_id = sua.person_id AND
2230 sut.transfer_course_cd = sua.course_cd AND
2231 -- anilk, 22-Apr-2003, Bug# 2829262
2232 sut.uoo_id = sua.uoo_id);
2233 BEGIN
2234 IF p_generic_course_ind = 'N' THEN
2235 RETURN TRUE;
2236 END IF;
2237 -- If the COURSE is a generic COURSE, determine if the COURSE contains
2238 -- any units that have not been transferred to another COURSE for the
2239 -- academic period.
2240 OPEN c_sua;
2241 FETCH c_sua INTO v_dummy;
2242 IF c_sua%NOTFOUND THEN
2243 CLOSE c_sua;
2244 RETURN FALSE;
2245 END IF;
2246 CLOSE c_sua;
2247 RETURN TRUE;
2248 EXCEPTION
2249 WHEN OTHERS THEN
2250 IF c_sua%ISOPEN THEN
2251 CLOSE c_sua;
2252 END IF;
2253 RAISE;
2254 END;
2255 EXCEPTION
2256 WHEN OTHERS THEN
2257 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2258 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.asspl_val_dsp_g_crs');
2259 IGS_GE_MSG_STACK.ADD;
2260 --APP_EXCEPTION.RAISE_EXCEPTION;
2261 END asspl_val_dsp_g_crs;
2262 --------------------------------------------------------------------------------
2263 ---------------------------------------------Main Program----------------------
2264 BEGIN
2265 -- Determine if this is the first time the procedure has been called for
2266 -- the PERSON (p_record_number = 1). If so, then populate the PL/SQL table
2267 -- that will be used to retrieve the rest of the records returned from the
2268 -- query.
2269 IF p_record_number = 1 THEN
2270 -- Initialise the counter for the PL/SQL table.
2271 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index := 0;
2272 -- Determine if the order by parameter is specified as to which
2273 -- select statement to use.
2274 IF p_order_by = 'COURSE' THEN
2275 FOR v_sca_rec IN c_sca LOOP
2276 -- Determine if the COURSE is a generic COURSE. If all the
2277 -- units within this COURSE for the academic period have been
2278 -- transferred to another COURSE, then do not show this COURSE.
2279 IF asspl_val_dsp_g_crs(
2280 v_sca_rec.person_id,
2281 v_sca_rec.course_cd,
2282 v_sca_rec.generic_course_ind,
2283 v_sca_rec.CAL_TYPE,
2284 v_sca_rec.sequence_number) THEN
2285 -- Store the COURSE in the PL/SQL table.
2286 -- Increment the counter.
2287 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index := c_sca%ROWCOUNT;
2288 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2289 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_alternate_code :=
2290 v_sca_rec.acad_alternate_code;
2291 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2292 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_course_cd :=
2293 v_sca_rec.course_cd;
2294 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2295 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_title := v_sca_rec.TITLE;
2296 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2297 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_cal_type :=
2298 v_sca_rec.CAL_TYPE;
2299 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2300 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_ci_sequence_number :=
2301 v_sca_rec.sequence_number;
2302 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2303 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_start_dt :=
2304 v_sca_rec.start_dt;
2305 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2306 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_attendance_type :=
2307 v_sca_rec.attendance_type;
2308 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2309 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_location_cd :=
2310 v_sca_rec.location_cd;
2311 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2312 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_commencement_dt :=
2313 v_sca_rec.commencement_dt;
2314 END IF;
2315 END LOOP;
2316 ELSE
2317 --ELSIF p_order_by <> 'COURSE' THEN
2318 -- order by academic period
2319 FOR v_sca_rec IN c_sca2 LOOP
2320 -- Determine if the COURSE is a generic COURSE. If all the
2321 -- units within this COURSE for the academic period have been
2322 -- transferred to another COURSE, then do not show this COURSE.
2323 IF asspl_val_dsp_g_crs(
2324 v_sca_rec.person_id,
2325 v_sca_rec.course_cd,
2326 v_sca_rec.generic_course_ind,
2327 v_sca_rec.CAL_TYPE,
2328 v_sca_rec.sequence_number) THEN
2329 -- Store the COURSE in the PL/SQL table.
2330 -- Increment the counter.
2331 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index := c_sca2%ROWCOUNT;
2332 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2333 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_alternate_code :=
2334 v_sca_rec.acad_alternate_code;
2335 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2336 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_course_cd :=
2337 v_sca_rec.course_cd;
2338 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2339 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_title := v_sca_rec.TITLE;
2340 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2341 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_cal_type :=
2342 v_sca_rec.CAL_TYPE;
2343 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2344 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_ci_sequence_number :=
2345 v_sca_rec.sequence_number;
2346 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2347 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_acad_start_dt :=
2348 v_sca_rec.start_dt;
2349 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2350 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_attendance_type :=
2351 v_sca_rec.attendance_type;
2352 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2353 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_location_cd :=
2354 v_sca_rec.location_cd;
2355 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(
2356 IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index).v_commencement_dt :=
2357 v_sca_rec.commencement_dt;
2358 END IF;
2359 END LOOP;
2360 END IF;
2361 END IF;
2362 -- Create the output string based on the p_s_letter_parameter_type
2363 -- and the p_record_number.
2364 v_index := p_record_number;
2365 v_out_string := NULL;
2366 IF v_index <= IGS_AS_PRC_TRANSCRPT.gv_sca_dtl_index THEN
2367 IF p_s_letter_parameter_type = cst_trn_course THEN
2368 v_out_string := fnd_global.local_chr(10) || RPAD(NVL(
2369 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_acad_alternate_code,'-'),10)
2370 || ' ' ||
2371 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_course_cd,'-'),10)
2372 || ' ' ||
2373 RPAD(NVL(IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_title,'-'),90);
2374 ELSIF p_s_letter_parameter_type = cst_trn_crs_cd THEN
2375 v_out_string := NVL(
2376 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_course_cd,'-');
2377 ELSIF p_s_letter_parameter_type = cst_trn_crs_dt THEN
2378 v_out_string := NVL(FND_DATE.DATE_TO_DISPLAYDATE(
2379 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_commencement_dt
2380 ),'-');
2381 ELSIF p_s_letter_parameter_type = cst_trn_crs_at THEN
2382 v_out_string := NVL(
2383 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_attendance_type,'-');
2384 ELSIF p_s_letter_parameter_type = cst_trn_crs_lc THEN
2385 v_out_string := NVL(
2386 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_location_cd,'-');
2387 ELSIF p_s_letter_parameter_type = cst_trn_crs_t THEN
2388 v_out_string := INITCAP(NVL(
2389 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_title,'-'));
2390 ELSE
2391 v_out_string := NULL;
2392 END IF;
2393 p_extra_context := IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_course_cd
2394 || '|' ||
2395 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_acad_cal_type || '|' ||
2396 TO_CHAR(
2397 IGS_AS_PRC_TRANSCRPT.gt_sca_dtl_table(v_index).v_acad_ci_sequence_number);
2398 END IF;
2399 RETURN v_out_string;
2400 EXCEPTION
2401 WHEN OTHERS THEN
2402 IF c_sca%ISOPEN THEN
2403 CLOSE c_sca;
2404 END IF;
2405 IF c_sca2%ISOPEN THEN
2406 CLOSE c_sca2;
2407 END IF;
2408 RAISE;
2409 END;
2410 EXCEPTION
2411 WHEN OTHERS THEN
2412 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2413 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.assp_get_trn_sca_dtl');
2414 IGS_GE_MSG_STACK.ADD;
2415 --APP_EXCEPTION.RAISE_EXCEPTION;
2416 END assp_get_trn_sca_dtl;
2417 END IGS_AS_PRC_TRANSCRPT;
[Home] [Help]