1 PACKAGE BODY IGS_OR_VAL_INS AS
2 /* $Header: IGSOR03B.pls 115.9 2003/10/31 12:35:29 gmaheswa ship $ */
3
4 /*
5 || Created By : [email protected]
6 || Created On : 28-AUG-2000
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || WHO WHEN WHAT
11 || pkpatel 5-MAR-2002 Bug NO: 2224621
12 || MOdified the field GOVT_INSTITUTION_CD from NUMBER to VARCHAR2
13 || in Procedure ORGP_VAL_GOVT_CD
14 || pkpatel 27-OCT-2002 Bug No: 2613704
15 || Modified for lookup migration of GOVT_INSTITUTION_CD
16 || gmaheswa 12-SEP-2003 Bug No: 2863933
17 || Modified orgp_val_ins_local to return False as default.
18 || (reverse chronological order - newest change first)
19 */
20
21 -- Validate the delete of an instn code on records with no foreign key.
22
23 FUNCTION orgp_val_instn_del(
24
25 p_institution_cd IN VARCHAR2 ,
26
27 p_message_name OUT NOCOPY VARCHAR2 )
28
29 RETURN BOOLEAN AS
30
31 gv_other_detail VARCHAR2(255);
32
33 BEGIN
34
35 DECLARE
36
37 v_person_id IGS_PE_STATISTICS.person_id%TYPE;
38
39 v_course_cd IGS_AV_ADV_STANDING.course_cd%TYPE;
40
41 CURSOR c_person_stats (
42
43 cp_institution_cd IGS_OR_INSTITUTION.institution_cd%TYPE) IS
44
45 SELECT ps.person_id
46
47 FROM IGS_PE_STATISTICS ps
48
49 WHERE ps.prior_ug_inst = cp_institution_cd;
50
51 CURSOR c_advanced_stand (
52
53 cp_institution_cd IGS_OR_INSTITUTION.institution_cd%TYPE) IS
54
55 SELECT advs.course_cd
56
57 FROM IGS_AV_ADV_STANDING advs
58
59 WHERE advs.exemption_institution_cd = cp_institution_cd;
60
61 BEGIN
62
63 -- This module validates the deletion of an institution record.
64
65 -- Ensure the record is not used on a table that uses institution
66
67 -- codes, but does not have a foreign key to the institution table.
68
69 -- Some tables have been designed like this to allow for the entry
70
71 -- of an institution code or a valid DEETYA value.
72
73 -- Set the default message number
74
75 p_message_name := NULL;
76
77 -- VALIDATE THE IGS_PE_STATISTICS TABLE
78
79 -- Get the person_id based on the institution
80
81 -- code entered
82
83 OPEN c_person_stats(
84
85 p_institution_cd);
86
87 FETCH c_person_stats INTO v_person_id;
88
89 -- Check if a record was found. If so, the
90
91 -- institution record trying to be deleted was
92
93 -- being used by the prior_ug_inst column on the
94
95 -- IGS_PE_STATISTICS table
96
97 IF (c_person_stats%FOUND) THEN
98
99 CLOSE c_person_stats;
100
101 p_message_name := 'IGS_GE_PER_STATS_EXISTS';
102
103 RETURN FALSE;
104
105 END IF;
106
107 CLOSE c_person_stats;
108
109 -- VALIDATE THE ADVANCED STANDING TABLE
110
111 -- Get the course_cd based on the institution
112
113 -- code entered
114
115 OPEN c_advanced_stand(
116
117 p_institution_cd);
118
119 FETCH c_advanced_stand INTO v_course_cd;
120
121 -- Check if a record was found. If so, the
122
123 -- institution record trying to be deleted was
124
125 -- being used by the exemption_institution_cd
126
127 -- column on the IGS_AV_ADV_STANDING table
128
129 IF (c_advanced_stand%FOUND) THEN
130
131 CLOSE c_advanced_stand;
132
133 p_message_name := 'IGS_GE_ADV_STANDING_EXISTS';
134
135 RETURN FALSE;
136
137 END IF;
138
139 CLOSE c_advanced_stand;
140
141 -- Return the default value
142
143 RETURN TRUE;
144
145 END;
146
147 EXCEPTION
148
149 WHEN OTHERS THEN
150
151 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception ;
154
155 END orgp_val_instn_del;
156
157 --
158
159 -- Validate the government institution code.
160
161 FUNCTION orgp_val_govt_cd(
162
163 p_govt_institution_cd IN VARCHAR2 ,
164
165 p_message_name OUT NOCOPY VARCHAR2 )
166
167 RETURN Boolean AS
168
169 CURSOR c_gic IS
170
171 SELECT enabled_flag
172
173 FROM igs_lookup_values
174
175 WHERE lookup_type = 'OR_INST_GOV_CD'
176
177 AND enabled_flag = 'N'
178
179 AND lookup_code = p_govt_institution_cd;
180
181 v_other_detail VARCHAR2(255);
182
183 BEGIN
184
185 p_message_name := NULL;
186
187 FOR gic IN c_gic LOOP
188
189 p_message_name := 'IGS_OR_GOV_INST_CANT_CLOSED';
190
191 RETURN FALSE;
192
193 END LOOP;
194
195 RETURN TRUE;
196
197 EXCEPTION
198
199 WHEN OTHERS THEN
200
201 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception ;
204
205 END orgp_val_govt_cd;
206
207 --
208
209 -- Validate the institution status.
210
211 FUNCTION orgp_val_instn_sts(
212
213 p_institution_cd IN VARCHAR2 ,
214
215 p_institution_status IN VARCHAR2 ,
216
217 p_message_name OUT NOCOPY VARCHAR2 )
218
219 RETURN Boolean AS
220
221 v_closed_ind IGS_OR_INST_STAT.closed_ind%TYPE;
222
223 v_s_institution_status IGS_OR_INST_STAT.s_institution_status%TYPE;
224
225 v_message_name VARCHAR2(30);
226
227 v_other_detail VARCHAR2(255);
228
229 BEGIN
230
231 SELECT closed_ind,
232
233 s_institution_status
234
235 INTO v_closed_ind,
236
237 v_s_institution_status
238
239 FROM IGS_OR_INST_STAT
240
241 WHERE institution_status = p_institution_status;
242
243 -- Validate the closed indicator.
244
245 IF v_closed_ind = 'Y' THEN
246
247 p_message_name := 'IGS_OR_INS_STAT_CANT_CLOSED';
248
249 RETURN FALSE;
250
251 END IF;
252
253 -- If INACTIVE, validate there are no ACTIVE associated org units
254
255 IF v_s_institution_status = 'INACTIVE' THEN
256
257 IF IGS_OR_VAL_INS.orgp_val_no_actv_ou (
258
259 p_institution_cd,
260
261 v_message_name) = FALSE THEN
262
263 p_message_name := v_message_name ;
264
265 return FALSE;
266
267 END IF;
268
269 END IF;
270
271 RETURN TRUE;
272
273 EXCEPTION
274
275 WHEN OTHERS THEN
276
277 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception ;
280
281 END orgp_val_instn_sts;
282
283 --
284
285 -- Validate no active org units are associated with the specified instn.
286
287 FUNCTION orgp_val_no_actv_ou(
288
289 p_institution_cd IN VARCHAR2 ,
290
291 p_message_name OUT NOCOPY VARCHAR2 )
292
293 RETURN Boolean AS
294
295 CURSOR c_ou IS
296
297 SELECT org_unit_cd
298
299 FROM IGS_OR_UNIT,
300
301 IGS_OR_STATUS
302
303 WHERE institution_cd = p_institution_cd
304
305 AND IGS_OR_UNIT.org_status = IGS_OR_STATUS.org_status
306
307 AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
308
309 v_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE;
310
311 v_other_detail VARCHAR2(255);
312
313 BEGIN
314
315 OPEN c_ou;
316
317 FETCH c_ou INTO v_org_unit_cd;
318
319 IF c_ou%NOTFOUND THEN
320
321 CLOSE c_ou;
322
323 p_message_name := NULL;
324
325 RETURN TRUE;
326
327 ELSE
328
329 CLOSE c_ou;
330
331 p_message_name := 'IGS_OR_INST_STAT_CANT_CHANGED';
332
333 RETURN FALSE;
334
335 END IF;
336
337 EXCEPTION
338
339 WHEN OTHERS THEN
340
341 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception ;
344
345 END orgp_val_no_actv_ou;
346
347 --
348 -- Modified to return false as per Bug:2863933
349 FUNCTION orgp_val_ins_local(
350
351 p_institution_cd IN VARCHAR2 ,
352
353 p_message_name OUT NOCOPY VARCHAR2 )
354
355 RETURN BOOLEAN AS
356 v_institution_cd IGS_OR_INSTITUTION.institution_cd%TYPE := NULL;
357 BEGIN
358 v_institution_cd := FND_PROFILE.VALUE('IGS_OR_LOCAL_INST');
359 IF (p_institution_cd <> NVL(v_institution_cd,'') ) THEN
360 RETURN FALSE;
361 ELSE
362 RETURN TRUE;
363 END IF;
364 END orgp_val_ins_local;
365
366 END IGS_OR_VAL_INS;
[Home] [Help]