This page (revision-21) was last changed on 18-Aug-2023 12:06 by Kevin Higgs

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

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
21 18-Aug-2023 12:06 7 KB Kevin Higgs to previous

Page References

Incoming links Outgoing links
LMTD LOADING DATE EFFECTIVE SPLITS
LMTD LOADING DATE EFFECTIVE SPLITS

Version management

Difference between version and

At line 1 changed 5 lines
Below are sample interfaces that show how to insert effective date records on to date effective tables. All samples show the required fields only to insert a new record. Additional fields to be updated may be added to these interfaces provided they are found on the subject table being updated.
The first line of these interfaces will make the assumption that the file will contain a header row identifying the column names. As the position of the data in the file will be established in the IDIF interface, the header row will not be required. Establishing a header row in the interface will instruct the interface to skip the first row of data found on the file. If the inbound file does not contain a header row, this part of the set up is not required.
Inserts a change in position to the P2K_HR_ASSIGNMENT_DETAILS table.
!!!LMTD LOADING DATE EFFECTIVE SPLITS
\\
The LMTD process can be used to insert date effective records onto date sensitive child tables.\\
\\
At line 7 changed 20 lines
!Load Away From Post Exceptions
||Rec\\#||Field\\#||Name||Beg\\Pos||End\\Pos||Field\\Type||Constant\\Value||Add to\\UK set||Derivation\\Expression||Notes
|0 |1| HEADER RECORD*| -1| -1| Char| | | Record Type must be Header Record
|1| 1 |TTX.EAS_ID.EEM_ID.EID_ID.PERSON_CODE| -1| -1| Char| | |
|1| 2| TTX.TIME_EXCEPTION_TYPE| -1| -1| Char| | |
|1| 3| TTX.START_DATE| -1| -1| Char| | {{{TO_CHAR(TO_DATE(RTRIM(~),
'MM/DD/YYYY'),'DDMon-YYYY')}}}|Formats date to match Personality standards
|1| 4| TTX.START_TIME| -1| -1| Char| | {{{concat(substr(~,1,2),substr(~,4,5))]}}}|The time in the file includes the :, the derivation is used to strip out the semi colon
|1| 5| TTX.END_TIME| -1| -1| Char| |{{{concat(substr(~,1,2),substr(~,4,5))}}}|The time in the file includes the :, the derivation is used to strip out the semi colon
|1| 6| TTX.DTC_ID.TIME_CODE| -1| -1| Char| |{{{ P2K_SMGLX('AESOP ABSENCE TRNSLT',UPPER(~))}}}|The derivation translates the value in the file to a time code using a user defined lexicon called AESOP ABSENCE
|1| 7| TTX.TSH_ID.EAS_ID.EEM_ID.TERMINATION_DATE| 0| 0| Char| ' '| |
|1| 8| TTX.EAS_ID.EEM_ID.TERMINATION_DATE| 0| 0| Char| ' '| |
|1 |9| TTX.TSH_ID.EAS_ID.EEM_ID.EID_ID.PERSON_CODE| 0| 0| Char| | {{{(:TTX.EAS_ID.EEM_ID.EID_ID.PERSON_CODE)}}}|This is required for [LMTD] to know which scheduled shift to update.
|1| 10 |TTX.TSH_ID.EAS_ID.PRIME_ASSIGNMENT| 0| 0 |Char| 1| | This is required for [LMTD] to know which scheduled shift to update
|1 |11| TTX.TSH_ID.SHIFT_DATE| 0| 0| Char| | {{{TO_CHAR(TO_DATE(RTRIM((:TTX.START_DATE)),
'MM/DD/YYYY'),'DDMon-YYYY')}}}|Formats date to match Personality standards
|1| 12| TTX.EAS_ID.PRIME_ASSIGNMENT |0| 0| Char| 1 | |Defaults to the prime assignment
|1| 13| TTX.ACT_DIF_POST_UPDATE_BUILD_SCHED| 0| 0| Char| | |Required so that the scheduled day is rebuilt.
|1|14| TTX.ACT_DIF_POST_INSERT_BUILD_SCHED| 0| 0| Char| | |Required so that the scheduled day is rebuilt.
An IDIF interface will need to be established. The interface must use the [Interface type|INTERFACE_TYPE] of 'EFFECTIVE SPLIT'. If the interface uses any other type the values will not load correctly.\\
\\
Below are sample interfaces that show how to insert effective date records on to date effective tables.
At line 28 changed 22 lines
!Load Replacement Exceptions
||Rec\\ #||Field\\ #||Name||Beg\\ Pos|| End\\ Pos||Field\\ Type||Constant\\ Value||Derivation\\ Expression||Notes
|0|1|HEADER RECORD*|-1|-1|Char| | | Must have Record Type of Header Record
|1|1|TTX.EAS_ID.EEM_ID.EID_ID.PERSON_CODE|-1|-1|Char| | |
|1| 2| TTX.EAS_ID.ASSIGNMENT_CODE| -1| -1| Char| | {{{P2K_SMGLX('AESOP PAY POSITION TRNSLT',
UPPER(~))}}}|The file includes a field which indicates which assignment to use, the derivation translates this using the specified user defined lexicon.
|1| 3| TTX.TSH_ID.EAS_ID.EEM_ID.EID_ID.PERSON_CODE**| -1| -1| Char| | |The file includes the person code of the employee who is away from post, so the LMTD will use this to locate which scheduled shift to update.
|1 |4| TTX.F_OVERRIDE_ASSIGNMENT| -1| -1| Char| | |The file included an overridden assignment code which is to be ignored. Since the file is character delimited, a foreign field was used to map its position within the file.
|1| 5| TTX.TIME_EXCEPTION_TYPE| -1| -1 |Char| | |
|1| 6| TTX.START_DATE |-1| -1| Char| |{{{TO_CHAR(TO_DATE(RTRIM(~),MM/DD/YYYY'),
'DDMon-YYYY')}}}|Formats date to match Personality standards
|1 |7 |TTX.START_TIME |-1| -1 |Char| | {{{concat(substr(~,1,2),substr(~,4,5))}}}|The time in the file includes the :, the derivation is used to strip out the semi colon
|1| 8| TTX.END_TIME| -1| -1| Char| |{{{ concat(substr(~,1,2),substr(~,4,5))}}}|The time in the file includes the :, the derivation is used to strip out the semi colon
|1 |9| TTX.DTC_ID.TIME_CODE| -1| -1 |Char| |{{{P2K_SMGLX('AESOP PAY TIMECODE TRNSLT',
UPPER(~))}}}|
|1| 10| TTX.TSH_ID.EAS_ID.EEM_ID.TERMINATION_DATE |0 |0| Char| ' '| |
|1| 11| TTX.EAS_ID.EEM_ID.TERMINATION_DATE| 0| 0| Char| ' '| |
|1 |12| TTX.TSH_ID.SHIFT_DATE| 0| 0| Char| |{{{TO_CHAR(TO_DATE(RTRIM((:TTX.START_DATE)),
'MM/DD/YYYY'),'DDMon-YYYY')}}}|Required to help the [LMTD] locate the correct scheduled shift to update. The derivation uses the TTX.START_DATE original value and formats it to match Personality standards.
|1| 13| TTX.TSH_ID.EAS_ID.PRIME_ASSIGNMENT***| 0| 0| Char| 1| | Used to help locate the correct scheduled shift to update.
|1| 14| TTX.ACT_DIF_POST_UPDATE_BUILD_SCHED| 0| 0| Char| | | Required so that the scheduled day is rebuilt.
|1| 15 |TTX.ACT_DIF_POST_INSERT_BUILD_SCHED| 0 |0 |Char| | | Required so that the scheduled day is rebuilt.
All samples show the required fields only to insert a new record. Additional fields to be updated may be added to these interfaces provided they are found on the subject table being updated.\\
\\
!!DISABLE TRIGGERS
If doing a __mass upload__ of data to a table containing workflow triggers, it is highly recommended to disable those triggers prior to performing the load. \\
\\
1) Prior to load - __ALTER TRIGGER P2K_XXXX_BIUD DISABLE;__\\
2) Complete the load\\
3) After the load - __ALTER TRIGGER P2K_XXXX_BIUD ENABLE;__\\
\\
!!Header Row
The first line of these interfaces will make the assumption that the file will contain a header row identifying the column names.
\\
!Header Row Set Up
||Rec\\ #||Field\\ #||Name||Beg\\ Pos|| End\\ Pos||Field\\ Type||Record\\ Type||Add to\\UK Set||Constant\\ Value||Derivation\\ Expression||Notes
|0|1|HEADER RECORD*|-1|-1|Char|Header| off | | | Must have Record Type of Header Record
\\
!!Sample # 1
!Insert a change in position to the P2K_HR_ASSIGNMENT_DETAILS table with an effective date.
\\
In this sample, the IEAS will be updated with a new position code as of a specific date. All the data being updated will be coming from the file. All position defaulting logic will be applied as part of this load (Updating the position code may impact the EASD.JOB_CODE, EASD.UNIT_CODE, EASD.WAGE_RATE, etc).
\\
!Sample #1 - File format
\\
PERSON_CODE,ASSIGNMENT_CODE,EFFECTIVE,CHANGE_CODE,POSITION_ID\\
3001,00,02/22/2021,POSITION_CHANGE,10789\\
3002,00,02/22/2021,POSITION_CHANGE,10985\\
3400,00,02/22/2021,POSITION_CHANGE,11700\\
3904,00,02/22/2021,POSITION_CHANGE,10001\\
\\
!Sample #1 - IDIF Set Up
\\
||Rec\\ #||Field\\ #||Name||Beg\\ Pos|| End\\ Pos||Field\\ Type||Record\\ Type||Add to\\UK Set||Constant\\ Value||Derivation\\ Expression||Notes
|0|1|HEADER RECORD*|-1|-1|Char|Header| | | | Must have Record Type of Header Record
|1|1|EASD.EAS_ID.EEM_ID.EID_ID.PERSON_CODE|-1|-1|Char| | Y| | | Person Code using in the IEID to identify the employee
|1|2|EASD.EAS_ID.ASSIGNMENT_CODE|-1|-1|Char| | Y | | | Assignment must match the code for the employee on IEAS
|1|3|EASD.EFFECTIVE|-1|-1|Char| | Y | |{{{TO_CHAR(TO_DATE(RTRIM((~)),
'MM/DD/YYYY'),'DD-Mon-YYYY')}}} | Formats date to match Personality standards. This deriviation is only required if the inbound date format does not match DD-MON-YYYY
|1|4|EASD.DCR_ID.CHANGE_CODE|-1|-1|Char| | | | | Optional Field - Change code as seen on IDCR
|1|5|EASD.DPS_ID|-1|-1|Char| | | | | Must be in the form of the ID of the P2K_CM_POSITIONS code (Positions may have more than 1 matching POSITION_CODE. So ID must be used).
\\
!IDIF Set Up
||Rec\\ #||Field\\ #||Name||Beg\\ Pos|| End\\ Pos||Field\\ Type||Record\\ Type||Add to\\UK Set||Constant\\ Value||Derivation\\ Expression||Notes
|0|1|HEADER RECORD*|-1|-1|Char|Header| | | | Must have Record Type of Header Record
|1|1|EASD.EAS_ID.EEM_ID.EID_ID.PERSON_CODE|-1|-1|Char| | Y| | | Person Code using in the IEID to identify the employee
|1|2|EASD.EAS_ID.ASSIGNMENT_CODE|-1|-1|Char| | Y | | | Assignment must match the code for the employee on IEAS
|1|3|EASD.EFFECTIVE|-1|-1|Char| | Y | |{{{TO_CHAR(TO_DATE(RTRIM((~)),
'MM/DD/YYYY'),'DD-Mon-YYYY')}}} | Formats date to match Personality standards. This deriviation is only required if the inbound date format does not match DD-MON-YYYY
|1|4|EASD.DCR_ID.CHANGE_CODE|-1|-1|Char| | | | | Optional Field - Change code as seen on IDCR
|1|5|EASD.DPS_ID|-1|-1|Char| | | | | Must be in the form of the ID of the P2K_CM_POSITIONS code (Positions may have more than 1 matching POSITION_CODE. So ID must be used).
\\
!!Sample #2
!Insert a wage rate change to the P2K_CM_POSITION_DETAILS table with an effective date.
\\
In this sample, the IDPS will be updated with a new wage rate, rate basis and a change reason as of a specific date.
\\
The inbound file will only contain the [position code|IDPS], wage rate and a [rate basis|X_RATE_BASIS]. All the other fields to be updated will come as constants in the interface.
The rate basis will need to be translated to match the values found on the [X_RATE_BASIS] lexicon on [IMLN].
\\
;:Note the the start and end position in the IDIF are set to 0 where columns are not found on the file.
\\
!Sample #2 - File format
ABC01,53.10,HR\\
ABC02,26.50,HR\\
ABC03,35.67,HR\\
MGRABC,90000,YR
\\
!Sample #2 - IDIF Set Up
||Rec\\ #||Field\\ #||Name||Beg\\ Pos|| End\\ Pos||Field\\ Type||Record\\ Type||Add to\\UK Set||Constant\\ Value||Derivation\\ Expression||Notes
|0|1|HEADER RECORD*|-1|-1|Char|Header| | | | Must have Record Type of Header Record
|1|1|DPD.DPS_ID_CHILD_OF.POSITION_CODE|-1|-1|Char| | Y| | | Position code as seen on IDPS
|1|2|DPD.WAGE_RATE|-1|-1|Char| | | | | Wage Rate to be updated
|1|3|DPD.RATE_BASIS|-1|-1|Char| | | |DECODE(~,'HR','07','01') | Changes the value in the file into the saved value of the lexicon HR = 07, YR = 01
|1|4|DPD.DCR_ID.CHANGE_CODE|0|0|Char| | | COLA INCREASE | | Optional Field - Change code as seen on IDCR
|1|5|DPD.EFFECTIVE|0|0|Char| | Y | 01-MAR-2021 | | Must be in the form DD-MON-YYYY
|1|6|DPD.DPS_ID_CHILD_OF.DEN_ID.ENTITY_CODE|0|0|Char| | Y |ABC123| | Entity Code as seen on IDEN
At line 51 changed 3 lines
;:*Record 0, Field 1 must have a Record Type of Header Record
;:**Record 1, Field 3 must have the Add to UK Set toggled ON
;:***Record 1, Field 12 must have the Add to UK Set toggled ON
;:*If the inbound file does not contain a header row, this part of the set up is not required.
At line 88 added 20 lines
!!Minimum set up requirements for effective date split screens
\\
The following chart shows the minimum set up required in an IDIF record to insert a new date effective record.\\
Once the fields found in the "Required" column have been defined, any value found in on the "Subject Table" can be updated. Please use the [Maintain Column Details|IMCD] form in the application for a listing of columns available to update based on you subject table.\\
\\
;:NOTE: this process will allow the update of [User Defined Columns|USER DEFINED COLUMN|USER DEFINED COLUMN (UDC)], but will not be able to update [User Defined Fields|USER DEFINED FIELD].
\\
\\
||Subject Table||Alias||Sample Screen||Required**||
|P2K_HR_ASSIGNMENT_DETAILS|EASD|IEAS|EASD.EAS_ID.EEM_ID.EID_ID.PERSON_CODE,\\EASD.EAS_ID.ASSIGNMENT_CODE,\\EASD.EFFECTIVE
|P2K_HR_PERSONALS|EPS|IEPI|EPS.EID_ID.PERSON_CODE,\\EPS.EFFECTIVE
|P2K_CM_POSITION_DETAILS|DPD|IDPS|DPD.DPS_ID_CHILD_OF.POSITION_CODE,\\DPD.DPS_ID_CHILD_OF.DEN_ID.ENTITY_CODE,\\DPD.EFFECTIVE
|P2K_CM_JOB_DETAILS|DJD|IDJB|DJD.DJB_ID.JOB_CODE,\\DJD.DJB_ID.DUN_ID.UNIT_CODE,\\DJD.DJB_ID.DUN_ID.DEN_ID.ENTITY_CODE,\\DJD.EFFECTIVE|
|P2K_CM_DEPARTMENT_DETAILS|DPD|IDDP|DDD.DDP_ID.DEPARTMENT_CODE,\\DDD.DDP_ID.DEN_ID_LEGAL.DEN_ID.ENTITY_CODE,\\DDD.EFFECTIVE
|P2K_CM_UNIT_DETAILS|DUD|IDUN|DUD.DUN_ID.UNIT_CODE,\\DUD.DUN_ID.DEN_ID.ENTITY_CODE,\\DUD.EFFECTIVE
|P2K_CM_GROUP_DETAILS|DGD|IDGR|DGD.DGR_ID.GROUP_CODE,\\DGD.DGR_ID.DUN_ID.UNIT_CODE,\\DGD.EFFECTIVE
|P2K_CM_WORK_RULES|DRWD|IDWR|DWRD.DWR_ID.WORK_RULE_CODE,\\DWRD.DWR_ID.DEN_ID.ENTITY_CODE,\\DWRD.EFFECTIVE
|P2K_SA_SALARY_RANGE_DETAILS|SSRD|ISSR|SSRD.SSR_ID.RANGE_CODE,\\SSRD.SSR_ID.DEN_ID.ENTITY_CODE,\\SSRD.EFFECTIVE
|P2K_SA_WAGE_STEP_DETAILS|SWSD|ISWS|SWSD.SWSS_ID.SWS_ID.SCALE_CODE,\\SWSD.SWSS_ID.SWS_ID.DUN_ID.UNIT_CODE,\\SWSD.SWSS_ID.SWS_ID.DUN_ID.DEN_ID.ENTITY_CODE,\\SWSD.SWSS_ID.STEP_CODE,\\SWSD.SWSS_ID.STEP_SEQUENCE,\\SWSD.EFFECTIVE
;:**The ADD TO UK SET toggle must be enabled for all of these fields. This toggle must be disabled for any other field added to the interface not listed in the chart.
This page (revision-21) was last changed on 18-Aug-2023 12:06 by Kevin HiggsTop