AFX connector: How can we write multiple sql queries in a single capability
example : EBS createAccount () capability will do 3 work ,
1) Register the person in oracle EBS using a stored procedure.
2) Select the person_ id using select query.
3) Using the same person id call the stored procedure to create user.
Please provide me the solution.
How about creating a new stored procedure that will execute all the required steps (1-3)?
As we don't have any permission to crate a new stored procedure ,only we can call the target application's predefined stored procedure. Now please help me what can we do now? @Boris Lekumovich
Which ACM version you are using?
I had used multiple queries in single AFX connector separating by ; i guess
need to check.
we are using ACM 7.0 and As we are using " SELECT" query to collect the person_id ,will we need to use "command Output parameters" to store that value???? @Kumar Gaurav
Trying to understand this a bit more. Are you leveraging a 'SELECT' query within the AFX capability to dynamically pull in the 'person_id' to utilize during provisioning?
Yes, this is a complex query but it involves select queries within INSERT to get NETWORK_ID, PERSON_ID etc
yes, i want 'person_id' dynamically to utilize during provisioning/create Account().Here i am not using any "Insert" query ,only calling stored procedure .
I have a database connector to execute multiple SQL statements / stored procedure on MS SQL.
The example below is for create account
INSERT INTO people (SYSTEM_ID,FULL_NAME,......) values(${Network ID}, ...............)
exec sp_nextkey @table= 'SYSTEMKEY'
INSERT INTO NETWORK_ALIASES (NETWORK_ID,SYSTEM_ID,..........)
values(${Account}),SEQ_SYSTEMKEY),.....................)
exec sp_nextkey @table= 'SYSTEMKEY'
I have a database connector to execute multiple SQL statements / stored procedure on MS SQL.
The example below is for create account
INSERT INTO people (SYSTEM_ID,FULL_NAME,......) values(${Network ID}, ...............) exec sp_nextkey @table= 'SYSTEMKEY' INSERT INTO NETWORK_ALIASES (NETWORK_ID,SYSTEM_ID,..........) values(${Account}),SEQ_SYSTEMKEY),.....................) exec sp_nextkey @table= 'SYSTEMKEY'