[Home] [Help]
10: G_BIS_CURRENT_ASOF_DATE CONSTANT varchar2(80) := 'BIS_CURRENT_ASOF_DATE';
11: G_BIS_PREVIOUS_ASOF_DATE CONSTANT VARCHAR2(80) := 'BIS_PREVIOUS_ASOF_DATE';
12: G_AS_OF_DATE CONSTANT varchar2(80) := 'AS_OF_DATE';
13: G_TIME_COMPARISON_TYPE CONSTANT varchar2(80) := 'TIME_COMPARISON_TYPE';
14: --G_PROD_DIRECTLY_ASSIGNED varchar2(80) := ' - '||bim_pmv_dbi_utl_pkg.get_lookup_value('DASS');
15: --G_UNASSIGNED varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('UNA');
16: --G_OTHERS varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('OTH');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
11: G_BIS_PREVIOUS_ASOF_DATE CONSTANT VARCHAR2(80) := 'BIS_PREVIOUS_ASOF_DATE';
12: G_AS_OF_DATE CONSTANT varchar2(80) := 'AS_OF_DATE';
13: G_TIME_COMPARISON_TYPE CONSTANT varchar2(80) := 'TIME_COMPARISON_TYPE';
14: --G_PROD_DIRECTLY_ASSIGNED varchar2(80) := ' - '||bim_pmv_dbi_utl_pkg.get_lookup_value('DASS');
15: --G_UNASSIGNED varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('UNA');
16: --G_OTHERS varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('OTH');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
19: Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_AGE');
12: G_AS_OF_DATE CONSTANT varchar2(80) := 'AS_OF_DATE';
13: G_TIME_COMPARISON_TYPE CONSTANT varchar2(80) := 'TIME_COMPARISON_TYPE';
14: --G_PROD_DIRECTLY_ASSIGNED varchar2(80) := ' - '||bim_pmv_dbi_utl_pkg.get_lookup_value('DASS');
15: --G_UNASSIGNED varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('UNA');
16: --G_OTHERS varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('OTH');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
19: Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_AGE');
20: Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_END_DT');
14: --G_PROD_DIRECTLY_ASSIGNED varchar2(80) := ' - '||bim_pmv_dbi_utl_pkg.get_lookup_value('DASS');
15: --G_UNASSIGNED varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('UNA');
16: --G_OTHERS varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('OTH');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
19: Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_AGE');
20: Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_END_DT');
21: Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_CLOSURE');
22:
15: --G_UNASSIGNED varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('UNA');
16: --G_OTHERS varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('OTH');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
19: Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_AGE');
20: Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_END_DT');
21: Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_CLOSURE');
22:
23:
16: --G_OTHERS varchar2(80) := bim_pmv_dbi_utl_pkg.get_lookup_value('OTH');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
19: Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_AGE');
20: Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_END_DT');
21: Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_CLOSURE');
22:
23:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
17: G_START_DATE CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
18: Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_STATUS');
19: Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_AGE');
20: Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_END_DT');
21: Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_CLOSURE');
22:
23:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
20: Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_END_DT');
21: Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_CLOSURE');
22:
23:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
21: Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_CLOSURE');
22:
23:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
29: L_viewby_cc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('CUSTOMER CATEGORY');
22:
23:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
29: L_viewby_cc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('CUSTOMER CATEGORY');
30: L_viewby_sc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIS_SALES_CHANNELS');
23:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
29: L_viewby_cc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('CUSTOMER CATEGORY');
30: L_viewby_sc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIS_SALES_CHANNELS');
31:
24: L_viewby_ls CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_SOURCE');
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
29: L_viewby_cc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('CUSTOMER CATEGORY');
30: L_viewby_sc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIS_SALES_CHANNELS');
31:
32:
25: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
29: L_viewby_cc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('CUSTOMER CATEGORY');
30: L_viewby_sc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIS_SALES_CHANNELS');
31:
32:
33: PROCEDURE test(parameter IN varchar2 ,
26: L_viewby_lq CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIM_LEAD_QUALITY');
27: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
28: L_viewby_sg CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('JTF_ORG_SALES_GROUP');
29: L_viewby_cc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('CUSTOMER CATEGORY');
30: L_viewby_sc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('BIS_SALES_CHANNELS');
31:
32:
33: PROCEDURE test(parameter IN varchar2 ,
34: value IN varchar2
1986: GROUP BY p.value,p.id,p.leaf_node_flag
1987: UNION ALL
1988: /******Directly Assigned to Category*******/
1989: select /*+ ORDERED */
1990: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,p.id VIEWBYID,''Y'' leaf_node_flag,1 dir_flag,
1991: SUM(case when c.report_date=&BIS_CURRENT_EFFECTIVE_START_DATE - 1 and &BIS_CURRENT_EFFECTIVE_START_DATE<> :g_start_date then b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end) prior_open,
1992: SUM(case when c.report_date=&BIS_CURRENT_ASOF_DATE then b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end) curr_open,
1993: SUM(case when c.report_date=&BIS_CURRENT_ASOF_DATE then b.leads else 0 end) curr_total,
1994: 0 leads_converted,0 leads_new,0 leads_dead,0 leads_closed,SUM(case when c.report_date=&BIS_CURRENT_ASOF_DATE then b.leads_changed else 0 end) curr_leads_changed
2014: GROUP BY p.value,p.id
2015: UNION ALL
2016: /*Others for sales group*/
2017: select /*+ ORDERED */
2018: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,p.id VIEWBYID,''Y'' leaf_node_flag,1 dir_flag,
2019: 0 prior_open,0 curr_open,0 curr_total,sum(leads_converted) leads_converted,sum(leads_new) leads_new,sum(leads_dead) leads_dead,sum(leads_closed) leads_closed,
2020: 0 curr_leads_changed
2021: FROM FII_TIME_RPT_STRUCT c, BIM_I_LP_GEN_SG_MV b
2022: ,(SELECT e.id id,e.value value
2133: AND ( leads_converted <> 0 or leads_new <> 0 or leads_dead <> 0 or leads_closed <> 0 )
2134: GROUP BY p.value,p.id,p.leaf_node_flag
2135: UNION ALL
2136: select /*+ ORDERED */
2137: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
2138: p.id VIEWBYID,''Y'' leaf_node_flag,1 dir_flag,
2139: SUM(case when c.report_date=&BIS_CURRENT_EFFECTIVE_START_DATE - 1 and &BIS_CURRENT_EFFECTIVE_START_DATE<> :g_start_date then b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end) prior_open,
2140: SUM(case when c.report_date=&BIS_CURRENT_ASOF_DATE then b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end) curr_open,
2141: SUM(case when c.report_date=&BIS_CURRENT_ASOF_DATE then b.leads else 0 end) curr_total,
2161: AND b.resource_id =:l_resource_id
2162: GROUP BY p.value,p.id
2163: UNION ALL
2164: select /*+ ORDERED */
2165: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
2166: p.id VIEWBYID,''Y'' leaf_node_flag,1 dir_flag,0 prior_open,0 curr_open,0 curr_total,
2167: sum(leads_converted) leads_converted,sum(leads_new) leads_new,sum(leads_dead) leads_dead,
2168: sum(leads_closed) leads_closed,0 curr_leads_changed
2169: FROM FII_TIME_RPT_STRUCT c,BIM_I_LP_GEN_SG_MV b
2202: l_hint := ' /*+ leading(c) */ ';
2203:
2204: IF l_resource_id is null then
2205: /* First query */
2206: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2207: l_col2_a := ' decode(d.meaning,null,null,b.lead_source) ';
2208: l_tables_a := ' BIM_I_LD_SRC_MV b,as_lookups d ';
2209: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2210: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2214: AND b.update_time_id=-1
2215: AND b.update_period_type_id =-1
2216: AND b.resource_id = :l_resource_id ';
2217: /* Third query */
2218: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2219: l_col2_c := ' decode(d.meaning,null,null,b.lead_source) ';
2220: l_tables_c := ' BIM_I_LD_SRC_MV b , as_lookups d ';
2221: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2222: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2229:
2230: ELSE
2231:
2232: /* Second query */
2233: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2234: l_col2_b := ' decode(d.meaning,null,null,b.lead_source) ';
2235: l_tables_b := ' BIM_I_LD_SRC_MV b,as_lookups d ';
2236: l_where_b := ' AND b.group_id = :l_group_id
2237: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2241: AND b.update_time_id=-1
2242: AND b.update_period_type_id=-1
2243: AND b.resource_id =:l_resource_id ';
2244: /* Fourth query */
2245: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2246: l_col2_d := ' decode(d.meaning,null,null,b.lead_source) ';
2247: l_tables_d := ' BIM_I_LD_SRC_MV b,as_lookups d ';
2248: l_where_d := ' AND b.group_id=:l_group_id
2249: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2258: l_hint := ' /*+ ORDERED */ ';
2259:
2260: IF l_resource_id is null then
2261: /* First query */
2262: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2263: l_col2_a := ' decode(d.meaning,null,null,b.lead_source) ';
2264: l_tables_a := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2265: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2266: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2272: AND b.resource_id = :l_resource_id
2273: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2274: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2275: /* Third query */
2276: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2277: l_col2_c := ' decode(d.meaning,null,null,b.lead_source) ';
2278: l_tables_c := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2279: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2280: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2287: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2288: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2289: ELSE
2290: /* Second query */
2291: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2292: l_col2_b := ' decode(d.meaning,null,null,b.lead_source) ';
2293: l_tables_b := ' BIM_I_LP_SRC_MV b,as_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2294: l_where_b := ' AND b.group_id = :l_group_id
2295: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2301: AND b.resource_id =:l_resource_id
2302: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2303: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2304: /* Fourth query */
2305: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2306: l_col2_d := ' decode(d.meaning,null,null,b.lead_source) ';
2307: l_tables_d := ' BIM_I_LP_SRC_MV b,as_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2308: l_where_d := ' AND b.group_id=:l_group_id
2309: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
2323: l_hint := ' /*+ leading(c) */ ';
2324:
2325: IF l_resource_id is null then
2326: /* First query */
2327: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2328: l_col2_a := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2329: l_tables_a := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
2330: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2331: AND d.rank_id (+)= b.lead_rank_id
2334: AND b.update_time_id=-1
2335: AND b.update_period_type_id =-1
2336: AND b.resource_id = :l_resource_id ';
2337: /* Third query */
2338: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2339: l_col2_c := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2340: l_tables_c := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
2341: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2342: AND d.rank_id (+)= b.lead_rank_id
2346: AND b.update_period_type_id=-1
2347: AND b.resource_id =:l_resource_id ';
2348: ELSE
2349: /* Second query */
2350: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2351: l_col2_b := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2352: l_tables_b := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
2353: l_where_b := ' AND b.group_id = :l_group_id
2354: AND d.rank_id (+)= b.lead_rank_id
2357: AND b.update_time_id=-1
2358: AND b.update_period_type_id=-1
2359: AND b.resource_id =:l_resource_id ';
2360: /* Fourth query */
2361: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2362: l_col2_d := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2363: l_tables_d := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
2364: l_where_d := ' AND b.group_id=:l_group_id
2365: AND d.rank_id (+)= b.lead_rank_id
2373: l_hint := ' /*+ ORDERED */ ';
2374:
2375: IF l_resource_id is null then
2376: /* First query */
2377: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2378: l_col2_a := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2379: l_tables_a := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2380: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2381: AND d.rank_id (+)= b.lead_rank_id
2386: AND b.resource_id = :l_resource_id
2387: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2388: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2389: /* Third query */
2390: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2391: l_col2_c := ' decode(d.meaning,null,null,b.lead_rank_id )';
2392: l_tables_c := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2393: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2394: AND d.rank_id (+)= b.lead_rank_id
2400: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2401: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2402: ELSE
2403: /* Second query */
2404: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2405: l_col2_b := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2406: l_tables_b := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2407: l_where_b := ' AND b.group_id = :l_group_id
2408: AND d.rank_id (+)= b.lead_rank_id
2413: AND b.resource_id =:l_resource_id
2414: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2415: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2416: /* Fourth query */
2417: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
2418: l_col2_d := ' decode(d.meaning,null,null,b.lead_rank_id ) ';
2419: l_tables_d := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2420: l_where_d := ' AND b.group_id=:l_group_id
2421: AND d.rank_id (+)= b.lead_rank_id
2434: l_hint := ' /*+ leading(c) */ ';
2435:
2436: IF l_resource_id is null then
2437: /* First query */
2438: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2439: l_col2_a := ' decode(d.name,null,null,b.lead_country) ';
2440: l_tables_a := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
2441: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2442: AND d.country_code(+) = b.lead_country
2445: AND b.update_time_id=-1
2446: AND b.update_period_type_id =-1
2447: AND b.resource_id =:l_resource_id ';
2448: /* Third query */
2449: l_col1_c := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2450: l_col2_c := ' decode(d.name,null,null,b.lead_country) ';
2451: l_tables_c := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
2452: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2453: AND d.country_code(+) = b.lead_country
2457: AND b.update_period_type_id=-1
2458: AND b.resource_id = :l_resource_id ';
2459: ELSE
2460: /* Second query */
2461: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2462: l_col2_b := ' decode(d.name,null,null,b.lead_country) ';
2463: l_tables_b := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
2464: l_where_b := ' AND b.group_id = :l_group_id
2465: AND d.country_code(+) = b.lead_country
2468: AND b.update_time_id=-1
2469: AND b.update_period_type_id=-1
2470: AND b.resource_id =:l_resource_id ';
2471: /* Fourth query */
2472: l_col1_d := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2473: l_col2_d := ' decode(d.name,null,null,b.lead_country) ';
2474: l_tables_d := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
2475: l_where_d := ' AND b.group_id=:l_group_id
2476: AND d.country_code(+) = b.lead_country
2484: l_hint := ' /*+ ORDERED */ ';
2485:
2486: IF l_resource_id is null then
2487: /* First query */
2488: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2489: l_col2_a := ' decode(d.name,null,null,b.lead_country) ';
2490: l_tables_a := ' BIM_I_LP_REGN_MV b,bis_countries_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2491: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2492: AND d.country_code(+) = b.lead_country
2497: AND b.resource_id = :l_resource_id
2498: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2499: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2500: /* Third query */
2501: l_col1_c := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2502: l_col2_c := ' decode(d.name,null,null,b.lead_country) ';
2503: l_tables_c := ' BIM_I_LP_REGN_MV b,bis_countries_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2504: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2505: AND d.country_code(+) = b.lead_country
2511: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2512: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2513: ELSE
2514: /* Second query */
2515: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2516: l_col2_b := ' decode(d.name,null,null,b.lead_country) ';
2517: l_tables_b := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2518: l_where_b := ' AND b.group_id = :l_group_id
2519: AND d.country_code(+) = b.lead_country
2524: AND b.resource_id = :l_resource_id
2525: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2526: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2527: /* Fourth query */
2528: l_col1_d := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
2529: l_col2_d := ' decode(d.name,null,null,b.lead_country) ';
2530: l_tables_d := ' BIM_I_LP_REGN_MV b,bis_countries_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2531: l_where_d := ' AND b.group_id=:l_group_id
2532: AND d.country_code(+) = b.lead_country
2545: l_hint := ' /*+ leading(c) */ ';
2546:
2547: IF l_resource_id is null then
2548: /* First query */
2549: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2550: l_col2_a := ' decode(d.meaning,null,null,b.channel_code) ';
2551: l_tables_a := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
2552: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2553: AND d.lookup_type(+) = ''SALES_CHANNEL''
2557: AND b.update_time_id=-1
2558: AND b.update_period_type_id =-1
2559: AND b.resource_id = :l_resource_id ';
2560: /* Third query */
2561: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2562: l_col2_c := ' decode(d.meaning,null,null,b.channel_code) ';
2563: l_tables_c := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
2564: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2565: AND d.lookup_type(+) = ''SALES_CHANNEL''
2570: AND b.update_period_type_id=-1
2571: AND b.resource_id =:l_resource_id ';
2572: ELSE
2573: /* Second query */
2574: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2575: l_col2_b := ' decode(d.meaning,null,null,b.channel_code) ';
2576: l_tables_b := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
2577: l_where_b := ' AND b.group_id =:l_group_id
2578: AND d.lookup_type(+) = ''SALES_CHANNEL''
2582: AND b.update_time_id=-1
2583: AND b.update_period_type_id=-1
2584: AND b.resource_id =:l_resource_id';
2585: /* Fourth query */
2586: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2587: l_col2_d := ' decode(d.meaning,null,null,b.channel_code) ';
2588: l_tables_d := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
2589: l_where_d := ' AND b.group_id=:l_group_id
2590: AND d.lookup_type(+) = ''SALES_CHANNEL''
2599: l_hint := ' /*+ ORDERED */ ';
2600:
2601: IF l_resource_id is null then
2602: /* First query */
2603: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2604: l_col2_a := ' decode(d.meaning,null,null,b.channel_code) ';
2605: l_tables_a := ' BIM_I_LP_CHNL_MV b,so_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2606: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2607: AND d.lookup_type(+) = ''SALES_CHANNEL''
2613: AND b.resource_id = :l_resource_id
2614: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2615: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2616: /* Third query */
2617: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2618: l_col2_c := ' decode(d.meaning,null,null,b.channel_code) ';
2619: l_tables_c := ' BIM_I_LP_CHNL_MV b,so_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2620: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2621: AND d.lookup_type(+) = ''SALES_CHANNEL''
2628: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2629: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2630: ELSE
2631: /* Second query */
2632: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2633: l_col2_b := ' decode(d.meaning,null,null,b.channel_code) ';
2634: l_tables_b := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2635: l_where_b := ' AND b.group_id = :l_group_id
2636: AND d.lookup_type(+) = ''SALES_CHANNEL''
2642: AND b.resource_id =:l_resource_id
2643: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2644: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2645: /* Fourth query */
2646: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
2647: l_col2_d := ' decode(d.meaning,null,null,b.channel_code) ';
2648: l_tables_d := ' BIM_I_LP_CHNL_MV b,so_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2649: l_where_d := 'AND b.group_id=:l_group_id
2650: AND d.lookup_type(+) = ''SALES_CHANNEL''
2664: l_hint := ' /*+ leading(c) */ ';
2665:
2666: IF l_resource_id is null then
2667: /* First query */
2668: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2669: l_col2_a := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2670: l_tables_a := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
2671: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2672: AND d.customer_category_code (+) = b.cust_category
2675: AND b.update_time_id=-1
2676: AND b.update_period_type_id =-1
2677: AND b.resource_id = :l_resource_id ';
2678: /* Third query */
2679: l_col1_c := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2680: l_col2_c := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2681: l_tables_c := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
2682: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2683: AND d.customer_category_code (+) = b.cust_category
2687: AND b.update_period_type_id=-1
2688: AND b.resource_id = :l_resource_id ';
2689: ELSE
2690: /* Second query */
2691: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2692: l_col2_b := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2693: l_tables_b := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
2694: l_where_b := 'AND b.group_id = :l_group_id
2695: AND d.customer_category_code (+) = b.cust_category
2698: AND b.update_time_id=-1
2699: AND b.update_period_type_id=-1
2700: AND b.resource_id =:l_resource_id ';
2701: /* Fourth query */
2702: l_col1_d := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2703: l_col2_d := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2704: l_tables_d := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
2705: l_where_d := ' AND b.group_id=:l_group_id
2706: AND d.customer_category_code (+) = b.cust_category
2714: l_hint := ' /*+ ORDERED */ ';
2715:
2716: IF l_resource_id is null then
2717: /* First query */
2718: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2719: l_col2_a := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2720: l_tables_a := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2721: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2722: AND d.customer_category_code (+) = b.cust_category
2727: AND b.resource_id = :l_resource_id
2728: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2729: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2730: /* Third query */
2731: l_col1_c := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2732: l_col2_c := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2733: l_tables_c := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2734: l_where_c := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
2735: AND d.customer_category_code (+) = b.cust_category
2741: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2742: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2743: ELSE
2744: /* Second query */
2745: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2746: l_col2_b := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2747: l_tables_b := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2748: l_where_b := ' AND b.group_id = :l_group_id
2749: AND d.customer_category_code (+) = b.cust_category
2754: AND b.resource_id =:l_resource_id
2755: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
2756: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
2757: /* Fourth query */
2758: l_col1_d := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
2759: l_col2_d := ' decode(d.customer_category_name,null,null,b.cust_category) ';
2760: l_tables_d := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
2761: l_where_d := ' AND b.group_id=:l_group_id
2762: AND d.customer_category_code (+) = b.cust_category
3944: HAVING sum(b.leads-(b.leads_closed+b.leads_dead+b.leads_converted)) <> 0
3945: UNION ALL
3946: /******Directly Assigned to Category*******/
3947: SELECT /*+ leading(c) */
3948: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
3949: p.id VIEWBYID,
3950: ''Y'' leaf_node_flag,
3951: 0 oppt_amt,
3952: 0 prev_oppt_amt,
3975: AND b.item_id = ''-1''
3976: GROUP BY p.value,p.id
3977: UNION ALL
3978: select /*+ leading(c) */
3979: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
3980: p.id VIEWBYID,
3981: ''Y'' leaf_node_flag,
3982: 0 oppt_amt,
3983: 0 prev_oppt_amt,
4037: AND BITAND(c.record_type_id,:l_record_type)=c.record_type_id
4038: GROUP BY p.value,p.id,p.leaf_node_flag
4039: UNION ALL/******Directly Assigned to Category*******/
4040: SELECT /*+ leading(c) */
4041: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
4042: p.id VIEWBYID,
4043: ''Y'' leaf_node_flag,
4044: sum(decode(c.report_date,&BIS_CURRENT_ASOF_DATE,cnv_opty_amt'||l_curr_suffix||',0)) oppt_amt,
4045: sum(decode(c.report_date,&BIS_PREVIOUS_ASOF_DATE,cnv_opty_amt'||l_curr_suffix||',0)) prev_oppt_amt,
4183: GROUP BY p.value,p.id,p.leaf_node_flag
4184: UNION ALL
4185: /******Directly Assigned to Category*******/
4186: SELECT /*+ leading(c) */
4187: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
4188: p.id VIEWBYID,
4189: ''Y'' leaf_node_flag,
4190: 0 oppt_amt,
4191: 0 prev_oppt_amt,
4214: AND b.item_id = ''-1''
4215: GROUP BY p.value,p.id
4216: UNION ALL
4217: select /*+ leading(c) */
4218: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
4219: p.id VIEWBYID,
4220: ''Y'' leaf_node_flag,
4221: 0 oppt_amt,
4222: 0 prev_oppt_amt,
4276: AND BITAND(c.record_type_id,:l_record_type)=c.record_type_id
4277: GROUP BY p.value,p.id,p.leaf_node_flag
4278: UNION ALL /******Directly Assigned to Category*******/
4279: SELECT /*+ leading(c) */
4280: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
4281: p.id VIEWBYID,
4282: ''Y'' leaf_node_flag,
4283: sum(decode(c.report_date,&BIS_CURRENT_ASOF_DATE,cnv_opty_amt'||l_curr_suffix||',0)) oppt_amt,
4284: sum(decode(c.report_date,&BIS_PREVIOUS_ASOF_DATE,cnv_opty_amt'||l_curr_suffix||',0)) prev_oppt_amt,
5771: GROUP BY p.value,p.id,p.leaf_node_flag
5772: UNION ALL
5773: /******Directly Assigned to Category*******/
5774: SELECT /*+ leading(c) */
5775: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
5776: p.id VIEWBYID,
5777: ''Y'' leaf_node_flag,
5778: sum(decode(c.report_date,&BIS_CURRENT_ASOF_DATE,leads_new,0)) leads_new,
5779: sum(decode(c.report_date,&BIS_CURRENT_ASOF_DATE,leads_converted,0)) leads_converted,
5803: GROUP BY p.value,p.id
5804: UNION ALL
5805: /******Leads New Conv Directly Assigned to Category*******/
5806: SELECT /*+ leading(c) */
5807: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
5808: p.id VIEWBYID,
5809: ''Y'' leaf_node_flag,
5810: 0 leads_new,
5811: 0 leads_converted,
5835: GROUP BY p.value,p.id
5836: UNION ALL
5837: /******Leads New Conv Directly Assigned to Category*******/
5838: SELECT /*+ leading(c) */
5839: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
5840: p.id VIEWBYID,
5841: ''Y'' leaf_node_flag,
5842: 0 leads_new,
5843: 0 leads_converted,
5866: AND b.item_id = ''-1''
5867: GROUP BY p.value,p.id
5868: UNION ALL
5869: select /*+ leading(c) */
5870: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
5871: p.id VIEWBYID,
5872: ''Y'' leaf_node_flag,
5873: 0 leads_new,
5874: 0 leads_converted,
6100: GROUP BY p.value,p.id,p.leaf_node_flag
6101: UNION ALL
6102: /******Directly Assigned to Category*******/
6103: SELECT /*+ leading(c) */
6104: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
6105: p.id VIEWBYID,
6106: ''Y'' leaf_node_flag,
6107: sum(decode(c.report_date,&BIS_CURRENT_ASOF_DATE,leads_new,0)) leads_new,
6108: sum(decode(c.report_date,&BIS_CURRENT_ASOF_DATE,leads_converted,0)) leads_converted,
6132: GROUP BY p.value,p.id
6133: UNION ALL
6134: /******Leads New Conv Directly Assigned to Category*******/
6135: SELECT /*+ leading(c) */
6136: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
6137: p.id VIEWBYID,
6138: ''Y'' leaf_node_flag,
6139: 0 leads_new,
6140: 0 leads_converted,
6164: GROUP BY p.value,p.id
6165: UNION ALL
6166: /******Leads New Conv Directly Assigned to Category*******/
6167: SELECT /*+ leading(c) */
6168: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
6169: p.id VIEWBYID,
6170: ''Y'' leaf_node_flag,
6171: 0 leads_new,
6172: 0 leads_converted,
6195: AND b.item_id = ''-1''
6196: GROUP BY p.value,p.id
6197: UNION ALL
6198: select /*+ leading(c) */
6199: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
6200: p.id VIEWBYID,
6201: ''Y'' leaf_node_flag,
6202: 0 leads_new,
6203: 0 leads_converted,
6247: if (l_category_id is null) THEN
6248: IF l_resource_id is null then
6249:
6250: /* First query */
6251: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6252: l_col2_a := ' b.group_id ';
6253: l_tables_a := ' BIM_I_LD_SRC_MV b,as_lookups d ';
6254: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6255: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
6260: AND b.update_period_type_id= -1
6261: AND b.resource_id = :l_resource_id ';
6262:
6263: /* Second query */
6264: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6265: l_col2_b := ' b.group_id ';
6266: l_tables_b := ' BIM_I_LD_SRC_MV b,as_lookups d ';
6267: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6268: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
6273: and b.period_type_id = :l_period_type_id
6274: AND b.resource_id =:l_resource_id ';
6275:
6276: /* eighth query */
6277: l_col1_h := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6278: l_col2_h := ' b.group_id ';
6279: l_tables_h := ' BIM_I_LD_SRC_MV b,as_lookups d ';
6280: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6281: AND d.lookup_type(+) = ''SOURCE_SYSTEM''
6286: and b.period_type_id = :l_period_type_id
6287: AND b.resource_id = :l_resource_id ';
6288:
6289: /* Fifth query */
6290: l_col1_e := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6291: l_col2_e := ' b.group_id ';
6292: l_tables_e := ' BIM_I_LD_SRC_MV b,as_lookups d ';
6293: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6294: AND b.lead_source=d.lookup_code(+)
6300: AND b.resource_id = :l_resource_id ';
6301:
6302: ELSE
6303: /* Third query */
6304: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6305: l_col2_c := ' b.group_id ';
6306: l_tables_c := ' BIM_I_LD_SRC_MV b , as_lookups d ';
6307: l_where_c := ' AND a.resource_id=b.resource_id
6308: AND b.group_id = :l_group_id
6314: AND b.update_period_type_id= -1
6315: AND b.resource_id = :l_resource_id ' ;
6316:
6317: /* Fourth query */
6318: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6319: l_col2_d := ' b.group_id ';
6320: l_tables_d := ' BIM_I_LD_SRC_MV b , as_lookups d ';
6321: l_where_d := ' AND a.resource_id=b.resource_id
6322: AND b.group_id = :l_group_id
6328: and b.period_type_id = :l_period_type_id
6329: AND b.resource_id = :l_resource_id ' ;
6330:
6331: /* Seventh query */
6332: l_col1_g := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6333: l_col2_g := ' b.group_id ';
6334: l_tables_g := ' BIM_I_LD_SRC_MV b , as_lookups d ';
6335: l_where_g := ' AND a.resource_id=b.resource_id
6336: AND b.group_id = :l_group_id
6342: and b.period_type_id = :l_period_type_id
6343: AND b.resource_id = :l_resource_id ' ;
6344:
6345: /* Sixth query */
6346: l_col1_f := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6347: l_col2_f := ' b.group_id ';
6348: l_tables_f := ' BIM_I_LD_SRC_MV b , as_lookups d ';
6349: l_where_f := ' AND a.resource_id=b.resource_id
6350: AND b.group_id = :l_group_id
6362: else
6363:
6364: IF l_resource_id is null then
6365: /* First query */
6366: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6367: l_col2_a := ' b.group_id ';
6368: l_tables_a := ' BIM_I_LP_SRC_MV b,as_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6369: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6370: AND b.lead_source=d.lookup_code(+)
6377: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6378: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6379:
6380: /* Second query */
6381: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6382: l_col2_b := ' b.group_id ';
6383: l_tables_b := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6384: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6385: AND b.lead_source=d.lookup_code(+)
6392: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6393: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6394:
6395: /* Eighth query */
6396: l_col1_h := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6397: l_col2_h := ' b.group_id ';
6398: l_tables_h := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6399: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6400: AND b.lead_source=d.lookup_code(+)
6407: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6408: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6409:
6410: /* Fifth query */
6411: l_col1_e := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6412: l_col2_e := ' b.group_id ';
6413: l_tables_e := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6414: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6415: AND b.lead_source=d.lookup_code(+)
6424:
6425: ELSE
6426:
6427: /* Third query */
6428: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6429: l_col2_c := ' b.group_id ';
6430: l_tables_c := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6431: l_where_c := ' AND a.resource_id=b.resource_id
6432: AND b.group_id = :l_group_id
6440: AND b.update_time_id = -1
6441: AND b.resource_id = :l_resource_id ' ;
6442:
6443: /* Fourth query */
6444: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6445: l_col2_d := ' b.group_id ';
6446: l_tables_d := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6447: l_where_d := ' AND a.resource_id=b.resource_id
6448: AND b.group_id = :l_group_id
6456: and b.period_type_id = :l_period_type_id
6457: AND b.resource_id = :l_resource_id ';
6458:
6459: /* Seventh query */
6460: l_col1_g := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6461: l_col2_g := ' b.group_id ';
6462: l_tables_g := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6463: l_where_g := ' AND a.resource_id=b.resource_id
6464: AND b.group_id = :l_group_id
6472: and b.period_type_id = :l_period_type_id
6473: AND b.resource_id = :l_resource_id ';
6474:
6475: /* Sixth query */
6476: l_col1_f := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6477: l_col2_f := ' b.group_id ';
6478: l_tables_f := ' BIM_I_LP_SRC_MV b,as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6479: l_where_f := ' AND a.resource_id=b.resource_id
6480: AND b.group_id = :l_group_id
6499: if (l_category_id is null) THEN
6500: IF l_resource_id is null then
6501:
6502: /* First query */
6503: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6504: l_col2_a := ' b.group_id ';
6505: l_tables_a := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
6506: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6507: AND d.rank_id (+) = b.lead_rank_id
6511: AND b.update_period_type_id= -1
6512: AND b.resource_id = :l_resource_id ';
6513:
6514: /* Second query */
6515: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6516: l_col2_b := ' b.group_id ';
6517: l_tables_b := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
6518: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6519: AND d.rank_id (+) = b.lead_rank_id
6523: and b.period_type_id = :l_period_type_id
6524: AND b.resource_id = :l_resource_id ';
6525:
6526: /* Eighth query */
6527: l_col1_h := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6528: l_col2_h := ' b.group_id ';
6529: l_tables_h := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
6530: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6531: AND d.rank_id (+) = b.lead_rank_id
6535: and b.period_type_id = :l_period_type_id
6536: AND b.resource_id = :l_resource_id ';
6537:
6538: /* Fifth query */
6539: l_col1_e := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6540: l_col2_e := ' b.group_id ';
6541: l_tables_e := ' BIM_I_LD_QUAL_MV b,as_sales_lead_ranks_vl d ';
6542: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6543: AND d.rank_id (+) = b.lead_rank_id
6548: AND b.resource_id = :l_resource_id ';
6549:
6550: ELSE
6551: /* Third query */
6552: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6553: l_col2_c := ' b.group_id ';
6554: l_tables_c := ' BIM_I_LD_QUAL_MV b , as_sales_lead_ranks_vl d ';
6555: l_where_c := ' AND a.resource_id=b.resource_id
6556: AND b.group_id = :l_group_id
6561: AND b.update_period_type_id= -1
6562: AND b.resource_id = :l_resource_id ' ;
6563:
6564: /* Fourth query */
6565: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6566: l_col2_d := ' b.group_id ';
6567: l_tables_d := ' BIM_I_LD_QUAL_MV b , as_sales_lead_ranks_vl d ';
6568: l_where_d := ' AND a.resource_id=b.resource_id
6569: AND b.group_id = :l_group_id
6574: and b.period_type_id = :l_period_type_id
6575: AND b.resource_id = :l_resource_id ' ;
6576:
6577: /* seventh query */
6578: l_col1_g := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6579: l_col2_g := ' b.group_id ';
6580: l_tables_g := ' BIM_I_LD_QUAL_MV b , as_sales_lead_ranks_vl d ';
6581: l_where_g := ' AND a.resource_id=b.resource_id
6582: AND b.group_id = :l_group_id
6587: and b.period_type_id = :l_period_type_id
6588: AND b.resource_id = :l_resource_id ' ;
6589:
6590: /* Sixth query */
6591: l_col1_f := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6592: l_col2_f := ' b.group_id ';
6593: l_tables_f := ' BIM_I_LD_QUAL_MV b , as_sales_lead_ranks_vl d ';
6594: l_where_f := ' AND a.resource_id=b.resource_id
6595: AND b.group_id = :l_group_id
6606: else
6607:
6608: IF l_resource_id is null then
6609: /* First query */
6610: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6611: l_col2_a := ' b.group_id ';
6612: l_tables_a := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6613: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6614: AND d.rank_id (+) = b.lead_rank_id
6620: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6621: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6622:
6623: /* Second query */
6624: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6625: l_col2_b := ' b.group_id ';
6626: l_tables_b := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6627: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6628: AND d.rank_id (+) = b.lead_rank_id
6635: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6636:
6637:
6638: /* Eighth query */
6639: l_col1_h := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6640: l_col2_h := ' b.group_id ';
6641: l_tables_h := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6642: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6643: AND d.rank_id (+) = b.lead_rank_id
6649: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6650: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6651:
6652: /* Fifth query */
6653: l_col1_e := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6654: l_col2_e := ' b.group_id ';
6655: l_tables_e := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6656: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6657: AND d.rank_id (+) = b.lead_rank_id
6665:
6666: ELSE
6667:
6668: /* Third query */
6669: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6670: l_col2_c := ' b.group_id ';
6671: l_tables_c := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6672: l_where_c := ' AND a.resource_id=b.resource_id
6673: AND b.group_id = :l_group_id
6680: AND b.update_time_id = -1
6681: AND b.resource_id = :l_resource_id ' ;
6682:
6683: /* Fourth query */
6684: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6685: l_col2_d := ' b.group_id ';
6686: l_tables_d := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6687: l_where_d := ' AND a.resource_id=b.resource_id
6688: AND b.group_id = :l_group_id
6695: and b.period_type_id = :l_period_type_id
6696: AND b.resource_id = :l_resource_id ';
6697:
6698: /* seventh query */
6699: l_col1_g := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6700: l_col2_g := ' b.group_id ';
6701: l_tables_g := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6702: l_where_g := ' AND a.resource_id=b.resource_id
6703: AND b.group_id = :l_group_id
6710: and b.period_type_id = :l_period_type_id
6711: AND b.resource_id = :l_resource_id ';
6712:
6713: /* Sixth query */
6714: l_col1_f := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
6715: l_col2_f := ' b.group_id ';
6716: l_tables_f := ' BIM_I_LP_QUAL_MV b,as_sales_lead_ranks_vl d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6717: l_where_f := ' AND a.resource_id=b.resource_id
6718: AND b.group_id = :l_group_id
6736: if (l_category_id is null) THEN
6737: IF l_resource_id is null then
6738:
6739: /* First query */
6740: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6741: l_col2_a := ' b.group_id ';
6742: l_tables_a := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
6743: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6744: AND d.lookup_type(+) = ''SALES_CHANNEL''
6749: AND b.update_period_type_id= -1
6750: AND b.resource_id = :l_resource_id ';
6751:
6752: /* Second query */
6753: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6754: l_col2_b := ' b.group_id ';
6755: l_tables_b := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
6756: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6757: AND d.lookup_type(+) = ''SALES_CHANNEL''
6762: and b.period_type_id = :l_period_type_id
6763: AND b.resource_id = :l_resource_id ';
6764:
6765: /* Eighth query */
6766: l_col1_h := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6767: l_col2_h := ' b.group_id ';
6768: l_tables_h := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
6769: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6770: AND d.lookup_type(+) = ''SALES_CHANNEL''
6775: and b.period_type_id = :l_period_type_id
6776: AND b.resource_id = :l_resource_id ';
6777:
6778: /* Fifth query */
6779: l_col1_e := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6780: l_col2_e := ' b.group_id ';
6781: l_tables_e := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
6782: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6783: AND b.channel_code=d.lookup_code(+)
6789: AND b.resource_id = :l_resource_id ';
6790:
6791: ELSE
6792: /* Third query */
6793: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6794: l_col2_c := ' b.group_id ';
6795: l_tables_c := ' BIM_I_LD_CHNL_MV b , so_lookups d ';
6796: l_where_c := ' AND a.resource_id=b.resource_id
6797: AND b.group_id = :l_group_id
6803: AND b.update_period_type_id= -1
6804: AND b.resource_id = :l_resource_id ' ;
6805:
6806: /* Fourth query */
6807: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6808: l_col2_d := ' b.group_id ';
6809: l_tables_d := ' BIM_I_LD_CHNL_MV b , so_lookups d ';
6810: l_where_d := ' AND a.resource_id=b.resource_id
6811: AND b.group_id = :l_group_id
6817: and b.period_type_id = :l_period_type_id
6818: AND b.resource_id = :l_resource_id ' ;
6819:
6820: /* Seventh query */
6821: l_col1_g := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6822: l_col2_g := ' b.group_id ';
6823: l_tables_g := ' BIM_I_LD_CHNL_MV b , so_lookups d ';
6824: l_where_g := ' AND a.resource_id=b.resource_id
6825: AND b.group_id = :l_group_id
6831: and b.period_type_id = :l_period_type_id
6832: AND b.resource_id = :l_resource_id ' ;
6833:
6834: /* Sixth query */
6835: l_col1_f := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6836: l_col2_f := ' b.group_id ';
6837: l_tables_f := ' BIM_I_LD_CHNL_MV b , so_lookups d ';
6838: l_where_f := ' AND a.resource_id=b.resource_id
6839: AND b.group_id = :l_group_id
6851: else
6852:
6853: IF l_resource_id is null then
6854: /* First query */
6855: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6856: l_col2_a := ' b.group_id ';
6857: l_tables_a := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6858: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6859: AND b.channel_code=d.lookup_code(+)
6866: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6867: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6868:
6869: /* Second query */
6870: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6871: l_col2_b := ' b.group_id ';
6872: l_tables_b := ' BIM_I_LP_CHNL_MV b,so_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6873: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6874: AND b.channel_code=d.lookup_code(+)
6881: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6882: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6883:
6884: /* Second query */
6885: l_col1_h := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6886: l_col2_h := ' b.group_id ';
6887: l_tables_h := ' BIM_I_LP_CHNL_MV b,so_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6888: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6889: AND b.channel_code=d.lookup_code(+)
6896: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
6897: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
6898:
6899: /* Fifth query */
6900: l_col1_e := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6901: l_col2_e := ' b.group_id ';
6902: l_tables_e := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6903: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6904: AND b.channel_code=d.lookup_code(+)
6913:
6914: ELSE
6915:
6916: /* Third query */
6917: l_col1_c := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6918: l_col2_c := ' b.group_id ';
6919: l_tables_c := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6920: l_where_c := ' AND a.resource_id=b.resource_id
6921: AND b.group_id = :l_group_id
6929: AND b.update_time_id = -1
6930: AND b.resource_id = :l_resource_id ' ;
6931:
6932: /* Fourth query */
6933: l_col1_d := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6934: l_col2_d := ' b.group_id ';
6935: l_tables_d := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6936: l_where_d := ' AND a.resource_id=b.resource_id
6937: AND b.group_id = :l_group_id
6945: and b.period_type_id = :l_period_type_id
6946: AND b.resource_id = :l_resource_id ';
6947:
6948: /* Seventh query */
6949: l_col1_g := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6950: l_col2_g := ' b.group_id ';
6951: l_tables_g := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6952: l_where_g := ' AND a.resource_id=b.resource_id
6953: AND b.group_id = :l_group_id
6961: and b.period_type_id = :l_period_type_id
6962: AND b.resource_id = :l_resource_id ';
6963:
6964: /* Sixth query */
6965: l_col1_f := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
6966: l_col2_f := ' b.group_id ';
6967: l_tables_f := ' BIM_I_LP_CHNL_MV b,so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
6968: l_where_f := ' AND a.resource_id=b.resource_id
6969: AND b.group_id = :l_group_id
6988: if (l_category_id is null) THEN
6989: IF l_resource_id is null then
6990:
6991: /* First query */
6992: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
6993: l_col2_a := ' b.group_id ';
6994: l_tables_a := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
6995: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
6996: AND d.country_code (+) = b.lead_country
7000: AND b.update_period_type_id= -1
7001: AND b.resource_id = :l_resource_id ';
7002:
7003: /* Second query */
7004: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7005: l_col2_b := ' b.group_id ';
7006: l_tables_b := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
7007: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7008: AND d.country_code (+) = b.lead_country
7012: and b.period_type_id = :l_period_type_id
7013: AND b.resource_id = :l_resource_id ';
7014:
7015: /* Eighth query */
7016: l_col1_h := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7017: l_col2_h := ' b.group_id ';
7018: l_tables_h := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
7019: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7020: AND d.country_code (+) = b.lead_country
7024: and b.period_type_id = :l_period_type_id
7025: AND b.resource_id = :l_resource_id ';
7026:
7027: /* Fifth query */
7028: l_col1_e := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7029: l_col2_e := ' b.group_id ';
7030: l_tables_e := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
7031: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7032: AND d.country_code (+) = b.lead_country
7037: AND b.resource_id = :l_resource_id ';
7038:
7039: ELSE
7040: /* Third query */
7041: l_col1_c := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7042: l_col2_c := ' b.group_id ';
7043: l_tables_c := ' BIM_I_LD_REGN_MV b , bis_countries_v d ';
7044: l_where_c := ' AND a.resource_id=b.resource_id
7045: AND b.group_id = :l_group_id
7050: AND b.update_period_type_id= -1
7051: AND b.resource_id = :l_resource_id ' ;
7052:
7053: /* Fourth query */
7054: l_col1_d := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7055: l_col2_d := ' b.group_id ';
7056: l_tables_d := ' BIM_I_LD_REGN_MV b , bis_countries_v d ';
7057: l_where_d := ' AND a.resource_id=b.resource_id
7058: AND b.group_id = :l_group_id
7063: and b.period_type_id = :l_period_type_id
7064: AND b.resource_id = :l_resource_id ' ;
7065:
7066: /* Seventh query */
7067: l_col1_g := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7068: l_col2_g := ' b.group_id ';
7069: l_tables_g := ' BIM_I_LD_REGN_MV b , bis_countries_v d ';
7070: l_where_g := ' AND a.resource_id=b.resource_id
7071: AND b.group_id = :l_group_id
7076: and b.period_type_id = :l_period_type_id
7077: AND b.resource_id = :l_resource_id ' ;
7078:
7079: /* Sixth query */
7080: l_col1_f := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7081: l_col2_f := ' b.group_id ';
7082: l_tables_f := ' BIM_I_LD_REGN_MV b , bis_countries_v d ';
7083: l_where_f := ' AND a.resource_id=b.resource_id
7084: AND b.group_id = :l_group_id
7095: else
7096:
7097: IF l_resource_id is null then
7098: /* First query */
7099: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7100: l_col2_a := ' b.group_id ';
7101: l_tables_a := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7102: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7103: AND d.country_code (+) = b.lead_country
7109: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
7110: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
7111:
7112: /* Second query */
7113: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7114: l_col2_b := ' b.group_id ';
7115: l_tables_b := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7116: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7117: AND d.country_code (+) = b.lead_country
7123: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
7124: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
7125:
7126: /* Eighth query */
7127: l_col1_h := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7128: l_col2_h := ' b.group_id ';
7129: l_tables_h := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7130: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7131: AND d.country_code (+) = b.lead_country
7137: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
7138: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
7139:
7140: /* Fifth query */
7141: l_col1_e := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7142: l_col2_e := ' b.group_id ';
7143: l_tables_e := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7144: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7145: AND d.country_code (+) = b.lead_country
7153:
7154: ELSE
7155:
7156: /* Third query */
7157: l_col1_c := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7158: l_col2_c := ' b.group_id ';
7159: l_tables_c := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7160: l_where_c := ' AND a.resource_id=b.resource_id
7161: AND b.group_id = :l_group_id
7168: AND b.update_time_id = -1
7169: AND b.resource_id = :l_resource_id ' ;
7170:
7171: /* Fourth query */
7172: l_col1_d := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7173: l_col2_d := ' b.group_id ';
7174: l_tables_d := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7175: l_where_d := ' AND a.resource_id=b.resource_id
7176: AND b.group_id = :l_group_id
7183: and b.period_type_id = :l_period_type_id
7184: AND b.resource_id = :l_resource_id ';
7185:
7186: /* Seventh query */
7187: l_col1_g := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7188: l_col2_g := ' b.group_id ';
7189: l_tables_g := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7190: l_where_g := ' AND a.resource_id=b.resource_id
7191: AND b.group_id = :l_group_id
7198: and b.period_type_id = :l_period_type_id
7199: AND b.resource_id = :l_resource_id ';
7200:
7201: /* Sixth query */
7202: l_col1_f := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
7203: l_col2_f := ' b.group_id ';
7204: l_tables_f := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7205: l_where_f := ' AND a.resource_id=b.resource_id
7206: AND b.group_id = :l_group_id
7224: if (l_category_id is null) THEN
7225: IF l_resource_id is null then
7226:
7227: /* First query */
7228: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7229: l_col2_a := ' b.group_id ';
7230: l_tables_a := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
7231: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7232: AND d.customer_category_code (+) = b.cust_category
7236: AND b.update_period_type_id= -1
7237: AND b.resource_id = :l_resource_id ';
7238:
7239: /* Second query */
7240: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7241: l_col2_b := ' b.group_id ';
7242: l_tables_b := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
7243: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7244: AND d.customer_category_code (+) = b.cust_category
7248: and b.period_type_id = :l_period_type_id
7249: AND b.resource_id = :l_resource_id ';
7250:
7251: /* Eighth query */
7252: l_col1_h := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7253: l_col2_h := ' b.group_id ';
7254: l_tables_h := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
7255: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7256: AND d.customer_category_code (+) = b.cust_category
7260: and b.period_type_id = :l_period_type_id
7261: AND b.resource_id = :l_resource_id ';
7262:
7263: /* Fifth query */
7264: l_col1_e := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7265: l_col2_e := ' b.group_id ';
7266: l_tables_e := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
7267: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7268: AND d.customer_category_code (+) = b.cust_category
7273: AND b.resource_id = :l_resource_id ';
7274:
7275: ELSE
7276: /* Third query */
7277: l_col1_c := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7278: l_col2_c := ' b.group_id ';
7279: l_tables_c := ' BIM_I_LD_CCAT_MV b , bic_cust_category_v d ';
7280: l_where_c := ' AND a.resource_id=b.resource_id
7281: AND b.group_id = :l_group_id
7286: AND b.update_period_type_id= -1
7287: AND b.resource_id = :l_resource_id ' ;
7288:
7289: /* Fourth query */
7290: l_col1_d := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7291: l_col2_d := ' b.group_id ';
7292: l_tables_d := ' BIM_I_LD_CCAT_MV b , bic_cust_category_v d ';
7293: l_where_d := ' AND a.resource_id=b.resource_id
7294: AND b.group_id = :l_group_id
7301:
7302:
7303:
7304: /* Seventh query */
7305: l_col1_g := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7306: l_col2_g := ' b.group_id ';
7307: l_tables_g := ' BIM_I_LD_CCAT_MV b , bic_cust_category_v d ';
7308: l_where_g := ' AND a.resource_id=b.resource_id
7309: AND b.group_id = :l_group_id
7316:
7317:
7318:
7319: /* Sixth query */
7320: l_col1_f := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7321: l_col2_f := ' b.group_id ';
7322: l_tables_f := ' BIM_I_LD_CCAT_MV b , bic_cust_category_v d ';
7323: l_where_f := ' AND a.resource_id=b.resource_id
7324: AND b.group_id = :l_group_id
7335: else
7336:
7337: IF l_resource_id is null then
7338: /* First query */
7339: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7340: l_col2_a := ' b.group_id ';
7341: l_tables_a := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7342: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7343: AND d.customer_category_code(+) = b.cust_category
7349: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
7350: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
7351:
7352: /* Second query */
7353: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7354: l_col2_b := ' b.group_id ';
7355: l_tables_b := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7356: l_where_b := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7357: AND d.customer_category_code (+) = b.cust_category
7363: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
7364: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
7365:
7366: /* Eighth query */
7367: l_col1_h := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7368: l_col2_h := ' b.group_id ';
7369: l_tables_h := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7370: l_where_h := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7371: AND d.customer_category_code (+) = b.cust_category
7377: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
7378: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
7379:
7380: /* Fifth query */
7381: l_col1_e := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7382: l_col2_e := ' b.group_id ';
7383: l_tables_e := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7384: l_where_e := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
7385: AND d.customer_category_code (+) = b.cust_category
7393:
7394: ELSE
7395:
7396: /* Third query */
7397: l_col1_c := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7398: l_col2_c := ' b.group_id ';
7399: l_tables_c := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7400: l_where_c := ' AND a.resource_id=b.resource_id
7401: AND b.group_id = :l_group_id
7408: AND b.update_time_id = -1
7409: AND b.resource_id = :l_resource_id ' ;
7410:
7411: /* Fourth query */
7412: l_col1_d := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7413: l_col2_d := ' b.group_id ';
7414: l_tables_d := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7415: l_where_d := ' AND a.resource_id=b.resource_id
7416: AND b.group_id = :l_group_id
7423: and b.period_type_id = :l_period_type_id
7424: AND b.resource_id = :l_resource_id ';
7425:
7426: /* Seventh query */
7427: l_col1_g := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7428: l_col2_g := ' b.group_id ';
7429: l_tables_g := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7430: l_where_g := ' AND a.resource_id=b.resource_id
7431: AND b.group_id = :l_group_id
7439: AND b.resource_id = :l_resource_id ';
7440:
7441:
7442: /* Sixth query */
7443: l_col1_f := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
7444: l_col2_f := ' b.group_id ';
7445: l_tables_f := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
7446: l_where_f := ' AND a.resource_id=b.resource_id
7447: AND b.group_id = :l_group_id
8542: AND b.update_time_id = -1
8543: GROUP BY p.value,p.id,p.leaf_node_flag
8544: UNION ALL
8545: select /*+ leading(c) */
8546: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
8547: p.id VIEWBYID,
8548: ''Y'' leaf_node_flag,
8549: 1 dir_flag,
8550: sum(rank_a) rank_a,
8635: AND b.update_time_id = -1
8636: GROUP BY p.value,p.id,p.leaf_node_flag
8637: UNION ALL
8638: select /*+ leading(c) */
8639: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
8640: p.id VIEWBYID,''Y'' leaf_node_flag,1 dir_flag,sum(rank_a) rank_a,sum(rank_b) rank_b,
8641: sum(rank_c) rank_c,sum(rank_d) rank_d,sum(rank_z) rank_z,sum(rank_a + rank_b + rank_c + rank_d + rank_z) rank_total,
8642: sum(leads_qualified) leads_qualified
8643: FROM FII_TIME_RPT_STRUCT c,BIM_I_LP_GEN_SG_MV b ,(select e.id id,e.value value
8672: /* Start assigning the local variables to be substituted in the query */
8673: IF l_resource_id is null then
8674:
8675: /* First query */
8676: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8677: l_col2_a := ' decode(d.meaning,null,null,b.lead_source) ';
8678: l_tables_a := ' BIM_I_LD_SRC_MV b,as_lookups d ';
8679: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8680: AND b.lead_source = d.lookup_code(+)
8687:
8688: ELSE
8689:
8690: /* Second query */
8691: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8692: l_col2_b := ' decode(d.meaning,null,null,b.lead_source) ';
8693: l_tables_b := ' BIM_I_LD_SRC_MV b, as_lookups d ';
8694: l_where_b := ' AND a.resource_id=b.resource_id
8695: AND b.lead_source = d.lookup_code(+)
8706: else
8707:
8708: IF l_resource_id is null then
8709: /* First query */
8710: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8711: l_col2_a := ' decode(d.meaning,null,null,b.lead_source) ';
8712: l_tables_a := ' BIM_I_LP_SRC_MV b,as_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8713: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8714: AND b.lead_source = d.lookup_code(+)
8723:
8724: ELSE
8725:
8726: /* Second query */
8727: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8728: l_col2_b := ' decode(d.meaning,null,null,b.lead_source) ';
8729: l_tables_b := ' BIM_I_LP_SRC_MV b, as_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8730: l_where_b := ' AND a.resource_id=b.resource_id
8731: AND b.group_id = :l_group_id
8751: /* Start assigning the local variables to be substituted in the query */
8752: IF l_resource_id is null then
8753:
8754: /* First query */
8755: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8756: l_col2_a := ' decode(d.meaning,null,null,b.channel_code) ';
8757: l_tables_a := ' BIM_I_LD_CHNL_MV b,so_lookups d ';
8758: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8759: AND b.channel_code = d.lookup_code(+)
8766:
8767: ELSE
8768:
8769: /* Second query */
8770: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8771: l_col2_b := ' decode(d.meaning,null,null,b.channel_code) ';
8772: l_tables_b := ' BIM_I_LD_CHNL_MV b, so_lookups d ';
8773: l_where_b := ' AND a.resource_id=b.resource_id
8774: AND b.channel_code = d.lookup_code(+)
8785: else
8786:
8787: IF l_resource_id is null then
8788: /* First query */
8789: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8790: l_col2_a := ' decode(d.meaning,null,null,b.channel_code) ';
8791: l_tables_a := ' BIM_I_LP_CHNL_MV b,so_lookups d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8792: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8793: AND b.channel_code = d.lookup_code(+)
8803:
8804: ELSE
8805:
8806: /* Second query */
8807: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
8808: l_col2_b := ' decode(d.meaning,null,null,b.channel_code) ';
8809: l_tables_b := ' BIM_I_LP_CHNL_MV b, so_lookups d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8810: l_where_b := ' AND a.resource_id=b.resource_id
8811: AND b.group_id = :l_group_id
8831: /* Start assigning the local variables to be substituted in the query */
8832: IF l_resource_id is null then
8833:
8834: /* First query */
8835: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
8836: l_col2_a := ' decode(d.customer_category_name,null,null,b.cust_category) ';
8837: l_tables_a := ' BIM_I_LD_CCAT_MV b,bic_cust_category_v d ';
8838: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8839: AND d.customer_category_code (+) = b.cust_category
8843: AND b.update_period_type_id= -1
8844: AND b.resource_id = :l_resource_id ';
8845: ELSE
8846: /* Second query */
8847: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
8848: l_col2_b := ' decode(d.customer_category_name,null,null,b.cust_category) ';
8849: l_tables_b := ' BIM_I_LD_CCAT_MV b, bic_cust_category_v d ';
8850: l_where_b := ' AND a.resource_id=b.resource_id
8851: AND d.customer_category_code (+) = b.cust_category
8861: else
8862:
8863: IF l_resource_id is null then
8864: /* First query */
8865: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
8866: l_col2_a := ' decode(d.customer_category_name,null,null,b.cust_category) ';
8867: l_tables_a := ' BIM_I_LP_CCAT_MV b,bic_cust_category_v d, eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8868: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8869: AND d.customer_category_code (+) = b.cust_category
8876: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
8877: ELSE
8878:
8879: /* Second query */
8880: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
8881: l_col2_b := ' decode(d.customer_category_name,null,null,b.cust_category) ';
8882: l_tables_b := ' BIM_I_LP_CCAT_MV b, bic_cust_category_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8883: l_where_b := ' AND a.resource_id=b.resource_id
8884: AND b.group_id = :l_group_id
8903: /* Start assigning the local variables to be substituted in the query */
8904: IF l_resource_id is null then
8905:
8906: /* First query */
8907: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
8908: l_col2_a := ' decode(d.name,null,null,b.lead_country) ';
8909: l_tables_a := ' BIM_I_LD_REGN_MV b,bis_countries_v d ';
8910: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8911: AND d.country_code (+) = b.lead_country
8915: AND b.update_period_type_id= -1
8916: AND b.resource_id = :l_resource_id ';
8917: ELSE
8918: /* Second query */
8919: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
8920: l_col2_b := ' decode(d.name,null,null,b.lead_country) ';
8921: l_tables_b := ' BIM_I_LD_REGN_MV b, bis_countries_v d ';
8922: l_where_b := ' AND a.resource_id=b.resource_id
8923: AND d.country_code (+) = b.lead_country
8933: else
8934:
8935: IF l_resource_id is null then
8936: /* First query */
8937: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
8938: l_col2_a := ' decode(d.name,null,null,b.lead_country) ';
8939: l_tables_a := ' BIM_I_LP_REGN_MV b,bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8940: l_where_a := ' AND b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
8941: AND d.country_code (+) = b.lead_country
8947: AND b.product_category_id = edh.child_id AND edh.object_type = ''CATEGORY_SET'' AND edh.object_id = mtl.category_set_id
8948: AND mtl.functional_area_id = 11 AND edh.dbi_flag = ''Y'' AND edh.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
8949: ELSE
8950: /* Second query */
8951: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
8952: l_col2_b := ' decode(d.name,null,null,b.lead_country) ';
8953: l_tables_b := ' BIM_I_LP_REGN_MV b, bis_countries_v d , eni_denorm_hierarchies edh,mtl_default_category_sets mtl ';
8954: l_where_b := ' AND a.resource_id=b.resource_id
8955: AND b.group_id = :l_group_id
9644: OR SUM(age_29_to_35) > 0
9645: OR SUM(age_36_to_42) > 0
9646: OR SUM(age_42_to_above) > 0
9647: UNION ALL
9648: SELECT bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
9649: b.product_category_id VIEWBYID,e.leaf_node_flag leaf_node_flag,1 dir_flag,
9650: sum(age_3_below) age_3_below, sum(age_3_to_7) age_3_to_7, sum(age_8_to_14) age_8_to_14,
9651: sum(age_15_to_21) age_15_to_21, sum(age_22_to_28) age_22_to_28, sum(age_29_to_35) age_29_to_35,
9652: sum(age_36_to_42) age_36_to_42, sum(age_42_to_above) age_42_to_above,
9730: OR SUM(age_29_to_35) > 0
9731: OR SUM(age_36_to_42) > 0
9732: OR SUM(age_42_to_above) > 0
9733: UNION ALL
9734: SELECT bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' BIM_SALES_GROUP,
9735: b.product_category_id VIEWBYID,e.leaf_node_flag leaf_node_flag,1 dir_flag,
9736: sum(age_3_below) age_3_below, sum(age_3_to_7) age_3_to_7, sum(age_8_to_14) age_8_to_14,
9737: sum(age_15_to_21) age_15_to_21, sum(age_22_to_28) age_22_to_28, sum(age_29_to_35) age_29_to_35,
9738: sum(age_36_to_42) age_36_to_42, sum(age_42_to_above) age_42_to_above,
9765: ELSIF (l_view_by = 'SALES CHANNEL+SALES CHANNEL') THEN
9766: if (l_category_id is null) then
9767: if l_resource_id is null then
9768: /* second query */
9769: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9770: l_col2_b := ' decode(d.meaning,null,null,b.channel_code) ';
9771: l_tables_b := ' BIM_I_LD_AGE_SG_MV b,so_lookups d ';
9772: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9773: AND b.dim_id=''CHANNEL''
9775: AND d.lookup_code(+) = b.channel_code
9776: AND b.resource_id =:l_resource_id ';
9777: else
9778: /* first query */
9779: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9780: l_col2_a := ' decode(d.meaning,null,null,b.channel_code) ';
9781: l_tables_a := ' BIM_I_LD_AGE_SG_MV b,so_lookups d ';
9782: l_where_a := ' a.resource_id=b.resource_id
9783: AND b.group_id = :l_group_id
9788: end if;
9789: else
9790: if l_resource_id is null then
9791: /* second query */
9792: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9793: l_col2_b := ' decode(d.meaning,null,null,b.channel_code) ';
9794: l_tables_b := ' BIM_I_LP_AGE_SG_MV b,so_lookups d ';
9795: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9796: AND b.dim_id=''CHANNEL''
9800: AND b.umark=1
9801: AND b.product_category_id = &ITEM+ENI_ITEM_VBH_CAT';
9802: else
9803: /* first query */
9804: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9805: l_col2_a := ' decode(d.meaning,null,null,b.channel_code) ';
9806: l_tables_a := ' BIM_I_LP_AGE_SG_MV b,so_lookups d ';
9807: l_where_a := ' a.resource_id=b.resource_id
9808: AND b.group_id = :l_group_id
9818: ELSIF (l_view_by = 'GEOGRAPHY+COUNTRY') THEN
9819: if (l_category_id is null) then
9820: if l_resource_id is null then
9821: /* second query */
9822: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
9823: l_col2_b := ' decode(d.name,null,null,b.lead_country) ';
9824: l_tables_b := ' BIM_I_LD_AGE_SG_MV b,bis_countries_v d ';
9825: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9826: AND b.dim_id=''COUNTRY''
9827: AND d.country_code(+) = b.lead_country
9828: AND b.resource_id = :l_resource_id ';
9829: else
9830: /* first query */
9831: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
9832: l_col2_a := ' decode(d.name,null,null,b.lead_country) ';
9833: l_tables_a := ' BIM_I_LD_AGE_SG_MV b,bis_countries_v d ';
9834: l_where_a := ' a.resource_id=b.resource_id
9835: AND b.group_id = :l_group_id
9839: end if;
9840: else
9841: if l_resource_id is null then
9842: /* second query */
9843: l_col1_b := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
9844: l_col2_b := ' decode(d.name,null,null,b.lead_country) ';
9845: l_tables_b := ' BIM_I_LP_AGE_SG_MV b,bis_countries_v d ';
9846: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9847: AND b.dim_id=''COUNTRY''
9850: AND b.umark=1
9851: AND b.product_category_id = &ITEM+ENI_ITEM_VBH_CAT';
9852: else
9853: /* first query */
9854: l_col1_a := ' decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
9855: l_col2_a := ' decode(d.name,null,null,b.lead_country) ';
9856: l_tables_a := ' BIM_I_LP_AGE_SG_MV b,bis_countries_v d ';
9857: l_where_a := ' a.resource_id=b.resource_id
9858: AND b.group_id = :l_group_id
9867: ELSIF (l_view_by = 'BIM_LEAD_ATTRIBUTES+BIM_LEAD_QUALITY') THEN
9868: if (l_category_id is null) then
9869: if l_resource_id is null then
9870: /* second query */
9871: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
9872: l_col2_b := ' decode(d.meaning,null,null,b.lead_rank_id) ';
9873: l_tables_b := ' BIM_I_LD_AGE_SG_MV b,as_sales_lead_ranks_vl d';
9874: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9875: AND b.dim_id=''QUALITY''
9876: AND d.rank_id (+)= b.lead_rank_id
9877: AND b.resource_id = :l_resource_id ';
9878: else
9879: /* first query */
9880: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
9881: l_col2_a := ' decode(d.meaning,null,null,b.lead_rank_id) ';
9882: l_tables_a := ' BIM_I_LD_AGE_SG_MV b,as_sales_lead_ranks_vl d';
9883: l_where_a := ' a.resource_id=b.resource_id
9884: AND b.group_id = :l_group_id
9888: end if;
9889: else
9890: if l_resource_id is null then
9891: /* second query */
9892: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
9893: l_col2_b := ' decode(d.meaning,null,null,b.lead_rank_id) ';
9894: l_tables_b := ' BIM_I_LP_AGE_SG_MV b,as_sales_lead_ranks_vl d ';
9895: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9896: AND b.dim_id=''QUALITY''
9899: AND b.umark=1
9900: AND b.product_category_id = &ITEM+ENI_ITEM_VBH_CAT';
9901: else
9902: /* first query */
9903: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'OTH'||''''||')'||',d.meaning) ';
9904: l_col2_a := ' decode(d.meaning,null,null,b.lead_rank_id) ';
9905: l_tables_a := ' BIM_I_LP_AGE_SG_MV b,as_sales_lead_ranks_vl d ';
9906: l_where_a := ' a.resource_id=b.resource_id
9907: AND b.group_id = :l_group_id
9916: ELSIF (l_view_by = 'BIM_LEAD_ATTRIBUTES+BIM_LEAD_SOURCE') THEN
9917: if (l_category_id is null) then
9918: if l_resource_id is null then
9919: /* second query */
9920: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9921: l_col2_b := ' decode(d.meaning,null,null,b.lead_source) ';
9922: l_tables_b := ' BIM_I_LD_AGE_SG_MV b,as_lookups d';
9923: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9924: AND b.dim_id=''SOURCE''
9926: AND d.lookup_code(+) = b.lead_source
9927: AND b.resource_id = :l_resource_id ';
9928: else
9929: /* first query */
9930: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9931: l_col2_a := ' decode(d.meaning,null,null,b.lead_source) ';
9932: l_tables_a := ' BIM_I_LD_AGE_SG_MV b,as_lookups d';
9933: l_where_a := ' a.resource_id=b.resource_id
9934: AND b.group_id = :l_group_id
9939: end if;
9940: else
9941: if l_resource_id is null then
9942: /* second query */
9943: l_col1_b := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9944: l_col2_b := ' decode(d.meaning,null,null,b.lead_source) ';
9945: l_tables_b := ' BIM_I_LP_AGE_SG_MV b,as_lookups d';
9946: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9947: AND b.dim_id=''SOURCE''
9951: AND b.umark=1
9952: AND b.product_category_id = &ITEM+ENI_ITEM_VBH_CAT';
9953: else
9954: /* first query */
9955: l_col1_a := ' decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
9956: l_col2_a := ' decode(d.meaning,null,null,b.lead_source) ';
9957: l_tables_a := ' BIM_I_LP_AGE_SG_MV b,as_lookups d ';
9958: l_where_a := ' a.resource_id=b.resource_id
9959: AND b.group_id = :l_group_id
9969: ELSIF (l_view_by = 'CUSTOMER CATEGORY+CUSTOMER CATEGORY') THEN
9970: if (l_category_id is null) then
9971: if l_resource_id is null then
9972: /* second query */
9973: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
9974: l_col2_b := ' decode(d.customer_category_name,null,null,b.cust_category) ';
9975: l_tables_b := ' BIM_I_LD_AGE_SG_MV b,bic_cust_category_v d';
9976: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9977: AND b.dim_id=''CUSTCAT''
9978: AND d.customer_category_code (+) = b.cust_category
9979: AND b.resource_id = :l_resource_id ';
9980: else
9981: /* first query */
9982: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
9983: l_col2_a := 'decode(d.customer_category_name,null,null,b.cust_category) ';
9984: l_tables_a := ' BIM_I_LD_AGE_SG_MV b,bic_cust_category_v d';
9985: l_where_a := ' a.resource_id=b.resource_id
9986: AND b.group_id = :l_group_id
9990: end if;
9991: else
9992: if l_resource_id is null then
9993: /* second query */
9994: l_col1_b := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
9995: l_col2_b := ' decode(d.customer_category_name,null,null,b.cust_category) ';
9996: l_tables_b := ' BIM_I_LP_AGE_SG_MV b,bic_cust_category_v d';
9997: l_where_b := ' b.group_id = &ORGANIZATION+JTF_ORG_SALES_GROUP
9998: AND b.dim_id=''CUSTCAT''
10001: AND b.umark=1
10002: AND b.product_category_id = &ITEM+ENI_ITEM_VBH_CAT';
10003: else
10004: /* first query */
10005: l_col1_a := ' decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
10006: l_col2_a := ' decode(d.customer_category_name,null,null,b.cust_category) ';
10007: l_tables_a := ' BIM_I_LP_AGE_SG_MV b,bic_cust_category_v d';
10008: l_where_a := ' a.resource_id=b.resource_id
10009: AND b.group_id = :l_group_id
12025: l_qry:= l_qry||',a.LEAD_closed_DATE BIM_ATTRIBUTE9
12026: ,a.lead_closed_date-a.lead_creation_date BIM_ATTRIBUTE10 ';
12027:
12028: elsif l_col_by ='C' then ---Closed without Conversion (First Intermediate Report)
12029: l_cls_resn_qry:= ' select cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING) BIM_ATTRIBUTE1
12030: ,count(a.lead_id) BIM_ATTRIBUTE2,avg(a.lead_closed_date-lead_creation_date) BIM_ATTRIBUTE3,
12031: '||''''||l_url_str||'''||a.close_reason||''&BIM_PARAMETER8=''||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING) BIM_URL1
12032: -- '||''''||l_url_str||''''||''||'||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING)'||'||'||''''||'&BIM_PARAMETER9=''||a.close_reason BIM_URL1
12033: ,SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL1, sum(avg(a.lead_closed_date-lead_creation_date)*count(a.lead_id)) OVER()/SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL2';
12027:
12028: elsif l_col_by ='C' then ---Closed without Conversion (First Intermediate Report)
12029: l_cls_resn_qry:= ' select cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING) BIM_ATTRIBUTE1
12030: ,count(a.lead_id) BIM_ATTRIBUTE2,avg(a.lead_closed_date-lead_creation_date) BIM_ATTRIBUTE3,
12031: '||''''||l_url_str||'''||a.close_reason||''&BIM_PARAMETER8=''||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING) BIM_URL1
12032: -- '||''''||l_url_str||''''||''||'||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING)'||'||'||''''||'&BIM_PARAMETER9=''||a.close_reason BIM_URL1
12033: ,SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL1, sum(avg(a.lead_closed_date-lead_creation_date)*count(a.lead_id)) OVER()/SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL2';
12034:
12035: elsif l_col_by ='D' then ---Current Open
12028: elsif l_col_by ='C' then ---Closed without Conversion (First Intermediate Report)
12029: l_cls_resn_qry:= ' select cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING) BIM_ATTRIBUTE1
12030: ,count(a.lead_id) BIM_ATTRIBUTE2,avg(a.lead_closed_date-lead_creation_date) BIM_ATTRIBUTE3,
12031: '||''''||l_url_str||'''||a.close_reason||''&BIM_PARAMETER8=''||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING) BIM_URL1
12032: -- '||''''||l_url_str||''''||''||'||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING)'||'||'||''''||'&BIM_PARAMETER9=''||a.close_reason BIM_URL1
12033: ,SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL1, sum(avg(a.lead_closed_date-lead_creation_date)*count(a.lead_id)) OVER()/SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL2';
12034:
12035: elsif l_col_by ='D' then ---Current Open
12036: l_qry:= l_qry||',a.lead_status lead_status
12074: l_frm_c :=' ,as_lookups cls ';
12075: l_whr_c :=' and cls.LOOKUP_CODE(+)=a.close_reason
12076: and cls.LOOKUP_TYPE (+)=''CLOSE_REASON'' ';
12077: l_grp_c := ' group by
12078: decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING),a.close_reason ';
12079: end if;
12080:
12081:
12082:
12548: elsif l_col_by ='C' then ---Closed without Conversion (First Intermediate Report)
12549: l_cls_resn_qry:= ' select cls.value BIM_ATTRIBUTE1
12550: ,count(a.lead_id) BIM_ATTRIBUTE2,avg(a.lead_closed_date-lead_creation_date) BIM_ATTRIBUTE3,
12551: decode(a.close_reason,null,null,'||''''||l_url_str||'''||a.close_reason||''&BIM_PARAMETER8=''||cls.value) BIM_URL1
12552: -- '||''''||l_url_str||''''||''||'||decode(cls.MEANING,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',cls.MEANING)'||'||'||''''||'&BIM_PARAMETER9=''||a.close_reason BIM_URL1
12553: ,SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL1, sum(avg(a.lead_closed_date-lead_creation_date)*count(a.lead_id)) OVER()/SUM(count(a.lead_id)) OVER() BIM_GRAND_TOTAL2';
12554:
12555: elsif l_col_by ='D' then ---Current Open
12556: l_qry:= l_qry||',a.lead_status lead_status