1 PACKAGE BODY IGS_AS_GEN_008 AS
2 /* $Header: IGSAS48B.pls 115.4 2003/11/04 13:41:24 msrinivi noship $ */
3 FUNCTION student_cohort(grading_period in varchar2,
4 person_id in number,
5 unit_cd in varchar2,
6 course_cd in varchar2,
7 load_cal_type in varchar2,
8 load_ci_sequence_number in number) RETURN VARCHAR2
9
10 IS
11
12 CURSOR c_ps(gp_cd varchar2,p_id number) is select 'X' from igs_en_stdnt_ps_att_all a, igs_as_gpc_aca_stndg b
13 where a.progression_status =b.progression_status
14 and b.grading_period_cd = gp_cd
15 and a.person_id = p_id;
16
17 CURSOR c_pr(gp_cd varchar2,p_id number) is select 'X' from igs_en_stdnt_ps_att_all a, igs_as_gpc_programs b
18 where a.course_cd = b.course_cd
19 and a.version_number = b.course_version_number
20 and b.grading_period_cd = gp_cd
21 and a.person_id = p_id
22 and a.COURSE_ATTEMPT_STATUS = 'ENROLLED';
23
24 CURSOR c_us(gp_cd varchar2,p_id number) is select 'X' from igs_as_su_setatmpt a, igs_as_gpc_unit_sets b
25 where a.unit_set_cd = b.unit_set_cd
26 and b.grading_period_cd = gp_cd
27 and a.person_id = p_id;
28
29 CURSOR c_pg(gp_cd varchar2,p_id number) is select 'X' from igs_pe_prsid_grp_mem_all a, igs_as_gpc_pe_id_grp b
30 where a.group_id = b.group_id
31 and b.grading_period_cd = gp_cd
32 and a.person_id = p_id;
33
34 CURSOR c_cs(gp_cd varchar2,p_id number,unit_cd varchar2,course_cd varchar2) is select 'X' from igs_as_gpc_cls_stndg a,igs_en_su_attempt_all b
35 where a.grading_period_cd = gp_cd
36 and b.unit_cd= unit_cd
37 and b.course_cd= course_cd
38 and b.person_id = p_id
39 and a.class_standing = Igs_Pr_Get_Class_Std.get_class_standing (p_id,b.course_cd,'N',sysdate,load_cal_type,load_ci_sequence_number);
40
41 p_status varchar2(30);
42 p_return char(1);
43
44 BEGIN -- student_cohort
45 -- Function for finding whether a student exists as per
46 -- the grading period cohort set up or not.
47 -- This Returns N if student does not exists in any of these cohorts.
48
49 p_return := 'N';
50
51 -- This Cursor checks if Student exists with a particular Progression Status.
52 if (grading_period = 'FINAL') then
53 p_return := 'Y';
54 else
55 open c_ps(grading_period,person_id);
56 fetch c_ps into p_status;
57 if c_ps%NOTFOUND then
58
59 -- This Cursor checks if Student exists with a particular Enrolled Program.
60 open c_pr(grading_period,person_id);
61 fetch c_pr into p_status;
62 if c_pr%NOTFOUND then
63
64 -- This Cursor checks if Student exists within a particular Unit Set.
65 open c_us(grading_period,person_id);
66 fetch c_us into p_status;
67 if c_us%NOTFOUND then
68
69 -- This Cursor checks if Student exists within a particular person group.
70 open c_pg(grading_period,person_id);
71 fetch c_pg into p_status;
72 if c_pg%NOTFOUND then
73
74 -- This Cursor checks if Student exists with a particular Class Standing.
75 open c_cs(grading_period,person_id,unit_cd,course_cd);
76 fetch c_cs into p_status;
77 if c_cs%FOUND then
78 p_return := 'Y';
79 end if;
80
81 close c_cs;
82
83
84 else
85 p_return := 'Y';
86 end if;
87
88 close c_pg;
89
90
91 else
92 p_return := 'Y';
93 end if;
94
95 close c_us;
96
97
98 else
99 p_return := 'Y';
100 end if;
101
102 close c_pr;
103
104 else
105 p_return := 'Y';
106 end if;
107
108
109 close c_ps;
110 end if;
111
112 RETURN p_return;
113
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
118 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.student_cohort');
119 IGS_GE_MSG_STACK.ADD;
120 App_Exception.Raise_Exception;
121
122 END student_cohort;
123
124 FUNCTION repeat_grades_exist( p_person_id IGS_AS_SU_STMPTOUT_ALL.person_id%TYPE,
125 p_unit_cd IGS_AS_SU_STMPTOUT_ALL.unit_cd%TYPE,
126 p_course_cd IGS_AS_SU_STMPTOUT_ALL.course_cd%TYPE,
127 p_cal_type IGS_AS_SU_STMPTOUT_ALL.cal_type%TYPE,
128 p_ci_sequence_number IGS_AS_SU_STMPTOUT_ALL.ci_sequence_number%TYPE,
129 -- anilk, 22-Apr-2003, Bug# 2829262
130 p_uoo_id IGS_AS_SU_STMPTOUT_ALL.uoo_id%TYPE) RETURN VARCHAR2 IS
131 BEGIN -- repeat_grades_exist
132 -- This function checks whether a particular student outcome record
133 -- has any other records created by repeat process
134
135 DECLARE
136
137 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
138 v_repeat_grade_exists VARCHAR2(1) := 'N';
139
140 CURSOR c_rg IS
141 SELECT suao.grade
142 FROM igs_as_suaoa_v suao,
143 igs_as_grd_sch_grade gsg
144 WHERE suao.person_id = p_person_id
145 AND suao.unit_cd = p_unit_cd
146 AND (suao.course_cd <> p_course_cd
147 -- anilk, 22-Apr-2003, Bug# 2829262
148 OR suao.uoo_id <> p_uoo_id)
149 AND suao.finalised_outcome_ind = 'Y'
150 AND suao.grading_period_cd = 'FINAL'
151 AND suao.grading_schema_cd = gsg.grading_schema_cd
152 AND suao.version_number = gsg.version_number
153 AND suao.grade = gsg.repeat_grade;
154
155
156 BEGIN -- Main
157
158 -- Get repeat grades
159 OPEN c_rg;
160 FETCH c_rg INTO v_grade;
161 IF c_rg%FOUND THEN
162 v_repeat_grade_exists := 'Y';
163 END IF;
164 CLOSE c_rg;
165
166 RETURN v_repeat_grade_exists;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
171 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_008.repeat_grades_exist');
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174
175 END;
176
177 END repeat_grades_exist ;
178
179 FUNCTION get_occur_details
180 (
181 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
182 p_occurs_id IGS_PS_USEC_OCCURS.UNIT_SECTION_OCCURRENCE_ID%TYPE
183 )
184 RETURN VARCHAR2 AS
185
186 CURSOR c_occur_dtls IS
187 SELECT
188 uoo_id,
189 DECODE(a.monday, 'Y', 'MONDAY', NULL) Monday,
190 DECODE(a.tuesday, 'Y', 'TUESDAY', NULL) Tuesday,
191 DECODE(a.wednesday, 'Y', 'WEDNESDAY', NULL) Wednesday,
192 DECODE(a.thursday, 'Y', 'THURSDAY', NULL) Thursday,
193 DECODE(a.friday, 'Y', 'FRIDAY', NULL) Friday,
194 DECODE(a.saturday, 'Y', 'SATURDAY', NULL) Saturday,
195 DECODE(a.sunday, 'Y', 'SUNDAY', NULL) Sunday,
196 TO_CHAR(a.start_time, 'hh:miam')||'-'|| TO_CHAR(a.end_time, 'hh:miam')||' '||
197 NVL(d.description,'-')||' '||
198 NVL(b.description,'-')||' '||
199 NVL(c.description,'') location
200 FROM igs_ps_usec_occurs a,
201 igs_ad_building b,
202 igs_ad_room c,
203 igs_ad_location d
204 WHERE
205 a.building_code = b.building_id(+) AND
206 a.room_code = c.room_id(+) AND
207 b.location_cd = d.location_cd(+) AND
208 a.uoo_id = p_uoo_id and
209 a.UNIT_SECTION_OCCURRENCE_ID = p_occurs_id;
210
211 c_occur_dtls_rec c_occur_dtls%ROWTYPE;
212 l_day_string VARCHAR2(2000);
213 BEGIN
214
215 OPEN c_occur_dtls ;
216 FETCH c_occur_dtls INTO c_occur_dtls_REC;
217 CLOSE c_occur_dtls;
218
219 IF c_occur_dtls_REC.MONDAY = 'MONDAY' THEN
220 l_day_string := l_day_string ||' ' ||'Monday';
221 END IF;
222 IF c_occur_dtls_REC.TUESDAY = 'TUESDAY' THEN
223 l_day_string := l_day_string ||' ' ||'Tuesday';
224 END IF;
225 IF c_occur_dtls_REC.WEDNESDAY = 'WEDNESDAY' THEN
226 l_day_string := l_day_string ||' ' ||'Wednesday';
227 END IF;
228 IF c_occur_dtls_REC.THURSDAY = 'THURSDAY' THEN
229 l_day_string := l_day_string ||' ' ||'Thursday';
230 END IF;
231 IF c_occur_dtls_REC.FRIDAY = 'FRIDAY' THEN
232 l_day_string := l_day_string ||' ' ||'Friday';
233 END IF;
234 IF c_occur_dtls_REC.SATURDAY = 'SATURDAY' THEN
235 l_day_string := l_day_string ||' ' ||'Saturday';
236 END IF;
237 IF c_occur_dtls_REC.SUNDAY = 'SUNDAY' THEN
238 l_day_string := l_day_string ||' ' ||'Sunday';
239 END IF;
240
241 RETURN l_day_string ||' ' || c_occur_dtls_REC.location;
242 END get_occur_details;
243
244
245 END IGS_AS_GEN_008;
[Home] [Help]