1 PACKAGE BODY IGS_PS_VAL_COusr AS
2 /* $Header: IGSPS29B.pls 115.4 2002/11/29 03:00:52 nsidana ship $ */
3
4 -- Validate IGS_PS_UNIT set status for ins/upd/del of detail records
5 FUNCTION crsp_val_iud_us_dtl(
6 p_unit_set_cd IN VARCHAR2 ,
7 p_version_number IN NUMBER ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 BEGIN
11 -- crsp_val_iud_us_dtl
12 -- This module validates whether or not inserts and updates can be made to
13 -- IGS_EN_UNIT_SET
14 -- detail records (ie; record structures underneath the IGS_EN_UNIT_SET table)
15 DECLARE
16 v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
17 CURSOR c_us IS
18 SELECT uss.s_unit_set_status
19 FROM IGS_EN_UNIT_SET us,
20 IGS_EN_UNIT_SET_STAT uss
21 WHERE us.unit_set_cd = p_unit_set_cd AND
22 us.version_number = p_version_number AND
23 us.unit_set_status = uss.unit_set_status;
24 BEGIN
25 -- Set the default message number
26 p_message_name := NULL;
27 -- 1. Select the IGS_EN_UNIT_SET.IGS_EN_UNIT_SET_STAT for the given p_unit_cd and
28 -- p_version_number and
29 -- Select the IGS_EN_UNIT_SET_STAT.s_unit_set_status for the selected
30 -- IGS_EN_UNIT_SET.IGS_EN_UNIT_SET_STAT.
31 OPEN c_us;
32 FETCH c_us INTO v_s_unit_set_status;
33 -- 2. If no record is found -
34 IF (c_us%NOTFOUND) THEN
35 CLOSE c_us;
36 p_message_name := NULL;
37 RETURN TRUE;
38 END IF;
39 CLOSE c_us;
40 -- 3. Test the value of the IGS_EN_UNIT_SET_STAT.s_unit_set_status
41 IF v_s_unit_set_status = 'INACTIVE' THEN
42 p_message_name := 'IGS_PS_UNIT_SET_INACTIVE';
43 RETURN FALSE;
44 END IF;
45 RETURN TRUE;
46 EXCEPTION
47 WHEN OTHERS THEN
48 IF (c_us%ISOPEN) THEN
49 CLOSE c_us;
50 END IF;
51 App_Exception.Raise_Exception;
52 END;
53 END crsp_val_iud_us_dtl;
54 --
55 -- Validate COUSR hierarchy for duplicate ancestors/descendants
56 FUNCTION crsp_val_cousr_tree(
57 p_course_cd IN VARCHAR2 ,
58 p_crv_version_number IN NUMBER ,
59 p_cal_type IN VARCHAR2 ,
60 p_sup_unit_set_cd IN VARCHAR2 ,
61 p_sup_us_version_number IN NUMBER ,
62 p_sub_unit_set_cd IN VARCHAR2 ,
63 p_sub_us_version_number IN NUMBER ,
64 p_message_name OUT NOCOPY VARCHAR2 )
65 RETURN BOOLEAN AS
66 BEGIN
67 -- crsp_val_cousr_tree
68 -- This module validates that a IGS_PS_OF_UNT_SET_RL record can not
69 -- be created such that a IGS_PS_UNIT set is a parent/ancestor or child/descendant
70 -- of itself.
71 DECLARE
72 v_dummy VARCHAR2(1);
73 CURSOR c_cous (
74 cp_unit_set_cd IGS_PS_OFR_UNIT_SET.unit_set_cd%TYPE,
75 cp_us_version_number IGS_PS_OFR_UNIT_SET.us_version_number%TYPE) IS
76 SELECT 'x'
77 FROM IGS_PS_OFR_UNIT_SET cous
78 WHERE cous.course_cd = p_course_cd AND
79 cous.crv_version_number = p_crv_version_number AND
80 cous.cal_type = p_cal_type AND
81 cous.unit_set_cd = cp_unit_set_cd AND
82 cous.us_version_number = cp_us_version_number;
83 FUNCTION crspl_val_tree_circle (
84 p_tmp_sup_us_cd IGS_PS_OF_UNT_SET_RL.sup_unit_set_cd%TYPE,
85 p_tmp_sup_us_ver_num
86 IGS_PS_OF_UNT_SET_RL.sup_us_version_number%TYPE)
87 RETURN BOOLEAN
88 AS
89 BEGIN -- crspl_val_tree_circle
90 -- This validation function requires recursion.
91 DECLARE
92 v_circle_exists BOOLEAN DEFAULT FALSE;
93 CURSOR c_cousr (
94 cp_sup_unit_set_cd IGS_PS_OF_UNT_SET_RL.sup_unit_set_cd%TYPE,
95 cp_sup_us_version_number
96 IGS_PS_OF_UNT_SET_RL.sup_us_version_number%TYPE) IS
97 SELECT cousr.sub_unit_set_cd,
98 cousr.sub_us_version_number
99 FROM IGS_PS_OF_UNT_SET_RL cousr
100 WHERE cousr.course_cd = p_course_cd AND
101 cousr.crv_version_number = p_crv_version_number AND
102 cousr.cal_type = p_cal_type AND
103 cousr.sup_unit_set_cd = cp_sup_unit_set_cd AND
104 cousr.sup_us_version_number = cp_sup_us_version_number;
105 BEGIN
106 FOR v_cousr_rec IN c_cousr (
107 p_tmp_sup_us_cd,
108 p_tmp_sup_us_ver_num) LOOP
109 IF v_cousr_rec.sub_unit_set_cd = p_sup_unit_set_cd AND
110 v_cousr_rec.sub_us_version_number = p_sup_us_version_number THEN
111 -- the sub IGS_PS_UNIT set already had the same sup IGS_PS_UNIT set as child.
112 -- find circle in tree.
113 v_circle_exists := TRUE;
114 EXIT;
115 END IF;
116 IF crspl_val_tree_circle (
117 v_cousr_rec.sub_unit_set_cd,
118 v_cousr_rec.sub_us_version_number) = FALSE THEN
119 -- the sub IGS_PS_UNIT set already had the same sup IGS_PS_UNIT set as descendant.
120 -- find circle in subtree.
121 v_circle_exists := TRUE;
122 EXIT;
123 END IF;
124 END LOOP;
125 IF v_circle_exists = TRUE THEN
126 RETURN FALSE;
127 ELSE
128 RETURN TRUE;
129 END IF;
130 EXCEPTION
131 WHEN OTHERS THEN
132 IF c_cousr%ISOPEN THEN
133 CLOSE c_cousr;
134 END IF;
135 App_Exception.Raise_Exception;
136 END;
137 END crspl_val_tree_circle;
138 BEGIN
139 -- set default vaule
140 p_message_name := NULL;
141 -- 1. Validate the superior IGS_PS_UNIT set:
142 OPEN c_cous (
143 p_sup_unit_set_cd,
144 p_sup_us_version_number);
145 FETCH c_cous INTO v_dummy;
146 IF c_cous%NOTFOUND THEN
147 CLOSE c_cous;
148 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
149 RETURN FALSE;
150 END IF;
151 CLOSE c_cous;
152 -- 2. Validate the subordinate IGS_PS_UNIT set:
153 OPEN c_cous (
154 p_sub_unit_set_cd,
155 p_sub_us_version_number);
156 FETCH c_cous INTO v_dummy;
157 IF c_cous%NOTFOUND THEN
158 CLOSE c_cous;
159 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
160 RETURN FALSE;
161 END IF;
162 CLOSE c_cous;
163 -- 3. Validate the superior and subordinate IGS_PS_UNIT sets are not the same:
164 IF p_sup_unit_set_cd = p_sub_unit_set_cd AND
165 p_sup_us_version_number = p_sub_us_version_number THEN
166 p_message_name := 'IGS_PS_INVALID_RELATION_SUP';
167 RETURN FALSE;
168 END IF;
169 -- 4. Validate the IGS_PS_OF_UNT_SET_RL tree to ensure the superior
170 -- IGS_PS_UNIT set of the relationship does not exist anywhere else in the
171 -- relationship tree (ie; a recursive loop). This must cater for branching
172 -- (ie; multiple dependants).
173 IF crspl_val_tree_circle (
174 p_sub_unit_set_cd,
175 p_sub_us_version_number) = FALSE THEN
176 p_message_name := 'IGS_PS_INVALID_RELATION_HIERA';
177 RETURN FALSE;
178 END IF;
179 RETURN TRUE;
180 EXCEPTION
181 WHEN OTHERS THEN
182 IF c_cous%ISOPEN THEN
183 CLOSE c_cous;
184 END IF;
185 App_Exception.Raise_Exception;
186 END;
187 END crsp_val_cousr_tree;
188 --
189 -- Validate COUSR can only be created with US as superior if appropriate
190 FUNCTION crsp_val_cousr_sub(
191 p_course_cd IN VARCHAR2 ,
192 p_crv_version_number IN NUMBER ,
193 p_cal_type IN VARCHAR2 ,
194 p_sup_unit_set_cd IN VARCHAR2 ,
195 p_sup_us_version_number IN NUMBER ,
196 p_message_name OUT NOCOPY VARCHAR2 )
197 RETURN BOOLEAN AS
198 BEGIN
199 -- crsp_val_cousr_sub
200 -- This module validates that a IGS_PS_OF_UNT_SET_RL record can not
201 -- be created with a IGS_PS_OFR_UNIT_SET as the superior when the
202 -- only_as_sub_ind for that IGS_PS_OFR_UNIT_SET is set to 'Y'.
203 BEGIN
204 -- set default vaule
205 p_message_name := NUll;
206 IF IGS_PS_GEN_003.CRSP_GET_COUS_SUBIND (
207 p_course_cd,
208 p_crv_version_number,
209 p_cal_type,
210 p_sup_unit_set_cd,
211 p_sup_us_version_number) = 'Y' THEN
212 p_message_name := 'IGS_PS_UNIT_SET_NOT_USED';
213 RETURN FALSE;
214 ELSE
215 RETURN TRUE;
216 END IF;
217 END;
218 END crsp_val_cousr_sub;
219 --
220 -- Validate COUSR can only be created as sub if CACUS rec does not exist
221 FUNCTION crsp_val_cousr_cacus(
222 p_course_cd IN VARCHAR2 ,
223 p_crv_version_number IN NUMBER ,
224 p_cal_type IN VARCHAR2 ,
225 p_sub_unit_set_cd IN VARCHAR2 ,
226 p_sub_us_version_number IN NUMBER ,
227 p_message_name OUT NOCOPY VARCHAR2 )
228 RETURN BOOLEAN AS
229 BEGIN
230 -- crsp_val_cousr_cacus
231 -- validate that a crs_off_set_relationship record can't be created
232 -- with a IGS_PS_OFR_UNIT_SET as the subordinate when the IGS_PS_UNIT set is
233 -- restricting an IGS_PS_COURSE offering option admission category
234 DECLARE
235 v_unit_set_cd IGS_PS_COO_AD_UNIT_S.unit_set_cd%TYPE;
236 CURSOR c_cacus IS
237 SELECT cacus.unit_set_cd
238 FROM IGS_PS_COO_AD_UNIT_S cacus
239 WHERE cacus.course_cd = p_course_cd AND
240 cacus.crv_version_number = p_crv_version_number AND
241 cacus.cal_type = p_cal_type AND
242 cacus.unit_set_cd = p_sub_unit_set_cd AND
243 cacus.us_version_number = p_sub_us_version_number;
244 BEGIN
245 OPEN c_cacus;
246 FETCH c_cacus INTO v_unit_set_cd;
247 IF (c_cacus%FOUND) THEN
248 CLOSE c_cacus;
249 p_message_name := 'IGS_PS_UNIT_SET_NOT_USED_SUB';
250 RETURN FALSE;
251 END IF;
252 CLOSE c_cacus;
253 p_message_name := NULL;
254 RETURN TRUE ;
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF (c_cacus%ISOPEN) THEN
258 CLOSE c_cacus;
259 END IF;
260 App_Exception.Raise_Exception;
261 END;
262 END crsp_val_cousr_cacus;
263 --
264 END IGS_PS_VAL_COusr;
[Home] [Help]