[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_IMPORT_PVT
Source
1 PACKAGE BODY OKC_XPRT_IMPORT_PVT AS
2 /* $Header: OKCVXCONCPGMB.pls 120.3.12020000.2 2012/07/18 11:23:35 harchand ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL VARIABLES
6 ------------------------------------------------------------------------------
7 G_RUN_ID NUMBER;
8 G_REQUEST_ID NUMBER;
9
10 ------------------------------------------------------------------------------
11 -- GLOBAL CONSTANTS
12 ------------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_IMPORT_PVT';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
17 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
18 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
19
20 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
21 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
22
23 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
24 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
25 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
26
27 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
28 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
29 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
30 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
31
32 ---------------------------------------------------
33 -- Procedure: This function will be return True if
34 -- the rule is active in cz
35 ---------------------------------------------------
36 FUNCTION check_rule_active
37 (
38 p_rule_id IN NUMBER
39 )
40 RETURN VARCHAR2 IS
41
42 CURSOR csr_check_rule_exists (p_rule_id NUMBER) IS
43 SELECT 'X'
44 FROM cz_rules
45 WHERE rule_type = 200 -- Statement rule
46 --AND devl_project_id = p_devl_project_id
47 AND rule_id = persistent_rule_id
48 AND SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id) --For Bug# 8240959
49 AND deleted_flag = 0;
50
51 l_exists VARCHAR2(1);
52
53 BEGIN
54 OPEN csr_check_rule_exists(p_rule_id);
55 FETCH csr_check_rule_exists INTO l_exists;
56 IF csr_check_rule_exists%FOUND THEN
57 CLOSE csr_check_rule_exists;
58 RETURN FND_API.G_TRUE;
59 ELSE
60 CLOSE csr_check_rule_exists;
61 RETURN FND_API.G_FALSE;
62 END IF;
63
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF csr_check_rule_exists%ISOPEN THEN
68 CLOSE csr_check_rule_exists;
69 END IF;
70
71 END check_rule_active;
72
73
74
75 /*====================================================================+
76 Procedure Name : import_template
77 Description : This is a PUBLIC API that imports template Model
78 This API is called from template approval concurrent program
79 Parameters:
80 p_template_id - Template Id of the template
81
82 +====================================================================*/
83
84 PROCEDURE import_template
85 (
86 p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2,
88 p_commit IN VARCHAR2,
89 p_template_id IN NUMBER,
90 p_mode IN VARCHAR2,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_data OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER
94 ) IS
95
96
97 G_ORGANIZATION_NAME VARCHAR2(240);
98
99 CURSOR csr_template_dtls IS
100 SELECT template_name,
101 DECODE(parent_template_id, NULL, template_id, parent_template_id),
102 intent,
103 name,
104 org_id
105 FROM okc_terms_templates_all,
106 hr_operating_units
107 WHERE organization_id = org_id
108 AND template_id = p_template_id ;
109
110 l_api_version CONSTANT NUMBER := 1;
111 l_api_name CONSTANT VARCHAR2(30) := 'import_template';
112 l_template_model_id NUMBER :=NULL;
113 l_run_id NUMBER;
114 l_template_folder_id NUMBER :=NULL;
115 l_folder_desc VARCHAR2(255);
116 l_import_status VARCHAR2(10);
117
118 l_template_name OKC_TERMS_TEMPLATES_ALL.template_name%TYPE;
119 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE;
120 l_intent OKC_TERMS_TEMPLATES_ALL.intent%TYPE;
121 l_org_id OKC_TERMS_TEMPLATES_ALL.org_id%TYPE;
122 l_tmpl_orig_sys_ref cz_devl_projects.orig_sys_ref%TYPE;
123 l_folder_name cz_rp_entries.name%TYPE;
124
125 BEGIN
126
127 -- start debug log
128 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
129 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
130 G_MODULE||l_api_name,
131 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
132 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
133 G_MODULE||l_api_name,
134 '100: Parameters ');
135 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
136 G_MODULE||l_api_name,
137 '100: p_template_id '||p_template_id);
138 END IF;
139
140 -- Standard call to check for call compatibility.
141 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END IF;
144
145 -- Initialize message list if p_init_msg_list is set to TRUE.
146 IF FND_API.to_Boolean( p_init_msg_list ) THEN
147 FND_MSG_PUB.initialize;
148 END IF;
149
150 -- Initialize API return status to success
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 -- Get Template Details
154 OPEN csr_template_dtls;
155 FETCH csr_template_dtls INTO l_template_name,
156 l_template_id,
157 l_intent,
158 G_ORGANIZATION_NAME,
159 l_org_id;
160
161 IF csr_template_dtls%NOTFOUND THEN
162 -- Log Error
163 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
165 G_MODULE||l_api_name,
166 '110: Invalid Template Id: '||p_template_id);
167 END IF;
168 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TEMPLATE');
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171 CLOSE csr_template_dtls;
172
173 -- Put the Template Name in Concurrent Request Log File
174 fnd_file.put_line(FND_FILE.LOG,' ');
175 fnd_file.put_line(FND_FILE.LOG,'Template Name : '||l_template_name);
176
177 -- Update the xprt_request_id for the current template
178 UPDATE okc_terms_templates_all
179 SET xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID,
180 last_update_login = FND_GLOBAL.LOGIN_ID,
181 last_update_date = SYSDATE,
182 last_updated_by = FND_GLOBAL.USER_ID
183 WHERE template_id = p_template_id;
184
185 -- debug log
186 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
187 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
188 G_MODULE||l_api_name,
189 '110: Calling populate_questions_order');
190 END IF;
191
192 --
193 -- Call API to populate template questions
194 --
195 OKC_XPRT_UTIL_PVT.populate_questions_order
196 (
197 p_template_id => p_template_id,
198 p_commit_flag => 'Y',
199 p_mode => p_mode,
200 x_return_status => x_return_status,
201 x_msg_data => x_msg_data,
202 x_msg_count => x_msg_count
203 );
204
205 -- debug log
206 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
207 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
208 G_MODULE||l_api_name,
209 '111: After Calling populate_questions_order x_return_status : '||x_return_status);
210 END IF;
211
212 --- If any errors happen abort API
213 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218
219
220 IF FND_API.To_Boolean( p_commit ) THEN
221 COMMIT WORK;
222 END IF;
223
224 -- Standard call to get message count and if count is 1, get message info.
225 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
226
227 -- end debug log
228 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
229 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
230 G_MODULE||l_api_name,
231 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
232 END IF;
233
234 EXCEPTION
235 WHEN FND_API.G_EXC_ERROR THEN
236 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
237 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
238 G_MODULE||l_api_name,
239 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
240 END IF;
241
242 x_return_status := G_RET_STS_ERROR ;
243 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
244
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
247 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
248 G_MODULE||l_api_name,
249 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
250 END IF;
251
252 x_return_status := G_RET_STS_UNEXP_ERROR ;
253 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
254
255 WHEN OTHERS THEN
256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
258 G_MODULE||l_api_name,
259 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
260 END IF;
261
262 x_return_status := G_RET_STS_UNEXP_ERROR ;
263 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
264 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
265 END IF;
266 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
267
268
269 END import_template;
270
271
272
273
274 PROCEDURE sync_templates
275 (
276 p_mode IN VARCHAR2,
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER
280 ) IS
281
282 -- Templates to be rebuilt for Publishing or Disabling Rules
283 CURSOR csr_local_rules_templates IS
284 -- Templates on Local Rules
285 SELECT DISTINCT to_char(r.template_id)
286 FROM okc_terms_templates_all t,
287 okc_xprt_template_rules r,
288 okc_xprt_rule_hdrs_all h
289 WHERE r.template_id = t.template_id
290 AND r.rule_id = h.rule_id
291 AND t.status_code IN ('APPROVED','ON_HOLD')
292 AND h.request_id = FND_GLOBAL.CONC_REQUEST_ID;
293
294
295 CURSOR csr_org_rules_templates(p_org_id IN NUMBER) IS
296 -- Org Wide Rule Templates
297 SELECT t.template_id
298 FROM okc_terms_templates_all t
299 WHERE t.org_id = p_org_id
300 AND t.intent IN (SELECT DISTINCT intent
301 FROM okc_xprt_rule_hdrs_all
302 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
303 )
304 AND t.contract_expert_enabled = 'Y'
305 AND t.status_code IN ('APPROVED','ON_HOLD');
306
307 -- Cursor to check if any rule is Org Wide
308 CURSOR csr_org_rule_exists IS
309 SELECT 'X'
310 FROM okc_xprt_rule_hdrs_all
311 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
312 AND NVL(org_wide_flag,'N') = 'Y';
313
314 -- Get the Rule Org Id
315 CURSOR csr_rule_org_id IS
316 SELECT org_id
317 FROM okc_xprt_rule_hdrs_all
318 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
319
320 l_api_name CONSTANT VARCHAR2(30) := 'sync_templates';
321 l_template_id okc_terms_templates_all.template_id%TYPE;
322 l_org_rules_yn okc_xprt_rule_hdrs_all.org_wide_flag%TYPE := NULL;
323 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
324
325 BEGIN
326 -- start debug log
327 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
329 G_MODULE||l_api_name,
330 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
331 END IF;
332
333 -- Initialize API return status to success
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335
336 -- Check if any rules in current request are Org Wide Rules
337 OPEN csr_org_rule_exists;
338 FETCH csr_org_rule_exists INTO l_org_rules_yn;
339 CLOSE csr_org_rule_exists;
340
341 fnd_file.put_line(FND_FILE.LOG,'Org wide rules exists ? '||l_org_rules_yn);
342 -- Get the Rule Org Id
343 OPEN csr_rule_org_id;
344 FETCH csr_rule_org_id INTO l_org_id;
345 CLOSE csr_rule_org_id;
346
347 -- debug log
348 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
350 G_MODULE||l_api_name,
351 '110: l_org_rules_yn '||l_org_rules_yn);
352 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
353 G_MODULE||l_api_name,
354 '110: l_org_id '||l_org_id);
355 END IF;
356
357
358
359 IF l_org_rules_yn IS NULL THEN
360 -- No Org Wide Rules in current concurrent request
361 -- Open the Local Csr
362 OPEN csr_local_rules_templates;
363 LOOP
364 FETCH csr_local_rules_templates INTO l_template_id;
365 EXIT WHEN csr_local_rules_templates%NOTFOUND;
366 import_template
367 (
368 p_api_version => 1,
369 p_init_msg_list => 'T',
370 p_commit => 'T',
371 p_template_id => l_template_id,
372 p_mode => p_mode,
373 x_return_status => x_return_status,
374 x_msg_data => x_msg_data,
375 x_msg_count => x_msg_count
376 ) ;
377
378 --- If any errors happen abort API
379 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384
385 END LOOP; -- local
386 CLOSE csr_local_rules_templates;
387
388 ELSE
389 -- Org Wide Rules exists in current concurrent request
390 -- Open the Org Wide Cursor
391 OPEN csr_org_rules_templates(p_org_id => l_org_id);
392 LOOP
393 FETCH csr_org_rules_templates INTO l_template_id;
394 EXIT WHEN csr_org_rules_templates%NOTFOUND;
395 import_template
396 (
397 p_api_version => 1,
398 p_init_msg_list => 'T',
399 p_commit => 'T',
400 p_template_id => l_template_id,
401 p_mode => p_mode,
402 x_return_status => x_return_status,
403 x_msg_data => x_msg_data,
404 x_msg_count => x_msg_count
405 ) ;
406
407 --- If any errors happen abort API
408 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
411 RAISE FND_API.G_EXC_ERROR;
412 END IF;
413
414 END LOOP; -- Org Rules Templates
415 CLOSE csr_org_rules_templates;
416
417 END IF;
418
419 -- Standard call to get message count and if count is 1, get message info
420 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
421
422 -- end debug log
423 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
424 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
425 G_MODULE||l_api_name,
426 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
427 END IF;
428
429 EXCEPTION
430 WHEN FND_API.G_EXC_ERROR THEN
431 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
432 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
433 G_MODULE||l_api_name,
434 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
435 END IF;
436
437 x_return_status := G_RET_STS_ERROR ;
438 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
439
440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
441 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
442 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
443 G_MODULE||l_api_name,
444 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
445 END IF;
446
447 x_return_status := G_RET_STS_UNEXP_ERROR ;
448 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
449
450 WHEN OTHERS THEN
451 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
453 G_MODULE||l_api_name,
454 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
455 END IF;
456
457 x_return_status := G_RET_STS_UNEXP_ERROR ;
458 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
459 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
460 END IF;
461 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
462
463
464 END sync_templates;
465
466
467
468 ---------------------------------------------------
469
470 ---------------------------------------------------
471 -- Procedure: This procedure will be registered as
472 -- Contract Expert Rules Publication concurrent program
473 ---------------------------------------------------
474 PROCEDURE publish_rules
475 (
476 errbuf OUT NOCOPY VARCHAR2,
477 retcode OUT NOCOPY VARCHAR2,
478 p_org_id IN NUMBER
479 ) IS
480
481 x_return_status VARCHAR2(1);
482 x_msg_data VARCHAR2(4000);
483 x_msg_count NUMBER;
484 l_api_name CONSTANT VARCHAR2(30) := 'publish_rules';
485 req_data VARCHAR2(240);
486 x_cz_cp_status BOOLEAN;
487 x_phase VARCHAR2(1000);
488 x_status VARCHAR2(1000);
489 x_dev_phase VARCHAR2(1000);
490 x_dev_status VARCHAR2(1000);
491 x_message VARCHAR2(1000);
492 l_rules_cnt NUMBER;
493 l_sequence_id NUMBER;
494 l_qa_status VARCHAR2(1);
495 l_conc_pgm_desc FND_NEW_MESSAGES.message_text%TYPE;
496
497
498
499 CURSOR csr_get_child_req_dtls IS
500 SELECT SUBSTR(req_data,
501 1,
502 INSTR(req_data,':',1) -1
503 ) child_req_id,
504 SUBSTR(req_data,
505 INSTR(req_data,':',1) + 1
506 ) run_id
507 FROM dual;
508
509 CURSOR csr_cz_imp_rules_cnt IS
510 SELECT COUNT(*)
511 FROM cz_imp_rules
512 WHERE run_id = G_RUN_ID;
513
514 -- Added for 4103931
515 CURSOR csr_pub_rule_list IS
516 SELECT rule_id
517 FROM okc_xprt_rule_hdrs_all
518 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
519
520 l_rule_exists_flag VARCHAR2(1) := NULL;
521
522 l_line_level_flag okc_xprt_rule_hdrs_all.line_level_flag%TYPE;
523
524 l_okc_rules_engine VARCHAR2(1);
525
526 l_rule_id NUMBER;
527
528 CURSOR csr_rules IS
529 SELECT rule_id
530 FROM okc_xprt_rule_hdrs_all
531 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
532
533
534 BEGIN
535
536 SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
537
538 fnd_file.put_line(FND_FILE.LOG,'Using OKC Rules Engine'||l_okc_rules_engine);
539
540 IF Nvl(l_okc_rules_engine,'N') = 'N' THEN
541
542 -- Check if the concurrent program is being restarted due to completion of child request
543 req_data := fnd_conc_global.request_data;
544
545 IF req_data IS NULL THEN
546 -- Calling the parent concurrent prorgam for the first time
547 -- Execute Steps 1 to 7
548
549 -- start debug log
550 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
552 G_MODULE||l_api_name,
553 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
554 END IF;
555
556 -- log file
557 fnd_file.put_line(FND_FILE.LOG,' ');
558 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
559 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
560 fnd_file.put_line(FND_FILE.LOG,'Org Id : '||p_org_id);
561
562 /*
563 Step 1: Update current request Id for all rules to be published
564 */
565
566 fnd_file.put_line(FND_FILE.LOG,' ');
567 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
568 fnd_file.put_line(FND_FILE.LOG,'Step 1: Updating request_id for rules to be published ');
569 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
570 fnd_file.put_line(FND_FILE.LOG,' ');
571
572 -- Update request_id for all Rules in Pending Publication for current org id
573 UPDATE okc_xprt_rule_hdrs_all
574 SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
575 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
576 program_application_id = FND_GLOBAL.PROG_APPL_ID,
577 program_update_date = SYSDATE,
578 last_update_login = FND_GLOBAL.LOGIN_ID,
579 last_update_date = SYSDATE,
580 last_updated_by = FND_GLOBAL.USER_ID
581 WHERE org_id = p_org_id
582 AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
583 intent,
584 fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
585 )
586 AND status_code = 'PENDINGPUB';
587
588 -- Check If any rules are to be processed else exit
589 IF SQL%NOTFOUND THEN
590 -- exit as no rules to be processed
591 fnd_file.put_line(FND_FILE.LOG,' ');
592 fnd_file.put_line(FND_FILE.LOG,'No Rules to be processed ');
593 fnd_file.put_line(FND_FILE.LOG,' ');
594
595 retcode := 0;
596 errbuf := '';
597 RETURN;
598
599 END IF; -- no rows to be processed
600
601 -- commit the data
602 COMMIT WORK;
603
604 /*
605 Step 1.1: Rules QA checks
606 */
607
608 fnd_file.put_line(FND_FILE.LOG,' ');
609 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
610 fnd_file.put_line(FND_FILE.LOG,'Step 1.1: Rules QA Checks old ');
611 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
612 fnd_file.put_line(FND_FILE.LOG,' ');
613
614 OKC_XPRT_UTIL_PVT.check_rules_validity
615 (
616 p_qa_mode => 'PUBLISH',
617 p_template_id => NULL,
618 x_sequence_id => l_sequence_id,
619 x_qa_status => l_qa_status,
620 x_return_status => x_return_status,
621 x_msg_data => x_msg_data,
622 x_msg_count => x_msg_count
623 ) ;
624
625 fnd_file.put_line(FND_FILE.LOG,' ');
626 fnd_file.put_line(FND_FILE.LOG,'After OKC_XPRT_UTIL_PVT.check_rules_validity');
627 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
628 fnd_file.put_line(FND_FILE.LOG,'x_qa_status: '||l_qa_status);
629 fnd_file.put_line(FND_FILE.LOG,'x_sequence_id: '||l_sequence_id);
630 fnd_file.put_line(FND_FILE.LOG,' ');
631
632 --- If any errors happen abort API
633 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
636 RAISE FND_API.G_EXC_ERROR;
637 END IF;
638
639 -- Check If any rules had QA errors and abort
640 IF l_qa_status <> 'S' THEN
641 -- exit as no rules had QA errors
642 fnd_file.put_line(FND_FILE.LOG,' ');
643 fnd_file.put_line(FND_FILE.LOG,'Rules QA Check failed');
644 fnd_file.put_line(FND_FILE.LOG,' ');
645
646
647 -- Added for Bug 4103931
648 FOR csr_pub_rule_rec IN csr_pub_rule_list
649 LOOP
650 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
651 UPDATE okc_xprt_rule_hdrs_all
652 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
653 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
654 AND rule_id = csr_pub_rule_rec.rule_id;
655 l_rule_exists_flag := 'F';
656 END LOOP;
657
658
659 -- Added for Bug 4690232
660 COMMIT;
661
662 retcode := 2;
663 errbuf := '';
664 RETURN;
665
666 END IF; -- QA Errors
667
668
669
670 /*
671 Step 2: Import Variable Model
672 */
673
674 fnd_file.put_line(FND_FILE.LOG,' ');
675 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
676 fnd_file.put_line(FND_FILE.LOG,'Step 2: Importing Variable Model');
677 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
678 fnd_file.put_line(FND_FILE.LOG,' ');
679
680 OKC_XPRT_IMPORT_VARIABLES_PVT.import_variables
681 (
682 p_api_version => 1,
683 p_init_msg_list => 'T',
684 p_commit => 'T',
685 p_org_id => p_org_id,
686 x_return_status => x_return_status,
687 x_msg_data => x_msg_data,
688 x_msg_count => x_msg_count
689 ) ;
690
691 fnd_file.put_line(FND_FILE.LOG,' ');
692 fnd_file.put_line(FND_FILE.LOG,'After Importing Variable Model');
693 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
694 fnd_file.put_line(FND_FILE.LOG,' ');
695
696 --- If any errors happen abort API
697 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
698 -- Added for Bug 4757731
699 FOR csr_pub_rule_rec IN csr_pub_rule_list
700 LOOP
701 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
702 UPDATE okc_xprt_rule_hdrs_all
703 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
704 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
705 AND rule_id = csr_pub_rule_rec.rule_id;
706 l_rule_exists_flag := 'F';
707 END LOOP;
708 COMMIT;
709 -- Added for Bug 4757731
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711
712 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
713 -- Added for Bug 4757731
714 FOR csr_pub_rule_rec IN csr_pub_rule_list
715 LOOP
716 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
717 UPDATE okc_xprt_rule_hdrs_all
718 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
719 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
720 AND rule_id = csr_pub_rule_rec.rule_id;
721 l_rule_exists_flag := 'F';
722 END LOOP;
723 COMMIT;
724 -- Added for Bug 4757731
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727
728
729 /*
730 Step 3: Import Clause Model
731 */
732
733 fnd_file.put_line(FND_FILE.LOG,' ');
734 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
735 fnd_file.put_line(FND_FILE.LOG,'Step 3: Importing Clause Model');
736 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
737 fnd_file.put_line(FND_FILE.LOG,' ');
738
739 OKC_XPRT_IMPORT_CLAUSES_PVT.import_clauses
740 (
741 p_api_version => 1,
742 p_init_msg_list => 'T',
743 p_commit => 'T',
744 p_org_id => p_org_id,
745 x_return_status => x_return_status,
746 x_msg_data => x_msg_data,
747 x_msg_count => x_msg_count
748 ) ;
749
750 fnd_file.put_line(FND_FILE.LOG,' ');
751 fnd_file.put_line(FND_FILE.LOG,'After Importing Clause Model');
752 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
753 fnd_file.put_line(FND_FILE.LOG,' ');
754
755 --- If any errors happen abort API
756 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
757 -- Added for Bug 4757731
758 FOR csr_pub_rule_rec IN csr_pub_rule_list
759 LOOP
760 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
761 UPDATE okc_xprt_rule_hdrs_all
762 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
763 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
764 AND rule_id = csr_pub_rule_rec.rule_id;
765 l_rule_exists_flag := 'F';
766 END LOOP;
767 COMMIT;
768 -- Added for Bug 4757731
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
770
771 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
772 -- Added for Bug 4757731
773 FOR csr_pub_rule_rec IN csr_pub_rule_list
774 LOOP
775 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
776 UPDATE okc_xprt_rule_hdrs_all
777 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
778 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
779 AND rule_id = csr_pub_rule_rec.rule_id;
780 l_rule_exists_flag := 'F';
781 END LOOP;
782 COMMIT;
783 -- Added for Bug 4757731
784 RAISE FND_API.G_EXC_ERROR;
785 END IF;
786
787 /*
788 Step 4: Import Template Model(s)
789 */
790
791 fnd_file.put_line(FND_FILE.LOG,' ');
792 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
793 fnd_file.put_line(FND_FILE.LOG,'Step 4: Importing Template Models');
794 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
795 fnd_file.put_line(FND_FILE.LOG,' ');
796
797 OKC_XPRT_IMPORT_TEMPLATE_PVT.rebuild_tmpl_pub_disable
798 (
799 x_return_status => x_return_status,
800 x_msg_data => x_msg_data,
801 x_msg_count => x_msg_count
802 ) ;
803
804 fnd_file.put_line(FND_FILE.LOG,' ');
805 fnd_file.put_line(FND_FILE.LOG,'After Importing Template Models');
806 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
807 fnd_file.put_line(FND_FILE.LOG,' ');
808
809 --- If any errors happen abort API
810 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
811 -- Added for Bug 4757731
812 FOR csr_pub_rule_rec IN csr_pub_rule_list
813 LOOP
814 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
815 UPDATE okc_xprt_rule_hdrs_all
816 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
817 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
818 AND rule_id = csr_pub_rule_rec.rule_id;
819 l_rule_exists_flag := 'F';
820 END LOOP;
821 COMMIT;
822 -- Added for Bug 4757731
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
824
825 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
826 -- Added for Bug 4757731
827 FOR csr_pub_rule_rec IN csr_pub_rule_list
828 LOOP
829 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
830 UPDATE okc_xprt_rule_hdrs_all
831 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
832 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
833 AND rule_id = csr_pub_rule_rec.rule_id;
834 l_rule_exists_flag := 'F';
835 END LOOP;
836 COMMIT;
837 -- Added for Bug 4757731
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841
842 /*
843 Step 5: Populate cz_imp_rules
844 */
845
846 fnd_file.put_line(FND_FILE.LOG,' ');
847 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
848 fnd_file.put_line(FND_FILE.LOG,'Step 5: Populating cz_imp_rules ');
849 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
850 fnd_file.put_line(FND_FILE.LOG,' ');
851
852 OKC_XPRT_IMPORT_RULES_PVT.import_rules_publish
853 (
854 x_run_id => G_RUN_ID,
855 x_return_status => x_return_status,
856 x_msg_data => x_msg_data,
857 x_msg_count => x_msg_count
858 );
859
860 fnd_file.put_line(FND_FILE.LOG,' ');
861 fnd_file.put_line(FND_FILE.LOG,'After Populating cz_imp_rules');
862 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
863 fnd_file.put_line(FND_FILE.LOG,'Rule Import Run Id : '||G_RUN_ID);
864 fnd_file.put_line(FND_FILE.LOG,' ');
865
866 --- If any errors happen abort API
867 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
868 -- Added for Bug 4757731
869 FOR csr_pub_rule_rec IN csr_pub_rule_list
870 LOOP
871 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
872 UPDATE okc_xprt_rule_hdrs_all
873 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
874 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
875 AND rule_id = csr_pub_rule_rec.rule_id;
876 l_rule_exists_flag := 'F';
877 END LOOP;
878 COMMIT;
879 -- Added for Bug 4757731
880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
881
882 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
883 -- Added for Bug 4757731
884 FOR csr_pub_rule_rec IN csr_pub_rule_list
885 LOOP
886 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
887 UPDATE okc_xprt_rule_hdrs_all
888 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
889 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
890 AND rule_id = csr_pub_rule_rec.rule_id;
891 l_rule_exists_flag := 'F';
892 END LOOP;
893 COMMIT;
894 -- Added for Bug 4757731
895 RAISE FND_API.G_EXC_ERROR;
896 END IF;
897
898 /*
899 Step 5.1: Count Rules to be imported
900 Check if there are any records in cz_imp_rules
901 If there are no records in cz_imp_rules then there were no expert enabled templates
902 attached to the rule. Just change the status of the rule to ACTIVE
903 */
904
905 fnd_file.put_line(FND_FILE.LOG,' ');
906 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
907 fnd_file.put_line(FND_FILE.LOG,'Step 5.1:Count Rules to be imported');
908 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
909 fnd_file.put_line(FND_FILE.LOG,' ');
910
911 OPEN csr_cz_imp_rules_cnt;
912 FETCH csr_cz_imp_rules_cnt INTO l_rules_cnt;
913 CLOSE csr_cz_imp_rules_cnt;
914
915 fnd_file.put_line(FND_FILE.LOG,' ');
916 fnd_file.put_line(FND_FILE.LOG,'Total Rules to be Imported to CZ: '||l_rules_cnt);
917 fnd_file.put_line(FND_FILE.LOG,' ');
918
919
920 IF l_rules_cnt = 0 THEN
921
922 OKC_XPRT_UTIL_PVT.publish_rule_with_no_tmpl
923 (
924 p_calling_mode => 'PUBLISH',
925 x_return_status => x_return_status,
926 x_msg_data => x_msg_data,
927 x_msg_count => x_msg_count
928 );
929
930 fnd_file.put_line(FND_FILE.LOG,' ');
931 fnd_file.put_line(FND_FILE.LOG,'After OKC_XPRT_UTIL_PVT.publish_rule_with_no_tmpl');
932 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
933 fnd_file.put_line(FND_FILE.LOG,' ');
934
935 --- If any errors happen abort API
936 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
937 -- Added for Bug 4757731
938 FOR csr_pub_rule_rec IN csr_pub_rule_list
939 LOOP
940 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
941 UPDATE okc_xprt_rule_hdrs_all
942 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
943 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
944 AND rule_id = csr_pub_rule_rec.rule_id;
945 l_rule_exists_flag := 'F';
946 END LOOP;
947 COMMIT;
948 -- Added for Bug 4757731
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950
951 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
952 -- Added for Bug 4757731
953 FOR csr_pub_rule_rec IN csr_pub_rule_list
954 LOOP
955 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
956 UPDATE okc_xprt_rule_hdrs_all
957 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
958 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
959 AND rule_id = csr_pub_rule_rec.rule_id;
960 l_rule_exists_flag := 'F';
961 END LOOP;
962 COMMIT;
963 -- Added for Bug 4757731
964 RAISE FND_API.G_EXC_ERROR;
965 END IF;
966
967 fnd_file.put_line(FND_FILE.LOG,' ');
968 fnd_file.put_line(FND_FILE.LOG,'Rules has No expert template attached');
969 fnd_file.put_line(FND_FILE.LOG,' ');
970
971 retcode := 0;
972 errbuf := '';
973 RETURN;
974
975 END IF; -- l_rules_cnt = 0
976
977
978 /*
979 Step 6: Insert Extension Rules
980 */
981
982 fnd_file.put_line(FND_FILE.LOG,' ');
983 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
984 fnd_file.put_line(FND_FILE.LOG,'Step 6: Calling API to insert extension rule records');
985 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
986 fnd_file.put_line(FND_FILE.LOG,' ');
987
988 OKC_XPRT_IMPORT_RULES_PVT.attach_extension_rule
989 (
990 p_api_version => 1,
991 p_init_msg_list => 'T',
992 p_run_id => G_RUN_ID,
993 x_return_status => x_return_status,
994 x_msg_data => x_msg_data,
995 x_msg_count => x_msg_count
996 );
997
998 fnd_file.put_line(FND_FILE.LOG,' ');
999 fnd_file.put_line(FND_FILE.LOG,'Step 6: After Calling API to insert extension rule records');
1000 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1001 fnd_file.put_line(FND_FILE.LOG,' ');
1002
1003 /*
1004 Step 7: Call the CZ Rule Import Concurrent Program
1005 CZ Pgm: Import Configuration Rules (CZRULEIMPORTCP)
1006 */
1007
1008 fnd_file.put_line(FND_FILE.LOG,' ');
1009 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1010 fnd_file.put_line(FND_FILE.LOG,'Step 7: Calling the CZ Rule Import Concurrent Program');
1011 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1012 fnd_file.put_line(FND_FILE.LOG,' ');
1013 fnd_file.put_line(FND_FILE.LOG,'Parameter Run Id : '||G_RUN_ID);
1014
1015
1016 FND_MESSAGE.set_name('OKC','OKC_XPRT_RULE_CCPRG_ACT_MSG');
1017 l_conc_pgm_desc := FND_MESSAGE.get;
1018
1019 G_REQUEST_ID := fnd_request.submit_request
1020 (application => 'CZ',
1021 program => 'CZRULEIMPORTCP',
1022 description => l_conc_pgm_desc,
1023 start_time => NULL,
1024 sub_request => TRUE,
1025 argument1 => G_RUN_ID
1026 );
1027
1028 fnd_file.put_line(FND_FILE.LOG,'Request Id of CZ Rule Concurrent Pgm : '||G_REQUEST_ID);
1029
1030 -- commit the data
1031 COMMIT WORK;
1032
1033 IF NVL(G_REQUEST_ID,0) = 0 THEN
1034 -- Could Not submit Conc Pgm
1035 fnd_message.set_name('OKC','OKC_XPRT_SUB_CZ_RUL_PGM');
1036 fnd_file.put_line(FND_FILE.LOG,'Could NOT submit CZ Concurrent Program');
1037
1038 -- Added for Bug 4757731
1039 FOR csr_pub_rule_rec IN csr_pub_rule_list
1040 LOOP
1041 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1042 UPDATE okc_xprt_rule_hdrs_all
1043 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1044 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1045 AND rule_id = csr_pub_rule_rec.rule_id;
1046 l_rule_exists_flag := 'F';
1047 END LOOP;
1048 COMMIT;
1049
1050 RAISE FND_API.G_EXC_ERROR;
1051 ELSE
1052 -- child submission successful, pause the parent program
1053 fnd_conc_global.set_req_globals
1054 ( conc_status => 'PAUSED',
1055 request_data => to_char(G_REQUEST_ID)||':'||to_char(G_RUN_ID)
1056 );
1057 END IF; -- child submitted successfully
1058
1059 ELSE
1060 -- req_data IS NOT NULL
1061 -- Restarting the Parent Concurrent Program after completing the child
1062 -- Execute the remaing steps
1063 /*
1064 Note : when the Parent pgm resumes, the session id for the pgm is different
1065 then the original session id. Any variables set before cannot be read
1066 Any variables required in this block are put in request_data and read from
1067 the same
1068
1069 */
1070
1071 OPEN csr_get_child_req_dtls;
1072 FETCH csr_get_child_req_dtls INTO G_REQUEST_ID, G_RUN_ID;
1073 CLOSE csr_get_child_req_dtls;
1074
1075 x_cz_cp_status := fnd_concurrent.get_request_status
1076 (G_REQUEST_ID,
1077 NULL,
1078 NULL,
1079 x_phase,
1080 x_status,
1081 x_dev_phase,
1082 x_dev_status,
1083 x_message
1084 );
1085
1086 fnd_file.put_line(FND_FILE.LOG,' ');
1087 fnd_file.put_line(FND_FILE.LOG,'After Completing CZ Rule Conc Pgm with request id :'||G_REQUEST_ID);
1088 fnd_file.put_line(FND_FILE.LOG,'Run Id : '||G_RUN_ID);
1089 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1090 fnd_file.put_line(FND_FILE.LOG,'Results ');
1091 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1092 fnd_file.put_line(FND_FILE.LOG,'Phase : '||x_phase);
1093 fnd_file.put_line(FND_FILE.LOG,'Status : '||x_status);
1094 fnd_file.put_line(FND_FILE.LOG,'dev_phase : '||x_dev_phase);
1095 fnd_file.put_line(FND_FILE.LOG,'dev_status : '||x_dev_status);
1096 fnd_file.put_line(FND_FILE.LOG,'Message : '||substr(x_message,1,100));
1097 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1098 fnd_file.put_line(FND_FILE.LOG,' ');
1099
1100 IF x_dev_phase <> 'COMPLETED' AND
1101 x_dev_status NOT IN ('NORMAL','WARNING') THEN
1102 -- error in CZ Concurrent Program
1103 fnd_file.put_line(FND_FILE.LOG, 'Error in CZ Rule Import Concurrent Program ');
1104 fnd_file.put_line(FND_FILE.LOG, 'Request Id :'||G_REQUEST_ID);
1105 fnd_file.put_line(FND_FILE.LOG, 'Run Id :'||G_RUN_ID);
1106
1107 -- Added for Bug 4757731
1108 FOR csr_pub_rule_rec IN csr_pub_rule_list
1109 LOOP
1110 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1111 UPDATE okc_xprt_rule_hdrs_all
1112 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1113 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1114 AND rule_id = csr_pub_rule_rec.rule_id;
1115 l_rule_exists_flag := 'F';
1116 END LOOP;
1117 COMMIT;
1118
1119 RAISE FND_API.G_EXC_ERROR;
1120 END IF;
1121
1122
1123 /*
1124 Step 8: Check status of Rules Imported
1125 */
1126
1127 fnd_file.put_line(FND_FILE.LOG,' ');
1128 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1129 fnd_file.put_line(FND_FILE.LOG,'Step 8: Checking Status of Rules imported');
1130 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1131 fnd_file.put_line(FND_FILE.LOG,' ');
1132
1133 OKC_XPRT_UTIL_PVT.check_import_status
1134 (
1135 p_run_id => G_RUN_ID,
1136 p_import_status => 'S',
1137 p_model_type => 'R', -- Rules Import
1138 x_return_status => x_return_status,
1139 x_msg_data => x_msg_data,
1140 x_msg_count => x_msg_count
1141 );
1142
1143 fnd_file.put_line(FND_FILE.LOG,' ');
1144 fnd_file.put_line(FND_FILE.LOG,'After checking import status');
1145 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1146 fnd_file.put_line(FND_FILE.LOG,' ');
1147
1148 --- If any errors happen abort API
1149 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1150 -- Added for Bug 4757731
1151 FOR csr_pub_rule_rec IN csr_pub_rule_list
1152 LOOP
1153 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1154 UPDATE okc_xprt_rule_hdrs_all
1155 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1156 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1157 AND rule_id = csr_pub_rule_rec.rule_id;
1158 l_rule_exists_flag := 'F';
1159 END LOOP;
1160 COMMIT;
1161 -- Added for Bug 4757731
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163
1164 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1165 -- Added for Bug 4757731
1166 FOR csr_pub_rule_rec IN csr_pub_rule_list
1167 LOOP
1168 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1169 UPDATE okc_xprt_rule_hdrs_all
1170 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1171 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1172 AND rule_id = csr_pub_rule_rec.rule_id;
1173 l_rule_exists_flag := 'F';
1174 END LOOP;
1175 COMMIT;
1176 -- Added for Bug 4757731
1177 RAISE FND_API.G_EXC_ERROR;
1178 END IF;
1179
1180
1181 /*
1182 Step 9: Call the Test Publication API
1183 */
1184
1185 fnd_file.put_line(FND_FILE.LOG,' ');
1186 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1187 fnd_file.put_line(FND_FILE.LOG,'Step 9: Calling the Test Publication API');
1188 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1189 fnd_file.put_line(FND_FILE.LOG,' ');
1190
1191 OKC_XPRT_UTIL_PVT.create_test_publication
1192 (
1193 x_return_status => x_return_status,
1194 x_msg_data => x_msg_data,
1195 x_msg_count => x_msg_count
1196 );
1197
1198 fnd_file.put_line(FND_FILE.LOG,' ');
1199 fnd_file.put_line(FND_FILE.LOG,'After Calling the Test Publication API ');
1200 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1201 fnd_file.put_line(FND_FILE.LOG,' ');
1202
1203 --- If any errors happen abort API
1204 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1205 -- Added for Bug 4757731
1206 FOR csr_pub_rule_rec IN csr_pub_rule_list
1207 LOOP
1208 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1209 UPDATE okc_xprt_rule_hdrs_all
1210 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1211 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1212 AND rule_id = csr_pub_rule_rec.rule_id;
1213 l_rule_exists_flag := 'F';
1214 END LOOP;
1215 COMMIT;
1216 -- Added for Bug 4757731
1217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1218
1219 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1220 -- Added for Bug 4757731
1221 FOR csr_pub_rule_rec IN csr_pub_rule_list
1222 LOOP
1223 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1224 UPDATE okc_xprt_rule_hdrs_all
1225 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1226 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1227 AND rule_id = csr_pub_rule_rec.rule_id;
1228 l_rule_exists_flag := 'F';
1229 END LOOP;
1230 COMMIT;
1231 -- Added for Bug 4757731
1232 RAISE FND_API.G_EXC_ERROR;
1233 END IF;
1234 /*
1235 Step 10: Call the Production Publication API
1236 */
1237
1238 fnd_file.put_line(FND_FILE.LOG,' ');
1239 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1240 fnd_file.put_line(FND_FILE.LOG,'Step 9: Calling the Production Publication API');
1241 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1242 fnd_file.put_line(FND_FILE.LOG,' ');
1243
1244 OKC_XPRT_UTIL_PVT.create_production_publication
1245 (
1246 p_calling_mode => 'PUBLISH',
1247 p_template_id => NULL, -- pick all templates for the current request
1248 x_return_status => x_return_status,
1249 x_msg_data => x_msg_data,
1250 x_msg_count => x_msg_count
1251 );
1252
1253 fnd_file.put_line(FND_FILE.LOG,' ');
1254 fnd_file.put_line(FND_FILE.LOG,'After Calling the Production Publication API ');
1255 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1256 fnd_file.put_line(FND_FILE.LOG,' ');
1257
1258 --- If any errors happen abort API
1259 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1260 -- Added for Bug 4757731
1261 FOR csr_pub_rule_rec IN csr_pub_rule_list
1262 LOOP
1263 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1264 UPDATE okc_xprt_rule_hdrs_all
1265 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1266 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1267 AND rule_id = csr_pub_rule_rec.rule_id;
1268 l_rule_exists_flag := 'F';
1269 END LOOP;
1270 COMMIT;
1271 -- Added for Bug 4757731
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273
1274 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1275 -- Added for Bug 4757731
1276 FOR csr_pub_rule_rec IN csr_pub_rule_list
1277 LOOP
1278 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1279 UPDATE okc_xprt_rule_hdrs_all
1280 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1281 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1282 AND rule_id = csr_pub_rule_rec.rule_id;
1283 l_rule_exists_flag := 'F';
1284 END LOOP;
1285 COMMIT;
1286 -- Added for Bug 4757731
1287 RAISE FND_API.G_EXC_ERROR;
1288 END IF;
1289
1290 -- Standard call to get message count and if count is 1, get message info.
1291 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1292
1293 -- end debug log
1294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1296 G_MODULE||l_api_name,
1297 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1298 END IF;
1299
1300 END IF; -- req_data IS NULL
1301
1302 ELSE
1303
1304 -- Code for the OKC Rules Engine
1305 -- start debug log
1306 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1307 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1308 G_MODULE||l_api_name,
1309 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1310 END IF;
1311
1312 -- log file
1313 fnd_file.put_line(FND_FILE.LOG,' ');
1314 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
1315 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
1316 fnd_file.put_line(FND_FILE.LOG,'Org Id : '||p_org_id);
1317
1318 /*
1319 Step 1: Update current request Id for all rules to be published
1320 */
1321
1322 fnd_file.put_line(FND_FILE.LOG,' ');
1323 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1324 fnd_file.put_line(FND_FILE.LOG,'Step 1: Updating request_id for rules to be published ');
1325 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1326 fnd_file.put_line(FND_FILE.LOG,' ');
1327
1328 -- Update request_id for all Rules in Pending Publication for current org id
1329 UPDATE okc_xprt_rule_hdrs_all
1330 SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
1331 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
1332 program_application_id = FND_GLOBAL.PROG_APPL_ID,
1333 program_update_date = SYSDATE,
1334 last_update_login = FND_GLOBAL.LOGIN_ID,
1335 last_update_date = SYSDATE,
1336 last_updated_by = FND_GLOBAL.USER_ID
1337 WHERE org_id = p_org_id
1338 AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
1339 intent,
1340 fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
1341 )
1342 AND status_code = 'PENDINGPUB';
1343
1344 -- Check If any rules are to be processed else exit
1345 IF SQL%NOTFOUND THEN
1346 -- exit as no rules to be processed
1347 fnd_file.put_line(FND_FILE.LOG,' ');
1348 fnd_file.put_line(FND_FILE.LOG,'No Rules to be processed ');
1349 fnd_file.put_line(FND_FILE.LOG,' ');
1350
1351 retcode := 0;
1352 errbuf := '';
1353 RETURN;
1354
1355 END IF; -- no rows to be processed
1356
1357 -- commit the data
1358 COMMIT WORK;
1359
1360 /*
1361 Step 1.1: Rules QA checks
1362 */
1363
1364 fnd_file.put_line(FND_FILE.LOG,' ');
1365 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1366 fnd_file.put_line(FND_FILE.LOG,'Step 1.1: Rules QA Checks new ');
1367 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1368 fnd_file.put_line(FND_FILE.LOG,' ');
1369
1370 OKC_XPRT_UTIL_PVT.check_rules_validity
1371 (
1372 p_qa_mode => 'PUBLISH',
1373 p_template_id => NULL,
1374 x_sequence_id => l_sequence_id,
1375 x_qa_status => l_qa_status,
1376 x_return_status => x_return_status,
1377 x_msg_data => x_msg_data,
1378 x_msg_count => x_msg_count
1379 ) ;
1380
1381 fnd_file.put_line(FND_FILE.LOG,' ');
1382 fnd_file.put_line(FND_FILE.LOG,'After OKC_XPRT_UTIL_PVT.check_rules_validity');
1383 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1384 fnd_file.put_line(FND_FILE.LOG,'x_qa_status: '||l_qa_status);
1385 fnd_file.put_line(FND_FILE.LOG,'x_sequence_id: '||l_sequence_id);
1386 fnd_file.put_line(FND_FILE.LOG,' ');
1387
1388 --- If any errors happen abort API
1389 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1391 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1392 RAISE FND_API.G_EXC_ERROR;
1393 END IF;
1394
1395 fnd_file.put_line(FND_FILE.LOG,'after');
1396
1397 -- Check If any rules had QA errors and abort
1398 IF l_qa_status <> 'S' THEN
1399 fnd_file.put_line(FND_FILE.LOG,'in');
1400 -- exit as no rules had QA errors
1401 fnd_file.put_line(FND_FILE.LOG,' ');
1402 fnd_file.put_line(FND_FILE.LOG,'Rules QA Check failed');
1403 fnd_file.put_line(FND_FILE.LOG,' ');
1404
1405
1406 -- Added for Bug 4103931
1407 FOR csr_pub_rule_rec IN csr_pub_rule_list
1408 LOOP
1409 l_rule_exists_flag := check_rule_active(csr_pub_rule_rec.rule_id);
1410 UPDATE okc_xprt_rule_hdrs_all
1411 SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
1412 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1413 AND rule_id = csr_pub_rule_rec.rule_id;
1414 l_rule_exists_flag := 'F';
1415 END LOOP;
1416
1417
1418 -- Added for Bug 4690232
1419 COMMIT;
1420
1421 retcode := 2;
1422 errbuf := '';
1423 fnd_file.put_line(FND_FILE.LOG,'Return');
1424 RETURN;
1425
1426 END IF; -- QA Errors
1427
1428
1429 fnd_file.put_line(FND_FILE.LOG,' ');
1430 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1431 fnd_file.put_line(FND_FILE.LOG,'Synchronize templates with rules ');
1432 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1433 fnd_file.put_line(FND_FILE.LOG,' ');
1434
1435
1436 sync_templates
1437 (
1438 p_mode => 'P',
1439 x_return_status => x_return_status,
1440 x_msg_data => x_msg_data,
1441 x_msg_count => x_msg_count
1442 );
1443
1444 -- Update all the Question order records that have question_rule_status as PENDINGPUB
1445 UPDATE Okc_Xprt_Question_Orders
1446 SET runtime_available_flag = 'Y',
1447 question_rule_status = 'ACTIVE',
1448 last_updated_by = FND_GLOBAL.USER_ID,
1449 last_update_date = SYSDATE,
1450 last_update_login = FND_GLOBAL.LOGIN_ID
1451 WHERE question_rule_status = 'PENDINGPUB'
1452 AND template_id IN ( SELECT template_id
1453 FROM okc_terms_templates_all
1454 WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
1455 );
1456
1457 -- Delete from okc_xprt_template_rules
1458 DELETE FROM okc_xprt_template_rules
1459 WHERE NVL(deleted_flag,'N') = 'Y'
1460 AND template_id IN ( SELECT template_id
1461 FROM okc_terms_templates_all
1462 WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
1463 );
1464
1465 -- Update published_flag in okc_xprt_template_rules
1466 UPDATE okc_xprt_template_rules
1467 SET published_flag = 'Y'
1468 WHERE template_id IN ( SELECT template_id
1469 FROM okc_terms_templates_all
1470 WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
1471 );
1472 -- Update Rule status
1473 OPEN csr_rules;
1474 LOOP
1475 FETCH csr_rules INTO l_rule_id;
1476 EXIT WHEN csr_rules%NOTFOUND;
1477
1478 SELECT okc_xprt_util_pvt.is_rule_line_level(l_rule_id) INTO l_line_level_flag FROM DUAL;
1479
1480 UPDATE okc_xprt_rule_hdrs_all
1481 SET status_code = 'ACTIVE',
1482 published_flag = 'Y',
1483 line_level_flag = l_line_level_flag, --is_rule_line_level(l_rule_id),
1484 last_updated_by = FND_GLOBAL.USER_ID,
1485 last_update_date = SYSDATE,
1486 last_update_login = FND_GLOBAL.LOGIN_ID,
1487 activation_date = SYSDATE
1488 WHERE rule_id = l_rule_id;
1489
1490 DELETE FROM okc_xprt_rule_outcomes_active WHERE rule_id = l_rule_id;
1491
1492 DELETE FROM okc_xprt_rule_cond_vals_active WHERE rule_condition_id IN (SELECT rule_condition_id FROM okc_xprt_rule_conditions WHERE rule_id = l_rule_id);
1493
1494 DELETE FROM okc_xprt_rule_cond_active WHERE rule_id = l_rule_id;
1495
1496 DELETE FROM okc_xprt_rule_hdrs_all_active WHERE rule_id = l_rule_id;
1497
1498 END LOOP;
1499 CLOSE csr_rules;
1500
1501 COMMIT WORK;
1502
1503
1504
1505 END IF;
1506
1507 EXCEPTION
1508 WHEN FND_API.G_EXC_ERROR THEN
1509 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1510 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1511 G_MODULE||l_api_name,
1512 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1513 END IF;
1514
1515 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1516 retcode := 2;
1517 errbuf := x_msg_data;
1518 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_ERROR);
1519 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
1520 fnd_file.put_line(FND_FILE.LOG,' ');
1521
1522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1524 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1525 G_MODULE||l_api_name,
1526 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1527 END IF;
1528
1529 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1530 retcode := 2;
1531 errbuf := x_msg_data;
1532 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
1533 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
1534 fnd_file.put_line(FND_FILE.LOG,' ');
1535
1536 WHEN OTHERS THEN
1537 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1538 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1539 G_MODULE||l_api_name,
1540 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1541 END IF;
1542
1543 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1544 retcode := 2;
1545 errbuf := SQLERRM;
1546 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
1547 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
1548 fnd_file.put_line(FND_FILE.LOG,' ');
1549
1550 END publish_rules;
1551
1552 ---------------------------------------------------
1553 -- Procedure: This procedure will be registered as
1554 -- Contract Expert Rules Disable concurrent program
1555 ---------------------------------------------------
1556 PROCEDURE disable_rules
1557 (
1558 errbuf OUT NOCOPY VARCHAR2,
1559 retcode OUT NOCOPY VARCHAR2,
1560 p_org_id IN NUMBER
1561 ) IS
1562
1563 x_return_status VARCHAR2(1);
1564 x_msg_data VARCHAR2(4000);
1565 x_msg_count NUMBER;
1566 l_api_name CONSTANT VARCHAR2(30) := 'disable_rules';
1567 req_data VARCHAR2(240);
1568 x_cz_cp_status BOOLEAN;
1569 x_phase VARCHAR2(1000);
1570 x_status VARCHAR2(1000);
1571 x_dev_phase VARCHAR2(1000);
1572 x_dev_status VARCHAR2(1000);
1573 x_message VARCHAR2(1000);
1574 l_rules_cnt NUMBER;
1575 l_conc_pgm_desc FND_NEW_MESSAGES.message_text%TYPE;
1576
1577 CURSOR csr_get_child_req_dtls IS
1578 SELECT SUBSTR(req_data,
1579 1,
1580 INSTR(req_data,':',1) -1
1581 ) child_req_id,
1582 SUBSTR(req_data,
1583 INSTR(req_data,':',1) + 1
1584 ) run_id
1585 FROM dual;
1586
1587 CURSOR csr_cz_imp_rules_cnt IS
1588 SELECT COUNT(*)
1589 FROM cz_imp_rules
1590 WHERE run_id = G_RUN_ID;
1591
1592 -- Added for 4757731
1593 CURSOR csr_dis_rule_list IS
1594 SELECT rule_id
1595 FROM okc_xprt_rule_hdrs_all
1596 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
1597
1598 l_rule_exists_flag VARCHAR2(1) := NULL;
1599
1600 l_okc_rules_engine VARCHAR2(1);
1601
1602 l_rule_id NUMBER;
1603
1604 l_line_level_flag okc_xprt_rule_hdrs_all.line_level_flag%TYPE;
1605
1606 CURSOR csr_rules IS
1607 SELECT rule_id
1608 FROM okc_xprt_rule_hdrs_all
1609 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1610
1611
1612
1613 BEGIN
1614
1615 SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
1616
1617 fnd_file.put_line(FND_FILE.LOG,'Using OKC Rules Engine'||l_okc_rules_engine);
1618
1619 IF Nvl(l_okc_rules_engine,'N') = 'N' THEN
1620
1621 -- Check if the concurrent program is being restarted due to completion of child request
1622 req_data := fnd_conc_global.request_data;
1623
1624 IF req_data IS NULL THEN
1625 -- Calling the parent concurrent prorgam for the first time
1626 -- Execute Steps 1 to 4
1627
1628 -- start debug log
1629 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1630 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1631 G_MODULE||l_api_name,
1632 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1633 END IF;
1634
1635 -- log file
1636 fnd_file.put_line(FND_FILE.LOG,' ');
1637 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
1638 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
1639 fnd_file.put_line(FND_FILE.LOG,'Org Id : '||p_org_id);
1640
1641 /*
1642 Step 1: Update current request Id for all rules to be published
1643 */
1644
1645 fnd_file.put_line(FND_FILE.LOG,' ');
1646 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1647 fnd_file.put_line(FND_FILE.LOG,'Step 1: Updating request_id for rules to be published ');
1648 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1649 fnd_file.put_line(FND_FILE.LOG,' ');
1650
1651 -- Update request_id for all Rules in Pending Publication for current org id
1652 UPDATE okc_xprt_rule_hdrs_all
1653 SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
1654 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
1655 program_application_id = FND_GLOBAL.PROG_APPL_ID,
1656 program_update_date = SYSDATE,
1657 last_update_login = FND_GLOBAL.LOGIN_ID,
1658 last_update_date = SYSDATE,
1659 last_updated_by = FND_GLOBAL.USER_ID
1660 WHERE org_id = p_org_id
1661 AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
1662 intent,
1663 fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
1664 )
1665 AND status_code = 'PENDINGDISABLE';
1666
1667 -- Check If any rules are to be processed else exit
1668 IF SQL%NOTFOUND THEN
1669 -- exit as no rules to be processed
1670 fnd_file.put_line(FND_FILE.LOG,' ');
1671 fnd_file.put_line(FND_FILE.LOG,'No Rules to be processed ');
1672 fnd_file.put_line(FND_FILE.LOG,' ');
1673
1674 retcode := 0;
1675 errbuf := '';
1676 RETURN;
1677
1678 END IF; -- no rows to be processed
1679
1680 -- commit the data
1681 COMMIT WORK;
1682
1683 /*
1684 Step 2: Import Template Model(s)
1685 */
1686
1687 fnd_file.put_line(FND_FILE.LOG,' ');
1688 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1689 fnd_file.put_line(FND_FILE.LOG,'Step 2: Importing Template Models');
1690 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1691 fnd_file.put_line(FND_FILE.LOG,' ');
1692
1693 OKC_XPRT_IMPORT_TEMPLATE_PVT.rebuild_tmpl_pub_disable
1694 (
1695 x_return_status => x_return_status,
1696 x_msg_data => x_msg_data,
1697 x_msg_count => x_msg_count
1698 ) ;
1699
1700 fnd_file.put_line(FND_FILE.LOG,' ');
1701 fnd_file.put_line(FND_FILE.LOG,'After Importing Template Models');
1702 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1703 fnd_file.put_line(FND_FILE.LOG,' ');
1704
1705 --- If any errors happen abort API
1706 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1707 -- Added for Bug 4757731
1708 FOR csr_dis_rule_rec IN csr_dis_rule_list
1709 LOOP
1710 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1711 UPDATE okc_xprt_rule_hdrs_all
1712 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1713 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1714 AND rule_id = csr_dis_rule_rec.rule_id;
1715 l_rule_exists_flag := 'F';
1716 END LOOP;
1717 COMMIT;
1718 -- Added for Bug 4757731
1719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1720
1721 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1722 -- Added for Bug 4757731
1723 FOR csr_dis_rule_rec IN csr_dis_rule_list
1724 LOOP
1725 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1726 UPDATE okc_xprt_rule_hdrs_all
1727 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1728 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1729 AND rule_id = csr_dis_rule_rec.rule_id;
1730 l_rule_exists_flag := 'F';
1731 END LOOP;
1732 COMMIT;
1733 -- Added for Bug 4757731
1734 RAISE FND_API.G_EXC_ERROR;
1735 END IF;
1736
1737
1738 /*
1739 Step 3: Populate cz_imp_rules
1740 */
1741
1742 fnd_file.put_line(FND_FILE.LOG,' ');
1743 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1744 fnd_file.put_line(FND_FILE.LOG,'Step 3: Populating cz_imp_rules ');
1745 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1746 fnd_file.put_line(FND_FILE.LOG,' ');
1747
1748 OKC_XPRT_IMPORT_RULES_PVT.import_rules_disable
1749 (
1750 x_run_id => G_RUN_ID,
1751 x_return_status => x_return_status,
1752 x_msg_data => x_msg_data,
1753 x_msg_count => x_msg_count
1754 );
1755
1756 fnd_file.put_line(FND_FILE.LOG,' ');
1757 fnd_file.put_line(FND_FILE.LOG,'After Populating cz_imp_rules');
1758 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1759 fnd_file.put_line(FND_FILE.LOG,'Rule Import Run Id : '||G_RUN_ID);
1760 fnd_file.put_line(FND_FILE.LOG,' ');
1761
1762 --- If any errors happen abort API
1763 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1764 -- Added for Bug 4757731
1765 FOR csr_dis_rule_rec IN csr_dis_rule_list
1766 LOOP
1767 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1768 UPDATE okc_xprt_rule_hdrs_all
1769 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1770 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1771 AND rule_id = csr_dis_rule_rec.rule_id;
1772 l_rule_exists_flag := 'F';
1773 END LOOP;
1774 COMMIT;
1775 -- Added for Bug 4757731
1776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1777
1778 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1779 -- Added for Bug 4757731
1780 FOR csr_dis_rule_rec IN csr_dis_rule_list
1781 LOOP
1782 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1783 UPDATE okc_xprt_rule_hdrs_all
1784 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1785 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1786 AND rule_id = csr_dis_rule_rec.rule_id;
1787 l_rule_exists_flag := 'F';
1788 END LOOP;
1789 COMMIT;
1790 -- Added for Bug 4757731
1791 RAISE FND_API.G_EXC_ERROR;
1792 END IF;
1793 /*
1794 Step 3.1: Count Rules to be imported
1795 Check if there are any records in cz_imp_rules
1796 If there are no records in cz_imp_rules then there were no expert enabled templates
1797 attached to the rule. Just change the status of the rule to INACTIVE
1798 */
1799
1800 fnd_file.put_line(FND_FILE.LOG,' ');
1801 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1802 fnd_file.put_line(FND_FILE.LOG,'Step 3.1:Count Rules to be imported');
1803 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1804 fnd_file.put_line(FND_FILE.LOG,' ');
1805
1806 OPEN csr_cz_imp_rules_cnt;
1807 FETCH csr_cz_imp_rules_cnt INTO l_rules_cnt;
1808 CLOSE csr_cz_imp_rules_cnt;
1809
1810 fnd_file.put_line(FND_FILE.LOG,' ');
1811 fnd_file.put_line(FND_FILE.LOG,'Total Rules to be Imported to CZ: '||l_rules_cnt);
1812 fnd_file.put_line(FND_FILE.LOG,' ');
1813
1814
1815 IF l_rules_cnt = 0 THEN
1816
1817 OKC_XPRT_UTIL_PVT.publish_rule_with_no_tmpl
1818 (
1819 p_calling_mode => 'DISABLE',
1820 x_return_status => x_return_status,
1821 x_msg_data => x_msg_data,
1822 x_msg_count => x_msg_count
1823 );
1824
1825 fnd_file.put_line(FND_FILE.LOG,' ');
1826 fnd_file.put_line(FND_FILE.LOG,'After OKC_XPRT_UTIL_PVT.publish_rule_with_no_tmpl');
1827 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
1828 fnd_file.put_line(FND_FILE.LOG,' ');
1829
1830 --- If any errors happen abort API
1831 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1832 -- Added for Bug 4757731
1833 FOR csr_dis_rule_rec IN csr_dis_rule_list
1834 LOOP
1835 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1836 UPDATE okc_xprt_rule_hdrs_all
1837 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1838 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1839 AND rule_id = csr_dis_rule_rec.rule_id;
1840 l_rule_exists_flag := 'F';
1841 END LOOP;
1842 COMMIT;
1843 -- Added for Bug 4757731
1844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1845
1846 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1847 -- Added for Bug 4757731
1848 FOR csr_dis_rule_rec IN csr_dis_rule_list
1849 LOOP
1850 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1851 UPDATE okc_xprt_rule_hdrs_all
1852 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1853 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1854 AND rule_id = csr_dis_rule_rec.rule_id;
1855 l_rule_exists_flag := 'F';
1856 END LOOP;
1857 COMMIT;
1858 -- Added for Bug 4757731
1859 RAISE FND_API.G_EXC_ERROR;
1860 END IF;
1861
1862 fnd_file.put_line(FND_FILE.LOG,' ');
1863 fnd_file.put_line(FND_FILE.LOG,'Rules has No expert template attached');
1864 fnd_file.put_line(FND_FILE.LOG,' ');
1865
1866 retcode := 0;
1867 errbuf := '';
1868 RETURN;
1869
1870 END IF; -- l_rules_cnt = 0
1871
1872 /*
1873 Step 4: Call the CZ Rule Import Concurrent Program
1874 CZ Pgm: Import Configuration Rules (CZRULEIMPORTCP)
1875 */
1876
1877 fnd_file.put_line(FND_FILE.LOG,' ');
1878 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1879 fnd_file.put_line(FND_FILE.LOG,'Step 4: Calling the CZ Rule Import Concurrent Program');
1880 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1881 fnd_file.put_line(FND_FILE.LOG,' ');
1882 fnd_file.put_line(FND_FILE.LOG,'Parameter Run Id : '||G_RUN_ID);
1883
1884 FND_MESSAGE.set_name('OKC','OKC_XPRT_RULE_CCPRG_DIS_MSG');
1885 l_conc_pgm_desc := FND_MESSAGE.get;
1886
1887 G_REQUEST_ID := fnd_request.submit_request
1888 (application => 'CZ',
1889 program => 'CZRULEIMPORTCP',
1890 description => l_conc_pgm_desc,
1891 start_time => NULL,
1892 sub_request => TRUE,
1893 argument1 => G_RUN_ID
1894 );
1895
1896 fnd_file.put_line(FND_FILE.LOG,'Request Id of CZ Rule Concurrent Pgm : '||G_REQUEST_ID);
1897
1898 -- commit the data
1899 COMMIT WORK;
1900
1901 IF NVL(G_REQUEST_ID,0) = 0 THEN
1902 -- Could Not submit Conc Pgm
1903 fnd_message.set_name('OKC','OKC_XPRT_SUB_CZ_RUL_PGM');
1904 fnd_file.put_line(FND_FILE.LOG,'Could NOT submit CZ Concurrent Program');
1905 FOR csr_dis_rule_rec IN csr_dis_rule_list
1906 LOOP
1907 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1908 UPDATE okc_xprt_rule_hdrs_all
1909 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1910 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1911 AND rule_id = csr_dis_rule_rec.rule_id;
1912 l_rule_exists_flag := 'F';
1913 END LOOP;
1914 COMMIT;
1915 -- Added for Bug 4757731
1916 RAISE FND_API.G_EXC_ERROR;
1917 ELSE
1918 -- child submission successful, pause the parent program
1919 fnd_conc_global.set_req_globals
1920 ( conc_status => 'PAUSED',
1921 request_data => to_char(G_REQUEST_ID)||':'||to_char(G_RUN_ID)
1922 );
1923 END IF; -- child submitted successfully
1924
1925 ELSE
1926 -- req_data IS NOT NULL
1927 -- Restarting the Parent Concurrent Program after completing the child
1928 -- Execute the remaing steps
1929 /*
1930 Note : when the Parent pgm resumes, the session id for the pgm is different
1931 then the original session id. Any variables set before cannot be read
1932 Any variables required in this block are put in request_data and read from
1933 the same
1934
1935 */
1936
1937 OPEN csr_get_child_req_dtls;
1938 FETCH csr_get_child_req_dtls INTO G_REQUEST_ID, G_RUN_ID;
1939 CLOSE csr_get_child_req_dtls;
1940
1941 x_cz_cp_status := fnd_concurrent.get_request_status
1942 (G_REQUEST_ID,
1943 NULL,
1944 NULL,
1945 x_phase,
1946 x_status,
1947 x_dev_phase,
1948 x_dev_status,
1949 x_message
1950 );
1951
1952 fnd_file.put_line(FND_FILE.LOG,' ');
1953 fnd_file.put_line(FND_FILE.LOG,'After Completing CZ Rule Conc Pgm with request id :'||G_REQUEST_ID);
1954 fnd_file.put_line(FND_FILE.LOG,'Run Id : '||G_RUN_ID);
1955 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1956 fnd_file.put_line(FND_FILE.LOG,'Results ');
1957 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1958 fnd_file.put_line(FND_FILE.LOG,'Phase : '||x_phase);
1959 fnd_file.put_line(FND_FILE.LOG,'Status : '||x_status);
1960 fnd_file.put_line(FND_FILE.LOG,'dev_phase : '||x_dev_phase);
1961 fnd_file.put_line(FND_FILE.LOG,'dev_status : '||x_dev_status);
1962 fnd_file.put_line(FND_FILE.LOG,'Message : '||substr(x_message,1,100));
1963 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1964 fnd_file.put_line(FND_FILE.LOG,' ');
1965
1966 IF x_dev_phase <> 'COMPLETED' AND
1967 x_dev_status NOT IN ('NORMAL','WARNING') THEN
1968 -- error in CZ Concurrent Program
1969 fnd_file.put_line(FND_FILE.LOG, 'Error in CZ Rule Import Concurrent Program ');
1970 fnd_file.put_line(FND_FILE.LOG, 'Request Id :'||G_REQUEST_ID);
1971 fnd_file.put_line(FND_FILE.LOG, 'Run Id :'||G_RUN_ID);
1972 FOR csr_dis_rule_rec IN csr_dis_rule_list
1973 LOOP
1974 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
1975 UPDATE okc_xprt_rule_hdrs_all
1976 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
1977 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
1978 AND rule_id = csr_dis_rule_rec.rule_id;
1979 l_rule_exists_flag := 'F';
1980 END LOOP;
1981 COMMIT;
1982 RAISE FND_API.G_EXC_ERROR;
1983 END IF;
1984
1985
1986 /*
1987 Step 5: Check status of Rules Imported
1988 */
1989
1990 fnd_file.put_line(FND_FILE.LOG,' ');
1991 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1992 fnd_file.put_line(FND_FILE.LOG,'Step 5: Checking Status of Rules imported');
1993 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
1994 fnd_file.put_line(FND_FILE.LOG,' ');
1995
1996 OKC_XPRT_UTIL_PVT.check_import_status
1997 (
1998 p_run_id => G_RUN_ID,
1999 p_import_status => 'S',
2000 p_model_type => 'R', -- Rules Import
2001 x_return_status => x_return_status,
2002 x_msg_data => x_msg_data,
2003 x_msg_count => x_msg_count
2004 );
2005
2006 fnd_file.put_line(FND_FILE.LOG,' ');
2007 fnd_file.put_line(FND_FILE.LOG,'After checking import status');
2008 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2009 fnd_file.put_line(FND_FILE.LOG,' ');
2010
2011 --- If any errors happen abort API
2012 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2013 -- Added for Bug 4757731
2014 FOR csr_dis_rule_rec IN csr_dis_rule_list
2015 LOOP
2016 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
2017 UPDATE okc_xprt_rule_hdrs_all
2018 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
2019 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2020 AND rule_id = csr_dis_rule_rec.rule_id;
2021 l_rule_exists_flag := 'F';
2022 END LOOP;
2023 COMMIT;
2024 -- Added for Bug 4757731
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2026
2027 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2028 -- Added for Bug 4757731
2029 FOR csr_dis_rule_rec IN csr_dis_rule_list
2030 LOOP
2031 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
2032 UPDATE okc_xprt_rule_hdrs_all
2033 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
2034 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2035 AND rule_id = csr_dis_rule_rec.rule_id;
2036 l_rule_exists_flag := 'F';
2037 END LOOP;
2038 COMMIT;
2039 -- Added for Bug 4757731
2040 RAISE FND_API.G_EXC_ERROR;
2041 END IF;
2042
2043 /*
2044 Step 6: Call the Test Publication API
2045 */
2046
2047 fnd_file.put_line(FND_FILE.LOG,' ');
2048 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2049 fnd_file.put_line(FND_FILE.LOG,'Step 6: Calling the Test Publication API');
2050 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2051 fnd_file.put_line(FND_FILE.LOG,' ');
2052
2053
2054 OKC_XPRT_UTIL_PVT.create_test_publication
2055 (
2056 x_return_status => x_return_status,
2057 x_msg_data => x_msg_data,
2058 x_msg_count => x_msg_count
2059 );
2060
2061 fnd_file.put_line(FND_FILE.LOG,' ');
2062 fnd_file.put_line(FND_FILE.LOG,'After Calling the Test Publication API ');
2063 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2064 fnd_file.put_line(FND_FILE.LOG,' ');
2065
2066 --- If any errors happen abort API
2067 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2068 -- Added for Bug 4757731
2069 FOR csr_dis_rule_rec IN csr_dis_rule_list
2070 LOOP
2071 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
2072 UPDATE okc_xprt_rule_hdrs_all
2073 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
2074 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2075 AND rule_id = csr_dis_rule_rec.rule_id;
2076 l_rule_exists_flag := 'F';
2077 END LOOP;
2078 COMMIT;
2079 -- Added for Bug 4757731
2080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081
2082 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2083 -- Added for Bug 4757731
2084 FOR csr_dis_rule_rec IN csr_dis_rule_list
2085 LOOP
2086 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
2087 UPDATE okc_xprt_rule_hdrs_all
2088 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
2089 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2090 AND rule_id = csr_dis_rule_rec.rule_id;
2091 l_rule_exists_flag := 'F';
2092 END LOOP;
2093 COMMIT;
2094 -- Added for Bug 4757731
2095 RAISE FND_API.G_EXC_ERROR;
2096 END IF;
2097
2098 /*
2099 Step 7: Call the Production Publication API
2100 */
2101
2102 fnd_file.put_line(FND_FILE.LOG,' ');
2103 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2104 fnd_file.put_line(FND_FILE.LOG,'Step 7: Calling the Production Publication API');
2105 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2106 fnd_file.put_line(FND_FILE.LOG,' ');
2107
2108 OKC_XPRT_UTIL_PVT.create_production_publication
2109 (
2110 p_calling_mode => 'DISABLE',
2111 p_template_id => NULL, -- pick all templates for the current request
2112 x_return_status => x_return_status,
2113 x_msg_data => x_msg_data,
2114 x_msg_count => x_msg_count
2115 );
2116
2117 fnd_file.put_line(FND_FILE.LOG,' ');
2118 fnd_file.put_line(FND_FILE.LOG,'After Calling the Production Publication API ');
2119 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2120 fnd_file.put_line(FND_FILE.LOG,' ');
2121
2122 --- If any errors happen abort API
2123 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2124 -- Added for Bug 4757731
2125 FOR csr_dis_rule_rec IN csr_dis_rule_list
2126 LOOP
2127 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
2128 UPDATE okc_xprt_rule_hdrs_all
2129 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
2130 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2131 AND rule_id = csr_dis_rule_rec.rule_id;
2132 l_rule_exists_flag := 'F';
2133 END LOOP;
2134 COMMIT;
2135 -- Added for Bug 4757731
2136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2137
2138 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2139 -- Added for Bug 4757731
2140 FOR csr_dis_rule_rec IN csr_dis_rule_list
2141 LOOP
2142 l_rule_exists_flag := check_rule_active(csr_dis_rule_rec.rule_id);
2143 UPDATE okc_xprt_rule_hdrs_all
2144 SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
2145 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2146 AND rule_id = csr_dis_rule_rec.rule_id;
2147 l_rule_exists_flag := 'F';
2148 END LOOP;
2149 COMMIT;
2150 -- Added for Bug 4757731
2151 RAISE FND_API.G_EXC_ERROR;
2152 END IF;
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171 -- Standard call to get message count and if count is 1, get message info.
2172 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2173
2174 -- end debug log
2175 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2177 G_MODULE||l_api_name,
2178 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2179 END IF;
2180
2181 END IF; -- req_data IS NULL
2182
2183 ELSE
2184 -- New OKC Rules Engine
2185
2186 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2187 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2188 G_MODULE||l_api_name,
2189 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2190 END IF;
2191
2192 -- log file
2193 fnd_file.put_line(FND_FILE.LOG,' ');
2194 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
2195 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
2196 fnd_file.put_line(FND_FILE.LOG,'Org Id : '||p_org_id);
2197
2198 /*
2199 Step 1: Update current request Id for all rules to be published
2200 */
2201
2202 fnd_file.put_line(FND_FILE.LOG,' ');
2203 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2204 fnd_file.put_line(FND_FILE.LOG,'Step 1: Updating request_id for rules to be published ');
2205 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2206 fnd_file.put_line(FND_FILE.LOG,' ');
2207
2208 -- Update request_id for all Rules in Pending Publication for current org id
2209 UPDATE okc_xprt_rule_hdrs_all
2210 SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
2211 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
2212 program_application_id = FND_GLOBAL.PROG_APPL_ID,
2213 program_update_date = SYSDATE,
2214 last_update_login = FND_GLOBAL.LOGIN_ID,
2215 last_update_date = SYSDATE,
2216 last_updated_by = FND_GLOBAL.USER_ID
2217 WHERE org_id = p_org_id
2218 AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
2219 intent,
2220 fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
2221 )
2222 AND status_code = 'PENDINGDISABLE';
2223
2224 -- Check If any rules are to be processed else exit
2225 IF SQL%NOTFOUND THEN
2226 -- exit as no rules to be processed
2227 fnd_file.put_line(FND_FILE.LOG,' ');
2228 fnd_file.put_line(FND_FILE.LOG,'No Rules to be processed ');
2229 fnd_file.put_line(FND_FILE.LOG,' ');
2230
2231 retcode := 0;
2232 errbuf := '';
2233 RETURN;
2234
2235 END IF; -- no rows to be processed
2236
2237 -- commit the data
2238 COMMIT WORK;
2239
2240 fnd_file.put_line(FND_FILE.LOG,' ');
2241 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2242 fnd_file.put_line(FND_FILE.LOG,'Synchronize templates with rules ');
2243 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2244 fnd_file.put_line(FND_FILE.LOG,' ');
2245
2246
2247 sync_templates
2248 (
2249 p_mode => 'D',
2250 x_return_status => x_return_status,
2251 x_msg_data => x_msg_data,
2252 x_msg_count => x_msg_count
2253 );
2254
2255
2256
2257 -- Update Rule status
2258 OPEN csr_rules;
2259 LOOP
2260 FETCH csr_rules INTO l_rule_id;
2261 EXIT WHEN csr_rules%NOTFOUND;
2262
2263 SELECT okc_xprt_util_pvt.is_rule_line_level(l_rule_id) INTO l_line_level_flag FROM DUAL;
2264
2265 UPDATE okc_xprt_rule_hdrs_all
2266 SET status_code = 'INACTIVE',
2267 published_flag = 'Y',
2268 line_level_flag = l_line_level_flag, --is_rule_line_level(l_rule_id),
2269 last_updated_by = FND_GLOBAL.USER_ID,
2270 last_update_date = SYSDATE,
2271 last_update_login = FND_GLOBAL.LOGIN_ID
2272 WHERE rule_id = l_rule_id;
2273
2274 DELETE FROM okc_xprt_rule_outcomes_active WHERE rule_id = l_rule_id;
2275
2276 DELETE FROM okc_xprt_rule_cond_vals_active WHERE rule_condition_id IN (SELECT rule_condition_id FROM okc_xprt_rule_conditions WHERE rule_id = l_rule_id);
2277
2278 DELETE FROM okc_xprt_rule_cond_active WHERE rule_id = l_rule_id;
2279
2280 DELETE FROM okc_xprt_rule_hdrs_all_active WHERE rule_id = l_rule_id;
2281
2282 END LOOP;
2283 CLOSE csr_rules;
2284
2285 COMMIT WORK;
2286
2287
2288
2289 END IF;
2290
2291 EXCEPTION
2292 WHEN FND_API.G_EXC_ERROR THEN
2293 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2294 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2295 G_MODULE||l_api_name,
2296 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2297 END IF;
2298
2299 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2300 retcode := 2;
2301 errbuf := x_msg_data;
2302 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_ERROR);
2303 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
2304 fnd_file.put_line(FND_FILE.LOG,' ');
2305
2306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2307 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2308 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2309 G_MODULE||l_api_name,
2310 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2311 END IF;
2312
2313 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2314 retcode := 2;
2315 errbuf := x_msg_data;
2316 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
2317 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
2318 fnd_file.put_line(FND_FILE.LOG,' ');
2319
2320 WHEN OTHERS THEN
2321 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2322 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2323 G_MODULE||l_api_name,
2324 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2325 END IF;
2326
2327 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2328 retcode := 2;
2329 errbuf := SQLERRM;
2330 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
2331 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
2332 fnd_file.put_line(FND_FILE.LOG,' ');
2333
2334 END disable_rules;
2335
2336 ---------------------------------------------------
2337 -- Procedure: This procedure will be registered as
2338 -- Contract Expert Template Sync concurrent program
2339 ---------------------------------------------------
2340 PROCEDURE rebuild_templates
2341 (
2342 errbuf OUT NOCOPY VARCHAR2,
2343 retcode OUT NOCOPY VARCHAR2,
2344 p_org_id IN NUMBER,
2345 p_intent IN VARCHAR2,
2346 p_template_id IN NUMBER DEFAULT NULL
2347 ) IS
2348
2349 CURSOR csr_tmpl_dtls IS
2350 SELECT 'x'
2351 FROM okc_terms_templates_all t
2352 WHERE t.org_id = p_org_id
2353 AND t.intent = p_intent
2354 AND t.template_id = NVL(p_template_id, template_id);
2355
2356 CURSOR csr_active_rules_cnt IS
2357 SELECT COUNT(*)
2358 FROM okc_xprt_rule_hdrs_all
2359 WHERE org_id = p_org_id
2360 AND intent = p_intent
2361 AND status_code = 'ACTIVE';
2362
2363 x_return_status VARCHAR2(1);
2364 x_msg_data VARCHAR2(4000);
2365 x_msg_count NUMBER;
2366 l_api_name CONSTANT VARCHAR2(30) := 'rebuild_templates';
2367 l_dummy VARCHAR2(1);
2368 l_rule_count NUMBER;
2369
2370 BEGIN
2371
2372 -- start debug log
2373 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2374 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2375 G_MODULE||l_api_name,
2376 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2377 END IF;
2378
2379 -- log file
2380 fnd_file.put_line(FND_FILE.LOG,' ');
2381 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
2382 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
2383 fnd_file.put_line(FND_FILE.LOG,'Org Id : '||p_org_id);
2384 fnd_file.put_line(FND_FILE.LOG,'Intent : '||p_intent);
2385 fnd_file.put_line(FND_FILE.LOG,'Template Id : '||p_template_id);
2386
2387 -- Validate Parameters if p_template_id IS NOT NULL
2388 OPEN csr_tmpl_dtls;
2389 FETCH csr_tmpl_dtls INTO l_dummy;
2390 IF csr_tmpl_dtls%NOTFOUND THEN
2391 -- exit as no templates to be processed
2392 CLOSE csr_tmpl_dtls;
2393 fnd_file.put_line(FND_FILE.LOG,' ');
2394 fnd_file.put_line(FND_FILE.LOG,'No Templates to be synchronized ');
2395 fnd_file.put_line(FND_FILE.LOG,' ');
2396 retcode := 0;
2397 errbuf := '';
2398 RETURN;
2399 END IF; -- no rows to be processed
2400 CLOSE csr_tmpl_dtls;
2401
2402 -- Validate if there is atleast 1 ACTIVE Rule for the Org and Intent
2403 OPEN csr_active_rules_cnt;
2404 FETCH csr_active_rules_cnt INTO l_rule_count;
2405 CLOSE csr_active_rules_cnt;
2406
2407 IF l_rule_count = 0 THEN
2408 -- no active rules
2409 fnd_file.put_line(FND_FILE.LOG,' ');
2410 fnd_file.put_line(FND_FILE.LOG,'No Active Rules for the Org and Intent');
2411 fnd_file.put_line(FND_FILE.LOG,' ');
2412 retcode := 0;
2413 errbuf := '';
2414 RETURN;
2415 END IF; -- no active rules
2416
2417 /*
2418 Step 1: Import Template Model(s)
2419 */
2420
2421 fnd_file.put_line(FND_FILE.LOG,' ');
2422 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2423 fnd_file.put_line(FND_FILE.LOG,'Step 1: Importing Template Models');
2424 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2425 fnd_file.put_line(FND_FILE.LOG,' ');
2426
2427 OKC_XPRT_IMPORT_TEMPLATE_PVT.rebuild_tmpl_sync
2428 (
2429 p_org_id => p_org_id,
2430 p_intent => p_intent,
2431 p_template_id => p_template_id,
2432 x_return_status => x_return_status,
2433 x_msg_data => x_msg_data,
2434 x_msg_count => x_msg_count
2435 ) ;
2436
2437 fnd_file.put_line(FND_FILE.LOG,' ');
2438 fnd_file.put_line(FND_FILE.LOG,'After Importing Template Models');
2439 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2440 fnd_file.put_line(FND_FILE.LOG,' ');
2441
2442 --- If any errors happen abort API
2443 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2445 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2446 RAISE FND_API.G_EXC_ERROR;
2447 END IF;
2448
2449
2450
2451 /*
2452 Step 2: Call the Test Publication API
2453 */
2454
2455 fnd_file.put_line(FND_FILE.LOG,' ');
2456 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2457 fnd_file.put_line(FND_FILE.LOG,'Step 2: Calling the Test Publication API');
2458 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2459 fnd_file.put_line(FND_FILE.LOG,' ');
2460
2461 OKC_XPRT_UTIL_PVT.create_test_publication
2462 (
2463 x_return_status => x_return_status,
2464 x_msg_data => x_msg_data,
2465 x_msg_count => x_msg_count
2466 );
2467
2468 fnd_file.put_line(FND_FILE.LOG,' ');
2469 fnd_file.put_line(FND_FILE.LOG,'After Calling the Test Publication API ');
2470 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2471 fnd_file.put_line(FND_FILE.LOG,' ');
2472
2473 --- If any errors happen abort API
2474 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2476 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2477 RAISE FND_API.G_EXC_ERROR;
2478 END IF;
2479
2480
2481 /*
2482 Step 3: Call the Production Publication API
2483 */
2484
2485 fnd_file.put_line(FND_FILE.LOG,' ');
2486 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2487 fnd_file.put_line(FND_FILE.LOG,'Step 3: Calling the Production Publication API');
2488 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2489 fnd_file.put_line(FND_FILE.LOG,' ');
2490
2491 OKC_XPRT_UTIL_PVT.create_production_publication
2492 (
2493 p_calling_mode => 'SYNC',
2494 p_template_id => NULL, -- pick all templates for the current request
2495 x_return_status => x_return_status,
2496 x_msg_data => x_msg_data,
2497 x_msg_count => x_msg_count
2498 );
2499
2500 fnd_file.put_line(FND_FILE.LOG,' ');
2501 fnd_file.put_line(FND_FILE.LOG,'After Calling the Production Publication API ');
2502 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2503 fnd_file.put_line(FND_FILE.LOG,' ');
2504
2505 --- If any errors happen abort API
2506 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2508 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2509 RAISE FND_API.G_EXC_ERROR;
2510 END IF;
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528 -- Standard call to get message count and if count is 1, get message info.
2529 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2530
2531 -- end debug log
2532 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2533 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2534 G_MODULE||l_api_name,
2535 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2536 END IF;
2537
2538
2539 EXCEPTION
2540 WHEN FND_API.G_EXC_ERROR THEN
2541 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2542 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2543 G_MODULE||l_api_name,
2544 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2545 END IF;
2546
2547 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2548 retcode := 2;
2549 errbuf := x_msg_data;
2550 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_ERROR);
2551 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
2552 fnd_file.put_line(FND_FILE.LOG,' ');
2553
2554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2555 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2556 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2557 G_MODULE||l_api_name,
2558 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2559 END IF;
2560
2561 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2562 retcode := 2;
2563 errbuf := x_msg_data;
2564 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
2565 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
2566 fnd_file.put_line(FND_FILE.LOG,' ');
2567
2568 WHEN OTHERS THEN
2569 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2570 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2571 G_MODULE||l_api_name,
2572 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2573 END IF;
2574
2575 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2576 retcode := 2;
2577 errbuf := SQLERRM;
2578 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
2579 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
2580 fnd_file.put_line(FND_FILE.LOG,' ');
2581
2582 END rebuild_templates;
2583
2584 ---------------------------------------------------
2585 -- Procedure: This procedure will be registered as
2586 -- Contract Expert Publish Template During Approval
2587 ---------------------------------------------------
2588 PROCEDURE tmpl_approval_publish_rules
2589 (
2590 errbuf OUT NOCOPY VARCHAR2,
2591 retcode OUT NOCOPY VARCHAR2,
2592 p_template_id IN NUMBER
2593 ) IS
2594
2595 x_return_status VARCHAR2(1);
2596 x_msg_data VARCHAR2(4000);
2597 x_msg_count NUMBER;
2598 l_api_name CONSTANT VARCHAR2(30) := 'tmpl_approval_publish_rules';
2599 req_data VARCHAR2(240);
2600 x_cz_cp_status BOOLEAN;
2601 x_phase VARCHAR2(1000);
2602 x_status VARCHAR2(1000);
2603 x_dev_phase VARCHAR2(1000);
2604 x_dev_status VARCHAR2(1000);
2605 x_message VARCHAR2(1000);
2606 l_rules_cnt NUMBER;
2607 l_sequence_id NUMBER;
2608 l_qa_status VARCHAR2(1);
2609 l_conc_pgm_desc FND_NEW_MESSAGES.message_text%TYPE;
2610
2611
2612 CURSOR csr_get_child_req_dtls IS
2613 SELECT SUBSTR(req_data,
2614 1,
2615 INSTR(req_data,':',1) -1
2616 ) child_req_id,
2617 SUBSTR(req_data,
2618 INSTR(req_data,':',1) + 1
2619 ) run_id
2620 FROM dual;
2621
2622 CURSOR csr_cz_imp_rules_cnt IS
2623 SELECT COUNT(*)
2624 FROM cz_imp_rules
2625 WHERE run_id = G_RUN_ID;
2626
2627
2628 l_okc_rules_engine VARCHAR2(1);
2629
2630
2631 BEGIN
2632
2633
2634
2635 SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
2636
2637 fnd_file.put_line(FND_FILE.LOG,'Using OKC Rules Engine'||l_okc_rules_engine);
2638
2639 IF Nvl(l_okc_rules_engine,'N') = 'N' THEN
2640
2641 -- Check if the concurrent program is being restarted due to completion of child request
2642 req_data := fnd_conc_global.request_data;
2643
2644 IF req_data IS NULL THEN
2645 -- Calling the parent concurrent prorgam for the first time
2646 -- Execute Steps 1 to 3
2647
2648 -- start debug log
2649 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2650 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2651 G_MODULE||l_api_name,
2652 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2653 END IF;
2654
2655 -- log file
2656 fnd_file.put_line(FND_FILE.LOG,' ');
2657 fnd_file.put_line(FND_FILE.LOG,'Current Concurrent Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
2658 fnd_file.put_line(FND_FILE.LOG,'Parameters ');
2659 fnd_file.put_line(FND_FILE.LOG,'Template Id : '||p_template_id);
2660
2661 /*
2662 Step 0: Template Rules QA checks
2663 */
2664
2665 fnd_file.put_line(FND_FILE.LOG,' ');
2666 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2667 fnd_file.put_line(FND_FILE.LOG,'Step 0: Template Rules QA Checks ');
2668 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2669 fnd_file.put_line(FND_FILE.LOG,' ');
2670
2671 OKC_XPRT_UTIL_PVT.check_rules_validity
2672 (
2673 p_qa_mode => 'APPROVAL',
2674 p_template_id => p_template_id,
2675 x_sequence_id => l_sequence_id,
2676 x_qa_status => l_qa_status,
2677 x_return_status => x_return_status,
2678 x_msg_data => x_msg_data,
2679 x_msg_count => x_msg_count
2680 ) ;
2681
2682 fnd_file.put_line(FND_FILE.LOG,' ');
2683 fnd_file.put_line(FND_FILE.LOG,'After OKC_XPRT_UTIL_PVT.check_rules_validity');
2684 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2685 fnd_file.put_line(FND_FILE.LOG,'x_qa_status: '||l_qa_status);
2686 fnd_file.put_line(FND_FILE.LOG,'x_sequence_id: '||l_sequence_id);
2687 fnd_file.put_line(FND_FILE.LOG,' ');
2688
2689 --- If any errors happen abort API
2690 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2692 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2693 RAISE FND_API.G_EXC_ERROR;
2694 END IF;
2695
2696 -- Check If any rules had QA errors and abort
2697 IF l_qa_status <> 'S' THEN
2698 -- exit as no rules had QA errors
2699 fnd_file.put_line(FND_FILE.LOG,' ');
2700 fnd_file.put_line(FND_FILE.LOG,'Template Rules QA Check failed');
2701 fnd_file.put_line(FND_FILE.LOG,' ');
2702
2703 retcode := 2;
2704 errbuf := '';
2705 RETURN;
2706
2707 END IF; -- QA Errors
2708
2709
2710
2711 /*
2712 Step 1: Import Template Model
2713 */
2714
2715 fnd_file.put_line(FND_FILE.LOG,' ');
2716 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2717 fnd_file.put_line(FND_FILE.LOG,'Step 1: Importing Template Model');
2718 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2719 fnd_file.put_line(FND_FILE.LOG,' ');
2720
2721 OKC_XPRT_IMPORT_TEMPLATE_PVT.import_template
2722 (
2723 p_api_version => 1,
2724 p_init_msg_list => 'T',
2725 p_commit => 'T',
2726 p_template_id => p_template_id,
2727 x_return_status => x_return_status,
2728 x_msg_data => x_msg_data,
2729 x_msg_count => x_msg_count
2730 ) ;
2731
2732 fnd_file.put_line(FND_FILE.LOG,' ');
2733 fnd_file.put_line(FND_FILE.LOG,'After Importing Template Model');
2734 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2735 fnd_file.put_line(FND_FILE.LOG,' ');
2736
2737 --- If any errors happen abort API
2738 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2740 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2741 RAISE FND_API.G_EXC_ERROR;
2742 END IF;
2743
2744
2745 /*
2746 Step 2: Populate cz_imp_rules
2747 */
2748
2749 fnd_file.put_line(FND_FILE.LOG,' ');
2750 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2751 fnd_file.put_line(FND_FILE.LOG,'Step 2: Populating cz_imp_rules ');
2752 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2753 fnd_file.put_line(FND_FILE.LOG,' ');
2754
2755 OKC_XPRT_IMPORT_RULES_PVT.import_rule_temp_approval
2756 (
2757 p_template_id => p_template_id,
2758 x_run_id => G_RUN_ID,
2759 x_return_status => x_return_status,
2760 x_msg_data => x_msg_data,
2761 x_msg_count => x_msg_count
2762 );
2763
2764 fnd_file.put_line(FND_FILE.LOG,' ');
2765 fnd_file.put_line(FND_FILE.LOG,'After Populating cz_imp_rules');
2766 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2767 fnd_file.put_line(FND_FILE.LOG,'x_msg_data: '||x_msg_data);
2768 fnd_file.put_line(FND_FILE.LOG,'Rule Import Run Id : '||G_RUN_ID);
2769 fnd_file.put_line(FND_FILE.LOG,' ');
2770
2771 --- If any errors happen abort API
2772 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2774 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2775 RAISE FND_API.G_EXC_ERROR;
2776 END IF;
2777
2778
2779 /*
2780 Step 2.1: Count Rules to be imported
2781 Check if there are any records in cz_imp_rules
2782 If there are no records in cz_imp_rules then Template has no rules attached
2783 Skip the rule import process
2784 */
2785
2786 fnd_file.put_line(FND_FILE.LOG,' ');
2787 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2788 fnd_file.put_line(FND_FILE.LOG,'Step 2.1:Count Rules to be imported');
2789 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2790 fnd_file.put_line(FND_FILE.LOG,' ');
2791
2792 OPEN csr_cz_imp_rules_cnt;
2793 FETCH csr_cz_imp_rules_cnt INTO l_rules_cnt;
2794 CLOSE csr_cz_imp_rules_cnt;
2795
2796 fnd_file.put_line(FND_FILE.LOG,' ');
2797 fnd_file.put_line(FND_FILE.LOG,'Total Rules to be Imported to CZ: '||l_rules_cnt);
2798 fnd_file.put_line(FND_FILE.LOG,' ');
2799
2800
2801 IF l_rules_cnt = 0 THEN
2802
2803 fnd_file.put_line(FND_FILE.LOG,' ');
2804 fnd_file.put_line(FND_FILE.LOG,'Template has no Rules attached');
2805 fnd_file.put_line(FND_FILE.LOG,' ');
2806
2807 retcode := 0;
2808 errbuf := '';
2809 RETURN;
2810
2811 END IF; -- l_rules_cnt = 0
2812
2813 /*
2814 Step 2.2: Insert Extension Rules in Template Approval Flow
2815 */
2816
2817 fnd_file.put_line(FND_FILE.LOG,' ');
2818 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2819 fnd_file.put_line(FND_FILE.LOG,'Step 2.2: Calling API to insert extension rule records');
2820 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2821 fnd_file.put_line(FND_FILE.LOG,' ');
2822
2823 OKC_XPRT_IMPORT_RULES_PVT.attach_extension_rule_tmpl
2824 (
2825 p_api_version => 1,
2826 p_init_msg_list => 'T',
2827 p_run_id => G_RUN_ID,
2828 p_template_id => p_template_id,
2829 x_return_status => x_return_status,
2830 x_msg_data => x_msg_data,
2831 x_msg_count => x_msg_count
2832 );
2833
2834 fnd_file.put_line(FND_FILE.LOG,' ');
2835 fnd_file.put_line(FND_FILE.LOG,'Step 2.2: After Calling API to insert extension rule records');
2836 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2837 fnd_file.put_line(FND_FILE.LOG,' ');
2838
2839
2840
2841 /*
2842 Step 3: Call the CZ Rule Import Concurrent Program
2843 CZ Pgm: Import Configuration Rules (CZRULEIMPORTCP)
2844 */
2845
2846 fnd_file.put_line(FND_FILE.LOG,' ');
2847 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2848 fnd_file.put_line(FND_FILE.LOG,'Step 3: Calling the CZ Rule Import Concurrent Program');
2849 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2850 fnd_file.put_line(FND_FILE.LOG,' ');
2851 fnd_file.put_line(FND_FILE.LOG,'Parameter Run Id : '||G_RUN_ID);
2852
2853 FND_MESSAGE.set_name('OKC','OKC_XPRT_RULE_CCPRG_TMPL_MSG');
2854 l_conc_pgm_desc := FND_MESSAGE.get;
2855
2856 G_REQUEST_ID := fnd_request.submit_request
2857 (application => 'CZ',
2858 program => 'CZRULEIMPORTCP',
2859 description => l_conc_pgm_desc,
2860 start_time => NULL,
2861 sub_request => TRUE,
2862 argument1 => G_RUN_ID
2863 );
2864
2865 fnd_file.put_line(FND_FILE.LOG,'Request Id of CZ Rule Concurrent Pgm : '||G_REQUEST_ID);
2866
2867 -- commit the data
2868 COMMIT WORK;
2869
2870
2871 IF NVL(G_REQUEST_ID,0) = 0 THEN
2872 -- Could Not submit Conc Pgm
2873 fnd_message.set_name('OKC','OKC_XPRT_SUB_CZ_RUL_PGM');
2874 fnd_file.put_line(FND_FILE.LOG,'Could NOT submit CZ Concurrent Program');
2875 RAISE FND_API.G_EXC_ERROR;
2876 ELSE
2877 -- child submission successful, pause the parent program
2878 fnd_conc_global.set_req_globals
2879 ( conc_status => 'PAUSED',
2880 request_data => to_char(G_REQUEST_ID)||':'||to_char(G_RUN_ID)
2881 );
2882 END IF; -- child submitted successfully
2883
2884 ELSE
2885 -- req_data IS NOT NULL
2886 -- Restarting the Parent Concurrent Program after completing the child
2887 -- Execute the remaing steps
2888 /*
2889 Note : when the Parent pgm resumes, the session id for the pgm is different
2890 then the original session id. Any variables set before cannot be read
2891 Any variables required in this block are put in request_data and read from
2892 the same
2893
2894 */
2895
2896 OPEN csr_get_child_req_dtls;
2897 FETCH csr_get_child_req_dtls INTO G_REQUEST_ID, G_RUN_ID;
2898 CLOSE csr_get_child_req_dtls;
2899
2900 x_cz_cp_status := fnd_concurrent.get_request_status
2901 (G_REQUEST_ID,
2902 NULL,
2903 NULL,
2904 x_phase,
2905 x_status,
2906 x_dev_phase,
2907 x_dev_status,
2908 x_message
2909 );
2910
2911 fnd_file.put_line(FND_FILE.LOG,' ');
2912 fnd_file.put_line(FND_FILE.LOG,'After Completing CZ Rule Conc Pgm with request id :'||G_REQUEST_ID);
2913 fnd_file.put_line(FND_FILE.LOG,'Run Id : '||G_RUN_ID);
2914 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2915 fnd_file.put_line(FND_FILE.LOG,'Results ');
2916 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2917 fnd_file.put_line(FND_FILE.LOG,'Phase : '||x_phase);
2918 fnd_file.put_line(FND_FILE.LOG,'Status : '||x_status);
2919 fnd_file.put_line(FND_FILE.LOG,'dev_phase : '||x_dev_phase);
2920 fnd_file.put_line(FND_FILE.LOG,'dev_status : '||x_dev_status);
2921 fnd_file.put_line(FND_FILE.LOG,'Message : '||substr(x_message,1,100));
2922 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2923 fnd_file.put_line(FND_FILE.LOG,' ');
2924
2925 IF x_dev_phase <> 'COMPLETED' AND
2926 x_dev_status NOT IN ('NORMAL','WARNING') THEN
2927 -- error in CZ Concurrent Program
2928 fnd_file.put_line(FND_FILE.LOG, 'Error in CZ Rule Import Concurrent Program ');
2929 fnd_file.put_line(FND_FILE.LOG, 'Request Id :'||G_REQUEST_ID);
2930 fnd_file.put_line(FND_FILE.LOG, 'Run Id :'||G_RUN_ID);
2931 RAISE FND_API.G_EXC_ERROR;
2932 END IF;
2933
2934
2935 /*
2936 Step 4: Check status of Rules Imported
2937 */
2938
2939 fnd_file.put_line(FND_FILE.LOG,' ');
2940 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2941 fnd_file.put_line(FND_FILE.LOG,'Step 4: Checking Status of Rules imported');
2942 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2943 fnd_file.put_line(FND_FILE.LOG,' ');
2944
2945 OKC_XPRT_UTIL_PVT.check_import_status
2946 (
2947 p_run_id => G_RUN_ID,
2948 p_import_status => 'S',
2949 p_model_type => 'R', -- Rules Import
2950 x_return_status => x_return_status,
2951 x_msg_data => x_msg_data,
2952 x_msg_count => x_msg_count
2953 );
2954
2955 fnd_file.put_line(FND_FILE.LOG,' ');
2956 fnd_file.put_line(FND_FILE.LOG,'After checking import status');
2957 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2958 fnd_file.put_line(FND_FILE.LOG,' ');
2959
2960 --- If any errors happen abort API
2961 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2963 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2964 RAISE FND_API.G_EXC_ERROR;
2965 END IF;
2966
2967
2968 /*
2969 Step 5: Call the Test Publication API
2970 */
2971
2972 fnd_file.put_line(FND_FILE.LOG,' ');
2973 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2974 fnd_file.put_line(FND_FILE.LOG,'Step 5: Calling the Test Publication API');
2975 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
2976 fnd_file.put_line(FND_FILE.LOG,' ');
2977
2978 OKC_XPRT_UTIL_PVT.create_test_publication
2979 (
2980 x_return_status => x_return_status,
2981 x_msg_data => x_msg_data,
2982 x_msg_count => x_msg_count
2983 );
2984
2985 fnd_file.put_line(FND_FILE.LOG,' ');
2986 fnd_file.put_line(FND_FILE.LOG,'After Calling the Test Publication API ');
2987 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
2988 fnd_file.put_line(FND_FILE.LOG,' ');
2989
2990 --- If any errors happen abort API
2991 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2993 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2994 RAISE FND_API.G_EXC_ERROR;
2995 END IF;
2996
2997
2998 -- Standard call to get message count and if count is 1, get message info.
2999 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3000
3001 -- end debug log
3002 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3003 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3004 G_MODULE||l_api_name,
3005 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3006 END IF;
3007
3008 END IF; -- req_data IS NULL
3009
3010 ELSE
3011
3012
3013 import_template
3014 (
3015 p_api_version => 1,
3016 p_init_msg_list => 'T',
3017 p_commit => 'T',
3018 p_template_id => p_template_id,
3019 p_mode => 'P',
3020 x_return_status => x_return_status,
3021 x_msg_data => x_msg_data,
3022 x_msg_count => x_msg_count
3023 ) ;
3024
3025 --- If any errors happen abort API
3026 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3028 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3029 RAISE FND_API.G_EXC_ERROR;
3030 END IF;
3031
3032
3033
3034 END IF;
3035
3036 EXCEPTION
3037 WHEN FND_API.G_EXC_ERROR THEN
3038 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3039 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3040 G_MODULE||l_api_name,
3041 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3042 END IF;
3043
3044 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3045 retcode := 2;
3046 errbuf := x_msg_data;
3047 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_ERROR);
3048 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
3049 fnd_file.put_line(FND_FILE.LOG,' ');
3050
3051 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3052 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3053 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3054 G_MODULE||l_api_name,
3055 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3056 END IF;
3057
3058 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3059 retcode := 2;
3060 errbuf := x_msg_data;
3061 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
3062 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
3063 fnd_file.put_line(FND_FILE.LOG,' ');
3064
3065 WHEN OTHERS THEN
3066 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3067 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3068 G_MODULE||l_api_name,
3069 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3070 END IF;
3071
3072 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3073 retcode := 2;
3074 errbuf := SQLERRM;
3075 fnd_file.put_line(FND_FILE.LOG,'x_return_status: '||G_RET_STS_UNEXP_ERROR);
3076 fnd_file.put_line(FND_FILE.LOG,'errbuf : '||errbuf);
3077 fnd_file.put_line(FND_FILE.LOG,' ');
3078
3079 END tmpl_approval_publish_rules;
3080
3081 END OKC_XPRT_IMPORT_PVT ;