[Home] [Help]
PACKAGE BODY: APPS.OKE_CONTRACT_APPROVAL_PVT
Source
1 package body OKE_CONTRACT_APPROVAL_PVT as
2 /* $Header: OKEVCAPB.pls 115.3 2002/12/02 21:05:13 alaw ship $ */
3 --
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKE_CONTRACT_APPROVAL_PVT';
6 G_LEVEL CONSTANT VARCHAR2(4) := '_PVT';
7 l_api_version CONSTANT NUMBER := 1;
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
10 --
11 G_K_WAS_APPROVED CONSTANT varchar2(200) := 'OKC_ALREADY_APPROVED';
12 --
13 G_PROCESS_NOT_FOR_APPROVAL CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_FOR_APPROVAL';
14 G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
15 G_WF_P_NAME_TOKEN CONSTANT varchar2(200) := 'WF_PROCESS';
16 --
17 G_PROCESS_NOTFOUND CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_FOUND';
18 --
19 G_K_ON_APPROVAL CONSTANT varchar2(200) := 'OKC_IS_ON_APPROVAL';
20 -- G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
21 G_KEY_TOKEN CONSTANT varchar2(200) := 'WF_KEY';
22 --
23 G_WF_NOT_PURGED CONSTANT varchar2(200) := 'OKC_WF_NOT_PURGED';
24 -- G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
25 -- G_KEY_TOKEN CONSTANT varchar2(200) := 'WF_KEY';
26 --
27 G_K_NOT_ON_APPROVAL CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_ACTIVE';
28 --
29 G_NO_U_PRIVILEGE CONSTANT varchar2(200) := 'OKC_NO_RIGHT_TO_CHANGE';
30
31 -- Start of comments
32 --
33 -- Procedure Name : k_approval_start
34 -- Description :
35 -- Business Rules :
36 -- Parameters :
37 -- Version : 1.0
38 -- End of comments
39
40 procedure k_approval_start(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2 default OKE_API.G_FALSE,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2,
46 p_contract_id IN number,
47 p_process_id IN number,
48 p_do_commit IN VARCHAR2 default OKE_API.G_TRUE
49 ) is
50 l_api_name CONSTANT VARCHAR2(30) := 'k_approval_start';
51 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
52 --
53 l_key varchar2(240);
54 l_contract_number varchar2(120);
55 l_contract_number_modifier varchar2(120);
56 l_date_approved date;
57 --
58 l_wf_name_active varchar2(150);
59 l_wf_name varchar2(150);
60 l_wf_process_name varchar2(150);
61 l_usage varchar2(60);
62 --
63 l_q varchar2(1);
64 --
65 L_PAR_NAME VARCHAR2(150);
66 L_PAR_TYPE VARCHAR2(90);
67 L_PAR_VALUE VARCHAR2(2000);
68 --
69 L_NLS_VALUE VARCHAR2(30);
70 --
71 L1_CPSV_REC OKC_CONTRACT_PUB.cpsv_rec_type;
72 L2_CPSV_REC OKC_CONTRACT_PUB.cpsv_rec_type;
73 l_msg_count NUMBER;
74 l_msg_data varchar2(2000);
75 cursor k_pid is
76 select ID
77 from okc_k_processes_v
78 where CHR_ID = p_contract_id
79 and PDF_ID = p_process_id
80 --because of bug in lock API
81 for update of process_id nowait;
82 --
83 cursor k_header_csr is
84 select H.CONTRACT_NUMBER,
85 H.CONTRACT_NUMBER_MODIFIER,
86 H.DATE_APPROVED,
87 S.MEANING,
88 S.STE_CODE
89 from OKC_K_HEADERS_B H, okc_statuses_v S
90 where H.ID = p_contract_id
91 and H.STS_CODE=S.CODE;
92 l_status varchar2(100);
93 l_status_type varchar2(100);
94 --
95 cursor process_def_csr is
96 select WF_NAME, WF_PROCESS_NAME, USAGE
97 from OKC_PROCESS_DEFS_B
98 where ID = p_process_id
99 and begin_date<=sysdate
100 and (end_date is NULL or end_date>=sysdate) and PDF_TYPE='WPS';
101 --
102 cursor approval_active_csr is
103 select item_type
104 from WF_ITEMS
105 where item_type in
106 ( select wf_name
107 from OKC_PROCESS_DEFS_B
108 where USAGE='APPROVE' and PDF_TYPE='WPS')
109 and item_key = l_key
110 and end_date is NULL;
111
112 --
113 cursor for_purge_csr is
114 select '!'
115 from WF_ITEMS
116 where item_type = l_wf_name
117 and item_key = l_key;
118 --
119 cursor defined_parameters_csr is
120 select
121 NAME,
122 DATA_TYPE,
123 DEFAULT_VALUE
124 from OKC_PROCESS_DEF_PARAMETERS_V
125 where PDF_ID = p_process_id;
126 --
127 begin
128 l_return_status := OKE_API.START_ACTIVITY(substr(l_api_name,1,26),
129 G_PKG_NAME,
130 p_init_msg_list,
131 l_api_version,
132 p_api_version,
133 G_LEVEL,
134 x_return_status);
135 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
136 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
137 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
138 RAISE OKE_API.G_EXCEPTION_ERROR;
139 END IF;
140 --
141 /* if k_accesible( p_contract_id => p_contract_id,
142 p_user_id => fnd_global.user_id,
143 p_level => 'U'
144 ) = OKE_API.G_FALSE
145 then
146 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
147 p_msg_name => G_NO_U_PRIVILEGE);
148 raise OKE_API.G_EXCEPTION_ERROR;
149 end if; */
150 open k_header_csr;
151 fetch k_header_csr
152 into L_CONTRACT_NUMBER, L_CONTRACT_NUMBER_MODIFIER, L_DATE_APPROVED, L_STATUS, L_STATUS_TYPE;
153 close k_header_csr;
154 if (L_DATE_APPROVED is not NULL) then
155 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
156 p_msg_name => G_K_WAS_APPROVED);
157 raise OKE_API.G_EXCEPTION_ERROR;
158 end if;
159 if (L_STATUS_TYPE <> 'ENTERED') then
160 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
161 p_msg_name => 'OKC_INVALID_K_STATUS',
162 p_token1 => 'NUMBER',
163 p_token1_value => L_CONTRACT_NUMBER||'-'||L_CONTRACT_NUMBER_MODIFIER,
164 p_token2 => 'STATUS',
165 p_token2_value => L_STATUS);
166 raise OKC_API.G_EXCEPTION_ERROR;
167 end if;
168 --
169 open process_def_csr;
170 fetch process_def_csr into L_WF_NAME, L_WF_PROCESS_NAME, L_USAGE;
171 close process_def_csr;
172 if (L_WF_NAME is NULL) then
173 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
174 p_msg_name => G_PROCESS_NOTFOUND);
175 raise OKE_API.G_EXCEPTION_ERROR;
176 end if;
177 if (L_USAGE <> 'APPROVE') then
178 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
179 p_msg_name => G_PROCESS_NOT_FOR_APPROVAL,
180 p_token1 => G_WF_NAME_TOKEN,
181 p_token1_value => L_WF_NAME,
182 p_token2 => G_WF_P_NAME_TOKEN,
183 p_token2_value => L_WF_PROCESS_NAME);
184 raise OKE_API.G_EXCEPTION_ERROR;
185 end if;
186 --
187 L_KEY := L_CONTRACT_NUMBER||L_CONTRACT_NUMBER_MODIFIER;
188 open approval_active_csr;
189 fetch approval_active_csr into L_WF_NAME_ACTIVE;
190 close approval_active_csr;
191 if (L_WF_NAME_ACTIVE is not NULL) then
192 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
193 p_msg_name => G_K_ON_APPROVAL,
194 p_token1 => G_WF_NAME_TOKEN,
195 p_token1_value => L_WF_NAME_ACTIVE,
196 p_token2 => G_KEY_TOKEN,
197 p_token2_value => L_KEY);
198 raise OKE_API.G_EXCEPTION_ERROR;
199 end if;
200 --
201 L_Q:='?';
202 open for_purge_csr;
203 fetch for_purge_csr into L_Q;
204 close for_purge_csr;
205 if (L_Q = '!') then
206 begin
207 wf_purge.total(l_wf_name,l_key);
208 exception
209 when others then
210 begin
211 wf_purge.totalPerm(l_wf_name,l_key);
212 exception
213 when others then
214 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
215 p_msg_name => G_WF_NOT_PURGED,
216 p_token1 => G_WF_NAME_TOKEN,
217 p_token1_value => L_WF_NAME,
218 p_token2 => G_KEY_TOKEN,
219 p_token2_value => L_KEY);
220 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
221 end;
222 end;
223 end if;
224 --
225 /* OKC_CONTRACT_PUB.lock_contract_process(
226 p_api_version => l_api_version,
227 x_return_status => l_return_status,
228 x_msg_count => l_msg_count,
229 x_msg_data => l_msg_data,
230 p_cpsv_rec => L1_CPSV_REC);
231 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
232 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
233 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
234 RAISE OKE_API.G_EXCEPTION_ERROR;
235 END IF;
236 */
237 begin
238 savepoint BECAUSE_OF_BUG_IN_lock;
239 open k_pid;
240 fetch k_pid into L1_CPSV_REC.id;
241 close k_pid;
242 exception
243 when others then
244 rollback to BECAUSE_OF_BUG_IN_lock;
245 OKE_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
246 RAISE OKE_API.G_EXCEPTION_ERROR;
247 end;
248 L1_CPSV_REC.PROCESS_ID := L_KEY;
249 OKC_CONTRACT_PUB.update_contract_process(
250 p_api_version => l_api_version,
251 x_return_status => l_return_status,
252 x_msg_count => l_msg_count,
253 x_msg_data => l_msg_data,
254 p_cpsv_rec => L1_CPSV_REC,
255 x_cpsv_rec => L2_CPSV_REC);
256 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
257 rollback to BECAUSE_OF_BUG_IN_lock;
258 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
259 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
260 rollback to BECAUSE_OF_BUG_IN_lock;
261 RAISE OKE_API.G_EXCEPTION_ERROR;
262 END IF;
263 wf_engine.CreateProcess( ItemType => L_WF_NAME,
264 ItemKey => L_KEY,
265 process => L_WF_PROCESS_NAME);
266 wf_engine.SetItemUserKey (ItemType => L_WF_NAME,
267 ItemKey => L_KEY,
268 UserKey => L_KEY);
269 /* -- commented not to jeopardize wf by wrong data format,
270 -- instead use process_id attribute
271
272 open defined_parameters_csr;
273 LOOP
274 fetch defined_parameters_csr into
275 L_PAR_NAME,
276 L_PAR_TYPE,
277 L_PAR_VALUE;
278 exit when defined_parameters_csr%NOTFOUND;
279 if L_PAR_TYPE = 'C' then
280 begin
281 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
282 itemkey => L_KEY,
283 aname => L_PAR_NAME,
284 avalue => L_PAR_VALUE);
285 exception
286 when others then
287 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
288 itemkey => L_KEY,
289 aname => L_PAR_NAME);
290 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
291 itemkey => L_KEY,
292 aname => L_PAR_NAME,
293 avalue => L_PAR_VALUE);
294 end;
295 elsif L_PAR_TYPE = 'N' then
296 begin
297 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
298 itemkey => L_KEY,
299 aname => L_PAR_NAME,
300 avalue => to_number(L_PAR_VALUE));
301 exception
302 when others then
303 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
304 itemkey => L_KEY,
305 aname => L_PAR_NAME);
306 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
307 itemkey => L_KEY,
308 aname => L_PAR_NAME,
309 avalue => to_number(L_PAR_VALUE));
310 end;
311 elsif L_PAR_TYPE = 'D' then
312 begin
313 wf_engine.SetItemAttrDate (itemtype => L_WF_NAME,
314 itemkey => L_KEY,
315 aname => L_PAR_NAME,
316 avalue => fnd_date.chardate_to_date(L_PAR_VALUE));
317 exception
318 when others then
319 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
320 itemkey => L_KEY,
321 aname => L_PAR_NAME);
322 wf_engine.SetItemAttrDate (itemtype => L_WF_NAME,
323 itemkey => L_KEY,
324 aname => L_PAR_NAME,
325 avalue => fnd_date.chardate_to_date(L_PAR_VALUE));
326 end;
327 end if;
328 END LOOP;
329 */
330 -- replacement to previous commented
331 begin
332 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
333 itemkey => L_KEY,
334 aname => 'PROCESS_ID',
335 avalue => p_process_id);
336 exception
337 when others then
338 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
339 itemkey => L_KEY,
340 aname => 'PROCESS_ID');
341 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
342 itemkey => L_KEY,
343 aname => 'PROCESS_ID',
344 avalue => p_process_id);
345 end;
346 --
347 begin
348 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
349 itemkey => L_KEY,
350 aname => 'CONTRACT_ID',
351 avalue => p_contract_id);
352 exception
353 when others then
354 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
355 itemkey => L_KEY,
356 aname => 'CONTRACT_ID');
357 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
358 itemkey => L_KEY,
359 aname => 'CONTRACT_ID',
360 avalue => p_contract_id);
361 end;
362 begin
363 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
364 itemkey => L_KEY,
365 aname => 'CONTRACT_NUMBER',
366 avalue => l_contract_number);
367 exception
368 when others then
369 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
370 itemkey => L_KEY,
371 aname => 'CONTRACT_NUMBER');
372 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
373 itemkey => L_KEY,
374 aname => 'CONTRACT_NUMBER',
375 avalue => l_contract_number);
376 end;
377 begin
378 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
379 itemkey => L_KEY,
380 aname => 'CONTRACT_NUMBER_MODIFIER',
381 avalue => l_contract_number_MODIFIER);
382 exception
383 when others then
384 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
385 itemkey => L_KEY,
386 aname => 'CONTRACT_NUMBER_MODIFIER');
387 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
388 itemkey => L_KEY,
389 aname => 'CONTRACT_NUMBER_MODIFIER',
390 avalue => l_contract_number_MODIFIER);
391 end;
392 begin
393 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
394 itemkey => L_KEY,
395 aname => 'USER_ID',
396 avalue => fnd_global.user_id);
397 exception
398 when others then
399 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
400 itemkey => L_KEY,
401 aname => 'USER_ID');
402 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
403 itemkey => L_KEY,
404 aname => 'USER_ID',
405 avalue => fnd_global.user_id);
406 end;
407 begin
408 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
409 itemkey => L_KEY,
410 aname => 'RESP_ID',
411 avalue => fnd_global.resp_id);
412 exception
413 when others then
414 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
415 itemkey => L_KEY,
416 aname => 'RESP_ID');
417 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
418 itemkey => L_KEY,
419 aname => 'RESP_ID',
420 avalue => fnd_global.resp_id);
421 end;
422 begin
423 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
424 itemkey => L_KEY,
425 aname => 'RESP_APPL_ID',
426 avalue => fnd_global.RESP_APPL_id);
427 exception
428 when others then
429 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
430 itemkey => L_KEY,
431 aname => 'RESP_APPL_ID');
432 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
433 itemkey => L_KEY,
434 aname => 'RESP_APPL_ID',
435 avalue => fnd_global.RESP_APPL_id);
436 end;
437 begin
438 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
439 itemkey => L_KEY,
440 aname => 'SECURITY_GROUP_ID',
441 avalue => fnd_global.SECURITY_GROUP_id);
442 exception
443 when others then
444 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
445 itemkey => L_KEY,
446 aname => 'SECURITY_GROUP_ID');
447 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
448 itemkey => L_KEY,
449 aname => 'SECURITY_GROUP_ID',
450 avalue => fnd_global.SECURITY_GROUP_id);
451 end;
452 select value into L_NLS_VALUE
453 from NLS_SESSION_PARAMETERS
454 where PARAMETER='NLS_LANGUAGE';
455 begin
456 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
457 itemkey => L_KEY,
458 aname => 'NLS_LANGUAGE',
459 avalue => L_NLS_VALUE);
460 exception
461 when others then
462 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
463 itemkey => L_KEY,
464 aname => 'NLS_LANGUAGE');
465 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
466 itemkey => L_KEY,
467 aname => 'NLS_LANGUAGE',
468 avalue => L_NLS_VALUE);
469 end;
470 select value into L_NLS_VALUE
471 from NLS_SESSION_PARAMETERS
472 where PARAMETER='NLS_DATE_FORMAT';
473 begin
474 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
475 itemkey => L_KEY,
476 aname => 'NLS_DATE_FORMAT',
477 avalue => L_NLS_VALUE);
478 exception
479 when others then
480 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
481 itemkey => L_KEY,
482 aname => 'NLS_DATE_FORMAT');
483 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
484 itemkey => L_KEY,
485 aname => 'NLS_DATE_FORMAT',
486 avalue => L_NLS_VALUE);
487 end;
488 select value into L_NLS_VALUE
489 from NLS_SESSION_PARAMETERS
490 where PARAMETER='NLS_DATE_LANGUAGE';
491 begin
492 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
493 itemkey => L_KEY,
494 aname => 'NLS_DATE_LANGUAGE',
495 avalue => L_NLS_VALUE);
496 exception
497 when others then
498 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
499 itemkey => L_KEY,
500 aname => 'NLS_DATE_LANGUAGE');
501 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
502 itemkey => L_KEY,
503 aname => 'NLS_DATE_LANGUAGE',
504 avalue => L_NLS_VALUE);
505 end;
506 select '"'||value||'"' into L_NLS_VALUE
507 from NLS_SESSION_PARAMETERS
508 where PARAMETER='NLS_NUMERIC_CHARACTERS';
509 begin
510 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
511 itemkey => L_KEY,
512 aname => 'NLS_NUMERIC_CHARACTERS',
513 avalue => L_NLS_VALUE);
514 exception
515 when others then
516 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
517 itemkey => L_KEY,
518 aname => 'NLS_NUMERIC_CHARACTERS');
519 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
520 itemkey => L_KEY,
521 aname => 'NLS_NUMERIC_CHARACTERS',
522 avalue => L_NLS_VALUE);
523 end;
524 wf_engine.SetItemOwner ( itemtype => L_WF_NAME,
525 itemkey => L_KEY,
526 owner => fnd_global.user_name);
527 wf_engine.StartProcess( itemtype => L_WF_NAME,
528 itemkey => L_KEY);
529 if (p_do_commit = OKE_API.G_TRUE) then
530 commit;
531 end if;
532 x_return_status := OKE_API.G_RET_STS_SUCCESS;
533 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
534 EXCEPTION
535 WHEN OKE_API.G_EXCEPTION_ERROR THEN
536 x_return_status := OKE_API.HANDLE_EXCEPTIONS
537 (substr(l_api_name,1,26),
538 G_PKG_NAME,
539 'OKE_API.G_RET_STS_ERROR',
540 x_msg_count,
541 x_msg_data,
542 G_LEVEL);
543 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
544 x_return_status := OKE_API.HANDLE_EXCEPTIONS
545 (substr(l_api_name,1,26),
546 G_PKG_NAME,
547 'OKE_API.G_RET_STS_UNEXP_ERROR',
548 x_msg_count,
549 x_msg_data,
550 G_LEVEL);
551 WHEN OTHERS THEN
552 x_return_status := OKE_API.HANDLE_EXCEPTIONS
553 (substr(l_api_name,1,26),
554 G_PKG_NAME,
555 'OTHERS',
556 x_msg_count,
557 x_msg_data,
558 G_LEVEL);
559 end k_approval_start;
560
561 -- Start of comments
562 --
563 -- Procedure Name : wf_monitor_url
564 -- Description :
565 -- Business Rules :
566 -- Parameters :
567 -- Version : 1.0
568 -- End of comments
569
570 function wf_monitor_url(
571 p_contract_id IN number,
572 p_process_id IN number,
573 p_mode IN varchar2 default 'USER'
574 ) return varchar2 is
575 -- to be used by fnd_utilities.open_url
576 l_wf_name varchar2(150);
577 l_key varchar2(240);
578 l_q varchar2(1);
579 l_admin varchar2(3);
580 --
581 cursor wf_name_csr is
582 select WF_NAME
583 from OKC_PROCESS_DEFS_B
584 where ID = p_process_id and PDF_TYPE='WPS';
585 --
586 cursor wf_key_csr is
587 select CONTRACT_NUMBER||CONTRACT_NUMBER_MODIFIER wf_key
588 from OKC_K_HEADERS_B
589 where ID = p_contract_id;
590 --
591 cursor wf_exist_csr is
592 select '!'
593 from WF_ITEMS
594 where item_type = l_wf_name
595 and item_key = l_key;
596 --
597 begin
598 open wf_name_csr;
599 fetch wf_name_csr into L_WF_NAME;
600 close wf_name_csr;
601 --
602 open wf_key_csr;
603 fetch wf_key_csr into L_KEY;
604 close wf_key_csr;
605 --
606 l_q := '?';
607 open wf_exist_csr;
608 fetch wf_exist_csr into L_Q;
609 close wf_exist_csr;
610 --
611 if l_q = '?' then return NULL;
612 else
613 if p_mode = 'ADMIN' then l_admin := 'YES';
614 else l_admin := 'NO';
615 end if;
616 return wf_monitor.GetDiagramURL(
617 X_AGENT => WF_CORE.TRANSLATE('WF_WEB_AGENT'),
618 X_ITEM_TYPE => L_WF_NAME,
619 X_ITEM_KEY => L_KEY,
620 X_ADMIN_MODE => l_admin);
621 end if;
622 end wf_monitor_url;
623
624 -- Start of comments
625 --
626 -- Procedure Name : k_approval_stop
627 -- Description :
628 -- Business Rules :
629 -- Parameters :
630 -- Version : 1.0
631 -- End of comments
632
633 procedure k_approval_stop(
634 p_api_version IN NUMBER,
635 p_init_msg_list IN VARCHAR2 default OKE_API.G_FALSE,
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_msg_count OUT NOCOPY NUMBER,
638 x_msg_data OUT NOCOPY VARCHAR2,
639 p_contract_id number,
640 p_do_commit IN VARCHAR2 default OKE_API.G_TRUE
641 ) is
642 l_api_name CONSTANT VARCHAR2(30) := 'k_approval_stop';
643 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
644 --
645 l_q varchar2(1);
646 l_key varchar2(240);
647 l_wf_name_active varchar2(150);
648 l_contract_number varchar2(120);
649 l_contract_number_modifier varchar2(120);
650 L_K_SHORT_DESCRIPTION varchar2(2000);
651 L_NLS_VALUE VARCHAR2(30);
652 --
653 cursor wf_key_csr is
654 select CONTRACT_NUMBER||CONTRACT_NUMBER_MODIFIER wf_key,
655 CONTRACT_NUMBER, CONTRACT_NUMBER_MODIFIER,
656 short_description
657 from OKC_K_HDR_AGREEDS_V
658 where ID = p_contract_id;
659 --
660 cursor approval_active_csr is
661 select item_type
662 from WF_ITEMS
663 where item_type in
664 ( select wf_name
665 from OKC_PROCESS_DEFS_B
666 where USAGE='APPROVE' and PDF_TYPE='WPS')
667 and item_key = l_key
668 and end_date is NULL;
669 --
670 cursor abort_csr is
671 select '!'
672 from wf_activities
673 where item_type=l_wf_name_active
674 and TYPE='PROCESS' and NAME='ABORT_PROCESS'
675 ;
676 --
677 cursor C_INITIATOR_DISPLAY_NAME is
678 /*
679 select display_name
680 from wf_roles
681 where orig_system = 'FND_USR'
682 and orig_system_id=fnd_global.user_id
683 -- changed to boost perf
684 */
685 select user_name display_name from fnd_user where user_id=fnd_global.user_id and EMPLOYEE_ID is null
686 union all
687 select
688 PER.FULL_NAME display_name
689 from
690 PER_PEOPLE_F PER,
691 FND_USER USR
692 where trunc(SYSDATE)
693 between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE
694 and PER.PERSON_ID = USR.EMPLOYEE_ID
695 and USR.USER_ID = fnd_global.user_id
696 ;
697 --
698 L_INITIATOR_NAME varchar2(100);
699 L_FINAL_APPROVER_UNAME varchar2(100);
700 L_INITIATOR_DISPLAY_NAME varchar2(200);
701 --
702 begin
703 l_return_status := OKE_API.START_ACTIVITY(substr(l_api_name,1,26),
704 G_PKG_NAME,
705 p_init_msg_list,
706 l_api_version,
707 p_api_version,
708 G_LEVEL,
709 x_return_status);
710 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
711 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
712 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
713 RAISE OKE_API.G_EXCEPTION_ERROR;
714 END IF;
715 --
716 /* if k_accesible( p_contract_id => p_contract_id,
717 p_user_id => fnd_global.user_id,
718 p_level => 'U'
719 ) = OKE_API.G_FALSE
720 then
721 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
722 p_msg_name => G_NO_U_PRIVILEGE);
723 raise OKE_API.G_EXCEPTION_ERROR;
724 end if; */
725 --
726 open wf_key_csr;
727 fetch wf_key_csr into L_KEY,l_contract_number,l_contract_number_modifier,L_K_SHORT_DESCRIPTION;
728 close wf_key_csr;
729 --
730 open approval_active_csr;
731 fetch approval_active_csr into l_wf_name_active;
732 close approval_active_csr;
733 --
734 if l_wf_name_active is NULL then
735 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
736 p_msg_name => G_K_NOT_ON_APPROVAL);
737 raise OKE_API.G_EXCEPTION_ERROR;
738 end if;
739 wf_engine.abortprocess(l_wf_name_active,l_key);
740 k_erase_approved(
741 p_contract_id => p_contract_id,
742 x_return_status => l_return_status
743 );
744 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
745 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
746 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
747 RAISE OKE_API.G_EXCEPTION_ERROR;
748 END IF;
749 --+
750 L_Q:='?';
751 open abort_csr;
752 fetch abort_csr into L_Q;
753 close abort_csr;
754 --+
755 --+ if abort process defined
756 --+
757 if (L_Q = '!') then
758 -- previous initiator Uname
759 L_INITIATOR_NAME := wf_engine.GetItemAttrText(l_wf_name_active,L_KEY,'INITIATOR_NAME');
760 -- last approver Uname
761 L_FINAL_APPROVER_UNAME := NVL(
762 wf_engine.GetItemAttrText(l_wf_name_active,L_KEY,'NEXT_PERFORMER_USERNAME'),
763 wf_engine.GetItemAttrText(l_wf_name_active,L_KEY,'FINAL_APPROVER_UNAME')
764 );
765 if (L_FINAL_APPROVER_UNAME = L_INITIATOR_NAME) then
766 L_FINAL_APPROVER_UNAME := NULL;
767 end if;
768 begin
769 wf_purge.total(l_wf_name_active,l_key);
770 exception
771 when others then
772 begin
773 wf_purge.totalPerm(l_wf_name_active,l_key);
774 exception
775 when others then
776 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
777 p_msg_name => G_WF_NOT_PURGED,
778 p_token1 => G_WF_NAME_TOKEN,
779 p_token1_value => l_wf_name_active,
780 p_token2 => G_KEY_TOKEN,
781 p_token2_value => L_KEY);
782 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
783 end;
784 end;
785 wf_engine.CreateProcess( ItemType => l_wf_name_active,
786 ItemKey => L_KEY,
787 process => 'ABORT_PROCESS');
788 wf_engine.SetItemUserKey (ItemType => l_wf_name_active,
789 ItemKey => L_KEY,
790 UserKey => L_KEY);
791 --+
792 --+ attributes
793 --+
794 begin
795 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
796 itemkey => L_KEY,
797 aname => 'CONTRACT_ID',
798 avalue => p_contract_id);
799 exception
800 when others then
801 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
802 itemkey => L_KEY,
803 aname => 'CONTRACT_ID');
804 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
805 itemkey => L_KEY,
806 aname => 'CONTRACT_ID',
807 avalue => p_contract_id);
808 end;
809 begin
810 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
811 itemkey => L_KEY,
812 aname => 'CONTRACT_NUMBER',
813 avalue => l_contract_number);
814 exception
815 when others then
816 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
817 itemkey => L_KEY,
818 aname => 'CONTRACT_NUMBER');
819 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
820 itemkey => L_KEY,
821 aname => 'CONTRACT_NUMBER',
822 avalue => l_contract_number);
823 end;
824 begin
825 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
826 itemkey => L_KEY,
827 aname => 'CONTRACT_NUMBER_MODIFIER',
828 avalue => l_contract_number_MODIFIER);
829 exception
830 when others then
831 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
832 itemkey => L_KEY,
833 aname => 'CONTRACT_NUMBER_MODIFIER');
834 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
835 itemkey => L_KEY,
836 aname => 'CONTRACT_NUMBER_MODIFIER',
837 avalue => l_contract_number_MODIFIER);
838 end;
839 begin
840 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
841 itemkey => L_KEY,
842 aname => 'K_SHORT_DESCRIPTION',
843 avalue => L_K_SHORT_DESCRIPTION);
844 exception
845 when others then
846 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
847 itemkey => L_KEY,
848 aname => 'K_SHORT_DESCRIPTION');
849 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
850 itemkey => L_KEY,
851 aname => 'K_SHORT_DESCRIPTION',
852 avalue => L_K_SHORT_DESCRIPTION);
853 end;
854 -- current initiator Dname
855 open C_INITIATOR_DISPLAY_NAME;
856 fetch C_INITIATOR_DISPLAY_NAME into L_INITIATOR_DISPLAY_NAME;
857 close C_INITIATOR_DISPLAY_NAME;
858 begin
859 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
860 itemkey => L_KEY,
861 aname => 'INITIATOR_DISPLAY_NAME',
862 avalue => L_INITIATOR_DISPLAY_NAME);
863 exception
864 when others then
865 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
866 itemkey => L_KEY,
867 aname => 'INITIATOR_DISPLAY_NAME');
868 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
869 itemkey => L_KEY,
870 aname => 'INITIATOR_DISPLAY_NAME',
871 avalue => L_INITIATOR_DISPLAY_NAME);
872 end;
873 -- previous initiator Uname
874 begin
875 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
876 itemkey => L_KEY,
877 aname => 'INITIATOR_NAME',
878 avalue => L_INITIATOR_NAME);
879 exception
880 when others then
881 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
882 itemkey => L_KEY,
883 aname => 'INITIATOR_NAME');
884 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
885 itemkey => L_KEY,
886 aname => 'INITIATOR_NAME',
887 avalue => L_INITIATOR_NAME);
888 end;
889 -- previous approver Uname
890 begin
891 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
892 itemkey => L_KEY,
893 aname => 'FINAL_APPROVER_UNAME',
894 avalue => L_FINAL_APPROVER_UNAME);
895 exception
896 when others then
897 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
898 itemkey => L_KEY,
899 aname => 'FINAL_APPROVER_UNAME');
900 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
901 itemkey => L_KEY,
902 aname => 'FINAL_APPROVER_UNAME',
903 avalue => L_FINAL_APPROVER_UNAME);
904 end;
905 --
906 -- environment
907 --
908 begin
909 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
910 itemkey => L_KEY,
911 aname => 'USER_ID',
912 avalue => fnd_global.user_id);
913 exception
914 when others then
915 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
916 itemkey => L_KEY,
917 aname => 'USER_ID');
918 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
919 itemkey => L_KEY,
920 aname => 'USER_ID',
921 avalue => fnd_global.user_id);
922 end;
923 begin
924 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
925 itemkey => L_KEY,
926 aname => 'RESP_ID',
927 avalue => fnd_global.resp_id);
928 exception
929 when others then
930 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
931 itemkey => L_KEY,
932 aname => 'RESP_ID');
933 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
934 itemkey => L_KEY,
935 aname => 'RESP_ID',
936 avalue => fnd_global.resp_id);
937 end;
938 begin
939 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
940 itemkey => L_KEY,
941 aname => 'RESP_APPL_ID',
942 avalue => fnd_global.RESP_APPL_id);
943 exception
944 when others then
945 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
946 itemkey => L_KEY,
947 aname => 'RESP_APPL_ID');
948 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
949 itemkey => L_KEY,
950 aname => 'RESP_APPL_ID',
951 avalue => fnd_global.RESP_APPL_id);
952 end;
953 begin
954 wf_engine.SetItemAttrNumber (itemtype => l_wf_name_active,
955 itemkey => L_KEY,
956 aname => 'SECURITY_GROUP_ID',
957 avalue => fnd_global.SECURITY_GROUP_id);
958 exception
959 when others then
960 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
961 itemkey => L_KEY,
962 aname => 'SECURITY_GROUP_ID');
963 wf_engine.SetItemAttrNumber(itemtype => l_wf_name_active,
964 itemkey => L_KEY,
965 aname => 'SECURITY_GROUP_ID',
966 avalue => fnd_global.SECURITY_GROUP_id);
967 end;
968 select value into L_NLS_VALUE
969 from NLS_SESSION_PARAMETERS
970 where PARAMETER='NLS_LANGUAGE';
971 begin
972 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
973 itemkey => L_KEY,
974 aname => 'NLS_LANGUAGE',
975 avalue => L_NLS_VALUE);
976 exception
977 when others then
978 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
979 itemkey => L_KEY,
980 aname => 'NLS_LANGUAGE');
981 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
982 itemkey => L_KEY,
983 aname => 'NLS_LANGUAGE',
984 avalue => L_NLS_VALUE);
985 end;
986 select value into L_NLS_VALUE
987 from NLS_SESSION_PARAMETERS
988 where PARAMETER='NLS_DATE_FORMAT';
989 begin
990 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
991 itemkey => L_KEY,
992 aname => 'NLS_DATE_FORMAT',
993 avalue => L_NLS_VALUE);
994 exception
995 when others then
996 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
997 itemkey => L_KEY,
998 aname => 'NLS_DATE_FORMAT');
999 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1000 itemkey => L_KEY,
1001 aname => 'NLS_DATE_FORMAT',
1002 avalue => L_NLS_VALUE);
1003 end;
1004 select value into L_NLS_VALUE
1005 from NLS_SESSION_PARAMETERS
1006 where PARAMETER='NLS_DATE_LANGUAGE';
1007 begin
1008 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1009 itemkey => L_KEY,
1010 aname => 'NLS_DATE_LANGUAGE',
1011 avalue => L_NLS_VALUE);
1012 exception
1013 when others then
1014 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1015 itemkey => L_KEY,
1016 aname => 'NLS_DATE_LANGUAGE');
1017 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1018 itemkey => L_KEY,
1019 aname => 'NLS_DATE_LANGUAGE',
1020 avalue => L_NLS_VALUE);
1021 end;
1022 select '"'||value||'"' into L_NLS_VALUE
1023 from NLS_SESSION_PARAMETERS
1024 where PARAMETER='NLS_NUMERIC_CHARACTERS';
1025 begin
1026 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1027 itemkey => L_KEY,
1028 aname => 'NLS_NUMERIC_CHARACTERS',
1029 avalue => L_NLS_VALUE);
1030 exception
1031 when others then
1032 wf_engine.AddItemAttr (itemtype => l_wf_name_active,
1033 itemkey => L_KEY,
1034 aname => 'NLS_NUMERIC_CHARACTERS');
1035 wf_engine.SetItemAttrText (itemtype => l_wf_name_active,
1036 itemkey => L_KEY,
1037 aname => 'NLS_NUMERIC_CHARACTERS',
1038 avalue => L_NLS_VALUE);
1039 end;
1040 --
1041 -- start
1042 --
1043 wf_engine.SetItemOwner ( itemtype => l_wf_name_active,
1044 itemkey => L_KEY,
1045 owner => fnd_global.user_name);
1046 wf_engine.StartProcess( itemtype => l_wf_name_active,
1047 itemkey => L_KEY);
1048
1049 end if;--+ abort process exists
1050 --
1051 if (p_do_commit = OKE_API.G_TRUE) then
1052 commit;
1053 end if;
1054 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1055 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1056 EXCEPTION
1057 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1058 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1059 (substr(l_api_name,1,26),
1060 G_PKG_NAME,
1061 'OKE_API.G_RET_STS_ERROR',
1062 x_msg_count,
1063 x_msg_data,
1064 G_LEVEL);
1065 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1066 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1067 (substr(l_api_name,1,26),
1068 G_PKG_NAME,
1069 'OKE_API.G_RET_STS_UNEXP_ERROR',
1070 x_msg_count,
1071 x_msg_data,
1072 G_LEVEL);
1073 WHEN OTHERS THEN
1074 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1075 (substr(l_api_name,1,26),
1076 G_PKG_NAME,
1077 'OTHERS',
1078 x_msg_count,
1079 x_msg_data,
1080 G_LEVEL);
1081 end k_approval_stop;
1082
1083 -- Start of comments
1084 --
1085 -- Procedure Name : wf_copy_env
1086 -- Description :
1087 -- Business Rules :
1088 -- Parameters :
1089 -- Version : 1.0
1090 -- End of comments
1091
1092 procedure wf_copy_env( p_item_type varchar2,
1093 p_item_key varchar2) is
1094 L_NLS_VALUE1 varchar2(40);
1095 L_NLS_VALUE2 varchar2(40);
1096 L_NLS_VALUE3 varchar2(40);
1097 L_NLS_VALUE4 varchar2(40);
1098 L_NLS_VALUE11 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_LANGUAGE');
1099 L_NLS_VALUE12 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_DATE_FORMAT');
1100 L_NLS_VALUE13 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_DATE_LANGUAGE');
1101 L_NLS_VALUE14 varchar2(40) := wf_engine.GetItemAttrText(p_item_type,p_item_key,'NLS_NUMERIC_CHARACTERS');
1102 cursor c1(p varchar2) is
1103 select value
1104 from NLS_SESSION_PARAMETERS
1105 where PARAMETER=p;
1106 begin
1107 open c1('NLS_LANGUAGE');
1108 fetch c1 into L_NLS_VALUE1;
1109 close c1;
1110 open c1('NLS_DATE_FORMAT');
1111 fetch c1 into L_NLS_VALUE2;
1112 close c1;
1113 open c1('NLS_DATE_LANGUAGE');
1114 fetch c1 into L_NLS_VALUE3;
1115 close c1;
1116 open c1('NLS_NUMERIC_CHARACTERS');
1117 fetch c1 into L_NLS_VALUE4;
1118 L_NLS_VALUE4 := '"'||L_NLS_VALUE4||'"';
1119 close c1;
1120 if not(
1121 (L_NLS_VALUE11 = L_NLS_VALUE1) and
1122 (L_NLS_VALUE12 = L_NLS_VALUE2) and
1123 (L_NLS_VALUE13 = L_NLS_VALUE3) and
1124 (L_NLS_VALUE14 = L_NLS_VALUE4)
1125 ) then
1126 fnd_global.set_nls_context
1127 (
1128 P_NLS_LANGUAGE => L_NLS_VALUE11,
1129 P_NLS_DATE_FORMAT => L_NLS_VALUE12,
1130 P_NLS_DATE_LANGUAGE => L_NLS_VALUE13,
1131 P_NLS_NUMERIC_CHARACTERS => L_NLS_VALUE14);
1132 end if;
1133 fnd_global.apps_initialize
1134 (
1135 user_id =>
1136 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'USER_ID'),
1137 resp_id =>
1138 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'RESP_ID'),
1139 resp_appl_id =>
1140 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'RESP_APPL_ID'),
1141 security_group_id =>
1142 wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'SECURITY_GROUP_ID')
1143 );
1144 -- okc_context.set_okc_org_context;
1145 end wf_copy_env;
1146
1147 -- Start of comments
1148 --
1149 -- Procedure Name : k_accesible
1150 -- Description :
1151 -- Business Rules :
1152 -- Parameters :
1153 -- Version : 1.0
1154 -- End of comments
1155
1156 function k_accesible(
1157 p_contract_id IN number,
1158 p_user_id IN number,
1159 p_level IN varchar2 default 'R'
1160 ) return varchar2 is
1161 l_q varchar2(1);
1162 /*
1163 cursor check_access_csr is
1164 (select '!'
1165 from OKC_K_ACCESSES
1166 where chr_id = p_contract_id
1167 and user_id = p_user_id
1168 and (p_level='R' or access_level='U')
1169 )
1170 UNION ALL
1171 (select '!' from dual
1172 where exists
1173 (select agp_code
1174 from OKC_K_ACCESSES
1175 where chr_id = P_CONTRACT_ID
1176 and (p_level='R' or access_level='U')
1177 INTERSECT
1178 SELECT AGP_CODE
1179 FROM okc_acc_group_members
1180 where begin_date<=sysdate
1181 and (end_date is null or end_date>=sysdate)
1182 start with user_id = p_user_id
1183 CONNECT BY PRIOR AGP_CODE = AGP_CODE_COMPOSED_OF
1184 )
1185 );
1186 */
1187 begin
1188 l_q :=okc_util.get_k_access_level(p_chr_id => p_contract_id);
1189 if ((l_q = p_level) or (l_q = 'U'))
1190 then return OKC_API.G_TRUE;
1191 else return OKC_API.G_FALSE;
1192 end if;
1193 /*
1194 --
1195 open check_access_csr;
1196 fetch check_access_csr into l_q;
1197 close check_access_csr;
1198 --
1199
1200 if l_q = '?' then return OKC_API.G_FALSE;
1201 else return OKC_API.G_TRUE;
1202 end if;
1203 */
1204
1205 end k_accesible;
1206
1207 --
1208 -- private procedure
1209 -- to set context of db failure
1210 --
1211 procedure db_failed(p_oper varchar2) is
1212 begin
1213 FND_MESSAGE.SET_NAME(application => G_APP_NAME,
1214 name => 'OKC_DB_OPERATION_FAILED');
1215 -- OKC_SIGN OKC_APPROVE OKC_REVOKE
1216 FND_MESSAGE.SET_TOKEN(token => 'OPERATION',
1217 value => p_oper,
1218 translate => TRUE);
1219 FND_MSG_PUB.add;
1220 end db_failed;
1221
1222
1223 -- Start of comments
1224 --
1225 -- Procedure Name : k_approved
1226 -- Description :
1227 -- Business Rules :
1228 -- Parameters :
1229 -- Version : 1.0
1230 -- End of comments
1231
1232 procedure k_approved(
1233 p_contract_id IN number,
1234 p_date_approved IN date default sysdate,
1235 x_return_status OUT NOCOPY varchar2
1236 ) is
1237 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1238 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1239 cursor lock_csr(p number) is
1240 select object_version_number
1241 from okc_k_headers_b
1242 where ID = p
1243 ;
1244 l_api_name CONSTANT VARCHAR2(30) := 'k_approved';
1245 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1246 l_msg_count NUMBER;
1247 l_msg_data varchar2(2000);
1248 begin
1249
1250 --start
1251 l_return_status := OKE_API.START_ACTIVITY(substr(l_api_name,1,26),
1252 G_PKG_NAME,
1253 OKE_API.G_TRUE,
1254 l_api_version,
1255 l_api_version,
1256 G_LEVEL,
1257 x_return_status);
1258 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1259 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1260 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1261 RAISE OKE_API.G_EXCEPTION_ERROR;
1262 END IF;
1263
1264 --lock
1265 L1_header_rec.id := p_contract_id;
1266 open lock_csr(p_contract_id);
1267 fetch lock_csr into L1_header_rec.object_version_number;
1268 close lock_csr;
1269 OKC_CONTRACT_PUB.lock_contract_header(
1270 p_api_version => l_api_version,
1271 x_return_status => x_return_status,
1272 x_msg_count => l_msg_count,
1273 x_msg_data => l_msg_data,
1274 p_chrv_rec => L1_header_rec);
1275 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1276 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1277 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1278 RAISE OKE_API.G_EXCEPTION_ERROR;
1279 END IF;
1280
1281 --update
1282 L1_header_rec.date_approved := p_date_approved;
1283 OKC_CONTRACT_PUB.update_contract_header(
1284 p_api_version => l_api_version,
1285 x_return_status => x_return_status,
1286 p_init_msg_list => OKE_API.G_TRUE,
1287 x_msg_count => l_msg_count,
1288 x_msg_data => l_msg_data,
1289 p_restricted_update => OKE_API.G_TRUE,
1290 p_chrv_rec => L1_header_rec,
1291 x_chrv_rec => L2_header_rec);
1292 IF (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1293 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1294 ELSIF (x_return_status = OKE_API.G_RET_STS_ERROR) THEN
1295 RAISE OKE_API.G_EXCEPTION_ERROR;
1296 END IF;
1297
1298 --end
1299 OKE_API.END_ACTIVITY(l_msg_count, l_msg_data);
1300 EXCEPTION
1301 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1302 db_failed('OKC_APPROVE');
1303 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1304 (substr(l_api_name,1,26),
1305 G_PKG_NAME,
1306 'OKE_API.G_RET_STS_ERROR',
1307 l_msg_count,
1308 l_msg_data,
1309 G_LEVEL);
1310 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1311 db_failed('OKC_APPROVE');
1312 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1313 (substr(l_api_name,1,26),
1314 G_PKG_NAME,
1315 'OKC_API.G_RET_STS_UNEXP_ERROR',
1316 l_msg_count,
1317 l_msg_data,
1318 G_LEVEL);
1319 WHEN OTHERS THEN
1320 db_failed('OKC_APPROVE');
1321 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1322 (substr(l_api_name,1,26),
1323 G_PKG_NAME,
1324 'OTHERS',
1325 l_msg_count,
1326 l_msg_data,
1327 G_LEVEL);
1328 end k_approved;
1329
1330 -- Start of comments
1331 --
1332 -- Procedure Name : k_erase_approved
1333 -- Description :
1334 -- Business Rules :
1335 -- Parameters :
1336 -- Version : 1.0
1337 -- End of comments
1338
1339 procedure k_erase_approved(
1340 p_contract_id IN number,
1341 x_return_status OUT NOCOPY VARCHAR2
1342 ) is
1343 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1344 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1345 dummy varchar2(1) := '?';
1346 cursor c1 is
1347 select '!'
1348 from OKC_K_HEADERS_B
1349 where ID = p_contract_id and date_approved is not null;
1350 --
1351 cursor lock_csr(p number) is
1352 select object_version_number
1353 from OKC_K_HEADERS_B
1354 where ID = p
1355 ;
1356 l_api_name CONSTANT VARCHAR2(30) := 'k_erase_approved';
1357 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1358 l_msg_count NUMBER;
1359 l_msg_data varchar2(2000);
1360 begin
1361
1362 --check if do anything
1363 open c1;
1364 fetch c1 into dummy;
1365 close c1;
1366 if (dummy = '?') then
1367 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1368 return;
1369 end if;
1370
1371 --start
1372 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1373 G_PKG_NAME,
1374 OKC_API.G_TRUE,
1375 l_api_version,
1376 l_api_version,
1377 G_LEVEL,
1378 x_return_status);
1379 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1380 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1381 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1382 RAISE OKC_API.G_EXCEPTION_ERROR;
1383 END IF;
1384
1385 --lock
1386 L1_header_rec.id := p_contract_id;
1387 open lock_csr(p_contract_id);
1388 fetch lock_csr into L1_header_rec.object_version_number;
1389 close lock_csr;
1390 OKC_CONTRACT_PUB.lock_contract_header(
1391 p_api_version => l_api_version,
1392 x_return_status => x_return_status,
1393 x_msg_count => l_msg_count,
1394 x_msg_data => l_msg_data,
1395 p_chrv_rec => L1_header_rec);
1396 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1397 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1398 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1399 RAISE OKC_API.G_EXCEPTION_ERROR;
1400 END IF;
1401
1402 --update
1403 L1_header_rec.date_approved := NULL;
1404 OKC_CONTRACT_PUB.update_contract_header(
1405 p_api_version => l_api_version,
1406 x_return_status => x_return_status,
1407 x_msg_count => l_msg_count,
1408 x_msg_data => l_msg_data,
1409 p_restricted_update => OKC_API.G_TRUE,
1410 p_chrv_rec => L1_header_rec,
1411 x_chrv_rec => L2_header_rec);
1412 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1413 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1414 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1415 RAISE OKC_API.G_EXCEPTION_ERROR;
1416 END IF;
1417
1418 --end
1419 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
1420 EXCEPTION
1421 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1422 db_failed('OKC_REVOKE');
1423 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1424 (substr(l_api_name,1,26),
1425 G_PKG_NAME,
1426 'OKC_API.G_RET_STS_ERROR',
1427 l_msg_count,
1428 l_msg_data,
1429 G_LEVEL);
1430 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1431 db_failed('OKC_REVOKE');
1432 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1433 (substr(l_api_name,1,26),
1434 G_PKG_NAME,
1435 'OKC_API.G_RET_STS_UNEXP_ERROR',
1436 l_msg_count,
1437 l_msg_data,
1438 G_LEVEL);
1439 WHEN OTHERS THEN
1440 db_failed('OKC_REVOKE');
1441 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1442 (substr(l_api_name,1,26),
1443 G_PKG_NAME,
1444 'OTHERS',
1445 l_msg_count,
1446 l_msg_data,
1447 G_LEVEL);
1448 end k_erase_approved;
1449
1450 -- Start of comments
1451 --
1452 -- Procedure Name : k_signed
1453 -- Description :
1454 -- Business Rules :
1455 -- Parameters :
1456 -- Version : 1.0
1457 -- End of comments
1458
1459 procedure k_signed(
1460 p_contract_id IN number,
1461 p_date_signed IN date default sysdate,
1462 x_return_status OUT NOCOPY VARCHAR2
1463 ) is
1464 l_api_name CONSTANT VARCHAR2(30) := 'k_signed';
1465 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1466 l_msg_count NUMBER;
1467 l_msg_data varchar2(2000);
1468
1469 L1_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1470 L2_header_rec OKC_CONTRACT_PUB.chrv_rec_type;
1471 cursor lock_csr(p number) is
1472 select object_version_number, START_DATE, END_DATE
1473 from okc_k_headers_b
1474 where ID = p
1475 ;
1476 --
1477 l_new_status varchar2(30);
1478 l_signed_status varchar2(30);
1479 l_active_status varchar2(30);
1480 l_expired_status varchar2(30);
1481 cursor c1 is
1482 select code from okc_statuses_b
1483 where ste_code='SIGNED'
1484 and default_yn='Y';
1485 cursor c2 is
1486 select code from okc_statuses_b
1487 where ste_code='ACTIVE'
1488 and default_yn='Y';
1489 cursor c3 is
1490 select code from okc_statuses_b
1491 where ste_code='EXPIRED'
1492 and default_yn='Y';
1493 --
1494 cursor lock1_csr is
1495 select L.ID ID, L.object_version_number
1496 ,decode(sign(months_between(sysdate-1, NVL(L.end_date,sysdate))),-1,
1497 decode(sign(months_between(p_date_signed-1,sysdate)),-1,
1498 decode(sign(months_between(L.start_date-1,sysdate)),-1,
1499 l_active_status,l_signed_status),l_signed_status),l_expired_status) STS_CODE
1500 from okc_k_lines_b L
1501 , okc_statuses_b S
1502 where L.dnz_chr_id = p_contract_id
1503 and S.code = L.sts_code
1504 and S.ste_code='ENTERED'
1505 ;
1506 --
1507 loc1_rec lock1_csr%ROWTYPE;
1508 i number :=0;
1509 --
1510 l1_lines okc_contract_pub.clev_tbl_type;
1511 l2_lines okc_contract_pub.clev_tbl_type;
1512 l3_lines okc_contract_pub.clev_tbl_type;
1513 --
1514 call_time varchar2(1);
1515
1516 begin
1517
1518 --start
1519 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1520 G_PKG_NAME,
1521 OKC_API.G_TRUE,
1522 l_api_version,
1523 l_api_version,
1524 G_LEVEL,
1525 x_return_status);
1526 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1527 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1528 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1529 RAISE OKC_API.G_EXCEPTION_ERROR;
1530 END IF;
1531
1532 --lock header
1533 L1_header_rec.id := p_contract_id;
1534 open lock_csr(p_contract_id);
1535 fetch lock_csr into
1536 L1_header_rec.object_version_number,L1_header_rec.START_DATE,L1_header_rec.END_DATE;
1537 close lock_csr;
1538 OKC_CONTRACT_PUB.lock_contract_header(
1539 p_api_version => l_api_version,
1540 x_return_status => x_return_status,
1541 x_msg_count => l_msg_count,
1542 x_msg_data => l_msg_data,
1543 p_chrv_rec => L1_header_rec);
1544 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1545 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1546 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1547 RAISE OKC_API.G_EXCEPTION_ERROR;
1548 END IF;
1549
1550 --update header
1551 L1_header_rec.date_signed := p_date_signed;
1552 --
1553 open c1;
1554 fetch c1 into l_signed_status;
1555 close c1;
1556 --
1557 open c2;
1558 fetch c2 into l_active_status;
1559 close c2;
1560 --
1561 open c3;
1562 fetch c3 into l_expired_status;
1563 close c3;
1564 --
1565 l_new_status := l_signed_status; call_time := 'Y';
1566 if (L1_header_rec.date_signed <= sysdate
1567 and L1_header_rec.START_DATE <= sysdate
1568 and (L1_header_rec.END_DATE is NULL or sysdate<=L1_header_rec.END_DATE+1)) then
1569 l_new_status := l_active_status; call_time := 'Y';
1570 end if;
1571 if (sysdate>L1_header_rec.END_DATE+1) then
1572 l_new_status := l_expired_status; call_time := 'N';
1573 end if;
1574 L1_header_rec.STS_CODE := l_new_status;
1575
1576 OKC_CONTRACT_PUB.update_contract_header(
1577 p_api_version => l_api_version,
1578 x_return_status => x_return_status,
1579 x_msg_count => l_msg_count,
1580 x_msg_data => l_msg_data,
1581 p_restricted_update => OKC_API.G_TRUE,
1582 p_chrv_rec => L1_header_rec,
1583 x_chrv_rec => L2_header_rec);
1584 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1586 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1587 RAISE OKC_API.G_EXCEPTION_ERROR;
1588 END IF;
1589
1590 -- lock lines
1591 for lock1_rec in lock1_csr LOOP
1592 i := i+1;
1593 l1_lines(i).id := lock1_rec.id;
1594 l1_lines(i).object_version_number := lock1_rec.object_version_number;
1595 --
1596 l2_lines(i).id := lock1_rec.id;
1597 l2_lines(i).object_version_number := lock1_rec.object_version_number;
1598 l2_lines(i).sts_code := lock1_rec.sts_code;
1599 end LOOP;
1600 OKC_CONTRACT_PUB.lock_contract_line(
1601 p_api_version => l_api_version,
1602 x_return_status => x_return_status,
1603 x_msg_count => l_msg_count,
1604 x_msg_data => l_msg_data,
1605 p_clev_tbl => l1_lines);
1606 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1607 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1608 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1609 RAISE OKC_API.G_EXCEPTION_ERROR;
1610 END IF;
1611
1612 -- update lines
1613 OKC_CONTRACT_PUB.update_contract_line(
1614 p_api_version => l_api_version,
1615 x_return_status => x_return_status,
1616 x_msg_count => l_msg_count,
1617 x_msg_data => l_msg_data,
1618 p_restricted_update => OKC_API.G_TRUE,
1619 p_clev_tbl => l2_lines,
1620 x_clev_tbl => l3_lines);
1621 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1622 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1623 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1624 RAISE OKC_API.G_EXCEPTION_ERROR;
1625 END IF;
1626
1627 -- call time ...
1628 if (call_time = 'Y') then
1629 OKC_TIME_RES_PUB.Res_Time_New_K(L2_header_rec.id, l_api_version,OKC_API.G_FALSE,x_return_status);
1630 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1631 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1632 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1633 RAISE OKC_API.G_EXCEPTION_ERROR;
1634 END IF;
1635 end if;
1636
1637 -- raise event
1638 OKC_K_SIGN_ASMBLR_PVT.acn_assemble(
1639 p_api_version => l_api_version,
1640 x_return_status => x_return_status,
1641 x_msg_count => l_msg_count,
1642 x_msg_data => l_msg_data,
1643 p_contract_id => p_contract_id);
1644 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1645 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1646 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1647 RAISE OKC_API.G_EXCEPTION_ERROR;
1648 END IF;
1649
1650 --end
1651 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
1652 EXCEPTION
1653 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1654 db_failed('OKC_SIGN');
1655 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1656 (substr(l_api_name,1,26),
1657 G_PKG_NAME,
1658 'OKC_API.G_RET_STS_ERROR',
1659 l_msg_count,
1660 l_msg_data,
1661 G_LEVEL);
1662 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1663 db_failed('OKC_SIGN');
1664 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1665 (substr(l_api_name,1,26),
1666 G_PKG_NAME,
1667 'OKC_API.G_RET_STS_UNEXP_ERROR',
1668 l_msg_count,
1669 l_msg_data,
1670 G_LEVEL);
1671 WHEN OTHERS THEN
1672 db_failed('OKC_SIGN');
1673 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1674 (substr(l_api_name,1,26),
1675 G_PKG_NAME,
1676 'OTHERS',
1677 l_msg_count,
1678 l_msg_data,
1679 G_LEVEL);
1680 end k_signed;
1681
1682 end OKE_CONTRACT_APPROVAL_PVT;