Technical‎ > ‎

Detailed Timecard with Element Names

posted Jan 15, 2013, 2:31 PM by Biswajit Mohanty
/********************************************************************************************************
*** 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;

Comments