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

Products

• Report Facility

Issue/Introduction

There are Report Facility queries at our site that were created by a user who is no longer with the company.
Would like to have another user have the authorization to update and execute these PRF queries.

Resolution

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') ;