1 PACKAGE BODY IGS_AD_VAL_SCFT AS
2 /* $Header: IGSAD69B.pls 115.4 2002/11/28 21:39:32 nsidana ship $ */
3
4 -----------------------------------------------------------------------
5 -- Change History :
6 -- Who When What
7 -- avenkatR 30-AUG-2001 Remove procedure "crsp_Val_fs_closed"
8 -- avenkatr 30-AUG-2001 Remove procedure "crsp_Val_iud_crv_dtl"
9 -----------------------------------------------------------------------
10 --
11 -- Validate SCFT optional values unique across records
12 FUNCTION admp_val_scft_uniq(
13 p_submission_yr IN NUMBER ,
14 p_submission_number IN NUMBER ,
15 p_course_cd IN VARCHAR2 ,
16 p_crv_version_number IN NUMBER ,
17 p_funding_source IN VARCHAR2 ,
18 p_location_cd IN VARCHAR2 ,
19 p_attendance_mode IN VARCHAR2 ,
20 p_attendance_type IN VARCHAR2 ,
21 p_unit_set_cd IN VARCHAR2 ,
22 p_us_version_number IN NUMBER ,
23 p_message_name OUT NOCOPY VARCHAR2 )
24 RETURN BOOLEAN AS
25 BEGIN -- admp_val_scft_uniq
26 -- This module checks the combination of optional components of
27 -- IGS_AD_SBM_PS_FNTRGT is unique within submission, course and funding source.
28 DECLARE
29 v_scft_count NUMBER;
30 CURSOR c_scft IS
31 SELECT count(*)
32 FROM IGS_AD_SBM_PS_FNTRGT
33 WHERE submission_yr = p_submission_yr AND
34 submission_number = p_submission_number AND
35 course_cd = p_course_cd AND
36 crv_version_number = p_crv_version_number AND
37 funding_source = p_funding_source AND
38 NVL(location_cd, 'NULL') = NVL(p_location_cd, 'NULL') AND
39 NVL(attendance_mode, 'NULL') = NVL(p_attendance_mode, 'NULL') AND
40 NVL(attendance_type, 'NULL') = NVL(p_attendance_type, 'NULL') AND
41 NVL(unit_set_cd, 'NULL') = NVL(p_unit_set_cd, 'NULL') AND
42 NVL(us_version_number, 0) = NVL(p_us_version_number, 0);
43 BEGIN
44 p_message_name := null;
45 OPEN c_scft;
46 FETCH c_scft INTO v_scft_count;
47 IF (c_scft%FOUND) AND
48 v_scft_count > 1 THEN
49 CLOSE c_scft;
50 p_message_name := 'IGS_AD_COMBINATION_UNIQUE';
51 RETURN FALSE;
52 END IF;
53 CLOSE c_scft;
54 RETURN TRUE;
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF c_scft%ISOPEN THEN
58 CLOSE c_scft;
59 END IF;
60 App_Exception.Raise_Exception;
61 END;
62 EXCEPTION
63 WHEN OTHERS THEN
64 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
65 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SCFT.admp_val_scft_uniq');
66 IGS_GE_MSG_STACK.ADD;
67 App_Exception.Raise_Exception;
68 END admp_val_scft_uniq;
69
70
71 -- Validate crs fund target course version in a valid course off pattern
72 FUNCTION admp_val_scft_cop(
73 p_submission_yr IN NUMBER ,
74 p_submission_number IN NUMBER ,
75 p_course_cd IN VARCHAR2 ,
76 p_crv_version_number IN NUMBER ,
77 p_message_name OUT NOCOPY VARCHAR2 )
78 RETURN BOOLEAN AS
79 BEGIN -- admp_val_scft_cop
80 -- This module validates the IGS_PS_VER component of IGS_AD_SBM_PS_FNTRGT
81 -- exists and is offered within a IGS_PS_OFR_PAT that is in the
82 -- academic period of the submission period.
83 DECLARE
84 v_cop VARCHAR2(1);
85 CURSOR c_cop IS
86 SELECT 'x'
87 FROM IGS_PS_OFR_PAT cop,
88 IGS_ST_GVTSEMLOAD_CA gslc,
89 IGS_CA_INST_REL cir
90 WHERE gslc.submission_yr = p_submission_yr AND
91 gslc.submission_number = p_submission_number AND
92 cop.course_cd = p_course_cd AND
93 cop.version_number = p_crv_version_number AND
94 (cop.offered_ind = 'Y' OR
95 cop.enrollable_ind = 'Y') AND
96 cop.cal_type = cir.sup_cal_type AND
97 cop.ci_sequence_number = cir.sup_ci_sequence_number AND
98 gslc.cal_type = cir.sub_cal_type AND
99 gslc.ci_sequence_number = cir.sub_ci_sequence_number;
100 BEGIN
101 p_message_name := null;
102 OPEN c_cop;
103 FETCH c_cop INTO v_cop;
104 IF (c_cop%NOTFOUND) THEN
105 CLOSE c_cop;
106 p_message_name := 'IGS_AD_PRG_VERSION_DETAILS';
107 RETURN FALSE;
108 END IF;
109 CLOSE c_cop;
110 RETURN TRUE;
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF c_cop%ISOPEN THEN
114 CLOSE c_cop;
115 END IF;
116 App_Exception.Raise_Exception;
117 END;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
121 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SCFT.admp_val_scft_cop');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END admp_val_scft_cop;
125
126 --
127 -- Validate crs fund target funding source is within restriction
128 FUNCTION admp_val_scft_fs(
129 p_course_cd IN VARCHAR2 ,
130 p_crv_version_number IN NUMBER ,
131 p_funding_source IN VARCHAR2 ,
132 p_message_name OUT NOCOPY VARCHAR2 )
133 RETURN BOOLEAN AS
134 BEGIN -- admp_val_scft_fs
135 -- This module validates that the funding_source for the IGS_AD_SBM_PS_FNTRGT
136 -- complies with any IGS_FI_FND_SRC_RSTN.
137 DECLARE
138 v_fsr VARCHAR2(1);
139 CURSOR c_fsr (
140 cp_funding_source IGS_AD_SBM_PS_FNTRGT.funding_source%TYPE) IS
141 SELECT 'x'
142 FROM IGS_FI_FND_SRC_RSTN
143 WHERE course_cd = p_course_cd AND
144 version_number = p_crv_version_number AND
145 restricted_ind = 'Y' AND
146 (cp_funding_source IS NULL OR
147 funding_source = cp_funding_source);
148 BEGIN
149 p_message_name := null;
150 OPEN c_fsr(
151 NULL);
152 FETCH c_fsr INTO v_fsr;
153 IF (c_fsr%FOUND) THEN
154 CLOSE c_fsr;
155 OPEN c_fsr(
156 p_funding_source);
157 FETCH c_fsr INTO v_fsr;
158 IF (c_fsr%NOTFOUND) THEN
159 CLOSE c_fsr;
160 p_message_name := 'IGS_AD_FUNDING_SRC_RESTRICTIO';
161 RETURN FALSE;
162 ELSE
163 CLOSE c_fsr;
164 END IF;
165 ELSE
166 CLOSE c_fsr;
167 END IF;
168 RETURN TRUE;
169 EXCEPTION
170 WHEN OTHERS THEN
171 IF c_fsr%ISOPEN THEN
172 CLOSE c_fsr;
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_VAL_SCFT.admp_val_scft_fs');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END admp_val_scft_fs;
183
184 --
185 -- Validate crs fund target IGS_PS_UNIT set in a valid course offering IGS_PS_UNIT set
186 FUNCTION admp_val_scft_cous(
187 p_course_cd IN VARCHAR2 ,
188 p_crv_version_number IN NUMBER ,
189 p_unit_set_cd IN VARCHAR2 ,
190 p_us_version_number IN NUMBER ,
191 p_location_cd IN VARCHAR2 ,
192 p_attendance_mode IN VARCHAR2 ,
193 p_attendance_type IN VARCHAR2 ,
194 p_message_name OUT NOCOPY VARCHAR2 )
195 RETURN BOOLEAN AS
196 BEGIN -- admp_val_scft_cous
197 -- This module validates the IGS_EN_UNIT_SET component of IGS_AD_SBM_PS_FNTRGT
198 -- does not have a system status of 'INACTIVE' and maps to at least one
199 -- course_offering_unot_set or IGS_PS_OF_OPT_UNT_ST.
200 DECLARE
201 v_coousv VARCHAR2(1);
202 cst_inactive CONSTANT IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE := 'INACTIVE';
203 CURSOR c_coousv IS
204 SELECT 'x'
205 FROM IGS_PS_OFR_OPT_UNIT_SET_V coousv,
206 IGS_EN_UNIT_SET us,
207 IGS_EN_UNIT_SET_STAT uss
208 WHERE coousv.unit_set_cd = p_unit_set_cd AND
209 coousv.us_version_number = p_us_version_number AND
210 coousv.course_cd = p_course_cd AND
211 coousv.crv_version_number = p_crv_version_number AND
212 coousv.location_cd LIKE NVL(p_location_cd, '%') AND
213 coousv.attendance_mode LIKE NVL(p_attendance_mode, '%') AND
214 coousv.attendance_type LIKE NVL(p_attendance_type, '%') AND
215 us.unit_set_cd = coousv.unit_set_cd AND
216 us.version_number = coousv.us_version_number AND
217 uss.unit_set_status = us.unit_set_status AND
218 uss.s_unit_set_status <> cst_inactive;
219 BEGIN
220 p_message_name := null;
221 IF p_unit_set_cd IS NULL AND
222 p_us_version_number IS NULL THEN
223 RETURN TRUE;
224 END IF;
225 OPEN c_coousv;
226 FETCH c_coousv INTO v_coousv;
227 IF (c_coousv%NOTFOUND) THEN
228 CLOSE c_coousv;
229 p_message_name := 'IGS_AD_UNIT_SET_DOESNOT_EXIST';
230 RETURN FALSE;
231 END IF;
232 CLOSE c_coousv;
233 RETURN TRUE;
234 EXCEPTION
235 WHEN OTHERS THEN
236 IF c_coousv%ISOPEN THEN
237 CLOSE c_coousv;
238 END IF;
239 App_Exception.Raise_Exception;
240 END;
241 EXCEPTION
242 WHEN OTHERS THEN
243 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
244 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SCFT.admp_val_scft_cous');
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception;
247 END admp_val_scft_cous;
248
249 --
250 -- Validate crs fund target detail in a valid course offering pattern
251 FUNCTION admp_val_scft_dtl(
252 p_submission_yr IN NUMBER ,
253 p_submission_number IN NUMBER ,
254 p_course_cd IN VARCHAR2 ,
255 p_crv_version_number IN NUMBER ,
256 p_location_cd IN VARCHAR2 ,
257 p_attendance_mode IN VARCHAR2 ,
258 p_attendance_type IN VARCHAR2 ,
259 p_message_name OUT NOCOPY VARCHAR2 )
260 RETURN BOOLEAN AS
261 BEGIN -- admp_val_scft_dtl
262 -- This module validates the location_cd/attendance_mode/attendance_type are in
263 -- at least one enrollable or offered IGS_PS_OFR_PAT for the academic
264 -- period of the submission period.
265 DECLARE
266 v_cop VARCHAR2(1);
267 CURSOR c_cop IS
268 SELECT 'x'
269 FROM IGS_PS_OFR_PAT cop,
270 IGS_ST_GVTSEMLOAD_CA gslc,
271 IGS_CA_INST_REL cir
272 WHERE gslc.submission_yr = p_submission_yr AND
273 gslc.submission_number = p_submission_number AND
274 cop.course_cd = p_course_cd AND
275 cop.version_number = p_crv_version_number AND
276 (cop.offered_ind = 'Y' OR
277 cop.enrollable_ind = 'Y') AND
278 cop.cal_type = cir.sup_cal_type AND
279 cop.ci_sequence_number = cir.sup_ci_sequence_number AND
280 gslc.cal_type = cir.sub_cal_type AND
281 gslc.ci_sequence_number = cir.sub_ci_sequence_number AND
282 cop.location_cd LIKE NVL(p_location_cd, '%') AND
283 cop.attendance_mode LIKE NVL(p_attendance_mode, '%') AND
284 cop.attendance_type LIKE NVL(p_attendance_type, '%');
285 BEGIN
286 p_message_name := null;
287 OPEN c_cop;
288 FETCH c_cop INTO v_cop;
289 IF (c_cop%NOTFOUND) THEN
290 CLOSE c_cop;
291 p_message_name := 'IGS_AD_COMBINATION_DOESNOT_EX';
292 RETURN FALSE;
293 END IF;
294 CLOSE c_cop;
295 RETURN TRUE;
296 EXCEPTION
297 WHEN OTHERS THEN
298 IF c_cop%ISOPEN THEN
299 CLOSE c_cop;
300 END IF;
301 App_Exception.Raise_Exception;
302 END;
303 EXCEPTION
304 WHEN OTHERS THEN
305 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
306 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_SCFT.admp_val_scft_dtl');
307 IGS_GE_MSG_STACK.ADD;
308 App_Exception.Raise_Exception;
309 END admp_val_scft_dtl;
310 END IGS_AD_VAL_SCFT;
[Home] [Help]