APPS.BIM_MCHACQ_ALL_TEMP_PVT SQL Statements

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

Line: 23

v_num_rows_inserted  integer;

Line: 24

v_num_rows_updated   integer;

Line: 32

SELECT meaning
  FROM FND_LOOKUPS
WHERE  lookup_type = 'BIM_VALUE_TYPE'
  AND  lookup_code = 'ALL' ;

Line: 72

   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.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.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_campaign_id,
     p_start_date,
     p_end_date,
     p_campaign_status_id,
     p_campaign_type,
     p_media_type,
     p_media_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: 115

   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 119

    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.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.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_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: 183

v_num_rows_inserted  integer;

Line: 184

v_num_rows_updated   integer;

Line: 191

SELECT meaning
  FROM FND_LOOKUPS
WHERE  lookup_type = 'BIM_VALUE_TYPE'
  AND  lookup_code = 'ALL' ;

Line: 209

      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' || from_clause ||
      ' where 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.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_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_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: 258

   v_num_rows_inserted := SQL%ROWCOUNT;

Line: 262

     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 ' || from_clause ||
      ' where 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 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.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_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: 306

  v_num_rows_updated := SQL%ROWCOUNT;