Dear All,
We require Replicate to have a source lookup functionality that would work with SAP Application (DB) endpoint, both for full load and CDC.
Brief background:
For some of the big tables in SAP (like CDHDR, CDPOS, BSEG, **bleep**LFLEXA etc.) we're using passthrough filters since we do not require the full data set (last 10-15 years sitting on SAP). This part is fine. However;
- When we want to filter the change tables (CDHDR (header) and CDPOS(detail) ) on change date field, the change date field only exists in the header, not in the detail table.
- Another example is the **bleep**LFLEXA table. What we require from **bleep**LFLEXA is only from 2020 onwards. Therefore, we use the passthrough filter on the Fiscal year field on **bleep**LFLEXA. This part is fine. However, we still have to look for **bleep**LFLEXA for the open items that are before 2020 by using the BSIK table, that includes open items. Normally, I would write a SQL query like:
select * from **bleep**LFLEXA where **bleep**LFLEXA.key_field in (select key_field from BSIK)
I attached an illustration of the requirement as well.
The solution should work for both Full load and CDC (so I believe there should be a mechanism to constantly scan BSIK for new rows and bring the corresponding **bleep**LFLEXA entries as captured changes).
Last but not least, when an open item in BSIK is cleared (like an invoice is paid), the line in BSIK gets deleted. But the corresponding line in **bleep**LFLEXA is not, but only receives and update and a new row.