Hi BPEL Gurus,
I am facing an issue in EBS to OTM Invoice integration.
AP Invoice header is inserted, AP Invoice lines does not get inserted due to the below error in BPEL. Payment import fails, as there are no invoice lines.
Non Recoverable System Fault :
Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'insert' failed due to: DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [APInvoice.ApInvoicesInterface]. Caused by java.sql.BatchUpdateException: ORA-00001: unique constraint (AP.AP_INVOICE_LINES_INTERFACE_U1) violated . Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-1" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.
SOA DB Connections seem to retain the used sequence in the cache and assign the same when Invoice line id is inserted. This is causing the Unique constraint error while inserting to AP Invoice lines table in EBS.
I am calling the standard APInvoices.wsdl for doing the AP_INVOICE_LINES_INTERFACE insert. I notice that in the APInvoice-or-mappings.xml file contain the following code
<sequence-field table="AP_INVOICE_LINES_INTERFACE" name="INVOICE_LINE_ID"/>
Is their anything that I can change, something in the BPEL process to refresh the Sequence cache all the time.
Alternative I am thinking now is the below.
1. Check with client if they can make the sequence AP_INVOICE_LINES_INTERFACE_S as NOCACHE.
2. Derive the invoice line id via pl/sql adapter and populate the invoice line id. But in this case I am not sure if the APInvoice webservice code will try to update with BPEL generated sequence.
Any direction on this issue will be of great help.