Not able to respond to Action Items in My Workspace

Products

• Clarity PPM On Premise

Issue/Introduction

You are not able to respond to Action Items in My Workspace.

It is happening for both Process and Library Action Items.

When the assignee take action by clicking on an action button, nothing happens.

Action items responses are only working through the classic interface.

STEPS TO REPRODUCE: 
1- Create Action Item in Modern - Administration - Action Items
2- Give 3 status codes or more, assign it to be assigned to you
3- Add Action Item to a blueprint with a business rule set to fire on attribute update
4- Apply blueprint to a project
5- Update the project to trigger the blueprint rule
6- Go to My Workspace - Action Items
7- Click on Status buttont to Approve or Reject, etc... nothing happens
8- There is an error in the logs on a query - 
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "CAI.ID" could not be bound.

Expected Results: Clicking on Action Item button works

Actual Results:  Nothing happens when clicking on the button

Environment

Release : 16.1.2, MS SQL 

Cause

There is a bad SQL Join causing the issue.  The app-ca.log files will have an error similar to:

<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="odf-pmd.actionitem_assignee.select-list-rest-api-conditional.map_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" uniqueSortColumn="odf_pk" xmlns="http://schemas.niku.com/2002/pmd">
  <secondaryInput id="objectContext" inputSource="xml" inputLocation="/data"/>
</statementRef>

Using input: 
{@ODF_WHERE@=and cal_action_item_assignees.CAL_ACTION_ITEM_ID in (6090001,6089003,6088004 ), requiredDataProviderItems_last_updated_date=last_updated_date, requiredDataProviderItems_assignee_status=assignee_status, ___odf_dummy=xyzzy, requiredDataProviderItems_assignee_id=assignee_id, requiredDataProviderItems_cal_action_item_id=cal_action_item_id, requiredDataProviderItems_id=id}
Using dynamic SQL: 
tag: @ODF_WHERE@ SQL:and cal_action_item_assignees.CAL_ACTION_ITEM_ID in (6090001,6089003,6088004 )
tag: @ODF_ORDER_BY@ SQL: partition by cal_action_item_id order by odf_pk
tag: @ODF_ROWS_PARTITION_BY@ SQL:cal_action_item_id

...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "CAI.ID" could not be bound.

Resolution

1- Check if the feature is disabled by running: 

select name, enabled from cmn_features where code = 'S207096_ANSI_JOIN'

If it's disabled, proceed with step 2

2- Run the below command in the database 

admin toggle-feature S207096_ANSI_JOIN 1

Restart all services