/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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 FORCE VIEW xxas_note_types_v
  AS
  SELECT a.assignment_id
        ,hrl.lookup_code type_code
        ,hrl.meaning type
        ,hrl.description
  FROM (SELECT paaf.assignment_id
              ,CASE WHEN paaf.effective_start_date =
                 MAX(paaf.effective_start_date)
                 OVER (PARTITION BY paaf.assignment_id) 
               THEN 'Y'
               ELSE 'N'
               END max_asg_dt_flag 
        FROM   per_all_assignments_f paaf) a
       ,hr_lookups hrl
  WHERE a.max_asg_dt_flag = 'Y'
  AND   hrl.lookup_type = 'XXAS_NOTE_TYPES'
  AND   trunc(sysdate) BETWEEN
        nvl(hrl.start_date_active, trunc(sysdate)) AND
        nvl(hrl.end_date_active, trunc(sysdate));

COMMIT;
EXIT;
