MATERIALIZED VIEW: APPS.AMS_ACT_METRICS_MONTHLY_MV

Source

select
m1.month_id time_id,
m1.start_date start_date,
m1.end_date end_date,
h2.arc_act_metric_used_by object_type,
h2.act_metric_used_by_id object_id,
h2.func_actual_value metric_value,
h2.metric_id metric_id
from
(
select
m2.month_id,
min(m2.start_date) start_date,
max(m2.end_date) end_date
from
ozf_time_day m2
group by m2.month_id
) m1,
ams_act_metric_hst h2
where m1.start_date < sysdate
and m1.end_date > (sysdate - 180)
and h2.last_update_date <= m1.end_date
and h2.last_update_date >= m1.start_date
and
h2.act_met_hst_id =
(select
  max(h1.act_met_hst_id)
  from ams_act_metric_hst h1
  where
  h1.last_update_date <= m1.end_date
  and h1.last_update_date >= m1.start_date
  and h1.metric_id = h2.metric_id
  and h1.act_metric_used_by_id = h2.act_metric_used_by_id
  and h1.arc_act_metric_used_by = h2.arc_act_metric_used_by
)
and h2.metric_id in (
---Total Leads
81
--- Total Responses
,161
-- A Leads Count
,290
---Dead Leads
,260
-- Leads Accepted
,270
 -- Opportunities
,91
 -- Quotes Count
,240
-- Quote Amount
,250
-- Orders Count
,151
-- Orders Amount
,101
-- Booked Revenue
,239
-- Invoiced revenue
,223
-- Cost
,56
-- Target Group Size
,300
-- Contact Group Size
,309
)