1 PACKAGE BODY CZ_RULES_SEQ AS
2 /* $Header: czrseqb.pls 115.26 2004/06/07 16:02:13 rheramba ship $ */
3
4 GLOBAL_RUN_ID INTEGER:=0;
5 EPOCH_BEGIN DATE:=CZ_UTILS.EPOCH_BEGIN_;
6 EPOCH_END DATE:=CZ_UTILS.EPOCH_END_;
7 NULL_VALUE CONSTANT INTEGER:=-1;
8
9 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
10 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
11
12 PROCEDURE LOG_REPORT
13 (p_caller IN VARCHAR2,
14 p_error_message IN VARCHAR2) IS
15
16 l_return BOOLEAN;
17
18 BEGIN
19 l_return := cz_utils.log_report(Msg => p_error_message,
20 Urgency => 1,
21 ByCaller => p_caller,
22 StatusCode => 11276,
23 RunId => GLOBAL_RUN_ID);
24 END;
25
26 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
27 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
28
29 PROCEDURE FND_REPORT
30 (p_message_name IN VARCHAR2,
31 p_token IN VARCHAR2,
32 p_value IN VARCHAR2) IS
33 l_message_text VARCHAR2(32000);
34 BEGIN
35 IF p_token IS NULL THEN
36 l_message_text := CZ_UTILS.GET_TEXT(p_message_name);
37 ELSE
38 l_message_text := CZ_UTILS.GET_TEXT(p_message_name,p_token,p_value);
39 END IF;
40 LOG_REPORT('CZ_RULES_SEQ',l_message_text);
41 END FND_REPORT;
42
43 PROCEDURE FND_REPORT
44 (p_message_name IN VARCHAR2,
45 p_token1 IN VARCHAR2,
46 p_value1 IN VARCHAR2,
47 p_token2 IN VARCHAR2,
48 p_value2 IN VARCHAR2) IS
49 l_message_text VARCHAR2(32000);
50 BEGIN
51 IF p_token1 IS NULL THEN
52 l_message_text := CZ_UTILS.GET_TEXT(p_message_name);
53 ELSIF p_token2 IS NULL THEN
54 l_message_text := CZ_UTILS.GET_TEXT(p_message_name,p_token1,p_value1);
55 ELSE
56 l_message_text := CZ_UTILS.GET_TEXT(p_message_name,p_token1,p_value1,p_token2,p_value2);
57 END IF;
58 LOG_REPORT('CZ_RULES_SEQ',l_message_text);
59 END FND_REPORT;
60
61 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
62 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
63
64 PROCEDURE Initialize(x_err OUT NOCOPY NUMBER) IS
65 BEGIN
66 x_err:=0;
67 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO GLOBAL_RUN_ID FROM dual;
68 END Initialize;
69
70 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
71 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
72
73 PROCEDURE get_Dates
74 (p_eff_set_id IN INTEGER,
75 p_out_from_date OUT NOCOPY DATE,
76 p_out_until_date OUT NOCOPY DATE) IS
77
78 BEGIN
79 SELECT effective_from,effective_until INTO p_out_from_date,p_out_until_date
80 FROM CZ_EFFECTIVITY_SETS WHERE effectivity_set_id=p_eff_set_id;
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 NULL;
85 END;
86
87 PROCEDURE set_Dates
88 (p_rule_id IN INTEGER,
89 p_from_date IN DATE,
90 p_until_date IN DATE,
91 p_eff_set_id IN INTEGER DEFAULT NULL,
92 p_seq_nbr IN INTEGER DEFAULT NULL) IS
93
94 BEGIN
95
96 UPDATE CZ_RULES
97 SET effective_from=NVL(p_from_date,effective_from),
98 effective_until=NVL(p_until_date,effective_until),
99 effectivity_set_id=DECODE(p_eff_set_id,NULL_VALUE,NULL,NULL,effectivity_set_id,p_eff_set_id),
100 seq_nbr=NVL(p_seq_nbr,seq_nbr)
101 WHERE rule_id=p_rule_id AND deleted_flag=NO_FLAG;
102
103 END;
104
105 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
106 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
107
108 PROCEDURE get_LCR
109 (p_model_id IN INTEGER,
110 p_rule_sequence_id IN INTEGER,
111 p_rule_id IN INTEGER,
112 p_out_left_rule_id OUT NOCOPY INTEGER,
113 p_out_right_rule_id OUT NOCOPY INTEGER,
114 p_seq IN OUT NOCOPY INTEGER,
115 p_out_left_seq OUT NOCOPY INTEGER,
116 p_out_right_seq OUT NOCOPY INTEGER,
117 p_out_eff_from OUT NOCOPY DATE,
118 p_out_eff_to OUT NOCOPY DATE,
119 p_out_left_eff_from OUT NOCOPY DATE,
120 p_out_left_eff_to OUT NOCOPY DATE,
121 p_out_right_eff_from OUT NOCOPY DATE,
122 p_out_right_eff_to OUT NOCOPY DATE,
123 p_out_left_set_id OUT NOCOPY INTEGER,
124 p_out_right_set_id OUT NOCOPY INTEGER,
125 p_out_set_id OUT NOCOPY INTEGER) IS
126
127 var_curr_rule_id INTEGER;
128 var_curr_nbr_seq INTEGER;
129 var_set_id INTEGER;
130 var_eff_from DATE;
131 var_eff_to DATE;
132
133 BEGIN
134
135 IF p_seq IS NULL THEN
136 SELECT seq_nbr INTO p_seq FROM CZ_RULES
137 WHERE rule_id=p_rule_id;
138 END IF;
139
140 p_out_left_seq:=NULL_VALUE;
141 p_out_right_seq:=NULL_VALUE;
142 p_out_left_set_id:=NULL_VALUE;
143 p_out_right_set_id:=NULL_VALUE;
144 p_out_set_id:=NULL_VALUE;
145 p_out_left_rule_id:=NULL_VALUE;
146 p_out_right_rule_id:=NULL_VALUE;
147 p_out_eff_from:=NULL;
148 p_out_eff_to:=NULL;
149 p_out_left_eff_from:=NULL;
150 p_out_left_eff_to:=NULL;
151 p_out_right_eff_from:=NULL;
152 p_out_right_eff_to:=NULL;
153
154
155 FOR i IN(SELECT a.rule_folder_id,a.rule_id,a.seq_nbr,p_seq as main_seq,
156 DECODE(a.seq_nbr,p_seq,'C',p_seq-1,'L',p_seq+1,'R','N') as where_in_seq,
157 NVL(a.effectivity_set_id,NULL_VALUE) as effectivity_set_id,
158 DECODE(a.effectivity_set_id,NULL,a.effective_from,NULL_VALUE,a.effective_from,b.effective_from) as effective_from,
159 DECODE(a.effectivity_set_id,NULL,a.effective_until,NULL_VALUE,a.effective_until,b.effective_until) as effective_until
160 FROM CZ_RULES a,CZ_EFFECTIVITY_SETS b
161 WHERE a.rule_folder_id=p_rule_sequence_id
162 AND a.seq_nbr BETWEEN (p_seq-1) AND (p_seq+1)
163 AND a.deleted_flag='0'
164 AND b.effectivity_set_id(+)=a.effectivity_set_id
165 AND b.deleted_flag(+)='0')
166 LOOP
167
168 IF i.where_in_seq='C' THEN
169 p_out_eff_from:=i.effective_from;
170 p_out_eff_to:=i.effective_until;
171 p_out_set_id:=i.effectivity_set_id;
172 END IF;
173
174 IF i.where_in_seq='L' THEN
175 p_out_left_seq:=i.seq_nbr;
176 p_out_left_rule_id:=i.rule_id;
177 p_out_left_eff_from:=i.effective_from;
178 p_out_left_eff_to:=i.effective_until;
179 p_out_left_set_id:=i.effectivity_set_id;
180 END IF;
181
182 IF i.where_in_seq='R' THEN
183 p_out_right_seq:=i.seq_nbr;
184 p_out_right_rule_id:=i.rule_id;
185 p_out_right_eff_from:=i.effective_from;
186 p_out_right_eff_to:=i.effective_until;
187 p_out_right_set_id:=i.effectivity_set_id;
188 END IF;
189
190 END LOOP;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 NULL;
195 END;
196
197 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
198 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
199
200 PROCEDURE get_NEWLCR
201 (p_model_id IN INTEGER,
202 p_rule_sequence_id IN INTEGER,
203 p_seq_nbr IN INTEGER,
204 p_out_left_rule_id OUT NOCOPY INTEGER,
205 p_out_left_eff_from OUT NOCOPY DATE,
206 p_out_left_eff_to OUT NOCOPY DATE,
207 p_out_left_set_id OUT NOCOPY INTEGER,
208 p_out_right_rule_id OUT NOCOPY INTEGER,
209 p_out_right_eff_from OUT NOCOPY DATE,
210 p_out_right_eff_to OUT NOCOPY DATE,
211 p_out_right_set_id OUT NOCOPY INTEGER) IS
212
213 var_curr_rule_id INTEGER;
214 var_curr_nbr_seq INTEGER;
215 var_set_id INTEGER;
216 var_eff_from DATE;
217 var_eff_to DATE;
218
219 BEGIN
220
221 p_out_left_rule_id:=NULL;
222 p_out_left_eff_from:=NULL;
223 p_out_left_eff_to:=NULL;
224 p_out_left_set_id:=NULL;
225
226 p_out_right_rule_id:=NULL;
227 p_out_right_eff_from:=NULL;
228 p_out_right_eff_to:=NULL;
229 p_out_right_set_id:=NULL;
230
231 BEGIN
232 SELECT effective_from,effective_until,effectivity_set_id,rule_id INTO
233 p_out_left_eff_from,p_out_left_eff_to,p_out_left_set_id,p_out_left_rule_id
234 FROM CZ_RULES WHERE devl_project_id=p_model_id AND rule_folder_id=p_rule_sequence_id
235 AND seq_nbr=p_seq_nbr;
236
237 IF p_out_left_set_id IS NOT NULL THEN
238 SELECT effective_from,effective_until INTO
239 p_out_left_eff_from,p_out_left_eff_to
240 FROM CZ_EFFECTIVITY_SETS WHERE effectivity_set_id=p_out_left_set_id;
241 END IF;
242
243 EXCEPTION
244 WHEN NO_DATA_FOUND THEN
245 /* *** Left Border case *** */
246 NULL;
247 END;
248
249 BEGIN
250 SELECT effective_from,effective_until,effectivity_set_id,rule_id INTO
251 p_out_right_eff_from,p_out_right_eff_to,p_out_right_set_id,p_out_right_rule_id
252 FROM CZ_RULES WHERE devl_project_id=p_model_id AND rule_folder_id=p_rule_sequence_id
253 AND seq_nbr=p_seq_nbr+1;
254
255 IF p_out_right_set_id IS NOT NULL THEN
256 SELECT effective_from,effective_until INTO
257 p_out_right_eff_from,p_out_right_eff_to
258 FROM CZ_EFFECTIVITY_SETS WHERE effectivity_set_id=p_out_right_set_id;
259 END IF;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 /* *** Right Border case *** */
264 NULL;
265 END;
266
267 END;
268
269 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
270 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
271
272 PROCEDURE RemoveFromSequence
273 (p_rule_sequence_id IN INTEGER, --ID of the rule sequence
274 p_model_id IN INTEGER, --ID of the model
275 p_rule_id IN INTEGER, --ID of the rule which was removed
276 p_out_err OUT NOCOPY INTEGER , --err flag
277 deleted_flag IN VARCHAR2 -- DEFAULT '0' -- flag if the rule was logically deleted
278 ) IS
279 var_curr_rule_id INTEGER;
280 var_curr_seq INTEGER;
281 var_left_seq INTEGER;
282 var_right_seq INTEGER;
283 var_next_rule_id INTEGER;
284 var_prev_rule_id INTEGER;
285 var_left_rule_id INTEGER;
286 var_right_rule_id INTEGER;
287 var_left_set_id INTEGER;
288 var_right_set_id INTEGER;
289 var_set_id INTEGER;
290 var_eff_from DATE;
291 var_eff_to DATE;
292 var_left_eff_from DATE;
293 var_left_eff_to DATE;
294 var_right_eff_from DATE;
295 var_right_eff_to DATE;
296
297 DO_NOTHING EXCEPTION;
298
299 BEGIN
300
301 p_out_err:=0;
302
303 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO GLOBAL_RUN_ID FROM dual;
304
305 get_LCR
306 (p_model_id,p_rule_sequence_id,p_rule_id,
307 var_left_rule_id,var_right_rule_id,
308 var_curr_seq,var_left_seq,var_right_seq,
309 var_eff_from,var_eff_to,
310 var_left_eff_from,var_left_eff_to,
311 var_right_eff_from,var_right_eff_to,
312 var_left_set_id,var_right_set_id,var_set_id);
313
314 BEGIN
315
316 IF (var_eff_from=EPOCH_END AND var_eff_to=EPOCH_BEGIN) OR
317 var_left_rule_id IS NULL THEN
318 RAISE DO_NOTHING;
319 END IF;
320
321 /* *** handle the case with fisrt rule separately *** */
322 IF var_curr_seq=1 THEN
323 set_Dates(var_right_rule_id,var_eff_from,var_right_eff_to,NULL_VALUE);
324 ELSE
325 set_Dates(var_left_rule_id,var_left_eff_from,var_eff_to,NULL_VALUE);
326 END IF;
327
328 EXCEPTION
329 WHEN DO_NOTHING THEN
330 NULL;
331 END;
332
333 /* *** shift all successors to the left side *** */
334
335 UPDATE CZ_RULES
336 SET seq_nbr=seq_nbr-1
337 WHERE devl_project_id=p_model_id AND seq_nbr>var_curr_seq AND
338 rule_folder_id=p_rule_sequence_id AND deleted_flag=NO_FLAG;
339
340
341 /* *** delete current rule from sequence *** */
342
343 UPDATE CZ_RULES
344 SET seq_nbr=NULL_VALUE,
345 rule_folder_id=NULL_VALUE
346 WHERE rule_id=p_rule_id AND deleted_flag=NO_FLAG;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 p_out_err:=GLOBAL_RUN_ID;
351 LOG_REPORT('CZ_RULES_SEQ.RemoveFromSequence','rule_id='||TO_CHAR(p_rule_id)||' : '||SQLERRM);
352 WHEN OTHERS THEN
353 p_out_err:=GLOBAL_RUN_ID;
354 LOG_REPORT('CZ_RULES_SEQ.RemoveFromSequence','rule_id='||TO_CHAR(p_rule_id)||' : '||SQLERRM);
355 END;
356
357 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
358 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
359
360 PROCEDURE MoveInSequence
361 (p_rule_sequence_id IN INTEGER, -- ID of the rule sequence
362 p_model_id IN INTEGER, -- ID of the model
363 p_rule_id IN INTEGER, -- ID of the rule which is moved within the sequence
364 p_new_sequence IN INTEGER, -- New sequence number of the moved rule
365 p_out_err OUT NOCOPY INTEGER -- err flag
366 ) IS
367
368 var_curr_seq INTEGER;
369 var_left_seq INTEGER;
370 var_right_seq INTEGER;
371 var_left_rule_id INTEGER;
372 var_right_rule_id INTEGER;
373 var_left_set_id INTEGER;
374 var_right_set_id INTEGER;
375 var_set_id INTEGER;
376 var_eff_from DATE;
377 var_eff_to DATE;
378 var_left_eff_from DATE;
379 var_left_eff_to DATE;
380 var_right_eff_from DATE;
381 var_right_eff_to DATE;
382 var_target_left_rule_id NUMBER;
383 var_target_left_eff_from DATE;
384 var_target_left_eff_to DATE;
385 var_target_left_set_id NUMBER;
386 var_target_right_rule_id NUMBER;
390
387 var_target_right_eff_from DATE;
388 var_target_right_eff_to DATE;
389 var_target_right_set_id NUMBER;
391 BEGIN
392
393 Initialize(p_out_err);
394
395 get_LCR
396 (p_model_id,p_rule_sequence_id,p_rule_id,
397 var_left_rule_id,var_right_rule_id,
398 var_curr_seq,var_left_seq,var_right_seq,
399 var_eff_from,var_eff_to,
400 var_left_eff_from,var_left_eff_to,
401 var_right_eff_from,var_right_eff_to,
402 var_left_set_id,var_right_set_id,var_set_id);
403
404 /** assume that we are moving Rn to the place of Rm
405 * ... Rn-1 Rn Rn+1 ... Rm-1 Rm Rm+1
406 *
407 *
408 */
409 IF p_new_sequence > var_curr_seq THEN
410 --
411 -- Rn-1 does not exist
412 --
413 IF var_left_rule_id=NULL_VALUE THEN
414 IF var_eff_from < var_eff_to THEN
415 IF var_eff_to=EPOCH_END AND var_right_eff_to=EPOCH_BEGIN THEN
416 set_Dates(var_right_rule_id,var_eff_from,var_eff_to,NULL_VALUE);
417 ELSE
418 IF var_right_eff_to=EPOCH_BEGIN THEN
419 set_Dates(var_right_rule_id,var_eff_from,var_eff_to,NULL_VALUE);
420 ELSE
421 set_Dates(var_right_rule_id,var_eff_from,var_right_eff_to,NULL_VALUE);
422 END IF;
423 END IF;
424 ELSE
425 --
426 -- if var_eff_from=var_eff_to ( and => = var_right_eff_from )
427 -- then we don't need to set dates for the right rule
428 --
429 NULL;
430 END IF;
431
432 ELSE -- Rn-1 exists
433
434 IF var_eff_from < var_eff_to THEN
435 set_Dates(var_left_rule_id,var_left_eff_from,var_eff_to,NULL_VALUE);
436 ELSE
437 --
438 -- if var_eff_from=var_eff_to ( and => = var_right_eff_from )
439 -- then we don't need to set dates for the right rule
440 --
441 NULL;
442 END IF;
443
444 END IF; -- end of IF var_left_rule_id=NULL_VALUE THEN
445
446 --
447 -- get dates of Rm-1, Rm and Rm+1
448 --
449 get_NEWLCR(p_model_id,p_rule_sequence_id,p_new_sequence,
450 var_target_left_rule_id,var_target_left_eff_from,var_target_left_eff_to,
451 var_target_left_set_id,var_target_right_rule_id,var_target_right_eff_from,
452 var_target_right_eff_to,var_target_right_set_id);
453
454 IF var_target_left_eff_to IN(EPOCH_END,EPOCH_BEGIN) THEN
455 IF var_eff_from=EPOCH_END AND var_eff_to=EPOCH_BEGIN THEN
456 set_Dates(p_rule_id,EPOCH_END,EPOCH_BEGIN,NULL_VALUE);
457 ELSE
458 set_Dates(p_rule_id,EPOCH_END,EPOCH_BEGIN,NULL);
459 END IF;
460 ELSE
461 set_Dates(p_rule_id,var_target_left_eff_to,var_target_left_eff_to,NULL_VALUE);
462 END IF;
463
464 UPDATE CZ_RULES
465 SET seq_nbr=seq_nbr-1
466 WHERE rule_folder_id=p_rule_sequence_id AND
467 seq_nbr > var_curr_seq AND
468 seq_nbr <= p_new_sequence AND
469 deleted_flag=NO_FLAG;
470
471 UPDATE CZ_RULES
472 SET seq_nbr=p_new_sequence
473 WHERE rule_id=p_rule_id;
474
475 END IF;
476
477 /** assume that we are moving Rn to the place of Rm
478 * ... Rm-1 Rm Rm+1 ... Rn-1 Rn Rn+1
479 *
480 *
481 */
482 IF p_new_sequence < var_curr_seq THEN
483 --
484 -- Rn+1 does not exist
485 --
486 IF var_right_rule_id=NULL_VALUE OR var_right_eff_from=EPOCH_END THEN
487 IF var_eff_from < var_eff_to THEN
488 set_Dates(var_left_rule_id,var_left_eff_from,var_eff_to,NULL_VALUE);
489 ELSE
490 --
491 -- if var_eff_from=var_eff_to ( and => = var_right_eff_from )
492 -- then we don't need to set dates for the right rule
493 --
494 NULL;
495 END IF;
496
497 ELSE -- Rn+1 exists
498
499 IF var_eff_from < var_eff_to THEN
500 set_Dates(var_right_rule_id,var_eff_from,var_right_eff_to,NULL_VALUE);
501 ELSE
502 --
503 -- if var_eff_from=var_eff_to ( and => = var_right_eff_from )
504 -- then we don't need to set dates for the right rule
505 --
506 NULL;
507 END IF;
508
509 END IF; -- end of IF var_left_rule_id=NULL_VALUE THEN
510
511 --
512 -- get dates of Rm-1, Rm and Rm+1
513 --
514 get_NEWLCR(p_model_id,p_rule_sequence_id,p_new_sequence,
515 var_target_left_rule_id,var_target_left_eff_from,var_target_left_eff_to,
516 var_target_left_set_id,var_target_right_rule_id,var_target_right_eff_from,
517 var_target_right_eff_to,var_target_right_set_id);
518
519 IF var_target_left_eff_to IN(EPOCH_END,EPOCH_BEGIN) THEN
520 IF var_eff_from=EPOCH_END AND var_eff_to=EPOCH_BEGIN THEN
521 IF var_target_left_eff_from IN(EPOCH_END) THEN
522 set_Dates(p_rule_id,EPOCH_END,EPOCH_BEGIN,NULL_VALUE);
523 ELSIF var_target_left_eff_from IN(EPOCH_BEGIN) AND var_target_left_eff_to=EPOCH_END THEN
524 set_Dates(p_rule_id,EPOCH_BEGIN,EPOCH_END,NULL);
525 ELSE
526 set_Dates(p_rule_id,var_target_left_eff_from,var_target_left_eff_from,NULL);
527 END IF;
531 set_Dates(p_rule_id,EPOCH_END,EPOCH_BEGIN,NULL);
528
529 ELSE
530 IF var_target_left_eff_from IN(EPOCH_END) THEN
532 ELSIF var_target_left_eff_from IN(EPOCH_BEGIN) AND var_target_left_eff_to=EPOCH_END THEN
533 set_Dates(p_rule_id,EPOCH_BEGIN,EPOCH_END,NULL);
534 ELSE
535 set_Dates(p_rule_id,var_target_left_eff_from,var_target_left_eff_from,NULL);
536 END IF;
537
538 END IF;
539 ELSE
540 IF var_target_left_eff_from=EPOCH_BEGIN THEN
541 set_Dates(p_rule_id,var_target_left_eff_from,var_target_left_eff_to,NULL_VALUE);
542 set_Dates(var_target_left_rule_id,var_target_left_eff_to,var_target_left_eff_to,NULL_VALUE);
543 ELSE
544 set_Dates(p_rule_id,var_target_left_eff_from,var_target_left_eff_from,NULL_VALUE);
545 END IF;
546 END IF;
547
548 UPDATE CZ_RULES
549 SET seq_nbr=seq_nbr+1
550 WHERE rule_folder_id=p_rule_sequence_id AND
551 seq_nbr < var_curr_seq AND
552 seq_nbr >= p_new_sequence AND
553 deleted_flag=NO_FLAG;
554
555 UPDATE CZ_RULES
556 SET seq_nbr=p_new_sequence
557 WHERE rule_id=p_rule_id;
558
559 END IF;
560
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 p_out_err:=GLOBAL_RUN_ID;
564 LOG_REPORT('CZ_RULES_SEQ.MoveInSequence','rule_id='||TO_CHAR(p_rule_id)||' : '||SQLERRM);
565 WHEN OTHERS THEN
566 p_out_err:=GLOBAL_RUN_ID;
567 LOG_REPORT('CZ_RULES_SEQ.MoveInSequence','rule_id='||TO_CHAR(p_rule_id)||' : '||SQLERRM);
568 END;
569
570 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
571 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
572
573 PROCEDURE ChangeEffectivity
574 (p_rule_sequence_id IN INTEGER, --ID of the rule sequence
575 p_model_id IN INTEGER, --ID of the model
576 p_rule_id IN INTEGER, --ID of the rule whose effectivity changed
577 p_out_err OUT NOCOPY INTEGER, --err flag
578 p_eff_start_date IN DATE DEFAULT NULL, --New start date
579 p_eff_end_date IN DATE DEFAULT NULL, --New end date
580 p_eff_set_id IN INTEGER -- DEFAULT -1 --New effectivity set ID
581 ) IS
582
583 var_curr_rule_id INTEGER;
584 var_curr_seq INTEGER;
585 var_new_curr_seq INTEGER;
586 var_left_seq INTEGER;
587 var_right_seq INTEGER;
588 var_next_rule_id INTEGER;
589 var_prev_rule_id INTEGER;
590 var_left_rule_id INTEGER;
591 var_right_rule_id INTEGER;
592 var_new_rule_id INTEGER;
593 var_left_set_id INTEGER;
594 var_right_set_id INTEGER;
595 var_set_id INTEGER;
596 var_from_date DATE;
597 var_until_date DATE;
598 var_eff_from DATE;
599 var_eff_to DATE;
600 var_left_eff_from DATE;
601 var_left_eff_to DATE;
602 var_right_eff_from DATE;
603 var_right_eff_to DATE;
604
605 END_OPERATION EXCEPTION;
606
607 BEGIN
608
609 p_out_err:=0;
610
611 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO GLOBAL_RUN_ID FROM dual;
612
613 get_LCR
614 (p_model_id,p_rule_sequence_id,p_rule_id,
615 var_left_rule_id,var_right_rule_id,
616 var_curr_seq,var_left_seq,var_right_seq,
617 var_eff_from,var_eff_to,
618 var_left_eff_from,var_left_eff_to,
619 var_right_eff_from,var_right_eff_to,
620 var_left_set_id,var_right_set_id,var_set_id);
621
622 IF p_eff_start_date=EPOCH_END AND p_eff_end_date=EPOCH_BEGIN THEN
623
624 IF (var_left_eff_from=EPOCH_END AND var_left_eff_to=EPOCH_BEGIN)
625 OR var_left_eff_to=EPOCH_END THEN
626 set_Dates(p_rule_id,NULL,NULL,p_eff_set_id);
627 RAISE END_OPERATION;
628 END IF;
629
630 IF var_eff_from=EPOCH_END AND var_eff_to=EPOCH_BEGIN THEN
631 set_Dates(p_rule_id,NULL,NULL,p_eff_set_id);
632 RAISE END_OPERATION;
633 END IF;
634
635 set_Dates(var_left_rule_id,var_left_eff_from,var_eff_to,NULL_VALUE);
636 set_Dates(p_rule_id,EPOCH_END,EPOCH_BEGIN,p_eff_set_id);
637 RAISE END_OPERATION;
638
639 END IF;
640
641 IF var_left_rule_id IS NULL THEN
642 IF p_eff_end_date=var_right_eff_from THEN
643 NULL;
644 ELSE
645 set_Dates(var_right_rule_id,p_eff_end_date,var_right_eff_to,NULL_VALUE);
646 END IF;
647 set_Dates(p_rule_id,p_eff_start_date,p_eff_end_date,p_eff_set_id);
648 RAISE END_OPERATION;
649 END IF;
650
651 IF var_right_rule_id IS NULL THEN
652 IF p_eff_start_date=var_left_eff_to THEN
653 NULL;
654 ELSE
655 set_Dates(var_left_rule_id,var_left_eff_from,p_eff_start_date,NULL_VALUE);
656 END IF;
657 set_Dates(p_rule_id,p_eff_start_date,p_eff_end_date,p_eff_set_id);
658 RAISE END_OPERATION;
659 END IF;
660
661 IF var_eff_from=EPOCH_END AND var_eff_to=EPOCH_BEGIN THEN
662 IF p_eff_start_date=var_left_eff_to THEN
663 set_Dates(var_left_rule_id,var_left_eff_from,p_eff_start_date);
664 ELSE
665 set_Dates(var_left_rule_id,var_left_eff_from,p_eff_start_date,NULL_VALUE);
666 END IF;
667 set_Dates(p_rule_id,p_eff_start_date,p_eff_end_date,p_eff_set_id);
671 IF p_eff_start_date=var_left_eff_to THEN
668 RAISE END_OPERATION;
669 END IF;
670
672 set_Dates(var_left_rule_id,var_left_eff_from,p_eff_start_date);
673 ELSE
674 set_Dates(var_left_rule_id,var_left_eff_from,p_eff_start_date,NULL_VALUE);
675 END IF;
676
677 IF var_right_eff_from=EPOCH_END AND var_right_eff_to=EPOCH_BEGIN THEN
678 NULL;
679 ELSE
680 IF p_eff_end_date=var_right_eff_from THEN
681 set_Dates(var_right_rule_id,p_eff_end_date,var_right_eff_to);
682 ELSE
683 set_Dates(var_right_rule_id,p_eff_end_date,var_right_eff_to,NULL_VALUE);
684 END IF;
685 END IF;
686
687 set_Dates(p_rule_id,p_eff_start_date,p_eff_end_date,p_eff_set_id);
688
689 EXCEPTION
690 WHEN END_OPERATION THEN
691 NULL;
692 WHEN NO_DATA_FOUND THEN
693 p_out_err:=1;
694 LOG_REPORT('CZ_RULES_SEQ.ChangeEffectivity','rule_id='||TO_CHAR(p_rule_id)||' : '||SQLERRM);
695 WHEN OTHERS THEN
696 p_out_err:=2;
697 LOG_REPORT('CZ_RULES_SEQ.ChangeEffectivity','rule_id='||TO_CHAR(p_rule_id)||' : '||SQLERRM);
698 END;
699
700 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
701 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
702
703 PROCEDURE PropagateEffectivitySetChange
704 (p_effectivity_set_id IN INTEGER, --ID of the effectivity set
705 p_new_start_date IN DATE, --new start date
706 p_new_end_date IN DATE, --new end date
707 p_out_err OUT NOCOPY INTEGER --Err flag
708 ) IS
709
710 var_start_date DATE;
711 var_end_date DATE;
712
713 var_curr_rule_id INTEGER;
714 var_curr_seq INTEGER;
715 var_left_seq INTEGER;
716 var_right_seq INTEGER;
717 var_next_rule_id INTEGER;
718 var_prev_rule_id INTEGER;
719 var_left_rule_id INTEGER;
720 var_right_rule_id INTEGER;
721 var_new_rule_id INTEGER;
722 var_left_set_id INTEGER;
723 var_right_set_id INTEGER;
724 var_set_id INTEGER;
725 var_name CZ_RULES.name%TYPE;
726 var_eff_name CZ_EFFECTIVITY_SETS.name%TYPE;
727 var_rule_name CZ_RULES.name%TYPE;
728
729 var_eff_from DATE;
730 var_eff_to DATE;
731 var_left_eff_from DATE;
732 var_left_eff_to DATE;
733 var_right_eff_from DATE;
734 var_right_eff_to DATE;
735
736 LEFT_OVERLAP EXCEPTION;
737 RIGHT_OVERLAP EXCEPTION;
738
739 BEGIN
740
741 FND_MSG_PUB.initialize;
742 p_out_err:=0;
743
744 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO GLOBAL_RUN_ID FROM dual;
745
746 UPDATE CZ_EFFECTIVITY_SETS
747 SET effective_from=p_new_start_date,
748 effective_until=p_new_end_date
749 WHERE effectivity_set_id=p_effectivity_set_id RETURNING name INTO var_eff_name;
750
751 FOR k IN ( SELECT a.* FROM CZ_RULES a, CZ_RULE_FOLDERS b
752 WHERE a.rule_folder_id = b.rule_folder_id
753 AND b.folder_type=1 AND b.deleted_flag=NO_FLAG
754 AND a.effectivity_set_id=p_effectivity_set_id
755 AND a.deleted_flag=NO_FLAG)
756 LOOP
757
758 var_curr_seq:=k.seq_nbr;
759
760 get_LCR
761 (k.devl_project_id,k.rule_folder_id,k.rule_id,
762 var_left_rule_id,var_right_rule_id,
763 var_curr_seq,var_left_seq,var_right_seq,
764 var_eff_from,var_eff_to,var_left_eff_from,var_left_eff_to,
765 var_right_eff_from,var_right_eff_to,
766 var_left_set_id,var_right_set_id,var_set_id);
767
768 IF p_new_start_date<var_left_eff_from THEN
769 var_rule_name:=k.name;
770 RAISE LEFT_OVERLAP;
771 END IF;
772
773 IF p_new_end_date>var_right_eff_to
774 AND var_right_eff_to<>CZ_UTILS.EPOCH_BEGIN_ THEN
775 RAISE RIGHT_OVERLAP;
776 END IF;
777
778 IF (var_left_set_id<>NULL_VALUE AND var_left_set_id IS NOT NULL) AND
779 (var_left_eff_from=CZ_UTILS.EPOCH_END_ AND var_left_eff_to=CZ_UTILS.EPOCH_BEGIN_) THEN
780
781 NULL;
782 ELSE
783 set_Dates(var_left_rule_id,var_left_eff_from,p_new_start_date,NULL_VALUE);
784 END IF;
785
786
787 IF (var_right_set_id<>NULL_VALUE AND var_right_set_id IS NOT NULL) AND
788 (var_right_eff_from=CZ_UTILS.EPOCH_END_ AND var_right_eff_to=CZ_UTILS.EPOCH_BEGIN_) THEN
789 NULL;
790 ELSE
791 set_Dates(var_right_rule_id,p_new_end_date,var_right_eff_to,NULL_VALUE);
792 END IF;
793
794 END LOOP;
795
796 EXCEPTION
797 WHEN LEFT_OVERLAP THEN
798 p_out_err:=GLOBAL_RUN_ID;
799 SELECT name INTO var_name FROM CZ_RULES WHERE rule_id=var_left_rule_id;
800 IF var_left_eff_from=EPOCH_END AND var_left_eff_to=EPOCH_BEGIN THEN
801 FND_REPORT('CZ_UNABLE_TO_ACTIVATE_RULE','RULENAME',var_name);
802 ELSE
803 FND_REPORT('CZ_EFF_SET_PREV_OVERLAP','EFFNAME',var_eff_name,'RULENAME',var_name);
804 END IF;
805 WHEN RIGHT_OVERLAP THEN
806 p_out_err:=GLOBAL_RUN_ID;
807 SELECT name INTO var_name FROM CZ_RULES WHERE rule_id=var_right_rule_id;
808 FND_REPORT('CZ_EFF_SET_NEXT_OVERLAP','EFFNAME',var_eff_name,'RULENAME',var_name);
812 LOG_REPORT('CZ_RULES_SEQ','effectivity "'||var_eff_name||'" : '||SQLERRM);
809 WHEN NO_DATA_FOUND THEN
810 p_out_err:=GLOBAL_RUN_ID;
811 SELECT name INTO var_name FROM CZ_RULES WHERE rule_id=var_right_rule_id;
813 WHEN OTHERS THEN
814 p_out_err:=GLOBAL_RUN_ID;
815 LOG_REPORT('CZ_RULES_SEQ','effectivity "'||var_eff_name||'" : '||SQLERRM);
816 END;
817
818 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
819 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
820
821 END;
[Home] [Help]