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.BSC_PERIODICITIES_PUB SQL Statements

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

Line: 16


REM | 25-AUG-2005 Pradeep    Bug #4570854, on delete current periodicity_id |
REM |                          need to be used for Annually_source          |
REM | 29-AUG-2005 Aditya Rao Fixed Bug#4574115 in API Validate_Periodicity  |
REM | 07-OCT-2005 Aditya Rao Fixed Bug#4655119, enabled corresponding DO    |
REM |                        created for Periodicities                      |
REM | 29-NOV-2005 Krishna Modified for enh#4711274                          |
REM | 29-DEC-2005 Krishna Passsing enabled = false for hidden periodicities |
REM | 07-FEB-2006 ashankar Fix for the bug4695330                           |
REM | 15-FEB-2006 visuri  Fixed bug#4757375 AK check for Delete Periodicity |
REM | 21-MAR-2006 ashankar  Fixed bug#5099465 Modified Validate_Periodicity |
REM +=======================================================================+
*/

G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODICITIES_PUB';

Line: 38


PROCEDURE Update_Annually_Source
( p_Calendar_Id    IN  NUMBER
, p_Periodicity_Id IN  NUMBER
, p_Action         IN  NUMBER
, x_Return_Status  OUT NOCOPY  VARCHAR2
, x_Msg_Count      OUT NOCOPY  NUMBER
, x_Msg_Data       OUT NOCOPY  VARCHAR2
);

Line: 120


    BSC_PERIODICITIES_PUB.Update_Annually_Source (
      p_Calendar_Id     => l_Periodicities_Rec_Type.Calendar_Id
     ,p_Periodicity_Id  => l_Periodicities_Rec_Type.Periodicity_Id
     ,p_Action          => 1  -- Action for new/updated Period.
     ,x_Return_Status   => x_Return_Status
     ,x_Msg_Count       => x_Msg_Count
     ,x_Msg_Data        => x_Msg_Data
    );

Line: 184


        BSC_UPDATE_UTIL.Populate_Calendar_Tables
        ( p_commit         => p_Commit
        , p_calendar_id    => l_Periodicities_Rec_Type.Calendar_Id
        , x_return_status  => x_Return_Status
        , x_msg_count      => x_Msg_Count
        , x_msg_data       => x_Msg_Data
        );

Line: 239


PROCEDURE Update_Periodicity (
  p_Api_Version             IN          NUMBER
 ,p_Commit                  IN          VARCHAR2 := FND_API.G_FALSE
 ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
 ,p_disable_period_val_flag IN          VARCHAR2
 ,x_Return_Status           OUT NOCOPY  VARCHAR2
 ,x_Msg_Count               OUT NOCOPY  NUMBER
 ,x_Msg_Data                OUT NOCOPY  VARCHAR2
) IS
    l_Periodicities_Rec_Type BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;

Line: 257


    SAVEPOINT UpdatePeriodicityPUB;

Line: 269


     ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_UPDATE
     ,x_Return_Status           => x_Return_Status
     ,x_Msg_Count               => x_Msg_Count
     ,x_Msg_Data                => x_Msg_Data
    );

Line: 289


    BSC_PERIODICITIES_PVT.Update_Periodicity (
      p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
     ,p_Commit                  => p_Commit
     ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
     ,x_Structural_Flag         => l_Structural_Flag
     ,x_Return_Status           => x_Return_Status
     ,x_Msg_Count               => x_Msg_Count
     ,x_Msg_Data                => x_Msg_Data
    );

Line: 309


     ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_UPDATE
     ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
     ,p_disable_period_val_flag => p_disable_period_val_flag
     ,x_Return_Status           => x_Return_Status
     ,x_Msg_Count               => x_Msg_Count
     ,x_Msg_Data                => x_Msg_Data
    );

Line: 320


    BSC_PERIODICITIES_PUB.Update_Annually_Source(
      p_Calendar_Id     => l_Periodicities_Rec_Type.Calendar_Id
     ,p_Periodicity_Id  => l_Periodicities_Rec_Type.Periodicity_Id
     ,p_Action          => 1  -- Action for new/updated Period.
     ,x_Return_Status   => x_Return_Status
     ,x_Msg_Count       => x_Msg_Count
     ,x_Msg_Data        => x_Msg_Data
    );

