ABAP Proxy to JDBC : UPDATE_INSERT issue.

[naimkhans_babi]

Dear Friends

I am inserting one ekpo records to MS SQL server. my structure and records are below.

<?xml version="1.0" encoding="UTF-8" ?> 
- <ns0:MT_EKPO_RECEIVE xmlns:ns0="http://gspcgas.net/asset_rfid">
- <StatementName>
               - <dbTableName action="UPDATE_INSERT">
                  <table>EKPO</table> 
- <access>
           <EBELN>4500000687</EBELN> 
           <EBELP>00010</EBELP> 
          <MATNR>000000000100000012</MATNR> 
          <MENGE>1.000</MENGE> 
          <MEINS>EA</MEINS> 
          <WERKS>1000</WERKS> 
          <LGORT /> 
          <AEDAT>2011-12-12</AEDAT> 
          <BUKRS>1000</BUKRS> 
          <ANLN1>010023000315</ANLN1> 
          <PSTYP>0</PSTYP> 
          <KNTTP>A</KNTTP> 
          <LOEKZ /> <-- during insertion I am not updating this field ( this is for item deletion) 
  </access>
  - <Key1>
          <EBELN>4500000687</EBELN> 
          <EBELP>00010</EBELP> 
  </Key1>
  </dbTableName>
  </StatementName>
  </ns0:MT_EKPO_RECEIVE>

this works absolutly fine and record inserted in the MS SQL server.

problem occurs when in second run my user is changing the fields means he deletes the item from the PO, but it is not reflecting in the data base. only I can see previously created entery not the changed one. I have below payload in RWB for the receiver communication channel.

<?xml version="1.0" encoding="UTF-8" ?> 
- <ns0:MT_EKPO_RECEIVE xmlns:ns0="http://gspcgas.net/asset_rfid">
- <StatementName>
               - <dbTableName action="UPDATE_INSERT">
                  <table>EKPO</table> 
- <access>
           <EBELN>4500000687</EBELN> 
           <EBELP>00010</EBELP> 
          <MATNR>000000000100000012</MATNR> 
          <MENGE>1.000</MENGE> 
          <MEINS>EA</MEINS> 
          <WERKS>1000</WERKS> 
          <LGORT /> 
          <AEDAT>2011-12-12</AEDAT> 
          <BUKRS>1000</BUKRS> 
          <ANLN1>010023000315</ANLN1> 
          <PSTYP>0</PSTYP> 
          <KNTTP>A</KNTTP> 
          <LOEKZ>L</LOEKZ> <---- I receive the value here when user has deleted the item in the PO. 
  </access>
  - <Key1>
          <EBELN>4500000687</EBELN> 
          <EBELP>00010</EBELP> 
  </Key1>
  </dbTableName>
  </StatementName>
  </ns0:MT_EKPO_RECEIVE>

Please help me to understand why the changed values are not reflecting in my MSSQL server from PI. and tell me what should i do to correct it. I have no error message in RWB for the receiver communication channel. please reply me. your any reply will be highly appreciated. Please reply me

[Former Member]

Delete is handled differently when compared to an Update_Insert.

an Update_insert statement is like a modify command. it either inserts an entry or changes it, but does not delete it. You need to specify the keys in the Update_insert XML-SQL.

In case you have a scenario for delete along with update/insert, please do the following

1. Let the source system determine an action, and pass it to PI

2. Based on the action code, implement 2 split mappings using enhanced interface determination ( rule based interface determination).

3. Implement 2 seperate maps for Delete and Update, and call them based on the action code rule from the interface determinaton.

Hope this helps.

Regards,

Abhishek

[Former Member]

Hi Naim,

Use a stored procedure on sql side and execute it from PI . It is easier to handle update and insert transactions. I can provide you the structure if you want.

Regards,

Teresa

[naimkhans_babi]

Hi!

Thank you so much for your reply. if possible please provide me the store procedure thing.

Regards

Naeem

[Former Member]

Hi

Why dont you change the INSERT_UPDATE to DELETE if the value is there?.

Regards.