APPS.BIM_MCHACQ_IND_TEMP_PVT SQL Statements

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

Line: 24

v_num_rows_inserted  integer;

Line: 25

v_num_rows_updated   integer;

Line: 62

   EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
       ( subject_name,
         view_by_name,
         rank_by,
         measure1,
         measure3  )
       select chn.channel_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,
              bim_dimv_channels chn ' || from_clause ||
      ' where cperf.channel_id = chn.channel_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 chn.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;

Line: 109

   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 113

    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_channels chn ' || from_clause ||
      ' where cperf.channel_id = chn.channel_id
         and cperf.media_id = med.media_id' ||  where_clause  ||
       ' and chn.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;

Line: 158

  v_num_rows_updated := SQL%ROWCOUNT;

Line: 184

v_num_rows_inserted  integer;

Line: 185

v_num_rows_updated   integer;

Line: 202

      EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
       ( subject_name,
         view_by_name,
         rank_by,
         measure1,
         measure3  )
       select chn.channel_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_channels chn,
              bim_dimv_periods per ' || from_clause ||
      ' where cperf.channel_id = chn.channel_id
         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 chn.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;

Line: 255

   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 259

     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_channels chn,
             bim_dimv_periods per ' || from_clause ||
      ' where cperf.channel_id = chn.channel_id
         and cperf.media_id = med.media_id' ||  where_clause  ||
       ' and chn.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;

Line: 308

  v_num_rows_updated := SQL%ROWCOUNT;