Search case details based on active/completed assignment
We need to search case details based on active/completed assignment details like assigned id, assignment status, assignment urgency, assignment name etc. The active assignments exist in two tables – pc_assign_worklist, pc_assign_workbasket. And for completed assignments we need to search history table as is done OOTB. The problem is first we will have some 200 case ids fetched from elastic search and then we need to filter that 200 cases again based on assignment details. If we go by OOTB, we need to join 200 case ids with Assign-Worklist table, then another join with Assign-Workbasket table and then with History table. Next, we need to merge the results from all the joins to filter out the duplicates. The said approach is complex.
We are thinking about creating a customized task tables where data will be populated from both pc_assign_worklist and pc_assign_workbasket tables. We will have a trigger built on save/delete on assignment tables. This task table will be used for all reporting/search purposes. We need Pega’s suggestion whether having this customized task table is advisable or not.
We can reuse the existing history table to search on both active/completed assignment details. The data that exists there does not fully serve our purpose. But we can make it work if we have some 2-3 more columns (urgency, status) created in that history table and Pega needs to update those column values when there is any change in assignment. This will be an enhancement request.
***Edited by Moderator Marissa to update SR Details***