Element Links

SELECT    pldgv.name legislative_data_group
         ,pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,TO_CHAR (pelf.effective_start_date
                  ,'YYYY/MM/DD')
             effective_start_date
         ,TO_CHAR (pelf.effective_end_date
                  ,'YYYY/MM/DD')
             effective_end_date
         ,haou.name legal_entity
         ,ppg.segment1 people_group_segment1
         ,ppg.segment2 people_group_segment2
         ,ppg.segment3 people_group_segment3
         ,pcac.source_sub_type
         ,pcac.segment1 cost_segment1
         ,pcac.segment2 cost_segment2
         ,pcac.segment3 cost_segment3
         ,pcac.segment4 cost_segment4
         ,pcac.segment5 cost_segment5
         ,pcac.segment6 cost_segment6
         ,pcac.segment7 cost_segment7
         ,pcac.segment8 cost_segment8
         ,pcif.costable_type costing_type
         ,pcif.transfer_to_gl_flag
FROM      pay_cost_alloc_accounts pcac
         ,pay_cost_allocations_f pcaf
         ,pay_cost_info_f pcif
         ,per_people_groups ppg
         ,hr_all_organization_units haou
         ,pay_element_criteria pec
         ,pay_element_links_f pelf
         ,pay_ele_classifications_tl pect
         ,per_legislative_data_groups_vl pldgv
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
WHERE         pcac.cost_allocation_record_id(+) = pcaf.cost_allocation_record_id
          AND pelf.effective_start_date BETWEEN pcaf.effective_start_date(+) AND pcaf.effective_end_date(+)
          AND pcaf.source_type(+) = 'EL'
          AND pcaf.source_id(+) = pelf.element_link_id
          AND pelf.effective_start_date BETWEEN pcif.effective_start_date(+) AND pcif.effective_end_date(+)
          AND pcif.source_type(+) = 'EL'
          AND pcif.source_id(+) = pelf.element_link_id
          AND ppg.people_group_id(+) = pec.people_group_id
          AND haou.organization_id(+) = pec.legal_employer_id
          AND pec.element_criteria_id(+) = pelf.element_criteria_id
          AND pelf.element_type_id(+) = pett.element_type_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pldgv.legislative_data_group_id = petf.legislative_data_group_id
          AND petf.effective_start_date = (SELECT  MAX (petfi.effective_start_date)
                                           FROM    pay_element_types_f petfi
                                           WHERE   petfi.element_type_id = petf.element_type_id)
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date




SELECT    pect.classification_name link_classification_name
         ,pett.element_name link_element_name
         ,pelf.element_link_name link_element_link_name
         ,TO_CHAR (plivf.effective_start_date
                  ,'YYYY/MM/DD')
             link_effective_start_date
         ,TO_CHAR (plivf.effective_end_date
                  ,'YYYY/MM/DD')
             link_effective_end_date
         ,pivt.name link_input_name
         ,pcif.costable_type link_costable_type
         ,pcif.costed_flag link_costed_flag
         ,pcif.transfer_to_gl_flag link_transfer_to_gl_flag
FROM      pay_ele_classifications_tl pect
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
         ,pay_element_links_f pelf
         ,pay_input_values_tl pivt
         ,pay_link_input_values_f plivf
         ,pay_cost_info_f pcif
WHERE         pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pcif.effective_start_date BETWEEN petf.effective_start_date AND petf.effective_end_date
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
          AND pett.element_type_id = pelf.element_type_id
          AND pcif.effective_start_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date
          AND pelf.element_link_id = plivf.element_link_id
          AND pivt.language = 'US'
          AND pivt.input_value_id = plivf.input_value_id
          AND pcif.effective_start_date BETWEEN plivf.effective_start_date AND plivf.effective_end_date
          AND plivf.link_input_value_id = pcif.source_id
          AND pcif.source_type = 'LIV'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date



SELECT    pldg.name ele_leg_data_group_name
         ,pect.classification_name ele_classification_name
         ,pett.element_name ele_element_name
         ,pivf.display_sequence ele_display_sequence
         ,pivt.name ele_input_name
         ,pivf.user_enterable_flag ele_user_enterable_flag
         ,pivf.mandatory_flag ele_mandatory_flag
         ,pivf.hot_default_flag ele_hot_default_flag
         ,hr_general.decode_lookup ('UOM'
                                   ,pivf.uom)
             ele_uom
         ,pivf.user_display_flag ele_user_display_flag
         ,pivf.DEFAULT_VALUE ele_default_value
         ,pivf.lookup_type ele_lookup_type
         ,pivf.min_value ele_min_value
         ,pivf.max_value ele_max_value
         ,pivf.value_set_code ele_value_set_code
FROM      pay_input_values_tl pivt
         ,pay_input_values_f pivf
         ,per_legislative_data_groups_vl pldg
         ,pay_ele_classifications_tl pect
         ,pay_element_types_tl pett
         ,pay_element_types_f petf
WHERE         pivt.language = 'US'
          AND pivt.input_value_id = pivf.input_value_id
          AND petf.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
          AND pldg.legislative_data_group_id = petf.legislative_data_group_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pett.language = 'US'
          AND pett.element_type_id = petf.element_type_id
and 1=2
ORDER BY  pect.classification_name
         ,pett.element_name
         ,petf.effective_start_date
         ,pivf.display_sequence