Restoring user’s responsibilities after refresh of QA/TEST/Devl instance

Many of us have fewer responsibilities in production instance and many responsibilities in QA or Test or Devl instance. Whenever QA/Test/Devl instances are refreshed from production, we loose our responsibilities unless Mr. DBA have some mechanism to restore user’s responsibilities. I written following scripts to download a user’s responsibilities and then upload after refresh. the scripts are

Note: These scripts serve an example of

1. UTL_FILE


2. XMLGEN.getxml


3. xslprocessor.valueof

Download script

DECLARE
  l_file_Name    VARCHAR2(30);
  l_dir          VARCHAR2(90);
  l_clob         CLOB  ;
  l_maxlen       NUMBER := 32767;
  l_buffer       VARCHAR2(32767);
  l_qry          VARCHAR2(2000);
  l_newline_pos  NUMBER;
  l_clobLen      NUMBER;
  l_file         UTL_FILE.FILE_TYPE;
  l_start_pos    NUMBER    := 1;

BEGIN

  fnd_global.apps_initialize(&1,21623,660);
  l_file_name := 'zUserResp_' || FND_GLOBAL.user_id || '.xml';
  l_dir       := '/home/users/smisra'; -- Should be change to your UNIX/LINUX home dir
  l_qry       := '
    SELECT a.user_id
         , a.security_group_id
         , TO_CHAR(a.start_date,''YYYYMMDD'') start_date
         , TO_CHAR(a.end_date  ,''YYYYMMDD'') end_date
         , a.description
         , a.responsibility_id
         , a.responsibility_application_id
      FROM fnd_user_resp_groups a
     WHERE a.user_id = ' ;

  SELECT DBMS_XMLGEN.getxml(l_qry || FND_GLOBAL.user_id)
    INTO l_clob
    FROM DUAL;

  l_clobLen := DBMS_LOB.GETLENGTH(l_clob);
  l_file    := UTL_FILE.FOPEN(l_dir, l_file_name,'W',l_maxlen);

  WHILE l_start_pos < l_clobLen LOOP
    l_buffer := DBMS_LOB.SUBSTR(l_clob, l_maxlen, l_start_pos);
    EXIT WHEN l_buffer IS NULL;
    l_newline_pos  := INSTR(l_buffer,CHR(10),-1);
    IF l_newline_pos <> 0
    THEN
      l_buffer := SUBSTR(l_buffer,1,l_newline_pos-1);
      l_start_pos := l_start_pos + l_newline_pos;
    ELSE
      l_start_pos := l_start_pos + l_maxlen;
    END IF;
    UTL_FILE.PUT_LINE(l_file, l_buffer,TRUE);
  END LOOP;

  UTL_FILE.FCLOSE(l_file);

EXCEPTION
WHEN OTHERS THEN
  IF UTL_FILE.IS_OPEN(l_file) THEN
    UTL_FILE.FCLOSE(l_file);
  END IF;
  RAISE;

END;
/

Upload Script

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_parser            XMLPARSER.parser;
  l_xml_doc           XMLDOM.domdocument;
  l_node_list         XMLDOM.domnodelist;
  l_user_id           VARCHAR2(240);
  l_security_group_id VARCHAR2(240);
  l_start_date        VARCHAR2(240);
  l_end_date          VARCHAR2(240);
  l_resp_id           VARCHAR2(240);
  l_resp_appl_id      VARCHAR2(240);
  l_dir               VARCHAR2(240);
  l_file              VARCHAR2(240);
BEGIN

   FND_GLOBAL.apps_initialize(&1,21623,660);

   l_dir := '/home/users/smisra/'; -- Should be change to your UNIX/LINUX home dir
   l_file := 'zUserResp_' || FND_GLOBAL.user_id ;
   --
   -- Create a parser.
   --
   l_parser := xmlparser.newparser;
   --
   -- set some characteristics
   --
   xmlparser.setValidationMode(l_parser, FALSE);
   xmlparser.setErrorLog(l_parser, l_dir || l_file || '.err');
   xmlparser.setBaseDir (l_parser, l_dir || l_file || '.xml');
   --
   -- parse input file
   --
   xmlparser.parse(l_parser, l_dir || l_file || '.xml');

   l_xml_doc := xmlparser.getdocument (l_parser);
   --
   -- Free resources associated with the Parser now it is no longer needed.
   xmlparser.freeparser (l_parser);
   --
   -- Get response code in the document using the XPATH syntax.
   l_node_list := xslprocessor.selectnodes (xmldom.makenode (l_xml_doc), '//ROWSET/ROW');
   --
   dbms_output.put_line(xmldom.getLength(l_node_list));
   FOR i in 0..xmldom.getLength(l_node_list) -1
   LOOP
     l_user_id           := xslprocessor.valueof(xmldom.item(l_node_list,i),'USER_ID');
     l_security_group_id := xslprocessor.valueof(xmldom.item(l_node_list,i),'SECURITY_GROUP_ID');
     l_start_date        := xslprocessor.valueof(xmldom.item(l_node_list,i),'START_DATE');
     l_end_date          := xslprocessor.valueof(xmldom.item(l_node_list,i),'END_DATE');
     l_resp_id           := xslprocessor.valueof(xmldom.item(l_node_list,i),'RESPONSIBILITY_ID');
     l_resp_appl_id      := xslprocessor.valueof(xmldom.item(l_node_list,i),'RESPONSIBILITY_APPLICATION_ID');

     dbms_output.put_line('No:' || i || ', ' ||l_user_id);
     dbms_output.put_line('No:' || i || ', ' ||l_security_group_id);
     dbms_output.put_line('No:' || i || ', ' ||l_start_date);
     dbms_output.put_line('No:' || i || ', ' ||l_end_date);
     dbms_output.put_line('No:' || i || ', ' ||l_resp_id);
     dbms_output.put_line('No:' || i || ', ' ||l_resp_appl_id);
     FND_USER_RESP_GROUPS_API.upload_assignment
     ( l_user_id
     , l_resp_id
     , l_resp_appl_id
     , l_security_group_id
     , TO_DATE(l_start_date,'YYYYMMDD')
     , TO_DATE(l_end_date  ,'YYYYMMDD')
     , NULL
     ) ;
   END LOOP;
END;
/