Loading

Wednesday, March 31, 2010

How to Assign catalog group to the Item through PL/SQL

DECLARE
 V_item_id                   NUMBER: = 0;
 V_catalog_Group_id          NUMBER: = 0;
 V_organization_id           NUMBER: = 0;
 V_request_id                NUMBER: = 0; 
BEGIN 
  --Getting the Organization id   
  BEGIN
      SELECT Organization_id
      INTO   v_organization_id
      FROM   mtl_parameters mp
      WHERE  mp.organization_code = 'V1';
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the Organization id for Organization code V1 and error is '||SUBSTR (SQLERRM, 1,200));
   END;

   --Getting the item id for the existing item ABCTEST
   BEGIN
      SELECT inventory_item_id
      INTO   v_item_id
      FROM   mtl_system_items_b msi
      WHERE  msi.segment1 = 'ABCTEST'
      AND    msi.organization_id = v_organization_id;
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the item id for Item ABCTEST and error is '||SUBSTR (SQLERRM, 1,200));
   END;


   --Getting the catalog group id for the existing Catalog Group Name 'NewCatalog'
   BEGIN
      SELECT item_catalog_group_id
      INTO   v_catalog_group_id
      FROM   mtl_item_catalog_groups
      WHERE segment1='NewCatalog';
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the catalog group id for catalog group Newcatalog and error is '||SUBSTR (SQLERRM, 1,200));
   END;

   --Inserting into Item interface table
   BEGIN
      INSERT INTO mtl_system_items_interface
                 (inventory_item_id,
                  organization_id,
                  process_flag,
                  set_process_id,
                  transaction_type,
                  item_catalog_group_id
                  )
            VALUES
                 (v_item_id,
                  v_organization_id,
                  1,
                  1,
                  'UPDATE',
                  v_catalog_group_id
                  );
        COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in inserting record in interface table and error is '||SUBSTR (SQLERRM, 1,200));
   END;
  
   --Submit the item import program in Update Mode to update the item catalog group information
   BEGIN
     
        fnd_file.put_line (fnd_file.LOG,'--Submitting Item Import Program for Item--');
     
     
        v_request_id:= 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
                       ); 
         COMMIT;                      
         IF (v_request_id = 0) THEN        
             fnd_file.put_line (fnd_file.LOG,'Item Import Program Not Submitted');
         ELSE
             fnd_file.put_line (fnd_file.LOG,'Item Import Program submitted');            
         END IF;
  END;

END;

0 Responses to “How to Assign catalog group to the Item through PL/SQL”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.