/* $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_per_jobs_considered_v 
AS
SELECT 
	IVC.PERSON_ID
	,VAC.NAME AS VACANCY_NAME
	,ORG.NAME AS ORGANIZATION_NAME
	,IVC.LAST_UPDATE_DATE AS LAST_UPDATED_DATE
	,LKP.MEANING AS CURRENT_CONSIDERATION
FROM IRC_VACANCY_CONSIDERATIONS IVC,
  PER_ALL_VACANCIES VAC,
  HR_ALL_ORGANIZATION_UNITS_VL ORG,
  HR_LOOKUPS LKP
WHERE IVC.VACANCY_ID    = VAC.VACANCY_ID
AND VAC.ORGANIZATION_ID = ORG.ORGANIZATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN ORG.DATE_FROM(+) AND NVL(ORG.DATE_TO(+),TRUNC(SYSDATE))
AND IVC.CONSIDERATION_STATUS    = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE    = 'IRC_CONSIDERATION'
AND DECODE(hr_general.get_xbg_profile,'Y' ,vac.business_group_id,hr_general.get_business_group_id)=vac.business_group_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_per_jobs_considered_v'
   ,p_description            => 'Applaud Person Applicant Jobs Considered For');
END;
/
  
COMMIT;
EXIT;