[Home] [Help]
PACKAGE BODY: APPS.BIC_CONSOLIDATE_CUST_DATA_PKG
Source
1 package body bic_consolidate_cust_data_pkg as
2 /* $Header: bicflatb.pls 115.10 2004/05/14 07:49:54 vsegu ship $ */
3
4 g_date date ;
5 g_org_id bic_dimv_operating_units.org_id % type;
6 g_party_id hz_parties.party_id % type;
7
8 procedure populate_party_data (p_start_date date,
9 p_end_date date) is
10 -- For a given date range, This cursor gets all parties,orgs and period,
11 -- which have any data in bic_customer_summary_all
12 cursor c_parties is
13 select distinct customer_id, org_id, period_start_date
14 from bic_customer_summary_all
15 where period_start_date between p_start_date and p_end_date;
16
17 -- This cursor gets measure_code and its value for a party, org and period
18 cursor c_measure_values is
19 select measure_code, value
20 from bic_customer_summary_all
21 where period_start_date = g_date
22 and customer_id = g_party_id
23 and nvl(org_id,-99) = nvl(g_org_id,-99);
24
25 l_date date;
26 l_col_str varchar2(4000);
27 l_val_str varchar2(4000);
28 l_ins_str varchar2(4000);
29
30 -- variables to hold value for each measure
31 -- For each measure there is one variable.
32 l_ACQUISITION number;
33 l_ACTIVATION number;
34 l_RETENTION number;
35 l_LIFE_CYCLE number;
36 l_ASSOCIATION_YRS number;
37 l_AVG_CLOSED_SRS number;
38 l_AVG_COMPLAINTS number;
39 l_AVG_HOLD_TIME number;
40 l_AVG_INTERACTIONS number;
41 l_AVG_INTERACTIONS_PER_SR number;
42 l_AVG_LEN_OF_EMP number;
43 l_AVG_OUTSTANDING_SRS number;
44 l_AVG_PERIOD_FOR_ACTIVE_CONT number;
45 l_AVG_SRS_LOGGED number;
46 l_AVG_SR_RESL_TIME number;
47 l_AVG_SR_RESPONSE_TIME number;
48 l_AVG_TALK_TIME number;
49 l_AVG_TRANSFERS_BEF_RESL number;
50 l_AVG_TRANSFERS_PER_SR number;
51 l_AVG_WORKLOAD number;
52 l_CALLS number;
53 l_CALL_LENGTH number;
54 l_CALL_TYPE_INTERACTIONS number;
55 l_CLOSED_SRS number;
56 l_COGS number;
57 l_CONTRACTS_CUML number;
58 l_CONTRACT_AMT number;
59 l_CONTRACT_DURATION number;
60 l_ESC_SRS number;
61 l_FIRST_CALL_CL_RATE number;
62 l_INACTIVE_CONTRACTS number;
63 l_INTERACTIONS number;
64 l_INTERAC_CUML number;
65 l_LOYALTY number;
66 l_NEW_CONTRACTS number;
67 l_NO_OF_COMPLAINTS number;
68 l_NO_OF_INTERACTIONS number;
69 l_NO_OF_TRANSFERS number;
70 l_OL_DEL_VALUE number;
71 l_OL_ONTIME_VALUE number;
72 l_ONTIME_PAYMENTS number;
73 l_ONTIME_SHIP_PCT number;
74 l_ONTIME_VALUE_PCT number;
75 l_ON_TIME_PAYMENT_RATE number;
76 l_OPEN_CONTRACTS number;
77 l_OPEN_SRS number;
78 l_ORDER_AMT number;
79 l_ORDER_LINES_DELIVERED number;
80 l_ORDER_LINES_ONTIME number;
81 l_ORDER_NUM number;
82 l_ORDER_QTY number;
83 l_ORDER_QTY_CUML number;
84 l_ORDER_RECENCY number;
85 l_PAYMENTS number;
86 l_PCT_ESC_SRS number;
87 l_PCT_REOPENED_SRS number;
88 l_PCT_RETURN_QTY number;
89 l_PERCT_CALL_REWORK number;
90 l_PRODUCTS number;
91 l_PROFITABILITY number;
92 l_REFERALS number;
93 l_RENEWED_CONTRACTS number;
94 l_REOPENED_SRS number;
95 l_RETURNS number;
96 l_RETURN_BY_VALUE number;
97 l_RETURN_BY_VALUE_PCT number;
98 l_RETURN_QTY number;
99 l_SALES number;
100 l_SATISFACTION number;
101 l_SF_BILLING number;
102 l_SF_CONTRACT number;
103 l_SF_QUALITY number;
104 l_SF_SERVICE number;
105 l_SF_SHIPMENT number;
106 l_SRS_LOGGED number;
107 l_SR_CLOSED_INT number;
108 l_TOTAL_HOLD_TIME number;
109 l_TOTAL_LEN_OF_EMP number;
110 l_TOTAL_SR_RESL_TIME number;
111 l_TOTAL_SR_RESPONSE_TIME number;
112
113
114 l_measure_code bic_measure_attribs.measure_code % type;
115 l_measure_value bic_customer_summary_all.value % type;
116 begin
117 g_date := to_date('01-01-1999','dd-mm-yyyy') ;
118 open c_parties;
119 loop
120 fetch c_parties into g_party_id, g_org_id, g_date;
121 if c_parties % notfound then exit; end if;
122
123 -- initialize values for measure_codes
124 l_ACQUISITION := null;
125 l_ACTIVATION := null;
126 l_RETENTION := null;
127 l_LIFE_CYCLE := null;
128 l_ASSOCIATION_YRS := null;
129 l_AVG_CLOSED_SRS := null;
130 l_AVG_COMPLAINTS := null;
131 l_AVG_HOLD_TIME := null;
132 l_AVG_INTERACTIONS := null;
133 l_AVG_INTERACTIONS_PER_SR := null;
134 l_AVG_LEN_OF_EMP := null;
135 l_AVG_OUTSTANDING_SRS := null;
136 l_AVG_PERIOD_FOR_ACTIVE_CONT := null;
137 l_AVG_SRS_LOGGED := null;
138 l_AVG_SR_RESL_TIME := null;
139 l_AVG_SR_RESPONSE_TIME := null;
140 l_AVG_TALK_TIME := null;
141 l_AVG_TRANSFERS_BEF_RESL := null;
142 l_AVG_TRANSFERS_PER_SR := null;
143 l_AVG_WORKLOAD := null;
144 l_CALLS := null;
145 l_CALL_LENGTH := null;
146 l_CALL_TYPE_INTERACTIONS := null;
147 l_CLOSED_SRS := null;
148 l_COGS := null;
149 l_CONTRACTS_CUML := null;
150 l_CONTRACT_AMT := null;
151 l_CONTRACT_DURATION := null;
152 l_ESC_SRS := null;
153 l_FIRST_CALL_CL_RATE := null;
154 l_INACTIVE_CONTRACTS := null;
155 l_INTERACTIONS := null;
156 l_INTERAC_CUML := null;
157 l_LOYALTY := null;
158 l_NEW_CONTRACTS := null;
159 l_NO_OF_COMPLAINTS := null;
160 l_NO_OF_INTERACTIONS := null;
161 l_NO_OF_TRANSFERS := null;
162 l_OL_DEL_VALUE := null;
163 l_OL_ONTIME_VALUE := null;
164 l_ONTIME_PAYMENTS := null;
165 l_ONTIME_SHIP_PCT := null;
166 l_ONTIME_VALUE_PCT := null;
167 l_ON_TIME_PAYMENT_RATE := null;
168 l_OPEN_CONTRACTS := null;
169 l_OPEN_SRS := null;
170 l_ORDER_AMT := null;
171 l_ORDER_LINES_DELIVERED := null;
172 l_ORDER_LINES_ONTIME := null;
173 l_ORDER_NUM := null;
174 l_ORDER_QTY := null;
175 l_ORDER_QTY_CUML := null;
176 l_ORDER_RECENCY := null;
177 l_PAYMENTS := null;
178 l_PCT_ESC_SRS := null;
179 l_PCT_REOPENED_SRS := null;
180 l_PCT_RETURN_QTY := null;
184 l_REFERALS := null;
181 l_PERCT_CALL_REWORK := null;
182 l_PRODUCTS := null;
183 l_PROFITABILITY := null;
185 l_RENEWED_CONTRACTS := null;
186 l_REOPENED_SRS := null;
187 l_RETURNS := null;
188 l_RETURN_BY_VALUE := null;
189 l_RETURN_BY_VALUE_PCT := null;
190 l_RETURN_QTY := null;
191 l_SALES := null;
192 l_SATISFACTION := null;
193 l_SF_BILLING := null;
194 l_SF_CONTRACT := null;
195 l_SF_QUALITY := null;
196 l_SF_SERVICE := null;
197 l_SF_SHIPMENT := null;
198 l_SRS_LOGGED := null;
199 l_SR_CLOSED_INT := null;
200 l_TOTAL_HOLD_TIME := null;
201 l_TOTAL_LEN_OF_EMP := null;
202 l_TOTAL_SR_RESL_TIME := null;
203 l_TOTAL_SR_RESPONSE_TIME := null;
204
205 -- This will get measures for a party, org and period and assign it to
206 -- appropriate variable.
207 open c_measure_values;
208 loop
209 fetch c_measure_values into l_measure_code, l_measure_value;
210 if c_measure_values%notfound then exit; end if;
211 if l_measure_code = 'ACQUISITION' then
212 l_ACQUISITION := l_measure_value;
213 elsif l_measure_code = 'ACTIVATION' then
214 l_ACTIVATION := l_measure_value;
215 elsif l_measure_code = 'ASSOCIATION_YRS' then
216 l_ASSOCIATION_YRS := l_measure_value;
217 elsif l_measure_code = 'AVG_CLOSED_SRS' then
218 l_AVG_CLOSED_SRS := l_measure_value;
219 elsif l_measure_code = 'AVG_COMPLAINTS' then
220 l_AVG_COMPLAINTS := l_measure_value;
221 elsif l_measure_code = 'AVG_HOLD_TIME' then
222 l_AVG_HOLD_TIME := l_measure_value;
223 elsif l_measure_code = 'AVG_INTERACTIONS' then
224 l_AVG_INTERACTIONS := l_measure_value;
225 elsif l_measure_code = 'AVG_INTERACTIONS_PER_SR' then
226 l_AVG_INTERACTIONS_PER_SR := l_measure_value;
227 elsif l_measure_code = 'AVG_LEN_OF_EMP' then
228 l_AVG_LEN_OF_EMP := l_measure_value;
229 elsif l_measure_code = 'AVG_OUTSTANDING_SRS' then
230 l_AVG_OUTSTANDING_SRS := l_measure_value;
231 elsif l_measure_code = 'AVG_PERIOD_FOR_ACTIVE_CONT' then
232 l_AVG_PERIOD_FOR_ACTIVE_CONT := l_measure_value;
233 elsif l_measure_code = 'AVG_SRS_LOGGED' then
234 l_AVG_SRS_LOGGED := l_measure_value;
235 elsif l_measure_code = 'AVG_SR_RESL_TIME' then
236 l_AVG_SR_RESL_TIME := l_measure_value;
237 elsif l_measure_code = 'AVG_SR_RESPONSE_TIME' then
238 l_AVG_SR_RESPONSE_TIME := l_measure_value;
239 elsif l_measure_code = 'AVG_TALK_TIME' then
240 l_AVG_TALK_TIME := l_measure_value;
241 elsif l_measure_code = 'AVG_TRANSFERS_BEF_RESL' then
242 l_AVG_TRANSFERS_BEF_RESL := l_measure_value;
243 elsif l_measure_code = 'AVG_TRANSFERS_PER_SR' then
244 l_AVG_TRANSFERS_PER_SR := l_measure_value;
245 elsif l_measure_code = 'AVG_WORKLOAD' then
246 l_AVG_WORKLOAD := l_measure_value;
247 elsif l_measure_code = 'CALLS' then
248 l_CALLS := l_measure_value;
249 elsif l_measure_code = 'CALL_LENGTH' then
250 l_CALL_LENGTH := l_measure_value;
251 elsif l_measure_code = 'CALL_TYPE_INTERACTIONS' then
252 l_CALL_TYPE_INTERACTIONS := l_measure_value;
253 elsif l_measure_code = 'CLOSED_SRS' then
254 l_CLOSED_SRS := l_measure_value;
255 elsif l_measure_code = 'COGS' then
256 l_COGS := l_measure_value;
257 elsif l_measure_code = 'CONTRACTS_CUML' then
258 l_CONTRACTS_CUML := l_measure_value;
259 elsif l_measure_code = 'CONTRACT_AMT' then
260 l_CONTRACT_AMT := l_measure_value;
261 elsif l_measure_code = 'CONTRACT_DURATION' then
262 l_CONTRACT_DURATION := l_measure_value;
263 elsif l_measure_code = 'ESC_SRS' then
264 l_ESC_SRS := l_measure_value;
265 elsif l_measure_code = 'FIRST_CALL_CL_RATE' then
266 l_FIRST_CALL_CL_RATE := l_measure_value;
267 elsif l_measure_code = 'INACTIVE_CONTRACTS' then
268 l_INACTIVE_CONTRACTS := l_measure_value;
269 elsif l_measure_code = 'INTERACTIONS' then
270 l_INTERACTIONS := l_measure_value;
271 elsif l_measure_code = 'INTERAC_CUML' then
272 l_INTERAC_CUML := l_measure_value;
273 elsif l_measure_code = 'LIFE_CYCLE' then
274 l_LIFE_CYCLE := l_measure_value;
275 elsif l_measure_code = 'LOYALTY' then
276 l_LOYALTY := l_measure_value;
277 elsif l_measure_code = 'NEW_CONTRACTS' then
278 l_NEW_CONTRACTS := l_measure_value;
279 elsif l_measure_code = 'NO_OF_COMPLAINTS' then
280 l_NO_OF_COMPLAINTS := l_measure_value;
281 elsif l_measure_code = 'NO_OF_INTERACTIONS' then
282 l_NO_OF_INTERACTIONS := l_measure_value;
283 elsif l_measure_code = 'NO_OF_TRANSFERS' then
284 l_NO_OF_TRANSFERS := l_measure_value;
285 elsif l_measure_code = 'OL_DEL_VALUE' then
286 l_OL_DEL_VALUE := l_measure_value;
287 elsif l_measure_code = 'OL_ONTIME_VALUE' then
288 l_OL_ONTIME_VALUE := l_measure_value;
289 elsif l_measure_code = 'ONTIME_PAYMENTS' then
290 l_ONTIME_PAYMENTS := l_measure_value;
291 elsif l_measure_code = 'ONTIME_SHIP_PCT' then
292 l_ONTIME_SHIP_PCT := l_measure_value;
293 elsif l_measure_code = 'ONTIME_VALUE_PCT' then
294 l_ONTIME_VALUE_PCT := l_measure_value;
295 elsif l_measure_code = 'ON_TIME_PAYMENT_RATE' then
296 l_ON_TIME_PAYMENT_RATE := l_measure_value;
297 elsif l_measure_code = 'OPEN_CONTRACTS' then
298 l_OPEN_CONTRACTS := l_measure_value;
299 elsif l_measure_code = 'OPEN_SRS' then
300 l_OPEN_SRS := l_measure_value;
301 elsif l_measure_code = 'ORDER_AMT' then
302 l_ORDER_AMT := l_measure_value;
303 elsif l_measure_code = 'ORDER_LINES_DELIVERED' then
304 l_ORDER_LINES_DELIVERED := l_measure_value;
305 elsif l_measure_code = 'ORDER_LINES_ONTIME' then
306 l_ORDER_LINES_ONTIME := l_measure_value;
307 elsif l_measure_code = 'ORDER_NUM' then
308 l_ORDER_NUM := l_measure_value;
309 elsif l_measure_code = 'ORDER_QTY' then
310 l_ORDER_QTY := l_measure_value;
311 elsif l_measure_code = 'ORDER_QTY_CUML' then
312 l_ORDER_QTY_CUML := l_measure_value;
313 elsif l_measure_code = 'ORDER_RECENCY' then
314 l_ORDER_RECENCY := l_measure_value;
315 elsif l_measure_code = 'PAYMENTS' then
316 l_PAYMENTS := l_measure_value;
317 elsif l_measure_code = 'PCT_ESC_SRS' then
318 l_PCT_ESC_SRS := l_measure_value;
319 elsif l_measure_code = 'PCT_REOPENED_SRS' then
320 l_PCT_REOPENED_SRS := l_measure_value;
321 elsif l_measure_code = 'PCT_RETURN_QTY' then
322 l_PCT_RETURN_QTY := l_measure_value;
323 elsif l_measure_code = 'PERCT_CALL_REWORK' then
324 l_PERCT_CALL_REWORK := l_measure_value;
325 elsif l_measure_code = 'PRODUCTS' then
326 l_PRODUCTS := l_measure_value;
327 elsif l_measure_code = 'PROFITABILITY' then
328 l_PROFITABILITY := l_measure_value;
329 elsif l_measure_code = 'REFERALS' then
330 l_REFERALS := l_measure_value;
331 elsif l_measure_code = 'RENEWED_CONTRACTS' then
332 l_RENEWED_CONTRACTS := l_measure_value;
333 elsif l_measure_code = 'REOPENED_SRS' then
334 l_REOPENED_SRS := l_measure_value;
335 elsif l_measure_code = 'RETENTION' then
336 l_RETENTION := l_measure_value;
337 elsif l_measure_code = 'RETURNS' then
338 l_RETURNS := l_measure_value;
339 elsif l_measure_code = 'RETURN_BY_VALUE' then
340 l_RETURN_BY_VALUE := l_measure_value;
341 elsif l_measure_code = 'RETURN_BY_VALUE_PCT' then
342 l_RETURN_BY_VALUE_PCT := l_measure_value;
343 elsif l_measure_code = 'RETURN_QTY' then
344 l_RETURN_QTY := l_measure_value;
345 elsif l_measure_code = 'SALES' then
346 l_SALES := l_measure_value;
347 elsif l_measure_code = 'SATISFACTION' then
348 l_SATISFACTION := l_measure_value;
349 elsif l_measure_code = 'SF_BILLING' then
350 l_SF_BILLING := l_measure_value;
351 elsif l_measure_code = 'SF_CONTRACT' then
352 l_SF_CONTRACT := l_measure_value;
353 elsif l_measure_code = 'SF_QUALITY' then
354 l_SF_QUALITY := l_measure_value;
355 elsif l_measure_code = 'SF_SERVICE' then
356 l_SF_SERVICE := l_measure_value;
357 elsif l_measure_code = 'SF_SHIPMENT' then
358 l_SF_SHIPMENT := l_measure_value;
359 elsif l_measure_code = 'SRS_LOGGED' then
360 l_SRS_LOGGED := l_measure_value;
361 elsif l_measure_code = 'SR_CLOSED_INT' then
362 l_SR_CLOSED_INT := l_measure_value;
363 elsif l_measure_code = 'TOTAL_HOLD_TIME' then
364 l_TOTAL_HOLD_TIME := l_measure_value;
365 elsif l_measure_code = 'TOTAL_LEN_OF_EMP' then
366 l_TOTAL_LEN_OF_EMP := l_measure_value;
367 elsif l_measure_code = 'TOTAL_SR_RESL_TIME' then
368 l_TOTAL_SR_RESL_TIME := l_measure_value;
369 elsif l_measure_code = 'TOTAL_SR_RESPONSE_TIME' then
370 l_TOTAL_SR_RESPONSE_TIME := l_measure_value;
371 end if;
372 /***********************
373 select ' elsif l_measure_code = ''' || measure_code || ''' then
374 l_' || measure_code || ' = l_measure_value'
375 from bic_measure_attribs
376 *****************************/
377 end loop;
378 close c_measure_values;
379
380 -- Begin statement is used for exception handling.
381 begin
382 update bic_party_summ set
383 ASSOCIATION_YRS = nvl(l_ASSOCIATION_YRS , ASSOCIATION_YRS ),
384 AVG_CLOSED_SRS = nvl(l_AVG_CLOSED_SRS , AVG_CLOSED_SRS ),
385 AVG_COMPLAINTS = nvl(l_AVG_COMPLAINTS , AVG_COMPLAINTS ),
386 AVG_HOLD_TIME = nvl(l_AVG_HOLD_TIME , AVG_HOLD_TIME ),
387 AVG_INTERACTIONS = nvl(l_AVG_INTERACTIONS , AVG_INTERACTIONS ),
388 AVG_INTERACTIONS_PER_SR = nvl(l_AVG_INTERACTIONS_PER_SR , AVG_INTERACTIONS_PER_SR ),
389 AVG_LEN_OF_EMP = nvl(l_AVG_LEN_OF_EMP , AVG_LEN_OF_EMP ),
390 AVG_OUTSTANDING_SRS = nvl(l_AVG_OUTSTANDING_SRS , AVG_OUTSTANDING_SRS ),
391 AVG_PERIOD_FOR_ACTIVE_CONT = nvl(l_AVG_PERIOD_FOR_ACTIVE_CONT, AVG_PERIOD_FOR_ACTIVE_CONT ),
392 AVG_SRS_LOGGED = nvl(l_AVG_SRS_LOGGED , AVG_SRS_LOGGED ),
393 AVG_SR_RESL_TIME = nvl(l_AVG_SR_RESL_TIME , AVG_SR_RESL_TIME ),
394 AVG_SR_RESPONSE_TIME = nvl(l_AVG_SR_RESPONSE_TIME , AVG_SR_RESPONSE_TIME ),
395 AVG_TALK_TIME = nvl(l_AVG_TALK_TIME , AVG_TALK_TIME ),
396 AVG_TRANSFERS_BEF_RESL = nvl(l_AVG_TRANSFERS_BEF_RESL , AVG_TRANSFERS_BEF_RESL ),
397 AVG_TRANSFERS_PER_SR = nvl(l_AVG_TRANSFERS_PER_SR , AVG_TRANSFERS_PER_SR ),
398 AVG_WORKLOAD = nvl(l_AVG_WORKLOAD , AVG_WORKLOAD ),
399 CALLS = nvl(l_CALLS , CALLS ),
400 CALL_LENGTH = nvl(l_CALL_LENGTH , CALL_LENGTH ),
401 CALL_TYPE_INTERACTIONS = nvl(l_CALL_TYPE_INTERACTIONS , CALL_TYPE_INTERACTIONS ),
402 CLOSED_SRS = nvl(l_CLOSED_SRS , CLOSED_SRS ),
403 COGS = nvl(l_COGS , COGS ),
404 CONTRACTS_CUML = nvl(l_CONTRACTS_CUML , CONTRACTS_CUML ),
405 CONTRACT_AMT = nvl(l_CONTRACT_AMT , CONTRACT_AMT ),
406 CONTRACT_DURATION = nvl(l_CONTRACT_DURATION , CONTRACT_DURATION ),
407 ESC_SRS = nvl(l_ESC_SRS , ESC_SRS ),
408 FIRST_CALL_CL_RATE = nvl(l_FIRST_CALL_CL_RATE , FIRST_CALL_CL_RATE ),
409 INACTIVE_CONTRACTS = nvl(l_INACTIVE_CONTRACTS , INACTIVE_CONTRACTS ),
410 INTERACTIONS = nvl(l_INTERACTIONS , INTERACTIONS ),
411 INTERAC_CUML = nvl(l_INTERAC_CUML , INTERAC_CUML ),
412 LOYALTY = nvl(l_LOYALTY , LOYALTY ),
413 NEW_CONTRACTS = nvl(l_NEW_CONTRACTS , NEW_CONTRACTS ),
414 NO_OF_COMPLAINTS = nvl(l_NO_OF_COMPLAINTS , NO_OF_COMPLAINTS ),
415 NO_OF_INTERACTIONS = nvl(l_NO_OF_INTERACTIONS , NO_OF_INTERACTIONS ),
416 NO_OF_TRANSFERS = nvl(l_NO_OF_TRANSFERS , NO_OF_TRANSFERS ),
417 OL_DEL_VALUE = nvl(l_OL_DEL_VALUE , OL_DEL_VALUE ),
418 OL_ONTIME_VALUE = nvl(l_OL_ONTIME_VALUE , OL_ONTIME_VALUE ),
419 ONTIME_PAYMENTS = nvl(l_ONTIME_PAYMENTS , ONTIME_PAYMENTS ),
420 ONTIME_SHIP_PCT = nvl(l_ONTIME_SHIP_PCT , ONTIME_SHIP_PCT ),
421 ONTIME_VALUE_PCT = nvl(l_ONTIME_VALUE_PCT , ONTIME_VALUE_PCT ),
422 ON_TIME_PAYMENT_RATE = nvl(l_ON_TIME_PAYMENT_RATE , ON_TIME_PAYMENT_RATE ),
423 OPEN_CONTRACTS = nvl(l_OPEN_CONTRACTS , OPEN_CONTRACTS ),
424 OPEN_SRS = nvl(l_OPEN_SRS , OPEN_SRS ),
425 ORDER_AMT = nvl(l_ORDER_AMT , ORDER_AMT ),
429 ORDER_QTY = nvl(l_ORDER_QTY , ORDER_QTY ),
426 ORDER_LINES_DELIVERED = nvl(l_ORDER_LINES_DELIVERED , ORDER_LINES_DELIVERED ),
427 ORDER_LINES_ONTIME = nvl(l_ORDER_LINES_ONTIME , ORDER_LINES_ONTIME ),
428 ORDER_NUM = nvl(l_ORDER_NUM , ORDER_NUM ),
430 ORDER_QTY_CUML = nvl(l_ORDER_QTY_CUML , ORDER_QTY_CUML ),
431 ORDER_RECENCY = nvl(l_ORDER_RECENCY , ORDER_RECENCY ),
432 PAYMENTS = nvl(l_PAYMENTS , PAYMENTS ),
433 PCT_ESC_SRS = nvl(l_PCT_ESC_SRS , PCT_ESC_SRS ),
434 PCT_REOPENED_SRS = nvl(l_PCT_REOPENED_SRS , PCT_REOPENED_SRS ),
435 PCT_RETURN_QTY = nvl(l_PCT_RETURN_QTY , PCT_RETURN_QTY ),
436 PERCT_CALL_REWORK = nvl(l_PERCT_CALL_REWORK , PERCT_CALL_REWORK ),
437 PRODUCTS = nvl(l_PRODUCTS , PRODUCTS ),
438 PROFITABILITY = nvl(l_PROFITABILITY , PROFITABILITY ),
439 REFERALS = nvl(l_REFERALS , REFERALS ),
440 RENEWED_CONTRACTS = nvl(l_RENEWED_CONTRACTS , RENEWED_CONTRACTS ),
441 REOPENED_SRS = nvl(l_REOPENED_SRS , REOPENED_SRS ),
442 RETURNS = nvl(l_RETURNS , RETURNS ),
443 RETURN_BY_VALUE = nvl(l_RETURN_BY_VALUE , RETURN_BY_VALUE ),
444 RETURN_BY_VALUE_PCT = nvl(l_RETURN_BY_VALUE_PCT , RETURN_BY_VALUE_PCT ),
445 RETURN_QTY = nvl(l_RETURN_QTY , RETURN_QTY ),
446 SALES = nvl(l_SALES , SALES ),
447 SATISFACTION = nvl(l_SATISFACTION , SATISFACTION ),
448 SF_BILLING = nvl(l_SF_BILLING , SF_BILLING ),
449 SF_CONTRACT = nvl(l_SF_CONTRACT , SF_CONTRACT ),
450 SF_QUALITY = nvl(l_SF_QUALITY , SF_QUALITY ),
451 SF_SERVICE = nvl(l_SF_SERVICE , SF_SERVICE ),
452 SF_SHIPMENT = nvl(l_SF_SHIPMENT , SF_SHIPMENT ),
453 SRS_LOGGED = nvl(l_SRS_LOGGED , SRS_LOGGED ),
454 SR_CLOSED_INT = nvl(l_SR_CLOSED_INT , SR_CLOSED_INT ),
455 TOTAL_HOLD_TIME = nvl(l_TOTAL_HOLD_TIME , TOTAL_HOLD_TIME ),
456 TOTAL_LEN_OF_EMP = nvl(l_TOTAL_LEN_OF_EMP , TOTAL_LEN_OF_EMP ),
457 TOTAL_SR_RESL_TIME = nvl(l_TOTAL_SR_RESL_TIME , TOTAL_SR_RESL_TIME ),
458 TOTAL_SR_RESPONSE_TIME = nvl(l_TOTAL_SR_RESPONSE_TIME , TOTAL_SR_RESPONSE_TIME )
459 where period_start_date = g_date
460 and party_id = g_party_id
461 and nvl(org_id,-99) = nvl(g_org_id,-99);
462
463 if sql%notfound then
464 insert into bic_party_summ (
465 party_id ,
466 org_id,
467 period_start_date,
468 ASSOCIATION_YRS ,
469 AVG_CLOSED_SRS ,
470 AVG_COMPLAINTS ,
471 AVG_HOLD_TIME ,
472 AVG_INTERACTIONS ,
473 AVG_INTERACTIONS_PER_SR ,
474 AVG_LEN_OF_EMP ,
475 AVG_OUTSTANDING_SRS ,
476 AVG_PERIOD_FOR_ACTIVE_CONT,
477 AVG_SRS_LOGGED ,
478 AVG_SR_RESL_TIME ,
479 AVG_SR_RESPONSE_TIME ,
480 AVG_TALK_TIME ,
481 AVG_TRANSFERS_BEF_RESL ,
482 AVG_TRANSFERS_PER_SR ,
483 AVG_WORKLOAD ,
484 CALLS ,
485 CALL_LENGTH ,
486 CALL_TYPE_INTERACTIONS ,
487 CLOSED_SRS ,
488 COGS ,
489 CONTRACTS_CUML ,
490 CONTRACT_AMT ,
491 CONTRACT_DURATION ,
492 ESC_SRS ,
493 FIRST_CALL_CL_RATE ,
494 INACTIVE_CONTRACTS ,
495 INTERACTIONS ,
496 INTERAC_CUML ,
497 LOYALTY ,
498 NEW_CONTRACTS ,
499 NO_OF_COMPLAINTS ,
500 NO_OF_INTERACTIONS ,
501 NO_OF_TRANSFERS ,
502 OL_DEL_VALUE ,
503 OL_ONTIME_VALUE ,
504 ONTIME_PAYMENTS ,
505 ONTIME_SHIP_PCT ,
506 ONTIME_VALUE_PCT ,
507 ON_TIME_PAYMENT_RATE ,
508 OPEN_CONTRACTS ,
509 OPEN_SRS ,
510 ORDER_AMT ,
511 ORDER_LINES_DELIVERED ,
512 ORDER_LINES_ONTIME ,
513 ORDER_NUM ,
514 ORDER_QTY ,
515 ORDER_QTY_CUML ,
519 PCT_REOPENED_SRS ,
516 ORDER_RECENCY ,
517 PAYMENTS ,
518 PCT_ESC_SRS ,
520 PCT_RETURN_QTY ,
521 PERCT_CALL_REWORK ,
522 PRODUCTS ,
523 PROFITABILITY ,
524 REFERALS ,
525 RENEWED_CONTRACTS ,
526 REOPENED_SRS ,
527 RETURNS ,
528 RETURN_BY_VALUE ,
529 RETURN_BY_VALUE_PCT ,
530 RETURN_QTY ,
531 SALES ,
532 SATISFACTION ,
533 SF_BILLING ,
534 SF_CONTRACT ,
535 SF_QUALITY ,
536 SF_SERVICE ,
537 SF_SHIPMENT ,
538 SRS_LOGGED ,
539 SR_CLOSED_INT ,
540 TOTAL_HOLD_TIME ,
541 TOTAL_LEN_OF_EMP ,
542 TOTAL_SR_RESL_TIME ,
543 TOTAL_SR_RESPONSE_TIME ,
544 last_updated_by ,
545 created_by ,
546 last_update_date ,
547 creation_date )
548 values ( g_party_id,
549 g_org_id,
550 g_date,
551 --l_ACQUISITION ,
552 --l_ACTIVATION ,
553 l_ASSOCIATION_YRS ,
554 l_AVG_CLOSED_SRS ,
555 l_AVG_COMPLAINTS ,
556 l_AVG_HOLD_TIME ,
557 l_AVG_INTERACTIONS ,
558 l_AVG_INTERACTIONS_PER_SR ,
559 l_AVG_LEN_OF_EMP ,
560 l_AVG_OUTSTANDING_SRS ,
561 l_AVG_PERIOD_FOR_ACTIVE_CONT,
562 l_AVG_SRS_LOGGED ,
563 l_AVG_SR_RESL_TIME ,
564 l_AVG_SR_RESPONSE_TIME ,
565 l_AVG_TALK_TIME ,
566 l_AVG_TRANSFERS_BEF_RESL ,
567 l_AVG_TRANSFERS_PER_SR ,
568 l_AVG_WORKLOAD ,
569 l_CALLS ,
570 l_CALL_LENGTH ,
571 l_CALL_TYPE_INTERACTIONS ,
572 l_CLOSED_SRS ,
573 l_COGS ,
574 l_CONTRACTS_CUML ,
575 l_CONTRACT_AMT ,
576 l_CONTRACT_DURATION ,
577 l_ESC_SRS ,
578 l_FIRST_CALL_CL_RATE ,
579 l_INACTIVE_CONTRACTS ,
580 l_INTERACTIONS ,
581 l_INTERAC_CUML ,
582 --l_LIFE_CYCLE ,
583 l_LOYALTY ,
584 l_NEW_CONTRACTS ,
585 l_NO_OF_COMPLAINTS ,
586 l_NO_OF_INTERACTIONS ,
587 l_NO_OF_TRANSFERS ,
588 l_OL_DEL_VALUE ,
589 l_OL_ONTIME_VALUE ,
590 l_ONTIME_PAYMENTS ,
591 l_ONTIME_SHIP_PCT ,
592 l_ONTIME_VALUE_PCT ,
593 l_ON_TIME_PAYMENT_RATE ,
594 l_OPEN_CONTRACTS ,
595 l_OPEN_SRS ,
596 l_ORDER_AMT ,
597 l_ORDER_LINES_DELIVERED ,
598 l_ORDER_LINES_ONTIME ,
599 l_ORDER_NUM ,
600 l_ORDER_QTY ,
601 l_ORDER_QTY_CUML ,
602 l_ORDER_RECENCY ,
603 l_PAYMENTS ,
604 l_PCT_ESC_SRS ,
605 l_PCT_REOPENED_SRS ,
606 l_PCT_RETURN_QTY ,
607 l_PERCT_CALL_REWORK ,
608 l_PRODUCTS ,
609 l_PROFITABILITY ,
610 l_REFERALS ,
611 l_RENEWED_CONTRACTS ,
612 l_REOPENED_SRS ,
613 --l_RETENTION ,
614 l_RETURNS ,
615 l_RETURN_BY_VALUE ,
616 l_RETURN_BY_VALUE_PCT ,
617 l_RETURN_QTY ,
618 l_SALES ,
619 l_SATISFACTION ,
620 l_SF_BILLING ,
621 l_SF_CONTRACT ,
622 l_SF_QUALITY ,
623 l_SF_SERVICE ,
624 l_SF_SHIPMENT ,
625 l_SRS_LOGGED ,
626 l_SR_CLOSED_INT ,
627 l_TOTAL_HOLD_TIME ,
628 l_TOTAL_LEN_OF_EMP ,
629 l_TOTAL_SR_RESL_TIME ,
630 l_TOTAL_SR_RESPONSE_TIME ,
631 0,0,sysdate,sysdate);
632 end if;
633 exception
637 ' Org Id:' || to_char(g_org_id) ||
634 when others then
635 fnd_file.put_line(fnd_file.log,
636 'Error for party id:' || to_char(g_party_id) ||
638 ' date:' || to_char(g_date,'dd-mon-yyyy') || '-'||
639 substr(sqlerrm,1,200));
640 /*
641 x_err := sqlerrm;
642 insert into bic_debug (report_id, message)
643 values ( 'SKM','Party_id:' || to_char(g_party_id) ||
644 x_err
645 );
646 commit;
647 *******************************/
648 end; -- of block for inserting record into bic_party_summ table.
649
650 end loop;
651 close c_parties;
652 end populate_party_data;
653 ----
654 ----
655 procedure populate_status_data (p_start_date date,
656 p_end_date date, p_measure_code varchar2) is
657 -- For a given date range, This cursor gets all parties and period,
658 -- which have any data in bic_customer_summary_all
659 cursor c_parties is
660 select distinct party_id, period_start_date
661 from bic_party_summary
662 where period_start_date between p_start_date and p_end_date;
663
664 -- This cursor gets measure_code and its value for a party and period
665 cursor c_measure_values is
666 select measure_code, value
667 from bic_party_summary
668 where period_start_date = g_date
669 and party_id = g_party_id;
670
671 l_date date;
672 l_col_str varchar2(4000);
673 l_val_str varchar2(4000);
674 l_ins_str varchar2(4000);
675
676 -- variables to hold value for each measure
677 -- For each measure there is one variable.
678 l_ACQUISITION number;
679 l_ACTIVATION number;
680 l_RETENTION number;
681 l_LIFE_CYCLE number;
682
683 l_measure_code bic_measure_attribs.measure_code % type;
684 l_measure_value bic_customer_summary_all.value % type;
685 x_err varchar2(2000);
686 begin
687 g_date := to_date('01-01-1999','dd-mm-yyyy') ;
688 open c_parties;
689 loop
690 fetch c_parties into g_party_id, g_date;
691 if c_parties % notfound then exit; end if;
692
693 -- initialize values for measure_codes
694 l_ACQUISITION := null;
695 l_ACTIVATION := null;
696 l_RETENTION := null;
697 -- l_LIFE_CYCLE := null ; changed by kalyan April 19
698
699 IF rtrim(ltrim(p_measure_code)) = 'LIFE_CYCLE' THEN
700 l_LIFE_CYCLE := 6 ;
701 ELSE
702 l_LIFE_CYCLE := null;
703 END IF;
704
705 -- This will get measures for a party, org and period and assign it to
706 -- appropriate variable.
707 open c_measure_values;
708 loop
709 fetch c_measure_values into l_measure_code, l_measure_value;
710 if c_measure_values%notfound then exit; end if;
711 if l_measure_code = 'ACQUISITION' then
712 l_ACQUISITION := l_measure_value;
713 elsif l_measure_code = 'ACTIVATION' then
714 l_ACTIVATION := l_measure_value;
715 elsif l_measure_code = 'LIFE_CYCLE' then
716 l_LIFE_CYCLE := l_measure_value;
717 elsif l_measure_code = 'RETENTION' then
718 l_RETENTION := l_measure_value;
719 end if;
720
721 end loop;
722 close c_measure_values;
723
724 -- Begin statement is used for exception handling.
725 begin
726 update bic_party_status_summ
727 set acquisition = nvl(l_acquisition, acquisition),
728 activation = nvl(l_activation , activation ),
729 retention = nvl(l_retention , retention ),
730 life_cycle = nvl(l_life_cycle , life_cycle )
731 where period_start_date = g_date
732 and party_id = g_party_id;
733
734 if sql%notfound then
735 insert into bic_party_status_summ (
736 party_id ,
737 period_start_date,
738 ACQUISITION ,
739 ACTIVATION ,
740 RETENTION ,
741 LIFE_CYCLE ,
742 last_updated_by,
743 created_by,
744 last_update_date,
745 creation_date)
746 values ( g_party_id ,
747 g_date ,
748 l_ACQUISITION ,
749 l_ACTIVATION ,
750 l_RETENTION ,
751 l_LIFE_CYCLE ,
752 0,0,sysdate,sysdate );
753 end if;
754 exception
755 when others then
756 fnd_file.put_line(fnd_file.log,
757 'Error n bic_party_status_summ for party id:' ||
758 to_char(g_party_id) ||
759 ' date:' || to_char(g_date,'dd-mon-yyyy') || '-'||
760 substr(sqlerrm,1,200));
761 /*******
762 x_err := sqlerrm;
763 insert into bic_debug (report_id, message)
764 values ( 'SKM','Party_id:' || to_char(g_party_id) ||
765 x_err
766 );
767 commit;
768 ******************/
769 end; -- of block for inserting record into bic_party_summ table.
770
771 end loop;
772 close c_parties;
773 end populate_status_data;
774 ----
775 ----
776 -- This procedure updates market segment for each party in bic_party_summ
777 -- and bic_party_status_summ tables.
778 procedure update_market_segment is
779 begin
780 update bic_party_summ summ
781 set market_segment_id = (select market_segment_id
782 from ams_party_market_segments mseg
783 where market_segment_flag = 'Y'
784 and mseg.party_id = summ.party_id
785 and rownum = 1);
786 update bic_party_status_summ summ
787 set market_segment_id = (select market_segment_id
788 from ams_party_market_segments mseg
789 where market_segment_flag = 'Y'
790 and mseg.party_id = summ.party_id
791 and rownum = 1);
792 commit;
793 exception when others then
794 rollback;
795 end update_market_segment;
796 ----
797 procedure main_proc(p_start_date date,
798 p_end_date date) is
799 begin
800 --populate_status_data(p_start_date, p_end_date);
801 --populate_party_data (p_start_date, p_end_date);
802 update_market_segment;
803 end main_proc;
804 procedure purge_summary_data (p_start_date date,
805 p_end_date date) is
806 begin
807 -- delete records from bic_customer_summary_all
808 delete from bic_customer_summary_all
809 where period_start_date between p_start_date and p_end_date;
810 commit;
811
812 -- delete records from bic_party_summary
813 delete from bic_party_summary
814 where period_start_date between p_start_date and p_end_date;
815 commit;
816 end purge_summary_data;
817
818 procedure purge_party_summary_data is
819 begin
820
821 -- delete records from bic_party_summary
822 delete
823 from bic_party_summary;
824 -- commit;
825 end purge_party_summary_data;
826
827 procedure purge_customer_summary_data is
828 begin
829
830 -- delete records from bic_customer_summary_all
831 delete
832 from bic_customer_summary_all;
833 -- commit;
834
835 end purge_customer_summary_data;
836 -- End of packqage body
837 end bic_consolidate_cust_data_pkg ;