Forms JEE workflows | Handling user data forms-jee-workflows-handling-user-data

Execute the following command in AEM Forms server database to retrieve the principal ID for workflow initiator or participant from the edcprincipalentity database table.

select id from edcprincipalentity where canonicalname='user_ID'

The query returns the principal ID for the specified user_ID.

(For workflow initiator) Execute the following command to retrieve all tasks associated with the principal ID for the initiator from the tb_task database table.

select * from tb_task where start_task = 1 and create_user_id= 'initiator_principal_id'

The query returns tasks initiated by the specified initiator_ principal_id. The tasks are of two types:

• Completed tasks: These tasks have been submitted and display an alphanumeric value in the process_instance_id field. Take note of all process instance IDs for submitted tasks and continue with the steps.
• Tasks initiated but not complete: These tasks have initiated but not submitted yet. The value in the process_instance_id field for these tasks is 0 (zero). In this case, take note of the corresponding task IDs and see Work with orphan tasks.

(For workflow participants) Execute the following command to retrieve process instance IDs associated with the principal ID of the process participant for the initiator from the tb_assignment database table.

select distinct a.process_instance_id from tb_assignment a join tb_queue q on a.queue_id = q.id where q.workflow_user_id='participant_principal_id'

The query returns instance IDs for all processes associated with the participant including those where the participant has not submitted any task.

Take note of all process instance IDs for submitted tasks and continue with the steps.

For orphan tasks or tasks where the process_instance_id is 0 (zero), take note of the corresponding task IDs and see Work with orphan tasks.

Follow the instructions in Purge user data from workflow instances based on process instance IDs section to delete user data for identified process instance IDs.

Execute the following database command:

select database_table from omd_object_type where name='pt_<app_name>/<workflow_name>'

The query returns a table name in tb_<number> format for the specified application ( app_name) and workflow ( workflow_name).

Review the tb_<number> table schema. The table contains variables that store user data for the specified workflow. The variables in the table correspond to the variables in the workflow.

Identify and take note of the variable that corresponds to workflow variable containing the user ID. If the identified variable is of primitive-type, you can run a query to determine workflow instances associated with a user ID.

Execute the following database command. In this command, the user_var is the primitive-type variable that contains user ID.

select process_instance_id from <tb_name> where <user_var>=<user_ID>

The query returns all process instance IDs associated with the specified user_ID.

Follow the instructions in Purge user data from workflow instances based on process instance IDs section to delete user data for identified process instance IDs.

Execute the following command to retrieve long-lived invocation ID and status for a process instance from the tb_process_instance table.

select long_lived_invocation_id, status from tb_process_instance where id='process_instance_id'

The query returns the long-lived invocation ID and status for the specified process_instance_id.

Create an instance of the public ProcessManager client ( com.adobe.idp.workflow.client.ProcessManager) using a ServiceClientFactory instance with the correct connection settings.

For more information, see Java API reference for Class ProcessManager.

Check the status of the workflow instance. If the status is other than 2 (COMPLETE) or 4 (TERMINATED), terminate the instance first by calling the following method:

ProcessManager.terminateProcess(<long_lived_invocation_id>).

Purge the workflow instance by calling the following method:

ProcessManager.purgeProcessInstance(<long_lived_invocation_id>)

The purgeProcessInstance method completely deletes all data for the specified invocation ID from AEM Forms server database and GDS, if configured.

Execute the following command on AEM Forms server database to retrieve IDs for the identified task IDs.

select id from tb_form_data where task_id=<task_id>

The query returns a list of IDs. For each ID ( fd_id) returned in the results, create a list of session ID strings as follows:

• _ wfattach<task_id>
• _wftask<fd_id>
• _wftaskformid<fd_id>

Depending on whether your GDS points to a file system or a database, perform one of the following steps:

1. GDS in file system

   In the GDS file system:

   1. Search for files with the following session ID strings as their extensions:

   • _wfattach<task_id>

   • _wftask<fd_id>

   • _wftaskformid<fd_id>

     The files with these extensions are the marker files. They are stored with filenames in the following format:

     <file_name_guid>.session<session_id_string>

   1. Delete all marker files and other files with the exact filename as <file_name_guid> from the file system.

2. GDS in database

   Execute the following commands for each session ID:

   code language-sql
   delete from tb_dm_chunk where documentid in (select documentid from tb_dm_session_reference where sessionid=<session_id>) delete from tb_dm_session_reference where sessionid=<session_id> delete from tb_dm_deletion where sessionid=<session_id>

Execute the following commands to delete data for task IDs from the AEM Forms server database:

delete from tb_task_acl where task_id=<task_id>
delete from tb_task_attachment where task_id=<task_id>
delete from tb_form_data where task_id=<task_id>
delete from tb_assignment where task_id=<task_id>
delete from tb_task where id=<task_id>