[Home] [Help]
PACKAGE BODY: APPS.CSM_USER_EVENT_PKG
Source
1 PACKAGE BODY CSM_USER_EVENT_PKG AS
2 /* $Header: csmeusrb.pls 120.26.12010000.2 2008/10/22 12:45:19 trajasek ship $ */
3
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- ravir
13 -- -- --------- ------ ------------------------------------------
14 -- Enter procedure, function bodies as shown below
15 FUNCTION is_omfs_palm_responsibility(p_responsibility_id IN NUMBER, p_user_id IN NUMBER)RETURN BOOLEAN
16 IS
17 l_sqlerrno VARCHAR2(20);
18 l_sqlerrmsg VARCHAR2(4000);
19 l_error_msg VARCHAR2(4000);
20 l_return_status VARCHAR2(2000);
21
22 CURSOR l_responsibilities_csr (p_resp_id NUMBER, l_user_id IN NUMBER)
23 IS
24 SELECT 1 --R12-Multiple Responsibility
25 FROM asg_user_pub_resps aupr,
26 asg_user asu
27 WHERE aupr.user_name = asu.user_name
28 AND aupr.responsibility_id = p_resp_id
29 AND asu.user_id = l_user_id
30 AND aupr.pub_name = 'SERVICEP';
31
32 l_responsibilities_rec l_responsibilities_csr%ROWTYPE;
33
34 BEGIN
35 CSM_UTIL_PKG.LOG('Entering IS_OMFS_PALM_RESPONSIBILITY for user_id: ' || p_user_id || ' and responsibility_id: '
36 || p_responsibility_id , 'CSM_USER_EVENT_PKG.IS_OMFS_PALM_RESPONSIBILITY',FND_LOG.LEVEL_PROCEDURE);
37
38 OPEN l_responsibilities_csr (p_responsibility_id,p_user_id);
39 FETCH l_responsibilities_csr INTO l_responsibilities_rec;
40
41 IF (l_responsibilities_csr%NOTFOUND) THEN
42 CLOSE l_responsibilities_csr;
43 RETURN FALSE;
44 END IF;
45 CLOSE l_responsibilities_csr;
46
47 CSM_UTIL_PKG.LOG('Leaving IS_OMFS_PALM_RESPONSIBILITY for user_id: ' || p_user_id || ' and responsibility_id: '
48 || p_responsibility_id , 'CSM_USER_EVENT_PKG.IS_OMFS_PALM_RESPONSIBILITY',FND_LOG.LEVEL_PROCEDURE);
49
50 RETURN TRUE;
51 EXCEPTION
52 WHEN OTHERS THEN
53 l_sqlerrno := to_char(SQLCODE);
54 l_sqlerrmsg := substr(SQLERRM, 1,2000);
55 l_error_msg := ' Exception in IS_OMFS_PALM_RESPONSIBILITY for for user_id: ' || p_user_id || ' and responsibility_id: '
56 || p_responsibility_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
57 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_USER_EVENT_PKG.IS_OMFS_PALM_RESPONSIBILITY',FND_LOG.LEVEL_EXCEPTION);
58 RETURN FALSE;
59 END IS_OMFS_PALM_RESPONSIBILITY;
60
61 PROCEDURE disable_user_pub_synch(p_user_id IN NUMBER)
62 IS
63 l_sqlerrno VARCHAR2(20);
64 l_sqlerrmsg VARCHAR2(4000);
65 l_error_msg VARCHAR2(4000);
66 l_return_status VARCHAR2(2000);
67
68 BEGIN
69 CSM_UTIL_PKG.LOG('Entering disable_user_pub_synch for user_id:' || p_user_id,
70 'CSM_USER_EVENT_PKG.disable_user_pub_synch',FND_LOG.LEVEL_PROCEDURE);
71
72 asg_helper.disable_user_pub_synch(p_user_id, 'SERVICEP');
73
74 CSM_UTIL_PKG.LOG('Leaving disable_user_pub_synch for user_id:' || p_user_id,
75 'CSM_USER_EVENT_PKG.disable_user_pub_synch',FND_LOG.LEVEL_PROCEDURE);
76 EXCEPTION
77 WHEN OTHERS THEN
78 l_sqlerrno := to_char(SQLCODE);
79 l_sqlerrmsg := substr(SQLERRM, 1,2000);
80 l_error_msg := ' Exception in disable_user_pub_synch for for user_id: ' || p_user_id
81 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
82 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_USER_EVENT_PKG.disable_user_pub_synch',FND_LOG.LEVEL_EXCEPTION);
83 RAISE;
84 END disable_user_pub_synch;
85
86 FUNCTION is_first_omfs_palm_user(p_user_id IN NUMBER) RETURN BOOLEAN
87 IS
88 l_sqlerrno VARCHAR2(20);
89 l_sqlerrmsg VARCHAR2(4000);
90 l_error_msg VARCHAR2(4000);
91 l_return_status VARCHAR2(2000);
92
93 CURSOR l_user_csr (p_user_id NUMBER)
94 IS
95 SELECT au.user_id
96 FROM asg_user au,
97 asg_user_pub_resps asg_resp
98 WHERE au.user_id <> p_user_id
99 AND au.enabled = 'Y'
100 AND asg_resp.pub_name = 'SERVICEP'
101 AND asg_resp.user_name = au.user_name;
102
103 l_user_rec l_user_csr%ROWTYPE;
104
105 BEGIN
106 CSM_UTIL_PKG.LOG('Entering IS_FIRST_OMFS_PALM_USER for user_id:' || p_user_id,
107 'CSM_USER_EVENT_PKG.IS_FIRST_OMFS_PALM_USER',FND_LOG.LEVEL_PROCEDURE);
108
109 OPEN l_user_csr(p_user_id);
110 FETCH l_user_csr INTO l_user_rec;
111 IF (l_user_csr%FOUND) THEN
112 CLOSE l_user_csr;
113 RETURN FALSE;
114 END IF;
115 CLOSE l_user_csr;
116
117 CSM_UTIL_PKG.LOG('Leaving IS_FIRST_OMFS_PALM_USER for user_id:' || p_user_id,
118 'CSM_USER_EVENT_PKG.IS_FIRST_OMFS_PALM_USER',FND_LOG.LEVEL_PROCEDURE);
119 RETURN TRUE;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 l_sqlerrno := to_char(SQLCODE);
124 l_sqlerrmsg := substr(SQLERRM, 1,2000);
125 l_error_msg := ' Exception in IS_FIRST_OMFS_PALM_USER for for user_id: ' || p_user_id
126 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
127 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_USER_EVENT_PKG.IS_FIRST_OMFS_PALM_USER',FND_LOG.LEVEL_EXCEPTION);
128 RETURN TRUE;
129 END IS_FIRST_OMFS_PALM_USER;
130
131 PROCEDURE spawn_dashboard_srch_cols_ins(p_user_id IN NUMBER)
132 IS
133 l_sqlerrno VARCHAR2(20);
134 l_sqlerrmsg VARCHAR2(4000);
135 l_msg VARCHAR2(4000);
136 l_status VARCHAR2(40);
137 BEGIN
138 CSM_UTIL_PKG.LOG('Entering spawn_dashboard_srch_cols_ins for user_id: ' || p_user_id,
139 'CSM_USER_EVENT_PKG.spawn_dashboard_srch_cols_ins',FND_LOG.LEVEL_PROCEDURE);
140
141 CSM_DBOARD_SRCH_COLS_EVENT_PKG.Refresh_Acc(l_status,l_msg);
142
143 CSM_UTIL_PKG.LOG('Leaving spawn_dashboard_srch_cols_ins for user_id: ' || p_user_id,
144 'CSM_USER_EVENT_PKG.spawn_dashboard_srch_cols_ins',FND_LOG.LEVEL_PROCEDURE);
145 EXCEPTION
146 WHEN OTHERS THEN
147 l_sqlerrno := to_char(SQLCODE);
148 l_sqlerrmsg := substr(SQLERRM, 1,2000);
149 l_msg := ' Exception in spawn_dashboard_srch_cols_ins for for user_id: ' || p_user_id
150 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
151 CSM_UTIL_PKG.LOG(l_msg, 'csm_user_event_pkg.spawn_dashboard_srch_cols_ins',FND_LOG.LEVEL_EXCEPTION);
152 RAISE;
153 END spawn_dashboard_srch_cols_ins;
154
155 --Bug 7239431
156 PROCEDURE spawn_perz_ins(p_user_id IN NUMBER)
157 IS
158 l_sqlerrno VARCHAR2(20);
159 l_sqlerrmsg VARCHAR2(4000);
160 l_msg VARCHAR2(4000);
161 l_return_status VARCHAR2(40);
162 BEGIN
163 CSM_UTIL_PKG.LOG('Entering spawn_perz_ins for user_id: ' || p_user_id,
164 'CSM_USER_EVENT_PKG.spawn_perz_ins',FND_LOG.LEVEL_PROCEDURE);
165
166 CSM_UTIL_PKG.LOG('Populating PERSONALIZED CUSTOMIZATION VIEWS Access table',
167 'CSM_USER_EVENT_PKG.spawn_perz_ins',FND_LOG.LEVEL_PROCEDURE);
168
169 CSM_CUSTMZ_VIEWS_EVENT_PKG.REFRESH_USER(p_user_id);
170
171 CSM_UTIL_PKG.LOG('Populating PERSONALIZED DELTA PAGE VIEWS Access table',
172 'CSM_USER_EVENT_PKG.spawn_perz_ins',FND_LOG.LEVEL_PROCEDURE);
173
174 CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_USER(p_user_id);
175
176
177 CSM_UTIL_PKG.LOG('Populating PERSONALIZED NEW MESSAGES Access table',
178 'CSM_USER_EVENT_PKG.spawn_perz_ins',FND_LOG.LEVEL_PROCEDURE);
179
180 CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER(p_user_id);
181
182 CSM_UTIL_PKG.LOG('Leaving spawn_perz_ins for user_id: ' || p_user_id,
183 'CSM_USER_EVENT_PKG.spawn_perz_ins',FND_LOG.LEVEL_PROCEDURE);
184 EXCEPTION
185 WHEN OTHERS THEN
186 l_sqlerrno := to_char(SQLCODE);
187 l_sqlerrmsg := substr(SQLERRM, 1,2000);
188 l_msg := ' Exception in spawn_perz_ins for for user_id: ' || p_user_id
189 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
190 CSM_UTIL_PKG.LOG(l_msg, 'csm_user_event_pkg.spawn_perz_ins',FND_LOG.LEVEL_EXCEPTION);
191 RAISE;
192 END spawn_perz_ins;
193
194 PROCEDURE spawn_task_ins(p_user_id IN NUMBER)
195 IS
196 l_sqlerrno VARCHAR2(20);
197 l_sqlerrmsg VARCHAR2(4000);
198 l_error_msg VARCHAR2(4000);
199 l_return_status VARCHAR2(2000);
200
201 -- get all task_ids created by mobile user
202 --Bug 4924584
203
204 CURSOR l_task_csr (b_user_id number)
205 IS --SELECT tasks which are open and are closed and within history days
206 SELECT jt.task_id
207 FROM JTF_TASKS_B jt,
208 jtf_task_statuses_b jts
209 WHERE jt.created_by = b_user_id
210 AND jts.task_status_id = jt.task_status_id
211 AND ( --task which are closed and within history days
212 ( jt.creation_date BETWEEN SYSDATE AND (SYSDATE - csm_profile_pkg.get_task_history_days(b_user_id))
213 AND ( NVL(jts.cancelled_flag,'N') = 'Y' OR NVL(jts.closed_flag,'N') = 'Y'
214 OR NVL(jts.rejected_flag,'N') = 'Y' OR NVL(jts.completed_flag, 'N') = 'Y'
215 )
216 )
217 OR --task which are open
218 ( NOT (NVL(jts.cancelled_flag,'N') = 'Y' OR NVL(jts.closed_flag,'N') = 'Y'
219 OR NVL(jts.rejected_flag,'N') = 'Y' OR NVL(jts.completed_flag, 'N') = 'Y'
220 )
221 )
222 )
223 UNION --select tasks which are created by the user and their correspdg SR is open
224 SELECT jt.task_id
225 FROM JTF_TASKS_B jt
226 WHERE jt.created_by = b_user_id
227 AND jt.source_object_type_code ='SR'
228 AND EXISTS
229 (SELECT inc.incident_id
230 FROM cs_incidents_all_b inc,
231 cs_incident_statuses_b ists
232 WHERE inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
233 AND NVL(ists.CLOSE_FLAG,'N')= 'N'
234 AND inc.incident_id = jt.source_object_id
235 );
236
237
238 BEGIN
239 CSM_UTIL_PKG.LOG('Entering spawn_task_ins for user_id: ' || p_user_id,
240 'CSM_USER_EVENT_PKG.spawn_task_ins',FND_LOG.LEVEL_PROCEDURE);
241
242 -- loop to spawn process
243 FOR l_task_rec IN l_task_csr(p_user_id) LOOP
244 csm_task_event_pkg.task_ins_init(p_task_id=>l_task_rec.task_id);
245 END LOOP;
246
247 CSM_UTIL_PKG.LOG('Leaving spawn_task_ins for user_id: ' || p_user_id,
248 'CSM_USER_EVENT_PKG.spawn_task_ins',FND_LOG.LEVEL_PROCEDURE);
249 EXCEPTION
250 WHEN OTHERS THEN
251 l_sqlerrno := to_char(SQLCODE);
252 l_sqlerrmsg := substr(SQLERRM, 1,2000);
253 l_error_msg := ' Exception in spawn_task_ins for for user_id: ' || p_user_id
254 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
255 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_task_ins',FND_LOG.LEVEL_EXCEPTION);
256 RAISE;
257 END spawn_task_ins;
258
259 --12.1XB6
260 PROCEDURE spawn_incident_ins(p_user_id IN NUMBER)
261 IS
262 l_sqlerrno VARCHAR2(20);
263 l_sqlerrmsg VARCHAR2(4000);
264 l_error_msg VARCHAR2(4000);
265 l_return_status VARCHAR2(2000);
266
267 -- get all open incident_ids owned/created by mobile user
268 CURSOR l_incident_csr (b_grp_id NUMBER,b_user_id NUMBER)
269 IS
270 SELECT incident_id
271 FROM cs_incidents_all_b inc,
272 cs_incident_statuses_b ists
273 WHERE (
274 inc.owner_group_id = b_grp_id
275 OR
276 csm_util_pkg.get_owner(inc.created_by)=b_user_id
277 )
278 AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
279 AND inc.install_site_id IS NOT NULL
280 AND NVL(ists.CLOSE_FLAG, 'N') <> 'Y';
281
282
283 CURSOR c_get_group(b_user_id NUMBER)
284 IS
285 SELECT GROUP_ID,USER_ID
286 FROM ASG_USER
287 WHERE USER_ID=b_user_id
288 AND USER_ID=OWNER_ID;
289
290 l_group_id NUMBER;
291 l_user_id NUMBER;
292 BEGIN
293 CSM_UTIL_PKG.LOG('Entering spawn_incident_ins for user_id: ' || p_user_id,
294 'CSM_USER_EVENT_PKG.spawn_incident_ins',FND_LOG.LEVEL_PROCEDURE);
295
296 OPEN c_get_group(p_user_id);
297 FETCH c_get_group INTO l_group_id,l_user_id;
298 CLOSE c_get_group;
299
300 IF (l_group_id is not null) OR (l_user_id is not null) THEN
301 -- loop to spawn process
302 FOR l_incident_rec IN l_incident_csr(l_group_id,l_user_id) LOOP
303 csm_sr_event_pkg.sr_ins_init(p_incident_id=>l_incident_rec.incident_id);
304 END LOOP;
305 END IF;
306
307 CSM_UTIL_PKG.LOG('Leaving spawn_incident_ins for user_id: ' || p_user_id,
308 'CSM_USER_EVENT_PKG.spawn_incident_ins',FND_LOG.LEVEL_PROCEDURE);
309 EXCEPTION
310 WHEN OTHERS THEN
311 l_sqlerrno := to_char(SQLCODE);
312 l_sqlerrmsg := substr(SQLERRM, 1,2000);
313 l_error_msg := ' Exception in spawn_incident_ins for for user_id: ' || p_user_id
314 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
315 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_incident_ins',FND_LOG.LEVEL_EXCEPTION);
316 RAISE;
317 END spawn_incident_ins;
318
319 PROCEDURE spawn_task_assignment_ins(p_user_id IN NUMBER)
320 IS
321 l_sqlerrno VARCHAR2(20);
322 l_sqlerrmsg VARCHAR2(4000);
323 l_error_msg VARCHAR2(4000);
324 l_return_status VARCHAR2(2000);
325
326 -- get all task assignment id's for a user
327 CURSOR l_task_assign_csr (p_user_id NUMBER)
328 IS
329 SELECT jtf_ta.task_assignment_id --get all the task assignments which are open and of type TASK
330 FROM jtf_rs_resource_extns jtf_rs,
331 jtf_task_assignments jtf_ta,
332 jtf_task_statuses_b jts_jta
333 WHERE jtf_rs.user_id = p_user_id
334 AND jtf_ta.resource_id = jtf_rs.resource_id
335 AND jts_jta.task_Status_id = jtf_ta.assignment_status_id
336 AND NOT( NVL(jts_jta.cancelled_flag, 'N') = 'Y' OR NVL(jts_jta.closed_flag, 'N') = 'Y'
337 OR NVL(jts_jta.completed_flag, 'N') = 'Y' OR NVL(jts_jta.rejected_flag,'N') = 'Y')
338 AND EXISTS (
339
340 SELECT 1
341 FROM jtf_tasks_b jt,
342 jtf_task_statuses_b jts
343 WHERE jt.task_id = jtf_ta.task_id
344 AND jts.task_status_id = jt.task_status_id
345 AND jt.source_object_type_code ='TASK'
346 AND NOT ( NVL(jts.cancelled_flag, 'N') = 'Y' OR NVL(jts.closed_flag, 'N') = 'Y'
347 OR NVL(jts.completed_flag, 'N') = 'Y' OR NVL(jts.rejected_flag,'N') = 'Y'
348 )
349 )
350 UNION ALL--Select task assignments which are for open SRs
351 SELECT jtf_ta.task_assignment_id
352 FROM jtf_rs_resource_extns jtf_rs,
353 jtf_task_assignments jtf_ta,
354 JTF_TASKS_B jt
355 WHERE jtf_rs.user_id = p_user_id
356 AND jt.task_id = jtf_ta.task_id
357 AND jtf_ta.resource_id = jtf_rs.resource_id
358 AND jt.source_object_type_code ='SR'
359 AND EXISTS
360 (SELECT 'X'
361 FROM cs_incidents_all_b inc,
362 cs_incident_statuses_b ists
363 WHERE inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
364 AND NVL(ists.CLOSE_FLAG,'N')= 'N'
365 AND inc.incident_id = jt.source_object_id
366 );
367
368
369 l_task_assign_rec l_task_assign_csr% ROWTYPE;
370
371 BEGIN
372 CSM_UTIL_PKG.LOG('Entering spawn_task_assignment_ins for user_id: ' || p_user_id,
373 'CSM_USER_EVENT_PKG.spawn_task_assignment_ins',FND_LOG.LEVEL_PROCEDURE);
374
375 -- loop to spawn process
376 FOR l_task_assign_rec IN l_task_assign_csr(p_user_id) LOOP
377 csm_task_assignment_event_pkg.task_assignment_initializer(p_task_assignment_id=>l_task_assign_rec.task_assignment_id,
378 p_error_msg=>l_error_msg,
379 x_return_status=>l_return_status);
380 END LOOP;
381
382 CSM_UTIL_PKG.LOG('Leaving spawn_task_assignment_ins for user_id: ' || p_user_id,
383 'CSM_USER_EVENT_PKG.spawn_task_assignment_ins',FND_LOG.LEVEL_PROCEDURE);
384 EXCEPTION
385 WHEN OTHERS THEN
386 l_sqlerrno := to_char(SQLCODE);
387 l_sqlerrmsg := substr(SQLERRM, 1,2000);
388 l_error_msg := ' Exception in spawn_task_assignment_ins for for user_id: ' || p_user_id
389 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
390 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_task_assignment_ins',FND_LOG.LEVEL_EXCEPTION);
391 RAISE;
392 END spawn_task_assignment_ins;
393
394 PROCEDURE items_acc_processor(p_user_id IN NUMBER)
395 IS
396 l_sqlerrno VARCHAR2(20);
397 l_sqlerrmsg VARCHAR2(4000);
398 l_error_msg VARCHAR2(4000);
399 l_return_status VARCHAR2(2000);
400 l_organization_id NUMBER;
401 l_category_id NUMBER;
402 l_category_set_id NUMBER;
403
404 BEGIN
405 CSM_UTIL_PKG.LOG('Entering items_acc_processor for user_id: ' || p_user_id,
406 'csm_user_event_pkg.items_acc_processor',FND_LOG.LEVEL_PROCEDURE);
407
408 l_organization_id := csm_profile_pkg.get_organization_id(p_user_id);
409 l_category_set_id := csm_profile_pkg.get_category_set_id(p_user_id);
410 l_category_id := csm_profile_pkg.get_category_id(p_user_id);
411
412 DELETE FROM csm_user_inventory_org WHERE user_id = p_user_id;
413
414 INSERT INTO csm_user_inventory_org (
415 user_id, organization_id, last_update_date, last_updated_by,
416 creation_date, created_by, category_set_id, category_id )
417 VALUES (
418 p_user_id, l_organization_id, SYSDATE, 1, SYSDATE, 1,
419 l_category_set_id, l_category_id );
420
421 -- get system items for the user - labor/expense
422 csm_system_item_event_pkg.get_new_user_system_items(p_user_id=>p_user_id);
423
424 -- get all mtl_system_items for the user
425 csm_mtl_system_items_event_pkg.get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>l_organization_id,
426 p_category_set_id=>l_category_set_id, p_category_id=>l_category_id);
427
428 CSM_UTIL_PKG.LOG('Leaving items_acc_processor for user_id: ' || p_user_id,
429 'csm_user_event_pkg.items_acc_processor',FND_LOG.LEVEL_PROCEDURE);
430 EXCEPTION
431 WHEN OTHERS THEN
432 l_sqlerrno := TO_CHAR(SQLCODE);
433 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
434 l_error_msg := ' Exception in items_acc_processor for user_id :'
435 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
436 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.items_acc_processor',FND_LOG.LEVEL_EXCEPTION);
437 RAISE;
438 END items_acc_processor;
439
440 PROCEDURE spawn_inv_loc_assignment_ins(p_resource_id IN NUMBER, p_user_id IN NUMBER)
441 IS
442 l_sqlerrno VARCHAR2(20);
443 l_sqlerrmsg VARCHAR2(4000);
444 l_error_msg VARCHAR2(4000);
445 l_return_status VARCHAR2(2000);
446
447 CURSOR l_csp_inv_loc_assignment_csr (p_resource_id NUMBER) IS
448 SELECT csp_inv_loc_assignment_id
449 FROM csp_inv_loc_assignments
450 WHERE resource_id = p_resource_id
451 AND resource_type = 'RS_EMPLOYEE'
452 AND SYSDATE BETWEEN nvl(effective_date_start, SYSDATE) AND nvl(effective_date_end, SYSDATE);
453
454 BEGIN
455 CSM_UTIL_PKG.LOG('Entering spawn_inv_loc_assignment_ins for user_id: ' || p_user_id,
456 'csm_user_event_pkg.spawn_inv_loc_assignment_ins',FND_LOG.LEVEL_PROCEDURE);
457
458 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
459 FOR r_csp_inv_loc_assignment_rec IN l_csp_inv_loc_assignment_csr(p_resource_id) LOOP
460 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT
461 (p_csp_inv_loc_assignment_id=>r_csp_inv_loc_assignment_rec.csp_inv_loc_assignment_id);
462 END LOOP;
463 END IF;
464
465 CSM_UTIL_PKG.LOG('Leaving spawn_inv_loc_assignment_ins for user_id: ' || p_user_id,
466 'csm_user_event_pkg.spawn_inv_loc_assignment_ins',FND_LOG.LEVEL_PROCEDURE);
467 EXCEPTION
468 WHEN OTHERS THEN
469 l_sqlerrno := TO_CHAR(SQLCODE);
470 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
471 l_error_msg := ' Exception in spawn_inv_loc_assignment_ins for user_id :'
472 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
473 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_inv_loc_assignment_ins',FND_LOG.LEVEL_EXCEPTION);
474 RAISE;
475 END spawn_inv_loc_assignment_ins;
476
477 PROCEDURE spawn_po_loc_ass_all_ins(p_resource_id IN NUMBER, p_user_id IN NUMBER)
478 IS
479 l_sqlerrno VARCHAR2(20);
480 l_sqlerrmsg VARCHAR2(4000);
481 l_error_msg VARCHAR2(4000);
482 l_return_status VARCHAR2(2000);
483
484 CURSOR l_po_loc_ass_all_csr (p_resource_id IN NUMBER)
485 IS
486 SELECT pla.location_id location_id,
487 pla.site_use_id site_use_id,
488 rcr.resource_id resource_id,
489 jtrs.user_id user_id,
490 hps.party_site_id party_site_id
491 FROM po_location_associations_all pla,
492 hz_cust_site_uses_all csu,
493 hz_cust_acct_sites_all cas,
494 csp_rs_cust_relations rcr,
495 jtf_rs_resource_extns jtrs,
496 hz_party_sites hps,
497 hz_locations hzl
498 WHERE csu.site_use_id = pla.site_use_id
499 AND csu.site_use_code = 'SHIP_TO'
500 AND csu.cust_acct_site_id = cas.cust_acct_site_id
501 AND csu.status = 'A'
502 AND cas.cust_account_id = rcr.customer_id
503 AND cas.status = 'A'
504 AND cas.party_site_id = hps.party_site_id
505 AND hps.location_id = hzl.location_id
506 AND jtrs.resource_id = rcr.resource_id
507 AND jtrs.resource_id = p_resource_id;
508
509 BEGIN
510 CSM_UTIL_PKG.LOG('Entering spawn_po_loc_ass_all_ins for user_id: ' || p_user_id,
511 'csm_user_event_pkg.spawn_po_loc_ass_all_ins',FND_LOG.LEVEL_PROCEDURE);
512
513 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
514
515 FOR r_po_loc_ass_all_rec IN l_po_loc_ass_all_csr(p_resource_id) LOOP
516 csm_party_site_event_pkg.party_sites_acc_i
517 (p_party_site_id=>r_po_loc_ass_all_rec.party_site_id,
518 p_user_id=>r_po_loc_ass_all_rec.user_id,
519 p_flowtype=>NULL,
520 p_error_msg=>l_error_msg,
521 x_return_status=>l_return_status);
522
523 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
524 (p_location_id=>r_po_loc_ass_all_rec.location_id,
525 p_site_use_id=>r_po_loc_ass_all_rec.site_use_id,
526 p_user_id=>r_po_loc_ass_all_rec.user_id);
527 END LOOP;
528
529 END IF;
530
531 CSM_UTIL_PKG.LOG('Leaving spawn_po_loc_ass_all_ins for user_id: ' || p_user_id,
532 'csm_user_event_pkg.spawn_po_loc_ass_all_ins',FND_LOG.LEVEL_PROCEDURE);
533 EXCEPTION
534 WHEN OTHERS THEN
535 l_sqlerrno := TO_CHAR(SQLCODE);
536 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
537 l_error_msg := ' Exception in spawn_po_loc_ass_all_ins for user_id :'
538 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
539 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_po_loc_ass_all_ins',FND_LOG.LEVEL_EXCEPTION);
540 RAISE;
541 END spawn_po_loc_ass_all_ins;
542
543 PROCEDURE spawn_csp_req_headers_ins(p_resource_id IN NUMBER, p_user_id IN NUMBER)
544 IS
545 l_sqlerrno VARCHAR2(20);
546 l_sqlerrmsg VARCHAR2(4000);
547 l_error_msg VARCHAR2(4000);
548 l_return_status VARCHAR2(2000);
549
550 CURSOR l_req_headers_csr (p_resource_id NUMBER)
551 IS
552 SELECT hdr.requirement_header_id,
553 jtrs.resource_id,
554 jtrs.user_id
555 FROM csp_requirement_headers hdr,
556 jtf_rs_resource_extns jtrs
557 WHERE hdr.resource_id = jtrs.resource_id
558 AND jtrs.resource_id = p_resource_id
559 AND NOT EXISTS
560 (SELECT 1
561 FROM csm_req_headers_acc acc
562 WHERE acc.requirement_header_id = hdr.requirement_header_id
563 AND acc.user_id = jtrs.user_id
564 );
565
566 BEGIN
567 CSM_UTIL_PKG.LOG('Entering spawn_csp_req_headers_ins for user_id: ' || p_user_id,
568 'csm_user_event_pkg.spawn_csp_req_headers_ins',FND_LOG.LEVEL_PROCEDURE);
569
570 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
571
572 FOR r_req_headers_rec IN l_req_headers_csr(p_resource_id) LOOP
573 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_i
574 (p_requirement_header_id=>r_req_headers_rec.requirement_header_id,
575 p_user_id=>r_req_headers_rec.user_id);
576 END LOOP;
577
578 END IF;
579
580 CSM_UTIL_PKG.LOG('Leaving spawn_csp_req_headers_ins for user_id: ' || p_user_id,
581 'csm_user_event_pkg.spawn_csp_req_headers_ins',FND_LOG.LEVEL_PROCEDURE);
582 EXCEPTION
583 WHEN OTHERS THEN
584 l_sqlerrno := TO_CHAR(SQLCODE);
585 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
586 l_error_msg := ' Exception in spawn_csp_req_headers_ins for user_id :'
587 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
588 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_csp_req_headers_ins',FND_LOG.LEVEL_EXCEPTION);
589 RAISE;
590 END spawn_csp_req_headers_ins;
591
592 PROCEDURE spawn_csp_req_lines_ins(p_resource_id IN NUMBER, p_user_id IN NUMBER)
593 IS
594 l_sqlerrno VARCHAR2(20);
595 l_sqlerrmsg VARCHAR2(4000);
596 l_error_msg VARCHAR2(4000);
597 l_return_status VARCHAR2(2000);
598
599 CURSOR l_req_lines_csr(p_resource_id IN NUMBER )
600 IS
601 SELECT hdr.requirement_header_id,
602 hdr.resource_id,
603 line.requirement_line_id,
604 jtrs.user_id
605 FROM csp_requirement_headers hdr,
606 csp_requirement_lines line,
607 jtf_rs_resource_extns jtrs
608 WHERE hdr.requirement_header_id = line.requirement_header_id
609 AND hdr.resource_id = jtrs.resource_id
610 AND jtrs.resource_id = p_resource_id
611 AND NOT EXISTS
612 (SELECT 1
613 FROM csm_req_lines_acc acc
614 WHERE acc.requirement_line_id = line.requirement_line_id
615 AND acc.user_id = jtrs.user_id
616 );
617
618 BEGIN
619 CSM_UTIL_PKG.LOG('Entering spawn_csp_req_lines_ins for user_id: ' || p_user_id,
620 'csm_user_event_pkg.spawn_csp_req_lines_ins',FND_LOG.LEVEL_PROCEDURE);
621
622 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
623
624 FOR r_req_lines_rec IN l_req_lines_csr(p_resource_id) LOOP
625 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_i
626 (p_requirement_line_id=>r_req_lines_rec.requirement_line_id,
627 p_user_id=>r_req_lines_rec.user_id);
628 END LOOP;
629
630 END IF;
631
632 CSM_UTIL_PKG.LOG('Leaving spawn_csp_req_lines_ins for user_id: ' || p_user_id,
633 'csm_user_event_pkg.spawn_csp_req_lines_ins',FND_LOG.LEVEL_PROCEDURE);
634 EXCEPTION
635 WHEN OTHERS THEN
636 l_sqlerrno := TO_CHAR(SQLCODE);
637 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
638 l_error_msg := ' Exception in spawn_csp_req_lines_ins for user_id :'
639 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
640 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_csp_req_lines_ins',FND_LOG.LEVEL_EXCEPTION);
641 RAISE;
642 END spawn_csp_req_lines_ins;
643
644 PROCEDURE enable_user_pub_synch(p_user_id IN NUMBER)
645 IS
646 l_sqlerrno VARCHAR2(20);
647 l_sqlerrmsg VARCHAR2(4000);
648 l_error_msg VARCHAR2(4000);
649 l_return_status VARCHAR2(2000);
650
651 BEGIN
652 CSM_UTIL_PKG.LOG('Entering enable_user_pub_synch for user_id:' || p_user_id,
653 'csm_user_event_pkg.enable_user_pub_synch',FND_LOG.LEVEL_PROCEDURE);
654
655 asg_helper.enable_user_pub_synch(p_user_id, 'SERVICEP');
656
657 CSM_UTIL_PKG.LOG('Leaving enable_user_pub_synch for user_id:' || p_user_id,
658 'csm_user_event_pkg.enable_user_pub_synch',FND_LOG.LEVEL_PROCEDURE);
659 EXCEPTION
660 WHEN OTHERS THEN
661 l_sqlerrno := to_char(SQLCODE);
662 l_sqlerrmsg := substr(SQLERRM, 1,2000);
663 l_error_msg := ' Exception in enable_user_pub_synch for for user_id: ' || p_user_id
664 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
665 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.enable_user_pub_synch',FND_LOG.LEVEL_EXCEPTION);
666 RAISE;
667 END enable_user_pub_synch;
668
669 PROCEDURE user_resp_ins_initializer (p_responsibility_id IN NUMBER, p_user_id IN NUMBER)
670 IS
671 l_sqlerrno VARCHAR2(20);
672 l_sqlerrmsg VARCHAR2(4000);
673 l_error_msg VARCHAR2(4000);
674 l_return_code NUMBER;
675 l_return_status VARCHAR2(2000);
676 l_resource_id NUMBER;
677
678 CURSOR l_get_resource_id_csr(p_user_id IN NUMBER)
679 IS
680 SELECT resource_id
681 FROM asg_user
682 WHERE user_id = p_user_id;
683
684 BEGIN
685 CSM_UTIL_PKG.LOG('Entering USER_RESP_INS_INITIALIZER for user_id:' || p_user_id,
686 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
687
688 -- check if responsibility is a MFS responsibility
689 IF NOT is_omfs_palm_responsibility(p_responsibility_id=>p_responsibility_id,
690 p_user_id=>p_user_id) THEN
691 CSM_UTIL_PKG.LOG('Responsibility id: ' || p_responsibility_id || ' not a valid MFS responsibility for user_id:' || p_user_id,
692 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_EXCEPTION);
693 RETURN;
694 END IF;
695
696 -- get resource_id of the user
697 OPEN l_get_resource_id_csr(p_user_id);
698 FETCH l_get_resource_id_csr INTO l_resource_id;
699 CLOSE l_get_resource_id_csr;
700
701 -- disable user sync
702 csm_user_event_pkg.disable_user_pub_synch(p_user_id=>p_user_id);
703 --Inserting the user into the Access table
704 CSM_USER_EVENT_PKG.INSERT_ACC (p_user_id =>p_user_id
705 ,x_return_status =>l_return_status
706 ,x_error_message =>l_error_msg);
707
708 -- if first user download all common lookup info
709 IF is_first_omfs_palm_user(p_user_id=>p_user_id) THEN
710 csm_concurrent_jobs_pkg.refresh_all_acc(x_retcode=>l_return_code,
711 x_return_status=>l_return_status);
712 END IF;
713
714 -- download profiles info
715 -- setup profile info first as they are need in other procedures
716 csm_profile_event_pkg.refresh_user_acc(p_user_id=>p_user_id);
717
718 -- Insert the user's group
719 CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP (p_user_id=>p_user_id
720 ,x_return_status=>l_return_status
721 , x_error_message => l_error_msg);
722
723 -- spawn task ins
724 csm_user_event_pkg.spawn_task_ins(p_user_id=>p_user_id);
725
726 -- spawn incident ins
727 csm_user_event_pkg.spawn_incident_ins(p_user_id=>p_user_id);
728
729 -- spawn task assignment ins
730 csm_user_event_pkg.spawn_task_assignment_ins(p_user_id=>p_user_id);
731
732 --spawn personalizations for CSM_CUSTOMIZATION_VIEWS,CSM_NEW_MESSAGES,CSM_PAGE_PERZ_DELTA
733 spawn_perz_ins(p_user_id=>p_user_id);
734
735 --spawn CSM_DASHBOARD_SEARCH_COLS
736 spawn_dashboard_srch_cols_ins(p_user_id=>p_user_id);
737
738 --spawn CSM_FND_LOBS_ACC
739 CSM_LOBS_EVENT_PKG.Insert_all_acc_records(p_user_id=>p_user_id);
740
741 -- download resources belonging to member's group
742 csm_resource_extns_event_pkg.resource_extns_acc_processor(p_resource_id=>l_resource_id,
743 p_user_id=>p_user_id);
744
745 -- download notifications for user sender/recipient
746 csm_notification_event_pkg.notifications_acc_processor(p_user_id=>p_user_id);
747
748 -- download state transitions, moved to concurrent program
749 -- csm_state_transition_event_pkg.Refresh_Acc(p_user_id=>p_user_id);
750
751
752 -- download labor,expense and other mtl_system_items
753 csm_user_event_pkg.items_acc_processor(p_user_id=>p_user_id);
754
755 -- download csp_inv_loc_assignments
756 csm_user_event_pkg.spawn_inv_loc_assignment_ins(p_resource_id=>l_resource_id,
757 p_user_id=>p_user_id);
758
759 -- download po_location_assignments
760 csm_user_event_pkg.spawn_po_loc_ass_all_ins(p_resource_id=>l_resource_id,
761 p_user_id=>p_user_id);
762
763 -- download csp_req_headers
764 csm_user_event_pkg.spawn_csp_req_headers_ins(p_resource_id=>l_resource_id,
765 p_user_id=>p_user_id);
766
767 -- download csp_req_lines
768 csm_user_event_pkg.spawn_csp_req_lines_ins(p_resource_id=>l_resource_id,
769 p_user_id=>p_user_id);
770
771 --Bug 5048151 - spawn Parts Transfer
772 spawn_mat_txn (p_user_id=>p_user_id);
773
774 --download serial numbers
775 spawn_mtl_serial_numbers(p_resource_id=>l_resource_id,
776 p_user_id=>p_user_id);
777
778
779 -- enable user sync
780 csm_user_event_pkg.enable_user_pub_synch(p_user_id=>p_user_id);
781
782 CSM_UTIL_PKG.LOG('Leaving USER_RESP_INS_INITIALIZER for user_id:' || p_user_id,
783 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
784 EXCEPTION
785 WHEN OTHERS THEN
786 l_sqlerrno := to_char(SQLCODE);
787 l_sqlerrmsg := substr(SQLERRM, 1,2000);
788 l_error_msg := ' Exception in USER_RESP_INS_INITIALIZER for for user_id: ' || p_user_id
789 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
790 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_EXCEPTION);
791 RAISE;
792 END USER_RESP_INS_INITIALIZER;
793
794 PROCEDURE user_del_init(p_user_id IN NUMBER)
795 IS
796 l_sqlerrno VARCHAR2(20);
797 l_sqlerrmsg VARCHAR2(4000);
798 l_error_msg VARCHAR2(4000);
799 l_return_status VARCHAR2(2000);
800
801 BEGIN
802 CSM_UTIL_PKG.LOG('Entering user_del_init for user_id:' || p_user_id,
803 'csm_user_event_pkg.user_del_init',FND_LOG.LEVEL_PROCEDURE);
804
805 -- purge all ACC tables
806 csm_user_event_pkg.purge_all_acc_tables(p_user_id=>p_user_id);
807
808 CSM_UTIL_PKG.LOG('Leaving user_del_init for user_id:' || p_user_id,
809 'csm_user_event_pkg.user_del_init',FND_LOG.LEVEL_PROCEDURE);
810 EXCEPTION
811 WHEN OTHERS THEN
812 l_sqlerrno := to_char(SQLCODE);
813 l_sqlerrmsg := substr(SQLERRM, 1,2000);
814 l_error_msg := ' Exception in user_del_init for for user_id: ' || p_user_id
815 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
816 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.user_del_init',FND_LOG.LEVEL_EXCEPTION);
817 RAISE;
818 END user_del_init;
819
820 PROCEDURE purge_all_acc_tables(p_user_id IN NUMBER)
821 IS
822 l_sqlerrno VARCHAR2(20);
823 l_sqlerrmsg VARCHAR2(4000);
824 l_error_msg VARCHAR2(4000);
825 l_return_status VARCHAR2(2000);
826
827 BEGIN
828 CSM_UTIL_PKG.LOG('Entering purge_all_acc_tables for user_id:' || p_user_id,
829 'csm_user_event_pkg.purge_all_acc_tables',FND_LOG.LEVEL_PROCEDURE);
830
831 DELETE FROM CSM_DEBRIEF_LINES_ACC WHERE user_id = p_user_id;
832 DELETE FROM CSM_DEBRIEF_HEADERS_ACC WHERE user_id = p_user_id;
833 DELETE FROM CSM_ITEM_INSTANCES_ACC WHERE user_id = p_user_id;
834 DELETE FROM CSM_NOTES_ACC WHERE user_id = p_user_id;
835 DELETE FROM CSM_NOTIFICATIONS_ACC WHERE user_id = p_user_id;
836
837 DELETE FROM CSM_PARTIES_ACC WHERE user_id = p_user_id;
838 DELETE FROM CSM_PARTY_SITES_ACC WHERE user_id = p_user_id;
839 DELETE FROM csm_profile_option_values_acc WHERE user_id = p_user_id;
840
841 DELETE FROM CSM_RS_RESOURCE_EXTNS_ACC WHERE user_id = p_user_id;
842 --Bug 5236469
843 csm_resource_extns_event_pkg.RESOURCE_EXTNS_ACC_CLEANUP(p_user_id);
844
845 DELETE FROM CSM_TASK_ASSIGNMENTS_ACC WHERE user_id = p_user_id;
846
847 DELETE FROM CSM_TASKS_ACC WHERE user_id = p_user_id;
848 DELETE FROM CSM_INCIDENTS_ALL_ACC WHERE user_id = p_user_id;
849 DELETE FROM CSM_SR_CONTACTS_ACC WHERE user_id = p_user_id;
850 DELETE FROM CSM_CUSTOMIZATION_VIEWS_ACC WHERE user_id = p_user_id;
851 DELETE FROM CSM_PAGE_PERZ_DELTA_ACC WHERE user_id = p_user_id;
852
853 DELETE FROM CSM_NEW_MESSAGES_ACC WHERE user_id = p_user_id;
854 DELETE FROM CSM_STATE_TRANSITIONS_ACC WHERE user_id = p_user_id;
855 DELETE FROM csm_mtl_system_items_acc WHERE user_id = p_user_id;
856 DELETE FROM csm_mtl_serial_numbers_acc WHERE user_id = p_user_id;
857 DELETE FROM csm_mtl_material_txn_acc WHERE user_id = p_user_id;
858
859 DELETE FROM csm_mtl_txn_lot_num_acc WHERE user_id = p_user_id;
860 DELETE FROM csm_mtl_unit_txn_acc WHERE user_id = p_user_id;
861 DELETE FROM csm_inv_loc_ass_acc WHERE user_id = p_user_id;
862 DELETE FROM csm_po_loc_ass_all_acc WHERE user_id = p_user_id;
863 DELETE FROM csm_req_lines_acc WHERE user_id = p_user_id;
864
865 DELETE FROM csm_req_headers_acc WHERE user_id = p_user_id;
866 DELETE FROM csm_system_items_acc WHERE user_id = p_user_id;
867 DELETE FROM csm_mtl_item_subinv_acc WHERE user_id = p_user_id;
868 DELETE FROM csm_mtl_sec_inv_acc WHERE user_id = p_user_id;
869 DELETE FROM csm_ii_relationships_acc WHERE user_id = p_user_id;
870
871 DELETE FROM csm_contr_headers_acc WHERE user_id = p_user_id;
872 DELETE FROM csm_contr_buss_processes_acc WHERE user_id = p_user_id;
873 DELETE FROM csm_contr_buss_txn_types_acc WHERE user_id = p_user_id;
874 --DELETE FROM csm_unit_of_measure_tl_acc WHERE user_id = p_user_id;
875 DELETE FROM csm_service_history_acc WHERE user_id = p_user_id;
876 DELETE FROM csm_debrief_headers_acc WHERE user_id = p_user_id;
877
878 DELETE FROM csm_counters_acc WHERE user_id = p_user_id;
879 DELETE FROM csm_counter_values_acc WHERE user_id = p_user_id;
880 DELETE FROM csm_mtl_item_locations_acc WHERE user_id = p_user_id;
881 DELETE FROM csm_mtl_secondary_locators_acc WHERE user_id = p_user_id;
882 DELETE FROM csm_mtl_onhand_qty_acc WHERE user_id = p_user_id;
883
884 DELETE FROM csm_user_inventory_org WHERE user_id = p_user_id;
885 DELETE FROM CSM_FND_LOBS_ACC WHERE user_id = p_user_id;--Bug 4938130
886 DELETE FROM CSM_HZ_CUST_ACCOUNTS_ACC WHERE user_id = p_user_id;--Bug 5213097
887 DELETE FROM CSM_SR_TYPE_MAPPING_ACC WHERE user_id = p_user_id;--Bug 5213097
888 DELETE FROM CSM_CSI_ITEM_ATTR_ACC WHERE user_id = p_user_id;--Bug 5213097
889 DELETE FROM CSM_HZ_LOCATIONS_ACC WHERE user_id = p_user_id;--Bug 5213097
890
891 DELETE FROM CSM_HZ_CONTACT_POINTS_ACC WHERE user_id = p_user_id;
892 DELETE FROM CSM_COUNTER_PROP_VALUES_ACC WHERE user_id = p_user_id;
893 DELETE FROM CSM_COUNTER_PROPERTIES_ACC WHERE user_id = p_user_id;
894 DELETE FROM CSM_HZ_RELATIONSHIPS_ACC WHERE user_id = p_user_id;
895 DELETE FROM CSM_PARTY_ASSIGNMENT WHERE user_id = p_user_id;
896
897 --Delete the Group of the User
898 CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP (p_user_id=>p_user_id
899 ,x_return_status=>l_return_status
900 , x_error_message => l_error_msg);
901 --Deleting User from the Access table
902 CSM_USER_EVENT_PKG.DELETE_ACC (p_user_id =>p_user_id
903 ,x_return_status =>l_return_status
904 ,x_error_message =>l_error_msg);
905
906 CSM_UTIL_PKG.LOG('Leaving purge_all_acc_tables for user_id:' || p_user_id,
907 'csm_user_event_pkg.purge_all_acc_tables',FND_LOG.LEVEL_PROCEDURE);
908 EXCEPTION
909 WHEN OTHERS THEN
910 l_sqlerrno := to_char(SQLCODE);
911 l_sqlerrmsg := substr(SQLERRM, 1,2000);
912 l_error_msg := ' Exception in purge_all_acc_tables for for user_id: ' || p_user_id
913 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
914 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.purge_all_acc_tables',FND_LOG.LEVEL_EXCEPTION);
915 RAISE;
916 END purge_all_acc_tables;
917
918 --Populate PIs related to parts transfer
919 PROCEDURE spawn_mat_txn(p_user_id IN NUMBER)
920 IS
921 l_sqlerrno VARCHAR2(20);
922 l_sqlerrmsg VARCHAR2(4000);
923 l_msg VARCHAR2(4000);
924 l_status VARCHAR2(40);
925 BEGIN
926 CSM_UTIL_PKG.LOG('Entering spawn_mat_txn for user_id: ' || p_user_id,
927 'CSM_USER_EVENT_PKG.spawn_mat_txn',FND_LOG.LEVEL_PROCEDURE);
928
929 CSM_MTL_MATERIAL_TXN_ACC_PKG.get_new_user_mat_txn(p_user_id);
930
931 CSM_UTIL_PKG.LOG('Leaving spawn_mat_txn for user_id: ' || p_user_id,
932 'CSM_USER_EVENT_PKG.spawn_mat_txn',FND_LOG.LEVEL_PROCEDURE);
933 EXCEPTION
934 WHEN OTHERS THEN
935 l_sqlerrno := to_char(SQLCODE);
936 l_sqlerrmsg := substr(SQLERRM, 1,2000);
937 l_msg := ' Exception in spawn_perz_ins for for user_id: ' || p_user_id
938 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
939 CSM_UTIL_PKG.LOG(l_msg, 'csm_user_event_pkg.spawn_perz_ins',FND_LOG.LEVEL_EXCEPTION);
940 RAISE;
941 END spawn_mat_txn;
942
943 --PROCEDURE TO DOWNLOAD SERIAL NUMBERS DURING USER CREATION.
944 PROCEDURE spawn_mtl_serial_numbers(p_resource_id IN NUMBER, p_user_id IN NUMBER)
945 IS
946 l_organization_id NUMBER;
947 l_sqlerrno VARCHAR(20);
948 l_sqlerrmsg VARCHAR(4000);
949 l_error_msg VARCHAR(4000);
950
951 BEGIN
952 CSM_UTIL_PKG.LOG('Entering spawn_mtl_serial_numbers for User_id ' || p_user_id,
953 'CSM_USER_EVENT_PKG.spawn_mtl_serial_numbers',FND_LOG.LEVEL_PROCEDURE);
954
955 l_organization_id := csm_profile_pkg.get_organization_id(p_user_id);
956
957 --get all the serial numbers for the User
958 CSM_SERIAL_NUMBERS_EVENT_PKG.insert_mtl_serial_numbers(l_organization_id,TO_DATE('1','J'),p_resource_id,p_user_id);
959
960 CSM_UTIL_PKG.LOG('Leaving spawn_mtl_serial_numbers for User_id ' || p_user_id,
961 'CSM_USER_EVENT_PKG.spawn_mtl_serial_numbers',FND_LOG.LEVEL_PROCEDURE);
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 l_sqlerrno := TO_CHAR(SQLCODE);
966 l_sqlerrmsg := TO_CHAR(SQLERRM);
967 l_error_msg := 'Exception in spawn_mtl_serial_numbers for User_id '
968 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
969
970 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.spawn_mtl_serial_numbers',FND_LOG.LEVEL_EXCEPTION);
971 RAISE;
972 END spawn_mtl_serial_numbers;
973
974 PROCEDURE INSERT_ACC (p_user_id IN NUMBER
975 ,x_return_status OUT NOCOPY VARCHAR2
976 , x_error_message OUT NOCOPY VARCHAR2)
977 IS
978 l_sqlerrno VARCHAR(20);
979 l_sqlerrmsg VARCHAR(4000);
980 l_error_msg VARCHAR(4000);
981 l_user_id NUMBER;
982 l_owner_id NUMBER;
983 l_group_id NUMBER;
984 l_access_id NUMBER;
985 l_markdirty BOOLEAN;
986 l_tab_user_id asg_download.user_list;
987 l_tab_owner_id asg_download.user_list;
988 l_tab_access_id asg_download.access_list;
989 g_pub_item varchar2(30) := 'CSF_M_USER';
990
991 CURSOR c_asg_user(l_user_id NUMBER)
992 IS
993 SELECT USER_ID,OWNER_ID,GROUP_ID
994 FROM
995 ASG_USER
996 WHERE USER_ID=l_user_id;
997
998 CURSOR c_user_acc(l_user_id NUMBER)
999 IS
1000 SELECT USER_ID
1001 FROM
1002 CSM_USER_ACC
1003 WHERE USER_ID=l_user_id;
1004
1005 CURSOR c_group_users(l_owner_id NUMBER)
1006 IS
1007 SELECT USR.USER_ID,USR.OWNER_ID,ACC.ACCESS_ID
1008 FROM ASG_USER USR,
1009 CSM_USER_ACC ACC
1010 WHERE USR.OWNER_ID=l_owner_id
1011 AND USR.USER_ID = ACC.USER_ID;
1012
1013 BEGIN
1014 CSM_UTIL_PKG.LOG('Entering INSERT_ACC for User_id ' || p_user_id,
1015 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1016 OPEN c_asg_user(p_user_id);
1017 FETCH c_asg_user INTO l_user_id,l_owner_id,l_group_id;
1018 CLOSE c_asg_user;
1019
1020 OPEN c_user_acc(p_user_id);
1021 FETCH c_user_acc INTO l_user_id;
1022 IF c_user_acc%FOUND THEN
1023 DELETE FROM CSM_USER_ACC WHERE USER_ID =l_user_id;
1024 END IF;
1025
1026 SELECT CSM_USER_ACC_S.NEXTVAL INTO l_access_id FROM DUAL;
1027
1028 INSERT INTO CSM_USER_ACC(ACCESS_ID,USER_ID,OWNER_ID,COUNTER,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
1029 VALUES (l_access_id,l_user_id,l_owner_id,1,sysdate,1,sysdate,1,1);
1030 CLOSE c_user_acc;
1031
1032 l_markdirty := asg_download.mark_dirty(
1033 p_pub_item => g_pub_item
1034 , p_accessid => l_access_id
1035 , p_userid => l_owner_id
1036 , p_dml => 'I'
1037 , P_TIMESTAMP => sysdate
1038 );
1039 CSM_UTIL_PKG.LOG('User is inserted into the Access table: User_id ' || p_user_id,
1040 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1041 --If the user is a owner
1042 IF l_user_id = l_owner_id AND l_group_id IS NOT NULL THEN
1043 OPEN c_group_users(l_owner_id);
1044 LOOP
1045 IF l_tab_user_id.COUNT > 0 THEN
1046 l_tab_user_id.DELETE;
1047 END IF;
1048 IF l_tab_owner_id.COUNT > 0 THEN
1049 l_tab_owner_id.DELETE;
1050 END IF;
1051 IF l_tab_access_id.COUNT > 0 THEN
1052 l_tab_access_id.DELETE;
1053 END IF;
1054
1055 FETCH c_group_users BULK COLLECT INTO l_tab_user_id,l_tab_owner_id,l_tab_access_id LIMIT 10;
1056 EXIT WHEN l_tab_access_id.COUNT = 0;
1057 FOR i in 1..l_tab_access_id.COUNT
1058 LOOP
1059 UPDATE CSM_USER_ACC
1060 SET OWNER_ID = l_tab_owner_id(i),
1061 LAST_UPDATE_DATE = SYSDATE
1062 WHERE USER_ID = l_tab_user_id(i);
1063
1064 l_markdirty := asg_download.mark_dirty(
1065 p_pub_item => g_pub_item
1066 , p_accessid => l_tab_access_id(i)
1067 , p_userid => l_tab_owner_id(i)
1068 , p_dml => 'U'
1069 , P_TIMESTAMP => sysdate
1070 );
1071
1072 END LOOP;
1073
1074 END LOOP;
1075 CLOSE c_group_users;
1076
1077 END IF;
1078 CSM_UTIL_PKG.LOG('Leaving INSERT_ACC for User_id ' || p_user_id,
1079 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 l_sqlerrno := TO_CHAR(SQLCODE);
1083 l_sqlerrmsg := TO_CHAR(SQLERRM);
1084 l_error_msg := 'Exception in INSERT_ACC for User_id '
1085 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1086
1087 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);
1088 RAISE;
1089 END INSERT_ACC;
1090
1091 PROCEDURE DELETE_ACC (p_user_id IN NUMBER
1092 ,x_return_status OUT NOCOPY VARCHAR2
1093 , x_error_message OUT NOCOPY VARCHAR2)
1094 IS
1095 l_sqlerrno VARCHAR(20);
1096 l_sqlerrmsg VARCHAR(4000);
1097 l_error_msg VARCHAR(4000);
1098 l_user_id NUMBER;
1099 l_owner_id NUMBER;
1100 l_access_id NUMBER;
1101 l_markdirty BOOLEAN;
1102 g_pub_item varchar2(30) := 'CSF_M_USER';
1103
1104 CURSOR c_user_acc(l_user_id NUMBER)
1105 IS
1106 SELECT ACCESS_ID,USER_ID,OWNER_ID
1107 FROM
1108 CSM_USER_ACC
1109 WHERE USER_ID=l_user_id;
1110
1111
1112 BEGIN
1113 CSM_UTIL_PKG.LOG('Entering DELETE_ACC for User_id ' || p_user_id,
1114 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1115
1116 OPEN c_user_acc(p_user_id);
1117 FETCH c_user_acc INTO l_access_id,l_user_id,l_owner_id;
1118 --Do mark dirty only for the User
1119 IF c_user_acc%FOUND THEN
1120 IF l_user_id <> l_owner_id THEN --Do not do mark dirty for individual users and owners
1121 l_markdirty := asg_download.mark_dirty(
1122 p_pub_item => g_pub_item
1123 , p_accessid => l_access_id
1124 , p_userid => l_owner_id
1125 , p_dml => 'D'
1126 , P_TIMESTAMP => sysdate
1127 );
1128 END IF;
1129 --delete for owner,member or individual user
1130 DELETE FROM CSM_USER_ACC WHERE USER_ID =l_user_id;
1131
1132 CSM_UTIL_PKG.LOG('User is Deleted From the Access table for User_id :' || p_user_id,
1133 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1134
1135 END IF;
1136
1137 CSM_UTIL_PKG.LOG('Leaving DELETE_ACC for User_id ' || p_user_id,
1138 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 l_sqlerrno := TO_CHAR(SQLCODE);
1142 l_sqlerrmsg := TO_CHAR(SQLERRM);
1143 l_error_msg := 'Exception in DELETE_ACC for User_id '
1144 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1145
1146 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);
1147 RAISE;
1148 END DELETE_ACC;
1149
1150 /*
1151 The following two apis are used for inserting/deleting
1152 non-group member records in CSM_USER_ACC for the (user,owner)
1153 */
1154
1155 PROCEDURE INSERT_ACC (p_user_id IN NUMBER,p_owner_id IN NUMBER)
1156 IS
1157 l_sqlerrno VARCHAR(20);
1158 l_sqlerrmsg VARCHAR(4000);
1159 l_error_msg VARCHAR(4000);
1160 l_access_id NUMBER;
1161 l_markdirty BOOLEAN;
1162
1163
1164 g_pub_item varchar2(30) := 'CSF_M_USER';
1165
1166
1167 CURSOR c_user_acc(b_user_id NUMBER,b_owner_id NUMBER)
1168 IS
1169 SELECT ACCESS_ID
1170 FROM CSM_USER_ACC
1171 WHERE USER_ID=b_user_id
1172 AND OWNER_ID = b_owner_id;
1173
1174 BEGIN
1175 CSM_UTIL_PKG.LOG('Entering INSERT_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
1176 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1177
1178 OPEN c_user_acc(p_user_id,p_owner_id);
1179 FETCH c_user_acc INTO l_access_id;
1180 IF c_user_acc%FOUND THEN
1181 CSM_UTIL_PKG.LOG('Record already there in the Access table: (User_id,owner_id) : (' || p_user_id||','||p_owner_id||')',
1182 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1183 UPDATE CSM_USER_ACC SET COUNTER=COUNTER+1 ,LAST_UPDATE_DATE=sysdate WHERE ACCESS_ID=l_access_id;
1184 CLOSE c_user_acc;
1185 ELSE
1186
1187 CLOSE c_user_acc;
1188
1189 SELECT CSM_USER_ACC_S.NEXTVAL INTO l_access_id FROM DUAL;
1190
1191 INSERT INTO CSM_USER_ACC(ACCESS_ID,USER_ID,OWNER_ID,COUNTER,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
1192 VALUES (l_access_id,p_user_id,p_owner_id,1,sysdate,1,sysdate,1,1);
1193
1194 CSM_UTIL_PKG.LOG('Record inserted into the Access table: (User_id,owner_id) : (' || p_user_id||','||p_owner_id||') and Marking dirty',
1195 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1196
1197 l_markdirty := asg_download.mark_dirty(
1198 p_pub_item => g_pub_item
1199 , p_accessid => l_access_id
1200 , p_userid => p_owner_id
1201 , p_dml => 'I'
1202 , P_TIMESTAMP => sysdate
1203 );
1204 END IF;
1205
1206 CSM_UTIL_PKG.LOG('Leaving INSERT_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 l_sqlerrno := TO_CHAR(SQLCODE);
1210 l_sqlerrmsg := TO_CHAR(SQLERRM);
1211 l_error_msg := 'Exception in INSERT_ACC for User_id '
1212 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1213
1214 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);
1215 RAISE;
1216 END INSERT_ACC;
1217
1218 PROCEDURE DELETE_ACC (p_user_id IN NUMBER,p_owner_id IN NUMBER)
1219 IS
1220 l_sqlerrno VARCHAR(20);
1221 l_sqlerrmsg VARCHAR(4000);
1222 l_error_msg VARCHAR(4000);
1223 l_access_id NUMBER;
1224 l_markdirty BOOLEAN;
1225 g_pub_item varchar2(30) := 'CSF_M_USER';
1226 l_counter NUMBER;
1227
1228 CURSOR c_user_acc(b_user_id NUMBER,b_owner_id NUMBER)
1229 IS
1230 SELECT access_id,counter
1231 FROM CSM_USER_ACC
1232 WHERE USER_ID=b_user_id
1233 AND OWNER_ID = b_owner_id;
1234
1235 BEGIN
1236 CSM_UTIL_PKG.LOG('Entering DELETE_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
1237 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1238
1239 OPEN c_user_acc(p_user_id,p_owner_id);
1240 FETCH c_user_acc INTO l_access_id,l_counter;
1241
1242 IF c_user_acc%FOUND THEN
1243 IF l_counter=1 THEN
1244 l_markdirty := asg_download.mark_dirty(
1245 p_pub_item => g_pub_item
1246 , p_accessid => l_access_id
1247 , p_userid => p_owner_id
1248 , p_dml => 'D'
1249 , P_TIMESTAMP => sysdate
1250 );
1251
1252 DELETE FROM CSM_USER_ACC WHERE USER_ID =p_user_id
1253 AND OWNER_ID=p_owner_id;
1254 ELSE
1255 UPDATE CSM_USER_ACC SET COUNTER=COUNTER-1 ,LAST_UPDATE_DATE=sysdate WHERE USER_ID =p_user_id
1256 AND OWNER_ID=p_owner_id;
1257 END IF;
1258 END IF;
1259
1260 CSM_UTIL_PKG.LOG('Leaving DELETE_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
1261 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 l_sqlerrno := TO_CHAR(SQLCODE);
1265 l_sqlerrmsg := TO_CHAR(SQLERRM);
1266 l_error_msg := 'Exception in DELETE_ACC for User_id '
1267 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1268
1269 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);
1270 RAISE;
1271 END DELETE_ACC;
1272
1273
1274 END CSM_USER_EVENT_PKG;