The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_num_rows_inserted integer;
v_num_rows_updated integer;
EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
( subject_name,
view_by_name,
rank_by,
measure1,
measure3,
measure4 )
select sch.sales_channel_name,' ||
v_view_by ||
', count( distinct cperf.lead_id ),
count( distinct cperf.lead_id ),
nvl(sum(cperf.initiated_revenue), 0 ),
count( distinct cperf.cust_account_id )
from bim_cmpgn_perf_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med,
bim_dimv_sales_channels sch ' || from_clause ||
' where cperf.sales_channel_code = sch.sales_channel_code
and cperf.media_id = med.media_id' || where_clause ||
' and cperf.period_start_date >= :p_start_date
and cperf.period_end_date <= :p_end_date
and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
and med.media_type_code = nvl(:p_media_type, med.media_type_code )
and cperf.media_id = nvl(:p_media_id, cperf.media_id )
and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
group by sch.sales_channel_name, ' || v_view_by
USING
p_campaign_id,
p_start_date,
p_end_date,
p_campaign_status_id,
p_campaign_type,
p_media_type,
p_media_id,
p_channel_id,
p_sales_channel_code,
p_market_segment_id,
p_interest_type_id,
p_primary_interest_code_id,
p_secondary_interest_code_id,
p_geography_code;
v_num_rows_inserted := SQL%ROWCOUNT;
EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
set tmp.measure2 =
( select nvl(sum(cperf.initiated_revenue), 0)
from bim_customer_rev_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med,
bim_dimv_sales_channels sch ' || from_clause ||
' where cperf.sales_channel_code = sch.sales_channel_code
and cperf.media_id = med.media_id' || where_clause ||
' and sch.sales_channel_name = tmp.subject_name
and cperf.period_start_date >= :p_start_date
and cperf.period_end_date <= :p_end_date
and cperf.first_order_date >= :p_start_date
and cperf.first_order_date <= :p_end_date
and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
and med.media_type_code = nvl(:p_media_type, med.media_type_code )
and cperf.media_id = nvl(:p_media_id, cperf.media_id )
and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
and tmp.view_by_name = ' || v_view_by || ' ) '
USING
p_campaign_id,
p_start_date,
p_end_date,
p_start_date,
p_end_date,
p_campaign_status_id,
p_campaign_type,
p_media_type,
p_media_id,
p_channel_id,
p_sales_channel_code,
p_market_segment_id,
p_interest_type_id,
p_primary_interest_code_id,
p_secondary_interest_code_id,
p_geography_code;
UPDATE bim_camp_acqu_summ_temp SET measure2 = measure2*100/measure3;
UPDATE bim_camp_acqu_summ_temp SET measure3 = measure3/measure1;
v_num_rows_updated := SQL%ROWCOUNT;
v_num_rows_inserted integer;
v_num_rows_updated integer;
EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
( subject_name,
view_by_name,
rank_by,
measure1,
measure2,
measure3 )
select sch.sales_channel_name,
per.period_name,
to_number( to_char(per.start_date, ''J'')),
count( distinct cperf.lead_id ),
nvl(sum(cperf.initiated_revenue), 0 ),
count( distinct cperf.cust_account_id )
from bim_cmpgn_perf_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med,
bim_dimv_sales_channels sch,
bim_dimv_periods per ' || from_clause ||
' where cperf.sales_channel_code = sch.sales_channel_code
and cperf.media_id = med.media_id' || where_clause ||
' and cperf.period_start_date >= per.start_date
and cperf.period_end_date <= per.end_date
and per.period_type = :p_period_type
and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
and per.start_date >= :p_start_date
and per.end_date <= :p_end_date
and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
and med.media_type_code = nvl(:p_media_type, med.media_type_code )
and cperf.media_id = nvl(:p_media_id, cperf.media_id )
and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
group by sch.sales_channel_name, per.period_name, per.start_date'
USING
p_campaign_id,
p_period_type,
p_start_date,
p_end_date,
p_campaign_status_id,
p_campaign_type,
p_media_type,
p_media_id,
p_channel_id,
p_sales_channel_code,
p_market_segment_id,
p_interest_type_id,
p_primary_interest_code_id,
p_secondary_interest_code_id,
p_geography_code;
v_num_rows_inserted := SQL%ROWCOUNT;
EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
set tmp.measure4 =
( select nvl(sum(cperf.initiated_revenue), 0)
from bim_customer_rev_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med,
bim_dimv_sales_channels sch,
bim_dimv_periods per ' || from_clause ||
' where cperf.sales_channel_code = sch.sales_channel_code
and cperf.media_id = med.media_id' || where_clause ||
' and sch.sales_channel_name = tmp.subject_name
and cperf.period_start_date >= per.start_date
and cperf.period_end_date <= per.end_date
and cperf.first_order_date >= per.start_date
and cperf.first_order_date <= per.end_date
and per.period_type = :p_period_type
and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
and per.start_date >= :p_start_date
and per.end_date <= :p_end_date
and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
and med.media_type_code = nvl(:p_media_type, med.media_type_code )
and cperf.media_id = nvl(:p_media_id, cperf.media_id )
and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
and tmp.view_by_name = per.period_name )'
USING
p_campaign_id,
p_period_type,
p_start_date,
p_end_date,
p_campaign_status_id,
p_campaign_type,
p_media_type,
p_media_id,
p_channel_id,
p_sales_channel_code,
p_market_segment_id,
p_interest_type_id,
p_primary_interest_code_id,
p_secondary_interest_code_id,
p_geography_code;
v_num_rows_updated := SQL%ROWCOUNT;