1 PACKAGE BODY FII_TIME_WH_API AS
2 /* $Header: FIIQTRSB.pls 120.1 2003/08/25 12:39:11 sgautam noship $ */
3 VERSION CONSTANT CHAR(80) := '$Header: FIIQTRSB.pls 120.1 2003/08/25 12:39:11 sgautam noship $';
4
5 g_initialized_set1 BOOLEAN := FALSE;
6 g_initialized_set2 BOOLEAN := FALSE;
7
8 g_curr_fqtr_start DATE := NULL;
9 g_curr_fqtr_end DATE := NULL;
10
11 g_ent_cycq_start DATE := NULL;
12 g_ent_cycq_end DATE := NULL;
13 g_ent_lycq_start DATE := NULL;
14 g_ent_lycq_end DATE := NULL;
15 g_ent_lycq_today1 DATE := NULL;
16 g_ent_lycq_today2 DATE := NULL;
17 g_ent_cy_start DATE := NULL;
18 g_ent_cy_end DATE := NULL;
19 g_ent_ly_start DATE := NULL;
20 g_ent_ly_end DATE := NULL;
21 g_cycq_pk_key NUMBER := NULL;
22 g_cycm_pk_key NUMBER := NULL;
23
24 procedure init_set2 is
25 begin
26 g_initialized_set2 := TRUE;
27 exception
28 when others then
29 -- This error will generally occur when people did not
30 -- follow the proper date format mask of MM-DD-YYYY,
31 -- in which case, we just ignore the profile
32 NULL;
33 end;
34
35 procedure init_set1 is
36
37 x_no_data_found EXCEPTION;
38 x_null_value EXCEPTION;
39
40 cursor c1 is
41 select trunc(min(least(ecqr_start_date, cqtr_start_date))),
42 trunc(max(greatest(ecqr_end_date, cqtr_end_date))),
43 trunc(min(ecqr_start_date)),
44 trunc(max(ecqr_end_date)),
45 trunc(min(ecyr_start_date)),
46 trunc(max(ecyr_end_date))
47 from edw_time_m
48 where cday_calendar_date = trunc(sysdate);
49
50 cursor c2 is
51 select trunc(min(ecqr_start_date)),
52 trunc(max(ecqr_end_date)),
53 trunc(min(ecyr_start_date)),
54 trunc(max(ecyr_end_date))
55 from edw_time_m
56 where cday_calendar_date = add_months(trunc(sysdate), -12);
57
58 cursor c3 is
59 select cal_qtr_pk_key
60 from edw_time_ep_cal_qtr_ltc
61 where start_date = (
62 select trunc(min(ecqr_start_date))
63 from edw_time_m
64 where cday_calendar_date = trunc(sysdate));
65
66 cursor c4 is
67 select month_pk_key
68 from edw_time_month_ltc
69 where start_date = (select trunc(min(mnth_start_date))
70 from edw_time_m
71 where cday_calendar_date = trunc(sysdate));
72 begin
73
74 init_set2;
75
76 OPEN c1;
77 FETCH c1 into g_curr_fqtr_start,
78 g_curr_fqtr_end,
79 g_ent_cycq_start,
80 g_ent_cycq_end,
81 g_ent_cy_start,
82 g_ent_cy_end;
83
84 IF c1%NOTFOUND THEN
85 CLOSE c1;
86 RAISE x_no_data_found;
87 END IF;
88
89 CLOSE c1;
90
91 OPEN c2;
92 FETCH c2 into g_ent_lycq_start,
93 g_ent_lycq_end,
94 g_ent_ly_start,
95 g_ent_ly_end;
96
97 IF c2%NOTFOUND THEN
98 CLOSE c2;
99 RAISE x_no_data_found;
100 END IF;
101
102 CLOSE c2;
103
104 OPEN c3;
105 FETCH c3 into g_cycq_pk_key;
106
107 IF c3%NOTFOUND THEN
108 CLOSE c3;
109 RAISE x_no_data_found;
110 END IF;
111
112 CLOSE c3;
113
114 OPEN c4;
115 FETCH c4 into g_cycm_pk_key;
116
117 IF c4%NOTFOUND THEN
118 CLOSE c4;
119 RAISE x_no_data_found;
120 END IF;
121
122 CLOSE c4;
123
124
125 IF (g_curr_fqtr_start is NULL or
126 g_curr_fqtr_end is NULL or
127 g_ent_cycq_start is NULL or
128 g_ent_cycq_end is NULL or
129 g_ent_lycq_start is NULL or
130 g_ent_lycq_end is NULL or
131 g_ent_cy_start is NULL or
132 g_ent_cy_end is NULL or
133 g_ent_ly_start is NULL or
134 g_ent_ly_end is NULL or
135 g_cycq_pk_key is NULL or
136 g_cycm_pk_key is NULL) THEN
137 RAISE x_null_value;
138 END IF;
139
140 g_initialized_set1 := TRUE;
141
142 exception
143 when x_no_data_found then
144 raise_application_error(-20000, 'No data found');
145 when x_null_value then
146 raise_application_error(-20001, 'Null Values');
147 when others then
148 if c1%ISOPEN then
149 close c1;
150 end if;
151 raise_application_error(-20002, 'Other Error');
152 end init_set1;
153
154
155 -- ----------------------------------------------------
156 -- ent_cycq_start
157 -- ----------------------------------------------------
158 FUNCTION ent_cycq_start RETURN DATE IS
159 BEGIN
160 IF (NOT g_initialized_set1) THEN
161 init_set1;
162 END IF;
163 RETURN(g_ent_cycq_start);
164 END ent_cycq_start;
165
166
167 -- ----------------------------------------------------
168 -- ent_cycq_end
169 -- ----------------------------------------------------
170 FUNCTION ent_cycq_end RETURN DATE IS
171 BEGIN
172 IF (NOT g_initialized_set1) THEN
173 init_set1;
174 END IF;
175 RETURN(g_ent_cycq_end);
176 END ent_cycq_end;
177
178
179 -- ----------------------------------------------------
180 -- ent_lycq_start
181 -- ----------------------------------------------------
182 FUNCTION ent_lycq_start RETURN DATE IS
183 BEGIN
184 IF (NOT g_initialized_set1) THEN
185 init_set1;
186 END IF;
187 RETURN(g_ent_lycq_start);
188 END ent_lycq_start;
189
190
191 -- ----------------------------------------------------
192 -- ent_lycq_end
193 -- ----------------------------------------------------
194 FUNCTION ent_lycq_end RETURN DATE IS
195 BEGIN
196 IF (NOT g_initialized_set1) THEN
197 init_set1;
198 END IF;
199 RETURN(g_ent_lycq_end);
200 END ent_lycq_end;
201
202
203 -- ----------------------------------------------------
204 -- ent_cy_start
205 -- ----------------------------------------------------
206 FUNCTION ent_cy_start RETURN DATE IS
207 BEGIN
208 IF (NOT g_initialized_set1) THEN
209 init_set1;
210 END IF;
211 RETURN(g_ent_cy_start);
212 END ent_cy_start;
213
214
215 -- ----------------------------------------------------
216 -- ent_cy_end
217 -- ----------------------------------------------------
218 FUNCTION ent_cy_end RETURN DATE IS
219 BEGIN
220 IF (NOT g_initialized_set1) THEN
221 init_set1;
222 END IF;
223 RETURN(g_ent_cy_end);
224 END ent_cy_end;
225
226
227 -- ----------------------------------------------------
228 -- ent_ly_start
229 -- ----------------------------------------------------
230 FUNCTION ent_ly_start RETURN DATE IS
231 BEGIN
232 IF (NOT g_initialized_set1) THEN
233 init_set1;
234 END IF;
235 RETURN(g_ent_ly_start);
236 END ent_ly_start;
237
238
239 -- ----------------------------------------------------
240 -- ent_ly_end
241 -- ----------------------------------------------------
242 FUNCTION ent_ly_end RETURN DATE IS
243 BEGIN
244 IF (NOT g_initialized_set1) THEN
245 init_set1;
246 END IF;
247 RETURN(g_ent_ly_end);
248 END ent_ly_end;
249
250
251 -- ----------------------------------------------------
252 -- ent_lycq_today1
253 -- ----------------------------------------------------
254 FUNCTION ent_lycq_today1 RETURN DATE IS
255 BEGIN
256 IF (NOT g_initialized_set1) THEN
257 init_set1;
258 END IF;
259 RETURN (g_ent_lycq_end - ( g_ent_cycq_end - trunc(sysdate) ) );
260 END ent_lycq_today1;
261
262
263 -- ----------------------------------------------------
264 -- ent_lycq_today2
265 -- ----------------------------------------------------
266 FUNCTION ent_lycq_today2 RETURN DATE IS
267 BEGIN
268 IF (NOT g_initialized_set1) THEN
269 init_set1;
270 END IF;
271 RETURN (g_ent_lycq_start + (trunc(sysdate) - g_ent_cycq_start));
272 END ent_lycq_today2;
273
274
275 -- ----------------------------------------------------
276 -- today
277 -- ----------------------------------------------------
278 FUNCTION today RETURN DATE IS
279 BEGIN
280 IF (NOT g_initialized_set2) THEN
281 init_set2;
282 END IF;
283 RETURN (trunc(sysdate));
284 END today;
285
286
287 -- ----------------------------------------------------
288 -- todaytime
289 -- ----------------------------------------------------
290 FUNCTION todaytime RETURN DATE IS
291 BEGIN
292 IF (NOT g_initialized_set2) THEN
293 init_set2;
294 END IF;
295 RETURN (sysdate);
296 END todaytime;
297
298
299 -- ----------------------------------------------------
300 -- get_fqtr_start
301 -- ----------------------------------------------------
302 FUNCTION get_fqtr_start RETURN DATE IS
303 BEGIN
304 IF (NOT g_initialized_set1) THEN
305 init_set1;
306 END IF;
307 RETURN(g_curr_fqtr_start);
308 END get_fqtr_start;
309
310
311 -- ----------------------------------------------------
312 -- get_fqtr_end
313 -- ----------------------------------------------------
314 FUNCTION get_fqtr_end RETURN DATE IS
315 BEGIN
316 IF (NOT g_initialized_set1) THEN
317 init_set1;
318 END IF;
319 RETURN(g_curr_fqtr_end);
320 END get_fqtr_end;
321
322
323 -- ----------------------------------------------------
324 -- get_curr_eqtr_start
325 -- ----------------------------------------------------
326 FUNCTION get_curr_eqtr_start RETURN DATE IS
327 BEGIN
328 RETURN(ent_cycq_start);
329 END get_curr_eqtr_start;
330
331
332 -- ----------------------------------------------------
333 -- get_curr_eqtr_end
334 -- ----------------------------------------------------
335 FUNCTION get_curr_eqtr_end RETURN DATE IS
336 BEGIN
337 RETURN(ent_cycq_end);
338 END get_curr_eqtr_end;
339
340 -- ----------------------------------------------------
341 -- get_cycq_pk_key
342 -- ----------------------------------------------------
343 FUNCTION get_cycq_pk_key RETURN NUMBER IS
344 BEGIN
345 IF (NOT g_initialized_set1) THEN
346 init_set1;
347 END IF;
348 RETURN(g_cycq_pk_key);
349 END get_cycq_pk_key;
350
351
352 -- ----------------------------------------------------
353 -- get_cycm_pk_key
354 -- ----------------------------------------------------
355 FUNCTION get_cycm_pk_key RETURN NUMBER IS
356 BEGIN
357 IF (NOT g_initialized_set1) THEN
358 init_set1;
359 END IF;
360 RETURN(g_cycm_pk_key);
361 END get_cycm_pk_key;
362
363 -- ----------------------------------------------------
364 -- ent_today
365 -- ----------------------------------------------------
366 FUNCTION ent_today RETURN DATE IS
367 BEGIN
368 IF (NOT g_initialized_set2) THEN
369 init_set2;
370 END IF;
371 RETURN trunc(sysdate);
372 END ent_today;
373
374 end;
[Home] [Help]