25.3.5.5.4 Generating the Data-Driven Document

Generate a PDF invoice and store it for review, download, and email delivery.

The workflow's Prepare Invoice activity ends up calling the GENERATE_INVOICE procedure below in the FRC_CLINIC package. This procedure looks up the procedure ID from the invoice ID, sets that value into the application item PROCEDURE_ID_TO_INVOICE used as a bind variable in the report query, and calls GENERATE_DOCUMENT in the APEX_PRINT package to create the pixel-perfect PDF invoice as a BLOB. Notice it passes the Static ID values of the Report Query and Report Layout, as well as the current application ID using V('APP_ID').

Finally, it updates the FRC_INVOICES row to assign the generated PDF to its DOCUMENT column. This lets staff review it interactively in the Woods Clinic app and download it. It also helps your workflow attach it to the email it eventually sends to the patient.

Tip:

The multiple calls to the UPDATE_INVOICE_STATUS procedure provide feedback that appears in the Review Invoices page in the app. The status info helps users see the report generation is in progress in the background.

-- in frc_clinic package
procedure generate_invoice(
    p_invoice_id in number)
is
    l_pdf blob;
    l_procedure_id number;
begin
    -- Lookup patient procedure id for invoice for report query bind var
    select patient_procedure_id
      into l_procedure_id
      from frc_invoices
     where id = p_invoice_id;
    -- Set application item used as a bind variable in report query
    apex_session_state.set_value('PROCEDURE_ID_TO_INVOICE',l_procedure_id);
    update_invoice_status(p_invoice_id,'Processing');
    commit;
    -- Generate the pixel-perfect PDF invoice
    l_pdf := apex_print.generate_document(
                p_application_id          => v('APP_ID'),
                p_report_query_static_id  => 'invoice_query',
                p_report_layout_static_id => 'invoice_layout');
    update frc_invoices
    set status = 'Prepared',
        document = l_pdf
    where id = p_invoice_id;
exception
    when others then
        update_invoice_status(p_invoice_id,'Error');
        commit;
        raise;
end;