/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2018 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_cost_allocation_rev_v 
AS
SELECT hat.transaction_id
      ,fnd_global.employee_id person_id
      ,hat.assignment_id
      ,hat.transaction_id approvers 
      ,cast(multiset(
           select ca.transaction_mode,
		          to_number(ca.proportion),
                  ca.concatenated_segments,
				  fnd_date.canonical_to_date(ca.start_date),
                  fnd_date.canonical_to_date(ca.end_date)
           from XMLTable('//value/*'
                    PASSING (select XXAS_COST_ALLOCATION_PKG.get_review_details(transaction_id)
                             from dual)
                    COLUMNS TRANSACTION_MODE      VARCHAR2(20)     PATH '/XXAS_REVIEW_DETAILS/TRANSACTION_MODE',
							PROPORTION   		  VARCHAR2(5)     PATH '/XXAS_REVIEW_DETAILS/PROPORTION',
                            CONCATENATED_SEGMENTS VARCHAR2(4000)   PATH '/XXAS_REVIEW_DETAILS/CONCATENATED_SEGMENTS',
							START_DATE   		  VARCHAR2(100)    PATH '/XXAS_REVIEW_DETAILS/START_DATE',
							END_DATE     		  VARCHAR2(100)    PATH '/XXAS_REVIEW_DETAILS/END_DATE'
                        ) ca
                ) as xxas_ca_review_tbl) REVIEW_DETAILS
FROM   hr_api_transactions         hat;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_COST_ALLOCATION_REV_V'
   ,p_description            => 'Applaud Cost Allocation Review');
END;
/

COMMIT;
EXIT;