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

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'CalEvent'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    ,p_path               => 'calendar-event');


  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateCalEvent'
    ,p_description        => 'Updatates the Calendar Event Entry'
    ,p_methods            => 'PUT'
    ,p_path               => 'edit-entry'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp     xxas_http_resp_obj;
          l_resp_xml XMLType;
        BEGIN

          l_resp_xml := xxas_cal_event_pkg.edit_entry_response
                          (p_resource_id     => {requestParam.resourceId}
                          ,p_event_id        => {requestParam.eventId}
                          ,p_start_time      => {requestParam.startTime}
                          ,p_end_time        => {requestParam.endTime}
                          ,p_source_block    => replace({requestParam.sourceBlock},'_','$')
                          ,p_saved_search_id => {requestParam.savedSearchId}
                          ,p_manager_id      => {requestParam.managerId});

          l_resp := xxas_http_response
                      .ok(l_resp_xml
                         )
                        .build();
          {response} := l_resp;

        END
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateAbsenceEventEntryForm'
    ,p_description        => 'Creates the Calendar Absence Event Entry Form'
    ,p_methods            => 'POST'
    ,p_path               => 'absence'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp xxas_http_resp_obj;
        BEGIN
          l_resp := xxas_http_response
                      .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                            ('absence-new/'
                         || (CASE WHEN {requestParam.resourceId} = '{resourceId}'
                                  THEN {requestParam.resourceId}
                                  ELSE to_char(xxas_util_pkg.get_person_from_asg({requestParam.resourceId},
                                  fnd_date.canonical_to_date(replace({requestParam.startTime},'{startTime}',to_char(sysdate,'YYYY-MM-DD')))))
                              END)
                         || '_'
                         || {requestParam.startTime}
                         || '_'
                         || {requestParam.endTime}
                         || '___'
                         || replace({requestParam.sourceBlock},'_','$')
                         || '_'
                         || {requestParam.savedSearchId}
                         || '_'
                         || {requestParam.managerId}
                            ,'modal'
                            ,'true'
                            )
                         )
                      .build();

         {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateWeeklyTimecardEntryForm'
    ,p_description        => 'Creates the calendar weekly timecard entry Form'
    ,p_methods            => 'POST'
    ,p_path               => 'weekly-timecard-entry'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp xxas_http_resp_obj;
        BEGIN

          l_resp := xxas_http_response
                      .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                            ('time-weekly-edit/'
                         || {requestParam.personId}
                         || '_'
                         || replace({requestParam.startTime},'{startTime}',to_char(sysdate,'YYYY-MM-DD'))
                         || '_'
                         || replace({requestParam.endTime},'{endTime}',to_char(sysdate,'YYYY-MM-DD'))
                         || '_'
                         || xxas_cal_util_pkg.default_weekly_payment_type
                              (xxas_util_pkg.get_primary_worker_asg({requestParam.personId},replace({requestParam.startTime},'{startTime}',trunc(sysdate)))
                              ,fnd_date.canonical_to_date(replace({requestParam.startTime},'{startTime}',to_char(sysdate,'YYYY-MM-DD')))
                              ,{requestParam.savedSearchId})
                         || '_'
                         || {requestParam.excludeTermBy}
                         || '_'
                         || replace({requestParam.sourceBlock},'_','$')
                         || '_'
                         || {requestParam.savedSearchId}
                            ,null
                            ,'true'
                            )
                         )
                      .build();

         {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateTimecardEventEntryForm'
    ,p_description        => 'Creates the Calendar Timecard Event Entry Form'
    ,p_methods            => 'POST'
    ,p_path               => 'timecard-entry'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp xxas_http_resp_obj;
          l_res   XMLType;
        BEGIN

          l_res := xxas_cal_util_pkg.eligible_timecard_period
                     ({requestParam.savedSearchId}
                     ,{requestParam.sourceBlock}
                     ,{requestParam.resourceId}
                     ,{requestParam.startTime}
                     ,{requestParam.endTime}
                     );
               
          IF l_res is not null Then
            l_resp := xxas_http_response
                        .ok(l_res).build();

			IF 1=2 THEN
			   l_res := xxas_cal_util_pkg.eligible_timecard_period
					        ({requestParam.resourceId},   
							 {requestParam.startTime},
							 {requestParam.savedSearchId},
							 {requestParam.sourceBlock},
							 {requestParam.savedSearchId}
							 );
            END IF;

	        ELSE
            l_resp := xxas_http_response
                        .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                              ('time-new/'
                              || {requestParam.resourceId}
                              || '_'
                              || replace({requestParam.startTime},'{startTime}',to_char(sysdate,'YYYY-MM-DD'))
                              || '_'
                              || {requestParam.endTime}
                              || '_'
					   	                || xxas_cal_util_pkg.default_payment_type
						                       (replace({requestParam.resourceId},'{resourceId}')
                                   ,fnd_date.canonical_to_date(replace({requestParam.startTime},'{startTime}',to_char(sysdate,'YYYY-MM-DD')))
                                   ,{requestParam.savedSearchId})
                              || '_'
                              || replace({requestParam.sourceBlock},'_','$')
                              || '_'
                              || {requestParam.savedSearchId}
                              ,'modal'
                              ,'true'
                              )
                           )
                        .build();
           END IF;
						
           {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateOtlTimecardEventEntryForm'
    ,p_description        => 'Creates the Calendar OTL Timecard Event Entry Form'
    ,p_methods            => 'POST'
    ,p_path               => 'otl-timecard-entry'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp xxas_http_resp_obj;
        BEGIN
          l_resp := xxas_http_response
                      .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                            ('otl-time-new/'
                         || (CASE WHEN {requestParam.resourceId} = '{resourceId}'
                                  THEN {requestParam.resourceId}
                                  ELSE to_char(xxas_util_pkg.get_person_from_asg({requestParam.resourceId},trunc(sysdate)))
                              END)
                         || '_'
                         || {requestParam.startTime}
                         || '_'
                         || {requestParam.endTime}
                         || '_'
                         || '_'
                         || replace({requestParam.sourceBlock},'_','$')
                         || '_'
                         || {requestParam.savedSearchId}
                            ,'modal'
                            ,'true'
                            )
                         )
                      .build();

         {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindLoggedinEmpDirReporteeOnly'
    ,p_description        => 'Find people matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'search/loggedinemp-or-dir-reportee-only'
    ,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"
                       ,p.short_name "value"
                       ,p.short_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT distinct /*+ cardinality(rslt 10)*/
                      rslt.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
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             (nvl({requestParam.q},-1), trunc(sysdate))) rslt,
                      per_all_people_f papf
               WHERE  ({requestParam.id} is null AND
                       ({requestParam.dateStart} = '-1' OR {requestParam.dateStart} is null) AND
                       ({requestParam.dateEnd} = '-1' OR {requestParam.dateEnd} is null))    
               AND    papf.person_id = rslt.person_id
               AND    trunc(sysdate) BETWEEN papf.effective_start_date 
                                     AND     papf.effective_end_date
               AND    (rslt.person_id = DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})
                       OR xxas_hierarchy_pkg.is_report_char
                            (xxas_util_pkg.get_primary_worker_asg(rslt.person_id,trunc(sysdate))
                            ,trunc(sysdate)
                            ,DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})) = 'Y'
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.person_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                         distinct rslts.person_id
                                                  FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                 (null
                                                                 ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                   FROM   xxas_saved_searches_v ss
                                                                   WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                   AND    xxas_com_sec_pkg.test_instance
                                                                            ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                 )
			                                                  ) rslts
                                                  WHERE  xxas_com_sec_pkg.test_instance
                                                           ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                           ,rslts.assignment_id
                                                           ,null
                                                           ,null
                                                           ,null
                                                           ,null) = 'Y'
                                                 )
                          )
                      )
                 AND    xxas_com_sec_pkg.test_instance
                        ('XXAS_ADD_ABSENCE_PERM'
                        ,rslt.person_id
                        ,null
                        ,null
                        ,null
                        ,null)  = 'Y'
               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
               FROM   per_all_people_f rslt
               WHERE  ({requestParam.id} is not null AND
                       ({requestParam.dateStart} = '-1' OR {requestParam.dateStart} is null) AND
                       ({requestParam.dateEnd} = '-1' OR {requestParam.dateEnd} is null))
               AND    rslt.person_id = {requestParam.id}
               AND    trunc(sysdate) BETWEEN rslt.effective_start_date 
                                     AND     rslt.effective_end_date
               AND    (rslt.person_id = DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})
                       OR xxas_hierarchy_pkg.is_report_char
                            (xxas_util_pkg.get_primary_worker_asg(rslt.person_id,trunc(sysdate))
                            ,trunc(sysdate)
                            ,DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})) = 'Y'
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.person_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                         distinct rslts.person_id
                                                  FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                 (null
                                                                 ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                   FROM   xxas_saved_searches_v ss
                                                                   WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                   AND    xxas_com_sec_pkg.test_instance
                                                                            ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                 )
			                                                  ) rslts
                                                  WHERE  xxas_com_sec_pkg.test_instance
                                                           ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                           ,rslts.assignment_id
                                                           ,null
                                                           ,null
                                                           ,null
                                                           ,null) = 'Y'
                                                 )
                          )
                      )
               AND    xxas_com_sec_pkg.test_instance
                        ('XXAS_ADD_ABSENCE_PERM'
                        ,rslt.person_id
                        ,null
                        ,null
                        ,null
                        ,null)  = 'Y'
               UNION
               SELECT /*+ cardinality(rslt 10)*/
                      rslt.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
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             (nvl({requestParam.q},-1), trunc(sysdate))) rslt,
                              per_all_people_f papf
               WHERE  ({requestParam.id} is null AND
                       {requestParam.dateStart} &lt;&gt; '-1' AND
                       {requestParam.dateEnd} &lt;&gt; '-1')
               AND    papf.person_id = rslt.person_id
               AND    trunc(sysdate) BETWEEN papf.effective_start_date 
                                     AND     papf.effective_end_date
               AND    (rslt.person_id = DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})
                       OR xxas_cal_util_pkg.is_report_char
                            (rslt.assignment_id
                            ,fnd_date.canonical_to_date({requestParam.dateStart})
                            ,fnd_date.canonical_to_date({requestParam.dateEnd})
                            ,trunc(sysdate)
                            ,DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})) = 'Y'
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.person_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                         distinct rslts.person_id
                                                  FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                 (null
                                                                 ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                   FROM   xxas_saved_searches_v ss
                                                                   WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                   AND    xxas_com_sec_pkg.test_instance
                                                                            ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                 )
			                                                  ) rslts
                                                  WHERE  xxas_com_sec_pkg.test_instance
                                                           ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                           ,rslts.assignment_id
                                                           ,null
                                                           ,null
                                                           ,null
                                                           ,null) = 'Y'
                                                 )
                          )
                      )
               AND    xxas_com_sec_pkg.test_instance
                        ('XXAS_ADD_ABSENCE_PERM'
                        ,rslt.person_id
                        ,null
                        ,null
                        ,null
                        ,null)  = 'Y'
               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
               FROM   per_all_people_f rslt
               WHERE  ({requestParam.id} is not null AND
                       {requestParam.dateStart} &lt;&gt; '-1' AND
                       {requestParam.dateEnd} &lt;&gt; '-1')
               AND    rslt.person_id = {requestParam.id}
               AND    trunc(sysdate) BETWEEN rslt.effective_start_date 
                                     AND     rslt.effective_end_date
               AND    (rslt.person_id = DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})
                       OR xxas_cal_util_pkg.is_report_char
                            (xxas_util_pkg.get_primary_worker_asg(rslt.person_id,fnd_date.canonical_to_date({requestParam.dateStart}))
                            ,fnd_date.canonical_to_date({requestParam.dateStart})
                            ,fnd_date.canonical_to_date({requestParam.dateEnd})
                            ,trunc(sysdate)
                            ,DECODE({requestParam.managerId},'-1',fnd_global.employee_id,{requestParam.managerId})) = 'Y'
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.person_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                         distinct rslts.person_id
                                                  FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                 (null
                                                                 ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                   FROM   xxas_saved_searches_v ss
                                                                   WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                   AND    xxas_com_sec_pkg.test_instance
                                                                            ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                 )
			                                                  ) rslts
                                                  WHERE  xxas_com_sec_pkg.test_instance
                                                           ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                           ,rslts.assignment_id
                                                           ,null
                                                           ,null
                                                           ,null
                                                           ,null) = 'Y'
                                                 )
                          )
                      )
               AND    xxas_com_sec_pkg.test_instance
                        ('XXAS_ADD_ABSENCE_PERM'
                        ,rslt.person_id
                        ,null
                        ,null
                        ,null
                        ,null)  = 'Y'
               ) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindAsgDirReporteeOnlyAndSavedSearch'
    ,p_description        => 'Find people matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'search/asg-dr-only-and-savedsearch'
    ,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.assignment_id "id"
                       ,decode
                         (p.result_text
                         ,null
                         ,p.short_name || ', ' || xxas_cal_util_pkg.job_name(p.job_id)
                         ,p.short_name || ', ' || p.result_text) "value"
                       ,decode
                         (p.result_text
                         ,null
                         ,p.short_name || ', ' || xxas_cal_util_pkg.job_name(p.job_id)
                         ,p.short_name || ', ' || p.result_text) "label")
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("image"
                         ,XMLForest
                           (xxas_com_rest_util_pkg.uri_from_path
                              ('people/' || p.img_person_id || '/photo') "self"
                           ,p.initials "initials"
                           ,case  when p.img_person_id  is null 
                                  then  xxas_per_image_pkg.url('-1',sysdate, 'SMALL') 
                                  else  xxas_per_image_pkg.url(p.img_person_id, p.img_last_update_date, 'SMALL') 
                                  end as "small"  
                           )
                         )
                       )
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      rslt.assignment_id
                     ,rslt.short_name
                     ,rslt.result_text
                     ,null job_id
                     ,rslt.initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is null
               AND    (((xxas_hierarchy_pkg.is_report_char
                           (rslt.assignment_id
                           ,DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart}))
                           ,fnd_global.employee_id) = 'Y') AND {requestParam.savedSearchId} = -1)
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.assignment_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                             rslts.assignment_id
                                                      FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                     (null
                                                                     ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                       FROM   xxas_saved_searches_v ss
                                                                       WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                       AND    xxas_com_sec_pkg.test_instance
                                                                                ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                     )
			                                                      ) rslts
                                                      WHERE  xxas_com_sec_pkg.test_instance
                                                               ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                               ,rslts.assignment_id
                                                               ,null
                                                               ,null
                                                               ,null
                                                               ,null) = 'Y'
                                                     )
                          )
                      )
               AND    rslt.person_id = img.person_id(+)
               UNION
               SELECT rslt.assignment_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
                     ,rslt.job_id job_id
                     ,xxas_util_pkg.get_initials(papf.first_name, papf.last_name, papf.known_as) initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   per_all_people_f papf
                     ,per_all_assignments_f rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is not null
               AND    papf.person_id = rslt.person_id
               AND    trunc(sysdate) BETWEEN papf.effective_start_date 
                                     AND     papf.effective_end_date
               AND    rslt.assignment_id = {requestParam.id}
               AND    DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart})) BETWEEN rslt.effective_start_date
                                                                                                                                AND     rslt.effective_end_date
               AND    (((xxas_hierarchy_pkg.is_report_char
                           (rslt.assignment_id
                           ,DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart}))
                           ,fnd_global.employee_id) = 'Y') AND {requestParam.savedSearchId} = -1)
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.assignment_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                             rslts.assignment_id
                                                      FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                     (null
                                                                     ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                       FROM   xxas_saved_searches_v ss
                                                                       WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                       AND    xxas_com_sec_pkg.test_instance
                                                                                ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                     )
			                                                      ) rslts
                                                      WHERE  xxas_com_sec_pkg.test_instance
                                                               ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                               ,rslts.assignment_id
                                                               ,null
                                                               ,null
                                                               ,null
                                                               ,null) = 'Y'
                                                     )
                          )
                      )
               AND    papf.person_id = img.person_id(+)) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindEmployeeWorkerAsgs'
    ,p_description        => 'Find people matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'search/emp-worker-asgs'
    ,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.assignment_id "id"
                       ,decode
                         (p.result_text
                         ,null
                         ,p.short_name || ', ' || xxas_cal_util_pkg.job_name(p.job_id)
                         ,p.short_name || ', ' || p.result_text) "value"
                       ,decode
                         (p.result_text
                         ,null
                         ,p.short_name || ', ' || xxas_cal_util_pkg.job_name(p.job_id)
                         ,p.short_name || ', ' || p.result_text) "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT rslt.assignment_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
                     ,rslt.job_id job_id
               FROM   per_all_people_f papf
                     ,per_all_assignments_f rslt
               WHERE  (({requestParam.id} is null) OR
                       ({requestParam.id} is not null AND
                        {requestParam.id} = rslt.assignment_id))
               AND    papf.person_id = rslt.person_id
               AND    trunc(sysdate) BETWEEN papf.effective_start_date 
                                     AND     papf.effective_end_date
               AND    rslt.person_id = DECODE({requestParam.personId},'-1',fnd_global.employee_id,{requestParam.personId})
               AND    DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart})) BETWEEN rslt.effective_start_date
                                                                                                                                AND     rslt.effective_end_date
               AND    rslt.assignment_id IN (SELECT distinct column_value
                                             FROM   TABLE(CAST(xxas_cal_util_pkg.per_asgs_eff_as_on_date_tbl
                                                                 (DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart}))
                                                                 ,DECODE({requestParam.personId},'-1',fnd_global.employee_id,{requestParam.personId})) AS xxas_com_num_table)))
              ) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindPersonDirReporteeOnlyAndSavedSearch'
    ,p_description        => 'Find people matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'search/per-dr-only-and-savedsearch'
    ,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"
                       ,p.short_name "value"
                       ,p.short_name "label")
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("image"
                         ,XMLForest
                           (xxas_com_rest_util_pkg.uri_from_path
                              ('people/' || p.img_person_id || '/photo') "self"
                           ,p.initials "initials"
                           ,xxas_per_image_pkg.url
                              (p.img_person_id, p.img_last_update_date, 'SMALL') "small"
                           )
                         )
                       )
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      distinct rslt.person_id
                     ,rslt.short_name
                     ,rslt.initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is null
               AND    (((xxas_hierarchy_pkg.is_report_char
                           (xxas_util_pkg.get_primary_worker_asg(rslt.person_id,trunc(sysdate))
                           ,DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart}))
                           ,fnd_global.employee_id) = 'Y') AND {requestParam.savedSearchId} = -1)
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.person_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                             distinct rslts.person_id
                                                      FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                     (null
                                                                     ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                       FROM   xxas_saved_searches_v ss
                                                                       WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                       AND    xxas_com_sec_pkg.test_instance
                                                                                ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                     )
			                                                      ) rslts
                                                      WHERE  xxas_com_sec_pkg.test_instance
                                                               ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                               ,rslts.assignment_id
                                                               ,null
                                                               ,null
                                                               ,null
                                                               ,null) = 'Y'
                                                     )
                          )
                      )
               AND    rslt.person_id = img.person_id(+)
               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_util_pkg.get_initials(rslt.first_name, rslt.last_name, rslt.known_as) initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   per_all_people_f rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is not null
               AND    rslt.person_id = {requestParam.id}
               AND    DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart})) BETWEEN rslt.effective_start_date 
                                                                                                                                AND     rslt.effective_end_date
               AND    (((xxas_hierarchy_pkg.is_report_char
                           (xxas_util_pkg.get_primary_worker_asg(rslt.person_id,trunc(sysdate))
                           ,DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart}))
                           ,fnd_global.employee_id) = 'Y') AND {requestParam.savedSearchId} = -1)
                       OR ({requestParam.savedSearchId} &lt;&gt; -1
                           AND rslt.person_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                             distinct rslts.person_id
                                                      FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                     (null
                                                                     ,(SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                                                       FROM   xxas_saved_searches_v ss
                                                                       WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                       AND    xxas_com_sec_pkg.test_instance
                                                                                ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y')
                                                                     )
			                                                      ) rslts
                                                      WHERE  xxas_com_sec_pkg.test_instance
                                                               ('XXAS_DT_SAVE_SRCH_TIME_ABS'
                                                               ,rslts.assignment_id
                                                               ,null
                                                               ,null
                                                               ,null
                                                               ,null) = 'Y'
                                                     )
                          )
                      )
               AND    rslt.person_id = img.person_id(+)) p
        ]'
    );

END;
/
COMMIT;
EXIT;
