The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_num_rows_inserted integer;
v_num_rows_updated integer;
SELECT meaning
FROM FND_LOOKUPS
WHERE lookup_type = 'BIM_VALUE_TYPE'
AND lookup_code = 'ALL' ;
EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
( subject_name,
view_by_name,
rank_by,
measure1,
measure3 )
select ''' || v_all_name || ''',' ||
v_view_by ||
', count( distinct cperf.cust_account_id ),
count( distinct cperf.cust_account_id ),
nvl(sum( cperf.initiated_revenue), 0 )
from bim_cmpgn_perf_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med ' || from_clause ||
' where cperf.campaign_id = cmp.campaign_id
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 ' || v_view_by
USING
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' || from_clause ||
' where cperf.campaign_id = cmp.campaign_id
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 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_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;
v_num_rows_updated := SQL%ROWCOUNT;
v_num_rows_inserted integer;
v_num_rows_updated integer;
SELECT meaning
FROM FND_LOOKUPS
WHERE lookup_type = 'BIM_VALUE_TYPE'
AND lookup_code = 'ALL' ;
EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
( subject_name,
view_by_name,
rank_by,
measure1,
measure3 )
select ''' || v_all_name ||
''', per.period_name,
to_number( to_char(per.start_date, ''J'')),
count( distinct cperf.cust_account_id ),
nvl(sum(cperf.initiated_revenue), 0 )
from bim_cmpgn_perf_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med,
bim_dimv_periods per
where cperf.campaign_id = cmp.campaign_id
and cperf.media_id = med.media_id
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 per.period_name, per.start_date'
USING
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.measure2 =
( select nvl(sum(cperf.initiated_revenue), 0)
from bim_customer_rev_summ cperf,
bim_dimv_campaigns cmp,
bim_dimv_media med,
bim_dimv_periods per
where cperf.campaign_id = cmp.campaign_id
and cperf.media_id = med.media_id
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_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;