Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/31/2024 03:17 AM
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}'"
]
}
10/31/2024 03:23 AM
@onarvekar update account json doesn't expose entitlement related details.
10/31/2024 03:24 AM
is there any other way/solution to achieve this?
10/31/2024 03:26 AM
@onarvekar you can store the entitlement value in accounts customproperty and then utilise it.
10/31/2024 03:54 AM
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.
10/31/2024 04:13 AM
@onarvekar similar to accountname
${account.customproperty11}
10/31/2024 04:17 AM
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
10/31/2024 04:31 AM - edited 10/31/2024 04:32 AM
"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}'"
10/31/2024 04:36 AM
Hi NM
for the above solution also it is giving same error
ss->
10/31/2024 04:39 AM - edited 10/31/2024 04:39 AM
@"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.
10/31/2024 06:32 AM
{
"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}'"
]
}
10/31/2024 07:13 AM - edited 10/31/2024 07:14 AM
Hi @rushikeshvartak ,
using this query in provisioning comments(Task type - Update Account) i am getting error as ->
10/31/2024 07:55 AM
{
"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}'"
]
}
10/31/2024 08:04 AM - edited 10/31/2024 08:33 AM
@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)
10/31/2024 09:05 AM
i have updated account to accountsObj so error is not valid with latest code
10/31/2024 09:20 AM
{
"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?