Form Securing Attributes

DECLARE
v_return_status VARCHAR2 (1);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (240);
exception_tag_user_id NUMBER;
api_unexpectedly_failed EXCEPTION;
BEGIN
FOR x IN (SELECT fu.user_id web_user_id,
fu.user_name,
'ICX_HR_PERSON_ID' attribute_code,
178 attribute_application_id,
fu.employee_id number_value,
-1 created_by,
SYSDATE creation_date,
-1 last_updated_by,
SYSDATE last_update_date,
-1 last_update_login
FROM fnd_user fu
WHERE fu.employee_id IS NOT NULL
AND fu.creation_date > SYSDATE - 220
AND NOT EXISTS ( SELECT 'x' FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = fu.user_id
AND awusav.attribute_application_id = 178
AND awusav.attribute_code = 'ICX_HR_PERSON_ID')
UNION
SELECT fu.user_id web_user_id,
fu.user_name,
'TO_PERSON_ID' attribute_code,
178 attribute_application_id,
fu.employee_id number_value,
-1 created_by, SYSDATE creation_date,
-1 last_updated_by,
SYSDATE last_update_date,
-1 last_update_login
FROM fnd_user fu
WHERE fu.employee_id IS NOT NULL
AND fu.creation_date > SYSDATE - 220
AND NOT EXISTS ( SELECT 'x' FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = fu.user_id
AND awusav.attribute_application_id = 178
AND awusav.attribute_code = 'TO_PERSON_ID'))
LOOP
icx_user_sec_attr_pub.create_user_sec_attr
(p_api_version_number => 1,
p_web_user_id => x.web_user_id,
p_attribute_code => x.attribute_code,
p_attribute_appl_id => x.attribute_application_id,
p_varchar2_value => fnd_api.g_false,
p_date_value => fnd_api.g_miss_date,
p_number_value => x.number_value,
p_created_by => x.created_by,
p_creation_date => x.creation_date,
p_last_updated_by => x.last_updated_by,
p_last_update_date => x.last_update_date,
p_last_update_login => x.last_update_login,
p_return_status => v_return_status,
p_msg_count => v_msg_count,
p_msg_data => v_msg_data );
IF v_return_status = fnd_api.g_ret_sts_unexp_error
THEN
exception_tag_user_id := x.web_user_id;
RAISE api_unexpectedly_failed;
ELSIF v_msg_count = 1
THEN-- retcode := 1;
DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ('*** Failed *** User ID ' x.web_user_id);
DBMS_OUTPUT.put_line ('. Message ' v_msg_count '. ' v_msg_data);
ELSIF v_msg_count > 1
THEN
-- retcode := 1;
DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ('*** Failed *** User ID ' x.web_user_id);
FOR y IN 0 .. v_msg_count
LOOP v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF v_msg_data IS NULL
THEN EXIT;
END IF;
DBMS_OUTPUT.put_line ('. Message ' y '. ' v_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.put_line (' Added Sec Attrib to ' x.web_user_id ' ' x.user_name ' ' x.attribute_code); END LOOP;
EXCEPTION
WHEN api_unexpectedly_failed
THEN
DBMS_OUTPUT.put_line ('API Unexpectedly Failed on User_id record ' (exception_tag_user_id)); DBMS_OUTPUT.put_line ('API Failed with ' SQLERRM);
DBMS_OUTPUT.put_line ('API Failed with ' SQLCODE);
DBMS_OUTPUT.put_line ('ALL API Transactions should / have been Rolled Back'); DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
-- retcode := 2;
DBMS_OUTPUT.put_line ('.');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('API Unexpectedly and passed to OTHERS Handler');
DBMS_OUTPUT.put_line ('Failed on receipt record ' (exception_tag_user_id));
DBMS_OUTPUT.put_line ('API Failed with ' SQLERRM);
DBMS_OUTPUT.put_line ('API Failed with ' SQLCODE);
DBMS_OUTPUT.put_line ('ALL API Transactions should / have been Rolled Back'); DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
--retcode := 2;
DBMS_OUTPUT.put_line ('.');
ROLLBACK;
END;/