APPS.BSC_PERIODS_UTILITY_PKG SQL Statements

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

Line: 65

    SELECT COUNT(1) INTO l_Count
    FROM   BSC_SYS_PERIODICITIES_VL P
    WHERE  P.CALENDAR_ID = p_Calendar_Id
    AND    UPPER(P.NAME) = UPPER(p_Periodicity_Name);

Line: 89

    SELECT C.NAME
    INTO   l_Calendar_Name
    FROM   BSC_SYS_CALENDARS_VL C
    WHERE  C.CALENDAR_ID = p_Calendar_Id;

Line: 109

    SELECT C.SHORT_NAME
    INTO   l_Calendar_Short_Name
    FROM   BSC_SYS_CALENDARS_B C
    WHERE  C.CALENDAR_ID = p_Calendar_Id;

Line: 129

    SELECT COUNT(1)
    INTO   l_Count
    FROM   BSC_SYS_PERIODICITIES P
    WHERE  P.CALENDAR_ID = p_Calendar_Id
    AND    P.CUSTOM_CODE <> BSC_PERIODS_UTILITY_PKG.C_BASE_PERIODICITY_TYPE;

Line: 148

    SELECT NVL(MAX(TO_NUMBER(SUBSTR(P.DB_COLUMN_NAME, LENGTH(C_CUSTOM_DB_COL_PREFIX)+1))), 0)
    INTO   l_Max_Custom_Id
    FROM   BSC_SYS_PERIODICITIES P
    WHERE  P.CUSTOM_CODE > 0
    AND    P.CALENDAR_ID = P_CALENDAR_ID;

Line: 170

    SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL
    INTO   l_Next_Number
    FROM   DUAL;

Line: 177

        SELECT NVL(MAX(P.PERIODICITY_ID)+1, 0)
        INTO   l_Next_Number
        FROM   BSC_SYS_PERIODICITIES P;

Line: 189

    SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL
    INTO   l_Next_Number
    FROM   DUAL;

Line: 196

        SELECT NVL(MAX(P.calendar_id)+1, 0)
        INTO   l_Next_Number
        FROM   bsc_sys_calendars_b P;

Line: 212

    SELECT COUNT(1)
    INTO   l_Count
    FROM   BSC_SYS_PERIODICITIES B
    WHERE  B.PERIODICITY_ID = p_Periodicity_Id
    AND    B.CALENDAR_ID    = p_Calendar_Id;

Line: 236

    SELECT B.SOURCE
    INTO   l_Source_Column
    FROM   BSC_SYS_PERIODICITIES B
    WHERE  B.PERIODICITY_ID = p_Periodicity_Id;

Line: 290

    DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Last_Updated_By          ' ||   p_Periodicities_Rec_Type.Last_Updated_By    );

Line: 291

    DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Last_Update_Date         ' ||   p_Periodicities_Rec_Type.Last_Update_Date   );

Line: 292

    DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Last_Update_Login        ' ||   p_Periodicities_Rec_Type.Last_Update_Login  );

Line: 308

  SELECT message
  INTO   l_error_message
  FROM   bsc_message_logs
  WHERE  type = 0
  AND    UPPER(SOURCE) = UPPER(l_source)
  AND    LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID;

Line: 337

  SELECT FISCAL_YEAR
  INTO   l_Fiscal_Year
  FROM   BSC_SYS_CALENDARS_B
  WHERE  CALENDAR_ID = p_Calendar_Id;

Line: 352

  SELECT periodicity_type
  INTO   l_Periodicity_Id
  FROM   bsc_sys_periodicities
  WHERE  periodicity_id = p_Base_Periodicity_Id;

Line: 372

    SELECT P.SHORT_NAME INTO l_Short_Name
    FROM   BSC_SYS_PERIODICITIES P
    WHERE  P.PERIODICITY_ID = p_Periodicity_Id;

Line: 390

    SELECT NUM_OF_PERIODS
    INTO   l_No_Periods
    FROM   BSC_SYS_PERIODICITIES
    WHERE  PERIODICITY_ID = p_Base_Periodicity_Id;

