/* $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_NEW_OFFERS_V as 
select hat.transaction_id
      ,transaction_ref_id offer_id
	  ,'SAVED' status_code
	  ,paf.assignment_id offer_assignment_id
      ,paf.assignment_id
      ,paf.effective_start_date as asg_effective_start_date
      ,paf.effective_end_date   as asg_effective_end_date
      ,paf.business_group_id
      ,paf.recruiter_id
      ,paf.job_id
      ,paf.grade_id
      ,paf.assignment_status_type_id
      ,paf.payroll_id
      ,paf.location_id
      ,paf.supervisor_id
      ,paf.organization_id
      ,paf.people_group_id
      ,paf.position_id
      ,paf.pay_basis_id
      ,paf.assignment_sequence
      ,paf.assignment_type
      ,paf.primary_flag
      ,paf.application_id
      ,paf.change_reason as asg_change_reason
      ,paf.employment_category
      ,paf.frequency     as asg_frequency
      ,paf.manager_flag
      ,paf.normal_hours
      ,paf.source_type
      ,paf.time_normal_finish
      ,paf.time_normal_start
      ,paf.last_update_date      as asg_last_update_date
      ,paf.last_updated_by       as asg_last_updated_by
      ,paf.last_update_login     as asg_last_update_login
      ,paf.created_by            as asg_created_by
      ,paf.creation_date         as asg_creation_date
      ,paf.object_version_number as asg_object_version_number
      ,paf.work_at_home
	  ,paf.vacancy_id
	  ,null proposed_salary
      ,null currency
      ,null grade_range
      ,null comparatio
      ,null quartile
      ,pav.manager_id	  
FROM    hr_api_transactions hat,
        per_all_assignments_f paf,
		per_all_vacancies pav
WHERE   hat.transaction_ref_table = 'IRC_OFFERS'
AND     hat.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND     paf.assignment_id = hat.assignment_id
AND     hat.transaction_effective_date between paf.effective_start_date and paf.effective_end_date
AND     pav.vacancy_id = paf.vacancy_id;

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