1 PACKAGE BODY IGS_CA_GEN_002 AS
2 /* $Header: IGSCA02B.pls 115.6 2002/11/28 22:56:30 nsidana ship $ */
3
4 /******************************************************************
5 Created By :
6 Date Created By :
7 Purpose :
8 remarks :
9 Change History
10 Who When What
11 schodava 08-Jul-02 Bug # 2442220
12 Modified function calp_clc_daio_cnstrt
13 ******************************************************************/
14
15 FUNCTION calp_clc_daio_cnstrt(
16 p_dt_alias IN VARCHAR2 ,
17 p_sequence_number IN NUMBER ,
18 p_cal_type IN VARCHAR2 ,
19 p_ci_sequence_number IN NUMBER ,
20 p_alias_val IN DATE ,
21 p_message_name OUT NOCOPY varchar2 )
22 RETURN DATE AS
23 /*************************************************************
24 Created By :
25 Date Created By :
26 Purpose :
27 Know limitations, enhancements or remarks
28 Change History
29 Who When What
30 schodava 08-Jul-02 Bug # 2442220
31 To improve the performance of this procedure,
32 modified local functions calpl_holiday_resolve
33 and calpl_inst_break_resolve.
34 Replaced 2 complex cursors by 4 simple cursors.
35 (reverse chronological order - newest change first)
36 ***************************************************************/
37
38 BEGIN -- calp_clc_daio_cnstrt
39 -- This module accepts a IGS_CA_DA_INST date value which has been derived
40 -- from a IGS_CA_DA_OFST, attempts to resolve any dt_alias_offset_constrts
41 -- which exist for the IGS_CA_DA_OFST and then returns the modified date
42 -- value. Refer to S_DT_OFFSET_CONSTRAINT_TYPE table for list of valid
43 -- constraint types.
44 DECLARE
45 cst_must CONSTANT VARCHAR2(10) := 'MUST';
46 cst_must_not CONSTANT VARCHAR2(10) := 'MUST NOT';
47 cst_monday CONSTANT VARCHAR2(10) := 'MONDAY';
48 cst_tuesday CONSTANT VARCHAR2(10) := 'TUESDAY';
49 cst_wednesday CONSTANT VARCHAR2(10) := 'WEDNESDAY';
50 cst_thursday CONSTANT VARCHAR2(10) := 'THURSDAY';
51 cst_friday CONSTANT VARCHAR2(10) := 'FRIDAY';
52 cst_saturday CONSTANT VARCHAR2(10) := 'SATURDAY';
53 cst_sunday CONSTANT VARCHAR2(10) := 'SUNDAY';
54 cst_week_day CONSTANT VARCHAR2(10) := 'WEEK DAY';
55 cst_holiday CONSTANT VARCHAR2(10) := 'HOLIDAY';
56 cst_inst_break CONSTANT VARCHAR2(10) := 'INST BREAK';
57 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
58 -- replaced declarations of the following integers to PLS_INTEGER,
59 -- from NUMBER(5), to improve the performance of the procedure.
60 v_mod_count PLS_INTEGER;
61 v_constraint_count PLS_INTEGER;
62 v_loop_count PLS_INTEGER;
63 v_message_name VARCHAR2(30) ;
64 v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
65 ---------------------------------------------------------
66 -- Function: calpl_holiday_resolve
67 -- This function try to resolve the constraint when the
68 -- s_dt_alias_offset_type = 'HOLIDAY'
69 ---------------------------------------------------------
70 FUNCTION calpl_holiday_resolve (
71 p_s_dt_offset_cnstrt_type
72 IGS_CA_DA_OFFCNT.S_DT_OFFSET_CONSTRAINT_TYPE%TYPE,
73 p_cnstrt_condition IGS_CA_DA_OFFCNT.constraint_condition%TYPE,
74 p_cnstrt_resolution IGS_CA_DA_OFFCNT.constraint_resolution%TYPE,
75 p_mod_count IN OUT NOCOPY PLS_INTEGER,
76 p_alias_val IN OUT NOCOPY DATE)
77 RETURN varchar2
78 AS
79 BEGIN
80 DECLARE
81 v_changed BOOLEAN;
82 v_dummy VARCHAR2(1);
83 v_tmp_mod_count PLS_INTEGER;
84 v_tmp_alias_val DATE;
85 v_max_alias_val DATE DEFAULT NULL;
86 v_min_alias_val DATE DEFAULT NULL;
87
88 -- Replaced cursor c_m_alias_val by 2 cursors
89 -- c_m_alias_val1 and c_m_alias_val2.
90 -- The cursor c_m_alias_val was using the group functions
91 -- max and min, causing a performance issue.
92 -- Replaced it with 2 cursors, which use and ORDER BY clause instead of a function.
93 -- Also, included the calendar instance sequence number in the join condition
94 -- between the dai and ci tables, to improve the performance of the cursor.
95 -- The FK between the 2 tables is a composite one of cal_type and sequence_number.
96
97 CURSOR c_m_alias_val1 IS
98 SELECT TRUNC(dai.absolute_val)
99 FROM IGS_CA_DA_INST dai,
100 IGS_CA_INST ci,
101 IGS_CA_TYPE ct,
102 IGS_CA_STAT cs
103 WHERE dai.CAL_TYPE = ci.CAL_TYPE AND
104 dai.ci_sequence_number = ci.sequence_number AND
105 ci.CAL_TYPE = ct.CAL_TYPE AND
106 ct.S_CAL_CAT = cst_holiday AND
107 cs.CAL_STATUS = ci.CAL_STATUS AND
108 cs.s_cal_status = cst_active
109 ORDER BY dai.absolute_val ASC;
110
111 CURSOR c_m_alias_val2 IS
112 SELECT TRUNC(dai.absolute_val)
113 FROM IGS_CA_DA_INST dai,
114 IGS_CA_INST ci,
115 IGS_CA_TYPE ct,
116 IGS_CA_STAT cs
117 WHERE dai.CAL_TYPE = ci.CAL_TYPE AND
118 dai.ci_sequence_number = ci.sequence_number AND
119 ci.CAL_TYPE = ct.CAL_TYPE AND
120 ct.S_CAL_CAT = cst_holiday AND
121 cs.CAL_STATUS = ci.CAL_STATUS AND
122 cs.s_cal_status = cst_active
123 ORDER BY dai.absolute_val DESC;
124
125 CURSOR c_holiday (
126 cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
127 SELECT 'x'
128 FROM IGS_CA_TYPE ct
129 WHERE ct.S_CAL_CAT = cst_holiday AND
130 EXISTS (SELECT 'x'
131 FROM IGS_CA_INST ci,
132 IGS_CA_STAT cs
133 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
134 ci.CAL_STATUS = cs.CAL_STATUS AND
135 cs.s_cal_status = cst_active AND
136 EXISTS (SELECT 'x'
137 FROM IGS_CA_DA_INST dai
138 WHERE dai.CAL_TYPE = ct.CAL_TYPE AND
139 TRUNC(dai.absolute_val) = cp_alias_val));
140 BEGIN
141
142 OPEN c_m_alias_val1;
143 FETCH c_m_alias_val1 INTO v_min_alias_val;
144 CLOSE c_m_alias_val1;
145
146 OPEN c_m_alias_val2;
147 FETCH c_m_alias_val2 INTO v_max_alias_val;
148 CLOSE c_m_alias_val2;
149
150
151 IF v_max_alias_val IS NULL AND
152 v_min_alias_val IS NULL THEN
153 -- No HOLIDAY date alias instances have been defined which can be resolved.
154 IF p_cnstrt_condition = cst_must_not THEN
155 -- constraint does not require resolving
156 RETURN null;
157 ELSE
158 -- constraint cannot be resolved
159 RETURN ('IGS_CA_HOLIDAY_CONST_UNRSLVD');
160 END IF;
161 ELSE
162 IF p_cnstrt_condition = cst_must THEN
163 IF (p_alias_val > v_max_alias_val AND
164 p_cnstrt_resolution >0 ) OR
165 (p_alias_val < v_min_alias_val AND
166 p_cnstrt_resolution <0 ) THEN
167 -- constraint cannot be resolved
168 RETURN ('IGS_CA_HOLIDAY_CONS_UNRSVLD');
169 END IF;
170 END IF;
171 v_tmp_alias_val := p_alias_val;
172 v_tmp_mod_count := p_mod_count;
173 LOOP
174 v_changed := FALSE;
175 OPEN c_holiday (v_tmp_alias_val);
176 FETCH c_holiday INTO v_dummy;
177 IF c_holiday%FOUND THEN
178 CLOSE c_holiday;
179 IF p_cnstrt_condition = cst_must_not THEN
180 --update the date value and test again.
181 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
182 v_tmp_mod_count := v_tmp_mod_count + 1;
183 v_changed := TRUE;
184 END IF;
185 ELSE -- record not found
186 CLOSE c_holiday;
187 IF p_cnstrt_condition = cst_must THEN
188 --update the date value and test again.
189 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
190 v_tmp_mod_count := v_tmp_mod_count + 1;
191 IF (v_tmp_alias_val > v_max_alias_val AND
192 p_cnstrt_resolution >0 ) OR
193 (v_tmp_alias_val < v_min_alias_val AND
194 p_cnstrt_resolution <0 ) THEN
195 -- constraint cannot be resolved
196 RETURN ('IGS_CA_HOLIDAY_CONS_UNRSVLD');
197 END IF;
198 v_changed := TRUE;
199 END IF;
200 END IF;
201 EXIT WHEN v_changed = FALSE;
202 END LOOP;
203 -- resolve success or no resolving needed.
204 p_alias_val := v_tmp_alias_val;
205 p_mod_count := v_tmp_mod_count;
206 RETURN null;
207 END IF;
208 EXCEPTION
209 WHEN OTHERS THEN
210 IF c_m_alias_val1%ISOPEN THEN
211 CLOSE c_m_alias_val1;
212 END IF;
213 IF c_m_alias_val2%ISOPEN THEN
214 CLOSE c_m_alias_val2;
215 END IF;
216 IF c_holiday%ISOPEN THEN
217 CLOSE c_holiday;
218 END IF;
219 RAISE;
220 END;
221 END calpl_holiday_resolve;
222 ---------------------------------------------------------
223 -- Function: calpl_inst_break_resolve
224 -- This function try to resolve the constraint when the
225 -- s_dt_alias_offset_type = 'INST BREAK'
226 ---------------------------------------------------------
227 FUNCTION calpl_inst_break_resolve (
228 p_s_dt_offset_cnstrt_type
229 IGS_CA_DA_OFFCNT.S_DT_OFFSET_CONSTRAINT_TYPE%TYPE,
230 p_cnstrt_condition IGS_CA_DA_OFFCNT.constraint_condition%TYPE,
231 p_cnstrt_resolution IGS_CA_DA_OFFCNT.constraint_resolution%TYPE,
232 p_mod_count IN OUT NOCOPY PLS_INTEGER,
233 p_alias_val IN OUT NOCOPY DATE)
234 RETURN varchar2
235 AS
236 BEGIN
237 DECLARE
238 v_changed BOOLEAN;
239 v_dummy VARCHAR2(1);
240 v_tmp_mod_count PLS_INTEGER;
241 v_tmp_alias_val DATE;
242 v_max_alias_val DATE DEFAULT NULL;
243 v_min_alias_val DATE DEFAULT NULL;
244
245 -- Replaced cursor c_m_alias_val2 by 2 cursors
246 -- c_m_alias_val2a and c_m_alias_val2b.
247 -- The cursor c_m_alias_val2 was using the group functions
248 -- max and min, causing a performance issue.
249 -- Replaced it with 2 cursors, which use and ORDER BY clause instead of a function.
250
251 CURSOR c_m_alias_val2a IS
252 SELECT TRUNC(dai2.absolute_val)
253 FROM IGS_CA_DA_INST dai1,
254 IGS_CA_DA_INST dai2,
255 IGS_CA_DA_INST_PAIR daip,
256 IGS_CA_INST ci,
257 IGS_CA_TYPE ct,
258 IGS_CA_STAT cs
259 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
260 ct.S_CAL_CAT = cst_holiday AND
261 cs.CAL_STATUS = ci.CAL_STATUS AND
262 cs.s_cal_status = cst_active AND
263 dai1.CAL_TYPE = ci.CAL_TYPE AND
264 dai1.DT_ALIAS = daip.DT_ALIAS AND
265 dai1.sequence_number = daip.dai_sequence_number AND
266 dai1.CAL_TYPE = daip.CAL_TYPE AND
267 dai1.ci_sequence_number = daip.ci_sequence_number AND
268 dai2.DT_ALIAS = daip.related_dt_alias AND
269 dai2.sequence_number = daip.related_dai_sequence_number AND
270 dai2.CAL_TYPE = daip.related_cal_type AND
271 dai2.ci_sequence_number = daip.related_ci_sequence_number
272 ORDER BY dai2.absolute_val DESC;
273
274 CURSOR c_m_alias_val2b IS
275 SELECT TRUNC(dai1.absolute_val)
276 FROM IGS_CA_DA_INST dai1,
277 IGS_CA_DA_INST dai2,
278 IGS_CA_DA_INST_PAIR daip,
279 IGS_CA_INST ci,
280 IGS_CA_TYPE ct,
281 IGS_CA_STAT cs
282 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
283 ct.S_CAL_CAT = cst_holiday AND
284 cs.CAL_STATUS = ci.CAL_STATUS AND
285 cs.s_cal_status = cst_active AND
286 dai1.CAL_TYPE = ci.CAL_TYPE AND
287 dai1.DT_ALIAS = daip.DT_ALIAS AND
288 dai1.sequence_number = daip.dai_sequence_number AND
289 dai1.CAL_TYPE = daip.CAL_TYPE AND
290 dai1.ci_sequence_number = daip.ci_sequence_number AND
291 dai2.DT_ALIAS = daip.related_dt_alias AND
292 dai2.sequence_number = daip.related_dai_sequence_number AND
293 dai2.CAL_TYPE = daip.related_cal_type AND
294 dai2.ci_sequence_number = daip.related_ci_sequence_number
295 ORDER BY dai1.absolute_val ASC;
296
297 CURSOR c_instbreak (
298 cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
299 SELECT 'x'
300 FROM IGS_CA_TYPE ct
301 WHERE ct.S_CAL_CAT = cst_holiday AND
302 EXISTS (SELECT 'x'
303 FROM IGS_CA_INST ci,
304 IGS_CA_STAT cs
305 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
306 ci.CAL_STATUS = cs.CAL_STATUS AND
307 cs.s_cal_status = cst_active AND
308 EXISTS (SELECT 'x'
309 FROM IGS_CA_DA_INST dai1,
310 IGS_CA_DA_INST dai2,
311 IGS_CA_DA_INST_PAIR daip
312 WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
313 dai1.DT_ALIAS = daip.DT_ALIAS AND
314 dai1.sequence_number = daip.dai_sequence_number AND
315 dai1.CAL_TYPE = daip.CAL_TYPE AND
316 dai1.ci_sequence_number = daip.ci_sequence_number AND
317 dai2.DT_ALIAS = daip.related_dt_alias AND
318 dai2.sequence_number = daip.related_dai_sequence_number AND
319 dai2.CAL_TYPE = daip.related_cal_type AND
320 dai2.ci_sequence_number = daip.related_ci_sequence_number AND
321 cp_alias_val BETWEEN TRUNC(dai1.absolute_val) AND
322 TRUNC(dai2.absolute_val)));
323 BEGIN
324 OPEN c_m_alias_val2a;
325 FETCH c_m_alias_val2a INTO v_max_alias_val;
326 CLOSE c_m_alias_val2a;
327
328 OPEN c_m_alias_val2b;
329 FETCH c_m_alias_val2b INTO v_min_alias_val;
330 CLOSE c_m_alias_val2b;
331
332 IF v_max_alias_val IS NULL AND
333 v_min_alias_val IS NULL THEN
334 -- No HOLIDAY date alias instances have been defined which can be resolved.
335 IF p_cnstrt_condition = cst_must_not THEN
336 -- constraint does not require resolving
337 RETURN null;
338 ELSE
339 -- constraint cannot be resolved
340 RETURN ('IGS_CA_INSTBREAK_CONST_UNRSLV');
341 END IF;
342 ELSE
343 IF p_cnstrt_condition = cst_must THEN
344 IF (p_alias_val > v_max_alias_val AND
345 p_cnstrt_resolution >0 ) OR
346 (p_alias_val < v_min_alias_val AND
347 p_cnstrt_resolution <0 ) THEN
348 -- constraint cannot be resolved
349 RETURN ('IGS_CA_INSTBREAK_CONS_UNRSVLD');
350 END IF;
351 END IF;
352 v_tmp_alias_val := p_alias_val;
353 v_tmp_mod_count := p_mod_count;
354 LOOP
355 v_changed := FALSE;
356 OPEN c_instbreak (v_tmp_alias_val);
357 FETCH c_instbreak INTO v_dummy;
358 IF c_instbreak%FOUND THEN
359 CLOSE c_instbreak;
360 IF p_cnstrt_condition = cst_must_not THEN
361 --update the date value and test again.
362 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
363 v_tmp_mod_count := v_tmp_mod_count + 1;
364 v_changed := TRUE;
365 END IF;
366 ELSE -- record not found
367 CLOSE c_instbreak;
368 IF p_cnstrt_condition = cst_must THEN
369 --update the date value and test again.
370 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
371 v_tmp_mod_count := v_tmp_mod_count + 1;
372 IF (v_tmp_alias_val > v_max_alias_val AND
373 p_cnstrt_resolution >0 ) OR
374 (v_tmp_alias_val < v_min_alias_val AND
375 p_cnstrt_resolution <0 ) THEN
376 -- constraint cannot be resolved
377 RETURN ('IGS_CA_INSTBREAK_CONS_UNRSVLD');
378 END IF;
379 v_changed := TRUE;
380 END IF;
381 END IF;
382 EXIT WHEN v_changed = FALSE;
383 END LOOP;
384 -- resolve success or no resolving needed.
385 p_alias_val := v_tmp_alias_val;
386 p_mod_count := v_tmp_mod_count;
387 RETURN NULL;
388 END IF;
389 EXCEPTION
390 WHEN OTHERS THEN
391 IF c_m_alias_val2a%ISOPEN THEN
392 CLOSE c_m_alias_val2a;
393 END IF;
394 IF c_m_alias_val2b%ISOPEN THEN
395 CLOSE c_m_alias_val2b;
396 END IF;
397 IF c_instbreak%ISOPEN THEN
398 CLOSE c_instbreak;
399 END IF;
400 RAISE;
401 END;
402 END calpl_inst_break_resolve;
403 ---------------------------------------------------------
404 -- Procedure: calpl_constraint_resolve
405 ---------------------------------------------------------
406 PROCEDURE calpl_constraint_resolve (
407 p_constraint_count IN OUT NOCOPY PLS_INTEGER,
408 p_mod_count IN OUT NOCOPY PLS_INTEGER,
409 p_alias_val IN OUT NOCOPY IGS_CA_DA_INST.absolute_val%TYPE,
410 p_message_name OUT NOCOPY VARCHAR2 )
411 AS
412 BEGIN -- This local procedure is try to resolve the clashed constraint record.
413 DECLARE
414 v_msg_name VARCHAR2(30) ;
415 v_changed BOOLEAN;
416 CURSOR c_daoc IS
417 SELECT daoc.S_DT_OFFSET_CONSTRAINT_TYPE,
418 daoc.constraint_condition,
419 daoc.constraint_resolution
420 FROM IGS_CA_DA_INST_OFCNT daoc
421 WHERE daoc.DT_ALIAS = p_dt_alias AND
422 daoc.dai_sequence_number = p_sequence_number AND
423 daoc.CAL_TYPE = p_cal_type AND
424 daoc.ci_sequence_number = p_ci_sequence_number;
425 BEGIN
426 v_msg_name := null;
427 FOR v_daoc_rec IN c_daoc LOOP
428 p_constraint_count := p_constraint_count + 1;
429 IF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE IN ( cst_monday,
430 cst_tuesday,
431 cst_wednesday,
432 cst_thursday,
433 cst_friday,
434 cst_saturday,
435 cst_sunday) THEN
436 IF v_daoc_rec.constraint_condition = cst_must THEN
437 -- Use an inner loop to check and resolve any clash.
438 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) <>
439 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE LOOP
440 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
441 p_mod_count := p_mod_count + 1;
442 END LOOP;
443 ELSE -- NUST NOT
444 -- Use an inner loop to check and resolve any clash.
445 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) =
446 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE LOOP
447 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
448 p_mod_count := p_mod_count + 1;
449 END LOOP;
450 END IF;
451 ELSIF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE = cst_week_day THEN
452 IF v_daoc_rec.constraint_condition = cst_must THEN
453 -- Use an inner loop to check and resolve any clash.
454 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) NOT IN (cst_monday,
455 cst_tuesday,
456 cst_wednesday,
457 cst_thursday,
458 cst_friday) LOOP
459 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
460 p_mod_count := p_mod_count + 1;
461 END LOOP;
462 ELSE -- MUST NOT
463 -- Use an inner loop to check and resolve any clash.
464 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) IN ( cst_monday,
465 cst_tuesday,
466 cst_wednesday,
467 cst_thursday,
468 cst_friday) LOOP
469 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
470 p_mod_count := p_mod_count + 1;
471 END LOOP;
472 END IF;
473 ELSIF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE = cst_holiday THEN
474 -- If the constraint type is 'HOLIDAY', check that the date does not clash
475 -- against any date alias instance values in HOLIDAY calendars if the
476 -- condition is 'MUST NOT' or that it matches a date alias instance value
477 -- in a HOLIDAY calendar if the condition is 'MUST'.
478 v_msg_name := calpl_holiday_resolve (
479 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE,
480 v_daoc_rec.constraint_condition,
481 v_daoc_rec.constraint_resolution,
482 p_mod_count,
483 p_alias_val);
484 IF v_msg_name IS NOT NULL THEN
485 p_message_name := v_msg_name;
486 END IF;
487 ELSIF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE = cst_inst_break THEN
488 --If the constraint type is 'INST BREAK', check that the date does not fall
489 -- between the dates defined by any date alias instance pairs in HOLIDAY
490 -- calendars if the condition is 'MUST NOT' or that it does if the
491 -- condition is 'MUST'.
492 -- Use an inner loop to match the date against all defined DAIP's.
493 -- Find the start and end dates of any DAI Pair.
494 v_msg_name := calpl_inst_break_resolve (
495 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE,
496 v_daoc_rec.constraint_condition,
497 v_daoc_rec.constraint_resolution,
498 p_mod_count,
499 p_alias_val);
500 IF v_msg_name IS NOT NULL THEN
501 p_message_name := v_msg_name;
502 END IF;
503 END IF;
504 END LOOP; -- daoc loop
505 EXCEPTION
506 WHEN OTHERS THEN
507 IF c_daoc%ISOPEN THEN
508 CLOSE c_daoc;
509 END IF;
510 RAISE;
511 END;
512 END calpl_constraint_resolve;
513 BEGIN
514 -- Set default value.
515 p_message_name := null;
516 v_message_name := null;
517 v_mod_count :=0;
518 v_constraint_count := 0;
519 v_loop_count := 0;
520 -- 1. Check parameters
521 IF ( p_dt_alias IS NULL OR
522 p_sequence_number IS NULL OR
523 p_cal_type IS NULL OR
524 p_ci_sequence_number IS NULL OR
525 p_alias_val IS NULL) THEN
526 RETURN p_alias_val;
527 END IF;
528 -- 2. Check if any constraints exist for the date alias.
529 -- If not, no further processing required.
530 v_alias_val := TRUNC(p_alias_val);
531 -- 3. Set counters to be used to determine if the date constraints are
532 -- unresolvable.
533 WHILE v_loop_count <= 10 LOOP
534 -- 4. Use a loop to select each existiong constraint record.
535 -- and check the constraint against the date and if a clash exists,
536 -- attempt to resolve it.
537 calpl_constraint_resolve (
538 v_constraint_count,
539 v_mod_count,
540 v_alias_val,
541 v_message_name);
542 IF v_message_name IS NOT NULL THEN
543 p_message_name := v_message_name;
544 END IF;
545 IF v_mod_count > 0 AND
546 v_constraint_count > 1 THEN
547 -- Value has been modified by a constraint, so reset the counters and loop
548 -- through constraints again to ensure that changing the value for one
549 -- constraint, has not caused to clash with another constraint it had
550 -- already satisfied.
551 v_mod_count := 0;
552 v_constraint_count := 0;
553 v_loop_count := v_loop_count + 1;
554 ELSE
555 RETURN v_alias_val;
556 END IF;
557 END LOOP; -- v_loop_count<=10
558 -- Constraint is deemed unresolvable
559 p_message_name := ('IGS_CA_ATTEMPT_TORESOLVE_FAIL');
560 RETURN p_alias_val;
561 END;
562 END calp_clc_daio_cnstrt;
563 --
564
565 FUNCTION calp_clc_dao_cnstrt(
566 p_dt_alias IN VARCHAR2 ,
567 p_alias_val IN DATE ,
568 p_message_name OUT NOCOPY varchar2 )
569 RETURN DATE AS
570 gv_other_detail VARCHAR2(255);
571 BEGIN -- calp_clc_dao_cnstrt
572 -- This module accepts a IGS_CA_DA_INST date value which has been derived
573 -- from a IGS_CA_DA_OFST, attempts to resolve any dt_alias_offset_constrts
574 -- which exist for the IGS_CA_DA_OFST and then returns the modified date
575 -- value. Refer to S_DT_OFFSET_CONSTRAINT_TYPE table for list of valid
576 -- constraint types.
577 DECLARE
578 cst_must CONSTANT VARCHAR2(10) := 'MUST';
579 cst_must_not CONSTANT VARCHAR2(10) := 'MUST NOT';
580 cst_monday CONSTANT VARCHAR2(10) := 'MONDAY';
581 cst_tuesday CONSTANT VARCHAR2(10) := 'TUESDAY';
582 cst_wednesday CONSTANT VARCHAR2(10) := 'WEDNESDAY';
583 cst_thursday CONSTANT VARCHAR2(10) := 'THURSDAY';
584 cst_friday CONSTANT VARCHAR2(10) := 'FRIDAY';
585 cst_saturday CONSTANT VARCHAR2(10) := 'SATURDAY';
586 cst_sunday CONSTANT VARCHAR2(10) := 'SUNDAY';
587 cst_week_day CONSTANT VARCHAR2(10) := 'WEEK DAY';
588 cst_holiday CONSTANT VARCHAR2(10) := 'HOLIDAY';
589 cst_inst_break CONSTANT VARCHAR2(10) := 'INST BREAK';
590 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
591 v_mod_count NUMBER(5);
592 v_constraint_count NUMBER(5);
593 v_loop_count NUMBER(5);
594 v_message_name varchar2(30);
595 v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
596 ---------------------------------------------------------
597 -- Function: calpl_holiday_resolve
598 -- This function try to resolve the constraint when the
599 -- s_dt_alias_offset_type = 'HOLIDAY'
600 ---------------------------------------------------------
601 FUNCTION calpl_holiday_resolve (
602 p_s_dt_offset_cnstrt_type
603 IGS_CA_DA_OFFCNT.S_DT_OFFSET_CONSTRAINT_TYPE%TYPE,
604 p_cnstrt_condition IGS_CA_DA_OFFCNT.constraint_condition%TYPE,
605 p_cnstrt_resolution IGS_CA_DA_OFFCNT.constraint_resolution%TYPE,
606 p_mod_count IN OUT NOCOPY NUMBER,
607 p_alias_val IN OUT NOCOPY DATE)
608 RETURN VARCHAR2
609 AS
610 BEGIN
611 DECLARE
612 v_changed BOOLEAN;
613 v_dummy VARCHAR2(1);
614 v_tmp_mod_count NUMBER;
615 v_tmp_alias_val DATE;
616 v_max_alias_val DATE DEFAULT NULL;
617 v_min_alias_val DATE DEFAULT NULL;
618 CURSOR c_m_alias_val IS
619 SELECT TRUNC(max(dai.absolute_val)), TRUNC(min(dai.absolute_val))
620 FROM IGS_CA_DA_INST dai,
621 IGS_CA_INST ci,
622 IGS_CA_TYPE ct,
623 IGS_CA_STAT cs
624 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
625 ct.S_CAL_CAT = cst_holiday AND
626 cs.s_cal_status = ci.CAL_STATUS AND
627 cs.s_cal_status = cst_active AND
628 dai.CAL_TYPE = ci.CAL_TYPE;
629 CURSOR c_holiday (
630 cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
631 SELECT 'x'
632 FROM IGS_CA_TYPE ct
633 WHERE ct.S_CAL_CAT = cst_holiday AND
634 EXISTS (SELECT 'x'
635 FROM IGS_CA_INST ci,
636 IGS_CA_STAT cs
637 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
638 ci.CAL_STATUS = cs.CAL_STATUS AND
639 cs.s_cal_status = cst_active AND
640 EXISTS (SELECT 'x'
641 FROM IGS_CA_DA_INST dai
642 WHERE dai.CAL_TYPE = ct.CAL_TYPE AND
643 TRUNC(dai.absolute_val)= cp_alias_val));
644 BEGIN
645 OPEN c_m_alias_val;
646 FETCH c_m_alias_val INTO v_max_alias_val,
647 v_min_alias_val;
648 CLOSE c_m_alias_val;
649 IF v_max_alias_val IS NULL AND
650 v_min_alias_val IS NULL THEN
651 -- No HOLIDAY date alias instances have been defined which can be resolved.
652 IF p_cnstrt_condition = cst_must_not THEN
653 -- constraint does not require resolving
654 RETURN null;
655 ELSE
656 -- constraint cannot be resolved
657 RETURN ('IGS_CA_HOLIDAY_CONST_UNRSLVD');
658 END IF;
659 ELSE
660 IF p_cnstrt_condition = cst_must THEN
661 IF (p_alias_val > v_max_alias_val AND
662 p_cnstrt_resolution >0 ) OR
663 (p_alias_val < v_min_alias_val AND
664 p_cnstrt_resolution <0 ) THEN
665 -- constraint cannot be resolved
666 RETURN ('IGS_CA_HOLIDAY_CONS_UNRSVLD');
667 END IF;
668 END IF;
669 v_tmp_alias_val := p_alias_val;
670 v_tmp_mod_count := p_mod_count;
671 LOOP
672 v_changed := FALSE;
673 OPEN c_holiday (v_tmp_alias_val);
674 FETCH c_holiday INTO v_dummy;
675 IF c_holiday%FOUND THEN
676 CLOSE c_holiday;
677 IF p_cnstrt_condition = cst_must_not THEN
678 --update the date value and test again.
679 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
680 v_tmp_mod_count := v_tmp_mod_count + 1;
681 v_changed := TRUE;
682 END IF;
683 ELSE -- record not found
684 CLOSE c_holiday;
685 IF p_cnstrt_condition = cst_must THEN
686 --update the date value and test again.
687 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
688 v_tmp_mod_count := v_tmp_mod_count + 1;
689 IF (v_tmp_alias_val > v_max_alias_val AND
690 p_cnstrt_resolution >0 ) OR
691 (v_tmp_alias_val < v_min_alias_val AND
692 p_cnstrt_resolution <0 ) THEN
693 -- constraint cannot be resolved
694 RETURN ('IGS_CA_HOLIDAY_CONS_UNRSVLD');
695 END IF;
696 v_changed := TRUE;
697 END IF;
698 END IF;
699 EXIT WHEN v_changed = FALSE;
700 END LOOP;
701 -- resolve success or no resolving needed.
702 p_alias_val := v_tmp_alias_val;
703 p_mod_count := v_tmp_mod_count;
704 RETURN null;
705 END IF;
706 EXCEPTION
707 WHEN OTHERS THEN
708 IF c_m_alias_val%ISOPEN THEN
709 CLOSE c_m_alias_val;
710 END IF;
711 IF c_holiday%ISOPEN THEN
712 CLOSE c_holiday;
713 END IF;
714 RAISE;
715 END;
716 END calpl_holiday_resolve;
717 ---------------------------------------------------------
718 -- Function: calpl_inst_break_resolve
719 -- This function try to resolve the constraint when the
720 -- s_dt_alias_offset_type = 'INST BREAK'
721 ---------------------------------------------------------
722 FUNCTION calpl_inst_break_resolve (
723 p_s_dt_offset_cnstrt_type
724 IGS_CA_DA_OFFCNT.S_DT_OFFSET_CONSTRAINT_TYPE%TYPE,
725 p_cnstrt_condition IGS_CA_DA_OFFCNT.constraint_condition%TYPE,
726 p_cnstrt_resolution IGS_CA_DA_OFFCNT.constraint_resolution%TYPE,
727 p_mod_count IN OUT NOCOPY NUMBER,
728 p_alias_val IN OUT NOCOPY DATE)
729 RETURN varchar2
730 AS
731 BEGIN
732 DECLARE
733 v_changed BOOLEAN;
734 v_dummy VARCHAR2(1);
735 v_tmp_mod_count NUMBER;
736 v_tmp_alias_val DATE;
737 v_max_alias_val DATE DEFAULT NULL;
738 v_min_alias_val DATE DEFAULT NULL;
739 CURSOR c_m_alias_val2 IS
740 SELECT TRUNC(MAX(dai2.absolute_val)), TRUNC(MIN(dai1.absolute_val))
741 FROM IGS_CA_DA_INST dai1,
742 IGS_CA_DA_INST dai2,
743 IGS_CA_DA_INST_PAIR daip,
744 IGS_CA_INST ci,
745 IGS_CA_TYPE ct,
746 IGS_CA_STAT cs
747 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
748 ct.S_CAL_CAT = cst_holiday AND
749 cs.s_cal_status = ci.CAL_STATUS AND
750 cs.s_cal_status = cst_active AND
751 dai1.CAL_TYPE = ci.CAL_TYPE AND
752 dai1.DT_ALIAS = daip.DT_ALIAS AND
753 dai1.sequence_number = daip.dai_sequence_number AND
754 dai1.CAL_TYPE = daip.CAL_TYPE AND
755 dai1.ci_sequence_number = daip.ci_sequence_number AND
756 dai2.DT_ALIAS = daip.related_dt_alias AND
757 dai2.sequence_number = daip.related_dai_sequence_number AND
758 dai2.CAL_TYPE = daip.related_cal_type AND
759 dai2.ci_sequence_number = daip.related_ci_sequence_number;
760 CURSOR c_instbreak (
761 cp_alias_val IGS_CA_DA_INST.absolute_val%TYPE) IS
762 SELECT 'x'
763 FROM IGS_CA_TYPE ct
764 WHERE ct.S_CAL_CAT = cst_holiday AND
765 EXISTS (SELECT 'x'
766 FROM IGS_CA_INST ci,
767 IGS_CA_STAT cs
768 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
769 ci.CAL_STATUS = cs.CAL_STATUS AND
770 cs.s_cal_status = cst_active AND
771 EXISTS (SELECT 'x'
772 FROM IGS_CA_DA_INST dai1,
773 IGS_CA_DA_INST dai2,
774 IGS_CA_DA_INST_PAIR daip
775 WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
776 dai1.DT_ALIAS = daip.DT_ALIAS AND
777 dai1.sequence_number = daip.dai_sequence_number AND
778 dai1.CAL_TYPE = daip.CAL_TYPE AND
779 dai1.ci_sequence_number = daip.ci_sequence_number AND
780 dai2.DT_ALIAS = daip.related_dt_alias AND
781 dai2.sequence_number = daip.related_dai_sequence_number AND
782 dai2.CAL_TYPE = daip.related_cal_type AND
783 dai2.ci_sequence_number = daip.related_ci_sequence_number AND
784 cp_alias_val BETWEEN TRUNC(dai1.absolute_val) AND
785 TRUNC(dai2.absolute_val)));
786 BEGIN
787 OPEN c_m_alias_val2;
788 FETCH c_m_alias_val2 INTO v_max_alias_val,
789 v_min_alias_val;
790 CLOSE c_m_alias_val2;
791 IF v_max_alias_val IS NULL AND
792 v_min_alias_val IS NULL THEN
793 -- No HOLIDAY date alias instances have been defined which can be resolved.
794 IF p_cnstrt_condition = cst_must_not THEN
795 -- constraint does not require resolving
796 RETURN null;
797 ELSE
798 -- constraint cannot be resolved
799 RETURN ('IGS_CA_INSTBREAK_CONST_UNRSLV');
800 END IF;
801 ELSE
802 IF p_cnstrt_condition = cst_must THEN
803 IF (p_alias_val > v_max_alias_val AND
804 p_cnstrt_resolution >0 ) OR
805 (p_alias_val < v_min_alias_val AND
806 p_cnstrt_resolution <0 ) THEN
807 -- constraint cannot be resolved
808 RETURN ('IGS_CA_INSTBREAK_CONS_UNRSVLD');
809 END IF;
810 END IF;
811 v_tmp_alias_val := p_alias_val;
812 v_tmp_mod_count := p_mod_count;
813 LOOP
814 v_changed := FALSE;
815 OPEN c_instbreak (v_tmp_alias_val);
816 FETCH c_instbreak INTO v_dummy;
817 IF c_instbreak%FOUND THEN
818 CLOSE c_instbreak;
819 IF p_cnstrt_condition = cst_must_not THEN
820 --update the date value and test again.
821 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
822 v_tmp_mod_count := v_tmp_mod_count + 1;
823 v_changed := TRUE;
824 END IF;
825 ELSE -- record not found
826 CLOSE c_instbreak;
827 IF p_cnstrt_condition = cst_must THEN
828 --update the date value and test again.
829 v_tmp_alias_val := v_tmp_alias_val + p_cnstrt_resolution;
830 v_tmp_mod_count := v_tmp_mod_count + 1;
831 IF (v_tmp_alias_val > v_max_alias_val AND
832 p_cnstrt_resolution >0 ) OR
833 (v_tmp_alias_val < v_min_alias_val AND
834 p_cnstrt_resolution <0 ) THEN
835 -- constraint cannot be resolved
836 RETURN ('IGS_CA_INSTBREAK_CONS_UNRSVLD');
837 END IF;
838 v_changed := TRUE;
839 END IF;
840 END IF;
841 EXIT WHEN v_changed = FALSE;
842 END LOOP;
843 -- resolve success or no resolving needed.
844 p_alias_val := v_tmp_alias_val;
845 p_mod_count := v_tmp_mod_count;
846 RETURN null;
847 END IF;
848 EXCEPTION
849 WHEN OTHERS THEN
850 IF c_m_alias_val2%ISOPEN THEN
851 CLOSE c_m_alias_val2;
852 END IF;
853 IF c_instbreak%ISOPEN THEN
854 CLOSE c_instbreak;
855 END IF;
856 RAISE;
857 END;
858 END calpl_inst_break_resolve;
859 ---------------------------------------------------------
860 -- Procedure: calpl_constraint_resolve
861 ---------------------------------------------------------
862 PROCEDURE calpl_constraint_resolve (
863 p_constraint_count IN OUT NOCOPY NUMBER,
864 p_mod_count IN OUT NOCOPY NUMBER,
865 p_alias_val IN OUT NOCOPY IGS_CA_DA_INST.absolute_val%TYPE,
866 p_message_name OUT NOCOPY varchar2)
867 AS
868 BEGIN -- This local procedure is try to resolve the clashed constraint record.
869 DECLARE
870 v_msg_name VARCHAR2(30);
871 v_changed BOOLEAN;
872 CURSOR c_daoc IS
873 SELECT daoc.S_DT_OFFSET_CONSTRAINT_TYPE,
874 daoc.constraint_condition,
875 daoc.constraint_resolution
876 FROM IGS_CA_DA_OFFCNT daoc
877 WHERE daoc.DT_ALIAS = p_dt_alias;
878 BEGIN
879 v_msg_name := NULL;
880 FOR v_daoc_rec IN c_daoc LOOP
881 p_constraint_count := p_constraint_count + 1;
882 IF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE IN ( cst_monday,
883 cst_tuesday,
884 cst_wednesday,
885 cst_thursday,
886 cst_friday,
887 cst_saturday,
888 cst_sunday) THEN
889 IF v_daoc_rec.constraint_condition = cst_must THEN
890 -- Use an inner loop to check and resolve any clash.
891 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) <>
892 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE LOOP
893 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
894 p_mod_count := p_mod_count + 1;
895 END LOOP;
896 ELSE -- NUST NOT
897 -- Use an inner loop to check and resolve any clash.
898 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) =
899 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE LOOP
900 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
901 p_mod_count := p_mod_count + 1;
902 END LOOP;
903 END IF;
904 ELSIF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE = cst_week_day THEN
905 IF v_daoc_rec.constraint_condition = cst_must THEN
906 -- Use an inner loop to check and resolve any clash.
907 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) NOT IN (cst_monday,
908 cst_tuesday,
909 cst_wednesday,
910 cst_thursday,
911 cst_friday) LOOP
912 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
913 p_mod_count := p_mod_count + 1;
914 END LOOP;
915 ELSE -- MUST NOT
916 -- Use an inner loop to check and resolve any clash.
917 WHILE RTRIM(TO_CHAR(p_alias_val,'DAY')) IN ( cst_monday,
918 cst_tuesday,
919 cst_wednesday,
920 cst_thursday,
921 cst_friday) LOOP
922 p_alias_val := p_alias_val + v_daoc_rec.constraint_resolution;
923 p_mod_count := p_mod_count + 1;
924 END LOOP;
925 END IF;
926 ELSIF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE = cst_holiday THEN
927 -- If the constraint type is 'HOLIDAY', check that the date does not clash
928 -- against any date alias instance values in HOLIDAY calendars if the
929 -- condition is 'MUST NOT' or that it matches a date alias instance value
930 -- in a HOLIDAY calendar if the condition is 'MUST'.
931 v_msg_name := calpl_holiday_resolve (
932 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE,
933 v_daoc_rec.constraint_condition,
934 v_daoc_rec.constraint_resolution,
935 p_mod_count,
936 p_alias_val);
937 IF v_msg_name IS NOT NULL THEN
938 p_message_name := v_msg_name;
939 END IF;
940 ELSIF v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE = cst_inst_break THEN
941 --If the constraint type is 'INST BREAK', check that the date does not fall
942 -- between the dates defined by any date alias instance pairs in HOLIDAY
943 -- calendars if the condition is 'MUST NOT' or that it does if the
944 -- condition is 'MUST'.
945 -- Use an inner loop to match the date against all defined DAIP's.
946 -- Find the start and end dates of any DAI Pair.
947 v_msg_name := calpl_inst_break_resolve (
948 v_daoc_rec.S_DT_OFFSET_CONSTRAINT_TYPE,
949 v_daoc_rec.constraint_condition,
950 v_daoc_rec.constraint_resolution,
951 p_mod_count,
952 p_alias_val);
953 IF v_msg_name IS NOT NULL THEN
954 p_message_name := v_msg_name;
955 END IF;
956 END IF;
957 END LOOP; -- daoc loop
958 EXCEPTION
959 WHEN OTHERS THEN
960 IF c_daoc%ISOPEN THEN
961 CLOSE c_daoc;
962 END IF;
963 RAISE;
964 END;
965 END calpl_constraint_resolve;
966 BEGIN
967 -- Set default value.
968 p_message_name := NULL;
969 v_message_name := NULL;
970 v_mod_count :=0;
971 v_constraint_count := 0;
972 v_loop_count := 0;
973 -- 1. Check parameters
974 IF ( p_dt_alias IS NULL OR
975 p_alias_val IS NULL) THEN
976 RETURN p_alias_val;
977 END IF;
978 -- 2. Check if any constraints exist for the date alias.
979 -- If not, no further processing required.
980 IF IGS_CA_VAL_DAOC.calp_val_daoc_exist (
981 p_dt_alias,
982 v_message_name) = TRUE THEN
983 RETURN p_alias_val;
984 END IF;
985 v_alias_val := TRUNC(p_alias_val);
986 -- 3. Set counters to be used to determine if the date constraints are
987 -- unresolvable.
988 WHILE v_loop_count <= 10 LOOP
989 -- 4. Use a loop to select each existiong constraint record.
990 -- and check the constraint against the date and if a clash exists,
991 -- attempt to resolve it.
992 calpl_constraint_resolve (
993 v_constraint_count,
994 v_mod_count,
995 v_alias_val,
996 v_message_name);
997 IF v_message_name <> 0 THEN
998 p_message_name := v_message_name;
999 END IF;
1000 IF v_mod_count > 0 AND
1001 v_constraint_count > 1 THEN
1002 -- Value has been modified by a constraint, so reset the counters and loop
1003 -- through constraints again to ensure that changing the value for one
1004 -- constraint, has not caused to clash with another constraint it had
1005 -- already satisfied.
1006 v_mod_count := 0;
1007 v_constraint_count := 0;
1008 v_loop_count := v_loop_count + 1;
1009 ELSE
1010 RETURN v_alias_val;
1011 END IF;
1012 END LOOP; -- v_loop_count<=10
1013 -- Constraint is deemed unresolvable
1014 p_message_name := 'IGS_CA_ATTEMPT_TORESOLVE_FAIL';
1015 RETURN p_alias_val;
1016 END;
1017 END calp_clc_dao_cnstrt;
1018 --
1019 FUNCTION CALP_CLC_DT_FROM_DA(
1020 p_cal_type IN VARCHAR2 ,
1021 p_ci_sequence_number IN NUMBER ,
1022 p_dt_alias IN VARCHAR2 ,
1023 p_message_name OUT NOCOPY VARCHAR2 )
1024 RETURN DATE AS
1025 v_other_detail VARCHAR(255);
1026 v_alias_value DATE;
1027 v_dt_alias_offset_rec IGS_CA_DA_OFST%ROWTYPE;
1028 v_dt_alias_instance_rec IGS_CA_DA_INST_V%ROWTYPE;
1029 v_date_offset DATE := NULL;
1030 CURSOR c_dt_alias_offset
1031 IS
1032 SELECT *
1033 FROM IGS_CA_DA_OFST
1034 WHERE DT_ALIAS = p_dt_alias;
1035 CURSOR c_dt_alias_instance
1036 IS
1037 SELECT *
1038 FROM IGS_CA_DA_INST_V
1039 WHERE DT_ALIAS = v_dt_alias_offset_rec.offset_dt_alias and
1040 CAL_TYPE = p_cal_type and
1041 ci_sequence_number = p_ci_sequence_number
1042 ORDER BY alias_val ASC;
1043 BEGIN
1044 BEGIN
1045 OPEN c_dt_alias_offset;
1046 LOOP
1047 FETCH c_dt_alias_offset
1048 INTO v_dt_alias_offset_rec;
1049 EXIT WHEN c_dt_alias_offset%NOTFOUND;
1050 END LOOP;
1051 EXCEPTION
1052 WHEN NO_DATA_FOUND THEN
1053 p_message_name := NULL;
1054 v_alias_value := NULL;
1055 RETURN v_alias_value;
1056 END;
1057 BEGIN
1058 OPEN c_dt_alias_instance;
1059 LOOP
1060 FETCH c_dt_alias_instance
1061 INTO v_dt_alias_instance_rec;
1062 EXIT;
1063 END LOOP;
1064 v_alias_value := v_dt_alias_instance_rec.alias_val;
1065 IF (NVL(v_dt_alias_offset_rec.year_offset,0) <> 0) THEN
1066 v_alias_value := add_months(v_dt_alias_instance_rec.alias_val,
1067 (v_dt_alias_offset_rec.year_offset * 12));
1068 END IF;
1069 IF (NVL(v_dt_alias_offset_rec.month_offset,0) <> 0) THEN
1070 v_alias_value := add_months(v_alias_value,
1071 v_dt_alias_offset_rec.month_offset);
1072 END IF;
1073 IF (NVL(v_dt_alias_offset_rec.week_offset,0) <> 0) THEN
1074 v_alias_value := v_alias_value +
1075 (v_dt_alias_offset_rec.week_offset * 7);
1076 END IF;
1077 IF (NVL(v_dt_alias_offset_rec.day_offset,0) <> 0) THEN
1078 v_alias_value := v_alias_value +
1079 v_dt_alias_offset_rec.day_offset;
1080 END IF;
1081 CLOSE c_dt_alias_instance;
1082 EXCEPTION
1083 WHEN NO_DATA_FOUND THEN
1084 p_message_name :='IGS_CA_DTALIAS_NOT_PRESENT';
1085 v_alias_value := NULL;
1086 RETURN v_alias_value;
1087 END;
1088 IF(c_dt_alias_offset%ISOPEN) THEN
1089 CLOSE c_dt_alias_offset;
1090 END IF;
1091 IF(c_dt_alias_instance%ISOPEN) THEN
1092 CLOSE c_dt_alias_instance;
1093 END IF;
1094 RETURN v_alias_value;
1095 END calp_clc_dt_from_da;
1096 --
1097 FUNCTION CALP_CLC_DT_FROM_DAI(
1098 p_ci_sequence_number IN NUMBER ,
1099 p_cal_type IN VARCHAR2 ,
1100 p_dt_alias IN VARCHAR2 ,
1101 p_dai_sequence_number IN NUMBER ,
1102 p_message_name OUT NOCOPY VARCHAR2 )
1103 RETURN DATE AS
1104 gv_other_detail VARCHAR2(255);
1105 BEGIN -- calp_clc_dt_from_dai
1106 -- Calculate a IGS_CA_DA_INST.alias_value from a
1107 -- IGS_CA_DA_INST_OFST.
1108 DECLARE
1109 CURSOR c_daio IS
1110 SELECT daio.DT_ALIAS,
1111 daio.dai_sequence_number,
1112 daio.CAL_TYPE,
1113 daio.ci_sequence_number,
1114 daio.offset_dt_alias,
1115 daio.offset_dai_sequence_number,
1116 daio.offset_cal_type,
1117 daio.offset_ci_sequence_number,
1118 daio.day_offset,
1119 daio.week_offset,
1120 daio.month_offset,
1121 daio.year_offset
1122 FROM IGS_CA_DA_INST_OFST daio
1123 WHERE daio.DT_ALIAS = p_dt_alias AND
1124 daio.dai_sequence_number = p_dai_sequence_number AND
1125 daio.CAL_TYPE = p_cal_type AND
1126 daio.ci_sequence_number = p_ci_sequence_number;
1127 v_c_daio_rec c_daio%ROWTYPE;
1128 CURSOR c_dai IS
1129 SELECT dai.DT_ALIAS,
1130 dai.sequence_number,
1131 dai.CAL_TYPE,
1132 dai.ci_sequence_number,
1133 dai.absolute_val
1134 FROM IGS_CA_DA_INST dai
1135 WHERE dai.DT_ALIAS = v_c_daio_rec.offset_dt_alias AND
1136 dai.sequence_number = v_c_daio_rec.offset_dai_sequence_number AND
1137 dai.CAL_TYPE = v_c_daio_rec.offset_cal_type AND
1138 dai.ci_sequence_number = v_c_daio_rec.offset_ci_sequence_number;
1139 CURSOR c_daioc IS
1140 SELECT daioc.DT_ALIAS,
1141 daioc.dai_sequence_number,
1142 daioc.CAL_TYPE,
1143 daioc.ci_sequence_number
1144 FROM IGS_CA_DA_INST_OFCNT daioc
1145 WHERE daioc.DT_ALIAS = p_dt_alias AND
1146 daioc.dai_sequence_number = p_dai_sequence_number AND
1147 daioc.CAL_TYPE = p_cal_type AND
1148 daioc.ci_sequence_number = p_ci_sequence_number;
1149 v_c_dai_rec c_dai%ROWTYPE;
1150 v_c_daioc_rec c_daioc%ROWTYPE;
1151 v_alias_value DATE DEFAULT NULL;
1152 dt_alias_inst_offset_c_row IGS_CA_DA_INST_OFCNT%ROWTYPE;
1153 v_other_detail VARCHAR(255);
1154 BEGIN
1155 p_message_name :=NULL;
1156 -- Find IGS_CA_DA_INST_OFST
1157 OPEN c_daio;
1158 FETCH c_daio INTO v_c_daio_rec;
1159 IF (c_daio%NOTFOUND) THEN
1160 CLOSE c_daio;
1161 p_message_name := 'IGS_CA_NO_DATE_ALIAS';
1162 v_alias_value := NULL;
1163 RETURN v_alias_value;
1164 END IF;
1165 CLOSE c_daio;
1166 -- Find IGS_CA_DA_INST
1167 OPEN c_dai;
1168 FETCH c_dai INTO v_c_dai_rec;
1169 IF (c_dai%NOTFOUND) THEN
1170 CLOSE c_dai;
1171 v_alias_value := NULL;
1172 RETURN v_alias_value;
1173 END IF;
1174 CLOSE c_dai;
1175 -- Calculate alias_value
1176 IF v_c_dai_rec.absolute_val IS NULL THEN
1177 v_alias_value := calp_clc_dt_from_dai(
1178 v_c_daio_rec.offset_ci_sequence_number,
1179 v_c_daio_rec.offset_cal_type,
1180 v_c_daio_rec.offset_dt_alias,
1181 v_c_daio_rec.offset_dai_sequence_number,
1182 p_message_name);
1183 ELSE
1184 v_alias_value := v_c_dai_rec.absolute_val;
1185 END IF;
1186 IF (NVL(v_c_daio_rec.year_offset,0) <> 0) THEN
1187 v_alias_value :=
1188 add_months(v_alias_value, (v_c_daio_rec.year_offset * 12));
1189 END IF;
1190 IF (NVL(v_c_daio_rec.month_offset,0) <> 0) THEN
1191 v_alias_value :=
1192 add_months(v_alias_value, v_c_daio_rec.month_offset);
1193 END IF;
1194 IF (NVL(v_c_daio_rec.week_offset,0) <> 0) THEN
1195 v_alias_value :=
1196 v_alias_value + (v_c_daio_rec.week_offset * 7);
1197 END IF;
1198 IF (NVL(v_c_daio_rec.day_offset,0) <> 0) THEN
1199 v_alias_value :=
1200 v_alias_value + v_c_daio_rec.day_offset;
1201 END IF;
1202 -- Following code relating to the resolution of offset constraints has been
1203 -- disabled due to performance issues.
1204 -- Test if offset constraints exist.
1205 OPEN c_daioc;
1206 FETCH c_daioc INTO v_c_daioc_rec;
1207 IF (c_daioc%NOTFOUND) THEN
1208 CLOSE c_daioc;
1209 RETURN v_alias_value;
1210 END IF;
1211 CLOSE c_daioc;
1212 -- Resolve the offset constraints.
1213 v_alias_value := calp_clc_daio_cnstrt(
1214 p_dt_alias,
1215 p_dai_sequence_number,
1216 p_cal_type,
1217 p_ci_sequence_number,
1218 v_alias_value,
1219 p_message_name);
1220 RETURN v_alias_value;
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 IF (c_daio%ISOPEN) THEN
1224 CLOSE c_daio;
1225 END IF;
1226 IF (c_dai%ISOPEN) THEN
1227 CLOSE c_dai;
1228 END IF;
1229 RAISE;
1230 END;
1231 END calp_clc_dt_from_dai;
1232 --
1233 FUNCTION CALP_CLC_WK_OF_MONTH(
1234 p_indate IN DATE )
1235 RETURN INTEGER AS
1236 BEGIN
1237 DECLARE
1238 v_first_date DATE;
1239 v_month_str VARCHAR(255);
1240 v_first_dt_str VARCHAR(255);
1241 v_first_day NUMBER;
1242 v_wk_of_month NUMBER;
1243 BEGIN
1244 -- Find out NOCOPY first date/day of this month
1245 v_month_str := TO_CHAR(p_indate, 'MM/YYYY');
1246 v_first_dt_str := '01/' || v_month_str;
1247 v_first_date := TO_DATE(v_first_dt_str, 'DD/MM/YYYY' );
1248 -- Get the day (number) of the week for the first date,
1249 -- giving the offset in days for the whole month.
1250 v_first_day := TO_NUMBER(TO_CHAR(v_first_date,'D'));
1251 v_wk_of_month := TO_NUMBER((TO_CHAR(p_indate, 'DD') +
1252 v_first_day - 1) / 7);
1253 RETURN CEIL(v_wk_of_month);
1254 END;
1255 END calp_clc_wk_of_month;
1256 --
1257 FUNCTION calp_val_ci_cat(
1258 p_cal_type IN VARCHAR2 ,
1259 p_message_name OUT NOCOPY VARCHAR2)
1260 RETURN BOOLEAN AS
1261 v_other_detail VARCHAR2(255);
1262 v_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
1263 CURSOR c_get_closed_ind IS
1264 SELECT closed_ind
1265 FROM IGS_CA_TYPE
1266 WHERE CAL_TYPE = p_cal_type;
1267 BEGIN
1268 p_message_name := NULL;
1269 OPEN c_get_closed_ind;
1270 FETCH c_get_closed_ind INTO v_closed_ind;
1271 IF (c_get_closed_ind%NOTFOUND) THEN
1272 CLOSE c_get_closed_ind;
1273 RETURN TRUE;
1274 END IF;
1275 CLOSE c_get_closed_ind;
1276 IF (v_closed_ind = 'N') THEN
1277 RETURN TRUE;
1278 ELSE
1279 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
1280 RETURN FALSE;
1281 END IF;
1282
1283 END calp_val_ci_cat;
1284 --
1285 FUNCTION CALS_CLC_DT_FROM_DAI(
1286 p_ci_sequence_number IN NUMBER ,
1287 p_cal_type IN VARCHAR2 ,
1288 p_dt_alias IN VARCHAR2 ,
1289 p_dai_sequence_number IN NUMBER )
1290 RETURN DATE AS
1291 v_message_name VARCHAR2(30);
1292 BEGIN
1293 RETURN calp_clc_dt_from_dai(p_ci_sequence_number,
1294 p_cal_type,
1295 p_dt_alias,
1296 p_dai_sequence_number,
1297 v_message_name);
1298 END cals_clc_dt_from_dai;
1299 --
1300 END IGS_CA_GEN_002 ;
[Home] [Help]