eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSL_USER_PKG

Source


1 PACKAGE BODY CSL_USER_PKG AS
2 /* $Header: cslmupb.pls 120.0 2005/05/25 11:04:49 appldev noship $ */
3 
4 /*** Globals ***/
5 g_debug_level NUMBER;
6 g_table_name  VARCHAR2(30) := 'CSL_USER_PKG';
7 
8 /* Put all create_user procedures here */
9 PROCEDURE CREATE_USER( p_resource_id IN NUMBER
10                      , x_return_status OUT NOCOPY VARCHAR2 ) IS
11 CURSOR c_user( b_resource_id NUMBER ) IS
12  SELECT USER_ID
13  FROM JTF_RS_RESOURCE_EXTNS
14  WHERE RESOURCE_ID = b_resource_id;
15 r_user NUMBER;
16 BEGIN
17  /*** get debug level ***/
18  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
19 
20  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
21    jtm_message_log_pkg.Log_Msg
22    ( null
23    , g_table_name
24    , 'Entering CREATE_USER'
25    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
26  END IF;
27 
28  /* First delete all ancient records*/
29  DELETE_USER( p_resource_id, x_return_status );
30 
31  /* Because we delete also resource and user we need to recreate those records*/
32  OPEN c_user(P_RESOURCE_ID );
33  FETCH c_user INTO r_user;
34  IF c_user%FOUND THEN
35   JTM_HOOK_UTIL_PKG.Insert_Acc
36   ( P_PUBLICATION_ITEM_NAMES => JTM_HOOK_UTIL_PKG.t_publication_item_list('FND_USER')
37    ,P_ACC_TABLE_NAME         => 'JTM_FND_USER_ACC'
38    ,P_PK1_NAME               => 'USER_ID'
39    ,P_PK1_NUM_VALUE          => r_user
40    ,P_RESOURCE_ID            => P_RESOURCE_ID
41    );
42  END IF;
43  CLOSE c_user;
44 
45   JTM_HOOK_UTIL_PKG.Insert_Acc
46   ( P_PUBLICATION_ITEM_NAMES => JTM_HOOK_UTIL_PKG.t_publication_item_list('JTF_RS_RESOURCE_EXTNS')
47    ,P_ACC_TABLE_NAME         => 'JTM_JTF_RS_RESOURCE_EXTNS_ACC'
48    ,P_PK1_NAME               => 'RESOURCE_PK'
49    ,P_PK1_NUM_VALUE          => P_RESOURCE_ID
50    ,P_RESOURCE_ID            => P_RESOURCE_ID
51    );
52 
53  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
54    jtm_message_log_pkg.Log_Msg
55    ( null
56    , g_table_name
57    , 'Calling CSL_JTF_TASK_ASS_ACC_PKG.INSERT_ALL_ACC_RECORDS'
58    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
59  END IF;
60  CSL_JTF_TASK_ASS_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
61  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
62   RAISE FND_API.G_EXC_ERROR;
63  END IF;
64 
65  COMMIT;
66 
67  /*** insert SRs created by mobile resource ***/
68  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
69    jtm_message_log_pkg.Log_Msg
70    ( null
71    , g_table_name
72    , 'Calling CSL_CS_INCIDENTS_ALL_ACC_PKG.INSERT_ALL_ACC_RECORDS'
73    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
74  END IF;
75  CSL_CS_INCIDENTS_ALL_ACC_PKG.INSERT_ALL_ACC_RECORDS
76   ( p_resource_id
77   , x_return_status );
78  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
79   RAISE FND_API.G_EXC_ERROR;
80  END IF;
81 
82  COMMIT;
83 
84  /*** insert tasks created by mobile resource ***/
85  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
86    jtm_message_log_pkg.Log_Msg
87    ( null
88    , g_table_name
89    , 'Calling CSL_JTF_TASKS_ACC_PKG.INSERT_ALL_ACC_RECORDS'
90    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
91  END IF;
92  CSL_JTF_TASKS_ACC_PKG.INSERT_ALL_ACC_RECORDS
93   ( p_resource_id
94   , x_return_status );
95  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
96   RAISE FND_API.G_EXC_ERROR;
97  END IF;
98 
99  COMMIT;
100 
101  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
102    jtm_message_log_pkg.Log_Msg
103    ( null
104    , g_table_name
105    , 'Calling CSL_CSP_INV_LOC_ASS_ACC_PKG.INSERT_ALL_ACC_RECORDS'
106    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
107  END IF;
108  CSL_CSP_INV_LOC_ASS_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
109  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
110   RAISE FND_API.G_EXC_ERROR;
111  END IF;
112 
113  COMMIT;
114 
115  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
116    jtm_message_log_pkg.Log_Msg
117    ( null
118    , g_table_name
119    , 'Calling CSL_CSP_LOCATIONS_ACC_PKG.INSERT_ALL_ACC_RECORDS'
120    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
121  END IF;
122  CSL_CSP_LOCATIONS_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
123  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
124   RAISE FND_API.G_EXC_ERROR;
125  END IF;
126 
127  COMMIT;
128 
129  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
130    jtm_message_log_pkg.Log_Msg
131    ( null
132    , g_table_name
133    , 'Calling CSL_CSP_REQ_HEADERS_ACC_PKG.INSERT_ALL_ACC_RECORDS'
134    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
135  END IF;
136  CSL_CSP_REQ_HEADERS_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
137  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
138   RAISE FND_API.G_EXC_ERROR;
139  END IF;
140 
141  COMMIT;
142 
143  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
144    jtm_message_log_pkg.Log_Msg
145    ( null
146    , g_table_name
147    , 'Calling CSL_CSP_REQ_LINES_ACC_PKG.INSERT_ALL_ACC_RECORDS'
148    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
149  END IF;
150  CSL_CSP_REQ_LINES_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
151  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
152   RAISE FND_API.G_EXC_ERROR;
153  END IF;
154 
155  COMMIT;
156 
157  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
158    jtm_message_log_pkg.Log_Msg
159    ( null
160    , g_table_name
161    , 'Calling CSL_WF_NOTIFICATION_AT_ACC_PKG.INSERT_ALL_ACC_RECORDS'
162    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
163  END IF;
164  CSL_WF_NOTIFICATION_AT_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
165  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
166   RAISE FND_API.G_EXC_ERROR;
167  END IF;
168 
169  COMMIT;
170 
171  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
172    jtm_message_log_pkg.Log_Msg
173    ( null
174    , g_table_name
175    , 'Calling CSL_JTF_RS_GRP_MEM_ACC_PKG.INSERT_ALL_ACC_RECORDS'
176    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
177  END IF;
178  CSL_JTF_RS_GRP_MEM_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status  );
179  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
180   RAISE FND_API.G_EXC_ERROR;
181  END IF;
182 
183  COMMIT;
184 
185  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
186    jtm_message_log_pkg.Log_Msg
187    ( null
188    , g_table_name
189    , 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.INSERT_ALL_ACC_RECORDS'
190    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
191  END IF;
192  CSL_MTL_SYSTEM_ITEMS_ACC_PKG.INSERT_ALL_ACC_RECORDS( p_resource_id, x_return_status );
193  IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
194   RAISE FND_API.G_EXC_ERROR;
195  END IF;
196 
197  /*Call Concurrent programs ( always after hook packages because of dependencies )*/
198 
199  /*** fix for bug 2457810 (deadlock issue), now doing commit before running autonomous inserts ***/
200  COMMIT;
201 
202  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
203    jtm_message_log_pkg.Log_Msg
204    ( null
205    , g_table_name
206    , 'Calling CSL_MTL_ONHAND_QTY_ACC_PKG.REFRESH_ONHAND_QTY'
207    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
208  END IF;
209  CSL_MTL_ONHAND_QTY_ACC_PKG.REFRESH_ONHAND_QTY;
210 
211  COMMIT;
212 
213  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
214    jtm_message_log_pkg.Log_Msg
215    ( null
216    , g_table_name
217    , 'Calling CSL_JTF_TASK_REFS_ACC_PKG.CON_REQUEST_TASK_REFERENCES'
218    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
219  END IF;
220  CSL_JTF_TASK_REFS_ACC_PKG.CON_REQUEST_TASK_REFERENCES;
221 
222  COMMIT;
223 
224  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
225    jtm_message_log_pkg.Log_Msg
226    ( null
227    , g_table_name
228    , 'Calling CSL_MTL_SERIAL_NUMBERS_ACC_PKG.INSERT_SERIAL_NUMBERS'
229    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
230  END IF;
231  CSL_MTL_SERIAL_NUMBERS_ACC_PKG.INSERT_SERIAL_NUMBERS(p_resource_id);
232 
233  COMMIT;
234 
235  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
236    jtm_message_log_pkg.Log_Msg
237    ( null
238    , g_table_name
239    , 'Leaving CREATE_USER'
240    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
241  END IF;
242 
243  x_return_status := FND_API.G_RET_STS_SUCCESS;
244 EXCEPTION
245  WHEN FND_API.G_EXC_ERROR THEN
246   /*** api call failed -> rolback and log error ***/
247   ROLLBACK ;
248 
249   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
250     jtm_message_log_pkg.Log_Msg
251       ( null
252       , g_table_name
253       , 'Exception occurred in CREATE_USER when calling API'
254       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
255   END IF;
256   fnd_msg_pub.Add_Exc_Msg('CSL_USER_PKG','CREATE_USER','API error');
257   x_return_status := FND_API.G_RET_STS_ERROR;
258 
259  WHEN OTHERS THEN
260   /*** uncaught exception -> rollback and log sql error ***/
261   ROLLBACK;
262 
263   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
264     jtm_message_log_pkg.Log_Msg
265       ( null
266       , g_table_name
267       , 'Caught exception in CREATE_USER:' || fnd_global.local_chr(10) || sqlerrm
268       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
269   END IF;
270 
271   fnd_msg_pub.Add_Exc_Msg('CSL_USER_PKG','CREATE_USER',sqlerrm);
272   x_return_status := FND_API.G_RET_STS_ERROR;
273 END CREATE_USER;
274 
275 /* Put all delete_user procedures here */
276 /* TODO add jtf_RS_resource_EXTNS and JTF_group_members and fnd_user*/
277 PROCEDURE DELETE_USER( p_resource_id IN NUMBER
278                      , x_return_status OUT NOCOPY VARCHAR2 ) IS
279 BEGIN
280  /*** get debug level ***/
281  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
282 
283  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
284    jtm_message_log_pkg.Log_Msg
285    ( null
286    , g_table_name
287    , 'Entering DELETE_USER'
288    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
289    , 'csl_user_pkg');
290  END IF;
291 
292  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
293    jtm_message_log_pkg.Log_Msg
294    ( null
295    , g_table_name
296    , 'Delete from CSL_CONTR_BUSS_PROCESSES_ACC for resource '||p_resource_id
297    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
298    , 'csl_user_pkg');
299  END IF;
300  DELETE CSL_CONTR_BUSS_PROCESSES_ACC
301  WHERE  RESOURCE_ID = p_resource_id;
302  COMMIT;
303 
304  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
305    jtm_message_log_pkg.Log_Msg
306    ( null
307    , g_table_name
308    , 'Delete from CSL_CONTR_BUSS_TXN_TYPES_ACC for resource '||p_resource_id
309    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
310    , 'csl_user_pkg');
311  END IF;
312  DELETE CSL_CONTR_BUSS_TXN_TYPES_ACC
313  WHERE  RESOURCE_ID = p_resource_id;
314  COMMIT;
315 
316  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
317    jtm_message_log_pkg.Log_Msg
318    ( null
319    , g_table_name
320    , 'Delete from CSL_CSI_ITEM_INSTANCES_ACC for resource '||p_resource_id
321    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
322    , 'csl_user_pkg');
323  END IF;
324  DELETE CSL_CSI_ITEM_INSTANCES_ACC
325  WHERE  RESOURCE_ID = p_resource_id;
326  COMMIT;
327 
328  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
329    jtm_message_log_pkg.Log_Msg
330    ( null
331    , g_table_name
332    , 'Delete from CSL_CS_INCIDENTS_ALL_ACC for resource '||p_resource_id
333    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
334    , 'csl_user_pkg');
335  END IF;
336  DELETE CSL_CS_INCIDENTS_ALL_ACC
337  WHERE  RESOURCE_ID = p_resource_id;
338  COMMIT;
339 
340  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
341    jtm_message_log_pkg.Log_Msg
342    ( null
343    , g_table_name
344    , 'Delete from CSL_CS_HZ_SR_CONTACT_PTS_ACC for resource '||p_resource_id
345    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
346    , 'csl_user_pkg');
347  END IF;
348  DELETE CSL_CS_HZ_SR_CONTACT_PTS_ACC
349  WHERE  RESOURCE_ID = p_resource_id;
350  COMMIT;
351 
352  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
353    jtm_message_log_pkg.Log_Msg
354    ( null
355    , g_table_name
356    , 'Delete from CSL_HZ_CONTACT_POINTS_ACC for resource '||p_resource_id
357    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
358    , 'csl_user_pkg');
359  END IF;
360  DELETE CSL_HZ_CONTACT_POINTS_ACC
361  WHERE  RESOURCE_ID = p_resource_id;
362  COMMIT;
363 
364  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
365    jtm_message_log_pkg.Log_Msg
366    ( null
367    , g_table_name
368    , 'Delete from CSL_HZ_CUST_ACCT_SITES_ALL_ACC for resource '||p_resource_id
369    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
370    , 'csl_user_pkg');
371  END IF;
372  DELETE CSL_HZ_CUST_ACCT_SITES_ALL_ACC
373  WHERE  RESOURCE_ID = p_resource_id;
374  COMMIT;
375 
376  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
377    jtm_message_log_pkg.Log_Msg
378    ( null
379    , g_table_name
380    , 'Delete from CSL_HZ_CUST_SITE_USES_ALL_ACC for resource '||p_resource_id
381    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
382    , 'csl_user_pkg');
383  END IF;
384  DELETE CSL_HZ_CUST_SITE_USES_ALL_ACC
385  WHERE  RESOURCE_ID = p_resource_id;
386  COMMIT;
387 
388  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
389    jtm_message_log_pkg.Log_Msg
390    ( null
391    , g_table_name
392    , 'Delete from CSL_HZ_LOCATIONS_ACC for resource '||p_resource_id
393    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
394    , 'csl_user_pkg');
395  END IF;
396  DELETE CSL_HZ_LOCATIONS_ACC
397  WHERE  RESOURCE_ID = p_resource_id;
398  COMMIT;
399 
400 
401  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
402    jtm_message_log_pkg.Log_Msg
403    ( null
404    , g_table_name
405    , 'Delete from CSL_HZ_PARTIES_ACC for resource '||p_resource_id
406    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
407    , 'csl_user_pkg');
408  END IF;
409  DELETE CSL_HZ_PARTIES_ACC
410  WHERE  RESOURCE_ID = p_resource_id;
411  COMMIT;
412 
413  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
414    jtm_message_log_pkg.Log_Msg
415    ( null
416    , g_table_name
417    , 'Delete from CSL_HZ_PARTY_SITES_ACC for resource '||p_resource_id
418    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
419    , 'csl_user_pkg');
420  END IF;
421  DELETE CSL_HZ_PARTY_SITES_ACC
422  WHERE  RESOURCE_ID = p_resource_id;
423  COMMIT;
424 
425  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
426    jtm_message_log_pkg.Log_Msg
427    ( null
428    , g_table_name
429    , 'Delete from CSL_HZ_RELATIONSHIPS_ACC for resource '||p_resource_id
430    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
431    , 'csl_user_pkg');
432  END IF;
433  DELETE CSL_HZ_RELATIONSHIPS_ACC
434  WHERE  RESOURCE_ID = p_resource_id;
435  COMMIT;
436 
437  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
438    jtm_message_log_pkg.Log_Msg
439    ( null
440    , g_table_name
441    , 'Delete from CSL_JTF_TASKS_ACC for resource '||p_resource_id
442    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
443    , 'csl_user_pkg');
444  END IF;
445  DELETE CSL_JTF_TASKS_ACC
446  WHERE  RESOURCE_ID = p_resource_id;
447  COMMIT;
448 
449  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
450    jtm_message_log_pkg.Log_Msg
451    ( null
452    , g_table_name
453    , 'Delete from CSL_JTF_TASK_ASS_ACC for resource '||p_resource_id
454    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
455    , 'csl_user_pkg');
456  END IF;
457  DELETE CSL_JTF_TASK_ASS_ACC
458  WHERE  RESOURCE_ID = p_resource_id;
459  COMMIT;
460 
461  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
462    jtm_message_log_pkg.Log_Msg
463    ( null
464    , g_table_name
465    , 'Delete from CSL_MTL_ONHAND_QTY_ACC for resource '||p_resource_id
466    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
467    , 'csl_user_pkg');
468  END IF;
469  DELETE CSL_MTL_ONHAND_QTY_ACC
470  WHERE  RESOURCE_ID = p_resource_id;
471  COMMIT;
472 
473  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
474    jtm_message_log_pkg.Log_Msg
475    ( null
476    , g_table_name
477    , 'Delete from CSL_SR_CONTRACT_HEADERS_ACC for resource '||p_resource_id
478    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
479    , 'csl_user_pkg');
480  END IF;
481  DELETE CSL_SR_CONTRACT_HEADERS_ACC
482  WHERE  RESOURCE_ID = p_resource_id;
483  COMMIT;
484 
485  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
486    jtm_message_log_pkg.Log_Msg
487    ( null
488    , g_table_name
489    , 'Delete from JTM_CSF_DEBRIEF_HEADERS_ACC for resource '||p_resource_id
490    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
491    , 'csl_user_pkg');
492  END IF;
493  DELETE JTM_CSF_DEBRIEF_HEADERS_ACC
494  WHERE  RESOURCE_ID = p_resource_id;
495  COMMIT;
496 
497  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
498    jtm_message_log_pkg.Log_Msg
499    ( null
500    , g_table_name
501    , 'Delete from JTM_CSF_DEBRIEF_LINES_ACC for resource '||p_resource_id
502    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
503    , 'csl_user_pkg');
504  END IF;
505  DELETE JTM_CSF_DEBRIEF_LINES_ACC
506  WHERE  RESOURCE_ID = p_resource_id;
507  COMMIT;
508 
509  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
510    jtm_message_log_pkg.Log_Msg
511    ( null
512    , g_table_name
513    , 'Delete from JTM_CSP_INV_LOC_ASS_ACC for resource '||p_resource_id
514    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
515    , 'csl_user_pkg');
516  END IF;
517  DELETE JTM_CSP_INV_LOC_ASS_ACC
518  WHERE  RESOURCE_ID = p_resource_id;
519  COMMIT;
520 
521  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
522    jtm_message_log_pkg.Log_Msg
523    ( null
524    , g_table_name
525    , 'Delete from JTM_CSP_REQ_HEADERS_ACC for resource '||p_resource_id
526    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
527    , 'csl_user_pkg');
528  END IF;
529  DELETE JTM_CSP_REQ_HEADERS_ACC
530  WHERE  RESOURCE_ID = p_resource_id;
531  COMMIT;
532 
533  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
534    jtm_message_log_pkg.Log_Msg
535    ( null
536    , g_table_name
537    , 'Delete from JTM_CSP_REQ_LINES_ACC for resource '||p_resource_id
538    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
539    , 'csl_user_pkg');
540  END IF;
541  DELETE JTM_CSP_REQ_LINES_ACC
542  WHERE  RESOURCE_ID = p_resource_id;
543  COMMIT;
544 
545  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
546    jtm_message_log_pkg.Log_Msg
547    ( null
548    , g_table_name
549    , 'Delete from JTM_CSP_RS_CUST_RELATIONS_ACC for resource '||p_resource_id
550    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
551    , 'csl_user_pkg');
552  END IF;
553  DELETE JTM_CSP_RS_CUST_RELATIONS_ACC
554  WHERE  RESOURCE_ID = p_resource_id;
555  COMMIT;
556 
557  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
558    jtm_message_log_pkg.Log_Msg
559    ( null
560    , g_table_name
561    , 'Delete from JTM_CSP_SEC_INV_ACC for resource '||p_resource_id
562    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
563    , 'csl_user_pkg');
564  END IF;
565  DELETE JTM_CSP_SEC_INV_ACC
566  WHERE  RESOURCE_ID = p_resource_id;
567  COMMIT;
568 
569 
570  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
571    jtm_message_log_pkg.Log_Msg
572    ( null
573    , g_table_name
574    , 'Delete from JTM_CS_COUNTERS_ACC for resource '||p_resource_id
575    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
576    , 'csl_user_pkg');
577  END IF;
578  DELETE JTM_CS_COUNTERS_ACC
579  WHERE  RESOURCE_ID = p_resource_id;
580  COMMIT;
581 
582  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
583    jtm_message_log_pkg.Log_Msg
584    ( null
585    , g_table_name
586    , 'Delete from JTM_CS_COUNTER_GROUPS_ACC for resource '||p_resource_id
587    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
588    , 'csl_user_pkg');
589  END IF;
590  DELETE JTM_CS_COUNTER_GROUPS_ACC
591  WHERE  RESOURCE_ID = p_resource_id;
592  COMMIT;
593 
594  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
595    jtm_message_log_pkg.Log_Msg
596    ( null
597    , g_table_name
598    , 'Delete from JTM_CS_COUNTER_PROPS_ACC for resource '||p_resource_id
599    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
600    , 'csl_user_pkg');
601  END IF;
602  DELETE JTM_CS_COUNTER_PROPS_ACC
603  WHERE  RESOURCE_ID = p_resource_id;
604  COMMIT;
605 
606  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
607    jtm_message_log_pkg.Log_Msg
608    ( null
609    , g_table_name
610    , 'Delete from JTM_CS_COUNTER_PROP_VALS_ACC for resource '||p_resource_id
611    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
612    , 'csl_user_pkg');
613  END IF;
614  DELETE JTM_CS_COUNTER_PROP_VALS_ACC
615  WHERE  RESOURCE_ID = p_resource_id;
616  COMMIT;
617 
618  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
619    jtm_message_log_pkg.Log_Msg
620    ( null
621    , g_table_name
622    , 'Delete from JTM_CS_COUNTER_VALUES_ACC for resource '||p_resource_id
623    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
624    , 'csl_user_pkg');
625  END IF;
626  DELETE JTM_CS_COUNTER_VALUES_ACC
627  WHERE  RESOURCE_ID = p_resource_id;
628  COMMIT;
629 
630  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
631    jtm_message_log_pkg.Log_Msg
632    ( null
633    , g_table_name
634    , 'Delete from JTM_JTF_NOTES_ACC for resource '||p_resource_id
635    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
636    , 'csl_user_pkg');
637  END IF;
638  DELETE JTM_JTF_NOTES_ACC
639  WHERE  RESOURCE_ID = p_resource_id;
640  COMMIT;
641 
642  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
643    jtm_message_log_pkg.Log_Msg
644    ( null
645    , g_table_name
646    , 'Delete from JTM_JTF_TASK_REFERENCES_ACC for resource '||p_resource_id
647    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
648    , 'csl_user_pkg');
649  END IF;
650  DELETE JTM_JTF_TASK_REFERENCES_ACC
651  WHERE  RESOURCE_ID = p_resource_id;
652  COMMIT;
653 
654  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
655    jtm_message_log_pkg.Log_Msg
656    ( null
657    , g_table_name
658    , 'Delete from JTM_MTL_SEC_INV_ACC for resource '||p_resource_id
659    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
660    , 'csl_user_pkg');
661  END IF;
662  DELETE JTM_MTL_SEC_INV_ACC
663  WHERE  RESOURCE_ID = p_resource_id;
664  COMMIT;
665 
666  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
667    jtm_message_log_pkg.Log_Msg
668    ( null
669    , g_table_name
670    , 'Delete from JTM_MTL_SERIAL_NUMBERS_ACC for resource '||p_resource_id
671    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
672    , 'csl_user_pkg');
673  END IF;
674  DELETE JTM_MTL_SERIAL_NUMBERS_ACC
675  WHERE  RESOURCE_ID = p_resource_id;
676  COMMIT;
677 
678  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
679    jtm_message_log_pkg.Log_Msg
680    ( null
681    , g_table_name
682    , 'Delete from JTM_MTL_SYSTEM_ITEMS_ACC for resource '||p_resource_id
683    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
684    , 'csl_user_pkg');
685  END IF;
686  DELETE JTM_MTL_SYSTEM_ITEMS_ACC
687  WHERE  RESOURCE_ID = p_resource_id;
688  COMMIT;
689 
690  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
691    jtm_message_log_pkg.Log_Msg
692    ( null
693    , g_table_name
694    , 'Delete from CSL_RESOURCE_INVENTORY_ORG for resource '||p_resource_id
695    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
696    , 'csl_user_pkg');
697  END IF;
698  DELETE CSL_RESOURCE_INVENTORY_ORG
699  WHERE RESOURCE_ID = p_resource_id;
700  COMMIT;
701 
702  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
703    jtm_message_log_pkg.Log_Msg
704    ( null
705    , g_table_name
706    , 'Delete from JTM_MTL_TRANS_LOT_NUM_ACC for resource '||p_resource_id
707    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
708    , 'csl_user_pkg');
709  END IF;
710  DELETE JTM_MTL_TRANS_LOT_NUM_ACC
711  WHERE  RESOURCE_ID = p_resource_id;
712  COMMIT;
713 
714  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
715    jtm_message_log_pkg.Log_Msg
716    ( null
717    , g_table_name
718    , 'Delete from JTM_MTL_UNIT_TRANS_ACC for resource '||p_resource_id
719    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
720    , 'csl_user_pkg');
721  END IF;
722  DELETE JTM_MTL_UNIT_TRANS_ACC
723  WHERE  RESOURCE_ID = p_resource_id;
724  COMMIT;
725 
726  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
727    jtm_message_log_pkg.Log_Msg
728    ( null
729    , g_table_name
730    , 'Delete from JTM_OE_ORDER_HEADERS_ALL_ACC for resource '||p_resource_id
731    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
732    , 'csl_user_pkg');
733  END IF;
734  DELETE JTM_OE_ORDER_HEADERS_ALL_ACC
735  WHERE  RESOURCE_ID = p_resource_id;
736  COMMIT;
737 
738  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
739    jtm_message_log_pkg.Log_Msg
740    ( null
741    , g_table_name
742    , 'Delete from JTM_OE_ORDER_LINES_ALL_ACC for resource '||p_resource_id
743    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
744    , 'csl_user_pkg');
745  END IF;
746  DELETE JTM_OE_ORDER_LINES_ALL_ACC
747  WHERE  RESOURCE_ID = p_resource_id;
748  COMMIT;
749 
750  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
751    jtm_message_log_pkg.Log_Msg
752    ( null
753    , g_table_name
754    , 'Delete from JTM_PER_ALL_PEOPLE_F_ACC for resource '||p_resource_id
755    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
756    , 'csl_user_pkg');
757  END IF;
758  DELETE JTM_PER_ALL_PEOPLE_F_ACC
759  WHERE  RESOURCE_ID = p_resource_id;
760  COMMIT;
761 
762  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
763    jtm_message_log_pkg.Log_Msg
764    ( null
765    , g_table_name
766    , 'Delete from JTM_PO_LOC_ASS_ALL_ACC for resource '||p_resource_id
767    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
768    , 'csl_user_pkg');
769  END IF;
770  DELETE JTM_PO_LOC_ASS_ALL_ACC
771  WHERE  RESOURCE_ID = p_resource_id;
772  COMMIT;
773 
774  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
775    jtm_message_log_pkg.Log_Msg
776    ( null
777    , g_table_name
778    , 'Delete from JTM_WF_NOTIFICATIONS_ACC for resource '||p_resource_id
779    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
780    , 'csl_user_pkg');
781  END IF;
782  DELETE JTM_WF_NOTIFICATIONS_ACC
783  WHERE  RESOURCE_ID = p_resource_id;
784  COMMIT;
785 
786  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
787    jtm_message_log_pkg.Log_Msg
788    ( null
789    , g_table_name
790    , 'Delete from JTM_WF_NOTIFICATION_AT_ACC for resource '||p_resource_id
791    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
792    , 'csl_user_pkg');
793  END IF;
794  DELETE JTM_WF_NOTIFICATION_AT_ACC
795  WHERE  RESOURCE_ID = p_resource_id;
796  COMMIT;
797 
798  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
799    jtm_message_log_pkg.Log_Msg
800    ( null
801    , g_table_name
802    , 'Delete from JTM_FND_USER_ACC for resource '||p_resource_id
803    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
804    , 'csl_user_pkg');
805  END IF;
806  DELETE JTM_FND_USER_ACC
807  WHERE  RESOURCE_ID = p_resource_id;
808  COMMIT;
809 
810  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
811    jtm_message_log_pkg.Log_Msg
812    ( null
813    , g_table_name
814    , 'Delete from JTM_JTF_RS_RESOURCE_EXTNS_ACC for resource '||p_resource_id
815    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
816    , 'csl_user_pkg');
817  END IF;
818  DELETE JTM_JTF_RS_RESOURCE_EXTNS_ACC
819  WHERE  RESOURCE_ID = p_resource_id;
820  COMMIT;
821 
822  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
823    jtm_message_log_pkg.Log_Msg
824    ( null
825    , g_table_name
826    , 'Delete from JTM_JTF_RS_GROUP_MEMBERS_ACC for resource '||p_resource_id
827    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
828    , 'csl_user_pkg');
829  END IF;
830  DELETE JTM_JTF_RS_GROUP_MEMBERS_ACC
831  WHERE  RESOURCE_ID = p_resource_id;
832  COMMIT;
833 
834  IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
835    jtm_message_log_pkg.Log_Msg
836    ( null
837    , g_table_name
838    , 'Delete from CSL_SERVICE_HISTORY for resource '||p_resource_id
839    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
840    , 'csl_user_pkg');
841  END IF;
842  DELETE CSL_SERVICE_HISTORY
843  WHERE  RESOURCE_ID = p_resource_id;
844  COMMIT;
845 
846  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
847    jtm_message_log_pkg.Log_Msg
848    ( null
849    , g_table_name
850    , 'Leaving DELETE_USER'
851    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
852    , 'csl_user_pkg');
853  END IF;
854 
855  x_return_status := FND_API.G_RET_STS_SUCCESS;
856 EXCEPTION
857  WHEN FND_API.G_EXC_ERROR THEN
858   /*** api call failed -> rolback and log error ***/
859   ROLLBACK;
860 
861   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
862     jtm_message_log_pkg.Log_Msg
863       ( null
864       , g_table_name
865       , 'Exception occurred in DELETE_USER when calling API'
866       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
867       , 'csl_user_pkg');
868   END IF;
869   fnd_msg_pub.Add_Exc_Msg('CSL_USER_PKG','DELETE_USER','API error');
870   x_return_status := FND_API.G_RET_STS_ERROR;
871 
872  WHEN OTHERS THEN
873   /*** uncaught exception -> rollback and log sql error ***/
874   ROLLBACK;
875 
876   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
877     jtm_message_log_pkg.Log_Msg
878       ( null
879       , g_table_name
880       , 'Caught exception in DELETE_USER:' || fnd_global.local_chr(10) || sqlerrm
881       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
882       , 'csl_user_pkg');
883   END IF;
884 
885   fnd_msg_pub.Add_Exc_Msg('CSL_USER_PKG','DELETE_USER',sqlerrm);
886   x_return_status := FND_API.G_RET_STS_ERROR;
887 END DELETE_USER;
888 
889 END CSL_USER_PKG;