Onhand Qty(Tree) Diagnostics Scripts

最新推荐文章于 2019-05-15 20:31:00 发布

原创

最新推荐文章于 2019-05-15 20:31:00 发布 · 4.7k 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一个用于收集Oracle库存管理中关键表（如MOQD, MR, MMTT等）数量树调试日志和数据转储的诊断脚本——qtytree_r12.sql。" 7783305,1286221,ArcGIS Server与Web Adaptor整合详解,"['GIS服务器', 'Web服务器', '集成', '负载均衡', '安全']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Diagnostics script qtytree_r12.sql is intended to collect quantity tree debug log and data dump from key tables related to quantity eg.MOQD,MR,MMTT etc.

qtytree_r12.sql

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
               A