/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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_st_key_ff_v
AS
SELECT  hsck.soft_coding_keyflex_id
       ,hsck.id_flex_num
       ,hsck.segment1              st_segment1
       ,hsck_tbl.segment1_value    st_segment1_value
       ,hsck_tbl.segment1_meaning  st_segment1_meaning
       ,hsck.segment2              st_segment2
       ,hsck_tbl.segment2_value    st_segment2_value
       ,hsck_tbl.segment2_meaning  st_segment2_meaning
       ,hsck.segment3              st_segment3
       ,hsck_tbl.segment3_value    st_segment3_value
       ,hsck_tbl.segment3_meaning  st_segment3_meaning
       ,hsck.segment4              st_segment4
       ,hsck_tbl.segment4_value    st_segment4_value
       ,hsck_tbl.segment4_meaning  st_segment4_meaning
       ,hsck.segment5              st_segment5
       ,hsck_tbl.segment5_value    st_segment5_value
       ,hsck_tbl.segment5_meaning  st_segment5_meaning
       ,hsck.segment6              st_segment6
       ,hsck_tbl.segment6_value    st_segment6_value
       ,hsck_tbl.segment6_meaning  st_segment6_meaning
       ,hsck.segment7              st_segment7
       ,hsck_tbl.segment7_value    st_segment7_value
       ,hsck_tbl.segment7_meaning  st_segment7_meaning
       ,hsck.segment8              st_segment8
       ,hsck_tbl.segment8_value    st_segment8_value
       ,hsck_tbl.segment8_meaning  st_segment8_meaning
       ,hsck.segment9              st_segment9
       ,hsck_tbl.segment9_value    st_segment9_value
       ,hsck_tbl.segment9_meaning  st_segment9_meaning
       ,hsck.segment10             st_segment10
       ,hsck_tbl.segment10_value   st_segment10_value
       ,hsck_tbl.segment10_meaning st_segment10_meaning
       ,hsck.segment11             st_segment11
       ,hsck_tbl.segment11_value   st_segment11_value
       ,hsck_tbl.segment11_meaning st_segment11_meaning
       ,hsck.segment12             st_segment12
       ,hsck_tbl.segment12_value   st_segment12_value
       ,hsck_tbl.segment12_meaning st_segment12_meaning
       ,hsck.segment13             st_segment13
       ,hsck_tbl.segment13_value   st_segment13_value
       ,hsck_tbl.segment13_meaning st_segment13_meaning
       ,hsck.segment14             st_segment14
       ,hsck_tbl.segment14_value   st_segment14_value
       ,hsck_tbl.segment14_meaning st_segment14_meaning
       ,hsck.segment15             st_segment15
       ,hsck_tbl.segment15_value   st_segment15_value
       ,hsck_tbl.segment15_meaning st_segment15_meaning
       ,hsck.segment16             st_segment16
       ,hsck_tbl.segment16_value   st_segment16_value
       ,hsck_tbl.segment16_meaning st_segment16_meaning
       ,hsck.segment17             st_segment17
       ,hsck_tbl.segment17_value   st_segment17_value
       ,hsck_tbl.segment17_meaning st_segment17_meaning
       ,hsck.segment18             st_segment18
       ,hsck_tbl.segment18_value   st_segment18_value
       ,hsck_tbl.segment18_meaning st_segment18_meaning
       ,hsck.segment19             st_segment19
       ,hsck_tbl.segment19_value   st_segment19_value
       ,hsck_tbl.segment19_meaning st_segment19_meaning
       ,hsck.segment20             st_segment20
       ,hsck_tbl.segment20_value   st_segment20_value
       ,hsck_tbl.segment20_meaning st_segment20_meaning
       ,hsck.segment21             st_segment21
       ,hsck_tbl.segment21_value   st_segment21_value
       ,hsck_tbl.segment21_meaning st_segment21_meaning
       ,hsck.segment22             st_segment22
       ,hsck_tbl.segment22_value   st_segment22_value
       ,hsck_tbl.segment22_meaning st_segment22_meaning
       ,hsck.segment23             st_segment23
       ,hsck_tbl.segment23_value   st_segment23_value
       ,hsck_tbl.segment23_meaning st_segment23_meaning
       ,hsck.segment24             st_segment24
       ,hsck_tbl.segment24_value   st_segment24_value
       ,hsck_tbl.segment24_meaning st_segment24_meaning
       ,hsck.segment25             st_segment25
       ,hsck_tbl.segment25_value   st_segment25_value
       ,hsck_tbl.segment25_meaning st_segment25_meaning
       ,hsck.segment26             st_segment26
       ,hsck_tbl.segment26_value   st_segment26_value
       ,hsck_tbl.segment26_meaning st_segment26_meaning
       ,hsck.segment27             st_segment27
       ,hsck_tbl.segment27_value   st_segment27_value
       ,hsck_tbl.segment27_meaning st_segment27_meaning
       ,hsck.segment28             st_segment28
       ,hsck_tbl.segment28_value   st_segment28_value
       ,hsck_tbl.segment28_meaning st_segment28_meaning
       ,hsck.segment29             st_segment29
       ,hsck_tbl.segment29_value   st_segment29_value
       ,hsck_tbl.segment29_meaning st_segment29_meaning
       ,hsck.segment30             st_segment30
       ,hsck_tbl.segment30_value   st_segment30_value
       ,hsck_tbl.segment30_meaning st_segment30_meaning
