1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| select
co.dw_co_id, co.MSISDN, cs.CS_FNAME ,cs.CS_LNAME ,cs.ADDRESS,cs.CARD_NUM,cs.CARD_TYPE,
CITY,CUSTCODE,co.co_id,tt.DESCRIPTION TARIFF_PLAN, trunc(co.FIRST_CALL_DATE)FIRST_CALL_DATE,
s.STATUS_DESC status,co.LAST_BALANCE,day LAST_CALL,
cs.CS_NAME NAME,BIRTHDATE,co.current_msisdn,cs.cust_activ_day_id
from
(SELECT ff.*,nvl(ff.dw_cur_tarif_index_id,-99) dw_tarif_index_id_lookup,
nvl(case when ff.dw_in_cur_tariff_plan_id =1061 and ff.kit_code like'2020001%' then dw_in_cur_tariff_plan_id
when t.status_in ='Y' then dw_in_cur_tariff_plan_id else ff.dw_cur_tariff_plan_id end ,-99) dw_tariff_plan_id_lookup
FROM DWHPROD.dw_contract_dim ff ,DWHPROD.dw_tariff_plans_dim t
WHERE
ff.first_call_day_id >0
and ff.dw_status_id in(2,3,4,8)
and used=1
and dw_in_cur_tariff_plan_id=t.dw_tariff_plan_id (+)
) co,
DWHPROD.dw_tariff_plans_dim tt,
DWHPROD.dw_tarif_index_dim m,
DWHPROD.dw_customer_dim cs,
DWHPROD.dw_status_dim s,
DWHPROD.dw_days d
where
co.dw_tariff_plan_id_lookup=tt.dw_tariff_plan_id and m.dw_tarif_index_id =co.dw_tarif_index_id_lookup
and tt.revenue_producing='Y'
and (tt.POST_PAID='Y' or m.POST_PAID='Y')
and s.DW_STATUS_ID=co.DW_STATUS_ID
and d.DW_DAY_ID=nvl(co.LAST_CALL_DAY_ID,-99)
and cs.dw_customer_id = co.dw_cust_id
and cs.dummy_customer='N'; |
Partager