APPS.BIM_TSGMT_PERF_TEMP_PVT SQL Statements

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

Line: 22

v_num_rows_inserted  integer;

Line: 23

v_num_rows_updated   integer;

Line: 79

   EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
       ( subject_name,
         view_by_name,
         rank_by,
         measure1,
         measure3,
         measure4  )
       select tgt.target_segment_name,' ||
              v_view_by ||
             ',nvl(sum(tperf.num_of_leads), 0),
               nvl(sum(tperf.num_of_leads), 0),
               nvl(sum(initiated_revenue), 0),
               nvl(sum(num_of_new_accts), 0)
         from bim_trgt_sgmt_perf_summ tperf,
              bim_target_segments_denorm tdnm,
              bim_dimv_target_sgmts  tgt,
              bim_dimv_campaigns cmp,
              bim_dimv_media med ' || from_clause ||
      ' where tperf.target_segment_id = tdnm.target_segment_id
         and tdnm.parent_target_segment_id = tgt.target_segment_id
         and tperf.media_id = med.media_id' ||  where_clause  ||
       ' and tperf.period_start_date  >=  :p_start_date
         and tperf.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 tperf.media_id = nvl(:p_media_id, tperf.media_id )
         and tperf.channel_id = nvl(:p_channel_id, tperf.channel_id)
         and tperf.sales_channel_code = nvl(:p_sales_channel_code, tperf.sales_channel_code)
         and tperf.market_segment_id =  nvl(:p_market_segment_id, tperf.market_segment_id)
         and tperf.bill_to_geography_code = nvl(:p_geography_code, tperf.bill_to_geography_code )
       group by tgt.target_segment_name, '  || v_view_by
    USING
     p_campaign_id,
     p_trgt_sgmt_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_geography_code ;

Line: 125

   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 129

    EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
        set tmp.measure2 =
        ( select nvl(sum(tperf.initiated_revenue), 0)
         from bim_customer_rev_summ tperf,
              bim_target_segments_denorm tdnm,
              bim_dimv_target_sgmts  tgt,
              bim_dimv_campaigns cmp,
              bim_dimv_media med ' || from_clause ||
      ' where tperf.target_segment_id = tdnm.target_segment_id
         and tdnm.parent_target_segment_id = tgt.target_segment_id
         and tperf.media_id = med.media_id' ||  where_clause  ||
       ' and tmp.subject_name = tgt.target_segment_name
         and tperf.period_start_date  >=  :p_start_date
         and tperf.period_end_date <= :p_end_date
         and tperf.first_order_date >= :p_start_date
         and tperf.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 tperf.media_id = nvl(:p_media_id, tperf.media_id )
         and tperf.channel_id = nvl(:p_channel_id, tperf.channel_id)
         and tperf.sales_channel_code = nvl(:p_sales_channel_code, tperf.sales_channel_code)
         and tperf.market_segment_id =  nvl(:p_market_segment_id, tperf.market_segment_id)
         and tperf.bill_to_geography_code = nvl(:p_geography_code, tperf.bill_to_geography_code )
         and tmp.view_by_name = ' || v_view_by || ' ) '
    USING
     p_campaign_id,
     p_trgt_sgmt_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_geography_code ;

Line: 176

  UPDATE bim_camp_acqu_summ_temp SET measure2 = measure2*100/measure3;

Line: 178

  UPDATE bim_camp_acqu_summ_temp SET measure3 = measure3/measure1;

Line: 181

  v_num_rows_updated := SQL%ROWCOUNT;

Line: 205

v_num_rows_inserted  integer;

Line: 206

v_num_rows_updated   integer;

Line: 233

      EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
       ( subject_name,
         view_by_name,
         rank_by,
         measure1,
         measure2,
         measure3  )
       select tgt.target_segment_name,
              per.period_name,
              to_number( to_char(per.start_date, ''J'')),
              nvl(sum(tperf.num_of_leads), 0),
              nvl(sum(initiated_revenue), 0),
              nvl(sum(num_of_new_accts), 0)
          from bim_trgt_sgmt_perf_summ tperf,
              bim_target_segments_denorm tdnm,
              bim_dimv_target_sgmts  tgt,
              bim_dimv_campaigns cmp,
              bim_dimv_media med,
              bim_dimv_periods per ' || from_clause ||
      ' where tperf.target_segment_id = tdnm.target_segment_id
         and tdnm.parent_target_segment_id = tgt.target_segment_id
         and tperf.media_id = med.media_id' ||  where_clause  ||
       ' and tperf.period_start_date >= per.start_date
         and tperf.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 tperf.media_id = nvl(:p_media_id, tperf.media_id )
         and tperf.channel_id = nvl(:p_channel_id, tperf.channel_id)
         and tperf.sales_channel_code = nvl(:p_sales_channel_code, tperf.sales_channel_code)
         and tperf.market_segment_id =  nvl(:p_market_segment_id, tperf.market_segment_id)
         and tperf.bill_to_geography_code = nvl(:p_geography_code, tperf.bill_to_geography_code )
       group by tgt.target_segment_name, per.period_name, per.start_date'
     USING
      p_campaign_id,
      p_trgt_sgmt_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_geography_code;

Line: 285

   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 289

     EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
        set tmp.measure4 =
        ( select nvl(sum(tperf.initiated_revenue), 0)
          from bim_customer_rev_summ tperf,
              bim_target_segments_denorm tdnm,
              bim_dimv_target_sgmts  tgt,
              bim_dimv_campaigns cmp,
              bim_dimv_media med,
              bim_dimv_periods per ' || from_clause ||
      ' where tperf.target_segment_id = tdnm.target_segment_id
         and tdnm.parent_target_segment_id = tgt.target_segment_id
         and tperf.media_id = med.media_id' ||  where_clause  ||
       ' and tgt.target_segment_name = tmp.subject_name
         and tperf.period_start_date >= per.start_date
         and tperf.period_end_date <= per.end_date
         and tperf.first_order_date >= per.start_date
         and tperf.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 tperf.media_id = nvl(:p_media_id, tperf.media_id )
         and tperf.channel_id = nvl(:p_channel_id, tperf.channel_id)
         and tperf.sales_channel_code = nvl(:p_sales_channel_code, tperf.sales_channel_code)
         and tperf.market_segment_id =  nvl(:p_market_segment_id, tperf.market_segment_id)
         and tperf.bill_to_geography_code = nvl(:p_geography_code, tperf.bill_to_geography_code )
         and tmp.view_by_name = per.period_name )'
    USING
      p_campaign_id,
      p_trgt_sgmt_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_geography_code;

Line: 335

  v_num_rows_updated := SQL%ROWCOUNT;