[Home] [Help]
7: l_cost_type CONSTANT varchar2(50) := fnd_profile.VALUE('BIM_COST_PER_LEAD');
8: l_revenue CONSTANT varchar2(50) := fnd_profile.VALUE('BIM_REVENUE');
9: l_csch_mode CONSTANT varchar2(50) := fnd_profile.VALUE('BIM_CSCH_OA_JTF');
10:
11: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
12: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
13: L_viewby_r CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('REGION');
14: L_viewby_mc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('CHANNEL');
15:
8: l_revenue CONSTANT varchar2(50) := fnd_profile.VALUE('BIM_REVENUE');
9: l_csch_mode CONSTANT varchar2(50) := fnd_profile.VALUE('BIM_CSCH_OA_JTF');
10:
11: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
12: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
13: L_viewby_r CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('REGION');
14: L_viewby_mc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('CHANNEL');
15:
16:
9: l_csch_mode CONSTANT varchar2(50) := fnd_profile.VALUE('BIM_CSCH_OA_JTF');
10:
11: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
12: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
13: L_viewby_r CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('REGION');
14: L_viewby_mc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('CHANNEL');
15:
16:
17: PROCEDURE write_debug(p_param_name varchar2, p_param_id varchar2 ,
10:
11: L_viewby_c CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('COUNTRY');
12: L_viewby_pc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('ENI_ITEM_VBH_CAT');
13: L_viewby_r CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_CONTEXT_VIEWBY('REGION');
14: L_viewby_mc CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('CHANNEL');
15:
16:
17: PROCEDURE write_debug(p_param_name varchar2, p_param_id varchar2 ,
18: p_param_value varchar2 ,
153: BEGIN
154:
155: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
156: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
157: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
158: l_as_of_date,
159: l_period_type,
160: l_record_type_id,
161: l_comp_type,
176: ELSE l_curr_suffix := '';
177: END IF;
178: l_admin_status := GET_ADMIN_STATUS;
179:
180: /*l_previous_as_of_date := BIM_PMV_DBI_UTL_PKG.Previous_Period_Asof_Date(l_as_of_date, l_period_type, l_comp_type);
181: l_curr_aod_str := 'to_date('||to_char(l_as_of_date,'J')||',''J'')';
182: l_prev_aod_str := 'to_date('||to_char(l_previous_as_of_date,'J')||',''J'')';*/
183: l_rsid := GET_RESOURCE_ID;
184: IF l_country IS NULL THEN
674:
675: BEGIN
676: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
677: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
678: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
679: l_as_of_date,
680: l_period_type,
681: l_record_type_id,
682: l_comp_type,
909: &ORDER_BY_CLAUSE';
910: /************** END OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NULL ***********/
911: ELSE
912: /************** START OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NOT NULL ***********/
913: l_dass:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('DASS');
914: l_sqltext :=
915: '
916: SELECT
917: VIEWBY,
1100: /*** directly assigned to the category *************/
1101: UNION ALL
1102: SELECT /*+ORDERED*/
1103: p.id VIEWBYID,
1104: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
1105: ''Y'' leaf_node_flag,
1106: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
1107: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
1108: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1146: GROUP BY p.id
1147: UNION ALL
1148: SELECT /*+ORDERED*/
1149: p.id VIEWBYID,
1150: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
1151: ''Y'' leaf_node_flag,
1152: 0 targeted_audience,
1153: 0 responses_positive,
1154: 0 leads,
1207: /************** END OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NOT NULL ***********/
1208: END IF;
1209:
1210: ELSIF (l_view_by ='MEDIA+MEDIA') THEN
1211: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
1212: l_sqltext :=
1213: '
1214: SELECT
1215: VIEWBY,
1287: SUM(prev_new_opportunity_amt) prev_new_opportunity_amt
1288: FROM
1289: (
1290: SELECT
1291: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1292: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
1293: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
1294: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1295: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.rank_a,0)) rank_a ,
1339: ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1340: AND d.id (+)= a.activity_id
1341: AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1342: AND cal.calendar_id=-1
1343: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
1344: UNION ALL
1345: SELECT
1346: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1347: 0 targeted_audience,
1342: AND cal.calendar_id=-1
1343: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
1344: UNION ALL
1345: SELECT
1346: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1347: 0 targeted_audience,
1348: 0 responses_positive,
1349: 0 leads,
1350: 0 rank_a ,
1391: ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id
1392: AND d.id (+)= a.activity_id
1393: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
1394: AND cal.calendar_id=-1
1395: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
1396: )GROUP BY name
1397: )
1398: )
1399: WHERE
1407: OR BIM_ATTRIBUTE10 > 0
1408: OR BIM_ATTRIBUTE11 > 0
1409: &ORDER_BY_CLAUSE';
1410: ELSIF (l_view_by ='GEOGRAPHY+AREA') THEN
1411: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
1412: l_sqltext :=
1413: '
1414: SELECT
1415: VIEWBY,
1487: SUM(prev_new_opportunity_amt) prev_new_opportunity_amt
1488: FROM
1489: (
1490: SELECT
1491: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1492: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
1493: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
1494: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1495: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.rank_a,0)) rank_a ,
1539: ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1540: AND d.id (+)= a.object_region
1541: AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1542: AND cal.calendar_id=-1
1543: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
1544: UNION ALL
1545: SELECT
1546: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1547: 0 targeted_audience,
1542: AND cal.calendar_id=-1
1543: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
1544: UNION ALL
1545: SELECT
1546: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1547: 0 targeted_audience,
1548: 0 responses_positive,
1549: 0 leads,
1550: 0 rank_a ,
1591: ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id
1592: AND d.id (+)= a.object_region
1593: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
1594: AND cal.calendar_id=-1
1595: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
1596: )GROUP BY name
1597: )
1598: )
1599: WHERE
1607: OR BIM_ATTRIBUTE10 > 0
1608: OR BIM_ATTRIBUTE11 > 0
1609: &ORDER_BY_CLAUSE';
1610: ELSIF (l_view_by ='GEOGRAPHY+COUNTRY') THEN
1611: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
1612: l_sqltext :=
1613: '
1614: SELECT
1615: VIEWBY,
1687: SUM(prev_new_opportunity_amt) prev_new_opportunity_amt
1688: FROM
1689: (
1690: SELECT
1691: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
1692: a.object_country viewbyid,
1693: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
1694: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
1695: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1744: AND a.object_country =d.territory_code(+)
1745: AND d.language(+) = userenv(''LANG'')
1746: AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1747: AND cal.calendar_id=-1
1748: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
1749: UNION ALL
1750: SELECT
1751: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
1752: a.object_country viewbyid,
1747: AND cal.calendar_id=-1
1748: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
1749: UNION ALL
1750: SELECT
1751: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
1752: a.object_country viewbyid,
1753: 0 targeted_audience,
1754: 0 responses_positive,
1755: 0 leads,
1804: AND a.object_country =d.territory_code(+)
1805: AND d.language(+) = userenv(''LANG'')
1806: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
1807: AND cal.calendar_id=-1
1808: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
1809: )GROUP BY name,viewbyid
1810: )
1811: )
1812: WHERE
2044:
2045: if l_object_type='CAMP' THEN
2046: l_csch_chnl :='|| '' - '' || channel';
2047: l_chnl_col := 'channel,';
2048: l_chnl_select := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,';
2049: l_chnl_from:= ' ,bim_dimv_media chnl ';
2050: l_chnl_where := ' AND campname.activity_id =chnl.id (+) ';
2051: l_chnl_group := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) ,';
2052:
2047: l_chnl_col := 'channel,';
2048: l_chnl_select := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,';
2049: l_chnl_from:= ' ,bim_dimv_media chnl ';
2050: l_chnl_where := ' AND campname.activity_id =chnl.id (+) ';
2051: l_chnl_group := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) ,';
2052:
2053: END IF;
2054:
2055:
2489: &ORDER_BY_CLAUSE';
2490: /************** END OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NULL AND CAMPAIGN IS NOT NULL ***********/
2491: ELSE
2492: /************** START OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NOT NULL AND CAMPAIGN IS NOT NULL ***********/
2493: l_dass:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('DASS');
2494: l_sqltext :=
2495: '
2496: SELECT
2497: VIEWBY,
2688: /*** directly assigned to the category *************/
2689: UNION ALL
2690: SELECT /*+ORDERED*/
2691: p.id VIEWBYID,
2692: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
2693: ''Y'' leaf_node_flag,
2694: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
2695: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
2696: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
2738: GROUP BY p.id
2739: UNION ALL
2740: SELECT /*+ORDERED*/
2741: p.id VIEWBYID,
2742: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
2743: ''Y'' leaf_node_flag,
2744: 0 targeted_audience,
2745: 0 responses_positive,
2746: 0 leads,
2802: &ORDER_BY_CLAUSE';
2803: END IF;
2804: /************** START OF VIEW BY IS MARKETING CHANNEL CAMPAIGN IS NOT NULL ***********/
2805: ELSIF (l_view_by ='MEDIA+MEDIA') THEN
2806: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
2807: l_sqltext :=
2808: '
2809: SELECT
2810: VIEWBY,
2882: SUM(prev_new_opportunity_amt) prev_new_opportunity_amt
2883: FROM
2884: (
2885: SELECT
2886: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
2887: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
2888: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
2889: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
2890: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.rank_a,0)) rank_a ,
2940: AND d.id (+)= a.activity_id
2941: AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
2942: AND cal.calendar_id=-1
2943: AND a.source_code_id = :l_campaign_id
2944: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
2945: UNION ALL
2946: SELECT
2947: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
2948: 0 targeted_audience,
2943: AND a.source_code_id = :l_campaign_id
2944: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
2945: UNION ALL
2946: SELECT
2947: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
2948: 0 targeted_audience,
2949: 0 responses_positive,
2950: 0 leads,
2951: 0 rank_a ,
3004: AND d.id (+)= a.activity_id
3005: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
3006: AND cal.calendar_id=-1
3007: AND a.source_code_id = :l_campaign_id
3008: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
3009: )GROUP BY name
3010: )
3011: )
3012: WHERE
3020: OR BIM_ATTRIBUTE10 > 0
3021: OR BIM_ATTRIBUTE11 > 0
3022: &ORDER_BY_CLAUSE';
3023: ELSIF (l_view_by ='GEOGRAPHY+AREA') THEN
3024: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
3025: l_sqltext :=
3026: '
3027: SELECT
3028: VIEWBY,
3100: SUM(prev_new_opportunity_amt) prev_new_opportunity_amt
3101: FROM
3102: (
3103: SELECT
3104: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
3105: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
3106: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
3107: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
3108: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.rank_a,0)) rank_a ,
3158: AND d.id (+)= a.object_region
3159: AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
3160: AND cal.calendar_id=-1
3161: AND a.source_code_id = :l_campaign_id
3162: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
3163: UNION ALL
3164: SELECT
3165: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
3166: 0 targeted_audience,
3161: AND a.source_code_id = :l_campaign_id
3162: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
3163: UNION ALL
3164: SELECT
3165: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
3166: 0 targeted_audience,
3167: 0 responses_positive,
3168: 0 leads,
3169: 0 rank_a ,
3222: AND d.id (+)= a.object_region
3223: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
3224: AND cal.calendar_id=-1
3225: AND a.source_code_id = :l_campaign_id
3226: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
3227: )GROUP BY name
3228: )
3229: )
3230: WHERE
3238: OR BIM_ATTRIBUTE10 > 0
3239: OR BIM_ATTRIBUTE11 > 0
3240: &ORDER_BY_CLAUSE';
3241: ELSIF (l_view_by ='GEOGRAPHY+COUNTRY') THEN
3242: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
3243: l_sqltext :=
3244: '
3245: SELECT
3246: VIEWBY,
3318: SUM(prev_new_opportunity_amt) prev_new_opportunity_amt
3319: FROM
3320: (
3321: SELECT
3322: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
3323: a.object_country viewbyid,
3324: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.customers_targeted,0)) targeted_audience,
3325: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses_positive,0)) responses_positive,
3326: SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
3380: AND d.language(+) = userenv(''LANG'')
3381: AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
3382: AND cal.calendar_id=-1
3383: AND a.source_code_id = :l_campaign_id
3384: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
3385: UNION ALL
3386: SELECT
3387: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
3388: a.object_country viewbyid,
3383: AND a.source_code_id = :l_campaign_id
3384: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
3385: UNION ALL
3386: SELECT
3387: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
3388: a.object_country viewbyid,
3389: 0 targeted_audience,
3390: 0 responses_positive,
3391: 0 leads,
3443: AND d.language(+) = userenv(''LANG'')
3444: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
3445: AND cal.calendar_id=-1
3446: AND a.source_code_id = :l_campaign_id
3447: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
3448: )GROUP BY name,viewbyid
3449: )
3450: )
3451: WHERE
3595:
3596:
3597: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
3598: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
3599: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
3600: l_as_of_date,
3601: l_period_type,
3602: l_record_type_id,
3603: l_comp_type,
4123: )
4124: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
4125: &ORDER_BY_CLAUSE';
4126: ELSE
4127: l_dass:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('DASS');
4128:
4129: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
4130:
4131: l_inner:= ',( SELECT DISTINCT a.source_code_id
4338: /*** directly assigned to the category *************/
4339: UNION ALL
4340: SELECT /*+ORDERED*/
4341: p.id VIEWBYID,
4342: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' viewby,
4343: ''Y'' leaf_node_flag,
4344: sum('||l_prog_cost1||l_curr_suffix||') cost_actual,
4345: 0 cost_forecasted,
4346: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
4378: GROUP BY p.id
4379: UNION ALL
4380: SELECT /*+ORDERED*/
4381: p.id VIEWBYID,
4382: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' viewby,
4383: ''Y'' leaf_node_flag,
4384: 0 cost_actual,
4385: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
4386: 0 actual_revenue ,
4424: end if;
4425:
4426: ELSIF (l_view_by ='MEDIA+MEDIA') THEN
4427:
4428: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
4429:
4430: /********* building inline view to filter those objects do not have any ptd cost or ptd revenue ***********/
4431:
4432: l_inner:= ',( SELECT DISTINCT a.source_code_id
4548: end roi_variance
4549: FROM
4550: (
4551: SELECT
4552: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
4553: sum('|| l_prog_cost1 ||l_curr_suffix||') cost_actual,
4554: 0 cost_forecasted,
4555: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
4556: 0 revenue_forecasted,
4597: ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
4598: AND d.id (+)= a.activity_id
4599: AND cal.report_date =&BIS_CURRENT_ASOF_DATE
4600: AND cal.calendar_id=-1
4601: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.source_code_id
4602: UNION ALL
4603: SELECT
4604: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
4605: 0 cost_actual,
4600: AND cal.calendar_id=-1
4601: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.source_code_id
4602: UNION ALL
4603: SELECT
4604: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
4605: 0 cost_actual,
4606: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
4607: 0 actual_revenue ,
4608: SUM(revenue_forecasted'||l_curr_suffix||') revenue_forecasted,
4645: AND d.id (+)= a.activity_id
4646: AND cal.report_date = trunc(sysdate)
4647: AND cal.calendar_id=-1
4648: AND a.source_code_id=inr.source_code_id
4649: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
4650: )
4651: GROUP BY viewby
4652: )
4653: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
4652: )
4653: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
4654: &ORDER_BY_CLAUSE';
4655: ELSIF (l_view_by ='GEOGRAPHY+AREA') THEN
4656: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
4657: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
4658:
4659: l_inner:= ',( SELECT DISTINCT a.source_code_id
4660: FROM fii_time_rpt_struct_v cal,BIM_I_CPB_METS_MV a';
4775: end roi_variance
4776: FROM
4777: (
4778: SELECT
4779: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
4780: sum('|| l_prog_cost1 ||l_curr_suffix||') cost_actual,
4781: 0 cost_forecasted,
4782: sum('|| l_prog_rev1 ||l_curr_suffix||') actual_revenue ,
4783: 0 revenue_forecasted,
4824: ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
4825: AND d.id (+)= a.object_region
4826: AND cal.report_date =&BIS_CURRENT_ASOF_DATE
4827: AND cal.calendar_id=-1
4828: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
4829: UNION ALL
4830: SELECT
4831: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
4832: 0 cost_actual,
4827: AND cal.calendar_id=-1
4828: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
4829: UNION ALL
4830: SELECT
4831: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
4832: 0 cost_actual,
4833: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
4834: 0 actual_revenue ,
4835: SUM(revenue_forecasted'||l_curr_suffix||') revenue_forecasted,
4871: AND d.id (+)= a.object_region
4872: AND cal.report_date = trunc(sysdate)
4873: AND cal.calendar_id=-1
4874: AND a.source_code_id=inr.source_code_id
4875: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
4876: )
4877: GROUP BY viewby
4878: )
4879: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
4878: )
4879: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
4880: &ORDER_BY_CLAUSE';
4881: ELSIF (l_view_by ='GEOGRAPHY+COUNTRY') THEN
4882: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
4883: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
4884:
4885: l_inner:= '( SELECT DISTINCT a.source_code_id
4886: FROM fii_time_rpt_struct_v cal,BIM_I_CPB_METS_MV a';
5001: end roi_variance
5002: FROM
5003: (
5004: SELECT
5005: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) viewby,
5006: a.object_country viewbyid,
5007: sum('|| l_prog_cost1 ||l_curr_suffix||') cost_actual,
5008: 0 cost_forecasted,
5009: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
5053: AND a.object_country =d.territory_code(+)
5054: AND d.language(+) = userenv(''LANG'')
5055: AND cal.report_date =&BIS_CURRENT_ASOF_DATE
5056: AND cal.calendar_id=-1
5057: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
5058: UNION ALL
5059: SELECT /*+ordered*/
5060: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) viewby,
5061: a.object_country viewbyid,
5056: AND cal.calendar_id=-1
5057: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
5058: UNION ALL
5059: SELECT /*+ordered*/
5060: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) viewby,
5061: a.object_country viewbyid,
5062: 0 cost_actual,
5063: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
5064: 0 actual_revenue ,
5110: AND d.language(+) = userenv(''LANG'')
5111: AND cal.report_date = trunc(sysdate)
5112: AND cal.calendar_id=-1
5113: AND a.source_code_id=inr.source_code_id
5114: group by decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
5115: )GROUP BY viewby,viewbyid
5116: )
5117: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
5118: &ORDER_BY_CLAUSE';
5132:
5133: if l_object_type='CAMP' THEN
5134: l_csch_chnl :='|| '' - '' || channel';
5135: l_chnl_col := 'channel,';
5136: l_chnl_select := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,';
5137: l_chnl_from:= ' ,bim_dimv_media chnl ';
5138: l_chnl_where := ' AND camp.activity_id =chnl.id (+) ';
5139: l_chnl_group := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) ,';
5140:
5135: l_chnl_col := 'channel,';
5136: l_chnl_select := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,';
5137: l_chnl_from:= ' ,bim_dimv_media chnl ';
5138: l_chnl_where := ' AND camp.activity_id =chnl.id (+) ';
5139: l_chnl_group := ' decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) ,';
5140:
5141: END IF;
5142:
5143:
5571:
5572: /************** START OF VIEW BY IS PRODUCT CATEGORY AND PRODUCT CATEGORY IS NOT NULL AND CAMPAIGN ID IS NOT NULL ***********/
5573:
5574: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
5575: l_dass:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('DASS');
5576: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
5577: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,bim_i_cpb_mets_mv a,eni_denorm_hierarchies edh,mtl_default_category_sets mdcs';
5578:
5579: IF l_admin_status = 'N' THEN
5795: /*** directly assigned to the category *************/
5796: UNION ALL
5797: SELECT /*+ORDERED*/
5798: p.id VIEWBYID,
5799: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' viewby,
5800: ''Y'' leaf_node_flag ,
5801: sum('|| l_prog_cost1 ||l_curr_suffix1||') cost_actual,
5802: 0 cost_forecasted,
5803: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
5840: GROUP BY p.id
5841: UNION ALL
5842: SELECT /*+ORDERED*/
5843: p.id VIEWBYID,
5844: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' viewby,
5845: ''Y'' leaf_node_flag,
5846: 0 cost_actual,
5847: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
5848: 0 actual_revenue ,
5889: END IF;
5890: /**********************************************************************************************************/
5891: /************** START OF VIEW BY IS MARKETING CHANNEL CAMPAIGN IS NOT NULL ***********/
5892: ELSIF (l_view_by ='MEDIA+MEDIA') THEN
5893: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
5894:
5895: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
5896: FROM fii_time_rpt_struct_v cal,bim_i_source_codes b,BIM_I_CPB_METS_MV a';
5897:
6003: end roi_variance
6004: FROM
6005: (
6006: SELECT
6007: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
6008: sum('|| l_prog_cost1 ||l_curr_suffix1||') cost_actual,
6009: 0 cost_forecasted,
6010: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
6011: 0 revenue_forecasted,
6054: AND d.id (+)= a.activity_id
6055: AND cal.report_date = &BIS_CURRENT_ASOF_DATE
6056: AND cal.calendar_id=-1
6057: AND a.source_code_id = :l_campaign_id
6058: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
6059: UNION ALL
6060: SELECT
6061: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
6062: 0 cost_actual,
6057: AND a.source_code_id = :l_campaign_id
6058: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
6059: UNION ALL
6060: SELECT
6061: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
6062: 0 cost_actual,
6063: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
6064: 0 actual_revenue ,
6065: SUM(revenue_forecasted'||l_curr_suffix||') revenue_forecasted,
6110: AND d.id (+)= a.activity_id
6111: AND cal.report_date = trunc(sysdate)
6112: AND cal.calendar_id=-1
6113: AND a.source_code_id = :l_campaign_id
6114: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
6115: )GROUP BY viewby
6116: )
6117: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
6118: &ORDER_BY_CLAUSE';
6117: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
6118: &ORDER_BY_CLAUSE';
6119: ELSIF (l_view_by ='GEOGRAPHY+AREA') THEN
6120:
6121: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
6122:
6123: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
6124:
6125: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
6234: end roi_variance
6235: FROM (
6236:
6237: SELECT
6238: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
6239: sum('|| l_prog_cost1 ||l_curr_suffix1||') cost_actual,
6240: 0 cost_forecasted,
6241: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
6242: 0 revenue_forecasted,
6274: AND d.id (+)= a.object_region
6275: AND cal.report_date = &BIS_CURRENT_ASOF_DATE
6276: AND cal.calendar_id=-1
6277: AND a.source_code_id = :l_campaign_id
6278: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
6279: UNION ALL
6280: SELECT
6281: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
6282: 0 cost_actual,
6277: AND a.source_code_id = :l_campaign_id
6278: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
6279: UNION ALL
6280: SELECT
6281: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) viewby,
6282: 0 cost_actual,
6283: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
6284: 0 actual_revenue ,
6285: SUM(revenue_forecasted'||l_curr_suffix||') revenue_forecasted,
6314: AND d.id (+)= a.object_region
6315: AND cal.report_date = trunc(sysdate)
6316: AND cal.calendar_id=-1
6317: AND a.source_code_id = :l_campaign_id
6318: GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value)
6319: )GROUP BY viewby
6320: )
6321: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
6322: &ORDER_BY_CLAUSE';
6321: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
6322: &ORDER_BY_CLAUSE';
6323: ELSIF (l_view_by ='GEOGRAPHY+COUNTRY') THEN
6324:
6325: --l_una:= BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
6326:
6327: /********* building inline view to filter thosedo not have any ptd cost or ptd revenue ***********/
6328:
6329: /* l_inner:= ',( SELECT DISTINCT a.source_code_id
6450: / ( ( sum(revenue_forecasted)- sum(cost_forecasted))/ sum(cost_forecasted)) )*100
6451: end roi_variance
6452: FROM(
6453: SELECT
6454: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) viewby,
6455: a.object_country viewbyid,
6456: sum('|| l_prog_cost1 ||l_curr_suffix1||') cost_actual,
6457: 0 cost_forecasted,
6458: SUM('||l_prog_rev1||l_curr_suffix||') actual_revenue ,
6501: AND d.language(+) = userenv(''LANG'')
6502: AND cal.report_date =&BIS_CURRENT_ASOF_DATE
6503: AND cal.calendar_id=-1
6504: AND a.source_code_id = :l_campaign_id
6505: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
6506: UNION ALL
6507: SELECT
6508: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) viewby,
6509: a.object_country viewbyid,
6504: AND a.source_code_id = :l_campaign_id
6505: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
6506: UNION ALL
6507: SELECT
6508: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) viewby,
6509: a.object_country viewbyid,
6510: 0 cost_actual,
6511: SUM(cost_forecasted'||l_curr_suffix||') cost_forecasted,
6512: 0 actual_revenue ,
6558: AND d.language(+) = userenv(''LANG'')
6559: AND cal.report_date = trunc(sysdate)
6560: AND cal.calendar_id=-1
6561: AND a.source_code_id = :l_campaign_id
6562: GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country
6563: )GROUP BY viewby,viewbyid
6564: )
6565: WHERE cost_actual <>0 OR actual_revenue <>0 OR total_actual_cost <>0 OR total_actual_revenue <>0 OR cost_forecasted <>0 OR revenue_forecasted <>0
6566: &ORDER_BY_CLAUSE';
6673:
6674: BEGIN
6675: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
6676: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
6677: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
6678: l_as_of_date,
6679: l_period_type,
6680: l_record_type_id,
6681: l_comp_type,
6900: l_media VARCHAR2(300);
6901: BEGIN
6902: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
6903: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
6904: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
6905: l_as_of_date,
6906: l_period_type,
6907: l_record_type_id,
6908: l_comp_type,
7124: l_media VARCHAR2(300);
7125: BEGIN
7126: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7127: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7128: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7129: l_as_of_date,
7130: l_period_type,
7131: l_record_type_id,
7132: l_comp_type,
7307: l_media VARCHAR2(300);
7308: BEGIN
7309: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7310: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7311: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7312: l_as_of_date,
7313: l_period_type,
7314: l_record_type_id,
7315: l_comp_type,
7491: l_media VARCHAR2(300);
7492: BEGIN
7493: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7494: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7495: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7496: l_as_of_date,
7497: l_period_type,
7498: l_record_type_id,
7499: l_comp_type,
7672: l_media VARCHAR2(300);
7673: BEGIN
7674: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7675: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7676: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7677: l_as_of_date,
7678: l_period_type,
7679: l_record_type_id,
7680: l_comp_type,
7869:
7870: BEGIN
7871: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
7872: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
7873: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
7874: l_as_of_date,
7875: l_period_type,
7876: l_record_type_id,
7877: l_comp_type,
7893: ELSE l_curr_suffix := '';
7894: END IF;
7895: l_admin_status := GET_ADMIN_STATUS;
7896:
7897: /*l_previous_as_of_date := BIM_PMV_DBI_UTL_PKG.Previous_Period_Asof_Date(l_as_of_date, l_period_type, l_comp_type);
7898: l_curr_aod_str := 'to_date('||to_char(l_as_of_date,'J')||',''J'')';
7899: l_prev_aod_str := 'to_date('||to_char(l_previous_as_of_date,'J')||',''J'')';*/
7900:
7901:
8375: BEGIN
8376: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
8377: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
8378:
8379: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
8380: l_as_of_date,
8381: l_period_type,
8382: l_record_type_id,
8383: l_comp_type,
8436: ELSIF l_record_type_id = 119 THEN l_period_type_id := 128;
8437: ELSE l_period_type_id := 64;
8438: END IF;
8439:
8440: BIM_PMV_DBI_UTL_PKG.GET_TREND_PARAMS( p_page_period_type => l_period_type,
8441: p_comp_type => l_comp_type,
8442: p_curr_as_of_date => l_as_of_date,
8443: p_table_name => l_table_name,
8444: p_column_name => l_time_id_column,
8942: l_prog_cost1 VARCHAR2(30);
8943: BEGIN
8944: x_custom_output := bis_query_attributes_tbl ();
8945: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
8946: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
8947: l_as_of_date,
8948: l_period_type,
8949: l_record_type_id,
8950: l_comp_type,
9100: l_camp_sel_col :=l_camp_sel_col|| ',usage,channel';
9101: l_camp_groupby_col :=l_camp_groupby_col||',usage,channel';
9102: end if;
9103: ELSE
9104: -- l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
9105: l_url_link:=' ,null ';
9106: l_view_disp:='viewby';
9107: END IF;
9108:
9425: /******** inner select start from here */
9426: /* select to get camapign schedules for current period values */
9427: ' SELECT
9428: a.source_code_id VIEWBYID, camp.name name,
9429: camp.object_id object_id, camp.object_type object_type, camp.child_object_usage usage, decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
9430: l_select1 ||
9431: l_from || ' ,bim_i_obj_name_mv camp,bim_dimv_media chnl '||
9432: l_where ||
9433: ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
9433: ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
9434: AND camp.object_type =''CSCH'' AND camp.activity_id =chnl.id (+) AND cal.report_date in ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
9435: AND camp.language=USERENV(''LANG'')'||
9436: l_groupby||
9437: ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
9438: ' UNION ALL /* select to get camapign schedules for previous period values */
9439: SELECT
9440: a.source_code_id VIEWBYID, camp.name name, camp.object_id object_id,camp.object_type object_type, camp.child_object_usage usage,
9441: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
9437: ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
9438: ' UNION ALL /* select to get camapign schedules for previous period values */
9439: SELECT
9440: a.source_code_id VIEWBYID, camp.name name, camp.object_id object_id,camp.object_type object_type, camp.child_object_usage usage,
9441: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
9442: l_select2 || l_from1 || ' ,bim_i_obj_name_mv camp,bim_dimv_media chnl '||
9443: l_where || ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,1143)= cal.record_type_id AND camp.object_type =''CSCH''
9444: AND camp.activity_id =chnl.id (+) AND cal.report_date = trunc(sysdate) AND camp.language=USERENV(''LANG'')'||
9445: l_groupby|| ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
9441: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
9442: l_select2 || l_from1 || ' ,bim_i_obj_name_mv camp,bim_dimv_media chnl '||
9443: l_where || ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,1143)= cal.record_type_id AND camp.object_type =''CSCH''
9444: AND camp.activity_id =chnl.id (+) AND cal.report_date = trunc(sysdate) AND camp.language=USERENV(''LANG'')'||
9445: l_groupby|| ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
9446: l_select_filter_camp ;
9447: /*************** if object type is event then childern are event schedules ***/
9448:
9449:
9612: IF l_cat_id is not null THEN
9613:
9614:
9615: l_pc_col:=' SELECT /*+ORDERED*/
9616: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
9617: p.id viewbyid,
9618: ''Y'' leaf_node_flag,
9619: null meaning ';
9620: l_pc_groupby := ' GROUP BY p.id ';
9644: /** l_union_inc and l_col_inc ,,is for performance gain ,for country and product category for admin combination */
9645: l_union_inc := ',fii_time_rpt_struct_v cal ,BIM_I_OBJ_METS_MV a,eni_denorm_hierarchies edh,mtl_default_category_sets mdcs
9646: ,fnd_territories_tl d ';
9647:
9648: l_col_inc :='SELECT /*+ ordered */ decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
9649: a.object_country viewbyid, null meaning ';
9650:
9651: IF l_cat_id is null then
9652: l_where := l_where ||l_pc_where;
9654: l_from := l_from ||l_pc_from;
9655: l_where := l_where||l_pc_where;
9656: END IF;
9657:
9658: l_col:=' SELECT decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
9659: a.object_country viewbyid, null meaning ';
9660:
9661: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
9662: l_from:=' FROM fnd_territories_tl d '||l_from;
9657:
9658: l_col:=' SELECT decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
9659: a.object_country viewbyid, null meaning ';
9660:
9661: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
9662: l_from:=' FROM fnd_territories_tl d '||l_from;
9663: IF l_campaign_id is null then
9664: IF l_admin_status ='Y' THEN
9665: l_from1:=l_from||' ,BIM_I_OBJ_METS_MV a ';
9699: l_from := l_from ||l_pc_from;
9700: l_where := l_where||l_pc_where;
9701: END IF;
9702: l_col:=' SELECT
9703: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
9704: null viewbyid,
9705: null meaning ';
9706: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
9707: l_from:=' FROM bim_dimv_media d '||l_from;
9702: l_col:=' SELECT
9703: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
9704: null viewbyid,
9705: null meaning ';
9706: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
9707: l_from:=' FROM bim_dimv_media d '||l_from;
9708: IF l_campaign_id is null then
9709: IF l_admin_status ='Y' THEN
9710:
9743: l_from := l_from ||l_pc_from;
9744: l_where := l_where||l_pc_where;
9745: END IF;
9746: l_col:=' SELECT
9747: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
9748: null viewbyid,
9749: null meaning ';
9750: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
9751: l_from:=' FROM bis_areas_v d '||l_from;
9746: l_col:=' SELECT
9747: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
9748: null viewbyid,
9749: null meaning ';
9750: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
9751: l_from:=' FROM bis_areas_v d '||l_from;
9752: IF l_campaign_id is null then
9753: IF l_admin_status ='Y' THEN
9754: l_from1:=l_from||' ,BIM_OBJ_REGN_MV a ';
9964: l_prog_cost1 VARCHAR2(30);
9965: BEGIN
9966: x_custom_output := bis_query_attributes_tbl ();
9967: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
9968: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
9969: l_as_of_date,
9970: l_period_type,
9971: l_record_type_id,
9972: l_comp_type,
10132: l_camp_sel_col :=l_camp_sel_col|| ',usage,channel';
10133: l_camp_groupby_col :=l_camp_groupby_col||',usage,channel';
10134: end if;
10135: ELSE
10136: -- l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
10137: l_url_link:=' ,null ';
10138: l_view_disp:='viewby';
10139: END IF;
10140:
10410: l_sqltext:= l_select_cal||
10411: /******** inner select start from here */
10412: /* select to get camapign schedules for current period values */
10413: ' SELECT a.source_code_id VIEWBYID, camp.name name, camp.object_id object_id, camp.object_type object_type,
10414: camp.child_object_usage usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
10415: l_select1 || l_from || ' ,bim_I_obj_name_mv camp,bim_dimv_media chnl '||
10416: l_where ||
10417: ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
10418: AND camp.object_type =''CSCH'' AND camp.activity_id =chnl.id (+) AND cal.report_date in ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
10417: ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
10418: AND camp.object_type =''CSCH'' AND camp.activity_id =chnl.id (+) AND cal.report_date in ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
10419: AND camp.language=USERENV(''LANG'')'||
10420: l_groupby||
10421: ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
10422: ' UNION ALL /* select to get camapign schedules for previous period values */
10423: SELECT
10424: a.source_code_id VIEWBYID, camp.name name, camp.object_id object_id,camp.object_type object_type,
10425: camp.child_object_usage usage, decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
10421: ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
10422: ' UNION ALL /* select to get camapign schedules for previous period values */
10423: SELECT
10424: a.source_code_id VIEWBYID, camp.name name, camp.object_id object_id,camp.object_type object_type,
10425: camp.child_object_usage usage, decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel, camp.object_type_mean meaning '||
10426: l_select2 ||
10427: l_from || ' ,bim_I_obj_name_mv camp,bim_dimv_media chnl '||
10428: l_where ||
10429: ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,1143)= cal.record_type_id
10429: ' AND camp.source_code_id = a.source_code_id AND BITAND(cal.record_type_id,1143)= cal.record_type_id
10430: AND CAMP.object_type =''CSCH'' AND camp.activity_id =chnl.id (+) AND cal.report_date = trunc(sysdate)
10431: AND camp.language=USERENV(''LANG'')'||
10432: l_groupby||
10433: ' camp.name,camp.object_id,camp.object_type,camp.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
10434: l_select_filter_camp;
10435: /*************** if object type is event then childern are event schedules ***/
10436:
10437:
10605: END IF;
10606: /* building l_pc_select to get values directly assigned to product category passed from the page */
10607: IF l_cat_id is not null THEN
10608: l_pc_col:=' SELECT /*+ORDERED*/
10609: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
10610: p.id viewbyid,
10611: ''Y'' leaf_node_flag,
10612: null meaning ';
10613: l_pc_groupby := ' GROUP BY p.id ';
10635:
10636: l_union_inc := ',fii_time_rpt_struct_v cal ,BIM_I_OBJ_METS_MV a,eni_denorm_hierarchies edh,mtl_default_category_sets mdcs
10637: ,fnd_territories_tl d ';
10638:
10639: l_col_inc :='SELECT /*+ ordered */ decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
10640: a.object_country viewbyid, null meaning ';
10641:
10642:
10643: IF l_cat_id is null then
10646: l_from := l_from ||l_pc_from;
10647: l_where := l_where||l_pc_where;
10648: END IF;
10649: l_col:=' SELECT
10650: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
10651: a.object_country viewbyid, null meaning ';
10652: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country';
10653: l_from:=' FROM fnd_territories_tl d '||l_from;
10654: IF l_campaign_id is null then
10648: END IF;
10649: l_col:=' SELECT
10650: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
10651: a.object_country viewbyid, null meaning ';
10652: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country';
10653: l_from:=' FROM fnd_territories_tl d '||l_from;
10654: IF l_campaign_id is null then
10655: IF l_admin_status ='Y' THEN
10656:
10687: l_from := l_from ||l_pc_from;
10688: l_where := l_where||l_pc_where;
10689: END IF;
10690: l_col:=' SELECT
10691: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
10692: null viewbyid,
10693: null meaning ';
10694: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
10695: l_from:=' FROM bim_dimv_media d '||l_from;
10690: l_col:=' SELECT
10691: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
10692: null viewbyid,
10693: null meaning ';
10694: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
10695: l_from:=' FROM bim_dimv_media d '||l_from;
10696: IF l_campaign_id is null then
10697: IF l_admin_status ='Y' THEN
10698:
10732: l_from := l_from ||l_pc_from;
10733: l_where := l_where||l_pc_where;
10734: END IF;
10735: l_col:=' SELECT
10736: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
10737: null viewbyid,
10738: null meaning ';
10739: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
10740: l_from:=' FROM bis_areas_v d '||l_from;
10735: l_col:=' SELECT
10736: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
10737: null viewbyid,
10738: null meaning ';
10739: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
10740: l_from:=' FROM bis_areas_v d '||l_from;
10741: IF l_campaign_id is null then
10742: IF l_admin_status ='Y' THEN
10743:
10932: l_media VARCHAR2(300);
10933: BEGIN
10934: x_custom_output := bis_query_attributes_tbl ();
10935: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
10936: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
10937: l_as_of_date,
10938: l_period_type,
10939: l_record_type_id,
10940: l_comp_type,
11006: l_camp_sel_col :=l_camp_sel_col|| ',usage,channel';
11007: l_camp_groupby_col :=l_camp_groupby_col||',usage,channel';
11008: end if;
11009: ELSE
11010: --l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
11011: l_url_link:=' ,null ';
11012: l_view_disp:='viewby';
11013: END IF;
11014: /* l_select_cal is common part of select statement for all view by to calculate grand totals and change */
11250: ,name.name name
11251: ,name.object_id object_id
11252: ,name.object_type object_type
11253: ,name.child_object_usage usage
11254: ,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel
11255: ,name.object_type_mean meaning'||
11256: l_comm_cols ||
11257: l_from || ' , bim_dimv_media chnl '||
11258: l_where ||
11258: l_where ||
11259: ' AND name.activity_id =chnl.id (+)
11260: AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
11261: AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id '||
11262: l_groupby||' , name.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||'
11263: UNION ALL
11264: SELECT
11265: a.source_code_id VIEWBYID
11266: ,name.name name
11266: ,name.name name
11267: ,name.object_id object_id
11268: ,name.object_type object_type
11269: ,name.child_object_usage usage
11270: ,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel
11271: ,name.object_type_mean meaning '||
11272: l_comm2_cols ||
11273: l_from || ' , bim_dimv_media chnl '||
11274: l_where ||
11274: l_where ||
11275: ' AND name.activity_id =chnl.id (+)
11276: AND cal.report_date = &BIS_CURRENT_EFFECTIVE_START_DATE - 1
11277: AND BITAND(cal.record_type_id,1143)= cal.record_type_id'||
11278: l_groupby||' , name.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
11279: l_select_filter ;
11280: END IF;
11281: END IF;
11282: /***** END CAMPAIGN HIRERACHY VIEW HANDLING ******************/
11396: END IF;
11397: /* building l_pc_select to get values directly assigned to product category passed from the page */
11398: IF l_cat_id is not null THEN
11399: l_pc_col:=' SELECT /*+ORDERED*/
11400: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
11401: p.id viewbyid,
11402: ''Y'' leaf_node_flag,
11403: null meaning ';
11404: l_pc_groupby := ' GROUP BY p.id';
11428: l_from := l_from ||l_pc_from;
11429: l_where := l_where||l_pc_where;
11430: END IF;
11431: l_col:=' SELECT
11432: decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) name,
11433: null viewbyid,
11434: null meaning ';
11435: l_groupby := ' GROUP BY decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
11436: l_from:=' FROM bic_cust_category_v d '||l_from;
11431: l_col:=' SELECT
11432: decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) name,
11433: null viewbyid,
11434: null meaning ';
11435: l_groupby := ' GROUP BY decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
11436: l_from:=' FROM bic_cust_category_v d '||l_from;
11437:
11438: IF l_campaign_id is null then
11439: IF l_admin_status ='Y' THEN
11462: l_from := l_from ||l_pc_from;
11463: l_where := l_where||l_pc_where;
11464: END IF;
11465: l_col:=' SELECT
11466: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
11467: null viewbyid,
11468: null meaning ';
11469: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
11470: l_from:=' FROM as_lookups d '||l_from;
11465: l_col:=' SELECT
11466: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
11467: null viewbyid,
11468: null meaning ';
11469: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
11470: l_from:=' FROM as_lookups d '||l_from;
11471: IF l_campaign_id is null then
11472: IF l_admin_status ='Y' THEN
11473: l_from:=l_from||' ,bim_mkt_ld_src_mv a ';
11498: l_from := l_from ||l_pc_from;
11499: l_where := l_where||l_pc_where;
11500: END IF;
11501: l_col:=' SELECT
11502: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
11503: null viewbyid,
11504: null meaning ';
11505: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
11506: l_from:=' FROM so_lookups d '||l_from;
11501: l_col:=' SELECT
11502: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
11503: null viewbyid,
11504: null meaning ';
11505: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
11506: l_from:=' FROM so_lookups d '||l_from;
11507: IF l_campaign_id is null then
11508: IF l_admin_status ='Y' THEN
11509: l_from:=l_from||' ,bim_mkt_ld_chnl_mv a ';
11534: l_from := l_from ||l_pc_from;
11535: l_where := l_where||l_pc_where;
11536: END IF;
11537: l_col:=' SELECT
11538: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
11539: a.object_country viewbyid,
11540: null meaning ';
11541: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
11542: l_from:=' FROM fnd_territories_tl d '||l_from;
11537: l_col:=' SELECT
11538: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
11539: a.object_country viewbyid,
11540: null meaning ';
11541: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
11542: l_from:=' FROM fnd_territories_tl d '||l_from;
11543: IF l_campaign_id is null then
11544: IF l_admin_status ='Y' THEN
11545: l_from:=l_from||' ,bim_i_obj_mets_mv a ';
11569: l_from := l_from ||l_pc_from;
11570: l_where := l_where||l_pc_where;
11571: END IF;
11572: l_col:=' SELECT
11573: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
11574: null viewbyid,
11575: null meaning ';
11576: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
11577: l_from:=' FROM as_sales_lead_ranks_vl d '||l_from;
11572: l_col:=' SELECT
11573: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
11574: null viewbyid,
11575: null meaning ';
11576: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
11577: l_from:=' FROM as_sales_lead_ranks_vl d '||l_from;
11578: IF l_campaign_id is null then
11579: IF l_admin_status ='Y' THEN
11580: l_from:=l_from||' ,bim_mkt_ld_qual_mv a ';
11602: l_from := l_from ||l_pc_from;
11603: l_where := l_where||l_pc_where;
11604: END IF;
11605: l_col:=' SELECT
11606: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
11607: null viewbyid,
11608: null meaning ';
11609: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
11610: l_from:=' FROM bim_dimv_media d '||l_from;
11605: l_col:=' SELECT
11606: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
11607: null viewbyid,
11608: null meaning ';
11609: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
11610: l_from:=' FROM bim_dimv_media d '||l_from;
11611: IF l_campaign_id is null then
11612: IF l_admin_status ='Y' THEN
11613: l_from:=l_from||' ,bim_mkt_chnl_mv a ';
11635: l_from := l_from ||l_pc_from;
11636: l_where := l_where||l_pc_where;
11637: END IF;
11638: l_col:=' SELECT
11639: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
11640: null viewbyid,
11641: null meaning ';
11642: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
11643: l_from:=' FROM bis_areas_v d '||l_from;
11638: l_col:=' SELECT
11639: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
11640: null viewbyid,
11641: null meaning ';
11642: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
11643: l_from:=' FROM bis_areas_v d '||l_from;
11644: IF l_campaign_id is null then
11645: IF l_admin_status ='Y' THEN
11646: l_from:=l_from||' ,bim_mkt_regn_mv a ';
11813:
11814: BEGIN
11815: x_custom_output := bis_query_attributes_tbl ();
11816: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
11817: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
11818: l_as_of_date,
11819: l_period_type,
11820: l_record_type_id,
11821: l_comp_type,
11890: l_camp_sel_col :=l_camp_sel_col|| ',usage,channel';
11891: l_camp_groupby_col :=l_camp_groupby_col||',usage,channel';
11892: end if;
11893: ELSE
11894: --l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
11895: l_url_link:=' ,null ';
11896: l_view_disp:='viewby';
11897: END IF;
11898: /* l_select_cal is common part of select statement for all view by to calculate grand totals and change */
12147: name.name name,
12148: name.object_id object_id,
12149: name.object_type object_type,
12150: name.child_object_usage usage,
12151: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel
12152: ,name.object_type_mean meaning '||
12153: l_comm_cols ||
12154: l_from || ' , bim_dimv_media chnl '||
12155: l_where ||
12155: l_where ||
12156: ' AND name.activity_id =chnl.id (+)
12157: AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)'||
12158: l_groupby||
12159: ' , name.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
12160: l_select_filter ;
12161: END IF;
12162: END IF;
12163:
12279: END IF;
12280: /* building l_pc_select to get values directly assigned to product category passed from the page */
12281: IF l_cat_id is not null THEN
12282: l_pc_col:=' SELECT /*+ORDERED*/
12283: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
12284: p.id viewbyid,
12285: ''Y'' leaf_node_flag,
12286: null meaning ';
12287: l_pc_groupby := ' GROUP BY p.id ';
12302: l_from := l_from ||l_pc_from;
12303: l_where := l_where||l_pc_where;
12304: END IF;
12305: l_col:=' SELECT
12306: decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) name,
12307: null viewbyid,
12308: null meaning ';
12309: l_groupby := ' GROUP BY decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
12310: l_from:=' FROM bic_cust_category_v d '||l_from;
12305: l_col:=' SELECT
12306: decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) name,
12307: null viewbyid,
12308: null meaning ';
12309: l_groupby := ' GROUP BY decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
12310: l_from:=' FROM bic_cust_category_v d '||l_from;
12311:
12312: IF l_campaign_id is null then
12313: IF l_admin_status ='Y' THEN
12336: l_from := l_from ||l_pc_from;
12337: l_where := l_where||l_pc_where;
12338: END IF;
12339: l_col:=' SELECT
12340: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
12341: null viewbyid,
12342: null meaning ';
12343: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
12344: l_from:=' FROM as_lookups d '||l_from;
12339: l_col:=' SELECT
12340: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
12341: null viewbyid,
12342: null meaning ';
12343: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
12344: l_from:=' FROM as_lookups d '||l_from;
12345: IF l_campaign_id is null then
12346: IF l_admin_status ='Y' THEN
12347: l_from:=l_from||' ,bim_mkt_ld_src_mv a ';
12372: l_from := l_from ||l_pc_from;
12373: l_where := l_where||l_pc_where;
12374: END IF;
12375: l_col:=' SELECT
12376: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
12377: null viewbyid,
12378: null meaning ';
12379: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
12380: l_from:=' FROM so_lookups d '||l_from;
12375: l_col:=' SELECT
12376: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
12377: null viewbyid,
12378: null meaning ';
12379: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
12380: l_from:=' FROM so_lookups d '||l_from;
12381: IF l_campaign_id is null then
12382: IF l_admin_status ='Y' THEN
12383: l_from:=l_from||' ,bim_mkt_ld_chnl_mv a ';
12408: l_from := l_from ||l_pc_from;
12409: l_where := l_where||l_pc_where;
12410: END IF;
12411: l_col:=' SELECT
12412: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
12413: a.object_country viewbyid,
12414: null meaning ';
12415: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
12416: l_from:=' FROM fnd_territories_tl d '||l_from;
12411: l_col:=' SELECT
12412: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
12413: a.object_country viewbyid,
12414: null meaning ';
12415: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
12416: l_from:=' FROM fnd_territories_tl d '||l_from;
12417: IF l_campaign_id is null then
12418: IF l_admin_status ='Y' THEN
12419: l_from:=l_from||' ,bim_i_obj_mets_mv a ';
12443: l_from := l_from ||l_pc_from;
12444: l_where := l_where||l_pc_where;
12445: END IF;
12446: l_col:=' SELECT
12447: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
12448: null viewbyid,
12449: null meaning ';
12450: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
12451: l_from:=' FROM as_sales_lead_ranks_vl d '||l_from;
12446: l_col:=' SELECT
12447: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
12448: null viewbyid,
12449: null meaning ';
12450: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
12451: l_from:=' FROM as_sales_lead_ranks_vl d '||l_from;
12452: IF l_campaign_id is null then
12453: IF l_admin_status ='Y' THEN
12454: l_from:=l_from||' ,bim_mkt_ld_qual_mv a ';
12476: l_from := l_from ||l_pc_from;
12477: l_where := l_where||l_pc_where;
12478: END IF;
12479: l_col:=' SELECT
12480: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
12481: null viewbyid,
12482: null meaning ';
12483: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
12484: l_from:=' FROM bim_dimv_media d '||l_from;
12479: l_col:=' SELECT
12480: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
12481: null viewbyid,
12482: null meaning ';
12483: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
12484: l_from:=' FROM bim_dimv_media d '||l_from;
12485: IF l_campaign_id is null then
12486: IF l_admin_status ='Y' THEN
12487: l_from:=l_from||' ,bim_mkt_chnl_mv a ';
12509: l_from := l_from ||l_pc_from;
12510: l_where := l_where||l_pc_where;
12511: END IF;
12512: l_col:=' SELECT
12513: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
12514: null viewbyid,
12515: null meaning ';
12516: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
12517: l_from:=' FROM bis_areas_v d '||l_from;
12512: l_col:=' SELECT
12513: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
12514: null viewbyid,
12515: null meaning ';
12516: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
12517: l_from:=' FROM bis_areas_v d '||l_from;
12518: IF l_campaign_id is null then
12519: IF l_admin_status ='Y' THEN
12520: l_from:=l_from||' ,bim_mkt_regn_mv a ';
12680: BEGIN
12681: x_custom_output := bis_query_attributes_tbl ();
12682: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
12683:
12684: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
12685: l_as_of_date,
12686: l_period_type,
12687: l_record_type_id,
12688: l_comp_type,
12991: name.name name,
12992: name.object_id object_id,
12993: name.object_type object_type,
12994: name.child_object_usage usage,
12995: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,
12996: name.object_type_mean meaning '||
12997: l_comm_cols ||
12998: l_from || ' ,bim_i_obj_name_mv name,bim_dimv_media chnl '||
12999: l_where ||
13001: and name.activity_id =chnl.id (+)
13002: AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
13003: AND name.language=USERENV(''LANG'')'||
13004: l_groupby||
13005: ' name.name,name.object_id,name.object_type,name.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
13006: l_select_filter ;
13007: END IF;
13008:
13009: END IF;
13125: END IF;
13126: /* building l_pc_select to get values directly assigned to product category passed from the page */
13127: IF l_cat_id is not null THEN
13128: l_pc_col:=' SELECT /*+ORDERED*/
13129: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
13130: p.id viewbyid,
13131: ''Y'' leaf_node_flag,
13132: null meaning ';
13133: l_pc_groupby := ' GROUP BY p.id ';
13148: l_from := l_from ||l_pc_from;
13149: l_where := l_where||l_pc_where;
13150: END IF;
13151: l_col:=' SELECT
13152: decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) name,
13153: null viewbyid,
13154: null meaning ';
13155: l_groupby := ' GROUP BY decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
13156: l_from:=' FROM bic_cust_category_v d '||l_from;
13151: l_col:=' SELECT
13152: decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) name,
13153: null viewbyid,
13154: null meaning ';
13155: l_groupby := ' GROUP BY decode(d.customer_category_name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.customer_category_name) ';
13156: l_from:=' FROM bic_cust_category_v d '||l_from;
13157:
13158: IF l_campaign_id is null then
13159: IF l_admin_status ='Y' THEN
13182: l_from := l_from ||l_pc_from;
13183: l_where := l_where||l_pc_where;
13184: END IF;
13185: l_col:=' SELECT
13186: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
13187: null viewbyid,
13188: null meaning ';
13189: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
13190: l_from:=' FROM as_lookups d '||l_from;
13185: l_col:=' SELECT
13186: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
13187: null viewbyid,
13188: null meaning ';
13189: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
13190: l_from:=' FROM as_lookups d '||l_from;
13191: IF l_campaign_id is null then
13192: IF l_admin_status ='Y' THEN
13193: l_from:=l_from||' ,bim_mkt_ld_src_mv a ';
13218: l_from := l_from ||l_pc_from;
13219: l_where := l_where||l_pc_where;
13220: END IF;
13221: l_col:=' SELECT
13222: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
13223: null viewbyid,
13224: null meaning ';
13225: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
13226: l_from:=' FROM so_lookups d '||l_from;
13221: l_col:=' SELECT
13222: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
13223: null viewbyid,
13224: null meaning ';
13225: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
13226: l_from:=' FROM so_lookups d '||l_from;
13227: IF l_campaign_id is null then
13228: IF l_admin_status ='Y' THEN
13229: l_from:=l_from||' ,bim_mkt_ld_chnl_mv a ';
13254: l_from := l_from ||l_pc_from;
13255: l_where := l_where||l_pc_where;
13256: END IF;
13257: l_col:=' SELECT
13258: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
13259: a.object_country viewbyid,
13260: null meaning ';
13261: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
13262: l_from:=' FROM fnd_territories_tl d '||l_from;
13257: l_col:=' SELECT
13258: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
13259: a.object_country viewbyid,
13260: null meaning ';
13261: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
13262: l_from:=' FROM fnd_territories_tl d '||l_from;
13263: IF l_campaign_id is null then
13264: IF l_admin_status ='Y' THEN
13265: l_from:=l_from||' ,bim_i_obj_mets_mv a ';
13289: l_from := l_from ||l_pc_from;
13290: l_where := l_where||l_pc_where;
13291: END IF;
13292: l_col:=' SELECT
13293: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
13294: null viewbyid,
13295: null meaning ';
13296: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
13297: l_from:=' FROM as_sales_lead_ranks_vl d '||l_from;
13292: l_col:=' SELECT
13293: decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) name,
13294: null viewbyid,
13295: null meaning ';
13296: l_groupby := ' GROUP BY decode(d.meaning,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.meaning) ';
13297: l_from:=' FROM as_sales_lead_ranks_vl d '||l_from;
13298: IF l_campaign_id is null then
13299: IF l_admin_status ='Y' THEN
13300: l_from:=l_from||' ,bim_mkt_ld_qual_mv a ';
13322: l_from := l_from ||l_pc_from;
13323: l_where := l_where||l_pc_where;
13324: END IF;
13325: l_col:=' SELECT
13326: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
13327: null viewbyid,
13328: null meaning ';
13329: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
13330: l_from:=' FROM bim_dimv_media d '||l_from;
13325: l_col:=' SELECT
13326: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
13327: null viewbyid,
13328: null meaning ';
13329: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
13330: l_from:=' FROM bim_dimv_media d '||l_from;
13331: IF l_campaign_id is null then
13332: IF l_admin_status ='Y' THEN
13333: l_from:=l_from||' ,bim_mkt_chnl_mv a ';
13355: l_from := l_from ||l_pc_from;
13356: l_where := l_where||l_pc_where;
13357: END IF;
13358: l_col:=' SELECT
13359: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
13360: null viewbyid,
13361: null meaning ';
13362: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
13363: l_from:=' FROM bis_areas_v d '||l_from;
13358: l_col:=' SELECT
13359: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
13360: null viewbyid,
13361: null meaning ';
13362: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
13363: l_from:=' FROM bis_areas_v d '||l_from;
13364: IF l_campaign_id is null then
13365: IF l_admin_status ='Y' THEN
13366: l_from:=l_from||' ,bim_mkt_regn_mv a ';
13488: l_media VARCHAR2(300);
13489: BEGIN
13490: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
13491: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
13492: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
13493: l_as_of_date,
13494: l_period_type,
13495: l_record_type_id,
13496: l_comp_type,
13517: ELSE
13518: l_access_table := '';
13519: l_access_clause := '';
13520: END IF;*/
13521: l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
13522: IF l_admin_status = 'Y' THEN
13523: if (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
13524: IF l_cat_id is null then
13525: l_sqltext :=
14005: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
14006: FROM
14007: (
14008: SELECT
14009: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14010: a.object_region id,
14011: sum(camp_started-camp_ended) curr_prior_active,
14012: 0 prev_prior_active,
14013: 0 curr_active,
14041: AND mdc.functional_area_id = 11
14042: AND edh.dbi_flag = ''Y''
14043: AND edh.parent_id = :l_cat_id ';
14044: end if;
14045: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ,a.object_region ';
14046: l_sqltext :=l_sqltext ||
14047: ' UNION ALL
14048: SELECT
14049: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14045: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ,a.object_region ';
14046: l_sqltext :=l_sqltext ||
14047: ' UNION ALL
14048: SELECT
14049: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14050: a.object_region id,
14051: 0 curr_prior_active,
14052: sum(camp_started-camp_ended) prev_prior_active,
14053: 0 curr_active,
14081: AND mdc.functional_area_id = 11
14082: AND edh.dbi_flag = ''Y''
14083: AND edh.parent_id = :l_cat_id ';
14084: end if;
14085: l_sqltext := l_sqltext ||'group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
14086: l_sqltext := l_sqltext ||
14087: ' UNION ALL
14088: SELECT
14089: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14085: l_sqltext := l_sqltext ||'group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
14086: l_sqltext := l_sqltext ||
14087: ' UNION ALL
14088: SELECT
14089: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14090: a.object_region id,
14091: 0 curr_prior_active,
14092: 0 prev_prior_active,
14093: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_started-camp_ended) else 0 end) curr_active,
14121: AND mdc.functional_area_id = 11
14122: AND edh.dbi_flag = ''Y''
14123: AND edh.parent_id = :l_cat_id ';
14124: end if;
14125: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region ) group by name,id )';
14126: ELSE
14127: l_sqltext :=
14128: '
14129: SELECT
14163: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
14164: FROM
14165: (
14166: SELECT
14167: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14168: a.object_country id,
14169: sum(camp_started-camp_ended) curr_prior_active,
14170: 0 prev_prior_active,
14171: 0 curr_active,
14199: AND edh.dbi_flag = ''Y''
14200: AND edh.parent_id = :l_cat_id ';
14201: end if;
14202: IF l_country = 'N' THEN
14203: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14204: ELSE
14205: l_sqltext := l_sqltext ||
14206: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14207: END IF;
14202: IF l_country = 'N' THEN
14203: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14204: ELSE
14205: l_sqltext := l_sqltext ||
14206: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14207: END IF;
14208: /*l_sqltext := l_sqltext ||
14209: ' group by object_country*/
14210: l_sqltext :=l_sqltext ||
14209: ' group by object_country*/
14210: l_sqltext :=l_sqltext ||
14211: ' UNION ALL
14212: SELECT
14213: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14214: a.object_country id,
14215: 0 curr_prior_active,
14216: sum(camp_started-camp_ended) prev_prior_active,
14217: 0 curr_active,
14245: AND edh.dbi_flag = ''Y''
14246: AND edh.parent_id = :l_cat_id ';
14247: end if;
14248: IF l_country = 'N' THEN
14249: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14250: ELSE
14251: l_sqltext := l_sqltext ||
14252: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14253: END IF;
14248: IF l_country = 'N' THEN
14249: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14250: ELSE
14251: l_sqltext := l_sqltext ||
14252: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
14253: END IF;
14254: l_sqltext := l_sqltext ||
14255: ' UNION ALL
14256: SELECT
14253: END IF;
14254: l_sqltext := l_sqltext ||
14255: ' UNION ALL
14256: SELECT
14257: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14258: a.object_country id,
14259: 0 curr_prior_active,
14260: 0 prev_prior_active,
14261: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_started-camp_ended) else 0 end) curr_active,
14289: AND edh.dbi_flag = ''Y''
14290: AND edh.parent_id = :l_cat_id ';
14291: end if;
14292: IF l_country = 'N' THEN
14293: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id)';
14294: ELSE
14295: l_sqltext := l_sqltext ||
14296: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id,leaf_node_id)';
14297: END IF;
14292: IF l_country = 'N' THEN
14293: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id)';
14294: ELSE
14295: l_sqltext := l_sqltext ||
14296: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id,leaf_node_id)';
14297: END IF;
14298: END IF;
14299: ELSE -- if admin_flag is not equal to Y
14300: if (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
14914: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
14915: FROM
14916: (
14917: SELECT
14918: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14919: a.object_region id,
14920: sum(camp_started-camp_ended) curr_prior_active,
14921: 0 prev_prior_active,
14922: 0 curr_active,
14965: AND edh.dbi_flag = ''Y''
14966: AND edh.parent_id = :l_cat_id ';
14967: end if;
14968:
14969: l_sqltext := l_sqltext ||'group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
14970: l_sqltext :=l_sqltext ||
14971: ' UNION ALL
14972: SELECT
14973: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14969: l_sqltext := l_sqltext ||'group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
14970: l_sqltext :=l_sqltext ||
14971: ' UNION ALL
14972: SELECT
14973: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
14974: a.object_region id,
14975: 0 curr_prior_active,
14976: sum(camp_started-camp_ended) prev_prior_active,
14977: 0 curr_active,
15019: AND mdc.functional_area_id = 11
15020: AND edh.dbi_flag = ''Y''
15021: AND edh.parent_id = :l_cat_id ';
15022: end if;
15023: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
15024: l_sqltext := l_sqltext ||
15025: ' UNION ALL
15026: SELECT
15027: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15023: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
15024: l_sqltext := l_sqltext ||
15025: ' UNION ALL
15026: SELECT
15027: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15028: a.object_region id,
15029: 0 curr_prior_active,
15030: 0 prev_prior_active,
15031: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_started-camp_ended) else 0 end) curr_active,
15073: AND mdc.functional_area_id = 11
15074: AND edh.dbi_flag = ''Y''
15075: AND edh.parent_id = :l_cat_id ';
15076: end if;
15077: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region ) group by name,id )';
15078: ELSE -- view by country
15079: l_sqltext :=
15080: '
15081: SELECT
15115: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
15116: FROM
15117: (
15118: SELECT
15119: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15120: a.object_country id,
15121: sum(camp_started-camp_ended) curr_prior_active,
15122: 0 prev_prior_active,
15123: 0 curr_active,
15165: AND edh.dbi_flag = ''Y''
15166: AND edh.parent_id = :l_cat_id ';
15167: end if;
15168: IF l_country = 'N' THEN
15169: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15170: ELSE
15171: l_sqltext := l_sqltext ||
15172: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15173: END IF;
15168: IF l_country = 'N' THEN
15169: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15170: ELSE
15171: l_sqltext := l_sqltext ||
15172: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15173: END IF;
15174: /*l_sqltext := l_sqltext ||
15175: --' AND a.object_country = :l_country
15176: 'group by object_country*/
15176: 'group by object_country*/
15177: l_sqltext :=l_sqltext ||
15178: ' UNION ALL
15179: SELECT
15180: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15181: a.object_country id,
15182: 0 curr_prior_active,
15183: sum(camp_started-camp_ended) prev_prior_active,
15184: 0 curr_active,
15226: AND edh.dbi_flag = ''Y''
15227: AND edh.parent_id = :l_cat_id ';
15228: end if;
15229: IF l_country = 'N' THEN
15230: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15231: ELSE
15232: l_sqltext := l_sqltext ||
15233: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15234: END IF;
15229: IF l_country = 'N' THEN
15230: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15231: ELSE
15232: l_sqltext := l_sqltext ||
15233: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
15234: END IF;
15235: l_sqltext := l_sqltext ||
15236: ' UNION ALL
15237: SELECT
15234: END IF;
15235: l_sqltext := l_sqltext ||
15236: ' UNION ALL
15237: SELECT
15238: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15239: a.object_country id,
15240: 0 curr_prior_active,
15241: 0 prev_prior_active,
15242: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_started-camp_ended) else 0 end) curr_active,
15285: AND edh.dbi_flag = ''Y''
15286: AND edh.parent_id = :l_cat_id ';
15287: end if;
15288: IF l_country = 'N' THEN
15289: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
15290: ELSE
15291: l_sqltext := l_sqltext ||
15292: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
15293: END IF; -- product category All loop
15288: IF l_country = 'N' THEN
15289: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
15290: ELSE
15291: l_sqltext := l_sqltext ||
15292: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
15293: END IF; -- product category All loop
15294: END IF; --end view by product category
15295:
15296: END IF; -- end if admin_flag = Y loop
15389: l_media VARCHAR2(300);
15390: BEGIN
15391: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
15392: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
15393: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
15394: l_as_of_date,
15395: l_period_type,
15396: l_record_type_id,
15397: l_comp_type,
15404: l_area,
15405: l_media,
15406: l_report_name
15407: );
15408: l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
15409:
15410: --l_curr_aod_str := 'to_date('||to_char(l_as_of_date,'J')||',''J'')';
15411: /* IF l_country IS NULL THEN
15412: l_country := 'N';
15897: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
15898: FROM
15899: (
15900: SELECT
15901: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15902: a.object_region id,
15903: sum(even_started-even_ended) curr_prior_active,
15904: 0 prev_prior_active,
15905: 0 curr_active,
15933: AND mdc.functional_area_id = 11
15934: AND edh.dbi_flag = ''Y''
15935: AND edh.parent_id = :l_cat_id ';
15936: end if;
15937: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
15938:
15939: l_sqltext :=l_sqltext ||
15940: ' UNION ALL
15941: SELECT
15938:
15939: l_sqltext :=l_sqltext ||
15940: ' UNION ALL
15941: SELECT
15942: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15943: a.object_region id,
15944: 0 curr_prior_active,
15945: sum(even_started-even_ended) prev_prior_active,
15946: 0 curr_active,
15974: AND mdc.functional_area_id = 11
15975: AND edh.dbi_flag = ''Y''
15976: AND edh.parent_id = :l_cat_id ';
15977: end if;
15978: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
15979: l_sqltext := l_sqltext ||
15980: ' UNION ALL
15981: SELECT
15982: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15978: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
15979: l_sqltext := l_sqltext ||
15980: ' UNION ALL
15981: SELECT
15982: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
15983: a.object_region id,
15984: 0 curr_prior_active,
15985: 0 prev_prior_active,
15986: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (even_started-even_ended) else 0 end) curr_active,
16015: AND edh.dbi_flag = ''Y''
16016: AND edh.parent_id = :l_cat_id ';
16017: end if;
16018:
16019: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region ) group by name,id )';
16020:
16021: ELSE --view by country
16022: l_sqltext :=
16023: '
16058: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
16059: FROM
16060: (
16061: SELECT
16062: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16063: a.object_country id,
16064: sum(even_started-even_ended) curr_prior_active,
16065: 0 prev_prior_active,
16066: 0 curr_active,
16094: AND edh.dbi_flag = ''Y''
16095: AND edh.parent_id = :l_cat_id ';
16096: end if;
16097: IF l_country = 'N' THEN
16098: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16099: ELSE
16100: l_sqltext := l_sqltext ||
16101: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16102: END IF;
16097: IF l_country = 'N' THEN
16098: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16099: ELSE
16100: l_sqltext := l_sqltext ||
16101: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16102: END IF;
16103: /*l_sqltext := l_sqltext ||
16104: 'group by object_country*/
16105: l_sqltext :=l_sqltext ||
16104: 'group by object_country*/
16105: l_sqltext :=l_sqltext ||
16106: ' UNION ALL
16107: SELECT
16108: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16109: a.object_country id,
16110: 0 curr_prior_active,
16111: sum(even_started-even_ended) prev_prior_active,
16112: 0 curr_active,
16140: AND edh.dbi_flag = ''Y''
16141: AND edh.parent_id = :l_cat_id ';
16142: end if;
16143: IF l_country = 'N' THEN
16144: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16145: ELSE
16146: l_sqltext := l_sqltext ||
16147: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16148: END IF;
16143: IF l_country = 'N' THEN
16144: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16145: ELSE
16146: l_sqltext := l_sqltext ||
16147: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
16148: END IF;
16149: l_sqltext := l_sqltext ||
16150: ' UNION ALL
16151: SELECT
16148: END IF;
16149: l_sqltext := l_sqltext ||
16150: ' UNION ALL
16151: SELECT
16152: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16153: a.object_country id,
16154: 0 curr_prior_active,
16155: 0 prev_prior_active,
16156: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (even_started-even_ended) else 0 end) curr_active,
16184: AND edh.dbi_flag = ''Y''
16185: AND edh.parent_id = :l_cat_id ';
16186: end if;
16187: IF l_country = 'N' THEN
16188: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
16189: ELSE
16190: l_sqltext := l_sqltext ||
16191: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
16192: END IF; -- end product category numm loop
16187: IF l_country = 'N' THEN
16188: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
16189: ELSE
16190: l_sqltext := l_sqltext ||
16191: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
16192: END IF; -- end product category numm loop
16193: END IF; --end view by product category
16194: ELSE -- if admin_flag is not equal to Y
16195: if (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
16801: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
16802: FROM
16803: (
16804: SELECT
16805: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16806: a.object_region id,
16807: sum(even_started-even_ended) curr_prior_active,
16808: 0 prev_prior_active,
16809: 0 curr_active,
16852: AND mdc.functional_area_id = 11
16853: AND edh.dbi_flag = ''Y''
16854: AND edh.parent_id = :l_cat_id ';
16855: end if;
16856: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
16857: l_sqltext :=l_sqltext ||
16858: ' UNION ALL
16859: SELECT
16860: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16856: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
16857: l_sqltext :=l_sqltext ||
16858: ' UNION ALL
16859: SELECT
16860: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16861: a.object_region id,
16862: 0 curr_prior_active,
16863: sum(even_started-even_ended) prev_prior_active,
16864: 0 curr_active,
16907: AND mdc.functional_area_id = 11
16908: AND edh.dbi_flag = ''Y''
16909: AND edh.parent_id = :l_cat_id ';
16910: end if;
16911: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
16912: l_sqltext := l_sqltext ||
16913: ' UNION ALL
16914: SELECT
16915: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16911: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
16912: l_sqltext := l_sqltext ||
16913: ' UNION ALL
16914: SELECT
16915: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
16916: a.object_region id,
16917: 0 curr_prior_active,
16918: 0 prev_prior_active,
16919: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (even_started-even_ended) else 0 end) curr_active,
16962: AND mdc.functional_area_id = 11
16963: AND edh.dbi_flag = ''Y''
16964: AND edh.parent_id = :l_cat_id ';
16965: end if;
16966: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region ) group by name,id )';
16967:
16968: ELSE --view by country
16969: l_sqltext :=
16970: '
17005: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
17006: FROM
17007: (
17008: SELECT
17009: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17010: a.object_country id,
17011: sum(even_started-even_ended) curr_prior_active,
17012: 0 prev_prior_active,
17013: 0 curr_active,
17056: AND edh.dbi_flag = ''Y''
17057: AND edh.parent_id = :l_cat_id ';
17058: end if;
17059: IF l_country = 'N' THEN
17060: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17061: ELSE
17062: l_sqltext := l_sqltext ||
17063: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17064: END IF;
17059: IF l_country = 'N' THEN
17060: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17061: ELSE
17062: l_sqltext := l_sqltext ||
17063: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17064: END IF;
17065: /*l_sqltext := l_sqltext ||
17066: --' AND a.object_country = :l_country
17067: 'group by object_country*/
17067: 'group by object_country*/
17068: l_sqltext :=l_sqltext ||
17069: ' UNION ALL
17070: SELECT
17071: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17072: a.object_country id,
17073: 0 curr_prior_active,
17074: sum(even_started-even_ended) prev_prior_active,
17075: 0 curr_active,
17118: AND edh.dbi_flag = ''Y''
17119: AND edh.parent_id = :l_cat_id ';
17120: end if;
17121: IF l_country = 'N' THEN
17122: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17123: ELSE
17124: l_sqltext := l_sqltext ||
17125: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17126: END IF;
17121: IF l_country = 'N' THEN
17122: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17123: ELSE
17124: l_sqltext := l_sqltext ||
17125: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17126: END IF;
17127: l_sqltext := l_sqltext ||
17128: ' UNION ALL
17129: SELECT
17126: END IF;
17127: l_sqltext := l_sqltext ||
17128: ' UNION ALL
17129: SELECT
17130: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17131: a.object_country id,
17132: 0 curr_prior_active,
17133: 0 prev_prior_active,
17134: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (even_started-even_ended) else 0 end) curr_active,
17177: AND edh.dbi_flag = ''Y''
17178: AND edh.parent_id = :l_cat_id ';
17179: end if;
17180: IF l_country = 'N' THEN
17181: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17182: ELSE
17183: l_sqltext := l_sqltext ||
17184: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17185: END IF; -- product category All loop
17180: IF l_country = 'N' THEN
17181: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17182: ELSE
17183: l_sqltext := l_sqltext ||
17184: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17185: END IF; -- product category All loop
17186: END IF; --end view by product category
17187: END IF; -- end if admin_flag = Y loop
17188:
17287: BEGIN
17288: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
17289: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
17290:
17291: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
17292: l_as_of_date,
17293: l_period_type,
17294: l_record_type_id,
17295: l_comp_type,
17310:
17311: END IF;
17312:
17313: l_admin_status := GET_ADMIN_STATUS;
17314: l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
17315:
17316: IF (l_curr = '''FII_GLOBAL1''') THEN
17317:
17318: l_curr_suffix := '';
17661:
17662: BEGIN
17663: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
17664: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
17665: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
17666: l_as_of_date,
17667: l_period_type,
17668: l_record_type_id,
17669: l_comp_type,
17677: l_media,
17678: l_report_name
17679: );
17680:
17681: l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
17682: l_eve := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('EVE');
17683:
17684: IF l_view_by ='GEOGRAPHY+COUNTRY' then
17685: l_view_name :=L_viewby_c; --'Country'
17678: l_report_name
17679: );
17680:
17681: l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
17682: l_eve := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('EVE');
17683:
17684: IF l_view_by ='GEOGRAPHY+COUNTRY' then
17685: l_view_name :=L_viewby_c; --'Country'
17686: ELSIF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' then
17749: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
17750: FROM
17751: (
17752: SELECT
17753: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17754: a.object_country id,
17755: sum(camp_sch_started-camp_sch_ended) curr_prior_active,
17756: 0 prev_prior_active,
17757: 0 curr_active,
17785: AND edh.dbi_flag = ''Y''
17786: AND edh.parent_id = :l_cat_id';
17787: end if;
17788: IF l_country = 'N' THEN
17789: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17790: ELSE
17791: l_sqltext := l_sqltext ||
17792: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17793: END IF;
17788: IF l_country = 'N' THEN
17789: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17790: ELSE
17791: l_sqltext := l_sqltext ||
17792: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17793: END IF;
17794: /*l_sqltext := l_sqltext ||
17795: --' AND a.object_country = :l_country
17796: 'group by object_country*/
17796: 'group by object_country*/
17797: l_sqltext :=l_sqltext ||
17798: ' UNION ALL
17799: SELECT
17800: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17801: a.object_country id,
17802: 0 curr_prior_active,
17803: sum(camp_sch_started-camp_sch_ended) prev_prior_active,
17804: 0 curr_active,
17832: AND edh.dbi_flag = ''Y''
17833: AND edh.parent_id = :l_cat_id ';
17834: end if;
17835: IF l_country = 'N' THEN
17836: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17837: ELSE
17838: l_sqltext := l_sqltext ||
17839: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17840: END IF;
17835: IF l_country = 'N' THEN
17836: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17837: ELSE
17838: l_sqltext := l_sqltext ||
17839: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
17840: END IF;
17841: l_sqltext := l_sqltext ||
17842: ' UNION ALL
17843: SELECT
17840: END IF;
17841: l_sqltext := l_sqltext ||
17842: ' UNION ALL
17843: SELECT
17844: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17845: a.object_country id,
17846: 0 curr_prior_active,
17847: 0 prev_prior_active,
17848: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_sch_started-camp_sch_ended) else 0 end) curr_active,
17876: AND edh.dbi_flag = ''Y''
17877: AND edh.parent_id = :l_cat_id ';
17878: end if;
17879: IF l_country = 'N' THEN
17880: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17881: ELSE
17882: l_sqltext := l_sqltext ||
17883: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17884: END IF;
17879: IF l_country = 'N' THEN
17880: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17881: ELSE
17882: l_sqltext := l_sqltext ||
17883: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
17884: END IF;
17885: ELSIF (l_view_by = 'GEOGRAPHY+AREA') then
17886: l_sqltext :=
17887: '
17922: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
17923: FROM
17924: (
17925: SELECT
17926: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17927: a.object_region id,
17928: sum(camp_sch_started-camp_sch_ended) curr_prior_active,
17929: 0 prev_prior_active,
17930: 0 curr_active,
17958: AND mdc.functional_area_id = 11
17959: AND edh.dbi_flag = ''Y''
17960: AND edh.parent_id = :l_cat_id';
17961: end if;
17962: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
17963: l_sqltext :=l_sqltext ||
17964: ' UNION ALL
17965: SELECT
17966: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17962: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
17963: l_sqltext :=l_sqltext ||
17964: ' UNION ALL
17965: SELECT
17966: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
17967: a.object_region id,
17968: 0 curr_prior_active,
17969: sum(camp_sch_started-camp_sch_ended) prev_prior_active,
17970: 0 curr_active,
17998: AND mdc.functional_area_id = 11
17999: AND edh.dbi_flag = ''Y''
18000: AND edh.parent_id = :l_cat_id ';
18001: end if;
18002: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
18003: l_sqltext := l_sqltext ||
18004: ' UNION ALL
18005: SELECT
18006: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18002: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
18003: l_sqltext := l_sqltext ||
18004: ' UNION ALL
18005: SELECT
18006: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18007: a.object_region id,
18008: 0 curr_prior_active,
18009: 0 prev_prior_active,
18010: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_sch_started-camp_sch_ended) else 0 end) curr_active,
18038: AND mdc.functional_area_id = 11
18039: AND edh.dbi_flag = ''Y''
18040: AND edh.parent_id = :l_cat_id ';
18041: end if;
18042: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region) group by name,id )';
18043: -- View By Marketing Channel
18044: ELSIF (l_view_by = 'MEDIA+MEDIA') then
18045: l_sqltext :=
18046: '
18680: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
18681: FROM
18682: (
18683: SELECT
18684: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18685: a.object_country id,
18686: sum(camp_sch_started-camp_sch_ended) curr_prior_active,
18687: 0 prev_prior_active,
18688: 0 curr_active,
18729: AND edh.dbi_flag = ''Y''
18730: AND edh.parent_id = :l_cat_id ';
18731: end if;
18732: IF l_country = 'N' THEN
18733: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18734: ELSE
18735: l_sqltext := l_sqltext ||
18736: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18737: END IF;
18732: IF l_country = 'N' THEN
18733: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18734: ELSE
18735: l_sqltext := l_sqltext ||
18736: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18737: END IF;
18738: l_sqltext :=l_sqltext ||
18739: ' UNION ALL
18740: SELECT
18737: END IF;
18738: l_sqltext :=l_sqltext ||
18739: ' UNION ALL
18740: SELECT
18741: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18742: a.object_country id,
18743: 0 curr_prior_active,
18744: sum(camp_sch_started-camp_sch_ended) prev_prior_active,
18745: 0 curr_active,
18786: AND edh.dbi_flag = ''Y''
18787: AND edh.parent_id = :l_cat_id ';
18788: end if;
18789: IF l_country = 'N' THEN
18790: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18791: ELSE
18792: l_sqltext := l_sqltext ||
18793: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18794: END IF;
18789: IF l_country = 'N' THEN
18790: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18791: ELSE
18792: l_sqltext := l_sqltext ||
18793: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country';
18794: END IF;
18795: l_sqltext := l_sqltext ||
18796: ' UNION ALL
18797: SELECT
18794: END IF;
18795: l_sqltext := l_sqltext ||
18796: ' UNION ALL
18797: SELECT
18798: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18799: a.object_country id,
18800: 0 curr_prior_active,
18801: 0 prev_prior_active,
18802: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_sch_started-camp_sch_ended) else 0 end) curr_active,
18843: AND edh.dbi_flag = ''Y''
18844: AND edh.parent_id = :l_cat_id ';
18845: end if;
18846: IF l_country = 'N' THEN
18847: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
18848: ELSE
18849: l_sqltext := l_sqltext ||
18850: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
18851: END IF;
18846: IF l_country = 'N' THEN
18847: l_sqltext := l_sqltext ||' AND a.object_country <> ''N'' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
18848: ELSE
18849: l_sqltext := l_sqltext ||
18850: ' AND a.object_country = :l_country group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_country ) group by name,id )';
18851: END IF;
18852: ELSIF (l_view_by = 'GEOGRAPHY+AREA') then
18853: l_sqltext :=
18854: '
18889: nvl(sum(prev_prior_active),0)+sum(prev_started)-sum(prev_act_ended) BIM_ATTRIBUTE8
18890: FROM
18891: (
18892: SELECT
18893: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18894: a.object_region id,
18895: sum(camp_sch_started-camp_sch_ended) curr_prior_active,
18896: 0 prev_prior_active,
18897: 0 curr_active,
18938: AND mdc.functional_area_id = 11
18939: AND edh.dbi_flag = ''Y''
18940: AND edh.parent_id = :l_cat_id ';
18941: end if;
18942: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
18943: l_sqltext :=l_sqltext ||
18944: ' UNION ALL
18945: SELECT
18946: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18942: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
18943: l_sqltext :=l_sqltext ||
18944: ' UNION ALL
18945: SELECT
18946: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18947: a.object_region id,
18948: 0 curr_prior_active,
18949: sum(camp_sch_started-camp_sch_ended) prev_prior_active,
18950: 0 curr_active,
18991: AND mdc.functional_area_id = 11
18992: AND edh.dbi_flag = ''Y''
18993: AND edh.parent_id = :l_cat_id ';
18994: end if;
18995: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
18996: l_sqltext := l_sqltext ||
18997: ' UNION ALL
18998: SELECT
18999: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
18995: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region';
18996: l_sqltext := l_sqltext ||
18997: ' UNION ALL
18998: SELECT
18999: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
19000: a.object_region id,
19001: 0 curr_prior_active,
19002: 0 prev_prior_active,
19003: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_sch_started-camp_sch_ended) else 0 end) curr_active,
19044: AND mdc.functional_area_id = 11
19045: AND edh.dbi_flag = ''Y''
19046: AND edh.parent_id = :l_cat_id ';
19047: end if;
19048: l_sqltext := l_sqltext ||' group by decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value),a.object_region ) group by name,id )';
19049: ELSIF (l_view_by = 'MEDIA+MEDIA') then
19050: l_sqltext :=
19051: '
19052: SELECT
19555: l_sqltext :=l_sqltext ||
19556: ' UNION ALL
19557: SELECT
19558: p.id ID,
19559: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
19560: ''Y'' leaf_node_flag ,
19561: sum(camp_sch_started-camp_sch_ended) curr_prior_active,
19562: 0 prev_prior_active,
19563: 0 curr_active,
19660: l_sqltext := l_sqltext ||' group by p.id , p.value , p.leaf_node_flag
19661: UNION ALL
19662: SELECT
19663: p.id ID,
19664: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
19665: ''Y'' leaf_node_flag ,
19666: 0 curr_prior_active,
19667: sum(camp_sch_started-camp_sch_ended) prev_prior_active,
19668: 0 curr_active,
19767: l_sqltext := l_sqltext ||' group by p.value,p.id , p.leaf_node_flag
19768: UNION ALL
19769: SELECT
19770: p.id ID,
19771: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
19772: ''Y'' leaf_node_flag ,
19773: 0 curr_prior_active,
19774: 0 prev_prior_active,
19775: SUM(case when cal.report_date=&BIS_CURRENT_ASOF_DATE then (camp_sch_started-camp_sch_ended) else 0 end) curr_active,
19938:
19939: BEGIN
19940: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
19941: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
19942: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
19943: l_as_of_date,
19944: l_period_type,
19945: l_record_type_id,
19946: l_comp_type,
19954: l_media,
19955: l_report_name
19956: );
19957:
19958: bim_pmv_dbi_utl_pkg.get_viewby_id(p_page_parameter_tbl,l_viewby_id);
19959:
19960:
19961: IF (l_curr = '''FII_GLOBAL1''')
19962: THEN l_curr_suffix := '';
19969: l_country := 'N';
19970: END IF;
19971: l_admin_status := GET_ADMIN_STATUS;
19972:
19973: l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
19974:
19975: --l_url_str :='pFunctionName=BIM_I_CAMP_START_DRILL&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID&PAGE.OBJ.ID_NAME0=objId&PAGE.OBJ.ID0=18917&PAGE.OBJ.ID_NAME1=customSetupId&PAGE.OBJ.ID1=1&PAGE.OBJ.objType=CAMP&PAGE.OBJ.objAttribute=DETL';
19976: --l_url_str :='pFunctionName=AMS_WB_CSCH_RPRT&addBreadCrumb=Y&objType=CSCH&objId=';
19977: l_url_str :='pFunctionName=BIM_I_CSCH_START_DRILL&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID&PAGE.OBJ.ID_NAME1=customSetupId&PAGE.OBJ.ID1=1&PAGE.OBJ.objType=CSCH&PAGE.OBJ.objAttribute=DETL&PAGE.OBJ.ID_NAME0=objId&PAGE.OBJ.ID0=';
20220:
20221: BEGIN
20222: x_custom_output := bis_query_attributes_tbl();
20223: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
20224: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
20225: l_as_of_date,
20226: l_period_type,
20227: l_record_type_id,
20228: l_comp_type,
20555:
20556: l_sqltext := l_select_cal ||
20557: /******** inner select start from here */
20558: ' SELECT a.source_code_id VIEWBYID, name.name name, NAME.object_id object_id,
20559: NAME.object_type object_type, name.object_type_mean meaning ,decode(name.activity_id,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',name.activity_id) channel ,
20560: name.child_object_usage usage ' ||
20561: l_select1 || l_from ||' ,bim_i_obj_name_mv name , bim_dimv_media chnl ' ||
20562: l_where || l_top_cond ||
20563: ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
20565: AND name.activity_id =chnl.id (+)
20566: AND immediate_parent_id = '||l_campaign_id||'
20567: AND cal.report_date in ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
20568: AND NAME.language=USERENV(''LANG'')' || l_groupby ||
20569: ' name.name,name.object_id,name.object_type , decode(name.activity_id,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',name.activity_id) , name.child_object_usage ' ||
20570: ' UNION ALL
20571: SELECT a.source_code_id VIEWBYID, name.name name, name.object_id object_id, name.object_type object_type,
20572: name.object_type_mean meaning ,decode(name.activity_id,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',name.activity_id) channel , name.child_object_usage usage ' || l_select2 || l_from ||
20573: ' ,bim_i_obj_name_mv name , bim_dimv_media chnl '
20568: AND NAME.language=USERENV(''LANG'')' || l_groupby ||
20569: ' name.name,name.object_id,name.object_type , decode(name.activity_id,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',name.activity_id) , name.child_object_usage ' ||
20570: ' UNION ALL
20571: SELECT a.source_code_id VIEWBYID, name.name name, name.object_id object_id, name.object_type object_type,
20572: name.object_type_mean meaning ,decode(name.activity_id,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',name.activity_id) channel , name.child_object_usage usage ' || l_select2 || l_from ||
20573: ' ,bim_i_obj_name_mv name , bim_dimv_media chnl '
20574: || l_where || l_top_cond ||
20575: ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id
20576: AND a.source_code_id = name.source_code_id
20577: AND name.activity_id =chnl.id (+)
20578: AND immediate_parent_id = '||l_campaign_id||'
20579: AND cal.report_date = trunc(sysdate)
20580: AND NAME.language=USERENV(''LANG'')' || l_groupby ||
20581: ' name.name,name.object_id,name.object_type , decode(name.activity_id,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',name.activity_id) , name.child_object_usage '
20582: ||l_select_filter;
20583: ELSE
20584: l_sqltext := l_select_cal ||
20585: /******** inner select start from here */
20701: END IF;
20702: /* building l_pc_select to get values directly assigned to product category passed from the page */
20703: IF l_cat_id is not null THEN
20704: l_pc_col := ' SELECT /*+ORDERED*/
20705: bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20706: 'DASS' || '''' || ')' || ' name,
20707: p.id viewbyid,
20708: ''Y'' leaf_node_flag,
20709: null meaning ';
20726: ELSE
20727: l_from := l_from || l_pc_from;
20728: l_where := l_where || l_pc_where;
20729: END IF;
20730: l_col := ' SELECT decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20731: 'UNA' || '''' || ')' || ',d.TERRITORY_SHORT_NAME) name, a.object_country viewbyid, null meaning ';
20732: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20733: 'UNA' || '''' || ')' ||',d.TERRITORY_SHORT_NAME),a.object_country ';
20734: l_from := ' FROM fnd_territories_tl d ' || l_from;
20728: l_where := l_where || l_pc_where;
20729: END IF;
20730: l_col := ' SELECT decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20731: 'UNA' || '''' || ')' || ',d.TERRITORY_SHORT_NAME) name, a.object_country viewbyid, null meaning ';
20732: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20733: 'UNA' || '''' || ')' ||',d.TERRITORY_SHORT_NAME),a.object_country ';
20734: l_from := ' FROM fnd_territories_tl d ' || l_from;
20735: IF l_campaign_id is null then
20736: IF l_admin_status = 'Y' THEN
20758: l_from := l_from || l_pc_from;
20759: l_where := l_where || l_pc_where;
20760: END IF;
20761: l_col := ' SELECT
20762: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20763: 'UNA' || '''' || ')' || ',d.value) name,
20764: null viewbyid,
20765: null meaning ';
20766: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20762: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20763: 'UNA' || '''' || ')' || ',d.value) name,
20764: null viewbyid,
20765: null meaning ';
20766: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20767: 'UNA' || '''' || ')' || ',d.value) ';
20768: l_from := ' FROM bim_dimv_media d ' || l_from;
20769: IF l_campaign_id is null then
20770: IF l_admin_status = 'Y' THEN
20795: ELSE
20796: l_from := l_from || l_pc_from;
20797: l_where := l_where || l_pc_where;
20798: END IF;
20799: l_col := ' SELECT decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20800: 'UNA' || '''' || ')' || ',d.value) name, null viewbyid, null meaning ';
20801: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20802: 'UNA' || '''' || ')' || ',d.value) ';
20803: l_from := ' FROM bis_areas_v d ' || l_from;
20797: l_where := l_where || l_pc_where;
20798: END IF;
20799: l_col := ' SELECT decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20800: 'UNA' || '''' || ')' || ',d.value) name, null viewbyid, null meaning ';
20801: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value(' || '''' ||
20802: 'UNA' || '''' || ')' || ',d.value) ';
20803: l_from := ' FROM bis_areas_v d ' || l_from;
20804: IF l_campaign_id is null then
20805: IF l_admin_status = 'Y' THEN
20974: l_media VARCHAR2(300);
20975: BEGIN
20976: x_custom_output := bis_query_attributes_tbl ();
20977: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
20978: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
20979: l_as_of_date,
20980: l_period_type,
20981: l_record_type_id,
20982: l_comp_type,
21128: l_camp_sel_col :=l_camp_sel_col|| ',usage,channel,list_header_id';
21129: l_camp_groupby_col :=l_camp_groupby_col||',usage,channel,list_header_id';
21130: end if;
21131: ELSE
21132: -- l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
21133: l_url_link:=' ,null ';
21134: l_view_disp:='viewby';
21135: END IF;
21136: /* l_select_cal is common part of select statement for all view by to calculate grand totals and change */
21383: a.source_code_id VIEWBYID, name.object_id object_id, name.object_type object_type,
21384: name.name name,
21385: name.child_object_usage usage,
21386: aal.list_header_id list_header_id,
21387: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,
21388: name.object_type_mean meaning '||
21389: l_select1 ||
21390: l_from || ' ,bim_dimv_media chnl,ams_act_lists aal '||
21391: l_where ||
21396: AND aal.list_used_by(+) = ''CSCH''
21397: AND cal.report_date in ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
21398: '||
21399: l_groupby||
21400: ' ,name.child_object_usage,aal.list_header_id,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
21401: ' UNION ALL /* select to get camapign schedules for previous period values */
21402: SELECT
21403: a.source_code_id VIEWBYID, name.object_id object_id, name.object_type object_type,
21404: name.name name,
21403: a.source_code_id VIEWBYID, name.object_id object_id, name.object_type object_type,
21404: name.name name,
21405: name.child_object_usage usage,
21406: aal.list_header_id list_header_id,
21407: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,
21408: name.object_type_mean meaning '||
21409: l_select2 ||
21410: l_from || ' ,bim_dimv_media chnl,ams_act_lists aal '||
21411: l_where ||
21415: AND aal.list_act_type(+) = ''TARGET''
21416: AND aal.list_used_by(+) = ''CSCH''
21417: AND cal.report_date = trunc(sysdate) '||
21418: l_groupby||
21419: ' ,name.child_object_usage,aal.list_header_id,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
21420: l_select_filter ;
21421: END IF;
21422: END IF;
21423: /***** END CAMPAIGN HIRERACHY VIEW HANDLING ******************/
21537: END IF;
21538: /* building l_pc_select to get values directly assigned to product category passed from the page */
21539: IF l_cat_id is not null THEN
21540: l_pc_col:=' SELECT /*+ORDERED*/
21541: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
21542: p.id viewbyid,
21543: ''Y'' leaf_node_flag,
21544: null meaning ';
21545: l_pc_groupby := ' GROUP BY p.id ';
21581: l_from := l_from ||l_pc_from;
21582: l_where := l_where||l_pc_where;
21583: END IF;
21584: l_col:=' SELECT
21585: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
21586: a.object_country viewbyid,
21587: null meaning ';
21588: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
21589: l_from:=' FROM fnd_territories_tl d '||l_from;
21584: l_col:=' SELECT
21585: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
21586: a.object_country viewbyid,
21587: null meaning ';
21588: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
21589: l_from:=' FROM fnd_territories_tl d '||l_from;
21590: IF l_campaign_id is null then
21591: IF l_admin_status ='Y' THEN
21592: l_from:=l_from||' ,BIM_I_obj_METS_MV a ,bim_i_obj_name_mv name';
21626: l_from := l_from ||l_pc_from;
21627: l_where := l_where||l_pc_where;
21628: END IF;
21629: l_col:=' SELECT
21630: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
21631: null viewbyid,
21632: null meaning ';
21633: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
21634: l_from:=' FROM bim_dimv_media d '||l_from;
21629: l_col:=' SELECT
21630: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
21631: null viewbyid,
21632: null meaning ';
21633: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
21634: l_from:=' FROM bim_dimv_media d '||l_from;
21635: IF l_campaign_id is null then
21636: IF l_admin_status ='Y' THEN
21637: /*l_from:=l_from||' ,bim_mkt_chnl_mv a';*/
21666: l_from := l_from ||l_pc_from;
21667: l_where := l_where||l_pc_where;
21668: END IF;
21669: l_col:=' SELECT
21670: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
21671: null viewbyid,
21672: null meaning ';
21673: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
21674: l_from:=' FROM bis_areas_v d '||l_from;
21669: l_col:=' SELECT
21670: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
21671: null viewbyid,
21672: null meaning ';
21673: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
21674: l_from:=' FROM bis_areas_v d '||l_from;
21675: IF l_campaign_id is null then
21676: IF l_admin_status ='Y' THEN
21677: /*l_from:=l_from||' ,bim_mkt_chnl_mv a ';*/
21857: l_media VARCHAR2(300);
21858: BEGIN
21859: x_custom_output := bis_query_attributes_tbl ();
21860: l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
21861: bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
21862: l_as_of_date,
21863: l_period_type,
21864: l_record_type_id,
21865: l_comp_type,
21936: l_camp_sel_col :=l_camp_sel_col|| ',usage,channel';
21937: l_camp_groupby_col :=l_camp_groupby_col||',usage,channel';
21938: end if;
21939: ELSE
21940: -- l_una := BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('UNA');
21941: l_url_link:=' ,null ';
21942: l_view_disp:='viewby';
21943: END IF;
21944:
22177: name.name name,
22178: name.object_id object_id,
22179: name.object_type object_type,
22180: name.child_object_usage usage,
22181: decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value) channel,
22182: name.object_type_mean meaning '||
22183: l_comm_cols ||
22184: l_from || ' , bim_dimv_media chnl '||
22185: l_where ||
22186: ' AND name.activity_id =chnl.id (+)
22187: AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
22188: AND name.language=USERENV(''LANG'')'||
22189: l_groupby||
22190: ' name.name,name.object_id,name.object_type,name.child_object_usage,decode(chnl.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',chnl.value)'||
22191: l_select_filter ;
22192: END IF;
22193:
22194: END IF;
22311: END IF;
22312: /* building l_pc_select to get values directly assigned to product category passed from the page */
22313: IF l_cat_id is not null THEN
22314: l_pc_col:=' SELECT /*+ORDERED*/
22315: bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||' name,
22316: p.id viewbyid,
22317: ''Y'' leaf_node_flag,
22318: null meaning ';
22319: l_pc_groupby := ' GROUP BY p.id ';
22334: l_from := l_from ||l_pc_from;
22335: l_where := l_where||l_pc_where;
22336: END IF;
22337: l_col:=' SELECT
22338: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
22339: a.object_country viewbyid,
22340: null meaning ';
22341: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
22342: l_from:=' FROM fnd_territories_tl d '||l_from;
22337: l_col:=' SELECT
22338: decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME) name,
22339: a.object_country viewbyid,
22340: null meaning ';
22341: l_groupby := ' GROUP BY decode(d.TERRITORY_SHORT_NAME,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.TERRITORY_SHORT_NAME),a.object_country ';
22342: l_from:=' FROM fnd_territories_tl d '||l_from;
22343: IF l_campaign_id is null then
22344: IF l_admin_status ='Y' THEN
22345: l_from:=l_from||' ,bim_i_obj_mets_mv a ';
22369: l_from := l_from ||l_pc_from;
22370: l_where := l_where||l_pc_where;
22371: END IF;
22372: l_col:=' SELECT
22373: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
22374: null viewbyid,
22375: null meaning ';
22376: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
22377: l_from:=' FROM bim_dimv_media d '||l_from;
22372: l_col:=' SELECT
22373: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
22374: null viewbyid,
22375: null meaning ';
22376: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
22377: l_from:=' FROM bim_dimv_media d '||l_from;
22378: IF l_campaign_id is null then
22379: IF l_admin_status ='Y' THEN
22380: l_from:=l_from||' ,bim_mkt_chnl_mv a ';
22402: l_from := l_from ||l_pc_from;
22403: l_where := l_where||l_pc_where;
22404: END IF;
22405: l_col:=' SELECT
22406: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
22407: null viewbyid,
22408: null meaning ';
22409: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
22410: l_from:=' FROM bis_areas_v d '||l_from;
22405: l_col:=' SELECT
22406: decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
22407: null viewbyid,
22408: null meaning ';
22409: l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
22410: l_from:=' FROM bis_areas_v d '||l_from;
22411: IF l_campaign_id is null then
22412: IF l_admin_status ='Y' THEN
22413: l_from:=l_from||' ,bim_mkt_regn_mv a ';