/* $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_cost_allocations_v 
AS
SELECT pca.assignment_id
      ,pca.cost_allocation_id
      ,pca.effective_start_date
      ,pca.effective_end_date
      ,pca.cost_allocation_keyflex_id
      ,pca.business_group_id
      ,pca.proportion
      ,pca.creation_date
      ,pca.last_update_date
      ,pca.created_by
      ,pca.last_updated_by
      ,kff.concatenated_segments
      ,kff.id_flex_num
      ,kff.summary_flag
      ,kff.enabled_flag
      ,kff.start_date_active
      ,kff.end_date_active
      ,kff.segment1
      ,kff.segment2
      ,kff.segment3
      ,kff.segment4
      ,kff.segment5
      ,kff.segment6
      ,kff.segment7
      ,kff.segment8
      ,kff.segment9
      ,kff.segment10
      ,kff.segment11
      ,kff.segment12
      ,kff.segment13
      ,kff.segment14
      ,kff.segment15
      ,kff.segment16
      ,kff.segment17
      ,kff.segment18
      ,kff.segment19
      ,kff.segment20
      ,kff.segment21
      ,kff.segment22
      ,kff.segment23
      ,kff.segment24
      ,kff.segment25
      ,kff.segment26
      ,kff.segment27
      ,kff.segment28
      ,kff.segment29
      ,kff.segment30
      ,kffm.*
FROM   pay_cost_allocation_keyflex kff
      ,pay_cost_allocations_f pca
      ,fnd_id_flex_structures fifs
      ,TABLE(xxas_com_flex_pkg.kff_value_set_details
        (801
        ,'COST'
        ,fifs.id_flex_structure_code
        ,'SEGMENT1'
        ,kff.segment1
        ,'SEGMENT2'
        ,kff.segment2
        ,'SEGMENT3'
        ,kff.segment3
        ,'SEGMENT4'
        ,kff.segment4
        ,'SEGMENT5'
        ,kff.segment5
        ,'SEGMENT6'
        ,kff.segment6
        ,'SEGMENT7'
        ,kff.segment7
        ,'SEGMENT8'
        ,kff.segment8
        ,'SEGMENT9'
        ,kff.segment9
        ,'SEGMENT10'
        ,kff.segment10
        ,'SEGMENT11'
        ,kff.segment11
        ,'SEGMENT12'
        ,kff.segment12
        ,'SEGMENT13'
        ,kff.segment13
        ,'SEGMENT14'
        ,kff.segment14
        ,'SEGMENT15'
        ,kff.segment15
        ,'SEGMENT16'
        ,kff.segment16
        ,'SEGMENT17'
        ,kff.segment17
        ,'SEGMENT18'
        ,kff.segment18
        ,'SEGMENT19'
        ,kff.segment19
        ,'SEGMENT20'
        ,kff.segment20
        ,'SEGMENT21'
        ,kff.segment21
        ,'SEGMENT22'
        ,kff.segment22
        ,'SEGMENT23'
        ,kff.segment23
        ,'SEGMENT24'
        ,kff.segment24
        ,'SEGMENT25'
        ,kff.segment25
        ,'SEGMENT26'
        ,kff.segment26
        ,'SEGMENT27'
        ,kff.segment27
        ,'SEGMENT28'
        ,kff.segment28
        ,'SEGMENT29'
        ,kff.segment29
        ,'SEGMENT30'
        ,kff.segment30)) kffm 
WHERE  pca.cost_allocation_keyflex_id = kff.cost_allocation_keyflex_id
AND    kff.id_flex_num                = fifs.id_flex_num
AND    fifs.application_id            = 801
AND    fifs.id_flex_code              = 'COST';

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

COMMIT;
EXIT;

