[Home] [Help]
PACKAGE BODY: APPS.OZF_TIME_API_PVT
Source
1 PACKAGE BODY OZF_TIME_API_PVT AS
2 /*$Header: ozfvtiab.pls 120.1 2005/09/26 16:41:28 mkothari noship $*/
3
4 g_debug_flag VARCHAR2(1) := 'Y';
5
6 FUNCTION get_period_start_date(p_time_id number,
7 p_period_type_id number)
8 RETURN DATE IS
9 l_time_id number;
10 l_curr_period number;
11 l_curr_qtr number;
12 l_curr_year number;
13 l_start_date date;
14 BEGIN
15 l_time_id := p_time_id;
16
17 IF p_period_type_id=1 THEN
18
19 SELECT start_date INTO l_start_date
20 FROM ozf_time_day
21 WHERE report_date_julian=p_time_id;
22
23 ELSIF p_period_type_id=16 THEN
24
25 SELECT start_date INTO l_start_date
26 FROM ozf_time_week
27 WHERE week_id=p_time_id;
28
29 ELSIF p_period_type_id=32 THEN
30
31 SELECT start_date INTO l_start_date
32 FROM ozf_time_ent_period
33 WHERE ent_period_id=p_time_id;
34
35 ELSIF p_period_type_id=64 THEN
36
37 SELECT start_date INTO l_start_date
38 FROM ozf_time_ent_qtr
39 WHERE ent_qtr_id=p_time_id;
40
41 ELSIF p_period_type_id=128 THEN
42
43 SELECT start_date INTO l_start_date
44 FROM ozf_time_ent_year
45 WHERE ent_year_id=p_time_id;
46
47 END IF;
48
49 return l_start_date;
50
51 END get_period_start_date;
52
53 FUNCTION get_period_end_date(p_time_id number,
54 p_period_type_id number)
55 RETURN DATE IS
56 l_time_id number;
57 l_curr_period number;
58 l_curr_qtr number;
59 l_curr_year number;
60 l_end_date date;
61 BEGIN
62 l_time_id := p_time_id;
63
64 IF p_period_type_id=1 THEN
65
66 SELECT end_date INTO l_end_date
67 FROM ozf_time_day
68 WHERE report_date_julian=p_time_id;
69
70 ELSIF p_period_type_id=16 THEN
71
72 SELECT end_date INTO l_end_date
73 FROM ozf_time_week
74 WHERE week_id=p_time_id;
75
76 ELSIF p_period_type_id=32 THEN
77
78 SELECT end_date INTO l_end_date
79 FROM ozf_time_ent_period
80 WHERE ent_period_id=p_time_id;
81
82 ELSIF p_period_type_id=64 THEN
83
84 SELECT end_date INTO l_end_date
85 FROM ozf_time_ent_qtr
86 WHERE ent_qtr_id=p_time_id;
87
88 ELSIF p_period_type_id=128 THEN
89
90 SELECT end_date INTO l_end_date
91 FROM ozf_time_ent_year
92 WHERE ent_year_id=p_time_id;
93
94 END IF;
95
96 return l_end_date;
97
98 END get_period_end_date;
99
100
101 FUNCTION get_period_name(p_time_id number,
102 p_period_type_id number)
103 RETURN VARCHAR2 IS
104 l_time_id number;
105 l_curr_period number;
106 l_curr_qtr number;
107 l_curr_year number;
108 l_name varchar2(100);
109 BEGIN
110 l_time_id := p_time_id;
111
112 IF p_period_type_id=1 THEN
113
114 SELECT TO_CHAR(report_date) INTO l_name
115 FROM ozf_time_day
116 WHERE report_date_julian=p_time_id;
117
118 ELSIF p_period_type_id=16 THEN
119
120 SELECT name INTO l_name
121 FROM ozf_time_week
122 WHERE week_id=p_time_id;
123
124 ELSIF p_period_type_id=32 THEN
125
126 SELECT name INTO l_name
127 FROM ozf_time_ent_period
128 WHERE ent_period_id=p_time_id;
129
130 ELSIF p_period_type_id=64 THEN
131
132 SELECT name INTO l_name
133 FROM ozf_time_ent_qtr
134 WHERE ent_qtr_id=p_time_id;
135
136 ELSIF p_period_type_id=128 THEN
137
138 SELECT name INTO l_name
139 FROM ozf_time_ent_year
140 WHERE ent_year_id=p_time_id;
141
142 END IF;
143
144 return l_name;
145
146 END get_period_name;
147
148 FUNCTION get_lysp_period_name(p_time_id number,
149 p_period_type_id number)
150 RETURN VARCHAR2 IS
151 l_time_id number;
152 l_curr_period number;
153 l_curr_qtr number;
154 l_curr_year number;
155 l_name varchar2(100);
156 BEGIN
157 l_time_id := get_lysp_id(p_time_id, p_period_type_id);
158
159 IF p_period_type_id=1 OR p_period_type_id=16 THEN
160 l_name := NULL;
161
162 ELSIF p_period_type_id=32 THEN
163
164 SELECT name INTO l_name
165 FROM ozf_time_ent_period
166 WHERE ent_period_id=l_time_id;
167
168 ELSIF p_period_type_id=64 THEN
169
170 SELECT name INTO l_name
171 FROM ozf_time_ent_qtr
172 WHERE ent_qtr_id=l_time_id;
173
174 ELSIF p_period_type_id=128 THEN
175
176 SELECT name INTO l_name
177 FROM ozf_time_ent_year
178 WHERE ent_year_id=l_time_id;
179
180 END IF;
181
182 return l_name;
183
184 END get_lysp_period_name;
185
186
187
188 FUNCTION GET_LYSP_ID(p_time_id number,
189 p_period_type_id number)
190 RETURN NUMBER IS
191 l_time_id number;
192 l_curr_period number;
193 l_curr_qtr number;
194 l_curr_year number;
195 BEGIN
196 l_time_id := p_time_id;
197
198 IF p_period_type_id=1 OR p_period_type_id=16 THEN
199 l_time_id := NULL; -- if required think of this logic
200
201 ELSIF p_period_type_id=32 THEN
202
203 SELECT sequence, ent_year_id INTO l_curr_period, l_curr_year
204 FROM ozf_time_ent_period
205 WHERE ent_period_id=p_time_id;
206
207 SELECT ent_period_id INTO l_time_id
208 FROM ozf_time_ent_period
209 WHERE sequence=l_curr_period
210 AND ent_year_id=l_curr_year-1;
211
212 ELSIF p_period_type_id=64 THEN
213
214 SELECT sequence, ent_year_id INTO l_curr_qtr, l_curr_year
215 FROM ozf_time_ent_qtr
216 WHERE ent_qtr_id=p_time_id;
217
218 SELECT ent_qtr_id INTO l_time_id
219 FROM ozf_time_ent_qtr
220 WHERE sequence=l_curr_qtr
221 AND ent_year_id=l_curr_year-1;
222
223 ELSIF p_period_type_id=128 THEN
224 l_time_id := p_time_id-1;
225
226 END IF;
227
228 return l_time_id;
229
230 END GET_LYSP_ID;
231
232
233 FUNCTION GET_PERIOD_TBL(p_start_date varchar2,
234 p_end_date varchar2,
235 p_period_type_id number)
236 RETURN G_PERIOD_TBL_TYPE IS
237
238 G_OZF_PARAMETER_NOT_SETUP EXCEPTION;
239 l_period_type_id NUMBER := 0;
240 l_index NUMBER := 0;
241 l_period_tbl G_PERIOD_TBL_TYPE;
242 l_start_date date;
243 l_end_date date;
244
245 Cursor get_year_ids_csr (l_start_date date,
246 l_end_date date) IS
247 SELECT
248 ent_year_id
249 FROM ozf_time_ent_year
250 WHERE start_date >= l_start_date
251 AND end_date <= l_end_date;
252
253 Cursor get_qtr_ids_csr (l_start_date date,
254 l_end_date date) IS
255 SELECT
256 ent_qtr_id
257 FROM ozf_time_ent_qtr
258 WHERE start_date >= l_start_date
259 AND end_date <= l_end_date;
260
261 Cursor get_month_ids_csr (l_start_date date,
262 l_end_date date) IS
263 SELECT
264 ent_period_id
265 FROM ozf_time_ent_period
266 WHERE start_date >= l_start_date
267 AND end_date <= l_end_date;
268
269 Cursor get_week_ids_csr (l_start_date date,
270 l_end_date date) IS
271 SELECT
272 week_id
273 FROM ozf_time_week
274 WHERE start_date >= l_start_date
275 AND end_date <= l_end_date;
276
277 BEGIN
278
279 if (p_start_date is NULL or p_end_date is NULL or p_period_type_id is NULL)
280 THEN
281 raise G_OZF_PARAMETER_NOT_SETUP;
282 end if;
283
284 l_start_date := trunc(to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS'));
285 l_end_date := trunc(to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS'));
286 l_period_type_id := p_period_type_id;
287
288 if g_debug_flag = 'Y' then
289 OZF_TP_UTIL_PVT.put_line('OZF_TIME_API_PVT.GET_PERIOD_TBL: Finding Time_ids of PeriodTypeId '
290 || l_period_type_id ||' From '||l_start_date||' to '||l_end_date);
291 end if;
292
293 IF l_period_type_id = 128 THEN
294 FOR l_period_rec in get_year_ids_csr(l_start_date, l_end_date)
295 LOOP
296 l_period_tbl(l_index) := l_period_rec.ent_year_id;
297 l_index := l_index + 1;
298 END LOOP;
299
300 ELSIF l_period_type_id = 64 THEN
301 FOR l_period_rec in get_qtr_ids_csr(l_start_date, l_end_date)
302 LOOP
303 l_period_tbl(l_index) := l_period_rec.ent_qtr_id;
304 l_index := l_index + 1;
305 END LOOP;
306
307 ELSIF l_period_type_id = 32 THEN
308 FOR l_period_rec in get_month_ids_csr(l_start_date, l_end_date)
309 LOOP
310 l_period_tbl(l_index) := l_period_rec.ent_period_id;
311 l_index := l_index + 1;
312 END LOOP;
313
314 ELSIF l_period_type_id = 16 THEN
315 FOR l_period_rec in get_week_ids_csr(l_start_date, l_end_date)
316 LOOP
317 l_period_tbl(l_index) := l_period_rec.week_id;
318 l_index := l_index + 1;
319 END LOOP;
320
321 END IF;
322
323 return l_period_tbl;
324
325 EXCEPTION
326
327 WHEN G_OZF_PARAMETER_NOT_SETUP THEN
328 if g_debug_flag = 'Y' then
329 OZF_TP_UTIL_PVT.put_line(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
330 end if;
331 WHEN OTHERS THEN
332 rollback;
333 if g_debug_flag = 'Y' then
334 OZF_TP_UTIL_PVT.put_line(sqlcode||' : '||sqlerrm);
335 end if;
336 END GET_PERIOD_TBL;
337
338
339 FUNCTION Is_Quarter_Allowed(p_start_date DATE,
340 p_end_date DATE)
341 RETURN CHAR IS
342
343 Cursor is_qtr_present_csr (l_start_date date,
344 l_end_date date) IS
345 SELECT 'Y'
346 FROM DUAL
347 WHERE TRUNC(l_start_date) IN (SELECT DISTINCT START_DATE FROM OZF_TIME_ENT_QTR)
348 AND TRUNC(l_end_date) IN (SELECT DISTINCT END_DATE FROM OZF_TIME_ENT_QTR);
349
350 l_return_value CHAR := 'N';
351
352 BEGIN
353
354 BEGIN
355 IF (p_start_date is NULL or p_end_date is NULL)
356 THEN
357 l_return_value := 'N';
358 ELSE
359 OPEN is_qtr_present_csr (p_start_date, p_end_date);
360 FETCH is_qtr_present_csr into l_return_value;
361 CLOSE is_qtr_present_csr;
362 END IF;
363 EXCEPTION
364 WHEN OTHERS THEN
365 l_return_value := 'N';
366 END;
367
368 IF l_return_value IS NULL THEN
369 l_return_value := 'N';
370 END IF;
371
372 return l_return_value;
373
374 END Is_Quarter_Allowed;
375
376
377
378 FUNCTION Is_Period_Range_Valid(p_start_date DATE,
379 p_end_date DATE)
380 RETURN CHAR IS
381
382 Cursor no_of_periods_csr (l_start_date date,
383 l_end_date date) IS
384 SELECT COUNT(ent_period_id)
385 FROM OZF_TIME_ENT_PERIOD
386 WHERE START_DATE >= TRUNC(p_start_date)
387 AND END_DATE <= TRUNC(p_end_date);
388
389 l_return_value NUMBER := 13;
390
391 BEGIN
392
393 BEGIN
394 IF (p_start_date is NULL or p_end_date is NULL)
395 THEN
396 l_return_value := 13;
397 ELSE
398 OPEN no_of_periods_csr (p_start_date, p_end_date);
399 FETCH no_of_periods_csr into l_return_value;
400 CLOSE no_of_periods_csr;
401 END IF;
402 EXCEPTION
403 WHEN OTHERS THEN
404 l_return_value := 13;
405 END;
406
407 IF l_return_value IS NULL OR l_return_value > 12 THEN
408 return 'N';
409 ELSE
410 return 'Y';
411 END IF;
412
413 END Is_Period_Range_Valid;
414
415
416 END OZF_TIME_API_PVT;