18.6.3 Querying Progress of Background Process

Query active background jobs to show status messages and percent complete.

The Employee Excellence page's Award Review Progress classic report region uses the following query to show the progress of all active background processes related to the page. It filters on the current page's process name: Review for Reward (in Background). Notice it only includes rows with STATUS_CODE values of ENQUEUED, SCHEDULED, and EXECUTING.

The query computes the percent complete by dividing the SOFAR value by the TOTALWORK. The PROGRESS select list column multiplies this ratio by 100 to display the value as a percentage. The PROGRESS_BAR column leaves the value as the decimal the Percent Graph report column type expects.

To show the employee name being processed, it joins the EBA_DEMO_EMP table using the CONTEXT_VALUE associated with each background process job.

Tip:

Other STATUS_CODE values include SUCCESS, FAILED, and ABORTED.

select e.ename,
       case when bgp.sofar is not null and bgp.totalwork is not null
            then round(bgp.sofar/bgp.totalwork*100)||'%'
            else '⏳'
       end progress,
       nvl(round(bgp.sofar/bgp.totalwork*100),0) progress_bar,
       coalesce(bgp.status_message,'⏳') as status_message
  from apex_appl_page_bg_proc_status bgp
  join eba_demo_emp e 
    on e.empno = to_number(bgp.context_value)
 where    bgp.process_name = 'Review for Reward (in Background)'
      and bgp.application_id = :APP_ID
      and bgp.page_id        = :APP_PAGE_ID
      /* Background Job is Waiting or Doing Work */
      and bgp.status_code in ( 'ENQUEUED','SCHEDULED', 'EXECUTING')