SQL-029: Retrieve available security questions¶

Retrieves all non-expired security question codes from EXT_USER_SEC_QUESTION_CODE with globalized text, for presentation during security question selection. Expired questions are excluded using NVL(EXPIRATION_DATE, sysdate+1) > sysdate.

Classification¶

Field	Value
SQL ID	SQL-029
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUserSecurityQuestion`
Criteria property	Local SQL
Lifecycle method	`GetAllSecurityQuestions`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:Culture`	Input	String	5	Culture code for globalized question text.

Tables¶

Table	Access	Columns used
EXT_USER_SEC_QUESTION_CODE	SELECT	`CODE`, `QUESTION_TEXT`, `EXPIRATION_DATE`
EXT_USER_SEC_QUESTION_GCODE	LEFT JOIN	`CODE`, `CULTURE_CODE`, `QUESTION_TEXT`

Sample SQL¶

SELECT
    null AS UserSecurityQuestionID,
    null AS UserID,
    c.CODE AS SecurityQuestionCode,
    CASE WHEN gc.question_text IS NULL
         THEN cast(c.question_text AS nvarchar2(200))
         ELSE gc.question_text
    END AS question,
    null AS Answer,
    null AS CreateUser,
    null AS CreateDate,
    null AS UpdateUser,
    null AS UpdateDate,
    null AS UpdateNum,
    c.expiration_date AS QuestionExpirationDate
FROM ext_user_sec_question_code C
LEFT JOIN ext_user_sec_question_gcode gC
    ON (c.code = gc.code AND UPPER(gc.culture_code) = :Culture)
WHERE nvl(C.EXPIRATION_DATE, sysdate + 1) > sysdate

Operations and processes¶

Security question management

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Business\SAIF.Business.External.Users\clsUserSecurityQuestion.vb