25.2.6.8.4 Handling New Account Requests

Process queued account requests, create users, and email portal credentials from PL/SQL.

A DBMS_SCHEDULER job calls the procedure below every minute to check the new account creation request table. It loops over any rows in that table, generates a temporary password, creates the new APEX end-user account as part of the Patients group that was recorded in the request table. Then it notifies the new user of their patient portal credentials by calling a SEND_APPROVAL_EMAIL in the FRC_ONBOARDING package. This email could have been sent using another Send E-Mail workflow activity, but doing it from PL/SQL is an opportunity to see how to send mail programmatically.

Tip:

The procedure uses EXECUTE IMMEDIATE in this example to avoid a circular dependency between the FRC_APEX_USER package containing HANDLER_NEW_USERS_QUEUE and the FRC_ONBOARDING package containing the rest of the patient onboarding application logic.

-- package frc_apex_user
procedure handle_new_users_queue is
    l_temp_password varchar2(255);
begin
    -- set workspace context
    apex_util.set_workspace('WOODS');
    for x in (select * from frc_apex_users_queue)
    loop
        -- Generate temporary password
        l_temp_password := generate_password();
        -- create the apex user in the supplied group if present
        apex_util.create_user
        (
            p_user_name     => x.username,
            p_web_password  => l_temp_password,
            p_email_address => x.email,
            p_group_ids     => x.group_id
        );
        execute immediate q'~
          begin 
             frc_onboarding.send_approval_email(
                :PATIENT_ID,
                :TEMP_PASSWORD);
          end;
        ~' using x.patient_id,
                 l_temp_password;
        -- remove the user from the queue
        delete from frc_apex_users_queue where id = x.id;
    end loop;
end;