1 package body bic_summary_extract_pkg as
2 /* $Header: bicsummb.pls 120.8 2006/08/17 07:18:24 vsegu ship $ */
3
4 -- following two variable are used for debugging purpose.
5 -- g_proc_name is set to procedure name in a procedure and g_srl_no
6 -- gives serial number of measure_code being processed. In this way
7 -- person debugging this program can group all the messages related to
8 -- processing of a measure and find the place of error.
9
10
11 g_proc_name_old varchar2(50);
12 g_srl_no number;
13 g_dup_record_error varchar2(10);
14 g_tot_recs_added number;
15 g_delete_flag varchar2(1); -- added by kalyan for delete flag
16
17 -- Global variables for who columns
18 g_last_updated_by number ;
19 g_created_by number ;
20 g_last_update_login number ;
21 g_request_id number ;
22 g_program_application_id number ;
23 g_program_id number ;
24
25 -- global variables for program parameters
26 -- g_period_start_date date;
27 g_period_end_date date;
28 --g_measure_code bic_measures_all.measure_code % type;
29 g_org_id bic_measures_all.org_id % type;
30
31 -- Global value for Activation and attrition periods
32 g_attrition_period bic_profile_values_all.attrition_period % type;
33 g_activation_period bic_profile_values_all.activation_period % type;
34
35 -- Global variables for measure codes
36 g_measure_id_for_retn bic_measures_all.measure_id % type;
37 g_measure_id_for_acqu bic_measures_all.measure_id % type;
38 g_measure_id_for_acti bic_measures_all.measure_id % type;
39
40
41 -- Global variable for debug option
42 g_debug varchar2(10);
43
44 -- global variable for insert scheme
45 g_insert_scheme varchar2(30) := null;
46
47 procedure insert_order_measures;
48 procedure insert_order_delivery_measures;
49 procedure process_sql_type_measures;
50 -------------------------------------------------------------------
51 PROCEDURE debug( debug_str VARCHAR2) IS
52 BEGIN
53 g_debug := fnd_profile.value('BIC_DEBUG');
54 FND_LOG.G_CURRENT_RUNTIME_LEVEL := 1;
55 FND_GLOBAL.APPS_INITIALIZE (fnd_global.user_id,fnd_global.resp_id,fnd_global.prog_appl_id);
56 if g_debug = 'Y' then
57 --This is to meet 11.5.10 logging standards
58 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
59
60 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
61 FND_PROFILE.value('AFLOG_MODULE')||'_'||g_proc_name, debug_str);
62 end if;
63 end if;
64 END debug;
65 procedure extract_calendar(errbuf out NOCOPY varchar2,
66 retcode out NOCOPY number ) is
67 begin
68 -- refresh bic_periods table
69 delete from bic_periods;
70 insert into bic_periods
71 ( ACT_PERIOD_NAME ,
72 LAST_UPDATE_DATE ,
73 CREATION_DATE ,
74 LAST_UPDATED_BY ,
75 CREATED_BY ,
76 START_DATE ,
77 ACT_PERIOD_START_DATE ,
78 ACT_PERIOD_END_DATE ,
79 ACT_YEAR ,
80 ACT_PERIOD_NUM ,
81 ACT_QUARTER ,
82 ACT_YEAR_START_DATE ,
83 ACT_QUARTER_START_DATE ,
84 ACT_HALF_YEAR )
85 SELECT period_name act_period_name
86 , sysdate
87 , sysdate
88 , 0
89 , 0
90 , start_date
91 , start_date act_period_start_date
92 , end_date act_period_end_date
93 , period_year act_year
94 , period_num act_period_num
95 , quarter_num act_quarter
96 , year_start_date act_year_start_date
97 , quarter_start_date act_quarter_start_date
98 , decode(quarter_num,1,1,2,1,3,2,4,2,null) act_half_year
99 from gl_periods gprd
100 WHERE
101 PERIOD_SET_NAME=FND_PROFILE.VALUE('CRMBIS:PERIOD_SET_NAME')
102 AND ADJUSTMENT_PERIOD_FLAG <>'Y'
103 AND PERIOD_TYPE=FND_PROFILE.VALUE('CRMBIS:PERIOD_TYPE')
104 ;
105 commit;
106
107 --------- Bic_periods table refreshed.
108 end;
109
110 --------------- functions / procedures new addition-------------
111 procedure generate_error(p_measure_code varchar2 default null, msg varchar2) is
112 status varchar2(10);
113 measure_code varchar2(50);
114 begin
115 if p_measure_code is null then
116 measure_code := 'MAIN';
117 else
118 measure_code := p_measure_code;
119 end if;
120 if measure_code is not null or measure_code = 'MAIN' then
121 status := 'ERROR';
122 else
123 status := 'WARNING';
124 end if;
125 if (FND_CONCURRENT.SET_COMPLETION_STATUS (status,msg)) = true then
126 null;
127 else
128 write_log(' setting the status failed');
129 end if;
130 end generate_error;
131
132 function if_exists ( p_date date ) return boolean is
133 cnt number;
134 begin
135 cnt := 0;
136 select count(*) into cnt
137 from bic_dimv_time
138 where --act_period_start_date = trunc(p_date,'MONTH');
139 trunc(p_date) between trunc(act_period_start_date) and trunc(act_period_end_date); --bug 4308058
140 if cnt = 1 then
141 return true;
142 else
143 return false;
144 end if;
145 exception when others then
146 write_log(' The date ' || p_date || ' does not exist in bic_dimv_time ');
147 return false;
148 end;
149
150 function set_periods_exist ( p_start_date varchar2,
151 p_end_date varchar2
152 ) return boolean is
153 x_date date;
154 begin
155 write_log('set_periods_exist entered');
156 if g_measure_code is null then
157 if p_start_date is not null then
158 g_period_start_date := fnd_date.canonical_to_date(p_start_date);
159 else
160 select fnd_profile.value('BIC_SMRY_EXTRACTION_DATE') into x_date
161 from dual;
162 if x_date is null then
163 write_log('Error: Start Date not Specified and Profile Option' ||
164 ' BIC_SMRY_EXTRACTION_DATE is not set');
165 generate_error('Main',' Profile Option BIC_SMRY_EXTRACTION_DATE is not set' );
166 return false;
167 end if;
168 select act_period_end_date +1 into g_period_start_date
169 from bic_dimv_time
170 where trunc(act_period_start_date) = trunc(x_date);
171 write_log('Start Date as obtained from Profile Value:'||
172 to_char(g_period_start_date,'dd-mm-yyyy'));
173 end if;
174 if p_end_date is not null then
175 g_period_end_date := fnd_date.canonical_to_date(p_end_date);
176 else
177 select max(act_period_start_date) into g_period_end_date
178 from bic_dimv_time
179 where trunc(act_period_end_date) < trunc(sysdate);
180 write_log('End Date set as:'||to_char(g_period_end_date,'dd-mm-yyyy'));
181 end if;
182 else
183 if ( (p_start_date is null) OR (p_end_date is null) ) then
184 write_log('Error: Start Date or End Date should not be null
185 while extracting only for a measure_code ');
186 generate_error('Main',' Error: Start Date or End Date should not be null
187 while extracting only for a measure_code ' );
188 return false;
189 else
190 g_period_start_date := fnd_date.canonical_to_date(p_start_date);
191 g_period_end_date := fnd_date.canonical_to_date(p_end_date);
192 end if;
193 end if;
194 if ( if_exists(g_period_start_date) AND
195 if_exists(g_period_end_date) ) then
196 null;
197 else
198 generate_error('Main',' Start Date or End Date doesnot exist in bic_dimv_time ' );
199 return false;
200 end if;
201 return true;
202 exception when others then
203 write_log(' Exception occurred in set_periods_exist function ');
204 generate_error('Main',' Exception occurred in set_periods_exist function ' );
205 return false;
206 end;
207
208 procedure extract_periods( p_start_date date,
209 p_end_date date,
210 p_measure_code varchar2,
211 p_org_flag varchar2,
212 p_delete_flag varchar2,
213 p_org_id number ) IS
214
215 TYPE curTyp IS REF CURSOR;
216 per_cur curTyp;
217 org_str varchar2(3000);
218 n_org_str varchar2(3000);
219 rec_temp bic_temp_periods%ROWTYPE;
220 rec bic_dimv_time%ROWTYPE;
221 i number;
222 errcode number;
223 errmesg varchar2(200);
224 begin
225 org_str := ' select distinct bdt.* , hou.organization_id
226 from hr_operating_units hou,
227 fnd_product_groups ,
228 bic_party_summ db ,
229 bic_dimv_time bdt
230 where product_group_id = 1
231 and multi_org_flag = ''Y''
232 and ACT_PERIOD_START_DATE between :p_start_date
233 and :p_end_date
234 and hou.organization_id = db.org_id (+)
235 and hou.organization_id = nvl ( :p_org_id , hou.organization_id)
236 and not exists (
237 select 1
238 from bic_party_summ bps
239 where bdt.ACT_PERIOD_START_DATE = bps.PERIOD_START_DATE
240 and bps.org_id = hou.organization_id
241 and '||rtrim(ltrim(p_measure_code))||' is not null ) ' ;
242
243 n_org_str:= ' select *
244 from bic_dimv_time bdt
245 where ACT_PERIOD_START_DATE between :p_start_date
246 and :p_end_date
247 and not exists (
248 select 1
249 from bic_party_status_summ bps
250 where bdt.ACT_PERIOD_START_DATE = bps.PERIOD_START_DATE
251 and '||rtrim(ltrim(p_measure_code))||' is not null ) ' ;
252 if (p_delete_flag = 'Y' ) then
253 write_log('Exiting from fill_dates as delete flag is Y');
254 return ;
255 end if;
256 delete from bic_temp_periods;
257 if ( p_org_flag = 'Y' ) then
258 OPEN per_cur FOR org_str
259 USING p_start_date,
260 p_end_date,
261 p_org_id;
262 LOOP
263 FETCH per_cur INTO rec_temp ;
264 EXIT WHEN per_cur%NOTFOUND;
265 insert into bic_temp_periods
266 ( ACT_PERIOD_NAME, START_DATE, ACT_PERIOD_START_DATE, ACT_PERIOD_END_DATE, ACT_YEAR,
267 ACT_PERIOD_NUM, ACT_QUARTER, ACT_YEAR_START_DATE, ACT_QUARTER_START_DATE, ACT_HALF_YEAR , ORG_ID ) values
268 ( rec_temp.ACT_PERIOD_NAME, rec_temp.START_DATE, rec_temp.ACT_PERIOD_START_DATE, rec_temp.ACT_PERIOD_END_DATE, rec_temp.ACT_YEAR,
269 rec_temp.ACT_PERIOD_NUM, rec_temp.ACT_QUARTER, rec_temp.ACT_YEAR_START_DATE, rec_temp.ACT_QUARTER_START_DATE, rec_temp.ACT_HALF_YEAR, rec_temp.ORG_ID ) ;
270 END LOOP;
271 CLOSE per_cur;
272 else
273 OPEN per_cur FOR n_org_str
274 USING p_start_date,
275 p_end_date ;
276 LOOP
277 FETCH per_cur INTO rec ;
278 EXIT WHEN per_cur%NOTFOUND;
279 insert into bic_temp_periods
280 ( ACT_PERIOD_NAME, START_DATE, ACT_PERIOD_START_DATE, ACT_PERIOD_END_DATE, ACT_YEAR,
281 ACT_PERIOD_NUM, ACT_QUARTER, ACT_YEAR_START_DATE, ACT_QUARTER_START_DATE, ACT_HALF_YEAR ) values
282 ( rec.ACT_PERIOD_NAME, rec.START_DATE, rec.ACT_PERIOD_START_DATE, rec.ACT_PERIOD_END_DATE, rec.ACT_YEAR,
283 rec.ACT_PERIOD_NUM, rec.ACT_QUARTER, rec.ACT_YEAR_START_DATE, rec.ACT_QUARTER_START_DATE, rec.ACT_HALF_YEAR ) ;
284 END LOOP;
285 CLOSE per_cur;
286 end if;
287 commit;
288 end;
289
290 procedure extract_all_periods ( p_start_date date,
291 p_end_date date ) IS
292 begin
293 delete from bic_temp_periods;
294 insert into bic_temp_periods (
295 SELECT bdt.act_period_name , bdt.start_date , bdt.act_period_start_date ,
296 bdt.act_period_end_date , bdt.act_year , bdt.act_period_num ,
297 bdt.act_quarter , bdt.act_year_start_date , bdt.act_quarter_start_date ,
298 bdt.act_half_year , null
299 FROM bic_dimv_time bdt
300 where ACT_PERIOD_START_DATE between p_start_date
301 and p_end_date );
302 commit;
303 end;
304
305 ---------------new functions / procedures end -------------
306
307 function get_measure_id(p_measure_code varchar2) return number is
308 x_measure_id bic_measures_all.measure_id % type;
309 cursor measure_id_cur is
310 select measure_id
311 from bic_measures_all
312 where org_id is null
313 and measure_code = p_measure_code;
314 begin
315 open measure_id_cur;
316 fetch measure_id_cur into x_measure_id;
317 if measure_id_cur % notfound then
318 close measure_id_cur;
319 return(0);
320 end if;
321 close measure_id_cur;
322 return(x_measure_id);
323 end;
324 function convert_amt(p_from_currency_code varchar2,
325 p_date date,
326 p_amt number) return number is
327 x_converted_amt number;
328 begin
329 g_to_currency_code := FND_PROFILE.VALUE('CRMBIS:CURRENCY_CODE');
330 g_conversion_type := FND_PROFILE.VALUE('CRMBIS:GL_CONVERSION_TYPE');
331 if p_from_currency_code is null then return p_amt;
332 end if;
333 x_converted_amt := gl_currency_api.convert_amount_sql(
334 x_from_currency => p_from_currency_code,
335 x_to_currency => g_to_currency_code,
336 x_conversion_date => p_date,
337 x_conversion_type => g_conversion_type,
338 x_amount => p_amt);
339 return(x_converted_amt);
340 end;
341
342 -- This procedure checks value of profile option, BIC_DEBUG and if its value
343 -- is 'Y' then it sets g_log_output variable and message are inserted into
344 -- bic_debug table.
345 procedure set_debug is
346 begin
347 g_debug := fnd_profile.value('BIC_DEBUG');
348 exception
349 when others then null;
350 end set_debug;
351 --
352 --This procedure writes debug messages
353 procedure write_debug_msg(p_msg varchar2) is
354 begin
355 if g_debug = 'Y' then
356 insert into bic_debug ( report_id,message,creation_date)
357 values ('BICCSUMM' || to_char(g_srl_no,'999'),
358 to_char(g_srl_no,'99') || '-'|| p_msg || ': ' ||
359 g_proc_name || ': ' ||
360 to_char(sysdate,'HH24:mi:ss'),
361 sysdate
362 );
363 commit;
364 end if;
365 exception
366 when others then null;
367 end write_debug_msg;
368 --
369 -- This procedure log errors and information messages
370 -- The variable g_log_output is set to null by default. In this way when
371 -- program is executed by concurrent manager, output is printed using
372 -- fnd_file.put_line procedure. When this procedure is being executed from SQL
373 -- prompt, user(mainly programmer) can set g_log_output to any not null value
374 -- in this way log output will be printed via dbms_output.put_line because
375 -- fnd_file.put_line does not work from SQL prompt.
376 function measure_disabled(p_measure_code varchar2) return varchar2 is
377 x_disable_flag bic_measure_attribs.disable_flag % type;
378 begin
379 select disable_flag into x_disable_flag
380 from bic_measure_attribs
381 where measure_code = p_measure_code;
382
383 return(nvl(x_disable_flag,'N'));
384
385 exception
386 when others then
387 -- write_log(sqlerrm);
388 return('Y');
389 end; -- measure_disabled
390 --
391 -- This procedure gets activation and attrition period. These values are
392 -- applicable to all org_ids
393
394 function get_activation_period return boolean is
395 cursor profile_cur is
396 select nvl(activation_period,90)
397 from bic_profile_values_all;
398 begin
399 g_proc_name := 'Get_Profile_values';
400 open profile_cur;
401 fetch profile_cur into g_activation_period;
402 if profile_cur % notfound then
403 --write_log('Bic_profile_values_all does not have any records.' ||
404 -- 'Contact Your System Administrator.');
405 close profile_cur;
406 return(false);
407 end if;
408 close profile_cur;
409 return(true);
410 end;
411
412 function get_attrition_period return boolean is
413 cursor profile_cur is
414 select nvl(ATTRITION_PERIOD,2)
415 from bic_profile_values_all;
416 begin
417 g_proc_name_old := g_proc_name;
418 g_proc_name := 'Get_attrition_period';
419 write_log('get_attrition_period entered');
420 open profile_cur;
421 fetch profile_cur into g_attrition_period;
422 if profile_cur % notfound then
423 -- write_log('Bic_profile_values_all does not have any records.' ||
424 -- 'Contact Your System Administrator.');
425 close profile_cur;
426 g_proc_name := g_proc_name_old;
427 return(false);
428 end if;
429 close profile_cur;
430 g_proc_name := g_proc_name_old;
431 return(true);
432 end;
433
434 --
435 -- This procedure gives the value of a given measure_id for a given customer
436 -- and period_start_date.
437 procedure get_measure_value (p_period_start_date date,
438 p_customer_id number,
439 p_measure_id number,
440 p_value in out NOCOPY number) as
441 begin
442 g_proc_name_old := g_proc_name;
443 g_proc_name := 'get_measure_value';
444 debug('entered +');
445 select value into p_value
446 from bic_customer_summary_all
447 where period_start_date = p_period_start_date
448 and customer_id = p_customer_id
449 and measure_id = p_measure_id;
450 debug('entered +');
451 g_proc_name := g_proc_name_old;
452 exception
453 when no_data_found then
454 p_value := 0;
455 -- the error message is not being generated because it possible that
456 -- a measure does not exist for a customer. For example a customer may
457 -- not have logger any service request in a given period and in that
458 -- case return of zero is the right value.
459 /* when others then
460 write_log('exception occurred for this measure_id : '||p_measure_id);*/
461 end get_measure_value;
462
463 -- This procedure gives measure_id for a given measure code and org
464 procedure get_measure_id(p_measure_code varchar2,
465 p_org_id number,
466 p_measure_id in out NOCOPY number) as
467 begin
468 g_proc_name_old := g_proc_name;
469 g_proc_name := 'get_measure_id';
470 select measure_id into p_measure_id
471 from bic_measures_all
472 where measure_code = p_measure_code
473 and nvl(org_id,-1) = nvl(p_org_id,-1);
474
475 g_proc_name := g_proc_name_old;
476 exception
477 when no_data_found then
478 write_log('Measure id not found in bic_measures_all for measure ' ||
479 'Code:' ||p_measure_code || ' & Org Id:'||to_char(p_org_id));
480 null;
481
482 end get_measure_id;
483
484 -- This procedure finds the weight of a given measure_id.
485 procedure get_weight (p_measure_id number,
486 p_weight in out NOCOPY number,
487 p_measure_code in out NOCOPY varchar2) as
488 begin
489 g_proc_name_old := g_proc_name;
490 g_proc_name := 'get_weight';
491 debug('entered +');
492 -- Key to this table is measure id, so org_id is not needed
493 -- in where condition
494 debug('get_weight entered for measure_id : '||p_measure_id);
495 select nvl(weight,0) , measure_code
496 into p_weight, p_measure_code
497 from bic_measures_all
498 where measure_id = p_measure_id;
499 debug('weight : '||p_weight || ' for measure_code : '||p_measure_code);
500 debug('entered +');
501 g_proc_name := g_proc_name_old;
502 exception
503 when no_data_found then
504 p_weight := 0;
505 --write_log('Measure Id:'|| to_char(p_measure_id) || '
506 -- does not exits in bic_measures_all table');
507
508 end get_weight;
509
510 -- This procedure finds bucket id, bucket points for a given measure id and
511 -- value.
512 procedure get_bucket (p_measure_id number,
513 p_value number,
514 p_bucket_id in out NOCOPY number,
515 p_bucket_points in out NOCOPY number) as
516 cursor bucket_cur is
517 select bucket_id, bucket_points --into p_bucket_id, p_bucket_points
518 from bic_measure_buckets
519 where nvl(p_value,0) >= nvl(low_value,0)
520 and nvl(p_value,0) < nvl(high_value,p_value+2)
521 and measure_id = p_measure_id
522 order by low_value;
523 begin
524 g_proc_name_old := g_proc_name;
525 g_proc_name := 'get_bucket';
526 -- nvl of null high value is set to p_value +2 so that
527 -- p_value less than high_value condition is true.
528 open bucket_cur;
529 fetch bucket_cur into p_bucket_id, p_bucket_points;
530 if bucket_cur % notfound then
531 p_bucket_id := null;
532 p_bucket_points := 0;
533 end if;
534 close bucket_cur;
535
536 g_proc_name := g_proc_name_old;
537 exception
538 when no_data_found then
539 -- If no bucket data is found then return bucket points as 0
540 -- and bucket id as null.
541 p_bucket_id := null;
542 p_bucket_points := 0;
543
544 end get_bucket;
545
546 -- This procedure updates score and bucket id fields of
547 -- bic_customer_summary_all table. For selected records from
548 -- bic_customer_summary_all table, it finds the weight of the measure,
549 -- bucket points and bucket id and updates score as weight*bucket_points.
550 procedure update_score is
551 cursor cust_summary_recs is
552 select measure_id, value, org_id
553 from bic_customer_summary_all
554 where bucket_id = -1
555 and trunc(period_start_date)
556 between trunc(g_period_start_date) and trunc(g_period_end_date)
557 and (g_org_id is null or g_org_id = org_id)
558 for update of score, bucket_id;
559 x_value bic_customer_summary_all.value % type;
560 x_org_id bic_customer_summary_all.org_id % type;
561 x_measure_id bic_customer_summary_all.measure_id % type;
562 x_bucket_points bic_measure_buckets.bucket_points % type;
563 x_bucket_id bic_measure_buckets.bucket_id % type;
564 x_weight bic_measures_all.weight % type;
565 x_measure_code bic_measures_all.measure_code % type;
566 begin
567 g_proc_name_old := g_proc_name;
568 g_proc_name := 'Update_score';
569
570 open cust_summary_recs;
571 loop
572 fetch cust_summary_recs into x_measure_id, x_value, x_org_id;
573 if cust_summary_recs % notfound then exit; end if;
574 get_weight(x_measure_id,x_weight,x_measure_code);
575 get_bucket(x_measure_id,x_value,x_bucket_id,x_bucket_points);
576 --write_log('Weight:'||to_char(x_weight) ||
577 -- ' Bucket Point:'||to_char(x_bucket_points));
578 update bic_customer_summary_all
579 set bucket_id = x_bucket_id,
580 measure_code = x_measure_code,
581 score = nvl(x_weight * x_bucket_points,0)
582 where current of cust_summary_recs;
583 end loop;
584
585 g_proc_name := g_proc_name_old;
586
587 end update_score;
588
589 -- This procedure gets neasure_id, score, bucket id for a given measure_code
590 -- and inserts into bic_customer_summary_all table.
591 procedure insert_record(p_measure_code varchar2,
592 p_period_start_date date,
593 p_customer_id number,
594 p_value number,
595 p_org_id number,
596 p_index varchar2 default null) as
597 x_measure_id bic_measures_all.measure_id % type;
598 x_bucket_id bic_customer_summary_all.bucket_id % type;
599 x_weight bic_measures_all.weight % type;
600 x_bucket_points bic_measure_buckets.bucket_points % type;
601 x_score bic_customer_summary_all.score % type;
602 x_dummy bic_measures_all.measure_code % type;
603 begin
604 -- do not insert ant record if value is null.
605 /* if p_value is null or p_value = 0 then return; end if;*/
606 if p_value is null then return; end if;
607
608 g_proc_name_old := g_proc_name;
609 g_proc_name := 'insert_record';
610 debug(' entered +');
611 g_dup_record_error := null;
612 get_measure_id(p_measure_code,p_org_id, x_measure_id);
613 if p_index is null or p_index <> 'MIX' then
614 get_weight(x_measure_id,x_weight,x_dummy);
615 -- If weight = 0 then it's score will be zero and it will
616 -- have no impact on parent measures' value. So do not insert
617 -- such records.
618 if x_weight = 0 then return; end if;
619 end if;
620 if p_index is null then
621 get_bucket(x_measure_id,p_value,x_bucket_id,x_bucket_points);
622 end if;
623 if p_index is null then
624 x_score := x_bucket_points * x_weight;
625 elsif p_index = 'SIX' then
626 x_score := p_value * x_weight;
627 else
628 x_score := null;
629 end if;
630 insert into bic_customer_summary_all (
631 MEASURE_ID ,
632 PERIOD_START_DATE ,
633 CUSTOMER_ID ,
634 BUCKET_ID ,
635 VALUE ,
636 LAST_UPDATE_DATE ,
637 LAST_UPDATED_BY ,
638 CREATION_DATE ,
639 CREATED_BY ,
640 ORG_ID ,
641 LAST_UPDATE_LOGIN ,
642 REQUEST_ID ,
643 PROGRAM_APPLICATION_ID ,
644 PROGRAM_ID ,
645 PROGRAM_UPDATE_DATE ,
646 SCORE ,
647 MEASURE_CODE )
648 values ( x_measure_id , --MEASURE_ID
649 p_period_start_date , --PERIOD_START_DATE
650 p_customer_id , --CUSTOMER_ID
651 x_bucket_id , --BUCKET_ID
652 p_value , --VALUE
653 sysdate ,
654 g_last_updated_by ,
655 sysdate ,
656 g_created_by ,
657 p_org_id , -- ORG_ID
658 g_last_update_login ,
659 g_request_id ,
660 g_program_application_id ,
661 g_program_id ,
662 sysdate ,
663 x_score , -- SCORE
664 p_measure_code
665 );
666 debug(' exited +');
667 g_proc_name := g_proc_name_old;
668 exception
669 when dup_val_on_index then
670 g_dup_record_error := 'Yes';
671 write_log('Duplicate records in bic_customer_summary_all:'
672 ||sqlerrm);
673 /* when others then
674 write_log('exception occurred for this measure_id : '||x_measure_id); */
675 end insert_record;
676
677 -- This procedure executes SQL statement
678 procedure run_sql (p_sttmnt varchar2) is
679 x_insert_str varchar2(2000);
680 x_bucket_id bic_customer_summary_all.bucket_id % type;
681 x_score bic_customer_summary_all.score % type;
682 x_from_pos number;
683 x_where_end number;
684 x_extra_cond varchar2(500);
685
686 type t_cursor is REF CURSOR;
687 x_cur t_cursor;
688 x_sql_sttmnt varchar2(32000);
689 x_measure_id bic_customer_summary_all.measure_id % type;
690 x_customer_id bic_customer_summary_all.customer_id % type;
691 x_period_start_date bic_customer_summary_all.period_start_date % type;
692 x_value bic_customer_summary_all.value % type;
693 x_org_id bic_customer_summary_all.org_id % type;
694 x_bucket_points bic_measure_buckets.bucket_points % type;
695 x_weight bic_measures_all.weight % type;
696 x_measure_code bic_measures_all.measure_code % type;
697
698 x_num number;
699 begin
700
701 x_num := 0;
702 g_proc_name_old := g_proc_name;
703 g_proc_name := 'run_sql';
704 -- write_log('sql statement :'||p_sttmnt);
705 if p_sttmnt is null then
706 write_log('Null or disabled SQL statement........');
707 return;
708 end if;
709 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
710 debug(g_proc_name || ' entered at + ');
711
712 -- Location of FROM in SQL statement is needed so that some other fields
713 -- such as login_id, update_date etc can be inserted into SQL statement
714 x_from_pos := instr(upper(p_sttmnt),'FROM' );
715
716 -- Location of end of WHERE word in SQL statement is needed so that extra
717 -- condition can be inserted into SQL statement. The extra condition may
718 -- be needed if org_id is not null.
719 x_where_end := instr(upper(p_sttmnt),'WHERE') + 6;
720
721 x_score := 0;
722 --Bucket id is set to -1 so that all bic_customery_summary_all records can
723 --be selected easily for update of scores and bucket ids in update_score
724 --procedure.
725 x_bucket_id := -1;
726
727 x_extra_cond := 'exists (select 1 from bic_temp_periods btp where btp.start_date = bdt.start_date) and';
728
729 if g_delete_flag = 'N' then
730
731 x_extra_cond := 'exists (select 1 from bic_temp_periods btp where btp.start_date = bdt.start_date and btp.org_id = bma.org_id) and ';
732 end if;
733
734 if g_org_id is not null then
735 x_extra_cond := x_extra_cond || ':p_org_id = bma.org_id and ';
736 else
737 x_extra_cond:= x_extra_cond || ' ' ;
738 -- write_log('extra condition : '||x_extra_cond);
739 end if;
740 x_insert_str := substr(p_sttmnt,1,x_where_end -1) ||
741 x_extra_cond ||
742 substr(p_sttmnt,x_where_end)
743 ;
744 debug('sql query : '|| x_insert_str);
745 if g_org_id is null then
746 open x_cur for x_insert_str using g_period_start_date,
747 g_period_end_date;
748
749 else
750 open x_cur for x_insert_str using g_org_id,
751 g_period_start_date,
752 g_period_end_date;
753
754 end if;
755 -- write_log('cursor opened in run_sql');
756 loop
757 fetch x_cur into x_measure_id, x_customer_id, x_period_start_date,
758 x_org_id, x_value;
759
760 if x_cur%notfound then
761 exit;
762 end if;
763 debug('calling get_weight for measure_id : '||x_measure_id);
764 -- write_log('calling get_weight for measure_id : '||x_measure_id);
765 get_weight(x_measure_id,x_weight,x_measure_code);
766 debug('got weight for measure id : '||x_measure_id);
767 debug('got weight for measure id : '|| x_measure_id);
768 -- if nvl(x_value,0) <> 0 and x_weight <> 0 then
769 /* calculate measure even if the weight is zero as only the direct sub measures of a measure are shown in the setup
770 Ex: in the setup page of BILLING only the measure 'ON_TIME_PAYMENT_RATE' is shown*/
771 if nvl(x_value,0) <> 0 then
772 debug('calling get_bucket for measure_id : '||x_measure_id);
773 get_bucket(x_measure_id,x_value,x_bucket_id,x_bucket_points);
774 /* write_log('got bucket for measure id : '||x_measure_id);
775 write_log('x_measure_id :'||x_measure_id);
776 write_log('x_customer_id :'||x_customer_id);
777 write_log('x_period_start_date :'||x_period_start_date);
778 write_log('x_org_id :'||x_org_id);
779 write_log('x_value :'||x_value);
780 write_log('g_last_updated_by :'||g_last_updated_by);
781 write_log('x_bucket_points : '||x_bucket_points);
782 write_log('g_created_by : '||g_created_by);*/
783
784 insert into bic_customer_summary_all (
785 MEASURE_ID
786 ,CUSTOMER_ID
787 ,PERIOD_START_DATE
788 ,ORG_ID
789 ,VALUE
790 ,BUCKET_ID
791 ,LAST_UPDATE_DATE
792 ,LAST_UPDATED_BY
793 ,CREATION_DATE
794 ,CREATED_BY
795 ,LAST_UPDATE_LOGIN
796 ,REQUEST_ID
797 ,PROGRAM_APPLICATION_ID
798 ,PROGRAM_ID
799 ,PROGRAM_UPDATE_DATE
800 ,SCORE
801 ,MEASURE_CODE)
802 values (
803 x_measure_id
804 ,x_customer_id
805 ,x_period_start_date
806 ,x_org_id
807 ,x_value
808 ,x_bucket_id
809 ,sysdate
810 ,g_last_updated_by
811 ,sysdate
812 ,g_created_by
813 ,g_last_update_login
814 ,g_request_id
815 ,g_program_application_id
816 ,g_program_id
817 ,sysdate
818 ,x_bucket_points*x_weight
819 ,x_measure_code)
820 ;
821
822 end if;
823
824 end loop;
825 -- write_log('No of Records Inserted='||to_char(x_num));
826 --write_log (sysdate);
827 debug('exited - ');
828 g_proc_name := g_proc_name_old;
829 exception
830 when dup_val_on_index then
831 debug('duplicate record entered');
832
833 end run_sql;
834
835 -- This procedure finds out NOCOPY if summary records are already created for a
836 -- given period and measure code.
837
838 -- This procedure insert record into bic_customer_summary_all for measure_codes
839 -- of type 'formula'
840 procedure run_fml (p_measure_code varchar2,
841 p_mult_factor number ) is
842 cursor cust_and_dates is
843 select distinct period_start_date, customer_id, bma.org_id
844 from bic_customer_summary_all bcs,
845 bic_measure_hierarchy bmh,
846 bic_measures_all bma
847 where bcs.measure_id = bma.measure_id
848 and bmh.measure_code = bma.measure_code
849 and bmh.parent_measure_code = p_measure_code
850 and trunc(bcs.period_start_date)
851 between trunc(g_period_start_date) and trunc(g_period_end_date)
852 and (g_org_id is null or g_org_id = bma.org_id);
853
854 x_value bic_customer_summary_all.value % type;
855 x_value1 bic_customer_summary_all.value % type;
856 x_value2 bic_customer_summary_all.value % type;
857 x_measure_id1 bic_measures_all.measure_id % type;
858 x_measure_id2 bic_measures_all.measure_id % type;
859 x_measure_code1 bic_measures_all.measure_code % type;
860 x_measure_code2 bic_measures_all.measure_code % type;
861 x_operation_code1 bic_measure_hierarchy.operation_code % type;
862 x_operation_code2 bic_measure_hierarchy.operation_code % type;
863 x_customer_id bic_customer_summary_all.customer_id % type;
864 x_org_id bic_measures_all.org_id % type;
865 x_period_start_date date;
866
867 cursor childs_cur (cp_parent_measure_code varchar2) is
868 select measure_code, operation_code
869 from bic_measure_hierarchy
870 where parent_measure_code = cp_parent_measure_code
871 order by sequence_number ;
872 begin
873 g_proc_name_old := g_proc_name;
874 g_proc_name := 'run_fml';
875 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
876 debug(' entered + ');
877
878 open childs_cur(p_measure_code);
879 -- Get first child
880 fetch childs_cur into x_measure_code1, x_operation_code1;
881 if childs_cur % notfound then
882 bic_summary_extract_pkg.write_log('First child of '|| p_measure_code || ' Not found...');
883 close childs_cur;
884 return;
885 end if;
886 -- Get second child
887 fetch childs_cur into x_measure_code2, x_operation_code2;
888 if childs_cur % notfound then
889 bic_summary_extract_pkg.write_log('Second child of '|| p_measure_code || ' Not found...');
890 close childs_cur;
891 return;
892 end if;
893 close childs_cur;
894
895 open cust_and_dates;
896 loop
897 fetch cust_and_dates into x_period_start_date, x_customer_id,
898 x_org_id;
899 if cust_and_dates%notfound then
900 exit;
901 end if;
902
903 get_measure_id(x_measure_code1,
904 x_org_id,
905 x_measure_id1);
906 get_measure_id(x_measure_code2,
907 x_org_id,
908 x_measure_id2);
909 get_measure_value (x_period_start_date,
910 x_customer_id,
911 x_measure_id1,
912 x_value1);
913 get_measure_value (x_period_start_date,
914 x_customer_id,
915 x_measure_id2,
916 x_value2);
917 if x_value2 = 0 then
918 bic_summary_extract_pkg.write_log('Value of second measure is 0:');
919 elsif x_operation_code1 = '/' then
920 x_value := x_value1*p_mult_factor/x_value2;
921 else
922 bic_summary_extract_pkg.write_log('No procedure to handle operation_code:'||
923 x_operation_code1);
924 end if;
925 insert_record(p_measure_code ,
926 x_period_start_date ,
927 x_customer_id ,
928 x_value ,
929 x_org_id ,
930 null );
931 --commented as the exception is caught in insert_record
932 /* if g_dup_record_error is not null then
933 rollback;
934 exit;
935 end if;*/
936 end loop;
937 bic_summary_extract_pkg.write_log('No of Recrods Inserted='||to_char(cust_and_dates%rowcount));
938 close cust_and_dates;
939 --write_log(' procedure '||g_proc_name || ' exited at : '||sysdate);
940 debug(' exited -');
941
942 g_proc_name := g_proc_name_old;
943
944
945 end run_fml;
946
947 -- This procedure inserts records into bic_customer_summary_all for measure
948 -- codes which are of type 'Formula with dates'. for example measure code
949 -- Average Service Requests Per Day is calculated as SRS logged divided by
950 -- days in a given period
951 procedure run_fmd (p_measure_code varchar2) is
952 cursor cust_and_dates is
953 select period_start_date, customer_id, bma.org_id,
954 bcs.measure_id, bcs.value, bmh.operation_code
955 from bic_customer_summary_all bcs,
956 bic_measure_hierarchy bmh,
957 bic_measures_all bma
958 where bcs.measure_id = bma.measure_id
959 and trunc(bcs.period_start_date)
960 between trunc(g_period_start_date) and trunc(g_period_end_date)
961 and bmh.measure_code = bma.measure_code
962 and bmh.parent_measure_code = p_measure_code
963 and (g_org_id is null or g_org_id = bma.org_id);
964 x_value bic_customer_summary_all.value % type;
965 x_value1 bic_customer_summary_all.value % type;
966 x_value2 bic_customer_summary_all.value % type;
967 x_measure_id1 bic_measures_all.measure_id % type;
968 x_measure_id2 bic_measures_all.measure_id % type;
969 x_measure_code1 bic_measures_all.measure_code % type;
970 x_measure_code2 bic_measures_all.measure_code % type;
971 x_operation_code1 bic_measure_hierarchy.operation_code % type;
972 x_operation_code2 bic_measure_hierarchy.operation_code % type;
973 x_period_start_date date;
974 x_customer_id bic_customer_summary_all.customer_id % type;
975 x_org_id bic_measures_all.org_id % type;
976 x_days number;
977
978 begin
979 g_proc_name_old := g_proc_name;
980 g_proc_name := 'run_fmd';
981 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
982 debug(' entered + ');
983
984 open cust_and_dates;
985 loop
986 fetch cust_and_dates into x_period_start_date, x_customer_id,
987 x_org_id, x_measure_id1, x_value1,
988 x_operation_code1;
989 if cust_and_dates%notfound then
990 exit;
991 end if;
992
993 -- 1 is added in following query so that 1/31/99 - 1/1/99 gives 31
994 -- not 30
995 select act_period_end_date - act_period_start_date +1 into x_days
996 from bic_temp_periods
997 where trunc(start_date) = trunc(x_period_start_date)
998 and nvl(org_id,x_org_id) = x_org_id;
999
1000 x_value := x_value1/x_days;
1001 insert_record(p_measure_code ,
1002 x_period_start_date ,
1003 x_customer_id ,
1004 x_value ,
1005 x_org_id ) ;
1006 --commented as the exception is caught in insert_record
1007 /* if g_dup_record_error is not null then
1008 rollback;
1009 exit;
1010 end if;*/
1011 end loop;
1012 bic_summary_extract_pkg.write_log('No of Recrods Inserted='||to_char(cust_and_dates%rowcount));
1013 close cust_and_dates;
1014 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
1015 bic_summary_extract_pkg.debug('exited - ');
1016
1017 g_proc_name := g_proc_name_old;
1018
1019 end run_fmd;
1020
1021 -- This procedure insert records into bic_customer_summary_all for measure
1022 -- codes which are sub indexes or main index
1023 procedure run_index (p_measure_code varchar2,
1024 p_index varchar2) is
1025 cursor index_recs is
1026 select bcs.customer_id, bcs.org_id, bcs.period_start_date,
1027 sum(nvl(bcs.score,0)), sum(nvl(bma.weight,0)),count(1)
1028 from bic_measure_hierarchy bmh,
1029 bic_measures_all bma,
1030 bic_customer_summary_all bcs
1031 where bmh.parent_measure_code = p_measure_code
1032 and bmh.measure_code = bma.measure_code
1033 and (bma.org_id = g_org_id or g_org_id is null)
1034 and bma.measure_id = bcs.measure_id
1035 and trunc(bcs.period_start_date)
1036 between trunc(g_period_start_date) and trunc(g_period_end_date)
1037 group by bcs.customer_id, bcs.org_id,bcs.period_start_date;
1038
1039 x_period_start_date bic_customer_summary_all.period_start_date % type;
1040 x_customer_id bic_customer_summary_all.customer_id % type;
1041 x_total_weight bic_measures_all.weight % type;
1042 x_total_score bic_customer_summary_all.score % type;
1043 x_value bic_customer_summary_all.value % type;
1044 x_org_id bic_customer_summary_all.org_id % type;
1045 x_cnt number;
1046 x_msr_cd varchar2(50);
1047 begin
1048 g_proc_name_old := g_proc_name;
1049 g_proc_name := 'run_index';
1050 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
1051 bic_summary_extract_pkg.debug(' entered + ');
1052
1053 open index_recs;
1054 loop
1055 fetch index_recs into x_customer_id, x_org_id, x_period_start_date,
1056 x_total_score, x_total_weight, x_cnt;
1057 if index_recs % notfound then exit; end if;
1058 if x_total_weight <> 0 then
1059 x_value := x_total_score/ x_total_weight;
1060 --There is no need to insert record with value=0 as it will
1061 -- not contribute to main index. main index is sub index value times
1062 -- weight and main index with 0 value is of no use.
1063 insert_record(p_measure_code,
1064 x_period_start_date,
1065 x_customer_id,
1066 x_value,
1067 x_org_id,
1068 p_index);
1069 --else
1070 --x_value := 0;
1071 end if;
1072 --commented as the exception is caught in insert_record
1073 /* if g_dup_record_error is not null then
1074 rollback;
1075 exit;
1076 end if;*/
1077 end loop;
1078 bic_summary_extract_pkg.write_log('No of Recrods Inserted='||to_char(index_recs%rowcount));
1079 close index_recs;
1080 g_proc_name := g_proc_name_old;
1081 --write_log(' procedure '||g_proc_name || ' exited at : '||sysdate);
1082 bic_summary_extract_pkg.debug(g_proc_name || ' exited at : '|| sysdate);
1083
1084 -- if any exception occurs entire satisfaction should not be calculated
1085 /* exception
1086 when others then
1087 write_log('exception occurred for this customer_id : '||x_customer_id); */
1088 end run_index;
1089
1090 -- This procedure returns SQL statement, operation type and multiplication
1091 -- factor associated with a measure code.
1092 procedure get_sql_sttmnt(p_measure_code varchar2,
1093 p_sttmnt in out NOCOPY varchar2,
1094 p_operation_type in out NOCOPY varchar2,
1095 p_mult_factor in out NOCOPY number) as
1096 begin
1097 g_proc_name_old := g_proc_name;
1098 g_proc_name := 'Get_SQL_Sttmnt';
1099 debug('entered +');
1100 select sql_statement, operation_type, nvl(mult_factor,1)
1101 into p_sttmnt, p_operation_type, p_mult_factor
1102 from bic_measure_attribs
1103 where measure_code = p_measure_code
1104 and nvl(disable_flag,'N') <> 'Y';
1105 debug('exited +');
1106 g_proc_name := g_proc_name_old;
1107 exception
1108 when no_data_found then
1109 bic_summary_extract_pkg.write_log(' Measure Code:'|| p_measure_code || 'might be disabled or there is no entry in Bic_measure_attribs for');
1110 g_proc_name := g_proc_name_old;
1111 end;
1112 -- This procedure extracts data for SATISFACTION and/or LOYALTY measure codes
1113 procedure extract_proc(p_measure_code varchar2) is
1114
1115
1116 x_measure_code bic_measures_all.measure_code % type;
1117 x_mult_factor bic_measure_attribs.mult_factor % type;
1118 x_operation_type bic_measure_attribs.operation_type % type;
1119 x_level number(5);
1120 x_sttmnt varchar2(2000);
1121
1122
1123 -- Purpose of this query is get measure code which are at lowest level first.
1124 -- At lowest level, you will always have measure codes with SQL statements.
1125 -- In this way when a measure cd calculation is done using other measure code
1126 -- those measure codes are already calculated.
1127 -- cursor gives measures in the order of their level. Highest level first..
1128
1129 cursor measure_cur is
1130 select measure_code, max(level)
1131 from bic_measure_hierarchy
1132 where measure_code not in ('REFERALS','INTERAC_CUML')
1133 start with parent_measure_code = p_measure_code
1134 connect by prior measure_code = parent_measure_code
1135 group by measure_code
1136 order by 2 desc, 1;
1137 begin
1138 g_proc_name_old := g_proc_name;
1139 g_proc_name := 'Extract_Proc';
1140
1141 g_srl_no := 0;
1142
1143
1144 open measure_cur;
1145 loop
1146
1147 g_srl_no := g_srl_no + 1;
1148 g_proc_name := 'extract_proc';
1149 debug('entered +');
1150 fetch measure_cur into x_measure_code, x_level;
1151 if measure_cur % notfound then
1152 exit;
1153 end if;
1154 x_operation_type := null;
1155
1156 get_sql_sttmnt(x_measure_code, x_sttmnt, x_operation_type, x_mult_factor);
1157
1158 debug('Measure Code being processed:'||x_measure_code ||':'||
1159 x_operation_type);
1160 -- Operation Types:
1161 -- SQL: It means value of measure code is calculated using a SQL
1162 -- statement
1163 -- FML: It means value of measure code is calculated using a formula
1164 -- involving two other measures and it is caluclated as
1165 -- measure code1 operator measure code2 * multiplication factor
1166 -- multiplication factor is 100 in case of percentages
1167 -- FMD: It means the value of a measure is calculated using another
1168 -- measure and no of days in a certain period. It is calculated as
1169 -- measure code1 / no of days in a period
1170 if x_operation_type = 'SQL' then
1171 --if x_sttmnt is null then
1172 -- Because all measures of SQL type are already processed using
1173 -- process_sql_type_measures. The procedure below just report
1174 -- the errors. If there are not errors, then part can be removed.
1175 run_sql(x_sttmnt);
1176 --end if;
1177 elsif x_operation_type = 'FML' then
1178 run_fml(x_measure_code,
1179 x_mult_factor);
1180 elsif x_operation_type = 'FMD' then
1181 run_fmd(x_measure_code);
1182 elsif x_operation_type = 'SIX' or x_operation_type = 'MIX' then
1183 run_index(x_measure_code, x_operation_type);
1184 else
1185 -- Fix made for Bug 2397179 ByteMobile/IKON issue
1186 -- This is only a temporary solution. Long term we should
1187 -- define a SQL for the three sub-measures
1188 -- SR_CLOSED_INT, FIRST_CALL_CL_RATE, NO_OF_COMPLAINTS
1189
1190 If x_measure_code NOT IN
1191 ('SR_CLOSED_INT','FIRST_CALL_CL_RATE','NO_OF_COMPLAINTS') Then
1192
1193 write_log('Invalid Operation Type:'|| x_operation_type ||
1194 ':Valid Operation Types are SQL, FML, FMD, SIX, MIX');
1195 End If;
1196
1197 end if;
1198 -- commit;
1199 end loop;
1200 close measure_cur;
1201
1202 debug('Measure code Being Processed:'||p_measure_code);
1203
1204 run_index(p_measure_code, 'MIX');
1205
1206 write_log('Processing of Measures Completed....');
1207 debug('exited -');
1208 g_proc_name := g_proc_name_old;
1209
1210 end extract_proc;
1211
1212 procedure extract_satisfaction is
1213 rec_count number;
1214 begin
1215 g_proc_name_old := g_proc_name;
1216 g_proc_name := 'Extract_satisfaction_data';
1217 write_log('Before satisfaction Extraction...');
1218
1219 extract_periods( g_period_start_date,
1220 g_period_end_date ,
1221 'SATISFACTION',
1222 'Y',
1223 g_delete_flag,
1224 g_org_id);
1225
1226 write_log('Extracted Periods Successfully In Extract_satisfaction...');
1227
1228 select count(*) into rec_count
1229 from bic_temp_periods;
1230
1231 if rec_count = 0 then
1232 write_log('data already exists');
1233 return;
1234 end if;
1235
1236 extract_proc('SATISFACTION');
1237
1238
1239 bic_consolidate_cust_data_pkg.populate_party_data (g_period_start_date,g_period_end_date);
1240 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
1241
1242
1243 Commit;
1244
1245 write_log('Total Records Added for satisfaction:'|| to_char(sql%rowcount));
1246 g_proc_name := 'Extract_Main';
1247
1248 Exception
1249 when others then
1250 write_log('Satisfaction data is not extracted due to : '||sqlerrm);
1251 generate_error(g_measure_code,'Satisfaction data is not extracted due to : '||sqlerrm);
1252 Rollback;
1253 end;
1254
1255 procedure extract_Loyalty is
1256 rec_count number;
1257 begin
1258 g_proc_name_old := g_proc_name;
1259 g_proc_name := 'Extract_Loyalty_data';
1260
1261 extract_periods( g_period_start_date,
1262 g_period_end_date ,
1263 'LOYALTY',
1264 'Y',
1265 g_delete_flag,
1266 g_org_id);
1267
1268 write_log('Extracted Periods Successfully In Extract_Loyalty...');
1269
1270 select count(*) into rec_count
1271 from bic_temp_periods;
1272
1273 if rec_count = 0 then
1274 write_log('Data already extracted');
1275 return;
1276 end if;
1277 extract_proc('LOYALTY');
1278
1279
1280 bic_consolidate_cust_data_pkg.populate_party_data (g_period_start_date,g_period_end_date);
1281
1282 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
1283
1284
1285 Commit;
1286 write_log('Loyalty extraction completed');
1287 -- write_log('Total Records Added for Loyalty:'|| to_char(sql%rowcount));
1288 g_proc_name := 'Extract_Main';
1289
1290 Exception
1291 when others then
1292 write_log('Loyalty data is not extracted due to : '||sqlerrm);
1293 generate_error(g_measure_code,'Loyalty data is not extracted due to : '||sqlerrm);
1294
1295 Rollback;
1296 end;
1297
1298 -- for customer retention records, 'VALUE' column of bic_customer_summary_all
1299 -- table stores customer retention status. The mapping between value of
1300 -- 'VALUE' column and status is given in SRS documnet but are reporduced here
1301 -- too for the convenience of reading.
1302 -- value = 1 means New
1303 -- value = 2 means Reactivated
1304 -- value = 3 means Retained
1305 -- value = 4 means Churned
1306 ---------------------------------
1307
1308 procedure retention_churned is
1309 cursor party_cur is
1310 select party_id, min(nvl(account_established_date,creation_date))
1311 from hz_cust_accounts
1312 group by party_id;
1313 x_party_id hz_cust_accounts.party_id % type;
1314 x_account_established_date hz_cust_accounts.account_established_date % type;
1315 begin
1316 g_proc_name_old := g_proc_name;
1317 g_proc_name := 'Retention_churned';
1318 debug(' entered +');
1319 --g_tot_recs_added := 0;
1320 insert into bic_party_summary (
1321 MEASURE_ID
1322 ,PARTY_ID --CUSTOMER_ID
1323 ,PERIOD_START_DATE
1324 ,VALUE
1325 ,BUCKET_ID
1326 ,LAST_UPDATE_DATE
1327 ,LAST_UPDATED_BY
1328 ,CREATION_DATE
1329 ,CREATED_BY
1330 ,LAST_UPDATE_LOGIN
1331 ,REQUEST_ID
1332 ,PROGRAM_APPLICATION_ID
1333 ,PROGRAM_ID
1334 ,PROGRAM_UPDATE_DATE
1335 ,SCORE
1336 ,measure_code)
1337 select distinct
1338 g_measure_id_for_retn
1339 ,party_id --hca.cust_account_id
1340 ,add_months(period_start_date,g_attrition_period*1)
1341 ,4
1342 ,null
1343 ,sysdate
1344 ,g_last_updated_by
1345 ,sysdate
1346 ,g_created_by
1347 ,g_last_update_login
1348 ,g_request_id
1349 ,g_program_application_id
1350 ,g_program_id
1351 ,sysdate
1352 ,null
1353 ,'RETENTION'
1354 from bic_party_summary psum
1355 where psum.period_start_date
1356 between add_months(g_period_start_date,g_attrition_period*-1)
1357 and add_months(g_period_end_date,g_attrition_period*-1)
1358
1359 and measure_id = g_measure_id_for_retn
1360 and not exists ( select 1 from bic_party_summary psum_in
1361 where psum_in.measure_id = g_measure_id_for_retn
1362 and psum_in.party_id = psum.party_id
1363 and psum_in.period_start_date =
1364 add_months(psum.period_start_date,g_attrition_period)
1365 );
1366
1367
1368 g_tot_recs_added := g_tot_recs_added + sql%rowcount;
1369 debug(' exited - ');
1370 write_log('Total Records Added for Retention Churned:'||to_char(g_tot_recs_added));
1371 g_proc_name := g_proc_name_old;
1372
1373 end retention_churned;
1374
1375 procedure retention_retained is
1376 cursor party_cur is
1377 select party_id, min(nvl(account_established_date,creation_date))
1378 from hz_cust_accounts
1379 group by party_id;
1380 x_party_id hz_cust_accounts.party_id % type;
1381 x_account_established_date hz_cust_accounts.account_established_date % type;
1382 begin
1383 g_proc_name_old := g_proc_name;
1384 g_proc_name := 'retention_retained';
1385
1386 -- g_tot_recs_added := 0;
1387 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
1388 bic_summary_extract_pkg.debug(' entered + ');
1389
1390 open party_cur;
1391 loop
1392 fetch party_cur into x_party_id, x_account_established_date;
1393 if party_cur % notfound then
1394 exit;
1395 end if;
1396 insert into bic_party_summary (
1397 MEASURE_ID
1398 ,PARTY_ID
1399 ,PERIOD_START_DATE
1400 ,VALUE
1401 ,BUCKET_ID
1402 ,LAST_UPDATE_DATE
1403 ,LAST_UPDATED_BY
1404 ,CREATION_DATE
1405 ,CREATED_BY
1406 ,LAST_UPDATE_LOGIN
1407 ,REQUEST_ID
1408 ,PROGRAM_APPLICATION_ID
1409 ,PROGRAM_ID
1410 ,PROGRAM_UPDATE_DATE
1411 ,SCORE
1412 ,measure_code)
1413 select g_measure_id_for_retn
1414 ,x_party_id --hca.cust_account_id
1415 ,bdt.act_period_start_date
1416 ,3
1417 ,null
1418 ,sysdate
1419 ,g_last_updated_by
1420 ,sysdate
1421 ,g_created_by
1422 ,g_last_update_login
1423 ,g_request_id
1424 ,g_program_application_id
1425 ,g_program_id
1426 ,sysdate
1427 ,null
1428 ,'RETENTION'
1429 from bic_temp_periods bdt
1430 where trunc(bdt.start_date)
1431 --Ex: if the start date is 1-mar-2003 and the attrition period is 2 months then calculate retention retained from
1432 --1-jan-2003 inorder to calculate retention_churned
1433 between trunc(add_months(g_period_start_date,g_attrition_period*-1)) and trunc(g_period_end_date)
1434 and x_account_established_date
1435 <= add_months(bdt.act_period_end_date, g_attrition_period*-1)
1436 and exists (select 'x' from oe_order_headers_all oeh,
1437 hz_cust_accounts hca
1438 where oeh.sold_to_org_id = hca.cust_account_id
1439 and hca.party_id = x_party_id
1440 and ordered_date between add_months(bdt.act_period_end_date,
1441 g_attrition_period*-1)+1
1442 and bdt.act_period_end_date
1443 )
1444 and (exists
1445 (select 'x' from oe_order_headers_all oeh,
1446 hz_cust_accounts hca
1447 where oeh.sold_to_org_id = hca.cust_account_id
1448 and hca.party_id = x_party_id
1449 and ordered_date between add_months(bdt.act_period_end_date,
1450 g_attrition_period*-2)+1
1451 and add_months(bdt.act_period_end_date,
1452 g_attrition_period*-1)
1453 )
1454 or x_account_established_date
1455 between add_months(bdt.act_period_end_date,
1456 g_attrition_period * -2) +1
1457 and add_months(bdt.act_period_end_date,
1458 g_attrition_period * -1)
1459 )
1460 ;
1461 g_tot_recs_added := g_tot_recs_added + sql%rowcount;
1462 end loop;
1463 close party_cur;
1464 write_log('Total Records Added for Retention Retained:'||
1465 to_char(g_tot_recs_added));
1466 --write_log(' procedure '||g_proc_name || ' exited at : '||sysdate);
1467 bic_summary_extract_pkg.debug(' exited - ');
1468
1469 g_proc_name := g_proc_name_old;
1470
1471 end retention_retained;
1472
1473 -- This procedure inserts reactivated customers
1474 procedure retention_reactivated is
1475 cursor party_cur is
1476 select party_id, min(nvl(account_established_date,creation_date))
1477 from hz_cust_accounts
1478 group by party_id;
1479 x_party_id hz_cust_accounts.party_id % type;
1480 x_account_established_date hz_cust_accounts.account_established_date % type;
1481 begin
1482 g_proc_name_old := g_proc_name;
1483 g_proc_name := 'retention_reactivated';
1484 write_log('retention_reactivated entered');
1485 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
1486 bic_summary_extract_pkg.debug(' entered +');
1487
1488 -- g_tot_recs_added := 0;
1489 open party_cur;
1490 loop
1491 fetch party_cur into x_party_id, x_account_established_date;
1492 if party_cur % notfound then
1493 exit;
1494 end if;
1495 -- raise no_data_found;
1496 insert into bic_party_summary (
1497 MEASURE_ID
1498 ,PARTY_ID
1499 ,PERIOD_START_DATE
1500 ,VALUE
1501 ,BUCKET_ID
1502 ,LAST_UPDATE_DATE
1503 ,LAST_UPDATED_BY
1504 ,CREATION_DATE
1505 ,CREATED_BY
1506 ,LAST_UPDATE_LOGIN
1507 ,REQUEST_ID
1508 ,PROGRAM_APPLICATION_ID
1509 ,PROGRAM_ID
1510 ,PROGRAM_UPDATE_DATE
1511 ,SCORE
1512 ,measure_code)
1513 select g_measure_id_for_retn
1514 ,x_party_id
1515 ,bdt.act_period_start_date
1516 ,2
1517 ,null
1518 ,sysdate
1519 ,g_last_updated_by
1520 ,sysdate
1521 ,g_created_by
1522 ,g_last_update_login
1523 ,g_request_id
1524 ,g_program_application_id
1525 ,g_program_id
1526 ,sysdate
1527 ,null
1528 ,'RETENTION'
1529 from bic_temp_periods bdt
1530 where trunc(bdt.start_date)
1531 --Ex: if the start date is 1-mar-2003 and the attrition period is 2 months then
1532 --calculate retention reactivated from
1533 --1-jan-2003 inorder to calculate retention_churned
1534 between trunc(add_months(g_period_start_date,g_attrition_period*-1))and trunc(g_period_end_date)
1535 and x_account_established_date
1536 <=add_months(bdt.act_period_end_date, g_attrition_period*-2)
1537 -- above line means acquired before previous attrition period
1538 -- <= sign is used because you want to account_established date between
1539 -- 1-apr-99 and 30-jun-99 and not between 31-mar-99 and 30-jun-99.
1540 -- 30-jun-99 minus 3 months will return 31-mar-99
1541 --
1542 -- for same reasons 1 is added while comparing ordered_date
1543 and exists (select 'x' from oe_order_headers_all oeh,
1544 hz_cust_accounts hca
1545 where oeh.sold_to_org_id = hca.cust_account_id
1546 and hca.party_id = x_party_id
1547 and ordered_date between add_months(bdt.act_period_end_date,
1548 g_attrition_period*-1)+1
1549 and bdt.act_period_end_date
1550 )
1551 and not exists
1552 (select 'x' from oe_order_headers_all oeh,
1553 hz_cust_accounts hca
1554 where oeh.sold_to_org_id = hca.cust_account_id
1555 and hca.party_id = x_party_id
1556 and ordered_date between add_months(bdt.act_period_end_date,
1557 g_attrition_period*-2)+1
1558 and add_months(bdt.act_period_end_date,
1559 g_attrition_period*-1)
1560 )
1561 ;
1562 g_tot_recs_added := g_tot_recs_added + sql%rowcount;
1563 end loop;
1564 close party_cur;
1565 -- write_log('Total Records Added for Retention Reactivated:'||to_char(g_tot_recs_added));
1566 --write_log(' procedure '||g_proc_name || ' exited at : '||sysdate);
1567 bic_summary_extract_pkg.debug( ' exited - ');
1568
1569 g_proc_name := g_proc_name_old;
1570
1571 end retention_reactivated;
1572
1573 -- This procedure inserts New customers for Retention measure code
1574 procedure retention_new is
1575 cursor party_cur is
1576 select party_id, min(nvl(account_established_date,creation_date))
1577 from hz_cust_accounts
1578 group by party_id
1579 having min(nvl(account_established_date,creation_date)) >=
1580 add_months(g_period_start_date,g_attrition_period*-1+1)
1581 -- 1 month is added so that you can compare with
1582 -- first period end date. Ex: g_period_start_date=1-aug-98, attrition
1583 -- period = 3 month and above expression will return you 1-jun-98.
1584 -- You want to know who was NEW on 31-aug-98
1585 ;
1586 x_party_id hz_cust_accounts.party_id % type;
1587 x_account_established_date hz_cust_accounts.account_established_date % type;
1588 begin
1589 g_proc_name_old := g_proc_name;
1590 g_proc_name := 'Retention_new';
1591 debug(' entered +');
1592 g_tot_recs_added := 0;
1593 write_log('retention_new entered');
1594 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
1595 bic_summary_extract_pkg.debug(g_proc_name || ' entered at : '|| sysdate);
1596
1597 open party_cur;
1598 loop
1599 fetch party_cur into x_party_id, x_account_established_date;
1600 if party_cur % notfound then
1601 exit;
1602 end if;
1603
1604 insert into bic_party_summary (
1605 MEASURE_ID
1606 ,PARTY_ID
1607 ,PERIOD_START_DATE
1608 ,VALUE
1609 ,BUCKET_ID
1610 ,LAST_UPDATE_DATE
1611 ,LAST_UPDATED_BY
1612 ,CREATION_DATE
1613 ,CREATED_BY
1614 ,LAST_UPDATE_LOGIN
1615 ,REQUEST_ID
1616 ,PROGRAM_APPLICATION_ID
1617 ,PROGRAM_ID
1618 ,PROGRAM_UPDATE_DATE
1619 ,SCORE
1620 ,measure_code)
1621 select
1622 g_measure_id_for_retn
1623 ,x_party_id
1624 ,bdt.act_period_start_date
1625 ,1
1626 ,null
1627 ,sysdate
1628 ,g_last_updated_by
1629 ,sysdate
1630 ,g_created_by
1631 ,g_last_update_login
1632 ,g_request_id
1633 ,g_program_application_id
1634 ,g_program_id
1635 ,sysdate
1636 ,null
1637 ,'RETENTION'
1638 from bic_temp_periods bdt
1639 where trunc(bdt.start_date)
1640 --Ex: if the start date is 1-mar-2003 and the attrition period is 2 months
1641 --then calculate retention new from
1642 --1-jan-2003 inorder to calculate retention_churned
1643 between trunc(add_months(g_period_start_date,g_attrition_period*-1))and trunc(g_period_end_date)
1644 and x_account_established_date
1645 between add_months(bdt.act_period_end_date,g_attrition_period*-1)+1
1646 and bdt.act_period_end_date
1647 ;
1648 g_tot_recs_added := g_tot_recs_added + sql%rowcount;
1649 end loop;
1650 close party_cur;
1651 write_log('retention new extracted');
1652 write_log('Total Records Added for Retention New:'|| to_char(g_tot_recs_added));
1653 --write_log(' procedure '||g_proc_name || ' exited at : '||sysdate);
1654 bic_summary_extract_pkg.debug(' exited - ');
1655
1656 g_proc_name := g_proc_name_old;
1657
1658 end retention_new;
1659
1660 procedure extract_retention is
1661 rec_count number;
1662 measure_id_not_found Exception;
1663 attrition_period_not_found Exception;
1664 begin
1665 g_proc_name_old := g_proc_name;
1666 g_proc_name := 'Extract_retention_data';
1667 g_proc_name := 'Extract_Retention';
1668 write_log('Before Retention Extraction...');
1669 --write_log(' procedure '||g_proc_name || ' entered at : '||sysdate);
1670 bic_summary_extract_pkg.debug(' entered + : ');
1671
1672 bic_consolidate_cust_data_pkg.purge_party_summary_data;
1673 g_measure_id_for_retn := get_measure_id('RETENTION');
1674 if g_measure_id_for_retn = 0 then
1675 raise measure_id_not_found;
1676 write_log('Error: Measure_id not found for RETENTION');
1677 end if;
1678 if get_attrition_period = false then
1679 raise attrition_period_not_found;
1680 end if;
1681
1682 if g_delete_flag = 'N' then
1683
1684 extract_periods( add_months(g_period_start_date,g_attrition_period*-1),
1685 g_period_end_date ,
1686 'RETENTION' ,
1687 'N',
1688 g_delete_flag,
1689 null);
1690 else
1691 extract_all_periods(add_months(g_period_start_date,g_attrition_period*-1),
1692 g_period_end_date);
1693 end if;
1694
1695 debug('Extracted Periods Successfully In Extract_Retention...');
1696
1697 select count(*) into rec_count
1698 from bic_temp_periods;
1699
1700 if rec_count = 0 then
1701 write_log('Data already extracted for these periods');
1702 return;
1703 end if;
1704 debug('calling retention new In Extract_Retention...');
1705
1706 retention_new ;
1707 debug('calling retention reactivated In Extract_Retention...');
1708 retention_reactivated;
1709 debug('calling retention retained In Extract_Retention...');
1710 retention_retained ;
1711 debug('calling retention churned In Extract_Retention...');
1712 retention_churned ;
1713
1714
1715 bic_consolidate_cust_data_pkg.populate_status_data (g_period_start_date,g_period_end_date);
1716
1717 bic_consolidate_cust_data_pkg.purge_party_summary_data;
1718
1719 Commit;
1720
1721 write_log('Total Records Added for retention:'||to_char(g_tot_recs_added) );
1722 --write_log(' procedure '||g_proc_name || ' exited at : '||sysdate);
1723 bic_summary_extract_pkg.debug(' exited - ');
1724
1725 g_proc_name := 'Extract_Main';
1726
1727 Exception
1728 when measure_id_not_found then
1729 generate_error(g_measure_code,'Measure_id not found for RETENTION in the table BIC_MEASURES_ALL');
1730 Rollback;
1731 when attrition_period_not_found then
1732 generate_error(g_measure_code,'No records in BIC_PROFILE_VALUES_ALL');
1733 Rollback;
1734 when others then
1735 write_log(' Retention data is not extracted due to exception : '||sqlerrm);
1736 generate_error(g_measure_code,'Retention data is not extracted : '||sqlerrm);
1737 Rollback;
1738 end;
1739
1740 procedure extract_sales(p_period_start_date date,p_period_end_date date,p_org_id number,p_lf_flag varchar2 default null)
1741 is
1742 rec_count number;
1743 lf_flag varchar2(2);
1744 begin
1745 if lf_flag is null then
1746 lf_flag := 'N';
1747 else
1748 lf_flag := p_lf_flag;
1749 end if;
1750 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
1751 g_proc_name_old := g_proc_name;
1752 g_proc_name := 'Extract_sales_data';
1753
1754 bic_summary_extract_pkg.debug(g_proc_name||' entered + ');
1755 write_log('Before Sales/Revenue Extraction...');
1756
1757 write_log('Before Extracting Periods In Extract_Sales...');
1758 write_log('g_delete_flag : ' || g_delete_flag);
1759 write_log('g_org_id : '||g_org_id);
1760 if(lf_flag = 'N') then
1761 extract_periods( g_period_start_date,
1762 g_period_end_date ,
1763 'SALES' ,
1764 'Y',
1765 g_delete_flag,
1766 g_org_id);
1767 end if;
1768 write_log('Extracted Periods Successfully In Extract_Sales...');
1769 select count(*) into rec_count
1770 from bic_temp_periods;
1771
1772 if rec_count = 0 then
1773 write_log('sales data already extracted');
1774 return;
1775 end if;
1776
1777 insert into bic_customer_summary_all (
1778 measure_id,
1779 customer_id,
1780 period_start_date,
1781 org_id,
1782 value,
1783 last_update_date,
1784 creation_date,
1785 program_update_date,
1786 last_updated_by,
1787 created_by,
1788 request_id,
1789 program_application_id,
1790 program_id,
1791 last_update_login,
1792 measure_code)
1793 select bma.measure_id,
1794 hca.party_id,
1795 bdt.act_period_start_date,
1796 bma.org_id,
1797 sum( bic_summary_extract_pkg.convert_amt( gsb.currency_code, gl.gl_date, gl.acctd_amount) ) ,
1798 sysdate,
1799 sysdate,
1800 sysdate,
1801 g_last_updated_by,
1802 g_created_by,
1803 g_request_id,
1804 g_program_application_id,
1805 g_program_id,
1806 g_last_update_login,
1807 'SALES'
1808 from hz_cust_accounts hca,
1809 bic_temp_periods bdt,
1810 bic_measures_all bma,
1811 ra_customer_trx_all trx,
1812 ra_customer_trx_lines_all lines,
1813 ra_cust_trx_line_gl_dist_all gl,
1814 gl_sets_of_books gsb
1815 where nvl(bma.org_id,-99) = nvl(trx.org_id,-99)
1816 and trx.bill_to_customer_id = hca.cust_account_id
1817 and bma.measure_code = 'SALES'
1818 and gl.gl_date between bdt.act_period_start_date and bdt.act_period_end_date
1819 and trunc(bdt.start_date)
1820 between trunc(p_period_start_date) and trunc(p_period_end_date)
1821 and bma.org_id = decode(g_delete_flag,'N',bdt.org_id,nvl(p_org_id,bma.org_id))
1822 and trx.customer_trx_id = lines.customer_trx_id
1823 and lines.customer_trx_line_id = gl.customer_trx_line_id
1824 and account_Set_flag = 'N'
1825 and complete_flag = 'Y'
1826 and account_class = 'REV'
1827 and lines.line_type = 'LINE'
1828 and trx.previous_customer_trx_id IS NULL -- modified for 2992478
1829 and trx.set_of_books_id = gsb.set_of_books_id (+)
1830 group by bma.measure_id,
1831 hca.party_id,
1832 bdt.act_period_start_date,
1833 bma.org_id;
1834 write_log('transferring data to summary tables');
1835 rec_count := 0;
1836 rec_count := sql%rowcount;
1837 if lf_flag = 'N' then
1838 bic_consolidate_cust_data_pkg.populate_party_data (g_period_start_date,g_period_end_date);
1839
1840 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
1841
1842
1843 Commit;
1844 end if;
1845 bic_summary_extract_pkg.debug(g_proc_name||' exited - ');
1846 g_proc_name := g_proc_name_old;
1847 write_log('Total Records Added for Sales:'|| rec_count);
1848 g_proc_name := 'Extract_Main';
1849
1850 Exception
1851 when others then
1852 write_log('Sales data is not extracted : '||sqlerrm);
1853 generate_error(g_measure_code,'Sales data is not extracted : '||sqlerrm);
1854 Rollback;
1855 end;----------------------------------------------------------------------
1856
1857 procedure extract_cogs is
1858 rec_count number;
1859 begin
1860 g_proc_name_old := g_proc_name;
1861 g_proc_name := 'Extract_cogs_data';
1862 --g_proc_name := 'Extract_Main';
1863 write_log('Before Cogs Extraction...');
1864 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
1865 bic_summary_extract_pkg.debug(g_proc_name||' entered + ');
1866 if( fnd_log.test(1,g_proc_name) = false ) then
1867 write_log('logging not enabled');
1868 end if;
1869
1870 extract_periods( g_period_start_date,
1871 g_period_end_date,
1872 'COGS' ,
1873 'Y',
1874 g_delete_flag,
1875 g_org_id);
1876
1877
1878 select count(*) into rec_count
1879 from bic_temp_periods;
1880
1881 if rec_count = 0 then
1882 write_log('COGS data already extracted');
1883 return;
1884 end if;
1885
1886 insert into bic_customer_summary_all (
1887 measure_id,
1888 customer_id,
1889 period_start_date,
1890 org_id,
1891 value,
1892 last_update_date,
1893 creation_date,
1894 program_update_date,
1895 last_updated_by,
1896 created_by,
1897 request_id,
1898 program_application_id,
1899 program_id,
1900 last_update_login,
1901 measure_code)
1902 select bma.measure_id,
1903 hca.party_id,
1904 bdt.act_period_start_date,
1905 bma.org_id,
1906 sum(bic_summary_extract_pkg.convert_amt(gsb.currency_code,
1907 cmt.gl_date,
1908 cogs_amount)
1909 ),
1910 sysdate,
1911 sysdate,
1912 sysdate,
1913 g_last_updated_by,
1914 g_created_by,
1915 g_request_id,
1916 g_program_application_id,
1917 g_program_id,
1918 g_last_update_login,
1919 'COGS'
1920 from hz_cust_accounts hca,
1921 bic_temp_periods bdt,
1922 bic_measures_all bma,
1923 cst_bis_margin_summary cmt,
1924 hr_organization_information hoi,
1925 gl_sets_of_books gsb
1926
1927 where nvl(bma.org_id,-99) = nvl(cmt.org_id,-99)
1928 and bma.measure_code = 'COGS'
1929 and cmt.gl_date between bdt.act_period_start_date and
1930 bdt.act_period_end_date
1931 and trunc(bdt.start_date)
1932 between trunc(g_period_start_date)and trunc(g_period_end_date)
1933 and bma.org_id = decode(g_delete_flag,'N',bdt.org_id,nvl(g_org_id,bma.org_id))
1934 and cmt.legal_entity_id = hoi.organization_id (+)
1935 and hoi.org_information_context (+)= 'Legal Entity Accounting'
1936 and hoi.org_information1 = gsb.set_of_books_id (+)
1937 and cmt.customer_id = hca.cust_account_id
1938 and cmt.source = 'COGS' -- added by vsegu
1939 group by bma.measure_id,
1940 hca.party_id,
1941 bdt.act_period_start_date,
1942 bma.org_id;
1943
1944 bic_consolidate_cust_data_pkg.populate_party_data (g_period_start_date,g_period_end_date);
1945 bic_consolidate_cust_data_pkg.purge_customer_summary_data;
1946 rec_count := sql%rowcount;
1947
1948 Commit;
1949
1950 write_log('Total Records Added for COGS:'|| rec_count);
1951 bic_summary_extract_pkg.debug(g_proc_name||' exited -');
1952 g_proc_name := 'Extract_Main';
1953
1954 Exception
1955 when others then
1956 write_log('COGS data is not extracted : '||sqlerrm);
1957 generate_error(g_measure_code,'COGS data is not extracted : '||sqlerrm);
1958 Rollback;
1959 end;
1960
1961 -- VSEGU CODE CHANGES END HERE
1962
1963
1964 procedure extract_acquisition_data
1965 is
1966 cursor party_cur is
1967 select party_id, min(nvl(account_established_date,creation_date))
1968 from hz_cust_accounts
1969 group by party_id;
1970 x_party_id hz_cust_accounts.party_id % type;
1971 x_account_established_date hz_cust_accounts.account_established_date % type;
1972 rec_count number;
1973 activation_id_not_found Exception;
1974 acquisition_not_found Exception;
1975 activation_period_not_found Exception;
1976 begin
1977 g_proc_name_old := g_proc_name;
1978 g_proc_name := 'Extract_acquisition_data';
1979 g_tot_recs_added := 0;
1980 rec_count := 0;
1981
1982 debug('entered +');
1983 bic_consolidate_cust_data_pkg.purge_party_summary_data;
1984
1985 if (get_activation_period = false) then
1986 write_log('Error: activation_period for ACQUISITION not defined');
1987 RAISE activation_period_not_found;
1988 -- return;
1989 end if;
1990
1991 g_measure_id_for_acti := get_measure_id('ACTIVATION');
1992 g_measure_id_for_acqu := get_measure_id('ACQUISITION');
1993 if g_measure_id_for_acti = 0 then
1994 write_log('Error: Measure_id not found for ACTIVATION');
1995 RAISE activation_id_not_found;
1996 --return;
1997 end if;
1998
1999 if g_measure_id_for_acqu = 0 then
2000 write_log('Error: Measure_id not found for ACQUISITION');
2001 RAISE acquisition_not_found;
2002 -- return;
2003 end if;
2004
2005 -- write_log ( 'values of passed params for acquistion ' || g_period_start_date ||
2006 -- g_delete_flag || g_org_id );
2007 bic_summary_extract_pkg.extract_periods (
2008 g_period_start_date - g_activation_period,
2009 g_period_end_date,
2010 'ACQUISITION',
2011 'N',
2012 g_delete_flag,
2013 g_org_id);
2014
2015 select count(*) into rec_count
2016 from bic_temp_periods;
2017
2018 if rec_count = 0 then
2019 write_log('acquitistion data already extracted');
2020 return;
2021 end if;
2022
2023 open party_cur;
2024 loop
2025 fetch party_cur into x_party_id, x_account_established_date;
2026 if party_cur % notfound then
2027 exit;
2028 end if;
2029 -- BEGIN
2030 -- SAVEPOINT start_transaction;
2031 insert into bic_party_summary ( --bic_customer_summary_all (
2032 measure_id,
2033 party_id, --customer_id,
2034 period_start_date,
2035 value,
2036 last_update_date,
2037 creation_date,
2038 program_update_date,
2039 last_updated_by,
2040 created_by,
2041 request_id,
2042 program_application_id,
2043 program_id,
2044 last_update_login,
2045 measure_code)
2046 select distinct
2047 g_measure_id_for_acqu, --bma.measure_id,
2048 x_party_id,
2049 bdt.start_date,
2050 /*to_number(to_char(nvl(hca.account_established_date,hca.creation_date)
2051 ,'J')
2052 ),*/ to_number(to_char(x_account_established_date,'J')),
2053 sysdate,
2054 sysdate,
2055 sysdate,
2056 g_last_updated_by,
2057 g_created_by,
2058 g_request_id,
2059 g_program_application_id,
2060 g_program_id,
2061 g_last_update_login,
2062 'ACQUISITION'
2063 from bic_temp_periods bdt
2064 where x_account_established_date
2065 between bdt.act_period_start_date and
2066 bdt.act_period_end_date ;
2067 /* and trunc(bdt.start_date)
2068 between (trunc(g_period_start_date) - g_activation_period +1) and
2069 trunc(g_period_end_date); */
2070 -- EXCEPTION WHEN OTHERS THEN
2071 -- ROLLBACK TO start_transaction;
2072 -- END;
2073 -- COMMIT;
2074 g_tot_recs_added := g_tot_recs_added + sql%rowcount;
2075 end loop;
2076 close party_cur;
2077
2078 write_log('Total Records Added for Acquisition:'||
2079 to_char(g_tot_recs_added));
2080 -- Now insert activated customers. Here sequence is important. Activation
2081 -- data can be inserted only after acquisition data has been extracted because
2082 -- activation data is dependent on acquistion data.
2083
2084 write_log('Before Activation Extraction...');
2085 -- A acquired customer can be considered activated
2086 -- only in the month in which the first order is made and the first order should
2087 -- be within the activation period and also within the start and end dates of extraction
2088 insert into bic_party_summary (measure_id ,
2089 party_id ,
2090 period_start_date ,
2091 value ,
2092 last_update_date ,
2093 creation_date ,
2094 program_update_date ,
2095 last_updated_by ,
2096 created_by ,
2097 request_id ,
2098 program_application_id,
2099 program_id ,
2100 last_update_login ,
2101 measure_code)
2102 select
2103 g_measure_id_for_acti,
2104 bcs.party_id,
2105 trunc(min(aoh.ordered_date), 'MONTH'),
2106 1,
2107 sysdate,
2108 sysdate,
2109 sysdate,
2110 g_last_updated_by,
2111 g_created_by,
2112 g_request_id,
2113 g_program_application_id,
2114 g_program_id,
2115 g_last_update_login,
2116 'ACTIVATION'
2117 from oe_order_headers_all aoh, --4434468 replaced aso_i_oe_order_headers_v with oe_order_headers_all
2118 bic_party_summary bcs,
2119 hz_cust_accounts acct
2120 where bcs.measure_id = g_measure_id_for_acqu
2121
2122 --and to_date(bcs.value + g_activation_period ,'J') >= g_period_start_date
2123
2124 --and to_date(bcs.value ,'J') <= g_period_end_date
2125 and bcs.party_id = acct.party_id
2126 and acct.cust_account_id = aoh.sold_to_org_id
2127 and aoh.ordered_date between to_date(bcs.value,'J') and
2128 to_date(bcs.value+g_activation_period,'J')
2129 group by
2130 g_measure_id_for_acti,
2131 bcs.party_id,
2132 sysdate,
2133 sysdate,
2134 sysdate,
2135 g_last_updated_by,
2136 g_created_by,
2137 g_request_id,
2138 g_program_application_id,
2139 g_program_id,
2140 g_last_update_login
2141 having min(aoh.ordered_date) between g_period_start_date
2142 and g_period_end_date;
2143 write_log('Total Records Added for Activation:'||
2144 to_char(sql%rowcount));
2145 g_proc_name := g_proc_name_old;
2146
2147 bic_consolidate_cust_data_pkg.populate_status_data(g_period_start_date,g_period_end_date);
2148 bic_consolidate_cust_data_pkg.purge_party_summary_data;
2149
2150 commit;
2151 debug('exited -');
2152
2153 EXCEPTION
2154 when activation_period_not_found then
2155 generate_error('ACTIVATION','No records in BIC_PROFILE_VALUES_ALL');
2156 rollback;
2157 when acquisition_not_found then
2158 generate_error('ACQUISITION','Measure_id not found for ACQUISITION in the table BIC_MEASURES_ALL');
2159 rollback;
2160 when activation_id_not_found then
2161 generate_error('ACTIVATION','Measure_id not found for ACTIVATION in the table BIC_MEASURES_ALL');
2162 rollback;
2163 when others then
2164 write_log(' Activation data is not extracted due to exception : '||sqlerrm);
2165 generate_error(g_measure_code,'Activation data is not extracted : '||sqlerrm);
2166 rollback;
2167 end extract_acquisition_data;
2168
2169 -- This procedure is called by concurrent program to extract customer
2170 -- summary data. This procedure take date range for extraction. If start
2171 -- date is null then profile value of 'BIC_SMRY_EXTRACTION_DATE is taken as
2172 -- start date. If end date is null then it is set to start date of the period
2173 -- which ends before system date.
2174 -- If delete flag is 'Y' then data is deleted before extraction. Default
2175 -- value of delete flag is 'N'.
2176 -- If measure code is null then data for all measures i.e. 'SATISFACTION',
2177 -- 'LOYALTY', 'RETENTION', 'ACQUISTION', 'LIFECYCLE' is extracted.
2178 -- if org id is null then dat for all orgs is extracted.
2179 procedure extract_main (
2180 errbuf out NOCOPY varchar2,
2181 retcode out NOCOPY number ,
2182 p_start_date varchar2 default null,
2183 p_end_date varchar2 default null,
2184 p_delete_flag varchar2 default null,
2185 p_measure_code varchar2 default null,
2186 p_org_id number default null) as
2187 x_start_date date;
2188 x_end_date date;
2189 x_date date;
2190 x_sql_string varchar2(500);
2191 x_cnt number;
2192 begin
2193 set_debug;
2194 g_proc_name := 'Extract_main';
2195 g_proc_name_old := g_proc_name;
2196 g_srl_no := 1;
2197
2198 -- start global variables initialization
2199 g_measure_code := p_measure_code;
2200 g_org_id := p_org_id;
2201 if p_delete_flag is null then
2202 g_delete_flag := 'N';
2203 else
2204 g_delete_flag := p_delete_flag;
2205 end if;
2206
2207 write_log('Parameters Passed to Extraction Program');
2208 write_log('---------------------------------------');
2209 write_log(' Start Date:'||p_start_date);
2210 write_log(' End Date:'||p_end_date );
2211 write_log(' Delete Flag:'||p_delete_flag);
2212 write_log(' Measure Code:'||nvl(g_measure_code,'Null'));
2213 write_log(' Org Id:'||nvl(to_char(p_org_id),'Null'));
2214 write_log('---------------------------------------');
2215 -- write_log('calling set_period_exist');
2216 if (set_periods_exist ( p_start_date,
2217 p_end_date
2218 ) = false ) then
2219 write_log('All Periods do not exist in the table bic_dimv_time , Plz. run the "Extract Calendar"');
2220 generate_error('Main','Periods should be extracted before running this program');
2221 return;
2222 end if;
2223 write_log('after set_periods_exist ' || g_period_start_date || ' ' || g_period_end_date );
2224 ----------------------------------------------------------------------------
2225 -- initialize global variables for who columns
2226 g_last_updated_by := fnd_global.user_id ;
2227 g_created_by := fnd_global.user_id ;
2228 g_last_update_login := fnd_global.login_id ;
2229 g_request_id := fnd_global.conc_request_id;
2230 g_program_application_id := fnd_global.prog_appl_id ;
2231 g_program_id := fnd_global.conc_program_id;
2232 -- global variables initialization complete
2233 g_proc_name := 'Extract_Main';
2234 -- extracting periods for all measures
2235 if nvl(g_delete_flag,'N') = 'Y' then
2236 extract_all_periods(g_period_start_date , g_period_end_date );
2237 end if;
2238 -- extract retention
2239 if g_measure_code is null or g_measure_code = 'RETENTION' then
2240 if measure_disabled('RETENTION') <> 'Y' then
2241 g_proc_name := 'Extract_Main';
2242 write_log('Before Retention Extraction...');
2243 extract_retention;
2244 end if;
2245 end if;
2246 -- extract sales. This is same as Revenue
2247 if g_measure_code is null or g_measure_code = 'SALES' then
2248 if measure_disabled('SALES') <> 'Y' then
2249 g_proc_name := 'Extract_Main';
2250 write_log('Before Sales/Revenue Extraction...');
2251 extract_sales(g_period_start_date,g_period_end_date,g_org_id);
2252 end if;
2253 end if;
2254 -- extract Cost Of Goods data
2255 if g_measure_code is null or g_measure_code = 'COGS' then
2256 if measure_disabled('COGS') <> 'Y' then
2257 g_proc_name := 'Extract_Main';
2258 write_log('Before Cost Of Goods Extraction...');
2259 extract_cogs;
2260 end if;
2261 end if;
2262 -- extract Satisfaction
2263 if g_measure_code is null or g_measure_code = 'SATISFACTION' then
2264 g_proc_name := 'Extract_Main';
2265 write_log('Before SATISFACTION Extraction...');
2266 extract_satisfaction;
2267 end if;
2268 -- extract Loyalty
2269 if g_measure_code is null or g_measure_code in ('LOYALTY') then
2270 g_proc_name := 'Extract_Main';
2271 write_log('Before Loyalty Extraction...');
2272 extract_loyalty;
2273 end if;
2274 -- extract Acquisition
2275 if g_measure_code is null or g_measure_code = 'ACQUISITION' then
2276 if measure_disabled('ACQUISITION') <> 'Y' then
2277 g_proc_name := 'Extract_Main';
2278 write_log('Before Acquisition Extraction....');
2279 extract_acquisition_data;
2280 end if;
2281 end if;
2282 -- extract LifeCycle
2283 if g_measure_code is null or g_measure_code = 'LIFE_CYCLE' then
2284 if measure_disabled('LIFE_CYCLE') <> 'Y' then
2285 g_proc_name := 'Extract_Main';
2286 write_log('Before Life Cycle Extraction....');
2287 bic_lifecycle_extract_pkg.extract_lifecycle_data( g_period_start_date,
2288 g_period_end_date,
2289 g_delete_flag,
2290 g_org_id
2291 );
2292 end if;
2293 end if;
2294 -- calling the update market segment procedure
2295 bic_consolidate_cust_data_pkg.update_market_segment;
2296 -- set profile value of BIC_SMRY_EXTRACTION_DATE iff start and end dates
2297 -- passed to the procedure are null.
2298 if p_start_date is null and p_end_date is null then
2299 fnd_profile.put('BIC_SMRY_EXTRACTION_DATE',
2300 to_char(g_period_end_date,'dd-mm-yyyy'));
2301 write_log('BIC_SMRY_EXTRACTION_DATE profile option is set to ' ||
2302 to_char(g_period_end_date,'dd-mm-yyyy'));
2303 end if;
2304 g_proc_name := g_proc_name_old;
2305
2306 exception when others then
2307 generate_error(null,'Exception occured inside extract_main :'||sqlerrm);
2308 end extract_main;
2309 --
2310 -- This procedure inserts some of the order measures
2311 procedure insert_order_measures is
2312 cursor c_orders is
2313 SELECT hca.party_id customer_id,
2314 bdt.start_date period_start_date,
2315 ooh.org_id,
2316 count(distinct decode(ool.line_category_code,'ORDER',ooh.header_id,
2317 null
2318 )) orders,
2319 count(distinct decode(ool.line_category_code,'RETURN',ooh.header_id,
2320 null
2321 )) returns,
2322 sum((decode(ool.line_category_code,'ORDER',
2323 ool.ordered_quantity - nvl(ool.cancelled_quantity,0)) *
2324 bic_summary_extract_pkg.convert_amt(
2325 ooh.transactional_curr_code,
2326 ooh.ordered_date,
2327 ool.unit_selling_price))
2328 ) order_amt,
2329 sum((decode(ool.line_category_code,'RETURN',
2330 ool.ordered_quantity - nvl(ool.cancelled_quantity,0)) *
2331 bic_summary_extract_pkg.convert_amt(
2332 ooh.transactional_curr_code,
2333 ooh.ordered_date,
2334 ool.unit_selling_price))
2335 ) return_amt,
2336 sum(decode(ool.line_category_code,'ORDER',
2337 ool.ordered_quantity - nvl(ool.cancelled_quantity,0),
2338 null)) order_qty,
2339 sum(decode(ool.line_category_code,'RETURN',
2340 ool.ordered_quantity - nvl(ool.cancelled_quantity,0),
2341 null)) return_qty
2342 FROM aso_i_oe_order_lines_v ool,
2343 aso_i_oe_order_headers_v ooh,
2344 bic_dimv_time bdt,
2345 hz_cust_accounts hca
2346 WHERE ooh.header_id = ool.header_id
2347 AND ooh.sold_to_org_id is not null
2348 AND ooh.ordered_date between bdt.start_date and act_period_end_date
2349 AND trunc(bdt.start_date)
2350 BETWEEN trunc(g_period_start_date)AND trunc(g_period_end_date)
2351 and hca.cust_account_id = ooh.sold_to_org_id
2352 group by hca.party_id , bdt.start_date, ooh.org_id
2353 ;
2354 x_orders number;
2355 x_order_qty oe_order_lines_all.ordered_quantity % type;
2356 x_order_amt oe_order_lines_all.unit_selling_price % type;
2357 x_returns number;
2358 x_return_qty oe_order_lines_all.ordered_quantity % type;
2359 x_return_amt oe_order_lines_all.unit_selling_price % type;
2360 x_party_id hz_parties.party_id % type;
2361 x_org_id oe_order_headers_all.org_id % type;
2362 x_start_date date;
2363 begin
2364 --dbms_output.put_line(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
2365 open c_orders;
2366 loop
2367 fetch c_orders into x_party_id, x_start_date, x_org_id,
2368 x_orders, x_returns, x_order_amt, x_return_amt,
2369 x_order_qty, x_return_qty;
2370 if c_orders % notfound then
2371 exit;
2372 end if;
2373 insert_record('ORDER_NUM', x_start_date, x_party_id,
2374 x_orders, x_org_id);
2375 insert_record('ORDER_QTY', x_start_date, x_party_id,
2376 x_order_qty, x_org_id);
2377 insert_record('ORDER_AMT', x_start_date, x_party_id,
2378 x_order_amt, x_org_id);
2379 insert_record('RETURNS', x_start_date, x_party_id,
2380 x_returns, x_org_id);
2381 insert_record('RETURN_QTY', x_start_date, x_party_id,
2382 x_return_qty, x_org_id);
2383 insert_record('RETURN_BY_VALUE', x_start_date, x_party_id,
2384 x_return_amt, x_org_id);
2385 /***********************************************************************
2386 ***********************************************************************/
2387 end loop;
2388 close c_orders;
2389 --dbms_output.put_line(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
2390 end insert_order_measures;
2391
2392 procedure insert_order_delivery_measures is
2393 cursor c_orders is
2394 select
2395 hca.party_id customer_id,
2396 bdt.start_date period_start_date,
2397 ooh.org_id,
2398 count(decode(ool.line_category_code,'ORDER', ool.line_id,null)) line_dl,
2399 count(decode(sign(ool.request_date-ool.actual_shipment_date),
2400 1,null, 1)) line_ot,
2401 sum(ool.shipped_quantity *
2402 bic_summary_extract_pkg.convert_amt(ooh.transactional_curr_code,
2403 ooh.ordered_date,
2404 ool.unit_selling_price)
2405 ) del_val,
2406 sum(decode(sign(ool.request_date-ool.actual_shipment_date),
2407 1,0,
2408 ool.shipped_quantity *
2409 bic_summary_extract_pkg.convert_amt(ooh.transactional_curr_code,
2410 ooh.ordered_date,
2411 ool.unit_selling_price)
2412 )) ontime_val
2413 from
2414 aso_i_oe_order_headers_v ooh,
2415 aso_i_oe_order_lines_v ool,
2416 hz_cust_accounts hca,
2417 bic_dimv_time bdt
2418 where
2419 trunc(bdt.start_date)
2420 BETWEEN trunc(g_period_start_date)AND trunc(g_period_end_date)
2421 and ooh.sold_to_org_id is not null
2422 and ool.header_id = ooh.header_id
2423 and ool.actual_shipment_date between bdt.start_date
2424 and bdt.act_period_end_date
2425 and hca.cust_account_id = ooh.sold_to_org_id
2426 group by
2427 hca.party_id,
2428 bdt.start_date,
2429 ooh.org_id;
2430
2431 x_line_ontime number;
2432 x_line_del number;
2433 x_ontime_val oe_order_lines_all.unit_selling_price % type;
2434 x_del_val oe_order_lines_all.unit_selling_price % type;
2435
2436 x_party_id hz_parties.party_id % type;
2437 x_org_id oe_order_headers_all.org_id % type;
2438 x_start_date date;
2439 begin
2440 --dbms_output.put_line(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
2441 open c_orders;
2442 loop
2443 fetch c_orders into x_party_id, x_start_date, x_org_id,
2444 x_line_del, x_line_ontime, x_del_val, x_ontime_val
2445 ;
2446 if c_orders % notfound then
2447 exit;
2448 end if;
2449 insert_record('ORDER_LINES_DELIVERED', x_start_date, x_party_id,
2450 x_line_del, x_org_id);
2451 insert_record('ORDER_LINES_ONTIME', x_start_date, x_party_id,
2452 x_line_ontime, x_org_id);
2453 insert_record('OL_DEL_VALUE', x_start_date, x_party_id,
2454 x_del_val, x_org_id);
2455 insert_record('OL_ONTIME_VALUE', x_start_date, x_party_id,
2456 x_ontime_val, x_org_id);
2457 /***********************************************************************
2458 ***********************************************************************/
2459 end loop;
2460 close c_orders;
2461 --dbms_output.put_line(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
2462 end insert_order_delivery_measures;
2463 ----------------------------------------------------------------------------
2464 procedure write_log (p_msg varchar2) is
2465 begin
2466 if g_log_output is null then
2467 fnd_file.put_line(fnd_file.log,substr(to_char(g_srl_no,'99') || '-'||
2468 p_msg || ': ' || g_proc_name,1,250) ||
2469 to_char(sysdate,'dd-mm-yy hh24:mi:ss')
2470 );
2471 end if;
2472 write_debug_msg(p_msg);
2473 end write_log;
2474
2475 procedure bulk_insert_sql_measures (p_stmnt varchar2) is
2476 x_err varchar2(250);
2477 begin
2478 if g_org_id is null then
2479 execute immediate p_stmnt using g_period_start_date,
2480 g_period_end_date;
2481 else
2482 execute immediate p_stmnt using g_org_id,
2483 g_period_start_date,
2484 g_period_end_date;
2485 end if;
2486 commit;
2487
2488 exception
2489 when others then
2490 x_err := substr(sqlerrm,1,240);
2491 --dbms_output.put_line(to_char(g_srl_no) || x_err);
2492 insert into bic_debug(report_id,message) values ('BICSUMMB',x_err);
2493 end;
2494 procedure process_sql_type_measures is
2495 x_str varchar2(5000);
2496 x_stmnt1 varchar2(2000);
2497 x_stmnt2 varchar2(2000);
2498 cursor c_sql_measures is
2499 select sql_statement, measure_code
2500 from bic_measure_attribs
2501 where sql_statement is not null
2502 and nvl(disable_flag,'N') = 'N'
2503 ;
2504
2505 x_end_date date;
2506 x_start_date date;
2507
2508 x_whr_pos number;
2509 x_from_pos number;
2510 x_extra_cond varchar2(60);
2511 x_err varchar2(251);
2512 x_msr_code bic_measure_attribs.measure_code % type;
2513 begin
2514 --x_start_date := to_date('01-jan-2000','dd-mm-yyyy');
2515 --x_end_date := to_date('31-jan-2000','dd-mm-yyyy');
2516 --delete from bic_debug where report_id like 'BIC%SUMM%';
2517 open c_sql_measures;
2518 loop
2519 fetch c_sql_measures into x_stmnt1, x_msr_code;
2520 if c_sql_measures % notfound then exit; end if;
2521 g_srl_no := g_srl_no + 1;
2522 x_from_pos := instr(upper(x_stmnt1),'FROM');
2523 x_whr_pos := instr(upper(x_stmnt1),'WHERE');
2524 if (g_org_id is not null) then
2525 x_extra_cond := ' bma.org_id = :x_org_id and ';
2526 else
2527 x_extra_cond := ' ';
2528 end if;
2529 x_stmnt2 := substr(x_stmnt1,1,x_from_pos-1) || ',weight ' ||
2530 substr(x_stmnt1,x_from_pos,x_whr_pos-x_from_pos+5) ||
2531 x_extra_cond ||
2532 substr(x_stmnt1,x_whr_pos+5) || ',weight ';
2533 x_str :=' insert into bic_customer_summary_all (
2534 measure_id
2535 ,customer_id
2536 ,period_start_date
2537 ,org_id
2538 ,value
2539 ,bucket_id
2540 ,score
2541 ,measure_code
2542 ,last_update_date
2543 ,creation_date
2544 ,last_updated_by
2545 ,created_by)
2546 select a.measure_id
2547 ,a.customer_id
2548 ,a.period_start_date
2549 ,a.org_id
2550 ,a.value
2551 ,b.bucket_id
2552 ,nvl(a.weight * b.bucket_points,0) score
2553 ,' || '''' || x_msr_code || '''' ||
2554 ',sysdate
2555 ,sysdate
2556 ,' || to_char(g_created_by) || ',' ||
2557 to_char(g_last_updated_by) || '
2558 from bic_measure_buckets b, (' || x_stmnt2 || ') a
2559 where a.measure_id = b.measure_id(+)
2560 and nvl(a.value,0) >= nvl(b.low_value (+),0)
2561 and nvl(a.value,0) < nvl(b.high_value (+),
2562 nvl(a.value,0)+2) ';
2563 insert into bic_debug(report_id,message) values ('BICSUMMB',x_str);
2564 write_log('Measure Code being processed-'||x_msr_code);
2565 bulk_insert_sql_measures(x_str);
2566 end loop;
2567 close c_sql_measures;
2568 end process_sql_type_measures;
2569 end bic_summary_extract_pkg; -- package body
[Home] [Help]