I am using Oracle Cash Management Adapter to Insert the Bank Files in to Oracle Applications Database. Everything works fine but I have a requirement that I need to insert only one Header for all the day to day transactions.
To do that I have written an JDBC Delete adapter service which deletes the header if the header already exists in the headers table based on the header record I get from the flat file. If the header already exists I will delete it otherwise I have to insert a new header and the detail lines for that header. I am doing the delete beacuse the Oracle Applications Adapters dosen’t allow the detail records insert without the header records.
When I call the JDBC delete header service with the Bank Account Num as 123456789 and Statement Date as JAN-2006 , the IS hangs.
Is there any way that I can check the header records and if it exists delete and insert the same header record with new detail lines without causing the IS hangovers?
Why don’t you create a SP call for this task?So are you first selecting header service and depending on the result count executing delete service and followed by insert header,detail lines etc…So if go with creating SP service you can minimize the execution time as well as faster performance.Schedule JDBC SP template in a wrapper flow service.
Are you positive that you cannot insert detail records without also inserting a header record via the adapter? I have worked with the Oracle Apps adapter predefined transactions in the past and I recall them being highly customizable. You should be able to modify the bizdocMapping service to accomplish what you need.
By the way, if modifying the predefined services, I’d suggest making a copy of the service you’re modifying to a different package and then making your modifications there. This way, if you ever need to revert back to the original, you will have it right away.
I have written a procedure which deletes the header record successfully.
But I have a minor problem like:
I might have 3 header records and its details or I might have 2 header records
and their details. So I am calling the delete header procedure in the Header LOOP and pass the unique parameters to delete the header that already exists and insert a new header and If I don’t find any header I don’t delete or insert.
But when I call this procedure with the parameters passed from the pipeline I get the error saying that :
[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service KSUIntrustBank.Adapters:CallDelHeaderProc.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource Queries:dbConnections. The resource is already being used in a parent transaction.
Do I need to use different db connections? I am using the same connections for 2 to 3 queries in my program.
Can you please let me know if I am making any mistake
Yeh could be problem with same connections,may be its worth changing with different db connections not fully sure about it?what is the transaction type (NO or Local or XA)…
Is the Oracle Cash Management adapter different that the Oracle Applications adapter? If not, then what’s the name of the predefined transaction service that you’re using? What version of the adapter are you running?
Are duplicate headers only possible within the same BankStatementBizdoc?
— OR —
Could duplicate headers occur in different bizdocs? In other words, could the receiveBankStatement service receive a BankStatementBizdoc, process it, and then some time later, receive another BankStatementBizdoc which contains a header that existed in a previous bizdoc?
IF THE FORMER:
I’d suggest modifying the bizDocMapping service so that in the main loop, you keep track of each header you have already processed by, FOR EXAMPLE, storing each header’s unique ID into a hashtable. You should then put the “Map Statement Header…” in a BRANCH so it’s only executed if the current header is not a duplicate (ie. the ID did NOT exist in the hashtable.)
IF THE LATTER:
In addition to implementing the solution above, you can execute a SELECT statement to determine if the current header already exists in the target table. If it does, then do NOT execute the “Map Statement Header…” step.
Suggested pseudo-code:
Loop
– Search for unique header ID in hashtable
– If unique ID DOES NOT EXIST in the hashtable
---- Add ID to the hashtable
---- SELECT unique ID FROM target table
---- If unique header ID DOES NOT EXIST in the target table
------ Map Statement Header
.
.
.
So, in simpler words, add logic to bizDocMapping so the “Map Statement Header” step is not executed when the header is a duplicate. This should prevent the duplicate from being inserted to begin with and it will eliminate the overhead of having to insert and then delete the record.
LAST THING: if implementing the second option, try to implement the SELECT with the Oracle Apps adapter instead of the JDBC adapter so it uses the same connection as the transaction service.
