/* $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 VIEW XXAS_PERF_PLAN_REVIEWS_LIST_V
AS
SELECT  'flexible' "type",
        CASE xprf.status_code
          WHEN 'NS' -- Not started
          THEN CASE WHEN trunc(xprf.start_date) > trunc(sysdate)
                    THEN 'calendar'
                    ELSE 'spinner'
               END
          WHEN 'D' -- In progress
          THEN 'spinner'
          WHEN 'A' -- Completed
          THEN 'tick'
          WHEN 'O' -- Completed (Optional)
          THEN 'tick'
          WHEN 'X' -- Cancelled
          THEN 'close'
        END "icon",
        CASE xprf.status_code
          WHEN 'NS' -- Not started
          THEN CASE WHEN trunc(xprf.start_date) > trunc(sysdate)
                    THEN xxas_rslt_cache_pkg.resolve_color_name('grey')
                    ELSE xxas_rslt_cache_pkg.resolve_color_name('orange')
               END
          WHEN 'D' -- In progress
          THEN xxas_rslt_cache_pkg.resolve_color_name('orange')
          WHEN 'A' -- Completed
          THEN xxas_rslt_cache_pkg.resolve_color_name('green')
          WHEN 'O' -- Completed (Optional)
          THEN xxas_rslt_cache_pkg.resolve_color_name('green')          
          WHEN 'X' -- Cancelled
          THEN xxas_rslt_cache_pkg.resolve_color_name('red')
        END "statusColor",
        xprf.perf_review_form_id "id",
        decode
          (xxas_emp_menu_pkg.has_access_to_page
            ('performance-review', xprf.perf_review_form_id)
            ,'Y'
            ,'performance-review') "pageId",
        decode
          (xxas_emp_menu_pkg.has_access_to_page
            ('performance-review', xprf.perf_review_form_id)
            ,'Y'
            ,xprf.perf_review_form_id) "objectId",       
        xxas_perf_review_templates_pkg.get_title
          (null,to_char(xprf.perf_review_form_id)) "line1",
        xprf.start_date,
        xprf.due_date,
        xppe.perf_plan_id,
        xppe.person_id,
        xppe.assignment_id,
        xprf.perf_plan_enrollment_id,
        '<font color="'
     || CASE xprf.status_code
          WHEN 'NS' -- Not started
          THEN CASE WHEN trunc(xprf.start_date) > trunc(sysdate)
                    THEN xxas_rslt_cache_pkg.resolve_color_name('grey')
                    ELSE xxas_rslt_cache_pkg.resolve_color_name('orange')
               END
          WHEN 'D' -- In progress
          THEN xxas_rslt_cache_pkg.resolve_color_name('orange')
          WHEN 'A' -- Completed
          THEN xxas_rslt_cache_pkg.resolve_color_name('green')
          WHEN 'O' -- Completed (Optional)
          THEN xxas_rslt_cache_pkg.resolve_color_name('green')           
          WHEN 'X' -- Cancelled
          THEN xxas_rslt_cache_pkg.resolve_color_name('red')
        END 
      || '">'
      || CASE xprf.status_code
          WHEN 'NS' -- Not started
          THEN CASE WHEN trunc(xprf.start_date) > trunc(sysdate)
                    THEN xxas_com_msg_pkg.get_string('XXAS_PERF_REV_OPENS_ON') --'Opens on '
                       || to_char(xprf.start_date,'FMdd Mon YYYY')
                    ELSE xprf.status_meaning
                      || decode(xprf.due_date, null, null
                               ,xxas_com_msg_pkg.get_string('XXAS_PERF_REV_DUE_ON') --', due on '
                             || to_char(xprf.due_date,'FMdd Mon YYYY'))
               END
          WHEN 'D' -- In progress
          THEN xprf.status_meaning
            || ' - ' || xxas_com_msg_pkg.get_string(xprts.title_message)
            || decode(xprf.due_date, null, null
                     ,xxas_com_msg_pkg.get_string('XXAS_PERF_REV_DUE_ON') --', due on '
                   || to_char(xprf.due_date,'FMdd Mon YYYY'))
          WHEN 'A' -- Completed
          THEN xprf.status_meaning
          WHEN 'O' -- Completed (Optional)
          THEN xxas_com_msg_pkg.get_string('XXAS_PERF_REV_COMPLETE') || 
		       ' (' || xxas_com_msg_pkg.get_string('XXAS_PERF_REV_PENDING') || xxas_perf_util_pkg.get_step_name(xprf.current_step_id)|| ')'         
          WHEN 'X' -- Cancelled
          THEN xprf.status_meaning
        END
      || '</div>' "line3"
       ,XMLElement
          ("data"
          ,xxas_perf_review_pkg.get_review_forms_actions
            (xprf.perf_review_form_id).get_xml()
          ).extract('/data/*') AS "linked"
   FROM xxas_perf_review_forms_v xprf,
        xxas_perf_plan_enrollments xppe,
        xxas_perf_review_steps xprs,
        xxas_perf_review_templ_steps xprts,
        per_all_people_f papf
  WHERE xprf.current_step_id = xprs.perf_review_step_id
    AND xprs.perf_review_templ_step_id = xprts.perf_review_templ_step_id
    AND xprf.perf_plan_enrollment_id = xppe.perf_plan_enrollment_id
    AND xprf.current_step_person_id = papf.person_id (+)
    AND trunc(sysdate) between nvl(trunc(papf.effective_start_date),sysdate-1) and nvl(trunc(papf.effective_end_date),sysdate)
    ORDER BY xprf.start_date;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_PLAN_REVIEWS_LIST_V'
   ,p_description            => 'Applaud Performance Reviews List');
END;
/

COMMIT;
EXIT;