Data Masking – Mask Salary Data in lower environment

There is a common requirement to mask salary data post P2T refreshes. This should be done in order to hide the actual salaries information as salary is a very sensitive information.

Use the below query to generate data in HDL format in a test environment immediately after P2T refresh. The below query generate a random salary amount. Save the downloaded data in .dat file format and upload it back to the instance.

Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
round(DBMS_RANDOM.VALUE (1,15000) , 2)||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type in ('E', 'C', 'P')
AND paam.assignment_number ='E788880'
ORDER BY data_flow_order