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