1 package body FND_WF_SYNCH as
2 /* $Header: AFWFSYNB.pls 120.1 2005/07/02 04:22:44 appldev noship $ */
3
4
5
6
7 -- The purpose of this package is to synchronize
8 -- FND_USER or FND_RESPONSIBILITY or FND_USER_RESP_GROUPS
9 -- table with wf tables WF_LOCAL_USERS, WF_LOCAL_ROLES,
10 -- WF_LOCAL_USER_ROLES by propagating the changes there.
11
12 -- WARNING!!! Procedures in this package are ONLY meant to be
13 -- called from either table handlers FND_USER_PKG,
14 -- FND_RESPONSIBILITY_PKG or from the FND forms interface.
15 -- The procedures here trust that the input parameters have
16 -- already been validated by the calling procedures, and won't
17 -- do any more validation. You risk putting corrupt data
18 -- in workflow directory services tables if you call these procedures
19 -- outside of the above mentioned places.
20
21
22 --
23 -- This procedure is known to be called by LOAD_RAW, UpdateUser and
24 -- LoadUser procedures in FND_USER_PKG to synchronize workflow
25 -- directory services table wf_local_roles. This should be called
26 -- AFTER the update to fnd_user table, so we just requery to get
27 -- all the values we need for workflow synchronization.
28 procedure synchFndUser(userName in varchar2,
29 old_employeeId in number -- employee_id before the update.
30 -- this is important since we need to determine if there
31 -- would be a change in orig_system and orig_system_id
32 )
33 AS
34 l_userId number;
35 l_description varchar2(240);
36 l_emailAddress varchar2(240);
37 l_fax varchar2(80);
38 l_startDate date;
39 l_endDate date;
40 l_employeeId number;
41 begin
42
43 null;
44
45 end synchFndUser;
46
47
48 -- notify workflow of event (UPDATE of a row in FND_USER)
49 -- to synchronize with workflow directory services table wf_local_users
50 -- known source:
51 -- NOTE: we need the old_employeeId before the update because need to
52 -- do some clean-up in the workflow directory tables when fnd_user is
53 -- linked to a different person, newly linked
54 -- to a person, or unlinked from a person based on the employee_id
55 procedure propagateUserUpdate
56 ( userId in number,
57 userName in varchar2,
58 employeeId in number,
59 description in varchar2,
60 emailAddress in varchar2,
61 fax in varchar2,
62 startDate in date,
63 endDate in date,
64 old_employeeId in number)
65 AS
66 l_language varchar2(240);
67 l_territory varchar2(240);
68 l_fullName varchar2(360);
69 l_emailAddress varchar2(2000);
70 l_notificationPreference varchar2(240);
71 l_status varchar2(30);
72 l_origSystem varchar2(30);
73 l_origSystemId number;
74 l_endDate date := NULL;
75
76 l_roleOrigSystemId number;
77 l_roleOrigSystem varchar2(30);
78 l_roleName varchar2(100);
79
80 /*cursor c_userrole(c_userName in varchar2, c_origSystem in varchar2, c_personId in number) is
81 select role_name, role_orig_system, role_orig_system_id
82 from wf_local_user_roles
83 where user_name = c_userName
84 and user_orig_system =c_origSystem
85 and user_orig_system_id = c_personId;*/
86 begin
87
88 null;
89
90 exception
91 when no_data_found then
92 raise;
93 when others then
94 raise;
95 end propagateUserUpdate;
96
97
98 -- notify workflow of event (INSERT to FND_USER)
99 -- to synchronize with workflow directory services table wf_local_users
100 -- known source:
101 procedure propagateUserInsert
102 ( userId in number,
103 userName in varchar2,
104 employeeId in number,
105 description in varchar2,
106 emailAddress in varchar2,
107 fax in varchar2,
108 startDate in date,
109 endDate in date)
110 as
111 l_language varchar2(240);
112 l_territory varchar2(240);
113 l_fullName varchar2(360);
114 l_emailAddress varchar2(2000);
115 l_notificationPreference varchar2(240);
116 l_status varchar2(30);
117 l_origSystem varchar2(30);
118 l_origSystemId number := -999;
119 l_endDate date := NULL;
120
121
122 begin
123
124 null;
125
126 exception
127 when no_data_found then
128 raise;
129 when others then
130 raise;
131 end propagateUserInsert;
132
133
134 -- notify workflow of event (UPDATE of end_date in FND_USER)
135 -- to synchronize with workflow directory services table wf_local_users
136 -- known source: FND_USER_PKG.disableUser
137 -- NOTE: This procedure should be called when ONLY the end_date is updated.
138 -- Otherwise, propagateUserUpdate should be called instead.
139 procedure propagateUserDisable
140 ( old_userName in varchar2)
141 as
142 begin
143
144 null;
145
146 end propagateUserDisable;
147
148
149 -- notify workflow of event (INSERT or UPDATE to FND_USER_RESP_GROUPS)
150 -- to synchronize with workflow directory services table wf_local_user_roles
151 -- known source:in the fnd_user_pkg.AddResp
152 -- this covers both the insert and update case;
153 -- wf_dir_trigger.insertUserRole will first tries to update and if
154 -- no row found, will do an insert
155 procedure propagateUserRespInsert(
156 userId in number,
157 responsibilityId in number,
158 respApplId in number, -- RESPONSIBILITY_APPLICATION_ID
159 startDate in date,
160 endDate in date)
161 AS
162 l_userOrigSystem varchar2(30);
163 l_userOrigSystemId number;
164 l_userName varchar2(100);
165 l_employeeId number;
166 begin
167
168 null;
169
170 exception
171 when no_data_found then
172 raise;
173 when others then
174 raise;
175 end propagateUserRespInsert;
176
177 -- notify workflow of event (UPDATE of RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID, START_DATE, END_DATE to FND_USER_RESP_GROUPS)
178 -- to synchronize with workflow directory services table wf_local_user_roles
179 -- known source: FND form
180 procedure propagateUserRespUpdate(
181 userId in number,
182 responsibilityId in number,
183 respApplId in number, -- RESPONSIBILITY_APPLICATION_ID
184 startDate in date,
185 endDate in date
186 )
187 AS
188 l_userOrigSystem varchar2(30);
189 l_userOrigSystemId number;
190 l_userName varchar2(100);
191 l_employeeId number;
192 begin
193
194 null;
195
196 exception
197 when no_data_found then
198 raise;
199 when others then
200 raise;
201 end propagateUserRespUpdate;
202
203
204
205
206 -- notify workflow of event (DELETE from FND_USER_RESP_GROUPS)
207 -- to synchronize with workflow directory services table wf_local_user_roles
208 -- known source:in the fnd_user_pkg.DelResp
209 procedure propagateUserRespDelete(
210 userId in number,
211 responsibilityId in number,
212 respApplId in number -- RESPONSIBILITY_APPLICATION_ID
213 )
214 AS
215 l_userOrigSystem varchar2(30);
216 l_userOrigSystemId number;
217 l_userName varchar2(100);
218 l_employeeId number;
219 begin
220
221 null;
222
223 exception
224 when no_data_found then
225 raise;
226 when others then
227 raise;
228 end propagateUserRespDelete;
229
230
231 -- notify workflow of event (INSERT to FND_RESPONSIBILITY)
232 -- to synchronize with workflow directory services table wf_local_roles
233 -- known source: in fnd_responsibility_pkg.INSERT_ROW
234 procedure propagateRespInsert(
235 applicationId in number,
236 responsibilityId in number,
237 responsibilityName in varchar2,
238 description in varchar2,
239 start_date in date,
240 end_date in date)
241 AS
242 l_language varchar2(30);
243 l_territory varchar2(30);
244 begin
245
246 null;
247
248
249 exception
250 when no_data_found then
251 raise;
252 when others then
253 raise;
254 end propagateRespInsert;
255
256
257 -- notify workflow of event (UPDATE to FND_RESPONSIBILITY/FND_RESPONSIBILITY_TL)
258 -- to synchronize with workflow directory services table wf_local_roles
259 -- known source: in fnd_responsibility_pkg.UPDATE_ROW
260 -- NOTE that change will only be propagated to wf table when userenv('LANG')
261 -- is same as base language. This is because wf local tables are not TL'd,
262 -- so we can only keep one row based on the base language.
263 procedure propagateRespUpdate(
264 applicationId in number,
265 responsibilityId in number,
266 responsibilityName in varchar2,
267 description in varchar2,
268 start_date in date,
269 end_date in date)
270 AS
271 l_languageCode varchar2(4);
272 l_language varchar2(30);
273 l_territory varchar2(30);
274 begin
275
276 null;
277
278
279 exception
280 when no_data_found then
281 raise;
282 when others then
283 raise;
284 end propagateRespUpdate;
285
286
287 -- notify workflow of event (UPDATE to FND_RESPONSIBILITY/FND_RESPONSIBILITY_TL)
288 -- to synchronize with workflow directory services table wf_local_roles
289 -- known source: in fnd_responsibility_pkg.TRANSLATE_ROW
290 -- NOTE that change will only be propagated to wf table when userenv('LANG')
291 -- is same as base language. This is because wf local tables are not TL'd,
292 -- so we can only keep one row based on the base language.
293 -- need this special version of propagetRespUpdate because
294 -- fnd_responsibility_pkg.TRANSLATE_ROW keeps the old value of
295 -- responsibility_name and description column if new value is null,
296 -- therefore, we cannot just update based on the passed in value, but
297 -- need to requery to get back actual value after the update
298 procedure propagateRespUpdate(
299 applicationId in number,
300 responsibilityId in number)
301 AS
302 l_languageCode varchar2(4);
303 l_language varchar2(30);
304 l_territory varchar2(30);
305 l_responsibilityName varchar2(100);
306 l_description varchar2(240);
307 begin
308
309 null;
310
311
312 exception
313 when no_data_found then
314 raise;
315 when others then
316 raise;
317 end propagateRespUpdate;
318
319
320 -- notify workflow of event (DELETE from FND_RESPONSIBILITY)
321 -- to synchronize with workflow directory services table wf_local_roles
322 -- known source: in fnd_responsibility_pkg.DELETE_ROW
323 procedure propagateRespDelete(
324 applicationId in number,
325 responsibilityId in number)
326 AS
327 begin
328
329 null;
330 end propagateRespDelete;
331
332 end FND_WF_SYNCH;
[Home] [Help]