[Home] [Help]
PACKAGE BODY: APPS.HXT_TIME_CLOCK
Source
1 PACKAGE BODY HXT_TIME_CLOCK AS
2 /* $Header: hxttclk.pkb 120.2 2011/02/08 09:40:41 asrajago ship $ */
3
4 FUNCTION get_time_period(i_payroll_id IN NUMBER,
5 i_date_worked IN DATE,
6 o_time_period OUT NOCOPY NUMBER,
7 o_start_date OUT NOCOPY DATE,
8 o_end_date OUT NOCOPY DATE)RETURN NUMBER;
9 FUNCTION check_for_timecard(i_person_id IN NUMBER,
10 i_time_period_id IN NUMBER,
11 o_timecard_id OUT NOCOPY NUMBER,
12 o_auto_gen_flag OUT NOCOPY VARCHAR2)RETURN NUMBER;
13 FUNCTION create_timecard(i_person_id IN NUMBER,
14 i_assignment_id IN NUMBER,
15 i_payroll_id IN NUMBER,
16 i_time_period_id IN NUMBER,
17 o_timecard_id OUT NOCOPY NUMBER)RETURN NUMBER;
18 FUNCTION create_batch( i_source IN VARCHAR2,
19 i_payroll_id IN NUMBER,
20 i_time_period_id IN NUMBER,
21 i_assignment_id IN NUMBER,
22 i_person_id IN NUMBER,
23 o_batch_id OUT NOCOPY NUMBER) RETURN NUMBER;
24 --BEGIN SIR343
25 --FUNCTION find_existing_batch(o_batch_id OUT NUMBER)RETURN NUMBER;
26 FUNCTION find_existing_batch(i_payroll_id IN NUMBER,
27 i_time_period_id IN NUMBER,
28 o_batch_id OUT NOCOPY NUMBER)RETURN NUMBER;
29 --END SIR343
30 FUNCTION create_holiday_hours( i_person_id IN NUMBER,
31 i_hcl_id IN NUMBER,
32 i_hxt_rotation_plan IN NUMBER, --SIR344
33 i_start_date IN DATE,
34 i_end_date IN DATE,
35 i_timecard_id IN NUMBER,
36 i_assignment_id IN NUMBER)RETURN NUMBER;
37 FUNCTION record_hours_worked( b_generate_holiday IN BOOLEAN,
38 i_timecard_id IN NUMBER,
39 i_assignment_id IN NUMBER,
40 i_person_id IN NUMBER,
41 i_date_worked IN DATE,
42 i_element_id IN NUMBER,
43 i_hours IN NUMBER,
44 i_start_time IN DATE,
45 i_end_time IN DATE,
46 i_start_date IN DATE)RETURN NUMBER;
47 PROCEDURE record_time( employee_number IN VARCHAR2,
48 assignment_id IN NUMBER,
49 start_time IN DATE,
50 end_time IN DATE,
51 ret_code OUT NOCOPY NUMBER,
52 err_buf OUT NOCOPY VARCHAR2)IS
53 l_person_id per_people_f.person_id%TYPE DEFAULT NULL;
54 l_last_name per_people_f.last_name%TYPE DEFAULT NULL;
55 l_first_name per_people_f.first_name%TYPE DEFAULT NULL;
56 l_bus_grp_id per_assignments_f.business_group_id%TYPE DEFAULT NULL;
57 l_timecard_id hxt_timecards.id%TYPE DEFAULT NULL;
58 l_date_worked DATE DEFAULT NULL;
59 l_time_period_id per_time_periods.time_period_id%TYPE DEFAULT NULL;
60 l_start_date DATE DEFAULT NULL;
61 l_end_date DATE DEFAULT NULL;
62 l_auto_gen_flag hxt_timecards.auto_gen_flag%TYPE DEFAULT NULL;
63 l_timecard_exists BOOLEAN DEFAULT TRUE;
64 l_hours NUMBER DEFAULT NULL;
65 l_person_id_data_err EXCEPTION;
66 l_person_id_sys_err EXCEPTION;
67 l_assign_id_data_err EXCEPTION;
68 l_assign_id_sys_err EXCEPTION;
69 l_pay_date_data_err EXCEPTION;
70 l_pay_date_sys_err EXCEPTION;
71 l_time_per_data_err EXCEPTION;
72 l_time_per_sys_err EXCEPTION;
73 l_istimecard_sys_err EXCEPTION;
74 l_make_card_data_err EXCEPTION;
75 l_make_card_sys_err EXCEPTION;
76 l_make_hol_data_err EXCEPTION;
77 l_make_hol_sys_err EXCEPTION;
78 l_autogen_error EXCEPTION;
79 l_rec_hours_data_err EXCEPTION;
80 l_rec_hours_sys_err EXCEPTION;
81 l_retcode NUMBER DEFAULT 0;
82 l_error_text VARCHAR2(240) DEFAULT NULL;
83 l_exists VARCHAR2(1);
84 CURSOR l_employee_cur(c_person_id NUMBER) IS
85 SELECT asm.payroll_id,
86 asm.assignment_id,
87 asm.business_group_id, --GLOBAL
88 asm.effective_start_date,
89 asm.effective_end_date,
90 aeiv.hxt_rotation_plan, --SIR344
91 egp.hcl_id
92 FROM hxt_earning_policies egp,
93 --ORACLE per_assignments_f_dfv asmv,
94 hxt_per_aei_ddf_v aeiv,--ORACLE
95 per_assignment_status_types ast,
96 per_assignments_f asm
97 WHERE c_person_id = asm.person_id
98 AND ast.assignment_status_type_id = asm.assignment_status_type_id
99 AND ast.pay_system_status = 'P' -- Check payroll status
100 --ORACLE AND asmv.row_id = asm.rowid
101 AND aeiv.assignment_id = asm.assignment_id --ORACLE
102 AND l_date_worked BETWEEN aeiv.effective_start_date /* GLOBAL */
103 AND aeiv.effective_end_date /* GLOBAL */
104 /*GLOBAL AND aeiv.information_type = 'HXT_ASS_INFO' --ORACLE */
105 --ORACLE AND egp.id(+) = asmv.hxt_earning_policy;
106 AND egp.id(+) = aeiv.hxt_earning_policy; --ORACLE
107 l_emp_rec l_employee_cur%ROWTYPE;
108 --
109 -- Fassadi 25/OCT/00 Get the Bug_id for the get_person_id procedure.
110 --
111 Cursor l_bus_grp_cur ( p_asg_id NUMBER ) is
112 select business_group_id
113 from per_assignments_f asm
114 where asm.assignment_id = p_asg_id;
115
116 --this to fix bug 756293
117 --
118 cursor C_session_exists is
119 select 'Y'
120 from fnd_sessions
121 where session_id = userenv('sessionid')
122 and trunc(effective_date) = trunc(sysdate);
123 --
124 --
125 BEGIN
126 /*Obtain date worked from user exit*/
127
128 -- Before anything insert a row in fdn_sessions.
129 -- fix for bug 756293
130 open C_session_exists;
131 fetch C_session_exists into l_exists;
132 if C_session_exists%notfound then
133 insert into fnd_sessions
134 (effective_date, session_id)
135 values (sysdate, userenv('sessionid'));
136 HXT_UTIL.DEBUG('Inserted a row in fnd session '); --HXT115
137 end if;
138 close C_session_exists;
139 --
140 -- End of fix for bug 756293
141
142 --
143 l_retcode := hxt_clock_user_edits.determine_pay_date( start_time,
144 end_time,
145 l_person_id,
146 l_date_worked);
147 HXT_UTIL.DEBUG('determine_pay_date returns: '|| fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt));--DEBUG ONLY --FORMS60 --HXT115
148 IF l_retcode = 1 THEN
149 RAISE l_pay_date_data_err;
150 ELSIF l_retcode = 2 THEN
151 RAISE l_pay_date_sys_err;
152 END IF;
153 --
154 -- Get the bus_grp_id to pass it to the get_person_id procedure.
155 --
156 BEGIN
157 OPEN l_bus_grp_cur ( assignment_id );
158 FETCH l_bus_grp_cur INTO l_bus_grp_id;
159 CLOSE l_bus_grp_cur;
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 l_retcode := 1;
163 RAISE l_assign_id_data_err;
164 WHEN OTHERS THEN
165 l_retcode := 2;
166 RAISE l_assign_id_sys_err;
167 END;
168 --
169 /* Obtain person id from user exit */
170 l_retcode := hxt_clock_user_edits.get_person_id(employee_number,
171 l_bus_grp_id,
172 l_date_worked,
173 l_person_id,
174 l_last_name,
175 l_first_name);
176 HXT_UTIL.DEBUG('get_person_id returns: '|| TO_CHAR(l_retcode));--DEBUG ONLY --HXT115
177 IF l_retcode = 1 THEN
178 RAISE l_person_id_data_err;
179 ELSIF l_retcode = 2 THEN
180 RAISE l_person_id_sys_err;
181 END IF;
182 --
183 /* Obtain vital employee information*/
184 BEGIN
185 HXT_UTIL.DEBUG('Opening Cursor');--DEBUG ONLY --HXT115
186 OPEN l_employee_cur(l_person_id);
187 HXT_UTIL.DEBUG('Fetching Cursor');--DEBUG ONLY --HXT115
188 FETCH l_employee_cur
189 INTO l_emp_rec;
190 HXT_UTIL.DEBUG('Closing Cursor');--DEBUG ONLY --HXT115
191 CLOSE l_employee_cur;
192 g_bus_group_id := l_emp_rec.business_group_id; --GLOBAL
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 l_retcode := 1;
196 RAISE l_assign_id_data_err;
197 WHEN OTHERS THEN
198 l_retcode := 2;
199 RAISE l_assign_id_sys_err;
200 END;
201 --DEBUG ONLY BEGIN
202 HXT_UTIL.DEBUG('Payroll id : '||TO_CHAR(l_emp_rec.payroll_id)); --HXT115
203 HXT_UTIL.DEBUG('Assignment id: '||TO_CHAR(l_emp_rec.assignment_id)); --HXT115
204 HXT_UTIL.DEBUG('Business Group id: '||TO_CHAR(l_emp_rec.business_group_id)); --GLOBAL
205 HXT_UTIL.DEBUG('Effective Start Date: '||fnd_date.date_to_chardate(l_emp_rec.effective_start_date)); --FORMS60 --HXT115
206 HXT_UTIL.DEBUG('Effective End Date: '||fnd_date.date_to_chardate(l_emp_rec.effective_end_date)); --FORMS60 --HXT115
207 HXT_UTIL.DEBUG('hcl_id: '||TO_CHAR(l_emp_rec.hcl_id)); --HXT115
208 HXT_UTIL.DEBUG('hxt_rotation_plan: '||l_emp_rec.hxt_rotation_plan); --SIR344 --HXT115
209 --DEBUG ONLY END
210 --
211 /*Obtain the current time period id for this payroll and date*/
212 l_retcode := get_time_period( l_emp_rec.payroll_id,
213 l_date_worked,
214 l_time_period_id,
215 l_start_date,
216 l_end_date);
217 HXT_UTIL.DEBUG('get_time_period returns period: '|| --HXT115
218 TO_CHAR(l_time_period_id)|| ' start date: '||
219 fnd_date.date_to_chardate(l_start_date)|| ' end date: '|| -- FORMS60
220 fnd_date.date_to_chardate(l_end_date));--DEBUG ONLY --FORMS60
221 IF l_retcode = 1 THEN
222 RAISE l_time_per_data_err;
223 ELSIF l_retcode = 2 THEN
224 RAISE l_time_per_data_err;
225 END IF;
226 g_time_period_err_id := l_time_period_id;
227 --
228 /*Determine effective start date*/
229 IF l_emp_rec.effective_start_date > l_start_date THEN
230 l_start_date := l_emp_rec.effective_start_date;
231 END IF;
232 IF l_emp_rec.effective_end_date < l_end_date THEN
233 l_end_date := l_emp_rec.effective_end_date;
234 END IF;
235 --
236 /*Check for an existing timecard */
237 l_retcode := check_for_timecard(l_person_id, l_time_period_id, l_timecard_id, l_auto_gen_flag);
238 HXT_UTIL.DEBUG('check_for_timecard returns timecard id: '|| TO_CHAR(l_timecard_id));--DEBUG ONLY --HXT115
239 --
240 IF l_retcode = 0 THEN
241 g_timecard_err_id := l_timecard_id;
242 ELSIF l_retcode = 1 THEN
243 /*Create a timecard when none exists*/
244 l_retcode := create_timecard(l_person_id,
245 l_emp_rec.assignment_id,
246 l_emp_rec.payroll_id,
247 l_time_period_id,
248 l_timecard_id);
249 IF l_retcode = 1 THEN
250 RAISE l_make_card_data_err;
251 ELSIF l_retcode = 2 THEN
252 RAISE l_make_card_sys_err;
253 END IF;
254 g_timecard_err_id := l_timecard_id;
255 /*Create holiday hours on the new timecard*/
256 HXT_UTIL.DEBUG('Now generating holidays'); --HXT115
257 l_retcode := create_holiday_hours(l_person_id,
258 l_emp_rec.hcl_id,
259 l_emp_rec.hxt_rotation_plan, --SIR344
260 l_start_date,
261 l_end_date,
262 l_timecard_id,
263 l_emp_rec.assignment_id);
264 IF l_retcode = 1 THEN
265 RAISE l_make_hol_data_err;
266 ELSIF l_retcode = 2 THEN
267 RAISE l_make_hol_sys_err;
268 END IF;
269 ELSIF l_retcode = 2 THEN
270 RAISE l_istimecard_sys_err;
271 END IF;
272 HXT_UTIL.DEBUG('Hours will be charged to Timecard id: '||TO_CHAR(l_timecard_id));--DEBUG ONLY --HXT115
273 --
274 /*Check to see if pre-existing timecards were autogened*/
275 IF l_auto_gen_flag = 'A' THEN
276 RAISE l_autogen_error;
277 END IF;
278 --
279 /*Calculate the hours worked*/
280 l_hours := 24 * (TRUNC(end_time,'MI') - TRUNC(start_time,'MI'));
281 HXT_UTIL.DEBUG('Hours worked: '||TO_CHAR(l_hours));--DEBUG ONLY --HXT115
282 /*Insert hours to the hxt_hours_worked table and call generate details*/
283 l_retcode := record_hours_worked( FALSE,
284 l_timecard_id,
285 l_emp_rec.assignment_id,
286 l_person_id,
287 l_date_worked,
288 NULL,
289 l_hours,
290 start_time,
291 end_time,
292 l_start_date);
293 IF l_retcode = 1 THEN
294 RAISE l_rec_hours_data_err;
295 ELSIF l_retcode = 2 THEN
296 RAISE l_rec_hours_sys_err;
297 END IF;
298 ret_code := 0;
299 --HXT11err_buf := 'Successful Completion. Time has posted.';
300 FND_MESSAGE.SET_NAME('HXT','HXT_39400_TIME_POSTED'); -- HXT11
301 err_buf := FND_MESSAGE.GET; -- HXT11
302 FND_MESSAGE.CLEAR; -- HXT11
303
304 RETURN;
305 EXCEPTION
306 WHEN l_person_id_data_err THEN
307 ret_code := l_retcode;
308 --HXT11err_buf := 'Data Error: Query Error Form for ACLOCK';
309 FND_MESSAGE.SET_NAME('HXT','HXT_39401_QRY_ERR_DATA'); -- HXT11
310 err_buf := FND_MESSAGE.GET; -- HXT11
311 FND_MESSAGE.CLEAR; -- HXT11
312
313 --HXT11l_error_text := 'No Employee was located for Employee Number ' || employee_number;
314 FND_MESSAGE.SET_NAME('HXT','HXT_39308_EMPLYEE_NF'); -- HXT11
315 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
316 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', NULL);
317 RETURN;
318
319 WHEN l_person_id_sys_err THEN
320 ret_code := l_retcode;
321 --HXT11err_buf := 'Sys. Error: Query Error Form for ACLOCK';
322 FND_MESSAGE.SET_NAME('HXT','HXT_39402_QRY_ERR_SYS'); -- HXT11
323 err_buf := FND_MESSAGE.GET; -- HXT11
324 FND_MESSAGE.CLEAR; -- HXT11
325
326 --HXT11l_error_text := 'System Error occurred while attempting to access Employee Data.' ||
327 --HXT11'Employee Number ' || employee_number ||
328 --HXT11'Function hxt_clock_user_edits.get_person_id';
329 FND_MESSAGE.SET_NAME('HXT','HXT_39308_EMPLYEE_NF'); -- HXT11
330 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
331 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', SQLERRM);
332 RETURN;
333
334 WHEN l_assign_id_data_err THEN
335 ret_code := l_retcode;
336 --HXT11err_buf := 'Data Error: Query Error Form for ACLOCK';
337 FND_MESSAGE.SET_NAME('HXT','HXT_39401_QRY_ERR_DATA'); -- HXT11
338 err_buf := FND_MESSAGE.GET; -- HXT11
339 FND_MESSAGE.CLEAR; -- HXT11
340
341 --HXT11l_error_text := 'No Assignment was located for '||l_first_name||' '||l_last_name||
342 --HXT11 '(Employee Number: '|| employee_number ||').';
343 FND_MESSAGE.SET_NAME('HXT','HXT_39306_ASSIGN_NF'); -- HXT11
344 FND_MESSAGE.SET_TOKEN('FIRST_NAME', l_first_name); --HXT11
345 FND_MESSAGE.SET_TOKEN('LAST_NAME', l_last_name); --HXT11
346 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
347 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', NULL);
348 RETURN;
349
350 WHEN l_assign_id_sys_err THEN
351 ret_code := l_retcode;
352 --HXT11err_buf := 'Sys. Error: Query Error Form for ACLOCK';
353 FND_MESSAGE.SET_NAME('HXT','HXT_39402_QRY_ERR_SYS'); -- HXT11
354 err_buf := FND_MESSAGE.GET; -- HXT11
355 FND_MESSAGE.CLEAR; -- HXT11
356
357 --HXT11l_error_text := 'System Error occurred atempting to Fetch Assignment.';
358 FND_MESSAGE.SET_NAME('HXT','HXT_39319_ERR_GET_ASSIGN'); -- HXT11
359 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', SQLERRM);
360 RETURN;
361
362 WHEN l_pay_date_data_err THEN
363 ret_code := l_retcode;
364 --HXT11err_buf := 'Data Error: Query Error Form for ACLOCK';
365 FND_MESSAGE.SET_NAME('HXT','HXT_39401_QRY_ERR_DATA'); -- HXT11
366 err_buf := FND_MESSAGE.GET; -- HXT11
367 FND_MESSAGE.CLEAR; -- HXT11
368
369 --HXT11l_error_text := 'Unable to determine date worked from user exit. Start time: ' || TO_CHAR(start_time);
370 FND_MESSAGE.SET_NAME('HXT','HXT_39331_CANT_CALC_DAT_WRKED'); -- HXT11
371 FND_MESSAGE.SET_TOKEN('START_TIME', TO_CHAR(start_time)); --HXT11
372 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', NULL);
373 RETURN;
374
375 WHEN l_pay_date_sys_err THEN
376 ret_code := l_retcode;
377 --HXT11err_buf := 'Sys. Error: Query Error Form for ACLOCK';
378 FND_MESSAGE.SET_NAME('HXT','HXT_39402_QRY_ERR_SYS'); -- HXT11
379 err_buf := FND_MESSAGE.GET; -- HXT11
380 FND_MESSAGE.CLEAR; -- HXT11
381
382 --HXT11l_error_text := 'System error occurred while attempting to determine date worked.';
383 FND_MESSAGE.SET_NAME('HXT','HXT_39323_ERR_DATE_WRKED'); -- HXT11
384 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', SQLERRM);
385 RETURN;
386
387 WHEN l_time_per_data_err THEN
388 ret_code := l_retcode;
389 --HXT11err_buf := 'Data Error: Query Error Form for ACLOCK';
390 FND_MESSAGE.SET_NAME('HXT','HXT_39401_QRY_ERR_DATA'); -- HXT11
391 err_buf := FND_MESSAGE.GET; -- HXT11
392 FND_MESSAGE.CLEAR; -- HXT11
393
394 --HXT11l_error_text := 'Unable to determine a time period for date worked of: '||TO_CHAR(l_date_worked)||
395 --HXT11' Payroll: '|| TO_CHAR(l_emp_rec.payroll_id);
396 FND_MESSAGE.SET_NAME('HXT','HXT_39330_CANT_CALC_TIM_PER'); -- HXT11
397 FND_MESSAGE.SET_TOKEN('DATE_WORKED', fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt)); --HXT11 --FORMS60
398 FND_MESSAGE.SET_TOKEN('PAYROLL', TO_CHAR(l_emp_rec.payroll_id)); --HXT11
399 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', NULL);
400 RETURN;
401
402 WHEN l_time_per_sys_err THEN
403 ret_code := l_retcode;
404 --HXT11err_buf := 'Sys. Error: Query Error Form for ACLOCK';
405 FND_MESSAGE.SET_NAME('HXT','HXT_39402_QRY_ERR_SYS'); -- HXT11
406 err_buf := FND_MESSAGE.GET; -- HXT11
407 FND_MESSAGE.CLEAR; -- HXT11
408
409 --HXT11l_error_text := 'System error occurred while attempting to obtain time period. ' || SQLERRM;
410 FND_MESSAGE.SET_NAME('HXT','HXT_39324_ERR_TIME_PERIOD'); -- HXT11
411 FND_MESSAGE.SET_TOKEN('SQLERR', SQLERRM); --HXT11
412 l_retcode := log_clock_errors(FALSE, NULL,'EMPL', NULL);
413 RETURN;
414
415 WHEN l_istimecard_sys_err THEN
416 ret_code := l_retcode;
417 --HXT11err_buf := 'Sys. Error: Query Error Form for ACLOCK';
418 FND_MESSAGE.SET_NAME('HXT','HXT_39402_QRY_ERR_SYS'); -- HXT11
419 err_buf := FND_MESSAGE.GET; -- HXT11
420 FND_MESSAGE.CLEAR; -- HXT11
421
422 --HXT11l_error_text := 'Error occurred while checking for an existing timecard. ';
423 FND_MESSAGE.SET_NAME('HXT','HXT_39298_ERR_GET_TIMCARD'); -- HXT11
424 l_retcode := log_clock_errors(FALSE, NULL,'TIM', SQLERRM);
425 RETURN;
426
427 WHEN l_autogen_error THEN
428 ret_code := l_retcode;
429 --HXT11err_buf := 'Data Error: Query Error Form for ACLOCK';
430 FND_MESSAGE.SET_NAME('HXT','HXT_39401_QRY_ERR_DATA'); -- HXT11
431 err_buf := FND_MESSAGE.GET; -- HXT11
432 FND_MESSAGE.CLEAR; -- HXT11
433
434 --HXT11l_error_text := 'An autogen timecard exists for '||l_first_name||' '||l_last_name||
435 --HXT11' (Employee: '|| employee_number ||').'||
436 --HXT11' Autogen timecards may not accept time from a timeclock.';
437 FND_MESSAGE.SET_NAME('HXT','HXT_39267_AG_TCARD_EXISTS'); -- HXT11
438 FND_MESSAGE.SET_TOKEN('FIRST_NAME', l_first_name); --HXT11
439 FND_MESSAGE.SET_TOKEN('LAST_NAME', l_last_name); --HXT11
440 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
441 l_retcode := log_clock_errors(TRUE, NULL,'TIM', NULL);
442 RETURN;
443
444 WHEN l_make_card_data_err THEN
445 ret_code := l_retcode;
446 --HXT11err_buf := 'Data Error: Query Error Form for ACLOCK';
447 FND_MESSAGE.SET_NAME('HXT','HXT_39401_QRY_ERR_DATA'); -- HXT11
448 err_buf := FND_MESSAGE.GET; -- HXT11
449 FND_MESSAGE.CLEAR; -- HXT11
450
451 --HXT11l_error_text := 'Data Error during create timecard processing for '||
452 --HXT11l_first_name||' '||l_last_name||' (Employee: '|| employee_number ||').';
453 FND_MESSAGE.SET_NAME('HXT','HXT_39291_CRT_TCARD_ERR'); -- HXT11
454 FND_MESSAGE.SET_TOKEN('FIRST_NAME', l_first_name); --HXT11
455 FND_MESSAGE.SET_TOKEN('LAST_NAME', l_last_name); --HXT11
456 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
457 l_retcode := log_clock_errors(FALSE, NULL,'TIM', NULL);
458 RETURN;
459
460 WHEN l_make_card_sys_err THEN
461 ret_code := l_retcode;
462 --HXT11err_buf := 'Sys. Error: Query Error Form for ACLOCK';
463 FND_MESSAGE.SET_NAME('HXT','HXT_39402_QRY_ERR_SYS'); -- HXT11
464 err_buf := FND_MESSAGE.GET; -- HXT11
465 FND_MESSAGE.CLEAR; -- HXT11
466
467 --HXT11l_error_text := 'System error during create timecard processing for '||
468 --HXT11 l_first_name||' '||l_last_name||' (Employee: '|| employee_number ||').';
469 FND_MESSAGE.SET_NAME('HXT','HXT_39318_ERR_CREAT_TCARD'); -- HXT11
470 FND_MESSAGE.SET_TOKEN('FIRST_NAME', l_first_name); --HXT11
471 FND_MESSAGE.SET_TOKEN('LAST_NAME', l_last_name); --HXT11
472 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
473 l_retcode := log_clock_errors(FALSE, NULL,'TIM', SQLERRM);
474 RETURN;
475
476 WHEN l_make_hol_data_err THEN
477 ret_code := l_retcode;
478 --HXT11err_buf := 'Data Error: Query timecard for'|| TO_CHAR(l_date_worked); --SIR149
479 FND_MESSAGE.SET_NAME('HXT','HXT_39404_QRY_DAT_TOK'); -- HXT11
480 FND_MESSAGE.SET_TOKEN('1', fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt)); --HXT11 --SIR149 --FORMS60
481 err_buf := FND_MESSAGE.GET; -- HXT11
482 FND_MESSAGE.CLEAR; -- HXT11
483
484 --HXT11l_error_text := 'Data error occurred during create holiday hours processing for '||
485 --HXT11 l_first_name||' '||l_last_name||' (Employee: '|| employee_number ||').';
486 FND_MESSAGE.SET_NAME('HXT','HXT_39292_CRT_HOL_HRS'); -- HXT11
487 FND_MESSAGE.SET_TOKEN('FIRST_NAME', l_first_name); --HXT11
488 FND_MESSAGE.SET_TOKEN('LAST_NAME', l_last_name); --HXT11
489 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
490 l_retcode := log_clock_errors(TRUE, NULL,'HOL', NULL);
491 RETURN;
492
493 WHEN l_make_hol_sys_err THEN
494 ret_code := l_retcode;
495 --HXT11err_buf := 'Sys. Error: Query timecard for'|| TO_CHAR(l_date_worked); --SIR149
496 FND_MESSAGE.SET_NAME('HXT','HXT_39405_QRY_SYS_TOK'); -- HXT11
497 FND_MESSAGE.SET_TOKEN('1', fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt)); --HXT11 --SIR149 --FORMS60
498 err_buf := FND_MESSAGE.GET; -- HXT11
499 FND_MESSAGE.CLEAR; -- HXT11
500
501 --HXT11l_error_text := 'System error occurred during create holiday hours processing for '||
502 --HXT11 l_first_name||' '||l_last_name||' (Employee: '|| employee_number ||').';
503 FND_MESSAGE.SET_NAME('HXT','HXT_39320_ERR_CREAT_HOL'); -- HXT11
504 FND_MESSAGE.SET_TOKEN('FIRST_NAME', l_first_name); --HXT11
505 FND_MESSAGE.SET_TOKEN('LAST_NAME', l_last_name); --HXT11
506 FND_MESSAGE.SET_TOKEN('EMP_NUMBER', employee_number); --HXT11
507 l_retcode := log_clock_errors(TRUE, NULL,'HOL', SQLERRM);
508 RETURN;
509
510 WHEN l_rec_hours_data_err THEN
511 ret_code := l_retcode;
512 --HXT11err_buf := 'Data Error: Query timecard for'|| TO_CHAR(l_date_worked); --SIR149
513 FND_MESSAGE.SET_NAME('HXT','HXT_39404_QRY_DAT_TOK'); -- HXT11
514 FND_MESSAGE.SET_TOKEN('1', fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt)); --HXT11 --SIR149 --FORMS60
515 err_buf := FND_MESSAGE.GET; -- HXT11
516 FND_MESSAGE.CLEAR; -- HXT11
517
518 --HXT11l_error_text := 'Data Error occurred in the record hours functionality';
519 FND_MESSAGE.SET_NAME('HXT','HXT_39293_REC_HRS_ERR'); -- HXT11
520 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', NULL);
521 RETURN;
522
523 WHEN l_rec_hours_sys_err THEN
524 ret_code := l_retcode;
525 --HXT11err_buf := 'Sys. Error: Query timecard for'|| TO_CHAR(l_date_worked); --SIR149
526 FND_MESSAGE.SET_NAME('HXT','HXT_39405_QRY_SYS_TOK'); -- HXT11
527 FND_MESSAGE.SET_TOKEN('1', fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt)); --HXT11 --SIR149 --FORMS60
528 err_buf := FND_MESSAGE.GET; -- HXT11
529 FND_MESSAGE.CLEAR; -- HXT11
530
531 --HXT11l_error_text := 'System error occurred in the record hours functionality';
532 FND_MESSAGE.SET_NAME('HXT','HXT_39321_ERR_REC_HRS'); -- HXT11
533 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', SQLERRM);
534 RETURN;
535
536 WHEN g_error_log_error THEN
537 --HXT11err_buf := 'Error in hxt_time_clock.log_clock_errors';
538 FND_MESSAGE.SET_NAME('HXT','HXT_39403_LOG_ERR_ERR'); -- HXT11
539 err_buf := FND_MESSAGE.GET; -- HXT11
540 FND_MESSAGE.CLEAR; -- HXT11
541
542 HXT_UTIL.DEBUG('Error in hxt_time_clock.log_clock_errors'); --DEBUG ONLY --HXT115
543 ret_code := 2;
544 RETURN;
545
546 WHEN OTHERS THEN
547 --HXT11err_buf := 'Sys. Error: Query timecard for'|| TO_CHAR(l_date_worked); --SIR149
548 FND_MESSAGE.SET_NAME('HXT','HXT_39405_QRY_SYS_TOK'); -- HXT11
549 FND_MESSAGE.SET_TOKEN('1', fnd_date.date_to_chardate(dateval=>l_date_worked,/*Bug 10621762*/calendar_aware=>fnd_date.calendar_aware_alt)); --HXT11 --SIR149 --FORMS60
550 err_buf := FND_MESSAGE.GET; -- HXT11
551 FND_MESSAGE.CLEAR; -- HXT11
552
553 ret_code := 2;
554 FND_MESSAGE.SET_NAME('HXT','HXT_39406_EXCP_REC_TIME'); -- HXT11
555 l_retcode := log_clock_errors(FALSE, NULL,'TIMEXCP', SQLERRM);
556 RETURN;
557 END record_time;
558 /***********************************
559 get_time_period()
560 Obtain the time period identifier
561 for this particular pay date
562 ************************************/
563 FUNCTION get_time_period(i_payroll_id IN NUMBER,
564 i_date_worked IN DATE,
565 o_time_period OUT NOCOPY NUMBER,
566 o_start_date OUT NOCOPY DATE,
567 o_end_date OUT NOCOPY DATE)RETURN NUMBER IS
568 BEGIN
569 SELECT time_period_id,
570 start_date,
571 end_date
572 INTO o_time_period,
573 o_start_date,
574 o_end_date
575 FROM per_time_periods
576 WHERE payroll_id = i_payroll_id
577 AND TRUNC(i_date_worked) BETWEEN TRUNC(start_date) AND TRUNC(end_date);
578 RETURN 0;
579 EXCEPTION
580 WHEN NO_DATA_FOUND THEN
581 RETURN 1;
582 WHEN OTHERS THEN
583 RETURN 2;
584 END get_time_period;
585 /****************************************************
586 check_for_timecard()
587 Check the HXT_TIMECARDS table to see if a timecard
588 already exists for the person punching the clock
589 ****************************************************/
590 FUNCTION check_for_timecard( i_person_id IN NUMBER,
591 i_time_period_id IN NUMBER,
592 o_timecard_id OUT NOCOPY NUMBER,
593 o_auto_gen_flag OUT NOCOPY VARCHAR2)RETURN NUMBER IS
594 BEGIN
595 SELECT id,
596 auto_gen_flag,
597 batch_id
598 INTO o_timecard_id,
599 o_auto_gen_flag,
600 g_batch_err_id
601 FROM hxt_timecards
602 WHERE for_person_id = i_person_id
603 AND time_period_id = i_time_period_id;
604 RETURN 0;
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 RETURN 1;
608 WHEN OTHERS THEN
609 RETURN 2;
610 END check_for_timecard;
611 /********************************************************
612 create_timecard()
613 Creates a timecard for the person punching the clock
614 for this particular time period based on the payroll
615 for this person.
616 *********************************************************/
617 FUNCTION create_timecard(i_person_id IN NUMBER,
618 i_assignment_id IN NUMBER,
619 i_payroll_id IN NUMBER,
620 i_time_period_id IN NUMBER,
621 o_timecard_id OUT NOCOPY NUMBER)RETURN NUMBER IS
622 l_retcode NUMBER DEFAULT 0;
623 l_batch_creation_error EXCEPTION;
624 l_batch_location_error EXCEPTION;
625 l_tim_id_creation_error EXCEPTION;
626 --BEGIN GLOBAL
627 -- l_batch_id pay_pdt_batch_headers.batch_id%TYPE DEFAULT NULL;
628 l_batch_id pay_batch_headers.batch_id%TYPE DEFAULT NULL;
629 --END GLOBAL
630 l_timecard_id hxt_timecards.id%TYPE DEFAULT NULL;
631 BEGIN
632 /* Obtain a batch id for the new timecard */
633 --BEGIN SIR343
634 -- l_retcode := find_existing_batch(l_batch_id);
635 l_retcode := find_existing_batch(i_payroll_id,
636 i_time_period_id,
637 l_batch_id);
638 --END SIR343
639 /* If Not Found */
640 IF l_retcode = 1 THEN
641 /* Create a batch id for the new timecard */
642 HXT_UTIL.DEBUG('Creating new batch'); --DEBUG ONLY --HXT115
643 l_retcode := create_batch( 'C', --source is timeclock
644 i_payroll_id,
645 i_time_period_id,
646 i_assignment_id,
647 i_person_id,
648 l_batch_id);
649 IF l_retcode <> 0 THEN
650 RAISE l_batch_creation_error;
651 END IF;
652 ELSIF l_retcode = 2 THEN
653 RAISE l_batch_location_error;
654 END IF;
655 g_batch_err_id := l_batch_id;
656 HXT_UTIL.DEBUG('Batch id is: '||TO_CHAR(l_batch_id));--DEBUG ONLY --HXT115
657 /* Generate a unique timecard id for the new timecard */
658 l_timecard_id := hxt_time_gen.Get_HXT_Seqno;
659 IF l_timecard_id = NULL THEN
660 RAISE l_tim_id_creation_error;
661 END IF;
662 HXT_UTIL.DEBUG('Timecard id is: '||TO_CHAR(l_timecard_id));--DEBUG ONLY --HXT115
663 /* Insert new timecard info to hxt_timecards */
664 --SIR012 INSERT into hxt_timecards
665 INSERT into hxt_timecards_f --SIR012
666 ( id,
667 for_person_id,
668 payroll_id,
669 time_period_id,
670 batch_id,
671 auto_gen_flag,
672 created_by,
673 creation_date
674 , effective_start_date --SIR012
675 , effective_end_date) --SIR012
676 VALUES
677 ( l_timecard_id,
678 i_person_id,
679 i_payroll_id,
680 i_time_period_id,
681 l_batch_id,
682 'T',
683 g_user_id,
684 g_sysdate
685 , g_sysdate --SIR012
686 ,hr_general.end_of_time); --SIR149 --FORMS60
687 COMMIT;
688 o_timecard_id := l_timecard_id;
689 RETURN 0;
690 EXCEPTION
691 WHEN l_batch_creation_error THEN
692 RETURN l_retcode;
693 WHEN l_batch_location_error THEN
694 RETURN l_retcode;
695 WHEN l_tim_id_creation_error THEN
696 FND_MESSAGE.SET_NAME('HXT','HXT_39407_CREATE_TIM'); -- HXT11
697 l_retcode := log_clock_errors(FALSE, NULL,'TIMCRT', NULL);
698 --HXT11l_retcode := log_clock_errors(FALSE, 'Error creating timecard id in hxt_time_gen.Get_HXT_Seqno','Timecard Creation', NULL);
699 RETURN 2;
700 WHEN OTHERS THEN
701 FND_MESSAGE.SET_NAME('HXT','HXT_39408_CREATE_TIM_FUNC'); -- HXT11
702 l_retcode := log_clock_errors(FALSE, NULL,'Timecard Creation', SQLERRM);
703 --HXT11l_retcode := log_clock_errors(FALSE, 'Error in function create_timecard','Timecard Creation', SQLERRM);
704 RETURN 2;
705 END create_timecard;
706 /******************************************************************
707 create_batch()
708 Obtains an existing clock batch id for this particular timecard.
709 If no clock batch id with less than 50 timecards exists.
710 Creates a new batch id for this particular timecard.
711 ******************************************************************/
712 FUNCTION create_batch( i_source IN VARCHAR2,
713 i_payroll_id IN NUMBER,
714 i_time_period_id IN NUMBER,
715 i_assignment_id IN NUMBER,
716 i_person_id IN NUMBER,
717 o_batch_id OUT NOCOPY NUMBER) RETURN NUMBER IS
718 --BEGIN GLOBAL
719 -- l_batch_id pay_pdt_batch_headers.batch_id%TYPE DEFAULT NULL;
720 -- l_reference_num pay_pdt_batch_headers.reference_num%TYPE DEFAULT NULL;
721 l_batch_id pay_batch_headers.batch_id%TYPE DEFAULT NULL;
722 l_reference_num pay_batch_headers.batch_reference%TYPE DEFAULT NULL;
723 l_batch_name pay_batch_headers.batch_name%TYPE DEFAULT NULL;
724 l_batch_name_error EXCEPTION;
725 --END GLOBAL
726 l_error_text VARCHAR2(128) DEFAULT NULL;
727 l_batch_id_error EXCEPTION;
728 l_reference_num_error EXCEPTION;
729 l_retcode NUMBER DEFAULT 0;
730 l_object_version_number pay_batch_headers.object_version_number%TYPE;
731 BEGIN
732 hxt_user_exits.Define_Reference_Number(i_payroll_id,
733 i_time_period_id,
734 i_assignment_id,
735 i_person_id,
736 g_user_name,
737 i_source,
738 l_reference_num,
739 l_error_text);
740 IF l_error_text <> NULL THEN
741 RAISE l_reference_num_error;
742 END IF;
743 -- create a batch first
744 pay_batch_element_entry_api.create_batch_header (
745 p_session_date=> g_sysdate,
746 p_batch_name=> to_char(sysdate, 'DD-MM-RRRR HH24:MI:SS'),
747 p_batch_status=> 'U',
748 p_business_group_id=> g_bus_group_id,
749 p_action_if_exists=> 'I',
750 p_batch_reference=> l_reference_num,
751 p_batch_source=> 'OTM',
752 p_purge_after_transfer=> 'N',
753 p_reject_if_future_changes=> 'N',
754 p_batch_id=> l_batch_id,
755 p_object_version_number=> l_object_version_number
756 );
757
758 -- from the batch id, get the batch name
759 hxt_user_exits.define_batch_name (
760 l_batch_id,
761 l_batch_name,
762 l_error_text
763 );
764
765 IF l_error_text <> NULL
766 THEN
767 RAISE l_batch_name_error;
768 END IF;
769
770 --update the batch name
771 pay_batch_element_entry_api.update_batch_header (
772 p_session_date => g_sysdate,
773 p_batch_id=> l_batch_id,
774 p_object_version_number=> l_object_version_number,
775 p_batch_name=> l_batch_name
776 );
777
778
779 --END GLOBAL
780 COMMIT;
781 o_batch_id := l_batch_id;
782 return 0;
783 EXCEPTION
784 WHEN l_batch_id_error THEN
785 FND_MESSAGE.SET_NAME('HXT','HXT_39409_CREATE_BATCH'); -- HXT11
786 l_retcode := log_clock_errors(FALSE, NULL,'TIMCRT', SQLERRM);
787 --HXT11l_retcode := log_clock_errors(FALSE, 'Error creating batch id in hxt_time_gen.Get_Next_Batch_Id','Timecard Creation', SQLERRM);
788 RETURN 2;
789
790 WHEN l_reference_num_error THEN
791 FND_MESSAGE.SET_NAME('HXT','HXT_39410_CREATE_REF_FUNC'); -- HXT11
792 l_retcode := log_clock_errors(FALSE, NULL,'Timecard Creation', SQLERRM);
793 --HXT11l_retcode := log_clock_errors(FALSE, 'Error creating reference number in hxt_user_exits.Define_Reference_Number','Timecard Creation', SQLERRM);
794 RETURN l_retcode;
795
796 --BEGIN GLOBAL
797 WHEN l_batch_name_error THEN
798 FND_MESSAGE.SET_NAME('HXT','HXT_39484_CREATE_BATCH_NAME');
799 l_retcode := log_clock_errors(FALSE, NULL,'Timecard Creation', SQLERRM);
800 RETURN l_retcode;
801 --END GLOBAL
802
803 WHEN OTHERS THEN
804 FND_MESSAGE.SET_NAME('HXT','HXT_39411_CREATE_BATCH_FUNC'); -- HXT11
805 l_retcode := log_clock_errors(FALSE, NULL,'TIMCRT', SQLERRM);
806 --HXT11l_retcode := log_clock_errors(FALSE, 'Error in function create_batch','Timecard Creation', SQLERRM);
807 RETURN 2;
808 END create_batch;
809 /********************************************************************
810 find_existing_batch()
811 Examine the pay_pdt_batch_headers and the hxt_timeclocks
812 tables for existing unprocessed timeclock batches. The
813 batches must be in a hold status (batch_status = 'H')
814 and have less than the max amount of timecards allowed per batch.
815 ********************************************************************/
816 --BEGIN SIR343
817 --FUNCTION find_existing_batch(o_batch_id OUT NUMBER)RETURN NUMBER IS
818 FUNCTION find_existing_batch(i_payroll_id IN NUMBER,
819 i_time_period_id IN NUMBER,
820 o_batch_id OUT NOCOPY NUMBER)RETURN NUMBER IS
821 --END SIR343
822 l_max_batches NUMBER := fnd_profile.Value('HXT_BATCH_SIZE');
823 l_retcode NUMBER DEFAULT 0;
824 BEGIN
825 --BEGIN GLOBAL
826 -- SELECT bat.batch_id
827 -- INTO o_batch_id
828 -- FROM pay_pdt_batch_headers bat
829 -- WHERE bat.reference_num like 'C_%'
830 -- AND bat.batch_id = (SELECT MAX(bat2.batch_id)
831 -- FROM pay_pdt_batch_headers bat2
832 -- WHERE bat2.batch_status = 'H')
833 -- AND l_max_batches > (SELECT COUNT(tim.id)
834 -- FROM hxt_timecards tim
835 -- WHERE tim.batch_id = bat.batch_id);
836 --BEGIN SIR343
837 -- SELECT bat.batch_id
838 -- INTO o_batch_id
839 -- FROM pay_batch_headers bat
840 -- WHERE bat.batch_reference like 'C_%'
841 -- AND bat.batch_id = (SELECT MAX(bat2.batch_id)
842 -- FROM pay_batch_headers bat2
843 -- WHERE bat2.batch_status = 'U')
844 -- AND l_max_batches > (SELECT COUNT(tim.id)
845 -- FROM hxt_timecards tim
846 -- WHERE tim.batch_id = bat.batch_id);
847 SELECT MAX(bat.batch_id)
848 INTO o_batch_id
849 FROM pay_batch_headers bat
850 WHERE bat.batch_status = 'U'
851 AND bat.batch_reference like 'C_%'
852 AND not exists (SELECT 'x'
853 FROM hxt_timecards tim
854 WHERE tim.batch_id = bat.batch_id
855 AND (tim.time_period_id <> i_time_period_id
856 OR tim.payroll_id <> i_payroll_id))
857 AND l_max_batches > (SELECT COUNT(tim2.id)
858 FROM hxt_timecards tim2
859 WHERE tim2.batch_id = bat.batch_id);
860 --END SIR343
861 --END GLOBAL
862 RETURN 0;
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 RETURN 1;
866 WHEN OTHERS THEN
867 FND_MESSAGE.SET_NAME('HXT','HXT_39412_FIND_BATCH_FUNC'); -- HXT11
868 l_retcode := log_clock_errors(FALSE, NULL,'TIMCRT', SQLERRM);
869 --HXT11l_retcode := log_clock_errors(FALSE, 'Error in function find_existing_batch','Timecard Creation', SQLERRM);
870 RETURN 2;
871 END find_existing_batch;
872 /**********************************************************
873 create_holiday_hours()
874 Creates hours on new timecards for all holidays falling
875 between the start and end dates of the pay period.
876 **********************************************************/
877 FUNCTION create_holiday_hours( i_person_id IN NUMBER,
878 i_hcl_id IN NUMBER,
879 i_hxt_rotation_plan IN NUMBER, --SIR344
880 i_start_date IN DATE,
881 i_end_date IN DATE,
882 i_timecard_id IN NUMBER,
883 i_assignment_id IN NUMBER)RETURN NUMBER IS
884 CURSOR l_hol_cur( c_start_date DATE, c_end_date DATE, c_hcl_id NUMBER) IS
885 SELECT hcl.element_type_id,
886 hdy.hours,
887 hdy.holiday_date
888 FROM hxt_holiday_calendars hcl,
889 hxt_holiday_days hdy
890 WHERE TRUNC(hdy.holiday_date,'DD') BETWEEN TRUNC(c_start_date,'DD') AND TRUNC(c_end_date,'DD')
891 AND hcl.id = hdy.hcl_id
892 AND hdy.holiday_date BETWEEN hcl.effective_start_date AND hcl.effective_end_date
893 AND hcl.id = c_hcl_id;
894 -- not needed. declared in for loop l_hol_rec l_hol_cur%ROWTYPE;
895 l_retcode NUMBER DEFAULT 0;
896 l_hours_worked_error EXCEPTION;
897 --BEGIN SIR344
898 l_time_in DATE := NULL;
899 l_time_out DATE := NULL;
900 l_hours NUMBER;
901 l_work_id NUMBER;
902 l_osp_id NUMBER;
903 l_sdf_id NUMBER;
904 l_standard_start NUMBER;
905 l_standard_stop NUMBER;
906 l_early_start NUMBER;
907 l_late_stop NUMBER;
908 --END SIR344
909 BEGIN
910 FOR l_hol_rec IN l_hol_cur(i_start_date, i_end_date, i_hcl_id)
911 LOOP
912 --BEGIN SIR344
913 IF (fnd_profile.value('HXT_HOL_HOURS_FROM_HOL_CAL') = 'Y' OR
914 fnd_profile.value('HXT_HOL_HOURS_FROM_HOL_CAL') IS NULL) THEN
915 l_hours := l_hol_rec.hours;
916 l_time_out := NULL;
917 l_time_in := NULL;
918 ELSE
919 IF i_hxt_rotation_plan IS NOT NULL THEN
920 HXT_UTIL.get_shift_info(l_hol_rec.holiday_date,
921 l_work_id,
922 i_hxt_rotation_plan,
923 l_osp_id,
924 l_sdf_id,
925 l_standard_start,
926 l_standard_stop,
927 l_early_start,
928 l_late_stop,
929 l_hours,
930 l_retcode);
931 IF l_retcode <> 0 THEN
932 RAISE l_hours_worked_error;
933 END IF;
934 IF l_hours IS NOT NULL THEN
935 l_time_out := NULL;
936 l_time_in := NULL;
937 ELSE
938 l_time_in := to_date(to_char(l_hol_rec.holiday_date,'DDMMYYYY ')||
939 to_char(l_standard_start,'0009'), 'DDMMYYYY HH24MI');
940 l_time_out := to_date(to_char(l_hol_rec.holiday_date,'DDMMYYYY ')||
941 to_char(l_standard_stop,'0009'), 'DDMMYYYY HH24MI');
942 l_hours := 24 * (l_time_out - l_time_in);
943 IF l_hours = 0 THEN
944 l_time_out := NULL;
945 l_time_in := NULL;
946 END IF;
947 END IF;
948 END IF;
949 END IF;
950 --END SIR344
951 HXT_UTIL.DEBUG('The following holiday information was obtained');--DEBUG ONLY --HXT115
952 HXT_UTIL.DEBUG(fnd_date.date_to_chardate(l_hol_rec.holiday_date)); --DEBUG ONLY --FORMS60 --HXT115
953 HXT_UTIL.DEBUG(TO_CHAR(l_hol_rec.element_type_id)); --DEBUG ONLY --HXT115
954 --BEGIN SIR344
955 HXT_UTIL.DEBUG(TO_CHAR(l_hours)); --DEBUG ONLY --HXT115
956 HXT_UTIL.DEBUG(TO_CHAR(l_time_in,'MM-DD-YYYY HH24:MI')); --DEBUG ONLY --HXT115
957 HXT_UTIL.DEBUG(TO_CHAR(l_time_out,'MM-DD-YYYY HH24:MI')); --DEBUG ONLY --HXT115
958 --END SIR344
959 IF l_hours >= 0 THEN --SIR344
960 l_retcode := record_hours_worked( TRUE,
961 i_timecard_id,
962 i_assignment_id,
963 i_person_id,
964 l_hol_rec.holiday_date,
965 l_hol_rec.element_type_id,
966 l_hours, --SIR344
967 l_time_in, --SIR344
968 l_time_out, --SIR344
969 i_start_date);
970 END IF;
971 IF l_retcode <> 0 THEN
972 RAISE l_hours_worked_error;
973 END IF;
974 END LOOP;
975 RETURN 0;
976 EXCEPTION
977 WHEN NO_DATA_FOUND THEN
978 RETURN 0;
979 WHEN l_hours_worked_error THEN
980 RETURN 1;
981 WHEN OTHERS THEN
982 FND_MESSAGE.SET_NAME('HXT','HXT_39413_LOC_HOL'); -- HXT11
983 FND_MESSAGE.SET_TOKEN('ASG_ID',TO_CHAR(i_assignment_id)); -- HXT11
984 l_retcode := log_clock_errors(TRUE,NULL, 'TIMCRT', SQLERRM);
985 --HXT11l_retcode := log_clock_errors(TRUE,'Error attempting to locate holiday information'||
986 --HXT11 'while creating a timecard for Assignment: '||
987 --HXT11 TO_CHAR(i_assignment_id)||' in function create_holiday_hours',
988 --HXT11 'Timecard Creation',
989 --HXT11 SQLERRM);
990 RETURN 2;
991 END create_holiday_hours;
992 /*************************************************************************
993 record_hours_worked()
994 Fetches additional assignment details about employees.
995 Creates hours worked records on the hxt_hours_worked database table.
996 Calls the hxt_time_summary.generate_details function to explode details.
997 **************************************************************************/
998 FUNCTION record_hours_worked( b_generate_holiday IN BOOLEAN,
999 i_timecard_id IN NUMBER,
1000 i_assignment_id IN NUMBER,
1001 i_person_id IN NUMBER,
1002 i_date_worked IN DATE,
1003 i_element_id IN NUMBER,
1004 i_hours IN NUMBER,
1005 i_start_time IN DATE,
1006 i_end_time IN DATE,
1007 i_start_date IN DATE)RETURN NUMBER IS
1008 l_hol_yn CHAR DEFAULT 'N';
1009 l_details_error EXCEPTION;
1010 l_details_system_error EXCEPTION;
1011 l_hours_worked_id_error EXCEPTION;
1012 l_seq_num_error EXCEPTION;
1013 l_generate_details_error EXCEPTION;
1014 l_retcode NUMBER DEFAULT 0;
1015 l_hours_worked_id NUMBER DEFAULT NULL;
1016 l_sequence_number NUMBER DEFAULT NULL;
1017 l_rowid ROWID; --SIR012
1018
1019 -- ***** Start commented code for Bug 2669059 **************
1020 -- Commenting this cursor and adding two different cursors
1021 -- CURSOR l_details_cur(c_assignment_id NUMBER, c_date_worked DATE) IS
1022 -- SELECT aeiv.hxt_earning_policy, -- ORACLE
1023 -- aeiv.hxt_shift_differential_policy, -- ORACLE
1024 -- aeiv.hxt_hour_deduction_policy, -- ORACLE
1025 -- egp.fcl_earn_type,
1026 -- egp.egt_id,
1027 -- egp.pep_id,
1028 -- egp.pip_id,
1029 -- wsh.off_shift_prem_id,
1030 -- wsh.shift_diff_ovrrd_id
1031 -- FROM hxt_earning_policies egp,
1032 ----ORACLE per_assignments_f_dfv asmv,
1033 -- hxt_per_aei_ddf_v aeiv, --ORACLE
1034 -- per_assignment_status_types ast,
1035 -- per_assignments_f asm,
1036 -- hxt_shifts sht,
1037 -- hxt_weekly_work_schedules wws,
1038 -- hxt_work_shifts wsh,
1039 -- hxt_rotation_schedules rts
1040 -- WHERE c_assignment_id = asm.assignment_id
1041 -- AND ast.assignment_status_type_id = asm.assignment_status_type_id
1042 -- AND ast.pay_system_status = 'P' -- Check payroll status
1043 --ORACLE AND asmv.row_id = asm.rowid
1044 -- AND aeiv.assignment_id = asm.assignment_id --ORACLE
1045 -- AND c_date_worked BETWEEN aeiv.effective_start_date /* GLOBAL */
1046 -- AND aeiv.effective_end_date /* GLOBAL */
1047 --/*GLOBAL AND aeiv.information_type = 'HXT_ASS_INFO' --ORACLE */
1048 ----ORACLE AND egp.id(+) = asmv.hxt_earning_policy
1049 -- AND egp.id(+) = aeiv.hxt_earning_policy --ORACLE
1050 -- AND c_date_worked >= rts.start_date
1051 -- AND wsh.week_day = to_char(c_date_worked,'DY')
1052 -- AND wws.id = wsh.tws_id
1053 -- AND c_date_worked between wws.date_from AND nvl(wws.date_to, c_date_worked)
1054 -- AND wws.id = rts.tws_id
1055 -- AND sht.id = wsh.sht_id;
1056 -- ***** End commented code for Bug 2669059 **************
1057
1058 -- ***** Start new code for Bug 2669059 **************
1059 CURSOR l_details_cur(c_assignment_id NUMBER, c_date_worked DATE) IS
1060
1061 SELECT
1062 aeiv.hxt_earning_policy,
1063 aeiv.hxt_shift_differential_policy,
1064 aeiv.hxt_hour_deduction_policy,
1065 aeiv.hxt_rotation_plan,
1066 egp.fcl_earn_type,
1067 egp.egt_id,
1068 egp.pep_id,
1069 egp.pip_id
1070 FROM hxt_earning_policies egp,
1071 hxt_per_aei_ddf_v aeiv,
1072 per_assignment_status_types past,
1073 per_assignments_f paf
1074 WHERE c_assignment_id = paf.assignment_id
1075 AND c_date_worked BETWEEN paf.effective_start_date AND paf.effective_end_date
1076 AND past.assignment_status_type_id = paf.assignment_status_type_id
1077 AND past.pay_system_status = 'P' -- Check payroll status
1078 AND aeiv.assignment_id = paf.assignment_id
1079 AND c_date_worked BETWEEN aeiv.effective_start_date
1080 AND aeiv.effective_end_date
1081 AND egp.id = aeiv.hxt_earning_policy
1082 AND c_date_worked BETWEEN egp.effective_start_date
1083 AND egp.effective_end_date;
1084
1085 -- ***** End new code for Bug 2669059 **************
1086
1087 l_det_rec l_details_cur%ROWTYPE;
1088
1089 -- ***** Start new code for Bug 2669059 **************
1090 CURSOR l_wsh_details_cur(c_hxt_rotation_plan NUMBER, c_date_worked DATE) IS
1091 SELECT hws.off_shift_prem_id, hws.shift_diff_ovrrd_id
1092 FROM hxt_rotation_plans hrp,
1093 hxt_rotation_schedules hrs,
1094 hxt_weekly_work_schedules hwws,
1095 hxt_work_shifts hws
1096 WHERE hrp.id = c_hxt_rotation_plan
1097 AND c_date_worked BETWEEN hrp.date_from
1098 AND NVL (hrp.date_to, c_date_worked )
1099 AND hrs.rtp_id = hrp.id
1100 AND c_date_worked >= hrs.start_date
1101 AND hwws.id = hrs.tws_id
1102 AND c_date_worked BETWEEN hwws.date_from
1103 AND NVL (hwws.date_to, c_date_worked)
1104 AND hws.tws_id = hwws.id
1105 AND hws.week_day = hxt_util.get_week_day(c_date_worked);
1106
1107 l_wsh_det_rec l_wsh_details_cur%ROWTYPE;
1108
1109 -- ***** End new code Bug 2669059 **************
1110
1111 BEGIN
1112 /*Fetch additional assignment details about this employee*/
1113 BEGIN
1114 HXT_UTIL.DEBUG('Opening details Cursor');--DEBUG ONLY --HXT115
1115 OPEN l_details_cur(i_assignment_id, i_date_worked);
1116 HXT_UTIL.DEBUG('Fetching details Cursor');--DEBUG ONLY --HXT115
1117 FETCH l_details_cur
1118 INTO l_det_rec;
1119 HXT_UTIL.DEBUG('Closing details Cursor');--DEBUG ONLY --HXT115
1120 CLOSE l_details_cur;
1121 --DEBUG ONLY BEGIN
1122 HXT_UTIL.DEBUG('earning policy : '||l_det_rec.hxt_earning_policy);--HXT115
1123 HXT_UTIL.DEBUG('shift differential policy: '||l_det_rec.hxt_shift_differential_policy); --HXT115
1124 HXT_UTIL.DEBUG('hour deduction policy: '||l_det_rec.hxt_hour_deduction_policy); --HXT115
1125 HXT_UTIL.DEBUG('fcl earn type: '||l_det_rec.fcl_earn_type); --HXT115
1126 HXT_UTIL.DEBUG('egt id: '||TO_CHAR(l_det_rec.egt_id)); --HXT115
1127 HXT_UTIL.DEBUG('pep id: '||TO_CHAR(l_det_rec.pep_id)); --HXT115
1128 HXT_UTIL.DEBUG('pip id: '||TO_CHAR(l_det_rec.pip_id)); --HXT115
1129
1130 -- ***** Start commented code for Bug 2669059 **************
1131 -- HXT_UTIL.DEBUG('off_shift_prem_id: '||TO_CHAR(l_det_rec.off_shift_prem_id)); --HXT115
1132 -- HXT_UTIL.DEBUG('shift_diff_ovrrd_id: '||TO_CHAR(l_det_rec.shift_diff_ovrrd_id)); --HXT115
1133 -- ***** End commented code for Bug 2669059 **************
1134
1135 --DEBUG ONLY END
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 RAISE l_details_error;
1139 WHEN OTHERS THEN
1140 RAISE l_details_system_error;
1141 END; /*end additional assignment details*/
1142
1143 -- ***** Start new code for Bug 2669059 **************
1144
1145 IF l_det_rec.hxt_rotation_plan is not null THEN
1146 HXT_UTIL.DEBUG('Opening wsh details Cursor');--DEBUG ONLY --HXT115
1147 OPEN l_wsh_details_cur(l_det_rec.hxt_rotation_plan,i_date_worked);
1148 HXT_UTIL.DEBUG('Fetching wsh details Cursor');--DEBUG ONLY --HXT115
1149 FETCH l_wsh_details_cur
1150 INTO l_wsh_det_rec;
1151 HXT_UTIL.DEBUG('Closing wsh details Cursor');--DEBUG ONLY --HXT115
1152 CLOSE l_wsh_details_cur;
1153 END IF;
1154
1155
1156 HXT_UTIL.DEBUG('off_shift_prem_id: '||TO_CHAR(l_wsh_det_rec.off_shift_prem_id)); --HXT115
1157 HXT_UTIL.DEBUG('shift_diff_ovrrd_id: '||TO_CHAR(l_wsh_det_rec.shift_diff_ovrrd_id)); --HXT115
1158
1159 -- ***** End new code for Bug 2669059 **************
1160
1161 /*Flag holidays being generated*/
1162 IF b_generate_holiday = TRUE THEN
1163 l_hol_yn := 'Y';
1164 END IF;
1165 /*Obtain a unique hours worked id*/
1166 l_hours_worked_id := hxt_time_gen.Get_HXT_Seqno;
1167 IF l_hours_worked_id = NULL THEN
1168 RAISE l_hours_worked_id_error;
1169 END IF;
1170 g_hours_worked_err_id := l_hours_worked_id;
1171 /*Obtain the next sequence number for hours worked on this day*/
1172 l_sequence_number := hxt_util.Get_Next_Seqno( i_timecard_id, i_date_worked);
1173 IF l_sequence_number = NULL THEN
1174 RAISE l_seq_num_error;
1175 END IF;
1176 HXT_UTIL.DEBUG('Hours Worked Id is: '||TO_CHAR(l_hours_worked_id));--DEBUG ONLY --HXT115
1177 HXT_UTIL.DEBUG('Hours Worked Sequence Number is: '||TO_CHAR(l_sequence_number));--DEBUG ONLY --HXT115
1178 --SIR012 INSERT INTO hxt_sum_hours_worked --C421
1179 INSERT INTO hxt_sum_hours_worked_f --SIR012
1180 ( id,
1181 tim_id,
1182 -- C421 parent_id,
1183 date_worked,
1184 seqno,
1185 hours,
1186 assignment_id,
1187 element_type_id,
1188 time_in,
1189 time_out,
1190 created_by,
1191 creation_date
1192 , earn_pol_id --SIR012
1193 , effective_start_date --SIR012
1194 , effective_end_date) --SIR012
1195 VALUES
1196 ( l_hours_worked_id,
1197 i_timecard_id,
1198 -- C421 0,
1199 i_date_worked,
1200 l_sequence_number,
1201 i_hours,
1202 i_assignment_id,
1203 i_element_id,
1204 i_start_time,
1205 i_end_time,
1206 g_user_id,
1207 g_sysdate
1208 , l_det_rec.hxt_earning_policy --SIR012
1209 , g_sysdate --SIR012
1210 , hr_general.end_of_time); --SIR149 --FORMS60
1211 COMMIT;
1212 HXT_UTIL.DEBUG('Hours Worked Successfully inserted');--DEBUG ONLY --HXT115
1213
1214 select rowid --SIR012
1215 into l_rowid --SIR012
1216 from HXT_SUM_HOURS_WORKED --SIR012
1217 where id = l_hours_worked_id; --SIR012
1218
1219 /*Generate time details*/
1220 l_retcode := hxt_time_summary.Generate_Details(
1221 l_det_rec.hxt_earning_policy,
1222 l_det_rec.fcl_earn_type,
1223 l_det_rec.egt_id,
1224 l_det_rec.hxt_shift_differential_policy,
1225 l_det_rec.hxt_hour_deduction_policy,
1226 NULL, --Holiday day id
1227 l_det_rec.pep_id,
1228 l_det_rec.pip_id,
1229 l_wsh_det_rec.shift_diff_ovrrd_id,
1230 l_wsh_det_rec.off_shift_prem_id,
1231 NULL, --standard start
1232 NULL, --standard stop
1233 NULL, --early start
1234 NULL, --late stop
1235 l_hol_yn,
1236 i_person_id,
1237 'hxt_time_clock',
1238 l_hours_worked_id,
1239 -- C421 0, -- parent id
1240 i_timecard_id,
1241 --SIR012 NULL, -- line status
1242 i_date_worked,
1243 i_assignment_id,
1244 i_hours,
1245 i_start_time,
1246 i_end_time,
1247 i_element_id,
1248 NULL, --fcl_earn_reason_code
1249 NULL, --ffv_cost_center_id
1250 NULL, --ffv_labor_account_id
1251 NULL, --tas_id
1252 NULL, --location_id
1253 NULL, --sht_id
1254 NULL, --hrw_comment
1255 NULL, --ffv_rate_code_id
1256 NULL, --rate_multiple
1257 NULL, --hourly_rate
1258 NULL, --amount
1259 NULL, --fcl_tax_rule_code
1260 NULL, --separarate_check_flag
1261 l_sequence_number,
1262 g_user_id,
1263 g_sysdate,
1264 NULL, --last_updated_by
1265 NULL, --last_update_date
1266 NULL, --last_update_login
1267 i_start_date,
1268 l_rowid, --SIR012
1269 trunc(sysdate), --SIR012
1270 hr_general.end_of_time, --SIR149 --FORMS60
1271 NULL, --SIR012
1272 NULL, --SIR012
1273 'P', --SIR012
1274 'P', --SIR012
1275 NULL, --SIR012
1276 'C'); --SIR012
1277 IF l_retcode = 2 THEN
1278 RAISE l_generate_details_error;
1279 END IF;
1280 COMMIT;
1281 RETURN 0;
1282
1283 EXCEPTION
1284 WHEN l_details_error THEN
1285 FND_MESSAGE.SET_NAME('HXT','HXT_39414_LOC_ADDL_ASG'); -- HXT11
1286 FND_MESSAGE.SET_TOKEN('ASG_ID',TO_CHAR(i_assignment_id)); -- HXT11
1287 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', NULL);
1288 --HXT11l_retcode := log_clock_errors(TRUE, 'Additional Assignment details not located for Assignment '||TO_CHAR(i_assignment_id),'Record Hours', NULL);
1289 RETURN 1;
1290
1291 WHEN l_details_system_error THEN
1292 FND_MESSAGE.SET_NAME('HXT','HXT_39415_FETCH_ASG_DET'); -- HXT11
1293 FND_MESSAGE.SET_TOKEN('ASG_ID',TO_CHAR(i_assignment_id)); -- HXT11
1294 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', SQLERRM);
1295 --HXT11l_retcode := log_clock_errors(TRUE,
1296 --HXT11 'System Error occurred atempting to Fetch Assignment details'||
1297 --HXT11 'for Assignment '||TO_CHAR(i_assignment_id)||
1298 --HXT11 ' in Function record_hours_worked.',
1299 --HXT11 'Record Hours',SQLERRM);
1300 RETURN 2;
1301
1302 WHEN l_hours_worked_id_error THEN
1303 FND_MESSAGE.SET_NAME('HXT','HXT_39416_GET_HRW_ID'); -- HXT11
1304 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', SQLERRM);
1305 --HXT11l_retcode := log_clock_errors(TRUE, 'Error while attempting to obtain hours worked id from hxt_time_gen.Get_HXT_Seqno.','Record Hours', SQLERRM);
1306 RETURN 2;
1307
1308 WHEN l_generate_details_error THEN
1309 FND_MESSAGE.SET_NAME('HXT','HXT_39417_PROB_GEN_DET'); -- HXT11
1310 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', SQLERRM);
1311 --HXT11l_retcode := log_clock_errors(TRUE,'A problem occurred in the function hxt_time_summary.Generate_Details.','Record Hours', SQLERRM);
1312 RETURN 2;
1313
1314 WHEN l_seq_num_error THEN
1315 FND_MESSAGE.SET_NAME('HXT','HXT_39418_GET_HRW_SEQ'); -- HXT11
1316 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', SQLERRM);
1317 --HXT11l_retcode := log_clock_errors(TRUE,'Error while attempting to obtain hours worked sequence number in function record_hours_worked.','Record Hours', SQLERRM);
1318 RETURN 2;
1319
1320 WHEN OTHERS THEN
1321 FND_MESSAGE.SET_NAME('HXT','HXT_39419_SYSERR_RECFUNC'); -- HXT11
1322 l_retcode := log_clock_errors(TRUE, NULL,'RECHRS', SQLERRM);
1323 --HXT11l_retcode := log_clock_errors(TRUE,'System Error occurred in Function record_hours_worked','Record Hours',SQLERRM);
1324 RETURN 2;
1325 END record_hours_worked;
1326 /****************************************************
1327 log_clock_errors()
1328 Records errors for timeclock processing.
1329 The timecard available flag determines
1330 where to post the error:
1331 * In Phase I all errors are posted to HXT_ERRORS.
1332 * In Phase II errors with no timecard will be
1333 posted to a new table called HXT_TIMECLOCK_ERRORS.
1334 ****************************************************/
1335 FUNCTION log_clock_errors(i_timecard_available IN BOOLEAN,
1336 i_error_text IN VARCHAR2,
1337 i_error_location IN VARCHAR2,
1338 i_sql_message IN VARCHAR2)RETURN NUMBER IS
1339 l_error_seqno NUMBER DEFAULT NULL;
1340 l_err_buf VARCHAR2(240); -- HXT11
1341 BEGIN
1342 HXT_UTIL.DEBUG('g_batch_err_id = ' || TO_CHAR(g_batch_err_id)); --DEBUG ONLY --HXT115
1343 HXT_UTIL.DEBUG('g_timecard_err_id = ' || TO_CHAR(g_timecard_err_id)); --DEBUG ONLY --HXT115
1344 HXT_UTIL.DEBUG('g_hours_worked_err_id = ' || TO_CHAR(g_hours_worked_err_id)); --DEBUG ONLY --HXT115
1345 HXT_UTIL.DEBUG('g_time_period_err_id = ' || g_time_period_err_id); --DEBUG ONLY --HXT115
1346 HXT_UTIL.DEBUG(i_error_text || i_sql_message); --DEBUG ONLY --HXT115
1347 --begin HXT11
1348 l_err_buf := FND_MESSAGE.GET;
1349 FND_MESSAGE.CLEAR;
1350 --end HXT11
1351 IF i_timecard_available THEN
1352 HXT_UTIL.DEBUG('TC available. Calling hxt_util.Gen_error'); --DEBUG ONLY --HXT115
1353 hxt_util.Gen_Error( g_batch_err_id,
1354 g_timecard_err_id,
1355 g_hours_worked_err_id,
1356 g_time_period_err_id,
1357 i_error_text,
1358 i_error_location,
1359 i_sql_message,
1360 trunc(sysdate), --SIR012
1361 hr_general.end_of_time, --SIR149 --FORMS60
1362 'ERR'); --HXT11i1
1363 HXT_UTIL.DEBUG('Back from calling hxt_util.Gen_error'); --DEBUG ONLY --HXT115
1364 ELSE
1365 SELECT hxt_seqno.nextval
1366 INTO l_error_seqno
1367 FROM DUAL;
1368 --SIR012 insert into hxt_errors( id,
1369 --SIR012 error_msg,
1370 --SIR012 creation_date,
1371 --SIR012 location,
1372 --SIR12 created_by,
1373 --SIR12 err_type,
1374 --SIR12 PPB_ID,
1375 --SIR12 TIM_ID,
1376 --SIR12 HRW_ID,
1377 --SIR12 PTP_ID,
1378 --SIR12 ora_message)
1379 --SIR12 values( l_error_seqno,
1380 --SIR12 i_error_text,
1381 --SIR12 sysdate,
1382 --SIR12 i_error_location,
1383 --SIR12 g_user_id,
1384 --SIR12 'CLK',
1385 --SIR12 g_batch_err_id,
1386 --SIR12 g_timecard_err_id,
1387 --SIR12 g_hours_worked_err_id,
1388 --SIR12 g_time_period_err_id,
1389 --SIR12 i_sql_message);
1390
1391 HXT_UTIL.DEBUG('TC not available. Calling hxt_util.Gen_error'); --DEBUG ONLY --HXT115
1392 HXT_util.Gen_Error( 0, --SIR12
1393 0, --SIR12
1394 0, --SIR12
1395 0, --SIR12
1396 i_error_text, --SIR12
1397 i_error_location, --SIR12
1398 i_sql_message, --SIR12
1399 trunc(sysdate) --SIR12
1400 , hr_general.end_of_time, --SIR149 --FORMS60
1401 'ERR'); --HXT11i1
1402 COMMIT;
1403 END IF;
1404 RETURN 0;
1405 EXCEPTION
1406 WHEN OTHERS THEN
1407 RAISE g_error_log_error;
1408 END log_clock_errors;
1409 END hxt_time_clock;