[Home] [Help]
172:
173: SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
174: (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
175: INTO p_duration
176: FROM DUAL;
177: -- hr_utility.trace('p_start_duration ' || p_start_duration);
178: hr_utility.trace('Start p_duration ' || p_duration);
179:
180: --
183: hr_utility.trace('l_end_time ' || l_end_time);
184: SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
185: (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
186: INTO p_duration
187: FROM DUAL;
188: --hr_utility.trace('p_end_duration ' || p_end_duration);
189: hr_utility.trace('End p_duration ' || p_duration);
190: --
191: -- Get between full day hours
190: --
191: -- Get between full day hours
192: SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
193: INTO p_duration
194: FROM DUAL;
195: ELSE
196: -- Start and End on same day
197: l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
198: l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
202:
203: SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
204: (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
205: INTO p_duration
206: FROM DUAL;
207: hr_utility.trace('duration l_idx '||l_idx||' ' ||p_duration);
208:
209: END IF;
210: END IF;
404: returned varies with NLS_TERRITORY. So replaced it with to_char(some_date,'DY') that gives the abbreviated day. */
405: CURSOR business_days_cur (p_low_date IN DATE,
406: p_high_date IN DATE) IS
407: SELECT SUM(DECODE(TO_CHAR(p_low_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), 'SUN', 0, 'SAT', 0, 1))
408: FROM DUAL
409: CONNECT BY 1=1
410: AND ROWNUM <= (p_high_date + 1) - p_low_date;
411:
412: -- Added the following cursor to find whether a particular day is a working day or not when
413: -- working schedules is not enabled, Bug 6779790
414:
415: CURSOR business_days2_cur (p_date IN DATE) IS
416: SELECT DECODE(TO_CHAR(p_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), 'SUN', 0, 'SAT', 0, 1)
417: FROM DUAL;
418:
419: -- Introduced the following cursors for work schedules enh.
420: l_schedule_source VARCHAR2(100);
421: l_schedule VARCHAR2(100);
434: CURSOR business_days_ws_cur (p_low_date IN DATE,
435: p_high_date IN DATE) IS
436: SELECT SUM(DECODE(hruserdt.get_table_value(l_business_group_id, l_ws_table_name, l_work_schedule,
437: TO_CHAR(p_low_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), p_low_date+ (ROWNUM-1)), 0, 0, 1))
438: FROM DUAL
439: CONNECT BY 1=1
440: AND ROWNUM <= (p_high_date + 1) - p_low_date;
441:
442: CURSOR workschedule_config_cur IS
820: WHERE task_id = p_task_id;
821:
822: SELECT least(p_payroll_end_date,l_project_end_date,l_award_end_date,l_completion_date)
823: INTO l_poeta_effective_date
824: FROM dual;
825:
826: IF (l_poeta_effective_date < p_payroll_end_date ) THEN
827: p_effective_date := p_payroll_end_date ;
828: ELSE
862: WHERE task_id = p_task_id;
863:
864: SELECT least(p_payroll_end_date,l_project_end_date,l_completion_date)
865: INTO l_poeta_effective_date
866: FROM dual;
867:
868: IF (l_poeta_effective_date < p_payroll_end_date ) THEN
869: p_effective_date := p_payroll_end_date ;
870: ELSE
2923:
2924: -- Change History :
2925:
2926: ------------------------------------------------------------------------------------
2927: -- RETURN CODES OF EACH OF THE INDIVIDUAL VALIDATION MEAN THE FOLLOWING
2928: ------------------------------------------------------------------------------------*/
2929: /*
2930: 0 => 'Transactions does not Exist in LD'
2931: 1 => 'Invalid Input Parameters';