/* $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_assignment_status_types_v
AS
SELECT past.assignment_status_type_id
      ,nvl(amend.business_group_id, past.business_group_id) business_group_id
      ,past.legislation_code
      ,nvl(amend.active_flag, past.active_flag) active_flag
      ,past.primary_flag
      ,past.default_flag
      ,past.pay_system_status
      ,past.per_system_status
      ,nvl(amend.user_status, past.user_status) user_status
      ,past.external_status
      ,xxas_util_pkg.decode_asg_system_status(past.per_system_status) system_meaning
      ,decode
        (xxas_util_pkg.decode_asg_system_status(past.per_system_status)
        ,nvl(amend.user_status, past.user_status)
        ,nvl(amend.user_status, past.user_status)
        ,xxas_util_pkg.decode_asg_system_status(past.per_system_status)
          || ' - '
          || nvl(amend.user_status, past.user_status)) customer_status
FROM   per_assignment_status_types_v past
      ,(SELECT a.assignment_status_type_id
              ,a.per_system_status
              ,a.active_flag
              ,a.business_group_id
              ,atl.user_status
        FROM   per_ass_status_type_amends a
              ,per_ass_status_type_amends_tl atl
        WHERE  a.ass_status_type_amend_id = atl.ass_status_type_amend_id
        AND    atl.language = userenv('LANG')) amend
WHERE  past.assignment_status_type_id = amend.assignment_status_type_id(+);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_ASSIGNMENT_STATUS_TYPES_V'
   ,p_description            => 'Applaud Assignment Status Types');
END;
/

COMMIT;
EXIT;

