Need help for creating UPDATEACCOUNT JSON

[onarvekar]

Hello Team,

I have a use case where when the user will be modifying his account then he will select the dynamic attributes(Multiselect which are compulsory) in the request form but the Entitlement value will remain same which he does not want to modify(single select).
Can you assist me in creating the UPDATEACCOUNTJSON here, i have created the same, it triggers the Update account task but here the procedure is also to be triggered and send all the values of dynamic attributes along with the entitlement value as an update account

Current UPDATEACCTONJSON-
{
"UpdateAccountQry" : [
"DECLARE @output varchar(700)= CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END",<PROCEDURE START>"EXEC XYZ '${accountName}', '<need to pass the selected entitlement value here>', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output"<PROCEDURE END>,
"Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"
]
}

[NM]

@onarvekar update account json doesn't expose entitlement related details.

[onarvekar]

is there any other way/solution to achieve this?

[NM]

@onarvekar you can store the entitlement value in accounts customproperty and then utilise it.

[onarvekar]

EXEC XYZ '${accountName}', '<need to pass the selected entitlement value(stored in account.cp11) here>', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output

i have used accounts cp11, how should i use it in the procedure position is highlighted in this color.

[NM]

@onarvekar similar to accountname

${account.customproperty11}

[onarvekar]

Done as you suggested but getting error when saving connection Here is the JSON and error screenshot->
JSON->
{
"UpdateAccountQry" : [
"DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END ; EXEC XYZ '${accountName}', '${account.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output;",
"Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"
]
}

error ss->

please help here

[NM]

"DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END ; EXEC XYZ '${accountName}', '${account.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output where 0=0;",

"Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"

[onarvekar]

Hi NM

for the above solution also it is giving same error
ss->

[NM]

@"DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END where 1=1 ; EXEC XYZ '${accountName}', '${account.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output where 1=1;",

"Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"

I hope overall syntax is correct.

[rushikeshvartak]

• You can't pass entitlement value in Update account JSON 
• Where keyword is mandatory in UpdateAccountJSON
• Use below

{
  "UpdateAccountQry": [
    "DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END ;DECLARE @where_dummy INT = 1; EXEC XYZ '${accountName}', '${account.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output;",
    "Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"
  ]
}

[onarvekar]

Hi @rushikeshvartak ,
using this query in provisioning comments(Task type - Update Account) i am getting error as ->

[rushikeshvartak]

{
  "UpdateAccountQry": [
    "DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END ;DECLARE @where_dummy INT = 1; EXEC XYZ '${accountName}', '${accountsObj.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output;",
    "Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"
  ]
}

[onarvekar]

@rushikeshvartak 

Logs->
Error while Executing update query - com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'accounts'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642) at java_sql_Statement$executeUpdate$0.call(Unknown Source) at com.saviynt.provisoning.DBProvisioningService.executeStatement(DBProvisioningService.groovy:274) at com.saviynt.provisoning.DBProvisioningService.this$2$executeStatement(DBProvisioningService.groovy) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1031) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:731) at groovy.lang.GroovyObjectSupport.invokeMethod(GroovyObjectSupport.java:44) at groovy.lang.MetaClassImpl.invokeMethodOnGroovyObject(MetaClassImpl.java:1151) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1045) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:145) at com.saviynt.provisoning.DBProvisioningService$_updateAccountDB_closure4_closure29.doCall(DBProvisioningService.groovy:732) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1325) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1297) at org.codehaus.groovy.runtime.dgm$148.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.provisoning.DBProvisioningService$_updateAccountDB_closure4.doCall(DBProvisioningService.groovy:726) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.callClosureForMapEntry(DefaultGroovyMethods.java:3875) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1354) at org.codehaus.groovy.runtime.dgm$149.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.provisoning.DBProvisioningService.updateAccountDB(DBProvisioningService.groovy:685) at com.saviynt.provisoning.DBProvisioningService$updateAccountDB.call(Unknown Source) at com.saviynt.ecm.services.ArsTaskService.updateAccountTarget(ArsTaskService.groovy:11680) at com.saviynt.ecm.services.ArsTaskService$updateAccountTarget$10.call(Unknown Source) at com.saviynt.ecm.services.ArsTaskHelperService$_whenTaskTypeIsTwelveUpdateAccount_closure46.doCall(ArsTaskHelperService.groovy:2994) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.callClosureForMapEntry(DefaultGroovyMethods.java:3875) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1354) at org.codehaus.groovy.runtime.dgm$149.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.ecm.services.ArsTaskHelperService.whenTaskTypeIsTwelveUpdateAccount(ArsTaskHelperService.groovy:2984) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1031) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66) at com.saviynt.ecm.services.ArsTaskHelperService$_completeAutoProvTasksUpgraded_closure1.doCall(ArsTaskHelperService.groovy:201) at sun.reflect.GeneratedMethodAccessor2392.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1325) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1297) at org.codehaus.groovy.runtime.dgm$148.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.ecm.services.ArsTaskHelperService.completeAutoProvTasksUpgraded(ArsTaskHelperService.groovy:161) at com.saviynt.ecm.services.ArsTaskHelperService$completeAutoProvTasksUpgraded$5.call(Unknown Source) at MultipleProvisioningJob.execute(MultipleProvisioningJob.groovy:222) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:196) at org.codehaus.groovy.grails.plugins.quartz.GrailsJobFactory$GrailsTaskClassJob.execute(GrailsJobFactory.java:72) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

[rushikeshvartak]

i have updated account to accountsObj so error is not valid with latest code

[onarvekar]

@rushikeshvartak 

{
  "UpdateAccountQry": [
    "DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END ;DECLARE @where_dummy INT = 1; EXEC XYZ '${accountName}', '${accountsObj.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output;",
    "Update accounts set customproperty1='${BusinessUnit}',customproperty2='${REGION}',customproperty3='${Market}',customproperty4='${Account}',customproperty57='${Country}',customproperty58='${SelectAccount}' where USERKEY='${user.id}'"
  ]
}

Has it anything to do with "Update accounts" line?

[rushikeshvartak]

Does target have accounts table ?

[onarvekar]

indeed @rushikeshvartak , i had a misunderstanding there so removed that query, now the issue is resolved the accounts with updated attributes is provisioning in the target, thank you for your consistent support throughout the day @rushikeshvartak and @NM . 

[rushikeshvartak · Accepted solution]

Below is working solution please confirm

{
  "UpdateAccountQry": [
    "DECLARE @output varchar(700) = CASE WHEN '${Account}'= 'All' THEN '${Account}' WHEN '${Account}'='Specific accounts' THEN '${SelectAccount.replace(',','|')}' END ;DECLARE @where_dummy INT = 1; EXEC XYZ '${accountName}', '${accountsObj.customproperty11}', '${BusinessUnit.replace(',','|')}','${Country.replace(',','|')}', @output;"
  ]
}

Update Account JSON needs where keyword which resolved by adding 

DECLARE @where_dummy INT = 1

[onarvekar]

Yes @rushikeshvartak , i confirm this is the solution.
Thanks for you support.
Regards,
Omkar Narvekar