[Home] [Help]
PACKAGE BODY: APPS.HXT_RETRO_PA
Source
1 PACKAGE BODY HXT_RETRO_PA AS
2 /* $Header: hxtrpa.pkb 120.0.12010000.2 2008/08/19 14:01:11 asrajago ship $ */
3 l_hours_per_year NUMBER(22,5) := TO_NUMBER(fnd_profile.Value('HXT_HOURS_PER_YEAR'));
4
5 PROCEDURE retro_pa_process(o_err_buf OUT NOCOPY VARCHAR2,
6 o_ret_code OUT NOCOPY NUMBER,
7 i_payroll_id IN NUMBER,
8 i_time_period_id IN NUMBER)IS
9
10 l_retcode NUMBER DEFAULT 0;
11 l_error_text VARCHAR2(240) DEFAULT NULL;
12 l_system_text VARCHAR2(120) DEFAULT NULL;
13 l_location VARCHAR2(120) DEFAULT NULL;
14 l_sum_start_date hxt_sum_hours_worked_f.effective_start_date%TYPE;
15 l_sum_end_date hxt_sum_hours_worked_f.effective_end_date%TYPE;
16 l_sum_id hxt_sum_hours_worked_f.id%TYPE;
17 l_ewr_error EXCEPTION;
18 l_non_ewr_error EXCEPTION;
19 l_conc_error EXCEPTION;
20 l_no_timecards EXCEPTION;
21 l_no_details EXCEPTION;
22 l_batch_state EXCEPTION;
23
24 l_conc_error_flag BOOLEAN DEFAULT FALSE;
25 l_any_timecards BOOLEAN DEFAULT FALSE;
26
27 /****************************************
28 Cursor to select each timecard within
29 the payroll/time-period requested
30 ****************************************/
31
32 CURSOR l_timecard_cur(c_payroll_id NUMBER, c_time_period_id NUMBER)IS
33 SELECT tim.id,
34 tim.batch_id,
35 tim.time_period_id,
36 tim.rowid,
37 tim.effective_start_date,
38 tim.effective_end_date,
39 ptp.end_date,
40 ptpt.number_per_fiscal_year,
41 ppbh.batch_status,
42 ppf.employee_number
43 FROM hxt_timecards_x tim,
44 per_time_periods ptp,
45 per_time_period_types ptpt,
46 pay_batch_headers ppbh,
47 per_people_f ppf
48 WHERE tim.payroll_id = c_payroll_id
49 AND ptp.time_period_id BETWEEN NVL(c_time_period_id,1) AND NVL(c_time_period_id,999999999)
50 AND tim.time_period_id = ptp.time_period_id
51 AND ptp.period_type = ptpt.period_type
52 AND tim.batch_id = ppbh.batch_id
53 AND ppbh.batch_status <> 'U'
54 AND ppf.person_id = tim.for_person_id
55 AND ppf.effective_start_date =
56 (select MAX (ppf2.effective_start_date) -- to handle mid pay period changes
57 from per_people_f ppf2
58 where ppf2.person_id = tim.for_person_id
59 and ppf2.effective_end_date >= ptp.START_date
60 and ppf2.effective_start_date <= ptp.END_date)
61 AND EXISTS (SELECT 'Y'
62 FROM hxt_det_hours_worked_x det
63 WHERE det.tim_id = tim.id
64 AND det.pa_status = 'R') ;
65 BEGIN
66 -- select each timecard within the payroll/time-period
67 FOR l_timecard_rec IN l_timecard_cur( i_payroll_id, i_time_period_id) LOOP
68 g_batch_err_id := l_timecard_rec.batch_id;
69 g_err_effective_start := l_timecard_rec.effective_start_date;
70 g_err_effective_end := l_timecard_rec.effective_end_date;
71 HXT_UTIL.DEBUG('.'); --DEBUG ONLY --HXT115
72 HXT_UTIL.DEBUG('l_timecard_rec.id = ' || TO_CHAR(l_timecard_rec.id));
73 HXT_UTIL.DEBUG('l_timecard_rec.batch_id = ' || TO_CHAR(l_timecard_rec.batch_id));
74 HXT_UTIL.DEBUG('l_timecard_rec.time_period_id = ' || TO_CHAR(l_timecard_rec.time_period_id));
75 HXT_UTIL.DEBUG('l_timecard_rec.end_date = ' ||
76 fnd_date.date_to_chardate(l_timecard_rec.end_date));
77
78 BEGIN --timecard error block
79
80 l_any_timecards := TRUE;
81 -- set id's related to each timecard for error messaging
82 g_timecard_err_id := l_timecard_rec.id;
83 g_time_period_err_id := l_timecard_rec.time_period_id;
84
85 HXT_UTIL.DEBUG('l_timecard_rec.id = ' || TO_CHAR(l_timecard_rec.id));
86
87 -- Clean up existing transfer to PA errors for this payroll/period
88 DELETE
89 FROM hxt_errors_f errf
90 WHERE errf.tim_id = l_timecard_rec.id
91 AND (errf.location LIKE 'hxt_retro_pa%'
92 OR errf.location LIKE 'hxt_pa_user_exits.p_a_%');
93
94 COMMIT;
95
96 /*************************************************
97 -- Effective Wage Rate Not Currently Implemented
98 --
99 -- Call the logic to calculate effective
100 -- wage and transfer retro effective wage
101 -- time details to Project Accounting.
102 --
103 -- l_retcode := hxt_ewr_pa.retro_effective_wage_rate(l_timecard_rec.id,
104 -- l_timecard_rec.end_date,
105 -- l_timecard_rec.number_per_fiscal_year,
106 -- 'Y',
107 -- l_timecard_rec.employee_number,
108 -- l_sum_start_date,
109 -- l_sum_end_date,
110 -- l_sum_id,
111 -- l_location,
112 -- l_error_text,
113 -- l_system_text);
114 --
115 -- Report any errors for EWR and ROLLBACK
116 -- IF l_retcode = 1 THEN
117 -- g_err_effective_start := l_sum_start_date;
118 -- g_err_effective_end := l_sum_end_date;
119 -- g_sum_hours_err_id := l_sum_id;
120 -- RAISE l_ewr_error;
121 -- END IF;
122 ***********************************************************************************/
123
124
125 -- Call the logic to transfer retro time
126 -- details to Project Accounting that were not
127 -- included in Effective Wage Rate processing.
128
129 l_retcode := retro_non_ewr_transfer(l_timecard_rec.id,
130 l_timecard_rec.end_date,
131 l_timecard_rec.number_per_fiscal_year,
132 l_timecard_rec.employee_number,
133 l_location,
134 l_error_text,
135 l_system_text);
136
137 -- Report any errors for non-EWR and ROLLBACK
138 IF l_retcode = 1 THEN
139 RAISE l_non_ewr_error;
140 END IF;
141
142 g_err_effective_start := l_timecard_rec.effective_start_date;
143 g_err_effective_end := l_timecard_rec.effective_end_date;
144
145 -- Update all retro rows processed to show completed
146 UPDATE hxt_det_hours_worked_f
147 SET pa_status = 'C'
148 WHERE rowid IN (SELECT d.rowid
149 FROM hxt_det_hours_worked_x d
150 WHERE d.pa_status = 'R'
151 AND d.tim_id = l_timecard_rec.id);
152
153 COMMIT; -- commit data for this timecard
154
155 EXCEPTION
156 -- Effective Wage Rate Errors
157 WHEN l_ewr_error THEN
158 ROLLBACK;
159 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
160 l_conc_error_flag := TRUE;
161 -- Non Effective Wage Rate Errors
162 WHEN l_non_ewr_error THEN
163 ROLLBACK;
164 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
165 l_conc_error_flag := TRUE;
166 -- Other errors
167 WHEN OTHERS THEN
168 ROLLBACK;
169 l_location := 'hxt_retro_pa.retro_pa_process';
170 FND_MESSAGE.SET_NAME('HXT','HXT_39454_PA_XFER_ERROR');
171 l_error_text := FND_MESSAGE.GET;
172 FND_MESSAGE.CLEAR;
173 l_system_text := SQLERRM;
174 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
175 l_conc_error_flag := TRUE;
176 END; -- timecard error block
177 END LOOP; -- timecard loop
178
179 -- Return an error if any timecards had a problem
180 IF l_conc_error_flag = TRUE THEN
181 RAISE l_conc_error;
182 END IF;
183
184 -- Return an error if NO timecards exist for this payroll/period
185 IF l_any_timecards = FALSE THEN
186 RAISE l_no_timecards;
187 END IF;
188
189 o_ret_code := 0;
190 HXT_UTIL.DEBUG('Retro details successfully transferred!');
191
192 EXCEPTION
193 WHEN l_conc_error THEN
194 FND_MESSAGE.SET_NAME('HXT','HXT_39456_CHK_TCARD_ERRS');
195 o_err_buf := FND_MESSAGE.GET;
196 FND_MESSAGE.CLEAR;
197 o_ret_code := 2;
198 HXT_UTIL.DEBUG('Check timecards for errors or run timecard report.');
199 RETURN;
200 WHEN l_no_timecards THEN
201 FND_MESSAGE.SET_NAME('HXT','HXT_39457_NO_TCARD_4_PAY_PRD');
202 o_err_buf := FND_MESSAGE.GET;
203 FND_MESSAGE.CLEAR;
204 o_ret_code := 2;
205 HXT_UTIL.DEBUG('No timecards located for this payroll');
206 RETURN;
207 WHEN l_no_details THEN
208 FND_MESSAGE.SET_NAME('HXT','HXT_39458_NO_TCARD_DET_XFER');
209 o_err_buf := FND_MESSAGE.GET;
210 FND_MESSAGE.CLEAR;
211 o_ret_code := 0;
212 HXT_UTIL.DEBUG('No timecard details transferable');
213 RETURN;
214 WHEN g_error_log_error THEN
215 FND_MESSAGE.SET_NAME('HXT','HXT_39460_HXTRPA_ERR');
216 o_err_buf := FND_MESSAGE.GET;
217 FND_MESSAGE.CLEAR;
218 HXT_UTIL.DEBUG('Error in hxt_retro_pa.log_transfer_errors.');
219 o_ret_code := 2;
220 RETURN;
221 WHEN OTHERS THEN
222 l_location := 'hxt_retro_pa';
223 FND_MESSAGE.SET_NAME('HXT','HXT_39454_PA_XFER_ERROR');
224 l_error_text := FND_MESSAGE.GET;
225 FND_MESSAGE.CLEAR;
226 l_system_text := SQLERRM;
227 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
228 o_ret_code := 2;
229 FND_MESSAGE.SET_NAME('HXT','HXT_39460_HXTRPA_ERR');
230 o_err_buf := FND_MESSAGE.GET;
231 FND_MESSAGE.CLEAR;
232 HXT_UTIL.DEBUG('Error processing timecard for PA Transfer');
233 RETURN;
234 END retro_pa_process;
235 /************************************************************************************************
236 non_eff_wage_rate_transfer()
237 Processing of transfer to Project Accounting for all Time details not
238 processed as a part of the Effective Wage Rate Logic.
239
240 Values in the pa_status column on the hxt_det_hours_worked_f (details) table
241 should be interpreted as follows:
242
243 P - pending: this row has not yet been successfully processed by any transfer Logic
244
245 C - completed: this row has been successfully processed by transfer or retro Project Accounting
246
247 R - retro: this row represents a new detail change and is ready for Retro processing
248 note: retro rows that have been updated with new retro rows by the user
249 will remain in a retro status permenantly becacuse of the effective_end_date
250
251 A - adjusted: this row had an adjusting entry sent to Project Accounting.
252 The adjustment was an hours or amount change only, representing
253 the difference between this previously sent detail/PA row and the new changes
254
255 B - backed out: the details related to this row that were previously sent to
256 Project Accouning had to be backed out nocopy (Insert negative hours to PA interface).
257 PA rows are backed out nocopy when newer (retro) entries have invalidated
258 them through changes to data vital to Project Accounting.
259
260 D - dead row: this row has been replaced by a newer(retro) detail row with identical information.
261 This can happen when the retro explosion process creates rows where data has not
262 changed. These newer retro rows are not sent to PA because an entry
263 already exists there representing the amount.
264
265 **************************************************************************************************/
266 FUNCTION retro_non_ewr_transfer(i_timecard_id IN NUMBER,
267 i_ending_date IN DATE,
268 i_annual_pay_periods IN NUMBER,
269 i_employee_number IN VARCHAR2,
270 o_location OUT NOCOPY VARCHAR2,
271 o_error_text OUT NOCOPY VARCHAR2,
272 o_system_text OUT NOCOPY VARCHAR2) RETURN NUMBER IS
273
274 /****************************************************************
275 Summary cursor to select each summmary row associated
276 with the timecard being processed.
277 ****************************************************************/
278 CURSOR l_sum_cur(c_timecard_id NUMBER) IS
279 SELECT id,
280 effective_start_date,
281 effective_end_date
282 FROM hxt_sum_hours_worked_x
283 WHERE tim_id = c_timecard_id;
284 --TA36 not needed. declared in FOR loop.l_sum_rec l_sum_cur%ROWTYPE;
285
286 /********************************************************************
287 Cursor to select all detail rows associated with the timecard
288 that are not associated with Effective Wage Rate calculations.
289 Each retro detail row will be passed to Project Accounting.
290 *********************************************************************/
291 CURSOR l_non_cur(c_summary_id NUMBER)IS
292 SELECT NVL(pro.proposed_salary_N,0) proposed_salary,
293 ppb.pay_basis,
294 fcl2.meaning emp_cat_description,
295 payd.hxt_earning_category,
296 payd.hxt_premium_type,
297 NVL(payd.hxt_premium_amount,0) hxt_premium_amount,
298 det.id,
299 det.hours,
300 det.amount,
301 det.date_worked,
302 det.effective_start_date det_effective_start,
303 det.effective_end_date det_effective_end,
304 det.hourly_rate,
305 det.rate_multiple,
306 det.ffv_cost_center_id,
307 det.job_id,
308 det.tas_id,
309 det.project_id,
310 prj.name project_name,
311 prj.segment1,
312 task.task_number,
313 task.task_name,
314 org.name,
315 payt.element_name,
316 asg.organization_id,
317 asg.assignment_id,
318 asg.assignment_number,
319 det.parent_id,
320 det.element_type_id
321 FROM hxt_det_hours_worked_x det,
322 per_pay_proposals pro,
323 per_pay_bases ppb,
324 hr_lookups fcl,
325 hr_lookups fcl2,
326 per_assignments_f asg,
327 hr_organization_units_v org,
328 pay_element_types_f pay,
329 pay_element_types_f_tl payt,
330 hxt_pay_element_types_f_ddf_v payd,
331 pa_projects_all prj,
332 pa_tasks task
333 WHERE det.parent_id = c_summary_id
334 AND det.pa_status = 'R'
335 AND det.assignment_id = asg.assignment_id
336 AND det.date_worked BETWEEN asg.effective_start_date AND asg.effective_end_date
337 AND asg.pay_basis_id = ppb.pay_basis_id
338 AND asg.organization_id = org.organization_id
339 AND det.assignment_id = pro.assignment_id
340 AND pro.approved = 'Y'
341 AND pro.change_date = (SELECT MAX(pro2.change_date)
342 FROM per_pay_proposals pro2
343 WHERE pro2.assignment_id = det.assignment_id
344 AND pro2.approved = 'Y'
345 AND det.date_worked >= pro2.change_date)
346 AND det.element_type_id = pay.element_type_id
347 AND payt.element_type_id = pay.element_type_id
348 AND payt.language = userenv('LANG')
349 AND pay.element_type_id = payd.element_type_id
350 AND det.date_worked BETWEEN payd.effective_start_date
351 AND payd.effective_end_date
352 AND det.date_worked BETWEEN pay.effective_start_date
353 AND pay.effective_end_date
354 AND payd.hxt_earning_category = fcl.lookup_code
355 AND fcl.lookup_type = 'HXT_EARNING_CATEGORY'
356 AND fcl.application_id = 808
357 AND asg.employment_category = fcl2.lookup_code
358 AND fcl2.lookup_type = 'EMP_CAT'
359 AND fcl2.application_id = 800
360 AND det.project_id = prj.project_id
361 AND det.tas_id = task.task_id(+);
362
363
364 /************************************************************************
365 Cursor to select any expired rows where only the hours have changed.
366 *************************************************************************/
367 CURSOR l_expired_cur(c_parent_id NUMBER,
368 c_project_id NUMBER,
369 c_task_id NUMBER,
370 c_element_type_id NUMBER,
371 c_hourly_rate NUMBER,
372 c_amount NUMBER,
373 c_ffv_cost_center_id NUMBER,
374 c_job_id NUMBER)IS
375 SELECT detf.hours,
376 detf.id,
377 detf.rowid row_id
378 FROM hxt_det_hours_worked_f detf
379 WHERE detf.parent_id = c_parent_id
380 AND detf.project_id = c_project_id
381 AND NVL(detf.tas_id,-1) = NVL(c_task_id,-1)
382 AND detf.element_type_id = c_element_type_id
383 AND NVL(detf.hourly_rate,-1) = NVL(c_hourly_rate,-1)
384 AND NVL(detf.amount,-1) = NVL(c_amount, -1)
385 AND detf.pa_status = 'C'
386 AND NVL(detf.ffv_cost_center_id,-1) = NVL(c_ffv_cost_center_id, -1)
387 AND NVL(detf.job_id,-1) = NVL(c_job_id, -1);
388 l_expired_rec l_expired_cur%ROWTYPE;
389
390 /***********************************************************
391 Cursor to select details rows for backout transactions.
392 Select all unprocessed rows 'C' that have been expired.
393 ***********************************************************/
394 CURSOR l_backout_cur(c_summary_id NUMBER)IS
395 SELECT NVL(pro.proposed_salary_N,0) proposed_salary,
396 ppb.pay_basis,
397 fcl2.meaning emp_cat_description,
398 payd.hxt_earning_category,
399 payd.hxt_premium_type,
400 NVL(payd.hxt_premium_amount,0) hxt_premium_amount,
401 det.rowid row_id,
402 det.id,
403 det.hours,
404 det.amount,
405 det.date_worked,
406 det.effective_start_date det_effective_start,
407 det.effective_end_date det_effective_end,
408 det.hourly_rate,
409 det.rate_multiple,
410 det.tas_id,
411 det.ffv_cost_center_id,
412 det.job_id,
413 det.project_id,
414 prj.name project_name,
415 prj.segment1,
416 task.task_number,
417 task.task_name,
418 org.name,
419 payt.element_name, --FORMS60
420 asg.organization_id,
421 det.element_type_id, --SIR162
422 asg.assignment_id,
423 asg.assignment_number
424 FROM hxt_det_hours_worked_f det,
425 per_pay_proposals pro,
426 per_pay_bases ppb,
427 hr_lookups fcl, --FORMS60
428 hr_lookups fcl2, --FORMS60
429 per_assignments_f asg,
430 hr_organization_units_v org,
431 pay_element_types_f pay,
432 pay_element_types_f_tl payt, --FORMS60
433 hxt_pay_element_types_f_ddf_v payd,
434 pa_projects_all prj,
435 pa_tasks task
436 WHERE det.parent_id = c_summary_id
437 AND det.pa_status = 'C'
438 AND det.assignment_id = asg.assignment_id
439 AND det.date_worked BETWEEN asg.effective_start_date AND asg.effective_end_date
440 AND asg.pay_basis_id = ppb.pay_basis_id
441 AND asg.organization_id = org.organization_id
442 AND asg.assignment_id = pro.assignment_id
443 AND pro.approved = 'Y'
444 AND pro.change_date = (SELECT MAX(pro2.change_date)
445 FROM per_pay_proposals pro2
446 WHERE pro2.assignment_id = det.assignment_id
447 AND pro2.approved = 'Y'
448 AND det.date_worked >= pro2.change_date)
449 AND payt.element_type_id = pay.element_type_id
450 AND payt.language = userenv('LANG')
451 AND pay.element_type_id = payd.element_type_id
452 AND payd.element_type_id = det.element_type_id
453 AND det.date_worked BETWEEN payd.effective_start_date
454 AND payd.effective_end_date
455 AND det.date_worked BETWEEN pay.effective_start_date
456 AND pay.effective_end_date
457 AND payd.hxt_earning_category = fcl.lookup_code
458 AND fcl.lookup_type = 'HXT_EARNING_CATEGORY'
459 AND fcl.application_id = 808
460 AND asg.employment_category = fcl2.lookup_code
461 AND fcl2.lookup_type = 'EMP_CAT'
462 AND fcl2.application_id = 800
463 AND det.project_id = prj.project_id
464 AND det.tas_id = task.task_id(+)
465 AND det.effective_end_date < hr_general.end_of_time;
466
467 --TA36 not needed. declared in FOR loop.l_backout_rec l_backout_cur%ROWTYPE;
468
469 l_retcode NUMBER DEFAULT 0;
470 l_message VARCHAR2(240) DEFAULT NULL;
471 l_premium_amount NUMBER(22,5) DEFAULT 0.00000;
472 l_rate NUMBER(22,5) DEFAULT 0.00000;
473 l_standard_rate NUMBER(22,5) DEFAULT 0.00000;
474 l_premium_hours NUMBER(22,5) DEFAULT NULL;
475
476 l_backout_hours NUMBER(22,5) DEFAULT NULL;
477 l_backout_amount NUMBER(22,5) DEFAULT NULL;
478
479 l_transfer_error EXCEPTION;
480 l_adjusting_error EXCEPTION;
481 l_backout_error EXCEPTION;
482 l_retro_error EXCEPTION;
483
484 BEGIN
485
486 -- Loop to process all summary records for this assignment
487 HXT_UTIL.DEBUG('Processing NON-EWR time'); --HXT115
488 FOR l_sum_rec IN l_sum_cur(i_timecard_id) LOOP
489
490 g_err_effective_start := l_sum_rec.effective_start_date;
491 g_err_effective_end := l_sum_rec.effective_end_date;
492 g_sum_hours_err_id := l_sum_rec.id;
493
494 -- Process all eligible time detail rows for this summary
495 FOR l_non_rec IN l_non_cur(l_sum_rec.id) LOOP
496
497 -- Calculate an houly rate for the salary basis
498 IF l_non_rec.pay_basis = 'ANNUAL' THEN
499 l_rate := l_non_rec.proposed_salary / l_hours_per_year;
500 ELSIF l_non_rec.pay_basis = 'MONTHLY' THEN
501 l_rate := (l_non_rec.proposed_salary * 12) / l_hours_per_year;
502 ELSIF l_non_rec.pay_basis = 'PERIOD' THEN
503 l_rate := (l_non_rec.proposed_salary * i_annual_pay_periods) / l_hours_per_year;
504 ELSE -- 'HOURLY'
505 l_rate := l_non_rec.proposed_salary;
506 END IF;
507 l_standard_rate := l_rate;
508
509 HXT_UTIL.DEBUG('Employee '||i_employee_number||
510 ' timecard '||TO_CHAR(i_timecard_id) ||' assignment id of '||
511 TO_CHAR(l_non_rec.assignment_id));
512 HXT_UTIL.DEBUG('Normal Hourly Rate for '||i_employee_number||' is: '
513 ||TO_CHAR(l_rate));
514
515 -- Take the override rate when one exists
516 IF l_non_rec.hourly_rate IS NOT NULL THEN
517 l_rate := l_non_rec.hourly_rate;
518 HXT_UTIL.DEBUG('Using Override Hourly Rate of '||TO_CHAR(l_rate));
519 END IF;
520
521 -- Process Base Hours
522 IF l_non_rec.hxt_earning_category IN ('ABS','OVT','REG') THEN
523 -- BEGIN SIR#5
524 -- Handle Flat Amounts on Base Hours Types
525 IF l_non_rec.amount IS NOT NULL THEN
526 l_premium_amount := l_non_rec.amount;
527 HXT_UTIL.DEBUG('Sending Premium Flat amount entered on timecard, amount:'
528 ||TO_CHAR(l_premium_amount)||
529 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
530 ELSE
531 l_premium_amount := NULL;
532 -- Calculate rate per hour for overtime using the available premium types
533 IF l_non_rec.hxt_earning_category = 'OVT' THEN
534 IF l_non_rec.hxt_premium_type = 'FACTOR' THEN
535 -- Use the manually entered multiple when one exists
536 -- else, use the multiple from the element descriptive flex
537 IF l_non_rec.rate_multiple IS NOT NULL THEN
538 l_rate := l_rate * l_non_rec.rate_multiple;
539 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.rate_multiple)
540 ||'Sending Overtime FACTOR/manual multiple rate:'||
541 TO_CHAR(l_rate)||' '||l_non_rec.element_name||','
542 ||' hours:'||TO_CHAR(l_non_rec.hours));
543 ELSE
544 l_rate := l_rate * l_non_rec.hxt_premium_amount;
545 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.hxt_premium_amount)
546 ||'Sending Overtime FACTOR/element premium rate:'||
547 TO_CHAR(l_rate)||' '||l_non_rec.element_name||','||' hours:'
548 ||TO_CHAR(l_non_rec.hours));
549 END IF;
550 ELSIF l_non_rec.hxt_premium_type = 'RATE' THEN
551 l_rate := l_non_rec.hxt_premium_amount;
552 HXT_UTIL.DEBUG('Sending Overtime RATE/element premium rate:'
553 ||TO_CHAR(l_rate)||
554 ' '||l_non_rec.element_name||','||' hours:'
555 ||TO_CHAR(l_non_rec.hours));
556 ELSE -- FIXED amount per day
557 l_rate := l_non_rec.hxt_premium_amount / l_non_rec.hours;
558 HXT_UTIL.DEBUG('Sending Overtime Flat/element premium rate:'
559 ||TO_CHAR(l_rate)||
560 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
561 END IF;
562 ELSIF l_non_rec.hxt_earning_category = 'ABS' THEN
563 HXT_UTIL.DEBUG('Sending Time at: rate:'||TO_CHAR(l_rate)||
564 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
565 ELSE
566 HXT_UTIL.DEBUG('Sending Time at: rate:'||TO_CHAR(l_rate)||
567 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
568 END IF; -- ABS,OVT,REG
569 END IF; -- IS amount NULL ?
570
571 -- Select any rows where only the hours have changed
572 OPEN l_expired_cur(l_non_rec.parent_id,
573 l_non_rec.project_id,
574 l_non_rec.tas_id,
575 l_non_rec.element_type_id,
576 l_non_rec.hourly_rate,
577 l_non_rec.amount,
578 l_non_rec.ffv_cost_center_id,
579 l_non_rec.job_id);
580
581
582 FETCH l_expired_cur INTO l_expired_rec;
583
584 -- Send adjusting enties to Project Accounting when only the hours have changed
585 IF l_expired_cur%FOUND THEN
586 -- Ignore identical entries, set these to 'D' for dead
587 IF l_non_rec.hours <> l_expired_rec.hours THEN
588 HXT_UTIL.DEBUG('This transaction is an adjustment for '
589 ||TO_CHAR(l_non_rec.hours - l_expired_rec.hours)||' hours');
590 l_retcode := hxt_pa_user_exits.p_a_interface
591 (l_non_rec.hours - l_expired_rec.hours,
592 l_rate,
593 l_premium_amount, --SIR#5
594 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
595 i_ending_date,
596 i_employee_number,
597 l_non_rec.emp_cat_description,
598 l_non_rec.element_type_id, --SIR162
599 l_non_rec.name,
600 l_non_rec.organization_id,
601 l_non_rec.date_worked,
602 l_non_rec.det_effective_start,
603 l_non_rec.det_effective_end,
604 l_non_rec.element_name,
605 l_non_rec.pay_basis,
606 l_non_rec.id,
607 l_non_rec.hxt_earning_category,
608 TRUE,
609 l_standard_rate,
610 l_non_rec.project_id,
611 l_non_rec.tas_id,
612 l_non_rec.segment1,
613 l_non_rec.task_number,
614 l_non_rec.project_name,
615 l_non_rec.task_name,
616 l_non_rec.assignment_id,
617 l_non_rec.ffv_cost_center_id,
618 l_non_rec.job_id,
619 o_location,
620 o_error_text,
621 o_system_text);
622 IF l_retcode = 1 THEN
623 CLOSE l_expired_cur;
624 RAISE l_adjusting_error;
625 END IF;
626
627 -- Update the expired (now adjusted) row so we don't pick it up again.
628 UPDATE hxt_det_hours_worked_f
629 SET pa_status = 'A'
630 WHERE rowid = l_expired_rec.row_id;
631
632 ELSE
633
634 -- Update expired rows matching exactly to 'D' for dead
635 -- No new identical PA transaction will be sent because one already exists in PA
636 -- The retro detail row replacing this one will be set to 'C' complete outside of the loops
637
638 UPDATE hxt_det_hours_worked_f
639 SET pa_status = 'D'
640 WHERE rowid = l_expired_rec.row_id;
641
642 END IF; -- identical entry
643
644 CLOSE l_expired_cur;
645
646 -- Send a new retro transaction to Project Accounting because vital data has changed
647 ELSE
648
649 CLOSE l_expired_cur;
650 -- Pass a normal entry to Project Accounting for the retro rows
651
652 HXT_UTIL.DEBUG('New transaction '||TO_CHAR(l_non_rec.hours)||' hours'); --HXT115
653 l_retcode := hxt_pa_user_exits.p_a_interface
654 (l_non_rec.hours,
655 l_rate,
656 l_premium_amount, --SIR#5
657 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
658 i_ending_date,
659 i_employee_number,
660 l_non_rec.emp_cat_description,
661 l_non_rec.element_type_id, --SIR162
662 l_non_rec.name,
663 l_non_rec.organization_id,
664 l_non_rec.date_worked,
665 l_non_rec.det_effective_start,
666 l_non_rec.det_effective_end,
667 l_non_rec.element_name,
668 l_non_rec.pay_basis,
669 l_non_rec.id,
670 l_non_rec.hxt_earning_category,
671 TRUE,
672 l_standard_rate,
673 l_non_rec.project_id,
674 l_non_rec.tas_id,
675 l_non_rec.segment1,
676 l_non_rec.task_number,
677 l_non_rec.project_name,
678 l_non_rec.task_name,
679 l_non_rec.assignment_id,
680 l_non_rec.ffv_cost_center_id,
681 l_non_rec.job_id,
682 o_location,
683 o_error_text,
684 o_system_text);
685 IF l_retcode = 1 THEN
686 RAISE l_retro_error;
687 END IF;
688 END IF; -- Adjusing entry or correcting entry
689 -- End Base Hours Processing
690 ELSE
691 IF l_non_rec.hxt_premium_type = 'FACTOR' THEN
692 -- Use the manually entered multiple when one exists
693 -- else, use the multiple from the element descriptive flex
694 IF l_non_rec.rate_multiple IS NOT NULL THEN
695 l_rate := l_rate * l_non_rec.rate_multiple;
696 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.rate_multiple)
697 ||'Sending Premium FACTOR/manual multiple rate:'||
698 TO_CHAR(l_rate)||' '||l_non_rec.element_name
699 ||','||' hours:'||TO_CHAR(l_non_rec.hours));
700 ELSE
701 -- When the element flex value is used,
702 -- Factor premiums are calculated by multiplying the (rate x (premium - 1) x hours)
703 l_rate := l_rate * (l_non_rec.hxt_premium_amount - 1);
704 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.hxt_premium_amount)
705 ||'Sending Premium FACTOR/element premium rate:'||
706 TO_CHAR(l_rate)||' '||l_non_rec.element_name||','
707 ||' hours:'||TO_CHAR(l_non_rec.hours));
708 END IF;
709 l_premium_hours := l_non_rec.hours;
710 l_premium_amount := NULL;
711 -- Rate per hour premiums are the (rate x hours)
712 ELSIF l_non_rec.hxt_premium_type = 'RATE' THEN
713 -- Use the Hourly Rate(override rate) entered by the user,
714 -- if one has been entered
715 IF l_non_rec.hourly_rate IS NOT NULL THEN
716 l_rate := l_non_rec.hourly_rate;
717 HXT_UTIL.DEBUG('Sending Premium RATE/element premium rate:'
718 ||TO_CHAR(l_rate)||
719 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
720 -- Use the rate entered on the Pay Element flex segment
721 ELSE
722 l_rate := l_non_rec.hxt_premium_amount;
723 HXT_UTIL.DEBUG('Sending Premium /element premium rate:'||TO_CHAR(l_rate)||
724 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
725 END IF;
726 l_premium_hours := l_non_rec.hours;
727 l_premium_amount := NULL;
728 -- FIXED amount premium
729 ELSE
730 -- If no amount was entered, Assign the premium attached to the Pay Element
731 IF l_non_rec.amount IS NULL THEN
732 l_premium_amount := l_non_rec.hxt_premium_amount;
733 HXT_UTIL.DEBUG('Sending Premium Flat/element premium amount:'
734 ||TO_CHAR(l_premium_amount)||
735 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
736
737 --Else, take the override premium amount entered on the timecard
738 ELSE
739 l_premium_amount := l_non_rec.amount;
740 HXT_UTIL.DEBUG('Sending Premium Flat amount entered on timecard, amount:'
741 ||TO_CHAR(l_premium_amount)||
742 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
743
744 END IF;
745 l_premium_hours := NULL;
746 l_rate := NULL;
747 END IF; -- premium calculations
748
749 --Select any rows where only the hours have changed
750 OPEN l_expired_cur(l_non_rec.parent_id,
751 l_non_rec.project_id,
752 l_non_rec.tas_id,
753 l_non_rec.element_type_id,
754 l_non_rec.hourly_rate,
755 l_non_rec.amount,
756 l_non_rec.ffv_cost_center_id,
757 l_non_rec.job_id);
758
759 FETCH l_expired_cur INTO l_expired_rec;
760
761 --Send adjusting enties to Project Accounting
762 IF l_expired_cur%FOUND THEN
763 -- pickup changed hours entries
764 IF NVL(l_non_rec.hours,-1) <> NVL(l_expired_rec.hours,-1) THEN
765
766 HXT_UTIL.DEBUG('This is an adjusting entry for '
767 ||to_char(l_non_rec.hours - l_expired_rec.hours)||' hours');
768 l_retcode := hxt_pa_user_exits.p_a_interface
769 (l_non_rec.hours - l_expired_rec.hours,
770 l_rate,
771 l_premium_amount,
772 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
773 i_ending_date,
774 i_employee_number,
775 l_non_rec.emp_cat_description,
776 l_non_rec.element_type_id, --SIR162
777 l_non_rec.name,
778 l_non_rec.organization_id,
779 l_non_rec.date_worked,
780 l_non_rec.det_effective_start,
781 l_non_rec.det_effective_end,
782 l_non_rec.element_name,
783 l_non_rec.pay_basis,
784 l_non_rec.id,
785 l_non_rec.hxt_earning_category,
786 TRUE,
787 l_standard_rate,
788 l_non_rec.project_id,
789 l_non_rec.tas_id,
790 l_non_rec.segment1,
791 l_non_rec.task_number,
792 l_non_rec.project_name,
793 l_non_rec.task_name,
794 l_non_rec.assignment_id,
795 l_non_rec.ffv_cost_center_id,
796 l_non_rec.job_id,
797 o_location,
798 o_error_text,
799 o_system_text);
800 IF l_retcode = 1 THEN
801 CLOSE l_expired_cur;
802 RAISE l_adjusting_error;
803 END IF;
804
805 -- Update the expired (now adjusted) row so we don't pick it up again.
806 UPDATE hxt_det_hours_worked_f
807 SET pa_status = 'A'
808 WHERE rowid = l_expired_rec.row_id;
809
810 ELSE
811 -- Update expired rows matching exactly to 'D' for dead
812 -- No new PA transaction will be sent because one already exists
813 -- The retro detail row replacing this one will be set to 'C' outside of the loops
814
815 UPDATE hxt_det_hours_worked_f
816 SET pa_status = 'D'
817 WHERE rowid = l_expired_rec.row_id;
818
819 END IF; -- identical hours entry
820
821 CLOSE l_expired_cur;
822
823 --Send a new retro transaction to Project Accounting because vital data has changed
824 ELSE
825
826 CLOSE l_expired_cur;
827 HXT_UTIL.DEBUG('New transaction '||TO_CHAR(l_non_rec.hours)||' hours');
828 l_retcode := hxt_pa_user_exits.p_a_interface
829 (l_premium_hours,
830 l_rate,
831 l_premium_amount,
832 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
833 i_ending_date,
834 i_employee_number,
835 l_non_rec.emp_cat_description,
836 l_non_rec.element_type_id, --SIR162
837 l_non_rec.name,
838 l_non_rec.organization_id,
839 l_non_rec.date_worked,
840 l_non_rec.det_effective_start,
841 l_non_rec.det_effective_end,
842 l_non_rec.element_name,
843 l_non_rec.pay_basis,
844 l_non_rec.id,
845 l_non_rec.hxt_earning_category,
846 TRUE,
847 l_standard_rate,
848 l_non_rec.project_id,
849 l_non_rec.tas_id,
850 l_non_rec.segment1,
851 l_non_rec.task_number,
852 l_non_rec.project_name,
853 l_non_rec.task_name,
854 l_non_rec.assignment_id,
855 l_non_rec.ffv_cost_center_id,
856 l_non_rec.job_id,
857 o_location,
858 o_error_text,
859 o_system_text);
860
861 IF l_retcode = 1 THEN
862 RAISE l_retro_error;
863 END IF;
864
865 END IF; -- adjust existing or add new retro row
866 END IF; -- end base or premium hours transfer
867 END LOOP; -- detail loop
868
869 -- Loop to pickup any detail rows still in a Complete Status (pa_status = 'C')
870 -- Send a backout tranaction to Project Accounting (- hours) for these rows
871 -- A new row representing the changes to these was inserted above as a retro row
872 -- The backed out rows will have their status set to 'B' to prevent any future processing.
873 -- Base hours and Premium hours are processed separately
874
875 FOR l_backout_rec IN l_backout_cur(l_sum_rec.id) LOOP
876
877 -- Calculate an houly rate for the salary basis
878 IF l_backout_rec.pay_basis = 'ANNUAL' THEN
879 l_rate := l_backout_rec.proposed_salary / l_hours_per_year;
880 ELSIF l_backout_rec.pay_basis = 'MONTHLY' THEN
881 l_rate := (l_backout_rec.proposed_salary * 12) / l_hours_per_year;
882 ELSIF l_backout_rec.pay_basis = 'PERIOD' THEN
883 l_rate := (l_backout_rec.proposed_salary * i_annual_pay_periods) / l_hours_per_year;
884 ELSE -- 'HOURLY'
885 l_rate := l_backout_rec.proposed_salary;
886 END IF;
887 l_standard_rate := l_rate;
888
889 -- Take the override rate when one exists
890 IF l_backout_rec.hourly_rate IS NOT NULL THEN
891 l_rate := l_backout_rec.hourly_rate;
892 END IF;
893
894 -- Process Base Hours
895 IF l_backout_rec.hxt_earning_category IN ('ABS','OVT','REG') THEN
896 -- BEGIN SIR#5
897 -- Handle Flat Amounts on Base Hours Types
898 IF l_backout_rec.amount IS NOT NULL THEN
899 l_backout_amount := l_backout_rec.amount * -1;
900 HXT_UTIL.DEBUG('Sending backout for amount entered on timecard, amount:'
901 ||TO_CHAR(l_backout_amount)||
902 ' '||l_backout_rec.element_name||','||' hours:'||TO_CHAR(l_backout_rec.hours));
903 ELSE
904 l_backout_amount := NULL;
905 -- Calculate rate per hour for overtime using the available premium types
906 IF l_backout_rec.hxt_earning_category = 'OVT' THEN
907 IF l_backout_rec.hxt_premium_type = 'FACTOR' THEN
908 -- Use the manually entered multiple when one exists
909 -- else, use the multiple from the element descriptive flex
910 IF l_backout_rec.rate_multiple IS NOT NULL THEN
911 l_rate := l_rate * l_backout_rec.rate_multiple;
912 ELSE
913 l_rate := l_rate * l_backout_rec.hxt_premium_amount;
914 END IF;
915 ELSIF l_backout_rec.hxt_premium_type = 'RATE' THEN
916 l_rate := l_backout_rec.hxt_premium_amount;
917 ELSE -- FIXED amount per day
918 l_rate := l_backout_rec.hxt_premium_amount / l_backout_rec.hours;
919 END IF;
920 END IF;
921 HXT_UTIL.DEBUG('Sending backout for '||TO_CHAR(l_backout_rec.hours)||' hours');
922 END IF; -- IS AMOUNT NULL? SIR#5
923
924 l_retcode := hxt_pa_user_exits.p_a_interface
925 (l_backout_rec.hours * -1,
926 l_rate,
927 l_backout_amount, --SIR#5
928 l_backout_rec.hxt_earning_category||l_backout_rec.hxt_premium_type,
929 i_ending_date,
930 i_employee_number,
931 l_backout_rec.emp_cat_description,
932 l_backout_rec.element_type_id, --SIR162
933 l_backout_rec.name,
934 l_backout_rec.organization_id,
935 l_backout_rec.date_worked,
936 l_backout_rec.det_effective_start,
937 l_backout_rec.det_effective_end,
938 l_backout_rec.element_name,
939 l_backout_rec.pay_basis,
940 l_backout_rec.id,
941 l_backout_rec.hxt_earning_category,
942 TRUE,
943 l_standard_rate,
944 l_backout_rec.project_id,
945 l_backout_rec.tas_id,
946 l_backout_rec.segment1,
947 l_backout_rec.task_number,
948 l_backout_rec.project_name,
949 l_backout_rec.task_name,
950 l_backout_rec.assignment_id,
951 l_backout_rec.ffv_cost_center_id,
952 l_backout_rec.job_id,
953 o_location,
954 o_error_text,
955 o_system_text);
956 IF l_retcode = 1 THEN
957 RAISE l_backout_error;
958 END IF;
959 ELSE --process premium hours
960 IF l_backout_rec.hxt_premium_type = 'FACTOR' THEN
961 -- Use the manually entered multiple when one exists
962 -- else, use the multiple from the element descriptive flex
963 IF l_backout_rec.rate_multiple IS NOT NULL THEN
964 l_rate := l_rate * l_backout_rec.rate_multiple;
965 ELSE
966 -- When the element flex value is used,
967 -- Factor premiums are calculated by multiplying the (rate x (premium - 1) x hours)
968 l_rate := l_rate * (l_backout_rec.hxt_premium_amount - 1);
969 END IF;
970 l_backout_hours := (l_backout_rec.hours * -1);
971 l_backout_amount := NULL;
972 -- Rate per hour premiums are the (rate x hours)
973 ELSIF l_backout_rec.hxt_premium_type = 'RATE' THEN
974 -- Use the Hourly Rate(override rate) entered by the user, if one has been entered
975 IF l_backout_rec.hourly_rate IS NOT NULL THEN
976 l_rate := l_backout_rec.hourly_rate;
977 -- Use the rate entered on the Pay Element flex segment
978 ELSE
979 l_rate := l_backout_rec.hxt_premium_amount;
980 END IF;
981 l_backout_hours := (l_backout_rec.hours * -1);
982 l_backout_amount := NULL;
983 -- FIXED amount premium
984 ELSE
985 --If no amount was entered, Assign the premium attached to the Pay Element
986 IF l_backout_rec.amount IS NULL THEN
987 l_backout_amount := l_backout_rec.hxt_premium_amount;
988 -- Else, take the override premium amount entered on the timecard
989 ELSE
990 l_backout_amount := (l_backout_rec.amount * -1);
991
992 END IF;
993 l_backout_hours := NULL;
994 l_rate := NULL;
995 END IF; -- premium calculations
996
997 HXT_UTIL.DEBUG('Sending backout for '||TO_CHAR(l_backout_hours)||' hours');
998 HXT_UTIL.DEBUG('Sending backout for '||TO_CHAR(l_backout_amount)||' amount');
999 l_retcode := hxt_pa_user_exits.p_a_interface
1000 (l_backout_hours,
1001 l_rate,
1002 l_backout_amount,
1003 l_backout_rec.hxt_earning_category||l_backout_rec.hxt_premium_type,
1004 i_ending_date,
1005 i_employee_number,
1006 l_backout_rec.emp_cat_description,
1007 l_backout_rec.element_type_id, --SIR162
1008 l_backout_rec.name,
1009 l_backout_rec.organization_id,
1010 l_backout_rec.date_worked,
1011 l_backout_rec.det_effective_start,
1012 l_backout_rec.det_effective_end,
1013 l_backout_rec.element_name,
1014 l_backout_rec.pay_basis,
1015 l_backout_rec.id,
1016 l_backout_rec.hxt_earning_category,
1017 TRUE,
1018 l_standard_rate,
1019 l_backout_rec.project_id,
1020 l_backout_rec.tas_id,
1021 l_backout_rec.segment1,
1022 l_backout_rec.task_number,
1023 l_backout_rec.project_name,
1024 l_backout_rec.task_name,
1025 l_backout_rec.assignment_id,
1026 l_backout_rec.ffv_cost_center_id,
1027 l_backout_rec.job_id,
1028 o_location,
1029 o_error_text,
1030 o_system_text);
1031 IF l_retcode = 1 THEN
1032 RAISE l_backout_error;
1033 END IF;
1034 END IF; -- base or premium hours
1035
1036 UPDATE hxt_det_hours_worked_f
1037 SET pa_status = 'B'
1038 WHERE rowid = l_backout_rec.row_id;
1039
1040 END LOOP; -- backout transactions
1041 END LOOP; -- summary loop
1042 RETURN 0;
1043
1044 EXCEPTION
1045 WHEN l_transfer_error THEN
1046 RETURN 1;
1047 WHEN l_adjusting_error THEN
1048 FND_MESSAGE.SET_NAME('HXT','HXT_39461_SEND_ADJ_TRANS');
1049 o_location := FND_MESSAGE.GET;
1050 FND_MESSAGE.CLEAR;
1051 o_system_text := NULL;
1052 RETURN 1;
1053 WHEN l_retro_error THEN
1054 FND_MESSAGE.SET_NAME('HXT','HXT_39462_SEND_RETRO_TRANS');
1055 o_location := FND_MESSAGE.GET;
1056 FND_MESSAGE.CLEAR;
1057 o_system_text := NULL;
1058 RETURN 1;
1059 WHEN l_backout_error THEN
1060 FND_MESSAGE.SET_NAME('HXT','HXT_39463_SEND_BACK_TRANS');
1061 o_location := FND_MESSAGE.GET;
1062 FND_MESSAGE.CLEAR;
1063 o_system_text := NULL;
1064 RETURN 1;
1065 WHEN OTHERS THEN
1066 o_location := 'hxt_retro_pa.retro_non_ewr_transfer' || l_message;
1067 o_error_text := NULL;
1068 o_system_text := SQLERRM;
1069 RETURN 1;
1070 END retro_non_ewr_transfer;
1071
1072 /****************************************************
1073 log_transfer_errors()
1074 Errors are posted to HXT_ERRORS table.
1075 ****************************************************/
1076 FUNCTION log_transfer_errors( i_location IN VARCHAR2,
1077 i_error_text IN VARCHAR2,
1078 i_system_text IN VARCHAR2)RETURN NUMBER IS
1079 BEGIN
1080
1081 HXT_UTIL.DEBUG('g_batch_err_id = ' || TO_CHAR(g_batch_err_id));
1082 HXT_UTIL.DEBUG('g_timecard_err_id = ' || TO_CHAR(g_timecard_err_id));
1083 HXT_UTIL.DEBUG('g_sum_hours_err_id = ' || g_sum_hours_err_id);
1084 HXT_UTIL.DEBUG('g_time_period_err_id = ' || TO_CHAR(g_time_period_err_id));
1085 HXT_UTIL.DEBUG('i_location = ' || i_location);
1086 HXT_UTIL.DEBUG('i_error_text = ' || i_error_text);
1087 HXT_UTIL.DEBUG('i_system_text = ' || i_system_text);
1088
1089 hxt_util.Gen_Error(g_batch_err_id,
1090 g_timecard_err_id,
1091 g_sum_hours_err_id,
1092 g_time_period_err_id,
1093 i_error_text,
1094 i_location,
1095 i_system_text,
1096 g_err_effective_start,
1097 g_err_effective_end,
1098 'ERR');
1099 COMMIT;
1100
1101 RETURN 0;
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 RAISE g_error_log_error;
1105
1106 END log_transfer_errors;
1107
1108 END HXT_RETRO_PA;