So let us discuss building a custom review report using the Aveksa (ACM) product.
This custom report will:
- Generate a review report that will show who the reviewer is that generated the review.
- Show who the reviewee is
- Show the status of the review
- Show the entitlement that is associated to the reviewee
- Show comments about the review.
- Show information about a group review.
The views used in the query are as follows:
pv_users – contains information about the (users) reviewer and reviewee, such as their first and last names.
pv_review – contains information about the review such as the review ID, state, type and name
pv_review_component – contains information about the review components
pv_unified_entitlement – contains information about the entitlements users that are being review for
The following query will generate the review report for our customer:
(select Distinct
users.first_name Reviewee_Fname,
users.last_name Reviewee_Lname,
users.user_id,
rc.component_name,
rc.review_id,
rc.reviewee_id,
rc.review_item_id,
r.name,
users_r.First_name Reviewer_Fname,
users_r.Last_Name Reviewer_Lname,
e.resource_name,rcc.comments,
CASE WHEN rc.STATE = ‘R’ THEN ‘Revoked’ ELSE ‘Approved’ END AS Status,
CASE WHEN r.review_type = ‘G’ THEN ‘Group’ ELSE ‘Entitlement’ END AS Type
from
avuser.pv_users users,
avuser.pv_review_component rc,
avuser.pv_review r,
avuser.pv_review_component_change rcc,
avuser.pv_users users_r,
avuser.pv_unified_entitlement e
where
rc.component_name=users.user_id
and rcc.reviewer=users_r.id
and rc.review_item_id=e.id)