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_CALENDAR_PUB SQL Statements

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

Line: 36


PROCEDURE Update_Calendar_Dimension
( p_Api_Version            IN          NUMBER
, p_Commit                 IN          VARCHAR2
, p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
, x_Return_Status          OUT NOCOPY  VARCHAR2
, x_Msg_Count              OUT NOCOPY  NUMBER
, x_Msg_Data               OUT NOCOPY  VARCHAR2
);

Line: 178


PROCEDURE Update_Calendar
( p_Api_Version            IN          NUMBER
, p_Commit                 IN          VARCHAR2
, p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
, x_Return_Status          OUT NOCOPY  VARCHAR2
, x_Msg_Count              OUT NOCOPY  NUMBER
, x_Msg_Data               OUT NOCOPY  VARCHAR2
)IS
l_Calendar_Record BSC_CALENDAR_PUB.Calendar_Type_Record;

Line: 192


  SAVEPOINT UpdateCalendarPubSP;

Line: 196


  SELECT fiscal_year
        ,start_month
        ,start_day
        ,name
  INTO   l_Fiscal_Year
        ,l_Start_Month
        ,l_Start_Day
        ,l_Calendar_Old_Name
  FROM   bsc_sys_calendars_vl
  WHERE  calendar_id = p_Calendar_Record.Calendar_Id;

Line: 218


  , p_Action           => 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: 240


  BSC_CALENDAR_PVT.Update_Calendar
  ( p_Api_Version      => p_Api_Version
  , p_Commit           => p_Commit
  , p_Calendar_Record  => l_Calendar_Record
  , x_Return_Status    => x_Return_Status
  , x_Msg_Count        => x_Msg_Count
  , x_Msg_Data         => x_Msg_Data
  );

Line: 252


  BSC_CALENDAR_PUB.Update_Calendar_Dimension
  ( p_Api_Version      => p_Api_Version
  , p_Commit           => p_Commit
  , p_Calendar_Record  => l_Calendar_Record
  , x_Return_Status    => x_Return_Status
  , x_Msg_Count        => x_Msg_Count
  , x_Msg_Data         => x_Msg_Data
  );

Line: 265


    BSC_CALENDAR_PVT.Update_PeriodNames_In_Calendar
    ( p_Calendar_Id        => p_Calendar_Record.Calendar_Id
    , x_Return_Status      => x_Return_Status
    , x_Msg_Count          => x_Msg_Count
    , x_Msg_Data           => x_Msg_Data
    );

Line: 276


  BSC_CALENDAR_PUB.Update_Calendar_Post_Action
  ( p_Api_Version      => p_Api_Version
  , p_Commit           => p_Commit
  , p_Calendar_Record  => l_Calendar_Record
  , x_Return_Status    => x_Return_Status
  , x_Msg_Count        => x_Msg_Count
  , x_Msg_Data         => x_Msg_Data
  );

Line: 294


    ROLLBACK TO UpdateCalendarPubSP;

Line: 304


    ROLLBACK TO UpdateCalendarPubSP;

Line: 314


    ROLLBACK TO UpdateCalendarPubSP;

Line: 317


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

Line: 319


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

Line: 322


    ROLLBACK TO UpdateCalendarPubSP;

Line: 325


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

Line: 327


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

Line: 329


END Update_Calendar;

Line: 334


PROCEDURE Delete_Calendar
( p_Api_Version            IN          NUMBER
, p_Commit                 IN          VARCHAR2
, p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
, x_Return_Status          OUT NOCOPY  VARCHAR2
, x_Msg_Count              OUT NOCOPY  NUMBER
, x_Msg_Data               OUT NOCOPY  VARCHAR2
)IS
CURSOR C_Periodicity_ShortNames IS
SELECT short_name
FROM   bsc_sys_periodicities
WHERE  calendar_id = p_Calendar_Record.Calendar_Id;

Line: 348


SELECT B.level_values_view_name
FROM   bsc_sys_periodicities P,
       bis_levels        B
WHERE  b.short_name = P.short_name
AND    P.calendar_id = p_Calendar_Record.Calendar_Id
AND    P.db_column_name IS NOT NULL;

Line: 356


SELECT short_name
FROM   bsc_sys_calendars_b
WHERE  calendar_id = p_Calendar_Record.Calendar_Id;

