Showing posts with label HCM. Show all posts
Showing posts with label HCM. Show all posts

Tuesday, 18 January 2022

Oracle HCM Salary Query / Element Entries Query

Query to fetch the salary and other element entries from HCM. Original query owner to a friend of mine.


SELECT DISTINCT
prg.assignment_number,
prg.assignment_id,
pet.base_element_name element_name,
pee.entry_type entry_type,
pee.effective_start_date entry_start_date,
pee.effective_end_date entry_end_date,
pee.multiple_entry_count multiple_entry_count,
pee.element_entry_id element_entry_id,
(
CASE
WHEN base_element_name = 'Hourly Paid' THEN
round(peev.screen_entry_value * 1820, 2)
ELSE
to_number(peev.screen_entry_value)
END
) screen_entry_value,
piv.base_name base_name,
pee.person_id person_id,
papf.person_number person_number
FROM
pay_rel_groups_dn prg,
pay_entry_usages peu,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_vl piv,
pay_element_types_vl pet,
per_all_people_f papf
WHERE
1 = 1
AND papf.person_id = pee.person_id
AND papf.person_number = 123456
--AND prg.assignment_number = 'E123456'
AND prg.payroll_relationship_id = peu.payroll_relationship_id
AND prg.relationship_group_id = peu.payroll_assignment_id
AND peu.element_entry_id = pee.element_entry_id
AND pee.element_entry_id = peev.element_entry_id
AND piv.input_value_id = peev.input_value_id
AND pee.element_type_id = pet.element_type_id
AND piv.base_name = 'Amount'
AND pet.base_element_name IN ( 'Basic Salary', 'Hourly Paid' )
AND trunc(pee.effective_start_date) BETWEEN trunc(piv.effective_start_date) AND trunc(piv.effective_end_date)
AND trunc(pee.effective_start_date) BETWEEN trunc(peev.effective_start_date) AND trunc(peev.effective_end_date)
AND trunc(pee.effective_start_date) BETWEEN trunc(pet.effective_start_date) AND trunc(pet.effective_end_date)
AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)

Tuesday, 3 August 2021

Query Get HCM Payroll Name

Query to get payroll name of the payroll assigned with person number -

select p.person_number, py.payroll_name
from PAY_ASSIGNED_PAYROLLS_DN ap
, pay_PAYROLL_TERMS pt
, PAY_PAY_RELATIONSHIPS_DN pr
, PAY_ALL_PAYROLLS_F py
, PER_ALL_PEOPLE_F p
where ap.payroll_term_id = pt.payroll_term_id
and pt.payroll_relationship_id = pr.payroll_relationship_id
and ap.payroll_id = py.payroll_id
and pr.person_id = p.person_id
and p.person_number = :person_num

Thursday, 25 February 2021

HCM Extract / Fast Formulas scribblings

HCM Extracts -

Add Parent Data Group
- Select the main UE

To add a Child data group -> Right click on the Parent group area and 'Add Child Data Group'
- Define the Child Data group (UE, Filters etc)
- Connect the Data groups

To add a Record -> Righ Click and 'Add Record'
- Define the details, Save and Add Attributes

To add a fast formula column add the columnd as Rule

DEFAULT FOR PER_ASG_REL_DATE_START IS '4712/12/31 00:00:00' (date)

l_rel_date_start = PER_ASG_REL_DATE_START

l_effective_date = GET_PARAMETER_VALUE_DATE('EFFECTIVE_DATE')

--RULE_VALUE is default return for Extract Rule FF Type
return RULE_VALUE

Friday, 19 February 2021

Integrations Lead - Lessons learnt

 Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pres...