1 package body hxc_ap_detail_links_pkg as
2 /* $Header: hxcadtsum.pkb 120.2 2005/09/23 08:03:11 sechandr noship $ */
3
4 g_debug BOOLEAN:= hr_utility.debug_enabled;
5 TYPE tbb_id_tab IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE INDEX BY BINARY_INTEGER;
6 TYPE app_period_tab IS TABLE OF hxc_tc_ap_links.application_period_id%TYPE INDEX BY BINARY_INTEGER;
7
8 procedure insert_summary_row(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type
9 ,p_time_building_block_id in hxc_time_building_blocks.time_building_block_id%type
10 ,p_time_building_block_ovn in hxc_time_building_blocks.object_version_number%type) is
11
12 begin
13
14 --
15 -- 1. Remove any previous links between an earlier version
16 -- of the detail
17 --
18 delete from hxc_ap_detail_links
19 where time_building_block_id = p_time_building_block_id
20 and application_period_id = p_application_period_id;
21 --
22 -- 2. Insert the new link
23 --
24
25 insert into hxc_ap_detail_links
26 (application_period_id
27 ,time_building_block_id
28 ,time_building_block_ovn
29 )
30 values
31 (p_application_period_id
32 ,p_time_building_block_id
33 ,p_time_building_block_ovn
34 );
35
36 end insert_summary_row;
37
38 procedure delete_summary_row(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type
39 ,p_time_building_block_id in hxc_time_building_blocks.time_building_block_id%type
40 ,p_time_building_block_ovn in hxc_time_building_blocks.object_version_number%type) is
41
42 begin
43
44 delete from hxc_ap_detail_links
45 where application_period_id = p_application_period_id
46 and time_building_block_id = p_time_building_block_id
47 and time_building_block_ovn = p_time_building_block_ovn;
48
49 end delete_summary_row;
50
51 procedure delete_ap_detail_links(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type) is
52
53 begin
54
55 delete from hxc_ap_detail_links where application_period_id = p_application_period_id;
56
57 end delete_ap_detail_links;
58
59
60 PROCEDURE bulk_delete ( p_application_period NUMBER
61 , p_tbb_id_tab tbb_id_tab
62 , p_app_period_tab app_period_tab) IS
63
64 l_proc varchar2(72);
65
66 l_app_period_tab app_period_tab;
67
68 BEGIN
69
70 if g_debug then
71 l_proc := g_package||'bulk_delete';
72 hr_utility.set_location('Entering '||l_proc, 10);
73 end if;
74
75 IF ( p_app_period_tab.COUNT = 0 )
76 THEN
77
78 FOR x IN p_tbb_id_tab.FIRST .. p_tbb_id_tab.LAST
79 LOOP
80
81 l_app_period_tab(x) := p_application_period;
82
83 END LOOP;
84
85 ELSE
86
87 -- for first application period this is already populated
88
89 l_app_period_tab := p_app_period_tab;
90
91 END IF;
92
93 FORALL i IN p_tbb_id_tab.FIRST .. p_tbb_id_tab.LAST
94 DELETE FROM hxc_ap_detail_links adl
95 WHERE adl.application_period_id = l_app_period_tab(i)
96 AND adl.time_building_block_id = p_tbb_id_tab(i);
97
98
99 END bulk_delete;
100
101 procedure delete_ap_detail_links(p_timecard_id in number
102 ,p_blocks in hxc_block_table_type) IS
103
104 CURSOR csr_get_appl_period_id ( p_tbb_id NUMBER ) IS
105 SELECT tal.application_period_id
106 FROM hxc_tc_ap_links tal
107 WHERE tal.timecard_id = p_tbb_id;
108
109 l_index PLS_INTEGER;
110 l_contiguous_index PLS_INTEGER;
111
112 l_application_period_id hxc_tc_ap_links.application_period_id%TYPE;
113
114 t_tbb_id tbb_id_tab;
115 t_app_period app_period_tab;
116
117 l_proc varchar2(72);
118
119 BEGIN
120 g_debug:=hr_utility.debug_enabled;
121
122 if g_debug then
123 l_proc := g_package||'delete_ap_detail_links';
124 hr_utility.set_location('Entering '||l_proc, 10);
125 end if;
126
127 IF ( p_blocks.COUNT <> 0 )
128 THEN
129
130 OPEN csr_get_appl_period_id ( p_timecard_id );
131 FETCH csr_get_appl_period_id INTO l_application_period_id;
132
133 IF csr_get_appl_period_id%FOUND
134 THEN
135
136 -- now populate the bb id and bb ovn arrays in preparation for the bulk delete
137 -- note we also populate the t_app_period array even though it can change.
138 -- so we must also delete it if there is more than one application period
139 -- for a timecard so we know to populate it again in the bulk_delete
140 -- procedure (the bb and ovn arrays being static)
141
142 l_index := p_blocks.FIRST;
143 l_contiguous_index := 1;
144
145 WHILE l_index IS NOT NULL
146 LOOP
147
148 -- we only want to delete detail links for deleted blocks
149 -- with non zero hours otherwise bug 3156317 happens again.
150
151 IF (
152 ( FND_DATE.CANONICAL_TO_DATE( p_blocks(l_index).date_to) <> hr_general.end_of_time )
153 AND
154 (
155 ( ( NVL(p_blocks(l_index).measure,0) <> 0 ) AND p_blocks(l_index).type = 'MEASURE' )
156 OR
157 ( p_blocks(l_index).start_time IS NOT NULL )
158 )
159 )
160 THEN
161
162 t_tbb_id (l_contiguous_index) := p_blocks(l_index).time_building_block_id;
163 t_app_period(l_contiguous_index) := l_application_period_id;
164
165 l_contiguous_index := l_contiguous_index + 1;
166
167 END IF;
168
169 l_index := p_blocks.NEXT(l_index);
170
171 END LOOP;
172
173 -- only delete if there are buildng blocks to delete
174
175 IF ( t_tbb_id.COUNT > 0 )
176 THEN
177
178 WHILE csr_get_appl_period_id%FOUND
179 LOOP
180
181 bulk_delete ( l_application_period_id, t_tbb_id, t_app_period );
182
183 FETCH csr_get_appl_period_id INTO l_application_period_id;
184
185 t_app_period.DELETE;
186
187 END LOOP;
188
189 CLOSE csr_get_appl_period_id;
190
191 END IF; -- t_tbb_id.COUNT > 0
192
193 t_tbb_id.DELETE;
194
195 END IF; -- csr_get_appl_period_id%FOUND
196
197 END IF; -- p_blocks.COUNT <> 0
198
199 if g_debug then
200 hr_utility.set_location('Leaving '||l_proc, 20);
201 end if;
202
203 END delete_ap_detail_links;
204
205
206
207 procedure create_ap_detail_links(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type) is
208
209 cursor c_app_period_info
210 (p_application_period_id in hxc_time_building_blocks.time_building_block_id%type) is
211 select resource_id
212 ,start_time
213 ,stop_time
214 from hxc_app_period_summary
215 where application_period_id = p_application_period_id;
216
217 cursor c_detail_info
218 (p_rid in hxc_time_building_blocks.resource_id%type
219 ,p_start_time in hxc_time_building_blocks.start_time%type
220 ,p_stop_time in hxc_time_building_blocks.stop_time%type
221 ) is
222 select details.time_building_block_id
223 ,details.object_version_number
224 from hxc_time_building_blocks details, hxc_time_building_blocks days
225 where days.resource_id = p_rid
226 and trunc(days.stop_time) >= trunc(p_start_time)
227 and trunc(days.start_time) <= trunc(p_stop_time)
228 and days.scope = 'DAY'
229 and days.date_to = hr_general.end_of_time
230 and details.parent_building_block_id = days.time_building_block_id
231 and details.parent_building_block_ovn = days.object_version_number
232 and details.date_to = hr_general.end_of_time
233 and details.scope = 'DETAIL';
234
235 l_resource_id hxc_time_building_blocks.resource_id%type;
236 l_start_time hxc_time_building_blocks.start_time%type;
237 l_stop_time hxc_time_building_blocks.stop_time%type;
238
239
240 begin
241
242 delete_ap_detail_links(p_application_period_id);
243
244 open c_app_period_info(p_application_period_id);
245 fetch c_app_period_info into l_resource_id, l_start_time, l_stop_time;
246 if (c_app_period_info%found) then
247
248 for det_rec in c_detail_info(l_resource_id,l_start_time,l_stop_time) loop
249
250 insert_summary_row(p_application_period_id,det_rec.time_building_block_id,det_rec.object_version_number);
251
252 end loop;
253
254 end if;
255 close c_app_period_info;
256
257 end create_ap_detail_links;
258
259 end hxc_ap_detail_links_pkg;
[Home] [Help]