topic Re: Issue with Database Commit in Application Development and Automation Discussions

Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 05:50:23 GMT

Hello All,

I have two include in my user exit of the sales order.

In one include I am doing some validation on the Order quantity of the sales order depending upon the order quantity in one of my Z table. If every validation is OK then I am exporting this order quantity to memory ID.

In the second include I am importing this Order Quantity and updating this Quantity in my database in my same Z table.

If I process one order at a time then everything is working fine but when I try to create two sales order at the same time then only second order is getting validated and order quantity is getting updated whereas for the first order no validation is performed and also order quantity is not getting updated in the Z table.

I really do not know is this issue related to database commit or something else.

Kindly help.

Regards

Sachin yadav

Re: Issue with Database Commit

former_member404244 — Mon, 06 Jun 2011 06:23:36 GMT

Hi,

I don't understand why you are importing the quantity in other other include and update the ztable.I believe you can do it in the same include..I might be wrong.

I think for two sales orders processing at the same time, you might be getting two different records in any temporary SAP internal table like XVBAK .. So instead of sending one variable, you can send internal table to memory-id and read the internal table from memory id, this way you can get multiple records..Please give a try and see.

Regards,

Nagaraj

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 06:48:33 GMT

Hello Nagaraj,

Thanks for your reply.

I am sending the internal table to memory id not the variable as it contains the order Quantity of the line itmes.

Also even if I use to same include then while fetching the data from this Ztable to perform the validation on the Quantity I will not get the correct data because I am processing the two orders at the same time and both the code are executed at the same time so the Quantities are not updated in the Ztable.

So even if I write the code in same include then also only one order quantity (i.e second order Quantity) will be updated in the Ztable while processing the two sales orders at same time.

Kindly help.

Regards

Sachin Yadav

Re: Issue with Database Commit

former_member404244 — Mon, 06 Jun 2011 07:00:42 GMT

Hi,

Can you please share your code, to have a look..Also which userexit you are using "USEREXIT_SAVE_DOCUMENT".For each oder the exit will be called right.what ever data is there it takes that and updates the ztable right.Both the orders should be updated to ztable.

Regards,

Nagaraj

Re: Issue with Database Commit

Former Member — Mon, 06 Jun 2011 07:17:15 GMT

Hi Sachin Yadav,

Along with the code it might be of more help if you provide the Z Table structure too with details like the key fields in the table.


I am processing the two orders at the same time and both the code are executed at the same time so the Quantities are not updated in the Ztable.

Also, didn't quite get what you meant by the above sentence, what/how do you mean by "Processing two orders at the same time and both code are updated at the same time"...provide more information on the actual scenario.

Regards,

Chen

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 08:55:17 GMT

Hello,

My Include are present in the USEREXIT_SAVE_DOCUMENT.

The Table which I am updating is ZDSQTA.

The fields of the tables are as below.

ZMANDT Client (Key)

ZVKORG Sales Organization (Key)

ZSCYC1 Sales Cycle 1 (Start Period) (Key)

ZSCYC2 Sales Cycle 2 (End Period) (Key)

ZBZIRK Sales district (Key)

ZVKBUR Sales Office (Key)

ZMATNR Material Number (Key)

ZQTANO Quota Number (Key)

ZGRQTY Allocated Quantity. (Non Key)

ZALQTY Allocated Quantity. (Non Key)

ZSHQTY Shipped Quantity. (Non Key)

ZORQTY Ordered Quantity. (Non Key)

Please find the code as below :-

Moderator message - Please respect the 5,000 character maximum when posting. Post only the relevant portions of code

Plz revert back in case you need further details.

Regards

Sachin

Edited by: Rob Burbank on Jun 8, 2011 3:22 PM

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 08:57:30 GMT

Nagaraj,

If you want i can send you code plz share your id.

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 09:02:18 GMT

Hello Chen,

By processing two order at same time mean I am saving two sales order at the almost same time with same details.

When I debug these two order by putting breakpoint and saving at the same time I realizes that the data is selected from the Z table in both the orders at same time.

Where as as per my requirement data should be selected for the second order after processing is done for the first order.

Regards

Sachin

Re: Issue with Database Commit

former_member404244 — Mon, 06 Jun 2011 09:09:45 GMT

Hi,

Please don't paste entire code only paste the code which is needed (example ztable update, ztable data retrieval). Also paste in read able format.

Regards,

Nagaraj

Re: Issue with Database Commit

matt — Mon, 06 Jun 2011 09:41:18 GMT


Nagaraj,
> 
> If you want i can send you code  plz share your id.

Do not exchange email addresses on these forums

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 10:06:07 GMT

Nagaraj,

any suggestion?

Re: Issue with Database Commit

Former Member — Mon, 06 Jun 2011 10:17:29 GMT

Hi Sachin,

Still not sure on what you are trying to achieve,


My Include are present in the USEREXIT_SAVE_DOCUMENT

Do you mean both of your includes are present in the above exit? If yes, then why do you have your code in two separate includes, why not have it in one, also why do you want to export and import the values?

Or if you mean only one of the include is called in save_doc where is the other include?


Where as as per my requirement data should be selected for the second order after processing is done for the first order.

How do you know that the data is not processes for the first order, i ask this because the key fields in your Z table is not order specific?


ZMANDT Client (Key) 
ZVKORG Sales Organization (Key) 
ZSCYC1 Sales Cycle 1 (Start Period) (Key) 
ZSCYC2 Sales Cycle 2 (End Period) (Key) 
ZBZIRK Sales district (Key) 
ZVKBUR Sales Office (Key) 
ZMATNR Material Number (Key) 
ZQTANO Quota Number (Key) 
ZGRQTY Allocated Quantity. (Non Key) 
ZALQTY Allocated Quantity. (Non Key) 
ZSHQTY Shipped Quantity. (Non Key) 
ZORQTY Ordered Quantity. (Non Key)

