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