/* $Id: mck_xxas_com_srv_q_pdm_drs_upd.sql 418 2013-10-03 11:32:57Z iharding $ */
/*============================================================================*
 |                 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 Assignment DRs
  --
  l_sql :=
         '   
         select group_name, 
                assignment_id,
                person_id, 
                short_name,
                business_group_id,
                worker_type,
                job_title,
                free_busy, 
                avail_color,
                avail,
                avail_desc,
                avail_update  from        
         (
         SELECT /*+ cardinality(hier 10)*/
              1
              ,''My Associates'' group_name
              ,hier.primary_key_id assignment_id
              ,hier.person_id
              ,xxas_util_pkg.get_formatted_name
                                  (papf.full_name
                                  ,papf.global_name
                                  ,papf.local_name
                                  ,papf.first_name
                                  ,papf.last_name) short_name
              ,papf.business_group_id
              ,xxas_com_asg_pkg.get_worker_type(hier.primary_key_id, :pEffectiveDate) worker_type
              ,xxas_com_asg_pkg.get_job_title(hier.primary_key_id, :pEffectiveDate) job_title
              ,''Y''  has_actions
              ,null /*xxas_com_avail_pkg.get_free_busy(hier.primary_key_id, :pEffectiveDate)*/ free_busy
              ,null /*xxas_com_avail_pkg.get_color(hier.primary_key_id, :pEffectiveDate)*/ avail_color
              ,null /*xxas_com_avail_pkg.get_availability(hier.primary_key_id, :pEffectiveDate)*/ avail
              ,null /*xxas_com_avail_pkg.get_description(hier.primary_key_id, :pEffectiveDate)*/ avail_desc
              ,null /*xxas_com_avail_pkg.get_update_datetime(hier.primary_key_id, :pEffectiveDate)*/ avail_update
        FROM   TABLE(xxas_com_hier_pkg.org_chart_drs_tbl(''ASG'', ''ASG'', :pAssignmentId, :pEffectiveDate)) hier
              ,per_all_people_f papf
        WHERE  hier.person_id = papf.person_id(+)
        AND    :pEffectiveDate BETWEEN
               papf.effective_start_date(+) AND papf.effective_end_date(+)
        AND    hier.entity_code = ''ASG''
        
        UNION 
         
         SELECT 
               2
               ,decode(criteria_type, ''Location'', mck_xxas_util.get_loc_name(ppl.organization_id),  
                            mck_xxas_util.get_org_name(nvl(ppl.level3_org_id, ppl.organization_id))) group_name
              ,ppl.assignment_id assignment_id
              ,papf.person_id
              ,xxas_util_pkg.get_formatted_name
                                  (papf.full_name
                                  ,papf.global_name
                                  ,papf.local_name
                                  ,papf.first_name
                                  ,papf.last_name) short_name
              ,papf.business_group_id
              ,xxas_com_asg_pkg.get_worker_type(ppl.assignment_id, :pEffectiveDate) worker_type
              ,xxas_com_asg_pkg.get_job_title(ppl.assignment_id,  :pEffectiveDate) job_title
              ,''Y'' has_actions
              ,null /*xxas_com_avail_pkg.get_free_busy(ppl.assignment_id,  :pEffectiveDate)*/free_busy
              ,null /*xxas_com_avail_pkg.get_color(ppl.assignment_id,  :pEffectiveDate)*/ avail_color
              ,null /*xxas_com_avail_pkg.get_availability(ppl.assignment_id,  :pEffectiveDate)*/ avail
              ,null /*xxas_com_avail_pkg.get_description(ppl.assignment_id,  :pEffectiveDate)*/  avail_desc
              ,null /*xxas_com_avail_pkg.get_update_datetime(ppl.assignment_id,  :pEffectiveDate)*/ avail_update
        FROM   mck_per_person_lists ppl
              ,fnd_responsibility resp
              ,per_all_people_f papf
        WHERE 
               ppl.user_id = fnd_global.user_id
        AND    ppl.person_id =  papf.person_id 
        AND    :pEffectiveDate BETWEEN
               papf.effective_start_date AND papf.effective_end_date
        AND    papf.current_employee_flag = ''Y''
        AND    ppl.responsibility_id = resp.responsibility_id
        and    resp.application_id = 800
        AND    resp.responsibility_key = ''XXMCK_PD''
        
        ORDER BY 1,2,5,7
        ) ';

  xxas_com_sr_pkg.add_custom_query_service
    (p_service_name      => 'Xx_MCK_PDM_QueryAssignmentDRs'
    ,p_securing_function => 'XXAS_SRV_Q_EMP_VIEWS_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pAssignmentId'      , xxas_com_sr_pkg.g_NUMBER)
                              ,xxas_com_sr_param_type('pEffectiveDate'     , xxas_com_sr_pkg.g_DATE)));

END;
/
COMMIT;
EXIT;
