APPS.BIS_UTILITIES_PVT SQL Statements

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

Line: 208

, p_selected    varchar2
, p_value       varchar2
)
is
begin
--
  if (p_number <> FND_API.G_MISS_NUM) then
    if (p_value is null) then
      htp.formSelectOption(p_number);

Line: 218

      htp.formSelectOption(p_number, p_selected, 'VALUE="'||p_value||'"');

Line: 260

, p_selected    varchar2 := NULL
, p_value       varchar2 := NULL
)
is
begin
--
  if (p_varchar <> BIS_UTILITIES_PUB.G_NULL_CHAR) then
    if (p_value is null) then
      htp.formSelectOption(p_varchar);

Line: 270

      htp.formSelectOption(p_varchar, p_selected, 'VALUE="'||p_value||'"');

Line: 562

  BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
                                    , l_return_status
                                    , l_error_tbl
                                    );

Line: 591

  BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
                                    , l_return_status
                                    , l_error_tbl
                                    );

Line: 624

  BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
                                    , l_return_status
                                    , l_error_tbl
                                    );

Line: 661

  BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
                                    , l_return_status
                                    , l_error_tbl
                                    );

Line: 687

  BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
                                    , l_return_status
                                    , l_error_tbl
                                    );

Line: 938

PROCEDURE putDeleteFunction
( p_form_name       varchar2
, p_action_var      varchar2
, p_str             varchar2
, p_submit_form     varchar2
)
is
begin

   htp.p('function ' || G_FUNCTION_SUBMIT_FORM_DELETE || '()
     {
     if (confirm("'
             || BIS_UTILITIES_PVT.Get_FND_Message
                           ( p_message_name => 'BIS_CONFIRM_DELETE_MESSAGE' )
             || '"))
          {document.'
           || p_form_name
           ||'.'
           || p_action_var
           || '.value="'
           || G_ACTION_DELETE
           || '";'

Line: 974

end putDeleteFunction;

Line: 993

PROCEDURE putUpdateFunction
( p_form_name       varchar2
, p_action_var      varchar2
, p_str             varchar2
, p_submit_form     varchar2
)
is
begin
  putFunction( p_form_name
             , p_action_var
             , p_str
             , G_FUNCTION_SUBMIT_FORM_UPDATE
             , G_ACTION_UPDATE
             , p_submit_form
             );

Line: 1008

end putUpdateFunction;

Line: 2299

  select DECODE(p_string,
                  NULL,'NULL',
                  FND_API.G_MISS_CHAR,'NULL',
                  ''''||p_string||'''')
  into x_string
  from dual;

Line: 2575

   SELECT x.sequence_no
   FROM   bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
   WHERE  x.dimension_id = y.dimension_id
   AND    y.short_name like p_dim
   AND    x.indicator_id = z.indicator_id
   AND    ((z.target_level_id = p_targetlevel_id  and  p_targetlevel_id is not null) OR
          (z.short_name = p_dim_values_rec.target_level_short_name
            and p_dim_values_rec.target_level_short_name IS NOT NULL))
   ;

Line: 2788

   SELECT x.sequence_no
   FROM   bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
   WHERE  x.dimension_id = y.dimension_id
   AND    y.short_name like p_dim
   AND    x.indicator_id = z.indicator_id
   AND    z.target_level_id = p_targetlevel_id;

Line: 2982

   SELECT x.sequence_no
   FROM   bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
   WHERE  x.dimension_id = y.dimension_id
   AND    y.short_name like p_dim
   AND    x.indicator_id = z.indicator_id
   AND    ((z.target_level_id = p_targetlevel_id  and  p_targetlevel_id is not null) OR
          (z.short_name = p_dim_values_rec.target_level_short_name
            and p_dim_values_rec.target_level_short_name IS NOT NULL))
   ;

Line: 3082

   SELECT x.sequence_no
   FROM   bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
   WHERE  x.dimension_id = y.dimension_id
   AND    y.short_name like p_dim
   AND    x.indicator_id = z.indicator_id
   AND    ((z.target_level_id = p_targetlevel_id  and  p_targetlevel_id is not null) OR
          (z.short_name = p_dim_values_rec.target_level_short_name
            and p_dim_values_rec.target_level_short_name IS NOT NULL))
   ;

Line: 3194

  SELECT source
  FROM  bis_levels
  WHERE level_id = p_DimLevelId
  ;

Line: 3200

  SELECT source
  FROM  bis_levels
  WHERE short_name = p_DimLevelName
  ;

Line: 3240

  SELECT source
  FROM  bis_levels
  WHERE level_id = p_DimLevelId
  ;

Line: 3246

  SELECT source
  FROM  bis_levels
  WHERE short_name = p_DimLevelShortName
  ;

Line: 3283

  SELECT source
  FROM  bis_levels
  WHERE level_id = p_DimLevelId
  ;

Line: 3289

  SELECT source
  FROM  bis_levels
  WHERE short_name = p_DimLevelName
  ;

Line: 3329

  SELECT source
  FROM  bis_levels
  WHERE level_id = p_DimLevelId;

Line: 3334

  SELECT source
  FROM  bis_levels
  WHERE short_name = p_DimLevelName;

Line: 3378

  SELECT source
  FROM  bis_target_levels
  WHERE target_level_id = p_TargetLevelId
  ;

Line: 3384

  SELECT source
  FROM  bis_target_levels
  WHERE short_name = p_TargetLevelName
  ;

Line: 3429

  SELECT source
  FROM  bis_target_levels
  WHERE target_level_id = p_TargetLevelId
  ;

Line: 3435

  SELECT source
  FROM  bis_target_levels
  WHERE short_name = p_TargetLevelName
  ;

Line: 3495

    SELECT short_name
    INTO l_tgt_lvl_short_name
    FROM bis_target_levels
    WHERE target_level_id = p_tgt_lvl_ID;

Line: 3508

    SELECT BL.short_name  -- 2735844
    INTO   l_level_short_name
    FROM   bis_levels BL,
           bis_target_levels BTL
    WHERE  BTL.short_name = l_tgt_lvl_short_name
         AND
       ( BL.level_id = BTL.dimension1_level_id
          OR BL.level_id = BTL.dimension2_level_id
          OR BL.level_id = BTL.dimension3_level_id
          OR BL.level_id = BTL.dimension4_level_id
          OR BL.level_id = BTL.dimension5_level_id
          OR BL.level_id = BTL.dimension6_level_id
          OR BL.level_id = BTL.dimension7_level_id )
         AND BL.short_name                         -- 2735844
               IN ('EDW_MTL_ILDM_OU', 'EDW_MTL_ILDM_PLANT',
                   'EDW_ORGA_OPER_UNIT', 'EDW_ORGA_ORG');

Line: 3525

    select short_name
    into l_dim_short_name
    from bis_dimensions where dimension_id in
      (select dimension_id from bis_levels where short_name = l_level_short_name);

Line: 3603

  SELECT source
  FROM  bis_levels
  WHERE short_name = p_dim_Level_short_name
  ;

Line: 3676

  SELECT source
  FROM  bis_levels
  WHERE level_id = p_DimLevelId
  ;

Line: 3682

  SELECT source
  FROM  bis_levels
  WHERE short_name = p_DimLevelName
  ;

Line: 3723

  SELECT source
  FROM  bis_target_levels
  WHERE target_level_id = p_TargetLevelId
  ;

Line: 3729

  SELECT source
  FROM  bis_target_levels
  WHERE short_name = p_TargetLevelName
  ;

Line: 3805

   SELECT SHORT_NAME
   INTO l_time_level_short_name
   FROM BIS_LEVELS
   WHERE LEVEL_ID = p_dim_level_id;

Line: 3838

   l_selectStmt          VARCHAR2(32000);

Line: 3951

    BIS_UTILITIES_PUB.put_line(p_text =>'GET_TIME_FROM : SQL Statement is  '|| l_selectStmt);

Line: 3981

   l_selectStmt          VARCHAR2(32000);

Line: 4097

      BIS_UTILITIES_PUB.put_line(p_text => ' l_selectStmt is : ' || l_selectStmt);

Line: 4206

  CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
    level_values_view_name =  cp_lvl_values_view ;

Line: 4234

      l_sql := ' select ' || p_id_name
             || ' from ' || p_view_name
             || ' where :l_target_date between '
             ||   ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
             ||   ' and ' || p_id_name || ' not in (-1,0) '
             || ' and nvl(organization_id, :l_star1) = nvl(:p_Org_Level_ID, :l_star1) '
             || ' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) '
             || ' ORDER BY abs( nvl(trunc(start_date), trunc (sysdate)) - '
         ||   '  nvl(trunc(end_date), trunc(sysdate))) ';

Line: 4244

      l_sql := ' select ' || p_id_name
             || ' from ' || p_view_name
             || ' where :l_target_date between '
             ||   ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
             ||   ' and ' || p_id_name || ' not in (-1,0) '
             || ' ORDER BY abs( nvl(trunc(start_date), trunc (sysdate)) - '
         ||   '  nvl(trunc(end_date), trunc(sysdate))) ';

Line: 4275

      l_sql := ' select distinct ' || p_id_name
             || ' from ' || p_view_name
             || ' where :l_target_date between '
             ||   ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
             || ' and nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) '
             || ' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';

Line: 4282

      l_sql := ' select distinct ' || p_id_name
             || ' from ' || p_view_name
             || ' where :l_target_date between '
             ||   ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
             ||   ' and rownum < 2';

Line: 4324

  l_selectStmt  VARCHAR2(32000);

Line: 4333

  CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
    level_values_view_name =  cp_lvl_values_view ;

Line: 4361

     l_selectStmt :=  ' SELECT ' || p_value_col_name  ||
                     ' FROM ' || p_table_name;

Line: 4365

          l_selectStmt := l_selectStmt || ' WHERE END_DATE >= :l_date ' ||
                     '   AND START_DATE <= :l_date ' ;

Line: 4368

          l_selectStmt := l_selectStmt || ' WHERE START_DATE = :l_date ';

Line: 4370

          l_selectStmt := l_selectStmt || ' WHERE END_DATE = :l_date ';

Line: 4376

         l_selectStmt :=  l_selectStmt ||
                      '   AND  nvl(organization_id, :l_star1) = nvl(:p_Org_Level_ID, :l_star1) ' ||
                      '   AND  nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';

Line: 4381

       l_selectStmt :=  l_selectStmt ||
                  ' ORDER BY abs( nvl(trunc(end_date), trunc (sysdate)) - ' ||
                       '    nvl(trunc(start_date), trunc(sysdate))) ';

Line: 4389

            OPEN l1_cursor FOR l_selectStmt
             using p_date, p_date,l_star1,p_Org_Level_ID,l_star1,l_star,p_Org_Level_Short_name,l_star;

Line: 4392

            OPEN l1_cursor FOR l_selectStmt
             using p_date, p_date;

Line: 4397

            OPEN l1_cursor FOR l_selectStmt
             using p_date ,l_star1,p_Org_Level_ID,l_star1,l_star,p_Org_Level_Short_name,l_star;

Line: 4400

            OPEN l1_cursor FOR l_selectStmt
             using p_date;

Line: 4410

       BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );

Line: 4424

    BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );

Line: 4432

    l_selectStmt :=  ' SELECT ' || p_value_col_name  ||
                     ' FROM ' || p_table_name;

Line: 4436

          l_selectStmt := l_selectStmt || ' WHERE END_DATE >= :l_date ' ||
                     '   AND START_DATE <= :l_date ' ;

Line: 4439

          l_selectStmt := l_selectStmt || ' WHERE START_DATE = :l_date ';

Line: 4441

          l_selectStmt := l_selectStmt || ' WHERE END_DATE = :l_date ';

Line: 4447

          l_selectStmt :=  l_selectStmt ||
                     '   AND  nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) ' ||
                      '   AND  nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';

Line: 4452

          l_selectStmt :=  l_selectStmt || ' AND rownum < 2 '; -- take the first row

Line: 4460

             EXECUTE IMMEDIATE  l_selectStmt INTO v_value2
                  USING p_date, p_date, l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;

Line: 4463

             EXECUTE IMMEDIATE  l_selectStmt INTO v_value2 USING p_date, p_date;

Line: 4467

             EXECUTE IMMEDIATE  l_selectStmt INTO v_value2
                  USING p_date, l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;

Line: 4470

             EXECUTE IMMEDIATE  l_selectStmt INTO v_value2 USING p_date;

Line: 4493

    BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );

Line: 4527

  CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
    level_values_view_name =  cp_lvl_values_view ;

Line: 4553

       l_sql :=  ' SELECT START_DATE, END_DATE ' ||
                ' FROM ' || p_view_name  ||
                ' WHERE '|| p_id_col_name || ' = :p_id' ||
                ' and nvl(organization_id, :l_star1) = nvl(:p_Org_Level_ID, :l_star1) '||
                ' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) '||
                -- ' AND ' || p_value_col_name || ' = :p_time_value ' ||
                ' ORDER BY abs( nvl(trunc(end_date), trunc (sysdate)) - ' ||
            '  nvl(trunc(start_date), trunc(sysdate))) ';

Line: 4562

       l_sql :=  ' SELECT START_DATE, END_DATE ' ||
                ' FROM ' || p_view_name  ||
                ' WHERE '|| p_id_col_name || ' = :p_id' ||
                -- ' AND ' || p_value_col_name || ' = :p_time_value ' ||
                ' ORDER BY abs( nvl(trunc(end_date), trunc (sysdate)) - ' ||
            '  nvl(trunc(start_date), trunc(sysdate))) ';

Line: 4603

          l_sql :=  ' SELECT DISTINCT START_DATE, END_DATE ' ||
                ' FROM ' || p_view_name  ||
                ' WHERE '|| p_id_col_name || ' = :p_id'
                     -- || ' and ' || p_value_col_name || ' = :p_time_value '
                         ||' and nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) '
                 ||' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';

Line: 4610

         l_sql :=  ' SELECT DISTINCT START_DATE, END_DATE ' ||
                ' FROM ' || p_view_name  ||
                ' WHERE '|| p_id_col_name || ' = :p_id' ;

Line: 4664

  l_selectStmt  VARCHAR2(32000);

Line: 4671

  CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
    level_values_view_name =  cp_lvl_values_view ;

Line: 4695

            l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE)  ' ||
                            ' FROM  ' || p_view_name ||
                            ' WHERE  nvl(organization_id, :l_star)  = nvl(:p_Org_Level_ID, :l_star) ' ||
                            '   AND  nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';

Line: 4700

            l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE)  ' ||
                            ' FROM  ' || p_view_name ;

Line: 4706

            OPEN l_cursor FOR l_selectStmt USING l_star ,p_Org_Level_ID, l_star , l_star ,p_Org_Level_Short_name,l_star  ;

Line: 4708

            OPEN l_cursor FOR l_selectStmt ;

Line: 4714

           BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );

