1 PACKAGE BODY HXT_TIMECARD_API AS
2 /* $Header: hxttapi.pkb 120.6.12010000.1 2008/07/25 09:50:46 appldev ship $ */
3
4 /* Begin ER180 - accrual balance*/
5 g_debug boolean := hr_utility.debug_enabled;
6 PROCEDURE obtain_accrual_balance
7 (--HXT11i1 i_employee_number IN VARCHAR2,
8 i_employee_id IN NUMBER, --HXT11i1
9 i_calculation_date IN DATE,
10 i_accrual_plan_name IN VARCHAR2,
11 o_net_accrual OUT NOCOPY NUMBER,
12 o_otm_error OUT NOCOPY VARCHAR2,
13 o_oracle_error OUT NOCOPY VARCHAR2) IS
14
15 CURSOR assignment_cur IS
16 SELECT asg.payroll_id,
17 asg.assignment_number,
18 asg.assignment_id,
19 asg.business_group_id
20 FROM per_assignments_f asg
21 --HXT11i1 per_people_f per
22 WHERE asg.person_id = i_employee_id --HXT11i1
23 --HXT11i1 per.employee_number = i_employee_number
24 --HXT11i1 AND per.person_id = asg.person_id
25 AND asg.assignment_type = 'E' --HXT11i1
26 AND asg.primary_flag = 'Y' --HXT11i1
27 --HXT11i1 AND i_calculation_date between per.effective_start_date
28 --HXT11i1 and per.effective_end_date
29 AND i_calculation_date between asg.effective_start_date
30 and asg.effective_end_date;
31
32 CURSOR accrual_details_cur (p_accrual_plan_name VARCHAR2,
33 p_business_group_id NUMBER) IS
34 SELECT pap.accrual_category,
35 pap.accrual_plan_id
36 FROM pay_accrual_plans pap
37 WHERE pap.accrual_plan_name=p_accrual_plan_name
38 AND pap.business_group_id=p_business_group_id;
39
40
41 l_accrual_category pay_accrual_plans.accrual_category%TYPE := NULL;
42 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE := NULL;
43 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE := NULL;
44 l_payroll_id per_assignments_f.payroll_id%TYPE := NULL;
45 l_assignment_number per_assignments_f.assignment_number%TYPE := NULL;
46 l_assignment_id per_assignments_f.assignment_id%TYPE := NULL;
47 l_business_group_id per_assignments_f.business_group_id%TYPE := NULL;
48 l_net_accrual_amt NUMBER (7,3);
49 l_calculation_date DATE := i_calculation_date;
50 l_display_error VARCHAR2(120);
51 l_oracle_error VARCHAR2(512);
52
53 assignment_not_found EXCEPTION;
54 accrual_not_found EXCEPTION;
55
56 BEGIN
57
58 g_debug :=hr_utility.debug_enabled;
59 if g_debug then
60 hr_utility.set_location('hxt_timecard_api.obtain_accrual_balance',10);
61 end if;
62 OPEN assignment_cur;
63 FETCH assignment_cur
64 INTO l_payroll_id,
65 l_assignment_number,
66 l_assignment_id,
67 l_business_group_id;
68 IF assignment_cur%NOTFOUND THEN
69 if g_debug then
70 hr_utility.set_location('hxt_timecard_api.obtain_accrual_balance',20);
71 end if;
72 CLOSE assignment_cur;
73 RAISE assignment_not_found;
74 END IF;
75 CLOSE assignment_cur;
76
77 OPEN accrual_details_cur(i_accrual_plan_name, l_business_group_id);
78 FETCH accrual_details_cur
79 INTO l_accrual_category,
80 l_accrual_plan_id;
81 IF accrual_details_cur%NOTFOUND THEN
82 if g_debug then
83 hr_utility.set_location('hxt_timecard_api.obtain_accrual_balance',30);
84 end if;
85 CLOSE accrual_details_cur;
86 RAISE accrual_not_found;
87 END IF;
88 CLOSE accrual_details_cur;
89
90 HXT_UTIL.DEBUG('l_calcuation_datet is '|| (l_calculation_date));
91
92 l_net_accrual_amt := pay_us_pto_accrual.get_net_accrual(
93 P_assignment_id => l_assignment_id,
94 P_calculation_date => l_calculation_date,
95 P_plan_id => l_accrual_plan_id,
96 P_plan_category => NULL); -- Do not pass Acc Category (ER180)
97
98 o_net_accrual := l_net_accrual_amt;
99
100 HXT_UTIL.DEBUG('The net accrual amount is '|| TO_CHAR(l_net_accrual_amt));
101 HXT_UTIL.DEBUG('sysdate '|| (sysdate));
102
103 RETURN;
104
105 EXCEPTION
106 WHEN assignment_not_found THEN
107 if g_debug then
108 hr_utility.set_location('hxt_timecard_api.obtain_accrual_balance',40);
109 end if;
110 FND_MESSAGE.SET_NAME('HXT','HXT_39306_ASSIGN_NF');
111 --HXT11iiFND_MESSAGE.SET_TOKEN('EMP_NUMBER',i_employee_number);
112 FND_MESSAGE.SET_TOKEN('EMP_NUMBER',l_assignment_number);
113 l_display_error := FND_MESSAGE.GET;
114 l_oracle_error := SQLERRM;
115 HXT_UTIL.DEBUG(l_display_error);
116 HXT_UTIL.DEBUG(l_oracle_error);
117 o_otm_error := l_display_error;
118 o_oracle_error := l_oracle_error;
119 RETURN;
120 WHEN accrual_not_found THEN
121 if g_debug then
122 hr_utility.set_location('hxt_timecard_api.obtain_accrual_balance',50);
123 end if;
124 FND_MESSAGE.SET_NAME('HXT','HXT_39511_ACCRUAL_PLAN_NF');
125 l_display_error := FND_MESSAGE.GET;
126 l_oracle_error := SQLERRM;
127 HXT_UTIL.DEBUG(l_display_error);
128 HXT_UTIL.DEBUG(l_oracle_error);
129 o_otm_error := l_display_error;
130 o_oracle_error := l_oracle_error;
131 RETURN;
132 WHEN OTHERS THEN
133 if g_debug then
134 hr_utility.set_location('hxt_timecard_api.obtain_accrual_balance',60);
135 end if;
136 FND_MESSAGE.SET_NAME('HXT','HXT_39512_OTH_OAB_ERROR');
137 l_display_error := FND_MESSAGE.GET;
138 l_oracle_error := SQLERRM;
139 HXT_UTIL.DEBUG(l_display_error);
140 HXT_UTIL.DEBUG(l_oracle_error);
141 o_otm_error := l_display_error;
142 o_oracle_error := l_oracle_error;
143 RETURN;
144 END obtain_accrual_balance;
145 -------------------------------------------------------------------------------
146 /* Begin ER180 - accrual balance*/
147 PROCEDURE accrual_plan_name(p_element_type_id IN NUMBER,
148 p_date_worked IN DATE,
149 p_assignment_id IN NUMBER,
150 o_accrual_plan_name OUT NOCOPY VARCHAR2,
151 o_return_code OUT NOCOPY NUMBER,
152 o_otm_error OUT NOCOPY VARCHAR2,
153 o_oracle_error OUT NOCOPY VARCHAR2) IS
154
155 /* -------------------- New Query for Acc plan for an Emp --------------------*/
156
157 CURSOR acc_plan_cur IS
158 SELECT PAP.ACCRUAL_PLAN_NAME
159 FROM PAY_ACCRUAL_PLANS PAP,
160 PAY_ELEMENT_TYPES_F PETF,
161 HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV,
162 PAY_NET_CALCULATION_RULES PNC,
163 PAY_INPUT_VALUES_F PIV
164 WHERE PETF.ELEMENT_TYPE_ID = p_element_type_id
165 AND PETF.ELEMENT_TYPE_ID = ELTV.ELEMENT_TYPE_ID
166 AND ELTV.hxt_earning_category = 'ABS'
167 AND ((PNC.ACCRUAL_PLAN_ID = PAP.ACCRUAL_PLAN_ID)
168 AND ( PNC.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID)
169 AND ( PIV.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID)
170 AND (P_DATE_WORKED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE))
171 AND p_date_worked BETWEEN PETF.EFFECTIVE_START_DATE
172 AND PETF.EFFECTIVE_END_DATE
173 AND p_date_worked BETWEEN ELTV.EFFECTIVE_START_DATE
174 AND ELTV.EFFECTIVE_END_DATE
175 AND EXISTS
176 (SELECT 1
177 FROM PAY_ELEMENT_TYPES_F PETF1,
178 PAY_ELEMENT_CLASSIFICATIONS PEC,
179 PAY_ELEMENT_ENTRIES_F PEEF,
180 PAY_ELEMENT_LINKS_F PELF,
181 PAY_ACCRUAL_PLANS PAP1
182 WHERE
183 PEEF.ASSIGNMENT_ID = p_assignment_id
184 AND PETF1.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
185 -- AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
186 AND PETF1.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
187 AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
188 AND p_date_worked BETWEEN PETF1.EFFECTIVE_START_DATE
189 AND PETF1.EFFECTIVE_END_DATE
190 AND p_date_worked BETWEEN PEEF.EFFECTIVE_START_DATE -- Bug fix for st.date
191 AND PEEF.EFFECTIVE_END_DATE -- and end.date ...
192 AND PETF1.ELEMENT_TYPE_ID = PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID
193 AND PAP1.ACCRUAL_PLAN_NAME=PAP.ACCRUAL_PLAN_NAME);
194
195 /* ------------------- End of New Query for Acc plan for an Emp --------------*/
196
197 l_accrual_plan_name VARCHAR2(80);
198 l_accrual_plan_element_type_id NUMBER;
199 l_display_error VARCHAR2(120);
200 l_oracle_error VARCHAR2(512);
201 l_count NUMBER :=0 ;
202
203 no_element_for_accrual_plan EXCEPTION;
204 employee_not_tied_to_accrual EXCEPTION;
205
206
207 /*Note: for finding the accrual plan name.*/
208
209 BEGIN
210
211 g_debug :=hr_utility.debug_enabled;
212 if g_debug then
213 hr_utility.set_location('hxt_timecard_api.accrual_plan_name',10);
214 end if;
215 o_return_code := 0;
216 o_accrual_plan_name := NULL;
217
218 OPEN acc_plan_cur;
219 LOOP
220 if g_debug then
221 hr_utility.set_location('hxt_timecard_api.accrual_plan_name',20);
222 end if;
223 FETCH acc_plan_cur INTO l_accrual_plan_name;
224 EXIT WHEN acc_plan_cur%NOTFOUND;
225 END LOOP;
226
227 IF acc_plan_cur%ROWCOUNT = 0 THEN
228 if g_debug then
229 hr_utility.set_location('hxt_timecard_api.accrual_plan_name',30);
230 end if;
231 CLOSE acc_plan_cur;
232 o_return_code := 2; -- Element Not Tied to Accrual Plan
233 RETURN;
234 END IF;
235
236 HXT_UTIL.DEBUG('rowcount is:'|| to_char(acc_plan_cur%ROWCOUNT)); --DEBUG
237
238 IF acc_plan_cur%ROWCOUNT > 1 THEN
239 if g_debug then
240 hr_utility.set_location('hxt_timecard_api.accrual_plan_name',40);
241 end if;
242
243 CLOSE acc_plan_cur;
244 o_return_code := 1; -- Too many Accrual Plans linked to the element type
245 RETURN;
246 END IF;
247
248 o_accrual_plan_name := l_accrual_plan_name;
249 o_return_code := 0;
250 RETURN;
251
252 EXCEPTION
253
254 /*WHEN no_element_for_accrual_plan THEN
255 o_return_code := 1;
256 RETURN;
257 WHEN no_data_found THEN
258 o_return_code := 2;-- ER 180 Give a Warning Msg, when an element not tied to
259 o_return_code := 0;-- Accruals...
260 RETURN; */
261
262 WHEN others THEN
263 if g_debug then
264 hr_utility.set_location('hxt_timecard_api.accrual_plan_name',50);
265 end if;
266 FND_MESSAGE.SET_NAME('HXT','HXT_39513_OTH_APN_ERROR');
267 l_display_error := FND_MESSAGE.GET;
268 l_oracle_error := SQLERRM;
269 HXT_UTIL.DEBUG(l_display_error);
270 HXT_UTIL.DEBUG(l_oracle_error);
271 o_otm_error := l_display_error;
272 o_oracle_error := l_oracle_error;
273 o_return_code := 3;
274 RETURN;
275
276 END accrual_plan_name;
277 -------------------------------------------------------------------------------
278 /* Begin ER180 - accrual balance*/
279 PROCEDURE total_accrual_for_week
280 (p_tim_id IN NUMBER
281 ,p_edit_date IN DATE
282 --,HXT11i1 p_empl_number IN VARCHAR2
283 ,p_empl_id IN NUMBER --HXT11i1
284 ,o_tot_hours OUT NOCOPY NUMBER
285 ,o_accrual_plan_name OUT NOCOPY VARCHAR2
286 ,o_return_code OUT NOCOPY NUMBER
287 ,o_otm_error OUT NOCOPY VARCHAR2
288 ,o_oracle_error OUT NOCOPY VARCHAR2
289 ,o_lookup_code OUT NOCOPY VARCHAR2) IS
290
291 Cursor do_accrual_cur is
292 SELECT hours
293 FROM hxt_pay_element_types_f_ddf_v eltv
294 ,pay_element_types_f elt
295 ,PAY_ACCRUAL_PLANS pap
296 ,PAY_NET_CALCULATION_RULES net
297 ,PAY_INPUT_VALUES_F piv
298 ,hxt_sum_hours_worked sm
299 ,per_assignments_f asm
300 WHERE elt.element_type_id = eltv.element_type_id
301 AND eltv.hxt_earning_category = 'ABS'
302 AND sm.date_worked BETWEEN ELT.EFFECTIVE_START_DATE
303 AND ELT.EFFECTIVE_END_DATE
304 AND sm.date_worked BETWEEN ELTV.EFFECTIVE_START_DATE
305 AND ELTV.EFFECTIVE_END_DATE
306 AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
307 AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
308 AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
309 AND sm.element_type_id = elt.element_type_id
310 AND asm.assignment_id = sm.assignment_id
311 AND sm.date_worked between asm.effective_start_date
312 AND asm.effective_end_date
313 AND sm.tim_id = p_tim_id
314 -- Begin ER180, to find accrual plan assigned for an emp.
315 AND PAP.ACCRUAL_PLAN_NAME IN
316 (SELECT PAP1.ACCRUAL_PLAN_NAME
317 FROM PAY_ELEMENT_TYPES_F PETF1,
318 PAY_ELEMENT_CLASSIFICATIONS PEC,
319 PAY_ELEMENT_ENTRIES_F PEEF,
320 PAY_ELEMENT_LINKS_F PELF,
321 PAY_ACCRUAL_PLANS PAP1
322 WHERE PEEF.ASSIGNMENT_ID = sm.assignment_id
323 AND PETF1.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
324 -- AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
325 AND PETF1.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
326 AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
327 AND sm.date_worked BETWEEN PETF1.EFFECTIVE_START_DATE
328 AND PETF1.EFFECTIVE_END_DATE
329 AND sm.date_worked BETWEEN PEEF.EFFECTIVE_START_DATE
330 AND PEEF.EFFECTIVE_END_DATE
331 AND PETF1.ELEMENT_TYPE_ID = PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID);
332 -- End ER180
333
334 CURSOR accrual_cur(p_date_worked hxt_sum_hours_worked.date_worked%TYPE) IS
335 SELECT distinct pap.accrual_plan_name
336 FROM hxt_pay_element_types_f_ddf_v eltv
337 ,pay_element_types_f elt
338 ,PAY_ACCRUAL_PLANS pap
339 ,PAY_NET_CALCULATION_RULES net
340 ,PAY_INPUT_VALUES_F piv
341 ,hxt_sum_hours_worked sm
342 ,per_assignments_f asm
343 ,per_people_f ppl -- ER180 Bug Fix
344 WHERE elt.element_type_id = eltv.element_type_id
345 AND eltv.hxt_earning_category = 'ABS'
346 AND sm.date_worked = p_date_worked
347 AND sm.date_worked BETWEEN ELT.EFFECTIVE_START_DATE
348 AND ELT.EFFECTIVE_END_DATE
349 AND sm.date_worked BETWEEN ELTV.EFFECTIVE_START_DATE
350 AND ELTV.EFFECTIVE_END_DATE
351 AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
352 AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
353 AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
354 AND sm.element_type_id = elt.element_type_id
355 AND asm.assignment_id = sm.assignment_id
356 AND sm.tim_id = p_tim_id
357 AND sm.date_worked between asm.effective_start_date
358 and asm.effective_end_date
359 AND asm.person_id = ppl.person_id
360 -- HXT11i1AND ppl.employee_number = p_empl_number -- ER180 Bug Fix
361 AND ppl.person_id = p_empl_id -- HXT11i1
362 AND sm.date_worked between ppl.effective_start_date-- ER180 Bug Fix
363 and ppl.effective_end_date -- ER180 Bug Fix
364 -- Begin ER180, to find accrual plan assigned for an emp
365 AND PAP.ACCRUAL_PLAN_NAME IN
366 (SELECT PAP1.ACCRUAL_PLAN_NAME
367 FROM PAY_ELEMENT_TYPES_F PETF1
368 ,PAY_ELEMENT_CLASSIFICATIONS PEC
369 ,PAY_ELEMENT_ENTRIES_F PEEF
370 ,PAY_ELEMENT_LINKS_F PELF
371 ,PAY_ACCRUAL_PLANS PAP1
372 WHERE PEEF.ASSIGNMENT_ID = sm.assignment_id
373 AND PETF1.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
377 AND sm.date_worked BETWEEN PETF1.EFFECTIVE_START_DATE
374 -- AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
375 AND PETF1.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
376 AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
378 AND PETF1.EFFECTIVE_END_DATE
379 AND sm.date_worked BETWEEN PEEF.EFFECTIVE_START_DATE
380 AND PEEF.EFFECTIVE_END_DATE
381 AND PETF1.ELEMENT_TYPE_ID = PAP1.ACCRUAL_PLAN_ELEMENT_TYPE_ID);
382 -- End ER180
383
384 Cursor on_timecard_cur(p_accrual_plan_name VARCHAR2) is
385 SELECT sum(sm.hours*(-1)*(net.add_or_subtract))
386 FROM hxt_pay_element_types_f_ddf_v eltv
387 ,pay_element_types_f elt
388 ,PAY_ACCRUAL_PLANS pap
389 ,PAY_NET_CALCULATION_RULES net
390 ,PAY_INPUT_VALUES_F piv
391 ,hxt_sum_hours_worked sm
392 ,per_assignments_f asm
393 WHERE elt.element_type_id = eltv.element_type_id
394 AND eltv.hxt_earning_category = 'ABS'
395 AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
396 AND pap.accrual_plan_name = p_accrual_plan_name
397 AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
398 AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
399 AND sm.element_type_id = elt.element_type_id
400 AND asm.assignment_id = sm.assignment_id
401 AND sm.date_worked between asm.effective_start_date
402 and asm.effective_end_date
403 AND sm.date_worked between piv.effective_start_date
404 and piv.effective_end_date
405 AND sm.tim_id = p_tim_id;
406
407
408 cursor get_max_retro_batch is
409 select max(batch_id) from pay_batch_headers pbh
410 where pbh.batch_status='T'
411 and pbh.batch_id in (select distinct retro_batch_id from hxt_det_hours_worked_f
412 where tim_id=p_tim_id);
413
414
415 cursor chk_retro_batch_status is
416 select batch_id from pay_batch_headers pbh
417 where pbh.batch_status='T'
418 and pbh.batch_id in (select distinct retro_batch_id from hxt_det_hours_worked_f
419 where tim_id=p_tim_id);
420
421
422 cursor chk_original_batch_status is
423 select null from pay_batch_headers pbh
424 where pbh.batch_status='T'
425 and pbh.batch_id in (select distinct batch_id from hxt_timecards_f
426 where id=p_tim_id);
427
428 cursor get_retro_total(p_accrual_plan_name VARCHAR2,p_batch_id number) is
429 SELECT nvl(sum(det.hours*(-1)*(net.add_or_subtract)),0)
430 FROM hxt_pay_element_types_f_ddf_v eltv
431 ,pay_element_types_f elt
432 ,PAY_ACCRUAL_PLANS pap
433 ,PAY_NET_CALCULATION_RULES net
434 ,PAY_INPUT_VALUES_F piv
435 ,hxt_det_hours_worked_f det
436 ,per_assignments_f asm
437 WHERE elt.element_type_id = eltv.element_type_id
438 AND eltv.hxt_earning_category = 'ABS'
439 AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
440 AND pap.accrual_plan_name = p_accrual_plan_name
441 AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
442 AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
443 AND det.element_type_id = elt.element_type_id
444 AND asm.assignment_id = det.assignment_id
445 AND det.date_worked between asm.effective_start_date
446 and asm.effective_end_date
447 AND det.date_worked between piv.effective_start_date
448 and piv.effective_end_date
449 AND det.tim_id = p_tim_id
450 and det.retro_batch_id=p_batch_id;
451
452
453 cursor get_org_total(p_accrual_plan_name VARCHAR2) is
454 SELECT nvl(sum(det.hours*(-1)*(net.add_or_subtract)),0)
455 FROM hxt_pay_element_types_f_ddf_v eltv
456 ,pay_element_types_f elt
457 ,PAY_ACCRUAL_PLANS pap
458 ,PAY_NET_CALCULATION_RULES net
459 ,PAY_INPUT_VALUES_F piv
460 ,hxt_det_hours_worked_f det
461 ,per_assignments_f asm
462 WHERE elt.element_type_id = eltv.element_type_id
463 AND eltv.hxt_earning_category = 'ABS'
464 AND net.ACCRUAL_PLAN_ID = pap.ACCRUAL_PLAN_ID
465 AND pap.accrual_plan_name = p_accrual_plan_name
466 AND net.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
467 AND piv.ELEMENT_TYPE_ID = elt.ELEMENT_TYPE_ID
468 AND det.element_type_id = elt.element_type_id
469 AND asm.assignment_id = det.assignment_id
470 AND det.date_worked between asm.effective_start_date
471 and asm.effective_end_date
472 AND det.date_worked between piv.effective_start_date
473 and piv.effective_end_date
474 AND det.tim_id = p_tim_id
475 and det.retro_batch_id is null;
476
477 cursor get_tc_dates(p_tim_id NUMBER) is
478 SELECT date_worked
479 FROM hxt_det_hours_worked_f det,
480 hxt_pay_element_types_f_ddf_v eltv
481 WHERE det.tim_id=p_tim_id
482 AND eltv.hxt_earning_category = 'ABS'
483 AND det.element_type_id = eltv.element_type_id
484 ORDER BY det.date_worked;
485
486
487
488
489
490 l_hours NUMBER;
491 l_accrual_plan_name VARCHAR2(80);
492 l_net_accrual NUMBER (7,3);
493 l_display_error VARCHAR2(1200);
494 l_oracle_error VARCHAR2(512);
495 v_otm_error VARCHAR2(1200);
496 v_oracle_error VARCHAR2(512);
497 l_batch_id NUMBER;
498 l_old_total NUMBER;
499 l_date_worked hxt_det_hours_worked_f.date_worked%TYPE;
500 l_flag_acc_exceeded NUMBER(1);
501
505 obtain_error EXCEPTION;
502 --no_accrual_plan EXCEPTION;
503 no_summary_rows EXCEPTION;
504 accrual_exceeded EXCEPTION;
506
507
508 BEGIN
509
510 l_flag_acc_exceeded := 0;
511 g_debug :=hr_utility.debug_enabled;
512 if g_debug then
513 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',10);
514 end if;
515 open do_accrual_cur;
516 -- HXT_UTIL.DEBUG('do acc cur :'||to_char(l_hours));
517 fetch do_accrual_cur into l_hours;
518 -- HXT_UTIL.DEBUG('do acc cur :'||to_char(l_hours));
519 if do_accrual_cur%NOTFOUND then
520 if g_debug then
521 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',20);
522 end if;
523 close do_accrual_cur;
524 o_return_code := 0;
525 o_otm_error := NULL;
526 o_oracle_error := NULL;
527 o_lookup_code := NULL;
528 return;
529 end if;
530 close do_accrual_cur;
531
532 FOR tc_rec IN get_tc_dates(p_tim_id) LOOP
533 l_date_worked := tc_rec.date_worked;
534 FOR accrual_rec IN accrual_cur(l_date_worked) LOOP
535 if g_debug then
536 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',30);
537 end if;
538 l_accrual_plan_name := accrual_rec.accrual_plan_name;
539 HXT_UTIL.DEBUG('Acc plan is:'||l_accrual_plan_name);
540 --HXT_UTIL.DEBUG('accrual_rec.element_type_id is '||to_char(accrual_rec.element_type_id));
541
542 open on_timecard_cur(l_accrual_plan_name);
543 fetch on_timecard_cur into l_hours;
544 --HXT_UTIL.DEBUG('timecard cur:'||to_char(l_hours));
545 if on_timecard_cur%NOTFOUND then
546 if g_debug then
547 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',40);
548 end if;
549 close on_timecard_cur;
550 RAISE no_summary_rows;
551 end if;
552 close on_timecard_cur;
553
554 l_batch_id:=0;
555 l_old_total:=0;
556
557 --check if timecard has been retro edited and is transferred to payroll
558
559 open chk_retro_batch_status ;
560 fetch chk_retro_batch_status into l_batch_id;
561
562 if chk_retro_batch_status %notfound then
563
564 -- if timecard has been retro edited but not transferred to payroll
565 -- or it is new timecard
566
567 l_batch_id :=0;
568 open chk_original_batch_status ;
569 fetch chk_original_batch_status into l_batch_id;
570 if chk_original_batch_status %notfound then
571
572 --it is new timecard
573
574 l_batch_id :=0;
575 end if;
576 close chk_original_batch_status ;
577 else
578
579 -- since the retro batch exists get the last batch_id
580
581 open get_max_retro_batch ;
582 fetch get_max_retro_batch into l_batch_id;
583 close get_max_retro_batch ;
584 end if;
585 close chk_retro_batch_status ;
586
587
588
589 if(l_batch_id is not null ) then
590
591 -- get the hours corresponding to retro batch
592
593 open get_retro_total(l_accrual_plan_name,l_batch_id);
594 fetch get_retro_total into l_old_total;
595
596 if(get_retro_total%notfound) then
597 l_old_total:=0;
598 end if;
599 close get_retro_total;
600
601 else
602 -- get the hours corresponding to non retro timecard
603
604 open get_org_total(l_accrual_plan_name);
605 fetch get_org_total into l_old_total;
606 if(get_org_total%notfound) then
607 l_old_total:=0;
608 end if;
609 close get_org_total;
610 end if;
611
612 if g_debug then
613 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',50);
614 end if;
615 HXT_TIMECARD_API.obtain_accrual_balance
616 (--HXT11i1 p_empl_number,
617 p_empl_id --HXT11i1
618 ,l_date_worked
619 ,l_accrual_plan_name
620 ,l_net_accrual
621 ,v_otm_error
622 ,v_oracle_error);
623
624 HXT_UTIL.DEBUG('params for o a b is:'||p_empl_id||
625 ':'||fnd_date.date_to_chardate(l_date_worked)||'Net acc is:'||to_char(l_net_accrual));
626 HXT_UTIL.DEBUG('Erros from ob acc bal :'||v_otm_error||v_oracle_error);
627 if v_otm_error is not null then
628 if g_debug then
629 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',60);
630 end if;
631 raise obtain_error;
632 end if;
633 if (nvl((l_hours-l_old_total),0) > l_net_accrual) then
634 if g_debug then
635 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',70);
636 end if;
637
638 if(l_display_error IS NULL) then
639 if g_debug then
640 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',70.1);
641 end if;
642
646 FND_MESSAGE.SET_TOKEN('ACCHRS', to_char(l_net_accrual));
643 -- CREATE AND ADD THE MESSAGE
644 FND_MESSAGE.SET_NAME('HXT','HXT_39509_ACCRUAL_EXCEEDED');
645 FND_MESSAGE.SET_TOKEN('ACCPLAN', l_accrual_plan_name);
647
648 l_display_error := FND_MESSAGE.GET;
649 else
650 -- IF A MESSAGE HAS BEEN ALREADY ADDED FOR AN ACCRUAL PLAN, DO NOT ADD IT
651 if(instr(l_display_error, l_accrual_plan_name) = 0) then
652 if g_debug then
653 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',70.2);
654 end if;
655 FND_MESSAGE.SET_NAME('HXT','HXT_39509_ACCRUAL_EXCEEDED');
656 FND_MESSAGE.SET_TOKEN('ACCPLAN', l_accrual_plan_name);
657 FND_MESSAGE.SET_TOKEN('ACCHRS', to_char(l_net_accrual));
658 --ADD MESSAGE WITH A NEW LINE
659 l_display_error := l_display_error ||'
660 '|| FND_MESSAGE.GET;
661 end if;
662 end if;
663
664 l_flag_acc_exceeded := 1;
665 --RAISE ERROR ONLY AFTER ADDING VALIDATION MESSAGES FOR ALL THE ACCRUAL PLANS
666 --raise obtain_error;
667 end if;
668 END LOOP;
669 END LOOP;
670
671 if(l_flag_acc_exceeded = 1) then
672 raise accrual_exceeded;
673 end if;
674
675
676
677 o_return_code := 0;
678 o_otm_error := NULL;
679 o_oracle_error := NULL;
680 o_lookup_code := NULL;
681 -- HXT_UTIL.DEBUG('l_accrual_plan_name is '|| (l_accrual_plan_name));
682 -- HXT_UTIL.DEBUG('l_tot_hours is '||to_char(l_tot_hours));
683
684 RETURN;
685
686 EXCEPTION
687 -- WHEN no_accrual_plan THEN
688 -- o_return_code := 1;
689 -- l_display_error := 'Other error from total_accrual_for_week procedure';
690 -- l_oracle_error := SQLERRM;
691 -- HXT_UTIL.DEBUG(l_display_error);
692 -- HXT_UTIL.DEBUG(l_oracle_error);
693 -- o_otm_error := l_display_error;
694 -- o_oracle_error := l_oracle_error;
695 -- o_return_code := 3;
696 -- RETURN;
697
698 WHEN no_summary_rows THEN
699 if g_debug then
700 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',80);
701 end if;
702 FND_MESSAGE.SET_NAME('HXT','HXT_39514_NO_TIMECARD_ROWS');
703 l_display_error := FND_MESSAGE.GET;
704 HXT_UTIL.DEBUG(l_display_error);
705 o_otm_error := l_display_error;
706 o_oracle_error := NULL;
707 o_return_code := 1;
708 o_lookup_code := NULL;
709 RETURN;
710
711 WHEN accrual_exceeded THEN
712 if g_debug then
713 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',90);
714 end if;
715 -- FND_MESSAGE.SET_NAME('HXT','HXT_39509_ACCRUAL_EXCEEDED');
716 -- FND_MESSAGE.SET_TOKEN('ACCPLAN', l_accrual_plan_name);
717 -- FND_MESSAGE.SET_TOKEN('ACCHRS', to_char(l_net_accrual));
718 -- l_display_error := FND_MESSAGE.GET;
719 HXT_UTIL.DEBUG(l_display_error);
720 o_otm_error := l_display_error;
721 o_oracle_error := NULL;
722 o_accrual_plan_name := l_accrual_plan_name;
723 o_return_code := 1;
724 o_lookup_code := 'ACCRUAL_EXCEEDED';
725 RETURN;
726
727 WHEN obtain_error THEN
728 if g_debug then
729 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',100);
730 end if;
731 FND_MESSAGE.SET_NAME('HXT','HXT_39515_TAFW_OAB_ERROR');
732 l_display_error := FND_MESSAGE.GET || ' ' || v_otm_error;
733 l_oracle_error := v_oracle_error;
734 HXT_UTIL.DEBUG(l_display_error);
735 HXT_UTIL.DEBUG(l_oracle_error);
736 o_otm_error := l_display_error;
737 o_oracle_error := l_oracle_error;
738 o_return_code := 1;
739 o_lookup_code := NULL;
740 RETURN;
741
742 WHEN others THEN
743 if g_debug then
744 hr_utility.set_location('hxt_timecard_api.total_accrual_for_week',110);
745 end if;
746 FND_MESSAGE.SET_NAME('HXT','HXT_39516_OTH_TAFW_ERROR');
747 l_display_error := FND_MESSAGE.GET;
748 l_oracle_error := SQLERRM;
749 HXT_UTIL.DEBUG(l_display_error);
750 HXT_UTIL.DEBUG(l_oracle_error);
751 o_otm_error := l_display_error;
752 o_oracle_error := l_oracle_error;
753 o_return_code := 1;
754 o_lookup_code := NULL;
755 RETURN;
756
757 END total_accrual_for_week;
758 /*End ER180 - accrual balance*/
759
760 END HXT_TIMECARD_API;
[Home] [Help]