/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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
                       := 'People';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View and maintain HR people data'
    ,p_path               => '/people'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPerson'
    ,p_description        => 'Get details of a single person'
    ,p_methods            => 'GET'
    ,p_path               => '{personId}'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
      SELECT XMLElement
               ("data"
               ,XMLForest
                 (papf.person_id "id"
                 ,'people' as "type"
                 ,xxas_util_pkg.get_formatted_name
                   (papf.full_name
                   ,papf.global_name
                   ,papf.local_name
                   ,papf.first_name
                   ,papf.last_name
                   ,papf.known_as) AS "title"
                 )
               ,XMLElement
                 ("linked"
                 ,XMLElement
                   ("image"
                   ,xxas_com_rest_mgr_pkg.get_xml_entity('/people/' || papf.person_id || '/photo'))
                 ,XMLElement
                   ("contexts"
                   ,xxas_com_rest_mgr_pkg.get_xml_entity('/people/' || papf.person_id || '/contexts'))
                 )
               ) as "data"
      FROM   per_all_people_f papf
            ,(SELECT pps.person_id
                    ,CASE WHEN max(pps.date_start)
                                 OVER
                                   (PARTITION BY pps.person_id) = pps.date_start
                          THEN 'Y'
                          ELSE 'N'
                     END is_latest
                    ,pps.date_start
                    ,pps.actual_termination_date
              FROM   per_periods_of_service pps) pos
            ,(SELECT ppp.person_id
                    ,CASE WHEN max(ppp.date_start)
                                 OVER
                                  (PARTITION BY ppp.person_id) = ppp.date_start
                          THEN 'Y'
                          ELSE 'N'
                     END is_latest
                    ,ppp.date_start
                    ,ppp.actual_termination_date
              FROM   per_periods_of_placement ppp) pop
            ,xxas_per_thumbnails img
      WHERE  papf.person_id = {pathParam.personId}
      AND    papf.person_id = pos.person_id(+)
      AND    pos.is_latest(+) = 'Y'
      AND    papf.person_id = pop.person_id(+)
      AND    pop.is_latest(+) = 'Y'
      AND    papf.person_id = img.person_id(+)
      AND   ((trunc(sysdate) BETWEEN
                papf.effective_start_date AND papf.effective_end_date)
              OR (nvl(papf.current_employee_flag, 'Y') = 'Y' AND
                  nvl(papf.current_npw_flag, 'Y') = 'Y' AND
                   ((papf.effective_start_date = CASE WHEN pos.date_start &gt; trunc(sysdate) THEN pos.date_start END		 
                 OR (papf.effective_start_date = CASE WHEN pop.date_start &gt; trunc(sysdate) THEN pop.date_start END))
                   )
                 )
            )
        ]'
    );

END;
/
COMMIT;
EXIT;
