LOV Extract SQL Query

For Pulling out LOV Extract you can use below SQL Query:

SELECT 

10000+ROWNUM ROW_ID,

'FOR_IMPORT' IF_ROW_STAT,

CASE WHEN LOV.TYPE='LOV_TYPE' THEN 1000+ TRUNC((ROWNUM-1)/1000)

    WHEN PAR.TYPE IS NULL THEN 1010+ TRUNC((ROWNUM-1)/1000)

    ELSE 1020+ TRUNC((ROWNUM-1)/1000)

END IF_ROW_BATCH_NUM,

LOV.TYPE,

LOV.VAL,

LOV.NAME,

LOV.ACTIVE_FLG,

LOV.DFLT_LIC_FLG,

LOV.LANG_ID,

LOV.MLTORG_DISALW_FLG,

LOV.MULTI_LINGUAL_FLG,

LOV.REQD_LIC_FLG,

LOV.RPLCTN_LVL_CD,

LOV.SUB_TYPE,

BU.NAME,

LOV.HIGH,

LOV.LOW,

LOV.TARGET_HIGH,

LOV.TARGET_LOW,

LOV.ORDER_BY,

LOV.DESC_TEXT,

LOV.TRANSLATE_FLG,

BU.NAME,

PAR.TYPE,

PAR.VAL,

BU.NAME,

PAR.SUB_TYPE,

PAR.LANG_ID

FROM SIEBEL.S_LST_OF_VAL LOV

LEFT OUTER JOIN SIEBEL.S_BU BU ON LOV.BU_ID=BU.ROW_ID

LEFT OUTER JOIN SIEBEL.S_LST_OF_VAL PAR ON LOV.PAR_ROW_ID=PAR.ROW_ID

--WHERE LOV.TYPE='PROVIDE LOV TYPE';-- In case you need some specific LOV Extract then remove the comment -- and provide LOV TYPE

WHERE LOV.LAST_UPD>SYSDATE-30;

How to find the latest Record in Siebel?

SQL Query to Get Latest Record

There are times when we require to find latest record in siebel through SQL.
Following are the SQL which fetches the latest records of S_SRV_REQ table.

SELECT * FROM SIEBEL.S_SRV_REQ 
WHERE CREATED  = (SELECT MAX(CREATED) FROM SIEBEL.S_SRV_REQ);
OR
SELECT * FROM SIEBEL.S_SRV_REQ
WHERE (CREATED>sysdate); 

Note : CREATED Column is the created date of the record.

SQL Query to List all users by "Responsibility Name"

With the below SQL query we can get list of users by responsibility name:

SELECT U.LOGIN
FROM SIEBEL.S_USER U, SIEBEL.S_PER_RESP PER, SIEBEL.S_RESP R
WHERE U.ROW_ID = PER.PER_ID
AND R.ROW_ID = PER.RESP_ID
AND R.NAME LIKE 'Responsibility Name';