A distributed join across SAP BTP and S/4HANA in a single ERP round trip

The procurement portal built for Penguin Solutions splits ownership on purpose. SAP S/4HANA stays the system of record for purchase orders: items, schedule lines, quantities, dates, deletion and delivery flags. The portal keeps only what S/4HANA has no place for, in PostgreSQL on SAP BTP: the collaboration status of each PO and each PO item (PFA, NEW, SUPPLIER_INITIATED_CHANGE, CANCELLATION_PENDING and so on), plus approvals, change requests and history.

The buyer portal opens on tabs like Pending actions, Open and Closed. Each tab filters by portal status, but every row it shows is S/4HANA data. Neither system can answer that query alone: S/4HANA has never heard of SUPPLIER_INITIATED_CHANGE, and PostgreSQL holds no schedule lines. Each request has to be a join across two systems that share nothing but the PO number.

Three options that do not work

• Replicate S/4HANA into the portal. That means keeping a second copy of the ERP in sync and deciding which copy wins when they disagree, which defeats the point of S/4HANA being the system of record.
• Fetch everything from S/4HANA and filter in Node.js. Most open POs are not in the requested tab, so most of the transfer through the Cloud Connector is thrown away, and paging stops meaning anything.
• Ask S/4HANA for each PO separately. An N+1 over an on-premise link: one round trip per PO, on every page load.

Step 1: reduce where the predicate lives

The status predicate only exists in PostgreSQL, so it runs there first. CAP queries PO and POItem in parallel for the statuses of the requested tab and takes the union of the PO numbers. A PO belongs in a tab if its header or any of its items has a matching status, so a PO with a single item awaiting the buyer still shows up under Pending actions.

The other predicates are placed the same way. A search term is matched against PO number and vendor in PostgreSQL first. If it matches nothing there, it is sent to S/4HANA as a purchasing-group (buyer) predicate instead. Purchasing organisation is not filterable in the S/4HANA entity, so it is checked against the organization column in PostgreSQL. If the key set ends up empty, the request returns straight away and S/4HANA is never called.

Step 2: ship the keys, not the rows

The key set can hold thousands of PO numbers, too many to fit in a $filter on a GET URL. The S/4HANA side exposes a custom POFilterSet entity that takes them in the body of an OData V2 deep insert instead. The keys go in the PONumberSet navigation along with the S/4HANA-side filter (material lines only, not fully received, plus the user's own filters), a sort on confirmed date and a fetch cap. The matching schedule lines come back in the response, under SchLineSet. It is a POST used as a parameterised read: a semi-join, run in the ERP, against keys computed in the cloud.

The call goes through the Destination service and the Cloud Connector with principal propagation, so S/4HANA runs it as the buyer and applies its own authorisations on top of the portal's.

Step 3: join back and aggregate

S/4HANA answers in schedule lines, the buyer sees one card per PO, and every card needs the portal status of its PO and items. CAP reads the overlay rows for the PO numbers that actually came back, as two parallel batch queries, and builds hash maps keyed on PONumber and PONumber|LineNumber. Each line is then matched in memory:

• It is a left outer join from the S/4HANA side. A line with no overlay row still shows, with an Only in SAP badge, instead of disappearing.
• Lines are grouped by PO: quantities are summed, deleted and delivered flags become “any line is”, and the header and item statuses are merged into the set of badges on the card.
• A hasActionableItems flag is computed in the same pass, so the UI can highlight POs waiting on the buyer without asking again.

Predicates that span both systems

The Closed tab shows a PO if S/4HANA has deleted or delivered its lines or the portal marked it cancelled or closed. That disjunction cannot run in either system alone. The fix is to make step 1 deliberately broader: the key set also includes POs in any status they could hold while their lines are deleted in S/4HANA. The exact predicate is then evaluated per PO after the join, once both halves of each row are in hand.

Every other tab gets a final check too. A row is kept only if its badges intersect the statuses of the tab. The coordinator does not assume the remote side honoured the key set exactly, so nothing can appear in a tab it does not belong to.

Paging after the join

The UI pages by PO, while S/4HANA returns schedule lines, and a PO can have any number of them. Paging in S/4HANA would cut POs in half, so CAP fetches up to 5,000 lines in a single call, pages the grouped rows itself with skip and top, and returns __count so the UI knows the total. If S/4HANA ever returns the full cap, CAP logs a warning saying the result may be truncated, so the limit is visible rather than silent. That trade-off fit Penguin's volumes; paging at PO level inside S/4HANA would be the next step if they outgrew it.

The same plan on the supplier side

The supplier portal lists its schedule lines with the same shape. There, the key set is built from the POs that belong to the supplier's own vendor IDs and purchasing organisations before any status filter applies, so the step that reduces the query also scopes it to that supplier.

Why it works

• Each predicate runs where its data lives: status in PostgreSQL, material and delivery state in S/4HANA, and the predicates that need both after the join.
• Only keys cross the boundary. The portal never copies schedule lines, and S/4HANA never has to learn about portal statuses.
• The cost does not grow with the page. A listing is at most a few parallel PostgreSQL queries and one S/4HANA call, never one call per PO.

Clean core, by construction

SAP's clean core guidance asks customers to keep S/4HANA close to standard and build extensions side by side on SAP BTP, so that the ERP can be upgraded without breaking them. The distributed join is what made that possible here.

• No portal data inside the ERP. The collaboration statuses that drive every tab live in PostgreSQL on BTP. Without a join across systems, they would have needed new custom fields on the purchasing tables or extra custom tables in S/4HANA, which are exactly the changes clean core tries to avoid.
• No modifications to SAP standard. The S/4HANA side is limited to OData services in the customer namespace and an output program that pushes POs to the portal. SAP's own code is never changed.
• A narrow, explicit contract. Everything the portal needs from the ERP goes through those few services. After an S/4HANA upgrade, that interface is the only thing to regression-test, and the portal itself can be upgraded or redeployed without touching the ERP.

It also keeps maintenance low for Penguin. There are no replication jobs to schedule or monitor, no second copy of purchasing data to reconcile, and no sync conflicts to resolve, because S/4HANA is read live on every request. The portal runs on managed BTP services and deploys as its own multi-target application on Cloud Foundry, independently of the ERP's transport landscape.