Many a times, consultants are required to modify the standard BIP reports. This is very much applicable in case of workflow notifications which are based on BIP reports. Both data model as well report can be customized to include/exclude any additional fields.
Sometimes it is required to delete the uploaded phones data from Fusion HCM. Below query can be used to create a BIP report which in turn can be used to generate HDL file to delete phones data.
The query can be modified to delete phones data for contacts as well as for other person types.
Supported Version :- 20D
SELECT 'METADATA|PersonPhone|PhoneId|PhoneType|PersonId|DateFrom|SourceSystemId|SourceSystemOwner' D_ROW
from per_phones pp,
where pp.phone_id= hikm.surrogate_id
and exists (select 1 from per_periods_of_service where person_id=pp.person_id)
In HR Helpdesk (HRHD), workers are assigned as resources. Below SQL query can be used to get the details of workers who are assigned as resources:
select hp.PERSON_FIRST_NAME "FirstName"
from HZ_PARTIES hp,
where papf.person_id = hp.ORIG_SYSTEM_REFERENCE
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
--and papf.person_number IN ('964091')
While creating an HCM extract based on a BIP report, there is a common requirement where the BIP should retrieve “Changed Only” data. This can be achieved using a BIP report data model which is based on globalReportsDataModel (/shared/Human Capital Management/Payroll/Data Model).
Use the below query in the data set to extract last run date of HCM extract and based on this date create a filter on last_update_date of the standard table from which we are extracting the data.
WITH pLastRunDate AS
(SELECT MAX(pfi.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM pay_flows pf
AND pf.base_flow_id = pfi.base_flow_id
AND pfi.LAST_UPDATE_DATE < sysdate
AND pf.base_flow_name ='XX Custom Integration Extract' -- - give the name of your HCM extract
AND pfi.status = 'COMPLETED'