/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 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_sql varchar2(32000);

BEGIN

  --
  -- Register Query People By Name
  --
  l_sql :=
'        SELECT papf.person_id
              ,papf.global_name short_name
              ,nvl(pos.name, nvl(pj.name, hou.name)) job_title
              /*,nvl2(img.image_id, ''Y'', ''N'') has_image */
        FROM   per_all_people_f papf
              ,per_all_assignments_f paaf
              ,hr_all_organization_units hou
              ,per_jobs pj
              ,hr_all_positions_f pos
              ,per_images img
        WHERE  papf.person_id = paaf.person_id
        AND  ((papf.current_employee_flag IS NOT NULL AND
               papf.current_employee_flag = ''Y'' AND
               paaf.assignment_type = ''E'') OR
              (papf.current_npw_flag IS NOT NULL AND
               papf.current_npw_flag = ''Y'' AND
               paaf.assignment_type = ''C''))
        AND    paaf.primary_flag = ''Y''
        AND    trunc(sysdate) BETWEEN
               papf.effective_start_date AND papf.effective_end_date
        AND    trunc(sysdate) BETWEEN
               paaf.effective_start_date AND paaf.effective_end_date
        AND    paaf.organization_id = hou.organization_id
        AND    paaf.job_id = pj.job_id(+)
        AND    paaf.position_id = pos.position_id(+)
        AND    trunc(sysdate) BETWEEN
               pos.effective_start_date(+) AND pos.effective_end_date(+)
        AND    papf.person_id = img.parent_id(+)
        AND    img.table_name(+) = ''PER_PEOPLE_F''
        AND    papf.person_id IN
                (SELECT column_value
                 FROM TABLE(xxas_per_search_pkg.people_by_name_tbl
                             (:pSearchText, trunc(sysdate))))
        ORDER BY 2, 3 ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryPeopleByName'
    ,p_securing_function => 'XXAS_SRV_Q_PER_NAME_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pSearchText', xxas_com_sr_pkg.g_VARCHAR2))
    ,p_page_size         => 25);

END;
/
COMMIT;
EXIT;
