1 PACKAGE BODY IGS_PS_VAL_COw AS
2 /* $Header: IGSPS30B.pls 115.4 2002/11/29 03:01:09 nsidana ship $ */
3
4 --
5 -- Validate IGS_PS_COURSE ownership percentage for the IGS_PS_COURSE version.
6 FUNCTION crsp_val_cow_perc(
7 p_course_cd IN VARCHAR2 ,
8 p_version_number IN NUMBER ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN AS
11 -- gv_percent NUMBER;
12 gv_course_ownership CHAR;
13 gv_course_status IGS_PS_STAT.s_course_status%TYPE;
14 CURSOR gc_course_status IS
15 SELECT CS.s_course_status
16 FROM IGS_PS_VER CV,
17 IGS_PS_STAT CS
18 WHERE CV.course_cd = p_course_cd AND
19 CV.version_number = p_version_number AND
20 CV.course_status = CS.course_status;
21 CURSOR gc_course_ownership_exists IS
22 SELECT 'x'
23 FROM IGS_PS_OWN
24 WHERE course_cd = p_course_cd AND
25 version_number = p_version_number;
26 gv_percent IGS_PS_OWN.PERCENTAGE%TYPE;
27
28 CURSOR cur_user IS
29 SELECT SUM(percentage)
30 FROM IGS_PS_OWN
31 WHERE course_cd = p_course_cd AND
32 version_number = p_version_number;
33
34
35 BEGIN
36 -- finding the s_course_status
37 OPEN gc_course_status;
38 FETCH gc_course_status INTO gv_course_status;
39 -- finding IGS_PS_OWN records
40 OPEN gc_course_ownership_exists;
41 FETCH gc_course_ownership_exists INTO gv_course_ownership;
42 -- Find the sum of all percentages
43
44 OPEN cur_user;
45 FETCH cur_user INTO gv_percent;
46 IF cur_user%NOTFOUND THEN
47 RAISE no_data_found ;
48 END IF;
49 CLOSE cur_user ;
50
51 -- when the percentage totals 100
52 IF gv_percent = 100.00 THEN
53 CLOSE gc_course_status;
54 CLOSE gc_course_ownership_exists;
55 p_message_name := NULL;
56 RETURN TRUE;
57 ELSE
58 -- when the percentage doesn't total 100 and
59 -- when the IGS_PS_STAT.s_unit_status is PLANNED
60 -- and no IGS_PS_OWN records exist
61 IF (gv_course_status = 'PLANNED' AND gc_course_ownership_exists%NOTFOUND) THEN
62 CLOSE gc_course_status;
63 CLOSE gc_course_ownership_exists;
64 p_message_name := NULL;
65 RETURN TRUE;
66 ELSE
67 -- when the percentage doesn't total 100 and
68 -- when the IGS_PS_STAT.s_unit_status is not PLANNED
69 -- or IGS_PS_OWN records exist
70 CLOSE gc_course_status;
71 CLOSE gc_course_ownership_exists;
72 p_message_name := 'IGS_PS_PRCALLOC_PRGOWN_100';
73 RETURN FALSE;
74 END IF;
75 END IF;
76 EXCEPTION
77 WHEN OTHERS THEN
78 IF cur_user%ISOPEN THEN
79 CLOSE cur_user;
80 END IF;
81 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
82 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_COw.crsp_val_cow_perc');
83 IGS_GE_MSG_STACK.ADD;
84 APP_EXCEPTION.RAISE_EXCEPTION;
85 END crsp_val_cow_perc;
86
87 END IGS_PS_VAL_COw;
[Home] [Help]