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;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment