The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_insert_stm varchar2(10000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
l_sql_insert_stm := l_sql_insert_stm
|| ' Sum(bscc.num_of_leads),';
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) )),';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMC.Channel_name ';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,bdm.media_name ';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.Campaign_name,BSCC.period_name ';
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;
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;
l_sql_insert_stm varchar2(10000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, measure4, subject_name, view_by_name)';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
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),';
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.forecasted_cost),';
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.actual_cost),';
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),';
l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name, BDMC.channel_name ';
l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name, bdmc.media_name ';
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;
l_sql_insert_stm varchar2(10000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, measure4, subject_name, view_by_name)';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
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),';
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.forecasted_cost),';
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.actual_cost),';
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),';
l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value, bdmc.channel_name ';
l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value, bdmc.media_name ';
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;
v_num_rows_inserted integer;
v_num_rows_updated integer;
l_sql_insert_stm varchar2(5000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
l_sql_insert_stm := l_sql_insert_stm
|| ' count(distinct(a.lead_id)), ';
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), ';
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 ) ';
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 ) ';
l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, c.channel_name';
l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, e.media_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.market_segment_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.sales_channel_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.period_name ';
l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
utl_file.put_line(fp,l_sql_insert_stm);
v_num_rows_inserted := SQL%ROWCOUNT;
v_num_rows_inserted integer;
v_num_rows_updated integer;
l_sql_insert_stm varchar2(5000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
l_sql_insert_stm := l_sql_insert_stm
|| ' count(distinct(a.lead_id)), ';
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 ,';
l_sql_insert_stm := l_sql_insert_stm || ' c.channel_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.media_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.market_segment_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.sales_channel_name ';
l_sql_insert_stm := l_sql_insert_stm || ' d.period_name ';
l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
utl_file.put_line(fp,l_sql_insert_stm);
delete bim_camp_acqu_summ_temp;
v_num_rows_inserted := SQL%ROWCOUNT;
l_sql_insert_stm varchar2(10000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
l_sql_insert_stm := l_sql_insert_stm || ' Sum(nvl(BSCR.num_responded,0)),';
l_sql_insert_stm := l_sql_insert_stm || ' decode(sum(BSCR.NUM_TARGETED),0,0,( SUM(BSCR.NUM_RESPONDED) / SUM(BSCR.NUM_TARGETED) ) * 100), ';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMS.market_segment_name ';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,bdp.period_name ';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMC.media_name ';
l_sql_insert_stm := l_sql_insert_stm || ' BDC.Campaign_name,BMC.Channel_name ';
utl_file.put_line(fp,l_sql_insert_stm);
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;
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;
l_sql_insert_stm varchar2(10000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, subject_name )';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.forecasted_revenue) , ';
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.initiated_revenue) ,';
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) ,';
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 ) ';
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 ) ';
l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name ';
EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by
Using p_campaign_id ;
l_sql_insert_stm varchar2(10000);
l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, subject_name )';
l_sql_insert_stm := l_sql_insert_stm
|| ' SELECT ' ;
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.forecasted_revenue) , ';
l_sql_insert_stm := l_sql_insert_stm
|| ' sum(ccost.initiated_revenue) ,';
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';
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;