1 PACKAGE BODY IGS_PS_VAL_POSU AS
2 /* $Header: IGSPS52B.pls 120.1 2005/11/16 02:13:04 appldev ship $ */
3 --
4 -- Validate pattern of study IGS_PS_UNIT record is unique.
5 FUNCTION crsp_val_posu_iu(
6 p_course_cd IN VARCHAR2 ,
7 p_version_number IN NUMBER ,
8 p_cal_type IN VARCHAR2 ,
9 p_pos_sequence_number IN NUMBER ,
10 p_posp_sequence_number IN NUMBER ,
11 p_sequence_number IN NUMBER ,
12 p_unit_cd IN VARCHAR2 ,
13 p_return_type OUT NOCOPY VARCHAR2 ,
14 p_message_name OUT NOCOPY VARCHAR2 ,
15 p_location_cd IN VARCHAR2,
16 p_unit_class IN VARCHAR2 )
17 RETURN BOOLEAN AS
18 BEGIN -- crsp_val_posu_iu
19 -- Validate IGS_PS_PAT_STUDY_UNT records.
20 -- Multiple records cannot exist with the same unit_cd for a parent
21 -- IGS_PS_PAT_OF_STUDY record.
22 -------------------------------------------------------------------
23 --Change History:
24 --Who When What
25 --sarakshi 16-NOV-2005 Bug#4726345, shifted the uniuqeness validation before the MUS validation.
26 -------------------------------------------------------------------
27 DECLARE
28 v_posp_sequence_number NUMBER;
29 cst_error VARCHAR2(1) := 'E';
30 cst_warning VARCHAR2(1) := 'W';
31 l_n_count NUMBER;
32
33 CURSOR cur_multiple_section IS
34 SELECT 'X'
35 FROM igs_ps_pat_study_unt psu,
36 igs_ps_unit_ver_all a,
37 igs_ps_unit_stat b
38 WHERE
39 psu.course_cd = p_course_cd AND
40 psu.version_number = p_version_number AND
41 psu.cal_type = p_cal_type AND
42 psu.pos_sequence_number = p_pos_sequence_number AND
43 psu.unit_cd = p_unit_cd AND
44 psu.unit_cd = a.unit_cd AND
45 a.same_teaching_period = 'Y' AND
46 ((a.expiry_dt IS NULL) OR (TRUNC(a.expiry_dt) >= TRUNC(SYSDATE))) AND
47 a.unit_status = b.unit_status AND
48 b.s_unit_status <> 'INACTIVE' AND
49 (p_sequence_number IS NULL OR psu.sequence_number <> p_sequence_number); --leave this record
50
51 CURSOR cur_check_multi_section IS
52 SELECT COUNT(*)
53 FROM igs_ps_pat_study_unt psu
54 WHERE
55 psu.course_cd = p_course_cd AND
56 psu.version_number = p_version_number AND
57 psu.cal_type = p_cal_type AND
58 psu.pos_sequence_number = p_pos_sequence_number AND
59 psu.unit_cd = p_unit_cd AND
60 (p_sequence_number IS NULL OR psu.sequence_number <> p_sequence_number); --leave this record
61
62
63 CURSOR c_pfsu1 IS
64 SELECT 'X'
65 FROM IGS_PS_PAT_STUDY_UNT
66 WHERE
67 course_cd = p_course_cd AND
68 version_number = p_version_number AND
69 cal_type = p_cal_type AND
70 pos_sequence_number = p_pos_sequence_number AND
71 posp_sequence_number = p_posp_sequence_number AND
72 NVL(unit_cd,'NULL') = NVL(p_unit_cd,'NULL') AND
73 NVL(unit_location_cd,'NULL') = NVL(p_location_cd,'NULL') AND
74 NVL(unit_class,'NULL') = NVL(p_unit_class,'NULL') AND
75 (p_sequence_number IS NULL OR
76 sequence_number <> p_sequence_number);
77 l_c_var VARCHAR2(1);
78
79 CURSOR c_pfsu IS
80 SELECT posp_sequence_number
81 FROM IGS_PS_PAT_STUDY_UNT
82 WHERE
83 course_cd = p_course_cd AND
84 version_number = p_version_number AND
85 cal_type = p_cal_type AND
86 pos_sequence_number = p_pos_sequence_number AND
87 NVL(unit_cd,'NULL') = NVL(p_unit_cd,'NULL') AND
88 NVL(unit_location_cd,'NULL') = NVL(p_location_cd,'NULL') AND
89 NVL(unit_class,'NULL') = NVL(p_unit_class,'NULL') AND
90 (p_sequence_number IS NULL OR
91 sequence_number <> p_sequence_number);
92
93 BEGIN
94 -- Set the default message number
95 p_message_name := NULL;
96 p_return_type := '';
97 -- 1. Check parameters:
98 IF p_course_cd IS NULL OR
99 p_version_number IS NULL OR
100 p_cal_type IS NULL OR
101 p_pos_sequence_number IS NULL OR
102 p_posp_sequence_number IS NULL OR
103 p_unit_cd IS NULL THEN
104 RETURN TRUE;
105 END IF;
106
107 ---added by sarakshi added as aprt of bug#4069211--
108
109 --Verify that the record is unique for a teaching calendar i.e. unit_cd,location_cd,unit_class
110 --cannot be same for a teaching class, Null values also cannot be same
111 OPEN c_pfsu1;
112 FETCH c_pfsu1 INTO l_c_var;
113 IF (c_pfsu1%FOUND) THEN
114 CLOSE c_pfsu1;
115 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
116 p_return_type := cst_error;
117 RETURN FALSE;
118 END IF;
119 CLOSE c_pfsu1;
120
121 -- For a pattern of study period multiple patten of study unit can exists with same unit code if multiple unit section checkbx
122 -- is checked at unit level.
123 OPEN cur_multiple_section;
124 FETCH cur_multiple_section INTO l_c_var;
125 IF cur_multiple_section%NOTFOUND THEN
126 CLOSE cur_multiple_section;
127 OPEN cur_check_multi_section;
128 FETCH cur_check_multi_section INTO l_n_count;
129 CLOSE cur_check_multi_section;
130 IF NVL(l_n_count,0) > 0 THEN
131 p_message_name := 'IGS_PS_NOT_MULTIPLE_USEC';
132 p_return_type := cst_error;
133 RETURN FALSE;
134 END IF;
135 ELSE
136 CLOSE cur_multiple_section;
137 END IF;
138
139
140 ---added by sarakshi --
141
142 -- 2. Check for records with the same unit_cd for
143 -- the parent IGS_PS_PAT_OF_STUDY record:
144
145 --Verify that if the record(unit code, location code and unit class) exists for some other teaching calendar for the academic period , then warn the user.
146 OPEN c_pfsu;
147 FETCH c_pfsu INTO v_posp_sequence_number;
148 IF (c_pfsu%FOUND) THEN
149 CLOSE c_pfsu;
150 p_message_name := 'IGS_PS_RECORD_ALREADY_EXISTS';
151 p_return_type := cst_warning;
152 RETURN FALSE;
153 END IF;
154 CLOSE c_pfsu;
155 RETURN TRUE;
156 EXCEPTION
157 WHEN OTHERS THEN
158 IF c_pfsu%ISOPEN THEN
159 CLOSE c_pfsu;
160 END IF;
161 App_Exception.Raise_Exception;
162 END;
163 EXCEPTION
164 WHEN OTHERS THEN
165 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
166 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POSu.crsp_val_posu_iu');
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception;
169 END crsp_val_posu_iu;
170 --
171 -- Validate the pattern of study IGS_PS_UNIT record has the required fields.
172 FUNCTION crsp_val_posu_rqrd(
173 p_unit_cd IN VARCHAR2 ,
174 p_unit_location_cd IN VARCHAR2 ,
175 p_unit_class IN VARCHAR2 ,
176 p_description IN VARCHAR2 ,
177 p_message_name OUT NOCOPY VARCHAR2 )
178 RETURN BOOLEAN AS
179 BEGIN -- crsp_val_posu_rqrd
180 -- Validate IGS_PS_PAT_STUDY_UNT records, either unit_cd or
181 -- description must be specified. The unit_location_cd and
182 -- IGS_AS_UNIT_CLASS can only be specified if the unit_cd is set.
183 DECLARE
184 BEGIN
185 -- 1. Check that one of either unit_cd or description is specified
186 IF (p_unit_cd IS NULL AND
187 p_description IS NULL) OR
188 (p_unit_cd IS NOT NULL AND
189 p_description IS NOT NULL)THEN
190 p_message_name := 'IGS_PS_UNITCD_OR_DESC_SPECIFY';
191 RETURN FALSE;
192 END IF;
193 -- 2. Check that if the unit_cd is not set that the unit_location_cd
194 -- and IGS_AS_UNIT_CLASS are not specified
195 IF (p_unit_cd IS NULL AND
196 (p_unit_location_cd IS NOT NULL OR
197 p_unit_class IS NOT NULL)) THEN
198 p_message_name := 'IGS_PS_UNITLOCCD_UNTCLASS_SPC';
199 RETURN FALSE;
200 END IF;
201 p_message_name := NULL;
202 RETURN TRUE;
203 EXCEPTION
204 WHEN OTHERS THEN
205 App_Exception.Raise_Exception;
206 END;
207 EXCEPTION
208 WHEN OTHERS THEN
209 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
210 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POSu.crsp_val_posu_rqrd');
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 END crsp_val_posu_rqrd;
214 --
215 -- Warn if no IGS_PS_UNIT offering option exists for the specified options.
216 FUNCTION crsp_val_posu_uoo(
217 p_course_cd IN VARCHAR2 ,
218 p_version_number IN NUMBER ,
219 p_cal_type IN VARCHAR2 ,
220 p_pos_sequence_number IN NUMBER ,
221 p_posp_sequence_number IN NUMBER ,
222 p_unit_cd IN VARCHAR2 ,
223 p_unit_location_cd IN VARCHAR2 ,
224 p_unit_class IN VARCHAR2 ,
225 p_message_name OUT NOCOPY VARCHAR2)
226 RETURN BOOLEAN AS
227 BEGIN
228 DECLARE
229 v_dummy VARCHAR2(1);
230 CURSOR c_posp_uoo_ci IS
231 SELECT 'X'
232 FROM IGS_PS_PAT_STUDY_PRD posp,
233 IGS_PS_UNIT_OFR_OPT uoo,
234 IGS_CA_INST ci
235 WHERE posp.course_cd = p_course_cd AND
236 posp.version_number = p_version_number AND
237 posp.cal_type = p_cal_type AND
238 posp.pos_sequence_number = p_pos_sequence_number AND
239 posp.sequence_number = p_posp_sequence_number AND
240 uoo.unit_cd = p_unit_cd AND
241 uoo.cal_type = posp.teach_cal_type AND
242 (p_unit_location_cd IS NULL OR
243 uoo.location_cd = p_unit_location_cd) AND
244 (p_unit_class IS NULL OR
245 uoo.unit_class = p_unit_class) AND
246 uoo.cal_type = ci.cal_type AND
247 uoo.ci_sequence_number = ci.sequence_number AND
248 ci.end_dt > SYSDATE;
249 BEGIN
250 -- 1. Check parameters
251 IF (p_course_cd IS NULL OR
252 p_version_number IS NULL OR
253 p_cal_type IS NULL OR
254 p_pos_sequence_number IS NULL OR
255 p_posp_sequence_number IS NULL OR
256 p_unit_cd IS NULL) THEN
257 p_message_name := NULL;
258 RETURN TRUE;
259 END IF;
260 -- 2. Check for future IGS_PS_UNIT_OFR_OPT records for the IGS_PS_COURSE offering
261 -- supplied for the IGS_PS_PAT_STUDY_UNT.unit_cd, location_cd, IGS_AS_UNIT_CLASS
262 -- and IGS_PS_PAT_STUDY_PRD.teach_cal_type
263 OPEN c_posp_uoo_ci;
264 FETCH c_posp_uoo_ci INTO v_dummy;
265 IF (c_posp_uoo_ci%NOTFOUND) THEN
266 CLOSE c_posp_uoo_ci;
267 p_message_name := 'IGS_PS_FUTURE_UOO_NOT_EXISTS';
268 RETURN FALSE;
269 END IF;
270 CLOSE c_posp_uoo_ci;
271 p_message_name := NULL;
272 RETURN TRUE;
273 EXCEPTION
274 WHEN OTHERS THEN
275 IF (c_posp_uoo_ci%ISOPEN) THEN
276 CLOSE c_posp_uoo_ci;
277 END IF;
278 App_Exception.Raise_Exception;
279 END;
280 EXCEPTION
281 WHEN OTHERS THEN
282 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
283 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POSu.crsp_val_posu_uoo');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286 END crsp_val_posu_uoo;
287 --
288 -- Validate a least one version of the IGS_PS_UNIT is active.
289 FUNCTION crsp_val_uv_active(
290 p_unit_cd IN IGS_PS_UNIT_VER_ALL.unit_cd%TYPE ,
291 p_message_name OUT NOCOPY VARCHAR2 )
292 RETURN BOOLEAN AS
293 -- who when What
294 --sarakshi 23-dec-2002 Bug#2689625,removed the exception section
295 BEGIN -- crsp_val_uv_active
296 -- Validate the IGS_PS_UNIT has at least one ACTIVE IGS_PS_UNIT_VER.
297 DECLARE
298 v_dummy VARCHAR2(1);
299 CURSOR c_chk_uv IS
300 SELECT 'X'
301 FROM IGS_PS_UNIT_VER uv,
302 IGS_PS_UNIT_STAT ust
303 WHERE uv.unit_cd = p_unit_cd AND
304 uv.unit_status = ust.unit_status AND
305 ust.s_unit_status = 'ACTIVE';
306 BEGIN
307 -- Set the default message number
308 p_message_name := NULL;
309 -- 1. Check parameters:
310 IF p_unit_cd IS NULL THEN
311 p_message_name := NULL;
312 RETURN TRUE;
313 END IF;
314 -- 2. Check if the unit_cd has an active IGS_PS_UNIT_VER:
315 OPEN c_chk_uv;
316 FETCH c_chk_uv INTO v_dummy;
317 -- 3. IF no active record is found return error:
318 IF (c_chk_uv%NOTFOUND) THEN
319 CLOSE c_chk_uv;
320 p_message_name := 'IGS_PS_UNITCD_NO_ACTIVE_UNITV';
321 RETURN FALSE;
322 END IF;
323 CLOSE c_chk_uv;
324 RETURN TRUE;
325 END;
326
327 END crsp_val_uv_active;
328
329 --
330 END IGS_PS_VAL_POSu;
[Home] [Help]