/* $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_element_entries_v 
AS
SELECT e.assignment_id
      ,e.element_type_id
      ,e.element_name
      ,e.reporting_name
      ,e.description
      ,e.business_group_id
      ,e.legislation_code
      ,e.input_currency_code
      ,e.output_currency_code
      ,e.classification_id
      ,e.classification_name
      ,e.additional_entry_allowed_flag
      ,e.adjustment_only_flag
      ,e.closed_for_entry_flag
      ,e.indirect_only_flag
      ,e.multiple_entries_allowed_flag
      ,e.post_termination_rule
      ,e.process_in_run_flag
      ,e.processing_type
      ,e.processing_type_meaning
      ,e.standard_link_flag
      ,e.element_link_id
      ,e.element_entry_id
      ,e.effective_start_date
      ,e.effective_end_date
      ,e.cost_allocation_keyflex_id
      ,e.creator_type
      ,e.entry_type
      ,e.creator_id
      ,e.reason
      ,e.creation_date
      ,e.last_update_date
      ,e.date_earned
      ,e.attribute_category
      ,e.attribute1
      ,e.attribute2
      ,e.attribute3
      ,e.attribute4
      ,e.attribute5
      ,e.attribute6
      ,e.attribute7
      ,e.attribute8
      ,e.attribute9
      ,e.attribute10
      ,e.attribute11
      ,e.attribute12
      ,e.attribute13
      ,e.attribute14
      ,e.attribute15
      ,e.attribute16
      ,e.attribute17
      ,e.attribute18
      ,e.attribute19
      ,e.attribute20
      ,e.entry_information_category
      ,e.entry_information1
      ,e.entry_information2
      ,e.entry_information3
      ,e.entry_information4
      ,e.entry_information5
      ,e.entry_information6
      ,e.entry_information7
      ,e.entry_information8
      ,e.entry_information9
      ,e.entry_information10
      ,e.entry_information11
      ,e.entry_information12
      ,e.entry_information13
      ,e.entry_information14
      ,e.entry_information15
      ,e.entry_information16
      ,e.entry_information17
      ,e.entry_information18
      ,e.entry_information19
      ,e.entry_information20
      ,e.entry_information21
      ,e.entry_information22
      ,e.entry_information23
      ,e.entry_information24
      ,e.entry_information25
      ,e.entry_information26
      ,e.entry_information27
      ,e.entry_information28
      ,e.entry_information29
      ,e.entry_information30
      ,extractvalue(e.entries, '/InputValue[1]/INPUT_VALUE_ID') input_value_id1
      ,extractvalue(e.entries, '/InputValue[1]/NAME') input_value_name1
      ,extractvalue(e.entries, '/InputValue[1]/UOM') input_value_uom1
      ,extractvalue(e.entries, '/InputValue[1]/SCREEN_ENTRY_VALUE') entry_value1
      ,extractvalue(e.entries, '/InputValue[2]/INPUT_VALUE_ID') input_value_id2
      ,extractvalue(e.entries, '/InputValue[2]/NAME') input_value_name2
      ,extractvalue(e.entries, '/InputValue[2]/UOM') input_value_uom2
      ,extractvalue(e.entries, '/InputValue[2]/SCREEN_ENTRY_VALUE') entry_value2
      ,extractvalue(e.entries, '/InputValue[3]/INPUT_VALUE_ID') input_value_id3
      ,extractvalue(e.entries, '/InputValue[3]/NAME') input_value_name3
      ,extractvalue(e.entries, '/InputValue[3]/UOM') input_value_uom3
      ,extractvalue(e.entries, '/InputValue[3]/SCREEN_ENTRY_VALUE') entry_value3
      ,extractvalue(e.entries, '/InputValue[4]/INPUT_VALUE_ID') input_value_id4
      ,extractvalue(e.entries, '/InputValue[4]/NAME') input_value_name4
      ,extractvalue(e.entries, '/InputValue[4]/UOM') input_value_uom4
      ,extractvalue(e.entries, '/InputValue[4]/SCREEN_ENTRY_VALUE') entry_value4
      ,extractvalue(e.entries, '/InputValue[5]/INPUT_VALUE_ID') input_value_id5
      ,extractvalue(e.entries, '/InputValue[5]/NAME') input_value_name5
      ,extractvalue(e.entries, '/InputValue[5]/UOM') input_value_uom5
      ,extractvalue(e.entries, '/InputValue[5]/SCREEN_ENTRY_VALUE') entry_value5
      ,extractvalue(e.entries, '/InputValue[6]/INPUT_VALUE_ID') input_value_id6
      ,extractvalue(e.entries, '/InputValue[6]/NAME') input_value_name6
      ,extractvalue(e.entries, '/InputValue[6]/UOM') input_value_uom6
      ,extractvalue(e.entries, '/InputValue[6]/SCREEN_ENTRY_VALUE') entry_value6
      ,extractvalue(e.entries, '/InputValue[7]/INPUT_VALUE_ID') input_value_id7
      ,extractvalue(e.entries, '/InputValue[7]/NAME') input_value_name7
      ,extractvalue(e.entries, '/InputValue[7]/UOM') input_value_uom7
      ,extractvalue(e.entries, '/InputValue[7]/SCREEN_ENTRY_VALUE') entry_value7
      ,extractvalue(e.entries, '/InputValue[8]/INPUT_VALUE_ID') input_value_id8
      ,extractvalue(e.entries, '/InputValue[8]/NAME') input_value_name8
      ,extractvalue(e.entries, '/InputValue[8]/UOM') input_value_uom8
      ,extractvalue(e.entries, '/InputValue[8]/SCREEN_ENTRY_VALUE') entry_value8
      ,extractvalue(e.entries, '/InputValue[9]/INPUT_VALUE_ID') input_value_id9
      ,extractvalue(e.entries, '/InputValue[9]/NAME') input_value_name9
      ,extractvalue(e.entries, '/InputValue[9]/UOM') input_value_uom9
      ,extractvalue(e.entries, '/InputValue[9]/SCREEN_ENTRY_VALUE') entry_value9
      ,extractvalue(e.entries, '/InputValue[10]/INPUT_VALUE_ID') input_value_id10
      ,extractvalue(e.entries, '/InputValue[10]/NAME') input_value_name10
      ,extractvalue(e.entries, '/InputValue[10]/UOM') input_value_uom10
      ,extractvalue(e.entries, '/InputValue[10]/SCREEN_ENTRY_VALUE') entry_value10
      ,extractvalue(e.entries, '/InputValue[11]/INPUT_VALUE_ID') input_value_id11
      ,extractvalue(e.entries, '/InputValue[11]/NAME') input_value_name11
      ,extractvalue(e.entries, '/InputValue[11]/UOM') input_value_uom11
      ,extractvalue(e.entries, '/InputValue[11]/SCREEN_ENTRY_VALUE') entry_value11
      ,extractvalue(e.entries, '/InputValue[12]/INPUT_VALUE_ID') input_value_id12
      ,extractvalue(e.entries, '/InputValue[12]/NAME') input_value_name12
      ,extractvalue(e.entries, '/InputValue[12]/UOM') input_value_uom12
      ,extractvalue(e.entries, '/InputValue[12]/SCREEN_ENTRY_VALUE') entry_value12
      ,extractvalue(e.entries, '/InputValue[13]/INPUT_VALUE_ID') input_value_id13
      ,extractvalue(e.entries, '/InputValue[13]/NAME') input_value_name13
      ,extractvalue(e.entries, '/InputValue[13]/UOM') input_value_uom13
      ,extractvalue(e.entries, '/InputValue[13]/SCREEN_ENTRY_VALUE') entry_value13
      ,extractvalue(e.entries, '/InputValue[14]/INPUT_VALUE_ID') input_value_id14
      ,extractvalue(e.entries, '/InputValue[14]/NAME') input_value_name14
      ,extractvalue(e.entries, '/InputValue[14]/UOM') input_value_uom14
      ,extractvalue(e.entries, '/InputValue[14]/SCREEN_ENTRY_VALUE') entry_value14
      ,extractvalue(e.entries, '/InputValue[15]/INPUT_VALUE_ID') input_value_id15
      ,extractvalue(e.entries, '/InputValue[15]/NAME') input_value_name15
      ,extractvalue(e.entries, '/InputValue[15]/UOM') input_value_uom15
      ,extractvalue(e.entries, '/InputValue[15]/SCREEN_ENTRY_VALUE') entry_value15
FROM   (SELECT pee.assignment_id
              ,pet.element_type_id
              ,pet.element_name
              ,pet.reporting_name
              ,pet.description
              ,pet.business_group_id
              ,pet.legislation_code
              ,pet.input_currency_code
              ,pet.output_currency_code
              ,pet.classification_id
              ,pec.classification_name
              ,pet.additional_entry_allowed_flag
              ,pet.adjustment_only_flag
              ,pet.closed_for_entry_flag
              ,pet.indirect_only_flag
              ,pet.multiple_entries_allowed_flag
              ,pet.post_termination_rule
              ,pet.process_in_run_flag
              ,pet.processing_type
              ,hrl.meaning processing_type_meaning
              ,pet.standard_link_flag
              ,pee.element_link_id
              ,pee.element_entry_id
              ,pee.effective_start_date
              ,pee.effective_end_date
              ,pee.cost_allocation_keyflex_id
              ,pee.creator_type
              ,pee.entry_type
              ,pee.creator_id
              ,pee.reason
              ,pee.creation_date
              ,pee.last_update_date
              ,pee.date_earned
              ,pee.attribute_category
              ,pee.attribute1
              ,pee.attribute2
              ,pee.attribute3
              ,pee.attribute4
              ,pee.attribute5
              ,pee.attribute6
              ,pee.attribute7
              ,pee.attribute8
              ,pee.attribute9
              ,pee.attribute10
              ,pee.attribute11
              ,pee.attribute12
              ,pee.attribute13
              ,pee.attribute14
              ,pee.attribute15
              ,pee.attribute16
              ,pee.attribute17
              ,pee.attribute18
              ,pee.attribute19
              ,pee.attribute20
              ,pee.entry_information_category
              ,pee.entry_information1
              ,pee.entry_information2
              ,pee.entry_information3
              ,pee.entry_information4
              ,pee.entry_information5
              ,pee.entry_information6
              ,pee.entry_information7
              ,pee.entry_information8
              ,pee.entry_information9
              ,pee.entry_information10
              ,pee.entry_information11
              ,pee.entry_information12
              ,pee.entry_information13
              ,pee.entry_information14
              ,pee.entry_information15
              ,pee.entry_information16
              ,pee.entry_information17
              ,pee.entry_information18
              ,pee.entry_information19
              ,pee.entry_information20
              ,pee.entry_information21
              ,pee.entry_information22
              ,pee.entry_information23
              ,pee.entry_information24
              ,pee.entry_information25
              ,pee.entry_information26
              ,pee.entry_information27
              ,pee.entry_information28
              ,pee.entry_information29
              ,pee.entry_information30
              ,(SELECT XMLAgg
                         (XMLElement
                           ("InputValue"
                           ,XMLForest
                             (piv.input_value_id
                             ,piv.name
                             ,piv.uom
                             ,decode
                                (piv.hot_default_flag
                                ,'Y'
                                ,nvl(pev.screen_entry_value, nvl(liv.default_value, piv.default_value))
                                ,pev.screen_entry_value) screen_entry_value
                             )
                           )
                         ORDER BY piv.display_sequence
                         )
                FROM   pay_element_entry_values_f pev
                      ,pay_input_values_f piv
                      ,pay_link_input_values_f liv
                WHERE  pev.element_entry_id = pee.element_entry_id
                AND    pev.input_value_id = piv.input_value_id
                AND    pev.input_value_id = liv.input_value_id
                AND    pee.element_link_id = liv.element_link_id
                AND    pee.effective_start_date BETWEEN
                       pev.effective_start_date AND pev.effective_end_date
                AND    trunc(sysdate) BETWEEN
                       piv.effective_start_date AND piv.effective_end_date
                AND    trunc(sysdate) BETWEEN
                       liv.effective_start_date AND liv.effective_end_date) entries
        FROM   pay_element_entries_f pee
              ,pay_element_types_f_vl pet
              ,pay_element_classifications pec
              ,hr_lookups hrl
        WHERE  pee.element_type_id = pet.element_type_id
        AND    pet.classification_id = pec.classification_id
        AND    hrl.lookup_type = 'PROCESSING_TYPE'
        AND    hrl.lookup_code = pet.processing_type
        AND    trunc(sysdate) BETWEEN
               pet.effective_start_date AND pet.effective_end_date) e;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_ELEMENT_ENTRIES_V'
   ,p_description            => 'Applaud Element Entries');
END;
/

COMMIT;
EXIT;

