|
|
|
|
|
|
|
|
| Re: how to delete unused disco reports by login as administrator [message #648952 is a reply to message #648948] |
Tue, 08 March 2016 11:11   |
 |
AyushJ26
Messages: 7
Registered: March 2016
Location: Delhi
|
Junior Member |
|
|
try this --->
Just change the name of users in cursor cur_from_user and cur_to_user. And run this script in sql plus
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;
|
|
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
