1 PACKAGE BODY IGS_CA_GEN_001 AS
2 /* $Header: IGSCA01B.pls 115.3 2002/11/28 22:56:08 nsidana ship $ */
3 FUNCTION calp_get_alias_val(
4 p_dt_alias IN IGS_CA_DA_INST.DT_ALIAS%TYPE ,
5 p_sequence_num IGS_CA_DA_INST.sequence_number%TYPE ,
6 p_cal_type IN IGS_CA_DA_INST.CAL_TYPE%TYPE ,
7 p_ci_sequence_num IN IGS_CA_DA_INST.ci_sequence_number%TYPE )
8 RETURN DATE AS
9 BEGIN
10 DECLARE
11 -- this cursor finds the alias value for a date alias instance
12 CURSOR c_dai ( cp_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE,
13 cp_sequence_num IGS_CA_DA_INST.sequence_number%TYPE,
14 cp_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE,
15 cp_ci_sequence_num IGS_CA_DA_INST.ci_sequence_number%TYPE) IS
16 SELECT absolute_val
17 FROM IGS_CA_DA_INST
18 WHERE DT_ALIAS = cp_dt_alias AND
19 sequence_number = cp_sequence_num AND
20 CAL_TYPE = cp_cal_type AND
21 ci_sequence_number = cp_ci_sequence_num;
22 v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
23 v_message_name varchar2(30);
24 BEGIN
25 IF p_dt_alias IS NULL OR
26 p_sequence_num IS NULL OR
27 p_cal_type IS NULL OR
28 p_ci_sequence_num IS NULL THEN
29 RETURN NULL;
30 END IF;
31 OPEN c_dai( p_dt_alias,
32 p_sequence_num,
33 p_cal_type,
34 p_ci_sequence_num);
35 FETCH c_dai INTO v_alias_val;
36 IF (c_dai%NOTFOUND) THEN
37 CLOSE c_dai;
38 RETURN NULL;
39 END IF;
40 CLOSE c_dai;
41 IF (v_alias_val IS NULL) THEN
42 -- alias defined by an offset dt alias instance
43 v_alias_val := IGS_CA_GEN_002.calp_clc_dt_from_dai(p_ci_sequence_num,
44 p_cal_type,
45 p_dt_alias,
46 p_sequence_num,
47 v_message_name);
48 END IF;
49 RETURN v_alias_val;
50 END;
51 END calp_get_alias_val;
52 --
53 FUNCTION CALP_GET_ALT_CD(
54 p_cal_type IN VARCHAR2 ,
55 p_sequence_number IN NUMBER )
56 RETURN VARCHAR2 AS
57 BEGIN
58 DECLARE
59 CURSOR c_ci (
60 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
61 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
62 SELECT ci.alternate_code
63 FROM IGS_CA_INST ci
64 WHERE ci.CAL_TYPE = cp_cal_type AND
65 ci.sequence_number = cp_sequence_number;
66 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
67 BEGIN
68 -- Load the start/end date from the source calendar instance.
69 OPEN c_ci(
70 p_cal_type,
71 p_sequence_number);
72 FETCH c_ci INTO v_alternate_code;
73 IF (c_ci%NOTFOUND)THEN
74 CLOSE c_ci;
75 RETURN NULL;
76 END IF;
77 CLOSE c_ci;
78 RETURN v_alternate_code;
79 END;
80 END calp_get_alt_cd;
81 --
82 FUNCTION calp_get_cat_closed(
83 p_cal_type IN VARCHAR2 ,
84 p_message_name OUT NOCOPY VARCHAR2 )
85 RETURN boolean AS
86 v_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
87 CURSOR c_cal_type IS
88 SELECT closed_ind
89 FROM IGS_CA_TYPE
90 WHERE CAL_TYPE = p_cal_type;
91 v_other_detail VARCHAR2(255);
92 BEGIN
93 p_message_name := null;
94 OPEN c_cal_type;
95 LOOP
96 FETCH c_cal_type
97 INTO v_closed_ind;
98 EXIT WHEN c_cal_type%NOTFOUND;
99 IF (v_closed_ind = 'Y') THEN
100 CLOSE c_cal_type;
101 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
102 RETURN TRUE;
103 ELSIF (v_closed_ind = 'N') THEN
104 CLOSE c_cal_type;
105 RETURN FALSE;
106 END IF;
107 END LOOP;
108 CLOSE c_cal_type;
109 END calp_get_cat_closed;
110 --
111 PROCEDURE CALP_GET_CI_DATES(
112 p_cal_type IN VARCHAR2 ,
113 p_ci_sequence_number IN NUMBER ,
114 p_start_dt OUT NOCOPY DATE ,
115 p_end_dt OUT NOCOPY DATE )
116 AS
117 BEGIN --calp_get_ci_dates
118 --Module returns the start/end dates as output parameters for a nominated
119 --calendar instance. This routine is used in triggers which have surrogate
120 --start/end dates which should be populated automatically.
121 DECLARE
122 v_ci_start_dt IGS_CA_INST.start_dt%TYPE;
123 v_ci_end_dt IGS_CA_INST.end_dt%TYPE;
124 CURSOR c_ci IS
125 SELECT ci.start_dt,
126 ci.end_dt
127 FROM IGS_CA_INST ci
128 WHERE CAL_TYPE = p_cal_type AND
129 sequence_number = p_ci_sequence_number;
130 BEGIN
131 --Validate parameters
132 IF (p_cal_type IS NOT NULL AND
133 p_ci_sequence_number IS NOT NULL) THEN
134 --If record exists then set output params acordingly
135 OPEN c_ci ;
136 FETCH c_ci INTO v_ci_start_dt,
137 v_ci_end_dt;
138 IF (c_ci%FOUND) THEN
139 p_start_dt := v_ci_start_dt;
140 p_end_dt := v_ci_end_dt;
141 ELSE
142 p_start_dt := NULL;
143 p_end_dt := NULL;
144 END IF;
145 CLOSE c_ci;
146 ELSE
147 p_start_dt := NULL;
148 p_end_dt := NULL;
149 END IF;
150 RETURN;
151 END;
152 EXCEPTION
153 WHEN OTHERS THEN
154 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
155 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_GEN_001.calp_get_ci_dates');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158
159 END calp_get_ci_dates;
160 --
161 FUNCTION calp_get_ci_start_dt(
162 p_cal_type IN VARCHAR2 ,
163 p_sequence_number IN NUMBER )
164 RETURN DATE AS
165 BEGIN -- calp_get_ci_start_dt
166 -- Return the calendar instance start date.
167 DECLARE
168 v_start_dt IGS_CA_INST.start_dt%TYPE;
169 CURSOR c_ci IS
170 SELECT ci.start_dt
171 FROM IGS_CA_INST ci
172 WHERE ci.CAL_TYPE = p_cal_type AND
173 sequence_number = p_sequence_number;
174 BEGIN
175 OPEN c_ci;
176 FETCH c_ci INTO v_start_dt;
177 IF (c_ci%FOUND) THEN
178 CLOSE c_ci;
179 RETURN v_start_dt;
180 ELSE
181 CLOSE c_ci;
182 RETURN NULL;
183 END IF;
184 EXCEPTION
185 WHEN OTHERS THEN
186 IF (c_ci%ISOPEN) THEN
187 CLOSE c_ci;
188 END IF;
189 RAISE;
190 END;
191
192 END calp_get_ci_start_dt;
193 --
194 FUNCTION CALP_GET_RLTV_TIME(
195 p_source_cal_type IN VARCHAR2 ,
196 p_source_ci_sequence_number IN NUMBER ,
197 p_related_cal_type IN VARCHAR2 ,
198 p_related_ci_sequence_number IN NUMBER )
199 RETURN VARCHAR2 AS
200 BEGIN
201 DECLARE
202 cst_before CONSTANT VARCHAR2(8) := 'BEFORE';
203 cst_current CONSTANT VARCHAR2(8) := 'CURRENT';
204 cst_after CONSTANT VARCHAR2(8) := 'AFTER';
205 cst_null CONSTANT VARCHAR2(4) := 'NULL';
206 v_census_dt_alias IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE;
207 v_start_dt IGS_CA_INST.start_dt%TYPE;
208 v_end_dt IGS_CA_INST.end_dt%TYPE;
209 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
210 v_within BOOLEAN;
211 v_before BOOLEAN;
212 v_after BOOLEAN;
213 CURSOR c_sgcc IS
214 SELECT sgcc.census_dt_alias
215 FROM IGS_GE_S_GEN_CAL_CON sgcc
216 WHERE sgcc.s_control_num = 1;
217 CURSOR c_ci (
218 cp_source_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
219 cp_source_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
220 SELECT ci.start_dt,
221 ci.end_dt
222 FROM IGS_CA_INST ci
223 WHERE ci.CAL_TYPE = cp_source_cal_type AND
224 ci.sequence_number = cp_source_ci_sequence_number;
225 CURSOR c_daiv (
226 cp_related_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
227 cp_related_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
228 cp_census_dt_alias IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE) IS
229 SELECT daiv.alias_val
230 FROM IGS_CA_DA_INST_V daiv
231 WHERE daiv.CAL_TYPE = cp_related_cal_type AND
232 daiv.ci_sequence_number = cp_related_ci_sequence_number AND
233 daiv.DT_ALIAS = cp_census_dt_alias;
234 BEGIN
235 -- This module gets the relative time period of a calendar
236 -- instance in relation to another.
237 -- Returns one of:
238 -- BEFORE - The related period is totally before the source period.
239 -- CURRENT - The related period overlaps the source period.
240 -- AFTER - The related period is totally after the source period.
241 -- OR
242 -- null - When the time could not be determined. This should
243 -- not happen in normal operation, but must be coded due to
244 -- the flexibility of the calendar facility.
245 -- An overlap is determined by checking whether any of the 'census date'
246 -- date alias instances within the related period is within or outside
247 -- the start/end date range of the source period. The assumption is being
248 -- made that all teaching period calendar instances have cencus dates.
249 -- Load the census date alias from the general calendar configuration table.
250 OPEN c_sgcc;
251 FETCH c_sgcc INTO v_census_dt_alias;
252 IF (c_sgcc%NOTFOUND) THEN
253 CLOSE c_sgcc;
254 RAISE NO_DATA_FOUND;
255 END IF;
256 CLOSE c_sgcc;
257 -- Load the start/end date from the source calendar instance.
258 OPEN c_ci(
259 p_source_cal_type,
260 p_source_ci_sequence_number);
261 FETCH c_ci INTO v_start_dt,
262 v_end_dt;
263 IF (c_ci%NOTFOUND)THEN
264 CLOSE c_ci;
265 RETURN cst_null;
266 END IF;
267 CLOSE c_ci;
268 -- Search for census date within the calendar instance.
269 v_within := FALSE;
270 v_before := FALSE;
271 v_after := FALSE;
272 OPEN c_daiv(
273 p_related_cal_type,
274 p_related_ci_sequence_number,
275 v_census_dt_alias);
276 FETCH c_daiv INTO v_alias_val;
277 IF (c_daiv%NOTFOUND) THEN
278 CLOSE c_daiv;
279 RETURN cst_null;
280 END IF;
281 CLOSE c_daiv;
282 FOR v_daiv_row IN c_daiv(
283 p_related_cal_type,
284 p_related_ci_sequence_number,
285 v_census_dt_alias) LOOP
286 IF (v_daiv_row.alias_val < v_start_dt) THEN
287 v_before := TRUE;
288 ELSIF (v_daiv_row.alias_val > v_end_dt) THEN
289 v_after := TRUE;
290 ELSE
291 v_within := TRUE;
292 END IF;
293 END LOOP;
294 IF (v_within = TRUE) OR
295 ((v_before = TRUE) AND
296 (v_after = TRUE)) THEN
297 RETURN cst_current;
298 ELSIF (v_before = TRUE) THEN
299 RETURN cst_before;
300 ELSIF (v_after = TRUE) THEN
301 RETURN cst_after;
302 END IF;
303 END;
304 END calp_get_rltv_time;
305 --
306 FUNCTION calp_get_sup_inst(
307 p_sup_cal_type IN VARCHAR2 ,
308 p_sub_cal_type IN VARCHAR2 ,
309 p_sub_ci_sequence_number IN NUMBER )
310 RETURN NUMBER AS
311 BEGIN -- calp_get_sup_inst
312 -- Return the superior calendar instance for a given calendar type and
313 -- subordinate calendar instance.
314 DECLARE
315 v_sup_ci_sequence_number
316 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE DEFAULT NULL;
317 CURSOR c_cir IS
318 SELECT sup_ci_sequence_number
319 FROM IGS_CA_INST_REL
320 WHERE sub_cal_type = p_sub_cal_type AND
321 sub_ci_sequence_number = p_sub_ci_sequence_number AND
322 sup_cal_type = p_sup_cal_type;
323 BEGIN
324 -- Get the superior calendar instance.
325 OPEN c_cir;
326 FETCH c_cir INTO v_sup_ci_sequence_number;
327 CLOSE c_cir;
328 RETURN v_sup_ci_sequence_number;
329 END;
330 END calp_get_sup_inst;
331 --
332 FUNCTION calp_set_alias_value(
333 p_absolute_val IN DATE ,
334 p_derived_val IN DATE )
335 RETURN DATE AS
336 BEGIN
337 IF p_absolute_val IS NULL THEN
338 RETURN(p_derived_val);
339 ELSE
340 RETURN(p_absolute_val);
341 END IF;
342 END calp_set_alias_value;
343 --
344 FUNCTION calp_set_alt_code(
345 p_cal_type IN VARCHAR2 ,
346 p_alternate_code IN VARCHAR2 ,
347 p_message_name OUT NOCOPY varchar2 )
348 RETURN VARCHAR2 AS
349 cst_teaching_period CONSTANT VARCHAR2(15) := 'TEACHING';
350 cst_academic_period CONSTANT VARCHAR2(15) := 'ACADEMIC';
351 v_s_cal_cat IGS_CA_TYPE.S_CAL_CAT%TYPE;
352 v_cal_instance_rec IGS_CA_INST%ROWTYPE;
353 v_other_detail VARCHAR2(255);
354 CURSOR c_cal_type IS
355 SELECT S_CAL_CAT
356 FROM IGS_CA_TYPE
357 WHERE CAL_TYPE = p_cal_type;
358 BEGIN
359 OPEN c_cal_type;
360 LOOP
361 FETCH c_cal_type
362 INTO v_s_cal_cat;
363 EXIT WHEN c_cal_type%NOTFOUND;
364 IF (v_s_cal_cat = cst_teaching_period) THEN
365 IF (p_alternate_code IS NULL) THEN
366 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
367 ELSE
368 p_message_name := null;
369 END IF;
370 CLOSE c_cal_type;
371 RETURN p_alternate_code;
372 ELSIF (v_s_cal_cat = cst_academic_period) THEN
373 IF (p_alternate_code IS NULL) THEN
374 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
375 ELSE
376 p_message_name := null;
377 END IF;
378 CLOSE c_cal_type;
379 RETURN p_alternate_code;
380 ELSE
381 IF (p_alternate_code IS NULL) THEN
382 p_message_name := null;
383 ELSE
384 p_message_name := 'IGS_CA_ENRALTCD_NOT_EXIST';
385 END IF;
386 CLOSE c_cal_type;
387 RETURN NULL;
388 END IF;
389 END LOOP;
390 CLOSE c_cal_type;
391 RETURN NULL;
392 END calp_set_alt_code;
393 --
394 END IGS_CA_GEN_001;
[Home] [Help]