LMTD LOADING DATE EFFECTIVE SPLITS#

The LMTD process can be used to insert date effective records onto date sensitive child tables.

An IDIF interface will need to be established. The interface must use the 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.
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, wage rate and a 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

*If the inbound file does not contain a header row, this part of the set up is not required.

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 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, but will not be able to update User Defined Fields.

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.

---

Notes #

Click to create a new notes page