Loading data with overlapping time intervals using generic datasource

[marval]

Dear gurus,

I've an issue/question with loading time-dependent master data from HCM-Infotype to ZEMPLOYEE via generic datasource based directly on Infotype table.

HCM Infotype-Data

PERNR; VALID_TO; VALID_FROM; SUBTYPE; VALUE
88888888; 99991231; 20140502; 9001; ABC
88888888; 99991231; 20230101; 9002; 123

Requirement:

• When Subtype 9001, I need VALUE (= ABC) in Attribute X in InfoObject ZEMPLOYEE
• When Subtype 9002, I need VALUE (= 123) in Attribute Y in InfoObject ZEMPLOYEE

Problem/Issue

As InfoObject only has PERNR and VALID_TO as keys, VALID_FROM information is lost when loading to InfoObject. Therefore time intervals overlap (both records with VALID_TO: 99991231) and are not created/inserted correctly with DTP-setting "handling duplicate data records".

Expected result

MasterData Table of ZEMPLOYEE should look like this after load:

/BIC/ZEMPLOYEE; DATETO; DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y

88888888; 01.05.2014; 01.01.1000; ;

88888888; 31.12.2022; 02.05.2014; ABC;

88888888; 31.12.9999; 01.01.2023; ABC; 123

Any ideas or best practise for this scenario?

Thanks!

[ranganath_korata]

Also, I suppose you are filtering SUBTYPES 9001 and 9002 in DTP. If not, then in the Start routine DELETE SOURCE_PACKAGE WHERE SUBTYPE <> '9001' or SUBTYPE <> '9002'

[ranganath_korata]

Hi Martin,

I can think of the following approach.

1) In the transformation, map SUBTYPE and VALUE to both /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y.

Write a simple field routine for /BIC/ZATTRBT_X such as IF SUBTYPE = 9001 then RESULT = VALUE.

for /BIC/ZATTRBT_Y:- IF SUBTYPE = 9002 then RESULT = VALUE.

If the above is executed, result should be:

/BIC/ZEMPLOYEE;DATETO;DATEFROM; SUBTYPE; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y

10000001; 99991231; 20230601; 9001; ABC; blank

10000001; 99991231; 20230801; 9002; blank; 123

10000002; 99991231; 20230101; 9001; XYZ; blank

10000002; 20230331; 20230101; 9002; blank; 123

10000002; 20231231; 20230401; 9002; blank; 456

10000002; 99991231; 20240101; 9002; blank; 789

Write an End routine with the following Pseudo logic:

Declare ITAB1, WA1, WA2, and FIELDSYMBOL

Assign RESULT_PACKAGE to ITAB1

SORT ITAB1 by /BIC/ZEMPLOYEE SUBTYPE DATEFROM

Loop at RESULT_PACKAGE assigning FIELDSYMBOL

READ ITAB1 into WA1 with KEY /BIC/ZEMPLOYEE = FIELDSYMBOL-/BIC/ZEMPLOYEE

IF WA1-SUBTYPE = '9001'.

READ ITAB1 into WA2 WITH KEY /BIC/ZEMPLOYEE = FIELDSYMBOL-/BIC/ZEMPLOYEE and SUBTYPE = '9002'

FIELDSYMBOL-DATETO = (WA2-DATEFROM) - 1

Above should handle the DATETO on the record where SUBTYPE is 9001

IF WA1-SUBTYPE = '9002'

.READ ITAB1 into WA2 WITH KEY /BIC/ZEMPLOYEE = FIELDSYMBOL-/BIC/ZEMPLOYEE and SUBTYPE = '9001'

FIELDSYMBOL-/BIC/ZATTRBT_X = WA2-/BIC/ZATTRBT_X

Above should take care of the blanks which we populated in the field routine

REFRESH WA1and2

ENDIF

ENDLOOP

CLEAR ITAB1

Thanks.

[marval]

Hey Ranganath,

first of all your idea looks quite promising - but while testing and debugging I found following behaviour:

In the endroutine (your pseudo-code): the first READ ITAB1 INTO WA1 ... statement within the LOOP AT RESULT_PACKAGE will always read the first record of ITAB1, which is the record with subtype 9001. This results in

1. the code running through first IF-Statement (IF WA1-SUBTYPE = '9001'.) for each loop and DATETO values getting changed for each record incorrectly
2. the code never running through the second IF statement (IF WA1-SUBTYPE = '9002'), which should take care of the blanks we pupulated in the field routine.

Do you have any idea on that?

When editing the field value from WA1-SUBTYPE = '9001' to '9002' in the second loop manually within the debugger, the result looks what I expect:

/BIC/ZEMPLOYEE;DATETO;DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y
10000001; 20230731; 20230601; ABC;
10000001; 99991231; 20230801; ABC; 123

PS: yes, I am filtering Subtypes 9001 and 902 via DTP.

Thank you very much for your help!

Kind regards,
Martin

[ranganath_korata]

Hi Martin,

Will 9001 and 9002 be the only two Subtypes for every employee? So will there be only two records for every employee? Can you share some more sample data, please?

Thanks.

[marval]

Hello,

there are also several other Subtypes (and records) available in InfoType for every employee.

HCM-InfoType (sample data):

