1 PACKAGE BODY HXT_UTIL AS
2 /* $Header: hxtutl.pkb 120.12.12020000.5 2013/01/28 11:27:38 asrajago ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 --BEGIN HXT11i1
6 FUNCTION GetPersonID(p_TIM_ID NUMBER) RETURN NUMBER; -- ER230
7 FUNCTION GetPayrollID(p_PTP_ID NUMBER) RETURN NUMBER; -- ER230
8 --END HXT11i1
9
10 --BEGIN HXT115
11 Procedure DEBUG(p_string IN VARCHAR2) IS
12 BEGIN
13 g_debug :=hr_utility.debug_enabled;
14 -- Uncomment line below to turn-on OTM package debug messages.
15 if g_debug then
16 hr_utility.trace(p_string);
17 end if;
18 -- NULL;
19 END DEBUG;
20 --END HXT115
21
22 /*----------------------Procedure GEN_ERROR NEWEST--------------------------*/
23 Procedure GEN_ERROR (p_PPB_ID IN NUMBER
24 ,p_TIM_ID IN NUMBER
25 , p_HRW_ID IN NUMBER
26 , p_PTP_ID IN NUMBER
27 , p_ERROR_MSG IN VARCHAR2
28 , p_LOCATION IN VARCHAR2
29 , p_ORA_MSG IN VARCHAR2
30 , p_EFFECTIVE_START_DATE IN DATE --ORA135
31 , p_EFFECTIVE_END_DATE IN DATE --ORA135
32 , p_TYPE IN VARCHAR2) IS --HXT11i1
33 -- Procedure GEN_ERROR
34 --
35 -- Purpose
36 -- Insert record in HXT_ERRORS table when an error is found regarding a
37 -- Timecard, summary or detailed Hours Worked or Pay Period record.
38 --
39 -- Returns
40 -- 0 - No errors occurred
41 -- 1 - Warnings occurred
42 -- 2 - Errors occurred
43 --
44 -- Arguments
45 -- p_TIM_ID - The source of the error is the BATCH record
46 -- p_TIM_ID - The source of the error is the TIMECARD record
47 -- p_HRW_ID - The source of the error is the Hours Worked record
48 -- p_PTP_ID - The source of the error is the TIME PERIOD RECORD.
49 --*** p_TYPE - will = 'NEW' until p_type is deleted from the HXT_ERRORS table *********
50 -- p_ERROR_MSG - the error message to show the user
51 -- p_LOCATION - the Procedure or Source
52 -- p_ORA_MSG - the ORACLE error number and message if any
53 --
54 -- Local Variables
55 l_CREATION_DATE DATE; --- the date time of the error
56 l_CREATED_BY NUMBER; --- the user logged in when the error occurred
57 l_EXCEP_seqno NUMBER; --- the next sequence number for new error record
58 l_ERROR_MSG VARCHAR2(240);
59 l_error VARCHAR2(240);
60 l_person_id NUMBER;
61 l_payroll_id NUMBER;
62
63 --
64 --
65 BEGIN
66 --
67 SELECT hxt_seqno.nextval
68 INTO l_EXCEP_seqno
69 FROM dual;
70 l_CREATED_BY := FND_GLOBAL.user_id;
71
72 l_person_id := GetPersonID(p_TIM_ID);
73 l_payroll_id := GetPayrollID(p_PTP_ID);
74
75 BEGIN
76
77 IF p_ERROR_MSG IS NULL THEN
78 l_ERROR_MSG := FND_MESSAGE.GET;
79 FND_MESSAGE.CLEAR;
80 ELSE
81 l_ERROR_MSG := p_ERROR_MSG;
82 END IF;
83
84 insert into hxt_errors_f(id, error_msg, creation_date, location,--ORA135
85 created_by, err_type, PPB_ID, TIM_ID, HRW_ID, PTP_ID, ora_message
86 ,EFFECTIVE_START_DATE
87 ,EFFECTIVE_END_DATE
88 ,PERSON_ID
89 ,PAYROLL_ID
90 )
91 values(l_EXCEP_seqno,
92 substr(nvl(l_ERROR_MSG,'NOTPROVIDED'),1,239),
93 sysdate,
94 substr(nvl(p_LOCATION,'NOTPROVIDED'),1,119),
95 nvl(l_CREATED_BY,-1),
96 p_TYPE, p_PPB_id, p_TIM_id, p_HRW_ID, p_PTP_ID,
97 substr(p_ORA_MSG,1,119)
98 ,nvl(p_EFFECTIVE_START_DATE,sysdate)
99 ,nvl(p_EFFECTIVE_END_DATE,hr_general.end_of_time)
100 ,l_Person_ID
101 ,l_Payroll_ID
102 ); --FORMS60
103
104
105 EXCEPTION
106 WHEN others THEN
107 -- Bug 12919783
108 hr_utility.trace(dbms_utility.format_error_backtrace);
109 FND_MESSAGE.SET_NAME('HXT','HXT_39469_ERR_INS_HXT_ERR');
110 l_error := SQLERRM;
111 insert into hxt_errors_f(id, error_msg, creation_date, location,
112 created_by, err_type, PPB_ID, TIM_ID, HRW_ID, PTP_ID, ora_message
113 ,EFFECTIVE_START_DATE
114 ,EFFECTIVE_END_DATE)
115 values(l_EXCEP_seqno,
116 FND_MESSAGE.GET||' '||nls_initcap(substr(p_error_msg,1,100)),
117 sysdate, 'ERROR', 999, 'NEW', 999, 999, 999, 999,
118 l_error
119 ,nvl(p_EFFECTIVE_START_DATE,sysdate)
120 ,nvl(p_EFFECTIVE_END_DATE,hr_general.end_of_time)); --FORMS60
121 FND_MESSAGE.CLEAR;
122 END;
123 END gen_error;
124 --
125 /*-------------------------Procedure GEN_ERROR---------------------------------*/
126 Procedure GEN_ERROR (p_TIM_ID IN NUMBER
127 , p_HRW_ID IN NUMBER
128 , p_PTP_ID IN NUMBER
129 , p_ERROR_MSG IN VARCHAR2
130 , p_LOCATION IN VARCHAR2
131 , p_ORA_MSG IN VARCHAR2
132 , p_EFFECTIVE_START_DATE IN DATE
133 , p_EFFECTIVE_END_DATE IN DATE
134 , p_TYPE IN VARCHAR2) IS
135 -- Procedure GEN_ERROR
136 --
137 -- Purpose
138 -- Insert record in HXT_ERRORS table when an error is found regarding a
139 -- Timecard, summary or detailed Hours Worked or Pay Period record.
140 --
141 -- Returns
142 -- 0 - No errors occurred
143 -- 1 - Warnings occurred
144 -- 2 - Errors occurred
145 --
146 -- Arguments
147 -- p_TIM_ID - The source of the error is the TIMECARD record
148 -- p_HRW_ID - The source of the error is the Hours Worked record
149 -- p_PTP_ID - The source of the error is the TIME PERIOD RECORD.
150 -- p_ERROR_MSG - the error message to show the user
151 -- p_LOCATION - the Procedure or Source
152 -- p_ORA_MSG - the ORACLE error number and message if any
153 --
154 -- Local Variables
155 l_CREATION_DATE DATE; --- the date time of the error
156 l_CREATED_BY NUMBER; --- the user logged in when the error occurred
157 l_EXCEP_seqno NUMBER; --- the next sequence number for new error record
158 l_error VARCHAR2(240);
159 l_ERROR_MSG VARCHAR2(240);
160 l_person_id NUMBER;
161 l_payroll_id NUMBER;
162 --
163 --
164 BEGIN
165 --
166 --
167 SELECT hxt_seqno.nextval
168 INTO l_EXCEP_seqno
169 FROM dual;
170 l_CREATED_BY := FND_GLOBAL.user_id;
171
172 l_person_id := GetPersonID(p_TIM_ID);
173 l_payroll_id := GetPayrollID(p_PTP_ID);
174
175 IF p_ERROR_MSG IS NULL THEN
176 l_ERROR_MSG := FND_MESSAGE.GET;
177 FND_MESSAGE.CLEAR;
178 ELSE
179 l_ERROR_MSG := p_ERROR_MSG;
180 END IF;
181 BEGIN
182 insert into hxt_errors_f(id, error_msg, creation_date, location,--ORA135
183 created_by, err_type, TIM_ID, HRW_ID, PTP_ID, ora_message
184 ,EFFECTIVE_START_DATE
185 ,EFFECTIVE_END_DATE
186 ,PERSON_ID --ER230
187 ,PAYROLL_ID --ER230
188 )
189 values(l_EXCEP_seqno,
190 substr(nvl(l_ERROR_MSG,'NOTPROVIDED'),1,239),
191 sysdate,
192 substr(nvl(p_LOCATION,'NOTPROVIDED'),1,119),
193 nvl(l_CREATED_BY,-1),
194 p_TYPE, p_TIM_id, p_HRW_ID, p_PTP_ID,
195 substr(p_ORA_MSG,1,119)
196 ,nvl(p_EFFECTIVE_START_DATE,sysdate)
197 ,nvl(p_EFFECTIVE_END_DATE,hr_general.end_of_time)
198 ,l_Person_ID
199 ,l_Payroll_ID
200 );
201 EXCEPTION
202 WHEN others THEN
203 -- Bug 12919783
204 hr_utility.trace(dbms_utility.format_error_backtrace);
205 FND_MESSAGE.SET_NAME('HXT','HXT_39469_ERR_INS_HXT_ERR');
206 l_error := SQLERRM;
207 insert into hxt_errors_f(id, error_msg, creation_date, location,
208 created_by, err_type, TIM_ID, HRW_ID, PTP_ID, ora_message
209 ,EFFECTIVE_START_DATE
210 ,EFFECTIVE_END_DATE)
211 values(l_EXCEP_seqno,
212 FND_MESSAGE.GET||' '||nls_initcap(substr(p_error_msg,1,100)),
213 sysdate, 'ERROR', 999, 'NEW', 999, 999, 999,
214 l_error
215 ,nvl(p_EFFECTIVE_START_DATE,sysdate)
216 ,nvl(p_EFFECTIVE_END_DATE,hr_general.end_of_time));
217 FND_MESSAGE.CLEAR;
218 END;
219 END gen_error;
220 --
221 -------------------------------------Procedure autogen_error-------------------------------------
222 --ORA136 removed.
223 ------------------------------------old --PROCEDURE GEN_ERROR-------------------------------------
224 --ORA136 removed.
225 ------------------------------------------PROCEDURE chk_absence------------------------------------
226 PROCEDURE chk_absence(P_assignment_id IN NUMBER,
227 P_period_id IN NUMBER,
228 P_calculation_date IN DATE,
229 P_element_type_id IN NUMBER,
230 P_hours IN NUMBER,
231 P_net_amt OUT NOCOPY NUMBER,
232 P_period_amt OUT NOCOPY NUMBER,
233 P_available_amt OUT NOCOPY NUMBER,
234 P_abs_status OUT NOCOPY NUMBER) IS
235 --
236 -- Procedure chk_absence
237 --
238 -- Purpose
239 -- Check the net accrual for a specific Absenece Element for sufficient hours
240 -- in order to allow person to take hours requested. If enough, then return
241 -- P_abs_status = 0; if not, then return P_abs_status = 1; if NO PTO Accrual Plan,
242 -- then return P_abs_status = 2.
243 --
244 -- Arguments
245 p_PLAN_ID NUMBER;
246 p_PLAN_NAME VARCHAR2(80);
247 p_PLAN_ELEMENT_TYPE_ID NUMBER;
248 p_PLAN_CATEGORY VARCHAR2(30);
249 p_PLAN_CATEGORY_NAME VARCHAR2(80);
250 p_PTO_ELEMENT_TYPE_ID NUMBER;
251 p_PTO_ELEMENT_NAME VARCHAR2(80);
252 p_PTO_INPUT_VALUE_ID NUMBER;
253 p_PTO_INPUT_VALUE_NAME VARCHAR2(30);
254 --
255 -- Local Variables
256 l_amount NUMBER;
257 l_total_period_hrs NUMBER;
258 --
259 -- Get PTO ACCRUAL PLAN for Absence Element
260 --
261 CURSOR pto_plan(p_element_type_id NUMBER) IS
262 select
263 ACCRUAL_PLAN_ID,
264 ACCRUAL_PLAN_NAME,
265 ACCRUAL_PLAN_ELEMENT_TYPE_ID,
266 ACCRUAL_CATEGORY,
267 ACCRUAL_CATEGORY_NAME,
268 PTO_ELEMENT_TYPE_ID,
269 PTO_ELEMENT_NAME,
270 PTO_INPUT_VALUE_ID,
271
272 PTO_INPUT_VALUE_NAME
273 FROM PAY_ACCRUAL_PLANS_V
274 WHERE PTO_ELEMENT_TYPE_ID = p_element_type_id;
275 --
276 -- Total all hours for Absence Element for current pay period
277 --
278 CURSOR hr_amt(p_period_id NUMBER,p_assignement_id NUMBER,p_element_type_id NUMBER) IS
279 select sum(hours)
280 from hxt_timecards tim, hxt_sum_hours_worked hrw
281 where hrw.tim_id = tim.id
282 and tim.time_period_id = p_period_id
283 and hrw.assignment_id = p_assignement_id
284 and hrw.element_type_id = p_element_type_id;
285 BEGIN
286 --
287 -- Initialize OUT variables;
288 P_net_amt := NULL;
289 P_period_amt := NULL;
290 P_abs_status := NULL;
291 P_available_amt := NULL;
292 --
293 -- Get PTO plan for element type passed
294 --
295 open pto_plan(p_element_type_id);
296 --
297 fetch pto_plan into p_PLAN_ID,
298 p_PLAN_NAME,
299 p_PLAN_ELEMENT_TYPE_ID,
300 p_PLAN_CATEGORY,
301 p_PLAN_CATEGORY_NAME,
302 p_PTO_ELEMENT_TYPE_ID,
303 p_PTO_ELEMENT_NAME,
304 p_PTO_INPUT_VALUE_ID,
305 p_PTO_INPUT_VALUE_NAME;
306 --
307 close pto_plan;
308 --
309 IF p_PLAN_ID IS NULL THEN
310 P_abs_status := 2; --- Element does not belong to an Accrual Plan
311 ELSE
312 --
313 -- Get net Accrual plan amount
314 --
315 l_amount := pay_us_pto_accrual.get_net_accrual( P_assignment_id ,
316 P_calculation_date,
317 P_plan_id ,
318 P_plan_category);
319 P_net_amt := l_amount;
320 --
321 -- Get total Absence Element hours for current pay period
322 --
323 open hr_amt(p_period_id,p_assignment_id,p_element_type_id);
324 --
325 fetch hr_amt into l_total_period_hrs;
326 --
327 close hr_amt;
328 --
329 -- Total Absence Element hours for current pay period
330 --
331 P_period_amt := l_total_period_hrs;
332 --
333 IF l_total_period_hrs IS NULL THEN
334 l_total_period_hrs := 0;
335 END IF;
336 l_total_period_hrs := l_total_period_hrs + p_hours;
337 --
338 -- Calculate net Accrual available if all Absence Elements are taken
339 P_available_amt := l_amount - l_total_period_hrs;
340 --
341 -- Check that Hours to be charge to this Accrual plan are available to take
342 --
343 IF l_total_period_hrs <= l_amount THEN
344 P_abs_status := 0;
345 ELSE
346 P_abs_status := 1;
347 END IF;
348 END IF;
349 --
350 EXCEPTION
351 WHEN others THEN
352 P_abs_status := 3;
353 END;
354 --
355 --------------------------Submit Req --------------------------
356 FUNCTION submit_req (p_program varchar2,
357 p_desc varchar2,
358 p_msg varchar2,
359 p_loc varchar2,
360 p_1 varchar2,p_2 varchar2,p_3 varchar2,p_4 varchar2,
361 p_5 varchar2,p_6 varchar2,p_7 varchar2,p_8 varchar2,
362 p_9 varchar2,p_10 varchar2,p_11 varchar2,p_12 varchar2,
363 p_13 varchar2,p_14 varchar2,p_15 varchar2,p_16 varchar2,
364 p_17 varchar2,p_18 varchar2,p_19 varchar2,p_20 varchar2,
365 p_21 varchar2,p_22 varchar2,p_23 varchar2,p_24 varchar2,
366 p_25 varchar2,p_26 varchar2,p_27 varchar2,p_28 varchar2,
367 p_29 varchar2,p_30 varchar2,p_31 varchar2,p_32 varchar2,
368 p_33 varchar2,p_34 varchar2,p_35 varchar2,p_36 varchar2,
369 p_37 varchar2,p_38 varchar2,p_39 varchar2,p_40 varchar2,
370 p_41 varchar2,p_42 varchar2,p_43 varchar2,p_44 varchar2,
371 p_45 varchar2,p_46 varchar2,p_47 varchar2,p_48 varchar2,
372 p_49 varchar2,p_50 varchar2,p_51 varchar2,p_52 varchar2,
373 p_53 varchar2,p_54 varchar2,p_55 varchar2,p_56 varchar2,
374 p_57 varchar2,p_58 varchar2,p_59 varchar2,p_60 varchar2,
375 p_61 varchar2,p_62 varchar2,p_63 varchar2,p_64 varchar2,
376 p_65 varchar2,p_66 varchar2,p_67 varchar2,p_68 varchar2,
377 p_69 varchar2,p_70 varchar2,p_71 varchar2,p_72 varchar2,
378 p_73 varchar2,p_74 varchar2,p_75 varchar2,p_76 varchar2,
379 p_77 varchar2,p_78 varchar2,p_79 varchar2,p_80 varchar2,
380 p_81 varchar2,p_82 varchar2,p_83 varchar2,p_84 varchar2,
381 p_85 varchar2,p_86 varchar2,p_87 varchar2,p_88 varchar2,
382 p_89 varchar2,p_90 varchar2,p_91 varchar2,p_92 varchar2,
383 p_93 varchar2,p_94 varchar2,p_95 varchar2,p_96 varchar2,
384 p_97 varchar2,p_98 varchar2,p_99 varchar2,p_100 varchar2
385 ) RETURN number IS
386 l_req_id number;
387 v_flag number := 0;
388 BEGIN
389 RETURN (v_flag);
390 END submit_req;
391 --
392 --------------------------Check For Holiday --------------------------
393 PROCEDURE check_for_holiday (p_date in DATE
394 ,p_hol_id in NUMBER
395 ,p_day_id OUT NOCOPY NUMBER
396 ,p_hours OUT NOCOPY NUMBER
397 ,p_retcode OUT NOCOPY NUMBER) IS
398 --
399 -- Procedure
400 -- Check_For_Holiday
401 -- Purpose
402 -- Check to see if a date is a holiday or the day before or after a holiday.
403 --
404 -- Arguments
405 -- p_date The date being checked.
406 -- p_hol_id The Holiday Calendar to be checked.
407 --
408 -- Returns:
409 -- p_day_id - holiday calendar day ID
410 -- p_hours - paid hours for holiday
411 -- p_retcode:
412 -- 0 - regular day
413 -- 1 - holiday
414 -- 2 - day before or after a holiday
415 --
416 -- Define cursor to return any holiday that falls between
417 -- the day before and the day after the date passed
418 CURSOR holiday_cur IS
419 select hdy.id, hdy.hours, hdy.holiday_date
420 from hxt_holiday_days hdy
421 ,hxt_holiday_calendars hcl
422 where hdy.holiday_date between p_date - 1 and p_date + 1
423 and hdy.hcl_id = hcl.id
424 and p_date between hcl.effective_start_date
425 and hcl.effective_end_date
426 and hcl.id = p_hol_id;
427 BEGIN
428 -- Initialize return code
429 p_retcode := 0;
430 -- Step through all holidays in three day range
431 FOR data in holiday_cur
432 LOOP
433 -- Check if date passed is holiday
434 IF p_date = data.holiday_date THEN
435 p_retcode := 1;
436 p_day_id := data.id;
437 p_hours := data.hours;
438 EXIT ; -- TA35 Loop must end when holiday is found. PWM 07/02/96 --ORA137
439 -- Otherwise, date passed is before or after holiday
440 ELSE
441 p_retcode := 2;
442 p_day_id := null;
443 p_hours := null;
444 END IF;
445 END LOOP;
446 END;
447 --
448 --------------------------Fnd Username --------------------------
449 FUNCTION Fnd_Username( a_user_id NUMBER ) RETURN VARCHAR2 IS
450 -- Get FND user name
451 CURSOR cur_user is
452 SELECT user_name
453 FROM fnd_user
454 WHERE user_id = a_user_id;
455 l_user_name fnd_user.user_name%TYPE;
456 BEGIN
457 OPEN cur_user;
458 FETCH cur_user INTO l_user_name;
459 CLOSE cur_user;
460 return (l_user_name);
461 END fnd_username;
462 --
463 --------------------------Element Cat --------------------------
464 -- BEGIN ORACLE bug #712501
465 FUNCTION element_cat(p_element_type_id IN NUMBER,
466 p_date_worked IN DATE) RETURN varchar2 IS
467
468 -- Returns earning category of the given element type
469 l_earning_cat VARCHAR2(30);
470 BEGIN
471 BEGIN
472 SELECT eltv.hxt_earning_category
473 INTO l_earning_cat
474 FROM hxt_pay_element_types_f_ddf_v eltv
475 WHERE eltv.element_type_id = p_element_type_id
476 AND p_date_worked BETWEEN eltv.effective_start_date
477 AND eltv.effective_end_date;
478 EXCEPTION
479 WHEN no_data_found THEN
480 l_earning_cat := NULL;
481 WHEN OTHERS THEN
482 l_earning_cat := 'ERR'; -- this done to flag error in calling routine
483 --
484 END;
485 return(l_earning_cat);
486 END;
487 --
488 --------------------------Check Policy Use --------------------------
489 FUNCTION check_policy_use (
490 p_policy_id IN NUMBER,
491 p_policy_name IN VARCHAR2,
492 p_policy_end_date IN DATE
493 )
494 RETURN BOOLEAN
495 IS
496
497 -- Function
498 -- check_policy_use
499 -- Purpose
500 -- Ensure ID values for ROTATION_PLAN, WORK_PLAN,
501 -- SHIFT_DIFFERENTIAL_POLICY, OVERTIME_POLICY or
502 -- HOUR_DEDUCT_POLICY have not been linked to the
503 -- ASSIGNMENT table if an attempt is made to delete them
504 -- or determine if a policy is currently assigned before
505 -- it is closed.
506 --
507 -- Arguments
508 -- p_policy_id the object Policy id.
509 -- p_policy_name the object Policy name.
510 -- p_policy_end_date the object Policy Date to (null if assignment check)
511 --
512 CURSOR assign_dfv (p_policy_id NUMBER)
513 IS
514 SELECT 1
515 FROM hxt_per_aei_ddf_v aeiv
516 WHERE ( aeiv.hxt_rotation_plan = p_policy_id
517 OR aeiv.hxt_earning_policy = p_policy_id
518 OR aeiv.hxt_hour_deduction_policy = p_policy_id
519 OR aeiv.hxt_shift_differential_policy = p_policy_id
520 )
521 AND ( p_policy_end_date
522 + 1 BETWEEN aeiv.effective_start_date
523 AND aeiv.effective_end_date
524 OR p_policy_end_date IS NULL
525 );
526
527 v_id NUMBER (15);
528 v_flag BOOLEAN;
529 -- v_date date;
530 -- v_session_date date;
531 BEGIN
532 v_id := NULL;
533 OPEN assign_dfv (p_policy_id);
534 FETCH assign_dfv INTO v_id;
535
536 IF assign_dfv%FOUND
537 THEN
538 v_flag := (TRUE);
539 ELSE
540 v_flag := (FALSE);
541 END IF;
542
543 CLOSE assign_dfv;
544 RETURN (v_flag);
545 END check_policy_use;
546 --
547 --------------------------Get Policies --------------------------
548 PROCEDURE get_policies(p_earn_pol_id IN NUMBER
549 ,p_assignment_id IN NUMBER
550 ,p_date IN DATE
551 ,p_work_plan OUT NOCOPY NUMBER
552 ,p_rotation_plan OUT NOCOPY NUMBER
553 ,p_ep_id OUT NOCOPY NUMBER
554 ,p_hdp_id OUT NOCOPY NUMBER
555 ,p_sdp_id OUT NOCOPY NUMBER
556 ,p_ep_type OUT NOCOPY VARCHAR2
557 ,p_egt_id OUT NOCOPY NUMBER
558 ,p_pep_id OUT NOCOPY NUMBER
559 ,p_pip_id OUT NOCOPY NUMBER
560 ,p_hcl_id OUT NOCOPY NUMBER
561 ,p_min_tcard_intvl OUT NOCOPY NUMBER
562 ,p_round_up OUT NOCOPY NUMBER
563 ,p_hcl_element_type_id OUT NOCOPY NUMBER
564 ,p_error OUT NOCOPY NUMBER) IS
565 --
566 --
567 -- Procedure GET_POLICIES
568 -- Purpose: Gets policies assigned to an input person on an input date.
569 -- Shift premiums returned by procedure Get_Shift_Info.
570 --
571 -- Returns p_error:
572 -- 0 - No errors occured
573 -- Other - Oracle error number
574 --
575 --
576 --
577 -- Modification Log:
578 -- 12/14/95 PJA Added Min_Tcard_Intvl and Round_Up.
579 -- 01/03/96 PJA Handle date effectivity and return error code.
580 --
581 --
582 CURSOR policies_cur(c_earn_pol_id NUMBER, c_assignment_id NUMBER, c_date DATE) IS
583 SELECT aeiv.hxt_rotation_plan
584 , egp.id
585 , aeiv.hxt_hour_deduction_policy
586 , aeiv.hxt_shift_differential_policy
587 , egp.fcl_earn_type
588 , egp.egt_id
589 , egp.pep_id
590 , egp.pip_id
591 , egp.hcl_id
592 , egp.min_tcard_intvl
593 , egp.round_up
594 , hcl.element_type_id
595 FROM
596 hxt_earning_policies egp
597 , hxt_holiday_calendars hcl
598 , hxt_per_aei_ddf_v aeiv
599 , per_all_assignments_f asm
600 WHERE asm.assignment_id = p_assignment_id
601 AND asm.assignment_id = aeiv.assignment_id
602 AND c_date between aeiv.effective_start_date
603 and aeiv.effective_end_date
604 AND c_date between asm.effective_start_date
605 and asm.effective_end_date
606 AND c_date between hcl.effective_start_date
607 and hcl.effective_end_date
608 AND egp.hcl_id = hcl.id
609 AND c_date between egp.effective_start_date
610 and egp.effective_end_date
611 AND egp.id = DECODE(c_earn_pol_id, NULL, aeiv.hxt_earning_policy, c_earn_pol_id);
612
613
614 BEGIN
615 OPEN policies_cur(p_earn_pol_id, p_assignment_id, p_date);
616 FETCH policies_cur
617 INTO p_rotation_plan
618 ,p_ep_id
619 ,p_hdp_id
620 ,p_sdp_id
621 ,p_ep_type
622 ,p_egt_id
623 ,p_pep_id
624 ,p_pip_id
625 ,p_hcl_id
626 ,p_min_tcard_intvl
627 ,p_round_up
628 ,p_hcl_element_type_id;
629 CLOSE policies_cur;
630
631 --
632 -- Set error code to no error
633 p_error := 0;
634 -- line C243 by BC
635 p_work_plan := null;
636 --
637 EXCEPTION
638 -- Return Oracle error number
639 WHEN others THEN
640 p_error := SQLCODE;
641 END;
642 --
643 --------------------------Get Shift Info --------------------------
644 PROCEDURE get_shift_info( p_date IN DATE
645 , p_work_id IN OUT NOCOPY NUMBER
646 , p_rotation_id IN NUMBER
647 , p_osp_id OUT NOCOPY NUMBER
648 , p_sdf_id OUT NOCOPY NUMBER
649 , p_standard_start OUT NOCOPY NUMBER
650 , p_standard_stop OUT NOCOPY NUMBER
651 , p_early_start OUT NOCOPY NUMBER
652 , p_late_stop OUT NOCOPY NUMBER
653 , p_hours OUT NOCOPY NUMBER --SIR212
654 , p_error OUT NOCOPY NUMBER) IS
655 --
656 -- Procedure GET_SHIFT_INFO
657 -- Purpose: Gets shift diff and off-shift premium for the person's
658 -- assigned shift on an input date
659 --
660 -- Returns p_error:
661 -- 0 - No errors occured
662 -- Other - Oracle error number
663 --
664 --
665 --
666 -- Modification Log:
667 -- 01/05/96 PJA Handle date effectivity and eturn error code.
668 -- 01/22/96 PJA Return shift start and stop times. Return weekly work
669 -- schedule ID if person assigned rotation schedule.
670 --
671 --
672 l_date DATE;
673 BEGIN
674 p_osp_id := NULL;
675 p_sdf_id := NULL;
676 p_early_start := NULL;
677 p_late_stop := NULL;
678 -- If rotation plan, get applicable work schedule id
679 -- Find rotation containing work date
680 SELECT rts.tws_id
681 INTO p_work_id
682 FROM hxt_rotation_schedules rts
683 WHERE rts.rtp_id = p_rotation_id
684 AND rts.start_date = (SELECT MAX(start_date)
685 FROM hxt_rotation_schedules
686 WHERE rtp_id = p_rotation_id
687 AND start_date <= p_date
688 );
689 --ORACLE END IF;
690 -- Get shift diff and off-shift premiums
691 SELECT wsh.off_shift_prem_id,
692 wsh.shift_diff_ovrrd_id,
693 sht.standard_start,
694 sht.standard_stop,
695 sht.early_start,
696 sht.late_stop,
697 sht.hours
698 INTO p_osp_id,
699 p_sdf_id,
700 p_standard_start,
701 p_standard_stop,
702 p_early_start,
703 p_late_stop,
704 p_hours
705 FROM hxt_shifts sht,
706 hxt_weekly_work_schedules wws,
707 hxt_work_shifts wsh
708 WHERE wsh.week_day = hxt_util.get_week_day(p_date)
709 AND wws.id = wsh.tws_id
710 AND p_date between wws.date_from
711 and nvl(wws.date_to, p_date)
712 AND wws.id = p_work_id
713 AND sht.id = wsh.sht_id;
714 --
715 -- Set error code to no error
716 p_error := 0;
717 --
718 EXCEPTION
719 -- Return Oracle error number
720 WHEN others THEN
721 p_error := SQLCODE;
722 END;
723 --
724 -------------------------- Get Period End Date --------------------------
725 --
726 FUNCTION get_period_end_date(p_batch_id IN NUMBER) return VARCHAR2 IS
727 --
728 cursor csr_period_end IS
729 select ptp.end_date
730 from per_time_periods ptp,
731 hxt_timecards_f htf
732 where htf.batch_id = p_batch_id
733 and htf.time_period_id = ptp.time_period_id;
734 --
735 cursor csr_retro_end IS
736 select ptp.end_date
737 from per_time_periods ptp,
738 hxt_det_hours_worked_f hdh,
739 hxt_timecards_f htf
740 where hdh.retro_batch_id = p_batch_id
741 and hdh.tim_id = htf.id
742 and htf.time_period_id = ptp.time_period_id;
743 --
744 l_period_end_date DATE;
745 --
746 BEGIN
747 --
748 OPEN csr_period_end;
749 FETCH csr_period_end into l_period_end_date;
750 --
751 IF csr_period_end%NOTFOUND THEN
752 --
753 OPEN csr_retro_end;
754 FETCH csr_retro_end into l_period_end_date;
755 CLOSE csr_retro_end;
756 --
757 END IF;
758 --
759 CLOSE csr_period_end;
760 --
761 RETURN fnd_date.date_to_displaydate(l_period_end_date);
762 --
763 END;
764 --
765 --------------------------Round Time --------------------------
766 FUNCTION round_time (p_time DATE
767 , p_interval NUMBER
768 , p_round_up NUMBER) RETURN DATE IS
769 l_min NUMBER;
770 l_mod NUMBER;
771 BEGIN
772 -- Get number of minutes past midnite
773 l_min := ( p_time - Trunc( p_time,'DD') ) * (24*60);
774 --
775 -- Get number of minutes past interval
776 l_mod := Mod( l_min, p_interval);
777 --
778 -- Apply interval rules to number of minutes (if remainder is less than round value,
779 -- deduct the remainder - otherwise, deduct the remainder then add the interval)
780 IF ( l_mod < p_round_up ) THEN
781 l_min := l_min - l_mod;
782 ELSE
783 l_min := l_min - l_mod + p_interval;
784 END IF;
785 --
786 -- Return new time (add minutes (converted to date) to date at midnite).
787 -- Round to prevent truncation
788 RETURN Round( ( Trunc( p_time,'DD') + (l_min/60/24) ), 'MI' );
789 --
790 END;
791 --
792 --------------------------Time to Hours --------------------------
793 FUNCTION time_to_hours(
794 P_TIME IN NUMBER ) RETURN NUMBER
795 IS
796 BEGIN
797 BEGIN
798 RETURN(FLOOR((p_time / 100)) +(MOD(p_time, 100) / 60));
799 END;
800 END time_to_hours;
801 --------------------------Get Next Seqno --------------------------
802 FUNCTION Get_Next_Seqno(a_timecard_id IN NUMBER, a_date_worked IN DATE) RETURN
803 NUMBER IS
804 -- Returns the max sequence + 10 of summary records of a given day on a given timecard
805 returned_seqno NUMBER;
806 BEGIN
807 SELECT NVL(MAX(seqno),0) INTO returned_seqno FROM hxt_sum_hours_worked thw --C421
808 WHERE thw.tim_id = a_timecard_id
809 AND thw.date_worked = a_date_worked;
810 RETURN returned_seqno + 10;
811 END;
812 --BEGIN SPR C166 BY BC
813 --
814 --------------------------Get Period Start --------------------------
815 FUNCTION Get_Period_Start(a_period_id IN NUMBER) RETURN DATE IS
816 -- Returns the end date of pay period a_period_id
817 returned_date DATE DEFAULT NULL; --SPR C166 BY BC
818 BEGIN
819 SELECT start_date INTO returned_date
820 FROM per_time_periods
821 WHERE TIME_PERIOD_ID = a_period_id;
822 RETURN returned_date;
823 EXCEPTION
824 WHEN OTHERS THEN
825 RETURN returned_date; --SPR C166 BY BC
826 END;
827 --
828 --------------------------Get Period End --------------------------
829 FUNCTION Get_Period_End(a_period_id IN NUMBER) RETURN DATE IS
830 -- Returns the end date of pay period a_period_id
831 returned_date DATE DEFAULT NULL; --SPR C166 BY BC
832 BEGIN
833 SELECT end_date INTO returned_date
834 FROM per_time_periods
835 WHERE TIME_PERIOD_ID = a_period_id;
836 RETURN returned_date;
837 EXCEPTION
838 WHEN OTHERS THEN
839 RETURN returned_date; --SPR C166 BY BC
840 END;
841 --
842 --------------------------Set Tim Status --------------------------
843 --ORAXXX removed
844 --
845 --------------------------Date Range --------------------------
846 FUNCTION date_range (start_date_in IN DATE,
847 end_date_in IN DATE,
848 check_time_in IN VARCHAR2 := 'NOTIME') RETURN VARCHAR2
849 /*
850 || date_range returns a string containing a date range
851 || in the format 'BETWEEN x AND y'
852 ||
853 || Parameters:
854 || start_date_in - The start date of the range. If NULL
855 || then use the min_start_date. If that is NULL, range
856 || has form '<= end_date'.
857 ||
858
859 || end_date_in - The end date of the range. If NULL
860 || then use the max_end_date. If that is NULL, range has
861 || form '>= start_date'.
862 ||
863 || check_time_in - If 'TIME' then use the time component
864 || of the dates as part of the comparison.
865 || If 'NOTIME' then strip off the time.
866 */
867
868 IS
869 /* String versions of parameters to place in return value */
870 start_date_int VARCHAR2(30);
871 end_date_int VARCHAR2(30);
872 /* Date mask for date<->character conversions. */
873 mask_int VARCHAR2(15) := 'MMDDYYYY';
874 /* Version of date mask which fits right into date range string */
875 mask_string VARCHAR2(30) := NULL;
876 /* The return value for the function. */
877 return_value VARCHAR2(1000) := NULL;
878 BEGIN
879 /*
880 || Finalize the date mask. If user wants to use time, add that to
881 || the mask. Then set the string version by embedding the mask
882 || in single quotes and with a trailing paranthesis.
883 */
884 IF UPPER (check_time_in) = 'TIME'
885 THEN
886 mask_int := mask_int || ' HHMISS';
887 END IF;
888 /*
889 || Convert mask. Example:
890 || If mask is: MMDDYYYY HHMISS
891 || then mask string is: ', 'MMDDYYYY HHMISS')
892 */
893 mask_string := ''', ''' || mask_int || ''')';
894 /* Now convert the dates to character strings using format mask */
895 start_date_int := TO_CHAR (start_date_in, mask_int);
896 end_date_int := TO_CHAR (end_date_in, mask_int);
897 /* If both start and end are NULL, then return NULL. */
898 IF start_date_int IS NULL AND end_date_int IS NULL
899 THEN
900 return_value := NULL;
901 /* If no start point then return "<=" format. */
902 ELSIF start_date_int IS NULL
903 THEN
904 return_value := '<= TO_DATE (''' || end_date_int || mask_string;
905 /* If no end point then return ">=" format. */
906 ELSIF end_date_int IS NULL
907 THEN
908 return_value := '>= TO_DATE (''' || start_date_int || mask_string;
909 /* Have start and end. A true range, so just put it together. */
910 ELSE
911 return_value :=
912 'BETWEEN TO_DATE (''' || start_date_int || mask_string ||
913 ' AND TO_DATE (''' || end_date_int || mask_string;
914 END IF;
915 RETURN return_value;
916 END;
917 --
918 --------------------------Get Retro Batch id --------------------------
919 FUNCTION Get_Retro_Batch_Id(p_tim_id IN NUMBER,
920 p_batch_name IN VARCHAR2 DEFAULT NULL,
921 p_batch_ref IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
922 l_retro_id NUMBER(15) := 0;
923 l_retcode NUMBER(15) := 0;
924 l_assignment_id NUMBER(15) := 0;
925
926 /*
927 cursor existing_batch is
928 select retro_batch_id, assignment_id
929 from hxt_det_hours_worked det,
930 hxt_batch_states tbs
931 where det.tim_id = p_tim_id
932 and tbs.batch_id=det.retro_batch_id
933 and tbs.status in ('H','VE')
934 and pay_status = 'R';
935 */
936
937 cursor existing_batch is
938 select retro_batch_id, assignment_id, pbh.batch_reference
939 from hxt_det_hours_worked det,
940 hxt_batch_states tbs,
941 pay_batch_headers pbh
942 where det.tim_id = p_tim_id
943 and tbs.batch_id = det.retro_batch_id
944 and tbs.status in ('H','VE')
945 and pay_status = 'R'
946 and pbh.batch_id = tbs.batch_id;
947
948 cursor batch_param is
949 select payroll_id, time_period_id, for_person_id
950 from hxt_timecards
951 where id = p_tim_id;
952
953 bp batch_param%ROWTYPE;
954 l_batch_ref VARCHAR2(30);
955
956 BEGIN
957 open existing_batch;
958 fetch existing_batch into l_retro_id,l_assignment_id, l_batch_ref;
959 if existing_batch%NOTFOUND then
960 open batch_param;
961 fetch batch_param into bp;
962 if batch_param%FOUND then
963 l_retcode := HXT_UTIL.create_batch('R', --source is retro-pay
964 p_batch_name,
965 p_batch_ref,
966 bp.payroll_id,
967 bp.time_period_id,
968 l_assignment_id,
969 bp.for_person_id,
970 l_retro_id);
971 if l_retcode <> 0 then
972 l_retro_id := 0;
973 end if;
974 else
975 l_retro_id := 0;
976 end if;
977 close batch_param;
978 else
979 IF p_batch_ref IS NOT NULL THEN
980 IF l_batch_ref NOT like p_batch_ref THEN
981 l_retcode := HXT_UTIL.create_batch('R',
982 p_batch_name,
983 p_batch_ref,
984 bp.payroll_id,
985 bp.time_period_id,
986 l_assignment_id,
987 bp.for_person_id,
988 l_retro_id);
989 if l_retcode <> 0 then
990 l_retro_id := 0;
991 end if;
992 END IF;
993 END IF;
994 end if;
995 close existing_batch;
996
997 -- Bug 12919783
998 -- Rollback Process needs each retro batch to be recorded.
999
1000 IF NOT hxt_otc_retrieval_interface.g_retro_tab.EXISTS(TO_CHAR(p_tim_id))
1001 THEN
1002 hxt_otc_retrieval_interface.g_retro_tab(TO_CHAR(p_tim_id)) := l_retro_id;
1003 END IF;
1004
1005 IF g_debug
1006 THEN
1007 hr_utility.trace('P_tim_id :'||p_tim_id);
1008 hr_utility.trace('Retro batch id '||l_retro_id);
1009 END IF;
1010
1011 return (l_retro_id);
1012
1013 END;
1014 --
1015 --------------------------Create Batch --------------------------
1016 FUNCTION create_batch( i_source IN VARCHAR2,
1017 p_batch_name IN VARCHAR2 DEFAULT NULL,
1018 p_batch_ref IN VARCHAR2 DEFAULT NULL,
1019 i_payroll_id IN NUMBER,
1020 i_time_period_id IN NUMBER,
1021 i_assignment_id IN NUMBER,
1022 i_person_id IN NUMBER,
1023 o_batch_id OUT NOCOPY NUMBER) RETURN NUMBER IS
1024
1025 l_batch_id pay_batch_headers.batch_id%TYPE DEFAULT NULL;
1026 l_reference_num pay_batch_headers.batch_reference%TYPE DEFAULT NULL;
1027 l_batch_name pay_batch_headers.batch_name%TYPE DEFAULT NULL;
1028 l_error_text VARCHAR2(128) DEFAULT NULL;
1029 l_batch_id_error EXCEPTION;
1030 l_reference_num_error EXCEPTION;
1031 l_retcode NUMBER DEFAULT 0;
1032 l_user_id fnd_user.user_id%TYPE := FND_GLOBAL.User_Id;
1033 l_user_name fnd_user.user_name%TYPE := 'OTM';
1034 l_sysdate DATE := trunc(SYSDATE);
1035 l_bus_group_id hr_organization_units.business_group_id%TYPE :=
1036 FND_PROFILE.Value( 'PER_BUSINESS_GROUP_ID' );
1037
1038 l_object_version_number pay_batch_headers.object_version_number%TYPE;
1039 cursor assign_param(c_person_id NUMBER) IS
1040 select asm.business_group_id
1041 from per_assignment_status_types ast,
1042 per_assignments_f asm
1043 where asm.person_id = c_person_id
1044 and ast.assignment_status_type_id = asm.assignment_status_type_id
1045 and ast.pay_system_status = 'P'; -- Check payroll status
1046
1047 BEGIN
1048 IF p_batch_ref IS NULL THEN
1049 hxt_user_exits.Define_Reference_Number(i_payroll_id,
1050 i_time_period_id,
1051 i_assignment_id,
1052 i_person_id,
1053 l_user_name,
1054 i_source,
1055 l_reference_num,
1056 l_error_text);
1057 ELSE
1058 l_reference_num := p_batch_ref || ' R';
1059 END IF;
1060
1061 IF l_error_text <> NULL THEN
1062 RETURN 1;
1063 END IF;
1064
1065 -- Get business_group_id from PER_ASSIGNMENTS_F table
1066 open assign_param(i_person_id);
1067 fetch assign_param into l_bus_group_id;
1068 if assign_param%NOTFOUND then
1069 close assign_param;
1070 RETURN 2;
1071 end if;
1072 close assign_param;
1073
1074 /* Get next batch number */
1075 -- create a batch first
1076 pay_batch_element_entry_api.create_batch_header (
1077 p_session_date=> l_sysdate,
1078 p_batch_name=> to_char(sysdate, 'DD-MM-RRRR HH24:MI:SS'),
1079 p_batch_status=> 'U',
1080 p_business_group_id=> l_bus_group_id,
1081 p_action_if_exists=> 'I',
1082 p_batch_reference=> l_reference_num,
1083 p_batch_source=> 'OTM',
1084 p_purge_after_transfer=> 'N',
1085 p_reject_if_future_changes=> 'N',
1086 p_batch_id=> l_batch_id,
1087 p_object_version_number=> l_object_version_number
1088 );
1089
1090 -- from the batch id, get the batch name
1091 IF p_batch_name IS NULL
1092 THEN
1093 hxt_user_exits.define_batch_name (
1094 l_batch_id,
1095 l_batch_name,
1096 l_error_text
1097 );
1098
1099 /* l_batch_id := hxt_time_gen.Get_Next_Batch_Id;
1100 IF l_batch_id = NULL THEN
1101 RETURN 2;
1102 END IF;
1103 --
1104 IF p_batch_name IS NULL THEN
1105 hxt_user_exits.Define_Batch_Name(l_batch_id, l_batch_name, l_error_text);*/
1106 ELSE
1107 l_batch_name := p_batch_name || 'R' || to_char(l_batch_id);
1108 END IF;
1109
1110 IF l_error_text <> NULL THEN
1111 RETURN 1;
1112 END IF;
1113
1114 /* INSERT INTO pay_batch_headers
1115 (batch_id,
1116 business_group_id,
1117 batch_name,
1118 batch_status,
1119 action_if_exists,
1120 batch_reference,
1121 batch_source,
1122 purge_after_transfer,
1123 reject_if_future_changes,
1124 created_by,
1125 creation_date)
1126 VALUES
1127 (l_batch_id,
1128 l_bus_group_id,
1129 l_batch_name,
1130 'U',
1131 'I',
1132 l_reference_num,
1133 'OTM',
1134 'N',
1135 'N',
1136 l_user_id,
1137 l_sysdate);*/
1138 --update the batch name
1139 pay_batch_element_entry_api.update_batch_header (
1140 p_session_date => l_sysdate,
1141 p_batch_id=> l_batch_id,
1142 p_object_version_number=> l_object_version_number,
1143 p_batch_name=> l_batch_name
1144 );
1145
1146 --COMMIT;
1147 o_batch_id := l_batch_id;
1148 return 0;
1149 END create_batch;
1150 --------------------------Gen Exception --------------------------
1151 PROCEDURE GEN_EXCEPTION
1152 (p_LOCATION IN VARCHAR2
1153 ,p_HXT_ERROR_MSG IN VARCHAR2
1154 ,p_ORACLE_ERROR_MSG IN VARCHAR2
1155 ,p_RESOLUTION IN VARCHAR2) IS
1156 l_EXCEP_seqno NUMBER; --- the next sequence number for new error record
1157 BEGIN
1158
1159 DEBUG(p_LOCATION || ' - ' || p_HXT_ERROR_MSG || ' : ' || p_ORACLE_ERROR_MSG ||
1160 ' => ' || p_RESOLUTION); --HXT115
1161 null;
1162 EXCEPTION WHEN OTHERS THEN
1163 null;
1164 END gen_exception;
1165 --
1166 --------------------------Build Cost Alloc Flex Entry --------------------------
1167 /******************************************************************
1168 build_cost_alloc_flex_entry()
1169 Select vital information from the fnd_id_flex_structures and
1170 fnd_id_flex_segments tables to build concatenated segments
1171 string using the segment values passed into this function.
1172 Then create a new entry to the pay_cost_allocation_keyflex table
1173 for the Cost Allocation Flexfield or retrieve the id of an existing
1174 one by calling the hr function hr_entry.maintain_cost_keyflex.
1175 ******************************************************************/
1176 FUNCTION build_cost_alloc_flex_entry(i_segment1 IN VARCHAR2,
1177 i_segment2 IN VARCHAR2,
1178 i_segment3 IN VARCHAR2,
1179 i_segment4 IN VARCHAR2,
1180 i_segment5 IN VARCHAR2,
1181 i_segment6 IN VARCHAR2,
1182 i_segment7 IN VARCHAR2,
1183 i_segment8 IN VARCHAR2,
1184 i_segment9 IN VARCHAR2,
1185 i_segment10 IN VARCHAR2,
1186 i_segment11 IN VARCHAR2,
1187 i_segment12 IN VARCHAR2,
1188 i_segment13 IN VARCHAR2,
1189 i_segment14 IN VARCHAR2,
1190 i_segment15 IN VARCHAR2,
1191 i_segment16 IN VARCHAR2,
1192 i_segment17 IN VARCHAR2,
1193 i_segment18 IN VARCHAR2,
1194 i_segment19 IN VARCHAR2,
1195 i_segment20 IN VARCHAR2,
1196 i_segment21 IN VARCHAR2,
1197 i_segment22 IN VARCHAR2,
1198 i_segment23 IN VARCHAR2,
1199 i_segment24 IN VARCHAR2,
1200 i_segment25 IN VARCHAR2,
1201 i_segment26 IN VARCHAR2,
1202 i_segment27 IN VARCHAR2,
1203 i_segment28 IN VARCHAR2,
1204 i_segment29 IN VARCHAR2,
1205 i_segment30 IN VARCHAR2,
1206 i_business_group_id IN NUMBER,
1207 io_keyflex_id IN OUT NOCOPY NUMBER,
1208 o_error_msg OUT NOCOPY VARCHAR2)
1209 -- p_mode IN VARCHAR2 default 'INSERT')
1210 RETURN NUMBER IS
1211
1212 /* This cursor selects Cost Allocation Flexfield info needed */
1213 /* to build an entry to pay_cost_allocation_keyflex */
1214
1215 CURSOR cost_cur IS
1216 SELECT fifs.concatenated_segment_delimiter,
1217 fifs.dynamic_inserts_allowed_flag,
1218 fifs.enabled_flag,
1219 fifs.freeze_flex_definition_flag
1220 FROM fnd_id_flex_structures fifs,
1221 per_business_groups_perf pbg
1222 WHERE fifs.id_flex_code = 'COST'
1223 AND fifs.application_id = 801
1224 AND pbg.business_group_id = i_business_group_id
1225 AND pbg.cost_allocation_structure = fifs.id_flex_num;
1226
1227 cost_rec cost_cur%ROWTYPE;
1228
1229 /* This cursor selects Cost Allocation Flexfield segments */
1230 /* that have been defined and are enabled and have been */
1231 /* qualified for element entry */
1232
1233 --CURSOR seg_cur IS
1234 --CURSOR seg_cur(c_id_flex_num NUMBER) IS
1235 -- SELECT seg.application_column_name,
1236 -- seg.display_size,
1237 -- seg.segment_num
1238 -- FROM fnd_id_flex_segments seg
1239 -- WHERE seg.id_flex_code = 'COST'
1240 -- AND seg.application_id = 801
1241 -- AND seg.id_flex_num = c_id_flex_num
1242 -- AND seg.application_column_name IN
1243 -- ('SEGMENT1','SEGMENT2','SEGMENT3','SEGMENT4','SEGMENT5',
1244 -- 'SEGMENT6','SEGMENT7','SEGMENT8','SEGMENT9','SEGMENT10',
1245 -- 'SEGMENT11','SEGMENT12','SEGMENT13','SEGMENT14','SEGMENT15',
1246 -- 'SEGMENT16','SEGMENT17','SEGMENT18','SEGMENT19','SEGMENT20',
1247 -- 'SEGMENT21','SEGMENT22','SEGMENT23','SEGMENT24','SEGMENT25',
1248 -- 'SEGMENT26','SEGMENT27','SEGMENT28','SEGMENT29','SEGMENT30')
1249 -- AND seg.enabled_flag = 'Y'
1250 -- AND EXISTS (SELECT 'X'
1251 -- FROM fnd_segment_attribute_values fsav
1252 -- WHERE fsav.id_flex_code = 'COST'
1253 -- AND fsav.application_id = 801
1254 -- AND fsav.id_flex_num = seg.id_flex_num
1255 -- AND fsav.application_column_name = seg.application_column_name
1256 -- AND fsav.attribute_value = 'Y')
1257 -- ORDER BY seg.segment_num;
1258
1259 /* This cursor checks to see if this particular segment combination already exists */
1260
1261 --CURSOR exist_flex_cur(c_concatenated_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE)IS
1262 -- SELECT pcak.cost_allocation_keyflex_id
1263 -- FROM pay_cost_allocation_keyflex pcak
1264 -- WHERE pcak.concatenated_segments = c_concatenated_segments;
1265
1266 /* Cursor to generate a new Cost Allocation Keyflex Id */
1267
1268 --CURSOR flex_id_cur IS
1269 -- SELECT pay_cost_allocation_keyflex_s.nextval
1270 -- FROM sys.dual;
1271
1272 l_return_code NUMBER;
1273
1274 --l_concatenated_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE DEFAULT NULL;
1275
1276 l_id_flex_num fnd_id_flex_structures.id_flex_num%TYPE;
1277 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
1278 l_allowed fnd_id_flex_structures.dynamic_inserts_allowed_flag%TYPE;
1279 l_enabled fnd_id_flex_structures.enabled_flag%TYPE;
1280 l_frozen fnd_id_flex_structures.freeze_flex_definition_flag%TYPE;
1281
1282 --l_key_num VARCHAR2(32);
1283
1284 flex_not_found EXCEPTION;
1285 cost_flex_not_enabled EXCEPTION;
1286 cost_flex_not_frozen EXCEPTION;
1287 no_structure_found EXCEPTION;
1288
1289 BEGIN
1290
1291 pay_paywsqee_pkg.populate_context_items(i_business_group_id, l_id_flex_num);
1292
1293 IF l_id_flex_num IS NULL THEN
1294 RAISE no_structure_found;
1295 END IF;
1296 OPEN cost_cur;
1297 FETCH cost_cur
1298 INTO l_delimiter,
1299 l_allowed,
1300 l_enabled,
1301 l_frozen;
1302 IF cost_cur%NOTFOUND THEN
1303 RAISE flex_not_found;
1304 CLOSE cost_cur;
1305 END IF;
1306 CLOSE cost_cur;
1307
1308 IF l_enabled = 'N' THEN
1309 RAISE cost_flex_not_enabled;
1310 END IF;
1311
1312 IF l_frozen = 'N' THEN
1313 RAISE cost_flex_not_frozen;
1314 END IF;
1315
1316 --used new procedure hr_entry.maintain_cost_keyflex --2711607
1317
1318 io_keyflex_id:=
1319 hr_entry.maintain_cost_keyflex(
1320 p_cost_keyflex_structure => l_id_flex_num,
1321 p_cost_allocation_keyflex_id => -1,
1322 p_concatenated_segments => NULL,
1323 p_summary_flag => 'N',
1324 p_start_date_active => NULL,
1325 p_end_date_active => NULL,
1326 p_segment1 => i_segment1,
1327 p_segment2 => i_segment2,
1328 p_segment3 => i_segment3,
1329 p_segment4 => i_segment4,
1330 p_segment5 => i_segment5,
1331 p_segment6 => i_segment6,
1332 p_segment7 => i_segment7,
1333 p_segment8 => i_segment8,
1334 p_segment9 => i_segment9,
1335 p_segment10 => i_segment10,
1336 p_segment11 => i_segment11,
1337 p_segment12 => i_segment12,
1338 p_segment13 => i_segment13,
1339 p_segment14 => i_segment14,
1340 p_segment15 => i_segment15,
1341 p_segment16 => i_segment16,
1342 p_segment17 => i_segment17,
1343 p_segment18 => i_segment18,
1344 p_segment19 => i_segment19,
1345 p_segment20 => i_segment20,
1346 p_segment21 => i_segment21,
1347 p_segment22 => i_segment22,
1348 p_segment23 => i_segment23,
1349 p_segment24 => i_segment24,
1350 p_segment25 => i_segment25,
1351 p_segment26 => i_segment26,
1352 p_segment27 => i_segment27,
1353 p_segment28 => i_segment28,
1354 p_segment29 => i_segment29,
1355 p_segment30 => i_segment30);
1356
1357 RETURN 0;
1358 -------------------------------------------
1359 --commented as part of 2711607
1360 -- FOR seg_rec IN seg_cur(l_id_flex_num) LOOP
1361 -- IF seg_rec.application_column_name = 'SEGMENT1' THEN
1362 -- l_concatenated_segments := l_concatenated_segments || i_segment1 || l_delimiter;
1363 -- ELSIF seg_rec.application_column_name = 'SEGMENT2' THEN
1364 -- l_concatenated_segments := l_concatenated_segments || i_segment2 || l_delimiter;
1365 -- ELSIF seg_rec.application_column_name = 'SEGMENT3' THEN
1366 -- l_concatenated_segments := l_concatenated_segments || i_segment3 || l_delimiter;
1367 -- ELSIF seg_rec.application_column_name = 'SEGMENT4' THEN
1368 -- l_concatenated_segments := l_concatenated_segments || i_segment4 || l_delimiter;
1369 -- ELSIF seg_rec.application_column_name = 'SEGMENT5' THEN
1370 -- l_concatenated_segments := l_concatenated_segments || i_segment5 || l_delimiter;
1371 -- ELSIF seg_rec.application_column_name = 'SEGMENT6' THEN
1372 -- l_concatenated_segments := l_concatenated_segments || i_segment6 || l_delimiter;
1373 -- ELSIF seg_rec.application_column_name = 'SEGMENT7' THEN
1374 -- l_concatenated_segments := l_concatenated_segments || i_segment7 || l_delimiter;
1375 -- ELSIF seg_rec.application_column_name = 'SEGMENT8' THEN
1376 -- l_concatenated_segments := l_concatenated_segments || i_segment8 || l_delimiter;
1377 -- ELSIF seg_rec.application_column_name = 'SEGMENT9' THEN
1378 -- l_concatenated_segments := l_concatenated_segments || i_segment9 || l_delimiter;
1379 -- ELSIF seg_rec.application_column_name = 'SEGMENT10' THEN
1380 -- l_concatenated_segments := l_concatenated_segments || i_segment10 || l_delimiter;
1381 -- ELSIF seg_rec.application_column_name = 'SEGMENT11' THEN
1382 -- l_concatenated_segments := l_concatenated_segments || i_segment11 || l_delimiter;
1383 -- ELSIF seg_rec.application_column_name = 'SEGMENT12' THEN
1384 -- l_concatenated_segments := l_concatenated_segments || i_segment12 || l_delimiter;
1385 -- ELSIF seg_rec.application_column_name = 'SEGMENT13' THEN
1386 -- l_concatenated_segments := l_concatenated_segments || i_segment13 || l_delimiter;
1387 -- ELSIF seg_rec.application_column_name = 'SEGMENT14' THEN
1388 -- l_concatenated_segments := l_concatenated_segments || i_segment14 || l_delimiter;
1389 -- ELSIF seg_rec.application_column_name = 'SEGMENT15' THEN
1390 -- l_concatenated_segments := l_concatenated_segments || i_segment15 || l_delimiter;
1391 -- ELSIF seg_rec.application_column_name = 'SEGMENT16' THEN
1392 -- l_concatenated_segments := l_concatenated_segments || i_segment16 || l_delimiter;
1393 -- ELSIF seg_rec.application_column_name = 'SEGMENT17' THEN
1394 -- l_concatenated_segments := l_concatenated_segments || i_segment17 || l_delimiter;
1395 -- ELSIF seg_rec.application_column_name = 'SEGMENT18' THEN
1396 -- l_concatenated_segments := l_concatenated_segments || i_segment18 || l_delimiter;
1397 -- ELSIF seg_rec.application_column_name = 'SEGMENT19' THEN
1398 -- l_concatenated_segments := l_concatenated_segments || i_segment19 || l_delimiter;
1399 -- ELSIF seg_rec.application_column_name = 'SEGMENT20' THEN
1400 -- l_concatenated_segments := l_concatenated_segments || i_segment20 || l_delimiter;
1401 -- ELSIF seg_rec.application_column_name = 'SEGMENT21' THEN
1402 -- l_concatenated_segments := l_concatenated_segments || i_segment21 || l_delimiter;
1403 -- ELSIF seg_rec.application_column_name = 'SEGMENT22' THEN
1404 -- l_concatenated_segments := l_concatenated_segments || i_segment22 || l_delimiter;
1405 -- ELSIF seg_rec.application_column_name = 'SEGMENT23' THEN
1406 -- l_concatenated_segments := l_concatenated_segments || i_segment23 || l_delimiter;
1407 -- ELSIF seg_rec.application_column_name = 'SEGMENT24' THEN
1408 -- l_concatenated_segments := l_concatenated_segments || i_segment24 || l_delimiter;
1409 -- ELSIF seg_rec.application_column_name = 'SEGMENT25' THEN
1410 -- l_concatenated_segments := l_concatenated_segments || i_segment25 || l_delimiter;
1411 -- ELSIF seg_rec.application_column_name = 'SEGMENT26' THEN
1412 -- l_concatenated_segments := l_concatenated_segments || i_segment26 || l_delimiter;
1413 -- ELSIF seg_rec.application_column_name = 'SEGMENT27' THEN
1414 -- l_concatenated_segments := l_concatenated_segments || i_segment27 || l_delimiter;
1415 -- ELSIF seg_rec.application_column_name = 'SEGMENT28' THEN
1416 -- l_concatenated_segments := l_concatenated_segments || i_segment28 || l_delimiter;
1417 -- ELSIF seg_rec.application_column_name = 'SEGMENT29' THEN
1418 -- l_concatenated_segments := l_concatenated_segments || i_segment29 || l_delimiter;
1419 -- ELSIF seg_rec.application_column_name = 'SEGMENT30' THEN
1420 -- l_concatenated_segments := l_concatenated_segments || i_segment30 || l_delimiter;
1421 -- END IF;
1422 -- END LOOP;
1423
1424 /* Strip off a trailing delimiter if one exists */
1425 -- IF SUBSTR(l_concatenated_segments,-1) = l_delimiter THEN
1426 -- l_concatenated_segments := SUBSTR(l_concatenated_segments,0,LENGTH(l_concatenated_segments)-1);
1427 -- END IF;
1428
1429 /* Return NULL when no values exist */
1430 -- IF LTRIM(l_concatenated_segments, l_delimiter) IS NULL THEN
1431 -- io_keyflex_id := NULL;
1432 -- RETURN 0;
1433 -- END IF;
1434
1435 -- DEBUG('Concatenated Segments : ' || l_concatenated_segments);
1436
1437 /* Attempt to find a matching keyflex entry */
1438 -- OPEN exist_flex_cur(l_concatenated_segments);
1439 -- FETCH exist_flex_cur INTO io_keyflex_id;
1440
1441 /*When no match exists,create a new entry in pay_cost_allocation_keyflex */
1442 -- IF exist_flex_cur%NOTFOUND THEN
1443 -- CLOSE exist_flex_cur;
1444
1445 -- OPEN flex_id_cur;
1446 -- FETCH flex_id_cur INTO io_keyflex_id;
1447 -- CLOSE flex_id_cur;
1448
1449 -- INSERT INTO pay_cost_allocation_keyflex
1450 -- (cost_allocation_keyflex_id
1451 -- ,concatenated_segments
1452 -- ,id_flex_num
1453 -- ,last_update_date
1454 -- ,last_updated_by
1455 -- ,summary_flag
1456 -- ,enabled_flag
1457 -- ,start_date_active
1458 -- ,end_date_active
1459 -- ,segment1
1460 -- ,segment2
1461 -- ,segment3
1462 -- ,segment4
1463 -- ,segment5
1464 -- ,segment6
1465 -- ,segment7
1466 -- ,segment8
1467 -- ,segment9
1468 -- ,segment10
1469 -- ,segment11
1470 -- ,segment12
1471 -- ,segment13
1472 -- ,segment14
1473 -- ,segment15
1474 -- ,segment16
1475 -- ,segment17
1476 -- ,segment18
1477 -- ,segment19
1478 -- ,segment20
1479 -- ,segment21
1480 -- ,segment22
1481 -- ,segment23
1482 -- ,segment24
1483 -- ,segment25
1484 -- ,segment26
1485 -- ,segment27
1486 -- ,segment28
1487 -- ,segment29
1488 -- ,segment30)
1489 -- VALUES
1490 -- (io_keyflex_id
1491 -- ,l_concatenated_segments
1492 -- ,l_id_flex_num
1493 -- ,SYSDATE
1494 -- ,NULL
1495 -- ,'N'
1496 -- ,'Y'
1497 -- ,to_date('01-01-1900', 'DD-MM-YYYY') --fnd_date.chardate_to_date('1900/01/01')
1498 -- ,NULL
1499 -- ,i_segment1
1500 -- ,i_segment2
1501 -- ,i_segment3
1502 -- ,i_segment4
1503 -- ,i_segment5
1504 -- ,i_segment6
1505 -- ,i_segment7
1506 -- ,i_segment8
1507 -- ,i_segment9
1508 -- ,i_segment10
1509 -- ,i_segment11
1510 -- ,i_segment12
1511 -- ,i_segment13
1512 -- ,i_segment14
1513 -- ,i_segment15
1514 -- ,i_segment16
1515 -- ,i_segment17
1516 -- ,i_segment18
1517 -- ,i_segment19
1518 -- ,i_segment20
1519 -- ,i_segment21
1520 -- ,i_segment22
1521 -- ,i_segment23
1522 -- ,i_segment24
1523 -- ,i_segment25
1524 -- ,i_segment26
1525 -- ,i_segment27
1526 -- ,i_segment28
1527 -- ,i_segment29
1528 -- ,i_segment30);
1529 -- ELSE
1530 -- CLOSE exist_flex_cur;
1531 -- END IF;
1532
1533 -- RETURN 0;
1534
1535 EXCEPTION
1536 WHEN no_structure_found THEN
1537 FND_MESSAGE.SET_NAME('HXT','HXT_39470_CA_NOT_LOC_4_BUS_GRP');
1538 o_error_msg := FND_MESSAGE.GET;
1539 FND_MESSAGE.CLEAR;
1540 DEBUG('Cost Allocation Structure not located for this Business Group');
1541 RETURN 1;
1542 WHEN flex_not_found THEN
1543 FND_MESSAGE.SET_NAME('HXT','HXT_39471_CA_NOT_LOC_4_APP');
1544 o_error_msg := FND_MESSAGE.GET;
1545 FND_MESSAGE.CLEAR;
1546 DEBUG('Cost Allocation Flexfield not located for application 801');
1547 RETURN 1;
1548 WHEN cost_flex_not_enabled THEN
1549 FND_MESSAGE.SET_NAME('HXT','HXT_39472_CA_NOT_ENABLED');
1550 o_error_msg := FND_MESSAGE.GET;
1551 FND_MESSAGE.CLEAR;
1552 DEBUG('Cost Allocation Flexfield is not enabled');
1553 RETURN 1;
1554 WHEN cost_flex_not_frozen THEN
1555 FND_MESSAGE.SET_NAME('HXT','HXT_39473_CA_NOT_COMPILED');
1556 o_error_msg := FND_MESSAGE.GET;
1557 FND_MESSAGE.CLEAR;
1558 DEBUG('Cost Allocation Flexfield needs to be frozen and compiled');
1559 RETURN 1;
1560 WHEN OTHERS THEN
1561 -- Bug 12919783
1562 hr_utility.trace(dbms_utility.format_error_backtrace);
1563 o_error_msg := SQLERRM;
1564 DEBUG(SQLERRM); --HXT115
1565 RETURN 2;
1566
1567 END build_cost_alloc_flex_entry;
1568 --End COSTING
1569 --------------------------------------------PROCEDURE check_absence------------------------------------
1570 -- added 07/31/97 RDB
1571 PROCEDURE check_absence(
1572 P_assignment_id IN NUMBER,
1573 P_period_id IN NUMBER,
1574 P_tim_id IN NUMBER,
1575 P_calculation_date IN DATE,
1576 P_element_type_id IN NUMBER,
1577 P_hours IN NUMBER,
1578 P_net_amt OUT NOCOPY NUMBER,
1579 P_period_amt OUT NOCOPY NUMBER,
1580 P_available_amt OUT NOCOPY NUMBER,
1581 P_abs_status OUT NOCOPY NUMBER) IS
1582 --
1583 -- Updated 07-23-97 by RDB ... added EXTRA DATES AND CHANGED CALL FROM
1584 -- PAY_US_PTO_ACCRUAL.GET_NET_ACCRUAL TO
1585 -- TA_UTIL.TA_GET_NEXT_ACCRUAL.
1586 -- Procedure check_absence
1587 --
1588 -- Purpose
1589 -- Check the net accrual for a specific Absenece Element for sufficient hours
1590 -- in order to allow person to take hours requested. If enough, then return
1591 -- P_abs_status = 0; if not, then return P_abs_status = 1; if NO PTO Accrual Plan,
1592 -- then return P_abs_status = 2.
1593 --
1594 -- Arguments
1595 p_plan_id nuMBER;
1596 p_PLAN_NAME VARCHAR2(80);
1597 p_PLAN_ELEMENT_TYPE_ID NUMBER;
1598 p_PLAN_CATEGORY VARCHAR2(30);
1599 p_PLAN_CATEGORY_NAME VARCHAR2(80);
1600 p_PTO_ELEMENT_TYPE_ID NUMBER;
1601 p_PTO_ELEMENT_NAME VARCHAR2(80);
1602 p_PTO_INPUT_VALUE_ID NUMBER;
1603 p_PTO_INPUT_VALUE_NAME VARCHAR2(30);
1604 --
1605 -- Local Variables
1606 l_amount NUMBER;
1607 l_total_period_hrs NUMBER;
1608 --
1609 -- Get PTO ACCRUAL PLAN for Absence Element
1610 --
1611 CURSOR pto_plan (p_assignment_id NUMBER,
1612 p_calculation_date DATE) IS
1613 SELECT
1614 PAP.ACCRUAL_PLAN_ID,
1615 PAP.ACCRUAL_CATEGORY
1616 FROM
1617 PAY_ELEMENT_TYPES_F PETF,
1618 PAY_ELEMENT_CLASSIFICATIONS PEC,
1619 PAY_ELEMENT_ENTRIES_F PEEF,
1620 PAY_ELEMENT_LINKS_F PELF,
1621 PAY_ACCRUAL_PLANS PAP
1622 WHERE
1623 PEEF.ASSIGNMENT_ID=P_assignment_id
1624 AND PETF.CLASSIFICATION_ID=PEC.CLASSIFICATION_ID
1625 AND UPPER(PEC.CLASSIFICATION_NAME) LIKE UPPER('PTO Accrual%')
1626 AND PETF.ELEMENT_TYPE_ID=PELF.ELEMENT_TYPE_ID
1627 AND PEEF.ELEMENT_LINK_ID=PELF.ELEMENT_LINK_ID
1628 AND p_calculation_date BETWEEN PETF.EFFECTIVE_START_DATE
1629 AND PETF.EFFECTIVE_END_DATE
1630 AND PETF.ELEMENT_TYPE_ID = PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID;
1631
1632 --
1633 -- Total all hours for Absence Element for current pay period
1634 --
1635 CURSOR hr_amt(p_tim_id NUMBER,
1636 p_assignment_id NUMBER,
1637 p_element_type_id NUMBER) IS
1638 select sum(hrw.hours)
1639 from
1640 hxt_sum_hours_worked_x hrw
1641 where
1642 hrw.tim_id = p_tim_id
1643 and hrw.assignment_id = p_assignment_id
1644 and hrw.element_type_id = p_element_type_id;
1645
1646 --
1647 BEGIN
1648 --
1649 -- Initialize OUT variables;
1650 P_net_amt := NULL;
1651 P_period_amt := NULL;
1652 P_abs_status := NULL;
1653 P_available_amt := NULL;
1654 --
1655 -- Get PTO plan for element type passed
1656 --
1657 open pto_plan(p_assignment_id,
1658 p_calculation_date);
1659 --
1660 --
1661 fetch pto_plan into P_PLAN_ID,
1662 p_PLAN_CATEGORY;
1663 --
1664 close pto_plan;
1665 --
1666 IF p_PLAN_ID IS NULL THEN
1667 P_abs_status := 2; --- Element does not belong to an Accrual Plan
1668 ELSE
1669 --
1670 -- Get net Accrual plan amount
1671 --
1672 l_amount := PAY_US_PTO_ACCRUAL.GET_NET_ACCRUAL( P_assignment_id,
1673 P_calculation_date,
1674 P_plan_id,
1675 P_plan_category);
1676
1677 --
1678 P_net_amt := l_amount;
1679 --
1680 -- Get total Absence Element hours for current pay period
1681 --
1682 open hr_amt(p_tim_id,p_assignment_id,p_element_type_id);
1683 --
1684 fetch hr_amt into l_total_period_hrs;
1685
1686
1687 --
1688 close hr_amt;
1689 --
1690 -- Total Absence Element hours for current pay period
1691 --
1692
1693 IF l_total_period_hrs IS NULL THEN
1694 l_total_period_hrs := 0;
1695 END IF;
1696
1697 P_period_amt := l_total_period_hrs;
1698
1699
1700 l_total_period_hrs := l_total_period_hrs + p_hours;
1701 --
1702 -- Calculate net Accrual available if all Absence Elements are taken
1703 P_available_amt := l_amount - l_total_period_hrs;
1704 --
1705 -- Check that Hours to be charge to this Accrual plan are available to take
1706 --
1707 IF l_total_period_hrs <= l_amount THEN
1708 P_abs_status := 0;
1709 ELSE
1710 -- P_abs_status := 1;
1711 P_abs_status := 0;
1712 END IF;
1713 END IF;
1714 --
1715 EXCEPTION
1716 WHEN others THEN
1717 P_abs_status := 3;
1718 --
1719 END CHECK_ABSENCE;
1720 FUNCTION accrual_exceeded( p_tim_id IN NUMBER,
1721 P_calculation_date IN DATE,
1722 P_accrual_plan_name OUT NOCOPY VARCHAR2,
1723 P_accrued_hrs OUT NOCOPY NUMBER,
1724 P_charged_hrs OUT NOCOPY NUMBER) return BOOLEAN IS
1725
1726 -- get all accrual plans for all assignments on this timecard
1727 -- Bug 16203269
1728 -- Commented this below cursor and rewrote it looking at the base
1729 -- tables ( not all of them; we do need just a couple of tables
1730 -- while the cursor with the view was joining too many tables )
1731 /*
1732 CURSOR plan_cur (p_assignment_id NUMBER) is
1733 SELECT vap.assignment_id, vap.accrual_plan_id, vap.accrual_plan_name,
1734 vap.accrual_category, vap.business_group_id
1735 FROM pay_view_accrual_plans_v vap
1736 WHERE vap.assignment_id = p_assignment_id
1737 AND p_calculation_date BETWEEN vap.asg_effective_start_date
1738 AND vap.asg_effective_end_date
1739 AND p_calculation_date BETWEEN vap.iv_effective_start_date
1740 AND vap.iv_effective_end_date
1741 AND p_calculation_date BETWEEN vap.e_entry_effective_start_date
1742 AND vap.e_entry_effective_end_date
1743 AND p_calculation_date BETWEEN vap.e_type_effective_start_date
1744 AND vap.e_type_effective_end_date
1745 AND p_calculation_date BETWEEN vap.e_link_effective_start_date
1746 AND vap.e_link_effective_end_date;
1747
1748 */
1749
1750 CURSOR plan_cur(p_assignment_id NUMBER)
1751 IS
1752 SELECT pee.assignment_id,
1753 pap.accrual_plan_id,
1754 pap.accrual_plan_name,
1755 pap.accrual_category,
1756 pap.business_group_id
1757 FROM pay_element_entries_f pee,
1758 pay_element_links_f pel,
1759 pay_element_types_f pet,
1760 pay_accrual_plans pap
1761 WHERE pee.assignment_id = p_assignment_id
1762 AND p_calculation_date BETWEEN pee.effective_start_date
1763 AND pee.effective_end_date
1764 AND pel.element_link_id = pee.element_link_id
1765 AND p_calculation_date BETWEEN pel.effective_start_date
1766 AND pel.effective_end_date
1767 AND pel.element_type_id = pet.element_type_id
1768 AND p_calculation_date BETWEEN pet.effective_start_date
1769 AND pet.effective_end_date
1770 AND pet.element_type_id = pap.accrual_plan_element_type_id;
1771
1772 -- Bug 16203269
1773 -- Not connected with the bugfix, but we modified this cursor slightly.
1774 -- There was an earlier fix bug 6594490 which, to avoid perf issue
1775 -- called this function for each day ONLY IF THERE ARE mid period
1776 -- changes to the assignment. If no mid period changes, this function
1777 -- will be called only once with calculation date set to timecard end date.
1778 -- This below cursor will fail if there is no worked hours on timecard end date
1779 -- Thus Accrual calculation would not go thru at all.
1780 -- Now we changed to look at the end date or the closest worked date.
1781
1782 /*
1783 CURSOR get_asg_id ( p_tim_id NUMBER, p_calculation_date DATE ) IS
1784 SELECT DISTINCT (assignment_id)
1785 FROM hxt_sum_hours_worked_f
1786 WHERE tim_id = p_tim_id
1787 AND DATE_WORKED=p_calculation_date
1788 AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
1789 */
1790
1791 CURSOR get_asg_id ( p_tim_id NUMBER, p_calculation_date DATE ) IS
1792 SELECT assignment_id
1793 FROM hxt_sum_hours_worked_f
1794 WHERE tim_id = p_tim_id
1795 AND DATE_WORKED<=p_calculation_date
1796 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
1797 ORDER BY date_worked DESC;
1798
1799
1800 -- get total on this timecard for all elements in the
1801 -- net calculation rules for this accrual plan
1802 CURSOR tc_tot_by_net_calc(p_tim_id NUMBER,
1803 p_assignment_id NUMBER,
1804 p_accrual_plan_id NUMBER,
1805 p_bus_group_id NUMBER) IS
1806 -- Bug 6785744
1807 -- Changed the query to look at the base tables instead of the
1808 -- view. The view was over multiple TL tables and was adding
1809 -- to the perf issues here.
1810 -- We may make the same changes to the below cursor for retro total too.
1811
1812 /*
1813 SELECT sum(hrw.hours*(-1)*(ncr.add_or_subtract))
1814 FROM hxt_sum_hours_worked_f hrw,
1815 pay_net_calculation_rules_v ncr
1816 WHERE hrw.tim_id = p_tim_id
1817 and SYSDATE between hrw.effective_start_date
1818 and hrw.effective_end_date
1819 AND hrw.assignment_id = p_assignment_id
1820 AND ncr.business_group_id +0 = p_bus_group_id
1821 AND ncr.accrual_plan_id = p_accrual_plan_id
1822 AND ncr.element_type_id = hrw.element_type_id;
1823 */
1824
1825 SELECT /*+ ORDERED */
1826 SUM(hrw.hours*(-1)*(ncr.add_or_subtract))
1827 FROM hxt_sum_hours_worked_f hrw,
1828 pay_element_types_f pef,
1829 pay_input_values_f piv,
1830 pay_net_calculation_rules ncr
1831 WHERE hrw.tim_id = p_tim_id
1832 AND SYSDATE BETWEEN hrw.effective_start_date
1833 AND hrw.effective_end_date
1834 AND hrw.assignment_id = p_assignment_id
1835 AND pef.element_type_id = hrw.element_type_id
1836 AND SYSDATE BETWEEN pef.effective_start_date
1837 AND pef.effective_end_date
1838 AND piv.element_type_id = pef.element_type_id
1839 AND SYSDATE BETWEEN piv.effective_start_date
1840 AND piv.effective_end_date
1841 AND piv.input_value_id = ncr.input_value_id
1842 AND ncr.business_group_id +0 = p_bus_group_id
1843 AND ncr.accrual_plan_id = p_accrual_plan_id ;
1844
1845
1846 cursor get_max_retro_batch is
1847 select max(batch_id) from pay_batch_headers pbh
1848 where pbh.batch_status='T'
1849 and pbh.batch_id in (select distinct retro_batch_id from hxt_det_hours_worked_f
1850 where tim_id=p_tim_id);
1851
1852
1853 cursor chk_retro_batch_status is
1854 select batch_id from pay_batch_headers pbh
1855 where pbh.batch_status='T'
1856 and pbh.batch_id in (select distinct retro_batch_id from hxt_det_hours_worked_f
1857 where tim_id=p_tim_id);
1858
1859
1860 cursor chk_original_batch_status is
1861 select null from pay_batch_headers pbh
1862 where pbh.batch_status='T'
1863 and pbh.batch_id in (select distinct batch_id from hxt_timecards_f
1864 where id=p_tim_id);
1865
1866 cursor get_retro_total( p_tim_id NUMBER,
1867 p_assignment_id NUMBER,
1868 p_accrual_plan_id NUMBER,
1869 p_bus_group_id NUMBER,
1870 p_batch_id number) is
1871 SELECT nvl(sum(det.hours*(-1)*(ncr.add_or_subtract)),0)
1872 FROM hxt_det_hours_worked_f det,
1873 pay_net_calculation_rules_v ncr
1874 WHERE det.tim_id = p_tim_id
1875 AND det.assignment_id = p_assignment_id
1876 AND ncr.business_group_id = p_bus_group_id
1877 AND ncr.accrual_plan_id = p_accrual_plan_id
1878 AND ncr.element_type_id = det.element_type_id
1879 AND det.retro_batch_id=p_batch_id;
1880
1881 cursor get_org_total( p_tim_id NUMBER,
1882 p_assignment_id NUMBER,
1883 p_accrual_plan_id NUMBER,
1884 p_bus_group_id NUMBER) is
1885 SELECT nvl(sum(det.hours*(-1)*(ncr.add_or_subtract)),0)
1886 FROM hxt_det_hours_worked_f det,
1887 pay_net_calculation_rules_v ncr
1888 WHERE det.tim_id = p_tim_id
1889 AND det.assignment_id = p_assignment_id
1890 AND ncr.business_group_id = p_bus_group_id
1891 AND ncr.accrual_plan_id = p_accrual_plan_id
1892 AND ncr.element_type_id = det.element_type_id
1893 AND det.retro_batch_id is null;
1894
1895
1896
1897
1898 l_charged_hrs NUMBER(7,3);
1899 l_accrued_hrs NUMBER(7,3);
1900 l_batch_id NUMBER;
1901 l_old_total NUMBER;
1902
1903 l_asg_id per_all_assignments_f.assignment_id%TYPE;
1904
1905
1906 BEGIN
1907
1908 OPEN get_asg_id ( p_tim_id, p_calculation_date);
1909 FETCH get_asg_id INTO l_asg_id;
1910 CLOSE get_asg_id;
1911
1912 FOR rec_plan IN plan_cur(l_asg_id) LOOP
1913 -- Get net Accrual plan amount
1914 --
1915 OPEN tc_tot_by_net_calc(p_tim_id,
1916 rec_plan.assignment_id,
1917 rec_plan.accrual_plan_id,
1918 rec_plan.business_group_id);
1919 FETCH tc_tot_by_net_calc into l_charged_hrs;
1920 CLOSE tc_tot_by_net_calc;
1921
1922 l_batch_id:=0;
1923 l_old_total:=0;
1924
1925 --check if timecard has been retro edited and is transferred to payroll
1926
1927 open chk_retro_batch_status ;
1928 fetch chk_retro_batch_status into l_batch_id;
1929
1930 if chk_retro_batch_status %notfound then
1931
1932 -- if timecard has been retro edited but not transferred to payroll
1933 -- or it is new timecard
1934
1935 l_batch_id :=0;
1936 open chk_original_batch_status ;
1937 fetch chk_original_batch_status into l_batch_id;
1938 if chk_original_batch_status %notfound then
1939 --it is new timecard
1940 l_batch_id :=0;
1941 end if;
1942 close chk_original_batch_status ;
1943 else
1944
1945 -- since the retro batch exists get the last batch_id
1946
1947 open get_max_retro_batch ;
1948 fetch get_max_retro_batch into l_batch_id;
1949 close get_max_retro_batch ;
1950 end if;
1951 close chk_retro_batch_status ;
1952
1953
1954
1955 if(l_batch_id is not null ) then
1956
1957 -- get the hours corresponding to retro batch
1958
1959 open get_retro_total(p_tim_id,
1960 rec_plan.assignment_id,
1961 rec_plan.accrual_plan_id,
1962 rec_plan.business_group_id,
1963 l_batch_id);
1964 fetch get_retro_total into l_old_total;
1965
1966 if(get_retro_total%notfound) then
1967 l_old_total:=0;
1968 end if;
1969 close get_retro_total;
1970
1971 else
1972 -- get the hours corresponding to non retro timecard
1973
1974 open get_org_total(p_tim_id,
1975 rec_plan.assignment_id,
1976 rec_plan.accrual_plan_id,
1977 rec_plan.business_group_id);
1978 fetch get_org_total into l_old_total;
1979 if(get_org_total%notfound) then
1980 l_old_total:=0;
1981 end if;
1982 close get_org_total;
1983 end if;
1984
1985 if l_charged_hrs <> 0 then
1986 l_accrued_hrs :=
1987 pay_us_pto_accrual.get_net_accrual( rec_plan.assignment_id,
1988 p_calculation_date,
1989 rec_plan.accrual_plan_id ,
1990 -- rec_plan.accrual_category);
1991 NULL);
1992 if nvl((l_charged_hrs-l_old_total),0) > l_accrued_hrs then
1993 p_accrual_plan_name := rec_plan.accrual_plan_name;
1994 p_charged_hrs := l_charged_hrs;
1995 p_accrued_hrs := l_accrued_hrs;
1996 return TRUE;
1997 end if;
1998 end if;
1999 END LOOP;
2000
2001 RETURN FALSE;
2002 END;
2003
2004 --BEGIN SIR450
2005 FUNCTION get_costable_type(p_element_type_id IN NUMBER,
2006 p_date_worked IN DATE,
2007 p_assignment_id IN NUMBER) return VARCHAR2 IS
2008
2009 CURSOR cur_costable_type IS
2010 SELECT pel.costable_type
2011 FROM pay_element_links_f pel,
2012 per_assignments_f asm
2013 WHERE asm.assignment_id = p_assignment_id
2014 AND p_date_worked BETWEEN asm.effective_start_date
2015 AND asm.effective_end_date
2016 AND nvl(pel.organization_id,nvl(asm.organization_id,-1)) = nvl(asm.organization_id,-1)
2017 AND (pel.people_group_id IS NULL
2018 OR exists (SELECT 'X'
2019 FROM pay_assignment_link_usages_f usage
2020 WHERE usage.assignment_id = asm.assignment_id
2021 AND usage.element_link_id = pel.element_link_id
2022 AND p_date_worked BETWEEN usage.effective_start_date
2023 AND usage.effective_end_date))
2024 AND nvl(pel.job_id, nvl(asm.job_id,-1)) = nvl(asm.job_id,-1)
2025 AND nvl(pel.position_id, nvl(asm.position_id,-1)) = nvl(asm.position_id,-1)
2026 AND nvl(pel.grade_id,nvl(asm.grade_id,-1)) = nvl(asm.grade_id,-1)
2027 AND nvl(pel.location_id,nvl(asm.location_id,-1)) = nvl(asm.location_id,-1)
2028 AND nvl(pel.payroll_id,nvl(asm.payroll_id,-1)) = nvl(asm.payroll_id,-1)
2029 AND nvl(pel.employment_category,nvl(asm.employment_category,-1)) = nvl(asm.employment_category,-1)
2030 AND nvl(pel.pay_basis_id,nvl(asm.pay_basis_id,-1)) = nvl(asm.pay_basis_id,-1)
2031 AND nvl(pel.business_group_id,nvl(asm.business_group_id,-1)) = nvl(asm.business_group_id,-1)
2032 AND p_date_worked BETWEEN pel.effective_start_date
2033 AND pel.effective_end_date
2034 AND pel.element_type_id = p_element_type_id;
2035
2036 l_costable_type VARCHAR2(30);
2037
2038 BEGIN
2039 OPEN cur_costable_type;
2040 FETCH cur_costable_type into l_costable_type;
2041 RETURN(l_costable_type);
2042 EXCEPTION
2043 WHEN others THEN
2044 RETURN('?');
2045 END get_costable_type;
2046
2047 /********************************************************************
2048 * PROCEDURE SET_TIMECARD_ERROR -- ER178 SDM 09-03-98 *
2049 * *
2050 * Purpose *
2051 * Retrieve Error Type From QUICK CODES then value is used to *
2052 * inserting record in HXT_ERRORS table when an error is found *
2053 * regarding Timecard, summary, detailed Hours Worked orPay *
2054 * Period record.of hxt_errors. *
2055 * *
2056 * Arguments *
2057 * p_PBD_ID - The source of the error is the BATCH record *
2058 * p_TIM_ID - The source of the error is the TIMECARD record *
2059 * p_HRW_ID - The source of the error is the Hours Worked record *
2060 * p_PTP_ID - The source of the error is the TIME PERIOD RECORD. *
2061 * p_ERROR_MSG - the error message to show the user *
2062 * p_LOCATION - the Procedure or Source *
2063 * p_ORA_MSG - the ORACLE error number and message if any *
2064 * *
2065 ********************************************************************/
2066 Procedure SET_TIMECARD_ERROR (p_PPB_ID IN NUMBER,
2067 p_TIM_ID IN NUMBER,
2068 p_HRW_ID IN NUMBER,
2069 p_PTP_ID IN NUMBER,
2070 p_ERROR_MSG IN OUT NOCOPY VARCHAR2,
2071 p_LOCATION IN VARCHAR2,
2072 p_ORA_MSG IN VARCHAR2,
2073 p_LOOKUP_CODE IN VARCHAR2,
2074 p_valid OUT NOCOPY VARCHAR,
2075 p_msg_level OUT NOCOPY VARCHAR2) IS
2076
2077 CURSOR tim_dates is
2078 SELECT effective_start_date,
2079 effective_end_date
2080 FROM HXT_TIMECARDS_X
2081 WHERE id = p_tim_id;
2082
2083
2084 -- Local Variables
2085 l_CREATION_DATE DATE; --- the date time of the error
2086 l_CREATED_BY NUMBER; --- the user logged in when the error occurred
2087 l_EXCEP_seqno NUMBER; --- the next sequence number for new error record
2088 l_error_msg VARCHAR2(240);
2089 l_error VARCHAR2(240);
2090 l_meaning VARCHAR2(80);
2091 l_type VARCHAR2(80); --- Error type either 'ERR', 'WRN' or 'SKIP'
2092 l_eff_start DATE;
2093 l_eff_end DATE;
2094 l_person_id NUMBER; -- ER230
2095 l_payroll_id NUMBER; -- ER230
2096 --
2097 --
2098 BEGIN
2099
2100 OPEN tim_dates;
2101 FETCH tim_dates into l_eff_start, l_eff_end;
2102
2103 -- Bug 8584436
2104 -- Modified the code slightly so that the global variable with messages with embedded
2105 -- token values is checked first before the error is inserted.
2106
2107 if tim_dates%FOUND
2108 then
2109
2110 IF hxt_batch_val.g_errtab.EXISTS(p_LOOKUP_CODE)
2111 THEN
2112 p_error_msg := hxt_batch_val.g_errtab(p_lookup_code).errmsg;
2113 l_type := hxt_batch_val.g_errtab(p_lookup_code).errtype;
2114 ELSE
2115
2116
2117 get_quick_codes(p_lookup_code,
2118 'HXT_TIMECARD_VALIDATION',
2119 808,
2120 l_meaning,
2121 l_type);
2122
2123
2124 IF ( l_meaning = 'XXX' )
2125 THEN
2126
2127 -- the USER has modified the lookup_code
2128
2129 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2130 fnd_message.set_token('PROCEDURE', 'HXTUTL');
2131 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||p_lookup_code);
2132 fnd_message.raise_error;
2133 END IF;
2134
2135 FND_MESSAGE.SET_NAME('HXT',l_meaning);
2136 p_error_msg := FND_MESSAGE.GET;
2137 END IF;
2138
2139 IF l_type = 'SKIP' THEN
2140 p_msg_level := ' ';
2141 p_valid := ' ';
2142 RETURN;
2143 ELSIF l_type = 'ERR' THEN
2144 FND_MESSAGE.SET_NAME('HXT','HXT_39503_QUICK_CODE_STOP');
2145 l_error_msg := FND_MESSAGE.GET || p_error_msg;
2146 p_msg_level := 'E';
2147 ELSIF l_type = 'WRN' THEN
2148 FND_MESSAGE.SET_NAME('HXT','HXT_39504_QUICK_CODE_WARN');
2149 l_error_msg := FND_MESSAGE.GET || p_error_msg;
2150 p_msg_level := 'W';
2151 ELSE
2152 FND_MESSAGE.SET_NAME('HXT','HXT_39505_INVALID_QUICK_CODE');
2153 l_error_msg := FND_MESSAGE.GET;
2154 l_type := 'ERR';
2155 p_msg_level := 'E';
2156 END IF;
2157
2158 p_valid := 'N';
2159
2160 SELECT hxt_seqno.nextval
2161 INTO l_EXCEP_seqno
2162 FROM dual;
2163 l_CREATED_BY := FND_GLOBAL.user_id;
2164 l_person_id := GetPersonID(p_TIM_ID); -- ER230
2165 l_payroll_id := GetPayrollID(p_PTP_ID); -- ER230
2166
2167
2168 insert into hxt_errors_f(
2169 id,
2170 error_msg,
2171 creation_date,
2172 location,
2173 created_by,
2174 err_type,
2175 PPB_ID,
2176 TIM_ID,
2177 HRW_ID,
2178 PTP_ID,
2179 ora_message,
2180 EFFECTIVE_START_DATE,
2181 EFFECTIVE_END_DATE
2182 ,PERSON_ID --ER230
2183 ,PAYROLL_ID --ER230
2184 )
2185 values(l_EXCEP_seqno,
2186 substr(nvl(l_ERROR_MSG,'NOTPROVIDED'),1,239),
2187 sysdate,
2188 substr(nvl(p_LOCATION,'NOTPROVIDED'),1,119),
2189 nvl(l_CREATED_BY,-1),
2190 l_TYPE,
2191 p_PPB_id,
2192 p_TIM_id,
2193 p_HRW_ID,
2194 p_PTP_ID,
2195 substr(p_ORA_MSG,1,119),
2196 nvl(l_eff_start,sysdate),
2197 nvl(l_eff_end,hr_general.end_of_time)
2198 ,l_Person_ID /* ER230 */
2199 ,l_Payroll_ID /* ER230 */
2200 );
2201
2202 CLOSE tim_dates;
2203
2204 END IF; -- l_meaning = 'XXX'
2205
2206 EXCEPTION
2207 WHEN others THEN
2208 -- Bug 12919783
2209 hr_utility.trace(dbms_utility.format_error_backtrace);
2210 l_error := SQLERRM;
2211
2212 FND_MESSAGE.SET_NAME('HXT','HXT_39469_ERR_INS_HXT_ERR');
2213 insert into hxt_errors_f(
2214 id,
2215 error_msg,
2216 creation_date,
2217 location,
2218 created_by,
2219 err_type,
2220 PPB_ID,
2221 TIM_ID,
2222 HRW_ID,
2223 PTP_ID,
2224 ora_message,
2225 EFFECTIVE_START_DATE,
2226 EFFECTIVE_END_DATE)
2227 values(l_EXCEP_seqno,
2228 FND_MESSAGE.GET
2229 ||nls_initcap(substr(p_error_msg,1,100)),
2230 sysdate,
2231 'SET_TIMECARD_ERROR',
2232 -1,
2233 'NEW',
2234 NULL,
2235 NULL,
2236 NULL,
2237 NULL,
2238 l_error,
2239 nvl(l_eff_start,sysdate),
2240 nvl(l_eff_end,hr_general.end_of_time)
2241 );
2242
2243 END SET_TIMECARD_ERROR;
2244
2245 /********************************************************************
2246 * PROCEDURE GET_QUICK_CODES -- ER178 SDM 09-03-98 *
2247 * *
2248 * Purpose *
2249 * Retrieve Values from QUICK CODES and retrun values *
2250 * *
2251 * Arguments *
2252 * Inputs *
2253 * p_lookup_code *
2254 * p_lookup_type *
2255 * p_application_id *
2256 * Outputs: *
2257 * p_lookup_meaning *
2258 * p_TAG *
2259 * *
2260 ********************************************************************/
2261
2262 Procedure GET_QUICK_CODES(p_lookup_code IN VARCHAR2,
2263 p_lookup_type IN VARCHAR2,
2264 p_application_id IN NUMBER,
2265 p_lookup_meaning OUT NOCOPY VARCHAR2,
2266 p_lookup_description OUT NOCOPY VARCHAR2) is
2267
2268 CURSOR quick_codes IS
2269 select
2270 meaning,
2271 UPPER(TAG)
2272 FROM fnd_lookup_values
2273 WHERE lookup_code = p_lookup_code
2274 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
2275 AND NVL(end_date_active, SYSDATE)
2276 AND LANGUAGE = userenv('LANG')
2277 AND VIEW_APPLICATION_ID = 3
2278 AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE,VIEW_APPLICATION_ID)
2279 AND enabled_flag = 'Y'
2280 AND lookup_type = p_lookup_type;
2281
2282 BEGIN
2283 OPEN quick_codes;
2284 FETCH quick_codes into p_lookup_meaning, p_lookup_description;
2285 IF quick_codes%notfound then
2286 p_lookup_meaning := 'XXX';
2287 p_lookup_description := 'XXX';
2288 END IF;
2289 CLOSE quick_codes;
2290
2291 END get_quick_codes;
2292
2293 FUNCTION GetPersonID(p_TIM_ID NUMBER) RETURN NUMBER IS
2294 cursor person_cur(c_tim_id NUMBER) is
2295 select tim.for_person_id
2296 from hxt_timecards_x tim
2297 where tim.id = c_tim_id;
2298
2299 l_person_id NUMBER := NULL;
2300
2301 BEGIN
2302 if p_tim_id is null then
2303 return null;
2304 end if;
2305 open person_cur(p_tim_id);
2306 fetch person_cur into l_person_id;
2307 close person_cur;
2308 return l_person_id;
2309 END GetPersonID;
2310
2311
2312 FUNCTION GetPayrollID(p_PTP_ID NUMBER) RETURN NUMBER IS
2313
2314 cursor payroll_cur(c_ptp_id NUMBER) is
2315 select ptp.payroll_id
2316 from per_time_periods ptp
2317 where ptp.time_period_id = c_ptp_id;
2318
2319 l_payroll_id NUMBER := NULL;
2320
2321 BEGIN
2322 if p_ptp_id is null then
2323 return null;
2324 end if;
2325 open payroll_cur(p_ptp_id);
2326 fetch payroll_cur into l_payroll_id;
2327 close payroll_cur;
2328 return l_payroll_id;
2329 END GetPayrollID;
2330
2331 PROCEDURE check_batch_states(P_BATCH_ID IN NUMBER) --3739107
2332 IS
2333 CURSOR c_chk_tc_exists
2334 IS
2335 SELECT 'Y'
2336 FROM pay_batch_headers pbh
2337 WHERE pbh.batch_status <> 'T'
2338 AND pbh.batch_id = p_batch_id
2339 AND EXISTS ((SELECT HTF.batch_id
2340 FROM hxt_timecards_f HTF
2341 WHERE HTF.batch_id = pbh.batch_id)
2342 UNION
2343 (SELECT hdhw.retro_batch_id
2344 FROM hxt_det_hours_worked_f hdhw
2345 WHERE hdhw.retro_batch_id = pbh.batch_id));
2346 l_dummy VARCHAR2 (1);
2347 BEGIN
2348 g_debug :=hr_utility.debug_enabled;
2349 --hr_utility.trace_on(null,'Y');
2350 if g_debug then
2351 hr_utility.trace('inside def');
2352 end if;
2353 OPEN c_chk_tc_exists;
2354 FETCH c_chk_tc_exists INTO l_dummy;
2355
2356 IF (c_chk_tc_exists%FOUND)
2357 THEN
2358 if g_debug then
2359 hr_utility.trace('inside def1');
2360 end if;
2361 HR_UTILITY.SET_MESSAGE(808, 'HXT_39144_CANT_DELETE_BATCH'); --This message is
2362 --not the actuall message, it is just meant for testing purpose
2363 CLOSE c_chk_tc_exists;
2364 hr_utility.raise_error;
2365
2366 END IF;
2367 CLOSE c_chk_tc_exists;
2368 END check_batch_states;
2369
2370
2371
2372 FUNCTION get_week_day(p_date in Date ) RETURN varchar2 IS
2373
2374 cursor weekday_cur(c_date Date) is
2375 select DECODE (MOD (trunc(p_date)-trunc(hr_general.START_OF_TIME),7),
2376 0,'SAT',
2377 1,'SUN',
2378 2,'MON',
2379 3,'TUE',
2380 4,'WED',
2381 5,'THU',
2382 6,'FRI'
2383 )
2384 from dual;
2385
2386
2387
2388 l_week_day varchar2(80) := NULL;
2389 BEGIN
2390 if p_date is null then
2391 return null;
2392 end if;
2393 open weekday_cur(p_date);
2394 fetch weekday_cur into l_week_day;
2395 close weekday_cur;
2396 return l_week_day;
2397
2398 END get_week_day;
2399
2400
2401 -- Commented for the Bug 5651731
2402 /*
2403 FUNCTION is_valid_time_entry (
2404 p_raw_time_in IN hxt_det_hours_worked_f.time_in%TYPE,
2405 p_rounded_time_in IN hxt_det_hours_worked_f.time_in%TYPE,
2406 p_raw_time_out IN hxt_det_hours_worked_f.time_in%TYPE,
2407 p_rounded_time_out IN hxt_det_hours_worked_f.time_in%TYPE
2408 )
2409 RETURN BOOLEAN AS
2410
2411 l_proc VARCHAR2 (30) ;
2412 l_valid_entry BOOLEAN := TRUE;
2413 c_hours_format CONSTANT VARCHAR2 (6) := 'HH24MI';
2414
2415 BEGIN
2416
2417 g_debug :=hr_utility.debug_enabled;
2418 if g_debug then
2419 l_proc := 'is_valid_time_entry';
2420
2421 hr_utility.set_location ( 'Entering: ' || l_proc, 10);
2422 end if;
2423 --Modified for the bug 12601255
2424 if (p_rounded_time_in is not null and p_rounded_time_out is not null)
2425 then
2426
2427 IF (p_rounded_time_in > p_rounded_time_out) then
2428
2429 l_valid_entry := FALSE;
2430 else
2431
2432 l_valid_entry := TRUE;
2433 end if;
2434
2435 else
2436
2437 if (p_raw_time_in is null or p_raw_time_out is null)
2438 then
2439 l_valid_entry := FALSE;
2440
2441 elsif (p_raw_time_in > p_raw_time_out) then
2442
2443 l_valid_entry := FALSE;
2444 else
2445
2446 l_valid_entry := TRUE;
2447
2448 end if;
2449 end if;
2450
2451
2452 if g_debug then
2453 hr_utility.set_location ( 'Leaving: ' || l_proc, 100);
2454 end if;
2455
2456 RETURN l_valid_entry;
2457
2458 END is_valid_time_entry;
2459 */
2460
2461 -- Added the foll for bug 5651731
2462 FUNCTION is_valid_time_entry (
2463 p_raw_time_in IN hxt_det_hours_worked_f.time_in%TYPE, -- :hrw.actual_time_in,
2464 p_rounded_time_in IN hxt_det_hours_worked_f.time_in%TYPE, -- :hrw.time_in
2465 p_raw_time_out IN hxt_det_hours_worked_f.time_in%TYPE, -- :hrw.actual_time_out,
2466 p_rounded_time_out IN hxt_det_hours_worked_f.time_in%TYPE -- :hrw.time_in
2467 )
2468 RETURN BOOLEAN AS
2469
2470 l_proc VARCHAR2 (30) ;
2471 l_valid_entry BOOLEAN := TRUE;
2472 c_hours_format CONSTANT VARCHAR2 (6) := 'HH24MI';
2473
2474 -- Bug 5651731 additions
2475 l_raw_time_in VARCHAR2(100);
2476 l_rounded_time_in VARCHAR2(100);
2477 l_raw_time_out VARCHAR2(100);
2478 l_rounded_time_out VARCHAR2(100);
2479
2480
2481 BEGIN
2482
2483 -- Bug 5651731 additions
2484 l_raw_time_in := TO_CHAR (p_raw_time_in, c_hours_format);
2485 l_rounded_time_in := TO_CHAR (p_rounded_time_in, c_hours_format);
2486 l_raw_time_out := TO_CHAR (p_raw_time_out, c_hours_format);
2487 l_rounded_time_out := TO_CHAR (p_rounded_time_out, c_hours_format);
2488
2489 if l_rounded_time_in = '0000' then
2490 if trunc(p_rounded_time_in) > trunc(p_raw_time_in) then
2491 l_rounded_time_in := '2400';
2492 end if; --
2493 end if; --l_rounded_time_in
2494
2495 if l_rounded_time_out = '0000' then
2496 if trunc(p_rounded_time_out) > trunc(p_raw_time_out) then
2497 l_rounded_time_out := '2400';
2498 end if; --
2499 end if; --l_rounded_time_in
2500
2501
2502
2503
2504 g_debug :=hr_utility.debug_enabled;
2505 if g_debug then
2506 l_proc := 'is_valid_time_entry';
2507
2508 hr_utility.set_location ( 'Entering: ' || l_proc, 10);
2509 end if;
2510
2511 if g_debug then
2512 hr_utility.trace('Input Params - ');
2513 hr_utility.trace('p_raw_time_in = '||p_raw_time_in);
2514 hr_utility.trace('p_rounded_time_in = '||p_rounded_time_in);
2515 hr_utility.trace('p_raw_time_out = '||p_raw_time_out);
2516 hr_utility.trace('p_rounded_time_out = '||p_rounded_time_out);
2517 hr_utility.trace('----------- ');
2518 hr_utility.trace('Hour Values - ');
2519 hr_utility.trace('l_raw_time_in = '||l_raw_time_in);
2520 hr_utility.trace('l_rounded_time_in = '||l_rounded_time_in);
2521 hr_utility.trace('l_raw_time_out = '||l_raw_time_out);
2522 hr_utility.trace('l_rounded_time_out = '||l_rounded_time_out);
2523 hr_utility.trace('----------- ');
2524 end if;
2525
2526 IF ( (
2527 (l_raw_time_in < l_raw_time_out)
2528 AND
2529 (l_rounded_time_in > l_rounded_time_out)
2530 )
2531
2532 OR
2533
2534 (
2535 (l_raw_time_in > l_raw_time_out)
2536 AND
2537 (l_rounded_time_in < l_rounded_time_out)
2538 )
2539 ) THEN
2540 if g_debug then
2541 hr_utility.set_location ( 'Invalid entry: ' || l_proc, 20);
2542 end if;
2543 l_valid_entry := FALSE;
2544 ELSE
2545 if g_debug then
2546 hr_utility.set_location ( 'Valid entry: ' || l_proc, 30);
2547 end if;
2548 l_valid_entry := TRUE;
2549 END IF;
2550
2551 if g_debug then
2552 hr_utility.set_location ( 'Leaving: ' || l_proc, 100);
2553 end if;
2554
2555 RETURN l_valid_entry;
2556
2557 END is_valid_time_entry;
2558
2559 ---------------------------------------
2560
2561
2562 PROCEDURE check_timecard_exists (p_person_id IN NUMBER)
2563 IS
2564 CURSOR csr_chk_tc_exists_hxt
2565 IS
2566 SELECT 'Y'
2567 FROM hxt_timecards_f
2568 WHERE for_person_id = p_person_id;
2569
2570
2571 CURSOR csr_chk_tc_exists_ss
2572 IS
2573 SELECT 'Y'
2574 FROM hxc_time_building_blocks
2575 WHERE resource_id = p_person_id
2576 AND date_to=hr_general.end_of_time
2577 AND ROWNUM<2;
2578
2579 l_dummy VARCHAR2 (1);
2580 BEGIN
2581 OPEN csr_chk_tc_exists_hxt;
2582 FETCH csr_chk_tc_exists_hxt INTO l_dummy;
2583
2584 IF (csr_chk_tc_exists_hxt%FOUND)
2585 THEN
2586 CLOSE csr_chk_tc_exists_hxt;
2587 hr_utility.set_message (808, 'HXT_CANT_DEL_PERSON');
2588 hr_utility.raise_error;
2589 ElSE
2590
2591 CLOSE csr_chk_tc_exists_hxt;
2592
2593 OPEN csr_chk_tc_exists_ss;
2594 FETCH csr_chk_tc_exists_ss INTO l_dummy;
2595
2596 IF (csr_chk_tc_exists_ss%FOUND)
2597 THEN
2598 CLOSE csr_chk_tc_exists_ss;
2599 hr_utility.set_message (808, 'HXT_CANT_DEL_PERSON');
2600 hr_utility.raise_error;
2601 END IF;
2602
2603 CLOSE csr_chk_tc_exists_ss;
2604
2605 END IF;
2606 END check_timecard_exists;
2607
2608
2609 --end ER230
2610 --END HXT11i1
2611
2612 END hxt_util;
[Home] [Help]