SQL-008: Task filtered collection select

Builds the collection-retrieval SELECT for task searches.

Classification

Field	Value
SQL ID	SQL-008
Operation type	Read
Execution method	StringBuilder-assembled
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsTaskCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:TaskSource	Input	String	5	Single source filter.
:SOURCE1..:SOURCEN	Input	String	5	Source-list filters.
:CAT1..:CATN	Input	String	5	Category filters.
:PriorityCode	Input	String	5	Priority filter.
:BeginDate	Input	Date	N/A	Inclusive lower date.
:EndDate	Input	Date	N/A	Inclusive upper date.
:MailID	Input	String	30	Assignee filter.
:ClaimID	Input	Decimal	N/A	Single claim filter.
:CLM1..:CLMN	Input	Decimal	N/A	Claim-list filters.
:PolicyNum	Input	Decimal	N/A	Single policy filter.
:POL1..:POLN	Input	Decimal	N/A	Policy-list filters.
:ACT1..:ACTN	Input	String	5	Action-code filters.

Tables

Table	Access	Columns used
TASK	SELECT	CLAIM_ID, CONTEXT_*, CREATE_DATE, CREATE_USER, DELETE_FLAG, IMAGE_SEQ_NUM, NOTEPAD_FLAG, POLICY_NUM, TASK_USER, TASK_ACTION_CODE, TASK_CATEGORY_CODE, TASK_DATE, TASK_SECURITY, TASK_PRIORITY_CODE, TASK_SEQ_NUM, TASK_SOURCE_CODE, TASK_TEXT, UPDATE_DATE, UPDATE_SEQ_NUM, UPDATE_USER

Sample SQL

SELECT TASK.CLAIM_ID AS ClaimID
     , TASK.CONTEXT_CATEGORY AS ContextCategory
     , TASK.CONTEXT_CODE AS ContextCode
     , TASK.CONTEXT_DATE AS ContextDate
     , TASK.CONTEXT_KEY AS ContextKey
     , TASK.CONTEXT_NUM AS ContextNum
     , TASK.CONTEXT_TEXT AS ContextText
     , TASK.CREATE_DATE AS CreateDate
     , TASK.CREATE_USER AS CreateUser
     , TASK.DELETE_FLAG AS DeleteFlag
     , TASK.IMAGE_SEQ_NUM AS ImageSeqNum
     , TASK.NOTEPAD_FLAG AS NotepadFlag
     , TASK.POLICY_NUM AS PolicyNum
     , TASK.TASK_USER AS MailID
     , TASK.TASK_ACTION_CODE AS TaskActionCode
     , TASK.TASK_CATEGORY_CODE AS TaskCategoryCode
     , TASK.TASK_DATE AS TaskDate
     , TASK.TASK_SECURITY AS TaskSecurity
     , TASK.TASK_PRIORITY_CODE AS TaskPriorityCode
     , TASK.TASK_SEQ_NUM AS TaskSeqNum
     , TASK.TASK_SOURCE_CODE AS TaskSourceCode
     , TASK.TASK_TEXT AS TaskText
     , TASK.UPDATE_DATE AS UpdateDate
     , TASK.UPDATE_SEQ_NUM AS UpdateSeqNum
     , TASK.UPDATE_USER AS UpdateUser
FROM TASK

Conditional append logic

m_SQL.Append(" AND DELETE_FLAG = ").Append(clsConstants.N).Append(" ")
m_SQL.Append(" AND TASK_TEXT LIKE %").Append(TaskText).Append("% ")
m_SQL.Append(" AND TASK_SOURCE_CODE = :TaskSource")
m_SQL.Append(" AND TASK_SOURCE_CODE IN (")
m_SQL.Append(" AND TASK_CATEGORY_CODE = :CAT1")
m_SQL.Append(" AND TASK_CATEGORY_CODE IN (")
m_SQL.Append(" AND TASK_PRIORITY_CODE = :PriorityCode")
m_SQL.Append(" AND TASK_PRIORITY_CODE IN (")
m_SQL.Append(" AND trunc(TASK_DATE) >= :BeginDate")
m_SQL.Append(" AND trunc(TASK_DATE) <= :EndDate")
m_SQL.Append(" AND TASK_USER = :MailID")
m_SQL.Append(" AND CLAIM_ID = :ClaimID")
m_SQL.Append(" AND CLAIM_ID IN (")
m_SQL.Append(" AND POLICY_NUM = :PolicyNum")
m_SQL.Append(" AND POLICY_NUM IN (")
m_SQL.Append(" AND TASK_ACTION_CODE = :ACT1 ")
m_SQL.Append(" AND TASK_ACTION_CODE IN (")
m_SQL.Append(" AND CONTEXT_CODE = ").Append(ContextCode).Append(" ")
m_SQL.Append(" AND CONTEXT_KEY = ").Append(ContextKey).Append(" ")
m_SQL.Append(" AND CONTEXT_CATEGORY = ").Append(ContextCategory).Append(" ")
m_SQL.Append(" AND CONTEXT_TEXT = ").Append(ContextTextValue).Append(" ")
m_SQL.Append(" AND CONTEXT_DATE = ").Append(ContextDate).Append(" ")
m_SQL.Append(" AND CONTEXT_NUM = ").Append(ContextNumericValue).Append(" ")

Operations and processes

• Retrieve task collection

Note: Statements marked with * are inferred from code behavior rather than explicit documentation or comments.

Sources

• Business\SAIF.Business.Tasks\clsTaskCollection.vb