[Home] [Help]
PACKAGE BODY: APPS.CSL_MTL_ITEM_LOCATIONS_ACC_PKG
Source
1 PACKAGE BODY CSL_MTL_ITEM_LOCATIONS_ACC_PKG AS
2 /* $Header: cslmlacb.pls 115.1 2003/10/24 23:35:06 yliao noship $ */
3
4 /*
5 This package will be called from CSL_CSP_INV_LOC_ASS_ACC_PKG.
6 Pre_Insert_Child(..).
7 When assigning a sub-inventory to a resource, we check the
8 mtl_item_locations records associated with this sub-inventory,
9 and insert them into the CSL_MTL_ITEM_LOCATIONS_ACC table.
10 Same check applies for deletion of system items.
11
12 We also need functions to be called for upgrade of exsting users
13 without new subinventory assignments.
14 */
15
16 /*** Globals ***/
17 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_MTL_ITEM_LOCATIONS_ACC';
18 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
19 JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_ITEM_LOCATIONS');
20 g_table_name CONSTANT VARCHAR2(30) := 'MTL_ITEM_LOCATIONS';
21 g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
22 g_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
23 g_debug_level NUMBER; -- debug level
24
25 /*
26 Private procedure that re-pushes replicated item locations
27 that were updated since the last time the concurrent program ran.
28 */
29 PROCEDURE UPDATE_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
30 IS
31 CURSOR c_changed( b_last_date DATE ) IS
32 SELECT acc.ACCESS_ID, acc.RESOURCE_ID
33 FROM CSL_MTL_ITEM_LOCATIONS_ACC acc
34 , MTL_ITEM_LOCATIONS b
35 , ASG_USER au
36 WHERE b.INVENTORY_LOCATION_ID = acc.INVENTORY_LOCATION_ID
37 AND b.ORGANIZATION_ID = acc.ORGANIZATION_ID
38 AND au.RESOURCE_ID = acc.RESOURCE_ID
39 AND b.LAST_UPDATE_DATE >= b_last_date;
40
41 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
42 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
43 l_dummy BOOLEAN;
44
45 BEGIN
46 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
47 jtm_message_log_pkg.Log_Msg
48 ( 0
49 , g_table_name
50 , 'Entering UPDATE_ACC_REC_MARKDIRTY'
51 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
52 );
53 END IF;
54
55 /*Fetch all changed item locations that are in the acc table*/
56 OPEN c_changed( p_last_run_date );
57 FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
58 /*Call oracle lite*/
59 IF l_tab_access_id.COUNT > 0 THEN
60 /*** 1 or more acc rows retrieved -> push to resource ***/
61 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
62 jtm_message_log_pkg.Log_Msg
63 ( 0
64 , g_table_name
65 , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
66 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
67 );
68 END IF;
69 /*** push to oLite using asg_download ***/
70 l_dummy := asg_download.markdirty(
71 P_PUB_ITEM => g_publication_item_name(1)
72 , P_ACCESSLIST => l_tab_access_id
73 , P_RESOURCELIST => l_tab_resource_id
74 , P_DML_TYPE => 'U'
75 , P_TIMESTAMP => SYSDATE
76 );
77 END IF;
78 CLOSE c_changed;
79
80 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
81 jtm_message_log_pkg.Log_Msg
82 ( 0
83 , g_table_name
84 , 'Leaving UPDATE_ACC_REC_MARKDIRTY'
85 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
86 );
87 END IF;
88 EXCEPTION
89 WHEN OTHERS THEN
90 jtm_message_log_pkg.Log_Msg
91 ( 0
92 , g_table_name
93 , 'UPDATE_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
94 'Error: '||sqlerrm
95 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
96 RAISE;
97 END UPDATE_ACC_REC_MARKDIRTY;
98
99 /*
100 Private procedure that re-pushes replicated item locations
101 that were inserted since the last time the concurrent program ran.
102 */
103 PROCEDURE INSERT_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
104 IS
105 CURSOR c_inserted( b_last_date DATE ) IS
106 SELECT CSL_ACC_SEQUENCE.NEXTVAL, SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID, SEC.COUNTER
107 FROM JTM_MTL_SEC_INV_ACC SEC, MTL_ITEM_LOCATIONS LOC
108 WHERE SEC.SECONDARY_INVENTORY_NAME = LOC.SUBINVENTORY_CODE
109 AND SEC.ORGANIZATION_ID = LOC.ORGANIZATION_ID
110 AND LOC.CREATION_DATE >= NVL(b_last_date, LOC.CREATION_DATE)
111 AND (SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID)
112 NOT IN
113 ( SELECT RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID
114 FROM CSL_MTL_ITEM_LOCATIONS_ACC
115 );
116
117 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
118 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
119 TYPE location_Tab IS TABLE OF MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
120 TYPE org_Tab IS TABLE OF MTL_ITEM_LOCATIONS.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
121 TYPE counter_Tab IS TABLE OF JTM_MTL_SEC_INV_ACC.COUNTER%TYPE INDEX BY BINARY_INTEGER;
122 locations location_Tab;
123 organizations org_Tab;
124 counters counter_Tab;
125
126 l_dummy BOOLEAN;
127 -- CSL.CSL_ACC_SEQUENCE
128 BEGIN
129 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
130 jtm_message_log_pkg.Log_Msg
131 ( 0
132 , g_table_name
133 , 'Entering INSERT_ACC_REC_MARKDIRTY'
134 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
135 );
136 END IF;
137
138 /*Increment count if the record already exists */
139 UPDATE CSL_MTL_ITEM_LOCATIONS_ACC
140 SET COUNTER = COUNTER + 1
141 , LAST_UPDATE_DATE = SYSDATE
142 , LAST_UPDATED_BY = 1
143 WHERE ( RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) IN
144 ( SELECT SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID
145 FROM JTM_MTL_SEC_INV_ACC SEC, MTL_ITEM_LOCATIONS LOC
146 WHERE SEC.SECONDARY_INVENTORY_NAME = LOC.SUBINVENTORY_CODE
147 AND LOC.CREATION_DATE >= NVL(p_last_run_date, LOC.CREATION_DATE)
148 );
149
150 /*Fetch all changed item locations that are in the acc table*/
151 OPEN c_inserted( p_last_run_date );
152 FETCH c_inserted BULK COLLECT
153 INTO l_tab_access_id, l_tab_resource_id, locations, organizations, counters;
154 /*Call oracle lite*/
155 IF l_tab_access_id.COUNT > 0 THEN
156 /*** 1 or more acc rows retrieved -> push to resource ***/
157 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
158 jtm_message_log_pkg.Log_Msg
159 ( 0
160 , g_table_name
161 , 'Pushing ' || l_tab_access_id.COUNT || ' inserted record(s)'
162 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
163 );
164 END IF;
165
166 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
167 INSERT INTO CSL_MTL_ITEM_LOCATIONS_ACC(
168 ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
169 , COUNTER, RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) VALUES (
170 l_tab_access_id(i), sysdate, 1, sysdate, 1, counters(i), l_tab_resource_id(i), locations(i), organizations(i));
171
172 /*** push to oLite using asg_download ***/
173 l_dummy := asg_download.markdirty(
174 P_PUB_ITEM => g_publication_item_name(1)
175 , P_ACCESSLIST => l_tab_access_id
176 , P_RESOURCELIST => l_tab_resource_id
177 , P_DML_TYPE => 'I'
178 , P_TIMESTAMP => SYSDATE
179 );
180 END IF;
181 CLOSE c_inserted;
182
183 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
184 jtm_message_log_pkg.Log_Msg
185 ( 0
186 , g_table_name
187 , 'Leaving INSERT_ACC_REC_MARKDIRTY'
188 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
189 );
190 END IF;
191 EXCEPTION
192 WHEN OTHERS THEN
193 jtm_message_log_pkg.Log_Msg
194 ( 0
195 , g_table_name
196 , 'INSERT_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
197 'Error: '||sqlerrm
198 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
199 RAISE;
200 END INSERT_ACC_REC_MARKDIRTY;
201
202 /*
203 This function will be called from
204 CSL_CSP_INV_LOC_ASS_ACC_PKG.Pre_Insert_Child(...)
205 It gets all the records associated with sub-inventories.
206 */
207 PROCEDURE Insert_Item_Locs_By_Subinv
208 ( p_subinventory_code IN VARCHAR2
209 , p_organization_id IN NUMBER
210 , p_resource_id IN NUMBER
211 )
212 IS
213 CURSOR c_item_loc_by_subinv ( b_organization_id NUMBER,
214 b_subinventory_code VARCHAR2 )
215 IS
216 SELECT INVENTORY_LOCATION_ID
217 FROM MTL_ITEM_LOCATIONS
218 WHERE ORGANIZATION_ID = b_organization_id
219 AND SUBINVENTORY_CODE = b_subinventory_code
220 AND (DISABLE_DATE > sysdate OR DISABLE_DATE IS NULL)
221 ;
222 r_item_loc_by_subinv c_item_loc_by_subinv%ROWTYPE;
223 BEGIN
224 FOR r_item_loc_by_subinv IN c_item_loc_by_subinv (p_organization_id, p_subinventory_code)
225 LOOP
226 Insert_Item_Location(
227 r_item_loc_by_subinv.inventory_location_id,
228 p_organization_id,
229 p_resource_id);
230 END LOOP;
231 END Insert_Item_Locs_By_Subinv;
232
233 /*
234 This function will be called from
235 CSL_CSP_INV_LOC_ASS_ACC_PKG.Post_Delete_Child(...)
236 It deletes all the records associated with sub-inventories.
237 */
238 PROCEDURE Delete_Item_Locs_By_Subinv
239 ( p_subinventory_code IN VARCHAR2
240 , p_organization_id IN NUMBER
241 , p_resource_id IN NUMBER
242 )
243 IS
244 CURSOR c_item_loc_by_subinv ( b_organization_id NUMBER,
245 b_subinventory_code VARCHAR2,
246 b_resource_id NUMBER )
247 IS
248 SELECT B.INVENTORY_LOCATION_ID
249 FROM MTL_ITEM_LOCATIONS B, CSL_MTL_ITEM_LOCATIONS_ACC A
250 WHERE B.ORGANIZATION_ID = b_organization_id
251 AND B.SUBINVENTORY_CODE = b_subinventory_code
252 AND A.RESOURCE_ID = b_resource_id
253 AND B.ORGANIZATION_ID = A.ORGANIZATION_ID
254 AND B.INVENTORY_LOCATION_ID = A.INVENTORY_LOCATION_ID
255 ;
256 r_item_loc_by_subinv c_item_loc_by_subinv%ROWTYPE;
257 BEGIN
258 FOR r_item_loc_by_subinv IN c_item_loc_by_subinv (p_organization_id, p_subinventory_code, p_resource_id)
259 LOOP
260 Delete_Item_Location(
261 r_item_loc_by_subinv.inventory_location_id,
262 p_organization_id,
263 p_resource_id);
264 END LOOP;
265 END Delete_Item_Locs_By_Subinv;
266
267 /*
268 This function will be called from
269 CSL_MTL_SEC_LOCATORS_ACC_PKG.Insert_Secondary_Locators(...)
270 and Insert_Item_Locs_By_Subinv.
271 It gets all records for MTL_SEC_LOCATORS records.
272 */
273 PROCEDURE Insert_Item_Location
274 ( p_inventory_location_id IN NUMBER
275 , p_organization_id IN NUMBER
276 , p_resource_id IN NUMBER
277 )
278 IS
279 BEGIN
280 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
281
282 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
283 jtm_message_log_pkg.Log_Msg
284 ( v_object_id => p_inventory_location_id
285 , v_object_name => g_table_name
286 , v_message => 'Entering Insert_Item_Location'
287 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
288 END IF;
289
290 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
291 jtm_message_log_pkg.Log_Msg
292 ( v_object_id => p_organization_id
293 , v_object_name => g_table_name
294 , v_message => 'Inserting ACC record :' || p_inventory_location_id || ' for resource id '
295 || p_resource_id
296 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
297 END IF;
298
299 /*** Call common package to insert record into ACC table ***/
300 JTM_HOOK_UTIL_PKG.Insert_Acc
301 ( p_publication_item_names => g_publication_item_name
302 ,p_acc_table_name => g_acc_table_name
303 ,p_resource_id => p_resource_id
304 ,p_pk1_name => g_pk1_name
305 ,p_pk1_char_value => p_inventory_location_id
306 ,p_pk2_name => g_pk2_name
307 ,p_pk2_num_value => p_organization_id
308 );
309
310 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
311 jtm_message_log_pkg.Log_Msg
312 ( v_object_id => p_inventory_location_id
313 , v_object_name => g_table_name
314 , v_message => 'Leaving Insert_Item_Location'
315 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
316 END IF;
317 END Insert_Item_Location;
318
319 PROCEDURE Update_Item_Location
320 ( p_inventory_location_id IN NUMBER
321 , p_organization_id IN NUMBER
322 , p_resource_id IN NUMBER
323 )
324 IS
325 l_access_id NUMBER := NULL;
326 CURSOR c_item_location_acc (
327 b_inventory_location_id NUMBER
328 , b_organization_id NUMBER
329 , b_resource_id NUMBER )
330 IS
331 SELECT ACCESS_ID
332 FROM CSL_MTL_ITEM_LOCATIONS_ACC
333 WHERE INVENTORY_LOCATION_ID = b_inventory_location_id
334 AND ORGANIZATION_ID = b_organization_id
335 AND RESOURCE_ID = b_resource_id;
336 BEGIN
337 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
338
339 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
340 jtm_message_log_pkg.Log_Msg
341 ( v_object_id => p_inventory_location_id
342 , v_object_name => g_table_name
343 , v_message => 'Entering Update_Item_Location'
344 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
345 END IF;
346
347 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
348 jtm_message_log_pkg.Log_Msg
349 ( v_object_id => p_organization_id
350 , v_object_name => g_table_name
351 , v_message => 'Deleting ACC record :' || p_inventory_location_id || ' for resource id '
352 || p_resource_id
353 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
354 END IF;
355
356 OPEN c_item_location_acc(
357 p_inventory_location_id,
358 p_organization_id,
359 p_resource_id);
360 FETCH c_item_location_acc INTO l_access_id;
361 CLOSE c_item_location_acc;
362
363 IF l_access_id IS NOT NULL THEN
364 /*** Call common package to delete record from ACC table ***/
365 JTM_HOOK_UTIL_PKG.Update_Acc
366 ( p_publication_item_names => g_publication_item_name
367 ,p_acc_table_name => g_acc_table_name
368 ,p_resource_id => p_resource_id
369 ,p_access_id => l_access_id
370 );
371 END IF;
372
373 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
374 jtm_message_log_pkg.Log_Msg
375 ( v_object_id => p_inventory_location_id
376 , v_object_name => g_table_name
377 , v_message => 'Leaving Update_Item_Location'
378 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
379 END IF;
380 END Update_Item_Location;
381
382 PROCEDURE Delete_Item_Location
383 ( p_inventory_location_id IN NUMBER
384 , p_organization_id IN NUMBER
385 , p_resource_id IN NUMBER
386 )
387 IS
388 BEGIN
389 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
390
391 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
392 jtm_message_log_pkg.Log_Msg
393 ( v_object_id => p_inventory_location_id
394 , v_object_name => g_table_name
395 , v_message => 'Entering Delete_Item_Location'
396 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
397 END IF;
398
399 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
400 jtm_message_log_pkg.Log_Msg
401 ( v_object_id => p_organization_id
402 , v_object_name => g_table_name
403 , v_message => 'Deleting ACC record :' || p_inventory_location_id || ' for resource id '
404 || p_resource_id
405 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
406 END IF;
407
408 /*** Call common package to delete record from ACC table ***/
409 JTM_HOOK_UTIL_PKG.Delete_Acc
410 ( p_publication_item_names => g_publication_item_name
411 ,p_acc_table_name => g_acc_table_name
412 ,p_resource_id => p_resource_id
413 ,p_pk1_name => g_pk1_name
414 ,p_pk1_char_value => p_inventory_location_id
415 ,p_pk2_name => g_pk2_name
416 ,p_pk2_num_value => p_organization_id
417 );
418
419 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
420 jtm_message_log_pkg.Log_Msg
421 ( v_object_id => p_inventory_location_id
422 , v_object_name => g_table_name
423 , v_message => 'Leaving Delete_Item_Location'
424 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
425 END IF;
426 END Delete_Item_Location;
427
428 /*
429 Iterate over all the acc records for subinventories for given mobile user.
430 populate the CSL_MTL_ITEM_LOCATIONS_ACC records for all mobile users.
431 */
432 PROCEDURE POPULATE_ITEM_LOCATIONS_ACC
433 IS
434 PRAGMA AUTONOMOUS_TRANSACTION;
435 CURSOR c_inserted
436 IS SELECT CSL_ACC_SEQUENCE.NEXTVAL, A.RESOURCE_ID, L.INVENTORY_LOCATION_ID, L.ORGANIZATION_ID, A.COUNTER
437 FROM MTL_ITEM_LOCATIONS L, JTM_MTL_SEC_INV_ACC A
438 WHERE L.SUBINVENTORY_CODE = A.SECONDARY_INVENTORY_NAME
439 AND L.ORGANIZATION_ID = A.ORGANIZATION_ID;
440
441 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
442 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
443 TYPE location_Tab IS TABLE OF MTL_SECONDARY_LOCATORS.SECONDARY_LOCATOR%TYPE INDEX BY BINARY_INTEGER;
444 TYPE org_Tab IS TABLE OF MTL_SECONDARY_LOCATORS.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
445 TYPE counter_Tab IS TABLE OF JTM_MTL_SYSTEM_ITEMS_ACC.COUNTER%TYPE INDEX BY BINARY_INTEGER;
446 locations location_Tab;
447 organizations org_Tab;
448 counters counter_Tab;
449
450 l_dummy BOOLEAN;
451
452 BEGIN
453 DELETE FROM CSL_MTL_ITEM_LOCATIONS_ACC;
454
455 OPEN c_inserted;
456 FETCH c_inserted BULK COLLECT
457 INTO l_tab_access_id, l_tab_resource_id, locations, organizations, counters;
458 /*Call oracle lite*/
459 IF l_tab_access_id.COUNT > 0 THEN
460 /*** 1 or more acc rows retrieved -> push to resource ***/
461 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
462 jtm_message_log_pkg.Log_Msg
463 ( 0
464 , g_table_name
465 , 'Pushing ' || l_tab_access_id.COUNT || ' inserted record(s)'
466 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
467 );
468 END IF;
469
470 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
471 INSERT INTO CSL_MTL_ITEM_LOCATIONS_ACC(
472 ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
473 , COUNTER, RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) VALUES (
474 l_tab_access_id(i), sysdate, 1, sysdate, 1,
475 counters(i), l_tab_resource_id(i), locations(i), organizations(i));
476
477 /*** push to oLite using asg_download ***/
478 l_dummy := asg_download.markdirty(
479 P_PUB_ITEM => g_publication_item_name(1)
480 , P_ACCESSLIST => l_tab_access_id
481 , P_RESOURCELIST => l_tab_resource_id
482 , P_DML_TYPE => 'I'
483 , P_TIMESTAMP => SYSDATE
484 );
485 END IF;
486 CLOSE c_inserted;
487 COMMIT;
488 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
489 jtm_message_log_pkg.Log_Msg
490 ( 0
491 , g_table_name
492 , 'Leaving INSERT_ACC_REC_MARKDIRTY'
493 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
494 );
495 END IF;
496 EXCEPTION
497 WHEN OTHERS THEN
498 ROLLBACK;
499 jtm_message_log_pkg.Log_Msg
500 ( 0
501 , g_table_name
502 , 'INSERT_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
503 'Error: '||sqlerrm
504 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
505 RAISE;
506 END POPULATE_ITEM_LOCATIONS_ACC;
507
508 PROCEDURE CON_REQUEST_ITEM_LOCATIONS
509 IS
510 PRAGMA AUTONOMOUS_TRANSACTION;
511 /*** get the last run date of the concurent program ***/
512 CURSOR c_LastRundate
513 IS
514 select LAST_RUN_DATE
515 from JTM_CON_REQUEST_DATA
516 where package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'
517 AND procedure_name = 'CON_REQUEST_MTL_ITEM_LOCATIONS';
518 r_LastRundate c_LastRundate%ROWTYPE;
519 l_current_run_date DATE;
520 BEGIN
521 /*** get debug level ***/
522 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
523 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
524 jtm_message_log_pkg.Log_Msg
525 ( 0
526 , g_table_name
527 , 'Entering CON_REQUEST_MTL_ITEM_LOCATIONS'
528 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
529 );
530 END IF;
531
532 /*** First retrieve last run date of the conccurent program ***/
533 OPEN c_LastRundate;
534 FETCH c_LastRundate INTO r_LastRundate;
535 CLOSE c_LastRundate;
536
537 l_current_run_date := SYSDATE;
538
539 /*** Push updated system item records to resources ***/
540 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
541 jtm_message_log_pkg.Log_Msg
542 ( 0
543 , g_table_name
544 , 'Pushing updated records'
545 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
546 );
547 END IF;
548 UPDATE_ACC_REC_MARKDIRTY( p_last_run_date => r_LastRundate.last_run_date );
549 COMMIT;
550
551 -- INSERT new records if found, for the mobile users' subinventories
552 /*** Get the mobile laptop resources and loop over all of them ***/
553 INSERT_ACC_REC_MARKDIRTY( p_last_run_date => r_LastRundate.last_run_date );
554
555 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
556 jtm_message_log_pkg.Log_Msg
557 ( 0
558 , g_table_name
559 , 'Updating LAST_RUN_DATE from '||r_LastRundate.LAST_RUN_DATE||' to '||l_current_run_date
560 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
561 );
562 END IF;
563
564 /*Update the last run date*/
565 UPDATE JTM_CON_REQUEST_DATA
566 SET LAST_RUN_DATE = l_current_run_date
567 WHERE package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'
568 AND procedure_name = 'CON_REQUEST_ITEM_LOCATIONS';
569
570 COMMIT;
571
572 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
573 jtm_message_log_pkg.Log_Msg
574 ( 0
575 , g_table_name
576 , 'Leaving CON_REQUEST_ITEM_LOCATIONS'
577 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
578 );
579 END IF;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
584 jtm_message_log_pkg.Log_Msg
585 ( 0
586 , g_table_name
587 , 'CON_REQUEST_ITEM_LOCATIONS'||fnd_global.local_chr(10)||
588 'Error: '||sqlerrm
589 , JTM_HOOK_UTIL_PKG.g_debug_level_error);
590 END IF;
591 ROLLBACK;
592 END CON_REQUEST_ITEM_LOCATIONS;
593
594 END CSL_MTL_ITEM_LOCATIONS_ACC_PKG;