/* $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_notifications_det_v
AS
SELECT  notification_id  
       ,'worklist'            AS "type"
       ,wf.recipient_role     AS "recipient"
       ,wf.subject            AS "subject"
       ,wf.begin_date    AS "dateSentFormatted"
       ,xxas_com_rest_util_pkg.uri_from_app_route('notification/' || wf.notification_id) 
                              AS "href"
       ,'modal'               AS "target"
FROM   wf_notifications wf
WHERE  (wf.recipient_role IN (SELECT ROLE_NAME
                              FROM   wf_user_roles wr
                              WHERE  wr.user_name = fnd_global.user_name
                              UNION
                              SELECT ROLE_NAME
                              FROM   wf_user_roles wr
                              WHERE  wr.user_name IN
                                       (SELECT parameter1
                                        FROM   fnd_grants fg
                                        WHERE  fg.grantee_key = fnd_global.user_name
                                        AND    instr(nvl(fg.parameter2,wf.message_type),wf.message_type) > 0
                                        AND    fg.INSTANCE_TYPE   = 'SET'
                                        AND fg.OBJECT_ID       IN  (SELECT fo.object_id from fnd_objects fo 
                                                                  where  fo.database_object_name = 'WF_NOTIFICATIONS'
                                                                  )
                                       AND Fg.MENU_ID       = (SELECT fm.menu_id
                                                               from   fnd_menus fm
                                                               where  fm.menu_name = 'FND_WF_WORKLIST'
                                                               )
                                       AND fg.INSTANCE_SET_ID = (select fis.instance_set_id
                                                                 from FND_OBJECT_INSTANCE_SETS fis
                                                                 where fis.instance_set_name = 'WL_PROXY_ACCESS'
                                                                 )
                                        AND    trunc(SYSDATE) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(SYSDATE))
                                        )
                             )
OR      wf.more_info_role IN (SELECT ROLE_NAME
                              FROM  wf_user_roles wr
                              WHERE  wr.user_name = fnd_global.user_name
                              UNION
                              SELECT ROLE_NAME
                              FROM   wf_user_roles wr
                              WHERE  wr.user_name IN
                                       (SELECT parameter1
                                        FROM   fnd_grants fg
                                        WHERE  fg.grantee_key = fnd_global.user_name
                                        AND    instr(nvl(fg.parameter2,wf.message_type),wf.message_type) > 0
                                        AND    fg.INSTANCE_TYPE   = 'SET'
                                        AND fg.OBJECT_ID       IN  (SELECT fo.object_id from fnd_objects fo 
                                                                  where  fo.database_object_name = 'WF_NOTIFICATIONS'
                                                                  )
                                       AND Fg.MENU_ID       = (SELECT fm.menu_id
                                                               from   fnd_menus fm
                                                               where  fm.menu_name = 'FND_WF_WORKLIST'
                                                               )
                                       AND fg.INSTANCE_SET_ID = (select fis.instance_set_id
                                                                 from FND_OBJECT_INSTANCE_SETS fis
                                                                 where fis.instance_set_name = 'WL_PROXY_ACCESS'
                                                                 )
                                        AND    trunc(SYSDATE) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(SYSDATE))
                                        )
                               )
         )
AND    wf.status = 'OPEN';

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_notifications_det_v'
   ,p_description            => 'Applaud Person Notification Details');
END;
/
  
COMMIT;
EXIT;
