1 PACKAGE BODY IGS_PS_VAL_POS AS
2 /* $Header: IGSPS50B.pls 115.7 2003/02/26 05:50:34 sarakshi ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --sarakshi 23-Feb-2003 Enh#2797116,modified cursor c_coo in the function crsp_val_pos_coo
7 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_am_closed"
8 -- avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_att_closed"
9 -- avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_crs_ci"
10 --smaddali 21-oct-2001 modified crsp_val_pos_iu procedure for bug#1838421 .
11 -- changed the select statement .it was selecting attendance_mode twice instead of attendance_type ,
12 -- so changed the second attendance_mode to attendance_type .Also changed the message name
13 -- to IGS_PS_PAT_MANDATORY for the corresponding cursor code
14 -------------------------------------------------------------------------------------------
15
16 --
17 -- Validate the admission category is not closed.
18 FUNCTION crsp_val_ac_closed(
19 p_admission_cat IN IGS_AD_CAT.admission_cat%TYPE ,
20 p_message_name OUT NOCOPY VARCHAR2 )
21 RETURN BOOLEAN AS
22 BEGIN -- crsp_val_ac_closed
23 -- Validate if IGS_AD_CAT.IGS_AD_CAT is closed
24 DECLARE
25 v_dummy VARCHAR2(1);
26 CURSOR c_ac IS
27 SELECT 'X'
28 FROM IGS_AD_CAT ac
29 WHERE ac.admission_cat = p_admission_cat AND
30 ac.closed_ind = 'Y';
31 BEGIN
32 IF p_admission_cat IS NOT NULL THEN
33 OPEN c_ac;
34 FETCH c_ac INTO v_dummy;
35 IF (c_ac%FOUND) THEN
36 CLOSE c_ac;
37 p_message_name := 'IGS_AD_ADM_CATEGORY_CLOSED';
38 RETURN FALSE;
39 END IF;
40 CLOSE c_ac;
41 END IF;
42 p_message_name := NULL;
43 RETURN TRUE;
44 EXCEPTION
45 WHEN OTHERS THEN
46 IF (c_ac%ISOPEN) THEN
47 CLOSE c_ac;
48 END IF;
49 App_Exception.Raise_Exception;
50 END;
51 EXCEPTION
52 WHEN OTHERS THEN
53 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
54 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POS.CRSP_VAL_AC_CLOSED');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57 END crsp_val_ac_closed;
58 --
59
60 -- Validate the calendar type is categorised admission and is not closed.
61 FUNCTION crsp_val_pos_cat(
62 p_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
63 p_message_name OUT NOCOPY VARCHAR2 )
64 RETURN BOOLEAN AS
65 BEGIN -- crsp_val_pos_cat
66 -- Validate the IGS_CA_TYPE is not closed and the SI_CA_S_CA_CAT is set to ADMISSION.
67 DECLARE
68 v_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
69 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
70 CURSOR c_cat IS
71 SELECT closed_ind,
72 s_cal_cat
73 FROM IGS_CA_TYPE
74 WHERE cal_type = p_cal_type;
75 BEGIN
76 -- check parameters
77 IF p_cal_type IS NULL THEN
78 p_message_name := NULL;
79 RETURN TRUE;
80 END IF;
81 -- get the closed indicator and calender category for this cal type
82 OPEN c_cat;
83 FETCH c_cat INTO v_closed_ind,
84 v_s_cal_cat;
85 -- if no records found
86 IF (c_cat%NOTFOUND) THEN
87 CLOSE c_cat;
88 p_message_name := NULL;
89 RETURN TRUE;
90 END IF;
91 CLOSE c_cat;
92 --check if IGS_CA_TYPE is closed
93 IF v_closed_ind = 'Y' THEN
94 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
95 RETURN FALSE;
96 END IF;
97 --check if the IGS_CA_TYPE is of category 'ADMISSION'
98 IF v_s_cal_cat <> 'ADMISSION' THEN
99 p_message_name := 'IGS_PS_CALTYPE_ADMISSION_CAL';
100 RETURN FALSE;
101 END IF;
102 -- validated IGS_CA_TYPE is not closed and SI_CA_S_CA_CAT set to ADMISSION
103 p_message_name := NULL;
104 RETURN TRUE;
105 EXCEPTION
106 WHEN OTHERS THEN
107 IF (c_cat%ISOPEN) THEN
108 CLOSE c_cat;
109 END IF;
110 App_Exception.Raise_Exception;
111 END;
112 EXCEPTION
113 WHEN OTHERS THEN
114 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
115 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POS.CRSP_VAL_POS_CAT');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 END crsp_val_pos_cat;
119 --
120 -- Warn if no IGS_PS_COURSE offering exists for the specified options.
121 FUNCTION crsp_val_pos_coo(
122 p_course_cd IN VARCHAR2 ,
123 p_version_number IN NUMBER ,
124 p_cal_type IN VARCHAR2 ,
125 p_location_cd IN VARCHAR2 ,
126 p_attendance_mode IN VARCHAR2 ,
127 p_attendance_type IN VARCHAR2 ,
128 p_message_name OUT NOCOPY VARCHAR2 )
129 RETURN BOOLEAN AS
130 BEGIN -- crsp_val_pos_coo
131 -- Warn the user if location_cd, IGS_EN_ATD_MODE or IGS_EN_ATD_TYPE are set
132 -- that a IGS_PS_OFR_OPT record exists for the specified option.
133 -- Warning only.
134 DECLARE
135 v_dummy VARCHAR2(1);
136 CURSOR c_coo IS
137 SELECT 'x'
138 FROM IGS_PS_OFR_OPT
139 WHERE course_cd = p_course_cd AND
140 version_number = p_version_number AND
141 cal_type = p_cal_type AND
142 (p_location_cd IS NULL OR
143 location_cd = p_location_cd) AND
144 (p_attendance_mode IS NULL OR
145 attendance_mode = p_attendance_mode) AND
146 (p_attendance_type IS NULL OR
147 attendance_type = p_attendance_type) AND
148 delete_flag = 'N';
149 BEGIN
150 -- check parameters
151 IF p_course_cd IS NULL OR
152 p_version_number IS NULL OR
153 p_cal_type IS NULL THEN
154 p_message_name := NULL;
155 RETURN TRUE;
156 END IF;
157 -- Check for IGS_PS_OFR_OPT records for the IGS_PS_COURSE offering supplied
158 -- and for IGS_EN_UNIT_SET versions with the uni_set_cd supplied which have a
159 -- IGS_EN_UNIT_SET_STAT.s_unit_set_status of ACTIVE:
160 OPEN c_coo;
161 FETCH c_coo INTO v_dummy;
162 IF (c_coo%NOTFOUND) THEN
163 -- no record found
164 CLOSE c_coo;
165 p_message_name := 'IGS_PS_POO_DOES_NOT_EXIST';
166 RETURN FALSE;
167 END IF;
168 -- record found
169 CLOSE c_coo;
170 p_message_name := NULL;
171 RETURN TRUE;
172 EXCEPTION
173 WHEN OTHERS THEN
174 IF (c_coo%ISOPEN) THEN
175 CLOSE c_coo;
176 END IF;
177 App_Exception.Raise_Exception;
178 END;
179 EXCEPTION
180 WHEN OTHERS THEN
181 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
182 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POS.CRSP_VAL_POS_COO');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END crsp_val_pos_coo;
186 --
187 -- Warn if no IGS_PS_COURSE offering IGS_PS_UNIT set record exists.
188 FUNCTION crsp_val_pos_cous(
189 p_course_cd IN VARCHAR2 ,
190 p_crv_version_number IN NUMBER ,
191 p_cal_type IN VARCHAR2 ,
192 p_unit_set_cd IN VARCHAR2 ,
193 p_message_name OUT NOCOPY VARCHAR2 )
194 RETURN BOOLEAN AS
195 BEGIN -- crsp_val_pos_cous
196 -- Warn the user if the IGS_EN_UNIT_SET is not linked to the IGS_PS_OFR.
197 -- Warning only.
198 DECLARE
199 cst_active CONSTANT IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE := 'ACTIVE';
200 v_dummy VARCHAR(1);
201 CURSOR c_cous_us_uss IS
202 SELECT 'X'
203 FROM IGS_PS_OFR_UNIT_SET cous,
204 IGS_EN_UNIT_SET us,
205 IGS_EN_UNIT_SET_STAT uss
206 WHERE cous.course_cd = p_course_cd AND
207 cous.crv_version_number = p_crv_version_number AND
208 cous.cal_type = p_cal_type AND
209 cous.unit_set_cd = p_unit_set_cd AND
210 cous.unit_set_cd = us.unit_set_cd AND
211 cous.us_version_number = us.version_number AND
212 us.unit_set_status = uss.unit_set_status AND
213 uss.s_unit_set_status = cst_active;
214 BEGIN
215 IF p_course_cd IS NULL OR
216 p_crv_version_number IS NULL OR
217 p_cal_type IS NULL OR
218 p_unit_set_cd IS NULL THEN
219 p_message_name := NULL;
220 RETURN TRUE;
221 ELSE
222 OPEN c_cous_us_uss;
223 FETCH c_cous_us_uss INTO v_dummy;
224 IF (c_cous_us_uss%NOTFOUND) THEN
225 CLOSE c_cous_us_uss;
226 p_message_name := 'IGS_PS_UNITSET_NOT_LINK_PRG';
227 RETURN FALSE;
228 END IF;
229 CLOSE c_cous_us_uss;
230 END IF;
231 p_message_name := NULL;
232 RETURN TRUE;
233 EXCEPTION
234 WHEN OTHERS THEN
235 IF (c_cous_us_uss%ISOPEN) THEN
236 CLOSE c_cous_us_uss;
237 END IF;
238 App_Exception.Raise_Exception;
239 END;
240 EXCEPTION
241 WHEN OTHERS THEN
242 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
243 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POS.CRSP_VAL_POS_COUS');
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END crsp_val_pos_cous;
247 --
248 -- Validate pattern of study record is not ambiguous.
249 FUNCTION crsp_val_pos_iu(
250 p_course_cd IN IGS_PS_PAT_OF_STUDY.course_cd%TYPE ,
251 p_version_number IN IGS_PS_PAT_OF_STUDY.version_number%TYPE ,
252 p_cal_type IN IGS_PS_PAT_OF_STUDY.cal_type%TYPE ,
253 p_sequence_number IN IGS_PS_PAT_OF_STUDY.sequence_number%TYPE ,
254 p_location_cd IN IGS_PS_PAT_OF_STUDY.location_cd%TYPE ,
255 p_attendance_mode IN IGS_PS_PAT_OF_STUDY.attendance_mode%TYPE ,
256 p_attendance_type IN IGS_PS_PAT_OF_STUDY.attendance_type%TYPE ,
257 p_unit_set_cd IN IGS_PS_PAT_OF_STUDY.unit_set_cd%TYPE ,
258 p_admission_cal_type IN IGS_PS_PAT_OF_STUDY.admission_cal_type%TYPE ,
259 p_admission_cat IN IGS_PS_PAT_OF_STUDY.admission_cat%TYPE ,
260 p_message_name OUT NOCOPY VARCHAR2 )
261 RETURN BOOLEAN AS
262 BEGIN -- crsp_val_pos_iu
263 -- Validate IGS_PS_PAT_OF_STUDY records are unique for the IGS_PS_OFR
264 -- specified. Once a IGS_AD_LOCATION code, attendance mode, attendance type,
265 -- IGS_PS_UNIT set code, admission calendar type or admission category is set
266 -- for one IGS_PS_PAT_OF_STUDY record for a IGS_PS_OFR it must be set
267 -- for all records. This excludes the single default IGS_PS_PAT_OF_STUDY
268 -- record which is allowed to exist for each IGS_PS_OFR where IGS_AD_LOCATION
269 -- code, attendance mode, attendance type, IGS_PS_UNIT set code, admission calendar
270 -- type and admission category are all not set.
271 DECLARE
272 v_dummy VARCHAR2(255);
273 v_ret_false_flg BOOLEAN;
274 v_location_cd IGS_PS_PAT_OF_STUDY.location_cd%TYPE;
275 v_attendance_mode IGS_PS_PAT_OF_STUDY.attendance_mode%TYPE;
276 v_attendance_type IGS_PS_PAT_OF_STUDY.attendance_type%TYPE;
277 v_unit_set_cd IGS_PS_PAT_OF_STUDY.unit_set_cd%TYPE;
278 v_admission_cal_type IGS_PS_PAT_OF_STUDY.admission_cal_type%TYPE;
279 v_admission_cat IGS_PS_PAT_OF_STUDY.admission_cat%TYPE;
280 CURSOR c_pos IS
281 SELECT 'X'
282 FROM IGS_PS_PAT_OF_STUDY pos
283 WHERE pos.course_cd = p_course_cd AND
284 pos.version_number = p_version_number AND
285 pos.cal_type = p_cal_type AND
286 pos.sequence_number <> p_sequence_number AND
287 pos.location_cd IS NULL AND
288 pos.attendance_mode IS NULL AND
289 pos.attendance_type IS NULL AND
290 pos.unit_set_cd IS NULL AND
291 pos.admission_cal_type IS NULL AND
292 pos.admission_cat IS NULL;
293
294 CURSOR c_pos2 IS
295 SELECT 'X'
296 FROM IGS_PS_PAT_OF_STUDY pos
297 WHERE pos.course_cd = p_course_cd AND
298 pos.version_number = p_version_number AND
299 pos.cal_type = p_cal_type AND
300 pos.sequence_number <> p_sequence_number AND
301 ((p_location_cd IS NULL AND
302 pos.location_cd IS NULL) OR
303 pos.location_cd = p_location_cd) AND
304 ((p_attendance_mode IS NULL AND
305 pos.attendance_mode IS NULL) OR
306 pos.attendance_mode = p_attendance_mode) AND
307 ((p_attendance_type IS NULL AND
308 pos.attendance_type IS NULL) OR
309 pos.attendance_type = p_attendance_type) AND
310 ((p_unit_set_cd IS NULL AND
311 pos.unit_set_cd IS NULL) OR
312 pos.unit_set_cd = p_unit_set_cd) AND
313 ((p_admission_cal_type IS NULL AND
314 pos.admission_cal_type IS NULL) OR
315 pos.admission_cal_type = p_admission_cal_type) AND
316 ((p_admission_cat IS NULL AND
317 pos.admission_cat IS NULL) OR
318 pos.admission_cat = p_admission_cat);
319 --smaddali changed the select statement for bug#1838421 . it was selecting attendance_mode twice instead of
320 -- attendance_type , so changed the second attendance_mode to attendance_type
321 CURSOR c_pos3 IS
322 SELECT pos.location_cd,
323 pos.attendance_mode,
324 pos.attendance_type,
325 pos.unit_set_cd,
326 pos.admission_cal_type,
327 pos.admission_cat
328 FROM IGS_PS_PAT_OF_STUDY pos
329 WHERE pos.course_cd = p_course_cd AND
330 pos.version_number = p_version_number AND
331 pos.cal_type = p_cal_type AND
332 pos.sequence_number <> p_sequence_number AND
333 (pos.location_cd IS NOT NULL OR
334 pos.attendance_mode IS NOT NULL OR
335 pos.attendance_type IS NOT NULL OR
336 pos.unit_set_cd IS NOT NULL OR
337 pos.admission_cal_type IS NOT NULL OR
338 pos.admission_cat IS NOT NULL);
339 BEGIN
340 -- 1. Check parameters
341 IF (p_course_cd IS NULL OR
342 p_version_number IS NULL OR
343 p_cal_type IS NULL OR
344 p_sequence_number IS NULL) THEN
345 p_message_name := NULL;
346 RETURN TRUE;
347 END IF;
348 -- 2. If all of the remaining parameters are null check that a
349 -- default (all null) record does not already exist
350 IF (p_location_cd IS NULL AND
351 p_attendance_mode IS NULL AND
352 p_attendance_type IS NULL AND
353 p_unit_set_cd IS NULL AND
354 p_admission_cal_type IS NULL AND
355 p_admission_cat IS NULL) THEN
356 OPEN c_pos;
357 FETCH c_pos INTO v_dummy;
358 IF (c_pos%FOUND) THEN
359 CLOSE c_pos;
360 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
361 RETURN FALSE;
362 END IF;
363 CLOSE c_pos;
364 p_message_name := NULL;
365 RETURN TRUE;
366 END IF;
367 -- 3. Check that this record is not the same as an existing record
368 OPEN c_pos2;
369 FETCH c_pos2 INTO v_dummy;
370 IF (c_pos2%FOUND) THEN
371 CLOSE c_pos2;
372 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
373 RETURN FALSE;
374 END IF;
375 CLOSE c_pos2;
376 -- 4. Check that if location_cd is set ,that other records have it set
377 -- (except the default all null record) or that if the location_cd
378 -- is not set that all other records do not have it set.
379 OPEN c_pos3;
380 v_ret_false_flg := FALSE;
381 LOOP
382 FETCH c_pos3 INTO v_location_cd,
383 v_attendance_mode,
384 v_attendance_type,
385 v_unit_set_cd,
386 v_admission_cal_type,
387 v_admission_cat;
388 IF (c_pos3%NOTFOUND) THEN
389 CLOSE c_pos3;
390 EXIT;
391 END IF;
392 -- 4.1 Check IGS_AD_LOCATION code is set or unset for all pattern
393 -- of study records for this IGS_PS_COURSE offering.
394 --smaddali for bug#1838421 ,changed the message name from IGS_GE_RECORD_ALREADY_EXISTS
395 -- to IGS_PS_PAT_MANDATORY in all the following IF cases
396 IF (p_location_cd IS NULL AND
397 v_location_cd IS NOT NULL) THEN
398 p_message_name := 'IGS_PS_PAT_MANDATORY';
399 v_ret_false_flg := TRUE;
400 EXIT;
401 END IF;
402 IF (p_location_cd IS NOT NULL AND
403 v_location_cd IS NULL) THEN
404 p_message_name := 'IGS_PS_PAT_MANDATORY';
405 v_ret_false_flg := TRUE;
406 EXIT;
407 END IF;
408 -- 4.2 Check attendance mode is set or unset for all pattern
409 -- of study records for this IGS_PS_COURSE offering.
410 IF (p_attendance_mode IS NULL AND
411 v_attendance_mode IS NOT NULL) THEN
412 p_message_name := 'IGS_PS_PAT_MANDATORY';
413 v_ret_false_flg := TRUE;
414 EXIT;
415 END IF;
416 IF (p_attendance_mode IS NOT NULL AND
417 v_attendance_mode IS NULL) THEN
418 p_message_name := 'IGS_PS_PAT_MANDATORY';
419 v_ret_false_flg := TRUE;
420 EXIT;
421 END IF;
422 -- 4.3 Check attendance type is set or unset for all pattern
423 -- of study records for this IGS_PS_COURSE offering.
424 IF (p_attendance_type IS NULL AND
425 v_attendance_type IS NOT NULL) THEN
426 p_message_name := 'IGS_PS_PAT_MANDATORY';
427 v_ret_false_flg := TRUE;
428 EXIT;
429 END IF;
430 IF (p_attendance_type IS NOT NULL AND
431 v_attendance_type IS NULL) THEN
432 p_message_name := 'IGS_PS_PAT_MANDATORY' ;
433 v_ret_false_flg := TRUE;
434 EXIT;
435 END IF;
436 -- 4.4 Check IGS_PS_UNIT set code is set or unset for all pattern
437 -- of study records for this IGS_PS_COURSE offering.
438 IF (p_unit_set_cd IS NULL AND
439 v_unit_set_cd IS NOT NULL) THEN
440 p_message_name := 'IGS_PS_PAT_MANDATORY';
441 v_ret_false_flg := TRUE;
442 EXIT;
443 END IF;
444 IF (p_unit_set_cd IS NOT NULL AND
445 v_unit_set_cd IS NULL) THEN
446 p_message_name := 'IGS_PS_PAT_MANDATORY' ;
447 v_ret_false_flg := TRUE;
448 EXIT;
449 END IF;
450 -- 4.5 Check admission calendar type is set or unset for all
451 -- pattern of study records for this IGS_PS_COURSE offering.
452 IF (p_admission_cal_type IS NULL AND
453 v_admission_cal_type IS NOT NULL) THEN
454 p_message_name := 'IGS_PS_PAT_MANDATORY' ;
455 v_ret_false_flg := TRUE;
456 EXIT;
457 END IF;
458 IF (p_admission_cal_type IS NOT NULL AND
459 v_admission_cal_type IS NULL) THEN
460 p_message_name := 'IGS_PS_PAT_MANDATORY' ;
461 v_ret_false_flg := TRUE;
462 EXIT;
463 END IF;
464 -- 4.6 Check admission category is set or unset for all
465 -- pattern of study records for this IGS_PS_COURSE offering.
466 IF (p_admission_cat IS NULL AND
467 v_admission_cat IS NOT NULL) THEN
468 p_message_name := 'IGS_PS_PAT_MANDATORY' ;
469 v_ret_false_flg := TRUE;
470 EXIT;
471 END IF;
472 IF (p_admission_cat IS NOT NULL AND
473 v_admission_cat IS NULL) THEN
474 p_message_name := 'IGS_PS_PAT_MANDATORY' ;
475 v_ret_false_flg := TRUE;
476 EXIT;
477 END IF;
478 END LOOP;
479 IF (v_ret_false_flg) THEN
480 RETURN FALSE;
481 END IF;
482 p_message_name := NULL;
483 RETURN TRUE;
484 EXCEPTION
485 WHEN OTHERS THEN
486 IF (c_pos%ISOPEN) THEN
487 CLOSE c_pos;
488 END IF;
489 IF (c_pos2%ISOPEN) THEN
490 CLOSE c_pos2;
491 END IF;
492 IF (c_pos3%ISOPEN) THEN
493 CLOSE c_pos3;
494 END IF;
495 App_Exception.Raise_Exception;
496 END;
497 EXCEPTION
498 WHEN OTHERS THEN
499 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
500 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POS.CRSP_VAL_POS_IU');
501 IGS_GE_MSG_STACK.ADD;
502 App_Exception.Raise_Exception;
503 END crsp_val_pos_iu;
504 --
505 -- Validate a least one version of the IGS_PS_UNIT set is active.
506 FUNCTION crsp_val_us_active(
507 p_unit_set_cd IN IGS_EN_UNIT_SET_ALL.unit_set_cd%TYPE ,
508 p_message_name OUT NOCOPY VARCHAR2 )
509 RETURN BOOLEAN AS
510 BEGIN -- crsp_val_us_active
511 -- Validate the IGS_EN_UNIT_SET contains at least one version which is ACTIVE.
512 DECLARE
513 v_dummy VARCHAR2(1);
514 CURSOR c_us_uss IS
515 SELECT 'X'
516 FROM IGS_EN_UNIT_SET us,
517 IGS_EN_UNIT_SET_STAT uss
518 WHERE us.unit_set_cd = p_unit_set_cd AND
519 us.unit_set_status = uss.unit_set_status AND
520 uss.s_unit_set_status = 'ACTIVE';
521 BEGIN
522 -- Check parameters:
523 IF p_unit_set_cd IS NULL THEN
524 p_message_name := NULL;
525 RETURN TRUE;
526 END IF;
527 -- Check if the IGS_EN_UNIT_SET contains an active version:
528 OPEN c_us_uss;
529 FETCH c_us_uss INTO v_dummy;
530 --If no active record is found return error:
531 IF (c_us_uss%NOTFOUND) THEN
532 CLOSE c_us_uss;
533 p_message_name := 'IGS_PS_UNITSET_NO_ACTIVEVER';
534 RETURN FALSE;
535 END IF;
536 -- record is found
537 CLOSE c_us_uss;
538 p_message_name := NULL;
539 RETURN TRUE;
540 EXCEPTION
541 WHEN OTHERS THEN
542 IF (c_us_uss%ISOPEN) THEN
543 CLOSE c_us_uss;
544 END IF;
545 App_Exception.Raise_Exception;
546 END;
547 EXCEPTION
548 WHEN OTHERS THEN
549 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
550 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POS.CRSP_VAL_US_ACTIVE');
551 IGS_GE_MSG_STACK.ADD;
552 App_Exception.Raise_Exception;
553 END crsp_val_us_active;
554 END IGS_PS_VAL_POS;
[Home] [Help]