Oracle Sub Inventories Script

/* Formatted on 2015/11/06 17:29 (Formatter Plus v4.8.8) */
SELECT ORGANIZATION_code , secondary_inventory_name, description, status_code,
       default_cost_group_name,
       DECODE (quantity_tracked, 1, 'Y', 2, 'N') quantity_tracked,
       DECODE (asset_inventory, 1, 'Y', 2, 'N') asset_inventory,
       DECODE (depreciable_flag, 1, 'Y', 2, 'N') depreciable_flag,
       DECODE (inventory_atp_code, 1, 'Y', 2, 'N') inventory_atp_code,
       DECODE (reservable_type, 1, 'Y', 2, 'N') reservable_type,
       DECODE (availability_type, 1, 'Y', 2, 'N') availability_type,
       DECODE (planning_level, 1, 'Y', 2, 'N') planning_level,
       DECODE (default_count_type_code,
               1, 'Y',
               2, 'N'
              ) default_count_type_code,
       DECODE (locator_type,
               1, 'None',
               2, 'Prespecified',
               '3', 'Dynamice entry',
               '4', 'Item level control'
              ) locator_control,
       default_loc_status_code, picking_order, dropping_order disable_date,
       a.source_type, source_organization_code, --xxr12_get_val (a.source_organization_id,'ORGCODE') source_organization,
       a.source_subinventory, preprocessing_lead_time, processing_lead_time,
       postprocessing_lead_time,
       xxr12_get_val (a.material_account, 'GLCODE') material_account,
       xxr12_get_val (a.outside_processing_account,
                      'GLCODE'
                     ) outside_processing_account,
       xxr12_get_val (a.material_overhead_account,
                      'GLCODE'
                     ) material_overhead_account,
       xxr12_get_val (a.overhead_account, 'GLCODE') overhead_account,
       xxr12_get_val (a.resource_account, 'GLCODE') resource_account,
       xxr12_get_val (a.expense_account, 'GLCODE') expense_account,
       xxr12_get_val (a.encumbrance_account, 'GLCODE') encumbrance_account,
       notify_list, location_code,
       DECODE (subinventory_type,
               1, 'Storage',
               2, 'Receiving'
              ) subinventory_type
  FROM mtl_secondary_inventories_fk_v a , mtl_parameters b
 WHERE 1=1-- secondary_inventory_name = '15709'
 and a.ORGANIZATION_ID=b.ORGANIZATION_ID

Function :
CREATE OR REPLACE FUNCTION APPS.xxr12_get_val (p_val varchar2, p_type VARCHAR2)
--Reason : r12 Implementation
--By : Shagul
RETURN VARCHAR2
IS
   l_op_val  VARCHAR (100);
BEGIN
--Function
   BEGIN
      IF p_type = 'GLCODE'
      THEN
         BEGIN
            l_op_val:= NULL;

            SELECT concatenated_segments
              INTO l_op_val
              FROM gl_code_combinations_kfv
             WHERE code_combination_id = p_val AND 'GLCODE' = p_type;
         END;
      ELSIF p_type = 'ORGCODE'
      THEN
         BEGIN
            SELECT organization_code
              INTO l_op_val
              FROM org_organization_definitions
             WHERE organization_id = p_val AND 'ORGCODE' = p_type;
         END;
      ELSIF p_type = 'ORGID'
      THEN
         BEGIN
            SELECT organization_id
              INTO l_op_val
              FROM org_organization_definitions
             WHERE organization_code = p_val AND 'ORGID' = p_type;
         END;
      END IF;
   END;

   RETURN l_op_val;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/