Background: This is one of the outbound HCM extract object used in our fusion cloud integration with the <Third Party> application.
HCM extract tool was used to extract the HCM job wherin FF (fast formula) and BI template was used for the counting/summary logic and formatting.
This blog would be helpful for those who wants to use fast formula and BIP template in the HCM extract for the sum/average/count functions at various HCM entity levels.
Third party application maintains the employees details in sync with fusion HCM employees data where latest job rates are maintained for few employees who are required to be work on the internal/external job tasks. There is a provision to generate estimation of the project during which it calculates the assigned resources job rates.
This is where there is a requirement of job rate calculation and integration of the same to the third party integration.
Requirement :Integration requirement was to extract the job rate details ( avg of max mid-rate details) of each job for a given LDG and link the already integrated/interfaced employee details.
These employee rate details are used for resource estimation purpose in the <third party> system.
Build Details : Created oracle HCM extract with FF and BI template attachment to format the data. HCM extract output would be interfaced to UCM and finally to OIC and <third party> system.
FLOW : Oracle fusion cloud =>UCM =>OIC => <Third Party> application
Issues and Resolution:We had faced performance issue on count and summary columns so we have moved the calculation logic to BIP template/style sheet.
This approach has increased the performance and it was working fine without any issues.
Extract Design:
DATA GROUP
JOB
VALID GRADES
GRADE GROUP
GRADE RATES
Grade,RATE_VALUE (Fast Formula would be attached to the Rate Value Column)
1) Rate Value (FF) : If Grade frequency = Hourly then RETURN grade mid value rating
ELSE (midvalue) * (Annualization Factor)/(Profile Rate value)
2) Final AVG Rate Value : Sum( Rate Value ) / Count of Rate Value : Rate Value is calculated in FF and passed to the HCM Extract.
(Sum and Avg can be calculated in HCM extract but we have avoided using BIP template and
style sheet. There were also some issues with additional column/TAGS display which we
avoided using BIP Template by limiting required columns ).
Setups required : 1)HCM Extract with 4 Groups and the respective entities are shown in the attached document
2)Fast Formula to calculate the rate value as show in the attached document and call
the same in the HCM extract
3)Call the HCM extract’s FF item inside the BIP template/Style sheet formula to calculate
the avg rate value
(Screen shots has been given in the attached document)
e.g : CASE1
Job
HCM<PROJCODE>_ELECTRICIAN
HCM<PROJCODE>_ELEC
Grade
Name HCM<PROJCODE>_M1
Code HCM<PROJCODE>_MCD1
Grade Rates
HCM<PROJCODE>_Grade_Rate3 USD 15,000.00 Minimum 17,000.00 Maximum 16,000.00
HCM<PROJCODE>_Grade_Rate2 USD 13,000.00 Minimum 15,000.00 Maximum 14,000.00
HCM<PROJCODE>_Grade_Rate1 USD 10,000.00 Minimum 12,000.00 Maximum 11,000.00
<PROJCODE>_Grade_Rate_details : US Legislative Data Group : Salary Monthly 12/2080 78.5
X=16000*12/2080 =Calculated by FF
y=14,0000*12/2080 =Calculated by FF
Z=11,000.00*12*2080 =Calculated by FF
AVG x,y and z value is 78.5 (calculated by BIP template)
This average job rate value would be integrated from HCM to the third party application where it is used for the project’s estimates.
Please go through the attached document for more information.
Note : Please check FF condition ” if isnull(VALU_SET_VALUE)=’N’ THEN ” condition and use it as per your requirement.
It returns ‘Y’ when value is not null.