/* $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_applicant_statuses_v
AS
SELECT paaf.person_id
      ,paaf.assignment_id
      ,paaf.effective_start_date
      ,paaf.effective_end_date
      ,cur.assignment_status_type_id cur_assignment_status_type_id
      ,cur.per_system_status cur_per_system_status
      ,cur.user_status cur_user_status
      ,cur.external_status cur_external_status
      ,cur.system_meaning cur_system_meaning
      ,cur.customer_status cur_customer_status
      ,past.*
FROM   xxas_assignment_status_types_v past
      ,per_all_assignments_f paaf
      ,per_business_groups_perf bg
      ,xxas_assignment_status_types_v cur
WHERE  past.active_flag = 'Y'
AND    past.primary_flag = 'P'
AND    past.per_system_status IN
         ('ACTIVE_APL','INTERVIEW1','INTERVIEW2','OFFER','ACCEPTED','TERM_APL')
AND    paaf.business_group_id = bg.business_group_id
AND    nvl(past.business_group_id, paaf.business_group_id) = paaf.business_group_id
AND    nvl(past.legislation_code, bg.legislation_code) = bg.legislation_code
AND    paaf.assignment_type = 'A'
AND    trunc(sysdate) BETWEEN
       paaf.effective_start_date AND paaf.effective_end_date
AND    paaf.assignment_status_type_id = cur.assignment_status_type_id
AND    paaf.assignment_status_type_id <> past.assignment_status_type_id
AND    nvl(cur.business_group_id, paaf.business_group_id) = paaf.business_group_id
AND    nvl(cur.legislation_code, bg.legislation_code) = bg.legislation_code;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_APPLICANT_STATUSES_V'
   ,p_description            => 'Applaud Applicant Statuses');
END;
/

COMMIT;
EXIT;

