/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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 VIEW xxas_wl_del_access_det_v
AS
SELECT   RAWTOHEX(grant_guid) grant_guid
           ,wf.person_id person_id
        --  ,nvl(g.grantee_key,fnd_global.user_name) grantee
          ,nvl(g.grantee_key,fnd_global.user_name) grantee
     --  ,'EMAVERY'   GRANTEE
           ,to_char(g.start_date,'YYYY-MM-DD') ||'_' ||to_char(g.end_date,'YYYY-MM-DD') date_range
           ,nvl2(g.parameter2,'N','Y')  all_notif
            ,xxas_com_worklist_pkg.get_item_types(g.parameter2)   item_type
                   FROM    fnd_grants g 
      ,(SELECT photo.person_id
              ,usr.user_name
              ,photo.last_update_date
        FROM   fnd_user usr
              ,xxas_per_thumbnails photo
        WHERE  photo.person_id = usr.employee_id
       )wf
  WHERE MENU_ID = (SELECT fm.menu_id FROM fnd_menus fm WHERE fm.menu_name = 'FND_WF_WORKLIST')
  AND OBJECT_ID in (SELECT fo.object_id  FROM fnd_objects fo  WHERE fo.database_object_name = 'WF_NOTIFICATIONS')
  AND INSTANCE_SET_ID = (SELECT fis.instance_set_id  FROM FND_OBJECT_INSTANCE_SETS fis WHERE fis.instance_set_name = 'WL_PROXY_ACCESS')
  AND INSTANCE_TYPE            = 'SET'
 AND nvl(parameter1,fnd_global.user_name) = wf.user_name(+);
 
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_wl_del_access_det_v'
   ,p_description            => 'Applaud wl delegate access Details');
END;
/
COMMIT;
EXIT;

