Detailed Timecard with Element Names

Post date: Jan 15, 2013 10:31:45 PM

/********************************************************************************************************

*** 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;