Search for:
Apr 29 2022
BIP – Extract Salary basis with associated element details
  SELECT csb.name
		,csb.salary_basis_code
		,csb.salary_annualization_factor
		,pld.name LDG
		,petft.element_name
		,pivt.name input_value_name
		,rate.name rate_name
    FROM cmp_salary_bases csb,
	     per_legislative_data_groups_tl pld,
	     pay_element_types_tl petft,
	     pay_input_values_f pivf,
	     pay_input_values_tl pivt,
	     per_rates_f_tl rate
   WHERE 1=1
     and csb.legislative_data_group_id = pld.legislative_data_group_id
     and pld.language = USERENV('LANG')
     and csb.element_type_id = petft.element_type_id
    -- and trunc(sysdate) between petft.effective_start_date and petft.effective_end_date
     and petft.language = USERENV('LANG')
     and csb.element_type_id = pivf.element_type_id
     and csb.input_value_id = pivf.input_value_id
     and trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date
     and pivf.input_value_id = pivt.input_value_id
     and pivt.language = USERENV('LANG')
     and csb.grade_rate_id = rate.rate_id(+)
     and NVL(rate.language,USERENV('LANG')) = USERENV('LANG')
ORDER BY 1
Mandeep Gupta 0 Comments
Apr 21 2022
BIP – Query to DELETE Salary data using HDL

There may be a requirement to delete salary data while doing HDL iterations for the first time. You can use below query for the same purpose

Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId' Header, 1 data_flow_order
from dual
UNION
SELECT 'DELETE|Salary'||'|'||
paam.assignment_number||'|'||
SALARY_AMOUNT||'|'||
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 trunc(cs.creation_date) < trunc(sysdate)-1
ORDER BY data_flow_order
Mandeep Gupta 0 Comments
Apr 21 2022
HDL – Query to DELETE Assigned Payroll data from Fusion

In case, you need to delete assigned payroll data for masking purpose in lower environment or for some other reason like correction etc, use below query to pull the data in HDL format:

SELECT 'METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner' HEADER, 1 DATA_ORDER
  FROM DUAL
UNION ALL
SELECT 'DELETE|AssignedPayroll'
	   ||'|'||
       papd.ASSIGNED_PAYROLL_ID
	   ||'|'||
       to_char(papd.start_date,'RRRR/MM/DD')
	   ||'|'||	   
	   paam.assignment_id
	   ||'|'||	   
	   papd.LEGISLATIVE_DATA_GROUP_ID
	   ||'|'||	   
	   papd.Payroll_Id
	   ||'|'||	   
	   papd.Payroll_Term_Id
	   ||'|'||	   
       hikm.source_system_id
	   ||'|'||	   
       hikm.source_system_owner HEADER, 2 DATA_ORDER
  FROM pay_assigned_payrolls_dn papd
	  ,pay_payroll_terms ppt
	  ,pay_pay_relationships_dn prrd
	  ,pay_all_payrolls_f papf_pay
	  ,pay_rel_groups_dn prgd
	  ,pay_rel_groups_f prgf
	  ,per_all_people_f papf
	  ,per_person_names_f ppnf
	  ,per_all_assignments_m paam 
	  ,hrc_integration_key_map hikm
 WHERE papd.payroll_term_id = ppt.payroll_term_id
   AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
   AND papd.payroll_id = papf_pay.payroll_id
   AND prrd.person_id = papf.person_id
   AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = prgf.relationship_group_id
   AND prgd.assignment_id = paam.assignment_id
   AND paam.effective_latest_change = 'Y'
   AND prgd.group_type = 'A'
   AND ppnf.person_id = papf.person_id
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND hikm.surrogate_id = papd.ASSIGNED_PAYROLL_ID
   AND trunc(papd.creation_date) < trunc(sysdate)-1
   --AND papf.person_number = '11111'
 ORDER BY 2

Sample HDL File:

METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner
DELETE|AssignedPayroll|300000012343876|2022/01/19|300000046430123|300000004188123|300000014605111|300000046430342|ASGN_PAYROLL_12345|EBS-HR
Mandeep Gupta 0 Comments