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 85 added one line
\\
At line 87 added 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 91 added 5,025 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',~)}}[{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',~)}}[{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',~)}}[{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',~)}}[{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',~)}}[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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',~)[{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 page (revision-44) was last changed on 26-Nov-2021 10:22 by khiggsTop