eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKC_XPRT_RULES_ENGINE_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 52


		SELECT 'Y' INTO x_has_questions FROM dual
		WHERE EXISTS (SELECT 1 FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
				    AND object_type = 'QUESTION' AND display_flag = 'Y');

Line: 77


SELECT rule.rule_id, rule.condition_expr_code
FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_hdrs_all_v rule
WHERE rultmpl.template_id = p_template_id
AND rule.rule_id = rultmpl.rule_id
UNION ALL
SELECT rule.rule_id, rule.condition_expr_code
FROM okc_xprt_rule_hdrs_all_v rule
WHERE rule.org_id = (SELECT org_id FROM okc_terms_templates_all WHERE template_id = p_template_id)
AND rule.intent = (SELECT intent FROM okc_terms_templates_all WHERE template_id = p_template_id)
AND rule.org_wide_flag = 'Y';

Line: 89


SELECT cond.rule_id, cond.rule_condition_id, cond.object_type condition_type, cond.object_code, rule.condition_expr_code, cond.object_value_type, cond.object_value_code, cond.operator
FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_hdrs_all_v rule, okc_xprt_rule_cond_active_v cond
WHERE rultmpl.template_id = p_template_id
AND rule.rule_id = rultmpl.rule_id
AND cond.rule_id = rule.rule_id
UNION ALL
SELECT cond.rule_id, cond.rule_condition_id, cond.object_type condition_type, cond.object_code, rule.condition_expr_code, cond.object_value_type, cond.object_value_code, cond.operator
FROM okc_xprt_rule_hdrs_all_v rule, okc_xprt_rule_cond_active_v cond
WHERE rule.org_id = (SELECT org_id FROM okc_terms_templates_all WHERE template_id = p_template_id)
AND rule.intent = (SELECT intent FROM okc_terms_templates_all WHERE template_id = p_template_id)
AND rule.org_wide_flag = 'Y'
AND cond.rule_id = rule.rule_id;

Line: 126


	DELETE okc_xprt_rule_eval_result_t WHERE doc_id = p_doc_id and doc_type = p_doc_type;

Line: 129


	     	FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Deleted entries in okc_xprt_rule_eval_result_t for this document before populating');

Line: 135


		INSERT INTO okc_xprt_rule_eval_result_t(rule_condition_result_id, doc_id, doc_type, rule_id, condition_id, condition_type, object_code, rule_expr_type, object_value_type, cond_operator, result)
		VALUES(OKC_XPRT_RULE_EVAL_RESULT_S.nextval, p_doc_id, p_doc_type, rule_ids(i), null, null, null, cond_expr_codes(i), null, null, null);

Line: 142


		INSERT INTO okc_xprt_rule_eval_result_t(rule_condition_result_id, doc_id, doc_type, rule_id, condition_id, condition_type, object_code, rule_expr_type, object_value_type, object_value_code, cond_operator, result)
		VALUES(OKC_XPRT_RULE_EVAL_RESULT_S.nextval, p_doc_id, p_doc_type, rule_ids(i), cond_ids(i), cond_types(i), cond_object_codes(i), cond_expr_codes(i), cond_value_type(i), cond_object_value_codes(i), cond_operator(i), null);

Line: 191


	DELETE okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id and doc_type = p_doc_type;

Line: 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');

Line: 241


  INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response)
  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);

Line: 247


  INSERT INTO okc_xprt_rule_eval_condval_t(doc_value_id, doc_id, doc_type, object_type, object_code, value_or_response)
  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);

Line: 270


