Data fix to remove the responsibility end date from back end(Where data file is comma seprated .csv file

/*============================================================================+
File Name : NAPP_GLOBAL_RESP_FIX.sql
Object Name : NAPP_GLOBAL_RESP_FIX.sql

Description : One Time update script to update the resp end date

Revision History :

Ver Date Name Revision Description
=== ========= ============== ==========================================
1.0 03-DEC-2010 Neelima Created the program
+=============================================================================*/
SET PAGESIZE 0;
SET LINESIZE 10000;
SET SERVEROUTPUT ON SIZE 100000;
SET TRIMSPOOL ON;
SET DEFINE OFF;


DECLARE
l_file_handle UTL_FILE.FILE_TYPE;
l_eof CHAR;
l_user_name VARCHAR2 (150);
l_resp_name VARCHAR2 (500);
l_text VARCHAR2 (5000);
l_start_date DATE;
l_start_date_str VARCHAR2(50);
l_user_id number;
l_resp_id number;
l_resp_appl_id number;
l_resp_start_date date;
l_message varchar2(100);
l_dir_name varchar2(100);



BEGIN

BEGIN

select name into l_dir_name from v$database;
dbms_output.put_line('UTL file directory ''/usr/tmp/'l_dir_name);

END;

Fnd_Global.apps_initialize (1429,20420,1);
l_eof := 'N';
l_file_handle :=
UTL_FILE.FOPEN
('/usr/tmp/'l_dir_name
,'NAPP_GLOBAL_RESP_DATA.csv'
,'r'
);

LOOP
l_user_id := -1;
l_resp_id := -1;
l_resp_appl_id := -1;
l_message:='SUCCESS';
l_resp_start_date := null;

BEGIN
UTL_FILE.GET_LINE (l_file_handle, l_text);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_eof := 'Y';
END;

EXIT WHEN l_eof = 'Y';

l_user_name := SUBSTR (l_text, 1, INSTR (l_text, ',') - 1);
l_resp_name := REPLACE(SUBSTR (l_text
, INSTR (l_text, ',') + 1
, length(l_text)
),',','');
BEGIN

SELECT fu.user_id,frv.responsibility_id,frv.application_id,grp.start_date
-- INTO l_user_id,l_resp_id,l_resp_appl_id,l_resp_start_date
from fnd_responsibility_vl frv
,fnd_user_resp_groups_direct grp
,fnd_user fu
WHERE fu.user_name = UPPER ('ZOLLER')
AND upper(frv.responsibility_name)=upper('NAPP US PS Internet Expense (HTML)')
AND grp.user_id = fu.user_id
AND frv.responsibility_id=grp.responsibility_id
AND fu.end_date is null;


Exception when OTHERS then

-- write_log ('ERROR: User name ' l_user_name 'do not exist');
l_message:='FAILED:VALIDATION';

END;

IF l_user_id=-1 OR l_resp_id=-1 OR l_resp_appl_id=-1 OR l_resp_start_date is NULL then

l_message:='FAILED:DATA MISSING';

ELSE

BEGIN
fnd_user_resp_groups_api.update_assignment
(user_id => l_user_id
,responsibility_id => l_resp_id
,responsibility_application_id => l_resp_appl_id
,security_group_id => 0
,start_date => l_resp_start_date
,end_date => NULL
,description => l_resp_name
,update_who_columns => 'Y'
);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_message:='FAILED:API';
END ;

END IF;

dbms_output.put_line(l_user_name''l_resp_name''l_message);


END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('When others ' SQLERRM);
RAISE;

END;
/