AnsweredAssumed Answered

Review escalation description table

Question asked by Boleslaw Mynarski on Dec 11, 2019
Latest reply on Dec 11, 2019 by Boleslaw Mynarski

Like • Show 0 Likes0
Comment • 5

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.

Mostafa Helmy

Dec 11, 2019 2:46 PM

Correct Answer

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

See the reply in context

No one else had this question

Outcomes

Jamie Pryer

Dec 11, 2019 2:40 PM

hey!

i need some time to look as im not usre, however not sure if you saw my blog, which has a helpful tip about finding tables you might need, using the following.

Reports, Charts & Dashboards - What are they and how can you build them?!

select * from user_tables
where lower(table_name) like '%%'
order by table_name asc;

select * from user_views
where lower(view_name) like '%%'
order by view_name asc;

Actions

Boleslaw Mynarski @ Jamie Pryer on Dec 11, 2019 8:22 PM

Oracle SQL Developer is your friend. No need for this approach unless you have access only to sqlplus, then, you are for some real pain... :-)

Actions

Mostafa Helmy

Dec 11, 2019 2:46 PM

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

2 people found this helpful

Actions

Boleslaw Mynarski @ Mostafa Helmy on Dec 11, 2019 7:24 PM

After review is generated, it's parked as "Reviews -> Results". I don't care how many times it was escalated. I care about the name of the escalation used as per the escalation tab. What I am referring to is an escalation name (I thought it was clear from my post but maybe it most more clearer in head than on paper. :-) ).

I want to extract the information highlighted in red. It sits somewhere as this is a review escalation workflow name.

Actions

Boleslaw Mynarski @ Mostafa Helmy on Dec 11, 2019 7:34 PM

If I could up-vote your post, I would. You gave me the info I needed (wp_proc table). Thank you!

Actions

5 Replies

Jamie Pryer Dec 11, 2019 2:40 PM
Mark Correct
Correct Answer
hey!
i need some time to look as im not usre, however not sure if you saw my blog, which has a helpful tip about finding tables you might need, using the following.

Reports, Charts & Dashboards - What are they and how can you build them?!
```
select * from user_tables
where lower(table_name) like '%%'
order by table_name asc;

select * from user_views
where lower(view_name) like '%%'
order by view_name asc;
```
Like • Show 0 Likes0
Actions
- Boleslaw Mynarski @ Jamie Pryer on Dec 11, 2019 8:22 PM
  Mark Correct
  Correct Answer
  Oracle SQL Developer is your friend. No need for this approach unless you have access only to sqlplus, then, you are for some real pain... :-)
  Like • Show 0 Likes0
  Actions
Mostafa Helmy Dec 11, 2019 2:46 PM
Unmark Correct
Correct Answer
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;

2 people found this helpful
Like • Show 2 Likes2
Actions
- Boleslaw Mynarski @ Mostafa Helmy on Dec 11, 2019 7:24 PM
  Mark Correct
  Correct Answer
  After review is generated, it's parked as "Reviews -> Results". I don't care how many times it was escalated. I care about the name of the escalation used as per the escalation tab. What I am referring to is an escalation name (I thought it was clear from my post but maybe it most more clearer in head than on paper. :-) ).
  
  I want to extract the information highlighted in red. It sits somewhere as this is a review escalation workflow name.
  Like • Show 0 Likes0
  Actions
- Boleslaw Mynarski @ Mostafa Helmy on Dec 11, 2019 7:34 PM
  Mark Correct
  Correct Answer
  If I could up-vote your post, I would. You gave me the info I needed (wp_proc table). Thank you!
  Like • Show 0 Likes0
  Actions

Review escalation description table

Outcomes

Related Content

Recommended Content