Updating the 'Ends on' column in usr table in IAM from end application

  • 14 March 2023
  • 6 replies

Userlevel 2
Badge +5


I am trying to update the ‘Ends on’ column in the IAM under period group, to stop the user from logging in the application. 

The postman API when tested for this works as expected : 
sending the url with format: {iam_link}iam/iam/usr(tenant_id={tenant_id}, usr_id='{email_id}')

Process flow :

subflow : 

input for the HTTP connector : 

The update iam parameter updates the process parameter for the http connector : email_id and tenant_id 

The update job_details updates the SF db columns.

Please note that the SF DB columns are getting updated correctly, however the IAM DB for end_on date is not.


Below is the code for update_iam_parameter control procedure : 

 set @email_id = null select top 1 @email_id = ed.email_id,        @tenant_id = ed.tenant_id,        @current_date = (select CONVERT(VARCHAR,GETDATE(),120) )    from resignation r    join employee_detail ed     on ed.employee_detail_id = r.employee_detail_id    where r.last_working_day = CONVERT(DATE, GETDATE())     and r.inactive_in_iam is null if @email_id <> null begin     set @update_iam_parameter_iam_update = 1 --action will be executed.     set @update_iam_parameter_stop = 0 end else begin    set @update_iam_parameter_stop = 1 end 


Below is the code to update the columns in SF DB :

 /* to update the deactivate field as 1*/ UPDATE job_detail        SET employee_status = 1 FROM job_detail jd  JOIN employee_detail ed ON jd.employee_detail_id = ed.employee_detail_id WHERE ed.email_id = @email_id update resignation -- updating the resignation after the user is disabled in IAM   set inactive_in_iam = 0 from resignation r join employee_detail edON ed.employee_detail_id = r.employee_detail_id WHERE  ed.email_id = @email_id


I am getting below error in the log : 

2023-03-14T15:15:00.8242996+00:00  [ERR] Error in process procedure of process action "iam_update" in process flow "employee_last_working_date" of application 4. (b2ae20de)
2023-03-14T15:15:00.8270264+00:00  [ERR] Process action "execute_sub_flow_employee_last_working_date" in processflow "employee_last_day_system_flow" in application 4 threw an exception. (571360e4)
Indicium.Shared.TSF.FatalDbException (0x80004005): The following query failed with an exception: 'prc_employee_last_working_date_iam_update'
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Procedure or function prc_employee_last_working_date_iam_update has too many arguments specified.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---


Please help what I am missing?


Best answer by Mark Jongeling 14 March 2023, 19:16

View original

6 replies

Userlevel 7
Badge +21

Hi Rucha,

I see this: 

Procedure or function prc_employee_last_working_date_iam_update has too many arguments specified.

Meaning the process flow tries to execute the process procedure prc_employee_last_working_date_iam_update, but the Model and the Stored procedure are out of sync.

Can you try regenerating and redeploying the procedure code onto the database? If the error still persists, the model in IAM is not up to date, then a synchronization to IAM with the correct model and branch name is required.

Hope this helps!

Userlevel 2
Badge +5

Thanks Mark for the quick reply!
I regenerated and redeployed the procedure code to database. Also synced the IAM with correct model and branch name.

That error is now resolved , however the ‘End on’ column in IAM did not get updated.

The other tables after the HTTP connector got updated correctly.

Do we need to give any rights to in order to update in IAM?

I am using super user for this flow.

Userlevel 7
Badge +21

No problem, what HTTP status code is returned? 

In terms of rights, only Root administrator and User management is allowed to update this field. However, I think it might be the way you send the JSON:

In Postman the given datetime is surrounded by quotes ("”), but in the HTTP connector, it is missing:

Content (input)

Could this be the missing piece?

Userlevel 6
Badge +4

Mark is correct, but I expect that you will run into another problem after that. I don’t think that the current_date value will be formatted correctly for a JSON object if you directly inject a DATETIME process variable into the constant value like that.

I would recommend using a VARCHAR process variable as an intermediary and giving it the following value:

set @current_date_string = convert(varchar(50), @current_date, 126);

And then the following constant value should work just fine:

"end_on": "{current_date_string}"

I hope this helps.

Userlevel 2
Badge +5

Thanks Mark and Vincent, 
The process is working and updating the ‘End on’ field , I was using below for current_date parameter:

@current_date = (select CONVERT(VARCHAR,GETDATE(),120) 

Should I change to : set @current_date_string = convert(varchar(50), @current_date, 126);?


Userlevel 6
Badge +4

Hello Rucha,

That should be fine. The 120, 121, 126 and 127 formats will all have pretty much the same result in this situation.