/* $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_person_tags_v 
AS
SELECT tc.tag_id
      ,tc.tag
      ,t.pk_id person_id
      ,nvl
        (xxas_util_pkg.get_formatted_name
          (papf.full_name, papf.global_name, papf.local_name, papf.first_name, papf.last_name, papf.known_as)
        ,u.user_name) created_by
      ,t.creation_date
      ,t.created_by created_by_user_id
FROM   xxas_com_tags t
      ,xxas_com_tag_cloud tc
      ,fnd_user u
      ,per_all_people_f papf
WHERE  t.tag_id = tc.tag_id
AND    t.entity_name = 'PERSON'
AND    t.created_by = u.user_id
AND    u.employee_id = papf.person_id(+)
AND    trunc(sysdate) BETWEEN
       papf.effective_start_date(+) AND papf.effective_end_date(+);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERSON_TAGS_V'
   ,p_description            => 'Applaud Person Tags');
END;
/
  
COMMIT;
EXIT;
