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 |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCUPERB.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 | 25-AUG-2005 Aditya Rao added API Get_Non_Rolling_Dim_Obj Bug#4566634 |
17 REM | 29-AUG-2005 Aditya Rao Changed API Get_Cust_Per_Cnt_By_Calendar for |
18 REM | Bug#4576424 |
19 REM | 29-AUG-2005 Aditya Rao Added API Get_Periodicity_Name for Bug#4574115|
20 REM | 28-SEP-2005 akoduri Bug#4626935 Get_Daily_Periodicity_Sht_Name API|
21 REM | is added |
22 REM | 29-NOV-2005 kyadamak Added APIs for Enhancement#4711274 |
23 REM | 16-FEB-2006 Aditya Rao added ABS() to DBMS_UTILITY.GET_TIME for |
24 REM | Bug#5039894 |
25 REM | 22-JUN-2006 Aditya Rao Added API Get_Quarter_Date_Label as requested |
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 (
35 p_Alias IN VARCHAR2
36 ) RETURN VARCHAR2
37 IS
38 l_alias VARCHAR2(4);
39 l_return VARCHAR2(4);
40 l_count NUMBER;
41 BEGIN
42 IF (p_Alias IS NULL) THEN
43 l_return := 'A';
44 ELSE
45 l_count := LENGTH(p_Alias);
46 IF (l_count = 1) THEN
47 l_return := 'A0';
48 ELSIF (l_count > 1) THEN
49 l_alias := SUBSTR(p_Alias, 2);
50 l_count := TO_NUMBER(l_alias)+1;
51 l_return := SUBSTR(p_Alias, 1, 1)||TO_CHAR(l_count);
52 END IF;
53 END IF;
54 RETURN l_return;
55 END get_Next_Alias;
56
57 FUNCTION Is_Period_Name_Unique (
58 p_Calendar_Id IN NUMBER
59 , p_Periodicity_Name IN VARCHAR2
60 ) RETURN VARCHAR2 IS
61 l_Count NUMBER;
62 BEGIN
63 l_Count := 0;
64
65 SELECT COUNT(1) INTO l_Count
66 FROM BSC_SYS_PERIODICITIES_VL P
67 WHERE P.CALENDAR_ID = p_Calendar_Id
68 AND UPPER(P.NAME) = UPPER(p_Periodicity_Name);
69
70
71 IF (l_Count <> 0) THEN
72 RETURN FND_API.G_FALSE;
73 END IF;
74
75 RETURN FND_API.G_TRUE;
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 RETURN FND_API.G_FALSE;
80 END Is_Period_Name_Unique;
81
82 -- Gets the Calendar Name from the CALENDAR_ID
83 FUNCTION Get_Calendar_Name (
84 p_Calendar_Id IN NUMBER
85 ) RETURN VARCHAR2 IS
86 l_Calendar_Name BSC_SYS_CALENDARS_TL.NAME%TYPE;
87 BEGIN
88
89 SELECT C.NAME
90 INTO l_Calendar_Name
91 FROM BSC_SYS_CALENDARS_VL C
92 WHERE C.CALENDAR_ID = p_Calendar_Id;
93
94 RETURN l_Calendar_Name;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 RETURN NULL;
99 END Get_Calendar_Name;
100
101 -- Returns the calendar short_name.
102 FUNCTION Get_Calendar_Short_Name (
103 p_Calendar_Id IN NUMBER
104 ) RETURN VARCHAR2
105 IS
106 l_Calendar_Short_Name BSC_SYS_CALENDARS_B.SHORT_NAME%TYPE;
107 BEGIN
108
109 SELECT C.SHORT_NAME
110 INTO l_Calendar_Short_Name
111 FROM BSC_SYS_CALENDARS_B C
112 WHERE C.CALENDAR_ID = p_Calendar_Id;
113
114 RETURN l_Calendar_Short_Name;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 RETURN NULL;
119 END Get_Calendar_Short_Name;
120
121
122 -- Get the number of custom periodicities by Calendar.
123 -- Changed condition for Bug#4576424
124 FUNCTION Get_Cust_Per_Cnt_By_Calendar (
125 p_Calendar_Id IN NUMBER
126 ) RETURN VARCHAR2 IS
127 l_Count NUMBER;
128 BEGIN
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
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 (
143 p_Calendar_Id IN NUMBER
144 ) RETURN VARCHAR2 IS
145 l_Max_Custom_Id NUMBER;
146 l_Custom_DB_Column BSC_SYS_PERIODICITIES.DB_COLUMN_NAME%TYPE;
147 BEGIN
148 SELECT NVL(MAX(TO_NUMBER(SUBSTR(P.DB_COLUMN_NAME, LENGTH(C_CUSTOM_DB_COL_PREFIX)+1))), 0)
149 INTO l_Max_Custom_Id
150 FROM BSC_SYS_PERIODICITIES P
151 WHERE P.CUSTOM_CODE > 0
152 AND P.CALENDAR_ID = P_CALENDAR_ID;
153
154 l_Custom_DB_Column := C_CUSTOM_DB_COL_PREFIX || (l_Max_Custom_Id+1);
155
156 RETURN l_Custom_DB_Column;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN NULL;
161 END Get_Next_Cust_Period_DB_Column;
162
163
164 -- Gets the next PERIODICITY_ID from sequence BSC_SYS_PERIODICITY_ID_S
165 FUNCTION Get_Next_Periodicity_Id
166 RETURN NUMBER IS
167 l_Next_Number NUMBER;
168 BEGIN
169
170 SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL
171 INTO l_Next_Number
172 FROM DUAL;
173
174 RETURN l_Next_Number;
175 EXCEPTION
176 WHEN OTHERS THEN
177 SELECT NVL(MAX(P.PERIODICITY_ID)+1, 0)
178 INTO l_Next_Number
179 FROM BSC_SYS_PERIODICITIES P;
180
181 RETURN l_Next_Number;
182 END Get_Next_Periodicity_Id;
183
184 FUNCTION Get_Next_Calendar_Id
185 RETURN NUMBER IS
186 l_Next_Number NUMBER;
187 BEGIN
188
189 SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL
190 INTO l_Next_Number
191 FROM DUAL;
192
193 RETURN l_Next_Number;
194 EXCEPTION
195 WHEN OTHERS THEN
196 SELECT NVL(MAX(P.calendar_id)+1, 0)
197 INTO l_Next_Number
198 FROM bsc_sys_calendars_b P;
199
200 RETURN l_Next_Number;
201 END Get_Next_Calendar_Id;
202
203
204 --Checks if a given periodicity belongs to a calendar.
205 FUNCTION Is_Periodicity_In_Calendar (
206 p_Calendar_Id IN NUMBER
207 , p_Periodicity_Id IN NUMBER
208 ) RETURN VARCHAR2
209 IS
210 l_Count NUMBER;
211 BEGIN
212 SELECT COUNT(1)
213 INTO l_Count
214 FROM BSC_SYS_PERIODICITIES B
215 WHERE B.PERIODICITY_ID = p_Periodicity_Id
216 AND B.CALENDAR_ID = p_Calendar_Id;
217
218 IF (l_Count = 1) THEN
219 RETURN FND_API.G_TRUE;
220 END IF;
221
222 RETURN FND_API.G_FALSE;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 RETURN FND_API.G_FALSE;
227 END Is_Periodicity_In_Calendar;
228
229
230 FUNCTION Get_Periodicity_Source (
231 p_Periodicity_Id IN NUMBER
232 ) RETURN VARCHAR2
233 IS
234 l_Source_Column BSC_SYS_PERIODICITIES.SOURCE%TYPE;
235 BEGIN
236 SELECT B.SOURCE
237 INTO l_Source_Column
238 FROM BSC_SYS_PERIODICITIES B
239 WHERE B.PERIODICITY_ID = p_Periodicity_Id;
240
241 RETURN l_Source_Column;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 RETURN NULL;
246 END Get_Periodicity_Source;
247
248
249 -- Get unique periodicity short_name
250 FUNCTION Get_Unique_Short_Name RETURN VARCHAR2
251 IS
252 l_Return_Short_Name VARCHAR2(30);
253 BEGIN
254 RETURN C_PERIOD_SHORT_NAME_PREFIX||TO_CHAR(SYSDATE,'J')||ABS(DBMS_UTILITY.GET_TIME);
255 EXCEPTION
256 WHEN OTHERS THEN
257 RETURN NULL;
258 END Get_Unique_Short_Name;
259
260
261 PROCEDURE Print_Period_Metadata (
262 p_Debug_Flag VARCHAR2
263 , p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
264 ) IS
265 BEGIN
266 NULL;
267 /*
268 DBMS_OUTPUT.PUT_LINE('p_Debug_Flag - ' || p_Debug_Flag);
269 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Periodicity_Id ' || p_Periodicities_Rec_Type.Periodicity_Id );
270 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Num_Of_Periods ' || p_Periodicities_Rec_Type.Num_Of_Periods );
271 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Source ' || p_Periodicities_Rec_Type.Source );
272 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Base_Periodicity_Id ' || p_Periodicities_Rec_Type.Base_Periodicity_Id);
273 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Num_Of_Subperiods ' || p_Periodicities_Rec_Type.Num_Of_Subperiods );
274 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Period_Col_Name ' || p_Periodicities_Rec_Type.Period_Col_Name );
275 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Subperiod_Col_Name ' || p_Periodicities_Rec_Type.Subperiod_Col_Name );
276 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Yearly_Flag ' || p_Periodicities_Rec_Type.Yearly_Flag );
277 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Edw_Flag ' || p_Periodicities_Rec_Type.Edw_Flag );
278 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Calendar_Id ' || p_Periodicities_Rec_Type.Calendar_Id );
279 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Edw_Periodicity_Id ' || p_Periodicities_Rec_Type.Edw_Periodicity_Id );
280 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Custom_Code ' || p_Periodicities_Rec_Type.Custom_Code );
281 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Db_Column_Name ' || p_Periodicities_Rec_Type.Db_Column_Name );
282 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Periodicity_Type ' || p_Periodicities_Rec_Type.Periodicity_Type );
283 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Period_Type_Id ' || p_Periodicities_Rec_Type.Period_Type_Id );
284 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Record_Type_Id ' || p_Periodicities_Rec_Type.Record_Type_Id );
285 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Xtd_Pattern ' || p_Periodicities_Rec_Type.Xtd_Pattern );
286 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Short_Name ' || p_Periodicities_Rec_Type.Short_Name );
287 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Name ' || p_Periodicities_Rec_Type.Name );
288 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Created_By ' || p_Periodicities_Rec_Type.Created_By );
289 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Creation_Date ' || p_Periodicities_Rec_Type.Creation_Date );
290 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Last_Updated_By ' || p_Periodicities_Rec_Type.Last_Updated_By );
291 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Last_Update_Date ' || p_Periodicities_Rec_Type.Last_Update_Date );
292 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Last_Update_Login ' || p_Periodicities_Rec_Type.Last_Update_Login );
293 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Application_id ' || p_Periodicities_Rec_Type.Application_id );
294 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Period_Year ' || p_Periodicities_Rec_Type.Period_Year );
295 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Start_Period ' || p_Periodicities_Rec_Type.Start_Period );
296 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.End_Period ' || p_Periodicities_Rec_Type.End_Period );
297 DBMS_OUTPUT.PUT_LINE('p_Periodicities_Rec_Type.Period_IDs ' || p_Periodicities_Rec_Type.Period_IDs );
298 */
299 END Print_Period_Metadata;
300
301
302 FUNCTION Check_Error_Message(p_Error_Message IN VARCHAR2)
303 RETURN BOOLEAN IS
304 l_error_message bsc_message_logs.message%TYPE;
305 l_Is_Error BOOLEAN;
306
307 CURSOR C_ERROR(l_source VARCHAR2) IS
308 SELECT message
309 INTO l_error_message
310 FROM bsc_message_logs
311 WHERE type = 0
312 AND UPPER(SOURCE) = UPPER(l_source)
313 AND LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID;
314
315 BEGIN
316 l_Is_Error := FALSE;
317 IF(C_ERROR%ISOPEN) THEN
318 CLOSE C_ERROR;
319 END IF;
320
321 OPEN C_ERROR(p_Error_Message);
322 FETCH C_ERROR INTO l_error_message;
323 CLOSE C_ERROR;
324
325 IF(LENGTH(l_error_message) > 0 ) THEN
326 l_Is_Error := TRUE;
327 END IF;
328
329 RETURN l_Is_Error;
330
331 END Check_Error_Message;
332
333 FUNCTION Get_FiscalYear_By_Calendar(p_Calendar_Id NUMBER)
334 RETURN NUMBER IS
335 l_Fiscal_Year BSC_SYS_CALENDARS_B.FISCAL_YEAR%TYPE;
336 BEGIN
337 SELECT FISCAL_YEAR
338 INTO l_Fiscal_Year
339 FROM BSC_SYS_CALENDARS_B
340 WHERE CALENDAR_ID = p_Calendar_Id;
341
342 RETURN l_Fiscal_Year;
343
344 END Get_FiscalYear_By_Calendar;
345
346 -- added exception for Bug#4626530
347 FUNCTION Is_Base_Periodicity_Daily(p_Base_Periodicity_Id NUMBER)
348 RETURN BOOLEAN IS
349 l_Is_Daily BOOLEAN := FALSE;
350 l_Periodicity_Id NUMBER;
351 BEGIN
352 SELECT periodicity_type
353 INTO l_Periodicity_Id
354 FROM bsc_sys_periodicities
355 WHERE periodicity_id = p_Base_Periodicity_Id;
356
357 IF(l_Periodicity_Id = 9) THEN
358 l_Is_Daily := TRUE;
359 END IF;
360
361 RETURN l_Is_Daily;
362 EXCEPTION
363 WHEN OTHERS THEN
364 RETURN FALSE;
365 END Is_Base_Periodicity_Daily;
366
367 FUNCTION Get_Periodicity_Short_Name (
368 p_Periodicity_Id IN NUMBER
369 ) RETURN VARCHAR2 IS
370 l_Short_Name BSC_SYS_PERIODICITIES.SHORT_NAME%TYPE;
371 BEGIN
372 SELECT P.SHORT_NAME INTO l_Short_Name
373 FROM BSC_SYS_PERIODICITIES P
374 WHERE P.PERIODICITY_ID = p_Periodicity_Id;
375
376 RETURN l_Short_Name;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 RETURN NULL;
381 END Get_Periodicity_Short_Name;
382
383 -- added exception for Bug#4626530
384 FUNCTION Get_Periods_In_Base_Period (
385 p_Base_Periodicity_Id IN NUMBER
386 ) RETURN NUMBER
387 IS
388 l_no_Periods NUMBER;
389 BEGIN
390 SELECT NUM_OF_PERIODS
391 INTO l_No_Periods
392 FROM BSC_SYS_PERIODICITIES
393 WHERE PERIODICITY_ID = p_Base_Periodicity_Id;
394
395 RETURN l_No_Periods;
396 EXCEPTION
397 WHEN OTHERS THEN
398 RETURN 0;
399 END Get_Periods_In_Base_Period;
400
401
402 PROCEDURE Rollback_API (
403 p_In_String IN VARCHAR2
404 ,p_ErrorOut IN VARCHAR2
405 ,x_Return_Status OUT NOCOPY VARCHAR2
406 ,x_Msg_Count OUT NOCOPY NUMBER
407 ,x_Msg_Data OUT NOCOPY VARCHAR2
408 ) IS
409 BEGIN
410 SAVEPOINT RollbackAPIPUB;
411
412 FND_MSG_PUB.Initialize;
413 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
414
415 IF (p_ErrorOut = FND_API.G_TRUE) THEN
416 FND_MESSAGE.SET_NAME('BSC','BSC_ERROR_MESSAGE');
417 FND_MSG_PUB.ADD;
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 END IF;
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 ROLLBACK TO RollbackAPIPUB;
424 IF (x_msg_data IS NULL) THEN
425 FND_MSG_PUB.Count_And_Get
426 ( p_encoded => FND_API.G_FALSE
427 , p_count => x_msg_count
428 , p_data => x_msg_data
429 );
430 END IF;
431 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
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
441 , p_Period_id IN NUMBER
442 , p_Year IN NUMBER
443 ) RETURN DATE IS
444 l_Sql VARCHAR2(1000);
445 l_Period_DB_Col BSC_SYS_PERIODICITIES.DB_COLUMN_NAME%TYPE;
446 l_Date DATE;
447 l_Cal_Yr BSC_DB_CALENDAR.CALENDAR_YEAR%TYPE;
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, ' ||
457 ' (DAY365 - MIN(DAY365) OVER (PARTITION BY '||l_Period_DB_Col||')) SORT_DATE ' ||
458 ' FROM BSC_DB_CALENDAR ' ||
459 ' WHERE CALENDAR_ID = :1 AND YEAR = :2 AND '||l_Period_DB_Col||' = :3) A ' ||
460 ' WHERE A.SORT_DATE = 0 ';
461
462
463 l_Sep := '/';
464
465 EXECUTE IMMEDIATE l_Sql
466 INTO l_Cal_Dy, l_Cal_Mn, l_Cal_Yr
467 USING p_Calendar_Id, p_Year, p_Period_id;
468
469 -- to avoid false postives (need to use direct values)
470 RETURN TO_DATE(l_Cal_Mn||l_Sep||l_Cal_Dy||l_Sep||l_Cal_Yr, 'mm/dd/yyyy');
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 RETURN NULL;
475 END Get_Start_Period_Date;
476
477
478 FUNCTION Get_End_Period_Date (
479 p_Calendar_Id IN NUMBER
480 , p_Periodicity_Id IN NUMBER
481 , p_Period_id IN NUMBER
482 , p_Year IN NUMBER
483 ) RETURN DATE IS
484 l_Sql VARCHAR2(1000);
485 l_Period_DB_Col BSC_SYS_PERIODICITIES.DB_COLUMN_NAME%TYPE;
486 l_Date DATE;
487 l_Cal_Yr BSC_DB_CALENDAR.CALENDAR_YEAR%TYPE;
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, ' ||
497 ' (MAX(DAY365) OVER (PARTITION BY '||l_Period_DB_Col||')-DAY365) SORT_DATE ' ||
498 ' FROM BSC_DB_CALENDAR ' ||
499 ' WHERE CALENDAR_ID = :1 AND YEAR = :2 AND '||l_Period_DB_Col||' = :3) A ' ||
500 ' WHERE A.SORT_DATE = 0 ';
501
502 l_Sep := '/';
503
504 EXECUTE IMMEDIATE l_Sql
505 INTO l_Cal_Dy, l_Cal_Mn, l_Cal_Yr
506 USING p_Calendar_Id, p_Year, p_Period_id;
507
508 --to avoid false postives (need to use direct values)
509 RETURN TO_DATE(l_Cal_Mn||l_Sep||l_Cal_Dy||l_Sep||l_Cal_Yr, 'mm/dd/yyyy');
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 RETURN NULL;
514 END Get_End_Period_Date;
515
516
517 FUNCTION Get_Periodicity_Db_Col (
518 p_Periodicity_Id IN NUMBER
519 ) RETURN VARCHAR2 IS
520 l_Period_DB_Col BSC_SYS_PERIODICITIES.DB_COLUMN_NAME%TYPE;
521 BEGIN
522 SELECT P.DB_COLUMN_NAME
523 INTO l_Period_Db_Col
524 FROM BSC_SYS_PERIODICITIES P
525 WHERE P.PERIODICITY_ID = p_Periodicity_Id;
526
527 RETURN l_Period_Db_Col;
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 RETURN NULL;
532 END Get_Periodicity_Db_Col;
533
534
535 -- Added API for Bug#4566634
536 -- This API returns the correspoding Enterprise Period when a
537 -- Rolling Period is passed to the API
538 FUNCTION Get_Non_Rolling_Dim_Obj (
539 p_Short_Name IN VARCHAR2
540 ) RETURN VARCHAR2 IS
541 BEGIN
542
543 IF (p_Short_Name = C_FII_ROLLING_QTR) THEN
544 RETURN C_FII_TIME_ENT_QTR;
545 ELSIF (p_Short_Name = C_FII_ROLLING_WEEK) THEN
546 RETURN C_FII_TIME_WEEK;
547 ELSIF (p_Short_Name = C_FII_ROLLING_MONTH) THEN
548 RETURN C_FII_TIME_ENT_PERIOD;
549 ELSIF (p_Short_Name = C_FII_ROLLING_YEAR) THEN
550 RETURN C_FII_TIME_ENT_YEAR;
551 END IF;
552
553 RETURN p_Short_Name;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 RETURN p_Short_Name;
558 END Get_Non_Rolling_Dim_Obj;
559
560 -- added for Bug#4574115
561 FUNCTION Get_Periodicity_Name (
562 p_Periodicity_Id IN NUMBER
563 ) RETURN VARCHAR2 IS
564 l_Name BSC_SYS_PERIODICITIES_VL.NAME%TYPE;
565 BEGIN
566 SELECT B.NAME
567 INTO l_Name
568 FROM BSC_SYS_PERIODICITIES_VL B
569 WHERE B.PERIODICITY_ID = p_Periodicity_Id;
570
571 RETURN l_Name;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 RETURN NULL;
576 END Get_Periodicity_Name;
577
578 FUNCTION Get_Daily_Periodicity_Sht_Name(
579 p_Calendar_Id IN NUMBER
580 ) RETURN VARCHAR2 IS
581 l_Periodicity_Short_Name BSC_SYS_PERIODICITIES_VL.SHORT_NAME%TYPE;
582
583 CURSOR c_CalendarPeriodicities IS
584 SELECT PERIODICITY_ID,
585 SHORT_NAME
586 FROM BSC_SYS_PERIODICITIES_VL
587 WHERE CALENDAR_id = p_Calendar_Id;
588
589 BEGIN
590 FOR prdcty IN c_CalendarPeriodicities LOOP
591 IF (Is_Base_Periodicity_Daily(prdcty.PERIODICITY_ID)) THEN
592 l_Periodicity_Short_Name := prdcty.SHORT_NAME;
593 END IF;
594 END LOOP;
595
596 RETURN l_Periodicity_Short_Name;
597 EXCEPTION
598 WHEN OTHERS THEN
599 RETURN l_Periodicity_Short_Name;
600 END Get_Daily_Periodicity_Sht_Name;
601
602
603
604 /**************************************************************************
605 Function Name :- generate_Period_Short_Name
606 Description :- generates period short Name as Below
607 BSC_PER_CALx_PERy where x = calendarId and y = periodId
608 Parameters :-
609 p_Calendar_Id :- Id of the calendar.
610 p_Period_Id :- periodicity Id of the period
611 *****************************************************************************/
612 FUNCTION generate_Period_Short_Name (
613 p_Calendar_Id IN NUMBER
614 ,p_Period_Id IN NUMBER
615 )RETURN VARCHAR2 IS
616 l_Short_Name BSC_SYS_PERIODICITIES.SHORT_NAME%TYPE;
617 l_Flag BOOLEAN;
618 l_Temp_Var BIS_LEVELS.SHORT_NAME%TYPE;
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;
628 l_Temp_Var := l_Short_Name;
629
630 WHILE (l_flag) LOOP
631 SELECT COUNT(1)
632 INTO l_Count
633 FROM BIS_LEVELS_VL
634 WHERE UPPER(Short_Name) = UPPER(l_temp_var);
635
636 IF (l_Count = 0) THEN
637 l_flag := FALSE;
638 l_Short_Name := l_temp_var;
639 END IF;
640 l_alias := get_Next_Alias(l_alias);
641 l_temp_var := l_Short_Name||l_alias;
642 END LOOP;
643
644 RETURN l_Short_Name;
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 RETURN NULL;
649 END generate_Period_Short_Name;
650 /**************************************************************************
651 Function Name :- get_Dimobj_Name_From_period
652 Description :- Makes the name of period dimension object from calendar name and period name
653 BSC_PER_CALx_PERy where x = calendarId and y = periodId
654 Parameters :-
655 p_Calendar_Id :- Id of the calendar.
656 p_Periodicity_Name :- Name of the period
657 *****************************************************************************/
658
659 FUNCTION get_Dimobj_Name_From_period (
660 p_Calendar_Id IN NUMBER
661 , p_Periodicity_Name IN VARCHAR2
662 ) RETURN VARCHAR2 IS
663 l_Count NUMBER;
664 l_Calendar_Name BSC_SYS_CALENDARS_TL.NAME%TYPE;
665 l_Return_Name BIS_LEVELS_TL.NAME%TYPE;
666 l_Flag BOOLEAN;
667 l_Sequence NUMBER;
668 l_Temp_Var BIS_LEVELS_TL.NAME%TYPE;
669 BEGIN
670 SELECT name
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;
680 WHILE(l_Flag) LOOP
681 SELECT COUNT(1)
682 INTO l_Count
683 FROM bis_levels_vl
684 WHERE UPPER(name) = UPPER(l_Temp_Var);
685
686 IF(l_Count = 0 ) THEN
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
696 WHEN OTHERS THEN
697 RETURN NULL;
698 END get_Dimobj_Name_From_period;
699
700 /**************************************************************************
701 Function Name :- Get_Bsc_Periodicity
702 Description :- First checks if Period is Roling type. (Picks corresponding level id for rolilng type)
703 Then calls BSC_DBI_CALENDAR.Get_Bsc_Periodicity to get the Periodicity corresponding to level.
704 Parameters :-
705 x_time_level_name :- takes the level Short_Name and returns corresponding short_name (short_name will change for rolling type)
706 x_periodicity_id :- returns periodicity id corresponding to period
707 x_calendar_id :- returns calendar_id it is associated to.
708 x_message :- Message returned from API.
709 *****************************************************************************/
710 FUNCTION Get_Bsc_Periodicity(
711 x_time_level_name IN OUT NOCOPY VARCHAR2
712 , x_periodicity_id OUT NOCOPY NUMBER
713 , x_calendar_id OUT NOCOPY NUMBER
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
723 ,x_periodicity_id => x_periodicity_id
724 ,x_calendar_id => x_calendar_id
725 ,x_message => x_message
726 );
727 END Get_Bsc_Periodicity;
728
729 /*************************************************************************************************************
730 API NAME : Get_Quarter_Date_Label
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
740 this will behave unexpectedly by returning null in case of DBI Periodicities
741
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
751
752 ORA-01839: date not valid for month specified
753 *************************************************************************************************************/
754
755 FUNCTION Get_Quarter_Date_Label(
756 p_As_Of_date IN VARCHAR2,
757 p_Dimension_Object_Short_Name IN VARCHAR2
758 ) RETURN VARCHAR2 IS
759
760 l_Calendar_Id NUMBER;
761 l_Day NUMBER;
762 l_Month NUMBER;
763 l_Year NUMBER;
764 l_YY VARCHAR2(2);
765 l_Quarter NUMBER;
766
767 l_Last_Day_Count NUMBER;
768 l_Current_Day_Count NUMBER;
769 l_Days_Left NUMBER;
770
771 l_Data_Label VARCHAR2(100);
772
773 CURSOR c_Get_Calendar_ID IS
774 SELECT P.CALENDAR_ID
775 FROM BSC_SYS_PERIODICITIES_VL P
776 WHERE P.SHORT_NAME = p_Dimension_Object_Short_Name;
777
778 BEGIN
779
780 l_Calendar_Id := NULL;
781
782 FOR cGC IN c_Get_Calendar_ID LOOP
783 l_Calendar_Id := cGC.CALENDAR_ID;
784 END LOOP;
785
786 IF (l_Calendar_Id IS NULL) THEN
787 RETURN NULL;
788 END IF;
789
790 IF (p_As_Of_date IS NULL) THEN
791 RETURN NULL;
792 END IF;
793
794 SELECT TO_NUMBER(TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'DD')),
795 TO_NUMBER(TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'MM')),
796 TO_NUMBER(TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'YYYY')),
797 TO_CHAR(TO_DATE(p_As_Of_date, 'DD/MM/YYYY'), 'YY')
798 INTO l_Day, l_Month, l_Year, l_YY
799 FROM DUAL;
800
801 SELECT C.QUARTER, C.DAY365
802 INTO l_Quarter, l_Current_Day_Count
803 FROM BSC_DB_CALENDAR C
804 WHERE C.CALENDAR_YEAR = l_Year
805 AND C.CALENDAR_MONTH = l_Month
806 AND C.CALENDAR_DAY = l_Day
807 AND C.CALENDAR_ID = l_Calendar_Id;
808
809 -- Get the last day count for the current quarter.
810
811 SELECT MAX(C.DAY365) INTO l_Last_Day_Count
812 FROM BSC_DB_CALENDAR C
813 WHERE C.CALENDAR_ID = l_Calendar_Id
814 AND C.CALENDAR_YEAR = l_Year
815 AND C.QUARTER = l_Quarter;
816
817
818 l_Days_Left := (l_Last_Day_Count - l_Current_Day_Count);
819
820 -- Q&QUARTER_NUMBER FY&YEAR_NUMBER Day -&DAY_NUMBER
821 FND_MESSAGE.SET_NAME('BSC', 'BSC_DATE_LABEL');
822 FND_MESSAGE.SET_TOKEN('DAY_NUMBER', l_Days_Left, FALSE);
823 FND_MESSAGE.SET_TOKEN('QUARTER_NUMBER', l_Quarter, FALSE);
824 FND_MESSAGE.SET_TOKEN('YEAR_NUMBER',l_YY, FALSE);
825
826 l_Data_Label := FND_MESSAGE.GET;
827
828 RETURN l_Data_Label;
829 EXCEPTION
830 WHEN OTHERS THEN
831 RETURN SQLERRM;
832 END Get_Quarter_Date_Label;
833
834
835 END BSC_PERIODS_UTILITY_PKG;
[Home] [Help]