Line: 357


    BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object
    (
            p_Commit                    =>  p_commit
        ,   p_Dim_Obj_Short_Name        =>  l_Periodicities_Rec_Type.Short_Name
        ,   p_Display_Name              =>  l_Dimobj_Name
        ,   p_Application_Id            =>  l_Periodicities_Rec_Type.Application_id
        ,   p_Description               =>  l_Periodicities_Rec_Type.Description
        ,   p_Data_Source               =>  BSC_PERIODS_UTILITY_PKG.C_PMF_DO_TYPE
        ,   p_Source_Table              =>  l_Periodicity_View_Name
        ,   p_Where_Clause              =>  NULL
        ,   p_Comparison_Label_Code     =>  NULL
        ,   p_Table_Column              =>  NULL
        ,   p_Source_Type               =>  BSC_PERIODS_UTILITY_PKG.C_OLTP_DO_TYPE
        ,   p_Maximum_Code_Size         =>  NULL
        ,   p_Maximum_Name_Size         =>  NULL
        ,   p_All_Item_Text             =>  NULL
        ,   p_Comparison_Item_Text      =>  NULL
        ,   p_Prototype_Default_Value   =>  NULL
        ,   p_Dimension_Values_Order    =>  NULL
        ,   p_Comparison_Order          =>  NULL
        ,   p_Assign_Dim_Short_Names    =>  l_Dim_Short_Name
        ,   p_Unassign_Dim_Short_Names  =>  NULL
        ,   p_Dim_Obj_Enabled           =>  l_Dim_Enabled
        ,   x_Return_Status             =>  x_Return_Status
        ,   x_Msg_Count                 =>  x_Msg_Count
        ,   x_Msg_Data                  =>  x_Msg_Data
    );

Line: 404


        BSC_UPDATE_UTIL.Populate_Calendar_Tables
        ( p_commit         => p_Commit
        , p_calendar_id    => l_Periodicities_Rec_Type.Calendar_Id
        , x_return_status  => x_Return_Status
        , x_msg_count      => x_Msg_Count
        , x_msg_data       => x_Msg_Data
        );

Line: 423


        ROLLBACK TO UpdatePeriodicityPUB;

Line: 433


        ROLLBACK TO UpdatePeriodicityPUB;

Line: 443


        ROLLBACK TO UpdatePeriodicityPUB;

Line: 446


            x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Update_Periodicity ';

Line: 448


            x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Update_Periodicity ';

Line: 451


        ROLLBACK TO UpdatePeriodicityPUB;

Line: 454


            x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Update_Periodicity ';

Line: 456


            x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Update_Periodicity ';

Line: 458


END Update_Periodicity;

Line: 463


PROCEDURE Delete_Periodicity (
  p_Api_Version             IN          NUMBER
 ,p_Commit                  IN          VARCHAR2
 ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
 ,x_Return_Status           OUT NOCOPY  VARCHAR2
 ,x_Msg_Count               OUT NOCOPY  NUMBER
 ,x_Msg_Data                OUT NOCOPY  VARCHAR2
) IS
    l_Dim_Object_SN  BSC_SYS_PERIODICITIES.SHORT_NAME%TYPE;

Line: 478


    CURSOR c_Delete_View IS
        SELECT L.LEVEL_VALUES_VIEW_NAME
        FROM   BIS_LEVELS L
        WHERE  L.SHORT_NAME = l_Dim_Object_SN
        AND    L.LEVEL_VALUES_VIEW_NAME IS NOT NULL;

Line: 484


    SAVEPOINT DeletePeriodicityPUB;

Line: 495


     ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_DELETE
     ,x_Return_Status           => x_Return_Status
     ,x_Msg_Count               => x_Msg_Count
     ,x_Msg_Data                => x_Msg_Data
    );

Line: 509


      SELECT c.name
      INTO   l_dim_name
      FROM   bsc_sys_calendars_vl c
      WHERE  c.short_name = l_Dimension_SN;

