/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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_sql varchar2(32000);

BEGIN

  --
  -- Register Query Notes
  --
  l_sql :=
'        SELECT n.note_id
              ,n.business_group_id
              ,n.person_id
              ,n.assignment_id
              ,n.type_code
              ,n.type
              ,n.content
              ,n.last_updated_by_user_id
              ,n.last_updated_by_person_id
              ,n.last_updated_by
              ,n.last_update_date_time
              ,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
                    ,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
              FROM   xxas_note_reminders_v xn
                    ,per_all_assignments_f paaf
                    ,fnd_user lu
                    ,per_all_people_f lup
              WHERE  xn.assignment_id = paaf.assignment_id
              AND    paaf.person_id = :pPersonId
              AND    xn.last_updated_by = lu.user_id
              AND    lu.employee_id = lup.person_id(+)
              AND    trunc(sysdate) BETWEEN
                     lup.effective_start_date(+) AND lup.effective_end_date(+)) n
        WHERE n.max_asg_dt_flag = ''Y''
        ORDER BY n.last_update_date_time DESC ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryNotes'
    ,p_securing_function => 'XXAS_SRV_Q_NOTES_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pPersonId', xxas_com_sr_pkg.g_NUMBER)));

END;
/
COMMIT;
EXIT;
