eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIS_PMF_GET_DIMLEVELS_PVT SQL Statements

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

Line: 14


REM |     Private API for getting the Select String for DimensionLevelValues|
REM |     This API will get the Select String from either EDW or BIS        |
REM |     depending on the profile option BIS_SOURCE                        |
REM |                                                                       |
REM | HISTORY                                                               |
REM | December-2000 amkulkar Creation                                       |
REM | 09-OCT-2002 MAHRAO Fix for 2617369                                    |
REM | 17-OCT-2002 MAHRAO Fix for 2525408                                    |
REM | 23-DEC-2002 MAHRAO Fix for 2668693                                    |
REM | 03-JAN-2003 RCHANDRA Bug 2721710, populate the global variable        |
REM |                       G_DIM_LEVEL_SELECT_INFO_REC; reuse info if the  |

Line: 27


REM |                       added proc cahce_dim_lvl_Select_info
REM |                       to copy values into the global variable
REM | 05-MAY-2003 arhegde  enh#2819971 overload get_dim_level_select_string |
REM | 16-MAY-2003 rchandra bug#2959622 logic to parse p_paramlist           |
REM |                       get_dimlevel_select_wrap                        |
REM | 11-JUL-2003 rchandra bug#3014105, added support for EDW dimensions    |
REM |                       level relationships thru API get_oltp_edw_cols  |
REM | 25-JAN-2004 gbhaloti bug#3388371 add support to get select string for |
REM |                      BSC dimension levels                             |
REM | 25-JAN-2004 gbhaloti bug#3395623 Removed "ORGANIZATION/               |
REM |         INV ORGANIZATION" from DimLvlList                             |
REM | 11-FEB-2004 ankgoel  bug#3426427 Added parameter p_add_distinct and   |
REM |         included "ORGANIZATION/INV ORGANIZATION" in DimLvlList        |
REM | 12-FEB-2004 ankgoel  bug#3436329 Removed whitespaces from "NAME" in   |
REM |         GET_EDW_SELECT_STRING procedure                               |
REM | 15-FEB-05   ppandey   Enh #4016669, support ID, Value for Autogen DO  |
REM | 27-JUN-05 arhegde enh# 4456833 - SQL + where clause for bis/bsc       |
REM |             dim level relationship from main API that PMV calls       |
REM | 29-JUN-05 arhegde enh# 4456833 - Chgd where clause + filtered "All"   |
REM | 30-JUN-05 arhegde enh# 4456833 - isRecursive in wrap API + BSC dim    |
REM |    level without parent_id is handled                                 |
REM | 13-JUL-05 adrao added condition to check if it is a Periodicity time  |
REM |           dimension object in the API GET_BIS_SELECT_STRING           |
REM | 27-Sep-05 ankgoel  Bug#4625598,4626579 Uptake common API to get dim   |
REM |                    level values                                       |
REM | 19-Oct-05 arhegde enh# 4456833 parent-child whereclause relationship  |
REM | 26-Oct-05 arhegde bug# 4699787 hierarchial where clause changes       |
REM | 26-Oct-05 arhegde bug# 4699787 is_append_where_clause() pre-seeded    |
REM |             relationships do not send back dynamic where clause       |
REM | 17-Nov-05 arhegde bug# 4697700 BSC DOs used in VBR fails.Passed back  |
REM |      code, value and bsc datasource for BSC DOs (get_bis_select_string|
REM | 11-Jan-06 arhegde bug# 4914929 Parent_Id is not passed back from SQL  |
REM |    unless it is a recursive dim object                                |
REM | 02-Feb-06 ashankar Bug#4871663 For BSC dim objects passing user_code  |
REM |                    instead of code                                    |
REM | 10-Feb-06 arhegde bug# 5029245 is_append_where_clause modified to     |
REM |           return true for non-seeded recursive dim object relations   |
REM | 17-Feb-06 arhegde bug# 5041300 Reverting fix for bug# 4871663 due to  |
REM |   other issues such as parameter passing. Will fix bug# 4871663 later |
REM |     09-feb-2007 ashankar Simulation Tree Enhacement 5386112           |
REM |     09-Mar-2007 ashankar Fix for the bug #5920996                     |
REM | 29/03/07   ashankar Bug#5932973 Supporting filters and key items for SM tree |
REM | 12/19/07   bijain   Bug Fix 5945766                                   |
REM +=======================================================================+
*/
--
-- CONSTANTS
   EDW_ACCT_FLEXFIELD           VARCHAR2(200) := 'EDW_GL_ACCT';

Line: 79


   G_DIM_LEVEL_SELECT_INFO_REC  BIS_PMF_GET_DIMLEVELS_PVT.dim_level_select_rec_Type;

Line: 126


PROCEDURE get_select_string (
   p_bis_source            IN VARCHAR2
  ,p_is_relation_recursive IN VARCHAR2
  ,p_is_relationship_found IN VARCHAR2
  ,p_dim_rel_info_rec      IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
  ,p_region_code           IN  ak_regions.region_code%TYPE
  ,x_select_string         OUT NOCOPY VARCHAR2
  ,x_bind_params            OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE-- (attribute_value, attribute_data_type)
  ,x_where_clause          OUT NOCOPY VARCHAR2
  ,x_data_source           OUT NOCOPY VARCHAR2
  ,x_id_name               OUT NOCOPY VARCHAR2
  ,x_value_name            OUT NOCOPY VARCHAR2
  ,x_parent_name           OUT NOCOPY VARCHAR2
  ,x_time_level            OUT NOCOPY VARCHAR2
  ,x_return_status         OUT NOCOPY VARCHAR2
  ,x_msg_count             OUT NOCOPY NUMBER
  ,x_msg_data              OUT NOCOPY VARCHAR2

);

Line: 150


  ,x_select_string         OUT NOCOPY VARCHAR2
  ,x_bind_params           OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
  ,x_data_source           OUT NOCOPY VARCHAR2
  ,x_id_name               OUT NOCOPY VARCHAR2
  ,x_value_name            OUT NOCOPY VARCHAR2
  ,x_parent_name           OUT NOCOPY VARCHAR2
  ,x_time_level            OUT NOCOPY VARCHAR2
  ,x_return_status         OUT NOCOPY VARCHAR2
  ,x_msg_count             OUT NOCOPY NUMBER
  ,x_msg_data              OUT NOCOPY VARCHAR2
);

