/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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 FORCE VIEW xxas_note_reminders_v
  AS
  SELECT xn.note_id
  ,      xn.assignment_id
  ,      xn.type_code
  ,      hl.meaning type
  ,      xn.content
  ,      xn.note_details
  ,      xn.created_by
  ,      xn.creation_date
  ,      xn.last_update_date
  ,      xn.last_updated_by
  ,      xn.last_update_login
  ,      xnr.note_reminder_id
  ,      xnr.reminder_date
  ,      xnr.reminder_user_id
  ,      xnr.sent_date_time
  ,      xnr.created_by         reminder_created_by
  ,      xnr.creation_date      reminder_creation_date
  ,      xnr.last_update_date   reminder_last_update_date
  ,      xnr.last_updated_by    reminder_last_updated_by
  ,      xnr.last_update_login  reminder_last_update_login
  FROM   xxas_notes          xn
  ,      xxas_note_reminders xnr
  ,      xxas_com_data       xcd
  ,      hr_lookups          hl  
  WHERE  xn.note_id              = xnr.note_id(+)
  AND    xnr.reminder_user_id(+) = fnd_global.user_id
  AND    xn.note_id              = xcd.data_id
  AND    xcd.data_type           = 2
  AND    xcd.current_state_code  = 'T'
  AND    xn.type_code            = hl.lookup_code
  AND    hl.lookup_type          = 'XXAS_NOTE_TYPES';

COMMIT;
EXIT;
