DBA Data[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;