Line: 514


      SELECT c.name
      INTO   l_dim_obj_name
      FROM   bsc_sys_periodicities_vl c
      WHERE  c.short_name = l_Dim_Object_SN;

Line: 527


    BSC_PERIODICITIES_PVT.Delete_Periodicity (
      p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
     ,p_Commit                  => p_Commit
     ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
     ,x_Return_Status           => x_Return_Status
     ,x_Msg_Count               => x_Msg_Count
     ,x_Msg_Data                => x_Msg_Data
    );

Line: 539


    BSC_PERIODICITIES_PUB.Update_Annually_Source(
      p_Calendar_Id     => p_Periodicities_Rec_Type.Calendar_Id
     ,p_Periodicity_Id  => p_Periodicities_Rec_Type.Periodicity_Id
     ,p_Action          => 2  -- Action for Period Delete.
     ,x_Return_Status   => x_Return_Status
     ,x_Msg_Count       => x_Msg_Count
     ,x_Msg_Data        => x_Msg_Data
    );

Line: 569


    FOR cDelView IN c_Delete_View LOOP
        l_Periodicity_View_Name := cDelView.LEVEL_VALUES_VIEW_NAME;

Line: 573


    BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object
    (       p_commit              => p_commit
        ,   p_dim_obj_short_name  => l_Dim_Object_SN
        ,   x_return_status       => x_return_status
        ,   x_msg_count           => x_msg_count
        ,   x_msg_data            => x_msg_data
    );

Line: 607


        ROLLBACK TO DeletePeriodicityPUB;

Line: 617


        ROLLBACK TO DeletePeriodicityPUB;

Line: 627


        ROLLBACK TO DeletePeriodicityPUB;

Line: 630


            x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Delete_Periodicity ';

Line: 632


            x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Delete_Periodicity ';

Line: 635


        ROLLBACK TO DeletePeriodicityPUB;

Line: 638


            x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Delete_Periodicity ';

Line: 640


            x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Delete_Periodicity ';

Line: 642


END Delete_Periodicity;

Line: 678


    SELECT K.NAME
    FROM   BSC_KPIS_VL K
         , BSC_KPI_PERIODICITIES P
    WHERE  P.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id
    AND    K.INDICATOR = P.INDICATOR;

Line: 698


    IF ((p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_CREATE) OR (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_UPDATE)) THEN

        IF (p_Periodicities_Rec_Type.Calendar_Id IS NULL) THEN
            FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_ID_NULL');

Line: 750


            SELECT COUNT(1) INTO l_Count
            FROM   BSC_SYS_PERIODICITIES P
            WHERE  P.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_id;

Line: 777


        IF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN

            SELECT COUNT(1) INTO l_Count
            FROM   BSC_SYS_PERIODICITIES_VL P
            WHERE  P.NAME            = p_Periodicities_Rec_Type.Name
            AND    P.CALENDAR_ID     = p_Periodicities_Rec_Type.Calendar_Id
            AND    P.PERIODICITY_ID <> p_Periodicities_Rec_Type.Periodicity_id;

Line: 799


            SELECT COUNT(1) INTO l_Count
            FROM   BSC_SYS_PERIODICITIES B
            WHERE  B.PERIODICITY_ID   = p_Periodicities_Rec_Type.Periodicity_id
            AND    B.CALENDAR_ID      = p_Periodicities_Rec_Type.Calendar_Id
            AND    B.PERIODICITY_TYPE <> 0;

Line: 823


    ELSIF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_DELETE) THEN

        -- You cannot delete a BASE Periodicity
        SELECT COUNT(1) INTO l_Count
        FROM   BSC_SYS_PERIODICITIES B
        WHERE  B.PERIODICITY_ID   = p_Periodicities_Rec_Type.Periodicity_id
        AND    B.CALENDAR_ID      = p_Periodicities_Rec_Type.Calendar_Id
        AND    B.PERIODICITY_TYPE <> 0;

