select des_bezeq,
vip.bezeq_cust_rec_id,
vip.bezeq_cust_phone_area || vip.bezeq_cust_phone_no orange,
vip.bezeq_cust_first_name || ' ' || vip.bezeq_cust_last_name name,
vip.bezeq_cust_id,
pr.custcode,
cc.ccstreet || ', ' || cc.ccstreetno || ', ' || cc.cczip address,
cc.cccity city,
des_bezeq,
MPU.PRM_VALUE_DES BUNDLE_DOWNLOAD_DES,
MPD.PRM_VALUE_DES BUNDLE_UPLOAD_DES,
bezeq_start_date,
bezeq_end_date,
to_char(to_date('01/01/13', 'dd/mm/rr'), 'dd/mm/yy') rep_from,
to_char(to_date('01/05/13', 'dd/mm/rr'), 'dd/mm/yy') rep_to,
(to_date('01/05/13','dd/mm/rr') - to_date('01/01/13','dd/mm/rr') +1) day_count,
(rep_to_date - rep_from_date + 1) day_count,
round(bezeq_amount + orange_amount, 3) month_amount,
round(bezeq_amount, 3) bezeq_month_amount,
round(actual_bezeq_amount, 3) actual_bezeq_amount
from (select co.co_id,
co.customer_id,
co.custcode,
co.rep_from_date,
co.rep_to_date,
co.sncode_bezeq,
kv_bezeq.des des_bezeq,
kv_bezeq.accessfee bezeq_amount,
decode(sign(co.bezeq_end_date -
to_date('01/01/2099', 'dd/mm/yyyy')),
1,
to_date(null),
co.bezeq_end_date) bezeq_end_date,
co.bezeq_status,
co.bezeq_prm_value_id,
co.bezeq_profile_id,
co.bezeq_spcode,
pv_bezeq.prm_value_number bezeq_prm_value_number,
pv_bezeq.prm_description bezeq_prm_description,
pv_bezeq.prm_seqno bezeq_prm_seqno,
pv_bezeq.prm_valid_from bezeq_param_start_date
pv_bezeq.parameter_id order by pv_bezeq.co_id, pv_bezeq.parameter_id, pv_bezeq.prm_valid_from) bezeq_param_end_date,
co.sncode_orange,
kv_orange.des des_orange,
kv_orange.accessfee orange_amount,
co.orange_start_date,
co.orange_end_date,
co.orange_status,
co.orange_prm_value_id,
co.orange_profile_id,
co.orange_spcode,
pv_orange.prm_value_number orange_prm_value_number,
pv_orange.prm_description orange_prm_description,
pv_orange.prm_seqno orange_prm_seqno,
pv_orange.prm_valid_from orange_param_start_date,
pv_orange.parameter_id order by pv_orange.co_id, pv_orange.parameter_id, pv_orange.prm_valid_from) orange_param_end_date,
co.tmcode
from (select sh.co_id,
sh.customer_id,
sh.custcode,
sh.sncode_bezeq,
sh.sncode_orange,
sh.tmcode,
sh.bezeq_spcode,
sh.bezeq_status,
sh.bezeq_start_date,
sh.bezeq_end_date,
sh.bezeq_prm_value_id,
sh.bezeq_profile_id,
sh.orange_spcode,
sh.orange_status,
sh.orange_start_date,
sh.orange_end_date,
sh.orange_prm_value_id,
sh.orange_profile_id,
trunc(greatest(to_date('01/01/10', 'dd/mm/rr'),
bezeq_start_date)) rep_from_date,
trunc(least(to_date('01/05/10', 'dd/mm/rr'),
bezeq_end_date)) rep_to_date
from (select bezeq.co_id,
bezeq.customer_id,
bezeq.custcode,
sncode_bezeq,
tmcode,
bezeq_spcode,
bezeq_status,
bezeq_start_date,
bezeq_end_date,
bezeq_prm_value_id,
bezeq_profile_id,
sn_orange.sncode sncode_orange,
psh_orange.spcode orange_spcode,
ph_orange.status orange_status,
ph_orange.valid_from_date orange_start_date
order by ph_orange.histno) orange_end_date,
ps_orange.prm_value_id orange_prm_value_id,
ps_orange.profile_id orange_profile_id
from (select ca.co_id,
ca.customer_id,
cu.custcode,
m.sncode sncode_bezeq,
ca.tmcode,
psh_bezeq.spcode bezeq_spcode,
ph_bezeq.status bezeq_status,
ph_bezeq.valid_from_date bezeq_start_date
order by ph_bezeq.histno) bezeq_end_date,
ps_bezeq.prm_value_id bezeq_prm_value_id,
ps_bezeq.profile_id bezeq_profile_id,
bf_bezeq.pack_id,
sn_bezeq.shdes bezeq_shdes
from (SELECT distinct (m.sncode)
FROM mpulktmb m
WHERE m.spcode = 595
AND m.tmcode in
(select t.tmcode
from rateplan_mapping t
where t.market = 15)
AND m.vscode =
(select max(m2.vscode)
from rateplan_version m2
where m2.tmcode = m.tmcode
and m2.status = 'P')) m,
pr_serv_status_hist ph_bezeq,
profile_service ps_bezeq,
contract_all ca,
customer_all cu,
bf_packs_components bf_bezeq,
mpusntab sn_bezeq,
pr_serv_spcode_hist psh_bezeq
where ps_bezeq.sncode = m.sncode
and ps_bezeq.co_id = ph_bezeq.co_id
and ps_bezeq.sncode = ph_bezeq.sncode
and ps_bezeq.profile_id =
ph_bezeq.profile_id
and ps_bezeq.co_id = ca.co_id
and ca.SCCODE = 15
and cu.prgcode <> 12
and cu.customer_id = ca.customer_id
and psh_bezeq.co_id = ps_bezeq.co_id
and psh_bezeq.sncode = ps_bezeq.sncode
and psh_bezeq.histno =
ps_bezeq.spcode_histno
and psh_bezeq.profile_id =
ps_bezeq.profile_id
and psh_bezeq.sncode = sn_bezeq.sncode
and ps_bezeq.profile_id =
ph_bezeq.profile_id
and bf_bezeq.entity_shdes = sn_bezeq.shdes) bezeq,
pr_serv_status_hist ph_orange,
profile_service ps_orange,
bf_packs_components bf_orange,
mpusntab sn_orange,
pr_serv_spcode_hist psh_orange
where bf_orange.pack_id = bezeq.pack_id
and bf_orange.entity_shdes <> bezeq.bezeq_shdes
and bf_orange.entity_shdes = sn_orange.shdes
and psh_orange.histno = ps_orange.spcode_histno
and psh_orange.profile_id = ps_orange.profile_id) sh
where sh.bezeq_status = 'A'
and sh.bezeq_end_date >= to_date('01/01/13', 'dd/mm/rr')
and sh.bezeq_start_date <= to_date('01/05/13', 'dd/mm/rr')
and sh.orange_status = 'A'
and sh.orange_end_date >= sh.bezeq_start_date
and sh.orange_end_date <= sh.bezeq_end_date) co,
parameter_value pv_bezeq,
mpulktmb kt_bezeq,
mpulkpvm kp_bezeq,
mpulkpvb kv_bezeq,
parameter_value pv_orange,
mpulktmb kt_orange,
mpulkpvm kp_orange,
mpulkpvb kv_orange
where pv_bezeq.co_id = co.co_id
and pv_bezeq.sncode = co.sncode_bezeq
and pv_bezeq.prm_value_id = co.bezeq_prm_value_id
and pv_bezeq.profile_id = co.bezeq_profile_id
and pv_bezeq.parameter_id = 6
and kt_bezeq.tmcode = co.tmcode
and kt_bezeq.spcode = co.bezeq_spcode
and kt_orange.spcode = co.orange_spcode
and kt_orange.sncode = co.sncode_orange
and kt_orange.pv_combi_id = kp_orange.pv_combi_id
and kt_orange.vscode = kp_orange.vscode
and kp_orange.sccode = 15
and kv_orange.pv_combi_id = kp_orange.pv_combi_id
and kv_orange.vscode = kp_orange.vscode
and kv_orange.set_id = kp_orange.set_id
and kp_orange.prm_value_string = pv_orange.prm_description) pr,
PT_TABLES@porto PTT,
PT_TABLES_CODE@porto PTC,
pt_billcodes_for_services PBS,
sw_val_service@porto SVS,
sw_component@porto SCT,
sw_component@porto SCU,
sw_component@porto SCD,
mkt_parameter_domain MPT,
mkt_parameter_domain MPD,
mkt_parameter_domain MPU,
info_contr_vip vip,
ccontact_all cc
where pr.bezeq_param_end_date >= pr.rep_from_date
and pr.bezeq_param_start_date < pr.rep_to_date + 1
and pr.orange_param_end_date >= pr.orange_start_date - 1 / (24 * 60)
and pr.orange_param_start_date <= pr.orange_end_date + 1 / (24 * 60)
and pr.bezeq_param_start_date < pr.rep_to_date + 1
and pr.orange_param_end_date >= pr.orange_start_date - 1 / (24 * 60)
and pr.orange_param_start_date <= pr.orange_end_date + 1 / (24 * 60)
and PTT.PTTABLEID = PTC.PTTABLEID
and PBS.swvalserviceid = SVS.swvalserviceid
and PTC.PTTABLEID = 3963
and PBs.PTBILLINGCODE = pr.sncode_orange
and MPT.sccode = 15
and MPD.sccode = 15
and MPU.sccode = 15
and vip.co_id = pr.co_id
and cc.customer_id = pr.customer_id
and cc.ccbill = 'X'
and cc.ccseq > 0
order by vip.bezeq_cust_rec_id, pr.co_id, bezeq_start_date
