/* $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_vacation_rules_det_v
AS
SELECT wrr.rule_id
      ,FND_GLOBAL.EMPLOYEE_ID person_id
      ,CASE WHEN wrr.message_type IS null and wrr.message_name IS null 
	    then 
        'Y' 
        else 
		'N'     
		end        all_notif
      ,nvl(wrr.message_type,'*')                                          message_type
      ,nvl(wrr.message_name,'*')                                          message_name
      ,to_char(wrr.begin_date,'YYYY-MM-DD HH24:MI:SS') ||'_' ||to_char(wrr.end_date,'YYYY-MM-DD HH24:MI:SS') date_time_range
      ,to_char(wrr.begin_date,'YYYY-MM-DD')                               begin_date 
      ,wf_directory.GetRoleDisplayName(wrr.ROLE) display_name
      ,wrr.ROLE
      ,decode(wrr.action,'FORWARD','DELEGATE','TRANSFER','TRANSFER',NULL) action
      ,to_char(wrr.end_date,'YYYY-MM-DD')                                 end_date
      ,wrr.action_argument               action_argument
      ,wf_directory.GetRoleDisplayName(wrr.action_argument)               reassign_unchanged
      ,wrr.rule_comment
      ,(SELECT wrra.TEXT_VALUE 
        FROM   WF_ROUTING_RULE_ATTRIBUTES wrra 
        WHERE  wrra.rule_id= wrr.rule_id
        AND    wrra.type='VARCHAR2' 
        AND    wrra.NAME <> 'RESULT'
        )                                                                 response_text
      ,decode(wrr.action,
             'RESPOND'
              ,nvl((SELECT wrra.TEXT_VALUE 
                    FROM   WF_ROUTING_RULE_ATTRIBUTES wrra 
                    WHERE  wrra.rule_id= wrr.rule_id
                              AND wrra.NAME = 'RESULT'
                   ),'CLOSE'
                  )
             )                                                            response_lov
      ,CASE WHEN wrr.action IN ('FORWARD','TRANSFER') THEN 
              'REASSIGN'      
              WHEN wrr.action = 'RESPOND' THEN 
              'CLOSE'
            ELSE 
              wrr.action
            END                                                           response_type
      ,(select text_value 
	    from WF_ROUTING_RULE_ATTRIBUTES 
		where rule_id = wrr.rule_id 
        and type = 'RESULT') reponse_value 
     FROM  wf_routing_rules wrr;
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_VACATION_RULES_DET_V'
   ,p_description            => 'Applaud Vacation Rule Details');
END;
/
COMMIT;
EXIT;