Provide a brief description of your requirement for getting helpful answers.

Regards,

Chen

Re: Issue with Database Commit

former_member404244 — Mon, 06 Jun 2011 10:43:09 GMT

Hi,

Please refer the below link, how to paste your code in SCN.

I would suggest create a lock object for the entire table when the update is going on and release it once the update is complete.

Call the lock object in a loop, so that every time you keep on checking if the lock is there or not, if yes you will not get sy-subrc eq 0. if no then sy-subrc eq 0. based on this you can update the ztable at one for one document.

Chen is correct with his question there is no order number, item number in your ztable and also you can put your code in one include as he and me suggested.

Regards,

Nagaraj

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 10:50:18 GMT

Hello Chen,

Do you mean both of your includes are present in the above exit?

Yes

If yes, then why do you have your code in two separate includes, why not have it in one, also why do you want to export and import the values?

Actually I am working on the maintenance issue so the codes are already in place.

Or if you mean only one of the include is called in save_doc where is the other include?

USEREXIT_SAVE_DOCUMENT_PREPARE

Include for the validation and preparing the data for Z table and then exporting data.

userexit_save_document

Include to update the Z table in the Data base table by importing.

How do you know that the data is not processes for the first order, i ask this because the key fields in your Z table is not order specific?

I put the breakpoint in the user exit and saved both the order at same time and in debug found that the data from the Z table is selected at same time into the internal table for both the orders. So before updating the data for first order in the Z table the data for second order is getting selected as i am saving both the order at almost same time.

Regards

Sachin

Re: Issue with Database Commit

former_member404244 — Mon, 06 Jun 2011 11:00:45 GMT

Hi,

Please use lock object concept as i suggested above. Also if the data is same then you will get the same data for the two sales orders, if different then you will get different. Please share you data base select query.

Regards,

Nagaraj

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 11:02:37 GMT

Hello Nagaraj,

I have to perform my validation depending upon the Quantity in the order and Quantity in the Z table.

When I am saving two orders at almost same time only second order is considered for the validation.

When I checked in the debug by saving these two order at same time I found that the data from the Z table is selected before the processing is done for the first order and quantity in the Ztable is updated for the first order.

My requirement is to perform the validation for all the order as these two order are created at same time the validation is done for only one order as both the order are selecting the data from the Z table at same time.

Regards

Sachin

Re: Issue with Database Commit

former_member404244 — Mon, 06 Jun 2011 11:15:36 GMT

hI,

This is fine you want to validate for both the records. At run time you will have the sap data stored in temp tables. You can very well use them to select the data. This will be different for both the orders .the ztable should have both the reocrds that you are validating on screen. While updating the data please use lock object and save once at one time , once it is complete then save the other one. if there is one record in the data base table for two orders then you will get one record only.

open two sessions go to VA01 for both, put a break-point in the program. First session input the values and press save button, then do the same thing for other order and press save button. the data available on screen should be different . I hope this is what you are doing.

Regards,

Nagaraj

Re: Issue with Database Commit

sachin_yadav3 — Mon, 06 Jun 2011 11:35:49 GMT


  SELECT * FROM ZDSQTA INTO TABLE XZDSQTA FOR ALL ENTRIES IN TABLE2
  WHERE ZVKORG EQ TABLE2-VKORG
  AND   ZSCYC1 LE TABLE2-ERDAT
  AND   ZSCYC2 GE TABLE2-ERDAT
  AND   ZBZIRK EQ BZIRK
  AND   ZVKBUR EQ VBAK-VKBUR
  AND   ZMATNR EQ TABLE2-MATNR.

Please note that I do not have the order number or the line item number in my z table.

In validation I am modify the records in the Ztable as well I mean to say for each order created I am updating the Quantity in my Z table.

So for the next order the validation should be done depending upon the new order quantity not the old order quantity.

I mean to say for the first order the validation should be done depending upon the old Qty and for the second order validation should be done by new order Qty.

When I am saving the two order at same time validation on both the order is done by old qyt bcoz new oder Qty from the first order is not yet upadated in the database when second order try to fetch the data from the Z table as i am saving both the order at same time.

Hope now i am bit clear now.

Re: Issue with Database Commit

former_member404244 — Tue, 07 Jun 2011 06:02:27 GMT

Hi,

Correct me if i understand your requirement correctly. You are saying for each order you are saving the quantity in the ztable.Now next time you are passing new value to the existing quantity in the ztable to update the new quantity for that order.So for each order you may be changing the qunatity or no change to the quantity.

I didnot get the below points.



I mean to say for the first order the validation should be done depending upon the old Qty and for the second order validation should be done by new order Qty.

When I am saving the two order at same time validation on both the order is done by old qyt bcoz new oder Qty from the first order is not yet upadated in the database when second order try to fetch the data from the Z table as i am saving both the order at same time.

Did you give a try with lock object??

Regards,

Nagaraj

Re: Issue with Database Commit

sachin_yadav3 — Tue, 07 Jun 2011 06:52:18 GMT

Hi,

I am validating my order qty with the qty stored in the table and after validation i am modifying the Qty in Z table with order Qty + Qty in Z table.

I am trying to use the "Exclusive but not cumulative lock" on my table.

As it will lock the entire table and there will be many order created at same time so there will be bit delay.

We are confirming with the user are they Ok with that bit delay if many oders are created at same time.

Regards

Sachin