SELECT distinct cond.object_code, q.mandatory_flag display_flag
FROM okc_xprt_rule_eval_result_t cond, okc_xprt_question_orders q
WHERE cond.doc_id = p_doc_id
AND cond.doc_type = p_doc_type
AND cond.condition_type = 'QUESTION'
AND q.template_id = p_template_id
AND cond.object_code = q.question_id
UNION
SELECT distinct cond.object_value_code, q.mandatory_flag display_flag
FROM okc_xprt_rule_eval_result_t cond, okc_xprt_question_orders q
WHERE cond.doc_id = p_doc_id
AND cond.doc_type = p_doc_type
AND cond.object_value_type = 'QUESTION'
AND q.template_id = p_template_id
AND cond.object_value_code = q.question_id
UNION
SELECT distinct to_char(outcome.object_value_id), q.mandatory_flag display_flag
FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_question_orders q
WHERE rultmpl.template_id = p_template_id
AND outcome.rule_id = rultmpl.rule_id
AND outcome.object_type = 'QUESTION'
AND q.template_id = p_template_id
AND outcome.object_value_id = q.question_id
UNION
SELECT distinct to_char(outcome.object_value_id), q.mandatory_flag display_flag
FROM okc_xprt_rule_hdrs_all_v rul, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_question_orders q
WHERE rul.org_wide_flag = 'Y'
AND outcome.rule_id = rul.rule_id
AND outcome.object_type = 'QUESTION'
AND q.template_id = p_template_id
AND outcome.object_value_id = q.question_id;

Line: 303


SELECT distinct cond.object_code, resp.response, q.mandatory_flag display_flag
FROM okc_xprt_rule_eval_result_t cond, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
WHERE cond.doc_id = p_doc_id
AND cond.doc_type = p_doc_type
AND cond.condition_type = 'QUESTION'
AND resp.doc_id (+) = cond.doc_id
AND resp.doc_type (+) = cond.doc_type
AND resp.question_id (+) = cond.object_code
AND q.template_id = p_template_id
AND cond.object_code = q.question_id
UNION
SELECT distinct cond.object_value_code, resp.response, q.mandatory_flag display_flag
FROM okc_xprt_rule_eval_result_t cond, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
WHERE cond.doc_id = p_doc_id
AND cond.doc_type = p_doc_type
AND cond.object_value_type = 'QUESTION'
AND resp.doc_id (+) = cond.doc_id
AND resp.doc_type (+) = cond.doc_type
AND resp.question_id (+) = cond.object_value_code
AND q.template_id = p_template_id
AND cond.object_value_code = q.question_id
UNION
SELECT distinct to_char(outcome.object_value_id), resp.response, q.mandatory_flag display_flag
FROM  okc_xprt_template_rules rultmpl, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
WHERE rultmpl.template_id = p_template_id
AND outcome.rule_id = rultmpl.rule_id
AND outcome.object_type = 'QUESTION'
AND resp.doc_id (+) = p_doc_id
AND resp.doc_type (+) = p_doc_type
AND resp.question_id (+) = outcome.object_value_id
AND q.template_id = p_template_id
AND outcome.object_value_id = q.question_id
UNION
SELECT distinct to_char(outcome.object_value_id), resp.response, q.mandatory_flag display_flag
FROM  okc_xprt_rule_hdrs_all_v rul, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
WHERE rul.org_wide_flag = 'Y'
AND outcome.rule_id = rul.rule_id
AND outcome.object_type = 'QUESTION'
AND resp.doc_id (+) = p_doc_id
AND resp.doc_type (+) = p_doc_type
AND resp.question_id (+) = outcome.object_value_id
AND q.template_id = p_template_id
AND outcome.object_value_id = q.question_id;

Line: 365


		SELECT 'Y' INTO is_contract_expert_run FROM dual
		WHERE EXISTS (SELECT 1 FROM okc_xprt_doc_ques_response WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND response IS NOT NULL);

Line: 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)
			VALUES(OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'QUESTION', question_ids(i), responses(i), display_flags(i));

Line: 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)
			VALUES(OKC_XPRT_RULE_EVAL_CONDVAL_S.nextval, p_doc_id, p_doc_type, 'QUESTION', question_ids(i), null, display_flags(i));

Line: 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)
	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;

Line: 437


SELECT rule_id, rule_expr_type
FROM okc_xprt_rule_eval_result_t
WHERE doc_id = p_doc_id
AND doc_type = p_doc_type
AND condition_id IS NULL;