Line: 169


  ,x_select_string              OUT NOCOPY VARCHAR2
  ,x_data_source                OUT NOCOPY VARCHAR2
  ,x_return_status              OUT NOCOPY VARCHAR2
  ,x_msg_count                  OUT NOCOPY NUMBER
  ,x_msg_data                   OUT NOCOPY VARCHAR2
);

Line: 221


,x_Select_String               OUT NOCOPY VARCHAR2
,x_table_name                  OUT NOCOPY VARCHAR2
,x_id_name                     OUT NOCOPY VARCHAR2
,x_value_name                  OUT NOCOPY VARCHAR2
,x_parent_name                 OUT NOCOPY VARCHAR2
,x_time_level                  OUT NOCOPY VARCHAR2
) RETURN BOOLEAN;

Line: 230


PROCEDURE cache_dim_lvl_Select_info
(p_DimLevelSName               IN  VARCHAR2
,p_Select_String               IN  VARCHAR2
,p_table_name                  IN  VARCHAR2
,p_id_name                     IN  VARCHAR2
,p_value_name                  IN  VARCHAR2
,p_parent_name                 IN  VARCHAR2
,p_time_level                  IN  VARCHAR2
) ;

Line: 260


PROCEDURE GET_DIMLEVEL_SELECT_STRING
(p_DimLevelName         IN     VARCHAR2
,p_add_distinct         IN     VARCHAR2 := 'F'
,x_Select_String        OUT NOCOPY    VARCHAR2
,x_table_name           OUT NOCOPY    VARCHAR2
,x_id_name              OUT NOCOPY    VARCHAR2
,x_value_name           OUT NOCOPY    VARCHAR2
,x_time_level           OUT NOCOPY    VARCHAR2
,x_return_status        OUT NOCOPY    VARCHAR2
,x_msg_count            OUT NOCOPY    NUMBER
,x_msg_data             OUT NOCOPY    VARCHAR2
)
IS
  l_api_name            VARCHAR2(200) :=' GET_DIMLEVEL_SELECT_STRING';

Line: 275


  SELECT source, level_values_view_name
  FROM  bis_levels
  WHERE short_name = p_DimLevelName
  ;

Line: 290


                          ,x_Select_String   => x_Select_String
                          ,x_table_name      => x_table_name
                          ,x_id_name         => x_id_name
                          ,x_value_name      => x_value_name
                    ,x_parent_name     => l_parent_name
                          ,x_time_level      => x_time_level ) ) THEN

    x_return_status     := FND_API.G_RET_STS_SUCCESS ;

Line: 319


      BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
      (p_dim_level_name    => p_DimLevelName
      ,p_source            => l_source
      ,x_table_name        => x_table_name
      ,x_id_name           => x_id_name
      ,x_value_name        => x_value_name
      ,x_edw_select_String => x_Select_String
      ,x_time_level    => x_time_level
      ,x_return_status     => x_return_status
      ,x_msg_count         => x_msg_count
      ,x_msg_data           => x_msg_data
      );

