[Home] [Help]
PACKAGE BODY: APPS.HXT_TRAN_PA
Source
1 PACKAGE BODY HXT_TRAN_PA AS
2 /* $Header: hxtpa.pkb 120.3.12020000.2 2013/02/27 09:25:21 jnerella ship $ */
3 l_hours_per_year NUMBER(22,5) := TO_NUMBER(fnd_profile.Value('HXT_HOURS_PER_YEAR'));
4 g_debug boolean := hr_utility.debug_enabled;
5 PROCEDURE transfer_timecards( 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 l_retcode NUMBER DEFAULT 0;
10 l_error_text VARCHAR2(240) DEFAULT NULL;
11 l_system_text VARCHAR2(120) DEFAULT NULL;
12 l_location VARCHAR2(120) DEFAULT NULL;
13 l_ewr_error EXCEPTION;
14 l_non_ewr_error EXCEPTION;
15 l_conc_error EXCEPTION;
16 l_no_timecards EXCEPTION;
17 l_no_details EXCEPTION;
18 l_batch_state EXCEPTION;
19 l_conc_error_flag BOOLEAN DEFAULT FALSE;
20 l_any_timecards BOOLEAN DEFAULT FALSE;
21 l_sum_start_date hxt_sum_hours_worked_f.effective_start_date%TYPE;
22 l_sum_end_date hxt_sum_hours_worked_f.effective_end_date%TYPE;
23 l_sum_id hxt_sum_hours_worked_f.id%TYPE;
24
25
26 -- Cursor to select each timecard within
27 -- the payroll/time-period requested
28
29 CURSOR l_timecard_cur(c_payroll_id NUMBER, c_time_period_id NUMBER)IS
30 SELECT tim.id,
31 tim.batch_id,
32 tim.time_period_id,
33 tim.rowid,
34 tim.effective_start_date,
35 tim.effective_end_date,
36 ptp.end_date,
37 ptpt.number_per_fiscal_year,
38 ppbh.batch_status,
39 ppf.employee_number
40 FROM hxt_timecards_x tim,
41 per_time_periods ptp,
42 per_time_period_types ptpt,
43 pay_batch_headers ppbh,
44 per_people_f ppf
45 WHERE tim.payroll_id = c_payroll_id
46 AND ptp.time_period_id BETWEEN NVL(c_time_period_id,1)
47 AND NVL(c_time_period_id,999999999)
48 AND tim.time_period_id = ptp.time_period_id
49 AND ptp.period_type = ptpt.period_type
50 AND tim.batch_id = ppbh.batch_id
51 AND ppbh.batch_status <> 'U'
52 AND ppf.person_id = tim.for_person_id
53 AND ppf.effective_start_date =
54 (select MAX (ppf2.effective_start_date) -- to handle mid pay period changes
55 from per_people_f ppf2
56 where ppf2.person_id = tim.for_person_id
57 and ppf2.effective_end_date >= ptp.START_date
58 and ppf2.effective_start_date <= ptp.END_date)
59 AND EXISTS (SELECT 'Y'
60 FROM hxt_det_hours_worked_x det
61 WHERE det.tim_id = tim.id
62 AND det.pa_status = 'P');
63 --SIR420 START Don't skip rows with errors because errors are deleted in cursor below
64 -- AND NOT EXISTS (SELECT 'X'
65 -- FROM hxt_errors_x err
66 -- WHERE err.tim_id = tim.id
67 -- AND err_type = 'ERR' --HXT11i1
68 -- AND err.hrw_id IS NOT NULL
69 -- AND err.location NOT LIKE 'hxt_tran%'
70 -- AND err.location NOT LIKE 'hxt_pa_user_exits.p_a_%');
71 --SIR420 END
72
73 BEGIN
74 g_debug :=hr_utility.debug_enabled;
75 if g_debug then
76 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',10);
77 end if;
78
79 HXT_UTIL.DEBUG('Selecting Cursor'); --DEBUG ONLY --HXT115
80
81 -- select each timecard within the payroll/time-period
82
83 FOR l_timecard_rec IN l_timecard_cur( i_payroll_id, i_time_period_id) LOOP
84
85 if g_debug then
86 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',20);
87 end if;
88
89 g_err_effective_start := l_timecard_rec.effective_start_date;
90 g_err_effective_end := l_timecard_rec.effective_end_date;
91 g_batch_err_id := l_timecard_rec.batch_id;
92
93 BEGIN --timecard error block
94
95 if g_debug then
96 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',30);
97 end if;
98
99 l_any_timecards := TRUE;
100
101 -- set id's related to each timecard for error messaging
102 g_timecard_err_id := l_timecard_rec.id;
103 g_time_period_err_id := l_timecard_rec.time_period_id;
104
105 if g_debug then
106 hr_utility.trace('l_timecard_rec.id = ' || TO_CHAR(l_timecard_rec.id));
107 hr_utility.trace('l_timecard_rec.batch_id = ' || TO_CHAR(l_timecard_rec.batch_id));
108 hr_utility.trace('l_timecard_rec.time_period_id = ' || TO_CHAR(l_timecard_rec.time_period_id));
109 hr_utility.trace('l_timecard_rec.end_date = ' || TO_CHAR(l_timecard_rec.end_date));
110 end if;
111
112 HXT_UTIL.DEBUG('.'); --DEBUG ONLY
113 HXT_UTIL.DEBUG('l_timecard_rec.id = ' ||
114 TO_CHAR(l_timecard_rec.id)); --DEBUG ONLY
115 HXT_UTIL.DEBUG('l_timecard_rec.batch_id = '
116 || TO_CHAR(l_timecard_rec.batch_id)); --DEBUG ONLY
117 HXT_UTIL.DEBUG('l_timecard_rec.time_period_id = '
118 || TO_CHAR(l_timecard_rec.time_period_id)); --DEBUG ONLY
119 HXT_UTIL.DEBUG('l_timecard_rec.end_date = '
120 || fnd_date.date_to_chardate(l_timecard_rec.end_date)); --DEBUG ONLY
121
122 -- Clean up existing transfer to PA errors for this payroll/period
123 DELETE
124 FROM hxt_errors_f errf
125 WHERE errf.tim_id = l_timecard_rec.id
126 AND (errf.location LIKE 'hxt_tran%'
127 OR errf.location LIKE 'hxt_pa_user_exits.p_a_%');
128 COMMIT;
129
130 /*************************************************
131 -- Effective Wage Rate Not Currently Implemented
132 --
133 -- Call the logic to calculate effective
134 -- wage and transfer effective wage time
135 -- details to Project Accounting.
136 --
137 -- g_sum_hours_err_id := NULL;
138 -- l_retcode := hxt_ewr_pa.calc_effective_wage_rate(l_timecard_rec.id,
139 -- l_timecard_rec.end_date,
140 -- l_timecard_rec.number_per_fiscal_year,
141 -- 'Y',
142 -- l_timecard_rec.employee_number,
143 -- l_sum_start_date,
144 -- l_sum_end_date,
145 -- l_sum_id,
146 -- l_location,
147 -- l_error_text,
148 -- l_system_text);
149 -- Report any errors for EWR and ROLLBACK
150 -- IF l_retcode = 1 THEN
151 -- g_err_effective_start := l_sum_start_date;
152 -- g_err_effective_end := l_sum_end_date;
153 -- g_sum_hours_err_id := l_sum_id;
154 -- RAISE l_ewr_error;
155 -- END IF;
156 ****************************************************/
157
158 -- Call the logic to transfer time
159 -- details to Project Accounting that
160 -- were not included in Effective
161 -- Wage Rate processing.
162
163 if g_debug then
164 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',40);
165 end if;
166
167 l_retcode := non_eff_wage_rate_transfer(l_timecard_rec.id,
168 l_timecard_rec.end_date,
169 l_timecard_rec.number_per_fiscal_year,
170 l_timecard_rec.employee_number,
171 l_location,
172 l_error_text,
173 l_system_text);
174
175 -- Report any errors for non-EWR and ROLLBACK
176 IF l_retcode = 1 THEN
177 if g_debug then
178 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',50);
179 end if;
180 RAISE l_non_ewr_error;
181 END IF;
182 if g_debug then
183 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',60);
184 end if;
185 COMMIT; -- commit data for this timecard
186
187 EXCEPTION
188 -- Effective Wage Rate Errors
189 WHEN l_ewr_error THEN
190 if g_debug then
191 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',70);
192 end if;
193 ROLLBACK;
194 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
195 l_conc_error_flag := TRUE;
196 -- Non Effective Wage Rate Errors
197 WHEN l_non_ewr_error THEN
198 if g_debug then
199 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',80);
200 end if;
201 ROLLBACK;
202 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
203 l_conc_error_flag := TRUE;
204 -- Other errors
205 WHEN OTHERS THEN
206 if g_debug then
207 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',90);
208 end if;
209 ROLLBACK;
210 l_location := 'hxt_tran_pa.transfer_timecards';
211 FND_MESSAGE.SET_NAME('HXT','HXT_39454_PA_XFER_ERROR');
212 l_error_text := FND_MESSAGE.GET;
213 FND_MESSAGE.CLEAR;
214 l_system_text := SQLERRM;
215 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
216 l_conc_error_flag := TRUE;
217 END; -- timecard error block
218 if g_debug then
219 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',100);
220 end if;
221 END LOOP; -- timecard loop
222
223 -- Return an error if any timecards had a problem
224 IF l_conc_error_flag = TRUE THEN
225 if g_debug then
226 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',110);
227 end if;
228 RAISE l_conc_error;
229 END IF;
230
231 -- Return an error if NO timecards exist for this payroll/period
232 IF l_any_timecards = FALSE THEN
233 if g_debug then
234 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',120);
235 end if;
236 RAISE l_no_timecards;
237 END IF;
238 o_ret_code := 0;
239 FND_MESSAGE.SET_NAME('HXT','HXT_39455_DET_XFER_SUCCESS');
240 o_err_buf := FND_MESSAGE.GET;
241 FND_MESSAGE.CLEAR;
242 HXT_UTIL.DEBUG('Time details successfully transferred!');
243 EXCEPTION
244 WHEN l_conc_error THEN
245 if g_debug then
246 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',130);
247 end if;
248 FND_MESSAGE.SET_NAME('HXT','HXT_39456_CHK_TCARD_ERRS');
249 o_err_buf := FND_MESSAGE.GET;
250 FND_MESSAGE.CLEAR;
251 o_ret_code := 2;
252 HXT_UTIL.DEBUG('Check timecards for errors or run timecard report.');
253 RETURN;
254 WHEN l_no_timecards THEN
255 if g_debug then
256 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',140);
257 end if;
258 --HXT111 o_err_buf := 'No timecards located for payroll/period';
259 FND_MESSAGE.SET_NAME('HXT','HXT_39457_NO_TCARD_4_PAY_PRD');
260 o_err_buf := FND_MESSAGE.GET;
261 FND_MESSAGE.CLEAR;
262 o_ret_code := 2;
263 HXT_UTIL.DEBUG('No timecards located for this payroll');
264 RETURN;
265 WHEN l_no_details THEN
266 if g_debug then
267 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',150);
268 end if;
269 --HXT111 o_err_buf := 'No timecard details transferable';
270 FND_MESSAGE.SET_NAME('HXT','HXT_39458_NO_TCARD_DET_XFER');
271 o_err_buf := FND_MESSAGE.GET;
272 FND_MESSAGE.CLEAR;
273 o_ret_code := 0;
274 HXT_UTIL.DEBUG('No timecard details transferable');
275 RETURN;
276 WHEN g_error_log_error THEN
277 if g_debug then
278 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',160);
279 end if;
280 FND_MESSAGE.SET_NAME('HXT','HXT_39459_HXTPA_ERR');
281 o_err_buf := FND_MESSAGE.GET;
282 FND_MESSAGE.CLEAR;
283 HXT_UTIL.DEBUG('Error in hxt_tran_pa.log_transfer_errors.');
284 o_ret_code := 2;
285 RETURN;
286 WHEN OTHERS THEN
287 if g_debug then
288 hr_utility.set_location('HXT_TRAN_PA.transfer_timecards',170);
289 end if;
290 l_location := 'hxt_tran_pa';
291 FND_MESSAGE.SET_NAME('HXT','HXT_39454_PA_XFER_ERROR');
292 l_error_text := FND_MESSAGE.GET;
293 FND_MESSAGE.CLEAR;
294 l_system_text := SQLERRM;
295 l_retcode := log_transfer_errors(l_location, l_error_text, l_system_text);
296 o_ret_code := 2;
297 FND_MESSAGE.SET_NAME('HXT','HXT_39459_HXTPA_ERR');
298 o_err_buf := FND_MESSAGE.GET;
299 FND_MESSAGE.CLEAR;
300 HXT_UTIL.DEBUG('Error processing timecard for PA Transfer');
301 RETURN;
302 END transfer_timecards;
303
304 /***********************************************************************************
305 non_eff_wage_rate_transfer()
306 Processing of transfer to Project Accounting for all Time details not
307 processed as a part of the Effective Wage Rate Logic.
308 ************************************************************************************/
309 FUNCTION non_eff_wage_rate_transfer(i_timecard_id IN NUMBER,
310 i_ending_date IN DATE,
311 i_annual_pay_periods IN NUMBER,
312 i_employee_number IN VARCHAR2,
313 o_location OUT NOCOPY VARCHAR2,
314 o_error_text OUT NOCOPY VARCHAR2,
315 o_system_text OUT NOCOPY VARCHAR2)RETURN NUMBER IS
316 /********************************************************************
317 Cursor to select all detail rows associated with the timecard
318 that are not associated with Effective Wage Rate calculations.
319 Each hourly detail row will be passed to Project Accounting.
320 *********************************************************************/
321 CURSOR l_non_cur(c_timecard_id NUMBER)IS
322 SELECT NVL(pro.proposed_salary_N,0) proposed_salary,
323 ppb.pay_basis,
324 fcl2.meaning emp_cat_description,
325 payd.hxt_earning_category,
326 payd.hxt_premium_type,
327 NVL(payd.hxt_premium_amount,0) hxt_premium_amount,
328 shw.id sum_id,
329 shw.effective_start_date,
330 shw.effective_end_date,
331 det.id,
332 det.hours,
333 det.amount,
334 det.date_worked,
335 det.effective_start_date det_effective_start,
336 det.effective_end_date det_effective_end,
337 det.hourly_rate,
338 det.rate_multiple,
339 det.tas_id,
340 det.ffv_cost_center_id,
341 det.job_id,
342 prj.project_id,
343 prj.name project_name,
344 prj.segment1,
345 task.task_number,
346 task.task_name,
347 org.name,
348 payt.element_name,
349 asg.organization_id,
350 det.element_type_id,
351 asg.assignment_id,
352 asg.assignment_number
353 FROM hxt_det_hours_worked_x det,
354 hxt_sum_hours_worked_x shw,
355 per_assignments_f asg,
356 per_pay_bases ppb,
357 per_pay_proposals pro,
358 hr_organization_units_v org,
359 hr_lookups fcl,
360 hr_lookups fcl2,
361 pay_element_types_f pay,
362 pay_element_types_f_tl payt,
363 hxt_pay_element_types_f_ddf_v payd,
364 pa_projects_all prj,
365 pa_tasks_expend_v task
366 WHERE det.tim_id = c_timecard_id
367 AND det.parent_id = shw.id
368 AND det.pa_status = 'P'
369 AND det.assignment_id = asg.assignment_id
370 AND det.date_worked BETWEEN asg.effective_start_date AND asg.effective_end_date
371 AND asg.pay_basis_id = ppb.pay_basis_id
372 AND asg.organization_id = org.organization_id
373 AND pro.assignment_id = det.assignment_id
374 AND pro.approved = 'Y'
375 AND pro.change_date = (SELECT MAX(pro2.change_date)
376 FROM per_pay_proposals pro2
377 WHERE pro2.assignment_id = det.assignment_id
378 AND pro2.approved = 'Y'
379 AND det.date_worked >= pro2.change_date)
380 AND det.element_type_id = pay.element_type_id
381 AND payt.element_type_id = pay.element_type_id
382 AND payt.language = userenv('LANG')
383 AND pay.element_type_id = payd.element_type_id
384 AND det.date_worked BETWEEN pay.effective_start_date
385 AND pay.effective_end_date
386 AND det.date_worked BETWEEN payd.effective_start_date
387 AND payd.effective_end_date
388 AND payd.hxt_earning_category = fcl.lookup_code
389 AND fcl.lookup_type = 'HXT_EARNING_CATEGORY'
390 AND fcl.application_id = 808
391 AND asg.employment_category = fcl2.lookup_code
392 AND fcl2.lookup_type = 'EMP_CAT'
393 AND fcl2.application_id = 800
394 AND det.project_id = prj.project_id
395 AND det.tas_id = task.task_id(+);
396 --TA36 not needed. declared in FOR loop.l_non_rec l_non_cur%ROWTYPE;
397
398 l_retcode NUMBER DEFAULT 0;
399 l_premium_amount NUMBER(22,5) DEFAULT 0.00000;
400 l_rate NUMBER(22,5) DEFAULT 0.00000;
401 l_standard_rate NUMBER(22,5) DEFAULT 0.00000;
402 l_premium_hours NUMBER(22,5) DEFAULT NULL;
403 l_transfer_error EXCEPTION;
404
405 BEGIN
406 g_debug :=hr_utility.debug_enabled;
407 if g_debug then
408 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',10);
409 end if;
410 HXT_UTIL.DEBUG('Processing NON-EWR time');
411
412 --Process all eligible time detail rows for this timecard
413 FOR l_non_rec IN l_non_cur(i_timecard_id) LOOP
414 if g_debug then
415 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',20);
416 end if;
417 HXT_UTIL.DEBUG(' in the loop');
418 g_err_effective_start := l_non_rec.effective_start_date;
419 g_err_effective_end := l_non_rec.effective_end_date;
420 g_sum_hours_err_id := l_non_rec.sum_id;
421
422 if g_debug then
423 hr_utility.trace('l_non_rec.sum_id = ' || TO_CHAR(l_non_rec.sum_id));
424 hr_utility.trace('l_non_rec.effective_start_date = '||fnd_date.date_to_chardate(l_non_rec.effective_start_date));
425 hr_utility.trace('l_non_rec.effective_end_date = '||fnd_date.date_to_chardate(l_non_rec.effective_end_date));
426 end if;
427 -- Calculate an houly rate for the salary basis
428 IF l_non_rec.pay_basis = 'ANNUAL' THEN
429 if g_debug then
430 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',30);
431 end if;
432 l_rate := l_non_rec.proposed_salary / l_hours_per_year;
433 ELSIF l_non_rec.pay_basis = 'MONTHLY' THEN
434 if g_debug then
435 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',40);
436 end if;
437 l_rate := (l_non_rec.proposed_salary * 12) / l_hours_per_year;
438 ELSIF l_non_rec.pay_basis = 'PERIOD' THEN
439 if g_debug then
440 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',50);
441 end if;
442 l_rate := (l_non_rec.proposed_salary * i_annual_pay_periods) / l_hours_per_year;
443 ELSE -- 'HOURLY'
444 if g_debug then
445 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',60);
446 end if;
447 l_rate := l_non_rec.proposed_salary;
448 END IF;
449 l_standard_rate := l_rate;
450
451 HXT_UTIL.DEBUG('Employee '||i_employee_number|| --HXT115
452 ' timecard '||TO_CHAR(i_timecard_id) ||' assignment id of '||
453 TO_CHAR(l_non_rec.assignment_id));
454 HXT_UTIL.DEBUG('Normal Hourly Rate for '||i_employee_number||' is: '
455 ||TO_CHAR(l_rate));
456 -- Take the override rate when one exists
457 IF l_non_rec.hourly_rate IS NOT NULL THEN
458 if g_debug then
459 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',70);
460 end if;
461 l_rate := l_non_rec.hourly_rate;
462 HXT_UTIL.DEBUG('Using Override Hourly Rate of '||TO_CHAR(l_rate));
463 END IF;
464
465 -- Process Base Hours
466 IF l_non_rec.hxt_earning_category IN ('ABS','OVT','REG') THEN
467 if g_debug then
468 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',80);
469 end if;
470 -- Handle Flat Amounts on Base Hours Types
471 IF l_non_rec.amount IS NOT NULL THEN
472 if g_debug then
473 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',90);
474 end if;
475 l_premium_amount := l_non_rec.amount;
476 HXT_UTIL.DEBUG('Sending Premium Flat amount entered on timecard, amount:'
477 ||TO_CHAR(l_premium_amount)||
478 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
479 ELSE
480 if g_debug then
481 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',100);
482 end if;
483 l_premium_amount := NULL;
484 -- Calculate rate per hour for overtime using the available premium types
485 IF l_non_rec.hxt_earning_category = 'OVT' THEN
486 if g_debug then
487 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',110);
488 end if;
489 IF l_non_rec.hxt_premium_type = 'FACTOR' THEN
490 if g_debug then
491 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',120);
492 end if;
493 -- Use the manually entered multiple when one exists
494 -- else, use the multiple from the element descriptive flex
495 IF l_non_rec.rate_multiple IS NOT NULL THEN
496 if g_debug then
497 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',130);
498 end if;
499 l_rate := l_rate * l_non_rec.rate_multiple;
500 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.rate_multiple)
501 ||'Sending Overtime FACTOR/manual multiple rate:'|| TO_CHAR(l_rate)
502 ||' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
503 ELSE
504 if g_debug then
505 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',140);
506 end if;
507 l_rate := l_rate * l_non_rec.hxt_premium_amount;
508 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.hxt_premium_amount)
509 ||'Sending Overtime FACTOR/element premium rate:'||
510 TO_CHAR(l_rate)||' '||l_non_rec.element_name||','||' hours:'
511 ||TO_CHAR(l_non_rec.hours));
512 END IF;
513 ELSIF l_non_rec.hxt_premium_type = 'RATE' THEN
514 if g_debug then
515 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',150);
516 end if;
517 l_rate := l_non_rec.hxt_premium_amount;
518 HXT_UTIL.DEBUG('Sending Overtime RATE/element premium rate:'||TO_CHAR(l_rate)||
519 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
520 ELSE -- FIXED amount per day
521 if g_debug then
522 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',160);
523 end if;
524 l_rate := l_non_rec.hxt_premium_amount / l_non_rec.hours;
525 HXT_UTIL.DEBUG('Sending Overtime Flat/element premium rate:'||TO_CHAR(l_rate)||
526 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
527 END IF;
528 ELSIF l_non_rec.hxt_earning_category = 'ABS' THEN
529 if g_debug then
530 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',170);
531 end if;
532 HXT_UTIL.DEBUG('Sending Time at: rate:'||TO_CHAR(l_rate)||
533 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
534 ELSE
535 if g_debug then
536 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',180);
537 end if;
538 HXT_UTIL.DEBUG('Sending Time at: rate:'||TO_CHAR(l_rate)|| --HXT115
539 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
540 END IF;
541 END IF; --End Amount NULL? SIR#5
542
543 if g_debug then
544 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',190);
545 end if;
546 l_retcode := hxt_pa_user_exits.p_a_interface
547 (l_non_rec.hours,
548 l_rate,
549 l_premium_amount,
550 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
551 i_ending_date,
552 i_employee_number,
553 l_non_rec.emp_cat_description,
554 l_non_rec.element_type_id,
555 l_non_rec.name,
556 l_non_rec.organization_id,
557 l_non_rec.date_worked,
558 l_non_rec.det_effective_start,
559 l_non_rec.det_effective_end,
560 l_non_rec.element_name,
561 l_non_rec.pay_basis,
562 l_non_rec.id,
563 l_non_rec.hxt_earning_category,
564 FALSE,
565 l_standard_rate,
566 l_non_rec.project_id,
567 l_non_rec.tas_id,
568 l_non_rec.segment1,
569 l_non_rec.task_number,
570 l_non_rec.project_name,
571 l_non_rec.task_name,
572 l_non_rec.assignment_id,
573 l_non_rec.ffv_cost_center_id,
574 l_non_rec.job_id,
575 o_location,
576 o_error_text,
577 o_system_text);
578 IF l_retcode = 1 THEN
579 if g_debug then
580 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',200);
581 end if;
582 RAISE l_transfer_error;
583 END IF;
584 -- End Base Hours Processing
585 ELSE
586 if g_debug then
587 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',210);
588 end if;
589 IF l_non_rec.hxt_premium_type = 'FACTOR' THEN
590 if g_debug then
591 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',220);
592 end if;
593 -- Use the manually entered multiple when one exists
594 -- else, use the multiple from the element descriptive flex
595 IF l_non_rec.rate_multiple IS NOT NULL THEN
596 if g_debug then
597 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',230);
598 end if;
599 l_rate := l_rate * l_non_rec.rate_multiple;
600 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.rate_multiple)
601 ||'Sending Premium FACTOR/manual multiple rate:'||
602 TO_CHAR(l_rate)||' '||l_non_rec.element_name
603 ||','||' hours:'||TO_CHAR(l_non_rec.hours));
604 ELSE
605 if g_debug then
606 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',240);
607 end if;
608 -- When the element flex value is used,
609 -- Factor premiums are calculated by multiplying the (rate x (premium - 1) x hours)
610 l_rate := l_rate * (l_non_rec.hxt_premium_amount - 1);
611 HXT_UTIL.DEBUG(TO_CHAR(l_non_rec.hxt_premium_amount)||
612 'Sending Premium FACTOR/element premium rate:'||
613 TO_CHAR(l_rate)||' '||l_non_rec.element_name||','||' hours:'
614 ||TO_CHAR(l_non_rec.hours));
615 END IF;
616 -- Rate per hour premiums are the (rate x hours)
617 ELSIF l_non_rec.hxt_premium_type = 'RATE' THEN
618 if g_debug then
619 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',250);
620 end if;
621 -- Use the Hourly Rate(override rate) entered by the user, if one has been entered
622 IF l_non_rec.hourly_rate IS NOT NULL THEN
623 if g_debug then
624 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',260);
625 end if;
626 l_rate := l_non_rec.hourly_rate;
627 HXT_UTIL.DEBUG('Sending Premium RATE/element premium rate:'||TO_CHAR(l_rate)||
628 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
629 -- Use the rate entered on the Pay Element flex segment
630 ELSE
631 if g_debug then
632 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',270);
633 end if;
634 l_rate := l_non_rec.hxt_premium_amount;
635 HXT_UTIL.DEBUG('Sending Premium Flat/element premium rate:'||TO_CHAR(l_rate)||
636 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
637 END IF;
638 -- FIXED amount premium
639 ELSE
640 if g_debug then
641 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',280);
642 end if;
643 -- If no amount was entered, Assign the premium attached to the Pay Element
644 IF l_non_rec.amount IS NULL THEN
645 if g_debug then
646 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',290);
647 end if;
648 l_premium_amount := l_non_rec.hxt_premium_amount;
649 HXT_UTIL.DEBUG('Sending Premium Flat/element premium amount:'||TO_CHAR(l_premium_amount)
650 || ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
651 -- Else, take the override premium amount entered on the timecard
652 ELSE
653 if g_debug then
654 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',300);
655 end if;
656 l_premium_amount := l_non_rec.amount;
657 HXT_UTIL.DEBUG('Sending Premium Flat amount entered on timecard, amount:'
658 ||TO_CHAR(l_premium_amount)||
659 ' '||l_non_rec.element_name||','||' hours:'||TO_CHAR(l_non_rec.hours));
660 END IF;
661 if g_debug then
662 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',310);
663 end if;
664 END IF; -- premium calculations
665
666 if g_debug then
667 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',320);
668 end if;
669 l_retcode := hxt_pa_user_exits.p_a_interface
670 (l_non_rec.hours,
671 l_rate,
672 l_premium_amount,
673 l_non_rec.hxt_earning_category||l_non_rec.hxt_premium_type,
674 i_ending_date,
675 i_employee_number,
676 l_non_rec.emp_cat_description,
677 l_non_rec.element_type_id, --SIR162
678 l_non_rec.name,
679 l_non_rec.organization_id,
680 l_non_rec.date_worked,
681 l_non_rec.det_effective_start,
682 l_non_rec.det_effective_end,
683 l_non_rec.element_name,
684 l_non_rec.pay_basis,
685 l_non_rec.id,
686 l_non_rec.hxt_earning_category,
687 FALSE,
688 l_standard_rate,
689 l_non_rec.project_id,
690 l_non_rec.tas_id,
691 l_non_rec.segment1,
692 l_non_rec.task_number,
693 l_non_rec.project_name,
694 l_non_rec.task_name,
695 l_non_rec.assignment_id,
696 l_non_rec.ffv_cost_center_id,
697 l_non_rec.job_id,
698 o_location,
699 o_error_text,
700 o_system_text);
701
702 IF l_retcode = 1 THEN
703 if g_debug then
704 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',330);
705 end if;
706 RAISE l_transfer_error;
707 END IF;
708 END IF; -- end premium hours transfer
709
710 -- Update the detail rows to a completed status
711 if g_debug then
712 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',340);
713 end if;
714 UPDATE hxt_det_hours_worked_f detf
715 SET detf.pa_status = 'C'
716 WHERE detf.rowid = (SELECT det2.rowid
717 FROM hxt_det_hours_worked_x det2
718 WHERE l_non_rec.id = det2.id);
719 END LOOP;
720 if g_debug then
721 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',350);
722 end if;
723 RETURN 0;
724 EXCEPTION
725 WHEN l_transfer_error THEN
726 if g_debug then
727 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',360);
728 end if;
729 RETURN 1;
730 WHEN OTHERS THEN
731 if g_debug then
732 hr_utility.set_location('HXT_TRAN_PA.non_eff_wage_rate_transfer',370);
733 end if;
734 HXT_UTIL.DEBUG('exception in non_eff_wage: '||SQLERRM);
735 o_location := 'hxt_tran_pa.non_eff_wage_rate_transfer';
736 o_error_text := NULL;
737 o_system_text := SQLERRM;
738 RETURN 1;
739 END non_eff_wage_rate_transfer;
740 /****************************************************
741 log_transfer_errors()
742 Errors are posted to HXT_ERRORS table.
743 ****************************************************/
744 FUNCTION log_transfer_errors(i_location IN VARCHAR2,
745 i_error_text IN VARCHAR2,
746 i_system_text IN VARCHAR2)RETURN NUMBER IS
747 BEGIN
748 g_debug :=hr_utility.debug_enabled;
749 if g_debug then
750 hr_utility.set_location('HXT_TRAN_PA.log_transfer_errors',10);
751 end if;
752 HXT_UTIL.DEBUG('g_batch_err_id = ' || TO_CHAR(g_batch_err_id)); --DEBUG ONLY
753 HXT_UTIL.DEBUG('g_timecard_err_id = ' || TO_CHAR(g_timecard_err_id)); --DEBUG ONLY
754 HXT_UTIL.DEBUG('g_sum_hours_err_id = ' || g_sum_hours_err_id); --DEBUG ONLY
755 HXT_UTIL.DEBUG('g_time_period_err_id = ' || TO_CHAR(g_time_period_err_id)); --DEBUG ONLY
756 HXT_UTIL.DEBUG('i_location = ' || i_location); --DEBUG ONLY
757 HXT_UTIL.DEBUG('i_error_text = ' || i_error_text); --DEBUG ONLY
758 HXT_UTIL.DEBUG('i_system_text = ' || i_system_text); --DEBUG ONLY
759 hxt_util.Gen_Error(g_batch_err_id,
760 g_timecard_err_id,
761 g_sum_hours_err_id,
762 g_time_period_err_id,
763 i_error_text,
764 i_location,
765 i_system_text,
766 g_err_effective_start,
767 g_err_effective_end,
768 'ERR'); --HXT11i1
769 COMMIT;
770 if g_debug then
771 hr_utility.set_location('HXT_TRAN_PA.log_transfer_errors',20);
772 end if;
773
774 RETURN 0;
775 EXCEPTION
776 WHEN OTHERS THEN
777 if g_debug then
778 hr_utility.set_location('HXT_TRAN_PA.log_transfer_errors',30);
779 end if;
780 RAISE g_error_log_error;
781 END log_transfer_errors;
782
783 --begin
784
785
786 END HXT_TRAN_PA;