Search for:
Aug 28 2022
Purge Worker/Person Records from Test Environment

Oracle provides a process Purge Person Data in Test Environments to remove Person/Contact/Worker records from a test environment of Oracle HCM. Please note that HDL for core worker object doesn’t support DELETE, so there is no other option to purge the Person/Contact/Worker information from Oracle HCM once a record is created. In this case, Purge Person Data in Test Environments is definitely a useful utility which helps to purge person related data from a test environment.

This process can’t be run in Oracle HCM Production.

Before 21D, the process required an additional step to get a key to enable to process in Test environments but from 21D onwards the process is enabled by default in all test environments.

You can find more details related to this process on below link:

https://docs.oracle.com/en/cloud/saas/human-resources/22c/fahdl/enable-the-purge-person-data-in-test-environments-process.html#s20065832

Below is the list of tables which gets purged once the process completes:

https://docs.oracle.com/en/cloud/saas/human-resources/22c/fahdl/tables-purged-by-the-purge-person-data-in-test-environments.html#s20065826

To run the process:

  1. Navigate to Tools -> Scheduled Process
  2. Click on schedule new process –> Search for Purge Person Data in Test Environments
  3. Supply the parameters
Make sure Save is set to Y to purge the records from DB.

Sample Person SQL’s:

To remove only Contact Person records:

SELECT DISTINCT papf.person_id FROM per_all_people_f papf WHERE 1=1 AND NOT EXISTS (SELECT 1 FROM per_periods_of_service ppos where ppos.person_id = papf.person_id)

To remove all person records:

SELECT DISTINCT papf.person_id FROM per_all_people_f papf

Use below SQL to monitor the progress of entries in each table:

SELECT a.*
FROM
(
SELECT 'ANC_PER_ABS_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ABS_ENTRIES
UNION
SELECT 'ANC_PER_ABS_ENTRY_DTLS' table_name, count(*) rowcount FROM ANC_PER_ABS_ENTRY_DTLS
UNION
SELECT 'ANC_PER_ABS_MATERNITY' table_name, count(*) rowcount FROM ANC_PER_ABS_MATERNITY
UNION
SELECT 'ANC_PER_PLAN_ENROLLMENT' table_name, count(*) rowcount FROM ANC_PER_PLAN_ENROLLMENT
UNION
SELECT 'ANC_PER_ABS_PLAN_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ABS_PLAN_ENTRIES
UNION
SELECT 'ANC_PER_ACCRUAL_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ACCRUAL_ENTRIES
UNION
SELECT 'ANC_PER_ACRL_ENTRY_DTLS' table_name, count(*) rowcount FROM ANC_PER_ACRL_ENTRY_DTLS
UNION
SELECT 'CMP_SALARY' table_name, count(*) rowcount FROM CMP_SALARY
UNION
SELECT 'PAY_ASSIGNED_PAYROLLS_DN' table_name, count(*) rowcount FROM PAY_ASSIGNED_PAYROLLS_DN
UNION
SELECT 'PAY_ASSIGNED_PAYROLLS_F' table_name, count(*) rowcount FROM PAY_ASSIGNED_PAYROLLS_F
UNION
SELECT 'PER_ADDRESSES_F' table_name, count(*) rowcount FROM PER_ADDRESSES_F
UNION
SELECT 'PER_ALL_ASSIGNMENTS_M' table_name, count(*) rowcount FROM PER_ALL_ASSIGNMENTS_M
UNION
SELECT 'PER_ALL_PEOPLE_F' table_name, count(*) rowcount FROM PER_ALL_PEOPLE_F
UNION
SELECT 'PER_ASSIGN_WORK_MEASURES_F' table_name, count(*) rowcount FROM PER_ASSIGN_WORK_MEASURES_F
UNION
SELECT 'PER_CITIZENSHIPS' table_name, count(*) rowcount FROM PER_CITIZENSHIPS
UNION
SELECT 'PER_DRIVERS_LICENSES' table_name, count(*) rowcount FROM PER_DRIVERS_LICENSES
UNION
SELECT 'PER_EMAIL_ADDRESSES' table_name, count(*) rowcount FROM PER_EMAIL_ADDRESSES
UNION
SELECT 'PER_ETHNICITIES' table_name, count(*) rowcount FROM PER_ETHNICITIES
UNION
SELECT 'PER_NATIONAL_IDENTIFIERS' table_name, count(*) rowcount FROM PER_NATIONAL_IDENTIFIERS
UNION
SELECT 'PER_PASSPORTS' table_name, count(*) rowcount FROM PER_PASSPORTS
UNION
SELECT 'PER_PEOPLE_LEGISLATIVE_F' table_name, count(*) rowcount FROM PER_PEOPLE_LEGISLATIVE_F
UNION
SELECT 'PER_PERIODS_OF_SERVICE' table_name, count(*) rowcount FROM PER_PERIODS_OF_SERVICE
UNION
SELECT 'PER_PERSON_ADDR_USAGES_F' table_name, count(*) rowcount FROM PER_PERSON_ADDR_USAGES_F
UNION
SELECT 'PER_PERSON_NAMES_F' table_name, count(*) rowcount FROM PER_PERSON_NAMES_F
UNION
SELECT 'PER_PERSON_TYPE_USAGES_M' table_name, count(*) rowcount FROM PER_PERSON_TYPE_USAGES_M
UNION
SELECT 'PER_PERSONS' table_name, count(*) rowcount FROM PER_PERSONS
UNION
SELECT 'PER_PHONES' table_name, count(*) rowcount FROM PER_PHONES
UNION
SELECT 'PER_RELIGIONS' table_name, count(*) rowcount FROM PER_RELIGIONS
UNION
SELECT 'PER_VISAS_PERMITS_F' table_name, count(*) rowcount FROM PER_VISAS_PERMITS_F
UNION
SELECT 'PAY_ELEMENT_ENTRIES_F' table_name, count(*) rowcount FROM PAY_ELEMENT_ENTRIES_F
UNION
SELECT 'PAY_ELEMENT_ENTRY_VALUES_F' table_name, count(*) rowcount FROM PAY_ELEMENT_ENTRY_VALUES_F
UNION
SELECT 'PER_WORKING_HOUR_PATTERNS_F' table_name, count(*) rowcount FROM PER_WORKING_HOUR_PATTERNS_F
UNION
SELECT 'PER_ASSIGNMENT_EXTRA_INFO_M' table_name, count(*) rowcount FROM PER_ASSIGNMENT_EXTRA_INFO_M
UNION
SELECT 'PER_ASSIGNMENT_SUPERVISORS_F' table_name, count(*) rowcount FROM PER_ASSIGNMENT_SUPERVISORS_F
UNION
SELECT 'PER_PEOPLE_EXTRA_INFO_F' table_name, count(*) rowcount FROM PER_PEOPLE_EXTRA_INFO_F
UNION
SELECT 'HR_DOCUMENTS_OF_RECORD ' table_name, count(*) rowcount FROM HR_DOCUMENTS_OF_RECORD 
UNION
SELECT 'PER_CONTACT_RELATIONSHIPS ' table_name, count(*) rowcount FROM PER_CONTACT_RELATIONSHIPS 
) a
where 
a.rowcount <> 0

