HCM Data Loader Transformation formula

Sample Data - Job.txt

----------------------------

Test Job3,TJ3,COMMON,2021/01/01,4712/12/31,A

Test Job4,TJ4,COMMON,2021/01/01,4712/12/31,A

/********************************************************** 

* FORMULA NAME: GENERATE_JOB_HDL_FROM_TXT

* FORMULA TYPE: HCM Data Loader

* DESCRIPTION: This formula will create Job HDL File from TXT File

******************************************************************/

/* Inputs  */

INPUTS ARE  OPERATION (text), LINENO (number),  LINEREPEATNO (number),POSITION1 (text), POSITION2 (text), POSITION3 (text), POSITION4 (text), POSITION5 (text), POSITION6 (text)

DEFAULT FOR POSITION1 IS 'NO DATA'

DEFAULT FOR POSITION2 IS 'NO DATA'

DEFAULT FOR POSITION3 IS 'NO DATA'

DEFAULT FOR POSITION4 IS 'NO DATA'

DEFAULT FOR POSITION5 IS 'NO DATA'

DEFAULT FOR POSITION6 IS 'NO DATA'

DEFAULT FOR LINEREPEATNO IS 1

IF OPERATION='FILETYPE' THEN 

   OUTPUTVALUE='DELIMITED' 

   

ELSE IF OPERATION='DELIMITER' THEN 

   OUTPUTVALUE=',' 

   

ELSE IF OPERATION='READ' THEN

   OUTPUTVALUE='NONE'

ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN

(

OUTPUTVALUE = '1'

RETURN OUTPUTVALUE

)

ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN

(   

METADATA1[1]  = 'Job' /*FileName*/ /*Reserved*/

METADATA1[2]  = 'Job' /*FileDiscriminator*/ /*Reserved*/

METADATA1[3]  = 'Name'

METADATA1[4]  = 'JobCode'

METADATA1[5]  = 'SetCode'

METADATA1[6]  = 'EffectiveStartDate'

METADATA1[7]  = 'EffectiveEndDate'

METADATA1[8]  = 'ActiveStatus'

RETURN METADATA1

)

ELSE IF OPERATION='MAP' THEN 

(

IF LINEREPEATNO = 1 THEN

(

LINEREPEAT = 'Y'

FileName = 'Job'

BusinessOperation = 'MERGE'

FileDiscriminator = 'Job'

Name = trim(POSITION1)

JobCode = trim(POSITION2)

SetCode = trim(POSITION3)

EffectiveStartDate = trim(POSITION4)

EffectiveEndDate = trim(POSITION5)

ActiveStatus = trim(POSITION6)

RETURN BusinessOperation,FileName,FileDiscriminator,Name,JobCode,SetCode,EffectiveStartDate,EffectiveEndDate,ActiveStatus,LINEREPEAT,LINEREPEATNO

)

)

ELSE 

   OUTPUTVALUE='NONE'

RETURN OUTPUTVALUE

/* End Formula Text */

Query to Fetch Business Unit in Cloud

select haou.name as business_unit,
paam.person_id
from hr_org_unit_classifications_f houcf
, hr_all_organization_units haou
,per_all_assignments_m paam
,per_periods_of_service ppos
where
1=1
and houcf.organization_id = haou.organization_id
and paam.organization_id= haou.organization_id
and PPOS.PERIOD_OF_SERVICE_ID= PAAM.PERIOD_OF_SERVICE_ID
and houcf.classification_code= 'DEPARTMENT'
and houcf.status = 'A' 
AND paam.assignment_type IN ('E','C')
AND paam.effective_latest_change = 'Y' 
and paam.assignment_status_type IN ('ACTIVE','SUSPENDED')
and ppos.ACTUAL_TERMINATION_DATE is null
and :EFFECTIVE_DATE BETWEEN Paam.EFFECTIVE_START_DATE AND Paam.EFFECTIVE_END_DATE
and :EFFECTIVE_DATE BETWEEN haou.EFFECTIVE_START_DATE AND haou.EFFECTIVE_END_DATE
and :EFFECTIVE_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

Font Mapping in BI Publisher

We were facing an issue where a particular font(Kunstler Regular) which was added in the RTF template. Though the font was visible in the RTF template, it was not visible in the final PDF report generated. To resolve this issue, the below steps need to be done.

• Pull out the font's .TTF file from C:/Windows/Fonts into a separate folder. If the font family has multiple fonts, then many files will be copied into your destination folder. In my case it was just font(KUNSTLER.ttf file)
• In BIP navigate to Administration-Manage BI Publisher-Font Mappings
• Choose the above file under Manage Custom Fonts and click on upload
• Then under RTF templates click on Add Font Mapping
• Give Base Font as the name of the font, it should be same as the name which is visible in MS word(else it didn't work for me)
• Select target font, which will the above uploaded file name and click on Apply
• Once this is done, in BIP navigate to Administration-Manage BI Publisher-Manage Cache and click on Clear Object Cache.

Once these steps are done and the report was re-run, the PDF output also had displayed the Kunstler Regular font.