RECORD; PERNR; VALID_TO; VALID_FROM; SUBTYPE; VALUE
1; 10000001; 99991231; 20230101; 0001; AAA
2; 10000001; 99991231; 20230101; 9000; BBB
3; 10000001; 99991231; 20230601; 9001; ABC
4; 10000001; 99991231; 20230801; 9002; 123
5; 10000001; 99991231; 20230101; 9003; 111
6; 10000002; 99991231; 20230101; 9001; XYZ
7; 10000002; 20230331; 20230101; 9002; 123
8; 10000002; 20231231; 20230401; 9002; 456
9; 10000002; 99991231; 20240101; 9002; 789

However, currently only subtypes 9001 and 9002 are needed in BW. Therefore record 3, 4 and 6-9 are relevant:

RECORD; PERNR; VALID_TO; VALID_FROM; SUBTYPE; VALUE
1; 10000001; 99991231; 20230101; 0001; AAA
2; 10000001; 99991231; 20230101; 9000; BBB
3; 10000001; 99991231; 20230601; 9001; ABC
4; 10000001; 99991231; 20230801; 9002; 123
5; 10000001; 99991231; 20230101; 9003; 111
6; 10000002; 99991231; 20230101; 9001; XYZ
7; 10000002; 20230331; 20230101; 9002; 123
8; 10000002; 20231231; 20230401; 9002; 456
9; 10000002; 99991231; 20240101; 9002; 789

Depending on subtype (9001 & 9002), we need either content of Field VALUE in attribute ZATTRBT_X for Subtype 9001 or ZATTRBT_Y for Subtype 9002. Time intervalls should be created accordingly:

Expected result (InfoObject master table)

/BIC/ZEMPLOYEE;DATETO;DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y
10000001; 20230731; 20230601; 9001; ABC;
10000001; 99991231; 20230801; 9001; ABC; 123
10000002; 20230331; 20230101; 9001; XYZ; 123
10000002; 20231231; 20230401; 9002; XYZ; 456
10000002; 99991231; 20240101; 9002; XYZ; 789

Thank you and kind regards.

[marval]

Thank you for your comment. I understand your input about the functionality and that it is how it should work technically.

My question aims more at how to load such HCM-Infotypes containing different subtypes with overlapping time intervals into InfoObject attributes. Can you provide information/best practise about how to model/code this master data loading scenario where Infotype data based on certain subtypes can be loaded into according InfoObject attributes with correct time delimitations/intervals?

HCM Infotype-Data

PERNR; VALID_TO; VALID_FROM; SUBTYPE; VALUE
88888888; 99991231; 20140502; 9001; ABC
88888888; 99991231; 20230101; 9002; 123

Expected result (InfoObject master table)

/BIC/ZEMPLOYEE; DATETO; DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y
88888888; 01.05.2014; 01.01.1000; ;
88888888; 31.12.2022; 02.05.2014; ABC;
88888888; 31.12.9999; 01.01.2023; ABC; 123

Thank you and kind regards!

[roland_szajko]

If there are multiple records with the same characterstic key + DATETO but with different DATEFROM values, you get an error during the load. If the "handling duplicate record keys" is set, then chronologically the last record is updated.

Both situations are correct and the system works as designed.

If you need it that way, then you should split the interval in the endroutine of the Transformation with an ABAP routine and create the desired time intervals yourself. The master data loading considers the DATETO as key.

[marval]

Thank you for your input.

Exactly, I use DTP setting "handling duplicate records" in order to let the system create the time intervals correctly. As you describe, only 1 (the last) of the 2 records is loaded from PSA to InfoObject, as InfoObject Key = PERNR & DATETO.

So master data table is updated with no errors (due to dtp-setting) but looks like this:

/BIC/ZEMPLOYEE; DATETO; DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y
88888888; 31.12.2022; 01.01.1000; ;
88888888; 31.12.9999; 01.01.2023; ABC; 123

This is wrong, as the attribute value 'ABC' is already valid from 02.05.2014, but this time interval is logically not created by the system. The correct master data table content should look like this:

/BIC/ZEMPLOYEE; DATETO; DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y
88888888; 01.05.2014; 01.01.1000; ;
88888888; 31.12.2022; 02.05.2014; ABC;
88888888; 31.12.9999; 01.01.2023; ABC; 123

After quite a bit of research I could not find a solution to fill different fields/attributes in one transformation depending on the value of a field (Subtype). As a quick low/no-code workaround I created separate TRFNs for each Subtype (using InfoSources in between - as only 1 TRFN per Source & Target is allowed) and DTP-Filter accordingly.

However, a reusable generic solution via Endroutine would be much appreciated.
Can you provide an example code on how to split the time-interval in the endroutine to fulfil my requirements?

Thank you very much!

[roland_szajko]

If we consider that the source is:

PERNR; VALID_TO; VALID_FROM; SUBTYPE; VALUE
88888888; 99991231; 20140502; 9001; ABC
88888888; 99991231; 20230101; 9002; 123

then the result is exactly as expected:

/BIC/ZEMPLOYEE; DATETO; DATEFROM; /BIC/ZATTRBT_X; /BIC/ZATTRBT_Y
88888888; 31.12.2022; 01.01.1000; ;
88888888; 31.12.9999; 01.01.2023; ABC; 123

Since you load PERNR 88888888 with a DATETO 99991231. There cannot be two values with the same PERNR and DATETO combination and the chronologically last record "wins". This is exactly how it should work.

The only part which is automatically added are the "infinite" intervals in case they are not part of the loaded data. This is required, since there must be always an interval with DATEFROM 01.01.1000 and there must be an interval with DATETO 31.12.9999.