1 package body MISPER_WF_SYNCH_USER_ROLE as
2 /* $Header: perhrwfs.pkb 120.6.12020000.6 2013/05/24 06:50:04 pathota ship $ */
3 -- ===========================================================================
4 -- Package name : MISPER_WF_SYNCH_USER_ROLE
5 -- Purpose :
6 -- This package contains
7
8 --
9 -- Function
10 -- synch_uname_to_role
11 --
12 -- Description
13 -- This method is a subscriber to the event oracle.apps.fnd.post.user.create
14 -- This method synchs email to uname
15 -- IN
16 -- the signature follows Workflow business events standards
17 -- p_subscription_guid -
18 -- IN/OUT
19 -- p_event - WF_EVENT_T which holds the data that needs to passed from/to
20 -- subscriber of the event
21 --
22 -- Original Author: Prasanna Athota
23 --
24
25 function synch_uname_to_role (p_subscription_guid in raw,
26 p_event in out NOCOPY WF_EVENT_T) return varchar2 is
27
28 Cursor C_User_Person (p_in_party_id in number, p_in_effective_start_date in date) is
29 Select distinct person_id,business_group_id,effective_start_date,effective_end_date
30 from per_all_people_f
31 where party_id = p_in_party_id
32 and trunc(p_in_effective_start_date) between effective_start_date and effective_end_date;
33
34 Cursor C_User_Pos_Prev_rel (p_in_user_name in varchar2, p_in_effective_start_date in date) is
35 Select *
36 from wf_user_role_assignments
37 where user_name = p_in_user_name
38 and role_name like 'POS:%'
39 and start_date < (trunc(p_in_effective_start_date));
40
41
42
43 l_param_list WF_PARAMETER_LIST_T ;
44 l_param_name VARCHAR2(240);
45 l_param_value VARCHAR2(2000);
46 l_event_name VARCHAR2(2000);
47 l_event_key VARCHAR2(2000);
48 l_email VARCHAR2(100);
49 l_user_name FND_USER.USER_NAME%TYPE;
50 l_email_address FND_USER.EMAIL_ADDRESS%TYPE;
51 l_exists varchar2(1);
52 l_owner varchar2(30);
53
54 l_old_user_name varchar2(200);
55 l_new_user_name varchar2(200);
56 l_event_fired varchar2(1);
57 l_user_id number;
58 l_party_id fnd_user.person_party_id%type;
59 l_user_start_date date;
60 l_user_end_date date;
61
62 begin
63
64
65 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
66 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'per.plsql.misper_wf_synch_user_role.synch_uname_to_role','Begin');
67 end if;
68
69 l_event_name := p_event.getEventName();
70 l_event_key := p_event.GetEventKey();
71 l_param_list := p_event.getparameterlist;
72
73 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
74 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'per.plsql.misper_wf_synch_user_role.synch_uname_to_role','userid - l_event_key:'||l_event_key);
75 end if;
76
77 Begin
78
79 select user_name, start_date, end_date, person_party_id, email_address
80 into l_user_name,l_user_start_date, l_user_end_date, l_party_id, l_email_address
81 from fnd_user
82 where user_id = l_event_key;
83
84
85 Exception when no_data_found then
86 Null;
87 End;
88
89
90 if (l_user_name is not null) -- start processing/propogating user roles if the specified user_name is not null
91 Then
92 /*Check if there are any relations existing for this user with any POS roles before the start date of the user.
93 This is to propogate user roles When the user start date is changed to a date later than the existing start date.*/
94
95 Begin
96 If l_event_name in ('oracle.apps.fnd.post.user.update')
97 then
98 For C_User_Pos_Prev_rel_ptr in C_User_Pos_Prev_rel (l_user_name,l_user_start_date)
99 Loop
100 WF_LOCAL_SYNCH.propagate_user_role(p_user_orig_system => C_User_Pos_Prev_rel_ptr.USER_ORIG_SYSTEM,
101 p_user_orig_system_id => C_User_Pos_Prev_rel_ptr.USER_ORIG_SYSTEM_ID,
102 p_role_orig_system => C_User_Pos_Prev_rel_ptr.ROLE_ORIG_SYSTEM,
103 p_role_orig_system_id => C_User_Pos_Prev_rel_ptr.ROLE_ORIG_SYSTEM_ID,
104 p_start_date => C_User_Pos_Prev_rel_ptr.START_DATE ,
105 p_expiration_date => l_user_start_date-1 );
106
107 wf_directory.DeleteUserRole(p_username => l_user_name,
108 p_rolename => C_User_Pos_Prev_rel_ptr.ROLE_NAME,
109 p_userOrigSystem => C_User_Pos_Prev_rel_ptr.USER_ORIG_SYSTEM,
110 p_userOrigSystemID => C_User_Pos_Prev_rel_ptr.USER_ORIG_SYSTEM_ID,
111 p_roleOrigSystem => C_User_Pos_Prev_rel_ptr.ROLE_ORIG_SYSTEM,
112 p_roleOrigSystemID => C_User_Pos_Prev_rel_ptr.ROLE_ORIG_SYSTEM_ID);
113 End Loop;
114
115 End if;
116 Exception
117 When Others then null;
118 End;
119
120 /*Now select the person records associated with this party id*/
121 For C_User_Person_ptr in C_User_Person(l_party_id, l_user_start_date) -- Call this to propogate the user role
122 Loop
123 per_hrwf_synch.sych_user_pos_role(p_in_user_name => l_user_name,
124 p_in_person_id => C_User_Person_ptr.person_id,
125 p_in_start_date => l_user_start_date,
126 p_in_exp_date => l_user_end_date);
127
128 End Loop;
129 End if;
130
131 return 'SUCCESS';
132 Exception
133 When Others then null;
134 End synch_uname_to_role;
135
136 End MISPER_WF_SYNCH_USER_ROLE;
[Home] [Help]