/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_candidate_saved_src_v
AS
SELECT papf.person_id as person_id,
(SELECT XMLAgg
                 (XMLElement
                   ("XXAS_IRC_SEARCH_CRITERIA"
                   ,XMLForest
                     (ss.search_criteria_id as search_criteria_id
                     ,xxas_com_msg_pkg.get_message(xxas_util_pkg.app,'XXAS_SEARCH_JOB_LABEL') as search_type
                     ,xxas_com_msg_pkg.get_message(xxas_util_pkg.app,'XXAS_SEARCH_JOB_LABEL')||': '||ss.search_name as search_name
										 ,xxas_com_rest_util_pkg.uri_from_app_route(xxas_saved_search_pkg.irc_saved_search_href(ss.search_criteria_id)) as save_search_href)
                   )
                 )
        FROM   irc_search_criteria ss
        WHERE  ss.object_id = papf.person_id
        AND    ss.object_type = 'PERSON'
        AND xxas_com_sec_pkg.test
            ('XXAS_VAC_SEARCH_PERM') = 'Y') as saved_searches,
( SELECT XMLAgg
                 (XMLElement
                   ("XXAS_IRC_SEARCH_CRITERIA"
                   ,XMLForest
                     (ss.saved_search_id as search_criteria_id
                     ,xxas_com_msg_pkg.get_message(xxas_util_pkg.app,'XXAS_SEARCH_PEOPLE_LABEL') as search_type
                     ,xxas_com_msg_pkg.get_message(xxas_util_pkg.app,'XXAS_SEARCH_PEOPLE_LABEL')||': '||REPLACE(ss.search_name,'"','') as search_name
										 ,xxas_com_rest_util_pkg.uri_from_app_route(xxas_saved_search_pkg.people_search_href(ss.saved_search_id)) as save_search_href)
                   )
                 )
      FROM xxas_saved_searches ss,fnd_user fuser
      WHERE ss.user_id = fuser.user_id
      and fuser.employee_id = papf.person_id
      AND xxas_com_sec_pkg.test
          ('XXAS_PEOPLE_SEARCH_PERM')= 'Y') as SAVED_PEOPLE_SEARCHES
FROM per_all_people_f papf
WHERE 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_CANDIDATE_SAVED_SRC_V'
   ,p_description            => 'Applaud Candidate Saved Searches');
END;
/

COMMIT;
EXIT;

