/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_tcm_clocked_in_v
AS
  SELECT   xtpc.pending_clock_id
          ,xtpc.assignment_id
          ,xtpc.person_id
          ,xtpc.start_date_time
		  ,to_char(fnd_date.canonical_to_date(NVL(extractvalue(xtpc.pending_details,'//In/ServerDateTime'),extractvalue(xtpc.pending_details,'//LunchIn/ServerDateTime'))),'YYYY-MM-DD') start_date  
		  ,to_char(fnd_date.canonical_to_date(NVL(extractvalue(xtpc.pending_details,'//In/ServerDateTime'),extractvalue(xtpc.pending_details,'//LunchIn/ServerDateTime'))),'HH24:MI') time_range  
          ,to_char(xtpc.start_date_time, 'HH24:MI') start_time
          ,xtpc.element_type_id payment_type_id
          ,petf.element_name
		  ,fnd_global.employee_id logged_person_id
  FROM 
  xxas_tcm_pending_clocks xtpc,
  pay_element_types_f petf,
  per_all_people_f papf,
  xxas_per_thumbnails img
  WHERE petf.element_type_id = xtpc.element_type_id
  AND papf.person_id = xtpc.person_id
  AND papf.person_id = img.person_id(+)
  AND trunc(sysdate) BETWEEN papf.effective_start_date 
                          AND  papf.effective_end_date
  AND trunc(sysdate) BETWEEN petf.effective_start_date 
                          AND  petf.effective_end_date 
  AND xtpc.status_code    = 'OPEN'
  AND xtpc.missed_in_flag  IS NULL 
  AND xtpc.missed_out_flag IS NULL;
  
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_TCM_CLOCKED_IN_V'
   ,p_description            => 'Applaud TCM Clocked In Entries View');
END;
/

COMMIT;
EXIT;
