[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_WF_APPROVAL
Source
1 PACKAGE BODY JTF_UM_WF_APPROVAL as
2 /* $Header: JTFUMWFB.pls 120.19.12020000.2 2012/07/18 16:05:58 anurtrip ship $ */
3
4 G_MODULE CONSTANT VARCHAR2(40) := 'JTF.UM.PLSQL.APPROVAL';
5 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(G_MODULE);
6
7
8 /*
9 Internal procedure to check if a Workflow has started or not. The Workflow should have been
10 created before.
11 */
12 function hasWorkFlowStarted(itemtype varchar2, itemkey varchar2) return boolean
13 is
14 status varchar2(50);
15 result varchar2(50);
16 begin
17 wf_engine.ItemStatus(itemType,itemkey,status,result);
18 if status is null then
19 return false;
20 else
21 return true;
22 end if;
23 end hasWorkFlowStarted;
24
25
26
27 -- Return the descriptive name of the usertype or enrollment request
28 function getRequestName(requestType in varchar2,
29 requestId in number) return varchar2 is
30
31 requestName varchar2 (1000);
32
33 cursor getUsertypeName(x_usertype_id in number) is
34 select USERTYPE_SHORTNAME
35 from jtf_um_usertypes_vl
36 where usertype_id = x_usertype_id;
37
38 cursor getSubscriptionName(x_subscription_id in number) is
39 select SUBSCRIPTION_NAME
40 from jtf_um_subscriptions_vl
41 where subscription_id = x_subscription_id;
42
43 begin
44
45 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering getRequestName (' ||
46 requestType || ',' || requestId || ') API');
47
48 if requestType = 'USERTYPE' then
49 open getUsertypeName(requestId);
50 fetch getUsertypeName into requestName;
51 close getUsertypeName;
52 else
53 open getSubscriptionName(requestId);
54 fetch getSubscriptionName into requestName;
55 close getSubscriptionName;
56 end if;
57
58 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting getRequestName API');
59
60 return requestName;
61
62 end getRequestName;
63
64 -- Return the application id of the usertype or enrollment request
65 function getRequestApplId (requestType in varchar2,
66 requestId in number) return varchar2 is
67
68 l_application_id jtf_um_usertypes_b.application_id%type;
69
70 cursor getUsertypeApplID (x_usertype_id in number) is
71 select application_id
72 from jtf_um_usertypes_b
73 where usertype_id = x_usertype_id;
74
75 cursor getSubscriptionApplID (x_subscription_id in number) is
76 select application_id
77 from jtf_um_subscriptions_vl
78 where subscription_id = x_subscription_id;
79
80 begin
81
82 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering getRequestApplId (' ||
83 requestType || ',' || requestId || ') API');
84
85 if requestType = 'USERTYPE' then
86 open getUsertypeApplID (requestId);
87 fetch getUsertypeApplID into l_application_id;
88 close getUsertypeApplID;
89 else
90 open getSubscriptionApplID (requestId);
91 fetch getSubscriptionApplID into l_application_id;
92 close getSubscriptionApplID;
93 end if;
94
95 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting getRequestApplId API');
96
97 return l_application_id;
98
99 end getRequestApplId;
100
101 -- Return the requester username
102 function getRequesterUsername (userID in varchar2) return varchar2 is
103
104 requesterUsername fnd_user.user_name%type;
105
106 cursor getUserNameCursor is
107 select USER_NAME
108 from FND_USER
109 where USER_ID = userID
110 and (nvl (END_DATE, sysdate + 1) > sysdate
111 or to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
112 -- Bug Fix: 4741111: Added the clause to look for pending users
113
114 begin
115
116 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering getRequesterUsername (' ||
117 userID || ') API');
118
119 open getUserNameCursor;
120 fetch getUserNameCursor into requesterUsername;
121 if (getUserNameCursor%notfound) then
122 close getUserNameCursor;
123 raise_application_error (-20000, 'requester username is not found while calling JTF_UM_WF_APPROVAL.getRequesterUsername');
124 end if;
125 close getUserNameCursor;
126
127 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting getRequesterUsername API');
128
129 return requesterUsername;
130 end getRequesterUsername;
131
132 -- Return the userid from username
133 function getUserID (username in varchar2) return fnd_user.user_id%type is
134
135 userId fnd_user.user_id%type;
136
137 cursor getUserIDCursor is
138 select user_id
139 from FND_USER
140 where USER_NAME = username
141 and (nvl (END_DATE, sysdate + 1) > sysdate OR
142 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
143
144 begin
145
146 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering getUserID (' ||
147 userID || ') API');
148
149 open getUserIDCursor;
150 fetch getUserIDCursor into userId;
151 if (getUserIDCursor%notfound) then
152 close getUserIDCursor;
153 raise_application_error (-20000, 'userId is not found while calling JTF_UM_WF_APPROVAL.getUserID');
154 end if;
155 close getUserIDCursor;
156
157 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting getUserID API');
158
159 return userId;
160 end getUserID;
161
162 --
163 --
164 -- Procedure
165 -- get_wf_owner_username (PRIVATE)
166 --
167 -- Description
168 -- Return the workflow owner username from the fnd profile option
169 -- IN
170 -- p_request_id - request type id (Usertype ID or Enrollment ID)
171 -- p_request_type - The type of request type, either 'USERTYPE' or
172 -- 'ENROLLMENT
173 --
174 function get_wf_owner_username (p_request_id in number,
175 p_request_type in varchar2) return varchar2 is
176
177 l_method_name varchar2 (21) := 'GET_WF_OWNER_USERNAME';
178 l_application_id JTF_UM_USERTYPES_B.APPLICATION_ID%TYPE;
179 l_owner_username varchar2 (100);
180
181 begin
182
183 -- Log the entering
184 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (G_MODULE, l_method_name);
185
186 -- Log parameters
187 if l_is_debug_parameter_on then
188 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
189 'p_request_id=' || p_request_id);
190 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
191 'p_request_type=' || p_request_type);
192 end if;
193
194 l_application_id := getRequestApplId (p_request_type, p_request_id);
195
196 l_owner_username := nvl (JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
197 NAME => 'JTF_UM_APPROVAL_OWNER',
198 APPLICATION_ID => l_application_id,
199 SITE_LEVEL => true), 'SYSADMIN');
200
201 JTF_DEBUG_PUB.LOG_EXITING_METHOD (G_MODULE, l_method_name);
202
203 return l_owner_username;
204
205 end get_wf_owner_username;
206
207 --
208 --
209 -- Procedure
210 -- get_org_info (PRIVATE)
211 --
212 -- Description
213 -- Return the organization information of the user.
214 -- IN
215 -- p_user_id - fnd user_id
216 -- OUT
217 -- x_org_name - organization's name
218 -- x_org_number - organization's number
219 --
220 procedure get_org_info (p_user_id in fnd_user.user_id%type,
221 x_org_name out NOCOPY hz_parties.party_name%type,
222 x_org_number out NOCOPY hz_parties.party_number%type) is
223
224 l_method_name varchar2 (12) := 'GET_ORG_INFO';
225
226 cursor getOrgNameAndNumber is
227 select hz.party_name, hz.party_number
228 from fnd_user fnd, hz_parties hz, hz_relationships hzr
229 where fnd.user_id = p_user_id
230 and fnd.customer_id = hzr.party_id
231 and hzr.start_date <= sysdate
232 and nvl (hzr.end_date, sysdate + 1) > sysdate
233 and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
234 and hzr.object_table_name = 'HZ_PARTIES'
235 and hzr.subject_table_name = 'HZ_PARTIES'
236 and hzr.object_id = hz.party_id;
237
238 begin
239
240 -- Log the entering
241 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (G_MODULE, l_method_name);
242
243 -- Log parameters
244 if l_is_debug_parameter_on then
245 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
246 'p_user_id=' || p_user_id);
247 end if;
248 open getOrgNameAndNumber;
249 fetch getOrgNameAndNumber into x_org_name, x_org_number;
250 close getOrgNameAndNumber;
251
252
253 JTF_DEBUG_PUB.LOG_EXITING_METHOD (G_MODULE, l_method_name);
254
255 end get_org_info;
256
257 --
258 -- Procedure
259 -- ValidateWF
260 --
261 -- Description
262 -- Check if the required workflow attributes are defined in the WF.
263 -- IN
264 -- itemtype -- The itemtype of the workflow.
265 --
266 procedure ValidateWF (itemtype in varchar2) is
267
268 l_atype varchar2 (8);
269 l_subtype varchar2 (8);
270 l_format varchar2 (240);
271
272 begin
273
274 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering ValidateWF (' ||
275 itemtype || ') API');
276
277 wf_engine.getItemAttrInfo (itemtype, 'REQUEST_TYPE', l_atype, l_subtype, l_format);
278 wf_engine.getItemAttrInfo (itemtype, 'REQUEST_ID', l_atype, l_subtype, l_format);
279 wf_engine.getItemAttrInfo (itemtype, 'REQUESTER_USER_ID', l_atype, l_subtype, l_format);
280 wf_engine.getItemAttrInfo (itemtype, 'REQUESTER_USERTYPE_ID', l_atype, l_subtype, l_format);
281 wf_engine.getItemAttrInfo (itemtype, 'APPROVAL_ID', l_atype, l_subtype, l_format);
282 wf_engine.getItemAttrInfo (itemtype, 'APPROVER_ID', l_atype, l_subtype, l_format);
283 wf_engine.getItemAttrInfo (itemtype, 'APPROVER_COMMENT', l_atype, l_subtype, l_format);
284
285 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting ValidateWF API');
286
287 exception
288 when others then
289 raise;
290 end ValidateWF;
291
292 --
293 -- Procedure
294 -- CreateProcess
295 --
296 -- Description
297 -- Initiate workflow for a um approval
298 -- This API will not launch the workflow process.
299 -- To launch workflow process, call LaunchProcess.
300 -- IN
301 -- ownerUserID -- The FND userID of the workflow owner
302 -- requestType -- The type of request, 'ENROLLMENT/USERTYPE'
303 -- requestID -- ID of the request.
304 -- requesterUserID -- The FND userID of the requester
305 -- requestRegID -- USERTYPE_REG_ID or SUBSCRIPTION_REG_ID
306 --
307 procedure CreateProcess (ownerUserId in number := null,
308 requestType in varchar2,
309 requestID in number,
310 requesterUserID in number,
311 requestRegID in number) is
312
313 itemtype varchar2 (8);
314 itemkey number := requestRegID;
315 itemUserKey wf_items.user_key%type;
316 userID number;
317 requesterUsername fnd_user.user_name%type;
318 requesterUsertypeID number;
319 approvalID number;
320 usePendingReqFlag varchar2 (1);
321 processOwner varchar2 (100);
322
323 cursor usertypeApprovalCursor is
324 select APPROVAL_ID
325 from JTF_UM_USERTYPES_B
326 where USERTYPE_ID = requestID;
327
328 cursor enrollApprovalCursor is
329 select APPROVAL_ID
330 from JTF_UM_SUBSCRIPTIONS_B
331 where SUBSCRIPTION_ID = requestID;
332
333 cursor getUsertypeIdCursor is
334 select USERTYPE_ID
335 from JTF_UM_USERTYPE_REG
336 where USER_ID = requesterUserID
337 and STATUS_CODE <>'REJECTED'
338 and EFFECTIVE_START_DATE <= sysdate
339 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
340
341 cursor approvalCursor is
342 select USE_PENDING_REQ_FLAG
343 from JTF_UM_APPROVALS_B
344 where APPROVAL_ID = approvalID
345 and EFFECTIVE_START_DATE <= sysdate
346 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
347
348 cursor wfApprovalCursor is
349 select WF_ITEM_TYPE
350 from JTF_UM_APPROVALS_B
351 where APPROVAL_ID = approvalID;
352 --
353
354 begin
355
356 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering CreateProcess (' ||
357 ownerUserId || ',' || requestType || ',' || requestID || ',' ||
358 requesterUserID || ',' || requestRegID || ') API');
359
360 -- Check input parameter
361 if (requestType is null) then
362 raise_application_error (-20000, 'requestType is null when calling JTF_UM_WF_APPROVAL.CreateProcess.');
363 end if;
364
365 if (requestID is null) then
366 raise_application_error (-20000, 'requestID is null when calling JTF_UM_WF_APPROVAL.CreateProcess.');
367 end if;
368
369 if (requesterUserID is null) then
370 raise_application_error (-20000, 'requesterUserID is null when calling JTF_UM_WF_APPROVAL.CreateProcess.');
371 end if;
372
373 if (requestRegID is null) then
374 raise_application_error (-20000, 'requestRegID is null when calling JTF_UM_WF_APPROVAL.CreateProcess.');
375 end if;
376
377 -- Get the approvalID
378 if (requestType = 'USERTYPE') then
379 open usertypeApprovalCursor;
380 fetch usertypeApprovalCursor into approvalID;
381 if (usertypeApprovalCursor%notfound) then
382 close usertypeApprovalCursor;
383 raise_application_error (-20000, 'ApprovalID is not found when calling JTF_UM_WF_APPROVAL.CreateProcess requestType='||requestType||'.');
384 end if;
385 close usertypeApprovalCursor;
386 requesterUsertypeID := requestId;
387 else
388 open enrollApprovalCursor;
389 fetch enrollApprovalCursor into approvalID;
390 if (enrollApprovalCursor%notfound) then
391 close enrollApprovalCursor;
392 raise_application_error (-20000, 'ApprovalID is not found when calling JTF_UM_WF_APPROVAL.CreateProcess requestType='||requestType||'.');
393 end if;
394 close enrollApprovalCursor;
395 -- Get the requesterUsertypeID
396 open getUsertypeIdCursor;
397 fetch getUsertypeIdCursor into requesterUsertypeID;
398 if (getUsertypeIdCursor%notfound) then
399 close getUsertypeIdCursor;
400 raise_application_error (-20000, 'requesterUsertypeID is not found when calling JTF_UM_WF_APPROVAL.CreateProcess.');
401 end if;
402 close getUsertypeIdCursor;
403 end if;
404
405
406 -- Get itemtype. Return if notfound or is null
407 open wfApprovalCursor;
408 fetch wfApprovalCursor into itemtype;
409 if (wfApprovalCursor%notfound) then
410 close wfApprovalCursor;
411 raise_application_error (-20000, 'Cannot find Approval from CreateProcess');
412 end if;
413 close wfApprovalCursor;
414
415 -- Get the requester Username.
416 userId := requesterUserID;
417 requesterUsername := getRequesterUsername (requesterUserID);
418 -- this should check whether the user is end dated, if so the account
419 -- has already been rejected.
420
421 -- the WF process owner should be the merchant sysadmin
422 processOwner := get_wf_owner_username (p_request_id => requestID,
423 p_request_type => requestType);
424
425
426 --
427 -- Start Process
428 --
429 wf_engine.CreateProcess (itemtype => itemtype,
430 itemkey => itemkey);
431 --
432 itemUserKey := substrb (requesterUsername || ' requests for ' || requestType || ' : '|| getRequestName (requestType, requestId), 1, 238);
433
434
435
436 wf_engine.SetItemUserKey (itemtype => itemtype,
437 itemkey => itemkey,
438 UserKey => itemUserKey);
439 --
440 -- Initialize workflow item attributes
441 --
442 wf_engine.SetItemAttrText (itemtype => itemtype,
443 itemkey => itemkey,
444 aname => 'REQUEST_TYPE',
445 avalue => requestType);
446
447 wf_engine.SetItemAttrNumber (itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'REQUEST_ID',
450 avalue => requestID);
451
452 wf_engine.SetItemAttrNumber (itemtype => itemtype,
453 itemkey => itemkey,
454 aname => 'REQUESTER_USER_ID',
455 avalue => requesterUserID);
456
457 wf_engine.SetItemAttrNumber (itemtype => itemtype,
458 itemkey => itemkey,
459 aname => 'REQUESTER_USERTYPE_ID',
460 avalue => requesterUsertypeID);
461
462 wf_engine.SetItemAttrNumber (itemtype => itemtype,
463 itemkey => itemkey,
464 aname => 'APPROVAL_ID',
465 avalue => approvalID);
466
467 wf_engine.SetItemOwner (itemtype => itemtype,
468 itemkey => itemkey,
469 owner => processOwner);
470
471
472
473 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting CreateProcess API');
474
475 end CreateProcess;
476
477 --
478 -- Procedure
479 -- LaunchProcess
480 --
481 -- Description
482 -- Launch the workflow process that has been created.
483 -- IN
484 -- requestType -- The type of request, 'USERTYPE/ENROLLMENT'
485 -- requestRegID -- USERTYPE_REG_ID or SUBSCRIPTION_REG_ID
486 --
487 procedure LaunchProcess (requestType in varchar2,
488 requestRegID in number) is
489
490 cursor get_ut_itemtype is
491 select WF_ITEM_TYPE
492 from JTF_UM_USERTYPE_REG
493 where USERTYPE_REG_ID = requestRegID;
494
495 cursor get_enroll_itemtype is
496 select WF_ITEM_TYPE
497 from JTF_UM_SUBSCRIPTION_REG
498 where SUBSCRIPTION_REG_ID = requestRegID;
499
500 itemtype varchar2 (8);
501
502 begin
503
504 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering LaunchProcess (' ||
505 requestType || ',' || requestRegID || ') API');
506
507 -- Get the WF item type from the reg table
508 -- If WF item type is missing, raise an exception.
509 if (requestType = 'USERTYPE') then
510
511 open get_ut_itemtype;
512 fetch get_ut_itemtype into itemtype;
513 if (get_ut_itemtype%notfound) then
514 close get_ut_itemtype;
515 raise_application_error ('20000', 'Workflow itemtype is missing in the JTF_UM_USERTYPE_REG table with USERTYPE_REG_ID: ' || requestRegID);
516 end if;
517 close get_ut_itemtype;
518
519 elsif (requestType = 'ENROLLMENT') then
520
521 open get_enroll_itemtype;
522 fetch get_enroll_itemtype into itemtype;
523 if (get_enroll_itemtype%notfound) then
524 close get_enroll_itemtype;
525 raise_application_error ('20000', 'Workflow itemtype is missing in the JTF_UM_SUBSCRIPTION_REG table with SUBSCRIPTION_REG_ID: ' || requestRegID);
526 end if;
527 close get_enroll_itemtype;
528
529 else
530 raise_application_error ('20000', 'Not a valid request type: ' || requestType);
531 end if;
532
533 wf_engine.startProcess (itemtype => itemType,
534 itemkey => to_char(requestRegID));
535
536 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting LaunchProcess API');
537
538 end LaunchProcess;
539
540 --
541 -- Procedure
542 -- Selector
543 --
544 -- Description
545 -- Determine which process to run
546 -- IN
547 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
548 -- itemkey - A string generated from the application object's primary key.
549 -- actid - The function activity(instance id).
550 -- funcmode - Run/Cancel/Timeout
551 -- OUT
552 -- resultout - Name of workflow process to run
553 --
554 procedure Selector (item_type in varchar2,
555 item_key in varchar2,
556 activity_id in number,
557 command in varchar2,
558 resultout out NOCOPY varchar2) is
559 --
560 begin
561
562 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Selector (' ||
563 item_type || ',' || item_key || ',' || activity_id || ',' ||
564 command || ') API');
565
566 --
567 -- RUN mode - normal process execution
568 --
569 if (command = 'RUN') then
570 --
571 -- Return process to run
572 --
573 resultout := 'UM_APPROVAL';
574 end if;
575
576 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Selector API');
577
578 exception
579 when others then
580 wf_core.context ('JTF_UM_WF_APPROVAL', 'Selector', item_type, item_key, to_char (activity_id), command);
581 raise;
582 end selector;
583
584 --
585 -- Initialization
586 -- DESCRIPTION
587 -- To initialize other variable(s)
588 -- IN
589 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
590 -- itemkey - A string generated from the application object's primary key.
591 -- actid - The function activity(instance id).
592 -- funcmode - Run/Cancel/Timeout
593 -- OUT
594 -- Resultout - 'COMPLETE:'
595 --
596 procedure Initialization (itemtype in varchar2,
597 itemkey in varchar2,
598 actid in number,
599 funcmode in varchar2,
600 resultout out NOCOPY varchar2) is
601 --
602 applicationURL fnd_profile_option_values.profile_option_value%type;
603 applID number;
604 approvalID number;
605 approvalURL fnd_profile_option_values.profile_option_value%type;
606 companyNumber number;
607 senderName fnd_profile_option_values.profile_option_value%type;
608 ownerUsername fnd_user.user_name%type;
609 requesterUserID fnd_user.user_id%type;
610 requesterUserName fnd_user.user_name%type;
611 requestId number;
612 requestName varchar2 (1000);
613 requestType varchar2 (10);
614 respApplID number;
615 responID number;
616 supportContact fnd_profile_option_values.profile_option_value%type;
617 timeout number;
618 usertypeId jtf_um_usertypes_b.usertype_id%type;
619 usertypeKey jtf_um_usertypes_b.usertype_key%type;
620 usertypeName jtf_um_usertypes_tl.usertype_name%type;
621 MISSING_REQUESTER_USER_ID exception;
622 x_wf_dispname WF_LOCAL_ROLES.DISPLAY_NAME%TYPE;
623 Email_Address WF_LOCAL_ROLES.EMAIL_ADDRESS%TYPE;
624 x_role_name WF_LOCAL_ROLES.NAME%TYPE;
625 l_approver_display_name varchar2(1000);
626 l_role_name wf_local_roles.name%type;
627 l_notif_pref WF_LOCAL_ROLES.NOTIFICATION_PREFERENCE%TYPE;
628
629 --
630 cursor getCompanyNumber is
631 select hz.party_number
632 from hz_parties hz, hz_relationships hzr, fnd_user fnd
633 where fnd.user_id = requesterUserID
634 and fnd.customer_id = hzr.party_id
635 and hzr.start_date <= sysdate
636 and nvl (hzr.end_date, sysdate + 1) > sysdate
637 and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
638 and hzr.object_table_name = 'HZ_PARTIES'
639 and hzr.subject_table_name = 'HZ_PARTIES'
640 and hzr.object_id = hz.party_id;
641
642 cursor getUsertypeKey is
643 select USERTYPE_KEY
644 from JTF_UM_USERTYPES_B
645 where USERTYPE_ID = usertypeId
646 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
647
648 cursor getUTRespApplID is
649 select rvl.responsibility_id, rvl.application_id
650 from fnd_responsibility_vl rvl, jtf_um_usertype_resp utr
651 where utr.usertype_id = requestId
652 and utr.responsibility_key = rvl.responsibility_key
653 and nvl (rvl.end_date, sysdate + 1) > sysdate
654 and nvl (utr.effective_end_date, sysdate + 1) > sysdate
655 and rvl.version in ('W','4');
656
657 cursor getEnrollRespApplID is
658 select rvl.responsibility_id, rvl.application_id
659 from fnd_responsibility_vl rvl, jtf_um_subscription_resp utr
660 where utr.subscription_id = requestId
661 and utr.responsibility_key = rvl.responsibility_key
662 and nvl (rvl.end_date, sysdate + 1) > sysdate
663 and nvl (utr.effective_end_date, sysdate + 1) > sysdate
664 and rvl.version in ('W','4');
665
666 cursor getEmailAddress is
667 select email_address from wf_local_roles where name=upper(requesterUserName);
668
669 --------------Bug No: 7270214-------------------
670
671 cursor getADHocRole is
672 select name, display_name
673 from WF_LOCAL_ROLES
674 where name = x_role_name;
675 --------------Bug No: 7270214-------------------
676
677
678 begin
679
680 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Initialization (' ||
681 itemtype || ',' || itemkey || ',' || actid || ',' ||
682 funcmode || ') API');
683
684 --
685 -- RUN mode - normal process execution
686 --
687 if (funcmode = 'RUN') then
688 approvalID := wf_engine.GetItemAttrNumber (
689 itemtype => itemtype,
690 itemkey => itemkey,
691 aname => 'APPROVAL_ID');
692
693 requesterUserID := wf_engine.GetItemAttrNumber (
694 itemtype => itemtype,
695 itemkey => itemkey,
696 aname => 'REQUESTER_USER_ID');
697
698 requestType := wf_engine.GetItemAttrText (
699 itemtype => itemtype,
700 itemkey => itemkey,
701 aname => 'REQUEST_TYPE');
702
703 requestId := wf_engine.GetItemAttrNumber (
704 itemtype => itemtype,
705 itemkey => itemkey,
706 aname => 'REQUEST_ID');
707
708 usertypeId := wf_engine.GetItemAttrNumber (
709 itemtype => itemtype,
710 itemkey => itemkey,
711 aname => 'REQUESTER_USERTYPE_ID');
712
713 usertypeName := getRequestName ('USERTYPE', usertypeId);
714 if requestType = 'USERTYPE' then
715 requestName := usertypeName;
716 else
717 requestName := getRequestName (requestType, requestId);
718 end if;
719
720 wf_engine.SetItemAttrText (
721 itemtype => itemtype,
722 itemkey => itemkey,
723 aname => 'REQUEST_NAME',
724 avalue => requestName);
725
726 wf_engine.SetItemAttrText (
727 itemtype => itemtype,
728 itemkey => itemkey,
729 aname => 'REQUESTER_USERTYPE_NAME',
730 avalue => usertypeName);
731
732 -- Need to get the approval URL from the profile option
733 -- But first, we need responsibility_id and application_id of
734 -- the responsibility.
735 if (requestType = 'USERTYPE') then
736 open getUTRespApplID;
737 fetch getUTRespApplID into responID, respApplID;
738 close getUTRespApplID;
739 else
740 -- requestType = 'ENROLLMENT'
741 open getEnrollRespApplID;
742 fetch getEnrollRespApplID into responID, respApplID;
743 close getEnrollRespApplID;
744 end if;
745
746 -- Get the application id of the requestType
747 applID := getRequestApplId (requestType, requestId);
748
749 -- Set the approval url
750 -- first get the approval url from the profile option
751 approvalURL := JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
752 NAME => 'JTF_UM_APPROVAL_URL',
753 RESPONSIBILITY_ID => responID,
754 RESP_APPL_ID => respApplID,
755 APPLICATION_ID => applID,
756 SITE_LEVEL => true);
757
758 wf_engine.SetItemAttrText (itemtype => itemtype,
759 itemkey => itemkey,
760 aname => 'APPROVAL_URL',
761 avalue => approvalURL);
762
763 -- Set the Owner UserID
764 -- first get the userID from the profile option
765 ownerUsername := get_wf_owner_username (
766 p_request_id => requestID,
767 p_request_type => requestType);
768
769 wf_engine.SetItemAttrText (itemtype => itemtype,
770 itemkey => itemkey,
771 aname => 'OWNER_USERNAME',
772 avalue => ownerUsername);
773
774 -- Set the timeout value
775 -- first get the timeout from the profile option
776 timeout := nvl (JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
777 NAME => 'JTF_UM_APPROVAL_TIMEOUT_MINS',
778 APPLICATION_ID => applID,
779 SITE_LEVEL => true), 0);
780
781 wf_engine.SetItemAttrNumber (itemtype => itemtype,
782 itemkey => itemkey,
783 aname => 'MIN_TO_TIMEOUT',
784 avalue => timeout);
785
786 -- Set the application url
787 -- first get the application url from the profile option
788 applicationURL := JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
789 NAME => 'JTA_UM_APPL_URL',
790 RESPONSIBILITY_ID => responID,
791 RESP_APPL_ID => respApplID,
792 APPLICATION_ID => applID,
793 SITE_LEVEL => true);
794
795 wf_engine.SetItemAttrText (itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'APPLICATION_URL',
798 avalue => applicationURL);
799
800 -- Set the sender name
801 -- first get the sender name from the profile option
802 senderName := JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
803 NAME => 'JTA_UM_SENDER',
804 APPLICATION_ID => applID,
805 SITE_LEVEL => true);
806
807 wf_engine.SetItemAttrText (itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'SENDER_NAME',
810 avalue => senderName);
811
812 -- Set the Support Contact
813 -- first get the Support Contact name from the profile option
814 supportContact := JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
815 NAME => 'JTA_UM_SUPPORT_CONTACT',
816 RESPONSIBILITY_ID => responID,
817 RESP_APPL_ID => respApplID,
818 APPLICATION_ID => applID,
819 SITE_LEVEL => true);
820
821 wf_engine.SetItemAttrText (itemtype => itemtype,
822 itemkey => itemkey,
823 aname => 'SUPPORT_CONTACT',
824 avalue => supportContact);
825
826 -- get the usertype key from usertype id
827 open getUsertypeKey;
828 fetch getUsertypeKey into usertypeKey;
829 close getUsertypeKey;
830
831 if (requestType = 'USERTYPE') and ((usertypeKey = 'PRIMARYUSER') or (usertypeKey = 'PRIMARYUSERNEW')) then
832 open getCompanyNumber;
833 fetch getCompanyNumber into companyNumber;
834 close getCompanyNumber;
835
836 wf_engine.SetItemAttrText (
837 itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'COMPANY_NUMBER',
840 avalue => to_char(companyNumber));
841
842 end if;
843
844 -- Get the requester Username.
845 requesterUserName := getRequesterUsername (requesterUserID);
846
847 wf_engine.SetItemAttrText (itemtype => itemtype,
848 itemkey => itemkey,
849 aname => 'REQUESTER_USERNAME',
850 avalue => requesterUserName);
851
852
853 wf_engine.SetItemAttrText (itemtype => itemtype,
854 itemkey => itemkey,
855 aname => 'REQUESTER_USERNAME_DISPLAY',
856 avalue => lower (requesterUserName));
857
858 -- set the ad hoc role
859 x_role_name:='__JTA_UM' || itemkey;
860
861 open getEmailAddress;
862 fetch getEmailAddress into Email_Address;
863
864 x_wf_dispname :=requesterUserName;
865
866
867 --------------Bug No: 7661549-------------------
868 l_notif_pref := fnd_profile.value_specific('JTA_UM_MAIL_PREFERENCE'); --added for bug# 7661549
869 l_role_name := null;
870 --------------Bug No: 7270214-------------------
871 open getADHocRole;
872 fetch getADHocRole into l_role_name, l_approver_display_name;
873 close getADHocRole;
874
875 if (l_role_name is null) then
876 WF_DIRECTORY.CreateAdHocRole(role_name => x_role_name, role_display_name =>x_wf_dispname, email_address => Email_Address, notification_preference =>nvl(l_notif_pref,'MAILHTML') );
877 else
878 WF_DIRECTORY.SetAdHocRoleAttr(role_name=> x_role_name,email_address => Email_Address);
879 end if;
880
881 --------------Bug No: 7270214-------------------
882 --------------Bug No: 7661549-------------------
883 wf_engine.SetItemAttrText (itemtype => itemtype,
884 itemkey => itemkey,
885 aname => 'USER_AD_HOC_ROLE',
886 avalue => x_role_name);
887
888 resultout := 'COMPLETE:';
889 end if;
890
891 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Initialization API');
892
893 exception
894 when MISSING_REQUESTER_USER_ID then
895 wf_core.context ('JTF_UM_WF_APPROVAL', 'Initialization', itemtype, itemkey, to_char (actid), funcmode,
896 'Requester User ID is missing in the FND_USER');
897 raise;
898 when others then
899 wf_core.context ('JTF_UM_WF_APPROVAL', 'Initialization', itemtype, itemkey, to_char (actid), funcmode);
900 raise;
901 end Initialization;
902
903 /**
904 * Procedure: get_org_ad_hoc_role
905 * Type: Private
906 * Prereqs:
907 * Description: This API will
908 * 1) create/update an ad hoc role with named "JTAUM###".
909 * 2) find all approvers from the same organization and with
910 * "JTF_PRIMARY_USER_SUMMARY" permission.
911 * 3) associate the ad hoc role with approvers
912 * Parameters
913 * input parameters: p_itemtype - itemtype of the workflow
914 * p_itemkey - itemkey of the workflow
915 * output parameters: x_role_name - The name of the ad hoc role, null if
916 * role didn't get created.
917 * x_role_name_display - The display name of the ad hoc role.
918 * Errors:
919 * Other Comments:
920 */
921 procedure get_org_ad_hoc_role (p_itemtype in varchar2,
922 p_itemkey in varchar2,
923 x_role_name out NOCOPY varchar2,
924 x_role_name_display out NOCOPY varchar2) is
925
926 l_method_name varchar2 (20) := 'GET_ORG_AD_HOC_ROLE';
927 l_requester_user_id fnd_user.user_id%type;
928 l_org_name hz_parties.party_name%type;
929 l_org_number hz_parties.party_number%type;
930 l_uni_approver_not_found boolean := true;
931 l_role_name wf_local_roles.name%type;
932 l_approver_display_name varchar2(1000);
933 l_notif_pref WF_LOCAL_ROLES.NOTIFICATION_PREFERENCE%TYPE;
934
935 cursor getADHocRole is
936 select name, display_name
937 from WF_LOCAL_ROLES
938 where name = x_role_name;
939
940 cursor getUniversalApprovers is
941 select fnd.user_name
942 from hz_parties hz_org, hz_relationships hzr, fnd_user fnd
943 where hz_org.party_number = l_org_number
944 and hz_org.party_type = 'ORGANIZATION'
945 and hz_org.party_id = hzr.object_id
946 and hzr.start_date <= sysdate
947 and nvl (hzr.end_date, sysdate + 1) > sysdate
948 and hzr.relationship_code = 'EMPLOYEE_OF'
949 and hzr.object_table_name = 'HZ_PARTIES'
950 and hzr.subject_table_name = 'HZ_PARTIES'
951 and fnd.customer_id = hzr.party_id
952 and fnd.start_date <= sysdate
953 and nvl (fnd.end_date, sysdate + 1) > sysdate
954
955 and exists (
956 select prin_b.principal_name
957 from jtf_auth_domains_b domains_b, jtf_auth_permissions_b perm,
958 jtf_auth_principal_maps prin_maps, jtf_auth_role_perms role_perms,
959 jtf_auth_principals_b prin_b, jtf_auth_principals_b prin_b2
960 where prin_b.jtf_auth_principal_id = prin_maps.jtf_auth_principal_id
961 and prin_maps.jtf_auth_parent_principal_id = prin_b2.jtf_auth_principal_id
962 and prin_b2.jtf_auth_principal_id = role_perms.jtf_auth_principal_id
963 and role_perms.jtf_auth_permission_id = perm.jtf_auth_permission_id
964 and prin_maps.jtf_auth_domain_id = domains_b.jtf_auth_domain_id
965 and domains_b.domain_name = 'CRM_DOMAIN'
966 and perm.permission_name = 'JTF_PRIMARY_USER_SUMMARY'
967 and prin_b.principal_name = fnd.user_name
968 );
969 --changes for 4734470
970
971 UserTable WF_DIRECTORY.UserTable;
972 idx pls_integer :=0;
973
974 begin
975
976 -- Log the entering
977 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (G_MODULE, l_method_name);
978
979 -- Log parameters
980 if l_is_debug_parameter_on then
981 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
982 'p_itemtype=' || p_itemtype);
983 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
984 'p_itemkey=' || p_itemkey);
985 end if;
986
987 -- to construct the roleName, we need the organization number
988 -- get the user id and find out what is his/her org number
989 l_requester_user_id := wf_engine.GetItemAttrNumber (
990 itemtype => p_itemtype,
991 itemkey => p_itemkey,
992 aname => 'REQUESTER_USER_ID');
993
994 -- get the organization number
995 get_org_info (p_user_id => l_requester_user_id,
996 x_org_name => l_org_name,
997 x_org_number => l_org_number);
998
999 -- the name of the role
1000 x_role_name := g_adhoc_role_name_prefix || l_org_number;
1001
1002 open getAdHocRole;
1003 fetch getAdHocRole into l_role_name, l_approver_display_name;
1004 if (getAdHocRole%found) then
1005 -- Update role
1006 WF_DIRECTORY.RemoveUsersFromAdHocRole (role_name => x_role_name);
1007 else
1008 -- Get the role display name from FND Message.
1009 fnd_message.set_name ('JTF', 'JTA_UM_APPROVAL_ROLE_DISP_NAME');
1010 fnd_message.set_token ('ORGNAME', l_org_name, false);
1011 fnd_message.set_token ('ORGNUMBER', l_org_number, false);
1012 l_approver_display_name := fnd_message.get;
1013
1014 x_role_name_display:= substr(l_approver_display_name, 1, 100);
1015
1016 -- Create role
1017 -- Changes for Bug 6010991
1018 -- restore to behaviour, prior to bug Bug 3361734,
1019 -- of JTA Ad Hoc Roles having notification pref of MAILHTML
1020 --
1021 l_notif_pref := nvl(fnd_profile.value_specific('JTA_UM_MAIL_PREFERENCE'),'MAILHTML'); --added for bug# 7661549
1022 WF_DIRECTORY.CreateAdHocRole (role_name => x_role_name,
1023 role_display_name => x_role_name_display,
1024 notification_preference =>l_notif_pref
1025 );
1026 -- End of changes for Bug 6010991
1027 end if;
1028 close getAdHocRole;
1029
1030 for approver in getUniversalApprovers loop
1031 userTable(idx) := approver.user_name;
1032 idx :=idx + 1;
1033 end loop;
1034 If userTable.count >0 then
1035 l_uni_approver_not_found := false;
1036 WF_DIRECTORY.AddUsersToAdHocRole2 (role_name => x_role_name,
1037 role_users => userTable);
1038 end if;
1039
1040 if l_uni_approver_not_found then
1041 x_role_name := null;
1042 end if;
1043
1044 JTF_DEBUG_PUB.LOG_EXITING_METHOD (G_MODULE, l_method_name);
1045
1046 end get_org_ad_hoc_role;
1047
1048 --
1049 -- SelectApprover
1050 -- DESCRIPTION
1051 -- Select the next approver from the approver order.
1052 -- IN
1053 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1054 -- itemkey - A string generated from the application object's primary key.
1055 -- actid - The function activity(instance id).
1056 -- funcmode - Run/Cancel/Timeout
1057 -- OUT
1058 -- Resultout - 'COMPLETE:T' if there is a next approver
1059 -- - 'COMPLETE:F' if there is not a next approver
1060 --
1061 procedure SelectApprover (itemtype in varchar2,
1062 itemkey in varchar2,
1063 actid in number,
1064 funcmode in varchar2,
1065 resultout out NOCOPY varchar2) is
1066 --
1067 applID number;
1068 approverID number (15);
1069 approverUsername fnd_user.user_name%type;
1070 approverUserID fnd_user.user_id%type;
1071 requestType varchar2 (10);
1072 requestId number;
1073 resultType varchar2 (5);
1074 uniPrimaryUser fnd_profile_option_values.profile_option_value%type;
1075 l_approver_username_display varchar2 (100);
1076
1077 begin
1078 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering SelectApprover (' ||
1079 itemtype || ',' || itemkey || ',' || actid || ',' ||
1080 funcmode || ') API');
1081
1082 --
1083 -- RUN mode - normal process execution
1084 --
1085 if (funcmode = 'RUN') then
1086 --
1087 -- Call API to retrieve the next approver.
1088 --
1089 GetApprover (itemtype, itemkey, approverUsername, approverUserID, approverID, resultType);
1090
1091 --
1092 -- There are two resultTypes from GetApprover.
1093 -- OK - The api returns the next approver.
1094 -- END - There is no more approver in the approver order.
1095 --
1096 if (resultType = 'END') then
1097 -- Can't find any approver from GetApprover
1098 resultout := 'COMPLETE:F';
1099 else
1100 resultout := 'COMPLETE:T';
1101
1102 -- Check to see if the approver is a Universal Approver
1103 -- Get the application id of the requestType
1104 requestType := wf_engine.GetItemAttrText (
1105 itemtype => itemtype,
1106 itemkey => itemkey,
1107 aname => 'REQUEST_TYPE');
1108
1109 requestId := wf_engine.GetItemAttrNumber (
1110 itemtype => itemtype,
1111 itemkey => itemkey,
1112 aname => 'REQUEST_ID');
1113
1114 applID := getRequestApplId (requestType, requestId);
1115
1116 uniPrimaryUser := JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
1117 NAME => 'JTF_PRIMARY_USER',
1118 APPLICATION_ID => applID,
1119 SITE_LEVEL => true);
1120
1121 if (approverUsername = uniPrimaryUser) then
1122 -- the current approver is a Universal Approver
1123 get_org_ad_hoc_role (p_itemtype => itemtype,
1124 p_itemkey => itemkey,
1125 x_role_name => approverUsername,
1126 x_role_name_display => l_approver_username_display);
1127 if (approverUsername is null) then
1128 -- Which mean an ad hoc role didn't get created. Use
1129 -- the default approver from the JTA_UM_DEFAULT_APPROVER
1130 -- profile option.
1131 approverUsername := nvl (JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
1132 NAME => 'JTA_UM_DEFAULT_APPROVER',
1133 APPLICATION_ID => applID,
1134 SITE_LEVEL => true), 'SYSADMIN');
1135
1136 l_approver_username_display := lower (approverUsername);
1137 approverUserID := getUserID (username => approverUsername);
1138 end if;
1139 else
1140 l_approver_username_display := lower (approverUsername);
1141 end if;
1142
1143 -- We need to update the approver username and id
1144 wf_engine.SetItemAttrText (
1145 itemtype => itemtype,
1146 itemkey => itemkey,
1147 aname => 'APPROVER_USERNAME',
1148 avalue => approverUsername);
1149
1150 wf_engine.SetItemAttrText (
1151 itemtype => itemtype,
1152 itemkey => itemkey,
1153 aname => 'APPROVER_USERNAME_DISPLAY',
1154 avalue => l_approver_username_display);
1155
1156 wf_engine.SetItemAttrNumber (
1157 itemtype => itemtype,
1158 itemkey => itemkey,
1159 aname => 'APPROVER_ID',
1160 avalue => approverID);
1161
1162 -- update the APPROVER_USER_ID in the ****_REG table.
1163 requestType := wf_engine.GetItemAttrText (
1164 itemtype => itemtype,
1165 itemkey => itemkey,
1166 aname => 'REQUEST_TYPE');
1167 if (requestType = 'USERTYPE') then
1168 update JTF_UM_USERTYPE_REG
1169 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1170 LAST_UPDATE_DATE = sysdate,
1171 APPROVER_USER_ID = approverUserID
1172 where USERTYPE_REG_ID = itemkey;
1173 else
1174 update JTF_UM_SUBSCRIPTION_REG
1175 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1176 LAST_UPDATE_DATE = sysdate,
1177 APPROVER_USER_ID = approverUserID
1178 where SUBSCRIPTION_REG_ID = itemkey;
1179 end if;
1180 end if;
1181 --
1182 end if;
1183
1184 --
1185 -- CANCEL mode
1186 --
1187 if (funcmode = 'CANCEL') then
1188 --
1189 resultout := 'COMPLETE:';
1190 --
1191 end if;
1192
1193 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Initialization API');
1194
1195 exception
1196 when others then
1197 wf_core.context ('JTF_UM_WF_APPROVAL', 'SelectorApprover', itemtype,
1198 itemkey, to_char (actid), funcmode);
1199 raise;
1200 end SelectApprover;
1201
1202 --
1203 -- Procedure
1204 -- GetApprover
1205 --
1206 -- Description
1207 -- Private method to get the next approver
1208 -- IN
1209 -- itemtype - workflow itemtype
1210 -- itemkey - workflow itemkey
1211 -- Out
1212 -- approver's user_name
1213 -- approver's user ID
1214 -- approver ID
1215 -- resultType - 'OK' return next approver.
1216 -- 'END' no more approver in the approver list.
1217 --
1218 Procedure GetApprover (x_itemtype in varchar2,
1219 x_itemkey in varchar2,
1220 x_approverUsername out NOCOPY varchar2,
1221 x_approverUserID out NOCOPY number,
1222 x_approverID out NOCOPY number,
1223 x_resultType out NOCOPY varchar2) is
1224 --
1225 l_approvalID number (15);
1226 l_approverSeq number (15);
1227 l_requesterUserID number (15);
1228 l_requestType varchar2 (10);
1229 l_org_party_id number;
1230 l_org_override varchar2 (1);
1231 --
1232 cursor approverSequenceCursor is
1233 select APPROVER_SEQ
1234 from JTF_UM_APPROVERS
1235 where APPROVER_ID = x_approverID
1236 and APPROVAL_ID = l_approvalID;
1237
1238 cursor nextApproverInfoCursor is
1239 select a.APPROVER_ID, a.USER_ID, f.USER_NAME
1240 from JTF_UM_APPROVERS a, FND_USER f
1241 where a.APPROVER_SEQ > l_approverSeq
1242 and a.APPROVAL_ID = l_approvalID
1243 and a.org_party_id is null
1244 and a.EFFECTIVE_START_DATE <= sysdate
1245 and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1246 and a.USER_ID = f.USER_ID
1247 and f.START_DATE <= sysdate
1248 and nvl (f.END_DATE, sysdate + 1) > sysdate
1249
1250 order by a.APPROVER_SEQ;
1251
1252 cursor nextOrgApproverInfoCursor is
1253 select a.APPROVER_ID, a.USER_ID, f.USER_NAME
1254 from JTF_UM_APPROVERS a, FND_USER f
1255 where a.APPROVER_SEQ > l_approverSeq
1256 and a.APPROVAL_ID = l_approvalID
1257 and a.ORG_PARTY_ID = l_org_party_id
1258 and a.EFFECTIVE_START_DATE <= sysdate
1259 and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1260 and a.USER_ID = f.USER_ID
1261 and f.START_DATE <= sysdate
1262 and nvl (f.END_DATE, sysdate + 1) > sysdate
1263 order by a.APPROVER_SEQ;
1264
1265
1266 cursor OrgApproverOverrideCursor is
1267 select 'X'
1268 from JTF_UM_APPROVERS a,
1269 FND_USER f
1270 where a.APPROVAL_ID = l_approvalID
1271 and a.ORG_PARTY_ID = l_org_party_id
1272 and a.EFFECTIVE_START_DATE <= sysdate
1273 and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1274 and a.USER_ID = f.USER_ID
1275 and f.START_DATE <= sysdate
1276 and nvl (f.END_DATE, sysdate + 1) > sysdate;
1277
1278 -- select the requesters org party id
1279 cursor requesterOrgCursor is
1280 select hzr.object_id requester_org_id
1281 from hz_relationships hzr,
1282 FND_USER fu
1283 where fu.USER_ID = l_requesterUserID
1284 and fu.CUSTOMER_ID = hzr.PARTY_ID
1285 and hzr.start_date <= sysdate
1286 and nvl (hzr.END_DATE, sysdate + 1) > sysdate
1287 and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
1288 and hzr.object_type = 'ORGANIZATION'
1289 and hzr.subject_table_name = 'HZ_PARTIES'
1290 and hzr.object_table_name = 'HZ_PARTIES';
1291 --
1292 begin
1293
1294 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering GetApprover (' ||
1295 x_itemtype || ',' || x_itemkey || ') API');
1296
1297 -- check input parameter
1298 if (x_itemtype is null) then
1299 raise_application_error (-20000, 'itemtype is null when calling JTF_UM_WF_APPROVAL.GetApprover');
1300 end if;
1301
1302 if (x_itemkey is null) then
1303 raise_application_error (-20000, 'itemkey is null when calling JTF_UM_WF_APPROVAL.GetApprover');
1304 end if;
1305
1306 x_resultType := 'OK';
1307 -- Get the APPROVER_ID and APPROVAL_ID
1308 x_approverID := wf_engine.GetItemAttrNumber (
1309 itemtype => x_itemtype,
1310 itemkey => x_itemkey,
1311 aname => 'APPROVER_ID');
1312
1313 l_approvalID := wf_engine.GetItemAttrNumber (
1314 itemtype => x_itemtype,
1315 itemkey => x_itemkey,
1316 aname => 'APPROVAL_ID');
1317
1318 l_requesterUserID := wf_engine.GetItemAttrNumber (
1319 itemtype => x_itemtype,
1320 itemkey => x_itemkey,
1321 aname => 'REQUESTER_USER_ID');
1322
1323 l_requestType := wf_engine.GetItemAttrText (
1324 itemtype => x_itemtype,
1325 itemkey => x_itemkey,
1326 aname => 'REQUEST_TYPE');
1327
1328 -- Get the requesters Organization Party Id
1329 -- if null, then we use the default approvers
1330 open requesterOrgCursor;
1331 fetch requesterOrgCursor into l_org_party_id;
1332 -- Are there any org specific approvers for the requesters org?
1333 -- if not we use the default approvers
1334 if requesterOrgCursor%FOUND then
1335 open OrgApproverOverrideCursor;
1336 fetch OrgApproverOverrideCursor into l_org_override;
1337 close OrgApproverOverrideCursor;
1338 end if;
1339 close requesterOrgCursor;
1340
1341
1342 -- if APPROVER_ID is null, then approverSeq will be 1, the first approver.
1343 -- else, use the APPROVER_ID to find the next approver.
1344 if (x_approverID is null) then
1345 l_approverSeq := -1;
1346 else
1347 open approverSequenceCursor;
1348 fetch approverSequenceCursor into l_approverSeq;
1349 if (approverSequenceCursor%notfound) then
1350 -- ERROR, can't find the approver's sequence.
1351 close approverSequenceCursor;
1352 wf_core.token ('MESSAGE', 'Cannot find the current approver (user_id = '||to_char(x_approverId)
1353 ||' approval_id ='||to_char(l_approvalId)||') in JTF_UM_APPROVERS - Data corruption.');
1354 wf_core.raise ('MISSING_APPROVER_SEQUENCE');
1355 end if;
1356 close approverSequenceCursor;
1357 end if;
1358
1359 -- If there are org specific approvers, get the first/next one
1360 if l_org_override is not null then
1361 open nextOrgApproverInfoCursor;
1362 fetch nextOrgApproverInfoCursor into x_approverID, x_approverUserID, x_approverUserName;
1363 if (nextOrgApproverInfoCursor%notfound) then
1364 -- No more approvers, result 'END' -
1365 x_resultType := 'END';
1366 close nextOrgApproverInfoCursor;
1367 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting GetApprover API');
1368 return;
1369 end if;
1370 close nextOrgApproverInfoCursor;
1371 -- If we should use the default approvers, find the first / next one
1372 else
1373
1374 open nextApproverInfoCursor;
1375 fetch nextApproverInfoCursor into x_approverID, x_approverUserID, x_approverUserName;
1376 if (nextApproverInfoCursor%notfound) then
1377 -- No more approvers, result 'END'
1378 x_resultType := 'END';
1379 close nextApproverInfoCursor;
1380 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting GetApprover API');
1381 return;
1382 end if;
1383 close nextApproverInfoCursor;
1384 end if;
1385
1386 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting GetApprover API');
1387
1388 exception
1389 when others then
1390 wf_core.context ('JTF_UM_WF_APPROVAL', 'GetApprover', x_itemtype, x_itemkey);
1391 raise;
1392 end GetApprover;
1393
1394 --
1395 -- SelectRequestType
1396 -- DESCRIPTION
1397 -- Return what requesttype that the requester is requesting.
1398 -- IN
1399 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1400 -- itemkey - A string generated from the application object's primary key.
1401 -- actid - The function activity(instance id).
1402 -- funcmode - Run/Cancel/Timeout
1403 -- OUT
1404 -- Resultout - 'COMPLETE:USERTYPE' if it is a usertype request
1405 -- - 'COMPLETE:ENROLLMENT' if it is a enrollment request
1406 --
1407 procedure SelectRequestType (itemtype in varchar2,
1408 itemkey in varchar2,
1409 actid in number,
1410 funcmode in varchar2,
1411 resultout out NOCOPY varchar2) is
1412 --
1413 requestType varchar2 (10);
1414 --
1415 begin
1416
1417 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Initialization (' ||
1418 itemtype || ',' || itemkey || ',' || actid || ',' ||
1419 funcmode || ') API');
1420
1421 --
1422 -- RUN mode - normal process execution
1423 --
1424 if (funcmode = 'RUN') then
1425 requestType := wf_engine.GetItemAttrText (
1426 itemtype => itemtype,
1427 itemkey => itemkey,
1428 aname => 'REQUEST_TYPE');
1429 if (requestType = 'USERTYPE') then
1430 resultout := 'COMPLETE:USERTYPE';
1431 else
1432 resultout := 'COMPLETE:ENROLLMENT';
1433 end if;
1434 --
1435 -- CANCEL mode
1436 --
1437 elsif (funcmode = 'CANCEL') then
1438 --
1439 resultout := 'COMPLETE:';
1440 --
1441 --
1442 -- TIMEOUT mode
1443 --
1444 elsif (funcmode = 'TIMEOUT') then
1445 resultout := 'COMPLETE:';
1446 end if;
1447
1448 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting SelectRequestType API');
1449
1450 exception
1451 when others then
1452 wf_core.context ('JTF_UM_WF_APPROVAL', 'SelectRequestType', itemtype, itemkey, to_char (actid), funcmode);
1453 raise;
1454 end SelectRequestType;
1455
1456 --
1457 -- cancel_notification
1458 -- DESCRIPTION
1459 -- Cancel all open notifications
1460 -- IN
1461 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
1462 -- p_itemkey - A string generated from the application object's primary key.
1463 --
1464 procedure cancel_notification (p_itemtype in varchar2,
1465 p_itemkey in varchar2) is
1466 --
1467 notificationID number (15);
1468
1469 cursor getNotificationID is
1470 select wias.notification_id
1471 from wf_process_activities wpa, wf_item_activity_statuses wias, wf_notifications wn
1472 where wpa.PROCESS_ITEM_TYPE = p_itemtype
1473 and wpa.ACTIVITY_ITEM_TYPE = wpa.PROCESS_ITEM_TYPE
1474 and (wpa.INSTANCE_LABEL = 'NTF_APPROVAL_USERTYPE_REQUIRED'
1475 or wpa.INSTANCE_LABEL = 'NTF_REMIND_USERTYPE_REQUIRED'
1476 or wpa.INSTANCE_LABEL = 'NTF_FAIL_ESCALATE_USERTYPE_REQ')
1477 and wias.item_type = wpa.PROCESS_ITEM_TYPE
1478 and wias.item_key = p_itemkey
1479 and wias.process_activity = wpa.instance_id
1480 and wn.status = 'OPEN'
1481 and wn.notification_id = wias.notification_id;
1482 --
1483 begin
1484
1485 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering cancel_notification (' ||
1486 p_itemtype || ',' || p_itemkey || ') API');
1487
1488 -- check input parameter
1489 if (p_itemtype is null) then
1490 raise_application_error (-20000, 'itemtype is null when calling JTF_UM_WF_APPROVAL.cancel_notification');
1491 end if;
1492
1493 if (p_itemkey is null) then
1494 raise_application_error (-20000, 'itemkey is null when calling JTF_UM_WF_APPROVAL.cancel_notification');
1495 end if;
1496
1497 -- Need to end all open notifications.
1498 open getNotificationID;
1499 -- We have two notification that we need to cancel.
1500 fetch getNotificationID into notificationID;
1501 while getNotificationID%found loop
1502 wf_notification.cancel (notificationID);
1503 fetch getNotificationID into notificationID;
1504 end loop;
1505 close getNotificationID;
1506
1507 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting cancel_notification API');
1508
1509 end cancel_notification;
1510
1511 --
1512 -- initialize_fail_escalate
1513 -- DESCRIPTION
1514 -- Update the reg table and performer when fail to escalate approver.
1515 -- IN
1516 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1517 -- itemkey - A string generated from the application object's primary key.
1518 -- actid - The function activity(instance id).
1519 -- funcmode
1520 -- OUT
1521 -- Resultout - 'COMPLETE:'
1522 --
1523 procedure initialize_fail_escalate (itemtype in varchar2,
1524 itemkey in varchar2,
1525 actid in number,
1526 funcmode in varchar2,
1527 resultout out NOCOPY varchar2) is
1528 --
1529 ownerUsername varchar2 (100);
1530 ownerUserID number (15);
1531 requestType varchar2 (10);
1532
1533 cursor getUserID is
1534 select USER_ID
1535 from FND_USER
1536 where USER_NAME = ownerUsername;
1537 --
1538 begin
1539
1540 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
1541 'Entering initialize_fail_escalate (' || itemtype || ',' ||
1542 itemkey || ',' || actid || ',' || funcmode || ') API');
1543
1544 --
1545 -- RUN mode
1546 --
1547 if (funcmode = 'RUN') then
1548
1549 -- Need to end all open notifications.
1550 cancel_notification (itemtype, itemkey);
1551
1552 -- The result of the request has not been decided
1553 -- Forward the request to the owner of this process.
1554 ownerUsername := wf_engine.GetItemAttrText (
1555 itemtype => itemtype,
1556 itemkey => itemkey,
1557 aname => 'OWNER_USERNAME');
1558
1559 -- This is for the next activity in the workflow, can_delegate.
1560 wf_engine.SetItemAttrText (
1561 itemtype => itemtype,
1562 itemkey => itemkey,
1563 aname => 'APPROVER_USERNAME',
1564 avalue => ownerUsername);
1565
1566 open getUserID;
1567 fetch getUserID into ownerUserID;
1568 close getUserID;
1569
1570 -- Get the requestType.
1571 requestType := wf_engine.GetItemAttrText (
1572 itemtype => itemtype,
1573 itemkey => itemkey,
1574 aname => 'REQUEST_TYPE');
1575
1576 -- We need to update the approver id in the reg table
1577 if (requestType = 'USERTYPE') then
1578 update JTF_UM_USERTYPE_REG
1579 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1580 LAST_UPDATE_DATE = sysdate,
1581 APPROVER_USER_ID = ownerUserID
1582 where USERTYPE_REG_ID = to_number(itemkey);
1583 elsif (requestType = 'ENROLLMENT') then
1584 update JTF_UM_SUBSCRIPTION_REG
1585 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1586 LAST_UPDATE_DATE = sysdate,
1587 APPROVER_USER_ID = ownerUserID
1588 where SUBSCRIPTION_REG_ID = to_number(itemkey);
1589 end if;
1590 resultout := 'COMPLETE';
1591 end if;
1592
1593 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting initialize_fail_escalate API');
1594
1595 exception
1596 when others then
1597 wf_core.context ('JTF_UM_WF_APPROVAL', 'initialize_fail_escalate', itemtype, itemkey, to_char (actid), funcmode);
1598 raise;
1599 end initialize_fail_escalate;
1600
1601 --
1602 -- WaitForApproval
1603 -- DESCRIPTION
1604 -- Check whether the task is approved or rejected.
1605 -- IN
1606 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1607 -- itemkey - A string generated from the application object's primary key.
1608 -- actid - The function activity(instance id).
1609 -- funcmode - Run/Cancel/Timeout
1610 -- OUT
1611 -- Resultout - 'COMPLETE:APPROVED' if the request is approved.
1612 -- - 'COMPLETE:REJECTED' if the request is rejected.
1613 --
1614 procedure WaitForApproval (itemtype in varchar2,
1615 itemkey in varchar2,
1616 actid in number,
1617 funcmode in varchar2,
1618 resultout out NOCOPY varchar2) is
1619 --
1620 requestResult varchar2 (8);
1621 --
1622 begin
1623
1624 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering WaitForApproval (' ||
1625 itemtype || ',' || itemkey || ',' || actid || ',' ||
1626 funcmode || ') API');
1627
1628 --
1629 -- RUN mode - normal process execution
1630 --
1631 if (funcmode = 'RUN') then
1632 requestResult := wf_engine.GetItemAttrText (
1633 itemtype => itemtype,
1634 itemkey => itemkey,
1635 aname => 'REQUEST_RESULT');
1636
1637 if (requestResult = 'APPROVED') then
1638 resultout := 'COMPLETE:APPROVED';
1639 elsif (requestResult = 'REJECTED') then
1640 resultout := 'COMPLETE:REJECTED';
1641 else
1642 fnd_message.set_name ('JTF', 'JTA_UM_REQUIRED_FIELD');
1643 fnd_message.set_token ('API_NAME', itemtype, false);
1644 fnd_message.set_token ('FIELD', 'REQUEST_RESULT', false);
1645 raise_application_error(-20000, fnd_message.get);
1646 end if;
1647
1648 --
1649 -- CANCEL mode
1650 --
1651 elsif (funcmode = 'CANCEL') then
1652 --
1653 resultout := 'COMPLETE:';
1654 --
1655
1656 --
1657 -- TIMEOUT mode
1658 --
1659 elsif (funcmode = 'TIMEOUT') then
1660 resultout := 'COMPLETE:';
1661 end if;
1662
1663 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting WaitForApproval API');
1664
1665 exception
1666 when others then
1667 wf_core.context ('JTF_UM_WF_APPROVAL', 'WaitForApproval', itemtype, itemkey, to_char (actid), funcmode);
1668 raise;
1669 end WaitForApproval;
1670
1671 --
1672 -- post_notification
1673 -- DESCRIPTION
1674 -- Update the reg table when notification is transfered.
1675 -- IN
1676 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1677 -- itemkey - A string generated from the application object's primary key.
1678 -- actid - The function activity(instance id).
1679 -- funcmode - FORWARD/TRANSFER
1680 -- OUT
1681 -- Resultout - 'COMPLETE:'
1682 --
1683 procedure post_notification (itemtype in varchar2,
1684 itemkey in varchar2,
1685 actid in number,
1686 funcmode in varchar2,
1687 resultout out NOCOPY varchar2) is
1688 --
1689 requestType varchar2 (10);
1690 userId number;
1691 l_permission_flag number;
1692 l_return_status varchar2 (1);
1693
1694 cursor getUserID is
1695 select USER_ID
1696 from FND_USER
1697 where USER_NAME = WF_ENGINE.context_text;
1698 --
1699 begin
1700
1701 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering post_notification (' ||
1702 itemtype || ',' || itemkey || ',' || actid || ',' ||
1703 funcmode || ') API');
1704
1705 --
1706 -- FORWARD or TRANSFER mode
1707 --
1708 if (funcmode = 'FORWARD') or (funcmode = 'TRANSFER') then
1709 -- Get the new recipient_role. In our case, a new userID.
1710 open getUserID;
1711 fetch getUserID into userId;
1712 close getUserID;
1713 -- First verifty if the new userId has the valid permission
1714 -- Check if the user is the SYSADMIN
1715 JTF_AUTH_SECURITY_PKG.CHECK_PERMISSION (
1716 x_flag => l_permission_flag,
1717 x_return_status => l_return_status,
1718 p_user_name => WF_ENGINE.context_text,
1719 p_permission_name => 'JTF_REG_APPROVAL');
1720 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1721 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1722 end if;
1723
1724 if (l_permission_flag = 0) then
1725 -- Not a SYSADMIN, check if the user is a Primary User
1726 JTF_AUTH_SECURITY_PKG.CHECK_PERMISSION (
1727 x_flag => l_permission_flag,
1728 x_return_status => l_return_status,
1729 p_user_name => WF_ENGINE.context_text,
1730 p_permission_name => 'JTF_PRIMARY_USER_SUMMARY');
1731 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1732 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1733 end if;
1734 end if;
1735
1736 if (l_permission_flag = 0) then
1737 -- Not a Primary User, check if the user is an Owner
1738 JTF_AUTH_SECURITY_PKG.CHECK_PERMISSION (
1739 x_flag => l_permission_flag,
1740 x_return_status => l_return_status,
1741 p_user_name => WF_ENGINE.context_text,
1742 p_permission_name => 'JTF_APPROVER');
1743 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1744 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1745 end if;
1746 end if;
1747
1748 if (l_permission_flag = 0) then
1749 -- Doesn't have the permission to be an approver.
1750 fnd_message.set_name ('JTF', 'JTF_APPROVAL_PERMISSION');
1751 raise_application_error (-20000, fnd_message.get);
1752 else
1753 -- Get the requestType.
1754 requestType := wf_engine.GetItemAttrText (
1755 itemtype => itemtype,
1756 itemkey => itemkey,
1757 aname => 'REQUEST_TYPE');
1758 --
1759 if (requestType = 'USERTYPE') then
1760 update JTF_UM_USERTYPE_REG
1761 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1762 LAST_UPDATE_DATE = sysdate,
1763 APPROVER_USER_ID = userId
1764 where USERTYPE_REG_ID = to_number(itemkey);
1765 elsif (requestType = 'ENROLLMENT') then
1766 update JTF_UM_SUBSCRIPTION_REG
1767 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1768 LAST_UPDATE_DATE = sysdate,
1769 APPROVER_USER_ID = userId
1770 where SUBSCRIPTION_REG_ID = to_number(itemkey);
1771 end if;
1772 resultout := 'COMPLETE';
1773 end if;
1774 end if;
1775
1776 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting post_notification API');
1777
1778 exception
1779 when others then
1780 wf_core.context ('JTF_UM_WF_APPROVAL', 'post_notification', itemtype, itemkey, to_char (actid), funcmode);
1781 raise;
1782 end post_notification;
1783
1784 --
1785 -- store_delegate_flag
1786 -- DESCRIPTION
1787 -- Store the delegate flag into the database
1788 -- IN
1789 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1790 -- itemkey - A string generated from the application object's primary key.
1791 -- actid - The function activity(instance id).
1792 -- funcmode - FORWARD/TRANSFER
1793 -- OUT
1794 -- Resultout - 'COMPLETE:'
1795 --
1796 procedure store_delegate_flag (itemtype in varchar2,
1797 itemkey in varchar2,
1798 actid in number,
1799 funcmode in varchar2,
1800 resultout out NOCOPY varchar2) is
1801
1802 l_bool_flag boolean;
1803 l_flag varchar2 (1);
1804 l_request_id number;
1805 l_requesterUserID number;
1806
1807 begin
1808
1809 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering store_delegate_flag (' ||
1810 itemtype || ',' || itemkey || ',' || actid || ',' ||
1811 funcmode || ') API');
1812
1813 --
1814 -- FORWARD or TRANSFER mode
1815 --
1816 if (funcmode = 'RUN') then
1817
1818 -- Save the Grant Delegation Flag
1819 l_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
1820 itemkey => itemkey,
1821 aname => 'DELEGATION_FLAG');
1822
1823 l_request_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1824 itemkey => itemkey,
1825 aname => 'REQUEST_ID');
1826
1827 l_requesterUserID := wf_engine.GetItemAttrNumber (
1828 itemtype => itemtype,
1829 itemkey => itemkey,
1830 aname => 'REQUESTER_USER_ID');
1831
1832 if (l_flag = 'Y') then
1833 l_bool_flag := true;
1834 else
1835 l_bool_flag := false;
1836 end if;
1837
1838 JTF_UM_SUBSCRIPTIONS_PKG.UPDATE_GRANT_DELEGATION_FLAG (
1839 P_SUBSCRIPTION_ID => l_request_id,
1840 P_USER_ID => l_requesterUserID,
1841 P_GRANT_DELEGATION_FLAG => l_bool_flag);
1842
1843 end if;
1844
1845 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting store_delegate_flag API');
1846
1847 exception
1848 when others then
1849 -- wf_core.context ('JTF_UM_WF_APPROVAL', 'store_delegate_flag', itemtype, itemkey, to_char (actid), funcmode);
1850 raise;
1851
1852 end store_delegate_flag;
1853
1854 --
1855 -- Procedure
1856 -- Do_Approve_Req
1857 --
1858 -- Description -
1859 -- Perform approve a request now
1860 --
1861 -- IN
1862 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1863 -- itemkey - A string generated from the application object's primary key.
1864 --
1865 procedure Do_Approve_Req (itemtype in varchar2,
1866 itemkey in varchar2) is
1867 --
1868 approverComment varchar2 (4000);
1869 requesterUserID number (15);
1870 requestID number (15);
1871 requestType varchar2 (10);
1872 requesterUsername varchar2 (100);
1873 langProfileValue varchar2 (240);
1874 terrProfileValue varchar2 (240);
1875 profileSave boolean;
1876
1877 cursor enrollmentNoApprovalCursor is
1878 select SUBSCRIPTION_ID
1879 from JTF_UM_SUBSCRIPTION_REG
1880 where USER_ID = requesterUserID
1881 and EFFECTIVE_START_DATE <= sysdate
1882 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1883 and WF_ITEM_TYPE is null
1884 and STATUS_CODE = 'PENDING';
1885
1886 cursor enrollmentApprovalCursor is
1887 select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID
1888 from JTF_UM_SUBSCRIPTION_REG
1889 where USER_ID = requesterUserID
1890 and EFFECTIVE_START_DATE <= sysdate
1891 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1892 and WF_ITEM_TYPE is not null
1893 and STATUS_CODE = 'PENDING';
1894
1895 cursor requesterUserNameCursor is
1896 select USER_NAME
1897 from FND_USER
1898 where USER_ID = requesterUserID
1899 and (nvl(END_DATE,sysdate) >= sysdate OR
1900 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
1901
1902 enrollAppRegRow enrollmentApprovalCursor%ROWTYPE;
1903 enrollNoAppRegRow enrollmentNoApprovalCursor%ROWTYPE;
1904
1905
1906
1907 begin
1908
1909 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Do_Approve_Req (' ||
1910 itemtype || ',' || itemkey || ') API');
1911
1912
1913 if itemtype is null then
1914 raise_application_error (-20000, 'itemtype is null while calling JTF_UM_WF_APPROVAL.Do_Approve_Req.');
1915 end if;
1916
1917 if itemkey is null then
1918 raise_application_error (-20000, 'itemkey is null while calling JTF_UM_WF_APPROVAL.Do_Approve_Req.');
1919 end if;
1920
1921 requesterUserID := wf_engine.GetItemAttrNumber (
1922 itemtype => itemtype,
1923 itemkey => itemkey,
1924 aname => 'REQUESTER_USER_ID');
1925
1926 requestType := wf_engine.GetItemAttrText (
1927 itemtype => itemtype,
1928 itemkey => itemkey,
1929 aname => 'REQUEST_TYPE');
1930
1931 requestID := wf_engine.GetItemAttrNumber (
1932 itemtype => itemtype,
1933 itemkey => itemkey,
1934 aname => 'REQUEST_ID');
1935
1936 approverComment := wf_engine.GetItemAttrText (
1937 itemtype => itemtype,
1938 itemkey => itemkey,
1939 aname => 'APPROVER_COMMENT');
1940
1941
1942 -- Get the username from userID
1943 requesterUsername := getRequesterUsername ( requesterUserID );
1944
1945 if (requestType = 'USERTYPE') then
1946 -- bug 7675285 Set the profile ICX_LANG and ICX_TERRITORY profiles for the user id being approved to match the entries
1947 -- for the ad hoc user role
1948
1949 select language, territory into langProfileValue, terrProfileValue
1950 from wf_roles
1951 where name = '__JTA_UM' ||itemkey;
1952
1953 -- Set the language and territory profile values for the pending user
1954 profileSave := FND_PROFILE.SAVE('ICX_LANGUAGE', langProfileValue, 'USER', requesterUserID);
1955 profileSave := FND_PROFILE.SAVE('ICX_TERRITORY', terrProfileValue, 'USER', requesterUserID);
1956
1957 -- end bug 7675285
1958
1959 -- Call AssignUTCredential ()
1960 JTF_UM_USERTYPE_CREDENTIALS.ASSIGN_USERTYPE_CREDENTIALS (
1961 X_USER_NAME => requesterUsername,
1962 X_USER_ID => requesterUserID,
1963 X_USERTYPE_ID => requestID);
1964
1965
1966 -- Save the approver comment to LAST_APPROVER_COMMENT in
1967 -- the JTF_UM_USERTYPE_REG table.
1968 update JTF_UM_USERTYPE_REG
1969 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1970 LAST_UPDATE_DATE = sysdate,
1971 LAST_APPROVER_COMMENT = approverComment
1972 where USERTYPE_REG_ID = itemkey;
1973
1974
1975
1976 for enrollNoAppRegRow in enrollmentNoApprovalCursor loop
1977 -- Call AssignEnrollCredential
1978
1979 JTF_UM_ENROLLMENT_CREDENTIALS.ASSIGN_ENROLLMENT_CREDENTIALS (
1980 X_USER_NAME => requesterUsername,
1981 X_USER_ID => requesterUserID,
1982 X_SUBSCRIPTION_ID => enrollNoAppRegRow.SUBSCRIPTION_ID);
1983
1984 end loop;
1985
1986
1987 for enrollAppRegRow in enrollmentApprovalCursor loop
1988 -- Launch workflow created during registration.
1989 if Not hasWorkFlowStarted(enrollAppRegRow.WF_ITEM_TYPE,to_char(enrollAppRegRow.SUBSCRIPTION_REG_ID)) then
1990 wf_engine.StartProcess (itemtype => enrollAppRegRow.WF_ITEM_TYPE,
1991 itemkey => to_char(enrollAppRegRow.SUBSCRIPTION_REG_ID));
1992 end if;
1993
1994 end loop;
1995
1996 else
1997 -- requestType is 'ENROLLMENT'
1998
1999 -- Save the approver comment to LAST_APPROVER_COMMENT in
2000 -- the JTF_UM_USERTYPE_REG table.
2001 update JTF_UM_SUBSCRIPTION_REG
2002 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2003 LAST_UPDATE_DATE = sysdate,
2004 LAST_APPROVER_COMMENT = approverComment
2005 where subscription_reg_id = itemkey;
2006
2007 -- Call Assign Enroll Credential
2008 JTF_UM_ENROLLMENT_CREDENTIALS.ASSIGN_ENROLLMENT_CREDENTIALS (
2009 X_USER_NAME => requesterUsername,
2010 X_USER_ID => requesterUserID,
2011 X_SUBSCRIPTION_ID => requestID);
2012
2013 end if;
2014
2015 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Do_Approve_Req API');
2016
2017 end Do_Approve_Req;
2018
2019 --
2020 -- Procedure
2021 -- Approve_Req
2022 --
2023 -- Description -
2024 -- Approve a request
2025 --
2026 -- IN
2027 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2028 -- itemkey - A string generated from the application object's primary key.
2029 -- actid - The function activity(instance id).
2030 -- funcmode - Run/Cancel/Timeout
2031 -- OUT
2032 -- resultout
2033 --
2034 procedure Approve_Req (itemtype in varchar2,
2035 itemkey in varchar2,
2036 actid in number,
2037 funcmode in varchar2,
2038 resultout out NOCOPY varchar2) is
2039 --
2040 begin
2041
2042 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Approve_Req (' ||
2043 itemtype || ',' || itemkey || ',' || actid || ',' ||
2044 funcmode || ') API');
2045
2046 --
2047 -- RUN mode - normal process execution
2048 --
2049 if (funcmode = 'RUN') then
2050 Do_Approve_Req (itemtype, itemkey);
2051
2052 --
2053 -- CANCEL mode
2054 --
2055 elsif (funcmode = 'CANCEL') then
2056 --
2057 -- Return process to run
2058 --
2059 resultout := 'COMPLETE:';
2060
2061 --
2062 -- TIMEOUT mode
2063 --
2064 elsif (funcmode = 'TIMEOUT') then
2065 resultout := 'COMPLETE:';
2066 end if;
2067
2068 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Approve_Req API');
2069
2070 exception
2071 when others then
2072 wf_core.context ('JTF_UM_WF_APPROVAL', 'Approve_Req', itemtype, itemkey, to_char(actid), funcmode);
2073 raise;
2074 end Approve_Req;
2075
2076 --
2077 -- Procedure
2078 -- Reject_Req
2079 --
2080 -- Description -
2081 -- Reject a request
2082 --
2083 -- IN
2084 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2085 -- itemkey - A string generated from the application object's primary key.
2086 -- actid - The function activity(instance id).
2087 -- funcmode - Run/Cancel
2088 -- OUT
2089 -- resultout
2090 --
2091 procedure Reject_Req (itemtype in varchar2,
2092 itemkey in varchar2,
2093 actid in number,
2094 funcmode in varchar2,
2095 resultout out NOCOPY varchar2) is
2096 --
2097 requestType varchar2 (10);
2098 requesterUserID number;
2099 approverComment varchar2 (4000);
2100
2101 l_parameter_list wf_parameter_list_t :=
2102 wf_parameter_list_t();
2103
2104 l_app_id number;
2105 l_usertype_reg_id number;
2106 l_usertype_key varchar2(30);
2107 requesterUsername varchar2 (100);
2108
2109 userStartDate date;
2110 userEndDate date;
2111 -- adding for Bug 4320347
2112 l_customer_id FND_USER.CUSTOMER_ID%TYPE;
2113 l_person_party_id FND_USER.PERSON_PARTY_ID%TYPE;
2114 -- end of changes for 4320347
2115 --
2116 cursor enrollmentsCursor is
2117 select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID
2118 from JTF_UM_SUBSCRIPTION_REG
2119 where USER_ID = requesterUserID
2120 and EFFECTIVE_START_DATE <= sysdate
2121 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
2122 and STATUS_CODE = 'PENDING';
2123
2124
2125 -- cursor for populating event parameters in case of user type rejection
2126 cursor getRejectEventData is
2127 Select ut.APPLICATION_ID,ut.USERTYPE_KEY,reg.USERTYPE_REG_ID
2128 From JTF_UM_USERTYPES_B ut , JTF_UM_USERTYPE_REG reg
2129 where ut.USERTYPE_ID=reg.USERTYPE_ID and reg.USERTYPE_REG_ID=to_number(itemkey);
2130 -- and reg.EFFECTIVE_START_DATE <= sysdate
2131 -- and reg.EFFECTIVE_END_DATE= sysdate;
2132
2133
2134
2135 --
2136 begin
2137
2138 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Reject_Req (' ||
2139 itemtype || ',' || itemkey || ',' || actid || ',' ||
2140 funcmode || ') API');
2141
2142 --
2143 -- RUN mode - normal process execution
2144 --
2145 if (funcmode = 'RUN') then
2146 requestType := wf_engine.GetItemAttrText (
2147 itemtype => itemtype,
2148 itemkey => itemkey,
2149 aname => 'REQUEST_TYPE');
2150
2151 requesterUserID := wf_engine.GetItemAttrNumber (
2152 itemtype => itemtype,
2153 itemkey => itemkey,
2154 aname => 'REQUESTER_USER_ID');
2155
2156 approverComment := wf_engine.GetItemAttrText (
2157 itemtype => itemtype,
2158 itemkey => itemkey,
2159 aname => 'APPROVER_COMMENT');
2160
2161 if (requestType = 'USERTYPE') then
2162
2163 -- Revoke pending resp.
2164 JTF_UM_USERTYPE_CREDENTIALS.REVOKE_RESPONSIBILITY (
2165 X_USER_ID => requesterUserID,
2166 X_RESPONSIBILITY_KEY => 'JTF_PENDING_APPROVAL',
2167 X_APPLICATION_ID => 690);
2168
2169 -- End date and rejected Usertype Reg
2170 update JTF_UM_USERTYPE_REG
2171 set STATUS_CODE = 'REJECTED',
2172 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2173 LAST_UPDATE_DATE = sysdate,
2174 LAST_APPROVER_COMMENT = approverComment,
2175 EFFECTIVE_END_DATE = sysdate
2176 where USERTYPE_REG_ID = itemkey;
2177
2178 for enrollRegRow in enrollmentsCursor
2179 loop
2180 -- Abort all Workflow Enrollment
2181 --if (enrollRegRow.WF_ITEM_TYPE is not null) then
2182 -- wf_engine.AbortProcess (itemtype => enrollRegRow.WF_ITEM_TYPE,
2183 -- itemkey => enrollRegRow.SUBSCRIPTION_REG_ID);
2184 --end if;
2185
2186 -- Set STATUS_CODE in JTF_UM_SUBSCRIPTION_REG to 'USER_REJECTED'
2187 update JTF_UM_SUBSCRIPTION_REG
2188 set STATUS_CODE = 'USER_REJECTED',
2189 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2190 LAST_UPDATE_DATE = sysdate,
2191 EFFECTIVE_END_DATE = sysdate
2192 where SUBSCRIPTION_REG_ID = enrollRegRow.SUBSCRIPTION_REG_ID;
2193 end loop;
2194
2195 -- release the user name
2196 -- check if user is a pending user
2197 Select start_date,end_date,USER_NAME,customer_id,person_party_id
2198 Into userStartDate,userEndDate,requesterUsername,l_customer_id,l_person_party_id
2199 From FND_USER
2200 Where user_id = requesterUserID;
2201
2202 If to_char(userStartDate) = to_char(FND_API.G_MISS_DATE)
2203 And to_char(userEndDate) = to_char(FND_API.G_MISS_DATE) then
2204 -- release user
2205 FND_USER_PKG.RemovePendingUser(requesterUsername);
2206 End If;
2207
2208 -- Event handling
2209 -- Get the values for creation of parameters for the event
2210
2211 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Start Raising Event');
2212
2213 open getRejectEventData;
2214
2215 fetch getRejectEventData into l_app_id,l_usertype_key,l_usertype_reg_id;
2216
2217 close getRejectEventData;
2218 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Parameters '|| l_app_id ||' '||l_usertype_key|| ' '||l_usertype_reg_id );
2219
2220 -- create the parameter list
2221 wf_event.AddParameterToList(
2222 p_name => 'USERTYPEREG_ID',
2223 p_value=>to_char(l_usertype_reg_id),
2224 p_parameterlist=>l_parameter_list
2225 );
2226 wf_event.AddParameterToList(
2227 p_name => 'APPID',
2228 p_value=>to_char(l_app_id),
2229 p_parameterlist=>l_parameter_list
2230 );
2231 wf_event.AddParameterToList(
2232 p_name => 'USER_TYPE_KEY',
2233 p_value=>l_usertype_key,
2234 p_parameterlist=>l_parameter_list
2235 );
2236 --changes for 4320347
2237 wf_event.AddParameterToList(
2238 p_name => 'CUSTOMER_ID',
2239 p_value=>to_char(nvl(l_customer_id,-1)) ,
2240 p_parameterlist=>l_parameter_list
2241 );
2242 wf_event.AddParameterToList(
2243 p_name => 'PERSON_PARTY_ID',
2244 p_value=>to_char(nvl(l_person_party_id,-1)),
2245 p_parameterlist=>l_parameter_list
2246 );
2247 --end of changes for 4320347
2248
2249
2250 -- raise the event
2251 wf_event.raise(
2252 p_event_name =>'oracle.apps.jtf.um.rejectUTEvent',
2253 p_event_key =>requesterUserID ,
2254 p_parameters => l_parameter_list
2255 );
2256
2257 -- delete parameter list as it is no longer required
2258 l_parameter_list.DELETE;
2259
2260 -- end of event handling
2261
2262
2263
2264 else
2265 -- Set STATUS_CODE in JTF_UM_SUBSCRIPTION_REG to 'REJECTED'
2266 update JTF_UM_SUBSCRIPTION_REG
2267 set STATUS_CODE = 'REJECTED',
2268 LAST_APPROVER_COMMENT = approverComment,
2269 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2270 LAST_UPDATE_DATE = sysdate,
2271 EFFECTIVE_END_DATE = sysdate
2272 where SUBSCRIPTION_REG_ID = itemkey;
2273 end if;
2274 resultout := 'COMPLETE:';
2275
2276 --
2277 -- CANCEL mode
2278 --
2279 elsif (funcmode = 'CANCEL') then
2280 --
2281 -- Return process to run
2282 --
2283 resultout := 'COMPLETE:';
2284
2285 --
2286 -- TIMEOUT mode
2287 --
2288 elsif (funcmode = 'TIMEOUT') then
2289 resultout := 'COMPLETE:';
2290 end if;
2291
2292 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Reject_Req API');
2293
2294 exception
2295 when others then
2296 wf_core.context ('JTF_UM_WF_APPROVAL', 'Reject_Req', itemtype, itemkey, to_char(actid), funcmode);
2297 raise;
2298 end Reject_Req;
2299
2300 --
2301 -- Can_Delegate
2302 -- DESCRIPTION
2303 -- Check the enrollment request has the delegation role.
2304 -- IN
2305 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2306 -- itemkey - A string generated from the application object's primary key.
2307 -- actid - The function activity(instance id).
2308 -- funcmode - Run/Cancel/Timeout
2309 -- OUT
2310 -- Resultout - 'COMPLETE:Y' enrollment has the delegation role.
2311 -- - 'COMPLETE:N' enrollment doesn't has the delegation role.
2312 --
2313 procedure Can_Delegate (itemtype in varchar2,
2314 itemkey in varchar2,
2315 actid in number,
2316 funcmode in varchar2,
2317 resultout out NOCOPY varchar2) is
2318 --
2319 l_approver_userID number;
2320 l_approver_username varchar (100);
2321 l_result varchar (10);
2322
2323 cursor getFNDUserID is
2324 select user_id
2325 from fnd_user
2326 where (nvl (end_date, sysdate + 1) > sysdate
2327 OR to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))
2328
2329 and user_name = l_approver_username;
2330 --
2331 begin
2332
2333 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Can_Delegate (' ||
2334 itemtype || ',' || itemkey || ',' || actid || ',' ||
2335 funcmode || ') API');
2336
2337 --
2338 -- RUN mode - normal process execution
2339 --
2340
2341 -- Default is No delegation
2342 resultout := 'COMPLETE:N';
2343
2344 if (funcmode = 'RUN') then
2345 -- Get the Approver User ID
2346 l_approver_username := wf_engine.GetItemAttrText (
2347 itemtype => itemtype,
2348 itemkey => itemkey,
2349 aname => 'APPROVER_USERNAME');
2350
2351 open getFNDUserID;
2352 fetch getFNDUserID into l_approver_userID;
2353 if (getFNDUserID%notfound) then
2354 close getFNDUserID;
2355 raise_application_error (-20000, 'userid not found('||l_approver_username||')');
2356 end if;
2357 close getFNDUserID;
2358
2359 -- Check if this enrollment has delegation
2360 JTF_UM_WF_DELEGATION_PVT.GET_CHECKBOX_STATUS (
2361 P_REG_ID => to_number (itemkey),
2362 P_USER_ID => l_approver_userID,
2363 X_RESULT => l_result);
2364
2365 if (l_result = JTF_UM_WF_DELEGATION_PVT.CHECKED_UPDATE) then
2366 -- Grant Delegation Flag is set to Yes.
2367 wf_engine.SetItemAttrText (itemtype => itemtype,
2368 itemkey => itemkey,
2369 aname => 'DELEGATION_FLAG',
2370 avalue => 'Y');
2371 resultout := 'COMPLETE:Y';
2372
2373 elsif (l_result = JTF_UM_WF_DELEGATION_PVT.NOT_CHECKED_UPDATE) then
2374 -- Grant Delegation Flag is set to No.
2375 wf_engine.SetItemAttrText (itemtype => itemtype,
2376 itemkey => itemkey,
2377 aname => 'DELEGATION_FLAG',
2378 avalue => 'N');
2379 resultout := 'COMPLETE:Y';
2380
2381 end if;
2382 end if;
2383
2384 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Can_Delegate API');
2385
2386 exception
2387 when others then
2388 wf_core.context ('JTF_UM_WF_APPROVAL', 'Can_Delegate', itemtype, itemkey, to_char (actid), funcmode, 'l_approver_userID='||to_char (l_approver_userID));
2389 raise;
2390 end Can_Delegate;
2391
2392 --
2393 -- CAN_ENROLLMENT_DELEGATE
2394 -- DESCRIPTION
2395 -- Check the enrollment request if it is delegation or
2396 -- delegation and self-service.
2397 -- IN
2398 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2399 -- itemkey - A string generated from the application object's primary key.
2400 -- actid - The function activity(instance id).
2401 -- funcmode - Run/Cancel/Timeout
2402 -- OUT
2403 -- Resultout - 'COMPLETE:Y' enrollment is a delegation or delegation
2404 -- and self-service.
2405 -- - 'COMPLETE:N' enrollment is a implicit or self-service.
2406 --
2407 procedure Can_Enrollment_Delegate (itemtype in varchar2,
2408 itemkey in varchar2,
2409 actid in number,
2410 funcmode in varchar2,
2411 resultout out NOCOPY varchar2) is
2412
2413 l_procedure_name CONSTANT varchar2(23) := 'can_enrollment_delegate';
2414 l_request_id number;
2415 l_requester_usertype_id number;
2416 l_result boolean;
2417
2418 begin
2419
2420 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (p_module => G_MODULE,
2421 p_message => l_procedure_name);
2422
2423 if l_is_debug_parameter_on then
2424 JTF_DEBUG_PUB.LOG_PARAMETERS (p_module => G_MODULE,
2425 p_message => itemtype || ',' || itemkey ||
2426 ',' || actid || ',' || funcmode || ') ');
2427 end if;
2428
2429 --
2430 -- RUN mode - normal process execution
2431 --
2432
2433 if (funcmode = 'RUN') then
2434 -- Get the Requester Usertype ID
2435 l_requester_usertype_id := wf_engine.GetItemAttrNumber (
2436 itemtype => itemtype,
2437 itemkey => itemkey,
2438 aname => 'REQUESTER_USERTYPE_ID');
2439
2440 -- Get the Request ID
2441 l_request_id := wf_engine.GetItemAttrNumber(
2442 itemtype => itemtype,
2443 itemkey => itemkey,
2444 aname => 'REQUEST_ID');
2445
2446 JTF_UM_WF_DELEGATION_PVT.CAN_ENROLLMENT_DELEGATE (
2447 p_subscription_id => l_request_id,
2448 p_usertype_id => l_requester_usertype_id,
2449 x_result => l_result);
2450
2451 if (l_result) then
2452 resultout := 'COMPLETE:Y';
2453 else
2454 resultout := 'COMPLETE:N';
2455 end if;
2456
2457 end if;
2458
2459 JTF_DEBUG_PUB.LOG_EXITING_METHOD (p_module => G_MODULE,
2460 p_message => l_procedure_name);
2461
2462 exception
2463 when others then
2464 wf_core.context ('JTF_UM_WF_APPROVAL', 'CAN_ENROLLMENT_DELEGATE', itemtype, itemkey, to_char (actid), funcmode);
2465 raise;
2466 end can_enrollment_delegate;
2467
2468 --
2469 -- UNIVERSAL_APPROVERS_EXISTS
2470 -- DESCRIPTION
2471 -- Check if the current approver is universal approvers role.
2472 -- IN
2473 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2474 -- itemkey - A string generated from the application object's primary key.
2475 -- actid - The function activity(instance id).
2476 -- funcmode - Run/Cancel/Timeout
2477 -- OUT
2478 -- Resultout - 'COMPLETE:Y' current approver is universal approvers role.
2479 -- - 'COMPLETE:N' current approver is not universal approvers
2480 -- role.
2481 --
2482 procedure universal_approvers_exists (itemtype in varchar2,
2483 itemkey in varchar2,
2484 actid in number,
2485 funcmode in varchar2,
2486 resultout out NOCOPY varchar2) is
2487
2488 l_appl_id JTF_UM_USERTYPES_B.APPLICATION_ID%TYPE;
2489 l_approver_username fnd_user.user_name%type;
2490 l_org_name hz_parties.party_name%type;
2491 l_org_number hz_parties.party_number%type;
2492 l_primary_user_role fnd_profile_option_values.profile_option_value%type;
2493 l_procedure_name CONSTANT varchar2(26) := 'universal_approvers_exists';
2494 l_request_id number;
2495 l_request_type varchar2 (10);
2496 l_requester_user_id fnd_user.user_id%type;
2497 l_universal_approvers fnd_profile_option_values.profile_option_value%type;
2498
2499 begin
2500
2501 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (p_module => G_MODULE,
2502 p_message => l_procedure_name);
2503
2504 if l_is_debug_parameter_on then
2505 JTF_DEBUG_PUB.LOG_PARAMETERS (p_module => G_MODULE,
2506 p_message => itemtype || ',' || itemkey ||
2507 ',' || actid || ',' || funcmode || ') ');
2508 end if;
2509
2510 --
2511 -- RUN mode - normal process execution
2512 --
2513
2514 if (funcmode = 'RUN') then
2515 -- Get the Current Approver Username
2516 l_approver_username := wf_engine.GetItemAttrText (
2517 itemtype => itemtype,
2518 itemkey => itemkey,
2519 aname => 'APPROVER_USERNAME');
2520
2521 -- Get the profile option of Universal Approvers
2522 l_request_type := wf_engine.GetItemAttrText (
2523 itemtype => itemtype,
2524 itemkey => itemkey,
2525 aname => 'REQUEST_TYPE');
2526
2527 l_request_id := wf_engine.GetItemAttrNumber (
2528 itemtype => itemtype,
2529 itemkey => itemkey,
2530 aname => 'REQUEST_ID');
2531
2532 l_requester_user_id := wf_engine.GetItemAttrNumber (
2533 itemtype => itemtype,
2534 itemkey => itemkey,
2535 aname => 'REQUESTER_USER_ID');
2536
2537 -- get the organization number
2538 get_org_info (p_user_id => l_requester_user_id,
2539 x_org_name => l_org_name,
2540 x_org_number => l_org_number);
2541
2542 -- the name of the role
2543 l_primary_user_role := g_adhoc_role_name_prefix || l_org_number;
2544
2545 if (l_approver_username = l_primary_user_role) then
2546 -- the current approver is a Universal Approver
2547 resultout := 'COMPLETE:Y';
2548 else
2549 resultout := 'COMPLETE:N';
2550 end if;
2551
2552 end if;
2553
2554 JTF_DEBUG_PUB.LOG_EXITING_METHOD (p_module => G_MODULE,
2555 p_message => l_procedure_name);
2556
2557 exception
2558 when others then
2559 wf_core.context ('JTF_UM_WF_APPROVAL', l_procedure_name, itemtype, itemkey, to_char (actid), funcmode);
2560 raise;
2561 end universal_approvers_exists;
2562
2563 --
2564 -- CHECK_EMAIL_NOTIFI_TYPE
2565 -- DESCRIPTION
2566 -- Check which email we will send to this requester.
2567 -- IN
2568 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2569 -- itemkey - A string generated from the application object's primary key.
2570 -- actid - The function activity(instance id).
2571 -- funcmode - Run/Cancel/Timeout
2572 -- OUT
2573 -- Resultout - 'COMPLETE:NO_NOTIFICATION' if email should not be sent.
2574 -- - 'COMPLETE:PRIMARY_USER' if primary user email should be sent.
2575 -- - 'COMPLETE:BUSINESS_USER' if business user email should be sent.
2576 -- - 'COMPLETE:INDIVIDUAL_USER' if individual user email should be sent.
2577 -- - 'COMPLETE:OTHER_USER' if other user email should be sent.
2578 -- - 'COMPLETE:ENROLLMENT' if enrollment email should be sent.
2579 --
2580 procedure CHECK_EMAIL_NOTIFI_TYPE (itemtype in varchar2,
2581 itemkey in varchar2,
2582 actid in number,
2583 funcmode in varchar2,
2584 resultout out NOCOPY varchar2) is
2585 --
2586 requestType varchar2 (10);
2587 requestName varchar2 (1000);
2588 usertypeKey varchar2 (30);
2589 emailFlag varchar2 (1);
2590 usertypeID number;
2591 MISSING_USERTYPE_INFO exception;
2592 --
2593 cursor getUsertypeInfo is
2594 select USERTYPE_KEY, EMAIL_NOTIFICATION_FLAG
2595 from JTF_UM_USERTYPES_B
2596 where USERTYPE_ID = usertypeID;
2597
2598 begin
2599
2600 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering CHECK_EMAIL_NOTIFI_TYPE (' ||
2601 itemtype || ',' || itemkey || ',' || actid || ',' ||
2602 funcmode || ') API');
2603
2604 --
2605 -- RUN mode - normal process execution
2606 --
2607
2608 if (funcmode = 'RUN') then
2609 usertypeID := wf_engine.GetItemAttrNumber (
2610 itemtype => itemtype,
2611 itemkey => itemkey,
2612 aname => 'REQUESTER_USERTYPE_ID');
2613
2614 open getUsertypeInfo;
2615 fetch getUsertypeInfo into usertypeKey, emailFlag;
2616 if (getUsertypeInfo%notfound) then
2617 close getUsertypeInfo;
2618 raise MISSING_USERTYPE_INFO;
2619 end if;
2620 close getUsertypeInfo;
2621
2622 if (emailFlag = 'N') then
2623 resultout := 'COMPLETE:NO_NOTIFICATION';
2624
2625 else
2626 -- We will send email out.
2627
2628 requestType := wf_engine.GetItemAttrText (
2629 itemtype => itemtype,
2630 itemkey => itemkey,
2631 aname => 'REQUEST_TYPE');
2632
2633 if (requestType = 'ENROLLMENT') then
2634 -- Send enrollment email
2635 resultout := 'COMPLETE:ENROLLMENT';
2636 else
2637 -- Send usertype email, but we need to know what kind
2638 -- of usertype email are we sending.
2639
2640 if (usertypeKey = 'PRIMARYUSER') or (usertypeKey = 'PRIMARYUSERNEW') then
2641 resultout := 'COMPLETE:PRIMARY_USER';
2642 elsif (usertypeKey = 'BUSINESSUSER') then
2643 resultout := 'COMPLETE:BUSINESS_USER';
2644 elsif (usertypeKey = 'INDIVIDUALUSER') then
2645 resultout := 'COMPLETE:INDIVIDUAL_USER';
2646 else
2647 resultout := 'COMPLETE:OTHER_USER';
2648 end if;
2649 end if;
2650 end if;
2651
2652 --
2653 -- CANCEL mode
2654 --
2655 elsif (funcmode = 'CANCEL') then
2656 --
2657 resultout := 'COMPLETE:';
2658 --
2659
2660 --
2661 -- TIMEOUT mode
2662 --
2663 elsif (funcmode = 'TIMEOUT') then
2664 resultout := 'COMPLETE:';
2665 end if;
2666
2667 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting CHECK_EMAIL_NOTIFI_TYPE API');
2668
2669 exception
2670 when MISSING_USERTYPE_INFO then
2671 wf_core.context ('JTF_UM_WF_APPROVAL', 'CHECK_EMAIL_NOTIFI_TYPE', itemtype, itemkey, to_char (actid), funcmode, 'Usertype info is missing');
2672 raise;
2673 when others then
2674 wf_core.context ('JTF_UM_WF_APPROVAL', 'CHECK_EMAIL_NOTIFI_TYPE', itemtype, itemkey, to_char (actid), funcmode);
2675 raise;
2676 end CHECK_EMAIL_NOTIFI_TYPE;
2677
2678 --
2679 -- CompleteApprovalActivity
2680 -- DESCRIPTION
2681 -- Complete the blocking activity
2682 -- This procedure will determine which request type this approval is for
2683 -- IN
2684 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2685 -- itemkey - A string generated from the application object's primary key.
2686 -- resultCode - 'APPROVED' or 'REJECTED'
2687 -- comment - Approver's comment
2688 -- delegationFlag - 'Y' = Grant Delegation Flag
2689 -- 'N' = Do not grant delegation flag
2690 -- null = No delegation flag
2691 -- lastUpdateDate - Last Update Date of the request record
2692 --
2693 procedure CompleteApprovalActivity (itemtype in varchar2,
2694 itemkey in varchar2,
2695 resultCode in varchar2,
2696 approverComment in varchar2,
2697 delegationFlag in varchar2 := null,
2698 lastUpdateDate in varchar2 := null) is
2699
2700 l_last_update_date varchar2 (14);
2701 request_type varchar2 (10);
2702 org_status varchar2(1);
2703
2704
2705
2706 UNKNOWN_REQUEST_TYPE exception;
2707
2708 cursor getLUDFromUserReg is
2709 select to_char (last_update_date, 'mmddyyyyhh24miss')
2710 from jtf_um_usertype_reg
2711 where usertype_reg_id = to_number (itemkey);
2712
2713 cursor getLUDFromEnrollReg is
2714 select to_char (last_update_date, 'mmddyyyyhh24miss')
2715 from jtf_um_subscription_reg
2716 where subscription_reg_id = to_number (itemkey);
2717
2718 -- For bug fix 3894853
2719 cursor getOrgDetail is
2720 SELECT party.status FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
2721 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
2722 AND PREL.PARTY_ID = (select fnd.customer_id
2723 from jtf_um_usertype_reg reg , fnd_user fnd
2724 where usertype_reg_id = to_number(itemkey)
2725 and reg.user_id=fnd.USER_ID
2726 )
2727 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2728 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2729 AND PREL.START_DATE < SYSDATE
2730 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
2731 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');
2732
2733
2734 -- end Bug fix 3894853
2735
2736
2737 begin
2738
2739 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering CompleteApprovalActivity (' ||
2740 itemtype || ',' || itemkey || ',' || resultCode || ',' ||
2741 approverComment || ') API');
2742
2743 -- check on the input
2744 if itemtype is null then
2745 raise_application_error (-20000, 'itemtype is null while calling JTF_UM_WF_APPROVAL.CompleteApprovalActivity.');
2746 end if;
2747
2748 if itemkey is null then
2749 raise_application_error (-20000, 'itemkey is null while calling JTF_UM_WF_APPROVAL.CompleteApprovalActivity.');
2750 end if;
2751
2752 if resultCode is null then
2753 raise_application_error (-20000, 'resultCode is null while calling JTF_UM_WF_APPROVAL.CompleteApprovalActivity.');
2754 end if;
2755
2756 -- Check Request type
2757 request_type := wf_engine.GetItemAttrText (
2758 itemtype => itemtype,
2759 itemkey => itemkey,
2760 aname => 'REQUEST_TYPE');
2761
2762 if (request_type = 'USERTYPE') then
2763
2764 -- bug fix 3894853
2765 -- check if there is an Organization associated with this usertype we are trying to approve
2766 -- also if that Organization Status is INACTIVE then raise an error
2767
2768 open getOrgDetail;
2769 fetch getOrgDetail into org_status;
2770 close getOrgDetail;
2771
2772 if org_status is not null and org_status <> 'A' then
2773 raise_application_error (-20001, ' ORG_INACTIVE ' );
2774 end if;
2775
2776
2777 -- end of bug fix 3894853
2778
2779 if lastUpdateDate is not null then
2780
2781 open getLUDFromUserReg;
2782 fetch getLUDFromUserReg into l_last_update_date;
2783 close getLUDFromUserReg;
2784
2785 if (lastUpdateDate <> l_last_update_date) then
2786 -- not the same request
2787 raise_application_error (-20001, 'The last update date from the input parameter and the last update date stored in the database is different.');
2788 end if;
2789 end if;
2790
2791 Do_Complete_Approval_Activity (p_itemtype => itemtype,
2792 p_itemkey => itemkey,
2793 p_resultCode => resultCode,
2794 p_approverComment => approverComment,
2795 p_act1 => 'NTF_BLOCK',
2796 p_act2 => 'REMINDER_NTF_BLOCK',
2797 p_act3 => 'FAIL_ESCLATE_NTF_BLOC');
2798
2799 elsif (request_type = 'ENROLLMENT') then
2800
2801 if lastUpdateDate is not null then
2802
2803 open getLUDFromEnrollReg;
2804 fetch getLUDFromEnrollReg into l_last_update_date;
2805 close getLUDFromEnrollReg;
2806
2807 if (lastUpdateDate <> l_last_update_date) then
2808 -- not the same request
2809 raise_application_error (-20001, 'The last update date from the input parameter and the last update date stored in the database is different.');
2810 end if;
2811 end if;
2812
2813 -- Set the delegation flag
2814 if (delegationFlag is not null) then
2815 wf_engine.SetItemAttrText (itemtype => itemtype,
2816 itemkey => itemkey,
2817 aname => 'DELEGATION_FLAG',
2818 avalue => delegationFlag);
2819 end if;
2820
2821 Do_Complete_Approval_Activity (p_itemtype => itemtype,
2822 p_itemkey => itemkey,
2823 p_resultCode => resultCode,
2824 p_approverComment => approverComment,
2825 p_act1 => 'NTF_APPROVAL_ENROLL_REQUIRED',
2826 p_act2 => 'NTF_REMIND_ENROLL_REQUIRED',
2827 p_act3 => 'NTF_FAIL_ESCALATE_ENROLL_REQ',
2828 p_act4 => 'NTF_APPROVAL_ENROLL_DELE_REQ',
2829 p_act5 => 'NTF_REMIND_ENROLL_DELE_REQ',
2830 p_act6 => 'NTF_FAIL_ESCA_ENROLL_DELE_REQ',
2831 p_act7 => 'NTF_APPROV_ENROL_DELE_DISP_REQ',
2832 p_act8 => 'NTF_REMIND_ENROL_DELE_DISP_REQ');
2833
2834
2835
2836 else
2837 raise UNKNOWN_REQUEST_TYPE;
2838 end if;
2839
2840 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting CompleteApprovalActivity API');
2841
2842 exception
2843 when UNKNOWN_REQUEST_TYPE then
2844 wf_core.context ('JTF_UM_WF_APPROVAL', 'CompleteApprovalActivity',
2845 itemtype, itemkey, resultCode, approverComment);
2846 raise;
2847 when others then
2848 wf_core.context ('JTF_UM_WF_APPROVAL', 'CompleteApprovalActivity', itemtype, itemkey, resultCode, approverComment);
2849 raise;
2850
2851 end CompleteApprovalActivity;
2852
2853 --
2854 -- Do_Complete_Approval_Activity
2855 -- DESCRIPTION
2856 -- Complete the blocking activity now
2857 -- IN
2858 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
2859 -- p_itemkey - A string generated from the application object's
2860 -- primary key.
2861 -- p_resultCode - 'APPROVED' or 'REJECTED'
2862 -- p_wf_resultCode - 'APPROVED' or 'REJECTED' but if approval is Usertype,
2863 -- this will be 'null'.
2864 -- p_approverComment - Approver's comment
2865 -- p_act1 - First Activity
2866 -- p_act2 - Second Activity
2867 -- p_act3 - Third Activity
2868 -- p_act4 - Fourth Activity
2869 -- p_act5 - Fifth Activity
2870 -- p_act6 - Sixth Activity
2871 --
2872 procedure Do_Complete_Approval_Activity (p_itemtype in varchar2,
2873 p_itemkey in varchar2,
2874 p_resultCode in varchar2,
2875 p_wf_resultCode in varchar2,
2876 p_approverComment in varchar2,
2877 p_act1 in varchar2 := null,
2878 p_act2 in varchar2 := null,
2879 p_act3 in varchar2 := null,
2880 p_act4 in varchar2 := null,
2881 p_act5 in varchar2 := null,
2882 p_act6 in varchar2 := null)
2883
2884 is
2885
2886 begin
2887
2888 Do_Complete_Approval_Activity (p_itemtype => p_itemtype,
2889 p_itemkey => p_itemkey,
2890 p_resultCode => p_resultCode,
2891 p_approverComment => p_approverComment,
2892 p_act1 => p_act1,
2893 p_act2 => p_act2,
2894 p_act3 => p_act3,
2895 p_act4 => p_act4,
2896 p_act5 => p_act5,
2897 p_act6 => p_act6);
2898
2899 end Do_Complete_Approval_Activity;
2900
2901 --
2902 -- Do_Complete_Approval_Activity
2903 -- DESCRIPTION
2904 -- Complete the blocking activity now
2905 -- IN
2906 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
2907 -- p_itemkey - A string generated from the application object's
2908 -- primary key.
2909 -- p_resultCode - 'APPROVED' or 'REJECTED'
2910 -- p_approverComment - Approver's comment
2911 -- p_act1 - First Activity
2912 -- p_act2 - Second Activity
2913 -- p_act3 - Third Activity
2914 -- p_act4 - Fourth Activity
2915 -- p_act5 - Fifth Activity
2916 -- p_act6 - Sixth Activity
2917 --
2918 procedure Do_Complete_Approval_Activity (p_itemtype in varchar2,
2919 p_itemkey in varchar2,
2920 p_resultCode in varchar2,
2921 p_approverComment in varchar2,
2922 p_act1 in varchar2 := null,
2923 p_act2 in varchar2 := null,
2924 p_act3 in varchar2 := null,
2925 p_act4 in varchar2 := null,
2926 p_act5 in varchar2 := null,
2927 p_act6 in varchar2 := null,
2928 p_act7 in varchar2 := null,
2929 p_act8 in varchar2 := null)
2930
2931 IS
2932
2933 OK boolean := FALSE;
2934 act varchar2 (30);
2935 requestType varchar2 (10);
2936 wf_resultCode varchar2 (8) := p_resultCode;
2937
2938 begin
2939
2940 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
2941 'Entering Do_Complete_Approval_Activity (' || p_itemtype ||
2942 ',' || p_itemkey || ',' || p_resultCode || ',' || p_approverComment ||
2943 ',' || p_act1 || ',' || p_act2 || ',' || p_act3 || ',' || p_act4 ||
2944 ',' || p_act5 || ',' || p_act6 || ',' || p_act7 || ',' || p_act8 || ') API');
2945
2946 wf_engine.SetItemAttrText (itemtype => p_itemtype,
2947 itemkey => p_itemkey,
2948 aname => 'APPROVER_COMMENT',
2949 avalue => p_approverComment);
2950
2951 wf_engine.SetItemAttrText (itemtype => p_itemtype,
2952 itemkey => p_itemkey,
2953 aname => 'REQUEST_RESULT',
2954 avalue => p_resultCode);
2955
2956 -- Find out what kind of request type is it
2957 requestType := wf_engine.GetItemAttrText (itemtype => p_itemtype,
2958 itemkey => p_itemkey,
2959 aname => 'REQUEST_TYPE');
2960
2961 if (p_act1 is not null) then
2962 begin
2963 act := p_act1;
2964 wf_engine.BeginActivity (itemtype => p_itemtype,
2965 itemkey => p_itemkey,
2966 activity => act);
2967 OK := TRUE;
2968 exception
2969 when others then
2970 wf_core.clear;
2971 end;
2972 end if;
2973
2974 if (not OK) and (p_act2 is not null) then
2975 begin
2976 act := p_act2;
2977 wf_engine.BeginActivity (itemtype => p_itemtype,
2978 itemkey => p_itemkey,
2979 activity => act);
2980 OK := TRUE;
2981 exception
2982 when others then
2983 wf_core.clear;
2984 end;
2985 end if;
2986
2987 if (not OK) and (p_act3 is not null) then
2988 begin
2989 act := p_act3;
2990 wf_engine.BeginActivity (itemtype => p_itemtype,
2991 itemkey => p_itemkey,
2992 activity => act);
2993 OK := TRUE;
2994 exception
2995 when others then
2996 wf_core.clear;
2997 end;
2998 end if;
2999
3000 if (not OK) and (p_act4 is not null) then
3001 begin
3002 act := p_act4;
3003 wf_engine.BeginActivity (itemtype => p_itemtype,
3004 itemkey => p_itemkey,
3005 activity => act);
3006 OK := TRUE;
3007 exception
3008 when others then
3009 wf_core.clear;
3010 end;
3011 end if;
3012
3013 if (not OK) and (p_act5 is not null) then
3014 begin
3015 act := p_act5;
3016 wf_engine.BeginActivity (itemtype => p_itemtype,
3017 itemkey => p_itemkey,
3018 activity => act);
3019 OK := TRUE;
3020 exception
3021 when others then
3022 wf_core.clear;
3023 end;
3024 end if;
3025
3026 if (not OK) and (p_act6 is not null) then
3027 begin
3028 act := p_act6;
3029 wf_engine.BeginActivity (itemtype => p_itemtype,
3030 itemkey => p_itemkey,
3031 activity => act);
3032 OK := TRUE;
3033 exception
3034 when others then
3035 wf_core.clear;
3036 end;
3037 end if;
3038
3039 if (not OK) and (p_act7 is not null) then
3040 begin
3041 act := p_act7;
3042 wf_engine.BeginActivity (itemtype => p_itemtype,
3043 itemkey => p_itemkey,
3044 activity => act);
3045 OK := TRUE;
3046 exception
3047 when others then
3048 wf_core.clear;
3049 end;
3050 end if;
3051
3052
3053 if (not OK) and (p_act8 is not null) then
3054 begin
3055 act := p_act8;
3056 wf_engine.BeginActivity (itemtype => p_itemtype,
3057 itemkey => p_itemkey,
3058 activity => act);
3059 OK := TRUE;
3060 exception
3061 when others then
3062 wf_core.clear;
3063 end;
3064 end if;
3065
3066
3067 if OK then
3068
3069 wf_engine.CompleteActivity (p_itemtype, p_itemkey, act, wf_resultCode);
3070
3071 else
3072
3073 raise_application_error (-20000, 'No Activity Found Failed at JTF_UM_WF_APPROVAL.Do_Complete_Approval_Activity ('
3074 ||p_itemtype||','||p_itemkey||','||p_resultCode||','||p_approverComment
3075 ||','||p_act1||','||p_act2||','||p_act3||','||p_act4||','||p_act5||','||
3076 p_act6||','||p_act7||','||p_act8||')');
3077
3078 end if;
3079
3080 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Do_Complete_Approval_Activity API');
3081
3082 end Do_Complete_Approval_Activity;
3083
3084 --
3085 -- abort_process
3086 -- DESCRIPTION
3087 -- Abort the Workflow Process with status is ACTIVE, ERROR, or SUSPENDED
3088 -- IN
3089 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
3090 -- p_itemkey - A string generated from the application object's
3091 -- primary key.
3092 --
3093 procedure abort_process (p_itemtype in varchar2,
3094 p_itemkey in varchar2)
3095
3096 IS
3097
3098 result varchar2 (10);
3099 status varchar2 (10);
3100 begin
3101
3102 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering abort_process (' ||
3103 p_itemtype || ',' || p_itemkey || ') API');
3104
3105 if p_itemtype is null then
3106 raise_application_error (-20000, 'itemtype is null while calling JTF_UM_WF_APPROVAL.abort_process.');
3107 end if;
3108
3109 if p_itemtype is null then
3110 raise_application_error (-20000, 'itemkey is null while calling JTF_UM_WF_APPROVAL.abort_process.');
3111 end if;
3112
3113 wf_engine.ItemStatus (p_itemtype, p_itemkey, status, result);
3114 if (status <> 'COMPLETE') then
3115 -- need to cancel any open notification
3116 cancel_notification (p_itemtype, p_itemkey);
3117 -- now call the workflow abort process
3118 wf_engine.abortprocess (itemtype => p_itemtype,
3119 itemkey => p_itemkey);
3120 end if;
3121
3122 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting abort_process API');
3123
3124 end abort_process;
3125
3126 procedure usertype_approval_changed (p_usertype_id in number,
3127 p_new_approval_id in number,
3128 p_old_approval_id in number) is
3129
3130 cursor usertype_reg is
3131 select usertype_reg_id,user_id
3132 from jtf_um_usertype_reg
3133 where usertype_id = p_usertype_id
3134 and status_code = 'PENDING'
3135 and nvl (effective_end_date, sysdate + 1) > sysdate;
3136
3137 p_usertype_reg_id number;
3138 p_user_id number;
3139
3140 cursor find_old_item_type is
3141 select utreg.wf_item_type
3142 from jtf_um_usertype_reg utreg, jtf_um_usertypes_b ut
3143 where utreg.usertype_id = p_usertype_id
3144 and utreg.usertype_id = ut.usertype_id
3145 and utreg.status_code = 'PENDING'
3146 and nvl (utreg.effective_end_date, sysdate + 1) > sysdate;
3147
3148 p_wf_old_item_type varchar2(8);
3149
3150 cursor find_new_item_type is
3151 select wf_item_type
3152 from jtf_um_approvals_b
3153 where approval_id = p_new_approval_id;
3154
3155 p_wf_new_item_type varchar2(8);
3156 p_new_item_key number;
3157
3158 begin
3159
3160 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3161 'Entering usertype_approval_changed (' || p_usertype_id || ',' ||
3162 p_new_approval_id || ',' || p_old_approval_id || ') API');
3163
3164
3165 -- Find out the old item_type to abort Workflow process
3166 open find_old_item_type;
3167 fetch find_old_item_type into p_wf_old_item_type;
3168 close find_old_item_type;
3169
3170
3171 -- Find out the new item_type to create Workflow process
3172 open find_new_item_type;
3173 fetch find_new_item_type into p_wf_new_item_type;
3174 close find_new_item_type;
3175
3176
3177
3178
3179 open usertype_reg;
3180 loop
3181 fetch usertype_reg into p_usertype_reg_id,p_user_id;
3182 exit when usertype_reg%NOTFOUND;
3183
3184
3185 -- abort WF Process first
3186 abort_process (p_wf_old_item_type, p_usertype_reg_id);
3187
3188
3189 if p_wf_new_item_type is null then
3190
3191
3192 -- approve the approval request
3193 do_approve_req(itemtype => p_wf_old_item_type,
3194 itemkey => p_usertype_reg_id);
3195
3196 else
3197
3198
3199 -- end date the old record in JTF_UM_USERTYPE_REG table
3200 update JTF_UM_USERTYPE_REG set effective_end_date = sysdate,
3201 last_update_date = sysdate, last_updated_by = FND_GLOBAL.USER_ID
3202 where usertype_reg_id = p_usertype_reg_id;
3203
3204
3205 -- create record in JTF_UM_USERTYPE_REG table
3206 JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW (
3207 X_USERTYPE_ID => p_usertype_id,
3208 X_LAST_APPROVER_COMMENT => null,
3209 X_APPROVER_USER_ID => null,
3210 X_EFFECTIVE_END_DATE => null,
3211 X_WF_ITEM_TYPE => p_wf_new_item_type,
3212 X_EFFECTIVE_START_DATE => sysdate,
3213 X_USERTYPE_REG_ID => p_new_item_key,
3214 X_USER_ID => p_user_id,
3215 X_STATUS_CODE => 'PENDING',
3216 X_CREATION_DATE => sysdate,
3217 X_CREATED_BY => FND_GLOBAL.USER_ID,
3218 X_LAST_UPDATE_DATE => sysdate,
3219 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3220 X_LAST_UPDATE_LOGIN => null);
3221
3222 -- Create WF process
3223 CreateProcess (ownerUserId => FND_GLOBAL.USER_ID,
3224 requestType => 'USERTYPE',
3225 requestID => p_usertype_id,
3226 requesterUserID => p_user_id,
3227 requestRegID => p_new_item_key);
3228
3229 -- Launch WF process
3230 LaunchProcess (requestType => 'USERTYPE',
3231 requestRegID => p_new_item_key);
3232
3233 end if;
3234
3235 end loop;
3236 close usertype_reg;
3237
3238 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting usertype_approval_changed API');
3239
3240 end usertype_approval_changed;
3241
3242 procedure usertype_approval_changed (p_usertype_id in number,
3243 p_new_approval_id in number,
3244 p_old_approval_id in number,
3245 p_org_party_id in number) is
3246
3247 cursor usertype_reg is
3248 select utreg.usertype_reg_id, utreg.user_id
3249 from jtf_um_usertype_reg utreg, fnd_user fu, hz_relationships hzr
3250 where utreg.usertype_id = p_usertype_id
3251 and utreg.status_code = 'PENDING'
3252 and nvl (utreg.effective_end_date, sysdate + 1) > sysdate
3253 and utreg.user_id = fu.user_id
3254 and fu.customer_id = hzr.party_id
3255 and hzr.start_date <= sysdate
3256 and nvl (hzr.end_date, sysdate + 1) > sysdate
3257 and hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
3258 and hzr.object_table_name = 'HZ_PARTIES'
3259 and hzr.subject_table_name = 'HZ_PARTIES'
3260 and hzr.object_id = p_org_party_id;
3261
3262 p_usertype_reg_id number;
3263 p_user_id number;
3264
3265 cursor find_old_item_type is
3266 select utreg.wf_item_type
3267 from jtf_um_usertype_reg utreg, jtf_um_usertypes_b ut
3268 where utreg.usertype_id = p_usertype_id
3269 and utreg.usertype_id = ut.usertype_id
3270 and utreg.status_code = 'PENDING'
3271 and nvl (utreg.effective_end_date, sysdate + 1) > sysdate;
3272
3273 p_wf_old_item_type varchar2(8);
3274
3275 cursor find_new_item_type is
3276 select wf_item_type
3277 from jtf_um_approvals_b
3278 where approval_id = p_new_approval_id;
3279
3280 p_wf_new_item_type varchar2(8);
3281 p_new_item_key number;
3282
3283 begin
3284
3285 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3286 'Entering usertype_approval_changed (' || p_usertype_id || ',' ||
3287 p_new_approval_id || ',' || p_old_approval_id || ',' ||
3288 p_org_party_id || ') API');
3289
3290 -- Find out the old item_type to abort Workflow process
3291 open find_old_item_type;
3292 fetch find_old_item_type into p_wf_old_item_type;
3293 close find_old_item_type;
3294
3295 -- Find out the new item_type to create Workflow process
3296 open find_new_item_type;
3297 fetch find_new_item_type into p_wf_new_item_type;
3298 close find_new_item_type;
3299
3300 open usertype_reg;
3301 loop
3302 fetch usertype_reg into p_usertype_reg_id,p_user_id;
3303 exit when usertype_reg%NOTFOUND;
3304
3305 -- abort WF Process first
3306 abort_process (p_wf_old_item_type, p_usertype_reg_id);
3307
3308 if p_wf_new_item_type is null then
3309
3310 do_approve_req(itemtype => p_wf_old_item_type,
3311 itemkey => p_usertype_reg_id);
3312 else
3313
3314 -- end date the old record in JTF_UM_USERTYPE_REG table
3315 update JTF_UM_USERTYPE_REG set effective_end_date = sysdate,
3316 last_update_date = sysdate, last_updated_by = FND_GLOBAL.USER_ID
3317 where usertype_reg_id = p_usertype_reg_id
3318 and user_id = p_user_id and status_code='PENDING';
3319
3320 -- create record in JTF_UM_USERTYPE_REG table
3321 JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW (
3322 X_USERTYPE_ID => p_usertype_id,
3323 X_LAST_APPROVER_COMMENT => null,
3324 X_APPROVER_USER_ID => null,
3325 X_EFFECTIVE_END_DATE => null,
3326 X_WF_ITEM_TYPE => p_wf_new_item_type,
3327 X_EFFECTIVE_START_DATE => sysdate,
3328 X_USERTYPE_REG_ID => p_new_item_key,
3329 X_USER_ID => p_user_id,
3330 X_STATUS_CODE => 'PENDING',
3331 X_CREATION_DATE => sysdate,
3332 X_CREATED_BY => FND_GLOBAL.USER_ID,
3333 X_LAST_UPDATE_DATE => sysdate,
3334 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3335 X_LAST_UPDATE_LOGIN => null);
3336
3337 -- Create WF process
3338 CreateProcess (ownerUserId => FND_GLOBAL.USER_ID,
3339 requestType => 'USERTYPE',
3340 requestID => p_usertype_id,
3341 requesterUserID => p_user_id,
3342 requestRegID => p_new_item_key);
3343
3344 -- Launch WF process
3345 LaunchProcess (requestType => 'USERTYPE',
3346 requestRegID => p_new_item_key);
3347
3348 end if;
3349 end loop;
3350 close usertype_reg;
3351
3352 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting usertype_approval_changed API');
3353
3354 end usertype_approval_changed;
3355
3356 procedure enrollment_approval_changed (p_subscription_id in number,
3357 p_new_approval_id in number,
3358 p_old_approval_id in number,
3359 p_org_party_id in number default null) is
3360
3361 p_user_id number;
3362 p_subscription_reg_id number;
3363 p_new_item_key number;
3364 p_usertype_status varchar2(30);
3365 p_wf_new_item_type varchar2(8);
3366 p_wf_old_item_type varchar2(8);
3367
3368 cursor subscription_reg is
3369 select subscription_reg_id, user_id, wf_item_type
3370 from jtf_um_subscription_reg
3371 where subscription_id = p_subscription_id
3372 and status_code = 'PENDING'
3373 and (effective_end_date is null
3374 or effective_end_date > sysdate);
3375
3376 cursor subscription_reg_w_org is
3377 select subreg.subscription_reg_id, fu.user_id, subreg.wf_item_type
3378 from jtf_um_subscription_reg subreg, fnd_user fu, hz_relationships hzr
3379 where subreg.subscription_id = p_subscription_id
3380 and subreg.status_code = 'PENDING'
3381 and nvl (subreg.effective_end_date, sysdate + 1) > sysdate
3382 and subreg.user_id = fu.user_id
3383 and fu.customer_id = hzr.party_id
3384 and hzr.start_date <= sysdate
3385 and nvl (hzr.end_date, sysdate + 1) > sysdate
3386 and hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
3387 and hzr.object_table_name = 'HZ_PARTIES'
3388 and hzr.subject_table_name = 'HZ_PARTIES'
3389 and hzr.object_id = p_org_party_id;
3390
3391 cursor find_new_item_type is
3392 select wf_item_type
3393 from jtf_um_approvals_b
3394 where approval_id = p_new_approval_id
3395 and (effective_end_date is null
3396 or effective_end_date > sysdate);
3397
3398 cursor check_usertype_status is
3399 select status_code
3400 from jtf_um_usertype_reg
3401 where user_id = p_user_id
3402 and (effective_end_date is null
3403 or effective_end_date > sysdate);
3404
3405 begin
3406
3407 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3408 'Entering enrollment_approval_changed (' || p_subscription_id || ',' ||
3409 p_new_approval_id || ',' || p_old_approval_id || ',' ||
3410 p_org_party_id || ') API');
3411
3412 -- Find out the new item_type to create Workflow process
3413 open find_new_item_type;
3414 fetch find_new_item_type into p_wf_new_item_type;
3415 close find_new_item_type;
3416
3417 if (p_org_party_id is null) then
3418 open subscription_reg;
3419 else
3420 open subscription_reg_w_org;
3421 end if;
3422 loop
3423 if (p_org_party_id is null) then
3424 fetch subscription_reg into p_subscription_reg_id, p_user_id, p_wf_old_item_type;
3425 exit when subscription_reg%NOTFOUND;
3426 else
3427 fetch subscription_reg_w_org into p_subscription_reg_id, p_user_id, p_wf_old_item_type;
3428 exit when subscription_reg_w_org%NOTFOUND;
3429 end if;
3430
3431 -- abort WF Process first
3432 if (p_wf_old_item_type is not null) then
3433 abort_process (p_wf_old_item_type, p_subscription_reg_id);
3434 end if;
3435
3436 if p_wf_new_item_type is null then
3437 -- The user selected no workflow
3438 -- Approve the request if the usertype status is approved.
3439 -- If status is PENDING, change the workflow in the
3440 -- JTF_UM_SUBSCRIPTION_REG to null.
3441
3442 open check_usertype_status;
3443 fetch check_usertype_status into p_usertype_status;
3444 if (check_usertype_status%notfound) then
3445 close check_usertype_status;
3446 if (p_org_party_id is null) then
3447 close subscription_reg;
3448 else
3449 close subscription_reg_w_org;
3450 end if;
3451 -- all Users who are using the UM should be in the
3452 -- JTF_UM_USERTYPE_REG table.
3453 raise_application_error (20000, 'User info is missing');
3454 end if;
3455 close check_usertype_status;
3456
3457 -- check if the user status code is pending.
3458 -- if pending, then we will not approve
3459
3460 if (p_usertype_status = 'PENDING') or
3461 (p_usertype_status = 'UPGRADE_PENDING') then
3462 -- usertype is 'PENDING', end date the last record and add a new
3463 -- record with null in the workflow itemtype column.
3464 update JTF_UM_SUBSCRIPTION_REG
3465 set EFFECTIVE_END_DATE = sysdate,
3466 LAST_UPDATE_DATE = sysdate,
3467 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
3468 where SUBSCRIPTION_REG_ID = p_subscription_reg_id;
3469
3470 -- create record in JTF_UM_SUBSCRIPTION_REG table
3471 JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW
3472 (X_SUBSCRIPTION_ID => p_subscription_id,
3473 X_LAST_APPROVER_COMMENT => null,
3474 X_APPROVER_USER_ID => null,
3475 X_EFFECTIVE_END_DATE => null,
3476 X_WF_ITEM_TYPE => null,
3477 X_EFFECTIVE_START_DATE => sysdate,
3478 X_SUBSCRIPTION_REG_ID => p_new_item_key,
3479 X_USER_ID => p_user_id,
3480 X_STATUS_CODE => 'PENDING',
3481 X_CREATION_DATE => sysdate,
3482 X_CREATED_BY => FND_GLOBAL.USER_ID,
3483 X_LAST_UPDATE_DATE => sysdate,
3484 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3485 X_LAST_UPDATE_LOGIN => null);
3486 else
3487 -- User status is not PENDING
3488 do_approve_req (itemtype => p_wf_old_item_type,
3489 itemkey => p_subscription_reg_id);
3490 end if;
3491 else
3492 -- p_wf_new_item_type is not null
3493 -- end date the old record in JTF_UM_SUBSCRIPTION_REG table
3494 update JTF_UM_SUBSCRIPTION_REG
3495 set EFFECTIVE_END_DATE = sysdate,
3496 LAST_UPDATE_DATE = sysdate,
3497 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
3498 where SUBSCRIPTION_REG_ID = p_subscription_reg_id;
3499
3500 -- create record in JTF_UM_SUBSCRIPTION_REG table
3501 JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW
3502 (X_SUBSCRIPTION_ID => p_subscription_id,
3503 X_LAST_APPROVER_COMMENT => null,
3504 X_APPROVER_USER_ID => null,
3505 X_EFFECTIVE_END_DATE => null,
3506 X_WF_ITEM_TYPE => p_wf_new_item_type,
3507 X_EFFECTIVE_START_DATE => sysdate,
3508 X_SUBSCRIPTION_REG_ID => p_new_item_key,
3509 X_USER_ID => p_user_id,
3510 X_STATUS_CODE => 'PENDING',
3511 X_CREATION_DATE => sysdate,
3512 X_CREATED_BY => FND_GLOBAL.USER_ID,
3513 X_LAST_UPDATE_DATE => sysdate,
3514 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3515 X_LAST_UPDATE_LOGIN => null);
3516
3517 -- Create WF process
3518 CreateProcess (ownerUserId => FND_GLOBAL.USER_ID,
3519 requestType => 'ENROLLMENT',
3520 requestID => p_subscription_id,
3521 requesterUserID => p_user_id,
3522 requestRegID => p_new_item_key);
3523
3524 -- Launch WF process if user type approval has gone through
3525 open check_usertype_status;
3526 fetch check_usertype_status into p_usertype_status;
3527 close check_usertype_status;
3528
3529 if (p_usertype_status = 'APPROVED') then
3530 wf_engine.startProcess (itemType => p_wf_new_item_type,
3531 itemKey => p_new_item_key);
3532 end if;
3533 end if;
3534 end loop;
3535 if (p_org_party_id is null) then
3536 close subscription_reg;
3537 else
3538 close subscription_reg_w_org;
3539 end if;
3540
3541 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Initialization API');
3542
3543 end enrollment_approval_changed;
3544
3545 procedure enrollment_approval_changed (p_subscription_id in number,
3546 p_new_approval_id in number,
3547 p_old_approval_id in number) is
3548 begin
3549
3550 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3551 'Entering enrollment_approval_changed (' || p_subscription_id || ',' ||
3552 p_new_approval_id || ',' || p_old_approval_id || ') API');
3553
3554 enrollment_approval_changed (p_subscription_id,
3555 p_new_approval_id,
3556 p_old_approval_id,
3557 null);
3558
3559 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting enrollment_approval_changed API');
3560
3561 end enrollment_approval_changed;
3562
3563 procedure approval_chain_changed(p_approval_id in number,
3564 p_org_party_id in number)
3565 is
3566
3567 cursor usertype_approval is select usertype_id from jtf_um_usertypes_b
3568 where approval_id = p_approval_id
3569 and nvl (effective_end_date, sysdate + 1) > sysdate;
3570 p_usertype_id number;
3571
3572 cursor subscription_approval is select subscription_id from jtf_um_subscriptions_b
3573 where approval_id = p_approval_id
3574 and nvl (effective_end_date, sysdate + 1) > sysdate;
3575 p_subscription_id number;
3576
3577 begin
3578
3579 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3580 'Entering approval_chain_changed (' || p_approval_id || ',' ||
3581 p_org_party_id || ') API');
3582
3583 open usertype_approval;
3584 loop
3585 fetch usertype_approval into p_usertype_id;
3586 exit when usertype_approval%NOTFOUND;
3587
3588 -- Call procedure for usertype
3589 if p_org_party_id is not null then
3590
3591 usertype_approval_changed (p_usertype_id => p_usertype_id,
3592 p_new_approval_id => p_approval_id,
3593 p_old_approval_id => p_approval_id,
3594 p_org_party_id => p_org_party_id);
3595 else
3596
3597 usertype_approval_changed (p_usertype_id => p_usertype_id,
3598 p_new_approval_id => p_approval_id,
3599 p_old_approval_id => p_approval_id);
3600
3601 end if;
3602
3603 end loop;
3604 close usertype_approval;
3605
3606 open subscription_approval;
3607 loop
3608 fetch subscription_approval into p_subscription_id;
3609 exit when subscription_approval%NOTFOUND;
3610
3611 -- Call procedure for enrollments
3612 if p_org_party_id is not null then
3613
3614 enrollment_approval_changed (p_subscription_id => p_subscription_id,
3615 p_new_approval_id => p_approval_id,
3616 p_old_approval_id => p_approval_id,
3617 p_org_party_id => p_org_party_id);
3618
3619 else
3620
3621 enrollment_approval_changed (p_subscription_id => p_subscription_id,
3622 p_new_approval_id => p_approval_id,
3623 p_old_approval_id => p_approval_id,
3624 p_org_party_id => null);
3625
3626 end if;
3627 end loop;
3628 close subscription_approval;
3629
3630 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting approval_chain_changed API');
3631
3632 end approval_chain_changed;
3633
3634 function get_approver_comment(p_reg_id in number,
3635 p_wf_item_type in varchar2) return varchar2 is
3636
3637 p_approver_comment varchar2(4000);
3638
3639 begin
3640
3641 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3642 'Entering get_approver_comment (' || p_reg_id || ',' ||
3643 p_wf_item_type || ') API');
3644
3645 p_approver_comment := wf_engine.GetItemAttrText (
3646 itemtype => p_wf_item_type,
3647 itemkey => to_char(p_reg_id),
3648 aname => 'APPROVER_COMMENT');
3649
3650 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting get_approver_comment API');
3651
3652 return p_approver_comment;
3653
3654 end get_approver_comment;
3655
3656
3657 end JTF_UM_WF_APPROVAL;