Mastering Siebel: SQL Query to Find Inbound Web Services and Associated Integration Objects

If you work with Siebel, you know how critical web services are in facilitating communication between systems. But finding specific details about inbound web services can be time-consuming, especially when you want to search based on parameters like Integration Objects (IO), business services, or workflows.

Wouldn’t it be easier if you could just query the database directly and retrieve all the necessary information? In this blog, I’ll share a versatile SQL query that helps you quickly locate inbound web services, along with associated details like workflows, business services, and integration objects.

Why Use This SQL Query?

Often, you may need to:

• Identify the workflow or business service associated with a web service.
• Find which integration object is linked to which web service.
• Check if an integration object is shared across multiple web services.
• Search for web services without logging into the Siebel application.

This SQL query can be adapted to search by various parameters, like service name, integration object name, and more. You can also modify it to list input/output arguments, making it an essential tool for Siebel administrators and developers.

The SQL Query

Here is a SQL query that will fetch all inbound web services in the Siebel application, along with details such as associated workflows or business services, input/output integration objects, and other relevant data:

Use this query to search Business Service based webservice

SELECT
    a.name                     "Service",
    d.name                     "Webservice",
    d.impl_type_cd             "BusServ/workflow",
    c.name                     "OperationName",
    c.method_name              "OperationDisplayName",
    servmetharg.arg_type       "BusServ Method type",
    servmetharg.name           "Argument Name",
    servmetharg.int_obj_name   io,
    servmetharg.storage_type
FROM
    s_ws_webservice   a,
    s_ws_port         b,
    s_ws_port_type    d,
    s_ws_operation    c,
    s_service         serv,
    s_repository      repos,
    s_service_meth    servmeth,
    s_service_m_arg   servmetharg
WHERE
    a.row_id = b.web_service_id
    AND d.impl_type_cd = 'BUS_SVC'
    AND trunc(a.created) >= '01-JAN-80'
    AND a.status_cd = 'ACTIVE'
    AND b.ws_port_type_id = d.row_id
    AND a.inbound_flg = 'Y'
    AND c.port_type_id = b.ws_port_type_id
    AND c.method_name = servmeth.name
    AND d.name = serv.name (+)
    AND repos.name = 'Siebel Repository'
    AND serv.repository_id = repos.row_id (+)
    AND servmeth.service_id (+) = serv.row_id
    AND servmetharg.service_meth_id (+) = servmeth.row_id
    AND servmetharg.data_type (+) = 'Integration Object'
ORDER BY
    a.name

Use this query to search Workflow based webservice

SELECT
    d.impl_type_cd      "BusServ/workflow",
    a.name              "WebserviceName",
    b.name              port_name,
    d.name              "service/WorkflowName",
    c.name              oprtn_name,
   -- c.method_name       oprtn_disp_name,
    wfpp.name           propertyname,
    wfpp.prop_def_type_cd,
    wfpp.int_obj_name   io_name --, b.port_address,b.port_transport
FROM
    s_ws_webservice   a,
    s_ws_port         b,
    s_ws_operation    c,
    s_ws_port_type    d,
    s_wfr_proc        wf,
    s_repository      repos,
    s_wfr_proc_prop   wfpp
WHERE
    a.row_id = b.web_service_id
    AND d.impl_type_cd = 'WORKFLOW'
    AND a.inbound_flg = 'Y'
    AND to_char(a.created, 'dd-MON-yy') >= '01-JAN-80'
    AND a.status_cd = 'ACTIVE'
    AND c.port_type_id = b.ws_port_type_id
    AND b.ws_port_type_id = d.row_id
    AND repos.row_id = wf.repository_id
    AND repos.name = 'Siebel Repository'
    AND wfpp.process_id = wf.row_id
    AND wfpp.data_type_cd = 'INT_OBJ'
    AND wf.status_cd = 'COMPLETED'
    AND wf.proc_name = d.name
ORDER BY
    a.created DESC,
    a.name,
    b.name,
    c.name

This query provides a holistic view of each web service, allowing you to gather the details without needing to log into the Siebel application.

Customization Options

One of the key advantages of this query is its flexibility. You can modify it based on your requirements:

1. Search by Service Name
2. Search by Integration Object

These are just a few examples. You can tweak this query further to suit your specific needs.

This SQL query is a powerful tool for anyone working with Siebel web services. It not only saves time but also allows you to quickly locate and understand the relationships between web services, workflows, business services, and integration objects. By modifying the query, you can focus on the exact details you need, making it easier to manage and troubleshoot your Siebel integrations.

Do you have a specific use case or additional query variations? Share your thoughts in the comments below!