[Home] [Help]
PACKAGE BODY: APPS.BSC_PERIODICITIES_PVT
Source
1 PACKAGE BODY BSC_PERIODICITIES_PVT AS
2 /* $Header: BSCVPERB.pls 120.4 2005/11/30 02:48:25 kyadamak noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCVPERB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: PRIVATE package body to manage periodicities |
13 REM | NOTES |
14 REM | 14-JUL-2005 Aditya Rao Created. |
15 REM | 08-AUG-2005 Aditya Rao Fixed Bug#4539411 in Update_Periodicity() |
16 REM | 19-SEP-2005 ashankar Fixed Bug#4612590 in Update_Periodicity() |
17 REM +=======================================================================+
18 */
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODICITIES_PVT';
21
22 /*
23 Procedure Name
24 Parameters
25
26 */
27
28 PROCEDURE Create_Periodicity (
29 p_Api_Version IN NUMBER
30 ,p_Commit IN VARCHAR2
31 ,p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
32 ,x_Return_Status OUT NOCOPY VARCHAR2
33 ,x_Msg_Count OUT NOCOPY NUMBER
34 ,x_Msg_Data OUT NOCOPY VARCHAR2
35 ) IS
36 l_Periodicities_Rec_Type BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
37 BEGIN
38 SAVEPOINT CreatePeriodicityPVT;
39 FND_MSG_PUB.Initialize;
40 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
41
42 l_Periodicities_Rec_Type := p_Periodicities_Rec_Type;
43
44 /*
45 NOTE: These are raw insert statements, for validations,
46 please refer to the public and wrapper layeres
47 */
48
49 -- Insert into Periodicity base tables
50 INSERT INTO BSC_SYS_PERIODICITIES (
51 PERIODICITY_ID
52 , NUM_OF_PERIODS
53 , SOURCE
54 , NUM_OF_SUBPERIODS
55 , PERIOD_COL_NAME
56 , SUBPERIOD_COL_NAME
57 , YEARLY_FLAG
58 , EDW_FLAG
59 , CALENDAR_ID
60 , EDW_PERIODICITY_ID
61 , CUSTOM_CODE
62 , DB_COLUMN_NAME
63 , PERIODICITY_TYPE
64 , PERIOD_TYPE_ID
65 , RECORD_TYPE_ID
66 , XTD_PATTERN
67 , SHORT_NAME
68 ) VALUES (
69 l_Periodicities_Rec_Type.Periodicity_Id
70 , l_Periodicities_Rec_Type.Num_Of_Periods
71 , l_Periodicities_Rec_Type.Source
72 , l_Periodicities_Rec_Type.Num_Of_Subperiods
73 , l_Periodicities_Rec_Type.Period_Col_Name
74 , l_Periodicities_Rec_Type.Subperiod_Col_Name
75 , l_Periodicities_Rec_Type.Yearly_Flag
76 , l_Periodicities_Rec_Type.Edw_Flag
77 , l_Periodicities_Rec_Type.Calendar_Id
78 , l_Periodicities_Rec_Type.Edw_Periodicity_Id
79 , l_Periodicities_Rec_Type.Custom_Code
80 , l_Periodicities_Rec_Type.Db_Column_Name
81 , l_Periodicities_Rec_Type.Periodicity_Type
82 , l_Periodicities_Rec_Type.Period_Type_Id
83 , l_Periodicities_Rec_Type.Record_Type_Id
84 , l_Periodicities_Rec_Type.Xtd_Pattern
85 , l_Periodicities_Rec_Type.Short_Name
86 );
87
88 -- Insert into Periodicity transalatable tables
89 INSERT INTO BSC_SYS_PERIODICITIES_TL (
90 PERIODICITY_ID
91 , LANGUAGE
92 , SOURCE_LANG
93 , NAME
94 , CREATED_BY
95 , CREATION_DATE
96 , LAST_UPDATED_BY
97 , LAST_UPDATE_DATE
98 , LAST_UPDATE_LOGIN
99 )
100 SELECT
101 l_Periodicities_Rec_Type.Periodicity_Id
102 , L.LANGUAGE_CODE
103 , USERENV('LANG')
104 , l_Periodicities_Rec_Type.Name
105 , l_Periodicities_Rec_Type.Created_By
106 , l_Periodicities_Rec_Type.Creation_Date
107 , l_Periodicities_Rec_Type.Last_Updated_By
108 , l_Periodicities_Rec_Type.Last_Update_Date
109 , l_Periodicities_Rec_Type.Last_Update_Login
110 FROM FND_LANGUAGES L
111 WHERE L.INSTALLED_FLAG IN ('I', 'B')
112 AND NOT EXISTS
113 (
114 SELECT NULL
115 FROM BSC_SYS_PERIODICITIES_TL T
116 WHERE T.PERIODICITY_ID = l_Periodicities_Rec_Type.Periodicity_Id
117 AND T.LANGUAGE = L.LANGUAGE_CODE
118 );
119
120
121 IF ((p_Commit IS NOT NULL) AND p_Commit = FND_API.G_TRUE) THEN
122 COMMIT;
123 END IF;
124
125 EXCEPTION
126 WHEN FND_API.G_EXC_ERROR THEN
127 ROLLBACK TO CreatePeriodicityPVT;
128 IF (x_msg_data IS NULL) THEN
129 FND_MSG_PUB.Count_And_Get
130 ( p_encoded => FND_API.G_FALSE
131 , p_count => x_msg_count
132 , p_data => x_msg_data
133 );
134 END IF;
135 x_Return_Status := FND_API.G_RET_STS_ERROR;
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137 ROLLBACK TO CreatePeriodicityPVT;
138 IF (x_msg_data IS NULL) THEN
139 FND_MSG_PUB.Count_And_Get
140 ( p_encoded => FND_API.G_FALSE
141 , p_count => x_msg_count
142 , p_data => x_msg_data
143 );
144 END IF;
145 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
146 WHEN NO_DATA_FOUND THEN
147 ROLLBACK TO CreatePeriodicityPVT;
148 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
149 IF (x_msg_data IS NOT NULL) THEN
150 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Create_Periodicity ';
151 ELSE
152 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Create_Periodicity ';
153 END IF;
154 WHEN OTHERS THEN
155 ROLLBACK TO CreatePeriodicityPVT;
156 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
157 IF (x_msg_data IS NOT NULL) THEN
158 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Create_Periodicity ';
159 ELSE
160 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Create_Periodicity ';
161 END IF;
162
163 END Create_Periodicity;
164
165 PROCEDURE Retrieve_Periodicity (
166 p_Api_Version IN NUMBER
167 ,p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
168 ,x_Periodicities_Rec_Type OUT NOCOPY BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
169 ,x_Return_Status OUT NOCOPY VARCHAR2
170 ,x_Msg_Count OUT NOCOPY NUMBER
171 ,x_Msg_Data OUT NOCOPY VARCHAR2
172 ) IS
173 CURSOR c_RetrievePeriodicity IS
174 SELECT
175 B.PERIODICITY_ID
176 , B.NUM_OF_PERIODS
177 , B.SOURCE
178 , B.NUM_OF_SUBPERIODS
179 , B.PERIOD_COL_NAME
180 , B.SUBPERIOD_COL_NAME
181 , B.YEARLY_FLAG
182 , B.EDW_FLAG
183 , B.CALENDAR_ID
184 , B.EDW_PERIODICITY_ID
185 , B.CUSTOM_CODE
186 , B.DB_COLUMN_NAME
187 , B.PERIODICITY_TYPE
188 , B.PERIOD_TYPE_ID
189 , B.RECORD_TYPE_ID
190 , B.XTD_PATTERN
191 , B.SHORT_NAME
192 , TL.NAME
193 , TL.CREATED_BY
194 , TL.CREATION_DATE
195 , TL.LAST_UPDATED_BY
196 , TL.LAST_UPDATE_DATE
197 , TL.LAST_UPDATE_LOGIN
198 FROM
199 BSC_SYS_PERIODICITIES B
200 , BSC_SYS_PERIODICITIES_TL TL
201 WHERE
202 B.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id
203 AND TL.PERIODICITY_ID = B.PERIODICITY_ID
204 AND TL.LANGUAGE = USERENV('LANG');
205
206 BEGIN
207 FND_MSG_PUB.Initialize;
208 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
209
210 FOR C1RP IN c_RetrievePeriodicity LOOP
211 x_Periodicities_Rec_Type.Periodicity_Id := C1RP.PERIODICITY_ID;
212 x_Periodicities_Rec_Type.Num_Of_Periods := C1RP.NUM_OF_PERIODS;
213 x_Periodicities_Rec_Type.Source := C1RP.SOURCE;
214 x_Periodicities_Rec_Type.Num_Of_Subperiods := C1RP.NUM_OF_SUBPERIODS;
215 x_Periodicities_Rec_Type.Period_Col_Name := C1RP.PERIOD_COL_NAME;
216 x_Periodicities_Rec_Type.Subperiod_Col_Name := C1RP.SUBPERIOD_COL_NAME;
217 x_Periodicities_Rec_Type.Yearly_Flag := C1RP.YEARLY_FLAG;
218 x_Periodicities_Rec_Type.Edw_Flag := C1RP.EDW_FLAG;
219 x_Periodicities_Rec_Type.Calendar_Id := C1RP.CALENDAR_ID;
220 x_Periodicities_Rec_Type.Edw_Periodicity_Id := C1RP.EDW_PERIODICITY_ID;
221 x_Periodicities_Rec_Type.Custom_Code := C1RP.CUSTOM_CODE;
222 x_Periodicities_Rec_Type.Db_Column_Name := C1RP.DB_COLUMN_NAME;
223 x_Periodicities_Rec_Type.Periodicity_Type := C1RP.PERIODICITY_TYPE;
224 x_Periodicities_Rec_Type.Period_Type_Id := C1RP.PERIOD_TYPE_ID;
225 x_Periodicities_Rec_Type.Record_Type_Id := C1RP.RECORD_TYPE_ID;
226 x_Periodicities_Rec_Type.Xtd_Pattern := C1RP.XTD_PATTERN;
227 x_Periodicities_Rec_Type.Short_Name := C1RP.SHORT_NAME;
228 x_Periodicities_Rec_Type.Name := C1RP.NAME;
229 x_Periodicities_Rec_Type.Created_By := C1RP.CREATED_BY;
230 x_Periodicities_Rec_Type.Creation_Date := C1RP.CREATION_DATE;
231 x_Periodicities_Rec_Type.Last_Updated_By := C1RP.LAST_UPDATED_BY;
232 x_Periodicities_Rec_Type.Last_Update_Date := C1RP.LAST_UPDATE_DATE;
233 x_Periodicities_Rec_Type.Last_Update_Login := C1RP.LAST_UPDATE_LOGIN ;
234 END LOOP;
235
236
237 EXCEPTION
238 WHEN FND_API.G_EXC_ERROR THEN
239 IF (x_msg_data IS NULL) THEN
240 FND_MSG_PUB.Count_And_Get
241 ( p_encoded => FND_API.G_FALSE
242 , p_count => x_msg_count
243 , p_data => x_msg_data
244 );
245 END IF;
246 x_Return_Status := FND_API.G_RET_STS_ERROR;
247 --removed rollback here.
248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
249 IF (x_msg_data IS NULL) THEN
250 FND_MSG_PUB.Count_And_Get
251 ( p_encoded => FND_API.G_FALSE
252 , p_count => x_msg_count
253 , p_data => x_msg_data
254 );
255 END IF;
256 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
257 WHEN NO_DATA_FOUND THEN
258 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
259 IF (x_msg_data IS NOT NULL) THEN
260 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Retrieve_Periodicity ';
261 ELSE
262 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Retrieve_Periodicity ';
263 END IF;
264 WHEN OTHERS THEN
265 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
266 IF (x_msg_data IS NOT NULL) THEN
267 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Retrieve_Periodicity ';
268 ELSE
269 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Retrieve_Periodicity ';
270 END IF;
271 END Retrieve_Periodicity;
272
273 -- Private Update Periodicity API
274
275 PROCEDURE Update_Periodicity (
276 p_Api_Version IN NUMBER
277 ,p_Commit IN VARCHAR2
278 ,p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
279 ,x_Structural_Flag OUT NOCOPY VARCHAR2
280 ,x_Return_Status OUT NOCOPY VARCHAR2
281 ,x_Msg_Count OUT NOCOPY NUMBER
282 ,x_Msg_Data OUT NOCOPY VARCHAR2
283 ) IS
284 l_Periodicities_Rec_Type BSC_PERIODICITIES_PUB.Periodicities_Rec_Type;
285 l_Base_Periodicity_Source BSC_SYS_PERIODICITIES.SOURCE%TYPE;
286
287 BEGIN
288 SAVEPOINT UpdatePeriodicityPVT;
289 FND_MSG_PUB.Initialize;
290 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
291
292 x_Structural_Flag := FND_API.G_FALSE;
293
294 BSC_PERIODICITIES_PVT.Retrieve_Periodicity (
295 p_Api_Version => BSC_PERIODS_UTILITY_PKG.C_API_VERSION_1_0
296 ,p_Periodicities_Rec_Type => p_Periodicities_Rec_Type
297 ,x_Periodicities_Rec_Type => l_Periodicities_Rec_Type
298 ,x_Return_Status => x_Return_Status
299 ,x_Msg_Count => x_Msg_Count
300 ,x_Msg_Data => x_Msg_Data
301 );
302
303 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307 IF (p_Periodicities_Rec_Type.Num_Of_Periods IS NOT NULL) THEN
308
309 IF(l_Periodicities_Rec_Type.Num_Of_Periods <> p_Periodicities_Rec_Type.Num_Of_Periods) THEN
310 x_Structural_Flag := FND_API.G_TRUE;
311 END IF;
312
313 l_Periodicities_Rec_Type.Num_Of_Periods := p_Periodicities_Rec_Type.Num_Of_Periods;
314 END IF;
315
316 IF (p_Periodicities_Rec_Type.Base_Periodicity_Id IS NOT NULL) THEN
317 -- Fixed for Bug#4539411 ,all Custom Periodicities should *not* have comma separated list.
318 IF (TO_CHAR(p_Periodicities_Rec_Type.Base_Periodicity_Id) <> l_Periodicities_Rec_Type.Source) THEN
319 x_Structural_Flag := FND_API.G_TRUE;
320 l_Periodicities_Rec_Type.Base_Periodicity_Id := p_Periodicities_Rec_Type.Base_Periodicity_Id;
321 l_Periodicities_Rec_Type.Source := p_Periodicities_Rec_Type.Base_Periodicity_Id;
322 END IF;
323 END IF;
324
325 IF (p_Periodicities_Rec_Type.Name IS NOT NULL) THEN
326 l_Periodicities_Rec_Type.Name := p_Periodicities_Rec_Type.Name;
327 END IF;
328
329 IF (p_Periodicities_Rec_Type.Last_Updated_By IS NOT NULL) THEN
330 l_Periodicities_Rec_Type.Last_Updated_By := p_Periodicities_Rec_Type.Last_Updated_By;
331 ELSE
332 l_Periodicities_Rec_Type.Last_Updated_By := FND_GLOBAL.USER_ID;
333 END IF;
334
335 IF (p_Periodicities_Rec_Type.Last_Update_Date IS NOT NULL) THEN
336 l_Periodicities_Rec_Type.Last_Update_Date := p_Periodicities_Rec_Type.Last_Update_Date;
337 ELSE
338 l_Periodicities_Rec_Type.Last_Update_Date := SYSDATE;
339 END IF;
340
341 IF (p_Periodicities_Rec_Type.Last_Update_Login IS NOT NULL) THEN
342 l_Periodicities_Rec_Type.Last_Update_Login := p_Periodicities_Rec_Type.Last_Update_Login;
343 ELSE
344 l_Periodicities_Rec_Type.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
345 END IF;
346
347 IF(p_Periodicities_Rec_Type.Custom_Code IS NOT NULL)THEN
348 l_Periodicities_Rec_Type.Custom_Code :=p_Periodicities_Rec_Type.Custom_Code;
349 END IF;
350
351 --BSC_PERIODS_UTILITY_PKG.Print_Period_Metadata ('Update_Periodicity 1', l_Periodicities_Rec_Type);
352
353
354 UPDATE bsc_sys_periodicities b
355 SET b.num_of_periods = l_Periodicities_Rec_Type.Num_Of_Periods
356 ,b.source = l_Periodicities_Rec_Type.Source
357 ,b.custom_code = l_Periodicities_Rec_Type.Custom_Code
358 WHERE b.periodicity_id = l_Periodicities_Rec_Type.Periodicity_id;
359
360
361 UPDATE bsc_sys_periodicities_tl t
362 SET t.name = l_Periodicities_Rec_Type.Name
363 ,t.last_updated_by = l_Periodicities_Rec_Type.Last_Updated_By
364 ,t.last_update_date = l_Periodicities_Rec_Type.Last_Update_Date
365 ,t.last_update_login = l_Periodicities_Rec_Type.Last_Update_Login
366 ,SOURCE_LANG = userenv('LANG')
367 WHERE t.periodicity_id = l_Periodicities_Rec_Type.Periodicity_id
368 AND USERENV('LANG') IN (t.language, t.source_lang);
369
370 SELECT name INTO l_Periodicities_Rec_Type.Name
371 FROM bsc_sys_periodicities_vl
372 WHERE periodicity_id = l_Periodicities_Rec_Type.Periodicity_id;
373 --dbms_output.PUT_LINE('Name - ' ||l_Periodicities_Rec_Type.Name);
374
375
376 IF ((p_Commit IS NOT NULL) AND p_Commit = FND_API.G_TRUE) THEN
377 COMMIT;
378 END IF;
379
380 EXCEPTION
381 WHEN FND_API.G_EXC_ERROR THEN
382 ROLLBACK TO UpdatePeriodicityPVT;
383 IF (x_msg_data IS NULL) THEN
384 FND_MSG_PUB.Count_And_Get
385 ( p_encoded => FND_API.G_FALSE
386 , p_count => x_msg_count
387 , p_data => x_msg_data
388 );
389 END IF;
390 x_Return_Status := FND_API.G_RET_STS_ERROR;
391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
392 ROLLBACK TO UpdatePeriodicityPVT;
393 IF (x_msg_data IS NULL) THEN
394 FND_MSG_PUB.Count_And_Get
395 ( p_encoded => FND_API.G_FALSE
396 , p_count => x_msg_count
397 , p_data => x_msg_data
398 );
399 END IF;
400 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
401 WHEN NO_DATA_FOUND THEN
402 ROLLBACK TO UpdatePeriodicityPVT;
403 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
404 IF (x_msg_data IS NOT NULL) THEN
405 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Update_Periodicity ';
406 ELSE
407 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Update_Periodicity ';
408 END IF;
409 WHEN OTHERS THEN
410 ROLLBACK TO UpdatePeriodicityPVT;
411 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
412 IF (x_msg_data IS NOT NULL) THEN
413 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Update_Periodicity ';
414 ELSE
415 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Update_Periodicity ';
416 END IF;
417 END Update_Periodicity;
418
419 PROCEDURE Incr_Refresh_Objectives(
420 p_Commit IN VARCHAR2
421 ,p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
422 ,x_Return_Status OUT NOCOPY VARCHAR2
423 ,x_Msg_Count OUT NOCOPY NUMBER
424 ,x_Msg_Data OUT NOCOPY VARCHAR2
425 ) IS
426 CURSOR c_Objectives IS
427 SELECT K.INDICATOR
428 FROM BSC_KPI_PERIODICITIES K
429 WHERE K.PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id;
430 BEGIN
431 SAVEPOINT IncrRefreshPerPVT;
432 FND_MSG_PUB.Initialize;
433 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
434
435
436 FOR cObj IN c_Objectives LOOP
437 BSC_DESIGNER_PVT.ActionFlag_Change(cObj.INDICATOR, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
438 END LOOP;
439
440 IF ((p_Commit IS NOT NULL) AND p_Commit = FND_API.G_TRUE) THEN
441 COMMIT;
442 END IF;
443
444 EXCEPTION
445 WHEN FND_API.G_EXC_ERROR THEN
446 ROLLBACK TO IncrRefreshPerPVT;
447 IF (x_msg_data IS NULL) THEN
448 FND_MSG_PUB.Count_And_Get
449 ( p_encoded => FND_API.G_FALSE
450 , p_count => x_msg_count
451 , p_data => x_msg_data
452 );
453 END IF;
454 x_Return_Status := FND_API.G_RET_STS_ERROR;
455 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
456 ROLLBACK TO IncrRefreshPerPVT;
457 IF (x_msg_data IS NULL) THEN
458 FND_MSG_PUB.Count_And_Get
459 ( p_encoded => FND_API.G_FALSE
460 , p_count => x_msg_count
461 , p_data => x_msg_data
462 );
463 END IF;
464 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
465 WHEN NO_DATA_FOUND THEN
466 ROLLBACK TO IncrRefreshPerPVT;
467 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
468 IF (x_msg_data IS NOT NULL) THEN
469 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Incr_Refresh_Objectives ';
470 ELSE
471 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Incr_Refresh_Objectives ';
472 END IF;
473 WHEN OTHERS THEN
474 ROLLBACK TO IncrRefreshPerPVT;
475 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
476 IF (x_msg_data IS NOT NULL) THEN
477 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Incr_Refresh_Objectives ';
478 ELSE
479 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Incr_Refresh_Objectives ';
480 END IF;
481 END Incr_Refresh_Objectives;
482
483
484 PROCEDURE Delete_Periodicity (
485 p_Api_Version IN NUMBER
486 ,p_Commit IN VARCHAR2
487 ,p_Periodicities_Rec_Type IN BSC_PERIODICITIES_PUB.Periodicities_Rec_Type
488 ,x_Return_Status OUT NOCOPY VARCHAR2
489 ,x_Msg_Count OUT NOCOPY NUMBER
490 ,x_Msg_Data OUT NOCOPY VARCHAR2
491 ) IS
492 BEGIN
493 SAVEPOINT DeletePeriodicityPVT;
494 FND_MSG_PUB.Initialize;
495
496 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
497
498 DELETE BSC_SYS_PERIODS_TL
499 WHERE PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id;
500
501 DELETE BSC_SYS_PERIODS
502 WHERE PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id;
503
504 DELETE BSC_SYS_PERIODICITIES_TL
505 WHERE PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id;
506
507 DELETE BSC_SYS_PERIODICITIES
508 WHERE PERIODICITY_ID = p_Periodicities_Rec_Type.Periodicity_Id;
509
510 IF ((p_Commit IS NOT NULL) AND p_Commit = FND_API.G_TRUE) THEN
511 COMMIT;
512 END IF;
513
514 EXCEPTION
515 WHEN FND_API.G_EXC_ERROR THEN
516 ROLLBACK TO DeletePeriodicityPVT;
517 IF (x_msg_data IS NULL) THEN
518 FND_MSG_PUB.Count_And_Get
519 ( p_encoded => FND_API.G_FALSE
520 , p_count => x_msg_count
521 , p_data => x_msg_data
522 );
523 END IF;
524 x_Return_Status := FND_API.G_RET_STS_ERROR;
525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
526 ROLLBACK TO DeletePeriodicityPVT;
527 IF (x_msg_data IS NULL) THEN
528 FND_MSG_PUB.Count_And_Get
529 ( p_encoded => FND_API.G_FALSE
530 , p_count => x_msg_count
531 , p_data => x_msg_data
532 );
533 END IF;
534 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
535 WHEN NO_DATA_FOUND THEN
536 ROLLBACK TO DeletePeriodicityPVT;
537 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
538 IF (x_msg_data IS NOT NULL) THEN
539 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Delete_Periodicity ';
540 ELSE
541 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Delete_Periodicity ';
542 END IF;
543 WHEN OTHERS THEN
544 ROLLBACK TO DeletePeriodicityPVT;
545 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
546 IF (x_msg_data IS NOT NULL) THEN
547 x_msg_data := x_msg_data||' -> BSC_PERIODICITIES_PVT.Delete_Periodicity ';
548 ELSE
549 x_msg_data := SQLERRM||' at BSC_PERIODICITIES_PVT.Delete_Periodicity ';
550 END IF;
551 END Delete_Periodicity;
552
553
554 END BSC_PERIODICITIES_PVT;