Sunday, June 18, 2017

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect