[Home] [Help]
PACKAGE BODY: APPS.BIS_PMV_TIME_LEVELS_PVT
Source
1 package body BIS_PMV_TIME_LEVELS_PVT AS
2 /* $Header: BISVTMLB.pls 120.3 2006/09/18 13:23:12 ashgarg noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.34=120.3):~PROD:~PATH:~FILE
5 --
6 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
7 -- directory of SQL Navigator
8 --
9 -- Purpose: Briefly explain the functionality of the package
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- --------- ------ ------------------------------------------
14 --
15 -- ansingh Apr 22, 2003 BugFix#2887200
16 -- nbarik Jul 16, 2003 Bug Fix#2999602
17 -- nkishore Feb 12, 2004 Bug Fix#3432746
18 -- ugodavar Oct 28, 2004 Bug.Fix#3921033
19
20 -- Enter package declarations as shown below
21 PROCEDURE GET_PREVIOUS_TIME_LEVEL_VALUE
22 (p_DimensionLevel in VARCHAR2
23 ,p_region_code in VARCHAR2
24 ,p_responsibility_id in VARCHAR2
25 ,p_asof_date in DATE
26 ,p_time_comparison_type in VARCHAR2
27 ,x_time_level_id OUT NOCOPY VARCHAR2
28 ,x_time_level_value OUT NOCOPY VARCHAR2
29 ,x_start_Date OUT NOCOPY DATE
30 ,x_end_date OUT NOCOPY DATE
31 ,x_return_status OUT NOCOPY VARCHAR2
32 ,x_msg_count OUT NOCOPY NUMBER
33 ,x_msg_data OUT NOCOPY VARCHAR2
34 ,p_use_current_mode IN BOOLEAN DEFAULT FALSE --added for bug 4475937
35 )
36 IS
37 l_Asof_date DATE;
38 l_mode VARCHAR2(2000):= 'GET_PREVIOUS';
39 BEGIN
40
41 l_asof_date := p_asof_date;
42 /*IF (p_time_comparison_type = 'TIME_COMPARISON_TYPE+SEQUENTIAL') then
43 l_Asof_Date := p_asof_Date;
44 END IF;*/
45 -- added for bug 4475937
46 IF (p_time_comparison_Type = 'TIME_COMPARISON_TYPE+YEARLY' OR p_use_current_mode )then
47 --l_asof_Date := add_months(l_asof_date, -12);
48 l_mode := 'GET_CURRENT';
49 END IF;
50
51 GET_TIME_LEVEL_INFO(p_dimensionlevel => p_DimensionLevel,
52 p_region_code => p_region_code,
53 p_Responsibility_id => p_responsibility_id,
54 p_Asof_date => l_asof_date,
55 p_mode => l_mode,
56 x_time_level_id => x_time_level_id,
57 x_time_level_value => x_time_level_Value,
58 x_Start_date => x_start_date,
59 x_end_date => x_end_date,
60 x_return_Status => x_return_status,
61 x_msg_count => x_msg_count,
62 x_msg_data => x_msg_data
63 );
64 END;
65 PROCEDURE GET_TIME_LEVEL_INFO
66 (p_DimensionLevel IN VARCHAR2
67 ,p_region_code IN VARCHAR2
68 ,p_responsibility_id IN VARCHAR2
69 ,p_asof_date IN DATE
70 ,p_mode IN VARCHAR2
71 ,x_time_level_id OUT NOCOPY VARCHAR2
72 ,x_time_level_Value OUT NOCOPY VARCHAR2
73 ,x_start_Date OUT NOCOPY DATE
74 ,x_end_date OUT NOCOPY DATE
75 ,x_return_Status OUT NOCOPY VARCHAR2
76 ,x_msg_count OUT NOCOPY NUMBER
77 ,x_msg_data OUT NOCOPY VARCHAR2
78 )
79 IS
80 l_start_date date;
81 tmp_start_date date;
82 l_end_date date;
83 l_mode varchar2(2000);
84 l_start_date_function varchar2(2000);
85 x_start_date_function varchar2(2000);
86 -- P1 3502644 fix
87 l_dynamic_sql varchar2(2000) ;
88 -- ashgarg bug: 5347447
89 l_sql VARCHAR2(32676);
90 BEGIN
91 --Bug Fix#3432746 Use Fii Apis for Rolling Time as well
92 if (p_Dimensionlevel = 'TIME+FII_ROLLING_WEEK') then
93 -- P1 3502644 fix
94 -- x_start_date := fii_time_api.rwk_start(p_asof_date);
95 -- ashgarg bug: 5347447
96 l_sql := 'select id, value from fii_time_week_v where :1 between start_date and end_date';
97 execute immediate l_sql INTO x_time_level_id, x_time_level_value using p_asof_date;
98
99 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rwk_start(:2); END;';
100 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_start_date, IN p_asof_date;
101
102 --p_asof_date-6;
103 x_end_date := p_asof_date;
104 --x_time_level_id := sysdate;
105 --x_time_level_value := sysdate;
106 return;
107 end if;
108 if (p_dimensionlevel = 'TIME+FII_ROLLING_MONTH') then
109 -- P1 3502644
110 -- x_start_date := fii_time_api.rmth_start(p_asof_date);
111 -- ashgarg bug: 5347447
112 l_sql := 'select id, value from fii_time_month_v where :1 between start_date and end_date';
113 execute immediate l_sql INTO x_time_level_id, x_time_level_value using p_asof_date;
114
115 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rmth_start(:2); END;';
116 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_start_date, IN p_asof_date;
117
118 --add_months(p_asof_date,-1)+1;
119 x_end_date := p_asof_date;
120 --x_time_level_id := sysdate;
121 --x_time_level_value := sysdate;
122 return;
123 end if;
124 if (p_dimensionlevel = 'TIME+FII_ROLLING_QTR') then
125 -- P1 3502644
126 -- x_start_date := fii_time_api.rqtr_start(p_asof_Date);
127 -- ashgarg bug: 5347447
128 l_sql := 'select id, value from fii_time_qtr_v where :1 between start_date and end_date';
129 execute immediate l_sql INTO x_time_level_id, x_time_level_value using p_asof_date;
130
131 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rqtr_start(:2); END;';
132 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_start_date, IN p_asof_date;
133
134
135 --add_months(p_asof_Date,-3)+1;
136 x_end_date := p_asof_date;
137 --x_time_level_id := sysdate;
138 --x_time_level_value := sysdate;
139 return;
140 end if;
141 if (p_dimensionlevel = 'TIME+FII_ROLLING_YEAR') then
142 -- P1 3502644
143 -- x_start_Date := fii_time_api.ryr_start(p_asof_Date);
144 -- ashgarg bug: 5347447
145 l_sql := 'select id, value from fii_time_year_v where :1 between start_date and end_date';
146 execute immediate l_sql INTO x_time_level_id, x_time_level_value using p_asof_date;
147
148
149 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ryr_start(:2); END;';
150 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_start_date, IN p_asof_date;
151
152 --add_months(p_asof_Date,-12)+1;
153 x_end_date := p_asof_date;
154 --x_time_level_id := sysdate;
155 --x_time_level_value := sysdate;
156 return;
157 end if;
158
159 if (p_mode = 'GET_YEARLY') then
160 l_mode := 'GET_CURRENT' ;
161 else
162 l_mode := p_mode;
163 end if;
164
165 BIS_PMV_PARAMETERS_PVT.GET_TIME_INFO(
166 p_region_code => p_region_Code
167 ,p_responsibility_id => p_responsibility_id
168 ,p_parameter_name => p_Dimensionlevel
169 ,p_mode => l_mode
170 ,p_date => p_asof_date
171 ,x_time_description => x_time_level_Value
172 ,x_time_id => x_time_level_id
173 ,x_start_date => x_start_date
174 ,x_end_date => x_end_date
175 ,x_return_status => x_return_status
176 ,x_msg_count => x_msg_count
177 ,x_msg_data => x_msg_data
178 );
179
180 END;
181 PROCEDURE GET_PREVIOUS_ASOF_DATE
182 (p_DimensionLevel IN VARCHAR2
183 ,p_time_comparison_type IN VARCHAR2
184 ,p_asof_date IN DATE
185 ,x_prev_asof_Date OUT NOCOPY DATE
186 ,x_Return_status OUT NOCOPY VARCHAR2
187 ,x_msg_count OUT NOCOPY NUMBER
188 ,x_msg_data OUT NOCOPY VARCHAR2
189 )
190 IS
191 l_sql varchar2(32000);
192 l_temp varchar2(3200);
193 -- P1 3502644
194 l_dynamic_sql varchar2(2000) ;
195 BEGIN
196 /*IF (p_dimensionlevel = 'TIME+FII_ROLLING_MONTH' or
197 p_dimensionlevel = 'TIME+FII_ROLLING_QTR' or
198 p_dimensionlevel = 'TIME+FII_ROLLING_WEEK' or
199 p_Dimensionlevel = 'TIME+FII_ROLLING_YEAR')
200 THEN
201 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_lyr_end(:2); end;';
202 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
203 return;
204 END IF;*/
205
206 IF ( p_time_comparison_type IS NULL OR p_time_comparison_type = 'TIME_COMPARISON_TYPE+SEQUENTIAL') THEN
207 IF (p_DimensionLevel = 'TIME+FII_TIME_WEEK') THEN
208 l_sql := 'BEGIN :1 := FII_TIME_API.sd_pwk(:2); end;';
209 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
210 END IF;
211 IF (p_DimensionLevel = 'TIME+FII_TIME_ENT_PERIOD') THEN
212 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_pper_end(:2); end;';
213 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
214 END IF;
215 IF (p_DimensionLevel = 'TIME+FII_TIME_ENT_QTR') THEN
216 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_pqtr_end (:2); end;';
217 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
218 END IF;
219 IF (p_DimensionLevel = 'TIME+FII_TIME_ENT_YEAR') THEN
220 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_lyr_end(:2); end;';
221 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
222 END IF;
223 IF (p_DimensionLevel = 'TIME+FII_TIME_DAY') THEN
224 x_prev_asof_date := p_asof_date-1;
225 END IF;
226 --Bug Fix#3432746 Use Fii Apis for Rolling Time as well
227 IF (p_DimensionLevel = 'TIME+FII_ROLLING_WEEK') THEN
228 --x_prev_asof_date := p_asof_date-7;
229 -- P1 3502644
230 -- x_prev_asof_date := fii_time_api.rwk_start(p_asof_date)-1;
231 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rwk_start(:2); END;';
232 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_prev_asof_date, IN p_asof_date;
233 x_prev_asof_date := x_prev_asof_date-1 ;
234
235 END IF;
236 IF (p_DimensionLevel = 'TIME+FII_ROLLING_MONTH') THEN
237 --x_prev_asof_Date := add_months(p_asof_date,-1);
238 -- P1 3502644
239 -- x_prev_asof_date := fii_time_api.rmth_start(p_asof_date)-1;
240 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rmth_start(:2); END;';
241 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_prev_asof_date, IN p_asof_date;
242 x_prev_asof_date := x_prev_asof_date-1 ;
243 END IF;
244 IF (p_DimensionLevel = 'TIME+FII_ROLLING_QTR') THEN
245 -- x_prev_asof_date := add_months(p_asof_date,-3);
246 -- P1 3502644
247 -- x_prev_asof_date := fii_time_api.rqtr_start(p_asof_date)-1;
248 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rqtr_start(:2); END;';
249 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_prev_asof_date, IN p_asof_date;
250 x_prev_asof_date := x_prev_asof_date-1 ;
251 END IF;
252 IF (p_DimensionLevel = 'TIME+FII_ROLLING_YEAR') THEN
253 -- x_prev_asof_date := add_months(p_asof_date,-12);
254 -- P1 3502644
255 -- x_prev_asof_date := fii_time_api.ryr_start(p_asof_date)-1;
256 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ryr_start(:2); END;';
257 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_prev_asof_date, IN p_asof_date;
258 x_prev_asof_date := x_prev_asof_date-1 ;
259 END IF;
260 ELSE
261 IF (p_DimensionLevel = 'TIME+FII_TIME_WEEK') THEN
262 l_sql := 'BEGIN :1 := FII_TIME_API.sd_lyswk(:2); end;';
263 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
264 END IF;
265 IF (p_DimensionLevel = 'TIME+FII_TIME_ENT_PERIOD') THEN
266 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_lysper_end(:2); end;';
267 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
268 END IF;
269 IF (p_DimensionLevel = 'TIME+FII_TIME_ENT_QTR') THEN
270 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_lysqtr_end(:2); end;';
271 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
272 END IF;
273 IF (p_DimensionLevel = 'TIME+FII_TIME_ENT_YEAR' OR p_dimensionlevel = 'TIME+FII_TIME_DAY') THEN
274 l_sql := 'BEGIN :1 := FII_TIME_API.ent_sd_lyr_end(:2); end;';
275 execute immediate l_sql USING OUT x_prev_asof_date , IN p_asof_date;
276 END IF;
277 IF (p_dimensionlevel = 'TIME+FII_ROLLING_WEEK' or
278 p_dimensionlevel = 'TIME+FII_ROLLING_MONTH' or
279 p_dimensionlevel = 'TIME+FII_ROLLING_QTR' or
280 p_dimensionlevel = 'TIME+FII_ROLLING_YEAR') THEN
281 x_prev_asof_date := add_months(p_asof_date,-12);
282 END IF;
283 END IF;
284 IF (x_prev_asof_Date IS NULL or (length(x_prev_asof_date) = 0)) THEN
285 GET_BIS_COMMON_START_DATE
286 (x_prev_asof_Date => x_prev_asof_date
287 ,x_return_Status => x_return_status
288 ,x_msg_count => x_msg_count
289 ,x_msg_data => x_msg_data );
290 END IF;
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292 EXCEPTION
293 WHEN FND_API.G_EXC_ERROR THEN
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
299 WHEN OTHERS THEN
300 IF (x_prev_asof_Date IS NULL or (length(x_prev_asof_date) = 0)) THEN
301 GET_BIS_COMMON_START_DATE
302 (x_prev_asof_Date => x_prev_asof_date
303 ,x_return_Status => x_return_status
304 ,x_msg_count => x_msg_count
305 ,x_msg_data => x_msg_data );
306 END IF;
307 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
308 END;
309 PROCEDURE GET_BIS_COMMON_START_DATE
310 (x_prev_asof_Date OUT NOCOPY DATE
311 ,x_return_Status OUT NOCOPY VARCHAR2
312 ,x_msg_count OUT NOCOPY NUMBER
313 ,x_msg_data OUT NOCOPY VARCHAR2
314 )
315 IS
316 l_sql VARCHAr2(2000);
317 --l_format VARCHAR2(20) := 'DD-MON-YYYY';
318 BEGIN
319 l_sql := 'BEGIN :1 := bis_common_parameters.get_global_start_date-1; end;';
320 execute immediate l_sql USING OUT x_prev_asof_date;
321 --As of Date 3094234--Already returning date so no need to format
322 --x_prev_asof_date := to_date(x_prev_asof_date, l_format);
323 EXCEPTION
324 WHEN OTHERS THEN
325 NULL;
326 END;
327 PROCEDURE GET_REPORT_START_DATE
328 (p_time_comparison_type IN VARCHAR2
329 ,p_asof_date IN DATE
330 ,p_time_level IN VARCHAR2
331 ,x_report_start_date OUT NOCOPY DATE
332 ,x_return_status OUT NOCOPY VARCHAR2
333 ,x_msg_count OUT NOCOPY NUMBER
334 ,x_msg_data OUT NOCOPY VARCHAR2
335 )
336 IS
337 l_sql VARCHAR2(32767);
338 l_curr_year number;
339 l_curr_qtr number;
340 l_curr_period number;
341 l_week_start_date date;
342
343 BEGIN
344 IF (p_time_level = 'TIME+FII_TIME_ENT_YEAR') THEN
345 l_sql := 'select sequence from fii_time_ent_year where :1 between start_date and end_date';
346 execute immediate l_sql INTO l_curr_year using p_asof_date;
347 l_sql := 'select min(start_date) from fii_time_ent_year where sequence >= :l_curr_year-3';
348 execute immediate l_sql INTO x_report_start_date using l_curr_year;
349 END IF;
350 IF (p_time_level = 'TIME+FII_TIME_ENT_QTR') THEN
351 l_sql := 'select sequence, ent_year_id from fii_time_ent_qtr where :p_asof_date between start_date and end_date';
352 execute immediate l_sql INTO l_curr_qtr, l_curr_year using p_asof_Date;
353 if (p_time_comparison_type = 'TIME_COMPARISON_TYPE+YEARLY') then
354 l_sql := 'select start_date from (select start_Date from fii_time_ent_qtr where ((sequence >=:l_curr_qtr+1 and '||
355 ' ent_year_id = :l_curr_year-1) or (sequence>=1 and ent_year_id = :l_curr_year)) order by start_date) '||
356 ' where rownum <=1 ';
357 execute immediate l_sql into x_report_start_date using l_curr_qtr, l_curr_year, l_curr_year ;
358 else
362 execute immediate l_sql into x_report_start_date using l_curr_qtr, l_curr_year, l_curr_year;
359 l_sql := 'select start_date from (select start_date from fii_time_ent_qtr where ((sequence >=:l_curr_qtr+1 and '||
360 ' ent_year_id = :l_curr_year-2) or (sequence>=1 and ent_year_id = :l_curr_year-1)) order by start_date) '||
361 ' where rownum <=1 ';
363 end if;
364 END IF;
365 IF (p_time_level = 'TIME+FII_TIME_ENT_PERIOD') THEN
366 l_sql := 'select p.sequence,q.ent_year_id FROM fii_time_ent_period p , fii_time_ent_qtr q '||
367 ' where p.ent_qtr_id=q.ent_qtr_id and :p_asof_date between p.start_Date and p.end_date';
368 execute immediate l_sql INTO l_curr_period, l_curr_year using p_asof_Date;
369 l_sql := 'SELECT start_date FROM (select p.start_date from fii_time_ent_period p, '||
370 ' fii_time_ent_qtr q where p.ent_qtr_id = q.ent_qtr_id and '||
371 ' ((p.sequence >= :l_curr_period+1 and q.ent_year_id = :l_curr_year-1) or '||
372 ' (p.sequence >= 1 and q.ent_year_id = :l_curr_year)) order by p.start_date) '||
373 ' where rownum <= 1';
374 execute immediate l_sql into x_report_start_date using l_curr_period, l_curr_year, l_curr_year;
375 END IF;
376 IF (p_time_level = 'TIME+FII_TIME_WEEK') THEN
377 l_sql := 'select start_Date from fii_time_Week where :p_asof_date between start_date and end_date';
378 execute immediate l_sql INTO l_week_start_date using p_asof_Date;
379 l_sql := 'select min(start_date) from fii_time_Week where start_date >= :l_week_start_Date-7*12';
380 execute immediate l_sql into x_report_Start_date using l_week_Start_date;
381 END IF;
382 IF (p_time_level = 'TIME+FII_TIME_DAY') THEN --Bug.Fix.3921033
383 x_report_start_date := p_asof_Date - 6;
384 END IF;
385 IF (p_time_level = 'TIME+FII_ROLLING_WEEK' OR
386 p_time_level = 'TIME+FII_ROLLING_MONTH' OR
387 p_time_level = 'TIME+FII_ROLLING_QTR' OR
388 p_time_level = 'TIME+FII_ROLLING_YEAR') then
389 x_report_start_date := sysdate;
390 END IF;
391 EXCEPTION
392 WHEN OTHERS THEN
393 l_sql := 'BEGIN :1 := bis_common_parameters.get_global_start_date; END;';
394 execute immediate l_sql USING OUT x_report_Start_date ;
395 END;
396
397
398 /*-----BugFix#2887200 -ansingh-------*/
399 PROCEDURE GET_TIME_PARAMETER_RECORD (
400 p_TimeParamterName IN VARCHAR2,
401 p_DateParameter IN DATE,
402 x_parameterRecord OUT NOCOPY BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
403 x_return_Status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_Data OUT NOCOPY VARCHAR2
406 ) IS
407
408 l_ParameterRecord BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
409
410 BEGIN
411
412 l_ParameterRecord.dimension := NULL;
413 l_ParameterRecord.default_flag := 'N';
414 l_ParameterRecord.parameter_name := p_TimeParamterName;
415 --l_ParameterRecord.parameter_description := to_char(p_DateParameter,'DD-MON-YYYY');
416 --l_ParameterRecord.parameter_value := to_char(p_DateParameter,'DD-MON-YYYY');
417 --As of Date 3094234--dd/mm/yyyy format
418 l_ParameterRecord.parameter_description := to_char(p_DateParameter,'DD/MM/YYYY');
419 l_ParameterRecord.parameter_value := to_char(p_DateParameter,'DD/MM/YYYY');
420 --BugFix 3308824
421 l_ParameterRecord.period_date := p_DateParameter;
422
423
424 x_parameterRecord := l_ParameterRecord;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
430 END;
431
432 /*-----BugFix#2887200 -ansingh-------*/
433 /*
434 nbarik - Bug Fix 2999602 Added x_prev_effective_start_date and x_prev_effective_end_date
435 */
436 PROCEDURE GET_COMPUTED_DATES (
437 p_region_code IN VARCHAR2,
438 p_resp_id IN VARCHAR2,
439 p_time_comparison_type IN VARCHAR2,
440 p_asof_date IN VARCHAR2,
441 p_time_level IN VARCHAR2,
442 x_prev_asof_Date OUT NOCOPY DATE,
443 x_curr_effective_start_date OUT NOCOPY DATE,
444 x_curr_effective_end_date OUT NOCOPY DATE,
445 x_curr_report_Start_date OUT NOCOPY DATE,
446 x_prev_report_Start_date OUT NOCOPY DATE,
447 x_time_level_id OUT NOCOPY VARCHAR2,
448 x_time_level_value OUT NOCOPY VARCHAR2,
449 x_prev_effective_start_date OUT NOCOPY DATE,
450 x_prev_effective_end_date OUT NOCOPY DATE,
451 x_prev_time_level_id OUT NOCOPY VARCHAR2,
452 x_prev_time_level_value OUT NOCOPY VARCHAR2,
453 x_return_status OUT NOCOPY VARCHAR2,
454 x_msg_count OUT NOCOPY NUMBER,
455 x_msg_Data OUT NOCOPY VARCHAR2
456 )
457 IS
458 l_asof_date DATE;
459 l_time_level_id VARCHAR2(2000);
460 l_time_level_value VARCHAR2(2000);
461 l_current_report_start_date DATE;
462 l_prev_asof_Date DATE;
463 l_prev_report_Start_date DATE;
464 l_date DATE;
465
466 l_Start_date DATE;
467 l_end_date DATE;
468 l_prev_effective_start_date DATE;
469 l_prev_effective_end_date DATE;
470 l_prev_time_level_id VARCHAR2(2000);
474 if (p_asof_Date is not null) then
471 l_prev_time_level_value VARCHAR2(2000);
472 l_use_current_mode BOOLEAN := FALSE;
473 BEGIN
475 l_asof_Date := to_Date(p_asof_Date,'DD/MM/YYYY');
476 else
477 l_asof_date := sysdate;
478 end if;
479 BIS_PMV_TIME_LEVELS_PVT.GET_TIME_LEVEL_INFO (
480 p_dimensionlevel => p_Time_level,
481 p_region_code => p_region_code,
482 p_Responsibility_id => p_resp_id,
483 p_Asof_date => l_asof_date,
484 p_mode => 'GET_CURRENT',
485 x_time_level_id => l_time_level_id,
486 x_time_level_value => l_time_level_Value,
487 x_Start_date => l_start_date,
488 x_end_date => l_end_date,
489 x_return_Status => x_return_status,
490 x_msg_count => x_msg_count,
491 x_msg_data => x_msg_data
492 );
493 BIS_PMV_TIME_LEVELS_PVT.GET_PREVIOUS_ASOF_DATE (
494 p_DimensionLevel => p_time_level,
495 p_time_comparison_type => p_time_comparison_Type,
496 p_asof_date => l_Asof_date,
497 x_prev_asof_Date => l_prev_asof_Date,
498 x_Return_status => x_return_Status,
499 x_msg_count => x_msg_count,
500 x_msg_data => x_msg_data
501 );
502 BIS_PMV_TIME_LEVELS_PVT.GET_REPORT_START_DATE (
503 p_time_comparison_type => p_time_comparison_type,
504 p_asof_date => l_asof_date,
505 p_time_level => p_time_level,
506 x_report_start_date => l_current_report_start_date,
507 x_return_status => x_return_status,
508 x_msg_count => x_msg_count,
509 x_msg_data => x_msg_data
510 );
511 BIS_PMV_TIME_LEVELS_PVT.GET_REPORT_START_DATE (
512 p_time_comparison_type => p_time_comparison_type,
513 p_asof_date => l_prev_asof_date,
514 p_time_level => p_time_level,
515 x_report_start_date => l_prev_report_start_date,
516 x_return_status => x_return_status,
517 x_msg_count => x_msg_count,
518 x_msg_data => x_msg_data
519 );
520
521 -- bug 3090746
522 --IF (p_time_comparison_Type = 'TIME_COMPARISON_TYPE+YEARLY') then
523 l_date := l_prev_asof_date; -- added for bug 4475937
524 l_use_current_mode := TRUE;
525
526 -- ELSE
527 -- l_date := l_asof_date;
528 --END IF;
529
530 IF (p_time_level = 'TIME+FII_ROLLING_WEEK' or
531 p_time_level = 'TIME+FII_ROLLING_MONTH' or
532 p_time_level = 'TIME+FII_ROLLING_QTR' or
533 p_time_level = 'TIME+FII_ROLLING_YEAR') then
534 l_date := l_prev_asof_date;
535 l_use_current_mode := FALSE;
536 end if;
537
538 BIS_PMV_TIME_LEVELS_PVT.GET_PREVIOUS_TIME_LEVEL_VALUE(
539 p_DimensionLevel => p_Time_level
540 ,p_region_code => p_region_code
541 ,p_responsibility_id => p_resp_id
542 ,p_asof_date => l_date
543 ,p_time_comparison_type => p_time_comparison_type
544 ,x_time_level_id => l_prev_time_level_id
545 ,x_time_level_value => l_prev_time_level_value
546 ,x_start_Date => l_prev_effective_start_date
547 ,x_end_date => l_prev_effective_end_date
548 ,x_return_status => x_return_status
549 ,x_msg_count => x_msg_count
550 ,x_msg_data => x_msg_data
551 ,p_use_current_mode => l_use_current_mode
552 );
553
554 x_prev_asof_Date := l_prev_asof_Date;
555 x_curr_effective_start_date := l_Start_date;
556 x_curr_effective_end_date := l_end_date;
557 x_curr_report_Start_date := l_current_report_start_date;
558 x_prev_report_Start_date := l_prev_report_Start_date;
559
560 x_time_level_id := l_time_level_id;
561 x_time_level_value := l_time_level_value;
562 x_prev_effective_start_date := l_prev_effective_start_date;
563 x_prev_effective_end_date := l_prev_effective_end_date;
564 x_prev_time_level_id := l_prev_time_level_id;
565 x_prev_time_level_value := l_prev_time_level_value;
566
567 END;
568
569
570
571 PROCEDURE GET_NESTED_PATTERN
572 (p_time_comparison_type IN VARCHAR2
573 ,p_time_level IN VARCHAR2
574 ,x_nested_pattern OUT NOCOPY VARCHAR2
575 ,x_return_Status OUT NOCOPY VARCHAR2
576 ,x_msg_count OUT NOCOPY NUMBER
577 ,x_msg_Data OUT NOCOPY VARCHAR2
578 )
579 IS
580 BEGIN
581 if(p_time_level = 'FII_TIME_ENT_YEAR') then
585 elsif(p_time_level = 'FII_TIME_ENT_PERIOD') then
582 x_nested_pattern := 119;
583 elsif(p_time_level = 'FII_TIME_ENT_QTR') then
584 x_nested_pattern := 55;
586 x_nested_pattern := 23;
587 elsif(p_time_level = 'FII_TIME_WEEK') then
588 x_nested_pattern := 11;
589 elsif(p_time_level = 'FII_TIME_DAY') then
590 x_nested_pattern := 1;
591 else
592 x_nested_pattern := 119;
593 end if;
594 x_return_Status := FND_API.G_RET_STS_SUCCESS;
595 EXCEPTION
596 WHEN FND_API.G_EXC_ERROR THEN
597 x_return_status := FND_API.G_RET_STS_ERROR;
598 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
602 WHEN OTHERS THEN
603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
605 END;
606
607
608 --Combo Box Enh
609 PROCEDURE GET_POPLIST_DATES (
610 p_asof_date IN DATE,
611 p_rolling IN VARCHAR2 DEFAULT NULL,
612 x_last_week OUT NOCOPY DATE,
613 x_last_period OUT NOCOPY DATE,
614 x_last_qtr OUT NOCOPY DATE,
615 x_last_year OUT NOCOPY DATE,
616 x_week OUT NOCOPY DATE,
617 x_period OUT NOCOPY DATE,
618 x_qtr OUT NOCOPY DATE,
619 x_year OUT NOCOPY DATE,
620 x_rolling_week OUT NOCOPY DATE,
621 x_rolling_period OUT NOCOPY DATE,
622 x_rolling_qtr OUT NOCOPY DATE,
623 x_rolling_year OUT NOCOPY DATE,
624 x_return_status OUT NOCOPY VARCHAR2,
625 x_msg_count OUT NOCOPY NUMBER,
626 x_msg_Data OUT NOCOPY VARCHAR2
627 )
628 IS
629 l_asof_date DATE;
630 l_dynamic_sql varchar2(2000) ;
631 BEGIN
632
633 IF (p_asof_date IS NOT NULL) THEN
634 l_asof_date := p_asof_date;
635 ELSE
636 l_asof_date := sysdate;
637 END IF;
638
639 IF ( (p_rolling = 'Y') OR (p_rolling = 'C') ) THEN
640 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rwk_start(:2); END;';
641 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_rolling_week, IN l_asof_date;
642
643 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rmth_start(:2); END;';
644 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_rolling_period, IN l_asof_date;
645
646 l_dynamic_sql := 'BEGIN :1 := fii_time_api.rqtr_start(:2); END;';
647 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_rolling_qtr, IN l_asof_date;
648
649 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ryr_start(:2); END;';
650 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_rolling_year, IN l_asof_date;
651 END IF;
652
653 IF ( (p_rolling = 'N') OR (p_rolling = 'C') ) THEN
654 l_dynamic_sql := 'BEGIN :1 := fii_time_api.pwk_end(:2); END;';
655 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_last_week, IN l_asof_date;
656
657 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_pper_end(:2); END;';
658 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_last_period, IN l_asof_date;
659
660 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_pqtr_end(:2); END;';
661 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_last_qtr, IN l_asof_date;
662
663 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_pyr_end(:2); END;';
664 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_last_year, IN l_asof_date;
665
666 l_dynamic_sql := 'BEGIN :1 := fii_time_api.cwk_end(:2); END;';
667 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_week, IN l_asof_date;
668
669 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_cper_end(:2); END;';
670 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_period, IN l_asof_date;
671
672 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_cqtr_end(:2); END;';
673 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_qtr, IN l_asof_date;
674
675 l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_cyr_end(:2); END;';
676 EXECUTE IMMEDIATE l_dynamic_sql USING OUT x_year, IN l_asof_date;
677 END IF;
678 x_return_Status := FND_API.G_RET_STS_SUCCESS;
679
680 EXCEPTION
681 WHEN FND_API.G_EXC_ERROR THEN
682 x_return_status := FND_API.G_RET_STS_ERROR;
683 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
684 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
687 WHEN OTHERS THEN
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
690 END GET_POPLIST_DATES;
691
692 END BIS_PMV_TIME_LEVELS_PVT;