You can add/remove more tables based on data in your environment.

Stats:

Normally the process takes around 5-6 hrs for 16-17K employees. Performance depends upon environment sizing as well among other factors.

Mandeep Gupta 0 Comments
Aug 25 2022
BIP – Queries related to Bank, Bank Branches and External Bank accounts

Query for bank details:

select * from ce_banks_v

Query for bank branch details:

select * from ce_bank_branches_v

select cbbv.bank_name
      ,cbbv.bank_branch_name
      ,cbbv.bank_home_country
      ,cbbv.branch_number 
      ,TO_CHAR(cbbv.start_date,'YYYY/MM/DD') branch_start_date
      ,TO_CHAR(cbbv.end_date,'YYYY/MM/DD') branch_end_date
  from ce_bank_branches_v cbbv

Query to extract Internal bank accounts:

select * from ce_bank_accounts

Query to extract External bank accounts:

select * from iby_ext_bank_accounts
Mandeep Gupta 0 Comments
Aug 25 2022
BIP – Query to extract Disability Org Details

Use below query to extract disability org details from Fusion HCM:

SELECT houft.name disability_org_name
      ,TO_CHAR(haouf.effective_start_date,'RRRR/MM/DD') disability_org_start_date
      ,TO_CHAR(haouf.effective_end_date,'RRRR/MM/DD') disability_org_end_date
      ,houcf.classification_code
      ,haouf.internal_address_line
      ,houcf.status
  FROM hr_org_unit_classifications_f houcf
     , hr_all_organization_units_f haouf
     , hr_organization_units_f_tl houft
 WHERE houcf.organization_id = haouf.organization_id
   AND houft.organization_id = haouf.organization_id
   AND TRUNC(SYSDATE) BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
   AND haouf.effective_start_date BETWEEN houft.effective_start_date AND houft.effective_end_date
   AND houft.language = USERENV('LANG')
   AND houcf.category_code = 'HCM_DISABILITY_ORGANIZATION'
Mandeep Gupta 0 Comments
Aug 25 2022
BIP – Query to extract absence reasons from Oracle HCM

Sometime for data migration, it is required to get a dump of all the absence reasons configured in Fusion HCM in order to validate the data being loaded.

Below query can be used to extract the setup data for absence reasons:

SELECT aarft.name absence_reason_name
      ,to_char(aarf.effective_start_date,'YYYY/MM/DD') reason_start_date
      ,to_char(aarf.effective_end_date,'YYYY/MM/DD') reason_end_date
      ,aarf.legislation_code legislation_code
      ,aarf.status reason_status
      ,aarft.description reason_description
      ,aarf.base_name reason_base_name
  FROM ANC_ABSENCE_REASONS_F aarf
      ,ANC_ABSENCE_REASONS_F_TL aarft
 WHERE aarf.absence_reason_id=aarft.absence_reason_id
   AND aarf.effective_start_date BETWEEN aarft.effective_start_date AND aarft.effective_end_date
   AND aarft.language=userenv('LANG')
Mandeep Gupta 0 Comments
Aug 25 2022
Configuration – Value Set to show numbers from 1 to 60

There is a common requirement to use value sets in various DFF attributes across Fusion HCM application. Oracle HCM supports different kind of value sets – Independent, Table validated etc.

There is a use case where a user wants to see a list of values containing numbers from 1 to n. There are two approaches to create a value set for this. Either you can define an Independent value set with 1 to n unique values or you can define a table validated value set.

Independent value set approach works fine if the value of n is small. But if the value of n is a large integer for example, n = 60, then it is not pragmatic to go ahead with a independent value set.

See the below example to define a table validated value set which will display values ranging from 1 to 60:

FROM Clause(SELECT TO_NUMBER( LEVEL) value FROM DUAL CONNECT BY LEVEL <= 60) a
Value Attributes Table Alias 
*Value Column Namea.value
Value Column TypeNUMBER
Value Column Length
Description Column Name
Description Column Type 
Description Column Length
ID Column Namea.value
ID Column TypeNUMBER
ID Column Length
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clause1=1
ORDER BY Clausea.value

This will look like below in application:

Mandeep Gupta 0 Comments