1 PACKAGE BSC_PERIODS_UTILITY_PKG AS
2 /* $Header: BSCUPERS.pls 120.8 2006/06/27 07:09:39 adrao noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCVPERS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Utility file for Calendar and Periodicities and TIME |
13 REM | integration modules (designer) |
14 REM | NOTES |
15 REM | 14-JUL-2005 Aditya Rao Created. |
16 REM | 08-AUG-2005 Aditya Rao added constants for Bug#4533089 |
17 REM | 25-AUG-2005 Aditya Rao added API Get_Non_Rolling_Dim_Obj Bug#4566634 |
18 REM | 29-AUG-2005 Aditya Rao Added API Get_Periodicity_Name for Bug#4574115|
19 REM | 28-SEP-2005 akoduri Bug#4626935 Get_Daily_Periodicity_Sht_Name API|
20 REM | is added |
21 REM | 29-NOV-2005 Krishna Modified for enh#4711274 |
22 REM | 22-JUN-2006 Aditya Rao Added API Get_Quarter_Date_Label as requested|
23 REM | by PMV Bug#4767731 |
24 REM +=======================================================================+
25 */
26
27 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODS_UTILITY_PKG';
28
29 C_CREATE CONSTANT VARCHAR2(30):='CREATE';
30 C_UPDATE CONSTANT VARCHAR2(30):='UPDATE';
31 C_RETRIEVE CONSTANT VARCHAR2(30):='RETRIEVE';
32 C_DELETE CONSTANT VARCHAR2(30):='DELETE';
33
34 C_MAX_CUSTOM_PERIODICITIES CONSTANT NUMBER := 20;
35 C_CUSTOM_PERIODICITY_CODE CONSTANT NUMBER := 2;
36 C_NON_CUSTOM_PERIODICITY_CODE CONSTANT NUMBER := 0;
37
38 C_PERIODICITY_YEARLY_FLAG CONSTANT NUMBER := 0;
39
40
41 C_CUSTOM_DB_COL_PREFIX CONSTANT VARCHAR2(30) :='CUSTOM_';
42 C_DFLT_PERIOD_COL_NAME CONSTANT VARCHAR2(30) :='PERIOD';
43 C_CUST_PERIODICITY_TYPE CONSTANT NUMBER := 0;
44 C_CUST_NUM_OF_SUBPERIODS CONSTANT NUMBER := 0;
45 C_CONSTANT_ZERO CONSTANT NUMBER := 0;
46 C_DEFAULT_START_MONTH CONSTANT NUMBER := 1;
47 C_DEFAULT_START_DAY CONSTANT NUMBER := 1;
48 C_SYSTEM_STAGE CONSTANT VARCHAR2(12) := 'SYSTEM_STAGE';
49
50
51
52
53 C_PERIOD_SHORT_NAME_PREFIX CONSTANT VARCHAR2(30) := 'BSC_PER_';
54 C_CALNEDAR_SHORT_PREFIX CONSTANT VARCHAR2(30) := 'CAL';
55 C_PERIOD_SHORT_PREFIX CONSTANT VARCHAR2(30) := 'PER';
56 C_UNDER_SCORE CONSTANT VARCHAR2(30) := '_';
57 C_HYPHEN CONSTANT VARCHAR2(30) := '-';
58
59 C_BSC_APPLICATION_ID CONSTANT NUMBER := 271;
60
61
62 C_API_VERSION_1_0 CONSTANT NUMBER := 1.0;
63
64 C_PMF_DO_TYPE CONSTANT VARCHAR2(30) := 'PMF';
65
66 C_BSC_DO_TYPE CONSTANT VARCHAR2(30) := 'BSC';
67 C_OLTP_DO_TYPE CONSTANT VARCHAR2(30) := 'OLTP';
68 C_CALENDAR_NAME CONSTANT VARCHAR2(9) := 'Calendar ';
69
70
71 C_CAL_DATE_FORMAT CONSTANT VARCHAR2(10) := 'mm/dd/yyyy';
72
73 C_BASE_PERIODICITY_TYPE CONSTANT NUMBER := 0;
74
75 C_COMMA_SEPARATOR CONSTANT VARCHAR2(3) :=',';
76
77 -- Added for Bug#4533089
78 C_YEAR_COLUMN CONSTANT VARCHAR(10) := 'YEAR';
79 C_SEMESTER_COLUMN CONSTANT VARCHAR(10) := 'SEMESTER';
80 C_QUARTER_COLUMN CONSTANT VARCHAR(10) := 'QUARTER';
81 C_BIMESTER_COLUMN CONSTANT VARCHAR(10) := 'BIMESTER';
82 C_MONTH_COLUMN CONSTANT VARCHAR(10) := 'MONTH';
83 C_WEEK52_COLUMN CONSTANT VARCHAR(10) := 'WEEK52';
84 C_DAY365_COLUMN CONSTANT VARCHAR(10) := 'DAY365';
85
86
87 C_FII_ROLLING_QTR CONSTANT VARCHAR(30) := 'FII_ROLLING_QTR';
88 C_FII_ROLLING_WEEK CONSTANT VARCHAR(30) := 'FII_ROLLING_WEEK';
89 C_FII_ROLLING_MONTH CONSTANT VARCHAR(30) := 'FII_ROLLING_MONTH';
90 C_FII_ROLLING_YEAR CONSTANT VARCHAR(30) := 'FII_ROLLING_YEAR';
91 C_FII_TIME_ENT_QTR CONSTANT VARCHAR(30) := 'FII_TIME_ENT_QTR';
92 C_FII_TIME_WEEK CONSTANT VARCHAR(30) := 'FII_TIME_WEEK';
93 C_FII_TIME_ENT_PERIOD CONSTANT VARCHAR(30) := 'FII_TIME_ENT_PERIOD';
94 C_FII_TIME_ENT_YEAR CONSTANT VARCHAR(30) := 'FII_TIME_ENT_YEAR';
95
96
97 FUNCTION Is_Period_Name_Unique (
98 p_Calendar_Id IN NUMBER
99 , p_Periodicity_Name IN VARCHAR2
100 ) RETURN VARCHAR2;
101
102
103 FUNCTION Get_Calendar_Name (
104 p_Calendar_Id IN NUMBER
105 ) RETURN VARCHAR2;
106
107
108 FUNCTION Get_Calendar_Short_Name (
109 p_Calendar_Id IN NUMBER
110 ) RETURN VARCHAR2;
111
112
113 FUNCTION Get_Cust_Per_Cnt_By_Calendar (
114 p_Calendar_Id IN NUMBER
115 ) RETURN VARCHAR2;
116
117 FUNCTION Get_Next_Cust_Period_DB_Column (
118 p_Calendar_Id IN NUMBER
119 ) RETURN VARCHAR2;
120
121 FUNCTION Get_Next_Periodicity_Id RETURN NUMBER;
122
123 FUNCTION Get_Next_Calendar_Id RETURN NUMBER;
124
125 FUNCTION Is_Periodicity_In_Calendar (
126 p_Calendar_Id IN NUMBER
127 , p_Periodicity_Id IN NUMBER
128 ) RETURN VARCHAR2;
129
130 FUNCTION Get_Periodicity_Source (
131 p_Periodicity_Id IN NUMBER
132 ) RETURN VARCHAR2;
133
134 -- added for Bug#4574115
135 FUNCTION Get_Periodicity_Name (
136 p_Periodicity_Id IN NUMBER
137 ) RETURN VARCHAR2;
138
139 FUNCTION Get_Unique_Short_Name RETURN VARCHAR2;
140
141 PROCEDURE Print_Period_Metadata (
142 p_Debug_Flag VARCHAR2
143 , p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
144 );
145
146 FUNCTION Check_Error_Message(p_Error_Message IN VARCHAR2)
147 RETURN BOOLEAN;
148
149 FUNCTION Is_Base_Periodicity_Daily(p_Base_Periodicity_Id NUMBER)
150 RETURN BOOLEAN;
151
152 FUNCTION Get_FiscalYear_By_Calendar(p_Calendar_Id NUMBER)
153 RETURN NUMBER;
154
155 FUNCTION Get_Periodicity_Short_Name (
156 p_Periodicity_Id IN NUMBER
157 ) RETURN VARCHAR2;
158
159 FUNCTION Get_Periods_In_Base_Period(p_Base_Periodicity_Id IN NUMBER)
160 RETURN NUMBER;
161
162
163 PROCEDURE Rollback_API (
164 p_In_String IN VARCHAR2
165 ,p_ErrorOut IN VARCHAR2
166 ,x_Return_Status OUT NOCOPY VARCHAR2
167 ,x_Msg_Count OUT NOCOPY NUMBER
168 ,x_Msg_Data OUT NOCOPY VARCHAR2
169 );
170
171 FUNCTION Get_Start_Period_Date (
172 p_Calendar_Id IN NUMBER
173 , p_Periodicity_Id IN NUMBER
174 , p_Period_id IN NUMBER
175 , p_Year IN NUMBER
176 ) RETURN DATE;
177
178
179 FUNCTION Get_End_Period_Date (
180 p_Calendar_Id IN NUMBER
181 , p_Periodicity_Id IN NUMBER
182 , p_Period_id IN NUMBER
183 , p_Year IN NUMBER
184 ) RETURN DATE;
185
186 FUNCTION Get_Periodicity_Db_Col (
187 p_Periodicity_Id IN NUMBER
188 ) RETURN VARCHAR2;
189
190 -- Added API for Bug#4566634
191 -- This API returns the correspoding Enterprise Period when a
192 -- Rolling Period is passed to the API
193 FUNCTION Get_Non_Rolling_Dim_Obj (p_Short_Name IN VARCHAR2) RETURN VARCHAR2;
194
195 FUNCTION Get_Daily_Periodicity_Sht_Name(
196 p_Calendar_Id IN NUMBER
197 ) RETURN VARCHAR2;
198
199 FUNCTION get_Dimobj_Name_From_period (
200 p_Calendar_Id IN NUMBER
201 , p_Periodicity_Name IN VARCHAR2
202 ) RETURN VARCHAR2 ;
203
204 FUNCTION generate_Period_Short_Name (
205 p_Calendar_Id IN NUMBER
206 , p_Period_Id IN NUMBER
207 )RETURN VARCHAR2 ;
208
209 FUNCTION Get_Bsc_Periodicity(
210 x_time_level_name IN OUT NOCOPY VARCHAR2
211 , x_periodicity_id OUT NOCOPY NUMBER
212 , x_calendar_id OUT NOCOPY NUMBER
213 , x_message OUT NOCOPY VARCHAR2
214 )RETURN BOOLEAN;
215
216 /*************************************************************************************
217 API NAME : Get_Quarter_Date_Label
218 FUNCTIONALITY : This API takes the as of date value and the
219 time dimension object under consideration and returns in the
220 Quarter date format, for example
221
222 SQL> select Get_Quarter_Date_Format('28-JUN-02', 'BSC_PER_2453567477645868') from dual
223
224 Q2 FY02 Day -2
225
226 NOTE 1: That this API should be used only with Custom Periodicities and
227 this will behave unexpectedly by returning null in case of DBI Periodicities
228
229 NOTE 2: The API will return the message error text in case of date errors
230
231 NOTE 3: Appropriate ORA errors will be returned, for example
232
233 SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('28-JUN-03', 'BSC_PER_2453567477645868') from dual
234
235 ORA-01403: no data found
236
237 SQL> select BSC_PERIODS_UTILITY_PKG.Get_Quarter_Date_Format('30-FEB-03', 'BSC_PER_2453567477645868') from dual
238
239 ORA-01839: date not valid for month specified
240 *************************************************************************************/
241
242 FUNCTION Get_Quarter_Date_Label(
243 p_As_Of_date IN VARCHAR2,
244 p_Dimension_Object_Short_Name IN VARCHAR2
245 ) RETURN VARCHAR2;
246
247 END BSC_PERIODS_UTILITY_PKG;
[Home] [Help]