Create Stock Locator using API - Oracle Apps R12

Create Stock Locator using API - Oracle Apps R12


We use locators to identify physical areas where we store inventory items. Item quantities can be tracked by locator.
Each item is assigned a locator so that it can be easily located when required for picking.

Locator Type: Indicate the locator type available choices.
Enter the subinventory where the locator resides.
Enter a picking order value indicating the priority for picking items from this locator relative to another locator. This value is used by Oracle Warehouse Management to sequence picking tasks.

Enter the inactive date for the locator. This is the date the locator becomes inactive.

Here is one simple PLSQL block to create Stock Locator using API:

Example:

Step 1) Create staging table:

CREATE TABLE XX_TEMP_STOCK_LOC_STG
( locator            VARCHAR2(120)
 ,description        VARCHAR2(120)
 ,type               VARCHAR2(80)
 ,status             VARCHAR2(30)
 ,subinventory       VARCHAR2(120)
 ,attribute_category VARCHAR2(120)
 ,Attribute1         VARCHAR2(250)
 ,Attribute2         VARCHAR2(250)
 ,Attribute3         VARCHAR2(250)
 ,Attribute4         VARCHAR2(250)
 ,Attribute5         VARCHAR2(250)
 ,rec_status         VARCHAR2(10)
 ,err_msg            VARCHAR2(2000)
);

Step 2) Insert data in Staging table:

* You will insert data in staging table using PLSQL block or Insert scripts.
Here I have insert data in staging table using Insert scripts for testing purpose:

INSERT INTO XX_TEMP_STOCK_LOC_STG (LOCATOR, DESCRIPTION, TYPE, STATUS, SUBINVENTORY, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5)
VALUES ('A.BIN.BIN', NULL, 'Storage Locator', 'Active', 'STR-HP', 'AB LOCATOR', NULL, NULL, '1', NULL, NULL);

INSERT INTO XX_TEMP_STOCK_LOC_STG (LOCATOR, DESCRIPTION, TYPE, STATUS, SUBINVENTORY, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5)
VALUES ('A0101.01.01', 'A0101.01.01', 'Storage Locator', 'Active', 'ZHAZ2', 'AB LOCATOR', 'ZH1A', 'P01', '1', NULL, NULL);

INSERT INTO XX_TEMP_STOCK_LOC_STG (LOCATOR, DESCRIPTION, TYPE, STATUS, SUBINVENTORY, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5)
VALUES ('A0102.01.01', 'A0102.01.01', 'Storage Locator', 'Active', 'ZHAZ', 'AB LOCATOR', 'ZH1A', 'P01', '1', NULL, NULL);

COMMIT;

I have inserted three records in Staging table:
   SELECT * FROM XX_TEMP_STOCK_LOC_STG;

OUTPUT: 

Step 3) Execute below PLSQL block to create Stock Locators:

SET SERVEROUTPUT ON SIZE 999999;
DECLARE
  CURSOR lcur_stock_loc_stg
  IS
    SELECT * FROM XX_TEMP_STOCK_LOC_STG
WHERE 1 = 1
   AND rec_status IS NULL;
    -- Variables
    lc_msg_data              VARCHAR2(4000);
    ln_msg_count             NUMBER;
    lc_return_status         VARCHAR2(5);
    ln_locator_id            NUMBER;
    lc_locator               VARCHAR2(200);
    ln_org_id                NUMBER;
    lc_organization_code     VARCHAR2(10) := 'AB2';
    lc_subinv_code           VARCHAR2(10) ;
    lc_concatenated_segments VARCHAR2(100);
    ln_inv_location_type     NUMBER;
    ln_organization_id       NUMBER;
    ln_responsibility_id     NUMBER;
    ln_application_id        NUMBER;
    ln_user_id               NUMBER;
    lc_err_msg               VARCHAR2(3999);

BEGIN
    --
    BEGIN
  SELECT user_id INTO ln_user_id
  FROM fnd_user WHERE user_name = 'ABC123';
    EXCEPTION
  WHEN others THEN ln_user_id := -1;
    END;
    --
    BEGIN
  SELECT responsibility_id, application_id
   INTO ln_responsibility_id, ln_application_id
   FROM fnd_responsibility_tl
   WHERE responsibility_name = 'ABC-Local Inventory'
        AND rownum = 1;
    EXCEPTION
  WHEN others THEN
    ln_responsibility_id := -1;
    ln_application_id    := -1;
    END;
    --
    BEGIN
