JDBC To IDoc Asynchronous Scenario with Update Statement in Response

We often come across a scenario where the middle ware(PO) picks the records from the database using a SELECT statement where the Flag field is not flagged and mark them flagged using the UPDATE statement. These both statements execute at sender channel.

Problems with this Approach:

1. Message is in Error in SAP PO- When the message will go into error in PO, those records will not get picked again as the sender channel has already marked flag of picked records as successful.
2. Idoc went into error in SAP ECC/SAP S4 system- When the Idoc posted by PO went into error in S4 system, those records will not get picked again and we will have to provide all the records information to DB team and DB team will have to do the manual work to reset the flags of those records. 

Flag Statuses:

1. Flag=” means the records are ready to be picked.
2. Flag=’P’ means the records are under processing.
3. Flag=’S’ means the records are successfully posted.
4. Flag=’E’ means the records posted contains error.

Resolution of above problems:

We have provided a solution to trigger a proxy from S4 when the Idoc gets successfully posted or is in error. The approach will to select the records where the Flag=”(Blank) and when they will get picked, PO will change the Flag=P(Processing).

When the Idoc gets successfully posted then, Proxy will send the response from S4 where it will collect all the primary key values of Database from Idoc and send Flag=’S'(Successful) to PO otherwise it will send Flag=’E’ with all primary key values to PO to create the further update statements.

The proxy function module will be called from the standard Idoc function module named as ‘ZBAPI_IDOC_INPUT1‘ which will be called at the end of this Idoc module.

Architecture of this approach:

The below diagram explains that PO is picking the data after given intervals and creating IDoc out of this. When the Idoc gets successfully posted the Proxy is sending the primary Keys value in response along with Flag status ‘S/E’. PO is creating multiple update statements from response data and executing them in a batch at database.

ESR part of Response:

Below is the structure created for structure and we can map them as per our need. I will not be going in detail into this.

ABAP Proxy Structure and Code

In S4/ECC System, we will create the proxy on the exposed outbound interface in sproxy tcode. Once the proxy is generated a method will be created automatically(here it is created with name- JEPOSTINGS_RESPONSE_OUT and can be different in your case) which will be called from ZBAPI_IDOC_INPUT1.

Method Automatically Generated

Passing data to Proxy & Calling the method

Populating the Primary Key Fields

Passing primary key values and status of Idoc in flag field. ‘msgty’ is a standard status field which contains the status of the idoc. It contains values as ‘S'(Successful) and ‘E'(Error) and have character range of 1.

Proxy Structure

First Iflow(JDBC to Idoc) Sender Channel config:

First SQL statement is picking the records where Flag is empty and the second update statement is updating those flags to ‘P'(Processing).

Second Iflow(Proxy to JDBC) Receiver JDBC Channel Config:

We can check this parameter if we have empty values in our data which we don’t want to be treated as null by PO.

From the proxy response we are creating multiple queries and to execute them in a batch we can enable batch mode in advance mode. This improves the performance of the interface and query execution.

The second Iflow will successfully mark the flag to successful(Flag=’S’) if Idoc is posted successful and with Error if Idoc is in error in S4.

If the records picked fails in SAP PO due to any data error or mapping error or any other issue then:

• An alert will be triggered to the concerned parties.
• The Flag status will remain as ‘P’ and sender party will have to correct the data and change the status of the flags manually for PO to pick again.