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