APPS.BIC_SUMMARY_EXTRACT_PKG dependencies on BIC_MEASURE_HIERARCHY

Line 845: bic_measure_hierarchy bmh,

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

Line 861: x_operation_code1 bic_measure_hierarchy.operation_code % 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;

Line 862: x_operation_code2 bic_measure_hierarchy.operation_code % 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:

Line 869: from bic_measure_hierarchy

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;

Line 956: bic_measure_hierarchy bmh,

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)

Line 971: x_operation_code1 bic_measure_hierarchy.operation_code % 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;

Line 972: x_operation_code2 bic_measure_hierarchy.operation_code % 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;

Line 1028: from bic_measure_hierarchy bmh,

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

Line 1131: from bic_measure_hierarchy

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