Search for:
Dec 31 2020
Reports (BIP) – Customizing a standard BIP report

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.

Follow the below steps:

  1. Login to your pod using below url:

https://xxzzaa.fa.bb.oraclecloud.com/xmlpserver/

(note the xmlpserver in the end).

2. Then navigate to /Shared Folders/Human Capital Management/Folder

3. Click on More and you will see Customize option. Click on Customize:

This will create a new report under:

/Shared Folders/Custom/Human Capital Management/Folder (notice the change in path).

You can upload your customized templates here.

Click on edit and choose the data model from standard path.

Similar approach will be followed for data model customizations as well.

In order to display the changes asap, please configure the below profile option for global scope:

BIP_CLIENT_REFRESH_TIME – Oracle Middleware Extensions for Applications – Application Core

Provide a value of 15 min.

Check below document for more details:

https://docs.oracle.com/en/cloud/saas/applications-common/20b/facia/approval-management.html#FACIA3819777

Mandeep Gupta 0 Comments
Dec 31 2020
Reports (BIP) – Tips/ Shortcuts
  • To display the date in DD-Mon-RRRR format, please use below in your SQL query:

to_char(date_of_birth,’DD-Mon-RRRR’, ‘nls_date_language=American’)

  • Use below condition to get current user id:

fnd_global.USER_GUID

AND PP.PERSON_ID = PU.PERSON_ID
AND PU.USER_GUID = FND_GLOBAL.USER_GUID

— Use below function to convert the amount in Arabic:

<?xdoxslt:toCheckNumber(‘ar-SA’, TOTAL, ‘AED’,’CASE_UPPER’)?>

— Using LISTAGG to show multiple rows into a single cell:

select listagg(papf.person_number,';') within group (order by person_number) from per_all_people_f papf
where rownum <=5

— Getting Person Id of logged in Person:

HRC_SESSION_UTIL.GET_USER_PERSONID

— Get UDT value in BIP:

ff_user_tables_pkg.get_table_value
Mandeep Gupta 0 Comments
Dec 31 2020
HDL – Query to delete phones data

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

Query:

SELECT 'METADATA|PersonPhone|PhoneId|PhoneType|PersonId|DateFrom|SourceSystemId|SourceSystemOwner' D_ROW
FROM DUAL
union
SELECT 'DELETE|PersonPhone|'||
       pp.phone_id||'|'||
 
       pp.phone_type||'|'||
       pp.person_id||'|'||
       to_char(pp.date_from,'RRRR/MM/DD')||'|'||
       hikm.source_system_id||'|'||
       hikm.source_system_owner
  from per_phones pp,
       hrc_integration_key_map hikm
 where pp.phone_id= hikm.surrogate_id
   and exists (select 1 from per_periods_of_service where person_id=pp.person_id)
Mandeep Gupta 1 Comments
Dec 31 2020
HRHD – Employee Resource Query

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"
,hp.PERSON_LAST_NAME "LastName"
,hp.EMAIL_ADDRESS "ResourceEmail"
,hp.PARTY_NUMBER "ResourcePartyNumber"
,papf.person_number
from HZ_PARTIES hp,
per_all_people_f papf
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')
Mandeep Gupta 0 Comments
Dec 30 2020
HCM Extract – Query to get last extract run date

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
,pay_flow_instances pfi
WHERE 1=1
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'
)
Mandeep Gupta 0 Comments