Line: 843


            SELECT COUNT(1) INTO l_Count
            FROM   BSC_SYS_PERIODICITIES B
            WHERE  TRIM(B.SOURCE) = TO_CHAR(p_Periodicities_Rec_Type.Periodicity_id);

Line: 857


        SELECT COUNT(1) INTO l_Count
        FROM   BSC_KPI_PERIODICITIES P
        WHERE  P.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_id;

Line: 1033


    IF (x_Periodicities_Rec_Type.Last_Updated_By IS NULL) THEN
        x_Periodicities_Rec_Type.Last_Updated_By := FND_GLOBAL.USER_ID;

Line: 1037


    IF (x_Periodicities_Rec_Type.Last_Update_Date IS NULL) THEN
        x_Periodicities_Rec_Type.Last_Update_Date := SYSDATE;

Line: 1041


    IF (x_Periodicities_Rec_Type.Last_Update_Login IS NULL) THEN
        x_Periodicities_Rec_Type.Last_Update_Login := FND_GLOBAL.LOGIN_ID;

Line: 1091


    IF (l_Period_Record.Last_Updated_By IS NULL) THEN
        l_Period_Record.Last_Updated_By := FND_GLOBAL.USER_ID;

Line: 1095


    IF (l_Period_Record.Last_Update_Date IS NULL) THEN
        l_Period_Record.Last_Update_Date := SYSDATE;

Line: 1099


    IF (l_Period_Record.Last_Update_Login IS NULL) THEN
        l_Period_Record.Last_Update_Login := FND_GLOBAL.LOGIN_ID;

Line: 1117


    ELSIF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
        BSC_PERIODS_PUB.Update_Periods
        (
          p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
        , p_Commit                  => p_Commit
        , p_Period_Record           => l_Period_Record
        , x_Structual_Change        => l_Struct_Flag
        , p_disable_period_val_flag => p_disable_period_val_flag
        , x_Return_Status           => x_Return_Status
        , x_Msg_Count               => x_Msg_Count
        , x_Msg_Data                => x_Msg_Data
        );

Line: 1236


PROCEDURE Update_Annually_Source
( p_Calendar_Id    IN  NUMBER
, p_Periodicity_Id IN  NUMBER
, p_Action         IN  NUMBER
, x_Return_Status  OUT NOCOPY  VARCHAR2
, x_Msg_Count      OUT NOCOPY  NUMBER
, x_Msg_Data       OUT NOCOPY  VARCHAR2
)IS
BEGIN
  FND_MSG_PUB.Initialize;

Line: 1247


  BSC_UPDATE_UTIL.Update_AnualPeriodicity_Src
  ( x_calendar_id     => p_Calendar_Id
  , x_periodicity_id  => p_Periodicity_Id
  , x_action          => p_Action
  );

Line: 1253


  IF(BSC_PERIODS_UTILITY_PKG.Check_Error_Message('BSC_UPDATE_UTIL.UpdAnualPeriodicitySrc')) THEN
    FND_MESSAGE.SET_NAME('BSC','BSC_ERROR_UPDATE_ANUAL_SOURCE');

Line: 1281


      x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';

Line: 1283


      x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';

Line: 1288


      x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';

Line: 1290


      x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';

Line: 1292


END Update_Annually_Source;

Line: 1315


      SELECT K.NAME, K.INDICATOR
      FROM   BSc_KPI_PERIODICITIES P
            ,BSC_KPIS_VL K
      WHERE K.INDICATOR = P.INDICATOR
      AND   K.PROTOTYPE_FLAG NOT IN (1, 2, 3)
      AND   P.PERIODICITY_ID = p_Periodicity_Id;

Line: 1399


 PROCEDURE Update_Periodicity (
   p_Api_Version             IN          NUMBER
  ,p_Commit                  IN          VARCHAR2 := FND_API.G_FALSE
  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
  ,x_Return_Status           OUT NOCOPY  VARCHAR2
  ,x_Msg_Count               OUT NOCOPY  NUMBER
  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
 ) IS
 BEGIN
    BSC_PERIODICITIES_PUB.Update_Periodicity (
      p_Api_Version             =>  p_Api_Version
     ,p_Commit                  =>  p_Commit
     ,p_Periodicities_Rec_Type  =>  p_Periodicities_Rec_Type
     ,p_disable_period_val_flag =>  FND_API.G_FALSE
     ,x_Return_Status           =>  x_Return_Status
     ,x_Msg_Count               =>  x_Msg_Count
     ,x_Msg_Data                =>  x_Msg_Data
 );

