APPS.OE_BIS_ALERTS SQL Statements

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

Line: 42

        select year_period, decode(sum(net_sales),0,0,sum(del_sales)/sum(net_sales) * 100)
        into l_time_period, l_actual_value
        from oe_bis_cust_sat_v
        where set_of_books_id = p_set_of_books_id
        and sales_channel_code = p_sales_channel
        and category_id = p_prod_catg
        and area = p_area
        and   period_set_name || '+' || year_period = p_period_set_name
        group by year_period;

Line: 59

        select year_period, decode(sum(net_sales),0,0,sum(ret_sales)/sum(net_sales) * 100)
	   into l_time_period, l_actual_value
        from oe_bis_cust_sat_v
        where set_of_books_id = p_set_of_books_id
        and sales_channel_code = p_sales_channel
        and category_id = P_prod_catg
        and area = p_area
        and   period_set_name || '+' || year_period = p_period_set_name
        group by year_period;

Line: 74

        select year_period, decode(sum(net_sales),0,0,sum(del_sales)/sum(net_sales) * 100)
        into l_time_period, l_actual_value
        from oe_bis_cust_sat_v
        where set_of_books_id = p_set_of_books_id
        and   period_set_name || '+' || year_period = p_period_set_name
        group by year_period;

Line: 86

        select year_period, decode(sum(net_sales),0,0,sum(ret_sales)/sum(net_sales) * 100)
        into l_time_period, l_actual_value
        from oe_bis_cust_sat_v
        where set_of_books_id = p_set_of_books_id
        and   period_set_name || '+' || year_period = p_period_set_name
        group by year_period;

Line: 110

/* User selected KPI's from the BIS home page are also updated with the actual values. BIS provided API's are used
   to retreive user selections and to post the actuals on the home page */

PROCEDURE process_alerts
( p_target_level_short_name    VARCHAR2,
  p_time_period                VARCHAR2
)
IS
l_period_set_name          VARCHAR2(30);

Line: 130

l_user_selection_tbl       BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;

Line: 158

  SELECT distinct set_of_books_id
  FROM   oe_bis_cust_sat_v;

Line: 162

  SELECT  tv.target_level_short_name
        , tv.target_level_name
        , tv.target_level_id
        , tv.plan_name
        , tv.org_level_value_id
        , tv.time_level_value_id
        , tv.dim1_level_value_id
        , tv.dim2_level_value_id
        , tv.dim3_level_value_id
        , tv.target
        , tv.range1_low
        , tv.range1_high
        , tv.range2_low
        , tv.range2_high
        , tv.range3_low
        , tv.range3_high
        , tv.notify_resp1_id
        , tv.notify_resp1_short_name
        , tv.notify_resp2_id
        , tv.notify_resp2_short_name
        , tv.notify_resp3_id
        , tv.notify_resp3_short_name
  FROM BISFV_TARGETS tv
  WHERE tv.target_level_short_name = p_target_level_short_name
  AND tv.time_level_value_id = p_time_period;

Line: 193

  SELECT workflow_process_short_name
  INTO l_workflow_process
  FROM bisfv_target_levels
  WHERE target_level_short_name = p_target_Level_Short_Name;

Line: 200

  BIS_ACTUAL_PUB.Retrieve_User_Selections
  ( p_api_version                  => 1.0
   ,p_Target_Level_Rec             => l_Target_Level_Rec
   ,x_Indicator_Region_Tbl         => l_user_selection_Tbl
   ,x_return_status                => l_return_status
   ,x_msg_count                    => l_msg_count
   ,x_msg_data                     => l_msg_data
   ,x_error_Tbl                    => l_error_tbl
  );

Line: 218

    FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
      IF l_user_selection_tbl(i).org_level_value_id = l_organization_id THEN
        l_Actual_Tbl(i).target_Level_Short_Name
          := l_user_selection_tbl(i).target_level_short_name;

Line: 224

        l_Actual_Tbl(i).dim1_Level_value_ID := l_user_selection_tbl(i).dim1_Level_value_ID;

Line: 225

        l_Actual_Tbl(i).dim2_Level_value_ID := l_user_selection_tbl(i).dim2_Level_value_ID;

Line: 226

        l_Actual_Tbl(i).dim3_Level_value_ID := l_user_selection_tbl(i).dim3_level_value_ID;

Line: 230

                p_sales_channel    => l_user_selection_tbl(i).dim1_level_value_ID,
                p_prod_catg        => l_user_selection_tbl(i).dim2_level_value_ID,
                p_area             => l_user_selection_tbl(i).dim3_level_value_ID,
                p_period_set_Name  => p_time_period,
                p_time_period      => p_time_period,
                p_target_level_short_name => p_target_level_short_name
              );

Line: 283

        select name into l_org_name
        from   gl_sets_of_books
        where  set_of_books_id = cr.org_level_value_id ;

Line: 292

        select distinct category_desc into l_catg_name
        from   oe_bis_cust_sat_v
        where  category_id =  cr.dim2_level_value_id ;

Line: 302

        select to_char(start_date, 'DD-MON-YYYY'), to_char(end_date + 1,'DD-MON-YYYY')
               into l_start_date , l_end_date
        from   bis_years_v
        where period_set_name = substr(p_time_period, 1, instr(p_time_period,'+')-1) and
		    period_name = substr(p_time_period,instr(p_time_period,'+')+1, length(p_time_period)) and
              rownum = 1;

Line: 828

   select name from wf_roles
   where name = p_role;

Line: 848

   select bis_excpt_wf_s.nextval
   into l_wf_item_key
   from dual;