[Home] [Help]
PACKAGE BODY: APPS.HXC_APP_PERIOD_SUMMARY_API
Source
1 package body hxc_app_period_summary_api as
2 /* $Header: hxcapsumapi.pkb 120.2.12010000.1 2008/07/28 11:04:41 appldev ship $ */
3 Procedure delete_app_period
4 (p_application_period_id in hxc_app_period_summary.application_period_id%type
5 ) is
6
7 Begin
8
9 --
10 -- 1. Remove all the links between the app_period and the timecards
11 --
12 hxc_tc_ap_links_pkg.remove_app_period_links
13 (p_application_period_id => p_application_period_id);
14 --
15 -- 2. Remove all detail links between details and application
16 -- periods
17 --
18 hxc_ap_detail_links_pkg.delete_ap_detail_links
19 (p_application_period_id => p_application_period_id);
20
21 --
22 -- 3. Remove the application period summary row itself
23 --
24 hxc_app_period_summary_pkg.delete_summary_row
25 (p_app_period_id => p_application_period_id);
26
27 End delete_app_period;
28
29 procedure app_period_clean_up
30 (p_application_period_id in hxc_app_period_summary.application_period_id%type
31 ,p_mode in varchar2
32 ) is
33
34 cursor app_period_info
35 (p_id in hxc_app_period_summary.application_period_id%type) is
36 select tbb.resource_id
37 ,tbb.start_time
38 ,tbb.stop_time
39 ,ta.attribute1 time_recipient_id
40 from hxc_time_building_blocks tbb, hxc_time_Attributes ta, hxc_time_attribute_usages tau
41 where tbb.time_building_block_id = p_id
42 and tbb.date_to = hr_general.end_of_time
43 and tau.time_building_block_id = tbb.time_building_block_id
44 and tau.time_building_block_ovn = tbb.object_version_number
45 and tau.time_attribute_id = ta.time_attribute_id
46 and ta.attribute_category = 'APPROVAL';
47
48 cursor app_periods_to_remove
49 (p_resource_id in hxc_app_period_summary.resource_id%type
50 ,p_start_time in hxc_app_period_summary.start_time%type
51 ,p_stop_time in hxc_app_period_summary.stop_time%type
52 ,p_time_recipient_id in hxc_app_period_summary.time_recipient_id%type) is
53 select application_period_id
54 from hxc_app_period_summary
55 where resource_id = p_resource_id
56 and start_time <= p_stop_time
57 and stop_time >= p_start_time
58 and time_recipient_id = p_time_recipient_id;
59
60 l_resource_id hxc_app_period_summary.resource_id%type;
61 l_start_time hxc_app_period_summary.start_time%type;
62 l_stop_time hxc_app_period_summary.stop_time%type;
63 l_time_recipient_id hxc_app_period_summary.time_recipient_id%type;
64
65 Begin
66
67 if(p_mode = hxc_timecard_summary_pkg.c_normal_mode) then
68 open app_period_info(p_application_period_id);
69 fetch app_period_info into l_resource_id, l_start_time, l_stop_time,l_time_recipient_id;
70 if (app_period_info%FOUND) then
71 for app_rec in app_periods_to_remove(l_resource_id,l_start_time,l_stop_time,l_time_recipient_id) loop
72 delete_app_period(app_rec.application_period_id);
73 end loop;
74 else
75 -- we can do nothing but delete the current app period
76 delete_app_period(p_application_period_id);
77 end if;
78 close app_period_info;
79 else
80 delete_app_period(p_application_period_id);
81 end if;
82
83 End app_period_clean_up;
84
85 procedure app_period_create
86 (p_application_period_id in hxc_app_period_summary.application_period_id%type
87 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
88 ) is
89
90 Begin
91 --
92 -- 1. Clean up current application period data
93 --
94 app_period_clean_up(p_application_period_id,p_mode);
95 --
96 -- 2. Create the application period summary row
97 --
98 hxc_app_period_summary_pkg.insert_summary_row
99 (p_app_period_id => p_application_period_id
100 ,p_approval_item_type => NULL
101 ,p_approval_process_name => NULL
102 ,p_approval_item_key => NULL
103 );
104 --
105 -- 3. Create the link between the application periods
106 -- and the timecards
107 --
108 hxc_tc_ap_links_pkg.create_app_period_links
109 (p_application_period_id => p_application_period_id);
110 --
111 -- 4. Links between details and application
112 -- periods made at another time.
113 --
114 hxc_ap_detail_links_pkg.create_ap_detail_links
115 (p_application_period_id => p_application_period_id);
116 --
117 -- 5. Reevaluate the timecard status
118 -- Not required on migration - status are
119 -- found as the migrated rows are created.
120 if(p_mode = hxc_timecard_summary_pkg.c_normal_mode) then
121 hxc_timecard_summary_api.reevaluate_timecard_statuses
122 (p_application_period_id => p_application_period_id);
123 end if;
124
125 --
126 -- End create application period
127 --
128 End app_period_create;
129
130 procedure app_period_create
131 (p_application_period_id in hxc_app_period_summary.application_period_id%type
132 ,p_application_period_ovn in hxc_app_period_summary.application_period_ovn%type
133 ,p_approval_status in hxc_app_period_summary.approval_status%type
134 ,p_time_recipient_id in hxc_app_period_summary.time_recipient_id%type
135 ,p_time_category_id in hxc_app_period_summary.time_category_id%type
136 ,p_start_time in hxc_app_period_summary.start_time%type
137 ,p_stop_time in hxc_app_period_summary.stop_time%type
138 ,p_resource_id in hxc_app_period_summary.resource_id%type
139 ,p_recipient_sequence in hxc_app_period_summary.recipient_sequence%type
140 ,p_category_sequence in hxc_app_period_summary.category_sequence%type
141 ,p_creation_date in hxc_app_period_summary.creation_date%type
142 ,p_notification_status in hxc_app_period_summary.notification_status%type
143 ,p_approver_id in hxc_app_period_summary.approver_id%type
144 ,p_approval_comp_id in hxc_app_period_summary.approval_comp_id%type
145 ,p_approval_item_key in hxc_app_period_summary.approval_item_key%type default null
146 ) is
147
148 cursor c_get_data_set_id(p_application_period_id number, p_application_period_ovn number) is
149 select data_set_id from hxc_time_building_blocks
150 where scope = 'APPLICATION_PERIOD'
151 and time_building_block_id = p_application_period_id
152 and object_version_number = p_application_period_ovn;
153
154 l_data_set_id hxc_time_building_blocks.data_set_id%TYPE;
155
156 Begin
157 --
158 -- 1. Clean up current application period data
159 --
160 delete_app_period(p_application_period_id);
161 --
162 -- 2. Create the application period summary row
163 --
164 open c_get_data_set_id(p_application_period_id, p_application_period_ovn);
165 fetch c_get_data_set_id into l_data_set_id;
166 close c_get_data_set_id;
167
168 hxc_app_period_summary_pkg.insert_summary_row
169 (p_application_period_id => p_application_period_id
170 ,p_application_period_ovn=> p_application_period_ovn
171 ,p_approval_status => p_approval_status
172 ,p_time_recipient_id => p_time_recipient_id
173 ,p_time_category_id => p_time_category_id
174 ,p_start_time => p_start_time
175 ,p_stop_time => p_stop_time
176 ,p_resource_id => p_resource_id
177 ,p_recipient_sequence => p_recipient_sequence
178 ,p_category_sequence => p_category_sequence
179 ,p_creation_date => p_creation_date
180 ,p_notification_status => p_notification_status
181 ,p_approver_id => p_approver_id
182 ,p_approval_comp_id => p_approval_comp_id
183 ,p_approval_item_type => NULL
184 ,p_approval_process_name => NULL
185 ,p_approval_item_key => p_approval_item_key
186 ,p_data_set_id => l_data_set_id
187 );
188 --
189 -- 3. Create the link between the application periods
190 -- and the timecards
191 --
192 hxc_tc_ap_links_pkg.create_app_period_links
193 (p_application_period_id => p_application_period_id);
194 --
195 -- 4. Links between details and application
196 -- periods made at another time.
197 --
198 --
199 -- 5. Reevaluate the timecard status
200 --
201 hxc_timecard_summary_api.reevaluate_timecard_statuses
202 (p_application_period_id => p_application_period_id);
203
204 --
205 -- End create application period
206 --
207 End app_period_create;
208
209 Procedure app_period_delete
210 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
211
212 Begin
213
214 delete_app_period(p_application_period_id);
215
216 End app_period_delete;
217
218
219 --below for Garry's retrieval
220 FUNCTION valid_status(
221 p_status IN hxc_time_building_blocks.approval_status%TYPE
222 ,p_block_status IN hxc_time_building_blocks.approval_status%TYPE
223 )
224 RETURN BOOLEAN
225 IS
226 BEGIN
227 IF p_status = 'APPROVED'
228 THEN
229 IF p_block_status = 'APPROVED'
230 THEN
231 RETURN TRUE;
232 ELSE
233 RETURN FALSE;
234 END IF;
235 ELSIF p_status = 'SUBMITTED' OR p_status = 'WORKING'
236 THEN
237 IF p_block_status = 'APPROVED'
238 OR p_block_status = 'SUBMITTED'
239 THEN
240 RETURN TRUE;
241 ELSE
242 RETURN FALSE;
243 END IF;
244
245 END IF;
246 END valid_status;
247
248 PROCEDURE add_old_period(
249 p_valid_periods IN OUT NOCOPY valid_period_tab
250 ,p_start_date IN DATE
251 ,p_stop_date IN DATE
252 )
253 IS
254 l_index NUMBER;
255 BEGIN
256 l_index := NVL(p_valid_periods.last, 0);
257
258 IF l_index <> 0
259 AND TRUNC(p_start_date) - TRUNC(p_valid_periods(l_index).stop_time) = 1
260 THEN
261 p_valid_periods(l_index).stop_time := p_stop_date;
262
263 RETURN;
264 END IF;
265
266 l_index := l_index + 1;
267 p_valid_periods(l_index).start_time := p_start_date;
268 p_valid_periods(l_index).stop_time := p_stop_date;
269
270 END add_old_period;
271 --
272 -- This version of add period is used by the new
273 -- version of get_valid_periods, not including
274 -- the work done by Soma.
275 --
276 PROCEDURE add_period(
277 p_valid_periods IN OUT NOCOPY valid_period_tab
278 ,p_start_time IN DATE
279 ,p_stop_time IN DATE
280 )
281 IS
282 l_index NUMBER;
283 BEGIN
284 l_index := to_number(to_char(p_start_time,'YYYYMMDD'));
285 p_valid_periods(l_index).start_time := trunc(p_start_time);
286 p_valid_periods(l_index).stop_time := trunc(p_stop_time);
287
288 END add_period;
289 --
290 -- Added for the 115.7 get_valid_periods rewrite
291 --
292 Function mergePeriods
293 (p_valid_periods in out nocopy valid_period_tab,
294 p_invalid_periods in out nocopy valid_period_tab) return valid_period_tab is
295 l_merged_periods valid_period_tab;
296 l_index number;
297 l_merged_index number;
298 l_last_index number;
299 Begin
300 l_index := p_invalid_periods.first;
301 Loop
302 Exit when not p_invalid_periods.exists(l_index);
303 if(p_valid_periods.exists(l_index)) then
304 p_valid_periods.delete(l_index);
305 end if;
306 l_index := p_invalid_periods.next(l_index);
307 End Loop;
308 l_index := p_valid_periods.first;
309 l_merged_index := 0;
310 Loop
311 Exit when not p_valid_periods.exists(l_index);
312 if(l_merged_periods.count > 0) then
313 l_last_index := l_merged_periods.last;
314 if(p_valid_periods(l_index).start_time > l_merged_periods(l_last_index).stop_time) then
315 if((p_valid_periods(l_index).start_time - l_merged_periods(l_last_index).stop_time) = 1) then
316 l_merged_periods(l_last_index).stop_time := p_valid_periods(l_index).stop_time;
317 else
318 l_merged_index := l_last_index +1;
319 l_merged_periods(l_merged_index).start_time:= p_valid_periods(l_index).start_time;
320 l_merged_periods(l_merged_index).stop_time:= p_valid_periods(l_index).stop_time;
321 end if;
322 end if;
323 else
324 l_merged_index := 1;
325 l_merged_periods(l_merged_index).start_time:= p_valid_periods(l_index).start_time;
326 l_merged_periods(l_merged_index).stop_time:= p_valid_periods(l_index).stop_time;
327 end if;
328 l_index := p_valid_periods.next(l_index);
329 End Loop;
330 return l_merged_periods;
331 End mergePeriods;
332
333 PROCEDURE get_valid_periods(
334 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
335 ,p_time_recipient_id IN hxc_time_recipients.time_recipient_id%TYPE
336 ,p_start_date IN DATE
337 ,p_stop_date IN DATE
338 ,p_valid_status IN VARCHAR2
339 ,p_valid_periods OUT NOCOPY valid_period_tab
340 ) is
341
342 CURSOR c_app_periods(
343 p_resource_id hxc_time_building_blocks.resource_id%TYPE
344 ,p_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE
345 ,p_start_date DATE
346 ,p_stop_date DATE
347 )
348 IS
349 SELECT aps.start_time
350 ,aps.stop_time
351 ,aps.approval_status
352 ,aps.time_category_id
353 FROM hxc_app_period_summary aps
354 WHERE aps.resource_id = p_resource_id
355 AND aps.time_recipient_id = p_time_recipient_id
356 AND aps.start_time <= p_stop_date
357 AND aps.stop_time >= p_start_date
358 and exists
359 (select 1
360 from hxc_tc_ap_links tal
361 where tal.application_period_id = aps.application_period_id
362 )
363 ORDER BY start_time;
364
365 CURSOR c_timecard_periods
366 ( p_resource_id hxc_time_building_blocks.resource_id%TYPE
367 ,p_start_date DATE
368 ,p_stop_date DATE
369 )
370 is
371 SELECT tc.start_time
372 ,tc.stop_time
373 FROM hxc_timecard_summary tc
374 WHERE tc.resource_id = p_resource_id
375 AND tc.start_time <= p_stop_date
376 AND tc.stop_time >= p_start_date
377 AND tc.approval_status in ('SUBMITTED','APPROVED')
378 ORDER BY start_time;
379
380 l_valid BOOLEAN;
381 l_start DATE := NULL;
382 l_stop DATE := NULL;
383 l_app_period_start DATE;
384 l_app_period_stop DATE;
385 l_app_period_status hxc_time_building_blocks.approval_status%TYPE;
386 l_index NUMBER := 0;
387
388 l_ela_used boolean;
389 l_time_category_id number;
390 l_invalid_periods valid_period_tab;
391
392 Begin
393 if p_valid_status = 'SUBMITTED' then
394
395 -- incoming status is 'SUBMITTED''
396 -- we open c_timecard_periods cursor and add every period
397 -- to the valid periods
398
399 OPEN c_timecard_periods(
400 p_resource_id => p_resource_id
401 ,p_start_date => p_start_date
402 ,p_stop_date => p_stop_date
403 );
404
405 LOOP
406 FETCH c_timecard_periods into l_start,l_stop;
407 EXIT WHEN c_timecard_periods%NOTFOUND;
408
409 add_old_period(
410 p_valid_periods => p_valid_periods
411 ,p_start_date => greatest(p_start_date,l_start)
412 ,p_stop_date => least(p_stop_date, l_stop)
413 );
414
415 END LOOP;
416 CLOSE c_timecard_periods;
417 -- finally after adding all periods, we return
418 return;
419 end if;
420
421 OPEN c_app_periods(
422 p_resource_id => p_resource_id
423 ,p_time_recipient_id => p_time_recipient_id
424 ,p_start_date => p_start_date
425 ,p_stop_date => p_stop_date
426 );
427
428 Loop
429 FETCH c_app_periods INTO l_app_period_start, l_app_period_stop, l_app_period_status, l_time_category_id;
430 EXIT WHEN c_app_periods%NOTFOUND;
431
432 if(l_time_category_id is not null) then
433 l_ela_used := true;
434 end if;
435
436 l_valid := valid_status(p_valid_status, l_app_period_status);
437
438 if(l_valid) then
439 add_period(
440 p_valid_periods => p_valid_periods,
441 p_start_time => greatest(p_start_date,l_app_period_start),
442 p_stop_time => least(p_stop_date,l_app_period_stop)
443 ); /* Bug: 5599914 */
444 else
445 add_period(
446 p_valid_periods => l_invalid_periods,
447 p_start_time => greatest(p_start_date,l_app_period_start),
448 p_stop_time => least(p_stop_date,l_app_period_stop)
449 ); /* Bug: 5599914 */
450 end if;
451 End Loop;
452
453 p_valid_periods := mergePeriods(p_valid_periods,l_invalid_periods);
454
455 End get_valid_periods;
456
457 end hxc_app_period_summary_api;