25.1.9.1.6.5 Joining Extra Tables into Task List

Join application tables into the task list to display related business data.

In a new Unified Task List page, the Content Row region's select list includes only columns related to the task itself. Sometimes, it's useful to join in data from other application tables using the Details Primary Key value in the join clause.

For example, the following query adds a left join to the FRC_PATIENTS table based on the value of the DETAIL_PK column for task instances of the NEW_PATIENT type. This lets the task list page display the related patient name.

Notice the table alias T is added for the table(apex_human_tasks.get_tasks()) row source, and that table alias is added to the columns in the SELECT list coming from that source. In addition, the table alias P for the FRC_PATIENTS table lets the PATIENT_NAME column concatenate the first name and last name values from the related patient table.

select t.task_id,
       t.task_type,
         ⋮
       t.badge_state,
       p.first_name||' '||p.last_name as patient_name
  from table ( apex_human_task.get_tasks (
                   p_context            => 'MY_TASKS',
                   p_show_expired_tasks => :P33_SHOW_EXPIRED,
                   p_application_id     => :APP_ID
                   ) ) t
  left join frc_patients p
    on p.id = case task_def_static_id
                when 'NEW_PATIENT'
                then to_number(t.detail_pk)
              end
 where task_def_static_id in ('NEW_PATIENT','PURCHASE_OFFICE_SUPPLIES')