Tuesday, October 21, 2008

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;
/

1 comment: