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_PVT

Source


1 PACKAGE BODY BSC_CALENDAR_PVT AS
2 /* $Header: BSCVCALB.pls 120.2 2005/11/30 02:47:25 kyadamak noship $ */
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 |     BSCVCALB.pls                                                                 |
10 REM |                                                                                  |
11 REM | DESCRIPTION                                                                      |
12 REM |     Module: Private package for populating the calendar  tables                  |
13 REM | NOTES                                                                            |
14 REM | 07-JUN-2005 Krishna  Created.                                                    |
15 REM | 31-AUG-2004 Aditya Rao fixed Bug#4565308 for START_DAY of fiscal year            |
16 REM | 29-NOV-2005 kyadamak Added API Update_PeriodNames_In_Calendar for Enh#4711274    |
17 REM +==================================================================================+
18 */
19 
20 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_CALENDAR_PVT';
21 
22 PROCEDURE Create_Calendar
23 ( p_Api_Version            IN          NUMBER
24 , p_Commit                 IN          VARCHAR2
25 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
26 , x_Return_Status          OUT NOCOPY  VARCHAR2
27 , x_Msg_Count              OUT NOCOPY  NUMBER
28 , x_Msg_Data               OUT NOCOPY  VARCHAR2
29 )IS
30 
31 BEGIN
32   SAVEPOINT CreateCalendarSP;
33   FND_MSG_PUB.Initialize;
34   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
35 
36   INSERT INTO bsc_sys_calendars_b
37   ( calendar_id
38   , edw_flag
39   , edw_calendar_id
40   , edw_calendar_type_id
41   , fiscal_year
42   , fiscal_change
43   , range_yr_mod
44   , current_year
45   , start_month
46   , start_day
47   , created_by
48   , creation_date
49   , last_updated_by
50   , last_update_date
51   , last_update_login
52   , short_name
53   )
54   VALUES
55   ( p_Calendar_Record.Calendar_Id
56   , p_Calendar_Record.Edw_Flag
57   , p_Calendar_Record.Edw_Calendar_Id
58   , p_Calendar_Record.Edw_Calendar_Type_Id
59   , p_Calendar_Record.Fiscal_Year
60   , p_Calendar_Record.Fiscal_Change
61   , p_Calendar_Record.Range_Yr_Mod
62   , p_Calendar_Record.Current_Year
63   , p_Calendar_Record.Start_Month
64   , p_Calendar_Record.Start_Day
65   , NVL(p_Calendar_Record.Created_By,FND_GLOBAL.USER_ID)
66   , NVL(p_Calendar_Record.Creation_Date,SYSDATE)
67   , NVL(p_Calendar_Record.Last_Updated_By,FND_GLOBAL.USER_ID)
68   , NVL(p_Calendar_Record.Last_Update_Date,SYSDATE)
69   , NVL(p_Calendar_Record.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
70   , p_Calendar_Record.Dim_Short_Name
71   );
72 
73   INSERT INTO bsc_sys_calendars_tl
74   ( calendar_id
75   , language
76   , source_lang
77   , name
78   , help
79   , created_by
80   , creation_date
81   , last_updated_by
82   , last_update_date
83   , last_update_login
84   )
85   SELECT
86     p_Calendar_Record.Calendar_Id
87   , L.LANGUAGE_CODE
88   , USERENV('LANG')
89   , p_Calendar_Record.name
90   , p_Calendar_Record.Help
91   , NVL(p_Calendar_Record.Created_By,FND_GLOBAL.USER_ID)
92   , NVL(p_Calendar_Record.Last_Update_Date,SYSDATE)
93   , NVL(p_Calendar_Record.Last_Updated_By,FND_GLOBAL.USER_ID)
94   , NVL(p_Calendar_Record.Last_Update_Date,SYSDATE)
95   , NVL(p_Calendar_Record.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
96   FROM FND_LANGUAGES L
97   WHERE L.INSTALLED_FLAG IN ('I', 'B')
98   AND NOT EXISTS
99   (
100     SELECT NULL
101     FROM   bsc_sys_calendars_tl T
102     WHERE  T.calendar_id = p_Calendar_Record.Calendar_Id
103     AND    T.LANGUAGE    = L.LANGUAGE_CODE
104   );
105 
106   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
107     COMMIT;
108   END IF;
109 
110 EXCEPTION
111   WHEN NO_DATA_FOUND THEN
112     ROLLBACK TO CreateCalendarSP;
113     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114     IF (x_msg_data IS NOT NULL) THEN
115       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Create_Calendar ';
116     ELSE
117       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Create_Calendar ';
118     END IF;
119   WHEN OTHERS THEN
120     ROLLBACK TO CreateCalendarSP;
121     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122     IF (x_msg_data IS NOT NULL) THEN
123       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Create_Calendar ';
124     ELSE
125       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Create_Calendar ';
126     END IF;
127 END Create_Calendar;
128 
129 /****************************************************************************************/
130 
131 PROCEDURE Update_Calendar
132 ( p_Api_Version            IN          NUMBER
133 , p_Commit                 IN          VARCHAR2
134 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
135 , x_Return_Status          OUT NOCOPY  VARCHAR2
136 , x_Msg_Count              OUT NOCOPY  NUMBER
137 , x_Msg_Data               OUT NOCOPY  VARCHAR2
138 )IS
139 l_System_Stage   bsc_sys_init.property_value%TYPE;
140 BEGIN
141   SAVEPOINT UpdateCalendarSP;
142   FND_MSG_PUB.Initialize;
143   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
144 
145   -- added for Bug#4565308
146   UPDATE bsc_sys_calendars_b
147   SET  fiscal_year        = p_Calendar_Record.Fiscal_Year
148      , current_year       = p_Calendar_Record.Current_Year
149      , start_month        = p_Calendar_Record.Start_Month
150      , start_day          = p_Calendar_Record.Start_Day
151      , last_updated_by    = p_Calendar_Record.Last_Updated_By
152      , last_update_date   = p_Calendar_Record.Last_Update_Date
153      , last_update_login  = p_Calendar_Record.Last_Update_Login
154   WHERE calendar_id       = p_Calendar_Record.Calendar_Id;
155 
156   UPDATE bsc_sys_calendars_tl
157   SET  name               = p_Calendar_Record.Name
158      , help               = p_Calendar_Record.Help
159      , last_updated_by    = p_Calendar_Record.Last_Updated_By
160      , last_update_date   = p_Calendar_Record.Last_Update_Date
161      , last_update_login  = p_Calendar_Record.Last_Update_Login
162      , source_lang        = USERENV('LANG')
163   WHERE calendar_id       = p_Calendar_Record.Calendar_Id
164   AND USERENV('LANG')     IN (LANGUAGE, SOURCE_LANG);
165 
166   UPDATE bsc_kpi_periodicities
167   SET    current_period = p_Calendar_Record.Fiscal_Year
168   WHERE  periodicity_id IN
169   ( SELECT periodicity_id
170     FROM   bsc_sys_periodicities
171     WHERE  calendar_id = p_Calendar_Record.Calendar_Id
172     AND    periodicity_type = 1
173   );
174 
175   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
176     COMMIT;
177   END IF;
178 
179 EXCEPTION
180   WHEN NO_DATA_FOUND THEN
181     ROLLBACK TO UpdateCalendarSP;
182     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183     IF (x_msg_data IS NOT NULL) THEN
184       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Update_Calendar ';
185     ELSE
186       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Update_Calendar ';
187     END IF;
188   WHEN OTHERS THEN
189     ROLLBACK TO UpdateCalendarSP;
190     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191     IF (x_msg_data IS NOT NULL) THEN
192       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Update_Calendar ';
193     ELSE
194       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Update_Calendar ';
195     END IF;
196 END Update_Calendar;
197 
198 PROCEDURE Delete_Calendar
199 ( p_Api_Version            IN          NUMBER
200 , p_Commit                 IN          VARCHAR2
201 , p_Calendar_Record        IN          BSC_CALENDAR_PUB.Calendar_Type_Record
202 , x_Return_Status          OUT NOCOPY  VARCHAR2
203 , x_Msg_Count              OUT NOCOPY  NUMBER
204 , x_Msg_Data               OUT NOCOPY  VARCHAR2
205 )IS
206 
207 BEGIN
208   SAVEPOINT DeleteCalendarSP;
209   FND_MSG_PUB.Initialize;
210   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
211 
212   DELETE bsc_db_week_maps
213   WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
214 
215   DELETE bsc_db_calendar
216   WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
217 
218   DELETE bsc_sys_periods_tl
219   WHERE  periodicity_id IN
220   (
221     SELECT periodicity_id
222     FROM   bsc_sys_periodicities
223     WHERE  calendar_id = p_Calendar_Record.Calendar_Id
224   );
225 
226   DELETE bsc_sys_periods
227   WHERE  periodicity_id IN
228   (
229     SELECT periodicity_id
230     FROM   bsc_sys_periodicities
231     WHERE  calendar_id = p_Calendar_Record.Calendar_Id
232   );
233 
234   DELETE bsc_sys_periodicities_tl
235   WHERE  periodicity_id IN
236   (
237     SELECT periodicity_id
238     FROM   bsc_sys_periodicities
239     WHERE  calendar_id = p_Calendar_Record.Calendar_Id
240   );
241 
242   DELETE bsc_sys_periodicities
243   WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
244 
245   DELETE bsc_sys_calendars_tl
246   WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
247   --dbms_output.put_line(' deleting from bsc_sys_calendars_b with calendar id as :- ' || p_Calendar_Record.Calendar_Id);
248 
249   DELETE bsc_sys_calendars_b
250   WHERE  calendar_id = p_Calendar_Record.Calendar_Id;
251 
252   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
253     COMMIT;
254   END IF;
255 
256 EXCEPTION
257   WHEN NO_DATA_FOUND THEN
258     ROLLBACK TO DeleteCalendarSP;
259     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
260     IF (x_msg_data IS NOT NULL) THEN
261       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Delete_Calendar ';
262     ELSE
263       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Delete_Calendar ';
264     END IF;
265   WHEN OTHERS THEN
266     ROLLBACK TO DeleteCalendarSP;
267     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268     IF (x_msg_data IS NOT NULL) THEN
269       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Delete_Calendar ';
270     ELSE
271       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Delete_Calendar ';
272     END IF;
273 END Delete_Calendar;
274 
275 PROCEDURE Update_Fiscal_Change
276 ( p_Api_Version            IN          NUMBER
277 , p_Commit                 IN          VARCHAR2
278 , p_Calendar_Id            IN          NUMBER
279 , x_Return_Status          OUT NOCOPY  VARCHAR2
280 , x_Msg_Count              OUT NOCOPY  NUMBER
281 , x_Msg_Data               OUT NOCOPY  VARCHAR2
282 )IS
283 BEGIN
284   SAVEPOINT DeleteCalendarSP;
285   FND_MSG_PUB.Initialize;
286   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
287 
288   UPDATE bsc_sys_calendars_b
289   SET    fiscal_change = 1
290   WHERE  calendar_id = p_Calendar_Id;
291 
292   IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
293     COMMIT;
294   END IF;
295 
296 EXCEPTION
297   WHEN NO_DATA_FOUND THEN
298     ROLLBACK TO DeleteCalendarSP;
299     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300     IF (x_msg_data IS NOT NULL) THEN
301       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Update_Fiscal_Change ';
302     ELSE
303       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Update_Fiscal_Change ';
304     END IF;
305   WHEN OTHERS THEN
306     ROLLBACK TO DeleteCalendarSP;
307     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308     IF (x_msg_data IS NOT NULL) THEN
309       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Update_Fiscal_Change ';
310     ELSE
311       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Update_Fiscal_Change ';
312     END IF;
313 END Update_Fiscal_Change;
314 
315 PROCEDURE Update_PeriodNames_In_Calendar
316 ( p_Calendar_Id            IN  NUMBER
317 , x_Return_Status          OUT NOCOPY  VARCHAR2
318 , x_Msg_Count              OUT NOCOPY  NUMBER
319 , x_Msg_Data               OUT NOCOPY  VARCHAR2
320 ) IS
321 
322 CURSOR C_Period_Names IS
323 SELECT BSCPER.short_name
324       ,BSCPER.name
325       ,BSCDIM.dim_level_id
326       ,BISDIM.level_id
327 FROM   bsc_sys_periodicities_vl BSCPER,
328        bsc_sys_dim_levels_vl    BSCDIM,
329        bis_levels_vl            BISDIM
330 WHERE  BSCPER.short_name = BSCDIM.short_name
331 AND    BSCPER.short_name = BISDIM.short_name
332 AND    BSCDIM.short_name = BISDIM.short_name
333 AND    BSCPER.calendar_id = p_Calendar_Id;
334 
335 
336 l_Dimobj_New_Name     BSC_SYS_DIM_LEVELS_TL.NAME%TYPE;
337 
338 BEGIN
339   FOR CD IN C_Period_Names LOOP
340     l_Dimobj_New_Name := BSC_PERIODS_UTILITY_PKG.get_Dimobj_Name_From_period
341                          ( p_Calendar_Id      => p_Calendar_Id
342                          , p_Periodicity_Name => CD.name
343                          );
344 
345     UPDATE bsc_sys_dim_levels_tl
346     SET    name         = l_Dimobj_New_Name
347           ,SOURCE_LANG  = userenv('LANG')
348     WHERE  dim_level_id = CD.dim_level_id
349     AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
350 
351     UPDATE bis_levels_tl
352     SET    name         = l_Dimobj_New_Name
353           ,SOURCE_LANG  = userenv('LANG')
354     WHERE  level_id     = CD.level_id
355     AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
356 
357   END LOOP;
358 
359 EXCEPTION
360   WHEN NO_DATA_FOUND THEN
361     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362     IF (x_msg_data IS NOT NULL) THEN
363       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Update_PeriodNames_In_Calendar ';
364     ELSE
365       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Update_PeriodNames_In_Calendar ';
366     END IF;
367   WHEN OTHERS THEN
368     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369     IF (x_msg_data IS NOT NULL) THEN
370       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PVT.Update_PeriodNames_In_Calendar ';
371     ELSE
372       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PVT.Update_PeriodNames_In_Calendar ';
373     END IF;
374 
375 END Update_PeriodNames_In_Calendar;
376 
377 
378 END BSC_CALENDAR_PVT;