[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_QA_PVT
Source
1 PACKAGE BODY OKC_XPRT_QA_PVT AS
2 /* $Header: OKCVXRULQAB.pls 120.14 2007/03/30 19:32:14 arsundar ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_QA_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
9
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
12 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
13
14 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
15 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
16
17 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
19 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
20
21 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
22 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
23 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
24 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
25
26 G_ERROR_RECORD_TYPE CONSTANT okc_qa_errors_t.error_record_type%TYPE := 'RULE';
27 G_RULE_QA_SEVERITY CONSTANT okc_qa_errors_t.error_severity%TYPE := 'E';
28 G_RULE_QA_SUCCESS CONSTANT okc_qa_errors_t.error_severity%TYPE := 'S';
29 G_CREATION_DATE CONSTANT DATE := SYSDATE;
30
31 --
32 -- Rules QA Codes (lookup_type = OKC_XPRT_RULES_QA_LIST)
33 --
34 G_CHECK_EXPIRED_CLAUSES CONSTANT VARCHAR2(30) := 'CHECK_EXPIRED_CLAUSES';
35 G_CHECK_DRAFT_CLAUSES CONSTANT VARCHAR2(30) := 'CHECK_DRAFT_CLAUSES';
36
37 G_CHECK_INVALID_VSET_QST CONSTANT VARCHAR2(30) := 'CHECK_INVALID_VSET_QST';
38 G_CHECK_INVALID_VSETSQL_QST CONSTANT VARCHAR2(30) := 'CHECK_INVALID_VSETSQL_QST';
39 G_CHECK_INVALID_VSETVAL_QST CONSTANT VARCHAR2(30) := 'CHECK_INVALID_VSETVAL_QST';
40
41
42 G_CHECK_INVALID_VSET_VAR CONSTANT VARCHAR2(30) := 'CHECK_INVALID_VSET_VAR';
43 G_CHECK_INVALID_VSETSQL_VAR CONSTANT VARCHAR2(30) := 'CHECK_INVALID_VSETSQL_VAR';
44 G_CHECK_INVALID_VSETVAL_VAR CONSTANT VARCHAR2(30) := 'CHECK_INVALID_VSETVAL_VAR';
45
46 G_CHECK_CIRCULAR_DEPENDENCY CONSTANT VARCHAR2(30) := 'CHECK_CIRCULAR_DEPENDENCY';
47
48 G_CHECK_INVALID_QUESTION CONSTANT VARCHAR2(30) := 'CHECK_INVALID_QUESTION';
49
50 G_CHECK_RULE_TEMPLATES CONSTANT VARCHAR2(30) := 'CHECK_RULE_TEMPLATES';
51
52 -- Added for UDV with Procedures
53 G_CHECK_NO_PROCEDURE_VAR CONSTANT VARCHAR2(30) := 'CHECK_NO_PROCEDURE_VAR';
54 G_CHECK_INVALID_PROCEDURE_VAR CONSTANT VARCHAR2(30) := 'CHECK_INVALID_PROCEDURE_VAR';
55
56 --
57 -- Rule Validation - Rule with Expired Clauses
58 --
59 G_OKC_EXP_RULE_CLAUSE CONSTANT VARCHAR2(30) := 'OKC_XPRT_EXP_RULE_CLAUSE';
60 G_OKC_EXP_RULE_CLAUSE_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_EXP_RULE_CLAUSE_S';
61 G_OKC_HOLD_RULE_CLAUSE CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_CLAUSE';
62 G_OKC_HOLD_RULE_CLAUSE_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_HOLD_RULE_CLAUSE_S';
63
64 --
65 -- Rule Validation - Rule with Draft Clauses
66 --
67 G_OKC_DFT_RULE_CLAUSE CONSTANT VARCHAR2(30) := 'OKC_XPRT_DFT_RULE_CLAUSE';
68 G_OKC_DFT_RULE_CLAUSE_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_DFT_RULE_CLAUSE_S';
69
70 --
71 -- Rule Validation - Rules with questions having circular dependency
72 --
73 G_OKC_CIRCULAR_DEPENDENCY CONSTANT VARCHAR2(30) := 'OKC_XPRT_CIRCULAR_DEPENDENCY';
74 G_OKC_CIRCULAR_DEPENDENCY_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_CIRCULAR_DEPENDENCY_S';
75
76 --
77 -- Rule Validation - Question with invalid Valueset
78 --
79 G_OKC_INVALID_VSET_QST CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSET_QST';
80 G_OKC_INVALID_VSET_QST_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSET_QST_S';
81
82
83 --
84 -- Rule Validation - Question with invalid Valueset SQL
85 --
86 G_OKC_INVALID_VSETSQL_QST CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETSQL_QST';
87 G_OKC_INVALID_VSETSQL_QST_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETSQL_QST_S';
88
89 --
90 -- Rule Validation - Question with invalid Valueset Value
91 --
92 G_OKC_INVALID_VSETVAL_QST CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETVAL_QST';
93 G_OKC_INVALID_VSETVAL_QST_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETVAL_QST_S';
94
95 --
96 -- Rule Validation - Variable with invalid Valueset
97 --
98 G_OKC_INVALID_VSET_VAR CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSET_VAR';
99 G_OKC_INVALID_VSET_VAR_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSET_VAR_S';
100
101 --
102 -- Rule Validation - Variable with invalid Valueset SQL
103 --
104 G_OKC_INVALID_VSETSQL_VAR CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETSQL_VAR';
105 G_OKC_INVALID_VSETSQL_VAR_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETSQL_VAR_S';
106
107 --
108 -- Rule Validation - Variable with invalid Valueset Value
109 --
110 G_OKC_INVALID_VSETVAL_VAR CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETVAL_VAR';
111 G_OKC_INVALID_VSETVAL_VAR_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_VSETVAL_VAR_S';
112
113 --
114 -- Rule Validation - Rule with question that is disabled
115 --
116 G_OKC_INVALID_QUESTION CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_QUESTION';
117 G_OKC_INVALID_QUESTION_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INVALID_QUESTION_S';
118
119 --
120 -- Rule Validation - Non Org Wide Rule with no templates attached
121 --
122 G_OKC_RULE_TEMPLATES CONSTANT VARCHAR2(30) := 'OKC_XPRT_RULE_TEMPLATES';
123 G_OKC_RULE_TEMPLATES_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_RULE_TEMPLATES_S';
124
125 --
126 -- Rule Validation - Variable with No/Invalid Procedure
127 --
128 G_OKC_NO_PROCEDURE_VAR CONSTANT VARCHAR2(30) := 'OKC_XPRT_NO_PROCEDURE_VAR';
129 G_OKC_NO_PROCEDURE_VAR_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_NO_PROCEDURE_VAR_S';
130 G_OKC_INVALID_PROCEDURE_VAR CONSTANT VARCHAR2(30) := 'OKC_XPRT_INV_PROCEDURE_VAR';
131 G_OKC_INVALID_PROCEDURE_VAR_S CONSTANT VARCHAR2(30) := 'OKC_XPRT_INV_PROCEDURE_VAR_S';
132
133
134
135
136 ---------------------------------------------------
137 -- Private Functions and Procedures
138 ---------------------------------------------------
139 FUNCTION get_qa_code_dtls
140 (
141 p_qa_code IN VARCHAR2
142 )
143 RETURN VARCHAR2 IS
144
145 CURSOR csr_qa_desc IS
146 SELECT meaning
147 FROM fnd_lookups
148 WHERE lookup_type = 'OKC_XPRT_RULES_QA_LIST'
149 AND lookup_code = p_qa_code;
150
151 l_meaning fnd_lookups.meaning%TYPE;
152
153 BEGIN
154 OPEN csr_qa_desc;
155 FETCH csr_qa_desc INTO l_meaning;
156 CLOSE csr_qa_desc;
157
158 RETURN l_meaning;
159
160 END get_qa_code_dtls;
161
162
163 ---------------------------------------------------
164 -- Procedure
165 ---------------------------------------------------
166 PROCEDURE insert_qa_errors_t
167 (
168 p_qa_errors_t_rec IN OKC_QA_ERRORS_T%ROWTYPE,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2
172 ) IS
173
174 l_api_name CONSTANT VARCHAR2(30) := 'insert_qa_errors_t';
175
176
177 BEGIN
178
179 -- start debug log
180 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
181 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
182 G_MODULE||l_api_name,
183 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
184 END IF;
185
186 x_return_status := G_RET_STS_SUCCESS;
187
188 INSERT INTO OKC_QA_ERRORS_T
189 (
190 DOCUMENT_TYPE,
191 DOCUMENT_ID,
192 SEQUENCE_ID,
193 ERROR_RECORD_TYPE,
194 TITLE,
195 ERROR_SEVERITY,
196 QA_CODE,
197 MESSAGE_NAME,
198 PROBLEM_SHORT_DESC,
199 PROBLEM_DETAILS_SHORT,
200 PROBLEM_DETAILS,
201 SUGGESTION,
202 ARTICLE_ID,
203 DELIVERABLE_ID,
204 SECTION_NAME,
205 REFERENCE_COLUMN1,
206 REFERENCE_COLUMN2,
207 REFERENCE_COLUMN3,
208 REFERENCE_COLUMN4,
209 REFERENCE_COLUMN5,
210 CREATION_DATE,
211 ERROR_RECORD_TYPE_NAME,
212 ERROR_SEVERITY_NAME,
213 RULE_ID
214 )
215 VALUES
216 (
217 p_qa_errors_t_rec.DOCUMENT_TYPE,
218 p_qa_errors_t_rec.DOCUMENT_ID,
219 p_qa_errors_t_rec.SEQUENCE_ID,
220 p_qa_errors_t_rec.ERROR_RECORD_TYPE,
221 p_qa_errors_t_rec.TITLE,
222 p_qa_errors_t_rec.ERROR_SEVERITY,
223 p_qa_errors_t_rec.QA_CODE,
224 p_qa_errors_t_rec.MESSAGE_NAME,
225 p_qa_errors_t_rec.PROBLEM_SHORT_DESC,
226 p_qa_errors_t_rec.PROBLEM_DETAILS_SHORT,
227 p_qa_errors_t_rec.PROBLEM_DETAILS,
228 p_qa_errors_t_rec.SUGGESTION,
229 p_qa_errors_t_rec.ARTICLE_ID,
230 p_qa_errors_t_rec.DELIVERABLE_ID,
231 p_qa_errors_t_rec.SECTION_NAME,
232 p_qa_errors_t_rec.REFERENCE_COLUMN1,
233 p_qa_errors_t_rec.REFERENCE_COLUMN2,
234 p_qa_errors_t_rec.REFERENCE_COLUMN3,
235 p_qa_errors_t_rec.REFERENCE_COLUMN4,
236 p_qa_errors_t_rec.REFERENCE_COLUMN5,
237 p_qa_errors_t_rec.CREATION_DATE,
238 p_qa_errors_t_rec.ERROR_RECORD_TYPE_NAME,
239 p_qa_errors_t_rec.ERROR_SEVERITY_NAME,
240 p_qa_errors_t_rec.RULE_ID
241 );
242
243 -- end debug log
244 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
245 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
246 G_MODULE||l_api_name,
247 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
248 END IF;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
253 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
254 G_MODULE||l_api_name,
255 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
256 END IF;
257
258 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
259 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
260 END IF;
261 x_return_status := G_RET_STS_UNEXP_ERROR ;
262 x_msg_data := SQLERRM;
263 x_msg_count := 1;
264
265 END insert_qa_errors_t;
266
267
268 ---------------------------------------------------
269 -- Procedure
270 ---------------------------------------------------
271
272 /*
273 The procedure checks for circular references among questions in rules on
274 each template associated with the rule to check if the rule getting published
275 causes any issue.
276 */
277
278 PROCEDURE check_circular_dependency
279 (
280 p_rule_id IN NUMBER,
281 p_sequence_id IN NUMBER,
282 p_ruleid_tbl IN RuleIdList,
283 x_qa_status OUT NOCOPY VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2
287 ) IS
288
289 l_conc_request_id CONSTANT Number := Fnd_Global.Conc_Request_Id;
290
291 TYPE circ_rec IS REF CURSOR;
292 insert_cursor circ_rec;
293 -- cursor to retrieve rule name
294 CURSOR csr_rule_dtls IS
295 SELECT rule_name
296 FROM okc_xprt_rule_hdrs_all
297 WHERE rule_id = p_rule_id;
298
299 -- cursor to retrieve templates associated to the rule
300 CURSOR template_cursor IS
301 select distinct(temp.template_id) Template_Id, temp.org_id, temp.intent
302 from OKC_XPRT_TEMPLATE_RULES rules, okc_terms_templates_all temp
303 where rules.rule_id = p_rule_id
304 and rules.template_id = temp.template_id
305 and (sysdate between nvl(temp.start_date, sysdate) and nvl(temp.end_date, sysdate ))
306 UNION
307 -- Org Wide Rule templates. Reverted back the changes done for 5019422 by removing
308 -- join to okc_xprt_template_rules
309 SELECT t.template_id, t.org_id, t.intent
310 FROM okc_terms_templates_all t,
311 okc_xprt_rule_hdrs_all r
312 WHERE t.org_id = r.org_id
313 AND t.intent = r.intent
314 AND t.contract_expert_enabled = 'Y'
315 -- AND t.status_code IN ('APPROVED','ON_HOLD')
316 AND (sysdate between nvl(t.start_date, sysdate) and nvl(t.end_date, sysdate ))
317 AND NVL(r.org_wide_flag,'N') = 'Y'
318 AND r.rule_id = p_rule_id;
319
320 -- cursor to check the circular reference
321 CURSOR circular_check IS
322 select condition_question_id,outcome_question_id,level
323 from OKC_XPRT_QUESTION_REF_T
324 connect by prior condition_question_id = outcome_question_id ;
325
326 l_api_name CONSTANT VARCHAR2(30) := 'check_circular_dependency';
327 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
328 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
329
330 l_Template_Id NUMBER;
331 l_Org_Id NUMBER;
332 l_Intent VARCHAR2(1);
333 l_request_rule_ids VARCHAR2(4000) := '(';
334
335 Circular_Ref_Exception Exception;
336 PRAGMA EXCEPTION_INIT(Circular_Ref_Exception, -1436);
337
338 --Modified for Bug 5858915
339 l_sql_string LONG; --VARCHAR2(4000);
340
341
342 condition_question_id VARCHAR2(200);
343 outcome_question_id VARCHAR2(200);
344
345 BEGIN
346
347 -- start debug log
348 IF (Fnd_Log.Level_Procedure >= Fnd_Log.g_Current_Runtime_Level) THEN
349 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
350 g_Module || l_Api_Name,
351 '100: Entered ' || g_Pkg_Name || '.' || l_Api_Name);
352 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
353 g_Module || l_Api_Name,
354 '100: Parameters passed: -----------------------');
355 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
356 g_Module || l_Api_Name,
357 '200: p_rule_Id:' || p_rule_id);
358 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
359 g_Module || l_Api_Name,
360 '200: p_sequence_Id:' || p_sequence_id);
361 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
362 g_Module || l_Api_Name,
363 '100: Parameters passed: -----------------------');
364 END IF;
365
366 -- Get Rule Name
367 OPEN csr_rule_dtls;
368 FETCH csr_rule_dtls INTO l_rule_name;
369 CLOSE csr_rule_dtls;
370
371 -- Build the string with all the rules.
372
373 FOR i IN p_ruleid_tbl.FIRST..p_ruleid_tbl.LAST
374 LOOP
375 l_request_rule_ids := l_request_rule_ids || p_ruleid_tbl(i) ||',';
376 END LOOP;
377 l_request_rule_ids := l_request_rule_ids || p_rule_id || ')';
378 x_Return_Status := g_Ret_Sts_Success;
379 x_qa_status := 'S';
380
381 FOR Template_Rec IN template_cursor LOOP
382
383 l_Template_Id := Template_Rec.Template_Id;
384 l_Org_Id := Template_Rec.Org_Id;
385 l_Intent := Template_Rec.Intent;
386
387 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
389 g_Module || l_Api_Name,
390 '200: Checking for l_Template_Id:' || l_Template_Id);
391 END IF;
392
393 -- insert into the global temp table
394
395 l_sql_string := 'select distinct to_char(cond.object_code) condition_question_id, to_char(object_value_id) outcome_question_id
396 from OKC_XPRT_RULE_CONDITIONS cond, OKC_XPRT_RULE_OUTCOMES outcome
397 where cond.object_type = ''QUESTION''
398 and outcome.object_type = ''QUESTION''
399 and cond.rule_id = outcome.rule_id
400 and cond.rule_id in (
401
402 select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
403 where assoc.rule_id = rules.rule_id
404 and assoc.template_id = ' ||l_Template_Id ||
405 ' and rules.status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
406
407 UNION ALL
408
409 select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
410 where status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
411 and org_wide_flag = ''Y''
412 and org_id = ' ||l_Org_Id ||
413 ' and intent = ''' ||l_Intent|| '''' ||
414
415 ' UNION ALL
416
417 select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
418 where org_wide_flag = ''Y''
419 and org_id = ' ||l_Org_Id||
420 ' and intent = ''' ||l_Intent|| '''' ||
421 ' and rule_id in '||l_request_rule_ids||
422
423 ' UNION ALL
424
425 select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
426 where assoc.rule_id = rules.rule_id
427 and assoc.template_id = ' ||l_Template_Id||
428 ' and rules.rule_id in ' ||l_request_rule_ids||
429
430 ' )
431
432 UNION ALL
433
434 select distinct to_char(cond.object_value_code) condition_question_id, to_char(object_value_id) outcome_question_id
435 from OKC_XPRT_RULE_CONDITIONS cond, OKC_XPRT_RULE_OUTCOMES outcome
436 where cond.object_value_type = ''QUESTION''
437 and outcome.object_type = ''QUESTION''
438 and cond.rule_id = outcome.rule_id
439 and cond.rule_id in (
440 select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
441 where assoc.rule_id = rules.rule_id
442 and assoc.template_id = ' ||l_Template_Id||
443 ' and rules.status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
444
445 UNION ALL
446
447 select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
448 where status_code in (''ACTIVE'',''PENDINGPUB'',''REVISION'',''PENDINGDISABLE'')
449 and org_wide_flag = ''Y''
450 and org_id = ' ||l_Org_Id||
451 ' and intent = ''' ||l_Intent|| '''' ||
452
453 ' UNION ALL
454
455 select distinct rule_id from OKC_XPRT_RULE_HDRS_ALL
456 where org_wide_flag = ''Y''
457 and org_id = ' ||l_Org_Id||
458 ' and intent = ''' ||l_Intent|| '''' ||
459 ' and rule_id in ' ||l_request_rule_ids||
460
461 ' UNION ALL
462
463 select distinct assoc.rule_id from OKC_XPRT_TEMPLATE_RULES assoc, OKC_XPRT_RULE_HDRS_ALL rules
464 where assoc.rule_id = rules.rule_id
465 and assoc.template_id = ' ||l_Template_Id||
466 ' and rules.rule_id in ' ||l_request_rule_ids||
467
468 ' )';
469
470 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
471 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
472 g_Module || l_Api_Name,
473 '205a: Stored the SQL on l_sql_string');
474 END IF;
475
476 OPEN insert_cursor FOR l_sql_string;
477
478 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
479 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
480 g_Module || l_Api_Name,
481 '205b: l_sql_string :' || l_sql_string);
482 END IF;
483
484
485 LOOP
486
487 FETCH insert_cursor INTO condition_question_id, outcome_question_id;
488
489 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
491 g_Module || l_Api_Name,
492 '210: condition_question_id:' || condition_question_id);
493 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
494 g_Module || l_Api_Name,
495 '220: outcome_question_id:' || outcome_question_id);
496 END IF;
497
498 EXIT WHEN insert_cursor%NOTFOUND;
499 insert into OKC_XPRT_QUESTION_REF_T
500 (
501 condition_question_id,
502 outcome_question_id
503 )
504 values
505 (
506 condition_question_id,
507 outcome_question_id
508 );
509 END LOOP;
510
511 CLOSE insert_cursor;
512
513
514 begin
515
516 FOR rec IN circular_check LOOP
517 null;
518 -- exit;
519 END LOOP;
520
521 exception
522
523 WHEN Circular_Ref_Exception THEN
524
525 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
526 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
527 g_Module || l_Api_Name,
528 '300: Failed for l_Template_Id:' || l_Template_Id);
529 END IF;
530
531 delete from OKC_XPRT_QUESTION_REF_T;
532
533 raise Circular_Ref_Exception;
534 end;
535
536
537 delete from OKC_XPRT_QUESTION_REF_T;
538
539 -- For each template, we need to check for circular reference
540 -- Only if all the templates are OK, we return success
541 -- Else we add the rule to the QA error stack
542 END LOOP;
543
544 EXCEPTION
545
546 WHEN Circular_Ref_Exception THEN
547
548 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
549 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
550 l_qa_errors_t_rec.TITLE := l_rule_name;
551 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
552 l_qa_errors_t_rec.QA_CODE := G_CHECK_CIRCULAR_DEPENDENCY;
553 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_CIRCULAR_DEPENDENCY;
554 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_CIRCULAR_DEPENDENCY);
555 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
556 G_OKC_CIRCULAR_DEPENDENCY);
557 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
558 G_OKC_CIRCULAR_DEPENDENCY_S);
559 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
560 l_qa_errors_t_rec.RULE_ID := p_rule_id;
561
562 -- insert into okc_qa_errors_t
563 insert_qa_errors_t
564 (
565 p_qa_errors_t_rec => l_qa_errors_t_rec,
566 x_return_status => x_return_status,
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data
569 );
570
571 -- set QA status to Error
572 x_qa_status := 'E';
573
574
575 -- handle the QA error
576 WHEN OTHERS THEN
577
578 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.g_Current_Runtime_Level) THEN
579 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
580 g_Module || l_Api_Name,
581 '4000: Leaving ' || g_Pkg_Name || '.' || l_Api_Name);
582 END IF;
583
584 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.g_Msg_Lvl_Unexp_Error) THEN
585 Fnd_Msg_Pub.Add_Exc_Msg(g_Pkg_Name, l_Api_Name);
586 END IF;
587 x_Return_Status := g_Ret_Sts_Unexp_Error;
588 x_msg_data := SQLERRM;
589 x_msg_count := 1;
590 Fnd_Msg_Pub.Count_And_Get(p_Encoded => 'F',
591 p_Count => x_Msg_Count,
592 p_Data => x_Msg_Data);
593
594 END check_circular_dependency;
595
596 ---------------------------------------------------
597 -- Procedure
598 ---------------------------------------------------
599 PROCEDURE check_expired_clauses
600 (
601 p_rule_id IN NUMBER,
602 p_sequence_id IN NUMBER,
603 x_qa_status OUT NOCOPY VARCHAR2,
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2
607 ) IS
608
609 CURSOR csr_rule_dtls IS
610 SELECT rule_name
611 FROM okc_xprt_rule_hdrs_all
612 WHERE rule_id = p_rule_id;
613
614 CURSOR csr_clause_options IS
615 SELECT article_id,
616 article_title
617 FROM
618 (
619 -- All DISTINCT Clauses from Conditions
620 SELECT v.object_value_code article_id,
621 a.article_title
622 FROM okc_xprt_rule_cond_vals v,
623 okc_xprt_rule_conditions c,
624 okc_xprt_rule_hdrs_all r,
625 okc_articles_all a
626 WHERE v.rule_condition_id = c.rule_condition_id
627 AND c.rule_id = r.rule_id
628 AND a.article_id = to_number(v.object_value_code) -- Fixed for Bug 4935811. Removed to_char on article_id
629 AND c.object_type = 'CLAUSE'
630 AND r.rule_id = p_rule_id
631 GROUP BY v.object_value_code, a.article_title
632 UNION
633 -- All DISTINCT Clauses from Outcome
634 SELECT to_char(o.object_value_id) article_id,
635 a.article_title
636 FROM okc_xprt_rule_outcomes o,
637 okc_xprt_rule_hdrs_all r,
638 okc_articles_all a
639 WHERE o.rule_id = r.rule_id
640 AND a.article_id = o.object_value_id
641 AND o.object_type = 'CLAUSE'
642 AND r.rule_id = p_rule_id
643 GROUP BY o.object_value_id, a.article_title
644 ) ;
645
646 CURSOR l_check_art_effectivity(p_article_id IN NUMBER) IS
647 SELECT v.article_status
648 FROM okc_article_versions v,
649 okc_articles_all a
650 WHERE a.article_id = v.article_id
651 AND a.article_id = p_article_id
652 AND v.article_status IN ('APPROVED','ON_HOLD')
653 AND sysdate BETWEEN v.start_date AND NVL(v.end_date,sysdate+1);
654
655 CURSOR csr_approved_ver_exists(p_article_id IN NUMBER) IS
656 SELECT 'x'
657 FROM okc_article_versions v,
658 okc_articles_all a
659 WHERE a.article_id = v.article_id
660 AND a.article_id = p_article_id
661 AND v.article_status IN ('APPROVED','ON_HOLD');
662
663
664 l_api_name CONSTANT VARCHAR2(30) := 'check_expired_clauses';
665 l_dummy VARCHAR2(1);
666 l_status VARCHAR2(30);
667 l_approved_exists VARCHAR2(1) :=NULL;
668 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
669 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
670
671 BEGIN
672
673 -- start debug log
674 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
675 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
676 G_MODULE||l_api_name,
677 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
678 END IF;
679
680 x_return_status := G_RET_STS_SUCCESS;
681 x_qa_status := 'S';
682
683 -- Get Rule Name
684 OPEN csr_rule_dtls;
685 FETCH csr_rule_dtls INTO l_rule_name;
686 CLOSE csr_rule_dtls;
687
688 -- Get all Clauses in the current Rule
689 FOR rec_clause_options IN csr_clause_options
690 LOOP
691 -- For each clause check if there exists a version valid as of sysdate
692 OPEN l_check_art_effectivity(p_article_id => rec_clause_options.article_id);
693 FETCH l_check_art_effectivity INTO l_status;
694 IF l_check_art_effectivity%NOTFOUND THEN
695 -- article version not active as of SYSDATE
696 -- Check if the article has a approved version existing
697 l_approved_exists := NULL;
698 OPEN csr_approved_ver_exists(p_article_id => rec_clause_options.article_id);
699 FETCH csr_approved_ver_exists INTO l_approved_exists;
700 IF csr_approved_ver_exists%FOUND THEN
701 -- clause was approved and current version is expired
702 -- No valid clause versions as of sysdate
703 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
704 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
705 l_qa_errors_t_rec.TITLE := l_rule_name;
706 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
707 l_qa_errors_t_rec.QA_CODE := G_CHECK_EXPIRED_CLAUSES;
708 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_EXP_RULE_CLAUSE;
709 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_EXPIRED_CLAUSES);
710 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
711 G_OKC_EXP_RULE_CLAUSE,
712 'CLAUSE',
713 rec_clause_options.article_title
714 );
715 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
716 G_OKC_EXP_RULE_CLAUSE_S,
717 'CLAUSE',
718 rec_clause_options.article_title
719 );
720 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
721 l_qa_errors_t_rec.RULE_ID := p_rule_id;
722
723 -- insert into okc_qa_errors_t
724 insert_qa_errors_t
725 (
726 p_qa_errors_t_rec => l_qa_errors_t_rec,
727 x_return_status => x_return_status,
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data
730 );
731
732 -- set QA status to Error
733 x_qa_status := 'E';
734
735 END IF; -- Clause had a approved version
736 CLOSE csr_approved_ver_exists;
737 Else
738 If l_status = 'ON_HOLD' Then
739 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
740 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
741 l_qa_errors_t_rec.TITLE := l_rule_name;
742 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
743 l_qa_errors_t_rec.QA_CODE := G_CHECK_EXPIRED_CLAUSES;
744 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_HOLD_RULE_CLAUSE;
745 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
746 G_OKC_HOLD_RULE_CLAUSE,
747 'CLAUSE_NAME',
748 rec_clause_options.article_title
749 );
750 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
751 G_OKC_HOLD_RULE_CLAUSE_S,
752 'CLAUSE',
753 rec_clause_options.article_title
754 );
755 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
756 l_qa_errors_t_rec.RULE_ID := p_rule_id;
757
758 -- insert into okc_qa_errors_t
759 insert_qa_errors_t
760 (
761 p_qa_errors_t_rec => l_qa_errors_t_rec,
762 x_return_status => x_return_status,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data
765 );
766
767 -- set QA status to Error
768 x_qa_status := 'E';
769 End IF;
770 END IF; -- no valid clause version
771 CLOSE l_check_art_effectivity;
772
773 END LOOP; -- all clauses in rule
774
775 -- end debug log
776 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
777 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
778 G_MODULE||l_api_name,
779 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
780 END IF;
781
782 EXCEPTION
783 WHEN FND_API.G_EXC_ERROR THEN
784 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
785 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
786 G_MODULE||l_api_name,
787 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
788 END IF;
789
790 x_return_status := G_RET_STS_ERROR ;
791 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
792
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
796 G_MODULE||l_api_name,
797 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
798 END IF;
799
800 x_return_status := G_RET_STS_UNEXP_ERROR ;
801 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
802
803 WHEN OTHERS THEN
804 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
806 G_MODULE||l_api_name,
807 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
808 END IF;
809
810 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
811 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
812 END IF;
813 x_return_status := G_RET_STS_UNEXP_ERROR ;
814 x_msg_data := SQLERRM;
815 x_msg_count := 1;
816 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
817
818 END check_expired_clauses;
819
820 ---------------------------------------------------
821 -- Procedure
822 ---------------------------------------------------
823 PROCEDURE check_draft_clauses
824 (
825 p_rule_id IN NUMBER,
826 p_sequence_id IN NUMBER,
827 x_qa_status OUT NOCOPY VARCHAR2,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_count OUT NOCOPY NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2
831 ) IS
832
833 CURSOR csr_rule_dtls IS
834 SELECT rule_name
835 FROM okc_xprt_rule_hdrs_all
836 WHERE rule_id = p_rule_id;
837
838 CURSOR csr_clause_options IS
839 SELECT article_id,
840 article_title
841 FROM
842 (
843 -- All DISTINCT Clauses from Conditions
844 SELECT v.object_value_code article_id,
845 a.article_title
846 FROM okc_xprt_rule_cond_vals v,
847 okc_xprt_rule_conditions c,
848 okc_xprt_rule_hdrs_all r,
849 okc_articles_all a
850 WHERE v.rule_condition_id = c.rule_condition_id
851 AND c.rule_id = r.rule_id
852 AND to_char(a.article_id) = v.object_value_code
853 AND c.object_type = 'CLAUSE'
854 AND r.rule_id = p_rule_id
855 GROUP BY v.object_value_code, a.article_title
856 UNION
857 -- All DISTINCT Clauses from Outcome
858 SELECT to_char(o.object_value_id) article_id,
859 a.article_title
860 FROM okc_xprt_rule_outcomes o,
861 okc_xprt_rule_hdrs_all r,
862 okc_articles_all a
863 WHERE o.rule_id = r.rule_id
864 AND a.article_id = o.object_value_id
865 AND o.object_type = 'CLAUSE'
866 AND r.rule_id = p_rule_id
867 GROUP BY o.object_value_id, a.article_title
868 ) ;
869
870 CURSOR l_check_art_effectivity(p_article_id IN NUMBER) IS
871 SELECT 'x'
872 FROM okc_article_versions v,
873 okc_articles_all a
874 WHERE a.article_id = v.article_id
875 AND a.article_id = p_article_id
876 AND v.article_status IN ('DRAFT','PENDING_APPROVAL','REJECTED')
877 -- AND sysdate BETWEEN v.start_date AND NVL(v.end_date,sysdate+1)
878 AND NOT EXISTS
879 (
880 SELECT 'x'
881 FROM okc_article_versions v,
882 okc_articles_all a
883 WHERE a.article_id = v.article_id
884 AND a.article_id = p_article_id
885 AND v.article_status IN ('APPROVED','ON_HOLD')
886 )
887 ;
888
889 l_api_name CONSTANT VARCHAR2(30) := 'check_draft_clauses';
890 l_dummy VARCHAR2(1);
891 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
892 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
893
894 BEGIN
895
896 -- start debug log
897 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
899 G_MODULE||l_api_name,
900 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
901 END IF;
902
903 x_return_status := G_RET_STS_SUCCESS;
904 x_qa_status := 'S';
905
906 -- Get Rule Name
907 OPEN csr_rule_dtls;
908 FETCH csr_rule_dtls INTO l_rule_name;
909 CLOSE csr_rule_dtls;
910
911 -- Get all Clauses in the current Rule
912 FOR rec_clause_options IN csr_clause_options
913 LOOP
914 -- For each clause check if there exists a version valid as of sysdate
915 OPEN l_check_art_effectivity(p_article_id => rec_clause_options.article_id);
916 FETCH l_check_art_effectivity INTO l_dummy;
917 IF l_check_art_effectivity%FOUND THEN
918 -- Draft Version and no approved version exists
919 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
920 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
921 l_qa_errors_t_rec.TITLE := l_rule_name;
922 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
923 l_qa_errors_t_rec.QA_CODE := G_check_draft_clauses;
924 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_DFT_RULE_CLAUSE;
925 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_DRAFT_CLAUSES);
926 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
927 G_OKC_DFT_RULE_CLAUSE,
928 'CLAUSE',
929 rec_clause_options.article_title
930 );
931 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
932 G_OKC_DFT_RULE_CLAUSE_S,
933 'CLAUSE',
934 rec_clause_options.article_title
935 );
936 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
937 l_qa_errors_t_rec.RULE_ID := p_rule_id;
938
939 -- insert into okc_qa_errors_t
940 insert_qa_errors_t
941 (
942 p_qa_errors_t_rec => l_qa_errors_t_rec,
943 x_return_status => x_return_status,
944 x_msg_count => x_msg_count,
945 x_msg_data => x_msg_data
946 );
947
948 -- set QA status to Error
949 x_qa_status := 'E';
950
951 END IF; -- no valid clause version
952 CLOSE l_check_art_effectivity;
953
954 END LOOP; -- all clauses in rule
955
956 -- end debug log
957 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
958 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
959 G_MODULE||l_api_name,
960 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
961 END IF;
962
963 EXCEPTION
964 WHEN FND_API.G_EXC_ERROR THEN
965 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
966 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
967 G_MODULE||l_api_name,
968 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
969 END IF;
970
971 x_return_status := G_RET_STS_ERROR ;
972 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
973
974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
975 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
976 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
977 G_MODULE||l_api_name,
978 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
979 END IF;
980
981 x_return_status := G_RET_STS_UNEXP_ERROR ;
982 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
983
984 WHEN OTHERS THEN
985 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
986 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
987 G_MODULE||l_api_name,
988 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
989 END IF;
990
991 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
992 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
993 END IF;
994 x_return_status := G_RET_STS_UNEXP_ERROR ;
995 x_msg_data := SQLERRM;
996 x_msg_count := 1;
997 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
998
999 END check_draft_clauses;
1000
1001 ---------------------------------------------------
1002 -- Procedure
1003 ---------------------------------------------------
1004 PROCEDURE check_invalid_vset_qst
1005 (
1006 p_rule_id IN NUMBER,
1007 p_sequence_id IN NUMBER,
1008 x_qa_status OUT NOCOPY VARCHAR2,
1009 x_return_status OUT NOCOPY VARCHAR2,
1010 x_msg_count OUT NOCOPY NUMBER,
1011 x_msg_data OUT NOCOPY VARCHAR2
1012 ) IS
1013
1014 CURSOR csr_rule_dtls IS
1015 SELECT rule_name
1016 FROM okc_xprt_rule_hdrs_all
1017 WHERE rule_id = p_rule_id;
1018
1019 -- List of questions used in rule
1020 CURSOR csr_rule_questions IS
1021 -- Questions in Conditions
1022 SELECT DISTINCT q.question_name question_name,
1023 c.object_value_set_name value_set_name,
1024 q.question_id question_id
1025 FROM okc_xprt_rule_conditions c,
1026 okc_xprt_questions_vl q
1027 WHERE q.question_id = c.object_code
1028 AND c.object_type='QUESTION'
1029 AND c.object_code_datatype IN ('L','B')
1030 AND c.rule_id = p_rule_id
1031 UNION
1032 -- Questions in Outcome
1033 SELECT DISTINCT q.question_name question_name,
1034 q.value_set_name value_set_name,
1035 q.question_id question_id
1036 FROM okc_xprt_rule_outcomes o,
1037 okc_xprt_questions_vl q
1038 WHERE q.question_id = o.object_value_id
1039 AND o.object_type='QUESTION'
1040 AND q.question_datatype IN ('L','B')
1041 AND o.rule_id = p_rule_id;
1042
1043 -- List of Values used in Rule for a Question
1044 CURSOR csr_rule_question_values(p_question_id IN NUMBER) IS
1045 SELECT v.object_value_code question_value
1046 FROM okc_xprt_rule_conditions c,
1047 okc_xprt_rule_cond_vals v
1048 WHERE c.rule_condition_id = v.rule_condition_id
1049 AND c.object_type = 'QUESTION'
1050 AND c.object_code_datatype IN ('L','B')
1051 AND c.rule_id = p_rule_id
1052 AND c.object_code = to_char(p_question_id);
1053
1054 CURSOR csr_value_set_dtls(p_value_set_name IN VARCHAR2) IS
1055 SELECT validation_type,
1056 flex_value_set_id
1057 FROM FND_FLEX_VALUE_SETS
1058 WHERE flex_value_set_name = p_value_set_name;
1059
1060 -- Create Dynamic sql for the valueset for Table
1061 CURSOR csr_value_set_tab(p_value_set_id IN NUMBER) IS
1062 SELECT application_table_name,
1063 value_column_name,
1064 id_column_name,
1065 additional_where_clause
1066 FROM fnd_flex_validation_tables
1067 WHERE flex_value_set_id = p_value_set_id;
1068
1069 -- SQL for Valueset type Independent
1070 CURSOR csr_value_set_ind(p_flex_value_id IN NUMBER) IS
1071 SELECT 'x'
1072 FROM fnd_flex_values_vl
1073 WHERE flex_value_id = p_flex_value_id
1074 AND enabled_flag = 'Y'
1075 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE+1);
1076
1077
1078 TYPE NameList IS TABLE OF fnd_flex_validation_tables.value_column_name%TYPE INDEX BY BINARY_INTEGER;
1079 TYPE IdList IS TABLE OF fnd_flex_validation_tables.id_column_name%TYPE INDEX BY BINARY_INTEGER;
1080
1081 l_api_name CONSTANT VARCHAR2(30) := 'check_invalid_vset_qst';
1082 l_validation_type FND_FLEX_VALUE_SETS.validation_type%TYPE;
1083 l_value_set_id FND_FLEX_VALUE_SETS.flex_value_set_id%TYPE;
1084 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
1085 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
1086
1087 l_table_name fnd_flex_validation_tables.application_table_name%TYPE;
1088 l_name_col fnd_flex_validation_tables.value_column_name%TYPE;
1089 l_id_col fnd_flex_validation_tables.id_column_name%TYPE;
1090 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
1091 l_sql_stmt LONG;
1092 l_error_message VARCHAR2(4000);
1093 l_found VARCHAR2(1);
1094 l_dummy VARCHAR2(1);
1095
1096 NameList_tbl NameList;
1097 IdList_tbl IdList;
1098
1099 TYPE cur_typ IS REF CURSOR;
1100 c_cursor cur_typ;
1101
1102 i number;
1103
1104 tempName fnd_flex_validation_tables.value_column_name%TYPE ;
1105 tempId fnd_flex_validation_tables.id_column_name%TYPE ;
1106
1107
1108 BEGIN
1109
1110 -- start debug log
1111 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1113 G_MODULE||l_api_name,
1114 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1115 END IF;
1116
1117 x_return_status := G_RET_STS_SUCCESS;
1118 x_qa_status := 'S';
1119
1120 -- Get Rule Name
1121 OPEN csr_rule_dtls;
1122 FETCH csr_rule_dtls INTO l_rule_name;
1123 CLOSE csr_rule_dtls;
1124
1125 -- Get all questions in the current Rule
1126 FOR rec_rule_questions IN csr_rule_questions
1127 LOOP
1128 OPEN csr_value_set_dtls(p_value_set_name => rec_rule_questions.value_set_name);
1129 FETCH csr_value_set_dtls INTO l_validation_type,l_value_set_id;
1130 IF csr_value_set_dtls%NOTFOUND THEN
1131 -- Value Set deleted
1132 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1133 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1134 l_qa_errors_t_rec.TITLE := l_rule_name;
1135 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1136 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSET_QST;
1137 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSET_QST;
1138 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSET_QST);
1139 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1140 G_OKC_INVALID_VSET_QST,
1141 'QUESTION',
1142 rec_rule_questions.question_name
1143 );
1144 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1145 G_OKC_INVALID_VSET_QST_S,
1146 'QUESTION',
1147 rec_rule_questions.question_name
1148 );
1149 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1150 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1151
1152 -- insert into okc_qa_errors_t
1153 insert_qa_errors_t
1154 (
1155 p_qa_errors_t_rec => l_qa_errors_t_rec,
1156 x_return_status => x_return_status,
1157 x_msg_count => x_msg_count,
1158 x_msg_data => x_msg_data
1159 );
1160
1161 -- set QA status to Error
1162 x_qa_status := 'E';
1163
1164 CLOSE csr_value_set_dtls;
1165 RETURN;
1166 END IF; -- valueset deleted
1167
1168 -- If Validation Type is table then check the dynamic sql is valid
1169 IF l_validation_type = 'F' THEN
1170
1171 -- Valueset is Table
1172
1173 -- Build the dynamic SQL for the valueset
1174 OPEN csr_value_set_tab(p_value_set_id => l_value_set_id);
1175 FETCH csr_value_set_tab INTO l_table_name, l_name_col, l_id_col, l_additional_where_clause;
1176 CLOSE csr_value_set_tab;
1177
1178 l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
1179 ' FROM '||l_table_name||' '||
1180 l_additional_where_clause ;
1181
1182 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1183 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1184 G_MODULE||l_api_name,
1185 '150: l_table_name '||l_table_name);
1186 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1187 G_MODULE||l_api_name,
1188 '150: l_name_col '||l_name_col);
1189 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1190 G_MODULE||l_api_name,
1191 '150: l_id_col '||l_id_col);
1192 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1193 G_MODULE||l_api_name,
1194 '150: l_additional_where_clause '||l_additional_where_clause);
1195 END IF;
1196 -- execute the dynamic sql
1197 BEGIN
1198 -- EXECUTE IMMEDIATE l_sql_stmt
1199 -- BULK COLLECT INTO NameList_tbl, IdList_tbl ;
1200 i:=0;
1201 OPEN c_cursor FOR l_sql_stmt;
1202 LOOP
1203 FETCH c_cursor INTO tempName, tempId;
1204 EXIT WHEN c_cursor%NOTFOUND;
1205
1206 -- process row here
1207 NameList_tbl(i) := tempName;
1208 IdList_tbl(i) := tempId;
1209 i:=i+1;
1210 END LOOP;
1211 CLOSE c_cursor;
1212
1213
1214
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 -- SQL is Invalid
1218 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1219 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1220 l_qa_errors_t_rec.TITLE := l_rule_name;
1221 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1222 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETSQL_QST;
1223 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETSQL_QST;
1224 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETSQL_QST);
1225 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1226 G_OKC_INVALID_VSETSQL_QST,
1227 'QUESTION',
1228 rec_rule_questions.question_name
1229 );
1230 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1231 G_OKC_INVALID_VSETSQL_QST_S,
1232 'QUESTION',
1233 rec_rule_questions.question_name
1234 );
1235 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1236 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1237
1238 -- insert into okc_qa_errors_t
1239 insert_qa_errors_t
1240 (
1241 p_qa_errors_t_rec => l_qa_errors_t_rec,
1242 x_return_status => x_return_status,
1243 x_msg_count => x_msg_count,
1244 x_msg_data => x_msg_data
1245 );
1246
1247 -- set QA status to Error
1248 x_qa_status := 'E';
1249 CLOSE csr_value_set_dtls;
1250 RETURN;
1251 END; -- Valueset Type F and SQL
1252
1253 -- SQL Is Valid, check if the Value used in Rule still exists
1254 FOR rec_rule_question_values IN csr_rule_question_values(p_question_id => rec_rule_questions.question_id)
1255 LOOP
1256 -- check if value exists
1257 l_found := 'N';
1258 FOR i IN NVL(IdList_tbl.FIRST,0)..NVL(IdList_tbl.LAST,-1)
1259 LOOP
1260 IF IdList_tbl(i) = rec_rule_question_values.question_value THEN
1261 -- found value
1262 l_found := 'Y';
1263 END IF; -- check if the value matches
1264 END LOOP; -- for all ids
1265 -- check if question value was found
1266 IF l_found = 'N' THEN
1267 -- question value not in value set sql
1268 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1269 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1270 l_qa_errors_t_rec.TITLE := l_rule_name;
1271 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1272 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETVAL_QST;
1273 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETVAL_QST;
1274 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETVAL_QST);
1275 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1276 G_OKC_INVALID_VSETVAL_QST,
1277 'QUESTION',
1278 rec_rule_questions.question_name,
1279 'VALUE_SET_NAME',
1280 rec_rule_questions.value_set_name
1281 );
1282 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1283 G_OKC_INVALID_VSETVAL_QST_S,
1284 'QUESTION',
1285 rec_rule_questions.question_name
1286 );
1287 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1288 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1289
1290 -- insert into okc_qa_errors_t
1291 insert_qa_errors_t
1292 (
1293 p_qa_errors_t_rec => l_qa_errors_t_rec,
1294 x_return_status => x_return_status,
1295 x_msg_count => x_msg_count,
1296 x_msg_data => x_msg_data
1297 );
1298
1299 -- set QA status to Error
1300 x_qa_status := 'E';
1301 CLOSE csr_value_set_dtls;
1302 RETURN;
1303 END IF; -- question value was not found
1304
1305 END LOOP; -- for each value for the question type Table
1306
1307
1308
1309 END IF; -- validation_type is 'F'
1310
1311 -- csr_value_set_ind(p_flex_value_id => rec_rule_question_values.question_value)
1312 IF l_validation_type = 'I' THEN
1313 FOR rec_rule_question_values IN csr_rule_question_values(p_question_id => rec_rule_questions.question_id)
1314 LOOP
1315 -- check if value exists
1316 OPEN csr_value_set_ind(p_flex_value_id => rec_rule_question_values.question_value);
1317 FETCH csr_value_set_ind INTO l_dummy;
1318 IF csr_value_set_ind%NOTFOUND THEN
1319 -- question value not in value set independent value set
1320 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1321 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1322 l_qa_errors_t_rec.TITLE := l_rule_name;
1323 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1324 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETVAL_QST;
1325 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETVAL_QST;
1326 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETVAL_QST);
1327 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1328 G_OKC_INVALID_VSETVAL_QST,
1329 'QUESTION',
1330 rec_rule_questions.question_name,
1331 'VALUE_SET_NAME',
1332 rec_rule_questions.value_set_name
1333 );
1334 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1335 G_OKC_INVALID_VSETVAL_QST_S,
1336 'QUESTION',
1337 rec_rule_questions.question_name
1338 );
1339 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1340 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1341
1342 -- insert into okc_qa_errors_t
1343 insert_qa_errors_t
1344 (
1345 p_qa_errors_t_rec => l_qa_errors_t_rec,
1346 x_return_status => x_return_status,
1347 x_msg_count => x_msg_count,
1348 x_msg_data => x_msg_data
1349 );
1350
1351 -- set QA status to Error
1352 x_qa_status := 'E';
1353 CLOSE csr_value_set_ind;
1354 CLOSE csr_value_set_dtls;
1355 RETURN;
1356 END IF; -- question value not exists
1357 CLOSE csr_value_set_ind;
1358
1359
1360 END LOOP; -- for each value for the question type Independent
1361
1362 END IF; -- validation_type is 'I'
1363
1364
1365
1366
1367
1368 CLOSE csr_value_set_dtls;
1369
1370 END LOOP; -- all questions in rule
1371
1372 -- end debug log
1373 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1374 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1375 G_MODULE||l_api_name,
1376 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1377 END IF;
1378
1379 EXCEPTION
1380 WHEN FND_API.G_EXC_ERROR THEN
1381 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1382 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1383 G_MODULE||l_api_name,
1384 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1385 END IF;
1386
1387 x_return_status := G_RET_STS_ERROR ;
1388 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1389
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1392 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1393 G_MODULE||l_api_name,
1394 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1395 END IF;
1396
1397 x_return_status := G_RET_STS_UNEXP_ERROR ;
1398 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1399
1400 WHEN OTHERS THEN
1401 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1402 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1403 G_MODULE||l_api_name,
1404 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1405 END IF;
1406
1407 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1408 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1409 END IF;
1410 x_return_status := G_RET_STS_UNEXP_ERROR ;
1411 x_msg_data := SQLERRM;
1412 x_msg_count := 1;
1413 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1414
1415 END check_invalid_vset_qst;
1416
1417
1418 ---------------------------------------------------
1419 -- Procedure
1420 ---------------------------------------------------
1421 PROCEDURE check_invalid_vset_var
1422 (
1423 p_rule_id IN NUMBER,
1424 p_sequence_id IN NUMBER,
1425 x_qa_status OUT NOCOPY VARCHAR2,
1426 x_return_status OUT NOCOPY VARCHAR2,
1427 x_msg_count OUT NOCOPY NUMBER,
1428 x_msg_data OUT NOCOPY VARCHAR2
1429 ) IS
1430
1431 CURSOR csr_rule_dtls IS
1432 SELECT rule_name
1433 FROM okc_xprt_rule_hdrs_all
1434 WHERE rule_id = p_rule_id;
1435
1436 -- List of system variables in Rule
1437 CURSOR csr_rule_variables IS
1438 SELECT DISTINCT okc_xprt_util_pvt.get_object_name(c.OBJECT_TYPE,c.OBJECT_CODE) variable_name,
1439 c.object_code variable_code,
1440 c.object_value_set_name value_set_name
1441 FROM okc_xprt_rule_conditions c
1442 WHERE c.rule_id = p_rule_id
1443 AND c.object_type = 'VARIABLE'
1444 AND c.object_code_datatype = 'V';
1445
1446 -- List of Values used in Rule for a Variable
1447 CURSOR csr_rule_variable_values(p_variable_name IN VARCHAR2) IS
1448 SELECT v.object_value_code variable_value
1449 FROM okc_xprt_rule_conditions c,
1450 okc_xprt_rule_cond_vals v
1451 WHERE c.rule_condition_id = v.rule_condition_id
1452 AND c.object_type = 'VARIABLE'
1453 AND c.object_code_datatype = 'V'
1454 AND c.rule_id = p_rule_id
1455 AND c.object_code = p_variable_name;
1456
1457 CURSOR csr_value_set_dtls(p_value_set_name IN VARCHAR2) IS
1458 SELECT validation_type,
1459 flex_value_set_id
1460 FROM FND_FLEX_VALUE_SETS
1461 WHERE flex_value_set_name = p_value_set_name;
1462
1463 -- Create Dynamic sql for the valueset for Table
1464 CURSOR csr_value_set_tab(p_value_set_id IN NUMBER) IS
1465 SELECT application_table_name,
1466 value_column_name,
1467 id_column_name,
1468 additional_where_clause
1469 FROM fnd_flex_validation_tables
1470 WHERE flex_value_set_id = p_value_set_id;
1471
1472 -- SQL for Valueset type Independent
1473 CURSOR csr_value_set_ind(p_flex_value_id IN NUMBER) IS
1474 SELECT 'x'
1475 FROM fnd_flex_values_vl
1476 WHERE flex_value_id = p_flex_value_id
1477 AND enabled_flag = 'Y'
1478 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE+1);
1479
1480 --Bug 5721543 New cursor for checking Item value against MTL_SYSTEM_ITEMS_VL
1481 CURSOR csr_sell_item_exists(p_concatenated_segments VARCHAR2) IS
1482 select 'X'
1483 from MTL_SYSTEM_ITEMS_VL
1484 where organization_id =
1485 TO_NUMBER(oe_sys_parameters.value('MASTER_ORGANIZATION_ID', to_number(fnd_profile.value('ORG_ID'))))
1486 AND (bom_item_type = 1 OR bom_item_type = 4)
1487 AND vendor_warranty_flag = 'N'
1488 AND primary_uom_code <> 'ENR'
1489 AND concatenated_segments = p_concatenated_segments
1490 order by 1;
1491
1492
1493 --Bug 4691106 replaced fnd_flex_validation_tables.value_column_name%TYPE with VARCHAR2(1000)
1494 TYPE NameList IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
1495 TYPE IdList IS TABLE OF fnd_flex_validation_tables.id_column_name%TYPE INDEX BY BINARY_INTEGER;
1496
1497 l_api_name CONSTANT VARCHAR2(30) := 'check_invalid_vset_var';
1498 l_validation_type FND_FLEX_VALUE_SETS.validation_type%TYPE;
1499 l_value_set_id FND_FLEX_VALUE_SETS.flex_value_set_id%TYPE;
1500 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
1501 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
1502
1503 l_table_name fnd_flex_validation_tables.application_table_name%TYPE;
1504 l_name_col fnd_flex_validation_tables.value_column_name%TYPE;
1505 l_id_col fnd_flex_validation_tables.id_column_name%TYPE;
1506 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
1507 l_sql_stmt LONG;
1508 l_error_message VARCHAR2(4000);
1509 l_found VARCHAR2(1);
1510 l_dummy VARCHAR2(1);
1511
1512 NameList_tbl NameList;
1513 IdList_tbl IdList;
1514
1515 TYPE cur_typ IS REF CURSOR;
1516 c_cursor cur_typ;
1517
1518 i number;
1519
1520 --Bug 4691106 replaced tempName fnd_flex_validation_tables.value_column_name%TYPE ; with below stmt
1521 tempName VARCHAR2(1000) ;
1522 tempId fnd_flex_validation_tables.id_column_name%TYPE ;
1523
1524 BEGIN
1525
1526 -- start debug log
1527 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1528 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1529 G_MODULE||l_api_name,
1530 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1531 END IF;
1532
1533 x_return_status := G_RET_STS_SUCCESS;
1534 x_qa_status := 'S';
1535
1536 -- Get Rule Name
1537 OPEN csr_rule_dtls;
1538 FETCH csr_rule_dtls INTO l_rule_name;
1539 CLOSE csr_rule_dtls;
1540
1541 -- Get all Variables in the current Rule
1542 FOR rec_rule_variables IN csr_rule_variables
1543 LOOP
1544 OPEN csr_value_set_dtls(p_value_set_name => rec_rule_variables.value_set_name);
1545 FETCH csr_value_set_dtls INTO l_validation_type,l_value_set_id;
1546 IF csr_value_set_dtls%NOTFOUND THEN
1547 -- Value Set deleted
1548 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1549 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1550 l_qa_errors_t_rec.TITLE := l_rule_name;
1551 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1552 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSET_VAR;
1553 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSET_VAR;
1554 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_check_invalid_vset_var);
1555 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1556 G_OKC_INVALID_VSET_VAR,
1557 'VARIABLE',
1558 rec_rule_variables.variable_name,
1559 'VALUE_SET_NAME',
1560 rec_rule_variables.value_set_name
1561 );
1562 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1563 G_OKC_INVALID_VSET_VAR_S,
1564 'VARIABLE',
1565 rec_rule_variables.variable_name
1566 );
1567 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1568 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1569
1570 -- insert into okc_qa_errors_t
1571 insert_qa_errors_t
1572 (
1573 p_qa_errors_t_rec => l_qa_errors_t_rec,
1574 x_return_status => x_return_status,
1575 x_msg_count => x_msg_count,
1576 x_msg_data => x_msg_data
1577 );
1578
1579 -- set QA status to Error
1580 x_qa_status := 'E';
1581
1582 CLOSE csr_value_set_dtls;
1583 RETURN;
1584 END IF; -- valueset deleted
1585
1586 -- Begin: Fix for Bug 5721543
1587 -- If Variable code is OKC$S_ITEM then use the special cursor to validate the item
1588 -- prsence in the Item value set query, Else follow the regular steps of verifying query, check the value
1589
1590 IF rec_rule_variables.variable_code = 'OKC$S_ITEM' THEN
1591 -- Add Cursor for getting the Item value used in Rule
1592 FOR rec_rule_variable_values IN csr_rule_variable_values(rec_rule_variables.variable_code)
1593 LOOP
1594 -- Check if the Item exists in the Item value set
1595 OPEN csr_sell_item_exists(rec_rule_variable_values.variable_value);
1596 FETCH csr_sell_item_exists INTO l_dummy;
1597 IF csr_sell_item_exists%NOTFOUND THEN
1598 -- Add Value not found QA failure
1599 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1600 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1601 l_qa_errors_t_rec.TITLE := l_rule_name;
1602 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1603 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETVAL_VAR;
1604 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETVAL_VAR;
1605 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETVAL_VAR);
1606 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1607 G_OKC_INVALID_VSETVAL_VAR,
1608 'VARIABLE',
1609 rec_rule_variables.variable_name,
1610 'VAR_VALUE',
1611 rec_rule_variable_values.variable_value,
1612 'VALUE_SET_NAME',
1613 rec_rule_variables.value_set_name
1614 );
1615 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1616 G_OKC_INVALID_VSETVAL_VAR_S,
1617 'VARIABLE',
1618 rec_rule_variables.variable_name
1619 );
1620 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1621 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1622
1623 -- insert into okc_qa_errors_t
1624 insert_qa_errors_t
1625 (
1626 p_qa_errors_t_rec => l_qa_errors_t_rec,
1627 x_return_status => x_return_status,
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data
1630 );
1631
1632 -- set QA status to Error
1633 x_qa_status := 'E';
1634 RETURN;
1635 END IF;
1636 CLOSE csr_sell_item_exists;
1637 END LOOP;
1638 ELSE -- Added for Bug 5721543
1639 -- Variable code in Rule is not OKC$S_ITEM
1640 -- Follow regular steps, Check Value set query, check rule value against Value set values
1641
1642
1643 -- If Validation Type is table then check the dynamic sql is valid
1644 IF l_validation_type = 'F' THEN
1645
1646 -- Valueset is Table
1647
1648 -- Build the dynamic SQL for the valueset
1649 OPEN csr_value_set_tab(p_value_set_id => l_value_set_id);
1650 FETCH csr_value_set_tab INTO l_table_name, l_name_col, l_id_col, l_additional_where_clause;
1651 CLOSE csr_value_set_tab;
1652
1653 l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
1654 ' FROM '||l_table_name||' '||
1655 l_additional_where_clause ;
1656
1657 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1658 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1659 G_MODULE||l_api_name,
1660 '150: l_table_name '||l_table_name);
1661 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1662 G_MODULE||l_api_name,
1663 '150: l_name_col '||l_name_col);
1664 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1665 G_MODULE||l_api_name,
1666 '150: l_id_col '||l_id_col);
1667 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1668 G_MODULE||l_api_name,
1669 '150: l_additional_where_clause '||l_additional_where_clause);
1670 END IF;
1671
1672 -- put sql in log
1673 fnd_file.put_line(FND_FILE.LOG,' ');
1674 fnd_file.put_line(FND_FILE.LOG,'Variable : '||rec_rule_variables.variable_name);
1675 fnd_file.put_line(FND_FILE.LOG,'Dynamic SQL for Valueset: '||rec_rule_variables.value_set_name);
1676 fnd_file.put_line(FND_FILE.LOG,'SELECT '||l_name_col||' , '||l_id_col);
1677 fnd_file.put_line(FND_FILE.LOG,'FROM '||l_table_name||' ');
1678 fnd_file.put_line(FND_FILE.LOG,l_additional_where_clause);
1679 fnd_file.put_line(FND_FILE.LOG,' ');
1680
1681 -- execute the dynamic sql
1682 BEGIN
1683 -- EXECUTE IMMEDIATE l_sql_stmt
1684 -- BULK COLLECT INTO NameList_tbl, IdList_tbl ;
1685
1686 i:=0;
1687 OPEN c_cursor FOR l_sql_stmt;
1688 LOOP
1689 FETCH c_cursor INTO tempName, tempId;
1690 EXIT WHEN c_cursor%NOTFOUND;
1691
1692 -- process row here
1693 NameList_tbl(i) := tempName;
1694 IdList_tbl(i) := tempId;
1695 i:=i+1;
1696 END LOOP;
1697 CLOSE c_cursor;
1698
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 -- SQL is Invalid
1702 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1703 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1704 l_qa_errors_t_rec.TITLE := l_rule_name;
1705 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1706 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETSQL_VAR;
1707 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETSQL_VAR;
1708 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETSQL_VAR);
1709 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1710 G_OKC_INVALID_VSETSQL_VAR,
1711 'VARIABLE',
1712 rec_rule_variables.variable_name,
1713 'VALUE_SET_NAME',
1714 rec_rule_variables.value_set_name
1715 );
1716 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1717 G_OKC_INVALID_VSETSQL_VAR_S,
1718 'VARIABLE',
1719 rec_rule_variables.variable_name
1720 );
1721 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1722 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1723
1724 -- insert into okc_qa_errors_t
1725 insert_qa_errors_t
1726 (
1727 p_qa_errors_t_rec => l_qa_errors_t_rec,
1728 x_return_status => x_return_status,
1729 x_msg_count => x_msg_count,
1730 x_msg_data => x_msg_data
1731 );
1732
1733 -- set QA status to Error
1734 x_qa_status := 'E';
1735 CLOSE csr_value_set_dtls;
1736 RETURN;
1737 END; -- Valueset Type F and SQL
1738
1739 -- SQL Is Valid, check if the Value used in Rule still exists
1740 FOR rec_rule_variable_values IN csr_rule_variable_values(p_variable_name => rec_rule_variables.variable_code)
1741 LOOP
1742 -- check if value exists
1743 l_found := 'N';
1744 FOR i IN NVL(IdList_tbl.FIRST,0)..NVL(IdList_tbl.LAST,-1)
1745 LOOP
1746 IF IdList_tbl(i) = rec_rule_variable_values.variable_value THEN
1747 -- found value
1748 l_found := 'Y';
1749 END IF; -- check if the value matches
1750 END LOOP; -- for all ids
1751 -- check if variable value was found
1752 IF l_found = 'N' THEN
1753 -- variable value not in value set sql
1754 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1755 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1756 l_qa_errors_t_rec.TITLE := l_rule_name;
1757 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1758 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETVAL_VAR;
1759 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETVAL_VAR;
1760 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETVAL_VAR);
1761 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1762 G_OKC_INVALID_VSETVAL_VAR,
1763 'VARIABLE',
1764 rec_rule_variables.variable_name,
1765 'VAR_VALUE',
1766 rec_rule_variable_values.variable_value,
1767 'VALUE_SET_NAME',
1768 rec_rule_variables.value_set_name
1769 );
1770 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1771 G_OKC_INVALID_VSETVAL_VAR_S,
1772 'VARIABLE',
1773 rec_rule_variables.variable_name
1774 );
1775 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1776 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1777
1778 -- insert into okc_qa_errors_t
1779 insert_qa_errors_t
1780 (
1781 p_qa_errors_t_rec => l_qa_errors_t_rec,
1782 x_return_status => x_return_status,
1783 x_msg_count => x_msg_count,
1784 x_msg_data => x_msg_data
1785 );
1786
1787 -- set QA status to Error
1788 x_qa_status := 'E';
1789 CLOSE csr_value_set_dtls;
1790 RETURN;
1791 END IF; -- variable value was not found
1792
1793 END LOOP; -- for each value for the variable type Table
1794
1795
1796
1797 END IF; -- validation_type is 'F'
1798
1799 -- csr_value_set_ind(p_flex_value_id => rec_rule_variable_values.variable_value)
1800 IF l_validation_type = 'I' THEN
1801 FOR rec_rule_variable_values IN csr_rule_variable_values(p_variable_name => rec_rule_variables.variable_code)
1802 LOOP
1803 -- check if value exists
1804 OPEN csr_value_set_ind(p_flex_value_id => rec_rule_variable_values.variable_value);
1805 FETCH csr_value_set_ind INTO l_dummy;
1806 IF csr_value_set_ind%NOTFOUND THEN
1807 -- variable value not in value set independent value set
1808 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1809 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1810 l_qa_errors_t_rec.TITLE := l_rule_name;
1811 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1812 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_VSETVAL_VAR;
1813 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_VSETVAL_VAR;
1814 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_VSETVAL_VAR);
1815 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1816 G_OKC_INVALID_VSETVAL_VAR,
1817 'VARIABLE',
1818 rec_rule_variables.variable_name
1819 );
1820 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1821 G_OKC_INVALID_VSETVAL_VAR_S,
1822 'VARIABLE',
1823 rec_rule_variables.variable_name
1824 );
1825 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1826 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1827
1828 -- insert into okc_qa_errors_t
1829 insert_qa_errors_t
1830 (
1831 p_qa_errors_t_rec => l_qa_errors_t_rec,
1832 x_return_status => x_return_status,
1833 x_msg_count => x_msg_count,
1834 x_msg_data => x_msg_data
1835 );
1836
1837 -- set QA status to Error
1838 x_qa_status := 'E';
1839 CLOSE csr_value_set_ind;
1840 CLOSE csr_value_set_dtls;
1841 RETURN;
1842 END IF; -- variable value not exists
1843 CLOSE csr_value_set_ind;
1844
1845
1846 END LOOP; -- for each value for the variable type Independent
1847
1848 END IF; -- validation_type is 'I'
1849
1850 END IF; -- Variable name in Rule is not OKC$S_ITEM for bug 5721543
1851
1852 CLOSE csr_value_set_dtls;
1853
1854 END LOOP; -- all variables in rule
1855
1856 -- end debug log
1857 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1858 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1859 G_MODULE||l_api_name,
1860 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1861 END IF;
1862
1863 EXCEPTION
1864 WHEN FND_API.G_EXC_ERROR THEN
1865 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1866 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1867 G_MODULE||l_api_name,
1868 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1869 END IF;
1870
1871 x_return_status := G_RET_STS_ERROR ;
1872 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1873
1874 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1875 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1876 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1877 G_MODULE||l_api_name,
1878 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1879 END IF;
1880
1881 x_return_status := G_RET_STS_UNEXP_ERROR ;
1882 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1883
1884 WHEN OTHERS THEN
1885 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1886 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1887 G_MODULE||l_api_name,
1888 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1889 END IF;
1890
1891 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1892 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1893 END IF;
1894 x_return_status := G_RET_STS_UNEXP_ERROR ;
1895 x_msg_data := SQLERRM;
1896 x_msg_count := 1;
1897 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1898
1899 END check_invalid_vset_var;
1900
1901 ---------------------------------------------------
1902 -- Procedure
1903 ---------------------------------------------------
1904 PROCEDURE check_invalid_questions
1905 (
1906 p_rule_id IN NUMBER,
1907 p_sequence_id IN NUMBER,
1908 x_qa_status OUT NOCOPY VARCHAR2,
1909 x_return_status OUT NOCOPY VARCHAR2,
1910 x_msg_count OUT NOCOPY NUMBER,
1911 x_msg_data OUT NOCOPY VARCHAR2
1912 ) IS
1913
1914 CURSOR csr_rule_dtls IS
1915 SELECT rule_name
1916 FROM okc_xprt_rule_hdrs_all
1917 WHERE rule_id = p_rule_id;
1918
1919 -- List of questions used in rule
1920 CURSOR csr_rule_questions IS
1921 -- Questions in Conditions LHS
1922 SELECT DISTINCT q.question_name question_name
1923 FROM okc_xprt_rule_conditions c,
1924 okc_xprt_questions_vl q
1925 WHERE q.question_id = c.object_code
1926 AND c.object_type='QUESTION'
1927 AND c.rule_id = p_rule_id
1928 AND q.disabled_flag = 'Y'
1929 UNION
1930 -- Questions in Conditions RHS
1931 SELECT DISTINCT q.question_name question_name
1932 FROM okc_xprt_rule_conditions c,
1933 okc_xprt_questions_vl q
1934 WHERE q.question_id = c.object_value_code
1935 AND c.object_value_type='QUESTION'
1936 AND c.rule_id = p_rule_id
1937 AND q.disabled_flag = 'Y'
1938 UNION
1939 -- Questions in Outcome
1940 SELECT DISTINCT q.question_name question_name
1941 FROM okc_xprt_rule_outcomes o,
1942 okc_xprt_questions_vl q
1943 WHERE q.question_id = o.object_value_id
1944 AND o.object_type='QUESTION'
1945 AND o.rule_id = p_rule_id
1946 AND q.disabled_flag = 'Y' ;
1947
1948 l_api_name CONSTANT VARCHAR2(30) := 'check_invalid_questions';
1949 l_dummy VARCHAR2(1);
1950 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
1951 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
1952
1953 BEGIN
1954
1955 -- start debug log
1956 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1957 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1958 G_MODULE||l_api_name,
1959 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1960 END IF;
1961
1962 x_return_status := G_RET_STS_SUCCESS;
1963 x_qa_status := 'S';
1964
1965 -- Get Rule Name
1966 OPEN csr_rule_dtls;
1967 FETCH csr_rule_dtls INTO l_rule_name;
1968 CLOSE csr_rule_dtls;
1969
1970 -- Check if any questions on the rule is disabled
1971 FOR rec_rule_questions IN csr_rule_questions
1972 LOOP
1973 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
1974 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
1975 l_qa_errors_t_rec.TITLE := l_rule_name;
1976 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
1977 l_qa_errors_t_rec.QA_CODE := G_CHECK_INVALID_QUESTION;
1978 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_INVALID_QUESTION;
1979 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_INVALID_QUESTION);
1980 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1981 G_OKC_INVALID_QUESTION,
1982 'QUESTION',
1983 rec_rule_questions.question_name
1984 );
1985 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
1986 G_OKC_INVALID_QUESTION_S,
1987 'QUESTION',
1988 rec_rule_questions.question_name
1989 );
1990 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
1991 l_qa_errors_t_rec.RULE_ID := p_rule_id;
1992
1993 -- insert into okc_qa_errors_t
1994 insert_qa_errors_t
1995 (
1996 p_qa_errors_t_rec => l_qa_errors_t_rec,
1997 x_return_status => x_return_status,
1998 x_msg_count => x_msg_count,
1999 x_msg_data => x_msg_data
2000 );
2001
2002 -- set QA status to Error
2003 x_qa_status := 'E';
2004 END LOOP; -- all questions that were disabled
2005
2006 -- end debug log
2007 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2008 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2009 G_MODULE||l_api_name,
2010 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2011 END IF;
2012
2013 EXCEPTION
2014 WHEN FND_API.G_EXC_ERROR THEN
2015 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2016 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2017 G_MODULE||l_api_name,
2018 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2019 END IF;
2020
2021 x_return_status := G_RET_STS_ERROR ;
2022 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2023
2024 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2025 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2026 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2027 G_MODULE||l_api_name,
2028 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2029 END IF;
2030
2031 x_return_status := G_RET_STS_UNEXP_ERROR ;
2032 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2033
2034 WHEN OTHERS THEN
2035 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2036 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2037 G_MODULE||l_api_name,
2038 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2039 END IF;
2040
2041 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2042 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2043 END IF;
2044 x_return_status := G_RET_STS_UNEXP_ERROR ;
2045 x_msg_data := SQLERRM;
2046 x_msg_count := 1;
2047 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2048
2049 END check_invalid_questions;
2050
2051 ---------------------------------------------------
2052 -- Procedure
2053 ---------------------------------------------------
2054 PROCEDURE check_rule_templates
2055 (
2056 p_rule_id IN NUMBER,
2057 p_sequence_id IN NUMBER,
2058 x_qa_status OUT NOCOPY VARCHAR2,
2059 x_return_status OUT NOCOPY VARCHAR2,
2060 x_msg_count OUT NOCOPY NUMBER,
2061 x_msg_data OUT NOCOPY VARCHAR2
2062 ) IS
2063
2064
2065 CURSOR csr_rule_dtls IS
2066 SELECT rule_name, NVL(org_wide_flag,'N')
2067 FROM okc_xprt_rule_hdrs_all
2068 WHERE rule_id = p_rule_id;
2069
2070 CURSOR csr_rule_templates IS
2071 SELECT 'x'
2072 FROM okc_xprt_template_rules
2073 WHERE rule_id = p_rule_id
2074 AND NVL(deleted_flag,'N') = 'N' ;
2075
2076 l_api_name CONSTANT VARCHAR2(30) := 'check_rule_templates';
2077 l_dummy VARCHAR2(1);
2078 l_rule_name okc_xprt_rule_hdrs_all.rule_name%TYPE;
2079 l_org_wide_flag okc_xprt_rule_hdrs_all.org_wide_flag%TYPE;
2080 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
2081
2082 BEGIN
2083
2084 -- start debug log
2085 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2086 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2087 G_MODULE||l_api_name,
2088 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2089 END IF;
2090
2091 x_return_status := G_RET_STS_SUCCESS;
2092 x_qa_status := 'S';
2093
2094 -- Get Rule Name
2095 OPEN csr_rule_dtls;
2096 FETCH csr_rule_dtls INTO l_rule_name, l_org_wide_flag;
2097 CLOSE csr_rule_dtls;
2098
2099 -- If Rule is Org Wide then return
2100 IF l_org_wide_flag = 'Y' THEN
2101 RETURN;
2102 ELSE
2103 -- rule is NOT Org Wide, check if there exists atleast one template
2104 -- assigned to this rule
2105
2106 OPEN csr_rule_templates;
2107 FETCH csr_rule_templates INTO l_dummy;
2108 IF csr_rule_templates%NOTFOUND THEN
2109 -- error
2110 l_qa_errors_t_rec.SEQUENCE_ID := p_sequence_id;
2111 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
2112 l_qa_errors_t_rec.TITLE := l_rule_name;
2113 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
2114 l_qa_errors_t_rec.QA_CODE := G_CHECK_RULE_TEMPLATES;
2115 l_qa_errors_t_rec.MESSAGE_NAME := G_OKC_RULE_TEMPLATES;
2116 l_qa_errors_t_rec.PROBLEM_SHORT_DESC := get_qa_code_dtls(G_CHECK_RULE_TEMPLATES);
2117 l_qa_errors_t_rec.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
2118 G_OKC_RULE_TEMPLATES
2119 );
2120 l_qa_errors_t_rec.SUGGESTION := OKC_TERMS_UTIL_PVT.Get_Message('OKC',
2121 G_OKC_RULE_TEMPLATES_S
2122 );
2123 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
2124 l_qa_errors_t_rec.RULE_ID := p_rule_id;
2125
2126 -- insert into okc_qa_errors_t
2127 insert_qa_errors_t
2128 (
2129 p_qa_errors_t_rec => l_qa_errors_t_rec,
2130 x_return_status => x_return_status,
2131 x_msg_count => x_msg_count,
2132 x_msg_data => x_msg_data
2133 );
2134
2135 -- set QA status to Error
2136 x_qa_status := 'E';
2137
2138 END IF; -- csr_rule_templates%NOTFOUND
2139 CLOSE csr_rule_templates;
2140 END IF; -- l_org_wide_flag = 'Y'
2141
2142
2143
2144 -- end debug log
2145 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2146 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2147 G_MODULE||l_api_name,
2148 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2149 END IF;
2150
2151 EXCEPTION
2152 WHEN FND_API.G_EXC_ERROR THEN
2153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2155 G_MODULE||l_api_name,
2156 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2157 END IF;
2158
2159 x_return_status := G_RET_STS_ERROR ;
2160 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2161
2162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2163 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2165 G_MODULE||l_api_name,
2166 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2167 END IF;
2168
2169 x_return_status := G_RET_STS_UNEXP_ERROR ;
2170 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2171
2172 WHEN OTHERS THEN
2173 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2175 G_MODULE||l_api_name,
2176 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2177 END IF;
2178
2179 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2180 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2181 END IF;
2182 x_return_status := G_RET_STS_UNEXP_ERROR ;
2183 x_msg_data := SQLERRM;
2184 x_msg_count := 1;
2185 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2186
2187 END check_rule_templates;
2188
2189
2190 ---------------------------------------------------
2191 -- Procedure
2192 ---------------------------------------------------
2193 PROCEDURE check_invalid_udv_procedure
2194 (
2195 p_rule_id IN NUMBER,
2196 p_sequence_id IN NUMBER,
2197 x_qa_status OUT NOCOPY VARCHAR2,
2198 x_return_status OUT NOCOPY VARCHAR2,
2199 x_msg_count OUT NOCOPY NUMBER,
2200 x_msg_data OUT NOCOPY VARCHAR2
2201 ) IS
2202
2203 CURSOR csr_rule_variables_w_proc IS
2204 -- Expert Enabled User Defined Variables with Procedures
2205 SELECT distinct v.procedure_name procedure_name,
2206 rcon.object_code variable_code, -- LHS of Condition
2207 t.variable_name variable_name,
2208 rhdr.rule_name rule_name
2209 FROM okc_xprt_rule_hdrs_all rhdr,
2210 okc_xprt_rule_conditions rcon,
2211 okc_bus_variables_b v,
2212 okc_bus_variables_tl t
2213 WHERE rhdr.rule_id = rcon.rule_id
2214 AND rhdr.rule_id = p_rule_id
2215 AND rcon.object_type = 'VARIABLE'
2216 AND rcon.object_code = v.variable_code
2217 AND v.variable_code = t.variable_code
2218 AND v.variable_source = 'P'
2219 AND t.LANGUAGE = USERENV('LANG')
2220 UNION
2221 SELECT distinct v.procedure_name procedure_name,
2222 rcon.object_value_code variable_code, -- RHS of Condition
2223 t.variable_name variable_name,
2224 rhdr.rule_name rule_name
2225 FROM okc_xprt_rule_hdrs_all rhdr,
2226 okc_xprt_rule_conditions rcon,
2227 okc_bus_variables_b v,
2228 okc_bus_variables_tl t
2229 WHERE rhdr.rule_id = rcon.rule_id
2230 AND rhdr.rule_id = p_rule_id
2231 AND rcon.object_value_type = 'VARIABLE'
2232 AND rcon.object_code = v.variable_code
2233 AND v.variable_code = t.variable_code
2234 AND v.variable_source = 'P'
2235 AND t.LANGUAGE = USERENV('LANG');
2236
2237 --Expected procedure name is SCHEMA.PACKAGENAME.PROCEDURENAME
2238
2239 CURSOR csr_check_proc_spec_status (p_procedure_name VARCHAR2) IS
2240 SELECT status
2241 FROM all_objects
2242 WHERE object_name = SUBSTR(p_procedure_name,
2243 INSTR(p_procedure_name,'.')+1,
2244 (INSTR(p_procedure_name,'.',1,2) -
2245 INSTR(p_procedure_name,'.') - 1))
2246 AND object_type = 'PACKAGE'
2247 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
2248
2249
2250 CURSOR csr_check_proc_body_status (p_procedure_name VARCHAR2) IS
2251 SELECT status
2252 FROM all_objects
2253 WHERE object_name = SUBSTR(p_procedure_name,
2254 INSTR(p_procedure_name,'.')+1,
2255 (INSTR(p_procedure_name,'.',1,2) -
2256 INSTR(p_procedure_name,'.') - 1))
2257 AND object_type = 'PACKAGE BODY'
2258 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
2259
2260 CURSOR csr_check_proc_exists (p_procedure_name VARCHAR2) IS
2261 SELECT 'X'
2262 FROM all_source
2263 WHERE name = SUBSTR(p_procedure_name,
2264 INSTR(p_procedure_name,'.')+1,
2265 (INSTR(p_procedure_name,'.',1,2) -
2266 INSTR(p_procedure_name,'.') - 1))
2267 AND type = 'PACKAGE'
2268 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1)
2269 -- Added +1 for Instr for bug 5964390
2270 AND text LIKE '%' || SUBSTR(p_procedure_name,INSTR(p_procedure_name,'.',1,2)+1) || '%';
2271
2272
2273 l_api_name CONSTANT VARCHAR2(30) := 'check_invalid_udv_procedure';
2274 l_dummy VARCHAR2(1);
2275 l_procedure_spec_status ALL_OBJECTS.status%TYPE;
2276 l_procedure_body_status ALL_OBJECTS.status%TYPE;
2277 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
2278
2279 BEGIN
2280
2281 -- start debug log
2282 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2283 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2284 G_MODULE||l_api_name,
2285 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2286 END IF;
2287
2288 x_return_status := G_RET_STS_SUCCESS;
2289 x_qa_status := 'S';
2290
2291 FOR rec_rule_variables_w_proc IN csr_rule_variables_w_proc
2292 LOOP
2293
2294 -- Check procedure existence and validity
2295 OPEN csr_check_proc_spec_status(p_procedure_name => rec_rule_variables_w_proc.procedure_name);
2296 FETCH csr_check_proc_spec_status INTO l_procedure_spec_status;
2297
2298 OPEN csr_check_proc_body_status(p_procedure_name => rec_rule_variables_w_proc.procedure_name);
2299 FETCH csr_check_proc_body_status INTO l_procedure_body_status;
2300
2301 OPEN csr_check_proc_exists(p_procedure_name => rec_rule_variables_w_proc.procedure_name);
2302 FETCH csr_check_proc_exists INTO l_dummy;
2303
2304 -- Debug log
2305 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2306 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2307 G_MODULE||l_api_name,
2308 '120: Checking Procedure spec/body ' || rec_rule_variables_w_proc.procedure_name ||
2309 'validity for variable'||rec_rule_variables_w_proc.variable_name);
2310 END IF;
2311
2312 -- If Procedure Spec is invalid in DB then return warning
2313 IF l_procedure_spec_status = 'INVALID' OR l_procedure_body_status = 'INVALID' THEN
2314 -- VARIABLE PROCEDURE IS INVALID
2315 L_QA_ERRORS_T_REC.SEQUENCE_ID := P_SEQUENCE_ID;
2316 L_QA_ERRORS_T_REC.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
2317 L_QA_ERRORS_T_REC.TITLE := rec_rule_variables_w_proc.rule_name;
2318 L_QA_ERRORS_T_REC.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
2319 L_QA_ERRORS_T_REC.QA_CODE := G_CHECK_INVALID_PROCEDURE_VAR;
2320 L_QA_ERRORS_T_REC.MESSAGE_NAME := G_OKC_INVALID_PROCEDURE_VAR;
2321 L_QA_ERRORS_T_REC.PROBLEM_SHORT_DESC := GET_QA_CODE_DTLS(G_CHECK_INVALID_PROCEDURE_VAR);
2322 L_QA_ERRORS_T_REC.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.GET_MESSAGE('OKC',
2323 G_OKC_INVALID_PROCEDURE_VAR,
2324 'VARIABLE',
2325 rec_rule_variables_w_proc.variable_name,
2326 'PROCEDURE',
2327 rec_rule_variables_w_proc.procedure_name);
2328 L_QA_ERRORS_T_REC.SUGGESTION := OKC_TERMS_UTIL_PVT.GET_MESSAGE('OKC',
2329 G_OKC_INVALID_PROCEDURE_VAR_S,
2330 'VARIABLE',
2331 rec_rule_variables_w_proc.variable_name,
2332 'PROCEDURE',
2333 rec_rule_variables_w_proc.procedure_name);
2334 L_QA_ERRORS_T_REC.CREATION_DATE := G_CREATION_DATE;
2335 L_QA_ERRORS_T_REC.RULE_ID := P_RULE_ID;
2336
2337 -- INSERT INTO OKC_QA_ERRORS_T
2338 INSERT_QA_ERRORS_T
2339 (
2340 P_QA_ERRORS_T_REC => L_QA_ERRORS_T_REC,
2341 X_RETURN_STATUS => X_RETURN_STATUS,
2342 X_MSG_COUNT => X_MSG_COUNT,
2343 X_MSG_DATA => X_MSG_DATA
2344 );
2345
2346 -- SET QA STATUS TO ERROR
2347 x_qa_status := 'E';
2348 CLOSE csr_check_proc_spec_status;
2349 CLOSE csr_check_proc_body_status;
2350 CLOSE csr_check_proc_exists;
2351 RETURN;
2352 END IF; -- Procedure Spec is invalid
2353
2354 -- Debug log
2355 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2356 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2357 G_MODULE||l_api_name,
2358 '110: Checking Procedure spec/body ' || rec_rule_variables_w_proc.procedure_name ||
2359 'existence for variable'||rec_rule_variables_w_proc.variable_name);
2360 END IF;
2361
2362 -- If Procedure Spec/Body/API not found in DB then return warning
2363 IF csr_check_proc_spec_status%NOTFOUND OR csr_check_proc_body_status%NOTFOUND
2364 OR csr_check_proc_exists%NOTFOUND THEN
2365 -- VARIABLE PROCEDURE IS NULL
2366 L_QA_ERRORS_T_REC.SEQUENCE_ID := P_SEQUENCE_ID;
2367 L_QA_ERRORS_T_REC.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
2368 L_QA_ERRORS_T_REC.TITLE := rec_rule_variables_w_proc.rule_name;
2369 L_QA_ERRORS_T_REC.ERROR_SEVERITY := G_RULE_QA_SEVERITY;
2370 L_QA_ERRORS_T_REC.QA_CODE := G_CHECK_NO_PROCEDURE_VAR;
2371 L_QA_ERRORS_T_REC.MESSAGE_NAME := G_OKC_NO_PROCEDURE_VAR;
2372 L_QA_ERRORS_T_REC.PROBLEM_SHORT_DESC := GET_QA_CODE_DTLS(G_CHECK_NO_PROCEDURE_VAR);
2373 L_QA_ERRORS_T_REC.PROBLEM_DETAILS := OKC_TERMS_UTIL_PVT.GET_MESSAGE('OKC',
2374 G_OKC_NO_PROCEDURE_VAR,
2375 'VARIABLE',
2376 rec_rule_variables_w_proc.variable_name,
2377 'PROCEDURE',
2378 rec_rule_variables_w_proc.procedure_name);
2379 L_QA_ERRORS_T_REC.SUGGESTION := OKC_TERMS_UTIL_PVT.GET_MESSAGE('OKC',
2380 G_OKC_NO_PROCEDURE_VAR_S,
2381 'VARIABLE',
2382 rec_rule_variables_w_proc.variable_name,
2383 'PROCEDURE',
2384 rec_rule_variables_w_proc.procedure_name);
2385 L_QA_ERRORS_T_REC.CREATION_DATE := G_CREATION_DATE;
2386 L_QA_ERRORS_T_REC.RULE_ID := P_RULE_ID;
2387
2388 -- INSERT INTO OKC_QA_ERRORS_T
2389 INSERT_QA_ERRORS_T
2390 (
2391 P_QA_ERRORS_T_REC => L_QA_ERRORS_T_REC,
2392 X_RETURN_STATUS => X_RETURN_STATUS,
2393 X_MSG_COUNT => X_MSG_COUNT,
2394 X_MSG_DATA => X_MSG_DATA
2395 );
2396
2397 -- SET QA STATUS TO ERROR
2398 x_qa_status := 'E';
2399 RETURN;
2400 END IF; -- Procedure Spec/Body not existing in DB
2401
2402
2403
2404 CLOSE csr_check_proc_spec_status;
2405 CLOSE csr_check_proc_body_status;
2406 CLOSE csr_check_proc_exists;
2407
2408 END LOOP; -- all Vairables with procedures in rule
2409
2410 -- end debug log
2411 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2412 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2413 G_MODULE||l_api_name,
2414 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2415 END IF;
2416
2417 EXCEPTION
2418 WHEN FND_API.G_EXC_ERROR THEN
2419 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2420 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2421 G_MODULE||l_api_name,
2422 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2423 END IF;
2424
2425 x_return_status := G_RET_STS_ERROR ;
2426 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2427
2428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2429 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2430 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2431 G_MODULE||l_api_name,
2432 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2433 END IF;
2434
2435 x_return_status := G_RET_STS_UNEXP_ERROR ;
2436 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2437
2438 WHEN OTHERS THEN
2439 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2440 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2441 G_MODULE||l_api_name,
2442 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2443 END IF;
2444
2445 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2446 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2447 END IF;
2448 x_return_status := G_RET_STS_UNEXP_ERROR ;
2449 x_msg_data := SQLERRM;
2450 x_msg_count := 1;
2451 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2452
2453 END check_invalid_udv_procedure;
2454
2455
2456 /*
2457 Procedure to be called from UI and Import
2458 p_qa_mode : PUBLISH when publishing rules
2459 APPROVAL when approving template
2460 DISABLE when disabling a rule
2461 */
2462
2463 PROCEDURE qa_rules
2464 (
2465 p_qa_mode IN VARCHAR2,
2466 p_ruleid_tbl IN RuleIdList,
2467 x_sequence_id OUT NOCOPY NUMBER,
2468 x_qa_status OUT NOCOPY VARCHAR2,
2469 x_return_status OUT NOCOPY VARCHAR2,
2470 x_msg_count OUT NOCOPY NUMBER,
2471 x_msg_data OUT NOCOPY VARCHAR2
2472 ) IS
2473
2474 CURSOR l_seq_csr IS
2475 SELECT OKC_QA_ERRORS_T_S.NEXTVAL
2476 FROM DUAL;
2477
2478 --Added for Bug 4725397
2479 CURSOR c_get_rule_org_id (p_rule_id NUMBER) IS
2480 SELECT rules.org_id
2481 FROM okc_xprt_rule_hdrs_all rules
2482 WHERE rules.rule_id = p_rule_id;
2483
2484 l_api_name CONSTANT VARCHAR2(30) := 'qa_rules';
2485 l_sequence_id okc_qa_errors_t.sequence_id%TYPE;
2486 l_qa_status VARCHAR2(1);
2487 l_rule_qa_status VARCHAR2(1);
2488 l_qa_errors_t_rec OKC_QA_ERRORS_T%ROWTYPE;
2489 l_rule_org_id NUMBER;
2490
2491
2492 BEGIN
2493
2494 -- start debug log
2495 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2496 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2497 G_MODULE||l_api_name,
2498 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2499 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2500 G_MODULE||l_api_name,
2501 'Parameter : '||p_qa_mode);
2502 END IF;
2503
2504 x_return_status := G_RET_STS_SUCCESS;
2505 x_qa_status := 'S';
2506
2507 -- generate the sequence
2508 OPEN l_seq_csr;
2509 FETCH l_seq_csr INTO l_sequence_id;
2510 CLOSE l_seq_csr;
2511
2512 x_sequence_id := l_sequence_id;
2513
2514 -- debug
2515 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2516 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2517 G_MODULE||l_api_name,
2518 '110: Sequence Id : '||l_sequence_id);
2519 END IF;
2520
2521 --Added for Bug 4725397
2522 IF p_ruleid_tbl.COUNT > 0 THEN
2523 OPEN c_get_rule_org_id(p_ruleid_tbl(1));
2524 FETCH c_get_rule_org_id INTO l_rule_org_id;
2525 CLOSE c_get_rule_org_id;
2526 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_rule_org_id);
2527 END IF;
2528
2529 -- QA all rules in p_ruleid_tbl
2530
2531 FOR i IN p_ruleid_tbl.FIRST..p_ruleid_tbl.LAST
2532 LOOP
2533
2534 -- Reset Rule QA status
2535 l_rule_qa_status := 'S';
2536
2537 /*
2538 Following QA Checks are only done in PUBLISH mode
2539 1. check_expired_clauses
2540 2. check_draft_clauses
2541 3. check_circular_dependency
2542 4. check_invalid_questions
2543 5. check_rule_templates
2544 6. check_invalid_vset_var
2545 */
2546
2547 IF p_qa_mode = 'PUBLISH' THEN
2548
2549 check_expired_clauses
2550 (
2551 p_rule_id => p_ruleid_tbl(i),
2552 p_sequence_id => l_sequence_id,
2553 x_qa_status => l_qa_status,
2554 x_return_status => x_return_status,
2555 x_msg_count => x_msg_count,
2556 x_msg_data => x_msg_data
2557 );
2558
2559 IF l_qa_status = 'E' THEN
2560 x_qa_status := 'E';
2561 l_rule_qa_status := 'E';
2562 END IF;
2563
2564 -- debug
2565 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2566 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2567 G_MODULE||l_api_name,
2568 '120: After Calling check_expired_clauses x_qa_status : '||x_qa_status);
2569 END IF;
2570
2571
2572 --- If any errors happen abort API
2573 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2575 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2576 RAISE FND_API.G_EXC_ERROR;
2577 END IF;
2578
2579 check_draft_clauses
2580 (
2581 p_rule_id => p_ruleid_tbl(i),
2582 p_sequence_id => l_sequence_id,
2583 x_qa_status => l_qa_status,
2584 x_return_status => x_return_status,
2585 x_msg_count => x_msg_count,
2586 x_msg_data => x_msg_data
2587 );
2588
2589 IF l_qa_status = 'E' THEN
2590 x_qa_status := 'E';
2591 l_rule_qa_status := 'E';
2592 END IF;
2593
2594 -- debug
2595 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2596 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2597 G_MODULE||l_api_name,
2598 '120: After Calling check_draft_clauses x_qa_status : '||x_qa_status);
2599 END IF;
2600
2601 --- If any errors happen abort API
2602 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2604 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2605 RAISE FND_API.G_EXC_ERROR;
2606 END IF;
2607
2608
2609 check_circular_dependency
2610 (
2611 p_rule_id => p_ruleid_tbl(i),
2612 p_sequence_id => l_sequence_id,
2613 p_ruleid_tbl => p_ruleid_tbl,
2614 x_qa_status => l_qa_status,
2615 x_return_status => x_return_status,
2616 x_msg_count => x_msg_count,
2617 x_msg_data => x_msg_data
2618 );
2619
2620 IF l_qa_status = 'E' THEN
2621 x_qa_status := 'E';
2622 l_rule_qa_status := 'E';
2623 END IF;
2624
2625 -- debug
2626 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2627 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2628 G_MODULE||l_api_name,
2629 '120: After Calling check_circular_dependency x_qa_status : '||x_qa_status);
2630 END IF;
2631
2632 --- If any errors happen abort API
2633 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2635 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2636 RAISE FND_API.G_EXC_ERROR;
2637 END IF;
2638
2639
2640 check_invalid_questions
2641 (
2642 p_rule_id => p_ruleid_tbl(i),
2643 p_sequence_id => l_sequence_id,
2644 x_qa_status => l_qa_status,
2645 x_return_status => x_return_status,
2646 x_msg_count => x_msg_count,
2647 x_msg_data => x_msg_data
2648 );
2649
2650 IF l_qa_status = 'E' THEN
2651 x_qa_status := 'E';
2652 l_rule_qa_status := 'E';
2653 END IF;
2654
2655 -- debug
2656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2658 G_MODULE||l_api_name,
2659 '120: After Calling check_invalid_questions x_qa_status : '||x_qa_status);
2660 END IF;
2661
2662 --- If any errors happen abort API
2663 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2665 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2666 RAISE FND_API.G_EXC_ERROR;
2667 END IF;
2668
2669 check_rule_templates
2670 (
2671 p_rule_id => p_ruleid_tbl(i),
2672 p_sequence_id => l_sequence_id,
2673 x_qa_status => l_qa_status,
2674 x_return_status => x_return_status,
2675 x_msg_count => x_msg_count,
2676 x_msg_data => x_msg_data
2677 );
2678
2679 IF l_qa_status = 'E' THEN
2680 x_qa_status := 'E';
2681 l_rule_qa_status := 'E';
2682 END IF;
2683
2684 -- debug
2685 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2686 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2687 G_MODULE||l_api_name,
2688 '120: After Calling check_rule_templates x_qa_status : '||x_qa_status);
2689 END IF;
2690
2691 --- If any errors happen abort API
2692 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2694 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2695 RAISE FND_API.G_EXC_ERROR;
2696 END IF;
2697
2698 check_invalid_vset_var
2699 (
2700 p_rule_id => p_ruleid_tbl(i),
2701 p_sequence_id => l_sequence_id,
2702 x_qa_status => l_qa_status,
2703 x_return_status => x_return_status,
2704 x_msg_count => x_msg_count,
2705 x_msg_data => x_msg_data
2706 );
2707
2708 IF l_qa_status = 'E' THEN
2709 x_qa_status := 'E';
2710 l_rule_qa_status := 'E';
2711 END IF;
2712
2713 -- debug
2714 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2715 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2716 G_MODULE||l_api_name,
2717 '120: After Calling check_invalid_vset_var x_qa_status : '||x_qa_status);
2718 END IF;
2719
2720 --- If any errors happen abort API
2721 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2723 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2724 RAISE FND_API.G_EXC_ERROR;
2725 END IF;
2726
2727 END IF; -- p_qa_mode is PUBLISH
2728
2729 /*
2730 Following QA Checks are always run for both modes i.e PUBLISH and APPROVAL
2731 1. check_invalid_vset_qst
2732 */
2733
2734 IF p_qa_mode IN ('PUBLISH','APPROVAL') THEN
2735
2736 check_invalid_vset_qst
2737 (
2738 p_rule_id => p_ruleid_tbl(i),
2739 p_sequence_id => l_sequence_id,
2740 x_qa_status => l_qa_status,
2741 x_return_status => x_return_status,
2742 x_msg_count => x_msg_count,
2743 x_msg_data => x_msg_data
2744 );
2745
2746 IF l_qa_status = 'E' THEN
2747 x_qa_status := 'E';
2748 l_rule_qa_status := 'E';
2749 END IF;
2750
2751 -- debug
2752 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2753 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2754 G_MODULE||l_api_name,
2755 '120: After Calling check_invalid_vset_qst x_qa_status : '||x_qa_status);
2756 END IF;
2757
2758 --- If any errors happen abort API
2759 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2761 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2762 RAISE FND_API.G_EXC_ERROR;
2763 END IF;
2764
2765
2766 check_invalid_udv_procedure
2767 (
2768 p_rule_id => p_ruleid_tbl(i),
2769 p_sequence_id => l_sequence_id,
2770 x_qa_status => l_qa_status,
2771 x_return_status => x_return_status,
2772 x_msg_count => x_msg_count,
2773 x_msg_data => x_msg_data
2774 );
2775
2776 IF l_qa_status = 'E' THEN
2777 x_qa_status := 'E';
2778 l_rule_qa_status := 'E';
2779 END IF;
2780
2781 -- debug
2782 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2783 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2784 G_MODULE||l_api_name,
2785 '125: After Calling check_invalid_udv_procedure x_qa_status : '||x_qa_status);
2786 END IF;
2787
2788 --- If any errors happen abort API
2789 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2791 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2792 RAISE FND_API.G_EXC_ERROR;
2793 END IF;
2794
2795 END IF; -- p_qa_mode IN ('PUBLISH','APPROVAL')
2796
2797 -- If the rule passed all QAs successfully then insert into okc_qa_errors_t as success
2798 IF l_rule_qa_status = 'S' THEN
2799
2800 l_qa_errors_t_rec.SEQUENCE_ID := l_sequence_id;
2801 l_qa_errors_t_rec.ERROR_RECORD_TYPE := G_ERROR_RECORD_TYPE;
2802 l_qa_errors_t_rec.ERROR_SEVERITY := G_RULE_QA_SUCCESS;
2803 l_qa_errors_t_rec.CREATION_DATE := G_CREATION_DATE;
2804 l_qa_errors_t_rec.RULE_ID := p_ruleid_tbl(i);
2805
2806 -- insert into okc_qa_errors_t
2807 insert_qa_errors_t
2808 (
2809 p_qa_errors_t_rec => l_qa_errors_t_rec,
2810 x_return_status => x_return_status,
2811 x_msg_count => x_msg_count,
2812 x_msg_data => x_msg_data
2813 );
2814
2815 --- If any errors happen abort API
2816 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2818 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2819 RAISE FND_API.G_EXC_ERROR;
2820 END IF;
2821
2822 END IF; -- Rule QA was successful
2823
2824
2825 END LOOP; -- all rules in p_ruleid_tbl
2826
2827
2828
2829 -- end debug log
2830 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2831 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2832 G_MODULE||l_api_name,
2833 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2834 END IF;
2835
2836 EXCEPTION
2837 WHEN FND_API.G_EXC_ERROR THEN
2838 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2839 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2840 G_MODULE||l_api_name,
2841 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2842 END IF;
2843
2844 x_return_status := G_RET_STS_ERROR ;
2845 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2846
2847 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2848 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2849 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2850 G_MODULE||l_api_name,
2851 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2852 END IF;
2853
2854 x_return_status := G_RET_STS_UNEXP_ERROR ;
2855 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2856
2857 WHEN OTHERS THEN
2858 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2859 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2860 G_MODULE||l_api_name,
2861 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2862 END IF;
2863
2864 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2865 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2866 END IF;
2867 x_return_status := G_RET_STS_UNEXP_ERROR ;
2868 x_msg_data := SQLERRM;
2869 x_msg_count := 1;
2870 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2871
2872 END qa_rules;
2873
2874 /*
2875 This procedure will be called from Rules Publish or Disable UI
2876 p_sync_mode = PUBLISH or DISABLE
2877 p_org_id Org Id of the Rules
2878 Depending on the p_sync_mode this API will update the rule status and kick-off the
2879 concurrent program to publish or disable rules
2880
2881 */
2882 PROCEDURE sync_rules
2883 (
2884 p_sync_mode IN VARCHAR2,
2885 p_org_id IN NUMBER,
2886 p_ruleid_tbl IN RuleIdList,
2887 x_request_id OUT NOCOPY NUMBER,
2888 x_return_status OUT NOCOPY VARCHAR2,
2889 x_msg_count OUT NOCOPY NUMBER,
2890 x_msg_data OUT NOCOPY VARCHAR2
2891 ) IS
2892
2893 l_api_name CONSTANT VARCHAR2(30) := 'sync_rules';
2894 i BINARY_INTEGER;
2895 l_user_name fnd_user.user_name%TYPE;
2896 l_notify BOOLEAN;
2897 l_conc_pgm_desc fnd_new_messages.message_text%TYPE;
2898
2899 CURSOR csr_wf_role_user IS
2900 SELECT name
2901 FROM wf_roles
2902 WHERE name = FND_GLOBAL.USER_NAME;
2903
2904 BEGIN
2905
2906 -- start debug log
2907 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2908 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2909 G_MODULE||l_api_name,
2910 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2911 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2912 G_MODULE||l_api_name,
2913 'Parameter p_sync_mode : '||p_sync_mode);
2914 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2915 G_MODULE||l_api_name,
2916 'p_org_id '||p_org_id);
2917 END IF;
2918
2919 x_return_status := G_RET_STS_SUCCESS;
2920
2921 -- initialize i
2922 i := 0;
2923
2924 -- For all rules in p_ruleid_tbl update the rule status to PENDINGPUB or PENDINGDISABLE
2925 FOR i IN NVL(p_ruleid_tbl.FIRST,0)..NVL(p_ruleid_tbl.LAST,-1)
2926 LOOP
2927 UPDATE okc_xprt_rule_hdrs_all
2928 SET status_code = DECODE(p_sync_mode,'PUBLISH','PENDINGPUB',
2929 'DISABLE','PENDINGDISABLE',
2930 p_sync_mode)
2931 WHERE rule_id = p_ruleid_tbl(i);
2932 END LOOP; -- all rules
2933
2934
2935 -- Commit the data
2936 commit work;
2937
2938 -- Set the Notification User
2939 OPEN csr_wf_role_user;
2940 FETCH csr_wf_role_user INTO l_user_name;
2941 IF csr_wf_role_user%FOUND THEN
2942 -- set Notify user
2943 l_notify := fnd_submit.add_notification(FND_GLOBAL.USER_NAME);
2944 END IF; --
2945 CLOSE csr_wf_role_user;
2946
2947 -- Submit the Concurrent Program
2948 IF p_sync_mode = 'PUBLISH' THEN
2949
2950 FND_MESSAGE.set_name('OKC','OKC_XPRT_RULE_CPRG_ACT_MSG');
2951 l_conc_pgm_desc := FND_MESSAGE.get;
2952
2953 x_request_id := fnd_request.submit_request
2954 (application => 'OKC',
2955 program => 'OKC_XPRT_PUBLISH_RULES',
2956 description => l_conc_pgm_desc,
2957 start_time => NULL,
2958 sub_request => FALSE,
2959 argument1 => p_org_id
2960 );
2961
2962 ELSIF p_sync_mode = 'DISABLE' THEN
2963
2964 FND_MESSAGE.set_name('OKC','OKC_XPRT_RULE_CPRG_DIS_MSG');
2965 l_conc_pgm_desc := FND_MESSAGE.get;
2966
2967 x_request_id := fnd_request.submit_request
2968 (application => 'OKC',
2969 program => 'OKC_XPRT_DISABLE_RULES',
2970 description => l_conc_pgm_desc,
2971 start_time => NULL,
2972 sub_request => FALSE,
2973 argument1 => p_org_id
2974 );
2975 END IF;
2976
2977 -- Commit the data
2978 commit work;
2979
2980
2981 IF NVL(x_request_id,0) = 0 THEN
2982 -- Could Not submit Conc Pgm
2983 fnd_message.set_name('OKC','OKC_XPRT_SUB_CZ_RUL_PGM');
2984 RAISE FND_API.G_EXC_ERROR;
2985 END IF; -- x_request_id is 0
2986
2987
2988 -- end debug log
2989 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2990 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2991 G_MODULE||l_api_name,
2992 'x_request_id : '||x_request_id);
2993 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2994 G_MODULE||l_api_name,
2995 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2996 END IF;
2997
2998 EXCEPTION
2999 WHEN FND_API.G_EXC_ERROR THEN
3000 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3001 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3002 G_MODULE||l_api_name,
3003 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3004 END IF;
3005
3006 x_return_status := G_RET_STS_ERROR ;
3007 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3008
3009 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3010 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3011 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3012 G_MODULE||l_api_name,
3013 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3014 END IF;
3015
3016 x_return_status := G_RET_STS_UNEXP_ERROR ;
3017 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3018
3019 WHEN OTHERS THEN
3020 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3021 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3022 G_MODULE||l_api_name,
3023 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3024 END IF;
3025
3026 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3027 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3028 END IF;
3029 x_return_status := G_RET_STS_UNEXP_ERROR ;
3030 x_msg_data := SQLERRM;
3031 x_msg_count := 1;
3032 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3033
3034 END sync_rules;
3035
3036 END OKC_XPRT_QA_PVT ;
3037