Tuesday, November 18, 2008

Copy Discoverer Priviliges of one/several users to another

I acted as an interim Discoverer gatekeeper when our discoverer guru suddenly decided to quit. I am a database person and do not quite like the way some of the business functions are "supposed" to be performed. I like to dwell more on the database side to dig and tweak it from that end to get some of the stuff done. I must advice that this is not recommended and should not be done all the time. I however blatantly admit getting into trouble for retorting to this method, but shamefully I just cannot hide my weakness and do most of the time go to the database to get these kind of things done. Here is a sample common activity that one as a discoverer gatekeeper needs to perform in the IT world. Test the script in a dev database before running it in production.

--
--
declare
cursor cur_from_user IS
SELECT DISTINCT
doc_id
FROM
EUL5_us.EUL5_documents disco_docs,
EUL5_us.EUL5_access_privs disco_shares,
EUL5_us.EUL5_eul_users disco_users,
apps.fnd_user users1
where 1=1
and disco_users.EU_USERNAME='#'||users1.USER_ID
and disco_shares.AP_EU_ID=disco_users.EU_ID
and disco_docs.DOC_ID=disco_shares.GD_DOC_ID
and user_name IN ('AMITABH','DHARMENDRA');

cursor cur_to_user IS
SELECT
disco_users.EU_ID
FROM
EUL5_us.EUL5_eul_users disco_users,
apps.fnd_user users1
where 1=1
and disco_users.EU_USERNAME='#'||users1.USER_ID
and user_name='ABHISHEK';

l_ap_id NUMBER;
l_eu_id NUMBER;
l_temp NUMBER := NULL;

BEGIN

OPEN cur_to_user;
FETCH cur_to_user INTO l_eu_id;
CLOSE cur_to_user;

IF l_eu_id IS NULL THEN
Raise_application_error(-20011, 'Invalid FND User Name');
END IF;


FOR i_rec in cur_from_user
LOOP

select EUL5_id_seq.nextval INTO l_ap_id from dual;

BEGIN

select gd_doc_id INTO l_temp from EUL5_ACCESS_PRIVS
where gd_doc_id=i_rec.doc_id
and ap_eu_id = l_eu_id;

EXCEPTION WHEN NO_DATA_FOUND THEN

INSERT INTO EUL5_ACCESS_PRIVS ( AP_ID
, AP_TYPE
, AP_EU_ID
, AP_PRIV_LEVEL
, GP_APP_ID
, GBA_BA_ID
, GD_DOC_ID
, AP_ELEMENT_STATE
, AP_CREATED_BY
, AP_CREATED_DATE
, AP_UPDATED_BY
, AP_UPDATED_DATE
, NOTM )
VALUES ( l_ap_id
, 'GD'
, l_eu_id
, 0
, NULL
, NULL
, i_rec.doc_id
, 0
, '#2309'
, SYSDATE
, '#2309'
, SYSDATE
, 0);

END;


END LOOP;

END ;

commit;

No comments:

Post a Comment