Line: 4725

            BIS_UTILITIES_PUB.put_line(p_text => ' l_selectStmt is : ' || l_selectStmt);

Line: 4732

              l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE)  ' ||
                             ' FROM  ' || p_view_name ||
                             ' WHERE  nvl(organization_id, :l_star)  = nvl(:p_Org_Level_ID, :l_star) ' ||
                             '   AND  nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ' ||
                             '   AND  start_date < end_date ';

Line: 4738

              l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE)  ' ||
                             ' FROM  ' || p_view_name ||
                             '   WHERE  start_date < end_date ';

Line: 4745

            EXECUTE IMMEDIATE  l_selectStmt INTO l_min_start_date, l_max_end_date
        USING l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;

Line: 4748

            EXECUTE IMMEDIATE  l_selectStmt INTO l_min_start_date, l_max_end_date;

Line: 4754

              BIS_UTILITIES_PUB.put_line(p_text => ' l_selectStmt is : ' || l_selectStmt);

Line: 4767

    BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );

Line: 4969

  SELECT level_id
  INTO   l_level_id
  FROM   bis_levels
  WHERE       short_name = p_level_short_name
         AND  source = 'OLTP'
         AND  level_values_view_name IS NULL;

Line: 5037

  SELECT MEANING
  INTO l_meaning
  FROM FND_LOOKUP_VALUES_VL
  WHERE LOOKUP_TYPE = p_lookup_type
  AND   LOOKUP_CODE = p_lookup_code;

Line: 5294

  SELECT vp.value
  INTO l_default_dir_name
  FROM v$parameter vp
  WHERE vp.name = BIS_UTILITIES_PUB.G_UTL_FILE_DIR; -- 'utl_file_dir';

Line: 5330

  SELECT bis_debug_log_s.nextval
  INTO l_temp_num
  FROM dual;

Line: 5503

    SELECT  source ,dimension_short_name, dimension_level_short_name
    FROM bisfv_dimension_levels
    WHERE dimension_level_id = p_bis_dimlevel_id;

Line: 5574

    SELECT orig_system_id FROM wf_local_roles
    WHERE name = p_role_name
    AND ( orig_system LIKE 'FND_RESP%'
      OR orig_system = 'FND_USR' )
    AND rownum < 2;