[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_VAL_POPU
Source
1 PACKAGE BODY IGS_PR_VAL_POPU AS
2 /* $Header: IGSPR17B.pls 120.0 2005/07/05 12:49:08 appldev noship $ */
3 /* Bug 1956374
4 Who msrinivi
5 What duplicate removal Rremoved genp_prc_clear_rowid,genp_set_row_id
6 */
7
8 --
9
10 -- Validate that a IGS_PR_OU_UNIT record can be created
11
12 FUNCTION prgp_val_popu_pro(
13
14 p_progression_rule_cat IN VARCHAR2 ,
15
16 p_pra_sequence_number IN NUMBER ,
17
18 p_sequence_number IN NUMBER ,
19
20
21 p_s_unit_type IN VARCHAR2 ,
22
23 p_message_name OUT NOCOPY VARCHAR2 )
24
25 RETURN BOOLEAN IS
26
27 gv_other_detail VARCHAR2(255);
28
29 BEGIN -- prgp_val_popu_pro
30
31 -- When creating a Progression Outcome Unit record validate that the record
32
33 -- is related to a progression_rule_outcome with a progression_outcome_type
34
35
36 -- that relates to a s_encmb_effect_type of EXC_CRS_U when s_unit_type
37
38 -- = EXCLUDE or a s_encmb_effect_type of RQRD_CRS_U when s_unit_type
39
40 -- = REQUIRED
41
42 DECLARE
43
44 cst_exc_crs_u CONSTANT VARCHAR(10) := 'EXC_CRS_U';
45
46 cst_rqrd_crs_u CONSTANT VARCHAR(10) := 'RQRD_CRS_U';
47
48
49 v_dummy VARCHAR2(1);
50
51 CURSOR c1_pro_pot_etde IS
52
53 SELECT 'X'
54
55 FROM IGS_PR_RU_OU pro,
56
57 IGS_PR_OU_TYPE pot,
58
59 IGS_FI_ENC_DFLT_EFT etde
60
61 WHERE pro.progression_rule_cat = p_progression_rule_cat AND
62
63
64 pro.pra_sequence_number = p_pra_sequence_number AND
65
66 pro.sequence_number = p_sequence_number AND
67
68 pro.progression_outcome_type = pot.progression_outcome_type AND
69
70 pot. encumbrance_type = etde.encumbrance_type AND
71
72 etde.s_encmb_effect_type = cst_exc_crs_u;
73
74 CURSOR c2_pro_pot_etde IS
75
76
77 SELECT 'X'
78
79 FROM IGS_PR_RU_OU pro,
80
81 IGS_PR_OU_TYPE pot,
82
83 IGS_FI_ENC_DFLT_EFT etde
84
85 WHERE pro.progression_rule_cat = p_progression_rule_cat AND
86
87 pro.pra_sequence_number = p_pra_sequence_number AND
88
89 pro.sequence_number = p_sequence_number AND
90
91
92 pro.progression_outcome_type = pot.progression_outcome_type AND
93
94 pot. encumbrance_type = etde.encumbrance_type AND
95
96 etde.s_encmb_effect_type = cst_rqrd_crs_u;
97
98 BEGIN
99
100 -- Set the default message number
101
102 p_message_name := null;
103
104
105 IF p_progression_rule_cat IS NULL OR
106
107 p_pra_sequence_number IS NULL OR
108
109 p_sequence_number IS NULL OR
110
111 p_s_unit_type IS NULL THEN
112
113 RETURN TRUE;
114
115 END IF;
116
117 IF p_s_unit_type = 'EXCLUDED' THEN
118
119
120 OPEN c1_pro_pot_etde;
121
122 FETCH c1_pro_pot_etde INTO v_dummy;
123
124 IF c1_pro_pot_etde%NOTFOUND THEN
125
126 CLOSE c1_pro_pot_etde;
127
128 p_message_name := 'IGS_PR_OUT_ENCTY_EXC_CRS_U';
129
130 RETURN FALSE;
131
132
133 END IF;
134
135 CLOSE c1_pro_pot_etde;
136
137 END IF;
138
139 IF p_s_unit_type = 'REQUIRED' THEN
140
141 OPEN c2_pro_pot_etde;
142
143 FETCH c2_pro_pot_etde INTO v_dummy;
144
145 IF c2_pro_pot_etde%NOTFOUND THEN
146
147
148 CLOSE c2_pro_pot_etde;
149
150 p_message_name := 'IGS_PR_OUT_ENCTY_RQRD_CRS_U';
151
152 RETURN FALSE;
153
154 END IF;
155
156 CLOSE c2_pro_pot_etde;
157
158 END IF;
159
160
161 RETURN TRUE;
162
163 EXCEPTION
164
165 WHEN OTHERS THEN
166
167 IF c1_pro_pot_etde%ISOPEN THEN
168
169 CLOSE c1_pro_pot_etde;
170
171 END IF;
172
173 IF c2_pro_pot_etde%ISOPEN THEN
174
175
176 CLOSE c2_pro_pot_etde;
177
178 END IF;
179
180 RAISE;
181
182 END;
183
184 EXCEPTION
185
186 WHEN OTHERS THEN
187
188
189
190 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193
194 END prgp_val_popu_pro;
195
196 --
197
198
199 -- Validate progression rule outcome automatically apply indicator
200
201 FUNCTION prgp_val_popu_auto(
202
203 p_progression_rule_cat IN VARCHAR2 ,
204
205 p_pra_sequence_number IN NUMBER ,
206
207 p_pro_sequence_number IN NUMBER ,
208
209 p_unit_cd IN VARCHAR2 ,
210
211 p_old_s_unit_type IN VARCHAR2 ,
212
213
214 p_message_name OUT NOCOPY VARCHAR2 )
215
216 RETURN BOOLEAN IS
217
218 gv_other_detail VARCHAR2(255);
219
220 BEGIN -- prgp_val_popu_auto
221
222 -- When updating or deleting a Progression Outcome Unit record validate that
223
224 -- the record is related to a IGS_PR_RU_OU with the
225
226
227 -- apply_automatically_ind set to 'Y' and a progression_outcome_type
228
229 -- that relates to:
230
231 -- a s_encmb_effect_type of EXC_CRS_U when s_unit_type = EXCLUDE or
232
233 -- a s_encmb_effect_type of RQRD_CRS_U when s_unit_type = REQUIRED
234
235 DECLARE
236
237 cst_exc_crs_u CONSTANT VARCHAR(10) := 'EXC_CRS_U';
238
239 cst_rqrd_crs_u CONSTANT VARCHAR(10) := 'RQRD_CRS_U';
240
241
242 cst_excluded CONSTANT VARCHAR(10) := 'EXCLUDED';
243
244 cst_required CONSTANT VARCHAR(10) := 'REQUIRED';
245
246 v_dummy VARCHAR2(1);
247
248 CURSOR c_pro_popu_pot_etde_1 IS
249 SELECT 'X'
250 FROM igs_pr_ru_ou pro
251 WHERE pro.progression_rule_cat = p_progression_rule_cat
252 AND pro.pra_sequence_number = p_pra_sequence_number
253 AND pro.sequence_number = p_pro_sequence_number
254 AND pro.apply_automatically_ind = 'N'
255 UNION ALL
256 SELECT 'X'
257 FROM igs_pr_ru_ou pro,
258 igs_pr_ou_unit popu,
259 igs_pr_ou_type pot,
260 igs_fi_enc_dflt_eft etde
261 WHERE pro.progression_rule_cat = p_progression_rule_cat
262 AND pro.pra_sequence_number = p_pra_sequence_number
263 AND pro.sequence_number = p_pro_sequence_number
264 AND pro.apply_automatically_ind = 'Y'
265 AND pro.progression_rule_cat = popu.progression_rule_cat
266 AND pro.pra_sequence_number = popu.pra_sequence_number
267 AND pro.sequence_number = popu.pro_sequence_number
268 AND popu.unit_cd <> p_unit_cd
269 AND popu.s_unit_type = cst_excluded
270 AND pro.progression_outcome_type = pot.progression_outcome_type
271 AND pot.encumbrance_type = etde.encumbrance_type
272 AND etde.s_encmb_effect_type = cst_exc_crs_u;
273
274 CURSOR c_pro_popu_pot_etde_2 IS
275
276 SELECT 'X'
277
278 FROM IGS_PR_RU_OU pro,
279
280 IGS_PR_OU_UNIT popu,
281
282
283 IGS_PR_OU_TYPE pot,
284
285 IGS_FI_ENC_DFLT_EFT etde
286
287 WHERE pro.progression_rule_cat = p_progression_rule_cat AND
288
289 pro.pra_sequence_number = p_pra_sequence_number AND
290
291 pro.sequence_number = p_pro_sequence_number AND
292
293 (pro.apply_automatically_ind = 'N' OR
294
295
296 (pro.apply_automatically_ind = 'Y' AND
297
298 pro.progression_rule_cat = popu.progression_rule_cat AND
299
300 pro.pra_sequence_number = popu.pra_sequence_number AND
301
302 pro.sequence_number = popu.pro_sequence_number AND
303
304 popu.unit_cd <> p_unit_cd AND
305
306 popu.s_unit_type = cst_required AND
307
308 pro.progression_outcome_type = pot.progression_outcome_type AND
309
310
311 pot. encumbrance_type = etde.encumbrance_type AND
312
313 etde.s_encmb_effect_type = cst_rqrd_crs_u));
314
315 BEGIN
316
317 -- Set the default message number
318
319 p_message_name := null;
320
321 IF p_progression_rule_cat IS NULL OR
322
323
324 p_pra_sequence_number IS NULL OR
325
326 p_pro_sequence_number IS NULL OR
327
328 p_unit_cd IS NULL OR
329
330 p_old_s_unit_type IS NULL THEN
331
332 RETURN TRUE;
333
334 END IF;
335
336 IF p_old_s_unit_type = cst_excluded THEN
337
338
339 OPEN c_pro_popu_pot_etde_1;
340
341 FETCH c_pro_popu_pot_etde_1 INTO v_dummy;
342
343 IF c_pro_popu_pot_etde_1%NOTFOUND THEN
344
345 CLOSE c_pro_popu_pot_etde_1;
346
347 p_message_name := 'IGS_PR_APAUO_OTY_ENEF_UEX_EXRT';
348
349 RETURN FALSE;
350
351
352 ELSE
353
354 CLOSE c_pro_popu_pot_etde_1;
355
356 END IF;
357
358 END IF;
359
360 IF p_old_s_unit_type = cst_required THEN
361
362 OPEN c_pro_popu_pot_etde_2;
363
364 FETCH c_pro_popu_pot_etde_2 INTO v_dummy;
365
366
367 IF c_pro_popu_pot_etde_2%NOTFOUND THEN
368
369 CLOSE c_pro_popu_pot_etde_2;
370
371 p_message_name := 'IGS_PR_APAUO_OTY_ENEF_UR_MET';
372
373 RETURN FALSE;
374
375 ELSE
376
377 CLOSE c_pro_popu_pot_etde_2;
378
379
380 END IF;
381
382 END IF;
383
384 RETURN TRUE;
385
386 EXCEPTION
387
388 WHEN OTHERS THEN
389
390 IF c_pro_popu_pot_etde_1%ISOPEN THEN
391
392 CLOSE c_pro_popu_pot_etde_1;
393
394
395 END IF;
396
397 IF c_pro_popu_pot_etde_2%ISOPEN THEN
398
399 CLOSE c_pro_popu_pot_etde_2;
400
401 END IF;
402
403 RAISE;
404
405 END;
406
407
408 EXCEPTION
409
410 WHEN OTHERS THEN
411
412
413 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
414 IGS_GE_MSG_STACK.ADD;
415 App_Exception.Raise_Exception;
416
417 END prgp_val_popu_auto;
418
419 --
420
421 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
422
423
424 FUNCTION prgp_prc_popu_rowids(
425
426 p_inserting IN BOOLEAN ,
427
428 p_updating IN BOOLEAN ,
429
430 p_deleting IN BOOLEAN ,
431
432 p_message_name OUT NOCOPY VARCHAR2 )
433
434 RETURN BOOLEAN IS
435
436 v_index BINARY_INTEGER;
437
438
439 v_message_name VARCHAR2(30);
440
441 BEGIN
442
443 -- Process saved rows.
444
445 FOR v_index IN 1..gv_table_index - 1 LOOP
446
447 -- Validate delete
448
449 IF p_deleting THEN
450
451
452 IF IGS_PR_val_popu.prgp_val_popu_auto (
453
454 gt_rowid_table(v_index).progression_rule_cat,
455
456 gt_rowid_table(v_index).pra_sequence_number,
457
458 gt_rowid_table(v_index).pro_sequence_number,
459
460 gt_rowid_table(v_index).unit_cd,
461
462 gt_rowid_table(v_index).old_s_unit_type,
463
464 v_message_name) = FALSE THEN
465
466
467 p_message_name := v_message_name;
468
469 RETURN FALSE;
470
471 END IF;
472
473 END IF;
474
475 -- Check the decision status can be changed
476
477 IF p_updating THEN
478
479
480 IF IGS_PR_val_popu.prgp_val_popu_auto (
481
482 gt_rowid_table(v_index).progression_rule_cat,
483
484 gt_rowid_table(v_index).pra_sequence_number,
485
486 gt_rowid_table(v_index).pro_sequence_number,
487
488 gt_rowid_table(v_index).unit_cd,
489
490 gt_rowid_table(v_index).old_s_unit_type,
491
492 v_message_name) = FALSE THEN
493
494
495 p_message_name := v_message_name;
496
497 RETURN FALSE;
498
499 END IF;
500
501 END IF;
502
503 END LOOP;
504
505 RETURN TRUE;
506
507
508 END prgp_prc_popu_rowids;
509
510 --
511
512 -- Routine to save key in a PL/SQL TABLE for the current commit.
513
514 PROCEDURE prgp_set_popu_rowid(
515
516 p_progression_rule_cat IN VARCHAR2 ,
517
518 p_pra_sequence_number IN NUMBER ,
519
520 p_pro_sequence_number IN NUMBER ,
521
522
523 p_unit_cd IN VARCHAR2 ,
524
525 p_old_s_unit_type IN VARCHAR2 )
526
527 IS
528
529 v_index BINARY_INTEGER;
530
531 v_popu_found BOOLEAN DEFAULT FALSE;
532
533 BEGIN
534
535
536 -- Check if record already exists in gt_rowid_table
537
538 FOR v_index IN 1..gv_table_index - 1 LOOP
539
540 IF gt_rowid_table(v_index).progression_rule_cat = p_progression_rule_cat AND
541
542
543
544 gt_rowid_table(v_index).pra_sequence_number = p_pra_sequence_number AND
545
546 gt_rowid_table(v_index).pro_sequence_number = p_pro_sequence_number AND
547
548 gt_rowid_table(v_index).unit_cd = p_unit_cd AND
549
550
551 gt_rowid_table(v_index).old_s_unit_type = p_old_s_unit_type THEN
552
553 v_popu_found := TRUE;
554
555 EXIT;
556
557 END IF;
558
559 END LOOP;
560
561 -- Save student progression outcome key details
562
563
564 IF NOT v_popu_found THEN
565
566 gt_rowid_table(gv_table_index).progression_rule_cat :=
567 p_progression_rule_cat;
568
569 gt_rowid_table(gv_table_index).pra_sequence_number := p_pra_sequence_number;
570
571
572
573 gt_rowid_table(gv_table_index).pro_sequence_number := p_pro_sequence_number;
574
575
576
577
578 gt_rowid_table(gv_table_index).unit_cd := p_unit_cd;
579
580 gt_rowid_table(gv_table_index).old_s_unit_type := p_old_s_unit_type;
581
582 gv_table_index := gv_table_index +1;
583
584 END IF;
585
586 END prgp_set_popu_rowid;
587
588 --
589
590
591 -- Warn if the unit does not have an active unit version
592
593 FUNCTION prgp_val_uv_active(
594
595 p_unit_cd IN VARCHAR2 ,
596
597 p_message_name OUT NOCOPY VARCHAR2 )
598
599 RETURN BOOLEAN IS
600
601 gv_other_detail VARCHAR2(255);
602
603 BEGIN -- PRGP_VAL_UV_ACTIVE
604
605
606 -- Purpose: Warn the user if the supplied unit_cd has no
607
608 -- ACTIVE unit_version records.
609
610 DECLARE
611
612 v_exists VARCHAR2(1);
613
614 CURSOR c_uv_ust IS
615
616 SELECT 'x'
617
618
619 FROM IGS_PS_UNIT_VER uv,
620
621 IGS_PS_UNIT_STAT ust
622
623 WHERE uv.unit_cd = p_unit_cd AND
624
625 uv.unit_status = ust.unit_status AND
626
627 ust.s_unit_status = 'ACTIVE';
628
629 BEGIN
630
631 -- Set the default message number
632
633
634 p_message_name := null;
635
636 IF p_unit_cd IS NULL THEN
637
638 RETURN TRUE;
639
640 END IF;
641
642 OPEN c_uv_ust;
643
644 FETCH c_uv_ust INTO v_exists;
645
646
647 IF c_uv_ust%NOTFOUND THEN
648
649 CLOSE c_uv_ust;
650
651 p_message_name := 'IGS_PR_UNT_CNT_NOACT_VER';
652
653 RETURN TRUE;
654
655 END IF;
656
657 CLOSE c_uv_ust;
658
659 -- Return the default value
660
661
662 RETURN TRUE;
663
664 EXCEPTION
665
666 WHEN OTHERS THEN
667
668 IF c_uv_ust%ISOPEN THEN
669
670 CLOSE c_uv_ust;
671
672 END IF;
673
674
675 RAISE;
676
677 END;
678
679 EXCEPTION
680
681 WHEN OTHERS THEN
682
683
684 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
685 IGS_GE_MSG_STACK.ADD;
686 App_Exception.Raise_Exception;
687
688 END; -- Function PRGP_VAL_UV_ACTIVE
689
690
691 END IGS_PR_VAL_POPU;