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