APPS.BSC_DIM_FILTERS_PVT SQL Statements

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

Line: 34

    SELECT Level_View_Name
    INTO   l_Level_View_Nane
    FROM   BSC_SYS_FILTERS_VIEWS
    WHERE  Source_Type    =  BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
    AND    Source_Code    =  p_Tab_Id
    AND    Dim_Level_Id   =  p_Dim_Level_Id;

Line: 64

    SELECT  TF.Dim_Level_Id
    FROM    BSC_SYS_FILTERS_VIEWS TF
    WHERE   TF.Source_Type  =   BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
    AND     TF.Source_Code  =   p_Tab_Id
    AND     TF.Dim_Level_Id NOT IN
            (   SELECT  SL.Dim_Level_Id
                FROM    BSC_TAB_INDICATORS      TI
                      , BSC_KPI_DIM_LEVELS_B    K
                      , BSC_SYS_DIM_LEVELS_B    SL
                WHERE   SL.SOURCE            <> 'PMF'
                AND     TI.Tab_Id            =  p_Tab_Id
                AND     K.INDICATOR          =  TI.INDICATOR
                AND     SL.Level_Table_Name  =  K.Level_Table_Name
            );

Line: 141

      DELETE a Filter metadata AND filter VIEW object
      AND CHECK IF EXISTS ANY  filter FOR a CHILD DIMENSION IN ORDER TO
      deleted OR recreated. (BY now it will be DELETE.  Later will be more intalligent
-------------------------------------------------------------------------------------------------------------------*/
PROCEDURE  Drop_Filter
(       p_Tab_Id            IN      NUMBER
    ,   p_Dim_Level_Id      IN      NUMBER
    ,   x_return_status     OUT NOCOPY     VARCHAR2
    ,   x_msg_count         OUT NOCOPY     NUMBER
    ,   x_msg_data          OUT NOCOPY     VARCHAR2
) IS
    l_Count                     NUMBER;

Line: 160

    SELECT  Dim_Level_Id
    FROM    BSC_SYS_DIM_LEVEL_RELS
    WHERE   Parent_Dim_Level_Id = p_Dim_Level_Id
    AND     Relation_Type       = 1;

Line: 169

      SELECT COUNT(A.DIM_LEVEL_VALUE)
        FROM BSC_SYS_FILTERS A
        WHERE A.SOURCE_TYPE = 1
         AND A.SOURCE_CODE = p_Tab_Id
         AND A.DIM_LEVEL_ID = l_Child_Dim_Level_Id;

Line: 218

        SAVEPOINT BcsFiltersPubDeleteFilterView;

Line: 239

        ROLLBACK TO BcsFiltersPubDeleteFilterView;

Line: 251

        ROLLBACK TO BcsFiltersPubDeleteFilterView;

Line: 263

      DELETE a Filter metadata AND filter VIEW object
-------------------------------------------------------------------------------------------------------------------*/
PROCEDURE  Drop_Filter_Objects
(       p_Tab_Id            IN      NUMBER
    ,   p_Dim_Level_Id      IN      NUMBER
    ,   x_return_status     OUT NOCOPY     VARCHAR2
    ,   x_msg_COUNT         OUT NOCOPY     NUMBER
    ,   x_msg_data          OUT NOCOPY     VARCHAR2
) IS
    l_Count                     NUMBER;

Line: 284

    SAVEPOINT BcsFiltersPvtDeleteFilterView;

Line: 296

        DELETE  FROM BSC_SYS_FILTERS_VIEWS
        WHERE   Source_Type  = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
        AND     Source_Code  = p_Tab_Id
        AND     Dim_Level_Id = p_Dim_Level_Id;

Line: 304

        DELETE  FROM BSC_SYS_FILTERS
        WHERE   Source_Type     =   BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
        AND     Source_Code     =   p_Tab_Id
        AND     Dim_Level_Id    =   p_Dim_Level_Id;

Line: 322

        SELECT  COUNT(OBJECT_NAME)
        INTO    l_Count
        FROM    USER_OBJECTS
        WHERE   OBJECT_NAME = l_Filter_Level_View_Name ;

Line: 346

        ROLLBACK TO BcsFiltersPvtDeleteFilterView;

Line: 355

        ROLLBACK TO BcsFiltersPvtDeleteFilterView;

Line: 383

    SELECT  DISTINCT KD.INDICATOR  --Distinct need it
         ,  SD.Level_Table_Name     SYS_TABLE
         ,  SD.Level_View_Name      SYS_VIEW
         ,  KD.Level_View_Name      KPI_VIEW
         ,  NVL(FV.Level_View_Name, SD.Level_View_Name) NEW_VIEW
    FROM    BSC_TAB_INDICATORS      TI
         ,  BSC_KPI_DIM_LEVELS_B    KD
         ,  BSC_SYS_DIM_LEVELS_B    SD
         ,  (
             SELECT *
             FROM BSC_SYS_FILTERS_VIEWS A
             WHERE A.Source_Type  = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
                AND A.Source_Code = p_Tab_Id
             ) FV
    WHERE   TI.Tab_Id               = p_Tab_Id
    AND     KD.INDICATOR            = TI.INDICATOR
    AND     KD.Level_Table_Name     = SD.Level_Table_Name
    AND     FV.Level_Table_Name(+)        = KD.Level_Table_Name
    AND     (  NVL(FV.Level_View_Name, SD.Level_View_Name) <> KD.Level_View_Name
    --               OR KD.Level_View_Name IS NULL
            );

Line: 408

    SELECT  DISTINCT KD.INDICATOR  --Distinct need it
         ,  SD.Level_Table_Name     SYS_TABLE
         ,  SD.Level_View_Name      SYS_VIEW
         ,  KD.Level_View_Name      KPI_VIEW
    FROM    BSC_TAB_INDICATORS      TI
         ,  BSC_KPI_DIM_LEVELS_B    KD
         ,  BSC_SYS_DIM_LEVELS_B    SD
    WHERE   KD.INDICATOR            = TI.INDICATOR (+)
    AND     TI.Tab_Id               IS NULL
    AND     KD.Level_Table_Name     = SD.Level_Table_Name
    AND     KD.Level_View_Name <> SD.Level_View_Name;

Line: 441

            UPDATE  BSC_KPI_DIM_LEVELS_B
            SET     Level_View_Name     = l_New_View_Name
            WHERE   INDICATOR           = l_indicator
            AND     Level_Table_Name    = l_Sys_Table_Name;

Line: 481

            UPDATE  BSC_KPI_DIM_LEVELS_B
            SET     Level_View_Name     = l_Sys_View_Name
            WHERE   INDICATOR           = l_indicator
            AND     Level_Table_Name    = l_Sys_Table_Name;