· 8 years ago · Nov 29, 2017, 04:46 PM
1create or replace function x_crm_pmr_products(pn_partner_id integer, pd_from date, pd_to date) return x_crm_pmr_product_data_t pipelined is
2 --cache
3 vn_suspendcontract_dic number;
4 --variables
5 vv_rateplan varchar2(255);
6 vv_startdate varchar2(16);
7 vv_enddate varchar2(16);
8 vv_bindingstartdate varchar2(16);
9 vv_bindingenddate varchar2(16);
10 vt_bindingenddate timestamp;
11 vv_product varchar2(255);
12 --temp variables
13 vn_tariff_id number;
14 vv_mno_code VARCHAR2(10);
15 vn_mno_id NUMBER;
16 vv_sql VARCHAR2(32000);
17 vt_date_to DATE := trunc(pd_to+1, 'dd');
18 vv_date_to varchar2(16) := to_char(vt_date_to, 'yy/mm/dd');
19 vt_date_from date := trunc(pd_from+1, 'dd');
20 vv_date_from varchar2(16) := to_char(vt_date_from, 'yy/mm/dd');
21 vt_start_date timestamp;
22
23 gv_debug CONSTANT BOOLEAN := false;
24
25 TYPE r_data IS record (
26 contract_item_id cdm_contract_items_tg.contract_item_id%type,
27 product_id cdm_contract_items_tg.product_id%type,
28 tariff_plan_variant_id cdm_contract_items_tg.tariff_plan_variant_id%type,
29 dic_status_id cdm_contract_items_tg.dic_status_id%TYPE,
30 contracted_to cdm_contract_items_tg.contracted_to%TYPE,
31 suspend_date etl_citems_status_hist_t.last_suspend_date%type,
32 unsuspend_date etl_citems_status_hist_t.last_unsuspend_date%type,
33 start_date timestamp,
34 binding_period cdm_contract_item_attr_ins_tg.date_value%type,
35 roots_status cdm_contract_items_tg.dic_status_id%type,
36 msisdn varchar2(20)
37 );
38 type t_data is table of r_data;
39
40 vr_data r_data;
41 vt_data t_data;
42
43 TYPE pmr_cur_t IS REF CURSOR;
44 c_select pmr_cur_t;
45
46 function get_formatted_date(pt_timestamp timestamp, pb_mandatory char) return varchar2 is
47 vv_returnstring varchar2(16);
48 begin
49 if pb_mandatory = 'Y' and pt_timestamp is null then
50 raise_application_error(-20000, '[X_CRM_PMR_GET_FORMATTED_DATE] Date is mandatory',false);
51 elsif pt_timestamp is not null then
52 vv_returnstring := to_char(pt_timestamp, 'YYYYMMDDHH24MISS');
53 else
54 return null;
55 end if;
56 return vv_returnstring;
57 end;
58
59 BEGIN
60 etl_loader.etl_init_for_report('PMR', pd_to);
61 SELECT ID INTO vn_suspendcontract_dic FROM cfg_dictionaries_t WHERE code = 'CONTRACT_ITEM_STATUS.SUSPEND';
62 select (select mno.mno_code from x_crm_mnos mno where mno.mno_id = tree.mno_id), tree.mno_id into vv_mno_code, vn_mno_id
63 FROM cdm_party_role_tree_v tree WHERE tree.party_role_id = pn_partner_id;
64 vv_sql := 'with
65 prod_tmp as (select /*+ materialize */ p.id from pc_products_t p
66 where p.is_root = 0
67 and p.type = ''S''
68 and exists(select 1 from pc_product_attr_ins_t bill
69 where bill.product_id = p.id
70 and bill.attribute_code = ''PRODUCT_ADDITIONAL_ATTRIBUTE.RELEVANT_FOR_BILLING''
71 and bill.boolean_value = 1)
72 AND NOT EXISTS (SELECT 1 FROM pc_packaged_products_t
73 where parent_product_id = p.id))
74 SELECT /*+ opt_param(''optimizer_features_enable'',''11.2.0.4'') */
75 citems.contract_item_id,
76 case
77 when simcontract.owning_party_id<>'||pn_partner_id||' and simcontract.party_role_Id<>'||pn_partner_id||'
78 then intermno_mapping.provider_prod_id
79 ELSE citems.product_id
80 end as product_id,
81 case
82 when simcontract.owning_party_id='||pn_partner_id||' and simcontract.owning_party_id<>'||vn_mno_id||'
83 then citemattr.number_value
84 when simcontract.owning_party_id<>'||pn_partner_id||' and simcontract.party_role_Id<>'||pn_partner_id||'
85 then intermno_attr.number_value
86 else citems.tariff_plan_variant_id
87 end as tariff_plan_variant_id,
88 citems.dic_status_id,
89 citems.contracted_to,
90 nvl(to_date(hist_changestatus.last_suspend_date + 1), to_date(''0001/01/01'', ''yyyy/dd/mm'')) AS suspend_date,
91 nvl(to_date(hist_changestatus.last_unsuspend_date + 1), to_date(''0001/01/01'', ''yyyy/dd/mm'')) AS unsuspend_date,
92 GREATEST(
93 nvl(hist_changetariff.valid_from, to_date(''0001/01/01'', ''yyyy/dd/mm'')),
94 citems.contracted_from) AS start_date,
95 bp.date_value as binding_period,
96 simcontract.dic_status_id AS roots_status,
97 xtmd.msisdn
98 from (SELECT citems2.*, partner.mno_id AS mno_id, 0 as is_for_wsp
99 FROM cdm_contract_items_tg citems2,
100 cdm_party_role_tree_v partner
101 WHERE partner.party_role_id = citems2.party_role_id
102 and partner.root_party_role_id != partner.mno_id
103 AND (partner.root_party_role_Id = '||pn_partner_id||' OR partner.party_role_Id = '||pn_partner_id||')
104 UNION ALL
105 SELECT /*+ index(citems2 cdm_citems_provider_ix) */citems2.*, partner.mno_id as mno_id, 1 as is_for_wsp
106 FROM cdm_contract_items_tg citems2,
107 cdm_party_role_tree_v partner
108 WHERE partner.party_role_id = citems2.party_role_id
109 and citems2.provider_id = (select mno_id from x_wholesale_partners_v where wholesale_partner_id = '||pn_partner_id||')
110 and citems2.provider_id != partner.mno_id
111 and citems2.party_role_id != '||pn_partner_id||'
112 AND citems2.owning_party_id != '||pn_partner_id||'
113 and citems2.owning_party_id != citems2.provider_id) citems
114 join cdm_contract_items_tg simcontract
115 ON simcontract.parent_id = citems.root_id
116 and simcontract.dic_status_id != (select d.id from cfg_dictionaries_t d where d.code = ''CONTRACT_ITEM_STATUS.PENDING'')
117 and simcontract.valid_from <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
118 and simcontract.parent_id = simcontract.root_id
119 and (simcontract.valid_to is null or simcontract.valid_to > to_date('''||vv_date_to||''', ''yy/mm/dd''))
120 and simcontract.party_role_Id = citems.party_role_id
121 AND simcontract.is_valid = 1
122 AND simcontract.product_id = (SELECT p.ID FROM pc_products_t p WHERE p.NAME = ''SIM contract'')
123 join cdm_access_point_name_tg apn
124 on apn.contract_item_id = simcontract.contract_item_id
125 and apn.valid_from <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
126 and apn.is_valid = 1
127 AND (apn.valid_to IS NULL OR apn.valid_to > to_date('''||vv_date_to||''', ''yy/mm/dd''))
128 and apn.access_point_type_name_id = (select aptn.id from pc_access_point_type_names_t aptn where aptn.caption = ''ICCID'')
129 left join etl_pmr_3arts_t xtmd
130 ON xtmd.iccid = apn.address
131 AND xtmd.partner_id = simcontract.party_role_id
132 AND xtmd.activation_date <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
133 and (xtmd.termination_date > to_date('''||vv_date_from||''', ''yy/mm/dd'') or xtmd.termination_date is null)
134 left join cdm_contract_item_attr_ins_tg citemattr
135 on citemattr.contract_item_id = simcontract.contract_item_id
136 and citemattr.attribute_code = ''PRODUCTS.SIM_CONTRACT.TARIFF_FOR_SETTLEMENT_WITH_PROVIDER''
137 and citemattr.is_valid=1
138 and (
139 (citems.contracted_to is null
140 and citemattr.valid_from <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
141 and (citemattr.valid_to is null or citemattr.valid_to > to_date('''||vv_date_to||''', ''yy/mm/dd''))
142 )
143 or
144 (citems.contracted_to is not null
145 and citemattr.valid_from < citems.contracted_to
146 and (citemattr.valid_to is null or citemattr.valid_to >= citems.contracted_to))
147 )
148 LEFT JOIN cdm_contract_item_attr_ins_tg bp
149 on bp.contract_item_id = citems.contract_item_id
150 and bp.attribute_code = ''PRODUCTS.BINDING_PERIOD_END''
151 and bp.is_valid = 1
152 and (
153 (citems.contracted_to is null
154 and bp.valid_from <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
155 and (bp.valid_to is null or bp.valid_to > to_date('''||vv_date_to||''', ''yy/mm/dd''))
156 )
157 or
158 (citems.contracted_to is not null
159 and bp.valid_from < citems.contracted_to
160 and (bp.valid_to is null or bp.valid_to >= citems.contracted_to))
161 )
162 left join cdm_contract_item_attr_ins_tg intermno_attr
163 on intermno_attr.contract_item_id = simcontract.contract_item_id
164 and intermno_attr.attribute_code = ''PRODUCTS.SIM_CONTRACT.INTER_MNO_TARIFF''
165 and intermno_attr.is_valid = 1
166 and (
167 (citems.contracted_to is null
168 and intermno_attr.valid_from <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
169 and (intermno_attr.valid_to is null or intermno_attr.valid_to > to_date('''||vv_date_to||''', ''yy/mm/dd''))
170 )
171 or
172 (citems.contracted_to is not null
173 and intermno_attr.valid_from < citems.contracted_to
174 and (intermno_attr.valid_to is null or intermno_attr.valid_to >= citems.contracted_to))
175 )
176 left join etl_product_mapping_t intermno_mapping
177 ON intermno_mapping.provider_id = simcontract.provider_id
178 and intermno_mapping.buyer_id = citems.mno_id
179 AND intermno_mapping.tariff_plan_variant_id = intermno_attr.number_value
180 AND intermno_mapping.buyer_prod_id = citems.product_id
181 left join etl_citems_tariff_hist_t hist_changetariff
182 ON hist_changetariff.owner_id = simcontract.contract_item_id
183 AND nvl(citems.contracted_to, to_date(''9999/12/30'', ''yyyy/mm/dd'')) BETWEEN hist_changetariff.valid_from AND hist_changetariff.valid_to
184 left join etl_citems_status_hist_t hist_changestatus
185 ON hist_changestatus.owner_id = simcontract.contract_item_id
186 and nvl(citems.contracted_to, to_date(''9999/12/30'', ''yyyy/mm/dd'')) between hist_changestatus.valid_from and hist_changestatus.valid_to
187 where citems.dic_status_id != (select d.id from cfg_dictionaries_t d where d.code = ''CONTRACT_ITEM_STATUS.PENDING'')
188 and citems.valid_from <= to_date('''||vv_date_to||''', ''yy/mm/dd'')
189 AND (citems.valid_to IS NULL OR citems.valid_to > to_date('''||vv_date_to||''', ''yy/mm/dd''))
190 AND citems.is_valid = 1
191 AND citems.contracted_from < to_date('''||vv_date_to||''', ''yy/mm/dd'') AND (citems.contracted_to IS NULL or citems.contracted_to > to_date('''||to_char(pd_from, 'yy/mm/dd')||''', ''yy/mm/dd''))
192 and exists(select 1 from prod_tmp p where p.id=citems.product_id)
193 AND (citems.is_for_wsp = 1 and (intermno_mapping.provider_prod_id IS NOT NULL AND citems.provider_id <> citems.mno_id or citems.provider_id = citems.mno_id) or citems.is_for_wsp = 0)';
194 --dbms_output.put_line(vv_sql);
195 OPEN c_select FOR vv_sql;
196 loop
197 BEGIN
198 fetch c_select
199 bulk collect into vt_data limit 10000;
200 for i in 1..vt_data.count
201 loop
202 vr_data := vt_data(i);
203 vt_start_date := GREATEST(vr_data.start_date, vr_data.suspend_date, vr_data.unsuspend_date);
204
205 if vr_data.contracted_to is not null and vr_data.contracted_to >= vt_date_to then
206 vv_enddate := null;
207 else
208 vv_enddate := get_formatted_date(vr_data.contracted_to,'N');
209 end if;
210 vv_bindingenddate := get_formatted_date(vr_data.binding_period,'N');
211 -- vv_enddate is not null => SIM is terminated (see comments of: M2MDTAG-10839)
212 if vv_enddate is null and ((vt_start_date = vr_data.suspend_date and vr_data.suspend_date <= pd_to) or (vt_start_date = vr_data.unsuspend_date and vr_data.unsuspend_date > pd_to)) then
213 if vv_mno_code = 'TMD' then
214 vv_rateplan := '1000000000';
215 elsif vv_mno_code = 'TMA' then
216 vv_rateplan := '3000000000';
217 end if;
218 ELSE
219 vv_rateplan := crm_cache_tools.get_external_id(vr_data.tariff_plan_variant_id, 'TariffPlanVariant');
220 END IF;
221 -- for cWB purposes
222 IF vt_start_date > pd_to THEN
223 if vt_start_date = vr_data.suspend_date then
224 vt_start_date := GREATEST(vr_data.start_date, vr_data.unsuspend_date);
225 elsif vt_start_date = vr_data.unsuspend_date then
226 vt_start_date := GREATEST(vr_data.start_date, vr_data.suspend_date);
227 end if;
228 END IF;
229 vv_startdate := get_formatted_date(trunc(vt_start_date, 'DDD'),'Y');
230 --
231 vv_bindingstartdate := vv_startdate;
232 -- M2MDTAG-11837
233 IF (vt_start_date = vr_data.suspend_date or vt_start_date = vr_data.unsuspend_date) and vv_bindingenddate is not null and vt_start_date >= vr_data.binding_period THEN
234 vv_bindingstartdate := null;
235 vv_bindingenddate := null;
236 ELSIF vv_rateplan = '1000000000' or vv_rateplan = '3000000000' THEN
237 vv_bindingenddate := null;
238 END IF;
239 --
240 vv_product := crm_cache_tools.get_external_id(vr_data.product_id, 'Product');
241 if (vr_data.msisdn is not null) THEN
242 pipe row (x_crm_pmr_product_data_o(vr_data.msisdn, vv_rateplan, vv_product, vv_startdate, vv_enddate, vv_bindingstartdate, vv_bindingenddate, null));
243 end if;
244 end loop;
245 exit when c_select%notfound;
246 EXCEPTION
247 when others then
248 IF NOT gv_debug THEN
249 crm_cache_tools.clearall;
250 raise_application_error(-20001, SQLERRM);
251 ELSE
252 dbms_output.put_line(substr(SQLERRM, 255));
253 END IF;
254 END;
255 end loop;
256 close c_select;
257 crm_cache_tools.clearall;
258 return;
259 end;