Line: 332


        BIS_PMF_GET_DIMLEVELS_PVT.GET_BIS_SELECT_STRING
     (p_dim_level_name     => p_DimLevelName
     ,p_source            => l_source
     ,p_add_distinct      => p_add_distinct
     ,x_table_name         => x_table_name
     ,x_id_name            => x_id_name
     ,x_value_name         => x_value_name
     ,x_bis_select_string  => x_select_string
     ,x_time_level     => x_time_level
     ,x_return_status     => x_return_status
     ,x_msg_count         => x_msg_count
     ,x_msg_data           => x_msg_data
     );

Line: 349


  cache_dim_lvl_Select_info
    (p_DimLevelSName               => p_DimLevelName
    ,p_Select_String               => x_Select_String
    ,p_table_name                  => x_table_name
    ,p_id_name                     => x_id_name
    ,p_value_name                  => x_value_name
    ,p_parent_name                 => l_parent_name
    ,p_time_level                  => x_time_level
    );

Line: 389


PROCEDURE get_dimlevel_select_string (
   p_DimLevelName         IN     VARCHAR2
  ,p_bis_source           IN     VARCHAR2
  ,x_Select_String        OUT NOCOPY    VARCHAR2
  ,x_table_name           OUT NOCOPY    VARCHAR2
  ,x_id_name              OUT NOCOPY    VARCHAR2
  ,x_value_name           OUT NOCOPY    VARCHAR2
  ,x_time_level           OUT NOCOPY    VARCHAR2
  ,x_return_status        OUT NOCOPY    VARCHAR2
  ,x_msg_count            OUT NOCOPY    NUMBER
  ,x_msg_data             OUT NOCOPY    VARCHAR2
)
IS
  l_parent_name VARCHAR2(100);

Line: 405


  GET_DIMLEVEL_SELECT_STRING (
    p_DimLevelName        => p_DimLevelName
   ,p_bis_source          => p_bis_source
   ,x_Select_String       => x_Select_String
   ,x_table_name          => x_table_name
   ,x_id_name             => x_id_name
   ,x_value_name          => x_value_name
   ,x_parent_name         => l_parent_name
   ,x_time_level          => x_time_level
   ,x_return_status       => x_return_status
   ,x_msg_count           => x_msg_count
   ,x_msg_data            => x_msg_data
);

Line: 422


PROCEDURE GET_DIMLEVEL_SELECT_STRING
(p_DimLevelName         IN     VARCHAR2
,p_bis_source           IN     VARCHAR2
,x_Select_String        OUT NOCOPY    VARCHAR2
,x_table_name           OUT NOCOPY    VARCHAR2
,x_id_name              OUT NOCOPY    VARCHAR2
,x_value_name           OUT NOCOPY    VARCHAR2
,x_parent_name          OUT NOCOPY    VARCHAR2
,x_time_level           OUT NOCOPY    VARCHAR2
,x_return_status        OUT NOCOPY    VARCHAR2
,x_msg_count            OUT NOCOPY    NUMBER
,x_msg_data             OUT NOCOPY    VARCHAR2
)
IS
  l_api_name            VARCHAR2(200) :=' GET_DIMLEVEL_SELECT_STRING';

Line: 438


  SELECT source, level_values_view_name
  FROM  bis_levels
  WHERE short_name = p_DimLevelName
  ;

Line: 451


                          ,x_Select_String   => x_Select_String
                          ,x_table_name      => x_table_name
                          ,x_id_name         => x_id_name
                          ,x_value_name      => x_value_name
              ,x_parent_name     => x_parent_name
                          ,x_time_level      => x_time_level ) ) THEN

    x_return_status     := FND_API.G_RET_STS_SUCCESS ;

Line: 482


      BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
      (p_dim_level_name    => p_DimLevelName
      ,p_source            => l_source
      ,x_table_name        => x_table_name
      ,x_id_name           => x_id_name
      ,x_value_name        => x_value_name
      ,x_edw_select_String => x_Select_String
      ,x_time_level    => x_time_level
      ,x_return_status     => x_return_status
      ,x_msg_count         => x_msg_count
      ,x_msg_data           => x_msg_data
      );

Line: 495


      BIS_PMF_GET_DIMLEVELS_PVT.GET_BIS_SELECT_STRING
     (p_dim_level_name     => p_DimLevelName
     ,p_source             => l_source
     ,x_table_name         => x_table_name
     ,x_id_name            => x_id_name
     ,x_value_name         => x_value_name
     ,x_parent_name        => x_parent_name
     ,x_bis_select_string  => x_select_string
     ,x_time_level     => x_time_level
     ,x_return_status     => x_return_status
     ,x_msg_count         => x_msg_count
     ,x_msg_data           => x_msg_data
     );

