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.
--
-- <
>
-- path:utl_file_dir
-- file name:qty_tree_diagnostics_itemname_orgcode_timestamp.log

-- <>
-- 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 (\'<>\', 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, \'<>\');
   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

<>                                                                
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                                                  

<>
========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====