[Home] [Help]
PACKAGE BODY: APPS.CSL_CSP_INV_LOC_ASS_ACC_PKG
Source
1 PACKAGE BODY CSL_CSP_INV_LOC_ASS_ACC_PKG AS
2 /* $Header: cslilacb.pls 120.0 2005/05/24 17:50:07 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CSP_INV_LOC_ASS_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSP_INV_LOC_ASSIGNMENTS');
8 g_table_name CONSTANT VARCHAR2(30) := 'CSP_INV_LOC_ASSIGNMENTS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'CSP_INV_LOC_ASSIGNMENT_ID';
10 g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
11 g_debug_level NUMBER; -- debug level
12
13 /*** Function that checks if assignment record should be replicated. Returns TRUE if it should ***/
14 FUNCTION Replicate_Record
15 ( p_csp_inv_loc_assignment_id NUMBER
16 )
17 RETURN BOOLEAN
18 IS
19 CURSOR c_csp_inv_loc_assignment (b_csp_inv_loc_assignment_id NUMBER) IS
20 SELECT *
21 FROM CSP_INV_LOC_ASSIGNMENTS
22 WHERE CSP_INV_LOC_ASSIGNMENT_ID = b_csp_inv_loc_assignment_id;
23 r_csp_inv_loc_assignment c_csp_inv_loc_assignment%ROWTYPE;
24
25 l_return_value BOOLEAN := FALSE;
26
27 BEGIN
28 /*** get debug level ***/
29 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
30
31 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
32 jtm_message_log_pkg.Log_Msg
33 ( v_object_id => p_csp_inv_loc_assignment_id
34 , v_object_name => g_table_name
35 , v_message => 'Entering Replicate_Record'
36 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
37 END IF;
38
39 /*** Retreive record assigned by Hook ***/
40 OPEN c_csp_inv_loc_assignment( p_csp_inv_loc_assignment_id );
41 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
42 IF c_csp_inv_loc_assignment%NOTFOUND THEN
43 /*** could not find assignment record -> exit ***/
44 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
45 jtm_message_log_pkg.Log_Msg
46 ( v_object_id => p_csp_inv_loc_assignment_id
47 , v_object_name => g_table_name
48 , v_message => 'Replicate_Record error: Could not find '
49 || g_pk1_name || ' ' || p_csp_inv_loc_assignment_id
50 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
51 END IF;
52
53 CLOSE c_csp_inv_loc_assignment;
54 RETURN l_return_value;
55 END IF;
56 CLOSE c_csp_inv_loc_assignment;
57
58 /*** is this an RS_EMPLOYEE assignment? ***/
59 IF NVL(r_csp_inv_loc_assignment.resource_type,'') <> 'RS_EMPLOYEE' THEN
60 /*** No -> exit ***/
61 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
62 jtm_message_log_pkg.Log_Msg
63 ( v_object_id => p_csp_inv_loc_assignment_id
64 , v_object_name => g_table_name
65 , v_message => 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
66 'RESOURCE_TYPE <> ''RS_EMPLOYEE'''
67 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
68 END IF;
69 RETURN l_return_value;
70 END IF;
71
72 /*** is resource a mobile user? ***/
73 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( r_csp_inv_loc_assignment.resource_id ) THEN
74 /*** No -> exit ***/
75 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
76 jtm_message_log_pkg.Log_Msg
77 ( v_object_id => p_csp_inv_loc_assignment_id
78 , v_object_name => g_table_name
79 , v_message => 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
80 'Resource_id ' || r_csp_inv_loc_assignment.resource_id || ' is not a mobile user.'
81 || fnd_global.local_chr(10) || 'For location assignment : ' || p_csp_inv_loc_assignment_id
82 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
83 END IF;
84
85 RETURN l_return_value;
86 END IF;
87
88 /*** Record is found OK return status ***/
89 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
90 jtm_message_log_pkg.Log_Msg
91 ( v_object_id => p_csp_inv_loc_assignment_id
92 , v_object_name => g_table_name
93 , v_message => 'Replicate_Record returned TRUE'
94 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
95 END IF;
96
97 l_return_value := TRUE;
98
99 RETURN l_return_value;
100 END Replicate_Record;
101
102 /*** Private procedure that replicates given assignment related data for resource ***/
103 PROCEDURE Insert_ACC_Record
104 ( p_csp_inv_loc_assignment_id IN NUMBER
105 ,p_resource_id IN NUMBER
106 )
107 IS
108 l_success BOOLEAN;
109 BEGIN
110 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
111 jtm_message_log_pkg.Log_Msg
112 ( v_object_id => p_csp_inv_loc_assignment_id
113 , v_object_name => g_table_name
114 , v_message => 'Entering Insert_ACC_Record'
115 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
116 END IF;
117
118 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
119 jtm_message_log_pkg.Log_Msg
120 ( v_object_id => p_csp_inv_loc_assignment_id
121 , v_object_name => g_table_name
122 , v_message => 'Inserting ACC record for resource_id = ' || p_resource_id
123 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
124 END IF;
125
126 /*** Call common package to insert record into ACC table ***/
127 JTM_HOOK_UTIL_PKG.Insert_Acc
128 ( p_publication_item_names => g_publication_item_name
129 ,p_acc_table_name => g_acc_table_name
130 ,p_pk1_name => g_pk1_name
131 ,p_pk1_num_value => p_csp_inv_loc_assignment_id
132 ,p_resource_id => p_resource_id
133 );
134
135 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
136 jtm_message_log_pkg.Log_Msg
137 ( v_object_id => p_csp_inv_loc_assignment_id
138 , v_object_name => g_table_name
139 , v_message => 'Leaving Insert_ACC_Record'
140 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
141 END IF;
142
143 END Insert_ACC_Record;
144
145 /*** Private procedure that re-sends given location assignment to mobile ***/
146 PROCEDURE Update_ACC_Record
147 ( p_csp_inv_loc_assignment_id IN NUMBER
148 ,p_resource_id IN NUMBER
149 ,p_acc_id IN NUMBER
150 )
151 IS
152
153 BEGIN
154 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
155 jtm_message_log_pkg.Log_Msg
156 ( v_object_id => p_csp_inv_loc_assignment_id
157 , v_object_name => g_table_name
158 , v_message => 'Entering Update_ACC_Record'
159 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
160 END IF;
161
162 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
163 jtm_message_log_pkg.Log_Msg
164 ( v_object_id => p_csp_inv_loc_assignment_id
165 , v_object_name => g_table_name
166 , v_message => 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) ||
167 'access_id = ' || p_acc_id
168 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
169 END IF;
170
171 /*** Update Inventor Location Assignment ACC record ***/
172 JTM_HOOK_UTIL_PKG.Update_Acc
173 ( p_publication_item_names => g_publication_item_name
174 ,p_acc_table_name => g_acc_table_name
175 ,p_resource_id => p_resource_id
176 ,p_access_id => p_acc_id
177 );
178
179 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
180 jtm_message_log_pkg.Log_Msg
181 ( v_object_id => p_csp_inv_loc_assignment_id
182 , v_object_name => g_table_name
183 , v_message => 'Leaving Update_ACC_Record'
184 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
185 END IF;
186 END Update_ACC_Record;
187
188 /*** Private procedure that deletes assignment for resource from acc table ***/
189 PROCEDURE Delete_ACC_Record
190 ( p_csp_inv_loc_assignment_id IN NUMBER
191 ,p_resource_id IN NUMBER
192 )
193 IS
194
195 BEGIN
196 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
197 jtm_message_log_pkg.Log_Msg
198 ( v_object_id => p_csp_inv_loc_assignment_id
199 , v_object_name => g_table_name
200 , v_message => 'Entering Delete_ACC_Record'
201 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
202 END IF;
203
204 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
205 jtm_message_log_pkg.Log_Msg
206 ( v_object_id => p_csp_inv_loc_assignment_id
207 , v_object_name => g_table_name
208 , v_message => 'Deleting ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) ||
209 'Location assignment = ' || p_csp_inv_loc_assignment_id
210 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
211 END IF;
212
213 /*** Delete Inventor Location Assignment ACC record ***/
214 JTM_HOOK_UTIL_PKG.Delete_Acc
215 ( p_publication_item_names => g_publication_item_name
216 ,p_acc_table_name => g_acc_table_name
217 ,p_pk1_name => g_pk1_name
218 ,p_pk1_num_value => p_csp_inv_loc_assignment_id
219 ,p_resource_id => p_resource_id
220 );
221
222 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
223 jtm_message_log_pkg.Log_Msg
224 ( v_object_id => p_csp_inv_loc_assignment_id
225 , v_object_name => g_table_name
226 , v_message => 'Leaving Delete_ACC_Record'
227 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
228 END IF;
229 END Delete_ACC_Record;
230
231 /***
232 Public function that gets called when a Inventory Location Assignment needs to be inserted into ACC table.
233 Returns TRUE when record already was or has been inserted into ACC table.
234 ***/
235 FUNCTION Pre_Insert_Child
236 ( p_csp_inv_loc_assignment_id IN NUMBER
237 ,p_resource_id IN NUMBER
238 )
239 RETURN BOOLEAN
240 IS
241 CURSOR c_retreive_org_name(b_loc_assignment_id NUMBER) IS
242 SELECT organization_id, subinventory_code
243 FROM CSP_INV_LOC_ASSIGNMENTS
244 WHERE csp_inv_loc_assignment_id = b_loc_assignment_id;
245
246 r_retreive_org_name c_retreive_org_name%ROWTYPE;
247
248 l_success BOOLEAN;
249 BEGIN
250 /*** get debug level ***/
251 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
252
253 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
254 jtm_message_log_pkg.Log_Msg
255 ( v_object_id => p_csp_inv_loc_assignment_id
256 , v_object_name => g_table_name
257 , v_message => 'Entering Pre_Insert_Child procedure'
258 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
259 END IF;
260
261 l_success := FALSE;
262 /*** no -> does record match criteria? ***/
263 IF Replicate_Record( p_csp_inv_loc_assignment_id ) THEN
264
265 OPEN c_retreive_org_name( p_csp_inv_loc_assignment_id );
266 FETCH c_retreive_org_name INTO r_retreive_org_name;
267 IF NOT c_retreive_org_name%NOTFOUND THEN
268
269 IF CSL_CSP_SEC_INV_ACC_PKG.Insert_CSP_Sec_Inventory(
270 p_resource_id
271 ,r_retreive_org_name.subinventory_code
272 ,r_retreive_org_name.organization_id) THEN
273
274 Insert_ACC_Record
275 ( p_csp_inv_loc_assignment_id
276 , p_resource_id
277 );
278
279 CSL_MTL_MAT_TRANS_ACC_PKG.Insert_MTL_Mat_Transaction(
280 p_resource_id,
281 r_retreive_org_name.subinventory_code,
282 r_retreive_org_name.organization_id
283 );
284
285 l_success := TRUE;
286 END IF;
287
288 END IF;
289 CLOSE c_retreive_org_name;
290 END IF;
291
292 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
293 jtm_message_log_pkg.Log_Msg
294 ( p_csp_inv_loc_assignment_id
295 , g_table_name
296 , 'Leaving Pre_Insert_Child procedure'
297 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
298 END IF;
299
300 RETURN l_success;
301 END Pre_Insert_Child;
302
303 /***
304 Public procedure that gets called when a Inventory Location Assignment needs to be deleted from ACC table.
305 ***/
306 PROCEDURE Post_Delete_Child
307 ( p_csp_inv_loc_assignment_id IN NUMBER
308 ,p_resource_id IN NUMBER
309 )
310 IS
311
312 CURSOR c_retreive_org_name(b_loc_assignment_id NUMBER) IS
313 SELECT organization_id, subinventory_code
314 FROM CSP_INV_LOC_ASSIGNMENTS
315 WHERE csp_inv_loc_assignment_id = b_loc_assignment_id;
316
317 r_retreive_org_name c_retreive_org_name%ROWTYPE;
318
319 l_return_value BOOLEAN;
320
321 BEGIN
322 /*** get debug level ***/
323 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
324
325 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
326 jtm_message_log_pkg.Log_Msg
327 ( p_csp_inv_loc_assignment_id
328 , g_table_name
329 , 'Entering Post_Delete_Child'
330 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
331 END IF;
332
333 /*** no -> delete task record from ACC ***/
334 Delete_ACC_Record(
335 p_csp_inv_loc_assignment_id
336 ,p_resource_id);
337
338 OPEN c_retreive_org_name( p_csp_inv_loc_assignment_id);
339 FETCH c_retreive_org_name INTO r_retreive_org_name;
340 IF c_retreive_org_name%FOUND THEN
341
342 l_return_value := CSL_CSP_SEC_INV_ACC_PKG.Delete_CSP_Sec_Inventory(
343 p_resource_id
344 ,r_retreive_org_name.subinventory_code
345 ,r_retreive_org_name.organization_id);
346
347 /* CSL_MTL_MAT_TRANS_ACC_PKG.Delete_MTL_Mat_Transaction(
348 p_resource_id,
349 r_retreive_org_name.subinventory_code,
350 r_retreive_org_name.organization_id); */
351
352 END IF;
353 CLOSE c_retreive_org_name;
354
355 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
356 jtm_message_log_pkg.Log_Msg
357 ( p_csp_inv_loc_assignment_id
358 , g_table_name
359 , 'Leaving Post_Delete_Child'
360 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
361 END IF;
362 END Post_Delete_Child;
363
364 /*** Called before assignment Insert ***/
365 PROCEDURE PRE_INSERT_INV_LOC_ASSIGNMENT
366 ( x_return_status OUT NOCOPY varchar2
367 )
368 IS
369 BEGIN
370 x_return_status := FND_API.G_RET_STS_SUCCESS;
371 END PRE_INSERT_INV_LOC_ASSIGNMENT;
372
373 /*** Called after assignment Insert ***/
374 PROCEDURE POST_INSERT_INV_LOC_ASSIGNMENT
378 l_resource_id NUMBER;
375 ( x_return_status OUT NOCOPY varchar2
376 )
377 IS
379 l_csp_inv_loc_assignment_id NUMBER;
380 l_dummy BOOLEAN;
381 CURSOR c_resource( b_csp_inv_loc_assignment_id NUMBER ) IS
382 SELECT resource_id
383 FROM csp_inv_loc_assignments
384 WHERE csp_inv_loc_assignment_id = b_csp_inv_loc_assignment_id;
385 l_enabled_flag VARCHAR2(30);
386 BEGIN
387 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
388 IF l_enabled_flag <> 'Y' THEN
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390 RETURN;
391 END IF;
392 /*** get debug level ***/
393 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
394
395 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
396 jtm_message_log_pkg.Log_Msg
397 ( v_object_id => l_csp_inv_loc_assignment_id
398 , v_object_name => g_table_name
399 , v_message => 'Entering POST_INSERT hook'
400 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
401 END IF;
402
403 /*** get assignment record details from public API ***/
404 l_csp_inv_loc_assignment_id := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
405
406 OPEN c_resource( l_csp_inv_loc_assignment_id );
407 FETCH c_resource INTO l_resource_id;
408 IF c_resource%FOUND THEN
409 /*** Insert record if applicable ***/
410 l_dummy := Pre_Insert_Child
411 ( l_csp_inv_loc_assignment_id
412 ,l_resource_id
413 );
414 ELSE
415 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
416 jtm_message_log_pkg.Log_Msg
417 ( v_object_id => l_csp_inv_loc_assignment_id
418 , v_object_name => g_table_name
419 , v_message => 'Cannot find resource for inv loc assignment '||l_csp_inv_loc_assignment_id
420 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
421 END IF;
422 END IF;
423 CLOSE c_resource;
424
425 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
426 jtm_message_log_pkg.Log_Msg
427 ( v_object_id => l_csp_inv_loc_assignment_id
428 , v_object_name => g_table_name
429 , v_message => 'Leaving POST_INSERT hook'
430 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
431 END IF;
432
433 x_return_status := FND_API.G_RET_STS_SUCCESS;
434 RETURN;
435
436 EXCEPTION WHEN OTHERS THEN
437 /*** hook failed -> log error ***/
438 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
439 jtm_message_log_pkg.Log_Msg
440 ( v_object_id => l_csp_inv_loc_assignment_id
441 , v_object_name => g_table_name
442 , v_message => 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
443 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
444 END IF;
445 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','POST_INSERT_INV_LOC_ASSIGNMENT',sqlerrm);
446 -- x_return_status := FND_API.G_RET_STS_ERROR;
447 x_return_status := FND_API.G_RET_STS_SUCCESS;
448 END POST_INSERT_INV_LOC_ASSIGNMENT;
449
450 /* Called before assignment Update */
451 PROCEDURE PRE_UPDATE_INV_LOC_ASSIGNMENT
452 ( x_return_status OUT NOCOPY varchar2
453 )
454 IS
455 CURSOR c_csp_inv_loc_assignment( b_csp_inv_loc_ass_id NUMBER ) IS
456 SELECT resource_id
457 FROM csp_inv_loc_assignments -- don't use synonym as that one filters on OWNER records
458 WHERE csp_inv_loc_assignment_id = b_csp_inv_loc_ass_id;
459
460 r_csp_inv_loc_assignment c_csp_inv_loc_assignment%ROWTYPE;
461 l_csp_inv_loc_assignment NUMBER;
462 l_enabled_flag VARCHAR2(30);
463 BEGIN
464 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
465 IF l_enabled_flag <> 'Y' THEN
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467 RETURN;
468 END IF;
469 /*** get assignment record details from public API ***/
470 l_csp_inv_loc_assignment := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
471
472 /*** get debug level ***/
473 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
474
475 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
476 jtm_message_log_pkg.Log_Msg
477 ( v_object_id => l_csp_inv_loc_assignment
478 , v_object_name => g_table_name
479 , v_message => 'Entering PRE_UPDATE hook'
480 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
481 END IF;
482
483 /*** retrieve old resource_id for task assignment ***/
484 OPEN c_csp_inv_loc_assignment(l_csp_inv_loc_assignment);
485 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
486 g_old_resource_id := r_csp_inv_loc_assignment.resource_id;
487 CLOSE c_csp_inv_loc_assignment;
488
489 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
490 jtm_message_log_pkg.Log_Msg
491 ( v_object_id => l_csp_inv_loc_assignment
492 , v_object_name => g_table_name
493 , v_message => 'Leaving PRE_UPDATE hook'
494 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
495 END IF;
496
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498
499 EXCEPTION WHEN OTHERS THEN
500 /*** hook failed -> log error ***/
501 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
502 jtm_message_log_pkg.Log_Msg
506 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
503 ( v_object_id => l_csp_inv_loc_assignment
504 , v_object_name => g_table_name
505 , v_message => 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
507 END IF;
508 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','PRE_UPDATE_INV_LOC_ASSIGNMENT',sqlerrm);
509 -- x_return_status := FND_API.G_RET_STS_ERROR;
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511 END PRE_UPDATE_INV_LOC_ASSIGNMENT;
512
513 /* Called after assignment Update */
514 PROCEDURE POST_UPDATE_INV_LOC_ASSIGNMENT
515 ( x_return_status OUT NOCOPY varchar2
516 )
517 IS
518 CURSOR c_retreive_org_name(b_loc_assignment_id NUMBER) IS
519 SELECT organization_id, subinventory_code
520 FROM CSP_INV_LOC_ASSIGNMENTS
521 WHERE csp_inv_loc_assignment_id = b_loc_assignment_id;
522
523 r_retreive_org_name c_retreive_org_name%ROWTYPE;
524
525 l_resource_id NUMBER;
526 l_csp_inv_loc_assignment NUMBER;
527 l_replicate BOOLEAN;
528 l_dummy BOOLEAN;
529 l_acc_id NUMBER;
530
531 CURSOR c_csp_inv_loc_assignment( b_csp_inv_loc_ass_id NUMBER ) IS
532 SELECT resource_id
533 FROM csp_inv_loc_assignments -- don't use synonym as that one filters on OWNER records
534 WHERE csp_inv_loc_assignment_id = b_csp_inv_loc_ass_id;
535
536 r_csp_inv_loc_assignment c_csp_inv_loc_assignment%ROWTYPE;
537
538 l_enabled_flag VARCHAR2(30);
539 BEGIN
540 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
541 IF l_enabled_flag <> 'Y' THEN
542 x_return_status := FND_API.G_RET_STS_SUCCESS;
543 RETURN;
544 END IF;
545 /*** get debug level ***/
546 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
547
548 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
549 jtm_message_log_pkg.Log_Msg
550 ( v_object_id => l_csp_inv_loc_assignment
551 , v_object_name => g_table_name
552 , v_message => 'Entering POST_UPDATE hook'
553 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
554 END IF;
555
556 /*** get assignment record details from public API ***/
557 l_csp_inv_loc_assignment := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
558
559 /*** retrieve 'new' resource_id for task assignment ***/
560 OPEN c_csp_inv_loc_assignment(l_csp_inv_loc_assignment);
561 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
562 l_resource_id := r_csp_inv_loc_assignment.resource_id;
563 CLOSE c_csp_inv_loc_assignment;
564
565
566 /*** did resource_id get changed? ***/
567 IF (g_old_resource_id <> l_resource_id) THEN
568 /*** yes -> do cascading delete for old resource_id ***/
569 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
570 jtm_message_log_pkg.Log_Msg
571 ( l_csp_inv_loc_assignment
572 , g_table_name
573 , 'Invntory Location Assignment resource_id changed from ' || g_old_resource_id ||
574 ' to ' || l_resource_id || '.' || fnd_global.local_chr(10) ||
575 'Deleting old assignment ACC record (if exists).'
576 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
577 END IF;
578
579 /*** DELETE SEQUENCE !!!!!! ***/
580
581 ELSE
582 /*** resource_id is same as before the update -> check if it already exists on mobile ***/
583 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
584 ( P_ACC_TABLE_NAME => g_acc_table_name
585 ,P_PK1_NAME => g_pk1_name
586 ,P_PK1_NUM_VALUE => l_csp_inv_loc_assignment
587 ,P_RESOURCE_ID => l_resource_id);
588 END IF;
589
590 /*** check if updated record needs to be replicated ***/
591 l_replicate := Replicate_Record( l_csp_inv_loc_assignment );
592 IF l_replicate THEN
593 /*** Check if it is going to be Update or Insert! ***/
594 IF l_acc_id = -1 THEN
595 /*** Insert ! ***/
596 l_dummy := Pre_Insert_Child
597 ( l_csp_inv_loc_assignment
598 ,l_resource_id
599 );
600 ELSE
601 /*** Update ! ***/
602 OPEN c_retreive_org_name( l_csp_inv_loc_assignment );
603 FETCH c_retreive_org_name INTO r_retreive_org_name;
604 IF NOT c_retreive_org_name%NOTFOUND THEN
605 CSL_CSP_SEC_INV_ACC_PKG.Update_CSP_Sec_Inventory(
606 l_resource_id ,
607 r_retreive_org_name.subinventory_code ,
608 r_retreive_org_name.organization_id);
609
610 Update_ACC_Record
611 ( l_csp_inv_loc_assignment
612 , l_resource_id
613 , l_acc_id
614 );
615
616 END IF;
617 CLOSE c_retreive_org_name;
618 END IF;
619 ELSE
620
621 /*** ??????????????????????????????????? ***/
622 /*** record should not be replicated ***/
623 IF l_acc_id > -1 THEN
624 /*** record exists on mobile but should not be replicated anymore -> delete from mobile ***/
625 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
626 jtm_message_log_pkg.Log_Msg
627 ( l_csp_inv_loc_assignment
631 END IF;
628 , g_table_name
629 , 'Inventory Location Assignment was replicated before update, but should not be replicated anymore.'
630 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
632
633 Post_Delete_Child
634 ( l_csp_inv_loc_assignment
635 ,l_resource_id);
636 END IF;
637 END IF;
638
639 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
640 jtm_message_log_pkg.Log_Msg
641 ( v_object_id => l_csp_inv_loc_assignment
642 , v_object_name => g_table_name
643 , v_message => 'Leaving POST_UPDATE hook'
644 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
645 END IF;
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648 EXCEPTION WHEN OTHERS THEN
649 /*** hook failed -> log error ***/
650 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
651 jtm_message_log_pkg.Log_Msg
652 ( v_object_id => l_csp_inv_loc_assignment
653 , v_object_name => g_table_name
654 , v_message => 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
655 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
656 END IF;
657 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','POST_UPDATE_INV_LOC_ASSIGNMENT',sqlerrm);
658 -- x_return_status := FND_API.G_RET_STS_ERROR;
659 x_return_status := FND_API.G_RET_STS_SUCCESS;
660 END POST_UPDATE_INV_LOC_ASSIGNMENT;
661
662 /* Called before assignment Delete */
663 PROCEDURE PRE_DELETE_INV_LOC_ASSIGNMENT
664 ( x_return_status OUT NOCOPY varchar2
665 )
666 IS
667 l_resource_id NUMBER;
668 l_csp_inv_loc_assignment NUMBER;
669 l_enabled_flag VARCHAR2(30);
670 BEGIN
671 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
672 IF l_enabled_flag <> 'Y' THEN
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674 RETURN;
675 END IF;
676 /*** get assignment record details from public API ***/
677 l_csp_inv_loc_assignment := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
678 l_resource_id := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.RESOURCE_ID;
679
680 /*** get debug level ***/
681 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
682
683 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
684 jtm_message_log_pkg.Log_Msg
685 ( v_object_id => l_csp_inv_loc_assignment
686 , v_object_name => g_table_name
687 , v_message => 'Entering PRE_DELETE hook'
688 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
689 END IF;
690
691 /*** yes -> delete assignment related data from the ACC tables ***/
692 Post_Delete_Child
693 ( l_csp_inv_loc_assignment
694 ,l_resource_id);
695
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
699 jtm_message_log_pkg.Log_Msg
700 ( v_object_id => l_csp_inv_loc_assignment
701 , v_object_name => g_table_name
702 , v_message => 'Leaving PRE_DELETE hook'
703 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
704 END IF;
705
706 EXCEPTION WHEN OTHERS THEN
707 /*** hook failed -> log error ***/
708 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
709 jtm_message_log_pkg.Log_Msg
710 ( v_object_id => l_csp_inv_loc_assignment
711 , v_object_name => g_table_name
712 , v_message => 'Caught exception in PRE_DELETE hook:' || fnd_global.local_chr(10) || sqlerrm
713 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
714 END IF;
715 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','PRE_DELETE_INV_LOC_ASSIGNMENT',sqlerrm);
716 -- x_return_status := FND_API.G_RET_STS_ERROR;
717 x_return_status := FND_API.G_RET_STS_SUCCESS;
718 END PRE_DELETE_INV_LOC_ASSIGNMENT;
719
720 /* Called after assignment Delete */
721 PROCEDURE POST_DELETE_INV_LOC_ASSIGNMENT
722 ( x_return_status OUT NOCOPY varchar2
723 )
724 IS
725 BEGIN
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727 END POST_DELETE_INV_LOC_ASSIGNMENT;
728
729 /* Remove all ACC records of a mobile user */
730 PROCEDURE Delete_All_ACC_Records
731 ( p_resource_id in NUMBER
732 , x_return_status OUT NOCOPY varchar2
733 )
734 IS
735
736 CURSOR c_csp_inv_loc_assignment (b_resource_id NUMBER) IS
737 SELECT *
738 FROM jtm_csp_inv_loc_ass_acc
739 WHERE RESOURCE_ID = b_resource_id;
740 r_csp_inv_loc_assignment c_csp_inv_loc_assignment%ROWTYPE;
741
742 -- l_return_value VARCHAR2(2000) := FND_API.G_RET_STS_ERROR;
743 l_return_value VARCHAR2(2000) := FND_API.G_RET_STS_SUCCESS;
744 l_dummy BOOLEAN;
745 BEGIN
746 g_debug_level := JTM_HOOK_UTIL_PKG.GET_DEBUG_LEVEL;
747 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
748 jtm_message_log_pkg.Log_Msg
749 ( p_resource_id
750 , g_table_name
751 , 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
752 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
753 END IF;
754 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
755 jtm_message_log_pkg.Log_Msg
756 ( p_resource_id
757 , g_table_name
758 , 'Delete all Inventory Location Assignemts acc records for user: ' || p_resource_id
762 OPEN c_csp_inv_loc_assignment( p_resource_id );
759 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
760 END IF;
761
763 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
764 IF c_csp_inv_loc_assignment%NOTFOUND THEN
765 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
766 jtm_message_log_pkg.Log_Msg
767 ( v_object_id => p_resource_id
768 , v_object_name => g_table_name
769 , v_message => 'There are no rows returned for user : ' || p_resource_id
770 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
771 END IF;
772 ELSE
773 WHILE c_csp_inv_loc_assignment%FOUND LOOP
774 Post_Delete_Child
775 ( r_csp_inv_loc_assignment.csp_inv_loc_assignment_id
776 , p_resource_id
777 );
778 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
779 END LOOP;
780 END IF;
781 CLOSE c_csp_inv_loc_assignment;
782
783 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
784 jtm_message_log_pkg.Log_Msg
785 ( p_resource_id
786 , g_table_name
787 , 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
788 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
789 END IF;
790
791 x_return_status := FND_API.G_RET_STS_SUCCESS;
792 EXCEPTION WHEN OTHERS THEN
793 /*** hook failed -> log error ***/
794 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_HEADERS_ACC_PKG','Delete_All_ACC_Records',sqlerrm);
795 -- x_return_status := FND_API.G_RET_STS_ERROR;
796 x_return_status := FND_API.G_RET_STS_SUCCESS;
797 END Delete_All_ACC_Records;
798
799 /* Full synch for a mobile user */
800 PROCEDURE Insert_All_ACC_Records
801 ( p_resource_id IN NUMBER
802 , x_return_status OUT NOCOPY VARCHAR2
803 )
804 IS
805
806 CURSOR c_csp_inv_loc_assignment (b_resource_id NUMBER) IS
807 SELECT *
808 FROM CSP_INV_LOC_ASSIGNMENTS
809 WHERE RESOURCE_ID = b_resource_id
810 AND RESOURCE_TYPE = 'RS_EMPLOYEE' ;
811
812 r_csp_inv_loc_assignment c_csp_inv_loc_assignment%ROWTYPE;
813
814 -- l_return_value VARCHAR2(2000) := FND_API.G_RET_STS_ERROR;
815 l_return_value VARCHAR2(2000) := FND_API.G_RET_STS_SUCCESS;
816 l_dummy BOOLEAN;
817 BEGIN
818 g_debug_level := JTM_HOOK_UTIL_PKG.GET_DEBUG_LEVEL;
819 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
820 jtm_message_log_pkg.Log_Msg
821 ( p_resource_id
822 , g_table_name
823 , 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
824 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
825 END IF;
826 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
827 jtm_message_log_pkg.Log_Msg
828 ( p_resource_id
829 , g_table_name
830 , 'Insert all Inventory Location Assignments acc records for user: ' || p_resource_id
831 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
832 END IF;
833
834 /*** Insert all of the ACC Records of Requirement Lines ***/
835
836 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
837 /*** Retreive record assigned by Hook ***/
838 OPEN c_csp_inv_loc_assignment( p_resource_id );
839 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
840 IF c_csp_inv_loc_assignment%NOTFOUND THEN
841 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
842 jtm_message_log_pkg.Log_Msg
843 ( v_object_id => p_resource_id
844 , v_object_name => g_table_name
845 , v_message => 'There are no rows returned for user : ' || p_resource_id
846 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
847 END IF;
848 ELSE
849 WHILE c_csp_inv_loc_assignment%FOUND LOOP
850 l_dummy := Pre_Insert_Child
851 ( r_csp_inv_loc_assignment.csp_inv_loc_assignment_id
852 , p_resource_id
853 );
854 FETCH c_csp_inv_loc_assignment INTO r_csp_inv_loc_assignment;
855 END LOOP;
856 END IF;
857 CLOSE c_csp_inv_loc_assignment;
858 l_return_value := FND_API.G_RET_STS_SUCCESS;
859 ELSE
860 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
861 jtm_message_log_pkg.Log_Msg
862 ( v_object_id => p_resource_id
863 , v_object_name => g_table_name
864 , v_message => 'User with resource id : ' || p_resource_id || ' is not a Mobile User.'
865 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
866 END IF;
867 END IF;
868
869 x_return_status := l_return_value;
870 EXCEPTION WHEN OTHERS THEN
871 /*** hook failed -> log error ***/
872 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_HEADERS_ACC_PKG','Insert_All_ACC_Records',sqlerrm);
873 -- x_return_status := FND_API.G_RET_STS_ERROR;
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875 END Insert_All_ACC_Records;
876
877 END CSL_CSP_INV_LOC_ASS_ACC_PKG;