[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_WF_PVT
Source
1 PACKAGE BODY OKC_REP_WF_PVT AS
2 /* $Header: OKCVREPWFB.pls 120.10.12020000.12 2013/04/11 07:40:49 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 G_WF_STATUS_TRANSFERRED CONSTANT VARCHAR2(200) := 'TRANSFERRED';
45 G_WF_STATUS_DELEGATED CONSTANT VARCHAR2(200) := 'DELEGATED';
46 G_WF_APPROVE_FORWARD CONSTANT VARCHAR2(200) := 'APPROVE_FORWARD';
47
48 -- Contracts business events codes TBL Type
49 SUBTYPE EVENT_TBL_TYPE IS OKC_MANAGE_DELIVERABLES_GRP.BUSDOCDATES_TBL_TYPE;
50 -- Contract events - deliverables integration
51 G_CONTRACT_EXPIRE_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_EXPIRE';
52 G_CONTRACT_EFFECTIVE_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_EFFECTIVE';
53 G_CONTRACT_TERMINATED_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_TERMINATED';
54
55 -- Required for Contract not found error message
56 G_INVALID_CONTRACT_ID_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_INVALID_CONTRACT_ID';
57 G_CANEL_APPROVAL_ERROR_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_CANCEL_APPROVAL_ERROR';
58 G_CONTRACT_ID_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_ID';
59 G_CONTRACT_NUM_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_NUM';
60
61
62 ------------------------------------------------------------------------------
63 -- GLOBAL EXCEPTION
64 ------------------------------------------------------------------------------
65 E_Resource_Busy EXCEPTION;
66 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
67
68 ------------------------------------------------------------------------------
69 -- GLOBAL VARIABLES
70 ------------------------------------------------------------------------------
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 BEGIN
114
115 l_api_name := 'initialize_attributes';
116
117 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
118 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
119 'Entered OKC_REP_WF_PVT.initialize_attributes');
120 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
121 'Item Type is: ' || itemtype);
122 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
123 'Item Key is: ' || itemkey);
124 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
125 'actid is: ' || to_char(actid));
126 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
127 'Function mode is: ' || funcmode);
128 END IF;
129 IF (funcmode = 'RUN') THEN
130 l_contract_id := wf_engine.GetItemAttrNumber(
131 itemtype => itemtype,
132 itemkey => itemkey,
133 aname => 'CONTRACT_ID');
134
135 -- Get contract attributes
136 OPEN contract_csr(l_contract_id);
137 FETCH contract_csr INTO contract_rec;
138 IF(contract_csr%NOTFOUND) THEN
139 RAISE NO_DATA_FOUND;
140 END IF;
141
142 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
144 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_TYPE ' || contract_rec.contract_type);
145 END IF;
146 WF_ENGINE.SetItemAttrText (
147 itemtype => itemtype,
148 itemkey => itemkey,
149 aname => 'CONTRACT_TYPE',
150 avalue => contract_rec.contract_type);
151
152 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
153 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
154 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_NUMBER ' || contract_rec.contract_number);
155 END IF;
156 WF_ENGINE.SetItemAttrText (
157 itemtype => itemtype,
158 itemkey => itemkey,
159 aname => 'CONTRACT_NUMBER',
160 avalue => contract_rec.contract_number);
161
162
163 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
164 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
165 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_VERSION_NUM ' || contract_rec.contract_version_num);
166 END IF;
167 WF_ENGINE.SetItemAttrNumber (
168 itemtype => itemtype,
169 itemkey => itemkey,
170 aname => 'CONTRACT_VERSION',
171 avalue => contract_rec.contract_version_num);
172
173 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
174 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
175 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_NAME ' || contract_rec.contract_name);
176 END IF;
177 WF_ENGINE.SetItemAttrText (
178 itemtype => itemtype,
179 itemkey => itemkey,
180 aname => 'CONTRACT_NAME',
181 avalue => contract_rec.contract_name);
182
183 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
184 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
185 'Calling WF_ENGINE.setitemattrnumber for CONTRACT_ATTACHMENTS');
186 END IF;
187 WF_ENGINE.SetItemAttrText (
188 itemtype => itemtype,
189 itemkey => itemkey,
190 aname => 'CONTRACT_ATTACHMENTS',
191 avalue => 'FND:entity=OKC_CONTRACT_DOCS&pk1name=BusinessDocumentType&pk1value='||contract_rec.contract_type
192 ||'&pk2name=BusinessDocumentId&pk2value='||l_contract_id
193 ||'&pk3name=BusinessDocumentVersion&pk3value=-99&categories=OKC_REPO_CONTRACT,OKC_REPO_APP_ABSTRACT');
194
195 -- Repository Contracts ER's - Calling this after approval
196 -- Initialize AME, clear all prior approvals on this transaction id.
197 -- ame_api2.clearAllApprovals(
198 -- applicationIdIn => G_APPLICATION_ID,
199 -- transactionTypeIn => G_TRANSACTION_TYPE,
200 -- transactionIdIn => fnd_number.number_to_canonical(l_contract_id));
201
202 CLOSE contract_csr;
203 resultout := 'COMPLETE:';
204 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
206 g_module || l_api_name ,
207 'Leaving OKC_REP_WF_PVT.initialize_attributes from funcmode=RUN');
208 END IF;
209 RETURN;
210 END IF; -- (funcmode = 'RUN')
211
212
213 IF (funcmode = 'CANCEL') THEN
214 resultout := 'COMPLETE:';
215 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
216 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
217 g_module || l_api_name,
218 'Leaving OKC_REP_WF_PVT.initialize_attributes from funcmode=CANCEL');
219 END IF;
220 RETURN;
221 END IF; -- (funcmode = 'CANCEL')
222
223 IF (funcmode = 'TIMEOUT') THEN
224 resultout := 'COMPLETE:';
225 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
227 g_module || l_api_name,
228 'Leaving OKC_REP_WF_PVT.initialize_attributes from funcmode=TIMEOUT');
229 END IF;
230 RETURN;
231 END IF; -- (funcmode = 'TIMEOUT')
232
233 EXCEPTION
234 WHEN others THEN
235 --close cursors
236 IF (contract_csr%ISOPEN) THEN
237 CLOSE contract_csr ;
238 END IF;
239 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
240 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
241 g_module || l_api_name,
242 'Leaving OKC_REP_WF_PVT.initialize_attributes with exceptions ' || sqlerrm);
243 END IF;
244 wf_core.context('OKC_REP_WF_PVT',
245 'initialize_attributes',
246 itemtype,
247 itemkey,
248 to_char(actid),
249 funcmode);
250 raise;
251
252 END initialize_attributes;
253
254 -- Start of comments
255 --API name : has_next_approver
256 --Type : Private.
257 --Function : This procedure is called by workflow to get the next approver in the list. Call AME to get the approver list.
258 -- Updates workflow with the approver list.
259 --Pre-reqs : None.
260 --Parameters :
261 --IN : itemtype IN VARCHAR2 Required
262 -- Workflow item type parameter
263 -- : itemkey IN VARCHAR2 Required
264 -- Workflow item key parameter
265 -- : actid IN VARCHAR2 Required
266 -- Workflow actid parameter
267 -- : funcmode IN VARCHAR2 Required
268 -- Workflow function mode parameter
269 --OUT : resultout OUT VARCHAR2(1)
270 -- Workflow standard out parameter
271 -- Note :
272 -- End of comments
273 PROCEDURE has_next_approver(
274 itemtype IN varchar2,
275 itemkey IN varchar2,
276 actid IN number,
277 funcmode IN varchar2,
278 resultout OUT nocopy varchar2
279 ) IS
280 l_api_name VARCHAR2(30);
281 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
282 l_process_complete_yn varchar2(1);
283 l_next_approvers ame_util.approversTable2;
284 l_item_indexes ame_util.idList;
285 l_item_classes ame_util.stringList;
286 l_item_ids ame_util.stringList;
287 l_item_sources ame_util.longStringList;
288 l_user_names varchar2(4000);
289 l_role_name varchar2(4000);
290 l_role_display_name varchar2(4000);
291
292 BEGIN
293
294 l_api_name := 'has_next_approver';
295
296 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
297 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
298 'Entered OKC_REP_WF_PVT.has_next_approver');
299 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
300 'Item Type is: ' || itemtype);
301 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
302 'Item Key is: ' || itemkey);
303 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
304 'actid is: ' || to_char(actid));
305 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
306 'Function mode is: ' || funcmode);
307 END IF;
308 IF (funcmode = 'RUN') then
309 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
310 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
311 g_module || l_api_name,
312 'Calling wf_engine.GetItemAttrNumber to get CONTRACT_ID');
313 END IF;
314 l_contract_id := wf_engine.GetItemAttrNumber(
315 itemtype => itemtype,
316 itemkey => itemkey,
317 aname => 'CONTRACT_ID');
318 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
319 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
320 g_module || l_api_name,
321 'Contract Id is: ' || to_char(l_contract_id));
322 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
323 g_module || l_api_name,
324 'Calling ame_api.getNextApprover to get the approver id');
325 END IF;
326 ame_api2.getNextApprovers1(
327 applicationIdIn => G_APPLICATION_ID,
328 transactionTypeIn => G_TRANSACTION_TYPE,
329 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
330 flagApproversAsNotifiedIn => ame_util.booleanFalse,
331 approvalProcessCompleteYNOut => l_process_complete_yn,
332 nextApproversOut => l_next_approvers,
333 itemIndexesOut => l_item_indexes,
334 itemClassesOut => l_item_classes,
335 itemIdsOut => l_item_ids,
336 itemSourcesOut => l_item_sources);
337 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
338 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
339 g_module || l_api_name,
340 'Number of approvers: ' || to_char(l_next_approvers.count));
341 END IF;
342 IF (l_next_approvers.count = 0) THEN
343 -- No more approver.
344 wf_engine.SetItemAttrText (
345 itemtype => itemtype,
346 itemkey => itemkey,
347 aname => 'APPROVER',
348 avalue => NULL);
349 resultout := 'COMPLETE:F';
350 ELSIF (l_next_approvers.count = 1) THEN
351 -- Only 1 approver remaining
352 wf_engine.SetItemAttrText (
353 itemtype => itemtype,
354 itemkey => itemkey,
355 aname => 'APPROVER',
356 avalue => l_next_approvers(1).name);
357 resultout := 'COMPLETE:T';
358 ELSE
359 l_user_names := l_next_approvers(1).name;
360 -- More than 1 approvers
361 -- Concatenate approver names using , separator
362 FOR i IN l_next_approvers.first..l_next_approvers.last LOOP
363 IF l_next_approvers.exists(i) THEN
364 IF (i=1) THEN
365 l_user_names := l_next_approvers(1).name;
366 ELSE
367 l_user_names := l_user_names || ',' || l_next_approvers(i).name;
368 END IF;
369 END IF;
370 END LOOP;
371
372 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
373 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
374 g_module || l_api_name,
375 'Adhoc role name is : ' || l_user_names);
376 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
377 g_module || l_api_name,
378 'Calling WF_DIRECTORY.createAdHocRole');
379 END IF;
380 -- Create an adhoc role using l_user_names
381 WF_DIRECTORY.createAdHocRole(
382 role_name=>l_role_name,
383 role_display_name=>l_role_display_name,
384 language=>null,
385 territory=>null,
386 role_description=>'Repository Contract Ad hoc role',
387 notification_preference=>'MAILHTML',
388 role_users=>l_user_names,
389 email_address=>null,
390 fax=>null,
391 status=>'ACTIVE',
392 expiration_date=>SYSDATE+1);
393 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
394 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
395 g_module || l_api_name,
396 'Completed Adhoc role creation');
397 END IF;
398 wf_engine.SetItemAttrText (
399 itemtype => itemtype,
400 itemkey => itemkey,
401 aname => 'APPROVER',
402 avalue => l_role_name);
403 resultout := 'COMPLETE:T';
404 END IF; -- (l_next_approvers.count = 0)
405
406 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
407 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
408 g_module || l_api_name,
409 'Leaving OKC_REP_WF_PVT.has_next_approver from funcmode=RUN');
410 END IF;
411 RETURN;
412 END IF; -- (funcmode = 'RUN')
413
414
415 IF (funcmode = 'CANCEL') THEN
416 resultout := 'COMPLETE:';
417 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
419 g_module || l_api_name,
420 'Leaving OKC_REP_WF_PVT.has_next_approver from funcmode=CANCEL');
421 END IF;
422 RETURN;
423 END IF; -- (funcmode = 'CANCEL')
424
425 IF (funcmode = 'TIMEOUT') THEN
426 resultout := 'COMPLETE:';
427 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
428 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
429 g_module || l_api_name,
430 'Leaving OKC_REP_WF_PVT.has_next_approver from funcmode=TIMEOUT');
431 END IF;
432 RETURN;
433 END IF; -- (funcmode = 'TIMEOUT')
434
435 EXCEPTION
436 WHEN others THEN
437 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
438 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
439 g_module || l_api_name,
440 'Leaving OKC_REP_WF_PVT.has_next_approver with exceptions ' || sqlerrm);
441 END IF;
442 wf_core.context('OKC_REP_WF_PVT',
443 'has_next_approver',
444 itemtype,
445 itemkey,
446 to_char(actid),
447 funcmode);
448 raise;
449
450 END has_next_approver;
451
452
453 -- Start of comments
454 --API name : is_approval_complete
455 --Type : Private.
456 --Function : This procedure is called by workflow Master Process to check if the approval is complete.
457 -- WF Notification process are started for the approvers pending notification
458 -- Updates workflow with the approver list.
459 --Pre-reqs : None.
460 --Parameters :
461 --IN : itemtype IN VARCHAR2 Required
462 -- Workflow item type parameter
463 -- : itemkey IN VARCHAR2 Required
464 -- Workflow item key parameter
465 -- : actid IN VARCHAR2 Required
466 -- Workflow actid parameter
467 -- : funcmode IN VARCHAR2 Required
468 -- Workflow function mode parameter
469 --OUT : resultout OUT VARCHAR2(1)
470 -- Workflow standard out parameter
471 -- Note :
472 -- End of comments
473 PROCEDURE is_approval_complete(
474 itemtype IN varchar2,
475 itemkey IN varchar2,
476 actid IN number,
477 funcmode IN varchar2,
478 resultout OUT nocopy varchar2
479 ) IS
480 l_api_name varchar2(30);
481 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
482 l_contract_number OKC_REP_CONTRACTS_ALL.contract_number%type;
483 l_requester varchar2(4000);
484 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%type;
485 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
486 l_contract_name OKC_REP_CONTRACTS_ALL.contract_name%type;
487 l_contract_attachments varchar2(4000);
488 l_process_complete_yn varchar2(1);
489 l_next_approvers ame_util.approversTable2;
490 l_item_indexes ame_util.idList;
491 l_item_classes ame_util.stringList;
492 l_item_ids ame_util.stringList;
493 l_item_sources ame_util.longStringList;
494 l_user_name varchar2(4000);
495 l_role_name varchar2(4000);
496 l_role_display_name varchar2(4000);
497 l_item_key wf_items.item_key%TYPE;
498 l_notified_count number;
499
500 l_approver_name VARCHAR2(100);
501 l_approver_type VARCHAR2(100);
502 l_group_id NUMBER; --Bug 16231003
503 BEGIN
504
505 l_api_name := 'is_approval_complete';
506
507
508 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
509 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
510 'Entered OKC_REP_WF_PVT.is_approval_complete');
511 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
512 'Item Type is: ' || itemtype);
513 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
514 'Item Key is: ' || itemkey);
515 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
516 'actid is: ' || to_char(actid));
517 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
518 'Function mode is: ' || funcmode);
519 END IF;
520 IF (funcmode = 'RUN') then
521 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
523 g_module || l_api_name,
524 'Calling wf_engine.GetItemAttrNumber to get CONTRACT_ID');
525 END IF;
526 l_contract_id := wf_engine.GetItemAttrNumber(
527 itemtype => itemtype,
528 itemkey => itemkey,
529 aname => 'CONTRACT_ID');
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 'Contract Id is: ' || to_char(l_contract_id));
534 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
535 g_module || l_api_name,
536 'Calling ame_api2.getNextApprover1 to get the approver id');
537 END IF;
538 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
539 ame_api2.getNextApprovers1(
540 applicationIdIn => G_APPLICATION_ID,
541 transactionTypeIn => G_TRANSACTION_TYPE,
542 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
543 flagApproversAsNotifiedIn => ame_util.booleanTrue,
544 approvalProcessCompleteYNOut => l_process_complete_yn,
545 nextApproversOut => l_next_approvers,
546 itemIndexesOut => l_item_indexes,
547 itemClassesOut => l_item_classes,
548 itemIdsOut => l_item_ids,
549 itemSourcesOut => l_item_sources);
550 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
552 g_module || l_api_name,
553 'Number of approvers: ' || to_char(l_next_approvers.count));
554 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
555 g_module || l_api_name,
556 'l_process_complete_yn: ' || l_process_complete_yn);
557 END IF;
558 IF (l_process_complete_yn = 'W') THEN
559 resultout := 'COMPLETE:F';
560 ELSE
561 resultout := 'COMPLETE:T';
562 END IF;
563
564 IF (l_next_approvers.count > 0) THEN
565 l_contract_number := wf_engine.GetItemAttrText(
566 itemtype => itemtype,
567 itemkey => itemkey,
568 aname => 'CONTRACT_NUMBER');
569 l_requester := wf_engine.GetItemAttrText(
570 itemtype => itemtype,
571 itemkey => itemkey,
572 aname => 'REQUESTER');
573 l_contract_name := wf_engine.GetItemAttrText(
574 itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'CONTRACT_NAME');
577 l_contract_version := wf_engine.GetItemAttrNumber(
578 itemtype => itemtype,
579 itemkey => itemkey,
580 aname => 'CONTRACT_VERSION');
581 l_contract_type := wf_engine.GetItemAttrText(
582 itemtype => itemtype,
583 itemkey => itemkey,
584 aname => 'CONTRACT_TYPE');
585 l_notified_count := wf_engine.GetItemAttrNumber(
586 itemtype => itemtype,
587 itemkey => itemkey,
588 aname => 'APPROVER_COUNTER');
589 FOR i IN l_next_approvers.first..l_next_approvers.last LOOP
590 IF l_next_approvers.exists(i) THEN
591 --kkolukul : Change code to support HR positions approval hierarchy
592 l_approver_type := l_next_approvers(i).orig_system;
593
594 IF (l_next_approvers(i).orig_system = ame_util.posOrigSystem) THEN
595
596 BEGIN
597 -----------------------------------------------------------------------
598 -- SQL What: Get the person assigned to position returned by AME.
599 -- SQL Why : When AME returns position id, then using this sql we find
600 -- one person assigned to this position and use this person
601 -- as approver.
602 -----------------------------------------------------------------------
603 l_approver_name := l_next_approvers(i).name;
604 --Bug 16231003
605 l_group_id := l_next_approvers(i).group_or_chain_id;
606
607
608 SELECT user_name INTO l_user_name
609 FROM (
610 SELECT user_name FROM fnd_user fu, per_all_assignments_f asg, per_all_people_f per
611 WHERE asg.position_id = l_next_approvers (i).orig_system_id
612 AND per.person_id = asg.person_id
613 AND fu.employee_id = per.person_id
614 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND NVL(per.effective_end_date, TRUNC( SYSDATE))
615 AND asg.primary_flag = 'Y'
616 AND asg.assignment_type IN ( 'E', 'C' )
617 AND ( per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y' )
618 AND asg.assignment_status_type_id NOT IN
619 ( SELECT assignment_status_type_id
620 FROM per_assignment_status_types
621 WHERE per_system_status = 'TERM_ASSIGN' )
622 AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
623 ORDER BY per.last_name )
624 WHERE ROWNUM = 1;
625
626 EXCEPTION
627 WHEN NO_DATA_FOUND THEN
628
629 -- As this is a blank record, remove it in AME and the global variable.
630 -- Return 'NO_USERS'. We use PO_SYS_GENERATED_APPROVERS_SUPPRESS dynamic profile to
631 -- override AME mandatory attribute ALLOW_DELETING_RULE_GENERATED_APPROVERS.
632 ame_api3.suppressApprover( applicationIdIn => G_APPLICATION_ID,
633 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
634 approverIn => l_next_approvers(i),
635 transactionTypeIn => G_TRANSACTION_TYPE );
636 -- l_next_approvers.delete(i);
637 -- l_position_has_valid_approvers := 'NO_USERS';
638 IF i = l_next_approvers.Count THEN
639 resultout := 'COMPLETE:F' ;
640 EXIT;
641
642 ELSE
643 CONTINUE;
644 END IF;
645 END;
646
647 ELSE
648 l_user_name := l_next_approvers(i).name;
649 --Bug 16231003
650 l_group_id := l_next_approvers(i).group_or_chain_id;
651
652 END IF; --g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem
653
654 -- l_user_name := l_next_approvers(i).name;
655 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
656 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
657 g_module || l_api_name,
658 'User name for role is : ' || l_user_name);
659 END IF;
660 l_notified_count := l_notified_count + 1;
661 l_item_key := itemkey || '_' || to_char(l_notified_count);
662 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
663 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
664 'Calling WF_ENGINE.createprocess for Notification');
665 END IF;
666
667 WF_ENGINE.createprocess (
668 itemtype => itemtype,
669 itemkey => l_item_key,
670 process => G_APPROVAL_NOTIF_PROCESS);
671
672 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
673 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
674 'Calling WF_ENGINE.SetItemOwner for Notification Process');
675 END IF;
676 WF_ENGINE.SetItemOwner (
677 itemtype => itemtype,
678 itemkey => l_item_key,
679 owner => fnd_global.user_name);
680
681 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
683 'Setting Notification Process Approver to: ' || l_user_name);
684 END IF;
685 WF_ENGINE.SetItemAttrText (
686 itemtype => itemtype,
687 itemkey => l_item_key,
688 aname => 'APPROVER',
689 avalue => l_user_name);
690
691 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
692 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
693 'Setting Notification Process Contract Id to: ' || l_contract_id);
694 END IF;
695 WF_ENGINE.SetItemAttrNumber (
696 itemtype => itemtype,
697 itemkey => l_item_key,
698 aname => 'CONTRACT_ID',
699 avalue => l_contract_id);
700
701 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
702 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
703 'Setting Notification Process Contract Name: ' || l_contract_name);
704 END IF;
705 WF_ENGINE.SetItemAttrText (
706 itemtype => itemtype,
707 itemkey => l_item_key,
708 aname => 'CONTRACT_NAME',
709 avalue => l_contract_name);
710
711 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
712 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
713 'Setting Notification Process Contract Version: ' || l_contract_version);
714 END IF;
715 WF_ENGINE.SetItemAttrNumber (
716 itemtype => itemtype,
717 itemkey => l_item_key,
718 aname => 'CONTRACT_VERSION',
719 avalue => l_contract_version);
720
721 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
722 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
723 'Setting Notification Process Contract Type: ' || l_contract_type);
724 END IF;
725 WF_ENGINE.SetItemAttrText (
726 itemtype => itemtype,
727 itemkey => l_item_key,
728 aname => 'CONTRACT_TYPE',
729 avalue => l_contract_type);
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 Number: ' || l_contract_number);
734 END IF;
735 WF_ENGINE.SetItemAttrText (
736 itemtype => itemtype,
737 itemkey => l_item_key,
738 aname => 'CONTRACT_NUMBER',
739 avalue => l_contract_number);
740
741 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
743 'Setting Notification Process Requester: ' || l_requester);
744 END IF;
745 WF_ENGINE.SetItemAttrText (
746 itemtype => itemtype,
747 itemkey => l_item_key,
748 aname => 'REQUESTER',
749 avalue => l_requester);
750
751 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
752 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
753 'Setting Notification Process Contract Attachment');
754 END IF;
755 WF_ENGINE.SetItemAttrText (
756 itemtype => itemtype,
757 itemkey => l_item_key,
758 aname => 'CONTRACT_ATTACHMENTS',
759 avalue => 'FND:entity=OKC_CONTRACT_DOCS&pk1name=BusinessDocumentType&pk1value='||l_contract_type
760 ||'&pk2name=BusinessDocumentId&pk2value='||l_contract_id
761 ||'&pk3name=BusinessDocumentVersion&pk3value=-99&categories=OKC_REPO_CONTRACT,OKC_REPO_APP_ABSTRACT');
762
763 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
765 'Setting Notification Process Master Item Key to: ' || itemkey);
766 END IF;
767 WF_ENGINE.SetItemAttrText (
768 itemtype => itemtype,
769 itemkey => l_item_key,
770 aname => 'MASTER_ITEM_KEY',
771 avalue => itemkey);
772
773 --14758583 : kkolukul : HR position group support
774 --Setting attributes for the parent process
775 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
777 'Setting Notification Process APPROVER_TYPE to: ' || l_approver_type);
778 END IF;
779 WF_ENGINE.SetItemAttrText (
780 itemtype => itemtype,
781 itemkey => itemkey,
782 aname => 'APPROVER_TYPE',
783 avalue => l_approver_type);
784
785 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
786 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
787 'Setting Notification Process APPROVER_POS_NAME to: ' || l_approver_name);
788 END IF;
789
790 WF_ENGINE.SetItemAttrText (
791 itemtype => itemtype,
792 itemkey => itemkey,
793 aname => 'APPROVER_POS_NAME',
794 avalue => l_approver_name);
795
796 --Setting attributes for the child notification process
797 WF_ENGINE.SetItemAttrText (
798 itemtype => itemtype,
799 itemkey => l_item_key,
800 aname => 'APPROVER_TYPE',
801 avalue => l_approver_type);
802
803 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
804 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
805 'Setting Notification Process APPROVER_POS_NAME to: ' || l_approver_name);
806 END IF;
807
808 WF_ENGINE.SetItemAttrText (
809 itemtype => itemtype,
810 itemkey => l_item_key,
811 aname => 'APPROVER_POS_NAME',
812 avalue => l_approver_name);
813
814 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
815 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
816 'Setting Notification Process Approver group Id to: ' || l_group_id);
817 END IF;
818 WF_ENGINE.SetItemAttrText (
819 itemtype => itemtype,
820 itemkey => l_item_key,
821 aname => 'APPROVER_GROUP_ID',
822 avalue => l_group_id);
823
824
825 --14758583 : kkolukul : HR position group support
826
827 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
828 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
829 'Starting Notification Process ');
830 END IF;
831 wf_engine.startProcess(
832 itemtype => itemtype,
833 itemkey => l_item_key);
834 END IF; -- l_next_approvers.exists(i)
835 END LOOP;
836 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
837 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
838 'Calling WF_ENGINE.setitemattrnumber for APPROVER_COUNTER: ' || l_notified_count);
839 END IF;
840 WF_ENGINE.SetItemAttrNumber (
841 itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'APPROVER_COUNTER',
844 avalue => l_notified_count);
845 END IF; -- (l_next_approvers.count > 0)
846
847 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
849 g_module || l_api_name,
850 'Leaving OKC_REP_WF_PVT.is_approval_complete from funcmode=RUN');
851 END IF;
852 RETURN;
853 END IF; -- (funcmode = 'RUN')
854
855
856 IF (funcmode = 'CANCEL') THEN
857 resultout := 'COMPLETE:';
858 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
859 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
860 g_module || l_api_name,
861 'Leaving OKC_REP_WF_PVT.is_approval_complete from funcmode=CANCEL');
862 END IF;
863 RETURN;
864 END IF; -- (funcmode = 'CANCEL')
865
866 IF (funcmode = 'TIMEOUT') THEN
867 resultout := 'COMPLETE:';
868 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
870 g_module || l_api_name,
871 'Leaving OKC_REP_WF_PVT.is_approval_complete from funcmode=TIMEOUT');
872 END IF;
873 RETURN;
874 END IF; -- (funcmode = 'TIMEOUT')
875
876 EXCEPTION
877 WHEN others THEN
878 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
879 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
880 g_module || l_api_name,
881 'Leaving OKC_REP_WF_PVT.is_approval_complete with exceptions ' || sqlerrm);
882 END IF;
883 wf_core.context('OKC_REP_WF_PVT',
884 'is_approval_complete',
885 itemtype,
886 itemkey,
887 to_char(actid),
888 funcmode);
889 raise;
890
891 END is_approval_complete;
892
893
894
895 -- Start of comments
896 --API name : update_ame_status
897 --Type : Private.
898 --Function : This procedure is called by workflow after each approver's response.
899 -- Updates AME approver's approval status, updates Contract's approval hisotry,
900 -- Calls ame_api2.getNextApprovers1 to check if more approvers exists. Return
901 -- COMPLETE:APPROVED if last approver approved the contract,
902 -- COMPLETE:REJECTED if current approver rejected the contract, COMPLETE: if more
903 -- exist for this contract approvers.
904 --Pre-reqs : None.
905 --Parameters :
906 --IN : itemtype IN VARCHAR2 Required
907 -- Workflow item type parameter
908 -- : itemkey IN VARCHAR2 Required
909 -- Workflow item key parameter
910 -- : actid IN VARCHAR2 Required
911 -- Workflow actid parameter
912 -- : funcmode IN VARCHAR2 Required
913 -- Workflow function mode parameter
914 --OUT : resultout OUT VARCHAR2(1)
915 -- Workflow standard out parameter
916 -- Note :
917 -- End of comments
918 PROCEDURE update_ame_status(
919 itemtype IN varchar2,
920 itemkey IN varchar2,
921 actid IN number,
922 funcmode IN varchar2,
923 resultout OUT nocopy varchar2
924 ) IS
925
926 l_contract_id OKC_REP_CON_APPROVALS.contract_id%type;
927 l_contract_version OKC_REP_CON_APPROVALS.contract_version_num%type;
928 l_approver_record2 ame_util.approverRecord2;
929 l_approver_id number;
930 l_approval_status VARCHAR2(30);
931 l_recipient_name FND_USER.user_name%type;
932 l_action_code OKC_REP_CON_APPROVALS.action_code%type;
933 l_wf_note VARCHAR2(2000);
934 l_api_name VARCHAR2(30);
935 l_return_status VARCHAR2(1);
936 l_msg_count NUMBER;
937 l_msg_data VARCHAR2(2000);
938 l_process_complete_yn varchar2(1);
939 l_next_approvers ame_util.approversTable2;
940 l_item_indexes ame_util.idList;
941 l_item_classes ame_util.stringList;
942 l_item_class_names ame_util.stringList;
943 l_item_ids ame_util.stringList;
944 l_item_sources ame_util.longStringList;
945
946 l_approver_type VARCHAR2(100);
947 l_approver_name VARCHAR2(100);
948
949 CURSOR notif_csr (p_notification_id NUMBER) IS
950 SELECT fu.user_id user_id, fu.user_name user_name,
951 fu1.user_id original_user_id,fu1.user_name original_user_name
952 FROM fnd_user fu, wf_notifications wfn, fnd_user fu1
953 WHERE fu.user_name = wfn.recipient_role
954 AND fu1.user_name = wfn.original_recipient
955 AND wfn.notification_id = p_notification_id ;
956
957 notif_rec notif_csr%ROWTYPE;
958
959 BEGIN
960
961 l_api_name := 'update_ame_status';
962
963 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
964 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
965 'Entered OKC_REP_WF_PVT.update_ame_status');
966 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
967 'Item Type is: ' || itemtype);
968 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
969 'Item Key is: ' || itemkey);
970 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
971 'actid is: ' || to_char(actid));
972 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
973 'Function mode is: ' || funcmode);
974 END IF;
975 -- Get contract id and version attributes
976 l_contract_id := wf_engine.GetItemAttrNumber(
977 itemtype => itemtype,
978 itemkey => itemkey,
979 aname => 'CONTRACT_ID');
980 l_contract_version := wf_engine.GetItemAttrNumber(
981 itemtype => itemtype,
982 itemkey => itemkey,
983 aname => 'CONTRACT_VERSION');
984 -- Get the approver comments
985 l_wf_note := WF_NOTIFICATION.GetAttrText(
986 nid => WF_ENGINE.context_nid,
987 aname => 'WF_NOTE');
988 -- Get the approval status
989 l_approval_status := WF_NOTIFICATION.GetAttrText(
990 nid => WF_ENGINE.context_nid,
991 aname => 'RESULT');
992 --14758583 : kkolukul : HR position support
993 l_approver_type := WF_NOTIFICATION.GetAttrText(
994 nid => WF_ENGINE.context_nid,
995 aname => 'APPROVER_TYPE');
996 l_approver_name := WF_NOTIFICATION.GetAttrText(
997 nid => WF_ENGINE.context_nid,
998 aname => 'APPROVER_POS_NAME');
999
1000 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1002 g_module || l_api_name,
1003 'Contract Id is: ' || to_char(l_contract_id));
1004 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1005 g_module || l_api_name,
1006 'Contract Version is: ' || to_char(l_contract_version));
1007 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1008 g_module || l_api_name,
1009 'Approver Notes : ' || l_wf_note);
1010 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1011 g_module || l_api_name,
1012 'Approver action is : ' || l_approval_status);
1013 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1014 g_module || l_api_name,
1015 'Approver Type is : ' || l_approver_type);
1016 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1017 g_module || l_api_name,
1018 'Approver Name is : ' || l_approver_name);
1019 END IF;
1020 -- Get the notification recipient
1021 OPEN notif_csr(WF_ENGINE.context_nid);
1022 FETCH notif_csr into notif_rec;
1023 IF(notif_csr%NOTFOUND) THEN
1024 RAISE NO_DATA_FOUND;
1025 END IF;
1026 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1027 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1028 g_module || l_api_name,
1029 'Approver Name is : ' || notif_rec.user_name);
1030 END IF;
1031 -- l_approver_record2.name := notif_rec.user_name;
1032 --14758583 : kkolukul : HR position support
1033 IF (l_approver_type = ame_util.posOrigSystem) THEN
1034 l_approver_record2.name := l_approver_name;
1035 ELSE
1036 l_approver_record2.name := notif_rec.original_user_name;
1037 END IF;
1038 -- FUNCTION MODE IS RESPOND.
1039 IF (funcmode = 'RESPOND') THEN
1040 -- CURRENT APPROVER APPROVED THE CONTRACTS
1041 IF (l_approval_status = G_WF_STATUS_APPROVED) THEN
1042 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1043 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1044 g_module || l_api_name,
1045 'Approver action is : ' || G_WF_STATUS_APPROVED);
1046 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1047 g_module || l_api_name,
1048 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1049 END IF;
1050 OKC_REP_UTIL_PVT.add_approval_hist_record(
1051 p_api_version => 1.0,
1052 p_init_msg_list => FND_API.G_FALSE,
1053 p_contract_id => l_contract_id,
1054 p_contract_version => l_contract_version,
1055 p_action_code => G_STATUS_APPROVED,
1056 p_user_id => notif_rec.user_id,
1057 p_note => l_wf_note,
1058 x_msg_data => l_msg_data,
1059 x_msg_count => l_msg_count,
1060 x_return_status => l_return_status);
1061 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1063 g_module || l_api_name,
1064 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1065 END IF;
1066 -------------------------------------------------------
1067 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1068 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1069 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1070 RAISE OKC_API.G_EXCEPTION_ERROR;
1071 END IF;
1072 --------------------------------------------------------
1073 l_approver_record2.approval_status := ame_util.approvedStatus;
1074 ame_api2.updateApprovalStatus(
1075 applicationIdIn => G_APPLICATION_ID,
1076 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1077 approverIn => l_approver_record2,
1078 transactionTypeIn => G_TRANSACTION_TYPE);
1079 -- resultout := 'COMPLETE:' || G_WF_STATUS_APPROVED;
1080
1081 -- CURRENT APPROVER APPROVED THE CONTRACTS
1082 ELSIF (l_approval_status = G_WF_STATUS_REJECTED) THEN
1083 -- Add a record in ONC_REP_CON_APPROVALS table.
1084 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1085 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1086 g_module || l_api_name,
1087 'Approver action is : ' || G_WF_STATUS_REJECTED);
1088 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1089 g_module || l_api_name,
1090 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1091 END IF;
1092 OKC_REP_UTIL_PVT.add_approval_hist_record(
1093 p_api_version => 1.0,
1094 p_init_msg_list => FND_API.G_FALSE,
1095 p_contract_id => l_contract_id,
1096 p_contract_version => l_contract_version,
1097 p_action_code => G_STATUS_REJECTED,
1098 p_user_id => notif_rec.user_id,
1099 p_note => l_wf_note,
1100 x_msg_data => l_msg_data,
1101 x_msg_count => l_msg_count,
1102 x_return_status => l_return_status);
1103 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1105 g_module || l_api_name,
1106 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1107 END IF;
1108 -------------------------------------------------------
1109 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1110 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1111 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1112 RAISE OKC_API.G_EXCEPTION_ERROR;
1113 END IF;
1114 --------------------------------------------------------
1115
1116 l_approver_record2.approval_status := ame_util.rejectStatus;
1117 -- Update AME approval status
1118 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1119 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1120 g_module || l_api_name,
1121 'Calling ame_api2.updateApprovalStatus');
1122 END IF;
1123 ame_api2.updateApprovalStatus(
1124 applicationIdIn => G_APPLICATION_ID,
1125 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1126 approverIn => l_approver_record2,
1127 transactionTypeIn => G_TRANSACTION_TYPE);
1128 END IF; -- (l_approval_status = G_WF_STATUS_APPROVED)
1129 CLOSE notif_csr;
1130 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1131 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1132 g_module || l_api_name,
1133 'resultout value is: ' || resultout);
1134 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1135 g_module || l_api_name,
1136 'Leaving OKC_REP_WF_PVT.update_ame_status from funcmode=RESPOND');
1137 END IF;
1138 END IF; -- (funcmode = 'RESPOND')
1139
1140
1141 IF (funcmode = 'RUN') THEN
1142 IF (l_approval_status = G_WF_STATUS_APPROVED) THEN
1143 resultout := 'COMPLETE:' || G_WF_STATUS_APPROVED;
1144 ELSIF (l_approval_status = G_WF_STATUS_REJECTED) THEN
1145 resultout := 'COMPLETE:' || G_WF_STATUS_REJECTED;
1146 ELSIF (l_approval_status = G_WF_STATUS_MORE_APPROVERS) THEN
1147 resultout := 'COMPLETE:' || G_WF_STATUS_MORE_APPROVERS;
1148 ELSE resultout := 'COMPLETE:';
1149 END IF;
1150 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1151 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1152 g_module || l_api_name,
1153 'Leaving OKC_REP_WF_PVT.update_ame_status from funcmode=RUN');
1154 END IF;
1155 CLOSE notif_csr;
1156 RETURN;
1157 END IF; -- (funcmode = 'RUN')
1158
1159 IF (funcmode = 'TIMEOUT') THEN
1160 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1161 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_module || l_api_name,
1162 'In OKC_REP_WF_PVT.update_ame_status funcmode=TIMEOUT');
1163 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,g_module || l_api_name,
1164 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1165 END IF;
1166 OKC_REP_UTIL_PVT.add_approval_hist_record(
1167 p_api_version => 1.0,
1168 p_init_msg_list => FND_API.G_FALSE,
1169 p_contract_id => l_contract_id,
1170 p_contract_version => l_contract_version,
1171 p_action_code => G_STATUS_TIMEOUT,
1172 p_user_id => notif_rec.user_id,
1173 p_note => l_wf_note,
1174 x_msg_data => l_msg_data,
1175 x_msg_count => l_msg_count,
1176 x_return_status => l_return_status);
1177 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1178 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1179 g_module || l_api_name,
1180 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1181 END IF;
1182 -------------------------------------------------------
1183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1184 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1185 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1186 RAISE OKC_API.G_EXCEPTION_ERROR;
1187 END IF;
1188 --------------------------------------------------------
1189 l_approver_record2.approval_status := ame_util.noResponseStatus;
1190
1191 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1193 g_module || l_api_name,
1194 'Calling ame_api2.updateApprovalStatus');
1195 END IF;
1196 ame_api2.updateApprovalStatus(
1197 applicationIdIn => G_APPLICATION_ID,
1198 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1199 approverIn => l_approver_record2,
1200 transactionTypeIn => G_TRANSACTION_TYPE);
1201
1202 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1204 g_module || l_api_name,
1205 'Leaving OKC_REP_WF_PVT.update_ame_status from funcmode=TIMEOUT');
1206 END IF;
1207 resultout := 'COMPLETE:';
1208 CLOSE notif_csr;
1209 RETURN;
1210 END IF; -- (funcmode = 'TIMEOUT')
1211
1212 exception
1213 when others then
1214 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1216 g_module || l_api_name,
1217 '618: Leaving OKC_REP_WF_PVT.update_ame_status with exceptions ' || sqlerrm);
1218 END IF;
1219 --close cursors
1220 IF (notif_csr%ISOPEN) THEN
1221 CLOSE notif_csr ;
1222 END IF;
1223 wf_core.context('OKC_REP_WF_PVT',
1224 'update_ame_status',
1225 itemtype,
1226 itemkey,
1227 to_char(actid),
1228 funcmode);
1229 raise;
1230 END update_ame_status;
1231
1232
1233 -- Start of comments
1234 --API name : update_ame_status_detailed
1235 --Type : Private.
1236 --Function : Same as updated_ame_status. This API calls ame_api6.updateApprovalStatus to update the notification
1237 -- text as well.
1238 --Pre-reqs : None.
1239 --Parameters :
1240 --IN : itemtype IN VARCHAR2 Required
1241 -- Workflow item type parameter
1242 -- : itemkey IN VARCHAR2 Required
1243 -- Workflow item key parameter
1244 -- : actid IN VARCHAR2 Required
1245 -- Workflow actid parameter
1246 -- : funcmode IN VARCHAR2 Required
1247 -- Workflow function mode parameter
1248 --OUT : resultout OUT VARCHAR2(1)
1249 -- Workflow standard out parameter
1250 -- Note :
1251 -- End of comments
1252 PROCEDURE update_ame_status_detailed(
1253 itemtype IN varchar2,
1254 itemkey IN varchar2,
1255 actid IN number,
1256 funcmode IN varchar2,
1257 resultout OUT nocopy varchar2
1258 ) IS
1259
1260 l_contract_id OKC_REP_CON_APPROVALS.contract_id%type;
1261 l_contract_version OKC_REP_CON_APPROVALS.contract_version_num%type;
1262 l_approver_record2 ame_util.approverRecord2;
1263 l_notification_record ame_util2.notificationRecord;
1264 l_approver_id number;
1265 l_approval_status VARCHAR2(30);
1266 l_recipient_name FND_USER.user_name%type;
1267 l_action_code OKC_REP_CON_APPROVALS.action_code%type;
1268 l_wf_note VARCHAR2(2000);
1269 l_api_name VARCHAR2(30);
1270 l_return_status VARCHAR2(1);
1271 l_msg_count NUMBER;
1272 l_msg_data VARCHAR2(2000);
1273 l_process_complete_yn varchar2(1);
1274 l_next_approvers ame_util.approversTable2;
1275 l_item_indexes ame_util.idList;
1276 l_item_classes ame_util.stringList;
1277 l_item_class_names ame_util.stringList;
1278 l_item_ids ame_util.stringList;
1279 l_item_sources ame_util.longStringList;
1280
1281 l_action_code_fwd VARCHAR2(250);
1282 l_recipient_id NUMBER;
1283 l_recipient_record2 ame_util.approverRecord2;
1284 l_approver_type VARCHAR2(100);
1285 l_approver_name VARCHAR2(100);
1286
1287 l_forwardTo varchar2(240);
1288
1289 CURSOR notif_csr (p_notification_id NUMBER) IS
1290 SELECT fu.user_id user_id, fu.user_name user_name,
1291 fu1.user_id original_user_id,fu1.user_name original_user_name
1292 FROM fnd_user fu, wf_notifications wfn, fnd_user fu1
1293 WHERE fu.user_name = wfn.recipient_role
1294 AND fu1.user_name = wfn.original_recipient
1295 AND wfn.notification_id = p_notification_id ;
1296
1297 notif_rec notif_csr%ROWTYPE;
1298 --Bug 16231003
1299 l_group_id NUMBER;
1300
1301 BEGIN
1302
1303 l_api_name := 'update_ame_status';
1304
1305 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1306 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1307 'Entered OKC_REP_WF_PVT.update_ame_status_detailed');
1308 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1309 'Item Type is: ' || itemtype);
1310 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1311 'Item Key is: ' || itemkey);
1312 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1313 'actid is: ' || to_char(actid));
1314 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1315 'Function mode is: ' || funcmode);
1316 END IF;
1317 -- Get contract id and version attributes
1318 l_contract_id := wf_engine.GetItemAttrNumber(
1319 itemtype => itemtype,
1320 itemkey => itemkey,
1321 aname => 'CONTRACT_ID');
1322 l_contract_version := wf_engine.GetItemAttrNumber(
1323 itemtype => itemtype,
1324 itemkey => itemkey,
1325 aname => 'CONTRACT_VERSION');
1326 -- Get the approver comments
1327 l_wf_note := WF_NOTIFICATION.GetAttrText(
1328 nid => WF_ENGINE.context_nid,
1329 aname => 'WF_NOTE');
1330 -- Get the approval status
1331 l_approval_status := WF_NOTIFICATION.GetAttrText(
1332 nid => WF_ENGINE.context_nid,
1333 aname => 'RESULT');
1334 -- 14758583 : kkolukul : HR position support
1335 l_approver_type := WF_NOTIFICATION.GetAttrText(
1336 nid => WF_ENGINE.context_nid,
1337 aname => 'APPROVER_TYPE');
1338 l_approver_name := WF_NOTIFICATION.GetAttrText(
1339 nid => WF_ENGINE.context_nid,
1340 aname => 'APPROVER_POS_NAME');
1341 --Bug 16231003
1342 l_group_id := WF_NOTIFICATION.GetAttrText(
1343 nid => WF_ENGINE.context_nid,
1344 aname => 'APPROVER_GROUP_ID');
1345
1346 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1347 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1348 g_module || l_api_name,
1349 'Contract Id is: ' || to_char(l_contract_id));
1350 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1351 g_module || l_api_name,
1352 'Contract Version is: ' || to_char(l_contract_version));
1353 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1354 g_module || l_api_name,
1355 'Approver Notes : ' || l_wf_note);
1356 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1357 g_module || l_api_name,
1358 'Approver action is : ' || l_approval_status);
1359 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1360 g_module || l_api_name,
1361 'Approver type is : ' || l_approver_type);
1362 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1363 g_module || l_api_name,
1364 'Approver pos name is : ' || l_approver_name);
1365 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1366 g_module || l_api_name,
1367 'Approver group Id is : ' || l_group_id);
1368
1369 END IF;
1370 -- Get the notification recipient
1371 OPEN notif_csr(WF_ENGINE.context_nid);
1372 FETCH notif_csr into notif_rec;
1373 IF(notif_csr%NOTFOUND) THEN
1374 RAISE NO_DATA_FOUND;
1375 END IF;
1376 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1377 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1378 g_module || l_api_name,
1379 'Approver Name is : ' || notif_rec.user_name);
1380 END IF;
1381 -- l_approver_record2.name := notif_rec.user_name;
1382 -- 14758583 : kkolukul : HR position support
1383 IF (l_approver_type = ame_util.posOrigSystem) THEN
1384 l_approver_record2.name := l_approver_name;
1385 ELSE
1386 l_approver_record2.name := notif_rec.original_user_name;
1387 END IF;
1388 --Bug 16231003
1389
1390 l_approver_record2.group_or_chain_id := l_group_id;
1391
1392
1393 l_notification_record.notification_id := WF_ENGINE.context_nid;
1394 l_notification_record.user_comments := l_wf_note;
1395 -- FUNCTION MODE IS RESPOND.
1396 IF (funcmode = 'RESPOND') THEN
1397 -- CURRENT APPROVER APPROVED THE CONTRACTS
1398 IF (l_approval_status = G_WF_STATUS_APPROVED) THEN
1399 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1400 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1401 g_module || l_api_name,
1402 'Approver action is : ' || G_WF_STATUS_APPROVED);
1403 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1404 g_module || l_api_name,
1405 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1406 END IF;
1407 OKC_REP_UTIL_PVT.add_approval_hist_record(
1408 p_api_version => 1.0,
1409 p_init_msg_list => FND_API.G_FALSE,
1410 p_contract_id => l_contract_id,
1411 p_contract_version => l_contract_version,
1412 p_action_code => G_STATUS_APPROVED,
1413 p_user_id => notif_rec.user_id,
1414 p_note => l_wf_note,
1415 x_msg_data => l_msg_data,
1416 x_msg_count => l_msg_count,
1417 x_return_status => l_return_status);
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 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1422 END IF;
1423 -------------------------------------------------------
1424 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1425 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1426 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1427 RAISE OKC_API.G_EXCEPTION_ERROR;
1428 END IF;
1429 --------------------------------------------------------
1430 l_approver_record2.approval_status := ame_util.approvedStatus;
1431 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1432 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1433 g_module || l_api_name,
1434 'Calling ame_api6.updateApprovalStatus');
1435 END IF;
1436 ame_api6.updateApprovalStatus(
1437 applicationIdIn => G_APPLICATION_ID,
1438 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1439 approverIn => l_approver_record2,
1440 transactionTypeIn => G_TRANSACTION_TYPE,
1441 notificationIn => l_notification_record);
1442 -- resultout := 'COMPLETE:' || G_WF_STATUS_APPROVED;
1443
1444 -- CURRENT APPROVER APPROVED THE CONTRACTS
1445 ELSIF (l_approval_status = G_WF_STATUS_REJECTED) THEN
1446 -- Add a record in ONC_REP_CON_APPROVALS table.
1447 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1448 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1449 g_module || l_api_name,
1450 'Approver action is : ' || G_WF_STATUS_REJECTED);
1451 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1452 g_module || l_api_name,
1453 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1454 END IF;
1455 OKC_REP_UTIL_PVT.add_approval_hist_record(
1456 p_api_version => 1.0,
1457 p_init_msg_list => FND_API.G_FALSE,
1458 p_contract_id => l_contract_id,
1459 p_contract_version => l_contract_version,
1460 p_action_code => G_STATUS_REJECTED,
1461 p_user_id => notif_rec.user_id,
1462 p_note => l_wf_note,
1463 x_msg_data => l_msg_data,
1464 x_msg_count => l_msg_count,
1465 x_return_status => l_return_status);
1466 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1467 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1468 g_module || l_api_name,
1469 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1470 END IF;
1471 -------------------------------------------------------
1472 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1473 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1474 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1475 RAISE OKC_API.G_EXCEPTION_ERROR;
1476 END IF;
1477 --------------------------------------------------------
1478
1479 l_approver_record2.approval_status := ame_util.rejectStatus;
1480 -- Update AME approval status
1481 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1483 g_module || l_api_name,
1484 'Calling ame_api6.updateApprovalStatus');
1485 END IF;
1486 ame_api6.updateApprovalStatus(
1487 applicationIdIn => G_APPLICATION_ID,
1488 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1489 approverIn => l_approver_record2,
1490 transactionTypeIn => G_TRANSACTION_TYPE,
1491 notificationIn => l_notification_record);
1492
1493 ELSIF (l_approval_status = G_WF_APPROVE_FORWARD ) THEN
1494
1495 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1496 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1497 g_module || l_api_name,
1498 'Approver action is : ' || G_WF_APPROVE_FORWARD);
1499 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1500 g_module || l_api_name,
1501 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1502 END IF;
1503
1504
1505 l_forwardTo := wf_notification.GetAttrText(WF_ENGINE.context_nid, 'FORWARD_TO_USERNAME_RESPONSE');
1506
1507 if(l_forwardTo is null) then
1508 fnd_message.set_name('OKC', 'OKC_WF_NOTIF_NO_USER');
1509 app_exception.raise_exception;
1510 end if;
1511
1512
1513 OKC_REP_UTIL_PVT.add_approval_hist_record(
1514 p_api_version => 1.0,
1515 p_init_msg_list => FND_API.G_FALSE,
1516 p_contract_id => l_contract_id,
1517 p_contract_version => l_contract_version,
1518 p_action_code => G_STATUS_APPROVED,
1519 p_user_id => notif_rec.user_id,
1520 p_note => l_wf_note,
1521 x_msg_data => l_msg_data,
1522 x_msg_count => l_msg_count,
1523 x_return_status => l_return_status);
1524 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1525 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1526 g_module || l_api_name,
1527 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1528 END IF;
1529 -------------------------------------------------------
1530 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1531 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1532 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1533 RAISE OKC_API.G_EXCEPTION_ERROR;
1534 END IF;
1535 --------------------------------------------------------
1536
1537 --for forward action
1538 l_recipient_record2.name := l_forwardTo ;
1539
1540 SELECT user_id INTO l_recipient_id
1541 FROM fnd_user
1542 WHERE user_name = l_recipient_record2.name;
1543 --l_recipient_record2.name := l_recipient;
1544
1545
1546 l_approver_record2.approval_status := ame_util.approveAndForwardStatus;
1547
1548
1549 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1550 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
1551 'Approver action is : ' || l_action_code);
1552 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
1553 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1554 END IF;
1555 l_action_code:= G_WF_APPROVE_FORWARD;
1556 OKC_REP_UTIL_PVT.add_approval_hist_record(
1557 p_api_version => 1.0,
1558 p_init_msg_list => FND_API.G_FALSE,
1559 p_contract_id => l_contract_id,
1560 p_contract_version => l_contract_version,
1561 p_action_code => l_action_code,
1562 p_user_id => notif_rec.user_id,
1563 p_note => l_wf_note,
1564 x_msg_data => l_msg_data,
1565 x_msg_count => l_msg_count,
1566 x_return_status => l_return_status,
1567 p_forward_user_id => l_recipient_id);
1568 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1569 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
1570 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1571 END IF;
1572
1573 -------------------------------------------------------
1574 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1575 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1576 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1577 RAISE OKC_API.G_EXCEPTION_ERROR;
1578 END IF;
1579 --------------------------------------------------------
1580
1581 -- l_approver_record2.approval_status := ame_util.forwardStatus;
1582 -- Update AME approval status
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 'Calling ame_api6.updateApprovalStatus');
1587 END IF;
1588
1589
1590 ame_api6.updateApprovalStatus(
1591 applicationIdIn => G_APPLICATION_ID,
1592 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1593 approverIn => l_approver_record2,
1594 transactionTypeIn => G_TRANSACTION_TYPE,
1595 notificationIn => l_notification_record,
1596 forwardeeIn => l_recipient_record2);
1597
1598
1599 END IF; -- (l_approval_status = G_WF_STATUS_APPROVED)
1600 CLOSE notif_csr;
1601 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1602 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1603 g_module || l_api_name,
1604 'resultout value is: ' || resultout);
1605 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1606 g_module || l_api_name,
1607 'Leaving OKC_REP_WF_PVT.update_ame_status_detailed from funcmode=RESPOND');
1608 END IF;
1609 END IF; -- (funcmode = 'RESPOND')
1610
1611
1612 IF (funcmode = 'RUN') THEN
1613 IF (l_approval_status = G_WF_STATUS_APPROVED) THEN
1614 resultout := 'COMPLETE:' || G_WF_STATUS_APPROVED;
1615 ELSIF (l_approval_status = G_WF_STATUS_REJECTED) THEN
1616 resultout := 'COMPLETE:' || G_WF_STATUS_REJECTED;
1617 ELSIF (l_approval_status = G_WF_STATUS_MORE_APPROVERS) THEN
1618 resultout := 'COMPLETE:' || G_WF_STATUS_MORE_APPROVERS;
1619 ELSE resultout := 'COMPLETE:';
1620 END IF;
1621 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1622 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1623 g_module || l_api_name,
1624 'Leaving OKC_REP_WF_PVT.update_ame_status_detailed from funcmode=RUN');
1625 END IF;
1626 CLOSE notif_csr;
1627 RETURN;
1628 END IF; -- (funcmode = 'RUN')
1629
1630 IF (funcmode = 'TIMEOUT') THEN
1631 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1632 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_module || l_api_name,
1633 'In OKC_REP_WF_PVT.update_ame_status funcmode=TIMEOUT');
1634 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,g_module || l_api_name,
1635 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1636 END IF;
1637 OKC_REP_UTIL_PVT.add_approval_hist_record(
1638 p_api_version => 1.0,
1639 p_init_msg_list => FND_API.G_FALSE,
1640 p_contract_id => l_contract_id,
1641 p_contract_version => l_contract_version,
1642 p_action_code => G_STATUS_TIMEOUT,
1643 p_user_id => notif_rec.user_id,
1644 p_note => l_wf_note,
1645 x_msg_data => l_msg_data,
1646 x_msg_count => l_msg_count,
1647 x_return_status => l_return_status);
1648 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1649 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1650 g_module || l_api_name,
1651 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1652 END IF;
1653 -------------------------------------------------------
1654 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1655 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1656 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1657 RAISE OKC_API.G_EXCEPTION_ERROR;
1658 END IF;
1659 --------------------------------------------------------
1660 l_approver_record2.approval_status := ame_util.noResponseStatus;
1661
1662 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1663 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1664 g_module || l_api_name,
1665 'Calling ame_api6.updateApprovalStatus');
1666 END IF;
1667 ame_api6.updateApprovalStatus(
1668 applicationIdIn => G_APPLICATION_ID,
1669 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1670 approverIn => l_approver_record2,
1671 transactionTypeIn => G_TRANSACTION_TYPE,
1672 notificationIn => l_notification_record);
1673
1674 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1675 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1676 g_module || l_api_name,
1677 'Leaving OKC_REP_WF_PVT.update_ame_status_detailed from funcmode=TIMEOUT');
1678 END IF;
1679 resultout := 'COMPLETE:';
1680 CLOSE notif_csr;
1681 RETURN;
1682 END IF; -- (funcmode = 'TIMEOUT')
1683
1684 --kkolukul: 9825586 - Huaweii ER
1685 IF (funcmode = 'FORWARD' OR funcmode = 'TRANSFER') THEN
1686 l_recipient_record2.name := wf_engine.context_new_role;
1687
1688 SELECT user_id INTO l_recipient_id
1689 FROM fnd_user
1690 WHERE user_name = l_recipient_record2.name;
1691 --l_recipient_record2.name := l_recipient;
1692
1693 IF funcmode = 'FORWARD' THEN
1694 l_action_code := G_WF_STATUS_DELEGATED;
1695 ELSIF funcmode = 'TRANSFER' THEN
1696 l_action_code := G_WF_STATUS_TRANSFERRED;
1697 l_approver_record2.approval_status := ame_util.forwardStatus;
1698 END IF;
1699
1700 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1701 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
1702 'Approver action is : ' || l_action_code);
1703 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
1704 'Calling OKC_REP_UTIL_PVT.add_approval_hist_record');
1705 END IF;
1706 OKC_REP_UTIL_PVT.add_approval_hist_record(
1707 p_api_version => 1.0,
1708 p_init_msg_list => FND_API.G_FALSE,
1709 p_contract_id => l_contract_id,
1710 p_contract_version => l_contract_version,
1711 p_action_code => l_action_code,
1712 p_user_id => notif_rec.user_id,
1713 p_note => l_wf_note,
1714 x_msg_data => l_msg_data,
1715 x_msg_count => l_msg_count,
1716 x_return_status => l_return_status,
1717 p_forward_user_id => l_recipient_id);
1718 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1719 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
1720 'Completed OKC_REP_UTIL_PVT.add_approval_hist_record with return status: ' || l_return_status);
1721 END IF;
1722
1723 -------------------------------------------------------
1724 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1725 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1726 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1727 RAISE OKC_API.G_EXCEPTION_ERROR;
1728 END IF;
1729 --------------------------------------------------------
1730
1731 -- l_approver_record2.approval_status := ame_util.forwardStatus;
1732 -- Update AME approval status
1733 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1734 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1735 g_module || l_api_name,
1736 'Calling ame_api6.updateApprovalStatus');
1737 END IF;
1738 ame_api6.updateApprovalStatus(
1739 applicationIdIn => G_APPLICATION_ID,
1740 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
1741 approverIn => l_approver_record2,
1742 transactionTypeIn => G_TRANSACTION_TYPE,
1743 notificationIn => l_notification_record,
1744 forwardeeIn => l_recipient_record2);
1745
1746 CLOSE notif_csr;
1747 RETURN;
1748
1749 END IF; --(funcmode = 'FORWARD')
1750
1751 exception
1752 when others then
1753 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1754 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1755 g_module || l_api_name,
1756 '618: Leaving OKC_REP_WF_PVT.update_ame_status_detailed with exceptions ' || sqlerrm);
1757 END IF;
1758 --close cursors
1759 IF (notif_csr%ISOPEN) THEN
1760 CLOSE notif_csr ;
1761 END IF;
1762 wf_core.context('OKC_REP_WF_PVT',
1763 'update_ame_status_detailed',
1764 itemtype,
1765 itemkey,
1766 to_char(actid),
1767 funcmode);
1768 raise;
1769 END update_ame_status_detailed;
1770
1771
1772
1773
1774
1775
1776 -- Start of comments
1777 --API name : approve_contract
1778 --Type : Private.
1779 --Function : This procedure is called by workflow after the contract is approved. Updates Contract's status
1780 -- to approved and logs the status change in OKC_REP_CON_STATUS_HIST table.
1781 --Pre-reqs : None.
1782 --Parameters :
1783 --IN : itemtype IN VARCHAR2 Required
1784 -- Workflow item type parameter
1785 -- : itemkey IN VARCHAR2 Required
1786 -- Workflow item key parameter
1787 -- : actid IN VARCHAR2 Required
1788 -- Workflow actid parameter
1789 -- : funcmode IN VARCHAR2 Required
1790 -- Workflow function mode parameter
1791 --OUT : resultout OUT VARCHAR2(1)
1792 -- Workflow standard out parameter
1793 -- Note :
1794 -- End of comments
1795
1796 PROCEDURE approve_contract(
1797 itemtype IN varchar2,
1798 itemkey IN varchar2,
1799 actid IN number,
1800 funcmode IN varchar2,
1801 resultout OUT nocopy varchar2
1802 ) IS
1803
1804 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
1805 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
1806 l_api_name VARCHAR2(30);
1807 l_return_status VARCHAR2(1);
1808 l_msg_count NUMBER;
1809 l_msg_data VARCHAR2(2000);
1810
1811 l_activate_event_tbl EVENT_TBL_TYPE;
1812 l_update_event_tbl EVENT_TBL_TYPE;
1813 l_sync_flag VARCHAR2(1);
1814 l_expiration_date_matches_flag VARCHAR2(1);
1815 l_effective_date_matches_flag VARCHAR2(1);
1816 l_prev_signed_expiration_date OKC_REP_CONTRACTS_ALL.CONTRACT_EXPIRATION_DATE%TYPE;
1817 l_prev_signed_effective_date OKC_REP_CONTRACTS_ALL.CONTRACT_EXPIRATION_DATE%TYPE;
1818
1819
1820
1821 CURSOR contract_csr(p_contract_id NUMBER) IS
1822 SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date,esignature_required
1823 FROM OKC_REP_CONTRACTS_ALL
1824 WHERE contract_id = p_contract_id;
1825
1826 CURSOR arch_contract_csr (l_contract_version NUMBER,p_contract_id number) IS
1827 SELECT contract_effective_date, contract_expiration_date
1828 FROM OKC_REP_CONTRACT_VERS
1829 WHERE contract_id = p_contract_id
1830 AND contract_version_num = l_contract_version;
1831
1832 contract_rec contract_csr%ROWTYPE;
1833 arch_contract_rec arch_contract_csr%ROWTYPE;
1834
1835 BEGIN
1836
1837 l_api_name := 'approve_contract';
1838
1839 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1840 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1841 'Entered OKC_REP_WF_PVT.approve_contract');
1842 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1843 'Item Type is: ' || itemtype);
1844 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1845 'Item Key is: ' || itemkey);
1846 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1847 'actid is: ' || to_char(actid));
1848 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1849 'Function mode is: ' || funcmode);
1850 END IF;
1851 IF (funcmode = 'RUN') THEN
1852 l_contract_id := wf_engine.GetItemAttrNumber(
1853 itemtype => itemtype,
1854 itemkey => itemkey,
1855 aname => 'CONTRACT_ID');
1856 l_contract_version := wf_engine.GetItemAttrNumber(
1857 itemtype => itemtype,
1858 itemkey => itemkey,
1859 aname => 'CONTRACT_VERSION');
1860 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1861 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1862 g_module || l_api_name,
1863 'Contract Id is: ' || to_char(l_contract_id));
1864 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1865 g_module || l_api_name,
1866 'Contract Version is: ' || to_char(l_contract_version));
1867 END IF;
1868 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1869 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1870 'Calling OKC_REP_UTIL_PVT.change_contract_status');
1871 END IF;
1872 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
1873 OKC_REP_UTIL_PVT.change_contract_status(
1874 p_api_version => 1.0,
1875 p_init_msg_list => FND_API.G_FALSE,
1876 p_contract_id => l_contract_id,
1877 p_contract_version => l_contract_version,
1878 p_status_code => G_STATUS_APPROVED,
1879 p_user_id => fnd_global.user_id,
1880 p_note => NULL,
1881 x_msg_data => l_msg_data,
1882 x_msg_count => l_msg_count,
1883 x_return_status => l_return_status);
1884 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1885 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1886 g_module || l_api_name,
1887 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || l_return_status);
1888 END IF;
1889
1890 -----------------------------------------------------
1891 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1892 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1893 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1894 RAISE OKC_API.G_EXCEPTION_ERROR;
1895 END IF;
1896 --------------------------------------------------------
1897
1898
1899 -- Get effective dates and version of the contract.
1900 OPEN contract_csr(l_contract_id);
1901 FETCH contract_csr INTO contract_rec;
1902
1903 IF Nvl(contract_rec.ESignature_Required,'M') = 'N' THEN
1904
1905 -- We need to first version the deliverables
1906 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1907 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1908 'Calling OKC_DELIVERABLE_PROCESS_PVT.version_deliverables');
1909 END IF;
1910 OKC_DELIVERABLE_PROCESS_PVT.version_deliverables (
1911 p_api_version => 1.0,
1912 p_init_msg_list => FND_API.G_FALSE,
1913 p_doc_id => l_contract_id,
1914 p_doc_version => contract_rec.contract_version_num,
1915 p_doc_type => contract_rec.contract_type,
1916 x_return_status => l_return_status,
1917 x_msg_count => l_msg_count,
1918 x_msg_data => l_msg_data
1919 );
1920 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1922 'OKC_DELIVERABLE_PROCESS_PVT.version_deliverables return status is : '
1923 || l_return_status);
1924 END IF;
1925 -----------------------------------------------------
1926 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1927 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1928 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1929 RAISE OKC_API.G_EXCEPTION_ERROR;
1930 END IF;
1931 --------------------------------------------------------
1932
1933 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1934 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1935 'Latest signed version number is : '
1936 || contract_rec.latest_signed_ver_number);
1937 END IF;
1938 -- Now we need to activate deliverables
1939 if (contract_rec.latest_signed_ver_number IS NULL) THEN
1940 l_sync_flag := FND_API.G_FALSE;
1941 ELSE
1942 l_sync_flag := FND_API.G_TRUE;
1943 END IF;
1944
1945 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1947 'l_sync_flag is : ' || l_sync_flag);
1948 END IF;
1949 l_activate_event_tbl(1).event_code := G_CONTRACT_EXPIRE_EVENT;
1950 l_activate_event_tbl(1).event_date := contract_rec.contract_expiration_date;
1951
1952 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1953 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1954 'Calling OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables');
1955 END IF;
1956
1957 OKC_MANAGE_DELIVERABLES_GRP.activateDeliverables (
1958 p_api_version => 1.0,
1959 p_init_msg_list => FND_API.G_FALSE,
1960 p_commit => FND_API.G_FALSE,
1961 p_bus_doc_id => l_contract_id,
1962 p_bus_doc_type => contract_rec.contract_type,
1963 p_bus_doc_version => contract_rec.contract_version_num,
1964 p_event_code => G_CONTRACT_EFFECTIVE_EVENT,
1965 p_event_date => contract_rec.contract_effective_date,
1966 p_sync_flag => l_sync_flag,
1967 p_bus_doc_date_events_tbl => l_activate_event_tbl,
1968 x_msg_data => l_msg_data,
1969 x_msg_count => l_msg_count,
1970 x_return_status => l_return_status);
1971
1972 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1973 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1974 'OKC_DELIVERABLE_PROCESS_PVT.activateDeliverables return status is : '
1975 || l_return_status);
1976 END IF;
1977 -----------------------------------------------------
1978 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1979 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1980 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1981 RAISE OKC_API.G_EXCEPTION_ERROR;
1982 END IF;
1983 --------------------------------------------------------
1984
1985 -- Checking if we need to call deliverable's APIs for synch-ing
1986 IF (l_sync_flag = FND_API.G_TRUE) THEN
1987 -- Get the previous signed contract's expiration date
1988 -- Get effective dates and version of the contract.
1989 OPEN arch_contract_csr(contract_rec.latest_signed_ver_number,l_contract_id);
1990 FETCH arch_contract_csr INTO arch_contract_rec;
1991 IF(contract_csr%NOTFOUND) THEN
1992 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1993 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION ,
1994 G_MODULE||l_api_name,
1995 'Invalid Contract Id: '|| l_contract_id);
1996 END IF;
1997 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1998 p_msg_name => G_INVALID_CONTRACT_ID_MSG,
1999 p_token1 => G_CONTRACT_ID_TOKEN,
2000 p_token1_value => to_char(l_contract_id));
2001 RAISE FND_API.G_EXC_ERROR;
2002 -- RAISE NO_DATA_FOUND;
2003 END IF;
2004 l_prev_signed_effective_date := arch_contract_rec.contract_effective_date;
2005 l_prev_signed_expiration_date := arch_contract_rec.contract_expiration_date;
2006
2007 CLOSE arch_contract_csr;
2008 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2009 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2010 'Before checking if we need to call updateDeliverable and disableDeliverable()');
2011 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2012 'Prev signed expiration date: ' || trunc(l_prev_signed_expiration_date));
2013 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2014 'Current version expiration date: ' || trunc(contract_rec.contract_expiration_date));
2015 END IF;
2016 l_update_event_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
2017 l_update_event_tbl(1).event_date := contract_rec.contract_effective_date;
2018 l_update_event_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
2019 l_update_event_tbl(2).event_date := contract_rec.contract_expiration_date;
2020 -- If last signed version's expiration date is different from the current version's expiration date
2021 -- we need to call deliverables API for synching previous signed deliverables.
2022 -- This logic is executed to handle the null date scenarios
2023 IF (trunc(l_prev_signed_expiration_date)=trunc(contract_rec.contract_expiration_date)) THEN
2024 l_expiration_date_matches_flag := FND_API.G_TRUE;
2025 END IF;
2026
2027 IF (trunc(l_prev_signed_effective_date)=trunc(contract_rec.contract_effective_date)) THEN
2028 l_effective_date_matches_flag := FND_API.G_TRUE;
2029 END IF;
2030
2031 IF ((l_expiration_date_matches_flag = FND_API.G_FALSE ) OR (l_effective_date_matches_flag = FND_API.G_FALSE)) THEN
2032 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2033 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2034 'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
2035 END IF;
2036 OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
2037 p_api_version => 1.0,
2038 p_init_msg_list => FND_API.G_FALSE,
2039 p_commit => FND_API.G_FALSE,
2040 p_bus_doc_id => l_contract_id,
2041 p_bus_doc_type => contract_rec.contract_type,
2042 p_bus_doc_version => contract_rec.contract_version_num,
2043 p_bus_doc_date_events_tbl => l_update_event_tbl,
2044 x_msg_data => l_msg_data,
2045 x_msg_count => l_msg_count,
2046 x_return_status => l_return_status);
2047
2048 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2049 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2050 'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
2051 || l_return_status);
2052 END IF;
2053 -----------------------------------------------------
2054 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2056 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2057 RAISE OKC_API.G_EXCEPTION_ERROR;
2058 END IF;
2059 --------------------------------------------------------
2060 END IF; -- expiration date comparision
2061 -- Disable prev. version deliverables
2062 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2063 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2064 'Calling OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables');
2065 END IF;
2066 OKC_MANAGE_DELIVERABLES_GRP.disableDeliverables (
2067 p_api_version => 1.0,
2068 p_init_msg_list => FND_API.G_FALSE,
2069 p_commit => FND_API.G_FALSE,
2070 p_bus_doc_id => l_contract_id,
2071 p_bus_doc_type => contract_rec.contract_type,
2072 p_bus_doc_version => contract_rec.latest_signed_ver_number,
2073 x_msg_data => l_msg_data,
2074 x_msg_count => l_msg_count,
2075 x_return_status => l_return_status);
2076
2077 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2078 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2079 'OKC_DELIVERABLE_PROCESS_PVT.disableDeliverables return status is : '
2080 || l_return_status);
2081 END IF;
2082 -----------------------------------------------------
2083 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2084 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2085 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2086 RAISE OKC_API.G_EXCEPTION_ERROR;
2087 END IF;
2088 --------------------------------------------------------
2089 END IF; -- (l_sync_flag = 'Y')
2090 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2091 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2092 'Updating latest_signed_ver_number column');
2093 END IF;
2094 UPDATE okc_rep_contracts_all
2095 SET latest_signed_ver_number = contract_rec.contract_version_num
2096 WHERE contract_id = l_contract_id;
2097 CLOSE contract_csr;
2098
2099 END IF; --contract_rec.ESignature_Required='N'
2100
2101 resultout := 'COMPLETE:';
2102 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2103 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2104 g_module || l_api_name,
2105 'Leaving OKC_REP_WF_PVT.approve_contract');
2106 END IF;
2107 RETURN;
2108 END IF; -- (funcmode = 'RUN')
2109
2110 EXCEPTION
2111 WHEN others THEN
2112 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2113 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2114 g_module || l_api_name,
2115 'Leaving OKC_REP_WF_PVT.approve_contract with exceptions ' || sqlerrm);
2116 END IF;
2117 wf_core.context('OKC_REP_WF_PVT',
2118 'approve_contract',
2119 itemtype,
2120 itemkey,
2121 to_char(actid),
2122 funcmode);
2123 raise;
2124 END approve_contract;
2125
2126
2127
2128 -- Start of comments
2129 --API name : reject_contract
2130 --Type : Private.
2131 --Function : This procedure is called by workflow after the contract is rejected. Updates Contract's status
2132 -- to rejected and logs the status change in OKC_REP_CON_STATUS_HIST table.
2133 --Pre-reqs : None.
2134 --Parameters :
2135 --IN : itemtype IN VARCHAR2 Required
2136 -- Workflow item type parameter
2137 -- : itemkey IN VARCHAR2 Required
2138 -- Workflow item key parameter
2139 -- : actid IN VARCHAR2 Required
2140 -- Workflow actid parameter
2141 -- : funcmode IN VARCHAR2 Required
2142 -- Workflow function mode parameter
2143 --OUT : resultout OUT VARCHAR2(1)
2144 -- Workflow standard out parameter
2145 -- Note :
2146 -- End of comments
2147 PROCEDURE reject_contract(
2148 itemtype IN varchar2,
2149 itemkey IN varchar2,
2150 actid IN number,
2151 funcmode IN varchar2,
2152 resultout OUT nocopy varchar2
2153 ) IS
2154
2155 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2156 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
2157 l_api_name VARCHAR2(30);
2158 l_return_status VARCHAR2(1);
2159 l_msg_count NUMBER;
2160 l_msg_data VARCHAR2(2000);
2161
2162 BEGIN
2163
2164 l_api_name := 'reject_contract';
2165
2166 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2168 'Entered OKC_REP_WF_PVT.reject_contract');
2169 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2170 'Item Type is: ' || itemtype);
2171 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2172 'Item Key is: ' || itemkey);
2173 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2174 'actid is: ' || to_char(actid));
2175 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2176 'Function mode is: ' || funcmode);
2177 END IF;
2178 if (funcmode = 'RUN') then
2179 l_contract_id := wf_engine.GetItemAttrNumber(
2180 itemtype => itemtype,
2181 itemkey => itemkey,
2182 aname => 'CONTRACT_ID');
2183 l_contract_version := wf_engine.GetItemAttrNumber(
2184 itemtype => itemtype,
2185 itemkey => itemkey,
2186 aname => 'CONTRACT_VERSION');
2187 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2188 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2189 g_module || l_api_name,
2190 'Contract Id is: ' || to_char(l_contract_id));
2191 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2192 g_module || l_api_name,
2193 'Contract Version is: ' || to_char(l_contract_version));
2194 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2195 'Calling OKC_REP_UTIL_PVT.change_contract_status');
2196 END IF;
2197
2198 -- Update the contract status and add a record in OKC_REP_CON_STATUS_HIST table.
2199 OKC_REP_UTIL_PVT.change_contract_status(
2200 p_api_version => 1.0,
2201 p_init_msg_list => FND_API.G_FALSE,
2202 p_contract_id => l_contract_id,
2203 p_contract_version => l_contract_version,
2204 p_status_code => G_STATUS_REJECTED,
2205 p_user_id => fnd_global.user_id,
2206 p_note => NULL,
2207 x_msg_data => l_msg_data,
2208 x_msg_count => l_msg_count,
2209 x_return_status => l_return_status);
2210 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2211 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2212 g_module || l_api_name,
2213 'Completed OKC_REP_UTIL_PVT.change_contract_status with return status: ' || l_return_status);
2214 END IF;
2215 -----------------------------------------------------
2216 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2217 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2218 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2219 RAISE OKC_API.G_EXCEPTION_ERROR;
2220 END IF;
2221 --------------------------------------------------------
2222
2223 resultout := 'COMPLETE:';
2224 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2225 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2226 g_module || l_api_name,
2227 'Leaving OKC_REP_WF_PVT.reject_contract');
2228 END IF;
2229 RETURN;
2230 END IF; -- (funcmode = 'RUN')
2231 EXCEPTION
2232 WHEN others THEN
2233 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2234 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2235 g_module || l_api_name,
2236 'Leaving OKC_REP_WF_PVT.reject_contract with exceptions ' || sqlerrm);
2237 END IF;
2238 wf_core.context('OKC_REP_WF_PVT',
2239 'reject_contract',
2240 itemtype,
2241 itemkey,
2242 to_char(actid),
2243 funcmode);
2244 raise;
2245 END reject_contract;
2246
2247
2248
2249 -- Start of comments
2250 --API name : is_contract_approved
2251 --Type : Private.
2252 --Function : This procedure is called by workflow to determine if the contract is approved.
2253 --Pre-reqs : None.
2254 --Parameters :
2255 --IN : itemtype IN VARCHAR2 Required
2256 -- Workflow item type parameter
2257 -- : itemkey IN VARCHAR2 Required
2258 -- Workflow item key parameter
2259 -- : actid IN VARCHAR2 Required
2260 -- Workflow actid parameter
2261 -- : funcmode IN VARCHAR2 Required
2262 -- Workflow function mode parameter
2263 --OUT : resultout OUT VARCHAR2(1)
2264 -- Workflow standard out parameter
2265 -- Note :
2266 -- End of comments
2267 PROCEDURE is_contract_approved(
2268 itemtype in varchar2,
2269 itemkey in varchar2,
2270 actid in number,
2271 funcmode in varchar2,
2272 resultout out nocopy varchar2) IS
2273
2274 l_api_name VARCHAR2(30);
2275 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2276 l_process_complete_yn varchar2(1);
2277 l_next_approvers ame_util.approversTable2;
2278 l_item_indexes ame_util.idList;
2279 l_item_classes ame_util.stringList;
2280 l_item_ids ame_util.stringList;
2281 l_item_sources ame_util.longStringList;
2282 l_user_names varchar2(4000);
2283
2284 BEGIN
2285
2286 l_api_name := 'is_contract_approved';
2287
2288 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2289 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2290 'Entered OKC_REP_WF_PVT.is_contract_approved');
2291 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2292 'Item Type is: ' || itemtype);
2293 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2294 'Item Key is: ' || itemkey);
2295 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2296 'actid is: ' || to_char(actid));
2297 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2298 'Function mode is: ' || funcmode);
2299 END IF;
2300 IF (funcmode = 'RUN') then
2301 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2302 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2303 g_module || l_api_name,
2304 'Calling wf_engine.GetItemAttrNumber to get CONTRACT_ID');
2305 END IF;
2306 l_contract_id := wf_engine.GetItemAttrNumber(
2307 itemtype => itemtype,
2308 itemkey => itemkey,
2309 aname => 'CONTRACT_ID');
2310 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2311 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2312 g_module || l_api_name,
2313 'Contract Id is: ' || to_char(l_contract_id));
2314 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2315 g_module || l_api_name,
2316 'Calling ame_api.getNextApprover to get the approver id');
2317 END IF;
2318 ame_api2.getNextApprovers1(
2319 applicationIdIn => G_APPLICATION_ID,
2320 transactionTypeIn => G_TRANSACTION_TYPE,
2321 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
2322 flagApproversAsNotifiedIn => ame_util.booleanFalse,
2323 approvalProcessCompleteYNOut => l_process_complete_yn,
2324 nextApproversOut => l_next_approvers,
2325 itemIndexesOut => l_item_indexes,
2326 itemClassesOut => l_item_classes,
2327 itemIdsOut => l_item_ids,
2328 itemSourcesOut => l_item_sources);
2329
2330 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2331 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2332 g_module || l_api_name,
2333 'Number of approvers: ' || to_char(l_next_approvers.count));
2334 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2335 g_module || l_api_name,
2336 'l_process_complete_yn is is_contract_approved: ' || l_process_complete_yn);
2337 END IF;
2338 IF (l_process_complete_yn = ame_util.booleanTrue) THEN
2339 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2340 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
2341 'The contract is approved');
2342 END IF;
2343 resultout := 'COMPLETE:T';
2344 ELSE
2345 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2346 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
2347 'The contract is rejected');
2348 END IF;
2349 resultout := 'COMPLETE:F';
2350 END IF;
2351 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2352 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2353 g_module || l_api_name,
2354 'Leaving OKC_REP_WF_PVT.is_contract_approved from funcmode=RUN');
2355 END IF;
2356 RETURN;
2357 END IF; -- (funcmode = 'RUN')
2358
2359 IF (funcmode = 'CANCEL') THEN
2360 resultout := 'COMPLETE:';
2361 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2362 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2363 g_module || l_api_name,
2364 'Leaving OKC_REP_WF_PVT.is_contract_approved from funcmode=CANCEL');
2365 END IF;
2366 RETURN;
2367 END IF; -- (funcmode = 'CANCEL')
2368
2369 IF (funcmode = 'TIMEOUT') THEN
2370 resultout := 'COMPLETE:';
2371 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2372 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2373 g_module || l_api_name,
2374 'Leaving OKC_REP_WF_PVT.is_contract_approved from funcmode=TIMEOUT');
2375 END IF;
2376 RETURN;
2377 END IF; -- (funcmode = 'TIMEOUT')
2378
2379 EXCEPTION
2380 WHEN others THEN
2381 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2382 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2383 g_module || l_api_name,
2384 'Leaving OKC_REP_WF_PVT.is_contract_approved with exceptions ' || sqlerrm);
2385 END IF;
2386 wf_core.context('OKC_REP_WF_PVT',
2387 'is_contract_approved',
2388 itemtype,
2389 itemkey,
2390 to_char(actid),
2391 funcmode);
2392 raise;
2393 END is_contract_approved;
2394
2395
2396
2397 -- Start of comments
2398 --API name : is_contract_approved_detailed
2399 --Type : Private.
2400 --Function : This procedure is called by workflow to determine if the contract is approved. Uses
2401 -- the detailed values of ame param approvalProcessCompleteYNOut. Is used in
2402 -- Master approval process.
2403 --Pre-reqs : None.
2404 --Parameters :
2405 --IN : itemtype IN VARCHAR2 Required
2406 -- Workflow item type parameter
2407 -- : itemkey IN VARCHAR2 Required
2408 -- Workflow item key parameter
2409 -- : actid IN VARCHAR2 Required
2410 -- Workflow actid parameter
2411 -- : funcmode IN VARCHAR2 Required
2412 -- Workflow function mode parameter
2413 --OUT : resultout OUT VARCHAR2(1)
2414 -- Workflow standard out parameter
2415 -- Note :
2416 -- End of comments
2417 PROCEDURE is_contract_approved_detailed(
2418 itemtype in varchar2,
2419 itemkey in varchar2,
2420 actid in number,
2421 funcmode in varchar2,
2422 resultout out nocopy varchar2) IS
2423
2424 l_api_name VARCHAR2(30);
2425 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2426 l_process_complete_yn varchar2(1);
2427 l_next_approvers ame_util.approversTable2;
2428 l_item_indexes ame_util.idList;
2429 l_item_classes ame_util.stringList;
2430 l_item_ids ame_util.stringList;
2431 l_item_sources ame_util.longStringList;
2432 l_user_names varchar2(4000);
2433
2434 CURSOR wf_process_csr IS
2435 SELECT item_key FROM wf_items
2436 WHERE item_type=itemtype
2437 AND item_key like itemkey || '_' || '%'
2438 and end_date is null;
2439
2440 wf_process_rec wf_process_csr%ROWTYPE;
2441
2442 BEGIN
2443
2444 l_api_name := 'is_contract_approved_detailed';
2445
2446 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2447 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2448 'Entered OKC_REP_WF_PVT.is_contract_approved');
2449 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2450 'Item Type is: ' || itemtype);
2451 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2452 'Item Key is: ' || itemkey);
2453 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2454 'actid is: ' || to_char(actid));
2455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2456 'Function mode is: ' || funcmode);
2457 END IF;
2458 IF (funcmode = 'RUN') then
2459 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2461 g_module || l_api_name,
2462 'Calling wf_engine.GetItemAttrNumber to get CONTRACT_ID');
2463 END IF;
2464 l_contract_id := wf_engine.GetItemAttrNumber(
2465 itemtype => itemtype,
2466 itemkey => itemkey,
2467 aname => 'CONTRACT_ID');
2468 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2469 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2470 g_module || l_api_name,
2471 'Contract Id is: ' || to_char(l_contract_id));
2472 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2473 g_module || l_api_name,
2474 'Calling ame_api.getNextApprover to get the approver id');
2475 END IF;
2476 -- Using this API to determine if process is complete. Complete process from AME implies
2477 -- Contract is Approved.
2478 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
2479 ame_api2.getNextApprovers1(
2480 applicationIdIn => G_APPLICATION_ID,
2481 transactionTypeIn => G_TRANSACTION_TYPE,
2482 transactionIdIn => fnd_number.number_to_canonical(l_contract_id),
2483 flagApproversAsNotifiedIn => ame_util.booleanFalse,
2484 approvalProcessCompleteYNOut => l_process_complete_yn,
2485 nextApproversOut => l_next_approvers,
2486 itemIndexesOut => l_item_indexes,
2487 itemClassesOut => l_item_classes,
2488 itemIdsOut => l_item_ids,
2489 itemSourcesOut => l_item_sources);
2490
2491 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2492 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2493 g_module || l_api_name,
2494 'Number of approvers: ' || to_char(l_next_approvers.count));
2495 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2496 g_module || l_api_name,
2497 'l_process_complete_yn is is_contract_approved_detailed: ' || l_process_complete_yn);
2498 END IF;
2499 IF ((l_process_complete_yn = 'Y') OR
2500 (l_process_complete_yn = 'X')) THEN
2501 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2502 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
2503 'The contract is approved');
2504 END IF;
2505 resultout := 'COMPLETE:T';
2506 ELSE
2507 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2508 fnd_log.string(FND_LOG.LEVEL_STATEMENT, g_module || l_api_name,
2509 'The contract is rejected');
2510 END IF;
2511 resultout := 'COMPLETE:F';
2512 END IF;
2513 -- We need to loop through the pending notif. process and abort those
2514 FOR wf_process_rec IN wf_process_csr
2515 LOOP
2516 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2517 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module || l_api_name,
2518 'Calling WF_ENGINE.AbortProcess');
2519 END IF;
2520
2521 WF_ENGINE.AbortProcess(
2522 itemtype => itemtype,
2523 itemkey => wf_process_rec.item_key,
2524 result => 'COMPLETE:',
2525 verify_lock => false,
2526 cascade => true);
2527 END LOOP;
2528
2529 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2530 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module || l_api_name,
2531 'Calling ame_api2.clearAllApprovals');
2532 END IF;
2533
2534 -- Repository Contracts ER's - Calling this after approval
2535 -- Clear all approvals on this transaction id.
2536 ame_api2.clearAllApprovals(
2537 applicationIdIn => G_APPLICATION_ID,
2538 transactionTypeIn => G_TRANSACTION_TYPE,
2539 transactionIdIn => fnd_number.number_to_canonical(l_contract_id));
2540
2541 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2542 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module || l_api_name,
2543 'After ame_api2.clearAllApprovals');
2544 END IF;
2545
2546 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2547 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2548 g_module || l_api_name,
2549 'Leaving OKC_REP_WF_PVT.is_contract_approved_detailed from funcmode=RUN');
2550 END IF;
2551 RETURN;
2552 END IF; -- (funcmode = 'RUN')
2553
2554 IF (funcmode = 'CANCEL') THEN
2555 resultout := 'COMPLETE:';
2556 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2557 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2558 g_module || l_api_name,
2559 'Leaving OKC_REP_WF_PVT.is_contract_approved_detailed from funcmode=CANCEL');
2560 END IF;
2561 RETURN;
2562 END IF; -- (funcmode = 'CANCEL')
2563
2564 IF (funcmode = 'TIMEOUT') THEN
2565 resultout := 'COMPLETE:';
2566 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2567 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2568 g_module || l_api_name,
2569 'Leaving OKC_REP_WF_PVT.is_contract_approved_detailed from funcmode=TIMEOUT');
2570 END IF;
2571 RETURN;
2572 END IF; -- (funcmode = 'TIMEOUT')
2573
2574 EXCEPTION
2575 WHEN others THEN
2576 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2578 g_module || l_api_name,
2579 'Leaving OKC_REP_WF_PVT.is_contract_approved_detailed with exceptions ' || sqlerrm);
2580 END IF;
2581 wf_core.context('OKC_REP_WF_PVT',
2582 'is_contract_approved_detailed',
2583 itemtype,
2584 itemkey,
2585 to_char(actid),
2586 funcmode);
2587 raise;
2588 END is_contract_approved_detailed;
2589
2590
2591
2592
2593 -- Start of comments
2594 --API name : complete_notification
2595 --Type : Private.
2596 --Function : This procedure is called by workflow after the approver responds to the Approval Notification Message.
2597 -- : It completes the master process's waiting activity.
2598 --Pre-reqs : None.
2599 --Parameters :
2600 --IN : itemtype IN VARCHAR2 Required
2601 -- Workflow item type parameter
2602 -- : itemkey IN VARCHAR2 Required
2603 -- Workflow item key parameter
2604 -- : actid IN VARCHAR2 Required
2605 -- Workflow actid parameter
2606 -- : funcmode IN VARCHAR2 Required
2607 -- Workflow function mode parameter
2608 --OUT : resultout OUT VARCHAR2(1)
2609 -- Workflow standard out parameter
2610 -- Note :
2611 -- End of comments
2612 PROCEDURE complete_notification(
2613 itemtype IN varchar2,
2614 itemkey IN varchar2,
2615 actid IN number,
2616 funcmode IN varchar2,
2617 resultout OUT nocopy varchar2
2618 ) IS
2619
2620 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2621 l_contract_version OKC_REP_CONTRACTS_ALL.contract_version_num%type;
2622 l_api_name VARCHAR2(30);
2623 l_return_status VARCHAR2(1);
2624 l_msg_count NUMBER;
2625 l_msg_data VARCHAR2(2000);
2626 l_master_key wf_items.user_key%TYPE;
2627
2628 BEGIN
2629
2630 l_api_name := 'complete_notification';
2631
2632 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2633 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2634 'Entered OKC_REP_WF_PVT.complete_notification');
2635 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2636 'Item Type is: ' || itemtype);
2637 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2638 'Item Key is: ' || itemkey);
2639 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2640 'actid is: ' || to_char(actid));
2641 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2642 'Function mode is: ' || funcmode);
2643 END IF;
2644 if (funcmode = 'RUN') then
2645 l_master_key := wf_engine.GetItemAttrText(
2646 itemtype => itemtype,
2647 itemkey => itemkey,
2648 aname => 'MASTER_ITEM_KEY');
2649 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2650 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2651 g_module || l_api_name,
2652 'Master Item Key is: ' || l_master_key);
2653 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2654 'Completing master process waiting activity');
2655 END IF;
2656 wf_engine.CompleteActivity(
2657 itemtype => itemtype,
2658 itemkey => l_master_key,
2659 activity => 'WAIT_FOR_APPROVER_RESPONSE',
2660 result => null);
2661
2662 resultout := 'COMPLETE:';
2663 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2664 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2665 g_module || l_api_name,
2666 'Leaving OKC_REP_WF_PVT.complete_notification');
2667 END IF;
2668 RETURN;
2669 END IF; -- (funcmode = 'RUN')
2670 EXCEPTION
2671 WHEN others THEN
2672 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2673 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2674 g_module || l_api_name,
2675 'Leaving OKC_REP_WF_PVT.complete_notification with exceptions ' || sqlerrm);
2676 END IF;
2677 wf_core.context('OKC_REP_WF_PVT',
2678 'complete_notification',
2679 itemtype,
2680 itemkey,
2681 to_char(actid),
2682 funcmode);
2683 raise;
2684 END complete_notification;
2685
2686 --Bug 6957819
2687 -- Start of comments
2688 --API name : con_has_terms
2689 --Type : Private.
2690 --Function : This procedure is called by workflow to check if terms has been applied on the document.
2691 --Pre-reqs : None.
2692 --Parameters :
2693 --IN : itemtype IN VARCHAR2 Required
2694 -- Workflow item type parameter
2695 -- : itemkey IN VARCHAR2 Required
2696 -- Workflow item key parameter
2697 -- : actid IN VARCHAR2 Required
2698 -- Workflow actid parameter
2699 -- : funcmode IN VARCHAR2 Required
2700 -- Workflow function mode parameter
2701 --OUT : resultout OUT VARCHAR2(1)
2702 -- Workflow standard out parameter
2703 -- Note :
2704 -- End of comments
2705
2706 PROCEDURE con_has_terms(
2707 itemtype IN varchar2,
2708 itemkey IN varchar2,
2709 actid IN number,
2710 funcmode IN varchar2,
2711 resultout OUT nocopy varchar2
2712 ) IS
2713
2714 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2715 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
2716 l_api_name VARCHAR2(30);
2717 l_return_status VARCHAR2(1);
2718 l_msg_count NUMBER;
2719 l_msg_data VARCHAR2(2000);
2720 l_master_key wf_items.user_key%TYPE;
2721 l_value VARCHAR2(1);
2722
2723
2724 BEGIN
2725
2726 l_api_name := 'con_has_terms';
2727
2728 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2729 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2730 'Entered OKC_REP_WF_PVT.complete_notification');
2731 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2732 'Item Type is: ' || itemtype);
2733 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2734 'Item Key is: ' || itemkey);
2735 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2736 'actid is: ' || to_char(actid));
2737 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2738 'Function mode is: ' || funcmode);
2739 END IF;
2740
2741 IF (funcmode = 'RUN') THEN
2742 l_contract_id := wf_engine.GetItemAttrNumber(
2743 itemtype => itemtype,
2744 itemkey => itemkey,
2745 aname => 'CONTRACT_ID');
2746
2747 l_contract_type := wf_engine.GetItemAttrText(
2748 itemtype => itemtype,
2749 itemkey => itemkey,
2750 aname => 'CONTRACT_TYPE');
2751
2752 l_value := OKC_TERMS_UTIL_GRP.HAS_TERMS( p_document_type => l_contract_type,
2753 p_document_id => l_contract_id);
2754 IF (l_value = 'Y') THEN
2755 resultout := 'COMPLETE:T';
2756 ELSE
2757 resultout := 'COMPLETE:F';
2758 END IF;
2759 END IF; -- RUN
2760
2761 EXCEPTION
2762 WHEN others THEN
2763 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2764 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2765 g_module || l_api_name,
2766 'Leaving OKC_REP_WF_PVT.con_has_terms with exceptions ' || sqlerrm);
2767 END IF;
2768 wf_core.context('OKC_REP_WF_PVT',
2769 'con_has_terms',
2770 itemtype,
2771 itemkey,
2772 to_char(actid),
2773 funcmode);
2774 raise;
2775
2776 END con_has_terms;
2777
2778 -- Start of comments
2779 --API name : Con_attach_generated_YN
2780 --Type : Private.
2781 --Function : This procedure is called by workflow to check if terms has been applied on the document.
2782 --Pre-reqs : None.
2783 --Parameters :
2784 --IN : itemtype IN VARCHAR2 Required
2785 -- Workflow item type parameter
2786 -- : itemkey IN VARCHAR2 Required
2787 -- Workflow item key parameter
2788 -- : actid IN VARCHAR2 Required
2789 -- Workflow actid parameter
2790 -- : funcmode IN VARCHAR2 Required
2791 -- Workflow function mode parameter
2792 --OUT : resultout OUT VARCHAR2(1)
2793 -- Workflow standard out parameter
2794 -- Note :
2795 -- End of comments
2796
2797 PROCEDURE con_attach_generated_yn(
2798 itemtype IN varchar2,
2799 itemkey IN varchar2,
2800 actid IN number,
2801 funcmode IN varchar2,
2802 resultout OUT nocopy varchar2
2803 ) IS
2804
2805 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2806 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
2807 l_con_req_id OKC_CONTRACT_DOCS.request_id%TYPE;
2808 l_api_name VARCHAR2(30);
2809 l_return_status VARCHAR2(1);
2810 l_msg_count NUMBER;
2811 l_msg_data VARCHAR2(2000);
2812 l_master_key wf_items.user_key%TYPE;
2813 l_value VARCHAR2(1);
2814
2815 CURSOR contract_attachment_exists(l_contract_id IN NUMBER,l_contract_type IN VARCHAR2, l_con_req_id IN NUMBER) IS
2816 select 'Y'
2817 from okc_contract_docs
2818 where business_document_type = l_contract_type
2819 and business_document_id = l_contract_id
2820 AND request_id = l_con_req_id;
2821
2822 BEGIN
2823
2824 l_api_name := 'con_attach_generated_yn';
2825
2826 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2827 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2828 'Entered OKC_REP_WF_PVT.complete_notification');
2829 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2830 'Item Type is: ' || itemtype);
2831 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2832 'Item Key is: ' || itemkey);
2833 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2834 'actid is: ' || to_char(actid));
2835 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2836 'Function mode is: ' || funcmode);
2837 END IF;
2838
2839 IF (funcmode = 'RUN') THEN
2840 l_contract_id := wf_engine.GetItemAttrNumber(
2841 itemtype => itemtype,
2842 itemkey => itemkey,
2843 aname => 'CONTRACT_ID');
2844
2845 l_contract_type := wf_engine.GetItemAttrText(
2846 itemtype => itemtype,
2847 itemkey => itemkey,
2848 aname => 'CONTRACT_TYPE');
2849
2850 l_con_req_id := wf_engine.GetItemAttrNumber(
2851 itemtype => itemtype,
2852 itemkey => itemkey,
2853 aname => 'CONC_REQUEST_ID' );
2854
2855 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2856 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1810: Entering con_attach_generated_yn');
2857 END IF;
2858
2859 OPEN contract_attachment_exists(l_contract_id, l_contract_type, l_con_req_id) ;
2860 FETCH contract_attachment_exists into l_value;
2861 CLOSE contract_attachment_exists ;
2862
2863 IF (l_value = 'Y') THEN
2864 resultout := 'COMPLETE:T';
2865 ELSE
2866 resultout := 'COMPLETE:F';
2867 END IF;
2868
2869 END IF; -- RUN
2870 EXCEPTION
2871 WHEN others THEN
2872 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2873 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
2874 g_module || l_api_name,
2875 'Leaving OKC_REP_WF_PVT.con_attach_generated_yn with exceptions ' || sqlerrm);
2876 END IF;
2877 wf_core.context('OKC_REP_WF_PVT',
2878 'con_attach_generated_yn',
2879 itemtype,
2880 itemkey,
2881 to_char(actid),
2882 funcmode);
2883 raise;
2884
2885 END con_attach_generated_yn;
2886
2887 -- Start of comments
2888 --API name : check_current_approver
2889 --Type : Private.
2890 --Function : This procedure is by ContractDetailsAMImpl.java to check if the logged in user is in list of penging approvers or not.
2891 --Pre-reqs : None.
2892 --Parameters :
2893 --IN :p_contract_id,p_user_id
2894 --OUT :'Y' or 'N'
2895 -- Note :
2896 -- End of comments
2897 FUNCTION check_current_approver
2898 (p_contract_id IN NUMBER
2899 , p_user_id IN NUMBER
2900 )
2901 RETURN varchar2 IS
2902
2903
2904 l_is_valid VARCHAR2(1) := 'N';
2905 l_usr_resp_pending VARCHAR2(1) := 'N';
2906 l_approversOut ame_util.approversTable;
2907
2908 x_approvalProcessCompleteYNOut VARCHAR2(10);
2909 x_nextApproversOut ame_util.approversTable2; -- New API approverOut
2910 currApprRec ame_util.approverRecord2;
2911
2912 xitemIndexesOut ame_util.idList;
2913 xitemClassesOut ame_util.stringList;
2914 xitemIdsOut ame_util.stringList;
2915 xitemSourcesOut ame_util.longStringList;
2916 l_person_id NUMBER;
2917 l_api_name VARCHAR2(50):='check_current_approver';
2918 CURSOR csr_person_id is
2919 select decode(category, 'EMPLOYEE', source_id, null) person_id from jtf_rs_resource_extns where user_id =p_user_id ;
2920
2921 BEGIN
2922
2923 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2924 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'Entering check_current_approver for contract_id '||p_contract_id || 'user id '||p_user_id);
2925 END IF;
2926
2927 ame_api2.getPendingApprovers(applicationIdIn => G_APPLICATION_ID,
2928 transactionTypeIn => G_TRANSACTION_TYPE,
2929 transactionIdIn => fnd_number.number_to_canonical(p_contract_id),
2930 approvalProcessCompleteYNOut => x_approvalProcessCompleteYNOut,
2931 approversOut => x_nextApproversOut);
2932
2933 OPEN csr_person_id;
2934 FETCH csr_person_id INTO l_person_id;
2935 CLOSE csr_person_id;
2936
2937 FOR i IN 1..x_nextApproversOut.count LOOP
2938 currApprRec := x_nextApproversOut(i);
2939
2940
2941
2942 IF (l_person_id = currApprRec.orig_system_id) THEN
2943 l_is_valid := 'Y';
2944 END IF;
2945 END LOOP;
2946
2947 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2948 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
2949 'Leaving check_current_approver l_is_valid as '||l_is_valid );
2950 END IF;
2951
2952 RETURN l_is_valid;
2953 END check_current_approver;
2954
2955 -- Start of comments
2956 --API name : allow_approver_edit
2957 --Type : Private.
2958 --Function : This procedure is checks if the approver is allowed to edit or not
2959 --Pre-reqs : None.
2960 --Parameters :
2961 -- Note :
2962 -- End of comments
2963 PROCEDURE allow_approver_edit
2964 (itemtype IN varchar2,
2965 itemkey IN varchar2,
2966 actid IN number,
2967 funcmode IN varchar2,
2968 resultout OUT nocopy varchar2
2969 ) IS
2970
2971 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
2972 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
2973 l_con_req_id OKC_CONTRACT_DOCS.request_id%TYPE;
2974 l_api_name VARCHAR2(30);
2975 l_return_status VARCHAR2(1);
2976 l_msg_count NUMBER;
2977 l_msg_data VARCHAR2(2000);
2978 l_master_key wf_items.user_key%TYPE;
2979 l_value VARCHAR2(1);
2980
2981 CURSOR csr_doc(l_contract_type IN VARCHAR2) IS
2982 SELECT Nvl(ALLOW_APPROVER_EDIT_YN,'N') FROM okc_bus_doc_types_b WHERE document_type=l_contract_type;
2983
2984 BEGIN
2985
2986 l_api_name := 'allow_approver_edit';
2987
2988 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2989 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2990 'Entered OKC_REP_WF_PVT.allow_approver_edit');
2991 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2992 'Item Type is: ' || itemtype);
2993 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2994 'Item Key is: ' || itemkey);
2995 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2996 'actid is: ' || to_char(actid));
2997 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2998 'Function mode is: ' || funcmode);
2999 END IF;
3000
3001 IF (funcmode = 'RUN') THEN
3002
3003 l_contract_type := wf_engine.GetItemAttrText(
3004 itemtype => itemtype,
3005 itemkey => itemkey,
3006 aname => 'CONTRACT_TYPE');
3007
3008
3009 OPEN csr_doc(l_contract_type) ;
3010 FETCH csr_doc into l_value;
3011 CLOSE csr_doc ;
3012
3013 IF (l_value = 'Y') THEN
3014 resultout := 'COMPLETE:Y';
3015 ELSE
3016 resultout := 'COMPLETE:N';
3017 END IF;
3018
3019 END IF; -- RUN
3020 EXCEPTION
3021 WHEN others THEN
3022 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3023 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3024 g_module || l_api_name,
3025 'Leaving OKC_REP_WF_PVT.allow_approver_edit with exceptions ' || sqlerrm);
3026 END IF;
3027 wf_core.context('OKC_REP_WF_PVT',
3028 'allow_approver_edit',
3029 itemtype,
3030 itemkey,
3031 to_char(actid),
3032 funcmode);
3033 raise;
3034
3035 END allow_approver_edit;
3036
3037
3038
3039 -- Start of comments
3040 --API name : contract_preview_yn
3041 --Type : Private.
3042 --Function : This procedure is checks if the contract can be printed or not
3043 --Pre-reqs : None.
3044 --Parameters :
3045 -- Note :
3046 -- End of comments
3047 PROCEDURE contract_preview_yn
3048 (itemtype IN varchar2,
3049 itemkey IN varchar2,
3050 actid IN number,
3051 funcmode IN varchar2,
3052 resultout OUT nocopy varchar2
3053 ) IS
3054
3055 l_contract_id OKC_REP_CONTRACTS_ALL.contract_id%type;
3056 l_contract_type OKC_REP_CONTRACTS_ALL.contract_type%TYPE;
3057 l_con_req_id OKC_CONTRACT_DOCS.request_id%TYPE;
3058 l_api_name VARCHAR2(30);
3059 l_return_status VARCHAR2(1);
3060 l_msg_count NUMBER;
3061 l_msg_data VARCHAR2(2000);
3062 l_master_key wf_items.user_key%TYPE;
3063 l_value VARCHAR2(1);
3064
3065 CURSOR csr_doc(l_contract_type IN VARCHAR2) IS
3066 SELECT Nvl(ALLOW_APPROVER_EDIT_YN,'N') FROM okc_bus_doc_types_b WHERE document_type=l_contract_type;
3067
3068 BEGIN
3069
3070 l_api_name := 'contract_preview_yn';
3071
3072 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3073 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3074 'Entered OKC_REP_WF_PVT.contract_preview_yn');
3075 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3076 'Item Type is: ' || itemtype);
3077 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3078 'Item Key is: ' || itemkey);
3079 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3080 'actid is: ' || to_char(actid));
3081 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3082 'Function mode is: ' || funcmode);
3083 END IF;
3084
3085 IF (funcmode = 'RUN') THEN
3086
3087 l_contract_type := wf_engine.GetItemAttrText(
3088 itemtype => itemtype,
3089 itemkey => itemkey,
3090 aname => 'CONTRACT_TYPE');
3091
3092 l_contract_id := wf_engine.GetItemAttrNumber(
3093 itemtype => itemtype,
3094 itemkey => itemkey,
3095 aname => 'CONTRACT_ID');
3096
3097 l_value:= okc_rep_contract_process_pvt.call_contract_preview_cp_yn (l_contract_id,
3098 l_contract_type ) ;
3099
3100 IF (l_value = 'Y') THEN
3101 resultout := 'COMPLETE:Y';
3102 ELSE
3103 resultout := 'COMPLETE:N';
3104 END IF;
3105
3106 END IF; -- RUN
3107 EXCEPTION
3108 WHEN others THEN
3109 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3110 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3111 g_module || l_api_name,
3112 'Leaving OKC_REP_WF_PVT.contract_preview_yn with exceptions ' || sqlerrm);
3113 END IF;
3114 wf_core.context('OKC_REP_WF_PVT',
3115 'contract_preview_yn',
3116 itemtype,
3117 itemkey,
3118 to_char(actid),
3119 funcmode);
3120 raise;
3121
3122 END contract_preview_yn;
3123
3124
3125 PROCEDURE Apps_initialize(itemtype IN varchar2,
3126 itemkey IN varchar2,
3127 actid IN number,
3128 funcmode IN varchar2,
3129 resultout OUT nocopy varchar2)
3130 IS
3131
3132
3133 l_user_id NUMBER;
3134 l_resp_id NUMBER;
3135 l_resp_appl_id NUMBER;
3136
3137 l_contract_id NUMBER;
3138 l_org_id NUMBER;
3139
3140 l_api_name VARCHAR2(100);
3141
3142 BEGIN
3143
3144 l_api_name := 'Apps_initialize';
3145
3146 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3148 'Entered OKC_REP_WF_PVT.Apps_initialize');
3149 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3150 'Item Type is: ' || itemtype);
3151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3152 'Item Key is: ' || itemkey);
3153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3154 'actid is: ' || to_char(actid));
3155 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
3156 'Function mode is: ' || funcmode);
3157 END IF;
3158
3159 IF (funcmode = 'RUN') THEN
3160
3161 l_contract_id := wf_engine.GetItemAttrNumber(
3162 itemtype => itemtype,
3163 itemkey => itemkey,
3164 aname => 'CONTRACT_ID');
3165
3166 l_user_id := wf_engine.GetItemAttrNumber(
3167 itemtype => itemtype,
3168 itemkey => itemkey,
3169 aname => 'CTX_USER_ID');
3170
3171 l_resp_appl_id := wf_engine.GetItemAttrNumber(
3172 itemtype => itemtype,
3173 itemkey => itemkey,
3174 aname => 'CTX_APPL_ID');
3175
3176 l_resp_id := wf_engine.GetItemAttrNumber(
3177 itemtype => itemtype,
3178 itemkey => itemkey,
3179 aname => 'CTX_RESP_ID');
3180
3181 fnd_global.apps_initialize
3182 ( user_id => l_user_id
3183 ,resp_id => l_resp_id
3184 ,resp_appl_id => l_resp_appl_id
3185 );
3186
3187 SELECT org_id INTO l_org_id
3188 FROM okc_rep_contracts_all
3189 WHERE contract_id = l_contract_id;
3190
3191 -- MO init
3192 mo_global.init('OKC');
3193
3194 -- MO Set policy Context
3195 mo_global.set_policy_context('S', l_org_id);
3196
3197 resultout := 'COMPLETE:Y';
3198 END IF; -- RUN
3199
3200 EXCEPTION
3201 WHEN others THEN
3202 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3203 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
3204 g_module || l_api_name,
3205 'Leaving OKC_REP_WF_PVT.Apps_initialize with exceptions ' || sqlerrm);
3206 END IF;
3207 wf_core.context('OKC_REP_WF_PVT',
3208 'Apps_initialize',
3209 itemtype,
3210 itemkey,
3211 to_char(actid),
3212 funcmode);
3213 raise;
3214
3215 END Apps_initialize;
3216
3217
3218 END OKC_REP_WF_PVT;