Retrieve Employee/User Positions and Divisions in Siebel, Including User’s Primary Position

In Siebel, positions and divisions play a key role in organizing users and defining their access to various parts of the application. When working with Siebel SQL queries, it’s often necessary to retrieve details about positions and divisions, particularly the primary position assigned to a specific user. This post walks you through a SQL query that provides a list of all positions and their associated divisions. Additionally, it will show the primary position of the user when their login ID is provided.

SELECT usr.login,
  pp.fst_name,
  pp.last_name,
  div.name DIV,
  post.name POSITION,
  CASE ppname.name
    WHEN post.name
    THEN 'Yes'
    ELSE 'No'
  END "Is Primary"
FROM s_user usr,
  s_postn post,
  s_org_ext div,
  s_party_per conpos,
  s_contact pp,
  s_postn ppname,
  s_party party
WHERE usr.row_id        = conpos.person_id
AND conpos.party_id     = party.row_id
AND party.PARTY_TYPE_CD ='Position'
AND usr.row_id          = pp.row_id
AND ppname.row_id       = pp.PR_HELD_POSTN_ID
AND post.row_id         = conpos.party_id
AND post.ou_id          = div.row_id
AND usr.login          IN ('YOUR LOGIN ID');

Imagine you want to check which positions a user holds and identify their primary position. This query will help in identifying those associations clearly.

Customization:

To execute this query for a specific user, replace 'YOUR LOGIN ID' with the actual login ID of the user.

This query is a simple yet effective way to retrieve a user’s position and division information, especially when dealing with large Siebel implementations where multiple positions may be assigned to users.