[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_APPROVAL_PVT
Source
1 package body OKC_CONTRACT_APPROVAL_PVT as
2 /* $Header: OKCRCAPB.pls 120.21 2011/05/04 08:03:36 vgujarat ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 --
6 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_CONTRACT_APPROVAL_PVT';
8 G_LEVEL CONSTANT VARCHAR2(4) := '_PVT';
9 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
10 l_api_version CONSTANT NUMBER := 1;
11 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
12 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
13 --
14 G_K_WAS_APPROVED CONSTANT varchar2(200) := 'OKC_ALREADY_APPROVED';
15 --
16 G_PROCESS_NOT_FOR_APPROVAL CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_FOR_APPROVAL';
17 G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
18 G_WF_P_NAME_TOKEN CONSTANT varchar2(200) := 'WF_PROCESS';
19 --
20 G_PROCESS_NOTFOUND CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_FOUND';
21 --
22 G_K_ON_APPROVAL CONSTANT varchar2(200) := 'OKC_IS_ON_APPROVAL';
23 -- G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
24 G_KEY_TOKEN CONSTANT varchar2(200) := 'WF_KEY';
25 --
26 G_WF_NOT_PURGED CONSTANT varchar2(200) := 'OKC_WF_NOT_PURGED';
27 -- G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
28 -- G_KEY_TOKEN CONSTANT varchar2(200) := 'WF_KEY';
29 --
30 G_K_NOT_ON_APPROVAL CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_ACTIVE';
31 --
32 G_NO_U_PRIVILEGE CONSTANT varchar2(200) := 'OKC_NO_RIGHT_TO_CHANGE';
33
34 PROCEDURE continue_k_process
35 (
36 p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 ,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_contract_id IN NUMBER,
42 p_wf_item_key IN VARCHAR2,
43 p_called_from IN VARCHAR2
44 ) AS
45 PRAGMA AUTONOMOUS_TRANSACTION;
46
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_api_name CONSTANT VARCHAR2(50) := 'continue_k_process';
49
50 CURSOR l_kdetails_csr(p_chr_id NUMBER) IS
51 SELECT wf_item_key
52 FROM oks_k_headers_b
53 WHERE chr_id = p_chr_id;
54
55 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
56 l_wf_attributes OKS_WF_K_PROCESS_PVT.WF_ATTR_DETAILS;
57 l_wf_item_key VARCHAR2(240);
58
59 BEGIN
60
61 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
62 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , 'okc.plsql.'||l_api_name,
63 'Entered '||G_PKG_NAME ||'.'||l_api_name||' p_contract_id='||p_contract_id
64 ||' p_called_from='||p_called_from);
65 END IF;
66
67 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
68 G_PKG_NAME,
69 p_init_msg_list,
70 l_api_version,
71 p_api_version,
72 G_LEVEL,
73 x_return_status);
74 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
75 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
76 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
77 RAISE OKC_API.G_EXCEPTION_ERROR;
78 END IF;
79
80 IF p_wf_item_key IS NULL THEN
81 OPEN l_kdetails_csr(p_contract_id);
82 FETCH l_kdetails_csr INTO l_wf_item_key;
83 CLOSE l_kdetails_csr;
84 ELSE
85 l_wf_item_key := p_wf_item_key;
86 END IF;
87
88 IF l_wf_item_key IS NOT NULL THEN
89 IF NVL(p_called_from,'!') = 'APPROVE' THEN
90 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
91 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
92 ' OKS_WF_K_PROCESS_PVT.complete_activity with APPROVED result');
93 END IF;
94 OKS_WF_K_PROCESS_PVT.complete_activity
95 (
96 p_api_version => l_api_version,
97 p_init_msg_list => OKC_API.G_FALSE,
98 p_contract_id => p_contract_id,
99 p_item_key => l_wf_item_key,
100 p_resultout => 'APPROVED',
101 p_process_status => NULL,
102 p_activity_name => 'REVIEW_AND_APPROVE',
103 x_return_status => x_return_status,
104 x_msg_data => x_msg_data,
105 x_msg_count => x_msg_count
106 );
107 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
108 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
109 ' Return status='||x_return_status||' x_msg_count='||x_msg_count);
110 END IF;
111 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
112 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
113 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
114 RAISE OKC_API.G_EXCEPTION_ERROR;
115 END IF;
116
117 ELSIF NVL(p_called_from,'!') = 'STOP' THEN
118 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
119 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
120 ' OKS_WF_K_PROCESS_PVT.complete_activity with STOPPED result');
121 END IF;
122 OKS_WF_K_PROCESS_PVT.complete_activity
123 (
124 p_api_version => l_api_version,
125 p_init_msg_list => OKC_API.G_FALSE,
126 p_contract_id => p_contract_id,
127 p_item_key => l_wf_item_key,
128 p_resultout => 'STOPPED',
129 p_process_status => 'ACT',
130 p_activity_name => 'REVIEW_AND_APPROVE',
131 x_return_status => x_return_status,
132 x_msg_data => x_msg_data,
133 x_msg_count => x_msg_count
134 );
135 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
136 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
137 ' Return status='||x_return_status||' x_msg_count='||x_msg_count);
138 END IF;
139 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
140 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
141 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
142 RAISE OKC_API.G_EXCEPTION_ERROR;
143 END IF;
144
145 -- update process status to 'Quote Accepted' coz approval workflow
146 -- is stopped so we should effectively revert back to the original
147 -- negotiation status.
148 /* IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
149 UPDATE oks_k_headers_b
150 SET object_version_number = object_version_number + 1,
151 renewal_status = 'ACT',
152 last_update_date = SYSDATE,
153 last_update_login = FND_GLOBAL.LOGIN_ID,
154 Last_updated_by = FND_GLOBAL.USER_ID
155 WHERE chr_id = p_contract_id;
156 END IF;
157 */
158 ELSIF NVL(p_called_from,'!') = 'REJECTED' THEN
159 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
160 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
161 ' OKS_WF_K_PROCESS_PVT.complete_activity with REJECTED result');
162 END IF;
163 OKS_WF_K_PROCESS_PVT.complete_activity
164 (
165 p_api_version => l_api_version,
166 p_init_msg_list => OKC_API.G_FALSE,
167 p_contract_id => p_contract_id,
168 p_item_key => l_wf_item_key,
169 p_resultout => NULL,
170 p_process_status => 'REJECTED',
171 p_activity_name => 'REVIEW_AND_APPROVE',
172 x_return_status => x_return_status,
173 x_msg_data => x_msg_data,
174 x_msg_count => x_msg_count
175 );
176 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
177 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
178 ' Return status='||x_return_status||' x_msg_count='||x_msg_count);
179 END IF;
180 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
182 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
183 RAISE OKC_API.G_EXCEPTION_ERROR;
184 END IF;
185
186 -- update process status to 'Quote Accepted' coz approval workflow
187 -- is stopped so we should effectively revert back to the original
188 -- negotiation status.
189 /* IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
190 UPDATE oks_k_headers_b
191 SET object_version_number = object_version_number + 1,
192 renewal_status = 'REJ',
193 last_update_date = SYSDATE,
194 last_update_login = FND_GLOBAL.LOGIN_ID,
195 Last_updated_by = FND_GLOBAL.USER_ID
196 WHERE chr_id = p_contract_id;
197 END IF;
198 */
199 END IF;
200 -- Following code is executed only after migration of pre-R12
201 -- contracts that are in the approval process (no prior OKS Contract
202 -- Process wf existing) and being either approved or rejected by approver.
203 ELSE
204 IF NVL(p_called_from,'!') = 'APPROVE' THEN
205 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
206 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
207 'Updating Negotiation status to Complete');
208 END IF;
209 -- We just want to update the negotiation status; NO interaction
210 -- or email or notification is sent for these scenarios
211 UPDATE oks_k_headers_b
212 SET renewal_status = 'COMPLETE',
213 object_version_number = object_version_number + 1,
214 last_update_date = SYSDATE,
215 last_updated_by = FND_GLOBAL.USER_ID,
216 last_update_login = FND_GLOBAL.LOGIN_ID
217 WHERE chr_id = p_contract_id;
218 ELSIF NVL(p_called_from,'!') = 'REJECTED' THEN
219 -- Launch process workflow for existing service contracts created prior to r12
220 -- as they would not have process workflow associated as they had been in the
221 -- Approval process. We'll have to place it in salesrep queue in Rejected status
222 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
223 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
224 'Preparing to launch workflow ...');
225 END IF;
226 l_wf_attributes.CONTRACT_ID := p_contract_id;
227 l_wf_attributes.NEGOTIATION_STATUS := 'REJECTED';
228 l_wf_attributes.ITEM_KEY := p_contract_id||to_char(sysdate,'YYYYMMDDHH24MISS');
229 l_wf_attributes.IRR_FLAG := 'Y';
230 l_wf_attributes.PROCESS_TYPE := 'MANUAL';
231 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
232 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
233 ' OKS_WF_K_PROCESS_PVT.launch_k_process_wf p_contract_id '||
234 p_contract_id);
235 END IF;
236 OKS_WF_K_PROCESS_PVT.launch_k_process_wf
237 (
238 p_api_version => 1.0,
239 p_init_msg_list => 'T',
240 p_wf_attributes => l_wf_attributes,
241 x_return_status => x_return_status,
242 x_msg_count => x_msg_count,
243 x_msg_data => x_msg_data
244 ) ;
245 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
246 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
247 'OKS_WF_K_PROCESS_PVT.launch_k_process_wf x_return_status=>'||
248 x_return_status);
249 END IF;
250 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
252 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
253 RAISE OKC_API.G_EXCEPTION_ERROR;
254 END IF;
255 UPDATE oks_k_headers_b
256 SET object_version_number = object_version_number + 1,
257 wf_item_key = l_wf_attributes.ITEM_KEY,
258 renewal_status = l_wf_attributes.NEGOTIATION_STATUS,
259 last_update_date = SYSDATE,
260 last_update_login = FND_GLOBAL.LOGIN_ID,
261 Last_updated_by = FND_GLOBAL.USER_ID
262 WHERE chr_id = l_wf_attributes.contract_id;
263 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
264 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE||l_api_name,
265 ' Updated negotiation status to REJECTED');
266 END IF;
267 END IF;
268 END IF;
269 -- Explicit commit needed
270 COMMIT;
271
272 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
273
274 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
275 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okc.plsql.'||l_api_name,
276 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
277 END IF;
278
279 EXCEPTION
280 WHEN OKC_API.G_EXCEPTION_ERROR THEN
281 x_return_status := OKC_API.HANDLE_EXCEPTIONS
282 (substr(l_api_name,1,26),
283 G_PKG_NAME,
284 'OKC_API.G_RET_STS_ERROR',
285 x_msg_count,
286 x_msg_data,
287 G_LEVEL);
288 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
289 x_return_status := OKC_API.HANDLE_EXCEPTIONS
290 (substr(l_api_name,1,26),
291 G_PKG_NAME,
292 'OKC_API.G_RET_STS_UNEXP_ERROR',
293 x_msg_count,
294 x_msg_data,
295 G_LEVEL);
296 WHEN OTHERS THEN
297 x_return_status := OKC_API.HANDLE_EXCEPTIONS
298 (substr(l_api_name,1,26),
299 G_PKG_NAME,
300 'OTHERS',
301 x_msg_count,
302 x_msg_data,
303 G_LEVEL);
304 END continue_k_process;
305
306 -- Start of comments
307 --
308 -- Procedure Name : k_approval_start
309 -- Description :
310 -- Business Rules :
311 -- Parameters :
312 -- Version : 1.0
313 -- End of comments
314
315 procedure k_approval_start(
316 p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 ,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 p_contract_id IN number,
322 p_process_id IN number,
323 p_do_commit IN VARCHAR2,
324 p_access_level IN VARCHAR2,
325 p_user_id IN NUMBER default null,
326 p_resp_id IN NUMBER default null,
327 p_resp_appl_id IN NUMBER default null,
328 p_security_group_id IN NUMBER default null
329 ) is
330 l_api_name CONSTANT VARCHAR2(30) := 'k_approval_start';
331 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
332 --
333 l_key varchar2(240);
334 l_contract_number varchar2(120);
335 l_contract_number_modifier varchar2(120);
336 l_date_approved date;
337 --
338 l_wf_name_active varchar2(150);
339 l_wf_name varchar2(150);
340 l_wf_process_name varchar2(150);
341 l_usage varchar2(60);
342 --
343 l_q varchar2(1);
344 --
345 L_PAR_NAME VARCHAR2(150);
346 L_PAR_TYPE VARCHAR2(90);
347 L_PAR_VALUE VARCHAR2(2000);
348 --
349 L_NLS_VALUE VARCHAR2(30);
350 --
351 L1_CPSV_REC OKC_CONTRACT_PUB.cpsv_rec_type;
352 L2_CPSV_REC OKC_CONTRACT_PUB.cpsv_rec_type;
353 l_msg_count NUMBER;
354 l_msg_data VARCHAR2(2000);
355 l_err_name VARCHAR2(30);
356 l_err_stack VARCHAR2(2000);
357
358 cursor k_pid is
359 select ID
360 from okc_k_processes_v
361 where CHR_ID = p_contract_id
362 and PDF_ID = p_process_id
363 --because of bug in lock API
364 for update of process_id nowait;
365 --
366 cursor k_header_csr is
367 select H.CONTRACT_NUMBER,
368 H.CONTRACT_NUMBER_MODIFIER,
369 H.DATE_APPROVED,
370 S.MEANING,
371 S.STE_CODE
372 from okc_k_headers_all_b H, okc_statuses_v S
373 where H.ID = p_contract_id
374 and H.STS_CODE=S.CODE;
375 l_status varchar2(100);
376 l_status_type varchar2(100);
377 --
378 cursor process_def_csr is
379 select WF_NAME, WF_PROCESS_NAME, USAGE
380 from OKC_PROCESS_DEFS_B
381 where ID = p_process_id
382 and begin_date<=sysdate
383 and (end_date is NULL or end_date>=sysdate) and PDF_TYPE='WPS';
384 --
385 cursor approval_active_csr is
386 select item_type
387 from WF_ITEMS
388 where item_type in
389 ( select wf_name
390 from OKC_PROCESS_DEFS_B
391 where USAGE='APPROVE' and PDF_TYPE='WPS')
392 and item_key = l_key
393 and end_date is NULL;
394
395 --
396 cursor for_purge_csr is
397 select '!'
398 from WF_ITEMS
399 where item_type = l_wf_name
400 and item_key = l_key;
401 --
402 cursor defined_parameters_csr is
403 select
404 NAME,
405 DATA_TYPE,
406 DEFAULT_VALUE
407 from OKC_PROCESS_DEF_PARAMETERS_V
408 where PDF_ID = p_process_id;
409 --
410 begin
411 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
412 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
413 'Entered '||G_PKG_NAME ||'.'||l_api_name);
414 END IF;
415 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
416 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
417 'p_contract_id=>'||p_contract_id||' p_process_id=>'||
418 p_process_id||' p_do_commit=>'|| p_do_commit);
419 END IF;
420 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
421 G_PKG_NAME,
422 p_init_msg_list,
423 l_api_version,
424 p_api_version,
425 G_LEVEL,
426 x_return_status);
427 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
428 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
429 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
430 RAISE OKC_API.G_EXCEPTION_ERROR;
431 END IF;
432 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
433 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
434 'OKC_API.START_ACTIVITY return status '||l_return_status);
435 END IF;
436 --
437 -- Modified for Bug 2046890
438 -- Below IF added because context does not set thru background processes
439 -- that's why need to bypass it
440 -- IF FND_GLOBAL.USER_ID <> -1 THEN
441 IF p_access_level = 'N' THEN
442 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
443 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
444 'k_accesible with p_level=U User Id '||fnd_global.user_id);
445 END IF;
446 if k_accesible( p_contract_id => p_contract_id,
447 p_user_id => fnd_global.user_id,
448 p_level => 'U'
449 ) = OKC_API.G_FALSE
450 then
451 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
452 p_msg_name => G_NO_U_PRIVILEGE);
453 raise OKC_API.G_EXCEPTION_ERROR;
454 end if;
455 END IF;
456 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
457 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
458 'Contract is accessible - get contract details');
459 END IF;
460 -- Modified for Bug 2046890
461 open k_header_csr;
462 fetch k_header_csr
463 into L_CONTRACT_NUMBER, L_CONTRACT_NUMBER_MODIFIER, L_DATE_APPROVED, L_STATUS, L_STATUS_TYPE;
464 close k_header_csr;
465 if (L_DATE_APPROVED is not NULL) then
466 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
467 p_msg_name => G_K_WAS_APPROVED);
468 raise OKC_API.G_EXCEPTION_ERROR;
469 end if;
470 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
471 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
472 'Got contract details L_STATUS_TYPE=>'||L_STATUS_TYPE);
473 END IF;
474 if (L_STATUS_TYPE <> 'ENTERED') then
475 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
476 p_msg_name => 'OKC_INVALID_K_STATUS',
477 p_token1 => 'NUMBER',
478 p_token1_value => L_CONTRACT_NUMBER||'-'||L_CONTRACT_NUMBER_MODIFIER,
479 p_token2 => 'STATUS',
480 p_token2_value => L_STATUS);
481 raise OKC_API.G_EXCEPTION_ERROR;
482 end if;
483 --
484 open process_def_csr;
485 fetch process_def_csr into L_WF_NAME, L_WF_PROCESS_NAME, L_USAGE;
486 close process_def_csr;
487 if (L_WF_NAME is NULL) then
488 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
489 p_msg_name => G_PROCESS_NOTFOUND);
490 raise OKC_API.G_EXCEPTION_ERROR;
491 end if;
492 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
493 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
494 'Got process def details L_WF_NAME=>'||L_WF_NAME||
495 ' L_WF_PROCESS_NAME=>'||L_WF_PROCESS_NAME||
496 ' L_USAGE=>'||L_USAGE);
497 END IF;
498 if (L_USAGE <> 'APPROVE') then
499 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
500 p_msg_name => G_PROCESS_NOT_FOR_APPROVAL,
501 p_token1 => G_WF_NAME_TOKEN,
502 p_token1_value => L_WF_NAME,
503 p_token2 => G_WF_P_NAME_TOKEN,
504 p_token2_value => L_WF_PROCESS_NAME);
505 raise OKC_API.G_EXCEPTION_ERROR;
506 end if;
507 --
508 L_KEY := L_CONTRACT_NUMBER||L_CONTRACT_NUMBER_MODIFIER;
509 open approval_active_csr;
510 fetch approval_active_csr into L_WF_NAME_ACTIVE;
511 close approval_active_csr;
512 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
513 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
514 'L_WF_NAME_ACTIVE=>'||L_WF_NAME_ACTIVE);
515 END IF;
516 if (L_WF_NAME_ACTIVE is not NULL) then
517 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
518 p_msg_name => G_K_ON_APPROVAL,
519 p_token1 => G_WF_NAME_TOKEN,
520 p_token1_value => L_WF_NAME_ACTIVE,
521 p_token2 => G_KEY_TOKEN,
522 p_token2_value => L_KEY);
523 raise OKC_API.G_EXCEPTION_ERROR;
524 end if;
525 --
526 L_Q:='?';
527 open for_purge_csr;
528 fetch for_purge_csr into L_Q;
529 close for_purge_csr;
530 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
531 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
532 'L_Q=>'||L_Q);
533 END IF;
534 if (L_Q = '!') then
535 begin
536 --- Bug#33096950 - wf_purge.total(l_wf_name,l_key);
537 wf_purge.total(l_wf_name,l_key,runtimeonly=>TRUE);
538 exception
539 when others then
540 begin
541 -- for Bug#3096950 - wf_purge.totalPerm(l_wf_name,l_key);
542 wf_purge.totalPerm(l_wf_name,l_key,runtimeonly=>TRUE);
543 exception
544 when others then
545 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
546 p_msg_name => G_WF_NOT_PURGED,
547 p_token1 => G_WF_NAME_TOKEN,
548 p_token1_value => L_WF_NAME,
549 p_token2 => G_KEY_TOKEN,
550 p_token2_value => L_KEY);
551 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
552 end;
553 end;
554 end if;
555 --
556 savepoint BECAUSE_OF_BUG_IN_lock;
557 begin
558 open k_pid;
559 fetch k_pid into L1_CPSV_REC.id;
560 close k_pid;
561 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
562 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
563 ' L1_CPSV_REC.id=>'|| L1_CPSV_REC.id);
564 END IF;
565 exception
566 when others then
567 rollback to BECAUSE_OF_BUG_IN_lock;
568 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
569 RAISE OKC_API.G_EXCEPTION_ERROR;
570 end;
571 L1_CPSV_REC.PROCESS_ID := L_KEY;
572 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
573 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name||'.external_call.before',
574 ' OKC_CONTRACT_PUB.update_contract_process L1_CPSV_REC.PROCESS_ID=>'||L1_CPSV_REC.PROCESS_ID);
575 END IF;
576 OKC_CONTRACT_PUB.update_contract_process(
577 p_api_version => l_api_version,
578 x_return_status => l_return_status,
579 x_msg_count => l_msg_count,
580 x_msg_data => l_msg_data,
581 p_cpsv_rec => L1_CPSV_REC,
582 x_cpsv_rec => L2_CPSV_REC);
583 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
584 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name||'.external_call.after',
585 ' OKC_CONTRACT_PUB.update_contract_process x_return_status=>'||l_return_status);
586 END IF;
587 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
588 rollback to BECAUSE_OF_BUG_IN_lock;
589 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
590 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
591 rollback to BECAUSE_OF_BUG_IN_lock;
592 RAISE OKC_API.G_EXCEPTION_ERROR;
593 END IF;
594 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
595 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
596 ' wf_engine.CreateProcess(ItemType=>'||L_WF_NAME||
597 ' ItemKey=>'||L_KEY||' process=>'||L_WF_PROCESS_NAME||')');
598 END IF;
599 wf_engine.CreateProcess( ItemType => L_WF_NAME,
600 ItemKey => L_KEY,
601 process => L_WF_PROCESS_NAME);
602 wf_engine.SetItemUserKey (ItemType => L_WF_NAME,
603 ItemKey => L_KEY,
604 UserKey => L_KEY);
605 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
606 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
607 'Setting wf item attributes');
608 END IF;
609 begin
610 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
611 itemkey => L_KEY,
612 aname => 'PROCESS_ID',
613 avalue => p_process_id);
614 exception
615 when others then
616 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
617 itemkey => L_KEY,
618 aname => 'PROCESS_ID');
619 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
620 itemkey => L_KEY,
621 aname => 'PROCESS_ID',
622 avalue => p_process_id);
623 end;
624 --
625 begin
626 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
627 itemkey => L_KEY,
628 aname => 'CONTRACT_ID',
629 avalue => p_contract_id);
630 exception
631 when others then
632 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
633 itemkey => L_KEY,
634 aname => 'CONTRACT_ID');
635 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
636 itemkey => L_KEY,
637 aname => 'CONTRACT_ID',
638 avalue => p_contract_id);
639 end;
640 begin
641 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
642 itemkey => L_KEY,
643 aname => 'CONTRACT_NUMBER',
644 avalue => l_contract_number);
645 exception
646 when others then
647 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
648 itemkey => L_KEY,
649 aname => 'CONTRACT_NUMBER');
650 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
651 itemkey => L_KEY,
652 aname => 'CONTRACT_NUMBER',
653 avalue => l_contract_number);
654 end;
655 begin
656 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
657 itemkey => L_KEY,
658 aname => 'CONTRACT_NUMBER_MODIFIER',
659 avalue => l_contract_number_MODIFIER);
660 exception
661 when others then
662 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
663 itemkey => L_KEY,
664 aname => 'CONTRACT_NUMBER_MODIFIER');
665 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
666 itemkey => L_KEY,
667 aname => 'CONTRACT_NUMBER_MODIFIER',
668 avalue => l_contract_number_MODIFIER);
669 end;
670 begin
671 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
672 itemkey => L_KEY,
673 aname => 'USER_ID',
674 avalue => NVL(p_user_id,fnd_global.user_id));
675 exception
676 when others then
677 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
678 itemkey => L_KEY,
679 aname => 'USER_ID');
680 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
681 itemkey => L_KEY,
682 aname => 'USER_ID',
683 avalue => NVL(p_user_id,fnd_global.user_id));
684 end;
685 begin
686 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
687 itemkey => L_KEY,
688 aname => 'RESP_ID',
689 avalue => NVL(p_resp_id,fnd_global.resp_id));
690 exception
691 when others then
692 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
693 itemkey => L_KEY,
694 aname => 'RESP_ID');
695 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
696 itemkey => L_KEY,
697 aname => 'RESP_ID',
698 avalue => NVL(p_resp_id,fnd_global.resp_id));
699 end;
700 begin
701 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
702 itemkey => L_KEY,
703 aname => 'RESP_APPL_ID',
704 avalue => NVL(p_resp_appl_id,fnd_global.RESP_APPL_id));
705 exception
706 when others then
707 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
708 itemkey => L_KEY,
709 aname => 'RESP_APPL_ID');
710 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
711 itemkey => L_KEY,
712 aname => 'RESP_APPL_ID',
713 avalue => NVL(p_resp_appl_id,fnd_global.RESP_APPL_id));
714 end;
715 begin
716 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
717 itemkey => L_KEY,
718 aname => 'SECURITY_GROUP_ID',
719 avalue => NVL(p_security_group_id,fnd_global.SECURITY_GROUP_id));
720 exception
721 when others then
722 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
723 itemkey => L_KEY,
724 aname => 'SECURITY_GROUP_ID');
725 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
726 itemkey => L_KEY,
727 aname => 'SECURITY_GROUP_ID',
728 avalue => NVL(p_security_group_id,fnd_global.SECURITY_GROUP_id));
729 end;
730 select value into L_NLS_VALUE
731 from NLS_SESSION_PARAMETERS
732 where PARAMETER='NLS_LANGUAGE';
733 begin
734 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
735 itemkey => L_KEY,
736 aname => 'NLS_LANGUAGE',
737 avalue => L_NLS_VALUE);
738 exception
739 when others then
740 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
741 itemkey => L_KEY,
742 aname => 'NLS_LANGUAGE');
743 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
744 itemkey => L_KEY,
745 aname => 'NLS_LANGUAGE',
746 avalue => L_NLS_VALUE);
747 end;
748 select value into L_NLS_VALUE
749 from NLS_SESSION_PARAMETERS
750 where PARAMETER='NLS_DATE_FORMAT';
751 begin
752 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
753 itemkey => L_KEY,
754 aname => 'NLS_DATE_FORMAT',
755 avalue => L_NLS_VALUE);
756 exception
757 when others then
758 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
759 itemkey => L_KEY,
760 aname => 'NLS_DATE_FORMAT');
761 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
762 itemkey => L_KEY,
763 aname => 'NLS_DATE_FORMAT',
764 avalue => L_NLS_VALUE);
765 end;
766 select value into L_NLS_VALUE
767 from NLS_SESSION_PARAMETERS
768 where PARAMETER='NLS_DATE_LANGUAGE';
769 begin
770 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
771 itemkey => L_KEY,
772 aname => 'NLS_DATE_LANGUAGE',
773 avalue => L_NLS_VALUE);
774 exception
775 when others then
776 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
777 itemkey => L_KEY,
778 aname => 'NLS_DATE_LANGUAGE');
779 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
780 itemkey => L_KEY,
781 aname => 'NLS_DATE_LANGUAGE',
782 avalue => L_NLS_VALUE);
783 end;
784 select '"'||value||'"' into L_NLS_VALUE
785 from NLS_SESSION_PARAMETERS
786 where PARAMETER='NLS_NUMERIC_CHARACTERS';
787 begin
788 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
789 itemkey => L_KEY,
790 aname => 'NLS_NUMERIC_CHARACTERS',
791 avalue => L_NLS_VALUE);
792 exception
793 when others then
794 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
795 itemkey => L_KEY,
796 aname => 'NLS_NUMERIC_CHARACTERS');
797 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
798 itemkey => L_KEY,
799 aname => 'NLS_NUMERIC_CHARACTERS',
800 avalue => L_NLS_VALUE);
801 end;
802 wf_engine.SetItemOwner ( itemtype => L_WF_NAME,
803 itemkey => L_KEY,
804 owner => fnd_global.user_name);
805 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
806 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
807 'Starting approval wf');
808 END IF;
809
810 BEGIN
811 wf_engine.StartProcess( itemtype => L_WF_NAME,
812 itemkey => L_KEY);
813 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
814 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
815 'Successfully started approval wf');
816 END IF;
817 EXCEPTION
818 WHEN OTHERS THEN
819 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
820 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
821 'In others exception ');
822 l_msg_data := substr(sqlerrm,1,2000);
823 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name, l_msg_data);
824 END IF;
825 IF l_msg_data IS NULL THEN
826 BEGIN
827 wf_core.get_error
828 (
829 err_name => l_err_name,
830 err_message => l_msg_data,
831 err_stack => l_err_stack,
832 maxerrstacklength => 2000
833 );
834 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
835 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
836 'Error starting approval wf');
837 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
838 l_msg_data);
839 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
840 l_err_stack);
841 END IF;
842 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, l_err_stack);
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
846 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
847 'In exception for wf_core.get_error: '||substr(sqlerrm,1,300));
848 END IF;
849 END;
850 END IF;
851 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, l_msg_data);
852 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
853 END;
854 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
855 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
856 'Updating okc header ');
857 END IF;
858
859 --Added to fix the bug#3269709
860 --issues lock if multiple users are accessing the same contract
861 UPDATE okc_k_headers_all_b
862 SET OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
863 WHERE ID=p_contract_id;
864 --
865 if (p_do_commit = OKC_API.G_TRUE) then
866 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
867 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
868 'Committing.. ');
869 END IF;
870 commit;
871 end if;
872 x_return_status := OKC_API.G_RET_STS_SUCCESS;
873 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
874 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
875 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,G_MODULE||l_api_name,
876 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
877 END IF;
878 EXCEPTION
879 WHEN OKC_API.G_EXCEPTION_ERROR THEN
880 x_return_status := OKC_API.HANDLE_EXCEPTIONS
881 (substr(l_api_name,1,26),
882 G_PKG_NAME,
883 'OKC_API.G_RET_STS_ERROR',
884 x_msg_count,
885 x_msg_data,
886 G_LEVEL);
887 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
888 x_return_status := OKC_API.HANDLE_EXCEPTIONS
889 (substr(l_api_name,1,26),
890 G_PKG_NAME,
891 'OKC_API.G_RET_STS_UNEXP_ERROR',
892 x_msg_count,
893 x_msg_data,
894 G_LEVEL);
895 WHEN OTHERS THEN
896 x_return_status := OKC_API.HANDLE_EXCEPTIONS
897 (substr(l_api_name,1,26),
898 G_PKG_NAME,
899 'OTHERS',
900 x_msg_count,
901 x_msg_data,
902 G_LEVEL);
903 end k_approval_start;
904
905 -- Start of comments
906 --
907 -- Procedure Name : wf_monitor_url
908 -- Description :
909 -- Business Rules :
910 -- Parameters :
911 -- Version : 1.0
912 -- End of comments
913
914 function wf_monitor_url(
915 p_contract_id IN number,
916 p_process_id IN number,
917 p_mode IN varchar2
918 ) return varchar2 is
919 -- to be used by fnd_utilities.open_url
920 l_wf_name varchar2(150);
921 l_key varchar2(240);
922 l_q varchar2(1);
923 l_admin varchar2(3);
924 --
925 cursor wf_name_csr is
926 select WF_NAME
927 from OKC_PROCESS_DEFS_B
928 where ID = p_process_id and PDF_TYPE='WPS';
929 --
930 cursor wf_key_csr is
931 select CONTRACT_NUMBER||CONTRACT_NUMBER_MODIFIER wf_key
932 from okc_k_headers_all_b
933 where ID = p_contract_id;
934 --
935 cursor wf_exist_csr is
936 select '!'
937 from WF_ITEMS
938 where item_type = l_wf_name
939 and item_key = l_key;
940 --
941 begin
942 open wf_name_csr;
943 fetch wf_name_csr into L_WF_NAME;
944 close wf_name_csr;
945 --
946 open wf_key_csr;
947 fetch wf_key_csr into L_KEY;
948 close wf_key_csr;
949 --
950 l_q := '?';
951 open wf_exist_csr;
952 fetch wf_exist_csr into L_Q;
953 close wf_exist_csr;
954 --
955 if l_q = '?' then return NULL;
956 else
957 if p_mode = 'ADMIN' then l_admin := 'YES';
958 else l_admin := 'NO';
959 end if;
960 return wf_monitor.GetDiagramURL(
961 X_AGENT => WF_CORE.TRANSLATE('WF_WEB_AGENT'),
962 X_ITEM_TYPE => L_WF_NAME,
963 X_ITEM_KEY => L_KEY,
964 X_ADMIN_MODE => l_admin);
965 end if;
966 end wf_monitor_url;
967
968 -- Start of comments
969 --
970 -- Procedure Name : k_approval_stop
971 -- Description :
972 -- Business Rules :
973 -- Parameters :
974 -- Version : 1.0
975 -- End of comments
976
977 procedure k_approval_stop(
978 p_api_version IN NUMBER,
979 p_init_msg_list IN VARCHAR2 ,
980 x_return_status OUT NOCOPY VARCHAR2,
981 x_msg_count OUT NOCOPY NUMBER,
982 x_msg_data OUT NOCOPY VARCHAR2,
983 p_contract_id number,
984 p_do_commit IN VARCHAR2
985 ) is
986 l_api_name CONSTANT VARCHAR2(30) := 'k_approval_stop';
987 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
988 --
989 l_q varchar2(1);
990 l_scs_code varchar2(30);
991 l_key varchar2(240);
992 l_wf_name_active varchar2(150);
993 l_contract_number varchar2(120);
994 l_contract_number_modifier varchar2(120);
995 L_K_SHORT_DESCRIPTION varchar2(2000);
996 L_NLS_VALUE VARCHAR2(30);
997 --
998 cursor wf_key_csr is
999 select CONTRACT_NUMBER||CONTRACT_NUMBER_MODIFIER wf_key,
1000 CONTRACT_NUMBER, CONTRACT_NUMBER_MODIFIER,
1001 short_description, scs_code
1002 from OKC_K_HDR_AGREEDS_V
1003 where ID = p_contract_id;
1004 --
1005 cursor approval_active_csr is
1006 select item_type
1007 from WF_ITEMS
1008 where item_type in
1009 ( select wf_name
1010 from OKC_PROCESS_DEFS_B
1011 where USAGE='APPROVE' and PDF_TYPE='WPS')
1012 and item_key = l_key
1013 and end_date is NULL;
1014 --
1015 cursor abort_csr is
1016 select '!'
1017 from wf_activities
1018 where item_type=l_wf_name_active
1019 and TYPE='PROCESS' and NAME='ABORT_PROCESS'
1020 ;
1021 --
1022 cursor C_INITIATOR_DISPLAY_NAME is
1023 /*
1024 select display_name
1025 from wf_roles
1026 where orig_system = 'FND_USR'
1027 and orig_system_id=fnd_global.user_id
1028 -- changed to boost perf
1029 */
1030 select user_name display_name from fnd_user where user_id=fnd_global.user_id and EMPLOYEE_ID is null
1031 union all
1032 select
1033 PER.FULL_NAME display_name
1034 from
1035 PER_PEOPLE_F PER,
1036 FND_USER USR
1037 where trunc(SYSDATE)
1038 between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE
1039 and PER.PERSON_ID = USR.EMPLOYEE_ID
1040 and USR.USER_ID = fnd_global.user_id
1041 ;
1042
1043 CURSOR k_process_csr(p_contract_id IN NUMBER ) IS
1044 SELECT wf_item_key FROM OKS_K_HEADERS_B
1045 WHERE chr_id = p_contract_id;
1046 --
1047 L_INITIATOR_NAME varchar2(100);
1048 L_FINAL_APPROVER_UNAME varchar2(100);
1049 L_INITIATOR_DISPLAY_NAME varchar2(200);
1050 l_rownotfound BOOLEAN := FALSE;
1051 l_wf_attributes OKS_WF_K_PROCESS_PVT.WF_ATTR_DETAILS;
1052 l_wf_item_key VARCHAR2(240);
1053 --
1054 begin
1055 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1056 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1057 'Entered '||G_PKG_NAME ||'.'||l_api_name);
1058 END IF;
1059 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1060 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1061 'p_contract_id=>'||p_contract_id||
1062 ' p_do_commit=>'|| p_do_commit);
1063 END IF;
1064 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1065 G_PKG_NAME,
1066 p_init_msg_list,
1067 l_api_version,
1068 p_api_version,
1069 G_LEVEL,
1070 x_return_status);
1071 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1072 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1073 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1074 RAISE OKC_API.G_EXCEPTION_ERROR;
1075 END IF;
1076 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1077 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1078 'OKC_API.START_ACTIVITY return status '||l_return_status);
1079 END IF;
1080 --
1081 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1082 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1083 'k_accesible with p_level=U User Id '||fnd_global.user_id);
1084 END IF;
1085 if k_accesible( p_contract_id => p_contract_id,
1086 p_user_id => fnd_global.user_id,
1087 p_level => 'U'
1088 ) = OKC_API.G_FALSE
1089 then
1090 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1091 p_msg_name => G_NO_U_PRIVILEGE);
1092 raise OKC_API.G_EXCEPTION_ERROR;
1093 end if;
1094 --
1095 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1096 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1097 'Contract is accessible - get contract details');
1098 END IF;
1099 open wf_key_csr;
1100 fetch wf_key_csr into L_KEY,l_contract_number,l_contract_number_modifier,L_K_SHORT_DESCRIPTION, l_scs_code;
1101 close wf_key_csr;
1102 --
1103 open approval_active_csr;
1104 fetch approval_active_csr into l_wf_name_active;
1105 close approval_active_csr;
1106 --
1107 if l_wf_name_active is NULL then
1108 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1109 p_msg_name => G_K_NOT_ON_APPROVAL);
1110 raise OKC_API.G_EXCEPTION_ERROR;
1111 end if;
1112 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1113 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1114 'Got wf details l_wf_name_active=>'||l_wf_name_active);
1115 END IF;
1116 wf_engine.abortprocess(l_wf_name_active,l_key);
1117 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1118 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name||'.external_call.before',
1119 'k_erase_approved p_contract_id=>'||p_contract_id);
1120 END IF;
1121 k_erase_approved(
1122 p_contract_id => p_contract_id,
1123 x_return_status => l_return_status
1124 );
1125 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1126 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name||'.external_call.before',
1127 'k_erase_approved x_return_status=>'||l_return_status);
1128 END IF;
1129 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1130 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1131 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1132 RAISE OKC_API.G_EXCEPTION_ERROR;
1133 END IF;
1134 --+
1135 L_Q:='?';
1136 open abort_csr;
1137 fetch abort_csr into L_Q;
1138 close abort_csr;
1139 --+
1140 --+ if abort process defined
1141 --+
1142 if (L_Q = '!') then
1143 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1144 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1145 'Abort process defined ');
1146 END IF;
1147 -- previous initiator Uname
1148 L_INITIATOR_NAME := wf_engine.GetItemAttrText(l_wf_name_active,L_KEY,'INITIATOR_NAME');
1149 -- last approver Uname
1150 L_FINAL_APPROVER_UNAME := NVL(
1151 wf_engine.GetItemAttrText(l_wf_name_active,L_KEY,'NEXT_PERFORMER_USERNAME'),
1152 wf_engine.GetItemAttrText(l_wf_name_active,L_KEY,'FINAL_APPROVER_UNAME')
1153 );
1154 if (L_FINAL_APPROVER_UNAME = L_INITIATOR_NAME) then
1155 L_FINAL_APPROVER_UNAME := NULL;
1156 end if;
1157 begin
1158 --Bug#3096950 - wf_purge.total(l_wf_name_active,l_key);
1159 wf_purge.total(l_wf_name_active,l_key,runtimeonly=>TRUE);
1160 exception
1161 when others then
1162 begin
1163 --for Bug#3096950 - wf_purge.totalPerm(l_wf_name_active,l_key);
1164 wf_purge.totalPerm(l_wf_name_active, l_key, runtimeonly=>TRUE);
1165 exception
1166 when others then
1167 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1168 p_msg_name => G_WF_NOT_PURGED,
1169 p_token1 => G_WF_NAME_TOKEN,
1170 p_token1_value => l_wf_name_active,
1171 p_token2 => G_KEY_TOKEN,
1172 p_token2_value => L_KEY);
1173 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1174 end;
1175 end;
1176 wf_engine.CreateProcess( ItemType => l_wf_name_active,
1177 ItemKey => L_KEY,
1178 process => 'ABORT_PROCESS');
1179 wf_engine.SetItemUserKey (ItemType => l_wf_name_active,
1180 ItemKey => L_KEY,
1181 UserKey => L_KEY);
1182 --+
1183 --+ attributes
1184 --+
1185 begin
1186 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
1187 itemkey => L_KEY,
1188 aname => 'CONTRACT_ID',
1189 avalue => p_contract_id);
1190 exception
1191 when others then
1192 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1193 itemkey => L_KEY,
1194 aname => 'CONTRACT_ID');
1195 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
1196 itemkey => L_KEY,
1197 aname => 'CONTRACT_ID',
1198 avalue => p_contract_id);
1199 end;
1200 begin
1201 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1202 itemkey => L_KEY,
1203 aname => 'CONTRACT_NUMBER',
1204 avalue => l_contract_number);
1205 exception
1206 when others then
1207 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1208 itemkey => L_KEY,
1209 aname => 'CONTRACT_NUMBER');
1210 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1211 itemkey => L_KEY,
1212 aname => 'CONTRACT_NUMBER',
1213 avalue => l_contract_number);
1214 end;
1215 begin
1216 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1217 itemkey => L_KEY,
1218 aname => 'CONTRACT_NUMBER_MODIFIER',
1219 avalue => l_contract_number_MODIFIER);
1220 exception
1221 when others then
1222 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1223 itemkey => L_KEY,
1224 aname => 'CONTRACT_NUMBER_MODIFIER');
1225 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1226 itemkey => L_KEY,
1227 aname => 'CONTRACT_NUMBER_MODIFIER',
1228 avalue => l_contract_number_MODIFIER);
1229 end;
1230 begin
1231 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1232 itemkey => L_KEY,
1233 aname => 'K_SHORT_DESCRIPTION',
1234 avalue => L_K_SHORT_DESCRIPTION);
1235 exception
1236 when others then
1237 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1238 itemkey => L_KEY,
1239 aname => 'K_SHORT_DESCRIPTION');
1240 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1241 itemkey => L_KEY,
1242 aname => 'K_SHORT_DESCRIPTION',
1243 avalue => L_K_SHORT_DESCRIPTION);
1244 end;
1245 -- current initiator Dname
1246 open C_INITIATOR_DISPLAY_NAME;
1247 fetch C_INITIATOR_DISPLAY_NAME into L_INITIATOR_DISPLAY_NAME;
1248 close C_INITIATOR_DISPLAY_NAME;
1249 begin
1250 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1251 itemkey => L_KEY,
1252 aname => 'INITIATOR_DISPLAY_NAME',
1253 avalue => L_INITIATOR_DISPLAY_NAME);
1254 exception
1255 when others then
1256 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1257 itemkey => L_KEY,
1258 aname => 'INITIATOR_DISPLAY_NAME');
1259 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1260 itemkey => L_KEY,
1261 aname => 'INITIATOR_DISPLAY_NAME',
1262 avalue => L_INITIATOR_DISPLAY_NAME);
1263 end;
1264 -- previous initiator Uname
1265 begin
1266 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1267 itemkey => L_KEY,
1268 aname => 'INITIATOR_NAME',
1269 avalue => L_INITIATOR_NAME);
1270 exception
1271 when others then
1272 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1273 itemkey => L_KEY,
1274 aname => 'INITIATOR_NAME');
1275 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1276 itemkey => L_KEY,
1277 aname => 'INITIATOR_NAME',
1278 avalue => L_INITIATOR_NAME);
1279 end;
1280 -- previous approver Uname
1281 begin
1282 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1283 itemkey => L_KEY,
1284 aname => 'FINAL_APPROVER_UNAME',
1285 avalue => L_FINAL_APPROVER_UNAME);
1286 exception
1287 when others then
1288 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1289 itemkey => L_KEY,
1290 aname => 'FINAL_APPROVER_UNAME');
1291 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1292 itemkey => L_KEY,
1293 aname => 'FINAL_APPROVER_UNAME',
1294 avalue => L_FINAL_APPROVER_UNAME);
1295 end;
1296 --
1297 -- environment
1298 --
1299 begin
1300 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
1301 itemkey => L_KEY,
1302 aname => 'USER_ID',
1303 avalue => fnd_global.user_id);
1304 exception
1305 when others then
1306 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1307 itemkey => L_KEY,
1308 aname => 'USER_ID');
1309 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
1310 itemkey => L_KEY,
1311 aname => 'USER_ID',
1312 avalue => fnd_global.user_id);
1313 end;
1314 begin
1315 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
1316 itemkey => L_KEY,
1317 aname => 'RESP_ID',
1318 avalue => fnd_global.resp_id);
1319 exception
1320 when others then
1321 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1322 itemkey => L_KEY,
1323 aname => 'RESP_ID');
1324 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
1325 itemkey => L_KEY,
1326 aname => 'RESP_ID',
1327 avalue => fnd_global.resp_id);
1328 end;
1329 begin
1330 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
1331 itemkey => L_KEY,
1332 aname => 'RESP_APPL_ID',
1333 avalue => fnd_global.RESP_APPL_id);
1334 exception
1335 when others then
1336 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1337 itemkey => L_KEY,
1338 aname => 'RESP_APPL_ID');
1339 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
1340 itemkey => L_KEY,
1341 aname => 'RESP_APPL_ID',
1342 avalue => fnd_global.RESP_APPL_id);
1343 end;
1344 begin
1345 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
1346 itemkey => L_KEY,
1347 aname => 'SECURITY_GROUP_ID',
1348 avalue => fnd_global.SECURITY_GROUP_id);
1349 exception
1350 when others then
1351 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1352 itemkey => L_KEY,
1353 aname => 'SECURITY_GROUP_ID');
1354 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
1355 itemkey => L_KEY,
1356 aname => 'SECURITY_GROUP_ID',
1357 avalue => fnd_global.SECURITY_GROUP_id);
1358 end;
1359 select value into L_NLS_VALUE
1360 from NLS_SESSION_PARAMETERS
1361 where PARAMETER='NLS_LANGUAGE';
1362 begin
1363 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1364 itemkey => L_KEY,
1365 aname => 'NLS_LANGUAGE',
1366 avalue => L_NLS_VALUE);
1367 exception
1368 when others then
1369 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1370 itemkey => L_KEY,
1371 aname => 'NLS_LANGUAGE');
1372 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1373 itemkey => L_KEY,
1374 aname => 'NLS_LANGUAGE',
1375 avalue => L_NLS_VALUE);
1376 end;
1377 select value into L_NLS_VALUE
1378 from NLS_SESSION_PARAMETERS
1379 where PARAMETER='NLS_DATE_FORMAT';
1380 begin
1381 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1382 itemkey => L_KEY,
1383 aname => 'NLS_DATE_FORMAT',
1384 avalue => L_NLS_VALUE);
1385 exception
1386 when others then
1387 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1388 itemkey => L_KEY,
1389 aname => 'NLS_DATE_FORMAT');
1390 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1391 itemkey => L_KEY,
1392 aname => 'NLS_DATE_FORMAT',
1393 avalue => L_NLS_VALUE);
1394 end;
1395 select value into L_NLS_VALUE
1396 from NLS_SESSION_PARAMETERS
1397 where PARAMETER='NLS_DATE_LANGUAGE';
1398 begin
1399 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1400 itemkey => L_KEY,
1401 aname => 'NLS_DATE_LANGUAGE',
1402 avalue => L_NLS_VALUE);
1403 exception
1404 when others then
1405 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1406 itemkey => L_KEY,
1407 aname => 'NLS_DATE_LANGUAGE');
1408 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1409 itemkey => L_KEY,
1410 aname => 'NLS_DATE_LANGUAGE',
1411 avalue => L_NLS_VALUE);
1412 end;
1413 select '"'||value||'"' into L_NLS_VALUE
1414 from NLS_SESSION_PARAMETERS
1415 where PARAMETER='NLS_NUMERIC_CHARACTERS';
1416 begin
1417 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1418 itemkey => L_KEY,
1419 aname => 'NLS_NUMERIC_CHARACTERS',
1420 avalue => L_NLS_VALUE);
1421 exception
1422 when others then
1423 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1424 itemkey => L_KEY,
1425 aname => 'NLS_NUMERIC_CHARACTERS');
1426 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1427 itemkey => L_KEY,
1428 aname => 'NLS_NUMERIC_CHARACTERS',
1429 avalue => L_NLS_VALUE);
1430 end;
1431 --
1432 -- start
1433 --
1434 wf_engine.SetItemOwner ( itemtype => l_wf_name_active,
1435 itemkey => L_KEY,
1436 owner => fnd_global.user_name);
1437 wf_engine.StartProcess( itemtype => l_wf_name_active,
1438 itemkey => L_KEY);
1439
1440 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1441 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1442 'Started Abort process successfully');
1443 END IF;
1444 end if;--+ abort process exists
1445 --
1446 -- Launch process workflow for existing service contracts created prior to r12 as they would not have process workflow associated
1447 -- as they had been in the Approval node
1448 --
1449 if l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') THEN
1450 open k_process_csr(p_contract_id);
1451 fetch k_process_csr INTO l_wf_item_key;
1452 l_rownotfound := k_process_csr%NOTFOUND;
1453 CLOSE k_process_csr;
1454 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1455 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1456 'l_wf_item_key=> '||l_wf_item_key);
1457 END IF;
1458 IF l_rownotfound OR l_wf_item_key IS NULL THEN
1459 l_wf_attributes.CONTRACT_ID := p_contract_id;
1460 l_wf_attributes.CONTRACT_NUMBER := l_contract_number;
1461 l_wf_attributes.CONTRACT_MODIFIER := l_contract_number_modifier;
1462 l_wf_attributes.NEGOTIATION_STATUS := 'ACT';
1463 l_wf_attributes.ITEM_KEY := p_contract_id||to_char(sysdate,'YYYYMMDDHH24MISS');
1464 l_wf_attributes.IRR_FLAG := 'Y';
1465 l_wf_attributes.PROCESS_TYPE := 'MANUAL';
1466 x_return_status := 'S';
1467 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1468 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
1469 ' OKS_WF_K_PROCESS_PVT.launch_k_process_wf p_contract_id '||p_contract_id);
1470 END IF;
1471 OKS_WF_K_PROCESS_PVT.launch_k_process_wf
1472 (
1473 p_api_version => 1.0,
1474 p_init_msg_list => 'T',
1475 p_wf_attributes => l_wf_attributes,
1476 x_return_status => l_return_status,
1477 x_msg_count => x_msg_count,
1478 x_msg_data => x_msg_data
1479 ) ;
1480 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1481 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
1482 'OKS_WF_K_PROCESS_PVT.launch_k_process_wf l_return_status=>'||l_return_status);
1483 END IF;
1484 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1485 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1486 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1487 RAISE OKC_API.G_EXCEPTION_ERROR;
1488 END IF;
1489 UPDATE oks_k_headers_b
1490 SET object_version_number = object_version_number + 1,
1491 wf_item_key = l_wf_attributes.ITEM_KEY,
1492 renewal_status = l_wf_attributes.NEGOTIATION_STATUS,
1493 last_update_date = SYSDATE,
1494 last_update_login = FND_GLOBAL.LOGIN_ID,
1495 Last_updated_by = FND_GLOBAL.USER_ID
1496 WHERE chr_id = l_wf_attributes.contract_id;
1497 ELSE
1498 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1499 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
1500 'continue_k_process(p_contract_id=>'||p_contract_id||')');
1501 END IF;
1502 -- Complete the Service Contracts Process workflow
1503 continue_k_process
1504 (
1505 p_api_version => l_api_version,
1506 p_init_msg_list => p_init_msg_list,
1507 x_return_status => x_return_status,
1508 x_msg_count => x_msg_count,
1509 x_msg_data => x_msg_data,
1510 p_contract_id => p_contract_id,
1511 p_wf_item_key => l_wf_item_key,
1512 p_called_from => 'STOP'
1513 );
1514 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1515 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
1516 'continue_k_process(x_return_status=>'||x_return_status||')');
1517 END IF;
1518 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1519 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1520 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1521 RAISE OKC_API.G_EXCEPTION_ERROR;
1522 END IF;
1523 END IF;
1524 END IF;
1525 if (p_do_commit = OKC_API.G_TRUE) then
1526 commit;
1527 end if;
1528 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1529 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1530 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1531 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,G_MODULE||l_api_name,
1532 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
1533 END IF;
1534 EXCEPTION
1535 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1536 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1537 (substr(l_api_name,1,26),
1538 G_PKG_NAME,
1539 'OKC_API.G_RET_STS_ERROR',
1540 x_msg_count,
1541 x_msg_data,
1542 G_LEVEL);
1543 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1544 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1545 (substr(l_api_name,1,26),
1546 G_PKG_NAME,
1547 'OKC_API.G_RET_STS_UNEXP_ERROR',
1548 x_msg_count,
1549 x_msg_data,
1550 G_LEVEL);
1551 WHEN OTHERS THEN
1552 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1553 (substr(l_api_name,1,26),
1554 G_PKG_NAME,
1555 'OTHERS',
1556 x_msg_count,
1557 x_msg_data,
1558 G_LEVEL);
1559 end k_approval_stop;
1560
1561 -- Start of comments
1562 --
1563 -- Procedure Name : wf_copy_env
1564 -- Description :
1565 -- Business Rules :
1566 -- Parameters :
1567 -- Version : 1.0
1568 -- End of comments
1569
1570 procedure wf_copy_env( p_item_type varchar2,
1571 p_item_key varchar2) is
1572 L_NLS_VALUE1 varchar2(40);
1573 L_NLS_VALUE2 varchar2(40);
1574 L_NLS_VALUE3 varchar2(40);
1575 L_NLS_VALUE4 varchar2(40);
1576 L_NLS_VALUE11 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_LANGUAGE');
1577 L_NLS_VALUE12 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_DATE_FORMAT');
1578 L_NLS_VALUE13 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_DATE_LANGUAGE');
1579 L_NLS_VALUE14 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_NUMERIC_CHARACTERS');
1580 cursor c1(p varchar2) is
1581 select value
1582 from NLS_SESSION_PARAMETERS
1583 where PARAMETER=p;
1584 begin
1585 open c1('NLS_LANGUAGE');
1586 fetch c1 into L_NLS_VALUE1;
1587 close c1;
1588 open c1('NLS_DATE_FORMAT');
1589 fetch c1 into L_NLS_VALUE2;
1590 close c1;
1591 open c1('NLS_DATE_LANGUAGE');
1592 fetch c1 into L_NLS_VALUE3;
1593 close c1;
1594 open c1('NLS_NUMERIC_CHARACTERS');
1595 fetch c1 into L_NLS_VALUE4;
1596 L_NLS_VALUE4 := '"'||L_NLS_VALUE4||'"';
1597 close c1;
1598 if not(
1599 (L_NLS_VALUE11 = L_NLS_VALUE1) and
1600 (L_NLS_VALUE12 = L_NLS_VALUE2) and
1601 (L_NLS_VALUE13 = L_NLS_VALUE3) and
1602 (L_NLS_VALUE14 = L_NLS_VALUE4)
1603 ) then
1604 fnd_global.set_nls_context
1605 (
1606 P_NLS_LANGUAGE => L_NLS_VALUE11,
1607 P_NLS_DATE_FORMAT => L_NLS_VALUE12,
1608 P_NLS_DATE_LANGUAGE => L_NLS_VALUE13,
1609 P_NLS_NUMERIC_CHARACTERS => L_NLS_VALUE14);
1610 end if;
1611 fnd_global.apps_initialize
1612 (
1613 user_id =>
1614 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'USER_ID'),
1615 resp_id =>
1616 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'RESP_ID'),
1617 resp_appl_id =>
1618 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'RESP_APPL_ID'),
1619 security_group_id =>
1620 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'SECURITY_GROUP_ID')
1621 );
1622 -- okc_context.set_okc_org_context;
1623 end wf_copy_env;
1624
1625 -- Start of comments
1626 --
1627 -- Procedure Name : k_accesible
1628 -- Description :
1629 -- Business Rules :
1630 -- Parameters :
1631 -- Version : 1.0
1632 -- End of comments
1633
1634 function k_accesible(
1635 p_contract_id IN number,
1636 p_user_id IN number,
1637 p_level IN varchar2
1638 ) return varchar2 is
1639 l_q varchar2(1);
1640 /*
1641 cursor check_access_csr is
1642 (select '!'
1643 from OKC_K_ACCESSES
1644 where chr_id = p_contract_id
1645 and user_id = p_user_id
1646 and (p_level='R' or access_level='U')
1647 )
1648 UNION ALL
1649 (select '!' from dual
1650 where exists
1651 (select agp_code
1652 from OKC_K_ACCESSES
1653 where chr_id = P_CONTRACT_ID
1654 and (p_level='R' or access_level='U')
1655 INTERSECT
1656 SELECT AGP_CODE
1657 FROM okc_acc_group_members
1658 where begin_date<=sysdate
1659 and (end_date is null or end_date>=sysdate)
1660 start with user_id = p_user_id
1661 CONNECT BY PRIOR AGP_CODE = AGP_CODE_COMPOSED_OF
1662 )
1663 );
1664 */
1665 begin
1666 l_q :=okc_util.get_k_access_level(p_chr_id => p_contract_id);
1667 if ((l_q = p_level) or (l_q = 'U'))
1668 then return OKC_API.G_TRUE;
1669 else return OKC_API.G_FALSE;
1670 end if;
1671 /*
1672 --
1673 open check_access_csr;
1674 fetch check_access_csr into l_q;
1675 close check_access_csr;
1676 --
1677
1678 if l_q = '?' then return OKC_API.G_FALSE;
1679 else return OKC_API.G_TRUE;
1680 end if;
1681 */
1682
1683 end k_accesible;
1684
1685 --
1686 -- private procedure
1687 -- to set context of db failure
1688 --
1689 procedure db_failed(p_oper varchar2) is
1690 begin
1691 FND_MESSAGE.SET_NAME(application => G_APP_NAME,
1692 name => 'OKC_DB_OPERATION_FAILED');
1693 -- OKC_SIGN OKC_APPROVE OKC_REVOKE
1694 FND_MESSAGE.SET_TOKEN(token => 'OPERATION',
1695 value => p_oper,
1696 translate => TRUE);
1697 FND_MSG_PUB.add;
1698 end db_failed;
1699
1700
1701 -- Start of comments
1702 --
1703 -- Procedure Name : k_approved
1704 -- Description :
1705 -- Business Rules :
1706 -- Parameters :
1707 -- Version : 1.0
1708 -- End of comments
1709
1710 procedure k_approved(
1711 p_contract_id IN number,
1712 p_date_approved IN date ,
1713 x_return_status OUT NOCOPY varchar2
1714 ) is
1715 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1716 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1717 cursor lock_csr(p number) is
1718 select object_version_number , org_id --mmadhavi added org_id for MOAC project
1719 from okc_k_headers_all_b
1720 where ID = p
1721 ;
1722 l_api_name CONSTANT VARCHAR2(30) := 'k_approved';
1723 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1724 l_msg_count NUMBER;
1725 l_msg_data varchar2(2000);
1726 begin
1727
1728 --start
1729 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1730 G_PKG_NAME,
1731 OKC_API.G_TRUE,
1732 l_api_version,
1733 l_api_version,
1734 G_LEVEL,
1735 x_return_status);
1736 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1737 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1738 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1739 RAISE OKC_API.G_EXCEPTION_ERROR;
1740 END IF;
1741
1742 --lock
1743 L1_header_rec.id := p_contract_id;
1744 open lock_csr(p_contract_id);
1745 fetch lock_csr into L1_header_rec.object_version_number, L1_header_rec.org_id;
1746 close lock_csr;
1747
1748 --npalepu 02-DEC-2005 modified for the bug # 4775848
1749 --/Rules Migration/
1750 --Set context before validation, new rules columns require context for validations
1751 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(p_org_id => L1_header_rec.org_id) ;
1752 --
1753 --end npalepu
1754
1755 OKC_CONTRACT_PUB.lock_contract_header(
1756 p_api_version => l_api_version,
1757 x_return_status => x_return_status,
1758 x_msg_count => l_msg_count,
1759 x_msg_data => l_msg_data,
1760 p_chrv_rec => L1_header_rec);
1761 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1762 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1763 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1764 RAISE OKC_API.G_EXCEPTION_ERROR;
1765 END IF;
1766
1767 --update
1768 L1_header_rec.date_approved := p_date_approved;
1769
1770 --npalepu moved the context setting code to above the OKC_CONTRACT_PUB.lock_contract_header API.
1771 --for bug # 4775848 on 02-DEC-2005.
1772 /*--/Rules Migration/
1773 --Set context before validation, new rules columns require context for validations
1774 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(p_org_id => L1_header_rec.org_id) ;
1775 --*/
1776 --end npalepu
1777
1778 OKC_CONTRACT_PUB.update_contract_header(
1779 p_api_version => l_api_version,
1780 x_return_status => x_return_status,
1781 p_init_msg_list => OKC_API.G_TRUE,
1782 x_msg_count => l_msg_count,
1783 x_msg_data => l_msg_data,
1784 p_restricted_update => OKC_API.G_TRUE,
1785 p_chrv_rec => L1_header_rec,
1786 x_chrv_rec => L2_header_rec);
1787 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1788 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1789 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1790 RAISE OKC_API.G_EXCEPTION_ERROR;
1791 END IF;
1792
1793 --end
1794 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
1795 EXCEPTION
1796 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1797 db_failed('OKC_APPROVE');
1798 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1799 (substr(l_api_name,1,26),
1800 G_PKG_NAME,
1801 'OKC_API.G_RET_STS_ERROR',
1802 l_msg_count,
1803 l_msg_data,
1804 G_LEVEL);
1805 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1806 db_failed('OKC_APPROVE');
1807 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1808 (substr(l_api_name,1,26),
1809 G_PKG_NAME,
1810 'OKC_API.G_RET_STS_UNEXP_ERROR',
1811 l_msg_count,
1812 l_msg_data,
1813 G_LEVEL);
1814 WHEN OTHERS THEN
1815 db_failed('OKC_APPROVE');
1816 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1817 (substr(l_api_name,1,26),
1818 G_PKG_NAME,
1819 'OTHERS',
1820 l_msg_count,
1821 l_msg_data,
1822 G_LEVEL);
1823 end k_approved;
1824
1825 -- Start of comments
1826 --
1827 -- Procedure Name : k_erase_approved
1828 -- Description :
1829 -- Business Rules :
1830 -- Parameters :
1831 -- Version : 1.0
1832 -- End of comments
1833
1834 procedure k_erase_approved(
1835 p_contract_id IN number,
1836 x_return_status OUT NOCOPY VARCHAR2
1837 ) is
1838 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1839 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1840 dummy varchar2(1) := '?';
1841 cursor c1 is
1842 select '!'
1843 from okc_k_headers_all_b
1844 where ID = p_contract_id and date_approved is not null;
1845 --
1846 cursor lock_csr(p number) is
1847 select object_version_number
1848 from okc_k_headers_all_b
1849 where ID = p
1850 ;
1851 l_api_name CONSTANT VARCHAR2(30) := 'k_erase_approved';
1852 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1853 l_msg_count NUMBER;
1854 l_msg_data varchar2(2000);
1855 begin
1856
1857 --check if do anything
1858 open c1;
1859 fetch c1 into dummy;
1860 close c1;
1861 if (dummy = '?') then
1862 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1863 return;
1864 end if;
1865
1866 --start
1867 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1868 G_PKG_NAME,
1869 OKC_API.G_TRUE,
1870 l_api_version,
1871 l_api_version,
1872 G_LEVEL,
1873 x_return_status);
1874 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1875 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1876 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1877 RAISE OKC_API.G_EXCEPTION_ERROR;
1878 END IF;
1879
1880 --lock
1881 L1_header_rec.id := p_contract_id;
1882 open lock_csr(p_contract_id);
1883 fetch lock_csr into L1_header_rec.object_version_number;
1884 close lock_csr;
1885 OKC_CONTRACT_PUB.lock_contract_header(
1886 p_api_version => l_api_version,
1887 x_return_status => x_return_status,
1888 x_msg_count => l_msg_count,
1889 x_msg_data => l_msg_data,
1890 p_chrv_rec => L1_header_rec);
1891 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1892 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1893 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1894 RAISE OKC_API.G_EXCEPTION_ERROR;
1895 END IF;
1896
1897 --update
1898 L1_header_rec.date_approved := NULL;
1899 OKC_CONTRACT_PUB.update_contract_header(
1900 p_api_version => l_api_version,
1901 x_return_status => x_return_status,
1902 x_msg_count => l_msg_count,
1903 x_msg_data => l_msg_data,
1904 p_restricted_update => OKC_API.G_TRUE,
1905 p_chrv_rec => L1_header_rec,
1906 x_chrv_rec => L2_header_rec);
1907 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1909 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1910 RAISE OKC_API.G_EXCEPTION_ERROR;
1911 END IF;
1912
1913 --end
1914 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
1915 EXCEPTION
1916 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1917 db_failed('OKC_REVOKE');
1918 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1919 (substr(l_api_name,1,26),
1920 G_PKG_NAME,
1921 'OKC_API.G_RET_STS_ERROR',
1922 l_msg_count,
1923 l_msg_data,
1924 G_LEVEL);
1925 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1926 db_failed('OKC_REVOKE');
1927 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1928 (substr(l_api_name,1,26),
1929 G_PKG_NAME,
1930 'OKC_API.G_RET_STS_UNEXP_ERROR',
1931 l_msg_count,
1932 l_msg_data,
1933 G_LEVEL);
1934 WHEN OTHERS THEN
1935 db_failed('OKC_REVOKE');
1936 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1937 (substr(l_api_name,1,26),
1938 G_PKG_NAME,
1939 'OTHERS',
1940 l_msg_count,
1941 l_msg_data,
1942 G_LEVEL);
1943 end k_erase_approved;
1944
1945 -- Start of comments
1946 --
1947 -- Procedure Name : k_signed
1948 -- Description :
1949 -- Business Rules :
1950 -- Parameters :
1951 -- Version : 1.0
1952 -- End of comments
1953
1954 procedure k_signed(
1955 p_contract_id IN number,
1956 p_date_signed IN date default sysdate,
1957 p_complete_k_prcs IN VARCHAR2 default 'Y',
1958 x_return_status OUT NOCOPY VARCHAR2
1959 ) is
1960 l_api_name CONSTANT VARCHAR2(30) := 'k_signed';
1961 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1962 l_msg_count NUMBER;
1963 l_msg_data varchar2(2000);
1964
1965 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1966 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1967 --Bug:3675868 fetch sts_code to set L1_header_rec.old_sts_code
1968 cursor lock_csr(p number) is
1969 select object_version_number, START_DATE, END_DATE, sts_code, scs_code
1970 from okc_k_headers_all_b
1971 where ID = p
1972 ;
1973 --
1974 l_new_status varchar2(30);
1975 l_signed_status varchar2(30);
1976 l_active_status varchar2(30);
1977 l_expired_status varchar2(30);
1978 l_scs_code VARCHAR2(30);
1979 l_sysdate DATE;
1980
1981 cursor c1 is
1982 select code from okc_statuses_b
1983 where ste_code='SIGNED'
1984 and default_yn='Y';
1985 cursor c2 is
1986 select code from okc_statuses_b
1987 where ste_code='ACTIVE'
1988 and default_yn='Y';
1989 cursor c3 is
1990 select code from okc_statuses_b
1991 where ste_code='EXPIRED'
1992 and default_yn='Y';
1993 --
1994 cursor lock1_csr is
1995 select L.ID ID, L.object_version_number
1996 ,decode(sign(months_between(sysdate-1, NVL(L.end_date,sysdate))),-1,
1997 decode(sign(months_between(p_date_signed-1,sysdate)),-1,
1998 decode(sign(months_between(L.start_date-1,sysdate)),-1,
1999 l_active_status,l_signed_status),l_signed_status),l_expired_status) STS_CODE
2000 from okc_k_lines_b L
2001 , okc_statuses_b S
2002 where L.dnz_chr_id = p_contract_id
2003 and S.code = L.sts_code
2004 and S.ste_code='ENTERED'
2005 ;
2006 --
2007 loc1_rec lock1_csr%ROWTYPE;
2008 i number :=0;
2009 --
2010 l1_lines okc_contract_pub.clev_tbl_type;
2011 l2_lines okc_contract_pub.clev_tbl_type;
2012 l3_lines okc_contract_pub.clev_tbl_type;
2013 --
2014 call_time varchar2(1);
2015
2016 begin
2017 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2018 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2019 'Entered '||G_PKG_NAME ||'.'||l_api_name);
2020 END IF;
2021 --start
2022 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
2023 G_PKG_NAME,
2024 OKC_API.G_TRUE,
2025 l_api_version,
2026 l_api_version,
2027 G_LEVEL,
2028 x_return_status);
2029 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2032 RAISE OKC_API.G_EXCEPTION_ERROR;
2033 END IF;
2034 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2035 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2036 'OKC_API.START_ACTIVITY return status '||l_return_status);
2037 END IF;
2038
2039 --npalepu added on 02-DEC-2005 for bug # 4775848 to set the context.
2040 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(p_chr_id => p_contract_id) ;
2041 --end npalepu
2042
2043 --lock header
2044 L1_header_rec.id := p_contract_id;
2045 open lock_csr(p_contract_id);
2046 --Bug:3675868 fetch sts_code into L1_header_rec.old_sts_code
2047 fetch lock_csr into
2048 L1_header_rec.object_version_number,L1_header_rec.START_DATE,L1_header_rec.END_DATE,L1_header_rec.old_sts_code,l_scs_code;
2049 close lock_csr;
2050 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2051 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2052 ' OKC_CONTRACT_PUB.lock_contract_header');
2053 END IF;
2054 OKC_CONTRACT_PUB.lock_contract_header(
2055 p_api_version => l_api_version,
2056 x_return_status => x_return_status,
2057 x_msg_count => l_msg_count,
2058 x_msg_data => l_msg_data,
2059 p_chrv_rec => L1_header_rec);
2060 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2061 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2062 ' OKC_CONTRACT_PUB.lock_contract_header(x_return_status=>'||x_return_status||')');
2063 END IF;
2064 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2067 RAISE OKC_API.G_EXCEPTION_ERROR;
2068 END IF;
2069
2070 --update header
2071 L1_header_rec.date_signed := p_date_signed;
2072 --
2073 open c1;
2074 fetch c1 into l_signed_status;
2075 close c1;
2076 --
2077 open c2;
2078 fetch c2 into l_active_status;
2079 close c2;
2080 --
2081 open c3;
2082 fetch c3 into l_expired_status;
2083 close c3;
2084 --
2085 l_new_status := l_signed_status; call_time := 'Y';
2086 if (L1_header_rec.date_signed <= sysdate
2087 and L1_header_rec.START_DATE <= sysdate
2088 and (L1_header_rec.END_DATE is NULL or sysdate<=L1_header_rec.END_DATE+1)) then
2089 l_new_status := l_active_status; call_time := 'Y';
2090 end if;
2091 if (sysdate>L1_header_rec.END_DATE+1) then
2092 l_new_status := l_expired_status; call_time := 'N';
2093 end if;
2094
2095 --Bug:3675868 set L1_header_rec.old_ste_code
2096 select ste_code
2097 into L1_header_rec.old_ste_code
2098 from okc_statuses_b
2099 where code = L1_header_rec.old_sts_code;
2100
2101 --Bug:3675868 set L1_header_rec.new_ste_code
2102 select ste_code
2103 into L1_header_rec.new_ste_code
2104 from okc_statuses_b
2105 where code = l_new_status;
2106
2107 --Bug:3675868 set L1_header_rec.new_sts_code
2108 L1_header_rec.new_sts_code := l_new_status;
2109
2110 L1_header_rec.STS_CODE := l_new_status;
2111
2112 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2113 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2114 'OKC_CONTRACT_PUB.update_contract_header');
2115 END IF;
2116 OKC_CONTRACT_PUB.update_contract_header(
2117 p_api_version => l_api_version,
2118 x_return_status => x_return_status,
2119 x_msg_count => l_msg_count,
2120 x_msg_data => l_msg_data,
2121 p_restricted_update => OKC_API.G_TRUE,
2122 p_chrv_rec => L1_header_rec,
2123 x_chrv_rec => L2_header_rec);
2124 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2125 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2126 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2127 RAISE OKC_API.G_EXCEPTION_ERROR;
2128 END IF;
2129 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2130 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2131 'OKC_CONTRACT_PUB.update_contract_header(x_return_status=>'||x_return_status||')');
2132 END IF;
2133
2134
2135
2136 --Bug 5442886, for service contracts use different logic for performance reasons
2137 IF (l_scs_code IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION')) THEN
2138
2139 --for service contracts directly update the status of all lines in the contract
2140 --using the exact same logic to derive sts_code
2141
2142 --Note: When we call OKC_CONTRACT_PUB.update_contract_line, there is some logic
2143 --inside it to call OKC_KL_STS_CHG_ASMBLR_PVT.Acn_Assemble, action assembler
2144 --for line status change. However, currently this call is not made because
2145 --we never pass values for old/new sts/ste_code.
2146
2147 --Bug 3675868, fixes this issue for the header, but there is no corresponding
2148 --fix for the lines, meaning customers have been signing contracts without the
2149 --line level status change action assembler being called. So, we are leaving the
2150 --call to this action assembler for the time being. If ever we do need to make a call
2151 --we should anyway check for OKC_K_SIGN_ASMBLR_PVT.isActionEnabled as per bug 4033775
2152 --before making that call.
2153
2154 l_sysdate := SYSDATE;
2155
2156 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2157 fnd_log.string(FND_LOG.LEVEL_EVENT, G_MODULE || l_api_name || '.direct_line_status_change',
2158 'updating line status');
2159 END IF;
2160 /*modified for bug8530862*/
2161 /*bug12370530 -- ATG12402762, As part of 12.2 techstack changes given in Database
2162 Object Development Standards, a new column zd_edition_name was added to OKC seed data tables
2163 and this new column is part of the existing Unique indexes of those tables.
2164 After these changes following UPDATE statement was failing because of ORA-01779 error and ATG
2165 team has suggested to modify the product code in order to handle the error through bug12402762*/
2166
2167 /*commented the below UPDATE statement and modified as per ATG suggestion
2168 UPDATE (SELECT l.sts_code, l.start_date, l.end_date, l.last_update_date, l.last_update_login, l.Last_updated_by
2169 FROM okc_k_lines_b l, okc_statuses_b s
2170 WHERE l.dnz_chr_id = p_contract_id
2171 AND s.code = l.sts_code
2172 AND s.ste_code = 'ENTERED') oks
2173 SET
2174 oks.sts_code =
2175 decode( sign(months_between(l_sysdate - 1, NVL(oks.end_date, l_sysdate))),
2176 -1, decode(sign(months_between(p_date_signed - 1, l_sysdate)),
2177 -1, decode(sign(months_between(oks.start_date - 1, l_sysdate)),
2178 -1, l_active_status, l_signed_status), l_signed_status), l_expired_status),
2179 oks.last_update_date = l_sysdate,
2180 oks.last_update_login = FND_GLOBAL.LOGIN_ID,
2181 oks.last_updated_by = FND_GLOBAL.USER_ID;
2182 */
2183 UPDATE (SELECT l.sts_code, l.start_date, l.end_date, l.last_update_date, l.last_update_login, l.Last_updated_by
2184 FROM okc_k_lines_b l
2185 WHERE l.dnz_chr_id = p_contract_id) oks
2186 SET
2187 oks.sts_code =
2188 decode( sign(months_between(l_sysdate - 1, NVL(oks.end_date, l_sysdate))),
2189 -1, decode(sign(months_between(p_date_signed - 1, l_sysdate)),
2190 -1, decode(sign(months_between(oks.start_date - 1, l_sysdate)),
2191 -1, l_active_status, l_signed_status), l_signed_status), l_expired_status),
2192 oks.last_update_date = l_sysdate,
2193 oks.last_update_login = FND_GLOBAL.LOGIN_ID,
2194 oks.last_updated_by = FND_GLOBAL.USER_ID
2195 WHERE oks.sts_code IN (SELECT code
2196 FROM okc_statuses_b
2197 WHERE ste_code = 'ENTERED');
2198
2199 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2200 fnd_log.string(FND_LOG.LEVEL_EVENT, G_MODULE || l_api_name || '.direct_line_status_change',
2201 'done line status update');
2202 END IF;
2203
2204
2205 ELSE
2206
2207 --for all other contracts use existing logic
2208
2209 -- lock lines
2210 for lock1_rec in lock1_csr LOOP
2211 i := i+1;
2212 l1_lines(i).id := lock1_rec.id;
2213 l1_lines(i).object_version_number := lock1_rec.object_version_number;
2214 --
2215 l2_lines(i).id := lock1_rec.id;
2216 l2_lines(i).object_version_number := lock1_rec.object_version_number;
2217 l2_lines(i).sts_code := lock1_rec.sts_code;
2218 end LOOP;
2219 OKC_CONTRACT_PUB.lock_contract_line(
2220 p_api_version => l_api_version,
2221 x_return_status => x_return_status,
2222 x_msg_count => l_msg_count,
2223 x_msg_data => l_msg_data,
2224 p_clev_tbl => l1_lines);
2225 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2226 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2227 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2228 RAISE OKC_API.G_EXCEPTION_ERROR;
2229 END IF;
2230
2231 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2232 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2233 'OKC_CONTRACT_PUB.update_contract_line');
2234 END IF;
2235 -- update lines
2236 OKC_CONTRACT_PUB.update_contract_line(
2237 p_api_version => l_api_version,
2238 x_return_status => x_return_status,
2239 x_msg_count => l_msg_count,
2240 x_msg_data => l_msg_data,
2241 p_restricted_update => OKC_API.G_TRUE,
2242 p_clev_tbl => l2_lines,
2243 x_clev_tbl => l3_lines);
2244 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2245 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2246 'OKC_CONTRACT_PUB.update_contract_line(x_return_status=>'||x_return_status||')');
2247 END IF;
2248 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2249 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2250 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2251 RAISE OKC_API.G_EXCEPTION_ERROR;
2252 END IF;
2253
2254 END IF; --of IF (L1_header_rec.scs_code IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION')) THEN
2255
2256
2257
2258 /* Commented for bug 5069035. Not used in R12 and commenting out to avoid performance overhead.
2259 -- call time ...
2260 if (call_time = 'Y') then
2261 OKC_TIME_RES_PUB.Res_Time_New_K(L2_header_rec.id, l_api_version,OKC_API.G_FALSE,x_return_status);
2262 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2263 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2264 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2265 RAISE OKC_API.G_EXCEPTION_ERROR;
2266 END IF;
2267 end if;
2268 */
2269
2270 -- Call to Price Hold API to create QP entries If there are any Price Hold Information on contract
2271
2272 IF l_new_status = l_active_status OR
2273 l_new_status = l_signed_status THEN
2274
2275
2276 --Bug 5442886, for service contracts no need to process price holds
2277 IF (l_scs_code IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION')) THEN
2278
2279 --do nothing
2280 NULL;
2281 ELSE
2282
2283 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2284 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2285 'OKC_PHI_PVT.process_price_hold(p_chr_id=>'||p_contract_id||')');
2286 END IF;
2287
2288 OKC_PHI_PVT.process_price_hold(p_api_version => l_api_version
2289 ,p_init_msg_list => OKC_API.G_FALSE
2290 ,p_chr_id => p_contract_id
2291 ,p_operation_code => 'UPDATE'
2292 ,p_termination_date => Null
2293 ,x_return_status => x_return_status
2294 ,x_msg_count => l_msg_count
2295 ,x_msg_data => l_msg_data);
2296 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2297 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2298 'OKC_PHI_PVT.process_price_hold(x_return_status=>'||x_return_status||')');
2299 END IF;
2300
2301 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2302 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2303 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2304 RAISE OKC_API.G_EXCEPTION_ERROR;
2305 END IF;
2306 END IF;
2307
2308 END IF;
2309
2310 -- End ** Call to Price Hold API to create QP entries If there are any Price Hold Information on contract
2311
2312 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2313 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2314 'OKC_K_SIGN_ASMBLR_PVT.acn_assemble(p_contract_id=>'||x_return_status||')');
2315 END IF;
2316 -- raise event
2317 OKC_K_SIGN_ASMBLR_PVT.acn_assemble(
2318 p_api_version => l_api_version,
2319 x_return_status => x_return_status,
2320 x_msg_count => l_msg_count,
2321 x_msg_data => l_msg_data,
2322 p_contract_id => p_contract_id);
2323 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2324 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2325 'OKC_K_SIGN_ASMBLR_PVT.acn_assemble(x_return_status=>'||x_return_status||')');
2326 END IF;
2327 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2328 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2329 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2330 RAISE OKC_API.G_EXCEPTION_ERROR;
2331 END IF;
2332
2333 --end
2334
2335 IF l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') AND p_complete_k_prcs = 'Y' THEN
2336 -- Complete the Service Contracts Process workflow if applicable
2337 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2338 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2339 'continue_k_process(p_contract_id=>'||p_contract_id||')');
2340 END IF;
2341 continue_k_process
2342 (
2343 p_api_version => l_api_version,
2344 p_init_msg_list => OKC_API.G_FALSE,
2345 x_return_status => x_return_status,
2346 x_msg_count => l_msg_count,
2347 x_msg_data => l_msg_data,
2348 p_contract_id => p_contract_id,
2349 p_wf_item_key => NULL,
2350 p_called_from => 'APPROVE'
2351 );
2352 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2353 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2354 'continue_k_process(x_return_status=>'||x_return_status||')');
2355 END IF;
2356 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2358 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2359 RAISE OKC_API.G_EXCEPTION_ERROR;
2360 END IF;
2361 END IF;
2362
2363 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
2364 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2365 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,G_MODULE||l_api_name,
2366 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
2367 END IF;
2368 EXCEPTION
2369 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2370 db_failed('OKC_SIGN');
2371 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2372 (substr(l_api_name,1,26),
2373 G_PKG_NAME,
2374 'OKC_API.G_RET_STS_ERROR',
2375 l_msg_count,
2376 l_msg_data,
2377 G_LEVEL);
2378 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2379 db_failed('OKC_SIGN');
2380 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2381 (substr(l_api_name,1,26),
2382 G_PKG_NAME,
2383 'OKC_API.G_RET_STS_UNEXP_ERROR',
2384 l_msg_count,
2385 l_msg_data,
2386 G_LEVEL);
2387 WHEN OTHERS THEN
2388 db_failed('OKC_SIGN');
2389 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2390 (substr(l_api_name,1,26),
2391 G_PKG_NAME,
2392 'OTHERS',
2393 l_msg_count,
2394 l_msg_data,
2395 G_LEVEL);
2396 end k_signed;
2397
2398 -- Procedure Name : Activate_Template
2399 -- Description :
2400 -- Business Rules :
2401 -- Parameters :
2402 -- Version : 1.0
2403 -- End of comments
2404
2405 procedure activate_template (
2406 p_contract_id IN number,
2407 x_return_status OUT NOCOPY VARCHAR2 ) is
2408 --
2409 l_api_name CONSTANT VARCHAR2(30) := 'activate_template';
2410 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2411 l_msg_count NUMBER;
2412 l_msg_data varchar2(2000);
2413 --
2414 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
2415 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
2416
2417 cursor lock_csr is
2418 select object_version_number
2419 from okc_k_headers_all_b
2420 where ID = p_contract_id;
2421 --
2422 l_active_status varchar2(30) := 'ACTIVE';
2423
2424 begin
2425
2426 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
2427 G_PKG_NAME,
2428 OKC_API.G_TRUE,
2429 l_api_version,
2430 l_api_version,
2431 G_LEVEL,
2432 x_return_status);
2433
2434 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2435 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2436 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2437 RAISE OKC_API.G_EXCEPTION_ERROR;
2438 END IF;
2439
2440 --lock header
2441 L1_header_rec.id := p_contract_id;
2442 open lock_csr;
2443 fetch lock_csr into
2444 L1_header_rec.object_version_number;
2445 close lock_csr;
2446 OKC_CONTRACT_PUB.lock_contract_header(
2447 p_api_version => l_api_version,
2448 x_return_status => x_return_status,
2449 x_msg_count => l_msg_count,
2450 x_msg_data => l_msg_data,
2451 p_chrv_rec => L1_header_rec);
2452 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2453 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2454 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2455 RAISE OKC_API.G_EXCEPTION_ERROR;
2456 END IF;
2457
2458
2459
2460 L1_header_rec.STS_CODE := l_active_status;
2461
2462 OKC_CONTRACT_PUB.update_contract_header(
2463 p_api_version => l_api_version,
2464 x_return_status => x_return_status,
2465 x_msg_count => l_msg_count,
2466 x_msg_data => l_msg_data,
2467 p_restricted_update => OKC_API.G_TRUE,
2468 p_chrv_rec => L1_header_rec,
2469 x_chrv_rec => L2_header_rec);
2470
2471 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2472 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2473 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2474 RAISE OKC_API.G_EXCEPTION_ERROR;
2475 END IF;
2476
2477 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
2478
2479 EXCEPTION
2480 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2481 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2482 (substr(l_api_name,1,26),
2483 G_PKG_NAME,
2484 'OKC_API.G_RET_STS_ERROR',
2485 l_msg_count,
2486 l_msg_data,
2487 G_LEVEL);
2488 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2489 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2490 (substr(l_api_name,1,26),
2491 G_PKG_NAME,
2492 'OKC_API.G_RET_STS_UNEXP_ERROR',
2493 l_msg_count,
2494 l_msg_data,
2495 G_LEVEL);
2496 WHEN OTHERS THEN
2497 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2498 (substr(l_api_name,1,26),
2499 G_PKG_NAME,
2500 'OTHERS',
2501 l_msg_count,
2502 l_msg_data,
2503 G_LEVEL);
2504
2505 end activate_template;
2506
2507 end OKC_CONTRACT_APPROVAL_PVT;