[Home] [Help]
PACKAGE BODY: APPS.ZPB_SC_REASSIGN
Source
1 PACKAGE BODY ZPB_SC_REASSIGN AS
2 /* $Header: zpbscreassign.plb 120.0.12010.2 2005/12/23 08:57:23 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(15) := 'zpb_sc_reassign';
5
6
7 /*
8 * Public */
9
10 -------------------------------------------------------------------------------
11 -- REASSIGN_ALL_OBJECTS - Helper routine called from middle tier to re-assign
12 -- all objects from current owner to new owner
13 --
14 -- IN: p_owner_id - User_id of current owner
15 -- IN: p_new_owner_id - User_id that objects will be transferred to
16 -- IN: p_business_area_id - Business Area Id
17 -------------------------------------------------------------------------------
18
19 PROCEDURE reassign_all_objects (
20 p_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
21 p_new_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
22 p_business_area_id IN zpb_analysis_cycles.business_area_id%TYPE)
23
24 IS
25 BEGIN
26 reassign_bus_proc_objs(p_owner_id, p_new_owner_id, p_business_area_id);
27 -- exceptions will come later. see bug 4568557
28 -- reassign_exception_objs(p_owner_id, p_new_owner_id, p_business_area_id);
29
30 END;
31
32 -------------------------------------------------------------------------------
33 -- REASSIGN_BUS_PROC_OBJS - Re-assign all business process objects to new owner
34 --
35 -- IN: p_owner_id - User_id of current owner
36 -- IN: p_new_owner_id - User_id that objects will be transferred to
37 -- IN: p_business_area_id - Business Area Id
38 -------------------------------------------------------------------------------
39
40 PROCEDURE reassign_bus_proc_objs (
41 p_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
42 p_new_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
43 p_business_area_id IN zpb_analysis_cycles.business_area_id%TYPE)
44
45 IS
46
47 BEGIN
48
49 zpb_log.write('zpb_sc_reassign.reassign_bus_proc_objs',
50 'Reassigning BP objects from user id: ' || p_owner_id ||
51 ' to user id: ' || p_new_owner_id);
52
53 UPDATE
54 zpb_analysis_cycles
55 SET
56 owner_id = p_new_owner_id,
57 last_updated_by = fnd_global.USER_ID,
58 last_update_date = SYSDATE,
59 last_update_login = fnd_global.LOGIN_ID
60 WHERE
61 owner_id = p_owner_id AND
62 business_area_id = p_business_area_id;
63
64 zpb_log.write('zpb_sc_reassign.reassign_bus_proc_objs', 'End');
65
66 END;
67
68 -------------------------------------------------------------------------------
69 -- GET_ACTIVE_BUSINESS_PROCS - Returns comma delimited list of all
70 -- active Business Processes for a specified user.
71 --
72 -- IN: p_owner_id - User_id of current owner
73 -- IN: p_business_area_id - Business Area Id
74 -------------------------------------------------------------------------------
75
76 FUNCTION get_active_business_procs (
77 p_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
78 p_business_area_id IN zpb_analysis_cycles.business_area_id%TYPE)
79 return varchar2
80
81 IS
82
83 CURSOR active_bus_procs_cur is
84 SELECT
85 name
86 FROM
87 zpb_analysis_cycles
88 WHERE
89 owner_id = p_owner_id AND
90 business_area_id = p_business_area_id AND
91 status_code in ('DISABLE_ASAP', 'ENABLE_TASK', 'ERROR',
92 'MARKED_FOR_DELETION', 'PAUSED', 'PAUSING', 'PUBLISHED',
93 'WARNING', 'ACTIVE');
94
95 l_business_proc_names varchar2(1000); -- name is varchar2(300).
96 -- s/b MAX_LENGTH
97
98 BEGIN
99 zpb_log.write('zpb_sc_reassign.get_active_business_procs', 'Getting list of active Business Processes');
100
101 for each in active_bus_procs_cur loop
102 if nvl(length(l_business_proc_names),0) + nvl(length(each.name),0) + 2 < MAX_LENGTH then
103 l_business_proc_names := l_business_proc_names || each.name || ', ';
104 end if;
105 end loop;
106
107 zpb_log.write('zpb_sc_reassign.get_active_business_procs', 'End');
108
109 return l_business_proc_names;
110
111 END;
112
113 -------------------------------------------------------------------------------
114 -- GET_WORKSHEETS - Returns comma delimited list of all worksheets for a
115 -- specified user.
116 --
117 -- IN: p_owner_id - User_id of current owner
118 -- IN: p_business_area_id - Business Area Id
119 -------------------------------------------------------------------------------
120
121 FUNCTION get_worksheets (
122 p_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
123 p_business_area_id IN zpb_analysis_cycles.business_area_id%TYPE)
124 return varchar2
125
126 IS
127
128 CURSOR worksheets_cur is
129 SELECT
130 template_name
131 FROM
132 zpb_dc_objects
133 WHERE
134 object_user_id = p_owner_id AND
135 object_type IN ('W', 'C') AND
136 business_area_id = p_business_area_id;
137
138
139 l_worksheet_names varchar2(1000); -- name is varchar2(300).
140 -- s/b MAX_LENGTH
141
142 BEGIN
143 zpb_log.write('zpb_sc_reassign.get_worksheets', 'Getting list of Worksheets');
144
145 for each in worksheets_cur loop
146 if nvl(length(l_worksheet_names),0) + nvl(length(each.template_name),0) + 2 < MAX_LENGTH then
147 l_worksheet_names := l_worksheet_names || each.template_name || ', ';
148 end if;
149 end loop;
150
151 zpb_log.write('zpb_sc_reassign.zpb_sc_reassign.get_worksheets', 'End');
152
153 return l_worksheet_names;
154
155 END;
156
157 -------------------------------------------------------------------------------
158 -- REASSIGN_EXCEPTION_OBJS - Re-assign all business process objects to new owner
159 --
160 -- IN: p_owner_id - User_id of current owner
161 -- IN: p_new_owner_id - User_id that objects will be transferred to
162 -- IN: p_business_area_id - Business Area Id
163 -------------------------------------------------------------------------------
164
165 PROCEDURE reassign_exception_objs (
166 p_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
167 p_new_owner_id IN zpb_analysis_cycles.owner_id%TYPE,
168 p_business_area_id IN zpb_analysis_cycles.business_area_id%TYPE)
169 IS
170
171 l_new_owner_user_name fnd_user.user_name%TYPE;
172 l_owner_user_name fnd_user.user_name%TYPE;
173
174 BEGIN
175
176 zpb_log.write('zpb_sc_reassign. reassign_exception_objs',
177 'Reassigning Exception objects from user id: ' || p_owner_id ||
178 ' to user id: ' || p_new_owner_id);
179
180 -- Some columns use text name.
181 SELECT user_name INTO l_new_owner_user_name FROM fnd_user WHERE user_id = p_new_owner_id;
182 SELECT user_name INTO l_owner_user_name FROM fnd_user WHERE user_id = p_owner_id;
183
184 -- zpb_analysis_cycle_tasks
185 UPDATE
186 zpb_analysis_cycle_tasks
187 SET
188 owner_id = p_new_owner_id,
189 last_updated_by = fnd_global.USER_ID,
190 last_update_date = SYSDATE,
191 last_update_login = fnd_global.LOGIN_ID
192 WHERE
193 owner_id = p_owner_id and
194 analysis_cycle_id in (SELECT
195 distinct(ZACT.analysis_cycle_id)
196 FROM
197 zpb_analysis_cycle_tasks ZACT,
198 zpb_analysis_cycles ZAC
199 WHERE
200 ZACT.analysis_cycle_id =
201 ZAC.analysis_cycle_id and
202 ZAC.business_area_id = p_business_area_id);
203
204
205 -- zpb_task_parameters owner_id
206 UPDATE
207 zpb_task_parameters
208 SET
209 value = p_new_owner_id,
210 last_updated_by = fnd_global.USER_ID,
211 last_update_date = SYSDATE,
212 last_update_login = fnd_global.LOGIN_ID
213 WHERE
214 name = 'OWNER_ID' and
215 value = p_owner_id and
216 task_id in (SELECT
217 ZTP.task_id
218 FROM
219 zpb_task_parameters ZTP,
220 zpb_analysis_cycle_tasks ZACT,
221 zpb_analysis_cycles ZAC
222 WHERE
223 ZTP.task_id = ZACT.task_id and
224 ZACT.analysis_cycle_id = ZAC.analysis_cycle_id and
225 ZAC.business_area_id = p_business_area_id);
226
227 -- zpb_task_parameters specfied_notification_recipient
228 UPDATE
229 zpb_task_parameters
230 SET
231 value = l_new_owner_user_name,
232 last_updated_by = fnd_global.USER_ID,
233 last_update_date = SYSDATE,
234 last_update_login = fnd_global.LOGIN_ID
235 WHERE
236 name = 'SPECIFIED_NOTIFICATION_RECIPIENT' and
237 value = l_owner_user_name and
238 task_id in (SELECT
239 ZTP.task_id
240 FROM
241 zpb_task_parameters ZTP,
242 zpb_analysis_cycle_tasks ZACT,
243 zpb_analysis_cycles ZAC
244 WHERE
245 ZTP.task_id = ZACT.task_id and
246 ZACT.analysis_cycle_id = ZAC.analysis_cycle_id and
247 ZAC.business_area_id = p_business_area_id);
248
249 -- zpb_excp_results owner info
250 UPDATE
251 zpb_excp_results
252 SET
253 owner_id = p_new_owner_id,
254 owner = l_new_owner_user_name,
255 last_updated_by = fnd_global.USER_ID,
256 last_update_date = SYSDATE,
257 last_update_login = fnd_global.LOGIN_ID
258 WHERE
259 owner_id = p_owner_id and
260 task_id in (SELECT
261 ZTP.task_id
262 FROM
263 zpb_task_parameters ZTP,
264 zpb_analysis_cycle_tasks ZACT,
265 zpb_analysis_cycles ZAC
266 WHERE
267 ZTP.task_id = ZACT.task_id and
268 ZACT.analysis_cycle_id = ZAC.analysis_cycle_id and
269 ZAC.business_area_id = p_business_area_id);
270
271 -- zpb_excp_results approver info
272 UPDATE
273 zpb_excp_results
274 SET
275 approver_id = p_new_owner_id,
276 approver = l_new_owner_user_name,
277 last_updated_by = fnd_global.USER_ID,
278 last_update_date = SYSDATE,
279 last_update_login = fnd_global.LOGIN_ID
280 WHERE
281 approver_id = p_owner_id and
282 task_id in (SELECT
283 ZTP.task_id
284 FROM
285 zpb_task_parameters ZTP,
286 zpb_analysis_cycle_tasks ZACT,
287 zpb_analysis_cycles ZAC
288 WHERE
289 ZTP.task_id = ZACT.task_id and
290 ZACT.analysis_cycle_id = ZAC.analysis_cycle_id and
291 ZAC.business_area_id = p_business_area_id);
292
293 -- zpb_excp_explanations owner info
294 UPDATE
295 zpb_excp_explanations
296 SET
297 owner_id = p_new_owner_id,
298 owner = l_new_owner_user_name,
299 last_updated_by = fnd_global.USER_ID,
300 last_update_date = SYSDATE,
301 last_update_login = fnd_global.LOGIN_ID
302 WHERE
303 owner_id = p_owner_id and
304 task_id in (SELECT
305 ZTP.task_id
306 FROM
307 zpb_task_parameters ZTP,
308 zpb_analysis_cycle_tasks ZACT,
309 zpb_analysis_cycles ZAC
310 WHERE
311 ZTP.task_id = ZACT.task_id and
312 ZACT.analysis_cycle_id = ZAC.analysis_cycle_id and
313 ZAC.business_area_id = p_business_area_id);
314
315
316 -- zpb_excp_explanations approver info
317 UPDATE
318 zpb_excp_explanations
319 SET
320 approver_id = p_new_owner_id,
321 approver = l_new_owner_user_name,
322 last_updated_by = fnd_global.USER_ID,
323 last_update_date = SYSDATE,
324 last_update_login = fnd_global.LOGIN_ID
325 WHERE
326 approver_id = p_owner_id and
327 task_id in (SELECT
328 ZTP.task_id
329 FROM
330 zpb_task_parameters ZTP,
331 zpb_analysis_cycle_tasks ZACT,
332 zpb_analysis_cycles ZAC
333 WHERE
334 ZTP.task_id = ZACT.task_id and
335 ZACT.analysis_cycle_id = ZAC.analysis_cycle_id and
336 ZAC.business_area_id = p_business_area_id);
337
338 zpb_log.write('zpb_sc_reassign. reassign_exception_objs', 'End');
339
340 END;
341
342 END ZPB_SC_REASSIGN;