Removing a pending application

NOTE: You will need access to TCIDP in order to perform this operation.

Determine which application is being referred to by running the attached query in TOAD, replacing the text “INSERT_TC_USER_ID” with the user’s TC User ID.
- See script below
From the list of results, determine which application is the one to be removed. There is usually only one application with Pending Tasks, but not always. If need be, confirm with the client. Appplicaion Date is a good reference point.
Once the application to be removed has been identified, get the relative application_id and related_application_id values from the list of results.
In TOAD, go to table UA019_TASK and locate any records with the application_id or related_application_id values. Update the value for TASK_STATUS_CD to 3 (Declined).
In TOAD, go to table AA009_APPLICATION and locate any records with the application_id or related_application_id values. Update the value for TASK_STATUS_CD to 3 (Declined).
Notify user.

TCID Application Locator

  SELECT
        ua019.inspector_person_id "inspector_person_id",
         ua019.application_id "application_id",
         -- UA019.retirement_id,
         aa009.related_application_id "related_application_id",
         ua019.task_sequence_num "task_sequence_num",
         --aa004.tc_user_id "tc_user_id",
         aa004.last_name_nm || ',' || aa004.first_name_nm AS "Inspector Name",
         tc040.application_function_etxt               "Application Type",
         tc021.task_type_etxt                          "Task Type",
         ta036.task_status_etxt                        "Task Status",
         ta011.inspector_group_etxt                    "Sent to Group",
         tc025.user_role_type_etxt "Sent to Person Role",
         (CASE
             WHEN ua019.sent_to_person_id IS NULL THEN ''
             ELSE (aa004_to.last_name_nm || ', ' || aa004_to.first_name_nm)
          END)
            AS "Sent To Person Name",
         --aa004_TO.tc_user_id AS "Sent To Person TC User ID",
         TO_CHAR(ua019.date_created_dte, 'YYYY-MM-DD') "Date Created",
         TO_CHAR(ua019.date_last_update_dte, 'YYYY-MM-DD') "Last Updated Date",
         aa004_updated.last_name_nm || ',' || aa004_updated.first_name_nm "Updated By",
         ua019.user_last_update_id "Updated By Id"
    FROM ua019_task ua019
         INNER JOIN aa004_person aa004 ON (ua019.inspector_person_id = aa004.person_id)
         INNER JOIN aa004_person aa004_updated ON (ua019.user_last_update_id = aa004_updated.person_id)
         LEFT JOIN aa009_application aa009 ON ua019.application_id = aa009.application_id
         LEFT JOIN tc021_task_type tc021 ON tc021.task_type_cd = ua019.task_type_cd
         LEFT JOIN tc040_application_function tc040 ON ua019.application_function_cd = tc040.application_function_cd
         LEFT JOIN tc025_user_role_type tc025 ON tc025.user_role_type_cd = ua019.sent_to_user_role_type_cd
         LEFT JOIN aa004_person aa004_to ON ua019.sent_to_person_id = aa004_to.person_id
         LEFT JOIN ta011_inspector_group ta011 ON ua019.sent_to_inspector_group_cd = ta011.inspector_group_cd
         INNER JOIN ta036_task_status ta036 ON ua019.task_status_cd = ta036.task_status_cd
   WHERE aa004.tc_user_id IN ('INSERT_TC_USER_ID')
         AND ua019.date_deleted_dte IS NULL
ORDER BY aa004.last_name_nm || ',' || aa004.first_name_nm,
         ua019.application_id DESC,
         ua019.task_sequence_num DESC,
         ua019.application_id;