Fast Keyword Search in Siebel Workflows Using PL/SQL

Working with Siebel workflows can sometimes be challenging when you need to find specific details in various workflow components like steps, inputs, and outputs. Instead of manually browsing through the repository, you can take advantage of this powerful PL/SQL query that allows you to search for specific keywords throughout workflows, steps, and arguments. This not only improves efficiency but also speeds up your repository searches.

Here’s how you can use this query for fast searching of workflows with any particular keyword of your choice.

declare
   string_to_be_searched_for varchar2 (255) default 'your keyword';      -- here include the string you are searching for
    loopnumber     integer;

    cursor cur_loop
    is
select 'Worfklow Process Property' ObjType,
    bc.name businesscomponent,
    sc.name scriptname,
     sc.char_val script
from siebel.S_WFR_PROC_PROP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'  
union all
select 'Worfklow Process Property' ObjType,
    bc.name businesscomponent,
    sc.name scriptname,
     sc.INT_OBJ_NAME script
from siebel.S_WFR_PROC_PROP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'
 union all
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'STEP BUSCOMP_NAME -' ||sc.name scriptname,
       sc.ACTION_BUSCOMP script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'   
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'SERVICE_NAME -' ||sc.name scriptname,
       sc.SERVICE_NAME script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'METHOD_NAME -' ||sc.name scriptname,
       sc.METHOD_NAME script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository' 
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'COMMENTS -' ||sc.name scriptname,
       sc.COMMENTS script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'        
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'DESC_TEXT -' ||sc.name scriptname,
       sc.DESC_TEXT script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'     
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'SUBPROCESS_NAME -' || sc.name scriptname,
       sc.SUBPROCESS_NAME script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'TASK_NAME -' ||sc.name scriptname,
       sc.TASK_NAME script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'
union all 
select 'Worfklow Process Step' ObjType,
    bc.name businesscomponent,
    'UI_VIEW -' ||sc.name scriptname,
       sc.UI_VIEW script
from siebel.S_WFR_STP sc, siebel.S_WFR_PROC bc, siebel.s_repository r
where  sc.PROCESS_ID = bc.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'    
union all 
select 'Worfklow Process Step IO' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.BUSCOMP_NAME script
from siebel.S_WFR_STP_ARG sc, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and sc.STEP_ID = bc1.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'
union all 
select 'Worfklow Process Step IO' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.BUSCOMP_FLD_NAME script
from siebel.S_WFR_STP_ARG sc, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and sc.STEP_ID = bc1.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'    
union all 
select 'Worfklow Process Step IO' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.COMMENTS script
from siebel.S_WFR_STP_ARG sc, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and sc.STEP_ID = bc1.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'    
union all 
select 'Worfklow Process Step IO' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.OUTPUT_ARG script
from siebel.S_WFR_STP_ARG sc, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and sc.STEP_ID = bc1.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'   
union all 
select 'Worfklow Process Step IO' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.VAL script
from siebel.S_WFR_STP_ARG sc, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and sc.STEP_ID = bc1.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository'    
union all 
select 'Worfklow Process Branch Condition' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.BUSCOMP_NAME script
from siebel.S_WFR_COND_CRIT sc, siebel.S_WFR_STP_BRNCH br, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and br.STEP_ID = bc1.row_id
    and sc.BRANCH_ID = br.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository' 
union all 
select 'Worfklow Process Branch Condition' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.APPLET_FLD_NAME script
from siebel.S_WFR_COND_CRIT sc, siebel.S_WFR_STP_BRNCH br, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and br.STEP_ID = bc1.row_id
    and sc.BRANCH_ID = br.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository' 
union all 
select 'Worfklow Process Branch Condition' ObjType,
    bc.name businesscomponent,
    bc1.name scriptname,
       sc.PROC_PROP_NAME script
from siebel.S_WFR_COND_CRIT sc, siebel.S_WFR_STP_BRNCH br, siebel.S_WFR_PROC bc, siebel.S_WFR_STP bc1, siebel.s_repository r
where  bc1.PROCESS_ID = bc.row_id
    and br.STEP_ID = bc1.row_id
    and sc.BRANCH_ID = br.row_id
    and r.row_id = bc.repository_id
     and bc.status_cd = 'COMPLETED'
    and r.name = 'Siebel Repository';

    posizione      integer (10);
begin
    loopnumber  := 0;

    for crs_loop in cur_loop
    loop
        posizione   := instr (crs_loop.script, string_to_be_searched_for);
        loopnumber  := loopnumber + 1;

        if posizione > 0
        then
            dbms_output.put_line (substr (   crs_loop.ObjType
                                          || ';'
                                          || crs_loop.businesscomponent
                                          || ';'
                                          || crs_loop.scriptname
                                          || ';'
                                          || posizione, 1, 255));
        end if;
    end loop;

    dbms_output.put_line (substr (to_char (loopnumber) || ' loops', 1, 255));
end;

Use Cases:

• Workflow Debugging: Easily locate which workflows or steps use certain keywords, helping to debug issues in the logic.
• Analysis and Audit: When performing analysis on workflows, quickly identify specific workflows that contain certain steps or arguments.
• Optimization: Since the query leverages Siebel’s repository structure directly, it’s faster than navigating Siebel’s Tools/GUI for such searches.

This PL/SQL query provides a fast and efficient way to search for keywords in Siebel workflows and their associated components. It’s a useful tool for developers and administrators working with Siebel CRM, offering a quick way to analyze or troubleshoot workflows in the repository. By reducing the time spent on searches, it allows you to focus on making your workflows more effective.