Line: 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,
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
							   AND object_type = 'QUESTION' AND object_code = cond.object_code),
							   NULL) lhs_response,
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
								    AND object_type = 'QUESTION' AND object_code = cond.object_value_code),
								    NULL) rhs_response
FROM okc_xprt_rule_eval_result_t cond
WHERE cond.doc_id = p_doc_id
AND cond.doc_type = p_doc_type
AND cond.condition_id IS NOT NULL
AND cond.rule_id = c_rule_id;

Line: 458


SELECT distinct * FROM table(c_crules1)
UNION
SELECT distinct * FROM table(c_crules2);

Line: 619


      	UPDATE okc_xprt_rule_eval_result_t result_tmp
      	SET result = result_tbl(k).result
      	WHERE result_tmp.rule_id = result_tbl(k).rule_id
     	AND nvl(result_tmp.condition_id, -999) = nvl(result_tbl(k).condition_id, -999)
      	AND result_tmp.doc_id = p_doc_id
      	AND result_tmp.doc_type = p_doc_type;

Line: 627


    		SELECT 'Y' INTO has_clauses FROM dual
    		WHERE EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND condition_type = 'CLAUSE');

Line: 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
	IF success_rule_ids.count > 0 THEN
		UPDATE okc_xprt_rule_eval_condval_t
		SET display_flag = 'Y'
		WHERE doc_id = p_doc_id
		AND doc_type = p_doc_type
		AND object_type = 'QUESTION'
		AND display_flag <> 'Y'
		AND object_code IN (SELECT object_value_id FROM okc_xprt_rule_outcomes_act_v WHERE rule_id IN (SELECT * FROM table(success_rule_ids)) and object_type = 'QUESTION');

Line: 651


			FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '160: No. of questions updated with display_flag = Y: ' || rows);

Line: 658


			UPDATE okc_xprt_rule_eval_result_t cond
			SET result = decode(cond_operator, 'IS', 'Y', 'IN', 'Y', 'N')
			WHERE doc_id = p_doc_id
			AND doc_type = p_doc_type
			AND condition_type = 'CLAUSE'
			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
			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
					     and object_type = 'RULE' and object_code IN (SELECT * FROM table(success_rule_ids)))
			RETURNING rule_id BULK COLLECT INTO clause_rules1;

Line: 669


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '170: No. of clause based conditions updated with result: ' || clause_rules1.count);

Line: 677


			UPDATE okc_xprt_rule_eval_result_t cond
			SET cond.result = 'N'
			WHERE cond.doc_id = p_doc_id
			AND cond.doc_type = p_doc_type
			AND condition_type = 'CLAUSE'
			AND cond.cond_operator IN ('IS', 'IN') --as the condition result wont change in other cases
			AND cond.result IS NULL --if the result is Y, then it wont change and the result will not be N in any case
			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
						  and object_type = 'RULE' and object_code IN (SELECT * FROM table(failure_rule_ids)))
			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
					AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id
					AND t.doc_id = d.doc_id AND t.doc_type = d.doc_type AND t.condition_id IS NULL
					AND t.result IS NULL AND d.object_code = t.rule_id)
			RETURNING cond.rule_id BULK COLLECT INTO clause_rules2;

Line: 693


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '180: No. of clause based conditions updated with result: ' || clause_rules2.count);

Line: 839


SELECT value_or_response
FROM okc_xprt_rule_eval_condval_t
WHERE doc_id = p_doc_id
AND doc_type = p_doc_type
AND object_code = p_object_code
AND object_type = p_cond_type;

Line: 848


SELECT value_or_response
FROM okc_xprt_rule_eval_condval_t
WHERE doc_id = p_doc_id
AND doc_type = p_doc_type
AND object_code = p_object_value_code
AND object_type = p_object_value_type;

Line: 857


SELECT object_value_code
FROM okc_xprt_rule_cond_vals_act_v
WHERE rule_condition_id = p_cond_id;

Line: 938


SELECT distinct * FROM table(c_qrules)
UNION
SELECT distinct * FROM table(c_crules1)
UNION
SELECT distinct * FROM table(c_crules2)
UNION
SELECT distinct * FROM table(c_crules3);

