/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_OFFER_HISTORY_DETAILS_V 
AS
SELECT offer.offer_id             as offer_id
      ,status.meaning             as off_hist_status
      ,history.status_change_date as off_hist_status_change_date
      ,responder.full_name        as resp_full_name
FROM   irc_offers offer
      ,irc_offer_status_history history
      ,hr_lookups status
      ,fnd_user responduser
      ,per_all_people_f responder
WHERE  history.offer_id          = offer.offer_id
/*AND history.offer_status_history_id =
  (SELECT MAX(osh.offer_status_history_id)
  FROM irc_offer_status_history osh
  WHERE osh.offer_id = offer.offer_id
  )*/
AND    status.lookup_code(+)     = offer.offer_status
AND    status.lookup_type(+)     = 'IRC_OFFER_STATUSES'
AND    responduser.user_id(+)    = history.last_updated_by
AND    responder.person_id(+)    = responduser.employee_id
AND    TRUNC(sysdate) BETWEEN NVL(responder.effective_start_date(+), TRUNC(sysdate) ) AND NVL(responder.effective_end_date(+), TRUNC(sysdate) );

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_OFFER_HISTORY_DETAILS_V'
   ,p_description            => 'Applaud Offer History Details');
END;
/
  
COMMIT;
EXIT;