[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_SIGN_WF_PVT
Source
1 PACKAGE BODY okc_rep_sign_wf_pvt AS
2 /* $Header: OKCREPSWFB.pls 120.0.12020000.10 2013/04/25 18:28:20 kkolukul noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Global VARIABLES
6 ---------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_SIGN_WF_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKC';
9 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
10
11 G_OBJECT_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_CONTRACT';
12 G_STATUS_REJECTED CONSTANT VARCHAR2(30) := 'REJECTED';
13 G_STATUS_TIMEOUT CONSTANT VARCHAR2(30) := 'TIMEOUT';
14 G_ACTION_SUBMITTED CONSTANT VARCHAR2(30) := 'SUBMITTED';
15 G_STATUS_APPROVED CONSTANT VARCHAR2(30) := 'APPROVED';
16
17 ------------------------------------------------------------------------------
18 -- GLOBAL CONSTANTS
19 ------------------------------------------------------------------------------
20 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
21 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
22 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
23 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
24 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
25 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
26 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
27 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
28 G_SIGN_NOTIF_PROCESS CONSTANT VARCHAR2(200) := 'SIGN_NOTIFICATION';
29 G_STATUS_SIGNED CONSTANT VARCHAR2(30) := 'SIGNED';
30 G_APPLICATION_ID CONSTANT NUMBER := 510;
31 G_WF_STATUS_SIGNED CONSTANT VARCHAR2(200) := 'SIGN';
32 G_WF_STATUS_REJECTED CONSTANT VARCHAR2(200) := 'REJECT';
33 G_WF_STATUS_MORE_APPROVERS CONSTANT VARCHAR2(200) := 'OKC_REP_MORE_APPROVERS';
34 G_WF_STATUS_TRANSFERRED CONSTANT VARCHAR2(200) := 'TRANSFERRED';
35 G_WF_STATUS_DELEGATED CONSTANT VARCHAR2(200) := 'DELEGATED';
36 -- Contracts business events codes TBL Type
37 SUBTYPE EVENT_TBL_TYPE IS OKC_MANAGE_DELIVERABLES_GRP.BUSDOCDATES_TBL_TYPE;
38 -- Contract events - deliverables integration
39 G_CONTRACT_EXPIRE_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_EXPIRE';
40 G_CONTRACT_EFFECTIVE_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_EFFECTIVE';
41 G_CONTRACT_TERMINATED_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_TERMINATED';
42
43 -- Required for Contract not found error message
44 G_INVALID_CONTRACT_ID_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_INVALID_CONTRACT_ID';
45 G_CANEL_APPROVAL_ERROR_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_CANCEL_APPROVAL_ERROR';
46 G_CONTRACT_ID_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_ID';
47 G_CONTRACT_NUM_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_NUM';
48
49 ------------------------------------------------------------------------------
50 -- GLOBAL EXCEPTION
51 ------------------------------------------------------------------------------
52 E_Resource_Busy EXCEPTION;
53 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
54
55 ------------------------------------------------------------------------------
56 -- GLOBAL VARIABLES
57 ------------------------------------------------------------------------------
58
59 TYPE SIGNERS_REC_TYPE IS RECORD
60 (
61 NAME VARCHAR2(70),
62 CONTACT_ID NUMBER ,
63 party_id NUMBER ,
64 party_role_code VARCHAR2(50),
65 SEQ NUMBER ,
66 subject VARCHAR2(2000)
67 );
68
69 TYPE SIGNERS_TBL_TYPE IS TABLE OF SIGNERS_REC_TYPE
70 INDEX BY BINARY_INTEGER;
71
72
73 ---------------------------------------------------------------------------
74 -- START: Procedures and Functions
75 ---------------------------------------------------------------------------
76
77 -- Start of comments
78 --API name : initialize_attributes
79 --Type : Private.
80 --Function : This procedure is called by workflow to initialize workflow attributes.
81 --Pre-reqs : None.
82 --Parameters :
83 --IN : itemtype IN VARCHAR2 Required
84 -- Workflow item type parameter
85 -- : itemkey IN VARCHAR2 Required
86 -- Workflow item key parameter
87 -- : actid IN VARCHAR2 Required
88 -- Workflow actid parameter
89 -- : funcmode IN VARCHAR2 Required
90 -- Workflow function mode parameter
91 --OUT : resultout OUT VARCHAR2(1)
92 -- Workflow standard out parameter
93 -- Note :
94 -- End of comments
95 PROCEDURE initialize_attributes(
96 itemtype IN varchar2,
97 itemkey IN varchar2,
98 actid IN number,
99 funcmode IN varchar2,
100 resultout OUT nocopy varchar2
101 ) IS
102
103 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
104 l_api_name VARCHAR2(30);
105
106 CURSOR contract_csr(l_contract_id NUMBER) IS
107 SELECT contract_type, contract_number, contract_name, contract_version_num
108 FROM okc_rep_contracts_all
109 WHERE contract_id = l_contract_id;
110
111 contract_rec contract_csr%ROWTYPE;
112
113 l_resolved_token varchar2(100);
114
115 BEGIN
116
117 l_api_name := 'initialize_attributes';
118
119 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
120 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
121 'Entered OKC_REP_SIGN_WF_PVT.initialize_attributes');
122 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
123 'Item Type is: ' || itemtype);
124 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
125 'Item Key is: ' || itemkey);
126 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
127 'actid is: ' || to_char(actid));
128 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
129 'Function mode is: ' || funcmode);
130 END IF;
131 IF (funcmode = 'RUN') THEN
132 l_contract_id := wf_engine.GetItemAttrNumber(
133 itemtype => itemtype,
134 itemkey => itemkey,
135 aname => 'CONTRACT_ID');
136
137 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
138 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
139 'Calling WF_ENGINE.getitemattrnumber for l_contract_id ' || l_contract_id);
140 END IF;
141
142 -- Get contract attributes
143 OPEN contract_csr(l_contract_id);
144 FETCH contract_csr INTO contract_rec;
145 IF(contract_csr%NOTFOUND) THEN
146 RAISE NO_DATA_FOUND;
147 END IF;
148
149 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
151 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_TYPE ' || contract_rec.contract_type);
152 END IF;
153 WF_ENGINE.SetItemAttrText (
154 itemtype => itemtype,
155 itemkey => itemkey,
156 aname => 'CONTRACT_TYPE',
157 avalue => contract_rec.contract_type);
158
159
160 l_resolved_token := OKC_API.resolve_hdr_token(contract_rec.contract_type);
161
162 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
164 'Calling WF_ENGINE.setitemattrtext for CONTRACT_HDR_TOKEN ' || l_resolved_token);
165 END IF;
166
167
168 WF_ENGINE.SetItemAttrText (
169 itemtype => itemtype,
170 itemkey => itemkey,
171 aname => 'CONTRACT_HDR_TOKEN',
172 avalue => l_resolved_token);
173
174
175 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
176 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
177 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_NUMBER ' || contract_rec.contract_number);
178 END IF;
179
180 WF_ENGINE.SetItemAttrText (
181 itemtype => itemtype,
182 itemkey => itemkey,
183 aname => 'CONTRACT_NUMBER',
184 avalue => contract_rec.contract_number);
185
186
187 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
188 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
189 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_VERSION ' || contract_rec.contract_version_num);
190 END IF;
191
192 WF_ENGINE.SetItemAttrNumber (
193 itemtype => itemtype,
194 itemkey => itemkey,
195 aname => 'CONTRACT_VERSION',
196 avalue => contract_rec.contract_version_num);
197
198 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
199 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
200 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_NAME ' || contract_rec.contract_name);
201 END IF;
202
203 WF_ENGINE.SetItemAttrText (
204 itemtype => itemtype,
205 itemkey => itemkey,
206 aname => 'CONTRACT_NAME',
207 avalue => contract_rec.contract_name);
208
209 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
210 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
211 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_ATTACHMENTS');
212 END IF;
213
214 WF_ENGINE.SetItemAttrText (
215 itemtype => itemtype,
216 itemkey => itemkey,
217 aname => 'CONTRACT_ATTACHMENTS',
218 avalue => 'FND:entity=OKC_CONTRACT_DOCS&pk1name=BusinessDocumentType&pk1value='||contract_rec.contract_type
219 ||'&pk2name=BusinessDocumentId&pk2value='||l_contract_id
220 ||'&pk3name=BusinessDocumentVersion&pk3value=-99&categories=OKC_REPO_CONTRACT,OKC_REPO_APP_ABSTRACT');
221
222
223 CLOSE contract_csr;
224
225 resultout := 'COMPLETE:';
226 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
227 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
228 g_module || l_api_name ,
229 'Leaving OKC_REP_SIGN_WF_PVT.initialize_attributes from funcmode=RUN');
230 END IF;
231 RETURN;
232 END IF; -- (funcmode = 'RUN')
233
234
235 IF (funcmode = 'CANCEL') THEN
236 resultout := 'COMPLETE:';
237 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
239 g_module || l_api_name,
240 'Leaving OKC_REP_SIGN_WF_PVT.initialize_attributes from funcmode=CANCEL');
241 END IF;
242 RETURN;
243 END IF; -- (funcmode = 'CANCEL')
244
245 IF (funcmode = 'TIMEOUT') THEN
246 resultout := 'COMPLETE:';
247 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
249 g_module || l_api_name,
250 'Leaving OKC_REP_SIGN_WF_PVT.initialize_attributes from funcmode=TIMEOUT');
251 END IF;
252 RETURN;
253 END IF; -- (funcmode = 'TIMEOUT')
254
255 EXCEPTION
256 WHEN others THEN
257 --close cursors
258 IF (contract_csr%ISOPEN) THEN
259 CLOSE contract_csr ;
260 END IF;
261 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
262 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
263 g_module || l_api_name,
264 'Leaving OKC_REP_SIGN_WF_PVT.initialize_attributes with exceptions ' || sqlerrm);
265 END IF;
266 wf_core.context('OKC_REP_SIGN_WF_PVT',
267 'initialize_attributes',
268 itemtype,
269 itemkey,
270 to_char(actid),
271 funcmode);
272 raise;
273
274 END initialize_attributes;
275
276
277 -- Start of comments
278 --API name : has_next_signer
279 --Type : Private.
280 --Function : This procedure is called by workflow to get the next signer in the list.
281 --Pre-reqs : None.
282 --Parameters :
283 --IN : itemtype IN VARCHAR2 Required
284 -- Workflow item type parameter
285 -- : itemkey IN VARCHAR2 Required
286 -- Workflow item key parameter
287 -- : actid IN VARCHAR2 Required
288 -- Workflow actid parameter
289 -- : funcmode IN VARCHAR2 Required
290 -- Workflow function mode parameter
291 --OUT : resultout OUT VARCHAR2(1)
292 -- Workflow standard out parameter
293 -- Note :
294 -- End of comments
295
296 PROCEDURE has_next_signer(
297 itemtype IN varchar2,
298 itemkey IN varchar2,
299 actid IN number,
300 funcmode IN varchar2,
301 resultout OUT nocopy varchar2
302 ) IS
303
304
305 CURSOR get_owner is
306 SELECT owner_role from wf_items wf WHERE wf.item_type=itemtype AND wf.item_key=itemkey;
307
308 l_api_name varchar2(30);
309 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
310 l_contract_number OKC_REP_CONTRACTS_ALL.contract_number%type;
311 l_requester varchar2(4000);
312 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%type;
313 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
314 l_contract_name OKC_REP_CONTRACTS_ALL.contract_name%type;
315 l_contract_attachments varchar2(4000);
316 l_process_complete_yn varchar2(1);
317 l_user_name varchar2(4000);
318 l_role_name varchar2(4000);
319 l_role_display_name varchar2(4000);
320 l_item_key wf_items.item_key%TYPE;
321 l_notified_count number;
322 l_next_signers SIGNERS_TBL_TYPE;
323 l_contract_hdr_token VARCHAR2(50);
324 l_owner VARCHAR2(50);
325
326 /* procedure to return the signers*/
327 PROCEDURE getNextSigner
328 (p_contract_id IN NUMBER,
329 p_next_signers OUT NOCOPY SIGNERS_TBL_TYPE,
330 p_process_complete OUT NOCOPY VARCHAR2 ) IS
331
332 CURSOR cur_contacts_seq is
333 SELECT Min(SIGNATURE_SEQUENCE)
334 FROM okc_rep_party_contacts opc
335 WHERE opc.contract_id=p_contract_id
336 AND NOT EXISTS
337 (SELECT 1 FROM okc_rep_signature_details orsd, okc_rep_contracts_all orca
338 WHERE orsd.contract_id=opc.contract_id AND sequence_level=opc.SIGNATURE_SEQUENCE
339 AND orca.contract_id = orsd.contract_id
340 AND orca.contract_version_num = orsd.contract_version_num);
341
342 CURSOR cur_contacts(p_seq IN NUMBER ) is
343 SELECT contact_id,party_id,PARTY_ROLE_CODE,SIGNATURE_SEQUENCE,ESIGNATURE_TYPE
344 FROM okc_rep_party_contacts opc
345 WHERE opc.contract_id=p_contract_id
346 AND signature_sequence=p_seq;
347
348
349 CURSOR cur_supplier_user(p_contact_id IN number) IS
350 SELECT f.user_name ,(pvc.first_name || ' ' || pvc.middle_name || ' ' || pvc.last_name) contact_name
351 FROM po_vendor_contacts pvc,FND_USER f
352 where pvc.PER_PARTY_ID= f.person_party_id
353 AND pvc.vendor_contact_id=p_contact_id;
354
355 CURSOR cur_cust_user(p_contact_id IN NUMBER,p_object_id IN number) IS
356 SELECT f.user_name ,( hz.person_first_name||' '||hz.person_last_name ) contact_name FROM
357 hz_relationships hr,
358 hz_parties hz,
359 FND_USER f
360 WHERE
361 hr.party_id = p_contact_id
362 AND hr.subject_id = hz.party_id
363 AND hr.object_id = p_object_id
364 AND hr.object_type = 'ORGANIZATION'
365 AND hr.object_table_name = 'HZ_PARTIES'
366 AND hr.subject_type = 'PERSON'
367 AND hz.party_id = hr.subject_id
368 AND hr.relationship_code = 'CONTACT_OF'
369 AND hr.status = 'A'
370 AND hr.start_date <= sysdate
371 AND nvl(hr.end_date, sysdate + 1) > SYSDATE
372 AND f.person_party_id(+) = hz.party_id ;
373
374
375 CURSOR cur_int_user(p_contact_id IN NUMBER) is
376 SELECT f.user_name,p.full_name contact_name from
377 per_workforce_v p,
378 fnd_user f
379 WHERE f.employee_id(+)=p.person_id and
380 p.person_id=p_contact_id;
381
382 CURSOR cur_contract_admin IS
383 SELECT f.user_name FROM
384 OKC_REP_CONTRACTS_ALL okr ,
385 fnd_user f
386 WHERE okr.contract_id=p_contract_id
387 AND okr.owner_id=f.user_id;
388
389
390 l_user_name VARCHAR2(40);
391 l_admin_name VARCHAR2(40);
392 l_min_seq NUMBER;
393 J NUMBER:=1;
394 l_exist BOOLEAN;
395 l_subject VARCHAR2(2000);
396 l_contact_name varchar2(500);
397
398 BEGIN
399
400 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
402 'Entered into procedure getNextSigner for contract_id '||p_contract_id );
403 END IF;
404
405 /* to get the contract admin name */
406 OPEN cur_contract_admin ;
407 FETCH cur_contract_admin INTO l_admin_name;
408 CLOSE cur_contract_admin;
409
410 OPEN cur_contacts_seq ;
411 FETCH cur_contacts_seq INTO l_min_seq;
412 CLOSE cur_contacts_seq;
413
414
415 FOR i IN cur_contacts(l_min_seq) LOOP
416
417 l_exist:=FALSE;
418 l_subject:=NULL;
419
420 IF i.party_role_code='SUPPLIER_ORG' THEN
421 --
422 OPEN cur_supplier_user(i.contact_id) ;
423 FETCH cur_supplier_user INTO l_user_name,l_contact_name ;
424 CLOSE cur_supplier_user;
425
426 ELSIF i.party_role_code = 'CUSTOMER_ORG' OR i.party_role_code = 'PARTNER_ORG' THEN
427
428 OPEN cur_cust_user(i.contact_id,i.party_id) ;
429 FETCH cur_cust_user INTO l_user_name,l_contact_name;
430 CLOSE cur_cust_user;
431
432 ELSIF i.party_role_code='INTERNAL_ORG' THEN
433
434 OPEN cur_int_user(i.contact_id) ;
435 FETCH cur_int_user INTO l_user_name,l_contact_name;
436 CLOSE cur_int_user;
437
438 END IF;
439
440
441 IF Nvl(i.ESIGNATURE_TYPE,'SELF')='RECORD' THEN
442 l_user_name:= l_admin_name;
443 l_subject := '(Request for Sign on behalf of '||l_contact_name ||')' ;
444
445 END IF;
446
447 IF l_user_name IS NULL THEN
448 l_user_name:=l_admin_name;
449 l_subject := '(Request for Sign on behalf of '||l_contact_name ||')' ;
450 END IF;
451
452 p_next_signers(J).name:=l_user_name;
453 p_next_signers(J).contact_id:=i.contact_id;
454 p_next_signers(J).party_id:=i.party_id;
455 p_next_signers(J).party_role_code:=i.party_role_code;
456 p_next_signers(J).seq:=i.SIGNATURE_SEQUENCE;
457 p_next_signers(J).subject:=l_subject;
458
459 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
460 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
461 'value of p_next_signers(J).name '||p_next_signers(J).name );
462 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
463 'value of p_next_signers(J).contact_id '||p_next_signers(J).contact_id );
464 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
465 'value of p_next_signers(J).party_id '||p_next_signers(J).party_id );
466 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
467 'value of p_next_signers(J).seq '||p_next_signers(J).seq );
468 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
469 'value of p_next_signers(J).subject '||p_next_signers(J).subject );
470 END IF;
471
472 j:=j+1;
473
474 END LOOP;
475
476
477 IF l_min_seq IS NULL THEN
478 p_process_complete:='Y';
479 ELSE
480 p_process_complete:='N';
481 END IF;
482
483
484 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
485 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
486 'Leaving procedure getNextSigner for contract_id '||p_contract_id );
487 END IF;
488
489 END getNextSigner;
490
491 BEGIN
492
493 l_api_name := 'has_next_signer';
494
495
496 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
498 'Entered OKC_REP_SIGN_WF_PVT.has_next_signer');
499 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
500 'Item Type is: ' || itemtype);
501 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
502 'Item Key is: ' || itemkey);
503 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
504 'actid is: ' || to_char(actid));
505 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
506 'Function mode is: ' || funcmode);
507 END IF;
508 IF (funcmode = 'RUN') then
509 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
511 g_module || l_api_name,
512 'Calling wf_engine.GetItemAttrNumber to get CONTRACT_ID');
513 END IF;
514
515 l_contract_id := wf_engine.GetItemAttrNumber(
516 itemtype => itemtype,
517 itemkey => itemkey,
518 aname => 'CONTRACT_ID');
519
520 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
521 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
522 g_module || l_api_name,
523 'Contract Id is: ' || to_char(l_contract_id));
524 END IF;
525
526 IF l_next_signers.Count<>0 then
527 l_next_signers.DELETE;
528 END IF;
529
530 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
531 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
532 g_module || l_api_name,
533 'Calling getNextSigner to get the signers list ');
534 END IF;
535
536 getNextSigner(
537 p_contract_id=> l_contract_id,
538 p_next_signers=>l_next_signers ,
539 p_process_complete=>l_process_complete_yn
540 );
541
542
543 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
544 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
545 g_module || l_api_name,
546 'Number of signers : ' || to_char(l_next_signers.count));
547 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
548 g_module || l_api_name,
549 'l_process_complete_yn: ' || l_process_complete_yn);
550 END IF;
551
552 IF (l_process_complete_yn = 'Y') THEN
553 resultout := 'COMPLETE:F';
554 ELSE
555 resultout := 'COMPLETE:T';
556 END IF;
557
558 IF (l_next_signers.count > 0) THEN
559 l_contract_number := wf_engine.GetItemAttrText(
560 itemtype => itemtype,
561 itemkey => itemkey,
562 aname => 'CONTRACT_NUMBER');
563 l_requester := wf_engine.GetItemAttrText(
564 itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'REQUESTER');
567 l_contract_name := wf_engine.GetItemAttrText(
568 itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'CONTRACT_NAME');
571 l_contract_version := wf_engine.GetItemAttrNumber(
572 itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'CONTRACT_VERSION');
575 l_contract_type := wf_engine.GetItemAttrText(
576 itemtype => itemtype,
577 itemkey => itemkey,
578 aname => 'CONTRACT_TYPE');
579 l_contract_hdr_token := wf_engine.GetItemAttrText(
580 itemtype => itemtype,
581 itemkey => itemkey,
582 aname => 'CONTRACT_HDR_TOKEN');
583
584
585 OPEN get_owner;
586 FETCH get_owner INTO l_owner;
587 CLOSE get_owner;
588
589 l_notified_count := wf_engine.GetItemAttrNumber(
590 itemtype => itemtype,
591 itemkey => itemkey,
592 aname => 'SIGN_COUNTER');
593
594 FOR i IN l_next_signers.first..l_next_signers.last LOOP
595 IF l_next_signers.exists(i) THEN
596 l_user_name := l_next_signers(i).name;
597
598 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
599 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
600 g_module || l_api_name,
601 'User name for role is : ' || l_user_name);
602 END IF;
603
604 l_notified_count := l_notified_count + 1;
605 l_item_key := itemkey || '_' || to_char(l_notified_count);
606
607 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
608 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
609 'Calling WF_ENGINE.createprocess for Notification');
610 END IF;
611
612 WF_ENGINE.createprocess (
613 itemtype => itemtype,
614 itemkey => l_item_key,
615 process => G_SIGN_NOTIF_PROCESS);
616
617 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
618 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
619 'Calling WF_ENGINE.SetItemOwner for Notification Process '||l_owner);
620 END IF;
621
622 WF_ENGINE.SetItemOwner (
623 itemtype => itemtype,
624 itemkey => l_item_key,
625 owner => l_owner);
626
627 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
629 'Setting Notification Process Signer to: ' || l_user_name);
630 END IF;
631
632 WF_ENGINE.SetItemAttrText (
633 itemtype => itemtype,
634 itemkey => l_item_key,
635 aname => 'SIGNER',
636 avalue => l_user_name);
637
638 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
639 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
640 'Setting current sequence id to: ' || l_next_signers(i).seq );
641 END IF;
642
643 wf_engine.SetItemAttrNumber(
644 itemtype => itemtype,
645 itemkey => l_item_key,
646 aname => 'CURRENT_SEQUENCE_ID',
647 avalue => l_next_signers(i).seq);
648
649 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
651 'Setting current contact id: ' || l_next_signers(i).contact_id );
652 END IF;
653
654
655 wf_engine.SetItemAttrNumber(
656 itemtype => itemtype,
657 itemkey => l_item_key,
658 aname => 'CURRENT_CONTACT_ID',
659 avalue => l_next_signers(i).contact_id);
660
661 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
663 'Setting current party id: ' || l_next_signers(i).party_id );
664 END IF;
665
666
667 wf_engine.SetItemAttrNumber(
668 itemtype => itemtype,
669 itemkey => l_item_key,
670 aname => 'CURRENT_PARTY_ID',
671 avalue => l_next_signers(i).party_id);
672
673 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
674 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
675 'Setting current party role code : ' || l_next_signers(i).party_role_code );
676 END IF;
677
678 wf_engine.SetItemAttrText(
679 itemtype => itemtype,
680 itemkey => l_item_key,
681 aname => 'CURRENT_PARTY_ROLE_CODE',
682 avalue => l_next_signers(i).party_role_code);
683
684
685 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
686 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
687 'Setting Notification Process Contract Id to: ' || l_contract_id);
688 END IF;
689
690 WF_ENGINE.SetItemAttrNumber (
691 itemtype => itemtype,
692 itemkey => l_item_key,
693 aname => 'CONTRACT_ID',
694 avalue => l_contract_id);
695
696
697 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
698 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
699 'Setting Notification Process Contract Header Token to: ' || l_contract_hdr_token);
700 END IF;
701
702 WF_ENGINE.SetItemAttrText (
703 itemtype => itemtype,
704 itemkey => l_item_key,
705 aname => 'CONTRACT_HDR_TOKEN',
706 avalue => l_contract_hdr_token);
707
708
709 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
710 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
711 'Setting Notification Process Contract Name: ' || l_contract_name);
712 END IF;
713
714 WF_ENGINE.SetItemAttrText (
715 itemtype => itemtype,
716 itemkey => l_item_key,
717 aname => 'CONTRACT_NAME',
718 avalue => l_contract_name);
719
720 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
722 'Setting Notification Process Contract Version: ' || l_contract_version);
723 END IF;
724
725 WF_ENGINE.SetItemAttrNumber (
726 itemtype => itemtype,
727 itemkey => l_item_key,
728 aname => 'CONTRACT_VERSION',
729 avalue => l_contract_version);
730
731 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
732 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
733 'Setting Notification Process Contract Type: ' || l_contract_type);
734 END IF;
735
736 WF_ENGINE.SetItemAttrText (
737 itemtype => itemtype,
738 itemkey => l_item_key,
739 aname => 'CONTRACT_TYPE',
740 avalue => l_contract_type);
741
742 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
743 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
744 'Setting Notification Process Contract Number: ' || l_contract_number);
745 END IF;
746
747 WF_ENGINE.SetItemAttrText (
748 itemtype => itemtype,
749 itemkey => l_item_key,
750 aname => 'CONTRACT_NUMBER',
751 avalue => l_contract_number);
752
753 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
755 'Setting Notification Process Requester: ' || l_requester);
756 END IF;
757
758 WF_ENGINE.SetItemAttrText (
759 itemtype => itemtype,
760 itemkey => l_item_key,
761 aname => 'REQUESTER',
762 avalue => l_requester);
763
764 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
765 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
766 'Setting Notification Process Subject to : ' || l_next_signers(i).SUBJECT);
767 END IF;
768
769 WF_ENGINE.SetItemAttrText (
770 itemtype => itemtype,
771 itemkey => l_item_key,
772 aname => 'SUBJECT',
773 avalue => l_next_signers(i).SUBJECT);
774
775
776 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
777 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
778 'Setting Notification Process Contract Attachment');
779 END IF;
780
781
782
783 WF_ENGINE.SetItemAttrText (
784 itemtype => itemtype,
785 itemkey => itemkey,
786 aname => 'CONTRACT_ATTACHMENTS',
787 avalue => 'FND:entity=OKC_CONTRACT_DOCS&pk1name=BusinessDocumentType&pk1value='||l_contract_type
788 ||'&pk2name=BusinessDocumentId&pk2value='||l_contract_id
789 ||'&pk3name=BusinessDocumentVersion&pk3value=-99&categories=OKC_REPO_CONTRACT,OKC_REPO_APP_ABSTRACT');
790
791
792
793 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
794 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
795 'Setting Notification Process Master Item Key to: ' || itemkey);
796 END IF;
797
798 WF_ENGINE.SetItemAttrText (
799 itemtype => itemtype,
800 itemkey => l_item_key,
801 aname => 'MASTER_ITEM_KEY',
802 avalue => itemkey);
803
804 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
806 'Starting Notification Process with item type '||itemtype ||'itemkey '||l_item_key );
807 END IF;
808
809 wf_engine.startProcess(
810 itemtype => itemtype,
811 itemkey => l_item_key);
812 END IF; -- l_next_signers.exists(i)
813
814 END LOOP;
815
816 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
818 'Calling WF_ENGINE.setitemattrnumber for SIGNER_COUNTER: ' || l_notified_count);
819 END IF;
820
821
822 WF_ENGINE.SetItemAttrNumber (
823 itemtype => itemtype,
824 itemkey => itemkey,
825 aname => 'SIGN_COUNTER',
826 avalue => l_notified_count);
827
828 END IF; -- (l_next_signer.count > 0)
829
830 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
831 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
832 g_module || l_api_name,
833 'Leaving OKC_REP_SIGN_WF_PVT.has_next_signer from funcmode=RUN');
834 END IF;
835 RETURN;
836 END IF; -- (funcmode = 'RUN')
837
838
839 IF (funcmode = 'CANCEL') THEN
840 resultout := 'COMPLETE:';
841 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
842 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
843 g_module || l_api_name,
844 'Leaving OKC_REP_SIGN_WF_PVT.has_next_signer from funcmode=CANCEL');
845 END IF;
846 RETURN;
847 END IF; -- (funcmode = 'CANCEL')
848
849 IF (funcmode = 'TIMEOUT') THEN
850 resultout := 'COMPLETE:';
851 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
852 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
853 g_module || l_api_name,
854 'Leaving OKC_REP_SIGN_WF_PVT.has_next_signer from funcmode=TIMEOUT');
855 END IF;
856 RETURN;
857 END IF; -- (funcmode = 'TIMEOUT')
858
859 EXCEPTION
860 WHEN others THEN
861 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
862 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
863 g_module || l_api_name,
864 'Leaving OKC_REP_SIGN_WF_PVT.has_next_signer with exceptions ' || sqlerrm);
865 END IF;
866 wf_core.context('OKC_REP_SIGN_WF_PVT',
867 'has_next_signer',
868 itemtype,
869 itemkey,
870 to_char(actid),
871 funcmode);
872 raise;
873
874 END has_next_signer;
875
876
877 -- Start of comments
878 --API name : update_hist_status_detailed
879 --Type : Private.
880 --Function : This procedure is called by workflow to update the signature history when the notification is responded
881 --Pre-reqs : None.
882 --Parameters :
883 --IN : itemtype IN VARCHAR2 Required
884 -- Workflow item type parameter
885 -- : itemkey IN VARCHAR2 Required
886 -- Workflow item key parameter
887 -- : actid IN VARCHAR2 Required
888 -- Workflow actid parameter
889 -- : funcmode IN VARCHAR2 Required
890 -- Workflow function mode parameter
891 --OUT : resultout OUT VARCHAR2(1)
892 -- Workflow standard out parameter
893 -- Note :
894 -- End of comments
895
896 PROCEDURE update_hist_status_detailed(
897 itemtype IN varchar2,
898 itemkey IN varchar2,
899 actid IN number,
900 funcmode IN varchar2,
901 resultout OUT nocopy varchar2
902 ) IS
903
904 l_contract_id okc_rep_signature_details.contract_id%type;
905 l_contract_version okc_rep_signature_details.contract_version_num%type;
906 l_signer_id number;
907 l_sign_status VARCHAR2(30);
908 l_recipient_name FND_USER.user_name%type;
909 l_action_code okc_rep_signature_details.action_code%type;
910 l_wf_note VARCHAR2(2000);
911 l_api_name VARCHAR2(30);
912 l_return_status VARCHAR2(1);
913 l_msg_count NUMBER;
914 l_msg_data VARCHAR2(2000);
915 l_process_complete_yn varchar2(1);
916 l_action_code_fwd VARCHAR2(250);
917 l_recipient_id NUMBER;
918
919
920 CURSOR notif_csr (p_notification_id NUMBER) IS
921 SELECT fu.user_id user_id, fu.user_name user_name,
922 fu1.user_id original_user_id,fu1.user_name original_user_name
923 FROM fnd_user fu, wf_notifications wfn, fnd_user fu1
924 WHERE fu.user_name = wfn.recipient_role
925 AND fu1.user_name = wfn.original_recipient
926 AND wfn.notification_id = p_notification_id ;
927
928 notif_rec notif_csr%ROWTYPE;
929 l_sequence_id NUMBER;
930 l_master_key wf_items.item_key%TYPE;
931 l_contact_id NUMBER;
932 l_party_id NUMBER;
933 l_party_role_code VARCHAR2(50);
934
935 l_control_number VARCHAR2(150);
936 l_contract_type VARCHAR2(30);
937 l_org_id NUMBER;
938 l_upd_cont_num_suc_yn VARCHAR2(1) := 'Y';
939 l_exist_id NUMBER;
940
941 CURSOR wf_process_csr(p_master_key IN VARCHAR2) IS
942 SELECT Count(item_key) FROM wf_items
943 WHERE item_type=itemtype
944 AND item_key like p_master_key || '!_' || '%' ESCAPE '!'
945 and end_date is null;
946
947 l_cnt_prc NUMBER;
948
949 CURSOR c_get_contract_details_csr (p_contract_id NUMBER) IS
950 SELECT CONTRACT_TYPE, org_id
951 FROM OKC_REP_CONTRACTS_ALL
952 WHERE contract_id = p_contract_id;
953
954 BEGIN
955
956 l_api_name := 'update_hist_status_detailed';
957
958 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
959 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
960 'Entered OKC_REP_SIGN_WF_PVT.update_hist_status_detailed');
961 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
962 'Item Type is: ' || itemtype);
963 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
964 'Item Key is: ' || itemkey);
965 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
966 'actid is: ' || to_char(actid));
967 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
968 'Function mode is: ' || funcmode);
969 END IF;
970
971 -- Get contract id and version Fs
972 l_contract_id := wf_engine.GetItemAttrNumber(
973 itemtype => itemtype,
974 itemkey => itemkey,
975 aname => 'CONTRACT_ID');
976 l_sequence_id := wf_engine.GetItemAttrNumber(
977 itemtype => itemtype,
978 itemkey => itemkey,
979 aname => 'CURRENT_SEQUENCE_ID');
980
981 l_contact_id:= wf_engine.GetItemAttrNumber(
982 itemtype => itemtype,
983 itemkey => itemkey,
984 aname => 'CURRENT_CONTACT_ID');
985
986 l_party_id:= wf_engine.GetItemAttrNumber(
987 itemtype => itemtype,
988 itemkey => itemkey,
989 aname => 'CURRENT_PARTY_ID');
990
991 l_party_role_code:= wf_engine.GetItemAttrText(
992 itemtype => itemtype,
993 itemkey => itemkey,
994 aname => 'CURRENT_PARTY_ROLE_CODE');
995 l_contract_version := wf_engine.GetItemAttrNumber(
996 itemtype => itemtype,
997 itemkey => itemkey,
998 aname => 'CONTRACT_VERSION');
999 -- Get the signer comments
1000 l_wf_note := WF_NOTIFICATION.GetAttrText(
1001 nid => WF_ENGINE.context_nid,
1002 aname => 'WF_NOTE');
1003 -- Get the sign status
1004 l_SIGN_status := WF_NOTIFICATION.GetAttrText(
1005 nid => WF_ENGINE.context_nid,
1006 aname => 'RESULT');
1007
1008 OPEN c_get_contract_details_csr(l_contract_id);
1009 FETCH c_get_contract_details_csr INTO l_contract_type , l_org_id;
1010 CLOSE c_get_contract_details_csr;
1011
1012 IF(l_contract_type = 'REP_SBCR') THEN
1013
1014 l_control_number := WF_NOTIFICATION.GetAttrText(
1015 nid => WF_ENGINE.context_nid,
1016 aname => 'CONTROL_NUMBER');
1017
1018 IF (l_control_number IS NOT NULL) THEN
1019
1020 BEGIN
1021 SELECT contract_id INTO l_exist_id FROM okc_rep_contracts_all orca
1022 WHERE orca.contract_type = l_contract_type
1023 AND org_id = l_org_id
1024 AND contract_number = l_control_number
1025 AND contract_id <> l_contract_id;
1026
1027 l_control_number := NULL;
1028
1029 EXCEPTION
1030 WHEN No_Data_Found THEN
1031 UPDATE okc_rep_contracts_all SET contract_number = l_control_number
1032 WHERE contract_id = l_contract_id
1033 AND contract_version_num = 1;
1034
1035 END;
1036
1037 IF (l_exist_id IS NOT null) THEN
1038 l_upd_cont_num_suc_yn := 'N';
1039 fnd_message.set_name('OKC', 'OKC_WF_INV_CONTROL_NUM');
1040 app_exception.raise_exception;
1041
1042 ELSE
1043 l_upd_cont_num_suc_yn := 'Y';
1044 END IF; --l_exist_id IS NOT null
1045 ELSE
1046 l_upd_cont_num_suc_yn := 'Y';
1047 END IF; --l_control_number IS NOT NULL
1048 END If; --l_contract_type = 'REP_SBCR'
1049
1050 IF(l_upd_cont_num_suc_yn = 'Y') THEN
1051
1052 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1054 g_module || l_api_name,
1055 'Contract Id is: ' || to_char(l_contract_id));
1056 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1057 g_module || l_api_name,
1058 'Contract Version is: ' || to_char(l_contract_version));
1059 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1060 g_module || l_api_name,
1061 'Signer Notes : ' || l_wf_note);
1062 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1063 g_module || l_api_name,
1064 'Signer action is : ' || l_sign_status);
1065 END IF;
1066
1067
1068
1069 -- Get the notification recipient
1070 OPEN notif_csr(WF_ENGINE.context_nid);
1071 FETCH notif_csr into notif_rec;
1072 IF(notif_csr%NOTFOUND) THEN
1073 RAISE NO_DATA_FOUND;
1074 END IF;
1075
1076 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1077 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1078 g_module || l_api_name,
1079 'Signer Name is : ' || notif_rec.user_name);
1080 END IF;
1081
1082
1083 -- FUNCTION MODE IS RESPOND.
1084 IF (funcmode = 'RESPOND') THEN
1085 -- CURRENT Signer signed THE CONTRACTS
1086 IF (l_sign_status = G_WF_STATUS_SIGNED) THEN
1087 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1088 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1089 g_module || l_api_name,
1090 'Signer action is : ' || G_WF_STATUS_SIGNED);
1091 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1092 g_module || l_api_name,
1093 'Calling OKC_REP_UTIL_PVT.add_signature_hist_record');
1094 END IF;
1095 OKC_REP_UTIL_PVT.add_signature_hist_record(
1096 p_api_version => 1.0,
1097 p_init_msg_list => FND_API.G_FALSE,
1098 p_contract_id => l_contract_id,
1099 p_sequence_id => l_sequence_id,
1100 p_contact_id => l_contact_id ,
1101 p_party_id => l_party_id,
1102 p_party_role_code => l_party_role_code,
1103 p_contract_version => l_contract_version,
1104 p_action_code => G_WF_STATUS_SIGNED,
1105 p_user_id => notif_rec.user_id,
1106 p_note => l_wf_note,
1107 x_msg_data => l_msg_data,
1108 x_msg_count => l_msg_count,
1109 x_return_status => l_return_status);
1110 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1111 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1112 g_module || l_api_name,
1113 'Completed OKC_REP_UTIL_PVT.add_signature_hist_record with return status: ' || l_return_status);
1114 END IF;
1115
1116 -------------------------------------------------------
1117 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1118 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1119 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1120 RAISE OKC_API.G_EXCEPTION_ERROR;
1121 END IF;
1122 --------------------------------------------------------
1123
1124
1125 -- CURRENT Signer rejected THE CONTRACTS
1126 ELSIF (l_sign_status = G_WF_STATUS_REJECTED) THEN
1127
1128 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1130 g_module || l_api_name,
1131 'Signer action is : ' || G_WF_STATUS_REJECTED);
1132 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1133 g_module || l_api_name,
1134 'Calling OKC_REP_UTIL_PVT.add_signature_hist_record');
1135 END IF;
1136 OKC_REP_UTIL_PVT.add_signature_hist_record(
1137 p_api_version => 1.0,
1138 p_init_msg_list => FND_API.G_FALSE,
1139 p_contract_id => l_contract_id,
1140 p_sequence_id => l_sequence_id,
1141 p_contact_id => l_contact_id ,
1142 p_party_id => l_party_id,
1143 p_party_role_code => l_party_role_code,
1144 p_contract_version => l_contract_version,
1145 p_action_code => G_STATUS_REJECTED,
1146 p_user_id => notif_rec.user_id,
1147 p_note => l_wf_note,
1148 x_msg_data => l_msg_data,
1149 x_msg_count => l_msg_count,
1150 x_return_status => l_return_status);
1151 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1152 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1153 g_module || l_api_name,
1154 'Completed OKC_REP_UTIL_PVT.add_signature_hist_record with return status: ' || l_return_status);
1155 END IF;
1156 -------------------------------------------------------
1157 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1159 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1160 RAISE OKC_API.G_EXCEPTION_ERROR;
1161 END IF;
1162 --------------------------------------------------------
1163 END IF;
1164
1165
1166
1167 l_master_key := wf_engine.GetItemAttrText(
1168 itemtype => itemtype,
1169 itemkey => itemkey,
1170 aname => 'MASTER_ITEM_KEY');
1171 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1172 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1173 g_module || l_api_name,
1174 'Master Item Key is: ' || l_master_key);
1175 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1176 'Completing master process waiting activity');
1177 END IF;
1178
1179 --set attribute contract number to the changed number
1180 IF(l_control_number IS NOT NULL) THEN
1181
1182 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1183 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1184 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_NUMBER ' || l_control_number);
1185 END IF;
1186
1187 WF_ENGINE.SetItemAttrText (
1188 itemtype => itemtype,
1189 itemkey => l_master_key,
1190 aname => 'CONTRACT_NUMBER',
1191 avalue => l_control_number);
1192
1193 WF_ENGINE.SetItemAttrText (
1194 itemtype => itemtype,
1195 itemkey => itemkey,
1196 aname => 'CONTRACT_NUMBER',
1197 avalue => l_control_number);
1198
1199 END IF; --l_control_number IS NOT NULL
1200
1201 OPEN wf_process_csr(l_master_key);
1202 FETCH wf_process_csr INTO l_cnt_prc ;
1203 CLOSE wf_process_csr;
1204
1205 IF (l_cnt_prc-1) <>0 THEN
1206
1207
1208 IF l_sign_status = G_WF_STATUS_REJECTED THEN
1209
1210 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1211 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1212 g_module || l_api_name,
1213 'Complete the activity WAIT_FOR_SIGNER_RESPONSE with l_sign_status : ' || l_sign_status);
1214 END IF;
1215
1216 wf_engine.CompleteActivity(
1217 itemtype => itemtype,
1218 itemkey => To_Char(l_master_key),
1219 activity => 'WAIT_FOR_SIGNER_RESPONSE',
1220 result => l_sign_status );
1221 END IF;
1222
1223 ELSE
1224
1225 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1226 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1227 g_module || l_api_name,
1228 'Complete the activity WAIT_FOR_SIGNER_RESPONSE with l_sign_status : ' || l_sign_status);
1229 END IF;
1230
1231 wf_engine.CompleteActivity(
1232 itemtype => itemtype,
1233 itemkey => To_Char(l_master_key),
1234 activity => 'WAIT_FOR_SIGNER_RESPONSE',
1235 result => l_sign_status );
1236
1237
1238
1239 END IF;
1240
1241 CLOSE notif_csr;
1242
1243 END IF; -- (funcmode = 'RESPOND')
1244
1245
1246 IF (funcmode = 'RUN') THEN
1247 IF (l_sign_status = G_WF_STATUS_SIGNED) THEN
1248 resultout := 'COMPLETE:' || G_WF_STATUS_SIGNED;
1249 ELSIF (l_sign_status = G_WF_STATUS_REJECTED) THEN
1250 resultout := 'COMPLETE:' || G_WF_STATUS_REJECTED;
1251 ELSIF (l_sign_status = G_WF_STATUS_MORE_APPROVERS) THEN
1252 resultout := 'COMPLETE:' || G_WF_STATUS_MORE_APPROVERS;
1253 ELSE resultout := 'COMPLETE:';
1254 END IF;
1255 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1256 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1257 g_module || l_api_name,
1258 'Leaving OKC_REP_SIGN_WF_PVT.update_hist_status_detailed from funcmode=RUN');
1259 END IF;
1260 CLOSE notif_csr;
1261 --RETURN;
1262 END IF; -- (funcmode = 'RUN')
1263
1264 IF (funcmode = 'TIMEOUT') THEN
1265 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1266 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_module || l_api_name,
1267 'In OKC_REP_SIGN_WF_PVT.update_hist_status_detailed funcmode=TIMEOUT');
1268 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,g_module || l_api_name,
1269 'Calling OKC_REP_UTIL_PVT.add_signature_hist_record');
1270 END IF;
1271 OKC_REP_UTIL_PVT.add_signature_hist_record(
1272 p_api_version => 1.0,
1273 p_init_msg_list => FND_API.G_FALSE,
1274 p_contract_id => l_contract_id,
1275 p_sequence_id => l_sequence_id,
1276 p_contact_id => l_contact_id ,
1277 p_party_id => l_party_id,
1278 p_party_role_code => l_party_role_code,
1279 p_contract_version => l_contract_version,
1280 p_action_code => G_STATUS_TIMEOUT,
1281 p_user_id => notif_rec.user_id,
1282 p_note => l_wf_note,
1283 x_msg_data => l_msg_data,
1284 x_msg_count => l_msg_count,
1285 x_return_status => l_return_status);
1286
1287 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1288 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1289 g_module || l_api_name,
1290 'Completed OKC_REP_UTIL_PVT.add_signature_hist_record with return status: ' || l_return_status);
1291 END IF;
1292 -------------------------------------------------------
1293 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1294 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1295 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1296 RAISE OKC_API.G_EXCEPTION_ERROR;
1297 END IF;
1298 --------------------------------------------------------
1299
1300 -- resultout := 'COMPLETE:';
1301 CLOSE notif_csr;
1302 RETURN;
1303 END IF; -- (funcmode = 'TIMEOUT')
1304
1305 END IF; --l_upd_cont_num_suc_yn = 'Y'
1306
1307 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1308 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1309 g_module || l_api_name,
1310 'Leaving OKC_REP_SIGN_WF_PVT.update_hist_status_detailed');
1311 END IF;
1312
1313
1314 EXCEPTION
1315
1316 when others then
1317 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1318 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1319 g_module || l_api_name,
1320 '618: Leaving OKC_REP_SIGN_WF_PVT.update_hist_status_detailed with exceptions ' || sqlerrm);
1321 END IF;
1322 --close cursors
1323 IF (notif_csr%ISOPEN) THEN
1324 CLOSE notif_csr ;
1325 END IF;
1326 wf_core.context('OKC_REP_SIGN_WF_PVT',
1327 'update_hist_status_detailed',
1328 itemtype,
1329 itemkey,
1330 to_char(actid),
1331 funcmode);
1332 raise;
1333 END update_hist_status_detailed;
1334
1335 -- Start of comments
1336 --API name : sign_contract
1337 --Type : Private.
1338 --Function : This procedure is called by workflow after the contract is signed. Updates Contract's status
1339 -- to Signed and logs the status change in OKC_REP_CON_STATUS_HIST table.
1340 --Pre-reqs : None.
1341 --Parameters :
1342 --IN : itemtype IN VARCHAR2 Required
1343 -- Workflow item type parameter
1344 -- : itemkey IN VARCHAR2 Required
1345 -- Workflow item key parameter
1346 -- : actid IN VARCHAR2 Required
1347 -- Workflow actid parameter
1348 -- : funcmode IN VARCHAR2 Required
1349 -- Workflow function mode parameter
1350 --OUT : resultout OUT VARCHAR2(1)
1351 -- Workflow standard out parameter
1352 -- Note :
1353 -- End of comments
1354 PROCEDURE sign_contract(
1355 itemtype IN varchar2,
1356 itemkey IN varchar2,
1357 actid IN number,
1358 funcmode IN varchar2,
1359 resultout OUT nocopy varchar2
1360 ) IS
1361
1362 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
1363 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
1364 l_api_name VARCHAR2(30);
1365 l_return_status VARCHAR2(1);
1366 l_msg_count NUMBER;
1367 l_msg_data VARCHAR2(2000);
1368 l_activate_event_tbl EVENT_TBL_TYPE;
1369 l_update_event_tbl EVENT_TBL_TYPE;
1370 l_sync_flag VARCHAR2(1);
1371 l_expiration_date_matches_flag VARCHAR2(1);
1372 l_effective_date_matches_flag VARCHAR2(1);
1373 l_prev_signed_expiration_date OKC_REP_CONTRACTS_ALL.CONTRACT_EXPIRATION_DATE%TYPE;
1374 l_prev_signed_effective_date OKC_REP_CONTRACTS_ALL.CONTRACT_EXPIRATION_DATE%TYPE;
1375
1376
1377
1378 CURSOR contract_csr(p_contract_id NUMBER) IS
1379 SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date
1380 FROM OKC_REP_CONTRACTS_ALL
1381 WHERE contract_id = p_contract_id;
1382
1383 CURSOR arch_contract_csr (l_contract_version NUMBER,p_contract_id number) IS
1384 SELECT contract_effective_date, contract_expiration_date
1385 FROM OKC_REP_CONTRACT_VERS
1386 WHERE contract_id = p_contract_id
1387 AND contract_version_num = l_contract_version;
1388
1389 contract_rec contract_csr%ROWTYPE;
1390 arch_contract_rec arch_contract_csr%ROWTYPE;
1391
1392
1393 BEGIN
1394
1395 l_api_name := 'sign_contract';
1396
1397 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1398 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1399 'Entered OKC_REP_SIGN_WF_PVT.sign_contract');
1400 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1401 'Item Type is: ' || itemtype);
1402 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1403 'Item Key is: ' || itemkey);
1404 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1405 'actid is: ' || to_char(actid));
1406 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1407 'Function mode is: ' || funcmode);
1408 END IF;
1409 IF (funcmode = 'RUN') THEN
1410 l_contract_id := wf_engine.GetItemAttrNumber(
1411 itemtype => itemtype,
1412 itemkey => itemkey,
1413 aname => 'CONTRACT_ID');
1414 l_contract_version := wf_engine.GetItemAttrNumber(
1415 itemtype => itemtype,
1416 itemkey => itemkey,
1417 aname => 'CONTRACT_VERSION');
1418 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1419 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1420 g_module || l_api_name,
1421 'Contract Id is: ' || to_char(l_contract_id));
1422 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1423 g_module || l_api_name,
1424 'Contract Version is: ' || to_char(l_contract_version));
1425 END IF;
1426 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1428 'Calling OKC_REP_UTIL_PVT.change_contract_status');
1429 END IF;
1430 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
1431 OKC_REP_UTIL_PVT.change_contract_status(
1432 p_api_version => 1.0,
1433 p_init_msg_list => FND_API.G_FALSE,
1434 p_contract_id => l_contract_id,
1435 p_contract_version => l_contract_version,
1436 p_status_code => G_STATUS_SIGNED,
1437 p_user_id => fnd_global.user_id,
1438 p_note => NULL,
1439 x_msg_data => l_msg_data,
1440 x_msg_count => l_msg_count,
1441 x_return_status => l_return_status);
1442 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1444 g_module || l_api_name,
1445 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || l_return_status);
1446 END IF;
1447 -----------------------------------------------------
1448 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1449 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1450 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1451 RAISE OKC_API.G_EXCEPTION_ERROR;
1452 END IF;
1453 --------------------------------------------------------
1454
1455 -- Get effective dates and version of the contract.
1456 OPEN contract_csr(l_contract_id);
1457 FETCH contract_csr INTO contract_rec;
1458
1459 -- We need to first version the deliverables
1460 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1461 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1462 'Calling OKC_DELIVERABLE_PROCESS_PVT.version_deliverables');
1463 END IF;
1464 OKC_DELIVERABLE_PROCESS_PVT.version_deliverables (
1465 p_api_version => 1.0,
1466 p_init_msg_list => FND_API.G_FALSE,
1467 p_doc_id => l_contract_id,
1468 p_doc_version => contract_rec.contract_version_num,
1469 p_doc_type => contract_rec.contract_type,
1470 x_return_status => l_return_status,
1471 x_msg_count => l_msg_count,
1472 x_msg_data => l_msg_data
1473 );
1474 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1475 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1476 'OKC_DELIVERABLE_PROCESS_PVT.version_deliverables return status is : '
1477 || l_return_status);
1478 END IF;
1479 -----------------------------------------------------
1480 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1481 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1482 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1483 RAISE OKC_API.G_EXCEPTION_ERROR;
1484 END IF;
1485 --------------------------------------------------------
1486
1487 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1488 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1489 'Latest signed version number is : '
1490 || contract_rec.latest_signed_ver_number);
1491 END IF;
1492 -- Now we need to activate deliverables
1493 if (contract_rec.latest_signed_ver_number IS NULL) THEN
1494 l_sync_flag := FND_API.G_FALSE;
1495 ELSE
1496 l_sync_flag := FND_API.G_TRUE;
1497 END IF;
1498
1499 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1500 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1501 'l_sync_flag is : ' || l_sync_flag);
1502 END IF;
1503 l_activate_event_tbl(1).event_code := G_CONTRACT_EXPIRE_EVENT;
1504 l_activate_event_tbl(1).event_date := contract_rec.contract_expiration_date;
1505
1506 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1507 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1508 'Calling OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
1509 END IF;
1510
1511 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
1512 p_api_version => 1.0,
1513 p_init_msg_list => FND_API.G_FALSE,
1514 p_commit => FND_API.G_FALSE,
1515 p_bus_doc_id => l_contract_id,
1516 p_bus_doc_type => contract_rec.contract_type,
1517 p_bus_doc_version => contract_rec.contract_version_num,
1518 p_event_code => G_CONTRACT_EFFECTIVE_EVENT,
1519 p_event_date => contract_rec.contract_effective_date,
1520 p_sync_flag => l_sync_flag,
1521 p_bus_doc_date_events_tbl => l_activate_event_tbl,
1522 x_msg_data => l_msg_data,
1523 x_msg_count => l_msg_count,
1524 x_return_status => l_return_status);
1525
1526 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1527 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1528 'OKC_DELIVERABLE_PROCESS_PVT.activateDeliverables return status is : '
1529 || l_return_status);
1530 END IF;
1531 -----------------------------------------------------
1532 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1533 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1534 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1535 RAISE OKC_API.G_EXCEPTION_ERROR;
1536 END IF;
1537 --------------------------------------------------------
1538
1539 -- Checking if we need to call deliverable's APIs for synch-ing
1540 IF (l_sync_flag = FND_API.G_TRUE) THEN
1541 -- Get the previous signed contract's expiration date
1542 -- Get effective dates and version of the contract.
1543 OPEN arch_contract_csr(contract_rec.latest_signed_ver_number,l_contract_id);
1544 FETCH arch_contract_csr INTO arch_contract_rec;
1545 IF(contract_csr%NOTFOUND) THEN
1546 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1547 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1548 G_MODULE||l_api_name,
1549 'Invalid Contract Id: '|| l_contract_id);
1550 END IF;
1551 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1552 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
1553 p_token1 => G_CONTRACT_ID_TOKEN,
1554 p_token1_value => to_char(l_contract_id));
1555 RAISE FND_API.G_EXC_ERROR;
1556 -- RAISE NO_DATA_FOUND;
1557 END IF;
1558 l_prev_signed_effective_date := arch_contract_rec.contract_effective_date;
1559 l_prev_signed_expiration_date := arch_contract_rec.contract_expiration_date;
1560
1561 CLOSE arch_contract_csr;
1562 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1563 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1564 'Before checking if we need to call updateDeliverable and disableDeliverable()');
1565 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1566 'Prev signed expiration date: ' || trunc(l_prev_signed_expiration_date));
1567 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1568 'Current version expiration date: ' || trunc(contract_rec.contract_expiration_date));
1569 END IF;
1570 l_update_event_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
1571 l_update_event_tbl(1).event_date := contract_rec.contract_effective_date;
1572 l_update_event_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
1573 l_update_event_tbl(2).event_date := contract_rec.contract_expiration_date;
1574 -- If last signed version's expiration date is different from the current version's expiration date
1575 -- we need to call deliverables API for synching previous signed deliverables.
1576 -- This logic is executed to handle the null date scenarios
1577 IF (trunc(l_prev_signed_expiration_date)=trunc(contract_rec.contract_expiration_date)) THEN
1578 l_expiration_date_matches_flag := FND_API.G_TRUE;
1579 END IF;
1580
1581 IF (trunc(l_prev_signed_effective_date)=trunc(contract_rec.contract_effective_date)) THEN
1582 l_effective_date_matches_flag := FND_API.G_TRUE;
1583 END IF;
1584
1585 IF ((l_expiration_date_matches_flag = FND_API.G_FALSE ) OR (l_effective_date_matches_flag = FND_API.G_FALSE)) THEN
1586 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1587 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1588 'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
1589 END IF;
1590 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
1591 p_api_version => 1.0,
1592 p_init_msg_list => FND_API.G_FALSE,
1593 p_commit => FND_API.G_FALSE,
1594 p_bus_doc_id => l_contract_id,
1595 p_bus_doc_type => contract_rec.contract_type,
1596 p_bus_doc_version => contract_rec.contract_version_num,
1597 p_bus_doc_date_events_tbl => l_update_event_tbl,
1598 x_msg_data => l_msg_data,
1599 x_msg_count => l_msg_count,
1600 x_return_status => l_return_status);
1601
1602 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1603 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1604 'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
1605 || l_return_status);
1606 END IF;
1607 -----------------------------------------------------
1608 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1609 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1610 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1611 RAISE OKC_API.G_EXCEPTION_ERROR;
1612 END IF;
1613 --------------------------------------------------------
1614 END IF; -- expiration date comparision
1615 -- Disable prev. version deliverables
1616 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1617 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1618 'Calling OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables');
1619 END IF;
1620 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
1621 p_api_version => 1.0,
1622 p_init_msg_list => FND_API.G_FALSE,
1623 p_commit => FND_API.G_FALSE,
1624 p_bus_doc_id => l_contract_id,
1625 p_bus_doc_type => contract_rec.contract_type,
1626 p_bus_doc_version => contract_rec.latest_signed_ver_number,
1627 x_msg_data => l_msg_data,
1628 x_msg_count => l_msg_count,
1629 x_return_status => l_return_status);
1630
1631 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1632 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1633 'OKC_DELIVERABLE_PROCESS_PVT.disableDeliverables return status is : '
1634 || l_return_status);
1635 END IF;
1636 -----------------------------------------------------
1637 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1638 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1639 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1640 RAISE OKC_API.G_EXCEPTION_ERROR;
1641 END IF;
1642 --------------------------------------------------------
1643 END IF; -- (l_sync_flag = 'Y')
1644 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1645 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1646 'Updating latest_signed_ver_number column');
1647 END IF;
1648 UPDATE okc_rep_contracts_all
1649 SET latest_signed_ver_number = contract_rec.contract_version_num
1650 WHERE contract_id = l_contract_id;
1651 CLOSE contract_csr;
1652
1653
1654 resultout := 'COMPLETE:';
1655 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1656 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1657 g_module || l_api_name,
1658 'Leaving OKC_REP_sign_WF_PVT.sign_contract');
1659 END IF;
1660 RETURN;
1661 END IF; -- (funcmode = 'RUN')
1662
1663 EXCEPTION
1664 WHEN others THEN
1665 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1666 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1667 g_module || l_api_name,
1668 'Leaving OKC_REP_sign_WF_PVT.sign_contract with exceptions ' || sqlerrm);
1669 END IF;
1670 wf_core.context('OKC_REP_SIGN_WF_PVT',
1671 'sign_contract',
1672 itemtype,
1673 itemkey,
1674 to_char(actid),
1675 funcmode);
1676 raise;
1677 END sign_contract;
1678
1679 -- Start of comments
1680 --API name : reject_contract
1681 --Type : Private.
1682 --Function : This procedure is called by workflow after the contract is rejected. Updates Contract's status
1683 -- to rejected and logs the status change in OKC_REP_CON_STATUS_HIST table.
1684 --Pre-reqs : None.
1685 --Parameters :
1686 --IN : itemtype IN VARCHAR2 Required
1687 -- Workflow item type parameter
1688 -- : itemkey IN VARCHAR2 Required
1689 -- Workflow item key parameter
1690 -- : actid IN VARCHAR2 Required
1691 -- Workflow actid parameter
1692 -- : funcmode IN VARCHAR2 Required
1693 -- Workflow function mode parameter
1694 --OUT : resultout OUT VARCHAR2(1)
1695 -- Workflow standard out parameter
1696 -- Note :
1697 -- End of comments
1698 PROCEDURE reject_contract(
1699 itemtype IN varchar2,
1700 itemkey IN varchar2,
1701 actid IN number,
1702 funcmode IN varchar2,
1703 resultout OUT nocopy varchar2
1704 ) IS
1705
1706 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
1707 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
1708 l_api_name VARCHAR2(30);
1709 l_return_status VARCHAR2(1);
1710 l_msg_count NUMBER;
1711 l_msg_data VARCHAR2(2000);
1712
1713 BEGIN
1714
1715 l_api_name := 'reject_contract';
1716
1717 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1718 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1719 'Entered OKC_REP_SIGN_WF_PVT.reject_contract');
1720 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1721 'Item Type is: ' || itemtype);
1722 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1723 'Item Key is: ' || itemkey);
1724 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1725 'actid is: ' || to_char(actid));
1726 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1727 'Function mode is: ' || funcmode);
1728 END IF;
1729 if (funcmode = 'RUN') then
1730 l_contract_id := wf_engine.GetItemAttrNumber(
1731 itemtype => itemtype,
1732 itemkey => itemkey,
1733 aname => 'CONTRACT_ID');
1734 l_contract_version := wf_engine.GetItemAttrNumber(
1735 itemtype => itemtype,
1736 itemkey => itemkey,
1737 aname => 'CONTRACT_VERSION');
1738 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1739 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1740 g_module || l_api_name,
1741 'Contract Id is: ' || to_char(l_contract_id));
1742 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1743 g_module || l_api_name,
1744 'Contract Version is: ' || to_char(l_contract_version));
1745 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1746 'Calling OKC_REP_UTIL_PVT.change_contract_status');
1747 END IF;
1748
1749 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
1750 OKC_REP_UTIL_PVT.change_contract_status(
1751 p_api_version => 1.0,
1752 p_init_msg_list => FND_API.G_FALSE,
1753 p_contract_id => l_contract_id,
1754 p_contract_version => l_contract_version,
1755 p_status_code => G_STATUS_REJECTED,
1756 p_user_id => fnd_global.user_id,
1757 p_note => NULL,
1758 x_msg_data => l_msg_data,
1759 x_msg_count => l_msg_count,
1760 x_return_status => l_return_status);
1761 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1762 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1763 g_module || l_api_name,
1764 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || l_return_status);
1765 END IF;
1766 -----------------------------------------------------
1767 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1768 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1769 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1770 RAISE OKC_API.G_EXCEPTION_ERROR;
1771 END IF;
1772 --------------------------------------------------------
1773
1774 resultout := 'COMPLETE:';
1775 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1776 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1777 g_module || l_api_name,
1778 'Leaving OKC_REP_SIGN_WF_PVT.reject_contract');
1779 END IF;
1780 RETURN;
1781 END IF; -- (funcmode = 'RUN')
1782 EXCEPTION
1783 WHEN others THEN
1784 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1785 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1786 g_module || l_api_name,
1787 'Leaving OKC_REP_SIGN_WF_PVT.reject_contract with exceptions ' || sqlerrm);
1788 END IF;
1789 wf_core.context('OKC_REP_SIGN_WF_PVT',
1790 'reject_contract',
1791 itemtype,
1792 itemkey,
1793 to_char(actid),
1794 funcmode);
1795 raise;
1796 END reject_contract;
1797
1798
1799 --Bug 6957819
1800 -- Start of comments
1801 --API name : con_has_terms
1802 --Type : Private.
1803 --Function : This procedure is called by workflow to check if terms has been applied on the document.
1804 --Pre-reqs : None.
1805 --Parameters :
1806 --IN : itemtype IN VARCHAR2 Required
1807 -- Workflow item type parameter
1808 -- : itemkey IN VARCHAR2 Required
1809 -- Workflow item key parameter
1810 -- : actid IN VARCHAR2 Required
1811 -- Workflow actid parameter
1812 -- : funcmode IN VARCHAR2 Required
1813 -- Workflow function mode parameter
1814 --OUT : resultout OUT VARCHAR2(1)
1815 -- Workflow standard out parameter
1816 -- Note :
1817 -- End of comments
1818
1819 PROCEDURE con_has_terms(
1820 itemtype IN varchar2,
1821 itemkey IN varchar2,
1822 actid IN number,
1823 funcmode IN varchar2,
1824 resultout OUT nocopy varchar2
1825 ) IS
1826
1827 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
1828 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
1829 l_api_name VARCHAR2(30);
1830 l_return_status VARCHAR2(1);
1831 l_msg_count NUMBER;
1832 l_msg_data VARCHAR2(2000);
1833 l_master_key wf_items.user_key%TYPE;
1834 l_value VARCHAR2(1);
1835
1836
1837 BEGIN
1838
1839 l_api_name := 'con_has_terms';
1840
1841 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1842 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1843 'Entered OKC_REP_SIGN_WF_PVT.complete_notification');
1844 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1845 'Item Type is: ' || itemtype);
1846 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1847 'Item Key is: ' || itemkey);
1848 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1849 'actid is: ' || to_char(actid));
1850 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1851 'Function mode is: ' || funcmode);
1852 END IF;
1853
1854 IF (funcmode = 'RUN') THEN
1855 l_contract_id := wf_engine.GetItemAttrNumber(
1856 itemtype => itemtype,
1857 itemkey => itemkey,
1858 aname => 'CONTRACT_ID');
1859
1860 l_contract_type := wf_engine.GetItemAttrText(
1861 itemtype => itemtype,
1862 itemkey => itemkey,
1863 aname => 'CONTRACT_TYPE');
1864
1865 l_value := OKC_TERMS_UTIL_GRP.HAS_TERMS( p_document_type => l_contract_type,
1866 p_document_id => l_contract_id);
1867 IF (l_value = 'Y') THEN
1868 resultout := 'COMPLETE:T';
1869 ELSE
1870 resultout := 'COMPLETE:F';
1871 END IF;
1872 END IF; -- RUN
1873
1874 EXCEPTION
1875 WHEN others THEN
1876 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1877 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1878 g_module || l_api_name,
1879 'Leaving OKC_REP_SIGN_WF_PVT.con_has_terms with exceptions ' || sqlerrm);
1880 END IF;
1881 wf_core.context('OKC_REP_SIGN_WF_PVT',
1882 'con_has_terms',
1883 itemtype,
1884 itemkey,
1885 to_char(actid),
1886 funcmode);
1887 raise;
1888
1889 END con_has_terms;
1890
1891
1892 -- Start of comments
1893 --API name : Con_attach_generated_YN
1894 --Type : Private.
1895 --Function : This procedure is called by workflow to check if terms has been applied on the document.
1896 --Pre-reqs : None.
1897 --Parameters :
1898 --IN : itemtype IN VARCHAR2 Required
1899 -- Workflow item type parameter
1900 -- : itemkey IN VARCHAR2 Required
1901 -- Workflow item key parameter
1902 -- : actid IN VARCHAR2 Required
1903 -- Workflow actid parameter
1904 -- : funcmode IN VARCHAR2 Required
1905 -- Workflow function mode parameter
1906 --OUT : resultout OUT VARCHAR2(1)
1907 -- Workflow standard out parameter
1908 -- Note :
1909 -- End of comments
1910
1911 PROCEDURE con_attach_generated_yn(
1912 itemtype IN varchar2,
1913 itemkey IN varchar2,
1914 actid IN number,
1915 funcmode IN varchar2,
1916 resultout OUT nocopy varchar2
1917 ) IS
1918
1919 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
1920 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
1921 l_con_req_id OKC_CONTRACT_DOCS.request_id%TYPE;
1922 l_api_name VARCHAR2(30);
1923 l_return_status VARCHAR2(1);
1924 l_msg_count NUMBER;
1925 l_msg_data VARCHAR2(2000);
1926 l_master_key wf_items.user_key%TYPE;
1927 l_value VARCHAR2(1);
1928
1929 CURSOR contract_attachment_exists(l_contract_id IN NUMBER,l_contract_type IN VARCHAR2, l_con_req_id IN NUMBER) IS
1930 select 'Y'
1931 from okc_contract_docs
1932 where business_document_type = l_contract_type
1933 and business_document_id = l_contract_id
1934 AND request_id = l_con_req_id;
1935
1936 BEGIN
1937
1938 l_api_name := 'con_attach_generated_yn';
1939
1940 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1941 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1942 'Entered OKC_REP_SIGN_WF_PVT.complete_notification');
1943 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1944 'Item Type is: ' || itemtype);
1945 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1946 'Item Key is: ' || itemkey);
1947 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1948 'actid is: ' || to_char(actid));
1949 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1950 'Function mode is: ' || funcmode);
1951 END IF;
1952
1953 IF (funcmode = 'RUN') THEN
1954 l_contract_id := wf_engine.GetItemAttrNumber(
1955 itemtype => itemtype,
1956 itemkey => itemkey,
1957 aname => 'CONTRACT_ID');
1958
1959 l_contract_type := wf_engine.GetItemAttrText(
1960 itemtype => itemtype,
1961 itemkey => itemkey,
1962 aname => 'CONTRACT_TYPE');
1963
1964 l_con_req_id := wf_engine.GetItemAttrNumber(
1965 itemtype => itemtype,
1966 itemkey => itemkey,
1967 aname => 'CONC_REQUEST_ID' );
1968
1969 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1970 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1810: Entering con_attach_generated_yn');
1971 END IF;
1972
1973 OPEN contract_attachment_exists(l_contract_id, l_contract_type, l_con_req_id) ;
1974 FETCH contract_attachment_exists into l_value;
1975 CLOSE contract_attachment_exists ;
1976
1977 IF (l_value = 'Y') THEN
1978 resultout := 'COMPLETE:T';
1979 ELSE
1980 resultout := 'COMPLETE:F';
1981 END IF;
1982
1983 END IF; -- RUN
1984 EXCEPTION
1985 WHEN others THEN
1986 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1987 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1988 g_module || l_api_name,
1989 'Leaving OKC_REP_SIGN_WF_PVT.con_attach_generated_yn with exceptions ' || sqlerrm);
1990 END IF;
1991 wf_core.context('OKC_REP_SIGN_WF_PVT',
1992 'con_attach_generated_yn',
1993 itemtype,
1994 itemkey,
1995 to_char(actid),
1996 funcmode);
1997 raise;
1998
1999 END con_attach_generated_yn;
2000
2001
2002 -- Start of comments
2003 --API name : contract_preview_yn
2004 --Type : Private.
2005 --Function : This procedure is checks if the contract can be printed or not
2006 --Pre-reqs : None.
2007 --Parameters :
2008 -- Note :
2009 -- End of comments
2010 PROCEDURE contract_preview_yn
2011 (itemtype IN varchar2,
2012 itemkey IN varchar2,
2013 actid IN number,
2014 funcmode IN varchar2,
2015 resultout OUT nocopy varchar2
2016 ) IS
2017
2018 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2019 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
2020 l_con_req_id OKC_CONTRACT_DOCS.request_id%TYPE;
2021 l_api_name VARCHAR2(30);
2022 l_return_status VARCHAR2(1);
2023 l_msg_count NUMBER;
2024 l_msg_data VARCHAR2(2000);
2025 l_master_key wf_items.user_key%TYPE;
2026 l_value VARCHAR2(1);
2027
2028 CURSOR csr_doc(l_contract_type IN VARCHAR2) IS
2029 SELECT Nvl(ALLOW_APPROVER_EDIT_YN,'N') FROM okc_bus_doc_types_b WHERE document_type=l_contract_type;
2030
2031 BEGIN
2032
2033 l_api_name := 'contract_preview_yn';
2034
2035 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2036 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2037 'Entered OKC_REP_SIGN_WF_PVT.contract_preview_yn');
2038 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2039 'Item Type is: ' || itemtype);
2040 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2041 'Item Key is: ' || itemkey);
2042 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2043 'actid is: ' || to_char(actid));
2044 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2045 'Function mode is: ' || funcmode);
2046 END IF;
2047
2048 IF (funcmode = 'RUN') THEN
2049
2050 l_contract_type := wf_engine.GetItemAttrText(
2051 itemtype => itemtype,
2052 itemkey => itemkey,
2053 aname => 'CONTRACT_TYPE');
2054
2055 l_contract_id := wf_engine.GetItemAttrNumber(
2056 itemtype => itemtype,
2057 itemkey => itemkey,
2058 aname => 'CONTRACT_ID');
2059
2060 l_value:= okc_rep_contract_process_pvt.call_contract_preview_cp_yn (l_contract_id,
2061 l_contract_type ) ;
2062
2063 IF (l_value = 'Y') THEN
2064 resultout := 'COMPLETE:Y';
2065 ELSE
2066 resultout := 'COMPLETE:N';
2067 END IF;
2068
2069 END IF; -- RUN
2070 EXCEPTION
2071 WHEN others THEN
2072 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2073 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2074 g_module || l_api_name,
2075 'Leaving OKC_REP_SIGN_WF_PVT.contract_preview_yn with exceptions ' || sqlerrm);
2076 END IF;
2077 wf_core.context('OKC_REP_SIGN_WF_PVT',
2078 'contract_preview_yn',
2079 itemtype,
2080 itemkey,
2081 to_char(actid),
2082 funcmode);
2083 raise;
2084
2085 END contract_preview_yn;
2086
2087
2088 PROCEDURE Apps_initialize(itemtype IN varchar2,
2089 itemkey IN varchar2,
2090 actid IN number,
2091 funcmode IN varchar2,
2092 resultout OUT nocopy varchar2)
2093 IS
2094
2095
2096 l_user_id NUMBER;
2097 l_resp_id NUMBER;
2098 l_resp_appl_id NUMBER;
2099
2100 l_contract_id NUMBER;
2101 l_org_id NUMBER;
2102
2103 l_api_name VARCHAR2(100);
2104
2105 BEGIN
2106
2107 l_api_name := 'Apps_initialize';
2108
2109 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2110 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2111 'Entered OKC_REP_WF_PVT.Apps_initialize');
2112 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2113 'Item Type is: ' || itemtype);
2114 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2115 'Item Key is: ' || itemkey);
2116 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2117 'actid is: ' || to_char(actid));
2118 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2119 'Function mode is: ' || funcmode);
2120 END IF;
2121
2122 IF (funcmode = 'RUN') THEN
2123
2124 l_contract_id := wf_engine.GetItemAttrNumber(
2125 itemtype => itemtype,
2126 itemkey => itemkey,
2127 aname => 'CONTRACT_ID');
2128
2129 l_user_id := wf_engine.GetItemAttrNumber(
2130 itemtype => itemtype,
2131 itemkey => itemkey,
2132 aname => 'CTX_USER_ID');
2133
2134 l_resp_appl_id := wf_engine.GetItemAttrNumber(
2135 itemtype => itemtype,
2136 itemkey => itemkey,
2137 aname => 'CTX_APPL_ID');
2138
2139 l_resp_id := wf_engine.GetItemAttrNumber(
2140 itemtype => itemtype,
2141 itemkey => itemkey,
2142 aname => 'CTX_RESP_ID');
2143
2144 fnd_global.apps_initialize
2145 ( user_id => l_user_id
2146 ,resp_id => l_resp_id
2147 ,resp_appl_id => l_resp_appl_id
2148 );
2149
2150 SELECT org_id INTO l_org_id
2151 FROM okc_rep_contracts_all
2152 WHERE contract_id = l_contract_id;
2153
2154 -- MO init
2155 mo_global.init('OKC');
2156
2157 -- MO Set policy Context
2158 mo_global.set_policy_context('S', l_org_id);
2159
2160 resultout := 'COMPLETE:Y';
2161 END IF; -- RUN
2162
2163 EXCEPTION
2164 WHEN others THEN
2165 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2166 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2167 g_module || l_api_name,
2168 'Leaving OKC_REP_WF_PVT.Apps_initialize with exceptions ' || sqlerrm);
2169 END IF;
2170 wf_core.context('OKC_REP_WF_PVT',
2171 'Apps_initialize',
2172 itemtype,
2173 itemkey,
2174 to_char(actid),
2175 funcmode);
2176 raise;
2177
2178 END Apps_initialize;
2179
2180 END okc_rep_sign_wf_pvt;