/* $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_REVIEW_FORMS_V
AS
SELECT
   xprf.perf_review_form_id
  ,xprf.perf_review_template_id
  ,xprf.perf_plan_enrollment_id
  ,xprf.person_id
  ,xprf.assignment_id
  ,xppe.perf_plan_id
  ,xxas_com_msg_pkg.get_string(xprt.title_message) review_name
  ,xxas_com_msg_pkg.get_message
    (p_message_app                   =>  xxas_util_pkg.app
    ,p_message_name                  =>  xprt.title_message
    ,p_message_token1_name           => 'PERSON_NAME' 
    ,p_message_token1_value          =>  nvl(xxas_util_pkg.get_formatted_name
                                                (papf.full_name
                                                ,papf.global_name
                                                ,papf.local_name
                                                ,papf.first_name
                                                ,papf.last_name)
                                                ,papf.last_name)) title
  ,xprf.start_date
  ,xprf.due_date
  ,xprf.status_code
  ,xxas_util_pkg.decode_lookup('XXAS_PERF_REVIEW_FORM_STATUSES', xprf.status_code) status_meaning
  ,xprf.creator_type
  ,xprf.current_step_id
  ,xprf.current_step_person_id
  ,xprf.created_by
  ,xprf.creation_date
  ,xprf.last_update_date
  ,xprf.last_updated_by
  ,xprf.last_update_login
  ,xprt.questionnaire_dff_structure questionnaire_template
  ,xprf.current_step_id perf_review_step_id
  ,xxas_perf_util_pkg.get_prev_step_id(xprf.current_step_id) previous_step_id
  ,xxas_perf_util_pkg.get_next_step_id(xprf.current_step_id) next_step_id  
  FROM xxas_perf_review_forms xprf
      ,per_all_people_f papf
      ,xxas_perf_review_templates xprt
      ,xxas_perf_plan_enrollments xppe
  WHERE xprf.perf_plan_enrollment_id = xppe.perf_plan_enrollment_id
    AND xprf.perf_review_template_id = xprt.perf_review_template_id
    AND xprf.person_id = papf.person_id
    AND trunc ( sysdate )
        BETWEEN papf.effective_start_date
            AND papf.effective_end_date
    AND NOT EXISTS
        (
         SELECT 1
           FROM xxas_com_data
          WHERE data_type = xxas_com_data_type_pkg.get_data_type_perf_form
            AND current_state_code = 'D'
            AND data_id = xprf.perf_review_form_id );
            
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_REVIEW_FORMS_V'
   ,p_description            => 'Applaud Performance Review Forms');
END;     
/       

COMMIT;
EXIT;

