1 PACKAGE BODY IGS_AS_VAL_GSG AS
2 /* $Header: IGSAS24B.pls 115.3 2002/11/28 22:45:30 nsidana ship $ */
3
4 --
5 -- Validate grade's gs date range is current or future
6 FUNCTION assp_val_gs_cur_fut(
7 p_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE ,
8 p_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE ,
9 p_message_name OUT NOCOPY varchar2 )
10 RETURN BOOLEAN IS
11 gv_other_detail VARCHAR2(255);
12 BEGIN
13 DECLARE
14 CURSOR c_gsg IS
15 SELECT start_dt,
16 end_dt
17 FROM IGS_AS_GRD_SCHEMA
18 WHERE grading_schema_cd = p_grading_schema_cd AND
19 version_number = p_version_number;
20 v_gsg_rec c_gsg%ROWTYPE;
21 v_fnc_return_value BOOLEAN;
22 BEGIN
23 -- Validate the IGS_AS_GRD_SCHEMA's date range is current or future
24 -- Set the default message number
25 p_message_name := null;
26 -- Cursor handling
27 OPEN c_gsg;
28 FETCH c_gsg INTO v_gsg_rec;
29 IF c_gsg%NOTFOUND THEN
30 CLOSE c_gsg;
31 RETURN TRUE;
32 END IF;
33 CLOSE c_gsg ;
34 -- Check if the grading schema is obsolete.
35 v_fnc_return_value := IGS_AS_VAL_GSG.GENP_VAL_DT_RANGE(v_gsg_rec.start_dt,
36 v_gsg_rec.end_dt,
37 p_message_name);
38 IF (NOT v_fnc_return_value) THEN
39 p_message_name := 'IGS_AS_GRD_SCHEMA_OBSLETE';
40 RETURN FALSE;
41 END IF;
42 -- Return the default value
43 RETURN TRUE;
44 END;
45 EXCEPTION
46 WHEN OTHERS THEN
47 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
48 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gs_cur_fut');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 END assp_val_gs_cur_fut;
52 --
53 -- Retrofitted
54 FUNCTION genp_val_dt_range(
55 p_start_dt IN DATE ,
56 p_end_dt IN DATE ,
57 p_message_name OUT NOCOPY varchar2 )
58 RETURN BOOLEAN IS
59 gv_other_detail VARCHAR2(255);
60 BEGIN
61 DECLARE
62 BEGIN
63 -- Validate the date range specified is current or future.
64 -- Set the default message number
65 p_message_name := null;
66 IF (p_end_dt IS NOT NULL) THEN
67 IF (p_start_dt <= TRUNC(SYSDATE) AND
68 p_end_dt < TRUNC(SYSDATE)) THEN
69 p_message_name := 'IGS_GE_INVALID_DATE_RANGE';
70 RETURN FALSE;
71 END IF;
72 END IF;
73 -- Return the default value
74 RETURN TRUE;
75 END;
76 EXCEPTION
77 WHEN OTHERS THEN
78 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
79 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.genp_val_dt_range');
80 IGS_GE_MSG_STACK.ADD;
81 App_Exception.Raise_Exception;
82 END genp_val_dt_range;
83 --
84 -- Validate upper mark range >= lower mark range and both set if one set
85 FUNCTION assp_val_gsg_mrk_rng(
86 p_lower_mark_range IN IGS_AS_GRD_SCH_GRADE.lower_mark_range%TYPE ,
87 p_upper_mark_range IN IGS_AS_GRD_SCH_GRADE.upper_mark_range%TYPE ,
88 p_message_name OUT NOCOPY varchar2 )
89 RETURN BOOLEAN IS
90 gv_other_detail VARCHAR2(255);
91 BEGIN
92 DECLARE
93 BEGIN
94 -- Validate the grade's mark range.
95 -- Set the default message number
96 p_message_name := null;
97 -- Lower mark is set if upper mark is set
98 IF (p_upper_mark_range IS NOT NULL AND
99 p_lower_mark_range IS NULL) THEN
100 p_message_name := 'IGS_AS_LOWER_MARKRANGE_SPEC';
101 RETURN FALSE;
102 END IF;
103 -- Upper mark is set if lower mark is set
104 IF (p_upper_mark_range IS NULL AND
105 p_lower_mark_range IS NOT NULL) THEN
106 p_message_name := 'IGS_AS_UPP_MARKRANGE_SPEC';
107 RETURN FALSE;
108 END IF;
109 -- Lower mark is greater than or equal to the lower mark range
110 IF (p_upper_mark_range IS NOT NULL) THEN
111 IF (p_upper_mark_range < p_lower_mark_range) THEN
112 p_message_name := 'IGS_AS_UPP_MARKRANGE_GE_LOWER';
113 RETURN FALSE;
114 END IF;
115 END IF;
116 --- Return the default value
117 RETURN TRUE;
118 END;
119 EXCEPTION
120 WHEN OTHERS THEN
121 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
122 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gsg_mrk_rng');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END assp_val_gsg_mrk_rng;
126 --
127 -- Validate max percentage >= min percentage
128 FUNCTION assp_val_gsg_min_max(
129 p_min_percentage IN IGS_AS_GRD_SCH_GRADE.min_percentage%TYPE ,
130 p_max_percentage IN IGS_AS_GRD_SCH_GRADE.max_percentage%TYPE ,
131 p_message_name OUT NOCOPY varchar2 )
132 RETURN BOOLEAN IS
133 gv_other_detail VARCHAR2(255);
134 BEGIN
135 DECLARE
136 BEGIN
137 -- Validate the maximum percentage is greater than the minimum percentage.
138 -- Set the default message number
139 p_message_name := null;
140 IF (p_max_percentage < p_min_percentage) THEN
141 p_message_name := 'IGS_AS_MAXPER_GE_MINPER';
142 RETURN FALSE;
143 END IF;
144 -- Return the default value
145 RETURN TRUE;
146 END;
147 EXCEPTION
148 WHEN OTHERS THEN
149 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
150 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gsg_min_max');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END assp_val_gsg_min_max;
154 --
155 -- Validate mark range does not overlap with other grades in GS version
156 FUNCTION assp_val_gsg_m_ovrlp(
157 p_grading_schema_cd IN IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE ,
158 p_version_number IN IGS_AS_GRD_SCH_GRADE.version_number%TYPE ,
159 p_grade IN IGS_AS_GRD_SCH_GRADE.grade%TYPE ,
160 p_lower_mark_range IN IGS_AS_GRD_SCH_GRADE.lower_mark_range%TYPE ,
161 p_upper_mark_range IN IGS_AS_GRD_SCH_GRADE.upper_mark_range%TYPE ,
162 p_message_name OUT NOCOPY varchar2 )
163 RETURN BOOLEAN IS
164 gv_other_detail VARCHAR2(255);
165 BEGIN
166 DECLARE
167 CURSOR c_gsg IS
168 SELECT lower_mark_range,
169 upper_mark_range
170 FROM IGS_AS_GRD_SCH_GRADE
171 WHERE grading_schema_cd = p_grading_schema_cd AND
172 version_number = p_version_number AND
173 grade <> p_grade AND
174 p_lower_mark_range IS NOT NULL AND
175 p_upper_mark_range IS NOT NULL;
176 v_gsg_rec c_gsg%ROWTYPE;
177 BEGIN
178 -- Validate that the mark range for the grade does not overlap with the mark
179 -- range for another grade within the same version of the grading schema.
180 -- Set the default message number
181 p_message_name := null;
182 FOR v_gsg_rec IN c_gsg LOOP
183 -- The lower mark range is within an existing range (inclusive).
184 IF (p_lower_mark_range >= v_gsg_rec.lower_mark_range AND
185 p_lower_mark_range <= v_gsg_rec.upper_mark_range) THEN
186 p_message_name := 'IGS_AS_LOWER_MARKRANGE_WITHIN';
187 RETURN FALSE;
188 END IF;
189 -- The upper mark range is within an existing range (inclusive).
190 IF (p_upper_mark_range >= v_gsg_rec.lower_mark_range AND
191 p_upper_mark_range <= v_gsg_rec.upper_mark_range) THEN
192 p_message_name := 'IGS_AS_UPP_MARKRANGE_WITHIN';
193 RETURN FALSE;
194 END IF;
195 -- The lower mark range is less than the lower mark range of an
196 -- existing range and the upper mark range is greater than the
197 -- upper mark range of an existing range.
198 IF (p_lower_mark_range < v_gsg_rec.lower_mark_range AND
199 p_upper_mark_range > v_gsg_rec.upper_mark_range) THEN
200 p_message_name := 'IGS_AS_MARKRANGE_ENCOMPASS';
201 RETURN FALSE;
202 END IF;
203 END LOOP;
204 RETURN TRUE;
205 END;
206 EXCEPTION
207 WHEN OTHERS THEN
208 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
209 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gsg_m_ovrlp');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception;
212 END assp_val_gsg_m_ovrlp;
213 --
214 -- Validate only 1 grade exists in a GS with the dflt outstanding ind set
215 FUNCTION assp_val_gsg_dflt(
216 p_grading_schema_cd IN IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE ,
217 p_version_number IN IGS_AS_GRD_SCH_GRADE.version_number%TYPE ,
218 p_grade IN IGS_AS_GRD_SCH_GRADE.grade%TYPE ,
219 p_message_name OUT NOCOPY varchar2 )
220 RETURN BOOLEAN IS
221 gv_other_detail VARCHAR2(255);
222 BEGIN
223 DECLARE
224 CURSOR c_gsg IS
225 SELECT COUNT(*)
226 FROM IGS_AS_GRD_SCH_GRADE
227 WHERE grading_schema_cd = p_grading_schema_cd AND
228 version_number = p_version_number AND
229 grade <> p_grade AND
230 dflt_outstanding_ind = 'Y';
231 v_gsg_count NUMBER;
232 BEGIN
233 -- Validate only one grade exists within a version of the grading schema
234 -- with the default outstanding indicator set.
235 -- Set the default message number
236 p_message_name := null;
237 -- Cursor handling
238 OPEN c_gsg ;
239 FETCH c_gsg INTO v_gsg_count;
240 IF c_gsg%NOTFOUND THEN
241 CLOSE c_gsg ;
242 RETURN TRUE;
243 END IF;
244 CLOSE c_gsg ;
245 IF (v_gsg_count > 0) THEN
246 p_message_name := 'IGS_AS_ONE_GRADE_VERSION_GRD';
247 RETURN FALSE;
248 END IF;
249 -- Return the default value
250 RETURN TRUE;
251 END;
252 EXCEPTION
253 WHEN OTHERS THEN
254 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
255 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gsg_dflt');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 END assp_val_gsg_dflt;
259 --
260 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
261 --
262 -- Process GSG rowids in a PL/SQL TABLE for the current commit.
263 --
264 -- Validate the result for a grade cannot be chngd when translat'ns exist
265 FUNCTION assp_val_gsg_gsgt(
266 p_grading_schema_cd IN VARCHAR2 ,
267 p_version_number IN NUMBER ,
268 p_grade IN VARCHAR2 ,
269 p_message_name OUT NOCOPY varchar2 )
270 RETURN BOOLEAN IS
271 gv_other_detail VARCHAR2(255);
272 BEGIN -- assp_val_gsg_gsgt
273 DECLARE
274 v_gsgt_exists VARCHAR2(1);
275 CURSOR c_gsgt IS
276 SELECT 'x'
277 FROM IGS_AS_GRD_SCH_TRN gsgt
278 WHERE gsgt.grading_schema_cd = p_grading_schema_cd AND
279 gsgt.version_number = p_version_number AND
280 gsgt.grade = p_grade;
281 CURSOR c_gsgt2 IS
282 SELECT 'x'
283 FROM IGS_AS_GRD_SCH_TRN gsgt
284 WHERE gsgt.to_grading_schema_cd = p_grading_schema_cd AND
285 gsgt.to_version_number = p_version_number AND
286 gsgt.to_grade = p_grade;
287 BEGIN
288 -- Set the default message number
289 p_message_name := null;
290 OPEN c_gsgt;
291 FETCH c_gsgt INTO v_gsgt_exists;
292 IF c_gsgt%FOUND THEN
293 CLOSE c_gsgt;
294 p_message_name :='IGS_AS_GRDRSLT_TYPE_MAP_FROM';
295 RETURN FALSE;
296 END IF;
297 CLOSE c_gsgt;
298 OPEN c_gsgt2;
299 FETCH c_gsgt2 INTO v_gsgt_exists;
300 IF c_gsgt2%FOUND THEN
301 CLOSE c_gsgt2;
302 p_message_name :='IGS_AS_GRDRSLT_TYPE_MAP_TO';
303 RETURN FALSE;
304 END IF;
305 CLOSE c_gsgt2;
306 -- Return the default value
307 RETURN TRUE;
308 EXCEPTION
309 WHEN OTHERS THEN
310 IF c_gsgt%ISOPEN THEN
311 CLOSE c_gsgt;
312 END IF;
313 IF c_gsgt2%ISOPEN THEN
314 CLOSE c_gsgt2;
315 END IF;
316 RAISE;
317 END;
318 EXCEPTION
319 WHEN OTHERS THEN
320 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
321 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gsg_gsgt');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324 END assp_val_gsg_gsgt;
325 --
326 -- Validate special grade type.
327 FUNCTION assp_val_gsg_ssgt(
328 p_s_special_grade_type IN VARCHAR2 ,
329 p_s_result_type IN VARCHAR2 ,
330 p_message_name OUT NOCOPY varchar2 )
331 RETURN BOOLEAN IS
332 gv_other_detail VARCHAR2(255);
333 BEGIN -- assp_val_gsg_ssgt
334 -- This module validates the special grade type. If the special grade
335 -- type is 'CONCEDED-PASS' then the s_result_type must be 'PASS'.
336 DECLARE
337 cst_conceded_pass CONSTANT
338 IGS_AS_GRD_SCH_GRADE.s_special_grade_type%TYPE := 'CONCEDED-PASS';
339 cst_pass CONSTANT IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE := 'PASS';
340 BEGIN
341 IF p_s_special_grade_type = cst_conceded_pass AND
342 p_s_result_type <> cst_pass THEN
343 p_message_name := 'IGS_AS_SPL_GRDTYPE_CONCEDED';
344 RETURN FALSE;
345 END IF;
346 p_message_name := null;
347 RETURN TRUE;
348 END;
349 EXCEPTION
350 WHEN OTHERS THEN
351 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
352 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GSG.assp_val_gsg_ssgt');
353 IGS_GE_MSG_STACK.ADD;
354 App_Exception.Raise_Exception;
355 END assp_val_gsg_ssgt;
356 END IGS_AS_VAL_GSG;
[Home] [Help]