Line: 512


  cache_dim_lvl_Select_info
    (p_DimLevelSName               => p_DimLevelName
    ,p_Select_String               => x_Select_String
    ,p_table_name                  => x_table_name
    ,p_id_name                     => x_id_name
    ,p_value_name                  => x_value_name
    ,p_parent_name                 => x_parent_name
    ,p_time_level                  => x_time_level
    );

Line: 560


  l_dim_lvl_sql := 'SELECT dim.DIM_NAME dimshortname '||
                   ' FROM '||
                   ' edw_dimensions_md_v dim, edw_levels_md_v lvl '||
                   ' WHERE '||
                   ' lvl.DIM_ID = dim.DIM_ID AND '||
                   ' lvl.LEVEL_NAME = :p_dim_level_name ';

Line: 582


PROCEDURE GET_EDW_SELECT_STRING
(p_dim_level_name       IN     VARCHAR2
,p_source               IN     VARCHAR2 := NULL -- 2617369
,x_table_name           OUT NOCOPY    VARCHAR2
,x_id_name              OUT NOCOPY    VARCHAR2
,x_value_name           OUT NOCOPY    VARCHAR2
,x_edw_select_String    OUT NOCOPY    VARCHAR2
,x_time_level           OUT NOCOPY    VARCHAR2
,x_return_status        OUT NOCOPY    VARCHAR2
,x_msg_count            OUT NOCOPY    NUMBER
,x_msg_data             OUT NOCOPY    VARCHAR2
)
IS
 l_api_name          VARCHAR2(200) :=' GET_EDW_SELECT_STRING';

Line: 608


 l_select_string     VARCHAR2(32000);

Line: 620


  l_dim_lvl_sql := 'SELECT dim.DIM_NAME dimshortname, dim.DIM_LONG_NAME dimname, dim.DIM_DESCRIPTION dimdesc'||
                   ',lvl.LEVEL_NAME lvlshortname, lvl.LEVEL_LONG_NAME lvlname, lvl.description lvldesc'||
                   ',lvl.LEVEL_PREFIX prefix'||
                   ' FROM '||
                   ' edw_dimensions_md_v dim, edw_levels_md_v lvl '||
                   ' WHERE '||
                   ' lvl.DIM_ID = dim.DIM_ID AND '||
                   ' lvl.LEVEL_NAME = :p_dim_level_name ';

Line: 645


     l_sql_string := 'SELECT '||l_pkkey||' from '||l_tablename|| ' where rownum < 2';

