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

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

DECLARE

  l_VIEW_NAME CONSTANT fnd_views.view_name%TYPE
    := 'XXAS_NOTE_TYPES_V';

  l_VIEW_TEXT CONSTANT varchar2(32767)
    := ' 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 '
    || '      ,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)) ';

BEGIN

  -- 
  -- Register the view
  --
  fnd_dictionary_pkg.UploadView
    (x_application_short_name => xxas_util_pkg.app
    ,x_view_name              => l_VIEW_NAME
    ,x_text                   => l_VIEW_TEXT
    ,x_description            => 'Note types by assignment'
    ,x_user_id                => xxas_util_pkg.app);
    
  -- 
  -- Register the view columns
  --
  fnd_dictionary_pkg.UploadViewColumn
    (x_application_short_name => xxas_util_pkg.app
    ,x_view_name              => l_VIEW_NAME
    ,x_view_column_name       => 'ASSIGNMENT_ID'
    ,x_view_column_sequence   => '5'
    ,x_user_id                => xxas_util_pkg.app);

  fnd_dictionary_pkg.UploadViewColumn
    (x_application_short_name => xxas_util_pkg.app
    ,x_view_name              => l_VIEW_NAME
    ,x_view_column_name       => 'TYPE_CODE'
    ,x_view_column_sequence   => '10'
    ,x_user_id                => xxas_util_pkg.app);
    
  fnd_dictionary_pkg.UploadViewColumn
    (x_application_short_name => xxas_util_pkg.app
    ,x_view_name              => l_VIEW_NAME
    ,x_view_column_name       => 'TYPE'
    ,x_view_column_sequence   => '15'
    ,x_user_id                => xxas_util_pkg.app);
    
  fnd_dictionary_pkg.UploadViewColumn
    (x_application_short_name => xxas_util_pkg.app
    ,x_view_name              => l_VIEW_NAME
    ,x_view_column_name       => 'DESCRIPTION'
    ,x_view_column_sequence   => '20'
    ,x_user_id                => xxas_util_pkg.app);

END;
/
COMMIT;
EXIT;
