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.
PACKAGE BODY: APPS.BSC_CALENDAR_PUB

Source


1 PACKAGE BODY BSC_CALENDAR_PUB AS
2 /* $Header: BSCPCALB.pls 120.4 2007/12/18 06:49:19 lbodired ship $ */
3 /*
4 REM +=======================================================================+
5 REM |    Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA     |
6 REM |                         All rights reserved.                          |
7 REM +=======================================================================+
8 REM | FILENAME                                                              |
9 REM |     BSCPCALB.pls                                                      |
10 REM |                                                                       |
11 REM | DESCRIPTION                                                           |
12 REM |     Module: Public package for populating the calendar  tables        |
13 REM | NOTES                                                                 |
14 REM | 07-JUN-2005 Krishna  Created.                                         |
15 REM | 31-AUG-2005 Aditya Rao fixed Bug#4565308 for START_DAY of fiscal year |
16 REM | 29-NOV-2005 Krishna Modified for enh#4711274                          |
17 REM | 06-JUL-07 psomesul  Bug#6168487 - CHANGING CALENDAR DEF WHICH IS IN PRODUCTION IS NOT TRIGGERING  TO USER |
18 REM +=======================================================================+
19 */
20 
21 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_CALENDAR_PUB';
22 
23 FISCAL_YEAR_CHANGE  NUMBER := 0;
24 --The following three APIs are used to call BIA populate canlednars
25 --in different phases
26 --For every calendar we create we create dimension also
27 PROCEDURE Create_Calendar_Dimension
28 ( p_Api_Version            IN          NUMBER
29 , p_Commit                 IN          VARCHAR2
30 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
31 , x_Return_Status          OUT NOCOPY  VARCHAR2
32 , x_Msg_Count              OUT NOCOPY  NUMBER
33 , x_Msg_Data               OUT NOCOPY  VARCHAR2
34 );
35 
36 PROCEDURE Update_Calendar_Dimension
37 ( p_Api_Version            IN          NUMBER
38 , p_Commit                 IN          VARCHAR2
39 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
40 , x_Return_Status          OUT NOCOPY  VARCHAR2
41 , x_Msg_Count              OUT NOCOPY  NUMBER
42 , x_Msg_Data               OUT NOCOPY  VARCHAR2
43 );
44 
45 PROCEDURE Change_Fiscal_Year
46 IS
47 BEGIN
48   FISCAL_YEAR_CHANGE := 1;
49 END Change_Fiscal_Year;
50 
51 FUNCTION  Get_Fiscal_Year
52 RETURN NUMBER IS
53 BEGIN
54   RETURN FISCAL_YEAR_CHANGE;
55 END Get_Fiscal_Year;
56 
57 PROCEDURE Create_Calendar
58 ( p_Api_Version            IN          NUMBER
59 , p_Commit                 IN          VARCHAR2
60 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
61 , x_Return_Status          OUT NOCOPY  VARCHAR2
62 , x_Msg_Count              OUT NOCOPY  NUMBER
63 , x_Msg_Data               OUT NOCOPY  VARCHAR2
64 )IS
65 l_Calendar_Record BSC_CALENDAR_PUB.Calendar_Type_Record;
66 BEGIN
67   SAVEPOINT CreateCalendarPubSP;
68   FND_MSG_PUB.Initialize;
69   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
70 
71   BSC_CALENDAR_PUB.Validate_Calendar_Action
72   ( p_Api_Version      => p_Api_Version
73   , p_Commit           => p_Commit
74   , p_Calendar_Record  => p_Calendar_Record
75   , p_Action           => BSC_PERIODS_UTILITY_PKG.C_CREATE
76   , x_Return_Status    => x_Return_Status
77   , x_Msg_Count        => x_Msg_Count
78   , x_Msg_Data         => x_Msg_Data
79   );
80   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
81     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
82   END IF;
83 
84   BSC_CALENDAR_PUB.Fill_Default_Values_Create_Cal
85   ( p_Api_Version      => p_Api_Version
86   , p_Commit           => p_Commit
87   , p_Calendar_Record  => p_Calendar_Record
88   , x_Calendar_Record  => l_Calendar_Record
89   , x_Return_Status    => x_Return_Status
90   , x_Msg_Count        => x_Msg_Count
91   , x_Msg_Data         => x_Msg_Data
92   );
93   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
94     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
95   END IF;
96 
97   BSC_CALENDAR_PVT.Create_Calendar
98   ( p_Api_Version      => p_Api_Version
99   , p_Commit           => p_Commit
100   , p_Calendar_Record  => l_Calendar_Record
101   , x_Return_Status    => x_Return_Status
102   , x_Msg_Count        => x_Msg_Count
103   , x_Msg_Data         => x_Msg_Data
104   );
105   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
106     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
107   END IF;
108 
109   BSC_CALENDAR_PUB.Create_Calendar_Dimension
110   ( p_Api_Version      => p_Api_Version
111   , p_Commit           => p_Commit
112   , p_Calendar_Record  => l_Calendar_Record
113   , x_Return_Status    => x_Return_Status
114   , x_Msg_Count        => x_Msg_Count
115   , x_Msg_Data         => x_Msg_Data
116   );
117   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
118     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
119   END IF;
120 
121   BSC_CALENDAR_PUB.Create_Calendar_Post_Action
122   ( p_Api_Version      => p_Api_Version
123   , p_Commit           => p_Commit
124   , p_Calendar_Record  => l_Calendar_Record
125   , x_Return_Status    => x_Return_Status
126   , x_Msg_Count        => x_Msg_Count
127   , x_Msg_Data         => x_Msg_Data
128   );
129   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
130     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
131   END IF;
132 
133   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
134     COMMIT;
135   END IF;
136 
137 EXCEPTION
138   WHEN FND_API.G_EXC_ERROR THEN
139     ROLLBACK TO CreateCalendarPubSP;
140     IF (x_msg_data IS NULL) THEN
141       FND_MSG_PUB.Count_And_Get
142       ( p_encoded   =>  FND_API.G_FALSE
143       , p_count     =>  x_msg_count
144       , p_data      =>  x_msg_data
145       );
146     END IF;
147     x_return_status :=  FND_API.G_RET_STS_ERROR;
148   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
149     ROLLBACK TO CreateCalendarPubSP;
150     IF (x_msg_data IS NULL) THEN
151       FND_MSG_PUB.Count_And_Get
152       ( p_encoded   =>  FND_API.G_FALSE
153       , p_count     =>  x_msg_count
154       , p_data      =>  x_msg_data
155       );
156     END IF;
157     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158   WHEN NO_DATA_FOUND THEN
159     ROLLBACK TO CreateCalendarPubSP;
160     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161     IF (x_msg_data IS NOT NULL) THEN
162       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Calendar ';
163     ELSE
164       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Calendar ';
165     END IF;
166   WHEN OTHERS THEN
167     ROLLBACK TO CreateCalendarPubSP;
168     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169     IF (x_msg_data IS NOT NULL) THEN
170       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Calendar ';
171     ELSE
172       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Calendar ';
173     END IF;
174 END Create_Calendar;
175 
176 /*****************************************************************************************/
177 
178 PROCEDURE Update_Calendar
179 ( p_Api_Version            IN          NUMBER
180 , p_Commit                 IN          VARCHAR2
181 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
182 , x_Return_Status          OUT NOCOPY  VARCHAR2
183 , x_Msg_Count              OUT NOCOPY  NUMBER
184 , x_Msg_Data               OUT NOCOPY  VARCHAR2
185 )IS
186 l_Calendar_Record BSC_CALENDAR_PUB.Calendar_Type_Record;
187 l_Fiscal_Year       BSC_SYS_CALENDARS_B.FISCAL_YEAR%TYPE;
188 l_Start_Month       BSC_SYS_CALENDARS_B.START_MONTH%TYPE;
189 l_Start_Day         BSC_SYS_CALENDARS_B.START_DAY%TYPE;
190 l_Calendar_Old_Name BSC_SYS_CALENDARS_TL.NAME%TYPE;
191 BEGIN
192   SAVEPOINT UpdateCalendarPubSP;
193   FND_MSG_PUB.Initialize;
194   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
195 
196   SELECT fiscal_year
197         ,start_month
198         ,start_day
199         ,name
200   INTO   l_Fiscal_Year
201         ,l_Start_Month
202         ,l_Start_Day
203         ,l_Calendar_Old_Name
204   FROM   bsc_sys_calendars_vl
205   WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
206 
207   -- added for Bug#4565308
208   IF((l_Fiscal_Year <> p_Calendar_Record.Fiscal_Year)
209       OR (l_Start_Month <> p_Calendar_Record.Start_Month)
210       OR (l_Start_Day <> p_Calendar_Record.Start_Day)) THEN
211     Change_Fiscal_Year();
212   END IF;
213 
214   BSC_CALENDAR_PUB.Validate_Calendar_Action
215   ( p_Api_Version      => p_Api_Version
216   , p_Commit           => p_Commit
217   , p_Calendar_Record  => p_Calendar_Record
218   , p_Action           => BSC_PERIODS_UTILITY_PKG.C_UPDATE
219   , x_Return_Status    => x_Return_Status
220   , x_Msg_Count        => x_Msg_Count
221   , x_Msg_Data         => x_Msg_Data
222   );
223   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
224     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
225   END IF;
226 
227   BSC_CALENDAR_PUB.Retrieve_And_Populate_Cal_Rec
228   ( p_Api_Version      => p_Api_Version
229   , p_Commit           => p_Commit
230   , p_Calendar_Record  => p_Calendar_Record
231   , x_Calendar_Record  => l_Calendar_Record
232   , x_Return_Status    => x_Return_Status
233   , x_Msg_Count        => x_Msg_Count
234   , x_Msg_Data         => x_Msg_Data
235   );
236   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
237     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
238   END IF;
239 
240   BSC_CALENDAR_PVT.Update_Calendar
241   ( p_Api_Version      => p_Api_Version
242   , p_Commit           => p_Commit
243   , p_Calendar_Record  => l_Calendar_Record
244   , x_Return_Status    => x_Return_Status
245   , x_Msg_Count        => x_Msg_Count
246   , x_Msg_Data         => x_Msg_Data
247   );
248   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
249     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
250   END IF;
251 
252   BSC_CALENDAR_PUB.Update_Calendar_Dimension
253   ( p_Api_Version      => p_Api_Version
254   , p_Commit           => p_Commit
255   , p_Calendar_Record  => l_Calendar_Record
256   , x_Return_Status    => x_Return_Status
257   , x_Msg_Count        => x_Msg_Count
258   , x_Msg_Data         => x_Msg_Data
259   );
260   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
261     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
262   END IF;
263 
264   IF(UPPER(LTRIM(RTRIM(p_Calendar_Record.Name))) <> UPPER(LTRIM(RTRIM(l_Calendar_Old_Name)))) THEN
265     BSC_CALENDAR_PVT.Update_PeriodNames_In_Calendar
266     ( p_Calendar_Id        => p_Calendar_Record.Calendar_Id
267     , x_Return_Status      => x_Return_Status
268     , x_Msg_Count          => x_Msg_Count
269     , x_Msg_Data           => x_Msg_Data
270     );
271     IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
272       RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
273     END IF;
274   END IF;
275 
276   BSC_CALENDAR_PUB.Update_Calendar_Post_Action
277   ( p_Api_Version      => p_Api_Version
278   , p_Commit           => p_Commit
279   , p_Calendar_Record  => l_Calendar_Record
280   , x_Return_Status    => x_Return_Status
281   , x_Msg_Count        => x_Msg_Count
282   , x_Msg_Data         => x_Msg_Data
283   );
284   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
285     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
286   END IF;
287 
288   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
289     COMMIT;
290   END IF;
291 
292 EXCEPTION
293   WHEN FND_API.G_EXC_ERROR THEN
294     ROLLBACK TO UpdateCalendarPubSP;
295     IF (x_msg_data IS NULL) THEN
296       FND_MSG_PUB.Count_And_Get
297       ( p_encoded   =>  FND_API.G_FALSE
298       , p_count     =>  x_msg_count
299       , p_data      =>  x_msg_data
300       );
301     END IF;
302     x_return_status :=  FND_API.G_RET_STS_ERROR;
303   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304     ROLLBACK TO UpdateCalendarPubSP;
305     IF (x_msg_data IS NULL) THEN
306       FND_MSG_PUB.Count_And_Get
307       ( p_encoded   =>  FND_API.G_FALSE
308       , p_count     =>  x_msg_count
309       , p_data      =>  x_msg_data
310       );
311     END IF;
312     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313   WHEN NO_DATA_FOUND THEN
314     ROLLBACK TO UpdateCalendarPubSP;
315     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316     IF (x_msg_data IS NOT NULL) THEN
317       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Update_Calendar ';
318     ELSE
319       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Update_Calendar ';
320     END IF;
321   WHEN OTHERS THEN
322     ROLLBACK TO UpdateCalendarPubSP;
323     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324     IF (x_msg_data IS NOT NULL) THEN
325       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Update_Calendar ';
326     ELSE
327       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Update_Calendar ';
328     END IF;
329 END Update_Calendar;
330 
331 
332 
333 /****************************************************************************************/
334 PROCEDURE Delete_Calendar
335 ( p_Api_Version            IN          NUMBER
336 , p_Commit                 IN          VARCHAR2
337 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
338 , x_Return_Status          OUT NOCOPY  VARCHAR2
339 , x_Msg_Count              OUT NOCOPY  NUMBER
340 , x_Msg_Data               OUT NOCOPY  VARCHAR2
341 )IS
342 CURSOR C_Periodicity_ShortNames IS
343 SELECT short_name
344 FROM   bsc_sys_periodicities
345 WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
346 
347 CURSOR C_Per_Views IS
348 SELECT B.level_values_view_name
349 FROM   bsc_sys_periodicities P,
350        bis_levels        B
351 WHERE  b.short_name = P.short_name
352 AND    P.calendar_id = p_Calendar_Record.Calendar_Id
353 AND    P.db_column_name IS NOT NULL;
354 
355 CURSOR C_Clendar_ShortName IS
356 SELECT short_name
357 FROM   bsc_sys_calendars_b
358 WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
359 
360 l_Views_Array     varchar2_tabletype;
361 l_Comma_Per_ShortNames  VARCHAR2(32000);
362 l_Calendar_ShortName    BSC_SYS_CALENDARS_B.SHORT_NAME%TYPE;
363 l_DimObj_ViewName       BIS_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
364 l_count                NUMBER;
365 
366 BEGIN
367   SAVEPOINT DeleteCalendarPubSP;
368   FND_MSG_PUB.Initialize;
369   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
370 
371   BSC_CALENDAR_PUB.Validate_Calendar_Action
372   ( p_Api_Version      => p_Api_Version
373   , p_Commit           => p_Commit
374   , p_Calendar_Record  => p_Calendar_Record
375   , p_Action           => BSC_PERIODS_UTILITY_PKG.C_DELETE
376   , x_Return_Status    => x_Return_Status
377   , x_Msg_Count        => x_Msg_Count
378   , x_Msg_Data         => x_Msg_Data
379   );
380   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
381     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
382   END IF;
383 
384   FOR CCAL IN C_Clendar_ShortName LOOP
385     l_Calendar_ShortName := CCAL.short_name;
386   END LOOP;
387 
388   FOR CD IN C_Periodicity_ShortNames LOOP
389     IF(l_Comma_Per_ShortNames IS NULL) THEN
390       l_Comma_Per_ShortNames := CD.short_name;
391     ELSE
392       l_Comma_Per_ShortNames := l_Comma_Per_ShortNames || ','||CD.short_name;
393     END IF;
394   END LOOP;
395 
396   l_count := 0;
397   FOR CViews IN C_Per_Views  LOOP
398       l_count := l_count + 1;
399       l_Views_Array(l_count) := CViews.level_values_view_name;
400   END LOOP;
401 
402   BSC_BIS_DIMENSION_PUB.UnAssign_Dimension_Objects
403   ( p_commit               => p_Commit
404   , p_dim_short_name       => l_Calendar_ShortName
405   , p_dim_obj_short_names  => l_Comma_Per_ShortNames
406   , p_time_stamp           => NULL
407   , x_return_status        => x_Return_Status
408   , x_msg_count            => x_Msg_Count
409   , x_msg_data             => x_Msg_Data
410   );
411   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
412     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
413   END IF;
414 
415   --Delete dimension objects corresponding to periodicities
416   FOR CPER in C_Periodicity_ShortNames LOOP
417     BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object
418     ( p_commit              => p_commit
419     , p_dim_obj_short_name  => CPER.short_name
420     , x_return_status       => x_Return_Status
421     , x_msg_count           => x_Msg_Count
422     , x_msg_data            => x_Msg_Data
423     );
424     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
425       RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
426     END IF;
427   END LOOP;
428   --Delete dimension corresponding to calendar
429   BSC_BIS_DIMENSION_PUB.Delete_Dimension
430   ( p_commit          => p_commit
431   , p_dim_short_name  => l_Calendar_ShortName
432   , x_return_status   => x_Return_Status
433   , x_msg_count       => x_Msg_Count
434   , x_msg_data        => x_Msg_Data
435   );
436   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
437     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
438   END IF;
439   --Delete calendar
440   BSC_CALENDAR_PVT.Delete_Calendar
441   ( p_Api_Version      => p_Api_Version
442   , p_Commit           => p_Commit
443   , p_Calendar_Record  => p_Calendar_Record
444   , x_Return_Status    => x_Return_Status
445   , x_Msg_Count        => x_Msg_Count
446   , x_Msg_Data         => x_Msg_Data
447   );
448   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
449     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
450   END IF;
451 
452 
453   FOR index_loop IN 1..l_count LOOP
454     l_DimObj_ViewName := l_Views_Array(index_loop);
455     IF(l_DimObj_ViewName IS NOT NULL) THEN
456       BSC_PERIODS_PUB.Drop_Periodicity_View
457       ( p_Periodicity_View  => l_DimObj_ViewName
458       , x_Return_Status     => x_Return_Status
459       , x_Msg_Count         => x_Msg_Count
460       , x_Msg_Data          => x_Msg_Data
461       );
462       IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
463         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
464       END IF;
465     END IF;
466   END LOOP;
467   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
468     COMMIT;
469   END IF;
470 
471 EXCEPTION
472   WHEN FND_API.G_EXC_ERROR THEN
473     ROLLBACK TO DeleteCalendarPubSP;
474     IF (x_msg_data IS NULL) THEN
475       FND_MSG_PUB.Count_And_Get
476       ( p_encoded   =>  FND_API.G_FALSE
477       , p_count     =>  x_msg_count
478       , p_data      =>  x_msg_data
479       );
480     END IF;
481     x_return_status :=  FND_API.G_RET_STS_ERROR;
482   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483     ROLLBACK TO DeleteCalendarPubSP;
484     IF (x_msg_data IS NULL) THEN
485       FND_MSG_PUB.Count_And_Get
486       ( p_encoded   =>  FND_API.G_FALSE
487       , p_count     =>  x_msg_count
488       , p_data      =>  x_msg_data
489       );
490     END IF;
491     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492   WHEN NO_DATA_FOUND THEN
493     ROLLBACK TO DeleteCalendarPubSP;
494     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495     IF (x_msg_data IS NOT NULL) THEN
496       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Delete_Calendar ';
497     ELSE
498       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Delete_Calendar ';
499     END IF;
500   WHEN OTHERS THEN
501     ROLLBACK TO DeleteCalendarPubSP;
502     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503     IF (x_msg_data IS NOT NULL) THEN
504       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Delete_Calendar ';
505     ELSE
506       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Delete_Calendar ';
507     END IF;
508 END Delete_Calendar;
509 
510 /***********************************************************************************/
511 
512 PROCEDURE Validate_Calendar_Action
513 ( p_Api_Version            IN          NUMBER
514 , p_Commit                 IN          VARCHAR2
515 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
516 , p_Action                 IN          VARCHAR2
517 , x_Return_Status          OUT NOCOPY  VARCHAR2
518 , x_Msg_Count              OUT NOCOPY  NUMBER
519 , x_Msg_Data               OUT NOCOPY  VARCHAR2
520 )IS
521 l_count             NUMBER := 0;
522 l_Objective_Names   VARCHAR2(32000);
523 l_Calendar_Name     BSC_SYS_CALENDARS_TL.NAME%TYPE;
524 
525 CURSOR C_Objectives IS
526 SELECT k.name
527 FROM   bsc_kpis_vl K
528 WHERE  K.calendar_id = p_Calendar_Record.Calendar_Id;
529 
530 
531 BEGIN
532 
533   FND_MSG_PUB.Initialize;
534   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
535   IF(p_Action = BSC_PERIODS_UTILITY_PKG.C_CREATE OR p_Action = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
536     IF(BIS_UTILITIES_PVT.Value_Missing_Or_Null(p_Calendar_Record.Name) = FND_API.G_TRUE) THEN
537       FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_NAME_NULL');
538       FND_MSG_PUB.ADD;
539       RAISE FND_API.G_EXC_ERROR;
540     END IF;
541     IF(BIS_UTILITIES_PVT.Value_Missing_Or_Null(p_Calendar_Record.Dim_Short_Name) = FND_API.G_TRUE) THEN
542       FND_MESSAGE.SET_NAME('BSC','BSC_CAL_SHORT_NAME_NULL');
543       FND_MSG_PUB.ADD;
544       RAISE FND_API.G_EXC_ERROR;
545     END IF;
546   END IF;
547 
548   IF(p_Action = BSC_PERIODS_UTILITY_PKG.C_CREATE ) THEN
549     SELECT COUNT(1)
550     INTO   l_count
551     FROM   bsc_sys_calendars_b
552     WHERE  TRIM(calendar_id) = TRIM(p_Calendar_Record.Calendar_Id);
553     IF(l_count > 0)THEN
554       FND_MESSAGE.SET_NAME('BSC','BSC_CAL_ID_EXISTS');
555       FND_MSG_PUB.ADD;
556       RAISE FND_API.G_EXC_ERROR;
557     END IF;
558 
559     SELECT COUNT(1)
560     INTO   l_count
561     FROM   bsc_sys_calendars_vl
562     WHERE  TRIM(NAME) = TRIM(p_Calendar_Record.Name);
563     IF(l_count > 0)THEN
564       FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_EXISTS');
565       FND_MESSAGE.SET_TOKEN('CALENDAR_NAME',TRIM(p_Calendar_Record.Name));
566       FND_MSG_PUB.ADD;
567       RAISE FND_API.G_EXC_ERROR;
568     END IF;
569 
570     SELECT COUNT(1)
571     INTO   l_count
572     FROM   bsc_sys_calendars_b
573     WHERE  TRIM(short_name) = TRIM(p_Calendar_Record.Dim_Short_Name);
574     IF( l_count > 0 ) THEN
575       FND_MESSAGE.SET_NAME('BSC','BSC_CAL_SHORT_NAME_UNIQUE');
576       FND_MSG_PUB.ADD;
577       RAISE FND_API.G_EXC_ERROR;
578     END IF;
579 
580   ELSIF(p_Action = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
581     IF(p_Calendar_Record.Fiscal_Year IS NULL) THEN
582       FND_MESSAGE.SET_NAME('BSC','BSC_FISCAL_YEAR_NULL');
583       FND_MSG_PUB.ADD;
584       RAISE FND_API.G_EXC_ERROR;
585     END IF;
586 
587     SELECT COUNT(1)
588     INTO   l_count
589     FROM   BSC_SYS_CALENDARS_B
590     WHERE  CALENDAR_ID <> p_Calendar_Record.Calendar_Id
591     AND    TRIM(short_name) = TRIM(p_Calendar_Record.Dim_Short_Name);
592     IF( l_count > 0 ) THEN
593       FND_MESSAGE.SET_NAME('BSC','BSC_CAL_SHORT_NAME_UNIQUE');
594       FND_MSG_PUB.ADD;
595       RAISE FND_API.G_EXC_ERROR;
596     END IF;
597   ELSIF(p_Action = BSC_PERIODS_UTILITY_PKG.C_DELETE ) THEN
598 
599     l_count := 0;
600     SELECT COUNT(1)
601     INTO   l_count
602     FROM   bsc_sys_calendars_b
603     WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
604     --first check if the calendar passed exists or not
605     IF(l_count = 0 ) THEN
606       FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_DEL_ALREADY');
607       FND_MSG_PUB.ADD;
608       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609     END IF;
610 
611     l_count := 0;
612     SELECT COUNT(K.INDICATOR)
613     INTO   l_count
614     FROM   BSC_KPI_PERIODICITIES K,
615            BSC_SYS_PERIODICITIES S
616     WHERE  S.PERIODICITY_ID = K.PERIODICITY_ID
617     AND    S.CALENDAR_ID = p_Calendar_Record.Calendar_Id;
618 
619     SELECT name
620     INTO   l_Calendar_Name
621     FROM   bsc_sys_calendars_vl
622     WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
623 
624     IF (l_count <> 0) THEN
625       FOR cObj IN C_Objectives LOOP
626         IF(l_Objective_Names IS NULL) THEN
627           l_Objective_Names := cObj.name;
628         ELSE
629           l_Objective_Names := l_Objective_Names || ',' || cObj.name;
630         END IF;
631       END LOOP;
632       FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_USED_IN_OBJECTIVE');
633       FND_MESSAGE.SET_TOKEN('CALENDAR', l_Calendar_Name);
634       FND_MESSAGE.SET_TOKEN('OBJECTIVES', l_Objective_Names);
635       FND_MSG_PUB.ADD;
636       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637     END IF;
638 
639   END IF;
640 
641   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
642     COMMIT;
643   END IF;
644 
645 EXCEPTION
646   WHEN FND_API.G_EXC_ERROR THEN
647     IF (x_msg_data IS NULL) THEN
648       FND_MSG_PUB.Count_And_Get
649       ( p_encoded   =>  FND_API.G_FALSE
650       , p_count     =>  x_msg_count
651       , p_data      =>  x_msg_data
652       );
653     END IF;
654     x_return_status :=  FND_API.G_RET_STS_ERROR;
655   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
656     IF (x_msg_data IS NULL) THEN
657       FND_MSG_PUB.Count_And_Get
658       ( p_encoded   =>  FND_API.G_FALSE
659       , p_count     =>  x_msg_count
660       , p_data      =>  x_msg_data
661       );
662     END IF;
663     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
664   WHEN NO_DATA_FOUND THEN
665     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
666     IF (x_msg_data IS NOT NULL) THEN
667       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Validate_Calendar_Action ';
668     ELSE
669       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Validate_Calendar_Action ';
670     END IF;
671   WHEN OTHERS THEN
672     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673     IF (x_msg_data IS NOT NULL) THEN
674       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Validate_Calendar_Action ';
675     ELSE
676       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Validate_Calendar_Action ';
677     END IF;
678 END Validate_Calendar_Action;
679 
680 /*******************************************************************************************/
681 
682 PROCEDURE Retrieve_And_Populate_Cal_Rec
683 ( p_Api_Version            IN          NUMBER
684 , p_Commit                 IN          VARCHAR2
685 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
686 , x_Calendar_Record        OUT NOCOPY  BSC_CALENDAR_PUB.Calendar_Type_Record
687 , x_Return_Status          OUT NOCOPY  VARCHAR2
688 , x_Msg_Count              OUT NOCOPY  NUMBER
689 , x_Msg_Data               OUT NOCOPY  VARCHAR2
690 )IS
691 
692 BEGIN
693   FND_MSG_PUB.Initialize;
694   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
695 
696  SELECT  calendar_id
697        , edw_flag
698        , edw_calendar_id
699        , edw_calendar_type_id
700        , fiscal_year
701        , fiscal_change
702        , range_yr_mod
703        , current_year
704        , start_month
705        , start_day
706        , name
707        , help
708        , created_by
709        , creation_date
710        , last_updated_by
711        , last_update_date
712        , last_update_login
713   INTO   x_Calendar_Record.Calendar_Id
714        , x_Calendar_Record.Edw_Flag
715        , x_Calendar_Record.Edw_Calendar_Id
716        , x_Calendar_Record.Edw_Calendar_Type_Id
717        , x_Calendar_Record.Fiscal_Year
718        , x_Calendar_Record.Fiscal_Change
719        , x_Calendar_Record.Range_Yr_Mod
720        , x_Calendar_Record.Current_Year
721        , x_Calendar_Record.Start_Month
722        , x_Calendar_Record.Start_Day
723        , x_Calendar_Record.Name
724        , x_Calendar_Record.Help
725        , x_Calendar_Record.Created_By
726        , x_Calendar_Record.Creation_Date
727        , x_Calendar_Record.Last_Updated_By
728        , x_Calendar_Record.Last_Update_Date
729        , x_Calendar_Record.Last_Update_Login
730   FROM  bsc_sys_calendars_vl
731   WHERE calendar_id = p_Calendar_Record.Calendar_Id;
732 
733 
734   IF(p_Calendar_Record.Name <> x_Calendar_Record.Name) THEN
735     x_Calendar_Record.Name := p_Calendar_Record.Name;
736   END IF;
737 
738   IF(p_Calendar_Record.Help <> x_Calendar_Record.Help) THEN
739     x_Calendar_Record.Help := p_Calendar_Record.Help;
740   END IF;
741 
742   IF(p_Calendar_Record.Fiscal_Year <> x_Calendar_Record.Fiscal_Year) THEN
743     x_Calendar_Record.Fiscal_Year := p_Calendar_Record.Fiscal_Year;
744   END IF;
745 
746   IF(p_Calendar_Record.Current_Year <> x_Calendar_Record.Current_Year) THEN
747     x_Calendar_Record.Current_Year := p_Calendar_Record.Current_Year;
748   END IF;
749 
750   IF(p_Calendar_Record.Start_Month <> x_Calendar_Record.Start_Month) THEN
751     x_Calendar_Record.Start_Month := p_Calendar_Record.Start_Month;
752   END IF;
753 
754   -- added for Bug#4565308
755   IF(p_Calendar_Record.Start_Day <> x_Calendar_Record.Start_Day) THEN
756     x_Calendar_Record.Start_Day := p_Calendar_Record.Start_Day;
757   END IF;
758 
759   IF(p_Calendar_Record.Last_Update_Date IS NULL ) THEN
760       x_Calendar_Record.Last_Update_Date := SYSDATE;
761   ELSE
762       x_Calendar_Record.Last_Update_Date := p_Calendar_Record.Last_Update_Date;
763   END IF;
764 
765   IF (p_Calendar_Record.Last_Updated_By IS NULL) THEN
766     x_Calendar_Record.Last_Updated_By := FND_GLOBAL.USER_ID;
767   ELSE
768     x_Calendar_Record.Last_Updated_By := p_Calendar_Record.Last_Updated_By;
769   END IF;
770 
771   IF (p_Calendar_Record.Last_Update_Login IS NULL) THEN
772     x_Calendar_Record.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
773   ELSE
774     x_Calendar_Record.Last_Update_Login := p_Calendar_Record.Last_Update_Login;
775   END IF;
776 
777   x_Calendar_Record.Dim_Short_Name  := p_Calendar_Record.Dim_Short_Name;
778   x_Calendar_Record.Application_Id  := p_Calendar_Record.Application_Id;
779 
780 
781 EXCEPTION
782   WHEN NO_DATA_FOUND THEN
783     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784     IF (x_msg_data IS NOT NULL) THEN
785       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Retrieve_And_Populate_Cal_Rec';
786     ELSE
787       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Retrieve_And_Populate_Cal_Rec ';
788     END IF;
789   WHEN OTHERS THEN
790     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791     IF (x_msg_data IS NOT NULL) THEN
792       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Retrieve_And_Populate_Cal_Rec ';
793     ELSE
794       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Retrieve_And_Populate_Cal_Rec ';
795     END IF;
796 END Retrieve_And_Populate_Cal_Rec;
797 /*************************************************************************************/
798 
799 PROCEDURE Create_Calendar_Post_Action
800 ( p_Api_Version            IN          NUMBER
801 , p_Commit                 IN          VARCHAR2
802 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
803 , x_Return_Status          OUT NOCOPY  VARCHAR2
804 , x_Msg_Count              OUT NOCOPY  NUMBER
805 , x_Msg_Data               OUT NOCOPY  VARCHAR2
806 )IS
807 
808 BEGIN
809   SAVEPOINT CreateCalendarPostActionSP;
810   FND_MSG_PUB.Initialize;
811   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
812 
813   BSC_CALENDAR_PUB.Create_Periodicities_Calendar
814   ( p_Api_Version      => p_Api_Version
815   , p_Commit           => p_Commit
816   , p_Calendar_Record  => p_Calendar_Record
817   , x_Return_Status    => x_Return_Status
818   , x_Msg_Count        => x_Msg_Count
819   , x_Msg_Data         => x_Msg_Data
820   );
821   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
822     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
823   END IF;
824 
825   BSC_UPDATE_UTIL.Populate_Calendar_Tables
826   ( p_commit         => p_Commit
827   , p_calendar_id    => p_Calendar_Record.Calendar_Id
828   , x_return_status  => x_Return_Status
829   , x_msg_count      => x_Msg_Count
830   , x_msg_data       => x_Msg_Data
831   );
832   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
833     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
834   END IF;
835 
836 EXCEPTION
837   WHEN FND_API.G_EXC_ERROR THEN
838     ROLLBACK TO CreateCalendarPostActionSP;
839     IF (x_msg_data IS NULL) THEN
840       FND_MSG_PUB.Count_And_Get
841       ( p_encoded   =>  FND_API.G_FALSE
842       , p_count     =>  x_msg_count
843       , p_data      =>  x_msg_data
844       );
845     END IF;
846     x_return_status :=  FND_API.G_RET_STS_ERROR;
847   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
848     ROLLBACK TO CreateCalendarPostActionSP;
849     IF (x_msg_data IS NULL) THEN
850       FND_MSG_PUB.Count_And_Get
851       ( p_encoded   =>  FND_API.G_FALSE
852       , p_count     =>  x_msg_count
853       , p_data      =>  x_msg_data
854       );
855     END IF;
856     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
857   WHEN NO_DATA_FOUND THEN
858     ROLLBACK TO CreateCalendarPostActionSP;
859      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
860     IF (x_msg_data IS NOT NULL) THEN
861       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Calendar_Post_Action';
862     ELSE
863       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Calendar_Post_Action ';
864     END IF;
865   WHEN OTHERS THEN
866     ROLLBACK TO CreateCalendarPostActionSP;
867     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
868     IF (x_msg_data IS NOT NULL) THEN
869       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Calendar_Post_Action ';
870     ELSE
871       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Calendar_Post_Action ';
872     END IF;
873 
874 END Create_Calendar_Post_Action;
875 
876 /*************************************************************************************/
877 
878 PROCEDURE Fill_Default_Values_Create_Cal
879 ( p_Api_Version            IN          NUMBER
880 , p_Commit                 IN          VARCHAR2
881 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
882 , x_Calendar_Record        OUT NOCOPY  BSC_CALENDAR_PUB.Calendar_Type_Record
883 , x_Return_Status          OUT NOCOPY  VARCHAR2
884 , x_Msg_Count              OUT NOCOPY  NUMBER
885 , x_Msg_Data               OUT NOCOPY  VARCHAR2
886 )IS
887 
888 BEGIN
889   FND_MSG_PUB.Initialize;
890   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
891 
892     x_Calendar_Record := p_Calendar_Record;
893 
894   IF (p_Calendar_Record.Calendar_Id IS NULL) THEN
895     x_Calendar_Record.Calendar_Id := BSC_PERIODS_UTILITY_PKG.Get_Next_Calendar_Id;
896   ELSE
897     x_Calendar_Record.Calendar_Id := p_Calendar_Record.Calendar_Id;
898   END IF;
899 
900   IF(p_Calendar_Record.Name IS NULL) THEN
901     x_Calendar_Record.Name := BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'CALENDAR') ||' '|| x_Calendar_Record.Calendar_Id;
902   ELSE
903     x_Calendar_Record.Name := P_Calendar_Record.Name;
904   END IF;
905 
906   IF(p_Calendar_Record.Help IS NULL) THEN
907     x_Calendar_Record.Help := p_Calendar_Record.Name;
908   ELSE
909     x_Calendar_Record.Help := P_Calendar_Record.Help;
910   END IF;
911 
912   IF(p_Calendar_Record.Edw_Flag IS NULL) THEN
913     x_Calendar_Record.Edw_Flag := BSC_PERIODS_UTILITY_PKG.C_CONSTANT_ZERO;
914   ELSE
915     x_Calendar_Record.Edw_Flag := p_Calendar_Record.Edw_Flag;
916   END IF;
917 
918   IF(p_Calendar_Record.Edw_Calendar_Id IS NOT NULL) THEN
919     x_Calendar_Record.Edw_Calendar_Id := p_Calendar_Record.Edw_Calendar_Id;
920   END IF;
921 
922   IF(p_Calendar_Record.Edw_Calendar_Type_Id IS NOT NULL) THEN
923     x_Calendar_Record.Edw_Calendar_Type_Id := p_Calendar_Record.Edw_Calendar_Type_Id;
924   END IF;
925 
926   IF(p_Calendar_Record.Fiscal_Year IS NULL) THEN
927     x_Calendar_Record.Fiscal_Year := to_char(SYSDATE,'YYYY');
928   ELSE
929     x_Calendar_Record.Fiscal_Year := p_Calendar_Record.Fiscal_Year;
930   END IF;
931 
932   IF(p_Calendar_Record.Fiscal_Change IS NULL) THEN
933     x_Calendar_Record.Fiscal_Change := BSC_PERIODS_UTILITY_PKG.C_CONSTANT_ZERO;
934   ELSE
935     x_Calendar_Record.Fiscal_Change  := p_Calendar_Record.Fiscal_Change;
936   END IF;
937 
938   IF(p_Calendar_Record.Range_Yr_Mod IS NULL) THEN
939     x_Calendar_Record.Range_Yr_Mod := BSC_PERIODS_UTILITY_PKG.C_CONSTANT_ZERO;
940   ELSE
941     x_Calendar_Record.Range_Yr_Mod := p_Calendar_Record.Range_Yr_Mod;
942   END IF;
943 
944   IF(p_Calendar_Record.Start_Month IS NULL) THEN
945     x_Calendar_Record.Start_Month := BSC_PERIODS_UTILITY_PKG.C_DEFAULT_START_MONTH;
946   ELSE
947     x_Calendar_Record.Start_Month := p_Calendar_Record.Start_Month;
948   END IF;
949 
950   IF(p_Calendar_Record.Start_Day IS NULL) THEN
951     x_Calendar_Record.Start_Day := BSC_PERIODS_UTILITY_PKG.C_DEFAULT_START_DAY;
952   ELSE
953     x_Calendar_Record.Start_Day  := p_Calendar_Record.Start_Day;
954   END IF;
955 
956   IF(p_Calendar_Record.Dim_Short_Name IS NULL) THEN
957     x_Calendar_Record.Dim_Short_Name := BSC_PERIODS_UTILITY_PKG.Get_Unique_Short_Name();
958   ELSE
959     x_Calendar_Record.Dim_Short_Name  := p_Calendar_Record.Dim_Short_Name;
960   END IF;
961 
962   IF(p_Calendar_Record.Application_Id IS NULL) THEN
963     x_Calendar_Record.Application_Id := BSC_PERIODS_UTILITY_PKG.C_BSC_APPLICATION_ID;
964   ELSE
965     x_Calendar_Record.Application_Id  := p_Calendar_Record.Application_Id;
966   END IF;
967 
968 
969 EXCEPTION
970   WHEN FND_API.G_EXC_ERROR THEN
971     IF (x_msg_data IS NULL) THEN
972       FND_MSG_PUB.Count_And_Get
973       ( p_encoded   =>  FND_API.G_FALSE
974       , p_count     =>  x_msg_count
975       , p_data      =>  x_msg_data
976       );
977     END IF;
978     x_return_status :=  FND_API.G_RET_STS_ERROR;
979   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
980     IF (x_msg_data IS NULL) THEN
981       FND_MSG_PUB.Count_And_Get
982       ( p_encoded   =>  FND_API.G_FALSE
983       , p_count     =>  x_msg_count
984       , p_data      =>  x_msg_data
985       );
986     END IF;
987     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988   WHEN NO_DATA_FOUND THEN
989     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990     IF (x_msg_data IS NOT NULL) THEN
991       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Fill_Default_Values_Create_Cal';
992     ELSE
993       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Fill_Default_Values_Create_Cal ';
994     END IF;
995   WHEN OTHERS THEN
996     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997     IF (x_msg_data IS NOT NULL) THEN
998       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Fill_Default_Values_Create_Cal ';
999     ELSE
1000       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Fill_Default_Values_Create_Cal';
1001     END IF;
1002 END Fill_Default_Values_Create_Cal;
1003 
1004 /*****************************************************************************************/
1005 
1006 PROCEDURE Update_Calendar_Post_Action
1007 ( p_Api_Version            IN          NUMBER
1008 , p_Commit                 IN          VARCHAR2
1009 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
1010 , x_Return_Status          OUT NOCOPY  VARCHAR2
1011 , x_Msg_Count              OUT NOCOPY  NUMBER
1012 , x_Msg_Data               OUT NOCOPY  VARCHAR2
1013 )IS
1014 l_System_Stage   bsc_sys_init.property_value%TYPE;
1015 
1016 BEGIN
1017   SAVEPOINT UpdateCalendarPostActionSP;
1018   FND_MSG_PUB.Initialize;
1019   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1020 
1021   SELECT property_value
1022   INTO   l_System_Stage
1023   from   bsc_sys_init
1024   WHERE  property_code =  BSC_PERIODS_UTILITY_PKG.C_SYSTEM_STAGE;
1025 
1026   IF(l_System_Stage = 2 AND  Get_Fiscal_Year() = 1) THEN
1027     BSC_CALENDAR_PVT.Update_Fiscal_Change
1028     ( p_Api_Version            => p_Api_Version
1029     , p_Commit                 => p_Commit
1030     , p_Calendar_Id            => p_Calendar_Record.Calendar_Id
1031     , x_Return_Status          => x_Return_Status
1032     , x_Msg_Count              => x_Msg_Count
1033     , x_Msg_Data               => x_Msg_Data
1034     );
1035     IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1036       RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1037     END IF;
1038 
1039     BSC_CALENDAR_PUB.Flag_Changes_For_Objectives
1040     ( p_Api_Version            => p_Api_Version
1041     , p_Commit                 => p_Commit
1042     , p_Calendar_Id            => p_Calendar_Record.Calendar_Id
1043     , x_Return_Status          => x_Return_Status
1044     , x_Msg_Count              => x_Msg_Count
1045     , x_Msg_Data               => x_Msg_Data
1046     );
1047     IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1048       RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1049     END IF;
1050 
1051     BSC_UPDATE_UTIL.Populate_Calendar_Tables
1052     ( p_commit         => p_Commit
1053     , p_calendar_id    => p_Calendar_Record.Calendar_Id
1054     , x_return_status  => x_Return_Status
1055     , x_msg_count      => x_Msg_Count
1056     , x_msg_data       => x_Msg_Data
1057     );
1058     IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1059       RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1060     END IF;
1061 
1062   END IF;
1063 
1064 
1065 EXCEPTION
1066   WHEN FND_API.G_EXC_ERROR THEN
1067     ROLLBACK TO UpdateCalendarPostActionSP;
1068     IF (x_msg_data IS NULL) THEN
1069       FND_MSG_PUB.Count_And_Get
1070       ( p_encoded   =>  FND_API.G_FALSE
1071       , p_count     =>  x_msg_count
1072       , p_data      =>  x_msg_data
1073       );
1074     END IF;
1075     x_return_status :=  FND_API.G_RET_STS_ERROR;
1076   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1077     ROLLBACK TO UpdateCalendarPostActionSP;
1078     IF (x_msg_data IS NULL) THEN
1079       FND_MSG_PUB.Count_And_Get
1080       ( p_encoded   =>  FND_API.G_FALSE
1081       , p_count     =>  x_msg_count
1082       , p_data      =>  x_msg_data
1083       );
1084     END IF;
1085     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1086   WHEN NO_DATA_FOUND THEN
1087     ROLLBACK TO UpdateCalendarPostActionSP;
1088     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1089     IF (x_msg_data IS NOT NULL) THEN
1090       x_msg_data      :=  x_msg_data ||'BSC_CALENDAR_PUB.Update_Calendar_Post_Action ';
1091     ELSE
1092       x_msg_data      :=  SQLERRM || 'at BSC_CALENDAR_PUB.Update_Calendar_Post_Action ';
1093     END IF;
1094   WHEN OTHERS THEN
1095     ROLLBACK TO UpdateCalendarPostActionSP;
1096     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1097     IF (x_msg_data IS NOT NULL) THEN
1098       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Update_Calendar_Post_Action ';
1099     ELSE
1100       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Update_Calendar_Post_Action ';
1101     END IF;
1102 END Update_Calendar_Post_Action;
1103 
1104 /*****************************************************************************************/
1105 
1106 PROCEDURE Flag_Changes_For_Objectives
1107 ( p_Api_Version            IN          NUMBER
1108 , p_Commit                 IN          VARCHAR2
1109 , p_Calendar_Id            IN          NUMBER
1110 , x_Return_Status          OUT NOCOPY  VARCHAR2
1111 , x_Msg_Count              OUT NOCOPY  NUMBER
1112 , x_Msg_Data               OUT NOCOPY  VARCHAR2
1113 )IS
1114 
1115 
1116 CURSOR C_FLAG_OBJECTIVES IS
1117   SELECT DISTINCT(K.indicator)
1118   FROM   bsc_kpi_periodicities K,
1119          bsc_sys_periodicities S
1120   WHERE  S.periodicity_id = K.periodicity_id
1121   AND    S.calendar_id    = p_Calendar_Id;
1122 
1123 BEGIN
1124   FND_MSG_PUB.Initialize;
1125   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1126 
1127   FOR CD IN C_FLAG_OBJECTIVES LOOP
1128     BSC_DESIGNER_PVT.ActionFlag_Change(CD.indicator,BSC_DESIGNER_PVT.G_ActionFlag.Update_Update);
1129 
1130     IF(BSC_PERIODS_UTILITY_PKG.Check_Error_Message('ActionFlag_Change')) THEN
1131       FND_MESSAGE.SET_NAME('BSC','BSC_ERROR_ACTION_FLAG_CHANGE');
1132       FND_MSG_PUB.ADD;
1133       RAISE FND_API.G_EXC_ERROR;
1134     END IF;
1135 
1136   END LOOP;
1137 
1138 EXCEPTION
1139   WHEN FND_API.G_EXC_ERROR THEN
1140     IF (x_msg_data IS NULL) THEN
1141       FND_MSG_PUB.Count_And_Get
1142       ( p_encoded   =>  FND_API.G_FALSE
1143       , p_count     =>  x_msg_count
1144       , p_data      =>  x_msg_data
1145       );
1146     END IF;
1147     x_return_status :=  FND_API.G_RET_STS_ERROR;
1148   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1149     IF (x_msg_data IS NULL) THEN
1150       FND_MSG_PUB.Count_And_Get
1151       ( p_encoded   =>  FND_API.G_FALSE
1152       , p_count     =>  x_msg_count
1153       , p_data      =>  x_msg_data
1154       );
1155     END IF;
1156     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1157   WHEN NO_DATA_FOUND THEN
1158     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1159     IF (x_msg_data IS NOT NULL) THEN
1160       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Flag_Changes_For_Objectives ';
1161     ELSE
1162       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Flag_Changes_For_Objectives ';
1163     END IF;
1164   WHEN OTHERS THEN
1165     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166     IF (x_msg_data IS NOT NULL) THEN
1167       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Flag_Changes_For_Objectives ';
1168     ELSE
1169       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Flag_Changes_For_Objectives ';
1170     END IF;
1171 END Flag_Changes_For_Objectives;
1172 /*****************************************************************************************/
1173 
1174 PROCEDURE Create_Periodicities_Calendar
1175 ( p_Api_Version            IN          NUMBER
1176 , p_Commit                 IN          VARCHAR2
1177 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
1178 , x_Return_Status          OUT NOCOPY  VARCHAR2
1179 , x_Msg_Count              OUT NOCOPY  NUMBER
1180 , x_Msg_Data               OUT NOCOPY  VARCHAR2
1181 )IS
1182 l_Calendar_Record     BSC_CALENDAR_PUB.Calendar_Type_Record;
1183 h_tmp_array           BSC_UPDATE_UTIL.t_array_of_number;
1184 h_count               NUMBER;
1185 h_new_per_id          NUMBER;
1186 h_periodicity_type    NUMBER;
1187 h_new_source          VARCHAR2(200);
1188 l_Periodicity_Record  BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
1189 
1190 l_Periodicity_Ids    BSC_UTILITY.VARCHAR_TABLETYPE;
1191 l_Periodicity_Count  NUMBER;
1192 l_Count              NUMBER;
1193 
1194 CURSOR c_base_per (p_calendar_id NUMBER, p_custom_code NUMBER) IS
1195   SELECT PERIODICITY_ID
1196   FROM   BSC_SYS_PERIODICITIES
1197   WHERE  CALENDAR_ID = p_calendar_id
1198   AND    CUSTOM_CODE < p_custom_code
1199   ORDER  BY PERIODICITY_ID;
1200 
1201 CURSOR c_new_per (p_calendar_id NUMBER) IS
1202   SELECT PERIODICITY_ID,SOURCE
1203   FROM   BSC_SYS_PERIODICITIES
1204   WHERE  CALENDAR_ID = p_calendar_id
1205   ORDER  BY PERIODICITY_ID;
1206 
1207 CURSOR c_get_per (p_calendar_id NUMBER, p_periodicity_type NUMBER) IS
1208   SELECT PERIODICITY_ID
1209   FROM   BSC_SYS_PERIODICITIES
1210   WHERE  CALENDAR_ID = p_calendar_id
1211   AND    PERIODICITY_TYPE = p_periodicity_type;
1212 
1213 BEGIN
1214   FND_MSG_PUB.Initialize;
1215   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1216   BSC_APPS.Init_Bsc_Apps;
1217   l_Count := 1;
1218 
1219   IF (p_Calendar_Record.Base_Periodicities_Ids IS NOT NULL) THEN
1220     BSC_UTILITY.Parse_String
1221     ( p_List         => p_Calendar_Record.Base_Periodicities_Ids
1222     , p_Separator    => BSC_PERIODS_UTILITY_PKG.C_COMMA_SEPARATOR
1223     , p_List_Data    => l_Periodicity_Ids
1224     , p_List_number  => l_Periodicity_Count
1225     );
1226   END IF;
1227 
1228   FOR CD IN c_base_per(1, 1) LOOP
1229     SELECT  NULL
1230           , num_of_periods
1231           , source
1232           , num_of_subperiods
1233           , period_col_name
1234           , subperiod_col_name
1235           , yearly_flag
1236           , edw_flag
1237           , p_Calendar_Record.Calendar_Id
1238           , edw_periodicity_id
1239           , custom_code
1240           , db_column_name
1241           , periodicity_type
1242           , name
1243     INTO    l_Periodicity_Record.Periodicity_Id
1244           , l_Periodicity_Record.Num_Of_Periods
1245           , l_Periodicity_Record.Source
1246           , l_Periodicity_Record.Num_Of_Subperiods
1247           , l_Periodicity_Record.Period_Col_Name
1248           , l_Periodicity_Record.Subperiod_Col_Name
1249           , l_Periodicity_Record.Yearly_Flag
1250           , l_Periodicity_Record.Edw_Flag
1251           , l_Periodicity_Record.Calendar_Id
1252           , l_Periodicity_Record.Edw_Periodicity_Id
1253           , l_Periodicity_Record.Custom_Code
1254           , l_Periodicity_Record.Db_Column_Name
1255           , l_Periodicity_Record.Periodicity_Type
1256           , l_Periodicity_Record.Name
1257     FROM bsc_sys_periodicities_vl
1258     WHERE periodicity_id = CD.periodicity_id;
1259 
1260     IF (p_Calendar_Record.Base_Periodicities_Ids IS NOT NULL) THEN
1261         IF (l_Periodicity_Count > 0) THEN
1262             l_Periodicity_Record.Periodicity_Id := l_Periodicity_Ids(l_Count);
1263         END IF;
1264 
1265         l_Periodicity_Count := l_Periodicity_Count - 1;
1266         l_Count := l_Count + 1;
1267     END IF;
1268     l_Periodicity_Record.ForceRunPopulateCalendar := FND_API.G_FALSE;
1269 
1270     BSC_PERIODICITIES_PUB.Create_Periodicity
1271     ( p_Api_Version             => p_Api_Version
1272     , p_Commit                  => p_Commit
1273     , p_Periodicities_Rec_Type  => l_Periodicity_Record
1274     , x_Return_Status           => x_Return_Status
1275     , x_Msg_Count               => x_Msg_Count
1276     , x_Msg_Data                => x_Msg_Data
1277     );
1278 
1279   END LOOP;
1280   FOR cd_new_per IN c_new_per(p_Calendar_Record.Calendar_Id) LOOP
1281     h_new_source := '';
1282     IF cd_new_per.source IS NOT NULL THEN
1283       h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(cd_new_per.source,h_tmp_array,',');
1284       FOR h_i IN 1.. h_count LOOP
1285         h_periodicity_type := h_tmp_array(h_i);
1286 
1287         OPEN c_get_per(p_Calendar_Record.Calendar_Id, h_periodicity_type);
1288         FETCH c_get_per INTO h_new_per_id;
1289         IF c_get_per%FOUND THEN
1290           IF h_new_source IS NOT NULL THEN
1291             h_new_source := h_new_source  || ',' || h_new_per_id;
1292           ELSE
1293             h_new_source :=h_new_per_id;
1294           END IF;
1295         END IF;
1296         CLOSE c_get_per;
1297       END LOOP;
1298 
1299       UPDATE bsc_sys_periodicities
1300       SET    source = h_new_source
1301       WHERE  periodicity_id = cd_new_per.periodicity_id;
1302     END IF;
1303   END LOOP;
1304 
1305 
1306 
1307 EXCEPTION
1308   WHEN FND_API.G_EXC_ERROR THEN
1309     IF (x_msg_data IS NULL) THEN
1310       FND_MSG_PUB.Count_And_Get
1311       ( p_encoded   =>  FND_API.G_FALSE
1312       , p_count     =>  x_msg_count
1313       , p_data      =>  x_msg_data
1314       );
1315     END IF;
1316     x_return_status :=  FND_API.G_RET_STS_ERROR;
1317   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1318     IF (x_msg_data IS NULL) THEN
1319       FND_MSG_PUB.Count_And_Get
1320       ( p_encoded   =>  FND_API.G_FALSE
1321       , p_count     =>  x_msg_count
1322       , p_data      =>  x_msg_data
1323       );
1324     END IF;
1325     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1326   WHEN NO_DATA_FOUND THEN
1327     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1328     IF (x_msg_data IS NOT NULL) THEN
1329       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Periodicities_Calendar ';
1330     ELSE
1331       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Periodicities_Calendar ';
1332     END IF;
1333   WHEN OTHERS THEN
1334     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335     IF (x_msg_data IS NOT NULL) THEN
1336       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Periodicities_Calendar ';
1337     ELSE
1338       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Periodicities_Calendar ';
1339     END IF;
1340 END Create_Periodicities_Calendar;
1341 /*********************************************************************************/
1342 PROCEDURE Create_Calendar_Dimension
1343 ( p_Api_Version            IN          NUMBER
1344 , p_Commit                 IN          VARCHAR2
1345 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
1346 , x_Return_Status          OUT NOCOPY  VARCHAR2
1347 , x_Msg_Count              OUT NOCOPY  NUMBER
1348 , x_Msg_Data               OUT NOCOPY  VARCHAR2
1349 )IS
1350 l_Short_Name  bsc_sys_calendars_b.short_name%TYPE;
1351 BEGIN
1352   FND_MSG_PUB.Initialize;
1353   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1354 
1355   IF(p_Calendar_Record.Dim_Short_Name IS NULL) THEN
1356     l_Short_Name := BSC_PERIODS_UTILITY_PKG.Get_Unique_Short_Name();
1357   ELSE
1358     l_Short_Name := p_Calendar_Record.Dim_Short_Name;
1359   END IF;
1360 
1361   BSC_BIS_DIMENSION_PUB.Create_Dimension
1362   ( p_commit                => p_Commit
1363   , p_dim_short_name        => l_Short_Name
1364   , p_display_name          => p_Calendar_Record.Name
1365   , p_description           => p_Calendar_Record.Help
1366   , p_dim_obj_short_names   => NULL
1367   , p_application_id        => p_Calendar_Record.Application_Id
1368   , p_create_view           => 0
1369   , x_return_status         => x_Return_Status
1370   , x_msg_count             => x_Msg_Count
1371   , x_msg_data              => x_Msg_Data
1372   );
1373   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1374     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1375   END IF;
1376 
1377   UPDATE bsc_sys_calendars_b
1378   SET    short_name  = l_Short_Name
1379   WHERE  calendar_id = p_Calendar_Record.calendar_id;
1380 
1381 
1382 EXCEPTION
1383   WHEN FND_API.G_EXC_ERROR THEN
1384     IF (x_msg_data IS NULL) THEN
1385       FND_MSG_PUB.Count_And_Get
1386       ( p_encoded   =>  FND_API.G_FALSE
1387       , p_count     =>  x_msg_count
1388       , p_data      =>  x_msg_data
1389       );
1390     END IF;
1391     x_return_status :=  FND_API.G_RET_STS_ERROR;
1392   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1393     IF (x_msg_data IS NULL) THEN
1394       FND_MSG_PUB.Count_And_Get
1395       ( p_encoded   =>  FND_API.G_FALSE
1396       , p_count     =>  x_msg_count
1397       , p_data      =>  x_msg_data
1398       );
1399     END IF;
1400     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1401   WHEN NO_DATA_FOUND THEN
1402     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1403     IF (x_msg_data IS NOT NULL) THEN
1404       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Calendar_Dimension ';
1405     ELSE
1406       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Calendar_Dimension ';
1407     END IF;
1408   WHEN OTHERS THEN
1409     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1410     IF (x_msg_data IS NOT NULL) THEN
1411       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Create_Calendar_Dimension ';
1412     ELSE
1413       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Create_Calendar_Dimension ';
1414     END IF;
1415 END Create_Calendar_Dimension;
1416 /*******************************************************************************/
1417 PROCEDURE Update_Calendar_Dimension
1418 ( p_Api_Version            IN          NUMBER
1419 , p_Commit                 IN          VARCHAR2
1420 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
1421 , x_Return_Status          OUT NOCOPY  VARCHAR2
1422 , x_Msg_Count              OUT NOCOPY  NUMBER
1423 , x_Msg_Data               OUT NOCOPY  VARCHAR2
1424 )IS
1425 CURSOR C_comma_objNames IS
1426 SELECT obj_short_name
1427 FROM   bsc_bis_dim_obj_by_dim_vl
1428 WHERE  dim_short_name = p_Calendar_Record.Dim_Short_Name;
1429 
1430 
1431 l_Short_Name  bsc_sys_calendars_b.short_name%TYPE;
1432 l_dim_obj_shortNames    VARCHAR2(32000);
1433 BEGIN
1434   FND_MSG_PUB.Initialize;
1435   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1436 
1437   FOR CD IN C_comma_objNames LOOP
1438     IF(l_dim_obj_shortNames IS NULL) THEN
1439       l_dim_obj_shortNames := CD.obj_short_name;
1440     ELSE
1441       l_dim_obj_shortNames := l_dim_obj_shortNames || ',' ||CD.obj_short_name;
1442     END IF;
1443   END LOOP;
1444 
1445 
1446   BSC_BIS_DIMENSION_PUB.Update_Dimension
1447   ( p_commit               => p_Commit
1448   , p_dim_short_name       => p_Calendar_Record.Dim_Short_Name
1449   , p_display_name         => p_Calendar_Record.Name
1450   , p_description          => p_Calendar_Record.Help
1451   , p_application_id       => p_Calendar_Record.Application_Id
1452   , p_dim_obj_short_names  => l_dim_obj_shortNames
1453   , p_time_stamp           => NULL
1454   , x_return_status        => x_return_status
1455   , x_msg_count            => x_msg_count
1456   , x_msg_data             => x_msg_data
1457   );
1458 
1459   IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1460     RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1461   END IF;
1462 
1463 
1464 EXCEPTION
1465   WHEN FND_API.G_EXC_ERROR THEN
1466     IF (x_msg_data IS NULL) THEN
1467       FND_MSG_PUB.Count_And_Get
1468       ( p_encoded   =>  FND_API.G_FALSE
1469       , p_count     =>  x_msg_count
1470       , p_data      =>  x_msg_data
1471       );
1472     END IF;
1473     x_return_status :=  FND_API.G_RET_STS_ERROR;
1474   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475     IF (x_msg_data IS NULL) THEN
1476       FND_MSG_PUB.Count_And_Get
1477       ( p_encoded   =>  FND_API.G_FALSE
1478       , p_count     =>  x_msg_count
1479       , p_data      =>  x_msg_data
1480       );
1481     END IF;
1482     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1483   WHEN NO_DATA_FOUND THEN
1484     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1485     IF (x_msg_data IS NOT NULL) THEN
1486       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Update_Calendar_Dimension ';
1487     ELSE
1488       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Update_Calendar_Dimension ';
1489     END IF;
1490   WHEN OTHERS THEN
1491     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1492     IF (x_msg_data IS NOT NULL) THEN
1493       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Update_Calendar_Dimension ';
1494     ELSE
1495       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Update_Calendar_Dimension ';
1496     END IF;
1497 END Update_Calendar_Dimension;
1498 
1499 
1500 FUNCTION get_production_obj_having_cal(
1501   p_cal_id  IN   bsc_kpis_b.calendar_id%TYPE
1502   )
1503 RETURN VARCHAR2 IS
1504   l_result        VARCHAR2(32000);
1505   CURSOR c_objs IS
1506     SELECT  indicator, name, calendar_id
1507     FROM bsc_kpis_vl
1508     WHERE prototype_flag = 0
1509       AND calendar_id    = p_cal_id;
1510 BEGIN
1511   l_result := NULL;
1512   IF (p_cal_id IS NOT NULL) THEN
1513     FOR cd IN c_objs LOOP
1514        IF (l_result IS NULL) THEN
1515            l_result := cd.name;
1516        ELSE
1517            l_result := l_result || ',' || cd.name;
1518        END IF;
1519     END LOOP;
1520   END IF;
1521   RETURN l_result;
1522 END get_production_obj_having_cal;
1523 
1524 PROCEDURE comp_leapyear_prioryear(
1525   p_calid IN NUMBER,
1526   p_cyear IN NUMBER,
1527   p_pyear IN NUMBER,
1528   x_result OUT nocopy NUMBER
1529  )IS
1530 lday number :=0;
1531 lmonth number:=0;
1532 
1533 CURSOR diff IS
1534 SELECT day30, MONTH
1535 FROM bsc_db_calendar
1536 WHERE calendar_id = p_calid
1537 AND   year =  p_cyear
1538 MINUS
1539 SELECT day30, MONTH
1540 FROM bsc_db_calendar
1541 WHERE calendar_id = p_calid
1542 AND   year = p_pyear;
1543 BEGIN
1544   OPEN diff;
1545   IF diff%NOTFOUND THEN
1546     x_result := -1;
1547   ELSE
1548     FETCH diff into lday, lmonth;
1549   END IF;
1550   CLOSE diff;
1551   IF lday <>0 THEN
1552    SELECT day365
1553    INTO x_result
1554    FROM bsc_db_calendar
1555    WHERE calendar_id = p_calid
1556    AND  year  = p_cyear
1557    AND  day30 = lday
1558    AND  MONTH = lmonth;
1559  END IF;
1560 EXCEPTION
1561  WHEN OTHERS THEN
1562    x_result := -1;
1563 END comp_leapyear_prioryear;
1564 
1565 
1566 END BSC_CALENDAR_PUB;