1 PACKAGE BODY IGS_PS_VAL_CAO AS
2 /* $Header: IGSPS16B.pls 115.4 2002/11/29 02:57:15 nsidana ship $ */
3
4 --
5 -- Validate if IGS_PS_COURSE IGS_PS_AWD ownership records exist for a IGS_PS_COURSE IGS_PS_AWD.
6 FUNCTION crsp_val_cao_exists(
7 p_course_cd IN VARCHAR2 ,
8 p_version_number IN NUMBER ,
9 p_award_cd IN VARCHAR2 ,
10 p_message_name OUT NOCOPY VARCHAR2 )
11 RETURN BOOLEAN AS
12 v_check CHAR;
13 CURSOR c_sel_course_award_ownership IS
14 SELECT 'x'
15 FROM IGS_PS_AWD_OWN
16 WHERE course_cd = p_course_cd AND
17 version_number = p_version_number AND
18 award_cd = p_award_cd;
19 BEGIN
20 OPEN c_sel_course_award_ownership;
21 FETCH c_sel_course_award_ownership INTO v_check;
22 -- validate if IGS_PS_COURSE IGS_PS_AWD ownership records exist
23 IF (c_sel_course_award_ownership%NOTFOUND) THEN
24 CLOSE c_sel_course_award_ownership;
25 p_message_name := 'IGS_PS_PRGAWARD_OWNERSHIP';
26 RETURN FALSE;
27 END IF;
28 CLOSE c_sel_course_award_ownership;
29 p_message_name := NULL;
30 RETURN TRUE;
31 EXCEPTION
32 WHEN OTHERS THEN
33 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
34 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CAO.crsp_val_cao_exists');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 END crsp_val_cao_exists;
38 --
39 -- Validate IGS_PS_COURSE IGS_PS_AWD ownership % for the IGS_PS_COURSE version IGS_PS_AWD.
40 FUNCTION crsp_val_cao_perc(
41 p_course_cd IN VARCHAR2 ,
42 p_version_number IN NUMBER ,
43 p_award_cd IN VARCHAR2 ,
44 p_message_name OUT NOCOPY VARCHAR2)
45 RETURN BOOLEAN AS
46 gv_course_award_own CHAR;
47 gv_course_status IGS_PS_STAT.s_course_status%TYPE;
48 CURSOR gc_course_status IS
49 SELECT CS.s_course_status
50 FROM IGS_PS_VER CV,
51 IGS_PS_STAT CS
52 WHERE CV.course_cd = p_course_cd AND
53 CV.version_number = p_version_number AND
54 CV.course_status = CS.course_status;
55 CURSOR gc_course_award_own_exists IS
56 SELECT 'x'
57 FROM IGS_PS_AWD_OWN
58 WHERE course_cd = p_course_cd AND
59 version_number = p_version_number AND
60 award_cd = p_award_cd;
61 gv_percent IGS_PS_AWD_OWN.percentage%type;
62 CURSOR gc_percent IS
63 SELECT SUM(percentage)
64 FROM IGS_PS_AWD_OWN
65 WHERE course_cd = p_course_cd AND
66 version_number = p_version_number AND
67 award_cd = p_award_cd;
68 BEGIN
69 -- finding the s_course_status
70 OPEN gc_course_status;
71 FETCH gc_course_status INTO gv_course_status;
72 -- finding IGS_PS_AWD_OWN records
73 OPEN gc_course_award_own_exists;
74 FETCH gc_course_award_own_exists INTO gv_course_award_own;
75 -- Find the sum of all percentages
76 OPEN gc_percent;
77 FETCH gc_percent INTO gv_percent;
78 IF gc_percent%NOTFOUND THEN
79 RAISE no_data_found;
80 END IF;
81 CLOSE gc_percent;
82 -- when the percentage totals 100
83 IF gv_percent = 100.0 THEN
84 CLOSE gc_course_status;
85 CLOSE gc_course_award_own_exists;
86 p_message_name := NULL;
87 RETURN TRUE;
88 ELSE
89 -- when the percentage doesn't total 100 and
90 -- when the IGS_PS_STAT.s_unit_status is PLANNED
91 -- and no IGS_PS_AWD_OWN records exist
92 IF (gv_course_status = 'PLANNED' AND gc_course_award_own_exists%NOTFOUND) THEN
93 CLOSE gc_course_status;
94 CLOSE gc_course_award_own_exists;
95 p_message_name := NULL;
96 RETURN TRUE;
97 ELSE
98 -- when the percentage doesn't total 100 and
99 -- when the IGS_PS_STAT.s_unit_status is not PLANNED
100 -- or IGS_PS_AWD_OWN records exist
101 CLOSE gc_course_status;
102 CLOSE gc_course_award_own_exists;
103 p_message_name := 'IGS_PS_PRCALLOC_PRGAWARD_100';
104 RETURN FALSE;
105 END IF;
106 END IF;
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 IF gc_percent%ISOPEN THEN
110 CLOSE gc_percent;
111 App_Exception.Raise_Exception;
112 END IF;
113 WHEN OTHERS THEN
114 IF gc_percent%ISOPEN THEN
115 CLOSE gc_percent;
116 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
117 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CAO.crsp_val_cao_perc');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END crsp_val_cao_perc;
122
123
124 END IGS_PS_VAL_CAO;
[Home] [Help]