Using Target Expressions in Oracle Cloud EPM Data Exchange
Oracle Cloud EPM Data Exchange Target Expressions can be used when importing data, to apply conditions to source values before loading to the target Oracle Cloud EPM application. Oracle advise that Target Expressions should be used when possible as they are more efficient than data load mappings, especially for loading large data sets.
To being, setup your integration as usual with a Location, Import Format and Data Load Rule.
Then to apply Target Expressions, go to Data Exchange, open the desired integration, and select Map Dimensions, as below:
For each dimension, use the Options cog to select Edit Target Expressions.
Expand the list to view the options:
The available Target Expressions are as follows:
Copy Source Value
Copies the exact source value to the Target.
This can be used instead of a Like * to * mapping.
Sets a constant value, regardless of the source value.
This can be used instead of a Like * to “Working” mapping, for example.
Will apply a default value when the Source is NULL, otherwise uses the Source value.
Previously to replicate this functionality, we had to use a SQL script to a map NULL value.
Add a prefix to the start of the source value.
This can be used instead of a Like * to PRE* mapping, where PRE is the desired Prefix.
Add a suffix to the end of the source value.
This can be used instead of a Like * to *SUFF mapping, where SUFF is the desired Suffix.
Retrieve a substring from the source value, based on a starting character position and the length of the substring to extract, using format substr(Dimension, position, length)
For example, to extract Location ‘London’ from ‘001London_11’, we can use expression substr(Location, 4, 6)
Specify a target string replacement for a given source string in the source value, using format replace(Dimension, SourceString, TargetString)
For example, to replace any “L” with “0” in a Location, we can use expression replace(Location, “L”, “0”). So source string “LL7991” would map to target “007991”.
Trim trailing characters from the right side of the source string, using format rtrim(Dimension, TrimChar), where TrimChar is the character to trim from the right.
For example, trim trailing 0’s from Location “LKK0000” to give “LKK” using expression rtrim(Location, “0”).
Same as above but from the left side, using format ltrim(Dimension, TrimChar), where TrimChar is the character to trim from the left.
For example, trim leading 0’s from Location “0000LKK” to give “LKK” using expression ltrim(Location, “0”).
Add characters to the right side of the source string, using format rpad(Dimension, length, ParChar), where PadChar is the character to pad with.
For example, add trailing 0’s to Location “LON” to give “LON0000” using expression rpad(Location, 7, “0”.
Same as above but adding to the left side, using format lpad(Dimension, length, ParChar), where PadChar is the character to pad with.
For example, add leading 0’s to Location “LON” to give “0000LON” using expression lpad(Location, 7, “0”.
Round data values as they are loaded.
This expression is only available for the Amount dimension.
Ability to use an if statement to provide conditions which should return a specified value if true or a different value if false.
Retrieve a section of the source string by splitting the string into sections indicated by a delimiter. Use format split(Dimension, “delimiter”, section), where section is a number indicating which section to retrieve once split.
For example, to retrieve Location “London” from string “000-300-London-1000”, use expression split(Location, “-“, 3)
Use a SQL statement to map members based on SQL conditions.
This can be used instead of the Like * to SQL method.
For more information, see Oracle help here:
For more useful Oracle Cloud EPM and NetSuite ERP blogs posted by my colleagues, see the Brovanture website HERE
Until next time