This page (revision-44) was last changed on 26-Nov-2021 10:22 by khiggs

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
44 26-Nov-2021 10:22 13 KB khiggs to previous
43 26-Nov-2021 10:22 13 KB khiggs to previous | to last
42 26-Nov-2021 10:22 14 KB RForbes to previous | to last DERIVATION EXPRESSIONS ==> DERIVATION_EXPRESSION_USAGE
41 26-Nov-2021 10:22 14 KB JMyers to previous | to last

Page References

Incoming links Outgoing links
DERIVATION_EXPRESSION_USAGE
DERIVATION_EXPRESSION_USAGE

Version management

Difference between version and

At line 3 changed one line
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the Personality database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the Personality specifications.
At line 7 changed one line
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
Inbound interfaces provide the ability to load a source file into Personality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
At line 22 added one line
*[UPDISBV] - Disburse Vendor Payments (A/P File Interface)
At line 28 added one line
The [Callable Functions|CALLABLE FUNCTION] page contains the list of available functions that may be called within a Derivation Expression that may be used in an interface. The list contains functions from Oracle and those created as part of the application.
At line 32 removed one line
\\
At line 35 removed 3 lines
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
At line 39 removed 129 lines
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
At line 170 removed one line
\\
At line 172 removed 3 lines
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
At line 176 removed 129 lines
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example thetime code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
At line 307 removed one line
\\
At line 309 removed 3 lines
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
At line 313 removed 130 lines
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
At line 445 removed one line
\\
At line 447 removed 3 lines
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
At line 451 removed 130 lines
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
At line 583 removed one line
\\
At line 585 removed 7 lines
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
At line 593 removed 126 lines
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
At line 721 removed 5 lines
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
At line 727 removed 129 lines
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
At line 859 removed one line
\\
At line 861 removed 3 lines
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
At line 865 removed 130 lines
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
At line 997 removed one line
\\
At line 999 removed 3 lines
;To remove whitespace
;: trim(~)
\\
At line 1,003 removed 130 lines
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
At line 1,135 removed one line
\\
At line 1,137 removed 2 lines
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
At line 1,140 removed 131 lines
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
At line 1,274 removed 4 lines
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
At line 1,279 removed 130 lines
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
At line 1,411 removed one line
\\
At line 1,413 removed 3 lines
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
At line 1,417 removed 130 lines
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
At line 1,549 removed one line
\\
At line 1,551 removed 3 lines
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
At line 1,555 removed 130 lines
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
At line 1,687 removed one line
\\
At line 1,689 removed 2 lines
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
At line 1,692 removed 131 lines
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
At line 1,826 removed one line
\\
At line 1,828 removed 3 lines
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
At line 1,832 changed 132 lines
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translationlexicon.
At line 1,966 removed 3 lines
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
At line 1,970 removed 131 lines
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
At line 2,104 removed 4 lines
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
At line 2,109 removed 130 lines
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and [UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
At line 2,241 removed one line
\\
At line 2,243 removed 2 lines
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
At line 2,246 removed 131 lines
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for [UPPHF] and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
At line 2,380 removed 3 lines
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
At line 2,384 removed 3 lines
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
At line 2,388 removed 127 lines
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
At line 2,518 removed 3 lines
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
At line 2,522 removed 4 lines
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
At line 2,527 changed 127 lines
!!Derivation Expression Examples for [UPPHF] and [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
; To removing a colon from time field in an inbound interface (ie TTX.START_TIME)
At line 2,656 removed 4 lines
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
At line 2,661 removed 4 lines
!!Derivation Expression Examples for [UPPHF] AND [UEEF]
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
35nnnn.
At line 2,666 changed 127 lines
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the translation, in this example the time code.
At line 2,795 removed 29 lines
!!Derivation Expression Examples for [UPPHF] AND UEEF
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
At line 2,825 changed 107 lines
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
![IDIF] - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
!!Derivation Expression Examples for [UPPHF] and [UEEF]
! Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
At line 2,934 changed one line
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
A full list of INF variables interchangeable for [UPPHF] and [UEEF] are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
At line 2,939 changed 2 lines
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field. Otherwise, use the Unit dun.id on the field for all other situations.
At line 2,942 changed 8 lines
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
*In the derivation expression, enter:
;: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
;:where:
;:[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal
;:[[320009] Identity eid_id
;:[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400] Unit dun_id.
At line 141 added 9 lines
Example: Record # 50, Field # 10 Requirement: Work Date or Pay Period
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee. In this case use the pay period on the interface file field, otherwise use the GL Effective date on the Interface File field.
*From above [IDIF] set up, you should specify the Variable Name using 'UDF (DGD)' and enter the UDF name in Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: \\
;:decode(~,'Y',[320102],to_char(to_date([[320756]),'DD/MM/YYYY'))
;: where:
;:[[320102] Pay Period
;:[[320756] GL Eff Date (PJD)
*This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then return the value from [[320102] pay period. Otherwise, return the value from [[320756] GL Effective date with the date format of 'DD/MM/YYYY'.
At line 2,952 changed 2 lines
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
!Internal Functions
You have the ability to call some Personality internal functions in the derivation expression under the guidance of a consultant.
At line 2,955 changed one line
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
One example of doing this is for the [UPPHF] Payroll History interface. In this example, the Hours Comp Time Amount field is defined with the Variable Name 'Pay Header pph_id' and with a derivation Expression of:
At line 2,957 changed 16 lines
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
;: P2K_PPAMTS.SPELPAY(~,P2K.PPAMTS.SPGETPEL('HOURS COMP TIME'))
At line 2,974 changed 2 lines
----
!!Example Derivation Expressions
The derivation expression will return the Element value of 'HOURS COMP TIME'.
At line 2,977 changed 2 lines
The following are examples of what derivation expressions can be used for:
\\
The above example can also be achieved by setting up a variable name of 'Element pph
Value' with the element code specified in the Constant Value field.
!Internal Functions with Parameters
You have the ability to call some Personality internal functions in the derivation expression under the guidance of a consultant and pass the following internal parameters in order to perform some internal calculation:
;#PPH_ID#
;:This value must be in capital letters; this #PPH_ID# will be replaced by the current pay header's pph.id.
;#EEM_ID#
;:This value must be in capital letters; this #EEM_ID# will be replaced by the current employment's eem.id.
;#EID_ID#
;:This value must be in capital letters; this #EID_ID# will be replaced by the current identity's eid.id e.g. the Element PC value of 'HOURS O/T' will be retrieved first and is represented as ~ in derivation expression. The derivation expression will return the Element value of 'HOURS COMP TIME' for #PPH_ID# and add it to the value of ~.
;:Example:
;:The OT Hours field name is defined with the Variable Element PC Value with a Constant Value of HOURS O/T and has a derivation expression of:
;:P2K_PPAMTS.SPELPAY(#PPH_ID#,P2K_PPAMTS.SPGETPEL('HOURS COMP TIME')),~
At line 2,981 changed 3 lines
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;:You may perform some internal arithmetic calculation in the derivation expression to return some prorated amount.
At line 2,985 removed 3 lines
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
At line 2,989 changed 3 lines
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;:Example:
;:The 'Prorate Element' Field Name is defined with the Varibale Name of elemnt PC Value with a Derivation Expression of:
;:~/P2K_PPAMTS.SPELPAY(#PPH_ID#,P2K-PPAMTS.SPGETPEL('HOURS O/T'))*P2K_PPAMTS.SPELPAY(#PPH_ID#,P2K_PPAMTS.SPGETPEL('HOURS COMPE TIME'))
!Call UserCalc Function
You can call the [UserCalc|USERCALC] function at each Record Number and each Field Number. You should set up the variable name to be 'User Calc', and specify the [UserCalc|USERCALC] name in the Constant Value field.
At line 2,993 changed 3 lines
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
The field type must be defined with a Char, Number or Date type. The Return Value [UserCalc|USERCALC] function on [IMUC] screen must match the values (Char, Number or Date) with the [IMUC] RET command to return the corresponding Char, Number or Date.
At line 2,997 changed 7 lines
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
The data base tables available for UserCalc are:
;:at company level: DEN / DLN / DDP / DDD / DUN / DGR / DGD / DGV
;:at employee level: EID / EPS / EEM / EAS / EASD / PPRU / PPRC
At line 3,005 changed 3 lines
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
!BYPASS Capability
*On [IDIF] Derivation expression, you may decode the Variable Name using the ~ character and return the word 'BYPASS' to bypass the Detail record entry that is with certain criteria.
* The 'BYPASS' criteria can be set up for Record Type = 'Qualify Record', 'Detail Record', and should not be used for Header and Trailer records.
* If the return value of any [IDIF] Record Number/Field Number = 'BYPASS', then this Detail record will be bypassed and will not be written to the Interface file.
;:Example:
*to include only Departments start with 'P' on interface file: decode(rtrim(substr(~,1,1)),'P',~,'BYPASS')
* to include only Departments ends with 'C' on interface file: decode(substr(~,length(rtrim(~)),1),'C','BYPASS',~)
* to bypass Cost Centers that has '????' in second segments: decode(SUBSTR(~,5,4),'????','BYPASS',SUBSTR(~,5,4))
* to include Journal Entries with Account Numbers over 40000 in segment 5: decode(greatest('40000',substr(~,16,5)),'40000','BYPASS',substr(~,16,5))
At line 3,009 changed 3 lines
;To remove whitespace
;: trim(~)
\\
! GOTO#nn#nnn
* when processing [IDIF] records of the same Record Type (e.g. Detail Record), you may want to skip some Record # Field # based on certain criteria and resume processing of the same Record Type at a later Record# Field# onward
* the following GOTO capabilities are available for all Record Types in Derivation Expression:
;:GOTO#nn#nnn where the 1st nn is the Record #, the 2nd nnn is the Field #
* since the [IDIF] entries are processed in chronological order, GOTO#nn#nnn must be for a Record# Field# that is after the current Record# Field#
* if an incorrect GOTO#nn#nnn is specified, or the #nn#nnn is NOT for the same Record Type, or if it is for a previous Rec# Field#, an exception message will be issued, the GOTO statement cannot be executed after skipping the records, you must verify the Interface File from this point onward
* multiple GOTO#nn#nnn can be used within the same Record Type
* if an Invalid GOTO is encountered in [IDIF] definition, the Trial mode parameter is set to 'Y' in order to prevent the Updating of the user-defined fields from the Record Type '92-Update Record'
* for XML File Format, the GOTO#nn#nnn can be used to skip certain XML Tags and carry on the processing
*for Fixed File Format, if the GOTO#nn#nnn is for the current Record#, it will GOTO the specified #nn#nnn, the current Record# information will be written
* for Fixed File Format, if the GOTO#nn#nnn is for a different Record#, it will GOTO the specified #nn#nnn of the different Record#, the current Record# information will NOT be written
* e.g. [UEEF] [IDIF] Record# 30 Field # 45, decode Plan_Code, if it equals to 'HL GROUP LIFE', GOTO#35#10
** this will skip the entire Record# 30 and carry on from Record# 35 Field#10 for the fixed file format
At line 3,013 removed 2 lines
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
At line 3,016 removed 76 lines
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
![IDIF - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
At line 3,093 changed 1,328 lines
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the [IDIF] derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME] are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for [X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from [IMLN] for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ]], e.g. [[320001]]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [[ ], e.g. [[320001]]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [ ], e.g. [[320001]]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [ ], e.g. 320001]]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [ ], e.g. (320001]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
[{TableOfContents }]
\\
Derivation expressions provide the ability to translate values or to retrieve information otherwise not accessible in an interface format in [IDIF]. Data from within the [{$applicationname}] database may need to be translated to match the requirements from a receiving third party system. Also, data within a source file may need to be translated to match the [{$applicationname}] specifications.
!!Derivation Expression Logic for Inbound Interfaces
Inbound interfaces provide the ability to load a source file into ePersonality; this is done by running [LMTD]. The [LMTD] allows you to apply derivation expressions to variables within the record that is being loaded.
The [LMTD] respects most of the same derivation expressions that are used with SQL Loader. One exception that the [LMTD] does not respect is the BYPASS function. Instead you should define a qualifier record, this can be done using an existing column within the table or by defining a foreign field. The derivation expression in this situation would contain an IF statement or a Case When clause and the constant field would contain the result (i.e. True or False). For more information on this please review the chapter on loading with interfaces.
[LMTD] can handle multiple fields being referenced in a derivation expression, however, for [LMTD] purposes the syntax is slightly different. The multiple fields must be specified in brackets with a colon prefacing the field name, for example: (:PLPL.TIME_CODE).
----
!!Calling Functions in [IDIF] for Outbound Interfaces
Define Interface Formats ([IDIF]) is the screen used within the system to define export interface definitions for processing by the various interface programs. Many of these interface programs support the use of derivation expressions, but not all. As at June 1, 2008, the following interface programs have this support:
*[UEEF] - Employee / Assignment Interface
*[UPPHF] - Pay History Interface
*[UBEF] - Employee / Benefits Interface
*[UPGLF] - General Ledger File Interface
*[UPDIF] - Disbursement File Interface
*[UENH] - New Hire Interface
*[UPVEND] - AP (Vendors) Interface
*[UPDTB] - Deposits to Bank Interface
*[UPROEF] - Canadian Record of Employment file interface
----
!!Derivation Expressions Calling Database Functions
[{InsertPage page = 'CALLABLE FUNCTION'}]
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
\\
; To translate Gender to M/F
;: DECODE(~, '01', 'M', '02', 'F')
\\
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:REPLACE(~, '-','')
\\
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: P2K_SMGCD(~,'DDP')
\\
;To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
;: DECODE(~,'ABC','1234','BYPASS')
\\
;To populate an effective date
;: NVL(RTRIM(~),'01-JAN-0001')
\\
;To change the case of supplied data
;: upper(~) or initcap(~)
\\
;To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only interested in loading some of it to our field):
;: substr(~,1,5)
\\
;To remove whitespace
;: trim(~)
\\
; To remove leading zeros off of a Person Code
;: LTRIM(~,'0')
\\
; To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the data base to accept it
;: TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
\\
; To shorten a fiscal period of 200307 for just the period:
;: Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
\\
; To shorten a fiscal period of 200307 for just the year:
;: Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
\\
; Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
;: Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
\\
;This is an example of populating a distribution segment using the value from another field defined in [IDIF]. In this example, the 6th segment will be populated based on the value in the time code field after it has been translated using a translation
lexicon.
;: '?-????-?????-???????-?-' || p2k_smglx('PL_LABOR_CD_KEYED', :PLPL.TIME_CODE) || '-?????'
\\
;Format a GL amount to display the leading zeros and display a decimal point for $2263.13:
;: Substr(TO_CHAR(ABS(~),'0000000000009.99'),2,16) will display 0000000002263.13, will begin at the 2nd character ignoring the leading "-" or "+" sign.
\\
; To decode specific areas of the string:
;: Decode(substr(~,117,2),'05',substr(~,55,5),'02',substr(~,51,3),' ')
\\
; To round information coming in from four to two decimal places:
;: ROUND(~,2)
\\
;This is an example of providing a Start Date for a field. This example will use the Beginning of Time date if there is a null value provided
;: NVL2(~,(to_char(to_date(rtrim(~),'YYYYMMDD'),'DD-Mon-YYYY')),'01-Jan-0001')
\\
; To removing a colon from time field in an inbound interface (ie: TTX.START_TIME)
;: concat(substr(~,1,2),substr(~,4,5))
\\
;Translate an incoming value to a defined Time Code in the system using a translation lexicon. The lexicon is defined in [IMLN], the saved value is the value defined in the source file and the displayed value is the eP translation, in this example the
time code.
;: P2K_SMGLX('GWRS_PC_TRANSLT',~)
!!Derivation Expression Examples for UPPHF AND UEEF
!IDIF - Derivation Expression - Multiple Field Processing
On the IDIF derivation expression, you can specify multiple variable names processing by indicating the lexicon value of the X_INF_VARIABLE_NAME inside the bracket [ ], e.g. [320001]. Lexicon values available from IMLN for X_INF_VARIABLE_NAME are: 32nnnn,
35nnnn.
A full list of INF variables interchangeable for UPPHF and UEEF are provided as long as the Database tables are being loaded according to the Interface Level parameter of the run.
Please see example below:
Example: Record # 50, Field # 20 Requirement for Employee Row id or Unit id:
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*From above IDIF set up, you should specify the variable name using 'UDF (DGD)' and enter the name in the Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: decode(~,'Y',decode([320803],'02',[320009],[350400]),[350400])
where: \\[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[320009] Identity eid_id \\
[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [320803] Journal Type. If the journal type is '02' Statistical Journal, then return [320009] Identify eid.id. Otherwise, return [350400]
Unit dun_id.
![Notes|Edit:Internal.DERIVATION+EXPRESSIONS]
[{InsertPage page='Internal.DERIVATION+EXPRESSIONS' default='Click to create a new notes page'}]
This page (revision-44) was last changed on 26-Nov-2021 10:22 by khiggsTop