Etext template tips and tricks – Part 2
Etext template tips and tricks – Part 2

 1,842 total views

In this article I will go through some of the functions which we use daily while developing etext templates along with sample code for them.

If you haven’t gone through the Etext Part 1 article, I suggest you to go through it by clicking here before proceeding with this article.

Some of the functions listed over here are not even on the oracle documentation and they come based on my personal experience and they will work perfectly without any issues.

Here is the list of functions along with samples:

Function UsedSample Code
Concatenation Operator (||)PAYROLL_NAME||’SAR’||’S’
Converting Date to Characterto_char(SYSDATE,’YYYYMMDD’)
Integer Part of NumberINTEGER_PART(CONTRIBUTION_AMOUNT)
Decimal Part of NumberDECIMAL_PART(CONTRIBUTION_AMOUNT)
Left padding with ‘0’ for 2 charactersLPAD(DECIMAL_PART(CONTRIBUTION_AMOUNT),2,’0’)
Right Padding with spaces for 5 charactersRpad(‘L01’,5,’ ‘)
IF ConditionIF YEAR_END_PARAM=’Y’ THEN YEAR_END_PERIOD ELSE SUBSTR(PERIOD_NAME,1,2) END IF
Formatting Numberformat_number(to_number(EARNS1YTD),’00000000.00’)
Instring to search for | characterINSTR(ABBR_RESULT,’|’,1,2)
Substring to get the organization till first occurrence of –substr(Organization,1,Instr(Organization,’-‘,1)-1)
Nested If conditionIF Pay_cycle IS NOT NULL THEN (IF Pay_cycle = ‘Biweekly’ THEN ‘B’ ELSE ‘M’ END IF) ELSE NULL END IF
Converting to Number and then to stringTO_CHAR(Round(TO_NUMBER(SALARY_AMOUNT),4))
Format Date to stringformat_date(sysdate,’YYhh’), FORMAT_DATE(SYSDATE,’YYYYMMDD’), FORMAT_DATE(SYSDATE,’HH24MI’)
Replace – with blankReplace(../../../../Person_Primary_National_Identifier_Number, ‘-’, ‘’)
Accessing child levels at parent level using XPATH commandsPPM_TRV/PPM_DG[1]/PPM_RC/BANK_ACCOUNT_NUMBER
Increase Date function to add number of days to dateINCREASE_DATE(ACTUAL_TERMINATION_DATE,1)
NVL if value is blankNVL(ACTUAL_TERMINATION_DATE, EFFECTIVE_START_DATE)
Age Calculation using Julian Number of TO_CHAR(TO_NUMBER(TO_CHAR(sysdate,’J’)) – TO_NUMBER(TO_CHAR(DATE_OF_BIRTH,’J’))) DIV 365
Division operatorDIV
Decode functionDECODE(SEQUENCE_NUMBER(FileSeq), 1, ‘A’, 2,’B’)
Count function to count the number of records at that levelCount(ISSUE)
Length functionIF LENGTH(Department)<=10 THEN Department ELSE NULL END IF
Resetting VariablesResetVar(‘DAILYOTTOTAL2’,0)
Adding to variableAddToVar(‘DAILYOTTOTAL2’,1)
Getting value of variableGetVar(‘DAILYOTTOTAL2’)
Not equal to conditionIF (INSTR(OPRID,’@’,1) != 0) THEN SUBSTR(OPRID,1,instr(OPRID,’@’,1)-1) ELSE OPRID END IF
Sum FunctionRound(SUM(TO_NUMBER(RUN_401K_HOURS)),2)
New Line CharacterChr(10)
Tab characterCHR(09)
Sequence Number (Used along with Sequence Block)SEQUENCE_NUMBER(Sequence_EMP)
Traverse a level up from current level and get the END_DATE../END_DATE
Traverse 4 levels up from the current level and get the NID../../../../Person_Primary_National_Identifier_Number
TRIM function to trim the leading and trailing spacesTRIM(EARN1_EARN2)
Accessing the BAL_VALUE at POSITIVEBALANCEDETAILS level for the Balance Name ’ABC US HEALTHCARE FSA’ (You can use this when you have multiple child rows and you need them in different columns in the output)POSITIVEBALANCEDETAILS[BALANCE_NAME=’ABC US HEALTHCARE FSA’]/BAL_VALUE
Sum of Balance Values for a Balance Namesum(POSITIVEBALANCEDETAILS[BALANCE_NAME=’ABC US HEALTHCARE FSA’]/BAL_VALUE)
Etext functions with sample

How to use Sequence function:

If we have to use the sequence function to get the auto incrementing sequence then we have to use the DEFINE SEQUENCE to define the sequence and use the increment basis at RECORD or LEVEL to increment the sequence. RECORD increments the sequence each time it is used in a new record. LEVEL increments the sequence only for a new instance of the level.

Sample etext for fixed length using sequence
Sample etext for fixed length using sequence

How to use the Concatenation option:

In order to use the Concatenation, we need to define the DEFINE CONCATENATION segment and define the level at which the concatenation should happen, field which should be concatenated and the delimiter for the concatenation.

Sample etext for delimited using concatenation
Sample etext for delimited using concatenation

The output will have the Person Number followed by concatenation of all assignment numbers. It’s basically like converting rows into columns with a delimiter.

How to use the Grouping condition in etext:

We can also use the grouping condition in etext template to achieve the grouping and get the distinct values. This is also achieved using DEFINE LEVEL but with GROUPING CRITERIA and GROUP SORT ASCENDING or GROUP SORT DESCENDING

Sample delimited template with distinct clause applied
Sample delimited template with distinct clause applied

You can find the complete list of functions supported in etext template by clicking here

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning all Fusion Technical tools go through this article

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Sahad

    Hi Sricharan,

    It would be helpful if we have similar functions for excel templates also.

    Thanks

    1. Sricharan

      Hi Sahad,

      Sure. Will do a post on it in near future.

      Thanks,
      Sricharan

  2. Mahesh

    Hi Sricharan,
    How to achieve group total and show then after detail records in etext?

Leave a Reply