Updating database table based on BAPI response in case of error

[Former Member]

Dear Experts,

My scenario includes pulling records from database stagging table, and push one record at a time to BAPI_ALM_NOTIF_CREATE.

I am using sender JDBC adapter(with select query and update query to mark records as processed).

My question is : Once XI selects records, immediately status is updated in stagging table for the selected records as processed. what if BAPI could not create document? In this case I need to update status of that record as "Not processed".

What should I use?

If BPM, then please tell me the steps.

Is this possible without BPM?

What are the chances of failure between XI and BAPI?

Please provide your inputs.

Thank you.

Div

[former_member187339 · Accepted solution]

Hi Divyesh,

>>What should I use?If BPM, then please tell me the steps.

I would suggest to try using ResponseOnewayBean in sender JDBC adapter, so that you can make the complete scenario as a synchronous one... JDBC>XI>BAPI>BAPI.response>XI--JDBC (receiver channel)

>>Is this possible without BPM?

If above suggestion didn't work then use BPM.. It should be:

1. receive (from DB)

2. Transfiorm

3. Sync send (to bapi)

4. Transform (if required)

5. send DB (to update the successful records details)

>>What are the chances of failure between XI and BAPI?

Very less... If communication failure happens then you restart the message in PI and those records will be updated.. But before making live you need to make sure that the BAPI should nor fail at any time

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

Thanks for your quick reply.

In my scenario, I do not have to send BAPI response to database records.

My scenario includes just pulling records from one stagging table and push them one by one in BAPI.

For this JDBC sender adapter will be used with select query like "select * from tablename where process_flag =0

and Update sql will be in JDBC configuration like "update process_flag=1 where process_flag=0.

But these will update all the records in staggingtable immediately after JDBC adapter selects the records.

I am comcerned about:if BAPI fails, then I need to reset the process_flag in the records which have been pulled by XI earlier, so that It can be processed again.

Thanks Again Suraj,

Div

[former_member187339 · Accepted solution]

Hi Divyesh,

I understood your problem earlier.. And had an idea on the current design which you are referring to..

but in this case the BAPI is asynchronous, so you will never know whether it was success or failure.. So you need to redesign in such a way that you get a response from bapi of the successful records.. and in this was you have the count of record that was successfully updated in SAP R3.

So the next receiver adapter can reset the failed records flag in Database

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

My new design of this scenario would be :

Jdbc sender adapter to Proxy

Under Jdbc sender adapter:

Select query based on process_flag value =0

Update query set process_flag =1 where process_flag =0

And then XI will push selected records to server proxy and server proxy will call Bapi and then fill tables parameter with the status and error message of every records which are failed.

Now based on proxy response which contains error records info, SAP XI has to update stagging table based on proxy response.

Based on Proxy response,SAP XI will finally update stagging table records with process_flag = 2.

Now my concern is : as sender JDBC channel will poll stagging table every 1 minute and select the records based on process_flag = 0 then also update staggingtable records with process_flag = 1 to mark it as taken by XI. So after selection and updation only proxy call will be done right? So for every polling of jdbc adapter ,proxy call will happen?

How to achieve this?

Do i need to use BPM? please suggest steps.

Thanks Suraj,

Div

[former_member187339 · Accepted solution]

Hi Divyesh,

Your steps are correct..

1. JBDC will read record with code = 0 and make it 1...

2. Once the bapi process is completed all these read records shoudl be made as 2 (if completed successful) else 0 if bapi went into some error.. so that again the jdbc adapter can poll these records and give back to bapi for processing...

Now your questions

>>So after selection and updation only proxy call will be done right? So for every polling of jdbc adapter ,proxy call will happen?

YEs every poll of jdbc will result in a proxy call and every time it will contain next set of records (as the earlier one have already marked as 1)

>>How to achieve this?

Since this is a synchronous scenario JBDC->XI->Proxy and reverse, I have suggested to use responseonewaybean as a module in sender jdbc adapter (because sender jdbc cannot act synchronously thats why you need to use this module)..

>>Do i need to use BPM? please suggest steps.

and if this is not working then go for BPM..the steps as i mentioned in my first thread..

Regards

Suraj

[Former Member · Accepted solution]

>

> Hi Divyesh,
> 
> Your steps are correct.. 
> 
> 1. JBDC will read record with code = 0 and make it 1...
> 2. Once the bapi process is completed all these read records shoudl be made as 2 (if completed successful) else 0 if bapi went into some error.. so that again the jdbc adapter can poll these records and give back to bapi for processing...
> 
> Now your questions
> >>So after selection and updation only proxy call will be done right? So for every polling of jdbc adapter ,proxy call will happen?
> YEs every poll of jdbc will result in a proxy call and every time it will contain next set of records (as the earlier one have already marked as 1)
> 
> >>How to achieve this?
> Since this is a synchronous scenario JBDC->XI->Proxy and reverse, I have suggested to use responseonewaybean as a module in sender jdbc adapter (because sender jdbc cannot act synchronously thats why you need to use this module).. 
> 
> >>Do i need to use BPM? please suggest steps. 
> and if this is not working then go for BPM..the steps as i mentioned in my first thread..
> 
> Regards
> Suraj

