/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2011 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 Available Org Charts
  --
  l_sql :=
'        SELECT paaf.assignment_id
              ,paaf.position_id
              ,paaf.organization_id
              ,nvl((SELECT ''Y''
                    FROM   per_pos_structure_elements pse
                          ,per_pos_structure_versions psv
                          ,per_position_structures pps
                    WHERE  paaf.position_id IS NOT NULL
                    AND   (pse.parent_position_id = paaf.position_id OR
                           pse.subordinate_position_id = paaf.position_id)
                    AND    pse.pos_structure_version_id = psv.pos_structure_version_id
                    AND    :pEffectiveDate BETWEEN
                           psv.date_from and nvl(psv.date_to, :pEffectiveDate)
                    AND    psv.position_structure_id = pps.position_structure_id
                    AND    nvl(pps.primary_position_flag, ''N'') = ''Y''
                    AND    pps.business_group_id = paaf.business_group_id
                    AND    rownum &lt; 2), ''N'') in_position_hierarchy
              ,nvl((SELECT ''Y''
                    FROM   per_org_structure_elements ose
                          ,per_org_structure_versions osv
                          ,per_organization_structures pos
                    WHERE (ose.organization_id_parent = paaf.organization_id OR
                           ose.organization_id_child = paaf.organization_id)
                    AND    ose.org_structure_version_id = osv.org_structure_version_id
                    AND    :pEffectiveDate BETWEEN
                           osv.date_from and nvl(osv.date_to, :pEffectiveDate)
                    AND    osv.organization_structure_id = pos.organization_structure_id
                    AND    nvl(pos.primary_structure_flag, ''N'') = ''Y''
                    AND   (pos.business_group_id IS NULL OR
                           (pos.business_group_id IS NOT NULL AND
                            pos.business_group_id = paaf.business_group_id))
                    AND    rownum &lt; 2), ''N'') in_organization_hierarchy
        FROM   per_all_assignments_f paaf
        WHERE  paaf.assignment_id = :pAssignmentId
        AND    :pEffectiveDate BETWEEN
               paaf.effective_start_date AND paaf.effective_end_date ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryAvailableOrgCharts'
    ,p_securing_function => 'XXAS_SRV_Q_AVAIL_CHARTS_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;
