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';
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';
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'
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*/
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)'
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(
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'
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
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: /*
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'
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 */
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)'
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
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'
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
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: /*
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
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'
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
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 '
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
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
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'
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
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
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'
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
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 '
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
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
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'
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: /*
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)'
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
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'
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
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: /*
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
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'
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 ***/
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'
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;
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
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*/
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'
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
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)||
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: