1 PACKAGE BODY AP_WEB_PROXY_ASSIGN_PKG AS
2 /* $Header: apwprasb.pls 120.1.12000000.2 2007/02/15 23:01:42 skoukunt ship $ */
3
4 -- api has two conditions, if responsibility id is null then effectively all the
5 -- responsbilities for the user gets updated else only the specified
6 -- responsibility id for the user gets updated.
7 PROCEDURE all_assignee_update(p_assignor_id IN NUMBER,
8 p_responsibility_id IN NUMBER,
9 p_resp_app_id IN NUMBER,
10 p_sec_id IN NUMBER,
11 p_app_short_name IN VARCHAR2,
12 p_end_date IN DATE,
13 p_notification IN VARCHAR2) AS
14
15 l_assignee_name varchar2(100);
16 l_responsibility_name varchar2(100);
17 l_effective_start_date date;
18 l_effective_end_date date;
19 l_end_date date;
20 l_resp_app_id number;
21 l_resp_key varchar2(30);
22 l_sec_key varchar2(30);
23
24 cursor c1 is
25 select c.user_name l_assignee_name,
26 b.responsibility_name l_responsibility_name,
27 pa.effective_start_date l_effecttive_start_date,
28 pa.effective_end_date l_effective_end_date,
29 b.application_id as resp_application_id,
30 d.responsibility_key, e.security_group_key
31 from AP_WEB_PROXY_ASSIGNMENTS pa, fnd_responsibility_tl b, fnd_user c,
32 fnd_responsibility d, fnd_security_groups e, per_people_f ppf
33 where pa.ASSIGNEE_ID = c.user_id
34 and pa.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
35 and pa.ASSIGNOR_ID = p_assignor_id
36 and b.language = userenv('LANG')
37 and b.responsibility_id = d.responsibility_id
38 and b.application_id = d.application_id
39 and pa.security_group_id = e.security_group_id and c.employee_id = ppf.person_id
40 and pa.RESPONSIBILITY_ID = p_responsibility_id
41 and pa.responsibility_app_id = p_resp_app_id
42 and pa.responsibility_app_id = b.application_id
43 and e.security_group_id = p_sec_id --
44 for update of pa.effective_end_date;
45 cursor c2 is
46 select c.user_name l_assignee_name,
47 b.responsibility_name l_responsibility_name,
48 pa.effective_start_date l_effecttive_start_date,
49 pa.effective_end_date l_effective_end_date,
50 b.application_id as resp_application_id,
51 d.responsibility_key, e.security_group_key
52 from AP_WEB_PROXY_ASSIGNMENTS pa, fnd_responsibility_tl b, fnd_user c,
53 fnd_responsibility d, fnd_security_groups e, per_people_f ppf
54 where pa.ASSIGNEE_ID = c.user_id
55 and pa.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
56 and pa.ASSIGNOR_ID = p_assignor_id
57 and b.language = userenv('LANG')
58 and b.responsibility_id = d.responsibility_id
59 and b.application_id = d.application_id
60 and pa.security_group_id = e.security_group_id
61 and c.employee_id = ppf.person_id
62 and pa.responsibility_app_id = b.application_id
63 for update of pa.effective_end_date;
64 BEGIN
65 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
66 fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.all_assignee_update','Enter');
67 end if;
68 if (p_end_date is not null) then
69 l_end_date := trunc(p_end_date);
70 end if;
71 if (p_responsibility_id is not null) then
72 open c1;
73 loop
74 FETCH c1 into l_assignee_name, l_responsibility_name,l_effective_start_date,
75 l_effective_end_date, l_resp_app_id, l_resp_key, l_sec_key;
76 EXIT WHEN c1%NOTFOUND;
77 if (p_notification = 'Y') then
78 send_notification(l_assignee_name,
79 l_responsibility_name,
80 'UPDATED',
81 l_effective_start_date,
82 l_end_date);
83 end if;
84 -- call fnd_user_pkg.addResp l_assignee_name,
85 FND_USER_PKG.addresp(l_assignee_name, p_app_short_name, l_resp_key, l_sec_key, null, l_effective_start_date, l_end_date);
86 update ap_web_proxy_assignments
87 set effective_end_date = l_end_date
88 where current of c1;
89 end loop;
90 close c1;
91 else
92 open c2;
93 loop
94 fetch c2 into l_assignee_name, l_responsibility_name,l_effective_start_date,
95 l_effective_end_date, l_resp_app_id, l_resp_key, l_sec_key ;
96 exit when c2%NOTFOUND;
97 if (p_notification = 'Y') then
98 send_notification(l_assignee_name,
99 l_responsibility_name,
100 'UPDATED',
101 l_effective_start_date,
102 l_end_date);
103 end if;
104 -- call fnd_user_pkg.addResp
105 FND_USER_PKG.addresp(l_assignee_name, p_app_short_name, l_resp_key, l_sec_key, null, l_effective_start_date, l_end_date);
106 update ap_web_proxy_assignments
107 set effective_end_date = l_end_date
108 where current of c2;
109 end loop;
110 close c2;
111 end if;
112 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
113 fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.all_assignee_update','Exit');
114 end if;
115 exception when OTHERS then
116 app_exception.raise_exception;
117 END all_assignee_update;
118
119 /*----------------------------------------------------------------------------*
120 | Procedure
121 | proxy_assignments
122 |
123 | DESCRIPTION
124 | -- for OIE development only
125 | function that gets called by the workflow
126 | this function is subscribed to following events:
127 | oracle.apps.fnd.wf.ds.userRole.updated
128 | oracle.apps.fnd.wf.ds.user.updated
129 | Based on event key and its paramters, function would call other private procedure within
130 | this package to update the ap_web_proxy_assignments table, update fnd responsibilities
131 | by calling fnd_user_pkg.addresp api and sending notificaiton to assignee.
132 |
133 | PARAMETERS
134 | p_subscription_guid
135 | p_event
136 |
137 | RETURNS
138 | SUCCESS/ ERROR
139 *----------------------------------------------------------------------------*/
140 FUNCTION proxy_assignments (p_subscription_guid IN RAW,
141 p_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2
142 AS
143 l_event_key VARCHAR2(1000);
144 l_user_id VARCHAR2(30);
145 l_resp_id VARCHAR2(30);
146 l_resp_app_id VARCHAR2(30);
147 l_event_name VARCHAR2(100);
148 l_end_date date;
149 l_expiration_date date;
150 l_user_name varchar2(100);
151 l_ignore_str varchar2(30);
152 l_resp_key varchar2(30);
153 l_sec_key varchar2(30);
154 l_start_date varchar2(100);
155 l_app_short_name varchar2(30);
156 l_sec_group_id number;
157
158 BEGIN
159 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
160 fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments','Start');
161 end if;
162 l_event_name := p_event.getEventName();
163 l_event_key := p_event.GetEventKey;
164
165 if (l_event_name = 'oracle.apps.fnd.user.role.update') then
166
167 l_user_id := p_event.GetValueForParameter('FND_USER_ID');
168 l_resp_id := p_event.GetValueForParameter('FND_RESPONSIBILITY_ID');
169 l_app_short_name := p_event.GetValueForParameter('FND_APPS_SHORT_NAME');
170 l_resp_app_id := p_event.GetValueForParameter('FND_RESPONSIBILITY_APPS_ID');
171
172 select end_date, security_group_id
173 into l_end_date, l_sec_group_id
174 from fnd_user_resp_groups_direct
175 where responsibility_id = l_resp_id
176 and user_id = l_user_id
177 and rownum = 1;
178
179 all_assignee_update(l_user_id, l_resp_id, l_resp_app_id, l_sec_group_id, l_app_short_name, l_end_date, 'N');
180
181 elsif (l_event_name = 'oracle.apps.fnd.wf.ds.user.updated') then
182 if (p_event.GetValueForParameter('PARENT_ORIG_SYSTEM') = 'PER') then
183 if ( (p_event.GetValueForParameter('STATUS') = 'INACTIVE')
184 or ((p_event.GetValueForParameter('EXPIRATION_DATE')) <
185 (p_event.GetValueForParameter('OLD_END_DATE')) ) ) then
186 l_user_name := p_event.GetValueForParameter('USER_NAME');
187 select user_id, nvl(end_date, sysdate)
188 into l_user_id, l_end_date from fnd_user
189 where user_name = l_event_key;
190 end if;
191 end if;
192 -- this event to capture when an employee is terminated
193 -- all assigned responsibilities get updated.
194 all_assignee_update(l_user_id, null, null, null, 'SQLAP', l_end_date, 'N');
195 end if;
196 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
197 fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.proxy_assignments','End');
198 end if;
199 return 'SUCCESS';
200 exception when others then
201 if p_subscription_guid IS NOT NULL THEN
202 WF_CORE.context('AP_WEB_PROXY_ASSIGN_PKG', 'proxy_assignments', p_event.getEventName(), p_subscription_guid);
203 WF_EVENT.setErrorInfo(p_event, 'ERROR');
204 end if;
205 raise;
206 return 'ERROR';
207 END proxy_assignments;
208
209
210 /*----------------------------------------------------------------------------*
211 | Procedure
212 | send_notification
213 |
214 | DESCRIPTION
215 | -- for OIE development only
216 | Procedure to send notification to assignee when a responsibility is
217 | assigned/ udpated Message indicates the responsibility name,
218 | start date and end date.
219 |
220 | PARAMETERS
221 | p_user_name fnd user name
222 | p_resp_name fnd responsibility name
223 | p_assignor_name fnd user_name of assignor
224 | p_start_date start date for the repsonsibility
225 | p_end_date end date for the responsibility.
226 |
227 *----------------------------------------------------------------------------*/
228
229 PROCEDURE send_notification(p_user_name IN VARCHAR2,
230 p_resp_name IN VARCHAR2,
231 p_assignor_name IN VARCHAR2,
232 p_start_date IN VARCHAR2,
233 p_end_date IN VARCHAR2) IS
234
235 l_role_name varchar2(30);
236 l_role_display_name varchar2(80);
237 l_subject varchar2(2000);
238 l_body varchar2(2000);
239 l_request_id varchar2(120) ;
240 l_notification_id number;
241 l_textNameArr Wf_Engine.NameTabTyp;
242 l_textValArr Wf_Engine.TextTabTyp;
243 iText number ;
244 l_full_name varchar2(255);
245
246 BEGIN
247
248 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
249 fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.send_notification','Enter');
250 end if;
251
252 select p_user_name || to_char(sysdate, 'DDMONYYYYHH24MISS') into l_request_id from dual;
253
254 -- get Assignor full name from db
255 select full_name
256 into l_full_name
257 from fnd_user fu, hr_employees hre
258 where fu.employee_id = hre.employee_id
259 and user_name = p_assignor_name;
260
261 -- Fetch the message used as the confirmation message subject
262 FND_MESSAGE.SET_NAME ('SQLAP', 'OIE_PROXY_NOTIF_SUB');
263 FND_MESSAGE.SET_TOKEN ('ASSIGNOR_NAME', l_full_name);
264 l_subject := FND_MESSAGE.get;
265
266 -- Fetch the message used as the confirmation message body
267 -- if end date is null which means forever get a different message.
268 if (p_end_date is not null) then
269 FND_MESSAGE.SET_NAME ('SQLAP', 'OIE_PROXY_NOTIF_BODY');
270 FND_MESSAGE.SET_TOKEN ('END_DATE', p_end_Date);
271 else
272 FND_MESSAGE.SET_NAME ('SQLAP', 'OIE_PROXY_NOEND_DATE');
273 end if;
274 FND_MESSAGE.SET_TOKEN ('RESP_NAME', p_resp_name);
275 FND_MESSAGE.SET_TOKEN ('START_DATE', p_start_date);
276 l_body := FND_MESSAGE.get;
277
278 -- Create a process using the WF definition for sending AP emails(APWPROXY)
279 iText := 0;
280 WF_ENGINE.CREATEPROCESS('APWPROXY', l_request_id, 'AP_WEB_PROXY_PROCESS');
281 l_textNameArr(iText) := 'AP_WEB_PROXY_SUBJECT';
282 l_textValArr(iText) := l_subject;
283 iText := iText + 1;
284 l_textNameArr(iText) := 'AP_WEB_PROXY_BODY';
285 l_textValArr(iText) := l_body;
286 iText := iText + 1;
287 l_textNameArr(iText) := 'AP_WEB_RECIPIENT';
288 l_textValArr(iText) := p_user_name;
289 WF_ENGINE.SetItemAttrTextArray('APWPROXY', l_request_id, l_textNameArr, l_textValArr);
290 -- Start the notification process
291 WF_ENGINE.STARTPROCESS('APWPROXY', l_request_id);
292
293 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
294 fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.send_notification','Exit');
295 end if;
296
297 exception when others then
298 Wf_Core.Context('AP_WEB_PROXY_ASSIGN_PKG', 'send_notification',
299 p_user_name, p_resp_name, to_char(sysdate));
300 raise;
301 END send_notification;
302
303 END AP_WEB_PROXY_ASSIGN_PKG;
[Home] [Help]