1 PACKAGE BODY POA_TIME_API AS
2 /* $Header: POAQTRSB.pls 115.5 2002/01/24 17:59:46 pkm ship $ */
3
4 g_initialized_set1 BOOLEAN := FALSE;
5 g_initialized_set2 BOOLEAN := FALSE;
6 g_poa_fixed_date DATE := NULL;
7 g_period_set_name varchar2(80) := NULL;
8 g_period_type varchar2(80) := NULL;
9
10 g_ent_cycq_start DATE := NULL;
11 g_ent_cycq_end DATE := NULL;
12 g_ent_lycq_start DATE := NULL;
13 g_ent_lycq_end DATE := NULL;
14 g_ent_cycq_today DATE := NULL;
15 g_ent_lycq_today DATE := NULL;
16 g_ent_cy_start DATE := NULL;
17 g_ent_ly_start DATE := NULL;
18 g_ent_cy_end DATE := NULL;
19 g_ent_ly_end DATE := NULL;
20
21 procedure init_set2 is
22 begin
23 g_initialized_set2 := TRUE;
24
25 g_period_set_name := fnd_profile.value('POA_PERIOD_NAME');
26 g_period_type := fnd_profile.value('POA_PERIOD_TYPE');
27
28 g_poa_fixed_date := to_date(fnd_profile.value('POA_FIXED_DATE'),
29 'MM-DD-YYYY');
30
31 if (g_poa_fixed_date IS NULL) then
32 g_poa_fixed_date := sysdate;
33 end if;
34
35 exception
36 when others then
37 -- This error will generally occur when people did not
38 -- follow the proper date format mask of MM-DD-YYYY,
39 -- in which case, we just ignore the profile
40 g_poa_fixed_date := NULL;
41 end;
42
43 procedure init_set1 is
44
45 x_no_data_found EXCEPTION;
46 x_null_value EXCEPTION;
47
48 cursor c1 is
49 select min(per.start_date),
50 max(per.end_date)
51 from gl_periods per
52 where per.period_set_name = g_period_set_name
53 and per.period_type = g_period_type
54 group by period_year, quarter_num
55 having nvl(g_poa_fixed_date, sysdate)
56 between min(per.start_date) and
57 max(per.end_date);
58
59 cursor c2 is
60 select min(per.start_date),
61 max(per.end_date)
62 from gl_periods per
63 where per.period_set_name = g_period_set_name
64 and per.period_type = g_period_type
65 group by period_year, quarter_num
66 having add_months(nvl(g_poa_fixed_date, sysdate), -12)
67 between min(per.start_date) and
68 max(per.end_date);
69
70 cursor c3 is
71 select min(per.start_date),
72 max(per.end_date)
73 from gl_periods per
74 where per.period_set_name = g_period_set_name
75 and per.period_type = g_period_type
76 group by period_year
77 having nvl(g_poa_fixed_date, sysdate)
78 between min(per.start_date) and
79 max(per.end_date);
80
81 cursor c4 is
82 select min(per.start_date),
83 max(per.end_date)
84 from gl_periods per
85 where per.period_set_name = g_period_set_name
86 and per.period_type = g_period_type
87 group by period_year
88 having add_months(nvl(g_poa_fixed_date, sysdate), -12)
89 between min(per.start_date) and
90 max(per.end_date);
91
92
93 begin
94
95 init_set2;
96
97 OPEN c1;
98 FETCH c1 into g_ent_cycq_start,
99 g_ent_cycq_end;
100
101 IF c1%NOTFOUND THEN
102 CLOSE c1;
103 RAISE x_no_data_found;
104 END IF;
105
106 OPEN c2;
107 FETCH c2 into g_ent_lycq_start,
108 g_ent_lycq_end;
109
110 IF c2%NOTFOUND THEN
111 CLOSE c2;
112 RAISE x_no_data_found;
113 END IF;
114
115 CLOSE c2;
116
117 OPEN c3;
118 FETCH c3 into g_ent_cy_start,
119 g_ent_cy_end;
120
121 IF c3%NOTFOUND THEN
122 CLOSE c3;
123 RAISE x_no_data_found;
124 END IF;
125
126 CLOSE c3;
127
128 OPEN c4;
129 FETCH c4 into g_ent_ly_start,
130 g_ent_ly_end;
131
132 IF c4%NOTFOUND THEN
133 CLOSE c4;
134 RAISE x_no_data_found;
135 END IF;
136
137 CLOSE c4;
138
139 IF (g_ent_cycq_start is NULL or
140 g_ent_cycq_end is NULL or
141 g_ent_lycq_start is NULL or
142 g_ent_lycq_end is NULL or
143 g_ent_cy_start is NULL or
144 g_ent_cy_end is NULL or
145 g_ent_ly_start is NULL or
146 g_ent_ly_end is NULL) THEN
147 RAISE x_null_value;
148 END IF;
149
150 g_initialized_set1 := TRUE;
151
152 exception
153 when x_no_data_found then
154 raise_application_error(-20000, 'No data found');
155 when x_null_value then
156 raise_application_error(-20001, 'Null Values');
157 when others then
158 if c1%ISOPEN then
159 close c1;
160 end if;
161 raise_application_error(-20002, 'Other Error');
162 end init_set1;
163
164 -- ----------------------------------------------------
165 -- ent_cycq_start
166 -- ----------------------------------------------------
167 FUNCTION get_today RETURN DATE IS
168 BEGIN
169 IF (NOT g_initialized_set2) THEN
170 init_set2;
171 END IF;
172 RETURN (trunc(nvl(g_poa_fixed_date, sysdate)));
173 END get_today;
174
175 -- ----------------------------------------------------
176 -- get_cqtr_start
177 -- ----------------------------------------------------
178 FUNCTION get_cqtr_start RETURN DATE IS
179 BEGIN
180 IF (NOT g_initialized_set1) THEN
181 init_set1;
182 END IF;
183 RETURN(g_ent_cycq_start);
184 END get_cqtr_start;
185
186
187 -- ----------------------------------------------------
188 -- get_cqtr_end
189 -- ----------------------------------------------------
190 FUNCTION get_cqtr_end RETURN DATE IS
191 BEGIN
192 IF (NOT g_initialized_set1) THEN
193 init_set1;
194 END IF;
195
196 RETURN(g_ent_cycq_end);
197 END get_cqtr_end;
198
199 -- ----------------------------------------------------
200 -- get_lycq_start
201 -- ----------------------------------------------------
202 FUNCTION get_lycq_start RETURN DATE IS
203 BEGIN
204 IF (NOT g_initialized_set1) THEN
205 init_set1;
206 END IF;
207 RETURN(g_ent_lycq_start);
208 END get_lycq_start;
209
210
211 -- ----------------------------------------------------
212 -- get_lycq_end
213 -- ----------------------------------------------------
214 FUNCTION get_lycq_end RETURN DATE IS
215 BEGIN
216 IF (NOT g_initialized_set1) THEN
217 init_set1;
218 END IF;
219 RETURN(g_ent_lycq_end);
220 END get_lycq_end;
221
222
223 -- ----------------------------------------------------
224 -- get_cy_start
225 -- ----------------------------------------------------
226 FUNCTION get_cy_start RETURN DATE IS
227 BEGIN
228 IF (NOT g_initialized_set1) THEN
229 init_set1;
230 END IF;
231 RETURN(g_ent_cy_start);
232 END get_cy_start;
233
234
235 -- ----------------------------------------------------
236 -- get_cy_end
237 -- ----------------------------------------------------
238 FUNCTION get_cy_end RETURN DATE IS
239 BEGIN
240 IF (NOT g_initialized_set1) THEN
241 init_set1;
242 END IF;
243 RETURN(g_ent_cy_end);
244 END get_cy_end;
245
246
247 -- ----------------------------------------------------
248 -- get_ly_start
249 -- ----------------------------------------------------
250 FUNCTION get_ly_start RETURN DATE IS
251 BEGIN
252 IF (NOT g_initialized_set1) THEN
253 init_set1;
254 END IF;
255 RETURN(g_ent_ly_start);
256 END get_ly_start;
257
258
259 -- ----------------------------------------------------
260 -- get_ly_end
261 -- ----------------------------------------------------
262 FUNCTION get_ly_end RETURN DATE IS
263 BEGIN
264 IF (NOT g_initialized_set1) THEN
265 init_set1;
266 END IF;
267 RETURN(g_ent_ly_end);
268 END get_ly_end;
269
270
271
272 -- ----------------------------------------------------
273 -- get_lycq_today
274 -- ----------------------------------------------------
275 FUNCTION get_lycq_today RETURN DATE IS
276 BEGIN
277 IF (NOT g_initialized_set1) THEN
278 init_set1;
279 END IF;
280 RETURN (g_ent_lycq_end - ( g_ent_cycq_end - trunc(nvl(g_poa_fixed_date,
281 sysdate)) ) );
282 END get_lycq_today;
283
284 end;
[Home] [Help]