[Home] [Help]
PACKAGE BODY: APPS.HXT_TIME_GEN
Source
1 PACKAGE BODY hxt_time_gen AS
2 /* $Header: hxttgen.pkb 120.0 2005/05/29 05:58:55 appldev noship $ */
3
4 PROCEDURE call_gen_error (
5 a_hrw_id NUMBER,
6 a_error_text VARCHAR2,
7 a_ora_err_text VARCHAR2 DEFAULT NULL
8 );
9
10 PROCEDURE manage_fnd_sessions; --SIR520
11
12 PROCEDURE update_batch_ref (l_batch_id NUMBER, a_reference_num VARCHAR2); --SIR71
13
14 FUNCTION convert_time (
15 a_date DATE,
16 a_time_in NUMBER,
17 a_time_out NUMBER DEFAULT NULL
18 )
19 RETURN DATE;
20
21 FUNCTION convert_time (
22 a_date DATE,
23 a_time_in VARCHAR2,
24 a_time_out VARCHAR2 DEFAULT NULL
25 )
26 RETURN DATE;
27
28 PROCEDURE del_existing_hrw (a_tim_id NUMBER);
29
30 PROCEDURE del_obsolete_tim;
31
32 FUNCTION chk_timecard_exists (
33 a_payroll_id NUMBER,
34 a_time_period_id NUMBER,
35 a_person_id NUMBER
36 )
37 RETURN NUMBER;
38
39 PROCEDURE get_holiday_info (
40 a_date IN DATE,
41 a_hcl_id IN NUMBER,
42 a_elt_id OUT NOCOPY NUMBER,
43 a_hours OUT NOCOPY NUMBER
44 );
45
46 -- Get all assignment segments valid for this payroll sometime during
47 -- this time period (auto-gen='Y', pay_status='P')
48 CURSOR g_cur_asm (a_payroll_id NUMBER, a_time_period_id NUMBER)
49 IS
50 SELECT ppl.person_id, ppl.last_name, ppl.first_name,
51 asm.assignment_id, asm.business_group_id,
52 asm.assignment_number, asm.time_normal_start normal_start,
53 asm.time_normal_finish normal_finish, asm.normal_hours -- delete C243 by BC
54 --, asmv.hxt_work_plan tws_id
55 ,
56 NULL osp_id, NULL sdf_id, aeiv.hxt_rotation_plan rtp_id,
57 aeiv.hxt_earning_policy egp_id,
58 aeiv.hxt_hour_deduction_policy hdp_id,
59 aeiv.hxt_shift_differential_policy sdp_id
60 -- -- use the latest of ppd_start and asm_start
61 -- use the latest of ptp_start, asm_start, and aeiv_start
62 ,
63 GREATEST (
64 ptp.start_date,
65 asm.effective_start_date,
66 aeiv.effective_start_date
67 )
68 start_date -- use the earliest of ppd_end, asm_end, aeiv_end
69 ,
70 LEAST (
71 ptp.end_date,
72 asm.effective_end_date,
73 aeiv.effective_end_date
74 ) end_date,
75 tim.id tim_id, tim.batch_id, tim.auto_gen_flag,
76 egp.fcl_earn_type egp_type, egp.egt_id, egp.hcl_id, egp.pep_id,
77 egp.pip_id, ptp.ROWID ptp_rowid, egp.effective_start_date,
78 egp.effective_end_date
79 FROM per_time_periods ptp,
80 hxt_timecards tim,
81 per_people_f ppl,
82 hxt_earning_policies egp,
83 hxt_per_aei_ddf_v aeiv,
84 per_assignment_status_types ast,
85 per_assignments_f asm
86 WHERE ptp.payroll_id = asm.payroll_id
87 AND ast.assignment_status_type_id = asm.assignment_status_type_id
88 AND ast.pay_system_status = 'P' -- Check payroll status
89 AND ast.per_system_status = 'ACTIVE_ASSIGN'
90 AND aeiv.assignment_id = asm.assignment_id
91 AND aeiv.effective_start_date <= ptp.end_date
92 AND aeiv.effective_end_date >= ptp.start_date
93 AND aeiv.hxt_autogen_hours_yn = 'Y'
94 AND egp.id(+) = aeiv.hxt_earning_policy
95 -- get policy valid for this time frame
96 AND ( ( egp.effective_start_date <=
97 LEAST (
98 ptp.end_date,
99 asm.effective_end_date,
100 aeiv.effective_end_date
101 )
102 AND egp.effective_end_date >=
103 GREATEST (
104 ptp.start_date,
105 asm.effective_start_date,
106 aeiv.effective_start_date
107 )
108 AND egp.id IS NOT NULL
109 )
110 OR egp.id IS NULL
111 )
112 AND ppl.person_id = asm.person_id
113 -- use ptp.end_date - may be hired after start_date --
114 AND ptp.end_date BETWEEN ppl.effective_start_date
115 AND ppl.effective_end_date
116 AND tim.for_person_id(+) = asm.person_id
117 AND tim.time_period_id(+) = a_time_period_id
118 -- ignore timecards that have been manually entered, we will report to the user on these later
119 -- get all assignments valid sometime during pay period --
120 AND asm.effective_start_date <= ptp.end_date
121 AND asm.effective_end_date >= ptp.start_date
122 AND ptp.time_period_id = a_time_period_id
123 AND ptp.payroll_id = a_payroll_id
124 AND not exists -- added 2772781
125 ( -- added 2772781
126 select '1' from HXT_BATCH_STATES -- added 2772781
127 where (hxt_batch_states.STATUS ='VT' and hxt_batch_states.batch_id=tim.batch_id) -- added 2772781
128 ) -- added 2772781
129 ORDER BY tim.batch_id,
130 ppl.person_id,
131 asm.assignment_number,
132 GREATEST (
133 ptp.start_date,
134 asm.effective_start_date,
135 aeiv.effective_start_date
136 );
137
138 g_asm_rec g_cur_asm%ROWTYPE;
139
140
141 ------------------------------------------------------------------
142 -- PUBLIC --
143 ------------------------------------------------------------------
144 PROCEDURE generate_time (
145 errbuf OUT NOCOPY VARCHAR2,
146 retcode OUT NOCOPY NUMBER,
147 a_payroll_id IN NUMBER,
148 a_time_period_id IN NUMBER,
149 a_reference_number IN VARCHAR2
150 )
151 IS --SPR C167 BY BC
152 -- Declare local variables
153 l_batch_id hxt_timecards.batch_id%TYPE;
154 l_pre_bat_id hxt_timecards.batch_id%TYPE DEFAULT 0; --SPR C362 by BC
155 l_person_id hxt_timecards.for_person_id%TYPE; -- null to see new person in first loop
156 l_tim_id hxt_timecards.id%TYPE; -- to hold tim_id for same person
157 l_request_id NUMBER := fnd_profile.VALUE ('CONC_REQUEST_ID');
158 l_tim_cntr NUMBER := g_batch_size; -- set to create batch in first loop
159 l_retcode NUMBER;
160 l_errors EXCEPTION;
161 v_retcode NUMBER := 0; --SIR60
162 a_person_id NUMBER; --SIR60
163 l_reference_number pay_batch_headers.batch_reference%TYPE
164 := a_reference_number;
165 l_tccount BOOLEAN; -- added 2772781
166 --this flag is used to indicate that at
167 --least one timecard has got autogenerated or
168 --change.If it remains false then log file will show message
169 --saying there no timecard is generated.
170 BEGIN
171 manage_fnd_sessions;
172 -- Save parameters to globals
173 g_payroll_id := a_payroll_id;
174 g_time_period_id := a_time_period_id;
175 -- Delete timecards that were autogen'ed for this period that won't be auto-
176 -- gen'ed again. Hours worked and error records will be cascade-deleted.
177 g_err_loc := 'Autogen '; -- SPR C336 by BC
178 g_autogen_error := NULL;
179 g_sub_loc := 'Del_Obsolete_Tim';
180 del_obsolete_tim;
181 -- Step through assignment segments
182 g_sub_loc := 'Cursor G_Cur_Asm';
183 l_tccount:=FALSE; -- added 2772781
184
185 FOR asm_rec IN g_cur_asm (g_payroll_id, g_time_period_id)
186 LOOP
187 -- Populate global record
188 g_asm_rec := asm_rec;
189 a_person_id := g_asm_rec.person_id; --SIR60
190 g_bus_group_id := g_asm_rec.business_group_id; --GLOBAL
191 l_tccount:=TRUE; -- added 2772781
192 -- Use block to handle exceptions and continue loop
193 BEGIN
194 v_retcode := chk_timecard_exists (
195 a_payroll_id,
196 a_time_period_id,
197 a_person_id
198 ); --SIR60
199
200 IF v_retcode = 1
201 THEN --SIR60
202 -- Check if different person from last loop
203 IF g_asm_rec.person_id <> NVL (l_person_id, 0)
204 THEN
205 -- Check if no timecard exists
206 IF g_asm_rec.tim_id IS NULL
207 THEN
208 -- Create batch if timecard/batch limit is reached
209 g_sub_loc := 'Create_Batch';
210
211 --BEGIN SPR C167 BY BC
212 IF l_reference_number IS NULL
213 THEN
214 g_autogen_error := NULL;
215 hxt_user_exits.define_reference_number (
216 g_payroll_id,
217 g_time_period_id,
218 g_asm_rec.assignment_id,
219 g_asm_rec.person_id,
220 g_user_name,
221 'A', --CLOCK BY BC
222 l_reference_number,
223 g_autogen_error
224 );
225
226 IF g_autogen_error IS NOT NULL
227 THEN
228 RAISE g_form_level_error;
229 END IF;
230 END IF;
231
232 l_retcode :=
233 create_batch (l_tim_cntr, l_reference_number);
234
235 -- Save new batch ID and reset timecard counter if new batch created
236 IF l_retcode IS NOT NULL
237 THEN
238 l_batch_id := l_retcode;
239 l_tim_cntr := 0;
240 COMMIT;
241 END IF;
242
243 -- Create timecard record and save id in cursor global
244 g_sub_loc := 'Create_Timecard';
245 g_asm_rec.batch_id := l_batch_id; -- SPR C349 by BC
246 g_asm_rec.tim_id := create_timecard (l_batch_id);
247 l_tim_cntr := l_tim_cntr
248 + 1;
249 -- Otherwise, timecard already generated
250 ELSE
251 -- Delete prior entries, including any errors
252 g_sub_loc := 'Del_Existing_Hrw';
253 del_existing_hrw (g_asm_rec.tim_id);
254
255 -- Set who update columns on timecard
256 -- Set previous batches back to a hold status
257 IF l_pre_bat_id <> g_asm_rec.batch_id
258 THEN
259 hxt_batch_process.set_batch_status (
260 NULL,
261 g_asm_rec.batch_id,
262 'H'
263 );
264 END IF;
265
266 l_pre_bat_id := g_asm_rec.batch_id;
267
268 IF l_reference_number IS NOT NULL
269 THEN
270 g_sub_loc := 'Update_Batch_Ref';
271 l_batch_id := g_asm_rec.batch_id;
272 update_batch_ref (l_batch_id, l_reference_number);
273 END IF;
274 --end SPR C362 by BC
275 -- end timecard exists or not
276 END IF;
277 -- restore previous timecard id
278 ELSE
279 g_asm_rec.tim_id := l_tim_id;
280 END IF; -- end same person or not
281 END IF; --SIR60 end if v_retcode 1;
282
283 -- Create hours worked records.
284 -- On the assignment flex, the user is only allowed to set autogen to Y with a rotation plan.
285 -- begin C257 C261 J35 by BC
286 IF v_retcode = 1
287 THEN --SIR60
288 g_sub_loc := 'Gen_Rot_Plan';
289
290 IF g_asm_rec.egp_id IS NULL
291 THEN -- SPR C258 by BC
292 fnd_message.set_name ('HXT', 'HXT_39287_AG_ERR_NO_ERN_POL');
293 fnd_message.set_token (
294 'ASSIGN',
295 g_asm_rec.assignment_number
296 );
297 g_autogen_error := '';
298 RAISE g_form_level_error;
299 END IF;
300
301 IF g_asm_rec.rtp_id IS NULL
302 THEN
303 fnd_message.set_name ('HXT', 'HXT_39289_AG_ERR_NO_ROT_PLAN');
304 fnd_message.set_token (
305 'ASSIGN',
306 g_asm_rec.assignment_number
307 );
308 g_autogen_error := '';
309 RAISE g_form_level_error;
310 END IF;
311
312 IF g_asm_rec.effective_start_date > g_asm_rec.end_date
313 THEN
314 fnd_message.set_name ('HXT', 'HXT_39326_INV_ERN_STRT_DATE');
315 g_autogen_error := '';
316 RAISE g_form_level_error;
317 END IF;
318
319 IF g_asm_rec.effective_end_date < g_asm_rec.start_date
320 THEN
321 fnd_message.set_name ('HXT', 'HXT_39325_ERN_POL_EXP');
322 g_autogen_error := '';
323 RAISE g_form_level_error;
324 END IF;
325
326 -- generate all autogen hours
327 IF g_asm_rec.start_date = NULL
328 THEN
329 fnd_message.set_name ('HXT', 'HXT_39310_START_DATE_NF');
330 g_autogen_error := '';
331 RAISE g_form_level_error;
332 END IF;
333
334 IF g_asm_rec.end_date = NULL
335 THEN
336 fnd_message.set_name ('HXT', 'HXT_39309_END_DATE_NF');
337 g_autogen_error := '';
338 RAISE g_form_level_error;
339 END IF;
340
341 gen_rot_plan (
342 g_asm_rec.start_date,
343 g_asm_rec.end_date,
344 g_asm_rec.rtp_id
345 );
346 END IF;
347 EXCEPTION
348 -- insert user error and tech error to hxt_errors
349 WHEN g_date_worked_error
350 THEN
351 call_gen_error (NULL, g_autogen_error, g_sqlerrm);
352
353 --SIR015 Set_Updated_By('E');
354 g_sqlerrm := NULL;
355 g_errors := TRUE; -- SPR C389
356 WHEN g_form_level_error
357 THEN
358 call_gen_error (NULL, g_autogen_error, g_sqlerrm);
359
360 --SIR015 Set_Updated_By('E');
361 g_sqlerrm := NULL;
362 g_errors := TRUE; -- SPR C389
363 END; -- end exception block
364
365 -- Save id of current person being processed
366 l_person_id := g_asm_rec.person_id;
367 -- Save id of current timecard being processed
368 l_tim_id := g_asm_rec.tim_id;
369 -- Reset error location
370 g_err_loc := 'Autogen '; -- SPR C336 by BC
371 END LOOP; -- autogen loop
372
373 --if l_count variable is FALSE then, no timecards have
374 --been changed ot have been autogenerated.
375
376 -- Check for errors
377 IF g_errors
378 THEN
379 -- begin SPR C348 by BC
380 fnd_message.set_name ('HXT', 'HXT_39364_AUTOGEN_COMP_W_ERRS');
381 errbuf := fnd_message.get;
382 fnd_message.CLEAR;
383 retcode := 2;
384 ELSE
385
386 If not l_tccount then -- added 2772781
387 FND_MESSAGE.SET_NAME('HXT','HXT_AUTOGEN_PROCESS'); -- added 2772781
388 else
389 fnd_message.set_name ('HXT', 'HXT_39365_AUTOGEN_COMP_NORM');
390 end IF;
391 errbuf := fnd_message.get;
392 fnd_message.CLEAR;
393 retcode := 0;
394 END IF;
395
396 DELETE FROM fnd_sessions
397 WHERE session_id = USERENV ('SESSIONID');
398
399 COMMIT;
400 EXCEPTION
401 WHEN g_del_obs_tim_error
402 THEN
403 DELETE FROM fnd_sessions
404 WHERE session_id = USERENV ('SESSIONID');
405
406 COMMIT;
407 errbuf := g_sqlerrm;
408 fnd_message.CLEAR;
409 call_gen_error (NULL, g_sqlerrm, SQLERRM);
410 retcode := 2;
411 WHEN OTHERS
412 THEN
413 DELETE FROM fnd_sessions
414 WHERE session_id = USERENV ('SESSIONID');
415
416 fnd_message.set_name ('HXT', 'HXT_39366_AUTOGEN_SYST_ERR');
417 fnd_message.set_token ('SQLERR', SQLERRM);
418 errbuf := fnd_message.get;
419 fnd_message.CLEAR;
420 retcode := 2;
421 COMMIT;
422 END;
423
424
425 ------------------------------------------------------------------
426 PROCEDURE get_work_day(
427 a_date IN DATE,
428 a_work_id IN NUMBER,
429 a_osp_id OUT NOCOPY NUMBER,
430 a_sdf_id OUT NOCOPY NUMBER,
431 a_standard_start OUT NOCOPY NUMBER,
432 a_standard_stop OUT NOCOPY NUMBER,
433 a_early_start OUT NOCOPY NUMBER,
434 a_late_stop OUT NOCOPY NUMBER --SIR212
435 ,
436 a_hours OUT NOCOPY NUMBER
437 )
438 IS --SIR212
439 --
440 -- Procedure GET_WORK_DAY
441 -- Purpose: Gets shift diff and off-shift premium for the person's
442 -- assigned shift on an input date
443 --
444 -- Returns p_error:
445 -- 0 - No errors occured
446 -- Other - Oracle error number
447 --
448 --
449 --
450 -- Modification Log:
451 --
452 --
453 CURSOR work_day (a_wp_id NUMBER, a_date DATE)
454 IS
455 SELECT wsh.off_shift_prem_id, wsh.shift_diff_ovrrd_id,
456 sht.standard_start, sht.standard_stop, sht.early_start,
457 sht.late_stop, sht.hours
458 FROM hxt_shifts sht,
459 hxt_weekly_work_schedules wws,
460 hxt_work_shifts wsh
461 WHERE wsh.week_day = hxt_util.get_week_day(a_date)
462 AND wws.id = wsh.tws_id
463 AND a_date BETWEEN wws.date_from AND NVL (wws.date_to, a_date)
464 AND wws.id = a_work_id
465 AND sht.id = wsh.sht_id;
466 BEGIN
467 -- Get shift diff and off-shift premiums
468 OPEN work_day (a_work_id, a_date);
469 FETCH work_day INTO a_osp_id,
470 a_sdf_id,
471 a_standard_start,
472 a_standard_stop,
473 a_early_start,
474 a_late_stop,
475 a_hours;
476 CLOSE work_day;
477
478 --
479 EXCEPTION
480 WHEN OTHERS
481 THEN
482
483 --HXT11 g_autogen_error := 'ERROR Get_Work_Day (' || SQLERRM || ')';
484 fnd_message.set_name ('HXT', 'HXT_39367_GET_WRK_DAY_ERR');
485 fnd_message.set_token ('SQLERR', SQLERRM);
486 g_autogen_error := '';
487 g_sqlerrm := SQLERRM;
488 call_gen_error (NULL, g_autogen_error, g_sqlerrm); --SPR C389
489 g_errors := TRUE; --SPR C389
490 END get_work_day;
491
492
493 -----------------------------------------------------------------
494 PROCEDURE gen_work_plan (a_start DATE, a_end DATE, a_tws_id NUMBER)
495 IS
496 -- Purpose
497 -- Generate hours worked records FOR employees who have a work plan.
498 l_location VARCHAR2 (100);
499 l_days NUMBER;
500 l_time_in hxt_det_hours_worked.time_in%TYPE; --C421
501 l_time_out hxt_det_hours_worked.time_out%TYPE; --C421
502 l_standard_start NUMBER;
503 l_standard_stop NUMBER;
504 l_early_start NUMBER;
505 l_late_stop NUMBER;
506 l_error NUMBER;
507 l_hours NUMBER;
508 -- l_group_id hxt_sum_hours_worked.group_id%TYPE default null;
509 BEGIN
510 -- Update location path with function name
511 g_err_loc := 'Autogen Workplan';
512 -- Get number of days to be generated
513 l_days := a_end
514 - a_start;
515
516 -- Loop through number of days passed
517 -- Get_Group_ID(l_group_id);
518 FOR i IN 0 .. l_days
519 LOOP
520 get_work_day (
521 a_start
522 + i,
523 a_tws_id,
524 g_osp_id,
525 g_sdf_id,
526 l_standard_start,
527 l_standard_stop,
528 l_early_start,
529 l_late_stop,
530 l_hours
531 );
532
533 -- Create summary record - if holiday, time_out may be changed
534 IF (l_hours IS NULL)
535 THEN --SIR212
536 l_time_in := convert_time ( a_start
537 + i, l_standard_start);
538 l_time_out := convert_time (
539 a_start
540 + i,
541 l_standard_start,
542 l_standard_stop
543 );
544 END IF; --SIR212
545
546 create_hrw (
547 g_asm_rec.assignment_id,
548 a_start
549 + i,
550 g_asm_rec.tim_id,
551 l_time_in,
552 l_time_out,
553 a_start,
554 l_hours
555 );
556 -- , l_group_id);
557 END LOOP;
558 EXCEPTION
559 WHEN OTHERS
560 THEN
561 fnd_message.set_name ('HXT', 'HXT_39368_GEN_WRK_PLAN_ERR');
562 fnd_message.set_token ('SQLERR', SQLERRM);
563 g_autogen_error := '';
564 hxt_util.DEBUG (g_autogen_error);
565 g_sqlerrm := SQLERRM;
566 call_gen_error (NULL, g_autogen_error, g_sqlerrm);
567 g_errors := TRUE; --SPR C389
568 END gen_work_plan;
569
570
571 ------------------------------------------------------------------
572 PROCEDURE gen_rot_plan (a_start DATE, a_end DATE, a_rtp_id NUMBER)
573 IS
574
575 -- Purpose
576 -- Generate hours worked records FOR employees who have a work plan
577 -- and rotation plan.
578 CURSOR cur_sch (c_start DATE, c_end DATE, c_rtp_id NUMBER)
579 IS
580 SELECT rt1.tws_id,
581 -- Use the latest of rotation plan start dates or assignment start date
582 TRUNC (
583 DECODE (
584 SIGN ( rt1.start_date
585 - c_start),
586 -1, c_start,
587 rt1.start_date
588 )
589 ) start_date,
590 -- Use the earliest of rotation plan end dates or assignment end date
591 NVL (
592 TRUNC (
593 DECODE (
594 SIGN ( MIN ( rt2.start_date
595 - 1)
596 - c_end),
597 -1, MIN ( rt2.start_date
598 - 1),
599 c_end
600 )
601 ),
602 hr_general.end_of_time
603 ) end_date
604 FROM hxt_rotation_schedules rt1, hxt_rotation_schedules rt2
605 WHERE rt1.rtp_id = rt2.rtp_id(+)
606 AND rt2.start_date(+) > rt1.start_date
607 AND rt1.rtp_id = c_rtp_id
608 AND c_end >= rt1.start_date
609 GROUP BY rt1.tws_id, rt1.start_date
610 HAVING c_start <=
611 NVL ( MIN (rt2.start_date)
612 - 1, hr_general.end_of_time)
613 ORDER BY rt1.start_date;
614
615 l_cntr NUMBER := 1;
616 BEGIN
617 -- Set error location
618 g_err_loc := 'Autogen Rotation'; -- SPR C336 by BC
619
620 <<sch_rec>>
621 g_sub_loc := 'Cursor Cur_Sch';
622
623 FOR sch_rec IN cur_sch (a_start, a_end, a_rtp_id)
624 LOOP
625 -- Report error if missing time on first pass
626 IF (l_cntr = 1) AND (a_start <> sch_rec.start_date)
627 THEN
628 fnd_message.set_name ('HXT', 'HXT_39288_AG_ERR_DATES');
629 fnd_message.set_token (
630 'A_START',
631 fnd_date.date_to_chardate (a_start)
632 );
633 fnd_message.set_token (
634 'START_DATE',
635 fnd_date.date_to_chardate (sch_rec.start_date)
636 );
637 call_gen_error (NULL, '');
638 END IF;
639
640 -- Otherwise, generate time
641 g_sub_loc := 'Gen_Work_Plan';
642 gen_work_plan (sch_rec.start_date, sch_rec.end_date, sch_rec.tws_id);
643 -- Reset error location
644 g_err_loc := 'Autogen Rotation';
645 -- Increment loop counter
646 l_cntr := l_cntr
647 + 1;
648 END LOOP;
649 EXCEPTION
650 WHEN OTHERS
651 THEN
652 g_sqlerrm := SQLERRM;
653 RAISE g_form_level_error;
654 END;
655
656
657 ------------------------------------------------------------------
658 FUNCTION create_batch (a_tim_cntr NUMBER, a_reference_num VARCHAR2)
659 RETURN NUMBER
660 IS
661 l_batch_id pay_batch_headers.batch_id%TYPE;
662 l_batch_name pay_batch_headers.batch_name%TYPE;
663 l_object_version_number pay_batch_headers.object_version_number%TYPE;
664 BEGIN
665 -- Check if batch limit exceeded
666 IF a_tim_cntr >= g_batch_size
667 THEN
668 -- Get next batch number
669 g_sub_loc := 'Get_Next_Batch_Id';
670 -- l_batch_id := get_next_batch_id;
671
672 -- create a batch first
673 pay_batch_element_entry_api.create_batch_header (
674 p_session_date=> g_sysdatetime,
675 p_batch_name=> to_char(sysdate, 'DD-MM-RRRR HH24:MI:SS'),
676 p_batch_status=> 'U',
677 p_business_group_id=> g_bus_group_id,
678 p_action_if_exists=> 'I',
679 p_batch_reference=> a_reference_num,
680 p_batch_source=> 'OTM',
681 p_purge_after_transfer=> 'N',
682 p_reject_if_future_changes=> 'N',
683 p_batch_id=> l_batch_id,
684 p_object_version_number=> l_object_version_number
685 );
686 -- from the batch id, get the batch name
687
688 hxt_user_exits.define_batch_name (
689 l_batch_id,
690 l_batch_name,
691 g_autogen_error
692 );
693 g_sub_loc := 'INSERT INTO pay_batch_headers';
694 --update the batch name
695 pay_batch_element_entry_api.update_batch_header (
696 p_session_date=> g_sysdatetime,
697 p_batch_id=> l_batch_id,
698 p_object_version_number=> l_object_version_number,
699 p_action_if_exists=> 'I',
700 p_batch_name=> l_batch_name,
701 p_batch_reference=> a_reference_num,
702 p_batch_source=> 'OTM',
703 p_batch_status=> 'U',
704 p_purge_after_transfer=> 'N',
705 p_reject_if_future_changes=> 'N'
706 );
707
708 /* INSERT INTO pay_batch_headers
709 (batch_id, business_group_id, batch_name, batch_status,
710 action_if_exists, batch_reference, batch_source,
711 purge_after_transfer, reject_if_future_changes,
712 created_by, creation_date, last_updated_by,
713 last_update_date, last_update_login)
714 VALUES (l_batch_id, g_bus_group_id, l_batch_name, 'U',
715 'I', a_reference_num, 'OTM',
716 'N', 'N',
717 g_user_id, g_sysdatetime, g_user_id,
718 g_sysdatetime, g_login_id);*/
719 END IF;
720
721 RETURN (l_batch_id);
722 EXCEPTION
723 WHEN OTHERS
724 THEN
725 fnd_message.set_name ('HXT', 'HXT_39284_AG_ERR_WRT_BATCHID');
726 fnd_message.set_token ('BATCH_ID', TO_CHAR (l_batch_id));
727 g_autogen_error := '';
728 g_sqlerrm := SQLERRM;
729 RAISE g_form_level_error;
730 END create_batch;
731
732
733 ---------------------------------------------------------------------
734
735 PROCEDURE update_batch_ref (l_batch_id NUMBER, a_reference_num VARCHAR2)
736 IS
737
738
739 CURSOR c_ovn is
740 Select object_version_number
741 From pay_batch_headers
742
743 Where batch_id = l_batch_id;
744
745 l_object_version_number pay_batch_headers.object_version_number%TYPE;
746
747 BEGIN
748
749
750 Open c_ovn;
751 Fetch c_ovn into l_object_version_number;
752 Close c_ovn;
753
754 pay_batch_element_entry_api.update_batch_header (
755 p_session_date => g_sysdatetime,
756 p_batch_id=> l_batch_id,
757 p_batch_reference=> a_reference_num,
758 p_object_version_number => l_object_version_number
759 );
760
761 COMMIT;
762 EXCEPTION
763 WHEN OTHERS
764 THEN
765 fnd_message.set_name ('HXT', 'HXT_39465_AG_ERR_WRT_REF_ID');
766 fnd_message.set_token ('REF_ID', TO_CHAR (l_batch_id));
767 g_autogen_error := '';
768 g_sqlerrm := SQLERRM;
769 RAISE g_form_level_error;
770 END update_batch_ref;
771
772
773 ------------------------------------------------------------------
774 FUNCTION create_timecard (a_batch_id NUMBER DEFAULT NULL)
775 RETURN NUMBER
776 IS
777 -- PUBLIC procedure to create timecard record.
778 l_tim_id NUMBER;
779 BEGIN
780 -- Get next sequence number
781 g_sub_loc := 'Get_hxt_Seqno';
782 l_tim_id := get_hxt_seqno;
783 -- Insert timecard
784 g_sub_loc := 'INSERT into hxt_timecards';
785
786 INSERT INTO hxt_timecards_f
787 (id, for_person_id, payroll_id,
788 time_period_id, batch_id, auto_gen_flag, created_by,
789 creation_date, last_updated_by, last_update_date,
790 last_update_login, effective_start_date,
791 effective_end_date)
792 VALUES (l_tim_id, g_asm_rec.person_id, g_payroll_id,
793 g_time_period_id, a_batch_id, 'A', g_user_id,
794 g_sysdatetime, g_user_id, g_sysdatetime,
795 g_login_id, g_sysdate,
796 hr_general.end_of_time);
797
798 RETURN (l_tim_id);
799 EXCEPTION
800 WHEN OTHERS
801 THEN --DEBUG ONLY
802 fnd_message.set_name ('HXT', 'HXT_39285_AG_ERR_WRT_TIMCARD');
803 fnd_message.set_token ('FIRST_NAME', g_asm_rec.first_name);
804 fnd_message.set_token ('LAST_NAME', g_asm_rec.last_name);
805 fnd_message.set_token ('EMP_NUMBER', TO_CHAR (g_asm_rec.person_id));
806 g_autogen_error := '';
807 g_sqlerrm := SQLERRM;
808 RAISE g_form_level_error;
809 END create_timecard;
810
811
812 -------------------------------------------------------------------------------------------------
813 PROCEDURE create_hrw (
814 a_assignment_id NUMBER,
815 a_date_worked DATE,
816 a_tim_id NUMBER,
817 a_time_in DATE,
818 a_time_out DATE,
819 a_start DATE,
820 a_hours NUMBER
821 )
822 IS
823 -- , a_group_id IN NUMBER) IS
824 -- PUBLIC procedure to create hour worked record - returns incremented seqno.
825 -- Calls HXT_TIME_SUMMARY.Generate_Details
826 l_hrw_id hxt_det_hours_worked.id%TYPE;
827 l_elt_id hxt_det_hours_worked.element_type_id%TYPE;
828 l_seqno hxt_det_hours_worked.seqno%TYPE; --C421
829 l_hours hxt_det_hours_worked.hours%TYPE DEFAULT NULL;
830 l_time_out hxt_det_hours_worked.time_out%TYPE := a_time_out;
831 l_hol_yn VARCHAR2 (1);
832 l_retcode NUMBER;
833 l_seq_exceptn EXCEPTION;
834 l_time_in hxt_det_hours_worked.time_in%TYPE := a_time_in;
835 l_rowid ROWID;
836 v_count NUMBER;
837 -- l_group_id NUMBER;
838 BEGIN
839 -- Check for a holiday
840 g_sub_loc := 'Get_Holiday_Info';
841 l_elt_id := NULL;
842 get_holiday_info (a_date_worked, g_asm_rec.hcl_id, l_elt_id, l_hours);
843
844
845 /*
846 l_group_id := a_group_id;
847 if (l_elt_id IS NOT NULL) then
848 Get_Group_ID(l_group_id);
849 end if;
850 */
851 -- Derive time-out if holiday
852 IF l_hours IS NOT NULL
853 THEN
854 IF ( fnd_profile.VALUE ('HXT_HOL_HOURS_FROM_HOL_CAL') = 'Y'
855 OR fnd_profile.VALUE ('HXT_HOL_HOURS_FROM_HOL_CAL') IS NULL
856 )
857 THEN --SIR212
858 l_time_out := NULL; -- SPR C332 by BC
859 l_time_in := NULL; -- SPR C332 by BC
860 ELSIF a_hours IS NOT NULL
861 THEN
862 l_hours := a_hours;
863 l_time_out := NULL;
864 l_time_in := NULL;
865 ELSE
866 l_hours := 24 * ( a_time_out
867 - a_time_in
868 );
869
870 IF l_hours = 0
871 THEN
872 l_time_out := NULL;
873 l_time_in := NULL;
874 ELSE
875 l_time_out := a_time_out;
876 l_time_in := a_time_in;
877 END IF;
878 END IF;
879
880 l_hol_yn := 'Y';
881 -- Otherwise, use time-out passed and derive hours
882 ELSE
883 --l_time_out := a_time_out;
884 IF a_hours IS NOT NULL
885 THEN
886 l_hours := a_hours;
887 l_time_out := NULL;
888 l_time_in := NULL;
889 ELSE
890 l_hours := 24 * ( a_time_out
891 - a_time_in
892 );
893
894 IF (l_hours = 0)
895 THEN
896 l_time_out := NULL;
897 l_time_in := NULL;
898 END IF;
899 END IF;
900
901 l_hol_yn := 'N';
902 END IF;
903
904 -- Get ID
905 g_sub_loc := 'Get_hxt_Seqno';
906 l_hrw_id := get_hxt_seqno;
907
908 IF l_hrw_id IS NULL
909 THEN -- C257 C261 by BC
910 RAISE l_seq_exceptn;
911 END IF;
912
913 -- Get next available line seqno
914 g_sub_loc := 'HXT_UTIL.Get_Next_Seqno';
915
916 IF g_sub_loc IS NULL
917 THEN -- C257 C261 by BC
918 RAISE l_seq_exceptn;
919 END IF;
920
921 l_seqno := hxt_util.get_next_seqno (a_tim_id, a_date_worked);
922
923 IF l_seqno IS NULL
924 THEN -- C257 C261 by BC
925 RAISE l_seq_exceptn;
926 END IF;
927
928 -- Insert hour worked record
929 g_sub_loc := 'INSERT into hxt_hours_worked';
930
931
932 --SIR012 INSERT into hxt_sum_hours_worked --C431
933 -- 1704149 Commented the lines which insert values into WHO columns.
934 INSERT INTO hxt_sum_hours_worked_f
935 (id, tim_id, date_worked, seqno,
936 hours, assignment_id, element_type_id, time_in, time_out
937 -- , created_by
938 -- , creation_date
939 -- , last_updated_by
940 -- , last_update_date
941 -- , last_update_login
942 ,
943 effective_start_date, effective_end_date, earn_pol_id)
944 -- , group_id)
945 VALUES (l_hrw_id, g_asm_rec.tim_id, a_date_worked, l_seqno,
946 l_hours, a_assignment_id, l_elt_id, l_time_in, l_time_out
947 -- , g_user_id
948 -- , g_sysdatetime
949 -- , g_user_id
950 -- , g_sysdatetime
951 -- , g_login_id
952 ,
953 g_sysdate, hr_general.end_of_time, g_asm_rec.egp_id);
954
955 -- , l_group_id);
956 COMMIT;
957
958 -- begin SIR012. need rowid for call to generate_details.
959 SELECT ROWID
960 INTO l_rowid
961 FROM hxt_sum_hours_worked_f
962 WHERE id = l_hrw_id;
963
964 -- Create detail records
965 g_sub_loc := 'HXT_TIME_SUMMARY.Generate_Details';
966 l_retcode :=
967 hxt_time_summary.generate_details (
968 g_asm_rec.egp_id,
969 g_asm_rec.egp_type,
970 g_asm_rec.egt_id,
971 g_asm_rec.sdp_id,
972 g_asm_rec.hdp_id,
973 g_asm_rec.hcl_id -- Fassadi 13/MAR/01 bug 1680151 was fixed.
974 ,
975 g_asm_rec.pep_id,
976 g_asm_rec.pip_id,
977 g_sdf_id --SPR C389
978 ,
979 g_osp_id --SPR C389
980 ,
981 NULL -- standard_start
982 ,
983 NULL -- standard_stop
984 ,
985 NULL -- early_start
986 ,
987 NULL -- late_stop
988 ,
989 l_hol_yn,
990 g_asm_rec.person_id,
991 g_err_loc,
992 l_hrw_id,
993 a_tim_id,
994 a_date_worked,
995 g_asm_rec.assignment_id,
996 l_hours,
997 l_time_in -- SPR C332 by BC
998 ,
999 l_time_out,
1000 l_elt_id -- element_type_id
1001 ,
1002 NULL -- FCL_EARN_REASON_CODE
1003 ,
1004 NULL -- FFV_COST_CENTER_ID
1005 ,
1006 NULL -- FFV_LABOR_ACCOUNT_ID
1007 ,
1008 NULL -- TAS_ID
1009 ,
1010 NULL -- LOCATION_ID
1011 ,
1012 NULL -- SHT_ID
1013 ,
1014 NULL -- HRW_COMMENT
1015 ,
1016 NULL -- FFV_RATE_CODE_ID
1017 ,
1018 NULL -- RATE_MULTIPLE
1019 ,
1020 NULL -- HOURLY_RATE
1021 ,
1022 NULL -- AMOUNT
1023 ,
1024 NULL -- FCL_TAX_RULE_CODE
1025 ,
1026 NULL -- SEPARATE_CHECK_FLAG
1027 ,
1028 l_seqno -- SEQNO
1029 ,
1030 g_user_id -- CREATED_BY
1031 ,
1032 g_sysdatetime -- CREATION_DATE
1033 ,
1034 g_user_id -- LAST_UPDATED_BY
1035 ,
1036 g_sysdatetime -- LAST_UPDATE_DATE
1037 ,
1038 g_login_id -- LAST_UPDATE_LOGIN
1039 ,
1040 a_start -- START DATE SPR C389
1041 ,
1042 l_rowid,
1043 g_sysdate,
1044 hr_general.end_of_time,
1045 NULL -- PROJACCT Project_id
1046 ,
1047 NULL -- TA35 Job_id
1048 ,
1049 'P' -- RETROPAY Pay_Status
1050 ,
1051 'P' -- PROJACCT PA_Status
1052 ,
1053 NULL -- RETROPAY Retro_Batch_Id
1054 ,
1055 'CORRECTION' -- RETROPAY DT_UPDATE_MODE
1056 -- , l_group_id -- HXT11i1
1057 );
1058
1059 -- Check for errors
1060 IF l_retcode = 2
1061 THEN
1062 g_errors := TRUE;
1063 fnd_message.set_name ('HXT', 'HXT_39268_ERR_IN_TIME_GEN');
1064 g_autogen_error := '';
1065 call_gen_error (NULL, g_autogen_error, g_sqlerrm); --SPR C389
1066 END IF;
1067 EXCEPTION
1068 WHEN l_seq_exceptn
1069 THEN
1070 fnd_message.set_name ('HXT', 'HXT_39278_AG_ERR_SEL_SEQNO');
1071 g_autogen_error := '';
1072 g_sqlerrm := SQLERRM;
1073 RAISE g_date_worked_error;
1074 WHEN OTHERS
1075 THEN
1076 fnd_message.set_name ('HXT', 'HXT_39277_AG_ERR_INS_HRS_WKED');
1077 g_autogen_error := '';
1078 g_sqlerrm := SQLERRM;
1079 RAISE g_date_worked_error;
1080 END create_hrw;
1081
1082
1083 ------------------------------------------------------------------
1084 FUNCTION get_hxt_seqno
1085 RETURN NUMBER
1086 IS
1087 -- PUBLIC procedure to get next sequence number from HXT_SEQNO.
1088 CURSOR cur_id
1089 IS
1090 SELECT hxt_seqno.NEXTVAL
1091 FROM DUAL;
1092
1093 l_nextval NUMBER;
1094 BEGIN
1095 -- Get next value
1096 g_sub_loc := 'OPEN cur_id';
1097 OPEN cur_id;
1098 FETCH cur_id INTO l_nextval;
1099 CLOSE cur_id;
1100 RETURN (l_nextval);
1101 EXCEPTION
1102 WHEN OTHERS
1103 THEN
1104 fnd_message.set_name ('HXT', 'HXT_39283_AG_ERR_GET_SEQNO2');
1105 g_autogen_error := '';
1106 g_sqlerrm := SQLERRM;
1107 RAISE g_form_level_error;
1108 END get_hxt_seqno;
1109
1110
1111 ------------------------------------------------------------------
1112 FUNCTION get_next_batch_id
1113 RETURN NUMBER
1114 IS
1115 -- PUBLIC procedure to get next BATCH sequence number
1116 l_nextval NUMBER;
1117 BEGIN
1118 -- Get next value
1119 g_sub_loc := 'OPEN cur_id';
1120
1121 SELECT pay_batch_headers_s.NEXTVAL
1122 INTO l_nextval
1123 FROM DUAL; --SPR C166 BY BC
1124
1125 RETURN (l_nextval);
1126 EXCEPTION
1127 WHEN OTHERS
1128 THEN
1129 g_sqlerrm := SQLERRM;
1130 fnd_message.set_name ('HXT', 'HXT_39282_AG_ERR_GET_BATCHID');
1131 g_autogen_error := '';
1132 RAISE g_form_level_error;
1133 END get_next_batch_id;
1134
1135
1136 ------------------------------------------------------------------
1137 -- PRIVATE --
1138 ------------------------------------------------------------------
1139 PROCEDURE call_gen_error (
1140 a_hrw_id NUMBER,
1141 a_error_text VARCHAR2,
1142 a_ora_err_text VARCHAR2 DEFAULT NULL
1143 )
1144 IS
1145 -- PRIVATE procedure to create error table entries for this package
1146 -- Parameters for call to Gen_Error:
1147 BEGIN
1148 -- Insert into error table
1149 hxt_util.gen_error (
1150 g_asm_rec.batch_id,
1151 g_asm_rec.tim_id,
1152 NULL,
1153 g_time_period_id,
1154 a_error_text,
1155 g_err_loc,
1156 a_ora_err_text,
1157 g_sysdate,
1158 hr_general.end_of_time,
1159 'ERR'
1160 );
1161 -- Set error flag
1162 g_errors := TRUE;
1163 END call_gen_error;
1164
1165
1166 ------------------------------------------------------------------
1167 FUNCTION convert_time (
1168 a_date DATE,
1169 a_time_in NUMBER,
1170 a_time_out NUMBER DEFAULT NULL
1171 )
1172 RETURN DATE
1173 IS
1174 l_date DATE := a_date;
1175 l_convert NUMBER := NVL (a_time_out, a_time_in);
1176 BEGIN
1177 IF (a_time_out IS NOT NULL)
1178 AND ( a_time_out < a_time_in
1179 OR (a_time_out = a_time_in AND a_time_in <> 0)
1180 )
1181 THEN
1182 l_date := l_date
1183 + 1; -- use next day if past midnight
1184 END IF;
1185
1186 RETURN (TO_DATE (
1187 TO_CHAR (l_date, 'MMDDYYYY')
1188 || TO_CHAR (l_convert, '0009'),
1189 'MMDDYYYYHH24MI'
1190 )
1191 );
1192 EXCEPTION
1193 WHEN OTHERS
1194 THEN
1195 fnd_message.set_name ('HXT', 'HXT_39280_AG_ERR_DAT_DESC');
1196 fnd_message.set_token ('DATE', fnd_date.date_to_chardate (l_date));
1197 g_autogen_error := '';
1198 g_sqlerrm := SQLERRM;
1199 RAISE g_date_worked_error;
1200 END convert_time;
1201
1202
1203 ------------------------------------------------------------------
1204 FUNCTION convert_time (
1205 a_date DATE,
1206 a_time_in VARCHAR2,
1207 a_time_out VARCHAR2 DEFAULT NULL
1208 )
1209 RETURN DATE
1210 IS
1211 BEGIN
1212 RETURN (convert_time (
1213 a_date,
1214 TO_NUMBER (REPLACE (a_time_in, ':')),
1215 TO_NUMBER (REPLACE (a_time_out, ':'))
1216 )
1217 );
1218 EXCEPTION
1219 WHEN OTHERS
1220 THEN
1221 fnd_message.set_name ('HXT', 'HXT_39281_AG_ERR_CONV_TIME');
1222 g_autogen_error := '';
1223 g_sqlerrm := SQLERRM;
1224 RAISE g_date_worked_error;
1225 END convert_time;
1226
1227
1228 ------------------------------------------------------------------
1229 PROCEDURE del_obsolete_tim
1230 IS
1231 -- PRIVATE procedure to delete timecards that were autogen'ed for this period
1232 -- that won't be autogen'ed again. Hours worked and error records will
1233 -- cascade-delete.
1234
1235 CURSOR c_get_tim_id IS
1236 SELECT id FROM hxt_timecards_f tim
1237 WHERE tim.auto_gen_flag = 'A'
1238 AND tim.time_period_id = g_time_period_id
1239 AND exists -- added 2772781
1240 (
1241 select '1' from HXT_BATCH_STATES -- added 2772781
1242 where (hxt_batch_states.STATUS <>'VT' and hxt_batch_states.batch_id=tim.batch_id) -- added 2772781
1243 OR tim.batch_id is null -- added 2772781
1244 )
1245 AND NOT EXISTS (
1246 SELECT 'x'
1247 FROM per_time_periods ptp,
1248 hxt_per_aei_ddf_v aeiv,
1249 per_assignments_f asm
1250 WHERE asm.person_id = tim.for_person_id
1251 AND ptp.start_date
1252 BETWEEN asm.effective_start_date
1253 AND asm.effective_end_date
1254 AND aeiv.assignment_id = asm.assignment_id --ORACLE
1255 AND ptp.start_date
1256 BETWEEN aeiv.effective_start_date
1257 AND aeiv.effective_end_date
1258 AND aeiv.hxt_autogen_hours_yn = 'Y' --ORACLE
1259 AND ptp.time_period_id = tim.time_period_id);
1260
1261 BEGIN
1262 -- Delete obsolete timecards
1263 g_sub_loc := 'DELETE from hxt_timecards';
1264 FOR l_record IN c_get_tim_id
1265 LOOP
1266 DELETE FROM hxt_det_hours_worked_f
1267 WHERE tim_id = l_record.id;
1268
1269 DELETE FROM hxt_sum_hours_worked_f
1270 WHERE tim_id = l_record.id;
1271
1272 DELETE FROM hxt_timecards_f
1273 WHERE id = l_record.id;
1274 END LOOP;
1275
1276 COMMIT;
1277 EXCEPTION
1278 WHEN OTHERS
1279 THEN
1280 fnd_message.set_name ('HXT', 'HXT_39466_TCARD_DEL_FAIL');
1281 fnd_message.set_token ('SQLERR', SQLERRM);
1282 g_sqlerrm := fnd_message.get;
1283 RAISE g_del_obs_tim_error;
1284 END del_obsolete_tim;
1285
1286
1287 ------------------------------------------------------------------
1288 --Begin SIR60
1289 ------------------------------------------------------------------
1290 FUNCTION chk_timecard_exists (
1291 a_payroll_id NUMBER,
1292 a_time_period_id NUMBER,
1293 a_person_id NUMBER
1294 )
1295 RETURN NUMBER
1296 IS
1297
1298 -- function to check to see if timecards exists where AutoGen
1299 -- flag set not equal to 'A'. If there are any then get out of loop above
1300 -- because do not want to re-autogen anything that is 'C', 'M', 'U', 'T'.
1301 -- g_sub_loc := 'Chk Timecards Exists from ta_timecards';
1302
1303 --There was a problem with the initial cursor g_cur_asm where it was returning
1304 --records that should not have been returned - for example, timecards with
1305 --autogen flag = 'C' were being returned when they shouldn't have and that
1306 --was causing record to be passed into loop which in turn created a second
1307 --timecard with source of autogen for same payroll and time period. This code
1308 -- returns a value of 1 if no data found which in turn falls through regular
1309 -- code to create autogen timecard, otherwise don't create autogen record
1310 -- and so get out of loop.
1311 v_retcode NUMBER;
1312 f_auto_gen_flag VARCHAR (1);
1313 f_person_id NUMBER (10); -- 30-Nov-98 THis is for R115 changes.
1314
1315
1316 --BEGIN SIR435
1317 CURSOR cur_tim_exists
1318 IS
1319 SELECT auto_gen_flag, for_person_id
1320 FROM hxt_timecards_f tim
1321 WHERE tim.auto_gen_flag <> 'A'
1322 AND tim.for_person_id = a_person_id
1323 AND tim.time_period_id = a_time_period_id
1324 AND tim.payroll_id = a_payroll_id
1325 -- MV: 03-DEC-2002
1326 -- I did some investigation with regards to this query and I do not see why
1327 -- we need the exists statement in this query. It checks if the autogen flag
1328 -- is set for the assignment, but it will always be set because the driving query
1329 -- g_cur_asm already has this check.
1330 -- For now we leave it in because from experience we know that drastic changes
1331 -- in OTM like this have a knock on effect on other code in OTM which is dificult
1332 -- to spot. We only change the statement to be in line with the driving cursor
1333 -- so it can handle mid period hiring and firing
1334 -- Again I do not think that this is the correct solution because it will
1335 -- return all assignment records within a payroll period, this could be
1336 -- multiple records, one for every datetrack update on the assignment or aei
1337 -- so there might still be an issue when an assignment switches from autogen
1338 -- to no-autogen mid-period. We will need to look at this later.
1339 AND EXISTS ( SELECT 'x'
1340 FROM per_time_periods ptp,
1341 hxt_per_aei_ddf_v asmv,
1342 per_assignments_f asm
1343 WHERE asm.person_id = tim.for_person_id
1344 -- next 2 lines copied from g_cur_asm
1345 AND asm.effective_start_date <= ptp.end_date
1346 AND asm.effective_end_date >= ptp.start_date
1347 /* AND ptp.start_date BETWEEN asm.effective_start_date
1348 AND asm.effective_end_date */
1349 AND asmv.assignment_id = asm.assignment_id
1350 -- next 2 lines copied from g_cur_asm
1351 AND asmv.effective_start_date <= ptp.end_date
1352 AND asmv.effective_end_date >= ptp.start_date
1353 /* AND ptp.start_date BETWEEN asmv.effective_start_date
1354 AND asmv.effective_end_date */
1355 AND asmv.hxt_autogen_hours_yn = 'Y'
1356 AND ptp.time_period_id = tim.time_period_id);
1357 BEGIN
1358 OPEN cur_tim_exists;
1359 FETCH cur_tim_exists INTO f_auto_gen_flag, f_person_id;
1360
1361 IF cur_tim_exists%FOUND
1362 THEN
1363 v_retcode := 0;
1364 RETURN (v_retcode);
1365 ELSE
1366 v_retcode := 1;
1367 RETURN (v_retcode);
1368 END IF;
1369 END chk_timecard_exists;
1370
1371
1372 ------------------------------------------------------------------
1373 PROCEDURE del_existing_hrw (a_tim_id NUMBER)
1374 IS
1375 -- PRIVATE procedure to delete hours-worked that were autogen'ed for this
1376 -- period that will be autogen'ed again. Error records will cascade-delete.
1377 BEGIN
1378 -- Delete existing hour-worked entries
1379 fnd_message.set_name ('HXT', 'HXT_39276_AG_ERR_DEL_HRS_WKED');
1380 g_autogen_error := '';
1381 g_sub_loc := 'DELETE from hxt_det_hours_worked';
1382
1383 DELETE FROM hxt_det_hours_worked_f --C421
1384 WHERE tim_id = a_tim_id;
1385
1386 --SIR012 DELETE from hxt_sum_hours_worked
1387 DELETE FROM hxt_sum_hours_worked
1388 WHERE tim_id = a_tim_id; --C421
1389
1390 COMMIT;
1391 -- Delete existing hxt_errors records for this timecard
1392 fnd_message.set_name ('HXT', 'HXT_39275_AG_ERR_DEL_ERRS');
1393 g_autogen_error := '';
1394
1395 DELETE FROM hxt_errors
1396 WHERE tim_id = a_tim_id
1397 AND ( location LIKE 'Autogen%'
1398 OR location LIKE 'AUTO%'
1399 ); --C336
1400
1401 COMMIT;
1402 EXCEPTION
1403 WHEN OTHERS
1404 THEN
1405 g_sqlerrm := SQLERRM;
1406 RAISE g_form_level_error;
1407 END del_existing_hrw;
1408
1409
1410 ------------------------------------------------------------------
1411 PROCEDURE get_holiday_info (
1412 a_date IN DATE,
1413 a_hcl_id IN NUMBER,
1414 a_elt_id OUT NOCOPY NUMBER,
1415 a_hours OUT NOCOPY NUMBER
1416 )
1417 IS
1418
1419 -- Procedure
1420 -- Get_Holiday_Info
1421 -- Purpose
1422 -- Return holiday earning and default hours for input holiday
1423 -- calendar if input day is holiday.
1424 -- Arguments
1425 -- a_date The date being checked.
1426 -- a_hcl_id The Holiday Calendar to be checked.
1427 -- Returns:
1428 -- a_elt_id - holiday earning ID or null
1429 -- a_hours - paid hours for holiday
1430 -- Modification Log:
1431 -- 01/31/96 PJA Created.
1432 CURSOR cur_hcl (c_date DATE, c_hcl_id NUMBER)
1433 IS
1434 -- SELECT hcl.element_type_id, hdy.hours -- SPR C332 by BC
1435 SELECT DECODE (hdy.hours, NULL, NULL, hcl.element_type_id), hdy.hours -- SPR C332 by BC
1436 FROM hxt_holiday_calendars hcl, hxt_holiday_days hdy
1437 WHERE hdy.holiday_date = c_date
1438 AND hcl.id = hdy.hcl_id
1439 AND c_date BETWEEN hcl.effective_start_date
1440 AND hcl.effective_end_date
1441 AND hcl.id = c_hcl_id;
1442 BEGIN
1443 -- Get holiday data
1444 g_sub_loc := 'Cur_Hcl';
1445 OPEN cur_hcl (a_date, a_hcl_id);
1446 FETCH cur_hcl INTO a_elt_id, a_hours;
1447 CLOSE cur_hcl;
1448 EXCEPTION
1449 WHEN OTHERS
1450 THEN
1451 fnd_message.set_name ('HXT', 'HXT_39286_AG_ERR_SEL_HOL_HRS');
1452 g_autogen_error := '';
1453 g_sqlerrm := SQLERRM;
1454 RAISE g_date_worked_error;
1455 END get_holiday_info;
1456
1457
1458 ------------------------------------------------------------------
1459 --BEGIN HXT11i1
1460 /*
1461 PROCEDURE Get_Group_ID (a_group_id OUT NOCOPY NUMBER) IS
1462 --
1463 -- This procedure returns the next group id. Group IDs are used to tie
1464 -- together summary and detail rows on the timecard.
1465 --
1466 CURSOR C IS
1467 SELECT HXT_GROUP_ID_S.NEXTVAL
1468 FROM SYS.DUAL;
1469 BEGIN
1470 g_sub_loc := 'Get_Group_ID';
1471 OPEN C;
1472 FETCH C
1473 INTO a_group_id;
1474 CLOSE C;
1475 EXCEPTION
1476 WHEN OTHERS THEN
1477 IF g_autogen_error IS NULL THEN
1478 g_autogen_error := 'Autogen failed trying to get Group ID for summary/detail rows';
1479 END IF;
1480 IF g_sqlerrm IS NULL THEN
1481 g_sqlerrm := SQLERRM;
1482 END IF;
1483 RAISE g_date_worked_error;
1484 END Get_Group_ID;
1485 */
1486 PROCEDURE manage_fnd_sessions
1487 IS
1488 CURSOR old_row_exists_cur
1489 IS
1490 SELECT '1'
1491 FROM fnd_sessions
1492 WHERE session_id = USERENV ('SESSIONID')
1493 AND effective_date < TRUNC ( SYSDATE
1494 - 1);
1495
1496 CURSOR row_exists_cur
1497 IS
1498 SELECT '1'
1499 FROM fnd_sessions
1500 WHERE session_id = USERENV ('SESSIONID')
1501 AND TRUNC (effective_date) BETWEEN TRUNC ( SYSDATE
1502 - 1)
1503 AND SYSDATE;
1504
1505 l_dummy VARCHAR2 (1);
1506 BEGIN
1507 OPEN old_row_exists_cur;
1508 FETCH old_row_exists_cur INTO l_dummy;
1509
1510 IF old_row_exists_cur%FOUND
1511 THEN
1512 DELETE FROM fnd_sessions
1513 WHERE session_id = USERENV ('SESSIONID')
1514 AND effective_date < TRUNC ( SYSDATE
1515 - 1);
1516
1517 COMMIT;
1518 END IF;
1519
1520 OPEN row_exists_cur;
1521 FETCH row_exists_cur INTO l_dummy;
1522
1523 IF row_exists_cur%NOTFOUND
1524 THEN
1525 INSERT INTO fnd_sessions
1526 (session_id, effective_date)
1527 VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
1528
1529 COMMIT;
1530 END IF;
1531 END;
1532 ------------------------------------------------------------------
1533 END hxt_time_gen;