1 PACKAGE BODY IGS_PR_VAL_POUS AS
2 /* $Header: IGSPR18B.pls 120.0 2005/07/05 11:39:41 appldev noship $ */
3
4 /* Bug 1956374
5 Who msrinivi
6 What duplicate removal Rremoved genp_prc_clear_rowid
7 */
8 /*
9 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
10 || Removed program unit (PRGP_VAL_US_ACTIVE) - from the spec and body. -- kdande
11 */
12 --
13
14 -- Validate that a prg_outcome_unit_set record can be created
15
16 FUNCTION prgp_val_pous_pro(
17
18 p_progression_rule_cat IN VARCHAR2 ,
19
20 p_pra_sequence_number IN NUMBER ,
21
22 p_sequence_number IN NUMBER ,
23
24
25 p_message_name OUT NOCOPY VARCHAR2 )
26
27 RETURN BOOLEAN IS
28
29 gv_other_detail VARCHAR2(255);
30
31 BEGIN -- prgp_val_pous_pro
32
33 -- When creating a Progression Outcome Unit Set record validate that the
34 -- record
35
36
37 -- is related to a progression_rule_outcome with a progression_outcome_type
38
39
40 -- that relates to a s_encmb_effect_type of EXC_CRS_US
41
42 DECLARE
43
44 cst_exc_crs_us CONSTANT VARCHAR(10) := 'EXC_CRS_US';
45
46 v_dummy VARCHAR2(1);
47
48 CURSOR c_pro_pot_etde IS
49
50 SELECT 'X'
51
52
53 FROM IGS_PR_RU_OU pro,
54
55 IGS_PR_OU_TYPE pot,
56
57 IGS_FI_ENC_DFLT_EFT etde
58
59 WHERE pro.progression_rule_cat = p_progression_rule_cat AND
60
61 pro.pra_sequence_number = p_pra_sequence_number AND
62
63 pro.sequence_number = p_sequence_number AND
64
65 pro.progression_outcome_type = pot.progression_outcome_type AND
66
67
68 pot. encumbrance_type = etde.encumbrance_type AND
69
70 etde.s_encmb_effect_type = cst_exc_crs_us;
71
72 BEGIN
73
74 -- Set the default message number
75
76 p_message_name := null;
77
78 IF p_progression_rule_cat IS NULL OR
79
80
81 p_pra_sequence_number IS NULL OR
82
83 p_sequence_number IS NULL THEN
84
85 RETURN TRUE;
86
87 END IF;
88
89 OPEN c_pro_pot_etde;
90
91 FETCH c_pro_pot_etde INTO v_dummy;
92
93 IF c_pro_pot_etde%NOTFOUND THEN
94
95
96 CLOSE c_pro_pot_etde;
97
98 p_message_name := 'IGS_PR_OUT_ENCTY_EXC_CRS_US';
99
100 RETURN FALSE;
101
102 END IF;
103
104 CLOSE c_pro_pot_etde;
105
106 RETURN TRUE;
107
108
109 EXCEPTION
110
111 WHEN OTHERS THEN
112
113 IF c_pro_pot_etde%ISOPEN THEN
114
115 CLOSE c_pro_pot_etde;
116
117 END IF;
118
119 RAISE;
120
121 END;
122
123
124 EXCEPTION
125
126 WHEN OTHERS THEN
127
128
129 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132
133 END prgp_val_pous_pro;
134
135
136 --
137
138 -- Validate progression rule outcome automatically apply indicator
139
140 FUNCTION prgp_val_pous_auto(
141
142 p_progression_rule_cat IN VARCHAR2 ,
143
144 p_pra_sequence_number IN NUMBER ,
145
146 p_pro_sequence_number IN NUMBER ,
147
148
149 p_unit_set_cd IN VARCHAR2 ,
150
151 p_us_version_number IN NUMBER ,
152
153 p_message_name OUT NOCOPY VARCHAR2 )
154
155 RETURN BOOLEAN IS
156
157 gv_other_detail VARCHAR2(255);
158
159 BEGIN -- prgp_val_pous_auto
160
161 -- When deleting a Progression Outcome Unit Set record validate if the record
162
163
164
165
166 -- is related to a IGS_PR_RU_OU with a progression_outcome_type
167
168 -- that relates to a s_encmb_effect_type of EXC_CRS_US and the
169
170 -- apply_automatically_ind is set to 'Y' that at least one
171
172 -- prg_outcome_unit_set record exists.
173
174 DECLARE
175
176
177 cst_exc_crs_us CONSTANT VARCHAR(10) := 'EXC_CRS_US';
178
179 v_dummy VARCHAR2(1);
180
181 CURSOR c_pro_pous_pot_etde IS
182 SELECT 'X'
183 FROM igs_pr_ru_ou pro
184 WHERE pro.progression_rule_cat = p_progression_rule_cat
185 AND pro.pra_sequence_number = p_pra_sequence_number
186 AND pro.sequence_number = p_pro_sequence_number
187 AND pro.apply_automatically_ind = 'N'
188 UNION ALL
189 SELECT 'X'
190 FROM igs_pr_ru_ou pro,
191 igs_pr_ou_unit_set pous,
192 igs_pr_ou_type_all pot,
193 igs_fi_enc_dflt_eft etde
194 WHERE pro.progression_rule_cat = p_progression_rule_cat
195 AND pro.pra_sequence_number = p_pra_sequence_number
196 AND pro.sequence_number = p_pro_sequence_number
197 AND pro.apply_automatically_ind = 'Y'
198 AND pro.progression_rule_cat = pous.progression_rule_cat
199 AND pro.pra_sequence_number = pous.pra_sequence_number
200 AND pro.sequence_number = pous.pro_sequence_number
201 AND ( pous.unit_set_cd <> p_unit_set_cd
202 OR pous.us_version_number <> p_us_version_number
203 )
204 AND pro.progression_outcome_type = pot.progression_outcome_type
205 AND pot.encumbrance_type = etde.encumbrance_type
206 AND etde.s_encmb_effect_type = cst_exc_crs_us ;
207
208 BEGIN
209
210 -- Set the default message number
211
212 p_message_name := null;
213
214 IF p_progression_rule_cat IS NULL OR
215
216 p_pra_sequence_number IS NULL OR
217
218
219 p_pro_sequence_number IS NULL OR
220
221 p_unit_set_cd IS NULL OR
222
223 p_us_version_number IS NULL THEN
224
225 RETURN TRUE;
226
227 END IF;
228
229 OPEN c_pro_pous_pot_etde;
230
231 FETCH c_pro_pous_pot_etde INTO v_dummy;
232
233
234 IF c_pro_pous_pot_etde%NOTFOUND THEN
235
236 CLOSE c_pro_pous_pot_etde;
237
238 p_message_name := 'IGS_PR_APAUO_SOTY_ENEF_USE_LEX';
239
240 RETURN FALSE;
241
242 END IF;
243
244 CLOSE c_pro_pous_pot_etde;
245
246
247 RETURN TRUE;
248
249 EXCEPTION
250
251 WHEN OTHERS THEN
252
253 IF c_pro_pous_pot_etde%ISOPEN THEN
254
255 CLOSE c_pro_pous_pot_etde;
256
257 END IF;
258
259 RAISE;
260
261
262 END;
263
264 EXCEPTION
265
266 WHEN OTHERS THEN
267
268
269 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272
273 RAISE;
274
275 END prgp_val_pous_auto;
276
277 --
278
279
280 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
281
282 FUNCTION prgp_prc_pous_rowids(
283
284 p_inserting IN BOOLEAN ,
285
286 p_updating IN BOOLEAN ,
287
288 p_deleting IN BOOLEAN ,
289
290 p_message_name OUT NOCOPY VARCHAR2 )
291
292
293 RETURN BOOLEAN IS
294
295 v_index BINARY_INTEGER;
296
297 v_message_name VARCHAR2(30);
298
299 BEGIN
300
301 -- Process saved rows.
302
303 FOR v_index IN 1..gv_table_index - 1 LOOP
304
305 -- Validate delete
306
307
308 IF p_deleting THEN
309
310 IF IGS_PR_val_pous.prgp_val_pous_auto (
311
312 gt_rowid_table(v_index).progression_rule_cat,
313
314 gt_rowid_table(v_index).pra_sequence_number,
315
316 gt_rowid_table(v_index).pro_sequence_number,
317
318 gt_rowid_table(v_index).unit_set_cd,
319
320
321 gt_rowid_table(v_index).us_version_number,
322
323 v_message_name) = FALSE THEN
324
325 p_message_name := v_message_name;
326
327 RETURN FALSE;
328
329 END IF;
330
331 END IF;
332
333 END LOOP;
334
335
336 RETURN TRUE;
337
338 END prgp_prc_pous_rowids;
339
340 --
341
342 -- Routine to save key in a PL/SQL TABLE for the current commit.
343
344 PROCEDURE prgp_set_pous_rowid(
345
346 p_progression_rule_cat IN VARCHAR2 ,
347
348
349 p_pra_sequence_number IN NUMBER ,
350
351 p_pro_sequence_number IN NUMBER ,
352
353 p_unit_set_cd IN VARCHAR2 ,
354
355 p_us_version_number IN NUMBER )
356
357 IS
358
359 v_index BINARY_INTEGER;
360
361 v_pous_found BOOLEAN DEFAULT FALSE;
362
363
364 BEGIN
365
366 -- Check if record already exists in gt_rowid_table
367
368 FOR v_index IN 1..gv_table_index - 1 LOOP
369
370 IF gt_rowid_table(v_index).progression_rule_cat = p_progression_rule_cat AND
371
372
373
374 gt_rowid_table(v_index).pra_sequence_number = p_pra_sequence_number AND
375
376
377 gt_rowid_table(v_index).pro_sequence_number = p_pro_sequence_number AND
378
379 gt_rowid_table(v_index).unit_set_cd = p_unit_set_cd AND
380
381 gt_rowid_table(v_index).us_version_number = p_us_version_number THEN
382
383 v_pous_found := TRUE;
384
385 EXIT;
386
387 END IF;
388
389 END LOOP;
390
391
392 -- Save student progression outcome key details
393
394 IF NOT v_pous_found THEN
395
396 gt_rowid_table(gv_table_index).progression_rule_cat :=
397 p_progression_rule_cat;
398
399
400 gt_rowid_table(gv_table_index).pra_sequence_number := p_pra_sequence_number;
401
402
403
404
405 gt_rowid_table(gv_table_index).pro_sequence_number := p_pro_sequence_number;
406
407
408
409 gt_rowid_table(gv_table_index).unit_set_cd := p_unit_set_cd;
410
411 gt_rowid_table(gv_table_index).us_version_number := p_us_version_number;
412
413 gv_table_index := gv_table_index +1;
414
415 END IF;
416
417 END prgp_set_pous_rowid;
418
419 END IGS_PR_VAL_POUS;
[Home] [Help]