25.2.6.8.5 Notifying User of Temporary Password

Email the approved patient their portal username and temporary password from PL/SQL.

The final step of user account provisioning is notifying the user of their new username and temporary password. While you could send the email from the workflow, here you explore the programmatic way to send an email using SEND_MAIL in the APEX_EMAIL package. It's using an email template for the new account notification that uses a number of placeholder values. Notice that the P_PLACEHOLDERS argument expects to receive the placeholder name/value pairs as a JSON document. The local APPROVAL_PLACEHOLDERS helper function builds up that JSON document using PL/SQL's native JSON_OBJECT_T type and returns it as a CLOB in JSON format.

-- in package frc_onboarding
procedure send_approval_email(
    p_patient_id         in number,
    p_temporary_password in varchar2)
is  
    l_patient   t_patient := patient(p_patient_id);
    -----------------------------------------------
    function approval_placeholders(
        p_patient            in t_patient,
        p_temporary_password in varchar2)
        return                  clob  
    is  
        l_json json_object_t := json_object_t;  
        l_clinic_app_url varchar2(255); 
    begin
        l_clinic_app_url := frc_app.url_for_page(
                                frc_app.clinic_app_id,'home');
        l_json.put('FIRST_NAME',
                    p_patient.first_name);  
        l_json.put('PROCEDURE_DESCRIPTION',
                    p_patient.procedure_description);  
        l_json.put('PORTAL_URL',
                    l_clinic_app_url);  
        l_json.put('USERNAME',
                    lower(p_patient.username));  
        l_json.put('TEMPORARY_PASSWORD',
                    p_temporary_password);  
        return l_json.stringify();  
    end;     
begin  
    apex_mail.send(
        p_to                 => l_patient.email,  
        p_from               => frc_app.get_sender_email,          
        p_template_static_id => frc_app.c_approved_email_template,  
        p_placeholders       => approval_placeholders(
                                    l_patient, 
                                    p_temporary_password),  
        p_application_id     => frc_app.onboarding_app_id());  
    -- Force mail to be sent immediately                 
    apex_mail.push_queue;                         
end;