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

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

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'PeopleSearch';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Find HR people'
    ,p_path               => '/peoplesearch'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindPeople'
    ,p_description        => 'Find people matching the given criteria'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("person"
                      ,XMLForest
                       (p.person_id "id"
                       ,decode({requestParam.showJob}
					     ,'Y'
                         ,p.short_name
                         ,decode
                        (p.result_text
                         ,null
                         ,p.short_name
                         ,p.short_name || ', ' || p.result_text)) "value" 
                       ,decode
                         ({requestParam.showJob}
                         ,'Y'
                         ,p.short_name
                         ,decode
                        (p.result_text
                         ,null
                         ,p.short_name
                         ,p.short_name || ', ' || p.result_text)) "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      rslt.person_id
                     ,rslt.short_name
                     ,rslt.result_text
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
               WHERE  {requestParam.id} IS NULL
               UNION
               SELECT papf.person_id
                     ,xxas_util_pkg.get_formatted_name
                        (papf.full_name
                        ,papf.global_name
                        ,papf.local_name
                        ,papf.first_name
                        ,papf.last_name
                        ,papf.known_as) short_name                     
                     ,null result_text
               FROM   per_all_people_f papf
               WHERE  {requestParam.id} IS NOT NULL
               AND    papf.person_id = {requestParam.id}
               AND    trunc(sysdate) BETWEEN
                      papf.effective_start_date AND papf.effective_end_date) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindDirReporteePeople'
    ,p_description        => 'Find people matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'dir-reportee'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("person"
                     ,XMLForest
                       (p.person_id "id"
                       ,decode
                         (p.result_text
                         ,null
                         ,p.short_name
                         ,p.short_name || ', ' || p.result_text) "value"
                       ,decode
                         (p.result_text
                         ,null
                         ,p.short_name
                         ,p.short_name || ', ' || p.result_text) "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      rslt.person_id
                     ,rslt.short_name
                     ,rslt.result_text
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
               WHERE  {requestParam.id} IS NULL
               AND    {authFunctionPredicate.XXAS_DIR_REP_HIER_VIEW}
               UNION
               SELECT rslt.person_id
                     ,xxas_util_pkg.get_formatted_name
                        (rslt.full_name
                        ,rslt.global_name
                        ,rslt.local_name
                        ,rslt.first_name
                        ,rslt.last_name
                        ,rslt.known_as) short_name                     
                     ,xxas_com_asg_pkg.get_job_title
                        (xxas_com_asg_pkg.get_primary_work_asg(rslt.person_id,trunc(sysdate))
                        ,trunc(sysdate)
                        ) result_text
               FROM   per_all_people_f rslt
               WHERE  {requestParam.id} IS NOT NULL
               AND    rslt.person_id = {requestParam.id}
               AND    trunc(sysdate) BETWEEN
                      rslt.effective_start_date AND rslt.effective_end_date
               AND    {authFunctionPredicate.XXAS_DIR_REP_HIER_VIEW}) p
        ]'
    );

    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindPeopleOtherthanDirectReportees'
    ,p_description        => 'Find people matching the given criteria other than direct reports'
    ,p_methods            => 'GET'
    ,p_path               => 'new-supervisor/{personId}'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("person"
                      ,XMLForest
                       (p.person_id "id"
                       ,decode({requestParam.showJob}
					     ,'Y'
                         ,p.short_name
                         ,decode
                        (p.result_text
                         ,null
                         ,p.short_name
                         ,p.short_name || ', ' || p.result_text)) "value" 
                       ,decode
                         ({requestParam.showJob}
                         ,'Y'
                         ,p.short_name
                         ,decode
                        (p.result_text
                         ,null
                         ,p.short_name
                         ,p.short_name || ', ' || p.result_text)) "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      rslt.person_id
                     ,rslt.short_name
                     ,rslt.result_text
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
               WHERE  {requestParam.id} IS NULL
               AND    not exists (select 'e' from per_all_assignments_f s 
                                  where s.person_id = rslt.person_id 
				                          connect by prior s.person_id = s.supervisor_id 
				                          and trunc(sysdate) between s.effective_start_date and s.effective_end_date 
				                          and s.primary_flag = 'Y' 
				                          and s.assignment_type in ('E', 'C') 
				                          start with s.person_id = {pathParam.personId} 
				                          and trunc(sysdate) between s.effective_start_date and s.effective_end_date 
				                          and s.primary_flag = 'Y' 
				                          and s.assignment_type in ('E', 'C'))
               UNION
               SELECT papf.person_id
                     ,xxas_util_pkg.get_formatted_name
                        (papf.full_name
                        ,papf.global_name
                        ,papf.local_name
                        ,papf.first_name
                        ,papf.last_name
                        ,papf.known_as) short_name                     
                     ,null result_text
               FROM   per_all_people_f papf
               WHERE  {requestParam.id} IS NOT NULL
               AND    papf.person_id = {requestParam.id}
               AND    trunc(sysdate) BETWEEN
                      papf.effective_start_date AND papf.effective_end_date
               AND    not exists (select 'e' from per_all_assignments_f s 
                                  where s.person_id = papf.person_id 
				                          connect by prior s.person_id = s.supervisor_id 
				                          and trunc(sysdate) between s.effective_start_date and s.effective_end_date 
				                          and s.primary_flag = 'Y' 
				                          and s.assignment_type in ('E', 'C') 
				                          start with s.person_id = {pathParam.personId} 
				                          and trunc(sysdate) between s.effective_start_date and s.effective_end_date 
				                          and s.primary_flag = 'Y' 
				                          and s.assignment_type in ('E', 'C'))) p
      ]'
	  );

END;
/
COMMIT;
EXIT;
