[Home] [Help]
PACKAGE BODY: APPS.OKC_CONDITION_EVAL_PVT
Source
1 package body okc_condition_eval_pvt as
2 /* $Header: OKCRCEVB.pls 120.3 2005/07/18 09:30:56 pnayani noship $ */
3
4 --l_debug VARCHAR2(1) := 'Y';
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 /*----------------------------------------------------------------------------+
8 |This package contains APIs for condition evaluation. It handles 4 major tasks|
9 |1. Evaluates condition headers and lines for a given action. |
10 |2.Creates condition occurrence and action attribute values for true |
11 |conditions. |
12 |3.Identify outcomes for true conditions. |
13 |4.Put the outcomes on the outcome queue. |
14 +----------------------------------------------------------------------------*/
15 --
16 -- Package Variables
17 --
18
19 -- This function takes the action attribute element name
20 -- and returns the corresponding aae_id
21
22 FUNCTION get_attribute_id (
23 p_acn_id IN okc_actions_b.id%TYPE,
24 p_element_name IN okc_action_attributes_b.element_name%TYPE
25 )
26 RETURN NUMBER
27 IS
28 CURSOR aae_cur IS
29 SELECT id,element_name
30 FROM okc_action_attributes_b
31 WHERE acn_id = p_acn_id;
32 aae_rec aae_cur%ROWTYPE;
33 x_aae_id NUMBER;
34
35 --
36 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_attribute_id';
37 --
38
39 BEGIN
40
41 IF (l_debug = 'Y') THEN
42 okc_debug.Set_Indentation(l_proc);
43 okc_debug.log('10: Entering ',2);
44 END IF;
45
46 FOR aae_rec IN aae_cur LOOP
47 IF UPPER(aae_rec.element_name) = UPPER(p_element_name) THEN
48 x_aae_id := aae_rec.id;
49 END IF;
50 END LOOP;
51
52 IF (l_debug = 'Y') THEN
53 okc_debug.log('1000: Leaving ',2);
54 okc_debug.Reset_Indentation;
55 END IF;
56
57
58 RETURN(x_aae_id);
59 END get_attribute_id;
60
61
62 -- R12 changed function to get counter reading instead of counter_grp_log_id
63 --This function accepts cnh_id,acn_id and returns counter_reading
64 FUNCTION get_last_counter_reading(
65 p_cnh_id IN okc_condition_headers_b.id%TYPE
66 ,p_acn_id IN okc_actions_b.id%TYPE)
67 RETURN NUMBER
68 IS
69 l_counter_reading NUMBER ;
70 l_aae_id NUMBER;
71 CURSOR aav_cur(X IN NUMBER)
72 IS
73 SELECT aav.value
74 FROM okc_action_att_vals aav
75 ,okc_condition_occurs coe
76 WHERE coe.id = aav.coe_id
77 AND coe.cnh_id = p_cnh_id
78 AND aav.aae_id = X
79 ORDER BY coe.datetime desc;
80 aav_rec aav_cur%ROWTYPE;
81
82 --
83 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_counter_reading';
84 --
85
86 BEGIN
87
88 IF (l_debug = 'Y') THEN
89 okc_debug.Set_Indentation(l_proc);
90 okc_debug.log('10: Entering ',2);
91 END IF;
92
93 l_aae_id := get_attribute_id(p_acn_id,'COUNTER_READING');
94 IF aav_cur%ISOPEN THEN
95 CLOSE aav_cur;
96 END IF;
97 OPEN aav_cur(l_aae_id);
98 FETCH aav_cur INTO aav_rec;
99 IF aav_cur%NOTFOUND THEN
100
101 IF (l_debug = 'Y') THEN
102 okc_debug.log('100: Leaving ',2);
103 okc_debug.Reset_Indentation;
104 END IF;
105
106
107 RETURN(l_counter_reading);
108 ELSE
109 l_counter_reading := aav_rec.value;
110
111 IF (l_debug = 'Y') THEN
112 okc_debug.log('200: Leaving ',2);
113 okc_debug.Reset_Indentation;
114 END IF;
115
116
117 RETURN(l_counter_reading);
118 END IF;
119 CLOSE aav_cur;
120
121 IF (l_debug = 'Y') THEN
122 okc_debug.log('300: Leaving ',2);
123 okc_debug.Reset_Indentation;
124 END IF;
125
126
127 RETURN(l_counter_reading);
128 END;
129
130 -- This function accepts the counter_id and
131 -- returns the latest reading of the counter
132 FUNCTION get_current_counter_val(
133 p_counter_id IN NUMBER)
134 RETURN NUMBER
135 IS
136 l_counter_value CSI_COUNTER_READINGS.counter_reading%TYPE ;
137 CURSOR cv_cur
138 IS
139 select net_reading counter_reading
140 from csi_counter_readings
141 where counter_id = p_counter_id
142 and nvl(disabled_flag,'N') = 'N'
143 order by value_timestamp desc;
144 /*SELECT cv.counter_reading
145 FROM okx_counter_values_v cv,
146 okx_counters_v c
147 WHERE cv.counter_id = c.counter_id
148 AND cv.counter_grp_log_id = p_counter_grp_log_id
149 AND (cv.counter_id = p_counter_id OR
150 c.created_from_counter_tmpl_id = p_counter_id);*/
151 cv_rec cv_cur%ROWTYPE;
152
153 --
154 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_current_counter_val';
155 --
156
157 BEGIN
158
159 IF (l_debug = 'Y') THEN
160 okc_debug.Set_Indentation(l_proc);
161 okc_debug.log('10: Entering ',2);
162 END IF;
163
164 IF cv_cur%ISOPEN THEN
165 CLOSE cv_cur;
166 END IF;
167 OPEN cv_cur;
168 FETCH cv_cur INTO cv_rec;
169 IF cv_cur%NOTFOUND THEN
170
171 IF (l_debug = 'Y') THEN
172 okc_debug.log('1000: Leaving ',2);
173 okc_debug.Reset_Indentation;
174 END IF;
175
176
177 RETURN(l_counter_value);
178 ELSE
179 l_counter_value := cv_rec.counter_reading;
180
181 IF (l_debug = 'Y') THEN
182 okc_debug.log('2000: Leaving ',2);
183 okc_debug.Reset_Indentation;
184 END IF;
185
186
187 RETURN(l_counter_value);
188 END IF;
189 CLOSE cv_cur;
190
191 IF (l_debug = 'Y') THEN
192 okc_debug.log('3000: Leaving ',2);
193 okc_debug.Reset_Indentation;
194 END IF;
195
196
197 RETURN(l_counter_value);
198
199 END;
200
201
202 -- This function takes the action attribute element name loops thro
203 -- message table and gives out the corresponding element value
204
205 FUNCTION get_attribute_value (
206 p_element_name IN okc_action_attributes_b.element_name%TYPE,
207 p_msg_tab IN okc_aq_pvt.msg_tab_typ
208 )
209 RETURN VARCHAR2
210 IS
211 x_element_value VARCHAR2(32000);
212
213 --
214 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_attribute_value';
215 --
216
217 BEGIN
218
219 IF (l_debug = 'Y') THEN
220 okc_debug.Set_Indentation(l_proc);
221 okc_debug.log('10: Entering ',2);
222 END IF;
223
224 IF p_element_name IS NOT NULL THEN
225 FOR i IN 1..p_msg_tab.count LOOP
226 IF UPPER(p_element_name) = UPPER(p_msg_tab(i).element_name) THEN
227 x_element_value := p_msg_tab(i).element_value;
228 END IF;
229 END LOOP;
230 END IF;
231
232 IF (l_debug = 'Y') THEN
233 okc_debug.log('1000: Leaving ',2);
234 okc_debug.Reset_Indentation;
235 END IF;
236
237
238 RETURN(x_element_value);
239
240 END get_attribute_value;
241
242 -- This function takes the action id and msg table
243 -- and returns attribute value of date datatype which is
244 -- the date of intrest for that action
245
246 FUNCTION get_datetime (
247 p_acn_id IN okc_actions_b.id%TYPE,
248 p_msg_tab IN okc_aq_pvt.msg_tab_typ
249 )
250 RETURN DATE
251 IS
252 CURSOR aae_cur IS
253 SELECT element_name,format_mask
254 FROM okc_action_attributes_b
255 WHERE acn_id = p_acn_id
256 AND data_type = 'DATE'
257 AND date_of_interest_yn = 'Y'
258 AND rownum = 1;
259 aae_rec aae_cur%ROWTYPE;
260 x_datetime DATE;
261 x_char_datetime VARCHAR2(20);
262 -- The following format was introduced by msengupt
263 l_decode_format_mask VARCHAR2(20) := 'DD-MON-YY';
264
265 --
266 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_datetime';
267 --
268
269 BEGIN
270
271 IF (l_debug = 'Y') THEN
272 okc_debug.Set_Indentation(l_proc);
273 okc_debug.log('10: Entering ',2);
274 END IF;
275
276 OPEN aae_cur;
277 FETCH aae_cur INTO aae_rec;
278 IF aae_cur%NOTFOUND THEN
279 x_datetime := SYSDATE;
280 ELSE
281 x_char_datetime := get_attribute_value(aae_rec.element_name,
282 p_msg_tab);
283 IF aae_rec.format_mask IS NULL THEN
284 x_datetime := to_date(x_char_datetime, l_decode_format_mask);
285 ELSE
286 x_datetime := to_date(x_char_datetime,aae_rec.format_mask);
287 END IF;
288 END IF;
289 CLOSE aae_cur;
290
291 IF (l_debug = 'Y') THEN
292 okc_debug.log('1000: Leaving ',2);
293 okc_debug.Reset_Indentation;
294 END IF;
295
296
297 RETURN(x_datetime);
298 END;
299
300 -- Builds function expression with parameters and parameter
301 -- values and returns the executable function for FEX in condition lines
302 FUNCTION get_function_value (
303 p_cnl_id IN okc_condition_lines_b.id%TYPE,
304 p_pdf_id IN okc_process_defs_b.id%TYPE,
305 p_msg_tab IN okc_aq_pvt.msg_tab_typ
306 )
307 RETURN VARCHAR2
308 IS
309 x_function_value VARCHAR2(1);
310 v_retval VARCHAR2(1);
311 l_string VARCHAR2(32000);
312 func_string VARCHAR2(32000);
313 CURSOR fep_cur IS
314 select id
315 from okc_function_expr_params
316 where cnl_id = p_cnl_id;
317 fep_rec fep_cur%ROWTYPE;
318 CURSOR cnl_cur IS
319 select pdf.package_name||'.'||pdf.procedure_name function_name
320 from okc_condition_lines_b cnl,
321 okc_process_defs_b pdf
322 where cnl.pdf_id = pdf.id
323 and cnl.id = p_cnl_id
324 and UPPER(pdf.usage) = 'FUNCTION';
325 cnl_rec cnl_cur%ROWTYPE;
326 CURSOR pdf_cur IS
327 SELECT pdf.package_name||'.'||pdf.procedure_name function_name,
328 pdp.name parameter_name,
329 aae.element_name element_name,
330 pdp.default_value default_value,
331 decode(pdp.data_type,'CHAR','''',
332 'DATE','''',NULL) prefix_param,
333 fep.value fep_value
334 FROM okc_process_defs_b pdf,
335 okc_process_def_parameters_v pdp,
336 okc_function_expr_params fep,
337 okc_action_attributes_b aae
338 WHERE pdf.id = p_pdf_id
339 AND pdp.pdf_id = pdf.id
340 AND pdp.id = fep.pdp_id
341 AND fep.cnl_id = p_cnl_id
342 AND fep.aae_id = aae.id(+)
343 AND UPPER(pdf.usage) = 'FUNCTION';
344 pdf_rec pdf_cur%ROWTYPE;
345 bind_ctr NUMBER := 0;
346 TYPE bind_var_rec IS RECORD (ctr NUMBER,value VARCHAR2(100));
347 TYPE bind_var_table IS TABLE OF bind_var_rec;
348 bind_var_tab bind_var_table:= bind_var_table();
349 bind_cur integer;
350 i integer;
351 BEGIN
352 ----------------------
353 IF fep_cur%ISOPEN THEN
354 CLOSE fep_cur;
355 END IF;
356 ----------------------
357 OPEN fep_cur;
358 FETCH fep_cur INTO fep_rec;
359 IF fep_cur%notfound THEN
360 ------------------------
361 IF cnl_cur%ISOPEN THEN
362 CLOSE cnl_cur;
363 END IF;
364 ------------------------
365 OPEN cnl_cur;
366 FETCH cnl_cur INTO cnl_rec;
367 IF cnl_cur%FOUND THEN
368 l_string := cnl_rec.function_name;
369 ELSE
370 x_function_value := 'F';
371
372 IF (l_debug = 'Y') THEN
373 okc_debug.log('1000: Leaving ',2);
374 okc_debug.Reset_Indentation;
375 END IF;
376
377
378 RETURN(x_function_value);
379 END IF;
380 CLOSE cnl_cur;
381 ELSE
382 IF pdf_cur%ISOPEN THEN
383 CLOSE pdf_cur;
384 END IF;
385 OPEN pdf_cur;
386 FETCH pdf_cur INTO pdf_rec;
387 IF pdf_cur%NOTFOUND THEN
388 x_function_value := 'F';
389
390 IF (l_debug = 'Y') THEN
391 okc_debug.log('2000: Leaving ',2);
392 okc_debug.Reset_Indentation;
393 END IF;
394
395
396 RETURN(x_function_value);
397 ELSE
398 ---** Bug#2934909 converting execute immediate into dbms_sql to bind input variables
399 -- ** as per new coding standards to avoid data security problems.
400 bind_var_tab.extend;
401 bind_ctr := bind_ctr+1;
402 l_string := pdf_rec.function_name || '('||pdf_rec.parameter_name||
403 '=> '||':'||bind_ctr;
404 bind_var_tab(bind_ctr).ctr := bind_ctr;
405 bind_var_tab(bind_ctr).value := replace(NVL(pdf_rec.fep_value,
406 NVL(get_attribute_value( pdf_rec.element_name, p_msg_tab),
407 NVL(pdf_rec.default_value,' '))),'''','''''');
408 LOOP
409 FETCH pdf_cur INTO pdf_rec;
410 IF pdf_cur%NOTFOUND THEN
411 EXIT;
412 ELSE
413 bind_var_tab.extend;
414 bind_ctr := bind_ctr+1;
415 l_string := l_string||','|| pdf_rec.parameter_name||
416 '=> '||':'||bind_ctr;
417 bind_var_tab(bind_ctr).ctr := bind_ctr;
418 bind_var_tab(bind_ctr).value := replace(NVL(pdf_rec.fep_value,
419 NVL(get_attribute_value( pdf_rec.element_name, p_msg_tab),
420 NVL(pdf_rec.default_value,' '))),'''','''''');
421 END IF;
422 END LOOP;
423 l_string := ':retval := '||l_string||');';
424 END IF;
425 CLOSE pdf_cur;
426 END IF;
427 -- return T or F
428 IF l_string IS NOT NULL THEN
429 func_string := 'begin '||l_string||' end;';
430 ---** Bug#2934909 converting execute immediate into dbms_sql to bind input variables
431 -- ** as per new coding standards to avoid data security problems.
432 BEGIN
433 bind_cur := dbms_sql.open_cursor;
434 dbms_sql.parse(bind_cur, func_string, dbms_sql.native);
435 dbms_sql.bind_variable(bind_cur,'retval','A');
436 FOR i IN 1.. bind_var_tab.COUNT LOOP
437 dbms_sql.bind_variable(bind_cur,to_char(bind_var_tab(i).ctr),bind_var_tab(i).value);
438 END LOOP;
439 x_function_value := dbms_sql.execute(bind_cur);
440 dbms_sql.variable_value(bind_cur,'retval',v_retval);
441 dbms_sql.close_cursor(bind_cur);
442 END;
443 ---** Bug#2934909 END **----------
444 --EXECUTE IMMEDIATE func_string into x_function_value;
445
446 IF (l_debug = 'Y') THEN
447 okc_debug.log('3000: Leaving ',2);
448 okc_debug.Reset_Indentation;
449 END IF;
450
451 RETURN(v_retval);
452 END IF;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 -- close the cursor
457 dbms_sql.close_cursor(bind_cur);
458 RETURN(v_retval);
459 RAISE;
460
461 END get_function_value;
462
463 /* Master counters are not supported
464 FUNCTION get_master_counter_value (
465 p_counter_master_id IN NUMBER
466 )
467 RETURN VARCHAR2
468 IS
469 CURSOR counter_master_cur IS
470 SELECT c.counter_id counter_id,
471 cv.counter_reading counter_reading
472 FROM okx_counter_values_v cv, okx_counters_v c
473 WHERE cv.counter_id = c.counter_id
474 AND c.ctr_val_max_seq_no = cv.seq_no
475 AND c.created_from_counter_tmpl_id = p_counter_master_id;
476 x_counter_value VARCHAR2(2000) ;
477 counter_master_rec counter_master_cur%ROWTYPE;
478
479 --
480 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_master_counter_value';
481 --
482
483 BEGIN
484
485 IF (l_debug = 'Y') THEN
486 okc_debug.Set_Indentation(l_proc);
487 okc_debug.log('10: Entering ',2);
488 END IF;
489
490 FOR counter_master_rec IN counter_master_cur LOOP
491 IF counter_master_cur%NOTFOUND THEN
492
493 IF (l_debug = 'Y') THEN
494 okc_debug.log('1000: Leaving ',2);
495 okc_debug.Reset_Indentation;
496 END IF;
497
498
499 RETURN(x_counter_value);
500 ELSE
501 x_counter_value := counter_master_rec.counter_reading;
502 END IF;
503 END LOOP;
504
505 IF (l_debug = 'Y') THEN
506 okc_debug.log('2000: Leaving ',2);
507 okc_debug.Reset_Indentation;
508 END IF;
509
510
511 RETURN (x_counter_value);
512 END get_master_counter_value;*/
513 -- this function returns latest counter reading.
514 FUNCTION get_counter_value (
515 p_counter_id IN NUMBER
516 )
517 RETURN VARCHAR2
518 IS
519 CURSOR counter_cur IS
520 select net_reading counter_reading
521 from csi_counter_readings
522 where counter_id = p_counter_id
523 and nvl(disabled_flag,'N') = 'N'
524 order by value_timestamp desc;
525 /*SELECT c.counter_id counter_id,
526 cv.counter_reading counter_reading
527 FROM okx_counter_values_v cv, okx_counters_v c
528 WHERE c.counter_id = cv.counter_id
529 AND c.ctr_val_max_seq_no = cv.seq_no
530 AND c.counter_id = p_counter_id;*/
531 x_counter_value VARCHAR2(2000) ;
532 counter_rec counter_cur%ROWTYPE;
533
534 --
535 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'get_counter_value';
536 --
537
538 BEGIN
539
540 IF (l_debug = 'Y') THEN
541 okc_debug.Set_Indentation(l_proc);
542 okc_debug.log('10: Entering ',2);
543 END IF;
544
545 --FOR counter_rec IN counter_cur LOOP
546 OPEN counter_cur;
547 FETCH counter_cur INTO counter_rec;
548 IF counter_cur%NOTFOUND THEN
549
550 IF (l_debug = 'Y') THEN
551 okc_debug.log('1000: Leaving ',2);
552 okc_debug.Reset_Indentation;
553 END IF;
554
555 RETURN(x_counter_value);
556 ELSE
557 x_counter_value := counter_rec.counter_reading;
558 END IF;
559 CLOSE counter_cur;
560
561 IF (l_debug = 'Y') THEN
562 okc_debug.log('2000: Leaving ',2);
563 okc_debug.Reset_Indentation;
564 END IF;
565
566
567 RETURN (x_counter_value);
568 END get_counter_value;
569
570
571 -- This function evaluates the counter condition line and returns
572 -- a string of 'T = F/T' to be evaluated by condition lines procedure
573 FUNCTION evaluate_counter_condition (
574 p_cnl_id IN okc_condition_lines_b.id%TYPE
575 ,p_previous_counter_value IN csi_counter_readings.counter_reading%TYPE
576 ,p_current_counter_value IN csi_counter_readings.counter_reading%TYPE
577 )
578 RETURN VARCHAR2
579 IS
580 CURSOR cnl_cur
581 IS
582 SELECT left_counter_id
583 ,right_operand
584 ,tolerance
585 ,start_at
586 FROM okc_condition_lines_b cnl
587 WHERE id = p_cnl_id;
588 cnl_rec cnl_cur%ROWTYPE;
589 l_value NUMBER;
590 l_string VARCHAR2(20) := '''F'' = ''T''';
591
592
593 --
594 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluate_counter_condition';
595 --
596
597 BEGIN
598
599 IF (l_debug = 'Y') THEN
600 okc_debug.Set_Indentation(l_proc);
601 okc_debug.log('10: Entering ',2);
602 END IF;
603
604 IF cnl_cur%ISOPEN THEN
605 CLOSE cnl_cur;
606 END IF;
607 OPEN cnl_cur;
608 FETCH cnl_cur INTO cnl_rec;
609 IF cnl_cur%FOUND THEN
610 -- commented as master counters are not supported
611 /*ELSIF cnl_rec.left_ctr_master_id IS NOT NULL THEN
612 l_curr_val := get_master_counter_value(cnl_rec.left_ctr_master_id); */
613 l_value := (((p_current_counter_value - NVL(p_previous_counter_value,
614 cnl_rec.start_at))
615 - NVL(cnl_rec.tolerance,0)) / cnl_rec.right_operand);
616 IF l_value > 1 THEN
617 l_string := '''T'' = ''T''';
618 ELSE
619 l_string := '''F'' = ''T''';
620 END IF;
621 END IF;
622 CLOSE cnl_cur;
623
624 IF (l_debug = 'Y') THEN
625 okc_debug.log('1000: Leaving ',2);
626 okc_debug.Reset_Indentation;
627 END IF;
628
629
630 RETURN(l_string);
631 END evaluate_counter_condition;
632
633 -- This function checks if the condition has been already evaluated
634 FUNCTION evaluated_once (p_cnh_id IN NUMBER)
635 RETURN BOOLEAN IS
636
637 CURSOR coe_cur IS
638 SELECT 'X'
639 FROM okc_condition_occurs coe
640 WHERE coe.cnh_id = p_cnh_id;
641 coe_rec coe_cur%ROWTYPE;
642
643
644
645 --
646 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluated_once';
647 --
648
649 BEGIN
650
651 IF (l_debug = 'Y') THEN
652 okc_debug.Set_Indentation(l_proc);
653 okc_debug.log('10: Entering ',2);
654 END IF;
655
656
657 IF coe_cur%ISOPEN THEN
658 CLOSE coe_cur;
659 END IF;
660 OPEN coe_cur;
661 FETCH coe_cur INTO coe_rec;
662 IF coe_cur%FOUND THEN
663
664 IF (l_debug = 'Y') THEN
665 okc_debug.log('1000: Leaving ',2);
666 okc_debug.Reset_Indentation;
667 END IF;
668
669
670 RETURN(TRUE);
671 ELSE
672
673 IF (l_debug = 'Y') THEN
674 okc_debug.log('2000: Leaving ',2);
675 okc_debug.Reset_Indentation;
676 END IF;
677
678
679 RETURN(FALSE);
680 END IF;
681 CLOSE coe_cur;
682
683 IF (l_debug = 'Y') THEN
684 okc_debug.log('3000: Leaving ',2);
685 okc_debug.Reset_Indentation;
686 END IF;
687
688
689 RETURN(FALSE);
690
691 END;
692
693 PROCEDURE evaluate_condition_lines(
694 p_cnh_id IN okc_condition_headers_b.id%TYPE,
695 p_msg_tab IN okc_aq_pvt.msg_tab_typ,
696 p_init_msg_list IN VARCHAR2 ,
697 x_return_status OUT NOCOPY VARCHAR2,
698 x_msg_count OUT NOCOPY NUMBER,
699 x_msg_data OUT NOCOPY VARCHAR2,
700 x_status OUT NOCOPY VARCHAR2
701 )
702 IS
703 CURSOR cnl_cur IS
704 SELECT cnl.id id,
705 cnl.pdf_id pdf_id,
706 cnl.aae_id aae_id,
707 aae.element_name element_name,
708 decode(aae.data_type,'CHAR','''',
709 'DATE','trunc(to_date('||'''',NULL) prefix_param_start,
710 decode(aae.data_type,'CHAR','''',
711 'DATE',''''||'))',NULL) prefix_param_end,
712 cnl.left_ctr_master_id left_ctr_master_id,
713 cnl.right_ctr_master_id right_ctr_master_id,
714 cnl.left_counter_id left_counter_id,
715 cnl.right_counter_id right_counter_id,
716 cnl.cnl_type cnl_type,
717 cnl.left_parenthesis left_parenthesis,
718 cnl.relational_operator relational_operator,
719 cnl.right_parenthesis right_parenthesis,
720 cnl.logical_operator logical_operator,
721 cnl.right_operand right_operand,
722 cnl.tolerance tolerance,
723 cnl.start_at start_at,
724 cnh.counter_group_id counter_group_id,
725 cnh.acn_id acn_id,
726 cnh.one_time_yn one_time_yn
727 FROM okc_condition_lines_b cnl,
728 okc_action_attributes_b aae,
729 okc_condition_headers_b cnh
730 WHERE cnl.cnh_id = p_cnh_id
731 AND cnl.cnh_id = cnh.id
732 AND cnl.aae_id = aae.id(+)
733 ORDER BY cnl.sortseq;
734 cnl_rec cnl_cur%ROWTYPE;
735
736 l_api_name CONSTANT VARCHAR2(30) := 'EVALUATE_CONDITION_LINES';
737 l_string VARCHAR2(32000);
738 l_count NUMBER := 1;
739 l_return_status VARCHAR2(1);
740 v_result VARCHAR2(10);
741 l_counter_group_log_id NUMBER;
742 l_previous_counter_val NUMBER;
743 v_quote VARCHAR2(6) := '''';
744 left_value VARCHAR2(150);
745 right_value VARCHAR2(150);
746 rel_op VARCHAR2(50);
747 bind_ctr NUMBER := 0;
748 TYPE bind_var_rec IS RECORD (ctr NUMBER,value VARCHAR2(100));
749 TYPE bind_var_table IS TABLE OF bind_var_rec;
750 bind_var_tab bind_var_table := bind_var_table();
751 b integer;
752 i integer;
753 l_processed number;
754
755 --
756 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluate_condition_lines';
757 --
758
759 BEGIN
760
761 IF (l_debug = 'Y') THEN
762 okc_debug.Set_Indentation(l_proc);
763 okc_debug.log('10: Entering ',2);
764 END IF;
765
766 x_return_status := OKC_API.G_RET_STS_SUCCESS;
767 x_status := 'TRUE';
768 IF cnl_cur%ISOPEN THEN
769 CLOSE cnl_cur;
770 END IF;
771 OPEN cnl_cur;
772 LOOP
773 FETCH cnl_cur INTO cnl_rec;
774 IF cnl_cur%NOTFOUND THEN
775 EXIT;
776 ELSE
777 IF cnl_rec.cnl_type = 'GEX' THEN
778 left_value := nvl(get_attribute_value(cnl_rec.element_name,
779 p_msg_tab),'OKCNULL');
780 right_value := NVL(cnl_rec.right_operand,'OKCNULL');
781 IF UPPER(cnl_rec.relational_operator) = 'IS NULL' THEN
782 rel_op := '=';
783 ELSIF UPPER(cnl_rec.relational_operator) = 'IS NOT NULL' THEN
784 rel_op := '<>';
785 ELSE rel_op := cnl_rec.relational_operator;
786 END IF;
787 ---** Bug#2934909 converting execute immediate into dbms_sql to bind input variables
788 -- ** as per new coding standards to avoid data security problems.
789 bind_var_tab.extend;
790 bind_ctr := bind_ctr+1;
791 l_string := l_string || cnl_rec.left_parenthesis;
792 l_string :=l_string||':'||bind_ctr||rel_op;
793 bind_var_tab(bind_ctr).ctr := bind_ctr;
794 bind_var_tab(bind_ctr).value := replace(left_value,'''','''''');
795 bind_var_tab.extend;
796 bind_ctr := bind_ctr+1;
797 l_string:=l_string||':'||bind_ctr||cnl_rec.right_parenthesis||NVL( cnl_rec.logical_operator,' ');
798 bind_var_tab(bind_ctr).ctr := bind_ctr;
799 bind_var_tab(bind_ctr).value := replace(right_value,'''','''''');
800
801 ELSIF cnl_rec.cnl_type = 'CEX' THEN
802 -------------------------------------------------------
803 -- commented out because template counter groups and counters
804 -- are not to be validated for this release
805 -------------------------------------------------------
806 /*IF cnl_rec.left_ctr_master_id IS NOT NULL THEN
807 IF cnl_rec.relational_operator = 'EVERY' THEN
808 l_counter_group_log_id := get_counter_group_log_id(
809 p_cnh_id,
810 cnl_rec.acn_id);
811 l_previous_counter_val := get_previous_counter_val(
812 l_counter_group_log_id,
813 cnl_rec.left_ctr_master_id);
814 l_string :=evaluate_counter_condition(
815 cnl_rec.id ,
816 l_previous_counter_val);
817 ELSE
818 l_string := l_string ||
819 cnl_rec.left_parenthesis ||
820 get_master_counter_value(
821 cnl_rec.left_ctr_master_id) ||
822 cnl_rec.relational_operator ||
823 NVL(cnl_rec.right_operand,
824 get_master_counter_value(
825 cnl_rec.right_ctr_master_id))||
826 cnl_rec.right_parenthesis ||
827 NVL ( cnl_rec.logical_operator,' ');
828 END IF;*/
829 ----------------------------------------------------
830 IF cnl_rec.left_counter_id IS NOT NULL THEN
831 IF cnl_rec.relational_operator = 'EVERY' THEN
832 -- In R12 counter_group_log_id is discontinued added new method call get_last_counter_reading
833 l_previous_counter_val:= get_last_counter_reading(
834 p_cnh_id,
835 cnl_rec.acn_id);
836 ---** Bug#2934909 converting execute immediate into dbms_sql to bind input variables
837 -- ** as per new coding standards to avoid data security problems.
838 l_string :=evaluate_counter_condition(
839 cnl_rec.id ,
840 l_previous_counter_val,
841 get_attribute_value('COUNTER_READING',p_msg_tab));
842 ELSE
843 bind_var_tab.extend;
844 bind_ctr := bind_ctr+1;
845 l_string := l_string || cnl_rec.left_parenthesis;
846 l_string :=l_string||':'||bind_ctr||cnl_rec.relational_operator;
847 bind_var_tab(bind_ctr).ctr := bind_ctr;
848 bind_var_tab(bind_ctr).value := replace(get_attribute_value('COUNTER_READING',p_msg_tab)
849 ,'''','''''');
850 bind_var_tab.extend;
851 bind_ctr := bind_ctr+1;
852 l_string :=l_string||':'||bind_ctr||cnl_rec.right_parenthesis
853 || NVL ( cnl_rec.logical_operator,' ');
854 bind_var_tab(bind_ctr).ctr := bind_ctr;
855 bind_var_tab(bind_ctr).value := replace(NVL(cnl_rec.right_operand, get_counter_value( cnl_rec.right_counter_id)) ,'''','''''');
856
857 END IF;
858 END IF;
859 -------------------------------------------------------------------------------------------
860 ELSIF cnl_rec.cnl_type = 'FEX' THEN
861 l_string := l_string ||
862 cnl_rec.left_parenthesis ||v_quote||
863 get_function_value (cnl_rec.id,
864 cnl_rec.pdf_id,
865 p_msg_tab) ||
866 v_quote||' ='||v_quote||'T'||v_quote||
867 cnl_rec.right_parenthesis ||
868 NVL ( cnl_rec.logical_operator,' ');
869 END IF;
870 END IF;
871 END LOOP;
872 CLOSE cnl_cur;
873 IF l_string is not null THEN
874 l_string := 'BEGIN select ''X'' INTO :v_result from dual where '||l_string||'; END;';
875 ---** Bug#2934909 converting execute immediate into dbms_sql to bind input variables
876 -- ** as per new coding standards to avoid data security problems.
877 BEGIN
878 b := dbms_sql.open_cursor;
879 dbms_sql.parse(b, l_string, dbms_sql.native);
880 dbms_sql.bind_variable(b,':v_result',v_result,20);
881 FOR i IN 1.. bind_var_tab.COUNT LOOP
882 dbms_sql.bind_variable(b,to_char(bind_var_tab(i).ctr),bind_var_tab(i).value);
883 END LOOP;
884 l_processed := dbms_sql.execute(b);
885 dbms_sql.variable_value(b,':v_result',v_result);
886
887 dbms_sql.close_cursor(b);
888 --EXECUTE IMMEDIATE l_string INTO v_result;
889 IF v_result = 'X' THEN
890 x_status := 'TRUE';
891 ELSE x_status := 'FALSE';
892 END IF;
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 -- bug#3192369
896 dbms_sql.close_cursor(b);
897 x_status := 'FALSE';
898 WHEN others THEN
899 -- bug#3188367
900 dbms_sql.close_cursor(b);
901
902 x_status := 'FALSE';
903 END;
904 ---** Bug#2934909 converting execute immediate into dbms_sql ** END ** ----
905 END IF;
906 x_return_status := OKC_API.G_RET_STS_SUCCESS;
907
908 IF (l_debug = 'Y') THEN
909 okc_debug.log('1000: Leaving ',2);
910 okc_debug.Reset_Indentation;
911 END IF;
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
916
917 IF (l_debug = 'Y') THEN
918 okc_debug.log('2000: Leaving ',2);
919 okc_debug.Reset_Indentation;
920 END IF;
921
922 RAISE;
923
924 END evaluate_condition_lines;
925
926
927
928 PROCEDURE build_outcome(
929 p_cnh_tab IN okc_condition_eval_pvt.id_tab_type,
930 p_msg_tab IN okc_aq_pvt.msg_tab_typ,
931 p_init_msg_list IN VARCHAR2 ,
932 x_return_status OUT NOCOPY VARCHAR2,
933 x_msg_count OUT NOCOPY NUMBER,
934 x_msg_data OUT NOCOPY VARCHAR2,
935 x_sync_outcome_tab OUT NOCOPY okc_condition_eval_pvt.outcome_tab_type
936 )
937 IS
938 CURSOR oce_cur (x IN NUMBER) IS
939 SELECT oce.id oce_id,
940 decode(pdf.pdf_type,'ALERT',pdf.message_name,
941 'SCRIPT',pdf.script_name,
942 'PPS',pdf.package_name||'.'||pdf.procedure_name,
943 'WPS',pdf.wf_name||'.'||pdf.wf_process_name,NULL) outcome,
944 pdf.pdf_type pdf_type,
945 pdf.name name,
946 pdp.id pdp_id,
947 pdp.name parameter,
948 decode(pdp.data_type,'CHAR','''',
949 'DATE','''',NULL) prefix_param,
950 nvl(pdp.default_value,
951 decode(pdp.data_type,
952 'CHAR' ,'OKC_API.G_MISS_CHAR',
953 'NUMBER','OKC_API.G_MISS_NUM',
954 'DATE' ,'OKC_API.G_MISS_DATE',
955 'OKC_API.G_MISS_CHAR')) default_value,
956 pdp.data_type datatype,
957 pdp.required_yn required_yn
958 FROM okc_outcomes_b oce,
959 okc_process_defs_v pdf,
960 okc_process_def_parameters_v pdp
961 WHERE oce.cnh_id = p_cnh_tab(x).v_id
962 AND oce.pdf_id = pdf.id
963 AND pdf.id = pdp.pdf_id(+)
964 AND UPPER(pdf.usage) = 'OUTCOME'
965 AND UPPER(oce.enabled_yn) = 'Y'
966 ORDER BY outcome,parameter;
967 oce_rec oce_cur%ROWTYPE;
968
969 l_oce_id okc_outcomes_b.id%TYPE;
970 l_pdp_id okc_process_def_parms_b.id%TYPE;
971 l_value okc_outcome_arguments.value%TYPE;
972 l_element_name okc_action_attributes_b.element_name%TYPE;
973 CURSOR oat_cur(l_oce_id IN NUMBER,
974 l_pdp_id IN NUMBER) IS
975 SELECT oat.value value,aae.element_name element_name
976 FROM okc_outcome_arguments oat,
977 okc_action_attributes_b aae
978 WHERE oat.oce_id = l_oce_id
979 AND oat.pdp_id = l_pdp_id
980 AND oat.aae_id = aae.id(+);
981 oat_rec oat_cur%ROWTYPE;
982
983 sync_index NUMBER :=1;
984 async_index NUMBER :=1;
985 l_msg_tab okc_aq_pvt.msg_tab_typ;
986 l_corrid_rec okc_aq_pvt.corrid_rec_typ;
987 p_cnh_id okc_condition_headers_b.id%TYPE;
988 l_string VARCHAR2(32000);
989 l_attr_value okc_outcome_arguments.value%TYPE; --Bug 3731760
990 v_oce_id NUMBER :=0;
991 sync_pdf_type okc_process_defs_b.pdf_type%TYPE;
992 async_pdf_type okc_process_defs_b.pdf_type%TYPE;
993 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_OUTCOME';
994 l_msg_data varchar2(1000);
995 l_msg_count number;
996 l_return_status varchar2(1);
997 param_exist number;
998 no_param number;
999
1000 --
1001 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'build_outcome';
1002 --
1003
1004 BEGIN
1005
1006 IF (l_debug = 'Y') THEN
1007 okc_debug.Set_Indentation(l_proc);
1008 okc_debug.log('10: Entering build outcome ',2);
1009 END IF;
1010
1011 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1012 x_sync_outcome_tab := okc_condition_eval_pvt.outcome_tab_type();
1013 l_msg_tab := okc_aq_pvt.msg_tab_typ();
1014 l_string := NULL;
1015 FOR i IN 1..p_cnh_tab.COUNT LOOP
1016 IF oce_cur%ISOPEN THEN
1017 CLOSE oce_cur;
1018 END IF;
1019 OPEN oce_cur(i) ;
1020 LOOP
1021 FETCH oce_cur INTO oce_rec;
1022 -- if last record then check if there is
1023 -- outcome built by previous loop
1024 -- add the outcome to sync_outcome_tab
1025
1026 IF oce_cur%NOTFOUND THEN
1027 IF l_string IS NOT NULL THEN
1028 param_exist := instr(l_string,'=>',1,1);
1029 IF param_exist <> 0 THEN
1030 l_string := l_string||');';
1031 END IF;
1032
1033 x_sync_outcome_tab.extend;
1034 x_sync_outcome_tab(sync_index).type := sync_pdf_type;
1035 x_sync_outcome_tab(sync_index).name := l_string;
1036 l_string := NULL;
1037 sync_index := sync_index+1;
1038 ELSIF l_msg_tab.COUNT <> 0 AND
1039 l_corrid_rec.corrid IS NOT NULL THEN
1040 -- put l_msg_tab and corrid_rec in the queue
1041 okc_aq_pvt.send_message (
1042 p_api_version => 1.0
1043 ,x_msg_count => l_msg_count
1044 ,x_msg_data => l_msg_data
1045 ,x_return_status => l_return_status
1046 ,p_corrid_rec => l_corrid_rec
1047 ,p_msg_tab => l_msg_tab
1048 ,p_queue_name => okc_aq_pvt.g_outcome_queue_name);
1049 -- initialize l_msg_tab
1050 l_msg_tab := okc_aq_pvt.msg_tab_typ();
1051 END IF;
1052 EXIT;
1053 ELSE
1054
1055 IF oat_cur%ISOPEN THEN
1056 CLOSE oat_cur;
1057 END IF;
1058 OPEN oat_cur(oce_rec.oce_id,oce_rec.pdp_id) ;
1059 FETCH oat_cur INTO oat_rec;
1060 IF oat_cur%FOUND THEN
1061 l_value := oat_rec.value;
1062 l_element_name := oat_rec.element_name;
1063 ELSE
1064 l_value := null;
1065 l_element_name := null;
1066 END IF;
1067 CLOSE oat_cur;
1068
1069 -- if new outcome then add old outcome to sync_outcome_tab
1070 -- initialize new string and index
1071 IF v_oce_id <> oce_rec.oce_id THEN
1072 IF l_string IS NOT NULL THEN
1073 l_string := l_string||');';
1074 x_sync_outcome_tab.extend;
1075 x_sync_outcome_tab(sync_index).type := sync_pdf_type;
1076 x_sync_outcome_tab(sync_index).name := l_string;
1077 l_string := NULL;
1078 sync_index := sync_index+1;
1079 ELSIF l_msg_tab.COUNT <> 0 AND
1080 l_corrid_rec.corrid IS NOT NULL THEN
1081 -- put l_msg_tab and corrid_rec in the queue
1082 okc_aq_pvt.send_message (
1083 p_api_version => 1.0
1084 , x_msg_count => l_msg_count
1085 , x_msg_data => l_msg_data
1086 , x_return_status => l_return_status
1087 , p_corrid_rec => l_corrid_rec
1088 , p_msg_tab => l_msg_tab
1089 , p_queue_name => okc_aq_pvt.g_outcome_queue_name);
1090 END IF;
1091 -- store new outcome id in a local variable
1092 -- build the new outcome string with first parameter
1093 IF upper(oce_rec.pdf_type) IN ('ALERT','SCRIPT') THEN
1094 IF NVL(oce_rec.parameter,'NO_VAL') = 'NO_VAL' THEN
1095 l_string := oce_rec.outcome||';';
1096 v_oce_id := oce_rec.oce_id;
1097 sync_pdf_type := oce_rec.pdf_type;
1098 x_sync_outcome_tab.extend;
1099 x_sync_outcome_tab(sync_index).type:=sync_pdf_type;
1100 x_sync_outcome_tab(sync_index).name := l_string;
1101 l_string := NULL;
1102 sync_index := sync_index+1;
1103 ELSE
1104 IF (l_debug = 'Y') THEN
1105 okc_debug.log('10: outcome: '||oce_rec.pdf_type,2);
1106 END IF;
1107 sync_pdf_type := oce_rec.pdf_type;
1108 l_string := oce_rec.outcome||
1109 '( '||oce_rec.parameter||
1110 ' => '||
1111 oce_rec.prefix_param||
1112 NVL(l_value,
1113 NVL(get_attribute_value(
1114 l_element_name,
1115 p_msg_tab),
1116 oce_rec.default_value))
1117 ||oce_rec.prefix_param;
1118 v_oce_id := oce_rec.oce_id;
1119 IF (l_debug = 'Y') THEN
1120 okc_debug.log('10: l_string: '||l_string,2);
1121 END IF;
1122 -- if any of the parameter values are missing
1123 -- then raise exception
1124 no_param := instr(l_string,'OKC_API',1,1);
1125 IF no_param <> 0 AND
1126 oce_rec.required_yn = 'Y' THEN
1127 l_string := null;
1128 OKC_API.SET_MESSAGE(
1129 p_app_name => g_app_name
1130 ,p_msg_name => 'OKC_NO_PARAMS'
1131 ,p_token1 => 'PROCESS'
1132 ,p_token1_value => oce_rec.outcome
1133 ,p_token2 => 'PARAM'
1134 ,p_token2_value => oce_rec.parameter
1135 );
1136 -- if parameter has no value then skip that outcome
1137 WHILE v_oce_id = oce_rec.oce_id LOOP
1138 v_oce_id := oce_rec.oce_id;
1139 FETCH oce_cur INTO oce_rec;
1140 IF oce_cur%NOTFOUND THEN
1141 EXIT;
1142 END IF;
1143 END LOOP;
1144 END IF;
1145 sync_pdf_type := oce_rec.pdf_type;
1146 END IF;
1147 ELSE
1148 -- initialize l_msg_tab,async_index and assign new pdf_type to corrid_rec
1149 l_corrid_rec.corrid := oce_rec.pdf_type;
1150 l_msg_tab := okc_aq_pvt.msg_tab_typ();
1151 l_msg_tab.extend;
1152 async_index := 1;
1153 l_msg_tab(async_index).element_name := 'K_ID';
1154 l_msg_tab(async_index).element_value:= get_attribute_value('K_ID',p_msg_tab);
1155 l_msg_tab.extend;
1156 async_index := async_index+1;
1157 l_msg_tab(async_index).element_name := 'OCE_ID';
1158 l_msg_tab(async_index).element_value:= oce_rec.oce_id;
1159 --***************************
1160 l_msg_tab.extend;
1161 async_index := async_index+1;
1162 l_msg_tab(async_index).element_name := 'NAME';
1163 l_msg_tab(async_index).element_value
1164 := UPPER(oce_rec.outcome);
1165 -- if outcome name is missing then raise exception
1166 IF l_msg_tab(async_index).element_value IS NULL THEN
1167 OKC_API.SET_MESSAGE(
1168 p_app_name => g_app_name
1169 ,p_msg_name => 'OKC_INVALID_PROCESS'
1170 );
1171 END IF;
1172 -- append parameters to async_outcome_tab
1173 l_msg_tab.extend;
1174 async_index := async_index+1;
1175 l_msg_tab(async_index).element_name
1176 := 'PARAM_NAME';
1177 l_msg_tab(async_index).element_value
1178 := oce_rec.parameter;
1179 l_msg_tab.extend;
1180 async_index := async_index+1;
1181 l_msg_tab(async_index).element_name
1182 := 'PARAM_DATATYPE';
1183 l_msg_tab(async_index).element_value
1184 := oce_rec.datatype;
1185 l_msg_tab.extend;
1186 async_index := async_index+1;
1187 l_msg_tab(async_index).element_name
1188 := 'PARAM_VALUE';
1189 l_msg_tab(async_index).element_value
1190 := NVL(l_value,
1191 NVL(get_attribute_value(l_element_name,
1192 p_msg_tab),
1193 oce_rec.default_value));
1194 v_oce_id := oce_rec.oce_id;
1195 END IF;
1196 ELSIF
1197 -- if the outcome is same as old outcome then append parameters to string
1198 v_oce_id = oce_rec.oce_id THEN
1199 IF oce_rec.pdf_type IN ('ALERT','SCRIPT') THEN
1200
1201 --Bug 3448425
1202 l_attr_value:=NVL(l_value,NVL(get_attribute_value( l_element_name,p_msg_tab),oce_rec.default_value));
1203 IF (l_debug = 'Y') THEN
1204 okc_debug.log('10: l_attr_value: '||l_attr_value,2);
1205 END IF;
1206 l_string := l_string||
1207 ' , '||
1208 oce_rec.parameter||
1209 ' => '||
1210 oce_rec.prefix_param||
1211 l_attr_value
1212 ||oce_rec.prefix_param;
1213 v_oce_id := oce_rec.oce_id;
1214 -- if any of the parameter values are missing
1215 -- then raise exception
1216 no_param := instr(l_string,'OKC_API',1,1);
1217
1218 --Bug 3448425 IF no_param <> 0 AND
1219 IF (l_debug = 'Y') THEN
1220 okc_debug.log('1000: Build Outcome: '||l_attr_value,2);
1221 END IF;
1222 IF l_attr_value like 'OKC_API.G_MISS%' AND
1223 oce_rec.required_yn = 'Y' THEN
1224 l_string := null;
1225 OKC_API.SET_MESSAGE(
1226 p_app_name => g_app_name
1227 ,p_msg_name => 'OKC_NO_PARAMS'
1228 ,p_token1 => 'PROCESS'
1229 ,p_token1_value => oce_rec.outcome
1230 ,p_token2 => 'PARAM'
1231 ,p_token2_value => oce_rec.parameter
1232 );
1233 -- if parameter has no value then skip that
1234 -- outcome
1235 WHILE v_oce_id = oce_rec.oce_id LOOP
1236 v_oce_id := oce_rec.oce_id;
1237 FETCH oce_cur INTO oce_rec;
1238 IF oce_cur%NOTFOUND THEN
1239 EXIT;
1240 END IF;
1241 END LOOP;
1242 END IF;
1243 sync_pdf_type := oce_rec.pdf_type;
1244 ELSE
1245 -- append parameters to async_outcome_tab
1246 l_msg_tab.extend;
1247 async_index := async_index+1;
1248 l_msg_tab(async_index).element_name
1249 := 'PARAM_NAME';
1250 l_msg_tab(async_index).element_value
1251 := oce_rec.parameter;
1252 l_msg_tab.extend;
1253 async_index := async_index+1;
1254 l_msg_tab(async_index).element_name
1255 := 'PARAM_DATATYPE';
1256 l_msg_tab(async_index).element_value
1257 := oce_rec.datatype;
1258 l_msg_tab.extend;
1259 async_index := async_index+1;
1260 l_msg_tab(async_index).element_name
1261 := 'PARAM_VALUE';
1262 l_msg_tab(async_index).element_value
1263 := NVL(l_value,
1264 NVL(get_attribute_value(l_element_name,
1265 p_msg_tab),
1266 oce_rec.default_value));
1267 -- if any of the parameter values are missing then write message to
1268 -- okc_aqerrors table and stop further processing of that outcome
1269 IF l_msg_tab(async_index).element_value IN
1270 ('OKC_API.G_MISS_CHAR',
1271 'OKC_API.G_MISS_NUM',
1272 'OKC_API.G_MISS_DATE')
1273 AND oce_rec.required_yn = 'Y' THEN
1274 OKC_API.SET_MESSAGE(
1275 p_app_name => g_app_name
1276 ,p_msg_name => 'OKC_NO_PARAMS'
1277 ,p_token1 => 'PROCESS'
1278 ,p_token1_value => oce_rec.outcome
1279 ,p_token2 => 'PARAM'
1280 ,p_token2_value => oce_rec.parameter
1281 );
1282
1283 END IF;
1284 v_oce_id := oce_rec.oce_id;
1285 END IF;
1286 END IF;
1287 END IF;
1288 END LOOP;
1289 CLOSE oce_cur;
1290 END LOOP;
1291 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1292
1293 IF (l_debug = 'Y') THEN
1294 okc_debug.log('1000: Leaving Build outcome ',2);
1295 okc_debug.Reset_Indentation;
1296 END IF;
1297
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1301
1302 IF (l_debug = 'Y') THEN
1303 okc_debug.log('2000: Leaving Build Outcome with error: '||sqlerrm,2);
1304 okc_debug.Reset_Indentation;
1305 END IF;
1306
1307 RAISE;
1308 END build_outcome;
1309
1310
1311 /** Release 12 code start
1312 New API build_outcome added to invoke launch_outcome directly.
1313 This API is used for KEXPIRE event. This will bypass the outcome queue **/
1314
1315
1316 PROCEDURE build_date_outcome(
1317 p_cnh_tab IN okc_condition_eval_pvt.id_tab_type,
1318 p_msg_tab IN okc_aq_pvt.msg_tab_typ,
1319 p_init_msg_list IN VARCHAR2 ,
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER,
1322 x_msg_data OUT NOCOPY VARCHAR2
1323 )
1324 IS
1325 CURSOR oce_cur (x IN NUMBER) IS
1326 SELECT oce.id oce_id,
1327 decode(pdf.pdf_type,'ALERT',pdf.message_name,
1328 'SCRIPT',pdf.script_name,
1329 'PPS',pdf.package_name||'.'||pdf.procedure_name,
1330 'WPS',pdf.wf_name||'.'||pdf.wf_process_name,NULL) outcome,
1331 pdf.pdf_type pdf_type,
1332 pdf.name name,
1333 pdp.id pdp_id,
1334 pdp.name parameter,
1335 decode(pdp.data_type,'CHAR','''',
1336 'DATE','''',NULL) prefix_param,
1337 nvl(pdp.default_value,
1338 decode(pdp.data_type,
1339 'CHAR' ,'OKC_API.G_MISS_CHAR',
1340 'NUMBER','OKC_API.G_MISS_NUM',
1341 'DATE' ,'OKC_API.G_MISS_DATE',
1342 'OKC_API.G_MISS_CHAR')) default_value,
1343 pdp.data_type datatype,
1344 pdp.required_yn required_yn
1345 FROM okc_outcomes_b oce,
1346 okc_process_defs_v pdf,
1347 okc_process_def_parameters_v pdp
1348 WHERE oce.cnh_id = p_cnh_tab(x).v_id
1349 AND oce.pdf_id = pdf.id
1350 AND pdf.id = pdp.pdf_id(+)
1351 AND UPPER(pdf.usage) = 'OUTCOME'
1352 AND UPPER(oce.enabled_yn) = 'Y'
1353 ORDER BY outcome,parameter;
1354 oce_rec oce_cur%ROWTYPE;
1355
1356 l_oce_id okc_outcomes_b.id%TYPE;
1357 l_pdp_id okc_process_def_parms_b.id%TYPE;
1358 l_value okc_outcome_arguments.value%TYPE;
1359 l_element_name okc_action_attributes_b.element_name%TYPE;
1360 CURSOR oat_cur(l_oce_id IN NUMBER,
1361 l_pdp_id IN NUMBER) IS
1362 SELECT oat.value value,aae.element_name element_name
1363 FROM okc_outcome_arguments oat,
1364 okc_action_attributes_b aae
1365 WHERE oat.oce_id = l_oce_id
1366 AND oat.pdp_id = l_pdp_id
1367 AND oat.aae_id = aae.id(+);
1368 oat_rec oat_cur%ROWTYPE;
1369
1370 sync_index NUMBER :=1;
1371 async_index NUMBER :=1;
1372 l_msg_tab okc_aq_pvt.msg_tab_typ;
1373 l_corrid_rec okc_aq_pvt.corrid_rec_typ;
1374 p_cnh_id okc_condition_headers_b.id%TYPE;
1375 l_string VARCHAR2(32000);
1376 l_attr_value okc_outcome_arguments.value%TYPE; --Bug 3731760
1377 v_oce_id NUMBER :=0;
1378 sync_pdf_type okc_process_defs_b.pdf_type%TYPE;
1379 async_pdf_type okc_process_defs_b.pdf_type%TYPE;
1380 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_DATE_OUTCOME';
1381 l_msg_data varchar2(1000);
1382 l_msg_count number;
1383 l_return_status varchar2(1);
1384 param_exist number;
1385 no_param number;
1386
1387 --
1388 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'build_date_outcome';
1389 --
1390
1391 BEGIN
1392
1393 IF (l_debug = 'Y') THEN
1394 okc_debug.Set_Indentation(l_proc);
1395 okc_debug.log('10: Entering build date outcome ',2);
1396 END IF;
1397 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1398 --x_sync_outcome_tab := okc_condition_eval_pvt.outcome_tab_type();
1399 l_msg_tab := okc_aq_pvt.msg_tab_typ();
1400 l_string := NULL;
1401 FOR i IN 1..p_cnh_tab.COUNT LOOP
1402 IF oce_cur%ISOPEN THEN
1403 CLOSE oce_cur;
1404 END IF;
1405 OPEN oce_cur(i) ;
1406 LOOP
1407 FETCH oce_cur INTO oce_rec;
1408 -- if last record then check if there is
1409 -- outcome built by previous loop
1410 -- add the outcome to sync_outcome_tab
1411
1412 IF oce_cur%NOTFOUND THEN
1413 IF l_msg_tab.COUNT <> 0 AND
1414 l_corrid_rec.corrid IS NOT NULL THEN
1415 -- launch outcome
1416 OKC_OUTCOME_INIT_PVT.Launch_outcome(
1417 p_api_version => 1,
1418 p_init_msg_list => OKC_API.G_FALSE,
1419 p_corrid_rec => l_corrid_rec,
1420 p_msg_tab_typ => l_msg_tab,
1421 x_msg_count => l_msg_count,
1422 x_msg_data => l_msg_data,
1423 x_return_status => l_return_status
1424 );
1425
1426 -- initialize l_msg_tab
1427 l_msg_tab := okc_aq_pvt.msg_tab_typ();
1428 END IF;
1429 EXIT;
1430 ELSE
1431
1432 IF oat_cur%ISOPEN THEN
1433 CLOSE oat_cur;
1434 END IF;
1435 OPEN oat_cur(oce_rec.oce_id,oce_rec.pdp_id) ;
1436 FETCH oat_cur INTO oat_rec;
1437 IF oat_cur%FOUND THEN
1438 l_value := oat_rec.value;
1439 l_element_name := oat_rec.element_name;
1440 ELSE
1441 l_value := null;
1442 l_element_name := null;
1443 END IF;
1444 CLOSE oat_cur;
1445
1446 -- if new outcome then launch outcome
1447 IF v_oce_id <> oce_rec.oce_id THEN
1448 IF l_msg_tab.COUNT <> 0 AND
1449 l_corrid_rec.corrid IS NOT NULL THEN
1450 -- launch outcome
1451 OKC_OUTCOME_INIT_PVT.Launch_outcome(
1452 p_api_version => 1,
1453 p_init_msg_list => OKC_API.G_FALSE,
1454 p_corrid_rec => l_corrid_rec,
1455 p_msg_tab_typ => l_msg_tab,
1456 x_msg_count => l_msg_count,
1457 x_msg_data => l_msg_data,
1458 x_return_status => l_return_status
1459 );
1460 END IF; -- l_msg_tab.COUNT <> 0
1461
1462 -- initialize l_msg_tab,async_index and assign new pdf_type to corrid_rec
1463 l_corrid_rec.corrid := oce_rec.pdf_type;
1464 l_msg_tab := okc_aq_pvt.msg_tab_typ();
1465 l_msg_tab.extend;
1466 async_index := 1;
1467 l_msg_tab(async_index).element_name := 'K_ID';
1468 l_msg_tab(async_index).element_value:= get_attribute_value('K_ID',p_msg_tab);
1469 l_msg_tab.extend;
1470 async_index := async_index+1;
1471 l_msg_tab(async_index).element_name := 'OCE_ID';
1472 l_msg_tab(async_index).element_value:= oce_rec.oce_id;
1473 --***************************
1474 l_msg_tab.extend;
1475 async_index := async_index+1;
1476 l_msg_tab(async_index).element_name := 'NAME';
1477 l_msg_tab(async_index).element_value := UPPER(oce_rec.outcome);
1478
1479 -- if outcome name is missing then raise exception
1480 IF l_msg_tab(async_index).element_value IS NULL THEN
1481 OKC_API.SET_MESSAGE(
1482 p_app_name => g_app_name
1483 ,p_msg_name => 'OKC_INVALID_PROCESS'
1484 );
1485 END IF;
1486 -- append parameters to async_outcome_tab
1487 l_msg_tab.extend;
1488 async_index := async_index+1;
1489 l_msg_tab(async_index).element_name
1490 := 'PARAM_NAME';
1491 l_msg_tab(async_index).element_value
1492 := oce_rec.parameter;
1493 l_msg_tab.extend;
1494 async_index := async_index+1;
1495 l_msg_tab(async_index).element_name
1496 := 'PARAM_DATATYPE';
1497 l_msg_tab(async_index).element_value
1498 := oce_rec.datatype;
1499 l_msg_tab.extend;
1500 async_index := async_index+1;
1501 l_msg_tab(async_index).element_name
1502 := 'PARAM_VALUE';
1503 l_msg_tab(async_index).element_value
1504 := NVL(l_value,
1505 NVL(get_attribute_value(l_element_name,
1506 p_msg_tab),
1507 oce_rec.default_value));
1508 v_oce_id := oce_rec.oce_id;
1509 -- if the outcome is same as old outcome then append parameters to string
1510 ELSIF v_oce_id = oce_rec.oce_id THEN
1511 -- append parameters to async_outcome_tab
1512 l_msg_tab.extend;
1513 async_index := async_index+1;
1514 l_msg_tab(async_index).element_name
1515 := 'PARAM_NAME';
1516 l_msg_tab(async_index).element_value
1517 := oce_rec.parameter;
1518 l_msg_tab.extend;
1519 async_index := async_index+1;
1520 l_msg_tab(async_index).element_name
1521 := 'PARAM_DATATYPE';
1522 l_msg_tab(async_index).element_value
1523 := oce_rec.datatype;
1524 l_msg_tab.extend;
1525 async_index := async_index+1;
1526 l_msg_tab(async_index).element_name
1527 := 'PARAM_VALUE';
1528 l_msg_tab(async_index).element_value
1529 := NVL(l_value,
1530 NVL(get_attribute_value(l_element_name,
1531 p_msg_tab),
1532 oce_rec.default_value));
1533 -- if any of the parameter values are missing then write message to
1534 -- okc_aqerrors table and stop further processing of that outcome
1535 IF l_msg_tab(async_index).element_value IN
1536 ('OKC_API.G_MISS_CHAR',
1537 'OKC_API.G_MISS_NUM',
1538 'OKC_API.G_MISS_DATE')
1539 AND oce_rec.required_yn = 'Y' THEN
1540 OKC_API.SET_MESSAGE(
1541 p_app_name => g_app_name
1542 ,p_msg_name => 'OKC_NO_PARAMS'
1543 ,p_token1 => 'PROCESS'
1544 ,p_token1_value => oce_rec.outcome
1545 ,p_token2 => 'PARAM'
1546 ,p_token2_value => oce_rec.parameter
1547 );
1548
1549 END IF;
1550 v_oce_id := oce_rec.oce_id;
1551 END IF; -- v_oce_id = oce_rec.oce_id
1552 END IF;
1553 END LOOP;
1554 CLOSE oce_cur;
1555 END LOOP;
1556 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1557
1558 IF (l_debug = 'Y') THEN
1559 okc_debug.log('1000: Leaving Build date outcome ',2);
1560 okc_debug.Reset_Indentation;
1561 END IF;
1562
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1566
1567 IF (l_debug = 'Y') THEN
1568 okc_debug.log('2000: Leaving Build Date Outcome with error: '||sqlerrm,2);
1569 okc_debug.Reset_Indentation;
1570 END IF;
1571
1572 RAISE;
1573 END build_date_outcome;
1574
1575 /**Release 12 code END **/
1576
1577
1578 PROCEDURE create_condition_occurrence (
1579 p_cnh_tab IN okc_condition_eval_pvt.id_tab_type,
1580 p_datetime IN DATE,
1581 p_init_msg_list IN VARCHAR2 ,
1582 x_return_status OUT NOCOPY VARCHAR2,
1583 x_msg_count OUT NOCOPY NUMBER,
1584 x_msg_data OUT NOCOPY VARCHAR2,
1585 x_coev_tbl OUT NOCOPY okc_coe_pvt.coev_tbl_type
1586 )
1587 IS
1588
1589 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONDITION_OCCURRENCE';
1590 l_api_version NUMBER := 1.0;
1591 l_msg_data varchar2(1000);
1592 l_msg_count number;
1593 l_return_status varchar2(1);
1594 l_coe_tab okc_condition_eval_pvt.id_tab_type;
1595 l_coev_tbl okc_coe_pvt.coev_tbl_type;
1596 v_coev_tbl okc_coe_pvt.coev_tbl_type;
1597 l_count number := 1;
1598 l_task_id jtf_tasks_b.task_id%TYPE;
1599 OKC_PROCESS_FAILED EXCEPTION;
1600
1601 CURSOR cnh_cur(x IN NUMBER) IS
1602 SELECT name,
1603 tracked_yn,
1604 task_owner_id,
1605 dnz_chr_id
1606 FROM OKC_CONDITION_HEADERS_V
1607 WHERE id = x;
1608 cnh_rec cnh_cur%ROWTYPE;
1609
1610 --
1611 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'create_condition_occurrence';
1612 --
1613
1614 BEGIN
1615
1616 IF (l_debug = 'Y') THEN
1617 okc_debug.Set_Indentation(l_proc);
1618 okc_debug.log('10: Entering ',2);
1619 END IF;
1620
1621 FOR i IN 1..p_cnh_tab.COUNT LOOP
1622 l_coev_tbl(l_count).cnh_id := p_cnh_tab(i).v_id;
1623 l_coev_tbl(l_count).datetime := p_datetime;
1624 l_count := l_count + 1;
1625 END LOOP;
1626
1627 OKC_CONDITIONS_PUB.create_cond_occurs(
1628 p_api_version => l_api_version,
1629 p_init_msg_list => OKC_API.G_FALSE,
1630 x_return_status => l_return_status,
1631 x_msg_data => l_msg_data,
1632 x_msg_count => l_msg_count,
1633 p_coev_tbl => l_coev_tbl,
1634 x_coev_tbl => v_coev_tbl
1635 );
1636 IF NVL(l_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1637 OKC_API.SET_MESSAGE(
1638 p_app_name => g_app_name
1639 ,p_msg_name => 'OKC_PROCESS_FAILED'
1640 ,p_token1 => 'SOURCE'
1641 ,p_token1_value => 'Condition Evaluator'
1642 ,p_token2 => 'PROCESS'
1643 ,p_token2_value => 'Create condition occur'
1644 );
1645 RAISE OKC_PROCESS_FAILED;
1646 END IF;
1647 -- call Time Resolver for all condition occurrences
1648 IF NVL(l_return_status,'X') = OKC_API.G_RET_STS_SUCCESS AND
1649 v_coev_tbl.count <> 0 THEN
1650 FOR i IN 1..v_coev_tbl.COUNT LOOP
1651 IF cnh_cur%ISOPEN THEN
1652 CLOSE cnh_cur;
1653 END IF;
1654 OPEN cnh_cur(v_coev_tbl(i).cnh_id);
1655 FETCH cnh_cur INTO cnh_rec;
1656 IF cnh_rec.dnz_chr_id IS NOT NULL THEN
1657 OKC_TIME_RES_PUB.Res_Time_Events(
1658 p_api_version => l_api_version,
1659 p_init_msg_list => OKC_API.G_FALSE,
1660 p_cnh_id => v_coev_tbl(i).cnh_id,
1661 p_coe_id => v_coev_tbl(i).id,
1662 p_date => v_coev_tbl(i).datetime,
1663 x_return_status => l_return_status);
1664 IF NVL(l_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1665 OKC_API.SET_MESSAGE(
1666 p_app_name => g_app_name
1667 ,p_msg_name => 'OKC_PROCESS_FAILED'
1668 ,p_token1 => 'SOURCE'
1669 ,p_token1_value => 'Condition Evaluator'
1670 ,p_token2 => 'PROCESS'
1671 ,p_token2_value => 'Create resolved time values'
1672 );
1673 RAISE OKC_PROCESS_FAILED;
1674 END IF;
1675 END IF;
1676 /**********************************************************************
1677 -- bug 1757364
1678 -- Task created moved to procedure create_action_att_vals in order to
1679 -- retrieve the document source number
1680 -- create tasks for true conditions with tracked_yn = 'Y'
1681 IF UPPER(cnh_rec.tracked_yn) = 'Y' THEN
1682 OKC_TASK_PUB.create_condition_task(
1683 p_api_version => l_api_version,
1684 p_init_msg_list => OKC_API.G_FALSE,
1685 p_cond_occr_id => v_coev_tbl(i).id,
1686 p_condition_name => cnh_rec.name,
1687 p_task_owner_id => cnh_rec.task_owner_id,
1688 p_actual_end_date => v_coev_tbl(i).datetime,
1689 x_return_status => l_return_status,
1690 x_msg_count => l_msg_count,
1691 x_msg_data => l_msg_data,
1692 x_task_id => l_task_id);
1693
1694 IF NVL(l_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1695 OKC_API.SET_MESSAGE(
1696 p_app_name => g_app_name
1697 ,p_msg_name => 'OKC_PROCESS_FAILED'
1698 ,p_token1 => 'SOURCE'
1699 ,p_token1_value => 'Condition Evaluator'
1700 ,p_token2 => 'PROCESS'
1701 ,p_token2_value => 'Create condition task'
1702 );
1703 RAISE OKC_PROCESS_FAILED;
1704 END IF;
1705 END IF;
1706 -- bug 1757364 end!
1707 ***********************************************************************/
1708 CLOSE cnh_cur;
1709 END LOOP;
1710 END IF;
1711 x_coev_tbl := v_coev_tbl;
1712 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1713
1714 IF (l_debug = 'Y') THEN
1715 okc_debug.log('1000: Leaving ',2);
1716 okc_debug.Reset_Indentation;
1717 END IF;
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1722
1723 IF (l_debug = 'Y') THEN
1724 okc_debug.log('2000: Leaving ',2);
1725 okc_debug.Reset_Indentation;
1726 END IF;
1727
1728 RAISE;
1729 END create_condition_occurrence;
1730
1731 -- creates action attribute values for condition occurrence
1732 PROCEDURE create_action_att_values (
1733 p_init_msg_list IN VARCHAR2 ,
1734 x_return_status OUT NOCOPY VARCHAR2,
1735 x_msg_count OUT NOCOPY NUMBER,
1736 x_msg_data OUT NOCOPY VARCHAR2,
1737 p_acn_id IN okc_actions_b.id%TYPE,
1738 p_coev_tab IN okc_coe_pvt.coev_tbl_type,
1739 p_msg_tab IN okc_aq_pvt.msg_tab_typ,
1740 x_aavv_tbl OUT NOCOPY okc_aav_pvt.aavv_tbl_type)
1741
1742 IS
1743 l_aavv_tbl okc_aav_pvt.aavv_tbl_type;
1744 v_aavv_tbl okc_aav_pvt.aavv_tbl_type;
1745 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ACTION_ATT_VALUES';
1746 l_api_version NUMBER := 1.0;
1747 l_msg_data varchar2(1000);
1748 l_msg_count number;
1749 l_return_status varchar2(1);
1750 l_count number :=1;
1751 OKC_PROCESS_FAILED EXCEPTION;
1752
1753 -- bug 1757364
1754 CURSOR cnh_cur(x IN NUMBER) IS
1755 SELECT name,
1756 tracked_yn,
1757 task_owner_id,
1758 dnz_chr_id
1759 FROM OKC_CONDITION_HEADERS_V
1760 WHERE id = x;
1761 --
1762 cnh_rec cnh_cur%ROWTYPE;
1763 l_task_id jtf_tasks_b.task_id%TYPE;
1764 -- bug 1757364 end.
1765
1766 --
1767 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'create_action_att_values';
1768 --
1769
1770 BEGIN
1771
1772 IF (l_debug = 'Y') THEN
1773 okc_debug.Set_Indentation(l_proc);
1774 okc_debug.log('10: Entering ',2);
1775 END IF;
1776
1777
1778 FOR outer_loop IN 1..p_coev_tab.count LOOP
1779
1780 FOR inner_loop IN 1..p_msg_tab.count LOOP
1781 l_aavv_tbl(l_count).coe_id := p_coev_tab(outer_loop).id;
1782 l_aavv_tbl(l_count).aae_id := get_attribute_id(
1783 p_acn_id,
1784 p_msg_tab(inner_loop).element_name);
1785 l_aavv_tbl(l_count).value := p_msg_tab(inner_loop).element_value;
1786 l_count := l_count+1;
1787 END LOOP;
1788
1789 END LOOP;
1790
1791 OKC_CONDITIONS_PUB.create_act_att_vals(
1792 p_api_version => '1',
1793 p_init_msg_list => OKC_API.G_FALSE,
1794 x_return_status => l_return_status,
1795 x_msg_data => l_msg_data,
1796 x_msg_count => l_msg_count,
1797 p_aavv_tbl => l_aavv_tbl,
1798 x_aavv_tbl => v_aavv_tbl
1799 );
1800 IF NVL(l_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1801 OKC_API.SET_MESSAGE(
1802 p_app_name => g_app_name
1803 ,p_msg_name => 'OKC_PROCESS_FAILED'
1804 ,p_token1 => 'SOURCE'
1805 ,p_token1_value => 'Condition Evaluator'
1806 ,p_token2 => 'PROCESS'
1807 ,p_token2_value => 'Create action att values'
1808 );
1809 RAISE OKC_PROCESS_FAILED;
1810 END IF;
1811 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1812
1813 -- bug 1757364
1814 -- addded task creation to this procedure in order to retrieve document
1815 -- source number from the action attribute values.
1816 --
1817 IF NVL(l_return_status,'X') = OKC_API.G_RET_STS_SUCCESS AND p_coev_tab.count <> 0 THEN
1818 FOR i IN 1..p_coev_tab.COUNT LOOP
1819 IF cnh_cur%ISOPEN THEN
1820 CLOSE cnh_cur;
1821 END IF;
1822 OPEN cnh_cur(p_coev_tab(i).cnh_id);
1823 FETCH cnh_cur INTO cnh_rec;
1824
1825 -- create tasks for true conditions with tracked_yn = 'Y'
1826 IF UPPER(cnh_rec.tracked_yn) = 'Y' THEN
1827 OKC_TASK_PUB.create_condition_task(
1828 p_api_version => l_api_version,
1829 p_init_msg_list => OKC_API.G_FALSE,
1830 p_cond_occr_id => p_coev_tab(i).id,
1831 p_condition_name => cnh_rec.name,
1832 p_task_owner_id => cnh_rec.task_owner_id,
1833 p_actual_end_date => p_coev_tab(i).datetime,
1834 x_return_status => l_return_status,
1835 x_msg_count => l_msg_count,
1836 x_msg_data => l_msg_data,
1837 x_task_id => l_task_id);
1838
1839 IF NVL(l_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1840 OKC_API.SET_MESSAGE(
1841 p_app_name => g_app_name
1842 ,p_msg_name => 'OKC_PROCESS_FAILED'
1843 ,p_token1 => 'SOURCE'
1844 ,p_token1_value => 'Condition Evaluator'
1845 ,p_token2 => 'PROCESS'
1846 ,p_token2_value => 'Create condition task'
1847 );
1848 RAISE OKC_PROCESS_FAILED;
1849 END IF;
1850 END IF;
1851 CLOSE cnh_cur;
1852 END LOOP;
1853 END IF;
1854 -- bug 1757364 end.
1855
1856 IF (l_debug = 'Y') THEN
1857 okc_debug.log('1000: Leaving ',2);
1858 okc_debug.Reset_Indentation;
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN OTHERS THEN
1863 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1864
1865 IF (l_debug = 'Y') THEN
1866 okc_debug.log('2000: Leaving ',2);
1867 okc_debug.Reset_Indentation;
1868 END IF;
1869
1870 RAISE;
1871 END create_action_att_values;
1872
1873
1874 -- Evaluate single plan
1875 PROCEDURE evaluate_plan_condition(
1876 p_api_version IN NUMBER,
1877 p_init_msg_list IN VARCHAR2 ,
1878 x_return_status OUT NOCOPY VARCHAR2,
1879 x_msg_count OUT NOCOPY NUMBER,
1880 x_msg_data OUT NOCOPY VARCHAR2,
1881 p_cnh_id IN okc_condition_headers_b.id%TYPE,
1882 p_msg_tab IN okc_aq_pvt.msg_tab_typ,
1883 x_sync_outcome_tab OUT NOCOPY okc_condition_eval_pvt.outcome_tab_type
1884 )
1885 IS
1886 l_status VARCHAR2(10);
1887 l_cnh_tab okc_condition_eval_pvt.id_tab_type;
1888 l_return_status varchar2(1);
1889 l_api_name CONSTANT VARCHAR2(30) := 'EVALUATE_PLAN_CONDITION';
1890 OKC_PROCESS_FAILED EXCEPTION;
1891 --
1892 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluate_plan_condition';
1893 --
1894 BEGIN
1895 l_cnh_tab := okc_condition_eval_pvt.id_tab_type();
1896 x_sync_outcome_tab := okc_condition_eval_pvt.outcome_tab_type();
1897
1898 IF (l_debug = 'Y') THEN
1899 okc_debug.Set_Indentation(l_proc);
1900 okc_debug.log('10: Entering evaluate_plan_condition',2);
1901 END IF;
1902
1903 l_return_status := OKC_API.START_ACTIVITY
1904 (l_api_name
1905 ,p_init_msg_list
1906 ,'_PVT'
1907 ,x_return_status);
1908
1909 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1910 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1911 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1912 RAISE OKC_API.G_EXCEPTION_ERROR;
1913 END IF;
1914
1915 -- evaluate the condition lines for p_cnh_id
1916 -- and return result as TRUE or FALSE
1917 evaluate_condition_lines( p_cnh_id
1918 ,p_msg_tab
1919 ,OKC_API.G_FALSE
1920 ,x_return_status
1921 ,x_msg_count
1922 ,x_msg_data
1923 ,l_status
1924 );
1925 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1926 OKC_API.SET_MESSAGE(
1927 p_app_name => g_app_name
1928 ,p_msg_name => 'OKC_PROCESS_FAILED'
1929 ,p_token1 => 'SOURCE'
1930 ,p_token1_value => 'Condition Evaluator'
1931 ,p_token2 => 'PROCESS'
1932 ,p_token2_value => 'Evaluate Condition Lines'
1933 );
1934 raise OKC_PROCESS_FAILED;
1935 END IF;
1936
1937 IF l_status = 'TRUE' THEN
1938 l_cnh_tab.extend;
1939 l_cnh_tab(1).v_id:=p_cnh_id;
1940
1941 IF (l_debug = 'Y') THEN
1942 okc_debug.log('10: before Build Outcome ',2);
1943 END IF;
1944 -- for the condition get the table of outcomes
1945 build_outcome( l_cnh_tab
1946 ,p_msg_tab
1947 ,OKC_API.G_FALSE
1948 ,x_return_status
1949 ,x_msg_count
1950 ,x_msg_data
1951 ,x_sync_outcome_tab
1952 );
1953 IF (l_debug = 'Y') THEN
1954 okc_debug.log('10: after Build Outcome ',2);
1955 END IF;
1956 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
1957 OKC_API.SET_MESSAGE(
1958 p_app_name => g_app_name
1959 ,p_msg_name => 'OKC_PROCESS_FAILED'
1960 ,p_token1 => 'SOURCE'
1961 ,p_token1_value => 'Condition Evaluator'
1962 ,p_token2 => 'PROCESS'
1963 ,p_token2_value => 'Build Outcome'
1964 );
1965 raise OKC_PROCESS_FAILED;
1966 END IF;
1967 END IF;
1968
1969 OKC_API.END_ACTIVITY( x_msg_count,x_msg_data);
1970
1971 IF (l_debug = 'Y') THEN
1972 okc_debug.log('1000: Leaving evaluate_plan_condition ',2);
1973 okc_debug.Reset_Indentation;
1974 END IF;
1975
1976 EXCEPTION
1977
1978 WHEN OTHERS THEN
1979 IF (l_debug = 'Y') THEN
1980 okc_debug.log('10: error in evaluate_plan_conditionl: '||sqlerrm,2);
1981 END IF;
1982 IF x_sync_outcome_tab.count <> 0 THEN
1983 x_sync_outcome_tab.delete;
1984 END IF;
1985 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1986 ( l_api_name,
1987 G_PKG_NAME,
1988 'OTHERS',
1989 x_msg_count,
1990 x_msg_data,
1991 '_PVT');
1992
1993 IF (l_debug = 'Y') THEN
1994 okc_debug.log('2000: Leaving ',2);
1995 okc_debug.Reset_Indentation;
1996 END IF;
1997
1998
1999 END evaluate_plan_condition;
2000
2001 -- ** SYNCRONOUS Condition Evaluation used by RelationShip Plan Application
2002 -- This procedure acts as a wrapper for other APIs in the package.
2003 -- This procedure first calls evaluate_condition_lines and gets back a table
2004 -- of TRUE condition header ids. This header id table and message table
2005 -- are passed to build_outcome which returns a table of outcomes.
2006 -- This table of outcomes are returned to Relationship Plan API
2007
2008 PROCEDURE evaluate_condition(
2009 p_api_version IN NUMBER,
2010 p_init_msg_list IN VARCHAR2 ,
2011 x_return_status OUT NOCOPY VARCHAR2,
2012 x_msg_count OUT NOCOPY NUMBER,
2013 x_msg_data OUT NOCOPY VARCHAR2,
2014 p_acn_id IN okc_actions_b.id%TYPE,
2015 p_msg_tab IN okc_aq_pvt.msg_tab_typ,
2016 x_sync_outcome_tab OUT NOCOPY okc_condition_eval_pvt.outcome_tab_type
2017 )
2018 IS
2019 CURSOR cnh_cur IS
2020 SELECT id
2021 FROM okc_condition_headers_b
2022 WHERE acn_id = p_acn_id
2023 AND dnz_chr_id is null
2024 AND condition_valid_yn = 'Y'
2025 AND template_yn = 'N'
2026 AND trunc(date_active) <= trunc(SYSDATE)
2027 AND NVL(trunc(date_inactive),trunc(SYSDATE)) >= trunc(SYSDATE);
2028 cnh_rec cnh_cur%ROWTYPE;
2029 l_status VARCHAR2(10);
2030 l_count NUMBER := 1;
2031 l_cnh_tab okc_condition_eval_pvt.id_tab_type;
2032 l_msg_data varchar2(1000);
2033 l_msg_count number;
2034 l_return_status varchar2(1);
2035 x_coev_tbl okc_coe_pvt.coev_tbl_type;
2036 l_coev_tbl okc_coe_pvt.coev_tbl_type;
2037 x_aavv_tbl okc_aav_pvt.aavv_tbl_type;
2038 l_api_name CONSTANT VARCHAR2(30) := 'EVALUATE_CONDITION';
2039 i NUMBER;
2040 OKC_PROCESS_FAILED EXCEPTION;
2041 l_datetime DATE;
2042
2043 --
2044 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluate_condition';
2045 --
2046
2047 BEGIN
2048
2049 IF (l_debug = 'Y') THEN
2050 okc_debug.Set_Indentation(l_proc);
2051 okc_debug.log('10: Entering ',2);
2052 END IF;
2053
2054 l_return_status := OKC_API.START_ACTIVITY
2055 (l_api_name
2056 ,p_init_msg_list
2057 ,'_PVT'
2058 ,x_return_status);
2059
2060 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2061 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2062 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2063 RAISE OKC_API.G_EXCEPTION_ERROR;
2064 END IF;
2065
2066 -- initializing and extending table type variables
2067 l_cnh_tab := okc_condition_eval_pvt.id_tab_type();
2068 x_sync_outcome_tab := okc_condition_eval_pvt.outcome_tab_type();
2069 IF cnh_cur%ISOPEN THEN
2070 CLOSE cnh_cur;
2071 END IF;
2072
2073 /* --Added by suma
2074 --get contract id
2075 -- commented out for now as this is not relevent for Rel Plan actions
2076 -- look at standard evaluator for usage of l_k_id
2077 l_k_id := get_attribute_value('K_ID',p_msg_tab);
2078 */
2079
2080 OPEN cnh_cur;
2081 LOOP
2082 FETCH cnh_cur INTO cnh_rec;
2083 IF cnh_cur%NOTFOUND THEN
2084 EXIT;
2085 ELSE
2086 -- for each condition header check if there are lines, evaluate them
2087 -- and return result as TRUE or FALSE
2088 evaluate_condition_lines( cnh_rec.id
2089 ,p_msg_tab
2090 ,OKC_API.G_FALSE
2091 ,x_return_status
2092 ,x_msg_count
2093 ,x_msg_data
2094 ,l_status
2095 );
2096 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2097 OKC_API.SET_MESSAGE(
2098 p_app_name => g_app_name
2099 ,p_msg_name => 'OKC_PROCESS_FAILED'
2100 ,p_token1 => 'SOURCE'
2101 ,p_token1_value => 'Condition Evaluator'
2102 ,p_token2 => 'PROCESS'
2103 ,p_token2_value => 'Evaluate Condition Lines'
2104 );
2105 raise OKC_PROCESS_FAILED;
2106 END IF;
2107 -- build a table of cnh_ids for all ids that evaluate to true
2108 IF l_status = 'TRUE' THEN
2109 l_cnh_tab.extend;
2110 l_cnh_tab(l_count).v_id := cnh_rec.id;
2111 l_count := l_count+1;
2112 END IF;
2113
2114 END IF;
2115 END LOOP;
2116 CLOSE cnh_cur;
2117
2118 -- If there are conditions that are true then
2119 -- for each condition get the table of outcomes
2120 IF l_cnh_tab.count <> 0 THEN
2121 build_outcome( l_cnh_tab
2122 ,p_msg_tab
2123 ,OKC_API.G_FALSE
2124 ,x_return_status
2125 ,x_msg_count
2126 ,x_msg_data
2127 ,x_sync_outcome_tab
2128 );
2129 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2130 OKC_API.SET_MESSAGE(
2131 p_app_name => g_app_name
2132 ,p_msg_name => 'OKC_PROCESS_FAILED'
2133 ,p_token1 => 'SOURCE'
2134 ,p_token1_value => 'Condition Evaluator'
2135 ,p_token2 => 'PROCESS'
2136 ,p_token2_value => 'Build Outcome'
2137 );
2138 raise OKC_PROCESS_FAILED;
2139 END IF;
2140
2141 -- get the datetime which is date of intrest to create condition occurrence
2142 l_datetime := get_datetime(p_acn_id,
2143 p_msg_tab);
2144 IF l_datetime IS NULL THEN
2145 l_datetime := SYSDATE;
2146 END IF;
2147 -- create condition occurrence for true conditions
2148 create_condition_occurrence ( l_cnh_tab
2149 ,l_datetime
2150 ,OKC_API.G_FALSE
2151 ,x_return_status
2152 ,x_msg_count
2153 ,x_msg_data
2154 , x_coev_tbl
2155 );
2156 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2157 OKC_API.SET_MESSAGE(
2158 p_app_name => g_app_name
2159 ,p_msg_name => 'OKC_PROCESS_FAILED'
2160 ,p_token1 => 'SOURCE'
2161 ,p_token1_value => 'Condition Evaluator'
2162 ,p_token2 => 'PROCESS'
2163 ,p_token2_value => 'Create Condition Occurrence'
2164 );
2165 raise OKC_PROCESS_FAILED;
2166 END IF;
2167 l_coev_tbl := x_coev_tbl;
2168 -- create action attribute values for each condition occurrence
2169 create_action_att_values ( OKC_API.G_FALSE
2170 ,x_return_status
2171 ,x_msg_count
2172 ,x_msg_data
2173 ,p_acn_id
2174 ,l_coev_tbl
2175 ,p_msg_tab
2176 ,x_aavv_tbl
2177 );
2178 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2179 OKC_API.SET_MESSAGE(
2180 p_app_name => g_app_name
2181 ,p_msg_name => 'OKC_PROCESS_FAILED'
2182 ,p_token1 => 'SOURCE'
2183 ,p_token1_value => 'Condition Evaluator'
2184 ,p_token2 => 'PROCESS'
2185 ,p_token2_value => 'Create action att values'
2186 );
2187 raise OKC_PROCESS_FAILED;
2188 END IF;
2189 END IF;
2190
2191 OKC_API.END_ACTIVITY( x_msg_count,
2192 x_msg_data);
2193
2194 IF (l_debug = 'Y') THEN
2195 okc_debug.log('1000: Leaving ',2);
2196 okc_debug.Reset_Indentation;
2197 END IF;
2198
2199 EXCEPTION
2200
2201 WHEN OTHERS THEN
2202 IF x_sync_outcome_tab.count <> 0 THEN
2203 x_sync_outcome_tab.delete;
2204 END IF;
2205 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2206 ( l_api_name,
2207 G_PKG_NAME,
2208 'OTHERS',
2209 x_msg_count,
2210 x_msg_data,
2211 '_PVT');
2212
2213 IF (l_debug = 'Y') THEN
2214 okc_debug.log('2000: Leaving ',2);
2215 okc_debug.Reset_Indentation;
2216 END IF;
2217
2218
2219 END evaluate_condition;
2220
2221
2222 -- standard condition evaluator used for counter update
2223 -- and other asynchronous actions. Invoked from DEQUEUE_EVENT
2224 -- This procedure acts as a wrapper for other APIs in the package.
2225 -- This procedure first calls evaluate_condition_lines and gets back a table
2226 -- of TRUE condition header ids. This header id table and message table
2227 -- are passed to build_outcome.
2228 PROCEDURE evaluate_condition(
2229 p_api_version IN NUMBER,
2230 p_init_msg_list IN VARCHAR2 ,
2231 x_return_status OUT NOCOPY VARCHAR2,
2232 x_msg_count OUT NOCOPY NUMBER,
2233 x_msg_data OUT NOCOPY VARCHAR2,
2234 p_acn_id IN okc_actions_b.id%TYPE,
2235 p_msg_tab IN okc_aq_pvt.msg_tab_typ
2236 )
2237 IS
2238 -- cursor for conditions based on action that occured
2239 CURSOR cnh_cur(p_k_id IN NUMBER) IS
2240 SELECT cnh.id id,
2241 cnh.one_time_yn one_time_yn,
2242 acn.counter_action_yn counter_action_yn,
2243 cnh.jtot_object_code jtot_object_code,
2244 cnh.counter_group_id counter_group_id
2245 FROM okc_condition_headers_b cnh,
2246 okc_actions_b acn
2247 WHERE cnh.acn_id = acn.id
2248 AND (cnh.dnz_chr_id = p_k_id or cnh.dnz_chr_id IS NULL)
2249 AND cnh.condition_valid_yn = 'Y'
2250 AND cnh.template_yn = 'N'
2251 AND trunc(cnh.date_active) <= trunc(SYSDATE)
2252 AND NVL(trunc(cnh.date_inactive),trunc(SYSDATE)) >= trunc(SYSDATE)
2253 AND acn.id = p_acn_id;
2254 cnh_rec cnh_cur%ROWTYPE;
2255 l_element_name okc_action_attributes_b.element_name%TYPE:='CTR_GROUP_ID';
2256 l_status VARCHAR2(10);
2257 l_count NUMBER := 0;
2258 l_cnh_tab okc_condition_eval_pvt.id_tab_type;
2259 l_msg_data varchar2(1000);
2260 l_msg_count number;
2261 l_return_status varchar2(1);
2262 x_coev_tbl okc_coe_pvt.coev_tbl_type;
2263 l_coev_tbl okc_coe_pvt.coev_tbl_type;
2264 x_aavv_tbl okc_aav_pvt.aavv_tbl_type;
2265 l_api_name CONSTANT VARCHAR2(30) := 'EVALUATE_CONDITION';
2266 x_sync_outcome_tab okc_condition_eval_pvt.outcome_tab_type;
2267 OKC_PROCESS_FAILED EXCEPTION;
2268 i NUMBER;
2269 l_k_id NUMBER;
2270 l_datetime DATE;
2271
2272 --
2273 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluate_condition';
2274 --
2275
2276 BEGIN
2277
2278 IF (l_debug = 'Y') THEN
2279 okc_debug.Set_Indentation(l_proc);
2280 okc_debug.log('10: Entering ',2);
2281 END IF;
2282
2283 l_return_status := OKC_API.START_ACTIVITY
2284 (l_api_name
2285 ,p_init_msg_list
2286 ,'_PVT'
2287 ,x_return_status);
2288
2289 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2291 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2292 RAISE OKC_API.G_EXCEPTION_ERROR;
2293 END IF;
2294
2295 --Added by suma
2296 --get contract id
2297 l_k_id := get_attribute_value('K_ID',p_msg_tab);
2298
2299
2300 -- initializing and extending table type variables
2301 l_cnh_tab := okc_condition_eval_pvt.id_tab_type();
2302 -- build the conditions table based on counter_action_yn
2303 IF cnh_cur%ISOPEN THEN
2304 CLOSE cnh_cur;
2305 END IF;
2306 OPEN cnh_cur(l_k_id);
2307 LOOP
2308 FETCH cnh_cur INTO cnh_rec;
2309 IF cnh_cur%NOTFOUND THEN
2310 EXIT;
2311 ELSIF cnh_rec.counter_action_yn = 'Y' THEN
2312 IF cnh_rec.one_time_yn = 'Y' THEN
2313 IF evaluated_once(cnh_rec.id) THEN
2314 l_status := 'FALSE';
2315 ELSE
2316 IF cnh_rec.jtot_object_code IS NOT NULL THEN
2317 evaluate_condition_lines(cnh_rec.id
2318 ,p_msg_tab
2319 ,OKC_API.G_FALSE
2320 ,x_return_status
2321 ,x_msg_count
2322 ,x_msg_data
2323 ,l_status);
2324
2325 IF NVL(x_return_status,'X')<>OKC_API.G_RET_STS_SUCCESS THEN
2326 OKC_API.SET_MESSAGE(
2327 p_app_name => g_app_name
2328 ,p_msg_name => 'OKC_PROCESS_FAILED'
2329 ,p_token1 => 'SOURCE'
2330 ,p_token1_value => 'Condition Evaluator'
2331 ,p_token2 => 'PROCESS'
2332 ,p_token2_value => 'Evaluate condition lines'
2333 );
2334 raise OKC_PROCESS_FAILED;
2335 END IF;
2336 END IF;
2337 END IF;
2338 ELSIF cnh_rec.one_time_yn = 'N' THEN
2339 -- for each condition header check if there are lines, evaluate them
2340 -- and return result as TRUE or FALSE
2341 evaluate_condition_lines(
2342 cnh_rec.id
2343 ,p_msg_tab
2344 ,OKC_API.G_FALSE
2345 ,x_return_status
2346 ,x_msg_count
2347 ,x_msg_data
2348 ,l_status);
2349
2350 IF NVL(x_return_status,'X')<>OKC_API.G_RET_STS_SUCCESS THEN
2351 OKC_API.SET_MESSAGE(
2352 p_app_name => g_app_name
2353 ,p_msg_name => 'OKC_PROCESS_FAILED'
2354 ,p_token1 => 'SOURCE'
2355 ,p_token1_value => 'Condition Evaluator'
2356 ,p_token2 => 'PROCESS'
2357 ,p_token2_value => 'Evaluate condition lines'
2358 );
2359 raise OKC_PROCESS_FAILED;
2360 END IF;
2361 END IF; -- one_time_yn
2362 ELSIF cnh_rec.counter_action_yn = 'N' THEN
2363 evaluate_condition_lines( cnh_rec.id
2364 ,p_msg_tab
2365 ,OKC_API.G_FALSE
2366 ,x_return_status
2367 ,x_msg_count
2368 ,x_msg_data
2369 ,l_status
2370 );
2371 IF NVL(x_return_status,'X')<>OKC_API.G_RET_STS_SUCCESS THEN
2372 OKC_API.SET_MESSAGE(
2373 p_app_name => g_app_name
2374 ,p_msg_name => 'OKC_PROCESS_FAILED'
2375 ,p_token1 => 'SOURCE'
2376 ,p_token1_value => 'Condition Evaluator'
2377 ,p_token2 => 'PROCESS'
2378 ,p_token2_value => 'Evaluate condition lines'
2379 );
2380 raise OKC_PROCESS_FAILED;
2381 END IF;
2382 END IF; -- counter_action_yn
2383
2384 -- build a table of cnh_ids for all ids that evaluate to true
2385 IF l_status = 'TRUE' THEN
2386 l_cnh_tab.extend;
2387 l_count := l_count+1;
2388 l_cnh_tab(l_count).v_id := cnh_rec.id;
2389 END IF;
2390
2391 END LOOP;
2392 CLOSE cnh_cur;
2393
2394 -- If there are conditions that are true then
2395 -- for each condition get the table of outcomes
2396
2397 IF l_cnh_tab.count <> 0 THEN
2398 build_outcome( l_cnh_tab
2399 ,p_msg_tab
2400 ,OKC_API.G_FALSE
2401 ,x_return_status
2402 ,x_msg_count
2403 ,x_msg_data
2404 ,x_sync_outcome_tab
2405 );
2406 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2407 OKC_API.SET_MESSAGE(
2408 p_app_name => g_app_name
2409 ,p_msg_name => 'OKC_PROCESS_FAILED'
2410 ,p_token1 => 'SOURCE'
2411 ,p_token1_value => 'Condition Evaluator'
2412 ,p_token2 => 'PROCESS'
2413 ,p_token2_value => 'Build outcome'
2414 );
2415 raise OKC_PROCESS_FAILED;
2416 END IF;
2417
2418 -- get the datetime which is date of intrest to create condition occurrence
2419 l_datetime := get_datetime(p_acn_id,
2420 p_msg_tab);
2421 IF l_datetime IS NULL THEN
2422 l_datetime := SYSDATE;
2423 END IF;
2424 -- create condition occurrence for true conditions
2425 create_condition_occurrence ( l_cnh_tab
2426 ,l_datetime
2427 ,OKC_API.G_FALSE
2428 ,x_return_status
2429 ,x_msg_count
2430 ,x_msg_data
2431 , x_coev_tbl
2432 );
2433 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2434 OKC_API.SET_MESSAGE(
2435 p_app_name => g_app_name
2436 ,p_msg_name => 'OKC_PROCESS_FAILED'
2437 ,p_token1 => 'SOURCE'
2438 ,p_token1_value => 'Condition Evaluator'
2439 ,p_token2 => 'PROCESS'
2440 ,p_token2_value => 'Create condition occurrence'
2441 );
2442 raise OKC_PROCESS_FAILED;
2443 END IF;
2444 l_coev_tbl := x_coev_tbl;
2445 -- create action attribute values for each condition occurrence
2446 create_action_att_values ( OKC_API.G_FALSE
2447 ,x_return_status
2448 ,x_msg_count
2449 ,x_msg_data
2450 ,p_acn_id
2451 ,l_coev_tbl
2452 ,p_msg_tab
2453 ,x_aavv_tbl
2454 );
2455 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2456 OKC_API.SET_MESSAGE(
2457 p_app_name => g_app_name
2458 ,p_msg_name => 'OKC_PROCESS_FAILED'
2459 ,p_token1 => 'SOURCE'
2460 ,p_token1_value => 'Condition Evaluator'
2461 ,p_token2 => 'PROCESS'
2462 ,p_token2_value => 'Create action att values'
2463 );
2464 raise OKC_PROCESS_FAILED;
2465 END IF;
2466 END IF; -- l_cnh_tab.count
2467 OKC_API.END_ACTIVITY( x_msg_count,
2468 x_msg_data);
2469
2470 IF (l_debug = 'Y') THEN
2471 okc_debug.log('1000: Leaving ',2);
2472 okc_debug.Reset_Indentation;
2473 END IF;
2474
2475 EXCEPTION
2476
2477 WHEN OTHERS THEN
2478 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2479 ( l_api_name,
2480 G_PKG_NAME,
2481 'OTHERS',
2482 x_msg_count,
2483 x_msg_data,
2484 '_PVT');
2485
2486 IF (l_debug = 'Y') THEN
2487 okc_debug.log('2000: Leaving ',2);
2488 okc_debug.Reset_Indentation;
2489 END IF;
2490
2491 END evaluate_condition;
2492
2493 -- ********** condition evaluator for Date based Actions
2494 PROCEDURE evaluate_date_condition(
2495 p_api_version IN NUMBER,
2496 p_init_msg_list IN VARCHAR2 ,
2497 x_return_status OUT NOCOPY VARCHAR2,
2498 x_msg_count OUT NOCOPY NUMBER,
2499 x_msg_data OUT NOCOPY VARCHAR2,
2500 p_cnh_id IN okc_condition_headers_b.id%TYPE,
2501 p_msg_tab IN okc_aq_pvt.msg_tab_typ
2502 )
2503 IS
2504 -- cursor for conditions based on action that occured
2505 CURSOR cnh_cur IS
2506 SELECT cnh.id id,
2507 cnh.acn_id acn_id,
2508 cnh.one_time_yn one_time_yn
2509 FROM okc_condition_headers_b cnh
2510 WHERE cnh.condition_valid_yn = 'Y'
2511 AND cnh.template_yn = 'N'
2512 AND trunc(cnh.date_active) <= trunc(SYSDATE)
2513 AND NVL(trunc(cnh.date_inactive),trunc(SYSDATE)) >= trunc(SYSDATE)
2514 AND cnh.id = p_cnh_id;
2515 cnh_rec cnh_cur%ROWTYPE;
2516 l_element_name okc_action_attributes_b.element_name%TYPE:='CTR_GROUP_ID';
2517 l_status VARCHAR2(10);
2518 l_count NUMBER := 0;
2519 l_cnh_tab okc_condition_eval_pvt.id_tab_type;
2520 l_msg_data varchar2(1000);
2521 l_msg_count number;
2522 l_return_status varchar2(1);
2523 x_coev_tbl okc_coe_pvt.coev_tbl_type;
2524 l_coev_tbl okc_coe_pvt.coev_tbl_type;
2525 x_aavv_tbl okc_aav_pvt.aavv_tbl_type;
2526 l_api_name CONSTANT VARCHAR2(30) := 'EVALUATE_DATE_CONDITION';
2527 x_sync_outcome_tab okc_condition_eval_pvt.outcome_tab_type;
2528 OKC_PROCESS_FAILED EXCEPTION;
2529 l_datetime DATE;
2530
2531 --
2532 l_proc varchar2(72) := ' okc_condition_eval_pvt.'||'evaluate_date_condition';
2533 --
2534
2535 BEGIN
2536
2537 IF (l_debug = 'Y') THEN
2538 okc_debug.Set_Indentation(l_proc);
2539 okc_debug.log('10: Entering ',2);
2540 END IF;
2541
2542 l_return_status := OKC_API.START_ACTIVITY
2543 (l_api_name
2544 ,p_init_msg_list
2545 ,'_PVT'
2546 ,x_return_status);
2547
2548 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2550 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2551 RAISE OKC_API.G_EXCEPTION_ERROR;
2552 END IF;
2553
2554 -- initializing and extending table type variables
2555 l_cnh_tab := okc_condition_eval_pvt.id_tab_type();
2556 -- build the conditions table based on counter_action_yn
2557 IF cnh_cur%ISOPEN THEN
2558 CLOSE cnh_cur;
2559 END IF;
2560 OPEN cnh_cur;
2561 FETCH cnh_cur INTO cnh_rec;
2562 IF cnh_cur%FOUND THEN
2563 evaluate_condition_lines( cnh_rec.id
2564 ,p_msg_tab
2565 ,OKC_API.G_FALSE
2566 ,x_return_status
2567 ,x_msg_count
2568 ,x_msg_data
2569 ,l_status
2570 );
2571 IF NVL(x_return_status,'X')<>OKC_API.G_RET_STS_SUCCESS THEN
2572 OKC_API.SET_MESSAGE(
2573 p_app_name => g_app_name
2574 ,p_msg_name => 'OKC_PROCESS_FAILED'
2575 ,p_token1 => 'SOURCE'
2576 ,p_token1_value => 'Condition Evaluator'
2577 ,p_token2 => 'PROCESS'
2578 ,p_token2_value => 'Evaluate Condition Lines'
2579 );
2580 raise OKC_PROCESS_FAILED;
2581 END IF;
2582 END IF;
2583
2584 -- build a table of cnh_ids for all ids that evaluate to true
2585 IF l_status = 'TRUE' THEN
2586 l_cnh_tab.extend;
2587 l_count := l_count+1;
2588 l_cnh_tab(l_count).v_id := cnh_rec.id;
2589 END IF;
2590
2591
2592 -- If there are conditions that are true then
2593 -- for each condition get the table of outcomes
2594 IF l_cnh_tab.count <> 0 THEN
2595 build_date_outcome( l_cnh_tab
2596 ,p_msg_tab
2597 ,OKC_API.G_FALSE
2598 ,x_return_status
2599 ,x_msg_count
2600 ,x_msg_data
2601 );
2602 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2603 OKC_API.SET_MESSAGE(
2604 p_app_name => g_app_name
2605 ,p_msg_name => 'OKC_PROCESS_FAILED'
2606 ,p_token1 => 'SOURCE'
2607 ,p_token1_value => 'Condition Evaluator'
2608 ,p_token2 => 'PROCESS'
2609 ,p_token2_value => 'Build Outcome'
2610 );
2611 raise OKC_PROCESS_FAILED;
2612 END IF;
2613
2614 -- get the datetime which is date of intrest to create condition occurrence
2615 l_datetime := get_datetime(cnh_rec.acn_id,
2616 p_msg_tab);
2617 IF l_datetime IS NULL THEN
2618 l_datetime := SYSDATE;
2619 END IF;
2620 -- create condition occurrence for true conditions
2621 create_condition_occurrence ( l_cnh_tab
2622 ,l_datetime
2623 ,OKC_API.G_FALSE
2624 ,x_return_status
2625 ,x_msg_count
2626 ,x_msg_data
2627 , x_coev_tbl
2628 );
2629 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2630 OKC_API.SET_MESSAGE(
2631 p_app_name => g_app_name
2632 ,p_msg_name => 'OKC_PROCESS_FAILED'
2633 ,p_token1 => 'SOURCE'
2634 ,p_token1_value => 'Condition Evaluator'
2635 ,p_token2 => 'PROCESS'
2636 ,p_token2_value => 'Create condition occurrence'
2637 );
2638 raise OKC_PROCESS_FAILED;
2639 END IF;
2640 l_coev_tbl := x_coev_tbl;
2641 CLOSE cnh_cur;
2642 -- create action attribute values for each condition occurrence
2643 create_action_att_values ( OKC_API.G_FALSE
2644 ,x_return_status
2645 ,x_msg_count
2646 ,x_msg_data
2647 ,cnh_rec.acn_id
2648 ,l_coev_tbl
2649 ,p_msg_tab
2650 ,x_aavv_tbl
2651 );
2652 IF NVL(x_return_status,'X') <> OKC_API.G_RET_STS_SUCCESS THEN
2653 OKC_API.SET_MESSAGE(
2654 p_app_name => g_app_name
2655 ,p_msg_name => 'OKC_PROCESS_FAILED'
2656 ,p_token1 => 'SOURCE'
2657 ,p_token1_value => 'Condition Evaluator'
2658 ,p_token2 => 'PROCESS'
2659 ,p_token2_value => 'Create action att values'
2660 );
2661 raise OKC_PROCESS_FAILED;
2662 END IF;
2663 END IF;
2664 OKC_API.END_ACTIVITY( x_msg_count,
2665 x_msg_data);
2666
2667 IF (l_debug = 'Y') THEN
2668 okc_debug.log('1000: Leaving ',2);
2669 okc_debug.Reset_Indentation;
2670 END IF;
2671
2672 EXCEPTION
2673
2674 WHEN OTHERS THEN
2675 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2676 ( l_api_name,
2677 G_PKG_NAME,
2678 'OTHERS',
2679 x_msg_count,
2680 x_msg_data,
2681 '_PVT');
2682
2683 IF (l_debug = 'Y') THEN
2684 okc_debug.log('2000: Leaving ',2);
2685 okc_debug.Reset_Indentation;
2686 END IF;
2687
2688 END evaluate_date_condition;
2689
2690
2691
2692 END okc_condition_eval_pvt;