create or replace view V8_STATS_rfm_rescount as
select xcms_id,(select count(1) from yres where yres_xcms_id=xcms_id and yres_resstatus<>3 and yres_actdeptime is not null and yres_expdeptime>=(select * from fideliodate)-(select wuss_value from wuss where wuss_name='RFMHistoryDays')) rescount
from xcms;

create or replace view V8_STATS_rfm_lastdep as
select xcms_id,(select max(trunc(yres_actdeptime)-(select * from fideliodate)) from yres where yres_xcms_id=xcms_id and yres_expdeptime>=(select * from fideliodate)-(select wuss_value from wuss where wuss_name='RFMHistoryDays')) lastdep
from xcms;

create or replace view V8_STATS_rfm_revenue as
select zpos_originated_xcms_id xcms_id,sum(nvl(zpos_sumupamount,0)) revenue 
from zpos 
where zpos_cdt=1
and zpos_postdate>=(select * from fideliodate)-(select wuss_value from wuss where wuss_name='RFMHistoryDays')
group by zpos_originated_xcms_id;

create or replace view V8_STATS_rfm_rescount_points as 
select xcms_id,rescount,6-round((1.1-(rank () over ( order by rescount ))/(count(1) over ()))/.2) point
--i need order by without desc to have the high number of people right in the beginning otherwise everybody has the same rank because otherwise the last big group goes to the end and no new rank is opened for them
--6- compensates for this reverse order   
from V8_STATS_rfm_rescount
where nvl(rescount,0)>0;

create or replace view V8_STATS_rfm_lastdep_points as 
select xcms_id,lastdep,round((1.1-(rank () over ( order by lastdep desc ))/(count(1) over ()))/.2) point 
from V8_STATS_rfm_lastdep
where lastdep is not null;

create or replace view V8_STATS_rfm_revenue_points as 
select xcms_id,revenue,round((1.1-(rank () over ( order by revenue desc ))/(count(1) over ()))/.2) point 
from V8_STATS_rfm_revenue
where revenue<>0;


create or replace procedure v8_store_rfm_points AUTHID CURRENT_USER is
begin

  begin
     execute immediate 'drop table v8_rfm_rescount_points';
  exception when others then null;   end; 
  execute immediate 'create table v8_rfm_rescount_points as select * from V8_STATS_rfm_rescount_points';
  execute immediate 'create index v8_rfm_rescount_points001 on v8_rfm_rescount_points(xcms_id) tablespace v8index';


  begin
     execute immediate 'drop table v8_rfm_lastdep_points';
  exception when others then null;   end; 
  execute immediate 'create table v8_rfm_lastdep_points as select * from V8_STATS_rfm_lastdep_points';
  execute immediate 'create index v8_rfm_lastdep_points001 on v8_rfm_lastdep_points(xcms_id) tablespace v8index';
  
  begin
     execute immediate 'drop table v8_rfm_revenue_points';
  exception when others then null;   end; 
  execute immediate 'create table v8_rfm_revenue_points as select * from V8_STATS_rfm_revenue_points';
  execute immediate 'create index v8_rfm_revenue_points001 on v8_rfm_revenue_points(xcms_id) tablespace v8index';  


end v8_store_rfm_points;
/  
