/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_VAC_OFFERS_REVIEW_V as 
SELECT  rslt.transaction_id
       ,rslt.transaction_ref_id offer_id
       ,paaf.vacancy_id 
	   ,rslt.transaction_id approvers
	   ,pav.manager_id
	   ,pav.recruiter_id
FROM    hr_api_transactions rslt, 
        per_all_assignments_f paaf,
        per_all_vacancies pav
WHERE   rslt.transaction_ref_table = 'IRC_OFFERS'
AND     rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND     rslt.assignment_id = paaf.assignment_id
AND     trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
AND     pav.vacancy_id = paaf.vacancy_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_VAC_OFFERS_REVIEW_V'
   ,p_description            => 'Applaud Vacancy Offers Review');
END;
/
COMMIT;
EXIT;