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.
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'
How about creating a new stored procedure that will execute all the required steps (1-3)?