eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIM_CMP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18


l_sql_insert_stm            varchar2(10000);

Line: 31


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';

Line: 33


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 36


l_sql_insert_stm := l_sql_insert_stm
                 || ' Sum(bscc.num_of_leads),';

Line: 39


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(BSCC.num_of_leads),0,0,( sum (BSCC.actual_cost) / sum (BSCC.num_of_leads) )),';

Line: 94


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMC.Channel_name ';

Line: 102


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,bdm.media_name ';

Line: 110


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.Campaign_name,BSCC.period_name ';

Line: 130


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
Using
 p_start_date
,p_end_date
,p_campaign_id
,p_campaign_status_id
,p_campaign_type_id
,p_media_id
,p_channel_id
,P_PERIOD_TYPE;

Line: 141


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
Using
 p_start_date
,p_end_date
,p_campaign_id
,p_campaign_status_id
,p_campaign_type_id
,p_media_id
,p_channel_id;

Line: 168


l_sql_insert_stm            varchar2(10000);

Line: 179


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, measure4, subject_name, view_by_name)';

Line: 181


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 184


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(ccost.actual_cost), 0, 0, ((sum(ccost.initiated_revenue)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';

Line: 187


l_sql_insert_stm := l_sql_insert_stm
                 || ' sum(ccost.forecasted_cost),';

Line: 190


l_sql_insert_stm := l_sql_insert_stm
                 || ' sum(ccost.actual_cost),';

Line: 193


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(ccost.actual_cost), 0,0,((sum(ccost.forecasted_cost)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';

Line: 242


   l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name, BDMC.channel_name ';

Line: 247


   l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name, bdmc.media_name ';

Line: 263


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
Using
p_start_date
,p_end_date
,p_campaign_id
,p_campaign_status_id
,p_campaign_type_id
,p_media_id
,p_channel_id;

Line: 285


l_sql_insert_stm            varchar2(10000);

Line: 296


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, measure4, subject_name, view_by_name)';

Line: 298


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 301


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(ccost.actual_cost), 0, 0, ((sum(ccost.initiated_revenue)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';

Line: 304


l_sql_insert_stm := l_sql_insert_stm
                 || ' sum(ccost.forecasted_cost),';

Line: 307


l_sql_insert_stm := l_sql_insert_stm
                 || ' sum(ccost.actual_cost),';

Line: 310


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(ccost.actual_cost), 0,0,((sum(ccost.forecasted_cost)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';

Line: 351


   l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value, bdmc.channel_name ';

Line: 356


   l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value, bdmc.media_name ';

Line: 372


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
Using
p_all_value
,p_campaign_status_id
,p_campaign_type_id
,p_media_id
,p_channel_id;

Line: 403


v_num_rows_inserted  integer;

Line: 404


v_num_rows_updated   integer;

Line: 412


l_sql_insert_stm 	varchar2(5000);

Line: 420


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';

Line: 422


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 425


l_sql_insert_stm := l_sql_insert_stm
                 || ' count(distinct(a.lead_id)), ';

Line: 428


l_sql_insert_stm := l_sql_insert_stm
                 || ' round(decode(count(distinct(a.lead_id)),0,0,sum(a.initiated_revenue)/count(distinct(a.lead_id))),2), ';

Line: 442


	l_sql_where:= l_sql_where|| ' and b.parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id is null  and :p_campaign_id is null ) ';

Line: 444


	l_sql_where:= l_sql_where|| ' and b.parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id is null  and :p_campaign_id is null ) ';

Line: 519


	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, c.channel_name';

Line: 525


	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, e.media_name ';

Line: 531


	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.market_segment_name ';

Line: 537


	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.sales_channel_name ';

Line: 543


	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.period_name ';

Line: 559


l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;

Line: 571


		utl_file.put_line(fp,l_sql_insert_stm);

Line: 634


   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 658


v_num_rows_inserted  	integer;

Line: 659


v_num_rows_updated   	integer;

Line: 666


l_sql_insert_stm 		varchar2(5000);

Line: 674


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';

Line: 676


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 679


l_sql_insert_stm := l_sql_insert_stm
                 || ' count(distinct(a.lead_id)), ';

Line: 682


l_sql_insert_stm := l_sql_insert_stm
                 || ' round(decode(count(distinct(a.lead_id)),0,0,sum(a.initiated_revenue)/count(distinct(a.lead_id))),2), :p_all_value ,';

Line: 777


	l_sql_insert_stm := l_sql_insert_stm || ' c.channel_name ';

Line: 783


	l_sql_insert_stm := l_sql_insert_stm || ' d.media_name ';

Line: 789


	l_sql_insert_stm := l_sql_insert_stm || ' d.market_segment_name ';

Line: 795


	l_sql_insert_stm := l_sql_insert_stm || ' d.sales_channel_name ';

Line: 801


	l_sql_insert_stm := l_sql_insert_stm || '  d.period_name ';

Line: 810


l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;

Line: 822


		utl_file.put_line(fp,l_sql_insert_stm);

Line: 836


delete bim_camp_acqu_summ_temp;

Line: 877


   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 902


l_sql_insert_stm            varchar2(10000);

Line: 916


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';

Line: 918


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 920


l_sql_insert_stm := l_sql_insert_stm || ' Sum(nvl(BSCR.num_responded,0)),';

Line: 921


l_sql_insert_stm := l_sql_insert_stm || ' decode(sum(BSCR.NUM_TARGETED),0,0,( SUM(BSCR.NUM_RESPONDED) / SUM(BSCR.NUM_TARGETED) ) * 100), ';

Line: 983


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMS.market_segment_name  ';

Line: 991


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,bdp.period_name ';

Line: 1008


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMC.media_name ';

Line: 1016


   l_sql_insert_stm := l_sql_insert_stm || ' BDC.Campaign_name,BMC.Channel_name ';

Line: 1049


		utl_file.put_line(fp,l_sql_insert_stm);

Line: 1065


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
Using
p_start_date,
p_end_date,
p_campaign_id,
p_campaign_status_id,
p_campaign_type_id,
p_media_id,
p_channel_id,
p_market_segment_id,
p_geography_code,
p_period_type;

Line: 1080


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
Using
p_start_date,
p_end_date,
p_campaign_id,
p_campaign_status_id,
p_campaign_type_id,
p_media_id,
p_channel_id,
p_market_segment_id,
p_geography_code;

Line: 1104


l_sql_insert_stm            varchar2(10000);

Line: 1114


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, subject_name )';

Line: 1116


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 1119


l_sql_insert_stm := l_sql_insert_stm
                 || '  sum(ccost.forecasted_revenue) , ';

Line: 1122


l_sql_insert_stm := l_sql_insert_stm
                 || ' sum(ccost.initiated_revenue) ,';

Line: 1126


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(ccost.initiated_revenue),0,0,((sum(ccost.initiated_revenue)-sum(ccost.forecasted_revenue))/sum(ccost.initiated_revenue))*100) ,';

Line: 1138


	l_sql_where:= l_sql_where|| ' and denorm .parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id is null  and :p_campaign_id is null ) ';

Line: 1140


	l_sql_where:= l_sql_where|| ' and denorm.parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id = :p_campaign_id ) ';

Line: 1144


l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name ';

Line: 1162


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by
Using  p_campaign_id ;

Line: 1175


l_sql_insert_stm            varchar2(10000);

Line: 1185


l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, subject_name )';

Line: 1187


l_sql_insert_stm := l_sql_insert_stm
                 || ' SELECT ' ;

Line: 1190


l_sql_insert_stm := l_sql_insert_stm
                 || '  sum(ccost.forecasted_revenue) , ';

Line: 1193


l_sql_insert_stm := l_sql_insert_stm
                 || ' sum(ccost.initiated_revenue) ,';

Line: 1197


l_sql_insert_stm := l_sql_insert_stm
                 || ' decode(sum(ccost.initiated_revenue),0,0,((sum(ccost.initiated_revenue)-sum(ccost.forecasted_revenue))/sum(ccost.initiated_revenue))*100), :p_all_value';

Line: 1238


EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where ||l_sql_group_by|| l_sql_order_by
Using
p_all_value
,p_campaign_type_id
,p_campaign_status_id
,p_all_value;