/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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 FORCE VIEW xxas_com_data_v AS
  SELECT xcd.internal_data_id
  ,      xcd.data_id
  ,      xcd.data_type
  ,      xcd.current_state_code        
  ,      CASE WHEN  xcd.current_state_code = 'D'
              THEN  xxas_com_data_action_util_pkg.get_state_draft_label
              WHEN  xcd.current_state_code = 'P'
              THEN  xxas_com_data_action_util_pkg.get_state_pending_label
              WHEN  xcd.current_state_code = 'R'
              THEN  xxas_com_data_action_util_pkg.get_state_returned_label
              WHEN  xcd.current_state_code = 'A'
              THEN  xxas_com_data_action_util_pkg.get_state_approved_label
              WHEN  xcd.current_state_code = 'T'
              THEN  xxas_com_data_action_util_pkg.get_state_transferred_label
              WHEN  xcd.current_state_code = 'X'
              THEN  xxas_com_data_action_util_pkg.get_state_removed_label
              WHEN  xcd.current_state_code = 'E'
              THEN  xxas_com_data_action_util_pkg.get_state_errored_label
         END current_state 
  ,      xcd.current_ins_upd_del       
  ,      NVL(xcda.comments, xca.comments) comments 
  FROM   xxas_com_data         xcd
  ,      xxas_com_data_actions xcda
  ,      xxas_com_actions      xca
  WHERE  xcd.comment_action_id      = xca.action_id(+)
  AND    xcd.comment_data_action_id = xcda.data_action_id(+);

COMMIT;
EXIT;
