/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE FORCE VIEW XXAS_GRANTEES_ME_AS_PROV_V
AS
SELECT 'note'                   as "type"
       ,v.grant_id              as "id"
       ,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 "author"
       ,'performance' as "pageId"
       ,v.granter_person_id     as "objectId"
      -- ,xxas_grantees_pkg.me_as_a_fb_provider_body(v.grantee_person_id) as "body"
       , v.relationship_meaning as "body"
       ,v.grant_id
       ,v.granter_person_id
       ,v.grantee_person_id
       ,v.from_date
       ,v.to_date
       ,v.relationship_code
       ,v.name
       ,v.relationship_meaning
       ,v.assignment_id 
       ,v.permissions
       ,XMLElement
        ("data"
        /*,xxas_grantees_pkg.fb_providers_item_actions
           (v.grant_id,xxas_util_pkg.get_primary_emp_asg
            (v.granter_person_id,sysdate),'edit-feedback').get_xml() */
        ,XMLElement
           ("image"
           ,XMLElement
              ("self"
              ,xxas_com_rest_util_pkg.uri_from_path
                ('people/' || nvl(v.granter_person_id, -1) || '/photo'))
           ,CASE WHEN v.granter_person_id IS NOT NULL THEN
              XMLForest
                (xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'SMALL') "small"
                ,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'MEDIUM') "medium"
                ,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'FULL_SIZE') "fullSize"
                )
            END
           )
         ).extract('/data/*') AS "linked"
FROM   XXAS_GRANTEES_V v
      ,xxas_per_thumbnails img
      ,per_all_people_f papf
WHERE v.granter_person_id = img.person_id (+)
AND   papf.person_id = v.granter_person_id
AND   trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date;

/*(
      SELECT                                                   xpg.grant_id
              ,max(xpg.granter_person_id)                      granter_person_id
              ,max(xpg.grantee_person_id)                      grantee_person_id
              ,max(xpg.from_date)                              from_date
              ,max(xpg.to_date)                                to_date
              ,max(xpg.relationship_code)                      relationship_code
              ,max(xxas_util_pkg.get_formatted_name
                     (papf.full_name  
                     ,papf.global_name
                     ,papf.local_name 
                     ,papf.first_name 
                     ,papf.last_name  
                     ,papf.known_as)
                  )                                            name
   --           ,max(paaf.assignment_id)                         assignment_id
              ,max(hr_general.decode_lookup
                     ('XXAS_GRANTEE_RELATIONSHIPS'
                     ,xpg.relationship_code))                  relationship_meaning
              ,XMLElement
                ("permissions"
                ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                   ,XMLAGG
                      (XMLELEMENT
                         ("permission"
                         ,xpgp.permission_code
                         )
                      )
                 )                                             permissions
      FROM   XXAS_GRANTEES xpg
            ,XXAS_GRANTEE_permissions xpgp
            ,PER_ALL_PEOPLE_F papf
      WHERE  xpg.grant_id = xpgp.grant_id (+)
      AND    papf.person_id = xpg.grantee_person_id
      AND    trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
      group by xpg.grant_id
      ) v*/
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GRANTEES_ME_AS_PROV_V'
   ,p_description            => 'Applaud Performance Grantee View');
END;
/
COMMIT;
EXIT;