/******************************************************************************************************** *** QUERY : OTL - Detailed Timecard with Element Names *** MODULE : OTL/PAYROLL *** DESCRIPTION : Extracts details of the employees' timecards along with the Element Names for a *** given Payroll_name, start_date and stop_date *** PARAMETERS : *** A. p_payroll_name *** B. p_start_date *** C. p_stop_date ********************************************************************************************************/
SELECT papf.employee_number, papf.full_name, hrttv.start_date, hrttv.stop_date, petf.element_name, SUM(hrttv.hours_worked) FROM hxc_resource_total_time_v hrttv, hxc_timecard_details_v htdv, per_all_people_f papf, per_all_assignments_f paaf, pay_payrolls_f ppf, pay_element_types_f petf WHERE hrttv.resource_id = papf.person_id AND hrttv.detail_id = htdv.detail_timecard_id AND hrttv.resource_id = htdv.detail_resource_id AND htdv.detail_bld_blk_info_type_id = 1 AND petf.element_type_id = SUBSTR(htdv.detail_attribute_category,11,LENGTH(htdv.detail_attribute_category)) AND papf.person_id = paaf.person_id AND paaf.payroll_id = ppf.payroll_id AND ppf.payroll_name = :p_payroll_name AND hrttv.stop_date BETWEEN papf.effective_start_date AND papf.effective_end_date AND hrttv.stop_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND hrttv.stop_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND hrttv.stop_date BETWEEN petf.effective_start_date AND petf.effective_end_date AND TRUNC(hrttv.start_date) >= :p_start_date AND TRUNC(hrttv.stop_date) <= :p_stop_date GROUP BY papf.employee_number,papf.full_name,hrttv.start_date,hrttv.stop_date,petf.element_name ORDER BY papf.employee_number,hrttv.start_date,hrttv.stop_date,petf.element_name; |
Technical >