Line: 1418


END Update_Periodicity;

Line: 1432


  UPDATE bsc_sys_periodicities_tl
  SET    name = NVL(p_Periodicities_Rec_Type.name,name)
      ,  source_lang = USERENV('LANG')
  WHERE  USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
  AND    periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id;

Line: 1459


    UPDATE bsc_sys_periodicities
    SET num_of_periods = p_Periodicities_Rec_Type.Num_Of_Periods,
        source = p_Periodicities_Rec_Type.Source,
        num_of_subperiods = p_Periodicities_Rec_Type.Num_Of_Subperiods,
        period_col_name = p_Periodicities_Rec_Type.Period_Col_Name,
        subperiod_col_name = p_Periodicities_Rec_Type.Subperiod_Col_Name,
        yearly_flag = p_Periodicities_Rec_Type.Yearly_Flag,
        edw_flag = p_Periodicities_Rec_Type.Edw_Flag,
        calendar_id = p_Periodicities_Rec_Type.Calendar_Id,
        custom_code = p_Periodicities_Rec_Type.Custom_Code,
        db_column_name = p_Periodicities_Rec_Type.Db_Column_Name,
        periodicity_type = p_Periodicities_Rec_Type.Periodicity_Type
    WHERE periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id;

Line: 1474


      INSERT INTO bsc_sys_periodicities(
        periodicity_id,
        num_of_periods,
        source,
        num_of_subperiods,
        period_col_name,
        subperiod_col_name,
        yearly_flag,
        edw_flag,
        calendar_id,
        custom_code,
        db_column_name,
        periodicity_type)
      VALUES(
        p_Periodicities_Rec_Type.Periodicity_Id,
        p_Periodicities_Rec_Type.Num_Of_Periods,
        p_Periodicities_Rec_Type.Source,
        p_Periodicities_Rec_Type.Num_Of_Subperiods,
        p_Periodicities_Rec_Type.Period_Col_Name,
        p_Periodicities_Rec_Type.Subperiod_Col_Name,
        p_Periodicities_Rec_Type.Yearly_Flag,
        p_Periodicities_Rec_Type.Edw_Flag,
        p_Periodicities_Rec_Type.Calendar_Id,
        p_Periodicities_Rec_Type.Custom_Code,
        p_Periodicities_Rec_Type.Db_Column_Name,
        p_Periodicities_Rec_Type.Periodicity_Type
      );

Line: 1503


      SELECT meaning
      INTO   l_name
      FROM   bsc_lookups
      WHERE  lookup_code=p_Periodicities_Rec_Type.Periodicity_Id
      AND    lookup_type = 'BSC_PERIODICITY';

Line: 1513


    UPDATE bsc_sys_periodicities_tl
    SET    name = l_name,
           SOURCE_LANG = userenv('LANG')
    WHERE  periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id
    AND    userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 1520


      INSERT INTO bsc_sys_periodicities_tl (
        PERIODICITY_ID,
        NAME,
        LANGUAGE,
        SOURCE_LANG,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN
      ) SELECT
        p_Periodicities_Rec_Type.Periodicity_Id,
        l_name,
        L.LANGUAGE_CODE,
        USERENV('LANG'),
        FND_GLOBAL.user_id,
        SYSDATE,
        FND_GLOBAL.user_id,
        SYSDATE,
        FND_GLOBAL.user_id
      FROM FND_LANGUAGES L
      WHERE L.INSTALLED_FLAG in ('I', 'B')
      AND NOT EXISTS
        (SELECT NULL
         FROM  bsc_sys_periodicities_tl t
         WHERE periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id
         AND t.LANGUAGE = L.LANGUAGE_CODE);