[Home] [Help]
PACKAGE BODY: APPS.BSC_PERIODS_PUB
Source
1 PACKAGE BODY BSC_PERIODS_PUB AS
2 /* $Header: BSCPPITB.pls 120.3.12000000.2 2007/01/31 09:41:25 ashankar ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCPPCTB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Public package for populating the calendar tables |
13 REM | NOTES |
14 REM | 07-JUN-2005 Krishna Created. |
15 REM | 08-AUG-2005 Aditya Rao modified view creation for Periodicities with |
16 REM | performance enhancement and creation of view for yearly |
17 REM | periodicity (Bug#4533089) |
18 REM | 12-AUG-2005 Aditya Rao added API Get_Period_List |
19 REM | 07-FEB-2006 ashankar Fix for the bug4695330 |
20 REM | 17-JAN-2007 ashankar Fix for the bug5654559 |
21 REM +=======================================================================+
22 */
23
24 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODS_PUB';
25
26
27 FUNCTION Parse_Periods
28 ( p_Periods VARCHAR2
29 , x_Start_Period OUT NOCOPY VARCHAR2
30 , x_End_Period OUT NOCOPY VARCHAR2
31 )RETURN BSC_PERIODS_PUB.Start_End_Period_Varray IS
32 l_Start_Pos NUMBER := 1;
33 l_End_Pos NUMBER;
34 l_Length NUMBER;
35 l_Is_String_End BOOLEAN := TRUE;
36 l_Period_Start VARCHAR2(32000);
37 l_Period_End VARCHAR2(32000);
38 l_Index NUMBER := 1 ;
39
40 period_Varray_Record BSC_PERIODS_PUB.Start_End_Period_Varray := BSC_PERIODS_PUB.Start_End_Period_Varray();
41 start_End_Record BSC_PERIODS_PUB.Start_End_Period_Record ;
42 BEGIN
43 l_Length := LENGTH(TRIM(p_Periods));
44 IF(p_Periods IS NOT NULL) THEN
45 LOOP
46 l_End_Pos := INSTR(p_Periods,',',l_Start_Pos);
47 IF(l_End_Pos = 0) THEN
48 l_End_Pos := l_Length + 1;
49 l_Is_String_End := FALSE;
50 END IF;
51
52 l_Period_Start := SUBSTR(p_Periods,l_Start_Pos,l_End_Pos-l_Start_Pos);
53 --dbms_output.put_line(' l_Period_Start :-' ||l_Period_Start );
54 l_Start_Pos := l_End_Pos + 1 ;
55 l_End_Pos := INSTR(p_Periods,',',l_Start_Pos);
56 --dbms_output.put_line(' l_End_Pos:-' || l_End_Pos);
57 IF(x_Start_Period IS NULL) THEN
58 x_Start_Period := l_Period_Start;
59 --dbms_output.put_line(' x_Start_Period:-' || x_Start_Period);
60 END IF;
61
62 IF(l_End_Pos = 0) THEN
63 l_End_Pos := l_Length + 1;
64 --dbms_output.put_line(' l_End_Pos:-' || l_End_Pos);
65 l_Is_String_End := FALSE;
66 END IF;
67 --dbms_output.put_line(' l_Period_End:-' ||l_Period_End );
68 l_Period_End := SUBSTR(p_Periods,l_Start_Pos,l_End_Pos-l_Start_Pos);
69 --dbms_output.put_line(' l_Period_End:-' || l_Period_End);
70 l_Start_Pos := l_End_Pos + 1;
71 x_End_Period := l_Period_End;
72 start_End_Record.Start_Period := l_Period_Start;
73 start_End_Record.End_Period := l_Period_End;
74 period_Varray_Record.extend(1);
75 period_Varray_Record(l_Index) := start_End_Record;
76 IF(NOT l_Is_String_End) THEN
77 EXIT;
78 END IF;
79 l_Index := l_Index + 1;
80 END LOOP;
81 END IF;
82
83 RETURN period_Varray_Record;
84 END Parse_Periods;
85
86 /****************************************************************************************/
87 FUNCTION Get_Valid_Period_View_Name
88 (
89 p_short_name IN VARCHAR2
90 )
91 RETURN VARCHAR2 IS
92 l_found BOOLEAN;
93 l_alias VARCHAR2(30);
94 l_count NUMBER;
95 l_table_name BIS_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
96 BEGIN
97
98 l_found := TRUE;
99 l_alias := NULL;
100 l_table_name := 'BSC_P_' || SUBSTR(p_short_name , 1, 22) || '_V';
101 WHILE (l_found) LOOP
102 SELECT COUNT(1)
103 INTO l_count
104 FROM bis_levels
105 WHERE level_values_view_name = l_table_name;
106 IF (l_count = 0) THEN
107 l_found := FALSE;
108 END IF;
109 IF(l_found) THEN
110 l_alias := bsc_utility.get_Next_Alias(l_alias);
111 l_table_name := 'BSC_P_' ||SUBSTR(p_short_name, 1, 18)||l_alias|| '_V';
112 END IF;
113 END LOOP;
114
115 RETURN l_table_name;
116
117 END Get_Valid_Period_View_Name;
118 /****************************************************************************************/
119
120
121 FUNCTION Get_Periods
122 ( p_Periodicity_Id IN NUMBER
123 , p_Base_Periodicity_Id IN NUMBER
124 )RETURN VARCHAR2 IS
125 l_Periods VARCHAR2(32000);
126 l_Is_Daily_Periodicity BOOLEAN;
127 CURSOR C_Base_Daily_Periods IS
128 SELECT start_date,end_date
129 FROM bsc_sys_periods
130 WHERE periodicity_id = p_Periodicity_Id;
131 CURSOR C_Periods IS
132 SELECT start_period,end_period
133 FROM bsc_sys_periods
134 WHERE periodicity_id = p_Periodicity_Id;
135 BEGIN
136 IF(p_Base_Periodicity_Id IS NOT NULL) THEN
137 l_Is_Daily_Periodicity := BSC_PERIODS_UTILITY_PKG.Is_Base_Periodicity_Daily(p_Base_Periodicity_Id);
138 END IF;
139 IF(l_Is_Daily_Periodicity) THEN
140 FOR CD_Base IN C_Base_Daily_Periods LOOP
141 IF(l_Periods IS NULL) THEN
142 l_Periods := CD_Base.start_date||','||CD_Base.end_date;
143 ELSE
144 l_Periods := l_Periods ||','||CD_Base.start_date||','||CD_Base.end_date;
145 END IF;
146 END LOOP;
147 ELSE
148 FOR CD_Base IN C_Periods LOOP
149 IF(l_Periods IS NULL) THEN
150 l_Periods := CD_Base.start_period||','||CD_Base.end_period;
151 ELSE
152 l_Periods := l_Periods ||','||CD_Base.start_period||','||CD_Base.end_period;
153 END IF;
154 END LOOP;
155 END IF;
156
157 RETURN l_Periods;
158
159 END Get_Periods;
160
161 /******************************************************************************/
162
163 FUNCTION Is_Period_Modified
164 (p_Period_Record IN BSC_PERIODS_PUB.Period_Record
165 )RETURN VARCHAR2 IS
166 l_periods VARCHAR2(32000);
167 l_Is_Period_Modified VARCHAR2(1);
168 l_In_Trimmed_String VARCHAR2(32000);
169 l_In_Dbase_String VARCHAR2(32000);
170 BEGIN
171 l_Is_Period_Modified := FND_API.G_FALSE;
172
173 l_periods := Get_Period_List(p_Period_Record.Periodicity_Id);
174
175 l_In_Trimmed_String := REPLACE(p_Period_Record.Periods,' ','');
176 l_In_Dbase_String := REPLACE(l_periods,' ','');
177 IF(l_In_Trimmed_String <> l_In_Dbase_String) THEN
178 l_Is_Period_Modified := FND_API.G_TRUE;
179 END IF;
180
181 RETURN l_Is_Period_Modified;
182 EXCEPTION
183 WHEN OTHERS THEN
184 RETURN FND_API.G_FALSE;
185 END Is_Period_Modified;
186
187 /******************************************************************************/
188
189 PROCEDURE Create_Periods
190 ( p_Api_Version IN NUMBER
191 , p_Commit IN VARCHAR2
192 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
193 , p_disable_period_val_flag IN VARCHAR2
194 , x_Return_Status OUT NOCOPY VARCHAR2
195 , x_Msg_Count OUT NOCOPY NUMBER
196 , x_Msg_Data OUT NOCOPY VARCHAR2
197 )IS
198 l_Period_Record BSC_PERIODS_PUB.Period_Record;
199 l_period_Varray_Record BSC_PERIODS_PUB.Start_End_Period_Varray;
200 x_Start_Period VARCHAR2(32000);
201 x_End_Period VARCHAR2(32000);
202
203 BEGIN
204 SAVEPOINT CreatePeriodsPubSP;
205 FND_MSG_PUB.Initialize;
206 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
207 --dbms_output.put_line('before assigning ');
208 l_Period_Record := p_Period_Record;
209 l_period_Varray_Record := Parse_Periods
210 ( p_Periods => p_Period_Record.Periods
211 , x_Start_Period => x_Start_Period
212 , x_End_Period => x_End_Period
213 );
214 --dbms_output.put_line('before Validate_Periods_Action');
215 l_Period_Record.period_Varry := l_period_Varray_Record;
216
217 IF(p_disable_period_val_flag = FND_API.G_FALSE)THEN
218
219 BSC_PERIODS_PUB.Validate_Periods_Action
220 ( p_Api_Version => p_Api_Version
221 , p_Commit => p_Commit
222 , p_Period_Record => l_Period_Record
223 , p_Action => BSC_PERIODS_UTILITY_PKG.C_CREATE
224 , x_Start_Period => x_Start_Period
225 , x_End_Period => x_End_Period
226 , x_Return_Status => x_Return_Status
227 , x_Msg_Count => x_Msg_Count
228 , x_Msg_Data => x_Msg_Data
229 );
230 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END IF;
233
234 END IF;
235 --dbms_output.put_line('before Create_Periods');
236 BSC_PERIODICITY_PVT.Create_Periods
237 ( p_Api_Version => p_Api_Version
238 , p_Commit => p_Commit
239 , p_Period_Record => l_Period_Record
240 , x_Return_Status => x_Return_Status
241 , x_Msg_Count => x_Msg_Count
242 , x_Msg_Data => x_Msg_Data
243 );
244 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
246 END IF;
247
248
249 IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
250 COMMIT;
251 END IF;
252
253 EXCEPTION
254 WHEN FND_API.G_EXC_ERROR THEN
255 ROLLBACK TO CreatePeriodsPubSP;
256 IF (x_msg_data IS NULL) THEN
257 FND_MSG_PUB.Count_And_Get
258 ( p_encoded => FND_API.G_FALSE
259 , p_count => x_msg_count
260 , p_data => x_msg_data
261 );
262 END IF;
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
265 ROLLBACK TO CreatePeriodsPubSP;
266 IF (x_msg_data IS NULL) THEN
267 FND_MSG_PUB.Count_And_Get
268 ( p_encoded => FND_API.G_FALSE
269 , p_count => x_msg_count
270 , p_data => x_msg_data
271 );
272 END IF;
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274 WHEN NO_DATA_FOUND THEN
275 ROLLBACK TO CreatePeriodsPubSP;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 IF (x_msg_data IS NOT NULL) THEN
278 x_msg_data := x_msg_data||' -> BSC_PERIODS_PUB.Create_Periods ';
279 ELSE
280 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Create_Periods ';
281 END IF;
282 WHEN OTHERS THEN
283 ROLLBACK TO CreatePeriodsPubSP;
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 IF (x_msg_data IS NOT NULL) THEN
286 x_msg_data := x_msg_data||' -> BSC_PERIODS_PUB.Create_Periods ';
287 ELSE
288 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Create_Periods ';
289 END IF;
290 END Create_Periods;
291
292 /*****************************************************************************************/
293
294 PROCEDURE Update_Periods
295 ( p_Api_Version IN NUMBER
296 , p_Commit IN VARCHAR2
297 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
298 , x_Structual_Change OUT NOCOPY BOOLEAN
299 , p_disable_period_val_flag IN VARCHAR2
300 , x_Return_Status OUT NOCOPY VARCHAR2
301 , x_Msg_Count OUT NOCOPY NUMBER
302 , x_Msg_Data OUT NOCOPY VARCHAR2
303 )IS
304 l_Period_Record BSC_PERIODS_PUB.Period_Record;
305 l_period_Varray_Record BSC_PERIODS_PUB.Start_End_Period_Varray;
306 x_End_Period VARCHAR2(32000);
307 x_Start_Period VARCHAR2(32000);
308
309 BEGIN
310 SAVEPOINT UpdatePeriodsPubSP;
311 FND_MSG_PUB.Initialize;
312 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
313
314 l_Period_Record := p_Period_Record;
315 l_period_Varray_Record := Parse_Periods
316 ( p_Periods => p_Period_Record.Periods
317 , x_Start_Period => x_Start_Period
318 , x_End_Period => x_End_Period
319 );
320
321 l_Period_Record.period_Varry := l_period_Varray_Record;
322
323 IF(p_disable_period_val_flag = FND_API.G_FALSE)THEN
324
325 BSC_PERIODS_PUB.Validate_Periods_Action
326 ( p_Api_Version => p_Api_Version
327 , p_Commit => p_Commit
328 , p_Period_Record => l_Period_Record
329 , p_Action => BSC_PERIODS_UTILITY_PKG.C_UPDATE
330 , x_Start_Period => x_Start_Period
331 , x_End_Period => x_End_Period
332 , x_Return_Status => x_Return_Status
333 , x_Msg_Count => x_Msg_Count
334 , x_Msg_Data => x_Msg_Data
335 );
336 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338 END IF;
339 END IF;
340
341 -- adrao modified to p_Period_Record
342 BSC_PERIODICITY_PVT.Update_Periods
343 ( p_Api_Version => p_Api_Version
344 , p_Commit => p_Commit
345 , p_Period_Record => l_Period_Record
346 , x_Return_Status => x_Return_Status
347 , x_Msg_Count => x_Msg_Count
348 , x_Msg_Data => x_Msg_Data
349 );
350 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
355 COMMIT;
356 END IF;
357
358 EXCEPTION
359 WHEN FND_API.G_EXC_ERROR THEN
360 ROLLBACK TO UpdatePeriodsPubSP;
361 IF (x_msg_data IS NULL) THEN
362 FND_MSG_PUB.Count_And_Get
363 ( p_encoded => FND_API.G_FALSE
364 , p_count => x_msg_count
365 , p_data => x_msg_data
366 );
367 END IF;
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
370 ROLLBACK TO UpdatePeriodsPubSP;
371 IF (x_msg_data IS NULL) THEN
372 FND_MSG_PUB.Count_And_Get
373 ( p_encoded => FND_API.G_FALSE
374 , p_count => x_msg_count
375 , p_data => x_msg_data
376 );
377 END IF;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 WHEN NO_DATA_FOUND THEN
380 ROLLBACK TO UpdatePeriodsPubSP;
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 IF (x_msg_data IS NOT NULL) THEN
383 x_msg_data := x_msg_data||' -> BSC_PERIODS_PUB.Update_Periods ';
384 ELSE
385 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Update_Periods ';
386 END IF;
387 WHEN OTHERS THEN
388 ROLLBACK TO UpdatePeriodsPubSP;
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
390 IF (x_msg_data IS NOT NULL) THEN
391 x_msg_data := x_msg_data||' -> BSC_PERIODS_PUB.Update_Periods ';
392 ELSE
393 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Update_Calendar ';
394 END IF;
395 END Update_Periods;
396
397
398
399 /****************************************************************************************/
400 PROCEDURE Delete_Periods
401 ( p_Api_Version IN NUMBER
402 , p_Commit IN VARCHAR2 := FND_API.G_FALSE
403 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
404 , x_Return_Status OUT NOCOPY VARCHAR2
405 , x_Msg_Count OUT NOCOPY NUMBER
406 , x_Msg_Data OUT NOCOPY VARCHAR2
407 )IS
408 BEGIN
409 SAVEPOINT DeletePeriodsPubSP;
410 FND_MSG_PUB.Initialize;
411 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
412
413 BSC_PERIODS_PUB.Validate_Periods_Action
414 ( p_Api_Version => p_Api_Version
415 , p_Commit => p_Commit
416 , p_Period_Record => p_Period_Record
417 , p_Action => BSC_PERIODS_UTILITY_PKG.C_DELETE
418 , x_Start_Period => NULL
419 , x_End_Period => NULL
420 , x_Return_Status => x_Return_Status
421 , x_Msg_Count => x_Msg_Count
422 , x_Msg_Data => x_Msg_Data
423 );
424 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427
428 BSC_PERIODICITY_PVT.Delete_Periods
429 ( p_Api_Version => p_Api_Version
430 , p_Commit => p_Commit
431 , p_Period_Record => p_Period_Record
432 , x_Return_Status => x_Return_Status
433 , x_Msg_Count => x_Msg_Count
434 , x_Msg_Data => x_Msg_Data
435 );
436 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 END IF;
439
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 DeletePeriodsPubSP;
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 DeletePeriodsPubSP;
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 DeletePeriodsPubSP;
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_PERIODS_PUB.Delete_Periods ';
470 ELSE
471 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Delete_Periods ';
472 END IF;
473 WHEN OTHERS THEN
474 ROLLBACK TO DeletePeriodsPubSP;
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_PERIODS_PUB.Delete_Periods ';
478 ELSE
479 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Delete_Periods ';
480 END IF;
481 END Delete_Periods;
482
483 /***********************************************************************************/
484
485 PROCEDURE Validate_Periods_Action
486 ( p_Api_Version IN NUMBER
487 , p_Commit IN VARCHAR2 := FND_API.G_FALSE
488 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
489 , p_Action IN VARCHAR2
490 , x_Start_Period IN VARCHAR2
491 , x_End_Period IN VARCHAR2
492 , x_Return_Status OUT NOCOPY VARCHAR2
493 , x_Msg_Count OUT NOCOPY NUMBER
494 , x_Msg_Data OUT NOCOPY VARCHAR2
495 )IS
496 l_No_Base_Periods NUMBER;
497 l_Is_Daily_Periodicity BOOLEAN;
498 BEGIN
499
500 FND_MSG_PUB.Initialize;
501 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
502 IF(p_Period_Record.Base_Periodicity_Id IS NOT NULL) THEN
503 l_No_Base_Periods := BSC_PERIODS_UTILITY_PKG.Get_Periods_In_Base_Period(p_Period_Record.Base_Periodicity_Id);
504 l_Is_Daily_Periodicity := BSC_PERIODS_UTILITY_PKG.Is_Base_Periodicity_Daily(p_Period_Record.Base_Periodicity_Id);
505 END IF;
506
507
508 IF(p_Action <> BSC_PERIODS_UTILITY_PKG.C_DELETE ) THEN
509 IF((NOT l_Is_Daily_Periodicity) AND (p_Period_Record.Base_Periodicity_Id IS NOT NULL)) THEN
510 IF(x_Start_Period <> 1 OR x_End_Period <> l_No_Base_Periods) THEN
511 FND_MESSAGE.SET_NAME('BSC','BSC_WRONG_PERIODS');
512 FND_MSG_PUB.ADD;
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515 END IF;
516 END IF;
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 IF (x_msg_data IS NULL) THEN
521 FND_MSG_PUB.Count_And_Get
522 ( p_encoded => FND_API.G_FALSE
523 , p_count => x_msg_count
524 , p_data => x_msg_data
525 );
526 END IF;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 IF (x_msg_data IS NULL) THEN
530 FND_MSG_PUB.Count_And_Get
531 ( p_encoded => FND_API.G_FALSE
532 , p_count => x_msg_count
533 , p_data => x_msg_data
534 );
535 END IF;
536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
537 WHEN NO_DATA_FOUND THEN
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 IF (x_msg_data IS NOT NULL) THEN
540 x_msg_data := x_msg_data||' -> BSC_PERIODS_PUB.Validate_Periods_Action ';
541 ELSE
542 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Validate_Periods_Action ';
543 END IF;
544 WHEN OTHERS THEN
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_PERIODS_PUB.Validate_Periods_Action ';
548 ELSE
549 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Validate_Periods_Action ';
550 END IF;
551 END Validate_Periods_Action;
552 /****************************************************************************************/
553
554 PROCEDURE Create_Periodicity_View
555 ( p_Periodicity_Id IN NUMBER
556 , p_Short_Name IN VARCHAR2
557 , p_Calendar_Id IN NUMBER
558 , x_Periodicity_View_Name OUT NOCOPY VARCHAR2
559 , x_Return_Status OUT NOCOPY VARCHAR2
560 , x_Msg_Count OUT NOCOPY NUMBER
561 , x_Msg_Data OUT NOCOPY VARCHAR2
562 ) IS
563 BEGIN
564 BSC_PERIODS_PUB.Create_Periodicity_View
565 (
566 p_Periodicity_Id => p_Periodicity_Id
567 , p_Short_Name => p_Short_Name
568 , p_Calendar_Id => p_Calendar_Id
569 , p_periodicity_Type => NULL
570 , x_Periodicity_View_Name => x_Periodicity_View_Name
571 , x_Return_Status => x_Return_Status
572 , x_Msg_Count => x_Msg_Count
573 , x_Msg_Data => x_Msg_Data
574 );
575 END Create_Periodicity_View;
576
577 PROCEDURE Create_Periodicity_View
578 (
579 p_Periodicity_Id IN NUMBER
580 , p_Short_Name IN VARCHAR2
581 , p_Calendar_Id IN NUMBER
582 , p_periodicity_Type IN BSC_SYS_PERIODICITIES.periodicity_type%TYPE
583 , x_Periodicity_View_Name OUT NOCOPY VARCHAR2
584 , x_Return_Status OUT NOCOPY VARCHAR2
585 , x_Msg_Count OUT NOCOPY NUMBER
586 , x_Msg_Data OUT NOCOPY VARCHAR2
587 )IS
588 l_Sql_Stmt VARCHAR2(32000);
589 l_View_Name BIS_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
590 l_Period_DB_Col BSC_SYS_PERIODICITIES.DB_COLUMN_NAME%TYPE;
591
592 BEGIN
593 --dbms_output.put_line('START :Create_Periodicity_View :- ' ||DBMS_UTILITY.GET_TIME);
594 FND_MSG_PUB.Initialize;
595 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
596 x_Periodicity_View_Name := Get_Valid_Period_View_Name(p_Short_Name);
597
598 --dbms_output.put_line('BEFORE PREPARING SQL TEXT ');
599
600 l_Period_DB_Col := BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Db_Col(p_Periodicity_Id);
601
602 /*l_Sql_Stmt := ' CREATE OR REPLACE VIEW ' || x_Periodicity_View_Name||'(ID,VALUE,START_DATE,END_DATE) AS '||
603 ' SELECT BP.PERIOD_ID, BP.NAME, ' ||
604 ' BSC_PERIODS_UTILITY_PKG.Get_Start_Period_Date(BC.CALENDAR_ID, BP.PERIODICITY_ID, BP.PERIOD_ID, BP.YEAR), ' ||
605 ' BSC_PERIODS_UTILITY_PKG.Get_End_Period_Date(BC.CALENDAR_ID, BP.PERIODICITY_ID, BP.PERIOD_ID, BP.YEAR) ' ||
606 ' FROM BSC_SYS_PERIODS_VL BP, BSC_SYS_CALENDARS_B BC ' ||
607 ' WHERE BP.PERIODICITY_ID = ' || p_Periodicity_Id ||
608 ' AND BC.CALENDAR_ID = ' || p_Calendar_Id ||
609 ' ORDER BY BP.PERIOD_ID, BP.MONTH '; */
610
611 -- The yearly periodicity needs a different query to calculate the ID, VALUE, START_DATE and END_DATE
612
613 --We are not supposed to create view for Month day and Month week as they are no longer supported.So we will skip
614 --view creation part for those periodicities. but we want the view name. This is required for the bug#5654559
615
616 IF((p_periodicity_Type IS NULL AND l_Period_DB_Col IS NOT NULL) OR (p_periodicity_Type IS NOT NULL AND (
617 p_periodicity_Type NOT IN
618 (BSC_PERIODS_PUB.C_MONTH_DAY,BSC_PERIODS_PUB.C_MONTH_WEEK)))) THEN
619 IF (l_Period_DB_Col = BSC_PERIODS_UTILITY_PKG.C_YEAR_COLUMN) THEN
620 l_Sql_Stmt := ' CREATE OR REPLACE VIEW ' || x_Periodicity_View_Name||'(ID,VALUE,START_DATE,END_DATE) AS '||
621 ' SELECT C.YEAR,C.YEAR, ' ||
622 ' MIN(TO_DATE(C.CALENDAR_YEAR||''-''||C.CALENDAR_MONTH||''-''||C.CALENDAR_DAY, ''YYYY-MM-DD'')) START_DATE, ' ||
623 ' MAX(TO_DATE(C.CALENDAR_YEAR||''-''||C.CALENDAR_MONTH||''-''||C.CALENDAR_DAY, ''YYYY-MM-DD'')) END_DATE ' ||
624 ' FROM BSC_DB_CALENDAR C ' ||
625 ' WHERE C.CALENDAR_ID = ' || p_Calendar_Id ||
626 ' GROUP BY C.YEAR ' ||
627 ' ORDER BY C.YEAR ';
628
629 ELSE
630 l_Sql_Stmt := ' CREATE OR REPLACE VIEW ' || x_Periodicity_View_Name||'(ID,VALUE,START_DATE,END_DATE) AS '||
631 ' SELECT BP.PERIOD_ID, BP.NAME, ' ||
632 ' MIN(TO_DATE(C.CALENDAR_YEAR||''-''||C.CALENDAR_MONTH||''-''||C.CALENDAR_DAY, ''YYYY-MM-DD'')) START_DATE, ' ||
633 ' MAX(TO_DATE(C.CALENDAR_YEAR||''-''||C.CALENDAR_MONTH||''-''||C.CALENDAR_DAY, ''YYYY-MM-DD'')) END_DATE ' ||
634 ' FROM BSC_SYS_PERIODS_VL BP, BSC_DB_CALENDAR C ' ||
635 ' WHERE BP.PERIODICITY_ID = ' || p_Periodicity_Id ||
636 ' AND C.CALENDAR_ID = ' || p_Calendar_Id ||
637 ' AND BP.YEAR = C.YEAR ' ||
638 ' AND BP.PERIOD_ID = C.' || l_Period_DB_Col ||
639 ' GROUP BY BP.PERIOD_ID, BP.NAME, BP.YEAR ' ||
640 ' ORDER BY BP.YEAR, BP.PERIOD_ID ';
641 END IF;
642
643 BSC_APPS.Do_Ddl_AT(l_Sql_Stmt, ad_ddl.create_view, x_Periodicity_View_Name, 'APPS', 'BSC');
644 END IF;
645 --dbms_output.put_line('END :Create_Periodicity_View :- ' ||DBMS_UTILITY.GET_TIME);
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 FND_MESSAGE.SET_NAME('BSC','BSC_ERROR_CREATE_PER_VIEW');
651 FND_MSG_PUB.ADD;
652 FND_MSG_PUB.Count_And_Get
653 ( p_encoded => FND_API.G_FALSE
654 , p_count => x_msg_count
655 , p_data => x_msg_data
656 );
657 IF (x_msg_data IS NOT NULL) THEN
658 x_msg_data := x_msg_data||' -> BSC_PERIODS_PUB.Create_Periodicity_View ';
659 ELSE
660 x_msg_data := SQLERRM||' at BSC_PERIODS_PUB.Create_Periodicity_View ';
661 END IF;
662 END Create_Periodicity_View;
663 /*************************************************************************************/
664 PROCEDURE Drop_Periodicity_View
665 ( p_Periodicity_View IN VARCHAR2
666 , x_Return_Status OUT NOCOPY VARCHAR2
667 , x_Msg_Count OUT NOCOPY NUMBER
668 , x_Msg_Data OUT NOCOPY VARCHAR2
669 )IS
670 l_Sql_Stmt VARCHAR2(32000);
671 BEGIN
672 FND_MSG_PUB.Initialize;
673 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
674
675 l_Sql_Stmt := 'DROP VIEW ' || p_Periodicity_View;
676
677 BSC_APPS.Do_Ddl_AT(l_Sql_Stmt, ad_ddl.drop_view, p_Periodicity_View, 'APPS', 'BSC');
678
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 FND_MESSAGE.SET_NAME('BSC','BSC_ERROR_DELETE_PER_VIEW');
683 FND_MSG_PUB.ADD;
684 --dbms_output.put_line('WHEN THEN ERROR IS :- '||SUBSTR(SQLERRM,1,200) );
685 END Drop_Periodicity_View;
686 /*************************************************************************************/
687
688
689 /*************************************************************************************/
690 FUNCTION Get_Period_List (p_Periodicity_Id IN NUMBER)
691 RETURN VARCHAR2 IS
692 CURSOR C_Non_Daily_Periods_List IS
693 SELECT DISTINCT P.START_PERIOD, P.END_PERIOD, P.PERIOD_ID
694 FROM BSC_SYS_PERIODS P
695 WHERE P.PERIODICITY_ID = p_Periodicity_Id
696 ORDER BY P.PERIOD_ID;
697
698 CURSOR c_Daily_Periods_List IS
699 SELECT DISTINCT P.START_DATE, P.END_DATE, P.PERIOD_ID
700 FROM BSC_SYS_PERIODS P
701 WHERE P.PERIODICITY_ID = p_Periodicity_Id
702 ORDER BY P.PERIOD_ID;
703
704 CURSOR c_Source_Type IS
705 SELECT P.SOURCE FROM BSC_SYS_PERIODICITIES P
706 WHERE P.PERIODICITY_ID = p_Periodicity_Id;
707
708 l_Comma_List VARCHAR2(12228);
709 l_Source BSC_SYS_PERIODICITIES.SOURCE%TYPE;
710 BEGIN
711
712 FOR Cst IN c_Source_Type LOOP
713 l_Source := Cst.SOURCE;
714 END LOOP;
715
716 IF(BSC_PERIODS_UTILITY_PKG.Is_Base_Periodicity_Daily(TO_NUMBER(l_Source))) THEN
717 FOR Cdpl IN c_Daily_Periods_List LOOP
718 IF (l_Comma_List IS NULL) THEN
719 l_Comma_List := TO_CHAR(Cdpl.START_DATE, 'MM/DD/YY') ||',' ||TO_CHAR(Cdpl.END_DATE, 'MM/DD/YY');
720 ELSE
721 l_Comma_List := l_Comma_List || ',' ||
722 TO_CHAR(Cdpl.START_DATE, 'MM/DD/YY') ||',' ||TO_CHAR(Cdpl.END_DATE, 'MM/DD/YY');
723 END IF;
724 END LOOP;
725 ELSE
726 FOR Cndpl IN C_Non_Daily_Periods_List LOOP
727 IF(l_Comma_List IS NULL) THEN
728 l_Comma_List := Cndpl.START_PERIOD||','||Cndpl.END_PERIOD;
729 ELSE
730 l_Comma_List := l_Comma_List||','||Cndpl.START_PERIOD||','||Cndpl.END_PERIOD;
731 END IF;
732 END LOOP;
733 END IF;
734
735 RETURN l_Comma_List;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 RETURN NULL;
740 END Get_Period_List;
741 /*************************************************************************************/
742
743
744
745 END BSC_PERIODS_PUB;