/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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 Contacts
  --
  l_sql :=
'        SELECT XMLElement
                ("View"
                ,XMLAttributes
                  (xxas_emp_views_pkg.create_allowed(:pViewName, :pAssignmentId) as "createAllowed")
                  ,XMLElement
                    ("Rows"
                    ,XMLAgg
                      (XMLElement
                        ("Row"
                        ,XMLAttributes
                          (r.delete_allowed as "deleteAllowed")
                        ,XMLForest
                          (person_id as "PersonId"
                          ,pending_person_id as "PendingPersonId"
                          ,assignment_id as "AssignmentId"
                          ,instance_pk1_value as "InstancePk1Value"
                          ,instance_pk2_value as "InstancePk2Value"
                          ,instance_pk3_value as "InstancePk3Value"
                          ,instance_pk4_value as "InstancePk4Value"
                          ,instance_pk5_value as "InstancePk5Value"
                          ,status_icon as "StatusIcon"
                          ,status_color as "StatusColor"
                          ,status_text as "StatusText"
                          ,main_text as "MainText"
                          ,sub_text as "SubText"))))) "ContactsView"
          FROM table(xxas_per_contact_pkg.get_contacts_tbl(:pViewName, :pAssignmentId)) r ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryPersonContacts'
    ,p_securing_function => 'XXAS_SRV_Q_PER_CONTACTS_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pViewName'    , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pAssignmentId', xxas_com_sr_pkg.g_NUMBER)));

END;
/
COMMIT;
EXIT;
