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 126 changed 2 lines
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field.
Otherwise, use the Unit dun.id on the field for all other situations.
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee and if the journal entry is reporting for 'Hours', then use the Identity eid.id on the Interface File field. Otherwise, use the Unit dun.id on the field for all other situations.
At line 129 changed 5 lines
*In the derivation expression, enter: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
where: \\[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal \\[[320009] Identity eid_id \\
[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400]
Unit dun_id.
*In the derivation expression, enter:
;: decode(~,'Y',decode([[320803],'02',[[320009],[[350400]),[[350400])
;:where:
;:[[320803] Journal Type (DGA), 01 - Financial Journal, 02 - Statistical Journal
;:[[320009] Identity eid_id
;:[[350400] Unit dun_id (DUN)
* This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then decode the value from [[320803] Journal Type. If the journal type is '02' Statistical Journal, then return [[320009] Identify eid.id. Otherwise, return [[350400] Unit dun_id.
Example: Record # 50, Field # 10 Requirement: Work Date or Pay Period
*If the Pay Header Group user-defined field (UDF) 'PROJECT HOURS BY PERSON' is 'Y', this means that the employee is an 'ADMIN' employee. In this case use the pay period on the interface file field, otherwise use the GL Effective date on the Interface File field.
*From above [IDIF] set up, you should specify the Variable Name using 'UDF (DGD)' and enter the UDF name in Constant Value field, then the value of UDF will be returned and represented as ~ in the derivation expression.
*In the derivation expression, enter: \\
;:decode(~,'Y',[320102],to_char(to_date([[320756]),'DD/MM/YYYY'))
;: where:
;:[[320102] Pay Period
;:[[320756] GL Eff Date (PJD)
*This derivation expression means decode the value of ~ from UDF. If the value is 'Y', then return the value from [[320102] pay period. Otherwise, return the value from [[320756] GL Effective date with the date format of 'DD/MM/YYYY'.
!IDIF - Derivation Expression - P2K Internal Functions
You have the ability to call some P2K internal functions in the derivation expression under the guidance of a consultant. The following example is for UPPHF Payroll History interface and is used to demonstrate the Derivation Expression only. In this example, the Element value of 'HOURS COMP TIME' will be returned for the Variable Name 'Pay Header pph_id'.
!IDIF - Derivation Expression - P2K Internal Functions with Parameters
You have the ability to call some P2K internal functions in the derivation expression under the guidance of a consultant and pass the following internal parameters in order to perform some internal calculation:
;#PPH_ID#
;:This value must be in capital letters; this #PPH_ID# will be replaced by the current pay header's pph.id.
;#EEM_ID#
;:This value must be in capital letters; this #EEM_ID# will be replaced by the current employment's eem.id.
;#EID_ID#
;:This value must be in capital letters; this #EID_ID# will be replaced by the current identity's eid.id e.g. the Element PC value of 'HOURS O/T' will be retrieved first and is represented as ~ in derivation expression. The derivation expression will return the Element value of 'HOURS COMP TIME' for #PPH_ID# and add it to the value of ~ .
;:You may perform some internal arithmetic calculation in the derivation expression to return some prorated amount.
;:Value of a PC in 'HOURS O/T element * Benefit Element 'HOURS COMP TIME' Value of 'HOURS O/T' Element
!IDIF - Call UserCalc Function
You can call the UserCalc function at each Record Number and each Field Number. You should set up the variable name to be 'User Calc', and specify the UserCalc name in the Constant Value field.
The field type must be defined with a Char, Number or Date type. The Return Value UserCalc function on IMUC screen must match the values (Char, Number or Date) with the IMUC RET command to return the corresponding Char, Number or Date.
The data base tables available for UserCalc are:
;:at company level: DEN / DLN / DDP / DDD / DUN / DGR / DGD / DGV
;:at employee level: EID / EPS / EEM / EAS / EASD / PPRU / PPRC
!IDIF - Derivation Expression - BYPASS Capability
*On IDIF Derivation expression, you may decode the Variable Name using the ~ character and return the word 'BYPASS' to bypass the Detail record entry that is with certain criteria.
* The 'BYPASS' criteria can be set up for Record Type = 'Qualify Record', 'Detail Record', and should not be used for Header and Trailer records.
* If the return value of any IDIF Record Number/Field Number = 'BYPASS', then this Detail record will be bypassed and will not be written to the Interface file.
;:Example:
*to include only Departments start with 'P' on interface file: decode(rtrim(substr(~,1,1)),'P',~,'BYPASS')
* to include only Departments ends with 'C' on interface file: decode(substr(~,length(rtrim(~)),1),'C','BYPASS',~)
* to bypass Cost Centers that has '????' in second segments: decode(SUBSTR(~,5,4),'????','BYPASS',SUBSTR(~,5,4))
* to include Journal Entries with Account Numbers over 40000 in segment 5: decode(greatest('40000',substr(~,16,5)),'40000','BYPASS',substr(~,16,5))
!IDIF - Derivation Expression GOTO#nn#nnn
* when processing IDIF records of the same Record Type (e.g. Detail Record), you may want to skip some Record # Field # based on certain criteria and resume processing of the same Record Type at a later Record# Field# onward
* the following GOTO capabilities are available for all Record Types in Derivation Expression:
GOTO#nn#nnn where the 1st nn is the Record #, the 2nd nnn is the Field #
* since the IDIF entries are processed in chronological order, GOTO#nn#nnn must be for a Record# Field# that is after the current Record# Field#
* if an incorrect GOTO#nn#nnn is specified, or the #nn#nnn is NOT for the same Record Type, or if it is for a previous Rec# Field#, an exception message will be issued, the GOTO statement cannot be executed after skipping the records, you must verify the Interface File from this point onward
* multiple GOTO#nn#nnn can be used within the same Record Type
* if an Invalid GOTO is encountered in IDIF definition, the Trial mode parameter is set to 'Y' in order to prevent the Updating of the user-defined fields from the
Record Type '92-Update Record'
* for XML File Format, the GOTO#nn#nnn can be used to skip certain XML Tags and carry on the processing
This page (revision-44) was last changed on 26-Nov-2021 10:22 by khiggsTop