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