Line: 992


		UPDATE okc_xprt_rule_eval_result_t result_tmp
		SET result = 'Y'
		WHERE result_tmp.rule_id = reeval_rules(j)
		AND result_tmp.condition_id IS NULL
		AND result_tmp.doc_id = p_doc_id
		AND result_tmp.doc_type = p_doc_type
      		AND nvl(result_tmp.result, '*') <> 'Y'
		AND ((result_tmp.rule_expr_type = 'ALL' AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') <> 'Y'
								      AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
		     OR (result_tmp.rule_expr_type = 'ANY' AND EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') = 'Y'
								      AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1)))
      		RETURNING n + 1, result_tmp.rule_id INTO n, rule_ids1(n);

Line: 1008


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: No. of rules updated with result = Y based on condition evaluation: ' || (n-1));

Line: 1017


		UPDATE okc_xprt_rule_eval_result_t result_tmp
		SET result = decode(result_tmp.rule_expr_type, 'ANY', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
								      WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
								      decode(result, NULL, 1, 2)) WHERE rownum = 1),
							       'ALL', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
								      WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
								      decode(result, 'N', 1, NULL, 2, 3)) WHERE rownum = 1))
		WHERE result_tmp.rule_id = reeval_rules(j)
      		AND result_tmp.condition_id IS NULL
		AND result_tmp.doc_id = p_doc_id
		AND result_tmp.doc_type = p_doc_type
      		AND nvl(result_tmp.result, '*') = 'Y'
		AND ((result_tmp.rule_expr_type = 'ALL' AND EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') <> 'Y'
								   AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								   AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
		     OR (result_tmp.rule_expr_type = 'ANY' AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') = 'Y'
								      AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1)))
      		RETURNING k + 1, result_tmp.rule_id INTO k, rule_ids2(k);

Line: 1040


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: No. of rules updated with result based on condition evaluation: ' || (k-1));

Line: 1048


		--there is not need to consider nul to success and failure to success case as they will be already handled in first update statement
		UPDATE okc_xprt_rule_eval_result_t result_tmp
		SET result = decode(result_tmp.rule_expr_type, 'ANY', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
								      WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
								      decode(result, NULL, 1, 2)) WHERE rownum = 1),
							       'ALL', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
								      WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
								      AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
								      decode(result, 'N', 1, NULL, 2, 3)) WHERE rownum = 1))
		WHERE result_tmp.rule_id = reeval_rules(j)
      		AND result_tmp.condition_id IS NULL
		AND result_tmp.doc_id = p_doc_id
		AND result_tmp.doc_type = p_doc_type
     		AND nvl(result_tmp.result, '*') <> 'Y'
		AND ((result_tmp.rule_expr_type = 'ALL' AND result_tmp.result IS NULL AND EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') = 'N'
												AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
												AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
		     OR (result_tmp.rule_expr_type = 'ALL' AND nvl(result_tmp.result, '*') = 'N' AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') = 'N'
												AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
												AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
		     OR (result_tmp.rule_expr_type = 'ANY' AND result_tmp.result IS NULL AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') IS NULL
												AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
												AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
		     OR (result_tmp.rule_expr_type = 'ANY' AND nvl(result_tmp.result, '*') = 'N' AND EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') <> 'N'
												AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
												AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1)))
      		RETURNING l + 1, result_tmp.rule_id INTO l, rule_ids3(l);

Line: 1078


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: No. of rules updated with result based on condition evaluation: ' || (l-1));

Line: 1092


    		SELECT 'Y' INTO has_clauses FROM dual
    		WHERE EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND condition_type = 'CLAUSE');

Line: 1108


	--updated additional questions display flag to Y if not updated already
	--reevaluation of rule_ids1 is not needed..
	IF rule_ids1.count > 0 THEN
		UPDATE okc_xprt_rule_eval_condval_t
		SET display_flag = 'Y'
		WHERE doc_id = p_doc_id
		AND doc_type = p_doc_type
		AND object_type = 'QUESTION'
		AND display_flag <> 'Y'   --it won't update if it is already Y, so that the rows1 count will not be included if there is no updation
		AND object_code IN (SELECT object_value_id FROM okc_xprt_rule_outcomes_act_v WHERE rule_id IN (SELECT * FROM table(rule_ids1)) and object_type = 'QUESTION');

Line: 1127


	--updated additional questions display flag to N, if and only if all the the rules with outcome as this question doesnt have their as Y
	--reevaluation of rule_ids2 is needed.. i.e the reason we collect the rule_ids to ques_rules for re-evalaution.
	IF rule_ids2.count > 0 THEN

		FORALL i IN 1 .. rule_ids2.count
			UPDATE okc_xprt_rule_eval_condval_t ques
			SET ques.display_flag = 'N'
			WHERE ques.doc_id = p_doc_id
			AND ques.doc_type = p_doc_type
			AND ques.object_type = 'QUESTION'
			AND ques.display_flag <> 'N'
			AND ques.object_code IN (SELECT object_value_id FROM okc_xprt_rule_outcomes_act_v WHERE rule_id = rule_ids2(i) and object_type = 'QUESTION')
			AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_outcomes_act_v d, okc_xprt_rule_eval_result_t t
					WHERE d.rule_id = t.rule_id AND t.doc_id = ques.doc_id AND t.doc_type = ques.doc_type AND nvl(t.result, '*') = 'Y' AND t.condition_id IS NULL
					AND to_char(d.object_value_id) = ques.object_code AND d.object_type = 'QUESTION');

