[Home] [Help]
1: PACKAGE BODY CSL_MTL_ONHAND_QTY_ACC_PKG AS
2: /* $Header: cslo1acb.pls 120.0 2005/08/30 01:38:06 utekumal noship $ */
3:
4:
5: /*** Globals for notifications ***/
43: ( v_object_id => null
44: , v_object_name => g_table_name
45: , v_message => 'Entering REFRESH_ONHAND_QTY'
46: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
47: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
48: END IF;
49:
50: l_current_run_date := SYSDATE;
51:
50: l_current_run_date := SYSDATE;
51:
52: UPDATE JTM_CON_REQUEST_DATA SET LAST_RUN_DATE = l_current_run_date
53: WHERE PRODUCT_CODE = 'CSL'
54: AND PACKAGE_NAME = 'CSL_MTL_ONHAND_QTY_ACC_PKG'
55: AND PROCEDURE_NAME = 'REFRESH_ONHAND_QTY';
56:
57: /*** First UPDATE existing MV records that changed ***/
58: /*** Fetch all records in a Bulk recordset ***/
99: , v_object_name => g_table_name
100: , v_message => 'Number of updated records for REFRESH_ONHAND_QTY : '
101: || sub_code.COUNT
102: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
103: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
104: END IF;
105: FORALL i IN sub_code.FIRST..sub_code.LAST
106: UPDATE CSL_MTL_ONHAND_QTY_MV SET LAST_UPDATE_DATE = l_current_run_date, TOT_TXN_QUANTITY = qty(i)
107: WHERE subinventory_code = sub_code(i)
185: ( v_object_id => 1
186: , v_object_name => g_table_name
187: , v_message => 'Leaving REFRESH_ONHAND_QTY'
188: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
189: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
190: END IF;
191: EXCEPTION WHEN OTHERS THEN
192: /*** hook failed -> log error ***/
193: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
195: ( v_object_id => 1
196: , v_object_name => g_table_name
197: , v_message => 'Caught exception in REFRESH_ONHAND_QTY hook:' || fnd_global.local_chr(10) || sqlerrm
198: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
199: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
200: END IF;
201: ROLLBACK;
202: fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','REFRESH_ONHAND_QTY',sqlerrm);
203:
198: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
199: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
200: END IF;
201: ROLLBACK;
202: fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','REFRESH_ONHAND_QTY',sqlerrm);
203:
204: END REFRESH_ONHAND_QTY;
205:
206: PROCEDURE PROCESS_ACC( l_current_run_date IN DATE)
252: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
253: , v_object_name => g_table_name
254: , v_message => 'Entering PROCESS_ACC'
255: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
256: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
257: END IF;
258:
259: /*** Loop through resources to which a subinventory is assigned ***/
260: FOR r_secinv_resources IN c_secinv_resources LOOP
265: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
266: , v_object_name => g_table_name
267: , v_message => 'Checking updates and inserts for resource_id = ' || r_secinv_resources.resource_id
268: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
269: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
270: END IF;
271:
272: /*** Push updated record to client ***/
273: SELECT ohqacc.access_id
291: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
292: , v_object_name => g_table_name
293: , v_message => 'Pushing ' || acc_id.COUNT || ' updated record(s)'
294: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
295: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
296: END IF;
297:
298: FOR i IN acc_id.FIRST..acc_id.LAST LOOP
299: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
301: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
302: , v_object_name => g_table_name
303: , v_message => 'Pushing record with access_id = ' || acc_id(i)
304: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
305: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
306: END IF;
307:
308: l_dummy := asg_download.markdirty(
309: P_PUB_ITEM => g_publication_item_name(1)
365: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
366: , v_object_name => g_table_name
367: , v_message => 'Pushing ' || acc_id.COUNT || ' inserted record(s)'
368: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
369: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
370: END IF;
371:
372: FORALL i IN acc_id.FIRST..acc_id.LAST
373: INSERT INTO CSL_MTL_ONHAND_QTY_ACC (RESOURCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
382: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
383: , v_object_name => g_table_name
384: , v_message => 'Pushing record with access_id = ' || acc_id(i)
385: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
386: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
387: END IF;
388:
389: l_dummy := asg_download.markdirty(
390: P_PUB_ITEM => g_publication_item_name(1)
400: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
401: , v_object_name => g_table_name
402: , v_message => 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD'
403: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
404: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
405: END IF;
406: CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD(inv_id(i),org_id(i),r_secinv_resources.resource_id);
407: END IF;
408:
444: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
445: , v_object_name => g_table_name
446: , v_message => 'Pushing ' || acc_id.COUNT || ' deleted record(s)'
447: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
448: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
449: END IF;
450:
451: FOR i IN acc_id.FIRST..acc_id.LAST LOOP
452:
455: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
456: , v_object_name => g_table_name
457: , v_message => 'Pushing record with access_id = ' || acc_id(i)
458: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
459: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
460: END IF;
461:
462: l_dummy := asg_download.markdirty(
463: P_PUB_ITEM => g_publication_item_name(1)
473: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
474: , v_object_name => g_table_name
475: , v_message => 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD'
476: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
477: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
478: END IF;
479:
480: CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD( inv_id(i)
481: , org_id(i)
500: ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
501: , v_object_name => g_table_name
502: , v_message => 'Leaving PROCESS_ACC'
503: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
504: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
505: END IF;
506: EXCEPTION WHEN OTHERS THEN
507: /*** hook failed -> log error ***/
508: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
510: ( v_object_id => 1
511: , v_object_name => g_table_name
512: , v_message => 'Caught exception in PROCESS_ACC hook:' || fnd_global.local_chr(10) || sqlerrm
513: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
514: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
515: END IF;
516: ROLLBACK;
517: fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','PROCESS_ACC',sqlerrm);
518: END PROCESS_ACC;
513: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
514: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
515: END IF;
516: ROLLBACK;
517: fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','PROCESS_ACC',sqlerrm);
518: END PROCESS_ACC;
519:
520: /*Delete all records for non-existing user ( e.g user was deleted )*/
521: PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
531: ( p_resource_id
532: , g_table_name
533: , 'Entering DELETE_ALL_ACC_RECORDS'
534: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
535: , v_module => 'csl_mtl_onhand_qty_acc_pkg'
536: );
537: END IF;
538:
539: /* DELETE CSL_MTL_ONHAND_QTY_ACC
551: ( p_resource_id
552: , g_table_name
553: , 'Pulling '||l_tab_access_id.COUNT||' records from Oracle Lite for resource '||p_resource_id
554: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
555: , v_module => 'csl_mtl_onhand_qty_acc_pkg'
556: );
557: END IF;
558:
559: FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
579: ( p_resource_id
580: , g_table_name
581: , 'Leaving DELETE_ALL_ACC_RECORDS'
582: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
583: , v_module => 'csl_mtl_onhand_qty_acc_pkg'
584: );
585: END IF;
586: EXCEPTION WHEN OTHERS THEN
587: /*** hook failed -> log error ***/
592: ( v_object_id => 1
593: , v_object_name => g_table_name
594: , v_message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
595: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
596: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
597: END IF;
598: fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','PROCESS_ACC',sqlerrm);
599: END DELETE_ALL_ACC_RECORDS;
600:
594: , v_message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
595: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
596: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
597: END IF;
598: fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','PROCESS_ACC',sqlerrm);
599: END DELETE_ALL_ACC_RECORDS;
600:
601:
602:
599: END DELETE_ALL_ACC_RECORDS;
600:
601:
602:
603: END CSL_MTL_ONHAND_QTY_ACC_PKG;