1 PACKAGE BODY IGS_PR_VAL_SPUS AS
2 /* $Header: IGSPR23B.pls 115.6 2002/11/29 02:49:52 nsidana ship $ */
3
4 /* Bug 1956374
5 Who msrinivi
6 What duplicate removal Rremoved genp_prc_clear_rowid
7 */
8
9 -- Validate student progression unit set / outcome relationship
10
11 FUNCTION prgp_val_spus_spo(
12
13 p_person_id IN NUMBER ,
14
15 p_course_cd IN VARCHAR2 ,
16
17 p_sequence_number IN NUMBER ,
18
19 p_message_name OUT NOCOPY VARCHAR2 )
20
21 RETURN BOOLEAN IS
22
23 gv_other_detail VARCHAR2(255);
24
25 BEGIN -- PRGP_VAL_SPUS_SPO
26
27 -- Purpose: When creating a Student Progression Unit Set record validate that
28
29
30 -- the record is related to a IGS_PR_STDNT_PR_OU with a
31
32 -- IGS_PR_OU_TYPE that relates to a s_encmb_effect_type of EXC_CRS_US
33
34 DECLARE
35
36 v_exists VARCHAR2(1);
37
38 CURSOR c_spo(
39
40 cp_see_type IGS_FI_ENC_DFLT_EFT.s_encmb_effect_type%TYPE) IS
41
42 SELECT 'x'
43
44 FROM IGS_PR_STDNT_PR_OU spo,
45
46 IGS_PR_OU_TYPE pot,
47
48 IGS_FI_ENC_DFLT_EFT etde
49
50 WHERE spo.person_id = p_person_id AND
51
52 spo.course_cd = p_course_cd AND
53
54
55 spo.sequence_number = p_sequence_number AND
56
57 spo.progression_outcome_type = pot.progression_outcome_type AND
58
59 pot.encumbrance_type = etde.encumbrance_type AND
60
61 etde.s_encmb_effect_type = cp_see_type;
62
63 BEGIN
64
65 -- Set the default message number
66
67 p_message_name := null;
68
69 IF p_person_id IS NULL OR
70
71 p_course_cd IS NULL OR
72
73 p_sequence_number IS NULL THEN
74
75 RETURN TRUE;
76
77
78 END IF;
79
80 OPEN c_spo('EXC_CRS_US');
81
82 FETCH c_spo INTO v_exists;
83
84 IF c_spo%NOTFOUND THEN
85
86 CLOSE c_spo;
87
88 p_message_name := 'IGS_PR_STPR_OUT_EXC_CRS_US';
89
90 RETURN FALSE;
91
92 END IF;
93
94 CLOSE c_spo;
95
96 -- Return the default value
97
98 RETURN TRUE;
99
100 EXCEPTION
101
102
103 WHEN OTHERS THEN
104
105 IF c_spo%ISOPEN THEN
106
107 CLOSE c_spo;
108
109 END IF;
110
111 RAISE;
112
113 END;
114
115 EXCEPTION
116
117 WHEN OTHERS THEN
118
119
120 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
121 IGS_GE_MSG_STACK.ADD;
122 App_Exception.Raise_Exception;
123
124 END; -- Function PRGP_VAL_SPUS_SPO
125
126 --
127
128 -- Validate that the unit set is active
129
130 FUNCTION prgp_val_us_active(
131
132 p_unit_set_cd IN VARCHAR2 ,
133
134
135 p_version_number IN NUMBER ,
136
137 p_message_name OUT NOCOPY VARCHAR2 )
138
139 RETURN BOOLEAN IS
140
141 gv_other_detail VARCHAR2(255);
142
143 BEGIN -- PRGP_VAL_US_ACTIVE
144
145 -- Purpose: Validate the unit_set identified by the supplied unit_set_cd
146
147 -- and version_number is ACTIVE.
148
149 DECLARE
150
151 v_exists VARCHAR2(1);
152
153 CURSOR c_us_uss IS
154
155 SELECT 'x'
156
157
158 FROM IGS_EN_UNIT_SET us,
159
160 IGS_EN_UNIT_SET_STAT uss
161
162 WHERE us.unit_set_cd = p_unit_set_cd AND
163
164 us.version_number = p_version_number AND
165
166 us.unit_set_status = uss.unit_set_status AND
167
168 uss.s_unit_set_status = 'ACTIVE';
169
170 BEGIN
171
172 -- Set the default message number
173
174 p_message_name := null;
175
176 IF p_version_number IS NULL OR
177
178 p_unit_set_cd IS NULL THEN
179
180 RETURN TRUE;
181
182
183 END IF;
184
185 OPEN c_us_uss;
186
187 FETCH c_us_uss INTO v_exists;
188
189 IF c_us_uss%NOTFOUND THEN
190
191 CLOSE c_us_uss;
192
193 p_message_name := 'IGS_FI_UNITSET_INACTIVE';
194
195 RETURN FALSE;
196
197 END IF;
198
199 CLOSE c_us_uss;
200
201 -- Return the default value
202
203 RETURN TRUE;
204
205
206 EXCEPTION
207
208 WHEN OTHERS THEN
209
210 IF c_us_uss%ISOPEN THEN
211
212 CLOSE c_us_uss;
213
214 END IF;
215
216 RAISE;
217
218 END;
219
220 EXCEPTION
221
222 WHEN OTHERS THEN
223
224
225 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
226 IGS_GE_MSG_STACK.ADD;
227 App_Exception.Raise_Exception;
228 END; -- Function PRGP_VAL_US_ACTIVE
229
230 --
231
232 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
233
234 FUNCTION prgp_prc_spus_rowids(
235
236 p_inserting IN BOOLEAN ,
237
238 p_updating IN BOOLEAN ,
239
240 p_deleting IN BOOLEAN ,
241
242 p_message_name OUT NOCOPY VARCHAR2 )
243
244
245 RETURN BOOLEAN IS
246
247 v_index BINARY_INTEGER;
248
249 BEGIN
250
251 -- Process saved rows.
252
253 FOR v_index IN 1..gv_table_index - 1 LOOP
254
255 IF p_inserting OR p_updating OR p_deleting THEN
256
257 -- Update student progression outcome applied date
258
259 IGS_PR_GEN_004.IGS_PR_UPD_SPO_APLY_DT (
260
261 gt_rowid_table(v_index).person_id,
262
263 gt_rowid_table(v_index).course_cd,
264
265 gt_rowid_table(v_index).sequence_number);
266
267
268 END IF;
269
270 END LOOP;
271
272 RETURN TRUE;
273
274 END prgp_prc_spus_rowids;
275
276 --
277
278 -- Routine to save key in a PL/SQL TABLE for the current commit.
279
280 PROCEDURE prgp_set_spus_rowid(
281
282 p_person_id IN NUMBER ,
283
284 p_course_cd IN VARCHAR2 ,
285
286 p_sequence_number IN NUMBER )
287
288 IS
289
290 v_index BINARY_INTEGER;
291
292
293 v_spo_found BOOLEAN DEFAULT FALSE;
294
295 BEGIN
296
297 -- Check if record already exists in gt_rowid_table
298
299 FOR v_index IN 1..gv_table_index - 1 LOOP
300
301 IF gt_rowid_table(v_index).person_id = p_person_id AND
302
303 gt_rowid_table(v_index).course_cd = p_course_cd AND
304
305 gt_rowid_table(v_index).sequence_number = p_sequence_number THEN
306
307 v_spo_found := TRUE;
308
309 EXIT;
310
311 END IF;
312
313 END LOOP;
314
315
316 -- Save student progression outcome key details
317
318 IF NOT v_spo_found THEN
319
320 gt_rowid_table(gv_table_index).person_id := p_person_id;
321
322 gt_rowid_table(gv_table_index).course_cd := p_course_cd;
323
324 gt_rowid_table(gv_table_index).sequence_number := p_sequence_number;
325
326 gv_table_index := gv_table_index +1;
327
328 END IF;
329
330 END prgp_set_spus_rowid;
331
332 END IGS_PR_VAL_SPUS;
[Home] [Help]