[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_WF_APPROVAL
Source
1 PACKAGE BODY JTF_UM_WF_APPROVAL as
2 /* $Header: JTFUMWFB.pls 120.11.12010000.4 2008/11/01 05:25:12 appldev 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
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: 7270214-------------------
868
869 open getAdHocRole;
870 fetch getAdHocRole into l_role_name, l_approver_display_name;
871 if (getAdHocRole%NOTFOUND) then
872 WF_DIRECTORY.CreateAdHocRole(role_name => x_role_name, role_display_name =>x_wf_dispname, email_address => Email_Address, notification_preference =>'MAILHTML' );
873 elsif (getAdHocRole%FOUND) then
874 WF_DIRECTORY.SetAdHocRoleAttr(role_name=> x_role_name,email_address => Email_Address);
875 end if;
876 --------------Bug No: 7270214-------------------
877 wf_engine.SetItemAttrText (itemtype => itemtype,
878 itemkey => itemkey,
879 aname => 'USER_AD_HOC_ROLE',
880 avalue => x_role_name);
881
882 resultout := 'COMPLETE:';
883 end if;
884
885 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Initialization API');
886
887 exception
888 when MISSING_REQUESTER_USER_ID then
889 wf_core.context ('JTF_UM_WF_APPROVAL', 'Initialization', itemtype, itemkey, to_char (actid), funcmode,
890 'Requester User ID is missing in the FND_USER');
891 raise;
892 when others then
893 wf_core.context ('JTF_UM_WF_APPROVAL', 'Initialization', itemtype, itemkey, to_char (actid), funcmode);
894 raise;
895 end Initialization;
896
897 /**
898 * Procedure: get_org_ad_hoc_role
899 * Type: Private
900 * Prereqs:
901 * Description: This API will
902 * 1) create/update an ad hoc role with named "JTAUM###".
903 * 2) find all approvers from the same organization and with
904 * "JTF_PRIMARY_USER_SUMMARY" permission.
905 * 3) associate the ad hoc role with approvers
906 * Parameters
907 * input parameters: p_itemtype - itemtype of the workflow
908 * p_itemkey - itemkey of the workflow
909 * output parameters: x_role_name - The name of the ad hoc role, null if
910 * role didn't get created.
911 * x_role_name_display - The display name of the ad hoc role.
912 * Errors:
913 * Other Comments:
914 */
915 procedure get_org_ad_hoc_role (p_itemtype in varchar2,
916 p_itemkey in varchar2,
917 x_role_name out NOCOPY varchar2,
918 x_role_name_display out NOCOPY varchar2) is
919
920 l_method_name varchar2 (20) := 'GET_ORG_AD_HOC_ROLE';
921 l_requester_user_id fnd_user.user_id%type;
922 l_org_name hz_parties.party_name%type;
923 l_org_number hz_parties.party_number%type;
924 l_uni_approver_not_found boolean := true;
925 l_role_name wf_local_roles.name%type;
926 l_approver_display_name varchar2(1000);
927
928 cursor getADHocRole is
929 select name, display_name
930 from WF_LOCAL_ROLES
931 where name = x_role_name;
932
933 cursor getUniversalApprovers is
934 select fnd.user_name
935 from hz_parties hz_org, hz_relationships hzr, fnd_user fnd
936 where hz_org.party_number = l_org_number
937 and hz_org.party_type = 'ORGANIZATION'
938 and hz_org.party_id = hzr.object_id
939 and hzr.start_date <= sysdate
940 and nvl (hzr.end_date, sysdate + 1) > sysdate
941 and hzr.relationship_code = 'EMPLOYEE_OF'
942 and hzr.object_table_name = 'HZ_PARTIES'
943 and hzr.subject_table_name = 'HZ_PARTIES'
944 and fnd.customer_id = hzr.party_id
945 and fnd.start_date <= sysdate
946 and nvl (fnd.end_date, sysdate + 1) > sysdate
947
948 and exists (
949 select prin_b.principal_name
950 from jtf_auth_domains_b domains_b, jtf_auth_permissions_b perm,
951 jtf_auth_principal_maps prin_maps, jtf_auth_role_perms role_perms,
952 jtf_auth_principals_b prin_b, jtf_auth_principals_b prin_b2
953 where prin_b.jtf_auth_principal_id = prin_maps.jtf_auth_principal_id
954 and prin_maps.jtf_auth_parent_principal_id = prin_b2.jtf_auth_principal_id
955 and prin_b2.jtf_auth_principal_id = role_perms.jtf_auth_principal_id
956 and role_perms.jtf_auth_permission_id = perm.jtf_auth_permission_id
957 and prin_maps.jtf_auth_domain_id = domains_b.jtf_auth_domain_id
958 and domains_b.domain_name = 'CRM_DOMAIN'
959 and perm.permission_name = 'JTF_PRIMARY_USER_SUMMARY'
960 and prin_b.principal_name = fnd.user_name
961 );
962 --changes for 4734470
963
964 UserTable WF_DIRECTORY.UserTable;
965 idx pls_integer :=0;
966
967 begin
968
969 -- Log the entering
970 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (G_MODULE, l_method_name);
971
972 -- Log parameters
973 if l_is_debug_parameter_on then
974 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
975 'p_itemtype=' || p_itemtype);
976 JTF_DEBUG_PUB.LOG_PARAMETERS (G_MODULE || '.' || l_method_name,
977 'p_itemkey=' || p_itemkey);
978 end if;
979
980 -- to construct the roleName, we need the organization number
981 -- get the user id and find out what is his/her org number
982 l_requester_user_id := wf_engine.GetItemAttrNumber (
983 itemtype => p_itemtype,
984 itemkey => p_itemkey,
985 aname => 'REQUESTER_USER_ID');
986
987 -- get the organization number
988 get_org_info (p_user_id => l_requester_user_id,
989 x_org_name => l_org_name,
990 x_org_number => l_org_number);
991
992 -- the name of the role
993 x_role_name := g_adhoc_role_name_prefix || l_org_number;
994
995 open getAdHocRole;
996 fetch getAdHocRole into l_role_name, l_approver_display_name;
997 if (getAdHocRole%found) then
998 -- Update role
999 WF_DIRECTORY.RemoveUsersFromAdHocRole (role_name => x_role_name);
1000 else
1001 -- Get the role display name from FND Message.
1002 fnd_message.set_name ('JTF', 'JTA_UM_APPROVAL_ROLE_DISP_NAME');
1003 fnd_message.set_token ('ORGNAME', l_org_name, false);
1004 fnd_message.set_token ('ORGNUMBER', l_org_number, false);
1005 l_approver_display_name := fnd_message.get;
1006
1007 x_role_name_display:= substr(l_approver_display_name, 1, 100);
1008
1009 -- Create role
1010 -- Changes for Bug 6010991
1011 -- restore to behaviour, prior to bug Bug 3361734,
1012 -- of JTA Ad Hoc Roles having notification pref of MAILHTML
1013 --
1014 WF_DIRECTORY.CreateAdHocRole (role_name => x_role_name,
1015 role_display_name => x_role_name_display,
1016 notification_preference =>'MAILHTML'
1017 );
1018 -- End of changes for Bug 6010991
1019 end if;
1020 close getAdHocRole;
1021
1022 for approver in getUniversalApprovers loop
1023 userTable(idx) := approver.user_name;
1024 idx :=idx + 1;
1025 end loop;
1026 If userTable.count >0 then
1027 l_uni_approver_not_found := false;
1028 WF_DIRECTORY.AddUsersToAdHocRole2 (role_name => x_role_name,
1029 role_users => userTable);
1030 end if;
1031
1032 if l_uni_approver_not_found then
1033 x_role_name := null;
1034 end if;
1035
1036 JTF_DEBUG_PUB.LOG_EXITING_METHOD (G_MODULE, l_method_name);
1037
1038 end get_org_ad_hoc_role;
1039
1040 --
1041 -- SelectApprover
1042 -- DESCRIPTION
1043 -- Select the next approver from the approver order.
1044 -- IN
1045 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1046 -- itemkey - A string generated from the application object's primary key.
1047 -- actid - The function activity(instance id).
1048 -- funcmode - Run/Cancel/Timeout
1049 -- OUT
1050 -- Resultout - 'COMPLETE:T' if there is a next approver
1051 -- - 'COMPLETE:F' if there is not a next approver
1052 --
1053 procedure SelectApprover (itemtype in varchar2,
1054 itemkey in varchar2,
1055 actid in number,
1056 funcmode in varchar2,
1057 resultout out NOCOPY varchar2) is
1058 --
1059 applID number;
1060 approverID number (15);
1061 approverUsername fnd_user.user_name%type;
1062 approverUserID fnd_user.user_id%type;
1063 requestType varchar2 (10);
1064 requestId number;
1065 resultType varchar2 (5);
1066 uniPrimaryUser fnd_profile_option_values.profile_option_value%type;
1067 l_approver_username_display varchar2 (100);
1068
1069 begin
1070 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering SelectApprover (' ||
1071 itemtype || ',' || itemkey || ',' || actid || ',' ||
1072 funcmode || ') API');
1073
1074 --
1075 -- RUN mode - normal process execution
1076 --
1077 if (funcmode = 'RUN') then
1078 --
1079 -- Call API to retrieve the next approver.
1080 --
1081 GetApprover (itemtype, itemkey, approverUsername, approverUserID, approverID, resultType);
1082
1083 --
1084 -- There are two resultTypes from GetApprover.
1085 -- OK - The api returns the next approver.
1086 -- END - There is no more approver in the approver order.
1087 --
1088 if (resultType = 'END') then
1089 -- Can't find any approver from GetApprover
1090 resultout := 'COMPLETE:F';
1091 else
1092 resultout := 'COMPLETE:T';
1093
1094 -- Check to see if the approver is a Universal Approver
1095 -- Get the application id of the requestType
1096 requestType := wf_engine.GetItemAttrText (
1097 itemtype => itemtype,
1098 itemkey => itemkey,
1099 aname => 'REQUEST_TYPE');
1100
1101 requestId := wf_engine.GetItemAttrNumber (
1102 itemtype => itemtype,
1103 itemkey => itemkey,
1104 aname => 'REQUEST_ID');
1105
1106 applID := getRequestApplId (requestType, requestId);
1107
1108 uniPrimaryUser := JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
1109 NAME => 'JTF_PRIMARY_USER',
1110 APPLICATION_ID => applID,
1111 SITE_LEVEL => true);
1112
1113 if (approverUsername = uniPrimaryUser) then
1114 -- the current approver is a Universal Approver
1115 get_org_ad_hoc_role (p_itemtype => itemtype,
1116 p_itemkey => itemkey,
1117 x_role_name => approverUsername,
1118 x_role_name_display => l_approver_username_display);
1119 if (approverUsername is null) then
1120 -- Which mean an ad hoc role didn't get created. Use
1121 -- the default approver from the JTA_UM_DEFAULT_APPROVER
1122 -- profile option.
1123 approverUsername := nvl (JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
1124 NAME => 'JTA_UM_DEFAULT_APPROVER',
1125 APPLICATION_ID => applID,
1126 SITE_LEVEL => true), 'SYSADMIN');
1127
1128 l_approver_username_display := lower (approverUsername);
1129 approverUserID := getUserID (username => approverUsername);
1130 end if;
1131 else
1132 l_approver_username_display := lower (approverUsername);
1133 end if;
1134
1135 -- We need to update the approver username and id
1136 wf_engine.SetItemAttrText (
1137 itemtype => itemtype,
1138 itemkey => itemkey,
1139 aname => 'APPROVER_USERNAME',
1140 avalue => approverUsername);
1141
1142 wf_engine.SetItemAttrText (
1143 itemtype => itemtype,
1144 itemkey => itemkey,
1145 aname => 'APPROVER_USERNAME_DISPLAY',
1146 avalue => l_approver_username_display);
1147
1148 wf_engine.SetItemAttrNumber (
1149 itemtype => itemtype,
1150 itemkey => itemkey,
1151 aname => 'APPROVER_ID',
1152 avalue => approverID);
1153
1154 -- update the APPROVER_USER_ID in the ****_REG table.
1155 requestType := wf_engine.GetItemAttrText (
1156 itemtype => itemtype,
1157 itemkey => itemkey,
1158 aname => 'REQUEST_TYPE');
1159 if (requestType = 'USERTYPE') then
1160 update JTF_UM_USERTYPE_REG
1161 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1162 LAST_UPDATE_DATE = sysdate,
1163 APPROVER_USER_ID = approverUserID
1164 where USERTYPE_REG_ID = itemkey;
1165 else
1166 update JTF_UM_SUBSCRIPTION_REG
1167 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1168 LAST_UPDATE_DATE = sysdate,
1169 APPROVER_USER_ID = approverUserID
1170 where SUBSCRIPTION_REG_ID = itemkey;
1171 end if;
1172 end if;
1173 --
1174 end if;
1175
1176 --
1177 -- CANCEL mode
1178 --
1179 if (funcmode = 'CANCEL') then
1180 --
1181 resultout := 'COMPLETE:';
1182 --
1183 end if;
1184
1185 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Initialization API');
1186
1187 exception
1188 when others then
1189 wf_core.context ('JTF_UM_WF_APPROVAL', 'SelectorApprover', itemtype,
1190 itemkey, to_char (actid), funcmode);
1191 raise;
1192 end SelectApprover;
1193
1194 --
1195 -- Procedure
1196 -- GetApprover
1197 --
1198 -- Description
1199 -- Private method to get the next approver
1200 -- IN
1201 -- itemtype - workflow itemtype
1202 -- itemkey - workflow itemkey
1203 -- Out
1204 -- approver's user_name
1205 -- approver's user ID
1206 -- approver ID
1207 -- resultType - 'OK' return next approver.
1208 -- 'END' no more approver in the approver list.
1209 --
1210 Procedure GetApprover (x_itemtype in varchar2,
1211 x_itemkey in varchar2,
1212 x_approverUsername out NOCOPY varchar2,
1213 x_approverUserID out NOCOPY number,
1214 x_approverID out NOCOPY number,
1215 x_resultType out NOCOPY varchar2) is
1216 --
1217 l_approvalID number (15);
1218 l_approverSeq number (15);
1219 l_requesterUserID number (15);
1220 l_requestType varchar2 (10);
1221 l_org_party_id number;
1222 l_org_override varchar2 (1);
1223 --
1224 cursor approverSequenceCursor is
1225 select APPROVER_SEQ
1226 from JTF_UM_APPROVERS
1227 where APPROVER_ID = x_approverID
1228 and APPROVAL_ID = l_approvalID;
1229
1230 cursor nextApproverInfoCursor is
1231 select a.APPROVER_ID, a.USER_ID, f.USER_NAME
1232 from JTF_UM_APPROVERS a, FND_USER f
1233 where a.APPROVER_SEQ > l_approverSeq
1234 and a.APPROVAL_ID = l_approvalID
1235 and a.org_party_id is null
1236 and a.EFFECTIVE_START_DATE <= sysdate
1237 and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1238 and a.USER_ID = f.USER_ID
1239 and f.START_DATE <= sysdate
1240 and nvl (f.END_DATE, sysdate + 1) > sysdate
1241
1242 order by a.APPROVER_SEQ;
1243
1244 cursor nextOrgApproverInfoCursor is
1245 select a.APPROVER_ID, a.USER_ID, f.USER_NAME
1246 from JTF_UM_APPROVERS a, FND_USER f
1247 where a.APPROVER_SEQ > l_approverSeq
1248 and a.APPROVAL_ID = l_approvalID
1249 and a.ORG_PARTY_ID = l_org_party_id
1250 and a.EFFECTIVE_START_DATE <= sysdate
1251 and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1252 and a.USER_ID = f.USER_ID
1253 and f.START_DATE <= sysdate
1254 and nvl (f.END_DATE, sysdate + 1) > sysdate
1255 order by a.APPROVER_SEQ;
1256
1257
1258 cursor OrgApproverOverrideCursor is
1259 select 'X'
1260 from JTF_UM_APPROVERS
1261 where APPROVAL_ID = l_approvalID
1262 and ORG_PARTY_ID = l_org_party_id
1263 and EFFECTIVE_START_DATE <= sysdate
1264 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
1265
1266 -- select the requesters org party id
1267 cursor requesterOrgCursor is
1268 select hzr.object_id requester_org_id
1269 from hz_relationships hzr,
1270 FND_USER fu
1271 where fu.USER_ID = l_requesterUserID
1272 and fu.CUSTOMER_ID = hzr.PARTY_ID
1273 and hzr.start_date <= sysdate
1274 and nvl (hzr.END_DATE, sysdate + 1) > sysdate
1275 and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
1276 and hzr.object_type = 'ORGANIZATION'
1277 and hzr.subject_table_name = 'HZ_PARTIES'
1278 and hzr.object_table_name = 'HZ_PARTIES';
1279 --
1280 begin
1281
1282 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering GetApprover (' ||
1283 x_itemtype || ',' || x_itemkey || ') API');
1284
1285 -- check input parameter
1286 if (x_itemtype is null) then
1287 raise_application_error (-20000, 'itemtype is null when calling JTF_UM_WF_APPROVAL.GetApprover');
1288 end if;
1289
1290 if (x_itemkey is null) then
1291 raise_application_error (-20000, 'itemkey is null when calling JTF_UM_WF_APPROVAL.GetApprover');
1292 end if;
1293
1294 x_resultType := 'OK';
1295 -- Get the APPROVER_ID and APPROVAL_ID
1296 x_approverID := wf_engine.GetItemAttrNumber (
1297 itemtype => x_itemtype,
1298 itemkey => x_itemkey,
1299 aname => 'APPROVER_ID');
1300
1301 l_approvalID := wf_engine.GetItemAttrNumber (
1302 itemtype => x_itemtype,
1303 itemkey => x_itemkey,
1304 aname => 'APPROVAL_ID');
1305
1306 l_requesterUserID := wf_engine.GetItemAttrNumber (
1307 itemtype => x_itemtype,
1308 itemkey => x_itemkey,
1309 aname => 'REQUESTER_USER_ID');
1310
1311 l_requestType := wf_engine.GetItemAttrText (
1312 itemtype => x_itemtype,
1313 itemkey => x_itemkey,
1314 aname => 'REQUEST_TYPE');
1315
1316 -- Get the requesters Organization Party Id
1317 -- if null, then we use the default approvers
1318 open requesterOrgCursor;
1319 fetch requesterOrgCursor into l_org_party_id;
1320 -- Are there any org specific approvers for the requesters org?
1321 -- if not we use the default approvers
1322 if requesterOrgCursor%FOUND then
1323 open OrgApproverOverrideCursor;
1324 fetch OrgApproverOverrideCursor into l_org_override;
1325 close OrgApproverOverrideCursor;
1326 end if;
1327 close requesterOrgCursor;
1328
1329
1330 -- if APPROVER_ID is null, then approverSeq will be 1, the first approver.
1331 -- else, use the APPROVER_ID to find the next approver.
1332 if (x_approverID is null) then
1333 l_approverSeq := -1;
1334 else
1335 open approverSequenceCursor;
1336 fetch approverSequenceCursor into l_approverSeq;
1337 if (approverSequenceCursor%notfound) then
1338 -- ERROR, can't find the approver's sequence.
1339 close approverSequenceCursor;
1340 wf_core.token ('MESSAGE', 'Cannot find the current approver (user_id = '||to_char(x_approverId)
1341 ||' approval_id ='||to_char(l_approvalId)||') in JTF_UM_APPROVERS - Data corruption.');
1342 wf_core.raise ('MISSING_APPROVER_SEQUENCE');
1343 end if;
1344 close approverSequenceCursor;
1345 end if;
1346
1347 -- If there are org specific approvers, get the first/next one
1348 if l_org_override is not null then
1349 open nextOrgApproverInfoCursor;
1350 fetch nextOrgApproverInfoCursor into x_approverID, x_approverUserID, x_approverUserName;
1351 if (nextOrgApproverInfoCursor%notfound) then
1352 -- No more approvers, result 'END' -
1353 x_resultType := 'END';
1354 close nextOrgApproverInfoCursor;
1355 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting GetApprover API');
1356 return;
1357 end if;
1358 close nextOrgApproverInfoCursor;
1359 -- If we should use the default approvers, find the first / next one
1360 else
1361
1362 open nextApproverInfoCursor;
1363 fetch nextApproverInfoCursor into x_approverID, x_approverUserID, x_approverUserName;
1364 if (nextApproverInfoCursor%notfound) then
1365 -- No more approvers, result 'END'
1366 x_resultType := 'END';
1367 close nextApproverInfoCursor;
1368 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting GetApprover API');
1369 return;
1370 end if;
1371 close nextApproverInfoCursor;
1372 end if;
1373
1374 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting GetApprover API');
1375
1376 exception
1377 when others then
1378 wf_core.context ('JTF_UM_WF_APPROVAL', 'GetApprover', x_itemtype, x_itemkey);
1379 raise;
1380 end GetApprover;
1381
1382 --
1383 -- SelectRequestType
1384 -- DESCRIPTION
1385 -- Return what requesttype that the requester is requesting.
1386 -- IN
1387 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1388 -- itemkey - A string generated from the application object's primary key.
1389 -- actid - The function activity(instance id).
1390 -- funcmode - Run/Cancel/Timeout
1391 -- OUT
1392 -- Resultout - 'COMPLETE:USERTYPE' if it is a usertype request
1393 -- - 'COMPLETE:ENROLLMENT' if it is a enrollment request
1394 --
1395 procedure SelectRequestType (itemtype in varchar2,
1396 itemkey in varchar2,
1397 actid in number,
1398 funcmode in varchar2,
1399 resultout out NOCOPY varchar2) is
1400 --
1401 requestType varchar2 (10);
1402 --
1403 begin
1404
1405 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Initialization (' ||
1406 itemtype || ',' || itemkey || ',' || actid || ',' ||
1407 funcmode || ') API');
1408
1409 --
1410 -- RUN mode - normal process execution
1411 --
1412 if (funcmode = 'RUN') then
1413 requestType := wf_engine.GetItemAttrText (
1414 itemtype => itemtype,
1415 itemkey => itemkey,
1416 aname => 'REQUEST_TYPE');
1417 if (requestType = 'USERTYPE') then
1418 resultout := 'COMPLETE:USERTYPE';
1419 else
1420 resultout := 'COMPLETE:ENROLLMENT';
1421 end if;
1422 --
1423 -- CANCEL mode
1424 --
1425 elsif (funcmode = 'CANCEL') then
1426 --
1427 resultout := 'COMPLETE:';
1428 --
1429 --
1430 -- TIMEOUT mode
1431 --
1432 elsif (funcmode = 'TIMEOUT') then
1433 resultout := 'COMPLETE:';
1434 end if;
1435
1436 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting SelectRequestType API');
1437
1438 exception
1439 when others then
1440 wf_core.context ('JTF_UM_WF_APPROVAL', 'SelectRequestType', itemtype, itemkey, to_char (actid), funcmode);
1441 raise;
1442 end SelectRequestType;
1443
1444 --
1445 -- cancel_notification
1446 -- DESCRIPTION
1447 -- Cancel all open notifications
1448 -- IN
1449 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
1450 -- p_itemkey - A string generated from the application object's primary key.
1451 --
1452 procedure cancel_notification (p_itemtype in varchar2,
1453 p_itemkey in varchar2) is
1454 --
1455 notificationID number (15);
1456
1457 cursor getNotificationID is
1458 select wias.notification_id
1459 from wf_process_activities wpa, wf_item_activity_statuses wias, wf_notifications wn
1460 where wpa.PROCESS_ITEM_TYPE = p_itemtype
1461 and wpa.ACTIVITY_ITEM_TYPE = wpa.PROCESS_ITEM_TYPE
1462 and (wpa.INSTANCE_LABEL = 'NTF_APPROVAL_USERTYPE_REQUIRED'
1463 or wpa.INSTANCE_LABEL = 'NTF_REMIND_USERTYPE_REQUIRED'
1464 or wpa.INSTANCE_LABEL = 'NTF_FAIL_ESCALATE_USERTYPE_REQ')
1465 and wias.item_type = wpa.PROCESS_ITEM_TYPE
1466 and wias.item_key = p_itemkey
1467 and wias.process_activity = wpa.instance_id
1468 and wn.status = 'OPEN'
1469 and wn.notification_id = wias.notification_id;
1470 --
1471 begin
1472
1473 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering cancel_notification (' ||
1474 p_itemtype || ',' || p_itemkey || ') API');
1475
1476 -- check input parameter
1477 if (p_itemtype is null) then
1478 raise_application_error (-20000, 'itemtype is null when calling JTF_UM_WF_APPROVAL.cancel_notification');
1479 end if;
1480
1481 if (p_itemkey is null) then
1482 raise_application_error (-20000, 'itemkey is null when calling JTF_UM_WF_APPROVAL.cancel_notification');
1483 end if;
1484
1485 -- Need to end all open notifications.
1486 open getNotificationID;
1487 -- We have two notification that we need to cancel.
1488 fetch getNotificationID into notificationID;
1489 while getNotificationID%found loop
1490 wf_notification.cancel (notificationID);
1491 fetch getNotificationID into notificationID;
1492 end loop;
1493 close getNotificationID;
1494
1495 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting cancel_notification API');
1496
1497 end cancel_notification;
1498
1499 --
1500 -- initialize_fail_escalate
1501 -- DESCRIPTION
1502 -- Update the reg table and performer when fail to escalate approver.
1503 -- IN
1504 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1505 -- itemkey - A string generated from the application object's primary key.
1506 -- actid - The function activity(instance id).
1507 -- funcmode
1508 -- OUT
1509 -- Resultout - 'COMPLETE:'
1510 --
1511 procedure initialize_fail_escalate (itemtype in varchar2,
1512 itemkey in varchar2,
1513 actid in number,
1514 funcmode in varchar2,
1515 resultout out NOCOPY varchar2) is
1516 --
1517 ownerUsername varchar2 (100);
1518 ownerUserID number (15);
1519 requestType varchar2 (10);
1520
1521 cursor getUserID is
1522 select USER_ID
1523 from FND_USER
1524 where USER_NAME = ownerUsername;
1525 --
1526 begin
1527
1528 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
1529 'Entering initialize_fail_escalate (' || itemtype || ',' ||
1530 itemkey || ',' || actid || ',' || funcmode || ') API');
1531
1532 --
1533 -- RUN mode
1534 --
1535 if (funcmode = 'RUN') then
1536
1537 -- Need to end all open notifications.
1538 cancel_notification (itemtype, itemkey);
1539
1540 -- The result of the request has not been decided
1541 -- Forward the request to the owner of this process.
1542 ownerUsername := wf_engine.GetItemAttrText (
1543 itemtype => itemtype,
1544 itemkey => itemkey,
1545 aname => 'OWNER_USERNAME');
1546
1547 -- This is for the next activity in the workflow, can_delegate.
1548 wf_engine.SetItemAttrText (
1549 itemtype => itemtype,
1550 itemkey => itemkey,
1551 aname => 'APPROVER_USERNAME',
1552 avalue => ownerUsername);
1553
1554 open getUserID;
1555 fetch getUserID into ownerUserID;
1556 close getUserID;
1557
1558 -- Get the requestType.
1559 requestType := wf_engine.GetItemAttrText (
1560 itemtype => itemtype,
1561 itemkey => itemkey,
1562 aname => 'REQUEST_TYPE');
1563
1564 -- We need to update the approver id in the reg table
1565 if (requestType = 'USERTYPE') then
1566 update JTF_UM_USERTYPE_REG
1567 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1568 LAST_UPDATE_DATE = sysdate,
1569 APPROVER_USER_ID = ownerUserID
1570 where USERTYPE_REG_ID = to_number(itemkey);
1571 elsif (requestType = 'ENROLLMENT') then
1572 update JTF_UM_SUBSCRIPTION_REG
1573 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1574 LAST_UPDATE_DATE = sysdate,
1575 APPROVER_USER_ID = ownerUserID
1576 where SUBSCRIPTION_REG_ID = to_number(itemkey);
1577 end if;
1578 resultout := 'COMPLETE';
1579 end if;
1580
1581 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting initialize_fail_escalate API');
1582
1583 exception
1584 when others then
1585 wf_core.context ('JTF_UM_WF_APPROVAL', 'initialize_fail_escalate', itemtype, itemkey, to_char (actid), funcmode);
1586 raise;
1587 end initialize_fail_escalate;
1588
1589 --
1590 -- WaitForApproval
1591 -- DESCRIPTION
1592 -- Check whether the task is approved or rejected.
1593 -- IN
1594 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1595 -- itemkey - A string generated from the application object's primary key.
1596 -- actid - The function activity(instance id).
1597 -- funcmode - Run/Cancel/Timeout
1598 -- OUT
1599 -- Resultout - 'COMPLETE:APPROVED' if the request is approved.
1600 -- - 'COMPLETE:REJECTED' if the request is rejected.
1601 --
1602 procedure WaitForApproval (itemtype in varchar2,
1603 itemkey in varchar2,
1604 actid in number,
1605 funcmode in varchar2,
1606 resultout out NOCOPY varchar2) is
1607 --
1608 requestResult varchar2 (8);
1609 --
1610 begin
1611
1612 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering WaitForApproval (' ||
1613 itemtype || ',' || itemkey || ',' || actid || ',' ||
1614 funcmode || ') API');
1615
1616 --
1617 -- RUN mode - normal process execution
1618 --
1619 if (funcmode = 'RUN') then
1620 requestResult := wf_engine.GetItemAttrText (
1621 itemtype => itemtype,
1622 itemkey => itemkey,
1623 aname => 'REQUEST_RESULT');
1624
1625 if (requestResult = 'APPROVED') then
1626 resultout := 'COMPLETE:APPROVED';
1627 elsif (requestResult = 'REJECTED') then
1628 resultout := 'COMPLETE:REJECTED';
1629 else
1630 fnd_message.set_name ('JTF', 'JTA_UM_REQUIRED_FIELD');
1631 fnd_message.set_token ('API_NAME', itemtype, false);
1632 fnd_message.set_token ('FIELD', 'REQUEST_RESULT', false);
1633 raise_application_error(-20000, fnd_message.get);
1634 end if;
1635
1636 --
1637 -- CANCEL mode
1638 --
1639 elsif (funcmode = 'CANCEL') then
1640 --
1641 resultout := 'COMPLETE:';
1642 --
1643
1644 --
1645 -- TIMEOUT mode
1646 --
1647 elsif (funcmode = 'TIMEOUT') then
1648 resultout := 'COMPLETE:';
1649 end if;
1650
1651 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting WaitForApproval API');
1652
1653 exception
1654 when others then
1655 wf_core.context ('JTF_UM_WF_APPROVAL', 'WaitForApproval', itemtype, itemkey, to_char (actid), funcmode);
1656 raise;
1657 end WaitForApproval;
1658
1659 --
1660 -- post_notification
1661 -- DESCRIPTION
1662 -- Update the reg table when notification is transfered.
1663 -- IN
1664 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1665 -- itemkey - A string generated from the application object's primary key.
1666 -- actid - The function activity(instance id).
1667 -- funcmode - FORWARD/TRANSFER
1668 -- OUT
1669 -- Resultout - 'COMPLETE:'
1670 --
1671 procedure post_notification (itemtype in varchar2,
1672 itemkey in varchar2,
1673 actid in number,
1674 funcmode in varchar2,
1675 resultout out NOCOPY varchar2) is
1676 --
1677 requestType varchar2 (10);
1678 userId number;
1679 l_permission_flag number;
1680 l_return_status varchar2 (1);
1681
1682 cursor getUserID is
1683 select USER_ID
1684 from FND_USER
1685 where USER_NAME = WF_ENGINE.context_text;
1686 --
1687 begin
1688
1689 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering post_notification (' ||
1690 itemtype || ',' || itemkey || ',' || actid || ',' ||
1691 funcmode || ') API');
1692
1693 --
1694 -- FORWARD or TRANSFER mode
1695 --
1696 if (funcmode = 'FORWARD') or (funcmode = 'TRANSFER') then
1697 -- Get the new recipient_role. In our case, a new userID.
1698 open getUserID;
1699 fetch getUserID into userId;
1700 close getUserID;
1701 -- First verifty if the new userId has the valid permission
1702 -- Check if the user is the SYSADMIN
1703 JTF_AUTH_SECURITY_PKG.CHECK_PERMISSION (
1704 x_flag => l_permission_flag,
1705 x_return_status => l_return_status,
1706 p_user_name => WF_ENGINE.context_text,
1707 p_permission_name => 'JTF_REG_APPROVAL');
1708 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1709 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1710 end if;
1711
1712 if (l_permission_flag = 0) then
1713 -- Not a SYSADMIN, check if the user is a Primary User
1714 JTF_AUTH_SECURITY_PKG.CHECK_PERMISSION (
1715 x_flag => l_permission_flag,
1716 x_return_status => l_return_status,
1717 p_user_name => WF_ENGINE.context_text,
1718 p_permission_name => 'JTF_PRIMARY_USER_SUMMARY');
1719 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1720 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1721 end if;
1722 end if;
1723
1724 if (l_permission_flag = 0) then
1725 -- Not a Primary User, check if the user is an Owner
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_APPROVER');
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 -- Doesn't have the permission to be an approver.
1738 fnd_message.set_name ('JTF', 'JTF_APPROVAL_PERMISSION');
1739 raise_application_error (-20000, fnd_message.get);
1740 else
1741 -- Get the requestType.
1742 requestType := wf_engine.GetItemAttrText (
1743 itemtype => itemtype,
1744 itemkey => itemkey,
1745 aname => 'REQUEST_TYPE');
1746 --
1747 if (requestType = 'USERTYPE') then
1748 update JTF_UM_USERTYPE_REG
1749 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1750 LAST_UPDATE_DATE = sysdate,
1751 APPROVER_USER_ID = userId
1752 where USERTYPE_REG_ID = to_number(itemkey);
1753 elsif (requestType = 'ENROLLMENT') then
1754 update JTF_UM_SUBSCRIPTION_REG
1755 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1756 LAST_UPDATE_DATE = sysdate,
1757 APPROVER_USER_ID = userId
1758 where SUBSCRIPTION_REG_ID = to_number(itemkey);
1759 end if;
1760 resultout := 'COMPLETE';
1761 end if;
1762 end if;
1763
1764 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting post_notification API');
1765
1766 exception
1767 when others then
1768 wf_core.context ('JTF_UM_WF_APPROVAL', 'post_notification', itemtype, itemkey, to_char (actid), funcmode);
1769 raise;
1770 end post_notification;
1771
1772 --
1773 -- store_delegate_flag
1774 -- DESCRIPTION
1775 -- Store the delegate flag into the database
1776 -- IN
1777 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1778 -- itemkey - A string generated from the application object's primary key.
1779 -- actid - The function activity(instance id).
1780 -- funcmode - FORWARD/TRANSFER
1781 -- OUT
1782 -- Resultout - 'COMPLETE:'
1783 --
1784 procedure store_delegate_flag (itemtype in varchar2,
1785 itemkey in varchar2,
1786 actid in number,
1787 funcmode in varchar2,
1788 resultout out NOCOPY varchar2) is
1789
1790 l_bool_flag boolean;
1791 l_flag varchar2 (1);
1792 l_request_id number;
1793 l_requesterUserID number;
1794
1795 begin
1796
1797 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering store_delegate_flag (' ||
1798 itemtype || ',' || itemkey || ',' || actid || ',' ||
1799 funcmode || ') API');
1800
1801 --
1802 -- FORWARD or TRANSFER mode
1803 --
1804 if (funcmode = 'RUN') then
1805
1806 -- Save the Grant Delegation Flag
1807 l_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
1808 itemkey => itemkey,
1809 aname => 'DELEGATION_FLAG');
1810
1811 l_request_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1812 itemkey => itemkey,
1813 aname => 'REQUEST_ID');
1814
1815 l_requesterUserID := wf_engine.GetItemAttrNumber (
1816 itemtype => itemtype,
1817 itemkey => itemkey,
1818 aname => 'REQUESTER_USER_ID');
1819
1820 if (l_flag = 'Y') then
1821 l_bool_flag := true;
1822 else
1823 l_bool_flag := false;
1824 end if;
1825
1826 JTF_UM_SUBSCRIPTIONS_PKG.UPDATE_GRANT_DELEGATION_FLAG (
1827 P_SUBSCRIPTION_ID => l_request_id,
1828 P_USER_ID => l_requesterUserID,
1829 P_GRANT_DELEGATION_FLAG => l_bool_flag);
1830
1831 end if;
1832
1833 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting store_delegate_flag API');
1834
1835 exception
1836 when others then
1837 -- wf_core.context ('JTF_UM_WF_APPROVAL', 'store_delegate_flag', itemtype, itemkey, to_char (actid), funcmode);
1838 raise;
1839
1840 end store_delegate_flag;
1841
1842 --
1843 -- Procedure
1844 -- Do_Approve_Req
1845 --
1846 -- Description -
1847 -- Perform approve a request now
1848 --
1849 -- IN
1850 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1851 -- itemkey - A string generated from the application object's primary key.
1852 --
1853 procedure Do_Approve_Req (itemtype in varchar2,
1854 itemkey in varchar2) is
1855 --
1856 approverComment varchar2 (4000);
1857 requesterUserID number (15);
1858 requestID number (15);
1859 requestType varchar2 (10);
1860 requesterUsername varchar2 (100);
1861
1862 cursor enrollmentNoApprovalCursor is
1863 select SUBSCRIPTION_ID
1864 from JTF_UM_SUBSCRIPTION_REG
1865 where USER_ID = requesterUserID
1866 and EFFECTIVE_START_DATE <= sysdate
1867 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1868 and WF_ITEM_TYPE is null
1869 and STATUS_CODE = 'PENDING';
1870
1871 cursor enrollmentApprovalCursor is
1872 select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID
1873 from JTF_UM_SUBSCRIPTION_REG
1874 where USER_ID = requesterUserID
1875 and EFFECTIVE_START_DATE <= sysdate
1876 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
1877 and WF_ITEM_TYPE is not null
1878 and STATUS_CODE = 'PENDING';
1879
1880 cursor requesterUserNameCursor is
1881 select USER_NAME
1882 from FND_USER
1883 where USER_ID = requesterUserID
1884 and (nvl(END_DATE,sysdate) >= sysdate OR
1885 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
1886
1887 enrollAppRegRow enrollmentApprovalCursor%ROWTYPE;
1888 enrollNoAppRegRow enrollmentNoApprovalCursor%ROWTYPE;
1889
1890
1891
1892 begin
1893
1894 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Do_Approve_Req (' ||
1895 itemtype || ',' || itemkey || ') API');
1896
1897
1898 if itemtype is null then
1899 raise_application_error (-20000, 'itemtype is null while calling JTF_UM_WF_APPROVAL.Do_Approve_Req.');
1900 end if;
1901
1902 if itemkey is null then
1903 raise_application_error (-20000, 'itemkey is null while calling JTF_UM_WF_APPROVAL.Do_Approve_Req.');
1904 end if;
1905
1906 requesterUserID := wf_engine.GetItemAttrNumber (
1907 itemtype => itemtype,
1908 itemkey => itemkey,
1909 aname => 'REQUESTER_USER_ID');
1910
1911 requestType := wf_engine.GetItemAttrText (
1912 itemtype => itemtype,
1913 itemkey => itemkey,
1914 aname => 'REQUEST_TYPE');
1915
1916 requestID := wf_engine.GetItemAttrNumber (
1917 itemtype => itemtype,
1918 itemkey => itemkey,
1919 aname => 'REQUEST_ID');
1920
1921 approverComment := wf_engine.GetItemAttrText (
1922 itemtype => itemtype,
1923 itemkey => itemkey,
1924 aname => 'APPROVER_COMMENT');
1925
1926
1927 -- Get the username from userID
1928 requesterUsername := getRequesterUsername ( requesterUserID );
1929
1930 if (requestType = 'USERTYPE') then
1931
1932
1933 -- Call AssignUTCredential ()
1934 JTF_UM_USERTYPE_CREDENTIALS.ASSIGN_USERTYPE_CREDENTIALS (
1935 X_USER_NAME => requesterUsername,
1936 X_USER_ID => requesterUserID,
1937 X_USERTYPE_ID => requestID);
1938
1939
1940 -- Save the approver comment to LAST_APPROVER_COMMENT in
1941 -- the JTF_UM_USERTYPE_REG table.
1942 update JTF_UM_USERTYPE_REG
1943 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1944 LAST_UPDATE_DATE = sysdate,
1945 LAST_APPROVER_COMMENT = approverComment
1946 where USERTYPE_REG_ID = itemkey;
1947
1948
1949
1950 for enrollNoAppRegRow in enrollmentNoApprovalCursor loop
1951 -- Call AssignEnrollCredential
1952
1953 JTF_UM_ENROLLMENT_CREDENTIALS.ASSIGN_ENROLLMENT_CREDENTIALS (
1954 X_USER_NAME => requesterUsername,
1955 X_USER_ID => requesterUserID,
1956 X_SUBSCRIPTION_ID => enrollNoAppRegRow.SUBSCRIPTION_ID);
1957
1958 end loop;
1959
1960
1961 for enrollAppRegRow in enrollmentApprovalCursor loop
1962 -- Launch workflow created during registration.
1963 if Not hasWorkFlowStarted(enrollAppRegRow.WF_ITEM_TYPE,to_char(enrollAppRegRow.SUBSCRIPTION_REG_ID)) then
1964 wf_engine.StartProcess (itemtype => enrollAppRegRow.WF_ITEM_TYPE,
1965 itemkey => to_char(enrollAppRegRow.SUBSCRIPTION_REG_ID));
1966 end if;
1967
1968 end loop;
1969
1970 else
1971 -- requestType is 'ENROLLMENT'
1972
1973 -- Save the approver comment to LAST_APPROVER_COMMENT in
1974 -- the JTF_UM_USERTYPE_REG table.
1975 update JTF_UM_SUBSCRIPTION_REG
1976 set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1977 LAST_UPDATE_DATE = sysdate,
1978 LAST_APPROVER_COMMENT = approverComment
1979 where subscription_reg_id = itemkey;
1980
1981 -- Call Assign Enroll Credential
1982 JTF_UM_ENROLLMENT_CREDENTIALS.ASSIGN_ENROLLMENT_CREDENTIALS (
1983 X_USER_NAME => requesterUsername,
1984 X_USER_ID => requesterUserID,
1985 X_SUBSCRIPTION_ID => requestID);
1986
1987 end if;
1988
1989 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Do_Approve_Req API');
1990
1991 end Do_Approve_Req;
1992
1993 --
1994 -- Procedure
1995 -- Approve_Req
1996 --
1997 -- Description -
1998 -- Approve a request
1999 --
2000 -- IN
2001 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2002 -- itemkey - A string generated from the application object's primary key.
2003 -- actid - The function activity(instance id).
2004 -- funcmode - Run/Cancel/Timeout
2005 -- OUT
2006 -- resultout
2007 --
2008 procedure Approve_Req (itemtype in varchar2,
2009 itemkey in varchar2,
2010 actid in number,
2011 funcmode in varchar2,
2012 resultout out NOCOPY varchar2) is
2013 --
2014 begin
2015
2016 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Approve_Req (' ||
2017 itemtype || ',' || itemkey || ',' || actid || ',' ||
2018 funcmode || ') API');
2019
2020 --
2021 -- RUN mode - normal process execution
2022 --
2023 if (funcmode = 'RUN') then
2024 Do_Approve_Req (itemtype, itemkey);
2025
2026 --
2027 -- CANCEL mode
2028 --
2029 elsif (funcmode = 'CANCEL') then
2030 --
2031 -- Return process to run
2032 --
2033 resultout := 'COMPLETE:';
2034
2035 --
2036 -- TIMEOUT mode
2037 --
2038 elsif (funcmode = 'TIMEOUT') then
2039 resultout := 'COMPLETE:';
2040 end if;
2041
2042 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Approve_Req API');
2043
2044 exception
2045 when others then
2046 wf_core.context ('JTF_UM_WF_APPROVAL', 'Approve_Req', itemtype, itemkey, to_char(actid), funcmode);
2047 raise;
2048 end Approve_Req;
2049
2050 --
2051 -- Procedure
2052 -- Reject_Req
2053 --
2054 -- Description -
2055 -- Reject a request
2056 --
2057 -- IN
2058 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2059 -- itemkey - A string generated from the application object's primary key.
2060 -- actid - The function activity(instance id).
2061 -- funcmode - Run/Cancel
2062 -- OUT
2063 -- resultout
2064 --
2065 procedure Reject_Req (itemtype in varchar2,
2066 itemkey in varchar2,
2067 actid in number,
2068 funcmode in varchar2,
2069 resultout out NOCOPY varchar2) is
2070 --
2071 requestType varchar2 (10);
2072 requesterUserID number;
2073 approverComment varchar2 (4000);
2074
2075 l_parameter_list wf_parameter_list_t :=
2076 wf_parameter_list_t();
2077
2078 l_app_id number;
2079 l_usertype_reg_id number;
2080 l_usertype_key varchar2(30);
2081 requesterUsername varchar2 (100);
2082
2083 userStartDate date;
2084 userEndDate date;
2085 -- adding for Bug 4320347
2086 l_customer_id FND_USER.CUSTOMER_ID%TYPE;
2087 l_person_party_id FND_USER.PERSON_PARTY_ID%TYPE;
2088 -- end of changes for 4320347
2089 --
2090 cursor enrollmentsCursor is
2091 select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID
2092 from JTF_UM_SUBSCRIPTION_REG
2093 where USER_ID = requesterUserID
2094 and EFFECTIVE_START_DATE <= sysdate
2095 and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
2096 and STATUS_CODE = 'PENDING';
2097
2098
2099 -- cursor for populating event parameters in case of user type rejection
2100 cursor getRejectEventData is
2101 Select ut.APPLICATION_ID,ut.USERTYPE_KEY,reg.USERTYPE_REG_ID
2102 From JTF_UM_USERTYPES_B ut , JTF_UM_USERTYPE_REG reg
2103 where ut.USERTYPE_ID=reg.USERTYPE_ID and reg.USERTYPE_REG_ID=to_number(itemkey);
2104 -- and reg.EFFECTIVE_START_DATE <= sysdate
2105 -- and reg.EFFECTIVE_END_DATE= sysdate;
2106
2107
2108
2109 --
2110 begin
2111
2112 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Reject_Req (' ||
2113 itemtype || ',' || itemkey || ',' || actid || ',' ||
2114 funcmode || ') API');
2115
2116 --
2117 -- RUN mode - normal process execution
2118 --
2119 if (funcmode = 'RUN') then
2120 requestType := wf_engine.GetItemAttrText (
2121 itemtype => itemtype,
2122 itemkey => itemkey,
2123 aname => 'REQUEST_TYPE');
2124
2125 requesterUserID := wf_engine.GetItemAttrNumber (
2126 itemtype => itemtype,
2127 itemkey => itemkey,
2128 aname => 'REQUESTER_USER_ID');
2129
2130 approverComment := wf_engine.GetItemAttrText (
2131 itemtype => itemtype,
2132 itemkey => itemkey,
2133 aname => 'APPROVER_COMMENT');
2134
2135 if (requestType = 'USERTYPE') then
2136
2137 -- Revoke pending resp.
2138 JTF_UM_USERTYPE_CREDENTIALS.REVOKE_RESPONSIBILITY (
2139 X_USER_ID => requesterUserID,
2140 X_RESPONSIBILITY_KEY => 'JTF_PENDING_APPROVAL',
2141 X_APPLICATION_ID => 690);
2142
2143 -- End date and rejected Usertype Reg
2144 update JTF_UM_USERTYPE_REG
2145 set STATUS_CODE = 'REJECTED',
2146 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2147 LAST_UPDATE_DATE = sysdate,
2148 LAST_APPROVER_COMMENT = approverComment,
2149 EFFECTIVE_END_DATE = sysdate
2150 where USERTYPE_REG_ID = itemkey;
2151
2152 for enrollRegRow in enrollmentsCursor
2153 loop
2154 -- Abort all Workflow Enrollment
2155 --if (enrollRegRow.WF_ITEM_TYPE is not null) then
2156 -- wf_engine.AbortProcess (itemtype => enrollRegRow.WF_ITEM_TYPE,
2157 -- itemkey => enrollRegRow.SUBSCRIPTION_REG_ID);
2158 --end if;
2159
2160 -- Set STATUS_CODE in JTF_UM_SUBSCRIPTION_REG to 'USER_REJECTED'
2161 update JTF_UM_SUBSCRIPTION_REG
2162 set STATUS_CODE = 'USER_REJECTED',
2163 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2164 LAST_UPDATE_DATE = sysdate,
2165 EFFECTIVE_END_DATE = sysdate
2166 where SUBSCRIPTION_REG_ID = enrollRegRow.SUBSCRIPTION_REG_ID;
2167 end loop;
2168
2169 -- release the user name
2170 -- check if user is a pending user
2171 Select start_date,end_date,USER_NAME,customer_id,person_party_id
2172 Into userStartDate,userEndDate,requesterUsername,l_customer_id,l_person_party_id
2173 From FND_USER
2174 Where user_id = requesterUserID;
2175
2176 If to_char(userStartDate) = to_char(FND_API.G_MISS_DATE)
2177 And to_char(userEndDate) = to_char(FND_API.G_MISS_DATE) then
2178 -- release user
2179 FND_USER_PKG.RemovePendingUser(requesterUsername);
2180 End If;
2181
2182 -- Event handling
2183 -- Get the values for creation of parameters for the event
2184
2185 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Start Raising Event');
2186
2187 open getRejectEventData;
2188
2189 fetch getRejectEventData into l_app_id,l_usertype_key,l_usertype_reg_id;
2190
2191 close getRejectEventData;
2192 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Parameters '|| l_app_id ||' '||l_usertype_key|| ' '||l_usertype_reg_id );
2193
2194 -- create the parameter list
2195 wf_event.AddParameterToList(
2196 p_name => 'USERTYPEREG_ID',
2197 p_value=>to_char(l_usertype_reg_id),
2198 p_parameterlist=>l_parameter_list
2199 );
2200 wf_event.AddParameterToList(
2201 p_name => 'APPID',
2202 p_value=>to_char(l_app_id),
2203 p_parameterlist=>l_parameter_list
2204 );
2205 wf_event.AddParameterToList(
2206 p_name => 'USER_TYPE_KEY',
2207 p_value=>l_usertype_key,
2208 p_parameterlist=>l_parameter_list
2209 );
2210 --changes for 4320347
2211 wf_event.AddParameterToList(
2212 p_name => 'CUSTOMER_ID',
2213 p_value=>to_char(nvl(l_customer_id,-1)) ,
2214 p_parameterlist=>l_parameter_list
2215 );
2216 wf_event.AddParameterToList(
2217 p_name => 'PERSON_PARTY_ID',
2218 p_value=>to_char(nvl(l_person_party_id,-1)),
2219 p_parameterlist=>l_parameter_list
2220 );
2221 --end of changes for 4320347
2222
2223
2224 -- raise the event
2225 wf_event.raise(
2226 p_event_name =>'oracle.apps.jtf.um.rejectUTEvent',
2227 p_event_key =>requesterUserID ,
2228 p_parameters => l_parameter_list
2229 );
2230
2231 -- delete parameter list as it is no longer required
2232 l_parameter_list.DELETE;
2233
2234 -- end of event handling
2235
2236
2237
2238 else
2239 -- Set STATUS_CODE in JTF_UM_SUBSCRIPTION_REG to 'REJECTED'
2240 update JTF_UM_SUBSCRIPTION_REG
2241 set STATUS_CODE = 'REJECTED',
2242 LAST_APPROVER_COMMENT = approverComment,
2243 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2244 LAST_UPDATE_DATE = sysdate,
2245 EFFECTIVE_END_DATE = sysdate
2246 where SUBSCRIPTION_REG_ID = itemkey;
2247 end if;
2248 resultout := 'COMPLETE:';
2249
2250 --
2251 -- CANCEL mode
2252 --
2253 elsif (funcmode = 'CANCEL') then
2254 --
2255 -- Return process to run
2256 --
2257 resultout := 'COMPLETE:';
2258
2259 --
2260 -- TIMEOUT mode
2261 --
2262 elsif (funcmode = 'TIMEOUT') then
2263 resultout := 'COMPLETE:';
2264 end if;
2265
2266 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Reject_Req API');
2267
2268 exception
2269 when others then
2270 wf_core.context ('JTF_UM_WF_APPROVAL', 'Reject_Req', itemtype, itemkey, to_char(actid), funcmode);
2271 raise;
2272 end Reject_Req;
2273
2274 --
2275 -- Can_Delegate
2276 -- DESCRIPTION
2277 -- Check the enrollment request has the delegation role.
2278 -- IN
2279 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2280 -- itemkey - A string generated from the application object's primary key.
2281 -- actid - The function activity(instance id).
2282 -- funcmode - Run/Cancel/Timeout
2283 -- OUT
2284 -- Resultout - 'COMPLETE:Y' enrollment has the delegation role.
2285 -- - 'COMPLETE:N' enrollment doesn't has the delegation role.
2286 --
2287 procedure Can_Delegate (itemtype in varchar2,
2288 itemkey in varchar2,
2289 actid in number,
2290 funcmode in varchar2,
2291 resultout out NOCOPY varchar2) is
2292 --
2293 l_approver_userID number;
2294 l_approver_username varchar (100);
2295 l_result varchar (10);
2296
2297 cursor getFNDUserID is
2298 select user_id
2299 from fnd_user
2300 where (nvl (end_date, sysdate + 1) > sysdate
2301 OR to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))
2302
2303 and user_name = l_approver_username;
2304 --
2305 begin
2306
2307 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Can_Delegate (' ||
2308 itemtype || ',' || itemkey || ',' || actid || ',' ||
2309 funcmode || ') API');
2310
2311 --
2312 -- RUN mode - normal process execution
2313 --
2314
2315 -- Default is No delegation
2316 resultout := 'COMPLETE:N';
2317
2318 if (funcmode = 'RUN') then
2319 -- Get the Approver User ID
2320 l_approver_username := wf_engine.GetItemAttrText (
2321 itemtype => itemtype,
2322 itemkey => itemkey,
2323 aname => 'APPROVER_USERNAME');
2324
2325 open getFNDUserID;
2326 fetch getFNDUserID into l_approver_userID;
2327 if (getFNDUserID%notfound) then
2328 close getFNDUserID;
2329 raise_application_error (-20000, 'userid not found('||l_approver_username||')');
2330 end if;
2331 close getFNDUserID;
2332
2333 -- Check if this enrollment has delegation
2334 JTF_UM_WF_DELEGATION_PVT.GET_CHECKBOX_STATUS (
2335 P_REG_ID => to_number (itemkey),
2336 P_USER_ID => l_approver_userID,
2337 X_RESULT => l_result);
2338
2339 if (l_result = JTF_UM_WF_DELEGATION_PVT.CHECKED_UPDATE) then
2340 -- Grant Delegation Flag is set to Yes.
2341 wf_engine.SetItemAttrText (itemtype => itemtype,
2342 itemkey => itemkey,
2343 aname => 'DELEGATION_FLAG',
2344 avalue => 'Y');
2345 resultout := 'COMPLETE:Y';
2346
2347 elsif (l_result = JTF_UM_WF_DELEGATION_PVT.NOT_CHECKED_UPDATE) then
2348 -- Grant Delegation Flag is set to No.
2349 wf_engine.SetItemAttrText (itemtype => itemtype,
2350 itemkey => itemkey,
2351 aname => 'DELEGATION_FLAG',
2352 avalue => 'N');
2353 resultout := 'COMPLETE:Y';
2354
2355 end if;
2356 end if;
2357
2358 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Can_Delegate API');
2359
2360 exception
2361 when others then
2362 wf_core.context ('JTF_UM_WF_APPROVAL', 'Can_Delegate', itemtype, itemkey, to_char (actid), funcmode, 'l_approver_userID='||to_char (l_approver_userID));
2363 raise;
2364 end Can_Delegate;
2365
2366 --
2367 -- CAN_ENROLLMENT_DELEGATE
2368 -- DESCRIPTION
2369 -- Check the enrollment request if it is delegation or
2370 -- delegation and self-service.
2371 -- IN
2372 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2373 -- itemkey - A string generated from the application object's primary key.
2374 -- actid - The function activity(instance id).
2375 -- funcmode - Run/Cancel/Timeout
2376 -- OUT
2377 -- Resultout - 'COMPLETE:Y' enrollment is a delegation or delegation
2378 -- and self-service.
2379 -- - 'COMPLETE:N' enrollment is a implicit or self-service.
2380 --
2381 procedure Can_Enrollment_Delegate (itemtype in varchar2,
2382 itemkey in varchar2,
2383 actid in number,
2384 funcmode in varchar2,
2385 resultout out NOCOPY varchar2) is
2386
2387 l_procedure_name CONSTANT varchar2(23) := 'can_enrollment_delegate';
2388 l_request_id number;
2389 l_requester_usertype_id number;
2390 l_result boolean;
2391
2392 begin
2393
2394 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (p_module => G_MODULE,
2395 p_message => l_procedure_name);
2396
2397 if l_is_debug_parameter_on then
2398 JTF_DEBUG_PUB.LOG_PARAMETERS (p_module => G_MODULE,
2399 p_message => itemtype || ',' || itemkey ||
2400 ',' || actid || ',' || funcmode || ') ');
2401 end if;
2402
2403 --
2404 -- RUN mode - normal process execution
2405 --
2406
2407 if (funcmode = 'RUN') then
2408 -- Get the Requester Usertype ID
2409 l_requester_usertype_id := wf_engine.GetItemAttrNumber (
2410 itemtype => itemtype,
2411 itemkey => itemkey,
2412 aname => 'REQUESTER_USERTYPE_ID');
2413
2414 -- Get the Request ID
2415 l_request_id := wf_engine.GetItemAttrNumber(
2416 itemtype => itemtype,
2417 itemkey => itemkey,
2418 aname => 'REQUEST_ID');
2419
2420 JTF_UM_WF_DELEGATION_PVT.CAN_ENROLLMENT_DELEGATE (
2421 p_subscription_id => l_request_id,
2422 p_usertype_id => l_requester_usertype_id,
2423 x_result => l_result);
2424
2425 if (l_result) then
2426 resultout := 'COMPLETE:Y';
2427 else
2428 resultout := 'COMPLETE:N';
2429 end if;
2430
2431 end if;
2432
2433 JTF_DEBUG_PUB.LOG_EXITING_METHOD (p_module => G_MODULE,
2434 p_message => l_procedure_name);
2435
2436 exception
2437 when others then
2438 wf_core.context ('JTF_UM_WF_APPROVAL', 'CAN_ENROLLMENT_DELEGATE', itemtype, itemkey, to_char (actid), funcmode);
2439 raise;
2440 end can_enrollment_delegate;
2441
2442 --
2443 -- UNIVERSAL_APPROVERS_EXISTS
2444 -- DESCRIPTION
2445 -- Check if the current approver is universal approvers role.
2446 -- IN
2447 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2448 -- itemkey - A string generated from the application object's primary key.
2449 -- actid - The function activity(instance id).
2450 -- funcmode - Run/Cancel/Timeout
2451 -- OUT
2452 -- Resultout - 'COMPLETE:Y' current approver is universal approvers role.
2453 -- - 'COMPLETE:N' current approver is not universal approvers
2454 -- role.
2455 --
2456 procedure universal_approvers_exists (itemtype in varchar2,
2457 itemkey in varchar2,
2458 actid in number,
2459 funcmode in varchar2,
2460 resultout out NOCOPY varchar2) is
2461
2462 l_appl_id JTF_UM_USERTYPES_B.APPLICATION_ID%TYPE;
2463 l_approver_username fnd_user.user_name%type;
2464 l_org_name hz_parties.party_name%type;
2465 l_org_number hz_parties.party_number%type;
2466 l_primary_user_role fnd_profile_option_values.profile_option_value%type;
2467 l_procedure_name CONSTANT varchar2(26) := 'universal_approvers_exists';
2468 l_request_id number;
2469 l_request_type varchar2 (10);
2470 l_requester_user_id fnd_user.user_id%type;
2471 l_universal_approvers fnd_profile_option_values.profile_option_value%type;
2472
2473 begin
2474
2475 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (p_module => G_MODULE,
2476 p_message => l_procedure_name);
2477
2478 if l_is_debug_parameter_on then
2479 JTF_DEBUG_PUB.LOG_PARAMETERS (p_module => G_MODULE,
2480 p_message => itemtype || ',' || itemkey ||
2481 ',' || actid || ',' || funcmode || ') ');
2482 end if;
2483
2484 --
2485 -- RUN mode - normal process execution
2486 --
2487
2488 if (funcmode = 'RUN') then
2489 -- Get the Current Approver Username
2490 l_approver_username := wf_engine.GetItemAttrText (
2491 itemtype => itemtype,
2492 itemkey => itemkey,
2493 aname => 'APPROVER_USERNAME');
2494
2495 -- Get the profile option of Universal Approvers
2496 l_request_type := wf_engine.GetItemAttrText (
2497 itemtype => itemtype,
2498 itemkey => itemkey,
2499 aname => 'REQUEST_TYPE');
2500
2501 l_request_id := wf_engine.GetItemAttrNumber (
2502 itemtype => itemtype,
2503 itemkey => itemkey,
2504 aname => 'REQUEST_ID');
2505
2506 l_requester_user_id := wf_engine.GetItemAttrNumber (
2507 itemtype => itemtype,
2508 itemkey => itemkey,
2509 aname => 'REQUESTER_USER_ID');
2510
2511 -- get the organization number
2512 get_org_info (p_user_id => l_requester_user_id,
2513 x_org_name => l_org_name,
2514 x_org_number => l_org_number);
2515
2516 -- the name of the role
2517 l_primary_user_role := g_adhoc_role_name_prefix || l_org_number;
2518
2519 if (l_approver_username = l_primary_user_role) then
2520 -- the current approver is a Universal Approver
2521 resultout := 'COMPLETE:Y';
2522 else
2523 resultout := 'COMPLETE:N';
2524 end if;
2525
2526 end if;
2527
2528 JTF_DEBUG_PUB.LOG_EXITING_METHOD (p_module => G_MODULE,
2529 p_message => l_procedure_name);
2530
2531 exception
2532 when others then
2533 wf_core.context ('JTF_UM_WF_APPROVAL', l_procedure_name, itemtype, itemkey, to_char (actid), funcmode);
2534 raise;
2535 end universal_approvers_exists;
2536
2537 --
2538 -- CHECK_EMAIL_NOTIFI_TYPE
2539 -- DESCRIPTION
2540 -- Check which email we will send to this requester.
2541 -- IN
2542 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2543 -- itemkey - A string generated from the application object's primary key.
2544 -- actid - The function activity(instance id).
2545 -- funcmode - Run/Cancel/Timeout
2546 -- OUT
2547 -- Resultout - 'COMPLETE:NO_NOTIFICATION' if email should not be sent.
2548 -- - 'COMPLETE:PRIMARY_USER' if primary user email should be sent.
2549 -- - 'COMPLETE:BUSINESS_USER' if business user email should be sent.
2550 -- - 'COMPLETE:INDIVIDUAL_USER' if individual user email should be sent.
2551 -- - 'COMPLETE:OTHER_USER' if other user email should be sent.
2552 -- - 'COMPLETE:ENROLLMENT' if enrollment email should be sent.
2553 --
2554 procedure CHECK_EMAIL_NOTIFI_TYPE (itemtype in varchar2,
2555 itemkey in varchar2,
2556 actid in number,
2557 funcmode in varchar2,
2558 resultout out NOCOPY varchar2) is
2559 --
2560 requestType varchar2 (10);
2561 requestName varchar2 (1000);
2562 usertypeKey varchar2 (30);
2563 emailFlag varchar2 (1);
2564 usertypeID number;
2565 MISSING_USERTYPE_INFO exception;
2566 --
2567 cursor getUsertypeInfo is
2568 select USERTYPE_KEY, EMAIL_NOTIFICATION_FLAG
2569 from JTF_UM_USERTYPES_B
2570 where USERTYPE_ID = usertypeID;
2571
2572 begin
2573
2574 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering CHECK_EMAIL_NOTIFI_TYPE (' ||
2575 itemtype || ',' || itemkey || ',' || actid || ',' ||
2576 funcmode || ') API');
2577
2578 --
2579 -- RUN mode - normal process execution
2580 --
2581
2582 if (funcmode = 'RUN') then
2583 usertypeID := wf_engine.GetItemAttrNumber (
2584 itemtype => itemtype,
2585 itemkey => itemkey,
2586 aname => 'REQUESTER_USERTYPE_ID');
2587
2588 open getUsertypeInfo;
2589 fetch getUsertypeInfo into usertypeKey, emailFlag;
2590 if (getUsertypeInfo%notfound) then
2591 close getUsertypeInfo;
2592 raise MISSING_USERTYPE_INFO;
2593 end if;
2594 close getUsertypeInfo;
2595
2596 if (emailFlag = 'N') then
2597 resultout := 'COMPLETE:NO_NOTIFICATION';
2598
2599 else
2600 -- We will send email out.
2601
2602 requestType := wf_engine.GetItemAttrText (
2603 itemtype => itemtype,
2604 itemkey => itemkey,
2605 aname => 'REQUEST_TYPE');
2606
2607 if (requestType = 'ENROLLMENT') then
2608 -- Send enrollment email
2609 resultout := 'COMPLETE:ENROLLMENT';
2610 else
2611 -- Send usertype email, but we need to know what kind
2612 -- of usertype email are we sending.
2613
2614 if (usertypeKey = 'PRIMARYUSER') or (usertypeKey = 'PRIMARYUSERNEW') then
2615 resultout := 'COMPLETE:PRIMARY_USER';
2616 elsif (usertypeKey = 'BUSINESSUSER') then
2617 resultout := 'COMPLETE:BUSINESS_USER';
2618 elsif (usertypeKey = 'INDIVIDUALUSER') then
2619 resultout := 'COMPLETE:INDIVIDUAL_USER';
2620 else
2621 resultout := 'COMPLETE:OTHER_USER';
2622 end if;
2623 end if;
2624 end if;
2625
2626 --
2627 -- CANCEL mode
2628 --
2629 elsif (funcmode = 'CANCEL') then
2630 --
2631 resultout := 'COMPLETE:';
2632 --
2633
2634 --
2635 -- TIMEOUT mode
2636 --
2637 elsif (funcmode = 'TIMEOUT') then
2638 resultout := 'COMPLETE:';
2639 end if;
2640
2641 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting CHECK_EMAIL_NOTIFI_TYPE API');
2642
2643 exception
2644 when MISSING_USERTYPE_INFO then
2645 wf_core.context ('JTF_UM_WF_APPROVAL', 'CHECK_EMAIL_NOTIFI_TYPE', itemtype, itemkey, to_char (actid), funcmode, 'Usertype info is missing');
2646 raise;
2647 when others then
2648 wf_core.context ('JTF_UM_WF_APPROVAL', 'CHECK_EMAIL_NOTIFI_TYPE', itemtype, itemkey, to_char (actid), funcmode);
2649 raise;
2650 end CHECK_EMAIL_NOTIFI_TYPE;
2651
2652 --
2653 -- CompleteApprovalActivity
2654 -- DESCRIPTION
2655 -- Complete the blocking activity
2656 -- This procedure will determine which request type this approval is for
2657 -- IN
2658 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2659 -- itemkey - A string generated from the application object's primary key.
2660 -- resultCode - 'APPROVED' or 'REJECTED'
2661 -- comment - Approver's comment
2662 -- delegationFlag - 'Y' = Grant Delegation Flag
2663 -- 'N' = Do not grant delegation flag
2664 -- null = No delegation flag
2665 -- lastUpdateDate - Last Update Date of the request record
2666 --
2667 procedure CompleteApprovalActivity (itemtype in varchar2,
2668 itemkey in varchar2,
2669 resultCode in varchar2,
2670 approverComment in varchar2,
2671 delegationFlag in varchar2 := null,
2672 lastUpdateDate in varchar2 := null) is
2673
2674 l_last_update_date varchar2 (14);
2675 request_type varchar2 (10);
2676 org_status varchar2(1);
2677
2678
2679
2680 UNKNOWN_REQUEST_TYPE exception;
2681
2682 cursor getLUDFromUserReg is
2683 select to_char (last_update_date, 'mmddyyyyhh24miss')
2684 from jtf_um_usertype_reg
2685 where usertype_reg_id = to_number (itemkey);
2686
2687 cursor getLUDFromEnrollReg is
2688 select to_char (last_update_date, 'mmddyyyyhh24miss')
2689 from jtf_um_subscription_reg
2690 where subscription_reg_id = to_number (itemkey);
2691
2692 -- For bug fix 3894853
2693 cursor getOrgDetail is
2694 SELECT party.status FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
2695 WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
2696 AND PREL.PARTY_ID = (select fnd.customer_id
2697 from jtf_um_usertype_reg reg , fnd_user fnd
2698 where usertype_reg_id = to_number(itemkey)
2699 and reg.user_id=fnd.USER_ID
2700 )
2701 AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2702 AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2703 AND PREL.START_DATE < SYSDATE
2704 AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
2705 AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');
2706
2707
2708 -- end Bug fix 3894853
2709
2710
2711 begin
2712
2713 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering CompleteApprovalActivity (' ||
2714 itemtype || ',' || itemkey || ',' || resultCode || ',' ||
2715 approverComment || ') API');
2716
2717 -- check on the input
2718 if itemtype is null then
2719 raise_application_error (-20000, 'itemtype is null while calling JTF_UM_WF_APPROVAL.CompleteApprovalActivity.');
2720 end if;
2721
2722 if itemkey is null then
2723 raise_application_error (-20000, 'itemkey is null while calling JTF_UM_WF_APPROVAL.CompleteApprovalActivity.');
2724 end if;
2725
2726 if resultCode is null then
2727 raise_application_error (-20000, 'resultCode is null while calling JTF_UM_WF_APPROVAL.CompleteApprovalActivity.');
2728 end if;
2729
2730 -- Check Request type
2731 request_type := wf_engine.GetItemAttrText (
2732 itemtype => itemtype,
2733 itemkey => itemkey,
2734 aname => 'REQUEST_TYPE');
2735
2736 if (request_type = 'USERTYPE') then
2737
2738 -- bug fix 3894853
2739 -- check if there is an Organization associated with this usertype we are trying to approve
2740 -- also if that Organization Status is INACTIVE then raise an error
2741
2742 open getOrgDetail;
2743 fetch getOrgDetail into org_status;
2744 close getOrgDetail;
2745
2746 if org_status is not null and org_status <> 'A' then
2747 raise_application_error (-20001, ' ORG_INACTIVE ' );
2748 end if;
2749
2750
2751 -- end of bug fix 3894853
2752
2753 if lastUpdateDate is not null then
2754
2755 open getLUDFromUserReg;
2756 fetch getLUDFromUserReg into l_last_update_date;
2757 close getLUDFromUserReg;
2758
2759 if (lastUpdateDate <> l_last_update_date) then
2760 -- not the same request
2761 raise_application_error (-20001, 'The last update date from the input parameter and the last update date stored in the database is different.');
2762 end if;
2763 end if;
2764
2765 Do_Complete_Approval_Activity (p_itemtype => itemtype,
2766 p_itemkey => itemkey,
2767 p_resultCode => resultCode,
2768 p_approverComment => approverComment,
2769 p_act1 => 'NTF_BLOCK',
2770 p_act2 => 'REMINDER_NTF_BLOCK',
2771 p_act3 => 'FAIL_ESCLATE_NTF_BLOC');
2772
2773 elsif (request_type = 'ENROLLMENT') then
2774
2775 if lastUpdateDate is not null then
2776
2777 open getLUDFromEnrollReg;
2778 fetch getLUDFromEnrollReg into l_last_update_date;
2779 close getLUDFromEnrollReg;
2780
2781 if (lastUpdateDate <> l_last_update_date) then
2782 -- not the same request
2783 raise_application_error (-20001, 'The last update date from the input parameter and the last update date stored in the database is different.');
2784 end if;
2785 end if;
2786
2787 -- Set the delegation flag
2788 if (delegationFlag is not null) then
2789 wf_engine.SetItemAttrText (itemtype => itemtype,
2790 itemkey => itemkey,
2791 aname => 'DELEGATION_FLAG',
2792 avalue => delegationFlag);
2793 end if;
2794
2795 Do_Complete_Approval_Activity (p_itemtype => itemtype,
2796 p_itemkey => itemkey,
2797 p_resultCode => resultCode,
2798 p_approverComment => approverComment,
2799 p_act1 => 'NTF_APPROVAL_ENROLL_REQUIRED',
2800 p_act2 => 'NTF_REMIND_ENROLL_REQUIRED',
2801 p_act3 => 'NTF_FAIL_ESCALATE_ENROLL_REQ',
2802 p_act4 => 'NTF_APPROVAL_ENROLL_DELE_REQ',
2803 p_act5 => 'NTF_REMIND_ENROLL_DELE_REQ',
2804 p_act6 => 'NTF_FAIL_ESCA_ENROLL_DELE_REQ');
2805
2806 else
2807 raise UNKNOWN_REQUEST_TYPE;
2808 end if;
2809
2810 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting CompleteApprovalActivity API');
2811
2812 exception
2813 when UNKNOWN_REQUEST_TYPE then
2814 wf_core.context ('JTF_UM_WF_APPROVAL', 'CompleteApprovalActivity',
2815 itemtype, itemkey, resultCode, approverComment);
2816 raise;
2817 when others then
2818 wf_core.context ('JTF_UM_WF_APPROVAL', 'CompleteApprovalActivity', itemtype, itemkey, resultCode, approverComment);
2819 raise;
2820
2821 end CompleteApprovalActivity;
2822
2823 --
2824 -- Do_Complete_Approval_Activity
2825 -- DESCRIPTION
2826 -- Complete the blocking activity now
2827 -- IN
2828 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
2829 -- p_itemkey - A string generated from the application object's
2830 -- primary key.
2831 -- p_resultCode - 'APPROVED' or 'REJECTED'
2832 -- p_wf_resultCode - 'APPROVED' or 'REJECTED' but if approval is Usertype,
2833 -- this will be 'null'.
2834 -- p_approverComment - Approver's comment
2835 -- p_act1 - First Activity
2836 -- p_act2 - Second Activity
2837 -- p_act3 - Third Activity
2838 -- p_act4 - Fourth Activity
2839 -- p_act5 - Fifth Activity
2840 -- p_act6 - Sixth Activity
2841 --
2842 procedure Do_Complete_Approval_Activity (p_itemtype in varchar2,
2843 p_itemkey in varchar2,
2844 p_resultCode in varchar2,
2845 p_wf_resultCode in varchar2,
2846 p_approverComment in varchar2,
2847 p_act1 in varchar2 := null,
2848 p_act2 in varchar2 := null,
2849 p_act3 in varchar2 := null,
2850 p_act4 in varchar2 := null,
2851 p_act5 in varchar2 := null,
2852 p_act6 in varchar2 := null)
2853
2854 is
2855
2856 begin
2857
2858 Do_Complete_Approval_Activity (p_itemtype => p_itemtype,
2859 p_itemkey => p_itemkey,
2860 p_resultCode => p_resultCode,
2861 p_approverComment => p_approverComment,
2862 p_act1 => p_act1,
2863 p_act2 => p_act2,
2864 p_act3 => p_act3,
2865 p_act4 => p_act4,
2866 p_act5 => p_act5,
2867 p_act6 => p_act6);
2868
2869 end Do_Complete_Approval_Activity;
2870
2871 --
2872 -- Do_Complete_Approval_Activity
2873 -- DESCRIPTION
2874 -- Complete the blocking activity now
2875 -- IN
2876 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
2877 -- p_itemkey - A string generated from the application object's
2878 -- primary key.
2879 -- p_resultCode - 'APPROVED' or 'REJECTED'
2880 -- p_approverComment - Approver's comment
2881 -- p_act1 - First Activity
2882 -- p_act2 - Second Activity
2883 -- p_act3 - Third Activity
2884 -- p_act4 - Fourth Activity
2885 -- p_act5 - Fifth Activity
2886 -- p_act6 - Sixth Activity
2887 --
2888 procedure Do_Complete_Approval_Activity (p_itemtype in varchar2,
2889 p_itemkey in varchar2,
2890 p_resultCode in varchar2,
2891 p_approverComment in varchar2,
2892 p_act1 in varchar2 := null,
2893 p_act2 in varchar2 := null,
2894 p_act3 in varchar2 := null,
2895 p_act4 in varchar2 := null,
2896 p_act5 in varchar2 := null,
2897 p_act6 in varchar2 := null)
2898
2899 IS
2900
2901 OK boolean := FALSE;
2902 act varchar2 (30);
2903 requestType varchar2 (10);
2904 wf_resultCode varchar2 (8) := p_resultCode;
2905
2906 begin
2907
2908 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
2909 'Entering Do_Complete_Approval_Activity (' || p_itemtype ||
2910 ',' || p_itemkey || ',' || p_resultCode || ',' || p_approverComment ||
2911 ',' || p_act1 || ',' || p_act2 || ',' || p_act3 || ',' || p_act4 ||
2912 ',' || p_act5 || ',' || p_act6 || ') API');
2913
2914 wf_engine.SetItemAttrText (itemtype => p_itemtype,
2915 itemkey => p_itemkey,
2916 aname => 'APPROVER_COMMENT',
2917 avalue => p_approverComment);
2918
2919 wf_engine.SetItemAttrText (itemtype => p_itemtype,
2920 itemkey => p_itemkey,
2921 aname => 'REQUEST_RESULT',
2922 avalue => p_resultCode);
2923
2924 -- Find out what kind of request type is it
2925 requestType := wf_engine.GetItemAttrText (itemtype => p_itemtype,
2926 itemkey => p_itemkey,
2927 aname => 'REQUEST_TYPE');
2928
2929 if (p_act1 is not null) then
2930 begin
2931 act := p_act1;
2932 wf_engine.BeginActivity (itemtype => p_itemtype,
2933 itemkey => p_itemkey,
2934 activity => act);
2935 OK := TRUE;
2936 exception
2937 when others then
2938 wf_core.clear;
2939 end;
2940 end if;
2941
2942 if (not OK) and (p_act2 is not null) then
2943 begin
2944 act := p_act2;
2945 wf_engine.BeginActivity (itemtype => p_itemtype,
2946 itemkey => p_itemkey,
2947 activity => act);
2948 OK := TRUE;
2949 exception
2950 when others then
2951 wf_core.clear;
2952 end;
2953 end if;
2954
2955 if (not OK) and (p_act3 is not null) then
2956 begin
2957 act := p_act3;
2958 wf_engine.BeginActivity (itemtype => p_itemtype,
2959 itemkey => p_itemkey,
2960 activity => act);
2961 OK := TRUE;
2962 exception
2963 when others then
2964 wf_core.clear;
2965 end;
2966 end if;
2967
2968 if (not OK) and (p_act4 is not null) then
2969 begin
2970 act := p_act4;
2971 wf_engine.BeginActivity (itemtype => p_itemtype,
2972 itemkey => p_itemkey,
2973 activity => act);
2974 OK := TRUE;
2975 exception
2976 when others then
2977 wf_core.clear;
2978 end;
2979 end if;
2980
2981 if (not OK) and (p_act5 is not null) then
2982 begin
2983 act := p_act5;
2984 wf_engine.BeginActivity (itemtype => p_itemtype,
2985 itemkey => p_itemkey,
2986 activity => act);
2987 OK := TRUE;
2988 exception
2989 when others then
2990 wf_core.clear;
2991 end;
2992 end if;
2993
2994 if (not OK) and (p_act6 is not null) then
2995 begin
2996 act := p_act6;
2997 wf_engine.BeginActivity (itemtype => p_itemtype,
2998 itemkey => p_itemkey,
2999 activity => act);
3000 OK := TRUE;
3001 exception
3002 when others then
3003 wf_core.clear;
3004 end;
3005 end if;
3006
3007 if OK then
3008
3009 wf_engine.CompleteActivity (p_itemtype, p_itemkey, act, wf_resultCode);
3010
3011 else
3012
3013 raise_application_error (-20000, 'No Activity Found Failed at JTF_UM_WF_APPROVAL.Do_Complete_Approval_Activity ('
3014 ||p_itemtype||','||p_itemkey||','||p_resultCode||','||p_approverComment
3015 ||','||p_act1||','||p_act2||','||p_act3||','||p_act4||','||p_act5||','||
3016 p_act6||')');
3017
3018 end if;
3019
3020 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Do_Complete_Approval_Activity API');
3021
3022 end Do_Complete_Approval_Activity;
3023
3024 --
3025 -- abort_process
3026 -- DESCRIPTION
3027 -- Abort the Workflow Process with status is ACTIVE, ERROR, or SUSPENDED
3028 -- IN
3029 -- p_itemtype - A valid item type from (WF_ITEM_TYPES table).
3030 -- p_itemkey - A string generated from the application object's
3031 -- primary key.
3032 --
3033 procedure abort_process (p_itemtype in varchar2,
3034 p_itemkey in varchar2)
3035
3036 IS
3037
3038 result varchar2 (10);
3039 status varchar2 (10);
3040 begin
3041
3042 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering abort_process (' ||
3043 p_itemtype || ',' || p_itemkey || ') API');
3044
3045 if p_itemtype is null then
3046 raise_application_error (-20000, 'itemtype is null while calling JTF_UM_WF_APPROVAL.abort_process.');
3047 end if;
3048
3049 if p_itemtype is null then
3050 raise_application_error (-20000, 'itemkey is null while calling JTF_UM_WF_APPROVAL.abort_process.');
3051 end if;
3052
3053 wf_engine.ItemStatus (p_itemtype, p_itemkey, status, result);
3054 if (status <> 'COMPLETE') then
3055 -- need to cancel any open notification
3056 cancel_notification (p_itemtype, p_itemkey);
3057 -- now call the workflow abort process
3058 wf_engine.abortprocess (itemtype => p_itemtype,
3059 itemkey => p_itemkey);
3060 end if;
3061
3062 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting abort_process API');
3063
3064 end abort_process;
3065
3066 procedure usertype_approval_changed (p_usertype_id in number,
3067 p_new_approval_id in number,
3068 p_old_approval_id in number) is
3069
3070 cursor usertype_reg is
3071 select usertype_reg_id,user_id
3072 from jtf_um_usertype_reg
3073 where usertype_id = p_usertype_id
3074 and status_code = 'PENDING'
3075 and nvl (effective_end_date, sysdate + 1) > sysdate;
3076
3077 p_usertype_reg_id number;
3078 p_user_id number;
3079
3080 cursor find_old_item_type is
3081 select utreg.wf_item_type
3082 from jtf_um_usertype_reg utreg, jtf_um_usertypes_b ut
3083 where utreg.usertype_id = p_usertype_id
3084 and utreg.usertype_id = ut.usertype_id
3085 and utreg.status_code = 'PENDING'
3086 and nvl (utreg.effective_end_date, sysdate + 1) > sysdate;
3087
3088 p_wf_old_item_type varchar2(8);
3089
3090 cursor find_new_item_type is
3091 select wf_item_type
3092 from jtf_um_approvals_b
3093 where approval_id = p_new_approval_id;
3094
3095 p_wf_new_item_type varchar2(8);
3096 p_new_item_key number;
3097
3098 begin
3099
3100 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3101 'Entering usertype_approval_changed (' || p_usertype_id || ',' ||
3102 p_new_approval_id || ',' || p_old_approval_id || ') API');
3103
3104
3105 -- Find out the old item_type to abort Workflow process
3106 open find_old_item_type;
3107 fetch find_old_item_type into p_wf_old_item_type;
3108 close find_old_item_type;
3109
3110
3111 -- Find out the new item_type to create Workflow process
3112 open find_new_item_type;
3113 fetch find_new_item_type into p_wf_new_item_type;
3114 close find_new_item_type;
3115
3116
3117
3118
3119 open usertype_reg;
3120 loop
3121 fetch usertype_reg into p_usertype_reg_id,p_user_id;
3122 exit when usertype_reg%NOTFOUND;
3123
3124
3125 -- abort WF Process first
3126 abort_process (p_wf_old_item_type, p_usertype_reg_id);
3127
3128
3129 if p_wf_new_item_type is null then
3130
3131
3132 -- approve the approval request
3133 do_approve_req(itemtype => p_wf_old_item_type,
3134 itemkey => p_usertype_reg_id);
3135
3136 else
3137
3138
3139 -- end date the old record in JTF_UM_USERTYPE_REG table
3140 update JTF_UM_USERTYPE_REG set effective_end_date = sysdate,
3141 last_update_date = sysdate, last_updated_by = FND_GLOBAL.USER_ID
3142 where usertype_reg_id = p_usertype_reg_id;
3143
3144
3145 -- create record in JTF_UM_USERTYPE_REG table
3146 JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW (
3147 X_USERTYPE_ID => p_usertype_id,
3148 X_LAST_APPROVER_COMMENT => null,
3149 X_APPROVER_USER_ID => null,
3150 X_EFFECTIVE_END_DATE => null,
3151 X_WF_ITEM_TYPE => p_wf_new_item_type,
3152 X_EFFECTIVE_START_DATE => sysdate,
3153 X_USERTYPE_REG_ID => p_new_item_key,
3154 X_USER_ID => p_user_id,
3155 X_STATUS_CODE => 'PENDING',
3156 X_CREATION_DATE => sysdate,
3157 X_CREATED_BY => FND_GLOBAL.USER_ID,
3158 X_LAST_UPDATE_DATE => sysdate,
3159 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3160 X_LAST_UPDATE_LOGIN => null);
3161
3162 -- Create WF process
3163 CreateProcess (ownerUserId => FND_GLOBAL.USER_ID,
3164 requestType => 'USERTYPE',
3165 requestID => p_usertype_id,
3166 requesterUserID => p_user_id,
3167 requestRegID => p_new_item_key);
3168
3169 -- Launch WF process
3170 LaunchProcess (requestType => 'USERTYPE',
3171 requestRegID => p_new_item_key);
3172
3173 end if;
3174
3175 end loop;
3176 close usertype_reg;
3177
3178 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting usertype_approval_changed API');
3179
3180 end usertype_approval_changed;
3181
3182 procedure usertype_approval_changed (p_usertype_id in number,
3183 p_new_approval_id in number,
3184 p_old_approval_id in number,
3185 p_org_party_id in number) is
3186
3187 cursor usertype_reg is
3188 select utreg.usertype_reg_id, utreg.user_id
3189 from jtf_um_usertype_reg utreg, fnd_user fu, hz_relationships hzr
3190 where utreg.usertype_id = p_usertype_id
3191 and utreg.status_code = 'PENDING'
3192 and nvl (utreg.effective_end_date, sysdate + 1) > sysdate
3193 and utreg.user_id = fu.user_id
3194 and fu.customer_id = hzr.party_id
3195 and hzr.start_date <= sysdate
3196 and nvl (hzr.end_date, sysdate + 1) > sysdate
3197 and hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
3198 and hzr.object_table_name = 'HZ_PARTIES'
3199 and hzr.subject_table_name = 'HZ_PARTIES'
3200 and hzr.object_id = p_org_party_id;
3201
3202 p_usertype_reg_id number;
3203 p_user_id number;
3204
3205 cursor find_old_item_type is
3206 select utreg.wf_item_type
3207 from jtf_um_usertype_reg utreg, jtf_um_usertypes_b ut
3208 where utreg.usertype_id = p_usertype_id
3209 and utreg.usertype_id = ut.usertype_id
3210 and utreg.status_code = 'PENDING'
3211 and nvl (utreg.effective_end_date, sysdate + 1) > sysdate;
3212
3213 p_wf_old_item_type varchar2(8);
3214
3215 cursor find_new_item_type is
3216 select wf_item_type
3217 from jtf_um_approvals_b
3218 where approval_id = p_new_approval_id;
3219
3220 p_wf_new_item_type varchar2(8);
3221 p_new_item_key number;
3222
3223 begin
3224
3225 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3226 'Entering usertype_approval_changed (' || p_usertype_id || ',' ||
3227 p_new_approval_id || ',' || p_old_approval_id || ',' ||
3228 p_org_party_id || ') API');
3229
3230 -- Find out the old item_type to abort Workflow process
3231 open find_old_item_type;
3232 fetch find_old_item_type into p_wf_old_item_type;
3233 close find_old_item_type;
3234
3235 -- Find out the new item_type to create Workflow process
3236 open find_new_item_type;
3237 fetch find_new_item_type into p_wf_new_item_type;
3238 close find_new_item_type;
3239
3240 open usertype_reg;
3241 loop
3242 fetch usertype_reg into p_usertype_reg_id,p_user_id;
3243 exit when usertype_reg%NOTFOUND;
3244
3245 -- abort WF Process first
3246 abort_process (p_wf_old_item_type, p_usertype_reg_id);
3247
3248 if p_wf_new_item_type is null then
3249
3250 do_approve_req(itemtype => p_wf_old_item_type,
3251 itemkey => p_usertype_reg_id);
3252 else
3253
3254 -- end date the old record in JTF_UM_USERTYPE_REG table
3255 update JTF_UM_USERTYPE_REG set effective_end_date = sysdate,
3256 last_update_date = sysdate, last_updated_by = FND_GLOBAL.USER_ID
3257 where usertype_reg_id = p_usertype_reg_id
3258 and user_id = p_user_id and status_code='PENDING';
3259
3260 -- create record in JTF_UM_USERTYPE_REG table
3261 JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW (
3262 X_USERTYPE_ID => p_usertype_id,
3263 X_LAST_APPROVER_COMMENT => null,
3264 X_APPROVER_USER_ID => null,
3265 X_EFFECTIVE_END_DATE => null,
3266 X_WF_ITEM_TYPE => p_wf_new_item_type,
3267 X_EFFECTIVE_START_DATE => sysdate,
3268 X_USERTYPE_REG_ID => p_new_item_key,
3269 X_USER_ID => p_user_id,
3270 X_STATUS_CODE => 'PENDING',
3271 X_CREATION_DATE => sysdate,
3272 X_CREATED_BY => FND_GLOBAL.USER_ID,
3273 X_LAST_UPDATE_DATE => sysdate,
3274 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3275 X_LAST_UPDATE_LOGIN => null);
3276
3277 -- Create WF process
3278 CreateProcess (ownerUserId => FND_GLOBAL.USER_ID,
3279 requestType => 'USERTYPE',
3280 requestID => p_usertype_id,
3281 requesterUserID => p_user_id,
3282 requestRegID => p_new_item_key);
3283
3284 -- Launch WF process
3285 LaunchProcess (requestType => 'USERTYPE',
3286 requestRegID => p_new_item_key);
3287
3288 end if;
3289 end loop;
3290 close usertype_reg;
3291
3292 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting usertype_approval_changed API');
3293
3294 end usertype_approval_changed;
3295
3296 procedure enrollment_approval_changed (p_subscription_id in number,
3297 p_new_approval_id in number,
3298 p_old_approval_id in number,
3299 p_org_party_id in number default null) is
3300
3301 p_user_id number;
3302 p_subscription_reg_id number;
3303 p_new_item_key number;
3304 p_usertype_status varchar2(30);
3305 p_wf_new_item_type varchar2(8);
3306 p_wf_old_item_type varchar2(8);
3307
3308 cursor subscription_reg is
3309 select subscription_reg_id, user_id, wf_item_type
3310 from jtf_um_subscription_reg
3311 where subscription_id = p_subscription_id
3312 and status_code = 'PENDING'
3313 and (effective_end_date is null
3314 or effective_end_date > sysdate);
3315
3316 cursor subscription_reg_w_org is
3317 select subreg.subscription_reg_id, fu.user_id, subreg.wf_item_type
3318 from jtf_um_subscription_reg subreg, fnd_user fu, hz_relationships hzr
3319 where subreg.subscription_id = p_subscription_id
3320 and subreg.status_code = 'PENDING'
3321 and nvl (subreg.effective_end_date, sysdate + 1) > sysdate
3322 and subreg.user_id = fu.user_id
3323 and fu.customer_id = hzr.party_id
3324 and hzr.start_date <= sysdate
3325 and nvl (hzr.end_date, sysdate + 1) > sysdate
3326 and hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
3327 and hzr.object_table_name = 'HZ_PARTIES'
3328 and hzr.subject_table_name = 'HZ_PARTIES'
3329 and hzr.object_id = p_org_party_id;
3330
3331 cursor find_new_item_type is
3332 select wf_item_type
3333 from jtf_um_approvals_b
3334 where approval_id = p_new_approval_id
3335 and (effective_end_date is null
3336 or effective_end_date > sysdate);
3337
3338 cursor check_usertype_status is
3339 select status_code
3340 from jtf_um_usertype_reg
3341 where user_id = p_user_id
3342 and (effective_end_date is null
3343 or effective_end_date > sysdate);
3344
3345 begin
3346
3347 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3348 'Entering enrollment_approval_changed (' || p_subscription_id || ',' ||
3349 p_new_approval_id || ',' || p_old_approval_id || ',' ||
3350 p_org_party_id || ') API');
3351
3352 -- Find out the new item_type to create Workflow process
3353 open find_new_item_type;
3354 fetch find_new_item_type into p_wf_new_item_type;
3355 close find_new_item_type;
3356
3357 if (p_org_party_id is null) then
3358 open subscription_reg;
3359 else
3360 open subscription_reg_w_org;
3361 end if;
3362 loop
3363 if (p_org_party_id is null) then
3364 fetch subscription_reg into p_subscription_reg_id, p_user_id, p_wf_old_item_type;
3365 exit when subscription_reg%NOTFOUND;
3366 else
3367 fetch subscription_reg_w_org into p_subscription_reg_id, p_user_id, p_wf_old_item_type;
3368 exit when subscription_reg_w_org%NOTFOUND;
3369 end if;
3370
3371 -- abort WF Process first
3372 if (p_wf_old_item_type is not null) then
3373 abort_process (p_wf_old_item_type, p_subscription_reg_id);
3374 end if;
3375
3376 if p_wf_new_item_type is null then
3377 -- The user selected no workflow
3378 -- Approve the request if the usertype status is approved.
3379 -- If status is PENDING, change the workflow in the
3380 -- JTF_UM_SUBSCRIPTION_REG to null.
3381
3382 open check_usertype_status;
3383 fetch check_usertype_status into p_usertype_status;
3384 if (check_usertype_status%notfound) then
3385 close check_usertype_status;
3386 if (p_org_party_id is null) then
3387 close subscription_reg;
3388 else
3389 close subscription_reg_w_org;
3390 end if;
3391 -- all Users who are using the UM should be in the
3392 -- JTF_UM_USERTYPE_REG table.
3393 raise_application_error (20000, 'User info is missing');
3394 end if;
3395 close check_usertype_status;
3396
3397 -- check if the user status code is pending.
3398 -- if pending, then we will not approve
3399
3400 if (p_usertype_status = 'PENDING') or
3401 (p_usertype_status = 'UPGRADE_PENDING') then
3402 -- usertype is 'PENDING', end date the last record and add a new
3403 -- record with null in the workflow itemtype column.
3404 update JTF_UM_SUBSCRIPTION_REG
3405 set EFFECTIVE_END_DATE = sysdate,
3406 LAST_UPDATE_DATE = sysdate,
3407 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
3408 where SUBSCRIPTION_REG_ID = p_subscription_reg_id;
3409
3410 -- create record in JTF_UM_SUBSCRIPTION_REG table
3411 JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW
3412 (X_SUBSCRIPTION_ID => p_subscription_id,
3413 X_LAST_APPROVER_COMMENT => null,
3414 X_APPROVER_USER_ID => null,
3415 X_EFFECTIVE_END_DATE => null,
3416 X_WF_ITEM_TYPE => null,
3417 X_EFFECTIVE_START_DATE => sysdate,
3418 X_SUBSCRIPTION_REG_ID => p_new_item_key,
3419 X_USER_ID => p_user_id,
3420 X_STATUS_CODE => 'PENDING',
3421 X_CREATION_DATE => sysdate,
3422 X_CREATED_BY => FND_GLOBAL.USER_ID,
3423 X_LAST_UPDATE_DATE => sysdate,
3424 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3425 X_LAST_UPDATE_LOGIN => null);
3426 else
3427 -- User status is not PENDING
3428 do_approve_req (itemtype => p_wf_old_item_type,
3429 itemkey => p_subscription_reg_id);
3430 end if;
3431 else
3432 -- p_wf_new_item_type is not null
3433 -- end date the old record in JTF_UM_SUBSCRIPTION_REG table
3434 update JTF_UM_SUBSCRIPTION_REG
3435 set EFFECTIVE_END_DATE = sysdate,
3436 LAST_UPDATE_DATE = sysdate,
3437 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
3438 where SUBSCRIPTION_REG_ID = p_subscription_reg_id;
3439
3440 -- create record in JTF_UM_SUBSCRIPTION_REG table
3441 JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW
3442 (X_SUBSCRIPTION_ID => p_subscription_id,
3443 X_LAST_APPROVER_COMMENT => null,
3444 X_APPROVER_USER_ID => null,
3445 X_EFFECTIVE_END_DATE => null,
3446 X_WF_ITEM_TYPE => p_wf_new_item_type,
3447 X_EFFECTIVE_START_DATE => sysdate,
3448 X_SUBSCRIPTION_REG_ID => p_new_item_key,
3449 X_USER_ID => p_user_id,
3450 X_STATUS_CODE => 'PENDING',
3451 X_CREATION_DATE => sysdate,
3452 X_CREATED_BY => FND_GLOBAL.USER_ID,
3453 X_LAST_UPDATE_DATE => sysdate,
3454 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
3455 X_LAST_UPDATE_LOGIN => null);
3456
3457 -- Create WF process
3458 CreateProcess (ownerUserId => FND_GLOBAL.USER_ID,
3459 requestType => 'ENROLLMENT',
3460 requestID => p_subscription_id,
3461 requesterUserID => p_user_id,
3462 requestRegID => p_new_item_key);
3463
3464 -- Launch WF process if user type approval has gone through
3465 open check_usertype_status;
3466 fetch check_usertype_status into p_usertype_status;
3467 close check_usertype_status;
3468
3469 if (p_usertype_status = 'APPROVED') then
3470 wf_engine.startProcess (itemType => p_wf_new_item_type,
3471 itemKey => p_new_item_key);
3472 end if;
3473 end if;
3474 end loop;
3475 if (p_org_party_id is null) then
3476 close subscription_reg;
3477 else
3478 close subscription_reg_w_org;
3479 end if;
3480
3481 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Initialization API');
3482
3483 end enrollment_approval_changed;
3484
3485 procedure enrollment_approval_changed (p_subscription_id in number,
3486 p_new_approval_id in number,
3487 p_old_approval_id in number) is
3488 begin
3489
3490 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3491 'Entering enrollment_approval_changed (' || p_subscription_id || ',' ||
3492 p_new_approval_id || ',' || p_old_approval_id || ') API');
3493
3494 enrollment_approval_changed (p_subscription_id,
3495 p_new_approval_id,
3496 p_old_approval_id,
3497 null);
3498
3499 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting enrollment_approval_changed API');
3500
3501 end enrollment_approval_changed;
3502
3503 procedure approval_chain_changed(p_approval_id in number,
3504 p_org_party_id in number)
3505 is
3506
3507 cursor usertype_approval is select usertype_id from jtf_um_usertypes_b
3508 where approval_id = p_approval_id
3509 and nvl (effective_end_date, sysdate + 1) > sysdate;
3510 p_usertype_id number;
3511
3512 cursor subscription_approval is select subscription_id from jtf_um_subscriptions_b
3513 where approval_id = p_approval_id
3514 and nvl (effective_end_date, sysdate + 1) > sysdate;
3515 p_subscription_id number;
3516
3517 begin
3518
3519 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3520 'Entering approval_chain_changed (' || p_approval_id || ',' ||
3521 p_org_party_id || ') API');
3522
3523 open usertype_approval;
3524 loop
3525 fetch usertype_approval into p_usertype_id;
3526 exit when usertype_approval%NOTFOUND;
3527
3528 -- Call procedure for usertype
3529 if p_org_party_id is not null then
3530
3531 usertype_approval_changed (p_usertype_id => p_usertype_id,
3532 p_new_approval_id => p_approval_id,
3533 p_old_approval_id => p_approval_id,
3534 p_org_party_id => p_org_party_id);
3535 else
3536
3537 usertype_approval_changed (p_usertype_id => p_usertype_id,
3538 p_new_approval_id => p_approval_id,
3539 p_old_approval_id => p_approval_id);
3540
3541 end if;
3542
3543 end loop;
3544 close usertype_approval;
3545
3546 open subscription_approval;
3547 loop
3548 fetch subscription_approval into p_subscription_id;
3549 exit when subscription_approval%NOTFOUND;
3550
3551 -- Call procedure for enrollments
3552 if p_org_party_id is not null then
3553
3554 enrollment_approval_changed (p_subscription_id => p_subscription_id,
3555 p_new_approval_id => p_approval_id,
3556 p_old_approval_id => p_approval_id,
3557 p_org_party_id => p_org_party_id);
3558
3559 else
3560
3561 enrollment_approval_changed (p_subscription_id => p_subscription_id,
3562 p_new_approval_id => p_approval_id,
3563 p_old_approval_id => p_approval_id,
3564 p_org_party_id => null);
3565
3566 end if;
3567 end loop;
3568 close subscription_approval;
3569
3570 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting approval_chain_changed API');
3571
3572 end approval_chain_changed;
3573
3574 function get_approver_comment(p_reg_id in number,
3575 p_wf_item_type in varchar2) return varchar2 is
3576
3577 p_approver_comment varchar2(4000);
3578
3579 begin
3580
3581 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE,
3582 'Entering get_approver_comment (' || p_reg_id || ',' ||
3583 p_wf_item_type || ') API');
3584
3585 p_approver_comment := wf_engine.GetItemAttrText (
3586 itemtype => p_wf_item_type,
3587 itemkey => to_char(p_reg_id),
3588 aname => 'APPROVER_COMMENT');
3589
3590 JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting get_approver_comment API');
3591
3592 return p_approver_comment;
3593
3594 end get_approver_comment;
3595
3596
3597 end JTF_UM_WF_APPROVAL;