[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_APPROVAL_PVT
Source
1 package body OKC_CONTRACT_APPROVAL_PVT as
2 /* $Header: OKCRCAPB.pls 120.18.12000000.2 2007/04/05 22:19:06 skkoppul 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
2161 UPDATE (SELECT l.sts_code, l.start_date, l.end_date
2162 FROM okc_k_lines_b l, okc_statuses_b s
2163 WHERE l.dnz_chr_id = p_contract_id
2164 AND s.code = l.sts_code
2165 AND s.ste_code = 'ENTERED') oks
2166 SET
2167 oks.sts_code =
2168 decode( sign(months_between(l_sysdate - 1, NVL(oks.end_date, l_sysdate))),
2169 -1, decode(sign(months_between(p_date_signed - 1, l_sysdate)),
2170 -1, decode(sign(months_between(oks.start_date - 1, l_sysdate)),
2171 -1, l_active_status, l_signed_status), l_signed_status), l_expired_status);
2172
2173 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2174 fnd_log.string(FND_LOG.LEVEL_EVENT, G_MODULE || l_api_name || '.direct_line_status_change',
2175 'done line status update');
2176 END IF;
2177
2178
2179 ELSE
2180
2181 --for all other contracts use existing logic
2182
2183 -- lock lines
2184 for lock1_rec in lock1_csr LOOP
2185 i := i+1;
2186 l1_lines(i).id := lock1_rec.id;
2187 l1_lines(i).object_version_number := lock1_rec.object_version_number;
2188 --
2189 l2_lines(i).id := lock1_rec.id;
2190 l2_lines(i).object_version_number := lock1_rec.object_version_number;
2191 l2_lines(i).sts_code := lock1_rec.sts_code;
2192 end LOOP;
2193 OKC_CONTRACT_PUB.lock_contract_line(
2194 p_api_version => l_api_version,
2195 x_return_status => x_return_status,
2196 x_msg_count => l_msg_count,
2197 x_msg_data => l_msg_data,
2198 p_clev_tbl => l1_lines);
2199 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2200 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2201 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2202 RAISE OKC_API.G_EXCEPTION_ERROR;
2203 END IF;
2204
2205 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2206 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2207 'OKC_CONTRACT_PUB.update_contract_line');
2208 END IF;
2209 -- update lines
2210 OKC_CONTRACT_PUB.update_contract_line(
2211 p_api_version => l_api_version,
2212 x_return_status => x_return_status,
2213 x_msg_count => l_msg_count,
2214 x_msg_data => l_msg_data,
2215 p_restricted_update => OKC_API.G_TRUE,
2216 p_clev_tbl => l2_lines,
2217 x_clev_tbl => l3_lines);
2218 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2219 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2220 'OKC_CONTRACT_PUB.update_contract_line(x_return_status=>'||x_return_status||')');
2221 END IF;
2222 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2223 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2224 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2225 RAISE OKC_API.G_EXCEPTION_ERROR;
2226 END IF;
2227
2228 END IF; --of IF (L1_header_rec.scs_code IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION')) THEN
2229
2230
2231
2232 /* Commented for bug 5069035. Not used in R12 and commenting out to avoid performance overhead.
2233 -- call time ...
2234 if (call_time = 'Y') then
2235 OKC_TIME_RES_PUB.Res_Time_New_K(L2_header_rec.id, l_api_version,OKC_API.G_FALSE,x_return_status);
2236 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2237 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2238 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2239 RAISE OKC_API.G_EXCEPTION_ERROR;
2240 END IF;
2241 end if;
2242 */
2243
2244 -- Call to Price Hold API to create QP entries If there are any Price Hold Information on contract
2245
2246 IF l_new_status = l_active_status OR
2247 l_new_status = l_signed_status THEN
2248
2249
2250 --Bug 5442886, for service contracts no need to process price holds
2251 IF (l_scs_code IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION')) THEN
2252
2253 --do nothing
2254 NULL;
2255 ELSE
2256
2257 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2258 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2259 'OKC_PHI_PVT.process_price_hold(p_chr_id=>'||p_contract_id||')');
2260 END IF;
2261
2262 OKC_PHI_PVT.process_price_hold(p_api_version => l_api_version
2263 ,p_init_msg_list => OKC_API.G_FALSE
2264 ,p_chr_id => p_contract_id
2265 ,p_operation_code => 'UPDATE'
2266 ,p_termination_date => Null
2267 ,x_return_status => x_return_status
2268 ,x_msg_count => l_msg_count
2269 ,x_msg_data => l_msg_data);
2270 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2271 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2272 'OKC_PHI_PVT.process_price_hold(x_return_status=>'||x_return_status||')');
2273 END IF;
2274
2275 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2276 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2277 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2278 RAISE OKC_API.G_EXCEPTION_ERROR;
2279 END IF;
2280 END IF;
2281
2282 END IF;
2283
2284 -- End ** Call to Price Hold API to create QP entries If there are any Price Hold Information on contract
2285
2286 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2287 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2288 'OKC_K_SIGN_ASMBLR_PVT.acn_assemble(p_contract_id=>'||x_return_status||')');
2289 END IF;
2290 -- raise event
2291 OKC_K_SIGN_ASMBLR_PVT.acn_assemble(
2292 p_api_version => l_api_version,
2293 x_return_status => x_return_status,
2294 x_msg_count => l_msg_count,
2295 x_msg_data => l_msg_data,
2296 p_contract_id => p_contract_id);
2297 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2298 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2299 'OKC_K_SIGN_ASMBLR_PVT.acn_assemble(x_return_status=>'||x_return_status||')');
2300 END IF;
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
2307 --end
2308
2309 IF l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') AND p_complete_k_prcs = 'Y' THEN
2310 -- Complete the Service Contracts Process workflow if applicable
2311 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2312 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2313 'continue_k_process(p_contract_id=>'||p_contract_id||')');
2314 END IF;
2315 continue_k_process
2316 (
2317 p_api_version => l_api_version,
2318 p_init_msg_list => OKC_API.G_FALSE,
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 p_wf_item_key => NULL,
2324 p_called_from => 'APPROVE'
2325 );
2326 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2327 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2328 'continue_k_process(x_return_status=>'||x_return_status||')');
2329 END IF;
2330 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2331 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2332 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2333 RAISE OKC_API.G_EXCEPTION_ERROR;
2334 END IF;
2335 END IF;
2336
2337 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
2338 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2339 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,G_MODULE||l_api_name,
2340 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
2341 END IF;
2342 EXCEPTION
2343 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2344 db_failed('OKC_SIGN');
2345 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2346 (substr(l_api_name,1,26),
2347 G_PKG_NAME,
2348 'OKC_API.G_RET_STS_ERROR',
2349 l_msg_count,
2350 l_msg_data,
2351 G_LEVEL);
2352 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2353 db_failed('OKC_SIGN');
2354 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2355 (substr(l_api_name,1,26),
2356 G_PKG_NAME,
2357 'OKC_API.G_RET_STS_UNEXP_ERROR',
2358 l_msg_count,
2359 l_msg_data,
2360 G_LEVEL);
2361 WHEN OTHERS THEN
2362 db_failed('OKC_SIGN');
2363 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2364 (substr(l_api_name,1,26),
2365 G_PKG_NAME,
2366 'OTHERS',
2367 l_msg_count,
2368 l_msg_data,
2369 G_LEVEL);
2370 end k_signed;
2371
2372 -- Procedure Name : Activate_Template
2373 -- Description :
2374 -- Business Rules :
2375 -- Parameters :
2376 -- Version : 1.0
2377 -- End of comments
2378
2379 procedure activate_template (
2380 p_contract_id IN number,
2381 x_return_status OUT NOCOPY VARCHAR2 ) is
2382 --
2383 l_api_name CONSTANT VARCHAR2(30) := 'activate_template';
2384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2385 l_msg_count NUMBER;
2386 l_msg_data varchar2(2000);
2387 --
2388 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
2389 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
2390
2391 cursor lock_csr is
2392 select object_version_number
2393 from okc_k_headers_all_b
2394 where ID = p_contract_id;
2395 --
2396 l_active_status varchar2(30) := 'ACTIVE';
2397
2398 begin
2399
2400 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
2401 G_PKG_NAME,
2402 OKC_API.G_TRUE,
2403 l_api_version,
2404 l_api_version,
2405 G_LEVEL,
2406 x_return_status);
2407
2408 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2409 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2410 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2411 RAISE OKC_API.G_EXCEPTION_ERROR;
2412 END IF;
2413
2414 --lock header
2415 L1_header_rec.id := p_contract_id;
2416 open lock_csr;
2417 fetch lock_csr into
2418 L1_header_rec.object_version_number;
2419 close lock_csr;
2420 OKC_CONTRACT_PUB.lock_contract_header(
2421 p_api_version => l_api_version,
2422 x_return_status => x_return_status,
2423 x_msg_count => l_msg_count,
2424 x_msg_data => l_msg_data,
2425 p_chrv_rec => L1_header_rec);
2426 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2427 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2428 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2429 RAISE OKC_API.G_EXCEPTION_ERROR;
2430 END IF;
2431
2432
2433
2434 L1_header_rec.STS_CODE := l_active_status;
2435
2436 OKC_CONTRACT_PUB.update_contract_header(
2437 p_api_version => l_api_version,
2438 x_return_status => x_return_status,
2439 x_msg_count => l_msg_count,
2440 x_msg_data => l_msg_data,
2441 p_restricted_update => OKC_API.G_TRUE,
2442 p_chrv_rec => L1_header_rec,
2443 x_chrv_rec => L2_header_rec);
2444
2445 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2446 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2447 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2448 RAISE OKC_API.G_EXCEPTION_ERROR;
2449 END IF;
2450
2451 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
2452
2453 EXCEPTION
2454 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2455 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2456 (substr(l_api_name,1,26),
2457 G_PKG_NAME,
2458 'OKC_API.G_RET_STS_ERROR',
2459 l_msg_count,
2460 l_msg_data,
2461 G_LEVEL);
2462 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2463 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2464 (substr(l_api_name,1,26),
2465 G_PKG_NAME,
2466 'OKC_API.G_RET_STS_UNEXP_ERROR',
2467 l_msg_count,
2468 l_msg_data,
2469 G_LEVEL);
2470 WHEN OTHERS THEN
2471 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2472 (substr(l_api_name,1,26),
2473 G_PKG_NAME,
2474 'OTHERS',
2475 l_msg_count,
2476 l_msg_data,
2477 G_LEVEL);
2478
2479 end activate_template;
2480
2481 end OKC_CONTRACT_APPROVAL_PVT;