Due to other events related to monitors of our reviews, we have to start tracking when review escalations start disappearing from review results. The following gives me 99% of all I want but I want to get the remaining 1%:
SELECT
rm.name AS review_name,
case
when re.workflow_id in ('acm$reviewrevoke') then 'Mark As Revoke'
else re.workflow_id
end as review_workflow,
re.reviewer_constraint as review_escalation,
re.num_days as days_into_review,
re.from_startdate,
rm.creation_date as review_create_date,
rm.end_date as review_end_date
FROM avuser.t_av_reviews_mst rm
left JOIN avuser.t_av_review_escalations re
ON rm.id = re.review_id
AND rm.is_active = 'Y'
WHERE rm.state NOT IN ('C')
order by rm.name;
Question: which table contains review escalation description??? I've been looking high and low and I cannot find any table/view that would give me a hint on what table stores it.
I checked t_av_review_escalations and it does not have any foreign keys linking with another table that would give me more information about the escalation. Where can I find this info?
Thanks.
Could you elaborate a bit more on the escalation description part? Do you mean which workflow it calls or how many times it has run?
I found this SQL statement in my notes which I previously used to list all escalation workflows for a single review. You can probably build on it to get any extra details needed.
SELECT
rvw.name review_name,
rvw.end_date,
rvesc.from_startdate,
rvesc.num_days,
rvesc.workflow_id,
escwf.name workflow_name,
extractvalue(xmltype(schtk.rule),'//ruleStartDate') escalation_date
FROM
avuser.t_av_reviews rvw
JOIN avuser.t_av_review_escalations rvesc ON rvw.id = rvesc.review_id
JOIN avuser.wp_proc escwf ON rvesc.workflow_id = escwf.proc_ref
JOIN avuser.t_scheduled_tasks schtk ON rvesc.schedule_task_id = schtk.id
--where rvw.id=1
ORDER BY
1,
7,
6;