Search for:
Aug 1 2021
BIP – Query to extract Salary component details
SELECT 	DISTINCT paam.assignment_id,
        paam.assignment_number,
		to_char(cs.date_from,'DD-MON-RRRR') date_from,  
		to_char(cs.date_to,'DD-MON-RRRR') date_to,
		cs.salary_id,
		csc.salary_component_id,
		csc.change_amount,
		csc.change_percentage,
		csc.component_reason_code
FROM 	PER_ALL_ASSIGNMENTS_M 		paam,
		CMP_SALARY					cs,		
		CMP_SALARY_COMPONENTS		csc
WHERE	cs.ASSIGNMENT_ID = paam.ASSIGNMENT_ID		
  AND   cs.SALARY_ID = csc.SALARY_ID
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