/* $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_PDP_TODO_LIST_V AS
SELECT   xprf.perf_plan_enrollment_id
        ,xprf.person_id
        ,'flexible'                AS "type"
        ,'infosymbol'              AS "icon"
        ,'positive'                AS "statusColor"
        ,'#/performance-review/'||
          xprf.perf_review_form_id AS "href"
        ,xxas_com_msg_pkg.get_decoded_string
          (xxas_com_msg_type
            (xxas_util_pkg.app
            ,'XXAS_PERF_REVIEW_OPEN'
            ,xxas_com_msg_token_varray
               (xxas_com_msg_token_type('REVIEW_PERIOD_NAME', xxas_com_msg_pkg.get_string(xprt.title_message))
               )
            )
          )                        AS "line2"
FROM    xxas_perf_review_forms xprf
       ,xxas_perf_review_templates xprt
WHERE   xprf.perf_review_template_id = xprt.perf_review_template_id
AND     xprf.status_code IN ('NS','D')
AND     xprf.start_date <= trunc(SYSDATE)
AND     xprf.due_date >= trunc(SYSDATE)
AND     fnd_global.employee_id IN (SELECT xprp.reviewer_person_id
                                   FROM    xxas_perf_review_participants xprp
                                   WHERE   xprp.perf_review_step_id = xprf.current_step_id
                                   AND     xprp.status_code IN ('NS','D')
                                  )     
UNION ALL
SELECT  xprf.perf_plan_enrollment_id
        ,xprf.person_id
        ,'flexible'                AS "type"
        ,'infosymbol'              AS "icon"
        ,'positive'                AS "statusColor"
        ,'#/performance-review/'||
          xprf.perf_review_form_id AS "href"
        ,xxas_com_msg_pkg.get_decoded_string
          (xxas_com_msg_type
            (xxas_util_pkg.app
            ,'XXAS_PERF_REVIEW_DUE'
            ,xxas_com_msg_token_varray
               (xxas_com_msg_token_type('REVIEW_PERIOD_NAME', xxas_com_msg_pkg.get_string(xprt.title_message))
               )
             )
           )                       AS "line2"
FROM    xxas_perf_review_forms xprf
       ,xxas_perf_review_templates xprt
WHERE   xprf.perf_review_template_id = xprt.perf_review_template_id
AND     xprf.status_code IN ('NS','D')
AND     xprf.due_date < trunc(SYSDATE);
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PDP_TODO_LIST_V'
   ,p_description            => 'PDP TO DO List');
END;
/
COMMIT;
EXIT;