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.
Table of Contents
Here is the list of functions along with samples:
| Function Used | Sample Code |
| Concatenation Operator (||) | PAYROLL_NAME||’SAR’||’S’ |
| Converting Date to Character | to_char(SYSDATE,’YYYYMMDD’) |
| Integer Part of Number | INTEGER_PART(CONTRIBUTION_AMOUNT) |
| Decimal Part of Number | DECIMAL_PART(CONTRIBUTION_AMOUNT) |
| Left padding with ‘0’ for 2 characters | LPAD(DECIMAL_PART(CONTRIBUTION_AMOUNT),2,’0’) |
| Right Padding with spaces for 5 characters | Rpad(‘L01’,5,’ ‘) |
| IF Condition | IF YEAR_END_PARAM=’Y’ THEN YEAR_END_PERIOD ELSE SUBSTR(PERIOD_NAME,1,2) END IF |
| Formatting Number | format_number(to_number(EARNS1YTD),’00000000.00’) |
| Instring to search for | character | INSTR(ABBR_RESULT,’|’,1,2) |
| Substring to get the organization till first occurrence of – | substr(Organization,1,Instr(Organization,’-‘,1)-1) |
| Nested If condition | IF 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 string | TO_CHAR(Round(TO_NUMBER(SALARY_AMOUNT),4)) |
| Format Date to string | format_date(sysdate,’YYhh’), FORMAT_DATE(SYSDATE,’YYYYMMDD’), FORMAT_DATE(SYSDATE,’HH24MI’) |
| Replace – with blank | Replace(../../../../Person_Primary_National_Identifier_Number, ‘-’, ‘’) |
| Accessing child levels at parent level using XPATH commands | PPM_TRV/PPM_DG[1]/PPM_RC/BANK_ACCOUNT_NUMBER |
| Increase Date function to add number of days to date | INCREASE_DATE(ACTUAL_TERMINATION_DATE,1) |
| NVL if value is blank | NVL(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 operator | DIV |
| Decode function | DECODE(SEQUENCE_NUMBER(FileSeq), 1, ‘A’, 2,’B’) |
| Count function to count the number of records at that level | Count(ISSUE) |
| Length function | IF LENGTH(Department)<=10 THEN Department ELSE NULL END IF |
| Resetting Variables | ResetVar(‘DAILYOTTOTAL2’,0) |
| Adding to variable | AddToVar(‘DAILYOTTOTAL2’,1) |
| Getting value of variable | GetVar(‘DAILYOTTOTAL2’) |
| Not equal to condition | IF (INSTR(OPRID,’@’,1) != 0) THEN SUBSTR(OPRID,1,instr(OPRID,’@’,1)-1) ELSE OPRID END IF |
| Sum Function | Round(SUM(TO_NUMBER(RUN_401K_HOURS)),2) |
| New Line Character | Chr(10) |
| Tab character | CHR(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 spaces | TRIM(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 Name | sum(POSITIVEBALANCEDETAILS[BALANCE_NAME=’ABC US HEALTHCARE FSA’]/BAL_VALUE) |
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.
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.
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
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.
Hi Sricharan,
It would be helpful if we have similar functions for excel templates also.
Thanks
Hi Sahad,
Sure. Will do a post on it in near future.
Thanks,
Sricharan
Hi Sricharan,
How to achieve group total and show then after detail records in etext?