/* $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_pg_key_ff_v
AS
SELECT  ppg.people_group_id
       ,ppg.id_flex_num
       ,ppg.segment1             pg_segment1
       ,pg_tbl.segment1_value    pg_segment1_value
       ,pg_tbl.segment1_meaning  pg_segment1_meaning
       ,ppg.segment2             pg_segment2
       ,pg_tbl.segment2_value    pg_segment2_value
       ,pg_tbl.segment2_meaning  pg_segment2_meaning
       ,ppg.segment3             pg_segment3
       ,pg_tbl.segment3_value    pg_segment3_value
       ,pg_tbl.segment3_meaning  pg_segment3_meaning
       ,ppg.segment4             pg_segment4
       ,pg_tbl.segment4_value    pg_segment4_value
       ,pg_tbl.segment4_meaning  pg_segment4_meaning
       ,ppg.segment5             pg_segment5
       ,pg_tbl.segment5_value    pg_segment5_value
       ,pg_tbl.segment5_meaning  pg_segment5_meaning
       ,ppg.segment6             pg_segment6
       ,pg_tbl.segment6_value    pg_segment6_value
       ,pg_tbl.segment6_meaning  pg_segment6_meaning
       ,ppg.segment7             pg_segment7
       ,pg_tbl.segment7_value    pg_segment7_value
       ,pg_tbl.segment7_meaning  pg_segment7_meaning
       ,ppg.segment8             pg_segment8
       ,pg_tbl.segment8_value    pg_segment8_value
       ,pg_tbl.segment8_meaning  pg_segment8_meaning
       ,ppg.segment9             pg_segment9
       ,pg_tbl.segment9_value    pg_segment9_value
       ,pg_tbl.segment9_meaning  pg_segment9_meaning
       ,ppg.segment10            pg_segment10
       ,pg_tbl.segment10_value   pg_segment10_value
       ,pg_tbl.segment10_meaning pg_segment10_meaning
       ,ppg.segment11            pg_segment11
       ,pg_tbl.segment11_value   pg_segment11_value
       ,pg_tbl.segment11_meaning pg_segment11_meaning
       ,ppg.segment12            pg_segment12
       ,pg_tbl.segment12_value   pg_segment12_value
       ,pg_tbl.segment12_meaning pg_segment12_meaning
       ,ppg.segment13            pg_segment13
       ,pg_tbl.segment13_value   pg_segment13_value
       ,pg_tbl.segment13_meaning pg_segment13_meaning
       ,ppg.segment14            pg_segment14
       ,pg_tbl.segment14_value   pg_segment14_value
       ,pg_tbl.segment14_meaning pg_segment14_meaning
       ,ppg.segment15            pg_segment15
       ,pg_tbl.segment15_value   pg_segment15_value
       ,pg_tbl.segment15_meaning pg_segment15_meaning
       ,ppg.segment16            pg_segment16
       ,pg_tbl.segment16_value   pg_segment16_value
       ,pg_tbl.segment16_meaning pg_segment16_meaning
       ,ppg.segment17            pg_segment17
       ,pg_tbl.segment17_value   pg_segment17_value
       ,pg_tbl.segment17_meaning pg_segment17_meaning
       ,ppg.segment18            pg_segment18
       ,pg_tbl.segment18_value   pg_segment18_value
       ,pg_tbl.segment18_meaning pg_segment18_meaning
       ,ppg.segment19            pg_segment19
       ,pg_tbl.segment19_value   pg_segment19_value
       ,pg_tbl.segment19_meaning pg_segment19_meaning
       ,ppg.segment20            pg_segment20
       ,pg_tbl.segment20_value   pg_segment20_value
       ,pg_tbl.segment20_meaning pg_segment20_meaning
       ,ppg.segment21            pg_segment21
       ,pg_tbl.segment21_value   pg_segment21_value
       ,pg_tbl.segment21_meaning pg_segment21_meaning
       ,ppg.segment22            pg_segment22
       ,pg_tbl.segment22_value   pg_segment22_value
       ,pg_tbl.segment22_meaning pg_segment22_meaning
       ,ppg.segment23            pg_segment23
       ,pg_tbl.segment23_value   pg_segment23_value
       ,pg_tbl.segment23_meaning pg_segment23_meaning
       ,ppg.segment24            pg_segment24
       ,pg_tbl.segment24_value   pg_segment24_value
       ,pg_tbl.segment24_meaning pg_segment24_meaning
       ,ppg.segment25            pg_segment25
       ,pg_tbl.segment25_value   pg_segment25_value
       ,pg_tbl.segment25_meaning pg_segment25_meaning
       ,ppg.segment26            pg_segment26
       ,pg_tbl.segment26_value   pg_segment26_value
       ,pg_tbl.segment26_meaning pg_segment26_meaning
       ,ppg.segment27            pg_segment27
       ,pg_tbl.segment27_value   pg_segment27_value
       ,pg_tbl.segment27_meaning pg_segment27_meaning
       ,ppg.segment28            pg_segment28
       ,pg_tbl.segment28_value   pg_segment28_value
       ,pg_tbl.segment28_meaning pg_segment28_meaning
       ,ppg.segment29            pg_segment29
       ,pg_tbl.segment29_value   pg_segment29_value
       ,pg_tbl.segment29_meaning pg_segment29_meaning
       ,ppg.segment30            pg_segment30
       ,pg_tbl.segment30_value   pg_segment30_value
       ,pg_tbl.segment30_meaning pg_segment30_meaning
FROM    pay_people_groups      ppg
       ,fnd_id_flex_structures fifs 
       ,table(xxas_com_flex_pkg.kff_value_set_details
                (xxas_util_pkg.get_app_id('PAY')
                ,'GRP'
                ,fifs.id_flex_structure_code
                ,'SEGMENT1'
                ,ppg.segment1
                ,'SEGMENT2'
                ,ppg.segment2
                ,'SEGMENT3'
                ,ppg.segment3
                ,'SEGMENT4'
                ,ppg.segment4
                ,'SEGMENT5'
                ,ppg.segment5
                ,'SEGMENT6'
                ,ppg.segment6
                ,'SEGMENT7'
                ,ppg.segment7
                ,'SEGMENT8'
                ,ppg.segment8
                ,'SEGMENT9'
                ,ppg.segment9
                ,'SEGMENT10'
                ,ppg.segment10
                ,'SEGMENT11'
                ,ppg.segment11
                ,'SEGMENT12'
                ,ppg.segment12
                ,'SEGMENT13'
                ,ppg.segment13
                ,'SEGMENT14'
                ,ppg.segment14
                ,'SEGMENT15'
                ,ppg.segment15
                ,'SEGMENT16'
                ,ppg.segment16
                ,'SEGMENT17'
                ,ppg.segment17
                ,'SEGMENT18'
                ,ppg.segment18
                ,'SEGMENT19'
                ,ppg.segment19
                ,'SEGMENT20'
                ,ppg.segment20
                ,'SEGMENT21'
                ,ppg.segment21
                ,'SEGMENT22'
                ,ppg.segment22
                ,'SEGMENT23'
                ,ppg.segment23
                ,'SEGMENT24'
                ,ppg.segment24
                ,'SEGMENT25'
                ,ppg.segment25
                ,'SEGMENT26'
                ,ppg.segment26
                ,'SEGMENT27'
                ,ppg.segment27
                ,'SEGMENT28'
                ,ppg.segment28
                ,'SEGMENT29'
                ,ppg.segment29
                ,'SEGMENT30'
                ,ppg.segment30)) pg_tbl
WHERE   ppg.id_flex_num     = fifs.id_flex_num
AND     fifs.application_id = xxas_util_pkg.get_app_id('PAY')
AND     fifs.id_flex_code   = 'GRP';

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

COMMIT;
EXIT;

