Harnessing the Power of Oracle Fusion AI Agents in Oracle APEX Extensions.

CREATE OR REPLACE VIEW XX_FUSION_AGENT_CHATS_V 
AS   
SELECT seq_id,       
c001 user_name,       
c002 workflow_code,       
c003 workflow_version,       
c004 job_id,       
c005 conversation_id,       
c006 message_owner,       
c007 message_text,       
c008 start_date    
FROM apex_collections    
WHERE collection_name = 'XX_FUSION_AGENT_CHATS';

{
  "input" : "sample_input",
  "output" : "sample_output",
  "progressMessage" : "",
  "error" : null,
  "followUpQuestions" :
  [
  ],
  "jobId" : "sample_jobId",
  "conversationId" : "sample_conversationId",
  "traceId" : "sample_traceId",
  "status" : "COMPLETE",
  "supportingChunks" :
  [
    {
      "textChunk" : "sample_textChunk",
      "vectorSearchScore" : 0.632657,
      "textSearchScore" : -1,
      "documentIdentificationCriteria" :
      {
        "documentType" : "application/pdf",
        "documentIdentifier" : "300000",
        "documentCategory" : null,
        "entityId" : null,
        "entityName" : null,
        "documentTitle" : "Policies",
        "parentObjectId" : null,
        "grandparentObjectId" : null,
        "rootObjectId" : null,
        "pastDue" : false
      },
      "additionalContext" :
      {
        "document" : "300000"
      }
    }
  ],
  "citations" :
  [
    {
      "citedText" : "sample_citedText",
      "documentIdentificationCriteria" :
      {
        "documentType" : "application/pdf",
        "documentIdentifier" : "300000",
        "documentCategory" : null,
        "entityId" : null,
        "entityName" : null,
        "documentTitle" : "Policies",
        "parentObjectId" : null,
        "grandparentObjectId" : null,
        "rootObjectId" : null,
        "pastDue" : false
      },
      "additionalContext" :
      {
        "document" : "300000"
      }
    }
  ],
  "nodes" :
  [
  ],
  "waitingToolCode" : null,
  "workflowCode" : "PROCUREMENT_POLICY_ADVISOR",
  "workflowName" : null,
  "architecture" : null,
  "workflowVersion" : 17,
  "workflowStatus" : "PUBLISHED",
  "startDate" : 1786531118905,
  "endTime" : 1786531140469,
  "createDate" : "2026-08-12T10:38:38.905+0000",
  "endDate" : "2026-08-12T10:39:00.469+0000",
  "timeTaken" : 0,
  "inputTokenCount" : 0,
  "outputTokenCount" : 0,
  "waitingNodeDetails" : null,
  "preview" : false
}

.xx-Chat .t-Chat--own .t-Comments-icon {
  margin-right: 0;
  margin-left: 12px;
}

.xx-Chat .t-Chat--own {
  flex-direction: row-reverse;
}

.xx-Chat .t-Chat--own .t-Comments-body {
  align-items: flex-end;
}

.xx-Chat .t-Chat--own .t-Comments-comment:after {
  border-left-color: var(--ut-comment-chat-background-color);
  border-right-color: rgba(0,0,0,0);
  right: none;
  left: 100%;
}

.xx-Chat .t-Comments {
   height: 470px;
   overflow: scroll;
}

.blinking-text {
    animation: blinker 1s linear infinite;
}

@keyframes blinker {
  50% {
            opacity: 0;
        }
    }

select to_number(seq_id) seq_id,         
case message_owner         
when 'USER' then user_name         
else 'AI Agent'         
end user_name,        
case message_text        
when :P_AGENT_TEAM_PROCESSING_MESSAGE then '<span class="blinking-text">'||:P_AGENT_TEAM_PROCESSING_MESSAGE||'</span>'        
else message_text        
end comment_text,        
start_date comment_date,        
case message_owner         
when 'USER' then apex_string.get_initials(user_name)         
else 'AI'         
end  user_icon,        
null                     as actions,        
null                     as attribute_1,        
null                     as attribute_2,        
null                     as attribute_3,        
null                     as attribute_4,        
case message_owner         
when 'USER' then 't-Chat--own'         
else null        
end comment_modifiers   
from xx_fusion_agent_chats_v   
order by seq_id desc

--This code can be moved to a PL/SQL package.
begin apex_collection.create_or_truncate_collection('XX_FUSION_AGENT_CHATS');   apex_collection.add_member(         
p_collection_name => 'XX_FUSION_AGENT_CHATS',         
p_c001            => :APP_USER,         
p_c002            => :P_AGENT_TEAM_CODE,         
p_c003            => :P_AGENT_TEAM_VERSION,         
p_c004            => null,         
p_c005            => null,         
p_c006            => 'AGENT',         
p_c007            => :P_AGENT_TEAM_WELCOME_MESSAGE,         p_c008            => SYSDATE     
); 
end;

