[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_AUDIT
Source
1 PACKAGE BODY hxc_timecard_audit AS
2 /* $Header: hxctcaudit.pkb 120.2 2005/09/23 09:02:16 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 Function find_overall_status
7 (p_transaction_info in hxc_timecard.transaction_info
8 ) return varchar2 is
9
10 l_index NUMBER;
11 l_status varchar2(20);
12
13 Begin
14
15 l_index := p_transaction_info.first;
16
17 LOOP
18 EXIT WHEN
19 (
20 (NOT p_transaction_info.exists(l_index))
21 OR
22 (l_status <> hxc_timecard.c_trans_success)
23 );
24
25 l_status := p_transaction_info(l_index).status;
26
27 l_index := p_transaction_info.next(l_index);
28
29 END LOOP;
30
31 return l_status;
32
33 End find_overall_status;
34
35 Procedure insert_audit_header
36 (p_overall_status in varchar2
37 ,p_transaction_info in hxc_timecard.transaction_info
38 ,p_messages in out nocopy hxc_message_table_type
39 ,p_transaction_id out nocopy hxc_transactions.transaction_id%type
40 ) is
41
42 cursor c_transaction_sequence is
43 select hxc_transactions_s.nextval from dual;
44
45 l_transaction_id hxc_transactions.transaction_id%TYPE;
46 l_deposit_process_id hxc_deposit_processes.deposit_process_id%type;
47 l_data_set_id hxc_transactions.data_set_id%TYPE;
48 l_index BINARY_INTEGER;
49
50 Begin
51
52 select deposit_process_id
53 into l_deposit_process_id
54 from hxc_deposit_processes
55 where name = 'OTL Deposit Process';
56
57 open c_transaction_sequence;
58 fetch c_transaction_sequence into l_transaction_id;
59 close c_transaction_sequence;
60
61 l_index := p_transaction_info.first;
62 if l_index is not null then
63 l_data_set_id := p_transaction_info(l_index).data_set_id;
64 end if;
65
66
67 insert into hxc_transactions
68 (transaction_id
69 ,transaction_date
70 ,type
71 ,transaction_process_id
72 ,created_by
73 ,creation_date
74 ,last_updated_by
75 ,last_update_date
76 ,last_update_login
77 ,status
78 ,data_set_id
79 ) values
80 (l_transaction_id
81 ,sysdate
82 ,'DEPOSIT'
83 ,l_deposit_process_id
84 ,null
85 ,sysdate
86 ,null
87 ,sysdate
88 ,null
89 ,p_overall_status
90 ,l_data_set_id
91 );
92
93 p_transaction_id := l_transaction_id;
94
95 End insert_audit_header;
96
97 Procedure insert_audit_details
98 (p_transaction_info in out nocopy hxc_timecard.transaction_info
99 ,p_messages in out nocopy hxc_message_table_type
100 ,p_transaction_id in hxc_transactions.transaction_id%type
101 ) is
102
103 l_index NUMBER;
104
105 cursor c_transaction_detail_sequence is
106 select hxc_transaction_details_s.nextval from dual;
107
108 l_transaction_detail_id hxc_transaction_details.transaction_detail_id%TYPE;
109
110 Begin
111
112 l_index := p_transaction_info.first;
113
114 Loop
115 EXIT WHEN NOT p_transaction_info.exists(l_index);
116
117 open c_transaction_detail_sequence;
118 fetch c_transaction_detail_sequence into l_transaction_detail_id;
119 close c_transaction_detail_sequence;
120
121 insert into hxc_transaction_details
122 (transaction_detail_id
123 ,time_building_block_id
124 ,transaction_id
125 ,created_by
126 ,creation_date
127 ,last_updated_by
128 ,last_update_date
129 ,last_update_login
130 ,time_building_block_ovn
131 ,status
132 ,exception_description
133 ,data_set_id
134 ) values
135 (l_transaction_detail_id
136 ,p_transaction_info(l_index).time_building_block_id
137 ,p_transaction_id
138 ,null
139 ,sysdate
140 ,null
141 ,sysdate
142 ,null
143 ,p_transaction_info(l_index).object_version_number
144 ,p_transaction_info(l_index).status
145 ,p_transaction_info(l_index).exception_desc
146 ,p_transaction_info(l_index).data_set_id
147 );
148
149 p_transaction_info(l_index).transaction_detail_id := l_transaction_detail_id;
150
151 l_index := p_transaction_info.next(l_index);
152
153 End Loop;
154
155 End insert_audit_details;
156
157 Procedure audit_deposit
158 (p_transaction_info in out nocopy hxc_timecard.transaction_info
159 ,p_messages in out nocopy hxc_message_table_type
160 ) is
161
162 PRAGMA AUTONOMOUS_TRANSACTION;
163
164 l_transaction hxc_transactions.transaction_id%type;
165
166 Begin
167
168 insert_audit_header
169 (find_overall_status(p_transaction_info)
170 ,p_transaction_info
171 ,p_messages
172 ,l_transaction
173 );
174
175 insert_audit_details
176 (p_transaction_info
177 ,p_messages
178 ,l_transaction
179 );
180
181 commit;
182
183 End audit_deposit;
184
185 Procedure maintain_latest_details
186 (p_blocks in hxc_block_table_type
187 ) IS
188
189 l_timecard_blocks hxc_timecard.block_list;
190 l_day_blocks hxc_timecard.block_list;
191 l_detail_blocks hxc_timecard.block_list;
192
193 l_detail_ind PLS_INTEGER;
194
195 l_blk_ind PLS_INTEGER;
196
197 l_status hxc_time_building_blocks.approval_status%TYPE;
198
199 BEGIN
200
201 g_debug := hr_utility.debug_enabled;
202
203 if g_debug then
204 hr_utility.trace('GAZ in maintain details 1');
205 end if;
206
207 hxc_timecard_block_utils.sort_blocks
208 (p_blocks => p_blocks
209 ,p_timecard_blocks => l_timecard_blocks
210 ,p_day_blocks => l_day_blocks
211 ,p_detail_blocks => l_detail_blocks
212 );
213
214 l_detail_ind := l_detail_blocks.FIRST;
215
216 WHILE l_detail_ind IS NOT NULL
217 LOOP
218
219 l_blk_ind := l_detail_blocks(l_detail_ind);
220
221 --if g_debug then
222 -- hr_utility.trace('resource_id is '||to_char(p_blocks(l_blk_ind).resource_id));
223 -- hr_utility.trace('tbb id is '||to_char(p_blocks(l_blk_ind).time_building_Block_id));
224 -- hr_utility.trace('ovn is '||to_char(p_blocks(l_blk_ind).object_version_number));
225 -- hr_utility.trace('process is '||p_blocks(l_blk_ind).process);
226 -- hr_utility.trace('approval is '||p_blocks(l_blk_ind).approval_status);
227 --end if;
228
229 -- Override status for deleted building blocks. These should always be passed to
230 -- recipient applications for processing once deleted.
231
232 IF ( fnd_date.canonical_to_date(( p_blocks(l_blk_ind).date_to )) = hr_general.end_of_time )
233 THEN
234
235 l_status := p_blocks(l_blk_ind).approval_status;
236
237 ELSE
238
239 l_status := 'SUBMITTED';
240
241 END IF;
242
243 IF ( p_blocks(l_blk_ind).object_version_number = 1 AND p_blocks(l_blk_ind).process = 'Y' )
244 THEN
245
246 --if g_debug then
247 -- hr_utility.trace('gaz about to insert '||to_char(p_blocks(l_blk_ind).time_building_Block_id));
248 --end if;
249
250 -- insert row
251
252 INSERT INTO hxc_latest_details (
253 resource_id,
254 time_building_block_id,
255 object_version_number,
256 approval_status,
257 start_time,
258 stop_time,
259 application_set_id,
260 last_update_date,
261 comment_text,
262 resource_type )
263 VALUES (
264 p_blocks(l_blk_ind).resource_id,
265 p_blocks(l_blk_ind).time_building_block_id,
266 p_blocks(l_blk_ind).object_version_number,
267 l_status,
268 hxc_timecard_block_utils.date_value(
269 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time),
270 hxc_timecard_block_utils.date_value(
271 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time),
272 p_blocks(l_blk_ind).application_set_id,
273 sysdate,
274 p_blocks(l_blk_ind).comment_text,
275 p_blocks(l_blk_ind).resource_type );
276
277 ELSIF ( p_blocks(l_blk_ind).object_version_number > 1 and p_blocks(l_blk_ind).process = 'Y' )
278 THEN
279 --if g_debug then
280 -- hr_utility.trace('gaz about to update '||to_char(p_blocks(l_blk_ind).time_building_Block_id));
281 --end if;
282
283 -- update row
284
285 UPDATE hxc_latest_details
286 SET object_version_number = p_blocks(l_blk_ind).object_version_number,
287 approval_status = l_status,
288 application_set_id = p_blocks(l_blk_ind).application_set_id,
289 last_update_date = sysdate,
290 comment_text = p_blocks(l_blk_ind).comment_text,
291 resource_type = p_blocks(l_blk_ind).resource_type
292 WHERE time_building_block_id = p_blocks(l_blk_ind).time_building_block_id;
293
294 IF ( SQL%ROWCOUNT = 0 )
295 THEN
296
297 -- nothing to update, insert
298
299 INSERT INTO hxc_latest_details (
300 resource_id,
301 time_building_block_id,
302 object_version_number,
303 approval_status,
304 start_time,
305 stop_time,
306 application_set_id,
307 last_update_date,
308 comment_text,
309 resource_type )
310 VALUES (
311 p_blocks(l_blk_ind).resource_id,
312 p_blocks(l_blk_ind).time_building_block_id,
313 p_blocks(l_blk_ind).object_version_number,
314 l_status,
315 hxc_timecard_block_utils.date_value(
316 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time),
317 hxc_timecard_block_utils.date_value(
318 p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time),
319 p_blocks(l_blk_ind).application_set_id,
320 sysdate,
321 p_blocks(l_blk_ind).comment_text,
322 p_blocks(l_blk_ind).resource_type );
323
324 END IF;
325
326 END IF;
327
328 l_detail_ind := l_detail_blocks.NEXT(l_detail_ind);
329
330 END LOOP;
331
332 exception when others then
333
334 if g_debug then
335 hr_utility.trace(SQLERRM);
336 end if;
337
338 raise;
339
340 END maintain_latest_details;
341
342
343 End hxc_timecard_audit;