1 PACKAGE BODY IGS_PS_VAL_CFOS AS
2 /* $Header: IGSPS19B.pls 120.1 2006/07/25 15:08:32 sommukhe noship $ */
3
4 --
5 -- Validate the IGS_PS_COURSE field of study.
6 FUNCTION crsp_val_cfos_fos(
7 p_field_of_study IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2)
9 RETURN BOOLEAN AS
10 v_closed_ind IGS_PS_FLD_OF_STUDY.closed_ind%TYPE;
11 CURSOR c_field_of_study IS
12 SELECT closed_ind
13 FROM IGS_PS_FLD_OF_STUDY
14 WHERE field_of_study = p_field_of_study;
15 BEGIN
16 OPEN c_field_of_study;
17 FETCH c_field_of_study INTO v_closed_ind;
18 IF c_field_of_study%NOTFOUND THEN
19 p_message_name := NULL;
20 CLOSE c_field_of_study;
21 RETURN TRUE;
22 ELSIF (v_closed_ind = 'N') THEN
23 p_message_name := NULL;
24 CLOSE c_field_of_study;
25 RETURN TRUE;
26 ELSE
27 p_message_name := 'IGS_PS_FIELD_OF_STUDY_CLOSED';
28 CLOSE c_field_of_study;
29 RETURN FALSE;
30 END IF;
31 END crsp_val_cfos_fos;
32 --
33 -- Validate IGS_PS_COURSE field of study percentage for the IGS_PS_COURSE version.
34 FUNCTION crsp_val_cfos_perc(
35 p_course_cd IN VARCHAR2 ,
36 p_version_number IN NUMBER ,
37 p_message_name OUT NOCOPY VARCHAR2 )
38 RETURN BOOLEAN AS
39 -- who when What
40 --sarakshi 23-dec-2002 Bug#2689625,removed the when other part of the exception
41 --skpandey 10-Jul-2006 Bug#5343912,Modified cursor gc_percent and the code logic.
42 gv_course_f_o_s CHAR;
43 gv_course_status IGS_PS_STAT.s_course_status%TYPE;
44 CURSOR gc_course_status IS
45 SELECT CS.s_course_status
46 FROM IGS_PS_VER CV,
47 IGS_PS_STAT CS
48 WHERE CV.course_cd = p_course_cd AND
49 CV.version_number = p_version_number AND
50 CV.course_status = CS.course_status;
51 CURSOR gc_course_f_o_s_exists IS
52 SELECT 'x'
53 FROM IGS_PS_FIELD_STUDY
54 WHERE course_cd = p_course_cd AND
55 version_number = p_version_number;
56
57 CURSOR gc_percent(cp_course_cd igs_ps_field_study.course_cd%TYPE,
58 cp_version_number igs_ps_field_study.version_number%TYPE )IS
59 SELECT NVL(SUM(percentage),0) sum_per,fos_type_code
60 FROM IGS_PS_FIELD_STUDY_V
61 WHERE FOS_TYPE_CODE <> 'CIP'
62 AND course_cd = cp_course_cd
63 AND version_number = cp_version_number
64 GROUP BY fos_type_code
65 HAVING Sum(percentage) <>100;
66 gv_percent gc_percent%ROWTYPE;
67
68
69
70 BEGIN
71 -- finding the s_course_status
72 OPEN gc_course_status;
73 FETCH gc_course_status INTO gv_course_status;
74 -- finding IGS_PS_FIELD_STUDY records
75 OPEN gc_course_f_o_s_exists;
76 FETCH gc_course_f_o_s_exists INTO gv_course_f_o_s;
77 -- Find the sum of all percentages
78 -- when the percentage totals 100
79 OPEN gc_percent(p_course_cd,p_version_number);
80 FETCH gc_percent INTO gv_percent;
81 IF gc_percent%NOTFOUND THEN
82 CLOSE gc_percent;
83 CLOSE gc_course_f_o_s_exists;
84 CLOSE gc_course_status;
85 p_message_name := NULL;
86 RETURN TRUE;
87 ELSE
88 -- when the percentage doesn't total 100 and
89 -- when the IGS_PS_STAT.s_unit_status is PLANNED
90 -- and no IGS_PS_FIELD_STUDY records exist
91 IF (gv_course_status = 'PLANNED' AND gc_course_f_o_s_exists%NOTFOUND) THEN
92 CLOSE gc_percent;
93 CLOSE gc_course_status;
94 CLOSE gc_course_f_o_s_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_FIELD_STUDY records exist
101 CLOSE gc_percent;
102 CLOSE gc_course_status;
103 CLOSE gc_course_f_o_s_exists;
104 p_message_name := 'IGS_PS_PRCALLOC_PRGFOS_100';
105 RETURN FALSE;
106 END IF;
107 END IF;
108
109 END crsp_val_cfos_perc;
110 --
111 -- Validate IGS_PS_COURSE field of study major indicator.
112 FUNCTION crsp_val_cfos_major(
113 p_course_cd IN VARCHAR2 ,
114 p_version_number IN NUMBER ,
115 p_message_name OUT NOCOPY VARCHAR2 )
116 RETURN BOOLEAN AS
117 -- who when What
118 --sarakshi 23-dec-2002 Bug#2689625,removed the when other part of the exception
119 --skpandey 10-Jul-2006 Bug#5343912, Modified cursor c_course_field_of_study and the code logic.
120 BEGIN
121 DECLARE
122 -- v_course_field_of_study_rec IGS_PS_FIELD_STUDY%ROWTYPE;
123 v_course_status IGS_PS_STAT.s_course_status%TYPE;
124 v_count_records NUMBER;
125 CURSOR c_course_status IS
126 SELECT CS.s_course_status
127 FROM IGS_PS_VER CV,
128 IGS_PS_STAT CS
129 WHERE CV.course_cd = p_course_cd AND
130 CV.version_number = p_version_number AND
131 CV.course_status = CS.course_status;
132
133 CURSOR c_course_field_of_study (cp_course_cd igs_ps_field_study.course_cd%TYPE,
134 cp_version_number igs_ps_field_study.version_number%TYPE )IS
135 SELECT fos_type_code
136 FROM IGS_PS_FIELD_STUDY_V out_fos
137 WHERE FOS_TYPE_Code <> 'CIP'
138 AND course_cd = cp_course_cd
139 AND version_number = cp_version_number
140 AND NOT EXISTS( SELECT in_fos.FOS_TYPE_Code
141 FROM IGS_PS_FIELD_STUDY_V in_fos
142 WHERE in_fos.FOS_TYPE_Code = out_fos.FOS_TYPE_Code
143 AND in_fos.course_cd = out_fos.course_cd
144 AND in_fos.version_number = out_fos.version_number
145 AND major_field_ind = 'Y'
146 GROUP BY in_fos.FOS_TYPE_Code
147 HAVING Count(major_field_ind) = 1);
148 v_course_field_of_study_rec c_course_field_of_study%ROWTYPE;
149
150 CURSOR c_count_records IS
151 SELECT count(*)
152 FROM IGS_PS_FIELD_STUDY
153 WHERE course_cd = p_course_cd AND
154 version_number = p_version_number;
155 BEGIN
156 -- finding the s_course_status
157 OPEN c_course_status;
158 FETCH c_course_status INTO v_course_status;
159 -- counting all IGS_PS_FIELD_STUDY records
160 -- based on the course_cd and version_number
161 OPEN c_count_records;
162 FETCH c_count_records INTO v_count_records;
163 IF c_count_records%NOTFOUND THEN
164 RAISE no_data_found;
165 END IF;
166 CLOSE c_count_records;
167 -- selecting IGS_PS_FIELD_STUDY records based on course_cd,
168 -- version_number and major_field_ind
169 OPEN c_course_field_of_study(p_course_cd,p_version_number);
170 FETCH c_course_field_of_study INTO v_course_field_of_study_rec;
171 IF c_course_field_of_study%NOTFOUND THEN
172
173
174 -- when exactly one IGS_PS_FIELD_STUDY
175 -- record is selected with a major_field_ind = 'Y'
176 CLOSE c_course_status;
177 CLOSE c_course_field_of_study;
178 p_message_name := NULL;
179 RETURN TRUE;
180 ELSE
181 -- when no records are selected for the given IGS_PS_VER
182 -- and the IGS_PS_STAT.s_unit_status is PLANNED
183 IF (v_course_status = 'PLANNED' AND v_count_records = 0) THEN
184 CLOSE c_course_status;
185 CLOSE c_course_field_of_study;
186 p_message_name := NULL;
187 RETURN TRUE;
188 ELSE
189 -- when none/more than one IGS_PS_FIELD_STUDY
190 -- record is selected for the given IGS_PS_VER with a
191 -- major_field_ind = 'Y' and the IGS_PS_STAT.s_unit_status
192 -- is not PLANNED
193 CLOSE c_course_status;
194 CLOSE c_course_field_of_study;
195 p_message_name := 'IGS_PS_ONLYONE_FOS_MAJORPRG';
196 return FALSE;
197 END IF;
198 END IF;
199 EXCEPTION
200 WHEN no_data_found THEN
201 IF c_count_records%ISOPEN THEN
202 CLOSE c_count_records;
203 App_Exception.Raise_Exception;
204 END IF;
205 END;
206 END crsp_val_cfos_major;
207 --
208 -- Cross-table validation on IGS_PS_COURSE field of study and IGS_PS_COURSE IGS_PS_AWD.
209 FUNCTION crsp_val_cfos_caw(
210 p_course_cd IN VARCHAR2 ,
211 p_version_number IN NUMBER ,
212 p_message_name OUT NOCOPY VARCHAR2 )
213 RETURN BOOLEAN AS
214 /***************************************************************
215 Created By :
216 Date Created By :
217 Purpose :
218 Known Limitations,Enhancements or Remarks:
219 Change History :
220 Who When What
221 smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_get_cnt_course_award_rec to select open program awards only.
222 ***************************************************************/
223
224 v_cnt_field_of_study_rec NUMBER(5);
225 v_cnt_course_award_rec NUMBER(5);
226 CURSOR c_get_cnt_field_of_study_rec IS
227 SELECT count(*)
228 FROM IGS_PS_FIELD_STUDY
229 WHERE course_cd = p_course_cd AND
230 version_number = p_version_number;
231 CURSOR c_get_cnt_course_award_rec IS
232 SELECT count(*)
233 FROM IGS_PS_AWARD
234 WHERE course_cd = p_course_cd AND
235 version_number = p_version_number AND
236 closed_ind = 'N' ;
237 BEGIN
238 OPEN c_get_cnt_field_of_study_rec;
239 FETCH c_get_cnt_field_of_study_rec INTO v_cnt_field_of_study_rec;
240 CLOSE c_get_cnt_field_of_study_rec;
241 OPEN c_get_cnt_course_award_rec;
242 FETCH c_get_cnt_course_award_rec INTO v_cnt_course_award_rec;
243 CLOSE c_get_cnt_course_award_rec;
244 -- Multiple fields of study should only exist for combined
245 -- degree IGS_PS_COURSE.
246 IF (v_cnt_field_of_study_rec > 1) AND (v_cnt_course_award_rec <= 1) THEN
247 p_message_name := 'IGS_PS_MULTIPLE_FOS_EXIST';
248 RETURN FALSE;
249 END IF;
250 -- Combined degree IGS_PS_COURSE is identified by multiple IGS_PS_AWDs
251 -- for the IGS_PS_COURSE
252 IF (v_cnt_course_award_rec > 1) AND (v_cnt_field_of_study_rec <= 1) THEN
253 p_message_name := 'IGS_PS_COMB_DEGREEPRG_FOS';
254 RETURN FALSE;
255 END IF;
256 p_message_name := NULL;
257 RETURN TRUE;
258 END crsp_val_cfos_caw;
259 END IGS_PS_VAL_CFOS;
[Home] [Help]