/* $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 VIEW XXAS_COST_ALLOCATION_V 
AS

select papf.person_id
      ,xxas_util_pkg.get_primary_emp_asg(papf.person_id,sysdate) assignment_id
	  ,cast(multiset(
               select ca.cost_allocation_id,
					  ca.assignment_id,
					  ca.business_group_id,
			          ca.proportion,
                      ca.segment1,
                      ca.segment2,
                      ca.segment3,
                      ca.segment4,
                      ca.segment5,
                      ca.segment6,
                      ca.segment7,
                      ca.segment8,
                      ca.segment9,
                      ca.segment10,
                      ca.segment11,
                      ca.segment12,
                      ca.segment13,
                      ca.segment14,
                      ca.segment15,
                      ca.segment16,
                      ca.segment17,
                      ca.segment18,
                      ca.segment19,
                      ca.segment20,
                      ca.segment21,
                      ca.segment22,
                      ca.segment23,
                      ca.segment24,
                      ca.segment25,
                      ca.segment26,
                      ca.segment27,
                      ca.segment28,
                      ca.segment29,
                      ca.segment30,
					  fnd_date.canonical_to_date(ca.start_date),
                      fnd_date.canonical_to_date(ca.end_date),
					  ca.object_version_number
               from XMLTable('//value/*'
                        PASSING (select XXAS_COST_ALLOCATION_PKG.get_cost_allocations(xxas_util_pkg.get_primary_emp_asg(papf.person_id,sysdate))
                                 from dual)
                        COLUMNS COST_ALLOCATION_ID    NUMBER    		 PATH '/XXAS_COST_ALLOCATIONS/COST_ALLOCATION_ID',
								ASSIGNMENT_ID         NUMBER    		 PATH '/XXAS_COST_ALLOCATIONS/ASSIGNMENT_ID',
								BUSINESS_GROUP_ID     NUMBER    		 PATH '/XXAS_COST_ALLOCATIONS/BUSINESS_GROUP_ID',
								PROPORTION   		  NUMBER           PATH '/XXAS_COST_ALLOCATIONS/PROPORTION',
                                SEGMENT1     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT1',
                                SEGMENT2     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT2',
                                SEGMENT3     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT3',
                                SEGMENT4     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT4',
                                SEGMENT5     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT5',
                                SEGMENT6     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT6',
                                SEGMENT7     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT7',
                                SEGMENT8     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT8',
                                SEGMENT9     		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT9',
                                SEGMENT10    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT10',
                                SEGMENT11    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT11',
                                SEGMENT12    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT12',
                                SEGMENT13    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT13',
                                SEGMENT14    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT14',
                                SEGMENT15    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT15',
                                SEGMENT16    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT16',
                                SEGMENT17    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT17',
                                SEGMENT18    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT18',
                                SEGMENT19    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT19',
                                SEGMENT20    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT20',
                                SEGMENT21    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT21',
                                SEGMENT22    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT22',
                                SEGMENT23    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT23',
                                SEGMENT24    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT24',
                                SEGMENT25    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT25',
                                SEGMENT26    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT26',
                                SEGMENT27    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT27',
                                SEGMENT28    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT28',
                                SEGMENT29    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT29',
                                SEGMENT30    		  VARCHAR2(400)    PATH '/XXAS_COST_ALLOCATIONS/SEGMENT30',
								START_DATE   		  VARCHAR2(100)    PATH '/XXAS_COST_ALLOCATIONS/START_DATE',
								END_DATE     		  VARCHAR2(100)    PATH '/XXAS_COST_ALLOCATIONS/END_DATE',
								OBJECT_VERSION_NUMBER NUMBER           PATH '/XXAS_COST_ALLOCATIONS/OBJECT_VERSION_NUMBER'
                            ) ca
                    ) as xxas_cost_allocation_tbl) CA_DETAILS
from per_all_people_f papf
where sysdate between papf.effective_start_date and papf.effective_end_date;

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