1 PACKAGE BODY hxt_time_summary AS
2 /* $Header: hxttsum.pkb 120.15.12020000.2 2013/02/12 12:08:42 asrajago ship $ */
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 -- Bug 10123886
68 -- Added this type to store the regular elements to be deleted.
69 --TYPE det_tab IS TABLE OF hxt_det_duplicate_regular%ROWTYPE;
70 --g_det_tab det_tab := det_tab();
71 --g_index BINARY_INTEGER;
72
73
74
75 -- Bug 8600894
76 -- Added the following variables for calculation
77 -- of Holiday hours as per holiday rule.
78 g_hol_ep_id NUMBER;
79 g_hol_ep_type HXT_EARNING_POLICIES.FCL_EARN_TYPE%TYPE;
80 g_hol_egt_id NUMBER;
81 g_hol_sdp_id NUMBER;
82 g_hol_hdp_id NUMBER;
83 g_hol_hol_id NUMBER;
84 g_hol_pep_id NUMBER;
85 g_hol_pip_id NUMBER;
86 g_hol_sdovr_id NUMBER;
87 g_hol_osp_id NUMBER;
88 g_hol_hol_yn VARCHAR2(1);
89 g_hol_person_id NUMBER;
90 g_hol_pk_ID NUMBER;
91 g_hol_EFFECTIVE_START_DATE DATE;
92 g_hol_EFFECTIVE_END_DATE DATE;
93 g_hol_ROWID ROWID;
94 g_hol_PARENT_ID NUMBER;
95 g_hol_TIM_ID NUMBER;
96 g_hol_DATE_WORKED DATE;
97 g_hol_ASSIGNMENT_ID NUMBER;
98 g_hol_HOURS NUMBER;
99 g_hol_TIME_IN DATE;
100 g_hol_TIME_OUT DATE;
101 g_hol_ELEMENT_TYPE_ID NUMBER;
102 g_hol_FCL_EARN_REASON_CODE HXT_SUM_HOURS_WORKED.FCL_EARN_REASON_CODE%TYPE;--C421
103 g_hol_FFV_COST_CENTER_ID NUMBER;
104 g_hol_FFV_LABOR_ACCOUNT_ID NUMBER;
105 g_hol_TAS_ID NUMBER;
106 g_hol_LOCATION_ID NUMBER;
107 g_hol_SHT_ID NUMBER;
108 g_hol_HRW_COMMENT HXT_SUM_HOURS_WORKED.HRW_COMMENT%TYPE;--C421
109 g_hol_FFV_RATE_CODE_ID NUMBER;
110 g_hol_RATE_MULTIPLE NUMBER;
111 g_hol_HOURLY_RATE NUMBER;
112 g_hol_AMOUNT NUMBER;
113 g_hol_FCL_TAX_RULE_CODE HXT_SUM_HOURS_WORKED.FCL_TAX_RULE_CODE%TYPE;--C421
114 g_hol_SEPARATE_CHECK_FLAG HXT_SUM_HOURS_WORKED.SEPARATE_CHECK_FLAG%TYPE;--C421
115 g_hol_SEQNO NUMBER;
116 g_hol_CREATED_BY NUMBER;
117 g_hol_CREATION_DATE DATE;
118 g_hol_LAST_UPDATED_BY NUMBER;
119 g_hol_LAST_UPDATE_DATE DATE;
120 g_hol_LAST_UPDATE_LOGIN NUMBER;
121 g_hol_EARLY_START NUMBER;
122 g_hol_LATE_STOP NUMBER;
123 g_hol_STANDARD_START NUMBER;
124 g_hol_STANDARD_STOP NUMBER;
125 g_hol_PROJECT_ID NUMBER;
126 g_hol_JOB_ID hxt_SUM_HOURS_WORKED.JOB_ID%TYPE;
127 g_hol_PAY_STATUS CHAR(1);
128 g_hol_PA_STATUS CHAR(1);
129 g_hol_PERIOD_START_DATE DATE;
130 g_hol_RETRO_BATCH_ID NUMBER;
131 g_hol_DT_UPDATE_MODE VARCHAR2(30);
132 g_hol_include_yn VARCHAR2(1);
133 g_hol_start_day_of_week VARCHAR2(30);
134 g_hol_STATE_NAME hxt_sum_hours_worked_f.state_name%type;
135 g_hol_COUNTY_NAME hxt_sum_hours_worked_f.county_name%type;
136 g_hol_CITY_NAME hxt_sum_hours_worked_f.city_name%type;
137 g_hol_ZIP_CODE hxt_sum_hours_worked_f.zip_code%type;
138
139 g_hol_location VARCHAR2(500);
140
141
142
143 --
144 --Flags for checking which shift diff premium gets paid for a segment chunk
145 --
146 g_sdf_rule_completed VARCHAR2(1);
147 g_sdf_carryover DATE;
148 g_STATE_NAME hxt_sum_hours_worked_f.state_name%type;
149 g_COUNTY_NAME hxt_sum_hours_worked_f.county_name%type;
150 g_CITY_NAME hxt_sum_hours_worked_f.city_name%type;
151 g_ZIP_CODE hxt_sum_hours_worked_f.zip_code%type;
152
153
154 --Function and Procedure declarations
155
156 FUNCTION valid_data(p_location IN VARCHAR2) RETURN NUMBER;
157
158 FUNCTION gen_details(p_location IN VARCHAR2
159 ,p_shift_adjusted_time_in IN DATE
160 ,p_shift_adjusted_time_out IN DATE )
161 RETURN NUMBER;
162
163 PROCEDURE Delete_Details(p_location IN VARCHAR2
164 ,p_error_code IN OUT NOCOPY NUMBER);
165
166 PROCEDURE shift_adjust_times(p_shift_adjusted_time_in OUT NOCOPY DATE
167 ,p_shift_adjusted_time_out OUT NOCOPY DATE);
168
169 PROCEDURE Rebuild_Details(p_location IN VARCHAR2
170 ,p_error_code IN OUT NOCOPY NUMBER);
171
172 FUNCTION call_gen_error
173 (p_location IN varchar2
174 ,p_error_text IN VARCHAR2
175 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER;
176
177 FUNCTION call_hxthxc_gen_error
178 ( p_app_short_name IN VARCHAR2
179 ,p_msg_name IN VARCHAR2
180 ,p_msg_token IN VARCHAR2
181 ,p_location IN varchar2
182 ,p_error_text IN VARCHAR2
183 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER ;
184
185
186 FUNCTION Get_Include(p_location IN VARCHAR2
187 ,p_egt_id IN NUMBER
188 ,p_element_type_id IN NUMBER
189 ,p_date IN DATE) RETURN VARCHAR2;
190
191 FUNCTION GEN_SPECIAL (p_location IN VARCHAR2
192 ,p_time_in IN DATE
193 ,p_time_out IN DATE
194 ,p_hours_worked IN NUMBER
195 ,p_shift_diff_earning_id IN NUMBER
196 ,p_sdovr_earning_id IN NUMBER) RETURN NUMBER;
197
198
199 PROCEDURE store_globals;
200
201 PROCEDURE clear_globals;
202
203 PROCEDURE check_holiday_rule_behavior;
204
205 FUNCTION adjust_holiday_rule
206 RETURN NUMBER;
207
208
209 -- Bug 8600894
210 -- Store the global variables for later Holiday adjustment.
211 PROCEDURE store_globals
212 IS
213
214 BEGIN
215
216 g_hol_ep_id := g_ep_id;
217 g_hol_ep_type := g_ep_type;
218 g_hol_egt_id := g_egt_id;
219 g_hol_sdp_id := g_sdp_id;
220 g_hol_hdp_id := g_hdp_id;
221 g_hol_hol_id := g_hol_id;
222 g_hol_pep_id := g_pep_id;
223 g_hol_pip_id := g_pip_id;
224 g_hol_sdovr_id := g_sdovr_id;
225 g_hol_osp_id := g_osp_id;
226 g_hol_standard_start := g_standard_start;
227 g_hol_standard_stop := g_standard_stop;
228 g_hol_early_start := g_early_start;
229 g_hol_late_stop := g_late_stop;
230 g_hol_hol_yn := g_hol_yn;
231 g_hol_person_id := g_person_id;
232 g_hol_pk_ID := g_ID;
233 g_hol_ROWID := CHARTOROWID(g_ROWID);
234 g_hol_EFFECTIVE_START_DATE := g_EFFECTIVE_START_DATE;
235 g_hol_EFFECTIVE_END_DATE := g_EFFECTIVE_END_DATE;
236 g_hol_TIM_ID := g_TIM_ID;
237 g_hol_DATE_WORKED := g_DATE_WORKED;
238 g_hol_ASSIGNMENT_ID := g_ASSIGNMENT_ID;
239 g_hol_HOURS := g_HOURS;
240 g_hol_TIME_IN := g_TIME_IN;
241 g_hol_TIME_OUT := g_TIME_OUT;
242 g_hol_ELEMENT_TYPE_ID := g_ELEMENT_TYPE_ID;
243 g_hol_FCL_EARN_REASON_CODE := g_FCL_EARN_REASON_CODE;
244 g_hol_FFV_COST_CENTER_ID := g_FFV_COST_CENTER_ID;
245 g_hol_FFV_LABOR_ACCOUNT_ID := g_FFV_LABOR_ACCOUNT_ID;
246 g_hol_TAS_ID := g_TAS_ID;
247 g_hol_LOCATION_ID := g_LOCATION_ID;
248 g_hol_SHT_ID := g_SHT_ID;
249 g_hol_HRW_COMMENT := g_HRW_COMMENT;
250 g_hol_FFV_RATE_CODE_ID := g_FFV_RATE_CODE_ID;
251 g_hol_RATE_MULTIPLE := g_RATE_MULTIPLE;
252 g_hol_HOURLY_RATE := g_HOURLY_RATE;
253 g_hol_AMOUNT := g_AMOUNT;
254 g_hol_FCL_TAX_RULE_CODE := g_FCL_TAX_RULE_CODE;
255 g_hol_SEPARATE_CHECK_FLAG := g_SEPARATE_CHECK_FLAG;
256 g_hol_SEQNO := g_SEQNO;
257 g_hol_CREATED_BY := g_CREATED_BY;
258 g_hol_CREATION_DATE := g_CREATION_DATE;
259 g_hol_LAST_UPDATED_BY := g_LAST_UPDATED_BY;
260 g_hol_LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
261 g_hol_LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
262 g_hol_PROJECT_ID := g_PROJECT_ID;
263 g_hol_JOB_ID := g_JOB_ID;
264 g_hol_PAY_STATUS := g_PAY_STATUS;
265 g_hol_PA_STATUS := g_PA_STATUS;
266 g_hol_RETRO_BATCH_ID := g_RETRO_BATCH_ID;
267 g_hol_DT_UPDATE_MODE := g_DT_UPDATE_MODE;
268 g_hol_PERIOD_START_DATE := g_PERIOD_START_DATE;
269 g_hol_STATE_NAME := g_STATE_NAME;
270 g_hol_COUNTY_NAME := g_COUNTY_NAME;
271 g_hol_CITY_NAME := g_CITY_NAME;
272 g_hol_ZIP_CODE := g_ZIP_CODE;
273 -- g_hol_GROUP_ID := g_GROUP_ID;
274
275
276 END store_globals;
277
278
279 -- Bug 8600894
280 -- Clears the holiday variables for the next detail.
281 PROCEDURE clear_globals
282 IS
283
284
285 -- Bug 10123886
286 -- Cursor to find out if there are any more summaries to be
287 -- exploded.
288 -- Bug 16192990
289 -- Modified this cursor below to use 99999999999 instead of 0
290 -- as the default value for NVL. Reason : When a NULL value is sorted
291 -- against an actual value, the actual value comes up first and then NULL.
292 -- Replacing NULL with zero is fine until a ">" comparison is used -- 0 is always
293 -- less than any actual value we get, while 99999999999 is always greater than the actual
294 -- values. In the below cursor, we want the NULL values to appear greater than the ones with values.
295 CURSOR more_details
296 IS SELECT 1
297 FROM hxt_sum_hours_worked_f sum
298 WHERE tim_id = g_hol_tim_id
299 AND g_sum_session_date BETWEEN sum.effective_start_date
300 AND sum.effective_end_date
301 AND ( date_worked > g_hol_date_worked
302 OR ( date_worked = g_hol_date_worked
303 AND NVL(element_type_id,99999999999) > NVL(g_hol_element_type_id,99999999999)
304 )
305 OR ( date_worked = g_hol_date_worked
306 AND NVL(element_type_id,99999999999) = NVL(g_hol_element_type_id,99999999999)
307 AND NVL(time_in,hr_general.end_of_time) > NVL(g_hol_time_in,hr_general.end_of_time)
308 )
309 OR (date_worked = g_hol_date_worked
310 AND NVL(element_type_id,99999999999) = NVL(g_hol_element_type_id,99999999999)
311 AND NVL(time_in,hr_general.end_of_time) = NVL(g_hol_time_in,hr_general.end_of_time)
312 AND seqno > g_hol_seqno
313 )
314 OR (date_worked = g_hol_date_worked
315 AND NVL(element_type_id,99999999999) = NVL(g_hol_element_type_id,99999999999)
316 AND NVL(time_in,hr_general.end_of_time) = NVL(g_hol_time_in,hr_general.end_of_time)
317 AND seqno = g_hol_seqno
318 AND id > g_hol_pk_id
319 )
320 );
321
322
323 -- Keeping a table here so that, just in case of an error
324 -- its easier to view the columns selected. Just need to convert
325 -- 1 to * in the cursor above.
326
327 TYPE T_DETAILS IS table of more_details%rowtype;
328 l_details T_DETAILS;
329
330 BEGIN
331
332 IF g_debug
333 THEN
334 hr_utility.trace('g_hol_id '||g_hol_id);
335 hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336 hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337 hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338 hr_utility.trace('g_sum_session_date '||g_sum_session_date);
339 hr_utility.trace('g_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy HH24:MI'));
340 END IF;
341
342
343
344 OPEN more_details;
345 --FETCH more_details BULK COLLECT INTO l_details;
346 -- We only need to know whether there are records or not.
347 FETCH more_details BULK COLLECT INTO l_details LIMIT 2;
348 CLOSE more_details;
349
350 IF l_details.COUNT = 0
351 THEN
352
353 IF g_debug
354 THEN
355 hr_utility.trace('There are no more details to explode ');
356 hr_utility.trace('All the duplicate details can be deleted ');
357 END IF;
358
359 -- Delete from Details table.
360 DELETE FROM hxt_det_hours_worked_f
361 WHERE (tim_id,
362 id,
363 parent_id) IN
364 ( SELECT dup.tim_id,
365 dup.det_id,
366 dup.parent_id
367 FROM hxt_det_duplicate_regular dup
368 WHERE dup.tim_id = g_hol_tim_id);
369
370 -- Delete from the table storing duplicates.
371 DELETE FROM hxt_det_duplicate_regular
372 WHERE tim_id = g_hol_tim_id;
373
374
375 END IF;
376
377
378 IF g_debug
379 THEN
380 hr_utility.trace('Clearing globals ');
381 END IF;
382 g_hol_ep_id := NULL;
383 g_hol_ep_type := NULL;
384 g_hol_egt_id := NULL;
385 g_hol_sdp_id := NULL;
386 g_hol_hdp_id := NULL;
387 g_hol_hol_id := NULL;
388 g_hol_pep_id := NULL;
389 g_hol_pip_id := NULL;
390 g_hol_sdovr_id := NULL;
391 g_hol_osp_id := NULL;
392 g_hol_standard_start := NULL;
393 g_hol_standard_stop := NULL;
394 g_hol_early_start := NULL;
395 g_hol_late_stop := NULL;
396 g_hol_hol_yn := NULL;
397 g_hol_person_id := NULL;
398 g_hol_pk_ID := NULL;
399 g_hol_ROWID := NULL;
400 g_hol_EFFECTIVE_START_DATE := NULL;
401 g_hol_EFFECTIVE_END_DATE := NULL;
402 g_hol_TIM_ID := NULL;
403 g_hol_DATE_WORKED := NULL;
404 g_hol_ASSIGNMENT_ID := NULL;
405 g_hol_HOURS := NULL;
406 g_hol_TIME_IN := NULL;
407 g_hol_TIME_OUT := NULL;
408 g_hol_ELEMENT_TYPE_ID := NULL;
409 g_hol_FCL_EARN_REASON_CODE := NULL;
410 g_hol_FFV_COST_CENTER_ID := NULL;
411 g_hol_FFV_LABOR_ACCOUNT_ID := NULL;
412 g_hol_TAS_ID := NULL;
413 g_hol_LOCATION_ID := NULL;
414 g_hol_SHT_ID := NULL;
415 g_hol_HRW_COMMENT := NULL;
416 g_hol_FFV_RATE_CODE_ID := NULL;
417 g_hol_RATE_MULTIPLE := NULL;
418 g_hol_HOURLY_RATE := NULL;
419 g_hol_AMOUNT := NULL;
420 g_hol_FCL_TAX_RULE_CODE := NULL;
421 g_hol_SEPARATE_CHECK_FLAG := NULL;
422 g_hol_SEQNO := NULL;
423 g_hol_CREATED_BY := NULL;
424 g_hol_CREATION_DATE := NULL;
425 g_hol_LAST_UPDATED_BY := NULL;
426 g_hol_LAST_UPDATE_DATE := NULL;
427 g_hol_LAST_UPDATE_LOGIN := NULL;
428 g_hol_PROJECT_ID := NULL;
429 g_hol_JOB_ID := NULL;
430 g_hol_PAY_STATUS := NULL;
431 g_hol_PA_STATUS := NULL;
432 g_hol_RETRO_BATCH_ID := NULL;
433 g_hol_DT_UPDATE_MODE := NULL;
434 g_hol_PERIOD_START_DATE := NULL;
435 g_hol_STATE_NAME := NULL;
436 g_hol_COUNTY_NAME := NULL;
437 g_hol_CITY_NAME := NULL;
438 g_hol_ZIP_CODE := NULL;
439 -- g_hol_GROUP_ID := NULL;
440
441
442 END clear_globals;
443
444
445
446 -- Check if this is a holiday day
447 PROCEDURE check_holiday_rule_behavior
448 IS
449
450 l_hdy_id NUMBER;
451 l_hours NUMBER;
452 l_retcode NUMBER:= 0;
453
454 BEGIN
455
456 -- Bug 12844923 (Bug 10123886)
457 -- We no longer need to check for Holiday.
458 -- We need these global variables for all days.
459 /*
460
461 HXT_UTIL.Check_For_Holiday(g_date_worked
462 ,g_hol_id
463 ,l_hdy_id
464 ,l_hours
465 ,l_retcode);
466 */
467
468
469 IF NVL(FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION'),'EX') <> 'EX'
470 -- AND l_retcode = 1
471 THEN
472 store_globals;
473 END IF;
474
475 -- Bug 16192990
476 -- Added this call here to NULL out Hours entered against an Overridden element.
477 -- We still need the Clear globals part and all other holiday looping for an Overridden element
478 -- but we do not want to pay anything on the Holiday days.
479 IF g_hol_element_type_id IS NOT NULL
480 THEN
481 HXT_UTIL.Check_For_Holiday(g_date_worked
482 ,g_hol_id
483 ,l_hdy_id
484 ,l_hours
485 ,l_retcode);
486 END IF;
487
488 IF g_debug
489 THEN
490 hr_utility.trace('Hol: g_date_worked '||g_date_worked);
491 hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
492 hr_utility.trace('Hol: l_Retcode'||l_retcode);
493 hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
494 hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
495 END IF;
496
497 IF l_retcode = 1
498 AND g_hol_element_type_id IS NOT NULL
499 THEN
500 g_hol_hours := 0;
501 END IF;
502
503 IF g_debug
504 THEN
505 hr_utility.trace('Hol: After correction ');
506 hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507 hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508 hr_utility.trace('Hol: l_Retcode'||l_retcode);
509 hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
510 hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
511 END IF;
512
513
514 END check_holiday_rule_behavior;
515
516
517 FUNCTION adjust_holiday_rule
518 RETURN NUMBER
519 IS
520
521 CURSOR get_holiday_values(p_ep_id IN NUMBER)
522 IS SELECT hours,
523 hours- NVL(LAG(hours) OVER (ORDER BY hours) ,0) cap,
524 element_type_id
525 FROM hxt_earning_policies ep,
526 hxt_earning_rules er
527 WHERE ep.id = p_ep_id
528 AND er.egp_id = ep.id
529 AND g_sum_session_date BETWEEN er.effective_start_date
530 AND er.effective_end_date
531 AND er.egr_type = 'HOL'
532 ORDER BY hours;
533
534 CURSOR get_reg_elements(p_ep_id IN NUMBER,
535 p_date IN DATE)
536 IS SELECT DISTINCT
537 er.element_type_id
538 FROM hxt_earning_rules er,
539 hxt_pay_element_types_f_ddf_v elem
540 WHERE er.egp_id = p_ep_id
541 AND p_date BETWEEN er.effective_start_date
542 AND er.effective_end_date
543 AND g_sum_session_date BETWEEN er.effective_start_date
544 AND er.effective_end_date
545 AND elem.element_type_id = er.element_type_id
546 AND elem.hxt_earning_category = 'REG';
547
548
549 CURSOR get_other_summary(p_seq_no IN NUMBER,
550 p_date IN DATE)
551 IS SELECT 1
552 FROM hxt_sum_hours_worked_f sum
553 WHERE seqno > p_seq_no
554 AND tim_id = g_hol_tim_id
555 AND g_sum_session_date BETWEEN sum.effective_start_date
556 AND sum.effective_end_date
557 AND date_worked = p_date
558 AND element_type_id IS NULL
559 AND ROWNUM < 2;
560
561 CURSOR get_next_summary(p_time_in IN DATE,
562 p_date IN DATE)
563 IS SELECT 1
564 FROM hxt_sum_hours_worked_f sum
565 WHERE time_in > p_time_in
566 AND g_sum_session_date BETWEEN sum.effective_start_date
567 AND sum.effective_end_date
568 AND tim_id = g_hol_tim_id
569 AND date_worked = p_date
570 AND element_type_id IS NULL
571 AND ROWNUM < 2;
572
573
574 CURSOR get_holiday_overtime
575 IS
576 SELECT NVL(SUM(det.hours),0)
577 FROM hxt_det_hours_worked_f det,
578 hxt_sum_hours_worked_f sum,
579 hxt_earning_rules er,
580 hxt_pay_element_types_f_ddf_v elem
581 WHERE det.tim_id = g_hol_tim_id
582 AND sum.id = det.parent_id
583 AND g_sum_session_date BETWEEN sum.effective_start_date
584 AND sum.effective_end_date
585 AND g_sum_session_date BETWEEN det.effective_start_date
586 AND det.effective_end_date
587 AND g_sum_session_date BETWEEN elem.effective_start_date
588 AND elem.effective_end_date
589 AND g_sum_session_date BETWEEN er.effective_start_date
590 AND er.effective_end_date
591 AND sum.date_worked = g_hol_date_worked
592 AND sum.element_type_id IS NULL
593 AND er.egp_id = g_hol_ep_id
594 AND er.egr_type NOT IN ('HOL')
595 AND er.element_type_id = det.element_type_id
596 AND elem.element_type_id = det.element_type_id
597 AND elem.hxt_earning_category = 'OVT' ;
598
599 -- Bug 10123886
600 CURSOR get_duplicate_sdp(p_id IN NUMBER)
601 IS
602 SELECT det.id,
603 det.time_in,
604 det.time_out,
605 det.element_type_id,
606 0 del_y_n
607 FROM hxt_det_hours_worked det,
608 hxt_add_elem_info_f elem
609 WHERE det.parent_id = p_id
610 AND det.element_type_id = elem.element_type_id
611 AND elem.earning_category = 'SDF'
612 ORDER BY det.element_type_id,
613 det.time_in,
614 det.time_out;
615
616 CURSOR get_other_holidays(p_tim_id IN NUMBER,
617 p_date_worked IN DATE,
618 p_hol_id IN NUMBER)
619 IS SELECT 1
620 FROM hxt_sum_hours_worked sum,
621 hxt_holiday_days hhd
622 WHERE tim_id = p_tim_id
623 AND sum.date_worked > p_date_worked
624 AND hhd.hcl_id = p_hol_id
625 AND sum.date_worked = hhd.holiday_date;
626
627
628
629 TYPE HOLIDAY_TAB IS TABLE OF get_holiday_values%ROWTYPE INDEX BY BINARY_INTEGER;
630 l_holidays HOLIDAY_TAB;
631 l_ind BINARY_INTEGER;
632
633 TYPE HOLIDAY_REC IS RECORD
634 ( hours NUMBER,
635 time_in DATE,
636 time_out DATE,
637 element_type_id NUMBER);
638
639 TYPE HOL_TAB IS TABLE OF HOLIDAY_REC INDEX BY BINARY_INTEGER;
640 l_hol_tab HOL_TAB;
641 e_ind BINARY_INTEGER;
642
643
644 TYPE ROWIDTAB IS TABLE OF VARCHAR2(30) ;
645 l_det_row ROWIDTAB;
646
647 -- Bug 10123886
648 TYPE NUMTAB IS TABLE OF NUMBER;
649 TYPE DATETAB IS TABLE OF DATE;
650
651 l_id_tab NUMTAB;
652 l_elem_tab NUMTAB;
653 l_time_in_tab DATETAB;
654 l_time_out_tab DATETAB;
655 l_del_tab NUMTAB;
656
657
658 l_det_tab det_tab;
659
660 l_element_string VARCHAR2(200);
661
662 l_sql VARCHAR2(32000) :=
663 'SELECT NVL(sum(sum.hours),0)
664 FROM hxt_sum_hours_worked_f SUM,
665 hxt_det_hours_worked_f DET
666 WHERE sum.tim_id = :tim_id
667 AND sum.id = det.parent_id
668 AND sum.element_type_id IS NULL
669 AND sum.date_worked = FND_date.CANONICAL_TO_DATE(:date_worked)
670 AND det.element_type_id IN ELEMENT_LIST
671 AND FND_DATE.CANONICAL_TO_DATE(:session_date1) BETWEEN sum.effective_start_date
672 AND sum.effective_end_date
673 AND FND_DATE.CANONICAL_TO_DATE(:session_date2) BETWEEN det.effective_start_date
674 AND det.effective_end_date' ;
675
676
677
678 l_refcursor SYS_REFCURSOR;
679 l_paid_hours NUMBER;
680 l_hours NUMBER;
681 l_adjusted_hours NUMBER;
682 l_adjusted_element NUMBER;
683
684 l_rebuild_code NUMBER;
685 l_time_in DATE;
686 l_time_out DATE;
687 l_reg NUMBER;
688 l_other_summary NUMBER;
689
690 l_hol_true NUMBER;
691
692
693 BEGIN
694
695 -- ADJUST_HOLIDAY_RULE
696 -- Basic functionality here is to explode the time as per Holiday rule defined in the
697 -- earning policy. The whole thing would be done based on the value of HXT_HOLIDAY_EXPLOSION
698 -- profile option.
699 -- EX -- The existing and archaic behavior -- No rule except holiday rule.
700 -- NE -- Normal explosion and Holiday rule together. This means that
701 -- explosion according to Daily/Weekly/Special rules happen as if there is
702 -- No holiday rule. And Holiday explosion would happen as per rules.
703 -- NO -- Normal explosion happens along with Holiday rule, but only Overtime elements are
704 -- -- paid.
705 -- OO -- Normal explosion happens and only Overtime elements are paid. Holiday rule is applicable
706 -- Only for regular hours -- ie. whatever falls outside Overtime.
707
708
709 -- The algorigthm followed is simple.
710
711
712
713 IF g_debug
714 THEN
715 hr_utility.trace('ADJUST_HOLIDAY_RULE call here '||g_hol_hol_id||'-'||g_hol_date_worked);
716 END IF;
717
718 -- Bug 12844923 (Bug 10123886)
719 -- Added the exception block below.
720 BEGIN
721 SELECT 1
722 INTO l_hol_true
723 FROM hxt_holiday_days
724 WHERE hcl_id = g_hol_hol_id
725 AND holiday_date = g_hol_date_worked;
726
727 EXCEPTION
728 WHEN NO_DATA_FOUND
729 THEN
730 hr_utility.trace('Not a holiday '||g_hol_date_worked);
731 clear_globals;
732 RETURN 0;
733 END;
734
735
736 If g_debug
737 THEN
738 hr_utility.trace('l_hol_true '||l_hol_true);
739 END IF;
740
741 -- Bug 12844923 (Bug 10123886)
742 /*
743
744 IF l_hol_true IS NULL
745 THEN
746 IF g_debug
747 THEN
748 hr_utility.trace('Not a holiday '||g_hol_date_worked);
749 END IF;
750 clear_globals;
751 RETURN 0;
752 END IF;
753 */
754
755
756
757 -- Find out if there is a holiday rule.
758 -- If there is none, there is no need to proceed further.
759 -- Do nothing, return.
760 OPEN get_holiday_values(g_hol_ep_id);
761 FETCH get_holiday_values BULK
762 COLLECT
763 INTO l_holidays;
764 IF l_holidays.COUNT = 0
765 THEN
766 clear_globals;
767 CLOSE get_holiday_values;
768 IF g_debug
769 THEN
770 hr_utility.trace('No HOLIDAY RULE, NO ADJUSTMENT');
771 END IF;
772 RETURN 0;
773 END IF;
774
775 CLOSE get_holiday_values;
776
777 -- If No Regular hours on holiday is the rule, delete
778 -- all that is paid as WEEKLY/DAILY regular.
779
780 IF fnd_profile.value('HXT_HOLIDAY_EXPLOSION') IN ('NO','OO')
781 THEN
782 -- Bug 10123886
783 -- We no longer need this kind of checking.
784 /*
785
786 IF g_hol_time_in IS NULL
787 THEN
788 OPEN get_other_summary(g_hol_seqno,g_hol_date_worked);
789 FETCH get_other_summary INTO l_other_summary;
790 CLOSE get_other_summary;
791 ELSE
792 OPEN get_next_summary(g_hol_time_in,g_hol_date_worked);
793 FETCH get_next_summary INTO l_other_summary;
794 CLOSE get_next_summary;
795 END IF;
796
797 IF g_debug
798 THEN
799 hr_utility.trace('Ash Hol G_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy hh24:mi'));
800 hr_utility.trace('Ash Hol G_hol_time_out '||to_char(g_hol_time_out,'dd-mon-yyyy hh24:mi'));
801 END IF;
802 */
803
804 l_other_summary := NULL;
805
806 -- We should not delete the reg hours if there is another detail waiting to come up.
807 -- If we do that OT calculation for the Day would topple up.
808
809 -- Bug 10123886
810 -- We are not deleting anything anymore -- instead just recording the stuff to
811 -- a new table.
812 IF l_other_summary IS NULL
813 THEN
814
815 hr_utility.trace('Selecting Regular Elements ');
816
817 OPEN get_reg_elements(g_hol_ep_id,g_date_worked);
818 LOOP
819 FETCH get_reg_elements INTO l_reg;
820
821 EXIT WHEN get_reg_elements%NOTFOUND;
822
823 SELECT det.id det_id,
824 ROWIDTOCHAR(det.rowid),
825 det.tim_id,
826 det.parent_id
827 BULK COLLECT INTO l_det_tab
828 FROM hxt_sum_hours_worked_f sum,
829 hxt_det_hours_worked_f det
830 WHERE sum.element_type_id IS NULL
831 AND sum.id = det.parent_id
832 AND SYSDATE BETWEEN sum.effective_start_date
833 AND sum.effective_end_date
834 AND SYSDATE BETWEEN det.effective_start_date
835 AND det.effective_end_Date
836 AND det.tim_id = g_hol_tim_id
837 AND det.element_type_id = l_reg
838 AND sum.date_worked = g_hol_date_worked;
839
840 IF l_det_tab.COUNT > 0
841 THEN
842 IF g_debug
843 THEN
844 hr_utility.trace(' Regular details exists for element'||l_reg);
845 END IF;
846
847 FOR i IN l_det_tab.FIRST..l_det_tab.LAST
848 LOOP
849
850 g_det_tab.EXTEND;
851 g_index := g_index+1;
852 g_det_tab(g_index) := l_det_tab(i);
853 INSERT INTO hxt_det_duplicate_regular
854 ( det_id,
855 det_rowid,
856 tim_id,
857 parent_id)
858 VALUES ( g_det_tab(g_index).det_id,
859 g_det_tab(g_index).det_rowid,
860 g_det_tab(g_index).tim_id,
861 g_det_tab(g_index).parent_id);
862
863 END LOOP;
864 END IF;
865
866
867 END LOOP;
868 END IF;
869
870
871
872
873
874 END IF;
875
876
877
878 -- Pick up all the holiday hours paid for this day.
879 -- This is required so that we pay for only what is remaining in the
880 -- Holiday rule.
881 l_element_string := '( ';
882
883 l_ind := l_holidays.FIRST;
884 LOOP
885 l_element_string := l_element_string||l_holidays(l_ind).element_type_id||',';
886 l_ind := l_holidays.NEXT(l_ind);
887 EXIT WHEN NOT l_holidays.EXISTS(l_ind);
888 END LOOP;
889 l_element_string := rtrim(l_element_string,',')||')';
890
891 -- l_element_string is a string of element_type_id s. Eg. (1234,2345)
892
893 l_sql := REPLACE(l_sql,'ELEMENT_LIST',l_element_string);
894
895
896 IF g_debug
897 THEN
898 hr_utility.trace('l_sql is '||l_sql);
899 END IF;
900
901 OPEN l_refcursor FOR l_sql USING g_hol_tim_id,
902 FND_DATE.DATE_TO_CANONICAL(g_hol_date_worked),
903 FND_DATE.DATE_TO_CANONICAL(g_sum_session_date),
904 FND_DATE.DATE_TO_CANONICAL(g_sum_session_date);
905
906 FETCH l_refcursor INTO l_paid_hours;
907
908 CLOSE l_refcursor;
909
910 l_hours := l_paid_hours;
911
912 IF l_hours > 0
913 THEN
914 l_ind := l_holidays.FIRST;
915 LOOP
916 EXIT WHEN NOT l_holidays.EXISTS(l_ind);
917 -- Paid hours is greater than this slab.
918 -- Delete this slab.
919 IF l_hours >= l_holidays(l_ind).hours
920 THEN
921 l_holidays.DELETE(l_ind);
922 -- Paid hour is not greater, but the cap needs to be adjusted.
923 ELSE
924 l_holidays(l_ind).cap := l_holidays(l_ind).hours - l_hours ;
925 l_holidays(l_ind).hours := l_holidays(l_ind).hours - l_hours ;
926 EXIT;
927 END IF;
928 l_ind := l_holidays.NEXT(l_ind);
929 END LOOP;
930 END IF;
931
932 -- If holiday explosion should occur only for what had to be paid for Regular,
933 -- we need to subtract the total overtime paid from the total number of hours.
934
935 IF FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION') = 'OO'
936 THEN
937 IF g_debug
938 THEN
939 hr_utility.trace('Holiday profile set to apply only on Reg elements ');
940 END IF;
941 OPEN get_holiday_overtime;
942
943 FETCH get_holiday_overtime INTO l_hours;
944
945 CLOSE get_holiday_overtime;
946 IF g_debug
947 THEN
948 hr_utility.trace('Overtime already paid = '||l_hours);
949 hr_utility.trace('Holiday hours now = '||g_hol_hours);
950 hr_utility.trace('Holiday time_in now = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
951 hr_utility.trace('Holiday time_out now = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
952 END IF;
953
954 IF l_hours >= g_hol_hours
955 THEN
956 -- Bug 10123886
957 -- Added this call to ensure issue in 10123886 does not crop up.
958 clear_globals;
959 RETURN 0;
960 END IF;
961
962 g_hol_hours := g_hol_hours - l_hours;
963 g_hol_time_out := g_hol_time_out - (l_hours/24);
964
965 IF g_debug
966 THEN
967 hr_utility.trace('After adjustment of Overtime ='||l_hours);
968 hr_utility.trace('Holiday hours = '||g_hol_hours);
969 hr_utility.trace('Holiday time_in = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
970 hr_utility.trace('Holiday time_out = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
971 END IF;
972 END IF;
973
974 -- Adjust the Hours and Time IN/OUT values.
975 e_ind := 0;
976 IF g_time_in IS NULL
977 THEN
978 l_hours := g_hol_hours;
979 ELSE
980 l_hours := (g_hol_time_out - g_hol_time_in)*24;
981 l_time_in := g_hol_time_in;
982 l_time_out := g_hol_time_out;
983 END IF;
984
985
986 l_ind := l_holidays.FIRST;
987 LOOP
988 EXIT WHEN NOT l_holidays.EXISTS(l_ind);
989 IF l_holidays(l_ind).hours >= l_hours
990 THEN
991 -- If the number of hours to be paid falls above the cap,
992 -- adjusted hours should be the Cap, and the element should be
993 -- the current element.
994 l_adjusted_hours := LEAST(l_hours,l_holidays(l_ind).cap);
995 l_adjusted_element := l_holidays(l_ind).element_type_id;
996 e_ind := e_ind +1;
997 -- If its only hours
998 IF g_time_in IS NULL
999 THEN
1000 l_hol_tab(e_ind).element_type_id := l_adjusted_element;
1001 l_hol_tab(e_ind).hours := l_adjusted_hours;
1002 -- If its time_in/Time_out
1003 ELSE
1004 l_hol_tab(e_ind).element_type_id := l_adjusted_element;
1005 l_hol_tab(e_ind).time_in := l_time_in;
1006 l_hol_tab(e_ind).time_out := l_time_in + (l_adjusted_hours/24);
1007 l_time_in := l_hol_tab(e_ind).time_out;
1008 l_hol_tab(e_ind).hours := l_adjusted_hours;
1009 END IF;
1010 EXIT;
1011 ELSE
1012 l_adjusted_hours := l_holidays(l_ind).cap;
1013 l_adjusted_element := l_holidays(l_ind).element_type_id;
1014 l_hours := l_hours - l_holidays(l_ind).cap;
1015 e_ind := e_ind +1;
1016 l_hol_tab(e_ind).element_type_id := l_adjusted_element;
1017 IF g_time_in IS NULL
1018 THEN
1019 l_hol_tab(e_ind).hours := l_adjusted_hours;
1020 ELSE
1021 l_hol_tab(e_ind).time_in := l_time_in;
1022 l_hol_tab(e_ind).time_out := l_time_in + (l_adjusted_hours/24);
1023 l_time_in := l_hol_tab(e_ind).time_out;
1024 l_hol_tab(e_ind).hours := l_adjusted_hours;
1025 END IF;
1026 END IF;
1027 l_ind := l_holidays.NEXT(l_ind);
1028 END LOOP;
1029
1030
1031 e_ind := l_hol_tab.FIRST;
1032 LOOP
1033
1034 EXIT WHEN NOT l_hol_tab.EXISTS(e_ind);
1035
1036 -- Bug 10123886
1037 -- Need to set this Boolean variable so that SDP
1038 -- is paid correctly.
1039 IF FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION') IN( 'OO','NO')
1040 THEN
1041 hxt_time_detail.g_holiday_sdp_paid := TRUE;
1042 hr_utility.trace('yes '||g_sdp_id);
1043 g_sdf_rule_completed := 'Y';
1044 g_sdf_carryover := null;
1045 ELSE
1046 hxt_time_detail.g_holiday_sdp_paid := FALSE;
1047 hr_utility.trace('No '||g_sdp_id);
1048 END IF;
1049 g_ep_id := g_hol_ep_id;
1050 g_ep_type := g_hol_ep_type;
1051 g_egt_id := g_hol_egt_id;
1052 g_sdp_id := g_hol_sdp_id;
1053 g_hdp_id := g_hol_hdp_id;
1054 g_hol_id := g_hol_hol_id;
1055 g_pep_id := g_hol_pep_id;
1056 g_pip_id := g_hol_pip_id;
1057 g_sdovr_id := g_hol_sdovr_id;
1058 g_osp_id := g_hol_osp_id;
1059 g_standard_start := g_hol_standard_start;
1060 g_standard_stop := g_hol_standard_stop;
1061 g_early_start := g_hol_early_start;
1062 g_late_stop := g_hol_late_stop;
1063 g_hol_yn := g_hol_hol_yn;
1064 g_person_id := g_hol_person_id;
1065 g_ID := g_hol_pk_ID;
1066 g_ROWID := CHARTOROWID(g_hol_ROWID);
1067 g_EFFECTIVE_START_DATE := g_hol_EFFECTIVE_START_DATE;
1068 g_EFFECTIVE_END_DATE := g_hol_EFFECTIVE_END_DATE;
1069 g_TIM_ID := g_hol_TIM_ID;
1070 g_DATE_WORKED := g_hol_DATE_WORKED;
1071 g_ASSIGNMENT_ID := g_hol_ASSIGNMENT_ID;
1072 g_HOURS := l_hol_tab(e_ind).hours;
1073 g_TIME_IN := l_hol_tab(e_ind).time_in;
1074 g_TIME_OUT := l_hol_tab(e_ind).time_out;
1075 g_ELEMENT_TYPE_ID := l_hol_tab(e_ind).element_type_id;
1076 g_FCL_EARN_REASON_CODE := g_hol_FCL_EARN_REASON_CODE;
1077 g_FFV_COST_CENTER_ID := g_hol_FFV_COST_CENTER_ID;
1078 g_FFV_LABOR_ACCOUNT_ID := g_hol_FFV_LABOR_ACCOUNT_ID;
1079 g_TAS_ID := g_hol_TAS_ID;
1080 g_LOCATION_ID := g_hol_LOCATION_ID;
1081 g_SHT_ID := g_hol_SHT_ID;
1082 g_HRW_COMMENT := g_hol_HRW_COMMENT;
1083 g_FFV_RATE_CODE_ID := g_hol_FFV_RATE_CODE_ID;
1084 g_RATE_MULTIPLE := g_hol_RATE_MULTIPLE;
1085 g_HOURLY_RATE := g_hol_HOURLY_RATE;
1086 g_AMOUNT := g_hol_AMOUNT;
1087 g_FCL_TAX_RULE_CODE := g_hol_FCL_TAX_RULE_CODE;
1088 g_SEPARATE_CHECK_FLAG := g_hol_SEPARATE_CHECK_FLAG;
1089 g_SEQNO := g_hol_SEQNO;
1090 g_CREATED_BY := g_hol_CREATED_BY;
1091 g_CREATION_DATE := g_hol_CREATION_DATE;
1092 g_LAST_UPDATED_BY := g_hol_LAST_UPDATED_BY;
1093 g_LAST_UPDATE_DATE := g_hol_LAST_UPDATE_DATE;
1094 g_LAST_UPDATE_LOGIN := g_hol_LAST_UPDATE_LOGIN;
1095 g_PROJECT_ID := g_hol_PROJECT_ID;
1096 g_JOB_ID := g_hol_JOB_ID;
1097 g_PAY_STATUS := g_hol_PAY_STATUS;
1098 g_PA_STATUS := g_hol_PA_STATUS;
1099 g_RETRO_BATCH_ID := g_hol_RETRO_BATCH_ID;
1100 g_DT_UPDATE_MODE := g_hol_DT_UPDATE_MODE;
1101 g_PERIOD_START_DATE := g_hol_PERIOD_START_DATE;
1102 g_STATE_NAME := g_hol_STATE_NAME;
1103 g_COUNTY_NAME := g_hol_COUNTY_NAME;
1104 g_CITY_NAME := g_hol_CITY_NAME;
1105 g_ZIP_CODE := g_hol_ZIP_CODE;
1106 -- g_GROUP_ID := g_hol_GROUP_ID;
1107
1108 IF g_debug
1109 THEN
1110 hr_utility.trace('Hol: Calling rebuild details ');
1111 END IF;
1112
1113 rebuild_details(g_hol_location,l_rebuild_code);
1114 IF l_rebuild_code <> 0
1115 THEN
1116 hr_utility.trace('There is a problem while readjusting holiday ');
1117 RETURN l_rebuild_code;
1118 END IF;
1119
1120
1121 IF g_debug
1122 THEN
1123 hr_utility.trace('Hol: Done Calling rebuild details ');
1124 END IF;
1125
1126
1127 e_ind := l_hol_tab.NEXT(e_ind);
1128 END LOOP;
1129
1130
1131 -- Bug 10123886
1132 -- We need to check and delete the duplicate SDPs here.
1133
1134 IF g_hol_time_in IS NOT NULL
1135 AND FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION') IN( 'NO','OO')
1136 THEN
1137
1138 IF g_debug
1139 THEN
1140 hr_utility.trace('Hol: Checking for duplicate SDPs ');
1141 END IF;
1142
1143 OPEN get_duplicate_sdp(g_id);
1144 FETCH get_duplicate_sdp BULK COLLECT INTO l_id_tab,
1145 l_time_in_tab,
1146 l_time_out_tab,
1147 l_elem_tab,
1148 l_del_tab ;
1149 CLOSE get_duplicate_sdp;
1150
1151
1152
1153 IF l_id_tab.COUNT > 0
1154 THEN
1155
1156 IF g_debug
1157 THEN
1158 hr_utility.trace(' Iteration 1 to find those to be updated ');
1159 END IF;
1160
1161 FOR i IN l_id_tab.FIRST..l_id_tab.LAST
1162 LOOP
1163 IF g_debug
1164 THEN
1165 hr_utility.trace(l_id_tab(i)||'-'||l_elem_tab(i)
1166 ||'-'||TO_CHAR(l_time_in_tab(i),'HH24:MI')
1167 ||'-'||TO_CHAR(l_time_out_tab(i),'HH24:MI') );
1168 END IF;
1169
1170 IF l_id_tab.EXISTS(i+1)
1171 THEN
1172
1173 IF l_elem_tab(i) = l_elem_tab(i+1)
1174 THEN
1175 IF l_time_in_tab(i+1) BETWEEN l_time_in_tab(i)
1176 AND l_time_out_tab(i)
1177 THEN
1178 l_time_in_tab(i+1) := l_time_in_tab(i);
1179 l_del_tab(i+1) := 2;
1180 END IF;
1181 IF l_time_out_tab(i) BETWEEN l_time_in_tab(i+1)
1182 AND l_time_out_tab(i+1)
1183 THEN
1184 l_time_out_tab(i) := l_time_out_tab(i+1);
1185 l_del_tab(i) := 2;
1186 END IF;
1187 END IF;
1188 END IF;
1189
1190 END LOOP;
1191
1192 IF g_debug
1193 THEN
1194 hr_utility.trace(' Iteration 2 to find those to be deleted ');
1195 END IF;
1196
1197
1198 FOR i IN l_id_tab.FIRST..l_id_tab.LAST
1199 LOOP
1200 IF g_debug
1201 THEN
1202 hr_utility.trace(l_id_tab(i)||'-'||l_elem_tab(i)
1203 ||'-'||TO_CHAR(l_time_in_tab(i),'HH24:MI')
1204 ||'-'||TO_CHAR(l_time_out_tab(i),'HH24:MI') );
1205 END IF;
1206 IF l_id_tab.EXISTS(i+1)
1207 THEN
1208 IF l_elem_tab(i) = l_elem_tab(i+1)
1209 THEN
1210 IF l_time_in_tab(i) = l_time_in_tab(i+1)
1211 AND l_time_out_tab(i) = l_time_out_tab(i+1)
1212 THEN
1213 l_del_tab(i) := 1;
1214 END IF;
1215 END IF;
1216 END IF;
1217
1218 END LOOP;
1219
1220
1221 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
1222 UPDATE hxt_det_hours_worked_f
1223 SET time_in = l_time_in_tab(i),
1224 time_out = l_time_out_tab(i),
1225 hours = (l_time_out_tab(i) - l_time_in_tab(i))*24
1226 WHERE id = l_id_tab(i)
1227 AND l_del_tab(i) = 2;
1228
1229 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
1230 DELETE FROM hxt_det_hours_worked_f
1231 WHERE id = l_id_tab(i)
1232 AND l_del_tab(i) = 1;
1233
1234
1235 END IF;
1236
1237
1238 END IF;
1239
1240 clear_globals;
1241 RETURN 0;
1242
1243 EXCEPTION
1244 WHEN NO_DATA_FOUND THEN
1245 clear_globals;
1246 RETURN 0;
1247
1248
1249
1250 END adjust_holiday_rule;
1251
1252
1253
1254 ------------generate details,loads globals and calls gen_details---------------
1255 ---------------return 0 for normal,1 for warning,2 for error-------------------
1256
1257 FUNCTION generate_details
1258 ( p_ep_id IN NUMBER
1259 ,p_ep_type IN VARCHAR2
1260 ,p_egt_id IN NUMBER
1261 ,p_sdp_id IN NUMBER
1262 ,p_hdp_id IN NUMBER
1263 ,p_hol_id IN NUMBER
1264 ,p_pep_id IN NUMBER
1265 ,p_pip_id IN NUMBER
1266 ,p_sdovr_id IN NUMBER
1267 ,p_osp_id IN NUMBER
1268 ,p_standard_start IN NUMBER
1269 ,p_standard_stop IN NUMBER
1270 ,p_early_start IN NUMBER
1271 ,p_late_stop IN NUMBER
1272 ,p_hol_yn IN VARCHAR2
1273 ,p_person_id IN NUMBER
1274 ,p_location IN VARCHAR2
1275 ,p_ID IN NUMBER
1276 ,p_TIM_ID IN NUMBER
1277 ,p_DATE_WORKED IN DATE
1278 ,p_ASSIGNMENT_ID IN NUMBER
1279 ,p_HOURS IN NUMBER
1280 ,p_TIME_IN IN DATE
1281 ,p_TIME_OUT IN DATE
1282 ,p_ELEMENT_TYPE_ID IN NUMBER
1283 ,p_FCL_EARN_REASON_CODE IN VARCHAR2
1284 ,p_FFV_COST_CENTER_ID IN NUMBER
1285 ,p_FFV_LABOR_ACCOUNT_ID IN NUMBER
1286 ,p_TAS_ID IN NUMBER
1287 ,p_LOCATION_ID IN NUMBER
1288 ,p_SHT_ID IN NUMBER
1289 ,p_HRW_COMMENT IN VARCHAR2
1290 ,p_FFV_RATE_CODE_ID IN NUMBER
1291 ,p_RATE_MULTIPLE IN NUMBER
1292 ,p_HOURLY_RATE IN NUMBER
1293 ,p_AMOUNT IN NUMBER
1294 ,p_FCL_TAX_RULE_CODE IN VARCHAR2
1295 ,p_SEPARATE_CHECK_FLAG IN VARCHAR2
1296 ,p_SEQNO IN NUMBER
1297 ,p_CREATED_BY IN NUMBER
1298 ,p_CREATION_DATE IN DATE
1299 ,p_LAST_UPDATED_BY IN NUMBER
1300 ,p_LAST_UPDATE_DATE IN DATE
1301 ,p_LAST_UPDATE_LOGIN IN NUMBER
1302 ,p_PERIOD_START_DATE IN DATE --SPR C389
1303 ,p_ROWIDIN IN VARCHAR2 --SIR012
1304 ,p_EFFECTIVE_START_DATE IN DATE --SIR012
1305 ,p_EFFECTIVE_END_DATE IN DATE --SIR012
1306 ,p_PROJECT_ID IN NUMBER --SIR022
1307 ,p_JOB_ID IN NUMBER --SIR015
1308 ,p_PAY_STATUS IN VARCHAR2 --SIR020
1309 ,p_PA_STATUS IN VARCHAR2 --SIR022
1310 ,p_RETRO_BATCH_ID IN NUMBER --SIR020
1311 ,p_DT_UPDATE_MODE IN VARCHAR2 --SIR020
1312 ,p_CALL_ADJUST_ABS IN VARCHAR2 DEFAULT 'Y'
1313 ,p_STATE_NAME IN VARCHAR2 DEFAULT NULL
1314 ,p_COUNTY_NAME IN VARCHAR2 DEFAULT NULL
1315 ,p_CITY_NAME IN VARCHAR2 DEFAULT NULL
1316 ,p_ZIP_CODE IN VARCHAR2 DEFAULT NULL
1317 --,p_GROUP_ID IN NUMBER -- HXT11i1
1318 ) RETURN NUMBER IS
1319
1320 l_location VARCHAR2(120);
1321 l_error_code NUMBER := 0;
1322 l_rebuild_code NUMBER := 0;
1323 shift_adjusted_time_in DATE;
1324 shift_adjusted_time_out DATE;
1325
1326 otl_recurring_period VARCHAR2(120);
1327 l_period_start_date hxc_recurring_periods.start_date%TYPE;
1328 l_period_start DATE;
1329 l_period_end DATE;
1330 l_period_type hxc_recurring_periods.period_type%TYPE;
1331
1332
1333 -- Bug 7359347
1334 -- Return code for valid_data function, to avoid multiple
1335 -- calls.
1336 l_vd_retcode NUMBER;
1337
1338
1339 -- Bug 11837942
1340 -- Added this cursor to pick up the previous timecard id
1341 -- in case the week start date is not the start_date of the timecard.
1342
1343 CURSOR get_prev_ids( p_tim_id NUMBER,
1344 p_session_date DATE,
1345 p_week_start DATE,
1346 p_person_id NUMBER)
1347 IS SELECT /*+ ORDERED */
1348 prev.id
1349 FROM hxt_timecards_f tim,
1350 per_time_periods ptp,
1351 hxt_timecards_f prev
1352 WHERE tim.id = p_tim_id
1353 AND p_session_date BETWEEN tim.effective_start_date
1354 AND tim.effective_end_date
1355 AND tim.payroll_id = ptp.payroll_id
1356 AND p_week_start BETWEEN ptp.start_date
1357 AND ptp.end_date
1358 AND prev.time_period_id = ptp.time_period_id
1359 AND prev.for_person_id = p_person_id
1360 AND p_session_date BETWEEN prev.effective_start_date
1361 AND prev.effective_end_date
1362 AND prev.id <> tim.id ;
1363
1364
1365
1366 l_prev_id NUMBER;
1367
1368
1369 /* Bug: 4489952 changes starts here */
1370
1371 -- procedure to insert the non-explosion timecard entries directly
1372 -- into table 'hxt_det_hours_worked_f' without calling the explosion code.
1373 PROCEDURE insert_non_explodable_hrs
1374 IS
1375 l_object_version_number HXT_DET_HOURS_WORKED_F.OBJECT_VERSION_NUMBER%TYPE;
1376 l_rowid ROWID;
1377 l_id NUMBER;
1378 l_pay_status VARCHAR2(1);
1379 l_pa_status VARCHAR2(1);
1380 l_retro_batch_id NUMBER(15);
1381 l_error_status NUMBER(15);
1382 l_sqlerrm VARCHAR2(200);
1383 l_rate_multiple HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
1384 l_hourly_rate HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
1385 l_amount HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
1386 l_hours HXT_DET_HOURS_WORKED_F.HOURS%TYPE; -- SIR092
1387 l_costable_type PAY_ELEMENT_LINKS_F.COSTABLE_TYPE%TYPE;
1388 l_ffv_cost_center_id HXT_DET_HOURS_WORKED_F.FFV_COST_CENTER_ID%TYPE;
1389 l_premium_type HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_TYPE%TYPE;
1390 l_premium_amount HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PREMIUM_AMOUNT%TYPE;
1391 l_processing_order HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_PROCESSING_ORDER%TYPE;
1392
1393 CURSOR next_id_cur IS
1394 SELECT hxt_seqno.nextval next_id
1395 FROM dual;
1396
1397 CURSOR get_ovt_rates_cur IS
1398 SELECT eltv.hxt_premium_type,
1399 eltv.hxt_premium_amount,
1400 eltv.hxt_processing_order
1401 FROM hxt_pay_element_types_f_ddf_v eltv
1402 WHERE eltv.hxt_earning_category NOT IN ('REG', 'ABS')
1403 AND g_DATE_WORKED between eltv.effective_start_date
1404 and eltv.effective_end_date
1405 AND eltv.element_type_id = g_element_type_id
1406 ORDER by eltv.hxt_processing_order;
1407
1408 BEGIN
1409
1410 IF g_debug THEN
1411 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',10);
1412 END IF;
1413
1414 l_hours := p_hours ; -- SIR092
1415
1416 OPEN next_id_cur;
1417 FETCH next_id_cur INTO l_id;
1418
1419 IF g_debug THEN
1420 hr_utility.trace('l_id :'||l_id);
1421 END IF;
1422
1423 CLOSE next_id_cur;
1424
1425 OPEN get_ovt_rates_cur;
1426 FETCH get_ovt_rates_cur
1427 INTO l_premium_type, l_premium_amount, l_processing_order;
1428
1429 IF g_debug THEN
1430 hr_utility.trace('premium_type :'||l_premium_type);
1431 hr_utility.trace('premium_amount :'||l_premium_amount);
1432 hr_utility.trace('processing_order :'||l_processing_order);
1433 END IF;
1434
1435 CLOSE get_ovt_rates_cur;
1436
1437 IF l_premium_type = 'FACTOR' THEN
1438 l_rate_multiple := l_premium_amount;
1439 ELSIF l_premium_type = 'RATE' THEN
1440 l_hourly_rate := l_premium_amount;
1441 ELSIF l_premium_type = 'FIXED' THEN
1442 l_amount := l_premium_amount;
1443 l_hours := 0 ; -- SIR092 Hours have no meaning with
1444 -- flat amount premiums
1445 END IF;
1446
1447 -- Any values passed in from globals will override retrieved values.
1448
1449 IF g_rate_multiple IS NOT NULL THEN
1450 l_rate_multiple := g_rate_multiple ;
1451 END IF ;
1452 IF g_hourly_rate IS NOT NULL THEN
1453 l_hourly_rate := g_hourly_rate ;
1454 END IF ;
1455 IF g_amount IS NOT NULL THEN
1456 l_amount := g_amount ;
1457 END IF ;
1458
1459 l_costable_type := HXT_UTIL.get_costable_type(g_element_type_id,
1460 g_date_worked,
1461 g_assignment_id);
1462 IF g_debug THEN
1463 hr_utility.trace('l_costable_type :'||l_costable_type);
1464 END IF;
1465
1466 IF l_costable_type in ('C','F') THEN
1467 l_ffv_cost_center_id := g_ffv_cost_center_id;
1468 ELSE
1469 l_ffv_cost_center_id := NULL;
1470 END IF;
1471
1472 hxt_time_pay.get_retro_fields( g_tim_id
1473 ,HXT_TIME_COLLECTION.g_batch_name
1474 ,HXT_TIME_COLLECTION.g_batch_ref
1475 ,l_pay_status
1476 ,l_pa_status
1477 ,l_retro_batch_id
1478 ,l_error_status
1479 ,l_sqlerrm);
1480
1481 IF l_error_status = 0 THEN
1482 IF g_debug then
1483 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',20);
1484 END IF;
1485
1486 HXT_DML.INSERT_HXT_DET_HOURS_WORKED (
1487 p_rowid => l_rowid,
1488 p_id => l_id,
1489 p_parent_id => g_id,
1490 p_tim_id => g_tim_id,
1491 p_date_worked => g_date_worked,
1492 p_assignment_id => g_assignment_id,
1493 p_hours => l_hours,
1494 p_time_in => g_time_in,
1495 p_time_out => g_time_out,
1496 p_element_type_id => g_element_type_id,
1497 p_fcl_earn_reason_code => g_fcl_earn_reason_code,
1498 p_ffv_cost_center_id => l_ffv_cost_center_id,
1499 p_ffv_labor_account_id => NULL,
1500 p_tas_id => g_TAS_ID,
1501 p_location_id => g_location_id,
1502 p_sht_id => g_sht_id,
1503 p_hrw_comment => g_hrw_comment,
1504 p_ffv_rate_code_id => g_ffv_rate_code_id,
1505 p_rate_multiple => l_rate_multiple,
1506 p_hourly_rate => l_hourly_rate,
1507 p_amount => l_amount,
1508 p_fcl_tax_rule_code => g_fcl_tax_rule_code,
1509 p_separate_check_flag => g_separate_check_flag,
1510 p_seqno => g_seqno,
1511 p_created_by => g_created_by,
1512 p_creation_date => g_creation_date,
1513 p_last_updated_by => g_last_updated_by,
1514 p_last_update_date => g_last_update_date,
1515 p_last_update_login => g_last_update_login,
1516 p_actual_time_in => NULL,
1517 p_actual_time_out => NULL,
1518 p_effective_start_date => g_effective_start_date,
1519 p_effective_end_date => g_effective_end_date,
1520 p_project_id => g_project_id,
1521 p_job_id => NULL,
1522 p_earn_pol_id => NULL,
1523 p_retro_batch_id => l_retro_batch_id,
1524 p_pa_status => l_pa_status,
1525 p_pay_status => l_pay_status,
1526 --p_group_id => g_group_id,
1527 p_object_version_number => l_object_version_number,
1528 p_STATE_NAME => g_STATE_NAME,
1529 p_COUNTY_NAME => g_COUNTY_NAME,
1530 p_CITY_NAME => g_CITY_NAME,
1531 p_ZIP_CODE => g_ZIP_CODE);
1532
1533 ELSE /* l_error_status <> 0 */
1534 IF g_debug then
1535 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 30);
1536 END IF;
1537 -- Insert record in error table.
1538 FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
1539 END IF;
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 IF g_debug THEN
1544 hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
1545 END IF;
1546 hr_utility.trace(dbms_utility.format_error_backtrace);
1547 -- Insert record in error table.
1548 FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
1549
1550 END insert_non_explodable_hrs;
1551
1552 Function check_non_explosion_entry (p_element_type_id IN NUMBER, p_date_worked DATE)
1553 RETURN BOOLEAN IS
1554 l_non_explosion_flag VARCHAR2(1);
1555
1556 CURSOR check_non_explosion_entry IS
1557 SELECT 'Y'
1558 FROM hxt_add_elem_info_f
1559 WHERE element_type_id = p_element_type_id
1560 AND p_date_worked BETWEEN effective_start_date
1561 AND effective_end_date
1562 AND NVL(exclude_from_explosion, 'N') = 'Y';
1563
1564 BEGIN
1565
1566 FOR i in check_non_explosion_entry
1567 LOOP
1568 l_non_explosion_flag := 'Y';
1569 END LOOP;
1570
1571 IF l_non_explosion_flag = 'Y' THEN
1572 Return TRUE;
1573 ELSE
1574 Return FALSE;
1575 END IF;
1576 END check_non_explosion_entry;
1577
1578
1579
1580
1581 /* Bug: 4489952 changes ends here */
1582
1583 BEGIN
1584 g_debug :=hr_utility.debug_enabled;
1585 if g_debug then
1586 hr_utility.set_location('hxt_time_summary.generate_details',10);
1587 end if;
1588
1589 -- Set the flags for checking which shift diff premium gets paid for a
1590 -- segment chunk.
1591 g_sdf_rule_completed := 'Y';
1592 g_sdf_carryover := null;
1593
1594 -- Set global variables for package with parameter values
1595 g_ep_id := p_ep_id;
1596 g_ep_type := p_ep_type;
1597 g_egt_id := p_egt_id;
1598 g_sdp_id := p_sdp_id;
1599 g_hdp_id := p_hdp_id;
1600 g_hol_id := p_hol_id;
1601 g_pep_id := p_pep_id;
1602 g_pip_id := p_pip_id;
1603 g_sdovr_id := p_sdovr_id;
1604 g_osp_id := p_osp_id;
1605 g_standard_start := p_standard_start;
1606 g_standard_stop := p_standard_stop;
1607 g_early_start := p_early_start;
1608 g_late_stop := p_late_stop;
1609 g_hol_yn := p_hol_yn;
1610 g_person_id := p_person_id;
1611 g_ID := p_ID;
1612 g_ROWID := CHARTOROWID(p_ROWIDIN);
1613 g_EFFECTIVE_START_DATE := p_EFFECTIVE_START_DATE;
1614 g_EFFECTIVE_END_DATE := p_EFFECTIVE_END_DATE;
1615 g_TIM_ID := p_TIM_ID;
1616 g_DATE_WORKED := p_DATE_WORKED;
1617 g_ASSIGNMENT_ID := p_ASSIGNMENT_ID;
1618 g_HOURS := p_HOURS;
1619 g_TIME_IN := p_TIME_IN;
1620 g_TIME_OUT := p_TIME_OUT;
1621 g_ELEMENT_TYPE_ID := p_ELEMENT_TYPE_ID;
1622 g_FCL_EARN_REASON_CODE := p_FCL_EARN_REASON_CODE;
1623 g_FFV_COST_CENTER_ID := p_FFV_COST_CENTER_ID;
1624 g_FFV_LABOR_ACCOUNT_ID := p_FFV_LABOR_ACCOUNT_ID;
1625 g_TAS_ID := p_TAS_ID;
1626 g_LOCATION_ID := p_LOCATION_ID;
1627 g_SHT_ID := p_SHT_ID;
1628 g_HRW_COMMENT := p_HRW_COMMENT;
1629 g_FFV_RATE_CODE_ID := p_FFV_RATE_CODE_ID;
1630 g_RATE_MULTIPLE := p_RATE_MULTIPLE;
1631 g_HOURLY_RATE := p_HOURLY_RATE;
1632 g_AMOUNT := p_AMOUNT;
1633 g_FCL_TAX_RULE_CODE := p_FCL_TAX_RULE_CODE;
1634 g_SEPARATE_CHECK_FLAG := p_SEPARATE_CHECK_FLAG;
1635 g_SEQNO := p_SEQNO;
1636 g_CREATED_BY := p_CREATED_BY;
1637 g_CREATION_DATE := p_CREATION_DATE;
1638 g_LAST_UPDATED_BY := p_LAST_UPDATED_BY;
1639 g_LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
1640 g_LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
1641 g_PROJECT_ID := p_PROJECT_ID;
1642 g_JOB_ID := p_JOB_ID;
1643 g_PAY_STATUS := p_PAY_STATUS;
1644 g_PA_STATUS := p_PA_STATUS;
1645 g_RETRO_BATCH_ID := p_RETRO_BATCH_ID;
1646 g_DT_UPDATE_MODE := p_DT_UPDATE_MODE;
1647 g_PERIOD_START_DATE := p_PERIOD_START_DATE;
1648 g_CALL_ADJUST_ABS := p_CALL_ADJUST_ABS;
1649 g_STATE_NAME :=p_STATE_NAME;
1650 g_COUNTY_NAME :=p_COUNTY_NAME;
1651 g_CITY_NAME :=p_CITY_NAME;
1652 g_ZIP_CODE :=p_ZIP_CODE;
1653 -- g_GROUP_ID := p_GROUP_ID;
1654
1655
1656
1657
1658 -- Bug 7359347
1659 -- Setting session date to the global variable
1660 IF hxt_tim_col_util.g_session_date.EXISTS(USERENV('SESSIONID'))
1661 THEN
1662 g_sum_session_date := hxt_tim_col_util.g_session_date(USERENV('SESSIONID'));
1663 ELSE
1664 l_error_code := hxt_tim_col_util.get_session_date(g_sum_session_date);
1665 END IF;
1666
1667 l_error_code := 0 ;
1668
1669
1670
1671
1672 -- generate_details returns 0 if no errors encountered,-1 to signal the
1673 -- Timecard that a summary status was changed from E,2 for errors that stopped
1674 -- processing on current record,3 for errors that stopped processing on related
1675 -- summaries and -2 for warning that changed records prevented complete rebuild
1676
1677 -- g_start_day_of_week := 'MON';
1678 -- hard coded for now must match hxt_time_details start day
1679
1680 --
1681 -- Get the Recurring Period assigned to the person. The recurring period is
1682 -- setup as a Preference - Self-Service -> Otl Rules Evaluation -> Overtime
1683 -- Recurring Period.
1684 -- Context of the Preference is TC_W_RULES_EVALUATION
1685 -- Segment is attribute 3 OT_RECURRING_PERIOD
1686 --
1687 if g_debug then
1688 hr_utility.set_location('hxt_time_summary.generate_details',20);
1689 end if;
1690
1691 -- Changed the following call for Bug 12689562
1692 IF NOT g_rec_period_id.EXISTS(to_char(p_tim_id))
1693 THEN
1694
1695 otl_recurring_period := hxc_preference_evaluation.resource_preferences
1696 (p_resource_id => g_person_id
1697 ,p_pref_code=>'TC_W_RULES_EVALUATION'
1698 ,p_attribute_n=>3
1699 ,p_evaluation_date => p_date_worked,
1700 p_resp_id => FND_GLOBAL.resp_id);
1701 g_rec_period_id(to_char(p_tim_id)) := otl_recurring_period;
1702
1703 ELSE
1704
1705 otl_recurring_period := g_rec_period_id(to_char(p_tim_id));
1706
1707 END IF;
1708
1709 if g_debug then
1710 hr_utility.set_location('hxt_time_summary.generate_details',30);
1711 hr_utility.trace('otl_recurring_period :'||otl_recurring_period);
1712 end if;
1713 --
1714 -- Now calculate the start day as per the otl recurring period
1715 --
1716 if g_debug then
1717 hr_utility.set_location('hxt_time_summary.generate_details',40);
1718 end if;
1719 IF otl_recurring_period is NOT NULL THEN
1720 --
1721 -- Get the period start_date, period_type for the recurring_period
1722 --
1723 if g_debug then
1724 hr_utility.set_location('hxt_time_summary.generate_details',50);
1725 end if;
1726 SELECT start_date,period_type
1727 INTO l_period_start_date,l_period_type
1728 FROM hxc_recurring_periods
1729 WHERE recurring_period_id = otl_recurring_period;
1730
1731 if g_debug then
1732 hr_utility.trace('l_period_start_date :'
1733 || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
1734 hr_utility.trace('l_period_type :'|| l_period_type);
1735 hr_utility.trace('g_date_worked :'
1736 || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1737 hr_utility.set_location('hxt_time_summary.generate_details',60);
1738 end if;
1739 hxc_period_evaluation.period_start_stop
1740 (g_date_worked
1741 ,l_period_start_date
1742 ,l_period_start
1743 ,l_period_end
1744 ,l_period_type);
1745
1746 if g_debug then
1747 hr_utility.set_location('hxt_time_summary.generate_details',70);
1748 hr_utility.trace('l_period_start :'
1749 || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
1750 hr_utility.trace('l_period_end :'
1751 || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
1752 end if;
1753 g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
1754 if g_debug then
1755 hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
1756 hr_utility.set_location('hxt_time_summary.generate_details',80);
1757 end if;
1758
1759 -- Bug 11837942
1760 -- Added this construct here to store the previous id of the timecard
1761 -- in case the Week start date does not commence with the start date
1762 -- of the timecard.
1763 IF NOT g_sum_prev_ids.EXISTS(TO_CHAR(p_tim_id))
1764 THEN
1765 OPEN get_prev_ids(p_tim_id,
1766 g_sum_session_date ,
1767 l_period_start ,
1768 p_person_id);
1769 LOOP
1770 FETCH get_prev_ids
1771 INTO l_prev_id;
1772 EXIT WHEN get_prev_ids%NOTFOUND;
1773
1774 g_sum_prev_ids(to_char(p_tim_id)) := l_prev_id;
1775
1776 IF g_debug
1777 THEN
1778 hr_utility.trace('OTL : l_prev_id '||l_prev_id);
1779 END IF;
1780
1781 END LOOP;
1782 CLOSE get_prev_ids;
1783 END IF;
1784
1785
1786 ELSE
1787 if g_debug then
1788 hr_utility.set_location('hxt_time_summary.generate_details',90);
1789 hr_utility.trace('p_PERIOD_START_DATE :'
1790 || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
1791 end if;
1792 g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
1793 if g_debug then
1794 hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
1795 end if;
1796 l_location := p_location||':GD';
1797 g_hol_location := l_location;
1798
1799 END IF;
1800
1801 if g_debug then
1802 hr_utility.set_location('hxt_time_summary.generate_details',100);
1803 end if;
1804
1805 -- Bug 7359347
1806 -- Execute valid data and capture that.
1807 l_vd_retcode := valid_data(l_location);
1808
1809 IF l_vd_retcode NOT IN (0,1) THEN
1810 -- check for time in/out and hour values
1811 if g_debug then
1812 hr_utility.set_location('hxt_time_summary.generate_details',110);
1813 end if;
1814 RETURN(2);
1815 ELSIF l_vd_retcode = 1 THEN
1816 if g_debug then
1817 hr_utility.set_location('hxt_time_summary.generate_details',120);
1818 end if;
1819 RETURN(11);
1820 END IF;
1821
1822 IF g_element_type_id is not null THEN
1823 if g_debug then
1824 hr_utility.set_location('hxt_time_summary.generate_details',130);
1825 end if;
1826 -- Determine if earning is in assigned INCLUDE earning group
1827 g_include_yn := Get_Include(l_location
1828 ,g_egt_id
1829 ,g_element_type_id
1830 ,g_date_worked);
1831
1832 IF g_include_yn = 'E' THEN
1833 if g_debug then
1834 hr_utility.set_location('hxt_time_summary.generate_details',140);
1835 end if;
1836 RETURN 2;
1837
1838 END IF;
1839
1840 -- Bug 16192990
1841 -- Commented out the ELSIF here, because we need to call this even if an Overriden entry is
1842 -- present.
1843 -- Moved this procedure call outside -- IF g_element_type_id is not null THEN
1844 /*
1845 ELSIF NVL(FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION'),'EX') <> 'EX'
1846 THEN
1847 NULL;
1848 -- check_holiday_rule_behavior;
1849 -- Do caching the values for holiday.
1850 -- Set flag
1851
1852 */
1853
1854 END IF; --element_type_id is not null
1855
1856 IF NVL(FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION'),'EX') <> 'EX'
1857 THEN
1858 -- Do caching the values for holiday.
1859 -- Set flag
1860 check_holiday_rule_behavior;
1861 END IF;
1862
1863 IF l_error_code < 2 THEN
1864 if g_debug then
1865 hr_utility.set_location('hxt_time_summary.generate_details',150);
1866 end if;
1867
1868 /* Bug: 4489952 changes starts here */
1869
1870 IF check_non_explosion_entry (g_element_type_id, g_date_worked) THEN
1871 IF g_debug then
1872 hr_utility.set_location('hxt_time_summary.generate_details',151);
1873 END IF;
1874
1875 insert_non_explodable_hrs;
1876
1877 ELSE /* check_non_explosion_entry = FALSE */
1878
1879 IF g_debug then
1880 hr_utility.set_location('hxt_time_summary.generate_details',155);
1881 END IF;
1882
1883 rebuild_details( l_location, l_rebuild_code);
1884 END IF;
1885
1886 /* Bug: 4489952 changes ends here */
1887
1888 END IF;
1889
1890 IF l_rebuild_code <> 0 THEN
1891 if g_debug then
1892 hr_utility.set_location('hxt_time_summary.generate_details',160);
1893 end if;
1894 RETURN l_rebuild_code;
1895 ELSE
1896 if g_debug then
1897 hr_utility.set_location('hxt_time_summary.generate_details',170);
1898 end if;
1899 -- Bug 8600894
1900 IF NVL(FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION'),'EX') <> 'EX'
1901 THEN
1902 l_error_code := adjust_holiday_rule;
1903 END IF;
1904 RETURN l_error_code;
1905 END IF;
1906
1907 EXCEPTION
1908
1909 WHEN OTHERS THEN
1910 if g_debug then
1911 hr_utility.set_location('hxt_time_summary.generate_details',180);
1912 end if;
1913 hr_utility.trace(dbms_utility.format_error_backtrace);
1914 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1915 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm);
1916 --2278400 RETURN call_gen_error(l_location, '', sqlerrm);
1917 END; -- generate_details
1918
1919 PROCEDURE time_in_dates(ln_start in number
1920 ,ln_stop in number
1921 ,ln_carryover in number
1922 ,time_in out nocopy date
1923 ,time_out out nocopy date
1924 ,carryover_time out nocopy date
1925 ,l_date_worked in date)
1926 IS
1927
1928 BEGIN
1929 g_debug :=hr_utility.debug_enabled;
1930 if g_debug then
1931 hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932 hr_utility.trace('ln_start :'||ln_start);
1933 hr_utility.trace('ln_stop :'||ln_stop);
1934 hr_utility.trace('ln_carryover :'||ln_carryover);
1935 end if;
1936 time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
1937 ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
1938 if g_debug then
1939 hr_utility.trace('time_in :'
1940 || to_char(time_in,'DD-MON-YYYY HH24:MI:SS'));
1941 end if;
1942 IF ln_start < ln_stop OR
1943 (ln_start = 0 AND
1944 ln_stop = 0) THEN
1945 if g_debug then
1946 hr_utility.set_location('hxt_time_summary.time_in_dates',20);
1947 end if;
1948 time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
1949 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1950 if g_debug then
1951 hr_utility.trace('time_out :'
1952 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1953 end if;
1954 ELSE --IF ln_start >= ln_stop
1955 if g_debug then
1956 hr_utility.set_location('hxt_time_summary.time_in_dates',30);
1957 end if;
1958 time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1959 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1960 if g_debug then
1961 hr_utility.trace('time_out :'
1962 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1963 end if;
1964 END IF;
1965
1966 IF ln_carryover is NOT NULL THEN
1967 if g_debug then
1968 hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1969 end if;
1970 IF ln_start < ln_carryover THEN
1971 if g_debug then
1972 hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1973 end if;
1974 carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
1975 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1976
1977 if g_debug then
1978 hr_utility.trace('carryover_time :'
1979 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1980 end if;
1981 ELSE --IF ln_start >= ln_carryover
1982 if g_debug then
1983 hr_utility.set_location('hxt_time_summary.time_in_dates',50);
1984 end if;
1985 carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1986 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1987
1988 if g_debug then
1989 hr_utility.trace('carryover_time :'
1990 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1991 end if;
1992 END IF;
1993
1994 END IF;
1995
1996 END;
1997
1998 FUNCTION call_gen_error
1999 (p_location IN varchar2
2000 ,p_error_text IN VARCHAR2
2001 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER IS
2002
2003 dummy NUMBER;
2004
2005 -- calls error processing procedure
2006
2007 BEGIN
2008
2009 if g_debug then
2010 hr_utility.set_location('hxt_time_summary.call_gen_error',10);
2011 end if;
2012 --Checking of the hours worked table for the current id is done so that
2013 --validation is not done on a record being deleted.
2014
2015 hxt_util.gen_error(g_tim_id
2016 ,g_id
2017 ,NULL
2018 ,p_error_text
2019 ,p_location
2020 ,p_oracle_error_text
2021 ,g_EFFECTIVE_START_DATE
2022 ,g_EFFECTIVE_END_DATE
2023 ,'ERR');
2024
2025 if g_debug then
2026 hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2027 end if;
2028 RETURN 2;
2029
2030 EXCEPTION
2031
2032 WHEN NO_DATA_FOUND THEN
2033 if g_debug then
2034 hr_utility.set_location('hxt_time_summary.call_gen_error',30);
2035 end if;
2036 RETURN 0;
2037
2038 WHEN OTHERS THEN
2039 if g_debug then
2040 hr_utility.set_location('hxt_time_summary.call_gen_error',40);
2041 end if;
2042 hxt_util.gen_error(g_tim_id
2043 ,g_id
2044 ,NULL
2045 ,p_error_text
2046 ,p_location
2047 ,p_oracle_error_text
2048 ,g_EFFECTIVE_START_DATE
2049 ,g_EFFECTIVE_END_DATE
2050 ,'ERR');
2051 if g_debug then
2052 hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2053 end if;
2054 hr_utility.trace(dbms_utility.format_error_backtrace);
2055 RETURN 2;
2056 END;
2057
2058 FUNCTION call_hxthxc_gen_error
2059 ( p_app_short_name IN VARCHAR2
2060 ,p_msg_name IN VARCHAR2
2061 ,p_msg_token IN VARCHAR2
2062 ,p_location IN varchar2
2063 ,p_error_text IN VARCHAR2
2064 ,p_oracle_error_text IN VARCHAR2 default NULL) RETURN NUMBER IS
2065
2066 dummy NUMBER;
2067
2068 -- calls error processing procedure
2069
2070 BEGIN
2071
2072 if g_debug then
2073 hr_utility.set_location('hxt_time_summary.call_gen_error',10);
2074 end if;
2075 --Checking of the hours worked table for the current id is done so that
2076 --validation is not done on a record being deleted.
2077
2078 hxt_util.gen_error(g_tim_id
2079 ,g_id
2080 ,NULL
2081 ,p_error_text
2082 ,p_location
2083 ,p_oracle_error_text
2084 ,g_EFFECTIVE_START_DATE
2085 ,g_EFFECTIVE_END_DATE
2086 ,'ERR');
2087
2088 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2089 p_message_table=> hxt_hxc_retrieval_process.g_otm_messages,
2090 p_message_name=> p_msg_name,
2091 p_message_token=> NULL ,
2092 p_message_level=> 'ERROR',
2093 p_message_field=> NULL,
2094 p_application_short_name=> p_app_short_name,
2095 p_timecard_bb_id=> null,
2096 p_time_attribute_id=> NULL,
2097 p_timecard_bb_ovn=> NULL,
2098 p_time_attribute_ovn=> NULL
2099 );
2100 if g_debug then
2101 hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
2102 hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2103 end if;
2104 RETURN 2;
2105
2106 EXCEPTION
2107
2108 WHEN NO_DATA_FOUND THEN
2109 if g_debug then
2110 hr_utility.set_location('hxt_time_summary.call_gen_error',30);
2111 end if;
2112 RETURN 0;
2113
2114 WHEN OTHERS THEN
2115 if g_debug then
2116 hr_utility.set_location('hxt_time_summary.call_gen_error',40);
2117 end if;
2118 hxt_util.gen_error(g_tim_id
2119 ,g_id
2120 ,NULL
2121 ,p_error_text
2122 ,p_location
2123 ,p_oracle_error_text
2124 ,g_EFFECTIVE_START_DATE
2125 ,g_EFFECTIVE_END_DATE
2126 ,'ERR');
2127
2128 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2129 p_message_table=> hxt_hxc_retrieval_process.g_otm_messages,
2130 p_message_name=> p_msg_name,
2131 p_message_token=> substr(p_msg_token,1,240),
2132 p_message_level=> 'ERROR',
2133 p_message_field=> NULL,
2134 p_application_short_name=> p_app_short_name,
2135 p_timecard_bb_id=> null,
2136 p_time_attribute_id=> NULL,
2137 p_timecard_bb_ovn=> NULL,
2138 p_time_attribute_ovn=> NULL
2139 );
2140
2141 if g_debug then
2142 hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2143 end if;
2144 hr_utility.trace(dbms_utility.format_error_backtrace);
2145 RETURN 2;
2146 END;
2147
2148 FUNCTION valid_data(p_location IN VARCHAR2) RETURN NUMBER IS
2149
2150 -- Checks in and out times to ensure that if one is present both are
2151 -- If no times are there hours must be there
2152
2153 l_cat VARCHAR2(10);
2154 error_code NUMBER := 0;
2155 location VARCHAR2(120) := p_location||':VD';
2156 l_non_exp_elem_count NUMBER; /* Bug: 4489952 */
2157
2158 BEGIN
2159
2160
2161
2162 -- Bug 7359347
2163 -- set session date.
2164 IF g_sum_session_date IS NULL
2165 THEN
2166 error_code := hxt_tim_col_util.get_session_date(g_sum_session_date);
2167 END IF;
2168
2169 if g_debug then
2170 hr_utility.set_location('hxt_time_summary.valid_data',10);
2171 end if;
2172 IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
2173
2174 if g_debug then
2175 hr_utility.set_location('hxt_time_summary.valid_data',20);
2176 end if;
2177 FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
2178 RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
2179
2180 END IF;
2181
2182 IF g_time_out IS NULL AND g_time_in IS NOT NULL THEN
2183
2184 if g_debug then
2185 hr_utility.set_location('hxt_time_summary.valid_data',30);
2186 end if;
2187 FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
2188 RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
2189
2190 END IF;
2191
2192 IF g_time_in IS NULL AND g_hours IS NULL THEN
2193
2194 if g_debug then
2195 hr_utility.set_location('hxt_time_summary.valid_data',40);
2196 end if;
2197 FND_MESSAGE.SET_NAME('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ');
2198 RETURN call_hxthxc_gen_error('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ',NULL,location, '');
2199
2200 END IF;
2201
2202 IF g_element_type_id IS NOT NULL THEN
2203
2204 if g_debug then
2205 hr_utility.set_location('hxt_time_summary.valid_data',50);
2206 end if;
2207 l_cat := hxt_util.element_cat(g_element_type_id
2208 ,g_date_worked);
2209
2210 END IF;
2211
2212 -- Hours or times must be consistent across all summary records in a day
2213
2214 BEGIN
2215 g_debug :=hr_utility.debug_enabled;
2216 if g_debug then
2217 hr_utility.set_location('hxt_time_summary.valid_data',60);
2218 end if;
2219
2220 /* Bug: 4489952 changes starts here */
2221
2222 SELECT count(1)
2223 INTO l_non_exp_elem_count
2224 FROM hxt_add_elem_info_f hei
2225 WHERE hei.element_type_id = g_element_type_id
2226 AND g_date_worked BETWEEN hei.effective_start_date
2227 AND hei.effective_end_date
2228 AND NVL(hei.exclude_from_explosion, 'N') = 'Y';
2229
2230 IF (g_hours<>0) and l_non_exp_elem_count = 0 THEN
2231 IF g_TIME_IN IS NULL THEN
2232 SELECT '1'
2233 INTO error_code
2234 FROM SYS.DUAL
2235 WHERE EXISTS (SELECT hrw.id
2236 FROM hxt_sum_hours_worked hrw, hxt_add_elem_info_f hei
2237 WHERE hrw.tim_id = g_TIM_ID
2238 AND hrw.date_worked = g_DATE_WORKED
2239 AND hrw.time_in IS NOT NULL
2240 AND hrw.element_type_id = hei.element_type_id
2241 AND hrw.element_type_id is not null
2242 AND g_date_worked BETWEEN hei.effective_start_date
2243 AND hei.effective_end_date
2244 AND NVL(hei.exclude_from_explosion, 'N') <> 'Y'
2245 UNION
2246 SELECT hrw.id
2247 FROM hxt_sum_hours_worked hrw
2248 WHERE hrw.tim_id = g_TIM_ID
2249 AND hrw.date_worked = g_DATE_WORKED
2250 AND hrw.time_in IS NOT NULL
2251 AND hrw.element_type_id is null
2252 );
2253 ELSE -- g_TIME_IN IS NOT NULL
2254 SELECT '1'
2255 INTO error_code
2256 FROM SYS.DUAL
2257 WHERE EXISTS (SELECT hrw.id
2258 FROM hxt_sum_hours_worked hrw, hxt_add_elem_info_f hei
2259 WHERE hrw.tim_id = g_TIM_ID
2260 AND hrw.date_worked = g_DATE_WORKED
2261 AND hrw.time_in IS NULL
2262 AND hrw.hours<>0
2263 AND hrw.element_type_id = hei.element_type_id
2264 AND hrw.element_type_id is not null
2265 AND g_date_worked BETWEEN hei.effective_start_date
2266 AND hei.effective_end_date
2267 AND NVL(hei.exclude_from_explosion, 'N') <> 'Y'
2268 UNION
2269 SELECT hrw.id
2270 FROM hxt_sum_hours_worked hrw
2271 WHERE hrw.tim_id = g_TIM_ID
2272 AND hrw.date_worked = g_DATE_WORKED
2273 AND hrw.time_in IS NULL
2274 AND hrw.hours<>0
2275 AND hrw.element_type_id is null
2276 );
2277 END IF;
2278 END IF;
2279
2280 /* Bug: 4489952 changes ends here */
2281
2282 EXCEPTION
2283 WHEN NO_DATA_FOUND THEN
2284 if g_debug then
2285 hr_utility.set_location('hxt_time_summary.valid_data',90);
2286 end if;
2287 NULL;
2288 END;
2289
2290 IF error_code = 1 THEN
2291 if g_debug then
2292 hr_utility.set_location('hxt_time_summary.valid_data',100);
2293 end if;
2294 FND_MESSAGE.SET_NAME('HXT','HXT_39329_INC_TIM_HR_ENTRIES');
2295
2296 END IF;
2297
2298 RETURN error_code;
2299
2300 EXCEPTION
2301
2302 WHEN OTHERS THEN
2303 if g_debug then
2304 hr_utility.set_location('hxt_time_summary.valid_data',110);
2305 end if;
2306 hr_utility.trace(dbms_utility.format_error_backtrace);
2307 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
2308 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
2309
2310 END; -- valid_data
2311 -------------------------------------------------------------------------------
2312 PROCEDURE Delete_Details(p_location IN VARCHAR2
2313 ,p_error_code IN OUT NOCOPY NUMBER) IS
2314 --Begins by deleting details for current summary record
2315 CURSOR completed_time_card IS
2316 SELECT 'Y'
2317 FROM hxt_det_hours_worked_f
2318 WHERE tim_id=g_tim_id
2319 AND pay_status = 'C';
2320
2321 l_completed_time_card VARCHAR2(1) := 'N';
2322 l_location hxt_errors.location%TYPE;
2323
2324 BEGIN
2325
2326 if g_debug then
2327 hr_utility.set_location('hxt_time_summary.Delete_Details',10);
2328 end if;
2329 p_error_code := 0;
2330
2331 --Add local code to location variable
2332 l_location := p_location||':DD';
2333
2334 IF nvl(g_DT_UPDATE_MODE, 'CORRECTION') = 'CORRECTION' THEN
2335 if g_debug then
2336 hr_utility.set_location('hxt_time_summary.Delete_Details',20);
2337 end if;
2338 DELETE from hxt_det_hours_worked_f
2339 WHERE parent_id = g_id;
2340
2341 DELETE from hxt_errors_f where --SPR C153
2342 hrw_id = g_id; --SPR C153
2343 ELSE
2344 if g_debug then
2345 hr_utility.set_location('hxt_time_summary.Delete_Details',30);
2346 end if;
2347 -- Delete details for this entry and all others that follow
2348 -- on this day for this person that are non-absence or are in
2349 -- the person's include group
2350
2351
2352 -- Bug 7359347
2353 -- Changed the reference to hxt_det_hours_worked to hxt_det_hours_worked_f
2354 /*
2355 UPDATE hxt_det_hours_worked_f
2356 SET effective_end_date = g_effective_start_date - 1
2357 WHERE rowid in (
2358 SELECT rowid
2359 FROM hxt_det_hours_worked
2360 WHERE parent_id = g_id);
2361 */
2362
2363 UPDATE hxt_det_hours_worked_f
2364 SET effective_end_date = g_effective_start_date - 1
2365 WHERE parent_id = g_id
2366 AND g_sum_session_date BETWEEN effective_start_date
2367 AND effective_end_date ;
2368
2369
2370 UPDATE hxt_errors_f
2371 SET effective_end_date = g_effective_start_date - 1
2372 WHERE rowid in (
2373 SELECT rowid
2374 FROM hxt_errors
2375 WHERE hrw_id = g_id);
2376
2377 END IF;--absence and not in include group or not
2378
2379 EXCEPTION
2380
2381 WHEN OTHERS THEN
2382 if g_debug then
2383 hr_utility.set_location('hxt_time_summary.Delete_Details',40);
2384 end if;
2385 hr_utility.trace(dbms_utility.format_error_backtrace);
2386 -- Write to error table - do not generate details
2387 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2388 p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
2389 --p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
2390 END; -- delete details
2391
2392 -------------------------------------------------------------------------------
2393 PROCEDURE shift_adjust_times(p_shift_adjusted_time_in OUT NOCOPY DATE
2394 ,p_shift_adjusted_time_out OUT NOCOPY DATE) IS
2395
2396 BEGIN
2397
2398 if g_debug then
2399 hr_utility.set_location('hxt_time_summary.shift_adjust_times',10);
2400 end if;
2401 p_shift_adjusted_time_in := g_TIME_IN;
2402 p_shift_adjusted_time_out := g_TIME_OUT;
2403
2404 IF (g_EARLY_START IS NOT NULL AND g_STANDARD_START IS NOT NULL) THEN
2405 if g_debug then
2406 hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
2407 end if;
2408 IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
2409 if g_debug then
2410 hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2411 end if;
2412 IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
2413 if g_debug then
2414 hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
2415 end if;
2416 p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2417 - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2418 ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
2419 if g_debug then
2420 hr_utility.set_location('hxt_time_summary.shift_adjust_times',50);
2421 end if;
2422 p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2423 + (hxt_util.time_to_hours(2400) -
2424 hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
2425 END IF;
2426 ELSE -- no midnight span
2427 if g_debug then
2428 hr_utility.set_location('hxt_time_summary.shift_adjust_times',60);
2429 end if;
2430 IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
2431 AND g_STANDARD_START THEN
2432 if g_debug then
2433 hr_utility.set_location('hxt_time_summary.shift_adjust_times',70);
2434 end if;
2435 p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2436 - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2437 END IF;
2438 END IF;
2439 END IF;
2440
2441 IF (g_LATE_STOP IS NOT NULL AND g_STANDARD_STOP IS NOT NULL) THEN
2442 if g_debug then
2443 hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
2444 end if;
2445 IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
2446 if g_debug then
2447 hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2448 end if;
2449 IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
2450 if g_debug then
2451 hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
2452 end if;
2453 p_shift_adjusted_time_out := g_TIME_OUT -
2454 (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2455 - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2456 ELSIF to_number(to_char(g_TIME_OUT, 'HH24MI')) < g_LATE_STOP THEN
2457 if g_debug then
2458 hr_utility.set_location('hxt_time_summary.shift_adjust_times',110);
2459 end if;
2460 p_shift_adjusted_time_out := g_TIME_OUT -
2461 (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2462 + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
2463 END IF;
2464 ELSE -- no midnight span
2465 if g_debug then
2466 hr_utility.set_location('hxt_time_summary.shift_adjust_times',120);
2467 end if;
2468 IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
2469 AND g_LATE_STOP THEN
2470 if g_debug then
2471 hr_utility.set_location('hxt_time_summary.shift_adjust_times',130);
2472 end if;
2473 p_shift_adjusted_time_out := g_TIME_OUT -
2474 (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2475 - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2476 END IF;
2477 END IF;
2478 END IF;
2479
2480 END; -- shift adjust times
2481 -------------------------------------------------------------------------------
2482 PROCEDURE Rebuild_Details(p_location IN VARCHAR2
2483 ,p_error_code IN OUT NOCOPY NUMBER) IS
2484
2485 --Rebuilds details for a person for this summary record.
2486
2487 --Define local variables
2488 l_error_code NUMBER := 0;
2489 l_location hxt_errors.location%TYPE;
2490 l_shift_adjusted_time_in DATE;
2491 l_shift_adjusted_time_out DATE;
2492 original_record_id NUMBER := g_ID;
2493 change_warning_flag NUMBER := 0;
2494 l_retcode NUMBER; -- BUG688072
2495 l_hdy_id NUMBER; -- BUG688072
2496 l_hours NUMBER; -- BUG688072
2497
2498 BEGIN
2499
2500 if g_debug then
2501 hr_utility.set_location('hxt_time_summary.Rebuild_Details',10);
2502 end if;
2503 --Add local code to location variable
2504 l_location := p_location||':RB';
2505
2506 --Currently, earning type is null in the summary record unless it is of type
2507 --'ABSENCE'.
2508
2509 --Check if absence and not in include group
2510 HXT_UTIL.Check_For_Holiday(g_date_worked
2511 ,g_hol_id
2512 ,l_hdy_id
2513 ,l_hours
2514 ,l_retcode);
2515 if g_debug then
2516 hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
2517 end if;
2518 IF l_retcode = 1 THEN
2519 if g_debug then
2520 hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
2521 end if;
2522 g_hol_yn := 'Y';
2523 ELSE
2524 if g_debug then
2525 hr_utility.set_location('hxt_time_summary.Rebuild_Details',40);
2526 end if;
2527 g_hol_yn := 'N';
2528 END IF;
2529
2530 l_error_code := Gen_Details(l_location, g_time_in, g_time_out);
2531
2532 IF l_error_code <> 0 THEN
2533 if g_debug then
2534 hr_utility.set_location('hxt_time_summary.Rebuild_Details',50);
2535 end if;
2536 p_error_code := l_error_code;
2537 ELSE
2538 if g_debug then
2539 hr_utility.set_location('hxt_time_summary.Rebuild_Details',60);
2540 end if;
2541 p_error_code := change_warning_flag;
2542 END IF;
2543
2544 EXCEPTION
2545
2546 WHEN OTHERS THEN
2547 if g_debug then
2548 hr_utility.set_location('hxt_time_summary.Rebuild_Details',70);
2549 end if;
2550 hr_utility.trace(dbms_utility.format_error_backtrace);
2551 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2552 p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
2553 --2278400 p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
2554 END; -- rebuild details
2555 -------------------------------------------------------------------------------
2556 FUNCTION gen_details
2557 (p_location IN VARCHAR2
2558 ,p_shift_adjusted_time_in IN DATE
2559 ,p_shift_adjusted_time_out IN DATE)
2560 RETURN NUMBER IS
2561
2562 -- g_variables are global variables set when package is entered and should not
2563 -- be changed
2564 -- p_variables are parameters
2565 -- all other variables are locals
2566
2567 location VARCHAR2(120) := p_location||':GDD';
2568 segment_start_time DATE; -- start time of segment - includes date
2569 segment_stop_time DATE; -- stop time of segment - includes date
2570 hours_worked NUMBER; -- # of hours in segment
2571 return_code NUMBER := 0;
2572 sd_rule_earning NUMBER; -- element type of sd rule found
2573 sd_rule_carryover NUMBER; -- carryover time of sd rule found
2574 sd_rule_start NUMBER; -- start of sd rule found
2575 loop_count NUMBER := 0;--count loop passes to break if necessary
2576
2577 /*CURSOR Get_shift_stop_time (p_assignment_id NUMBER
2578 ,p_date_worked DATE ) IS
2579 SELECT hs.standard_stop
2580 FROM hxt_shifts hs
2581 ,hxt_work_shifts hws
2582 ,hxt_per_aei_ddf_v aeiv
2583 ,hxt_rotation_schedules rts
2584 WHERE aeiv.assignment_id = p_ASSIGNMENT_ID
2585 AND p_DATE_WORKED between aeiv.effective_start_date
2586 and aeiv.effective_end_date
2587 AND rts.rtp_id = aeiv.hxt_rotation_plan
2588 AND rts.start_date = (SELECT MAX(start_date)
2589 FROM hxt_rotation_schedules
2590 WHERE rtp_id = rts.rtp_id
2591 AND start_date <= p_DATE_WORKED
2592 )
2593 AND hws.tws_id = rts.tws_id
2594 AND hws.week_day = to_char(p_DATE_WORKED,'DY')
2595 AND hws.sht_id = hs.id;
2596 */
2597 ln_standard_start hxt_shifts.standard_start%TYPE;
2598 ln_standard_stop hxt_shifts.standard_stop%TYPE;
2599 wp_start_time DATE;
2600 wp_stop_time DATE;
2601 ld_carryover2 DATE;
2602
2603 CURSOR Get_Work_plan IS
2604 SELECT hs.standard_start , hs.standard_stop
2605 FROM hxt_per_aei_ddf_v aeiv
2606 ,hxt_rotation_schedules rts
2607 ,hxt_work_shifts hws
2608 ,hxt_shifts hs
2609 WHERE aeiv.assignment_id = g_ASSIGNMENT_ID
2610 AND g_DATE_WORKED between aeiv.effective_start_date
2611 and aeiv.effective_end_date
2612 AND rts.rtp_id = aeiv.hxt_rotation_plan
2613 AND rts.start_date = (SELECT MAX(start_date)
2614 FROM hxt_rotation_schedules
2615 WHERE rtp_id = rts.rtp_id
2616 AND start_date <= g_DATE_WORKED
2617 )
2618 AND hws.tws_id = rts.tws_id
2619 AND hws.week_day = hxt_util.get_week_day(g_DATE_WORKED)
2620 AND hws.sht_id = hs.id;
2621
2622 CURSOR Get_sd_rules IS
2623 SELECT sdr.start_time
2624 ,sdr.stop_time
2625 -- ,sdr.element_type_id
2626 ,sdr.carryover_time
2627 FROM hxt_shift_diff_rules sdr
2628 WHERE sdr.sdp_id = g_sdp_id
2629 AND g_DATE_WORKED BETWEEN
2630 sdr.effective_start_date AND sdr.effective_end_date;
2631
2632 ln_sd_start hxt_shift_diff_rules.start_time%TYPE;
2633 ln_sd_stop hxt_shift_diff_rules.stop_time%TYPE;
2634 ln_sd_carryover hxt_shift_diff_rules.carryover_time%TYPE;
2635 sd_start_time DATE;
2636 sd_stop_time DATE;
2637 sd_carryover_time DATE;
2638 sd_date_worked DATE;
2639
2640 wplan_date_worked DATE;
2641
2642 segment_start number;
2643 segment_stop number;
2644 chunk_start date;
2645 chunk_stop date;
2646 chunk_start_date date;
2647 chunk_stop_date date;
2648 p_sdp_earning_type number;
2649 p_sdovr_earning_type number;
2650
2651 l_next_index BINARY_INTEGER := 0;
2652 chunk_count NUMBER := 1;
2653
2654 -----------------Gen Details local functions and procedures---------------------
2655
2656 ----------------- Populate PL/SQL Table ---------------------------------------
2657 PROCEDURE populate_plsql_table (p_value in date)
2658 IS
2659 lv_insert_flag VARCHAR2(1) := 'Y';
2660 ln_next_index NUMBER;
2661
2662 BEGIN
2663 if segment_chunks.count > 0 then
2664 for i in segment_chunks.first .. segment_chunks.last loop
2665 if p_value = segment_chunks(i) then
2666 lv_insert_flag := 'N';
2667 exit;
2668 end if;
2669 end loop;
2670 end if;
2671
2672 if lv_insert_flag = 'Y' then
2673 ln_next_index := segment_chunks.count + 1;
2674 segment_chunks(ln_next_index) := p_value;
2675 end if;
2676
2677 END populate_plsql_table;
2678
2679 /*
2680 PROCEDURE time_in_dates(ln_start in number
2681 ,ln_stop in number
2682 ,ln_carryover in number
2683 ,time_in out nocopy date
2684 ,time_out out nocopy date
2685 ,carryover_time out nocopy date
2686 ,l_date_worked in date)
2687 IS
2688
2689 BEGIN
2690 g_debug :=hr_utility.debug_enabled;
2691 if g_debug then
2692 hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693 hr_utility.trace('ln_start :'||ln_start);
2694 hr_utility.trace('ln_stop :'||ln_stop);
2695 hr_utility.trace('ln_carryover :'||ln_carryover);
2696 end if;
2697 time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
2698 ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
2699 if g_debug then
2700 hr_utility.trace('time_in :'
2701 || to_char(time_in,'DD-MON-YYYY HH24:MI:SS'));
2702 end if;
2703 IF ln_start < ln_stop OR
2704 (ln_start = 0 AND
2705 ln_stop = 0) THEN
2706 if g_debug then
2707 hr_utility.set_location('hxt_time_summary.time_in_dates',20);
2708 end if;
2709 time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
2710 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2711 if g_debug then
2712 hr_utility.trace('time_out :'
2713 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
2714 end if;
2715 ELSE --IF ln_start >= ln_stop
2716 if g_debug then
2717 hr_utility.set_location('hxt_time_summary.time_in_dates',30);
2718 end if;
2719 time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2720 ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2721 if g_debug then
2722 hr_utility.trace('time_out :'
2723 || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
2724 end if;
2725 END IF;
2726
2727 IF ln_carryover is NOT NULL THEN
2728 if g_debug then
2729 hr_utility.set_location('hxt_time_summary.time_in_dates',35);
2730 end if;
2731 IF ln_start < ln_carryover THEN
2732 if g_debug then
2733 hr_utility.set_location('hxt_time_summary.time_in_dates',40);
2734 end if;
2735 carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
2736 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2737
2738 if g_debug then
2739 hr_utility.trace('carryover_time :'
2740 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2741 end if;
2742 ELSE --IF ln_start >= ln_carryover
2743 if g_debug then
2744 hr_utility.set_location('hxt_time_summary.time_in_dates',50);
2745 end if;
2746 carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2747 ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2748
2749 if g_debug then
2750 hr_utility.trace('carryover_time :'
2751 || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2752 end if;
2753 END IF;
2754
2755 END IF;
2756
2757 END;
2758 */
2759
2760 FUNCTION sort (segment_chunks in t_date, p_order in Varchar2)
2761 RETURN t_date IS
2762
2763 sorted_chunks T_DATE;
2764 v_temp date;
2765
2766 BEGIN
2767
2768 if g_debug then
2769 hr_utility.set_location('hxt_time_summary.sort',10);
2770 end if;
2771 if segment_chunks.count > 0 then
2772 -- We do not need to copy each member of the array one by one.
2773 /*
2774 For i in segment_chunks.first .. segment_chunks.LAST
2775 Loop
2776 if g_debug then
2777 hr_utility.set_location('hxt_time_summary.sort',20);
2778 end if;
2779 sorted_chunks(i):= segment_chunks(i);
2780 End loop;
2781 */
2782 if g_debug then
2783 hr_utility.set_location('hxt_time_summary.sort',20);
2784 end if;
2785 sorted_chunks := segment_chunks;
2786 end if;
2787 /*
2788
2789 if g_debug then
2790 hr_utility.trace('FYI');
2791 end if;
2792 if sorted_chunks.count <> 0 then
2793 if g_debug then
2794 hr_utility.set_location('hxt_time_summary.sort',30);
2795 end if;
2796 for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2797 if g_debug then
2798 hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2799 end if;
2800 end loop;
2801 end if;
2802 if g_debug then
2803 hr_utility.trace('END FYI');
2804 end if;
2805
2806 */
2807
2808 if g_debug then
2809 hr_utility.trace('FYI');
2810 if sorted_chunks.count <> 0 then
2811 hr_utility.set_location('hxt_time_summary.sort',30);
2812 for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2813 hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2814 end loop;
2815 end if;
2816 hr_utility.trace('END FYI');
2817 end if;
2818
2819
2820
2821 For i in sorted_chunks.First+1 .. sorted_chunks.LAST
2822 Loop
2823 if g_debug then
2824 hr_utility.set_location('hxt_time_summary.sort',40);
2825 end if;
2826 v_temp:= sorted_chunks(i);
2827 if g_debug then
2828 hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
2829 end if;
2830 <<inner_loop>>
2831 if g_debug then
2832 hr_utility.set_location('hxt_time_summary.sort',50);
2833 end if;
2834 For j in REVERSE sorted_chunks.First .. (i-1)
2835 Loop
2836 if g_debug then
2837 hr_utility.set_location('hxt_time_summary.sort',60);
2838 hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
2839 end if;
2840 If sorted_chunks(j) >= v_temp then
2841 if g_debug then
2842 hr_utility.set_location('hxt_time_summary.sort',70);
2843 end if;
2844 sorted_chunks(j+1) := sorted_chunks(j);
2845 sorted_chunks(j) := v_temp;
2846 end if;
2847 if g_debug then
2848 hr_utility.set_location('hxt_time_summary.sort',80);
2849 end if;
2850 end loop inner_loop;
2851 if g_debug then
2852 hr_utility.set_location('hxt_time_summary.sort',90);
2853 end if;
2854 end loop;
2855 IF p_order ='ASC' then
2856 if g_debug then
2857 hr_utility.set_location('hxt_time_summary.sort',100);
2858 end if;
2859 RETURN sorted_chunks;
2860 END IF;
2861 if g_debug then
2862 hr_utility.set_location('hxt_time_summary.sort',110);
2863 end if;
2864 END;
2865
2866 FUNCTION segment_earning (segment_start in date
2867 ,segment_stop in date
2868 ,sdp_earning_type out nocopy number
2869 ,sdovr_earning_type out nocopy number
2870 )
2871 RETURN BOOLEAN IS
2872
2873 Cursor sd_earning is
2874 select sdr.start_time
2875 ,sdr.stop_time
2876 ,sdr.carryover_time
2877 ,sdr.element_type_id
2878 from hxt_shift_diff_rules sdr
2879 where sdr.sdp_id = g_sdp_id
2880 and g_date_worked between sdr.effective_start_date
2881 and sdr.effective_end_date;
2882
2883 sdp_start hxt_shift_diff_rules.start_time%TYPE;
2884 sdp_stop hxt_shift_diff_rules.stop_time%TYPE;
2885 sdp_carryover hxt_shift_diff_rules.carryover_time%TYPE;
2886 sdp_earning hxt_shift_diff_rules.element_type_id%TYPE;
2887 sdp_start_time DATE;
2888 sdp_stop_time DATE;
2889 sdp_carryover_time DATE;
2890
2891 worked_time_in DATE := p_shift_adjusted_time_in;
2892 worked_time_out DATE := p_shift_adjusted_time_out;
2893
2894 wp_start NUMBER;
2895 wp_stop NUMBER;
2896 ld_wp_start DATE;
2897 ld_wp_stop DATE;
2898 ld_carryover1 DATE;
2899
2900 lv_date_worked DATE;
2901 wp_date_worked DATE;
2902
2903 sdp_earning_found VARCHAR2(1) := 'N';
2904 elig_for_sdovr VARCHAR2(1) := 'N';
2905 elig_for_sdovr1 VARCHAR2(1) := 'N';
2906 elig_for_sdovr2 VARCHAR2(1) := 'N';
2907 elig_for_sdovr3 VARCHAR2(1) := 'N';
2908
2909 l_proc VARCHAR2(50) ;
2910
2911 ---------------------------segment_earning local functions---------------------
2912 FUNCTION check_eligibility(lv_time_in in date
2913 ,lv_time_out in date
2914 ,lv_segment_start in date
2915 ,lv_segment_stop in date)
2916 RETURN BOOLEAN is
2917
2918 --This function is used to check whether employee actually worked for
2919 --this chunk or not ,i.e., whether this chunk is between the time_in and
2920 --time_out.
2921 --The | in the comments shows where midnight falls.
2922 --This function is also used to check whether this chunk falls outside the
2923 --regular shift. If yes then pay override earning for this chunk.
2924
2925 BEGIN
2926
2927
2928 if g_debug then
2929
2930 hr_utility.set_location('hxt_time_summary.check_eligibility',10);
2931 end if;
2932
2933 IF (
2934 (lv_time_in <= lv_segment_start
2935 AND lv_segment_start <= lv_time_out
2936 AND lv_time_in <= lv_segment_stop
2937 AND lv_segment_stop <= lv_time_out)
2938 -- lv_time_in lv_segment_start lv_segment_stop lv_time_out |
2939 OR (lv_segment_start < lv_time_in
2940 AND lv_segment_start < lv_time_out
2941 AND lv_segment_stop < lv_time_in
2942 AND lv_segment_stop < lv_time_out
2943 AND lv_time_in > lv_time_out)
2944 -- lv_time_in | lv_segment_start lv_segment_stop lv_time_out
2945 OR (lv_time_in <= lv_segment_start
2946 AND lv_time_in < lv_segment_stop
2947 AND lv_time_in > lv_time_out )
2948 -- lv_time_in lv_segment_start lv_segment_stop | lv_time_out
2949 OR (lv_time_in <= lv_segment_start
2950 AND lv_segment_stop <= lv_time_out
2951 AND lv_segment_start > lv_segment_stop
2952 AND lv_time_in > lv_segment_stop
2953 AND lv_time_in > lv_time_out)
2954 -- lv_time_in lv_segment_start | lv_segment_stop lv_time_out
2955 )
2956 THEN
2957 if g_debug then
2958 hr_utility.set_location('hxt_time_summary.check_eligibility',20);
2959 end if;
2960 RETURN TRUE;
2961 ELSE
2962 if g_debug then
2963 hr_utility.set_location('hxt_time_summary.check_eligibility',30);
2964 end if;
2965 RETURN FALSE;
2966 END IF;
2967 if g_debug then
2968 hr_utility.set_location('hxt_time_summary.check_eligibility',40);
2969 end if;
2970 END;
2971
2972 ----------------------------segment_earning main module-------------------------
2973
2974 BEGIN
2975
2976 if g_debug then
2977 l_proc := 'hxt_time_summary.segment_earning';
2978 hr_utility.set_location(l_proc,10);
2979
2980 -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981 -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982 -- hr_utility.trace('segment_start :'||segment_start);
2983 -- hr_utility.trace('segment_stop :'||segment_stop);
2984
2985 hr_utility.trace('worked_time_in :'
2986 ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
2987 hr_utility.trace('worked_time_out :'
2988 ||to_char(worked_time_out,'DD-MON-YYYY HH24:MI:SS'));
2989 hr_utility.trace('segment_start :'
2990 ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
2991 hr_utility.trace('segment_stop :'
2992 ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
2993 end if;
2994 --First check whether employee worked for this chunk or not ,i.e., whether
2995 --this chunk is between the worked_time_in and worked_time_out.
2996 --The | in the comments shows where midnight falls.
2997
2998 IF check_eligibility(worked_time_in
2999 ,worked_time_out
3000 ,segment_start
3001 ,segment_stop) THEN
3002
3003 if g_debug then
3004 hr_utility.set_location(l_proc,20);
3005 end if;
3006 -- Check whether eligible for shift override
3007 if g_debug then
3008 hr_utility.set_location(l_proc,30);
3009 end if;
3010 open get_work_plan;
3011 fetch get_work_plan into wp_start,wp_stop;
3012 if g_debug then
3013 hr_utility.trace('wp_start :'||wp_start);
3014 hr_utility.trace('wp_stop :'||wp_stop);
3015 end if;
3016 close get_work_plan;
3017
3018 wp_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3019
3020 FOR i in 1 .. 3 LOOP
3021
3022 if g_debug then
3023 hr_utility.set_location(l_proc,40);
3024 hr_utility.trace('wp_date_worked :'
3025 || to_char(wp_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3026 end if;
3027 time_in_dates(wp_start
3028 ,wp_stop
3029 ,null
3030 ,ld_wp_start
3031 ,ld_wp_stop
3032 ,ld_carryover1
3033 ,wp_date_worked);
3034
3035 if g_debug then
3036 hr_utility.set_location(l_proc,50);
3037 hr_utility.trace('ld_wp_start :'
3038 || to_char(ld_wp_start,'DD-MON-YYYY HH24:MI:SS'));
3039 hr_utility.trace('ld_wp_stop :'
3040 || to_char(ld_wp_stop,'DD-MON-YYYY HH24:MI:SS'));
3041 hr_utility.trace('ld_carryover1:'
3042 || to_char(ld_carryover1,'DD-MON-YYYY HH24:MI:SS'));
3043 end if;
3044 IF g_sdovr_id is NOT NULL THEN
3045
3046 if g_debug then
3047 hr_utility.set_location(l_proc,60);
3048
3049 hr_utility.trace('segment_start :'
3050 ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
3051 hr_utility.trace('segment_stop :'
3052 ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
3053 end if;
3054 IF check_eligibility(ld_wp_start
3055 ,ld_wp_stop
3056 ,segment_start
3057 ,segment_stop) THEN
3058 if g_debug then
3059 hr_utility.set_location(l_proc,70);
3060 end if;
3061 -- the chunk falls within the regular shift.
3062 -- So , not eligible for shift override.
3063 -- sdovr_earning_type := null;
3064 elig_for_sdovr := 'N';
3065 if g_debug then
3066 hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
3067 end if;
3068 ELSE
3069 if g_debug then
3070 hr_utility.set_location(l_proc,80);
3071 end if;
3072 -- sdovr_earning_type := g_sdovr_id;
3073 --if g_debug then
3074 -- hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
3075 --end if;
3076 elig_for_sdovr := 'Y';
3077 END IF;
3078
3079 if g_debug then
3080 hr_utility.set_location(l_proc,90);
3081 end if;
3082
3083 -- Check if eligible before midnight
3084 IF i = 1 THEN
3085 if g_debug then
3086 hr_utility.set_location(l_proc,100);
3087 end if;
3088 IF elig_for_sdovr = 'Y' THEN
3089 if g_debug then
3090 hr_utility.set_location(l_proc,110);
3091 end if;
3092 elig_for_sdovr1 := 'Y';
3093 END IF;
3094 if g_debug then
3095 hr_utility.set_location(l_proc,120);
3096 end if;
3097 END IF;
3098
3099 -- Now Check if eligible on current day
3100 IF i = 2 THEN
3101 if g_debug then
3102 hr_utility.set_location(l_proc,130);
3103 end if;
3104 IF elig_for_sdovr = 'Y' THEN
3105 if g_debug then
3106 hr_utility.set_location(l_proc,140);
3107 end if;
3108 elig_for_sdovr2 := 'Y';
3109 END IF;
3110 if g_debug then
3111 hr_utility.set_location(l_proc,145);
3112 end if;
3113 END IF;
3114
3115 -- Now Check if eligible after midnight
3116 IF i = 3 THEN
3117 if g_debug then
3118 hr_utility.set_location(l_proc,150);
3119 end if;
3120 IF elig_for_sdovr = 'Y' THEN
3121 if g_debug then
3122 hr_utility.set_location(l_proc,160);
3123 end if;
3124 elig_for_sdovr3 := 'Y';
3125 END IF;
3126 if g_debug then
3127 hr_utility.set_location(l_proc,170);
3128 end if;
3129 END IF;
3130
3131 -- If eligible for both before,current day and after the midnight then
3132 -- pay the shift diff override
3133 IF elig_for_sdovr1= 'Y' and elig_for_sdovr2 = 'Y' and
3134 elig_for_sdovr3 = 'Y' THEN
3135 if g_debug then
3136 hr_utility.set_location(l_proc,180);
3137 end if;
3138 sdovr_earning_type := g_sdovr_id;
3139 ELSE
3140 if g_debug then
3141 hr_utility.set_location(l_proc,190);
3142 end if;
3143 sdovr_earning_type := null;
3144 END IF;
3145
3146 if g_debug then
3147 hr_utility.set_location(l_proc,200);
3148 end if;
3149 wp_date_worked := TRUNC(wp_date_worked + 1, 'DD');
3150
3151 END IF;
3152
3153 if g_debug then
3154 hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3155 hr_utility.set_location(l_proc,210);
3156 end if;
3157 END LOOP;
3158
3159 if g_debug then
3160 hr_utility.set_location(l_proc,215);
3161 hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3162 hr_utility.trace('sdp_earning_type :'|| sdp_earning_type);
3163 end if;
3164 -- BUG 2721493
3165 -- Now before proceeding to calculate the shift diff premium
3166 -- check that the employee is not eligible for any shift diff override.
3167 -- If eligible for a shift diff override then no need to evaluate the shift
3168 -- diff premiums since the shift diff Override overrides all the other
3169 -- premiums for this chunk.
3170
3171 IF sdovr_earning_type IS NULL THEN
3172
3173 if g_debug then
3174 hr_utility.set_location(l_proc,230);
3175 end if;
3176 -- Check whether eligible for shift diff premium
3177 open sd_earning;
3178
3179 LOOP
3180 if g_debug then
3181 hr_utility.set_location(l_proc,240);
3182 end if;
3183 fetch sd_earning into sdp_start,sdp_stop,sdp_carryover,sdp_earning;
3184
3185 Exit when sd_earning%NOTFOUND;
3186
3187 if g_debug then
3188 hr_utility.trace('sdp_start :'||sdp_start);
3189 hr_utility.trace('sdp_stop :'||sdp_stop);
3190 hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191 hr_utility.trace('sdp_earning:'||sdp_earning);
3192 hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193
3194 hr_utility.trace('p_shift_adjusted_time_in :'
3195 ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3196 hr_utility.trace('p_shift_adjusted_time_out:'
3197 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3198 end if;
3199 lv_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3200
3201 -- Loop through for the day before, the current day and the day after
3202 FOR i in 1 .. 3 LOOP
3203 if g_debug then
3204 hr_utility.set_location(l_proc,250);
3205 hr_utility.trace('lv_date_worked :'
3206 ||to_char(lv_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3207 end if;
3208 time_in_dates(sdp_start
3209 ,sdp_stop
3210 ,sdp_carryover
3211 ,sdp_start_time
3212 ,sdp_stop_time
3213 ,sdp_carryover_time
3214 ,lv_date_worked
3215 );
3216
3217 if g_debug then
3218 hr_utility.trace('sdp_start_time :'
3219 || to_char(sdp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3220 hr_utility.trace('sdp_stop_time :'
3221 || to_char(sdp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3222 hr_utility.trace('sdp_carryover_time :'
3223 || to_char(sdp_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3224 hr_utility.set_location(l_proc,260);
3225
3226 hr_utility.trace('g_sdf_rule_completed :'
3227 || g_sdf_rule_completed);
3228 hr_utility.trace('g_sdf_carryover :'
3229 || to_char(g_sdf_carryover,'DD-MON-YYYY HH24:MI:SS'));
3230 end if;
3231 IF g_sdf_rule_completed = 'Y' THEN
3232 if g_debug then
3233 hr_utility.set_location(l_proc,270);
3234 end if;
3235 if segment_start >= sdp_start_time and
3236 segment_start < sdp_stop_time then
3237 if g_debug then
3238 hr_utility.set_location(l_proc,280);
3239 end if;
3240 sdp_earning_type := sdp_earning;
3241 sdp_earning_found := 'Y';
3242
3243 if segment_stop = sdp_carryover_time then
3244 if g_debug then
3245 hr_utility.set_location(l_proc,290);
3246 end if;
3247 g_sdf_rule_completed := 'Y';
3248 g_sdf_carryover := null;
3249 if g_debug then
3250 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3251 end if;
3252 else
3253 if g_debug then
3254 hr_utility.set_location(l_proc,300);
3255 end if;
3256 g_sdf_rule_completed := 'N';
3257 g_sdf_carryover := sdp_carryover_time;
3258 if g_debug then
3259 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3260 hr_utility.set_location(l_proc,310);
3261 end if;
3262 end if;
3263
3264 if g_debug then
3265 hr_utility.set_location(l_proc,320);
3266 end if;
3267 exit;
3268 else
3269 if g_debug then
3270 hr_utility.set_location(l_proc,330);
3271 end if;
3272 sdp_earning_type := null;
3273 end if;
3274
3275 if g_debug then
3276 hr_utility.set_location(l_proc,340);
3277 end if;
3278 ELSIF g_sdf_rule_completed = 'N' THEN
3279
3280 if g_debug then
3281 hr_utility.set_location(l_proc,350);
3282 end if;
3283 if g_sdf_carryover = sdp_carryover_time then
3284
3285 if g_debug then
3286 hr_utility.set_location(l_proc,360);
3287 end if;
3288 sdp_earning_type := sdp_earning;
3289 sdp_earning_found := 'Y';
3290
3291 if segment_stop = sdp_carryover_time then
3292 if g_debug then
3293 hr_utility.set_location(l_proc,370);
3294 end if;
3295 g_sdf_rule_completed := 'Y';
3296 g_sdf_carryover := null;
3297 if g_debug then
3298 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3299 end if;
3300 else
3301 if g_debug then
3302 hr_utility.set_location(l_proc,380);
3303 end if;
3304 g_sdf_rule_completed := 'N';
3305 if g_debug then
3306 hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3307 end if;
3308 end if;
3309
3310 if g_debug then
3311 hr_utility.set_location(l_proc,390);
3312 end if;
3313 exit;
3314 else
3315 if g_debug then
3316 hr_utility.set_location(l_proc,400);
3317 end if;
3318 sdp_earning_type := null;
3319 end if;
3320
3321 if g_debug then
3322 hr_utility.set_location(l_proc,410);
3323 end if;
3324 END IF;
3325
3326 lv_date_worked := TRUNC(lv_date_worked + 1, 'DD');
3327
3328 if g_debug then
3329 hr_utility.set_location(l_proc,420);
3330 end if;
3331 END LOOP;
3332
3333 if g_debug then
3334 hr_utility.set_location(l_proc,430);
3335 end if;
3336 IF sdp_earning_found = 'Y' THEN
3337 if g_debug then
3338 hr_utility.set_location(l_proc,440);
3339 end if;
3340 EXIT;
3341 END IF;
3342 if g_debug then
3343 hr_utility.set_location(l_proc,450);
3344 end if;
3345 END LOOP;
3346
3347 if g_debug then
3348 hr_utility.set_location(l_proc,460);
3349 end if;
3350 close sd_earning;
3351
3352 ELSE
3353
3354 if g_debug then
3355 hr_utility.set_location(l_proc,465);
3356 end if;
3357 sdp_earning_type := null;
3358
3359 END IF;
3360
3361 if g_debug then
3362 hr_utility.set_location(l_proc,470);
3363 end if;
3364 RETURN TRUE;
3365
3366 ELSE
3367 if g_debug then
3368 hr_utility.set_location(l_proc,480);
3369 end if;
3370 RETURN FALSE;
3371 END IF; -- check_eligibility
3372
3373 if g_debug then
3374
3375 hr_utility.set_location(l_proc,490);
3376 end if;
3377 END; -- segment_earning
3378
3379 FUNCTION segment_start_in_rule
3380 (p_rule_earning_type OUT NOCOPY NUMBER
3381 ,p_carryover OUT NOCOPY NUMBER)
3382 RETURN BOOLEAN IS
3383
3384 -- Checks to see if the segment start time falls within a shift diff rule.
3385 -- If so, returns the earning type of the rule and the carryover time for
3386 -- calculating the end of the segment.Returns true if the start is within a
3387 -- rule, false if it is not.The | in the comments shows where midnight falls.
3388 -- The segment start and stop are both dates while sdr.start and stop are
3389 -- numbers.
3390
3391 CURSOR sd_rules IS
3392 SELECT sdr.element_type_id
3393 ,sdr.carryover_time
3394 FROM hxt_shift_diff_rules sdr
3395 WHERE sdr.sdp_id = g_sdp_id
3396 AND g_DATE_WORKED BETWEEN
3397 sdr.effective_start_date AND sdr.effective_end_date
3398 AND ( (sdr.start_time <= to_number(to_char(segment_start_time, 'HH24MI'))
3399 AND to_number(to_char(segment_start_time, 'HH24MI')) < sdr.stop_time)
3400 -- sdr.start segment.start sdr.stop |
3401 OR ((to_number(to_char(segment_start_time, 'HH24MI')) <= sdr.start_time)
3402 AND to_number(to_char(segment_start_time, 'HH24MI')) < sdr.stop_time
3403 AND sdr.start_time > sdr.stop_time)
3404 -- sdr.start | segment.start sdr.stop
3405 OR (sdr.start_time <= to_number(to_char(segment_start_time, 'HH24MI'))
3406 AND sdr.start_time > sdr.stop_time) );
3407 -- sdr.start segment.start | sdr.stop
3408
3409 BEGIN
3410
3411 if g_debug then
3412 hr_utility.set_location('hxt_time_summary.segment_start_in_rule',10);
3413 end if;
3414 OPEN sd_rules;
3415 FETCH sd_rules INTO p_rule_earning_type, p_carryover;
3416 if g_debug then
3417 hr_utility.trace('p_rule_earning_type :'||p_rule_earning_type);
3418 hr_utility.trace('p_carryover :'||p_carryover);
3419 end if;
3420 IF sd_rules%NOTFOUND THEN
3421 if g_debug then
3422 hr_utility.set_location('hxt_time_summary.segment_start_in_rule',20);
3423 end if;
3424 CLOSE sd_rules;
3425 RETURN FALSE;
3426 END IF;
3427 if g_debug then
3428 hr_utility.set_location('hxt_time_summary.segment_start_in_rule',30);
3429 end if;
3430 CLOSE sd_rules;
3431 RETURN TRUE;
3432
3433 END;
3434
3435 FUNCTION rule_start_in_segment( p_rule_start OUT NOCOPY NUMBER) RETURN BOOLEAN IS
3436
3437 -- Checks to see if a shift diff rule starts within the time segment being
3438 -- generated.This is only called if it is already determined that the start of
3439 -- the segment does not fall within any rule.
3440
3441 CURSOR sd_rules IS
3442 SELECT sdr.start_time
3443 FROM hxt_shift_diff_rules sdr
3444 WHERE sdr.sdp_id = g_sdp_id
3445 AND g_DATE_WORKED BETWEEN
3446 sdr.effective_start_date AND sdr.effective_end_date
3447 AND ( ( to_number(to_char(segment_start_time, 'HH24MI')) < sdr.start_time
3448 AND sdr.start_time < to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) )
3449 -- segment.start sdr.start segment.stop |
3450 OR (to_number(to_char(segment_start_time, 'HH24MI')) > sdr.start_time
3451 AND sdr.start_time < to_number(to_char(p_shift_adjusted_time_out, 'HH24MI'))
3452 AND to_number(to_char(segment_start_time, 'HH24MI')) >
3453 to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) )
3454 -- segment.start | sdr.start segment.stop
3455 OR ( to_number(to_char(segment_start_time, 'HH24MI')) < sdr.start_time
3456 AND to_number(to_char(segment_start_time, 'HH24MI')) >
3457 to_number(to_char(p_shift_adjusted_time_out, 'HH24MI')) ));
3458 -- segment.start sdr.start | segment.stop
3459
3460 BEGIN
3461
3462 if g_debug then
3463 hr_utility.set_location('hxt_time_summary.rule_start_in_segment',10);
3464 end if;
3465 OPEN sd_rules;
3466 FETCH sd_rules INTO p_rule_start;
3467 if g_debug then
3468 hr_utility.trace('p_rule_start :'||p_rule_start);
3469 end if;
3470 IF sd_rules%NOTFOUND THEN
3471 if g_debug then
3472 hr_utility.set_location('hxt_time_summary.rule_start_in_segment',20);
3473 end if;
3474 CLOSE sd_rules;
3475 RETURN FALSE;
3476 END IF;
3477 CLOSE sd_rules;
3478 RETURN TRUE;
3479
3480 END;
3481
3482 FUNCTION set_stop_and_hours
3483 (p_segment_stop_time IN OUT NOCOPY DATE
3484 ,p_hours_worked IN OUT NOCOPY NUMBER) RETURN NUMBER IS
3485
3486 -- Sets the stop time and hours worked of a segment if the start of the segment
3487 -- is within a shift diff rule.The stop time is the earliest of the input time
3488 -- out or the carryover time of the applicable rule.Segment start and stop
3489 -- times are dates which include the day.Carryover and rule start stop times
3490 -- are numbers.Diagrams showing relative times to midnight are S segment start,
3491 -- ST time out of input record, C carryover time,| midnight.
3492
3493 time_in_hours NUMBER := to_number(to_char(segment_start_time, 'HH24MI'));
3494 time_out_hours NUMBER := to_number(to_char(g_time_out, 'HH24MI'));
3495
3496 BEGIN
3497
3498 if g_debug then
3499 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',10);
3500 end if;
3501 p_segment_stop_time := NULL;
3502 if g_debug then
3503 hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3504 hr_utility.trace('time_out_hours :'||time_out_hours);
3505 hr_utility.trace('time_in_hours :'||time_in_hours);
3506 hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
3507 hr_utility.trace('segment_start_time :'||segment_start_time);
3508 end if;
3509 IF sd_rule_carryover >= time_out_hours THEN
3510 if g_debug then
3511 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
3512 end if;
3513 IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
3514 if g_debug then
3515 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
3516 end if;
3517 -- | S ST C S | ST C
3518 p_segment_stop_time := p_shift_adjusted_time_out;
3519 if g_debug then
3520 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3521 end if;
3522 ELSIF time_in_hours >= time_out_hours THEN
3523 if g_debug then
3524 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',40);
3525 end if;
3526 -- S C | ST
3527 p_segment_stop_time := segment_start_time +
3528 ((hxt_util.time_to_hours(sd_rule_carryover) -
3529 hxt_util.time_to_hours(time_in_hours)) / 24);
3530 if g_debug then
3531 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3532 end if;
3533 END IF;
3534 ELSE -- carryover < time_out_hours
3535 IF time_in_hours < sd_rule_carryover THEN
3536 if g_debug then
3537 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',50);
3538 end if;
3539 -- S C ST |
3540 p_segment_stop_time := segment_start_time +
3541 ((hxt_util.time_to_hours(sd_rule_carryover) -
3542 hxt_util.time_to_hours(time_in_hours)) / 24);
3543 if g_debug then
3544 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3545 end if;
3546
3547 ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours > time_out_hours)) THEN
3548 if g_debug then
3549 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',60);
3550 end if;
3551 -- S | C ST
3552 p_segment_stop_time := segment_start_time +
3553 ((hxt_util.time_to_hours(sd_rule_carryover) +
3554 (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3555 if g_debug then
3556 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3557 end if;
3558 ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours < time_out_hours)) THEN
3559 if g_debug then
3560 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',70);
3561 end if;
3562 -- S ST | C
3563 p_segment_stop_time := p_shift_adjusted_time_out;
3564 if g_debug then
3565 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3566 end if;
3567 ELSIF ((time_in_hours > sd_rule_carryover)
3568 AND (time_in_hours = time_out_hours)) THEN --SIR523
3569 -- S ST | C
3570 if g_debug then
3571 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',80);
3572 end if;
3573 p_segment_stop_time := p_shift_adjusted_time_out;
3574 if g_debug then
3575 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3576 end if;
3577 ELSIF time_in_hours = sd_rule_carryover THEN
3578 if g_debug then
3579 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
3580 end if;
3581 p_segment_stop_time := p_shift_adjusted_time_out;
3582 if g_debug then
3583 hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3584 end if;
3585 END IF;
3586 END IF;
3587
3588 p_hours_worked := ((p_segment_stop_time - segment_start_time) * 24);
3589 if g_debug then
3590 hr_utility.trace('p_hours_worked :'||p_hours_worked);
3591 end if;
3592
3593 IF p_segment_stop_time IS NULL THEN
3594 if g_debug then
3595 hr_utility.set_location('hxt_time_summary.set_stop_and_hours',100);
3596 end if;
3597 FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
3598 RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
3599 --2278400 RETURN call_gen_error(location, ''); -- HXT11
3600 END IF;
3601
3602 RETURN 0;
3603
3604 END;
3605
3606 FUNCTION set_stop_at_rule(p_segment_stop_time OUT NOCOPY DATE
3607 ,p_hours_worked OUT NOCOPY NUMBER) RETURN NUMBER IS
3608 -- Sets the segment stop time and hours worked when a segment starts with no
3609 -- shift diff rule but a rule starts before the segment would end.The stop
3610 -- time is the rule start time.
3611
3612 time_in_hours NUMBER := to_number(to_char(segment_start_time, 'HH24MI'));
3613 time_out_hours NUMBER := to_number(to_char(g_time_out, 'HH24MI'));
3614 l_stop_time DATE; -- local to use to set p_segment_stop_time
3615
3616 BEGIN
3617
3618 if g_debug then
3619 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',10);
3620 end if;
3621 l_stop_time := NULL;
3622
3623 if g_debug then
3624 hr_utility.trace('sd_rule_start :'||sd_rule_start);
3625 hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3626 end if;
3627 IF sd_rule_start > time_in_hours THEN
3628 if g_debug then
3629 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
3630 hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3631 end if;
3632 l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
3633 - hxt_util.time_to_hours(time_in_hours)) / 24);
3634 -- | Segment.start Rule.start or Segment.start Rule.start |
3635 if g_debug then
3636 hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3637 end if;
3638 ELSE
3639 if g_debug then
3640 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
3641 hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3642 end if;
3643 l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
3644 (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3645 -- Segment.start | Rule.start
3646 if g_debug then
3647 hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3648 end if;
3649 END IF;
3650
3651 if g_debug then
3652 hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3653 hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3654 end if;
3655 p_hours_worked := ((l_stop_time - segment_start_time) * 24);
3656 p_segment_stop_time := l_stop_time;
3657 if g_debug then
3658 hr_utility.trace('p_hours_worked :'||to_char(p_hours_worked,'DD-MON-YYYY HH24:MI:SS'));
3659 hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3660 end if;
3661 IF l_stop_time IS NULL THEN
3662 if g_debug then
3663 hr_utility.set_location('hxt_time_summary.set_stop_at_rule',40);
3664 end if;
3665 FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
3666 RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
3667 --2278400 RETURN call_gen_error(location, ''); -- HXT11
3668 END IF;
3669
3670 RETURN 0;
3671
3672 END;
3673 ----------------------------GEN DETAILS MAIN MODULE----------------------------
3674 BEGIN
3675 g_debug :=hr_utility.debug_enabled;
3676 if g_debug then
3677 hr_utility.set_location('hxt_time_summary.gen_details',10);
3678 end if;
3679 IF segment_chunks.count > 0 THEN
3680 if g_debug then
3681 hr_utility.set_location('hxt_time_summary.gen_details',11);
3682 end if;
3683 -- Bug 10123886
3684 -- No need to NULL out every member before we delete the array
3685 /*
3686
3687 FOR l in segment_chunks.first .. segment_chunks.last LOOP
3688 if g_debug then
3689 hr_utility.set_location('hxt_time_summary.gen_details',12);
3690 end if;
3691 segment_chunks(l) := null;
3692 END LOOP;
3693 */
3694 segment_chunks.delete;
3695 if g_debug then
3696 hr_utility.set_location('hxt_time_summary.gen_details',13);
3697 end if;
3698 END IF;
3699
3700 IF sorted_chunks.count > 0 THEN
3701 if g_debug then
3702 hr_utility.set_location('hxt_time_summary.gen_details',14);
3703 end if;
3704 -- Bug 10123886
3705 -- No need to NULL out every member before we delete the array
3706 /*
3707 FOR l in sorted_chunks.first .. sorted_chunks.last LOOP
3708 if g_debug then
3709 hr_utility.set_location('hxt_time_summary.gen_details',15);
3710 end if;
3711 sorted_chunks(l) := null;
3712 END LOOP;
3713 */
3714
3715 sorted_chunks.delete;
3716 if g_debug then
3717 hr_utility.set_location('hxt_time_summary.gen_details',16);
3718 end if;
3719 END IF;
3720
3721 -- Takes the incoming time in and out from the summary record and splits into
3722 -- segments corresponding to the rules of the applicable shift diff policy.
3723 -- Sends the segments to Gen Special for generating.The segment start and stop
3724 -- are dates which include the day for processing times that span midnight.
3725 -- Returns 0 for normal,2 for error.
3726
3727 -- IF g_sdp_id IS NULL OR g_TIME_IN IS NULL THEN
3728 -- If there is no shift diff policy or only hours entered
3729
3730 if g_debug then
3731 hr_utility.trace('g_sdp_id :'||g_sdp_id);
3732 hr_utility.trace('g_TIME_IN :'
3733 ||to_char(g_time_in,'DD-MON-YYYY HH24:MI:SS'));
3734 hr_utility.trace('g_element_type_id :'||g_element_type_id);
3735 end if;
3736 IF g_sdp_id IS NULL OR g_TIME_IN IS NULL
3737 OR (
3738 g_element_type_id IS NOT NULL
3739 AND g_CALL_ADJUST_ABS = 'Y'
3740 -- Bug 10123886
3741 AND hxt_time_detail.g_holiday_sdp_paid = FALSE ) THEN
3742 if g_debug then
3743 hr_utility.set_location('hxt_time_summary.gen_details',20);
3744 end if;
3745 -- If there is no shift diff policy or only hours entered or override hours
3746 -- type entered
3747 segment_start_time := p_shift_adjusted_time_in;
3748 segment_stop_time := p_shift_adjusted_time_out;
3749 hours_worked := g_hours;
3750 return_code := gen_special(location
3751 ,segment_start_time
3752 ,segment_stop_time
3753 ,hours_worked
3754 ,NULL
3755 ,NULL);
3756 ELSE
3757
3758 if g_debug then
3759 hr_utility.set_location('hxt_time_summary.gen_details',21);
3760 end if;
3761
3762 segment_start_time := p_shift_adjusted_time_in;
3763 segment_stop_time := p_shift_adjusted_time_out;
3764
3765 -- segment_start := to_number(to_char(p_shift_adjusted_time_in,'HH24MI'));
3766 -- segment_stop := to_number(to_char(p_shift_adjusted_time_out,'HH24MI'));
3767
3768 if g_debug then
3769 hr_utility.trace('p_shift_adjusted_time_in :'
3770 ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3771 hr_utility.trace('p_shift_adjusted_time_out:'
3772 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3773
3774 hr_utility.trace('segment_start_time :'
3775 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3776 hr_utility.trace('segment_stop_time :'
3777 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3778
3779 hr_utility.set_location('hxt_time_summary.gen_details',22);
3780 end if;
3781 -- Insert these rows for hours worked start stop into the
3782 -- pl sql table segment_chunks.
3783 -- These start and stop times are entered into a single column
3784 -- and then sorted by the function sort.
3785
3786 populate_plsql_table(segment_start_time);
3787 populate_plsql_table(segment_stop_time);
3788
3789 -- Now insert the rows for work plan start stop
3790 -- into the pl sql table segment_chunks for day before , day worked and day
3791 -- after.
3792 open get_work_plan;
3793 fetch get_work_plan into ln_standard_start ,ln_standard_stop;
3794 if g_debug then
3795 hr_utility.trace('ln_standard_start :'|| ln_standard_start);
3796 hr_utility.trace('ln_standard_stop :'|| ln_standard_stop);
3797 end if;
3798 close get_work_plan;
3799
3800 wplan_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3801 FOR i in 1 .. 3 LOOP
3802 if g_debug then
3803 hr_utility.trace('wplan_date_worked :'
3804 || to_char(wplan_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3805 end if;
3806 time_in_dates(ln_standard_start
3807 ,ln_standard_stop
3808 ,null
3809 ,wp_start_time
3810 ,wp_stop_time
3811 ,ld_carryover2
3812 ,wplan_date_worked);
3813
3814 if g_debug then
3815 hr_utility.trace('wp_start_time :'
3816 || to_char(wp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3817 hr_utility.trace('wp_stop_time :'
3818 || to_char(wp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3819 hr_utility.trace('ld_carryover2 :'
3820 || to_char(ld_carryover2,'DD-MON-YYYY HH24:MI:SS'));
3821 end if;
3822 populate_plsql_table(wp_start_time);
3823 populate_plsql_table(wp_stop_time);
3824
3825 if g_debug then
3826 hr_utility.set_location('hxt_time_summary.gen_details',23);
3827 end if;
3828 wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
3829 END LOOP;
3830
3831 if g_debug then
3832 hr_utility.set_location('hxt_time_summary.gen_details',23.4);
3833 end if;
3834 open get_sd_rules;
3835 LOOP
3836 -- fetch get_sd_rules into lv_sd_start,lv_sd_stop;
3837 fetch get_sd_rules into ln_sd_start,ln_sd_stop,ln_sd_carryover;
3838 Exit when get_sd_rules%NOTFOUND;
3839
3840 if g_debug then
3841 hr_utility.trace('ln_sd_start :'||ln_sd_start);
3842 hr_utility.trace('ln_sd_stop :'||ln_sd_stop);
3843 hr_utility.trace('ln_sd_carryover :'||ln_sd_carryover);
3844 end if;
3845 -- Populate the plsql table with the shift policy chunks appended
3846 -- with the dates for day before , current day and day after
3847 sd_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3848
3849 FOR i in 1 .. 3 LOOP
3850 if g_debug then
3851 hr_utility.set_location('hxt_time_summary.gen_details',23.5);
3852 hr_utility.trace('sd_date_worked :'
3853 || to_char(sd_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3854 end if;
3855 time_in_dates(ln_sd_start
3856 ,ln_sd_stop
3857 ,ln_sd_carryover
3858 ,sd_start_time
3859 ,sd_stop_time
3860 ,sd_carryover_time
3861 ,sd_date_worked);
3862
3863 if g_debug then
3864 hr_utility.trace('sd_start_time :'
3865 || to_char(sd_start_time,'DD-MON-YYYY HH24:MI:SS'));
3866 hr_utility.trace('sd_carryover_time :'
3867 || to_char(sd_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3868 end if;
3869 populate_plsql_table(sd_start_time);
3870 populate_plsql_table(sd_carryover_time);
3871
3872 if g_debug then
3873 hr_utility.set_location('hxt_time_summary.gen_details',23.6);
3874 end if;
3875 sd_date_worked := TRUNC(sd_date_worked + 1, 'DD');
3876
3877 END LOOP;
3878
3879 if g_debug then
3880 hr_utility.set_location('hxt_time_summary.gen_details',23.8);
3881 end if;
3882 END LOOP;
3883 close get_sd_rules;
3884
3885 if g_debug then
3886 hr_utility.set_location('hxt_time_summary.gen_details',24);
3887
3888 hr_utility.trace('FYI');
3889 end if;
3890 if segment_chunks.count <> 0 then
3891 if g_debug then
3892 hr_utility.set_location('hxt_time_summary.gen_details',26);
3893 end if;
3894 for l_cnt in segment_chunks.first .. segment_chunks.last loop
3895 if g_debug then
3896 hr_utility.trace('segment_chunks is:'
3897 ||to_char(segment_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
3898 end if;
3899 end loop;
3900 end if;
3901 if g_debug then
3902 hr_utility.trace('END FYI');
3903 hr_utility.set_location('hxt_time_summary.gen_details',27);
3904 end if;
3905 -- Get the sorted pl sql table
3906 sorted_chunks := sort( segment_chunks , 'ASC');
3907
3908 if g_debug then
3909 hr_utility.trace('FYI');
3910 end if;
3911 if sorted_chunks.count <> 0 then
3912 if g_debug then
3913 hr_utility.set_location('hxt_time_summary.gen_details',28);
3914 end if;
3915 for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
3916 if g_debug then
3917 hr_utility.trace('sorted_chunks is:'
3918 ||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
3919 end if;
3920 end loop;
3921 if g_debug then
3922 hr_utility.set_location('hxt_time_summary.gen_details',29);
3923 end if;
3924 end if;
3925 if g_debug then
3926 hr_utility.trace('END FYI');
3927 end if;
3928
3929
3930 if sorted_chunks.count > 0 then
3931 if g_debug then
3932 hr_utility.set_location('hxt_time_summary.gen_details',30);
3933 end if;
3934 for i in sorted_chunks.first .. sorted_chunks.last-1 loop
3935 if g_debug then
3936 hr_utility.set_location('hxt_time_summary.gen_details',31);
3937 end if;
3938 chunk_start := sorted_chunks(i);
3939
3940 if g_debug then
3941 hr_utility.trace('chunk_start:'
3942 ||to_char(chunk_start,'DD-MON-YYYY HH24:MI:SS'));
3943 end if;
3944 /* if i = sorted_chunks.last then
3945 chunk_stop := sorted_chunks(sorted_chunks.first);
3946 if g_debug then
3947 hr_utility.trace('chunk_stop :'
3948 ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
3949 end if;
3950 else
3951 */
3952 chunk_stop := sorted_chunks(i+1);
3953 if g_debug then
3954 hr_utility.trace('chunk_stop :'
3955 ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
3956 end if;
3957 -- end if;
3958 if g_debug then
3959 hr_utility.set_location('hxt_time_summary.gen_details',32);
3960 end if;
3961 IF segment_earning(chunk_start
3962 ,chunk_stop
3963 ,p_sdp_earning_type
3964 ,p_sdovr_earning_type
3965 ) THEN
3966 if g_debug then
3967 hr_utility.set_location('hxt_time_summary.gen_details',33);
3968 end if;
3969 -- hours_worked:= ((chunk_stop - chunk_start) * 24);
3970 hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
3971
3972 if g_debug then
3973 hr_utility.trace('hours_worked :'|| hours_worked);
3974 hr_utility.set_location('hxt_time_summary.gen_details',34);
3975 end if;
3976 return_code := gen_special(location
3977 ,chunk_start
3978 ,chunk_stop
3979 ,hours_worked
3980 ,p_sdp_earning_type
3981 ,p_sdovr_earning_type);
3982 if g_debug then
3983 hr_utility.set_location('hxt_time_summary.gen_details',35);
3984 end if;
3985 END IF; -- segment_earning
3986
3987 end loop;
3988
3989 if g_debug then
3990 hr_utility.set_location('hxt_time_summary.gen_details',36);
3991 end if;
3992
3993 end if;
3994
3995
3996
3997 /*
3998
3999 if g_debug then
4000 hr_utility.set_location('hxt_time_summary.gen_details',30);
4001 end if;
4002 segment_stop_time := p_shift_adjusted_time_in;
4003 if g_debug then
4004 hr_utility.trace('p_shift_adjusted_time_in :'
4005 ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
4006 hr_utility.trace('p_shift_adjusted_time_out:'
4007 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4008 hr_utility.trace('return_code :'
4009 ||return_code);
4010 hr_utility.trace('segment_stop_time :'
4011 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4012 end if;
4013 WHILE (segment_stop_time < p_shift_adjusted_time_out) AND return_code = 0
4014 LOOP
4015 if g_debug then
4016 hr_utility.set_location('hxt_time_summary.gen_details',40);
4017 end if;
4018 --while the end of the last segment is not end of time
4019 segment_start_time := segment_stop_time; -- start each segment with
4020 -- stop time of last
4021 if g_debug then
4022 hr_utility.trace('segment_start_time :'
4023 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4024 end if;
4025 IF segment_start_in_rule(sd_rule_earning,sd_rule_carryover) THEN
4026 if g_debug then
4027 hr_utility.set_location('hxt_time_summary.gen_details',50);
4028 end if;
4029 -- if the start is in a rule
4030 if g_debug then
4031 hr_utility.trace('segment_stop_time :'
4032 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4033 hr_utility.trace('hours_worked :'||hours_worked);
4034 end if;
4035 return_code := set_stop_and_hours(segment_stop_time,hours_worked);
4036 if g_debug then
4037 hr_utility.trace('segment_stop_time :'
4038 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4039 hr_utility.trace('hours_worked :'||hours_worked);
4040 hr_utility.trace('return_code :'||return_code);
4041 end if;
4042 ELSIF rule_start_in_segment(sd_rule_start) THEN
4043 if g_debug then
4044 hr_utility.set_location('hxt_time_summary.gen_details',60);
4045 end if;
4046 -- if a rule starts in the segment
4047 return_code := set_stop_at_rule(segment_stop_time,hours_worked);
4048 if g_debug then
4049 hr_utility.trace('segment_stop_time :'
4050 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4051 hr_utility.trace('hours_worked :'||hours_worked);
4052 hr_utility.trace('return_code :'||return_code);
4053 end if;
4054 sd_rule_earning := NULL;
4055
4056
4057 -- CHECK IF THERE IS A SHIFT DIFFERENTIAL OVERRIDE APPLICABLE TO THIS DAY
4058 ELSIF g_sdovr_id is NOT NULL THEN
4059
4060 open Get_Shift_stop_time( g_ASSIGNMENT_ID,g_DATE_WORKED );
4061 fetch Get_Shift_stop_time into lv_standard_stop;
4062 if g_debug then
4063 hr_utility.trace('lv_standard_stop :'||lv_standard_stop);
4064 end if;
4065 close Get_Shift_stop_time;
4066
4067 -- IF working from 0800 to 2300 then pay only regular hours
4068 -- from 1200 to 1700 (shift diff defined from 0800 to 1200) and
4069 -- shift diff Override from 1700 to 2300
4070 IF to_number(to_char(segment_stop_time,'HH24MI')) < lv_standard_stop THEN
4071 if g_debug then
4072 hr_utility.set_location('hxt_time_summary.gen_details',75);
4073 end if;
4074 -- segment_stop_time := lv_standard_stop;
4075 segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
4076 if g_debug then
4077 hr_utility.trace('segment_stop_time :'
4078 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4079 hr_utility.trace('segment_start_time :'
4080 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4081 end if;
4082 hours_worked:=((segment_stop_time - segment_start_time) * 24);
4083 if g_debug then
4084 hr_utility.trace('hours_worked :'||hours_worked);
4085 end if;
4086 sd_rule_earning := NULL;
4087 ELSE -- segment_stop_time >= lv_standard_stop
4088 if g_debug then
4089 hr_utility.set_location('hxt_time_summary.gen_details',76);
4090 end if;
4091 segment_stop_time := p_shift_adjusted_time_out;
4092 if g_debug then
4093 hr_utility.trace('segment_stop_time :'
4094 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4095 hr_utility.trace('segment_start_time :'
4096 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4097 end if;
4098 hours_worked:=((segment_stop_time - segment_start_time) * 24);
4099 if g_debug then
4100 hr_utility.trace('hours_worked :'||hours_worked);
4101 end if;
4102 -- IF the shift differential override is applicable to the time_in and
4103 -- time_outs ,then set p_sdf_id to g_sdovr_id so that the cursor
4104 -- cur_elig_prem in hxt_time_pay.gen_premiums returns a row and the
4105 -- logic gets called to insert the data into hxt_det_hours_worked
4106 sd_rule_earning := g_sdovr_id;
4107 END IF;
4108 if g_debug then
4109 hr_utility.set_location('hxt_time_summary.gen_details',77);
4110 end if;
4111 -- END g_sdovr_id is NOT NULL
4112
4113 ELSE
4114 -- NO SHIFT DIFF RULES APPLY
4115 if g_debug then
4116 hr_utility.trace('segment_start_time :'
4117 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4118 hr_utility.trace('segment_stop_time :'
4119 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4120 hr_utility.trace('p_shift_adjusted_time_out :'
4121 ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4122 hr_utility.set_location('hxt_time_summary.gen_details',78);
4123 end if;
4124 segment_stop_time := p_shift_adjusted_time_out;
4125 if g_debug then
4126 hr_utility.trace('segment_stop_time :'
4127 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4128 end if;
4129 hours_worked :=((segment_stop_time - segment_start_time) * 24);
4130 if g_debug then
4131 hr_utility.trace('segment_stop_time :'
4132 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4133 hr_utility.trace('hours_worked :'||hours_worked);
4134 end if;
4135 sd_rule_earning := NULL;
4136
4137 END IF;
4138
4139 if g_debug then
4140 hr_utility.trace('location :'||location);
4141 hr_utility.trace('segment_start_time :'
4142 ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4143 hr_utility.trace('segment_stop_time :'
4144 ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4145 hr_utility.trace('sd_rule_earning :'||sd_rule_earning);
4146 hr_utility.trace('return_code :'||return_code);
4147 end if;
4148 IF return_code = 0 THEN
4149 if g_debug then
4150 hr_utility.set_location('hxt_time_summary.gen_details',80);
4151 end if;
4152 loop_count := loop_count + 1;
4153 if g_debug then
4154 hr_utility.trace('loop_count :'||loop_count);
4155 end if;
4156 return_code := gen_special(location
4157 ,segment_start_time
4158 ,segment_stop_time
4159 ,hours_worked
4160 ,sd_rule_earning);
4161 END IF;
4162 IF loop_count > 50 THEN
4163 if g_debug then
4164 hr_utility.set_location('hxt_time_summary.gen_details',90);
4165 end if;
4166 FND_MESSAGE.SET_NAME('HXT','HXT_39300_GEN_DTAIL_EXC_LOOP'); -- HXT11
4167 return_code := call_gen_error(location, ''); -- HXT11
4168 END IF;
4169 IF return_code > 0 THEN
4170 if g_debug then
4171 hr_utility.set_location('hxt_time_summary.gen_details',100);
4172 end if;
4173 EXIT;
4174 END IF;
4175 END LOOP;
4176 */
4177
4178
4179 if g_debug then
4180 hr_utility.set_location('hxt_time_summary.gen_details',40);
4181 end if;
4182 END IF; -- there is a shift diff policy
4183
4184 if g_debug then
4185 hr_utility.set_location('hxt_time_summary.gen_details',45);
4186 end if;
4187 RETURN return_code;
4188
4189 EXCEPTION
4190
4191 WHEN OTHERS THEN
4192 if g_debug then
4193 hr_utility.set_location('hxt_time_summary.gen_details',110);
4194 end if;
4195 hr_utility.trace(dbms_utility.format_error_backtrace);
4196 FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
4197 RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm); -- HXT11
4198 --2278400 RETURN call_gen_error(location, '', sqlerrm); -- HXT11
4199
4200 END;
4201
4202 FUNCTION Get_Include(p_location IN VARCHAR2
4203 ,p_egt_id IN NUMBER
4204 ,p_element_type_id IN NUMBER
4205 ,p_date IN DATE)
4206 RETURN VARCHAR2 IS
4207
4208 -- Returns 1 if element_type passed found in earning group passed,
4209 -- 0 if not in earning group,
4210 -- SQLCODE if Oracle error occurred.
4211
4212 -- Modification Log:
4213 -- 01/19/96 PJA Created.
4214 -- 02/01/96 AVS Modified cursor and error handling.
4215
4216 l_retcode VARCHAR2(1) DEFAULT 'N';
4217 l_error_code NUMBER;
4218 l_location VARCHAR2(120) := p_location||':GI';
4219
4220 BEGIN
4221
4222 if g_debug then
4223 hr_utility.set_location('hxt_time_summary.Get_Include',10);
4224 end if;
4225 -- Check if no earn group passed
4226 IF p_egt_id is null THEN
4227 if g_debug then
4228 hr_utility.set_location('hxt_time_summary.Get_Include',20);
4229 end if;
4230 RETURN l_retcode;
4231 ELSE
4232 BEGIN
4233 if g_debug then
4234 hr_utility.set_location('hxt_time_summary.Get_Include',30);
4235 end if;
4236 -- Check if element_type exists in earning group
4237 SELECT 'Y'
4238 INTO l_retcode
4239 FROM hxt_earn_group_types egt
4240 WHERE egt.FCL_EG_TYPE = 'INCLUDE'
4241 AND p_date between egt.effective_start_date
4242 and egt.effective_end_date
4243 AND egt.id = p_egt_id
4244 AND exists (SELECT 'x'
4245 FROM hxt_earn_groups egr
4246 WHERE egr.egt_id = p_egt_id -- SPR C150
4247 AND egr.element_type_id = p_element_type_id
4248 );
4249 RETURN l_retcode;
4250
4251 EXCEPTION
4252 WHEN no_data_found THEN
4253 if g_debug then
4254 hr_utility.set_location('hxt_time_summary.Get_Include',40);
4255 end if;
4256 RETURN l_retcode;
4257 END;
4258 END IF;
4259
4260 EXCEPTION
4261 -- Return Oracle error number
4262 WHEN others THEN
4263 if g_debug then
4264 hr_utility.set_location('hxt_time_summary.Get_Include',50);
4265 end if;
4266 hr_utility.trace(dbms_utility.format_error_backtrace);
4267 FND_MESSAGE.SET_NAME('HXT','HXT_39270_OR_ERR_G_GROUP'); -- HXT11
4268 l_error_code := call_hxthxc_gen_error('HXT','HXT_39270_OR_ERR_G_GROUP',l_location,'', sqlerrm); -- HXT11
4269 --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm); -- HXT11
4270 RETURN 'E';
4271
4272 END; -- get include
4273 -------------------------------------------------------------------------------
4274 FUNCTION GEN_SPECIAL (p_location IN VARCHAR2
4275 ,p_time_in IN DATE
4276 ,p_time_out IN DATE
4277 ,p_hours_worked IN NUMBER
4278 ,p_shift_diff_earning_id IN NUMBER
4279 ,p_sdovr_earning_id IN NUMBER)
4280 RETURN NUMBER IS
4281
4282
4283 -- Bug 9218428
4284 -- Added the following cursors to pick up the daily cap
4285 -- and the total number of hours worked on the same day
4286 -- entered in a previous summary.
4287
4288 -- Bug 12830622
4289 -- Added p_date_worked parameter to pick up
4290 -- details only for the same day.
4291 CURSOR get_other_hours(p_session_date IN DATE,
4292 p_tim_id IN NUMBER,
4293 p_id IN NUMBER,
4294 p_date_worked IN DATE)
4295 IS SELECT sum(hours)
4296 FROM hxt_sum_hours_worked_f sum
4297 WHERE p_session_date BETWEEN sum.effective_start_date
4298 AND sum.effective_end_date
4299 AND date_worked = p_date_worked
4300 AND tim_id = p_tim_id
4301 AND id <> p_id;
4302
4303 l_total_hours NUMBER := 0;
4304
4305
4306 l_max_hours NUMBER := 0;
4307
4308 CURSOR get_max_hours(p_ep_id IN NUMBER)
4309 IS SELECT max(hours)
4310 FROM hxt_earning_policies ep,
4311 hxt_earning_rules er
4312 WHERE ep.id = p_ep_id
4313 AND g_sum_session_date BETWEEN ep.effective_start_date
4314 AND ep.effective_end_date
4315 AND ep.fcl_earn_type = 'SPECIAL'
4316 AND ep.id = er.egp_id
4317 AND g_sum_session_date BETWEEN er.effective_start_date
4318 AND er.effective_end_date
4319 AND er.egr_type = 'DAY'
4320 AND ep.use_points_assigned = 'Y';
4321
4322
4323 BEGIN
4324
4325 if g_debug then
4326 hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327 hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328 hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329 hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330 hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4331 hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
4332 end if;
4333
4334 -- Bug 9218428
4335 -- The below code checks for the Max number of hours which can be entered on
4336 -- a day and compares it against the total number of hours to be paid in this
4337 -- summary.
4338 -- This is done only if the Earn Policy uses Points. If it is so, compare
4339 -- and throw this error if the total number of hours to be paid on a day is
4340 -- more than the max daily rule cap.
4341
4342 -- Bug 13408603
4343 -- Modified all references of g_daily_cap below with TO_CHAR conversion.
4344 IF NOT g_daily_cap.EXISTS(TO_CHAR(g_ep_id))
4345 THEN
4346
4347 OPEN get_max_hours(g_ep_id);
4348 FETCH get_max_hours INTO l_max_hours;
4349 CLOSE get_max_hours;
4350
4351 IF g_debug
4352 THEN
4353 hr_utility.trace('Daily Cap is '||l_max_hours);
4354 END IF;
4355
4356 IF l_max_hours IS NULL
4357 OR l_max_hours = 0
4358 THEN
4359 g_daily_cap(TO_CHAR(g_ep_id)) := -99;
4360 ELSE
4361 g_daily_cap(TO_CHAR(g_ep_id)) := l_max_hours;
4362 END IF;
4363 END IF;
4364
4365 IF g_daily_cap(TO_CHAR(g_ep_id)) <> -99
4366 THEN
4367 -- Bug 12830622
4368 -- Passing the date also the the cursor.
4369 OPEN get_other_hours(g_sum_session_date,
4370 g_tim_id,
4371 g_id,
4372 g_date_worked);
4373
4374 FETCH get_other_hours INTO l_total_hours;
4375 CLOSE get_other_hours;
4376
4377 IF g_debug
4378 THEN
4379 hr_utility.trace('Total Hours is '||l_total_hours);
4380 END IF;
4381
4382 l_total_hours := NVL(l_total_hours,0) + g_hours;
4383
4384
4385 IF l_total_hours > g_daily_cap(TO_CHAR(g_ep_id))
4386 THEN
4387 fnd_message.set_name ('HXT', 'HXT_39577_NO_HOURS_ADJUST');
4388 RETURN call_hxthxc_gen_error ('HXT',
4389 'HXT_39577_NO_HOURS_ADJUST',
4390 NULL,
4391 'hxt_time_sum.gen_special',
4392 '',
4393 NULL
4394 );
4395 END IF;
4396
4397 END IF;
4398
4399
4400
4401
4402 RETURN hxt_time_detail.generate_special
4403 ( g_ep_id
4404 ,g_ep_type
4405 ,g_egt_id
4406 ,p_shift_diff_earning_id
4407 ,g_hdp_id
4408 ,g_hol_id
4409 ,g_sdp_id -- ORACLE bug #715964
4410 ,g_pep_id
4411 ,g_pip_id
4412 ,p_sdovr_earning_id
4413 ,g_osp_id
4414 ,g_hol_yn
4415 ,g_person_id
4416 ,p_location
4417 ,g_ID
4418 ,g_TIM_ID
4419 ,g_DATE_WORKED
4420 ,g_ASSIGNMENT_ID
4421 ,p_hours_worked
4422 ,p_time_in
4423 ,p_time_out
4424 ,g_ELEMENT_TYPE_ID
4425 ,g_FCL_EARN_REASON_CODE
4426 ,g_FFV_COST_CENTER_ID
4427 ,g_FFV_LABOR_ACCOUNT_ID
4428 ,g_TAS_ID
4429 ,g_LOCATION_ID
4430 ,g_SHT_ID
4431 ,g_HRW_COMMENT
4432 ,g_FFV_RATE_CODE_ID
4433 ,g_RATE_MULTIPLE
4434 ,g_HOURLY_RATE
4435 ,g_AMOUNT
4436 ,g_FCL_TAX_RULE_CODE
4437 ,g_SEPARATE_CHECK_FLAG
4438 ,g_SEQNO
4439 ,g_CREATED_BY
4440 ,g_CREATION_DATE
4441 ,g_LAST_UPDATED_BY
4442 ,g_LAST_UPDATE_DATE
4443 ,g_LAST_UPDATE_LOGIN
4444 ,g_start_day_of_week
4445 ,g_EFFECTIVE_START_DATE
4446 ,g_EFFECTIVE_END_DATE
4447 ,g_PROJECT_ID
4448 ,g_JOB_ID
4449 ,g_PAY_STATUS
4450 ,g_PA_STATUS
4451 ,g_RETRO_BATCH_ID
4452 ,g_PERIOD_START_DATE
4453 ,g_CALL_ADJUST_ABS
4454 ,g_STATE_NAME
4455 ,g_COUNTY_NAME
4456 ,g_CITY_NAME
4457 ,g_ZIP_CODE
4458 --,g_GROUP_ID
4459 );
4460
4461 if g_debug then
4462 hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',20);
4463 end if;
4464 END; -- gen special
4465
4466 -- begin
4467
4468
4469 END; -- package
[Home] [Help]