/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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_PERF_STEP_ACTIONS_V
AS
SELECT xprf.*,
       xxas_perf_util_pkg.get_step_stage(xprf.current_step_id) current_step_stage,
       xxas_perf_util_pkg.get_step_stage(xprf.previous_step_id) previous_step_stage,
       xxas_perf_util_pkg.get_step_stage(xprf.next_step_id) next_step_stage,
       xxas_perf_util_pkg.get_step_interaction(xprf.current_step_id) current_step_interaction,
       xxas_perf_util_pkg.get_step_interaction(xprf.previous_step_id) previous_step_interaction,
       xxas_perf_util_pkg.get_step_interaction(xprf.next_step_id) next_step_interaction,
       xxas_perf_util_pkg.get_step_partic_count(xprf.current_step_id) current_step_partic_count,
       xxas_perf_util_pkg.get_step_partic_count(xprf.current_step_id,'Y') curr_st_part_count_ex_self,
       xxas_perf_util_pkg.get_step_partic_count(xprf.previous_step_id) previous_step_partic_count,
       xxas_perf_util_pkg.get_step_partic_count(xprf.previous_step_id,'Y') prev_st_part_count_ex_self,
       xxas_perf_util_pkg.get_step_partic_count(xprf.next_step_id) next_step_partic_count,
       xxas_perf_util_pkg.get_formatted_name
                  (xxas_perf_util_pkg.get_step_partic_id(xprf.current_step_id,'Y')) curr_st_partic_ex_self,
       xxas_perf_util_pkg.get_formatted_name
                  (xxas_perf_util_pkg.get_step_partic_id(xprf.previous_step_id,'Y')) prev_st_partic_ex_self,
       hrl.lookup_code action
  FROM xxas_perf_review_forms_v xprf
      ,hr_lookups hrl
  WHERE hrl.lookup_type = 'XXAS_PERF_STEP_ACTIONS'
    AND hrl.enabled_flag     = 'Y'
    AND trunc(sysdate )
        BETWEEN nvl(hrl.start_date_active,
                trunc(sysdate))
            AND nvl(hrl.end_date_active,
                trunc(sysdate));
            
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_STEP_ACTIONS_V'
   ,p_description            => 'Applaud Performance Review Step Actions');
END;     
/       

COMMIT;
EXIT;

