[Home] [Help]
40: p_template_id := template_id;
41:
42: --populating all temp tables and evaluating conditions
43: populate_rule_cond_eval_table(); --populating okc_xprt_rule_eval_result_t table with all rules and conditions
44: populate_doc_var_values(); --populating okc_xprt_rule_eval_condval_t with variables values
45: populate_doc_questions(); --populating okc_xprt_rule_eval_condval_t with questions for the VO to render in Questions UI page
46: populate_rule_cond_dep(); --populating rule, cond clause lvel dependency into a gt table
47: evaluate_rules_and_conditions(); --evaluating rules and conditions. evaluate variable based conditions and question based condition if it has a response
48:
41:
42: --populating all temp tables and evaluating conditions
43: populate_rule_cond_eval_table(); --populating okc_xprt_rule_eval_result_t table with all rules and conditions
44: populate_doc_var_values(); --populating okc_xprt_rule_eval_condval_t with variables values
45: populate_doc_questions(); --populating okc_xprt_rule_eval_condval_t with questions for the VO to render in Questions UI page
46: populate_rule_cond_dep(); --populating rule, cond clause lvel dependency into a gt table
47: evaluate_rules_and_conditions(); --evaluating rules and conditions. evaluate variable based conditions and question based condition if it has a response
48:
49: x_has_questions := 'N';
49: x_has_questions := 'N';
50:
51: BEGIN
52: SELECT 'Y' INTO x_has_questions FROM dual
53: WHERE EXISTS (SELECT 1 FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
54: AND object_type = 'QUESTION' AND display_flag = 'Y');
55: EXCEPTION
56: WHEN NO_DATA_FOUND THEN
57: NULL;
187: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
188: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
189: END IF;
190:
191: DELETE okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id and doc_type = p_doc_type;
192:
193: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
194: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Deleted entries in okc_xprt_rule_eval_condval_t for this document before populating the variable values');
195: END IF;
190:
191: DELETE okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id and doc_type = p_doc_type;
192:
193: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
194: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Deleted entries in okc_xprt_rule_eval_condval_t for this document before populating the variable values');
195: END IF;
196:
197: okc_xprt_xrule_values_pvt.get_document_values(p_api_version => 1,
198: p_init_msg_list => FND_API.G_FALSE,
237: variable_values_tbl(j).variable_value_id := x_hdr_var_value_tbl(j).variable_value_id;
238: END LOOP;
239:
240: FORALL j IN variable_values_tbl.first..variable_values_tbl.last
241: INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response)
242: VALUES (OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'VARIABLE', variable_values_tbl(j).variable_code ,variable_values_tbl(j).variable_value_id);
243: END IF;
244:
245: IF x_line_sysvar_value_tbl.Count > 0 THEN
243: END IF;
244:
245: IF x_line_sysvar_value_tbl.Count > 0 THEN
246: FORALL j IN x_line_sysvar_value_tbl.first..x_line_sysvar_value_tbl.LAST
247: INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response)
248: VALUES (OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'VARIABLE',x_line_sysvar_value_tbl(j).variable_code,x_line_sysvar_value_tbl(j).variable_value);
249: END IF;
250:
251: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248: VALUES (OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'VARIABLE',x_line_sysvar_value_tbl(j).variable_code,x_line_sysvar_value_tbl(j).variable_value);
249: END IF;
250:
251: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
252: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: Populated entries i.e variable values in okc_xprt_rule_eval_condval_t for this document');
253: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: No. of global variables populated: ' || x_hdr_var_value_tbl.count);
254: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: No. of line variables populated: ' || x_line_sysvar_value_tbl.count);
255: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: Leaving method');
256: END IF;
376: IF is_contract_expert_run = 'Y' THEN
377: OPEN c_question_and_response;
378: FETCH c_question_and_response BULK COLLECT INTO question_ids, responses, display_flags;
379: FORALL i IN 1 .. question_ids.count
380: INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response, display_flag)
381: VALUES(OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'QUESTION', question_ids(i), responses(i), display_flags(i));
382: CLOSE c_question_and_response;
383: ELSE
384: OPEN c_question;
383: ELSE
384: OPEN c_question;
385: FETCH c_question BULK COLLECT INTO question_ids, display_flags;
386: FORALL i IN 1 .. question_ids.count
387: INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response, display_flag)
388: VALUES(OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'QUESTION', question_ids(i), null, display_flags(i));
389: CLOSE c_question;
390: END IF;
391:
389: CLOSE c_question;
390: END IF;
391:
392: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
393: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Populated entries i.e questions and responses(if exists) in okc_xprt_rule_eval_condval_t for this document');
394: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: No. of questions populated: ' || question_ids.count);
395: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Leaving method');
396: END IF;
397:
413: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
415: END IF;
416:
417: INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response, display_flag, dep_clause_cond_id)
418: SELECT OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'RULE', rule_id, null, null, dep_clause_cond_id FROM okc_xprt_rule_dependencies_v;
419:
420: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Rule_clause dependency rows populated into okc_xprt_rule_eval_condval_t table from okc_xprt_rule_dependencies_v view');
417: INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response, display_flag, dep_clause_cond_id)
418: SELECT OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'RULE', rule_id, null, null, dep_clause_cond_id FROM okc_xprt_rule_dependencies_v;
419:
420: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Rule_clause dependency rows populated into okc_xprt_rule_eval_condval_t table from okc_xprt_rule_dependencies_v view');
422: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Leaving method');
423: END IF;
424:
425: EXCEPTION
441: AND condition_id IS NULL;
442:
443: CURSOR c_rule_conditions(c_rule_id NUMBER) IS
444: SELECT cond.condition_id, cond.condition_type, cond.rule_expr_type, cond.object_code, cond.object_value_type, cond.object_value_code, cond.cond_operator,
445: DECODE(cond.condition_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
446: AND object_type = 'QUESTION' AND object_code = cond.object_code),
447: NULL) lhs_response,
448: DECODE(cond.object_value_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
449: AND object_type = 'QUESTION' AND object_code = cond.object_value_code),
444: SELECT cond.condition_id, cond.condition_type, cond.rule_expr_type, cond.object_code, cond.object_value_type, cond.object_value_code, cond.cond_operator,
445: DECODE(cond.condition_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
446: AND object_type = 'QUESTION' AND object_code = cond.object_code),
447: NULL) lhs_response,
448: DECODE(cond.object_value_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
449: AND object_type = 'QUESTION' AND object_code = cond.object_value_code),
450: NULL) rhs_response
451: FROM okc_xprt_rule_eval_result_t cond
452: WHERE cond.doc_id = p_doc_id
638: --Step I: Additional Questions handling:
639: --Updating the display_flag to 'Y' if the rule_id has any additional questions
640: --For the first run, update of display_flag to 'N' for failure_rule_ids is not needed, as all additional question are marked as 'N' by default
641: IF success_rule_ids.count > 0 THEN
642: UPDATE okc_xprt_rule_eval_condval_t
643: SET display_flag = 'Y'
644: WHERE doc_id = p_doc_id
645: AND doc_type = p_doc_type
646: AND object_type = 'QUESTION'
660: WHERE doc_id = p_doc_id
661: AND doc_type = p_doc_type
662: AND condition_type = 'CLAUSE'
663: AND ((cond_operator IN ('IS', 'IN') and nvl(result, '*') <> 'Y') OR (cond_operator IN ('IS_NOT', 'NOT_IN') and nvl(result, '*') = 'Y')) --to avoid non-updatable statements
664: AND condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t WHERE doc_id = cond.doc_id and doc_type = cond.doc_type
665: and object_type = 'RULE' and object_code IN (SELECT * FROM table(success_rule_ids)))
666: RETURNING rule_id BULK COLLECT INTO clause_rules1;
667:
668: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
680: AND cond.doc_type = p_doc_type
681: AND condition_type = 'CLAUSE'
682: AND cond.cond_operator IN ('IS', 'IN') --as the condition result wont change in other cases
683: AND cond.result IS NULL --if the result is Y, then it wont change and the result will not be N in any case
684: AND cond.condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t WHERE doc_id = cond.doc_id and doc_type = cond.doc_type
685: and object_type = 'RULE' and object_code IN (SELECT * FROM table(failure_rule_ids)))
686: AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
687: AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id
688: AND t.doc_id = d.doc_id AND t.doc_type = d.doc_type AND t.condition_id IS NULL
682: AND cond.cond_operator IN ('IS', 'IN') --as the condition result wont change in other cases
683: AND cond.result IS NULL --if the result is Y, then it wont change and the result will not be N in any case
684: AND cond.condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t WHERE doc_id = cond.doc_id and doc_type = cond.doc_type
685: and object_type = 'RULE' and object_code IN (SELECT * FROM table(failure_rule_ids)))
686: AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
687: AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id
688: AND t.doc_id = d.doc_id AND t.doc_type = d.doc_type AND t.condition_id IS NULL
689: AND t.result IS NULL AND d.object_code = t.rule_id)
690: RETURNING cond.rule_id BULK COLLECT INTO clause_rules2;
836: FUNCTION evaluate_condition(p_cond_id NUMBER, p_cond_type VARCHAR2, p_object_code VARCHAR2, p_object_value_type VARCHAR2, p_object_value_code VARCHAR2, p_operator VARCHAR2) RETURN BOOLEAN IS
837:
838: CURSOR c_lhsvalues IS
839: SELECT value_or_response
840: FROM okc_xprt_rule_eval_condval_t
841: WHERE doc_id = p_doc_id
842: AND doc_type = p_doc_type
843: AND object_code = p_object_code
844: AND object_type = p_cond_type;
845:
846: -- FOR object_value_type of 'QUESTION', 'CONSTANT', 'VARIABLE'
847: CURSOR c_rhsvalues IS
848: SELECT value_or_response
849: FROM okc_xprt_rule_eval_condval_t
850: WHERE doc_id = p_doc_id
851: AND doc_type = p_doc_type
852: AND object_code = p_object_value_code
853: AND object_type = p_object_value_type;
1107: --A) rule_ids1--- which have changed their result from null to Y or N to Y
1108: --updated additional questions display flag to Y if not updated already
1109: --reevaluation of rule_ids1 is not needed..
1110: IF rule_ids1.count > 0 THEN
1111: UPDATE okc_xprt_rule_eval_condval_t
1112: SET display_flag = 'Y'
1113: WHERE doc_id = p_doc_id
1114: AND doc_type = p_doc_type
1115: AND object_type = 'QUESTION'
1128: --reevaluation of rule_ids2 is needed.. i.e the reason we collect the rule_ids to ques_rules for re-evalaution.
1129: IF rule_ids2.count > 0 THEN
1130:
1131: FORALL i IN 1 .. rule_ids2.count
1132: UPDATE okc_xprt_rule_eval_condval_t ques
1133: SET ques.display_flag = 'N'
1134: WHERE ques.doc_id = p_doc_id
1135: AND ques.doc_type = p_doc_type
1136: AND ques.object_type = 'QUESTION'
1150: --updated additional questions response to null, if and only if all the the rules with outcome as this question doesnt have their as Y..
1151: --if response is made to null from any value then only we need to re-evaluate the rule status that is the reason seperation of updating the
1152: -- response and display_flag is needed.
1153: FORALL i IN 1 .. rule_ids2.count
1154: UPDATE okc_xprt_rule_eval_condval_t ques
1155: SET ques.value_or_response = NULL
1156: WHERE ques.doc_id = p_doc_id
1157: AND ques.doc_type = p_doc_type
1158: AND ques.object_type = 'QUESTION'
1220: WHERE doc_id = p_doc_id
1221: AND doc_type = p_doc_type
1222: AND condition_type = 'CLAUSE'
1223: AND ((cond_operator IN ('IS', 'IN') and nvl(result, '*') <> 'Y') OR (cond_operator IN ('IS_NOT', 'NOT_IN') and nvl(result, '*') = 'Y')) --to avoid non-updatable statements
1224: AND condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id and d.doc_type = cond.doc_type
1225: and d.object_type = 'RULE' and d.object_code IN (SELECT * FROM table(rule_ids1)))
1226: RETURNING rule_id BULK COLLECT INTO clause_rules1;
1227:
1228: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1237: --updated dependent clause conditions result by considering all the other rules result
1238:
1239: IF rule_ids2.count > 0 THEN
1240: UPDATE okc_xprt_rule_eval_result_t cond
1241: SET cond.result = decode(cond.cond_operator, 'IS_NOT', 'Y', 'NOT_IN', 'Y', decode((SELECT nvl(t.result, 1) FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d
1242: WHERE d.doc_id = cond.doc_id and d.doc_type = cond.doc_type AND d.object_type = 'RULE'
1243: AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id AND t.doc_id = d.doc_id
1244: AND t.doc_type = d.doc_type AND t.condition_id IS NULL AND t.result IS NULL AND ROWNUM = 1),
1245: 1, NULL, 'N'))
1245: 1, NULL, 'N'))
1246: WHERE cond.doc_id = p_doc_id
1247: AND cond.doc_type = p_doc_type
1248: AND condition_type = 'CLAUSE'
1249: AND cond.condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
1250: AND d.object_type = 'RULE' AND d.object_code IN (SELECT * FROM table(rule_ids2)))
1251: AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
1252: AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id
1253: AND t.doc_id = d.doc_id AND t.doc_type = d.doc_type AND t.condition_id IS NULL AND nvl(t.result, '*') = 'Y')
1247: AND cond.doc_type = p_doc_type
1248: AND condition_type = 'CLAUSE'
1249: AND cond.condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
1250: AND d.object_type = 'RULE' AND d.object_code IN (SELECT * FROM table(rule_ids2)))
1251: AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
1252: AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id
1253: AND t.doc_id = d.doc_id AND t.doc_type = d.doc_type AND t.condition_id IS NULL AND nvl(t.result, '*') = 'Y')
1254: --it their exists a sucess condition, then the result of the rule reamins same i.e T in case of IN,IS operator and F in case of IS_NOT, NOT_IN operator
1255: RETURNING cond.rule_id BULK COLLECT INTO clause_rules2;
1268: --for operator 'IS' or 'IN', the result wont change if it is already 'y'
1269:
1270: IF rule_ids3.count > 0 THEN
1271: UPDATE okc_xprt_rule_eval_result_t cond
1272: SET cond.result = decode((SELECT nvl(t.result, 1) FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d
1273: WHERE d.doc_id = cond.doc_id and d.doc_type = cond.doc_type AND d.object_type = 'RULE'
1274: AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id AND t.doc_id = d.doc_id
1275: AND t.doc_type = d.doc_type AND t.condition_id IS NULL AND t.result IS NULL AND ROWNUM = 1),
1276: 1, NULL, 'N')
1278: AND cond.doc_type = p_doc_type
1279: AND condition_type = 'CLAUSE'
1280: AND cond.cond_operator IN ('IS', 'IN') --as the condition result wont change in other cases
1281: AND nvl(cond.result, '*') <> 'Y' --if the result is already Y, then it wont change
1282: AND cond.condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id and d.doc_type = cond.doc_type
1283: AND d.object_type = 'RULE' and d.object_code IN (SELECT * FROM table(rule_ids3)))
1284: AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
1285: AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id AND t.doc_id = d.doc_id
1286: AND t.doc_type = d.doc_type AND d.object_code = t.rule_id AND t.condition_id IS NULL AND t.result IS NULL AND cond.result IS NULL)
1280: AND cond.cond_operator IN ('IS', 'IN') --as the condition result wont change in other cases
1281: AND nvl(cond.result, '*') <> 'Y' --if the result is already Y, then it wont change
1282: AND cond.condition_id IN (SELECT distinct dep_clause_cond_id FROM okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id and d.doc_type = cond.doc_type
1283: AND d.object_type = 'RULE' and d.object_code IN (SELECT * FROM table(rule_ids3)))
1284: AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t t, okc_xprt_rule_eval_condval_t d WHERE d.doc_id = cond.doc_id AND d.doc_type = cond.doc_type
1285: AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id AND t.doc_id = d.doc_id
1286: AND t.doc_type = d.doc_type AND d.object_code = t.rule_id AND t.condition_id IS NULL AND t.result IS NULL AND cond.result IS NULL)
1287: RETURNING cond.rule_id BULK COLLECT INTO clause_rules3;
1288:
1336: FUNCTION evaluate_user_response(doc_id NUMBER, doc_type VARCHAR2, template_id NUMBER, p_question_id VARCHAR2, p_response VARCHAR2) RETURN VARCHAR2 IS
1337:
1338: CURSOR c_rule_conditions IS
1339: SELECT rule_id, condition_id, condition_type, rule_expr_type, object_code, cond_operator, result, object_value_type, object_value_code,
1340: (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1341: AND object_type = 'QUESTION' AND object_code = cond.object_code) lhs_response,
1342: DECODE(cond.object_value_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1343: AND object_type = 'QUESTION' AND object_code = cond.object_value_code),
1344: NULL) rhs_response
1338: CURSOR c_rule_conditions IS
1339: SELECT rule_id, condition_id, condition_type, rule_expr_type, object_code, cond_operator, result, object_value_type, object_value_code,
1340: (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1341: AND object_type = 'QUESTION' AND object_code = cond.object_code) lhs_response,
1342: DECODE(cond.object_value_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1343: AND object_type = 'QUESTION' AND object_code = cond.object_value_code),
1344: NULL) rhs_response
1345: FROM okc_xprt_rule_eval_result_t cond
1346: WHERE doc_id = p_doc_id
1350:
1351: UNION
1352:
1353: SELECT rule_id, condition_id, condition_type, rule_expr_type, object_code, cond_operator, result, object_value_type, object_value_code,
1354: DECODE(cond.condition_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1355: AND object_type = 'QUESTION' AND object_code = cond.object_code),
1356: NULL) lhs_response,
1357: (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1358: AND object_type = 'QUESTION' AND object_code = cond.object_value_code) rhs_response
1353: SELECT rule_id, condition_id, condition_type, rule_expr_type, object_code, cond_operator, result, object_value_type, object_value_code,
1354: DECODE(cond.condition_type, 'QUESTION', (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1355: AND object_type = 'QUESTION' AND object_code = cond.object_code),
1356: NULL) lhs_response,
1357: (SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1358: AND object_type = 'QUESTION' AND object_code = cond.object_value_code) rhs_response
1359: FROM okc_xprt_rule_eval_result_t cond
1360: WHERE doc_id = p_doc_id
1361: AND doc_type = p_doc_type
1384: p_doc_id := doc_id;
1385: p_doc_type := doc_type;
1386: p_template_id := template_id;
1387:
1388: UPDATE okc_xprt_rule_eval_condval_t
1389: SET value_or_response = p_response
1390: WHERE object_code = p_question_id
1391: AND object_type = 'QUESTION'
1392: AND doc_id = p_doc_id
1579: DELETE okc_xprt_doc_ques_response WHERE doc_id = p_doc_id AND doc_type = p_doc_type;
1580:
1581: --copying the responses to okc_xprt_doc_ques_response
1582: INSERT INTO okc_xprt_doc_ques_response (doc_question_response_id, doc_id, doc_type, question_id, response)
1583: (SELECT OKC_XPRT_DOC_QUES_RESPONSE_S.nextval, p_doc_id, p_doc_type, object_code, value_or_response FROM okc_xprt_rule_eval_condval_t
1584: WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND object_type = 'QUESTION' AND display_flag = 'Y');
1585:
1586: --updating contract_expert_finish_flag to N to say that the process of contract expert is not finished
1587: UPDATE okc_template_usages
1622: p_doc_type := doc_type;
1623:
1624: BEGIN
1625: SELECT 'N' INTO has_all_ques_answered FROM dual
1626: WHERE EXISTS (SELECT 1 FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
1627: AND object_type = 'QUESTION' AND display_flag = 'Y' AND value_or_response IS NULL);
1628: EXCEPTION
1629: WHEN NO_DATA_FOUND THEN
1630: NULL;