TinaSchulze (Customer) asked a question.
Need to connect an approver to what they approved in a report. I am missing the connection between the specific item that was was approved & who approved it.
I have info from PV_CHANGE_REQUEST_WORK_ITEM, PV_CHANGE_REQUEST_WORK_USER & V_AV_WFWORKITEM_USERS along with T_AV_CHANGE_REQUEST_DETAILS and can not find a connection from the t_av_change_request_details.value_name to the pv_change_request_work_item.activity_instance_id . I am connecting to every approver on the request and need it to be specific.
Try this:
SELECT
wi.cr_id,
wi.changerequestname AS change_request_name,
wi.name,
CASE
WHEN wi.button_selected IS NULL THEN
'Pending Action'
ELSE
wi.button_selected
END AS action,
wi.due_date,
wi.complete_date,
wi.work_type,
u.user_id AS workitem_assigned,
u1.user_id AS complete_by
FROM
avuser.v_av_wfworkitems wi
JOIN avuser.v_av_wfworkitem_users wu ON wi.acti_id = wu.acti_id
AND wi.acti_db = wu.acti_db
AND wi.wi_iteration = wu.wi_iteration
LEFT JOIN avuser.pv_users u ON u.id = wu.resource_id
LEFT JOIN avuser.pv_users u1 ON u1.id = wi.complete_by
WHERE
wi.changerequestname IS NOT NULL
how do you connect this to the change request detail so we know which person approved which thing?
Did you try the SQL I've provided?
It lists the change request and who approved it.
Yes I did use your SQL and yes it does connect the request and the approvers. Unfortunately, that isn't my question. My request has multiple roles requested on it (as do most of our requests). As I said originally I need the approver connected to what they approved. So the request has 5 roles all with different approvers, I need to connect role #3 to the approver of role #3. The SQL you provided connects the request to all the approvers so for each of the 5 roles, 5 approvers are returned. I can't connect the current role approver to the completed by because I can't insure that the approver from 3 months ago is the current approver of the role. During the request process, each approver only acts on what they are responsible for, I need that for this audit report. If it helps, I do have a case open (02615555) but was told to come here to ask for assistance.
@TimWillemstein2 (Customer) @OverthinkerDave (Customer) - do you have by any chance a similar query that you can share?
I'm using this view for these scenario's: pv_change_request_activity
It has information on the change items and who completed the approval for them.
I have a sql query we use for audit of "who approved what, and for what"... and even on-behalf included etc... who should have approved... which source... jada jada.
But this sql query is a monster. Think it comes down to maybe 100lines of SQL code.
And if your database is to big, it will fail due to "big kardinality" (or in other words take to long)
In practice we use it to dump the latest day of approvals... and then create the report in Kibana.
I can't find my english-version of this document, but my "monster sql query" gives the following resulting columns (not all are self-explanatory, because we have extended the role object with some extended approval policy logic):
So is something like that useful @TinaSchulze (Customer) ?