Hi Suraj,

Thanks a lot for your support.

It would be good if you can provide your inputs.

Stagging table records status:

Flag = 0 (XI will poll records)

Flag = 1(XI has polled records and XI will se tthis flag by JDBC adapter)

Flag =2 (should be done based on response from Proxy Tables parameter) Proxy will set tables parameter which will include info on error message and type.

(Now based on new requiremets: for error records status should not be reset to 0 so that XI can poll records again, now stagging table should contain error records(for which proxy can not create notification in SAP) with error message and status flag 2)

Now scenario would be JDBC to Proxy.

Polling interval for JDBC adapter should be 1 minute.

So I think status 0 to 1 will be done by XI after immediately selecting records.

But how about Status 2 that has to be set only for error records only and with error information.

I have to update stagging table records with status 2 and error info based on proxy table response. How this can be done?

This updation will be done in case of error only.

I was thinking this aproach:

We can have two interfaces.

First interface will be jdbc to proxy:

JDBC adapter select and update records with flag =1 and then send to proxy call and create document in SAP. At SAP side if document can not be created then they will store that error info in some table.

Second Interfgace:

Client SAP Proxy will periodically run and send these error records info to XI and XI will update stagging table records accordingly.

Suraj, What is your sugestion in this solution? Do I need to use Responseonewaybean?

Thanks Suraj

Div

[Former Member · Accepted solution]

Hi Suraj,

Sorry message was not formatted properly.

Hi Suraj, Thanks a lot for your support. It would be good if you can provide your inputs.

Stagging table records status: Flag = 0 (XI will poll records)

Flag = 1(XI has polled records and XI will se tthis flag by JDBC adapter)

Flag =2 (should be done based on response from Proxy Tables parameter) Proxy will set tables parameter which will include info on error message and type.

(Now based on new requiremets: for error records status should not be reset to 0 so that XI can poll records again, now stagging table should contain error records(for which proxy can not create notification in SAP) with error message and status flag 2)

Now scenario would be JDBC to Proxy.

Polling interval for JDBC adapter should be 1 minute.

So I think status 0 to 1 will be done by XI after immediately selecting records.

But how about Status 2 that has to be set only for error records only and with error information.

I have to update stagging table records with status 2 and error info based on proxy table response. How this can be done? This updation will be done in case of error only.

I was thinking this aproach:

We can have two interfaces.

First interface will be jdbc to proxy:

JDBC adapter select and update records with flag =1 and then send to proxy call and create document in SAP.

At SAP side if document can not be created then they will store that error info in some table.

Second Interfgace: Client SAP Proxy will periodically run and send these error records info to XI and XI will update stagging table records accordingly. Suraj, What is your sugestion in this solution? Do I need to use Responseonewaybean?

You can also suggest any alternative solution.

Thanks Suraj

Div

[former_member187339 · Accepted solution]

Hello...

The approach which you thought is also good.. but you need to maintain two separate interfaces... buit you dont need BPM or the responseonewaybean...

when you make BPM or jdbc (sync)> XI> Proxy (sync), you have only one interface...

Check the pros and cons for both the approach and take the decision.. one more thing, when XI picks the records from DB it immediately set it to 1, but if you make the senario synchronous, you will get back the response immediately (make after 2-3 sec) and you can then change the error record status to 0 and correct one to 2...

So

1. with BPM your scenario looks like http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=%28J2EE3414800%29ID1723107350DB0073121252144225032... (change File with JDBC)

2. With responseonewaybean

http://wiki.sdn.sap.com/wiki/display/XI/File-RFC-File%28Without%20BPM%29

Change file with jdbc and rfc with Proxy

More on responseonewaybean http://help.sap.com/saphelp_nw04/helpdata/en/45/20cc5dc2180733e10000000a155369/content.htm

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

I am planning to avoid BPM.

Now Client has suggested to send email the error info instead of setting status in stagging table.

I will go for two interfaces solution.

Second interface would be proxy to mail.

THanks Suraj for your support.

Div

[Former Member · Accepted solution]

Hi Suraj,

Scenario : Jdbc sender adapter to Proxy

In case, SAP can not create docuement, it will send error status and infor as Proxy tables parameter.

Now I need to send email this eror info only if Proxy table contain any error record.

So should i gofor two separate interfaces or CAn I use ResponseoneWaybean and make JDBC to PROxy as Syn scenario.

But in this case, response from proxy table may be blank(in case if there are no error records info in Proxy table)

What you suggest? Suraj

Thanks

Div

[former_member187339 · Accepted solution]

Hi Divyesh,

Either have BPM or go for two separate interface and in that you can decide whether to send mail or not...

i would not suggest you to use responseonewaybean now....

Regards

Suraj