Search for:
Oct 6 2022
HDL – Mask Candidate email addresses

Post P2T, there is always a requirement to mask the candidate email addresses in lower environments. Below SQL can be used to generate data masked email data in HDL format:

SELECT 'METADATA|CandidateEmail|EmailAddressId|PersonId|CandidateNumber|EmailAddress|DateFrom|DateTo|SourceSystemOwner|SourceSystemId' "Record"

 FROM DUAL

UNION

SELECT DISTINCT 'MERGE'    || '|'||

'CandidateEmail'        || '|'||

ICEA.EMAIL_ADDRESS_ID          || '|'||

ICEA.PERSON_ID             || '|'||

ICEA.CANDIDATE_NUMBER          || '|'||

replace(PEAD.EMAIL_ADDRESS,'.com','.invalidcomx')           || '|'||

TO_CHAR(PEAD.DATE_FROM,'YYYY/MM/DD')  || '|'||

TO_CHAR(PEAD.DATE_TO,'YYYY/MM/DD')   || '|'||

HIKM.SOURCE_SYSTEM_OWNER      || '|'||

    HIKM.SOURCE_SYSTEM_ID      "Record"

FROM IRC_CAND_EMAIL_ADDRESS_V ICEA,

PER_EMAIL_ADDRESSES PEAD,

HRC_INTEGRATION_KEY_MAP HIKM

WHERE ICEA.EMAIL_ADDRESS_ID = PEAD.EMAIL_ADDRESS_ID

  AND HIKM.SURROGATE_ID = PEAD.EMAIL_ADDRESS_ID

You can update the email address as per your requirement.

Mandeep Gupta 0 Comments
Apr 3 2021
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

Mandeep Gupta 0 Comments
Jan 1 2021
HDL – Email Data Obfuscation in Test environment

Post P2T refresh, it is mandatory to mask the actual email Ids of the users in cloned test environment. This is required to avoid any unwanted emails getting triggered to the end users.

The approach is already discussed in below post: https://fusionhcmconsulting.com/2020/12/hdl-query-to-delete-phones-data/

Use the below SQL to extract the data in HDL format:

select 'MERGE' "METADATA",
       'PersonEmail' "PersonEmail",
       pea.email_address_id "EmailAddressId",
       pea.person_id "PersonId",
       to_char(pea.date_from,'RRRR/MM/DD') "DateFrom",
       to_char(pea.date_to,'RRRR/MM/DD') "DateTo",
       pea.email_type "EmailType",
       -->pea.email_address "1EmailAddress",
       replace(replace(pea.email_address,'@','@invalid'),'.co','.xco') "EmailAddress", --> Change the format as per your requirement
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemId",
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemOwner"
  from per_email_addresses pea
 where 1=1
   and upper(email_address) like '%XXCOMPANY%' --> Change the format as per your requirement

Query with Person Number:

select 'MERGE' "METADATA",
       'PersonEmail' "PersonEmail",
       pea.email_address_id "EmailAddressId",
       pea.person_id "PersonId",
       papf.person_number "PersonNumber",
       to_char(pea.date_from,'RRRR/MM/DD') "DateFrom",
       to_char(pea.date_to,'RRRR/MM/DD') "DateTo",
       pea.email_type "EmailType",
       -->pea.email_address "1EmailAddress",
       replace(replace(pea.email_address,'@','@invalid'),'.co','.xco') "EmailAddress", --> Change the format as per your requirement
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemId",
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemOwner"
  from per_email_addresses pea, per_all_people_f papf
 where 1=1
   and pea.person_id = papf.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and upper(email_address) like '%XXCOMPANY%' --> Change the format as per your requirement

Once the data is generated, create the HDL file and load the Worker.dat.

Post data load steps:

  1. Run “Send Personal Data for Multiple Users to LDAP” process. This process will sync up the latest email address from worker email to user.
  2. Login to Security Console and verify for couple of users from UI or you can verify from backend.
  3. Disable the notifications (optional) – Navigate to Security Console -> User Categories -> Notification Preferences -> Uncheck the Enable Notifications:

4. Or you can redirect all the notification to single email address. Search for ‘Manage Approval Groups’ task in ‘Setup and Maintenance”:

  • Clicking on above task will take you to the BPM Worklist page.
  • Click on “Administration” button and search for Test Notification Email Address and provide the value where you want to redirect all your notifications:

If you want to completely purge the email data then use below post:

https://fusionhcmconsulting.com/2022/10/hdl-delete-person-email-addresses/

Mandeep Gupta 1 Comments