/* $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_workflow_vacation_rules_v
AS
SELECT  wrr.rule_id
       ,wrr.rule_id                                                                       AS "id"
       ,wrr.role
       ,case wrr.action
        when 'NOOP' then
          wf_directory.GetRoleDisplayName(fnd_global.user_name)
        when 'RESPOND' then
          wf_directory.GetRoleDisplayName(fnd_global.user_name)
        else
          wf_directory.GetRoleDisplayName(nvl(wrr.action_argument,fnd_global.user_name))
        end                                                                               AS "sender"
       ,'worklist'                                                                        AS "type"
       ,CASE WHEN wrr.end_date < TRUNC(SYSDATE) THEN
          'Disabled'
        ELSE
          nvl2(wrr.end_date,'Enabled until ' 
            || xxas_com_worklist_pkg.date_to_phrase(wrr.end_date,'DD Mon YYYY')
            ,'Enabled indefinitely')
        END
          || ' | ' || 
          nvl2(wrr.message_type
              ,xxas_com_worklist_pkg.item_type_to_name(wrr.message_type)
              ,'All Notifications')                                                       AS "subject"
       ,xxas_com_rest_util_pkg.uri_from_app_route('vacation-rule/' || wrr.rule_id)        AS "href"
       ,'modal'                                                                           AS "target"
       ,XMLElement
         ("data"
         ,xxas_com_worklist_pkg.actions_vac_rules(wrr.rule_id)
         ,CASE WHEN wrr.action IN ('FORWARD','TRANSFER')THEN
           XMLElement
                 ("image"
                 ,XMLElement
                    ("self"
                    ,xxas_com_rest_util_pkg.uri_from_path
                      ('people/' || nvl(wf.person_id, -1) || '/photo'))
                 ,CASE WHEN wf.person_id IS NOT NULL THEN
                    XMLForest
                      (xxas_per_image_pkg.url(wf.person_id, wf.last_update_date, 'SMALL') "small"
                      ,xxas_per_image_pkg.url(wf.person_id, wf.last_update_date, 'MEDIUM') "medium"
                      ,xxas_per_image_pkg.url(wf.person_id, wf.last_update_date, 'FULL_SIZE') "fullSize"
                      )
                  END
                 )
         END 
         ).extract('/data/*')                                                           AS "linked"
       /*,xxas_com_worklist_pkg.actions_vac_rules(wrr.rule_id
                                               ,decode(wrr.action
                                                      ,'NOOP',fnd_global.user_name
                                                      ,'RESPOND',fnd_global.user_name
                                                      ,nvl(wrr.action_argument
                                                          ,fnd_global.user_name)
                                                      ))                                  AS "linked"*/
FROM   wf_routing_rules wrr
      ,(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  wrr.action_argument = wf.user_name(+)
ORDER  BY wrr.begin_date;
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_WORKFLOW_VACATION_RULES_V'
   ,p_description            => 'Applaud Person Vacation Rules');
END;
/
COMMIT;
EXIT;

