[Home] [Help]
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 +');
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
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
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)
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;
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;
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;
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;
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,
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;
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;*/
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 ,
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);
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:
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;
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;
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;
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;
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:
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
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
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
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;
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;
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;
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
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
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;
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;
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;
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
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;
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;
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);
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
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;
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
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,
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,
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,
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