Some of the most commonly used SQL queries in Siebel to retrieve information from the database.
SQL Query to find partners in Siebel.
SELECT COUNT (*)
FROM siebel.s_org_ext
WHERE row_id IN
(SELECT a.row_id
FROM siebel.s_org_ext a,
siebel.s_org_ext_x ax,
siebel.s_postn p,
siebel.s_bu I
WHERE a.row_id = ax.par_row_id
AND a.pr_postn_id = p.row_id
AND a.bu_id = i.row_id
AND ax.attrib_35 = 'Delete'
AND i.name NOT LIKE 'Delete%')
AND prtnr_flg <> 'N';
SQL Query for pulling up active and expired assignment rules
SELECT a.created,
(SELECT login
FROM s_user
WHERE row_id = a.created_by), a.last_upd,
(SELECT login
FROM s_user
WHERE row_id = a.last_upd_by), a.NAME,
a.eff_start_dt,
a.eff_end_dt
FROM s_asgn_grp a
WHERE EXISTS
( SELECT *
FROM s_asgn_grp_obj
WHERE asgn_object_name = 'Service Request'
AND a.row_id = asgn_grp_id)
AND (eff_end_dt IS NULL
OR eff_end_dt > SYSDATE)
Query to find the division and position of the Login:
SELECT div.name AS Division,
div.loc AS LOCATION,
pos.name AS POSITION,
u.login AS Login
FROM s_org_ext div,
s_postn pos,
s_user u
WHERE pos.ou_id = div.row_id
AND pos.pr_emp_id = u.row_id
AND u.login ='BMARK
SQL Query for Assignment rules for a particular user
SELECT distinct(a.name)
FROM s_asgn_grp a,
S_ASGN_GRP_EMP b
WHERE b.asgn_grp_id = a.row_id
AND b.EMP_ID = '1-AN0VX'
SQL Query to Find Template of an Activity
SELECT name
FROM s_tmpl_planitem
WHERE row_id IN
(SELECT TMPL_PLANITEM_ID
FROM s_evt_act
WHERE todo_cd LIKE 'Pend%')
Query to find the dedicated users who are currently logged in
SELECT username,
schemaname,
osuser,
terminal,
program,
TYPE,
status
FROM sys.v_$session
WHERE username NOT LIKE 'SADMI%'
AND program = 'siebel.exe'
SQL Query for finding accounts belonging to a specific organization
SELECT row_id,
name
FROM siebel.s_org_ext
WHERE row_id IN
(SELECT a.row_id
FROM siebel.s_org_ext a,
siebel.s_org_ext_x ax,
siebel.s_postn p,
siebel.s_org_int I
WHERE a.row_id = ax.par_row_id
AND a.pr_postn_id = p.row_id
AND a.bu_id = i.row_id
AND ax.attrib_35 LIKE 'Disq%'
AND a.PR_POSTN_ID<>'0-5220'
AND i.name LIKE 'GE Capital%')
SQL Query to find when was a user added to a responsibility
SELECT login,
pr.*
FROM S_PER_RESP pr,
s_user u
WHERE resp_id = '1-N6DW8'
AND pr.per_id = u.ROW_ID
AND Login IN ('BMARK')
SQL Query to find mobile users last synchronization sessions
This query displays each active mobile user, the number of sessions since last extract, their extract date and last sync date.SELECT T7.NAME "Userid",
T6.FST_NAME,
t6.last_name,
ep.x_attrib_35 "Bus Comp",
ep.x_attrib_34 "Parent RM Div",
T7.APP_SERVER_NAME,
t2.Last_file_num "Total sessions",
decode(t2.last_file_num, 0, t2.last_upd, NULL) "Extracted",
decode(t2.last_file_num, 0, to_date(NULL, 'mm/dd/yyyy'), t2.last_upd) "Last sync"
FROM SIEBEL.S_DOCK_STATUS T2,
SIEBEL.S_CONTACT T6,
SIEBEL.S_NODE T7,
siebel.s_emp_per ep
WHERE t7.emp_id = t6.PAR_ROW_ID
AND T7.ROW_ID = T2.NODE_ID
AND T7.NODE_TYPE_CD = 'REMOTE'
AND T2.TYPE = 'SESSION'
AND T7.EFF_END_DT IS NULL
AND t2.local_flg = 'N'
AND t6.par_row_id = ep.par_row_id
ORDER BY T7.NAME
SQL Query to find Organization of an Opportunity
SELECT name
FROM s_bu
WHERE row_id =
(SELECT bu_id
FROM s_opty
WHERE row_id = '1-2BM7S8')
SQL Query to find Division of an Opportunity
SELECT name
FROM s_org_ext
WHERE row_id IN
(SELECT ou_id
FROM s_postn
WHERE row_id IN
(SELECT pr_postn_id
FROM S_opty
WHERE row_id = '1-7VC0PN'))
No comments :