/* $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 Person
  --
  l_sql :=
'      SELECT papf.person_id AS "PersonId"
              ,xxas_util_pkg.get_username_from_per(papf.person_id, :pEffectiveDate) AS "Username"
              ,papf.business_group_id AS "BusinessGroupId"
              ,nvl(papf.current_employee_flag, ''N'') AS "IsCurrentEmployee"
              ,nvl(papf.current_npw_flag, ''N'') AS "IsCurrentContractor"
              ,decode(img.last_update_date, null, ''N'', ''Y'') AS "HasImage"
              ,xxas_per_image_pkg.url(papf.person_id, img.last_update_date, ''FULL_SIZE'') as "ImageHref"
              ,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 "ShortName"
              ,nvl(papf.known_as, papf.first_name) AS "FirstName"
              ,papf.last_name AS "LastName"
              ,papf.employee_number AS "EmployeeNumber"
              ,papf.npw_number AS "ContractorNumber"
              ,papf.sex AS "Gender"
              ,am.about_me AS "AboutMe"
              ,xxas_com_sec_pkg.test_instance(''XXAS_EMP_UPL_PHOTO_PERM'', papf.person_id) AS "UploadPhotoAllowed"
              ,xxas_com_sec_pkg.test_instance(''XXAS_EMP_TAKE_PHOTO_PERM'', papf.person_id) AS "TakePhotoAllowed"
              ,xxas_com_sec_pkg.test_instance(''XXAS_EMP_IMPFB_PHOTO_PERM'', papf.person_id) AS "ImportPhotoFromFacebookAllowed"
              ,xxas_com_sec_pkg.test_instance(''XXAS_EMP_DEL_PHOTO_PERM'', papf.person_id) AS "DeletePhotoAllowed"
              ,xxas_com_sec_pkg.test_instance(''XXAS_DATA_PERTAG_CREATE'', papf.person_id) AS "CreateTagsAllowed"
              ,xxas_com_sec_pkg.test_instance(''XXAS_DATA_ABOUTME_UPDATE'', papf.person_id) AS "UpdateAboutMeAllowed"              
              ,(SELECT XMLAgg
                         (XMLElement
                           ("Assignment"
                           ,XMLForest
                             (paaf.assignment_id AS "AssignmentId"
                             ,paaf.assignment_type AS "AssignmentType"
                             ,paaf.effective_start_date AS "EffectiveStartDate"
                             ,paaf.effective_end_date AS "EffectiveEndDate"
                             ,xxas_com_hier_pkg.get_mgr_names_by_asg
                               (paaf.assignment_id
                               ,least(paaf.effective_end_date
                                     ,greatest(paaf.effective_start_date, :pEffectiveDate))) AS "Managers"
                             ,(SELECT count(*)
                               FROM   TABLE(xxas_com_hier_pkg.drs_for_asg_tbl
                                        (paaf.assignment_id
                                        ,least(paaf.effective_end_date
                                              ,greatest(paaf.effective_start_date, :pEffectiveDate))))) AS "DirectReports"
                             ,CASE
                                WHEN paaf.effective_start_date &lt;= :pEffectiveDate AND
                                     paaf.effective_end_date &gt;= :pEffectiveDate THEN ''C''
                                WHEN paaf.effective_start_date &gt; :pEffectiveDate THEN ''F''
                                WHEN paaf.effective_end_date &lt; :pEffectiveDate THEN ''X''
                              END AS "CurrentTerminatedOrFuture"
                             ,paaf.primary_flag AS "PrimaryFlag"
                             ,xxas_com_asg_pkg.get_job_title
                               (paaf.assignment_id
                               ,least(paaf.effective_end_date
                                     ,greatest(paaf.effective_start_date, :pEffectiveDate))) AS "JobTitle"
                             ,xxas_com_asg_pkg.get_worker_type
                               (paaf.assignment_id
                               ,least(paaf.effective_end_date
                                     ,greatest(paaf.effective_start_date, :pEffectiveDate))) AS "WorkerType"
                             ,xxas_com_asg_pkg.get_status
                               (paaf.assignment_id
                               ,least(paaf.effective_end_date
                                     ,greatest(paaf.effective_start_date, :pEffectiveDate))) AS "Status"
                             ,xxas_com_sec_pkg.test_instance(''XXAS_DIR_ABOUT_ME_EDIT_PERM'', paaf.assignment_id) AS "UpdateAboutMeAllowed"
                             ,xxas_com_sec_pkg.test_instance(''XXAS_TAG_PERSON_CREATE_PERM'', paaf.assignment_id) AS "CreateTagsAllowed"
                             ,xxas_com_sec_pkg.test_instance(''XXAS_TAG_PERSON_DELETE_PERM'', paaf.assignment_id) AS "DeleteTagsAllowed"
                             ,XMLForest
                                (xxas_com_avail_pkg.get_free_busy(paaf.assignment_id) AS "FreeBusy"
                                ,xxas_com_avail_pkg.get_color(paaf.assignment_id) AS "Color"
                                ,xxas_com_avail_pkg.get_availability(paaf.assignment_id) AS "Availability"
                                ,xxas_com_avail_pkg.get_description(paaf.assignment_id) AS "Description"
                                ,xxas_com_avail_pkg.get_update_datetime(paaf.assignment_id) AS "LastUpdated"
                                ,xxas_com_avail_pkg.get_icon(paaf.assignment_id) AS "IconName") AS "Availability")))
                FROM   per_all_assignments_f paaf
                      ,per_assignment_status_types past
                WHERE  paaf.person_id = papf.person_id
                AND  ((:pEffectiveDate BETWEEN
                       paaf.effective_start_date AND paaf.effective_end_date)
                   OR (nvl(papf.current_employee_flag, ''N'') &lt;&gt; ''Y'' AND
                       nvl(papf.current_npw_flag, ''N'') &lt;&gt; ''Y'' AND
                       ((paaf.effective_end_date &lt; :pEffectiveDate AND
                        ((pos.actual_termination_date IS NOT NULL AND
                          paaf.effective_end_date = pos.actual_termination_date AND
                          paaf.assignment_type = ''E'') OR
                         (pop.actual_termination_date IS NOT NULL AND
                          paaf.effective_end_date = pop.actual_termination_date AND
                          paaf.assignment_type = ''C'')))
                     OR (paaf.effective_start_date &gt; :pEffectiveDate AND
                        ((pos.date_start IS NOT NULL AND
                          paaf.effective_start_date = pos.date_start AND
                          paaf.assignment_type = ''E'') OR
                         (pop.date_start IS NOT NULL AND
                          paaf.effective_start_date = pop.date_start AND
                          paaf.assignment_type = ''C'')))))
					OR  (nvl(papf.current_employee_flag,''Y'') = ''Y'' AND
						 nvl(papf.current_npw_flag,''Y'')= ''Y'' AND
						 paaf.effective_start_date > :pEffectiveDate AND
						(( pos.date_start > :pEffectiveDate AND		        
						   pos.date_start IS NOT NULL AND
						   paaf.effective_start_date = pos.date_start AND
					       paaf.assignment_type = ''E'') OR
						(pop.date_start > :pEffectiveDate AND
						   pop.date_start IS NOT NULL AND
						   paaf.effective_start_date = pop.date_start AND
						   paaf.assignment_type = ''C'')))
                          )	                       
                AND    paaf.assignment_type IN (''E'', ''C'')
                AND    paaf.assignment_status_type_id = past.assignment_status_type_id
                AND    past.per_system_status &lt;&gt; ''TERM_ASSIGN'') AS "Assignments"
        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
              ,xxas_per_about_me am
        WHERE  papf.person_id = :pPersonId
        AND    papf.person_id = am.person_id(+)
        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   ((:pEffectiveDate 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 > :pEffectiveDate THEN pos.date_start END		 
                 OR (papf.effective_start_date = CASE WHEN pop.date_start > :pEffectiveDate THEN pop.date_start END))
                 ))
				 )
        UNION ALL
        SELECT pc.contact_person_id AS "PersonId"
              ,null AS "Username"
              ,null AS "BusinessGroupId"
              ,''N'' AS "IsCurrentEmployee"
              ,''N'' AS "IsCurrentContractor"
              ,''N'' AS "HasImage"
              ,null AS "Images"
              ,nvl(pc.formatted_name, xxas_com_msg_pkg.get_string(''XXAS_EMP_NEW_CONTACT'')) AS "ShortName"
              ,pc.first_name AS "FirstName"
              ,pc.last_name AS "LastName"
              ,null AS "EmployeeNumber"
              ,null AS "ContractorNumber"
              ,null AS "Gender"
              ,null AS "AboutMe"
              ,''N'' AS "UploadPhotoAllowed"
              ,''N'' AS "TakePhotoAllowed"
              ,''N'' AS "ImportPhotoFromFacebookAllowed"
              ,''N'' AS "DeletePhotoAllowed"
              ,''N'' AS "CreateTagsAllowed"
              ,''N'' AS "UpdateAboutMeAllowed"                            
              ,null AS "Assignments"
        FROM   xxas_per_contacts_v pc
        WHERE  pc.contact_person_id IS NULL
        AND    :pPersonId IS NULL
        AND    pc.transaction_step_id = :pPendingPersonId ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryPerson'
    ,p_securing_function => 'XXAS_SRV_Q_PER_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pEffectiveDate'  , xxas_com_sr_pkg.g_DATE)
                              ,xxas_com_sr_param_type('pPersonId'       , xxas_com_sr_pkg.g_NUMBER)
                              ,xxas_com_sr_param_type('pPendingPersonId', xxas_com_sr_pkg.g_NUMBER)));

END;
/
COMMIT;
EXIT;