FROM    hr_soft_coding_keyflex hsck
       ,fnd_id_flex_structures fifs 
       ,table(xxas_com_flex_pkg.kff_value_set_details
                (xxas_util_pkg.get_app_id('PER')
                ,'SCL'
                ,fifs.id_flex_structure_code
                ,'SEGMENT1'
                ,hsck.segment1
                ,'SEGMENT2'
                ,hsck.segment2
                ,'SEGMENT3'
                ,hsck.segment3
                ,'SEGMENT4'
                ,hsck.segment4
                ,'SEGMENT5'
                ,hsck.segment5
                ,'SEGMENT6'
                ,hsck.segment6
                ,'SEGMENT7'
                ,hsck.segment7
                ,'SEGMENT8'
                ,hsck.segment8
                ,'SEGMENT9'
                ,hsck.segment9
                ,'SEGMENT10'
                ,hsck.segment10
                ,'SEGMENT11'
                ,hsck.segment11
                ,'SEGMENT12'
                ,hsck.segment12
                ,'SEGMENT13'
                ,hsck.segment13
                ,'SEGMENT14'
                ,hsck.segment14
                ,'SEGMENT15'
                ,hsck.segment15
                ,'SEGMENT16'
                ,hsck.segment16
                ,'SEGMENT17'
                ,hsck.segment17
                ,'SEGMENT18'
                ,hsck.segment18
                ,'SEGMENT19'
                ,hsck.segment19
                ,'SEGMENT20'
                ,hsck.segment20
                ,'SEGMENT21'
                ,hsck.segment21
                ,'SEGMENT22'
                ,hsck.segment22
                ,'SEGMENT23'
                ,hsck.segment23
                ,'SEGMENT24'
                ,hsck.segment24
                ,'SEGMENT25'
                ,hsck.segment25
                ,'SEGMENT26'
                ,hsck.segment26
                ,'SEGMENT27'
                ,hsck.segment27
                ,'SEGMENT28'
                ,hsck.segment28
                ,'SEGMENT29'
                ,hsck.segment29
                ,'SEGMENT30'
                ,hsck.segment30)) hsck_tbl
WHERE   hsck.id_flex_num    = fifs.id_flex_num
AND     fifs.application_id = xxas_util_pkg.get_app_id('PER')
AND     fifs.id_flex_code   = 'SCL';

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_ST_KEY_FF_V'
   ,p_description            => 'Applaud Statutory Key Flex Field View');
END;
/

COMMIT;
EXIT;

