Script to get Profile Option Values at Responsibility Level

SELECT frv.responsibility_name,
       fpo.profile_option_name,
       fpot.user_profile_option_name,
       fpov.profile_option_value
FROM   fnd_profile_options_tl fpot,
       fnd_profile_options fpo,
       fnd_profile_option_values fpov,
       fnd_responsibility_vl frv
WHERE  fpot.profile_option_name = fpo.profile_option_name
       AND fpo.profile_option_id = fpov.profile_option_id
       AND fpov.level_id = '10003' --Level=> 10001:SITE,10002:APP,10003:RESP,10005:SERVER,10006:ORG,10004:USER
       AND fpov.level_value = frv.responsibility_id
       AND fpot.LANGUAGE = 'US'
       --and fpot.user_profile_option_name = '<USER_PROFILE_NAME>'
       AND frv.responsibility_name LIKE '<RESPONSIBILITY_NAME>'

ORDER  BY frv.responsibility_name;  

(OR)

/*Query Profile Option Values at All Levels*/
SELECT po.user_profile_option_name,
       po.profile_option_name                      name,
       pov.level_id,
       Decode(pov.level_id, 10001, 'Site',
                            10002, 'Application',
                            10003, 'Responsibility',
                            10004, 'User',
                            10005, 'Server',
                            10006, 'Organization',
                            10007, 'ServResp',
                            'Undefined')           LEVEL_SET,
       Decode(To_char(pov.level_id), '10001', '',
                                     '10002', app.application_short_name,
                                     '10003', rsp.responsibility_key,
                                     '10004', usr.user_name,
                                     '10005', svr.node_name,
                                     '10006', org.name,
                                     '10007', (SELECT n.node_name
                                               FROM   fnd_nodes n
                                               WHERE  n.node_id = level_value2)
                                              ||'/'
                                              || (
       Decode(pov.level_value, -1, 'Default',
                               (SELECT responsibility_key
                                FROM   fnd_responsibility
                                WHERE  responsibility_id = level_value)) ),
                                     pov.level_id) CONTEXT,
       pov.profile_option_value                    VALUE,
       pov.last_update_date,
       usrlst.user_name                            last_updated_by
FROM   fnd_profile_options_vl po,
       fnd_profile_option_values pov,
       fnd_user usr,
       fnd_application app,
       fnd_responsibility_vl rsp,
       fnd_user usrlst,
       fnd_nodes svr,
       hr_operating_units org
WHERE  usrlst.user_id = pov.last_updated_by
       AND pov.application_id = po.application_id
       AND pov.profile_option_id = po.profile_option_id
       AND usr.user_id(+) = pov.level_value
       AND rsp.application_id(+) = pov.level_value_application_id
       AND rsp.responsibility_id(+) = pov.level_value
       AND app.application_id(+) = pov.level_value
       AND svr.node_id(+) = pov.level_value
       AND org.organization_id(+) = pov.level_value
       AND po.user_profile_option_name = '---------'
       --and usr.END_DATE is NULL

; 

Item Category Assignment In Oracle Apps R12

 CREATE OR replace PROCEDURE Xx_apply_item_category(p_organization_id   NUMBER,                                                           p_category_set_name VARCHAR2,                                                         p_category_code     VARCHAR2,                                                         p_item_code         VARCHAR2,                                                         po_status OUT VARCHAR2,                                                               po_msg OUT VARCHAR2 )

AS
  l_msg_index_out     NUMBER;
  l_error_message     VARCHAR2(2000);
  l_error_code        VARCHAR2(2000);
  l_return_status     VARCHAR2(80);
  l_msg_count         NUMBER;
  l_msg_data          VARCHAR2(250);
  l_category_id       NUMBER;
  l_category_set_id   NUMBER;
  l_inventory_item_id NUMBER;
  l_organization_id   NUMBER;
  l_old_category_id   NUMBER;
  l_errm              VARCHAR2(4000);
  l_old_category      VARCHAR2(200);
