[Home] [Help]
PACKAGE BODY: APPS.CSM_USER_EVENT_PKG
Source
1 PACKAGE BODY CSM_USER_EVENT_PKG AS
2 /* $Header: csmeusrb.pls 120.32.12020000.2 2013/04/09 11:00:58 saradhak 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
422 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) THEN --no org based mtl item download for htm5 users
423 -- get all mtl_system_items for the user
424 csm_mtl_system_items_event_pkg.get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>l_organization_id,
425 p_category_set_id=>l_category_set_id, p_category_id=>l_category_id);
426 END IF;
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 ,
487 pla.org_id ,
488 decode(rcr.resource_type,'RS_GROUP','G','E') resource_type,
489 au.user_id user_id,
490 cas.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 asg_user au
496 WHERE csu.site_use_id = pla.site_use_id
497 AND csu.site_use_code = 'SHIP_TO'
498 AND csu.cust_acct_site_id = cas.cust_acct_site_id
499 AND csu.status = 'A'
500 AND cas.cust_account_id = rcr.customer_id
501 AND cas.status = 'A'
502 AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
503 OR
504 (rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE category='EMPLOYEE' AND resource_id = au.resource_id)))
505 AND au.resource_id = p_resource_id;
506
507 BEGIN
508 CSM_UTIL_PKG.LOG('Entering spawn_po_loc_ass_all_ins for user_id: ' || p_user_id,
509 'csm_user_event_pkg.spawn_po_loc_ass_all_ins',FND_LOG.LEVEL_PROCEDURE);
510
511 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
512
513 FOR r_po_loc_ass_all_rec IN l_po_loc_ass_all_csr(p_resource_id) LOOP
514 csm_party_site_event_pkg.party_sites_acc_i
515 (p_party_site_id=>r_po_loc_ass_all_rec.party_site_id,
516 p_user_id=>r_po_loc_ass_all_rec.user_id,
517 p_flowtype=>NULL,
518 p_error_msg=>l_error_msg,
519 x_return_status=>l_return_status);
520
521 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
522 (p_location_id=>r_po_loc_ass_all_rec.location_id,
523 p_org_id=>r_po_loc_ass_all_rec.org_id,
524 p_res_type=>r_po_loc_ass_all_rec.resource_type,
525 p_user_id=>r_po_loc_ass_all_rec.user_id);
526 END LOOP;
527
528 END IF;
529
530 CSM_UTIL_PKG.LOG('Leaving spawn_po_loc_ass_all_ins for user_id: ' || p_user_id,
531 'csm_user_event_pkg.spawn_po_loc_ass_all_ins',FND_LOG.LEVEL_PROCEDURE);
532 EXCEPTION
533 WHEN OTHERS THEN
534 l_sqlerrno := TO_CHAR(SQLCODE);
535 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
536 l_error_msg := ' Exception in spawn_po_loc_ass_all_ins for user_id :'
537 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
538 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_po_loc_ass_all_ins',FND_LOG.LEVEL_EXCEPTION);
539 RAISE;
540 END spawn_po_loc_ass_all_ins;
541
542 PROCEDURE spawn_csp_req_headers_ins(p_resource_id IN NUMBER, p_user_id IN NUMBER)
543 IS
544 l_sqlerrno VARCHAR2(20);
545 l_sqlerrmsg VARCHAR2(4000);
546 l_error_msg VARCHAR2(4000);
547 l_return_status VARCHAR2(2000);
548
549 CURSOR l_req_headers_csr (p_resource_id NUMBER)
550 IS
551 SELECT hdr.requirement_header_id,
552 au.resource_id,
553 au.user_id
554 FROM csp_requirement_headers hdr,
555 asg_user au
556 WHERE hdr.resource_id = au.resource_id
557 AND au.resource_id = p_resource_id
558 AND NOT EXISTS
559 (SELECT 1
560 FROM csm_req_headers_acc acc
561 WHERE acc.requirement_header_id = hdr.requirement_header_id
562 AND acc.user_id = au.user_id
563 );
564
565 BEGIN
566 CSM_UTIL_PKG.LOG('Entering spawn_csp_req_headers_ins for user_id: ' || p_user_id,
567 'csm_user_event_pkg.spawn_csp_req_headers_ins',FND_LOG.LEVEL_PROCEDURE);
568
569 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
570 FOR r_req_headers_rec IN l_req_headers_csr(p_resource_id) LOOP
571 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_i
572 (p_requirement_header_id=>r_req_headers_rec.requirement_header_id,
573 p_user_id=>r_req_headers_rec.user_id);
574 END LOOP;
575
576 END IF;
577
578 CSM_UTIL_PKG.LOG('Leaving spawn_csp_req_headers_ins for user_id: ' || p_user_id,
579 'csm_user_event_pkg.spawn_csp_req_headers_ins',FND_LOG.LEVEL_PROCEDURE);
580 EXCEPTION
581 WHEN OTHERS THEN
582 l_sqlerrno := TO_CHAR(SQLCODE);
583 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
584 l_error_msg := ' Exception in spawn_csp_req_headers_ins for user_id :'
585 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
586 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_csp_req_headers_ins',FND_LOG.LEVEL_EXCEPTION);
587 RAISE;
588 END spawn_csp_req_headers_ins;
589
590 PROCEDURE spawn_csp_req_lines_ins(p_resource_id IN NUMBER, p_user_id IN NUMBER)
591 IS
592 l_sqlerrno VARCHAR2(20);
593 l_sqlerrmsg VARCHAR2(4000);
594 l_error_msg VARCHAR2(4000);
595 l_return_status VARCHAR2(2000);
596
597 CURSOR l_req_lines_csr(p_resource_id IN NUMBER )
598 IS
599 SELECT hdr.requirement_header_id,
600 hdr.resource_id,
601 line.requirement_line_id,
602 au.user_id
603 FROM csp_requirement_headers hdr,
604 csp_requirement_lines line,
605 asg_user au
606 WHERE hdr.requirement_header_id = line.requirement_header_id
607 AND hdr.resource_id = au.resource_id
608 AND au.resource_id = p_resource_id
609 AND NOT EXISTS
610 (SELECT 1
611 FROM csm_req_lines_acc acc
612 WHERE acc.requirement_line_id = line.requirement_line_id
613 AND acc.user_id = au.user_id
614 );
615
616 BEGIN
617 CSM_UTIL_PKG.LOG('Entering spawn_csp_req_lines_ins for user_id: ' || p_user_id,
618 'csm_user_event_pkg.spawn_csp_req_lines_ins',FND_LOG.LEVEL_PROCEDURE);
619
620 IF csm_util_pkg.is_palm_resource(p_resource_id) THEN
621
622 FOR r_req_lines_rec IN l_req_lines_csr(p_resource_id) LOOP
623 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_i
624 (p_requirement_line_id=>r_req_lines_rec.requirement_line_id,
625 p_user_id=>r_req_lines_rec.user_id);
626 END LOOP;
627
628 END IF;
629
630 CSM_UTIL_PKG.LOG('Leaving spawn_csp_req_lines_ins for user_id: ' || p_user_id,
631 'csm_user_event_pkg.spawn_csp_req_lines_ins',FND_LOG.LEVEL_PROCEDURE);
632 EXCEPTION
633 WHEN OTHERS THEN
634 l_sqlerrno := TO_CHAR(SQLCODE);
635 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
636 l_error_msg := ' Exception in spawn_csp_req_lines_ins for user_id :'
637 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
638 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.spawn_csp_req_lines_ins',FND_LOG.LEVEL_EXCEPTION);
639 RAISE;
640 END spawn_csp_req_lines_ins;
641
642 PROCEDURE enable_user_pub_synch(p_user_id IN NUMBER)
643 IS
644 l_sqlerrno VARCHAR2(20);
645 l_sqlerrmsg VARCHAR2(4000);
646 l_error_msg VARCHAR2(4000);
647 l_return_status VARCHAR2(2000);
648
649 BEGIN
650 CSM_UTIL_PKG.LOG('Entering enable_user_pub_synch for user_id:' || p_user_id,
651 'csm_user_event_pkg.enable_user_pub_synch',FND_LOG.LEVEL_PROCEDURE);
652
653 asg_helper.enable_user_pub_synch(p_user_id, 'SERVICEP');
654
655 CSM_UTIL_PKG.LOG('Leaving enable_user_pub_synch for user_id:' || p_user_id,
656 'csm_user_event_pkg.enable_user_pub_synch',FND_LOG.LEVEL_PROCEDURE);
657 EXCEPTION
658 WHEN OTHERS THEN
659 l_sqlerrno := to_char(SQLCODE);
660 l_sqlerrmsg := substr(SQLERRM, 1,2000);
661 l_error_msg := ' Exception in enable_user_pub_synch for for user_id: ' || p_user_id
662 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
663 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.enable_user_pub_synch',FND_LOG.LEVEL_EXCEPTION);
664 RAISE;
665 END enable_user_pub_synch;
666
667 procedure spawn_html5_data(p_username in VARCHAR2)
668 is
669 l_req NUMBER:=1;
670 x CLOB; l_ret VARCHAR2(10); l_emsg VARCHAR2(4000);
671 begin
672 select count(1) into l_req from csm_html5_cache where user_name='CSM_CACHE_PVT' and rownum=1;
673 IF l_req=0 THEN
674 CSM_UTIL_PKG.LOG('Spawning HTML5 DDL Cache','csm_user_event_pkg.spawn_html5_data',FND_LOG.LEVEL_PROCEDURE);
675 CSM_HTML5_PKG.query_get_schema_js(x,l_ret,l_emsg);
676 END IF;
677
678 /* --CSM_HTML5_PKG.generate_html5_data(p_username); --not reqd since lookup data fills sdq and obviates this.*/
679
680 end spawn_html5_data;
681
682
683 PROCEDURE user_resp_ins_initializer (p_responsibility_id IN NUMBER, p_user_id IN NUMBER)
684 IS
685 l_sqlerrno VARCHAR2(20);
686 l_sqlerrmsg VARCHAR2(4000);
687 l_error_msg VARCHAR2(4000);
688 L_RETURN_CODE number;
689 L_RETURN_STATUS varchar2(2000);
690 L_RESOURCE_ID number;
691 L_HTML5_ENABLED varchar2(1);
692 l_username VARCHAR2(400);
693
694 CURSOR l_get_resource_id_csr(p_user_id IN NUMBER)
695 is
696 SELECT user_name,resource_id, NVL(multi_platform,'N')
697 FROM asg_user
698 WHERE user_id = p_user_id;
699
700 BEGIN
701 CSM_UTIL_PKG.LOG('Entering USER_RESP_INS_INITIALIZER for user_id:' || p_user_id,
702 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
703
704 -- check if responsibility is a MFS responsibility
705 IF NOT is_omfs_palm_responsibility(p_responsibility_id=>p_responsibility_id,
706 p_user_id=>p_user_id) THEN
707 CSM_UTIL_PKG.LOG('Responsibility id: ' || p_responsibility_id || ' not a valid MFS responsibility for user_id:' || p_user_id,
708 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_EXCEPTION);
709 RETURN;
710 END IF;
711
712 -- get resource_id of the user
713 open L_GET_RESOURCE_ID_CSR(P_USER_ID);
714 FETCH l_get_resource_id_csr INTO l_username,l_resource_id,L_HTML5_ENABLED;
715 CLOSE l_get_resource_id_csr;
716
717 -- disable user sync
718 csm_user_event_pkg.disable_user_pub_synch(p_user_id=>p_user_id);
719 --Inserting the user into the Access table
720 CSM_USER_EVENT_PKG.INSERT_ACC (p_user_id =>p_user_id
721 ,x_return_status =>l_return_status
722 ,x_error_message =>l_error_msg);
723
724 -- if first user download all common lookup info
725 IF is_first_omfs_palm_user(p_user_id=>p_user_id) THEN
726 csm_concurrent_jobs_pkg.refresh_all_acc(x_retcode=>l_return_code,
727 x_return_status=>l_return_status);
728 END IF;
729
730 -- download profiles info
731 -- setup profile info first as they are need in other procedures
732 csm_profile_event_pkg.refresh_user_acc(p_user_id=>p_user_id);
733
734
735 IF L_HTML5_ENABLED ='N' THEN --sr/task are not downloaded to created by or owners
736 -- spawn task ins
737 csm_user_event_pkg.spawn_task_ins(p_user_id=>p_user_id);
738
739 -- spawn incident ins
740 csm_user_event_pkg.spawn_incident_ins(p_user_id=>p_user_id);
741 END IF;
742
743 -- spawn task assignment ins
744 csm_user_event_pkg.spawn_task_assignment_ins(p_user_id=>p_user_id);
745
746 -- get system items for the user - labor/expense
747 csm_system_item_event_pkg.get_new_user_system_items(p_user_id=>p_user_id);
748
749 -- download mtl_system_items
750 csm_user_event_pkg.items_acc_processor(p_user_id=>p_user_id);
751
752 --download serial numbers
753 spawn_mtl_serial_numbers(p_resource_id=>l_resource_id,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 -- download notifications for user sender/recipient
772 csm_notification_event_pkg.notifications_acc_processor(p_user_id=>p_user_id);
773
774 --download task status transtions
775 CSM_STATE_TRANSITION_EVENT_PKG.Refresh_Acc_Ins(p_user_id,p_responsibility_id);
776
777 if L_HTML5_ENABLED ='N' then
778
779 -- download resources belonging to member's group
780 csm_resource_extns_event_pkg.resource_extns_acc_processor(p_resource_id=>l_resource_id,
781 p_user_id=>p_user_id);
782
783 --spawn personalizations for CSM_CUSTOMIZATION_VIEWS,CSM_NEW_MESSAGES,CSM_PAGE_PERZ_DELTA
784 spawn_perz_ins(p_user_id=>p_user_id);
785
786 -- Insert the user's group
787 CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP (p_user_id=>p_user_id
788 ,X_RETURN_STATUS=>L_RETURN_STATUS
789 , x_error_message => l_error_msg);
790 --spawn CSM_DASHBOARD_SEARCH_COLS
791 spawn_dashboard_srch_cols_ins(p_user_id=>p_user_id);
792
793 --spawn CSM_FND_LOBS_ACC
794 CSM_LOBS_EVENT_PKG.Insert_all_acc_records(p_user_id=>p_user_id);
795
796 -- download state transitions, moved to concurrent program
797 -- csm_state_transition_event_pkg.Refresh_Acc(p_user_id=>p_user_id);
798
799 --Bug 5048151 - spawn Parts Transfer
800 spawn_mat_txn (p_user_id=>p_user_id);
801
802 csm_auto_sync_nfn_pkg.auto_sync_nfn_acc_processor(p_user_id=>p_user_id);
803
804 csm_client_nfn_log_pkg.client_nfn_log_acc_processor(p_user_id=>p_user_id);
805
806 csm_auto_sync_log_pkg.auto_sync_log_acc_processor(p_user_id=>p_user_id);
807
808 csm_auto_sync_pkg.auto_sync_acc_processor(p_user_id=>p_user_id);
809
810 CSM_QUERY_EVENT_PKG.REFRESH_USER(P_USER_ID=>P_USER_ID);
811
812 ELSE
813
814 CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER(p_user_id); --this might be required from spawn_perz_ins
815 spawn_html5_data(l_username);
816
817 END IF;
818
819 -- enable user sync
820 csm_user_event_pkg.enable_user_pub_synch(p_user_id=>p_user_id);
821
822 CSM_UTIL_PKG.LOG('Leaving USER_RESP_INS_INITIALIZER for user_id:' || p_user_id,
823 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
824 EXCEPTION
825 WHEN OTHERS THEN
826 l_sqlerrno := to_char(SQLCODE);
827 l_sqlerrmsg := substr(SQLERRM, 1,2000);
828 l_error_msg := ' Exception in USER_RESP_INS_INITIALIZER for for user_id: ' || p_user_id
829 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
830 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_USER_EVENT_PKG.USER_RESP_INS_INITIALIZER',FND_LOG.LEVEL_EXCEPTION);
831 RAISE;
832 END USER_RESP_INS_INITIALIZER;
833
834 PROCEDURE user_del_init(p_user_id IN NUMBER)
835 IS
836 l_sqlerrno VARCHAR2(20);
837 l_sqlerrmsg VARCHAR2(4000);
838 l_error_msg VARCHAR2(4000);
839 l_return_status VARCHAR2(2000);
840
841 BEGIN
842 CSM_UTIL_PKG.LOG('Entering user_del_init for user_id:' || p_user_id,
843 'csm_user_event_pkg.user_del_init',FND_LOG.LEVEL_PROCEDURE);
844
845 -- purge all ACC tables
846 csm_user_event_pkg.purge_all_acc_tables(p_user_id=>p_user_id);
847
848 CSM_UTIL_PKG.LOG('Leaving user_del_init for user_id:' || p_user_id,
849 'csm_user_event_pkg.user_del_init',FND_LOG.LEVEL_PROCEDURE);
850 EXCEPTION
851 WHEN OTHERS THEN
852 l_sqlerrno := to_char(SQLCODE);
853 l_sqlerrmsg := substr(SQLERRM, 1,2000);
854 l_error_msg := ' Exception in user_del_init for for user_id: ' || p_user_id
855 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
856 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.user_del_init',FND_LOG.LEVEL_EXCEPTION);
857 RAISE;
858 END user_del_init;
859
860 PROCEDURE purge_all_acc_tables(p_user_id IN NUMBER)
861 IS
862 l_sqlerrno VARCHAR2(20);
863 l_sqlerrmsg VARCHAR2(4000);
864 l_error_msg VARCHAR2(4000);
865 l_return_status VARCHAR2(2000);
866
867 BEGIN
868 CSM_UTIL_PKG.LOG('Entering purge_all_acc_tables for user_id:' || p_user_id,
869 'csm_user_event_pkg.purge_all_acc_tables',FND_LOG.LEVEL_PROCEDURE);
870
871 DELETE FROM CSM_DEBRIEF_LINES_ACC WHERE user_id = p_user_id;
872 DELETE FROM CSM_DEBRIEF_HEADERS_ACC WHERE user_id = p_user_id;
873 DELETE FROM CSM_ITEM_INSTANCES_ACC WHERE user_id = p_user_id;
874 DELETE FROM CSM_NOTES_ACC WHERE user_id = p_user_id;
875 DELETE FROM CSM_NOTIFICATIONS_ACC WHERE user_id = p_user_id;
876
877 DELETE FROM CSM_PARTIES_ACC WHERE user_id = p_user_id;
878 DELETE FROM CSM_PARTY_SITES_ACC WHERE user_id = p_user_id;
879 DELETE FROM csm_profile_option_values_acc WHERE user_id = p_user_id;
880
881 DELETE FROM CSM_RS_RESOURCE_EXTNS_ACC WHERE user_id = p_user_id;
882 --Bug 5236469
883 csm_resource_extns_event_pkg.RESOURCE_EXTNS_ACC_CLEANUP(p_user_id);
884
885 DELETE FROM CSM_TASK_ASSIGNMENTS_ACC WHERE user_id = p_user_id;
886
887 DELETE FROM CSM_TASKS_ACC WHERE user_id = p_user_id;
888 DELETE FROM CSM_INCIDENTS_ALL_ACC WHERE user_id = p_user_id;
889 DELETE FROM CSM_SR_CONTACTS_ACC WHERE user_id = p_user_id;
890 DELETE FROM CSM_CUSTOMIZATION_VIEWS_ACC WHERE user_id = p_user_id;
891 DELETE FROM CSM_PAGE_PERZ_DELTA_ACC WHERE user_id = p_user_id;
892
893 DELETE FROM CSM_NEW_MESSAGES_ACC WHERE user_id = p_user_id;
894 DELETE FROM CSM_STATE_TRANSITIONS_ACC WHERE user_id = p_user_id;
895 DELETE FROM csm_mtl_system_items_acc WHERE user_id = p_user_id;
896 DELETE FROM csm_mtl_serial_numbers_acc WHERE user_id = p_user_id;
897 DELETE FROM csm_mtl_material_txn_acc WHERE user_id = p_user_id;
898
899 DELETE FROM csm_mtl_txn_lot_num_acc WHERE user_id = p_user_id;
900 DELETE FROM csm_mtl_unit_txn_acc WHERE user_id = p_user_id;
901 DELETE FROM csm_inv_loc_ass_acc WHERE user_id = p_user_id;
902 DELETE FROM csm_po_loc_ass_all_acc WHERE user_id = p_user_id;
903 DELETE FROM csm_req_lines_acc WHERE user_id = p_user_id;
904
905 DELETE FROM csm_req_line_Details_acc WHERE user_id = p_user_id;
906 DELETE FROM csm_req_headers_acc WHERE user_id = p_user_id;
907 DELETE FROM csm_system_items_acc WHERE user_id = p_user_id;
908 DELETE FROM csm_mtl_item_subinv_acc WHERE user_id = p_user_id;
909 DELETE FROM csm_mtl_sec_inv_acc WHERE user_id = p_user_id;
910 DELETE FROM csm_ii_relationships_acc WHERE user_id = p_user_id;
911
912 DELETE FROM csm_contr_headers_acc WHERE user_id = p_user_id;
913 DELETE FROM csm_contr_buss_processes_acc WHERE user_id = p_user_id;
914 DELETE FROM csm_contr_buss_txn_types_acc WHERE user_id = p_user_id;
915 --DELETE FROM csm_unit_of_measure_tl_acc WHERE user_id = p_user_id;
916 DELETE FROM csm_service_history_acc WHERE user_id = p_user_id;
917 DELETE FROM csm_debrief_headers_acc WHERE user_id = p_user_id;
918
919 DELETE FROM csm_counters_acc WHERE user_id = p_user_id;
920 DELETE FROM csm_counter_values_acc WHERE user_id = p_user_id;
921 DELETE FROM csm_mtl_item_locations_acc WHERE user_id = p_user_id;
922 DELETE FROM csm_mtl_secondary_locators_acc WHERE user_id = p_user_id;
923 DELETE FROM csm_mtl_onhand_qty_acc WHERE user_id = p_user_id;
924
925 DELETE FROM csm_user_inventory_org WHERE user_id = p_user_id;
926 DELETE FROM CSM_FND_LOBS_ACC WHERE user_id = p_user_id;--Bug 4938130
927 DELETE FROM CSM_HZ_CUST_ACCOUNTS_ACC WHERE user_id = p_user_id;--Bug 5213097
928 DELETE FROM CSM_SR_TYPE_MAPPING_ACC WHERE user_id = p_user_id;--Bug 5213097
929 DELETE FROM CSM_CSI_ITEM_ATTR_ACC WHERE user_id = p_user_id;--Bug 5213097
930 DELETE FROM CSM_HZ_LOCATIONS_ACC WHERE user_id = p_user_id;--Bug 5213097
931
932 DELETE FROM CSM_HZ_CONTACT_POINTS_ACC WHERE user_id = p_user_id;
933 DELETE FROM CSM_COUNTER_PROP_VALUES_ACC WHERE user_id = p_user_id;
934 DELETE FROM CSM_COUNTER_PROPERTIES_ACC WHERE user_id = p_user_id;
935 DELETE FROM CSM_HZ_RELATIONSHIPS_ACC WHERE user_id = p_user_id;
936 DELETE FROM CSM_PARTY_ASSIGNMENT WHERE user_id = p_user_id;
937
938 DELETE FROM CSM_CLIENT_NFN_LOG_ACC WHERE user_id=p_user_id;
939 DELETE FROM CSM_AUTO_SYNC_LOG_ACC WHERE user_id=p_user_id;
940 DELETE FROM CSM_AUTO_SYNC_ACC WHERE user_id=p_user_id;
941 DELETE FROM CSM_QUERY_ACC WHERE user_id=p_user_id;
942 DELETE FROM CSM_QUERY_VARIABLES_ACC WHERE user_id=p_user_id;
943 DELETE FROM CSM_QUERY_INSTANCES_ACC WHERE user_id=p_user_id;
944 DELETE FROM CSM_QUERY_VARIABLE_VALUES_ACC WHERE user_id=p_user_id;
945 DELETE FROM CSM_QUERY_RESULTS_ACC WHERE user_id=p_user_id;
946 DELETE FROM CSM_COV_ACTION_TIMES_ACC WHERE user_id=p_user_id;
947 DELETE FROM CSM_CLIENT_UNDO_REQUEST_ACC WHERE user_id=p_user_id;
948
949 --Purge Auto sync tables
950 CSM_NOTIFICATION_EVENT_PKG.PURGE_USER(p_user_id);
951
952 --Delete the Group of the User
953 CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP (p_user_id=>p_user_id
954 ,x_return_status=>l_return_status
955 , x_error_message => l_error_msg);
956 --Deleting User from the Access table
957 CSM_USER_EVENT_PKG.DELETE_ACC (p_user_id =>p_user_id
958 ,x_return_status =>l_return_status
959 ,x_error_message =>l_error_msg);
960
961 CSM_HTML5_PKG.DROP_USER(p_user_id);
962
963 CSM_UTIL_PKG.LOG('Leaving purge_all_acc_tables for user_id:' || p_user_id,
964 'csm_user_event_pkg.purge_all_acc_tables',FND_LOG.LEVEL_PROCEDURE);
965 EXCEPTION
966 WHEN OTHERS THEN
967 l_sqlerrno := to_char(SQLCODE);
968 l_sqlerrmsg := substr(SQLERRM, 1,2000);
969 l_error_msg := ' Exception in purge_all_acc_tables for for user_id: ' || p_user_id
970 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
971 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_user_event_pkg.purge_all_acc_tables',FND_LOG.LEVEL_EXCEPTION);
972 RAISE;
973 END purge_all_acc_tables;
974
975 --Populate PIs related to parts transfer
976 PROCEDURE spawn_mat_txn(p_user_id IN NUMBER)
977 IS
978 l_sqlerrno VARCHAR2(20);
979 l_sqlerrmsg VARCHAR2(4000);
980 l_msg VARCHAR2(4000);
981 l_status VARCHAR2(40);
982 BEGIN
983 CSM_UTIL_PKG.LOG('Entering spawn_mat_txn for user_id: ' || p_user_id,
984 'CSM_USER_EVENT_PKG.spawn_mat_txn',FND_LOG.LEVEL_PROCEDURE);
985
986 CSM_MTL_MATERIAL_TXN_ACC_PKG.get_new_user_mat_txn(p_user_id);
987
988 CSM_UTIL_PKG.LOG('Leaving spawn_mat_txn for user_id: ' || p_user_id,
989 'CSM_USER_EVENT_PKG.spawn_mat_txn',FND_LOG.LEVEL_PROCEDURE);
990 EXCEPTION
991 WHEN OTHERS THEN
992 l_sqlerrno := to_char(SQLCODE);
993 l_sqlerrmsg := substr(SQLERRM, 1,2000);
994 l_msg := ' Exception in spawn_perz_ins for for user_id: ' || p_user_id
995 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
996 CSM_UTIL_PKG.LOG(l_msg, 'csm_user_event_pkg.spawn_perz_ins',FND_LOG.LEVEL_EXCEPTION);
997 RAISE;
998 END spawn_mat_txn;
999
1000 --PROCEDURE TO DOWNLOAD SERIAL NUMBERS DURING USER CREATION.
1001 PROCEDURE spawn_mtl_serial_numbers(p_resource_id IN NUMBER, p_user_id IN NUMBER)
1002 IS
1003 l_organization_id NUMBER;
1004 l_sqlerrno VARCHAR(20);
1005 l_sqlerrmsg VARCHAR(4000);
1006 l_error_msg VARCHAR(4000);
1007
1008 BEGIN
1009 CSM_UTIL_PKG.LOG('Entering spawn_mtl_serial_numbers for User_id ' || p_user_id,
1010 'CSM_USER_EVENT_PKG.spawn_mtl_serial_numbers',FND_LOG.LEVEL_PROCEDURE);
1011
1012 l_organization_id := csm_profile_pkg.get_organization_id(p_user_id);
1013
1014 --get all the serial numbers for the User
1015 CSM_SERIAL_NUMBERS_EVENT_PKG.insert_mtl_serial_numbers(l_organization_id,TO_DATE('1','J'),p_resource_id,p_user_id);
1016
1017 CSM_UTIL_PKG.LOG('Leaving spawn_mtl_serial_numbers for User_id ' || p_user_id,
1018 'CSM_USER_EVENT_PKG.spawn_mtl_serial_numbers',FND_LOG.LEVEL_PROCEDURE);
1019
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 l_sqlerrno := TO_CHAR(SQLCODE);
1023 l_sqlerrmsg := TO_CHAR(SQLERRM);
1024 l_error_msg := 'Exception in spawn_mtl_serial_numbers for User_id '
1025 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1026
1027 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.spawn_mtl_serial_numbers',FND_LOG.LEVEL_EXCEPTION);
1028 RAISE;
1029 END spawn_mtl_serial_numbers;
1030
1031 PROCEDURE INSERT_ACC (p_user_id IN NUMBER
1032 ,x_return_status OUT NOCOPY VARCHAR2
1033 , x_error_message OUT NOCOPY VARCHAR2)
1034 IS
1035 l_sqlerrno VARCHAR(20);
1036 l_sqlerrmsg VARCHAR(4000);
1037 l_error_msg VARCHAR(4000);
1038 l_user_id NUMBER;
1039 l_owner_id NUMBER;
1040 l_group_id NUMBER;
1041 l_access_id NUMBER;
1042 l_markdirty BOOLEAN;
1043 l_tab_user_id asg_download.user_list;
1044 l_tab_owner_id asg_download.user_list;
1045 l_tab_access_id asg_download.access_list;
1046 g_pub_item varchar2(30) := 'CSF_M_USER';
1047
1048 CURSOR c_asg_user(l_user_id NUMBER)
1049 IS
1050 SELECT USER_ID,OWNER_ID,GROUP_ID
1051 FROM
1052 ASG_USER
1053 WHERE USER_ID=l_user_id;
1054
1055 CURSOR c_user_acc(l_user_id NUMBER)
1056 IS
1057 SELECT USER_ID
1058 FROM
1059 CSM_USER_ACC
1060 WHERE USER_ID=l_user_id;
1061
1062 CURSOR c_group_users(l_owner_id NUMBER)
1063 IS
1064 SELECT USR.USER_ID,USR.OWNER_ID,ACC.ACCESS_ID
1065 FROM ASG_USER USR,
1066 CSM_USER_ACC ACC
1067 WHERE USR.OWNER_ID=l_owner_id
1068 AND USR.USER_ID = ACC.USER_ID;
1069
1070 BEGIN
1071 CSM_UTIL_PKG.LOG('Entering INSERT_ACC for User_id ' || p_user_id,
1072 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1073 OPEN c_asg_user(p_user_id);
1074 FETCH c_asg_user INTO l_user_id,l_owner_id,l_group_id;
1075 CLOSE c_asg_user;
1076
1077 OPEN c_user_acc(p_user_id);
1078 FETCH c_user_acc INTO l_user_id;
1079 IF c_user_acc%FOUND THEN
1080 DELETE FROM CSM_USER_ACC WHERE USER_ID =l_user_id;
1081 END IF;
1082
1083 SELECT CSM_USER_ACC_S.NEXTVAL INTO l_access_id FROM DUAL;
1084
1085 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)
1086 VALUES (l_access_id,l_user_id,l_owner_id,1,sysdate,1,sysdate,1,1);
1087 CLOSE c_user_acc;
1088
1089 l_markdirty := asg_download.mark_dirty(
1090 p_pub_item => g_pub_item
1091 , p_accessid => l_access_id
1092 , p_userid => l_owner_id
1093 , p_dml => 'I'
1094 , P_TIMESTAMP => sysdate
1095 );
1096 CSM_UTIL_PKG.LOG('User is inserted into the Access table: User_id ' || p_user_id,
1097 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1098 --If the user is a owner
1099 IF l_user_id = l_owner_id AND l_group_id IS NOT NULL THEN
1100 OPEN c_group_users(l_owner_id);
1101 LOOP
1102 IF l_tab_user_id.COUNT > 0 THEN
1103 l_tab_user_id.DELETE;
1104 END IF;
1105 IF l_tab_owner_id.COUNT > 0 THEN
1106 l_tab_owner_id.DELETE;
1107 END IF;
1108 IF l_tab_access_id.COUNT > 0 THEN
1109 l_tab_access_id.DELETE;
1110 END IF;
1111
1112 FETCH c_group_users BULK COLLECT INTO l_tab_user_id,l_tab_owner_id,l_tab_access_id LIMIT 10;
1113 EXIT WHEN l_tab_access_id.COUNT = 0;
1114 FOR i in 1..l_tab_access_id.COUNT
1115 LOOP
1116 UPDATE CSM_USER_ACC
1117 SET OWNER_ID = l_tab_owner_id(i),
1118 LAST_UPDATE_DATE = SYSDATE
1119 WHERE USER_ID = l_tab_user_id(i);
1120
1121 l_markdirty := asg_download.mark_dirty(
1122 p_pub_item => g_pub_item
1123 , p_accessid => l_tab_access_id(i)
1124 , p_userid => l_tab_owner_id(i)
1125 , p_dml => 'U'
1126 , P_TIMESTAMP => sysdate
1127 );
1128
1129 END LOOP;
1130
1131 END LOOP;
1132 CLOSE c_group_users;
1133
1134 END IF;
1135 CSM_UTIL_PKG.LOG('Leaving INSERT_ACC for User_id ' || p_user_id,
1136 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 l_sqlerrno := TO_CHAR(SQLCODE);
1140 l_sqlerrmsg := TO_CHAR(SQLERRM);
1141 l_error_msg := 'Exception in INSERT_ACC for User_id '
1142 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1143
1144 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);
1145 RAISE;
1146 END INSERT_ACC;
1147
1148 PROCEDURE DELETE_ACC (p_user_id IN NUMBER
1149 ,x_return_status OUT NOCOPY VARCHAR2
1150 , x_error_message OUT NOCOPY VARCHAR2)
1151 IS
1152 l_sqlerrno VARCHAR(20);
1153 l_sqlerrmsg VARCHAR(4000);
1154 l_error_msg VARCHAR(4000);
1155 l_user_id NUMBER;
1156 l_owner_id NUMBER;
1157 l_access_id NUMBER;
1158 l_markdirty BOOLEAN;
1159 g_pub_item varchar2(30) := 'CSF_M_USER';
1160
1161 CURSOR c_user_acc(l_user_id NUMBER)
1162 IS
1163 SELECT ACCESS_ID,USER_ID,OWNER_ID
1164 FROM
1165 CSM_USER_ACC
1166 WHERE USER_ID=l_user_id;
1167
1168
1169 BEGIN
1170 CSM_UTIL_PKG.LOG('Entering DELETE_ACC for User_id ' || p_user_id,
1171 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1172
1173 OPEN c_user_acc(p_user_id);
1174 FETCH c_user_acc INTO l_access_id,l_user_id,l_owner_id;
1175 --Do mark dirty only for the User
1176 IF c_user_acc%FOUND THEN
1177 IF l_user_id <> l_owner_id THEN --Do not do mark dirty for individual users and owners
1178 l_markdirty := asg_download.mark_dirty(
1179 p_pub_item => g_pub_item
1180 , p_accessid => l_access_id
1181 , p_userid => l_owner_id
1182 , p_dml => 'D'
1183 , P_TIMESTAMP => sysdate
1184 );
1185 END IF;
1186 --delete for owner,member or individual user
1187 DELETE FROM CSM_USER_ACC WHERE USER_ID =l_user_id;
1188
1189 CSM_UTIL_PKG.LOG('User is Deleted From the Access table for User_id :' || p_user_id,
1190 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1191
1192 END IF;
1193
1194 CSM_UTIL_PKG.LOG('Leaving DELETE_ACC for User_id ' || p_user_id,
1195 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1196 EXCEPTION
1197 WHEN OTHERS THEN
1198 l_sqlerrno := TO_CHAR(SQLCODE);
1199 l_sqlerrmsg := TO_CHAR(SQLERRM);
1200 l_error_msg := 'Exception in DELETE_ACC for User_id '
1201 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1202
1203 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);
1204 RAISE;
1205 END DELETE_ACC;
1206
1207 /*
1208 The following two apis are used for inserting/deleting
1209 non-group member records in CSM_USER_ACC for the (user,owner)
1210 */
1211
1212 PROCEDURE INSERT_ACC (p_user_id IN NUMBER,p_owner_id IN NUMBER)
1213 IS
1214 l_sqlerrno VARCHAR(20);
1215 l_sqlerrmsg VARCHAR(4000);
1216 l_error_msg VARCHAR(4000);
1217 l_access_id NUMBER;
1218 l_markdirty BOOLEAN;
1219
1220
1221 g_pub_item varchar2(30) := 'CSF_M_USER';
1222
1223
1224 CURSOR c_user_acc(b_user_id NUMBER,b_owner_id NUMBER)
1225 IS
1226 SELECT ACCESS_ID
1227 FROM CSM_USER_ACC
1228 WHERE USER_ID=b_user_id
1229 AND OWNER_ID = b_owner_id;
1230
1231 BEGIN
1232 CSM_UTIL_PKG.LOG('Entering INSERT_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
1233 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1234
1235 OPEN c_user_acc(p_user_id,p_owner_id);
1236 FETCH c_user_acc INTO l_access_id;
1237 IF c_user_acc%FOUND THEN
1238 CSM_UTIL_PKG.LOG('Record already there in the Access table: (User_id,owner_id) : (' || p_user_id||','||p_owner_id||')',
1239 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1240 UPDATE CSM_USER_ACC SET COUNTER=COUNTER+1 ,LAST_UPDATE_DATE=sysdate WHERE ACCESS_ID=l_access_id;
1241 CLOSE c_user_acc;
1242 ELSE
1243
1244 CLOSE c_user_acc;
1245
1246 SELECT CSM_USER_ACC_S.NEXTVAL INTO l_access_id FROM DUAL;
1247
1248 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)
1249 VALUES (l_access_id,p_user_id,p_owner_id,1,sysdate,1,sysdate,1,1);
1250
1251 CSM_UTIL_PKG.LOG('Record inserted into the Access table: (User_id,owner_id) : (' || p_user_id||','||p_owner_id||') and Marking dirty',
1252 'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);
1253
1254 l_markdirty := asg_download.mark_dirty(
1255 p_pub_item => g_pub_item
1256 , p_accessid => l_access_id
1257 , p_userid => p_owner_id
1258 , p_dml => 'I'
1259 , P_TIMESTAMP => sysdate
1260 );
1261 END IF;
1262
1263 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);
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 l_sqlerrno := TO_CHAR(SQLCODE);
1267 l_sqlerrmsg := TO_CHAR(SQLERRM);
1268 l_error_msg := 'Exception in INSERT_ACC for User_id '
1269 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1270
1271 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);
1272 RAISE;
1273 END INSERT_ACC;
1274
1275 PROCEDURE DELETE_ACC (p_user_id IN NUMBER,p_owner_id IN NUMBER)
1276 IS
1277 l_sqlerrno VARCHAR(20);
1278 l_sqlerrmsg VARCHAR(4000);
1279 l_error_msg VARCHAR(4000);
1280 l_access_id NUMBER;
1281 l_markdirty BOOLEAN;
1282 g_pub_item varchar2(30) := 'CSF_M_USER';
1283 l_counter NUMBER;
1284
1285 CURSOR c_user_acc(b_user_id NUMBER,b_owner_id NUMBER)
1286 IS
1287 SELECT access_id,counter
1288 FROM CSM_USER_ACC
1289 WHERE USER_ID=b_user_id
1290 AND OWNER_ID = b_owner_id;
1291
1292 BEGIN
1293 CSM_UTIL_PKG.LOG('Entering DELETE_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
1294 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1295
1296 OPEN c_user_acc(p_user_id,p_owner_id);
1297 FETCH c_user_acc INTO l_access_id,l_counter;
1298
1299 IF c_user_acc%FOUND THEN
1300 IF l_counter=1 THEN
1301 l_markdirty := asg_download.mark_dirty(
1302 p_pub_item => g_pub_item
1303 , p_accessid => l_access_id
1304 , p_userid => p_owner_id
1305 , p_dml => 'D'
1306 , P_TIMESTAMP => sysdate
1307 );
1308
1309 DELETE FROM CSM_USER_ACC WHERE USER_ID =p_user_id
1310 AND OWNER_ID=p_owner_id;
1311 ELSE
1312 UPDATE CSM_USER_ACC SET COUNTER=COUNTER-1 ,LAST_UPDATE_DATE=sysdate WHERE USER_ID =p_user_id
1313 AND OWNER_ID=p_owner_id;
1314 END IF;
1315 END IF;
1316
1317 CSM_UTIL_PKG.LOG('Leaving DELETE_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
1318 'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 l_sqlerrno := TO_CHAR(SQLCODE);
1322 l_sqlerrmsg := TO_CHAR(SQLERRM);
1323 l_error_msg := 'Exception in DELETE_ACC for User_id '
1324 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1325
1326 CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);
1327 RAISE;
1328 END DELETE_ACC;
1329
1330
1331 END CSM_USER_EVENT_PKG;