Line: 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..
		--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
		-- response and display_flag is needed.
		FORALL i IN 1 .. rule_ids2.count
			UPDATE okc_xprt_rule_eval_condval_t ques
			SET ques.value_or_response = NULL
			WHERE ques.doc_id = p_doc_id
			AND ques.doc_type = p_doc_type
			AND ques.object_type = 'QUESTION'
			AND ques.value_or_response IS NOT NULL   --it won't update if it is already NULL, so that re-evaluation of rules for additional questions take place only when it is an actual update
			AND ques.object_code IN (SELECT object_value_id FROM okc_xprt_rule_outcomes_act_v WHERE rule_id = rule_ids2(i) and object_type = 'QUESTION')
			AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_outcomes_act_v d, okc_xprt_rule_eval_result_t t
					WHERE d.rule_id = t.rule_id AND t.doc_id = ques.doc_id AND t.doc_type = ques.doc_type AND nvl(t.result, '*') = 'Y' AND t.condition_id IS NULL
					AND to_char(d.object_value_id) = ques.object_code AND d.object_type = 'QUESTION')
			RETURNING ques.object_code BULK COLLECT INTO question_ids;

Line: 1167


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '180: No. of additional questions which are updated the result to Y based on the new rules that are evaluated: ' || question_ids.count);

Line: 1175


		--question_rules can have duplicate rule_ids as it can have two questions updated to result Null so it comes twice in the question_rules
		IF question_ids.count > 0 THEN
			UPDATE okc_xprt_rule_eval_result_t
			SET result = NULL
			WHERE object_code IN (SELECT * FROM table(question_ids))
			AND doc_id = p_doc_id
			AND doc_type = p_doc_type
			AND condition_type = 'QUESTION'
			RETURNING rule_id BULK COLLECT INTO ques_rules;

Line: 1186


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '190: No. of question based conditions rule_ids which are updated the result to null as the resposne is made to null: ' || ques_rules.count);

Line: 1210


	--updated dependent clause questions display flag to Y if op IN (IS IN) OR N if op IN (IS_NOT NOT_IN)

	IF has_clauses = 'Y' THEN

		--A) rule_ids1--- which have changed their result from null to Y or N to Y
		--updated dependent clause conditions result to Y if op IN (IS,IN) OR N if op IN (IS_NOT,NOT_IN)

		IF rule_ids1.count > 0 THEN
			UPDATE okc_xprt_rule_eval_result_t cond
			SET result = decode(cond_operator, 'IS', 'Y', 'IN', 'Y', 'N')
			WHERE doc_id = p_doc_id
			AND doc_type = p_doc_type
			AND condition_type = 'CLAUSE'
			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
			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
					     and d.object_type = 'RULE' and d.object_code IN (SELECT * FROM table(rule_ids1)))
			RETURNING rule_id BULK COLLECT INTO clause_rules1;