Line: 367


  SAVEPOINT DeleteCalendarPubSP;

Line: 375


  , p_Action           => 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: 417


    BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object
    ( p_commit              => p_commit
    , p_dim_obj_short_name  => CPER.short_name
    , x_return_status       => x_Return_Status
    , x_msg_count           => x_Msg_Count
    , x_msg_data            => x_Msg_Data
    );

Line: 429


  BSC_BIS_DIMENSION_PUB.Delete_Dimension
  ( p_commit          => p_commit
  , p_dim_short_name  => l_Calendar_ShortName
  , x_return_status   => x_Return_Status
  , x_msg_count       => x_Msg_Count
  , x_msg_data        => x_Msg_Data
  );

Line: 440


  BSC_CALENDAR_PVT.Delete_Calendar
  ( p_Api_Version      => p_Api_Version
  , p_Commit           => p_Commit
  , p_Calendar_Record  => p_Calendar_Record
  , x_Return_Status    => x_Return_Status
  , x_Msg_Count        => x_Msg_Count
  , x_Msg_Data         => x_Msg_Data
  );

Line: 473


    ROLLBACK TO DeleteCalendarPubSP;

Line: 483


    ROLLBACK TO DeleteCalendarPubSP;

Line: 493


    ROLLBACK TO DeleteCalendarPubSP;

Line: 496


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

Line: 498


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

Line: 501


    ROLLBACK TO DeleteCalendarPubSP;

Line: 504


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

Line: 506


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

Line: 508


END Delete_Calendar;

Line: 526


SELECT k.name
FROM   bsc_kpis_vl K
WHERE  K.calendar_id = p_Calendar_Record.Calendar_Id;

