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