/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2018 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_otl_event_view_edit_v 
AS
  SELECT htbb_detail.time_building_block_id timecard_id
        ,decode(htbb_detail.type,'RANGE',htbb_detail.start_time,'MEASURE',htbb_day.start_time,null) start_date_time
        ,decode(htbb_detail.type,'RANGE',htbb_detail.stop_time,'MEASURE',htbb_day.stop_time,null)  stop_date_time
        ,decode(htbb_detail.type,'RANGE',to_char(htbb_detail.start_time, 'HH24:MI:SS'),'MEASURE',to_char(htbb_day.start_time, 'HH24:MI:SS'),null) start_time
        ,decode(htbb_detail.type,'RANGE',to_char(htbb_detail.stop_time, 'HH24:MI:SS'),'MEASURE',to_char(htbb_day.stop_time, 'HH24:MI:SS'),null) end_time
        ,htbb_detail.resource_id person_id
        ,petf.element_name
        ,petf.element_type_id hourstype_id
        ,hts.approval_status current_state_code
        ,flv.meaning         current_state
        ,(CASE WHEN xxas_cal_rslt_cache_pkg.is_otl_absence_element(petf.element_type_id) = 'Y' 
               THEN null
               ELSE decode(htbb_detail.type,'RANGE',round((htbb_detail.stop_time - htbb_detail.start_time)*24,2),'MEASURE',htbb_detail.measure,null)
          END) duration_hours
        ,(CASE WHEN xxas_cal_rslt_cache_pkg.is_otl_absence_element(petf.element_type_id) = 'Y' 
               THEN decode(htbb_detail.type,'RANGE',round((htbb_detail.stop_time - htbb_detail.start_time)*24,2),'MEASURE',htbb_detail.measure,null)
               ELSE null
          END) absence_days
        ,null absence_hours
        ,nvl(hts.transferred_to,'None') transferred_to
        ,htbb_detail.type entry_type
        ,htbb_detail.object_version_number timecard_ovn
        ,hts.start_time timecard_summary_start_date
        ,hts.stop_time  timecard_summary_end_date
        ,hts.timecard_id  timecard_summary_id
        ,hts.timecard_ovn timecard_summary_ovn
        ,fnd_date.date_to_canonical(htbb_detail.date_from) timecard_date_from
  FROM   hxc_timecard_summary     hts
        ,hxc_time_building_blocks htbb_time
        ,hxc_time_building_blocks htbb_day
        ,hxc_time_building_blocks htbb_detail
        ,hxc_time_attributes hta
        ,hxc_time_attribute_usages htau
        ,pay_element_types_f petf
        ,per_all_people_f papf
        ,fnd_lookup_values flv
  WHERE  hts.timecard_id                     = htbb_time.time_building_block_id
  AND    hts.timecard_ovn                    = htbb_time.object_version_number
  AND    htbb_time.time_building_block_id    = htbb_day.parent_building_block_id
  AND    htbb_time.object_version_number     = htbb_day.parent_building_block_ovn
  AND    htbb_time.scope                     = 'TIMECARD'
  AND    htbb_time.resource_type             = 'PERSON'
  AND    htbb_time.date_to                   = hr_general.end_of_time
  AND    htbb_day.time_building_block_id     = htbb_detail.parent_building_block_id
  AND    htbb_day.object_version_number      = htbb_detail.parent_building_block_ovn
  AND    htbb_day.scope                      = 'DAY'
  AND    htbb_day.date_to                    = hr_general.end_of_time
  AND    htbb_detail.scope                   = 'DETAIL'
  AND    htbb_detail.date_to                 = hr_general.end_of_time
  AND    htau.time_building_block_id         = htbb_detail.time_building_block_id
  AND    htau.time_building_block_ovn        = htbb_detail.object_version_number
  AND    htau.time_attribute_id              = hta.time_attribute_id
  AND    to_char(petf.element_type_id)       = (substr(hta.attribute_category,11,length(hta.attribute_category)))
  AND    hta.attribute_category is not null
  AND    hta.attribute_category not like 'SEC%'
  AND    hts.resource_id                     = papf.person_id
  AND    trunc(sysdate) BETWEEN papf.effective_start_date
                        AND     papf.effective_end_date
  AND    (nvl(papf.current_employee_flag, 'N') = 'Y'
          OR nvl(papf.current_npw_flag, 'N')   = 'Y')
  AND     hts.approval_status     = flv.lookup_code
  AND     flv.language            = userenv('LANG')
  AND     flv.view_application_id = 3
  AND     flv.lookup_type         = 'HXC_APPROVAL_STATUS'
  AND     flv.security_group_id   = 0
  AND     fnd_profile.value('XXAS_TIMESTORE') = 'OTL';

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_OTL_EVENT_VIEW_EDIT_V'
   ,p_description            => 'Applaud OTL Events View Edit View');
END;
/

COMMIT;
EXIT;

