R12 - Upload Item Specific UOM Conversion

Hi,

Today i am posting how to upload item specific UOM conversion rates by using API.

Procedure : CREATE_UOM_CONVERSION  Is introduced in R12.1.3.

DECLARE
v_from_uom       VARCHAR2(200);
v_to_uom         VARCHAR2(200);
v_item_id        NUMBER;
v_uom_rate       NUMBER;
x_return_status  VARCHAR2(99);

BEGIN

  v_from_uom := 'DOZEN'; 
  v_item_id := '9999';
  v_uom_rate := '9';
  v_to_uom := 'EA';


  INV_CONVERT.CREATE_UOM_CONVERSION
                  (
                    p_from_uom_code => v_from_uom,
                    p_to_uom_code   => v_to_uom,
                    p_item_id       => v_item_id,
                    p_uom_rate      => v_uom_rate,
                    x_return_status => x_return_status
                  );
END;

R12 - API To Assign Catalog Group To Item

Hi All,

Here is the process to attach catalog group to an item through interface.

1. Insert the data into mtl_system_items_interface.

      INSERT INTO mtl_system_items_interface
                 (inventory_item_id,
                  organization_id,
                  process_flag,
                  set_process_id,
                  transaction_type,
                  item_catalog_group_id
                  )
            VALUES
                 (9999,
                  207,
                  1,
                  1,
                  'UPDATE',
                 9
                  );

2. Run Item Import Program.

Fnd_Request.submit_request (
                       application   => 'INV',
                       Program       => 'INCOIN',
                       description   => NULL,
                       start_time    => SYSDATE,
                       sub_request   => FALSE,
                       argument1     => v_organization_id,
                       argument2     => 1,           
                       argument3     => 1,  --Group ID option (All)
                       argument4     => 1,  -- Group ID Dummy
                       argument5     => 1,  -- Delete processed Record 
                       argument6     => 1,  -- Set Process id                   
                       argument7     => 2   -- Update item
                       );

R12 - API to Create Oracle Sub Inventories

Hi All,

There is no API available to create Sub Inventories in oracle. We have to directly update the   mtl_secondary_inventories by using API callled : mtl_secondary_inventories_pkg.insert_row.

R12 - API To Create Locators In Inventory

Hi,
Today i am posting the sample code to create Locators in Inventory.


Data File :
------------------------------


ORGANIZATION_CODE|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|DECRIPTION|SUBINVENTORY|
M1|2|2|2|2|DESCRIPTION_1|Stores|
M1|3|3|3|3|DESCRIPTION_1|Stores|
M1|4|4|4|4|DESCRIPTION_1|Staging|
M1|5|5|5|5|DESCRIPTION_1|Staging|

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



DECLARE
l_msg_data              VARCHAR2(100);
l_msg_count             NUMBER;
l_return_status         VARCHAR2(1);
l_locator_id            NUMBER;
l_locator_exists        VARCHAR2(1);
l_org_id                NUMBER := 207;
l_organization_code     VARCHAR2(10) := 'M1';
l_sub_code              VARCHAR2(10) ;
l_concatenated_segments VARCHAR2(100); /

BEGIN

    l_concatenated_segments := '2.2.2.2';  --  SEGMENT1.SEGMENT2.SEGMENT3.SEGMENT4
    l_sub_code := 'Stores';
    l_org_id:= 141;

    INV_LOC_WMS_PUB.CREATE_LOCATOR(
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            x_inventory_location_id     => l_locator_id,
            x_locator_exists            => l_locator_exists,
            p_organization_id           => l_org_id,
            p_organization_code         => l_organization_code,
            p_concatenated_segments     => l_concatenated_segments,
            p_description               => 'TEST',
            p_inventory_location_type   => 3,         -- Storage locator
            p_picking_order             => NULL,
            p_location_maximum_units    => NULL,
            p_subinventory_code         => l_sub_code,
            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
            );
         
         
       DBMS_OUTPUT.PUT_LINE('Return Status '||l_concatenated_segments||' - '||l_return_status);
     
END;