Line: 535


  IF(p_Action = BSC_PERIODS_UTILITY_PKG.C_CREATE OR p_Action = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
    IF(BIS_UTILITIES_PVT.Value_Missing_Or_Null(p_Calendar_Record.Name) = FND_API.G_TRUE) THEN
      FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_NAME_NULL');

Line: 549


    SELECT COUNT(1)
    INTO   l_count
    FROM   bsc_sys_calendars_b
    WHERE  TRIM(calendar_id) = TRIM(p_Calendar_Record.Calendar_Id);

Line: 559


    SELECT COUNT(1)
    INTO   l_count
    FROM   bsc_sys_calendars_vl
    WHERE  TRIM(NAME) = TRIM(p_Calendar_Record.Name);

Line: 570


    SELECT COUNT(1)
    INTO   l_count
    FROM   bsc_sys_calendars_b
    WHERE  TRIM(short_name) = TRIM(p_Calendar_Record.Dim_Short_Name);

Line: 580


  ELSIF(p_Action = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
    IF(p_Calendar_Record.Fiscal_Year IS NULL) THEN
      FND_MESSAGE.SET_NAME('BSC','BSC_FISCAL_YEAR_NULL');

Line: 587


    SELECT COUNT(1)
    INTO   l_count
    FROM   BSC_SYS_CALENDARS_B
    WHERE  CALENDAR_ID <> p_Calendar_Record.Calendar_Id
    AND    TRIM(short_name) = TRIM(p_Calendar_Record.Dim_Short_Name);

Line: 597


  ELSIF(p_Action = BSC_PERIODS_UTILITY_PKG.C_DELETE ) THEN

    l_count := 0;

Line: 600


    SELECT COUNT(1)
    INTO   l_count
    FROM   bsc_sys_calendars_b
    WHERE  calendar_id = p_Calendar_Record.Calendar_Id;

Line: 612


    SELECT COUNT(K.INDICATOR)
    INTO   l_count
    FROM   BSC_KPI_PERIODICITIES K,
           BSC_SYS_PERIODICITIES S
    WHERE  S.PERIODICITY_ID = K.PERIODICITY_ID
    AND    S.CALENDAR_ID = p_Calendar_Record.Calendar_Id;

Line: 619


    SELECT name
    INTO   l_Calendar_Name
    FROM   bsc_sys_calendars_vl
    WHERE  calendar_id = p_Calendar_Record.Calendar_Id;

Line: 696


 SELECT  calendar_id
       , edw_flag
       , edw_calendar_id
       , edw_calendar_type_id
       , fiscal_year
       , fiscal_change
       , range_yr_mod
       , current_year
       , start_month
       , start_day
       , name
       , help
       , created_by
       , creation_date
       , last_updated_by
       , last_update_date
       , last_update_login
  INTO   x_Calendar_Record.Calendar_Id
       , x_Calendar_Record.Edw_Flag
       , x_Calendar_Record.Edw_Calendar_Id
       , x_Calendar_Record.Edw_Calendar_Type_Id
       , x_Calendar_Record.Fiscal_Year
       , x_Calendar_Record.Fiscal_Change
       , x_Calendar_Record.Range_Yr_Mod
       , x_Calendar_Record.Current_Year
       , x_Calendar_Record.Start_Month
       , x_Calendar_Record.Start_Day
       , x_Calendar_Record.Name
       , x_Calendar_Record.Help
       , x_Calendar_Record.Created_By
       , x_Calendar_Record.Creation_Date
       , x_Calendar_Record.Last_Updated_By
       , x_Calendar_Record.Last_Update_Date
       , x_Calendar_Record.Last_Update_Login
  FROM  bsc_sys_calendars_vl
  WHERE calendar_id = p_Calendar_Record.Calendar_Id;

Line: 759


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

Line: 762


      x_Calendar_Record.Last_Update_Date := p_Calendar_Record.Last_Update_Date;

Line: 765


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

Line: 768


    x_Calendar_Record.Last_Updated_By := p_Calendar_Record.Last_Updated_By;

Line: 771


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

Line: 774


    x_Calendar_Record.Last_Update_Login := p_Calendar_Record.Last_Update_Login;

Line: 825


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

Line: 1006


PROCEDURE Update_Calendar_Post_Action
( p_Api_Version            IN          NUMBER
, p_Commit                 IN          VARCHAR2
, p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
, x_Return_Status          OUT NOCOPY  VARCHAR2
, x_Msg_Count              OUT NOCOPY  NUMBER
, x_Msg_Data               OUT NOCOPY  VARCHAR2
)IS
l_System_Stage   bsc_sys_init.property_value%TYPE;

Line: 1017


  SAVEPOINT UpdateCalendarPostActionSP;

Line: 1021


  SELECT property_value
  INTO   l_System_Stage
  from   bsc_sys_init
  WHERE  property_code =  BSC_PERIODS_UTILITY_PKG.C_SYSTEM_STAGE;

Line: 1027


    BSC_CALENDAR_PVT.Update_Fiscal_Change
    ( p_Api_Version            => p_Api_Version
    , p_Commit                 => p_Commit
    , p_Calendar_Id            => p_Calendar_Record.Calendar_Id
    , x_Return_Status          => x_Return_Status
    , x_Msg_Count              => x_Msg_Count
    , x_Msg_Data               => x_Msg_Data
    );

Line: 1051


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

Line: 1067


    ROLLBACK TO UpdateCalendarPostActionSP;

Line: 1077


    ROLLBACK TO UpdateCalendarPostActionSP;

Line: 1087


    ROLLBACK TO UpdateCalendarPostActionSP;

Line: 1090


      x_msg_data      :=  x_msg_data ||'BSC_CALENDAR_PUB.Update_Calendar_Post_Action ';

Line: 1092


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

Line: 1095


    ROLLBACK TO UpdateCalendarPostActionSP;

Line: 1098


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

Line: 1100


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

Line: 1102


END Update_Calendar_Post_Action;

Line: 1117


  SELECT DISTINCT(K.indicator)
  FROM   bsc_kpi_periodicities K,
         bsc_sys_periodicities S
  WHERE  S.periodicity_id = K.periodicity_id
  AND    S.calendar_id    = p_Calendar_Id;

Line: 1128


    BSC_DESIGNER_PVT.ActionFlag_Change(CD.indicator,BSC_DESIGNER_PVT.G_ActionFlag.Update_Update);

Line: 1183


h_tmp_array           BSC_UPDATE_UTIL.t_array_of_number;

Line: 1195


  SELECT PERIODICITY_ID
  FROM   BSC_SYS_PERIODICITIES
  WHERE  CALENDAR_ID = p_calendar_id
  AND    CUSTOM_CODE < p_custom_code
  ORDER  BY PERIODICITY_ID;

Line: 1202


  SELECT PERIODICITY_ID,SOURCE
  FROM   BSC_SYS_PERIODICITIES
  WHERE  CALENDAR_ID = p_calendar_id
  ORDER  BY PERIODICITY_ID;

Line: 1208


  SELECT PERIODICITY_ID
  FROM   BSC_SYS_PERIODICITIES
  WHERE  CALENDAR_ID = p_calendar_id
  AND    PERIODICITY_TYPE = p_periodicity_type;

Line: 1229


    SELECT  NULL
          , num_of_periods
          , source
          , num_of_subperiods
          , period_col_name
          , subperiod_col_name
          , yearly_flag
          , edw_flag
          , p_Calendar_Record.Calendar_Id
          , edw_periodicity_id
          , custom_code
          , db_column_name
          , periodicity_type
          , name
    INTO    l_Periodicity_Record.Periodicity_Id
          , l_Periodicity_Record.Num_Of_Periods
          , l_Periodicity_Record.Source
          , l_Periodicity_Record.Num_Of_Subperiods
          , l_Periodicity_Record.Period_Col_Name
          , l_Periodicity_Record.Subperiod_Col_Name
          , l_Periodicity_Record.Yearly_Flag
          , l_Periodicity_Record.Edw_Flag
          , l_Periodicity_Record.Calendar_Id
          , l_Periodicity_Record.Edw_Periodicity_Id
          , l_Periodicity_Record.Custom_Code
          , l_Periodicity_Record.Db_Column_Name
          , l_Periodicity_Record.Periodicity_Type
          , l_Periodicity_Record.Name
    FROM bsc_sys_periodicities_vl
    WHERE periodicity_id = CD.periodicity_id;

Line: 1283


      h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(cd_new_per.source,h_tmp_array,',');

Line: 1299


      UPDATE bsc_sys_periodicities
      SET    source = h_new_source
      WHERE  periodicity_id = cd_new_per.periodicity_id;

Line: 1377


  UPDATE bsc_sys_calendars_b
  SET    short_name  = l_Short_Name
  WHERE  calendar_id = p_Calendar_Record.calendar_id;

Line: 1417


PROCEDURE Update_Calendar_Dimension
( p_Api_Version            IN          NUMBER
, p_Commit                 IN          VARCHAR2
, p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
, x_Return_Status          OUT NOCOPY  VARCHAR2
, x_Msg_Count              OUT NOCOPY  NUMBER
, x_Msg_Data               OUT NOCOPY  VARCHAR2
)IS
CURSOR C_comma_objNames IS
SELECT obj_short_name
FROM   bsc_bis_dim_obj_by_dim_vl
WHERE  dim_short_name = p_Calendar_Record.Dim_Short_Name;

Line: 1446


  BSC_BIS_DIMENSION_PUB.Update_Dimension
  ( p_commit               => p_Commit
  , p_dim_short_name       => p_Calendar_Record.Dim_Short_Name
  , p_display_name         => p_Calendar_Record.Name
  , p_description          => p_Calendar_Record.Help
  , p_application_id       => p_Calendar_Record.Application_Id
  , p_dim_obj_short_names  => l_dim_obj_shortNames
  , p_time_stamp           => NULL
  , x_return_status        => x_return_status
  , x_msg_count            => x_msg_count
  , x_msg_data             => x_msg_data
  );

Line: 1486


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

Line: 1488


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

Line: 1493


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

Line: 1495


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

Line: 1497


END Update_Calendar_Dimension;

Line: 1506


    SELECT  indicator, name, calendar_id
    FROM bsc_kpis_vl
    WHERE prototype_flag = 0
      AND calendar_id    = p_cal_id;

Line: 1534


SELECT day30, MONTH
FROM bsc_db_calendar
WHERE calendar_id = p_calid
AND   year =  p_cyear
MINUS
SELECT day30, MONTH
FROM bsc_db_calendar
WHERE calendar_id = p_calid
AND   year = p_pyear;

Line: 1552


   SELECT day365
   INTO x_result
   FROM bsc_db_calendar
   WHERE calendar_id = p_calid
   AND  year  = p_cyear
   AND  day30 = lday
   AND  MONTH = lmonth;