1 PACKAGE BODY IGS_AD_GEN_005 AS
2 /* $Header: IGSAD05B.pls 120.0 2005/06/01 17:19:04 appldev noship $ */
3 /* Change History
4 who when what
5 smvk 09-Jul-2004 Bug # 3676145. Modified cursors c_ucl to use Active (not closed) unit classes.
6 */
7
8 Function Admp_Get_Crv_Strt_Dt(
9 p_adm_cal_type IN VARCHAR2 ,
10 p_adm_ci_sequence_number IN NUMBER )
11 RETURN DATE IS
12 BEGIN -- admp_get_crv_strt_dt
13 -- Routine to return the course version start date
14 DECLARE
15 v_alias_val DATE;
16 CURSOR c_daiv IS
17 SELECT IGS_CA_GEN_001.calp_set_alias_value(
18 daiv.absolute_val,
19 IGS_CA_GEN_002.cals_clc_dt_from_dai(
20 daiv.ci_sequence_number,
21 daiv.CAL_TYPE,
22 daiv.DT_ALIAS,
23 daiv.sequence_number) ) alias_val
24 FROM IGS_CA_DA_INST daiv,
25 IGS_AD_CAL_CONF sacc
26 WHERE daiv.dt_alias = sacc.adm_appl_course_strt_dt_alias AND
27 daiv.cal_type = p_adm_cal_type AND
28 daiv.ci_sequence_number = p_adm_ci_sequence_number AND
29 sacc.s_control_num = 1
30 ORDER BY 1 desc;
31 BEGIN
32 OPEN c_daiv;
33 FETCH c_daiv INTO v_alias_val;
34 IF (c_daiv%NOTFOUND) THEN
35 CLOSE c_daiv;
36 RETURN NULL;
37 ELSE -- for the first record
38 CLOSE c_daiv;
39 RETURN v_alias_val;
40 END IF;
41 END;
42 END admp_get_crv_strt_dt;
43
44 Function Admp_Get_Dflt_Ccm(
45 p_admission_cat IN VARCHAR2 ,
46 p_description OUT NOCOPY VARCHAR2 )
47 RETURN VARCHAR2 IS
48 BEGIN -- admp_get_dflt_ccm
49 -- Gets the default correspondence category mapping for an admission category.
50 -- The default value must not be closed.
51 p_description := NULL;
52 DECLARE
53 CURSOR c_ccm IS
54 SELECT ccm.correspondence_cat,
55 cc.description
56 FROM IGS_CO_CAT_MAP ccm,
57 IGS_CO_CAT cc
58 WHERE ccm.admission_cat = p_admission_cat AND
59 ccm.dflt_cat_ind = 'Y' AND
60 cc.correspondence_cat = ccm.correspondence_cat AND
61 cc.closed_ind = 'N';
62 v_ccm_rec c_ccm%ROWTYPE;
63 v_correspondence_cat IGS_CO_CAT_MAP.correspondence_cat%TYPE
64 DEFAULT NULL;
65 BEGIN
66 -- get the correspondence_cat record and check for Multiple Rows
67 FOR v_ccm_rec IN c_ccm LOOP
68 IF c_ccm%ROWCOUNT > 1 THEN
69 v_correspondence_cat := NULL;
70 p_description := NULL;
71 EXIT;
72 END IF;
73 v_correspondence_cat := v_ccm_rec.correspondence_cat;
74 p_description := v_ccm_rec.description;
75 END LOOP;
76 RETURN v_correspondence_cat;
77 EXCEPTION
78 WHEN OTHERS THEN
79 IF c_ccm%ISOPEN THEN
80 CLOSE c_ccm;
81 END IF;
82 App_Exception.Raise_Exception;
83 END;
84 EXCEPTION
85 WHEN OTHERS THEN
86 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
87 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_ccm');
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END admp_get_dflt_ccm;
91
92 Function Admp_Get_Dflt_Ecm(
93 p_admission_cat IN VARCHAR2 ,
94 p_description OUT NOCOPY VARCHAR2 )
95 RETURN VARCHAR2 IS
96 BEGIN -- admp_get_dflt_ecm
97 -- Gets the default enrolment category mapping for an admission category.
98 -- The default value must not be closed.
99 DECLARE
100 CURSOR c_ecm IS
101 SELECT ecm.enrolment_cat,
102 ec.description
103 FROM IGS_EN_CAT_MAPPING ecm,
104 IGS_EN_ENROLMENT_CAT ec
105 WHERE ecm.admission_cat = p_admission_cat AND
106 ecm.dflt_cat_ind = 'Y' AND
107 ec.enrolment_cat = ecm.enrolment_cat AND
108 ec.closed_ind = 'N';
109 v_ecm_rec c_ecm%ROWTYPE;
110 v_enrolment_cat IGS_EN_CAT_MAPPING.enrolment_cat%TYPE DEFAULT NULL;
111 BEGIN
112 -- get the enrolment_cat record and check for Multiple Rows
113 FOR v_ecm_rec IN c_ecm LOOP
114 IF c_ecm%ROWCOUNT > 1 THEN
115 v_enrolment_cat := NULL;
116 p_description := NULL;
117 EXIT;
118 END IF;
119 v_enrolment_cat := v_ecm_rec.enrolment_cat;
120 p_description := v_ecm_rec.description;
121 END LOOP;
122 RETURN v_enrolment_cat;
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF c_ecm%ISOPEN THEN
126 CLOSE c_ecm;
127 END IF;
128 App_Exception.Raise_Exception;
129 END;
130 EXCEPTION
131 WHEN OTHERS THEN
132 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
133 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_ecm');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END admp_get_dflt_ecm ;
137
138 Function Admp_Get_Dflt_Fcm(
139 p_admission_cat IN VARCHAR2 ,
140 p_description OUT NOCOPY VARCHAR2 )
141 RETURN VARCHAR2 IS
142 BEGIN -- admp_get_dflt_fcm
143 -- Gets the default fee category mapping for an admission category.
144 -- The default value must not be closed.
145 DECLARE
146 CURSOR c_fcm IS
147 SELECT fcm.fee_cat,
148 fc.description
149 FROM IGS_FI_FEE_CAT_MAP fcm,
150 IGS_FI_FEE_CAT fc
151 WHERE admission_cat = p_admission_cat AND
152 dflt_cat_ind = 'Y' AND
153 fc.fee_cat = fcm.fee_cat AND
154 fc.closed_ind = 'N';
155 v_fcm_rec c_fcm%ROWTYPE;
156 v_fee_cat IGS_FI_FEE_CAT_MAP.fee_cat%TYPE DEFAULT NULL;
157 BEGIN
158 --get the fee_cat record and check for Multiple Rows
159 FOR v_fcm_rec IN c_fcm LOOP
160 IF c_fcm%ROWCOUNT > 1 THEN
161 v_fee_cat := NULL;
162 p_description := NULL;
163 EXIT;
164 END IF;
165 v_fee_cat := v_fcm_rec.fee_cat;
166 p_description := v_fcm_rec.description;
167 END LOOP;
168 RETURN v_fee_cat;
169 EXCEPTION
170 WHEN OTHERS THEN
171 IF c_fcm%ISOPEN THEN
172 CLOSE c_fcm;
173 END IF;
174 App_Exception.Raise_Exception;
175 END;
176 EXCEPTION
177 WHEN OTHERS THEN
178 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
179 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_fcm');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END admp_get_dflt_fcm ;
183
184 Function Admp_Get_Dflt_Fs(
185 p_course_cd IN VARCHAR2 ,
186 p_version_number IN NUMBER ,
187 p_description OUT NOCOPY VARCHAR2 )
188 RETURN VARCHAR2 IS
189 BEGIN -- admp_get_dflt_fs
190 -- Description: This module gets the default funding source for a course
191 -- version. The default value must not be closed
192 DECLARE
193 v_loop_boolean BOOLEAN DEFAULT FALSE;
194 v_funding_source IGS_FI_FUND_SRC.funding_source%TYPE;
195 CURSOR c_fsr_fs IS
196 SELECT fsr.funding_source,
197 fs.description
198 FROM IGS_FI_FND_SRC_RSTN fsr,
199 IGS_FI_FUND_SRC fs
200 WHERE fsr.course_cd = p_course_cd AND
201 fsr.version_number = p_version_number AND
202 fsr.dflt_ind = 'Y' AND
203 fs.funding_source = fsr.funding_source AND
204 fs.closed_ind = 'N';
205 BEGIN
206 FOR v_fsr_fs_recs IN c_fsr_fs LOOP
207 IF ((c_fsr_fs%ROWCOUNT) > 1) THEN
208 v_loop_boolean := TRUE;
209 EXIT;
210 ELSE
211 p_description := v_fsr_fs_recs.description;
212 v_funding_source := v_fsr_fs_recs.funding_source;
213 END IF;
214 END LOOP;
215 IF v_loop_boolean = TRUE THEN
216 RETURN NULL;
217 ELSE
218 RETURN v_funding_source;
219 END IF;
220 END;
221 EXCEPTION
222 WHEN OTHERS THEN
223 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
224 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_fs');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END admp_get_dflt_fs;
228
229 Function Admp_Get_Dflt_Hpo(
230 p_admission_cat IN VARCHAR2 ,
231 p_description OUT NOCOPY VARCHAR2 )
232 RETURN VARCHAR2 IS
233 BEGIN -- admp_get_dflt_hpo
234 -- Returns the default HECS payment option for an admission category.
235 -- The default value only exists when one and only one HECS payment option
236 -- is mapped to the admission category.
237 -- If no default value exists the routine returns a null value.
238 -- The default value must not be closed.
239 DECLARE
240 v_hecs_payment_option IGS_AD_CT_HECS_PAYOP.hecs_payment_option%TYPE;
241 CURSOR c_achpo (
242 cp_admission_cat IGS_AD_CT_HECS_PAYOP.admission_cat%TYPE) IS
243 SELECT achpo.hecs_payment_option,
244 hpo.description
245 FROM IGS_AD_CT_HECS_PAYOP achpo,
246 IGS_FI_HECS_PAY_OPTN hpo
247 WHERE achpo.admission_cat = cp_admission_cat AND
248 1 = (
249 SELECT count(*)
250 FROM IGS_AD_CT_HECS_PAYOP achpo
251 WHERE achpo.admission_cat = cp_admission_cat) AND
252 hpo.hecs_payment_option = achpo.hecs_payment_option AND
253 hpo.closed_ind = 'N';
254 BEGIN
255 OPEN c_achpo(
256 p_admission_cat);
257 FETCH c_achpo INTO v_hecs_payment_option,
258 p_description;
259 IF(c_achpo%FOUND) THEN
260 CLOSE c_achpo;
261 RETURN v_hecs_payment_option;
262 ELSE
263 CLOSE c_achpo;
264 RETURN NULL;
265 END IF;
266 END;
267 EXCEPTION
268 WHEN OTHERS THEN
269 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
270 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_hpo');
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END admp_get_dflt_hpo;
274
275 Function Admp_Get_Dflt_Uc(
276 p_unit_mode IN VARCHAR2 )
277 RETURN VARCHAR2 IS
278 BEGIN -- admp_get_dflt_uc
279 -- Return the default unit class for a unit mode.
280 DECLARE
281 v_multiple_records BOOLEAN DEFAULT FALSE;
282 v_unit_class IGS_AS_UNIT_CLASS.unit_class%TYPE;
283 CURSOR c_ucl IS
284 SELECT UNIQUE ucl.unit_class
285 FROM IGS_AS_UNIT_CLASS ucl
286 WHERE ucl.unit_mode = p_unit_mode AND
287 ucl.closed_ind = 'N';
288 BEGIN
289 FOR v_ucl_rec IN c_ucl LOOP
290 IF (c_ucl%ROWCOUNT > 1) THEN
291 v_multiple_records := TRUE;
292 EXIT;
293 END IF;
294 v_unit_class := v_ucl_rec.unit_class;
295 END LOOP;
296 IF NOT v_multiple_records THEN
297 RETURN v_unit_class;
298 ELSE -- multiple records
299 RETURN NULL;
300 END IF;
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF c_ucl%ISOPEN THEN
304 CLOSE c_ucl;
305 END IF;
306 App_Exception.Raise_Exception;
307 END;
308 EXCEPTION
309 WHEN OTHERS THEN
310 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
311 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_uc');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END admp_get_dflt_uc;
315
316 Function Admp_Get_Dflt_Um(
317 p_unit_class IN VARCHAR2 )
318 RETURN VARCHAR2 IS
319 BEGIN -- admp_get_dflt_um
320 -- Return the default unit mode for a unit class
321 DECLARE
322 v_unit_mode IGS_AS_UNIT_CLASS.unit_mode%TYPE;
323 v_count_OK BOOLEAN DEFAULT FALSE;
324 CURSOR c_ucl IS
325 SELECT ucl.unit_mode
326 FROM IGS_AS_UNIT_CLASS ucl
327 WHERE ucl.unit_class = p_unit_class
328 AND ucl.closed_ind = 'N';
329 BEGIN
330 FOR v_ucl_rec IN c_ucl LOOP
331 IF c_ucl%ROWCOUNT = 1 THEN
332 v_unit_mode := v_ucl_rec.unit_mode;
333 v_count_OK := TRUE;
334 ELSE
335 v_count_OK := FALSE;
336 EXIT;
337 END IF;
338 END LOOP;
339 IF NOT v_count_OK THEN
340 RETURN NULL;
341 END IF;
342 RETURN v_unit_mode;
343 EXCEPTION
344 WHEN OTHERS THEN
345 IF c_ucl%ISOPEN THEN
346 CLOSE c_ucl;
347 END IF;
348 App_Exception.Raise_Exception;
349 END;
350 EXCEPTION
351 WHEN OTHERS THEN
352 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
353 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_005.admp_get_dflt_um');
354 IGS_GE_MSG_STACK.ADD;
355 App_Exception.Raise_Exception;
356 END admp_get_dflt_um;
357
358 END IGS_AD_GEN_005;
[Home] [Help]