/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2017 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
                       := 'PeopleViews';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage views of multiple people' 
    ,p_path               => 'people-views'    
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
 
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetViews'
    ,p_description        => 'Gets available views for a given person'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => '{personId}'
    ,p_sql                =>
      q'[
       SELECT 
         XMLElement
           ("data"
           ,XMLAttributes
              ('true' as "json:force-array",
               'http://json.org/' as "xmlns:json")
           ,XMLAgg
              (XMLElement
                ("item"
                ,XMLForest
                  ('flexible' "type"
                  ,v.the_id "id"
                  ,v.line1 "line1"
                  ,v.line3 "line3"
                  ,v.icon "icon"
                  ,v.page_id "pageId" 
                  ,v.object_id "objectId"
                 )
               ) ORDER BY v.last_update_date DESC
             )
           ) "data"
       FROM  (SELECT papf.person_id the_id
                    ,xxas_com_msg_pkg.get_string('XXAS_RB_EMP_DRS') line1
                    ,null line3
                    ,'drs' icon
                    ,{requestParam.pageId} page_id
                    ,to_char(papf.person_id) object_id
                    ,trunc(sysdate) + 1 last_update_date
              FROM   per_all_people_f papf
              WHERE  papf.person_id = {pathParam.personId}
              AND    trunc(sysdate) BETWEEN
                     papf.effective_start_date AND papf.effective_end_date
              AND    EXISTS
                       (SELECT null
                        FROM   TABLE(xxas_com_hier_pkg.drs_for_PER_tbl
                                      ({pathParam.personId}, trunc(sysdate))))
              UNION ALL
              SELECT rslt.saved_search_id the_id
                    ,xxas_com_msg_pkg.get_decoded_string
                       (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_SAVED_SEARCH_NAME'
                         ,xxas_com_msg_token_varray
                           (xxas_com_msg_token_type('SEARCH_NAME', rslt.search_name)))) line1
                    ,xxas_util_pkg.decode_last_updated
                      (usr.user_name, usr.last_update_date
                      ,papf.full_name, papf.global_name, papf.local_name
                      ,papf.first_name, papf.last_name, papf.known_as) line3
                    ,'searchperson' icon
                    ,{requestParam.pageId} page_id
                    ,{pathParam.personId} || '_' || to_char(rslt.saved_search_id) object_id
                    ,rslt.last_update_date
              FROM   xxas_saved_searches_v rslt
                    ,fnd_user usr
                    ,per_all_people_f papf
              WHERE  rslt.last_updated_by = usr.user_id
              AND    usr.employee_id = papf.person_id
              AND    papf.person_id = {pathParam.personId}
              AND    trunc(sysdate) BETWEEN
                     papf.effective_start_date AND papf.effective_end_date
              AND    trunc(sysdate) BETWEEN
                     usr.start_date AND nvl(usr.end_date, trunc(sysdate))
              AND   {authFunctionPredicate.XXAS_DATA_SAVED_SEARCH_VIEW}) v
     ]'
    ); 

END;
/
COMMIT;
EXIT;
