So let us discuss building a custom review report using the Aveksa (ACM) product.

This custom report will:

1. Generate a review report that will show who the reviewer is that generated the review.
2. Show who the reviewee is
3. Show the status of the review
4. Show the entitlement that is associated to the reviewee
5. Show comments about the review.
6. 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)