1 PACKAGE BODY IGS_EN_VAL_PUSE AS
2 /* $Header: IGSEN60B.pls 115.4 2002/11/29 00:05:09 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 if person is enrolled in an excluded unit set.
11 FUNCTION enrp_val_puse_us(
12 p_person_id IN NUMBER ,
13 p_unit_set_cd IN VARCHAR2 ,
14 p_us_version_number IN NUMBER ,
15 p_course_cd IN VARCHAR2 ,
16 p_message_name OUT NOCOPY VARCHAR2)
17 RETURN BOOLEAN AS
18
19 BEGIN -- enrp_val_puse_us
20 -- validate whether or not a person is enrolled
21 -- in a specified unit set within a specified COURSE
22 DECLARE
23 v_check VARCHAR2(1);
24 CURSOR c_person_exist IS
25 SELECT 'x'
26 FROM IGS_AS_SU_SETATMPT susa
27 WHERE susa.person_id = p_person_id AND
28 susa.course_cd = p_course_cd AND
29 susa.unit_set_cd = p_unit_set_cd AND
30 susa.us_version_number = p_us_version_number AND
31 susa.student_confirmed_ind = 'Y' AND
32 susa.end_dt IS NULL;
33 BEGIN
34 p_message_name := null;
35 -- validate input parameters
36 IF (p_person_id IS NULL OR
37 p_course_cd IS NULL) THEN
38 RETURN TRUE;
39 END IF;
40 -- Check if the person is enrolled in the specified unit set
41 OPEN c_person_exist;
42 FETCH c_person_exist INTO v_check;
43 IF (c_person_exist%FOUND) THEN
44 -- person is enrolled in the specified unit set
45 CLOSE c_person_exist;
46 p_message_name := 'IGS_EN_PRSN_CURR_ENROLLED';
47 RETURN FALSE;
48 END IF;
49 -- person is not enrolled in the specified unit set
50 CLOSE c_person_exist;
51 RETURN TRUE;
52 END;
53 EXCEPTION
54 WHEN OTHERS THEN
55 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
56 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PUSE.enrp_val_puse_us');
57 IGS_GE_MSG_STACK.ADD;
58 App_Exception.Raise_Exception;
59 END enrp_val_puse_us;
60 --
61 -- Validate that person doesn't already have an open unit set exclusion.
62 FUNCTION enrp_val_puse_open(
63 p_person_id IN NUMBER ,
64 p_encumbrance_type IN VARCHAR2 ,
65 p_pen_start_dt IN DATE ,
66 p_s_encmb_effect_type IN VARCHAR2 ,
67 p_pee_start_dt IN DATE ,
68 p_unit_set_cd IN VARCHAR2 ,
69 p_us_version_number IN NUMBER ,
70 p_puse_start_dt IN DATE ,
71 p_message_name OUT NOCOPY VARCHAR2)
72 RETURN BOOLEAN AS
73
74 BEGIN -- enrp_val_puse_open
75 -- Validate that there are no other "open ended" puse records
76 -- for the nominated encumbrance effect type
77 DECLARE
78 v_check VARCHAR2(1);
79 v_ret_val BOOLEAN DEFAULT TRUE;
80 CURSOR c_person_unit_set_exclusion IS
81 SELECT 'x'
82 FROM IGS_PE_UNT_SET_EXCL
83 WHERE
84 person_id = p_person_id AND
85 encumbrance_type = p_encumbrance_type AND
86 pen_start_dt = p_pen_start_dt AND
87 s_encmb_effect_type = p_s_encmb_effect_type AND
88 pee_start_dt = p_pee_start_dt AND
89 unit_set_cd = p_unit_set_cd AND
90 us_version_number = p_us_version_number AND
91 expiry_dt IS NULL AND
92 puse_start_dt <> p_puse_start_dt;
93 BEGIN
94 p_message_name := null;
95 OPEN c_person_unit_set_exclusion;
96 FETCH c_person_unit_set_exclusion INTO v_check;
97 IF (c_person_unit_set_exclusion%FOUND) THEN
98 p_message_name := 'IGS_EN_PRSN_UNIT_SET_EXCLUSIO';
99 v_ret_val := FALSE;
100 END IF;
101 CLOSE c_person_unit_set_exclusion;
102 RETURN v_ret_val;
103 END;
104 EXCEPTION
105 WHEN OTHERS THEN
106 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
107 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PUSE.enrp_val_puse_open');
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END enrp_val_puse_open;
111 --
112 -- Validate unit set exists
113 FUNCTION crsp_val_us_exists(
114 p_unit_set_cd IN VARCHAR2 ,
115 p_version_number IN NUMBER ,
116 p_message_name OUT NOCOPY VARCHAR2)
117 RETURN BOOLEAN AS
118
119 BEGIN -- crsp_val_us_exists
120 -- This module validates the unit set exists.
121 DECLARE
122 v_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE;
123 CURSOR c_us IS
124 SELECT us.unit_set_cd
125 FROM IGS_EN_UNIT_SET us
126 WHERE us.unit_set_cd = p_unit_set_cd AND
127 us.version_number = p_version_number;
128 BEGIN
129 -- Set the default message number
130 p_message_name := null;
131 OPEN c_us;
132 FETCH c_us INTO v_unit_set_cd;
133 IF (c_us%NOTFOUND) THEN
134 CLOSE c_us;
135 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
136 RETURN FALSE;
137 END IF;
138 CLOSE c_us;
139 RETURN TRUE;
140 EXCEPTION
141 WHEN OTHERS THEN
142 IF (c_us%NOTFOUND) THEN
143 CLOSE c_us;
144 END IF;
145 RAISE;
146 END;
147 EXCEPTION
148 WHEN OTHERS THEN
149 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
150 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PUSE.crsp_val_us_exists');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END crsp_val_us_exists;
154
155 --
156 -- bug id : 1956374
157 -- removed FUNCTION enrp_val_encmb_dt
158 --
159 -- removed FUNCTION enrp_val_encmb_dts
160 --
161 END IGS_EN_VAL_PUSE;
[Home] [Help]