/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'Note';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage notes'
    ,p_path               => 'notes'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetJobNotes'
    ,p_description        => 'Gets notes for a person''s job'
    ,p_methods            => 'GET'
    ,p_path               => 'by-job/{assignmentId}'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
      SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('note' "type"
                     ,n.type_code
                     ,n.note_id "id"
                     ,n.last_updated_by "author"
                     ,decode(n.content, null, null
                            ,'&lt;div class="pre"&gt;'
                          || SUBSTR(n.content, 0, 3977)
                          || '&lt;/div&gt;') "body"
                     ,decode(n.reminder_date, null, null, 'timecard') "icon"
                     ,decode(n.reminder_date, null, null, 'positive') "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"
                     ,xxas_util_pkg.dateSentFormatted(n.last_update_date_time) "dateCreatedFormatted"
                     ,n.type "noteType"
                     ,nvl({requestParam.editPageId}, 'note-edit') "pageId"
                     ,n.note_id "objectId"
                     ,'modal' "target"
                     )
                   ,XMLElement
                     ("linked"
                     ,xxas_note_pkg.note_item_actions
                       (n.note_id, '/note-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
                             (n.last_updated_by_initials "initials"
                             ,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
                       )
                     )
                   ) ORDER BY n.last_update_date_time DESC
                 )
               ) "data"
        FROM (SELECT rslt.note_id
                    ,paaf.business_group_id
                    ,paaf.person_id
                    ,rslt.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 
                    ,rslt.type_code
                    ,rslt.type
                    ,rslt.content
                    ,rslt.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.get_initials
                      (lup.first_name, lup.last_name, lup.known_as) last_updated_by_initials
                    ,xxas_util_pkg.server_to_user_timezone(rslt.last_update_date) last_update_date_time
                    ,rslt.note_reminder_id
                    ,rslt.reminder_date
                    ,xxas_util_pkg.server_to_user_timezone(rslt.sent_date_time) sent_date_time
              FROM   xxas_note_reminders_v rslt
                    ,per_all_assignments_f paaf
                    ,fnd_user lu
                    ,per_all_people_f lup
                    ,xxas_per_thumbnails t
              WHERE  rslt.assignment_id = paaf.assignment_id
              AND    paaf.assignment_id = {pathParam.assignmentId}
              AND    rslt.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(+)
              AND   ({requestParam.noteType} IS NULL OR
                      ({requestParam.noteType} IS NOT NULL AND
                         rslt.type_code IN
                           (SELECT column_value
                            FROM   TABLE(xxas_util_pkg.string_to_varchar2tbl
                                          ({requestParam.noteType})))
                      )
                    )
              AND    {authFunctionPredicate.XXAS_SRV_Q_NOTES_PERM} ) n
        WHERE n.max_asg_dt_flag = 'Y'
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteNotes'
    ,p_description        => 'Bulk delete one or more notes'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          
        BEGIN
        
          --
          -- Security is handled at a row-level via the permission
          -- XXAS_D_NOTE_PERM
          --
          xxas_note_pkg.delete_notes
            (p_request_body => {requestBody});
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type(xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                           )
                         )
                      .build();
         
         {response} := l_resp;

        END
      ]'
    );
    
END;
/
COMMIT;
EXIT;
