In this article, we will look into scrambling the salary information post P2T refresh in order to prevent misuse of salary information in test/dev instances.
Email Address and Salary information are the two most important things that clients want to mask/scramble in lower environments in order to prevent notifications to end-users and hide the sensitive salary information.
In our previous article we have gone through how to mask the email addresses and today we will look into scrambling the salary information.
There are two approaches:
- Scramble the salaries with random numbers, so that even if someone looks at them they will not be able to make up the actual numbers.
- Purge the salary information completely, i.e. deleting the complete salary history information.
We will be using the HCM Data Loader Salary.dat for both approaches and we have the flexibility to mask/purge operation for all employees or a specific set of employees.
Table of Contents
Scramble Salary Information with random numbers
In this approach, we generate the HDL file with random numbers. The advantage of this approach is the salary information will still exist in the system and any interfaces/integrations can be created to pull salary data. The query is intelligent enough to generate smaller numbers for hourly employees and larger numbers for salaried employees.
WITH PERSONS_LIST AS (
select person_id, person_number from per_all_people_f where sysdate between effective_start_date and effective_end_date
--and person_number in ('15937','15595','15591') -- person list can be provided over here.. else salaries will be scrambled for all employees
)
Select HDL from
(
SELECT ('METADATA|Salary|AssignmentNumber|DateFrom|DateTo|SalaryAmount|SalaryBasisName|ActionCode|ActionReasonCode|MultipleComponents|SalaryApproved') HDL, 1 as SNO FROM DUAL
union
SELECT
('MERGE|Salary|'||PAAM.ASSIGNMENT_NUMBER||'|'||TO_CHAR(CS.DATE_FROM,'YYYY/MM/DD')||'|'||TO_CHAR(CS.DATE_TO,'YYYY/MM/DD')||'|'||(case when upper(CSBT.SALARY_BASIS_NAME) like '%HOURLY%' then (ROUND(DBMS_RANDOM.VALUE(40,150))) else (ROUND(DBMS_RANDOM.VALUE(10000,9999999))) END)||'|'||CSBT.SALARY_BASIS_NAME||'|'||PAB.ACTION_CODE||'|'||PARB.ACTION_REASON_CODE||'|'||CS.MULTIPLE_COMPONENTS||'|'||CS.SALARY_APPROVED) HDL, 2 as SNO
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
CMP_SALARY CS,
CMP_SALARY_BASES_TL CSBT,
PER_ACTIONS_B PAB,
PER_ACTION_REASONS_B PARB,
PERSONS_LIST PL
WHERE 1=1
AND PAAM.PERSON_ID = PL.PERSON_ID
AND PAAM.ASSIGNMENT_ID = CS.ASSIGNMENT_ID
AND CS.SALARY_BASIS_ID = CSBT.SALARY_BASIS_ID
AND CS.ACTION_ID=PAB.ACTION_ID
AND CS.ACTION_REASON_ID=PARB.ACTION_REASON_ID(+)
AND PAAM.Assignment_type = 'E'
--AND PAAM.PRIMARY_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
union
SELECT ('METADATA|SalaryComponent|AssignmentNumber|ComponentReasonCode|ComponentApproved|ChangeAmount|DateFrom') HDL, 3 as SNO FROM DUAL
Union
SELECT
('METADATA|SalaryComponent|'||PAAM.ASSIGNMENT_NUMBER||'|'||CSC.COMPONENT_REASON_CODE||'|'||CSC.COMPONENT_APPROVED||'|'||(ROUND(DBMS_RANDOM.VALUE(10000,9999999)))||'|'||TO_CHAR(CSC.SALARY_DATE_FROM,'YYYY/MM/DD')) HDL,4 as SNO
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
CMP_SALARY_COMPONENTS CSC,
PERSONS_LIST PL
WHERE 1=1
AND PAAM.PERSON_ID = PL.PERSON_ID
AND PAAM.ASSIGNMENT_ID = CSC.ASSIGNMENT_ID
AND PAAM.Assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
)
order by SNOPurge Salary Information completely
With this approach, the complete salary information will be deleted. We can run it for a specific set of employees or all employees. We will use the PurgeAssignmentSalary flag to purge salary information.
WITH PERSONS_LIST AS (
select person_id, person_number from per_all_people_f where sysdate between effective_start_date and effective_end_date
--and person_number in ('101','102','103') -- person list can be provided over here.. else salaries will be purged for all employees
)
Select HDL from
(
SELECT ('METADATA|Salary|AssignmentNumber|PurgeAssignmentSalary|DateFrom|SalaryAmount|SalaryBasisName') HDL, 1 as SNO FROM DUAL
union
SELECT
('DELETE|Salary|'||PAAM.ASSIGNMENT_NUMBER||'|Y|'||TO_CHAR(CS.DATE_FROM,'YYYY/MM/DD')||'|0|'||CSBT.SALARY_BASIS_NAME) HDL, 2 as SNO
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
CMP_SALARY CS,
CMP_SALARY_BASES_TL CSBT,
PER_ACTIONS_B PAB,
PER_ACTION_REASONS_B PARB,
PERSONS_LIST PL
WHERE 1=1
AND PAAM.PERSON_ID = PL.PERSON_ID
AND PAAM.ASSIGNMENT_ID = CS.ASSIGNMENT_ID
AND CS.SALARY_BASIS_ID = CSBT.SALARY_BASIS_ID
AND CS.ACTION_ID=PAB.ACTION_ID
AND CS.ACTION_REASON_ID=PARB.ACTION_REASON_ID(+)
AND PAAM.Assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN CS.DATE_FROM AND NVL(CS.DATE_TO,sysdate+1)
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
)
order by SNO
Salary Information of sample employee
Salary.dat file to Purge Salary Information for this employee:
METADATA|Salary|AssignmentNumber|PurgeAssignmentSalary|DateFrom|SalaryAmount|SalaryBasisName
DELETE|Salary|E101|Y|2020/07/01|0|US1 Hourly Rate
Salary information after purge:
Hope this will be useful.
