INVENTORY MANAGER > SETUP > ORGANIZATIONS > STOCK LOCATORS


CREATE TABLE XX_STOCK_LOCATOR_STAGING (LOCATOR_CONCAT_SEGMENTS VARCHAR2(2000))

-----------------------------------------------------------------------------------
create or replace procedure XX_CREATE_STOCK_LOCATORS
is
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 := 1350; /*Organization_id */
l_organization_code     VARCHAR2(10) := '201'; /*Organization_Code */
l_sub_code              VARCHAR2(10) ; /*Variable for Subinventory*/
l_concatenated_segments VARCHAR2(100); /*Variable for Locator Segment*/

--l_user_id NUMBER := 1262; /* User ID From FND_users Table */
--l_resp_id NUMBER := 20634; /*Responsibility Id*/
--l_resp_appl_id NUMBER := 401; /* Responsibility Application id */

CURSOR C1
IS
SELECT * FROM XX_STOCK_LOCATOR_STAGING;

BEGIN
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/

FND_GLOBAL.APPS_INITIALIZE(1090,51007,401);

FND_MSG_PUB.INITIALIZE;

  FOR I IN C1 LOOP


    l_concatenated_segments := i.LOCATOR_CONCAT_SEGMENTS;

    l_sub_code := 'Main Store';

    l_org_id:= 1350;

    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               => 'DEFAULT', /*You can also use here description of Your Locator Combination*/
            p_inventory_location_type   => 3,         -- Storage locator
            p_picking_order             => NULL,
            p_location_maximum_units    => NULL,
            p_subinventory_code         => l_sub_code, /*Subinventory 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);
       
                   /*
            IF l_return_status IN ('E', 'U') THEN
            DBMS_OUTPUT.PUT_LINE('# of Errors '||l_msg_count);

            IF l_msg_count = 1 THEN
            DBMS_OUTPUT.PUT_LINE('Error '||l_msg_data);
            ELSE
            FOR i IN 1..l_msg_count LOOP
            DBMS_OUTPUT.PUT_LINE('Error '||FND_MSG_PUB.GET(i, 'F'));
            END LOOP;
            END IF;
            ELSE
            DBMS_OUTPUT.PUT_LINE('Locator Id is '||l_locator_id);
            END IF;
            */
   END LOOP;

1) Custom Table to store account segments.

create table XX_ACCOUNT_COMBINATIONS (CODE VARCHAR2(2000))

===========================================================================================


2) Function to Create or Check the Code Combinations in GL 

create or replace function create_ccid 
( p_concat_segs in varchar2
) return varchar2
is
  -- pragma autonomous_transaction; -- if you need autonomy!
  l_keyval_status     BOOLEAN;
  l_coa_id            NUMBER;
   l_err_msg          varchar2(2000);
   l_error varchar2(255);
begin
  begin
    select chart_of_accounts_id
    into   l_coa_id
    from   gl_sets_of_books
    where  set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID');
  exception
    when no_data_found then
      dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');
      dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');
      raise;
  end;
  -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
  --create will only work if dynamic inserts on and cross validation rules not broken
  l_keyval_status := fnd_flex_keyval.validate_segs(
                                           'CHECK_COMBINATION',
                                           'SQLGL',
                                           'GL#',
                                           l_coa_id,
                                           p_concat_segs,
                                           'V',
                                           sysdate,
                                           'ALL', NULL, NULL, NULL, NULL,
                                           FALSE,FALSE, NULL, NULL, NULL);
                                
  if l_keyval_status then  
    return 'S';   
  else
   --return l_error;
    l_err_msg:=substr(fnd_flex_keyval.error_message, 1, 240);     --fnd_message.get;
    
    l_error := substr(fnd_flex_keyval.error_message, 1, 240); 
   dbms_output.put_line(l_error); 
   dbms_output.put_line('ERROR SEGMENT :');
   l_error := to_char(fnd_flex_keyval.error_segment);
   dbms_output.put_line(l_error); 
   dbms_output.put_line('ERROR ENCODED :');
   l_error := substr(fnd_flex_keyval.encoded_error_message, 1, 240);
   dbms_output.put_line(l_error); 
   dbms_output.put_line('FALSE'); 

    dbms_output.put_line(l_err_msg||substr(sqlerrm,150,3));
    return l_error;
  end if;
end create_ccid;
/

3) Table to store Segments and Error / Success Status

CREATE TABLE XX_COMBINATION_ERROR_STATUS
(
  CODE    VARCHAR2(2000 BYTE),
  STATUS  VARCHAR2(2000 BYTE)
)


4) Procedure to Call Function to create combinations from the custom table

CREATE OR REPLACE procedure APPS.XX_CREATE_GL_ACC_COMBINATION
IS
V_COMBINATION VARCHAR2(240);
CURSOR C1 IS
SELECT DISTINCT CODE 
FROM   XX_ACCOUNT_COMBINATIONS;
--where   rownum=1;

begin

FOR I IN C1 LOOP

select create_ccid(I.CODE) into V_COMBINATION  from dual;

dbms_output.put_line (V_COMBINATION);

insert into xx_combination_error_status (code,status) values (i.code,v_combination);

END LOOP;


end;
/