/* $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_anytime_feedback_v
AS
SELECT n.note_id
      ,n.person_id
      ,n.assignment_id
      ,n.note_id "id"
      ,'note' "type"
      ,n.last_updated_by "author"
      ,last_updated_by_user_id
      ,decode(n.content, null, null
             ,'<div class="pre">'
           || n.content
           || '</div>') "body"
      ,n.content
      ,decode(n.reminder_date, null, null, 'timecard') "icon"
      ,decode(n.reminder_date, null, null, 'assertive') "iconColor" 
      ,decode(n.reminder_date, null, null
             ,xxas_com_msg_pkg.get_string('XXAS_RB_NOTE_REM') ||
              xxas_util_pkg.dateSentFormatted(n.reminder_date)) "status"
      ,XMLElement
        ("data"
        ,xxas_note_pkg.note_item_actions
          (n.note_id, '/anytime-feedback-edit').get_xml()
        ,XMLElement
           ("image"
           ,XMLElement
              ("self"
              ,xxas_com_rest_util_pkg.uri_from_path
                ('people/' || nvl(n.last_updated_by_person_id, -1) || '/photo'))
           ,CASE WHEN n.last_updated_by_person_id IS NOT NULL THEN
              XMLForest
                (xxas_per_image_pkg.url(n.last_updated_by_person_id, n.img_last_update_date, 'SMALL') "small"
                ,xxas_per_image_pkg.url(n.last_updated_by_person_id, n.img_last_update_date, 'MEDIUM') "medium"
                ,xxas_per_image_pkg.url(n.last_updated_by_person_id, n.img_last_update_date, 'FULL_SIZE') "fullSize"
                )
            END
           )
       ).extract('/data/*') AS "linked"
      ,xxas_util_pkg.dateSentFormatted(n.last_update_date_time) "dateCreatedFormatted"
      ,n.type_code
      ,n.type
      ,decode(n.type_code, 'FB', null, n.type) "noteType"
      ,n.last_update_date_time
      ,n.creation_date
/*      ,to_char(n.last_update_date_time, 'dd Mon YYYY hh24:mi') friendly_update_date
      ,n.note_reminder_id
      ,n.reminder_date
      ,n.sent_date_time
      ,xxas_com_sec_pkg.test_instance('XXAS_U_NOTE_PERM', n.note_id) update_allowed
      ,xxas_com_sec_pkg.test_instance('XXAS_D_NOTE_PERM', n.note_id) delete_allowed*/
FROM (SELECT xn.note_id
          ,paaf.business_group_id
          ,paaf.person_id
          ,xn.assignment_id
          ,CASE WHEN paaf.effective_start_date =
             MAX(paaf.effective_start_date)
             OVER (PARTITION BY paaf.assignment_id) 
           THEN 'Y'
           ELSE 'N'
           END max_asg_dt_flag 
          ,xn.type_code
          ,xn.type
          ,xn.content
          ,xn.last_updated_by last_updated_by_user_id
          ,lup.person_id last_updated_by_person_id
          ,t.last_update_date img_last_update_date
          ,nvl(xxas_util_pkg.get_formatted_name
                (lup.full_name
                ,lup.global_name
                ,lup.local_name
                ,lup.first_name
                ,lup.last_name)
              ,lu.user_name) last_updated_by
          ,xxas_util_pkg.server_to_user_timezone(xn.last_update_date) last_update_date_time
          ,xn.note_reminder_id
          ,xn.reminder_date
          ,xxas_util_pkg.server_to_user_timezone(xn.sent_date_time) sent_date_time
          ,xn.creation_date
    FROM   xxas_note_reminders_v xn
          ,per_all_assignments_f paaf
          ,fnd_user lu
          ,per_all_people_f lup
          ,xxas_per_thumbnails t
    WHERE  xn.assignment_id = paaf.assignment_id
    AND    xn.last_updated_by = lu.user_id
    AND    lu.employee_id = lup.person_id(+)
    AND    lu.employee_id = t.person_id(+)
    AND    trunc(sysdate) BETWEEN
           lup.effective_start_date(+) AND lup.effective_end_date(+)) n
WHERE n.max_asg_dt_flag = 'Y';

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_ANYTIME_FEEDBACK_V'
   ,p_description            => 'Applaud Anytime Feedback');
END;
/

COMMIT;
EXIT;

