Oracle Time and Labor: Identify Layouts used by a timecard

OTL layouts are selected for an employee based on OTL Preferences configuration.  There could be many custom layouts defined in the system.  The SQL below accepts the employee number as prameter and displays the layouts being used by the timecards entered for the employee.




SELECT

person.full_name,

       tc.start_time,

       tc.stop_time,

       tc.recorded_hours,

       tc.approval_status,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute1) timecard_entry_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute2) review_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute3) confirmation_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute4) detail_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute5) export_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute6) audit_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute7) approval_fragment_layout,

       (SELECT layout_name FROM hxc_layouts WHERE layout_id = attr.attribute8) notification_layout

FROM hxc_timecard_summary      tc,

     per_all_people_f          person,

     hxc_time_attribute_usages usag,

     hxc_time_attributes       attr

WHERE tc.resource_id = person.person_id

AND SYSDATE BETWEEN person.effective_start_date AND person.effective_end_date

AND person.employee_number = '&Employee_number'

AND tc.timecard_id = usag.time_building_block_id

AND tc.timecard_ovn = usag.time_building_block_ovn

AND attr.time_attribute_id = usag.time_attribute_id

AND attr.attribute_category = 'LAYOUT'

ORDER BY tc.start_time DESC;