Line: 455

    l_Sql :=  ' SELECT A.CALENDAR_DAY, A.CALENDAR_MONTH, A.CALENDAR_YEAR FROM (' ||
              '  SELECT CALENDAR_DAY, CALENDAR_MONTH, CALENDAR_YEAR,  ' ||
              '  (DAY365 - MIN(DAY365) OVER (PARTITION BY '||l_Period_DB_Col||')) SORT_DATE  ' ||
              '  FROM BSC_DB_CALENDAR  ' ||
              '  WHERE CALENDAR_ID = :1 AND YEAR = :2 AND '||l_Period_DB_Col||' = :3) A ' ||
              ' WHERE A.SORT_DATE = 0 ';

Line: 495

    l_Sql :=  ' SELECT A.CALENDAR_DAY, A.CALENDAR_MONTH, A.CALENDAR_YEAR FROM (' ||
              '  SELECT CALENDAR_DAY, CALENDAR_MONTH, CALENDAR_YEAR,  ' ||
              '  (MAX(DAY365) OVER (PARTITION BY '||l_Period_DB_Col||')-DAY365) SORT_DATE  ' ||
              '  FROM BSC_DB_CALENDAR  ' ||
              '  WHERE CALENDAR_ID = :1 AND YEAR = :2 AND '||l_Period_DB_Col||' = :3) A ' ||
              ' WHERE A.SORT_DATE = 0 ';

Line: 522

    SELECT P.DB_COLUMN_NAME
    INTO   l_Period_Db_Col
    FROM   BSC_SYS_PERIODICITIES P
    WHERE  P.PERIODICITY_ID = p_Periodicity_Id;

Line: 566

    SELECT B.NAME
    INTO   l_Name
    FROM   BSC_SYS_PERIODICITIES_VL B
    WHERE  B.PERIODICITY_ID = p_Periodicity_Id;

Line: 584

    SELECT PERIODICITY_ID,
           SHORT_NAME
    FROM   BSC_SYS_PERIODICITIES_VL
    WHERE  CALENDAR_id = p_Calendar_Id;

Line: 631

    SELECT COUNT(1)
    INTO   l_Count
    FROM   BIS_LEVELS_VL
    WHERE  UPPER(Short_Name) = UPPER(l_temp_var);

Line: 670

  SELECT name
  INTO   l_Calendar_Name
  FROM   bsc_sys_calendars_vl
  WHERE  calendar_id = p_Calendar_Id;

Line: 681

    SELECT COUNT(1)
    INTO   l_Count
    FROM   bis_levels_vl
    WHERE  UPPER(name) = UPPER(l_Temp_Var);

Line: 735

SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('28-JUN-02', 'BSC_PER_2453567477645868') from dual

Q2 FY02 Day -2

NOTE 1: That this API should be used only with Custom Periodicities and
this will behave unexpectedly by returning null in case of DBI Periodicities

NOTE 2: The API will return the message error text in case of date errors

NOTE 3: Appropriate ORA errors will be returned, for example

SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('28-JUN-03', 'BSC_PER_2453567477645868') from dual

ORA-01403: no data found

SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('30-FEB-03', 'BSC_PER_2453567477645868') from dual

ORA-01839: date not valid for month specified
*************************************************************************************************************/

FUNCTION Get_Quarter_Date_Label(
  p_As_Of_date                   IN VARCHAR2,
  p_Dimension_Object_Short_Name  IN VARCHAR2
) RETURN VARCHAR2 IS

  l_Calendar_Id NUMBER;

Line: 774

    SELECT P.CALENDAR_ID
    FROM BSC_SYS_PERIODICITIES_VL P
    WHERE P.SHORT_NAME = p_Dimension_Object_Short_Name;

Line: 794

  SELECT TO_NUMBER(TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'DD')),
         TO_NUMBER(TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'MM')),
         TO_NUMBER(TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'YYYY')),
         TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'YY')
  INTO   l_Day, l_Month, l_Year, l_YY
  FROM   DUAL;

Line: 801

  SELECT C.QUARTER, C.DAY365
  INTO   l_Quarter, l_Current_Day_Count
  FROM   BSC_DB_CALENDAR C
  WHERE  C.CALENDAR_YEAR  = l_Year
  AND    C.CALENDAR_MONTH = l_Month
  AND    C.CALENDAR_DAY   = l_Day
  AND    C.CALENDAR_ID    = l_Calendar_Id;

Line: 811

  SELECT MAX(C.DAY365) INTO l_Last_Day_Count
  FROM   BSC_DB_CALENDAR C
  WHERE  C.CALENDAR_ID    = l_Calendar_Id
  AND    C.CALENDAR_YEAR  = l_Year
  AND    C.QUARTER        = l_Quarter;