1 PACKAGE BODY IGS_EN_VAL_PUE AS
2 /* $Header: IGSEN58B.pls 115.5 2002/11/29 00:04:26 nsidana ship $ */
3 --
4 -- bug id : 1956374
5 -- sjadhav , 28-aug-2001
6 -- removed FUNCTION enrp_val_encmb_dt
7 -- removed FUNCTION enrp_val_encmb_dts
8 --
9 --
10 -- Validate that person doesn't already have an open unit exclusion.
11 FUNCTION enrp_val_pue_open(
12 p_person_id IN NUMBER ,
13 p_encumbrance_type IN VARCHAR2 ,
14 p_pen_start_dt IN DATE ,
15 p_s_encmb_effect_type IN VARCHAR2 ,
16 p_pee_start_dt IN DATE ,
17 p_unit_cd IN VARCHAR2 ,
18 p_pue_start_dt IN DATE ,
19 p_message_name OUT NOCOPY VARCHAR2)
20 RETURN BOOLEAN AS
21
22 BEGIN -- enrp_val_pue_open
23 -- Validate that there are no other "open ended" pue records
24 -- for the nominated encumbrance effect type
25 DECLARE
26 v_check VARCHAR2(1);
27 v_ret_val BOOLEAN DEFAULT TRUE;
28 CURSOR c_person_unit_exclusion IS
29 SELECT 'x'
30 FROM IGS_PE_PERS_UNT_EXCL
31 WHERE
32 person_id = p_person_id AND
33 encumbrance_type = p_encumbrance_type AND
34 pen_start_dt = p_pen_start_dt AND
35 s_encmb_effect_type = p_s_encmb_effect_type AND
36 pee_start_dt = p_pee_start_dt AND
37 unit_cd = p_unit_cd AND
38 expiry_dt IS NULL AND
39 pue_start_dt <> p_pue_start_dt;
40 BEGIN
41 p_message_name := null;
42 OPEN c_person_unit_exclusion;
43 FETCH c_person_unit_exclusion INTO v_check;
44 IF (c_person_unit_exclusion%FOUND) THEN
45 p_message_name := 'IGS_EN_PRSN_UNIT_EXCLUSION';
46 v_ret_val := FALSE;
47 END IF;
48 CLOSE c_person_unit_exclusion;
49 RETURN v_ret_val;
50 END;
51 EXCEPTION
52 WHEN OTHERS THEN
53 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
54 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PUE.enrp_val_pue_open');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57 END enrp_val_pue_open;
58 --
59 -- Validate if person is enrolled in an exluded unit.
60 FUNCTION enrp_val_pue_unit(
61 p_person_id IN NUMBER ,
62 p_unit_cd IN VARCHAR2 ,
63 p_course_cd IN VARCHAR2 ,
64 p_message_name OUT NOCOPY VARCHAR2)
65 RETURN BOOLEAN AS
66
67 BEGIN -- enrp_val_pue_unit
68 -- validate whether or not a person is enrolled
69 -- in a specified unit within a specified course
70 DECLARE
71 v_check VARCHAR2(1);
72 CURSOR c_person_exist IS
73 SELECT 'x'
74 FROM IGS_EN_SU_ATTEMPT
75 WHERE person_id = p_person_id AND
76 course_cd = p_course_cd AND
77 unit_cd = p_unit_cd AND
78 unit_attempt_status = 'ENROLLED';
79 BEGIN
80 p_message_name := null;
81 -- validate input parameters
82 IF (p_person_id IS NULL OR
83 p_course_cd IS NULL) THEN
84 RETURN TRUE;
85 END IF;
86 -- Check if the person is enrolled in the specified unit
87 OPEN c_person_exist;
88 FETCH c_person_exist INTO v_check;
89 IF (c_person_exist%FOUND) THEN
90 -- person is enrolled in the specified unit
91 CLOSE c_person_exist;
92 p_message_name := 'IGS_EN_PERS_ENRL_EXCL_UNIT';
93 RETURN FALSE;
94 END IF;
95 -- person is not enrolled in the specified unit
96 CLOSE c_person_exist;
97 RETURN TRUE;
98 END;
99 EXCEPTION
100 WHEN OTHERS THEN
101 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
102 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PUE.enrp_val_pue_unit');
103 IGS_GE_MSG_STACK.ADD;
104 App_Exception.Raise_Exception;
105 END enrp_val_pue_unit;
106 --
107 --
108 -- bug id : 1956374
109 -- sjadhav , 28-aug-2001
110 -- removed FUNCTION enrp_val_encmb_dts
111 END IGS_EN_VAL_PUE;