SELECT organization_id INTO ln_organization_id
FROM org_organization_definitions
WHERE organization_code = lc_organization_code;
    EXCEPTION
        WHEN others THEN
          lc_err_msg := 'Organization code not found.';
          rec_status   := 'E';
    END;
    --
    For lreq IN lcur_stock_loc_stg
    LOOP
          lc_msg_data              := NULL;
  ln_msg_count            := NULL;
  lc_return_status         := NULL;
  ln_locator_id             := NULL;
  lc_subinv_code          := NULL;
  lc_concatenated_segments := NULL;
  ln_org_id                := NULL;
  ln_inv_location_type     := NULL;
  lc_err_msg               := NULL;
  ---
  fnd_global.apps_initialize(ln_user_id,ln_responsibility_id,ln_application_id);

          fnd_profile.put('MFG_ORGANIZATION_ID',ln_organization_id) ;
          lc_concatenated_segments := lreq.locator;
  lc_subinv_code           := lreq.subinventory;

  -- Get organization_id
  BEGIN
    SELECT organization_id INTO ln_org_id
  FROM org_organization_definitions
  WHERE organization_code = lc_organization_code;
          EXCEPTION
              WHEN others THEN
       lc_err_msg := 'Error in organization is: '||sqlerrm;
  END;

  -- Get inventory location type
  IF (lreq.type IS NOT NULL) THEN
    BEGIN
          SELECT lookup_code INTO ln_inv_location_type
  FROM fnd_lookup_values
  WHERE lookup_type = 'MTL_LOCATOR_TYPES'
  AND language = 'US'
  AND UPPER(meaning) = UPPER(lreq.type);
             EXCEPTION
                  WHEN others THEN
           lc_err_msg := lc_err_msg || ', Error in locator type is: '||sqlerrm||', for locator: '||lc_concatenated_segments;
    END;
    --
 END IF;
         --
 -- Call API
 inv_loc_wms_pub.create_locator(
              x_return_status               => lc_return_status,
              x_msg_count                  => ln_msg_count,
              x_msg_data                    => lc_msg_data,
              x_inventory_location_id     => ln_locator_id,
              x_locator_exists                  => lc_locator,
              p_organization_id                => ln_org_id,
              p_organization_code            => lc_organization_code,
              p_concatenated_segments     => lc_concatenated_segments,
              p_description               => lreq.description,
              p_inventory_location_type   => ln_inv_location_type, -- Storage locator
              p_picking_order             => NULL,
              p_location_maximum_units    => NULL,
              p_subinventory_code         => lc_subinv_code, -- subinventory
              p_location_weight_uom_code  => NULL,
              p_max_weight                   => NULL,
              p_volume_uom_code        => NULL,
              p_max_cubic_area            => NULL,
              p_x_coordinate                 => NULL,
              p_y_coordinate                 => NULL,
              p_z_coordinate                 => NULL,
              p_physical_location_id    => NULL,
              p_pick_uom_code            => NULL,
              p_dimension_uom_code  => NULL,
              p_length                    => NULL,
              p_width                     => NULL,
              p_height                    => NULL,
              p_status_id                 => 1,  -- Default status 'Active'
              p_dropping_order            => NULL,
              p_attribute_category        => lreq.attribute_category,
              p_attribute7                => NULL,
              p_attribute3   => NULL,
              p_attribute5                => NULL,
      p_attribute6                => NULL,
      p_attribute2                => NULL,
      p_attribute8                => NULL
   );
   -- Update staging table
     BEGIN
     UPDATE XX_TEMP_STOCK_LOC_STG
     SET rec_status = lc_return_status
    ,err_msg    = lc_msg_data||' '||lc_err_msg
     WHERE locator  = lreq.locator
   AND subinventory = lc_subinv_code;
       EXCEPTION
            WHEN others THEN NULL;
            dbms_output.put_line('Error in Update stg table is: '||sqlerrm);
      END;
   --
   COMMIT;
END LOOP;       -- End loop
 
EXCEPTION
    WHEN others THEN
dbms_output.put_line('Error is: '||lc_msg_data||'--'||sqlerrm);
END;
/

-----------------------------------------

After completed above PLSQL script then check Stock Locators create or not in Base table:

SELECT milk.concatenated_segments stock_locator_concat_seg
     , mil.description stock_loc_description
     , mil.subinventory_code
FROM mtl_item_locations mil
   , mtl_item_locations_kfv milk
WHERE  mil.inventory_location_id = milk.inventory_location_id
  AND (mil.disable_date IS NULL OR mil.disable_date > sysdate);


* If you want to check Stock Locator of Item then use below SQL query:

SELECT msib.segment1 item_number
     , milk.concatenated_segments stock_locator_concat_seg
     , mil.description stock_loc_description
     , mil.subinventory_code
FROM mtl_system_items_b msib
   , mtl_item_locations mil
   , mtl_item_locations_kfv milk
WHERE msib.organization_id = mil.organization_id
  AND msib.inventory_item_id = mil.inventory_item_id
  AND mil.inventory_location_id = milk.inventory_location_id
  AND (mil.disable_date IS NULL OR mil.disable_date > sysdate)
  AND msib.inventory_item_status_code = 'Active';