Onhand Qty(Tree) Diagnostics Scripts R12
来源：互联网 发布：linux挂载ntfs硬盘 编辑：程序博客网 时间：2024/06/13 12:00
qtytree_r12.sql
http://blog.csdn.net/pan_tian/article/details/8508122
SET SERVEROUTPUT ON SIZE 1000000 -- DESCRIPTION: -- ============ -- Script qtytree12.sql is intended to collect quantity tree debug log and data dump from key tables related to quantity eg.MOQD,MR,MMTT etc. -- qtytree12.sql is only used For EBS R12 or higher version. -- -- <<OUTPUT FILE>> -- path:utl_file_dir -- file name:qty_tree_diagnostics_itemname_orgcode_timestamp.log -- <<History>> -- Sep 16, 2010. pan.tian create. prompt About Input Parameters prompt How to get input parameter ITEM_ID prompt eg.SELECT DISTINCT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = 'AS54888'; prompt prompt How to get input parameter ORGANIZATION_ID? prompt eg.SELECT DISTINCT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = 'M1'; prompt prompt How to get input parameter USER_ID? prompt eg.SELECT USER_ID FROM FND_USER WHERE USER_NAME = 'MFG'; prompt prompt How to get input parameter RESP_ID? prompt eg.SELECT RESPONSIBILITY_ID FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME = 'Inventory'; prompt prompt How to get input parameter APPL_ID? prompt eg.SELECT APPLICATION_ID FROM FND_APPLICATION WHERE APPLICATION_SHORT_NAME = 'INV'; prompt prompt How to get input parameter utl_file_dir? prompt Ensure the utl_file_dir file is set with a directory existing in utl_file_dir that can be found with this sql: prompt eg.SELECT VALUE FROM V$PARAMETER WHERE NAME = 'utl_file_dir'; DECLARE v_item_id NUMBER := &ITEM_ID; v_org_id NUMBER := &ORGANIZATION_ID; v_user_id NUMBER := &USER_ID; v_resp_id NUMBER := &RESP_ID; v_appl_id NUMBER := &APPL_ID; v_path VARCHAR2 (90) := '&utl_file_dir'; /* v_item_id NUMBER := 149; --AS54888 v_org_id NUMBER := 207; --M1 v_user_id NUMBER := 1068; --hardcoded at vision v_resp_id NUMBER := 20634; --hardcoded to inventory v_appl_id NUMBER := 401; --hardcoded to inventory v_path VARCHAR2 (80) := '/usr/tmp'; */ v_filename VARCHAR2 (80) := 'qty_tree_diagnostics'; v_filetemp VARCHAR2 (80) := 'qty_tree_diagnostics_temp'; l_return_status VARCHAR2 (1); l_qty_oh NUMBER; l_qty_res_oh NUMBER; l_qty_res NUMBER; l_qty_sug NUMBER; l_qty_att NUMBER; l_qty_atr NUMBER; l_sqty_oh NUMBER; l_sqty_res_oh NUMBER; l_sqty_res NUMBER; l_sqty_sug NUMBER; l_sqty_att NUMBER; l_sqty_atr NUMBER; l_msg_count NUMBER; l_msg_data VARCHAR2 (1000); l_inventory_item_id NUMBER; fHandler UTL_FILE.FILE_TYPE; l_item VARCHAR2 (40); l_org VARCHAR2 (40); status_id NUMBER; l_tree_id NUMBER; ex BOOLEAN; flen NUMBER; bsize NUMBER; v_sysdate VARCHAR2 (15); l_dbversion VARCHAR2 (15); l_patch_level VARCHAR2 (15); l_utl_path VARCHAR2 (80); CURSOR ONHAND_CURSOR IS SELECT inventory_item_id, organization_id, subinventory_code, locator_id, revision, lot_number, status_id, SUM (primary_transaction_quantity) onhand_qty FROM apps.mtl_onhand_quantities_detail WHERE inventory_item_id = v_item_id AND organization_id = v_org_id GROUP BY organization_id, inventory_item_id, subinventory_code, locator_id, revision, lot_number, status_id; CURSOR MR_CURSOR IS SELECT RESERVATION_ID, inventory_item_id, organization_id, subinventory_code, locator_id, revision, lot_number, PRIMARY_RESERVATION_QUANTITY FROM apps.mtl_reservations WHERE inventory_item_id = v_item_id AND organization_id = v_org_id ORDER BY inventory_item_id, organization_id, subinventory_code, locator_id, revision, lot_number; CURSOR MMTT_CURSOR IS SELECT TRANSACTION_TEMP_ID, inventory_item_id, organization_id, subinventory_code, locator_id, revision, lot_number, PRIMARY_QUANTITY FROM apps.mtl_material_transactions_temp WHERE inventory_item_id = v_item_id AND organization_id = v_org_id ORDER BY inventory_item_id, organization_id, subinventory_code, locator_id, revision, lot_number; CURSOR MLN_CURSOR IS SELECT mln.INVENTORY_ITEM_ID, mln.ORGANIZATION_ID, mln.LOT_NUMBER, mln.DISABLE_FLAG, mln.EXPIRATION_DATE, mln.RESERVABLE_TYPE FROM mtl_lot_numbers mln, mtl_onhand_quantities_detail moqd, mtl_system_items msi WHERE moqd.inventory_item_id = mln.inventory_item_id AND moqd.organization_id = mln.organization_id AND mln.lot_number = moqd.lot_number AND mln.inventory_item_id = msi.inventory_item_id AND mln.organization_id = msi.organization_id AND msi.lot_control_code = 2 AND moqd.inventory_item_id = v_item_id AND moqd.organization_id = v_org_id ORDER BY mln.INVENTORY_ITEM_ID, mln.ORGANIZATION_ID, mln.LOT_NUMBER; CURSOR MP_CURSOR IS SELECT ORGANIZATION_ID, ORGANIZATION_CODE, MASTER_ORGANIZATION_ID, NEGATIVE_INV_RECEIPT_CODE, STOCK_LOCATOR_CONTROL_CODE, SERIAL_NUMBER_TYPE, SERIAL_NUMBER_GENERATION, LOT_NUMBER_UNIQUENESS, LOT_NUMBER_GENERATION, WMS_ENABLED_FLAG, SOURCE_TYPE, PRIMARY_COST_METHOD FROM mtl_parameters WHERE organization_id = v_org_id; CURSOR MSI_CURSOR IS SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, SEGMENT1, ENABLED_FLAG, LOT_CONTROL_CODE, SHELF_LIFE_CODE, SERIAL_NUMBER_CONTROL_CODE, LOCATION_CONTROL_CODE, RESTRICT_SUBINVENTORIES_CODE, RESTRICT_LOCATORS_CODE, RESERVABLE_TYPE, PURCHASING_ITEM_FLAG, SHIPPABLE_ITEM_FLAG, CUSTOMER_ORDER_FLAG, INTERNAL_ORDER_FLAG, INVENTORY_ITEM_FLAG, INVENTORY_ASSET_FLAG, PURCHASING_ENABLED_FLAG, STOCK_ENABLED_FLAG, BOM_ENABLED_FLAG, REVISION_QTY_CONTROL_CODE, LOT_STATUS_ENABLED, SERIAL_STATUS_ENABLED FROM MTL_SYSTEM_ITEMS_B WHERE organization_id = v_org_id AND inventory_item_id = v_item_id; CURSOR SUBINV_CURSOR IS SELECT DISTINCT MSINV.SECONDARY_INVENTORY_NAME, NVL (MSINV.RESERVABLE_TYPE, 1) RESERVABLE_TYPE FROM MTL_SECONDARY_INVENTORIES MSINV, MTL_ONHAND_QUANTITIES_DETAIL MOQD WHERE MOQD.ORGANIZATION_ID = V_ORG_ID AND MOQD.INVENTORY_ITEM_ID = V_ITEM_ID AND MOQD.ORGANIZATION_ID = MSINV.ORGANIZATION_ID AND MOQD.SUBINVENTORY_CODE = MSINV.SECONDARY_INVENTORY_NAME; CURSOR LOC_CURSOR IS SELECT DISTINCT MIL.INVENTORY_LOCATION_ID, MIL.SEGMENT1, MIL.SEGMENT2, MIL.SEGMENT3, MIL.SEGMENT19, MIL.SEGMENT20, NVL (MIL.RESERVABLE_TYPE, 1) RESERVABLE_TYPE FROM MTL_ITEM_LOCATIONS MIL, MTL_ONHAND_QUANTITIES_DETAIL MOQD WHERE MOQD.ORGANIZATION_ID = V_ORG_ID AND MOQD.INVENTORY_ITEM_ID = V_ITEM_ID AND MOQD.ORGANIZATION_ID = MIL.ORGANIZATION_ID AND MOQD.LOCATOR_ID IS NOT NULL AND MOQD.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID; CURSOR FVERSION_CURSOR IS SELECT name, text FROM dba_source WHERE name IN ('INV_QUANTITY_TREE_PVT', 'INV_QUANTITY_TREE_PUB', 'INV_QUANTITY_TREE_GRP', 'INV_QUANTITY_TREE_UE') AND text LIKE '%$Header%' AND TYPE = 'PACKAGE BODY'; BEGIN DBMS_OUTPUT.put_line ('Start...'); IF (v_user_id IS NULL OR v_resp_id IS NULL OR v_appl_id IS NULL) THEN DBMS_OUTPUT.put_line('Input parameters v_user_id/v_resp_id/v_appl_id is NULL,please input a valid value.'); RETURN; END IF; IF (v_item_id IS NULL) THEN DBMS_OUTPUT.put_line ( 'v_item_id is NULL,please input a valid item_id.' ); RETURN; END IF; IF (v_org_id IS NULL) THEN DBMS_OUTPUT.put_line ( 'v_org_id is NULL,please input a valid organization_id.' ); RETURN; END IF; IF (v_path IS NULL) THEN DBMS_OUTPUT.put_line ( 'v_path is NULL,please input a valid utl_file_dir path.' ); RETURN; END IF; FND_GLOBAL.apps_initialize (v_user_id, v_resp_id, v_appl_id); apps.inv_quantity_tree_grp.clear_quantity_cache; --turn off debug fnd_profile.put ('INV_DEBUG_TRACE', '0'); BEGIN SELECT segment1 INTO l_item FROM mtl_system_items_b WHERE inventory_item_id = v_item_id AND ORGANIZATION_ID = v_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('invalid v_item_id/v_org_id.'); RETURN; END; BEGIN SELECT ORGANIZATION_CODE INTO l_org FROM MTL_PARAMETERS WHERE ORGANIZATION_ID = v_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('invalid v_org_id.'); RETURN; END; SELECT TO_CHAR (SYSDATE, 'HH24MISS') INTO v_sysdate FROM DUAL; DBMS_OUTPUT.put_line ('create_tree...'); inv_quantity_tree_grp.create_tree ( p_api_version_number => 1.0, p_init_msg_lst => apps.fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => v_org_id, p_inventory_item_id => v_item_id, p_tree_mode => apps.INV_Quantity_Tree_PUB.g_reservation_mode, p_is_revision_control => FALSE, p_is_lot_control => FALSE, p_is_serial_control => FALSE, p_grade_code => NULL, p_demand_source_type_id => -9999, p_demand_source_header_id => -9999, p_demand_source_line_id => -9999, p_demand_source_name => NULL, p_lot_expiration_date => NULL, p_onhand_source => 3, x_tree_id => l_tree_id ); IF l_return_status = fnd_api.g_ret_sts_error THEN RAISE fnd_api.g_exc_error; END IF; IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN RAISE fnd_api.g_exc_unexpected_error; END IF; DBMS_OUTPUT.put_line ('query_tree...'); inv_quantity_tree_grp.query_tree ( p_api_version_number => 1.0, p_init_msg_lst => apps.fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_tree_id => l_tree_id, p_revision => NULL, p_lot_number => NULL, p_subinventory_code => NULL, p_locator_id => NULL, x_qoh => l_qty_oh, x_rqoh => l_qty_res_oh, x_qr => l_qty_res, x_qs => l_qty_sug, x_att => l_qty_att, x_atr => l_qty_atr, x_sqoh => l_sqty_oh -- invConv change , x_srqoh => l_sqty_res_oh -- invConv change , x_sqr => l_sqty_res -- invConv change , x_sqs => l_sqty_sug -- invConv change , x_satt => l_sqty_att -- invConv change , x_satr => l_sqty_atr -- invConv change , p_transfer_subinventory_code => NULL, p_cost_group_id => NULL, p_lpn_id => NULL, p_transfer_locator_id => NULL ); v_filename := v_filename || '_' || l_item || '_' || l_org || '.log'; v_filetemp := v_filetemp || '_' || l_item || '_' || l_org || '_' || v_sysdate || '.log'; --delete the log file if any. UTL_FILE.fgetattr (v_path, v_filename, ex, flen, bsize); IF ex THEN UTL_FILE.fremove (v_path, v_filename); END IF; --turn on debug fnd_profile.put ('INV_DEBUG_TRACE', '1'); fnd_profile.put ('INV_DEBUG_LEVEL', '15'); fnd_profile.put ('INV_DEBUG_FILE', v_path || '/' || v_filetemp); DBMS_OUTPUT.put_line ('print_tree...'); apps.INV_QUANTITY_TREE_PVT.print_tree (l_tree_id); UTL_FILE.fcopy (v_path, v_filetemp, v_path, v_filename); --delete the temp log file if any. UTL_FILE.fgetattr (v_path, v_filetemp, ex, flen, bsize); IF ex THEN UTL_FILE.fremove (v_path, v_filetemp); ELSE DBMS_OUTPUT.put_line ('Temp Log File Does Not Exist'); END IF; DBMS_OUTPUT.put_line ('QUANTITY DIAGNOSTICS OUTPUT...'); fHandler := UTL_FILE.FOPEN (v_path, v_filename, 'A'); UTL_FILE.put_line (fHandler, ''); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('<<LEGENDS>>', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Rev : Revision', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Lot : Lot Number', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Sub : Subinventory', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Loc : Locator', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Lpn : LPN Number', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('qoh : Quantity On-hand', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('rqoh : Reservable Quantity On-hand', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('qr : Quantity Reserved', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('qs : Quantity Suggested (Allocated)', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('att : Available to Transact', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('atr : Available to Reserve', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Rsv : Is Reservable?', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('------------------', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('qs_adj : For Internal Use', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('qs_adj1: For Internal Use', 75)); UTL_FILE.PUT_LINE (FHANDLER, RPAD ('Marked : For Internal Use', 75)); UTL_FILE.put_line (fHandler, ''); UTL_FILE.PUT_LINE (FHANDLER, '<<Quantity Related Information>>'); DBMS_OUTPUT.put_line ('========RUN PARAMETERS========='); UTL_FILE.put_line (fHandler, '========RUN PARAMETERS========='); UTL_FILE.put_line (fHandler, 'item_id:' || v_item_id); UTL_FILE.put_line (fHandler, 'org_id:' || v_org_id); UTL_FILE.put_line (fHandler, 'user_id:' || v_user_id); UTL_FILE.put_line (fHandler, 'resp_id:' || v_resp_id); UTL_FILE.put_line (fHandler, 'appl_id:' || v_appl_id); UTL_FILE.put_line (fHandler, ''); SELECT DISTINCT version INTO l_dbversion FROM v$instance; SELECT DISTINCT patch_level INTO l_patch_level FROM fnd_product_installations WHERE application_id = 401; DBMS_OUTPUT.put_line ('========Instance Info========='); UTL_FILE.put_line (fHandler, '========Instance Info========='); UTL_FILE.put_line (fHandler, 'Patch Level:' || TO_CHAR (l_patch_level)); UTL_FILE.put_line (fHandler, 'DB Version:' || TO_CHAR (l_dbversion)); UTL_FILE.put_line (fHandler, ''); UTL_FILE.put_line (fHandler, '========Total ATT/ATR etc..========='); UTL_FILE.put_line (fHandler, 'QUANTITY ON HAND:' || TO_CHAR (l_qty_oh)); UTL_FILE.put_line (fHandler, 'QUANTITY RES OH:' || TO_CHAR (l_qty_res_oh)); UTL_FILE.put_line (fHandler, 'QUANTITY RES :' || TO_CHAR (l_qty_res)); UTL_FILE.put_line (fHandler, 'QUANTITY SUG :' || TO_CHAR (l_qty_sug)); UTL_FILE.put_line (fHandler, 'Quantity ATT :' || TO_CHAR (l_qty_att)); UTL_FILE.put_line (fHandler, 'Quantity ATR :' || TO_CHAR (l_qty_atr)); UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========MTL_ONHAND_QUANTITIES_DETAIL========='); UTL_FILE.put_line (fHandler, '====MTL_ONHAND_QUANTITIES_DETAIL===='); UTL_FILE.put_line ( fHandler, RPAD ('ITEM_ID', 15) || RPAD ('ORG_ID', 10) || RPAD ('SUB_CODE', 15) || RPAD ('LOC_ID', 15) || RPAD ('REVISION', 11) || RPAD ('LOT', 15) || RPAD ('STATUS_ID', 12) || RPAD ('SUM(PRIMARY_QTY)', 20) ); FOR ONHAND_REC IN ONHAND_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (ONHAND_REC.inventory_item_id, 15) || RPAD (ONHAND_REC.organization_id, 10) || RPAD (ONHAND_REC.subinventory_code, 15) || RPAD (NVL (TO_CHAR (ONHAND_REC.locator_id), '...'), 15) || RPAD (NVL (ONHAND_REC.revision, '...'), 11) || RPAD (NVL (ONHAND_REC.lot_number, '...'), 15) || RPAD (NVL (TO_CHAR (ONHAND_REC.status_id), '...'), 12) || RPAD (ONHAND_REC.onhand_qty, 15) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========MTL_RESERVATIONS========='); UTL_FILE.put_line (fHandler, '====MTL_RESERVATIONS===='); UTL_FILE.put_line ( fHandler, RPAD ('RESERVATION_ID', 17) || RPAD ('ITEM_ID', 15) || RPAD ('ORG_ID', 10) || RPAD ('SUB_CODE', 15) || RPAD ('LOC_ID', 17) || RPAD ('REVISION', 11) || RPAD ('LOT', 15) || RPAD ('PRIMARY_RESERVATION_QTY', 25) ); FOR MR_REC IN MR_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (MR_REC.RESERVATION_ID, 17) || RPAD (MR_REC.inventory_item_id, 15) || RPAD (MR_REC.organization_id, 10) || RPAD (NVL (MR_REC.subinventory_code, '...'), 15) || RPAD (NVL (TO_CHAR (MR_REC.locator_id), '...'), 17) || RPAD (NVL (MR_REC.revision, '...'), 11) || RPAD (NVL (MR_REC.lot_number, '...'), 15) || RPAD (MR_REC.PRIMARY_RESERVATION_QUANTITY, 25) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========MTL_MATERIAL_TRANSACTIONS_TEMP========='); UTL_FILE.put_line (fHandler, '====MTL_MATERIAL_TRANSACTIONS_TEMP===='); UTL_FILE.put_line ( fHandler, RPAD ('TEMP_ID', 17) || RPAD ('ITEM_ID', 15) || RPAD ('ORG_ID', 10) || RPAD ('SUB_CODE', 15) || RPAD ('LOC_ID', 17) || RPAD ('REVISION', 11) || RPAD ('LOT', 15) || RPAD ('PRIMARY_QTY', 25) ); FOR MMTT_REC IN MMTT_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (MMTT_REC.TRANSACTION_TEMP_ID, 17) || RPAD (MMTT_REC.inventory_item_id, 15) || RPAD (MMTT_REC.organization_id, 10) || RPAD (NVL (MMTT_REC.subinventory_code, '...'), 15) || RPAD (NVL (TO_CHAR (MMTT_REC.locator_id), '...'), 17) || RPAD (NVL (MMTT_REC.revision, '...'), 11) || RPAD (NVL (MMTT_REC.lot_number, '...'), 15) || RPAD (MMTT_REC.PRIMARY_QUANTITY, 25) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========MTL_LOT_NUMBERS========='); UTL_FILE.put_line (fHandler, '====MTL_LOT_NUMBERS===='); UTL_FILE.put_line ( fHandler, RPAD ('ITEM_ID', 17) || RPAD ('ORG_ID', 15) || RPAD ('LOT', 15) || RPAD ('DISABLE_FLAG', 15) || RPAD ('EXPIRATION_DATE', 25) || RPAD ('RESERVABLE_TYPE', 25) ); FOR MLN_REC IN MLN_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (MLN_REC.INVENTORY_ITEM_ID, 17) || RPAD (MLN_REC.organization_id, 15) || RPAD (NVL (MLN_REC.LOT_NUMBER, '...'), 15) || RPAD (NVL (TO_CHAR (MLN_REC.DISABLE_FLAG), '...'), 15) || RPAD (NVL (TO_CHAR (MLN_REC.EXPIRATION_DATE), '...'), 25) || RPAD (NVL (TO_CHAR (MLN_REC.RESERVABLE_TYPE), '...'), 25) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========MTL_PARAMETERS========='); UTL_FILE.put_line (fHandler, '====MTL_PARAMETERS===='); UTL_FILE.put_line ( fHandler, RPAD ('ORG_ID', 17) || RPAD ('ORG_CODE', 15) || RPAD ('MASTER_ORG_ID', 25) || RPAD ('NEGATIVE_INV_RECEIPT_CODE', 30) || RPAD ('STOCK_LOC_CONTROL_CODE', 30) || RPAD ('SERIAL_NUMBER_TYPE', 25) || RPAD ('SERIAL_NUMBER_GENERATION', 25) || RPAD ('LOT_NUMBER_UNIQUENESS', 25) || RPAD ('LOT_NUMBER_GENERATION', 25) || RPAD ('WMS_ENABLED_FLAG', 25) || RPAD ('SOURCE_TYPE', 25) || RPAD ('PRIMARY_COST_METHOD', 25) ); FOR MP_REC IN MP_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (MP_REC.ORGANIZATION_ID, 17) || RPAD (MP_REC.ORGANIZATION_CODE, 15) || RPAD (NVL (TO_CHAR (MP_REC.MASTER_ORGANIZATION_ID), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.NEGATIVE_INV_RECEIPT_CODE), '...'), 30) || RPAD (NVL (TO_CHAR (MP_REC.STOCK_LOCATOR_CONTROL_CODE), '...'), 30) || RPAD (NVL (TO_CHAR (MP_REC.SERIAL_NUMBER_TYPE), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.SERIAL_NUMBER_GENERATION), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.LOT_NUMBER_UNIQUENESS), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.LOT_NUMBER_GENERATION), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.WMS_ENABLED_FLAG), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.SOURCE_TYPE), '...'), 25) || RPAD (NVL (TO_CHAR (MP_REC.PRIMARY_COST_METHOD), '...'), 25) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========MTL_SYSTEM_ITEMS_B========='); UTL_FILE.put_line (fHandler, '====MTL_SYSTEM_ITEMS_B===='); UTL_FILE.put_line ( fHandler, RPAD ('ITEM_ID', 17) || RPAD ('ORG_ID', 15) || RPAD ('SEGMENT1', 25) || RPAD ('ENABLED_FLAG', 25) || RPAD ('LOT_CONTROL_CODE', 25) || RPAD ('SERIAL_NUMBER_CONTROL_CODE', 30) || RPAD ('LOCATION_CONTROL_CODE', 25) || RPAD ('RESTRICT_SUBINVENTORIES_CODE', 30) || RPAD ('RESTRICT_LOCATORS_CODE', 25) || RPAD ('REVISION_QTY_CONTROL_CODE', 30) || RPAD ('RESERVABLE_TYPE', 25) || RPAD ('INVENTORY_ITEM_FLAG', 25) || RPAD ('STOCK_ENABLED_FLAG', 25) ); FOR MSI_REC IN MSI_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (MSI_REC.INVENTORY_ITEM_ID, 17) || RPAD (MSI_REC.ORGANIZATION_ID, 15) || RPAD (NVL (TO_CHAR (MSI_REC.SEGMENT1), '...'), 25) || RPAD (NVL (TO_CHAR (MSI_REC.ENABLED_FLAG), '...'), 25) || RPAD (NVL (TO_CHAR (MSI_REC.LOT_CONTROL_CODE), '...'), 25) || RPAD (NVL (TO_CHAR (MSI_REC.SERIAL_NUMBER_CONTROL_CODE), '...'), 30) || RPAD (NVL (TO_CHAR (MSI_REC.LOCATION_CONTROL_CODE), '...'), 25) || RPAD ( NVL (TO_CHAR (MSI_REC.RESTRICT_SUBINVENTORIES_CODE), '...'), 30 ) || RPAD (NVL (TO_CHAR (MSI_REC.RESTRICT_LOCATORS_CODE), '...'), 25) || RPAD (NVL (TO_CHAR (MSI_REC.REVISION_QTY_CONTROL_CODE), '...'), 30) || RPAD (NVL (TO_CHAR (MSI_REC.RESERVABLE_TYPE), '...'), 25) || RPAD (NVL (TO_CHAR (MSI_REC.INVENTORY_ITEM_FLAG), '...'), 25) || RPAD (NVL (TO_CHAR (MSI_REC.STOCK_ENABLED_FLAG), '...'), 25) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========SUBINV RESERVABLE========='); UTL_FILE.put_line (fHandler, '====SUBINV RESERVABLE===='); UTL_FILE.put_line ( fHandler, RPAD ('SECONDARY_INVENTORY_NAME', 30) || RPAD ('RESERVABLE_TYPE', 25) ); FOR SUB_REC IN SUBINV_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (NVL (TO_CHAR (SUB_REC.SECONDARY_INVENTORY_NAME), '...'), 30) || RPAD (NVL (TO_CHAR (SUB_REC.RESERVABLE_TYPE), '...'), 25) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========LOCATOR RESERVABLE========='); UTL_FILE.put_line (fHandler, '====LOCATOR RESERVABLE===='); UTL_FILE.put_line ( fHandler, RPAD ('INVENTORY_LOCATION_ID', 30) || RPAD ('SEGMENT1', 10) || RPAD ('SEGMENT2', 10) || RPAD ('SEGMENT3', 10) || RPAD ('SEGMENT19', 10) || RPAD ('SEGMENT20', 10) || RPAD ('RESERVABLE_TYPE', 10) ); FOR LOC_REC IN LOC_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (NVL (TO_CHAR (LOC_REC.INVENTORY_LOCATION_ID), '...'), 30) || RPAD (NVL (TO_CHAR (LOC_REC.SEGMENT1), '...'), 10) || RPAD (NVL (TO_CHAR (LOC_REC.SEGMENT2), '...'), 10) || RPAD (NVL (TO_CHAR (LOC_REC.SEGMENT3), '...'), 10) || RPAD (NVL (TO_CHAR (LOC_REC.SEGMENT19), '...'), 10) || RPAD (NVL (TO_CHAR (LOC_REC.SEGMENT20), '...'), 10) || RPAD (NVL (TO_CHAR (LOC_REC.RESERVABLE_TYPE), '...'), 10) ); END LOOP; UTL_FILE.put_line (fHandler, ''); DBMS_OUTPUT.put_line ('========File Version========='); UTL_FILE.put_line (fHandler, '====File Version===='); UTL_FILE.put_line (fHandler, RPAD ('NAME', 30) || RPAD ('TEXT', 60)); FOR FVERSION_REC IN FVERSION_CURSOR LOOP UTL_FILE.put_line ( fHandler, RPAD (NVL (TO_CHAR (FVERSION_REC.NAME), '...'), 30) || RPAD (NVL (TO_CHAR (FVERSION_REC.TEXT), '...'), 60) ); END LOOP; UTL_FILE.put_line (fHandler, ''); UTL_FILE.put_line (fHandler, '====END===='); UTL_FILE.fflush (fHandler); --Close UTL_FILE.FCLOSE (fHandler); DBMS_OUTPUT.put_line ('Output File:' || v_path || '/' || v_filename); END;
=============================================================
Sample Output
******** New Session:10194566****20-SEP-10:04.46.12 ********** [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: print tree: number=1 id=956178 for item=149 org=207 [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Org_id:207 Item_id:149 [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: _____start of tree 956178 [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Node Rev Lot Sub Loc Lpn qoh rqoh qr qs att atr qs_adj1 Rsv Marked [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Item ... ... ... ... ... 1818.961648.96437 4 1377.961207.960 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... Engineer ... ... 0 0 0 0 0 0 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... FS_Truck6 ... ... 0 0 0 0 0 0 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... bb_stores ... ... 3 3 0 0 3 3 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... Staging1 ... ... 43 43 23 0 20 20 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... FGI ... ... 1379.961379.964 4 1371.961371.960 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... FldSvc ... ... 100 0 0 0 100 0 0 N FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... CustReturn... ... 65 0 0 0 65 0 0 N FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... ptfgi ... ... 113 108 0 0 113 108 0 N FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Loc ... ... ptfgi 3679 ... 108 108 0 0 108 108 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Loc ... ... ptfgi 3678 ... 5 0 0 0 5 0 0 N FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... DropShip ... ... 15 15 0 0 15 15 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: Sub ... ... Stores ... ... 100 100 0 0 100 100 0 Y FALSE [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: ---------------------------------------------------------------------------------------------------------- [20-SEP-10 04:46:12] INV_QUANTITY_TREE_PVT: _____end of tree 956178 <<LEGENDS>> Rev : Revision Lot : Lot Number Sub : Subinventory Loc : Locator Lpn : LPN Number qoh : Quantity On-hand rqoh : Reservable Quantity On-hand qr : Quantity Reserved qs : Quantity Suggested (Allocated) att : Available to Transact atr : Available to Reserve Rsv : Is Reservable? ------------------ qs_adj : For Internal Use qs_adj1: For Internal Use Marked : For Internal Use <<Quantity Related Information>> ========RUN PARAMETERS========= item_id:149 org_id:207 user_id:1068 resp_id:20634 appl_id:401 ========Instance Info========= Patch Level:R12.INV.A DB Version:10.2.0.2.0 ========Total ATT/ATR etc..========= QUANTITY ON HAND:1818.96295 QUANTITY RES OH:1648.96295 QUANTITY RES :437 QUANTITY SUG :4 Quantity ATT :1377.96295 Quantity ATR :1207.96295 ====MTL_ONHAND_QUANTITIES_DETAIL==== ITEM_ID ORG_ID SUB_CODE LOC_ID REVISION LOT STATUS_ID SUM(PRIMARY_QTY) 149 207 Engineer ... ... ... ... 0 149 207 CustReturn ... ... ... ... 65 149 207 FldSvc ... ... ... ... 100 149 207 ptfgi 3679 ... ... ... 108 149 207 DropShip ... ... ... ... 15 149 207 FGI ... ... ... ... 1406.96295 149 207 FS_Truck6 ... ... ... ... 0 149 207 ptfgi 3678 ... ... ... 5 149 207 Staging1 ... ... ... ... 43 149 207 bb_stores ... ... ... ... 3 149 207 Stores ... ... ... ... 100 ====MTL_RESERVATIONS==== RESERVATION_ID ITEM_ID ORG_ID SUB_CODE LOC_ID REVISION LOT PRIMARY_RESERVATION_QTY 307 149 207 FGI ... ... ... 1 306 149 207 FGI ... ... ... 3 361752 149 207 Staging1 ... ... ... 2 2614426 149 207 Staging1 ... ... ... 10 2614423 149 207 Staging1 ... ... ... 1 2611441 149 207 Staging1 ... ... ... 10 2605405 149 207 ... ... ... ... 100 2605399 149 207 ... ... ... ... 100 2605402 149 207 ... ... ... ... 100 2605396 149 207 ... ... ... ... 10 2608393 149 207 ... ... ... ... 10 2608396 149 207 ... ... ... ... 10 2608399 149 207 ... ... ... ... 10 2608405 149 207 ... ... ... ... 10 2617426 149 207 ... ... ... ... 1 2608411 149 207 ... ... ... ... 10 2610402 149 207 ... ... ... ... 1 2610405 149 207 ... ... ... ... 1 2610408 149 207 ... ... ... ... 1 2610396 149 207 ... ... ... ... 2 2610426 149 207 ... ... ... ... 1 2610429 149 207 ... ... ... ... 1 2610432 149 207 ... ... ... ... 1 2610435 149 207 ... ... ... ... 1 2610438 149 207 ... ... ... ... 1 2610441 149 207 ... ... ... ... 1 2610444 149 207 ... ... ... ... 10 2614432 149 207 ... ... ... ... 10 2617423 149 207 ... ... ... ... 1 2602393 149 207 ... ... ... ... 2 2602399 149 207 ... ... ... ... 2 2602396 149 207 ... ... ... ... 1 2601402 149 207 ... ... ... ... 1 2597396 149 207 ... ... ... ... 1 13809 149 207 ... ... ... ... 3 2608408 149 207 ... ... ... ... 10 ====MTL_MATERIAL_TRANSACTIONS_TEMP==== TEMP_ID ITEM_ID ORG_ID SUB_CODE LOC_ID REVISION LOT PRIMARY_QTY 22189757 149 207 FGI ... ... ... -7 22196134 149 207 FGI ... ... ... -10 22189749 149 207 FGI ... ... ... -10 22196132 149 207 FGI ... ... ... 1 22188337 149 207 FGI ... ... ... 1 22188339 149 207 FGI ... ... ... 1 22188335 149 207 FGI ... ... ... 1 ====MTL_LOT_NUMBERS==== ITEM_ID ORG_ID LOT DISABLE_FLAG EXPIRATION_DATE RESERVABLE_TYPE ====MTL_PARAMETERS==== ORG_ID ORG_CODE MASTER_ORG_ID NEGATIVE_INV_RECEIPT_CODE STOCK_LOC_CONTROL_CODE SERIAL_NUMBER_TYPE SERIAL_NUMBER_GENERATION LOT_NUMBER_UNIQUENESS LOT_NUMBER_GENERATION WMS_ENABLED_FLAG SOURCE_TYPE PRIMARY_COST_METHOD 207 M1 204 1 4 1 2 1 2 N 2 1 ====MTL_SYSTEM_ITEMS_B==== ITEM_ID ORG_ID SEGMENT1 ENABLED_FLAG LOT_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE LOCATION_CONTROL_CODE RESTRICT_SUBINVENTORIES_CODE RESTRICT_LOCATORS_CODE REVISION_QTY_CONTROL_CODE RESERVABLE_TYPE INVENTORY_ITEM_FLAG STOCK_ENABLED_FLAG 149 207 AS54888 Y 1 1 1 2 2 1 1 Y Y ====SUBINV RESERVABLE==== SECONDARY_INVENTORY_NAME RESERVABLE_TYPE Engineer 1 FGI 1 Staging1 1 FS_Truck6 1 FldSvc 2 DropShip 1 bb_stores 1 Stores 1 CustReturn 2 ptfgi 1 ====LOCATOR RESERVABLE==== INVENTORY_LOCATION_ID SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT19 SEGMENT20 RESERVABLE 3679 1 1 24 ... ... 1 3678 1 1 23 ... ... 2 ====File Version==== NAME TEXT INV_QUANTITY_TREE_UE /* $Header: INVQTUEB.pls 120.2.12000000.3 2008/06/12 15:53 INV_QUANTITY_TREE_PVT /* $Header: INVVQTTB.pls 120.20.12000000.33 2010/08/13 11:01 INV_QUANTITY_TREE_GRP /* $Header: INVGQTTB.pls 120.0 2005/05/25 05:13:16 appldev n INV_QUANTITY_TREE_PUB /* $Header: INVPQTTB.pls 120.4 2006/09/13 09:34:02 ramarava ====END====
0 0
- Onhand Qty(Tree) Diagnostics Scripts R12
- Onhand Qty(Tree) Diagnostics Scripts
- Quantity Tree - Inventory Onhand Quantity Calculation Logic
- -diagnostics
- SCRIPTS
- scripts
- scripts
- rabbitMq:DIAGNOSTICS
- BSOD Diagnostics
- Clang Diagnostics
- R12设置
- R12客户关系
- 刷题#R12
- System.Diagnostics.Debug和System.Diagnostics.Trace
- System.Diagnostics.Debug和System.Diagnostics.Trace
- System.Diagnostics.Debug和System.Diagnostics.Trace
- System.Diagnostics.Debug和System.Diagnostics.Trace
- System.Diagnostics.Process
- jQuery给控件赋值
- Measurement Studio6.0安装参考（转）
- Oracle Create Table as Select
- UIView之setNeedsDisplay、setNeedsLayout
- Android利用LocalSocket实现Java端进程与C端进程之间的IPC
- Onhand Qty(Tree) Diagnostics Scripts R12
- Android 下挂载android的nfs文件系统
- Drools学习笔记1-规则引擎介绍
- 用户在页面上提交表单到服务器时，因为插入html代码报错
- 编写android HAL举例: lights
- ORA-00001 : Unique Constraint Violated Caused by DATE VS Timestamp(6)
- naver各个词典服务
- android 几种获得收益渠道的封装
- ORA-00845: MEMORY_TARGET not Supported on This System