Line: 1229


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '210: No. of clause based questions which are updated the result based on the new rules that are evaluated: ' || clause_rules1.count);

Line: 1237


		--updated dependent clause conditions  result by considering all the other rules result

		IF rule_ids2.count > 0 THEN
			UPDATE okc_xprt_rule_eval_result_t cond
			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
													   WHERE d.doc_id = cond.doc_id and d.doc_type = cond.doc_type AND d.object_type = 'RULE'
													   AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id AND t.doc_id = d.doc_id
													   AND t.doc_type = d.doc_type AND t.condition_id IS NULL AND t.result IS NULL AND ROWNUM = 1),
												           1, NULL, 'N'))
			WHERE cond.doc_id = p_doc_id
			AND cond.doc_type = p_doc_type
			AND condition_type = 'CLAUSE'
			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
						  AND d.object_type = 'RULE' AND d.object_code IN (SELECT * FROM table(rule_ids2)))
			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
					 AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id
					 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')
					 --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
			RETURNING cond.rule_id BULK COLLECT INTO clause_rules2;

Line: 1258


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '220: No. of clause based questions which are updated the result based on the new rules that are evaluated: ' || clause_rules2.count);

Line: 1266


		--updated dependent clause conditions  result by considering all the other rules result
		--for this case (null to N or N to null), when the clasue condition operator is IS_NOT or NOT_IN, the condition result wont change
		--for operator 'IS' or 'IN', the result wont change if it is already 'y'

		IF rule_ids3.count > 0 THEN
			UPDATE okc_xprt_rule_eval_result_t cond
			SET cond.result = decode((SELECT nvl(t.result, 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 AND d.object_type = 'RULE'
						  AND d.dep_clause_cond_id = cond.condition_id AND d.object_code = t.rule_id AND t.doc_id = d.doc_id
						  AND t.doc_type = d.doc_type AND t.condition_id IS NULL AND t.result IS NULL AND ROWNUM = 1),
						  1, NULL, 'N')
			WHERE cond.doc_id = p_doc_id
			AND cond.doc_type = p_doc_type
			AND condition_type = 'CLAUSE'
			AND cond.cond_operator IN ('IS', 'IN') --as the condition result wont change in other cases
			AND nvl(cond.result, '*') <> 'Y'  --if the result is already Y, then it wont change
			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
						  AND d.object_type = 'RULE' and d.object_code IN (SELECT * FROM table(rule_ids3)))
			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
					 AND d.object_type = 'RULE' AND d.dep_clause_cond_id = cond.condition_id AND t.doc_id = d.doc_id
					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)
			RETURNING cond.rule_id BULK COLLECT INTO clause_rules3;

Line: 1290


				FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '230: No. of clause based questions which are updated the result based on the new rules that are evaluated: ' || clause_rules3.count);

Line: 1298


	--clause_rules1 OR clause_rules2 OR clause_rules3 can have duplicate rule_ids as a rule can be updated twice when it have as two clause based conditions
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

	--Step III: Finding dependent rules for all the above changes and reevaluating them
	IF (has_clauses = 'Y' AND (clause_rules1.count > 0 OR clause_rules2.count > 0 OR clause_rules3.count > 0)) OR (ques_rules.count > 0) THEN
		OPEN c_dep_rules(clause_rules1, clause_rules2, clause_rules3, ques_rules);

Line: 1339


SELECT rule_id, condition_id, condition_type, rule_expr_type, object_code, cond_operator, result, object_value_type, object_value_code,
(SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
 AND object_type = 'QUESTION' AND object_code = cond.object_code) lhs_response,
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
								    AND object_type = 'QUESTION' AND object_code = cond.object_value_code),
								    NULL) rhs_response
