[Home] [Help]
PACKAGE BODY: APPS.OKC_OUTCOME_INIT_PVT
Source
1 Package body OKC_OUTCOME_INIT_PVT AS
2 /* $Header: OKCROCEB.pls 120.2 2010/07/12 12:14:38 skuchima ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7 -- Package Variables
8 --
9
10
11
12 -- Start of comments
13 -- Procedure Name : Launch outcome
14 -- Description : Executes a plsql procedure or launches a workflow
15 -- Version : 1.0
16 -- End of comments
17 PROCEDURE Launch_outcome(p_api_version IN NUMBER,
18 p_init_msg_list IN VARCHAR2 ,
19 p_corrid_rec IN corrid_rec_typ,
20 p_msg_tab_typ IN msg_tab_typ,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 x_return_status OUT NOCOPY VARCHAR2) IS
24
25 l_outcome_tbl p_outcometbl_type;
26 ctr NUMBER := 0;
27 ctr_wf NUMBER := 0;
31 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
28 l_outcome_name VARCHAR2(200);
29 l_api_name CONSTANT VARCHAR2(30) := 'launch_outcome';
30 l_api_version NUMBER := 1.0;
32 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
33 v_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(240);
36 l_proc VARCHAR2(4000);
37 --NPALEPU
38 --14-DEC-2005
39 --bug # 4699009
40 l_proc_name VARCHAR2(4000);
41 l_wf_proc_name VARCHAR2(4000);
42 l_plsql_proc_name VARCHAR2(4000);
43 --END NPALEPU
44 l_plsql_proc VARCHAR2(4000);
45 l_wf_proc VARCHAR2(4000);
46 prof_s_recipient VARCHAR2(100);
47 prof_e_recipient VARCHAR2(100);
48 l_s_recipient VARCHAR2(100);
49 l_e_recipient VARCHAR2(100);
50 l_oce_id NUMBER;
51 l_contract_id NUMBER;
52
53 CURSOR profile_cur1 IS
54 SELECT opval.profile_option_value profile_value
55 FROM fnd_profile_options op,
56 fnd_profile_option_values opval
57 WHERE op.profile_option_id = opval.profile_option_id
58 AND op.application_id = opval.application_id
59 AND op.profile_option_name = 'OKC_S_RECIPIENT';
60 profile_rec1 profile_cur1%ROWTYPE;
61
62 CURSOR profile_cur2 IS
63 SELECT opval.profile_option_value profile_value
64 FROM fnd_profile_options op,
65 fnd_profile_option_values opval
66 WHERE op.profile_option_id = opval.profile_option_id
67 AND op.application_id = opval.application_id
68 AND op.profile_option_name = 'OKC_E_RECIPIENT';
69 profile_rec2 profile_cur2%ROWTYPE;
70
71 CURSOR success_user_cur(x IN NUMBER)
72 IS
73 SELECT f.user_name success_user
74 FROM jtf_rs_resource_extns r,
75 okc_outcomes_v o,
76 fnd_user f
77 WHERE f.user_id = r.user_id
78 AND o.success_resource_id = r.resource_id
79 AND r.category = 'EMPLOYEE'
80 AND o.id = x;
81 success_user_rec success_user_cur%ROWTYPE;
82
83 CURSOR failure_user_cur(x IN NUMBER)
84 IS
85 SELECT f.user_name failure_user
86 FROM jtf_rs_resource_extns r,
87 okc_outcomes_v o,
88 fnd_user f
89 WHERE f.user_id = r.user_id
90 AND o.failure_resource_id = r.resource_id
91 AND r.category = 'EMPLOYEE'
92 AND o.id = x;
93 failure_user_rec failure_user_cur%ROWTYPE;
94
95 --
96 l_proc_n varchar2(72) := ' OKC_OUTCOME_INIT_PVT.'||'Launch_outcome';
97 --
98
99 BEGIN
100
101 IF (l_debug = 'Y') THEN
102 okc_debug.Set_Indentation(l_proc_n);
103 okc_debug.Log('10: Entering ',2);
104 END IF;
105
106 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
107 G_PKG_NAME,
108 l_init_msg_list,
109 l_api_version,
110 p_api_version,
111 G_LEVEL,
112 x_return_status);
113 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
114 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
115 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
116 RAISE OKC_API.G_EXCEPTION_ERROR;
117 END IF;
118
119 -- populate profile option values for success and error message recipients
120 OPEN profile_cur1;
121 FETCH profile_cur1 INTO profile_rec1;
122 prof_s_recipient := profile_rec1.profile_value;
123 CLOSE profile_cur1;
124
125 OPEN profile_cur2;
126 FETCH profile_cur2 INTO profile_rec2;
127 prof_e_recipient := profile_rec2.profile_value;
128 CLOSE profile_cur2;
129
130 --Populate the Table with name, data_type and value
131 IF p_msg_tab_typ.COUNT > 0 THEN
132 ctr := p_msg_tab_typ.FIRST;
133 -- Contract id is the first record, save it in a variable
134 l_contract_id := p_msg_tab_typ(ctr).element_value;
135 ctr := p_msg_tab_typ.FIRST + 1;
136 -- Outcome id is the second record, save it in a variable
137 l_oce_id := p_msg_tab_typ(ctr).element_value;
138 ctr := p_msg_tab_typ.FIRST + 2;
139 --The outcome name is Package.Procedure name
140 l_outcome_name := p_msg_tab_typ(ctr).element_value;
141 ctr := p_msg_tab_typ.NEXT(ctr);
142 LOOP
143 ctr_wf := ctr_wf + 1;
144 l_outcome_tbl(ctr_wf).name := p_msg_tab_typ(ctr).element_value;
145 EXIT when (ctr = p_msg_tab_typ.LAST);
146 ctr := p_msg_tab_typ.NEXT(ctr);
147 l_outcome_tbl(ctr_wf).data_type := p_msg_tab_typ(ctr).element_value;
148 EXIT when (ctr = p_msg_tab_typ.LAST);
149 ctr := p_msg_tab_typ.NEXT(ctr);
150 l_outcome_tbl(ctr_wf).value := p_msg_tab_typ(ctr).element_value;
151 EXIT when (ctr = p_msg_tab_typ.LAST);
152 ctr := p_msg_tab_typ.NEXT(ctr);
153 END LOOP;
154 END IF;
155
156 -- populate resource names for success and error recipients
157 OPEN success_user_cur(l_oce_id);
158 FETCH success_user_cur INTO success_user_rec;
159 l_s_recipient := success_user_rec.success_user;
160 CLOSE success_user_cur;
161
162 OPEN failure_user_cur(l_oce_id);
163 FETCH failure_user_cur INTO failure_user_rec;
164 l_e_recipient := failure_user_rec.failure_user;
165 CLOSE failure_user_cur;
166
167 -- assign values to recipients
171 --Check the correlation
168 l_s_recipient := NVL(l_s_recipient,prof_s_recipient);
169 l_e_recipient := NVL(l_e_recipient,prof_e_recipient);
170
172 IF p_corrid_rec.corrid NOT IN ('PPS', 'WPS') THEN
173 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
174 p_msg_name => G_INVALID_VALUE,
175 p_token1 => g_col_name_token,
176 p_token1_value => 'p_corrid_rec');
177 raise OKC_API.G_EXCEPTION_ERROR;
178 ELSIF p_corrid_rec.corrid = 'PPS' THEN
179 --Create a plsql procedure
180 Launch_plsql(p_api_version => p_api_version,
181 p_init_msg_list => p_init_msg_list,
182 p_outcome_name => l_outcome_name,
183 p_outcome_tbl => l_outcome_tbl,
184 x_proc => l_plsql_proc,
185 --NPALEPU
186 --14-DEC-2005
187 --BUG # 4699009
188 x_proc_name => l_plsql_proc_name,
189 --END NPALEPU
190 x_msg_count => l_msg_count,
191 x_msg_data => l_msg_data,
192 x_return_status => l_return_status);
193 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
194 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
195 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
196 raise OKC_API.G_EXCEPTION_ERROR;
197 END IF;
198
199 ELSIF p_corrid_rec.corrid = 'WPS' THEN
200 --a workflow
201 Launch_workflow(p_api_version => p_api_version,
202 p_init_msg_list => p_init_msg_list,
203 p_outcome_name => l_outcome_name,
204 p_outcome_tbl => l_outcome_tbl,
205 x_proc => l_wf_proc,
206 --NPALEPU
207 --14-DEC-2005
208 --BUG # 4699009
209 x_proc_name => l_wf_proc_name,
210 --END NPALEPU
211 x_msg_count => l_msg_count,
212 x_msg_data => l_msg_data,
213 x_return_status => l_return_status);
214 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
215 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
216 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
217 raise OKC_API.G_EXCEPTION_ERROR;
218 END IF;
219 END IF;
220
221 IF l_plsql_proc IS NOT NULL THEN
222 l_proc := l_plsql_proc;
223 --NPALEPU
224 --14-DEC-2005
225 --BUG # 4699009
226 l_proc_name := l_plsql_proc_name ;
227 --END NPALEPU
228 ELSIF l_wf_proc IS NOT NULL THEN
229 l_proc := l_wf_proc;
230 --NPALEPU
231 --14-DEC-2005
232 --BUG # 4699009
233 l_proc_name := l_wf_proc_name;
234 --END NPALEPU
235 END IF;
236
237 -- Build executable to pass to generic workflow and attach message to show
238 -- Notification subject.
239 l_proc := 'Begin declare V_MSG_COUNT NUMBER; V_MSG_DATA VARCHAR2(2000); '
240 ||l_proc||' end;';
241
242
243
244
245 /*skuchima: Bug :9724454 stop call to generic workflow based on the profile "OKS: Stop Workflow Launch From Date Assembler " .
246 It will be called from the concurrent program "Service Contracts Date Assembler Workflow Manager" if the profile is set to YES
247 Capture all the required data into interface table*/
248
249 IF( Nvl(FND_PROFILE.VALUE('OKS_STOP_WF_FROM_DA'),'NO')='NO') THEN
250
251 IF (l_debug = 'Y') THEN
252 okc_debug.Log('Calling OKC_ASYNC_PUB.loop_call to create the workflow processes',2);
253 END IF;
254
255 -- Call generic workflow process to launch all types of outcomes and pass the executable string
256 -- bug#4014546 changed p_loops to 2 from 100
257 OKC_ASYNC_PUB.loop_call( p_api_version => l_api_version
258 ,x_return_status => l_return_status
259 ,x_msg_count => l_msg_count
260 ,x_msg_data => l_msg_data
261 ,p_proc => l_proc
262 --NPALEPU
263 --14-DEC-2005
264 --BUG # 4699009
265 ,p_proc_name => l_proc_name
266 --END NPALEPU
267 ,p_s_recipient => l_s_recipient
268 ,p_e_recipient => l_e_recipient
269 ,p_contract_id => l_contract_id
270 ,p_loops => 2
271 ,p_subj_first_msg => 'F'
272 );
273 ELSE
274
275 IF (l_debug = 'Y') THEN
276 okc_debug.Log('Inserting data into interface table OKS_DA_INTERFACE_B',2);
277 END IF;
278
279 INSERT INTO OKS_DA_INTERFACE_B
280 (id,contract_id,enque_date,status,proc,proc_name,s_recipient,e_recipient,extra_attr_text)values
281 (OKS_DA_INTERFACE_S.NEXTVAL,l_contract_id,SYSDATE,'PENDING',l_proc,l_proc_name,l_s_recipient,l_e_recipient,okc_wf.get_wf_string);
282
283 END IF;
284
285 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
286
287 IF (l_debug = 'Y') THEN
288 okc_debug.Log('1000: Leaving ',2);
289 okc_debug.Reset_Indentation;
290 END IF;
291
292 EXCEPTION
293 WHEN OKC_API.G_EXCEPTION_ERROR THEN
294 x_return_status := OKC_API.HANDLE_EXCEPTIONS
295 (l_api_name,
296 G_PKG_NAME,
297 'OKC_API.G_RET_STS_ERROR',
298 l_msg_count,
299 l_msg_data,
300 G_LEVEL);
301 IF (l_debug = 'Y') THEN
302 okc_debug.Log('2000: Leaving ',2);
303 okc_debug.Reset_Indentation;
304 END IF;
305 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
306
307 x_return_status := OKC_API.HANDLE_EXCEPTIONS
308 (l_api_name,
309 G_PKG_NAME,
310 'OKC_API.G_RET_STS_UNEXP_ERROR',
311 l_msg_count,
312 l_msg_data,
313 G_LEVEL);
314 IF (l_debug = 'Y') THEN
315 okc_debug.Log('3000: Leaving ',2);
316 okc_debug.Reset_Indentation;
317 END IF;
318 WHEN OTHERS THEN
319 x_return_status := OKC_API.HANDLE_EXCEPTIONS
320 (l_api_name,
321 G_PKG_NAME,
322 'OTHERS',
323 l_msg_count,
324 l_msg_data,
325 G_LEVEL);
326 IF (l_debug = 'Y') THEN
327 okc_debug.Log('4000: Leaving ',2);
328 okc_debug.Reset_Indentation;
329 END IF;
330 END launch_outcome;
331
332 -- Start of comments
333 -- Procedure Name : launch_plsql
334 -- Description : Executes a plsql procedure
335 -- Version : 1.0
336 -- End of comments
337 PROCEDURE Launch_plsql(p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2 ,
339 p_outcome_name IN VARCHAR2,
340 p_outcome_tbl IN p_outcometbl_type,
341 x_proc OUT NOCOPY VARCHAR2,
342 --NPALEPU
343 --14-DEC-2005
344 --BUG # 4699009
345 x_proc_name OUT NOCOPY VARCHAR2,
346 --END NPALEPU
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2,
349 x_return_status OUT NOCOPY VARCHAR2) IS
350
351 l_api_name CONSTANT VARCHAR2(30) := 'launch_plsql';
352 l_api_version CONSTANT NUMBER := 1.0;
353 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
354 v_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
355 v_msg_count NUMBER;
356 v_msg_data VARCHAR2(2000);
357 plsql_block VARCHAR2(4000);
358 l_error_exception EXCEPTION;
359 var2 VARCHAR2(100);
360 i NUMBER;
361 l_pack_proc VARCHAR2(4000);
362 l_std_params VARCHAR2(500);
363 l_data_type VARCHAR2(40);
364 l_outcome_tbl p_outcometbl_type;
365 ctr NUMBER := 0;
366 --
367 l_proc_n varchar2(72) := ' OKC_OUTCOME_INIT_PVT.'||'Launch_plsql';
368 --
369 --NPALEPU
370 --14-DEC-2005
371 --For Bug # 4699009
372 CURSOR l_proc_name_csr(l_procedure_name VARCHAR2,l_package_name VARCHAR2) is
373 SELECT NAME
374 FROM OKC_PROCESS_DEFS_V
375 WHERE PROCEDURE_NAME = l_procedure_name
376 AND PACKAGE_NAME = l_package_name;
377
378 l_proc_name VARCHAR2(4000);
379 l_package_name VARCHAR2(4000);
380 l_procedure_name VARCHAR2(4000);
381
382 --END NPALEPU
383
384 BEGIN
385
386 IF (l_debug = 'Y') THEN
387 okc_debug.Set_Indentation(l_proc_n);
388 okc_debug.Log('10: Entering ',2);
389 END IF;
390
391 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
392 G_PKG_NAME,
393 p_init_msg_list,
394 l_api_version,
395 p_api_version,
396 G_LEVEL,
397 x_return_status);
398 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
399 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
400 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
401 RAISE OKC_API.G_EXCEPTION_ERROR;
402 END IF;
403
404 --NPALEPU
405 --14-DEC-2005
406 --Bug # 4699009
407 --Package name
408 l_package_name := substr(p_outcome_name, 1, instr(p_outcome_name, '.', 1) - 1);
409 --Procedure name
410 l_procedure_name := substr(p_outcome_name, instr(p_outcome_name , '.') + 1);
411
412 IF l_package_name IS NOT NULL OR l_procedure_name IS NOT NULL THEN
413 BEGIN
414 OPEN l_proc_name_csr(l_procedure_name,l_package_name);
415 FETCH l_proc_name_csr into l_proc_name;
416 CLOSE l_proc_name_csr;
417 x_proc_name := l_proc_name;
418 EXCEPTION
419 WHEN OTHERS THEN
420 x_proc_name := NULL;
421 END;
422 END IF;
423 --END NPALEPU
424 /*
425 -- below commented out by marat (bug#2477385)
426 --Build the plsql string
427 IF p_outcome_tbl.COUNT > 0 THEN
428 i := p_outcome_tbl.FIRST;
429 l_data_type := p_outcome_tbl(i).data_type;
430
431 IF (l_data_type IN ('DATE', 'CHAR') AND p_outcome_tbl(i).name
432 NOT IN ('X_RETURN_STATUS', 'X_MSG_DATA', 'P_INIT_MSG_LIST')) THEN
433 IF UPPER(p_outcome_tbl(i).value) NOT IN ('OKC_API.G_MISS_CHAR','OKC_API.G_MISS_DATE','NULL') THEN
434 l_pack_proc := p_outcome_name || '('||p_outcome_tbl(i).name||
435 ' => '||''''||p_outcome_tbl(i).value||'''';
436 ELSIF UPPER(p_outcome_tbl(i).value) IN ('OKC_API.G_MISS_CHAR','OKC_API.G_MISS_DATE','NULL') THEN
440
437 l_pack_proc := p_outcome_name || '('||p_outcome_tbl(i).name||
438 ' => '||p_outcome_tbl(i).value;
439 END IF;
441 ELSIF (l_data_type IN ('DATE', 'CHAR') AND p_outcome_tbl(i).name
442 IN ('X_RETURN_STATUS', 'X_MSG_DATA', 'P_INIT_MSG_LIST')) THEN
443 null;
444 ELSIF (l_data_type = 'NUMBER') AND (p_outcome_tbl(i).name <> 'X_MSG_COUNT') THEN
445 l_pack_proc := p_outcome_name ||
446 '('||p_outcome_tbl(i).name||' => '||p_outcome_tbl(i).value;
447
448 ELSIF (l_data_type = 'NUMBER') AND (p_outcome_tbl(i).name = 'X_MSG_COUNT') THEN
449 null;
450 ELSE
451 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
452 p_msg_name => G_INVALID_VALUE,
453 p_token1 => g_col_name_token,
454 p_token1_value => 'datatype');
455 raise OKC_API.G_EXCEPTION_ERROR;
456 END IF;
457
458 IF p_outcome_tbl.COUNT > 1 THEN
459
460 i := p_outcome_tbl.FIRST + 1;
461
462 LOOP
463 l_data_type := p_outcome_tbl(i).data_type;
464 IF (l_data_type IN ('DATE', 'CHAR') AND p_outcome_tbl(i).name
465 NOT IN ('X_RETURN_STATUS', 'X_MSG_DATA', 'P_INIT_MSG_LIST')) THEN
466 IF UPPER(p_outcome_tbl(i).value) NOT IN ('OKC_API.G_MISS_CHAR','OKC_API.G_MISS_DATE','NULL') THEN
467 l_pack_proc := l_pack_proc||', '||p_outcome_tbl(i).name||
468 ' => '||''''||p_outcome_tbl(i).value||'''';
469 ELSIF UPPER(p_outcome_tbl(i).value) IN ('OKC_API.G_MISS_CHAR','OKC_API.G_MISS_DATE','NULL') THEN
470 l_pack_proc := l_pack_proc||', '||p_outcome_tbl(i).name||
471 ' => '||p_outcome_tbl(i).value;
472 END IF;
473
474 ELSIF (l_data_type IN ('DATE', 'CHAR') AND p_outcome_tbl(i).name
475 IN ('X_RETURN_STATUS', 'X_MSG_DATA', 'P_INIT_MSG_LIST')) THEN
476 null;
477
478 ELSIF (l_data_type = 'NUMBER') AND (p_outcome_tbl(i).name <>'X_MSG_COUNT')THEN
479 l_pack_proc := l_pack_proc ||', '||
480 p_outcome_tbl(i).name||' => '||p_outcome_tbl(i).value;
481
482 ELSIF (l_data_type = 'NUMBER') AND (p_outcome_tbl(i).name = 'X_MSG_COUNT') THEN
483 null;
484 ELSE
485 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
486 p_msg_name => G_INVALID_VALUE,
487 p_token1 => g_col_name_token,
488 p_token1_value => 'datatype');
489 raise OKC_API.G_EXCEPTION_ERROR;
490 END IF;
491 EXIT WHEN (i = p_outcome_tbl.LAST);
492 i := p_outcome_tbl.NEXT(i);
493 END LOOP;
494 END IF;
495 END IF;
496
497 --Append all the standard parameters
498 l_std_params := 'P_INIT_MSG_LIST => OKC_API.G_FALSE, X_RETURN_STATUS => :V_RETURN_STATUS,'||
499 'X_MSG_COUNT => V_MSG_COUNT, X_MSG_DATA => V_MSG_DATA)';
500 --Build the plsql string
501 l_pack_proc := l_pack_proc ||', '||l_std_params;
502
503 --Build the plsql block
504 plsql_block := 'Begin ' ||l_pack_proc||'; End;';
505
506 -- assign the executable string to out parameter
507 x_proc := plsql_block;
508 -- above commented out by marat (bug#2477385)
509 */
510
511 -- added by pnayani bug#2778651 - start
512 -- Removing standard params from the table and pass it to okc_wf package to build outcome
513 for i in p_outcome_tbl.FIRST..p_outcome_tbl.LAST loop
514
515 IF (p_outcome_tbl(i).name NOT IN
516 ('X_RETURN_STATUS', 'X_MSG_DATA','X_MSG_COUNT','P_INIT_MSG_LIST')) THEN
517
518 ctr := ctr + 1;
519 l_outcome_tbl(ctr).name := p_outcome_tbl(i).name;
520 l_outcome_tbl(ctr).data_type := p_outcome_tbl(i).data_type;
521 l_outcome_tbl(ctr).value := p_outcome_tbl(i).value;
522
523 END IF;
524
525 end loop;
526
527 -- added by pnayani bug#2778651 - end
528
529
530 -- added by marat - start (bug#2477385) - replacement for the above
531 l_pack_proc:=okc_wf.build_wf_plsql
532 (okc_wf.prebuild_wf_plsql
533 (okc_wf.build_wf_string( p_outcome_name,
534 l_outcome_tbl
535 )));
536 if l_pack_proc is null then
537 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME,
538 p_msg_name => G_INVALID_VALUE,
539 p_token1 => g_col_name_token,
540 p_token1_value => 'datatype');
541 raise OKC_API.G_EXCEPTION_ERROR;
542 end if;
543 x_proc:=l_pack_proc;
544 -- added by marat - end (bug#2477385)
545
546 OKC_API.END_ACTIVITY(v_msg_count, v_msg_data);
547
548
549 IF (l_debug = 'Y') THEN
550 okc_debug.Log('1000: Leaving ',2);
551 okc_debug.Reset_Indentation;
552 END IF;
553
554 Exception
555 WHEN OKC_API.G_EXCEPTION_ERROR THEN
556 x_return_status := OKC_API.HANDLE_EXCEPTIONS
557 (l_api_name,
558 G_PKG_NAME,
559 'OKC_API.G_RET_STS_ERROR',
560 v_msg_count,
561 v_msg_data,
562 G_LEVEL);
563 IF (l_debug = 'Y') THEN
564 okc_debug.Log('2000: Leaving ',2);
565 okc_debug.Reset_Indentation;
566 END IF;
567 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
568 x_return_status := OKC_API.HANDLE_EXCEPTIONS
569 (l_api_name,
570 G_PKG_NAME,
571 'OKC_API.G_RET_STS_UNEXP_ERROR',
572 v_msg_count,
573 v_msg_data,
574 G_LEVEL);
575 IF (l_debug = 'Y') THEN
576 okc_debug.Log('3000: Leaving ',2);
577 okc_debug.Reset_Indentation;
578 END IF;
582 G_PKG_NAME,
579 WHEN OTHERS THEN
580 x_return_status := OKC_API.HANDLE_EXCEPTIONS
581 (l_api_name,
583 'OTHERS',
584 v_msg_count,
585 v_msg_data,
586 G_LEVEL);
587 IF (l_debug = 'Y') THEN
588 okc_debug.Log('4000: Leaving ',2);
589 okc_debug.Reset_Indentation;
590 END IF;
591 End Launch_plsql;
592
593 -- Start of comments
594 -- Procedure Name : launch_workflow
595 -- Description : Launches a workflow
596 -- Version : 1.0
597 -- End of comments
598 PROCEDURE Launch_workflow(p_api_version IN NUMBER,
599 p_init_msg_list IN VARCHAR2 ,
600 p_outcome_name IN VARCHAR2,
601 p_outcome_tbl IN p_outcometbl_type,
602 x_proc OUT NOCOPY VARCHAR2,
603 --NPALEPU
604 --14-DEC-2005
605 --BUG # 4699009
606 x_proc_name OUT NOCOPY VARCHAR2,
607 --END NPALEPU
608 x_msg_count OUT NOCOPY NUMBER,
609 x_msg_data OUT NOCOPY VARCHAR2,
610 x_return_status OUT NOCOPY VARCHAR2) IS
611
612 l_api_name CONSTANT VARCHAR2(30) := 'Launch_workflow';
613 l_api_version CONSTANT NUMBER := 1.0;
614 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
615 l_item_type VARCHAR2(100);
616 l_item_key VARCHAR2(100);
617 l_process VARCHAR2(100);
618 l_wf_proc VARCHAR2(4000);
619 l_error_exception EXCEPTION;
620 l_dummy VARCHAR2(1);
621 l_end_date DATE;
622 l_result VARCHAR2(1);
623 i NUMBER := 0;
624 --
625 l_proc_n varchar2(72) := ' OKC_OUTCOME_INIT_PVT.'||'Launch_workflow';
626 --
627 --NPALEPU
628 --14-DEC-2005
629 --For Bug # 4699009
630 CURSOR l_proc_name_csr(l_wf_name VARCHAR2,l_wf_process_name VARCHAR2) is
631 SELECT NAME
632 FROM OKC_PROCESS_DEFS_V
633 WHERE WF_NAME=l_wf_name
634 AND WF_PROCESS_NAME = l_wf_process_name;
635
636 l_proc_name VARCHAR2(4000);
637 --END NPALEPU
638
639 Begin
640
641 IF (l_debug = 'Y') THEN
642 okc_debug.Set_Indentation(l_proc_n);
643 okc_debug.Log('10: Entering ',2);
644 END IF;
645
646 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
647 G_PKG_NAME,
648 p_init_msg_list,
649 l_api_version,
650 p_api_version,
651 G_LEVEL,
652 x_return_status);
653 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
654 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
655 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
656 RAISE OKC_API.G_EXCEPTION_ERROR;
657 END IF;
658
659 --Workflow name
660 l_item_type := substr(p_outcome_name, 1, instr(p_outcome_name, '.', 1) - 1);
661 --Workflow Process name
662 l_process := substr(p_outcome_name, instr(p_outcome_name , '.') + 1);
663 IF l_item_type IS NULL OR l_process IS NULL THEN
664 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
665 p_msg_name => G_PROCESS_NOTFOUND,
666 p_token1 => g_wf_name_token,
667 p_token1_value => l_item_type,
668 p_token2 => G_WF_P_NAME_TOKEN,
669 p_token2_value => l_process);
670 RAISE OKC_API.G_EXCEPTION_ERROR;
671 END IF;
672
673 --NPALEPU
674 --14-DEC-2005
675 --Bug # 4699009
676 BEGIN
677 OPEN l_proc_name_csr(l_item_type,l_process);
678 FETCH l_proc_name_csr into l_proc_name;
679 CLOSE l_proc_name_csr;
680 x_proc_name := l_proc_name;
681 EXCEPTION
682 WHEN OTHERS THEN
683 x_proc_name := NULL;
684 END;
685 --END NPALEPU
686
687 --Select the sequence number into l_item_key
688 select okc_wf_outcome_s1.nextval into l_item_key from dual;
689
690 --Launch the Workflow process
691 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
692
693 IF (p_outcome_tbl.COUNT > 0) THEN
694 i := p_outcome_tbl.FIRST;
695 LOOP
696 --Set the item attributes
697 if p_outcome_tbl(i).data_type = 'CHAR' then
698 WF_ENGINE.Setitemattrtext(itemtype => l_item_type,
699 itemkey => l_item_key,
700 aname => p_outcome_tbl(i).name,
701 avalue => p_outcome_tbl(i).value);
702 end if;
703
704 if p_outcome_tbl(i).data_type = 'NUMBER' then
705 WF_ENGINE.Setitemattrnumber(itemtype => l_item_type,
706 itemkey => l_item_key,
707 aname => p_outcome_tbl(i).name,
708 avalue => p_outcome_tbl(i).value);
709 end if;
710
711 if p_outcome_tbl(i).data_type = 'DATE' then
712 WF_ENGINE.Setitemattrdate(itemtype => l_item_type,
713 itemkey => l_item_key,
714 aname => p_outcome_tbl(i).name,
715 avalue => p_outcome_tbl(i).value);
716 end if;
717 EXIT WHEN (i = p_outcome_tbl.LAST);
718 i := p_outcome_tbl.NEXT(i);
719 END LOOP;
720 END IF;
721 commit;
722 l_wf_proc := 'begin WF_ENGINE.STARTPROCESS('||''''||l_item_type||''''||','||''''||l_item_key||''''||'); end;';
723 x_proc := l_wf_proc;
724 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
725
726 IF (l_debug = 'Y') THEN
727 okc_debug.Log('1000: Leaving ',2);
728 okc_debug.Reset_Indentation;
732 WHEN OKC_API.G_EXCEPTION_ERROR THEN
729 END IF;
730
731 Exception
733 x_return_status := OKC_API.HANDLE_EXCEPTIONS
734 (l_api_name,
735 G_PKG_NAME,
736 'OKC_API.G_RET_STS_ERROR',
737 x_msg_count,
738 x_msg_data,
739 G_LEVEL);
740 IF (l_debug = 'Y') THEN
741 okc_debug.Log('2000: Leaving ',2);
742 okc_debug.Reset_Indentation;
743 END IF;
744 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
745 x_return_status := OKC_API.HANDLE_EXCEPTIONS
746 (l_api_name,
747 G_PKG_NAME,
748 'OKC_API.G_RET_STS_UNEXP_ERROR',
749 x_msg_count,
750 x_msg_data,
751 G_LEVEL);
752 IF (l_debug = 'Y') THEN
753 okc_debug.Log('3000: Leaving ',2);
754 okc_debug.Reset_Indentation;
755 END IF;
756 WHEN OTHERS THEN
757 x_return_status := OKC_API.HANDLE_EXCEPTIONS
758 (l_api_name,
759 G_PKG_NAME,
760 'OTHERS',
761 x_msg_count,
762 x_msg_data,
763 G_LEVEL);
764 IF (l_debug = 'Y') THEN
765 okc_debug.Log('4000: Leaving ',2);
766 okc_debug.Reset_Indentation;
767 END IF;
768 End Launch_workflow;
769
770
771
772 /* Start of comments
773 Procedure Name : submit_da_wf_mgr
774 Description : Registered as the concurrent program "Service Contracts Date Assembler Workflow Manager" to fetch records from interface table and create the worklfow processes
775 skuchima : Bug9724454
776 End of comments
777 */
778
779 PROCEDURE submit_da_wf_mgr(errbuf OUT NOCOPY VARCHAR2,
780 retcode OUT NOCOPY VARCHAR2) IS
781
782 l_api_name CONSTANT VARCHAR2(30) := 'submit_da_wf_mgr';
783 l_api_version CONSTANT VARCHAR2(30) := 1.0;
784 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
785 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
786 l_msg_count NUMBER;
787 l_msg_data VARCHAR2(1000);
788 l_init_msg_list VARCHAR2(3) := 'F';
789
790 --
791 l_proc varchar2(72) := G_PKG_NAME||'submit_da_wf_mgr';
792 --
793 --Get all the contracts information captured to launch the workflow
794 CURSOR launch_wf_csr(l_tot_que IN number) IS
795 SELECT * from
796 (SELECT *
797 FROM OKS_DA_INTERFACE_B
798 WHERE status IN ('PENDING','ERROR')
799 ORDER BY enque_date)
800 where ROWNUM<=l_tot_que;
801
802
803
804
805 l_oks_cnt NUMBER:= 0;
806 l_wf_prc_cnt NUMBER :=0;
807 l_queue_size NUMBER;
808 l_REQUEST_ID NUMBER;
809 x_hook NUMBER;
810
811 BEGIN
812
813 IF (l_debug = 'Y') THEN
814 okc_debug.Set_Indentation(l_proc);
815 okc_debug.Log('10: Entering submit_da_wf_mgr ',2);
816 END IF;
817
818 ---Total Queue_Size
819 l_queue_size:= Nvl(FND_PROFILE.VALUE('OKS_DA_QUEUE_SIZE'),0);
820
821 IF (l_debug = 'Y') THEN
822 okc_debug.Log('20: Total Queue Size '||l_queue_size,2);
823 END IF;
824
825 ---fetch the pending records from the interface table
826 FOR launch_wf_rec IN launch_wf_csr(l_queue_size) LOOP
827
828 okc_wf.init_wf_string(launch_wf_rec.EXTRA_ATTR_TEXT);
829
830 ---Call the genric workflow OKCALERT
831 OKC_ASYNC_PUB.loop_call( p_api_version => l_api_version
832 ,x_return_status => l_return_status
833 ,x_msg_count => l_msg_count
834 ,x_msg_data => l_msg_data
835 ,p_proc => launch_wf_rec.proc
836 --NPALEPU
837 --14-DEC-2005
838 --BUG # 4699009
839 ,p_proc_name => launch_wf_rec.proc_name
840 --END NPALEPU
841 ,p_s_recipient => launch_wf_rec.s_recipient
842 ,p_e_recipient => launch_wf_rec.e_recipient
843 ,p_contract_id => launch_wf_rec.contract_id
844 ,p_loops => 2
845 ,p_subj_first_msg => 'F'
846 );
847
848
849
850 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
851 UPDATE OKS_DA_INTERFACE_B
852 SET status='ERROR'
853 WHERE id=launch_wf_rec.id;
854
855 ELSE
856
857 l_oks_cnt:=l_oks_cnt+1;
858
859 DELETE from OKS_DA_INTERFACE_B
860 WHERE id=launch_wf_rec.id;
861
862 END IF;
863
864 END LOOP;
865
866 COMMIT;
867
868 fnd_file.put_line(fnd_file.log,'Total Queue Size '||l_queue_size);
869 fnd_file.put_line(fnd_file.log,'Total Contracts picked from interface table '||l_oks_cnt);
870
871 ---call the custom hook
872 oks_code_hook.custom_da_prc(l_oks_cnt,x_hook);
873
874 IF x_hook = -1 THEN
875 fnd_file.put_line(fnd_file.log,'Error in the custom hook oks_code_hook.custom_da_prc ');
876 retcode :=2;
877 errbuf :=substr('Error in custom hook OKS_CODE_HOOK.CUSTOM_DA_PRC ',1,200);
878 END IF;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882
883 fnd_file.put_line(fnd_file.log,'Error in the procedure OKC_OUTCOME_INIT_PVT.SUBMIT_DA_WF_MGR '||sqlerrm);
884 retcode := 2;
885 errbuf := substr(sqlerrm,1,200);
886
887 END submit_da_wf_mgr;
888
889
890 End OKC_OUTCOME_INIT_PVT;