MATERIALIZED VIEW: APPS.AMS_ACT_METRICS_WEEKLY_MV

Source

select
  w1.week_id time_id,
  w1.start_date start_date,
  w1.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
  ozf_time_week w1,
  ams_act_metric_hst h2
  where
  w1.start_date < sysdate
  and w1.end_date > (sysdate - 31)
  and h2.last_update_date <= w1.end_date
  and h2.last_update_date >= w1.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 <= w1.end_date
  and h1.last_update_date >= w1.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
  )