You are a senior Oracle APEX Workflow and PL/SQL incident en...

Prompt

You are a senior Oracle APEX Workflow and PL/SQL incident engineer. You have no access to a database, repository, internet, or external tools. Analyze only the evidence below. Clearly separate confirmed facts, logical inferences, and items requiring runtime verification. ## System Architecture - Application 300 hosts the active APEX automations. - Application 210 owns the workflow definitions and business pages. - Automation copies in Application 210 are disabled legacy components. - Both applications are in the same APEX workspace. - The database uses Oracle APEX 26.1. ## Incident A: Workflow-Starting Automation An active automation named `EMPLOYEE_EXTENSION` runs in Application 300 every two minutes. It contains four PL/SQL actions: 1. `DEPARTURE_WORKFLOW` 2. `RETURN_WORKFLOW` 3. `RESIDENCY_FEES_WORKFLOW` 4. `WORK_PERMIT_FEES_WORKFLOW` Each action contains this call: APEX_WORKFLOW.START_WORKFLOW( P_APPLICATION_ID => V('APP_ID'), P_STATIC_ID => 'EMPLOYEE_EXTENSION_WF', P_DETAIL_PK => CURRENT_RECORD.ACTION_ID, P_PARAMETERS => ... ); Metadata inventory: | Application | Workflow static ID | State | |-------------|--------------------|-------| | 210 | EMPLOYEE_EXTENSION_WF | ACTIVE | | 225 | EMPLOYEE_EXTENSION_WF | ACTIVE | | 300 | No matching definition | N/A | Recent automation history: | Time | Status | Failing action | |------|--------|----------------| | 10:00 | Successful | None; no qualifying rows | | 10:02 | Successful | None; no qualifying rows | | 10:04 | Failure | RETURN_WORKFLOW | | 10:06 | Failure | RETURN_WORKFLOW | | 10:08 | Failure | RETURN_WORKFLOW | Error details: ORA-01403: no data found ORA-06512: at "APEX_260100.WWV_FLOW_WORKFLOW" ORA-06512: at "APEX_260100.WWV_FLOW_WORKFLOW_API" ORA-06512: at line 31 At 10:04, one Return candidate became eligible: - `ACTION_ID = 5001001` - Action status is `DONE` - Visa status is `USED` - Exit date is populated - Actual return date is null - `RETURN_WORKFLOW_ID` is null - No current non-terminated Return workflow exists The action updates `RETURN_WORKFLOW_ID` only after `START_WORKFLOW` succeeds. ## Incident B: Cleanup Automation A second active automation named `WORKFLOW_CLEANUP` is also hosted in Application 300. It is intended to process completed or terminated workflows belonging to Application 210. Its proposed query contains: AND W.APPLICATION_ID = :APP_ID Two developers suggested alternative fixes: - Developer A: Replace `:APP_ID` with `210`. - Developer B: Remove the application predicate so the automation can find every workflow in the workspace. Other applications in the workspace also contain active workflows that must not be processed by this automation. ## Incident C: Duplicate Human Tasks After an application import, some active workflows were suspended and resumed. One affected workflow now has: - Workflow state: `ACTIVE` - Two open APEX human tasks for the same task definition - One task created before the import - One task created immediately after resume - Both APEX tasks are genuinely `UNASSIGNED` - A custom task-log table displays only one of them Activity history shows: - The numerically largest `ACTIVITY_ID` belongs to an older completed activity. - A smaller activity ID belongs to the actual waiting task. - Resume continued from the activity with the largest ID and created another task. Previous testing demonstrated: - Calling `APEX_HUMAN_TASK.CANCEL_TASK` against a duplicate task while its workflow is `ACTIVE` faults the workflow. - Marking one custom task-log row `CANCELED` can hide it from the business page without changing the APEX task. - A later synchronization can copy the APEX `UNASSIGNED` state back into the custom log. - A bulk resume loop can lose earlier successful work when a later workflow fails and rolls back the transaction. ## Your Tasks 1. Diagnose the root cause of Incident A. 2. Explain why the automation succeeded before a candidate appeared. 3. Determine whether the defect affects only `RETURN_WORKFLOW` or all four actions. 4. Provide the smallest safe code correction. 5. Explain why using the workflow definition from Application 225 would be unsafe even though its static ID matches. 6. Decide how the application predicate in Incident B should be written. 7. Explain the risk of removing that predicate. 8. Explain Incident C without incorrectly blaming the custom task-log table. 9. State what must never be done to a duplicate task belonging to an active workflow. 10. Propose a safe temporary containment for visible duplicate tasks. 11. Explain why that containment may need to be reapplied. 12. Recommend transaction handling for a bulk workflow-resume procedure. 13. Design a Stage validation sequence that proves: - The corrected automation creates the workflow in the intended application. - The business record receives the new workflow ID. - Exactly one new active workflow is created. - Exactly one open human task is created. - The candidate disappears after processing. - A second automation execution creates nothing. - No new relevant errors appear. 14. Provide a production deployment, rollback, and monitoring checklist. 15. Identify which conclusions are confirmed by the supplied evidence and which still require live verification. ## Safety Constraints - Do not propose importing an entire application for a one-line automation correction. - Do not cancel human tasks belonging to active workflows. - Do not modify unrelated packages or tables. - Do not remove application boundaries merely to increase the number of matched rows. - Treat custom logs and caches as derived data; verify the authoritative APEX workflow and task states first. - Require a successful first run and an idempotent second run before declaring the issue fixed. ## Required Response Format Use these labelled sections: 1. Executive Verdict 2. Incident A Root Cause 3. Incident B Predicate Decision 4. Incident C Duplicate-Task Analysis 5. Exact Minimal Corrections 6. Stage Test Plan 7. Production Deployment and Rollback 8. Safety Warnings 9. Confirmed Facts vs Inferences 10. Remaining Unknowns Lead with conclusions, support every conclusion using the supplied evidence, and avoid unnecessary explanation.