[Home] [Help]
PACKAGE BODY: APPS.HXT_RETRO_PA
Source
1 PACKAGE BODY HXT_RETRO_PA AS
2 /* $Header: hxtrpa.pkb 120.1.12020000.4 2013/02/27 09:34:40 jnerella 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_expend_v task /*Bug 16391367*/
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_expend_v task /*Bug 16391367*/
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
558 -- Added for Bug 14028853
559
560 HXT_UTIL.DEBUG('l_non_rec.hours ::'||l_non_rec.hours);
561 HXT_UTIL.DEBUG('l_non_rec.hxt_premium_type ::'||l_non_rec.hxt_premium_type);
562 HXT_UTIL.DEBUG('l_rate ::'||l_rate);
563 HXT_UTIL.DEBUG('l_non_rec.hxt_premium_amount ::'||l_non_rec.hxt_premium_amount);
564
565 IF l_non_rec.hours = 0 THEN
566 l_rate := 0;
567 ELSE
568 l_rate := l_non_rec.hxt_premium_amount / l_non_rec.hours;
569 END IF;
570
571 HXT_UTIL.DEBUG('Sending Overtime Flat/element premium rate:'
572 ||TO_CHAR(l_rate)||
573 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
574 END IF;
575 ELSIF l_non_rec.hxt_earning_category = 'ABS' THEN
576 HXT_UTIL.DEBUG('Sending Time at: rate:'||TO_CHAR(l_rate)||
577 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
578 ELSE
579 HXT_UTIL.DEBUG('Sending Time at: rate:'||TO_CHAR(l_rate)||
580 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
581 END IF; -- ABS,OVT,REG
582 END IF; -- IS amount NULL ?
583
584 -- Select any rows where only the hours have changed
585 OPEN l_expired_cur(l_non_rec.parent_id,
586 l_non_rec.project_id,
587 l_non_rec.tas_id,
588 l_non_rec.element_type_id,
589 l_non_rec.hourly_rate,
590 l_non_rec.amount,
591 l_non_rec.ffv_cost_center_id,
592 l_non_rec.job_id);
593
594
595 FETCH l_expired_cur INTO l_expired_rec;
596
597 -- Send adjusting enties to Project Accounting when only the hours have changed
598 IF l_expired_cur%FOUND THEN
599 -- Ignore identical entries, set these to 'D' for dead
600 IF l_non_rec.hours <> l_expired_rec.hours THEN
601 HXT_UTIL.DEBUG('This transaction is an adjustment for '
602 ||TO_CHAR(l_non_rec.hours - l_expired_rec.hours)||' hours');
603 l_retcode := hxt_pa_user_exits.p_a_interface
604 (l_non_rec.hours - l_expired_rec.hours,
605 l_rate,
606 l_premium_amount, --SIR#5
607 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
608 i_ending_date,
609 i_employee_number,
610 l_non_rec.emp_cat_description,
611 l_non_rec.element_type_id, --SIR162
612 l_non_rec.name,
613 l_non_rec.organization_id,
614 l_non_rec.date_worked,
615 l_non_rec.det_effective_start,
616 l_non_rec.det_effective_end,
617 l_non_rec.element_name,
618 l_non_rec.pay_basis,
619 l_non_rec.id,
620 l_non_rec.hxt_earning_category,
621 TRUE,
622 l_standard_rate,
623 l_non_rec.project_id,
624 l_non_rec.tas_id,
625 l_non_rec.segment1,
626 l_non_rec.task_number,
627 l_non_rec.project_name,
628 l_non_rec.task_name,
629 l_non_rec.assignment_id,
630 l_non_rec.ffv_cost_center_id,
631 l_non_rec.job_id,
632 o_location,
633 o_error_text,
634 o_system_text);
635 IF l_retcode = 1 THEN
636 CLOSE l_expired_cur;
637 RAISE l_adjusting_error;
638 END IF;
639
640 -- Update the expired (now adjusted) row so we don't pick it up again.
641 UPDATE hxt_det_hours_worked_f
642 SET pa_status = 'A'
643 WHERE rowid = l_expired_rec.row_id;
644
645 ELSE
646
647 -- Update expired rows matching exactly to 'D' for dead
648 -- No new identical PA transaction will be sent because one already exists in PA
649 -- The retro detail row replacing this one will be set to 'C' complete outside of the loops
650
651 UPDATE hxt_det_hours_worked_f
652 SET pa_status = 'D'
653 WHERE rowid = l_expired_rec.row_id;
654
655 END IF; -- identical entry
656
657 CLOSE l_expired_cur;
658
659 -- Send a new retro transaction to Project Accounting because vital data has changed
660 ELSE
661
662 CLOSE l_expired_cur;
663 -- Pass a normal entry to Project Accounting for the retro rows
664
665 HXT_UTIL.DEBUG('New transaction '||TO_CHAR(l_non_rec.hours)||' hours'); --HXT115
666 l_retcode := hxt_pa_user_exits.p_a_interface
667 (l_non_rec.hours,
668 l_rate,
669 l_premium_amount, --SIR#5
670 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
671 i_ending_date,
672 i_employee_number,
673 l_non_rec.emp_cat_description,
674 l_non_rec.element_type_id, --SIR162
675 l_non_rec.name,
676 l_non_rec.organization_id,
677 l_non_rec.date_worked,
678 l_non_rec.det_effective_start,
679 l_non_rec.det_effective_end,
680 l_non_rec.element_name,
681 l_non_rec.pay_basis,
682 l_non_rec.id,
683 l_non_rec.hxt_earning_category,
684 TRUE,
685 l_standard_rate,
686 l_non_rec.project_id,
687 l_non_rec.tas_id,
688 l_non_rec.segment1,
689 l_non_rec.task_number,
690 l_non_rec.project_name,
691 l_non_rec.task_name,
692 l_non_rec.assignment_id,
693 l_non_rec.ffv_cost_center_id,
694 l_non_rec.job_id,
695 o_location,
696 o_error_text,
697 o_system_text);
698 IF l_retcode = 1 THEN
699 RAISE l_retro_error;
700 END IF;
701 END IF; -- Adjusing entry or correcting entry
702 -- End Base Hours Processing
703 ELSE
704 IF l_non_rec.hxt_premium_type = 'FACTOR' THEN
705 -- Use the manually entered multiple when one exists
706 -- else, use the multiple from the element descriptive flex
707 IF l_non_rec.rate_multiple IS NOT NULL THEN
708 l_rate := l_rate * l_non_rec.rate_multiple;
709 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.rate_multiple)
710 ||'Sending Premium FACTOR/manual multiple rate:'||
711 TO_CHAR(l_rate)||' '||l_non_rec.element_name
712 ||','||' hours:'||TO_CHAR(l_non_rec.hours));
713 ELSE
714 -- When the element flex value is used,
715 -- Factor premiums are calculated by multiplying the (rate x (premium - 1) x hours)
716 l_rate := l_rate * (l_non_rec.hxt_premium_amount - 1);
717 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.hxt_premium_amount)
718 ||'Sending Premium FACTOR/element premium rate:'||
719 TO_CHAR(l_rate)||' '||l_non_rec.element_name||','
720 ||' hours:'||TO_CHAR(l_non_rec.hours));
721 END IF;
722 l_premium_hours := l_non_rec.hours;
723 l_premium_amount := NULL;
724 -- Rate per hour premiums are the (rate x hours)
725 ELSIF l_non_rec.hxt_premium_type = 'RATE' THEN
726 -- Use the Hourly Rate(override rate) entered by the user,
727 -- if one has been entered
728 IF l_non_rec.hourly_rate IS NOT NULL THEN
729 l_rate := l_non_rec.hourly_rate;
730 HXT_UTIL.DEBUG('Sending Premium RATE/element premium rate:'
731 ||TO_CHAR(l_rate)||
732 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
733 -- Use the rate entered on the Pay Element flex segment
734 ELSE
735 l_rate := l_non_rec.hxt_premium_amount;
736 HXT_UTIL.DEBUG('Sending Premium /element premium rate:'||TO_CHAR(l_rate)||
737 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
738 END IF;
739 l_premium_hours := l_non_rec.hours;
740 l_premium_amount := NULL;
741 -- FIXED amount premium
742 ELSE
743 -- If no amount was entered, Assign the premium attached to the Pay Element
744 IF l_non_rec.amount IS NULL THEN
745 l_premium_amount := l_non_rec.hxt_premium_amount;
746 HXT_UTIL.DEBUG('Sending Premium Flat/element premium amount:'
747 ||TO_CHAR(l_premium_amount)||
748 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
749
750 --Else, take the override premium amount entered on the timecard
751 ELSE
752 l_premium_amount := l_non_rec.amount;
753 HXT_UTIL.DEBUG('Sending Premium Flat amount entered on timecard, amount:'
754 ||TO_CHAR(l_premium_amount)||
755 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
756
757 END IF;
758 l_premium_hours := NULL;
759 l_rate := NULL;
760 END IF; -- premium calculations
761
762 --Select any rows where only the hours have changed
763 OPEN l_expired_cur(l_non_rec.parent_id,
764 l_non_rec.project_id,
765 l_non_rec.tas_id,
766 l_non_rec.element_type_id,
767 l_non_rec.hourly_rate,
768 l_non_rec.amount,
769 l_non_rec.ffv_cost_center_id,
770 l_non_rec.job_id);
771
772 FETCH l_expired_cur INTO l_expired_rec;
773
774 --Send adjusting enties to Project Accounting
775 IF l_expired_cur%FOUND THEN
776 -- pickup changed hours entries
777 IF NVL(l_non_rec.hours,-1) <> NVL(l_expired_rec.hours,-1) THEN
778
779 HXT_UTIL.DEBUG('This is an adjusting entry for '
780 ||to_char(l_non_rec.hours - l_expired_rec.hours)||' hours');
781 l_retcode := hxt_pa_user_exits.p_a_interface
782 (l_non_rec.hours - l_expired_rec.hours,
783 l_rate,
784 l_premium_amount,
785 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
786 i_ending_date,
787 i_employee_number,
788 l_non_rec.emp_cat_description,
789 l_non_rec.element_type_id, --SIR162
790 l_non_rec.name,
791 l_non_rec.organization_id,
792 l_non_rec.date_worked,
793 l_non_rec.det_effective_start,
794 l_non_rec.det_effective_end,
795 l_non_rec.element_name,
796 l_non_rec.pay_basis,
797 l_non_rec.id,
798 l_non_rec.hxt_earning_category,
799 TRUE,
800 l_standard_rate,
801 l_non_rec.project_id,
802 l_non_rec.tas_id,
803 l_non_rec.segment1,
804 l_non_rec.task_number,
805 l_non_rec.project_name,
806 l_non_rec.task_name,
807 l_non_rec.assignment_id,
808 l_non_rec.ffv_cost_center_id,
809 l_non_rec.job_id,
810 o_location,
811 o_error_text,
812 o_system_text);
813 IF l_retcode = 1 THEN
814 CLOSE l_expired_cur;
815 RAISE l_adjusting_error;
816 END IF;
817
818 -- Update the expired (now adjusted) row so we don't pick it up again.
819 UPDATE hxt_det_hours_worked_f
820 SET pa_status = 'A'
821 WHERE rowid = l_expired_rec.row_id;
822
823 ELSE
824 -- Update expired rows matching exactly to 'D' for dead
825 -- No new PA transaction will be sent because one already exists
826 -- The retro detail row replacing this one will be set to 'C' outside of the loops
827
828 UPDATE hxt_det_hours_worked_f
829 SET pa_status = 'D'
830 WHERE rowid = l_expired_rec.row_id;
831
832 END IF; -- identical hours entry
833
834 CLOSE l_expired_cur;
835
836 --Send a new retro transaction to Project Accounting because vital data has changed
837 ELSE
838
839 CLOSE l_expired_cur;
840 HXT_UTIL.DEBUG('New transaction '||TO_CHAR(l_non_rec.hours)||' hours');
841 l_retcode := hxt_pa_user_exits.p_a_interface
842 (l_premium_hours,
843 l_rate,
844 l_premium_amount,
845 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
846 i_ending_date,
847 i_employee_number,
848 l_non_rec.emp_cat_description,
849 l_non_rec.element_type_id, --SIR162
850 l_non_rec.name,
851 l_non_rec.organization_id,
852 l_non_rec.date_worked,
853 l_non_rec.det_effective_start,
854 l_non_rec.det_effective_end,
855 l_non_rec.element_name,
856 l_non_rec.pay_basis,
857 l_non_rec.id,
858 l_non_rec.hxt_earning_category,
859 TRUE,
860 l_standard_rate,
861 l_non_rec.project_id,
862 l_non_rec.tas_id,
863 l_non_rec.segment1,
864 l_non_rec.task_number,
865 l_non_rec.project_name,
866 l_non_rec.task_name,
867 l_non_rec.assignment_id,
868 l_non_rec.ffv_cost_center_id,
869 l_non_rec.job_id,
870 o_location,
871 o_error_text,
872 o_system_text);
873
874 IF l_retcode = 1 THEN
875 RAISE l_retro_error;
876 END IF;
877
878 END IF; -- adjust existing or add new retro row
879 END IF; -- end base or premium hours transfer
880 END LOOP; -- detail loop
881
882 -- Loop to pickup any detail rows still in a Complete Status (pa_status = 'C')
883 -- Send a backout tranaction to Project Accounting (- hours) for these rows
884 -- A new row representing the changes to these was inserted above as a retro row
885 -- The backed out rows will have their status set to 'B' to prevent any future processing.
886 -- Base hours and Premium hours are processed separately
887
888 FOR l_backout_rec IN l_backout_cur(l_sum_rec.id) LOOP
889
890 -- Calculate an houly rate for the salary basis
891 IF l_backout_rec.pay_basis = 'ANNUAL' THEN
892 l_rate := l_backout_rec.proposed_salary / l_hours_per_year;
893 ELSIF l_backout_rec.pay_basis = 'MONTHLY' THEN
894 l_rate := (l_backout_rec.proposed_salary * 12) / l_hours_per_year;
895 ELSIF l_backout_rec.pay_basis = 'PERIOD' THEN
896 l_rate := (l_backout_rec.proposed_salary * i_annual_pay_periods) / l_hours_per_year;
897 ELSE -- 'HOURLY'
898 l_rate := l_backout_rec.proposed_salary;
899 END IF;
900 l_standard_rate := l_rate;
901
902 -- Take the override rate when one exists
903 IF l_backout_rec.hourly_rate IS NOT NULL THEN
904 l_rate := l_backout_rec.hourly_rate;
905 END IF;
906
907 -- Process Base Hours
908 IF l_backout_rec.hxt_earning_category IN ('ABS','OVT','REG') THEN
909 -- BEGIN SIR#5
910 -- Handle Flat Amounts on Base Hours Types
911 IF l_backout_rec.amount IS NOT NULL THEN
912 l_backout_amount := l_backout_rec.amount * -1;
913 HXT_UTIL.DEBUG('Sending backout for amount entered on timecard, amount:'
914 ||TO_CHAR(l_backout_amount)||
915 ' '||l_backout_rec.element_name||','||' hours:'||TO_CHAR(l_backout_rec.hours));
916 ELSE
917 l_backout_amount := NULL;
918 -- Calculate rate per hour for overtime using the available premium types
919 IF l_backout_rec.hxt_earning_category = 'OVT' THEN
920 IF l_backout_rec.hxt_premium_type = 'FACTOR' THEN
921 -- Use the manually entered multiple when one exists
922 -- else, use the multiple from the element descriptive flex
923 IF l_backout_rec.rate_multiple IS NOT NULL THEN
924 l_rate := l_rate * l_backout_rec.rate_multiple;
925 ELSE
926 l_rate := l_rate * l_backout_rec.hxt_premium_amount;
927 END IF;
928 ELSIF l_backout_rec.hxt_premium_type = 'RATE' THEN
929 l_rate := l_backout_rec.hxt_premium_amount;
930 ELSE -- FIXED amount per day
931 l_rate := l_backout_rec.hxt_premium_amount / l_backout_rec.hours;
932 END IF;
933 END IF;
934 HXT_UTIL.DEBUG('Sending backout for '||TO_CHAR(l_backout_rec.hours)||' hours');
935 END IF; -- IS AMOUNT NULL? SIR#5
936
937 l_retcode := hxt_pa_user_exits.p_a_interface
938 (l_backout_rec.hours * -1,
939 l_rate,
940 l_backout_amount, --SIR#5
941 l_backout_rec.hxt_earning_category||l_backout_rec.hxt_premium_type,
942 i_ending_date,
943 i_employee_number,
944 l_backout_rec.emp_cat_description,
945 l_backout_rec.element_type_id, --SIR162
946 l_backout_rec.name,
947 l_backout_rec.organization_id,
948 l_backout_rec.date_worked,
949 l_backout_rec.det_effective_start,
950 l_backout_rec.det_effective_end,
951 l_backout_rec.element_name,
952 l_backout_rec.pay_basis,
953 l_backout_rec.id,
954 l_backout_rec.hxt_earning_category,
955 TRUE,
956 l_standard_rate,
957 l_backout_rec.project_id,
958 l_backout_rec.tas_id,
959 l_backout_rec.segment1,
960 l_backout_rec.task_number,
961 l_backout_rec.project_name,
962 l_backout_rec.task_name,
963 l_backout_rec.assignment_id,
964 l_backout_rec.ffv_cost_center_id,
965 l_backout_rec.job_id,
966 o_location,
967 o_error_text,
968 o_system_text);
969 IF l_retcode = 1 THEN
970 RAISE l_backout_error;
971 END IF;
972 ELSE --process premium hours
973 IF l_backout_rec.hxt_premium_type = 'FACTOR' THEN
974 -- Use the manually entered multiple when one exists
975 -- else, use the multiple from the element descriptive flex
976 IF l_backout_rec.rate_multiple IS NOT NULL THEN
977 l_rate := l_rate * l_backout_rec.rate_multiple;
978 ELSE
979 -- When the element flex value is used,
980 -- Factor premiums are calculated by multiplying the (rate x (premium - 1) x hours)
981 l_rate := l_rate * (l_backout_rec.hxt_premium_amount - 1);
982 END IF;
983 l_backout_hours := (l_backout_rec.hours * -1);
984 l_backout_amount := NULL;
985 -- Rate per hour premiums are the (rate x hours)
986 ELSIF l_backout_rec.hxt_premium_type = 'RATE' THEN
987 -- Use the Hourly Rate(override rate) entered by the user, if one has been entered
988 IF l_backout_rec.hourly_rate IS NOT NULL THEN
989 l_rate := l_backout_rec.hourly_rate;
990 -- Use the rate entered on the Pay Element flex segment
991 ELSE
992 l_rate := l_backout_rec.hxt_premium_amount;
993 END IF;
994 l_backout_hours := (l_backout_rec.hours * -1);
995 l_backout_amount := NULL;
996 -- FIXED amount premium
997 ELSE
998 --If no amount was entered, Assign the premium attached to the Pay Element
999 IF l_backout_rec.amount IS NULL THEN
1000 l_backout_amount := l_backout_rec.hxt_premium_amount;
1001 -- Else, take the override premium amount entered on the timecard
1002 ELSE
1003 l_backout_amount := (l_backout_rec.amount * -1);
1004
1005 END IF;
1006 l_backout_hours := NULL;
1007 l_rate := NULL;
1008 END IF; -- premium calculations
1009
1010 HXT_UTIL.DEBUG('Sending backout for '||TO_CHAR(l_backout_hours)||' hours');
1011 HXT_UTIL.DEBUG('Sending backout for '||TO_CHAR(l_backout_amount)||' amount');
1012 l_retcode := hxt_pa_user_exits.p_a_interface
1013 (l_backout_hours,
1014 l_rate,
1015 l_backout_amount,
1016 l_backout_rec.hxt_earning_category||l_backout_rec.hxt_premium_type,
1017 i_ending_date,
1018 i_employee_number,
1019 l_backout_rec.emp_cat_description,
1020 l_backout_rec.element_type_id, --SIR162
1021 l_backout_rec.name,
1022 l_backout_rec.organization_id,
1023 l_backout_rec.date_worked,
1024 l_backout_rec.det_effective_start,
1025 l_backout_rec.det_effective_end,
1026 l_backout_rec.element_name,
1027 l_backout_rec.pay_basis,
1028 l_backout_rec.id,
1029 l_backout_rec.hxt_earning_category,
1030 TRUE,
1031 l_standard_rate,
1032 l_backout_rec.project_id,
1033 l_backout_rec.tas_id,
1034 l_backout_rec.segment1,
1035 l_backout_rec.task_number,
1036 l_backout_rec.project_name,
1037 l_backout_rec.task_name,
1038 l_backout_rec.assignment_id,
1039 l_backout_rec.ffv_cost_center_id,
1040 l_backout_rec.job_id,
1041 o_location,
1042 o_error_text,
1043 o_system_text);
1044 IF l_retcode = 1 THEN
1045 RAISE l_backout_error;
1046 END IF;
1047 END IF; -- base or premium hours
1048
1049 UPDATE hxt_det_hours_worked_f
1050 SET pa_status = 'B'
1051 WHERE rowid = l_backout_rec.row_id;
1052
1053 END LOOP; -- backout transactions
1054 END LOOP; -- summary loop
1055 RETURN 0;
1056
1057 EXCEPTION
1058 WHEN l_transfer_error THEN
1059 RETURN 1;
1060 WHEN l_adjusting_error THEN
1061 FND_MESSAGE.SET_NAME('HXT','HXT_39461_SEND_ADJ_TRANS');
1062 o_location := FND_MESSAGE.GET;
1063 FND_MESSAGE.CLEAR;
1064 o_system_text := NULL;
1065 RETURN 1;
1066 WHEN l_retro_error THEN
1067 FND_MESSAGE.SET_NAME('HXT','HXT_39462_SEND_RETRO_TRANS');
1068 o_location := FND_MESSAGE.GET;
1069 FND_MESSAGE.CLEAR;
1070 o_system_text := NULL;
1071 RETURN 1;
1072 WHEN l_backout_error THEN
1073 FND_MESSAGE.SET_NAME('HXT','HXT_39463_SEND_BACK_TRANS');
1074 o_location := FND_MESSAGE.GET;
1075 FND_MESSAGE.CLEAR;
1076 o_system_text := NULL;
1077 RETURN 1;
1078 WHEN OTHERS THEN
1079 o_location := 'hxt_retro_pa.retro_non_ewr_transfer' || l_message;
1080 o_error_text := NULL;
1081 o_system_text := SQLERRM;
1082 RETURN 1;
1083 END retro_non_ewr_transfer;
1084
1085 /****************************************************
1086 log_transfer_errors()
1087 Errors are posted to HXT_ERRORS table.
1088 ****************************************************/
1089 FUNCTION log_transfer_errors( i_location IN VARCHAR2,
1090 i_error_text IN VARCHAR2,
1091 i_system_text IN VARCHAR2)RETURN NUMBER IS
1092 BEGIN
1093
1094 HXT_UTIL.DEBUG('g_batch_err_id = ' || TO_CHAR(g_batch_err_id));
1095 HXT_UTIL.DEBUG('g_timecard_err_id = ' || TO_CHAR(g_timecard_err_id));
1096 HXT_UTIL.DEBUG('g_sum_hours_err_id = ' || g_sum_hours_err_id);
1097 HXT_UTIL.DEBUG('g_time_period_err_id = ' || TO_CHAR(g_time_period_err_id));
1098 HXT_UTIL.DEBUG('i_location = ' || i_location);
1099 HXT_UTIL.DEBUG('i_error_text = ' || i_error_text);
1100 HXT_UTIL.DEBUG('i_system_text = ' || i_system_text);
1101
1102 hxt_util.Gen_Error(g_batch_err_id,
1103 g_timecard_err_id,
1104 g_sum_hours_err_id,
1105 g_time_period_err_id,
1106 i_error_text,
1107 i_location,
1108 i_system_text,
1109 g_err_effective_start,
1110 g_err_effective_end,
1111 'ERR');
1112 COMMIT;
1113
1114 RETURN 0;
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 RAISE g_error_log_error;
1118
1119 END log_transfer_errors;
1120
1121 END HXT_RETRO_PA;