FROM okc_xprt_rule_eval_result_t cond
WHERE doc_id = p_doc_id
AND doc_type = p_doc_type
AND object_code = p_question_id
AND condition_type = 'QUESTION'

UNION

SELECT rule_id, condition_id, condition_type, rule_expr_type, object_code, cond_operator, result, object_value_type, object_value_code,
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
							      AND object_type = 'QUESTION' AND object_code = cond.object_code),
							      NULL) lhs_response,
(SELECT value_or_response FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
 AND object_type = 'QUESTION' AND object_code = cond.object_value_code) rhs_response
FROM okc_xprt_rule_eval_result_t cond
WHERE doc_id = p_doc_id
AND doc_type = p_doc_type
AND object_value_code = p_question_id
AND object_value_type = 'QUESTION';

Line: 1388


	UPDATE okc_xprt_rule_eval_condval_t
	SET value_or_response  = p_response
	WHERE object_code = p_question_id
	AND object_type = 'QUESTION'
 	AND doc_id = p_doc_id
	AND doc_type = p_doc_type;

Line: 1478


		UPDATE okc_xprt_rule_eval_result_t result_tmp
		SET result = result_tbl(j).result
		WHERE result_tmp.rule_id = result_tbl(j).rule_id
		AND result_tmp.condition_id = result_tbl(j).condition_id
		AND result_tmp.doc_id = p_doc_id
		AND result_tmp.doc_type = p_doc_type;

Line: 1510


SELECT orig_system_reference_code, orig_system_reference_id1
FROM okc_template_usages
WHERE document_type = p_doc_type
AND document_id = p_doc_id;

Line: 1579


	DELETE okc_xprt_doc_ques_response WHERE doc_id = p_doc_id AND doc_type = p_doc_type;

Line: 1582


	INSERT INTO okc_xprt_doc_ques_response (doc_question_response_id, doc_id, doc_type, question_id, response)
	(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
	 WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND object_type = 'QUESTION' AND display_flag = 'Y');

Line: 1587


	UPDATE okc_template_usages
	SET contract_expert_finish_flag = 'N'
	WHERE document_id = p_doc_id
	AND document_type = p_doc_type;

Line: 1625


    		SELECT 'N' INTO has_all_ques_answered FROM dual
    		WHERE EXISTS (SELECT 1 FROM okc_xprt_rule_eval_condval_t WHERE doc_id = p_doc_id AND doc_type = p_doc_type
				AND object_type = 'QUESTION' AND display_flag = 'Y' AND value_or_response IS NULL);

Line: 1668


	INSERT INTO okc_xprt_doc_ques_response_h (doc_question_response_id, doc_id, doc_type, major_version, question_id, response)
	(SELECT doc_question_response_id, p_doc_id, p_doc_type, p_major_version, question_id, response FROM okc_xprt_doc_ques_response
	 WHERE doc_id = p_doc_id AND doc_type = p_doc_type);

Line: 1705


	INSERT INTO okc_xprt_doc_ques_response(doc_question_response_id, doc_id, doc_type, question_id, response)
	(SELECT doc_question_response_id, p_doc_id, p_doc_type, question_id, response FROM okc_xprt_doc_ques_response_h
	 WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND major_version = p_major_version);

Line: 1723


PROCEDURE delete_xprt_responses_version(doc_id IN NUMBER, doc_type IN VARCHAR2, p_major_version IN NUMBER) IS

l_api_name CONSTANT VARCHAR2(30) := 'delete_xprt_responses_version';

Line: 1731


	     	FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering delete_xprt_responses_version method');

Line: 1741


	--deleting the responses from okc_xprt_doc_ques_response_h history table as the revision is deleted.
	DELETE FROM okc_xprt_doc_ques_response_h
	WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND major_version = p_major_version;

Line: 1746


	     	FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: no. of rows deleted : ' || sql%rowcount);

Line: 1747


	     	FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Leaving delete_xprt_responses_version method');

Line: 1756


END delete_xprt_responses_version;