1 PACKAGE BODY IGS_GR_VAL_ACUS AS
2 /* $Header: IGSGR03B.pls 115.4 2002/11/29 00:39:50 nsidana ship $ */
3 --
4 -- Validate if the award ceremony unit set group is closed
5 FUNCTION grdp_val_acusg_close(
6 p_grd_cal_type IGS_GR_AWD_CRM_US_GP.grd_cal_type%TYPE ,
7 p_grd_ci_sequence_number IGS_GR_AWD_CRM_US_GP.grd_ci_sequence_number%TYPE ,
8 p_ceremony_number IGS_GR_AWD_CRM_US_GP.ceremony_number%TYPE ,
9 p_award_course_cd IGS_GR_AWD_CRM_US_GP.award_course_cd%TYPE ,
10 p_award_crs_version_number IGS_GR_AWD_CRM_US_GP.award_crs_version_number%TYPE ,
11 p_award_cd IGS_GR_AWD_CRM_US_GP.award_cd%TYPE ,
12 p_us_group_number IGS_GR_AWD_CRM_US_GP.us_group_number%TYPE ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 BEGIN -- grdp_val_acusg_close
16 -- Description: Validate if the award ceremony unit set group is closed
17 DECLARE
18 v_acusg_rec IGS_GR_AWD_CRM_US_GP.closed_ind%TYPE;
19 CURSOR c_acusg IS
20 SELECT acusg.closed_ind
21 FROM IGS_GR_AWD_CRM_US_GP acusg
22 WHERE acusg.grd_cal_type = p_grd_cal_type and
23 acusg.grd_ci_sequence_number = p_grd_ci_sequence_number and
24 acusg.ceremony_number = p_ceremony_number and
25 acusg.award_course_cd = p_award_course_cd and
26 acusg.award_crs_version_number =p_award_crs_version_number and
27 acusg.award_cd = p_award_cd and
28 acusg.us_group_number = p_us_group_number and
29 acusg.closed_ind ='Y';
30 BEGIN
31 p_message_name := NULL;
32 IF p_grd_cal_type IS NULL OR
33 p_grd_ci_sequence_number IS NULL OR
34 p_ceremony_number IS NULL OR
35 p_award_course_cd IS NULL OR
36 p_award_crs_version_number IS NULL OR
37 p_award_cd IS NULL OR
38 p_us_group_number iS NULL THEN
39 RETURN TRUE;
40 END IF;
41 OPEN c_acusg;
42 FETCH c_acusg INTO v_acusg_rec;
43 IF (c_acusg%FOUND) THEN
44 CLOSE c_acusg;
45 p_message_name := 'IGS_GR_AWD_CERM_GRP_CLOSED';
46 RETURN FALSE;
47 END IF;
48 CLOSE c_acusg;
49 RETURN TRUE;
50 EXCEPTION
51 WHEN OTHERS THEN
52 IF (c_acusg%ISOPEN) THEN
53 CLOSE c_acusg;
54 END IF;
55 RAISE;
56 END;
57 EXCEPTION
58 WHEN OTHERS THEN
59 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
60 IGS_GE_MSG_STACK.ADD;
61 App_Exception.Raise_Exception;
62 END grdp_val_acusg_close;
63 --
64 -- Validate the award ceremony unit set has related unit set attempts
65 FUNCTION grdp_val_acus_susa(
66 p_unit_set_cd IN VARCHAR2 ,
67 p_us_version_number IN NUMBER ,
68 p_message_name OUT NOCOPY VARCHAR2 )
69 RETURN BOOLEAN AS
70 BEGIN -- grdp_val_acus_susa
71 -- Description: Warn the user if no primary student_unit_set_attempt records
72 -- exist for the specified unit_set_cd and us_version_number. WARNING ONLY
73 DECLARE
74 v_dummy VARCHAR2(1);
75 CURSOR c_susa IS
76 SELECT 'X'
77 FROM IGS_AS_SU_SETATMPT susa
78 WHERE susa.unit_set_cd = p_unit_set_cd AND
79 susa.us_version_number = p_us_version_number AND
80 susa.primary_set_ind = 'Y';
81 BEGIN
82 p_message_name := NULL;
83 IF p_unit_set_cd IS NULL OR
84 p_us_version_number IS NULL THEN
85 RETURN TRUE;
86 END IF;
87 OPEN c_susa;
88 FETCH c_susa INTO v_dummy;
89 IF (c_susa%NOTFOUND) THEN
90 CLOSE c_susa;
91 p_message_name := 'IGS_GR_NO_STUD_UNIT_EXISTS';
92 RETURN TRUE;
93 END IF;
94 CLOSE c_susa;
95 RETURN TRUE;
96 END;
97 EXCEPTION
98 WHEN OTHERS THEN
99 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END grdp_val_acus_susa;
103 --
104 -- Validate if the award ceremony is closed.
105 FUNCTION grdp_val_awc_closed(
106 p_grd_cal_type IGS_GR_AWD_CEREMONY_ALL.grd_cal_type%TYPE ,
107 p_grd_ci_sequence_number IGS_GR_AWD_CEREMONY_ALL.grd_ci_sequence_number%TYPE ,
108 p_ceremony_number IGS_GR_AWD_CEREMONY_ALL.ceremony_number%TYPE ,
109 p_award_course_cd IGS_GR_AWD_CEREMONY_ALL.award_course_cd%TYPE ,
110 p_award_crs_version_number IGS_GR_AWD_CEREMONY_ALL.award_crs_version_number%TYPE ,
111 p_award_cd IGS_GR_AWD_CEREMONY_ALL.award_cd%TYPE ,
112 p_message_name OUT NOCOPY VARCHAR2 )
113 RETURN BOOLEAN AS
114 BEGIN -- grdp_val_awc_closed
115 -- Description: Validate if the award ceremony is closed
116 DECLARE
117 v_awc_rec IGS_GR_AWD_CEREMONY.closed_ind%TYPE;
118 CURSOR c_awc IS
119 SELECT 'X'
120 FROM IGS_GR_AWD_CEREMONY awc
121 WHERE awc.grd_cal_type = p_grd_cal_type AND
122 awc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
123 awc.ceremony_number = p_ceremony_number AND
124 NVL(awc.award_course_cd, 'NULL')= NVL(p_award_course_cd, 'NULL') AND
125 NVL(awc.award_crs_version_number, 0) =
126 NVL(p_award_crs_version_number, 0) AND
127 awc.award_cd = p_award_cd AND
128 awc.closed_ind = 'Y';
129 BEGIN
130 p_message_name := NULL;
131 IF p_grd_cal_type IS NULL OR
132 p_grd_ci_sequence_number IS NULL OR
133 p_ceremony_number IS NULL OR
134 p_award_cd IS NULL THEN
135 RETURN TRUE;
136 END IF;
137 OPEN c_awc;
138 FETCH c_awc INTO v_awc_rec;
139 IF (c_awc%FOUND) THEN
140 CLOSE c_awc;
141 p_message_name := 'IGS_GR_AWD_CERM_CLOSED';
142 RETURN FALSE;
143 END IF;
144 CLOSE c_awc;
145 RETURN TRUE;
146 EXCEPTION
147 WHEN OTHERS THEN
148 IF (c_awc%ISOPEN) THEN
149 CLOSE c_awc;
150 END IF;
151 RAISE;
152 END;
153 EXCEPTION
154 WHEN OTHERS THEN
155 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END grdp_val_awc_closed;
159 --
160 -- Validate the unit set has related course offering unit set records
161 FUNCTION grdp_val_crv_us(
162 p_award_course_cd IN VARCHAR2 ,
163 p_award_crs_version_number IN NUMBER ,
164 p_unit_set_cd IN VARCHAR2 ,
165 p_us_version_number IN NUMBER ,
166 p_message_name OUT NOCOPY VARCHAR2 )
167 RETURN BOOLEAN AS
168 BEGIN -- grdp_val_crv_us
169 -- Check if that the award_ceremony_unit_set.unit_set_cd and
170 --us_version_number is related to the award_ceremony.award_course_cd and
171 --award_crs_version_number.
172 DECLARE
173 v_exists VARCHAR2(1);
174 CURSOR c_us IS
175 SELECT 'x'
176 FROM IGS_EN_UNIT_SET us
177 WHERE us.unit_set_cd = p_unit_set_cd AND
178 us.version_number = p_us_version_number AND
179 us.administrative_ind = 'N';
180 CURSOR c_cous IS
181 SELECT 'x'
182 FROM IGS_PS_OFR_UNIT_SET cous
183 WHERE cous.course_cd = p_award_course_cd AND
184 cous.crv_version_number = p_award_crs_version_number AND
185 cous.unit_set_cd = p_unit_set_cd AND
186 cous.us_version_number = p_us_version_number;
187 BEGIN
188 -- Set the default message number
189 p_message_name := NULL;
190 --1. Check parameters :
191 If p_award_course_cd IS NULL OR
192 p_award_crs_version_number IS NULL OR
193 p_unit_set_cd IS NULL OR
194 p_us_version_number IS NULL THEN
195 RETURN TRUE;
196 END IF;
197 --2. Check that the unit_set is not an adminidtraive unit set.
198 OPEN c_us;
199 FETCH c_us INTO v_exists;
200 IF c_us%NOTFOUND THEN
201 CLOSE c_us;
202 p_message_name := 'IGS_GR_ADM_UNIT_SET_NOT_ALLOW';
203 RETURN FALSE;
204 END IF;
205 CLOSE c_us;
206 --3. Check that a course_offering_unit_set record exists for the supplied
207 --award_course_cd, award_crs_version_number, unit_set_cd and
208 --us_version_number.
209 OPEN c_cous;
210 FETCH c_cous INTO v_exists;
211 IF c_cous%NOTFOUND THEN
212 CLOSE c_cous;
213 p_message_name := 'IGS_GR_UNIT_SET_NOT_OFFERED';
214 RETURN FALSE;
215 END IF;
216 CLOSE c_cous;
217 -- Return the default value
218 RETURN TRUE;
219 EXCEPTION
220 WHEN OTHERS THEN
221 IF c_us%ISOPEN THEN
222 CLOSE c_us;
223 END IF;
224 IF c_cous%ISOPEN THEN
225 CLOSE c_cous;
226 END IF;
227 RAISE;
228 END;
229 EXCEPTION
230 WHEN OTHERS THEN
231 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END grdp_val_crv_us;
235 END IGS_GR_VAL_ACUS;
[Home] [Help]