0028834: M_Reservation_Post errors in oracle

Description

Database Stored Procedures they work in different way in Oracle and in Postgres. In oracle while calling AD_UPDATE_PINSTANCE the process commits unless an extra parameter is passed to the procedure. In Postgres it never commits. A lot of Stored Procedures the don't have that flag, so they commit automatically in Oracle, and sometimes this is not the expected behavior.
To avoid this, I have added in one of this cases the extra parameter in the call to AD_UPATE_PINSTANCE, but when executing the stored procedure from java using CallProcess.callProcess method it fails in the following piece of code:
OBDal.getInstance().getSession().refresh(pInstance);
with this error:
No row with the given identifier exists: [ADParameter#635B4607588F4D68B28D054795AC7482]
org.hibernate.UnresolvableObjectException: No row with the given identifier exists: [ADParameter#635B4607588F4D68B28D054795AC7482]
    at org.hibernate.UnresolvableObjectException.throwIfNull(UnresolvableObjectException.java:65)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:165)
    at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:1127)
    at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:1110)
    at org.hibernate.engine.CascadingAction$3.cascade(CascadingAction.java:203)
    at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:392)
    at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:335)
    at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:204)
    at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:425)
    at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:362)
    at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:338)
    at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:204)
    at org.hibernate.engine.Cascade.cascade(Cascade.java:161)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:127)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:62)
    at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:1118)
    at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:1098)
    at org.openbravo.service.db.CallProcess.callProcess(CallProcess.java:224)
    at org.openbravo.service.db.CallProcess.call(CallProcess.java:117)
    at org.openbravo.service.db.CallProcess.call(CallProcess.java:268)
    at org.openbravo.materialmgmt.ReservationUtils.processReserve(ReservationUtils.java:163)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.getGridData(StockReservationPickAndEditDataSource.java:592)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.getData(StockReservationPickAndEditDataSource.java:131)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.fetchJSONObject(StockReservationPickAndEditDataSource.java:103)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.fetch(StockReservationPickAndEditDataSource.java:83)
    at org.openbravo.service.datasource.DataSourceServlet.doFetch(DataSourceServlet.java:261)
    at org.openbravo.service.datasource.DataSourceServlet.doPost(DataSourceServlet.java:747)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
    at org.openbravo.base.HttpBaseServlet.serviceInitialized(HttpBaseServlet.java:222)
    at org.openbravo.base.secureApp.HttpSecureAppServlet.service(HttpSecureAppServlet.java:449)
    at org.openbravo.client.kernel.BaseKernelServlet.callServiceInSuper(BaseKernelServlet.java:88)
    at org.openbravo.client.kernel.BaseKernelServlet.service(BaseKernelServlet.java:59)
    at org.openbravo.service.datasource.DataSourceServlet.service(DataSourceServlet.java:140)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.SessionExpirationFilter.doFilter(SessionExpirationFilter.java:66)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.CharsetFilter.doFilter(CharsetFilter.java:35)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.client.kernel.KernelFilter$1.doAction(KernelFilter.java:62)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.client.kernel.KernelFilter.doFilter(KernelFilter.java:71)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.dal.core.DalRequestFilter$1.doAction(DalRequestFilter.java:81)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.dal.core.DalRequestFilter.doFilter(DalRequestFilter.java:103)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:701)

Steps To Reproduce

In oracle environment:
* Enable Reservations preference.
* Logout and Login.
* Create a Sales Order with a line. Click on that line and press Manage Reservations.
* Click on Cancel.
* Go to Linked Items of that line and realize that a reservation has been created. This is wrong because we've selected cancel in the P&E. This happens because M_RESERVATION_POST commits.

* In sqldeveloper, open M_RESERVATION_POST stored procedure. In all places where a call to AD_UPDATE_PINSTANCE is performed, add an extra parameter ('N'). Click again on Manage Reservations in Sales Order Line.
* Realize that the previous error is raised.

* The stored procedure it should not commit in Oracle to avoid different behaviors between databases. In general the procedures they should not have to commit, what I think that probably is happening with several stored procedures.

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

related to	defect	0029063		closed	umartirena	M_Reservation_Post fails in Oracle
related to	defect	0028510	3.0PR15Q2	closed	reinaldoguerra	Manage Reservation P&E behavior could be confusing in some cases opened from Sales Order Line
related to	design defect	0028875		acknowledged	Triage Platform Base	Class "CallStoredProcedure" does not support parameters of type out

Notes
(0074500) alostale (manager) 2015-02-16 09:43	The actual problem is due to incorrect M_Reservation_Trg trigger implementation which causes "ORA-04091: M_Reservation is mutating" error. If, as explained in the steps to reproduce, commits are prevented in ad_update_pinstance calls, an exception is raised and this code is executed: EXCEPTION WHEN OTHERS THEN v_ResultStr:= '@ERROR=' \|\| SQLERRM; DBMS_OUTPUT.PUT_LINE(v_ResultStr) ; IF (p_pinstance_id IS NOT NULL) THEN ROLLBACK; AD_UPDATE_PINSTANCE(p_pinstance_id, NULL, 'N', 0, v_ResultStr, 'N') ; ELSE RAISE; END IF; RETURN; END M_RESERVATION_POST which rollbacks the whole transaction, causing the ad_pinstance row created in the transaction to be rolled back so DAL can't find it afterward. Removing the ROLLBACK line prevents this failure, though the real problem is within the trigger.

(0074994) umartirena (reporter) 2015-02-26 18:56	Finally not a Platform issue after fixing the following issue: https://issues.openbravo.com/view.php?id=29063 [^] The flag to not to commit in M_RESERVATION_PROCESS will be added in the following issue: https://issues.openbravo.com/view.php?id=28510 [^]

Issue History
Date Modified	Username	Field	Change
2015-02-02 10:44	umartirena	New Issue
2015-02-02 10:44	umartirena	Assigned To	=> AugustoMauch
2015-02-02 10:44	umartirena	Modules	=> Core
2015-02-02 10:44	umartirena	Triggers an Emergency Pack	=> No
2015-02-06 07:42	alostale	Status	new => acknowledged
2015-02-10 11:04	alostale	Relationship added	related to 0028875
2015-02-16 09:43	alostale	Note Added: 0074500
2015-02-16 09:44	alostale	Assigned To	AugustoMauch => umartirena
2015-02-16 09:44	alostale	Summary	Stored Procedures have different behavior in Oracle and Postgres => M_Reservation_Post errors in oracle
2015-02-16 09:46	alostale	Category	A. Platform => 03. Procurement management
2015-02-26 18:40	umartirena	Status	acknowledged => scheduled
2015-02-26 18:40	umartirena	fix_in_branch	=> pi
2015-02-26 18:56	umartirena	Review Assigned To	=> umartirena
2015-02-26 18:56	umartirena	Note Added: 0074994
2015-02-26 18:56	umartirena	Status	scheduled => closed
2015-02-26 18:56	umartirena	Resolution	open => no change required
2015-02-26 18:56	umartirena	Relationship added	related to 0029063
2015-02-26 18:57	umartirena	Relationship added	related to 0028510