BEGIN
  BEGIN
    SELECT mcs_tl.category_set_id,
           decode(control_level_disp,
                  'Org',p_organization_id,
                  <:MASTER_ORGANIZATION_ID>)organization_id
    INTO   l_category_set_id,
           l_organization_id
    FROM   mtl_category_sets_v mcs_tl
    WHERE  mcs_tl.category_set_name=p_category_set_name;
  
  EXCEPTION
  WHEN OTHERS THEN
    l_errm:=l_errm
    ||','
    ||'InvalidCategorySet';
  END;
  BEGIN
    SELECT mcb.category_id
    INTO   l_category_id
    FROM   mtl_categories_kfv mcb
    WHERE  mcb.concatenated_segments=p_category_code
    AND    mcb.structure_id=
           (
                  SELECT mcs.structure_id
                  FROM   mtl_category_sets_b mcs
                  WHERE  mcs.category_set_id=l_category_set_id);
  
  EXCEPTION
  WHEN OTHERS THEN
    l_errm:=l_errm
    ||','
    ||'InvalidCategoryCode';
  END;
  BEGIN
    SELECT inventory_item_id
    INTO   l_inventory_item_id
    FROM   mtl_system_items_b
    WHERE  segment1=p_item_code
    AND    organization_id=l_organization_id;
  
  EXCEPTION
  WHEN OTHERS THEN
    l_errm:=l_errm
    ||','
    ||'InvalidItemCode';
  END;
  BEGIN
    SELECT mcb.category_id,
           mcb.concatenated_segments
    INTO   l_old_category_id,
           l_old_category
    FROM   mtl_system_items_b msi ,
           mtl_item_categories mic ,
           mtl_categories_kfv mcb ,
           mtl_category_sets mcs
    WHERE  1=1
    AND    mic.inventory_item_id=msi.inventory_item_id
    AND    mic.organization_id=msi.organization_id
    AND    mic.category_id=mcb.category_id
    AND    mic.category_set_id=mcs.category_set_id
    AND    mcb.structure_id=mcs.structure_id
    AND    msi.inventory_item_id=l_inventory_item_id
    AND    msi.organization_id=l_organization_id
    AND    mcs.category_set_id=l_category_set_id;
  
  EXCEPTION
  WHEN OTHERS THEN
    l_old_category_id:=NULL;
  END;
  IF l_old_category=p_category_code THEN
    l_errm:=l_errm
    ||',Item already assigned to the category.';
  END IF;
  IF l_errm IS NULL THEN
    IF l_old_category_id IS NULL THEN
      inv_item_category_pub.Create_category_assignment(p_api_version=>1.0 ,
                                                       p_init_msg_list =>fnd_api.g_true ,
                                                       p_commit=>fnd_api.g_true ,
                                                       x_return_status=>l_return_status ,
                                                       x_errorcode=>l_error_code ,
                                                       x_msg_count=>l_msg_count ,
                                                       x_msg_data=>l_msg_data ,
                                                       p_category_id=>l_category_id ,
                                                       p_category_set_id=>l_category_set_id ,
                                                       p_inventory_item_id=>l_inventory_item_id ,
                                                       p_organization_id=>l_organization_id );
    ELSE
      inv_item_category_pub.Update_category_assignment(p_api_version=>1.0 ,
                                                       p_init_msg_list=>fnd_api.g_false ,
                                                       p_commit=>fnd_api.g_true ,
                                                       x_return_status=>l_return_status ,
                                                       x_errorcode=>l_error_code ,
                                                       x_msg_count=>l_msg_count ,
                                                       x_msg_data=>l_msg_data ,
                                                       p_category_id=>l_category_id ,
                                                       p_category_set_id=>l_category_set_id ,
                                                       p_inventory_item_id=>l_inventory_item_id ,
                                                       p_organization_id=>l_organization_id ,
                                                       p_old_category_id=>l_old_category_id );
    END IF;
    IF l_return_status<>fnd_api.g_ret_sts_success THEN
      FOR i IN 1..l_msg_count
      LOOP
        apps.fnd_msg_pub.Get(p_msg_index=>i ,
                             p_encoded=>fnd_api.g_false ,
                             p_data=>l_msg_data ,
                             p_msg_index_out=>l_msg_index_out );
        IF l_error_message IS NULL THEN
          l_error_message:=Substr(l_msg_data,1,250);
        ELSE
          l_error_message:= l_error_message
          ||'/'
          ||Substr(l_msg_data,1,250);
        END IF;
      END LOOP;
      -- DBMS_OUTPUT.put_line('*****************************************');
      --  DBMS_OUTPUT.put_line('APIError:'||l_error_message);
      --  DBMS_OUTPUT.put_line('*****************************************');
      po_msg:=l_error_message;
      po_status:='E';
    ELSE
      po_status:='S';
      --   DBMS_OUTPUT.put_line('*****************************************');
      --   DBMS_OUTPUT.put_line('Created Category Assiginment from Item id:'||l_inventory_item_id||'Successfully');
      --   DBMS_OUTPUT.put_line('*****************************************');
    END IF;
  ELSE
    po_msg:=l_errm;
    po_status:='E';
  END IF;
EXCEPTION
WHEN OTHERS THEN
  po_msg:=SQLERRM;
  po_status:='E';
END xx_apply_item_category;

CREATE TABLE xx_items_temp
             (
                          item_code        VARCHAR2(50 byte),
                          inv_cat          VARCHAR2(240 byte),
                          category_flag    VARCHAR2(1 byte)
             );

DECLARE
  v_status VARCHAR2(10);
  v_msg    VARCHAR2(2000);
BEGIN
  FOR c1 IN
  (
         SELECT msi.inventory_item_id,
                msi.segment1 item_code,
                1            category_set_id,
                'Inventory'  category_set_name,
                mck.category_id,
                mck.concatenated_segments category_code,
                <organization_id>         organization_id
         FROM   mtl_system_items_b msi,
                mtl_categories_kfv mck ,
                xx_items_temp xi
         WHERE  1=1
         AND    xi.inv_cat=mck.concatenated_segments
         AND    xi.item_code=msi.segment1
         AND    msi.organization_id = <:MASTER_ORGANIZATION>
         AND    mck.structure_id=101
         AND    xi.category_flag='N'
  )
  LOOP
    v_status:=NULL;
    v_msg:=NULL;
    Xx_apply_item_category(c1.organization_id,c1.category_set_name, c1.category_code,c1.item_code, v_status, v_msg );
    IF v_status!='S' THEN
      dbms_output.Put_line('Error in Item Code:'
      ||c1.item_code);
    ELSE
      UPDATE xx_items_temp
      SET    category_flag='Y'
      WHERE  item_code=c1.item_code;
    
    END IF;
    COMMIT;
  END LOOP;
END;