[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_RULE_PVT
Source
1 PACKAGE BODY okc_xprt_rule_pvt
2 /* $Header: OKCVXIRB.pls 120.2 2011/03/10 18:17:28 harchand noship $ */
3 AS
4 g_clause_rule_type VARCHAR2 (30) := 'CLAUSE_SELECTION';
5 g_term_dev_rule_type VARCHAR2 (30) := 'TERM_DEVIATION';
6 g_clause VARCHAR2 (30) := 'CLAUSE';
7 g_question VARCHAR2 (30) := 'QUESTION';
8 g_variable VARCHAR2 (30) := 'VARIABLE';
9 g_rule_id okc_xprt_rule_hdrs_all.rule_id%TYPE;
10 g_rule_intent VARCHAR2 (1);
11 g_rule_type VARCHAR2 (30);
12 g_rule_status_code VARCHAR2 (30);
13 g_org_wide_flag VARCHAR2 (1);
14 g_org_id NUMBER;
15 g_rule_outcome_id okc_xprt_rule_outcomes.rule_outcome_id%TYPE;
16 g_rule_condition_value_id okc_xprt_rule_cond_vals.rule_condition_value_id%TYPE;
17 g_rule_condition_id okc_xprt_rule_conditions.rule_condition_id%TYPE;
18 l_okc_i_not_null VARCHAR2 (30) := 'OKC_I_NOT_NULL';
19 l_okc_i_invalid_value VARCHAR2 (30) := 'OKC_I_INVALID_VALUE';
20 l_field VARCHAR2 (30) := 'FIELD';
21 g_rule_count NUMBER;
22 g_rule_condition_count NUMBER;
23 g_rule_condition_val_count NUMBER;
24 g_rule_outcome_count NUMBER;
25 g_rule_template_rule_count NUMBER;
26
27
28 g_unexpected_error CONSTANT VARCHAR2 (200) := 'OKC_UNEXPECTED_ERROR';
29 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
30 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
31
32 -- Need to check uniquiness of condition
33 PROCEDURE create_rule (p_rule_rec IN OUT NOCOPY rule_rec_type);
34
35 PROCEDURE create_rule_header (
36 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
37 );
38
39 PROCEDURE create_rule_header (
40 p_rule_header_tbl IN OUT NOCOPY rule_header_tbl_type
41 );
42
43 PROCEDURE create_rule_condition (
44 p_rule_condition_rec IN OUT NOCOPY rule_condition_rec_type
45 );
46
47 PROCEDURE create_rule_condition (
48 p_rule_condition_tbl IN OUT NOCOPY rule_condition_tbl_type
49 );
50
51 PROCEDURE create_rule_condn_value (
52 p_rule_condition_id IN NUMBER,
53 p_rule_cond_vals_tbl IN OUT NOCOPY rule_cond_vals_tbl_type
54 );
55
56 PROCEDURE create_rule_outcome (
57 p_rule_outcome_rec IN OUT NOCOPY rule_outcome_rec_type
58 );
59
60 PROCEDURE create_rule_outcome (
61 p_rule_outcome_tbl IN OUT NOCOPY rule_outcome_tbl_type
62 );
63
64 PROCEDURE create_template_rules (
65 p_template_rules_rec IN OUT NOCOPY template_rules_rec_type
66 );
67
68 PROCEDURE create_template_rules (
69 p_template_rules_tbl IN OUT NOCOPY template_rules_tbl_type
70 );
71
72 PROCEDURE update_rule_header (
73 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
74 );
75
76 PROCEDURE update_rule (p_rule_rec IN OUT NOCOPY rule_rec_type);
77
78 PROCEDURE delete_rule_child_entities (
79 p_rule_child_entities_rec IN OUT NOCOPY rule_child_entities_rec_type
80 );
81
82 -------------------------------
83 -- PRIVATE PROCEDURES --
84 -------------------------------
85 FUNCTION isValidLookup(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
86 RETURN VARCHAR2
87 IS
88
89 CURSOR c_validate_lookup
90 IS
91 SELECT 'Y'
92 FROM fnd_lookup_values
93 WHERE lookup_type = p_lookup_type
94 AND lookup_code = p_lookup_code
95 AND LANGUAGE = 'US'
96 AND enabled_flag = 'Y'
97 AND NVL (end_date_active, SYSDATE) >= SYSDATE;
98 l_flag VARCHAR2(1);
99 BEGIN
100 OPEN c_validate_lookup;
101 FETCH c_validate_lookup INTO l_flag;
102 CLOSE c_validate_lookup;
103
104 RETURN Nvl(l_flag,'N');
105
106 EXCEPTION WHEN OTHERS THEN
107 RETURN 'N';
108 END;
109 FUNCTION is_valid_rule (p_rule_id IN NUMBER)
110 RETURN VARCHAR2
111 IS
112 l_flag VARCHAR2 (1);
113 BEGIN
114 SELECT 'Y'
115 INTO l_flag
116 FROM okc_xprt_rule_hdrs_all
117 WHERE rule_id = p_rule_id AND org_id = g_org_id;
118
119 IF NVL (l_flag, 'N') = 'Y'
120 THEN
121 RETURN 'Y';
122 ELSE
123 RETURN 'N';
124 END IF;
125 EXCEPTION
126 WHEN OTHERS
127 THEN
128 RETURN 'N';
129 END is_valid_rule;
130
131 FUNCTION get_rule_type (p_rule_id IN NUMBER)
132 RETURN VARCHAR2
133 IS
134 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
135 BEGIN
136 SELECT rule_type
137 INTO l_rule_type
138 FROM okc_xprt_rule_hdrs_all
139 WHERE rule_id = p_rule_id;
140
141 RETURN l_rule_type;
142 EXCEPTION
143 WHEN OTHERS
144 THEN
145 RETURN NULL;
146 END get_rule_type;
147
148 -- To get LHS side values
149 PROCEDURE get_question_details (
150 p_question_id IN NUMBER,
151 x_question_datatype OUT NOCOPY VARCHAR2,
152 x_value_set_name OUT NOCOPY VARCHAR2
153 )
154 IS
155 BEGIN
156 SELECT question_datatype, value_set_name
157 INTO x_question_datatype, x_value_set_name
158 FROM okc_xprt_questions_b
159 WHERE question_id = p_question_id;
160 EXCEPTION
161 WHEN OTHERS
162 THEN
163 RAISE;
164 END get_question_details;
165
166 -- This function used to test validity of LHS value
167 FUNCTION is_valid_question (p_question_id IN NUMBER, p_intent IN VARCHAR2)
168 RETURN VARCHAR2
169 IS
170 CURSOR cur_validate_question
171 IS
172 SELECT 'Y'
173 FROM okc_xprt_questions_b
174 WHERE question_type = 'Q' -- Question Type must be 'Q'
175 AND NVL (disabled_flag, 'N') = 'N'
176 -- Disabled questions must not be available in the Rule creation/updataion
177 AND question_intent = p_intent
178 AND question_id = p_question_id;
179
180 l_flag VARCHAR2 (1);
181 BEGIN
182 OPEN cur_validate_question;
183
184 FETCH cur_validate_question
185 INTO l_flag;
186
187 CLOSE cur_validate_question;
188
189 IF NVL (l_flag, 'N') = 'Y'
190 THEN
191 RETURN 'Y';
192 ELSE
193 RETURN 'N';
194 END IF;
195 EXCEPTION
196 WHEN OTHERS
197 THEN
198 RETURN 'N';
199 END is_valid_question;
200
201 -- This function used to test validity of LHS value
202 PROCEDURE is_valid_variable (
203 p_variable_code IN VARCHAR2,
204 p_intent IN VARCHAR2,
205 x_valid_variable OUT NOCOPY VARCHAR2,
206 x_vlaue_set_id OUT NOCOPY NUMBER,
207 x_value_set_name OUT NOCOPY VARCHAR2,
208 x_variable_datatype OUT NOCOPY VARCHAR2
209 )
210 IS
211 BEGIN
212 SELECT 'Y', var1.value_set_id, var1.value_set_name,
213 var1.variable_datatype
214 INTO x_valid_variable, x_vlaue_set_id, x_value_set_name,
215 x_variable_datatype
216 FROM (SELECT variable_intent, variable_code, variable_name,
217 a.description, xprt_value_set_name value_set_name,
218 variable_datatype, b.longlist_flag, b.validation_type,
219 b.flex_value_set_id value_set_id, variable_type,
220 vartype.meaning var_type_meaning
221 FROM okc_bus_variables_v a,
222 fnd_flex_value_sets b,
223 fnd_lookups vartype
224 WHERE contract_expert_yn = 'Y'
225 AND variable_datatype <> 'D'
226 AND a.xprt_value_set_name = b.flex_value_set_name(+)
227 AND vartype.lookup_type = 'OKC_ART_VAR_TYPE'
228 AND vartype.lookup_code = variable_type
229 UNION
230 SELECT variable_intent, variable_code, variable_name,
231 a.description, flex_value_set_name value_set_name,
232 variable_datatype, b.longlist_flag, b.validation_type,
233 b.flex_value_set_id value_set_id, variable_type,
234 vartype.meaning var_type_meaning
235 FROM okc_bus_variables_v a,
236 fnd_flex_value_sets b,
237 fnd_lookups vartype
238 WHERE a.variable_type = 'U'
239 AND a.value_set_id = b.flex_value_set_id
240 AND ( ( b.validation_type IN ('I', 'X', 'F')
241 AND b.format_type = 'C'
242 )
243 OR (a.variable_datatype = 'N'
244 AND b.validation_type = 'N'
245 )
246 )
247 AND vartype.lookup_type = 'OKC_ART_VAR_TYPE'
248 AND vartype.lookup_code = variable_type) var1
249 WHERE var1.variable_code = p_variable_code
250 AND var1.variable_intent = p_intent;
251 EXCEPTION
252 WHEN NO_DATA_FOUND
253 THEN
254 x_vlaue_set_id := NULL;
255 x_valid_variable := 'N';
256 x_value_set_name := NULL;
257 x_variable_datatype := NULL;
258 WHEN OTHERS
259 THEN
260 x_vlaue_set_id := NULL;
261 x_valid_variable := 'N';
262 x_value_set_name := NULL;
263 x_variable_datatype := NULL;
264 RAISE;
265 END is_valid_variable;
266
267 -- This function used to test validity of RHS value
268 FUNCTION is_valid_clause (p_article_id IN NUMBER, p_intent IN VARCHAR2)
269 RETURN VARCHAR2
270 IS
271 -- Need to check whether any conditions required for global clauses
272 -- Do not allow future dated clauses
273 -- Allow only standard clauses
274 -- Check Org ID
275 -- Get the validation from ArticlesSearchExpVO.xml
276 CURSOR c_validate_clause
277 IS
278 SELECT 'Y'
279 FROM okc_articles_all
280 WHERE org_id = g_org_id
281 AND standard_yn = 'Y'
282 AND article_id = p_article_id
283 AND article_intent = p_intent;
284
285 CURSOR cur_val_adopted_article
286 IS
287 SELECT 'Y'
288 FROM okc_articles_all art,
289 okc_article_versions ver,
290 okc_article_adoptions adp
291 WHERE art.article_id = ver.article_id
292 AND art.standard_yn = 'Y'
293 AND ver.global_yn = 'Y'
294 AND ver.article_status = 'APPROVED'
295 AND adp.global_article_version_id = ver.article_version_id
296 AND adp.adoption_type = 'ADOPTED'
297 AND adp.adoption_status = 'APPROVED'
298 AND art.article_id = p_article_id
299 AND art.article_intent = p_intent
300 AND adp.local_org_id = g_org_id;
301
302 l_flag VARCHAR2 (1);
303 l_adopt_art_flag VARCHAR2 (1);
304 BEGIN
305 OPEN c_validate_clause;
306
307 FETCH c_validate_clause
308 INTO l_flag;
309
310 CLOSE c_validate_clause;
311
312 IF NVL (l_flag, 'N') = 'Y'
313 THEN
314 RETURN 'Y';
315 END IF;
316
317 OPEN cur_val_adopted_article;
318
319 FETCH cur_val_adopted_article
320 INTO l_adopt_art_flag;
321
322 CLOSE cur_val_adopted_article;
323
324 RETURN NVL (l_adopt_art_flag, 'N');
325 EXCEPTION
326 WHEN OTHERS
327 THEN
328 RETURN 'N';
329 END is_valid_clause;
330
331 -- This function used to test validity of RHS value
332 FUNCTION is_valid_question (
333 p_question_id IN NUMBER,
334 p_intent IN VARCHAR2,
335 p_question_datatype IN VARCHAR2
336 )
337 RETURN VARCHAR2
338 IS
339 CURSOR cur_validate_question
340 IS
341 SELECT 'Y'
342 FROM okc_xprt_questions_b
343 WHERE question_type = 'Q' -- Question Type must be 'Q'
344 AND NVL (disabled_flag, 'N') = 'N'
345 -- Disabled questions must not be available in the Rule creation/updataion
346 AND question_intent = p_intent
347 AND question_id = p_question_id
348 AND question_datatype = 'N';
349
350 l_flag VARCHAR2 (1);
351 BEGIN
352 OPEN cur_validate_question;
353
354 FETCH cur_validate_question
355 INTO l_flag;
356
357 CLOSE cur_validate_question;
358
359 IF NVL (l_flag, 'N') = 'Y'
360 THEN
361 RETURN 'Y';
362 ELSE
363 RETURN 'N';
364 END IF;
365 EXCEPTION
366 WHEN OTHERS
367 THEN
368 RETURN 'N';
369 END is_valid_question;
370
371 -- This function used to test validity of RHS value
372 FUNCTION is_valid_constant (p_constant_id IN NUMBER, p_intent IN VARCHAR2)
373 RETURN VARCHAR2
374 IS
375 CURSOR cur_validate_constant
376 IS
377 SELECT 'Y'
378 FROM okc_xprt_questions_b
379 WHERE question_type = 'C' -- Constant Type must be 'Q'
380 -- Disabled questions must not be available in the Rule creation/updataion
381 AND question_intent = p_intent
382 AND question_id = p_constant_id;
383
384 l_flag VARCHAR2 (1);
385 BEGIN
386 OPEN cur_validate_constant;
387
388 FETCH cur_validate_constant
389 INTO l_flag;
390
391 CLOSE cur_validate_constant;
392
393 IF NVL (l_flag, 'N') = 'Y'
394 THEN
395 RETURN 'Y';
396 ELSE
397 RETURN 'N';
398 END IF;
399 EXCEPTION
400 WHEN OTHERS
401 THEN
402 RETURN 'N';
403 END is_valid_constant;
404
405 -- This function used to test validity of RHS value
406 FUNCTION is_valid_variable (
407 p_variable_code IN VARCHAR2,
408 p_intent IN VARCHAR2
409 )
410 RETURN VARCHAR2
411 IS
412 CURSOR cur_validate_variable
413 IS
414 SELECT 'Y'
415 FROM okc_bus_variables_v
416 WHERE contract_expert_yn = 'Y'
417 AND variable_datatype = 'N'
418 AND variable_intent = p_intent
419 AND variable_code = p_variable_code;
420
421 l_flag VARCHAR2 (1);
422 BEGIN
423 OPEN cur_validate_variable;
424
425 FETCH cur_validate_variable
426 INTO l_flag;
427
428 CLOSE cur_validate_variable;
429
430 IF NVL (l_flag, 'N') = 'Y'
431 THEN
432 RETURN 'Y';
433 ELSE
434 RETURN 'N';
435 END IF;
436 EXCEPTION
437 WHEN OTHERS
438 THEN
439 RETURN 'N';
440 END is_valid_variable;
441
442 PROCEDURE set_proc_error_message (p_proc IN VARCHAR2)
443 IS
444 BEGIN
445 okc_api.set_message (p_app_name => g_app_name,
446 p_msg_name => 'OKC_I_ERROR_PROCEDURE',
447 p_token1 => 'PROCEDURE',
448 p_token1_value => p_proc
449 );
450 END set_proc_error_message;
451
452 PROCEDURE set_rec_num_message (p_rec_num IN NUMBER)
453 IS
454 BEGIN
455 okc_api.set_message (p_app_name => g_app_name,
456 p_msg_name => 'OKC_I_RECORD_NUM',
457 p_token1 => 'RECORD_NUM',
458 p_token1_value => p_rec_num
459 );
460 END set_rec_num_message;
461
462 -----------------------------------------------
463 FUNCTION is_duplicate_outcome (
464 p_rule_id IN NUMBER,
465 p_object_type IN VARCHAR2,
466 p_object_value_id IN NUMBER
467 )
468 RETURN VARCHAR2
469 IS
470 CURSOR cur_outcome_exists
471 IS
472 SELECT 'Y'
473 FROM okc_xprt_rule_outcomes
474 WHERE rule_id = p_rule_id
475 AND object_type = p_object_type
476 AND object_value_id = p_object_value_id;
477
478 l_dup_flag VARCHAR2 (1);
479 BEGIN
480 OPEN cur_outcome_exists;
481
482 FETCH cur_outcome_exists
483 INTO l_dup_flag;
484
485 CLOSE cur_outcome_exists;
486
487 RETURN NVL (l_dup_flag, 'N');
488 EXCEPTION
489 WHEN OTHERS
490 THEN
491 RETURN 'N';
492 END is_duplicate_outcome;
493
494 -- This function is used to test the Outcome
495 FUNCTION is_valid_outcome (
496 p_rule_id IN NUMBER,
497 p_object_type IN VARCHAR2,
498 p_object_value_id IN NUMBER
499 )
500 RETURN VARCHAR2
501 /*
502 Need to check ON-HOLD and Expiry clauses in Outcome
503 But as of now excluding the valdiation. Need to do this in future.
504 Source: RulesAMImpl.java Method: checkClauseOutcomes
505 */
506 IS
507 l_intent VARCHAR2 (30);
508 BEGIN
509 SELECT intent
510 INTO l_intent
511 FROM okc_xprt_rule_hdrs_all
512 WHERE rule_id = p_rule_id;
513
514 IF p_object_type = g_clause
515 THEN
516 RETURN is_valid_clause (p_article_id => p_object_value_id,
517 p_intent => l_intent
518 );
519 ELSIF p_object_type = g_question
520 THEN
521 RETURN is_valid_question (p_question_id => p_object_value_id,
522 p_intent => l_intent
523 );
524 END IF;
525
526 RETURN 'N';
527 EXCEPTION
528 WHEN OTHERS
529 THEN
530 RETURN 'N';
531 END is_valid_outcome;
532
533 ---------------------------------
534 PROCEDURE validate_rule_condn_val_tbl (
535 p_object_value_type IN VARCHAR2,
536 p_rule_intent IN VARCHAR2,
537 p_value_set_name IN VARCHAR2 DEFAULT NULL,
538 p_rule_cond_vals_tbl IN rule_cond_vals_tbl_type
539 )
540 IS
541 -- Question
542 -- CHECK question_type='Q'
543 -- Check whether it is enabled
544 l_validate_flag VARCHAR2 (1);
545 l_value_set_id NUMBER;
546 l_validation_type VARCHAR2 (10);
547 l_value VARCHAR2 (1000);
548 l_proc VARCHAR2 (120) := 'VALIDATE_RULE_CONDN_VAL_TBL';
549 l_failed_rec_num NUMBER := 0;
550 BEGIN
551 IF p_object_value_type = 'CLAUSE'
552 THEN
553 FOR i IN p_rule_cond_vals_tbl.FIRST .. p_rule_cond_vals_tbl.LAST
554 LOOP
555 l_failed_rec_num := i;
556
557 IF is_valid_clause
558 (p_article_id => TO_NUMBER
559 (p_rule_cond_vals_tbl (i).object_value_code
560 ),
561 p_intent => p_rule_intent
562 ) <> 'Y'
563 THEN
564 okc_api.set_message (p_app_name => g_app_name,
565 p_msg_name => 'OKC_I_INVALID_COND_VALUE',
566 p_token1 => 'ENTITY',
567 p_token1_value => p_object_value_type
568 );
569 RAISE fnd_api.g_exc_error;
570 END IF;
571 END LOOP;
572 ELSIF p_object_value_type = 'QUESTION'
573 THEN
574 FOR i IN p_rule_cond_vals_tbl.FIRST .. p_rule_cond_vals_tbl.LAST
575 LOOP
576 l_failed_rec_num := i;
577
578 IF is_valid_question
579 (p_question_id => TO_NUMBER
580 (p_rule_cond_vals_tbl (i).object_value_code
581 ),
582 p_intent => p_rule_intent,
583 p_question_datatype => 'N'
584 ) <> 'Y'
585 THEN
586 okc_api.set_message (p_app_name => g_app_name,
587 p_msg_name => 'OKC_I_INVALID_COND_VALUE',
588 p_token1 => 'ENTITY',
589 p_token1_value => p_object_value_type
590 );
591 RAISE fnd_api.g_exc_error;
592 END IF;
593 END LOOP;
594 ELSIF p_object_value_type = 'CONSTANT'
595 THEN
596 FOR i IN p_rule_cond_vals_tbl.FIRST .. p_rule_cond_vals_tbl.LAST
597 LOOP
598 l_failed_rec_num := i;
599
600 IF is_valid_constant
601 (p_constant_id => TO_NUMBER
602 (p_rule_cond_vals_tbl (i).object_value_code
603 ),
604 p_intent => p_rule_intent
605 ) <> 'Y'
606 THEN
607 okc_api.set_message (p_app_name => g_app_name,
608 p_msg_name => 'OKC_I_INVALID_COND_VALUE',
609 p_token1 => 'ENTITY',
610 p_token1_value => p_object_value_type
611 );
612 RAISE fnd_api.g_exc_error;
613 END IF;
614 END LOOP;
615 ELSIF p_object_value_type = 'VARIABLE'
616 THEN
617 FOR i IN p_rule_cond_vals_tbl.FIRST .. p_rule_cond_vals_tbl.LAST
618 LOOP
619 l_failed_rec_num := i;
620
621 IF is_valid_variable
622 (p_variable_code => p_rule_cond_vals_tbl (i).object_value_code,
623 p_intent => p_rule_intent
624 ) <> 'Y'
625 THEN
626 okc_api.set_message (p_app_name => g_app_name,
627 p_msg_name => 'OKC_I_INVALID_COND_VALUE',
628 p_token1 => 'ENTITY',
629 p_token1_value => p_object_value_type
630 );
631 RAISE fnd_api.g_exc_error;
632 END IF;
633 END LOOP;
634 ELSIF p_object_value_type = 'VALUE'
635 THEN
636 SELECT flex_value_set_id, validation_type
637 INTO l_value_set_id, l_validation_type
638 FROM fnd_flex_value_sets
639 WHERE flex_value_set_name = p_value_set_name;
640
641 FOR i IN p_rule_cond_vals_tbl.FIRST .. p_rule_cond_vals_tbl.LAST
642 LOOP
643 l_failed_rec_num := i;
644 l_value := NULL;
645 l_value :=
646 okc_xprt_util_pvt.get_valueset_value
647 (p_object_value_set_id => l_value_set_id,
648 p_object_value_code => p_rule_cond_vals_tbl (i).object_value_code,
649 p_validation_type => l_validation_type
650 );
651
652 IF l_value IS NULL
653 THEN
654 okc_api.set_message (p_app_name => g_app_name,
655 p_msg_name => 'OKC_I_INVALID_COND_VALUE',
656 p_token1 => 'ENTITY',
657 p_token1_value => p_object_value_type
658 );
659 RAISE fnd_api.g_exc_error;
660 END IF;
661 END LOOP;
662 END IF;
663 EXCEPTION
664 WHEN fnd_api.g_exc_error
665 THEN
666 set_proc_error_message (p_proc => l_proc);
667 set_rec_num_message (p_rec_num => l_failed_rec_num);
668 RAISE;
669 WHEN OTHERS
670 THEN
671 set_proc_error_message (p_proc => l_proc);
672 set_rec_num_message (p_rec_num => l_failed_rec_num);
673 RAISE;
674 END validate_rule_condn_val_tbl;
675
676 ---------------------------------
677
678 /** Method checks if no clause/question exists in both condition and outcome */
679 FUNCTION validaterulecondition (p_rule_id IN NUMBER)
680 RETURN VARCHAR2
681 IS
682 CURSOR cur_conditions
683 IS
684 SELECT object_type, object_code, rule_condition_id
685 FROM okc_xprt_rule_conditions
686 WHERE rule_id = p_rule_id;
687
688 CURSOR cur_condn_values (p_rule_condition_id NUMBER)
689 IS
690 SELECT TO_NUMBER (object_value_code) clause_id
691 FROM okc_xprt_rule_cond_vals
692 WHERE rule_condition_id = p_rule_condition_id;
693
694 l_proc VARCHAR2 (60) := 'validateRuleCondition';
695 l_failed_rec_num NUMBER := 0;
696 BEGIN
697 FOR l_rec IN cur_conditions
698 LOOP
699 IF l_rec.object_type = g_question
700 THEN
701 -- A outcome exists with this question.
702 IF is_duplicate_outcome (p_rule_id,
703 l_rec.object_type,
704 TO_NUMBER (l_rec.object_code)
705 ) = 'Y'
706 THEN
707 okc_api.set_message (p_app_name => g_app_name,
708 p_msg_name => 'OKC_XPRT_COND_OUTCOME_ERR'
709 );
710 set_proc_error_message (p_proc => l_proc);
711 RETURN 'N';
712 END IF;
713 END IF;
714
715 IF l_rec.object_type = g_clause
716 THEN
717 FOR clause_rec IN cur_condn_values (l_rec.rule_condition_id)
718 LOOP
719 -- A outcome exists with this clause.
720 IF is_duplicate_outcome (p_rule_id,
721 g_clause,
722 clause_rec.clause_id
723 ) = 'Y'
724 THEN
725 okc_api.set_message
726 (p_app_name => g_app_name,
727 p_msg_name => 'OKC_XPRT_COND_OUTCOME_ERR'
728 );
729 set_proc_error_message (p_proc => l_proc);
730 RETURN 'N';
731 END IF;
732 END LOOP;
733 END IF;
734 END LOOP;
735
736 RETURN 'Y';
737 EXCEPTION
738 WHEN OTHERS
739 THEN
740 RAISE;
741 END validaterulecondition;
742
743 PROCEDURE read_message (x_message IN OUT NOCOPY VARCHAR2)
744 IS
745 l_message VARCHAR2 (2000);
746 BEGIN
747 FOR i IN 1 .. fnd_msg_pub.count_msg
748 LOOP
749 l_message := fnd_msg_pub.get (i, p_encoded => fnd_api.g_false);
750
751 IF (LENGTH (l_message) + LENGTH (Nvl(x_message,' '))) <= 2500
752 THEN
753 x_message := x_message || l_message;
754 ELSE
755 EXIT;
756 END IF;
757 END LOOP;
758 EXCEPTION
759 WHEN OTHERS
760 THEN
761 NULL;
762 END read_message;
763
764 PROCEDURE create_rule_header (
765 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
766 )
767 IS
768 l_operating_unit NUMBER;
769 l_validate_flag VARCHAR2 (1);
770 l_proc VARCHAR2 (120) := 'CREATE_RULE_HEADER';
771
772 CURSOR cur_val_ou (p_org_id NUMBER)
773 IS
774 SELECT 'X'
775 FROM hr_operating_units ou, hr_organization_information oi
776 WHERE mo_global.check_access (ou.organization_id) = 'Y'
777 AND oi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
778 AND oi.organization_id = ou.organization_id
779 AND NVL (date_to, SYSDATE) >= SYSDATE
780 AND ou.organization_id = p_org_id;
781
782 PROCEDURE default_rule_header (
783 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
784 )
785 IS
786 l_proc VARCHAR2 (60) := 'DEFAULT_RULE_HEADER';
787 BEGIN
788 p_rule_header_rec.status_code := 'DRAFT';
789 p_rule_header_rec.published_flag := 'N';
790 p_rule_header_rec.object_version_number := 1;
791
792 IF p_rule_header_rec.org_id = okc_api.g_miss_num
793 THEN
794 -- Derive the operating info
795 p_rule_header_rec.org_id := mo_utils.get_default_org_id;
796 END IF;
797
798 -- IF p_rule_header_rec.status_code = OKC_API.G_MISS_CHAR THEN
799 -- END IF;
800 IF p_rule_header_rec.org_wide_flag = okc_api.g_miss_char
801 THEN
802 p_rule_header_rec.org_wide_flag := 'N';
803 END IF;
804
805 --IF p_rule_header_rec.PUBLISHED_FLAG=OKC_API.G_MISS_CHAR THEN
806 --END IF;
807 IF p_rule_header_rec.line_level_flag = okc_api.g_miss_char
808 THEN
809 p_rule_header_rec.line_level_flag := NULL;
810 END IF;
811
812 -- Start Defaulting 'WHO' columns
813 IF p_rule_header_rec.created_by = okc_api.g_miss_num
814 THEN
815 p_rule_header_rec.created_by := fnd_global.user_id;
816 END IF;
817
818 IF p_rule_header_rec.creation_date = okc_api.g_miss_date
819 THEN
820 p_rule_header_rec.creation_date := SYSDATE;
821 END IF;
822
823 IF p_rule_header_rec.last_updated_by = okc_api.g_miss_num
824 THEN
825 p_rule_header_rec.last_updated_by := fnd_global.user_id;
826 END IF;
827
828 IF p_rule_header_rec.last_update_date = okc_api.g_miss_date
829 THEN
830 p_rule_header_rec.last_update_date := SYSDATE;
831 END IF;
832
833 IF p_rule_header_rec.last_update_login = okc_api.g_miss_num
834 THEN
835 p_rule_header_rec.last_update_login := fnd_global.login_id;
836 END IF;
837
838 IF fnd_global.conc_request_id = -1
839 THEN
840 p_rule_header_rec.request_id := NULL;
841 ELSE
842 p_rule_header_rec.request_id := fnd_global.conc_request_id;
843 END IF;
844
845 IF fnd_global.conc_program_id = -1
846 THEN
847 p_rule_header_rec.program_id := NULL;
848 p_rule_header_rec.program_update_date := NULL;
849 ELSE
850 p_rule_header_rec.program_id := fnd_global.conc_program_id;
851 -- Directly initializing it to the sysdate
852 p_rule_header_rec.program_update_date := SYSDATE;
853 END IF;
854
855 IF fnd_global.prog_appl_id = -1
856 THEN
857 p_rule_header_rec.program_application_id := NULL;
858 ELSE
859 p_rule_header_rec.program_application_id :=
860 fnd_global.prog_appl_id;
861 END IF;
862 END default_rule_header;
863
864 PROCEDURE validate_header (p_rule_header_rec IN rule_header_rec_type)
865 IS
866 l_proc VARCHAR2 (120) := 'VALIDATE_HEADER';
867
868 CURSOR cur_rule_name_exists (p_rule_name VARCHAR2, p_org_id NUMBER)
869 IS
870 SELECT 'Y'
871 FROM okc_xprt_rule_hdrs_all
872 WHERE rule_name = p_rule_name AND org_id = p_org_id;
873
874 l_validate_flag VARCHAR2 (1);
875 BEGIN
876 IF p_rule_header_rec.org_id IS NULL
877 THEN
878 okc_api.set_message (p_app_name => g_app_name,
879 p_msg_name => l_okc_i_not_null,
880 p_token1 => l_field,
881 p_token1_value => 'ORG_ID'
882 );
883 RAISE fnd_api.g_exc_error;
884 END IF;
885
886 IF p_rule_header_rec.intent IS NULL
887 THEN
888 okc_api.set_message (p_app_name => g_app_name,
889 p_msg_name => l_okc_i_not_null,
890 p_token1 => l_field,
891 p_token1_value => 'INTENT'
892 );
893 RAISE fnd_api.g_exc_error;
894 ELSE
895 IF p_rule_header_rec.intent NOT IN ('B', 'S')
896 THEN
897 okc_api.set_message (p_app_name => g_app_name,
898 p_msg_name => l_okc_i_invalid_value,
899 p_token1 => l_field,
900 p_token1_value => 'INTENT'
901 );
902 RAISE fnd_api.g_exc_error;
903 END IF;
904 END IF;
905
906 IF p_rule_header_rec.rule_name IS NULL
907 THEN
908 okc_api.set_message (p_app_name => g_app_name,
909 p_msg_name => l_okc_i_not_null,
910 p_token1 => l_field,
911 p_token1_value => 'RULE_NAME'
912 );
913 RAISE fnd_api.g_exc_error;
914 ELSE
915 OPEN cur_rule_name_exists (p_rule_header_rec.rule_name,
916 p_rule_header_rec.org_id
917 );
918
919 FETCH cur_rule_name_exists
920 INTO l_validate_flag;
921
922 CLOSE cur_rule_name_exists;
923
924 IF NVL (l_validate_flag, 'X') = 'Y'
925 THEN
926 okc_api.set_message (p_app_name => g_app_name,
927 p_msg_name => 'OKC_XPRT_RULE_NAME_EXISTS'
928 );
929 RAISE fnd_api.g_exc_error;
930 END IF;
931
932 l_validate_flag := NULL;
933 END IF;
934
935 IF p_rule_header_rec.rule_type IS NULL
936 THEN
937 okc_api.set_message (p_app_name => g_app_name,
938 p_msg_name => l_okc_i_not_null,
939 p_token1 => l_field,
940 p_token1_value => 'RULE_TYPE'
941 );
942 RAISE fnd_api.g_exc_error;
943 ELSE
944 -- verify the lookup from OKC_XPRT_RULE_TYPE and execlude the 'All' Type
945 IF p_rule_header_rec.rule_type NOT IN
946 (g_clause_rule_type, g_term_dev_rule_type)
947 THEN
948 okc_api.set_message (p_app_name => g_app_name,
949 p_msg_name => l_okc_i_invalid_value,
950 p_token1 => l_field,
951 p_token1_value => 'RULE_TYPE'
952 );
953 RAISE fnd_api.g_exc_error;
954 END IF;
955 END IF;
956
957 IF p_rule_header_rec.condition_expr_code IS NULL
958 THEN
959 okc_api.set_message (p_app_name => g_app_name,
960 p_msg_name => l_okc_i_not_null,
961 p_token1 => l_field,
962 p_token1_value => 'CONDITION_EXPR_CODE'
963 );
964 RAISE fnd_api.g_exc_error;
965
966 ELSE
967 -- OKC_XPRT_CONDITION_CRITERIA
968 -- ALL -> Match all Conditions => All Conditions must be true
969 -- ANY -> Match Any Condition
970 IF p_rule_header_rec.condition_expr_code NOT IN ('ALL', 'ANY')
971 THEN
972 okc_api.set_message (p_app_name => g_app_name,
973 p_msg_name => l_okc_i_invalid_value,
974 p_token1 => l_field,
975 p_token1_value => 'CONDITION_EXPR_CODE'
976 );
977 RAISE fnd_api.g_exc_error;
978 END IF;
979 END IF;
980
981 IF p_rule_header_rec.org_wide_flag NOT IN ('Y' , 'N') THEN
982 okc_api.set_message (p_app_name => g_app_name,
983 p_msg_name => l_okc_i_invalid_value,
984 p_token1 => l_field,
985 p_token1_value => 'ORG_WIDE_FLAG'
986 );
987 RAISE fnd_api.g_exc_error;
988 END IF;
989
990 EXCEPTION
991 WHEN fnd_api.g_exc_error
992 THEN
993 set_proc_error_message (p_proc => l_proc);
994 RAISE;
995 WHEN OTHERS
996 THEN
997 set_proc_error_message (p_proc => l_proc);
998 RAISE;
999 END validate_header;
1000 BEGIN
1001 -- Default Header
1002 default_rule_header (p_rule_header_rec => p_rule_header_rec);
1003
1004 -- Validate Operating Unit
1005 OPEN cur_val_ou (p_rule_header_rec.org_id);
1006
1007 FETCH cur_val_ou
1008 INTO l_validate_flag;
1009
1010 CLOSE cur_val_ou;
1011
1012 IF NVL (l_validate_flag, 'Y') <> 'X'
1013 THEN
1014 okc_api.set_message (p_app_name => g_app_name,
1015 p_msg_name => l_okc_i_invalid_value,
1016 p_token1 => l_field,
1017 p_token1_value => 'ORG_ID'
1018 );
1019 RAISE fnd_api.g_exc_error;
1020 END IF;
1021
1022 l_validate_flag := NULL;
1023
1024 -- Set Policy context
1025 mo_global.set_policy_context ('S', TO_CHAR (p_rule_header_rec.org_id));
1026 -- Validate Rule Header
1027 -- Rules
1028 -- Rule_Id => Can be generated from sequence OKC_XPRT_RULE_HDRS_ALL_S
1029 -- Org_Id => Derive or get it from the user input
1030 -- Intent => User must pass the intent and the allowed values are 'B', 'S'
1031 -- Status_Code => Default to 'DRAFT'
1032 -- Rule_Name,CONDITION_EXPR_CODE,RULE_TYPE,
1033 -- PUBLISHED_FLAG,OBJECT_VERSION_NUMBER
1034 -- CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE
1035 validate_header (p_rule_header_rec => p_rule_header_rec);
1036
1037 /*-- Get the rule id from Sequnce
1038 SELECT okc_xprt_rule_hdrs_all_s.NEXTVAL
1039 INTO p_rule_header_rec.rule_id
1040 FROM DUAL;*/
1041
1042 -- Insert into Rule Header
1043 INSERT INTO okc_xprt_rule_hdrs_all
1044 (rule_id,
1045 org_id, intent,
1046 status_code,
1047 rule_name,
1048 rule_description,
1049 org_wide_flag,
1050 published_flag,
1051 condition_expr_code,
1052 request_id,
1053 object_version_number,
1054 created_by,
1055 creation_date,
1056 last_updated_by,
1057 last_update_date,
1058 last_update_login,
1059 program_id,
1060 program_application_id,
1061 program_update_date,
1062 rule_type,
1063 line_level_flag
1064 )
1065 VALUES (okc_xprt_rule_hdrs_all_s.NEXTVAL,
1066 p_rule_header_rec.org_id, p_rule_header_rec.intent,
1067 p_rule_header_rec.status_code,
1068 p_rule_header_rec.rule_name,
1069 p_rule_header_rec.rule_description,
1070 p_rule_header_rec.org_wide_flag,
1071 p_rule_header_rec.published_flag,
1072 p_rule_header_rec.condition_expr_code,
1073 p_rule_header_rec.request_id,
1074 p_rule_header_rec.object_version_number,
1075 p_rule_header_rec.created_by,
1076 p_rule_header_rec.creation_date,
1077 p_rule_header_rec.last_updated_by,
1078 p_rule_header_rec.last_update_date,
1079 p_rule_header_rec.last_update_login,
1080 p_rule_header_rec.program_id,
1081 p_rule_header_rec.program_application_id,
1082 p_rule_header_rec.program_update_date,
1083 p_rule_header_rec.rule_type,
1084 p_rule_header_rec.line_level_flag
1085 )
1086 RETURNING rule_id
1087 INTO p_rule_header_rec.rule_id;
1088 EXCEPTION
1089 WHEN fnd_api.g_exc_error
1090 THEN
1091 set_proc_error_message (p_proc => l_proc);
1092 RAISE;
1093 WHEN OTHERS
1094 THEN
1095 set_proc_error_message (p_proc => l_proc);
1096 RAISE;
1097 END create_rule_header;
1098
1099 PROCEDURE create_rule_header (
1100 p_rule_header_tbl IN OUT NOCOPY rule_header_tbl_type
1101 )
1102 IS
1103 BEGIN
1104 IF p_rule_header_tbl.COUNT > 0
1105 THEN
1106 FOR i IN p_rule_header_tbl.FIRST .. p_rule_header_tbl.LAST
1107 LOOP
1108 create_rule_header (p_rule_header_rec => p_rule_header_tbl (i));
1109 END LOOP;
1110 END IF;
1111 END create_rule_header;
1112
1113 /*
1114 OBJECT_TYPE: => Required
1115 Indicates if condition type is a clause, system or user defined variable or question.
1116 FK to FND lookup OKC_XPRT_COND_OBJECT_TYPE
1117 Valid values are CLAUSE, QUESTION, VARIABLE (System Variable)
1118
1119 OBJECT_CODE:
1120 This is used where the condition types are either system or user defined variables or questoins.
1121 The code identifies the actual system or user defined variable or the question used in the condition.
1122 For clauses, this will be NULL.
1123
1124 object_code_datatype:
1125 Indicates the datatype for the object_code. For Clause this is NULL.
1126 Used values are 'N': Numeric variable or Numeric Question, 'V': Character variable,
1127 'L': Character Question, 'B': Boolean (Yes/No) quesion.
1128
1129 operator: => Required
1130 Condition operator for Numeric variables and Questions: <=, >=, <>, = , >, <.
1131 For Character type variables and questions: IS, IS NOT, IN, NOT IN.
1132
1133 object_value_set_name:
1134 FND value set name of Variables / Questions for the RHS of condition.
1135
1136 object_value_type: => Required
1137 Indicates if the RHS of condition is CLAUSE, VARIABLE, QUESTION, CONSTANT, VALUE.
1138 FK to FND lookup OKC_XPRT_COND_VALUE_TYPE
1139
1140 object_value_code:
1141 Variable's value Id or Variable code or Clause Id or Constant Id or Question Id.
1142 This will be populated only if the condition has one value.
1143
1144 Business Rules:
1145 Condition Type: The Clause type is available only for Clause Creation rules i.e
1146 Rule type is: G_CLAUSE_RULE_TYPE
1147
1148 Variable: Use this type for the Clause Creation rules if a
1149 system variable drives the clause selection on a business document.
1150
1151 For Policy Deviation rules, use this type if a system or user-defined variable
1152 drives the policy deviation for a business document.
1153
1154 Question : Use this type if a user question should drive the clause selection or
1155 policy deviation on business documents.
1156
1157 */
1158 PROCEDURE create_rule_condition (
1159 p_rule_condition_rec IN OUT NOCOPY rule_condition_rec_type
1160 )
1161 IS
1162 l_rule_type VARCHAR2 (30);
1163 l_rule_condition_id NUMBER;
1164
1165 PROCEDURE default_rule_condition (
1166 p_rule_condition_rec IN OUT NOCOPY rule_condition_rec_type
1167 )
1168 IS
1169 BEGIN
1170 IF p_rule_condition_rec.object_type = okc_api.g_miss_char
1171 THEN
1172 p_rule_condition_rec.object_type := NULL;
1173 END IF;
1174
1175 IF p_rule_condition_rec.object_code = okc_api.g_miss_char
1176 THEN
1177 p_rule_condition_rec.object_code := NULL;
1178 END IF;
1179
1180 IF p_rule_condition_rec.object_code_datatype = okc_api.g_miss_char
1181 THEN
1182 p_rule_condition_rec.object_code_datatype := NULL;
1183 END IF;
1184
1185 IF p_rule_condition_rec.OPERATOR = okc_api.g_miss_char
1186 THEN
1187 p_rule_condition_rec.OPERATOR := NULL;
1188 END IF;
1189
1190 IF p_rule_condition_rec.object_value_set_name = okc_api.g_miss_char
1191 THEN
1192 p_rule_condition_rec.object_value_set_name := NULL;
1193 END IF;
1194
1195 IF p_rule_condition_rec.object_value_type = okc_api.g_miss_char
1196 THEN
1197 p_rule_condition_rec.object_value_type := NULL;
1198 END IF;
1199
1200 IF p_rule_condition_rec.object_value_code = okc_api.g_miss_char
1201 THEN
1202 p_rule_condition_rec.object_value_code := NULL;
1203 END IF;
1204
1205 IF p_rule_condition_rec.object_version_number = okc_api.g_miss_num
1206 THEN
1207 p_rule_condition_rec.object_version_number := 1;
1208 END IF;
1209 END default_rule_condition;
1210
1211 PROCEDURE validate_rule_condition (
1212 p_rule_condition_rec IN OUT NOCOPY rule_condition_rec_type
1213 )
1214 IS
1215 CURSOR c_validate_lookup (
1216 p_lookup_type VARCHAR2,
1217 p_lookup_code VARCHAR2
1218 )
1219 IS
1220 SELECT 'Y'
1221 FROM fnd_lookup_values
1222 WHERE lookup_type = p_lookup_type
1223 AND lookup_code = p_lookup_code
1224 AND LANGUAGE = 'US'
1225 AND enabled_flag = 'Y'
1226 AND NVL (end_date_active, SYSDATE) >= SYSDATE;
1227
1228 l_validate_flag VARCHAR2 (1);
1229 i NUMBER;
1230 l_proc VARCHAR2 (60) := 'VALIDATE_RULE_CONDITION';
1231 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE
1232 := g_rule_type;
1233 l_rule_intent okc_xprt_rule_hdrs_all.intent%TYPE
1234 := g_rule_intent;
1235 l_question_datatype VARCHAR2 (1);
1236 l_value_set_name VARCHAR2 (60);
1237 l_operator_lookup VARCHAR2 (240);
1238 x_valid_variable VARCHAR2 (1);
1239 x_vlaue_set_id NUMBER;
1240 x_value_set_name VARCHAR2 (240);
1241 x_variable_datatype VARCHAR2 (60);
1242 BEGIN
1243 /*
1244 SELECT rule_type, intent
1245 INTO l_rule_type, l_rule_intent
1246 FROM okc_xprt_rule_hdrs_all
1247 WHERE rule_id = p_rule_condition_rec.rule_id;
1248
1249 */
1250 IF p_rule_condition_rec.rule_id IS NULL
1251 THEN
1252 okc_api.set_message (p_app_name => g_app_name,
1253 p_msg_name => l_okc_i_not_null,
1254 p_token1 => l_field,
1255 p_token1_value => 'RULE_ID'
1256 );
1257 RAISE fnd_api.g_exc_error;
1258 END IF;
1259
1260 IF p_rule_condition_rec.object_type IS NULL
1261 THEN
1262 okc_api.set_message (p_app_name => g_app_name,
1263 p_msg_name => l_okc_i_not_null,
1264 p_token1 => l_field,
1265 p_token1_value => 'OBJECT_TYPE'
1266 );
1267 RAISE fnd_api.g_exc_error;
1268 END IF;
1269
1270 IF p_rule_condition_rec.object_type <> 'CLAUSE'
1271 AND p_rule_condition_rec.object_code IS NULL
1272 THEN
1273 okc_api.set_message (p_app_name => g_app_name,
1274 p_msg_name => l_okc_i_not_null,
1275 p_token1 => l_field,
1276 p_token1_value => 'OBJECT_CODE'
1277 );
1278 RAISE fnd_api.g_exc_error;
1279 END IF;
1280
1281
1282
1283 IF p_rule_condition_rec.OPERATOR IS NULL
1284 THEN
1285 okc_api.set_message (p_app_name => g_app_name,
1286 p_msg_name => l_okc_i_not_null,
1287 p_token1 => l_field,
1288 p_token1_value => 'OPERATOR'
1289 );
1290 RAISE fnd_api.g_exc_error;
1291 END IF;
1292
1293 IF p_rule_condition_rec.object_type <> 'CLAUSE'
1294 AND p_rule_condition_rec.object_value_type IS NULL
1295 THEN
1296 okc_api.set_message (p_app_name => g_app_name,
1297 p_msg_name => l_okc_i_not_null,
1298 p_token1 => l_field,
1299 p_token1_value => 'OBJECT_VALUE_TYPE'
1300 );
1301 RAISE fnd_api.g_exc_error;
1302 END IF;
1303
1304 IF NOT (p_rule_condition_rec.rule_cond_vals_tbl.COUNT > 0)
1305 THEN
1306 okc_api.set_message (p_app_name => g_app_name,
1307 p_msg_name => l_okc_i_not_null,
1308 p_token1 => l_field,
1309 p_token1_value => 'RULE_COND_VALS_TBL'
1310 );
1311 RAISE fnd_api.g_exc_error;
1312 END IF;
1313
1314 IF l_rule_type = g_clause_rule_type
1315 AND p_rule_condition_rec.object_type NOT IN
1316 ('CLAUSE', 'QUESTION', 'VARIABLE')
1317 THEN
1318 okc_api.set_message (p_app_name => g_app_name,
1319 p_msg_name => l_okc_i_invalid_value,
1320 p_token1 => l_field,
1321 p_token1_value => 'OBJECT_TYPE'
1322 );
1323 RAISE fnd_api.g_exc_error;
1324 END IF;
1325
1326 IF l_rule_type = g_term_dev_rule_type
1327 AND p_rule_condition_rec.object_type NOT IN
1328 ('QUESTION', 'VARIABLE')
1329 THEN
1330 okc_api.set_message (p_app_name => g_app_name,
1331 p_msg_name => l_okc_i_invalid_value,
1332 p_token1 => l_field,
1333 p_token1_value => 'OBJECT_TYPE'
1334 );
1335 RAISE fnd_api.g_exc_error;
1336 END IF;
1337
1338 IF p_rule_condition_rec.object_type = 'CLAUSE'
1339 THEN
1340 -- Set Object Code to null
1341 p_rule_condition_rec.object_code := NULL;
1342 -- Object Code type will also be null
1343 p_rule_condition_rec.object_code_datatype := NULL;
1344
1345 -- Operator can't be null
1346 -- OKC_XPRT_CHAR_OPERATOR
1347 -- OKC_XPRT_NUMBER_OPERATOR
1348 OPEN c_validate_lookup ('OKC_XPRT_CHAR_OPERATOR',
1349 p_rule_condition_rec.OPERATOR
1350 );
1351
1352 FETCH c_validate_lookup
1353 INTO l_validate_flag;
1354
1355 IF c_validate_lookup%NOTFOUND
1356 THEN
1357 okc_api.set_message (p_app_name => g_app_name,
1358 p_msg_name => l_okc_i_invalid_value,
1359 p_token1 => l_field,
1360 p_token1_value => 'OPERATOR'
1361 );
1362 RAISE fnd_api.g_exc_error;
1363 END IF;
1364
1365 CLOSE c_validate_lookup;
1366
1367 -- Set the object_value_set_name to null;
1368 p_rule_condition_rec.object_value_set_name := NULL;
1369 -- set the object_value_type to 'CLAUSE'
1370 p_rule_condition_rec.object_value_type := 'CLAUSE';
1371 -- Validate the p_rule_condition_rec.rule_cond_vals_tbl
1372 validate_rule_condn_val_tbl
1373 (p_object_value_type => 'CLAUSE',
1374 p_rule_intent => l_rule_intent,
1375 p_rule_cond_vals_tbl => p_rule_condition_rec.rule_cond_vals_tbl
1376 );
1377 ELSIF p_rule_condition_rec.object_type = 'QUESTION'
1378 THEN
1379 IF is_valid_question
1380 (p_question_id => TO_NUMBER
1381 (p_rule_condition_rec.object_code),
1382 p_intent => l_rule_intent
1383 ) <> 'Y'
1384 THEN
1385 okc_api.set_message (p_app_name => g_app_name,
1386 p_msg_name => l_okc_i_invalid_value,
1387 p_token1 => l_field,
1388 p_token1_value => 'OBJECT_CODE'
1389 );
1390 RAISE fnd_api.g_exc_error;
1391 END IF;
1392
1393 get_question_details
1394 (p_question_id => TO_NUMBER
1395 (p_rule_condition_rec.object_code
1396 ),
1397 x_question_datatype => l_question_datatype,
1398 x_value_set_name => l_value_set_name
1399 );
1400 p_rule_condition_rec.object_code_datatype := l_question_datatype;
1401 p_rule_condition_rec.object_value_set_name := l_value_set_name;
1402
1403 -- Operator can't be null
1404 -- OKC_XPRT_CHAR_OPERATOR
1405 -- OKC_XPRT_NUMBER_OPERATOR
1406 IF p_rule_condition_rec.object_code_datatype = 'B'
1407 THEN
1408 IF p_rule_condition_rec.OPERATOR NOT IN ('IS', 'IS_NOT')
1409 THEN
1410 okc_api.set_message (p_app_name => g_app_name,
1411 p_msg_name => l_okc_i_invalid_value,
1412 p_token1 => l_field,
1413 p_token1_value => 'OPERATOR'
1414 );
1415 RAISE fnd_api.g_exc_error;
1416 END IF;
1417 ELSIF p_rule_condition_rec.object_code_datatype = 'L'
1418 THEN
1419 l_operator_lookup := 'OKC_XPRT_CHAR_OPERATOR';
1420 ELSIF p_rule_condition_rec.object_code_datatype = 'N'
1421 THEN
1422 l_operator_lookup := 'OKC_XPRT_NUMBER_OPERATOR';
1423 END IF;
1424
1425 IF NVL (l_operator_lookup, 'X') IN
1426 ('OKC_XPRT_CHAR_OPERATOR', 'OKC_XPRT_NUMBER_OPERATOR')
1427 THEN
1428 OPEN c_validate_lookup (l_operator_lookup,
1429 p_rule_condition_rec.OPERATOR
1430 );
1431
1432 FETCH c_validate_lookup
1433 INTO l_validate_flag;
1434
1435 IF c_validate_lookup%NOTFOUND
1436 THEN
1437 okc_api.set_message (p_app_name => g_app_name,
1438 p_msg_name => l_okc_i_invalid_value,
1439 p_token1 => l_field,
1440 p_token1_value => 'OPERATOR'
1441 );
1442 RAISE fnd_api.g_exc_error;
1443 END IF;
1444
1445 CLOSE c_validate_lookup;
1446 END IF;
1447
1448 IF p_rule_condition_rec.object_value_type NOT IN
1449 ('VALUE', 'QUESTION', 'VARIABLE', 'CONSTANT')
1450 THEN
1451 okc_api.set_message (p_app_name => g_app_name,
1452 p_msg_name => l_okc_i_invalid_value,
1453 p_token1 => l_field,
1454 p_token1_value => 'OBJECT_VALUE_TYPE'
1455 );
1456 RAISE fnd_api.g_exc_error;
1457 END IF;
1458
1459 IF p_rule_condition_rec.object_type <> 'CLAUSE'
1460 AND p_rule_condition_rec.object_code_datatype IS NULL
1461 THEN
1462 okc_api.set_message (p_app_name => g_app_name,
1463 p_msg_name => l_okc_i_not_null,
1464 p_token1 => l_field,
1465 p_token1_value => 'OBJECT_CODE_DATATYPE'
1466 );
1467 RAISE fnd_api.g_exc_error;
1468 END IF;
1469
1470 validate_rule_condn_val_tbl
1471 (p_object_value_type => p_rule_condition_rec.object_value_type,
1472 p_rule_intent => l_rule_intent,
1473 p_value_set_name => p_rule_condition_rec.object_value_set_name,
1474 p_rule_cond_vals_tbl => p_rule_condition_rec.rule_cond_vals_tbl
1475 );
1476 ELSIF p_rule_condition_rec.object_type = 'VARIABLE'
1477 THEN
1478 is_valid_variable
1479 (p_variable_code => p_rule_condition_rec.object_code,
1480 p_intent => l_rule_intent,
1481 x_valid_variable => x_valid_variable,
1482 x_vlaue_set_id => x_vlaue_set_id,
1483 x_value_set_name => x_value_set_name,
1484 x_variable_datatype => x_variable_datatype
1485 );
1486
1487 IF x_valid_variable <> 'Y'
1488 THEN
1489 okc_api.set_message (p_app_name => g_app_name,
1490 p_msg_name => l_okc_i_invalid_value,
1491 p_token1 => l_field,
1492 p_token1_value => 'OBJECT_CODE'
1493 );
1494 RAISE fnd_api.g_exc_error;
1495 END IF;
1496
1497 p_rule_condition_rec.object_code_datatype := x_variable_datatype;
1498 p_rule_condition_rec.object_value_set_name := x_value_set_name;
1499
1500 IF p_rule_condition_rec.object_code_datatype = 'V'
1501 THEN
1502 l_operator_lookup := 'OKC_XPRT_CHAR_OPERATOR';
1503 ELSIF p_rule_condition_rec.object_code_datatype = 'N'
1504 THEN
1505 l_operator_lookup := 'OKC_XPRT_NUMBER_OPERATOR';
1506 END IF;
1507
1508 OPEN c_validate_lookup (l_operator_lookup,
1509 p_rule_condition_rec.OPERATOR
1510 );
1511
1512 FETCH c_validate_lookup
1513 INTO l_validate_flag;
1514
1515 IF c_validate_lookup%NOTFOUND
1516 THEN
1517 okc_api.set_message (p_app_name => g_app_name,
1518 p_msg_name => l_okc_i_invalid_value,
1519 p_token1 => l_field,
1520 p_token1_value => 'OPERATOR'
1521 );
1522 RAISE fnd_api.g_exc_error;
1523 END IF;
1524
1525 CLOSE c_validate_lookup;
1526
1527 IF p_rule_condition_rec.object_value_type NOT IN
1528 ('VALUE', 'QUESTION', 'VARIABLE', 'CONSTANT')
1529 THEN
1530 okc_api.set_message (p_app_name => g_app_name,
1531 p_msg_name => l_okc_i_invalid_value,
1532 p_token1 => l_field,
1533 p_token1_value => 'OBJECT_VALUE_TYPE'
1534 );
1535 RAISE fnd_api.g_exc_error;
1536 END IF;
1537
1538
1539
1540 validate_rule_condn_val_tbl
1541 (p_object_value_type => p_rule_condition_rec.object_value_type,
1542 p_rule_intent => l_rule_intent,
1543 p_value_set_name => p_rule_condition_rec.object_value_set_name,
1544 p_rule_cond_vals_tbl => p_rule_condition_rec.rule_cond_vals_tbl
1545 );
1546 END IF;
1547
1548 IF
1549 -- p_rule_condition_rec.rule_cond_vals_tbl.Count=1
1550 p_rule_condition_rec.OPERATOR NOT IN ('IN', 'NOT_IN')
1551 THEN
1552 --i := p_rule_condition_rec.rule_cond_vals_tbl.first;
1553 p_rule_condition_rec.object_value_code :=
1554 p_rule_condition_rec.rule_cond_vals_tbl (1).object_value_code;
1555 ELSE
1556 p_rule_condition_rec.object_value_code := NULL;
1557 END IF;
1558 EXCEPTION
1559 WHEN OTHERS
1560 THEN
1561 set_proc_error_message (p_proc => l_proc);
1562 RAISE;
1563 END validate_rule_condition;
1564 BEGIN
1565 -- Default --> Set the unpassed values to null
1566 default_rule_condition (p_rule_condition_rec => p_rule_condition_rec);
1567 -- Validate n Derive
1568 validate_rule_condition (p_rule_condition_rec => p_rule_condition_rec);
1569
1570 /*SELECT okc_xprt_rule_condition_s.NEXTVAL
1571 INTO p_rule_condition_rec.rule_condition_id
1572 FROM DUAL; */
1573
1574 -- Insert
1575 INSERT INTO okc_xprt_rule_conditions
1576 (rule_condition_id,
1577 rule_id,
1578 object_type,
1579 object_code,
1580 object_code_datatype,
1581 OPERATOR,
1582 object_value_set_name,
1583 object_value_type,
1584 object_value_code, object_version_number,
1585 created_by, creation_date, last_updated_by,
1586 last_update_date, last_update_login
1587 )
1588 VALUES (okc_xprt_rule_condition_s.NEXTVAL,
1589 p_rule_condition_rec.rule_id,
1590 -- Need to see to pass or derive
1591 p_rule_condition_rec.object_type,
1592 p_rule_condition_rec.object_code,
1593 p_rule_condition_rec.object_code_datatype,
1594 p_rule_condition_rec.OPERATOR,
1595 p_rule_condition_rec.object_value_set_name,
1596 p_rule_condition_rec.object_value_type,
1597 p_rule_condition_rec.object_value_code, 1,
1598 fnd_global.user_id, SYSDATE, fnd_global.user_id,
1599 SYSDATE, fnd_global.login_id
1600 )
1601 RETURNING rule_condition_id
1602 INTO p_rule_condition_rec.rule_condition_id;
1603
1604 -- Values
1605 create_rule_condn_value
1606 (p_rule_condition_id => p_rule_condition_rec.rule_condition_id,
1607 p_rule_cond_vals_tbl => p_rule_condition_rec.rule_cond_vals_tbl
1608 );
1609 -- Insert
1610 EXCEPTION
1611 WHEN OTHERS
1612 THEN
1613 RAISE;
1614 END create_rule_condition;
1615
1616 PROCEDURE create_rule_condition (
1617 p_rule_condition_tbl IN OUT NOCOPY rule_condition_tbl_type
1618 )
1619 IS
1620 l_proc VARCHAR2 (60) := 'CREATE_RULE_CONDITION';
1621 l_failed_rec_num NUMBER := 0;
1622 BEGIN
1623 FOR i IN p_rule_condition_tbl.FIRST .. p_rule_condition_tbl.LAST
1624 LOOP
1625 l_failed_rec_num := i;
1626
1627 IF p_rule_condition_tbl (i).rule_id <> okc_api.g_miss_num
1628 AND g_rule_id <> NVL (p_rule_condition_tbl (i).rule_id, g_rule_id)
1629 THEN
1630 okc_api.set_message (p_app_name => g_app_name,
1631 p_msg_name => 'OKC_I_RULE_NO_MATCH',
1632 p_token1 => 'ENTITY',
1633 p_token1_value => 'CONDITION'
1634 );
1635 RAISE fnd_api.g_exc_error;
1636 END IF;
1637
1638 p_rule_condition_tbl (i).rule_id := g_rule_id;
1639 create_rule_condition
1640 (p_rule_condition_rec => p_rule_condition_tbl
1641 (i)
1642 );
1643 END LOOP;
1644 EXCEPTION
1645 WHEN OTHERS
1646 THEN
1647 set_proc_error_message (p_proc => l_proc);
1648 set_rec_num_message (p_rec_num => l_failed_rec_num);
1649 RAISE;
1650 END create_rule_condition;
1651
1652 /*This procedure assumes the Condition values has been validated */
1653 PROCEDURE create_rule_condn_value (
1654 p_rule_condition_id IN NUMBER,
1655 p_rule_cond_vals_tbl IN OUT NOCOPY rule_cond_vals_tbl_type
1656 )
1657 IS
1658 l_proc VARCHAR2 (60) := 'CREATE_RULE_CONDN_VALUE';
1659 l_failed_rec_num NUMBER := 0;
1660 BEGIN
1661 FOR i IN p_rule_cond_vals_tbl.FIRST .. p_rule_cond_vals_tbl.LAST
1662 LOOP
1663 l_failed_rec_num := i;
1664
1665 INSERT INTO okc_xprt_rule_cond_vals
1666 (rule_condition_value_id,
1667 rule_condition_id,
1668 object_value_code, object_version_number,
1669 created_by, creation_date, last_updated_by,
1670 last_update_date, last_update_login
1671 )
1672 VALUES (okc_xprt_rule_cond_vals_s.NEXTVAL
1673 --p_rule_cond_vals_tbl(i).rule_condition_value_id
1674 ,
1675 p_rule_condition_id
1676 --p_rule_cond_vals_tbl(i).rule_condition_id
1677 ,
1678 p_rule_cond_vals_tbl (i).object_value_code, 1,
1679 fnd_global.user_id, SYSDATE, fnd_global.user_id,
1680 SYSDATE, fnd_global.login_id
1681 )
1682 RETURNING rule_condition_value_id,
1683 rule_condition_id
1684 INTO p_rule_cond_vals_tbl (i).rule_condition_value_id,
1685 p_rule_cond_vals_tbl (i).rule_condition_id;
1686 END LOOP;
1687 EXCEPTION
1688 WHEN OTHERS
1689 THEN
1690 set_proc_error_message (p_proc => l_proc);
1691 set_rec_num_message (p_rec_num => l_failed_rec_num);
1692 RAISE;
1693 END create_rule_condn_value;
1694
1695 PROCEDURE create_rule_outcome (
1696 p_rule_outcome_rec IN OUT NOCOPY rule_outcome_rec_type
1697 )
1698 IS
1699 PROCEDURE validate_rule_outcome (
1700 p_rule_outcome_rec IN OUT NOCOPY rule_outcome_rec_type
1701 )
1702 IS
1703 l_proc VARCHAR2 (60) := 'VALIDATE_RULE_OUTCOME';
1704 BEGIN
1705 IF p_rule_outcome_rec.rule_id IS NULL
1706 THEN
1707 okc_api.set_message (p_app_name => g_app_name,
1708 p_msg_name => l_okc_i_not_null,
1709 p_token1 => l_field,
1710 p_token1_value => 'RULE_ID'
1711 );
1712 RAISE fnd_api.g_exc_error;
1713 END IF;
1714
1715 IF p_rule_outcome_rec.object_type IS NULL
1716 THEN
1717 okc_api.set_message (p_app_name => g_app_name,
1718 p_msg_name => l_okc_i_not_null,
1719 p_token1 => l_field,
1720 p_token1_value => 'OBJECT_TYPE'
1721 );
1722 RAISE fnd_api.g_exc_error;
1723 ELSE
1724 IF p_rule_outcome_rec.object_type NOT IN ('QUESTION', 'CLAUSE')
1725 THEN
1726 okc_api.set_message (p_app_name => g_app_name,
1727 p_msg_name => l_okc_i_invalid_value,
1728 p_token1 => l_field,
1729 p_token1_value => 'OBJECT_TYPE'
1730 );
1731 RAISE fnd_api.g_exc_error;
1732 END IF;
1733 END IF;
1734
1735 IF p_rule_outcome_rec.object_value_id IS NULL
1736 THEN
1737 okc_api.set_message (p_app_name => g_app_name,
1738 p_msg_name => l_okc_i_not_null,
1739 p_token1 => l_field,
1740 p_token1_value => 'OBJECT_VALUE_ID'
1741 );
1742 RAISE fnd_api.g_exc_error;
1743 END IF;
1744
1745 IF is_valid_rule (p_rule_id => p_rule_outcome_rec.rule_id) <> 'Y'
1746 THEN
1747 okc_api.set_message (p_app_name => g_app_name,
1748 p_msg_name => l_okc_i_invalid_value,
1749 p_token1 => l_field,
1750 p_token1_value => 'RULE_ID'
1751 );
1752 RAISE fnd_api.g_exc_error;
1753 END IF;
1754
1755 IF g_rule_type <> g_clause_rule_type
1756 THEN
1757 okc_api.set_message (p_app_name => g_app_name,
1758 p_msg_name => l_okc_i_invalid_value,
1759 p_token1 => l_field,
1760 p_token1_value => 'RULE_TYPE'
1761 );
1762 RAISE fnd_api.g_exc_error;
1763 END IF;
1764
1765 IF is_valid_outcome
1766 (p_rule_id => p_rule_outcome_rec.rule_id,
1767 p_object_type => p_rule_outcome_rec.object_type,
1768 p_object_value_id => p_rule_outcome_rec.object_value_id
1769 ) <> 'Y'
1770 THEN
1771 okc_api.set_message (p_app_name => g_app_name,
1772 p_msg_name => l_okc_i_invalid_value,
1773 p_token1 => l_field,
1774 p_token1_value => 'OUTCOME'
1775 );
1776 RAISE fnd_api.g_exc_error;
1777 END IF;
1778
1779 IF is_duplicate_outcome
1780 (p_rule_id => p_rule_outcome_rec.rule_id,
1781 p_object_type => p_rule_outcome_rec.object_type,
1782 p_object_value_id => p_rule_outcome_rec.object_value_id
1783 ) = 'Y'
1784 THEN
1785 -- Duplicate Outcome
1786 okc_api.set_message (p_app_name => g_app_name,
1787 p_msg_name => l_okc_i_invalid_value,
1788 p_token1 => l_field,
1789 p_token1_value => 'OUTCOME'
1790 );
1791 RAISE fnd_api.g_exc_error;
1792 END IF;
1793 /* RWA Changes Start */
1794 IF p_rule_outcome_rec.mandatory_yn IS NOT NULL
1795 AND p_rule_outcome_rec.mandatory_yn NOT IN ('Y','N') THEN
1796
1797 okc_api.set_message (p_app_name => g_app_name,
1798 p_msg_name => l_okc_i_invalid_value,
1799 p_token1 => l_field,
1800 p_token1_value => 'MANDATORY_YN'
1801 );
1802 RAISE fnd_api.g_exc_error;
1803 END IF;
1804
1805 IF p_rule_outcome_rec.mandatory_rwa IS NOT NULL
1806 AND isValidLookup('OKC_CLAUSE_RWA', p_rule_outcome_rec.mandatory_rwa) = 'N' THEN
1807
1808 okc_api.set_message (p_app_name => g_app_name,
1809 p_msg_name => l_okc_i_invalid_value,
1810 p_token1 => l_field,
1811 p_token1_value => 'MANDATORY_RWA'
1812 );
1813 RAISE fnd_api.g_exc_error;
1814 END IF;
1815
1816 /* RWA Changes End */
1817
1818 END validate_rule_outcome;
1819 BEGIN
1820
1821 /* RWA Changes Start */
1822 -- Default
1823 IF p_rule_outcome_rec.mandatory_yn = OKC_API.G_MISS_CHAR
1824 THEN
1825 p_rule_outcome_rec.mandatory_yn := NULL;
1826 END IF;
1827
1828 IF p_rule_outcome_rec.mandatory_rwa = OKC_API.G_MISS_CHAR
1829 THEN
1830 p_rule_outcome_rec.mandatory_rwa := NULL;
1831 END IF;
1832 /* RWA Changes End */
1833
1834 validate_rule_outcome (p_rule_outcome_rec => p_rule_outcome_rec);
1835
1836 -- Insert into okc_xprt_rule_outcomes table
1837 INSERT INTO okc_xprt_rule_outcomes
1838 (rule_outcome_id,
1839 rule_id,
1840 object_type,
1841 object_value_id, object_version_number,
1842 created_by, creation_date, last_updated_by,
1843 last_update_date, last_update_login,mandatory_yn, mandatory_rwa
1844 )
1845 VALUES (okc_xprt_rule_outcomes_s.NEXTVAL,
1846 p_rule_outcome_rec.rule_id,
1847 p_rule_outcome_rec.object_type,
1848 p_rule_outcome_rec.object_value_id, 1,
1849 fnd_global.user_id, SYSDATE, fnd_global.user_id,
1850 SYSDATE, fnd_global.login_id,p_rule_outcome_rec.mandatory_yn, p_rule_outcome_rec.mandatory_rwa
1851 )
1852 RETURNING rule_outcome_id
1853 INTO p_rule_outcome_rec.rule_outcome_id;
1854 EXCEPTION
1855 WHEN OTHERS
1856 THEN
1857 RAISE;
1858 END create_rule_outcome;
1859
1860 PROCEDURE create_rule_outcome (
1861 p_rule_outcome_tbl IN OUT NOCOPY rule_outcome_tbl_type
1862 )
1863 IS
1864 l_proc VARCHAR2 (60) := 'CREATE_RULE_OUTCOME';
1865 l_failed_rec_num NUMBER := 0;
1866 BEGIN
1867 IF p_rule_outcome_tbl.COUNT > 0
1868 THEN
1869 FOR i IN p_rule_outcome_tbl.FIRST .. p_rule_outcome_tbl.LAST
1870 LOOP
1871 l_failed_rec_num := i;
1872
1873 IF p_rule_outcome_tbl (i).rule_id <> okc_api.g_miss_num
1874 AND g_rule_id <>
1875 NVL (p_rule_outcome_tbl (i).rule_id, g_rule_id)
1876 THEN
1877 okc_api.set_message (p_app_name => g_app_name,
1878 p_msg_name => 'OKC_I_RULE_NO_MATCH',
1879 p_token1 => 'ENTITY',
1880 p_token1_value => 'OUTCOME'
1881 );
1882 RAISE fnd_api.g_exc_error;
1883 END IF;
1884
1885 p_rule_outcome_tbl (i).rule_id := g_rule_id;
1886 create_rule_outcome (p_rule_outcome_rec => p_rule_outcome_tbl
1887 (i));
1888 END LOOP;
1889 END IF;
1890 EXCEPTION
1891 WHEN OTHERS
1892 THEN
1893 set_proc_error_message (p_proc => l_proc);
1894 set_rec_num_message (p_rec_num => l_failed_rec_num);
1895 RAISE;
1896 END create_rule_outcome;
1897
1898 PROCEDURE create_template_rules (
1899 p_template_rules_tbl IN OUT NOCOPY template_rules_tbl_type
1900 )
1901 IS
1902 l_proc VARCHAR2 (60) := 'CREATE_TEMPLATE_RULES';
1903 l_failed_rec_num NUMBER := 0;
1904 BEGIN
1905 IF p_template_rules_tbl.COUNT > 0
1906 THEN
1907 FOR i IN p_template_rules_tbl.FIRST .. p_template_rules_tbl.LAST
1908 LOOP
1909 l_failed_rec_num := i;
1910
1911 IF p_template_rules_tbl (i).rule_id <> okc_api.g_miss_num
1912 AND g_rule_id <>
1913 NVL (p_template_rules_tbl (i).rule_id, g_rule_id)
1914 THEN
1915 okc_api.set_message (p_app_name => g_app_name,
1916 p_msg_name => 'OKC_I_RULE_NO_MATCH',
1917 p_token1 => 'ENTITY',
1918 p_token1_value => 'TEMPLATE_RULE'
1919 );
1920 RAISE fnd_api.g_exc_error;
1921 END IF;
1922
1923 create_template_rules
1924 (p_template_rules_rec => p_template_rules_tbl
1925 (i)
1926 );
1927 END LOOP;
1928 END IF;
1929 EXCEPTION
1930 WHEN OTHERS
1931 THEN
1932 set_rec_num_message (p_rec_num => l_failed_rec_num);
1933 set_proc_error_message (p_proc => l_proc);
1934 RAISE;
1935 END create_template_rules;
1936
1937 PROCEDURE create_template_rules (
1938 p_template_rules_rec IN OUT NOCOPY template_rules_rec_type
1939 )
1940 IS
1941 CURSOR cur_val_template
1942 IS
1943 SELECT 'Y'
1944 FROM okc_terms_templates_all temp, fnd_lookups status
1945 WHERE intent = g_rule_intent
1946 AND status.lookup_code = temp.status_code
1947 AND status.lookup_type = 'OKC_TERMS_TMPL_STATUS'
1948 AND contract_expert_enabled = 'Y'
1949 AND NVL (end_date, SYSDATE) >= SYSDATE
1950 AND org_id = g_org_id;
1951
1952 CURSOR cur_dup_template
1953 IS
1954 SELECT 'Y'
1955 FROM okc_xprt_template_rules
1956 WHERE template_id = p_template_rules_rec.template_id
1957 AND rule_id = g_rule_id
1958 AND deleted_flag = NVL (p_template_rules_rec.deleted_flag, 'N');
1959
1960 l_val_flag VARCHAR2 (1);
1961 l_dup_flag VARCHAR2 (1);
1962 BEGIN
1963 -- Default
1964 /*SELECT okc_xprt_template_rules_s.NEXTVAL
1965 INTO p_template_rules_rec.template_rule_id
1966 FROM DUAL;*/
1967 p_template_rules_rec.rule_id := g_rule_id;
1968 p_template_rules_rec.deleted_flag := 'N';
1969 p_template_rules_rec.published_flag := NULL;
1970 p_template_rules_rec.object_version_number := 1;
1971 p_template_rules_rec.created_by := fnd_global.user_id;
1972 p_template_rules_rec.creation_date := SYSDATE;
1973 p_template_rules_rec.last_updated_by := fnd_global.user_id;
1974 p_template_rules_rec.last_update_date := SYSDATE;
1975 p_template_rules_rec.last_update_login := fnd_global.login_id;
1976
1977 -- Validate
1978 IF p_template_rules_rec.template_id IS NULL
1979 THEN
1980 okc_api.set_message (p_app_name => g_app_name,
1981 p_msg_name => l_okc_i_not_null,
1982 p_token1 => l_field,
1983 p_token1_value => 'TEMPLATE_ID'
1984 );
1985 RAISE fnd_api.g_exc_error;
1986 ELSE
1987 OPEN cur_val_template;
1988
1989 FETCH cur_val_template
1990 INTO l_val_flag;
1991
1992 CLOSE cur_val_template;
1993
1994 IF NVL (l_val_flag, 'X') <> 'Y'
1995 THEN
1996 okc_api.set_message (p_app_name => g_app_name,
1997 p_msg_name => l_okc_i_invalid_value,
1998 p_token1 => l_field,
1999 p_token1_value => 'OUTCOME'
2000 );
2001 RAISE fnd_api.g_exc_error;
2002 END IF;
2003
2004 l_val_flag := NULL;
2005 END IF;
2006
2007 OPEN cur_dup_template;
2008
2009 FETCH cur_dup_template
2010 INTO l_dup_flag;
2011
2012 CLOSE cur_dup_template;
2013
2014 IF NVL (l_dup_flag, 'N') = 'Y'
2015 THEN
2016 okc_api.set_message (p_app_name => g_app_name,
2017 p_msg_name => 'OKC_XPRT_DUPLICATE_TMPL_ASSIGN'
2018 );
2019 RAISE fnd_api.g_exc_error;
2020 END IF;
2021
2022 -- Insert
2023 INSERT INTO okc_xprt_template_rules
2024 (template_rule_id,
2025 template_id,
2026 rule_id,
2027 deleted_flag,
2028 published_flag,
2029 object_version_number,
2030 created_by,
2031 creation_date,
2032 last_updated_by,
2033 last_update_date,
2034 last_update_login
2035 )
2036 VALUES (okc_xprt_template_rules_s.NEXTVAL,
2037 p_template_rules_rec.template_id,
2038 p_template_rules_rec.rule_id,
2039 p_template_rules_rec.deleted_flag,
2040 p_template_rules_rec.published_flag,
2041 p_template_rules_rec.object_version_number,
2042 p_template_rules_rec.created_by,
2043 p_template_rules_rec.creation_date,
2044 p_template_rules_rec.last_updated_by,
2045 p_template_rules_rec.last_update_date,
2046 p_template_rules_rec.last_update_login
2047 )
2048 RETURNING template_rule_id
2049 INTO p_template_rules_rec.template_rule_id;
2050 EXCEPTION
2051 WHEN OTHERS
2052 THEN
2053 RAISE;
2054 END create_template_rules;
2055
2056 /*
2057
2058 The system allows updates to the conditions, results, and template assignments.
2059 However, you cannot update the following fields:
2060 Operating Unit
2061 Rule Type => You cannot change the intent on a rule that has conditions, results or template assignments defined
2062 Name =>
2063 Intent => You cannot change the intent on a rule that has conditions, results or template assignments defined.
2064 Apply to All Templates
2065
2066 When Status is Draft you can not update the following:
2067 OU
2068 Rule Type
2069 Intent
2070
2071 -- Manual status changes are not allowed
2072 */
2073 PROCEDURE update_rule_header (
2074 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
2075 )
2076 IS
2077 l_progress VARCHAR2 (3) := '000';
2078 l_rule_header_row okc_xprt_rule_hdrs_all%ROWTYPE;
2079 l_proc VARCHAR2 (60) := 'UPDATE_RULE_HEADER';
2080
2081 PROCEDURE default_row (
2082 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type,
2083 p_db_rule_header IN okc_xprt_rule_hdrs_all%ROWTYPE
2084 )
2085 IS
2086 BEGIN
2087 p_rule_header_rec.last_updated_by := fnd_global.user_id;
2088 p_rule_header_rec.last_update_date := SYSDATE;
2089 p_rule_header_rec.last_update_login := fnd_global.login_id;
2090
2091 IF p_rule_header_rec.org_id = okc_api.g_miss_num
2092 THEN
2093 p_rule_header_rec.org_id := p_db_rule_header.org_id;
2094 END IF;
2095
2096 IF p_rule_header_rec.intent = okc_api.g_miss_char
2097 THEN
2098 p_rule_header_rec.intent := p_db_rule_header.intent;
2099 END IF;
2100
2101 IF p_rule_header_rec.status_code = okc_api.g_miss_char
2102 THEN
2103 p_rule_header_rec.status_code := p_db_rule_header.status_code;
2104 END IF;
2105
2106 IF p_rule_header_rec.rule_name = okc_api.g_miss_char
2107 THEN
2108 p_rule_header_rec.rule_name := p_db_rule_header.rule_name;
2109 END IF;
2110
2111 IF p_rule_header_rec.rule_description = okc_api.g_miss_char
2112 THEN
2113 p_rule_header_rec.rule_description :=
2114 p_db_rule_header.rule_description;
2115 END IF;
2116
2117 IF p_rule_header_rec.org_wide_flag = okc_api.g_miss_char
2118 THEN
2119 p_rule_header_rec.org_wide_flag := p_db_rule_header.org_wide_flag;
2120 END IF;
2121
2122 IF p_rule_header_rec.condition_expr_code = okc_api.g_miss_char
2123 THEN
2124 p_rule_header_rec.condition_expr_code :=
2125 p_db_rule_header.condition_expr_code;
2126 END IF;
2127
2128 IF p_rule_header_rec.rule_type = okc_api.g_miss_char
2129 THEN
2130 p_rule_header_rec.rule_type := p_db_rule_header.rule_type;
2131 END IF;
2132 EXCEPTION
2133 WHEN OTHERS
2134 THEN
2135 RAISE;
2136 END default_row;
2137
2138 PROCEDURE validate_row (
2139 p_rule_header_rec IN rule_header_rec_type,
2140 p_db_rule_header IN okc_xprt_rule_hdrs_all%ROWTYPE
2141 )
2142 IS
2143 l_proc VARCHAR2 (60) := 'VALIDATE_ROW';
2144
2145 CURSOR cur_rule_name_exists (p_rule_name VARCHAR2, p_org_id NUMBER)
2146 IS
2147 SELECT 'Y'
2148 FROM okc_xprt_rule_hdrs_all
2149 WHERE rule_name = p_rule_name AND org_id = p_org_id;
2150
2151 l_validate_flag VARCHAR2 (1);
2152 BEGIN
2153 IF p_rule_header_rec.rule_name IS NULL
2154 THEN
2155 okc_api.set_message (p_app_name => g_app_name,
2156 p_msg_name => l_okc_i_not_null,
2157 p_token1 => l_field,
2158 p_token1_value => 'RULE_NAME'
2159 );
2160 RAISE fnd_api.g_exc_error;
2161 ELSE
2162 IF p_rule_header_rec.rule_name <> p_db_rule_header.rule_name
2163 THEN
2164 OPEN cur_rule_name_exists (p_rule_header_rec.rule_name,
2165 p_rule_header_rec.org_id
2166 );
2167
2168 FETCH cur_rule_name_exists
2169 INTO l_validate_flag;
2170
2171 CLOSE cur_rule_name_exists;
2172
2173 IF NVL (l_validate_flag, 'X') = 'Y'
2174 THEN
2175 okc_api.set_message
2176 (p_app_name => g_app_name,
2177 p_msg_name => 'OKC_XPRT_RULE_NAME_EXISTS'
2178 );
2179 RAISE fnd_api.g_exc_error;
2180 END IF;
2181 END IF;
2182 END IF;
2183
2184 IF p_rule_header_rec.org_wide_flag NOT IN ('Y' , 'N') THEN
2185 okc_api.set_message (p_app_name => g_app_name,
2186 p_msg_name => l_okc_i_invalid_value,
2187 p_token1 => l_field,
2188 p_token1_value => 'ORG_WIDE_FLAG'
2189 );
2190 RAISE fnd_api.g_exc_error;
2191 END IF;
2192
2193 IF p_rule_header_rec.condition_expr_code NOT IN ('ALL', 'ANY')
2194 THEN
2195 okc_api.set_message (p_app_name => g_app_name,
2196 p_msg_name => l_okc_i_invalid_value,
2197 p_token1 => l_field,
2198 p_token1_value => 'CONDITION_EXPR_CODE'
2199 );
2200 RAISE fnd_api.g_exc_error;
2201 END IF;
2202
2203 EXCEPTION
2204 WHEN OTHERS
2205 THEN
2206 set_proc_error_message (p_proc => l_proc);
2207 RAISE;
2208 END validate_row;
2209
2210 PROCEDURE derive_row (
2211 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
2212 )
2213 IS
2214 BEGIN
2215 IF p_rule_header_rec.status_code = 'ACTIVE'
2216 THEN
2217 p_rule_header_rec.status_code := 'REVISION';
2218 END IF;
2219 EXCEPTION
2220 WHEN OTHERS
2221 THEN
2222 NULL;
2223 END derive_row;
2224
2225 PROCEDURE update_row (
2226 p_rule_header_rec IN OUT NOCOPY rule_header_rec_type
2227 )
2228 IS
2229 l_proc VARCHAR2 (60) := 'UPDATE_ROW';
2230 BEGIN
2231 UPDATE okc_xprt_rule_hdrs_all
2232 SET status_code = p_rule_header_rec.status_code,
2233 rule_name = p_rule_header_rec.rule_name,
2234 rule_description = p_rule_header_rec.rule_description,
2235 org_wide_flag = p_rule_header_rec.org_wide_flag,
2236 condition_expr_code = p_rule_header_rec.condition_expr_code,
2237 object_version_number = object_version_number + 1,
2238 last_updated_by = fnd_global.user_id,
2239 last_update_date = SYSDATE,
2240 last_update_login = fnd_global.login_id
2241 WHERE rule_id = p_rule_header_rec.rule_id;
2242 EXCEPTION
2243 WHEN OTHERS
2244 THEN
2245 set_proc_error_message (p_proc => l_proc);
2246 RAISE;
2247 END update_row;
2248 BEGIN
2249 -- Detect what values are changed and throw exception if the update is not allowed
2250
2251 -- Get the values from the db
2252 -- Compare it with the record
2253 l_progress := '010';
2254
2255 BEGIN
2256 l_progress := '015';
2257
2258 SELECT *
2259 INTO l_rule_header_row
2260 FROM okc_xprt_rule_hdrs_all
2261 WHERE rule_id = p_rule_header_rec.rule_id;
2262 EXCEPTION
2263 WHEN OTHERS
2264 THEN
2265 okc_api.set_message (p_app_name => g_app_name,
2266 p_msg_name => l_okc_i_invalid_value,
2267 p_token1 => l_field,
2268 p_token1_value => 'RULE_ID'
2269 );
2270 RAISE fnd_api.g_exc_error;
2271 END;
2272
2273 -- If status is Active(Pending), Disable (Pending), Disabled then do not allow update to the
2274 -- Rule.
2275 l_progress := '020';
2276
2277 IF l_rule_header_row.status_code IN
2278 ('PENDINGPUB', 'PENDINGDISABLE', 'INACTIVE')
2279 THEN
2280 l_progress := '025';
2281 -- Can't update anything just return the error.
2282 okc_api.set_message (p_app_name => g_app_name,
2283 p_msg_name => 'OKC_I_RULE_STS_NO_UPD',
2284 p_token1 => 'STATUS',
2285 p_token1_value => l_rule_header_row.status_code
2286 );
2287 RAISE fnd_api.g_exc_error;
2288 END IF;
2289
2290 l_progress := '030';
2291
2292 -- Irrespective of status(draft,revision,active the following fields can't be updated by the user)
2293 IF ( p_rule_header_rec.org_id <> okc_api.g_miss_num
2294 AND NVL (p_rule_header_rec.org_id, -100) <> l_rule_header_row.org_id
2295 )
2296 THEN
2297 l_progress := '035';
2298 -- You can not change Org_Id.
2299 okc_api.set_message
2300 (p_app_name => g_app_name,
2301 p_msg_name => 'OKC_I_RULE_STS_NO_UPD_FIELD',
2302 p_token1 => 'STATUS',
2303 p_token1_value => l_rule_header_row.status_code,
2304 p_token2 => 'FIELD',
2305 p_token2_value => 'ORG_ID'
2306 );
2307 RAISE fnd_api.g_exc_error;
2308 END IF;
2309
2310 l_progress := '040';
2311
2312 IF ( p_rule_header_rec.intent <> okc_api.g_miss_char
2313 AND NVL (p_rule_header_rec.intent, 'ABC') <>
2314 l_rule_header_row.intent
2315 )
2316 THEN
2317 l_progress := '045';
2318 -- You can not change Intent.
2319 okc_api.set_message
2320 (p_app_name => g_app_name,
2321 p_msg_name => 'OKC_I_RULE_STS_NO_UPD_FIELD',
2322 p_token1 => 'STATUS',
2323 p_token1_value => l_rule_header_row.status_code,
2324 p_token2 => 'FIELD',
2325 p_token2_value => 'INTENT'
2326 );
2327 RAISE fnd_api.g_exc_error;
2328 END IF;
2329
2330 l_progress := '050';
2331
2332 IF ( p_rule_header_rec.rule_type <> okc_api.g_miss_char
2333 AND NVL (p_rule_header_rec.rule_type, 'ABC') <>
2334 l_rule_header_row.rule_type
2335 )
2336 THEN
2337 l_progress := '055';
2338 -- You can not change Rule Type
2339 okc_api.set_message
2340 (p_app_name => g_app_name,
2341 p_msg_name => 'OKC_I_RULE_STS_NO_UPD_FIELD',
2342 p_token1 => 'STATUS',
2343 p_token1_value => l_rule_header_row.status_code,
2344 p_token2 => 'FIELD',
2345 p_token2_value => 'RULE_TYPE'
2346 );
2347 RAISE fnd_api.g_exc_error;
2348 END IF;
2349
2350 l_progress := '060';
2351
2352 IF ( p_rule_header_rec.status_code <> okc_api.g_miss_char
2353 AND NVL (p_rule_header_rec.status_code, 'ABC') <>
2354 l_rule_header_row.status_code
2355 )
2356 THEN
2357 l_progress := '065';
2358 -- You can not change Status
2359 okc_api.set_message (p_app_name => g_app_name,
2360 p_msg_name => 'OKC_I_RULE_STS_CHANGE'
2361 );
2362 RAISE fnd_api.g_exc_error;
2363 END IF;
2364
2365 l_progress := '070';
2366
2367 IF l_rule_header_row.status_code IN ('ACTIVE', 'REVISION')
2368 THEN
2369 /*
2370 The system allows updates to the conditions, results, and template assignments.
2371 However, you cannot update the following fields:
2372 Operating Unit => Covered in above code
2373 Rule Type => Covered in above code
2374 You cannot change the intent on a rule that has conditions, results or template assignments defined
2375 Name =>
2376 Intent => Covered in above code
2377 You cannot change the intent on a rule that has conditions, results or template assignments defined.
2378 Apply to All Templates =>
2379 */
2380 l_progress := '075';
2381
2382 IF ( p_rule_header_rec.rule_name <> okc_api.g_miss_char
2383 AND p_rule_header_rec.rule_name <> l_rule_header_row.rule_name
2384 )
2385 THEN
2386 l_progress := '080';
2387 -- You can not change rule_name
2388 okc_api.set_message
2389 (p_app_name => g_app_name,
2390 p_msg_name => 'OKC_I_RULE_STS_NO_UPD_FIELD',
2391 p_token1 => 'STATUS',
2392 p_token1_value => l_rule_header_row.status_code,
2393 p_token2 => 'FIELD',
2394 p_token2_value => 'RULE_NAME'
2395 );
2396 RAISE fnd_api.g_exc_error;
2397 END IF;
2398
2399 l_progress := '085';
2400
2401 IF ( p_rule_header_rec.org_wide_flag <> okc_api.g_miss_char
2402 AND NVL (p_rule_header_rec.org_wide_flag, 'X') <>
2403 l_rule_header_row.org_wide_flag
2404 )
2405 THEN
2406 l_progress := '090';
2407 -- You can not change org_wide_flag
2408 okc_api.set_message
2409 (p_app_name => g_app_name,
2410 p_msg_name => 'OKC_I_RULE_STS_NO_UPD_FIELD',
2411 p_token1 => 'STATUS',
2412 p_token1_value => l_rule_header_row.status_code,
2413 p_token2 => 'FIELD',
2414 p_token2_value => 'ORG_WIDE_FLAG'
2415 );
2416 RAISE fnd_api.g_exc_error;
2417 END IF;
2418 END IF;
2419
2420 l_progress := '100';
2421 -- set the policy context
2422 mo_global.set_policy_context ('S', TO_CHAR (l_rule_header_row.org_id));
2423 l_progress := '110';
2424 -- Default values for coulmns from table for which the values are not provided by user
2425 default_row (p_rule_header_rec => p_rule_header_rec,
2426 p_db_rule_header => l_rule_header_row
2427 );
2428 l_progress := '120';
2429 -- Validate the values provided by the user
2430 validate_row (p_rule_header_rec => p_rule_header_rec,
2431 p_db_rule_header => l_rule_header_row
2432 );
2433 -- Derive values ex; status change from active to revision
2434 l_progress := '130';
2435 derive_row (p_rule_header_rec => p_rule_header_rec);
2436
2437 IF NVL (l_rule_header_row.org_wide_flag, 'N') <>
2438 p_rule_header_rec.org_wide_flag
2439 AND p_rule_header_rec.org_wide_flag = 'Y'
2440 AND l_rule_header_row.status_code = 'DRAFT'
2441 THEN
2442 -- Delete the Template Assignments if any.
2443 -- Same as changing the org wide flag from UI when the staus is 'DRAFT'.
2444 l_progress := '140';
2445
2446 DELETE FROM okc_xprt_template_rules
2447 WHERE rule_id = p_rule_header_rec.rule_id;
2448 END IF;
2449
2450 l_progress := '150';
2451 -- Update the Rule header
2452 update_row (p_rule_header_rec => p_rule_header_rec);
2453 EXCEPTION
2454 WHEN OTHERS
2455 THEN
2456 set_proc_error_message (p_proc => l_proc);
2457 RAISE;
2458 END update_rule_header;
2459
2460 PROCEDURE update_rule_header (
2461 p_rule_header_tbl IN OUT NOCOPY rule_header_tbl_type
2462 )
2463 IS
2464 l_proc VARCHAR2 (60) := 'UPDATE_RULE_HEADER';
2465 l_failed_rec_num NUMBER := 0;
2466 BEGIN
2467 IF p_rule_header_tbl.COUNT > 0
2468 THEN
2469 FOR i IN p_rule_header_tbl.FIRST .. p_rule_header_tbl.LAST
2470 LOOP
2471 l_failed_rec_num := i;
2472 update_rule_header (p_rule_header_rec => p_rule_header_tbl (i));
2473 END LOOP;
2474 END IF;
2475 EXCEPTION
2476 WHEN OTHERS
2477 THEN
2478 set_proc_error_message (p_proc => l_proc);
2479 set_rec_num_message (p_rec_num => l_failed_rec_num);
2480 RAISE;
2481 END update_rule_header;
2482
2483 PROCEDURE create_rule (p_rule_rec IN OUT NOCOPY rule_rec_type)
2484 IS
2485 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
2486 l_error_message VARCHAR2 (2500);
2487 l_proc VARCHAR2 (60) := 'CREATE_RULE';
2488 BEGIN
2489 -- PRE VALIDATION START
2490 -- Rule Header Must Exist
2491 -- Atleast one condition must exist
2492 -- Atleast one outcome is required for 'CLAUSE_SELECTION' type rules.
2493 -- If not throw invalid input error.
2494 --
2495 fnd_msg_pub.initialize;
2496 l_rule_type := p_rule_rec.rule_header_rec.rule_type;
2497
2498 IF l_rule_type = g_clause_rule_type
2499 THEN
2500 IF NOT ( (p_rule_rec.rule_condition_tbl.COUNT > 0)
2501 AND (p_rule_rec.rule_outcome_tbl.COUNT > 0)
2502 )
2503 THEN
2504 p_rule_rec.status := g_ret_sts_error;
2505 okc_api.set_message (p_app_name => g_app_name,
2506 p_msg_name => 'OKC_XPRT_RULE_INCOMPLETE'
2507 );
2508 RAISE fnd_api.g_exc_error;
2509 END IF;
2510 ELSIF l_rule_type = g_term_dev_rule_type
2511 THEN
2512 IF NOT ((p_rule_rec.rule_condition_tbl.COUNT > 0))
2513 THEN
2514 p_rule_rec.status := g_ret_sts_error;
2515 okc_api.set_message
2516 (p_app_name => g_app_name,
2517 p_msg_name => 'OKC_XPRT_POLICYRULE_INCOMPLETE'
2518 );
2519 RAISE fnd_api.g_exc_error;
2520 END IF;
2521 ELSE
2522 p_rule_rec.status := g_ret_sts_error;
2523 okc_api.set_message (p_app_name => g_app_name,
2524 p_msg_name => l_okc_i_invalid_value,
2525 p_token1 => l_field,
2526 p_token1_value => 'RULE_TYPE'
2527 );
2528 RAISE fnd_api.g_exc_error;
2529 END IF;
2530
2531 -- PRE VALIDATION END
2532 BEGIN
2533 fnd_msg_pub.initialize;
2534 l_error_message := '';
2535 g_rule_id := NULL;
2536 g_rule_intent := NULL;
2537 g_rule_type := NULL;
2538 g_rule_status_code := NULL;
2539 g_org_wide_flag := NULL;
2540 g_org_id := NULL;
2541 create_rule_header (p_rule_header_rec => p_rule_rec.rule_header_rec);
2542 g_rule_id := p_rule_rec.rule_header_rec.rule_id;
2543 g_rule_intent := p_rule_rec.rule_header_rec.intent;
2544 g_rule_type := p_rule_rec.rule_header_rec.rule_type;
2545 g_rule_status_code := p_rule_rec.rule_header_rec.status_code;
2546 g_org_wide_flag := p_rule_rec.rule_header_rec.org_wide_flag;
2547 g_org_id := p_rule_rec.rule_header_rec.org_id;
2548 EXCEPTION
2549 WHEN OTHERS
2550 THEN
2551 p_rule_rec.status := g_ret_sts_error;
2552 RAISE;
2553 END;
2554
2555 BEGIN
2556 fnd_msg_pub.initialize;
2557 l_error_message := '';
2558 create_rule_condition
2559 (p_rule_condition_tbl => p_rule_rec.rule_condition_tbl);
2560 EXCEPTION
2561 WHEN OTHERS
2562 THEN
2563 p_rule_rec.status := g_ret_sts_error;
2564 RAISE;
2565 END;
2566
2567 IF l_rule_type = g_clause_rule_type
2568 THEN
2569 BEGIN
2570 fnd_msg_pub.initialize;
2571 l_error_message := '';
2572 create_rule_outcome
2573 (p_rule_outcome_tbl => p_rule_rec.rule_outcome_tbl);
2574 EXCEPTION
2575 WHEN OTHERS
2576 THEN
2577 p_rule_rec.status := g_ret_sts_error;
2578 RAISE;
2579 END;
2580
2581 fnd_msg_pub.initialize;
2582
2583 IF validaterulecondition (g_rule_id) = 'N'
2584 THEN
2585 p_rule_rec.status := g_ret_sts_error;
2586 RAISE fnd_api.g_exc_error;
2587 END IF;
2588 END IF;
2589
2590 IF NVL (p_rule_rec.rule_header_rec.org_wide_flag, 'X') <> 'Y'
2591 AND p_rule_rec.template_rules_tbl.COUNT > 0
2592 THEN
2593 BEGIN
2594 fnd_msg_pub.initialize;
2595 l_error_message := '';
2596 create_template_rules
2597 (p_template_rules_tbl => p_rule_rec.template_rules_tbl);
2598 EXCEPTION
2599 WHEN OTHERS
2600 THEN
2601 p_rule_rec.status := g_ret_sts_error;
2602 RAISE;
2603 END;
2604 END IF;
2605
2606 -- POST VALIDATION
2607 -- Need to check whether Rule Header, condition and out come exists
2608 p_rule_rec.status := g_ret_sts_success;
2609 EXCEPTION
2610 WHEN OTHERS
2611 THEN
2612 RAISE;
2613 END create_rule;
2614
2615 PROCEDURE update_rule (p_rule_rec IN OUT NOCOPY rule_rec_type)
2616 IS
2617 l_rule_type okc_xprt_rule_hdrs_all.rule_type%TYPE;
2618 l_error_message VARCHAR2 (2500);
2619 BEGIN
2620 BEGIN
2621 fnd_msg_pub.initialize;
2622 l_error_message := '';
2623 g_rule_id := NULL;
2624 g_rule_intent := NULL;
2625 g_rule_type := NULL;
2626 g_rule_status_code := NULL;
2627 g_org_wide_flag := NULL;
2628 g_org_id := NULL;
2629 update_rule_header (p_rule_header_rec => p_rule_rec.rule_header_rec);
2630 g_rule_id := p_rule_rec.rule_header_rec.rule_id;
2631 g_rule_intent := p_rule_rec.rule_header_rec.intent;
2632 g_rule_type := p_rule_rec.rule_header_rec.rule_type;
2633 g_rule_status_code := p_rule_rec.rule_header_rec.status_code;
2634 g_org_wide_flag := p_rule_rec.rule_header_rec.org_wide_flag;
2635 g_org_id := p_rule_rec.rule_header_rec.org_id;
2636 EXCEPTION
2637 WHEN OTHERS
2638 THEN
2639 p_rule_rec.status := g_ret_sts_error;
2640 RAISE;
2641 END;
2642
2643 IF p_rule_rec.rule_condition_tbl.COUNT > 0
2644 THEN
2645 fnd_msg_pub.initialize;
2646 l_error_message := '';
2647 create_rule_condition
2648 (p_rule_condition_tbl => p_rule_rec.rule_condition_tbl);
2649 END IF;
2650
2651 IF l_rule_type = g_clause_rule_type
2652 AND p_rule_rec.rule_outcome_tbl.COUNT > 0
2653 THEN
2654 fnd_msg_pub.initialize;
2655 l_error_message := '';
2656 create_rule_outcome
2657 (p_rule_outcome_tbl => p_rule_rec.rule_outcome_tbl);
2658 END IF;
2659
2660 IF p_rule_rec.rule_header_rec.org_wide_flag <> 'Y'
2661 AND p_rule_rec.template_rules_tbl.COUNT > 0
2662 THEN
2663 fnd_msg_pub.initialize;
2664 l_error_message := '';
2665 create_template_rules
2666 (p_template_rules_tbl => p_rule_rec.template_rules_tbl);
2667 END IF;
2668
2669 -- POST VALIDATION
2670 fnd_msg_pub.initialize;
2671
2672 IF validaterulecondition (g_rule_id) = 'N'
2673 THEN
2674 p_rule_rec.status := g_ret_sts_error;
2675 RAISE fnd_api.g_exc_error;
2676 END IF;
2677
2678 -- Need to check whether Rule Header, condition and out come exists
2679 p_rule_rec.status := g_ret_sts_success;
2680 EXCEPTION
2681 WHEN OTHERS
2682 THEN
2683 RAISE;
2684 END update_rule;
2685
2686 PROCEDURE delete_rule_child_entities (
2687 p_rule_child_entities_rec IN OUT NOCOPY rule_child_entities_rec_type
2688 )
2689 IS
2690 l_rule_type VARCHAR2 (60);
2691 l_rule_status VARCHAR2 (60);
2692 l_conditions_count NUMBER;
2693 l_outcomes_count NUMBER;
2694 l_flag VARCHAR2 (1);
2695 l_failed_rec_num NUMBER := 0;
2696 BEGIN
2697 fnd_msg_pub.initialize;
2698
2699 -- Get rule status, rule type
2700 BEGIN
2701 SELECT status_code, rule_type
2702 INTO g_rule_status_code, g_rule_type
2703 FROM okc_xprt_rule_hdrs_all
2704 WHERE rule_id = p_rule_child_entities_rec.rule_id;
2705 EXCEPTION
2706 WHEN OTHERS
2707 THEN
2708 p_rule_child_entities_rec.status := g_ret_sts_error;
2709 okc_api.set_message (p_app_name => g_app_name,
2710 p_msg_name => l_okc_i_invalid_value,
2711 p_token1 => l_field,
2712 p_token1_value => 'RULE_ID'
2713 );
2714 RAISE;
2715 END;
2716
2717 fnd_msg_pub.initialize;
2718
2719 -- Check whether the rules is available for update
2720 IF g_rule_status_code NOT IN ('DRAFT', 'REVISION', 'ACTIVE')
2721 THEN
2722 -- Can't update anything just return the error
2723 p_rule_child_entities_rec.status := g_ret_sts_error;
2724 okc_api.set_message (p_app_name => g_app_name,
2725 p_msg_name => 'OKC_I_RULE_STS_NO_UPD',
2726 p_token1 => 'STATUS',
2727 p_token1_value => g_rule_status_code
2728 );
2729 RAISE fnd_api.g_exc_error;
2730 END IF;
2731
2732 -- Check validity of input conditions
2733 IF p_rule_child_entities_rec.rule_condition_id_tbl.COUNT > 0
2734 THEN
2735 FOR i IN
2736 p_rule_child_entities_rec.rule_condition_id_tbl.FIRST .. p_rule_child_entities_rec.rule_condition_id_tbl.LAST
2737 LOOP
2738 l_failed_rec_num := i;
2739
2740 BEGIN
2741 SELECT 'Y'
2742 INTO l_flag
2743 FROM okc_xprt_rule_conditions
2744 WHERE rule_id = p_rule_child_entities_rec.rule_id
2745 AND rule_condition_id =
2746 p_rule_child_entities_rec.rule_condition_id_tbl (i)
2747 AND ROWNUM = 1;
2748 EXCEPTION
2749 WHEN OTHERS
2750 THEN
2751 p_rule_child_entities_rec.status := g_ret_sts_error;
2752 okc_api.set_message (p_app_name => g_app_name,
2753 p_msg_name => l_okc_i_invalid_value,
2754 p_token1 => l_field,
2755 p_token1_value => 'RULE_CONDITION_ID'
2756 );
2757 set_rec_num_message (p_rec_num => l_failed_rec_num);
2758 RAISE;
2759 END;
2760 END LOOP;
2761
2762 /*As of now Commenting out the validation */
2763
2764 -- Delete
2765 FORALL i IN INDICES OF p_rule_child_entities_rec.rule_condition_id_tbl
2766 DELETE FROM okc_xprt_rule_cond_vals
2767 WHERE rule_condition_id =
2768 p_rule_child_entities_rec.rule_condition_id_tbl (i);
2769
2770 FORALL i IN INDICES OF p_rule_child_entities_rec.rule_condition_id_tbl
2771 DELETE FROM okc_xprt_rule_conditions
2772 WHERE rule_condition_id =
2773 p_rule_child_entities_rec.rule_condition_id_tbl (i);
2774
2775 -- A valid rule requires atleasrt one condition.
2776 -- Atleast one condition must exist other wise return error
2777 SELECT COUNT (1)
2778 INTO l_conditions_count
2779 FROM okc_xprt_rule_conditions
2780 WHERE rule_id = p_rule_child_entities_rec.rule_id;
2781
2782 IF l_conditions_count = 0
2783 THEN
2784 p_rule_child_entities_rec.status := g_ret_sts_error;
2785 okc_api.set_message (p_app_name => g_app_name,
2786 p_msg_name => 'OKC_XPRT_RULE_INCOMPLETE'
2787 );
2788 RAISE fnd_api.g_exc_error;
2789 END IF;
2790 END IF;
2791
2792 -- Check validity of input Outcomes and delete
2793 IF g_rule_type = g_clause_rule_type
2794 AND p_rule_child_entities_rec.rule_outcome_id_tbl.COUNT > 1
2795 THEN
2796 FOR i IN
2797 p_rule_child_entities_rec.rule_outcome_id_tbl.FIRST .. p_rule_child_entities_rec.rule_outcome_id_tbl.LAST
2798 LOOP
2799 l_failed_rec_num := i;
2800
2801 BEGIN
2802 SELECT 'Y'
2803 INTO l_flag
2804 FROM okc_xprt_rule_outcomes
2805 WHERE rule_id = p_rule_child_entities_rec.rule_id
2806 AND rule_outcome_id =
2807 p_rule_child_entities_rec.rule_outcome_id_tbl (i)
2808 AND ROWNUM = 1;
2809 EXCEPTION
2810 WHEN OTHERS
2811 THEN
2812 p_rule_child_entities_rec.status := g_ret_sts_error;
2813 okc_api.set_message (p_app_name => g_app_name,
2814 p_msg_name => l_okc_i_invalid_value,
2815 p_token1 => l_field,
2816 p_token1_value => 'RULE_OUTCOME_ID'
2817 );
2818 set_rec_num_message (p_rec_num => l_failed_rec_num);
2819 RAISE;
2820 END;
2821 END LOOP;
2822
2823 -- Delete
2824 FORALL i IN INDICES OF p_rule_child_entities_rec.rule_outcome_id_tbl
2825 DELETE FROM okc_xprt_rule_outcomes
2826 WHERE rule_outcome_id =
2827 p_rule_child_entities_rec.rule_outcome_id_tbl (i);
2828
2829 -- Atleast one outcome must exist for clause_selection rules
2830 SELECT COUNT (1)
2831 INTO l_outcomes_count
2832 FROM okc_xprt_rule_outcomes
2833 WHERE rule_id = p_rule_child_entities_rec.rule_id;
2834
2835 IF l_outcomes_count = 0
2836 THEN
2837 p_rule_child_entities_rec.status := g_ret_sts_error;
2838 okc_api.set_message (p_app_name => g_app_name,
2839 p_msg_name => 'OKC_XPRT_RULE_INCOMPLETE'
2840 );
2841 RAISE fnd_api.g_exc_error;
2842 END IF;
2843 END IF;
2844
2845 -- Check validity of template rules
2846 IF p_rule_child_entities_rec.template_rule_id_tbl.COUNT > 0
2847 THEN
2848 FOR i IN
2849 p_rule_child_entities_rec.template_rule_id_tbl.FIRST .. p_rule_child_entities_rec.template_rule_id_tbl.LAST
2850 LOOP
2851 l_failed_rec_num := i;
2852
2853 BEGIN
2854 SELECT 'Y'
2855 INTO l_flag
2856 FROM okc_xprt_template_rules
2857 WHERE rule_id = p_rule_child_entities_rec.rule_id
2858 AND template_rule_id =
2859 p_rule_child_entities_rec.template_rule_id_tbl (i)
2860 AND ROWNUM = 1;
2861 EXCEPTION
2862 WHEN OTHERS
2863 THEN
2864 p_rule_child_entities_rec.status := g_ret_sts_error;
2865 okc_api.set_message (p_app_name => g_app_name,
2866 p_msg_name => l_okc_i_invalid_value,
2867 p_token1 => l_field,
2868 p_token1_value => 'TEMPLATE_RULE_ID'
2869 );
2870 set_rec_num_message (p_rec_num => l_failed_rec_num);
2871 RAISE;
2872 END;
2873 END LOOP;
2874
2875 --As of now Commenting out the validation */
2876
2877 -- Delete
2878 FORALL i IN INDICES OF p_rule_child_entities_rec.template_rule_id_tbl
2879 DELETE FROM okc_xprt_template_rules
2880 WHERE 1 = 1
2881 AND template_rule_id =
2882 p_rule_child_entities_rec.template_rule_id_tbl (i);
2883 END IF;
2884
2885 -- Change the rule status to 'REVISION' from active
2886 IF g_rule_status_code = 'ACTIVE'
2887 THEN
2888 UPDATE okc_xprt_rule_hdrs_all
2889 SET status_code = 'REVISION'
2890 WHERE rule_id = p_rule_child_entities_rec.rule_id;
2891 END IF;
2892
2893 p_rule_child_entities_rec.status := g_ret_sts_success;
2894 EXCEPTION
2895 WHEN OTHERS
2896 THEN
2897 RAISE;
2898 END delete_rule_child_entities;
2899
2900 -------------------------------
2901 -- PUBLIC PROCEDURES --
2902 -------------------------------
2903 PROCEDURE create_rule (
2904 p_rule_tbl IN OUT NOCOPY rule_tbl_type,
2905 p_commit IN VARCHAR2 := fnd_api.g_false
2906 )
2907 IS
2908 l_success_count NUMBER := 0;
2909 l_error_count NUMBER := 0;
2910 l_input_count NUMBER := p_rule_tbl.COUNT;
2911 l_error_message VARCHAR2 (2500);
2912 l_proc VARCHAR2 (60) := 'CREATE_RULE';
2913 l_failed_rec_num NUMBER := 0;
2914 BEGIN
2915 IF p_rule_tbl.COUNT > 0
2916 THEN
2917 FOR i IN p_rule_tbl.FIRST .. p_rule_tbl.LAST
2918 LOOP
2919 l_failed_rec_num := i;
2920
2921 BEGIN
2922 SAVEPOINT create_rule_sp;
2923 create_rule (p_rule_rec => p_rule_tbl (i));
2924
2925 IF p_rule_tbl (i).status = g_ret_sts_success
2926 THEN
2927 l_success_count := l_success_count + 1;
2928
2929 IF fnd_api.to_boolean (p_commit)
2930 THEN
2931 COMMIT;
2932 END IF;
2933 ELSE
2934 l_error_count := l_error_count + 1;
2935 ROLLBACK TO create_rule_sp;
2936 END IF;
2937 EXCEPTION
2938 WHEN fnd_api.g_exc_error
2939 THEN
2940 p_rule_tbl (i).status := g_ret_sts_error;
2941 set_proc_error_message (p_proc => l_proc);
2942 set_rec_num_message (p_rec_num => l_failed_rec_num);
2943 read_message (l_error_message);
2944 p_rule_tbl (i).errmsg := l_error_message;
2945 ROLLBACK TO create_rule_sp;
2946 fnd_msg_pub.initialize;
2947 WHEN OTHERS
2948 THEN
2949 p_rule_tbl (i).status := g_ret_sts_error;
2950 set_proc_error_message (p_proc => l_proc);
2951 set_rec_num_message (p_rec_num => l_failed_rec_num);
2952 okc_api.set_message (p_app_name => g_app_name,
2953 p_msg_name => g_unexpected_error,
2954 p_token1 => g_sqlcode_token,
2955 p_token1_value => SQLCODE,
2956 p_token2 => g_sqlerrm_token,
2957 p_token2_value => SQLERRM
2958 );
2959 read_message (l_error_message);
2960 p_rule_tbl (i).errmsg := l_error_message;
2961 ROLLBACK TO create_rule_sp;
2962 fnd_msg_pub.initialize;
2963 END;
2964 END LOOP;
2965 END IF;
2966 EXCEPTION
2967 WHEN OTHERS
2968 THEN
2969 ROLLBACK TO create_rule_sp;
2970 RAISE;
2971 END create_rule;
2972
2973 PROCEDURE update_rule (
2974 p_rule_tbl IN OUT NOCOPY rule_tbl_type,
2975 p_commit IN VARCHAR2 := fnd_api.g_false
2976 )
2977 IS
2978 l_success_count NUMBER := 0;
2979 l_error_count NUMBER := 0;
2980 l_input_count NUMBER := p_rule_tbl.COUNT;
2981 l_error_message VARCHAR2 (2500);
2982 l_proc VARCHAR2 (60) := 'UPDATE_RULE';
2983 l_failed_rec_num NUMBER := 0;
2984 BEGIN
2985 IF p_rule_tbl.COUNT > 0
2986 THEN
2987 FOR i IN p_rule_tbl.FIRST .. p_rule_tbl.LAST
2988 LOOP
2989 BEGIN
2990 l_failed_rec_num := i;
2991 SAVEPOINT update_rule_sp;
2992 update_rule (p_rule_rec => p_rule_tbl (i));
2993
2994 IF p_rule_tbl (i).status = g_ret_sts_success
2995 THEN
2996 l_success_count := l_success_count + 1;
2997
2998 IF fnd_api.to_boolean (p_commit)
2999 THEN
3000 COMMIT;
3001 END IF;
3002 ELSE
3003 l_error_count := l_error_count + 1;
3004 ROLLBACK TO update_rule_sp;
3005 END IF;
3006 EXCEPTION
3007 WHEN fnd_api.g_exc_error
3008 THEN
3009 p_rule_tbl (i).status := g_ret_sts_error;
3010 set_proc_error_message (p_proc => l_proc);
3011 set_rec_num_message (p_rec_num => l_failed_rec_num);
3012 read_message (l_error_message);
3013 p_rule_tbl (i).errmsg := l_error_message;
3014 ROLLBACK TO update_rule_sp;
3015 fnd_msg_pub.initialize;
3016 WHEN OTHERS
3017 THEN
3018 p_rule_tbl (i).status := g_ret_sts_error;
3019 set_proc_error_message (p_proc => l_proc);
3020 set_rec_num_message (p_rec_num => l_failed_rec_num);
3021 okc_api.set_message (p_app_name => g_app_name,
3022 p_msg_name => g_unexpected_error,
3023 p_token1 => g_sqlcode_token,
3024 p_token1_value => SQLCODE,
3025 p_token2 => g_sqlerrm_token,
3026 p_token2_value => SQLERRM
3027 );
3028 read_message (l_error_message);
3029 p_rule_tbl (i).errmsg := l_error_message;
3030 ROLLBACK TO update_rule_sp;
3031 fnd_msg_pub.initialize;
3032 END;
3033 END LOOP;
3034 END IF;
3035 EXCEPTION
3036 WHEN OTHERS
3037 THEN
3038 ROLLBACK TO update_rule_sp;
3039 RAISE;
3040 END update_rule;
3041
3042 PROCEDURE delete_rule_child_entities (
3043 p_rule_child_entities_tbl IN OUT NOCOPY rule_child_entities_tbl_type,
3044 p_commit IN VARCHAR2 := fnd_api.g_false
3045 )
3046 IS
3047 l_success_count NUMBER := 0;
3048 l_error_count NUMBER := 0;
3049 l_input_count NUMBER := p_rule_child_entities_tbl.COUNT;
3050 l_error_message VARCHAR2 (2500);
3051 l_proc VARCHAR2 (60) := 'DELETE_RULE_CHILD_ENTITIES';
3052 l_failed_rec_num NUMBER := 0;
3053 BEGIN
3054 IF p_rule_child_entities_tbl.COUNT > 0
3055 THEN
3056 FOR i IN
3057 p_rule_child_entities_tbl.FIRST .. p_rule_child_entities_tbl.LAST
3058 LOOP
3059 BEGIN
3060 SAVEPOINT del_rule_child_entity_sp;
3061 l_failed_rec_num := i;
3062 delete_rule_child_entities
3063 (p_rule_child_entities_rec => p_rule_child_entities_tbl
3064 (i)
3065 );
3066
3067 IF p_rule_child_entities_tbl (i).status = g_ret_sts_success
3068 THEN
3069 l_success_count := l_success_count + 1;
3070
3071 IF fnd_api.to_boolean (p_commit)
3072 THEN
3073 COMMIT;
3074 END IF;
3075 ELSE
3076 l_error_count := l_error_count + 1;
3077 ROLLBACK TO del_rule_child_entity_sp;
3078 END IF;
3079 EXCEPTION
3080 WHEN OTHERS
3081 THEN
3082 p_rule_child_entities_tbl (i).status := g_ret_sts_error;
3083 set_proc_error_message (p_proc => l_proc);
3084 set_rec_num_message (p_rec_num => l_failed_rec_num);
3085
3086 read_message (l_error_message);
3087 p_rule_child_entities_tbl (i).errmsg := l_error_message;
3088 ROLLBACK TO del_rule_child_entity_sp;
3089 fnd_msg_pub.initialize;
3090 END;
3091 END LOOP;
3092 END IF;
3093 EXCEPTION
3094 WHEN OTHERS
3095 THEN
3096 ROLLBACK TO del_rule_child_entity_sp;
3097 RAISE;
3098 END delete_rule_child_entities;
3099 END okc_xprt_rule_pvt;