25.3.6.2 Simplifying Event Management

Define a package API that lets the trigger add events and the automation process them.

The FRC_PROCEDURE_LIFECYCLE package offers a simple API for managing lifecycle events. Its package specification shown below has procedures to add and process an event, and a function that returns the list of events.

package frc_procedure_lifecycle is
    type t_lifecycle_event is record (id frc_patient_procedure_events.id%type);
    type t_lifecycle_events is table of t_lifecycle_event;
    ------------------------------------------------------------------
    -- Add procedure lifecycle event to table processed by automation
    ------------------------------------------------------------------
    procedure add_event(
        p_procedure_id in number,
        p_type         in varchar2);
    ------------------------------------------------------------------
    -- Process procedure lifecycle event (called by automation)
    ------------------------------------------------------------------
    procedure process_event(
        p_event_id in number);
    ------------------------------------------------------------------
    -- Return list of event ids to process (used in automation query)
    ------------------------------------------------------------------
    function event_list
    return t_lifecycle_events
    pipelined;
end frc_procedure_lifecycle;

The ADD_EVENT procedure simply inserts a row into the FRC_PATIENT_PROCEDURE_EVENTS table. The table includes a primary key ID that is defaulted when null from an identity sequence, and a CREATED timestamp column that defaults to SYSTIMESTAMP. So here you only need to insert the patient procedure ID and the event type passed in (e.g. NEW, STATUS, or SCHEDULE).

-- external in package frc_procedure_lifecycle
procedure add_event(
    p_procedure_id in number,
    p_type         in varchar2)
is
begin
    insert into frc_patient_procedure_events(procedure_id, event_type)
    values (p_procedure_id, p_type);
end add_event;

The PROCESS_EVENT accepts the unique ID of a lifecycle event and signals a status or schedule change to a medical procedure's workflow. It calls the NOTIFY_DATA_CHANGED helper procedure to do so. If the process is successful, it deletes the event row it just processed from the FRC_PATIENT_PROCEDURE_LIFECYCLE table.

-- external in package frc_procedure_lifecycle
procedure process_event(
    p_event_id in number)
is
    l_success boolean := false;
begin
    for j in (select id, procedure_id, event_type
                from frc_patient_procedure_events
               where id = p_event_id )
    loop
        if j.event_type in ('STATUS','SCHEDULE') then
            l_success := notify_data_changed(j.procedure_id,j.event_type);
            if l_success then
                delete from frc_patient_procedure_events
                where id = p_event_id;
            end if;
        end if;            
    end loop;
end process_event;

The EVENT_LIST function returns a table of T_LIFECYCLE_EVENT records. Each record contains a single ID field. Notice the PIPELINED modifier and corresponding PIPE ROW statement inside the loop. This combination defines a function whose result you can use in a SELECT statement as a row source by "wrapping" the function call with the TABLE() operator.

function event_list
return t_lifecycle_events
pipelined
is
begin
    for j in (select id
                from (select id,
                             row_number() over
                             (partition by procedure_id 
                              order by created) as rn
                      from frc_patient_procedure_events
                  )
                 where rn = 1)
    loop
        pipe row (t_lifecycle_event(j.id));
    end loop;
exception
    when no_data_needed then
        null;
end event_list; 

Tip:

EVENT_LIST's cursor for loop uses the ROW_NUMBER() analytic function – aliased to RN – along with the WHERE RN = 1 clause to ensure only a single event per distinct PROCEDURE_ID value is returned. Using the ORDER BY CREATED in the windowing clause guarantees that the single event returned for each procedure ID is the one that occurred earliest in time. This simplifies the event processing so that only one event per patient procedure ID row is processed in a given automation job run.