The idea for creating Query Pages came from here.
UPDATE: Ramsey County has agreed (quite enthusiastically) to post the detail design document for this process. You can download it here.
The client I am working for, Ramsey County, wanted to control the information managers saw in Managers Self Service. They did not want to modify the delivered pages, but we were running low on available development hours so creating custom pages wasn't much of an option. Thanks to the idea above, we could create Queries and make them quick to access.
The idea of query pages was expanded to dynamically grab the current user’s OPRID and EMPLD so there would no Prompts for the user. This would allow the user to only see the information tied directly to the person logged in. Ramsey County also wanted to use the REPORTS_TO field for Query security. When Managers ran the Query, it would return information for people who reported directly to them and no one else.
To accomplish the first requirement (grab the current user’s OPRID and EMPLID), the table PSQRYTRANS is used. When a query is run, a row is inserted into PSQRYTRANS that contains the Query Name, the User ID and the Start Time. A new view was created to grab the OPRID and QRYNAME from the PSQRYTRANS table and return a single row based on the Query Name and the Start Time of the query.
Next, the OPRID is joined to the PSOPRDEFN table to grab the current user’s EMPLID. Then, the EMPLID is joined with the view PS_EMPL_CURRJOB_VW to grab the current user’s POSITION_NBR. Finally, the POSITION_NBR is joined to the view PS_EMPL_INFO_VW to return a list of EMPLIDs of users whose REPORTS_TO field matches the POSITION_NBR.
They query in this document is the starting point for all Manager Queries. Because this query returns just them EMPLID of direct reports, it can be expanded to grab any information that includes EMPLID.
First, I created a new view to return the information from PSQRYTRANS. The SQL I wanted to run is not possible in PS/Query, so a custom view was created. Here is the SQL for the new view RC_QRY_OPRID_VW.
SELECT %FirstRows(1) USERID , QRYNAME FROM PSQRYTRANS ORDER BY QRYSTARTTIME
Note the following: %FirstRows(1) is essential so the subquery does not return two rows if two people happen to running the same query at the same time; the ORDER BY is by the Query Start Time, so that the row returned is the most recent person to kick off the query.
Make sure to add the new view to a Query Tree so that it is available to PS/Query.
Next, create the query in PS/Query. The Query is saved as RC_H_HR_MANAGERSELFSERVICE and is a public query. Here is the generated SQL:
SELECT a.name
FROM ps_names a,
ps_pers_srch_qry a1
WHERE a.emplid = a1.emplid
AND a1.oprid = 'DANIEL.IVERSON'
AND(a.effdt =
(SELECT MAX(a_ed.effdt)
FROM ps_names a_ed
WHERE a.emplid = a_ed.emplid
AND a.name_type = a_ed.name_type)
AND a.emplid IN
(SELECT b.emplid
FROM ps_empl_info_vw b,
ps_empl_currjob_vw c,
ps_empl_info_vw d
WHERE b.emplid = c.emplid
AND b.empl_rcd = c.empl_rcd
AND c.reports_to = d.position_nbr
AND d.emplid =
(SELECT e.emplid
FROM psoprdefn e
WHERE e.oprid =
(SELECT f.oprid
FROM ps_rc_qry_oprid_vw f
WHERE f.qryname = 'RC_H_HR_MANAGERSELFSERVICE')
)
)
)
The query above is a template for create more queries. The criteria at the very bottom must be changed; it is used to ensure that the OPRID returned from the custom view is for the correct query. The first SELECT & FROM values can be changed to select information that managers will need to see about their direct reports. The template query above just selects the direct reports' names.
Once the query is complete, use the Query Page idea from the link above to create content reference and add security.
Recent Comments