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_PERIODICITIES_PUB

Source


1 PACKAGE BODY BSC_PERIODICITIES_PUB AS
2 /* $Header: BSCPPERB.pls 120.13.12000000.4 2007/05/16 12:51:35 ppandey ship $ */
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 |     BSCPPERB.pls                                                      |
10 REM |                                                                       |
11 REM | DESCRIPTION                                                           |
12 REM |     Module: PUBLIC package body to manage periodicities               |
13 REM | NOTES                                                                 |
14 REM | 14-JUL-2005 Aditya Rao  Created.                                      |
15 REM | 12-AUG-2005 Aditya Rao added API Get_Incr_Change                      |
16 REM | 25-AUG-2005 Pradeep    Bug #4570854, on delete current periodicity_id |
17 REM |                          need to be used for Annually_source          |
18 REM | 29-AUG-2005 Aditya Rao Fixed Bug#4574115 in API Validate_Periodicity  |
19 REM | 07-OCT-2005 Aditya Rao Fixed Bug#4655119, enabled corresponding DO    |
20 REM |                        created for Periodicities                      |
21 REM | 29-NOV-2005 Krishna Modified for enh#4711274                          |
22 REM | 29-DEC-2005 Krishna Passsing enabled = false for hidden periodicities |
23 REM | 07-FEB-2006 ashankar Fix for the bug4695330                           |
24 REM | 15-FEB-2006 visuri  Fixed bug#4757375 AK check for Delete Periodicity |
25 REM | 21-MAR-2006 ashankar  Fixed bug#5099465 Modified Validate_Periodicity |
26 REM +=======================================================================+
27 */
28 
29 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODICITIES_PUB';
30 
31 
32 /*
33 Procedure Name
34 Parameters
35 
36 */
37 
38 PROCEDURE Update_Annually_Source
39 ( p_Calendar_Id    IN  NUMBER
40 , p_Periodicity_Id IN  NUMBER
41 , p_Action         IN  NUMBER
42 , x_Return_Status  OUT NOCOPY  VARCHAR2
43 , x_Msg_Count      OUT NOCOPY  NUMBER
44 , x_Msg_Data       OUT NOCOPY  VARCHAR2
45 );
46 /**************************************************************/
47 
48 PROCEDURE Create_Periodicity (
49   p_Api_Version             IN          NUMBER
50  ,p_Commit                  IN          VARCHAR2
51  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
52  ,p_disable_period_val_flag IN          VARCHAR2
53  ,x_Return_Status           OUT NOCOPY  VARCHAR2
54  ,x_Msg_Count               OUT NOCOPY  NUMBER
55  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
56 ) IS
57     l_Periodicities_Rec_Type  BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
58     l_Dim_Short_Name          BSC_SYS_CALENDARS_B.SHORT_NAME%TYPE;
59     l_Periodicity_View_Name   VARCHAR2(30);
60     l_Dimobj_Name             BIS_LEVELS_TL.NAME%TYPE;
61     l_Dim_Enabled             VARCHAR2(10);
62 BEGIN
63     SAVEPOINT CreatePeriodicityPUB;
64     FND_MSG_PUB.Initialize;
65 
66     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
67     l_Dim_Enabled   := FND_API.G_TRUE;
68     l_Periodicities_Rec_Type := p_Periodicities_Rec_Type;
69 
70     BSC_PERIODICITIES_PUB.Populate_Periodicity_Record (
71       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
72      ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
73      ,x_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
74      ,x_Return_Status           => x_Return_Status
75      ,x_Msg_Count               => x_Msg_Count
76      ,x_Msg_Data                => x_Msg_Data
77     );
78     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
79         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
80     END IF;
81 
82     BSC_PERIODICITIES_PUB.Validate_Periodicity (
83       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
84      ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
85      ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_CREATE
86      ,x_Return_Status           => x_Return_Status
87      ,x_Msg_Count               => x_Msg_Count
88      ,x_Msg_Data                => x_Msg_Data
89     );
90     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
91         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
92     END IF;
93 
94     BSC_PERIODICITIES_PVT.Create_Periodicity (
95       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
96      ,p_Commit                  => p_Commit
97      ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
98      ,x_Return_Status           => x_Return_Status
99      ,x_Msg_Count               => x_Msg_Count
100      ,x_Msg_Data                => x_Msg_Data
101     );
102     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
103         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
104     END IF;
105 
106     BSC_PERIODICITIES_PUB.Populate_Period_Metadata (
107       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
108      ,p_Commit                  => p_Commit
109      ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_CREATE
110      ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
111      ,p_disable_period_val_flag => p_disable_period_val_flag
112      ,x_Return_Status           => x_Return_Status
113      ,x_Msg_Count               => x_Msg_Count
114      ,x_Msg_Data                => x_Msg_Data
115     );
116     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
117         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
118     END IF;
119 
120     BSC_PERIODICITIES_PUB.Update_Annually_Source (
121       p_Calendar_Id     => l_Periodicities_Rec_Type.Calendar_Id
122      ,p_Periodicity_Id  => l_Periodicities_Rec_Type.Periodicity_Id
123      ,p_Action          => 1  -- Action for new/updated Period.
124      ,x_Return_Status   => x_Return_Status
125      ,x_Msg_Count       => x_Msg_Count
126      ,x_Msg_Data        => x_Msg_Data
127     );
128     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
129         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
130     END IF;
131 
132     BSC_PERIODS_PUB.Create_Periodicity_View (
133       p_Periodicity_Id         => l_Periodicities_Rec_Type.Periodicity_Id
134     , p_Short_Name             => l_Periodicities_Rec_Type.Short_Name
135     , p_Calendar_Id            => l_Periodicities_Rec_Type.Calendar_Id
136     , x_Periodicity_View_Name  => l_Periodicity_View_Name
137     , x_Return_Status          => x_Return_Status
138     , x_Msg_Count              => x_Msg_Count
139     , x_Msg_Data               => x_Msg_Data
140     );
141     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
142         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
143     END IF;
144     IF(l_Periodicities_Rec_Type.Periodicity_Type IN (11,12) )THEN
145       l_Dim_Enabled := FND_API.G_FALSE;
146     END IF;
147 
148     l_Dim_Short_Name := BSC_PERIODS_UTILITY_PKG.Get_Calendar_Short_Name(l_Periodicities_Rec_Type.Calendar_Id);
149     l_Dimobj_Name := BSC_PERIODS_UTILITY_PKG.get_Dimobj_Name_From_period
150                      ( p_Calendar_Id      => l_Periodicities_Rec_Type.Calendar_Id
151                      , p_Periodicity_Name => l_Periodicities_Rec_Type.Name
152                      );
153     -- passed p_Dim_Obj_Enabled = 'T' for Dimension Objects, Bug#4655119
154     BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object
155     (
156             p_commit                  =>  p_Commit
157         ,   p_dim_obj_short_name      =>  l_Periodicities_Rec_Type.Short_Name
158         ,   p_display_name            =>  l_Dimobj_Name
159         ,   p_application_id          =>  l_Periodicities_Rec_Type.Application_id
160         ,   p_description             =>  l_Periodicities_Rec_Type.Description
161         ,   p_data_source             =>  BSC_PERIODS_UTILITY_PKG.C_PMF_DO_TYPE
162         ,   p_source_table            =>  l_Periodicity_View_Name
163         ,   p_where_clause            =>  NULL
164         ,   p_comparison_label_code   =>  NULL
165         ,   p_table_column            =>  NULL
166         ,   p_source_type             =>  BSC_PERIODS_UTILITY_PKG.C_OLTP_DO_TYPE
167         ,   p_maximum_code_size       =>  NULL
168         ,   p_maximum_name_size       =>  NULL
169         ,   p_all_item_text           =>  NULL
170         ,   p_comparison_item_text    =>  NULL
171         ,   p_prototype_default_value =>  NULL
172         ,   p_dimension_values_order  =>  NULL
173         ,   p_comparison_order        =>  1
174         ,   p_dim_short_names         =>  l_Dim_Short_Name
175         ,   p_Dim_Obj_Enabled         =>  l_Dim_Enabled
176         ,   x_return_status           =>  x_return_status
177         ,   x_msg_count               =>  x_msg_count
178         ,   x_msg_data                =>  x_msg_data
179     );
180     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
181         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
182     END IF;
183     IF(l_Periodicities_Rec_Type.ForceRunPopulateCalendar = FND_API.G_TRUE ) THEN
184         BSC_UPDATE_UTIL.Populate_Calendar_Tables
185         ( p_commit         => p_Commit
186         , p_calendar_id    => l_Periodicities_Rec_Type.Calendar_Id
187         , x_return_status  => x_Return_Status
188         , x_msg_count      => x_Msg_Count
189         , x_msg_data       => x_Msg_Data
190         );
191         IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
192           RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
193         END IF;
194     END IF;
195     IF (p_Commit IS NOT NULL AND p_Commit = FND_API.G_TRUE) THEN
196         COMMIT;
197     END IF;
198 
199 EXCEPTION
200     WHEN FND_API.G_EXC_ERROR THEN
201         ROLLBACK TO CreatePeriodicityPUB;
202         IF (x_msg_data IS NULL) THEN
203             FND_MSG_PUB.Count_And_Get
204             (      p_encoded   =>  FND_API.G_FALSE
205                ,   p_count     =>  x_msg_count
206                ,   p_data      =>  x_msg_data
207             );
208         END IF;
209         x_Return_Status :=  FND_API.G_RET_STS_ERROR;
210     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211         ROLLBACK TO CreatePeriodicityPUB;
212         IF (x_msg_data IS NULL) THEN
213             FND_MSG_PUB.Count_And_Get
214             (      p_encoded   =>  FND_API.G_FALSE
215                ,   p_count     =>  x_msg_count
216                ,   p_data      =>  x_msg_data
217             );
218         END IF;
219         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
220     WHEN NO_DATA_FOUND THEN
221         ROLLBACK TO CreatePeriodicityPUB;
222         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
223         IF (x_msg_data IS NOT NULL) THEN
224             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Create_Periodicity ';
225         ELSE
226             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Create_Periodicity ';
227         END IF;
228     WHEN OTHERS THEN
229         ROLLBACK TO CreatePeriodicityPUB;
230         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
231         IF (x_msg_data IS NOT NULL) THEN
232             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Create_Periodicity ';
233         ELSE
234             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Create_Periodicity ';
235         END IF;
236 END Create_Periodicity;
237 
238 
239 PROCEDURE Update_Periodicity (
240   p_Api_Version             IN          NUMBER
241  ,p_Commit                  IN          VARCHAR2 := FND_API.G_FALSE
242  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
243  ,p_disable_period_val_flag IN          VARCHAR2
244  ,x_Return_Status           OUT NOCOPY  VARCHAR2
245  ,x_Msg_Count               OUT NOCOPY  NUMBER
246  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
247 ) IS
248     l_Periodicities_Rec_Type BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
249     l_Structural_Flag        VARCHAR2(1);
250     l_Periodicity_View_Name  VARCHAR2(30);
251     l_Message_Name           BSC_MESSAGES.MESSAGE_NAME%TYPE;
252     l_Objective_List         VARCHAR2(2000);
253     l_Dimobj_Name             BIS_LEVELS_TL.NAME%TYPE;
254     l_Dim_Short_Name         BSC_SYS_DIM_GROUPS_TL.SHORT_NAME%TYPE;
255     l_Dim_Enabled            VARCHAR2(10);
256 BEGIN
257     SAVEPOINT UpdatePeriodicityPUB;
258     FND_MSG_PUB.Initialize;
259     l_Dim_Enabled   := FND_API.G_TRUE;
260     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
261 
262     l_Periodicities_Rec_Type := p_Periodicities_Rec_Type;
263 
264     l_Structural_Flag := FND_API.G_FALSE;
265 
266     BSC_PERIODICITIES_PUB.Validate_Periodicity (
267       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
268      ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
269      ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_UPDATE
270      ,x_Return_Status           => x_Return_Status
271      ,x_Msg_Count               => x_Msg_Count
272      ,x_Msg_Data                => x_Msg_Data
273     );
274     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
275       RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
276     END IF;
277 
278     BSC_PERIODICITIES_PUB.Get_Incr_Change (
279        p_Periodicity_Id       => l_Periodicities_Rec_Type.Periodicity_Id
280       ,p_Calendar_ID          => l_Periodicities_Rec_Type.Calendar_Id
281       ,p_Base_Periodicity_Id  => l_Periodicities_Rec_Type.Base_Periodicity_Id
282       ,p_Num_Of_Periods       => l_Periodicities_Rec_Type.Num_Of_Periods
283       ,p_Period_Ids           => l_Periodicities_Rec_Type.Period_IDs
284       ,p_Return_Values        => FND_API.G_FALSE
285       ,x_Message_Name         => l_Message_Name
286       ,x_Objective_List       => l_Objective_List
287     );
288 
289     BSC_PERIODICITIES_PVT.Update_Periodicity (
290       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
291      ,p_Commit                  => p_Commit
292      ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
293      ,x_Structural_Flag         => l_Structural_Flag
294      ,x_Return_Status           => x_Return_Status
295      ,x_Msg_Count               => x_Msg_Count
296      ,x_Msg_Data                => x_Msg_Data
297     );
298     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
299         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
300     END IF;
301 
302     IF (l_Message_Name IS NOT NULL) THEN
303         l_Structural_Flag := FND_API.G_TRUE;
304     END IF ;
305 
306     BSC_PERIODICITIES_PUB.Populate_Period_Metadata (
307       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
308      ,p_Commit                  => p_Commit
309      ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_UPDATE
310      ,p_Periodicities_Rec_Type  => l_Periodicities_Rec_Type
311      ,p_disable_period_val_flag => p_disable_period_val_flag
312      ,x_Return_Status           => x_Return_Status
313      ,x_Msg_Count               => x_Msg_Count
314      ,x_Msg_Data                => x_Msg_Data
315     );
316     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
317         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
318     END IF;
319 
320     BSC_PERIODICITIES_PUB.Update_Annually_Source(
321       p_Calendar_Id     => l_Periodicities_Rec_Type.Calendar_Id
322      ,p_Periodicity_Id  => l_Periodicities_Rec_Type.Periodicity_Id
323      ,p_Action          => 1  -- Action for new/updated Period.
324      ,x_Return_Status   => x_Return_Status
325      ,x_Msg_Count       => x_Msg_Count
326      ,x_Msg_Data        => x_Msg_Data
327     );
328     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
329         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
330     END IF;
331 
332     -- Recreate underlying views.
333     BSC_PERIODS_PUB.Create_Periodicity_View
334     (
335       p_Periodicity_Id         => l_Periodicities_Rec_Type.Periodicity_Id
336     , p_Short_Name             => l_Periodicities_Rec_Type.Short_Name
337     , p_Calendar_Id            => l_Periodicities_Rec_Type.Calendar_Id
338     , x_Periodicity_View_Name  => l_Periodicity_View_Name
339     , x_Return_Status          => x_Return_Status
340     , x_Msg_Count              => x_Msg_Count
341     , x_Msg_Data               => x_Msg_Data
342     );
343     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
344         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
345     END IF;
346 
347     IF(l_Periodicities_Rec_Type.Periodicity_Type IN (11,12) )THEN
348       l_Dim_Enabled := FND_API.G_FALSE;
349     END IF;
350 
351     l_Dim_Short_Name := BSC_PERIODS_UTILITY_PKG.Get_Calendar_Short_Name(l_Periodicities_Rec_Type.Calendar_Id);
352     l_Dimobj_Name := BSC_PERIODS_UTILITY_PKG.get_Dimobj_Name_From_period
353                      ( p_Calendar_Id      => l_Periodicities_Rec_Type.Calendar_Id
354                      , p_Periodicity_Name => l_Periodicities_Rec_Type.Name
355                      );
356     -- passed p_Dim_Obj_Enabled = 'T' for Dimension Objects, Bug#4655119
357     BSC_BIS_DIM_OBJ_PUB.Update_Dim_Object
358     (
359             p_Commit                    =>  p_commit
360         ,   p_Dim_Obj_Short_Name        =>  l_Periodicities_Rec_Type.Short_Name
361         ,   p_Display_Name              =>  l_Dimobj_Name
362         ,   p_Application_Id            =>  l_Periodicities_Rec_Type.Application_id
363         ,   p_Description               =>  l_Periodicities_Rec_Type.Description
364         ,   p_Data_Source               =>  BSC_PERIODS_UTILITY_PKG.C_PMF_DO_TYPE
365         ,   p_Source_Table              =>  l_Periodicity_View_Name
366         ,   p_Where_Clause              =>  NULL
367         ,   p_Comparison_Label_Code     =>  NULL
368         ,   p_Table_Column              =>  NULL
369         ,   p_Source_Type               =>  BSC_PERIODS_UTILITY_PKG.C_OLTP_DO_TYPE
370         ,   p_Maximum_Code_Size         =>  NULL
371         ,   p_Maximum_Name_Size         =>  NULL
372         ,   p_All_Item_Text             =>  NULL
373         ,   p_Comparison_Item_Text      =>  NULL
374         ,   p_Prototype_Default_Value   =>  NULL
375         ,   p_Dimension_Values_Order    =>  NULL
376         ,   p_Comparison_Order          =>  NULL
377         ,   p_Assign_Dim_Short_Names    =>  l_Dim_Short_Name
378         ,   p_Unassign_Dim_Short_Names  =>  NULL
379         ,   p_Dim_Obj_Enabled           =>  l_Dim_Enabled
380         ,   x_Return_Status             =>  x_Return_Status
381         ,   x_Msg_Count                 =>  x_Msg_Count
382         ,   x_Msg_Data                  =>  x_Msg_Data
383     );
384     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
385         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
386     END IF;
387 
388 
389     IF (l_Structural_Flag = FND_API.G_TRUE) THEN
390         BSC_PERIODICITIES_PVT.Incr_Refresh_Objectives(
391           p_Commit                  => p_Commit
392          ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
393          ,x_Return_Status           => x_Return_Status
394          ,x_Msg_Count               => x_Msg_Count
395          ,x_Msg_Data                => x_Msg_Data
396         );
397         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
398             RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
399         END IF;
400     END IF;
401     -- populate Calendar tables.
402 
403     IF(l_Periodicities_Rec_Type.ForceRunPopulateCalendar = FND_API.G_TRUE ) THEN
404         BSC_UPDATE_UTIL.Populate_Calendar_Tables
405         ( p_commit         => p_Commit
406         , p_calendar_id    => l_Periodicities_Rec_Type.Calendar_Id
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     END IF;
416 
417     IF ((p_Commit IS NOT NULL) AND p_Commit = FND_API.G_TRUE) THEN
418         COMMIT;
419     END IF;
420 
421 EXCEPTION
422     WHEN FND_API.G_EXC_ERROR THEN
423         ROLLBACK TO UpdatePeriodicityPUB;
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_ERROR;
432     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
433         ROLLBACK TO UpdatePeriodicityPUB;
434         IF (x_msg_data IS NULL) THEN
435             FND_MSG_PUB.Count_And_Get
436             (      p_encoded   =>  FND_API.G_FALSE
437                ,   p_count     =>  x_msg_count
438                ,   p_data      =>  x_msg_data
439             );
440         END IF;
441         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
442     WHEN NO_DATA_FOUND THEN
443         ROLLBACK TO UpdatePeriodicityPUB;
444         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
445         IF (x_msg_data IS NOT NULL) THEN
446             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Update_Periodicity ';
447         ELSE
448             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Update_Periodicity ';
449         END IF;
450     WHEN OTHERS THEN
451         ROLLBACK TO UpdatePeriodicityPUB;
452         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
453         IF (x_msg_data IS NOT NULL) THEN
454             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Update_Periodicity ';
455         ELSE
456             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Update_Periodicity ';
457         END IF;
458 END Update_Periodicity;
459 
460 
461 
462 -- Delete periodicity API
463 PROCEDURE Delete_Periodicity (
464   p_Api_Version             IN          NUMBER
465  ,p_Commit                  IN          VARCHAR2
466  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
467  ,x_Return_Status           OUT NOCOPY  VARCHAR2
468  ,x_Msg_Count               OUT NOCOPY  NUMBER
469  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
470 ) IS
471     l_Dim_Object_SN  BSC_SYS_PERIODICITIES.SHORT_NAME%TYPE;
472     l_Dimension_SN   BSC_SYS_CALENDARS_B.SHORT_NAME%TYPE;
473     l_Periodicity_View_Name   VARCHAR2(30);
474     l_dim_name       BSC_SYS_CALENDARS_VL.NAME%TYPE;
475     l_dim_obj_name   BSC_SYS_PERIODICITIES_VL.NAME%TYPE;
476     l_regions        VARCHAR2(32000);
477 
478     CURSOR c_Delete_View IS
479         SELECT L.LEVEL_VALUES_VIEW_NAME
480         FROM   BIS_LEVELS L
481         WHERE  L.SHORT_NAME = l_Dim_Object_SN
482         AND    L.LEVEL_VALUES_VIEW_NAME IS NOT NULL;
483 BEGIN
484     SAVEPOINT DeletePeriodicityPUB;
485     FND_MSG_PUB.Initialize;
486 
487     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
488     --dbms_output.PUT_LINE('p_Periodicities_Rec_Type.Periodicity_Id - ' ||p_Periodicities_Rec_Type.Periodicity_Id);
489     --dbms_output.PUT_LINE('p_Periodicities_Rec_Type.Calendar_Id - ' ||p_Periodicities_Rec_Type.Calendar_Id);
490 
491 
492     BSC_PERIODICITIES_PUB.Validate_Periodicity (
493       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
494      ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
495      ,p_Action_Type             => BSC_PERIODS_UTILITY_PKG.C_DELETE
496      ,x_Return_Status           => x_Return_Status
497      ,x_Msg_Count               => x_Msg_Count
498      ,x_Msg_Data                => x_Msg_Data
499     );
500     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
501         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
502     END IF;
503 
504     l_Dim_Object_SN := BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Short_Name(p_Periodicities_Rec_Type.Periodicity_Id);
505     l_Dimension_SN  := BSC_PERIODS_UTILITY_PKG.Get_Calendar_Short_Name(p_Periodicities_Rec_Type.Calendar_Id);
506     l_regions := BSC_UTILITY.Is_Dim_In_AKReport(l_Dimension_SN||'+'||l_Dim_Object_SN);
507     IF(l_regions IS NOT NULL) THEN
508 
509       SELECT c.name
510       INTO   l_dim_name
511       FROM   bsc_sys_calendars_vl c
512       WHERE  c.short_name = l_Dimension_SN;
513 
514       SELECT c.name
515       INTO   l_dim_obj_name
516       FROM   bsc_sys_periodicities_vl c
517       WHERE  c.short_name = l_Dim_Object_SN;
518 
519       FND_MESSAGE.SET_NAME('BIS','BIS_DIM_OBJ_RPTASSOC_ERROR');
520       FND_MESSAGE.SET_TOKEN('DIM_NAME', l_dim_obj_name);
521       FND_MESSAGE.SET_TOKEN('DIM_OBJ_NAME', l_dim_name);
522       FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
523       FND_MSG_PUB.ADD;
524       RAISE FND_API.G_EXC_ERROR;
525     END IF;
526 
527     BSC_PERIODICITIES_PVT.Delete_Periodicity (
528       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
529      ,p_Commit                  => p_Commit
530      ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
531      ,x_Return_Status           => x_Return_Status
532      ,x_Msg_Count               => x_Msg_Count
533      ,x_Msg_Data                => x_Msg_Data
534     );
535     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
536         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
537     END IF;
538 
539     BSC_PERIODICITIES_PUB.Update_Annually_Source(
540       p_Calendar_Id     => p_Periodicities_Rec_Type.Calendar_Id
541      ,p_Periodicity_Id  => p_Periodicities_Rec_Type.Periodicity_Id
542      ,p_Action          => 2  -- Action for Period Delete.
543      ,x_Return_Status   => x_Return_Status
544      ,x_Msg_Count       => x_Msg_Count
545      ,x_Msg_Data        => x_Msg_Data
546     );
547     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
548       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
549     END IF;
550 
551 
552     --dbms_output.PUT_LINE('Shortnames - ' || l_Dim_Object_SN || ', ' || l_Dimension_SN);
553 
554     BSC_BIS_DIM_OBJ_PUB.Unassign_Dimensions
555     (       p_commit                =>  p_commit
556         ,   p_dim_obj_short_name    =>  l_Dim_Object_SN
557         ,   p_dim_short_names       =>  l_Dimension_SN
558         ,   x_return_status         =>  x_return_status
559         ,   x_msg_count             =>  x_msg_count
560         ,   x_msg_data              =>  x_msg_data
561     );
562     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
563         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
564     END IF;
565 
566     --dbms_output.PUT_LINE(' After Unassign_Dimensions ');
567 
568     -- Get hold of the view that needs to be dropped.
569     FOR cDelView IN c_Delete_View LOOP
570         l_Periodicity_View_Name := cDelView.LEVEL_VALUES_VIEW_NAME;
571     END LOOP;
572 
573     BSC_BIS_DIM_OBJ_PUB.Delete_Dim_Object
574     (       p_commit              => p_commit
575         ,   p_dim_obj_short_name  => l_Dim_Object_SN
576         ,   x_return_status       => x_return_status
577         ,   x_msg_count           => x_msg_count
578         ,   x_msg_data            => x_msg_data
579     );
580     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
581         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
582     END IF;
583 
584     --dbms_output.PUT_LINE(' After Delete_Dim_Object ');
585 
586 
587     -- Drop the periodicity view
588     IF (l_Periodicity_View_Name IS NOT NULL) THEN
589         BSC_PERIODS_PUB.Drop_Periodicity_View
590         (
591           p_Periodicity_View  => l_Periodicity_View_Name
592         , x_Return_Status     => x_Return_Status
593         , x_Msg_Count         => x_Msg_Count
594         , x_Msg_Data          => x_Msg_Data
595         );
596         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
597             RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
598         END IF;
599     END IF;
600 
601     IF (p_Commit IS NOT NULL AND p_Commit = FND_API.G_TRUE) THEN
602         COMMIT;
603     END IF;
604 
605 EXCEPTION
606     WHEN FND_API.G_EXC_ERROR THEN
607         ROLLBACK TO DeletePeriodicityPUB;
608         IF (x_msg_data IS NULL) THEN
609             FND_MSG_PUB.Count_And_Get
610             (      p_encoded   =>  FND_API.G_FALSE
611                ,   p_count     =>  x_msg_count
612                ,   p_data      =>  x_msg_data
613             );
614         END IF;
615         x_Return_Status :=  FND_API.G_RET_STS_ERROR;
616     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
617         ROLLBACK TO DeletePeriodicityPUB;
618         IF (x_msg_data IS NULL) THEN
619             FND_MSG_PUB.Count_And_Get
620             (      p_encoded   =>  FND_API.G_FALSE
621                ,   p_count     =>  x_msg_count
622                ,   p_data      =>  x_msg_data
623             );
624         END IF;
625         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
626     WHEN NO_DATA_FOUND THEN
627         ROLLBACK TO DeletePeriodicityPUB;
628         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
629         IF (x_msg_data IS NOT NULL) THEN
630             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Delete_Periodicity ';
631         ELSE
632             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Delete_Periodicity ';
633         END IF;
634     WHEN OTHERS THEN
635         ROLLBACK TO DeletePeriodicityPUB;
636         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
637         IF (x_msg_data IS NOT NULL) THEN
638             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Delete_Periodicity ';
639         ELSE
640             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Delete_Periodicity ';
641         END IF;
642 END Delete_Periodicity;
643 
644 
645 PROCEDURE Validate_Periodicity (
646   p_Api_Version             IN          NUMBER
647  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
648  ,p_Action_Type             IN          VARCHAR2
649  ,x_Return_Status           OUT NOCOPY  VARCHAR2
650  ,x_Msg_Count               OUT NOCOPY  NUMBER
651  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
652 )IS
653 BEGIN
654    BSC_PERIODICITIES_PUB.Validate_Periodicity
655    (
656       p_Api_Version             => p_Api_Version
657      ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
658      ,p_Action_Type             => p_Action_Type
659      ,p_disable_period_val_flag => FND_API.G_FALSE
660      ,x_Return_Status           => x_Return_Status
661      ,x_Msg_Count               => x_Msg_Count
662      ,x_Msg_Data                => x_Msg_Data
663     );
664 END Validate_Periodicity;
665 
666 
667 
668 PROCEDURE Validate_Periodicity (
669   p_Api_Version             IN          NUMBER
670  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
671  ,p_Action_Type             IN          VARCHAR2
672  ,p_disable_period_val_flag IN          VARCHAR2
673  ,x_Return_Status           OUT NOCOPY  VARCHAR2
674  ,x_Msg_Count               OUT NOCOPY  NUMBER
675  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
676 ) IS
677     CURSOR c_Objectives IS
678     SELECT K.NAME
679     FROM   BSC_KPIS_VL K
680          , BSC_KPI_PERIODICITIES P
681     WHERE  P.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id
682     AND    K.INDICATOR = P.INDICATOR;
683 
684 
685     l_Periodicity_Name  BSC_SYS_PERIODICITIES_TL.NAME%TYPE;
686     l_Is_Name_Unique    VARCHAR2(1);
687     l_Max_Periodicities NUMBER;
688     l_Count             NUMBER;
689     l_Is_Circular       VARCHAR2(3);
690     l_Objective_Names   VARCHAR2(2000);
691 BEGIN
692     SAVEPOINT ValidatePeriodicityPUB;
693 
694     FND_MSG_PUB.Initialize;
695     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
696 
697 
698     IF ((p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_CREATE) OR (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_UPDATE)) THEN
699 
700         IF (p_Periodicities_Rec_Type.Calendar_Id IS NULL) THEN
701             FND_MESSAGE.SET_NAME('BSC','BSC_CALENDAR_ID_NULL');
702             FND_MSG_PUB.ADD;
703             RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
704         END IF;
705 
706         IF (p_Periodicities_Rec_Type.Custom_Code <> BSC_PERIODS_UTILITY_PKG.C_BASE_PERIODICITY_TYPE) THEN
707             IF (p_Periodicities_Rec_Type.Base_Periodicity_Id IS NULL) THEN
708                 FND_MESSAGE.SET_NAME('BSC','BSC_BASE_PERIODICITY_NULL');
709                 FND_MSG_PUB.ADD;
710                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711             END IF;
712 
713             IF (p_Periodicities_Rec_Type.Period_IDs IS NULL) THEN
714                 FND_MESSAGE.SET_NAME('BSC','BSC_PERIOD_IDS_NULL');
715                 FND_MSG_PUB.ADD;
716                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
717             END IF;
718         END IF;
719 
720         l_Is_Name_Unique := BSC_PERIODS_UTILITY_PKG.Is_Period_Name_Unique (
721                                   p_Periodicities_Rec_Type.Calendar_Id
722                                 , p_Periodicities_Rec_Type.Name
723                             );
724 
725         -- Validation#3
726         IF (p_Periodicities_Rec_Type.Num_Of_Periods IS NULL) THEN
727             FND_MESSAGE.SET_NAME('BSC','BSC_NUM_PERIODS_CANNOT_NULL');
728             FND_MSG_PUB.ADD;
729             RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
730         END IF;
731 
732 
733 
734         IF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_CREATE) THEN
735 
736             -- Validation#1:
737             IF(l_Is_Name_Unique = FND_API.G_FALSE) THEN
738                 FND_MESSAGE.SET_NAME('BSC','BSC_PERIOD_EXISTS');
739                 FND_MESSAGE.SET_TOKEN('PERIOD', p_Periodicities_Rec_Type.Name);
740                 FND_MESSAGE.SET_TOKEN('CALENDAR',
741                     BSC_PERIODS_UTILITY_PKG.Get_Calendar_Name(
742                         p_Periodicities_Rec_Type.Calendar_Id
743                     )
744                 );
745                 FND_MSG_PUB.ADD;
746                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747             END IF;
748 
749             -- Validation#2
750             SELECT COUNT(1) INTO l_Count
751             FROM   BSC_SYS_PERIODICITIES P
752             WHERE  P.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_id;
753 
754             IF (l_Count <> 0) THEN
755                 FND_MESSAGE.SET_NAME('BSC','BSC_PERIODICITY_ID_UNIQUE');
756                 FND_MESSAGE.SET_TOKEN('PERIODICITY_ID', p_Periodicities_Rec_Type.Periodicity_id);
757                 FND_MSG_PUB.ADD;
758                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759             END IF;
760 
761             -- Validation#2:
762             l_Max_Periodicities := BSC_PERIODS_UTILITY_PKG.Get_Cust_Per_Cnt_By_Calendar(
763                                         p_Periodicities_Rec_Type.Calendar_Id
764                                    );
765             IF(l_Max_Periodicities =  BSC_PERIODS_UTILITY_PKG.C_MAX_CUSTOM_PERIODICITIES) THEN
766                 FND_MESSAGE.SET_NAME('BSC','BSC_PER_CAL_EXCEEDS_LIMIT');
767                 FND_MESSAGE.SET_TOKEN('CALENDAR',
768                     BSC_PERIODS_UTILITY_PKG.Get_Calendar_Name(
769                         p_Periodicities_Rec_Type.Calendar_Id
770                     )
771                 );
772                 FND_MSG_PUB.ADD;
773                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
774             END IF;
775         END IF;
776 
777         IF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
778 
779             SELECT COUNT(1) INTO l_Count
780             FROM   BSC_SYS_PERIODICITIES_VL P
781             WHERE  P.NAME            = p_Periodicities_Rec_Type.Name
782             AND    P.CALENDAR_ID     = p_Periodicities_Rec_Type.Calendar_Id
783             AND    P.PERIODICITY_ID <> p_Periodicities_Rec_Type.Periodicity_id;
784 
785             IF (l_Count <> 0) THEN
786                 FND_MESSAGE.SET_NAME('BSC','BSC_PERIOD_EXISTS');
787                 FND_MESSAGE.SET_TOKEN('PERIOD', p_Periodicities_Rec_Type.Name);
788                 FND_MESSAGE.SET_TOKEN('CALENDAR',
789                     BSC_PERIODS_UTILITY_PKG.Get_Calendar_Name(
790                         p_Periodicities_Rec_Type.Calendar_Id
791                     )
792                 );
793                 FND_MSG_PUB.ADD;
794                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795             END IF;
796 
797             -- Do not allow update of Base Periodicities .
798             -- Change Periodicity_Type to Custom_Code.
799             SELECT COUNT(1) INTO l_Count
800             FROM   BSC_SYS_PERIODICITIES B
801             WHERE  B.PERIODICITY_ID   = p_Periodicities_Rec_Type.Periodicity_id
802             AND    B.CALENDAR_ID      = p_Periodicities_Rec_Type.Calendar_Id
803             AND    B.PERIODICITY_TYPE <> 0;
804 
805             IF (l_Count <> 0) THEN
806                 FND_MESSAGE.SET_NAME('BSC','BSC_NO_UPT_BASE_PERIODICITIES');
807                 FND_MSG_PUB.ADD;
808                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809             END IF;
810 
811             -- Check for circular dependency
812             l_Is_Circular := BSC_BIS_KPI_MEAS_PUB.is_Period_Circular (
813                                       p_Periodicities_Rec_Type.Base_Periodicity_Id
814                                     , p_Periodicities_Rec_Type.Periodicity_id
815                              );
816 
817             IF (l_Is_Circular = BSC_BIS_KPI_MEAS_PUB.CIR_REF_EXISTS) THEN
818                 FND_MESSAGE.SET_NAME('BSC','BSC_PERIOD_NO_CIRCULAR_REF');
819                 FND_MSG_PUB.ADD;
820                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
821             END IF;
822         END IF;
823     ELSIF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_DELETE) THEN
824 
825         -- You cannot delete a BASE Periodicity
826         SELECT COUNT(1) INTO l_Count
827         FROM   BSC_SYS_PERIODICITIES B
828         WHERE  B.PERIODICITY_ID   = p_Periodicities_Rec_Type.Periodicity_id
829         AND    B.CALENDAR_ID      = p_Periodicities_Rec_Type.Calendar_Id
830         AND    B.PERIODICITY_TYPE <> 0;
831 
832         IF (l_Count <> 0) THEN
833             FND_MESSAGE.SET_NAME('BSC','BSC_NO_DEL_BASE_PERIODICITIES');
834             FND_MSG_PUB.ADD;
835             RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836         END IF;
837 
838         -- You cannot delete a custom periodicity, which is the base
839         -- periodicity of another Custom Periodicity
840 
841         IF(p_disable_period_val_flag=FND_API.G_FALSE)THEN
842 
843             SELECT COUNT(1) INTO l_Count
844             FROM   BSC_SYS_PERIODICITIES B
845             WHERE  TRIM(B.SOURCE) = TO_CHAR(p_Periodicities_Rec_Type.Periodicity_id);
846 
847             IF (l_Count <> 0) THEN
848                 FND_MESSAGE.SET_NAME('BSC','BSC_NO_DEL_IS_BASE_PER');
849                 FND_MSG_PUB.ADD;
850                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
851             END IF;
852         END IF;
853 
854         -- You cannot delete a periodicity, if it being used in some
855         -- objectives.
856 
857         SELECT COUNT(1) INTO l_Count
858         FROM   BSC_KPI_PERIODICITIES P
859         WHERE  P.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_id;
860 
861         IF (l_Count <> 0) THEN
862             FOR cObj IN c_Objectives LOOP
863                 IF(l_Objective_Names IS NULL) THEN
864                     l_Objective_Names := cObj.NAME;
865                 ELSE
866                     l_Objective_Names := l_Objective_Names || ',' || cObj.NAME;
867                 END IF;
868             END LOOP;
869 
870             -- fixed for Bug#4574115
871             FND_MESSAGE.SET_NAME('BSC','BSC_PERIOD_USED_IN_OBJECTIVE');
872             FND_MESSAGE.SET_TOKEN('PERIODICITY', BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Name(p_Periodicities_Rec_Type.Periodicity_id));
873             FND_MESSAGE.SET_TOKEN('OBJECTIVES', l_Objective_Names);
874             FND_MSG_PUB.ADD;
875             RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876         END IF;
877 
878     END IF;
879 
880 EXCEPTION
881     WHEN FND_API.G_EXC_ERROR THEN
882         ROLLBACK TO ValidatePeriodicityPUB;
883         IF (x_msg_data IS NULL) THEN
884             FND_MSG_PUB.Count_And_Get
885             (      p_encoded   =>  FND_API.G_FALSE
886                ,   p_count     =>  x_msg_count
887                ,   p_data      =>  x_msg_data
888             );
889         END IF;
890         x_Return_Status :=  FND_API.G_RET_STS_ERROR;
891     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892         ROLLBACK TO ValidatePeriodicityPUB;
893         IF (x_msg_data IS NULL) THEN
894             FND_MSG_PUB.Count_And_Get
895             (      p_encoded   =>  FND_API.G_FALSE
896                ,   p_count     =>  x_msg_count
897                ,   p_data      =>  x_msg_data
898             );
899         END IF;
900         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
901     WHEN NO_DATA_FOUND THEN
902         ROLLBACK TO ValidatePeriodicityPUB;
903         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
904         IF (x_msg_data IS NOT NULL) THEN
905             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Validate_Periodicity ';
906         ELSE
907             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Validate_Periodicity ';
908         END IF;
909     WHEN OTHERS THEN
910         ROLLBACK TO ValidatePeriodicityPUB;
911         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
912         IF (x_msg_data IS NOT NULL) THEN
913             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Validate_Periodicity ';
914         ELSE
915             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Validate_Periodicity ';
916         END IF;
917 END Validate_Periodicity;
918 
919 -- This API tries to populate the periodicity record with pre-req/default values.
920 
921 PROCEDURE Populate_Periodicity_Record (
922   p_Api_Version             IN          NUMBER
923  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
924  ,x_Periodicities_Rec_Type  OUT NOCOPY  BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
925  ,x_Return_Status           OUT NOCOPY  VARCHAR2
926  ,x_Msg_Count               OUT NOCOPY  NUMBER
927  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
928 ) IS
929 
930     l_Calendar_Id               BSC_SYS_CALENDARS_B.CALENDAR_ID%TYPE;
931     l_Base_Periodicity_Source   BSC_SYS_PERIODICITIES.SOURCE%TYPE;
932 BEGIN
933     FND_MSG_PUB.Initialize;
934     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
935     x_Periodicities_Rec_Type := p_Periodicities_Rec_Type;
936 
937     l_Calendar_Id := p_Periodicities_Rec_Type.Calendar_Id;
938     l_Base_Periodicity_Source := NULL;
939 
940     -- Get the next periodicity_id from sequence
941     IF (x_Periodicities_Rec_Type.Periodicity_Id IS NULL) THEN
942         x_Periodicities_Rec_Type.Periodicity_Id := BSC_PERIODS_UTILITY_PKG.Get_Next_Periodicity_Id;
943     END IF;
944 
945     IF (x_Periodicities_Rec_Type.Custom_Code IS NULL) THEN
946         x_Periodicities_Rec_Type.Custom_Code := BSC_PERIODS_UTILITY_PKG.C_CUSTOM_PERIODICITY_CODE;
947     END IF;
948 
949     -- If the periodicity_type is Custom then differnt defaults needs to populated differently
950 
951     IF (x_Periodicities_Rec_Type.Custom_Code <> BSC_PERIODS_UTILITY_PKG.C_NON_CUSTOM_PERIODICITY_CODE) THEN
952 
953         -- Populate the BSC_SYS_PERIODICITY.SOURCE column
954         IF (x_Periodicities_Rec_Type.Base_Periodicity_Id IS NOT NULL) THEN
955             /*l_Base_Periodicity_Source := BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Source (
956                                                 x_Periodicities_Rec_Type.Base_Periodicity_Id
957                                          );
958             IF (l_Base_Periodicity_Source IS NOT NULL) THEN
959                 l_Base_Periodicity_Source := l_Base_Periodicity_Source ||
960                                              ',' ||
961                                              x_Periodicities_Rec_Type.Base_Periodicity_Id;
962             ELSE
963                 l_Base_Periodicity_Source := x_Periodicities_Rec_Type.Base_Periodicity_Id;
964             END IF;
965             */
966             -- Fixed for UTP issue#1
967             l_Base_Periodicity_Source := x_Periodicities_Rec_Type.Base_Periodicity_Id;
968 
969         END IF;
970 
971         x_Periodicities_Rec_Type.Source             := l_Base_Periodicity_Source;
972 
973         x_Periodicities_Rec_Type.Num_Of_Subperiods  := BSC_PERIODS_UTILITY_PKG.C_CUST_NUM_OF_SUBPERIODS;
974         x_Periodicities_Rec_Type.Period_Col_Name    := BSC_PERIODS_UTILITY_PKG.C_DFLT_PERIOD_COL_NAME;
975         x_Periodicities_Rec_Type.Subperiod_Col_Name := NULL;
976         x_Periodicities_Rec_Type.Yearly_Flag        := BSC_PERIODS_UTILITY_PKG.C_PERIODICITY_YEARLY_FLAG;
977         x_Periodicities_Rec_Type.Edw_Flag           := 0; -- not used anymore
978         x_Periodicities_Rec_Type.Edw_Periodicity_Id := NULL; -- not used anymore
979 
980         IF(x_Periodicities_Rec_Type.Db_Column_Name IS NULL) THEN
981             x_Periodicities_Rec_Type.Db_Column_Name := BSC_PERIODS_UTILITY_PKG.Get_Next_Cust_Period_DB_Column (
982                                                             l_Calendar_Id
983                                                        );
984         END IF;
985 
986         x_Periodicities_Rec_Type.Periodicity_Type   := BSC_PERIODS_UTILITY_PKG.C_CUST_PERIODICITY_TYPE;
987         x_Periodicities_Rec_Type.Period_Type_Id     := NULL;
988         x_Periodicities_Rec_Type.Record_Type_Id     := NULL;
989         x_Periodicities_Rec_Type.Xtd_Pattern        := NULL;
990 
991     ELSE  -- else these periodicities are of BSC type
992         x_Periodicities_Rec_Type.Num_Of_Subperiods  := BSC_PERIODS_UTILITY_PKG.C_CUST_NUM_OF_SUBPERIODS;
993 
994         IF (x_Periodicities_Rec_Type.Period_Col_Name IS NULL) THEN
995             x_Periodicities_Rec_Type.Period_Col_Name    := BSC_PERIODS_UTILITY_PKG.C_DFLT_PERIOD_COL_NAME;
996         END IF;
997 
998         x_Periodicities_Rec_Type.Subperiod_Col_Name := NULL;
999 
1000         IF (x_Periodicities_Rec_Type.Yearly_Flag IS NULL) THEN
1001             x_Periodicities_Rec_Type.Yearly_Flag        := BSC_PERIODS_UTILITY_PKG.C_PERIODICITY_YEARLY_FLAG;
1002         END IF;
1003 
1004         x_Periodicities_Rec_Type.Edw_Flag           := 0; -- not used anymore
1005         x_Periodicities_Rec_Type.Edw_Periodicity_Id := NULL; -- not used anymore
1006 
1007         x_Periodicities_Rec_Type.Period_Type_Id     := NULL;
1008         x_Periodicities_Rec_Type.Record_Type_Id     := NULL;
1009         x_Periodicities_Rec_Type.Xtd_Pattern        := NULL;
1010 
1011     END IF;
1012 
1013     IF (x_Periodicities_Rec_Type.Short_Name IS NULL) THEN
1014          x_Periodicities_Rec_Type.Short_Name := BSC_PERIODS_UTILITY_PKG.generate_Period_Short_Name
1015                                                                         ( l_Calendar_Id
1016                                                                         , x_Periodicities_Rec_Type.Periodicity_Id
1017                                                                         );
1018     END IF;
1019 
1020 
1021     IF (x_Periodicities_Rec_Type.Application_Id IS NULL) THEN
1022         x_Periodicities_Rec_Type.Application_Id := BSC_PERIODS_UTILITY_PKG.C_BSC_APPLICATION_ID;
1023     END IF;
1024 
1025     IF (x_Periodicities_Rec_Type.Created_By IS NULL) THEN
1026         x_Periodicities_Rec_Type.Created_By := FND_GLOBAL.USER_ID;
1027     END IF;
1028 
1029     IF (x_Periodicities_Rec_Type.Creation_Date IS NULL) THEN
1030         x_Periodicities_Rec_Type.Creation_Date := SYSDATE;
1031     END IF;
1032 
1033     IF (x_Periodicities_Rec_Type.Last_Updated_By IS NULL) THEN
1034         x_Periodicities_Rec_Type.Last_Updated_By := FND_GLOBAL.USER_ID;
1035     END IF;
1036 
1037     IF (x_Periodicities_Rec_Type.Last_Update_Date IS NULL) THEN
1038         x_Periodicities_Rec_Type.Last_Update_Date := SYSDATE;
1039     END IF;
1040 
1041     IF (x_Periodicities_Rec_Type.Last_Update_Login IS NULL) THEN
1042         x_Periodicities_Rec_Type.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
1043     END IF;
1044 
1045 
1046 EXCEPTION
1047     WHEN OTHERS THEN
1048         IF (x_msg_data IS NULL) THEN
1049             FND_MSG_PUB.Count_And_Get
1050             (      p_encoded   =>  FND_API.G_FALSE
1051                ,   p_count     =>  x_msg_count
1052                ,   p_data      =>  x_msg_data
1053             );
1054         END IF;
1055         x_Return_Status :=  FND_API.G_RET_STS_ERROR;
1056 END Populate_Periodicity_Record;
1057 
1058 -- populates the BSC_SYS_PERIODS Metadata.
1059 
1060 PROCEDURE Populate_Period_Metadata (
1061   p_Api_Version             IN          NUMBER
1062  ,p_Commit                  IN          VARCHAR2
1063  ,p_Action_Type             IN          VARCHAR2
1064  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1065  ,p_disable_period_val_flag IN          VARCHAR2
1066  ,x_Return_Status           OUT NOCOPY  VARCHAR2
1067  ,x_Msg_Count               OUT NOCOPY  NUMBER
1068  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
1069 ) IS
1070     l_Period_Record         BSC_PERIODS_PUB.Period_Record;
1071     l_Struct_Flag           BOOLEAN;
1072 BEGIN
1073     SAVEPOINT PopulatePeriodsPUB;
1074     FND_MSG_PUB.Initialize;
1075     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1076 
1077     l_Period_Record.Periodicity_Id      := p_Periodicities_Rec_Type.Periodicity_Id;
1078     l_Period_Record.Base_Periodicity_Id := p_Periodicities_Rec_Type.Base_Periodicity_Id;
1079     l_Period_Record.Calendar_Id         := p_Periodicities_Rec_Type.Calendar_Id;
1080     l_Period_Record.Periods             := p_Periodicities_Rec_Type.Period_IDs;
1081     l_Period_Record.No_Of_Periods       := p_Periodicities_Rec_Type.Num_Of_Periods;
1082 
1083     IF (l_Period_Record.Created_By IS NULL) THEN
1084         l_Period_Record.Created_By := FND_GLOBAL.USER_ID;
1085     END IF;
1086 
1087     IF (l_Period_Record.Creation_Date IS NULL) THEN
1088         l_Period_Record.Creation_Date := SYSDATE;
1089     END IF;
1090 
1091     IF (l_Period_Record.Last_Updated_By IS NULL) THEN
1092         l_Period_Record.Last_Updated_By := FND_GLOBAL.USER_ID;
1093     END IF;
1094 
1095     IF (l_Period_Record.Last_Update_Date IS NULL) THEN
1096         l_Period_Record.Last_Update_Date := SYSDATE;
1097     END IF;
1098 
1099     IF (l_Period_Record.Last_Update_Login IS NULL) THEN
1100         l_Period_Record.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
1101     END IF;
1102 
1103     IF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_CREATE) THEN
1104         BSC_PERIODS_PUB.Create_Periods
1105         (
1106           p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
1107         , p_Commit                  => p_Commit
1108         , p_Period_Record           => l_Period_Record
1109         , p_disable_period_val_flag => p_disable_period_val_flag
1110         , x_Return_Status           => x_Return_Status
1111         , x_Msg_Count               => x_Msg_Count
1112         , x_Msg_Data                => x_Msg_Data
1113         );
1114         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1115             RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1116         END IF;
1117     ELSIF (p_Action_Type = BSC_PERIODS_UTILITY_PKG.C_UPDATE) THEN
1118         BSC_PERIODS_PUB.Update_Periods
1119         (
1120           p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
1121         , p_Commit                  => p_Commit
1122         , p_Period_Record           => l_Period_Record
1123         , x_Structual_Change        => l_Struct_Flag
1124         , p_disable_period_val_flag => p_disable_period_val_flag
1125         , x_Return_Status           => x_Return_Status
1126         , x_Msg_Count               => x_Msg_Count
1127         , x_Msg_Data                => x_Msg_Data
1128         );
1129         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1130             RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1131         END IF;
1132     END IF;
1133 
1134     IF (p_Commit IS NOT NULL AND p_Commit = FND_API.G_TRUE) THEN
1135         COMMIT;
1136     END IF;
1137 EXCEPTION
1138     WHEN FND_API.G_EXC_ERROR THEN
1139         ROLLBACK TO PopulatePeriodsPUB;
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         ROLLBACK TO PopulatePeriodsPUB;
1150         IF (x_msg_data IS NULL) THEN
1151             FND_MSG_PUB.Count_And_Get
1152             (      p_encoded   =>  FND_API.G_FALSE
1153                ,   p_count     =>  x_msg_count
1154                ,   p_data      =>  x_msg_data
1155             );
1156         END IF;
1157         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1158     WHEN NO_DATA_FOUND THEN
1159         ROLLBACK TO PopulatePeriodsPUB;
1160         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1161         IF (x_msg_data IS NOT NULL) THEN
1162             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Populate_Period_Metadata ';
1163         ELSE
1164             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Populate_Period_Metadata ';
1165         END IF;
1166     WHEN OTHERS THEN
1167         ROLLBACK TO PopulatePeriodsPUB;
1168         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1169         IF (x_msg_data IS NOT NULL) THEN
1170             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Populate_Period_Metadata ';
1171         ELSE
1172             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Populate_Period_Metadata ';
1173         END IF;
1174 END Populate_Period_Metadata;
1175 
1176 -- Public Retrieve API
1177 PROCEDURE Retrieve_Periodicity (
1178   p_Api_Version             IN          NUMBER
1179  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1180  ,x_Periodicities_Rec_Type  OUT NOCOPY  BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1181  ,x_Return_Status           OUT NOCOPY  VARCHAR2
1182  ,x_Msg_Count               OUT NOCOPY  NUMBER
1183  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
1184 ) IS
1185 BEGIN
1186     FND_MSG_PUB.Initialize;
1187     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1188 
1189     BSC_PERIODICITIES_PVT.Retrieve_Periodicity (
1190       p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
1191      ,p_Periodicities_Rec_Type  => p_Periodicities_Rec_Type
1192      ,x_Periodicities_Rec_Type  => x_Periodicities_Rec_Type
1193      ,x_Return_Status           => x_Return_Status
1194      ,x_Msg_Count               => x_Msg_Count
1195      ,x_Msg_Data                => x_Msg_Data
1196     );
1197     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1198         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
1199     END IF;
1200 
1201 EXCEPTION
1202     WHEN FND_API.G_EXC_ERROR THEN
1203         IF (x_msg_data IS NULL) THEN
1204             FND_MSG_PUB.Count_And_Get
1205             (      p_encoded   =>  FND_API.G_FALSE
1206                ,   p_count     =>  x_msg_count
1207                ,   p_data      =>  x_msg_data
1208             );
1209         END IF;
1210         x_Return_Status :=  FND_API.G_RET_STS_ERROR;
1211     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1212         IF (x_msg_data IS NULL) THEN
1213             FND_MSG_PUB.Count_And_Get
1214             (      p_encoded   =>  FND_API.G_FALSE
1215                ,   p_count     =>  x_msg_count
1216                ,   p_data      =>  x_msg_data
1217             );
1218         END IF;
1219         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1220     WHEN NO_DATA_FOUND THEN
1221         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1222         IF (x_msg_data IS NOT NULL) THEN
1223             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Retrieve_Periodicity ';
1224         ELSE
1225             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Retrieve_Periodicity ';
1226         END IF;
1227     WHEN OTHERS THEN
1228         x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1229         IF (x_msg_data IS NOT NULL) THEN
1230             x_msg_data      :=  x_msg_data||' -> BSC_PERIODICITIES_PUB.Retrieve_Periodicity ';
1231         ELSE
1232             x_msg_data      :=  SQLERRM||' at BSC_PERIODICITIES_PUB.Retrieve_Periodicity ';
1233         END IF;
1234 END Retrieve_Periodicity;
1235 /**********************************************************************************************/
1236 PROCEDURE Update_Annually_Source
1237 ( p_Calendar_Id    IN  NUMBER
1238 , p_Periodicity_Id IN  NUMBER
1239 , p_Action         IN  NUMBER
1240 , x_Return_Status  OUT NOCOPY  VARCHAR2
1241 , x_Msg_Count      OUT NOCOPY  NUMBER
1242 , x_Msg_Data       OUT NOCOPY  VARCHAR2
1243 )IS
1244 BEGIN
1245   FND_MSG_PUB.Initialize;
1246   x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1247   BSC_UPDATE_UTIL.Update_AnualPeriodicity_Src
1248   ( x_calendar_id     => p_Calendar_Id
1249   , x_periodicity_id  => p_Periodicity_Id
1250   , x_action          => p_Action
1251   );
1252 
1253   IF(BSC_PERIODS_UTILITY_PKG.Check_Error_Message('BSC_UPDATE_UTIL.UpdAnualPeriodicitySrc')) THEN
1254     FND_MESSAGE.SET_NAME('BSC','BSC_ERROR_UPDATE_ANUAL_SOURCE');
1255     FND_MSG_PUB.ADD;
1256     RAISE FND_API.G_EXC_ERROR;
1257   END IF;
1258 
1259 EXCEPTION
1260   WHEN FND_API.G_EXC_ERROR THEN
1261     IF (x_msg_data IS NULL) THEN
1262       FND_MSG_PUB.Count_And_Get
1263       ( p_encoded   =>  FND_API.G_FALSE
1264       , p_count     =>  x_msg_count
1265       , p_data      =>  x_msg_data
1266       );
1267     END IF;
1268     x_return_status :=  FND_API.G_RET_STS_ERROR;
1269   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1270     IF (x_msg_data IS NULL) THEN
1271       FND_MSG_PUB.Count_And_Get
1272       ( p_encoded   =>  FND_API.G_FALSE
1273       , p_count     =>  x_msg_count
1274       , p_data      =>  x_msg_data
1275       );
1276     END IF;
1277     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1278   WHEN NO_DATA_FOUND THEN
1279     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1280     IF (x_msg_data IS NOT NULL) THEN
1281       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';
1282     ELSE
1283       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';
1284     END IF;
1285   WHEN OTHERS THEN
1286     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1287     IF (x_msg_data IS NOT NULL) THEN
1288       x_msg_data      :=  x_msg_data||' -> BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';
1289     ELSE
1290       x_msg_data      :=  SQLERRM||' at BSC_CALENDAR_PUB.Delete_Calendar_Indexes ';
1291     END IF;
1292 END Update_Annually_Source;
1293 /*****************************************************************************************/
1294 
1295 PROCEDURE Get_Incr_Change (
1296    p_Periodicity_Id       IN NUMBER
1297   ,p_Calendar_ID          IN NUMBER
1298   ,p_Base_Periodicity_Id  IN NUMBER
1299   ,p_Num_Of_Periods       IN NUMBER
1300   ,p_Period_Ids           IN VARCHAR2
1301   ,p_Return_Values        IN VARCHAR2
1302   ,x_Message_Name         OUT NOCOPY VARCHAR2
1303   ,x_Objective_List       OUT NOCOPY VARCHAR2
1304 ) IS
1305     l1_Periodicities_Rec_Type    BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
1306     l2_Periodicities_Rec_Type    BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
1307     l_Period_Record              BSC_PERIODS_PUB.Period_Record;
1308     l_Structural_Flag            VARCHAR2(1);
1309     l_Comma_List                 VARCHAR2(12228);
1310     l_Return_Status              VARCHAR2(1);
1311     l_Msg_Count                  NUMBER;
1312     l_Msg_Data                   VARCHAR2(2000);
1313 
1314     CURSOR C_Obj_List IS
1315       SELECT K.NAME, K.INDICATOR
1316       FROM   BSc_KPI_PERIODICITIES P
1317             ,BSC_KPIS_VL K
1318       WHERE K.INDICATOR = P.INDICATOR
1319       AND   K.PROTOTYPE_FLAG NOT IN (1, 2, 3)
1320       AND   P.PERIODICITY_ID = p_Periodicity_Id;
1321 
1322 BEGIN
1323 
1324   l1_Periodicities_Rec_Type.Periodicity_Id := p_Periodicity_Id;
1325   l_Structural_Flag := FND_API.G_FALSE;
1326 
1327   BSC_PERIODICITIES_PUB.Retrieve_Periodicity (
1328     p_Api_Version             => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
1329    ,p_Periodicities_Rec_Type  => l1_Periodicities_Rec_Type
1330    ,x_Periodicities_Rec_Type  => l2_Periodicities_Rec_Type
1331    ,x_Return_Status           => l_Return_Status
1332    ,x_Msg_Count               => l_Msg_Count
1333    ,x_Msg_Data                => l_Msg_Data
1334   );
1335 
1336   IF (p_Num_Of_Periods IS NOT NULL) THEN
1337       IF(l2_Periodicities_Rec_Type.Num_Of_Periods <> p_Num_Of_Periods) THEN
1338           l_Structural_Flag := FND_API.G_TRUE;
1339       END IF;
1340   END IF;
1341 
1342   IF (p_Base_Periodicity_Id IS NOT NULL) THEN
1343       IF (TO_CHAR(p_Base_Periodicity_Id) <> l2_Periodicities_Rec_Type.Source) THEN
1344           l_Structural_Flag := FND_API.G_TRUE;
1345       END IF;
1346   END IF;
1347 
1348   l_Period_Record.Periodicity_Id := p_Periodicity_Id;
1349   l_Period_Record.Periods        := p_Period_Ids;
1350 
1351   IF(BSC_PERIODS_PUB.Is_Period_Modified(l_Period_Record) = FND_API.G_TRUE) THEN
1352     l_Structural_Flag := FND_API.G_TRUE;
1353   END IF;
1354 
1355   IF(l_Structural_Flag = FND_API.G_TRUE) THEN
1356     x_Message_Name := 'BSC_PMD_KPI_STRUCT_INVALID';
1357 
1358     IF(p_Return_Values = FND_API.G_TRUE) THEN
1359         FOR Colst IN C_Obj_List LOOP
1360           IF(x_Objective_List Is NULL) THEN
1361             x_Objective_List := Colst.NAME||'['||Colst.INDICATOR||']';
1362           ELSE
1363             x_Objective_List := x_Objective_List ||','||Colst.NAME||'['||Colst.INDICATOR||']';
1364           END IF;
1365         END LOOP;
1366     END IF;
1367   END IF;
1368 
1369 EXCEPTION
1370   WHEN OTHERS THEN
1371     x_Objective_List := NULL;
1372     x_Message_Name   := 'BSC_ERROR_ACTION_FLAG_CHANGE';
1373 END Get_Incr_Change;
1374 
1375 
1376 PROCEDURE Create_Periodicity (
1377   p_Api_Version             IN          NUMBER
1378  ,p_Commit                  IN          VARCHAR2
1379  ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1380  ,x_Return_Status           OUT NOCOPY  VARCHAR2
1381  ,x_Msg_Count               OUT NOCOPY  NUMBER
1382  ,x_Msg_Data                OUT NOCOPY  VARCHAR2
1383 ) IS
1384  BEGIN
1385 
1386   BSC_PERIODICITIES_PUB.Create_Periodicity (
1387     p_Api_Version              =>  p_Api_Version
1388    ,p_Commit                   =>  p_Commit
1389    ,p_Periodicities_Rec_Type   =>  p_Periodicities_Rec_Type
1390    ,p_disable_period_val_flag  =>  FND_API.G_FALSE
1391    ,x_Return_Status            =>  x_Return_Status
1392    ,x_Msg_Count                =>  x_Msg_Count
1393    ,x_Msg_Data                 =>  x_Msg_Data
1394  );
1395 
1396  END  Create_Periodicity;
1397 
1398 
1399  PROCEDURE Update_Periodicity (
1400    p_Api_Version             IN          NUMBER
1401   ,p_Commit                  IN          VARCHAR2 := FND_API.G_FALSE
1402   ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1403   ,x_Return_Status           OUT NOCOPY  VARCHAR2
1404   ,x_Msg_Count               OUT NOCOPY  NUMBER
1405   ,x_Msg_Data                OUT NOCOPY  VARCHAR2
1406  ) IS
1407  BEGIN
1408     BSC_PERIODICITIES_PUB.Update_Periodicity (
1409       p_Api_Version             =>  p_Api_Version
1410      ,p_Commit                  =>  p_Commit
1411      ,p_Periodicities_Rec_Type  =>  p_Periodicities_Rec_Type
1412      ,p_disable_period_val_flag =>  FND_API.G_FALSE
1413      ,x_Return_Status           =>  x_Return_Status
1414      ,x_Msg_Count               =>  x_Msg_Count
1415      ,x_Msg_Data                =>  x_Msg_Data
1416  );
1417 
1418 END Update_Periodicity;
1419 
1420 PROCEDURE Translate_Periodicity (
1421    p_Api_Version             IN          NUMBER
1422   ,p_Commit                  IN          VARCHAR2 := FND_API.G_FALSE
1423   ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1424   ,p_disable_period_val_flag IN          VARCHAR2
1425   ,x_Return_Status           OUT NOCOPY  VARCHAR2
1426   ,x_Msg_Count               OUT NOCOPY  NUMBER
1427   ,x_Msg_Data                OUT NOCOPY  VARCHAR2
1428  ) IS
1429  BEGIN
1430   x_return_status := FND_API.G_RET_STS_SUCCESS;
1431 
1432   UPDATE bsc_sys_periodicities_tl
1433   SET    name = NVL(p_Periodicities_Rec_Type.name,name)
1434       ,  source_lang = USERENV('LANG')
1435   WHERE  USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
1436   AND    periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id;
1437 
1438 EXCEPTION
1439   WHEN OTHERS THEN
1440     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1441 
1442 END Translate_Periodicity;
1443 
1444 
1445 PROCEDURE Load_Periodicity (
1446    p_Api_Version             IN          NUMBER
1447   ,p_Commit                  IN          VARCHAR2 := FND_API.G_FALSE
1448   ,p_Periodicities_Rec_Type  IN          BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
1449   ,p_disable_period_val_flag IN          VARCHAR2
1450   ,x_Return_Status           OUT NOCOPY  VARCHAR2
1451   ,x_Msg_Count               OUT NOCOPY  NUMBER
1452   ,x_Msg_Data                OUT NOCOPY  VARCHAR2
1453  ) IS
1454   l_count           NUMBER;
1455   l_name            bsc_sys_periodicities_tl.name%TYPE;
1456  BEGIN
1457     x_return_status := FND_API.G_RET_STS_SUCCESS;
1458 
1459     UPDATE bsc_sys_periodicities
1460     SET num_of_periods = p_Periodicities_Rec_Type.Num_Of_Periods,
1461         source = p_Periodicities_Rec_Type.Source,
1462         num_of_subperiods = p_Periodicities_Rec_Type.Num_Of_Subperiods,
1463         period_col_name = p_Periodicities_Rec_Type.Period_Col_Name,
1464         subperiod_col_name = p_Periodicities_Rec_Type.Subperiod_Col_Name,
1465         yearly_flag = p_Periodicities_Rec_Type.Yearly_Flag,
1466         edw_flag = p_Periodicities_Rec_Type.Edw_Flag,
1467         calendar_id = p_Periodicities_Rec_Type.Calendar_Id,
1468         custom_code = p_Periodicities_Rec_Type.Custom_Code,
1469         db_column_name = p_Periodicities_Rec_Type.Db_Column_Name,
1470         periodicity_type = p_Periodicities_Rec_Type.Periodicity_Type
1471     WHERE periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id;
1472 
1473     IF (SQL%NOTFOUND) THEN
1474       INSERT INTO bsc_sys_periodicities(
1475         periodicity_id,
1476         num_of_periods,
1477         source,
1478         num_of_subperiods,
1479         period_col_name,
1480         subperiod_col_name,
1481         yearly_flag,
1482         edw_flag,
1483         calendar_id,
1484         custom_code,
1485         db_column_name,
1486         periodicity_type)
1487       VALUES(
1488         p_Periodicities_Rec_Type.Periodicity_Id,
1489         p_Periodicities_Rec_Type.Num_Of_Periods,
1490         p_Periodicities_Rec_Type.Source,
1491         p_Periodicities_Rec_Type.Num_Of_Subperiods,
1492         p_Periodicities_Rec_Type.Period_Col_Name,
1493         p_Periodicities_Rec_Type.Subperiod_Col_Name,
1494         p_Periodicities_Rec_Type.Yearly_Flag,
1495         p_Periodicities_Rec_Type.Edw_Flag,
1496         p_Periodicities_Rec_Type.Calendar_Id,
1497         p_Periodicities_Rec_Type.Custom_Code,
1498         p_Periodicities_Rec_Type.Db_Column_Name,
1499         p_Periodicities_Rec_Type.Periodicity_Type
1500       );
1501     END IF;
1502     IF (p_Periodicities_Rec_Type.name IS NULL) THEN
1503       SELECT meaning
1504       INTO   l_name
1505       FROM   bsc_lookups
1506       WHERE  lookup_code=p_Periodicities_Rec_Type.Periodicity_Id
1507       AND    lookup_type = 'BSC_PERIODICITY';
1508     ELSE
1509       l_name := p_Periodicities_Rec_Type.name;
1510 
1511     END IF;
1512 
1513     UPDATE bsc_sys_periodicities_tl
1514     SET    name = l_name,
1515            SOURCE_LANG = userenv('LANG')
1516     WHERE  periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id
1517     AND    userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1518 
1519     IF (SQL%NOTFOUND) THEN
1520       INSERT INTO bsc_sys_periodicities_tl (
1521         PERIODICITY_ID,
1522         NAME,
1523         LANGUAGE,
1524         SOURCE_LANG,
1525         CREATED_BY,
1526         CREATION_DATE,
1527         LAST_UPDATED_BY,
1528         LAST_UPDATE_DATE,
1529         LAST_UPDATE_LOGIN
1530       ) SELECT
1531         p_Periodicities_Rec_Type.Periodicity_Id,
1532         l_name,
1533         L.LANGUAGE_CODE,
1534         USERENV('LANG'),
1535         FND_GLOBAL.user_id,
1536         SYSDATE,
1537         FND_GLOBAL.user_id,
1538         SYSDATE,
1539         FND_GLOBAL.user_id
1540       FROM FND_LANGUAGES L
1541       WHERE L.INSTALLED_FLAG in ('I', 'B')
1542       AND NOT EXISTS
1543         (SELECT NULL
1544          FROM  bsc_sys_periodicities_tl t
1545          WHERE periodicity_id = p_Periodicities_Rec_Type.Periodicity_Id
1546          AND t.LANGUAGE = L.LANGUAGE_CODE);
1547 
1548     END IF;
1549 
1550 EXCEPTION
1551   WHEN OTHERS THEN
1552     -- If error is set from previous API don't change it.
1553     IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1554       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1555     END IF;
1556 END Load_Periodicity;
1557 
1558 END BSC_PERIODICITIES_PUB;