Oracle HRMS Queries

--     Event Groups

SELECT ROWID, event_value_change_id, effective_start_date, effective_end_date,

       event_qualifier_id, datetracked_event_id, default_event,

       business_group_id, legislation_code, object_version_number, created_by,

       creation_date, last_updated_by, last_update_date, last_update_login,

       qualifier_value, from_value, to_value, valid_event, proration_style

  FROM pay_event_value_changes_f   /*WHERE event_qualifier_id = ''

     AND datetracked_event_id =

     AND default_event <> 'Y'

     AND TO_DATE ('', 'DD-MM-YYYY') BETWEEN effective_start_date

                                                  AND effective_end_date

     AND (legislation_code =

          OR (legislation_code IS NULL AND business_group_id = )

          OR (legislation_code IS NULL AND business_group_id IS NULL)

         )

     AND (datetracked_event_id = )

ORDER BY from_value

;

*/

--    Formula Functions

SELECT   ff.NAME, ff.data_type, ff.definition, ffp.sequence_number, ffp.NAME,

         ffp.data_type, ffp.CLASS, ffp.optional, ffc.sequence_number,

         ffc.context_name,

         ffc.data_type    --name, data_type_code, data_type, class, definition

    FROM ff_functions_v ff,

         ff_function_parameters_v ffp,

         ff_function_context_usages_v ffc

   WHERE       /*ff.definition like '% %'

         and*/ ff.function_id = ffp.function_id

          AND ff.function_id = ffc.function_id

ORDER BY ff.definition ASC

SELECT   *

    FROM ff_functions_v ff, ff_function_context_usages_v ffc

   WHERE ff.definition LIKE '%%' AND ff.function_id = ffc.function_id

ORDER BY definition ASC

--    Element Info.

SELECT   pet.element_name, pet.reporting_name, pet.description,

         pet.classification_id, pet.effective_start_date,

         pet.effective_end_date, pet.processing_type,

         pet.post_termination_rule, pet.multiple_entries_allowed_flag,

         pet.additional_entry_allowed_flag, pet.closed_for_entry_flag,

         pet.process_in_run_flag, pet.processing_priority,

         pet.third_party_pay_only_flag, pet.input_currency_code,

         pet.output_currency_code, pet.proration_group_id, piv.NAME, piv.uom,

         piv.display_sequence, piv.mandatory_flag      --, user_enterable_flag

    FROM pay_element_types_f pet, pay_input_values_f piv

   WHERE pet.element_type_id = piv.element_type_id

--and pet.business_group_id =

ORDER BY element_name ASC

SELECT   pet.element_name, pet.reporting_name, pet.description,

                                                      --pet.classification_id,

         (CASE pet.classification_id

             WHEN 122

                THEN 'Information'

             WHEN 123

                THEN 'Earning'

             WHEN 126

                THEN 'Voluntary Deduction'

          END

         ) classification,

         pet.effective_start_date, pet.effective_end_date,

         pet.processing_type, pet.post_termination_rule,

         pet.multiple_entries_allowed_flag, pet.additional_entry_allowed_flag,

         pet.closed_for_entry_flag, pet.process_in_run_flag,

         pet.processing_priority, pet.third_party_pay_only_flag,

         pet.input_currency_code, pet.output_currency_code,

         pet.proration_group_id, piv.NAME, piv.uom, piv.display_sequence,

         piv.mandatory_flag, piv.generate_db_items_flag, piv.hot_default_flag,

         piv.DEFAULT_VALUE, piv.lookup_type, piv.formula_id, piv.min_value,

         piv.max_value, piv.warning_or_error           --, user_enterable_flag

    FROM pay_element_types_f pet, pay_input_values_f piv

   WHERE pet.element_type_id = piv.element_type_id

-- and   pet.business_group_id =

ORDER BY element_name ASC

--    Element Link

SELECT   pet.element_name, pel.costable_type, pel.link_to_all_payrolls_flag,

         ppf.payroll_name, pca.concatenated_segments costing,        

         --pel.cost_allocation_keyflex_id, pel.balancing_keyflex_id,

         pca1.concatenated_segments balancing, pel.standard_link_flag,

         pel.transfer_to_gl_flag

    FROM pay_element_types_f pet,

         pay_element_links_f pel,

         pay_cost_allocation_keyflex pca,

         pay_cost_allocation_keyflex pca1,

         pay_payrolls_f ppf

   WHERE pet.element_type_id = pel.element_type_id

--   AND pet.business_group_id =

     AND pel.payroll_id = ppf.payroll_id

     --and sysdate between pet.effective_start_date and pet.effective_end_date

     AND pel.cost_allocation_keyflex_id = pca.cost_allocation_keyflex_id(+)

     AND pel.balancing_keyflex_id = pca1.cost_allocation_keyflex_id(+)

ORDER BY element_name ASC

--    Absence Types

SELECT pat.NAME, pat.d_absence_category, pat.absence_overlap_flag,

       pat.d_element_name, date_effective, piv.NAME, hours_or_days,

       increasing_or_decreasing_flag

  FROM fnd_lookup_values_vl flv,

       per_abs_attendance_types_v pat,

       pay_input_values_f piv

 WHERE flv.lookup_type = 'ABSENCE_CATEGORY'

   AND flv.lookup_code = pat.absence_category

   AND piv.input_value_id = pat.input_value_id

--    Formula

SELECT ff.formula_name, ft.formula_type_name, ff.formula_text

  FROM ff_formulas_f ff, ff_formula_types ft

 WHERE ff.formula_type_id = ft.formula_type_id

--and business_group_id =

--    Balance

SELECT bttl.balance_name, bttl.reporting_name, hl2.meaning unit_of_measure,

       ettl.element_name element_name, ivtl.NAME input_value_name,

       hl.meaning add_or_subtract, pdb.dimension_description

  FROM pay_balance_feeds_f bf,

       pay_balance_types bt,

       pay_balance_types_tl bttl,

       pay_input_values_f iv,

       pay_input_values_f_tl ivtl,

       pay_element_types_f et,

       pay_element_types_f_tl ettl,

       pay_element_classifications ec,

       pay_element_classifications_tl ectl,

       hr_lookups hl,

       hr_lookups hl2,

       pay_defined_balances_v2 pdb

 --    fnd_sessions ses

WHERE  ec.classification_id = ectl.classification_id

   AND ectl.LANGUAGE = USERENV ('LANG')

   AND et.element_type_id = ettl.element_type_id

   AND ettl.LANGUAGE = USERENV ('LANG')

   AND iv.input_value_id = ivtl.input_value_id

   AND ivtl.LANGUAGE = USERENV ('LANG')

   AND bt.balance_type_id = bttl.balance_type_id

   AND bttl.LANGUAGE = USERENV ('LANG')

   AND bt.balance_type_id = bf.balance_type_id

   AND bt.balance_type_id = pdb.balance_type_id

   AND iv.input_value_id = bf.input_value_id

   AND et.element_type_id = iv.element_type_id

   AND ec.classification_id = et.classification_id

   AND hl.lookup_type = 'ADD_SUBTRACT'

   AND hl.lookup_code = bf.scale

   AND hl2.lookup_type = 'UNITS'

   AND hl2.lookup_code = iv.uom