change tthe Discovere report Owner and Check the Discoverer Report Owner

The trick is to change the owner of the workbook to an Apps user then log in as that user and delete the workbooks. I recommend doing this in Desktop.

The workbooks are stored in a table called EUL5_DOCUMENTS and the column that contains the ID of the owner is called DOC_EU_ID. This is what we will change.

Step 1:

Log in to SQL as the owner of the EUL and run this:

SELECT DOC_EU_ID, DOC_NAME FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

obviously changing eul_owner to be the EUL owner you will see the list of workbooks that you want to delete. Make a note of the DOC_EU_ID.

Step 2:

Let's get a count.

SELECT COUNT(*) FROM EUL5_DOCUMENTS WHERE DOC_CREATED_BY = 'eul_owner';

obviously again changing eul_owner to be the EUL owner. Make a note of the COUNT

Ok so far?

Step 3:

Next, let's find the name and ID of a real Apps user who will become a temporary owner for your workbooks. The following code will list all Apps users who currently own workbooks in the database:

SELECT DISTINCT

USERS.USER_NAME APPS_USER,

DISCO_USERS.EU_USERNAME,

DISCO_USERS.EU_ID,

DISCO_DOCS.DOC_CREATED_BY

FROM

EUL5_DOCUMENTS DISCO_DOCS,

EUL5_EUL_USERS DISCO_USERS,

APPS.FND_USER USERS

WHERE

SUBSTR(DISCO_DOCS.DOC_CREATED_BY, 2)) = USERS.USER_ID AND

DISCO_USERS.EU_ID = DISCO_DOCS.DOC_EU_ID;

Pick the Apps user you want to log in. Make a note of the DISCO_USERS.EU_ID. You will need this later.

Note: the reason I suggest using someone who already owns workbooks is this saves you from having to create an entry in the EUL5_EUL_USERS table. All users who own workbooks have an entry in this table.

Step 4:

Now let's check whether the original owner shared any workbooks. These will need deleting too.

First let's check if any sharing has been done.

SELECT COUNT(*) FROM

EUL5_DOCUMENTS, EUL5_ACCESS_PRIVS

WHERE

EUL5_DOCUMENTS.DOC_CREATED_BY = 'eul_owner' AND

EUL5_DOCUMENTS.DOC_ID = EUL5_ACCESS_PRIVS.GD_DOC_ID;

Once again replace eul_owner with real owner name. If this returns zero then no sharing has taken place.

If this returns a number greater than zero then we need to note that these records have to be removed. We'll let the system do it for us and recheck later. For now make a note of the number of shares.

Step 5:

Next we'll update the owner of the workbook(s) to the DISCO_ID you figured out earlier.

UPDATE EUL5_DOCUMENTS

SET DOC_EU_ID = the_ID_from_step_3

WHERE DOC_CREATED_BY = 'eul_owner';

You should get the same number updated as you got earlier when you did the count in Step 2. If this is true then COMMIT the change.

Step 6:

Now log in to Desktop as the Apps user that you picked and delete the workbooks. When you log in you will be informed that the workbook was created under another account and asked if you want to open it under that account or the account you are logged in as. You should open it using the account you are logged in as.

Use Desktop's normal delete mechanism for deleting the workbooks. From the toolbar use File | Manage Workbooks | Delete. You should see and delete the same workbooks you saw in Steps 1 and 2

Step 7:

Having deleted the workbooks, if there were any shares, let's make sure that all of these have gone too. You will need the DOC_EU_ID I told you to take a note of in Step 1.

SELECT COUNT(*) FROM

EUL5_ACCESS_PRIVS

WHERE

EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND

EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;

If this now returns zero then all sharing has been removed.

If this still returns a number greater than zero then we need to manually delete those records.

Before you do this, for safety take a backup of EUL5_ACCESS_PRIVS then run this:

DELETE FROM EUL5_ACCESS_PRIVS

WHERE

EUL5_ACCESS_PRIVS.AP_TYPE = 'GD' AND

EUL5_ACCESS_PRIVS.AP_EU_ID = SAVED_DOC_EU_ID;

You should get a message that X rows have been deleted. If this count equals what you got earlier a few moments ago then COMMIT the delete. If you get a different count then use ROLLBACK and recheck your code.