How do I authorize a User to update and execute another User's queries in Report Facility?

Document ID : KB000049974
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

There are queries at our site that were created by a User who is no longer with the company. I would like to have another User have the authorization to update and execute these queries.

Solution:

The SHR method for a query must be set to U.

This parameter indicates the authorization within Report Facility for others to use and/or update the object.

Valid parameters are:
Y  =  others can use  -  but not update  -  your object.
U  =  others can use  -  and update  -  your object.
N  =  others cannot reference or copy your object.
        You will not see objects created by others with a Share option of N.

This information is stored in the PTPRF_LIB_ver table. The specific column name is PRF_SHARE_CODE.

The following query can be used to identify the queries and forms that require their share code to be updated.

SELECT PRF_ID, PRF_TYPE, PRF_NAME, PRF_SHARE_CODE
FROM PTI.PTPRF_LIB_ver
WHERE PRF_ID - 'xxxxxxx'         <==== user ID no longer available
AND PRF_TYPE IN ('QERY', 'FORM')
AND PRF_SHARE_CODE IN ('N', 'Y');

Once these queries and forms are identified, then the Share code can be updated with the following SQL.

UPDATE PTI.PTPRF_LIB_ver
SET PTF_SHARE_CODE = 'U'
WHERE PRF_ID = 'xxxxxxxx'        <==== user ID no longer available
AND PRF_TYPE IN ('QERY', 'FORM')
AND PRF_SHARE_CODE IN ('N', 'Y') ;