/* $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_REVIEW_PARTIC_LIST_V
AS  
SELECT 'note' "type",
        xprp.perf_review_step_id,
        xprf.perf_review_form_id,
        xprf.person_id,
        xprp.reviewer_person_id "id",
        xprp.reviewer_person_id,
        xprf.status_code form_status_code,
        xprp.status_code participant_status_code,
        CASE WHEN fnd_global.employee_id = xprp.reviewer_person_id THEN 1 
         ELSE 2
         END "seq",
        CASE WHEN fnd_global.employee_id = xprp.reviewer_person_id THEN xxas_com_msg_pkg.get_string('XXAS_PERF_YOU')
         ELSE 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)
         END || ' ('||xpp.participant ||')' "author",
         CASE WHEN xprp.status_code = 'A' THEN '<font color="#74b343">'
                WHEN xprp.status_code = 'D'  THEN '<font color="#f2a61e">'
                WHEN xprp.status_code = 'NS'  THEN '<font color="#e12028">'
                ELSE '<font color="#018ed8">'
            END || xxas_util_pkg.decode_lookup('XXAS_PERF_REVIEW_PER_STATUSES', xprp.status_code)||'</font>' "body",
        XMLElement
          ("data"
          ,xxas_perf_participants_pkg.currently_with_actions
             (xprf.perf_review_form_id
             ,xprp.reviewer_person_id).get_xml()
          ,XMLElement
             ("image"
             ,XMLElement
                ("self"
                ,xxas_com_rest_util_pkg.uri_from_path
                  ('people/' || nvl(xprp.reviewer_person_id, -1) || '/photo'))
             ,CASE WHEN xprp.reviewer_person_id IS NOT NULL THEN
                XMLForest
                  (xxas_per_image_pkg.url(xprp.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                  ,xxas_per_image_pkg.url(xprp.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                  ,xxas_per_image_pkg.url(xprp.reviewer_person_id, t.last_update_date, 'FULL_SIZE') "fullSize"
                  )
              END
             )
           ).extract('/data/*') AS "linked"
   FROM xxas_perf_review_participants xprp,
        xxas_perf_participants_v xpp,
        xxas_per_thumbnails t,
        per_all_people_f papf,
        xxas_perf_review_forms xprf
  WHERE  xprf.current_step_id = xprp.perf_review_step_id
    AND xpp.perf_participant_id = xprp.perf_participant_id
    AND xprp.reviewer_person_id = t.person_id (+)
    AND  xprp.reviewer_person_id = papf.person_id
    AND trunc ( sysdate )
        BETWEEN papf.effective_start_date
            AND papf.effective_end_date
  ORDER BY 6;

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

COMMIT;
EXIT;

