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