There are times when we just need a quick query to count number of positions created in HCM against a department. Below is a sample SQL which can be handy in such situations:
select *
from hr_all_positions_f hapf
where hapf.organization_id IN (select distinct organization_id
from HR_ORGANIZATION_UNITS_F_TL houft
where (houft.name LIKE 'Test%'
OR houft.name LIKE 'Abc%'
)
)
Starting 23C, a new HDL object has been added to close any pending actions for benefits. The certification type is validated against a lookup called – BEN_ENRT_CTFN_TYP
Below is a sample file to close any pending certification actions against “POC”:
METADATA|CloseEnrollmentCertification|PersonNumber|ParticipantFirstName|ParticipantLastName|CertificationType|ProgramName|PlanName|OptionName|ReceivedDate|DeniedDate|SourceSystemId|SourceSystemOwner
MERGE|CloseEnrollmentCertification|12|First Name 1|Last Name 1|POC|Test Benefits Program|Test Optical|Employee + 1|2023/02/01||12_First Name 1_Last Name 1_POC|HRC_SQLLOADER
MERGE|CloseEnrollmentCertification|13|First Name 2|Last Name 2|POC|Test Benefits Program|Test Medical|Employee + 1||2023/02/01|12_First Name 2_Last Name 2_POC|HRC_SQLLOADER
There are requirements where customers want to display Honors in “Display Name” if First Name is blank. This where the conditional name formatting functionality of Oracle HCM helps to achieve desired results.
Let us assume, we want display name in below format:
NVL(First Name, Honors), Name Information 1
Follow below steps to achieve this requirement:
Search for Task “Manage Person Name Formats” from setup and maintenance:
2. Search the name format you want to modify/ or create a new one if you want to have a new name format:
3. Click on edit and Choose Conditional Name Formatting component:
4. Scroll down and supply the values in “Preview Name Format” section:
5. As can be seen, first name is blank so Formatted Name is displayed as – Honors Name_Information_1
6. If you supply a value for first name:
Formatted Name is displayed as – Test Name_Information_1 (Honors is ignored as First Name is present).
When creating a new announcement (Tools -> Announcements) , user has an option to upload images from local directory or a link can be given to a file uploaded in content server.
From local directory:
For content server link:
One needs to follow below steps in order to upload the image file to content server:
Login into content server. Suffix your pod url with /cs:
https://pod.fa.oracle.com/cs
2. Do a new check-in:
3. Click on “Check In”
4. Once the file is successfully checked-in. Click on “New Folio” Under “Content Management”
Below sample queries can be used to extract count of workers (Active/ Inactive) in HCM:
Query 1:
select count(distinct person_number)
from per_all_people_f papf
where 1=1
and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
Query 2: Find count workers not having basic assignment details:
select (distinct person_number)
from per_all_people_f papf
where 1=1
and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
and person_number not in (SELECT papf.person_number
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_jobs_f_tl pjft,
hr_organization_units_f_tl hauft,
per_periods_of_service ppos
WHERE paam.person_id = ppnf.person_id
AND papf.person_id = ppnf.person_id
AND papf.person_id = paam.person_id
AND paam.job_id = pjft.job_id (+)
AND pjft.language(+) = 'US'
AND hauft.language (+) = 'US'
- -and papf.person_number = '414'
AND hauft.organization_id (+) = paam.organization_id
AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND trunc(sysdate) BETWEEN hauft.effective_start_date(+) AND hauft.effective_end_date(+)
AND trunc(sysdate) BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)
AND paam.effective_latest_change = 'Y'
AND paam.primary_flag = 'Y'
AND ppnf.name_type = 'GLOBAL'
AND paam.period_of_service_id = ppos.period_of_service_id
AND ppos.date_start = (select max(ppos2.date_start) from per_periods_of_service ppos2
where ppos2.person_id = ppos.person_id
and ppos2.primary_flag = 'Y')
)
On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative.
Below SQL can be used to identify such persons:
SELECT paam.person_id, hapfv.name
FROM per_all_assignments_m paam
,hr_all_positions_f_vl hapfv
WHERE paam.position_id = hapfv.position_id
AND paam.assignment_type NOT LIKE '%T'
AND paam.assignment_status_type NOT LIKE 'INACTIVE'
AND TRUNC(SYSDATE) BETWEEN hapfv.effective_start_date AND hapfv.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP BY paam.person_id, hapfv.name
HAVING COUNT(*) > 1
Oracle HCM provides lots of pre-defined messages which are triggered/displayed in UI on various actions. For example, if you are trying to hire a new person on a position which doesn’t have the vacant FTE, system will throw below warning message:
However, in this particular scenario, business wanted to see an Error message instead of Warning. This can be achieved easily, using “Manage Messages” task under “Setup and Maintenance”.
However, before using Manage messages task, one should know the message number or message name to search. The message name/ message number can be found by running a quick query on fnd_messages_vl:
select * from fnd_messages_vl
where upper(message_text) like 'THE%POSITION%HAS%FUTURE%INCU%'
This will return you the list of all messages which matches the given text:
One we are looking to modify is – 1532361/PER_EMCOR_NO_HC_NO_FTE_FUTURE.
Note it down and open Manage Messages task. Enter the message number and click on search:
Click on edit icon to edit the message:
On edit screen, you can change message text as well as message type:
Change the type to Error and save the changes:
Please note that there are certain messages on UI which are not available in manage messages. To edit those messages, User Interface Text tool can be utilized:
There are different requirements where one wants to get number of active/inactive work relationships in a legal entity.
Below SQL query can be run to get these counts:
select ple.name, count(ppos.period_of_Service_id) number_of_wrs
from per_legal_employers ple
,per_periods_of_service ppos
where ppos.legal_entity_id = ple.organization_id
group by ple.name
order by 1
