eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.IGS_AS_PRC_TRANSCRPT

Source


1 PACKAGE IGS_AS_PRC_TRANSCRPT AS
2 /* $Header: IGSAS08S.pls 120.0 2005/07/05 11:21:53 appldev noship $ */
3   --
4   TYPE r_sca_dtl IS RECORD
5   (
6   v_acad_alternate_code VARCHAR2(10),
7   v_course_cd IGS_EN_STDNT_PS_ATT_ALL.COURSE_CD%TYPE,
8   v_title IGS_PS_VER_ALL.TITLE%TYPE,
9   v_acad_cal_type IGS_CA_INST_ALL.CAL_TYPE%TYPE,
10   v_acad_ci_sequence_number IGS_CA_INST_ALL.SEQUENCE_NUMBER%TYPE,
11   v_acad_start_dt IGS_CA_INST_ALL.START_DT%TYPE,
12   v_attendance_type IGS_PS_OFR_PAT.ATTENDANCE_TYPE%TYPE,
13   v_location_cd IGS_PS_OFR_PAT.LOCATION_CD%TYPE,
14   v_commencement_dt IGS_EN_STDNT_PS_ATT_ALL.COMMENCEMENT_DT%TYPE);
15   --
16   --
17   TYPE r_sua_dtl IS RECORD
18   (
19   v_teach_alternate_code IGS_CA_INST_ALL.ALTERNATE_CODE%TYPE,
20   v_unit_cd IGS_EN_SU_ATTEMPT_ALL.UNIT_CD%TYPE,
21   v_title IGS_PS_UNIT_VER_ALL.TITLE%TYPE,
22   v_short_title IGS_PS_UNIT_VER_ALL.SHORT_TITLE%TYPE,
23   v_cp_achievable IGS_PS_UNIT_VER_ALL.ACHIEVABLE_CREDIT_POINTS%TYPE,
24   v_cp_achieved IGS_PS_UNIT_VER_ALL.ACHIEVABLE_CREDIT_POINTS%TYPE,
25   v_unit_level IGS_PS_UNIT_VER_ALL.UNIT_LEVEL%TYPE,
26   v_mark IGS_AS_SU_STMPTOUT_ALL.MARK%TYPE,
27   v_grade IGS_AS_SU_STMPTOUT_ALL.GRADE%TYPE,
28   v_grading_schema_cd IGS_AS_SU_STMPTOUT_ALL.GRADING_SCHEMA_CD%TYPE,
29   v_gs_version_number IGS_AS_SU_STMPTOUT_ALL.VERSION_NUMBER%TYPE,
30   v_s_result IGS_AS_GRD_SCH_GRADE.S_RESULT_TYPE%TYPE);
31   --
32   --
33   TYPE r_stdg_dtl IS RECORD
34   (
35   v_acad_alternate_code VARCHAR2(10),
36   v_course_cd IGS_EN_STDNT_PS_ATT_ALL.COURSE_CD%TYPE,
37   v_int_start_dt IGS_EN_STDNT_PS_INTM.START_DT%TYPE,
38   v_int_end_dt IGS_EN_STDNT_PS_INTM.END_DT%TYPE,
39   v_lapsed_dt IGS_EN_STDNT_PS_ATT_ALL.LAPSED_DT%TYPE,
40   v_discontinued_dt IGS_EN_STDNT_PS_ATT_ALL.DISCONTINUED_DT%TYPE,
41   v_type VARCHAR2(10));
42   --
43   --
44   TYPE r_adv_dtl IS RECORD
45   (
46   v_title VARCHAR2(50));
47   --
48   --
49   TYPE r_asu_dtl IS RECORD
50   (
51   v_unit_cd IGS_AV_STND_UNIT_ALL.UNIT_CD%TYPE,
52   v_unit_level IGS_PS_UNIT_VER_ALL.UNIT_LEVEL%TYPE,
53   v_cp_achievable IGS_PS_UNIT_VER_ALL.ACHIEVABLE_CREDIT_POINTS%TYPE,
54   v_title IGS_PS_UNIT_VER_ALL.TITLE%TYPE,
55   v_short_title IGS_PS_UNIT_VER_ALL.SHORT_TITLE%TYPE);
56   --
57   --
58   TYPE r_asule_dtl IS RECORD
59   (
60   v_description VARCHAR2(50),
61   v_unit_level IGS_AV_STND_UNIT_LVL_ALL.UNIT_LEVEL%TYPE,
62   v_cp_granted IGS_AV_STND_UNIT_LVL_ALL.CREDIT_POINTS%TYPE);
63   --
64   --
65   TYPE r_sct_dtl IS RECORD
66   (
67   v_teach_alternate_code IGS_CA_INST_ALL.ALTERNATE_CODE%TYPE,
68   v_from_course IGS_PS_STDNT_TRN.TRANSFER_COURSE_CD%TYPE,
69   v_to_course IGS_PS_STDNT_TRN.COURSE_CD%TYPE,
70   v_transfer_dt IGS_PS_STDNT_TRN.TRANSFER_DT%TYPE,
71   v_unit_ind VARCHAR2(1));
72   --
73   --
74   TYPE r_sut_dtl IS RECORD
75   (
76   v_teach_alternate_code IGS_CA_INST_ALL.ALTERNATE_CODE%TYPE,
77   v_acad_alternate_code IGS_CA_INST_ALL.ALTERNATE_CODE%TYPE,
78   v_unit_cd IGS_PS_STDNT_UNT_TRN.UNIT_CD%TYPE,
79   v_title IGS_PS_UNIT_VER_ALL.TITLE%TYPE,
80   v_short_title IGS_PS_UNIT_VER_ALL.SHORT_TITLE%TYPE,
81   v_cp_achievable IGS_PS_UNIT_VER_ALL.ACHIEVABLE_CREDIT_POINTS%TYPE,
82   v_cp_achieved IGS_PS_UNIT_VER_ALL.ACHIEVABLE_CREDIT_POINTS%TYPE,
83   v_unit_level IGS_PS_UNIT_VER_ALL.UNIT_LEVEL%TYPE,
84   v_mark IGS_AS_SU_STMPTOUT_ALL.MARK%TYPE,
85   v_grade IGS_AS_SU_STMPTOUT_ALL.GRADE%TYPE,
86   v_grading_schema_cd IGS_AS_SU_STMPTOUT_ALL.GRADING_SCHEMA_CD%TYPE,
87   v_gs_version_number IGS_AS_SU_STMPTOUT_ALL.VERSION_NUMBER%TYPE);
88   --
89   --
90   TYPE r_susa_dtl IS RECORD
91   (
92   v_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
93   v_title IGS_EN_UNIT_SET_ALL.TITLE%TYPE,
94   v_unit_set_cat IGS_EN_UNIT_SET_CAT.UNIT_SET_CAT%TYPE,
95   v_unit_set_cat_desc IGS_EN_UNIT_SET_CAT.DESCRIPTION%TYPE,
96   v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE,
97   v_primary_set_ind IGS_AS_SU_SETATMPT.PRIMARY_SET_IND%TYPE,
98   v_completion_dt IGS_AS_SU_SETATMPT.RQRMNTS_COMPLETE_DT%TYPE);
99   --
100   --
101   TYPE r_res_dtl IS RECORD
102   (
103   v_teach_alternate_code IGS_CA_INST_ALL.ALTERNATE_CODE%TYPE,
104   v_course_cd IGS_EN_STDNT_PS_ATT_ALL.COURSE_CD%TYPE,
105   v_title IGS_RE_THESIS_ALL.TITLE%TYPE,
106   v_final_title_ind IGS_RE_THESIS_ALL.FINAL_TITLE_IND%TYPE);
107   --
108   --
109   TYPE r_grd_dtl IS RECORD
110   (
111   v_acad_alternate_code VARCHAR2(10),
112   v_course_cd IGS_EN_STDNT_PS_ATT_ALL.COURSE_CD%TYPE,
113   v_completion_dt IGS_EN_STDNT_PS_ATT_ALL.COURSE_RQRMNTS_COMPLETE_DT%TYPE,
114   v_conferral_dt IGS_GR_GRADUAND.CONFERRAL_DT%TYPE,
115   v_award_title IGS_PS_AWD.AWARD_TITLE%TYPE,
116   v_type VARCHAR2(10));
117   --
118   --
119   TYPE t_sca_dtl IS TABLE OF
120   IGS_AS_PRC_TRANSCRPT.r_sca_dtl
121   INDEX BY BINARY_INTEGER;
122   --
123   --
124   TYPE t_sua_dtl IS TABLE OF
125   IGS_AS_PRC_TRANSCRPT.r_sua_dtl
126   INDEX BY BINARY_INTEGER;
127   --
128   --
129   TYPE t_stdg_dtl IS TABLE OF
130   IGS_AS_PRC_TRANSCRPT.r_stdg_dtl
131   INDEX BY BINARY_INTEGER;
132   --
133   --
134   TYPE t_adv_dtl IS TABLE OF
135   IGS_AS_PRC_TRANSCRPT.r_adv_dtl
136   INDEX BY BINARY_INTEGER;
137   --
138   --
139   TYPE t_asu_dtl IS TABLE OF
140   IGS_AS_PRC_TRANSCRPT.r_asu_dtl
141   INDEX BY BINARY_INTEGER;
142   --
143   --
144   TYPE t_asule_dtl IS TABLE OF
145   IGS_AS_PRC_TRANSCRPT.r_asule_dtl
146   INDEX BY BINARY_INTEGER;
147   --
148   --
149   TYPE t_sct_dtl IS TABLE OF
150   IGS_AS_PRC_TRANSCRPT.r_sct_dtl
151   INDEX BY BINARY_INTEGER;
152   --
153   --
154   TYPE t_sut_dtl IS TABLE OF
155   IGS_AS_PRC_TRANSCRPT.r_sut_dtl
156   INDEX BY BINARY_INTEGER;
157   --
158   --
159   TYPE t_susa_dtl IS TABLE OF
160   IGS_AS_PRC_TRANSCRPT.r_susa_dtl
161   INDEX BY BINARY_INTEGER;
162   --
163   --
164   TYPE t_res_dtl IS TABLE OF
165   IGS_AS_PRC_TRANSCRPT.r_res_dtl
166   INDEX BY BINARY_INTEGER;
167   --
168   --
169   TYPE t_grd_dtl IS TABLE OF
170   IGS_AS_PRC_TRANSCRPT.r_grd_dtl
171   INDEX BY BINARY_INTEGER;
172   --
173   --
174   gt_sca_dtl_table t_sca_dtl;
175   --
176   --
177   gt_asule_dtl_table t_asule_dtl;
178   --
179   --
180   gt_sut_dtl_table t_sut_dtl;
181   --
182   --
183   gt_sct_dtl_table t_sct_dtl;
184   --
185   --
186   gt_res_dtl_table t_res_dtl;
187   --
188   --
189   gt_asu_dtl_table t_asu_dtl;
190   --
191   --
192   gt_adv_dtl_table t_adv_dtl;
193   --
194   --
195   gt_susa_dtl_table t_susa_dtl;
196   --
197   --
198   gt_sua_dtl_table t_sua_dtl;
199   --
200   --
201   gt_stdg_dtl_table t_stdg_dtl;
202   --
203   --
204   gt_grd_dtl_table t_grd_dtl;
205   --
206   --
207   gv_sca_dtl_index BINARY_INTEGER;
208   --
209   --
210   gv_stdg_dtl_index BINARY_INTEGER;
211   --
212   --
213   gv_grd_dtl_index BINARY_INTEGER;
214   --
215   --
216   gv_asule_dtl_index BINARY_INTEGER;
217   --
218   --
219   gv_sut_dtl_index BINARY_INTEGER;
220   --
221   --
222   gv_res_dtl_index BINARY_INTEGER;
223   --
224   --
225   gv_sct_dtl_index BINARY_INTEGER;
226   --
227   --
228   gv_susa_dtl_index BINARY_INTEGER;
229   --
230   --
231   gv_asu_dtl_index BINARY_INTEGER;
232   --
233   --
234   gv_adv_dtl_index BINARY_INTEGER;
235   --
236   --
237   gv_sua_dtl_index BINARY_INTEGER;
238   --
239   -- Retrieves graduation details for display on transcript
240   FUNCTION assp_get_trn_grd_dtl(
241   p_person_id IN NUMBER ,
242   p_course_cd IN VARCHAR2 ,
243   p_s_letter_parameter_type IN VARCHAR2 ,
244   p_acad_cal_type IN VARCHAR2 ,
245   p_acad_ci_sequence_number IN NUMBER ,
246   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
247   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
248   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
249   p_exclude_unit_category IN VARCHAR2 ,
250   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
251   p_record_number IN NUMBER )
252 RETURN VARCHAR2;
253 
254   --
255   -- To get one component of a string which is delimited.
256   FUNCTION ASSP_GET_TRN_DESC(
257   p_extract_course_cd IN VARCHAR2 )
258 RETURN VARCHAR2;
259 PRAGMA RESTRICT_REFERENCES(ASSP_GET_TRN_DESC,WNDS);
260   --
261   -- Retrieves research details for display on transcript.
262   FUNCTION assp_get_trn_res_dtl(
263   p_person_id IN NUMBER ,
264   p_course_cd IN VARCHAR2 ,
265   p_s_letter_parameter_type IN VARCHAR2 ,
266   p_acad_cal_type IN VARCHAR2 ,
267   p_acad_ci_sequence_number IN NUMBER ,
268   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
269   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
270   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
271   p_exclude_unit_category IN VARCHAR2 ,
272   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
273   p_record_number IN NUMBER )
274 RETURN VARCHAR2;
275 
276  --
277   -- Retrieves UNIT set attempt details for display on transcript.
278   FUNCTION assp_get_trn_us_dtl(
279   p_person_id IN NUMBER ,
280   p_course_cd IN VARCHAR2 ,
281   p_s_letter_parameter_type IN VARCHAR2 ,
282   p_acad_cal_type IN VARCHAR2 ,
283   p_acad_ci_sequence_number IN NUMBER ,
284   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
285   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
286   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
287   p_exclude_unit_category IN VARCHAR2 ,
288   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
289   p_record_number IN NUMBER )
290 RETURN VARCHAR2;
291 
292   --
293   -- Retrieves UNIT transfer details for display on transcript
294   FUNCTION assp_get_trn_sut_dtl(
295   p_person_id IN NUMBER ,
296   p_to_course_cd IN VARCHAR2 ,
297   p_s_letter_parameter_type IN VARCHAR2 ,
298   p_acad_cal_type IN VARCHAR2 ,
299   p_acad_ci_sequence_number IN NUMBER ,
300   p_record_number IN NUMBER )
301 RETURN VARCHAR2;
302 
303   --
304   -- Retrieves COURSE transfer details for display on transcript
305   FUNCTION assp_get_trn_sct_dtl(
306   p_person_id IN NUMBER ,
307   p_course_cd IN VARCHAR2 ,
308   p_s_letter_parameter_type IN VARCHAR2 ,
309   p_acad_cal_type IN VARCHAR2 ,
310   p_acad_ci_sequence_number IN NUMBER ,
311   p_record_number IN NUMBER )
312 RETURN VARCHAR2;
313 PRAGMA RESTRICT_REFERENCES(assp_get_trn_sct_dtl,WNDS);
314   --
315   -- Retrieves COURSE standing details for display on transcript
316   FUNCTION assp_get_trn_crs_std(
317   p_person_id IN NUMBER ,
318   p_course_cd IN VARCHAR2 ,
319   p_s_letter_parameter_type IN VARCHAR2 ,
320   p_acad_cal_type IN VARCHAR2 ,
321   p_acad_ci_sequence_number IN NUMBER ,
322   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
323   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
324   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
325   p_exclude_unit_category IN VARCHAR2 ,
326   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
327   p_record_number IN NUMBER )
328 RETURN VARCHAR2;
329 PRAGMA RESTRICT_REFERENCES(assp_get_trn_crs_std,WNDS);
330   --
331   -- Retrieves basic UNIT details for display on transcript
332   FUNCTION assp_get_trn_sua_dtl(
333   p_person_id IN NUMBER ,
334   p_course_cd IN VARCHAR2 ,
335   p_s_letter_parameter_type IN VARCHAR2 ,
336   p_acad_cal_type IN VARCHAR2 ,
337   p_acad_ci_sequence_number IN NUMBER ,
338   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
339   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
340   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
341   p_exclude_unit_category IN VARCHAR2 ,
342   p_record_number IN NUMBER )
343 RETURN VARCHAR2;
344 PRAGMA RESTRICT_REFERENCES(assp_get_trn_sua_dtl,WNDS);
345   --
346   -- Retrieves adv standing UNIT level details for display on transcript
347   FUNCTION assp_get_trn_asl_dtl(
348   p_person_id IN NUMBER ,
349   p_course_cd IN VARCHAR2 ,
350   p_s_letter_parameter_type IN VARCHAR2 ,
351   p_acad_cal_type IN VARCHAR2 ,
352   p_acad_ci_sequence_number IN NUMBER ,
353   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
354   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
355   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
356   p_exclude_unit_category IN VARCHAR2 ,
357   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
358   p_record_number IN NUMBER )
359 RETURN VARCHAR2;
360 
361   --
362   -- Retrieves advanced standing UNIT details for display on transcript
363   FUNCTION assp_get_trn_asu_dtl(
364   p_person_id IN NUMBER ,
365   p_course_cd IN VARCHAR2 ,
366   p_s_letter_parameter_type IN VARCHAR2 ,
367   p_acad_cal_type IN VARCHAR2 ,
368   p_acad_ci_sequence_number IN NUMBER ,
369   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
370   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
371   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
372   p_exclude_unit_category IN VARCHAR2 ,
373   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
374   p_record_number IN NUMBER )
375 RETURN VARCHAR2;
376 
377   --
378   -- Retrieves basic advanced standing details for display on transcript
379   FUNCTION assp_get_trn_adv_dtl(
380   p_person_id IN NUMBER ,
381   p_course_cd IN VARCHAR2 ,
382   p_s_letter_parameter_type IN VARCHAR2 ,
383   p_acad_cal_type IN VARCHAR2 ,
384   p_acad_ci_sequence_number IN NUMBER ,
385   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
386   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
387   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
388   p_exclude_unit_category IN VARCHAR2 ,
389   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
390   p_record_number IN NUMBER )
391 RETURN VARCHAR2;
392 
393   --
394   -- Retrieves basic COURSE details for display on transcript
395   FUNCTION assp_get_trn_sca_dtl(
396   p_person_id IN NUMBER ,
397   p_course_cd IN VARCHAR2 ,
398   p_s_letter_parameter_type IN VARCHAR2 ,
399   p_order_by IN VARCHAR2 DEFAULT 'YEAR',
400   p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
401   p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
402   p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
403   p_exclude_unit_category IN VARCHAR2 ,
404   p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
405   p_record_number IN NUMBER ,
406   p_extra_context OUT NOCOPY VARCHAR2 )
407 RETURN VARCHAR2;
408 
409 END IGS_AS_PRC_TRANSCRPT;