[Home] [Help]
PACKAGE BODY: APPS.HXT_TIME_COLLECTION
Source
1 PACKAGE BODY hxt_time_collection AS
2 /* $Header: hxttcol.pkb 120.8.12010000.2 2008/09/11 18:13:13 asrajago ship $ */
3
4 /*-------------------------------------------------------------------------
5 ||
6 || Private Module Declarations
7 ||
8 -------------------------------------------------------------------------*/
9
10 -- Global package name
11 g_debug BOOLEAN := hr_utility.debug_enabled;
12 g_package CONSTANT VARCHAR2 (33) := 'hxt_time_collection.';
13 g_cache BOOLEAN := TRUE;
14 g_max_tc_allowed PLS_INTEGER := fnd_profile.VALUE ('HXT_BATCH_SIZE');
15
16 TYPE batch_info_rec IS RECORD (
17 batch_id pay_batch_headers.batch_id%TYPE,
18 batch_ref pay_batch_headers.batch_reference%TYPE,
19 period_id per_time_periods.time_period_id%TYPE,
20 num_tcs NUMBER
21 );
22
23 TYPE batch_info_table IS TABLE OF batch_info_rec
24 INDEX BY BINARY_INTEGER;
25
26 g_batch_info batch_info_table;
27
28 FUNCTION CACHE
29 RETURN BOOLEAN
30 IS
31 l_proc VARCHAR2 (72);
32 BEGIN
33 g_debug := hr_utility.debug_enabled;
34
35 IF g_debug
36 THEN
37 l_proc := g_package || 'cache';
38 hr_utility.set_location ('Entering:' || l_proc, 10);
39 END IF;
40
41 IF (g_cache)
42 THEN
43 IF g_debug
44 THEN
45 hr_utility.set_location (' returning g_cache = TRUE', 20);
46 END IF;
47 ELSE
48 IF g_debug
49 THEN
50 hr_utility.set_location (' returning g_cache = FALSE', 30);
51 END IF;
52 END IF;
53
54 IF g_debug
55 THEN
56 hr_utility.set_location ('Leaving:' || l_proc, 100);
57 END IF;
58
59 RETURN g_cache;
60 END CACHE;
61
62 PROCEDURE set_cache (p_cache IN BOOLEAN)
63 IS
64 l_proc VARCHAR2 (72);
65 BEGIN
66 g_debug := hr_utility.debug_enabled;
67
68 IF g_debug
69 THEN
70 l_proc := g_package || 'set_cache';
71 hr_utility.set_location ('Entering:' || l_proc, 10);
72 END IF;
73
74 IF (p_cache)
75 THEN
76 IF g_debug
77 THEN
78 hr_utility.set_location (' setting g_cache to TRUE', 20);
79 END IF;
80 ELSE
81 IF g_debug
82 THEN
83 hr_utility.set_location (' setting g_cache to FALSE', 30);
84 END IF;
85 END IF;
86
87 g_cache := p_cache;
88
89 IF g_debug
90 THEN
91 hr_utility.set_location ('Leaving:' || l_proc, 100);
92 END IF;
93 END set_cache;
94
95 FUNCTION round_time (p_time DATE, p_interval NUMBER, p_round_up NUMBER)
96 RETURN DATE;
97
98 FUNCTION check_time_overlap (
99 p_date DATE,
100 p_time_in DATE,
101 p_time_out DATE,
102 p_id NUMBER,
103 p_tim_id NUMBER
104 )
105 RETURN VARCHAR2;
106
107 FUNCTION reset_hours (p_in IN DATE, p_out IN DATE)
108 RETURN NUMBER;
109
110 --END SIR236
111 FUNCTION get_time_period (
112 i_payroll_id IN NUMBER,
113 i_date_worked IN DATE,
114 o_time_period OUT NOCOPY NUMBER,
115 o_start_date OUT NOCOPY DATE,
116 o_end_date OUT NOCOPY DATE
117 )
118 RETURN NUMBER;
119
120 FUNCTION check_for_timecard (
121 i_person_id IN NUMBER,
122 i_time_period_id IN NUMBER,
123 o_timecard_id OUT NOCOPY NUMBER,
124 o_auto_gen_flag OUT NOCOPY VARCHAR2
125 )
126 RETURN NUMBER;
127
128 FUNCTION create_timecard (
129 i_person_id IN NUMBER,
130 i_business_group_id IN NUMBER,
131 i_assignment_id IN NUMBER,
132 i_payroll_id IN NUMBER,
133 i_time_period_id IN NUMBER,
134 i_approver_id IN NUMBER,
135 i_timecard_source_code IN VARCHAR2,
136 o_timecard_id OUT NOCOPY NUMBER
137 )
138 RETURN NUMBER;
139
140 FUNCTION create_batch (
141 i_source IN VARCHAR2,
142 i_payroll_id IN NUMBER,
143 i_time_period_id IN NUMBER,
144 i_assignment_id IN NUMBER,
145 i_person_id IN NUMBER,
146 i_business_group_id IN NUMBER,
147 o_batch_id OUT NOCOPY NUMBER
148 )
149 RETURN NUMBER;
150
151 FUNCTION find_existing_batch (
152 p_time_period_id IN per_time_periods.time_period_id%TYPE,
153 p_batch_reference IN pay_batch_headers.batch_reference%TYPE
154 )
155 RETURN pay_batch_headers.batch_id%TYPE;
156
157 FUNCTION create_holiday_hours (
158 i_person_id IN NUMBER,
159 i_hcl_id IN NUMBER,
160 i_hxt_rotation_plan IN NUMBER, --SIR344
161 i_start_date IN DATE,
162 i_end_date IN DATE,
163 i_timecard_id IN NUMBER,
164 i_wage_code IN VARCHAR2,
165 i_task_id IN NUMBER,
166 i_location_id IN NUMBER,
167 i_project_id IN hxt_sum_hours_worked.project_id%TYPE,
168 i_earn_pol_id IN hxt_sum_hours_worked.earn_pol_id%TYPE,
169 i_earn_reason_code IN VARCHAR2,
170 i_comment IN VARCHAR2,
171 i_rate_multiple IN NUMBER,
172 i_hourly_rate IN NUMBER,
173 i_amount IN NUMBER,
174 i_separate_check_flag IN VARCHAR2,
175 i_assignment_id IN NUMBER,
176 i_time_summary_id IN NUMBER,
177 i_tim_sum_eff_start_date IN DATE,
178 i_tim_sum_eff_end_date IN DATE,
179 i_created_by IN NUMBER,
180 i_last_updated_by IN NUMBER,
181 i_last_update_login IN NUMBER,
182 i_writesum_yn IN VARCHAR2,
183 i_explode_yn IN VARCHAR2,
184 i_batch_status IN VARCHAR2,
185 i_dt_update_mode IN VARCHAR2, --SIR290
186 p_time_building_block_id IN NUMBER DEFAULT NULL,
187 p_time_building_block_ovn IN NUMBER DEFAULT NULL,
188 o_otm_error OUT NOCOPY VARCHAR2,
189 o_oracle_error OUT NOCOPY VARCHAR2,
190 o_created_tim_sum_id OUT NOCOPY NUMBER,
191 i_start_time IN DATE,
192 i_end_time IN DATE,
193 i_state_name IN VARCHAR2 DEFAULT NULL,
194 i_county_name IN VARCHAR2 DEFAULT NULL,
195 i_city_name IN VARCHAR2 DEFAULT NULL,
196 i_zip_code IN VARCHAR2 DEFAULT NULL
197 )
198 RETURN NUMBER;
199
200 --p_mode IN VARCHAR2 default 'INSERT') RETURN NUMBER;
201 FUNCTION check_for_batch_status (
202 i_batch_id IN NUMBER,
203 o_batch_status OUT NOCOPY VARCHAR2
204 )
205 RETURN NUMBER;
206
207 PROCEDURE load_policies (
208 p_summ_id IN NUMBER,
209 p_summ_earn_pol_id IN NUMBER,
210 p_summ_assignment_id IN NUMBER,
211 p_summ_date_worked IN DATE,
212 p_work_plan OUT NOCOPY NUMBER,
213 p_rotation_plan OUT NOCOPY NUMBER,
214 p_rotation_or_work_plan OUT NOCOPY VARCHAR2
215 -- ,p_retcode OUT NOCOPY NUMBER
216 -- ,p_hours OUT NOCOPY NUMBER
217 ,
218 p_shift_hours OUT NOCOPY NUMBER,
219 p_egp_id OUT NOCOPY hxt_sum_hours_worked.earn_pol_id%TYPE -- 5903580 NUMBER earning policy
220 ,
221 p_hdp_id OUT NOCOPY NUMBER -- hrs deduction policy
222 -- ,p_hdy_id OUT NOCOPY NUMBER -- holiday day ID
223 ,
224 p_sdp_id OUT NOCOPY NUMBER -- shift diff policy
225 ,
226 p_egp_type OUT NOCOPY VARCHAR2
227 -- earning policy type
228 ,
229 p_egt_id OUT NOCOPY NUMBER
230 -- include earning group
231 ,
232 p_pep_id OUT NOCOPY NUMBER -- prem elig policy
233 ,
234 p_pip_id OUT NOCOPY NUMBER -- prem interact policy
235 ,
236 p_hcl_id OUT NOCOPY NUMBER -- holiday calendar
237 ,
238 p_hcl_elt_id OUT NOCOPY NUMBER -- holiday earning type
239 ,
240 p_sdf_id OUT NOCOPY NUMBER -- override shift diff
241 ,
242 p_osp_id OUT NOCOPY NUMBER -- off-shift prem
243 ,
244 p_standard_start OUT NOCOPY NUMBER,
245 p_standard_stop OUT NOCOPY NUMBER,
246 p_early_start OUT NOCOPY NUMBER,
247 p_late_stop OUT NOCOPY NUMBER,
248 p_min_tcard_intvl OUT NOCOPY NUMBER,
249 p_round_up OUT NOCOPY NUMBER,
250 p_hol_code OUT NOCOPY NUMBER,
251 p_hol_yn OUT NOCOPY VARCHAR2,
252 p_error OUT NOCOPY NUMBER,
253 p_overtime_type OUT NOCOPY VARCHAR2,
254 p_otm_error OUT NOCOPY VARCHAR2
255 );
256
257 FUNCTION record_hours_worked (
258 p_timecard_source IN VARCHAR2,
259 b_generate_holiday IN BOOLEAN,
260 i_timecard_id IN NUMBER,
261 i_assignment_id IN NUMBER,
262 i_person_id IN NUMBER,
263 i_date_worked IN DATE,
264 i_element_id IN NUMBER,
265 i_hours IN NUMBER,
266 i_start_time IN DATE,
267 i_end_time IN DATE,
268 i_start_date IN DATE,
269 i_wage_code VARCHAR2,
270 i_task_id IN NUMBER,
271 i_location_id IN NUMBER,
272 i_project_id IN hxt_sum_hours_worked.project_id%TYPE,
273 i_earn_pol_id IN hxt_sum_hours_worked.earn_pol_id%TYPE,
274 i_earn_reason_code IN VARCHAR2,
275 i_cost_center_id IN NUMBER,
276 i_comment IN VARCHAR2,
277 i_rate_multiple IN NUMBER,
278 i_hourly_rate IN NUMBER,
279 i_amount IN NUMBER,
280 i_separate_check_flag IN VARCHAR2,
281 i_time_summary_id IN NUMBER,
282 i_tim_sum_eff_start_date IN DATE,
283 i_tim_sum_eff_end_date IN DATE,
284 i_created_by IN NUMBER,
285 i_last_updated_by IN NUMBER,
286 i_last_update_login IN NUMBER,
287 i_writesum_yn IN VARCHAR2,
288 i_explode_yn IN VARCHAR2,
289 i_batch_status IN VARCHAR2,
290 i_dt_update_mode IN VARCHAR2,
291 p_time_building_block_id IN NUMBER DEFAULT NULL,
292 p_time_building_block_ovn IN NUMBER DEFAULT NULL,
293 o_otm_error OUT NOCOPY VARCHAR2,
294 o_oracle_error OUT NOCOPY VARCHAR2,
295 o_created_tim_sum_id OUT NOCOPY NUMBER,
296 i_state_name IN VARCHAR2 DEFAULT NULL,
297 i_county_name IN VARCHAR2 DEFAULT NULL,
298 i_city_name IN VARCHAR2 DEFAULT NULL,
299 i_zip_code IN VARCHAR2 DEFAULT NULL
300 )
301 RETURN NUMBER;
302
303 -- p_mode IN VARCHAR2 default 'INSERT') RETURN NUMBER;
304 PROCEDURE cost_allocation_entry (
305 i_concat_segments IN VARCHAR2,
306 i_cost_segment1 IN VARCHAR2,
307 i_cost_segment2 IN VARCHAR2,
308 i_cost_segment3 IN VARCHAR2,
309 i_cost_segment4 IN VARCHAR2,
310 i_cost_segment5 IN VARCHAR2,
311 i_cost_segment6 IN VARCHAR2,
312 i_cost_segment7 IN VARCHAR2,
313 i_cost_segment8 IN VARCHAR2,
314 i_cost_segment9 IN VARCHAR2,
315 i_cost_segment10 IN VARCHAR2,
316 i_cost_segment11 IN VARCHAR2,
317 i_cost_segment12 IN VARCHAR2,
318 i_cost_segment13 IN VARCHAR2,
319 i_cost_segment14 IN VARCHAR2,
320 i_cost_segment15 IN VARCHAR2,
321 i_cost_segment16 IN VARCHAR2,
322 i_cost_segment17 IN VARCHAR2,
323 i_cost_segment18 IN VARCHAR2,
324 i_cost_segment19 IN VARCHAR2,
325 i_cost_segment20 IN VARCHAR2,
326 i_cost_segment21 IN VARCHAR2,
327 i_cost_segment22 IN VARCHAR2,
328 i_cost_segment23 IN VARCHAR2,
329 i_cost_segment24 IN VARCHAR2,
330 i_cost_segment25 IN VARCHAR2,
331 i_cost_segment26 IN VARCHAR2,
332 i_cost_segment27 IN VARCHAR2,
333 i_cost_segment28 IN VARCHAR2,
334 i_cost_segment29 IN VARCHAR2,
335 i_cost_segment30 IN VARCHAR2,
336 i_business_group_id IN NUMBER,
337 o_ffv_cost_center_id OUT NOCOPY NUMBER,
338 o_otm_error OUT NOCOPY VARCHAR2,
339 o_oracle_error OUT NOCOPY VARCHAR2
340 );
341
342 PROCEDURE call_hxthxc_gen_error (
343 p_app_short_name IN VARCHAR2,
344 p_msg_name IN VARCHAR2,
345 p_msg_token IN VARCHAR2
346 )
347 IS
348 l_msg_token VARCHAR2 (500);
349 -- calls error processing procedure --
350 BEGIN
351 IF p_msg_name = 'HXC_HXT_DEP_VAL_OTMERR' AND p_msg_token IS NOT NULL
352 THEN
353 l_msg_token := 'ERROR&' || p_msg_token;
354 ELSE
355 l_msg_token := p_msg_token;
356 END IF;
357
358 IF p_msg_name = 'HXC_HXT_DEP_VAL_OTMERR' AND p_msg_token IS NOT NULL
359 OR p_msg_name <> 'HXC_HXT_DEP_VAL_OTMERR'
360 THEN
361 hxc_time_entry_rules_utils_pkg.add_error_to_table
362 (p_message_table => hxt_hxc_retrieval_process.g_otm_messages,
363 p_message_name => p_msg_name,
364 p_message_token => SUBSTR (l_msg_token, 1, 240),
365 p_message_level => 'ERROR',
366 p_message_field => NULL,
367 p_application_short_name => p_app_short_name,
368 p_timecard_bb_id => NULL,
369 p_time_attribute_id => NULL,
370 p_timecard_bb_ovn => NULL,
371 p_time_attribute_ovn => NULL
372 );
373 END IF;
374
375 IF g_debug
376 THEN
377 hr_utility.TRACE ('Adding to g_otm_messages' || p_msg_name);
378 END IF;
379 END;
380
381 -- p_mode IN VARCHAR2 default 'INSERT');
382 FUNCTION delete_summary_record (p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE)
383 RETURN NUMBER;
384
385 /*-------------------------------------------------------------------------
386 ||
387 || Public Module Definitions
388 ||
389 -------------------------------------------------------------------------*/
390 PROCEDURE record_time (
391 timecard_source IN VARCHAR2,
392 batch_ref IN VARCHAR2 DEFAULT NULL,
393 batch_name IN VARCHAR2 DEFAULT NULL,
394 approver_number IN VARCHAR2 DEFAULT NULL,
395 employee_number IN VARCHAR2,
396 date_worked IN DATE DEFAULT NULL,
397 start_time IN DATE DEFAULT NULL,
398 end_time IN DATE DEFAULT NULL,
399 hours IN NUMBER DEFAULT NULL,
400 wage_code IN VARCHAR2 DEFAULT NULL,
401 earning_policy IN VARCHAR2 DEFAULT NULL,
402 hours_type IN VARCHAR2 DEFAULT NULL,
403 earn_reason_code IN VARCHAR2 DEFAULT NULL,
404 project IN VARCHAR2 DEFAULT NULL,
405 task_number IN VARCHAR2 DEFAULT NULL,
406 location_code IN VARCHAR2 DEFAULT NULL,
407 COMMENT IN VARCHAR2 DEFAULT NULL,
408 rate_multiple IN NUMBER DEFAULT NULL,
409 hourly_rate IN NUMBER DEFAULT NULL,
410 amount IN NUMBER DEFAULT NULL,
411 separate_check_flag IN VARCHAR2 DEFAULT NULL,
412 business_group_id IN NUMBER DEFAULT NULL,
413 concat_cost_segments IN VARCHAR2 DEFAULT NULL,
414 cost_segment1 IN VARCHAR2 DEFAULT NULL,
415 cost_segment2 IN VARCHAR2 DEFAULT NULL,
416 cost_segment3 IN VARCHAR2 DEFAULT NULL,
417 cost_segment4 IN VARCHAR2 DEFAULT NULL,
418 cost_segment5 IN VARCHAR2 DEFAULT NULL,
419 cost_segment6 IN VARCHAR2 DEFAULT NULL,
420 cost_segment7 IN VARCHAR2 DEFAULT NULL,
421 cost_segment8 IN VARCHAR2 DEFAULT NULL,
422 cost_segment9 IN VARCHAR2 DEFAULT NULL,
423 cost_segment10 IN VARCHAR2 DEFAULT NULL,
424 cost_segment11 IN VARCHAR2 DEFAULT NULL,
425 cost_segment12 IN VARCHAR2 DEFAULT NULL,
426 cost_segment13 IN VARCHAR2 DEFAULT NULL,
427 cost_segment14 IN VARCHAR2 DEFAULT NULL,
428 cost_segment15 IN VARCHAR2 DEFAULT NULL,
429 cost_segment16 IN VARCHAR2 DEFAULT NULL,
430 cost_segment17 IN VARCHAR2 DEFAULT NULL,
431 cost_segment18 IN VARCHAR2 DEFAULT NULL,
432 cost_segment19 IN VARCHAR2 DEFAULT NULL,
433 cost_segment20 IN VARCHAR2 DEFAULT NULL,
434 cost_segment21 IN VARCHAR2 DEFAULT NULL,
435 cost_segment22 IN VARCHAR2 DEFAULT NULL,
436 cost_segment23 IN VARCHAR2 DEFAULT NULL,
437 cost_segment24 IN VARCHAR2 DEFAULT NULL,
438 cost_segment25 IN VARCHAR2 DEFAULT NULL,
439 cost_segment26 IN VARCHAR2 DEFAULT NULL,
440 cost_segment27 IN VARCHAR2 DEFAULT NULL,
441 cost_segment28 IN VARCHAR2 DEFAULT NULL,
442 cost_segment29 IN VARCHAR2 DEFAULT NULL,
443 cost_segment30 IN VARCHAR2 DEFAULT NULL,
444 time_summary_id IN NUMBER DEFAULT NULL,
445 tim_sum_eff_start_date IN DATE DEFAULT NULL,
446 tim_sum_eff_end_date IN DATE DEFAULT NULL,
447 created_by IN NUMBER,
448 last_updated_by IN NUMBER,
449 last_update_login IN NUMBER,
450 writesum_yn IN VARCHAR2 DEFAULT 'Y',
451 explode_yn IN VARCHAR2 DEFAULT 'Y',
452 delete_yn IN VARCHAR2 DEFAULT 'N',
453 --AM 001
454 dt_update_mode IN VARCHAR2 DEFAULT NULL,
455 created_tim_sum_id OUT NOCOPY NUMBER,
456 otm_error OUT NOCOPY VARCHAR2,
457 oracle_error OUT NOCOPY VARCHAR2,
458 p_time_building_block_id IN NUMBER DEFAULT NULL,
459 p_time_building_block_ovn IN NUMBER DEFAULT NULL,
460 p_validate IN BOOLEAN DEFAULT FALSE,
461 p_state_name IN VARCHAR2 DEFAULT NULL,
462 p_county_name IN VARCHAR2 DEFAULT NULL,
463 p_city_name IN VARCHAR2 DEFAULT NULL,
464 p_zip_code IN VARCHAR2 DEFAULT NULL
465 )
466 IS
467 l_person_id per_people_f.person_id%TYPE DEFAULT NULL;
468 l_last_name per_people_f.last_name%TYPE DEFAULT NULL;
469 l_first_name per_people_f.first_name%TYPE DEFAULT NULL;
470 l_approver_id per_people_f.person_id%TYPE DEFAULT NULL;
471 l_appr_last_name per_people_f.last_name%TYPE DEFAULT NULL;
472 l_appr_first_name per_people_f.first_name%TYPE DEFAULT NULL;
473 l_timecard_id hxt_timecards.ID%TYPE DEFAULT NULL;
474 l_date_worked DATE DEFAULT NULL;
475 l_element_type_id pay_element_types_f.element_type_id%TYPE
476 DEFAULT NULL;
477 l_task_id hxt_tasks_v.task_id%TYPE DEFAULT NULL;
478 l_location_id hr_locations.location_id%TYPE DEFAULT NULL;
479 l_project_id hxt_sum_hours_worked.project_id%TYPE
480 DEFAULT NULL;
481 l_time_period_id per_time_periods.time_period_id%TYPE
482 DEFAULT NULL;
483 l_start_date DATE DEFAULT NULL;
484 l_end_date DATE DEFAULT NULL;
485 l_auto_gen_flag hxt_timecards.auto_gen_flag%TYPE
486 DEFAULT NULL;
487 l_timecard_exists BOOLEAN DEFAULT TRUE;
488 l_hours hxt_sum_hours_worked.hours%TYPE
489 DEFAULT NULL;
490 l_sep_chk_flg hxt_sum_hours_worked.separate_check_flag%TYPE
491 DEFAULT NULL;
492 l_timecard_source_code hxt_timecards.auto_gen_flag%TYPE
493 DEFAULT NULL;
494 l_earn_pol_id hxt_sum_hours_worked.earn_pol_id%TYPE
495 DEFAULT NULL;
496 l_ffv_cost_center_id hxt_sum_hours_worked.ffv_cost_center_id%TYPE
497 DEFAULT NULL;
498 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE DEFAULT NULL;
499 l_batch_status VARCHAR2 (30);
500 l_person_id_data_err EXCEPTION;
501 l_person_id_sys_err EXCEPTION;
502 l_appr_id_data_err EXCEPTION;
503 l_appr_id_sys_err EXCEPTION;
504 l_assign_id_data_err EXCEPTION;
505 l_assign_id_sys_err EXCEPTION;
506 l_pay_date_data_err EXCEPTION;
507 l_pay_date_sys_err EXCEPTION;
508 l_elem_type_data_err EXCEPTION;
509 l_elem_type_sys_err EXCEPTION;
510 l_elem_link_data_err EXCEPTION;
511 l_elem_link_sys_err EXCEPTION;
512 l_task_id_data_err EXCEPTION;
513 l_task_id_sys_err EXCEPTION;
514 l_locn_id_data_err EXCEPTION;
515 l_locn_id_sys_err EXCEPTION;
516 l_proj_id_data_err EXCEPTION;
517 l_proj_id_sys_err EXCEPTION;
518 l_time_per_data_err EXCEPTION;
519 l_time_per_sys_err EXCEPTION;
520 l_istimecard_sys_err EXCEPTION;
521 l_make_card_data_err EXCEPTION;
522 l_make_card_sys_err EXCEPTION;
523 l_make_hol_data_err EXCEPTION;
524 l_make_hol_sys_err EXCEPTION;
525 l_autogen_error EXCEPTION;
526 l_rec_hours_data_err EXCEPTION;
527 l_rec_hours_sys_err EXCEPTION;
528 l_sep_chk_flg_data_err EXCEPTION;
529 l_hours_reason_data_err EXCEPTION;
530 l_reason_code_data_err EXCEPTION;
531 l_reason_code_sys_err EXCEPTION;
532 l_time_summary_id_data_err EXCEPTION;
533 l_time_summary_id_sys_err EXCEPTION;
534 l_cost_center_data_err EXCEPTION;
535 l_cost_center_sys_err EXCEPTION;
536 l_prev_wage_data_err EXCEPTION;
537 l_prev_wage_sys_err EXCEPTION;
538 l_hours_amount_data_err EXCEPTION;
539 l_amt_hrs_elmnt_data_err EXCEPTION;
540 l_amt_hrs_zero_data_err EXCEPTION;
541 l_start_end_data_err EXCEPTION;
542 l_hours_null_data_err EXCEPTION;
543 l_no_time_data_err EXCEPTION;
544 l_tim_src_data_err EXCEPTION;
545 l_tim_src_sys_err EXCEPTION;
546 l_earn_pol_data_err EXCEPTION;
547 l_earn_pol_sys_err EXCEPTION;
548 l_sess_date_err EXCEPTION;
549 l_date_worked_time_err EXCEPTION;
550 l_delete_sys_error EXCEPTION;
551 l_delete_finished EXCEPTION;
552 l_dt_update_mode_err EXCEPTION;
553 l_dt_upt_mode_null_err EXCEPTION;
554 l_retcode NUMBER DEFAULT 0;
555 l_error_text VARCHAR2 (240) DEFAULT NULL;
556 l_otm_error VARCHAR2 (240) DEFAULT NULL;
557 l_oracle_error VARCHAR2 (512) DEFAULT NULL;
558 l_emp_rec g_employee_cur%ROWTYPE;
559 l_proc VARCHAR2 (100);
560 BEGIN
561 g_debug := hr_utility.debug_enabled;
562
563 IF g_debug
564 THEN
565 l_proc := 'hxt_time_collection.RECORD_TIME';
566 hr_utility.set_location (l_proc, 10);
567 END IF;
568
569 SAVEPOINT only_validate;
570
571 IF g_debug
572 THEN
573 hr_utility.set_location (l_proc, 20);
574 END IF;
575
576 /* Initialize globals */
577 g_batch_ref := batch_ref;
578 g_batch_name := batch_name;
579 g_sysdate := TRUNC (SYSDATE);
580 g_sysdatetime := SYSDATE;
581 g_user_id := fnd_global.user_id;
582 g_login_id := fnd_global.login_id;
583
584 IF g_debug
585 THEN
586 hr_utility.set_location (l_proc, 30);
587 END IF;
588
589 /* Copy parameters to error variables */
590 e_timecard_source := timecard_source;
591 e_approver_number := approver_number;
592 e_employee_number := employee_number;
593 e_date_worked := date_worked;
594 e_start_time := start_time;
595 e_end_time := end_time;
596 e_hours := hours;
597 e_hours_type := hours_type;
598 e_earn_reason_code := earn_reason_code;
599 e_project := project;
600 e_task_number := task_number;
601 e_location_code := location_code;
602 e_comment := COMMENT;
603 e_rate_multiple := rate_multiple;
604 e_hourly_rate := hourly_rate;
605 e_amount := amount;
606 e_separate_check_flag := separate_check_flag;
607 e_business_group_id := business_group_id;
608 e_concat_cost_segments := concat_cost_segments;
609 e_cost_segment1 := cost_segment1;
610 e_cost_segment2 := cost_segment2;
611 e_cost_segment3 := cost_segment3;
612 e_cost_segment4 := cost_segment4;
613 e_cost_segment5 := cost_segment5;
614 e_cost_segment6 := cost_segment6;
615 e_cost_segment7 := cost_segment7;
616 e_cost_segment8 := cost_segment8;
617 e_cost_segment9 := cost_segment9;
618 e_cost_segment10 := cost_segment10;
619 e_cost_segment11 := cost_segment11;
620 e_cost_segment12 := cost_segment12;
621 e_cost_segment13 := cost_segment13;
622 e_cost_segment14 := cost_segment14;
623 e_cost_segment15 := cost_segment15;
624 e_cost_segment16 := cost_segment16;
625 e_cost_segment17 := cost_segment17;
626 e_cost_segment18 := cost_segment18;
627 e_cost_segment19 := cost_segment19;
628 e_cost_segment20 := cost_segment20;
629 e_cost_segment21 := cost_segment21;
630 e_cost_segment22 := cost_segment22;
631 e_cost_segment23 := cost_segment23;
632 e_cost_segment24 := cost_segment24;
633 e_cost_segment25 := cost_segment25;
634 e_cost_segment26 := cost_segment26;
635 e_cost_segment27 := cost_segment27;
636 e_cost_segment28 := cost_segment28;
637 e_cost_segment29 := cost_segment29;
638 e_cost_segment30 := cost_segment30;
639 e_state_name := p_state_name;
640 e_county_name := p_county_name;
641 e_city_name := p_city_name;
642 e_zip_code := p_zip_code;
643
644 IF g_debug
645 THEN
646 hr_utility.TRACE ('Done INIT');
647 hr_utility.set_location (l_proc, 40);
648 END IF;
649
650 /*Get Session Date*/
651 l_retcode := hxt_tim_col_util.get_session_date (g_sess_date);
652
653 IF g_debug
654 THEN
655 hr_utility.TRACE ('l_retcode :' || l_retcode);
656 END IF;
657
658 IF l_retcode = 1
659 THEN
660 IF g_debug
661 THEN
662 hr_utility.set_location (l_proc, 50);
663 END IF;
664
665 RAISE l_sess_date_err;
666 END IF;
667
668 IF g_debug
669 THEN
670 hr_utility.TRACE ('GOT SESS DATE');
671 END IF;
672
673 /* Validate dt_update_mode */
674 IF (time_summary_id IS NOT NULL) AND (dt_update_mode IS NULL)
675 THEN
676 IF g_debug
677 THEN
678 hr_utility.set_location (l_proc, 60);
679 END IF;
680
681 RAISE l_dt_upt_mode_null_err;
682 END IF;
683
684 IF (dt_update_mode IS NOT NULL)
685 AND -- SIR293
686 (dt_update_mode NOT IN ('CORRECTION', 'UPDATE')
687 )
688 THEN
689 IF g_debug
690 THEN
691 hr_utility.set_location (l_proc, 70);
692 END IF;
693
694 RAISE l_dt_update_mode_err;
695 END IF;
696
697 IF g_debug
698 THEN
699 hr_utility.TRACE ('VALID DT MODE');
700 hr_utility.set_location (l_proc, 80);
701 END IF;
702
703 /* Validate time summary id */
704 IF time_summary_id IS NOT NULL
705 THEN
706 IF g_debug
707 THEN
708 hr_utility.set_location (l_proc, 90);
709 END IF;
710
711 l_retcode :=
712 hxt_tim_col_util.validate_time_summary_id (time_summary_id);
713
714 IF g_debug
715 THEN
716 hr_utility.TRACE ('l_retcode :' || l_retcode);
717 END IF;
718
719 IF l_retcode = 1
720 THEN
721 IF g_debug
722 THEN
723 hr_utility.set_location (l_proc, 100);
724 END IF;
725
726 RAISE l_time_summary_id_data_err;
727 ELSIF l_retcode = 2
728 THEN
729 IF g_debug
730 THEN
731 hr_utility.set_location (l_proc, 110);
732 END IF;
733
734 RAISE l_time_summary_id_sys_err;
735 END IF;
736 END IF;
737
738 IF g_debug
739 THEN
740 hr_utility.set_location (l_proc, 120);
741 END IF;
742
743 /* Check for and perform any deletes */
744 IF delete_yn = 'Y' AND time_summary_id IS NOT NULL
745 THEN
746 IF g_debug
747 THEN
748 hr_utility.set_location (l_proc, 130);
749 END IF;
750
751 l_retcode := delete_summary_record (time_summary_id);
752
753 IF g_debug
754 THEN
755 hr_utility.TRACE ('l_retcode :' || l_retcode);
756 END IF;
757
758 IF l_retcode = 1
759 THEN
760 IF g_debug
761 THEN
762 hr_utility.set_location (l_proc, 140);
763 END IF;
764
765 RAISE l_delete_sys_error;
766 END IF;
767
768 IF g_debug
769 THEN
770 hr_utility.set_location (l_proc, 150);
771 END IF;
772
773 RAISE l_delete_finished;
774 END IF;
775
776 /*Determine date worked */
777 /* If there are start and end times derive date from them */
778 /* otherwise use date_worked parameter value. */
779 IF g_debug
780 THEN
781 hr_utility.TRACE ('date_worked :' || date_worked);
782 hr_utility.TRACE ('start_time :' || start_time);
783 hr_utility.TRACE ('end_time :' || end_time);
784 END IF;
785
786 IF start_time IS NOT NULL OR end_time IS NOT NULL
787 THEN
788 IF g_debug
789 THEN
790 hr_utility.set_location (l_proc, 160);
791 END IF;
792
793 IF start_time IS NULL OR end_time IS NULL
794 THEN
795 IF g_debug
796 THEN
797 hr_utility.set_location (l_proc, 170);
798 END IF;
799
800 RAISE l_start_end_data_err;
801 ELSE
802 IF g_debug
803 THEN
804 hr_utility.set_location (l_proc, 180);
805 END IF;
806
807 l_retcode :=
808 hxt_tim_col_util.determine_pay_date (start_time,
809 end_time,
810 l_person_id,
811 l_date_worked
812 );
813
814 IF l_retcode = 1
815 THEN
816 IF g_debug
817 THEN
818 hr_utility.set_location (l_proc, 190);
819 END IF;
820
821 RAISE l_pay_date_data_err;
822 ELSIF l_retcode = 2
823 THEN
824 IF g_debug
825 THEN
826 hr_utility.set_location (l_proc, 200);
827 END IF;
828
829 RAISE l_pay_date_sys_err;
830 END IF;
831
832 IF g_debug
833 THEN
834 hr_utility.set_location (l_proc, 210);
835 END IF;
836
837 /* Calcualte hours worked */
838 l_hours := 24
839 * (TRUNC (end_time, 'MI') - TRUNC (start_time, 'MI'));
840
841 IF g_debug
842 THEN
843 hr_utility.TRACE ('l_hours :' || l_hours);
844 END IF;
845 END IF;
846 ELSIF date_worked IS NOT NULL
847 THEN
848 IF g_debug
849 THEN
850 hr_utility.set_location (l_proc, 220);
851 hr_utility.TRACE ('DATE WORKED is not null');
852 END IF;
853
854 l_date_worked := date_worked;
855
856 IF g_debug
857 THEN
858 hr_utility.TRACE ('l_date_worked :' || l_date_worked);
859 hr_utility.TRACE ('TRUNC(date_worked) :' || TRUNC (date_worked));
860 END IF;
861
862 IF date_worked <> TRUNC (date_worked)
863 THEN
864 IF g_debug
865 THEN
866 hr_utility.set_location (l_proc, 230);
867 END IF;
868
869 RAISE l_date_worked_time_err;
870 END IF;
871
872 IF g_debug
873 THEN
874 hr_utility.set_location (l_proc, 240);
875 hr_utility.TRACE ('hours :' || hours);
876 END IF;
877
878 IF hours IS NOT NULL
879 THEN
880 IF g_debug
881 THEN
882 hr_utility.set_location (l_proc, 250);
883 END IF;
884
885 l_hours := hours;
886
887 -- Removed for bug 3868006
888 -- ELSE
889 IF g_debug
890 THEN
891 hr_utility.set_location (l_proc, 260);
892 END IF;
893 -- RAISE l_hours_null_data_err;
894 END IF;
895
896 IF g_debug
897 THEN
898 hr_utility.set_location (l_proc, 270);
899 END IF;
900 ELSIF date_worked IS NULL
901 THEN
902 IF g_debug
903 THEN
904 hr_utility.set_location (l_proc, 280);
905 END IF;
906
907 RAISE l_no_time_data_err;
908 END IF;
909
910 IF g_debug
911 THEN
912 hr_utility.set_location (l_proc, 290);
913 END IF;
914
915 /* Obtain person id from user exit if call not from the time store */
916 IF timecard_source <> 'Time Store'
917 THEN
918 IF g_debug
919 THEN
920 hr_utility.set_location (l_proc, 300);
921 END IF;
922
923 l_retcode :=
924 hxt_tim_col_util.get_person_id (employee_number,
925 business_group_id, --SIR461
926 l_date_worked,
927 l_person_id,
928 l_last_name,
929 l_first_name
930 );
931
932 IF g_debug
933 THEN
934 hr_utility.set_location (l_proc, 310);
935 END IF;
936
937 IF l_retcode = 1
938 THEN
939 IF g_debug
940 THEN
941 hr_utility.set_location (l_proc, 320);
942 END IF;
943
944 RAISE l_person_id_data_err;
945 ELSIF l_retcode = 2
946 THEN
947 IF g_debug
948 THEN
949 hr_utility.set_location (l_proc, 330);
950 END IF;
951
952 RAISE l_person_id_sys_err;
953 END IF;
954 ELSE
955 IF g_debug
956 THEN
957 hr_utility.set_location (l_proc, 340);
958 END IF;
959
960 l_person_id := TO_NUMBER (employee_number);
961
962 IF g_debug
963 THEN
964 hr_utility.TRACE ('l_person_id :' || l_person_id);
965 END IF;
966
967 SELECT last_name, first_name
968 INTO l_last_name, l_first_name
969 FROM per_all_people_f
970 WHERE person_id = l_person_id
971 AND l_date_worked BETWEEN effective_start_date AND effective_end_date;
972
973 IF g_debug
974 THEN
975 hr_utility.TRACE ('l_last_name :' || l_last_name);
976 hr_utility.TRACE ('l_first_name:' || l_first_name);
977 END IF;
978 END IF;
979
980 IF g_debug
981 THEN
982 hr_utility.set_location (l_proc, 350);
983 hr_utility.TRACE ('Person ID is ' || TO_CHAR (l_person_id));
984 END IF;
985
986 /*Obtain vital employee information*/
987 BEGIN
988 IF g_debug
989 THEN
990 hr_utility.set_location (l_proc, 360);
991 END IF;
992
993 OPEN g_employee_cur (l_person_id, l_date_worked);
994
995 FETCH g_employee_cur
996 INTO l_emp_rec;
997
998 CLOSE g_employee_cur;
999 EXCEPTION
1000 WHEN NO_DATA_FOUND
1001 THEN
1002 IF g_debug
1003 THEN
1004 hr_utility.set_location (l_proc, 370);
1005 END IF;
1006
1007 l_retcode := 1;
1008 RAISE l_assign_id_data_err;
1009 WHEN OTHERS
1010 THEN
1011 IF g_debug
1012 THEN
1013 hr_utility.set_location (l_proc, 380);
1014 END IF;
1015
1016 l_retcode := 2;
1017 RAISE l_assign_id_sys_err;
1018 END;
1019
1020 IF g_debug
1021 THEN
1022 hr_utility.set_location (l_proc, 390);
1023 hr_utility.TRACE ('Got emp info');
1024 END IF;
1025
1026 /* Obtain person id for APPROVER_NUMBER from user exit */
1027 IF approver_number IS NOT NULL
1028 THEN
1029 IF g_debug
1030 THEN
1031 hr_utility.set_location (l_proc, 400);
1032 END IF;
1033
1034 l_retcode :=
1035 hxt_tim_col_util.get_person_id (approver_number,
1036 business_group_id, --SIR461
1037 l_date_worked,
1038 l_approver_id,
1039 l_appr_last_name,
1040 l_appr_first_name
1041 );
1042
1043 IF g_debug
1044 THEN
1045 hr_utility.TRACE ('l_retcode :' || l_retcode);
1046 END IF;
1047
1048 IF l_retcode = 1
1049 THEN
1050 IF g_debug
1051 THEN
1052 hr_utility.set_location (l_proc, 410);
1053 END IF;
1054
1055 RAISE l_appr_id_data_err;
1056 ELSIF l_retcode = 2
1057 THEN
1058 IF g_debug
1059 THEN
1060 hr_utility.set_location (l_proc, 420);
1061 END IF;
1062
1063 RAISE l_appr_id_sys_err;
1064 END IF;
1065
1066 IF g_debug
1067 THEN
1068 hr_utility.set_location (l_proc, 430);
1069 END IF;
1070 END IF;
1071
1072 /* Validate the timecard source */
1073 IF g_debug
1074 THEN
1075 hr_utility.set_location (l_proc, 440);
1076 hr_utility.TRACE ('BEFORE VALID TIMECARD SOURCE');
1077 END IF;
1078
1079 l_retcode :=
1080 hxt_tim_col_util.validate_timecard_source (timecard_source,
1081 l_date_worked,
1082 l_timecard_source_code
1083 );
1084
1085 IF g_debug
1086 THEN
1087 hr_utility.TRACE ('l_retcode :' || l_retcode);
1088 END IF;
1089
1090 IF l_retcode = 1
1091 THEN
1092 IF g_debug
1093 THEN
1094 hr_utility.set_location (l_proc, 450);
1095 END IF;
1096
1097 RAISE l_tim_src_data_err;
1098 ELSIF l_retcode = 2
1099 THEN
1100 IF g_debug
1101 THEN
1102 hr_utility.set_location (l_proc, 460);
1103 END IF;
1104
1105 RAISE l_tim_src_sys_err;
1106 END IF;
1107
1108 IF g_debug
1109 THEN
1110 hr_utility.set_location (l_proc, 470);
1111 hr_utility.TRACE ('VALID TIMECARD SOURCE');
1112 END IF;
1113
1114 /*Obtain element type id */
1115 IF g_debug
1116 THEN
1117 hr_utility.TRACE ('hours_type :' || hours_type);
1118 END IF;
1119
1120 IF hours_type IS NOT NULL
1121 THEN
1122 IF g_debug
1123 THEN
1124 hr_utility.set_location (l_proc, 480);
1125 END IF;
1126
1127 l_retcode :=
1128 hxt_tim_col_util.get_element_type_id (hours_type,
1129 l_date_worked,
1130 business_group_id,
1131 l_element_type_id
1132 );
1133
1134 IF g_debug
1135 THEN
1136 hr_utility.TRACE ('l_retcode :' || l_retcode);
1137 END IF;
1138
1139 IF l_retcode = 1
1140 THEN
1141 IF g_debug
1142 THEN
1143 hr_utility.set_location (l_proc, 490);
1144 END IF;
1145
1146 RAISE l_elem_type_data_err;
1147 ELSIF l_retcode = 2
1148 THEN
1149 IF g_debug
1150 THEN
1151 hr_utility.set_location (l_proc, 500);
1152 END IF;
1153
1154 RAISE l_elem_type_sys_err;
1155 END IF;
1156
1157 --
1158 IF g_debug
1159 THEN
1160 hr_utility.TRACE ('element type id is ' || l_element_type_id);
1161 END IF;
1162
1163 --
1164 -- Check Element Link eligibility
1165 --
1166 IF g_debug
1167 THEN
1168 hr_utility.set_location (l_proc, 510);
1169 END IF;
1170
1171 l_retcode :=
1172 hxt_tim_col_util.chk_element_link
1173 (p_asg_id => l_emp_rec.assignment_id,
1174 p_date_worked => l_date_worked,
1175 p_element_type_id => l_element_type_id
1176 );
1177
1178 IF g_debug
1179 THEN
1180 hr_utility.TRACE ('l_retcode :' || l_retcode);
1181 END IF;
1182
1183 IF l_retcode = 1
1184 THEN
1185 IF g_debug
1186 THEN
1187 hr_utility.set_location (l_proc, 520);
1188 END IF;
1189
1190 RAISE l_elem_link_data_err;
1191 ELSIF l_retcode = 2
1192 THEN
1193 IF g_debug
1194 THEN
1195 hr_utility.set_location (l_proc, 530);
1196 END IF;
1197
1198 RAISE l_elem_link_sys_err;
1199 END IF;
1200
1201 --
1202 IF g_debug
1203 THEN
1204 hr_utility.set_location (l_proc, 540);
1205 END IF;
1206 END IF;
1207
1208 --
1209 IF g_debug
1210 THEN
1211 hr_utility.TRACE ('element link id is ' || l_element_type_id);
1212 END IF;
1213
1214 --
1215
1216 /* Validate Amount/Hours 05-APR-00 PWM changed 'hours' variable
1217 to 'l_hours' in case user entered in/out times */
1218
1219 /* Bring API in line with Timecard behavior. 15-AUG-2001 AI
1220 IF amount IS NOT NULL THEN
1221 hr_utility.set_location(l_proc, 550);
1222 -- 05-APR-00 PWM IF hours IS NULL THEN
1223
1224 IF l_hours <> 0 THEN -- 05-APR-00 PWM Hours and Amounts are exclusive
1225 hr_utility.set_location(l_proc, 560);
1226 RAISE l_hours_amount_data_err;
1227 ELSIF l_hours = 0 AND l_element_type_id IS NULL THEN
1228 hr_utility.set_location(l_proc, 570);
1229 RAISE l_amt_hrs_elmnt_data_err;
1230 END IF;
1231 -- ELSIF l_hours IS NOT NULL THEN PWM 05-APR-00
1232
1233 ELSE
1234 hr_utility.set_location(l_proc, 580);
1235 IF l_hours = 0 THEN
1236 hr_utility.set_location(l_proc, 590);
1237 RAISE l_amt_hrs_zero_data_err;
1238 END IF;
1239 hr_utility.set_location(l_proc, 600);
1240 END IF;
1241 */
1242
1243 /* Validate Wage Code */
1244 IF wage_code IS NOT NULL
1245 THEN
1246 IF g_debug
1247 THEN
1248 hr_utility.set_location (l_proc, 610);
1249 END IF;
1250
1251 l_retcode :=
1252 hxt_tim_col_util.validate_wage_code (wage_code, l_date_worked);
1253
1254 IF g_debug
1255 THEN
1256 hr_utility.TRACE ('l_retcode :' || l_retcode);
1257 END IF;
1258
1259 IF l_retcode = 1
1260 THEN
1261 IF g_debug
1262 THEN
1263 hr_utility.set_location (l_proc, 620);
1264 END IF;
1265
1266 RAISE l_prev_wage_data_err;
1267 ELSIF l_retcode = 2
1268 THEN
1269 IF g_debug
1270 THEN
1271 hr_utility.set_location (l_proc, 630);
1272 END IF;
1273
1274 RAISE l_prev_wage_sys_err;
1275 END IF;
1276
1277 IF g_debug
1278 THEN
1279 hr_utility.set_location (l_proc, 640);
1280 END IF;
1281 END IF;
1282
1283 /* Get Earning Policy Id - If null get based on assignment, otherwise, */
1284 /* get override policy id. */
1285 IF g_debug
1286 THEN
1287 hr_utility.set_location (l_proc, 650);
1288 END IF;
1289
1290 l_retcode :=
1291 hxt_tim_col_util.get_earn_pol_id (l_emp_rec.assignment_id,
1292 l_date_worked,
1293 earning_policy,
1294 l_earn_pol_id
1295 );
1296
1297 IF g_debug
1298 THEN
1299 hr_utility.TRACE ('l_retcode :' || l_retcode);
1300 END IF;
1301
1302 IF l_retcode = 1
1303 THEN
1304 IF g_debug
1305 THEN
1306 hr_utility.set_location (l_proc, 660);
1307 END IF;
1308
1309 RAISE l_earn_pol_data_err;
1310 ELSIF l_retcode = 2
1311 THEN
1312 IF g_debug
1313 THEN
1314 hr_utility.set_location (l_proc, 670);
1315 END IF;
1316
1317 RAISE l_earn_pol_sys_err;
1318 END IF;
1319
1320 IF g_debug
1321 THEN
1322 hr_utility.TRACE ('earning policy id is ' || l_earn_pol_id);
1323 hr_utility.set_location (l_proc, 680);
1324 END IF;
1325
1326 /* Obtain project id */
1327 IF g_debug
1328 THEN
1329 hr_utility.TRACE ('project :' || project);
1330 END IF;
1331
1332 IF project IS NOT NULL
1333 THEN
1334 IF g_debug
1335 THEN
1336 hr_utility.set_location (l_proc, 680);
1337 END IF;
1338
1339 l_retcode :=
1340 hxt_tim_col_util.get_project_id (project,
1341 l_date_worked,
1342 l_project_id
1343 );
1344
1345 IF g_debug
1346 THEN
1347 hr_utility.TRACE ('l_retcode :' || l_retcode);
1348 END IF;
1349
1350 IF l_retcode = 1
1351 THEN
1352 IF g_debug
1353 THEN
1354 hr_utility.set_location (l_proc, 690);
1355 END IF;
1356
1357 RAISE l_proj_id_data_err;
1358 ELSIF l_retcode = 2
1359 THEN
1360 IF g_debug
1361 THEN
1362 hr_utility.set_location (l_proc, 700);
1363 END IF;
1364
1365 RAISE l_proj_id_sys_err;
1366 END IF;
1367
1368 IF g_debug
1369 THEN
1370 hr_utility.set_location (l_proc, 710);
1371 END IF;
1372 END IF;
1373
1374 /*Obtain task id */
1375 IF g_debug
1376 THEN
1377 hr_utility.TRACE ('task_number :' || task_number);
1378 END IF;
1379
1380 IF task_number IS NOT NULL
1381 THEN
1382 IF g_debug
1383 THEN
1384 hr_utility.set_location (l_proc, 720);
1385 END IF;
1386
1387 l_retcode :=
1388 hxt_tim_col_util.get_task_id (task_number,
1389 l_date_worked,
1390 l_project_id, /* PWM 05-APR-00 */
1391 l_task_id
1392 );
1393
1394 IF g_debug
1395 THEN
1396 hr_utility.TRACE ('l_retcode :' || l_retcode);
1397 END IF;
1398
1399 IF l_retcode = 1
1400 THEN
1401 IF g_debug
1402 THEN
1403 hr_utility.set_location (l_proc, 730);
1404 END IF;
1405
1406 RAISE l_task_id_data_err;
1407 ELSIF l_retcode = 2
1408 THEN
1409 IF g_debug
1410 THEN
1411 hr_utility.set_location (l_proc, 740);
1412 END IF;
1413
1414 RAISE l_task_id_sys_err;
1415 END IF;
1416
1417 IF g_debug
1418 THEN
1419 hr_utility.set_location (l_proc, 750);
1420 END IF;
1421 END IF;
1422
1423 /*Obtain location id */
1424 IF g_debug
1425 THEN
1426 hr_utility.TRACE ('location_code :' || location_code);
1427 END IF;
1428
1429 IF location_code IS NOT NULL
1430 THEN
1431 IF g_debug
1432 THEN
1433 hr_utility.set_location (l_proc, 760);
1434 END IF;
1435
1436 l_retcode :=
1437 hxt_tim_col_util.get_location_id (location_code,
1438 l_date_worked,
1439 l_location_id
1440 );
1441
1442 IF g_debug
1443 THEN
1444 hr_utility.TRACE ('l_retcode :' || l_retcode);
1445 END IF;
1446
1447 IF l_retcode = 1
1448 THEN
1449 IF g_debug
1450 THEN
1451 hr_utility.set_location (l_proc, 770);
1452 END IF;
1453
1454 RAISE l_locn_id_data_err;
1455 ELSIF l_retcode = 2
1456 THEN
1457 IF g_debug
1458 THEN
1459 hr_utility.set_location (l_proc, 780);
1460 END IF;
1461
1462 RAISE l_locn_id_sys_err;
1463 END IF;
1464
1465 IF g_debug
1466 THEN
1467 hr_utility.set_location (l_proc, 790);
1468 END IF;
1469 END IF;
1470
1471 /* Validate earn reason code */
1472 IF g_debug
1473 THEN
1474 hr_utility.TRACE ('earn_reason_code :' || earn_reason_code);
1475 END IF;
1476
1477 IF earn_reason_code IS NOT NULL
1478 THEN
1479 IF g_debug
1480 THEN
1481 hr_utility.set_location (l_proc, 800);
1482 END IF;
1483
1484 l_retcode :=
1485 hxt_tim_col_util.validate_earn_reason_code (earn_reason_code,
1486 l_date_worked
1487 );
1488
1489 -- l_element_type_id );
1490 IF g_debug
1491 THEN
1492 hr_utility.TRACE ('l_retcode :' || l_retcode);
1493 END IF;
1494
1495 IF l_retcode = 1
1496 THEN
1497 IF g_debug
1498 THEN
1499 hr_utility.set_location (l_proc, 810);
1500 END IF;
1501
1502 RAISE l_hours_reason_data_err;
1503 ELSIF l_retcode = 2
1504 THEN
1505 IF g_debug
1506 THEN
1507 hr_utility.set_location (l_proc, 820);
1508 END IF;
1509
1510 RAISE l_reason_code_data_err;
1511 ELSIF l_retcode = 3
1512 THEN
1513 IF g_debug
1514 THEN
1515 hr_utility.set_location (l_proc, 830);
1516 END IF;
1517
1518 RAISE l_reason_code_sys_err;
1519 END IF;
1520
1521 IF g_debug
1522 THEN
1523 hr_utility.set_location (l_proc, 840);
1524 END IF;
1525 END IF;
1526
1527 /* Validate separate check flag */
1528 IF g_debug
1529 THEN
1530 hr_utility.TRACE ('separate_check_flag :' || separate_check_flag);
1531 END IF;
1532
1533 IF separate_check_flag IS NOT NULL
1534 THEN
1535 IF g_debug
1536 THEN
1537 hr_utility.set_location (l_proc, 850);
1538 END IF;
1539
1540 l_sep_chk_flg := separate_check_flag;
1541 l_retcode :=
1542 hxt_tim_col_util.validate_separate_chk_flg (l_sep_chk_flg);
1543
1544 IF g_debug
1545 THEN
1546 hr_utility.TRACE ('l_sep_chk_flg :' || l_sep_chk_flg);
1547 hr_utility.TRACE ('l_retcode :' || l_retcode);
1548 END IF;
1549
1550 IF l_retcode = 1
1551 THEN
1552 IF g_debug
1553 THEN
1554 hr_utility.set_location (l_proc, 860);
1555 END IF;
1556
1557 RAISE l_sep_chk_flg_data_err;
1558 END IF;
1559
1560 IF g_debug
1561 THEN
1562 hr_utility.set_location (l_proc, 870);
1563 END IF;
1564 END IF;
1565
1566 /*Obtain the current time period id for this payroll and date*/
1567 IF g_debug
1568 THEN
1569 hr_utility.set_location (l_proc, 880);
1570 END IF;
1571
1572 l_retcode :=
1573 get_time_period (l_emp_rec.payroll_id,
1574 l_date_worked,
1575 l_time_period_id,
1576 l_start_date,
1577 l_end_date
1578 );
1579
1580 IF g_debug
1581 THEN
1582 hr_utility.TRACE ('l_start_date :' || l_start_date);
1583 hr_utility.TRACE ('l_end_date :' || l_end_date);
1584 hr_utility.TRACE ('l_retcode :' || l_retcode);
1585 END IF;
1586
1587 IF l_retcode = 1
1588 THEN
1589 IF g_debug
1590 THEN
1591 hr_utility.set_location (l_proc, 890);
1592 END IF;
1593
1594 RAISE l_time_per_data_err;
1595 ELSIF l_retcode = 2
1596 THEN
1597 IF g_debug
1598 THEN
1599 hr_utility.set_location (l_proc, 900);
1600 END IF;
1601
1602 RAISE l_time_per_sys_err;
1603 END IF;
1604
1605 g_time_period_err_id := l_time_period_id;
1606
1607 IF g_debug
1608 THEN
1609 hr_utility.TRACE ('Time Period id is ' || l_time_period_id);
1610 END IF;
1611
1612 /*Determine effective start date*/
1613 IF g_debug
1614 THEN
1615 hr_utility.set_location (l_proc, 910);
1616 END IF;
1617
1618 --Bug#2995224
1619 -- IF l_emp_rec.effective_start_date > l_start_date
1620 -- THEN
1621 -- hr_utility.set_location (l_proc, 920);
1622 -- l_start_date := l_emp_rec.effective_start_date;
1623 -- END IF;
1624
1625 -- IF l_emp_rec.effective_end_date < l_end_date
1626 -- THEN
1627 -- hr_utility.set_location (l_proc, 930);
1628 -- l_end_date := l_emp_rec.effective_end_date;
1629 -- END IF;
1630
1631 /*Make cost allocation entry. */
1632 IF g_debug
1633 THEN
1634 hr_utility.set_location (l_proc, 940);
1635 END IF;
1636
1637 cost_allocation_entry (concat_cost_segments,
1638 cost_segment1,
1639 cost_segment2,
1640 cost_segment3,
1641 cost_segment4,
1642 cost_segment5,
1643 cost_segment6,
1644 cost_segment7,
1645 cost_segment8,
1646 cost_segment9,
1647 cost_segment10,
1648 cost_segment11,
1649 cost_segment12,
1650 cost_segment13,
1651 cost_segment14,
1652 cost_segment15,
1653 cost_segment16,
1654 cost_segment17,
1655 cost_segment18,
1656 cost_segment19,
1657 cost_segment20,
1658 cost_segment21,
1659 cost_segment22,
1660 cost_segment23,
1661 cost_segment24,
1662 cost_segment25,
1663 cost_segment26,
1664 cost_segment27,
1665 cost_segment28,
1666 cost_segment29,
1667 cost_segment30,
1668 business_group_id,
1669 l_ffv_cost_center_id,
1670 otm_error,
1671 oracle_error
1672 );
1673
1674 IF g_debug
1675 THEN
1676 hr_utility.TRACE ('Cost Alloc entry made ');
1677 END IF;
1678
1679 -----------------------------------------------------------------------------
1680 /*Check for an existing timecard */
1681 IF g_debug
1682 THEN
1683 hr_utility.set_location (l_proc, 950);
1684 hr_utility.TRACE ('calling check_for_timecard');
1685 END IF;
1686
1687 l_retcode :=
1688 check_for_timecard (l_person_id,
1689 l_time_period_id,
1690 l_timecard_id,
1691 l_auto_gen_flag
1692 );
1693
1694 IF g_debug
1695 THEN
1696 hr_utility.TRACE ('after call to check_for_timecard');
1697 hr_utility.TRACE ('l_retcode :' || l_retcode);
1698 hr_utility.TRACE ('l_timecard_id :' || l_timecard_id);
1699 hr_utility.TRACE ('l_auto_gen_flag :' || l_auto_gen_flag);
1700 hr_utility.TRACE ('l_retcode :' || l_retcode);
1701 hr_utility.set_location (l_proc, 960);
1702 END IF;
1703
1704 IF l_retcode = 0
1705 THEN
1706 IF g_debug
1707 THEN
1708 hr_utility.set_location (l_proc, 970);
1709 END IF;
1710
1711 g_timecard_err_id := l_timecard_id;
1712 ELSIF l_retcode = 1
1713 THEN
1714 IF g_debug
1715 THEN
1716 hr_utility.set_location (l_proc, 980);
1717 hr_utility.TRACE ('BEFORE create timecard');
1718 END IF;
1719
1720 l_retcode :=
1721 create_timecard (l_person_id,
1722 business_group_id,
1723 l_emp_rec.assignment_id,
1724 l_emp_rec.payroll_id,
1725 l_time_period_id,
1726 l_approver_id,
1727 l_timecard_source_code,
1728 l_timecard_id
1729 );
1730
1731 IF g_debug
1732 THEN
1733 hr_utility.TRACE ('l_retcode :' || l_retcode);
1734 END IF;
1735
1736 IF l_retcode = 1
1737 THEN
1738 IF g_debug
1739 THEN
1740 hr_utility.set_location (l_proc, 990);
1741 END IF;
1742
1743 RAISE l_make_card_data_err;
1744 ELSIF l_retcode = 2
1745 THEN
1746 IF g_debug
1747 THEN
1748 hr_utility.set_location (l_proc, 1000);
1749 END IF;
1750
1751 RAISE l_make_card_sys_err;
1752 END IF;
1753
1754 IF g_debug
1755 THEN
1756 hr_utility.set_location (l_proc, 1010);
1757 END IF;
1758
1759 g_timecard_err_id := l_timecard_id;
1760
1761 IF g_debug
1762 THEN
1763 hr_utility.TRACE ('Created TIMECARD. ID is ' || l_timecard_id);
1764 END IF;
1765
1766 /* Create holiday hours on the new timecard */
1767 /*l_retcode := create_holiday_hours(l_person_id,
1768 l_emp_rec.hcl_id,
1769 l_emp_rec.hxt_rotation_plan,--SIR344
1770 l_start_date,
1771 l_end_date,
1772 l_timecard_id,
1773 wage_code,
1774 l_task_id,
1775 l_location_id,
1776 l_project_id,
1777 l_earn_pol_id,
1778 earn_reason_code,
1779 comment,
1780 rate_multiple,
1781 hourly_rate,
1782 amount,
1783 l_sep_chk_flg,
1784 l_emp_rec.assignment_id,
1785 time_summary_id,
1786 tim_sum_eff_start_date,
1787 tim_sum_eff_end_date,
1788 created_by,
1789 last_updated_by,
1790 last_update_login,
1791 writesum_yn,
1792 explode_yn,
1793 l_batch_status,
1794 dt_update_mode, --SIR290
1795 p_time_building_block_id,
1796 p_time_building_block_ovn,
1797 l_otm_error,
1798 l_oracle_error,
1799 l_created_tim_sum_id,
1800 start_time,
1801 end_time);
1802 --p_mode);
1803
1804
1805 hr_utility.set_location(l_proc, 1020);
1806 hr_utility.trace('Created Holiday Hours ');
1807 hr_utility.trace('l_retcode :'||l_retcode);
1808
1809 IF l_retcode = 1 THEN
1810 hr_utility.set_location(l_proc, 1030);
1811 RAISE l_make_hol_data_err;
1812 ELSIF l_retcode = 2 THEN
1813 hr_utility.set_location(l_proc, 1040);
1814 RAISE l_make_hol_sys_err;
1815 END IF;
1816 */
1817 IF g_debug
1818 THEN
1819 hr_utility.set_location (l_proc, 1050);
1820 END IF;
1821 ELSIF l_retcode = 2
1822 THEN
1823 IF g_debug
1824 THEN
1825 hr_utility.set_location (l_proc, 1060);
1826 END IF;
1827
1828 RAISE l_istimecard_sys_err;
1829 END IF;
1830
1831 -----------------------------------------------------------------------------
1832
1833 /*Check to see if pre-existing timecards were autogened*/
1834 IF l_auto_gen_flag = 'A'
1835 THEN
1836 IF g_debug
1837 THEN
1838 hr_utility.set_location (l_proc, 1070);
1839 END IF;
1840
1841 RAISE l_autogen_error;
1842 END IF;
1843
1844 IF g_debug
1845 THEN
1846 hr_utility.set_location (l_proc, 1080);
1847 END IF;
1848
1849 l_retcode := check_for_batch_status (g_batch_err_id, l_batch_status);
1850
1851 IF g_debug
1852 THEN
1853 hr_utility.TRACE ('l_retcode :' || l_retcode);
1854 hr_utility.TRACE ('delete_yn:' || delete_yn);
1855 END IF;
1856
1857 IF delete_yn = 'N'
1858 THEN
1859 -- Bugs 3384941, 3382457, 3381642 fix
1860 -- i.e., If it is not a deleted record then create it.
1861 -- If it is a deleted record then we should skip this call to
1862 -- record_hours_worked
1863
1864 -- Insert hours to the hxt_sum_hours_worked table and
1865 -- call generate details
1866 IF g_debug
1867 THEN
1868 hr_utility.TRACE ('BEFORE record_hours worked');
1869 END IF;
1870
1871 l_retcode :=
1872 record_hours_worked (timecard_source,
1873 FALSE,
1874 l_timecard_id,
1875 l_emp_rec.assignment_id,
1876 l_person_id,
1877 l_date_worked,
1878 l_element_type_id,
1879 l_hours,
1880 start_time,
1881 end_time,
1882 l_start_date,
1883 wage_code,
1884 l_task_id,
1885 l_location_id,
1886 l_project_id,
1887 l_earn_pol_id,
1888 earn_reason_code,
1889 l_ffv_cost_center_id,
1890 COMMENT,
1891 rate_multiple,
1892 hourly_rate,
1893 amount,
1894 l_sep_chk_flg,
1895 time_summary_id,
1896 tim_sum_eff_start_date,
1897 tim_sum_eff_end_date,
1898 created_by,
1899 last_updated_by,
1900 last_update_login,
1901 writesum_yn,
1902 explode_yn,
1903 l_batch_status,
1904 dt_update_mode,
1905 p_time_building_block_id,
1906 p_time_building_block_ovn,
1907 l_otm_error,
1908 l_oracle_error,
1909 l_created_tim_sum_id,
1910 p_state_name,
1911 p_county_name,
1912 p_city_name,
1913 p_zip_code
1914 -- , p_mode
1915 );
1916
1917 IF g_debug
1918 THEN
1919 hr_utility.set_location (l_proc, 1090);
1920 hr_utility.TRACE ('AFTER record_hours worked');
1921 hr_utility.TRACE ( 'AFTER record_hours worked RET CODE IS '
1922 || TO_CHAR (l_retcode)
1923 );
1924 hr_utility.TRACE ('OTM ERROR IS ' || l_otm_error);
1925 hr_utility.TRACE ('ORACLE ERROR IS ' || l_oracle_error);
1926 END IF;
1927
1928 IF l_retcode = 1
1929 THEN
1930 IF g_debug
1931 THEN
1932 hr_utility.set_location (l_proc, 1100);
1933 END IF;
1934
1935 otm_error := l_otm_error;
1936 oracle_error := l_oracle_error;
1937 RAISE l_rec_hours_data_err;
1938 ELSIF l_retcode = 2
1939 THEN
1940 IF g_debug
1941 THEN
1942 hr_utility.set_location (l_proc, 1110);
1943 END IF;
1944
1945 otm_error := l_otm_error;
1946 oracle_error := l_oracle_error;
1947 RAISE l_rec_hours_sys_err;
1948 END IF;
1949
1950 --
1951 created_tim_sum_id := l_created_tim_sum_id;
1952
1953 IF g_debug
1954 THEN
1955 hr_utility.TRACE ('created_tim_sum_id :' || created_tim_sum_id);
1956 END IF;
1957
1958 --
1959 otm_error := NULL;
1960 oracle_error := NULL;
1961 END IF;
1962
1963 IF p_validate
1964 THEN
1965 IF g_debug
1966 THEN
1967 hr_utility.set_location (l_proc, 1120);
1968 hr_utility.TRACE ('VALIDATE only so ROLLBACK');
1969 END IF;
1970
1971 ROLLBACK TO only_validate;
1972 created_tim_sum_id := 0;
1973
1974 IF g_debug
1975 THEN
1976 hr_utility.TRACE ('created_tim_sum_id :' || created_tim_sum_id);
1977 END IF;
1978 END IF; -- End of p_mode check
1979
1980 IF g_debug
1981 THEN
1982 hr_utility.set_location (l_proc, 1130);
1983 END IF;
1984
1985 RETURN;
1986 EXCEPTION
1987 WHEN l_person_id_data_err
1988 THEN
1989 IF g_debug
1990 THEN
1991 hr_utility.set_location (l_proc, 1140);
1992 END IF;
1993
1994 fnd_message.set_name ('HXT', 'HXT_39308_EMPLYEE_NF');
1995 fnd_message.set_token ('EMP_NUMBER', employee_number);
1996 l_otm_error := fnd_message.get;
1997 otm_error := l_otm_error;
1998 oracle_error := SQLERRM;
1999 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2000 --2278400
2001 RETURN;
2002 WHEN l_person_id_sys_err
2003 THEN
2004 IF g_debug
2005 THEN
2006 hr_utility.set_location (l_proc, 1150);
2007 END IF;
2008
2009 fnd_message.set_name ('HXT', 'HXT_39529_EMP_DATA_SYS_ERR');
2010 fnd_message.set_token ('EMP_NUMBER', employee_number);
2011 l_otm_error := fnd_message.get;
2012 otm_error := l_otm_error;
2013 oracle_error := SQLERRM;
2014 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2015 --2278400
2016 RETURN;
2017 WHEN l_appr_id_data_err
2018 THEN
2019 IF g_debug
2020 THEN
2021 hr_utility.set_location (l_proc, 1160);
2022 END IF;
2023
2024 fnd_message.set_name ('HXT', 'HXT_39530_APPRVR_NF');
2025 fnd_message.set_token ('APP_NUMBER', approver_number);
2026 l_otm_error := fnd_message.get;
2027 otm_error := l_otm_error;
2028 oracle_error := SQLERRM;
2029 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2030 --2278400
2031 RETURN;
2032 WHEN l_appr_id_sys_err
2033 THEN
2034 IF g_debug
2035 THEN
2036 hr_utility.set_location (l_proc, 1170);
2037 END IF;
2038
2039 fnd_message.set_name ('HXT', 'HXT_39531_APP_DATA_SYS_ERR');
2040 fnd_message.set_token ('APP_NUMBER', approver_number);
2041 l_otm_error := fnd_message.get;
2042 otm_error := l_otm_error;
2043 oracle_error := SQLERRM;
2044 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2045 --2278400
2046 RETURN;
2047 WHEN l_assign_id_data_err
2048 THEN
2049 IF g_debug
2050 THEN
2051 hr_utility.set_location (l_proc, 1180);
2052 END IF;
2053
2054 fnd_message.set_name ('HXT', 'HXT_39306_ASSIGN_NF');
2055 fnd_message.set_token ('FIRST_NAME', l_first_name);
2056 fnd_message.set_token ('LAST_NAME', l_last_name);
2057 fnd_message.set_token ('EMP_NUMBER', employee_number);
2058 l_otm_error := fnd_message.get;
2059 otm_error := l_otm_error;
2060 oracle_error := SQLERRM;
2061 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2062 --2278400
2063 RETURN;
2064 WHEN l_assign_id_sys_err
2065 THEN
2066 IF g_debug
2067 THEN
2068 hr_utility.set_location (l_proc, 1190);
2069 END IF;
2070
2071 fnd_message.set_name ('HXT', 'HXT_39319_ERR_GET_ASSIGN');
2072 l_otm_error := fnd_message.get;
2073 otm_error := l_otm_error;
2074 oracle_error := SQLERRM;
2075 call_hxthxc_gen_error ('HXT', 'HXT_39319_ERR_GET_ASSIGN', NULL);
2076 --2278400
2077 RETURN;
2078 WHEN l_pay_date_data_err
2079 THEN
2080 IF g_debug
2081 THEN
2082 hr_utility.set_location (l_proc, 1200);
2083 END IF;
2084
2085 fnd_message.set_name ('HXT', 'HXT_39331_CANT_CALC_DAT_WRKED');
2086 fnd_message.set_token ('START_TIME', TO_CHAR (start_time));
2087 l_otm_error := fnd_message.get;
2088 otm_error := l_otm_error;
2089 oracle_error := SQLERRM;
2090 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2091 --2278400
2092 RETURN;
2093 WHEN l_pay_date_sys_err
2094 THEN
2095 IF g_debug
2096 THEN
2097 hr_utility.set_location (l_proc, 1210);
2098 END IF;
2099
2100 fnd_message.set_name ('HXT', 'HXT_39323_ERR_DATE_WRKED');
2101 l_otm_error := fnd_message.get;
2102 otm_error := l_otm_error;
2103 oracle_error := SQLERRM;
2104 call_hxthxc_gen_error ('HXT', 'HXT_39323_ERR_DATE_WRKED', NULL);
2105 --2278400
2106 RETURN;
2107 WHEN l_prev_wage_data_err
2108 THEN
2109 IF g_debug
2110 THEN
2111 hr_utility.set_location (l_proc, 1220);
2112 END IF;
2113
2114 fnd_message.set_name ('HXT', 'HXT_39532_INV_PREV_WAGE_CODE');
2115 fnd_message.set_token ('WAGE_CODE', wage_code);
2116 l_error_text := fnd_message.get;
2117 otm_error := l_error_text;
2118 oracle_error := SQLERRM;
2119 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', otm_error);
2120 --2278400
2121 RETURN;
2122 WHEN l_prev_wage_sys_err
2123 THEN
2124 IF g_debug
2125 THEN
2126 hr_utility.set_location (l_proc, 1230);
2127 END IF;
2128
2129 fnd_message.set_name ('HXT', 'HXT_39533_PREV_WAGE_CD_SYS_ERR');
2130 l_error_text := fnd_message.get;
2131 otm_error := l_error_text;
2132 oracle_error := SQLERRM;
2133 call_hxthxc_gen_error ('HXT', 'HXT_39533_PREV_WAGE_CD_SYS_ERR', NULL);
2134 --2278400
2135 RETURN;
2136 WHEN l_elem_type_data_err
2137 THEN
2138 IF g_debug
2139 THEN
2140 hr_utility.set_location (l_proc, 1240);
2141 END IF;
2142
2143 fnd_message.set_name ('HXT', 'HXT_39534_ELEM_TYPE_NF');
2144 fnd_message.set_token ('HRS_TYPE', hours_type);
2145 l_otm_error := fnd_message.get;
2146 otm_error := l_otm_error;
2147 oracle_error := SQLERRM;
2148 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', otm_error);
2149 --2278400
2150 RETURN;
2151 WHEN l_elem_type_sys_err
2152 THEN
2153 IF g_debug
2154 THEN
2155 hr_utility.set_location (l_proc, 1250);
2156 END IF;
2157
2158 fnd_message.set_name ('HXT', 'HXT_39535_ELEM_TYPE_SYS_ERR');
2159 l_otm_error := fnd_message.get;
2160 otm_error := l_otm_error;
2161 oracle_error := SQLERRM;
2162 call_hxthxc_gen_error ('HXT', 'HXT_39535_ELEM_TYPE_SYS_ERR', NULL);
2163 --2278400
2164 RETURN;
2165 WHEN l_elem_link_data_err
2166 THEN
2167 IF g_debug
2168 THEN
2169 hr_utility.set_location (l_proc, 1260);
2170 END IF;
2171
2172 fnd_message.set_name ('HXT', 'HXT_ELEM_LINK_NF');
2173 fnd_message.set_token ('HRS_TYPE', hours_type);
2174 l_otm_error := fnd_message.get;
2175 otm_error := l_otm_error;
2176 oracle_error := SQLERRM;
2177 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2178 --2278400
2179 RETURN;
2180 WHEN l_elem_link_sys_err
2181 THEN
2182 IF g_debug
2183 THEN
2184 hr_utility.set_location (l_proc, 1270);
2185 END IF;
2186
2187 fnd_message.set_name ('HXT', 'HXT_ELEM_LINK_SYS_ERR');
2188 l_otm_error := fnd_message.get;
2189 otm_error := l_otm_error;
2190 oracle_error := SQLERRM;
2191 call_hxthxc_gen_error ('HXT', 'HXT_ELEM_LINK_SYS_ERR', NULL);
2192 --2278400
2193 RETURN;
2194 WHEN l_task_id_data_err
2195 THEN
2196 IF g_debug
2197 THEN
2198 hr_utility.set_location (l_proc, 1280);
2199 END IF;
2200
2201 fnd_message.set_name ('HXT', 'HXT_39536_TASK_ID_NF');
2202 fnd_message.set_token ('TASK_NUMBER', task_number);
2203 l_otm_error := fnd_message.get;
2204 otm_error := l_otm_error;
2205 oracle_error := SQLERRM;
2206 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2207 --2278400
2208 RETURN;
2209 WHEN l_task_id_sys_err
2210 THEN
2211 IF g_debug
2212 THEN
2213 hr_utility.set_location (l_proc, 1290);
2214 END IF;
2215
2216 fnd_message.set_name ('HXT', 'HXT_39537_TASK_ID_SYS_ERR');
2217 l_otm_error := fnd_message.get;
2218 otm_error := l_otm_error;
2219 oracle_error := SQLERRM;
2220 RETURN;
2221 WHEN l_locn_id_data_err
2222 THEN
2223 IF g_debug
2224 THEN
2225 hr_utility.set_location (l_proc, 1300);
2226 END IF;
2227
2228 fnd_message.set_name ('HXT', 'HXT_39538_LOC_ID_NF');
2229 fnd_message.set_token ('LOC_CODE', location_code);
2230 l_otm_error := fnd_message.get;
2231 otm_error := l_otm_error;
2232 oracle_error := SQLERRM;
2233 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2234 --2278400
2235 RETURN;
2236 WHEN l_locn_id_sys_err
2237 THEN
2238 IF g_debug
2239 THEN
2240 hr_utility.set_location (l_proc, 1310);
2241 END IF;
2242
2243 fnd_message.set_name ('HXT', 'HXT_39539_LOC_ID_SYS_ERR');
2244 l_otm_error := fnd_message.get;
2245 otm_error := l_otm_error;
2246 oracle_error := SQLERRM;
2247 call_hxthxc_gen_error ('HXT', 'HXT_39539_LOC_ID_SYS_ERR', NULL);
2248 --2278400
2249 RETURN;
2250 WHEN l_proj_id_data_err
2251 THEN
2252 IF g_debug
2253 THEN
2254 hr_utility.set_location (l_proc, 1320);
2255 END IF;
2256
2257 fnd_message.set_name ('HXT', 'HXT_39540_PRJ_ID_NF');
2258 fnd_message.set_token ('PRJ_NUMBER', project);
2259 l_otm_error := fnd_message.get;
2260 otm_error := l_otm_error;
2261 oracle_error := SQLERRM;
2262 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2263 --2278400
2264 RETURN;
2265 WHEN l_proj_id_sys_err
2266 THEN
2267 IF g_debug
2268 THEN
2269 hr_utility.set_location (l_proc, 1330);
2270 END IF;
2271
2272 fnd_message.set_name ('HXT', 'HXT_39541_PRJ_ID_SYS_ERR');
2273 l_otm_error := fnd_message.get;
2274 otm_error := l_otm_error;
2275 oracle_error := SQLERRM;
2276 call_hxthxc_gen_error ('HXT', 'HXT_39541_PRJ_ID_SYS_ERR', NULL);
2277 --2278400
2278 RETURN;
2279 WHEN l_hours_reason_data_err
2280 THEN
2281 IF g_debug
2282 THEN
2283 hr_utility.set_location (l_proc, 1340);
2284 END IF;
2285
2286 fnd_message.set_name ('HXT', 'HXT_39542_ERN_RSN_WO_HRS_TYPE');
2287 l_otm_error := fnd_message.get;
2288 otm_error := l_otm_error;
2289 oracle_error := SQLERRM;
2290 call_hxthxc_gen_error ('HXT', 'HXT_39542_ERN_RSN_WO_HRS_TYPE', NULL);
2291 --2278400
2292 RETURN;
2293 WHEN l_reason_code_data_err
2294 THEN
2295 IF g_debug
2296 THEN
2297 hr_utility.set_location (l_proc, 1350);
2298 END IF;
2299
2300 fnd_message.set_name ('HXT', 'HXT_39543_ERN_RSN_NF');
2301 fnd_message.set_token ('ERN_RSN_CD', earn_reason_code);
2302 l_otm_error := fnd_message.get;
2303 otm_error := l_otm_error;
2304 oracle_error := SQLERRM;
2305 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2306 --2278400
2307 RETURN;
2308 WHEN l_reason_code_sys_err
2309 THEN
2310 IF g_debug
2311 THEN
2312 hr_utility.set_location (l_proc, 1360);
2313 END IF;
2314
2315 fnd_message.set_name ('HXT', 'HXT_39544_ERN_RSN_SYS_ERR');
2316 l_otm_error := fnd_message.get;
2317 otm_error := l_otm_error;
2318 oracle_error := SQLERRM;
2319 call_hxthxc_gen_error ('HXT', 'HXT_39544_ERN_RSN_SYS_ERR', NULL);
2320 --2278400
2321 RETURN;
2322 WHEN l_sep_chk_flg_data_err
2323 THEN
2324 IF g_debug
2325 THEN
2326 hr_utility.set_location (l_proc, 1370);
2327 END IF;
2328
2329 fnd_message.set_name ('HXT', 'HXT_39545_SEP_CHK_NF');
2330 fnd_message.set_token ('SEP_CHK', l_sep_chk_flg);
2331 l_otm_error := fnd_message.get;
2332 otm_error := l_otm_error;
2333 oracle_error := SQLERRM;
2334 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2335 --2278400
2336 RETURN;
2337 WHEN l_time_per_data_err
2338 THEN
2339 IF g_debug
2340 THEN
2341 hr_utility.set_location (l_proc, 1380);
2342 END IF;
2343
2344 fnd_message.set_name ('HXT', 'HXT_39330_CANT_CALC_TIM_PER');
2345 fnd_message.set_token ('DATE_WORKED', TO_CHAR (l_date_worked));
2346 fnd_message.set_token ('PAYROLL', TO_CHAR (l_emp_rec.payroll_id));
2347 l_otm_error := fnd_message.get;
2348 otm_error := l_otm_error;
2349 oracle_error := SQLERRM;
2350 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2351 --2278400
2352 RETURN;
2353 WHEN l_time_per_sys_err
2354 THEN
2355 IF g_debug
2356 THEN
2357 hr_utility.set_location (l_proc, 1390);
2358 END IF;
2359
2360 fnd_message.set_name ('HXT', 'HXT_39324_ERR_TIME_PERIOD');
2361 fnd_message.set_token ('SQLERR', SQLERRM);
2362 l_otm_error := fnd_message.get;
2363 otm_error := l_otm_error;
2364 oracle_error := SQLERRM;
2365 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2366 --2278400
2367 RETURN;
2368 WHEN l_istimecard_sys_err
2369 THEN
2370 IF g_debug
2371 THEN
2372 hr_utility.set_location (l_proc, 1400);
2373 END IF;
2374
2375 fnd_message.set_name ('HXT', 'HXT_39298_ERR_GET_TIMCARD');
2376 l_otm_error := fnd_message.get;
2377 otm_error := l_otm_error;
2378 oracle_error := SQLERRM;
2379 call_hxthxc_gen_error ('HXT', 'HXT_39298_ERR_GET_TIMCARD', NULL);
2380 --2278400
2381 RETURN;
2382 WHEN l_autogen_error
2383 THEN
2384 IF g_debug
2385 THEN
2386 hr_utility.set_location (l_proc, 1410);
2387 END IF;
2388
2389 fnd_message.set_name ('HXT', 'HXT_39267_AG_TCARD_EXISTS');
2390 fnd_message.set_token ('FIRST_NAME', l_first_name);
2391 fnd_message.set_token ('LAST_NAME', l_last_name);
2392 fnd_message.set_token ('EMP_NUMBER', employee_number);
2393 l_otm_error := fnd_message.get;
2394 otm_error := l_otm_error;
2395 oracle_error := SQLERRM;
2396 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2397 --2278400
2398 RETURN;
2399 WHEN l_make_card_data_err
2400 THEN
2401 IF g_debug
2402 THEN
2403 hr_utility.set_location (l_proc, 1420);
2404 END IF;
2405
2406 fnd_message.set_name ('HXT', 'HXT_39291_CRT_TCARD_ERR');
2407 fnd_message.set_token ('FIRST_NAME', l_first_name);
2408 fnd_message.set_token ('LAST_NAME', l_last_name);
2409 fnd_message.set_token ('EMP_NUMBER', employee_number);
2410 l_otm_error := fnd_message.get;
2411 otm_error := l_otm_error;
2412 oracle_error := SQLERRM;
2413 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2414 --2278400
2415 RETURN;
2416 WHEN l_make_card_sys_err
2417 THEN
2418 IF g_debug
2419 THEN
2420 hr_utility.set_location (l_proc, 1430);
2421 END IF;
2422
2423 fnd_message.set_name ('HXT', 'HXT_39318_ERR_CREAT_TCARD');
2424 fnd_message.set_token ('FIRST_NAME', l_first_name);
2425 fnd_message.set_token ('LAST_NAME', l_last_name);
2426 fnd_message.set_token ('EMP_NUMBER', employee_number);
2427 l_otm_error := fnd_message.get;
2428 l_oracle_error := SQLERRM;
2429 otm_error := l_otm_error;
2430 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2431 --2278400
2432 oracle_error := SQLERRM;
2433 RETURN;
2434 WHEN l_make_hol_data_err
2435 THEN
2436 IF g_debug
2437 THEN
2438 hr_utility.set_location (l_proc, 1440);
2439 END IF;
2440
2441 fnd_message.set_name ('HXT', 'HXT_39292_CRT_HOL_HRS');
2442 fnd_message.set_token ('FIRST_NAME', l_first_name);
2443 fnd_message.set_token ('LAST_NAME', l_last_name);
2444 fnd_message.set_token ('EMP_NUMBER', employee_number);
2445 l_otm_error := fnd_message.get;
2446 otm_error := l_otm_error;
2447 oracle_error := SQLERRM;
2448 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2449 --2278400
2450 RETURN;
2451 WHEN l_make_hol_sys_err
2452 THEN
2453 IF g_debug
2454 THEN
2455 hr_utility.set_location (l_proc, 1450);
2456 END IF;
2457
2458 fnd_message.set_name ('HXT', 'HXT_39320_ERR_CREAT_HOL');
2459 fnd_message.set_token ('FIRST_NAME', l_first_name);
2460 fnd_message.set_token ('LAST_NAME', l_last_name);
2461 fnd_message.set_token ('EMP_NUMBER', employee_number);
2462 l_otm_error := fnd_message.get;
2463 otm_error := l_otm_error;
2464 oracle_error := SQLERRM;
2465 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2466 --2278400
2467 RETURN;
2468 WHEN l_rec_hours_data_err
2469 THEN
2470 IF g_debug
2471 THEN
2472 hr_utility.set_location (l_proc, 1460);
2473 END IF;
2474
2475 -- GPM v115.23
2476 -- there is no point writing over a specific system error
2477 -- with a generic translated error
2478 IF (l_otm_error IS NULL)
2479 THEN
2480 fnd_message.set_name ('HXT', 'HXT_39293_REC_HRS_ERR');
2481 l_otm_error := fnd_message.get;
2482 END IF;
2483
2484 otm_error := l_otm_error;
2485
2486 IF (l_oracle_error IS NULL)
2487 THEN
2488 oracle_error := SQLERRM;
2489 ELSE
2490 oracle_error := l_oracle_error;
2491 END IF;
2492
2493 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2494 --2278400
2495 RETURN;
2496 WHEN l_rec_hours_sys_err
2497 THEN
2498 IF g_debug
2499 THEN
2500 hr_utility.set_location (l_proc, 1470);
2501 END IF;
2502
2503 -- GPM v115.23
2504 -- there is no point writing over a specific system error
2505 -- with a generic translated error
2506 IF (l_otm_error IS NULL)
2507 THEN
2508 fnd_message.set_name ('HXT', 'HXT_39321_ERR_REC_HRS');
2509 l_otm_error := fnd_message.get;
2510 END IF;
2511
2512 otm_error := l_otm_error;
2513
2514 IF (l_oracle_error IS NULL)
2515 THEN
2516 oracle_error := SQLERRM;
2517 ELSE
2518 oracle_error := l_oracle_error;
2519 END IF;
2520
2521 l_otm_error := fnd_message.get;
2522 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2523 --2278400
2524 RETURN;
2525 WHEN l_hours_amount_data_err
2526 THEN
2527 IF g_debug
2528 THEN
2529 hr_utility.set_location (l_proc, 1480);
2530 END IF;
2531
2532 fnd_message.set_name ('HXT', 'HXT_39527_HRS_REQ_IF_AMT');
2533 l_otm_error := fnd_message.get;
2534 otm_error := l_otm_error;
2535 oracle_error := SQLERRM;
2536 call_hxthxc_gen_error ('HXT', 'HXT_39527_HRS_REQ_IF_AMT', NULL);
2537 --2278400
2538 RETURN;
2539 WHEN l_amt_hrs_elmnt_data_err
2540 THEN
2541 IF g_debug
2542 THEN
2543 hr_utility.set_location (l_proc, 1490);
2544 END IF;
2545
2546 fnd_message.set_name ('HXT', 'HXT_39528_HRS_NE0_IF_NO_HRSTYP');
2547 l_otm_error := fnd_message.get;
2548 otm_error := l_otm_error;
2549 oracle_error := SQLERRM;
2550 call_hxthxc_gen_error ('HXT', 'HXT_39528_HRS_NE0_IF_NO_HRSTYP', NULL);
2551 --2278400
2552 RETURN;
2553 WHEN l_amt_hrs_zero_data_err
2554 THEN
2555 IF g_debug
2556 THEN
2557 hr_utility.set_location (l_proc, 1500);
2558 END IF;
2559
2560 fnd_message.set_name ('HXT', 'HXT_39546_HRS_NE0_IF_NO_AMT');
2561 l_otm_error := fnd_message.get;
2562 otm_error := l_otm_error;
2563 oracle_error := SQLERRM;
2564 call_hxthxc_gen_error ('HXT', 'HXT_39546_HRS_NE0_IF_NO_AMT', NULL);
2565 --2278400
2566 RETURN;
2567 WHEN l_start_end_data_err
2568 THEN
2569 IF g_debug
2570 THEN
2571 hr_utility.set_location (l_proc, 1510);
2572 END IF;
2573
2574 fnd_message.set_name ('HXT', 'HXT_39547_START_END_REQ');
2575 l_otm_error := fnd_message.get;
2576 otm_error := l_otm_error;
2577 oracle_error := SQLERRM;
2578 call_hxthxc_gen_error ('HXT', 'HXT_39547_START_END_REQ', NULL);
2579 --2278400
2580 RETURN;
2581 WHEN l_hours_null_data_err
2582 THEN
2583 IF g_debug
2584 THEN
2585 hr_utility.set_location (l_proc, 1520);
2586 END IF;
2587
2588 fnd_message.set_name ('HXT', 'HXT_39548_HRS_REQ_IF_DT_WRK');
2589 l_otm_error := fnd_message.get;
2590 otm_error := l_otm_error;
2591 oracle_error := SQLERRM;
2592 call_hxthxc_gen_error ('HXT', 'HXT_39548_HRS_REQ_IF_DT_WRK', NULL);
2593 --2278400
2594 RETURN;
2595 WHEN l_no_time_data_err
2596 THEN
2597 IF g_debug
2598 THEN
2599 hr_utility.set_location (l_proc, 1530);
2600 END IF;
2601
2602 fnd_message.set_name ('HXT', 'HXT_39549_ST_END_OR_DT_WRK_REQ');
2603 l_otm_error := fnd_message.get;
2604 otm_error := l_otm_error;
2605 oracle_error := SQLERRM;
2606 call_hxthxc_gen_error ('HXT', 'HXT_39549_ST_END_OR_DT_WRK_REQ', NULL);
2607 --2278400
2608 RETURN;
2609 WHEN l_tim_src_data_err
2610 THEN
2611 IF g_debug
2612 THEN
2613 hr_utility.set_location (l_proc, 1540);
2614 END IF;
2615
2616 fnd_message.set_name ('HXT', 'HXT_39550_TIM_SRC_NF');
2617 l_otm_error := fnd_message.get;
2618 otm_error := l_otm_error;
2619 oracle_error := SQLERRM;
2620 call_hxthxc_gen_error ('HXT', 'HXT_39550_TIM_SRC_NF', NULL);
2621 --2278400
2622 RETURN;
2623 WHEN l_tim_src_sys_err
2624 THEN
2625 IF g_debug
2626 THEN
2627 hr_utility.set_location (l_proc, 1550);
2628 END IF;
2629
2630 fnd_message.set_name ('HXT', 'HXT_39551_TIM_SRC_SYS_ERR');
2631 l_otm_error := fnd_message.get;
2632 otm_error := l_otm_error;
2633 oracle_error := SQLERRM;
2634 call_hxthxc_gen_error ('HXT', 'HXT_39551_TIM_SRC_SYS_ERR', NULL);
2635 --2278400
2636 RETURN;
2637 WHEN l_time_summary_id_data_err
2638 THEN
2639 IF g_debug
2640 THEN
2641 hr_utility.set_location (l_proc, 1560);
2642 END IF;
2643
2644 fnd_message.set_name ('HXT', 'HXT_39552_TIM_SUM_ID_NF');
2645 l_otm_error := fnd_message.get;
2646 otm_error := l_otm_error;
2647 oracle_error := SQLERRM;
2648 call_hxthxc_gen_error ('HXT', 'HXT_39552_TIM_SUM_ID_NF', NULL);
2649 --2278400
2650 RETURN;
2651 WHEN l_time_summary_id_sys_err
2652 THEN
2653 IF g_debug
2654 THEN
2655 hr_utility.set_location (l_proc, 1570);
2656 END IF;
2657
2658 fnd_message.set_name ('HXT', 'HXT_39553_TIM_SUM_ID_SYS_ERR');
2659 l_otm_error := fnd_message.get;
2660 otm_error := l_otm_error;
2661 oracle_error := SQLERRM;
2662 call_hxthxc_gen_error ('HXT', 'HXT_39553_TIM_SUM_ID_SYS_ERR', NULL);
2663 --2278400
2664 RETURN;
2665 WHEN l_earn_pol_data_err
2666 THEN
2667 IF g_debug
2668 THEN
2669 hr_utility.set_location (l_proc, 1580);
2670 END IF;
2671
2672 fnd_message.set_name ('HXT', 'HXT_39554_ERN_POL_NF');
2673 l_otm_error := fnd_message.get;
2674 otm_error := l_otm_error;
2675 oracle_error := SQLERRM;
2676 call_hxthxc_gen_error ('HXT', 'HXT_39554_ERN_POL_NF', NULL);
2677 --2278400
2678 RETURN;
2679 WHEN l_earn_pol_sys_err
2680 THEN
2681 IF g_debug
2682 THEN
2683 hr_utility.set_location (l_proc, 1590);
2684 END IF;
2685
2686 fnd_message.set_name ('HXT', 'HXT_39555_ERN_POL_SYS_ERR');
2687 l_otm_error := fnd_message.get;
2688 otm_error := l_otm_error;
2689 oracle_error := SQLERRM;
2690 call_hxthxc_gen_error ('HXT', 'HXT_39555_ERN_POL_SYS_ERR', NULL);
2691 --2278400
2692 RETURN;
2693 WHEN l_sess_date_err
2694 THEN
2695 IF g_debug
2696 THEN
2697 hr_utility.set_location (l_proc, 1600);
2698 END IF;
2699
2700 fnd_message.set_name ('HXT', 'HXT_39556_SESSION_DT_NF');
2701 l_otm_error := fnd_message.get;
2702 otm_error := l_otm_error;
2703 oracle_error := SQLERRM;
2704 call_hxthxc_gen_error ('HXT', 'HXT_39556_SESSION_DT_NF', NULL);
2705 --2278400
2706 RETURN;
2707 WHEN l_date_worked_time_err
2708 THEN
2709 IF g_debug
2710 THEN
2711 hr_utility.set_location (l_proc, 1610);
2712 END IF;
2713
2714 fnd_message.set_name ('HXT', 'HXT_39557_NO_TIME_IN_DT_WRK');
2715 l_otm_error := fnd_message.get;
2716 otm_error := l_otm_error;
2717 oracle_error := SQLERRM;
2718 call_hxthxc_gen_error ('HXT', 'HXT_39557_NO_TIME_IN_DT_WRK', NULL);
2719 --2278400
2720 RETURN;
2721 WHEN l_delete_sys_error
2722 THEN
2723 IF g_debug
2724 THEN
2725 hr_utility.set_location (l_proc, 1620);
2726 END IF;
2727
2728 fnd_message.set_name ('HXT', 'HXT_39558_ERR_IN_DSR');
2729 l_otm_error := fnd_message.get;
2730 otm_error := l_otm_error;
2731 oracle_error := SQLERRM;
2732 -- Removed for bug 3868006
2733 -- call_hxthxc_gen_error ('HXT', 'HXT_39558_ERR_IN_DSR', NULL); --2278400
2734 RETURN;
2735 WHEN l_delete_finished
2736 THEN
2737 IF g_debug
2738 THEN
2739 hr_utility.set_location (l_proc, 1630);
2740 END IF;
2741
2742 NULL;
2743 --SIR290
2744 WHEN l_dt_update_mode_err
2745 THEN
2746 IF g_debug
2747 THEN
2748 hr_utility.set_location (l_proc, 1640);
2749 END IF;
2750
2751 fnd_message.set_name ('HXT', 'HXT_39559_DT_UPD_MODE_INV');
2752 fnd_message.set_token ('DT_UPD_MODE', dt_update_mode);
2753 l_otm_error := fnd_message.get;
2754 otm_error := l_otm_error;
2755 oracle_error := '';
2756 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2757 --2278400
2758 RETURN;
2759 --SIR293
2760 WHEN l_dt_upt_mode_null_err
2761 THEN
2762 IF g_debug
2763 THEN
2764 hr_utility.set_location (l_proc, 1650);
2765 END IF;
2766
2767 fnd_message.set_name ('HXT', 'HXT_39560_DT_UPD_MODE_NULL');
2768 l_otm_error := fnd_message.get;
2769 otm_error := l_otm_error;
2770 oracle_error := '';
2771 call_hxthxc_gen_error ('HXT', 'HXT_39560_DT_UPD_MODE_NULL', NULL);
2772 --2278400
2773 RETURN;
2774 WHEN OTHERS
2775 THEN
2776 IF g_debug
2777 THEN
2778 hr_utility.set_location (l_proc, 1660);
2779 END IF;
2780
2781 -- GPM v115.23
2782 IF (l_otm_error IS NULL)
2783 THEN
2784 fnd_message.set_name ('HXT', 'HXT_39406_EXCP_REC_TIME');
2785 l_otm_error := fnd_message.get;
2786 END IF;
2787
2788 otm_error := l_otm_error;
2789
2790 IF (l_oracle_error IS NULL)
2791 THEN
2792 oracle_error := SQLERRM;
2793 ELSE
2794 oracle_error := l_oracle_error;
2795 END IF;
2796
2797 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', l_otm_error);
2798 --2278400
2799 RETURN;
2800 END record_time;
2801
2802 PROCEDURE log_timeclock_errors (
2803 otm_msg IN VARCHAR2,
2804 created_by IN NUMBER,
2805 ora_message IN VARCHAR2,
2806 timecard_source IN VARCHAR2,
2807 approver_number IN VARCHAR2 DEFAULT NULL,
2808 employee_number IN VARCHAR2,
2809 date_worked IN DATE DEFAULT NULL,
2810 start_time IN DATE DEFAULT NULL,
2811 end_time IN DATE DEFAULT NULL,
2812 hours IN NUMBER DEFAULT NULL,
2813 wage_code IN VARCHAR2 DEFAULT NULL,
2814 earning_policy IN VARCHAR2 DEFAULT NULL,
2815 hours_type IN VARCHAR2 DEFAULT NULL,
2816 earn_reason_code IN VARCHAR2 DEFAULT NULL,
2817 cost_center_id IN NUMBER DEFAULT NULL,
2818 project IN VARCHAR2 DEFAULT NULL,
2819 task_number IN VARCHAR2 DEFAULT NULL,
2820 location_code IN VARCHAR2 DEFAULT NULL,
2821 hrw_comment IN VARCHAR2 DEFAULT NULL,
2822 rate_multiple IN NUMBER DEFAULT NULL,
2823 hourly_rate IN NUMBER DEFAULT NULL,
2824 amount IN NUMBER DEFAULT NULL,
2825 separate_check_flag IN VARCHAR2 DEFAULT NULL,
2826 business_group_id IN NUMBER DEFAULT NULL,
2827 concat_cost_segments IN VARCHAR2 DEFAULT NULL,
2828 cost_segment1 IN VARCHAR2 DEFAULT NULL,
2829 cost_segment2 IN VARCHAR2 DEFAULT NULL,
2830 cost_segment3 IN VARCHAR2 DEFAULT NULL,
2831 cost_segment4 IN VARCHAR2 DEFAULT NULL,
2832 cost_segment5 IN VARCHAR2 DEFAULT NULL,
2833 cost_segment6 IN VARCHAR2 DEFAULT NULL,
2834 cost_segment7 IN VARCHAR2 DEFAULT NULL,
2835 cost_segment8 IN VARCHAR2 DEFAULT NULL,
2836 cost_segment9 IN VARCHAR2 DEFAULT NULL,
2837 cost_segment10 IN VARCHAR2 DEFAULT NULL,
2838 cost_segment11 IN VARCHAR2 DEFAULT NULL,
2839 cost_segment12 IN VARCHAR2 DEFAULT NULL,
2840 cost_segment13 IN VARCHAR2 DEFAULT NULL,
2841 cost_segment14 IN VARCHAR2 DEFAULT NULL,
2842 cost_segment15 IN VARCHAR2 DEFAULT NULL,
2843 cost_segment16 IN VARCHAR2 DEFAULT NULL,
2844 cost_segment17 IN VARCHAR2 DEFAULT NULL,
2845 cost_segment18 IN VARCHAR2 DEFAULT NULL,
2846 cost_segment19 IN VARCHAR2 DEFAULT NULL,
2847 cost_segment20 IN VARCHAR2 DEFAULT NULL,
2848 cost_segment21 IN VARCHAR2 DEFAULT NULL,
2849 cost_segment22 IN VARCHAR2 DEFAULT NULL,
2850 cost_segment23 IN VARCHAR2 DEFAULT NULL,
2851 cost_segment24 IN VARCHAR2 DEFAULT NULL,
2852 cost_segment25 IN VARCHAR2 DEFAULT NULL,
2853 cost_segment26 IN VARCHAR2 DEFAULT NULL,
2854 cost_segment27 IN VARCHAR2 DEFAULT NULL,
2855 cost_segment28 IN VARCHAR2 DEFAULT NULL,
2856 cost_segment29 IN VARCHAR2 DEFAULT NULL,
2857 cost_segment30 IN VARCHAR2 DEFAULT NULL,
2858 time_summary_id IN NUMBER DEFAULT NULL,
2859 tim_sum_eff_start_date IN DATE DEFAULT NULL,
2860 tim_sum_eff_end_date IN DATE DEFAULT NULL,
2861 oracle_error OUT NOCOPY VARCHAR2
2862 )
2863 IS
2864 l_error_seqno NUMBER DEFAULT NULL;
2865 BEGIN
2866 /* Initialize globals */
2867 g_sysdate := TRUNC (SYSDATE);
2868 g_sysdatetime := SYSDATE;
2869 g_user_id := fnd_global.user_id;
2870 g_login_id := fnd_global.login_id;
2871
2872 SELECT hxt_seqno.NEXTVAL
2873 INTO l_error_seqno
2874 FROM DUAL;
2875
2876 --
2877 INSERT INTO hxt_timeclock_errors
2878 (ID, otm_error, creation_date, created_by,
2879 ora_error, timecard_source, approver_number,
2880 employee_number, date_worked, start_time, end_time, hours,
2881 earning_policy, hours_type, earn_reason_code, project,
2882 task_number, location_code, hrw_comment, rate_multiple,
2883 hourly_rate, amount, separate_check_flag,
2884 business_group_id, concat_cost_segments, cost_segment1,
2885 cost_segment2, cost_segment3, cost_segment4,
2886 cost_segment5, cost_segment6, cost_segment7,
2887 cost_segment8, cost_segment9, cost_segment10,
2888 cost_segment11, cost_segment12, cost_segment13,
2889 cost_segment14, cost_segment15, cost_segment16,
2890 cost_segment17, cost_segment18, cost_segment19,
2891 cost_segment20, cost_segment21, cost_segment22,
2892 cost_segment23, cost_segment24, cost_segment25,
2893 cost_segment26, cost_segment27, cost_segment28,
2894 cost_segment29, cost_segment30, time_summary_id,
2895 tim_sum_eff_start_date, tim_sum_eff_end_date
2896 )
2897 VALUES (l_error_seqno, otm_msg, g_sysdatetime, created_by,
2898 ora_message, timecard_source, approver_number,
2899 employee_number, date_worked, start_time, end_time, hours,
2900 earning_policy, hours_type, earn_reason_code, project,
2901 task_number, location_code, hrw_comment, rate_multiple,
2902 hourly_rate, amount, separate_check_flag,
2903 business_group_id, concat_cost_segments, cost_segment1,
2904 cost_segment2, cost_segment3, cost_segment4,
2905 cost_segment5, cost_segment6, cost_segment7,
2906 cost_segment8, cost_segment9, cost_segment10,
2907 cost_segment11, cost_segment12, cost_segment13,
2908 cost_segment14, cost_segment15, cost_segment16,
2909 cost_segment17, cost_segment18, cost_segment19,
2910 cost_segment20, cost_segment21, cost_segment22,
2911 cost_segment23, cost_segment24, cost_segment25,
2912 cost_segment26, cost_segment27, cost_segment28,
2913 cost_segment29, cost_segment30, time_summary_id,
2914 tim_sum_eff_start_date, tim_sum_eff_end_date
2915 );
2916 END;
2917
2918 /**********************************************************
2919 re_explode_timecard()
2920 Fetch all hxt_sum_hours_worked records for the timecard
2921 indicated. Call record_time for each record to re-explode.
2922 ***********************************************************/
2923 PROCEDURE re_explode_timecard (
2924 timecard_id IN NUMBER,
2925 tim_eff_start_date IN DATE,
2926 tim_eff_end_date IN DATE,
2927 dt_update_mode IN VARCHAR2, --SIR290
2928 otm_error OUT NOCOPY VARCHAR2,
2929 oracle_error OUT NOCOPY VARCHAR2
2930 )
2931 -- p_mode IN VARCHAR2 default 'INSERT')
2932 IS
2933 CURSOR get_timecard_rec (
2934 c_tim_id NUMBER,
2935 c_tim_start_date DATE,
2936 c_tim_end_date DATE
2937 )
2938 IS
2939 SELECT tim.for_person_id, tbh.status, ptp.start_date --SIR286
2940 FROM hxt_timecards tim, --SIR290
2941 hxt_batch_states tbh,
2942 per_time_periods ptp --SIR286
2943 WHERE tim.ID = c_tim_id
2944 AND tbh.batch_id = tim.batch_id
2945 AND ptp.time_period_id = tim.time_period_id -- SIR286
2946 ; --SIR290
2947
2948 CURSOR get_summary_rows (
2949 c_tim_id NUMBER,
2950 c_tim_start_date DATE,
2951 c_tim_end_date DATE
2952 )
2953 IS
2954 SELECT ID, -- group_id,
2955 effective_start_date, effective_end_date, tim_id,
2956 date_worked, assignment_id, seqno, hours, time_in, time_out,
2957 element_type_id, fcl_earn_reason_code, ffv_cost_center_id,
2958 tas_id, location_id, sht_id, hrw_comment, ffv_rate_code_id,
2959 rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
2960 separate_check_flag, created_by, creation_date,
2961 last_updated_by, last_update_date, last_update_login,
2962 actual_time_in, actual_time_out, prev_wage_code, project_id,
2963 earn_pol_id, time_building_block_id,
2964 time_building_block_ovn, state_name, county_name, city_name,
2965 zip_code
2966 FROM hxt_sum_hours_worked
2967 WHERE tim_id = c_tim_id
2968 ORDER BY date_worked, element_type_id, time_in, seqno, ID;
2969
2970 l_retcode NUMBER;
2971 l_batch_status VARCHAR2 (30);
2972 l_timecard_rec get_timecard_rec%ROWTYPE;
2973 l_otm_error VARCHAR2 (120) DEFAULT NULL;
2974 l_oracle_error VARCHAR2 (512) DEFAULT NULL;
2975 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE DEFAULT NULL;
2976 l_tim_not_found_err EXCEPTION;
2977 l_rec_hours_data_err EXCEPTION;
2978 l_rec_hours_sys_err EXCEPTION;
2979 l_delete_details_err EXCEPTION;
2980 l_session_date DATE;
2981 l_sess_date_err EXCEPTION;
2982 BEGIN
2983 /* Initialize globals */
2984 g_debug := hr_utility.debug_enabled;
2985 g_sysdate := TRUNC (SYSDATE);
2986 g_sysdatetime := SYSDATE;
2987 g_user_id := fnd_global.user_id;
2988 g_login_id := fnd_global.login_id;
2989
2990 IF g_debug
2991 THEN
2992 hr_utility.TRACE ('start re explode for loop');
2993 END IF;
2994
2995 --
2996 -- Retrieve the timecard's header information.
2997 --
2998 OPEN get_timecard_rec (timecard_id, tim_eff_start_date,
2999 tim_eff_end_date);
3000
3001 FETCH get_timecard_rec
3002 INTO l_timecard_rec;
3003
3004 IF get_timecard_rec%NOTFOUND
3005 THEN
3006 CLOSE get_timecard_rec;
3007
3008 RAISE l_tim_not_found_err;
3009 ELSE
3010 CLOSE get_timecard_rec;
3011 END IF;
3012
3013 l_retcode := hxt_tim_col_util.get_session_date (l_session_date);
3014
3015 IF l_retcode = 1
3016 THEN
3017 RAISE l_sess_date_err;
3018 END IF;
3019
3020 hxt_time_collection.delete_details (timecard_id,
3021 dt_update_mode,
3022 l_session_date,
3023 l_otm_error
3024 );
3025
3026 IF l_otm_error IS NOT NULL
3027 THEN
3028 RAISE l_delete_details_err;
3029 END IF;
3030
3031 --
3032 -- Call record_hours_worked to re-explode each summary record.
3033 IF g_debug
3034 THEN
3035 hr_utility.TRACE ('Before for loop');
3036 END IF;
3037
3038 --
3039 FOR l_sum_hours_rec IN get_summary_rows (timecard_id,
3040 tim_eff_start_date,
3041 tim_eff_end_date
3042 )
3043 LOOP
3044 l_retcode :=
3045 record_hours_worked (NULL,
3046 FALSE,
3047 l_sum_hours_rec.tim_id,
3048 l_sum_hours_rec.assignment_id,
3049 l_timecard_rec.for_person_id,
3050 l_sum_hours_rec.date_worked,
3051 l_sum_hours_rec.element_type_id,
3052 l_sum_hours_rec.hours,
3053 l_sum_hours_rec.time_in,
3054 l_sum_hours_rec.time_out,
3055 l_timecard_rec.start_date,
3056 l_sum_hours_rec.prev_wage_code,
3057 l_sum_hours_rec.tas_id,
3058 l_sum_hours_rec.location_id,
3059 l_sum_hours_rec.project_id,
3060 l_sum_hours_rec.earn_pol_id,
3061 l_sum_hours_rec.fcl_earn_reason_code,
3062 l_sum_hours_rec.ffv_cost_center_id,
3063 l_sum_hours_rec.hrw_comment,
3064 l_sum_hours_rec.rate_multiple,
3065 l_sum_hours_rec.hourly_rate,
3066 l_sum_hours_rec.amount,
3067 l_sum_hours_rec.separate_check_flag,
3068 l_sum_hours_rec.ID,
3069 l_sum_hours_rec.effective_start_date,
3070 l_sum_hours_rec.effective_end_date,
3071 l_sum_hours_rec.created_by,
3072 l_sum_hours_rec.last_updated_by,
3073 l_sum_hours_rec.last_update_login,
3074 'N', --writesum_yn
3075 'Y', --explode_yn
3076 l_timecard_rec.status,
3077 dt_update_mode, --SIR290
3078 l_sum_hours_rec.time_building_block_id,
3079 l_sum_hours_rec.time_building_block_ovn,
3080 l_otm_error,
3081 l_oracle_error,
3082 l_created_tim_sum_id,
3083 l_sum_hours_rec.state_name,
3084 l_sum_hours_rec.county_name,
3085 l_sum_hours_rec.city_name,
3086 l_sum_hours_rec.zip_code
3087 );
3088
3089 IF g_debug
3090 THEN
3091 hr_utility.TRACE ('l_retcode is :' || TO_CHAR (l_retcode));
3092 END IF;
3093
3094 IF l_retcode = 1
3095 THEN
3096 RAISE l_rec_hours_data_err;
3097 ELSIF l_retcode = 2
3098 THEN
3099 RAISE l_rec_hours_sys_err;
3100 END IF;
3101 END LOOP;
3102 EXCEPTION
3103 WHEN l_tim_not_found_err
3104 THEN
3105 fnd_message.set_name ('HXT', 'HXT_39561_CANNOT_FIND_TCARD');
3106 fnd_message.set_token ('TIM_ID', TO_CHAR (timecard_id));
3107 otm_error := fnd_message.get;
3108 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', otm_error);
3109 --2278400
3110 WHEN l_rec_hours_data_err
3111 THEN
3112 fnd_message.set_name ('HXT', 'HXT_39562_ERR_IN_RET');
3113 fnd_message.set_token ('ERR_TEXT', l_otm_error);
3114 otm_error := fnd_message.get;
3115 --call_hxthxc_gen_error('HXC','HXC_HXT_DEP_VAL_OTMERR',otm_error); --2278400
3116 WHEN l_rec_hours_sys_err
3117 THEN
3118 fnd_message.set_name ('HXT', 'HXT_39562_ERR_IN_RET');
3119 fnd_message.set_token ('ERR_TEXT', l_oracle_error);
3120 oracle_error := fnd_message.get;
3121 --call_hxthxc_gen_error('HXC','HXC_HXT_DEP_VAL_OTMERR',oracle_error); --2278400
3122 --begin SIR334
3123 WHEN l_delete_details_err
3124 THEN
3125 fnd_message.set_name ('HXT', 'HXT_39563_ERR_IN_RET_DD');
3126 fnd_message.set_token ('ERR_TEXT', l_otm_error);
3127 otm_error := fnd_message.get;
3128 --call_hxthxc_gen_error('HXC','HXC_HXT_DEP_VAL_OTMERR',otm_error); --2278400
3129 WHEN l_sess_date_err
3130 THEN
3131 fnd_message.set_name ('HXT', 'HXT_39556_SESSION_DT_NF');
3132 otm_error := fnd_message.get;
3133 oracle_error := SQLERRM;
3134 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', otm_error);
3135 --2278400
3136 --end SIR334
3137 WHEN OTHERS
3138 THEN
3139 fnd_message.set_name ('HXT', 'HXT_39564_EXCP_IN_RET');
3140 otm_error := fnd_message.get;
3141 oracle_error := SQLERRM;
3142 call_hxthxc_gen_error ('HXT', 'HXT_39564_EXCP_IN_RET', NULL);
3143 --2278400
3144 END re_explode_timecard;
3145
3146 /*-------------------------------------------------------------------------
3147 ||
3148 || Private Module Definitions
3149 ||
3150 -------------------------------------------------------------------------*/
3151
3152 /***********************************
3153 get_time_period()
3154 Obtain the time period identifier
3155 for this particular pay date
3156 ************************************/
3157 FUNCTION get_time_period (
3158 i_payroll_id IN NUMBER,
3159 i_date_worked IN DATE,
3160 o_time_period OUT NOCOPY NUMBER,
3161 o_start_date OUT NOCOPY DATE,
3162 o_end_date OUT NOCOPY DATE
3163 )
3164 RETURN NUMBER
3165 IS
3166 BEGIN
3167 SELECT time_period_id, start_date, end_date
3168 INTO o_time_period, o_start_date, o_end_date
3169 FROM per_time_periods
3170 WHERE payroll_id = i_payroll_id
3171 AND TRUNC (i_date_worked) BETWEEN TRUNC (start_date) AND TRUNC
3172 (end_date);
3173
3174 RETURN 0;
3175 EXCEPTION
3176 WHEN NO_DATA_FOUND
3177 THEN
3178 RETURN 1;
3179 WHEN OTHERS
3180 THEN
3181 RETURN 2;
3182 END get_time_period;
3183
3184 /***************************************************
3185 check_for_timecard()
3186 Check the HXT_TIMECARDS table to see if a timecard
3187 already exists for the person punching the clock
3188 ****************************************************/
3189 FUNCTION check_for_timecard (
3190 i_person_id IN NUMBER,
3191 i_time_period_id IN NUMBER,
3192 o_timecard_id OUT NOCOPY NUMBER,
3193 o_auto_gen_flag OUT NOCOPY VARCHAR2
3194 )
3195 RETURN NUMBER
3196 IS
3197 BEGIN
3198 IF g_debug
3199 THEN
3200 hr_utility.set_location ('hxt_time_collection.check_for_timecard',
3201 10
3202 );
3203 END IF;
3204
3205 SELECT ID, auto_gen_flag, batch_id
3206 INTO o_timecard_id, o_auto_gen_flag, g_batch_err_id
3207 FROM hxt_timecards_f
3208 WHERE for_person_id = i_person_id AND time_period_id = i_time_period_id;
3209
3210 IF g_debug
3211 THEN
3212 hr_utility.TRACE ('Timecard id is:' || o_timecard_id);
3213 hr_utility.TRACE ('auto_gen_flag :' || o_auto_gen_flag);
3214 hr_utility.TRACE ('batch_id :' || g_batch_err_id);
3215 hr_utility.set_location ('hxt_time_collection.check_for_timecard',
3216 20
3217 );
3218 END IF;
3219
3220 RETURN 0;
3221 EXCEPTION
3222 WHEN NO_DATA_FOUND
3223 THEN
3224 IF g_debug
3225 THEN
3226 hr_utility.set_location
3227 ('hxt_time_collection.check_for_timecard',
3228 30
3229 );
3230 END IF;
3231
3232 RETURN 1;
3233 WHEN OTHERS
3234 THEN
3235 IF g_debug
3236 THEN
3237 hr_utility.set_location
3238 ('hxt_time_collection.check_for_timecard',
3239 40
3240 );
3241 END IF;
3242
3243 RETURN 2;
3244 END check_for_timecard;
3245
3246 /****************************************************
3247 check_for_batch_status()
3248 ****************************************************/
3249 FUNCTION check_for_batch_status (
3250 i_batch_id IN NUMBER,
3251 o_batch_status OUT NOCOPY VARCHAR2
3252 )
3253 RETURN NUMBER
3254 IS
3255 BEGIN
3256 SELECT status
3257 INTO o_batch_status
3258 FROM hxt_batch_states
3259 WHERE batch_id = i_batch_id;
3260
3261 RETURN 0;
3262 EXCEPTION
3263 WHEN NO_DATA_FOUND
3264 THEN
3265 RETURN 1;
3266 WHEN OTHERS
3267 THEN
3268 RETURN 2;
3269 END check_for_batch_status;
3270
3271 /********************************************************
3272 create_timecard()
3273 Creates a timecard for the person punching the clock
3274 for this particular time period based on the payroll
3275 for this person.
3276 *********************************************************/
3277 FUNCTION create_timecard (
3278 i_person_id IN NUMBER,
3279 i_business_group_id IN NUMBER,
3280 i_assignment_id IN NUMBER,
3281 i_payroll_id IN NUMBER,
3282 i_time_period_id IN NUMBER,
3283 i_approver_id IN NUMBER,
3284 i_timecard_source_code IN VARCHAR2,
3285 o_timecard_id OUT NOCOPY NUMBER
3286 )
3287 RETURN NUMBER
3288 IS
3289 l_proc VARCHAR2 (72);
3290 l_retcode NUMBER DEFAULT 0;
3291 l_batch_creation_error EXCEPTION;
3292 l_batch_location_error EXCEPTION;
3293 l_tim_id_creation_error EXCEPTION;
3294 l_batch_id pay_batch_headers.batch_id%TYPE DEFAULT NULL;
3295 l_timecard_id hxt_timecards.ID%TYPE DEFAULT NULL;
3296 l_object_version_number NUMBER DEFAULT NULL;
3297 l_rowid ROWID;
3298 BEGIN
3299 /* Obtain a batch id for the new timecard */
3300 IF g_debug
3301 THEN
3302 l_proc := g_package || 'create_timecard';
3303 hr_utility.set_location ('Entering ' || l_proc, 10);
3304 END IF;
3305
3306 l_batch_id :=
3307 find_existing_batch (p_time_period_id => i_time_period_id,
3308 --SIR413
3309 p_batch_reference => g_batch_ref
3310 );
3311
3312 /* If Not Found */
3313 IF (l_batch_id IS NULL)
3314 THEN
3315 IF g_debug
3316 THEN
3317 hr_utility.TRACE ('No existing_batch ');
3318 END IF;
3319
3320 /* Create a batch id for the new timecard */
3321 /* A Autogen; C Autogen (changed); M Manual; U Manual (changed);
3322 T Time Clock; S Time Store */
3323 l_retcode :=
3324 create_batch (i_timecard_source_code, --'C' source is timeclock
3325 i_payroll_id,
3326 i_time_period_id,
3327 i_assignment_id,
3328 i_person_id,
3329 i_business_group_id,
3330 l_batch_id
3331 );
3332
3333 IF g_debug
3334 THEN
3335 hr_utility.TRACE ( 'AFTER create batch. Create batch id is '
3336 || TO_CHAR (l_batch_id)
3337 );
3338 hr_utility.TRACE ( 'AFTER create batch. RETCODE is '
3339 || TO_CHAR (l_retcode)
3340 );
3341 END IF;
3342
3343 IF l_retcode <> 0
3344 THEN
3345 RAISE l_batch_creation_error;
3346 END IF;
3347
3348 -- Initialize counter + batch_id
3349 g_batch_info (NVL (g_batch_info.LAST, 0) + 1).batch_id := l_batch_id;
3350 g_batch_info (g_batch_info.LAST).period_id := i_time_period_id;
3351 g_batch_info (g_batch_info.LAST).batch_ref := g_batch_ref;
3352 g_batch_info (g_batch_info.LAST).num_tcs := 1;
3353 ELSIF l_retcode = 2
3354 THEN
3355 RAISE l_batch_location_error;
3356 END IF;
3357
3358 g_batch_err_id := l_batch_id;
3359 --
3360 /* Generate a unique timecard id for the new timecard */
3361 l_timecard_id := hxt_time_gen.get_hxt_seqno;
3362
3363 IF l_timecard_id = NULL
3364 THEN
3365 RAISE l_tim_id_creation_error;
3366 END IF;
3367
3368 --
3369 /* Insert new timecard info to hxt_timecards */
3370 /* INSERT into hxt_timecards_f
3371 ( id,
3372 for_person_id,
3373 payroll_id,
3374 time_period_id,
3375 batch_id,
3376 approv_person_id,
3377 auto_gen_flag,
3378 created_by,
3379 creation_date,
3380 last_updated_by,
3381 last_update_date,
3382 last_update_login,
3383 effective_start_date,
3384 effective_end_date)
3385 VALUES
3386 ( l_timecard_id,
3387 i_person_id,
3388 i_payroll_id,
3389 i_time_period_id,
3390 l_batch_id,
3391 i_approver_id,
3392 i_timecard_source_code,
3393 g_user_id,
3394 g_sysdatetime,
3395 g_user_id,
3396 g_sysdatetime,
3397 g_login_id,
3398 trunc(g_sess_date),
3399 hr_general.end_of_time);
3400 */
3401
3402 /* Call dml to insert new timecard. */
3403 IF g_debug
3404 THEN
3405 hr_utility.TRACE ('BEFORE DML create timecard');
3406 END IF;
3407
3408 hxt_dml.insert_hxt_timecards
3409 (p_rowid => l_rowid,
3410 p_id => l_timecard_id,
3411 p_for_person_id => i_person_id,
3412 p_time_period_id => i_time_period_id,
3413 p_auto_gen_flag => i_timecard_source_code,
3414 p_batch_id => l_batch_id,
3415 p_approv_person_id => i_approver_id,
3416 p_approved_timestamp => NULL,
3417 p_created_by => g_user_id,
3418 p_creation_date => g_sysdatetime,
3419 p_last_updated_by => g_user_id,
3420 p_last_update_date => g_sysdatetime,
3421 p_last_update_login => g_login_id,
3422 p_payroll_id => i_payroll_id,
3423 p_status => NULL,
3424 p_effective_start_date => TRUNC (g_sess_date),
3425 p_effective_end_date => hr_general.end_of_time,
3426 p_object_version_number => l_object_version_number
3427 );
3428 --
3429 o_timecard_id := l_timecard_id;
3430
3431 IF g_debug
3432 THEN
3433 hr_utility.TRACE ( 'AFTER DML create timecard. timecard id is '
3434 || TO_CHAR (l_timecard_id)
3435 );
3436 END IF;
3437
3438 RETURN 0;
3439 EXCEPTION
3440 WHEN l_batch_creation_error
3441 THEN
3442 RETURN l_retcode;
3443 WHEN l_batch_location_error
3444 THEN
3445 RETURN l_retcode;
3446 WHEN l_tim_id_creation_error
3447 THEN
3448 RETURN 2;
3449 WHEN OTHERS
3450 THEN
3451 RETURN 2;
3452 END create_timecard;
3453
3454 /******************************************************************
3455 create_batch()
3456 Obtains an existing clock batch id for this particular timecard.
3457 If no clock batch id with less than 50 timecards exists.
3458 Creates a new batch id for this particular timecard.
3459 ******************************************************************/
3460 FUNCTION create_batch (
3461 i_source IN VARCHAR2,
3462 i_payroll_id IN NUMBER,
3463 i_time_period_id IN NUMBER,
3464 i_assignment_id IN NUMBER,
3465 i_person_id IN NUMBER,
3466 i_business_group_id IN NUMBER,
3467 o_batch_id OUT NOCOPY NUMBER
3468 )
3469 RETURN NUMBER
3470 IS
3471 l_batch_id pay_batch_headers.batch_id%TYPE DEFAULT NULL;
3472 l_batch_name pay_batch_headers.batch_name%TYPE
3473 DEFAULT NULL;
3474 l_reference_num pay_batch_headers.batch_reference%TYPE
3475 DEFAULT NULL;
3476 l_error_text VARCHAR2 (128) DEFAULT NULL;
3477 l_batch_id_error EXCEPTION;
3478 l_batch_name_error EXCEPTION;
3479 l_reference_num_error EXCEPTION;
3480 l_retcode NUMBER DEFAULT 0;
3481 l_object_version_number NUMBER;
3482 BEGIN
3483 IF g_debug
3484 THEN
3485 hr_utility.TRACE ('IN cREATE BATCH ');
3486 END IF;
3487
3488 IF (i_source = 'S') OR (g_batch_ref IS NOT NULL)
3489 THEN
3490 l_reference_num := g_batch_ref;
3491 ELSE
3492 hxt_user_exits.define_reference_number (i_payroll_id,
3493 i_time_period_id,
3494 i_assignment_id,
3495 i_person_id,
3496 g_user_name,
3497 i_source,
3498 l_reference_num,
3499 l_error_text
3500 );
3501 END IF;
3502
3503 IF g_debug
3504 THEN
3505 hr_utility.TRACE ('AFTER REF NUM ');
3506 END IF;
3507
3508 IF l_error_text <> NULL
3509 THEN
3510 IF g_debug
3511 THEN
3512 hr_utility.TRACE ('ERROR IS ' || l_error_text);
3513 END IF;
3514
3515 RAISE l_reference_num_error;
3516 END IF;
3517
3518 --
3519 IF g_debug
3520 THEN
3521 hr_utility.TRACE ('GET batch id');
3522 END IF;
3523
3524 /* Get next batch number */
3525 /* l_batch_id := hxt_time_gen.get_next_batch_id;
3526
3527 IF l_batch_id = NULL
3528 THEN
3529 hr_utility.TRACE ('batch id is null');
3530 RAISE l_batch_id_error;
3531 END IF;
3532
3533 --
3534 hr_utility.TRACE ( 'batch id is -----'
3535 || TO_CHAR (l_batch_id));
3536 IF i_source = 'S'
3537 THEN
3538 l_batch_name := g_batch_name
3539 || TO_CHAR (l_batch_id);
3540 ELSE
3541 hxt_user_exits.define_batch_name (
3542 l_batch_id,
3543 l_batch_name,
3544 l_error_text
3545 );
3546 END IF;
3547 */
3548 IF g_debug
3549 THEN
3550 hr_utility.TRACE ('batch name is -----' || l_batch_name);
3551 END IF;
3552
3553 IF l_error_text <> NULL
3554 THEN
3555 IF g_debug
3556 THEN
3557 hr_utility.TRACE ('batch name error ');
3558 END IF;
3559
3560 RAISE l_batch_name_error;
3561 END IF;
3562
3563 IF g_debug
3564 THEN
3565 hr_utility.TRACE ('BEFORE INSERT batch ');
3566 END IF;
3567
3568 /* INSERT INTO pay_batch_headers
3569 (batch_id, business_group_id, batch_name, batch_status,
3570 action_if_exists, batch_reference, batch_source,
3571 purge_after_transfer, reject_if_future_changes,
3572 last_update_date, last_updated_by, last_update_login,
3573 created_by, creation_date)
3574 VALUES (l_batch_id, i_business_group_id, l_batch_name, 'U',
3575 'I', l_reference_num, 'OTM',
3576 'N', 'N',
3577 g_sysdatetime, g_user_id, g_login_id,
3578 g_user_id, g_sysdatetime);
3579
3580 hr_utility.TRACE ('AFTER insert batch ');
3581 */-- create a batch first
3582 pay_batch_element_entry_api.create_batch_header
3583 (p_session_date => g_sysdatetime,
3584 p_batch_name => TO_CHAR
3585 (SYSDATE,
3586 'DD-MM-RRRR HH24:MI:SS'
3587 ),
3588 p_batch_status => 'U',
3589 p_business_group_id => i_business_group_id,
3590 p_action_if_exists => 'I',
3591 p_batch_reference => l_reference_num,
3592 p_batch_source => 'OTM',
3593 p_purge_after_transfer => 'N',
3594 p_reject_if_future_changes => 'N',
3595 p_batch_id => l_batch_id,
3596 p_object_version_number => l_object_version_number
3597 );
3598
3599 -- from the batch id, get the batch name
3600 IF i_source = 'S'
3601 THEN
3602 l_batch_name := g_batch_name || TO_CHAR (l_batch_id);
3603 ELSE
3604 hxt_user_exits.define_batch_name (l_batch_id,
3605 l_batch_name,
3606 l_error_text
3607 );
3608 END IF;
3609
3610 IF l_error_text <> NULL
3611 THEN
3612 RAISE l_batch_name_error;
3613 END IF;
3614
3615 --update the batch name
3616 pay_batch_element_entry_api.update_batch_header
3617 (p_session_date => g_sysdatetime,
3618 p_batch_id => l_batch_id,
3619 p_object_version_number => l_object_version_number,
3620 p_batch_name => l_batch_name
3621 );
3622 o_batch_id := l_batch_id;
3623 RETURN 0;
3624 EXCEPTION
3625 WHEN l_batch_id_error
3626 THEN
3627 fnd_message.set_name ('HXT', 'HXT_39409_CREATE_BATCH');
3628 call_hxthxc_gen_error ('HXT', 'HXT_39409_CREATE_BATCH', NULL);
3629 --2278400
3630 RETURN 2;
3631 WHEN l_reference_num_error
3632 THEN
3633 fnd_message.set_name ('HXT', 'HXT_39410_CREATE_REF_FUNC');
3634 call_hxthxc_gen_error ('HXT', 'HXT_39410_CREATE_REF_FUNC', NULL);
3635 --2278400
3636 RETURN l_retcode;
3637 WHEN l_batch_name_error
3638 THEN
3639 fnd_message.set_name ('HXT', 'HXT_39484_CREATE_BATCH_NAME');
3640 call_hxthxc_gen_error ('HXT', 'HXT_39484_CREATE_BATCH_NAME', NULL);
3641 --2278400
3642 RETURN l_retcode;
3643 WHEN OTHERS
3644 THEN
3645 fnd_message.set_name ('HXT', 'HXT_39411_CREATE_BATCH_FUNC');
3646 call_hxthxc_gen_error ('HXT', 'HXT_39411_CREATE_BATCH_FUNC', NULL);
3647 --2278400
3648 RETURN 2;
3649 END create_batch;
3650
3651 /********************************************************************
3652 find_existing_batch()
3653 Examine the pay_batch_headers and the hxt_timeclocks
3654 tables for existing unprocessed timeclock batches. The
3655 batches must be in a hold status (batch_status = 'H')
3656 and have less than the max amount of timecards allowed per batch.
3657 ********************************************************************/
3658 FUNCTION find_existing_batch (
3659 p_time_period_id IN per_time_periods.time_period_id%TYPE,
3660 p_batch_reference IN pay_batch_headers.batch_reference%TYPE
3661 )
3662 RETURN pay_batch_headers.batch_id%TYPE
3663 IS
3664 CURSOR csr_timecard_batches (
3665 p_time_period_id per_time_periods.time_period_id%TYPE,
3666 p_batch_reference pay_batch_headers.batch_reference%TYPE
3667 )
3668 IS
3669 SELECT COUNT (ht.ID) num_tcs, MAX (ht.batch_id) batch_id
3670 FROM hxt_timecards ht,
3671 hxt_batch_states hbs,
3672 pay_batch_headers pbh
3673 WHERE ht.time_period_id = p_time_period_id
3674 AND hbs.batch_id = ht.batch_id
3675 AND pbh.batch_id = ht.batch_id
3676 AND hbs.status <> 'VT'
3677 AND pbh.batch_reference LIKE NVL (p_batch_reference, '%') || '%'
3678 HAVING COUNT (ht.ID) < g_max_tc_allowed
3679 GROUP BY ht.batch_id;
3680
3681 l_proc VARCHAR2 (72);
3682 l_timecard_batches csr_timecard_batches%ROWTYPE;
3683 l_batch_id pay_batch_headers.batch_id%TYPE;
3684 l_batch_tbl_idx PLS_INTEGER := g_batch_info.FIRST;
3685 BEGIN
3686 IF g_debug
3687 THEN
3688 l_proc := g_package || 'find_existing_batch';
3689 hr_utility.set_location ('Entering:' || l_proc, 10);
3690 END IF;
3691
3692 IF (CACHE)
3693 THEN
3694
3695 <<check_cached_batches>>
3696 LOOP
3697 EXIT check_cached_batches WHEN (NOT (g_batch_info.EXISTS
3698 (l_batch_tbl_idx)
3699 )
3700 );
3701
3702 IF ( (g_batch_info (l_batch_tbl_idx).batch_ref LIKE
3703 NVL (p_batch_reference, '%')
3704 )
3705 AND (g_batch_info (l_batch_tbl_idx).period_id =
3706 p_time_period_id
3707 )
3708 AND (g_batch_info (l_batch_tbl_idx).num_tcs < g_max_tc_allowed
3709 )
3710 )
3711 THEN
3712 l_batch_id := g_batch_info (l_batch_tbl_idx).batch_id;
3713 g_batch_info (l_batch_tbl_idx).num_tcs :=
3714 g_batch_info (l_batch_tbl_idx).num_tcs + 1;
3715 l_batch_tbl_idx := g_batch_info.LAST;
3716
3717 -- to trigger exit of loop
3718 IF g_debug
3719 THEN
3720 hr_utility.set_location ( ' Found batch_id in cache:'
3721 || l_batch_id,
3722 20
3723 );
3724 END IF;
3725 END IF;
3726
3727 l_batch_tbl_idx := g_batch_info.NEXT (l_batch_tbl_idx);
3728 END LOOP check_cached_batches;
3729 END IF;
3730
3731 IF g_debug
3732 THEN
3733 hr_utility.set_location ('Leaving:' || l_proc, 100);
3734 END IF;
3735
3736 RETURN l_batch_id;
3737 EXCEPTION
3738 WHEN OTHERS
3739 THEN
3740 fnd_message.set_name ('HXT', 'HXT_39412_FIND_BATCH_FUNC');
3741 call_hxthxc_gen_error ('HXT', 'HXT_39412_FIND_BATCH_FUNC', NULL);
3742 RETURN NULL;
3743 END find_existing_batch;
3744
3745 /**********************************************************
3746 create_holiday_hours()
3747 Creates hours on new timecards for all holidays falling
3748 between the start and end dates of the pay period.
3749 **********************************************************/
3750 FUNCTION create_holiday_hours (
3751 i_person_id IN NUMBER,
3752 i_hcl_id IN NUMBER,
3753 i_hxt_rotation_plan IN NUMBER, --SIR344
3754 i_start_date IN DATE,
3755 i_end_date IN DATE,
3756 i_timecard_id IN NUMBER,
3757 i_wage_code IN VARCHAR2,
3758 i_task_id IN NUMBER,
3759 i_location_id IN NUMBER,
3760 i_project_id IN hxt_sum_hours_worked.project_id%TYPE,
3761 i_earn_pol_id IN hxt_sum_hours_worked.earn_pol_id%TYPE,
3762 i_earn_reason_code IN VARCHAR2,
3763 i_comment IN VARCHAR2,
3764 i_rate_multiple IN NUMBER,
3765 i_hourly_rate IN NUMBER,
3766 i_amount IN NUMBER,
3767 i_separate_check_flag IN VARCHAR2,
3768 i_assignment_id IN NUMBER,
3769 i_time_summary_id IN NUMBER,
3770 i_tim_sum_eff_start_date IN DATE,
3771 i_tim_sum_eff_end_date IN DATE,
3772 i_created_by IN NUMBER,
3773 i_last_updated_by IN NUMBER,
3774 i_last_update_login IN NUMBER,
3775 i_writesum_yn IN VARCHAR2,
3776 i_explode_yn IN VARCHAR2,
3777 i_batch_status IN VARCHAR2,
3778 i_dt_update_mode IN VARCHAR2, --SIR290
3779 p_time_building_block_id IN NUMBER DEFAULT NULL,
3780 p_time_building_block_ovn IN NUMBER DEFAULT NULL,
3781 o_otm_error OUT NOCOPY VARCHAR2,
3782 o_oracle_error OUT NOCOPY VARCHAR2,
3783 o_created_tim_sum_id OUT NOCOPY NUMBER,
3784 i_start_time IN DATE,
3785 i_end_time IN DATE,
3786 i_state_name IN VARCHAR2 DEFAULT NULL,
3787 i_county_name IN VARCHAR2 DEFAULT NULL,
3788 i_city_name IN VARCHAR2 DEFAULT NULL,
3789 i_zip_code IN VARCHAR2 DEFAULT NULL
3790 )
3791 -- p_mode IN VARCHAR2 default 'INSERT')
3792 RETURN NUMBER
3793 IS
3794 l_hol_rec g_hol_cur%ROWTYPE;
3795 l_retcode NUMBER DEFAULT 0;
3796 l_otm_error VARCHAR2 (120) DEFAULT NULL;
3797 l_oracle_error VARCHAR2 (512) DEFAULT NULL;
3798 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE DEFAULT NULL;
3799 l_hours_worked_error EXCEPTION;
3800 --BEGIN SIR344
3801 l_time_in DATE := NULL;
3802 l_time_out DATE := NULL;
3803 l_hours NUMBER;
3804 l_work_id NUMBER;
3805 l_osp_id NUMBER;
3806 l_sdf_id NUMBER;
3807 l_standard_start NUMBER;
3808 l_standard_stop NUMBER;
3809 l_early_start NUMBER;
3810 l_late_stop NUMBER;
3811 l_proc VARCHAR2 (100);
3812 BEGIN
3813 IF g_debug
3814 THEN
3815 l_proc := 'hxt_time_collection.CREATE_HOLIDAY_HOURS';
3816 hr_utility.set_location (l_proc, 10);
3817 hr_utility.TRACE ('i_start_date :' || i_start_date);
3818 hr_utility.TRACE ('i_end_date :' || i_end_date);
3819 hr_utility.TRACE ('i_hcl_id :' || i_hcl_id);
3820 hr_utility.TRACE ( 'i_start_time is '
3821 || TO_CHAR (i_start_time, 'DD-MON-YYYY HH:MI:SS')
3822 );
3823 hr_utility.TRACE ( 'i_end_time is '
3824 || TO_CHAR (i_end_time, 'DD-MON-YYYY HH:MI:SS')
3825 );
3826 END IF;
3827
3828 FOR l_hol_rec IN g_hol_cur (i_start_date, i_end_date, i_hcl_id)
3829 LOOP
3830 hr_utility.set_location (l_proc, 20);
3831
3832 IF ( fnd_profile.VALUE ('HXT_HOL_HOURS_FROM_HOL_CAL') = 'Y'
3833 OR fnd_profile.VALUE ('HXT_HOL_HOURS_FROM_HOL_CAL') IS NULL
3834 )
3835 THEN
3836 IF g_debug
3837 THEN
3838 hr_utility.set_location (l_proc, 30);
3839 END IF;
3840
3841 l_hours := l_hol_rec.hours;
3842 l_time_out := NULL;
3843 l_time_in := NULL;
3844
3845 IF g_debug
3846 THEN
3847 hr_utility.TRACE ('l_hours :' || l_hours);
3848 hr_utility.TRACE ('l_time_in :' || l_time_in);
3849 hr_utility.TRACE ('l_time_out :' || l_time_out);
3850 END IF;
3851 ELSE
3852 IF g_debug
3853 THEN
3854 hr_utility.set_location (l_proc, 40);
3855 END IF;
3856
3857 IF i_hxt_rotation_plan IS NOT NULL
3858 THEN
3859 IF g_debug
3860 THEN
3861 hr_utility.set_location (l_proc, 50);
3862 END IF;
3863
3864 hxt_util.get_shift_info (l_hol_rec.holiday_date,
3865 l_work_id,
3866 i_hxt_rotation_plan,
3867 l_osp_id,
3868 l_sdf_id,
3869 l_standard_start,
3870 l_standard_stop,
3871 l_early_start,
3872 l_late_stop,
3873 l_hours,
3874 l_retcode
3875 );
3876
3877 IF g_debug
3878 THEN
3879 hr_utility.TRACE ('l_retcode :' || l_retcode);
3880 END IF;
3881
3882 IF l_retcode <> 0
3883 THEN
3884 IF g_debug
3885 THEN
3886 hr_utility.set_location (l_proc, 60);
3887 END IF;
3888
3889 RAISE l_hours_worked_error;
3890 END IF;
3891
3892 IF l_hours IS NOT NULL
3893 THEN
3894 IF g_debug
3895 THEN
3896 hr_utility.set_location (l_proc, 70);
3897 END IF;
3898
3899 l_time_out := NULL;
3900 l_time_in := NULL;
3901
3902 IF g_debug
3903 THEN
3904 hr_utility.TRACE ('l_time_in :' || l_time_in);
3905 hr_utility.TRACE ('l_time_out :' || l_time_out);
3906 END IF;
3907 ELSE
3908 IF g_debug
3909 THEN
3910 hr_utility.set_location (l_proc, 80);
3911 END IF;
3912
3913 l_time_in :=
3914 TO_DATE ( TO_CHAR (l_hol_rec.holiday_date, 'DDMMYYYY ')
3915 || TO_CHAR (l_standard_start, '0009'),
3916 'DDMMYYYY HH24MI'
3917 );
3918 l_time_out :=
3919 TO_DATE ( TO_CHAR (l_hol_rec.holiday_date, 'DDMMYYYY ')
3920 || TO_CHAR (l_standard_stop, '0009'),
3921 'DDMMYYYY HH24MI'
3922 );
3923 l_hours := 24 * (l_time_out - l_time_in);
3924
3925 IF g_debug
3926 THEN
3927 hr_utility.TRACE ('l_hours :' || l_hours);
3928 hr_utility.TRACE ('l_time_in :' || l_time_in);
3929 hr_utility.TRACE ('l_time_out :' || l_time_out);
3930 END IF;
3931
3932 IF l_hours = 0
3933 THEN
3934 IF g_debug
3935 THEN
3936 hr_utility.set_location (l_proc, 90);
3937 END IF;
3938
3939 l_time_out := NULL;
3940 l_time_in := NULL;
3941
3942 IF g_debug
3943 THEN
3944 hr_utility.TRACE ('l_time_in :' || l_time_in);
3945 hr_utility.TRACE ('l_time_out :' || l_time_out);
3946 END IF;
3947 END IF;
3948
3949 IF g_debug
3950 THEN
3951 hr_utility.set_location (l_proc, 100);
3952 END IF;
3953 END IF;
3954
3955 IF g_debug
3956 THEN
3957 hr_utility.set_location (l_proc, 110);
3958 END IF;
3959 END IF;
3960
3961 IF g_debug
3962 THEN
3963 hr_utility.set_location (l_proc, 120);
3964 END IF;
3965 END IF;
3966
3967 IF g_debug
3968 THEN
3969 hr_utility.TRACE ('l_hours:' || l_hours);
3970 END IF;
3971
3972 IF l_hours >= 0
3973 THEN
3974 IF g_debug
3975 THEN
3976 hr_utility.set_location (l_proc, 130);
3977 END IF;
3978
3979 l_retcode :=
3980 record_hours_worked (NULL,
3981 TRUE,
3982 i_timecard_id,
3983 i_assignment_id,
3984 i_person_id,
3985 l_hol_rec.holiday_date,
3986 l_hol_rec.element_type_id,
3987 l_hours,
3988 --l_time_in,
3989 --l_time_out,
3990 i_start_time,
3991 i_end_time,
3992 i_start_date,
3993 i_wage_code,
3994 i_task_id,
3995 i_location_id,
3996 i_project_id,
3997 i_earn_pol_id,
3998 i_earn_reason_code,
3999 NULL,
4000 i_comment,
4001 i_rate_multiple,
4002 i_hourly_rate,
4003 i_amount,
4004 i_separate_check_flag,
4005 i_time_summary_id,
4006 i_tim_sum_eff_start_date,
4007 i_tim_sum_eff_end_date,
4008 i_created_by,
4009 i_last_updated_by,
4010 i_last_update_login,
4011 i_writesum_yn,
4012 i_explode_yn,
4013 i_batch_status,
4014 i_dt_update_mode,
4015 p_time_building_block_id,
4016 p_time_building_block_ovn,
4017 l_otm_error,
4018 l_oracle_error,
4019 l_created_tim_sum_id,
4020 i_state_name,
4021 i_county_name,
4022 i_city_name,
4023 i_zip_code
4024 );
4025
4026 --p_mode);
4027 IF g_debug
4028 THEN
4029 hr_utility.TRACE ('l_retcode :' || l_retcode);
4030 hr_utility.set_location (l_proc, 140);
4031 END IF;
4032 END IF;
4033
4034 IF l_retcode <> 0
4035 THEN
4036 IF g_debug
4037 THEN
4038 hr_utility.set_location (l_proc, 150);
4039 END IF;
4040
4041 RAISE l_hours_worked_error;
4042 END IF;
4043
4044 IF g_debug
4045 THEN
4046 hr_utility.set_location (l_proc, 160);
4047 END IF;
4048
4049 o_otm_error := l_otm_error;
4050 o_oracle_error := l_oracle_error;
4051 o_created_tim_sum_id := l_created_tim_sum_id;
4052
4053 IF g_debug
4054 THEN
4055 hr_utility.TRACE ('o_otm_error :' || o_otm_error);
4056 hr_utility.TRACE ('o_oracle_error :' || o_oracle_error);
4057 hr_utility.TRACE ('o_created_tim_sum_id :' || o_created_tim_sum_id
4058 );
4059 END IF;
4060 END LOOP;
4061
4062 IF g_debug
4063 THEN
4064 hr_utility.set_location (l_proc, 170);
4065 END IF;
4066
4067 RETURN 0;
4068 EXCEPTION
4069 WHEN NO_DATA_FOUND
4070 THEN
4071 IF g_debug
4072 THEN
4073 hr_utility.set_location (l_proc, 180);
4074 END IF;
4075
4076 RETURN 0;
4077 WHEN l_hours_worked_error
4078 THEN
4079 IF g_debug
4080 THEN
4081 hr_utility.set_location (l_proc, 190);
4082 END IF;
4083
4084 fnd_message.set_name ('HXT', 'HXT_39565_ERR_IN_CHH');
4085 o_otm_error := fnd_message.get;
4086 o_oracle_error := SQLERRM;
4087 call_hxthxc_gen_error ('HXT', 'HXT_39565_ERR_IN_CHH', NULL);
4088 --2278400
4089 RETURN 1;
4090 WHEN OTHERS
4091 THEN
4092 IF g_debug
4093 THEN
4094 hr_utility.set_location (l_proc, 200);
4095 END IF;
4096
4097 fnd_message.set_name ('HXT', 'HXT_39413_LOC_HOL');
4098 fnd_message.set_token ('ASG_ID', TO_CHAR (i_assignment_id));
4099 o_otm_error := fnd_message.get;
4100 o_oracle_error := SQLERRM;
4101 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', o_otm_error);
4102 --2278400
4103 RETURN 2;
4104 END create_holiday_hours;
4105
4106 /*************************************************************************/
4107 -- Procedure LOAD_POLICIES
4108 -- Purpose: Gets policies and premiums assigned to an input person
4109 -- on the date worked.
4110 --
4111 -- Modification Log:
4112 -- MM/DD/YY INI Description
4113 /*************************************************************************/
4114 PROCEDURE load_policies (
4115 p_summ_id IN NUMBER,
4116 p_summ_earn_pol_id IN NUMBER,
4117 p_summ_assignment_id IN NUMBER,
4118 p_summ_date_worked IN DATE,
4119 p_work_plan OUT NOCOPY NUMBER,
4120 p_rotation_plan OUT NOCOPY NUMBER,
4121 p_rotation_or_work_plan OUT NOCOPY VARCHAR2
4122 -- ,p_retcode OUT NOCOPY NUMBER
4123 -- ,p_hours OUT NOCOPY NUMBER
4124 ,
4125 p_shift_hours OUT NOCOPY NUMBER,
4126 p_egp_id OUT NOCOPY hxt_sum_hours_worked.earn_pol_id%TYPE -- 5903580 NUMBER earning policy
4127 ,
4128 p_hdp_id OUT NOCOPY NUMBER -- hrs deduction policy
4129 -- ,p_hdy_id OUT NOCOPY NUMBER -- holiday day ID
4130 ,
4131 p_sdp_id OUT NOCOPY NUMBER -- shift diff policy
4132 ,
4133 p_egp_type OUT NOCOPY VARCHAR2
4134 -- earning policy type
4135 ,
4136 p_egt_id OUT NOCOPY NUMBER
4137 -- include earning group
4138 ,
4139 p_pep_id OUT NOCOPY NUMBER -- prem elig policy
4140 ,
4141 p_pip_id OUT NOCOPY NUMBER -- prem interact policy
4142 ,
4143 p_hcl_id OUT NOCOPY NUMBER -- holiday calendar
4144 ,
4145 p_hcl_elt_id OUT NOCOPY NUMBER -- holiday earning type
4146 ,
4147 p_sdf_id OUT NOCOPY NUMBER -- override shift diff
4148 ,
4149 p_osp_id OUT NOCOPY NUMBER -- off-shift prem
4150 ,
4151 p_standard_start OUT NOCOPY NUMBER,
4152 p_standard_stop OUT NOCOPY NUMBER,
4153 p_early_start OUT NOCOPY NUMBER,
4154 p_late_stop OUT NOCOPY NUMBER,
4155 p_min_tcard_intvl OUT NOCOPY NUMBER,
4156 p_round_up OUT NOCOPY NUMBER,
4157 p_hol_code OUT NOCOPY NUMBER,
4158 p_hol_yn OUT NOCOPY VARCHAR2,
4159 p_error OUT NOCOPY NUMBER,
4160 p_overtime_type OUT NOCOPY VARCHAR2,
4161 p_otm_error OUT NOCOPY VARCHAR2
4162 )
4163 IS
4164 error_in_policies EXCEPTION;
4165 error_in_shift_info EXCEPTION;
4166 -- error_in_check_hol exception;
4167 l_proc VARCHAR2 (100);
4168 BEGIN
4169 g_debug := hr_utility.debug_enabled;
4170
4171 IF g_debug
4172 THEN
4173 l_proc := 'hxt_time_collection.LOAD_POLICIES';
4174 hr_utility.set_location (l_proc, 10);
4175 END IF;
4176
4177 p_hol_yn := 'N';
4178
4179 -- Get policies assigned to person
4180 BEGIN
4181 IF g_debug
4182 THEN
4183 hr_utility.set_location (l_proc, 20);
4184 END IF;
4185
4186 p_egp_id := p_summ_earn_pol_id;
4187 hxt_util.get_policies (p_egp_id,
4188 p_summ_assignment_id,
4189 p_summ_date_worked,
4190 p_work_plan,
4191 p_rotation_plan,
4192 p_egp_id,
4193 p_hdp_id,
4194 p_sdp_id,
4195 p_egp_type,
4196 p_egt_id,
4197 p_pep_id,
4198 p_pip_id,
4199 p_hcl_id,
4200 p_min_tcard_intvl,
4201 p_round_up,
4202 p_hcl_elt_id,
4203 p_error
4204 );
4205
4206 IF g_debug
4207 THEN
4208 hr_utility.set_location (l_proc, 30);
4209 END IF;
4210
4211 -- Check if error encountered
4212 IF p_error <> 0
4213 THEN
4214 IF g_debug
4215 THEN
4216 hr_utility.set_location (l_proc, 40);
4217 END IF;
4218
4219 RAISE error_in_policies;
4220 END IF;
4221
4222 IF g_debug
4223 THEN
4224 hr_utility.set_location (l_proc, 50);
4225 END IF;
4226 END;
4227
4228 -- Check if person assigned work or rotation plan
4229 BEGIN
4230 IF g_debug
4231 THEN
4232 hr_utility.set_location (l_proc, 60);
4233 END IF;
4234
4235 IF (p_work_plan IS NOT NULL) OR (p_rotation_plan IS NOT NULL)
4236 THEN
4237 IF g_debug
4238 THEN
4239 hr_utility.set_location (l_proc, 70);
4240 END IF;
4241
4242 -- Get premiums for shift
4243 hxt_util.get_shift_info (p_summ_date_worked,
4244 p_work_plan,
4245 p_rotation_plan,
4246 p_osp_id,
4247 p_sdf_id,
4248 p_standard_start,
4249 p_standard_stop,
4250 p_early_start,
4251 p_late_stop,
4252 p_shift_hours,
4253 p_error
4254 );
4255
4256 IF g_debug
4257 THEN
4258 hr_utility.set_location (l_proc, 80);
4259 END IF;
4260
4261 -- Check if error encountered
4262 IF p_error <> 0
4263 THEN
4264 IF g_debug
4265 THEN
4266 hr_utility.set_location (l_proc, 90);
4267 END IF;
4268
4269 RAISE error_in_shift_info;
4270 END IF;
4271
4272 IF g_debug
4273 THEN
4274 hr_utility.set_location (l_proc, 100);
4275 END IF;
4276 END IF; -- person assigned work or rotation plan
4277
4278 IF g_debug
4279 THEN
4280 hr_utility.set_location (l_proc, 110);
4281 END IF;
4282 END;
4283 -- Get holiday earning, day before/after, etc
4284 /* BEGIN
4285
4286 HXT_UTIL.Check_For_Holiday
4287 (p_summ_date_worked
4288 , p_hcl_id
4289 , p_hdy_id
4290 , p_hours
4291 , p_retcode);
4292
4293 -- Check if holiday
4294 IF p_retcode = 1 THEN
4295 p_hol_yn := 'Y'; -- Set holiday code
4296 END IF; -- holiday or not
4297
4298 EXCEPTION
4299 -- Check for error
4300 WHEN others THEN
4301 RAISE error_in_check_hol;
4302 END;
4303 */
4304 EXCEPTION
4305 WHEN error_in_policies
4306 THEN
4307 IF g_debug
4308 THEN
4309 hr_utility.set_location (l_proc, 120);
4310 END IF;
4311
4312 fnd_message.set_name ('HXT', 'HXT_39171_ERN_POL_OP_VIOL');
4313 fnd_message.set_token ('ORA_ERROR', SQLERRM);
4314 p_otm_error := fnd_message.get;
4315 WHEN error_in_shift_info
4316 THEN
4317 IF g_debug
4318 THEN
4319 hr_utility.set_location (l_proc, 130);
4320 END IF;
4321
4322 fnd_message.set_name ('HXT', 'HXT_39172_SHF_PREMS_OP_VIOL');
4323 fnd_message.set_token ('ORA_ERROR', SQLERRM);
4324 p_otm_error := fnd_message.get;
4325 /*
4326 WHEN error_in_check_hol THEN
4327 FND_MESSAGE.SET_NAME('HXT','HXT_39173_HOL_OP_VIOL');
4328 FND_MESSAGE.SET_TOKEN('ORA_ERROR',SQLERRM);
4329 p_otm_error := fnd_message.get;
4330 */
4331 END load_policies;
4332
4333 /*************************************************************************
4334 record_hours_worked()
4335 Fetches additional assignment details about employees.
4336 Creates hours worked records on the hxt_hours_worked database table.
4337 Calls the hxt_time_summary.generate_details function to explode details.
4338 **************************************************************************/
4339 FUNCTION record_hours_worked (
4340 p_timecard_source IN VARCHAR2,
4341 b_generate_holiday IN BOOLEAN,
4342 i_timecard_id IN NUMBER,
4343 i_assignment_id IN NUMBER,
4344 i_person_id IN NUMBER,
4345 i_date_worked IN DATE,
4346 i_element_id IN NUMBER,
4347 i_hours IN NUMBER,
4348 i_start_time IN DATE,
4349 i_end_time IN DATE,
4350 i_start_date IN DATE,
4351 i_wage_code VARCHAR2,
4352 i_task_id IN NUMBER,
4353 i_location_id IN NUMBER,
4354 i_project_id IN hxt_sum_hours_worked.project_id%TYPE,
4355 i_earn_pol_id IN hxt_sum_hours_worked.earn_pol_id%TYPE,
4356 -- SIR286
4357 i_earn_reason_code IN VARCHAR2,
4358 i_cost_center_id IN NUMBER,
4359 i_comment IN VARCHAR2,
4360 i_rate_multiple IN NUMBER,
4361 i_hourly_rate IN NUMBER,
4362 i_amount IN NUMBER,
4363 i_separate_check_flag IN VARCHAR2,
4364 i_time_summary_id IN NUMBER,
4365 i_tim_sum_eff_start_date IN DATE,
4366 i_tim_sum_eff_end_date IN DATE,
4367 i_created_by IN NUMBER,
4368 i_last_updated_by IN NUMBER,
4369 i_last_update_login IN NUMBER,
4370 i_writesum_yn IN VARCHAR2,
4371 i_explode_yn IN VARCHAR2,
4372 i_batch_status IN VARCHAR2,
4373 i_dt_update_mode IN VARCHAR2, --SIR290
4374 p_time_building_block_id IN NUMBER DEFAULT NULL,
4375 p_time_building_block_ovn IN NUMBER DEFAULT NULL,
4376 o_otm_error OUT NOCOPY VARCHAR2,
4377 o_oracle_error OUT NOCOPY VARCHAR2,
4378 o_created_tim_sum_id OUT NOCOPY NUMBER,
4379 i_state_name IN VARCHAR2 DEFAULT NULL,
4380 i_county_name IN VARCHAR2 DEFAULT NULL,
4381 i_city_name IN VARCHAR2 DEFAULT NULL,
4382 i_zip_code IN VARCHAR2 DEFAULT NULL
4383 )
4384 RETURN NUMBER
4385 IS
4386 CURSOR upd_det_cur (p_id NUMBER)
4387 IS
4388 SELECT fcl_earn_reason_code, ffv_cost_center_id, rate_multiple,
4389 hourly_rate, separate_check_flag, seqno, creation_date
4390 -- group_id
4391 FROM hxt_sum_hours_worked
4392 WHERE ID = p_id
4393 AND g_sysdate BETWEEN effective_start_date AND effective_end_date;
4394
4395 -- Begin AM 007a
4396 CURSOR allow_summary_correction (c_sum_id NUMBER)
4397 IS
4398 SELECT 'Y'
4399 FROM hxt_sum_hours_worked
4400 WHERE ID = c_sum_id AND effective_start_date = TRUNC (g_sess_date);
4401
4402 -- END AM 007a
4403
4404 -- l_hol_yn CHAR DEFAULT 'N';
4405 l_details_error EXCEPTION;
4406 l_details_system_error EXCEPTION;
4407 l_hours_worked_id_error EXCEPTION;
4408 l_seq_num_error EXCEPTION;
4409 l_paymix_error EXCEPTION;
4410 l_generate_details_error EXCEPTION;
4411 l_inc_tim_hr_entry_err EXCEPTION;
4412 l_adjust_tim_error EXCEPTION;
4413 l_retcode NUMBER DEFAULT 0;
4414 l_hours_worked_id NUMBER DEFAULT NULL;
4415 l_sequence_number NUMBER DEFAULT NULL;
4416 l_otm_error VARCHAR2 (300);
4417 l_rowid ROWID;
4418 l_object_version_number NUMBER DEFAULT NULL;
4419 l_allow_sum_correction VARCHAR2 (1) := 'N';
4420 --l_det_rec g_details_cur%ROWTYPE;
4421 --l_ep_det_rec g_earn_pol_details_cur%ROWTYPE;
4422 l_dt_update_mode VARCHAR2 (20) := i_dt_update_mode;
4423 l_return_code NUMBER;
4424 l_error_message VARCHAR2 (300);
4425 v_row_id ROWID;
4426 l_time_id NUMBER;
4427 l_created_by NUMBER;
4428 l_creation_date DATE;
4429 l_actual_time_in DATE;
4430 l_actual_time_out DATE;
4431 l_job_id NUMBER;
4432 l_start_time DATE := i_start_time;
4433 l_end_time DATE := i_end_time;
4434 l_d_hours NUMBER;
4435 l_hours NUMBER := i_hours;
4436 l_ad_code NUMBER;
4437 l_ad_error VARCHAR2 (300);
4438 l_session_date DATE;
4439 l_sess_date_err EXCEPTION;
4440 l_retro_edit_err EXCEPTION;
4441 l_delete_details_err EXCEPTION;
4442 o_error_message VARCHAR2 (300);
4443 o_return_code NUMBER;
4444 l_work_plan NUMBER;
4445 l_rotation_plan NUMBER;
4446 l_rotation_or_work_plan VARCHAR2 (1);
4447 l_shift_hours NUMBER;
4448 l_egp_id NUMBER; -- earning policy
4449 l_hdp_id NUMBER; -- hours deduction policy
4450 l_hdy_id NUMBER; -- holiday day ID
4451 l_sdp_id NUMBER; -- shift diff policy
4452 l_egp_type VARCHAR2 (30); -- earning policy type
4453 l_egt_id NUMBER; -- include earning group
4454 l_pep_id NUMBER; -- prem elig policy
4455 l_pip_id NUMBER; -- prem interact policy
4456 l_hcl_id NUMBER; -- holiday calendar
4457 l_hcl_elt_id NUMBER; -- holiday earning type
4458 l_sdf_id NUMBER; -- override shift diff prem
4459 l_osp_id NUMBER; -- off-shift prem
4460 l_standard_start NUMBER;
4461 l_standard_stop NUMBER;
4462 l_early_start NUMBER;
4463 l_late_stop NUMBER;
4464 l_min_tcard_intvl NUMBER;
4465 l_round_up NUMBER;
4466 l_hol_code NUMBER;
4467 l_hol_yn VARCHAR2 (1) := 'N';
4468 l_error NUMBER;
4469 l_overtime_type VARCHAR2 (4);
4470 l_otm_err VARCHAR2 (400);
4471 l_upd_rec upd_det_cur%ROWTYPE;
4472 l_proc VARCHAR2 (100);
4473 l_timecard_info hxc_self_service_time_deposit.timecard_info;
4474 l_index NUMBER;
4475 l_changed_flag BOOLEAN;
4476 BEGIN
4477 IF g_debug
4478 THEN
4479 l_proc := 'hxt_time_collection.RECORD_HOURS_WORKED';
4480 hr_utility.set_location (l_proc, 10);
4481 END IF;
4482
4483 /*Fetch additional assignment details about this employee*/
4484 /*
4485 BEGIN
4486 hr_utility.set_location (l_proc, 20);
4487 OPEN g_details_cur (i_assignment_id, i_date_worked);
4488 FETCH g_details_cur INTO l_det_rec;
4489 CLOSE g_details_cur;
4490 EXCEPTION
4491 WHEN NO_DATA_FOUND
4492 THEN
4493 hr_utility.set_location (l_proc, 30);
4494 RAISE l_details_error;
4495 WHEN OTHERS
4496 THEN
4497 hr_utility.set_location (l_proc, 40);
4498 RAISE l_details_system_error;
4499 END;
4500 */
4501 /*Get earning policy details */
4502 /*
4503 hr_utility.set_location (l_proc, 50);
4504 OPEN g_earn_pol_details_cur (
4505 NVL (i_earn_pol_id, l_det_rec.hxt_earning_policy),
4506 i_date_worked
4507 );
4508 FETCH g_earn_pol_details_cur INTO l_ep_det_rec;
4509 CLOSE g_earn_pol_details_cur;
4510 */
4511
4512 -- Gets policies and premiums assigned to the person on the date worked.
4513 load_policies (i_time_summary_id,
4514 i_earn_pol_id,
4515 i_assignment_id,
4516 i_date_worked,
4517 l_work_plan,
4518 l_rotation_plan,
4519 l_rotation_or_work_plan,
4520 -- l_retcode,
4521 -- l_hours,
4522 l_shift_hours, -- SIR212
4523 l_egp_id, -- earning policy
4524 l_hdp_id, -- hours deduction policy
4525 -- l_hdy_id, -- holiday day ID
4526 l_sdp_id, -- shift diff policy
4527 l_egp_type, -- earning policy type
4528 l_egt_id, -- include earning group
4529 l_pep_id, -- prem elig policy
4530 l_pip_id, -- prem interact policy
4531 l_hcl_id, -- holiday calendar
4532 l_hcl_elt_id, -- holiday earning type
4533 l_sdf_id, -- override shift diff prem
4534 l_osp_id, -- off-shift prem
4535 l_standard_start,
4536 l_standard_stop,
4537 l_early_start,
4538 l_late_stop,
4539 l_min_tcard_intvl,
4540 l_round_up,
4541 l_hol_code,
4542 l_hol_yn,
4543 l_error,
4544 l_overtime_type,
4545 l_otm_error
4546 );
4547
4548 IF g_debug
4549 THEN
4550 hr_utility.set_location (l_proc, 20);
4551 END IF;
4552
4553 IF l_otm_error IS NOT NULL
4554 THEN
4555 IF g_debug
4556 THEN
4557 hr_utility.set_location (l_proc, 30);
4558 END IF;
4559
4560 RAISE l_details_error;
4561 END IF;
4562
4563 /* Adjust the timings for any T/C Interval and Rounding factors given in */
4564 /* the earn policies - SIR236 */
4565
4566 /* Perform the holiday processing */
4567
4568 /*Obtain a unique hours worked id*/
4569 IF g_debug
4570 THEN
4571 hr_utility.set_location (l_proc, 40);
4572 END IF;
4573
4574 IF i_time_summary_id IS NULL
4575 THEN
4576 IF g_debug
4577 THEN
4578 hr_utility.set_location (l_proc, 50);
4579 END IF;
4580
4581 l_hours_worked_id := hxt_time_gen.get_hxt_seqno;
4582
4583 IF g_debug
4584 THEN
4585 hr_utility.TRACE ('l_hours_worked_id :' || l_hours_worked_id);
4586 END IF;
4587 ELSE
4588 IF g_debug
4589 THEN
4590 hr_utility.set_location (l_proc, 60);
4591 END IF;
4592
4593 l_hours_worked_id := i_time_summary_id;
4594
4595 IF g_debug
4596 THEN
4597 hr_utility.TRACE ('l_hours_worked_id :' || l_hours_worked_id);
4598 END IF;
4599 END IF;
4600
4601 IF g_debug
4602 THEN
4603 hr_utility.set_location (l_proc, 70);
4604 END IF;
4605
4606 IF l_hours_worked_id = NULL
4607 THEN
4608 IF g_debug
4609 THEN
4610 hr_utility.set_location (l_proc, 80);
4611 END IF;
4612
4613 RAISE l_hours_worked_id_error;
4614 END IF;
4615
4616 IF g_debug
4617 THEN
4618 hr_utility.set_location (l_proc, 90);
4619 END IF;
4620
4621 g_hours_worked_err_id := l_hours_worked_id;
4622 o_created_tim_sum_id := l_hours_worked_id;
4623
4624 IF g_debug
4625 THEN
4626 hr_utility.TRACE ('g_hours_worked_err_id :' || g_hours_worked_err_id);
4627 hr_utility.TRACE ('o_created_tim_sum_id :' || o_created_tim_sum_id);
4628 END IF;
4629
4630 IF b_generate_holiday = TRUE
4631 THEN
4632 IF g_debug
4633 THEN
4634 hr_utility.set_location (l_proc, 100);
4635 END IF;
4636
4637 l_hours := i_hours;
4638 l_start_time := i_start_time;
4639 l_end_time := i_end_time;
4640 l_hol_yn := 'Y';
4641
4642 IF g_debug
4643 THEN
4644 hr_utility.TRACE ('l_hours :' || l_hours);
4645 hr_utility.TRACE ('l_start_time :' || l_start_time);
4646 hr_utility.TRACE ('l_end_time :' || l_end_time);
4647 hr_utility.TRACE ('l_hol_yn :' || l_hol_yn);
4648 END IF;
4649 ELSE
4650 IF (i_start_time IS NOT NULL AND i_end_time IS NOT NULL)
4651 THEN
4652 IF g_debug
4653 THEN
4654 hr_utility.set_location (l_proc, 110);
4655 hr_utility.TRACE ('before Adjust_Timings');
4656 END IF;
4657
4658 hxt_time_collection.adjust_timings
4659 (p_timecard_source,
4660 i_assignment_id,
4661 i_person_id,
4662 i_date_worked,
4663 i_timecard_id,
4664 l_hours_worked_id,
4665 i_earn_pol_id,
4666 l_start_time,
4667 l_end_time,
4668 l_d_hours,
4669 l_ad_code,
4670 l_ad_error,
4671 l_start_time, -- null,
4672 l_end_time, -- null,
4673 l_start_time,
4674 -- l_actual_time_in,
4675 l_end_time -- l_actual_time_out
4676 ); -- SIR236
4677
4678 IF g_debug
4679 THEN
4680 hr_utility.set_location (l_proc, 120);
4681 hr_utility.TRACE ('after Adjust_Timings');
4682 hr_utility.TRACE ('l_ad_error is : ' || l_ad_error);
4683 hr_utility.TRACE ('l_ad_code is : ' || TO_CHAR (l_ad_code));
4684 END IF;
4685
4686 IF l_ad_code <> 0
4687 THEN -- SIR236
4688 IF g_debug
4689 THEN
4690 hr_utility.set_location (l_proc, 125);
4691 END IF;
4692
4693 RAISE l_adjust_tim_error;
4694 END IF;
4695
4696 IF g_debug
4697 THEN
4698 hr_utility.set_location (l_proc, 130);
4699 END IF;
4700
4701 l_hours := reset_hours (l_start_time, l_end_time);
4702
4703 IF g_debug
4704 THEN
4705 hr_utility.set_location (l_proc, 140);
4706 hr_utility.TRACE ('l_hours :' || l_hours);
4707 END IF;
4708 END IF;
4709
4710 IF g_debug
4711 THEN
4712 hr_utility.set_location (l_proc, 150);
4713 END IF;
4714 END IF;
4715
4716 /*Obtain a unique hours worked id*/
4717
4718 /*
4719 IF i_time_summary_id IS NULL THEN
4720 l_hours_worked_id := hxt_time_gen.Get_HXT_Seqno;
4721 ELSE
4722 l_hours_worked_id := i_time_summary_id;
4723 END IF;
4724 --
4725
4726 IF l_hours_worked_id = NULL THEN
4727 RAISE l_hours_worked_id_error;
4728 END IF;
4729 --
4730 g_hours_worked_err_id := l_hours_worked_id;
4731 o_created_tim_sum_id := l_hours_worked_id;
4732 */
4733 IF g_debug
4734 THEN
4735 hr_utility.set_location (l_proc, 155);
4736 END IF;
4737
4738 /*Obtain the next sequence number for hours worked on this day*/
4739 l_sequence_number :=
4740 hxt_util.get_next_seqno (i_timecard_id, i_date_worked);
4741
4742 IF g_debug
4743 THEN
4744 hr_utility.TRACE ('l_sequence_number :' || l_sequence_number);
4745 END IF;
4746
4747 --
4748 IF l_sequence_number = NULL
4749 THEN
4750 IF g_debug
4751 THEN
4752 hr_utility.set_location (l_proc, 160);
4753 END IF;
4754
4755 RAISE l_seq_num_error;
4756 END IF;
4757
4758 --
4759 IF i_writesum_yn = 'Y'
4760 THEN
4761 IF g_debug
4762 THEN
4763 hr_utility.set_location (l_proc, 170);
4764 END IF;
4765
4766 IF i_time_summary_id IS NULL
4767 THEN
4768 IF g_debug
4769 THEN
4770 hr_utility.set_location (l_proc, 180);
4771 END IF;
4772
4773 -- SELECT hxt_group_id_s.nextval
4774 -- INTO l_group_id
4775 -- FROM dual;
4776 --
4777 /*
4778 INSERT INTO hxt_sum_hours_worked_f
4779 ( id,
4780 tim_id,
4781 date_worked,
4782 seqno,
4783 hours,
4784 group_id,
4785 assignment_id,
4786 element_type_id,
4787 actual_time_in, --SIR374
4788 actual_time_out, --SIR374
4789 time_in,
4790 time_out,
4791 fcl_earn_reason_code,
4792 ffv_cost_center_id,
4793 tas_id,
4794 location_id,
4795 project_id,
4796 earn_pol_id,
4797 separate_check_flag,
4798 created_by,
4799 creation_date,
4800 last_updated_by,
4801 last_update_date,
4802 last_update_login,
4803 prev_wage_code,
4804 hrw_comment,
4805 rate_multiple,
4806 hourly_rate,
4807 amount,
4808 effective_start_date,
4809 effective_end_date)
4810 VALUES
4811 ( l_hours_worked_id,
4812 i_timecard_id,
4813 i_date_worked,
4814 l_sequence_number,
4815 l_hours, -- SIR236
4816 l_group_id,
4817 i_assignment_id,
4818 i_element_id,
4819 i_start_time, -- SIR374 Actual time in
4820 i_end_time, -- SIR374 Actual time out
4821 l_start_time,
4822 l_end_time,
4823 i_earn_reason_code,
4824 i_cost_center_id,
4825 i_task_id,
4826 i_location_id,
4827 i_project_id,
4828 i_earn_pol_id,
4829 i_separate_check_flag,
4830 i_created_by,
4831 g_sysdatetime,
4832 i_last_updated_by,
4833 g_sysdatetime,
4834 i_last_update_login,
4835 i_wage_code,
4836 i_comment,
4837 i_rate_multiple,
4838 i_hourly_rate,
4839 i_amount,
4840 TRUNC(g_sess_date),
4841 hr_general.end_of_time);
4842 */
4843
4844 /* Call dml to insert hours */
4845 hxt_dml.insert_hxt_sum_hours_worked
4846 (p_rowid => l_rowid,
4847 p_id => l_hours_worked_id,
4848 p_tim_id => i_timecard_id,
4849 p_date_worked => i_date_worked,
4850 p_assignment_id => i_assignment_id,
4851 p_hours => l_hours,
4852 p_time_in => l_start_time,
4853 p_time_out => l_end_time,
4854 p_element_type_id => i_element_id,
4855 p_fcl_earn_reason_code => i_earn_reason_code,
4856 p_ffv_cost_center_id => i_cost_center_id,
4857 p_ffv_labor_account_id => NULL,
4858 p_tas_id => i_task_id,
4859 p_location_id => i_location_id,
4860 p_sht_id => NULL,
4861 p_hrw_comment => i_comment,
4862 p_ffv_rate_code_id => NULL,
4863 p_rate_multiple => i_rate_multiple,
4864 p_hourly_rate => i_hourly_rate,
4865 p_amount => i_amount,
4866 p_fcl_tax_rule_code => NULL,
4867 p_separate_check_flag => i_separate_check_flag,
4868 p_seqno => l_sequence_number,
4869 p_created_by => i_created_by,
4870 p_creation_date => g_sysdatetime,
4871 p_last_updated_by => i_last_updated_by,
4872 p_last_update_date => g_sysdatetime,
4873 p_last_update_login => i_last_update_login,
4874 p_actual_time_in => i_start_time,
4875 p_actual_time_out => i_end_time,
4876 p_effective_start_date => TRUNC (g_sess_date),
4877 p_effective_end_date => hr_general.end_of_time,
4878 p_project_id => i_project_id,
4879 p_prev_wage_code => i_wage_code,
4880 p_job_id => NULL,
4881 p_earn_pol_id => i_earn_pol_id,
4882 p_time_building_block_id => p_time_building_block_id,
4883 p_time_building_block_ovn => p_time_building_block_ovn,
4884 p_object_version_number => l_object_version_number,
4885 p_state_name => i_state_name,
4886 p_county_name => i_county_name,
4887 p_city_name => i_city_name,
4888 p_zip_code => i_zip_code
4889 );
4890
4891 IF g_debug
4892 THEN
4893 hr_utility.set_location (l_proc, 190);
4894 END IF;
4895 --
4896 -- Note: If a non-NULL tim_summary_id value is received by the API, check
4897 -- the current batch status. If the batch is in a hold state the
4898 -- current summary row may be updated. *ALL* input values will be
4899 -- updated. This means that if a named parameter call was used any
4900 -- parameters not received by the API will be NULL'ed out. If the
4901 -- batch is not in a hold status make a 'retro' entry. That is,
4902 -- expire the old summary record and insert a new one that goes into
4903 -- effect immediately after the expiration of the old record.
4904 --
4905 ELSE
4906 IF g_debug
4907 THEN
4908 hr_utility.set_location (l_proc, 200);
4909 END IF;
4910
4911 --SIR290 IF nvl(i_batch_status, 'QQ') in ('VV','VW', 'H', 'VE') THEN
4912 --
4913 -- Check whether to allow corrections to summary rows during retro.
4914 --
4915 OPEN allow_summary_correction (i_time_summary_id);
4916
4917 FETCH allow_summary_correction
4918 INTO l_allow_sum_correction;
4919
4920 IF g_debug
4921 THEN
4922 hr_utility.TRACE ( 'l_allow_sum_correction :'
4923 || l_allow_sum_correction
4924 );
4925 END IF;
4926
4927 CLOSE allow_summary_correction;
4928
4929 --
4930 IF (l_dt_update_mode = 'CORRECTION')
4931 AND (l_allow_sum_correction = 'Y')
4932 THEN
4933 IF g_debug
4934 THEN
4935 hr_utility.set_location (l_proc, 210);
4936 END IF;
4937
4938 /* UPDATE hxt_sum_hours_worked_f
4939 SET effective_start_date = i_tim_sum_eff_start_date,
4940 effective_end_date = i_tim_sum_eff_end_date,
4941 date_worked = i_date_worked,
4942 assignment_id = i_assignment_id,
4943 hours = l_hours, -- SIR236
4944 time_in = l_start_time, -- SIR236
4945 time_out = l_end_time, -- SIR236
4946 element_type_id = i_element_id,
4947 fcl_earn_reason_code = i_earn_reason_code,
4948 ffv_cost_center_id = i_cost_center_id,
4949 tas_id = i_task_id,
4950 location_id = i_location_id,
4951 hrw_comment = i_comment,
4952 rate_multiple = i_rate_multiple,
4953 hourly_rate = i_hourly_rate,
4954 amount = i_amount,
4955 separate_check_flag = i_separate_check_flag,
4956 last_updated_by = i_last_updated_by,
4957 last_update_date = g_sysdatetime,
4958 last_update_login = i_last_update_login,
4959 prev_wage_code = i_wage_code,
4960 project_id = i_project_id,
4961 earn_pol_id = i_earn_pol_id
4962 WHERE ROWID = (SELECT ROWID
4963 FROM hxt_sum_hours_worked
4964 WHERE id = i_time_summary_id); */
4965
4966 /* Call DML to do the update */
4967 SELECT ROWID, tim_id, seqno, created_by,
4968 creation_date, actual_time_in, actual_time_out,
4969 job_id, object_version_number
4970 INTO l_rowid, l_time_id, l_sequence_number, l_created_by,
4971 l_creation_date, l_actual_time_in, l_actual_time_out,
4972 l_job_id, l_object_version_number
4973 FROM hxt_sum_hours_worked
4974 WHERE ID = i_time_summary_id;
4975
4976 IF g_debug
4977 THEN
4978 hr_utility.set_location (l_proc, 220);
4979 END IF;
4980
4981 hxt_dml.update_hxt_sum_hours_worked
4982 (p_rowid => l_rowid,
4983 p_id => i_time_summary_id,
4984 p_tim_id => l_time_id,
4985 p_date_worked => i_date_worked,
4986 p_assignment_id => i_assignment_id,
4987 p_hours => l_hours,
4988 p_time_in => l_start_time,
4989 p_time_out => l_end_time,
4990 p_element_type_id => i_element_id,
4991 p_fcl_earn_reason_code => i_earn_reason_code,
4992 p_ffv_cost_center_id => i_cost_center_id,
4993 p_ffv_labor_account_id => NULL,
4994 p_tas_id => i_task_id,
4995 p_location_id => i_location_id,
4996 p_sht_id => NULL,
4997 p_hrw_comment => i_comment,
4998 p_ffv_rate_code_id => NULL,
4999 p_rate_multiple => i_rate_multiple,
5000 p_hourly_rate => i_hourly_rate,
5001 p_amount => i_amount,
5002 p_fcl_tax_rule_code => NULL,
5003 p_separate_check_flag => i_separate_check_flag,
5004 p_seqno => l_sequence_number,
5005 p_created_by => l_created_by,
5006 p_creation_date => l_creation_date,
5007 p_last_updated_by => i_last_updated_by,
5008 p_last_update_date => g_sysdatetime,
5009 p_last_update_login => i_last_update_login,
5010 p_actual_time_in => l_actual_time_in,
5011 p_actual_time_out => l_actual_time_out,
5012 p_effective_start_date => i_tim_sum_eff_start_date,
5013 p_effective_end_date => i_tim_sum_eff_end_date,
5014 p_project_id => i_project_id,
5015 p_prev_wage_code => i_wage_code,
5016 p_job_id => l_job_id,
5017 p_earn_pol_id => i_earn_pol_id,
5018 p_time_building_block_id => p_time_building_block_id,
5019 p_time_building_block_ovn => p_time_building_block_ovn,
5020 p_object_version_number => l_object_version_number,
5021 p_state_name => i_state_name,
5022 p_county_name => i_county_name,
5023 p_city_name => i_city_name,
5024 p_zip_code => i_zip_code
5025 );
5026 ELSE
5027 IF g_debug
5028 THEN
5029 hr_utility.set_location (l_proc, 230);
5030 END IF;
5031
5032 SELECT ROWID
5033 INTO v_row_id
5034 FROM hxt_sum_hours_worked
5035 WHERE ID = i_time_summary_id;
5036
5037 --
5038 /*
5039 INSERT INTO hxt_sum_hours_worked_f
5040 ( id,
5041 tim_id,
5042 date_worked,
5043 seqno,
5044 hours,
5045 -- group_id,
5046 assignment_id,
5047 element_type_id,
5048 actual_time_in, --SIR374
5049 actual_time_out, --SIR374
5050 time_in,
5051 time_out,
5052 fcl_earn_reason_code,
5053 ffv_cost_center_id,
5054 tas_id,
5055 location_id,
5056 project_id,
5057 earn_pol_id,
5058 separate_check_flag,
5059 created_by,
5060 creation_date,
5061 last_updated_by,
5062 last_update_date,
5063 last_update_login,
5064 prev_wage_code,
5065 hrw_comment,
5066 rate_multiple,
5067 hourly_rate,
5068 amount,
5069 effective_start_date,
5070 effective_end_date)
5071 SELECT tsm.id,
5072 tsm.tim_id,
5073 i_date_worked, -- SIR290 tsm.date_worked,
5074 tsm.seqno,
5075 i_hours, -- SIR290 tsm.hours,
5076 -- l_group_id,
5077 i_assignment_id, -- SIR290 tsm.assignment_id,
5078 i_element_id, -- SIR290 tsm.element_type_id,
5079 i_start_time, -- SIR374 Actual time in
5080 i_end_time, -- SIR374 Actual time out
5081 i_start_time, -- SIR290 tsm.time_in,
5082 i_end_time, -- SIR290 tsm.time_out,
5083 i_earn_reason_code, -- SIR290 tsm.fcl_earn_reason_code,
5084 i_cost_center_id, -- SIR290 tsm.ffv_cost_center_id,
5085 i_task_id, -- SIR290 tsm.tas_id,
5086 i_location_id, -- SIR290 tsm.location_id,
5087 i_project_id, -- SIR290 tsm.project_id,
5088 i_earn_pol_id, -- SIR290 tsm.earn_pol_id,
5089 i_separate_check_flag, -- SIR290 tsm.separate_check_flag,
5090 i_created_by, -- SIR290 tsm.created_by,
5091 g_sysdatetime, -- SIR290 tsm.creation_date,
5092 i_last_updated_by,
5093 g_sysdatetime,
5094 i_last_update_login,
5095 i_wage_code, -- SIR290 tsm.wage_code,
5096 i_comment, -- SIR290 tsm.hrw_comment,
5097 i_rate_multiple, -- SIR290 tsm.rate_multiple,
5098 i_hourly_rate, -- SIR290 tsm.hourly_rate,
5099 i_amount, -- SIR290 tsm.amount,
5100 trunc(g_sess_date),
5101 hr_general.end_of_time
5102 FROM HXT_SUM_HOURS_WORKED_F TSM
5103 WHERE ROWID = v_row_id;
5104 */
5105 SELECT tsm.ID, tsm.tim_id, tsm.seqno
5106 INTO l_hours_worked_id, l_time_id, l_sequence_number
5107 FROM hxt_sum_hours_worked_f tsm
5108 WHERE ROWID = v_row_id;
5109
5110 IF g_debug
5111 THEN
5112 hr_utility.set_location (l_proc, 240);
5113 END IF;
5114
5115 hxt_dml.insert_hxt_sum_hours_worked
5116 (p_rowid => l_rowid,
5117 p_id => l_hours_worked_id,
5118 p_tim_id => l_time_id,
5119 p_date_worked => i_date_worked,
5120 p_assignment_id => i_assignment_id,
5121 p_hours => l_hours,
5122 p_time_in => l_start_time,
5123 p_time_out => l_end_time,
5124 p_element_type_id => i_element_id,
5125 p_fcl_earn_reason_code => i_earn_reason_code,
5126 p_ffv_cost_center_id => i_cost_center_id,
5127 p_ffv_labor_account_id => NULL,
5128 p_tas_id => i_task_id,
5129 p_location_id => i_location_id,
5130 p_sht_id => NULL,
5131 p_hrw_comment => i_comment,
5132 p_ffv_rate_code_id => NULL,
5133 p_rate_multiple => i_rate_multiple,
5134 p_hourly_rate => i_hourly_rate,
5135 p_amount => i_amount,
5136 p_fcl_tax_rule_code => NULL,
5137 p_separate_check_flag => i_separate_check_flag,
5138 p_seqno => l_sequence_number,
5139 p_created_by => i_created_by,
5140 p_creation_date => g_sysdatetime,
5141 p_last_updated_by => i_last_updated_by,
5142 p_last_update_date => g_sysdatetime,
5143 p_last_update_login => i_last_update_login,
5144 p_actual_time_in => i_start_time,
5145 p_actual_time_out => i_end_time,
5146 p_effective_start_date => TRUNC (g_sess_date),
5147 p_effective_end_date => hr_general.end_of_time,
5148 p_project_id => i_project_id,
5149 p_prev_wage_code => i_wage_code,
5150 p_job_id => NULL,
5151 p_earn_pol_id => i_earn_pol_id,
5152 p_time_building_block_id => p_time_building_block_id,
5153 p_time_building_block_ovn => p_time_building_block_ovn,
5154 p_object_version_number => l_object_version_number,
5155 p_state_name => i_state_name,
5156 p_county_name => i_county_name,
5157 p_city_name => i_city_name,
5158 p_zip_code => i_zip_code
5159 );
5160
5161 IF g_debug
5162 THEN
5163 hr_utility.set_location (l_proc, 250);
5164 END IF;
5165
5166 --
5167 UPDATE hxt_sum_hours_worked_f
5168 SET effective_end_date = TRUNC (g_sess_date - 1)
5169 WHERE ROWID = v_row_id;
5170
5171 --
5172 IF g_debug
5173 THEN
5174 hr_utility.set_location (l_proc, 260);
5175 END IF;
5176 END IF; -- End of Correction/History Check
5177
5178 IF g_debug
5179 THEN
5180 hr_utility.set_location (l_proc, 270);
5181 END IF;
5182 END IF; -- End of Time Summary Id Check
5183
5184 IF g_debug
5185 THEN
5186 hr_utility.set_location (l_proc, 280);
5187 END IF;
5188 END IF; -- End of Write Summary Check
5189
5190 /*Generate time details*/
5191 IF g_debug
5192 THEN
5193 hr_utility.set_location (l_proc, 290);
5194 END IF;
5195
5196 OPEN upd_det_cur (l_hours_worked_id);
5197
5198 FETCH upd_det_cur
5199 INTO l_upd_rec;
5200
5201 CLOSE upd_det_cur;
5202
5203 --
5204 IF i_explode_yn = 'Y'
5205 THEN
5206 IF g_debug
5207 THEN
5208 hr_utility.set_location (l_proc, 300);
5209 END IF;
5210
5211 --
5212 l_retcode := hxt_tim_col_util.get_session_date (l_session_date);
5213
5214 IF g_debug
5215 THEN
5216 hr_utility.TRACE ('l_retcode :' || l_retcode);
5217 END IF;
5218
5219 IF l_retcode = 1
5220 THEN
5221 IF g_debug
5222 THEN
5223 hr_utility.set_location (l_proc, 310);
5224 END IF;
5225
5226 RAISE l_sess_date_err;
5227 END IF;
5228
5229 --
5230 IF g_debug
5231 THEN
5232 hr_utility.set_location (l_proc, 320);
5233 END IF;
5234
5235 hxt_td_util.retro_restrict_edit (p_tim_id => i_timecard_id,
5236 p_session_date => l_session_date,
5237 o_dt_update_mod => l_dt_update_mode,
5238 o_error_message => l_otm_error,
5239 o_return_code => o_return_code,
5240 p_parent_id => l_hours_worked_id
5241 );
5242
5243 IF g_debug
5244 THEN
5245 hr_utility.set_location (l_proc, 330);
5246 hr_utility.TRACE ('o_return_code :' || o_return_code);
5247 END IF;
5248
5249 IF o_return_code = 1 OR l_otm_error IS NOT NULL
5250 THEN
5251 IF g_debug
5252 THEN
5253 hr_utility.set_location (l_proc, 340);
5254 END IF;
5255
5256 -- Bug 7380181
5257 -- Raised the exception here instead of raising it from the
5258 -- below IF construct. This checks for time building blocks info
5259 -- if it is changed, while it does not look at the attributes being changed.
5260 -- For a timecard which is transferred to BEE today, even an attribute change
5261 -- should be stopped.
5262
5263 RAISE l_retro_edit_err;
5264 /*
5265 l_timecard_info :=
5266 hxc_self_service_time_deposit.get_building_blocks
5267 ();
5268 l_index := l_timecard_info.FIRST;
5269
5270 LOOP
5271 EXIT WHEN NOT l_timecard_info.EXISTS (l_index);
5272
5273 IF ( l_timecard_info (l_index).changed = 'N'
5274 OR l_timecard_info (l_index).changed IS NULL
5275 )
5276 THEN
5277 l_changed_flag := FALSE;
5278 ELSE
5279 l_changed_flag := TRUE;
5280 EXIT;
5281 END IF;
5282
5283 l_index := l_timecard_info.NEXT (l_index);
5284 END LOOP;
5285
5286 IF p_timecard_source = 'Time Store' AND l_changed_flag = FALSE
5287 THEN
5288 NULL;
5289 ELSE
5290 RAISE l_retro_edit_err;
5291 END IF;
5292 */
5293 END IF;
5294
5295 --
5296 BEGIN
5297 IF NVL (l_dt_update_mode, 'CORRECTION') = 'CORRECTION'
5298 THEN
5299 IF g_debug
5300 THEN
5301 hr_utility.set_location (l_proc, 350);
5302 END IF;
5303
5304 -- Delete
5305 DELETE FROM hxt_det_hours_worked_f
5306 WHERE ROWID IN (SELECT ROWID
5307 FROM hxt_det_hours_worked
5308 WHERE parent_id = l_hours_worked_id);
5309
5310 DELETE FROM hxt_errors_f
5311 WHERE ROWID IN (
5312 SELECT ROWID
5313 FROM hxt_errors
5314 WHERE hrw_id = l_hours_worked_id
5315 AND hrw_id IS NOT NULL
5316 --hxt11ipatch don't delete timecard
5317 ); --level errors
5318 ELSE
5319 IF g_debug
5320 THEN
5321 hr_utility.set_location (l_proc, 360);
5322 END IF;
5323
5324 -- Expire
5325 UPDATE hxt_det_hours_worked_f
5326 SET effective_end_date = l_session_date - 1
5327 WHERE ROWID IN (SELECT ROWID
5328 FROM hxt_det_hours_worked
5329 WHERE parent_id = l_hours_worked_id);
5330
5331 UPDATE hxt_errors_f
5332 SET effective_end_date = l_session_date - 1
5333 WHERE ROWID IN (
5334 SELECT ROWID
5335 FROM hxt_errors
5336 WHERE hrw_id = l_hours_worked_id
5337 AND hrw_id IS NOT NULL
5338 --hxt11ipatch don't expire timecard
5339 ); -- level errors
5340 END IF; -- Update or Correction
5341
5342 IF g_debug
5343 THEN
5344 hr_utility.set_location (l_proc, 370);
5345 END IF;
5346 EXCEPTION
5347 WHEN OTHERS
5348 THEN
5349 IF g_debug
5350 THEN
5351 hr_utility.set_location (l_proc, 380);
5352 END IF;
5353
5354 fnd_message.set_name ('HXT', 'HXT_39567_ERR_IN_DD');
5355 fnd_message.set_token ('ERR_TEXT', SQLERRM);
5356 o_error_message := fnd_message.get;
5357 call_hxthxc_gen_error ('HXC',
5358 'HXC_HXT_DEP_VAL_OTMERR',
5359 o_error_message
5360 ); --2278400
5361 END; -- delete details
5362
5363 IF g_debug
5364 THEN
5365 hr_utility.set_location (l_proc, 390);
5366 hr_utility.TRACE ('Before Generate_Details ');
5367 hr_utility.TRACE ('l_osp_id :' || l_osp_id);
5368 hr_utility.TRACE ('l_sdf_id :' || l_sdf_id);
5369 hr_utility.TRACE ('l_sdp_id :' || l_sdp_id);
5370 hr_utility.TRACE ('l_hdp_id :' || l_hdp_id);
5371 hr_utility.TRACE ('l_egp_id :' || l_egp_id);
5372 hr_utility.TRACE ('l_rotation_plan:' || l_rotation_plan);
5373 END IF;
5374
5375 --
5376 l_retcode :=
5377 hxt_time_summary.generate_details
5378 (l_egp_id,
5379 l_egp_type -- fcl_earn_type
5380 ,
5381 l_egt_id -- egt_id
5382 ,
5383 l_sdp_id -- hxt_shift_differential_policy
5384 ,
5385 l_hdp_id -- hxt_hour_deduction_policy
5386 ,
5387 l_hcl_id -- hcl_id --Holiday calendar id
5388 ,
5389 l_pep_id -- pep_id
5390 ,
5391 l_pip_id -- pip_id
5392 ,
5393 l_sdf_id -- shift_diff_ovrrd_id
5394 ,
5395 l_osp_id -- off_shift_prem_id
5396 ,
5397 NULL -- standard start
5398 ,
5399 NULL -- standard stop
5400 ,
5401 NULL -- early start
5402 ,
5403 NULL -- late stop
5404 ,
5405 l_hol_yn,
5406 i_person_id,
5407 'hxt_time_collection',
5408 l_hours_worked_id,
5409 i_timecard_id,
5410 i_date_worked,
5411 i_assignment_id,
5412 l_hours,
5413 l_start_time,
5414 l_end_time,
5415 i_element_id,
5416 l_upd_rec.fcl_earn_reason_code -- fcl_earn_reason_code
5417 ,
5418 l_upd_rec.ffv_cost_center_id -- ffv_cost_center_id
5419 ,
5420 NULL -- ffv_labor_account_id
5421 ,
5422 i_task_id -- tas_id
5423 ,
5424 i_location_id -- location_id
5425 ,
5426 NULL -- sht_id
5427 ,
5428 i_comment -- hrw_comment
5429 ,
5430 NULL -- ffv_rate_code_id
5431 ,
5432 l_upd_rec.rate_multiple -- rate_multiple
5433 ,
5434 l_upd_rec.hourly_rate -- hourly_rate
5435 ,
5436 i_amount -- amount
5437 ,
5438 NULL -- fcl_tax_rule_code
5439 ,
5440 l_upd_rec.separate_check_flag -- separarate_check_flag
5441 ,
5442 l_upd_rec.seqno,
5443 i_created_by,
5444 l_upd_rec.creation_date,
5445 i_last_updated_by,
5446 SYSDATE -- last_update_date
5447 ,
5448 i_last_update_login,
5449 i_start_date,
5450 NULL,
5451 TRUNC (g_sess_date),
5452 hr_general.end_of_time,
5453 i_project_id -- p_PROJECT_ID
5454 ,
5455 NULL -- p_job_id
5456 ,
5457 'P' -- p_PAY_STATUS
5458 ,
5459 'P' -- p_PA_STATUS
5460 ,
5461 NULL -- p_RETRO_BATCH_ID
5462 ,
5463 NVL (l_dt_update_mode, 'CORRECTION'),
5464 p_state_name => i_state_name,
5465 p_county_name => i_county_name,
5466 p_city_name => i_city_name,
5467 p_zip_code => i_zip_code
5468 );
5469
5470 IF g_debug
5471 THEN
5472 hr_utility.set_location (l_proc, 400);
5473 hr_utility.TRACE ('l_retcode :' || l_retcode);
5474 END IF;
5475
5476 IF l_retcode = 2
5477 THEN
5478 IF g_debug
5479 THEN
5480 hr_utility.set_location (l_proc, 410);
5481 END IF;
5482
5483 RAISE l_generate_details_error;
5484 END IF;
5485
5486 IF l_retcode = 11
5487 THEN
5488 IF g_debug
5489 THEN
5490 hr_utility.set_location (l_proc, 415);
5491 END IF;
5492
5493 RAISE l_inc_tim_hr_entry_err;
5494 END IF;
5495
5496 IF g_debug
5497 THEN
5498 hr_utility.set_location (l_proc, 420);
5499 END IF;
5500 END IF; -- End of Explode YN Check
5501
5502 --
5503 IF g_debug
5504 THEN
5505 hr_utility.set_location (l_proc, 430);
5506 END IF;
5507
5508 RETURN 0;
5509 EXCEPTION
5510 WHEN l_adjust_tim_error
5511 THEN
5512 IF g_debug
5513 THEN
5514 hr_utility.set_location (l_proc, 440);
5515 END IF;
5516
5517 o_otm_error := l_ad_error;
5518
5519 IF g_debug
5520 THEN
5521 hr_utility.TRACE ('o_otm_error :' || o_otm_error);
5522 END IF;
5523
5524 RETURN 2;
5525 WHEN l_details_error
5526 THEN
5527 IF g_debug
5528 THEN
5529 hr_utility.set_location (l_proc, 450);
5530 END IF;
5531
5532 fnd_message.set_name ('HXT', 'HXT_39414_LOC_ADDL_ASG');
5533 fnd_message.set_token ('ASG_ID', TO_CHAR (i_assignment_id));
5534 o_otm_error := fnd_message.get;
5535 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', o_otm_error);
5536 --2278400
5537 RETURN 1;
5538 WHEN l_details_system_error
5539 THEN
5540 IF g_debug
5541 THEN
5542 hr_utility.set_location (l_proc, 460);
5543 END IF;
5544
5545 fnd_message.set_name ('HXT', 'HXT_39415_FETCH_ASG_DET');
5546 fnd_message.set_token ('ASG_ID', TO_CHAR (i_assignment_id));
5547 o_otm_error := fnd_message.get;
5548 o_oracle_error := SQLERRM;
5549 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', o_otm_error);
5550 --2278400
5551 RETURN 2;
5552 WHEN l_paymix_error
5553 THEN
5554 IF g_debug
5555 THEN
5556 hr_utility.set_location (l_proc, 470);
5557 END IF;
5558
5559 fnd_message.set_name ('HXT', 'HXT_39445_CHG_DATE');
5560 o_otm_error := fnd_message.get;
5561 o_oracle_error := SQLERRM;
5562 call_hxthxc_gen_error ('HXT', 'HXT_39445_CHG_DATE', NULL); --2278400
5563 RETURN 1;
5564 WHEN l_hours_worked_id_error
5565 THEN
5566 IF g_debug
5567 THEN
5568 hr_utility.set_location (l_proc, 480);
5569 END IF;
5570
5571 fnd_message.set_name ('HXT', 'HXT_39416_GET_HRW_ID');
5572 o_otm_error := fnd_message.get;
5573 o_oracle_error := SQLERRM;
5574 call_hxthxc_gen_error ('HXT', 'HXT_39416_GET_HRW_ID', NULL);
5575 --2278400
5576 RETURN 2;
5577 WHEN l_generate_details_error
5578 THEN
5579 IF g_debug
5580 THEN
5581 hr_utility.set_location (l_proc, 490);
5582 END IF;
5583
5584 --fnd_message.set_name ('HXT', 'HXT_39417_PROB_GEN_DET');
5585 o_otm_error := fnd_message.get;
5586 --call_hxthxc_gen_error('HXT', 'HXT_39417_PROB_GEN_DET',NULL); --2278400
5587 RETURN 2;
5588 WHEN l_inc_tim_hr_entry_err
5589 THEN
5590 IF g_debug
5591 THEN
5592 hr_utility.set_location (l_proc, 495);
5593 END IF;
5594
5595 fnd_message.set_name ('HXT', 'HXT_39329_INC_TIM_HR_ENTRIES');
5596 o_otm_error := fnd_message.get;
5597 call_hxthxc_gen_error ('HXT', 'HXT_39329_INC_TIM_HR_ENTRIES', NULL);
5598 --2278400
5599 RETURN 2;
5600 WHEN l_seq_num_error
5601 THEN
5602 IF g_debug
5603 THEN
5604 hr_utility.set_location (l_proc, 500);
5605 END IF;
5606
5607 fnd_message.set_name ('HXT', 'HXT_39418_GET_HRW_SEQ');
5608 o_otm_error := fnd_message.get;
5609 o_oracle_error := SQLERRM;
5610 call_hxthxc_gen_error ('HXT', 'HXT_39418_GET_HRW_SEQ', NULL);
5611 --2278400
5612 RETURN 2;
5613 WHEN l_delete_details_err
5614 THEN
5615 IF g_debug
5616 THEN
5617 hr_utility.set_location (l_proc, 510);
5618 END IF;
5619
5620 fnd_message.set_name ('HXT', 'HXT_39563_ERR_IN_RET_DD');
5621 fnd_message.set_token ('ERR_TEXT', l_otm_error);
5622 o_otm_error := fnd_message.get;
5623 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', o_otm_error);
5624 --2278400
5625 WHEN l_sess_date_err
5626 THEN
5627 IF g_debug
5628 THEN
5629 hr_utility.set_location (l_proc, 520);
5630 END IF;
5631
5632 fnd_message.set_name ('HXT', 'HXT_39556_SESSION_DT_NF');
5633 o_otm_error := fnd_message.get;
5634 o_oracle_error := SQLERRM;
5635 call_hxthxc_gen_error ('HXT', 'HXT_39556_SESSION_DT_NF', NULL);
5636 --2278400
5637 WHEN l_retro_edit_err
5638 THEN
5639 IF g_debug
5640 THEN
5641 hr_utility.set_location (l_proc, 530);
5642 END IF;
5643
5644 o_otm_error := l_otm_error;
5645 o_oracle_error := SQLERRM;
5646 -- Replace message + return added
5647 -- call_hxthxc_gen_error('HXC','HXC_HXT_DEP_VAL_OTMERR',o_otm_error); --2278400
5648 call_hxthxc_gen_error ('HXT',
5649 'HXT_TC_CANNOT_BE_CHANGED_TODAY',
5650 o_otm_error
5651 ); --2278400
5652 RETURN 2;
5653 WHEN OTHERS
5654 THEN
5655 IF g_debug
5656 THEN
5657 hr_utility.set_location (l_proc, 540);
5658 END IF;
5659
5660 fnd_message.set_name ('HXT', 'HXT_39419_SYSERR_RECFUNC');
5661 o_otm_error := fnd_message.get;
5662 o_oracle_error := SQLERRM;
5663 call_hxthxc_gen_error ('HXT', 'HXT_39419_SYSERR_RECFUNC', NULL);
5664 --2278400
5665 RETURN 2;
5666 END record_hours_worked;
5667
5668 /**********************************************************
5669 cost_allocation_entry()
5670 Creates or retrieves cost allocation entries for segments
5671 and business group entered by calling HR function
5672 maintain_cost_keyflex. Returns costing_keyflex_i in
5673 out nocopy parameter o_keyflex_id.
5674 **********************************************************/
5675 PROCEDURE cost_allocation_entry (
5676 i_concat_segments IN VARCHAR2,
5677 i_cost_segment1 IN VARCHAR2,
5678 i_cost_segment2 IN VARCHAR2,
5679 i_cost_segment3 IN VARCHAR2,
5680 i_cost_segment4 IN VARCHAR2,
5681 i_cost_segment5 IN VARCHAR2,
5682 i_cost_segment6 IN VARCHAR2,
5683 i_cost_segment7 IN VARCHAR2,
5684 i_cost_segment8 IN VARCHAR2,
5685 i_cost_segment9 IN VARCHAR2,
5686 i_cost_segment10 IN VARCHAR2,
5687 i_cost_segment11 IN VARCHAR2,
5688 i_cost_segment12 IN VARCHAR2,
5689 i_cost_segment13 IN VARCHAR2,
5690 i_cost_segment14 IN VARCHAR2,
5691 i_cost_segment15 IN VARCHAR2,
5692 i_cost_segment16 IN VARCHAR2,
5693 i_cost_segment17 IN VARCHAR2,
5694 i_cost_segment18 IN VARCHAR2,
5695 i_cost_segment19 IN VARCHAR2,
5696 i_cost_segment20 IN VARCHAR2,
5697 i_cost_segment21 IN VARCHAR2,
5698 i_cost_segment22 IN VARCHAR2,
5699 i_cost_segment23 IN VARCHAR2,
5700 i_cost_segment24 IN VARCHAR2,
5701 i_cost_segment25 IN VARCHAR2,
5702 i_cost_segment26 IN VARCHAR2,
5703 i_cost_segment27 IN VARCHAR2,
5704 i_cost_segment28 IN VARCHAR2,
5705 i_cost_segment29 IN VARCHAR2,
5706 i_cost_segment30 IN VARCHAR2,
5707 i_business_group_id IN NUMBER,
5708 o_ffv_cost_center_id OUT NOCOPY NUMBER,
5709 o_otm_error OUT NOCOPY VARCHAR2,
5710 o_oracle_error OUT NOCOPY VARCHAR2
5711 )
5712 -- p_mode IN VARCHAR2 default 'INSERT')
5713 IS
5714 l_retcode NUMBER;
5715 l_ffv_cost_center_id NUMBER (15);
5716 l_otm_error VARCHAR2 (240);
5717 l_oracle_error VARCHAR2 (512);
5718 cost_alloc_entry_err EXCEPTION;
5719 BEGIN
5720 IF i_cost_segment1 IS NOT NULL
5721 OR i_cost_segment2 IS NOT NULL
5722 OR i_cost_segment3 IS NOT NULL
5723 OR i_cost_segment4 IS NOT NULL
5724 OR i_cost_segment5 IS NOT NULL
5725 OR i_cost_segment6 IS NOT NULL
5726 OR i_cost_segment7 IS NOT NULL
5727 OR i_cost_segment8 IS NOT NULL
5728 OR i_cost_segment9 IS NOT NULL
5729 OR i_cost_segment10 IS NOT NULL
5730 OR i_cost_segment11 IS NOT NULL
5731 OR i_cost_segment12 IS NOT NULL
5732 OR i_cost_segment13 IS NOT NULL
5733 OR i_cost_segment14 IS NOT NULL
5734 OR i_cost_segment15 IS NOT NULL
5735 OR i_cost_segment16 IS NOT NULL
5736 OR i_cost_segment17 IS NOT NULL
5737 OR i_cost_segment18 IS NOT NULL
5738 OR i_cost_segment19 IS NOT NULL
5739 OR i_cost_segment20 IS NOT NULL
5740 OR i_cost_segment21 IS NOT NULL
5741 OR i_cost_segment22 IS NOT NULL
5742 OR i_cost_segment23 IS NOT NULL
5743 OR i_cost_segment24 IS NOT NULL
5744 OR i_cost_segment25 IS NOT NULL
5745 OR i_cost_segment26 IS NOT NULL
5746 OR i_cost_segment27 IS NOT NULL
5747 OR i_cost_segment28 IS NOT NULL
5748 OR i_cost_segment29 IS NOT NULL
5749 OR i_cost_segment30 IS NOT NULL
5750 THEN
5751 l_retcode :=
5752 hxt_util.build_cost_alloc_flex_entry (i_cost_segment1,
5753 i_cost_segment2,
5754 i_cost_segment3,
5755 i_cost_segment4,
5756 i_cost_segment5,
5757 i_cost_segment6,
5758 i_cost_segment7,
5759 i_cost_segment8,
5760 i_cost_segment9,
5761 i_cost_segment10,
5762 i_cost_segment11,
5763 i_cost_segment12,
5764 i_cost_segment13,
5765 i_cost_segment14,
5766 i_cost_segment15,
5767 i_cost_segment16,
5768 i_cost_segment17,
5769 i_cost_segment18,
5770 i_cost_segment19,
5771 i_cost_segment20,
5772 i_cost_segment21,
5773 i_cost_segment22,
5774 i_cost_segment23,
5775 i_cost_segment24,
5776 i_cost_segment25,
5777 i_cost_segment26,
5778 i_cost_segment27,
5779 i_cost_segment28,
5780 i_cost_segment29,
5781 i_cost_segment30,
5782 i_business_group_id,
5783 l_ffv_cost_center_id,
5784 l_otm_error
5785 );
5786
5787 -- p_mode);
5788 IF l_retcode = 1
5789 THEN
5790 RAISE cost_alloc_entry_err;
5791 END IF;
5792
5793 --
5794 o_ffv_cost_center_id := l_ffv_cost_center_id;
5795 END IF;
5796 EXCEPTION
5797 WHEN cost_alloc_entry_err
5798 THEN
5799 o_otm_error := l_otm_error;
5800 o_oracle_error := SQLERRM;
5801 WHEN OTHERS
5802 THEN
5803 fnd_message.set_name ('HXT', 'HXT_39566_ERR_IN_CAE');
5804 l_otm_error := fnd_message.get;
5805 o_otm_error := l_otm_error;
5806 o_oracle_error := SQLERRM;
5807 call_hxthxc_gen_error ('HXT', 'HXT_39566_ERR_IN_CAE', NULL);
5808 --2278400
5809 END cost_allocation_entry;
5810
5811 /**************************************************************
5812 delete_summary_record()
5813 Deletes hxt_sum_hours_worked_f row indicated by i_tim_sum_id.
5814 ***************************************************************/
5815 FUNCTION delete_summary_record (p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE)
5816 RETURN NUMBER
5817 IS
5818 l_proc VARCHAR2 (72);
5819 c_success CONSTANT NUMBER := 0;
5820 c_failure CONSTANT NUMBER := 1;
5821 l_retro_edit_err EXCEPTION;
5822 l_delete_not_allowed EXCEPTION;
5823 l_dt_update_mode VARCHAR2 (20);
5824 l_return_code NUMBER;
5825 l_error_message VARCHAR2 (300);
5826
5827 FUNCTION tim_id (p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE)
5828 RETURN hxt_sum_hours_worked_f.tim_id%TYPE
5829 AS
5830 l_proc VARCHAR2 (72);
5831
5832 CURSOR csr_tim_id (p_sum_id hxt_sum_hours_worked_f.ID%TYPE)
5833 IS
5834 SELECT tim_id
5835 FROM hxt_sum_hours_worked
5836 WHERE ID = p_sum_id;
5837
5838 l_tim_id csr_tim_id%ROWTYPE;
5839 BEGIN
5840 IF g_debug
5841 THEN
5842 l_proc := g_package || 'tim_id';
5843 hr_utility.set_location ('Entering ' || l_proc, 10);
5844 END IF;
5845
5846 OPEN csr_tim_id (p_sum_id);
5847
5848 FETCH csr_tim_id
5849 INTO l_tim_id;
5850
5851 CLOSE csr_tim_id;
5852
5853 IF g_debug
5854 THEN
5855 hr_utility.set_location ( ' returning l_tim_id = '
5856 || l_tim_id.tim_id,
5857 20
5858 );
5859 hr_utility.set_location ('Leaving ' || l_proc, 100);
5860 END IF;
5861
5862 RETURN l_tim_id.tim_id;
5863 END tim_id;
5864
5865 PROCEDURE remove_tc_details (p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE)
5866 AS
5867 l_proc VARCHAR2 (72);
5868 BEGIN
5869 IF g_debug
5870 THEN
5871 l_proc := g_package || 'remove_tc_details';
5872 hr_utility.set_location ('Entering ' || l_proc, 10);
5873 END IF;
5874
5875 DELETE FROM hxt_det_hours_worked_f
5876 WHERE parent_id = p_sum_id;
5877
5878 DELETE FROM hxt_sum_hours_worked_f
5879 WHERE ID = p_sum_id;
5880
5881 IF g_debug
5882 THEN
5883 hr_utility.set_location ('Leaving ' || l_proc, 100);
5884 END IF;
5885 END remove_tc_details;
5886 BEGIN -- Main delete_summary_record
5887 g_debug := hr_utility.debug_enabled;
5888
5889 IF g_debug
5890 THEN
5891 l_proc := g_package || 'delete_summary_record';
5892 hr_utility.set_location ('Entering ' || l_proc, 10);
5893 END IF;
5894
5895 hxt_td_util.retro_restrict_edit (p_tim_id => tim_id
5896 (p_sum_id),
5897 p_session_date => g_sess_date,
5898 o_dt_update_mod => l_dt_update_mode,
5899 o_error_message => l_error_message,
5900 o_return_code => l_return_code
5901 );
5902
5903 IF g_debug
5904 THEN
5905 hr_utility.TRACE (' l_return_code :' || l_return_code);
5906 END IF;
5907
5908 IF (l_dt_update_mode IS NULL)
5909 THEN
5910 RAISE l_retro_edit_err;
5911 ELSIF (l_dt_update_mode = 'UPDATE')
5912 THEN
5913 RAISE l_delete_not_allowed;
5914 ELSE
5915 remove_tc_details (p_sum_id => p_sum_id);
5916 END IF;
5917
5918 IF g_debug
5919 THEN
5920 hr_utility.set_location ('Leaving ' || l_proc, 100);
5921 END IF;
5922
5923 RETURN c_success;
5924 EXCEPTION
5925 WHEN l_retro_edit_err
5926 THEN
5927 IF g_debug
5928 THEN
5929 hr_utility.set_location ('Leaving ' || l_proc, 110);
5930 END IF;
5931
5932 call_hxthxc_gen_error ('HXT',
5933 'HXT_TC_CANNOT_BE_CHANGED_TODAY',
5934 l_error_message
5935 );
5936 RETURN c_failure;
5937 WHEN l_delete_not_allowed
5938 THEN
5939 IF g_debug
5940 THEN
5941 hr_utility.set_location ('Leaving ' || l_proc, 120);
5942 END IF;
5943
5944 call_hxthxc_gen_error ('HXT',
5945 'HXT_TC_CANNOT_BE_DELETED',
5946 l_error_message
5947 );
5948 RETURN c_failure;
5949 WHEN OTHERS
5950 THEN
5951 IF g_debug
5952 THEN
5953 hr_utility.set_location ('Leaving ' || l_proc, 130);
5954 END IF;
5955
5956 RETURN c_failure;
5957 END delete_summary_record;
5958
5959 --BEGIN SIR334
5960 ---------------------------------------------------------------------------
5961 PROCEDURE delete_details (
5962 p_tim_id IN NUMBER,
5963 p_dt_update_mode IN VARCHAR2,
5964 p_effective_start_date IN DATE,
5965 o_error_message OUT NOCOPY NUMBER
5966 )
5967 IS
5968 BEGIN
5969 /* Initialize globals */
5970 g_sysdate := TRUNC (SYSDATE);
5971 g_sysdatetime := SYSDATE;
5972 g_user_id := fnd_global.user_id;
5973 g_login_id := fnd_global.login_id;
5974
5975 IF NVL (p_dt_update_mode, 'CORRECTION') = 'CORRECTION'
5976 THEN
5977 -- Delete
5978 DELETE FROM hxt_det_hours_worked_f
5979 WHERE ROWID IN (SELECT ROWID
5980 FROM hxt_det_hours_worked
5981 WHERE tim_id = p_tim_id);
5982
5983 DELETE FROM hxt_errors_f
5984 WHERE ROWID IN (
5985 SELECT ROWID
5986 FROM hxt_errors
5987 WHERE tim_id = p_tim_id
5988 AND hrw_id IS NOT NULL
5989 --hxt11ipatch don't delete timecard
5990 ); -- level errors
5991 ELSE
5992 -- Expire
5993 UPDATE hxt_det_hours_worked_f
5994 SET effective_end_date = p_effective_start_date - 1
5995 WHERE ROWID IN (SELECT ROWID
5996 FROM hxt_det_hours_worked
5997 WHERE tim_id = p_tim_id);
5998
5999 UPDATE hxt_errors_f
6000 SET effective_end_date = p_effective_start_date - 1
6001 WHERE ROWID IN (SELECT ROWID
6002 FROM hxt_errors
6003 WHERE tim_id = p_tim_id AND hrw_id IS NOT NULL
6004 --hxt11ipatch don't expire timecard
6005 ); -- level errors
6006 END IF; -- Update or Correction
6007 EXCEPTION
6008 WHEN OTHERS
6009 THEN
6010 fnd_message.set_name ('HXT', 'HXT_39567_ERR_IN_DD');
6011 fnd_message.set_token ('ERR_TEXT', SQLERRM);
6012 o_error_message := fnd_message.get;
6013 call_hxthxc_gen_error ('HXC',
6014 'HXC_HXT_DEP_VAL_OTMERR',
6015 o_error_message
6016 ); --2278400
6017 END; -- delete details
6018
6019 ---------------------------------------------------------------------------
6020 --END SIR334
6021
6022 --BEGIN SIR236
6023 /**************************************************************
6024 Adjust_timings()
6025
6026 ***************************************************************/
6027 PROCEDURE adjust_timings (
6028 p_timecard_source IN VARCHAR2,
6029 p_assignment_id IN NUMBER,
6030 p_person_id IN NUMBER,
6031 p_date_worked IN DATE,
6032 p_tim_id IN NUMBER,
6033 p_hours_id IN NUMBER,
6034 p_earn_pol_id IN NUMBER,
6035 p_time_in IN OUT NOCOPY DATE,
6036 p_time_out IN OUT NOCOPY DATE,
6037 p_hours IN OUT NOCOPY NUMBER,
6038 p_code OUT NOCOPY NUMBER,
6039 p_error OUT NOCOPY VARCHAR2,
6040 p_org_in IN DATE DEFAULT NULL,
6041 p_org_out IN DATE DEFAULT NULL,
6042 p_actual_time_in IN OUT NOCOPY DATE,
6043 p_actual_time_out IN OUT NOCOPY DATE
6044 )
6045 IS
6046 l_tc_intvl hxt_earning_policies.min_tcard_intvl%TYPE;
6047 l_round_up hxt_earning_policies.round_up%TYPE;
6048 l_rot_id hxt_rotation_plans.ID%TYPE;
6049 l_work_id hxt_rotation_schedules.tws_id%TYPE;
6050 l_osp_id NUMBER;
6051 l_sdf_id NUMBER;
6052 l_standard_start NUMBER;
6053 l_standard_stop NUMBER;
6054 l_early_start NUMBER;
6055 l_late_stop NUMBER;
6056 l_hours NUMBER;
6057 l_error NUMBER;
6058 l_ovlp_error VARCHAR2 (1);
6059 l_in_time_std BOOLEAN := FALSE;
6060 l_out_time_std BOOLEAN := FALSE;
6061 l_time_in_date DATE;
6062 l_early_date DATE;
6063 l_std_date_start DATE;
6064 l_time_out_date DATE;
6065 l_late_date DATE;
6066 l_std_date_stop DATE;
6067 l_time_in_actual DATE;
6068 l_time_out_actual DATE;
6069 l_valid_entry BOOLEAN;
6070 l_proc VARCHAR2 (100);
6071 BEGIN
6072 g_debug := hr_utility.debug_enabled;
6073
6074 IF g_debug
6075 THEN
6076 l_proc := 'hxt_time_collection.ADJUST_TIMINGS';
6077 hr_utility.TRACE ('hxt_time_collection.ADJUST_TIMINGS BEGIN');
6078 hr_utility.set_location (l_proc, 10);
6079 hr_utility.TRACE ( 'p_time_in:'
6080 || TO_CHAR (p_time_in, 'dd-mon-yyyy hh24:mi:ss')
6081 );
6082 hr_utility.TRACE ( 'p_time_out:'
6083 || TO_CHAR (p_time_out, 'dd-mon-yyyy hh24:mi:ss')
6084 );
6085 hr_utility.TRACE ( 'p_actual_time_in:'
6086 || TO_CHAR (p_actual_time_in,
6087 'dd-mon-yyyy hh24:mi:ss'
6088 )
6089 );
6090 hr_utility.TRACE ( 'p_actual_time_out:'
6091 || TO_CHAR (p_actual_time_out,
6092 'dd-mon-yyyy hh24:mi:ss'
6093 )
6094 );
6095 hr_utility.TRACE ( 'p_org_in:'
6096 || TO_CHAR (p_org_in, 'dd-mon-yyyy hh24:mi:ss')
6097 );
6098 hr_utility.TRACE ( 'p_org_out:'
6099 || TO_CHAR (p_org_out, 'dd-mon-yyyy hh24:mi:ss')
6100 );
6101 END IF;
6102
6103 l_time_in_actual := p_actual_time_in;
6104 l_time_out_actual := p_actual_time_out;
6105
6106 --get rotation plan id for the assignment
6107 BEGIN
6108 SELECT hxt_rotation_plan
6109 INTO l_rot_id
6110 FROM hxt_per_aei_ddf_v aeiv, per_assignments_f asm
6111 WHERE asm.assignment_id = p_assignment_id
6112 AND asm.assignment_id = aeiv.assignment_id
6113 AND p_date_worked BETWEEN asm.effective_start_date
6114 AND asm.effective_end_date
6115 AND p_date_worked BETWEEN aeiv.effective_start_date
6116 AND aeiv.effective_end_date;
6117
6118 IF g_debug
6119 THEN
6120 hr_utility.set_location (l_proc, 20);
6121 hr_utility.TRACE ('l_rot_id :' || l_rot_id);
6122 END IF;
6123 EXCEPTION
6124 WHEN NO_DATA_FOUND
6125 THEN
6126 IF g_debug
6127 THEN
6128 hr_utility.set_location (l_proc, 30);
6129 END IF;
6130
6131 l_rot_id := NULL;
6132 END;
6133
6134 --
6135 IF l_rot_id IS NOT NULL
6136 THEN
6137 IF g_debug
6138 THEN
6139 hr_utility.set_location (l_proc, 40);
6140 END IF;
6141
6142 hxt_util.get_shift_info (p_date_worked,
6143 l_work_id,
6144 l_rot_id,
6145 l_osp_id,
6146 l_sdf_id,
6147 l_standard_start,
6148 l_standard_stop,
6149 l_early_start,
6150 l_late_stop,
6151 l_hours,
6152 l_error
6153 );
6154
6155 IF g_debug
6156 THEN
6157 hr_utility.set_location (l_proc, 50);
6158 hr_utility.TRACE ('l_error :' || l_error);
6159 END IF;
6160
6161 IF (l_error = 0)
6162 THEN
6163 IF g_debug
6164 THEN
6165 hr_utility.set_location (l_proc, 60);
6166 END IF;
6167
6168 -- No error, so get the early start,etc timings...
6169 -- convert time_in, standard_start, and early_start to a date, plus
6170 -- embed date_worked into all three.
6171 IF p_time_in IS NOT NULL
6172 THEN
6173 IF (l_early_start IS NOT NULL AND l_standard_start IS NOT NULL
6174 )
6175 THEN
6176 IF g_debug
6177 THEN
6178 hr_utility.set_location (l_proc, 70);
6179 END IF;
6180
6181 l_time_in_date := p_time_in;
6182 /* ROUND (
6183 TRUNC (p_date_worked, 'DD')
6184 + ( p_time_in
6185 - TRUNC (p_time_in, 'DD')
6186 ),
6187 'MI'
6188 );*/
6189 l_early_date :=
6190 TRUNC (p_date_worked)
6191 + hxt_util.time_to_hours (l_early_start) / 24;
6192 /* TO_DATE (
6193 TO_CHAR (p_date_worked, 'DD-MM-YYYY ')
6194 || TO_CHAR (l_early_start, '0009'),
6195 'DD-MM-YYYY HH24MI'
6196 );*/
6197 l_std_date_start :=
6198 TRUNC (p_date_worked)
6199 + hxt_util.time_to_hours (l_standard_start) / 24;
6200
6201 /* TO_DATE (
6202 TO_CHAR (
6203 p_date_worked,
6204 'DD-MM-YYYY '
6205 )
6206 || TO_CHAR (l_standard_start, '0009'),
6207 'DD-MM-YYYY HH24MI'
6208 );*/
6209 IF g_debug
6210 THEN
6211 hr_utility.TRACE ( 'l_time_in_date :'
6212 || TO_CHAR (l_time_in_date,
6213 'DD-MON-YYYY HH24:MI:SS'
6214 )
6215 );
6216 hr_utility.TRACE ( 'l_early_date :'
6217 || TO_CHAR (l_early_date,
6218 'DD-MON-YYYY HH24:MI:SS'
6219 )
6220 );
6221 hr_utility.TRACE ( 'l_std_date_start:'
6222 || TO_CHAR (l_std_date_start,
6223 'DD-MON-YYYY HH24:MI:SS'
6224 )
6225 );
6226 END IF;
6227
6228 IF l_time_in_date BETWEEN l_early_date AND l_std_date_start
6229 THEN
6230 IF g_debug
6231 THEN
6232 hr_utility.set_location (l_proc, 80);
6233 END IF;
6234
6235 p_time_in := l_std_date_start;
6236 l_in_time_std := TRUE;
6237
6238 IF g_debug
6239 THEN
6240 hr_utility.TRACE ( 'p_time_in :'
6241 || TO_CHAR (p_time_in,
6242 'DD-MON-YYYY HH24:MI:SS'
6243 )
6244 );
6245 END IF;
6246 END IF;
6247
6248 IF g_debug
6249 THEN
6250 hr_utility.set_location (l_proc, 90);
6251 END IF;
6252 END IF; -- ( l_early_start is not null AND....)
6253 END IF;
6254
6255 IF p_time_out IS NOT NULL
6256 THEN
6257 -- For Time Store
6258 IF ( ( l_late_stop IS NOT NULL
6259 AND l_standard_stop IS NOT NULL
6260 AND p_timecard_source = 'Time Store'
6261 )
6262 -- For PUI
6263 OR (p_timecard_source <> 'Time Store')
6264 )
6265 THEN
6266 IF g_debug
6267 THEN
6268 hr_utility.set_location (l_proc, 100);
6269 END IF;
6270
6271 l_time_out_date := p_time_out;
6272
6273 /* ROUND (
6274 TRUNC (p_date_worked, 'DD')
6275 + ( p_time_out
6276 - TRUNC (p_time_out, 'DD')
6277 ),
6278 'MI'
6279 );*/
6280 IF l_late_stop IS NOT NULL
6281 THEN
6282 l_late_date :=
6283 TRUNC (p_date_worked)
6284 + hxt_util.time_to_hours (l_late_stop) / 24;
6285
6286 IF g_debug
6287 THEN
6288 hr_utility.TRACE ( 'l_late_date :'
6289 || TO_CHAR (l_late_date,
6290 'DD-MON-YYYY HH24:MI:SS'
6291 )
6292 );
6293 END IF;
6294 END IF;
6295
6296 /* TO_DATE (
6297 TO_CHAR (p_date_worked, 'DD-MM-YYYY ')
6298 || TO_CHAR (l_late_stop, '0009'),
6299 'DD-MM-YYYY HH24MI'
6300 );*/
6301 l_std_date_stop :=
6302 TRUNC (p_date_worked)
6303 + hxt_util.time_to_hours (l_standard_stop) / 24;
6304
6305 /* TO_DATE (
6306 TO_CHAR (p_date_worked, 'DD-MM-YYYY ')
6307 || TO_CHAR (l_standard_stop, '0009'),
6308 'DD-MM-YYYY HH24MI'
6309 );*/
6310 IF g_debug
6311 THEN
6312 hr_utility.TRACE ( 'l_time_out_date :'
6313 || TO_CHAR (l_time_out_date,
6314 'DD-MON-YYYY HH24:MI:SS'
6315 )
6316 );
6317 hr_utility.TRACE ( 'l_std_date_stop :'
6318 || TO_CHAR (l_std_date_stop,
6319 'DD-MON-YYYY HH24:MI:SS'
6320 )
6321 );
6322 END IF;
6323
6324 IF p_timecard_source <> 'Time Store' and p_timecard_source <> 'Time Clock'
6325 THEN
6326 -- i.e., adjust_timings being called from the PUI then
6327 -- Check if Time_out goes over midnight
6328 IF (l_time_out_date - TRUNC (l_time_out_date, 'DD')
6329 ) < (p_time_in - TRUNC (p_time_in, 'DD'))
6330 OR ( ( l_late_date IS NOT NULL
6331 AND l_std_date_stop IS NOT NULL
6332 )
6333 AND ( l_time_out_date BETWEEN l_std_date_stop
6334 AND l_late_date
6335 AND ( l_std_date_stop
6336 - TRUNC (l_std_date_stop, 'DD')
6337 ) <
6338 (p_time_in - TRUNC (p_time_in, 'DD')
6339 )
6340 )
6341 )
6342 THEN
6343 IF g_debug
6344 THEN
6345 hr_utility.set_location (l_proc, 105);
6346 END IF;
6347
6348 l_time_out_date := l_time_out_date + 1;
6349 END IF;
6350
6351 IF l_late_stop IS NOT NULL
6352 THEN
6353 l_late_date :=
6354 TRUNC (l_time_out_date)
6355 + hxt_util.time_to_hours (l_late_stop) / 24;
6356
6357 IF g_debug
6358 THEN
6359 hr_utility.TRACE
6360 ( 'l_late_date :'
6361 || TO_CHAR
6362 (l_late_date,
6363 'DD-MON-YYYY HH24:MI:SS'
6364 )
6365 );
6366 END IF;
6367 END IF;
6368
6369 l_std_date_stop :=
6370 TRUNC (l_time_out_date)
6371 + hxt_util.time_to_hours (l_standard_stop) / 24;
6372
6373 IF g_debug
6374 THEN
6375 hr_utility.TRACE ( 'l_time_out_date :'
6376 || TO_CHAR (l_time_out_date,
6377 'DD-MON-YYYY HH24:MI:SS'
6378 )
6379 );
6380 hr_utility.TRACE ( 'l_std_date_stop :'
6381 || TO_CHAR (l_std_date_stop,
6382 'DD-MON-YYYY HH24:MI:SS'
6383 )
6384 );
6385 END IF;
6386
6387 IF l_time_out_date BETWEEN l_std_date_stop AND l_late_date
6388 THEN
6389 hr_utility.set_location (l_proc, 110);
6390 p_time_out := l_std_date_stop;
6391 l_out_time_std := TRUE;
6392
6393 IF g_debug
6394 THEN
6395 hr_utility.TRACE
6396 ( 'p_time_out :'
6397 || TO_CHAR
6398 (p_time_out,
6399 'DD-MON-YYYY HH24:MI:SS'
6400 )
6401 );
6402 END IF;
6403 ELSE
6404 IF g_debug
6405 THEN
6406 hr_utility.set_location (l_proc, 115);
6407 END IF;
6408
6409 p_time_out := l_time_out_date;
6410
6411 IF g_debug
6412 THEN
6413 hr_utility.TRACE
6414 ( 'p_time_out :'
6415 || TO_CHAR
6416 (p_time_out,
6417 'DD-MON-YYYY HH24:MI:SS'
6418 )
6419 );
6420 END IF;
6421 END IF;
6422 ELSE -- P_timecard_source = 'Time Store'
6423 IF g_debug
6424 THEN
6425 hr_utility.set_location (l_proc, 116);
6426 END IF;
6427
6428 l_late_date :=
6429 TRUNC (l_time_out_date)
6430 + hxt_util.time_to_hours (l_late_stop) / 24;
6431 l_std_date_stop :=
6432 TRUNC (l_time_out_date)
6433 + hxt_util.time_to_hours (l_standard_stop) / 24;
6434
6435 IF g_debug
6436 THEN
6437 hr_utility.TRACE ( 'l_time_out_date :'
6438 || TO_CHAR (l_time_out_date,
6439 'DD-MON-YYYY HH24:MI:SS'
6440 )
6441 );
6442 hr_utility.TRACE ( 'l_late_date :'
6443 || TO_CHAR (l_late_date,
6444 'DD-MON-YYYY HH24:MI:SS'
6445 )
6446 );
6447 hr_utility.TRACE ( 'l_std_date_stop :'
6448 || TO_CHAR (l_std_date_stop,
6449 'DD-MON-YYYY HH24:MI:SS'
6450 )
6451 );
6452 END IF;
6453
6454 IF l_time_out_date BETWEEN l_std_date_stop AND l_late_date
6455 THEN
6456 IF g_debug
6457 THEN
6458 hr_utility.set_location (l_proc, 120);
6459 END IF;
6460
6461 p_time_out := l_std_date_stop;
6462 l_out_time_std := TRUE;
6463
6464 IF g_debug
6465 THEN
6466 hr_utility.TRACE
6467 ( 'p_time_out :'
6468 || TO_CHAR
6469 (p_time_out,
6470 'DD-MON-YYYY HH24:MI:SS'
6471 )
6472 );
6473 hr_utility.TRACE ( 'p_org_out :'
6474 || TO_CHAR
6475 (p_org_out,
6476 'DD-MON-YYYY HH24:MI:SS'
6477 )
6478 );
6479 END IF;
6480 END IF;
6481 END IF; -- P_timecard_source <> 'Time Store'
6482
6483 IF g_debug
6484 THEN
6485 hr_utility.set_location (l_proc, 125);
6486 END IF;
6487 END IF; -- ( l_late_stop is not null AND....)
6488 END IF;
6489
6490 IF g_debug
6491 THEN
6492 hr_utility.set_location (l_proc, 130);
6493 END IF;
6494 END IF; -- (l_error = 0)
6495
6496 IF g_debug
6497 THEN
6498 hr_utility.set_location (l_proc, 140);
6499 END IF;
6500 -- END IF; -- (rot id is not NULL)
6501 ELSE -- l_rot_id IS NULL
6502 IF g_debug
6503 THEN
6504 hr_utility.set_location (l_proc, 141);
6505 END IF;
6506
6507 IF p_time_out IS NOT NULL
6508 THEN
6509 IF g_debug
6510 THEN
6511 hr_utility.set_location (l_proc, 142);
6512 END IF;
6513
6514 l_time_out_date := p_time_out;
6515
6516 IF p_timecard_source <> 'Time Store' and p_timecard_source <> 'Time Clock'
6517 THEN
6518 IF g_debug
6519 THEN
6520 hr_utility.set_location (l_proc, 143);
6521 END IF;
6522
6523 -- i.e., adjust_timings being called from the PUI then
6524 -- Check if Time_out goes over midnight
6525 IF (l_time_out_date - TRUNC (l_time_out_date, 'DD')) <
6526 (p_time_in - TRUNC (p_time_in, 'DD')
6527 )
6528 THEN
6529 IF g_debug
6530 THEN
6531 hr_utility.set_location (l_proc, 144);
6532 END IF;
6533
6534 l_time_out_date := l_time_out_date + 1;
6535 END IF;
6536
6537 IF g_debug
6538 THEN
6539 hr_utility.set_location (l_proc, 145);
6540 END IF;
6541 END IF; -- P_timecard_source <> 'Time Store'
6542
6543 IF g_debug
6544 THEN
6545 hr_utility.set_location (l_proc, 146);
6546 END IF;
6547
6548 p_time_out := l_time_out_date;
6549 END IF;
6550
6551 IF g_debug
6552 THEN
6553 hr_utility.set_location (l_proc, 147);
6554 END IF;
6555 END IF; -- (rot id is NULL)
6556
6557 --Round the timings according to the earn policy, if not std....
6558 IF (l_in_time_std <> TRUE OR l_out_time_std <> TRUE)
6559 THEN
6560 IF g_debug
6561 THEN
6562 hr_utility.set_location (l_proc, 150);
6563 END IF;
6564
6565 SELECT min_tcard_intvl, round_up
6566 INTO l_tc_intvl, l_round_up
6567 FROM hxt_earning_policies
6568 WHERE ID = p_earn_pol_id;
6569
6570 IF g_debug
6571 THEN
6572 hr_utility.TRACE ('l_tc_intvl :' || l_tc_intvl);
6573 hr_utility.TRACE ('l_round_up :' || l_round_up);
6574 END IF;
6575
6576 IF (l_in_time_std <> TRUE)
6577 THEN
6578 IF g_debug
6579 THEN
6580 hr_utility.set_location (l_proc, 160);
6581 END IF;
6582
6583 p_time_in := round_time (p_time_in, l_tc_intvl, l_round_up);
6584 END IF;
6585
6586 IF (l_out_time_std <> TRUE)
6587 THEN
6588 IF g_debug
6589 THEN
6590 hr_utility.set_location (l_proc, 165);
6591 END IF;
6592
6593 p_time_out := round_time (p_time_out, l_tc_intvl, l_round_up);
6594 END IF;
6595
6596 IF g_debug
6597 THEN
6598 hr_utility.set_location (l_proc, 170);
6599 END IF;
6600 END IF;
6601
6602 IF p_timecard_source <> 'Time Store'
6603 THEN
6604 IF p_org_in IS NOT NULL
6605 THEN
6606 IF g_debug
6607 THEN
6608 hr_utility.set_location (l_proc, 175);
6609 END IF;
6610
6611 p_actual_time_in := p_org_in;
6612
6613 IF g_debug
6614 THEN
6615 hr_utility.TRACE ( 'p_actual_time_in :'
6616 || TO_CHAR (p_actual_time_in,
6617 'DD-MON-YYYY HH24:MI:SS'
6618 )
6619 );
6620 END IF;
6621 END IF;
6622
6623 IF g_debug
6624 THEN
6625 hr_utility.TRACE ( 'p_org_out :'
6626 || TO_CHAR (p_org_out, 'DD-MON-YYYY HH24:MI:SS')
6627 );
6628 END IF;
6629
6630 IF p_org_out IS NOT NULL
6631 THEN
6632 IF g_debug
6633 THEN
6634 hr_utility.set_location (l_proc, 180);
6635 END IF;
6636
6637 p_actual_time_out := p_org_out;
6638
6639 IF g_debug
6640 THEN
6641 hr_utility.TRACE ( 'p_actual_time_out :'
6642 || TO_CHAR (p_actual_time_out,
6643 'DD-MON-YYYY HH24:MI:SS'
6644 )
6645 );
6646 END IF;
6647 END IF;
6648 END IF;
6649
6650 -- Check that time does not fall within another time entry
6651 --Bug 2770487 Sonarasi 04-Apr-2003
6652 --The parameters of check_time_overlap have been changed. Previously this
6653 --function used to be called two times once for IN time and once for OUT time.
6654 --Now both IN time and OUT time are being passed and a common error is being
6655 --raised instead of two errors.
6656 --Bug 2770487 Sonarasi Over
6657 IF g_debug
6658 THEN
6659 hr_utility.set_location (l_proc, 190);
6660 END IF;
6661
6662 IF p_timecard_source <> 'Time Store'
6663 THEN
6664 IF g_debug
6665 THEN
6666 hr_utility.set_location (l_proc, 191);
6667 END IF;
6668
6669 l_ovlp_error :=
6670 check_time_overlap (p_date_worked,
6671 p_time_in,
6672 p_time_out,
6673 p_hours_id,
6674 p_tim_id
6675 );
6676
6677 IF g_debug
6678 THEN
6679 hr_utility.set_location (l_proc, 200);
6680 hr_utility.TRACE ('l_ovlp_error :' || l_ovlp_error);
6681 END IF;
6682
6683 -- Raise exception if found
6684 IF l_ovlp_error = 'E'
6685 THEN
6686 IF g_debug
6687 THEN
6688 hr_utility.set_location (l_proc, 210);
6689 END IF;
6690
6691 fnd_message.set_name ('HXT', 'HXT_39598_IN_OUT_TIME_OVERLAP');
6692 fnd_message.set_token ('TIME_IN', TO_CHAR (p_time_in, 'HH24MI'));
6693 fnd_message.set_token ('TIME_OUT', TO_CHAR (p_time_out, 'HH24MI'));
6694 fnd_message.set_token ('DT_WRK',
6695 TO_CHAR (p_date_worked, 'DD-MON-YYYY')
6696 );
6697 fnd_message.set_token ('TIM_ID', TO_CHAR (p_tim_id));
6698 fnd_message.set_token ('SUM_ID', TO_CHAR (p_hours_id));
6699 p_error := fnd_message.get;
6700 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', p_error);
6701 --2278400
6702 p_code := 1;
6703 RETURN;
6704 END IF;
6705 END IF;
6706
6707 IF g_debug
6708 THEN
6709 hr_utility.TRACE ( 'p_time_in:'
6710 || TO_CHAR (p_time_in, 'DD-MON-YYYY HH24:MI:SS')
6711 );
6712 hr_utility.TRACE ( 'p_time_out:'
6713 || TO_CHAR (p_time_out, 'DD-MON-YYYY HH24:MI:SS')
6714 );
6715 hr_utility.TRACE ( 'p_actual_time_in:'
6716 || TO_CHAR (p_actual_time_in,
6717 'DD-MON-YYYY HH24:MI:SS'
6718 )
6719 );
6720 hr_utility.TRACE ( 'p_actual_time_out:'
6721 || TO_CHAR (p_actual_time_out,
6722 'DD-MON-YYYY HH24:MI:SS'
6723 )
6724 );
6725 hr_utility.TRACE ( 'l_time_in_actual:'
6726 || TO_CHAR (l_time_in_actual,
6727 'DD-MON-YYYY HH24:MI:SS'
6728 )
6729 );
6730 hr_utility.TRACE ( 'l_time_out_actual:'
6731 || TO_CHAR (l_time_out_actual,
6732 'DD-MON-YYYY HH24:MI:SS'
6733 )
6734 );
6735 END IF;
6736
6737 IF p_timecard_source = 'Time Store'
6738 THEN
6739 l_valid_entry :=
6740 hxt_util.is_valid_time_entry
6741 (l_time_in_actual, -- p_actual_time_in,
6742 p_time_in,
6743 l_time_out_actual, -- p_actual_time_out,
6744 p_time_out
6745 );
6746 END IF;
6747
6748 IF l_valid_entry = FALSE
6749 THEN
6750 IF g_debug
6751 THEN
6752 hr_utility.set_location (l_proc, 220);
6753 END IF;
6754
6755 fnd_message.set_name ('HXT', 'HXT_39600_INVALID_ENTRIES');
6756 p_error := fnd_message.get;
6757 call_hxthxc_gen_error ('HXC', 'HXC_HXT_DEP_VAL_OTMERR', p_error);
6758 --2278400
6759 p_code := 1;
6760 RETURN;
6761 END IF;
6762
6763 IF g_debug
6764 THEN
6765 hr_utility.set_location (l_proc, 250);
6766 END IF;
6767
6768 p_code := 0;
6769
6770 IF g_debug
6771 THEN
6772 hr_utility.TRACE ('hxt_time_collection.ADJUST_TIMINGS END');
6773 END IF;
6774 END adjust_timings;
6775
6776 /**************************************************************
6777 round_time() --
6778
6779 ***************************************************************/
6780 FUNCTION round_time (p_time DATE, p_interval NUMBER, p_round_up NUMBER)
6781 RETURN DATE
6782 IS
6783 l_min NUMBER;
6784 l_mod NUMBER;
6785 -- Modification Log
6786 -- 02/13/96 PJA Round number of minutes past midnite.
6787 --
6788 BEGIN
6789 -- Get number of minutes past midnite
6790 l_min := ROUND ((p_time - TRUNC (p_time, 'DAY')) * (24 * 60));
6791 -- Get number of minutes past interval
6792 l_mod := MOD (l_min, p_interval);
6793
6794 -- Apply interval rules to number of minutes (if remainder is less than round value,
6795 -- deduct the remainder - otherwise, deduct the remainder then add the interval)
6796 IF (l_mod - p_round_up) < 0
6797 THEN
6798 l_min := l_min - l_mod;
6799 ELSE
6800 l_min := l_min - l_mod + p_interval;
6801 END IF;
6802
6803 -- Return new time (add minutes converted to date to date at midnite)
6804 RETURN TRUNC (p_time, 'DAY') + (l_min / 60 / 24);
6805 END round_time;
6806
6807 ------------------------------------------------------------
6808 FUNCTION check_time_overlap (
6809 p_date DATE,
6810 p_time_in DATE,
6811 p_time_out DATE,
6812 p_id NUMBER,
6813 p_tim_id NUMBER
6814 )
6815 RETURN VARCHAR2
6816 IS
6817 -- Description:
6818 -- Returns 'E'rror if time passed falls within in and out
6819 -- times on other summary lines.
6820
6821 -- Parameters:
6822 -- p_date - date worked
6823 -- p_time_in - in time being evaluated
6824 -- p_time_out - out time being evaluated
6825 -- p_id - hour worked ID
6826 -- p_tim_id - timecard ID
6827
6828 --Bug 2770487 Sonarasi 04-Apr-2003
6829 --The parameters for the function check_time_overlap have been changed.
6830 --The p_type and p_time parameters have been removed. Instead p_time_in and p_time_out
6831 --are passed to the function. This is because both in time and out time are necessary
6832 --to detect overlap.
6833 --Bug 2770487 Sonarasi Over
6834
6835 -- Modification Log:
6836 -- 12/28/95 PJA Created
6837
6838 -- Check that time does not fall within any other time block on
6839 -- this timesheet.
6840
6841 -- Define cursor - OK for out-time to be same as another in-time
6842 CURSOR c
6843 IS
6844 SELECT 'E'
6845 FROM hxt_sum_hours_worked --C421
6846 WHERE (p_time_in < time_out AND p_time_out > time_in)
6847 --C421 AND parent_id = 0
6848 AND date_worked = p_date
6849 AND ID <> NVL (p_id, 0)
6850 AND tim_id = p_tim_id;
6851
6852 l_error VARCHAR2 (1) := NULL;
6853 l_proc VARCHAR2 (100);
6854 BEGIN
6855 IF g_debug
6856 THEN
6857 l_proc := 'hxt_time_collection.Check_Time_Overlap';
6858 hr_utility.set_location (l_proc, 10);
6859 END IF;
6860
6861 --Check if overlap found
6862 --begin SPR C245 - have to check for null or this process will hang,
6863 --causing need to bounce database.
6864 IF p_time_in IS NOT NULL AND p_time_out IS NOT NULL
6865 THEN
6866 IF g_debug
6867 THEN
6868 hr_utility.set_location (l_proc, 20);
6869 END IF;
6870
6871 --end SPR C245
6872 OPEN c;
6873
6874 FETCH c
6875 INTO l_error;
6876
6877 IF g_debug
6878 THEN
6879 hr_utility.TRACE ('l_error :' || l_error);
6880 END IF;
6881
6882 CLOSE c;
6883
6884 RETURN l_error;
6885 --begin SPR C245
6886 ELSE
6887 IF g_debug
6888 THEN
6889 hr_utility.set_location (l_proc, 30);
6890 END IF;
6891
6892 RETURN ('');
6893 END IF;
6894
6895 --end SPR C245
6896 IF g_debug
6897 THEN
6898 hr_utility.set_location (l_proc, 40);
6899 END IF;
6900 END check_time_overlap;
6901
6902 --------------------------------------------------------------------------------
6903 FUNCTION reset_hours (p_in IN DATE, p_out IN DATE)
6904 RETURN NUMBER
6905 IS
6906 -- Reset input hours if they don't match times
6907 l_diff NUMBER;
6908 BEGIN
6909 -- Get number of hours between times
6910 l_diff := (p_out - p_in) * 24;
6911
6912 -- begin SPR C260
6913 -- Check if time out after midnite
6914 IF l_diff < 0
6915 THEN
6916 l_diff := l_diff + 24;
6917 END IF;
6918
6919 RETURN (l_diff);
6920 END reset_hours;
6921 --END SIR236
6922 END hxt_time_collection;