1 PACKAGE BODY OKC_XPRT_RULES_ENGINE_PVT AS
2 /* $Header: OKCVXRULENGB.pls 120.1.12020000.4 2012/08/22 09:03:02 nbingi noship $ */
3
4 --global_variables
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_RULES_ENGINE_PVT';
6 G_MODULE_NAME CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
7
8 FUNCTION getTemplateId RETURN NUMBER IS
9 BEGIN
10 RETURN p_template_id;
11 END;
12
13 FUNCTION getDocId RETURN NUMBER IS
14 BEGIN
15 RETURN p_doc_id;
16 END;
17
18 FUNCTION getDocType RETURN VARCHAR2 IS
19 BEGIN
20 RETURN p_doc_type;
21 END;
22
23 PROCEDURE init_contract_expert(doc_id IN NUMBER, doc_type IN VARCHAR2, template_id IN NUMBER, x_has_questions OUT NOCOPY VARCHAR2) IS
24
25 l_api_name CONSTANT VARCHAR2(30) := 'init_contract_expert';
26 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
27
28 l_template_id NUMBER;
29 BEGIN
30
31 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
32 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
33 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
34 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
35 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: template_id : ' || template_id);
36 END IF;
37
38 p_doc_id := doc_id;
39 p_doc_type := doc_type;
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
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;
58 END;
59
60 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
61 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: x_has_questions : ' || x_has_questions);
62 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Leaving method');
63 END IF;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
68 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '120: Exception occured: ' || sqlerrm);
69 END IF;
70 raise;
71 END init_contract_expert;
72
73
74 PROCEDURE populate_rule_cond_eval_table IS
75
76 CURSOR c_rules IS
77 SELECT rule.rule_id, rule.condition_expr_code
78 FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_hdrs_all_v rule
79 WHERE rultmpl.template_id = p_template_id
80 AND rule.rule_id = rultmpl.rule_id
81 UNION ALL
82 SELECT rule.rule_id, rule.condition_expr_code
83 FROM okc_xprt_rule_hdrs_all_v rule
84 WHERE rule.org_id = (SELECT org_id FROM okc_terms_templates_all WHERE template_id = p_template_id)
85 AND rule.intent = (SELECT intent FROM okc_terms_templates_all WHERE template_id = p_template_id)
86 AND rule.org_wide_flag = 'Y';
87
88 CURSOR c_rule_conditions IS
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
90 FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_hdrs_all_v rule, okc_xprt_rule_cond_active_v cond
91 WHERE rultmpl.template_id = p_template_id
92 AND rule.rule_id = rultmpl.rule_id
93 AND cond.rule_id = rule.rule_id
94 UNION ALL
95 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
96 FROM okc_xprt_rule_hdrs_all_v rule, okc_xprt_rule_cond_active_v cond
97 WHERE rule.org_id = (SELECT org_id FROM okc_terms_templates_all WHERE template_id = p_template_id)
98 AND rule.intent = (SELECT intent FROM okc_terms_templates_all WHERE template_id = p_template_id)
99 AND rule.org_wide_flag = 'Y'
100 AND cond.rule_id = rule.rule_id;
101
102 rule_ids number_type;
103 cond_ids number_type;
104
105 TYPE varchar2_30 IS TABLE OF VARCHAR2(30);
106 cond_types varchar2_30;
107 cond_expr_codes varchar2_30;
108 cond_operator varchar2_30;
109 cond_value_type varchar2_30;
110
111 TYPE varchar2_40 IS TABLE OF VARCHAR2(40);
112 cond_object_codes varchar2_40;
113
114 TYPE varchar2_1000 IS TABLE OF VARCHAR2(1000);
115 cond_object_value_codes varchar2_1000;
116
117 l_api_name CONSTANT VARCHAR2(30) := 'populate_rule_cond_eval_table';
118 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
119
120 BEGIN
121
122 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
123 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
124 END IF;
125
126 DELETE okc_xprt_rule_eval_result_t WHERE doc_id = p_doc_id and doc_type = p_doc_type;
127
128 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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');
130 END IF;
131
132 OPEN c_rules;
133 FETCH c_rules BULK COLLECT INTO rule_ids, cond_expr_codes;
134 FORALL i IN 1 .. rule_ids.count
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)
136 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);
137 CLOSE c_rules;
138
139 OPEN c_rule_conditions;
140 FETCH c_rule_conditions BULK COLLECT INTO rule_ids, cond_ids, cond_types, cond_object_codes, cond_expr_codes, cond_value_type, cond_object_value_codes, cond_operator;
141 FORALL i IN 1 .. rule_ids.count
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)
143 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);
144 CLOSE c_rule_conditions;
145
146 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
147 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Populated entries in okc_xprt_rule_eval_result_t for this document');
148 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: No. of rules populated: ' || cond_expr_codes.count);
149 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: No. of rule conditions populated: ' || cond_ids.count);
150 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Leaving method');
151 END IF;
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
156 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '130: Exception occured: ' || sqlerrm);
157 END IF;
158 raise;
159 END populate_rule_cond_eval_table;
160
161
162 PROCEDURE populate_doc_var_values IS
163
164 x_return_status VARCHAR(1);
165 x_msg_data VARCHAR2(4000);
166 x_msg_count NUMBER;
167 x_hdr_var_value_tbl okc_xprt_xrule_values_pvt.var_value_tbl_type;
168 x_line_sysvar_value_tbl okc_xprt_xrule_values_pvt.line_sys_var_value_tbl_type;
169 x_line_count NUMBER;
170 x_line_variables_count NUMBER;
171 x_intent VARCHAR2(1);
172 x_org_id NUMBER;
173
174 TYPE var_val_rec_type IS RECORD (
175 variable_code VARCHAR2(40),
176 variable_value_id VARCHAR2(4000)
177 );
178
179 TYPE var_val_tbl_type IS TABLE OF var_val_rec_type INDEX BY BINARY_INTEGER;
180 variable_values_tbl var_val_tbl_type;
181
182 l_api_name CONSTANT VARCHAR2(30) := 'populate_doc_var_values';
183 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
184
185 BEGIN
186
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;
196
197 okc_xprt_xrule_values_pvt.get_document_values(p_api_version => 1,
198 p_init_msg_list => FND_API.G_FALSE,
199 p_doc_type => p_doc_type,
200 p_doc_id => p_doc_id,
201 x_return_status => x_return_status,
202 x_msg_data => x_msg_data,
203 x_msg_count => x_msg_count,
204 x_hdr_var_value_tbl => x_hdr_var_value_tbl,
205 x_line_sysvar_value_tbl => x_line_sysvar_value_tbl,
206 x_line_count => x_line_count,
207 x_line_variables_count => x_line_variables_count,
208 x_intent => x_intent,
209 x_org_id => x_org_id
210 );
211
212 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
213 IF x_hdr_var_value_tbl.Count > 0 THEN
214 FOR j IN x_hdr_var_value_tbl.first..x_hdr_var_value_tbl.LAST LOOP
215 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Variable code: ' || x_hdr_var_value_tbl(j).variable_code);
216 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Variable value id: ' || x_hdr_var_value_tbl(j).variable_value_id);
217 END LOOP;
218 END IF;
219
220 IF x_line_sysvar_value_tbl.Count > 0 THEN
221 FOR j IN x_line_sysvar_value_tbl.first..x_line_sysvar_value_tbl.LAST LOOP
222 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: Variable code: ' || x_line_sysvar_value_tbl(j).variable_code);
223 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: Variable value: ' || x_line_sysvar_value_tbl(j).variable_value);
224 END LOOP;
225 END IF;
226 END IF;
227
228 IF x_hdr_var_value_tbl.Count > 0 THEN
229 FOR j IN x_hdr_var_value_tbl.first..x_hdr_var_value_tbl.LAST LOOP
230 IF InStr(x_hdr_var_value_tbl(j).variable_code,'CONSTANT$') > 0 THEN
231 variable_values_tbl(j).variable_code := SubStr(x_hdr_var_value_tbl(j).variable_code,Length('CONSTANT$')+1);
232 ELSIF InStr(x_hdr_var_value_tbl(j).variable_code,'USER$') > 0 THEN
233 variable_values_tbl(j).variable_code := SubStr(x_hdr_var_value_tbl(j).variable_code,Length('USER$')+1);
234 ELSE
235 variable_values_tbl(j).variable_code := x_hdr_var_value_tbl(j).variable_code;
236 END IF;
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
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
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;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
261 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '150: Exception occured: ' || sqlerrm);
262 END IF;
263 raise;
264 END populate_doc_var_values;
265
266
267 PROCEDURE populate_doc_questions IS
268
269 CURSOR c_question is
270 SELECT distinct cond.object_code, q.mandatory_flag display_flag
271 FROM okc_xprt_rule_eval_result_t cond, okc_xprt_question_orders q
272 WHERE cond.doc_id = p_doc_id
273 AND cond.doc_type = p_doc_type
274 AND cond.condition_type = 'QUESTION'
275 AND q.template_id = p_template_id
276 AND cond.object_code = q.question_id
277 UNION
278 SELECT distinct cond.object_value_code, q.mandatory_flag display_flag
279 FROM okc_xprt_rule_eval_result_t cond, okc_xprt_question_orders q
280 WHERE cond.doc_id = p_doc_id
281 AND cond.doc_type = p_doc_type
282 AND cond.object_value_type = 'QUESTION'
283 AND q.template_id = p_template_id
284 AND cond.object_value_code = q.question_id
285 UNION
286 SELECT distinct to_char(outcome.object_value_id), q.mandatory_flag display_flag
287 FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_question_orders q
288 WHERE rultmpl.template_id = p_template_id
289 AND outcome.rule_id = rultmpl.rule_id
290 AND outcome.object_type = 'QUESTION'
291 AND q.template_id = p_template_id
292 AND outcome.object_value_id = q.question_id
293 UNION
294 SELECT distinct to_char(outcome.object_value_id), q.mandatory_flag display_flag
295 FROM okc_xprt_rule_hdrs_all_v rul, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_question_orders q
296 WHERE rul.org_wide_flag = 'Y'
297 AND outcome.rule_id = rul.rule_id
298 AND outcome.object_type = 'QUESTION'
299 AND q.template_id = p_template_id
300 AND outcome.object_value_id = q.question_id;
301
302 CURSOR c_question_and_response is
303 SELECT distinct cond.object_code, resp.response, q.mandatory_flag display_flag
304 FROM okc_xprt_rule_eval_result_t cond, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
305 WHERE cond.doc_id = p_doc_id
306 AND cond.doc_type = p_doc_type
307 AND cond.condition_type = 'QUESTION'
308 AND resp.doc_id (+) = cond.doc_id
309 AND resp.doc_type (+) = cond.doc_type
310 AND resp.question_id (+) = cond.object_code
311 AND q.template_id = p_template_id
312 AND cond.object_code = q.question_id
313 UNION
314 SELECT distinct cond.object_value_code, resp.response, q.mandatory_flag display_flag
315 FROM okc_xprt_rule_eval_result_t cond, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
316 WHERE cond.doc_id = p_doc_id
317 AND cond.doc_type = p_doc_type
318 AND cond.object_value_type = 'QUESTION'
319 AND resp.doc_id (+) = cond.doc_id
320 AND resp.doc_type (+) = cond.doc_type
321 AND resp.question_id (+) = cond.object_value_code
322 AND q.template_id = p_template_id
323 AND cond.object_value_code = q.question_id
324 UNION
325 SELECT distinct to_char(outcome.object_value_id), resp.response, q.mandatory_flag display_flag
326 FROM okc_xprt_template_rules rultmpl, okc_xprt_rule_outcomes_act_v outcome, okc_xprt_doc_ques_response resp, okc_xprt_question_orders q
327 WHERE rultmpl.template_id = p_template_id
328 AND outcome.rule_id = rultmpl.rule_id
329 AND outcome.object_type = 'QUESTION'
330 AND resp.doc_id (+) = p_doc_id
331 AND resp.doc_type (+) = p_doc_type
332 AND resp.question_id (+) = outcome.object_value_id
333 AND q.template_id = p_template_id
334 AND outcome.object_value_id = q.question_id
335 UNION
336 SELECT distinct to_char(outcome.object_value_id), resp.response, q.mandatory_flag display_flag
337 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
338 WHERE rul.org_wide_flag = 'Y'
339 AND outcome.rule_id = rul.rule_id
340 AND outcome.object_type = 'QUESTION'
341 AND resp.doc_id (+) = p_doc_id
342 AND resp.doc_type (+) = p_doc_type
343 AND resp.question_id (+) = outcome.object_value_id
344 AND q.template_id = p_template_id
345 AND outcome.object_value_id = q.question_id;
346
347 TYPE varchar2_1 IS TABLE OF VARCHAR2(1);
348 display_flags varchar2_1;
349 TYPE varchar2_40 IS TABLE OF VARCHAR2(40);
350 question_ids varchar2_40;
351 TYPE varchar2_4000 IS TABLE OF VARCHAR2(4000);
352 responses varchar2_4000;
353 is_contract_expert_run VARCHAR2(1) := 'N';
354
355 l_api_name CONSTANT VARCHAR2(30) := 'populate_doc_questions';
356 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
357
358 BEGIN
359
360 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
361 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
362 END IF;
363
364 BEGIN
365 SELECT 'Y' INTO is_contract_expert_run FROM dual
366 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);
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN
369 NULL;
370 END;
371
372 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
373 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: is_contract_expert_run : ' || is_contract_expert_run);
374 END IF;
375
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;
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
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
398 EXCEPTION
399 WHEN OTHERS THEN
400 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '130: Exception occured: ' || sqlerrm);
402 END IF;
403 raise;
404 END populate_doc_questions;
405
406 PROCEDURE populate_rule_cond_dep IS
407
408 l_api_name CONSTANT VARCHAR2(30) := 'populate_rule_cond_dep';
409 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
410
411 BEGIN
412
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');
422 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Leaving method');
423 END IF;
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
428 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '130: Exception occured: ' || sqlerrm);
429 END IF;
430 raise;
431 END populate_rule_cond_dep;
432
433
434 PROCEDURE evaluate_rules_and_conditions IS
435
436 CURSOR c_rules IS
437 SELECT rule_id, rule_expr_type
438 FROM okc_xprt_rule_eval_result_t
439 WHERE doc_id = p_doc_id
440 AND doc_type = p_doc_type
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),
450 NULL) rhs_response
451 FROM okc_xprt_rule_eval_result_t cond
452 WHERE cond.doc_id = p_doc_id
453 AND cond.doc_type = p_doc_type
454 AND cond.condition_id IS NOT NULL
455 AND cond.rule_id = c_rule_id;
456
457 CURSOR c_dep_rules(c_crules1 OKC_TBL_NUMBER, c_crules2 OKC_TBL_NUMBER) IS
458 SELECT distinct * FROM table(c_crules1)
459 UNION
460 SELECT distinct * FROM table(c_crules2);
461
462 success_rule_ids OKC_TBL_NUMBER;
463 failure_rule_ids OKC_TBL_NUMBER;
464 reeval_rule_ids OKC_TBL_NUMBER;
465 clause_rules1 OKC_TBL_NUMBER;
466 clause_rules2 OKC_TBL_NUMBER;
467
468 result_tbl result_tbl_type;
469 is_rule_evaluated VARCHAR2(1);
470 has_clauses VARCHAR2(1) := 'N';
471 l_cond_result BOOLEAN;
472 i NUMBER := 0;
473 s NUMBER := 0;
474 f NUMBER := 0;
475 rows NUMBER := 0;
476
477 l_api_name CONSTANT VARCHAR2(30) := 'evaluate_rules_and_conditions';
478 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
479
480 BEGIN
481
482 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
483 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
484 END IF;
485
486 success_rule_ids := OKC_TBL_NUMBER();
487 failure_rule_ids := OKC_TBL_NUMBER();
488 reeval_rule_ids := OKC_TBL_NUMBER();
489 clause_rules1 := OKC_TBL_NUMBER();
490 clause_rules2 := OKC_TBL_NUMBER();
491
492 FOR rule IN c_rules LOOP
493 is_rule_evaluated := 'N';
494
495 FOR rule_cond IN c_rule_conditions(rule.rule_id) LOOP
496
497 l_cond_result := NULL;
498
499 IF (rule_cond.condition_type = 'CLAUSE' AND (rule_cond.cond_operator = 'IS_NOT' OR rule_cond.cond_operator = 'NOT_IN')) THEN
500 l_cond_result := TRUE;
501
502 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
503 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110:1 rule_id : ' || rule.rule_id || ' condition_id : ' || rule_cond.condition_id);
504 END IF;
505 ELSIF ( (rule_cond.condition_type = 'QUESTION' AND rule_cond.lhs_response IS NULL)
506 OR (rule_cond.object_value_type = 'QUESTION' AND rule_cond.rhs_response IS NULL)) THEN
507 l_cond_result := NULL;
508
509 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110:2 rule_id : ' || rule.rule_id || ' condition_id : ' || rule_cond.condition_id);
511 END IF;
512 ELSIF (rule_cond.condition_type = 'QUESTION' AND rule_cond.lhs_response IS NOT NULL) THEN
513 l_cond_result := evaluate_condition(rule_cond.condition_id, rule_cond.condition_type, rule_cond.object_code, rule_cond.object_value_type, rule_cond.object_value_code, rule_cond.cond_operator);
514
515 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
516 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110:3 rule_id : ' || rule.rule_id || ' condition_id : ' || rule_cond.condition_id);
517 END IF;
518 ELSIF (rule_cond.condition_type = 'VARIABLE') THEN
519 l_cond_result := evaluate_condition(rule_cond.condition_id, rule_cond.condition_type, rule_cond.object_code, rule_cond.object_value_type, rule_cond.object_value_code, rule_cond.cond_operator);
520
521 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110:4 rule_id : ' || rule.rule_id || ' condition_id : ' || rule_cond.condition_id);
523 END IF;
524 ELSE
525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
526 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110:5 rule_id : ' || rule.rule_id || ' condition_id : ' || rule_cond.condition_id);
527 END IF;
528 END IF;
529
530 IF l_cond_result THEN
531 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
532 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_cond_result : TRUE');
533 END IF;
534
535 i := i + 1;
536 result_tbl(i).rule_id := rule.rule_id;
537 result_tbl(i).condition_id := rule_cond.condition_id;
538 result_tbl(i).result := 'Y';
539
540 IF rule.rule_expr_type = 'ANY' THEN
541 i := i + 1;
542 result_tbl(i).rule_id := rule.rule_id;
543 result_tbl(i).condition_id := null;
544 result_tbl(i).result := 'Y';
545 is_rule_evaluated := 'Y';
546 success_rule_ids.extend(1);
547 s := s + 1;
548 success_rule_ids(s) := rule.rule_id;
549 EXIT;
550 END IF;
551 ELSIF l_cond_result IS NULL THEN
552 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_cond_result : NULL');
554 END IF;
555
556 is_rule_evaluated := 'L'; -- atleast one of the rule condition cannot be evaluated now.
557 ELSE
558 -- if the condition result is false
559 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
560 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_cond_result : FALSE');
561 END IF;
562
563 i := i + 1;
564 result_tbl(i).rule_id := rule.rule_id;
565 result_tbl(i).condition_id := rule_cond.condition_id;
566 result_tbl(i).result := 'N';
567
568 IF rule.rule_expr_type = 'ALL' THEN
569 i := i + 1;
570 result_tbl(i).rule_id := rule.rule_id;
571 result_tbl(i).condition_id := null;
572 result_tbl(i).result := 'N';
573 is_rule_evaluated := 'Y';
574 failure_rule_ids.extend(1);
575 f := f + 1;
576 failure_rule_ids(f) := rule.rule_id;
577 EXIT;
578 END IF;
579 END IF;
580
581 END LOOP;
582
583 IF is_rule_evaluated = 'N' AND rule.rule_expr_type = 'ANY' THEN --all the condition results are false for this rule
584 i := i + 1;
585 result_tbl(i).rule_id := rule.rule_id;
586 result_tbl(i).condition_id := null;
587 result_tbl(i).result := 'N';
588 failure_rule_ids.extend(1);
589 f := f + 1;
590 failure_rule_ids(f) := rule.rule_id;
591 END IF;
592
593 IF is_rule_evaluated = 'N' AND rule.rule_expr_type = 'ALL' THEN --all the condition results are success for this rule.
594 i := i + 1;
595 result_tbl(i).rule_id := rule.rule_id;
596 result_tbl(i).condition_id := null;
597 result_tbl(i).result := 'Y';
598 success_rule_ids.extend(1);
599 s := s + 1;
600 success_rule_ids(s) := rule.rule_id;
601 END IF;
602 END LOOP;
603
604 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
605 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: result_tbl.count: ' || result_tbl.count);
606 FOR a IN 1 .. result_tbl.count LOOP
607 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: result_tbl rule_id: ' || result_tbl(a).rule_id || ' condition_id: ' || result_tbl(a).condition_id || ' result: ' || result_tbl(a).result); END LOOP;
608
609 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: success_rule_ids.count: ' || success_rule_ids.count);
610 FOR a IN 1 .. success_rule_ids.count LOOP
611 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: success_rule_id: ' || success_rule_ids(a)); END LOOP;
612
613 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: failure_rule_ids.count: ' || failure_rule_ids.count);
614 FOR a IN 1 .. failure_rule_ids.count LOOP
615 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: failure_rule_id: ' || failure_rule_ids(a)); END LOOP;
616 END IF;
617
618 FORALL k IN 1 .. result_tbl.count
619 UPDATE okc_xprt_rule_eval_result_t result_tmp
620 SET result = result_tbl(k).result
621 WHERE result_tmp.rule_id = result_tbl(k).rule_id
622 AND nvl(result_tmp.condition_id, -999) = nvl(result_tbl(k).condition_id, -999)
623 AND result_tmp.doc_id = p_doc_id
624 AND result_tmp.doc_type = p_doc_type;
625
626 BEGIN
627 SELECT 'Y' INTO has_clauses FROM dual
628 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');
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 NULL;
632 END;
633
634 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '150: has_clauses: ' || has_clauses);
636 END IF;
637
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'
647 AND display_flag <> 'Y'
648 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');
649 rows := sql%rowcount;
650 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
651 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '160: No. of questions updated with display_flag = Y: ' || rows);
652 END IF;
653 END IF;
654
655 --Step II: Dependent Clause based conditions handling:
656 IF has_clauses = 'Y' THEN
657 IF success_rule_ids.count > 0 THEN
658 UPDATE okc_xprt_rule_eval_result_t cond
659 SET result = decode(cond_operator, 'IS', 'Y', 'IN', 'Y', 'N')
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
669 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '170: No. of clause based conditions updated with result: ' || clause_rules1.count);
670
671 FOR a IN 1 .. clause_rules1.count LOOP
672 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '170: clause_rules1 rule_id: ' || clause_rules1(a)); END LOOP;
673 END IF;
674 END IF;
675
676 IF failure_rule_ids.count > 0 THEN
677 UPDATE okc_xprt_rule_eval_result_t cond
678 SET cond.result = 'N'
679 WHERE cond.doc_id = p_doc_id
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
689 AND t.result IS NULL AND d.object_code = t.rule_id)
690 RETURNING cond.rule_id BULK COLLECT INTO clause_rules2;
691
692 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
693 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '180: No. of clause based conditions updated with result: ' || clause_rules2.count);
694
695 FOR a IN 1 .. clause_rules2.count LOOP
696 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '180: clause_rules2 rule_id: ' || clause_rules2(a)); END LOOP;
697 END IF;
698 END IF;
699
700 --Step III: Finding dependent rules for all the above changes and reevaluating them
701 IF clause_rules1.count > 0 OR clause_rules2.count > 0 THEN
702 OPEN c_dep_rules(clause_rules1, clause_rules2);
703 FETCH c_dep_rules BULK COLLECT INTO reeval_rule_ids;
704 CLOSE c_dep_rules;
705
706 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
707 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '190: reeval_rule_ids.count: ' || reeval_rule_ids.count);
708
709 FOR a IN 1 .. reeval_rule_ids.count LOOP
710 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '190: reeval_rule_ids rule_id: ' || reeval_rule_ids(a)); END LOOP;
711 END IF;
712 END IF;
713 END IF;
714
715 IF reeval_rule_ids.count > 0 THEN
716 reevaluate_rules(reeval_rule_ids);
717 END IF;
718
719 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
720 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '200: Leaving method');
721 END IF;
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
726 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '210: Exception occured: ' || sqlerrm);
727 END IF;
728 raise;
729 END evaluate_rules_and_conditions;
730
731
732 FUNCTION op_is RETURN BOOLEAN IS
733 BEGIN
734
735 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
736 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
737 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
738 IF lhs_values_tbl(k) = rhs_values_tbl(m) THEN
739 RETURN TRUE;
740 END IF;
741 END LOOP;
742 END LOOP;
743 END IF;
744 RETURN FALSE;
745 END op_is;
746
747 FUNCTION op_in RETURN BOOLEAN IS
748 BEGIN
749 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
750 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
751 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
752 IF lhs_values_tbl(k) = rhs_values_tbl(m) THEN
753 RETURN TRUE;
754 END IF;
755 END LOOP;
756 END LOOP;
757 END IF;
758 RETURN FALSE;
759 END op_in;
760
761 FUNCTION op_is_not return BOOLEAN IS
762 BEGIN
763 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
764 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
765 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
766 IF lhs_values_tbl(k) = rhs_values_tbl(m) THEN
767 RETURN FALSE;
768 END IF;
769 END LOOP;
770 END LOOP;
771 END IF;
772 RETURN TRUE;
773 END op_is_not;
774
775 FUNCTION op_not_in return BOOLEAN IS
776 BEGIN
777 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
778 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
779 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
780 IF lhs_values_tbl(k) = rhs_values_tbl(m) THEN
781 RETURN FALSE;
782 END IF;
783 END LOOP;
784 END LOOP;
785 END IF;
786 RETURN TRUE;
787 END op_not_in;
788
789 FUNCTION op_numeric(op VARCHAR2) return BOOLEAN IS
790 BEGIN
791 IF op = '<' THEN
792 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
793 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
794 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
795 IF lhs_values_tbl(k) < rhs_values_tbl(m) THEN
796 RETURN TRUE;
797 END IF;
798 END LOOP;
799 END LOOP;
800 END IF;
801 ELSIF op = '>' THEN
802 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
803 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
804 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
805 IF lhs_values_tbl(k) > rhs_values_tbl(m) THEN
806 RETURN TRUE;
807 END IF;
808 END LOOP;
809 END LOOP;
810 END IF;
811 ELSIF op = '>=' THEN
812 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
813 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
814 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
815 IF lhs_values_tbl(k) >= rhs_values_tbl(m) THEN
816 RETURN TRUE;
817 END IF;
818 END LOOP;
819 END LOOP;
820 END IF;
821 ELSIF op = '<=' THEN
822 IF lhs_values_tbl.Count > 0 AND rhs_values_tbl.Count > 0 THEN
823 FOR k IN lhs_values_tbl.FIRST..lhs_values_tbl.LAST LOOP
824 FOR m IN rhs_values_tbl.FIRST..rhs_values_tbl.LAST LOOP
825 IF lhs_values_tbl(k) <= rhs_values_tbl(m) THEN
826 RETURN TRUE;
827 END IF;
828 END LOOP;
829 END LOOP;
830 END IF;
831
832 END IF;
833 RETURN FALSE;
834 END op_numeric;
835
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;
854
855 --FOR object_value_type of 'VALUE'
856 CURSOR c_rhsvalues_stored IS
857 SELECT object_value_code
858 FROM okc_xprt_rule_cond_vals_act_v
859 WHERE rule_condition_id = p_cond_id;
860
861 l_op_result BOOLEAN := FALSE;
862
863 l_api_name CONSTANT VARCHAR2(30) := 'evaluate_condition';
864 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
865
866 BEGIN
867
868 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
870 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_cond_id : ' || p_cond_id);
871 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_cond_type : ' || p_cond_type);
872 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_object_code : ' || p_object_code);
873 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_object_value_type : ' || p_object_value_type);
874 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_object_value_code : ' || p_object_value_code);
875 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_operator : ' || p_operator);
876 END IF;
877
878 OPEN c_lhsvalues;
879 FETCH c_lhsvalues BULK COLLECT INTO lhs_values_tbl;
880 CLOSE c_lhsvalues;
881
882 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
883 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: No. of lhs values: ' || lhs_values_tbl.count);
884
885 FOR a IN 1 .. lhs_values_tbl.count LOOP
886 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: lhs value: ' || lhs_values_tbl(a)); END LOOP;
887 END IF;
888
889 IF p_object_value_type = 'VALUE' THEN
890 OPEN c_rhsvalues_stored;
891 FETCH c_rhsvalues_stored BULK COLLECT INTO rhs_values_tbl;
892 CLOSE c_rhsvalues_stored;
893 ELSE
894 OPEN c_rhsvalues;
895 FETCH c_rhsvalues BULK COLLECT INTO rhs_values_tbl;
896 CLOSE c_rhsvalues;
897 END IF;
898
899 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: No. of rhs values: ' || rhs_values_tbl.count);
901
902 FOR a IN 1 .. rhs_values_tbl.count LOOP
903 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: rhs value: ' || rhs_values_tbl(a)); END LOOP;
904 END IF;
905
906 IF p_operator = 'IS' THEN l_op_result := op_is();
907 ELSIF p_operator = 'IS_NOT' THEN l_op_result := op_is_not();
908 ELSIF p_operator = 'IN' THEN l_op_result := op_in();
909 ELSIF p_operator = 'NOT_IN' THEN l_op_result := op_not_in();
910 ELSIF p_operator IN ('=') THEN l_op_result := op_is();
911 ELSIF p_operator IN ('<>') THEN l_op_result := op_is_not();
912 ELSIF p_operator IN ('<=','>=','<','>') THEN l_op_result := op_numeric(p_operator);
913 END IF;
914
915 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
916 IF l_op_result THEN
917 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: l_op_result: TRUE');
918 ELSE
919 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: l_op_result: FALSE');
920 END IF;
921 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: Leaving method');
922 END IF;
923
924 RETURN l_op_result;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '140: Exception occured: ' || sqlerrm);
930 END IF;
931 raise;
932 END evaluate_condition;
933
934
935 PROCEDURE reevaluate_rules(reeval_rules OKC_TBL_NUMBER) IS
936
937 CURSOR c_dep_rules(c_crules1 OKC_TBL_NUMBER, c_crules2 OKC_TBL_NUMBER, c_crules3 OKC_TBL_NUMBER, c_qrules OKC_TBL_NUMBER) IS
938 SELECT distinct * FROM table(c_qrules)
939 UNION
940 SELECT distinct * FROM table(c_crules1)
941 UNION
942 SELECT distinct * FROM table(c_crules2)
943 UNION
944 SELECT distinct * FROM table(c_crules3);
945
946 new_reevalrule_ids OKC_TBL_NUMBER;
947 rule_ids1 OKC_TBL_NUMBER;
948 rule_ids2 OKC_TBL_NUMBER;
949 rule_ids3 OKC_TBL_NUMBER;
950 clause_rules1 OKC_TBL_NUMBER;
951 clause_rules2 OKC_TBL_NUMBER;
952 clause_rules3 OKC_TBL_NUMBER;
953 ques_rules OKC_TBL_NUMBER;
954 question_ids OKC_TBL_NUMBER;
955 has_clauses VARCHAR2(1) := 'N';
956 rows1 NUMBER := 0;
957 rows2 NUMBER := 0;
958 no_of_questions NUMBER := 0;
959
960 i NUMBER := 1;
961 n NUMBER := 1;
962 k NUMBER := 1;
963 l NUMBER := 1;
964
965 l_api_name CONSTANT VARCHAR2(30) := 'reevaluate_rules';
966 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
967
968 BEGIN
969
970 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
972 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: No. of reeval_rules rule_ids: ' || reeval_rules.count);
973
974 FOR a IN 1 .. reeval_rules.count LOOP
975 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: reeval_rules rule_id: ' || reeval_rules(a)); END LOOP;
976 END IF;
977
978 rule_ids1 := OKC_TBL_NUMBER();
979 rule_ids2 := OKC_TBL_NUMBER();
980 rule_ids3 := OKC_TBL_NUMBER();
981 clause_rules1 := OKC_TBL_NUMBER();
982 clause_rules2 := OKC_TBL_NUMBER();
983 clause_rules3 := OKC_TBL_NUMBER();
984 ques_rules := OKC_TBL_NUMBER();
985 question_ids := OKC_TBL_NUMBER();
986 new_reevalrule_ids := OKC_TBL_NUMBER();
987 rule_ids1.extend(reeval_rules.count);
988 rule_ids2.extend(reeval_rules.count);
989 rule_ids3.extend(reeval_rules.count);
990
991 FOR j IN 1 .. reeval_rules.count LOOP
992 UPDATE okc_xprt_rule_eval_result_t result_tmp
993 SET result = 'Y'
994 WHERE result_tmp.rule_id = reeval_rules(j)
995 AND result_tmp.condition_id IS NULL
996 AND result_tmp.doc_id = p_doc_id
997 AND result_tmp.doc_type = p_doc_type
998 AND nvl(result_tmp.result, '*') <> 'Y'
999 AND ((result_tmp.rule_expr_type = 'ALL' AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') <> 'Y'
1000 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1001 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
1002 OR (result_tmp.rule_expr_type = 'ANY' AND EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') = 'Y'
1003 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1004 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1)))
1005 RETURNING n + 1, result_tmp.rule_id INTO n, rule_ids1(n);
1006 IF SQL%FOUND THEN
1007 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1008 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: No. of rules updated with result = Y based on condition evaluation: ' || (n-1));
1009
1010 FOR a IN 1 .. rule_ids1.count LOOP
1011 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: rule_ids1 rule_id: ' || rule_ids1(a)); END LOOP;
1012 END IF;
1013 continue;
1014 END IF;
1015
1016
1017 UPDATE okc_xprt_rule_eval_result_t result_tmp
1018 SET result = decode(result_tmp.rule_expr_type, 'ANY', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
1019 WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1020 AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
1021 decode(result, NULL, 1, 2)) WHERE rownum = 1),
1022 'ALL', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
1023 WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1024 AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
1025 decode(result, 'N', 1, NULL, 2, 3)) WHERE rownum = 1))
1026 WHERE result_tmp.rule_id = reeval_rules(j)
1027 AND result_tmp.condition_id IS NULL
1028 AND result_tmp.doc_id = p_doc_id
1029 AND result_tmp.doc_type = p_doc_type
1030 AND nvl(result_tmp.result, '*') = 'Y'
1031 AND ((result_tmp.rule_expr_type = 'ALL' AND EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') <> 'Y'
1032 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1033 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
1034 OR (result_tmp.rule_expr_type = 'ANY' AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_eval_result_t WHERE nvl(result, '*') = 'Y'
1035 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1036 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1)))
1037 RETURNING k + 1, result_tmp.rule_id INTO k, rule_ids2(k);
1038 IF SQL%FOUND THEN
1039 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1040 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: No. of rules updated with result based on condition evaluation: ' || (k-1));
1041
1042 FOR a IN 1 .. rule_ids2.count LOOP
1043 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: rule_ids2 rule_id: ' || rule_ids2(a)); END LOOP;
1044 END IF;
1045 continue;
1046 END IF;
1047
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
1049 UPDATE okc_xprt_rule_eval_result_t result_tmp
1050 SET result = decode(result_tmp.rule_expr_type, 'ANY', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
1051 WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1052 AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
1053 decode(result, NULL, 1, 2)) WHERE rownum = 1),
1054 'ALL', (SELECT result FROM (SELECT result FROM okc_xprt_rule_eval_result_t
1055 WHERE rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1056 AND doc_id = p_doc_id AND doc_type = p_doc_type ORDER BY
1057 decode(result, 'N', 1, NULL, 2, 3)) WHERE rownum = 1))
1058 WHERE result_tmp.rule_id = reeval_rules(j)
1059 AND result_tmp.condition_id IS NULL
1060 AND result_tmp.doc_id = p_doc_id
1061 AND result_tmp.doc_type = p_doc_type
1062 AND nvl(result_tmp.result, '*') <> 'Y'
1063 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'
1064 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1065 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
1066 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'
1067 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1068 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
1069 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
1070 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1071 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1))
1072 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'
1073 AND rule_id = reeval_rules(j) AND condition_id IS NOT NULL
1074 AND doc_id = p_doc_id AND doc_type = p_doc_type AND rownum = 1)))
1075 RETURNING l + 1, result_tmp.rule_id INTO l, rule_ids3(l);
1076 IF SQL%FOUND THEN
1077 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: No. of rules updated with result based on condition evaluation: ' || (l-1));
1079
1080 FOR a IN 1 .. rule_ids3.count LOOP
1081 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: rule_ids3 rule_id: ' || rule_ids3(a)); END LOOP;
1082 END IF;
1083 continue;
1084 END IF;
1085 END LOOP;
1086
1087 rule_ids1.trim(reeval_rules.count - (n - 1));
1088 rule_ids2.trim(reeval_rules.count - (k - 1));
1089 rule_ids3.trim(reeval_rules.count - (l - 1));
1090
1091 BEGIN
1092 SELECT 'Y' INTO has_clauses FROM dual
1093 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');
1094 EXCEPTION
1095 WHEN NO_DATA_FOUND THEN
1096 NULL;
1097 END;
1098
1099 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1100 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '150: has_clauses: ' || has_clauses);
1101 END IF;
1102
1103 ---------------------------------------------------------------------------------------------------------------------------------------------------
1104 --Step I: Additional Questions handling:
1105 --Updating the display_flag to 'Y' if the rule_id has any additional questions
1106
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'
1116 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
1117 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');
1118 rows1 := sql%rowcount;
1119
1120 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1121 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '160: No. of additional questions which are getting displayed based on the new rules that are evaluated: ' || rows1);
1122 END IF;
1123 END IF;
1124
1125
1126 --B) rule_ids2--- which have changed their result from Y to N or Y to null
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
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'
1137 AND ques.display_flag <> 'N'
1138 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')
1139 AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_outcomes_act_v d, okc_xprt_rule_eval_result_t t
1140 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
1141 AND to_char(d.object_value_id) = ques.object_code AND d.object_type = 'QUESTION');
1142 IF SQL%FOUND AND rule_ids2.count > 0 THEN
1143 rows2 := sql%rowcount;
1144 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1145 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '170: No. of additional questions which will getting displayed from the UI based on the new rules that are evaluated: ' || rows2);
1146 END IF;
1147 END IF;
1148
1149
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'
1159 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
1160 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')
1161 AND NOT EXISTS (SELECT 1 FROM okc_xprt_rule_outcomes_act_v d, okc_xprt_rule_eval_result_t t
1162 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
1163 AND to_char(d.object_value_id) = ques.object_code AND d.object_type = 'QUESTION')
1164 RETURNING ques.object_code BULK COLLECT INTO question_ids;
1165
1166 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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);
1168
1169 FOR a IN 1 .. question_ids.count LOOP
1170 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '180: question_ids question_id: ' || question_ids(a)); END LOOP;
1171 END IF;
1172
1173 --updating the result of all the conditions to null which contains this additional question
1174 --question_ids will have unique question ids
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
1176 IF question_ids.count > 0 THEN
1177 UPDATE okc_xprt_rule_eval_result_t
1178 SET result = NULL
1179 WHERE object_code IN (SELECT * FROM table(question_ids))
1180 AND doc_id = p_doc_id
1181 AND doc_type = p_doc_type
1182 AND condition_type = 'QUESTION'
1183 RETURNING rule_id BULK COLLECT INTO ques_rules;
1184
1185 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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);
1187
1188 FOR a IN 1 .. ques_rules.count LOOP
1189 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '190: ques_rules rule_id: ' || ques_rules(a)); END LOOP;
1190 END IF;
1191 END IF;
1192 END IF;
1193
1194 --C) rule_ids3--- which have changed their result from null to N or N to null
1195 --updation of additional questions display flag is not required
1196 --reevaluation of rule_ids3 is not needed..
1197 IF rows1 > 0 OR rows2 > 0 THEN
1198 questions_display_changed := 'Y';
1199 END IF;
1200
1201 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1202 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '200: questions_display_changed: ' || questions_display_changed);
1203 END IF;
1204
1205 ---------------------------------------------------------------------------------------------------------------------------------------------------
1206 --Step II: Dependent Clause based conditions evaluation:
1207 --Updating the result of dependent clause based conditions
1208
1209 --A) rule_ids1--- which have changed their result from null to Y or N to Y
1210 --updated dependent clause questions display flag to Y if op IN (IS IN) OR N if op IN (IS_NOT NOT_IN)
1211
1212 IF has_clauses = 'Y' THEN
1213
1214 --A) rule_ids1--- which have changed their result from null to Y or N to Y
1215 --updated dependent clause conditions result to Y if op IN (IS,IN) OR N if op IN (IS_NOT,NOT_IN)
1216
1217 IF rule_ids1.count > 0 THEN
1218 UPDATE okc_xprt_rule_eval_result_t cond
1219 SET result = decode(cond_operator, 'IS', 'Y', 'IN', 'Y', 'N')
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
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);
1230
1231 FOR a IN 1 .. clause_rules1.count LOOP
1232 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '210: clause_rules1 rule_id: ' || clause_rules1(a)); END LOOP;
1233 END IF;
1234 END IF;
1235
1236 --B) rule_ids2--- which have changed their result from Y to N or Y to null
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'))
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')
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;
1256
1257 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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);
1259
1260 FOR a IN 1 .. clause_rules2.count LOOP
1261 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '220: clause_rules2 rule_id: ' || clause_rules2(a)); END LOOP;
1262 END IF;
1263 END IF;
1264
1265 --C) rule_ids3--- which have changed their result from null to N or N to null
1266 --updated dependent clause conditions result by considering all the other rules result
1267 --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
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')
1277 WHERE cond.doc_id = p_doc_id
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)
1287 RETURNING cond.rule_id BULK COLLECT INTO clause_rules3;
1288
1289 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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);
1291
1292 FOR a IN 1 .. clause_rules3.count LOOP
1293 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '230: clause_rules3 rule_id: ' || clause_rules3(a)); END LOOP;
1294 END IF;
1295 END IF;
1296
1297 END IF;
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
1299 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1300
1301 --Step III: Finding dependent rules for all the above changes and reevaluating them
1302 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
1303 OPEN c_dep_rules(clause_rules1, clause_rules2, clause_rules3, ques_rules);
1304 FETCH c_dep_rules BULK COLLECT INTO new_reevalrule_ids;
1305 CLOSE c_dep_rules;
1306 END IF;
1307
1308 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1309 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '240: No. of rules that have to be re-evaluated based on changes done: ' || new_reevalrule_ids.count);
1310
1311 FOR a IN 1 .. new_reevalrule_ids.count LOOP
1312 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '240: new_reevalrule_ids rule_id: ' || new_reevalrule_ids(a)); END LOOP;
1313 END IF;
1314
1315 IF new_reevalrule_ids.count > 0 THEN
1316 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1317 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '250: calling reevaluate_rules method.');
1318 END IF;
1319
1320 reevaluate_rules(new_reevalrule_ids);
1321 END IF;
1322
1323 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '260: Leaving method');
1325 END IF;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1330 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '270: Exception occured: ' || sqlerrm);
1331 END IF;
1332 raise;
1333 END reevaluate_rules;
1334
1335
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
1345 FROM okc_xprt_rule_eval_result_t cond
1346 WHERE doc_id = p_doc_id
1347 AND doc_type = p_doc_type
1348 AND object_code = p_question_id
1349 AND condition_type = 'QUESTION'
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
1359 FROM okc_xprt_rule_eval_result_t cond
1360 WHERE doc_id = p_doc_id
1361 AND doc_type = p_doc_type
1362 AND object_value_code = p_question_id
1363 AND object_value_type = 'QUESTION';
1364
1365 result_tbl result_tbl_type;
1366 rule_ids OKC_TBL_NUMBER;
1367
1368 l_cond_result BOOLEAN;
1369 i NUMBER := 0;
1370
1371 l_api_name CONSTANT VARCHAR2(30) := 'evaluate_user_response';
1372 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1373
1374 BEGIN
1375 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1376 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
1377 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
1378 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
1379 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: template_id : ' || template_id);
1380 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_question_id : ' || p_question_id);
1381 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_response : ' || p_response);
1382 END IF;
1383
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
1393 AND doc_type = p_doc_type;
1394
1395 questions_display_changed := 'N';
1396 rule_ids := OKC_TBL_NUMBER();
1397
1398 IF p_response IS NULL THEN
1399 FOR rule_cond IN c_rule_conditions LOOP
1400 --if both lhs and rhs are questions then atleast one of the response should not be null, then only we will ahve a change in the result of the condition
1401 IF ((rule_cond.condition_type = 'QUESTION' AND rule_cond.object_value_type = 'QUESTION') AND (rule_cond.lhs_response IS NOT NULL OR rule_cond.rhs_response IS NOT NULL)) THEN
1402 i := i + 1;
1403 result_tbl(i).rule_id := rule_cond.rule_id;
1404 result_tbl(i).condition_id := rule_cond.condition_id;
1405 result_tbl(i).result := NULL;
1406 rule_ids.extend(1);
1407 rule_ids(i) := rule_cond.rule_id;
1408 END IF;
1409 --if only one of the rhs or lhs is a question then set the result to N of that condition as the response of the question is null
1410 IF (rule_cond.condition_type <> 'QUESTION' OR rule_cond.object_value_type <> 'QUESTION') THEN
1411 i := i + 1;
1412 result_tbl(i).rule_id := rule_cond.rule_id;
1413 result_tbl(i).condition_id := rule_cond.condition_id;
1414 result_tbl(i).result := NULL;
1415 rule_ids.extend(1);
1416 rule_ids(i) := rule_cond.rule_id;
1417 END IF;
1418 END LOOP;
1419 ELSE
1420 FOR rule_cond IN c_rule_conditions LOOP
1421 IF ((rule_cond.condition_type = 'QUESTION' AND rule_cond.object_value_type = 'QUESTION') AND (rule_cond.lhs_response IS NULL OR rule_cond.rhs_response IS NULL)) THEN
1422 l_cond_result := NULL;
1423 ELSE
1424 l_cond_result := evaluate_condition(rule_cond.condition_id, rule_cond.condition_type, rule_cond.object_code, rule_cond.object_value_type, rule_cond.object_value_code, rule_cond.cond_operator);
1425 END IF;
1426
1427 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1428 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: condition_id : ' || rule_cond.condition_id);
1429 IF l_cond_result IS NULL THEN
1430 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_cond_result : NULL');
1431 ELSIF l_cond_result THEN
1432 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_cond_result : TRUE');
1433 ELSE
1434 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_cond_result : FALSE');
1435 END IF;
1436 END IF;
1437
1438 IF l_cond_result IS NULL AND rule_cond.result IS NOT NULL THEN
1439 i := i + 1;
1440 result_tbl(i).rule_id := rule_cond.rule_id;
1441 result_tbl(i).condition_id := rule_cond.condition_id;
1442 result_tbl(i).result := NULL;
1443 rule_ids.extend(1);
1444 rule_ids(i) := rule_cond.rule_id;
1445 ELSIF l_cond_result = FALSE AND nvl(rule_cond.result, '*') <> 'N' THEN
1446 i := i + 1;
1447 result_tbl(i).rule_id := rule_cond.rule_id;
1448 result_tbl(i).condition_id := rule_cond.condition_id;
1449 result_tbl(i).result := 'N';
1450 rule_ids.extend(1);
1451 rule_ids(i) := rule_cond.rule_id;
1452 ELSIF l_cond_result = TRUE AND nvl(rule_cond.result, '*') <> 'Y' THEN
1453 i := i + 1;
1454 result_tbl(i).rule_id := rule_cond.rule_id;
1455 result_tbl(i).condition_id := rule_cond.condition_id;
1456 result_tbl(i).result := 'Y';
1457 rule_ids.extend(1);
1458 rule_ids(i) := rule_cond.rule_id;
1459 END IF;
1460 END LOOP;
1461 END IF;
1462
1463 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1464 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: : result_tbl count' || result_tbl.count);
1465
1466 FOR a IN 1 .. result_tbl.count LOOP
1467 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: result_tbl rule_id: ' || result_tbl(a).rule_id || ' condition_id: ' || result_tbl(a).condition_id || ' result: ' || result_tbl(a).result); END LOOP;
1468 END IF;
1469
1470 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1471 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: rule_ids count: ' || rule_ids.count);
1472
1473 FOR a IN 1 .. rule_ids.count LOOP
1474 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '130: rule_ids rule_id: ' || rule_ids(a)); END LOOP;
1475 END IF;
1476
1477 FORALL j IN 1 .. result_tbl.count
1478 UPDATE okc_xprt_rule_eval_result_t result_tmp
1479 SET result = result_tbl(j).result
1480 WHERE result_tmp.rule_id = result_tbl(j).rule_id
1481 AND result_tmp.condition_id = result_tbl(j).condition_id
1482 AND result_tmp.doc_id = p_doc_id
1483 AND result_tmp.doc_type = p_doc_type;
1484
1485 IF result_tbl.count > 0 THEN
1486 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1487 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '140: calling reevaluate_rules method.');
1488 END IF;
1489 reevaluate_rules(rule_ids);
1490 END IF;
1491
1492 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1493 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '150: questions_display_changed: ' || questions_display_changed);
1494 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '150: Leaving method');
1495 END IF;
1496
1497 RETURN questions_display_changed;
1498
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '160: Exception occured: ' || sqlerrm);
1503 END IF;
1504 raise;
1505 END evaluate_user_response;
1506
1507 PROCEDURE save_responses(doc_id IN NUMBER, doc_type IN VARCHAR2, p_lock_xprt_yn IN VARCHAR2, x_return_status OUT NOCOPY VARCHAR2) IS
1508
1509 CURSOR c_base_table_dtls IS
1510 SELECT orig_system_reference_code, orig_system_reference_id1
1511 FROM okc_template_usages
1512 WHERE document_type = p_doc_type
1513 AND document_id = p_doc_id;
1514
1515 l_src_document_type okc_template_usages.orig_system_reference_code%TYPE;
1516 l_src_document_id okc_template_usages.orig_system_reference_id1%TYPE;
1517 x_msg_data VARCHAR2(2000);
1518 x_msg_count NUMBER;
1519
1520 l_api_name CONSTANT VARCHAR2(30) := 'save_responses';
1521 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1522
1523 BEGIN
1524 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1525 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
1526 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
1527 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
1528 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_lock_xprt_yn : ' || p_lock_xprt_yn);
1529 END IF;
1530
1531 p_doc_id := doc_id;
1532 p_doc_type := doc_type;
1533
1534 -- Initialize API return status to success
1535 x_return_status := FND_API.G_RET_STS_SUCCESS;
1536
1537 --concurrent mod changes start
1538 -- Read the base table details
1539 OPEN c_base_table_dtls;
1540 FETCH c_base_table_dtls INTO l_src_document_type,l_src_document_id;
1541 CLOSE c_base_table_dtls;
1542
1543 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1544 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_src_document_type: ' || l_src_document_type);
1545 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: l_src_document_id: ' || l_src_document_id);
1546 END IF;
1547
1548 IF p_lock_xprt_yn = 'Y' THEN
1549 IF l_src_document_type IS NOT NULL AND l_src_document_id IS NOT NULL THEN
1550 -- Lock base table.
1551 okc_k_entity_locks_grp.lock_entity
1552 ( p_api_version => 1,
1553 p_init_msg_list => FND_API.G_FALSE ,
1554 p_commit => FND_API.G_FALSE,
1555 p_entity_name => okc_k_entity_locks_grp.G_XPRT_ENTITY,
1556 p_entity_pk1 => To_Char(l_src_document_id),
1557 p_entity_pk2 => To_Char(l_src_document_type),
1558 p_LOCK_BY_DOCUMENT_TYPE => p_doc_type,
1559 p_LOCK_BY_DOCUMENT_ID => p_doc_id,
1560 X_RETURN_STATUS => X_RETURN_STATUS,
1561 X_MSG_COUNT => X_MSG_COUNT,
1562 X_MSG_DATA => X_MSG_DATA
1563 );
1564 --------------------------------------------
1565 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1567 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1568 RAISE FND_API.G_EXC_ERROR ;
1569 END IF;
1570 --------------------------------------------
1571 ELSE
1572 NULL;
1573 -- throw error;
1574 END IF;
1575 END IF; -- p_create_lock_for_xprt = 'Y'
1576 --concurrent mod chanegs end
1577
1578 --deleting the responses and copying the new responses into okc_xprt_doc_ques_response table
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
1588 SET contract_expert_finish_flag = 'N'
1589 WHERE document_id = p_doc_id
1590 AND document_type = p_doc_type;
1591
1592 commit;
1593
1594 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1595 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Commit done');
1596 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '120: Leaving method');
1597 END IF;
1598
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1602 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '130: Exception occured: ' || sqlerrm);
1603 END IF;
1604 raise;
1605 END save_responses;
1606
1607 FUNCTION has_all_questions_answered(doc_id NUMBER, doc_type VARCHAR2) RETURN VARCHAR2 IS
1608
1609 has_all_ques_answered VARCHAR2(1) := 'Y';
1610
1611 l_api_name CONSTANT VARCHAR2(30) := 'has_all_questions_answered';
1612 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1613
1614 BEGIN
1615 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1616 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering method');
1617 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
1618 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
1619 END IF;
1620
1621 p_doc_id := doc_id;
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;
1631 END;
1632
1633 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1634 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: has_all_ques_answered: ' || has_all_ques_answered);
1635 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Leaving method');
1636 END IF;
1637
1638 RETURN has_all_ques_answered;
1639
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1643 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '120: Exception occured: ' || sqlerrm);
1644 END IF;
1645 raise;
1646 END has_all_questions_answered;
1647
1648
1649 PROCEDURE create_xprt_responses_version(doc_id IN NUMBER, doc_type IN VARCHAR2, p_major_version IN NUMBER) IS
1650
1651 l_api_name CONSTANT VARCHAR2(30) := 'create_xprt_responses_version';
1652 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1653
1654 BEGIN
1655
1656 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1657 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering create_xprt_responses_version method');
1658 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
1659 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
1660 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_major_version : ' || p_major_version);
1661 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Saving responses into history table');
1662 END IF;
1663
1664 p_doc_id := doc_id;
1665 p_doc_type := doc_type;
1666
1667 --copying the responses to okc_xprt_doc_ques_response_h
1668 INSERT INTO okc_xprt_doc_ques_response_h (doc_question_response_id, doc_id, doc_type, major_version, question_id, response)
1669 (SELECT doc_question_response_id, p_doc_id, p_doc_type, p_major_version, question_id, response FROM okc_xprt_doc_ques_response
1670 WHERE doc_id = p_doc_id AND doc_type = p_doc_type);
1671
1672 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1673 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: no. of rows copied : ' || sql%rowcount);
1674 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Leaving method');
1675 END IF;
1676
1677 EXCEPTION
1678 WHEN OTHERS THEN
1679 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1680 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '120: Exception occured: ' || sqlerrm);
1681 END IF;
1682 raise;
1683 END create_xprt_responses_version;
1684
1685
1686 PROCEDURE restore_xprt_responses_version(doc_id IN NUMBER, doc_type IN VARCHAR2, p_major_version IN NUMBER) IS
1687
1688 l_api_name CONSTANT VARCHAR2(30) := 'restore_xprt_responses_version';
1689 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1690
1691 BEGIN
1692
1693 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1694 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering restore_xprt_responses_version method');
1695 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
1696 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
1697 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_major_version : ' || p_major_version);
1698 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Restoring responses from history table to the main table');
1699 END IF;
1700
1701 p_doc_id := doc_id;
1702 p_doc_type := doc_type;
1703
1704 --copying the responses to okc_xprt_doc_ques_response from okc_xprt_doc_ques_response_h table
1705 INSERT INTO okc_xprt_doc_ques_response(doc_question_response_id, doc_id, doc_type, question_id, response)
1706 (SELECT doc_question_response_id, p_doc_id, p_doc_type, question_id, response FROM okc_xprt_doc_ques_response_h
1707 WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND major_version = p_major_version);
1708
1709 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1710 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: no. of rows copied : ' || sql%rowcount);
1711 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Leaving restore_xprt_responses_version method');
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1717 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '120: Exception occured: ' || sqlerrm);
1718 END IF;
1719 raise;
1720 END restore_xprt_responses_version;
1721
1722
1723 PROCEDURE delete_xprt_responses_version(doc_id IN NUMBER, doc_type IN VARCHAR2, p_major_version IN NUMBER) IS
1724
1725 l_api_name CONSTANT VARCHAR2(30) := 'delete_xprt_responses_version';
1726 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1727
1728 BEGIN
1729
1730 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1731 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Entering delete_xprt_responses_version method');
1732 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_id : ' || doc_id);
1733 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: doc_type : ' || doc_type);
1734 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: p_major_version : ' || p_major_version);
1735 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '100: Deleting responses from history table');
1736 END IF;
1737
1738 p_doc_id := doc_id;
1739 p_doc_type := doc_type;
1740
1741 --deleting the responses from okc_xprt_doc_ques_response_h history table as the revision is deleted.
1742 DELETE FROM okc_xprt_doc_ques_response_h
1743 WHERE doc_id = p_doc_id AND doc_type = p_doc_type AND major_version = p_major_version;
1744
1745 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1746 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: no. of rows deleted : ' || sql%rowcount);
1747 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, '110: Leaving delete_xprt_responses_version method');
1748 END IF;
1749
1750 EXCEPTION
1751 WHEN OTHERS THEN
1752 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1753 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_module, '120: Exception occured: ' || sqlerrm);
1754 END IF;
1755 raise;
1756 END delete_xprt_responses_version;
1757
1758 END OKC_XPRT_RULES_ENGINE_PVT;
[Home] [Help]