Payment terms mismatch

Dears, I’m trying to calculate Payment terms mismatch. For MM related Invoices, Payment terms should be equal to PO payment terms.
For this motivation, I’m trying to obtain the field “EKKO”.“ZTERM” to compare it against “BSEG”.“ZTERM”. But I’m block because of “no not have a common parent in the schema” message.

I try different approaches using pullout functions but I’m stuck . Can you please advise? – below the relations available and the error message

My formula should look like something like this:
CASE WHEN (“EKKO”.“ZTERM”)<>(“BSEG”.“ZTERM”) THEN ‘NOK’ ELSE ‘OK’ END

I read that somehow this can be achieved through the DOMAIN_TABLE but to be honest I could not manage to get it. Below I’m including a screenshot of the data model mapping.

Hi Cristian,

You get this message probably because there is no 1 to 1 relation with the BSEG table and the EKKO table. In other words, Celonis is not able to link instances from both tables uniquely to each others. Can you confirm if that is the case?
If there is a 1 to 1 relation, I would advise to link the EKKO table directly to the case table ‘BSEG’, this will resolve this type of issues. the RBKP and EKKO table still should be able to communicate to each other since the have a common parent.

Kind regards,
Jan-peter

Hi Jan-Peter,
I attempt your suggestion on our QA environment

But the datamodel failed to load : Could not load data model. Load failed. Continue to use existing load. Error Message: Cycle in table joins detected. Cycle is within these tables: bkpf, bseg, ekko, mara, rbkp, rseg. Celonis Support ID: ef69b213-9741-4fb2-bf57-98c8d59bcadb

any other idea? advice?

Hi Cristian,

you get the error because we don’t support cycle joins in Celonis.
The problem lies in the data model and the way tables are joined. Between the BSEG and EKKO tables, you need a common parent. I guess that there is a join in your DM that is in the wrong direction and thus, prevents the usage of both tables inside the same query.
From your screenshot, it is very hard to tell which tables are actually joined together. Can you please align the tables in your datamodel differently such that we can see which tables are actually joined, and send a full screenshot of your datamodel?

Thanks!

Best
David

Hi David,
Thanks a lot for your help, please see below our data model. Temporary I remove the connection between EKKO and BSEG cause I think it was raising the “cycle error in join” The relation i was trying to build was EKKO.MANDT,EKKO.EBELN,EKKO.SOURCESYSTEM, BSEG.MANDT ,BSEG.SOURCESYSTEM, BSEG.MANDT

Hi Cristian,

thanks for the screenshot. So we think that the Tables have the following join directions, is that correct?

EKKO 1: N RSEG
RSEG N : 1 RBKP
RBKP N : 1 BKPF
BKPF 1 : N BSEG

In this case, I guess that EKKO and BKPF work in the same query. Both tables have the same parent, which is RSEG. Because of the 1:N relationship between BKPF and BSEG, EKKO and BSEG don’t have a common parent table. In Fact, I think you cannot use the BSEG table with RSEG and RBKP as well due to the same reason.

You can however use a PU function to aggregate values from BSEG to BKPF (PU_X(“BKPF”, “BSEG”.“X”)) . You can use this aggregated value then in the same query together with EKKO.

Best
David

Hi David,
Unless I’m missing something EKKO and BKPF does not have the same parent, The only connection EKKO has in the data model is EKKO 1:N RSEG.

Any idea how to adjust the data model or implement a PLQ to be able to compare (“EKKO”.“ZTERM”) and (“BSEG”.“ZTERM”) in the same query?

All the best,
Cristian

Hi Cristian,

I’m pretty sure they have the same common parent, because “grandparents” also count in this definition. If you can use EKKO and BKPF in the same OLAP table without using PU functions, that is the case.

For changing the DM I cannot give any advice because I don’t know the AP process :frowning: In your current DM you could use a PU function to pull BSEG entries to BKPF, but that’s probably not what you want, because in the end you want to do a N:M join (when I understood you correctly), which is not supported in Celonis. In general, to fix this, you could perform such a join on database level, but I’m lacking domain knowledge to give any advice here.

Maybe somebody else experienced the same issue in AP?

Best
David

Hi,

We have similar analysis in AP data model, we followed below steps to get through this N-N relationship issue.

-At database level in BKPF table we pulled MAX(ZTERM) and MAX(EBELN) from BSEG
-In Data model we joined BKPF and EKKO
-In Analysis you can now compare BKPF.ZTERM with EKKO.ZTERM

  • Our data model case is based on Invoice header (BKPF)
2 Likes