• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:December 24, 2021
  • Reading time:4 mins read
You are currently viewing SQL Query to extract Element Configuration Information
SQL Query to extract Element Configuration Information
- SQL Query to extract Element Configuration Information
Total Visits: 47

In this article, we will look into extracting the Element Configuration information. Elements are the basic building blocks for Payroll and they will also be used in other modules like Core HR, Benefits, Compensation, Absence, and Time and Labor.

Do you know how to extract the Element Configuration information like Element Name, ID, Classification, Legislation, Processing Level, Gross Up, Multiple Entries Allowed, Retro, Proration, Skip Rules, Validation Formulas and Processing priority, etc?

SQL Query:

SELECT  pett.element_type_id
       ,petf.base_element_name
       ,pett.element_name
       ,pett.reporting_name
       ,TO_CHAR(petf.effective_start_date, 'YYYY/MM/DD') effective_start_date
       ,TO_CHAR(petf.effective_end_date, 'YYYY/MM/DD') effective_end_date
       ,pett.description
       ,pec.base_classification_name prim_base_classification
       ,pect.classification_name prim_classification_name
       ,pec_sec.base_classification_name sec_base_classification
       ,pect_sec.classification_name sec_classification_name
       ,pldgv.name legislative_data_group
       ,pldgv.legislation_code
       ,petf.standard_link_flag 
       ,DECODE(petf.standard_link_flag, 'Y', 'Standard', NULL) category
       ,petf.processing_type
       ,hr_general.decode_lookup('PAY_PROCESSING_TYPE',petf.processing_type) processing_type_description
       ,petf.use_at_rel_level
       ,petf.use_at_term_level
       ,petf.use_at_asg_level
       ,petf.adjustment_only_flag
       ,petf.closed_for_entry_flag
       ,petf.indirect_only_flag accept_result_frm_formula_only
       ,CASE WHEN (petf.processing_type = 'R' AND petf.standard_link_flag = 'Y')
              THEN 'Y'
            ELSE 'N' 
        END       automatic_entry
       ,petf.multiple_entries_allowed_flag
       ,petf.additional_entry_allowed_flag
       ,petf.process_in_run_flag
       ,petf.once_each_period_flag
       ,petf.processing_priority
       ,ptd_start.short_name earliest_entry
       ,ptd_start.definition_name earliest_entry_description
       ,ptd_end.short_name latest_entry
       ,ptd_end.definition_name latest_entry_description
       ,petf.grossup_flag
       ,petf.process_mode
       ,hr_general.decode_lookup('PAY_RUN_METHOD', petf.process_mode) process_mode_description
       ,petf.recalc_event_group_id
       ,(SELECT base_event_group_name
         FROM   pay_event_groups
         WHERE  event_group_id = petf.recalc_event_group_id
        ) retro_calc_group_name
       ,petf.proration_group_id
       ,(SELECT base_event_group_name
         FROM   pay_event_groups
         WHERE  event_group_id = petf.proration_group_id
        ) proration_group_name
       ,petf.proration_formula_id
       ,(SELECT base_formula_name
         FROM   ff_formulas_vl 
         WHERE  formula_id = petf.proration_formula_id
        ) proration_formula
       ,petf.input_currency_code
       ,petf.output_currency_code
       ,petf.formula_id skip_formula_id
       ,(SELECT base_formula_name
         FROM   ff_formulas_vl 
         WHERE  formula_id = petf.formula_id
        ) skip_rule       
       ,petf.defaulting_formula_id
       ,(SELECT base_formula_name
         FROM   ff_formulas_vl 
         WHERE  formula_id = petf.defaulting_formula_id
        ) default_formula
       ,petf.calculation_formula_id
       ,(SELECT base_formula_name
         FROM   ff_formulas_vl 
         WHERE  formula_id = petf.calculation_formula_id
        ) calculation_formula
       ,petf.validation_formula_id
       ,(SELECT base_formula_name
         FROM   ff_formulas_vl 
         WHERE  formula_id = petf.validation_formula_id
        ) validation_formula
       ,petf.validation_override_message
       ,petf.iterative_flag
       ,petf.iterative_formula_id
       ,(SELECT base_formula_name
         FROM   ff_formulas_vl 
         WHERE  formula_id = petf.iterative_formula_id
        ) iterative_formula
       ,petf.iterative_priority
       ,petf.deduction_type_id
       ,(SELECT deduction_code
         FROM   pay_deduction_types_vl
         WHERE  deduction_type_id = petf.deduction_type_id
        ) deduction_type
       ,petf.deduction_or_exemption
       
FROM    pay_element_types_f petf
       ,pay_element_types_tl pett
       ,pay_ele_classifications pec
       ,pay_ele_classifications pec_sec
       ,pay_ele_classifications_tl pect
       ,pay_ele_classifications_tl pect_sec
       ,per_legislative_data_groups_vl pldgv
       ,pay_time_definitions ptd_start
       ,pay_time_definitions ptd_end
       
WHERE   petf.element_type_id = pett.element_type_id
AND     pett.language = USERENV ('LANG')
AND     petf.classification_id = pec.base_classification_id
AND     pec.legislation_code = pldgv.legislation_code
AND     pec.classification_id = pect.classification_id
AND     pect.language = USERENV ('LANG')
AND     petf.secondary_classification_id = pec_sec.classification_id (+)
AND     petf.secondary_classification_id = pect_sec.classification_id (+)
AND     pect_sec.language (+) = USERENV ('LANG')

AND     petf.legislative_data_group_id = pldgv.legislative_data_group_id(+)
AND     petf.starting_time_def_id = ptd_start.time_definition_id(+)
AND     petf.ending_time_def_id = ptd_end.time_definition_id(+)
AND     TRUNC(SYSDATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
ORDER BY petf.base_element_name
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.