OTL- Employee's Time Card Details

select hts.resource_id                      "Person id"

      ,to_char(hts.start_time,'Mon-DD-YYYY') "Start Date"

      ,to_char(hts.stop_time,'Mon-DD-YYYY')  "End Date"

      ,htb2.measure                          "Hours"

      ,petf.element_name                     "Hours Type"

      ,hta.attribute12                       "Description"

      ,htb.comment_text                      "Comments"

  from hxc_time_building_blocks  htb,

       hxc_time_building_blocks  htb1,

       hxc_time_building_blocks  htb2,

       hxc_time_attribute_usages htau,

       hxc_time_attributes       hta,

       hxc_timecard_summary      hts,

       pay_element_types_f       petf

 where htb1.parent_building_block_id= htb.time_building_block_id

   and htb1.parent_building_block_ovn = htb.object_version_number

   and htb.date_to  = hr_general.end_of_time

   and htb.scope    = 'TIMECARD'

   and htb1.scope   = 'DAY'

   and htb1.date_to = hr_general.end_of_time

   and htb2.parent_building_block_id= htb1.time_building_block_id

   and htb2.parent_building_block_ovn= htb1.object_version_number

   and htb2.scope   = 'DETAIL'

   and htb2.date_to = hr_general.end_of_time

   and htau.time_building_block_id  = htb2.time_building_block_id

   and htau.time_building_block_ovn = htb2.object_version_number

   and htau.time_attribute_id       = hta.time_attribute_id

   and hts.start_time  = htb.start_time

   and hts.resource_id = htb.resource_id

   and to_char(petf.element_type_id) = (substr(hta.attribute_category,11,length(hta.attribute_category)))

   and hts.resource_id                       = :p_person_id

   and to_char(hts.start_time,'DD-MON-YYYY') = :p_start_date

   and hta.attribute_category is not null

   order by htb1.start_time;

-- More Specific with element name

select *

  from (select tt.resource_id                 "Person id"

              ,rt.status_name                 "Status"

              ,tt.start_date                  "Start Date"

              ,tt.stop_date                   "Stop Date"

              ,round (tt.hours_worked, 2)     "Hours"

              ,round (tt.hours_worked, 2) as hours

              ,td.detail_attribute12          "Description"

              ,substr(rt.timecard_comment,40) "Comments"

              ,(select alias_value_name

                  from hxc_alias_values_v

                 where alias_definition_id = 1051 -->>

                   and attribute1 =

                       substr (td.detail_attribute_category,11,length(td.detail_attribute_category)))

                       as ot_type

         from hxc_resource_total_time_v tt,

              hxc_resource_timecards_v rt,

              hxc_timecard_details_v td

        where tt.time_id = rt.timecard_id

          and tt.detail_id = td.detail_timecard_id

          and td.detail_bld_blk_info_type_id = 1

          and tt.resource_id   = :p_person_id  

          and to_char(tt.start_date,'DD-MON-YYYY') = :p_start_date

          and tt.start_date >=

              trunc((last_day (add_months (sysdate, -3)) + 1))) pivot                          (sum (hours) 

             for ot_type 

             in ('01. Half Time Hours (0.5)' "Half Time"

                ,'02. Single Time Hours (1.0)' "Single Time"

                ,'03. Time and a Quarter (1.25)' "Time And a Quarter"

                ,'04. Time and a Half (1.5)' "Time And a Half"

                ,'05. Double Time Hours (2.0)' "Double Time"

                ,'06. Time and three quarters (1.75)' "Time And Three Quarters"

                ,'07. EW - Premium @ 1/3 Third of Time' "Premium Third"

                ,'08. EW - Premium @ 1/2 Half of Time'  "Premium Half"

                ,'09. EW - Premium @ Single'  "Uremium Single"

                ,'10. EW - Unsocial Hours @ 1/5 Fifth of Time' "Unsocial"

               ));