Displaying Order Items in a Hierarchical Structure with SQL for Siebel Order Management

In Siebel Order Management, especially when dealing with complex orders that contain multiple products and services, it’s important to understand the parent-child relationship between various order items. Knowing which item belongs to which product group or bundle can help in troubleshooting and understanding how the system is structured.

In this blog, we will cover an SQL query that retrieves order items in a hierarchical format. This query will be particularly useful for Siebel developers, administrators, and analysts who need to comprehend the structure of order data.

---

SQL Query Explanation

The query below will display the order items in a parent-child hierarchy, making it easier to visualize how items are related within an order. Each parent item (like a product bundle) will show its child items (like individual products or services within that bundle).

WITH q1(created, row_id, par_order_item_id, prod_id, name, isattr, action_cd,
     status)
     AS (SELECT a.created,
                a.row_id,
                a.par_order_item_id,
                a.prod_id,
                p.name      name,
                'N'         AS isAttr,
                a.action_cd,
                a.status_cd status
         FROM   s_order_item a,
                s_order b,
                s_prod_int p
         WHERE  p.row_id = a.prod_id
                AND a.order_id = b.row_id
                AND b.order_num = 'Your_Order_number'
         UNION ALL
         SELECT a.created,
                a.row_id,
                a.order_item_id AS par_order_item_id,
                ' '             AS prod_id,
                ( CASE
                    WHEN a.data_type_cd = 'Text' THEN a.attr_name
                                                      ||' : '
                                                      ||a.char_val
                    WHEN a.data_type_cd = 'Date' THEN a.attr_name
                                                      ||' : '
                                                      ||a.date_val
                    WHEN a.data_type_cd = 'Integer' THEN a.attr_name
                                                         ||' : '
                                                         ||a.num_val
                  END )         name,
                'Y'             AS isAttr,
                a.action_cd,
                ' '             AS status
         FROM   s_order_item_xa a,
                q1
         WHERE  a.order_item_id = q1.row_id)
SELECT a.row_id,
       a.prod_id prod,
       ( CASE
           WHEN isattr = 'Y' THEN Lpad('|_', ( LEVEL - 1 ) * 4)
                                  ||a.name
           ELSE Lpad('|-', ( LEVEL - 1 ) * 3)
                ||a.name
         END )   AS "Item/Attr",
       a.action_cd,
       a.status
FROM   q1 a
START WITH a.par_order_item_id IS NULL
CONNECT BY PRIOR a.row_id = a.par_order_item_id
ORDER  SIBLINGS BY a.created DESC; 

Breakdown of the Query

• S_ORDER_ITEM: This table stores information about all the items related to a particular order. The key columns are:
  • ROW_ID: Unique identifier for each order item.
  • PROD_ID: The product Id associated with the order item.
  • NAME: Name of the product.
  • PAR_ORDER_ITEM_ID: The parent order item ID, which links items in a hierarchical manner (e.g., parent-child relationship).
• START WITH: We start the hierarchy with order items that do not have a parent item (i.e., where PAR_ORDER_ITEM_ID IS NULL).
• CONNECT BY PRIOR: This defines the parent-child relationship, where the parent item is identified by the ROW_ID and the child items are linked by PAR_ORDER_ITEM_ID.
• LEVEL: This pseudo-column returns the level of hierarchy for each item in the result set. Level 1 would be the parent items, while Level 2, 3, and so on would indicate child and sub-child items.
• ORDER SIBLINGS BY: This ensures that items at the same level in the hierarchy are ordered by the Created date or any other parameter.

---

Use Case Example

Let’s say you have an order with a bundle product (e.g., a mobile phone package) and several accessories (e.g., a case, charger) added as child items. The SQL query would show the bundle as the parent product at level 1, and the accessories as child items at level 2.

This hierarchical structure is immensely helpful for:

• Debugging issues related to product bundling.
• Reporting and tracking order composition.
• Building custom business logic that needs to act differently on parent versus child order items.

---

By using the above SQL query, you can easily visualize the parent-child relationship between order items in Siebel Order Management. This hierarchical structure is essential for anyone involved in managing or analyzing complex orders. Whether you’re troubleshooting order issues or performing data analysis, understanding the hierarchy will give you a better grasp of how items are organized in a sales order.

---

Would you like to see more SQL queries or Siebel-specific tips? Let me know in the comments!