--This code can be moved to a PL/SQL package.
declare 
lc_http_response      clob; 
lv_job_id             varchar2(500); 
lv_conversation_id    varchar2(500); 
l_params              apex_exec.t_parameters; 
begin     
apex_exec.add_parameter(l_params, 'P_AGENT_TEAM_CODE', :P_AGENT_TEAM_CODE);     
apex_exec.add_parameter(l_params, 'P_AGENT_TEAM_VERSION', :P_AGENT_TEAM_VERSION);     
apex_exec.add_parameter(l_params, 'P_AGENT_TEAM_MESSAGE', :PXX_USER_MESSAGE);     
apex_exec.add_parameter(l_params, 'P_CONVERSATION_ID', :PXX_CONVERSATION_ID);       
apex_exec.execute_rest_source(         
p_static_id  => 'FUSION_AGENT_INVOKEASYNC',         
p_operation  => 'POST',         
p_parameters => l_params     
);     
lc_http_response      := apex_exec.get_parameter_clob(l_params, 'o-response-body');       
Select json_value(lc_http_response,'$.jobId'),     json_value(lc_http_response,'$.conversationId')     
into lv_job_id,lv_conversation_id     
from dual;     
apex_collection.add_member(         
p_collection_name => 'XX_FUSION_AGENT_CHATS',         
p_c001            => :APP_USER,         
p_c002            => :P_AGENT_TEAM_CODE,         
p_c003            => :P_AGENT_TEAM_VERSION,         
p_c004            => lv_job_id,         
p_c005            => lv_conversation_id,         
p_c006            => 'USER',         
p_c007            => :PXX_USER_MESSAGE,         
p_c008            => SYSDATE     
);       
apex_collection.add_member(         
p_collection_name => 'XX_FUSION_AGENT_CHATS',         
p_c001            => :APP_USER,         
p_c002            => :P_AGENT_TEAM_CODE,         
p_c003            => :P_AGENT_TEAM_VERSION,         
p_c004            => lv_job_id,         
p_c005            => lv_conversation_id,         
p_c006            => 'AGENT',         
p_c007            => :P_AGENT_TEAM_PROCESSING_MESSAGE,         p_c008            => SYSDATE     
);     
:PXX_JOB_ID  := lv_job_id;     
:PXX_CONVERSATION_ID := lv_conversation_id;     
:PXX_USER_MESSAGE := NULL; 
EXCEPTION WHEN OTHERS THEN 
apex_debug.error('Error executing REST Source : Submit the User message :' ||SQLERRM); 
RAISE; 
end;

--This code can be moved to a PL/SQL package.
declare
    l_params  apex_exec.t_parameters;
    l_rc      apex_exec.t_context;
    lv_status VARCHAR2(500);
    lv_output VARCHAR2(32767);
    ln_seq_id NUMBER;
begin
    loop
        l_params.DELETE;
        apex_exec.add_parameter(l_params, 'P_AGENT_TEAM_CODE', :P_AGENT_TEAM_CODE);
        apex_exec.add_parameter(l_params, 'P_JOB_ID', :PXX_JOB_ID);
        l_rc := apex_exec.open_rest_source_query(
            p_static_id  => 'FUSION_AGENT_INVOKEASYNC',
            p_parameters => l_params
            );
    
        WHILE apex_exec.next_row(l_rc) 
        LOOP
            lv_status := apex_exec.get_varchar2(l_rc, apex_exec.get_column_position( l_rc, 'STATUS' ));
            lv_output := apex_exec.get_varchar2(l_rc, apex_exec.get_column_position( l_rc, 'OUTPUT' ));
            apex_exec.close_array(l_rc);
        END LOOP;
        
        apex_exec.close(l_rc);
        DBMS_SESSION.SLEEP(2); -- Decide the sleep time based on the activities performed by the Agent.
        exit when lv_output is not null;
    end loop;
    select seq_id
      into ln_seq_id
      from XX_FUSION_AGENT_CHATS_V
     where seq_id = (select max(seq_id) from XX_FUSION_AGENT_CHATS_V where message_text like :P_AGENT_TEAM_PROCESSING_MESSAGE||'%');
    apex_collection.update_member(
        p_collection_name => 'XX_FUSION_AGENT_CHATS',
        p_seq             => ln_seq_id,
        p_c001            => :APP_USER,
        p_c002            => :P_AGENT_TEAM_CODE,
        p_c003            => :P_AGENT_TEAM_VERSION,
        p_c004            => :PXX_JOB_ID,
        p_c005            => :PXX_CONVERSATION_ID,
        p_c006            => 'AGENT',
        p_c007            => lv_output,
        p_c008            => SYSDATE
        );
EXCEPTION
WHEN OTHERS THEN
apex_debug.error('Error executing REST Source : Check Job status :' ||SQLERRM);
RAISE;
end;