1 PACKAGE BODY hxt_time_summary AS
2 /* $Header: hxttsum.pkb 120.4.12000000.2 2007/03/13 11:42:02 nissharm noship $ */
3
4 --Global variables for package
5 --Used for parameters received that are not changed
6 g_debug boolean := hr_utility.debug_enabled;
7 g_ep_id NUMBER;
8 g_ep_type HXT_EARNING_POLICIES.FCL_EARN_TYPE%TYPE;
9 g_egt_id NUMBER;
10 g_sdp_id NUMBER;
11 g_hdp_id NUMBER;
12 g_hol_id NUMBER;
13 g_pep_id NUMBER;
14 g_pip_id NUMBER;
15 g_sdovr_id NUMBER;
16 g_osp_id NUMBER;
17 g_hol_yn VARCHAR2(1);
18 g_person_id NUMBER;
19 g_ID NUMBER;
20 g_EFFECTIVE_START_DATE DATE;
21 g_EFFECTIVE_END_DATE DATE;
22 g_ROWID ROWID;
23 g_PARENT_ID NUMBER;
24 g_TIM_ID NUMBER;
25 g_DATE_WORKED DATE;
26 g_ASSIGNMENT_ID NUMBER;
27 g_HOURS NUMBER;
28 g_TIME_IN DATE;
29 g_TIME_OUT DATE;
30 g_ELEMENT_TYPE_ID NUMBER;
31 g_FCL_EARN_REASON_CODE HXT_SUM_HOURS_WORKED.FCL_EARN_REASON_CODE%TYPE;--C421
32 g_FFV_COST_CENTER_ID NUMBER;
33 g_FFV_LABOR_ACCOUNT_ID NUMBER;
34 g_TAS_ID NUMBER;
35 g_LOCATION_ID NUMBER;
36 g_SHT_ID NUMBER;
37 g_HRW_COMMENT HXT_SUM_HOURS_WORKED.HRW_COMMENT%TYPE;--C421
38 g_FFV_RATE_CODE_ID NUMBER;
39 g_RATE_MULTIPLE NUMBER;
40 g_HOURLY_RATE NUMBER;
41 g_AMOUNT NUMBER;
42 g_FCL_TAX_RULE_CODE HXT_SUM_HOURS_WORKED.FCL_TAX_RULE_CODE%TYPE;--C421
43 g_SEPARATE_CHECK_FLAG HXT_SUM_HOURS_WORKED.SEPARATE_CHECK_FLAG%TYPE;--C421
44 g_SEQNO NUMBER;
45 g_CREATED_BY NUMBER;
46 g_CREATION_DATE DATE;
47 g_LAST_UPDATED_BY NUMBER;
48 g_LAST_UPDATE_DATE DATE;
49 g_LAST_UPDATE_LOGIN NUMBER;
50 g_EARLY_START NUMBER;
51 g_LATE_STOP NUMBER;
52 g_STANDARD_START NUMBER;
53 g_STANDARD_STOP NUMBER;
54 g_PROJECT_ID NUMBER;
55 g_JOB_ID hxt_SUM_HOURS_WORKED.JOB_ID%TYPE;
56 g_PAY_STATUS CHAR(1);
57 g_PA_STATUS CHAR(1);
58 g_PERIOD_START_DATE DATE;
59 g_RETRO_BATCH_ID NUMBER;
60 g_DT_UPDATE_MODE VARCHAR2(30);
61 --g_GROUP_ID NUMBER;
62 g_include_yn VARCHAR2(1);
63 g_start_day_of_week VARCHAR2(30);
64
65 g_CALL_ADJUST_ABS VARCHAR2(1);
66
67 --
68 --Flags for checking which shift diff premium gets paid for a segment chunk
69 --
70 g_sdf_rule_completed VARCHAR2(1);
71 g_sdf_carryover DATE;
72 g_STATE_NAME hxt_sum_hours_worked_f.state_name%type;
73 g_COUNTY_NAME hxt_sum_hours_worked_f.county_name%type;
74 g_CITY_NAME hxt_sum_hours_worked_f.city_name%type;
75 g_ZIP_CODE hxt_sum_hours_worked_f.zip_code%type;
76
77
78 --Function and Procedure declarations
79
80 FUNCTION valid_data(p_location IN VARCHAR2) RETURN NUMBER;
81
82 FUNCTION gen_details(p_location IN VARCHAR2
83 ,p_shift_adjusted_time_in IN DATE
84 ,p_shift_adjusted_time_out IN DATE )
85 RETURN NUMBER;
86
87 PROCEDURE Delete_Details(p_location IN VARCHAR2
88 ,p_error_code IN OUT NOCOPY NUMBER);
89
90 PROCEDURE shift_adjust_times(p_shift_adjusted_time_in OUT NOCOPY DATE
91 ,p_shift_adjusted_time_out OUT NOCOPY DATE);
92
93 PROCEDURE Rebuild_Details(p_location IN VARCHAR2
94 ,p_error_code IN OUT NOCOPY NUMBER);
95
96 FUNCTION call_gen_error
97 (p_location IN varchar2
98 ,p_error_text IN VARCHAR2
99 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER;
100
101 FUNCTION call_hxthxc_gen_error
102 ( p_app_short_name IN VARCHAR2
103 ,p_msg_name IN VARCHAR2
104 ,p_msg_token IN VARCHAR2
105 ,p_location IN varchar2
106 ,p_error_text IN VARCHAR2
107 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER ;
108
109
110 FUNCTION Get_Include(p_location IN VARCHAR2
111 ,p_egt_id IN NUMBER
112 ,p_element_type_id IN NUMBER
113 ,p_date IN DATE) RETURN VARCHAR2;
114
115 FUNCTION GEN_SPECIAL (p_location IN VARCHAR2
116 ,p_time_in IN DATE
117 ,p_time_out IN DATE
118 ,p_hours_worked IN NUMBER
119 ,p_shift_diff_earning_id IN NUMBER
120 ,p_sdovr_earning_id IN NUMBER) RETURN NUMBER;
121
122
123 ------------generate details,loads globals and calls gen_details---------------
124 ---------------return 0 for normal,1 for warning,2 for error-------------------
125
126 FUNCTION generate_details
127 ( p_ep_id IN NUMBER
128 ,p_ep_type IN VARCHAR2
129 ,p_egt_id IN NUMBER
130 ,p_sdp_id IN NUMBER
131 ,p_hdp_id IN NUMBER
132 ,p_hol_id IN NUMBER
133 ,p_pep_id IN NUMBER
134 ,p_pip_id IN NUMBER
135 ,p_sdovr_id IN NUMBER
136 ,p_osp_id IN NUMBER
137 ,p_standard_start IN NUMBER
138 ,p_standard_stop IN NUMBER
139 ,p_early_start IN NUMBER
140 ,p_late_stop IN NUMBER
141 ,p_hol_yn IN VARCHAR2
142 ,p_person_id IN NUMBER
143 ,p_location IN VARCHAR2
144 ,p_ID IN NUMBER
145 ,p_TIM_ID IN NUMBER
146 ,p_DATE_WORKED IN DATE
147 ,p_ASSIGNMENT_ID IN NUMBER
148 ,p_HOURS IN NUMBER
149 ,p_TIME_IN IN DATE
150 ,p_TIME_OUT IN DATE
151 ,p_ELEMENT_TYPE_ID IN NUMBER
152 ,p_FCL_EARN_REASON_CODE IN VARCHAR2
153 ,p_FFV_COST_CENTER_ID IN NUMBER
154 ,p_FFV_LABOR_ACCOUNT_ID IN NUMBER
155 ,p_TAS_ID IN NUMBER
156 ,p_LOCATION_ID IN NUMBER
157 ,p_SHT_ID IN NUMBER
158 ,p_HRW_COMMENT IN VARCHAR2
159 ,p_FFV_RATE_CODE_ID IN NUMBER
160 ,p_RATE_MULTIPLE IN NUMBER
161 ,p_HOURLY_RATE IN NUMBER
162 ,p_AMOUNT IN NUMBER
163 ,p_FCL_TAX_RULE_CODE IN VARCHAR2
164 ,p_SEPARATE_CHECK_FLAG IN VARCHAR2
165 ,p_SEQNO IN NUMBER
166 ,p_CREATED_BY IN NUMBER
167 ,p_CREATION_DATE IN DATE
168 ,p_LAST_UPDATED_BY IN NUMBER
169 ,p_LAST_UPDATE_DATE IN DATE
170 ,p_LAST_UPDATE_LOGIN IN NUMBER
171 ,p_PERIOD_START_DATE IN DATE --SPR C389
172 ,p_ROWIDIN IN VARCHAR2 --SIR012
173 ,p_EFFECTIVE_START_DATE IN DATE --SIR012
174 ,p_EFFECTIVE_END_DATE IN DATE --SIR012
175 ,p_PROJECT_ID IN NUMBER --SIR022
176 ,p_JOB_ID IN NUMBER --SIR015
177 ,p_PAY_STATUS IN VARCHAR2 --SIR020
178 ,p_PA_STATUS IN VARCHAR2 --SIR022
179 ,p_RETRO_BATCH_ID IN NUMBER --SIR020
180 ,p_DT_UPDATE_MODE IN VARCHAR2 --SIR020
181 ,p_CALL_ADJUST_ABS IN VARCHAR2 DEFAULT 'Y'
182 ,p_STATE_NAME IN VARCHAR2 DEFAULT NULL
183 ,p_COUNTY_NAME IN VARCHAR2 DEFAULT NULL
184 ,p_CITY_NAME IN VARCHAR2 DEFAULT NULL
185 ,p_ZIP_CODE IN VARCHAR2 DEFAULT NULL
186 --,p_GROUP_ID IN NUMBER -- HXT11i1
187 ) RETURN NUMBER IS
188
189 l_location VARCHAR2(120);
190 l_error_code NUMBER := 0;
191 l_rebuild_code NUMBER := 0;
192 shift_adjusted_time_in DATE;
193 shift_adjusted_time_out DATE;
194
195 otl_recurring_period VARCHAR2(120);
196 l_period_start_date hxc_recurring_periods.start_date%TYPE;
197 l_period_start DATE;
198 l_period_end DATE;
199 l_period_type hxc_recurring_periods.period_type%TYPE;
200
201 /* Bug: 4489952 changes starts here */
202
203 -- procedure to insert the non-explosion timecard entries directly
204 -- into table 'hxt_det_hours_worked_f' without calling the explosion code.
205 PROCEDURE insert_non_explodable_hrs
206 IS
207 l_object_version_number HXT_DET_HOURS_WORKED_F.OBJECT_VERSION_NUMBER%TYPE;
208 l_rowid ROWID;
209 l_id NUMBER;
210 l_pay_status VARCHAR2(1);
211 l_pa_status VARCHAR2(1);
212 l_retro_batch_id NUMBER(15);
213 l_error_status NUMBER(15);
214 l_sqlerrm VARCHAR2(200);
215 l_rate_multiple HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
216 l_hourly_rate HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
217 l_amount HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
218 l_hours HXT_DET_HOURS_WORKED_F.HOURS%TYPE; -- SIR092
219 l_costable_type PAY_ELEMENT_LINKS_F.COSTABLE_TYPE%TYPE;
220 l_ffv_cost_center_id HXT_DET_HOURS_WORKED_F.FFV_COST_CENTER_ID%TYPE;
221 l_premium_type HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_TYPE%TYPE;
222 l_premium_amount HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
223 l_processing_order HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PROCESSING_ORDER%TYPE;
224
225 CURSOR next_id_cur IS
226 SELECT hxt_seqno.nextval next_id
227 FROM dual;
228
229 CURSOR get_ovt_rates_cur IS
230 SELECT eltv.hxt_premium_type,
231 eltv.hxt_premium_amount,
232 eltv.hxt_processing_order
233 FROM hxt_pay_element_types_f_ddf_v eltv
234 WHERE eltv.hxt_earning_category NOT IN ('REG', 'ABS')
235 AND g_DATE_WORKED between eltv.effective_start_date
236 and eltv.effective_end_date
237 AND eltv.element_type_id = g_element_type_id
238 ORDER by eltv.hxt_processing_order;
239
240 BEGIN
241
242 IF g_debug THEN
243 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',10);
244 END IF;
245
246 l_hours := p_hours ; -- SIR092
247
248 OPEN next_id_cur;
249 FETCH next_id_cur INTO l_id;
250
251 IF g_debug THEN
252 hr_utility.trace('l_id :'||l_id);
253 END IF;
254
255 CLOSE next_id_cur;
256
257 OPEN get_ovt_rates_cur;
258 FETCH get_ovt_rates_cur
259 INTO l_premium_type, l_premium_amount, l_processing_order;
260
261 IF g_debug THEN
262 hr_utility.trace('premium_type :'||l_premium_type);
263 hr_utility.trace('premium_amount :'||l_premium_amount);
264 hr_utility.trace('processing_order :'||l_processing_order);
265 END IF;
266
267 CLOSE get_ovt_rates_cur;
268
269 IF l_premium_type = 'FACTOR' THEN
270 l_rate_multiple := l_premium_amount;
271 ELSIF l_premium_type = 'RATE' THEN
272 l_hourly_rate := l_premium_amount;
273 ELSIF l_premium_type = 'FIXED' THEN
274 l_amount := l_premium_amount;
275 l_hours := 0 ; -- SIR092 Hours have no meaning with
276 -- flat amount premiums
277 END IF;
278
279 -- Any values passed in from globals will override retrieved values.
280
281 IF g_rate_multiple IS NOT NULL THEN
282 l_rate_multiple := g_rate_multiple ;
283 END IF ;
284 IF g_hourly_rate IS NOT NULL THEN
285 l_hourly_rate := g_hourly_rate ;
286 END IF ;
287 IF g_amount IS NOT NULL THEN
288 l_amount := g_amount ;
289 END IF ;
290
291 l_costable_type := HXT_UTIL.get_costable_type(g_element_type_id,
292 g_date_worked,
293 g_assignment_id);
294 IF g_debug THEN
295 hr_utility.trace('l_costable_type :'||l_costable_type);
296 END IF;
297
298 IF l_costable_type in ('C','F') THEN
299 l_ffv_cost_center_id := g_ffv_cost_center_id;
300 ELSE
301 l_ffv_cost_center_id := NULL;
302 END IF;
303
304 hxt_time_pay.get_retro_fields( g_tim_id
305 ,HXT_TIME_COLLECTION.g_batch_name
306 ,HXT_TIME_COLLECTION.g_batch_ref
307 ,l_pay_status
308 ,l_pa_status
309 ,l_retro_batch_id
310 ,l_error_status
311 ,l_sqlerrm);
312
313 IF l_error_status = 0 THEN
314 IF g_debug then
315 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',20);
316 END IF;
317
318 HXT_DML.INSERT_HXT_DET_HOURS_WORKED (
319 p_rowid => l_rowid,
320 p_id => l_id,
321 p_parent_id => g_id,
322 p_tim_id => g_tim_id,
323 p_date_worked => g_date_worked,
324 p_assignment_id => g_assignment_id,
325 p_hours => l_hours,
326 p_time_in => g_time_in,
327 p_time_out => g_time_out,
328 p_element_type_id => g_element_type_id,
329 p_fcl_earn_reason_code => g_fcl_earn_reason_code,
330 p_ffv_cost_center_id => l_ffv_cost_center_id,
331 p_ffv_labor_account_id => NULL,
332 p_tas_id => g_TAS_ID,
333 p_location_id => g_location_id,
334 p_sht_id => g_sht_id,
335 p_hrw_comment => g_hrw_comment,
336 p_ffv_rate_code_id => g_ffv_rate_code_id,
337 p_rate_multiple => l_rate_multiple,
338 p_hourly_rate => l_hourly_rate,
339 p_amount => l_amount,
340 p_fcl_tax_rule_code => g_fcl_tax_rule_code,
341 p_separate_check_flag => g_separate_check_flag,
342 p_seqno => g_seqno,
343 p_created_by => g_created_by,
344 p_creation_date => g_creation_date,
345 p_last_updated_by => g_last_updated_by,
346 p_last_update_date => g_last_update_date,
347 p_last_update_login => g_last_update_login,
348 p_actual_time_in => NULL,
349 p_actual_time_out => NULL,
350 p_effective_start_date => g_effective_start_date,
351 p_effective_end_date => g_effective_end_date,
352 p_project_id => g_project_id,
353 p_job_id => NULL,
354 p_earn_pol_id => NULL,
355 p_retro_batch_id => l_retro_batch_id,
356 p_pa_status => l_pa_status,
357 p_pay_status => l_pay_status,
358 --p_group_id => g_group_id,
359 p_object_version_number => l_object_version_number,
360 p_STATE_NAME => g_STATE_NAME,
361 p_COUNTY_NAME => g_COUNTY_NAME,
362 p_CITY_NAME => g_CITY_NAME,
363 p_ZIP_CODE => g_ZIP_CODE);
364
365 ELSE /* l_error_status <> 0 */
366 IF g_debug then
367 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 30);
368 END IF;
369 -- Insert record in error table.
370 FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
371 END IF;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 IF g_debug THEN
376 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
377 END IF;
378 -- Insert record in error table.
379 FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
380
381 END insert_non_explodable_hrs;
382
383 Function check_non_explosion_entry (p_element_type_id IN NUMBER, p_date_worked DATE)
384 RETURN BOOLEAN IS
385 l_non_explosion_flag VARCHAR2(1);
386
387 CURSOR check_non_explosion_entry IS
388 SELECT 'Y'
389 FROM hxt_add_elem_info_f
390 WHERE element_type_id = p_element_type_id
391 AND p_date_worked BETWEEN effective_start_date
392 AND effective_end_date
393 AND NVL(exclude_from_explosion, 'N') = 'Y';
394
395 BEGIN
396
397 FOR i in check_non_explosion_entry
398 LOOP
399 l_non_explosion_flag := 'Y';
400 END LOOP;
401
402 IF l_non_explosion_flag = 'Y' THEN
403 Return TRUE;
404 ELSE
405 Return FALSE;
406 END IF;
407 END check_non_explosion_entry;
408
409 /* Bug: 4489952 changes ends here */
410
411 BEGIN
412 g_debug :=hr_utility.debug_enabled;
413 if g_debug then
414 hr_utility.set_location('hxt_time_summary.generate_details',10);
415 end if;
416
417 -- Set the flags for checking which shift diff premium gets paid for a
418 -- segment chunk.
419 g_sdf_rule_completed := 'Y';
420 g_sdf_carryover := null;
421
422 -- Set global variables for package with parameter values
423 g_ep_id := p_ep_id;
424 g_ep_type := p_ep_type;
425 g_egt_id := p_egt_id;
426 g_sdp_id := p_sdp_id;
427 g_hdp_id := p_hdp_id;
428 g_hol_id := p_hol_id;
429 g_pep_id := p_pep_id;
430 g_pip_id := p_pip_id;
431 g_sdovr_id := p_sdovr_id;
432 g_osp_id := p_osp_id;
433 g_standard_start := p_standard_start;
434 g_standard_stop := p_standard_stop;
435 g_early_start := p_early_start;
436 g_late_stop := p_late_stop;
437 g_hol_yn := p_hol_yn;
438 g_person_id := p_person_id;
439 g_ID := p_ID;
440 g_ROWID := CHARTOROWID(p_ROWIDIN);
441 g_EFFECTIVE_START_DATE := p_EFFECTIVE_START_DATE;
442 g_EFFECTIVE_END_DATE := p_EFFECTIVE_END_DATE;
443 g_TIM_ID := p_TIM_ID;
444 g_DATE_WORKED := p_DATE_WORKED;
445 g_ASSIGNMENT_ID := p_ASSIGNMENT_ID;
446 g_HOURS := p_HOURS;
447 g_TIME_IN := p_TIME_IN;
448 g_TIME_OUT := p_TIME_OUT;
449 g_ELEMENT_TYPE_ID := p_ELEMENT_TYPE_ID;
450 g_FCL_EARN_REASON_CODE := p_FCL_EARN_REASON_CODE;
451 g_FFV_COST_CENTER_ID := p_FFV_COST_CENTER_ID;
452 g_FFV_LABOR_ACCOUNT_ID := p_FFV_LABOR_ACCOUNT_ID;
453 g_TAS_ID := p_TAS_ID;
454 g_LOCATION_ID := p_LOCATION_ID;
455 g_SHT_ID := p_SHT_ID;
456 g_HRW_COMMENT := p_HRW_COMMENT;
457 g_FFV_RATE_CODE_ID := p_FFV_RATE_CODE_ID;
458 g_RATE_MULTIPLE := p_RATE_MULTIPLE;
459 g_HOURLY_RATE := p_HOURLY_RATE;
460 g_AMOUNT := p_AMOUNT;
461 g_FCL_TAX_RULE_CODE := p_FCL_TAX_RULE_CODE;
462 g_SEPARATE_CHECK_FLAG := p_SEPARATE_CHECK_FLAG;
463 g_SEQNO := p_SEQNO;
464 g_CREATED_BY := p_CREATED_BY;
465 g_CREATION_DATE := p_CREATION_DATE;
466 g_LAST_UPDATED_BY := p_LAST_UPDATED_BY;
467 g_LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
468 g_LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
469 g_PROJECT_ID := p_PROJECT_ID;
470 g_JOB_ID := p_JOB_ID;
471 g_PAY_STATUS := p_PAY_STATUS;
472 g_PA_STATUS := p_PA_STATUS;
473 g_RETRO_BATCH_ID := p_RETRO_BATCH_ID;
474 g_DT_UPDATE_MODE := p_DT_UPDATE_MODE;
475 g_PERIOD_START_DATE := p_PERIOD_START_DATE;
476 g_CALL_ADJUST_ABS := p_CALL_ADJUST_ABS;
477 g_STATE_NAME :=p_STATE_NAME;
478 g_COUNTY_NAME :=p_COUNTY_NAME;
479 g_CITY_NAME :=p_CITY_NAME;
480 g_ZIP_CODE :=p_ZIP_CODE;
481 -- g_GROUP_ID := p_GROUP_ID;
482
483
484 -- generate_details returns 0 if no errors encountered,-1 to signal the
485 -- Timecard that a summary status was changed from E,2 for errors that stopped
486 -- processing on current record,3 for errors that stopped processing on related
487 -- summaries and -2 for warning that changed records prevented complete rebuild
488
489 -- g_start_day_of_week := 'MON';
490 -- hard coded for now must match hxt_time_details start day
491
492 --
493 -- Get the Recurring Period assigned to the person. The recurring period is
494 -- setup as a Preference - Self-Service -> Otl Rules Evaluation -> Overtime
495 -- Recurring Period.
496 -- Context of the Preference is TC_W_RULES_EVALUATION
497 -- Segment is attribute 3 OT_RECURRING_PERIOD
498 --
499 if g_debug then
500 hr_utility.set_location('hxt_time_summary.generate_details',20);
501 end if;
502 otl_recurring_period := hxc_preference_evaluation.resource_preferences
503 (g_person_id
504 ,'TC_W_RULES_EVALUATION'
505 ,3
506 ,p_date_worked);
507 if g_debug then
508 hr_utility.set_location('hxt_time_summary.generate_details',30);
509 hr_utility.trace('otl_recurring_period :'||otl_recurring_period);
510 end if;
511 --
512 -- Now calculate the start day as per the otl recurring period
513 --
514 if g_debug then
515 hr_utility.set_location('hxt_time_summary.generate_details',40);
516 end if;
517 IF otl_recurring_period is NOT NULL THEN
518 --
519 -- Get the period start_date, period_type for the recurring_period
520 --
521 if g_debug then
522 hr_utility.set_location('hxt_time_summary.generate_details',50);
523 end if;
524 SELECT start_date,period_type
525 INTO l_period_start_date,l_period_type
526 FROM hxc_recurring_periods
527 WHERE recurring_period_id = otl_recurring_period;
528
529 if g_debug then
530 hr_utility.trace('l_period_start_date :'
531 || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
532 hr_utility.trace('l_period_type :'|| l_period_type);
533 hr_utility.trace('g_date_worked :'
534 || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
535 hr_utility.set_location('hxt_time_summary.generate_details',60);
536 end if;
537 hxc_period_evaluation.period_start_stop
538 (g_date_worked
539 ,l_period_start_date
540 ,l_period_start
541 ,l_period_end
542 ,l_period_type);
543
544 if g_debug then
545 hr_utility.set_location('hxt_time_summary.generate_details',70);
546 hr_utility.trace('l_period_start :'
547 || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
548 hr_utility.trace('l_period_end :'
549 || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
550 end if;
551 g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
552 if g_debug then
553 hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
554 hr_utility.set_location('hxt_time_summary.generate_details',80);
555 end if;
556 ELSE
557 if g_debug then
558 hr_utility.set_location('hxt_time_summary.generate_details',90);
559 hr_utility.trace('p_PERIOD_START_DATE :'
560 || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
561 end if;
562 g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
563 if g_debug then
564 hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
565 end if;
566 l_location := p_location||':GD';
567 END IF;
568
569 if g_debug then
570 hr_utility.set_location('hxt_time_summary.generate_details',100);
571 end if;
572 IF valid_data(l_location) NOT IN (0,1) THEN
573 -- check for time in/out and hour values
574 if g_debug then
575 hr_utility.set_location('hxt_time_summary.generate_details',110);
576 end if;
577 RETURN(2);
578 ELSIF valid_data(l_location) = 1 THEN
579 if g_debug then
580 hr_utility.set_location('hxt_time_summary.generate_details',120);
581 end if;
582 RETURN(11);
583 END IF;
584
585 IF g_element_type_id is not null THEN
586 if g_debug then
587 hr_utility.set_location('hxt_time_summary.generate_details',130);
588 end if;
589 -- Determine if earning is in assigned INCLUDE earning group
590 g_include_yn := Get_Include(l_location
591 ,g_egt_id
592 ,g_element_type_id
593 ,g_date_worked);
594
595 IF g_include_yn = 'E' THEN
596 if g_debug then
597 hr_utility.set_location('hxt_time_summary.generate_details',140);
598 end if;
599 RETURN 2;
600 END IF;
601
602 END IF; --element_type_id is not null
603
604 IF l_error_code < 2 THEN
605 if g_debug then
606 hr_utility.set_location('hxt_time_summary.generate_details',150);
607 end if;
608
609 /* Bug: 4489952 changes starts here */
610
611 IF check_non_explosion_entry (g_element_type_id, g_date_worked) THEN
612 IF g_debug then
613 hr_utility.set_location('hxt_time_summary.generate_details',151);
614 END IF;
615
616 insert_non_explodable_hrs;
617
618 ELSE /* check_non_explosion_entry = FALSE */
619
620 IF g_debug then
621 hr_utility.set_location('hxt_time_summary.generate_details',155);
622 END IF;
623
624 rebuild_details( l_location, l_rebuild_code);
625 END IF;
626
627 /* Bug: 4489952 changes ends here */
628
629 END IF;
630
631 IF l_rebuild_code <> 0 THEN
632 if g_debug then
633 hr_utility.set_location('hxt_time_summary.generate_details',160);
634 end if;
635 RETURN l_rebuild_code;
636 ELSE
637 if g_debug then
638 hr_utility.set_location('hxt_time_summary.generate_details',170);
639 end if;
640 RETURN l_error_code;
641 END IF;
642
643 EXCEPTION
644
645 WHEN OTHERS THEN
646 if g_debug then
647 hr_utility.set_location('hxt_time_summary.generate_details',180);
648 end if;
649 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
650 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm);
651 --2278400 RETURN call_gen_error(l_location, '', sqlerrm);
652 END; -- generate_details
653
654 PROCEDURE time_in_dates(ln_start in number
655 ,ln_stop in number
656 ,ln_carryover in number
657 ,time_in out nocopy date
658 ,time_out out nocopy date
659 ,carryover_time out nocopy date
660 ,l_date_worked in date)
661 IS
662
663 BEGIN
664 g_debug :=hr_utility.debug_enabled;
665 if g_debug then
666 hr_utility.set_location('hxt_time_summary.time_in_dates',10);
667 hr_utility.trace('ln_start :'||ln_start);
668 hr_utility.trace('ln_stop :'||ln_stop);
669 hr_utility.trace('ln_carryover :'||ln_carryover);
670 end if;
671 time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
672 ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
673 if g_debug then
674 hr_utility.trace('time_in :'
675 || to_char(time_in,'DD-MON-YYYY HH24:MI:SS'));
676 end if;
677 IF ln_start < ln_stop OR
678 (ln_start = 0 AND
679 ln_stop = 0) THEN
680 if g_debug then
681 hr_utility.set_location('hxt_time_summary.time_in_dates',20);
682 end if;
683 time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
684 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
685 if g_debug then
686 hr_utility.trace('time_out :'
687 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
688 end if;
689 ELSE --IF ln_start >= ln_stop
690 if g_debug then
691 hr_utility.set_location('hxt_time_summary.time_in_dates',30);
692 end if;
693 time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
694 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
695 if g_debug then
696 hr_utility.trace('time_out :'
697 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
698 end if;
699 END IF;
700
701 IF ln_carryover is NOT NULL THEN
702 if g_debug then
703 hr_utility.set_location('hxt_time_summary.time_in_dates',35);
704 end if;
705 IF ln_start < ln_carryover THEN
706 if g_debug then
707 hr_utility.set_location('hxt_time_summary.time_in_dates',40);
708 end if;
709 carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
710 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
711
712 if g_debug then
713 hr_utility.trace('carryover_time :'
714 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
715 end if;
716 ELSE --IF ln_start >= ln_carryover
717 if g_debug then
718 hr_utility.set_location('hxt_time_summary.time_in_dates',50);
719 end if;
720 carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
721 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
722
723 if g_debug then
724 hr_utility.trace('carryover_time :'
725 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
726 end if;
727 END IF;
728
729 END IF;
730
731 END;
732
733 FUNCTION call_gen_error
734 (p_location IN varchar2
735 ,p_error_text IN VARCHAR2
736 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER IS
737
738 dummy NUMBER;
739
740 -- calls error processing procedure
741
742 BEGIN
743
744 if g_debug then
745 hr_utility.set_location('hxt_time_summary.call_gen_error',10);
746 end if;
747 --Checking of the hours worked table for the current id is done so that
748 --validation is not done on a record being deleted.
749
750 hxt_util.gen_error(g_tim_id
751 ,g_id
752 ,NULL
753 ,p_error_text
754 ,p_location
755 ,p_oracle_error_text
756 ,g_EFFECTIVE_START_DATE
757 ,g_EFFECTIVE_END_DATE
758 ,'ERR');
759
760 if g_debug then
761 hr_utility.set_location('hxt_time_summary.call_gen_error',20);
762 end if;
763 RETURN 2;
764
765 EXCEPTION
766
767 WHEN NO_DATA_FOUND THEN
768 if g_debug then
769 hr_utility.set_location('hxt_time_summary.call_gen_error',30);
770 end if;
771 RETURN 0;
772
773 WHEN OTHERS THEN
774 if g_debug then
775 hr_utility.set_location('hxt_time_summary.call_gen_error',40);
776 end if;
777 hxt_util.gen_error(g_tim_id
778 ,g_id
779 ,NULL
780 ,p_error_text
781 ,p_location
782 ,p_oracle_error_text
783 ,g_EFFECTIVE_START_DATE
784 ,g_EFFECTIVE_END_DATE
785 ,'ERR');
786 if g_debug then
787 hr_utility.set_location('hxt_time_summary.call_gen_error',50);
788 end if;
789 RETURN 2;
790 END;
791
792 FUNCTION call_hxthxc_gen_error
793 ( p_app_short_name IN VARCHAR2
794 ,p_msg_name IN VARCHAR2
795 ,p_msg_token IN VARCHAR2
796 ,p_location IN varchar2
797 ,p_error_text IN VARCHAR2
798 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER IS
799
800 dummy NUMBER;
801
802 -- calls error processing procedure
803
804 BEGIN
805
806 if g_debug then
807 hr_utility.set_location('hxt_time_summary.call_gen_error',10);
808 end if;
809 --Checking of the hours worked table for the current id is done so that
810 --validation is not done on a record being deleted.
811
812 hxt_util.gen_error(g_tim_id
813 ,g_id
814 ,NULL
815 ,p_error_text
816 ,p_location
817 ,p_oracle_error_text
818 ,g_EFFECTIVE_START_DATE
819 ,g_EFFECTIVE_END_DATE
820 ,'ERR');
821
822 hxc_time_entry_rules_utils_pkg.add_error_to_table (
823 p_message_table=> hxt_hxc_retrieval_process.g_otm_messages,
824 p_message_name=> p_msg_name,
825 p_message_token=> NULL ,
826 p_message_level=> 'ERROR',
827 p_message_field=> NULL,
828 p_application_short_name=> p_app_short_name,
829 p_timecard_bb_id=> null,
830 p_time_attribute_id=> NULL,
831 p_timecard_bb_ovn=> NULL,
832 p_time_attribute_ovn=> NULL
833 );
834 if g_debug then
835 hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
836 hr_utility.set_location('hxt_time_summary.call_gen_error',20);
837 end if;
838 RETURN 2;
839
840 EXCEPTION
841
842 WHEN NO_DATA_FOUND THEN
843 if g_debug then
844 hr_utility.set_location('hxt_time_summary.call_gen_error',30);
845 end if;
846 RETURN 0;
847
848 WHEN OTHERS THEN
849 if g_debug then
850 hr_utility.set_location('hxt_time_summary.call_gen_error',40);
851 end if;
852 hxt_util.gen_error(g_tim_id
853 ,g_id
854 ,NULL
855 ,p_error_text
856 ,p_location
857 ,p_oracle_error_text
858 ,g_EFFECTIVE_START_DATE
859 ,g_EFFECTIVE_END_DATE
860 ,'ERR');
861
862 hxc_time_entry_rules_utils_pkg.add_error_to_table (
863 p_message_table=> hxt_hxc_retrieval_process.g_otm_messages,
864 p_message_name=> p_msg_name,
865 p_message_token=> substr(p_msg_token,1,240),
866 p_message_level=> 'ERROR',
867 p_message_field=> NULL,
868 p_application_short_name=> p_app_short_name,
869 p_timecard_bb_id=> null,
870 p_time_attribute_id=> NULL,
871 p_timecard_bb_ovn=> NULL,
872 p_time_attribute_ovn=> NULL
873 );
874
875 if g_debug then
876 hr_utility.set_location('hxt_time_summary.call_gen_error',50);
877 end if;
878 RETURN 2;
879 END;
880
881 FUNCTION valid_data(p_location IN VARCHAR2) RETURN NUMBER IS
882
883 -- Checks in and out times to ensure that if one is present both are
884 -- If no times are there hours must be there
885
886 l_cat VARCHAR2(10);
887 error_code NUMBER := 0;
888 location VARCHAR2(120) := p_location||':VD';
889 l_non_exp_elem_count NUMBER; /* Bug: 4489952 */
890
891 BEGIN
892
893 if g_debug then
894 hr_utility.set_location('hxt_time_summary.valid_data',10);
895 end if;
896 IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
897
898 if g_debug then
899 hr_utility.set_location('hxt_time_summary.valid_data',20);
900 end if;
901 FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
902 RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
903
904 END IF;
905
906 IF g_time_out IS NULL AND g_time_in IS NOT NULL THEN
907
908 if g_debug then
909 hr_utility.set_location('hxt_time_summary.valid_data',30);
910 end if;
911 FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
912 RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
913
914 END IF;
915
916 IF g_time_in IS NULL AND g_hours IS NULL THEN
917
918 if g_debug then
919 hr_utility.set_location('hxt_time_summary.valid_data',40);
920 end if;
921 FND_MESSAGE.SET_NAME('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ');
922 RETURN call_hxthxc_gen_error('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ',NULL,location, '');
923
924 END IF;
925
926 IF g_element_type_id IS NOT NULL THEN
927
928 if g_debug then
929 hr_utility.set_location('hxt_time_summary.valid_data',50);
930 end if;
931 l_cat := hxt_util.element_cat(g_element_type_id
932 ,g_date_worked);
933
934 END IF;
935
936 -- Hours or times must be consistent across all summary records in a day
937
938 BEGIN
939 g_debug :=hr_utility.debug_enabled;
940 if g_debug then
941 hr_utility.set_location('hxt_time_summary.valid_data',60);
942 end if;
943
944 /* Bug: 4489952 changes starts here */
945
946 SELECT count(1)
947 INTO l_non_exp_elem_count
948 FROM hxt_add_elem_info_f hei
949 WHERE hei.element_type_id = g_element_type_id
950 AND g_date_worked BETWEEN hei.effective_start_date
951 AND hei.effective_end_date
952 AND NVL(hei.exclude_from_explosion, 'N') = 'Y';
953
954 IF (g_hours<>0) and l_non_exp_elem_count = 0 THEN
955 IF g_TIME_IN IS NULL THEN
956 SELECT '1'
957 INTO error_code
958 FROM SYS.DUAL
959 WHERE EXISTS (SELECT hrw.id
960 FROM hxt_sum_hours_worked hrw, hxt_add_elem_info_f hei
961 WHERE hrw.tim_id = g_TIM_ID
962 AND hrw.date_worked = g_DATE_WORKED
963 AND hrw.time_in IS NOT NULL
964 AND hrw.element_type_id = hei.element_type_id
965 AND hrw.element_type_id is not null
966 AND g_date_worked BETWEEN hei.effective_start_date
967 AND hei.effective_end_date
968 AND NVL(hei.exclude_from_explosion, 'N') <> 'Y'
969 UNION
970 SELECT hrw.id
971 FROM hxt_sum_hours_worked hrw
972 WHERE hrw.tim_id = g_TIM_ID
973 AND hrw.date_worked = g_DATE_WORKED
974 AND hrw.time_in IS NOT NULL
975 AND hrw.element_type_id is null
976 );
977 ELSE -- g_TIME_IN IS NOT NULL
978 SELECT '1'
979 INTO error_code
980 FROM SYS.DUAL
981 WHERE EXISTS (SELECT hrw.id
982 FROM hxt_sum_hours_worked hrw, hxt_add_elem_info_f hei
983 WHERE hrw.tim_id = g_TIM_ID
984 AND hrw.date_worked = g_DATE_WORKED
985 AND hrw.time_in IS NULL
986 AND hrw.hours<>0
987 AND hrw.element_type_id = hei.element_type_id
988 AND hrw.element_type_id is not null
989 AND g_date_worked BETWEEN hei.effective_start_date
990 AND hei.effective_end_date
991 AND NVL(hei.exclude_from_explosion, 'N') <> 'Y'
992 UNION
993 SELECT hrw.id
994 FROM hxt_sum_hours_worked hrw
995 WHERE hrw.tim_id = g_TIM_ID
996 AND hrw.date_worked = g_DATE_WORKED
997 AND hrw.time_in IS NULL
998 AND hrw.hours<>0
999 AND hrw.element_type_id is null
1000 );
1001 END IF;
1002 END IF;
1003
1004 /* Bug: 4489952 changes ends here */
1005
1006 EXCEPTION
1007 WHEN NO_DATA_FOUND THEN
1008 if g_debug then
1009 hr_utility.set_location('hxt_time_summary.valid_data',90);
1010 end if;
1011 NULL;
1012 END;
1013
1014 IF error_code = 1 THEN
1015 if g_debug then
1016 hr_utility.set_location('hxt_time_summary.valid_data',100);
1017 end if;
1018 FND_MESSAGE.SET_NAME('HXT','HXT_39329_INC_TIM_HR_ENTRIES');
1019
1020 END IF;
1021
1022 RETURN error_code;
1023
1024 EXCEPTION
1025
1026 WHEN OTHERS THEN
1027 if g_debug then
1028 hr_utility.set_location('hxt_time_summary.valid_data',110);
1029 end if;
1030 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1031 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
1032
1033 END; -- valid_data
1034 -------------------------------------------------------------------------------
1035 PROCEDURE Delete_Details(p_location IN VARCHAR2
1036 ,p_error_code IN OUT NOCOPY NUMBER) IS
1037 --Begins by deleting details for current summary record
1038 CURSOR completed_time_card IS
1039 SELECT 'Y'
1040 FROM hxt_det_hours_worked_f
1041 WHERE tim_id=g_tim_id
1042 AND pay_status = 'C';
1043
1044 l_completed_time_card VARCHAR2(1) := 'N';
1045 l_location hxt_errors.location%TYPE;
1046
1047 BEGIN
1048
1049 if g_debug then
1050 hr_utility.set_location('hxt_time_summary.Delete_Details',10);
1051 end if;
1052 p_error_code := 0;
1053
1054 --Add local code to location variable
1055 l_location := p_location||':DD';
1056
1057 IF nvl(g_DT_UPDATE_MODE, 'CORRECTION') = 'CORRECTION' THEN
1058 if g_debug then
1059 hr_utility.set_location('hxt_time_summary.Delete_Details',20);
1060 end if;
1061 DELETE from hxt_det_hours_worked_f
1062 WHERE parent_id = g_id;
1063
1064 DELETE from hxt_errors_f where --SPR C153
1065 hrw_id = g_id; --SPR C153
1066 ELSE
1067 if g_debug then
1068 hr_utility.set_location('hxt_time_summary.Delete_Details',30);
1069 end if;
1070 -- Delete details for this entry and all others that follow
1071 -- on this day for this person that are non-absence or are in
1072 -- the person's include group
1073
1074 UPDATE hxt_det_hours_worked_f
1075 SET effective_end_date = g_effective_start_date - 1
1076 WHERE rowid in (
1077 SELECT rowid
1078 FROM hxt_det_hours_worked
1079 WHERE parent_id = g_id);
1080 UPDATE hxt_errors_f
1081 SET effective_end_date = g_effective_start_date - 1
1082 WHERE rowid in (
1083 SELECT rowid
1084 FROM hxt_errors
1085 WHERE hrw_id = g_id);
1086
1087 END IF;--absence and not in include group or not
1088
1089 EXCEPTION
1090
1091 WHEN OTHERS THEN
1092 if g_debug then
1093 hr_utility.set_location('hxt_time_summary.Delete_Details',40);
1094 end if;
1095 -- Write to error table - do not generate details
1096 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
1097 p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
1098 --p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
1099 END; -- delete details
1100
1101 -------------------------------------------------------------------------------
1102 PROCEDURE shift_adjust_times(p_shift_adjusted_time_in OUT NOCOPY DATE
1103 ,p_shift_adjusted_time_out OUT NOCOPY DATE) IS
1104
1105 BEGIN
1106
1107 if g_debug then
1108 hr_utility.set_location('hxt_time_summary.shift_adjust_times',10);
1109 end if;
1110 p_shift_adjusted_time_in := g_TIME_IN;
1111 p_shift_adjusted_time_out := g_TIME_OUT;
1112
1113 IF (g_EARLY_START IS NOT NULL AND g_STANDARD_START IS NOT NULL) THEN
1114 if g_debug then
1115 hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
1116 end if;
1117 IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
1118 if g_debug then
1119 hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
1120 end if;
1121 IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
1122 if g_debug then
1123 hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
1124 end if;
1125 p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1126 - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
1127 ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
1128 if g_debug then
1129 hr_utility.set_location('hxt_time_summary.shift_adjust_times',50);
1130 end if;
1131 p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1132 + (hxt_util.time_to_hours(2400) -
1133 hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
1134 END IF;
1135 ELSE -- no midnight span
1136 if g_debug then
1137 hr_utility.set_location('hxt_time_summary.shift_adjust_times',60);
1138 end if;
1139 IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
1140 AND g_STANDARD_START THEN
1141 if g_debug then
1142 hr_utility.set_location('hxt_time_summary.shift_adjust_times',70);
1143 end if;
1144 p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1145 - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
1146 END IF;
1147 END IF;
1148 END IF;
1149
1150 IF (g_LATE_STOP IS NOT NULL AND g_STANDARD_STOP IS NOT NULL) THEN
1151 if g_debug then
1152 hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
1153 end if;
1154 IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
1155 if g_debug then
1156 hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
1157 end if;
1158 IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
1159 if g_debug then
1160 hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
1161 end if;
1162 p_shift_adjusted_time_out := g_TIME_OUT -
1163 (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1164 - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
1165 ELSIF to_number(to_char(g_TIME_OUT, 'HH24MI')) < g_LATE_STOP THEN
1166 if g_debug then
1167 hr_utility.set_location('hxt_time_summary.shift_adjust_times',110);
1168 end if;
1169 p_shift_adjusted_time_out := g_TIME_OUT -
1170 (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1171 + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
1172 END IF;
1173 ELSE -- no midnight span
1174 if g_debug then
1175 hr_utility.set_location('hxt_time_summary.shift_adjust_times',120);
1176 end if;
1177 IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
1178 AND g_LATE_STOP THEN
1179 if g_debug then
1180 hr_utility.set_location('hxt_time_summary.shift_adjust_times',130);
1181 end if;
1182 p_shift_adjusted_time_out := g_TIME_OUT -
1183 (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1184 - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
1185 END IF;
1186 END IF;
1187 END IF;
1188
1189 END; -- shift adjust times
1190 -------------------------------------------------------------------------------
1191 PROCEDURE Rebuild_Details(p_location IN VARCHAR2
1192 ,p_error_code IN OUT NOCOPY NUMBER) IS
1193
1194 --Rebuilds details for a person for this summary record.
1195
1196 --Define local variables
1197 l_error_code NUMBER := 0;
1198 l_location hxt_errors.location%TYPE;
1199 l_shift_adjusted_time_in DATE;
1200 l_shift_adjusted_time_out DATE;
1201 original_record_id NUMBER := g_ID;
1202 change_warning_flag NUMBER := 0;
1203 l_retcode NUMBER; -- BUG688072
1204 l_hdy_id NUMBER; -- BUG688072
1205 l_hours NUMBER; -- BUG688072
1206
1207 BEGIN
1208
1209 if g_debug then
1210 hr_utility.set_location('hxt_time_summary.Rebuild_Details',10);
1211 end if;
1212 --Add local code to location variable
1213 l_location := p_location||':RB';
1214
1215 --Currently, earning type is null in the summary record unless it is of type
1216 --'ABSENCE'.
1217
1218 --Check if absence and not in include group
1219 HXT_UTIL.Check_For_Holiday(g_date_worked
1220 ,g_hol_id
1221 ,l_hdy_id
1222 ,l_hours
1223 ,l_retcode);
1224 if g_debug then
1225 hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
1226 end if;
1227 IF l_retcode = 1 THEN
1228 if g_debug then
1229 hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
1230 end if;
1231 g_hol_yn := 'Y';
1232 ELSE
1233 if g_debug then
1234 hr_utility.set_location('hxt_time_summary.Rebuild_Details',40);
1235 end if;
1236 g_hol_yn := 'N';
1237 END IF;
1238
1239 l_error_code := Gen_Details(l_location, g_time_in, g_time_out);
1240
1241 IF l_error_code <> 0 THEN
1242 if g_debug then
1243 hr_utility.set_location('hxt_time_summary.Rebuild_Details',50);
1244 end if;
1245 p_error_code := l_error_code;
1246 ELSE
1247 if g_debug then
1248 hr_utility.set_location('hxt_time_summary.Rebuild_Details',60);
1249 end if;
1250 p_error_code := change_warning_flag;
1251 END IF;
1252
1253 EXCEPTION
1254
1255 WHEN OTHERS THEN
1256 if g_debug then
1257 hr_utility.set_location('hxt_time_summary.Rebuild_Details',70);
1258 end if;
1259 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
1260 p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
1261 --2278400 p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
1262 END; -- rebuild details
1263 -------------------------------------------------------------------------------
1264 FUNCTION gen_details
1265 (p_location IN VARCHAR2
1266 ,p_shift_adjusted_time_in IN DATE
1267 ,p_shift_adjusted_time_out IN DATE)
1268 RETURN NUMBER IS
1269
1270 -- g_variables are global variables set when package is entered and should not
1271 -- be changed
1272 -- p_variables are parameters
1273 -- all other variables are locals
1274
1275 location VARCHAR2(120) := p_location||':GDD';
1276 segment_start_time DATE; -- start time of segment - includes date
1277 segment_stop_time DATE; -- stop time of segment - includes date
1278 hours_worked NUMBER; -- # of hours in segment
1279 return_code NUMBER := 0;
1280 sd_rule_earning NUMBER; -- element type of sd rule found
1281 sd_rule_carryover NUMBER; -- carryover time of sd rule found
1282 sd_rule_start NUMBER; -- start of sd rule found
1283 loop_count NUMBER := 0;--count loop passes to break if necessary
1284
1285 /*CURSOR Get_shift_stop_time (p_assignment_id NUMBER
1286 ,p_date_worked DATE ) IS
1287 SELECT hs.standard_stop
1288 FROM hxt_shifts hs
1289 ,hxt_work_shifts hws
1290 ,hxt_per_aei_ddf_v aeiv
1291 ,hxt_rotation_schedules rts
1292 WHERE aeiv.assignment_id = p_ASSIGNMENT_ID
1293 AND p_DATE_WORKED between aeiv.effective_start_date
1294 and aeiv.effective_end_date
1295 AND rts.rtp_id = aeiv.hxt_rotation_plan
1296 AND rts.start_date = (SELECT MAX(start_date)
1297 FROM hxt_rotation_schedules
1298 WHERE rtp_id = rts.rtp_id
1299 AND start_date <= p_DATE_WORKED
1300 )
1301 AND hws.tws_id = rts.tws_id
1302 AND hws.week_day = to_char(p_DATE_WORKED,'DY')
1303 AND hws.sht_id = hs.id;
1304 */
1305 ln_standard_start hxt_shifts.standard_start%TYPE;
1306 ln_standard_stop hxt_shifts.standard_stop%TYPE;
1307 wp_start_time DATE;
1308 wp_stop_time DATE;
1309 ld_carryover2 DATE;
1310
1311 CURSOR Get_Work_plan IS
1312 SELECT hs.standard_start , hs.standard_stop
1313 FROM hxt_per_aei_ddf_v aeiv
1314 ,hxt_rotation_schedules rts
1315 ,hxt_work_shifts hws
1316 ,hxt_shifts hs
1317 WHERE aeiv.assignment_id = g_ASSIGNMENT_ID
1318 AND g_DATE_WORKED between aeiv.effective_start_date
1319 and aeiv.effective_end_date
1320 AND rts.rtp_id = aeiv.hxt_rotation_plan
1321 AND rts.start_date = (SELECT MAX(start_date)
1322 FROM hxt_rotation_schedules
1323 WHERE rtp_id = rts.rtp_id
1324 AND start_date <= g_DATE_WORKED
1325 )
1326 AND hws.tws_id = rts.tws_id
1327 AND hws.week_day = hxt_util.get_week_day(g_DATE_WORKED)
1328 AND hws.sht_id = hs.id;
1329
1330 CURSOR Get_sd_rules IS
1331 SELECT sdr.start_time
1332 ,sdr.stop_time
1333 -- ,sdr.element_type_id
1334 ,sdr.carryover_time
1335 FROM hxt_shift_diff_rules sdr
1336 WHERE sdr.sdp_id = g_sdp_id
1337 AND g_DATE_WORKED BETWEEN
1338 sdr.effective_start_date AND sdr.effective_end_date;
1339
1340 ln_sd_start hxt_shift_diff_rules.start_time%TYPE;
1341 ln_sd_stop hxt_shift_diff_rules.stop_time%TYPE;
1342 ln_sd_carryover hxt_shift_diff_rules.carryover_time%TYPE;
1343 sd_start_time DATE;
1344 sd_stop_time DATE;
1345 sd_carryover_time DATE;
1346 sd_date_worked DATE;
1347
1348 wplan_date_worked DATE;
1349
1350 segment_start number;
1351 segment_stop number;
1352 chunk_start date;
1353 chunk_stop date;
1354 chunk_start_date date;
1355 chunk_stop_date date;
1356 p_sdp_earning_type number;
1357 p_sdovr_earning_type number;
1358
1359 l_next_index BINARY_INTEGER := 0;
1360 chunk_count NUMBER := 1;
1361
1362 -----------------Gen Details local functions and procedures---------------------
1363
1364 ----------------- Populate PL/SQL Table ---------------------------------------
1365 PROCEDURE populate_plsql_table (p_value in date)
1366 IS
1367 lv_insert_flag VARCHAR2(1) := 'Y';
1368 ln_next_index NUMBER;
1369
1370 BEGIN
1371 if segment_chunks.count > 0 then
1372 for i in segment_chunks.first .. segment_chunks.last loop
1373 if p_value = segment_chunks(i) then
1374 lv_insert_flag := 'N';
1375 exit;
1376 end if;
1377 end loop;
1378 end if;
1379
1380 if lv_insert_flag = 'Y' then
1381 ln_next_index := segment_chunks.count + 1;
1382 segment_chunks(ln_next_index) := p_value;
1383 end if;
1384
1385 END populate_plsql_table;
1386
1387 /*
1388 PROCEDURE time_in_dates(ln_start in number
1389 ,ln_stop in number
1390 ,ln_carryover in number
1391 ,time_in out nocopy date
1392 ,time_out out nocopy date
1393 ,carryover_time out nocopy date
1394 ,l_date_worked in date)
1395 IS
1396
1397 BEGIN
1398 g_debug :=hr_utility.debug_enabled;
1399 if g_debug then
1400 hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1401 hr_utility.trace('ln_start :'||ln_start);
1402 hr_utility.trace('ln_stop :'||ln_stop);
1403 hr_utility.trace('ln_carryover :'||ln_carryover);
1404 end if;
1405 time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
1406 ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
1407 if g_debug then
1408 hr_utility.trace('time_in :'
1409 || to_char(time_in,'DD-MON-YYYY HH24:MI:SS'));
1410 end if;
1411 IF ln_start < ln_stop OR
1412 (ln_start = 0 AND
1413 ln_stop = 0) THEN
1414 if g_debug then
1415 hr_utility.set_location('hxt_time_summary.time_in_dates',20);
1416 end if;
1417 time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
1418 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1419 if g_debug then
1420 hr_utility.trace('time_out :'
1421 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1422 end if;
1423 ELSE --IF ln_start >= ln_stop
1424 if g_debug then
1425 hr_utility.set_location('hxt_time_summary.time_in_dates',30);
1426 end if;
1427 time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1428 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1429 if g_debug then
1430 hr_utility.trace('time_out :'
1431 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1432 end if;
1433 END IF;
1434
1435 IF ln_carryover is NOT NULL THEN
1436 if g_debug then
1437 hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1438 end if;
1439 IF ln_start < ln_carryover THEN
1440 if g_debug then
1441 hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1442 end if;
1443 carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
1444 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1445
1446 if g_debug then
1447 hr_utility.trace('carryover_time :'
1448 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1449 end if;
1450 ELSE --IF ln_start >= ln_carryover
1451 if g_debug then
1452 hr_utility.set_location('hxt_time_summary.time_in_dates',50);
1453 end if;
1454 carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1455 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1456
1457 if g_debug then
1458 hr_utility.trace('carryover_time :'
1459 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1460 end if;
1461 END IF;
1462
1463 END IF;
1464
1465 END;
1466 */
1467
1468 FUNCTION sort (segment_chunks in t_date, p_order in Varchar2)
1469 RETURN t_date IS
1470
1471 sorted_chunks T_DATE;
1472 v_temp date;
1473
1474 BEGIN
1475
1476 if g_debug then
1477 hr_utility.set_location('hxt_time_summary.sort',10);
1478 end if;
1479 if segment_chunks.count > 0 then
1480 For i in segment_chunks.first .. segment_chunks.LAST
1481 Loop
1482 if g_debug then
1483 hr_utility.set_location('hxt_time_summary.sort',20);
1484 end if;
1485 sorted_chunks(i):= segment_chunks(i);
1486 End loop;
1487 end if;
1488
1489 if g_debug then
1490 hr_utility.trace('FYI');
1491 end if;
1492 if sorted_chunks.count <> 0 then
1493 if g_debug then
1494 hr_utility.set_location('hxt_time_summary.sort',30);
1495 end if;
1496 for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
1497 if g_debug then
1498 hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
1499 end if;
1500 end loop;
1501 end if;
1502 if g_debug then
1503 hr_utility.trace('END FYI');
1504 end if;
1505 For i in sorted_chunks.First+1 .. sorted_chunks.LAST
1506 Loop
1507 if g_debug then
1508 hr_utility.set_location('hxt_time_summary.sort',40);
1509 end if;
1510 v_temp:= sorted_chunks(i);
1511 if g_debug then
1512 hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
1513 end if;
1514 <<inner_loop>>
1515 if g_debug then
1516 hr_utility.set_location('hxt_time_summary.sort',50);
1517 end if;
1518 For j in REVERSE sorted_chunks.First .. (i-1)
1519 Loop
1520 if g_debug then
1521 hr_utility.set_location('hxt_time_summary.sort',60);
1522 hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
1523 end if;
1524 If sorted_chunks(j) >= v_temp then
1525 if g_debug then
1526 hr_utility.set_location('hxt_time_summary.sort',70);
1527 end if;
1528 sorted_chunks(j+1) := sorted_chunks(j);
1529 sorted_chunks(j) := v_temp;
1530 end if;
1531 if g_debug then
1532 hr_utility.set_location('hxt_time_summary.sort',80);
1533 end if;
1534 end loop inner_loop;
1535 if g_debug then
1536 hr_utility.set_location('hxt_time_summary.sort',90);
1537 end if;
1538 end loop;
1539 IF p_order ='ASC' then
1540 if g_debug then
1541 hr_utility.set_location('hxt_time_summary.sort',100);
1542 end if;
1543 RETURN sorted_chunks;
1544 END IF;
1545 if g_debug then
1546 hr_utility.set_location('hxt_time_summary.sort',110);
1547 end if;
1548 END;
1549
1550 FUNCTION segment_earning (segment_start in date
1551 ,segment_stop in date
1552 ,sdp_earning_type out nocopy number
1553 ,sdovr_earning_type out nocopy number
1554 )
1555 RETURN BOOLEAN IS
1556
1557 Cursor sd_earning is
1558 select sdr.start_time
1559 ,sdr.stop_time
1560 ,sdr.carryover_time
1561 ,sdr.element_type_id
1562 from hxt_shift_diff_rules sdr
1563 where sdr.sdp_id = g_sdp_id
1564 and g_date_worked between sdr.effective_start_date
1565 and sdr.effective_end_date;
1566
1567 sdp_start hxt_shift_diff_rules.start_time%TYPE;
1568 sdp_stop hxt_shift_diff_rules.stop_time%TYPE;
1569 sdp_carryover hxt_shift_diff_rules.carryover_time%TYPE;
1570 sdp_earning hxt_shift_diff_rules.element_type_id%TYPE;
1571 sdp_start_time DATE;
1572 sdp_stop_time DATE;
1573 sdp_carryover_time DATE;
1574
1575 worked_time_in DATE := p_shift_adjusted_time_in;
1576 worked_time_out DATE := p_shift_adjusted_time_out;
1577
1578 wp_start NUMBER;
1579 wp_stop NUMBER;
1580 ld_wp_start DATE;
1581 ld_wp_stop DATE;
1582 ld_carryover1 DATE;
1583
1584 lv_date_worked DATE;
1585 wp_date_worked DATE;
1586
1587 sdp_earning_found VARCHAR2(1) := 'N';
1588 elig_for_sdovr VARCHAR2(1) := 'N';
1589 elig_for_sdovr1 VARCHAR2(1) := 'N';
1590 elig_for_sdovr2 VARCHAR2(1) := 'N';
1591 elig_for_sdovr3 VARCHAR2(1) := 'N';
1592
1593 l_proc VARCHAR2(50) ;
1594
1595 ---------------------------segment_earning local functions---------------------
1596 FUNCTION check_eligibility(lv_time_in in date
1597 ,lv_time_out in date
1598 ,lv_segment_start in date
1599 ,lv_segment_stop in date)
1600 RETURN BOOLEAN is
1601
1602 --This function is used to check whether employee actually worked for
1603 --this chunk or not ,i.e., whether this chunk is between the time_in and
1604 --time_out.
1605 --The | in the comments shows where midnight falls.
1606 --This function is also used to check whether this chunk falls outside the
1607 --regular shift. If yes then pay override earning for this chunk.
1608
1609 BEGIN
1610
1611
1612 if g_debug then
1613
1614 hr_utility.set_location('hxt_time_summary.check_eligibility',10);
1615 end if;
1616
1617 IF (
1618 (lv_time_in <= lv_segment_start
1619 AND lv_segment_start <= lv_time_out
1620 AND lv_time_in <= lv_segment_stop
1621 AND lv_segment_stop <= lv_time_out)
1622 -- lv_time_in lv_segment_start lv_segment_stop lv_time_out |
1623 OR (lv_segment_start < lv_time_in
1624 AND lv_segment_start < lv_time_out
1625 AND lv_segment_stop < lv_time_in
1626 AND lv_segment_stop < lv_time_out
1627 AND lv_time_in > lv_time_out)
1628 -- lv_time_in | lv_segment_start lv_segment_stop lv_time_out
1629 OR (lv_time_in <= lv_segment_start
1630 AND lv_time_in < lv_segment_stop
1631 AND lv_time_in > lv_time_out )
1632 -- lv_time_in lv_segment_start lv_segment_stop | lv_time_out
1633 OR (lv_time_in <= lv_segment_start
1634 AND lv_segment_stop <= lv_time_out
1635 AND lv_segment_start > lv_segment_stop
1636 AND lv_time_in > lv_segment_stop
1637 AND lv_time_in > lv_time_out)
1638 -- lv_time_in lv_segment_start | lv_segment_stop lv_time_out
1639 )
1640 THEN
1641 if g_debug then
1642 hr_utility.set_location('hxt_time_summary.check_eligibility',20);
1643 end if;
1644 RETURN TRUE;
1645 ELSE
1646 if g_debug then
1647 hr_utility.set_location('hxt_time_summary.check_eligibility',30);
1648 end if;
1649 RETURN FALSE;
1650 END IF;
1651 if g_debug then
1652 hr_utility.set_location('hxt_time_summary.check_eligibility',40);
1653 end if;
1654 END;
1655
1656 ----------------------------segment_earning main module-------------------------
1657
1658 BEGIN
1659
1660 if g_debug then
1661 l_proc := 'hxt_time_summary.segment_earning';
1662 hr_utility.set_location(l_proc,10);
1663
1664 -- hr_utility.trace('worked_time_in :'||worked_time_in);
1665 -- hr_utility.trace('worked_time_out :'||worked_time_out);
1666 -- hr_utility.trace('segment_start :'||segment_start);
1667 -- hr_utility.trace('segment_stop :'||segment_stop);
1668
1669 hr_utility.trace('worked_time_in :'
1670 ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
1671 hr_utility.trace('worked_time_out :'
1672 ||to_char(worked_time_out,'DD-MON-YYYY HH24:MI:SS'));
1673 hr_utility.trace('segment_start :'
1674 ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
1675 hr_utility.trace('segment_stop :'
1676 ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
1677 end if;
1678 --First check whether employee worked for this chunk or not ,i.e., whether
1679 --this chunk is between the worked_time_in and worked_time_out.
1680 --The | in the comments shows where midnight falls.
1681
1682 IF check_eligibility(worked_time_in
1683 ,worked_time_out
1684 ,segment_start
1685 ,segment_stop) THEN
1686
1687 if g_debug then
1688 hr_utility.set_location(l_proc,20);
1689 end if;
1690 -- Check whether eligible for shift override
1691 if g_debug then
1692 hr_utility.set_location(l_proc,30);
1693 end if;
1694 open get_work_plan;
1695 fetch get_work_plan into wp_start,wp_stop;
1696 if g_debug then
1697 hr_utility.trace('wp_start :'||wp_start);
1698 hr_utility.trace('wp_stop :'||wp_stop);
1699 end if;
1700 close get_work_plan;
1701
1702 wp_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
1703
1704 FOR i in 1 .. 3 LOOP
1705
1706 if g_debug then
1707 hr_utility.set_location(l_proc,40);
1708 hr_utility.trace('wp_date_worked :'
1709 || to_char(wp_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1710 end if;
1711 time_in_dates(wp_start
1712 ,wp_stop
1713 ,null
1714 ,ld_wp_start
1715 ,ld_wp_stop
1716 ,ld_carryover1
1717 ,wp_date_worked);
1718
1719 if g_debug then
1720 hr_utility.set_location(l_proc,50);
1721 hr_utility.trace('ld_wp_start :'
1722 || to_char(ld_wp_start,'DD-MON-YYYY HH24:MI:SS'));
1723 hr_utility.trace('ld_wp_stop :'
1724 || to_char(ld_wp_stop,'DD-MON-YYYY HH24:MI:SS'));
1725 hr_utility.trace('ld_carryover1:'
1726 || to_char(ld_carryover1,'DD-MON-YYYY HH24:MI:SS'));
1727 end if;
1728 IF g_sdovr_id is NOT NULL THEN
1729
1730 if g_debug then
1731 hr_utility.set_location(l_proc,60);
1732
1733 hr_utility.trace('segment_start :'
1734 ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
1735 hr_utility.trace('segment_stop :'
1736 ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
1737 end if;
1738 IF check_eligibility(ld_wp_start
1739 ,ld_wp_stop
1740 ,segment_start
1741 ,segment_stop) THEN
1742 if g_debug then
1743 hr_utility.set_location(l_proc,70);
1744 end if;
1745 -- the chunk falls within the regular shift.
1746 -- So , not eligible for shift override.
1747 -- sdovr_earning_type := null;
1748 elig_for_sdovr := 'N';
1749 if g_debug then
1750 hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
1751 end if;
1752 ELSE
1753 if g_debug then
1754 hr_utility.set_location(l_proc,80);
1755 end if;
1756 -- sdovr_earning_type := g_sdovr_id;
1757 --if g_debug then
1758 -- hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
1759 --end if;
1760 elig_for_sdovr := 'Y';
1761 END IF;
1762
1763 if g_debug then
1764 hr_utility.set_location(l_proc,90);
1765 end if;
1766
1767 -- Check if eligible before midnight
1768 IF i = 1 THEN
1769 if g_debug then
1770 hr_utility.set_location(l_proc,100);
1771 end if;
1772 IF elig_for_sdovr = 'Y' THEN
1773 if g_debug then
1774 hr_utility.set_location(l_proc,110);
1775 end if;
1776 elig_for_sdovr1 := 'Y';
1777 END IF;
1778 if g_debug then
1779 hr_utility.set_location(l_proc,120);
1780 end if;
1781 END IF;
1782
1783 -- Now Check if eligible on current day
1784 IF i = 2 THEN
1785 if g_debug then
1786 hr_utility.set_location(l_proc,130);
1787 end if;
1788 IF elig_for_sdovr = 'Y' THEN
1789 if g_debug then
1790 hr_utility.set_location(l_proc,140);
1791 end if;
1792 elig_for_sdovr2 := 'Y';
1793 END IF;
1794 if g_debug then
1795 hr_utility.set_location(l_proc,145);
1796 end if;
1797 END IF;
1798
1799 -- Now Check if eligible after midnight
1800 IF i = 3 THEN
1801 if g_debug then
1802 hr_utility.set_location(l_proc,150);
1803 end if;
1804 IF elig_for_sdovr = 'Y' THEN
1805 if g_debug then
1806 hr_utility.set_location(l_proc,160);
1807 end if;
1808 elig_for_sdovr3 := 'Y';
1809 END IF;
1810 if g_debug then
1811 hr_utility.set_location(l_proc,170);
1812 end if;
1813 END IF;
1814
1815 -- If eligible for both before,current day and after the midnight then
1816 -- pay the shift diff override
1817 IF elig_for_sdovr1= 'Y' and elig_for_sdovr2 = 'Y' and
1818 elig_for_sdovr3 = 'Y' THEN
1819 if g_debug then
1820 hr_utility.set_location(l_proc,180);
1821 end if;
1822 sdovr_earning_type := g_sdovr_id;
1823 ELSE
1824 if g_debug then
1825 hr_utility.set_location(l_proc,190);
1826 end if;
1827 sdovr_earning_type := null;
1828 END IF;
1829
1830 if g_debug then
1831 hr_utility.set_location(l_proc,200);
1832 end if;
1833 wp_date_worked := TRUNC(wp_date_worked + 1, 'DD');
1834
1835 END IF;
1836
1837 if g_debug then
1838 hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
1839 hr_utility.set_location(l_proc,210);
1840 end if;
1841 END LOOP;
1842
1843 if g_debug then
1844 hr_utility.set_location(l_proc,215);
1845 hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
1846 hr_utility.trace('sdp_earning_type :'|| sdp_earning_type);
1847 end if;
1848 -- BUG 2721493
1849 -- Now before proceeding to calculate the shift diff premium
1850 -- check that the employee is not eligible for any shift diff override.
1851 -- If eligible for a shift diff override then no need to evaluate the shift
1852 -- diff premiums since the shift diff Override overrides all the other
1853 -- premiums for this chunk.
1854
1855 IF sdovr_earning_type IS NULL THEN
1856
1857 if g_debug then
1858 hr_utility.set_location(l_proc,230);
1859 end if;
1860 -- Check whether eligible for shift diff premium
1861 open sd_earning;
1862
1863 LOOP
1864 if g_debug then
1865 hr_utility.set_location(l_proc,240);
1866 end if;
1867 fetch sd_earning into sdp_start,sdp_stop,sdp_carryover,sdp_earning;
1868
1869 Exit when sd_earning%NOTFOUND;
1870
1871 if g_debug then
1872 hr_utility.trace('sdp_start :'||sdp_start);
1873 hr_utility.trace('sdp_stop :'||sdp_stop);
1874 hr_utility.trace('sdp_carryover :'||sdp_carryover);
1875 hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
1876
1877 hr_utility.trace('p_shift_adjusted_time_in :'
1878 ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
1879 hr_utility.trace('p_shift_adjusted_time_out:'
1880 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
1881 end if;
1882 lv_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
1883
1884 -- Loop through for the day before, the current day and the day after
1885 FOR i in 1 .. 3 LOOP
1886 if g_debug then
1887 hr_utility.set_location(l_proc,250);
1888 hr_utility.trace('lv_date_worked :'
1889 ||to_char(lv_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1890 end if;
1891 time_in_dates(sdp_start
1892 ,sdp_stop
1893 ,sdp_carryover
1894 ,sdp_start_time
1895 ,sdp_stop_time
1896 ,sdp_carryover_time
1897 ,lv_date_worked
1898 );
1899
1900 if g_debug then
1901 hr_utility.trace('sdp_start_time :'
1902 || to_char(sdp_start_time,'DD-MON-YYYY HH24:MI:SS'));
1903 hr_utility.trace('sdp_stop_time :'
1904 || to_char(sdp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
1905 hr_utility.trace('sdp_carryover_time :'
1906 || to_char(sdp_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1907 hr_utility.set_location(l_proc,260);
1908
1909 hr_utility.trace('g_sdf_rule_completed :'
1910 || g_sdf_rule_completed);
1911 hr_utility.trace('g_sdf_carryover :'
1912 || to_char(g_sdf_carryover,'DD-MON-YYYY HH24:MI:SS'));
1913 end if;
1914 IF g_sdf_rule_completed = 'Y' THEN
1915 if g_debug then
1916 hr_utility.set_location(l_proc,270);
1917 end if;
1918 if segment_start >= sdp_start_time and
1919 segment_start < sdp_stop_time then
1920 if g_debug then
1921 hr_utility.set_location(l_proc,280);
1922 end if;
1923 sdp_earning_type := sdp_earning;
1924 sdp_earning_found := 'Y';
1925
1926 if segment_stop = sdp_carryover_time then
1927 if g_debug then
1928 hr_utility.set_location(l_proc,290);
1929 end if;
1930 g_sdf_rule_completed := 'Y';
1931 g_sdf_carryover := null;
1932 if g_debug then
1933 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
1934 end if;
1935 else
1936 if g_debug then
1937 hr_utility.set_location(l_proc,300);
1938 end if;
1939 g_sdf_rule_completed := 'N';
1940 g_sdf_carryover := sdp_carryover_time;
1941 if g_debug then
1942 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
1943 hr_utility.set_location(l_proc,310);
1944 end if;
1945 end if;
1946
1947 if g_debug then
1948 hr_utility.set_location(l_proc,320);
1949 end if;
1950 exit;
1951 else
1952 if g_debug then
1953 hr_utility.set_location(l_proc,330);
1954 end if;
1955 sdp_earning_type := null;
1956 end if;
1957
1958 if g_debug then
1959 hr_utility.set_location(l_proc,340);
1960 end if;
1961 ELSIF g_sdf_rule_completed = 'N' THEN
1962
1963 if g_debug then
1964 hr_utility.set_location(l_proc,350);
1965 end if;
1966 if g_sdf_carryover = sdp_carryover_time then
1967
1968 if g_debug then
1969 hr_utility.set_location(l_proc,360);
1970 end if;
1971 sdp_earning_type := sdp_earning;
1972 sdp_earning_found := 'Y';
1973
1974 if segment_stop = sdp_carryover_time then
1975 if g_debug then
1976 hr_utility.set_location(l_proc,370);
1977 end if;
1978 g_sdf_rule_completed := 'Y';
1979 g_sdf_carryover := null;
1980 if g_debug then
1981 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
1982 end if;
1983 else
1984 if g_debug then
1985 hr_utility.set_location(l_proc,380);
1986 end if;
1987 g_sdf_rule_completed := 'N';
1988 if g_debug then
1989 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
1990 end if;
1991 end if;
1992
1993 if g_debug then
1994 hr_utility.set_location(l_proc,390);
1995 end if;
1996 exit;
1997 else
1998 if g_debug then
1999 hr_utility.set_location(l_proc,400);
2000 end if;
2001 sdp_earning_type := null;
2002 end if;
2003
2004 if g_debug then
2005 hr_utility.set_location(l_proc,410);
2006 end if;
2007 END IF;
2008
2009 lv_date_worked := TRUNC(lv_date_worked + 1, 'DD');
2010
2011 if g_debug then
2012 hr_utility.set_location(l_proc,420);
2013 end if;
2014 END LOOP;
2015
2016 if g_debug then
2017 hr_utility.set_location(l_proc,430);
2018 end if;
2019 IF sdp_earning_found = 'Y' THEN
2020 if g_debug then
2021 hr_utility.set_location(l_proc,440);
2022 end if;
2023 EXIT;
2024 END IF;
2025 if g_debug then
2026 hr_utility.set_location(l_proc,450);
2027 end if;
2028 END LOOP;
2029
2030 if g_debug then
2031 hr_utility.set_location(l_proc,460);
2032 end if;
2033 close sd_earning;
2034
2035 ELSE
2036
2037 if g_debug then
2038 hr_utility.set_location(l_proc,465);
2039 end if;
2040 sdp_earning_type := null;
2041
2042 END IF;
2043
2044 if g_debug then
2045 hr_utility.set_location(l_proc,470);
2046 end if;
2047 RETURN TRUE;
2048
2049 ELSE
2050 if g_debug then
2051 hr_utility.set_location(l_proc,480);
2052 end if;
2053 RETURN FALSE;
2054 END IF; -- check_eligibility
2055
2056 if g_debug then
2057
2058 hr_utility.set_location(l_proc,490);
2059 end if;
2060 END; -- segment_earning
2061
2062 FUNCTION segment_start_in_rule
2063 (p_rule_earning_type OUT NOCOPY NUMBER
2064 ,p_carryover OUT NOCOPY NUMBER)
2065 RETURN BOOLEAN IS
2066
2067 -- Checks to see if the segment start time falls within a shift diff rule.
2068 -- If so, returns the earning type of the rule and the carryover time for
2069 -- calculating the end of the segment.Returns true if the start is within a
2070 -- rule, false if it is not.The | in the comments shows where midnight falls.
2071 -- The segment start and stop are both dates while sdr.start and stop are
2072 -- numbers.
2073
2074 CURSOR sd_rules IS
2075 SELECT sdr.element_type_id
2076 ,sdr.carryover_time
2077 FROM hxt_shift_diff_rules sdr
2078 WHERE sdr.sdp_id = g_sdp_id
2079 AND g_DATE_WORKED BETWEEN
2080 sdr.effective_start_date AND sdr.effective_end_date
2081 AND ( (sdr.start_time <= to_number(to_char(segment_start_time, 'HH24MI'))
2082 AND to_number(to_char(segment_start_time, 'HH24MI')) < sdr.stop_time)
2083 -- sdr.start segment.start sdr.stop |
2084 OR ((to_number(to_char(segment_start_time, 'HH24MI')) <= sdr.start_time)
2085 AND to_number(to_char(segment_start_time, 'HH24MI')) < sdr.stop_time
2086 AND sdr.start_time > sdr.stop_time)
2087 -- sdr.start | segment.start sdr.stop
2088 OR (sdr.start_time <= to_number(to_char(segment_start_time, 'HH24MI'))
2089 AND sdr.start_time > sdr.stop_time) );
2090 -- sdr.start segment.start | sdr.stop
2091
2092 BEGIN
2093
2094 if g_debug then
2095 hr_utility.set_location('hxt_time_summary.segment_start_in_rule',10);
2096 end if;
2097 OPEN sd_rules;
2098 FETCH sd_rules INTO p_rule_earning_type, p_carryover;
2099 if g_debug then
2100 hr_utility.trace('p_rule_earning_type :'||p_rule_earning_type);
2101 hr_utility.trace('p_carryover :'||p_carryover);
2102 end if;
2103 IF sd_rules%NOTFOUND THEN
2104 if g_debug then
2105 hr_utility.set_location('hxt_time_summary.segment_start_in_rule',20);
2106 end if;
2107 CLOSE sd_rules;
2108 RETURN FALSE;
2109 END IF;
2110 if g_debug then
2111 hr_utility.set_location('hxt_time_summary.segment_start_in_rule',30);
2112 end if;
2113 CLOSE sd_rules;
2114 RETURN TRUE;
2115
2116 END;
2117
2118 FUNCTION rule_start_in_segment( p_rule_start OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2119
2120 -- Checks to see if a shift diff rule starts within the time segment being
2121 -- generated.This is only called if it is already determined that the start of
2122 -- the segment does not fall within any rule.
2123
2124 CURSOR sd_rules IS
2125 SELECT sdr.start_time
2126 FROM hxt_shift_diff_rules sdr
2127 WHERE sdr.sdp_id = g_sdp_id
2128 AND g_DATE_WORKED BETWEEN
2129 sdr.effective_start_date AND sdr.effective_end_date
2130 AND ( ( to_number(to_char(segment_start_time, 'HH24MI')) < sdr.start_time
2131 AND sdr.start_time < to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) )
2132 -- segment.start sdr.start segment.stop |
2133 OR (to_number(to_char(segment_start_time, 'HH24MI')) > sdr.start_time
2134 AND sdr.start_time < to_number(to_char(p_shift_adjusted_time_out, 'HH24MI'))
2135 AND to_number(to_char(segment_start_time, 'HH24MI')) >
2136 to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) )
2137 -- segment.start | sdr.start segment.stop
2138 OR ( to_number(to_char(segment_start_time, 'HH24MI')) < sdr.start_time
2139 AND to_number(to_char(segment_start_time, 'HH24MI')) >
2140 to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) ));
2141 -- segment.start sdr.start | segment.stop
2142
2143 BEGIN
2144
2145 if g_debug then
2146 hr_utility.set_location('hxt_time_summary.rule_start_in_segment',10);
2147 end if;
2148 OPEN sd_rules;
2149 FETCH sd_rules INTO p_rule_start;
2150 if g_debug then
2151 hr_utility.trace('p_rule_start :'||p_rule_start);
2152 end if;
2153 IF sd_rules%NOTFOUND THEN
2154 if g_debug then
2155 hr_utility.set_location('hxt_time_summary.rule_start_in_segment',20);
2156 end if;
2157 CLOSE sd_rules;
2158 RETURN FALSE;
2159 END IF;
2160 CLOSE sd_rules;
2161 RETURN TRUE;
2162
2163 END;
2164
2165 FUNCTION set_stop_and_hours
2166 (p_segment_stop_time IN OUT NOCOPY DATE
2167 ,p_hours_worked IN OUT NOCOPY NUMBER) RETURN NUMBER IS
2168
2169 -- Sets the stop time and hours worked of a segment if the start of the segment
2170 -- is within a shift diff rule.The stop time is the earliest of the input time
2171 -- out or the carryover time of the applicable rule.Segment start and stop
2172 -- times are dates which include the day.Carryover and rule start stop times
2173 -- are numbers.Diagrams showing relative times to midnight are S segment start,
2174 -- ST time out of input record, C carryover time,| midnight.
2175
2176 time_in_hours NUMBER := to_number(to_char(segment_start_time, 'HH24MI'));
2177 time_out_hours NUMBER := to_number(to_char(g_time_out, 'HH24MI'));
2178
2179 BEGIN
2180
2181 if g_debug then
2182 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',10);
2183 end if;
2184 p_segment_stop_time := NULL;
2185 if g_debug then
2186 hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
2187 hr_utility.trace('time_out_hours :'||time_out_hours);
2188 hr_utility.trace('time_in_hours :'||time_in_hours);
2189 hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
2190 hr_utility.trace('segment_start_time :'||segment_start_time);
2191 end if;
2192 IF sd_rule_carryover >= time_out_hours THEN
2193 if g_debug then
2194 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
2195 end if;
2196 IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
2197 if g_debug then
2198 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
2199 end if;
2200 -- | S ST C S | ST C
2201 p_segment_stop_time := p_shift_adjusted_time_out;
2202 if g_debug then
2203 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2204 end if;
2205 ELSIF time_in_hours >= time_out_hours THEN
2206 if g_debug then
2207 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',40);
2208 end if;
2209 -- S C | ST
2210 p_segment_stop_time := segment_start_time +
2211 ((hxt_util.time_to_hours(sd_rule_carryover) -
2212 hxt_util.time_to_hours(time_in_hours)) / 24);
2213 if g_debug then
2214 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2215 end if;
2216 END IF;
2217 ELSE -- carryover < time_out_hours
2218 IF time_in_hours < sd_rule_carryover THEN
2219 if g_debug then
2220 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',50);
2221 end if;
2222 -- S C ST |
2223 p_segment_stop_time := segment_start_time +
2224 ((hxt_util.time_to_hours(sd_rule_carryover) -
2225 hxt_util.time_to_hours(time_in_hours)) / 24);
2226 if g_debug then
2227 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2228 end if;
2229
2230 ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours > time_out_hours)) THEN
2231 if g_debug then
2232 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',60);
2233 end if;
2234 -- S | C ST
2235 p_segment_stop_time := segment_start_time +
2236 ((hxt_util.time_to_hours(sd_rule_carryover) +
2237 (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
2238 if g_debug then
2239 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2240 end if;
2241 ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours < time_out_hours)) THEN
2242 if g_debug then
2243 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',70);
2244 end if;
2245 -- S ST | C
2246 p_segment_stop_time := p_shift_adjusted_time_out;
2247 if g_debug then
2248 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2249 end if;
2250 ELSIF ((time_in_hours > sd_rule_carryover)
2251 AND (time_in_hours = time_out_hours)) THEN --SIR523
2252 -- S ST | C
2253 if g_debug then
2254 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',80);
2255 end if;
2256 p_segment_stop_time := p_shift_adjusted_time_out;
2257 if g_debug then
2258 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2259 end if;
2260 ELSIF time_in_hours = sd_rule_carryover THEN
2261 if g_debug then
2262 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
2263 end if;
2264 p_segment_stop_time := p_shift_adjusted_time_out;
2265 if g_debug then
2266 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2267 end if;
2268 END IF;
2269 END IF;
2270
2271 p_hours_worked := ((p_segment_stop_time - segment_start_time) * 24);
2272 if g_debug then
2273 hr_utility.trace('p_hours_worked :'||p_hours_worked);
2274 end if;
2275
2276 IF p_segment_stop_time IS NULL THEN
2277 if g_debug then
2278 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',100);
2279 end if;
2280 FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
2281 RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
2282 --2278400 RETURN call_gen_error(location, ''); -- HXT11
2283 END IF;
2284
2285 RETURN 0;
2286
2287 END;
2288
2289 FUNCTION set_stop_at_rule(p_segment_stop_time OUT NOCOPY DATE
2290 ,p_hours_worked OUT NOCOPY NUMBER) RETURN NUMBER IS
2291 -- Sets the segment stop time and hours worked when a segment starts with no
2292 -- shift diff rule but a rule starts before the segment would end.The stop
2293 -- time is the rule start time.
2294
2295 time_in_hours NUMBER := to_number(to_char(segment_start_time, 'HH24MI'));
2296 time_out_hours NUMBER := to_number(to_char(g_time_out, 'HH24MI'));
2297 l_stop_time DATE; -- local to use to set p_segment_stop_time
2298
2299 BEGIN
2300
2301 if g_debug then
2302 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',10);
2303 end if;
2304 l_stop_time := NULL;
2305
2306 if g_debug then
2307 hr_utility.trace('sd_rule_start :'||sd_rule_start);
2308 hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2309 end if;
2310 IF sd_rule_start > time_in_hours THEN
2311 if g_debug then
2312 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
2313 hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2314 end if;
2315 l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
2316 - hxt_util.time_to_hours(time_in_hours)) / 24);
2317 -- | Segment.start Rule.start or Segment.start Rule.start |
2318 if g_debug then
2319 hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2320 end if;
2321 ELSE
2322 if g_debug then
2323 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
2324 hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2325 end if;
2326 l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
2327 (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
2328 -- Segment.start | Rule.start
2329 if g_debug then
2330 hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2331 end if;
2332 END IF;
2333
2334 if g_debug then
2335 hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2336 hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2337 end if;
2338 p_hours_worked := ((l_stop_time - segment_start_time) * 24);
2339 p_segment_stop_time := l_stop_time;
2340 if g_debug then
2341 hr_utility.trace('p_hours_worked :'||to_char(p_hours_worked,'DD-MON-YYYY HH24:MI:SS'));
2342 hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2343 end if;
2344 IF l_stop_time IS NULL THEN
2345 if g_debug then
2346 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',40);
2347 end if;
2348 FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
2349 RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
2350 --2278400 RETURN call_gen_error(location, ''); -- HXT11
2351 END IF;
2352
2353 RETURN 0;
2354
2355 END;
2356 ----------------------------GEN DETAILS MAIN MODULE----------------------------
2357 BEGIN
2358 g_debug :=hr_utility.debug_enabled;
2359 if g_debug then
2360 hr_utility.set_location('hxt_time_summary.gen_details',10);
2361 end if;
2362 IF segment_chunks.count > 0 THEN
2363 if g_debug then
2364 hr_utility.set_location('hxt_time_summary.gen_details',11);
2365 end if;
2366 FOR l in segment_chunks.first .. segment_chunks.last LOOP
2367 if g_debug then
2368 hr_utility.set_location('hxt_time_summary.gen_details',12);
2369 end if;
2370 segment_chunks(l) := null;
2371 END LOOP;
2372 segment_chunks.delete;
2373 if g_debug then
2374 hr_utility.set_location('hxt_time_summary.gen_details',13);
2375 end if;
2376 END IF;
2377
2378 IF sorted_chunks.count > 0 THEN
2379 if g_debug then
2380 hr_utility.set_location('hxt_time_summary.gen_details',14);
2381 end if;
2382 FOR l in sorted_chunks.first .. sorted_chunks.last LOOP
2383 if g_debug then
2384 hr_utility.set_location('hxt_time_summary.gen_details',15);
2385 end if;
2386 sorted_chunks(l) := null;
2387 END LOOP;
2388 sorted_chunks.delete;
2389 if g_debug then
2390 hr_utility.set_location('hxt_time_summary.gen_details',16);
2391 end if;
2392 END IF;
2393
2394 -- Takes the incoming time in and out from the summary record and splits into
2395 -- segments corresponding to the rules of the applicable shift diff policy.
2396 -- Sends the segments to Gen Special for generating.The segment start and stop
2397 -- are dates which include the day for processing times that span midnight.
2398 -- Returns 0 for normal,2 for error.
2399
2400 -- IF g_sdp_id IS NULL OR g_TIME_IN IS NULL THEN
2401 -- If there is no shift diff policy or only hours entered
2402
2403 if g_debug then
2404 hr_utility.trace('g_sdp_id :'||g_sdp_id);
2405 hr_utility.trace('g_TIME_IN :'
2406 ||to_char(g_time_in,'DD-MON-YYYY HH24:MI:SS'));
2407 hr_utility.trace('g_element_type_id :'||g_element_type_id);
2408 end if;
2409 IF g_sdp_id IS NULL OR g_TIME_IN IS NULL
2410 OR (g_element_type_id IS NOT NULL AND g_CALL_ADJUST_ABS = 'Y') THEN
2411 if g_debug then
2412 hr_utility.set_location('hxt_time_summary.gen_details',20);
2413 end if;
2414 -- If there is no shift diff policy or only hours entered or override hours
2415 -- type entered
2416 segment_start_time := p_shift_adjusted_time_in;
2417 segment_stop_time := p_shift_adjusted_time_out;
2418 hours_worked := g_hours;
2419 return_code := gen_special(location
2420 ,segment_start_time
2421 ,segment_stop_time
2422 ,hours_worked
2423 ,NULL
2424 ,NULL);
2425 ELSE
2426
2427 if g_debug then
2428 hr_utility.set_location('hxt_time_summary.gen_details',21);
2429 end if;
2430
2431 segment_start_time := p_shift_adjusted_time_in;
2432 segment_stop_time := p_shift_adjusted_time_out;
2433
2434 -- segment_start := to_number(to_char(p_shift_adjusted_time_in,'HH24MI'));
2435 -- segment_stop := to_number(to_char(p_shift_adjusted_time_out,'HH24MI'));
2436
2437 if g_debug then
2438 hr_utility.trace('p_shift_adjusted_time_in :'
2439 ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
2440 hr_utility.trace('p_shift_adjusted_time_out:'
2441 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
2442
2443 hr_utility.trace('segment_start_time :'
2444 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2445 hr_utility.trace('segment_stop_time :'
2446 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2447
2448 hr_utility.set_location('hxt_time_summary.gen_details',22);
2449 end if;
2450 -- Insert these rows for hours worked start stop into the
2451 -- pl sql table segment_chunks.
2452 -- These start and stop times are entered into a single column
2453 -- and then sorted by the function sort.
2454
2455 populate_plsql_table(segment_start_time);
2456 populate_plsql_table(segment_stop_time);
2457
2458 -- Now insert the rows for work plan start stop
2459 -- into the pl sql table segment_chunks for day before , day worked and day
2460 -- after.
2461 open get_work_plan;
2462 fetch get_work_plan into ln_standard_start ,ln_standard_stop;
2463 if g_debug then
2464 hr_utility.trace('ln_standard_start :'|| ln_standard_start);
2465 hr_utility.trace('ln_standard_stop :'|| ln_standard_stop);
2466 end if;
2467 close get_work_plan;
2468
2469 wplan_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
2470 FOR i in 1 .. 3 LOOP
2471 if g_debug then
2472 hr_utility.trace('wplan_date_worked :'
2473 || to_char(wplan_date_worked,'DD-MON-YYYY HH24:MI:SS'));
2474 end if;
2475 time_in_dates(ln_standard_start
2476 ,ln_standard_stop
2477 ,null
2478 ,wp_start_time
2479 ,wp_stop_time
2480 ,ld_carryover2
2481 ,wplan_date_worked);
2482
2483 if g_debug then
2484 hr_utility.trace('wp_start_time :'
2485 || to_char(wp_start_time,'DD-MON-YYYY HH24:MI:SS'));
2486 hr_utility.trace('wp_stop_time :'
2487 || to_char(wp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2488 hr_utility.trace('ld_carryover2 :'
2489 || to_char(ld_carryover2,'DD-MON-YYYY HH24:MI:SS'));
2490 end if;
2491 populate_plsql_table(wp_start_time);
2492 populate_plsql_table(wp_stop_time);
2493
2494 if g_debug then
2495 hr_utility.set_location('hxt_time_summary.gen_details',23);
2496 end if;
2497 wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
2498 END LOOP;
2499
2500 if g_debug then
2501 hr_utility.set_location('hxt_time_summary.gen_details',23.4);
2502 end if;
2503 open get_sd_rules;
2504 LOOP
2505 -- fetch get_sd_rules into lv_sd_start,lv_sd_stop;
2506 fetch get_sd_rules into ln_sd_start,ln_sd_stop,ln_sd_carryover;
2507 Exit when get_sd_rules%NOTFOUND;
2508
2509 if g_debug then
2510 hr_utility.trace('ln_sd_start :'||ln_sd_start);
2511 hr_utility.trace('ln_sd_stop :'||ln_sd_stop);
2512 hr_utility.trace('ln_sd_carryover :'||ln_sd_carryover);
2513 end if;
2514 -- Populate the plsql table with the shift policy chunks appended
2515 -- with the dates for day before , current day and day after
2516 sd_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
2517
2518 FOR i in 1 .. 3 LOOP
2519 if g_debug then
2520 hr_utility.set_location('hxt_time_summary.gen_details',23.5);
2521 hr_utility.trace('sd_date_worked :'
2522 || to_char(sd_date_worked,'DD-MON-YYYY HH24:MI:SS'));
2523 end if;
2524 time_in_dates(ln_sd_start
2525 ,ln_sd_stop
2526 ,ln_sd_carryover
2527 ,sd_start_time
2528 ,sd_stop_time
2529 ,sd_carryover_time
2530 ,sd_date_worked);
2531
2532 if g_debug then
2533 hr_utility.trace('sd_start_time :'
2534 || to_char(sd_start_time,'DD-MON-YYYY HH24:MI:SS'));
2535 hr_utility.trace('sd_carryover_time :'
2536 || to_char(sd_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2537 end if;
2538 populate_plsql_table(sd_start_time);
2539 populate_plsql_table(sd_carryover_time);
2540
2541 if g_debug then
2542 hr_utility.set_location('hxt_time_summary.gen_details',23.6);
2543 end if;
2544 sd_date_worked := TRUNC(sd_date_worked + 1, 'DD');
2545
2546 END LOOP;
2547
2548 if g_debug then
2549 hr_utility.set_location('hxt_time_summary.gen_details',23.8);
2550 end if;
2551 END LOOP;
2552 close get_sd_rules;
2553
2554 if g_debug then
2555 hr_utility.set_location('hxt_time_summary.gen_details',24);
2556
2557 hr_utility.trace('FYI');
2558 end if;
2559 if segment_chunks.count <> 0 then
2560 if g_debug then
2561 hr_utility.set_location('hxt_time_summary.gen_details',26);
2562 end if;
2563 for l_cnt in segment_chunks.first .. segment_chunks.last loop
2564 if g_debug then
2565 hr_utility.trace('segment_chunks is:'
2566 ||to_char(segment_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2567 end if;
2568 end loop;
2569 end if;
2570 if g_debug then
2571 hr_utility.trace('END FYI');
2572 hr_utility.set_location('hxt_time_summary.gen_details',27);
2573 end if;
2574 -- Get the sorted pl sql table
2575 sorted_chunks := sort( segment_chunks , 'ASC');
2576
2577 if g_debug then
2578 hr_utility.trace('FYI');
2579 end if;
2580 if sorted_chunks.count <> 0 then
2581 if g_debug then
2582 hr_utility.set_location('hxt_time_summary.gen_details',28);
2583 end if;
2584 for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2585 if g_debug then
2586 hr_utility.trace('sorted_chunks is:'
2587 ||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2588 end if;
2589 end loop;
2590 if g_debug then
2591 hr_utility.set_location('hxt_time_summary.gen_details',29);
2592 end if;
2593 end if;
2594 if g_debug then
2595 hr_utility.trace('END FYI');
2596 end if;
2597
2598
2599 if sorted_chunks.count > 0 then
2600 if g_debug then
2601 hr_utility.set_location('hxt_time_summary.gen_details',30);
2602 end if;
2603 for i in sorted_chunks.first .. sorted_chunks.last-1 loop
2604 if g_debug then
2605 hr_utility.set_location('hxt_time_summary.gen_details',31);
2606 end if;
2607 chunk_start := sorted_chunks(i);
2608
2609 if g_debug then
2610 hr_utility.trace('chunk_start:'
2611 ||to_char(chunk_start,'DD-MON-YYYY HH24:MI:SS'));
2612 end if;
2613 /* if i = sorted_chunks.last then
2614 chunk_stop := sorted_chunks(sorted_chunks.first);
2615 if g_debug then
2616 hr_utility.trace('chunk_stop :'
2617 ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
2618 end if;
2619 else
2620 */
2621 chunk_stop := sorted_chunks(i+1);
2622 if g_debug then
2623 hr_utility.trace('chunk_stop :'
2624 ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
2625 end if;
2626 -- end if;
2627 if g_debug then
2628 hr_utility.set_location('hxt_time_summary.gen_details',32);
2629 end if;
2630 IF segment_earning(chunk_start
2631 ,chunk_stop
2632 ,p_sdp_earning_type
2633 ,p_sdovr_earning_type
2634 ) THEN
2635 if g_debug then
2636 hr_utility.set_location('hxt_time_summary.gen_details',33);
2637 end if;
2638 -- hours_worked:= ((chunk_stop - chunk_start) * 24);
2639 hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
2640
2641 if g_debug then
2642 hr_utility.trace('hours_worked :'|| hours_worked);
2643 hr_utility.set_location('hxt_time_summary.gen_details',34);
2644 end if;
2645 return_code := gen_special(location
2646 ,chunk_start
2647 ,chunk_stop
2648 ,hours_worked
2649 ,p_sdp_earning_type
2650 ,p_sdovr_earning_type);
2651 if g_debug then
2652 hr_utility.set_location('hxt_time_summary.gen_details',35);
2653 end if;
2654 END IF; -- segment_earning
2655
2656 end loop;
2657
2658 if g_debug then
2659 hr_utility.set_location('hxt_time_summary.gen_details',36);
2660 end if;
2661
2662 end if;
2663
2664
2665
2666 /*
2667
2668 if g_debug then
2669 hr_utility.set_location('hxt_time_summary.gen_details',30);
2670 end if;
2671 segment_stop_time := p_shift_adjusted_time_in;
2672 if g_debug then
2673 hr_utility.trace('p_shift_adjusted_time_in :'
2674 ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
2675 hr_utility.trace('p_shift_adjusted_time_out:'
2676 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
2677 hr_utility.trace('return_code :'
2678 ||return_code);
2679 hr_utility.trace('segment_stop_time :'
2680 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2681 end if;
2682 WHILE (segment_stop_time < p_shift_adjusted_time_out) AND return_code = 0
2683 LOOP
2684 if g_debug then
2685 hr_utility.set_location('hxt_time_summary.gen_details',40);
2686 end if;
2687 --while the end of the last segment is not end of time
2688 segment_start_time := segment_stop_time; -- start each segment with
2689 -- stop time of last
2690 if g_debug then
2691 hr_utility.trace('segment_start_time :'
2692 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2693 end if;
2694 IF segment_start_in_rule(sd_rule_earning,sd_rule_carryover) THEN
2695 if g_debug then
2696 hr_utility.set_location('hxt_time_summary.gen_details',50);
2697 end if;
2698 -- if the start is in a rule
2699 if g_debug then
2700 hr_utility.trace('segment_stop_time :'
2701 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2702 hr_utility.trace('hours_worked :'||hours_worked);
2703 end if;
2704 return_code := set_stop_and_hours(segment_stop_time,hours_worked);
2705 if g_debug then
2706 hr_utility.trace('segment_stop_time :'
2707 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2708 hr_utility.trace('hours_worked :'||hours_worked);
2709 hr_utility.trace('return_code :'||return_code);
2710 end if;
2711 ELSIF rule_start_in_segment(sd_rule_start) THEN
2712 if g_debug then
2713 hr_utility.set_location('hxt_time_summary.gen_details',60);
2714 end if;
2715 -- if a rule starts in the segment
2716 return_code := set_stop_at_rule(segment_stop_time,hours_worked);
2717 if g_debug then
2718 hr_utility.trace('segment_stop_time :'
2719 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2720 hr_utility.trace('hours_worked :'||hours_worked);
2721 hr_utility.trace('return_code :'||return_code);
2722 end if;
2723 sd_rule_earning := NULL;
2724
2725
2726 -- CHECK IF THERE IS A SHIFT DIFFERENTIAL OVERRIDE APPLICABLE TO THIS DAY
2727 ELSIF g_sdovr_id is NOT NULL THEN
2728
2729 open Get_Shift_stop_time( g_ASSIGNMENT_ID,g_DATE_WORKED );
2730 fetch Get_Shift_stop_time into lv_standard_stop;
2731 if g_debug then
2732 hr_utility.trace('lv_standard_stop :'||lv_standard_stop);
2733 end if;
2734 close Get_Shift_stop_time;
2735
2736 -- IF working from 0800 to 2300 then pay only regular hours
2737 -- from 1200 to 1700 (shift diff defined from 0800 to 1200) and
2738 -- shift diff Override from 1700 to 2300
2739 IF to_number(to_char(segment_stop_time,'HH24MI')) < lv_standard_stop THEN
2740 if g_debug then
2741 hr_utility.set_location('hxt_time_summary.gen_details',75);
2742 end if;
2743 -- segment_stop_time := lv_standard_stop;
2744 segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
2745 if g_debug then
2746 hr_utility.trace('segment_stop_time :'
2747 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2748 hr_utility.trace('segment_start_time :'
2749 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2750 end if;
2751 hours_worked:=((segment_stop_time - segment_start_time) * 24);
2752 if g_debug then
2753 hr_utility.trace('hours_worked :'||hours_worked);
2754 end if;
2755 sd_rule_earning := NULL;
2756 ELSE -- segment_stop_time >= lv_standard_stop
2757 if g_debug then
2758 hr_utility.set_location('hxt_time_summary.gen_details',76);
2759 end if;
2760 segment_stop_time := p_shift_adjusted_time_out;
2761 if g_debug then
2762 hr_utility.trace('segment_stop_time :'
2763 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2764 hr_utility.trace('segment_start_time :'
2765 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2766 end if;
2767 hours_worked:=((segment_stop_time - segment_start_time) * 24);
2768 if g_debug then
2769 hr_utility.trace('hours_worked :'||hours_worked);
2770 end if;
2771 -- IF the shift differential override is applicable to the time_in and
2772 -- time_outs ,then set p_sdf_id to g_sdovr_id so that the cursor
2773 -- cur_elig_prem in hxt_time_pay.gen_premiums returns a row and the
2774 -- logic gets called to insert the data into hxt_det_hours_worked
2775 sd_rule_earning := g_sdovr_id;
2776 END IF;
2777 if g_debug then
2778 hr_utility.set_location('hxt_time_summary.gen_details',77);
2779 end if;
2780 -- END g_sdovr_id is NOT NULL
2781
2782 ELSE
2783 -- NO SHIFT DIFF RULES APPLY
2784 if g_debug then
2785 hr_utility.trace('segment_start_time :'
2786 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2787 hr_utility.trace('segment_stop_time :'
2788 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2789 hr_utility.trace('p_shift_adjusted_time_out :'
2790 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
2791 hr_utility.set_location('hxt_time_summary.gen_details',78);
2792 end if;
2793 segment_stop_time := p_shift_adjusted_time_out;
2794 if g_debug then
2795 hr_utility.trace('segment_stop_time :'
2796 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2797 end if;
2798 hours_worked :=((segment_stop_time - segment_start_time) * 24);
2799 if g_debug then
2800 hr_utility.trace('segment_stop_time :'
2801 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2802 hr_utility.trace('hours_worked :'||hours_worked);
2803 end if;
2804 sd_rule_earning := NULL;
2805
2806 END IF;
2807
2808 if g_debug then
2809 hr_utility.trace('location :'||location);
2810 hr_utility.trace('segment_start_time :'
2811 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2812 hr_utility.trace('segment_stop_time :'
2813 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2814 hr_utility.trace('sd_rule_earning :'||sd_rule_earning);
2815 hr_utility.trace('return_code :'||return_code);
2816 end if;
2817 IF return_code = 0 THEN
2818 if g_debug then
2819 hr_utility.set_location('hxt_time_summary.gen_details',80);
2820 end if;
2821 loop_count := loop_count + 1;
2822 if g_debug then
2823 hr_utility.trace('loop_count :'||loop_count);
2824 end if;
2825 return_code := gen_special(location
2826 ,segment_start_time
2827 ,segment_stop_time
2828 ,hours_worked
2829 ,sd_rule_earning);
2830 END IF;
2831 IF loop_count > 50 THEN
2832 if g_debug then
2833 hr_utility.set_location('hxt_time_summary.gen_details',90);
2834 end if;
2835 FND_MESSAGE.SET_NAME('HXT','HXT_39300_GEN_DTAIL_EXC_LOOP'); -- HXT11
2836 return_code := call_gen_error(location, ''); -- HXT11
2837 END IF;
2838 IF return_code > 0 THEN
2839 if g_debug then
2840 hr_utility.set_location('hxt_time_summary.gen_details',100);
2841 end if;
2842 EXIT;
2843 END IF;
2844 END LOOP;
2845 */
2846
2847
2848 if g_debug then
2849 hr_utility.set_location('hxt_time_summary.gen_details',40);
2850 end if;
2851 END IF; -- there is a shift diff policy
2852
2853 if g_debug then
2854 hr_utility.set_location('hxt_time_summary.gen_details',45);
2855 end if;
2856 RETURN return_code;
2857
2858 EXCEPTION
2859
2860 WHEN OTHERS THEN
2861 if g_debug then
2862 hr_utility.set_location('hxt_time_summary.gen_details',110);
2863 end if;
2864 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2865 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm); -- HXT11
2866 --2278400 RETURN call_gen_error(location, '', sqlerrm); -- HXT11
2867
2868 END;
2869
2870 FUNCTION Get_Include(p_location IN VARCHAR2
2871 ,p_egt_id IN NUMBER
2872 ,p_element_type_id IN NUMBER
2873 ,p_date IN DATE)
2874 RETURN VARCHAR2 IS
2875
2876 -- Returns 1 if element_type passed found in earning group passed,
2877 -- 0 if not in earning group,
2878 -- SQLCODE if Oracle error occurred.
2879
2880 -- Modification Log:
2881 -- 01/19/96 PJA Created.
2882 -- 02/01/96 AVS Modified cursor and error handling.
2883
2884 l_retcode VARCHAR2(1) DEFAULT 'N';
2885 l_error_code NUMBER;
2886 l_location VARCHAR2(120) := p_location||':GI';
2887
2888 BEGIN
2889
2890 if g_debug then
2891 hr_utility.set_location('hxt_time_summary.Get_Include',10);
2892 end if;
2893 -- Check if no earn group passed
2894 IF p_egt_id is null THEN
2895 if g_debug then
2896 hr_utility.set_location('hxt_time_summary.Get_Include',20);
2897 end if;
2898 RETURN l_retcode;
2899 ELSE
2900 BEGIN
2901 if g_debug then
2902 hr_utility.set_location('hxt_time_summary.Get_Include',30);
2903 end if;
2904 -- Check if element_type exists in earning group
2905 SELECT 'Y'
2906 INTO l_retcode
2907 FROM hxt_earn_group_types egt
2908 WHERE egt.FCL_EG_TYPE = 'INCLUDE'
2909 AND p_date between egt.effective_start_date
2910 and egt.effective_end_date
2911 AND egt.id = p_egt_id
2912 AND exists (SELECT 'x'
2913 FROM hxt_earn_groups egr
2914 WHERE egr.egt_id = p_egt_id -- SPR C150
2915 AND egr.element_type_id = p_element_type_id
2916 );
2917 RETURN l_retcode;
2918
2919 EXCEPTION
2920 WHEN no_data_found THEN
2921 if g_debug then
2922 hr_utility.set_location('hxt_time_summary.Get_Include',40);
2923 end if;
2924 RETURN l_retcode;
2925 END;
2926 END IF;
2927
2928 EXCEPTION
2929 -- Return Oracle error number
2930 WHEN others THEN
2931 if g_debug then
2932 hr_utility.set_location('hxt_time_summary.Get_Include',50);
2933 end if;
2934 FND_MESSAGE.SET_NAME('HXT','HXT_39270_OR_ERR_G_GROUP'); -- HXT11
2935 l_error_code := call_hxthxc_gen_error('HXT','HXT_39270_OR_ERR_G_GROUP',l_location,'', sqlerrm); -- HXT11
2936 --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm); -- HXT11
2937 RETURN 'E';
2938
2939 END; -- get include
2940 -------------------------------------------------------------------------------
2941 FUNCTION GEN_SPECIAL (p_location IN VARCHAR2
2942 ,p_time_in IN DATE
2943 ,p_time_out IN DATE
2944 ,p_hours_worked IN NUMBER
2945 ,p_shift_diff_earning_id IN NUMBER
2946 ,p_sdovr_earning_id IN NUMBER)
2947 RETURN NUMBER IS
2948
2949 BEGIN
2950
2951 if g_debug then
2952 hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
2953 hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
2954 hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
2955 hr_utility.trace('p_hours_worked :'||p_hours_worked);
2956 hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
2957 hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
2958 end if;
2959 RETURN hxt_time_detail.generate_special
2960 ( g_ep_id
2961 ,g_ep_type
2962 ,g_egt_id
2963 ,p_shift_diff_earning_id
2964 ,g_hdp_id
2965 ,g_hol_id
2966 ,g_sdp_id -- ORACLE bug #715964
2967 ,g_pep_id
2968 ,g_pip_id
2969 ,p_sdovr_earning_id
2970 ,g_osp_id
2971 ,g_hol_yn
2972 ,g_person_id
2973 ,p_location
2974 ,g_ID
2975 ,g_TIM_ID
2976 ,g_DATE_WORKED
2977 ,g_ASSIGNMENT_ID
2978 ,p_hours_worked
2979 ,p_time_in
2980 ,p_time_out
2981 ,g_ELEMENT_TYPE_ID
2982 ,g_FCL_EARN_REASON_CODE
2983 ,g_FFV_COST_CENTER_ID
2984 ,g_FFV_LABOR_ACCOUNT_ID
2985 ,g_TAS_ID
2986 ,g_LOCATION_ID
2987 ,g_SHT_ID
2988 ,g_HRW_COMMENT
2989 ,g_FFV_RATE_CODE_ID
2990 ,g_RATE_MULTIPLE
2991 ,g_HOURLY_RATE
2992 ,g_AMOUNT
2993 ,g_FCL_TAX_RULE_CODE
2994 ,g_SEPARATE_CHECK_FLAG
2995 ,g_SEQNO
2996 ,g_CREATED_BY
2997 ,g_CREATION_DATE
2998 ,g_LAST_UPDATED_BY
2999 ,g_LAST_UPDATE_DATE
3000 ,g_LAST_UPDATE_LOGIN
3001 ,g_start_day_of_week
3002 ,g_EFFECTIVE_START_DATE
3003 ,g_EFFECTIVE_END_DATE
3004 ,g_PROJECT_ID
3005 ,g_JOB_ID
3006 ,g_PAY_STATUS
3007 ,g_PA_STATUS
3008 ,g_RETRO_BATCH_ID
3009 ,g_PERIOD_START_DATE
3010 ,g_CALL_ADJUST_ABS
3011 ,g_STATE_NAME
3012 ,g_COUNTY_NAME
3013 ,g_CITY_NAME
3014 ,g_ZIP_CODE
3015 --,g_GROUP_ID
3016 );
3017
3018 if g_debug then
3019 hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',20);
3020 end if;
3021 END; -- gen special
3022
3023 -- begin
3024
3025
3026 END; -- package
[Home] [Help]