[Home] [Help]
1: PACKAGE BODY BSC_PERIODS_UTILITY_PKG AS
2: /* $Header: BSCUPERB.pls 120.11 2006/06/27 08:43:13 adrao noship $ */
3: /*
4: REM +=======================================================================+
5: REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
26: REM | by PMV Bug#4767731 |
27: REM +=======================================================================+
28: */
29:
30: G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODS_UTILITY_PKG';
31:
32: -- Checks if the Periodicity Name is unique to the calendar or not.
33: FUNCTION get_Next_Alias
34: (
129: SELECT COUNT(1)
130: INTO l_Count
131: FROM BSC_SYS_PERIODICITIES P
132: WHERE P.CALENDAR_ID = p_Calendar_Id
133: AND P.CUSTOM_CODE <> BSC_PERIODS_UTILITY_PKG.C_BASE_PERIODICITY_TYPE;
134:
135: RETURN l_Count;
136: EXCEPTION
137: WHEN OTHERS THEN
134:
135: RETURN l_Count;
136: EXCEPTION
137: WHEN OTHERS THEN
138: RETURN BSC_PERIODS_UTILITY_PKG.C_NON_CUSTOM_PERIODICITY_CODE;
139: END Get_Cust_Per_Cnt_By_Calendar;
140:
141: -- Returns the next custom DB Column name
142: FUNCTION Get_Next_Cust_Period_DB_Column (
432: END Rollback_API;
433:
434: -- Added APIs to get View START_DATE and END_DATE
435: -- A typical usage example is
436: -- BSC_PERIODS_UTILITY_PKG.Get_Start_Period_Date (424, 1569, 2, 2005);
437:
438: FUNCTION Get_Start_Period_Date (
439: p_Calendar_Id IN NUMBER
440: , p_Periodicity_Id IN NUMBER
448: l_Cal_Mn BSC_DB_CALENDAR.CALENDAR_MONTH%TYPE;
449: l_Cal_Dy BSC_DB_CALENDAR.CALENDAR_DAY%TYPE;
450: l_Sep VARCHAR2(1);
451: BEGIN
452: l_Period_DB_Col := BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Db_Col(p_Periodicity_Id);
453:
454:
455: l_Sql := ' SELECT A.CALENDAR_DAY, A.CALENDAR_MONTH, A.CALENDAR_YEAR FROM (' ||
456: ' SELECT CALENDAR_DAY, CALENDAR_MONTH, CALENDAR_YEAR, ' ||
488: l_Cal_Mn BSC_DB_CALENDAR.CALENDAR_MONTH%TYPE;
489: l_Cal_Dy BSC_DB_CALENDAR.CALENDAR_DAY%TYPE;
490: l_Sep VARCHAR2(1);
491: BEGIN
492: l_Period_DB_Col := BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Db_Col(p_Periodicity_Id);
493:
494:
495: l_Sql := ' SELECT A.CALENDAR_DAY, A.CALENDAR_MONTH, A.CALENDAR_YEAR FROM (' ||
496: ' SELECT CALENDAR_DAY, CALENDAR_MONTH, CALENDAR_YEAR, ' ||
619: l_Count NUMBER;
620: l_alias VARCHAR2(4);
621:
622: BEGIN
623: l_Short_Name := BSC_PERIODS_UTILITY_PKG.C_PERIOD_SHORT_NAME_PREFIX||C_CALNEDAR_SHORT_PREFIX||p_Calendar_Id||
624: C_UNDER_SCORE||C_PERIOD_SHORT_PREFIX||p_Period_Id;
625:
626: l_Flag := TRUE;
627: l_alias := NULL;
671: INTO l_Calendar_Name
672: FROM bsc_sys_calendars_vl
673: WHERE calendar_id = p_Calendar_Id;
674:
675: l_Return_Name := SUBSTR(l_Calendar_Name,1,254-LENGTH(p_Periodicity_Name))|| BSC_PERIODS_UTILITY_PKG.C_HYPHEN || p_Periodicity_Name;
676:
677: l_Sequence := 0;
678: l_Flag := TRUE;
679: l_Temp_Var := l_Return_Name;
687: l_Flag := FALSE;
688: l_Return_Name := l_Temp_Var;
689: END IF;
690: l_Sequence := l_Sequence + 1;
691: l_Temp_Var := SUBSTR(l_Calendar_Name,1,250-LENGTH(p_Periodicity_Name))||l_Sequence ||BSC_PERIODS_UTILITY_PKG.C_HYPHEN || p_Periodicity_Name;
692:
693: END LOOP;
694: RETURN l_Return_Name;
695: EXCEPTION
714: , x_message OUT NOCOPY VARCHAR2
715: )RETURN BOOLEAN IS
716: BEGIN
717: IF(BIS_UTILITIES_PVT.Is_Rolling_Period_Level(x_time_level_name) = 1) THEN
718: x_time_level_name := BSC_PERIODS_UTILITY_PKG.Get_Non_Rolling_Dim_Obj(x_time_level_name);
719: END IF;
720:
721: RETURN BSC_DBI_CALENDAR.Get_Bsc_Periodicity(
722: p_time_level_name => x_time_level_name
731: FUNCTIONALITY : This API takes the as of date value and the
732: time dimension object under consideration and returns in the
733: Quarter date format, for example
734:
735: SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('28-JUN-02', 'BSC_PER_2453567477645868') from dual
736:
737: Q2 FY02 Day -2
738:
739: NOTE 1: That this API should be used only with Custom Periodicities and
742: NOTE 2: The API will return the message error text in case of date errors
743:
744: NOTE 3: Appropriate ORA errors will be returned, for example
745:
746: SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('28-JUN-03', 'BSC_PER_2453567477645868') from dual
747:
748: ORA-01403: no data found
749:
750: SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('30-FEB-03', 'BSC_PER_2453567477645868') from dual
746: SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('28-JUN-03', 'BSC_PER_2453567477645868') from dual
747:
748: ORA-01403: no data found
749:
750: SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('30-FEB-03', 'BSC_PER_2453567477645868') from dual
751:
752: ORA-01839: date not valid for month specified
753: *************************************************************************************************************/
754:
831: RETURN SQLERRM;
832: END Get_Quarter_Date_Label;
833:
834:
835: END BSC_PERIODS_UTILITY_PKG;