Line: 670


        l_pkkey_sql := ' SELECT level_table_col_name '||
                       ' FROM edw_level_Table_atts_md_v '||
                       ' WHERE key_type=''UK'' AND '||
                       ' upper(level_Table_name) = upper(:l_lvlshortname) AND '||
                       ' level_table_col_name like '''||EDW_PK_KEY||'''';

Line: 685


     l_sql_string := 'SELECT '||l_pkkey||' from '||l_tablename|| ' where rownum < 2';

Line: 724


       l_sql_string   := 'SELECT start_date from '||l_tablename||' where rownum < 2';

Line: 742


       l_sql_string   := 'SELECT end_date from '||l_tablename||' where rownum < 2';

Line: 764


   l_sql_string := 'SELECT '||l_valuename||' from '||l_tablename|| ' where rownum < 2';

Line: 783


   x_edw_select_string := 'select '||l_distinct||' '|| l_pkkey ||'  id , ' ||
                           l_valuename ||'  value '||l_time_columns|| ' FROM '|| l_tablename;

Line: 823


PROCEDURE GET_BIS_SELECT_STRING (
 p_dim_level_name       IN   VARCHAR2
,p_source               IN   VARCHAR2 := NULL -- 2617369
,p_add_distinct         IN   VARCHAR2 := 'F'
,x_table_name           OUT NOCOPY  VARCHAR2
,x_id_name              OUT NOCOPY  VARCHAR2
,x_value_name           OUT NOCOPY  VARCHAR2
,x_bis_select_string    OUT NOCOPY  VARCHAR2
,x_time_level           OUT NOCOPY  VARCHAR2
,x_return_status        OUT NOCOPY  VARCHAR2
,x_msg_count            OUT NOCOPY  NUMBER
,x_msg_data             OUT NOCOPY  VARCHAR2
)
IS
  l_parent_name VARCHAR2(100);

Line: 840


GET_BIS_SELECT_STRING (
   p_dim_level_name     =>  p_dim_level_name
  ,p_source             =>  p_source
  ,p_add_distinct       =>  p_add_distinct
  ,x_table_name         =>  x_table_name
  ,x_id_name            =>  x_id_name
  ,x_value_name         =>  x_value_name
  ,x_parent_name        =>  l_parent_name
  ,x_bis_select_string  =>  x_bis_select_string
  ,x_time_level         =>  x_time_level
  ,x_return_status      =>  x_return_status
  ,x_msg_count          =>  x_msg_count
  ,x_msg_data           =>  x_msg_data
);

Line: 859


PROCEDURE GET_BIS_SELECT_STRING
(p_dim_level_name       IN   VARCHAR2
,p_source               IN   VARCHAR2 := NULL -- 2617369
,p_add_distinct         IN   VARCHAR2 := 'F'
,x_table_name           OUT NOCOPY  VARCHAR2
,x_id_name              OUT NOCOPY  VARCHAR2
,x_value_name           OUT NOCOPY  VARCHAR2
,x_parent_name          OUT NOCOPY VARCHAR2
,x_bis_select_string    OUT NOCOPY  VARCHAR2
,x_time_level           OUT NOCOPY  VARCHAR2
,x_return_status        OUT NOCOPY  VARCHAR2
,x_msg_count            OUT NOCOPY  NUMBER
,x_msg_data             OUT NOCOPY  VARCHAR2
)
IS
  CURSOR c_dimlvls IS
  SELECT lvl.short_name, dim.short_name, lvl.level_values_view_name, bscdl.source, bscdl.level_view_name
  FROM   bis_levels lvl, bis_dimensions dim, bsc_sys_dim_levels_b bscdl
  WHERE  lvl.dimension_id = dim.dimension_id AND
         (lvl.short_name = p_dim_level_name AND p_dim_level_name IS NOT NULL)
         AND bscdl.short_name = lvl.short_name
  ;

Line: 885


  l_Api_name              VARCHAR2(200) := 'GET_BIS_SELECT_STRING';

Line: 908


    x_bis_select_string := ' select -1 Id, '
                   || ''''
                   || BIS_UTILITIES_PVT.GET_FND_MESSAGE( 'BIS_ALL_VALUE_ROLLING' )
                   || ''''
                   || ' Value, sysdate start_date, sysdate end_date from dual ' ;

Line: 942


      x_bis_select_string := 'SELECT DISTINCT id , value '|| l_time_columns ||' FROM ' || l_levelvalueview;

Line: 945


        x_bis_select_string := 'SELECT id , value '|| l_time_columns ||' FROM ' || l_levelvalueview;

Line: 950


        x_bis_select_string := 'SELECT code , name '|| l_time_columns ||' FROM ' || l_bscLevelViewName;

Line: 958


      x_bis_select_string := 'SELECT id , value , set_of_books_id '|| l_time_columns ||' FROM ' ||
                 l_levelvalueview;

Line: 967


      x_bis_select_string := 'SELECT id , value , set_of_books_id '|| l_time_columns ||' FROM ' ||
                 l_levelvalueview;

Line: 1022


,x_select_String           OUT NOCOPY  VARCHAR2
,x_table_name              OUT NOCOPY  VARCHAR2
,x_value_name              OUT NOCOPY  VARCHAR2
,x_id_name                 OUT NOCOPY  VARCHAR2
,x_level_name              OUT NOCOPY  VARCHAR2
,x_description             OUT NOCOPY  VARCHAR2
,x_return_status           OUT NOCOPY  VARCHAR2
,x_msg_Count               OUT NOCOPY  NUMBER
,x_msg_data                OUT NOCOPY  VARCHAR2
)
IS
  l_api_name            VARCHAR2(200) :=' GET_DIMLEVEL_SELECT_STRING';

Line: 1035


  SELECT source, name,description , short_name, level_values_view_name
  FROM  bis_levels_vl
  WHERE level_id = p_bis_dimlevel_id
  ;

Line: 1059


      BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
      (p_dim_level_name    => l_Short_name
      ,p_source            => l_source
      ,x_table_name        => x_table_name
      ,x_id_name           => x_id_name
      ,x_value_name        => x_value_name
      ,x_edw_select_String => x_Select_String
      ,x_time_level        => l_time_level
      ,x_return_status     => x_return_status
      ,x_msg_count         => x_msg_count
      ,x_msg_data           => x_msg_data
      );

Line: 1072


      BIS_PMF_GET_DIMLEVELS_PVT.GET_BIS_SELECT_STRING
     (p_dim_level_name     => l_short_name
     ,p_source             => l_source
     ,x_table_name         => x_table_name
     ,x_id_name            => x_id_name
     ,x_value_name         => x_value_name
     ,x_bis_select_string  => x_select_string
     ,x_time_level        => l_time_level
     ,x_return_status     => x_return_status
     ,x_msg_count         => x_msg_count
     ,x_msg_data           => x_msg_data
     );

Line: 1135


PROCEDURE cache_dim_lvl_Select_info
(p_DimLevelSName               IN  VARCHAR2
,p_Select_String               IN  VARCHAR2
,p_table_name                  IN  VARCHAR2
,p_id_name                     IN  VARCHAR2
,p_value_name                  IN  VARCHAR2
,p_parent_name                 IN  VARCHAR2
,p_time_level                  IN  VARCHAR2
) IS
BEGIN

  -- Store the values into the Global variable
  G_DIM_LEVEL_SELECT_INFO_REC.Dim_level_sname      := p_DimLevelSName ;

Line: 1148


  G_DIM_LEVEL_SELECT_INFO_REC.Table_name           := p_table_name ;

Line: 1149


  G_DIM_LEVEL_SELECT_INFO_REC.Id_name              := p_id_name ;

Line: 1150


  G_DIM_LEVEL_SELECT_INFO_REC.Value_name           := p_value_name ;

Line: 1151


  G_DIM_LEVEL_SELECT_INFO_REC.parent_name          := p_parent_name ;

Line: 1152


  G_DIM_LEVEL_SELECT_INFO_REC.Select_String        := p_select_String ;

Line: 1153


  G_DIM_LEVEL_SELECT_INFO_REC.Time_level           := p_time_level ;

Line: 1158


END cache_dim_lvl_Select_info;

Line: 1164


,x_Select_String               OUT NOCOPY VARCHAR2
,x_table_name                  OUT NOCOPY VARCHAR2
,x_id_name                     OUT NOCOPY VARCHAR2
,x_value_name                  OUT NOCOPY VARCHAR2
,x_parent_name                 OUT NOCOPY VARCHAR2
,x_time_level                  OUT NOCOPY VARCHAR2
) RETURN BOOLEAN IS
BEGIN

  IF ( UPPER(G_DIM_LEVEL_SELECT_INFO_REC.Dim_level_sname) = UPPER(p_DimLevelSName) ) THEN
    x_table_name        := G_DIM_LEVEL_SELECT_INFO_REC.Table_name    ;

Line: 1175


    x_id_name           := G_DIM_LEVEL_SELECT_INFO_REC.Id_name       ;

Line: 1176


    x_value_name        := G_DIM_LEVEL_SELECT_INFO_REC.Value_name    ;

Line: 1177


    x_parent_name       := G_DIM_LEVEL_SELECT_INFO_REC.Parent_name   ;

Line: 1178


    x_Select_String     := G_DIM_LEVEL_SELECT_INFO_REC.Select_String ;

Line: 1179


    x_time_level        := G_DIM_LEVEL_SELECT_INFO_REC.Time_level    ;

Line: 1195


PROCEDURE get_dimlevel_select_wrap (
   p_dimLevel              IN VARCHAR2
  ,p_paramlist             IN VARCHAR2
  ,p_bis_source            IN VARCHAR2
  ,p_region_code           IN  ak_regions.region_code%TYPE
  ,x_select_string         OUT NOCOPY VARCHAR2
  ,x_table_name            OUT NOCOPY VARCHAR2
  ,x_where_clause          OUT NOCOPY VARCHAR2
  ,x_bind_param_string     OUT NOCOPY VARCHAR2
  ,x_id_name               OUT NOCOPY VARCHAR2
  ,x_value_name            OUT NOCOPY VARCHAR2
  ,x_parent_name           OUT NOCOPY VARCHAR2
  ,x_time_level            OUT NOCOPY VARCHAR2
  ,x_is_relation_recursive OUT NOCOPY VARCHAR2
  ,x_return_status         OUT NOCOPY VARCHAR2
  ,x_msg_count             OUT NOCOPY NUMBER
  ,x_msg_data              OUT NOCOPY VARCHAR2
)
IS
  l_dimlevelrec           BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_rec_Type;

Line: 1263


  get_dimlevel_select_string(
    p_dimlevel_rec          => l_dimlevelrec
   ,p_param_lists_tbl       => l_dimleveltbl
   ,p_bis_source            => p_bis_source
   ,x_select_string         => x_select_string
   ,x_table_name            => x_table_name
   ,x_where_clause          => x_where_clause
   ,x_bind_params           => l_bind_params
   ,x_id_name               => x_id_name
   ,x_value_name            => x_value_name
   ,x_parent_name           => x_parent_name
   ,x_time_level            => x_time_level
   ,x_is_relation_recursive => x_is_relation_recursive
   ,x_return_status         => x_return_status
   ,x_msg_count             => x_msg_count
   ,x_msg_data              => x_msg_data
   ,p_region_code           => p_region_code
  );

Line: 1298


END GET_DIMLEVEL_SELECT_WRAP;

Line: 1302


PROCEDURE get_dimlevel_select_string(
   p_dimlevel_rec          IN  BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_rec_Type
  ,p_param_lists_tbl       IN  BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_tbl_Type
  ,p_bis_source            IN  bis_levels.source%TYPE := NULL
  ,p_region_code           IN  ak_regions.region_code%TYPE
  ,x_select_string         OUT NOCOPY VARCHAR2
  ,x_table_name            OUT NOCOPY VARCHAR2
  ,x_where_clause          OUT NOCOPY VARCHAR2
  ,x_bind_params           OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
  ,x_id_name               OUT NOCOPY VARCHAR2
  ,x_value_name            OUT NOCOPY VARCHAR2
  ,x_parent_name           OUT NOCOPY VARCHAR2
  ,x_time_level            OUT NOCOPY VARCHAR2
  ,x_is_relation_recursive OUT NOCOPY VARCHAR2
  ,x_return_status         OUT NOCOPY VARCHAR2
  ,x_msg_count             OUT NOCOPY NUMBER
  ,x_msg_data              OUT NOCOPY VARCHAR2
)
IS
  l_bis_source bis_levels.source%TYPE;

Line: 1350


       get_dimlevel_select_string(
         p_DimLevelName   => p_dimlevel_rec.dimension_level_short_name
        ,p_bis_source     => l_bis_source
        ,x_Select_String  => x_select_string
        ,x_table_name     => x_table_name
        ,x_id_name        => x_id_name
        ,x_value_name     => x_value_name
        ,x_parent_name    => x_parent_name
        ,x_time_level     => x_time_level
        ,x_return_status  => x_return_status
        ,x_msg_count      => x_msg_count
        ,x_msg_data       => x_msg_data
      );

Line: 1373


        ,x_select_string     => x_select_string
        ,x_data_source       => x_table_name
        ,x_return_status     => x_return_status
        ,x_msg_count         => x_msg_count
        ,x_msg_data          => x_msg_data
        ,p_region_code       => p_region_code
      );

Line: 1387


    get_select_string(
      p_bis_source            => l_bis_source
     ,p_is_relation_recursive => x_is_relation_recursive
     ,p_is_relationship_found => l_is_relationship_found
     ,p_dim_rel_info_rec      => l_dim_rel_info_rec
     ,x_select_string         => x_select_string
     ,x_bind_params           => x_bind_params-- (attribute_value, attribute_data_type)
     ,x_where_clause          => x_where_clause
     ,x_data_source           => x_table_name
     ,x_id_name               => x_id_name
     ,x_value_name            => x_value_name
     ,x_parent_name           => x_parent_name
     ,x_time_level            => x_time_level
     ,x_return_status         => x_return_status
     ,x_msg_count             => x_msg_count
     ,x_msg_data              => x_msg_data
     ,p_region_code           => p_region_code

    );

Line: 1416


END get_dimlevel_select_string;

Line: 1431


PROCEDURE get_select_string (
   p_bis_source            IN VARCHAR2
  ,p_is_relation_recursive IN VARCHAR2
  ,p_is_relationship_found IN VARCHAR2
  ,p_dim_rel_info_rec      IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
  ,p_region_code           IN  ak_regions.region_code%TYPE
  ,x_select_string         OUT NOCOPY VARCHAR2
  ,x_bind_params           OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE-- (attribute_value, attribute_data_type)
  ,x_where_clause          OUT NOCOPY VARCHAR2
  ,x_data_source           OUT NOCOPY VARCHAR2
  ,x_id_name               OUT NOCOPY VARCHAR2
  ,x_value_name            OUT NOCOPY VARCHAR2
  ,x_parent_name           OUT NOCOPY VARCHAR2
  ,x_time_level            OUT NOCOPY VARCHAR2
  ,x_return_status         OUT NOCOPY VARCHAR2
  ,x_msg_count             OUT NOCOPY NUMBER
  ,x_msg_data              OUT NOCOPY VARCHAR2
)
IS
  l_is_pmf_bsc_source BSC_SYS_DIM_LEVELS_B.SOURCE%TYPE;

Line: 1461


      ,x_select_string => x_select_string
      ,x_bind_params => x_bind_params
      ,x_data_source => x_data_source
      ,x_id_name => x_id_name
      ,x_value_name => x_value_name
      ,x_parent_name => x_parent_name
      ,x_time_level => x_time_level
      ,x_return_status => x_return_status
      ,x_msg_count => x_msg_count
      ,x_msg_data => x_msg_data
    );

Line: 1478


      ,x_select_string => x_select_string
      ,x_data_source => x_data_source
      ,x_return_status => x_return_status
      ,x_msg_count => x_msg_count
      ,x_msg_data => x_msg_data
      ,p_region_code => p_region_code
    );

Line: 1496


                              ' OR ( ' || x_id_name || ' IN (SELECT ' || x_parent_name || ' FROM ' || x_data_source || ' WHERE ' || x_id_name || ' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}))))';

Line: 1515


END get_select_string;

Line: 1522


  ,x_select_string         OUT NOCOPY VARCHAR2
  ,x_bind_params           OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
  ,x_data_source           OUT NOCOPY VARCHAR2
  ,x_id_name               OUT NOCOPY VARCHAR2
  ,x_value_name            OUT NOCOPY VARCHAR2
  ,x_parent_name           OUT NOCOPY VARCHAR2
  ,x_time_level            OUT NOCOPY VARCHAR2
  ,x_return_status         OUT NOCOPY VARCHAR2
  ,x_msg_count             OUT NOCOPY NUMBER
  ,x_msg_data              OUT NOCOPY VARCHAR2
)
IS
  l_data_source BIS_LEVELS.level_values_view_name%TYPE;

Line: 1541


  l_relation_col_in_select      VARCHAR2(300);

Line: 1584


    l_relation_col_in_select := ', ' || l_relation_col || ' as parent_id ';

Line: 1586


  x_select_string := 'SELECT '|| x_Id_Name ||' , '|| x_Value_Name || l_relation_col_in_select || l_time_columns || ' FROM '|| l_data_source;

Line: 1609


  ,x_select_string              OUT NOCOPY VARCHAR2
  ,x_data_source                OUT NOCOPY VARCHAR2
  ,x_return_status              OUT NOCOPY VARCHAR2
  ,x_msg_count                  OUT NOCOPY NUMBER
  ,x_msg_data                   OUT NOCOPY VARCHAR2
)
IS

  l_relation_type           BSC_SYS_DIM_LEVEL_RELS.RELATION_TYPE%TYPE;

Line: 1652


        x_data_source := ' (SELECT a.code id, a.name value, ' || p_dim_rel_info_rec.relation_col || ' parent_id FROM '|| l_child_level_view_name || ' a WHERE ' || l_restrict_all_value || ')';

Line: 1653


        x_select_string := 'SELECT id, value FROM ' || x_data_source;

Line: 1656


        x_data_source := ' (SELECT a.code id, a.name value FROM '|| l_child_level_view_name || ' a WHERE ' || l_restrict_all_value || ')';

Line: 1657


        x_select_string := 'SELECT id, value FROM ' || x_data_source;

Line: 1660


      x_data_source := ' (SELECT a.code id, a.name value, c.'  || l_parent_level_pk_col || ' parent_id FROM ' || l_child_level_view_name
                         || ' a, ' || l_parent_level_view_name || ' b,' || p_dim_rel_info_rec.relation_col || ' c WHERE a.code'
                         || ' = c.' || l_child_level_pk_col || ' AND b.code = ' || 'c.' || l_parent_level_pk_col || ' AND ' || l_restrict_all_value || ')';

Line: 1665


      x_select_string := 'SELECT distinct id, value FROM ' || x_data_source;

Line: 1815


  SELECT created_by INTO l_created_by FROM bis_levels WHERE short_name = p_dim_rel_info_rec.dimension_level_short_name;

Line: 1818


    SELECT created_by INTO l_created_by_parent FROM bis_levels WHERE short_name = p_dim_rel_info_rec.parent_level_short_name;

Line: 1845


    SELECT source
    FROM  bis_levels
    WHERE short_name = cp_dimLevelName ;

Line: 1928


    SELECT level_values_view_name
    FROM  bis_levels
    WHERE short_name = cp_dimLevel_short_name ;

Line: 1932


  l_select_string VARCHAR2(1000);

Line: 1959


    BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
      (p_dim_level_name    => p_Dim_Level_Short_Name
      ,p_source            => p_Source
      ,x_table_name        => x_table_name
      ,x_id_name           => x_id_name
      ,x_value_name        => x_value_name
      ,x_edw_select_String => l_Select_String
      ,x_time_level        => l_time_level
      ,x_return_status     => x_return_status
      ,x_msg_count         => x_msg_count
      ,x_msg_data          => x_msg_data
    );

Line: 2031


    SELECT source
    FROM  bsc_sys_dim_levels_b
    WHERE short_name = cp_dimLevelName ;

Line: 2075


  SELECT level_pk_col
        ,level_view_name
  FROM   bsc_sys_dim_levels_vl
  WHERE  short_name = cp_dimlevelSN;

Line: 2083


  SELECT level_pk_col
        ,level_view_name
  FROM   bsc_kpi_dim_levels_vl
  WHERE  indicator = cp_objId
  AND    level_shortname =cp_dimlevelSN;

Line: 2093


  SELECT indicator
  FROM   bsc_kpis_b
  WHERE  short_name = p_region_code;