SQL-007 Select all task category codes for a source system

Retrieves every TASK_CATEGORY_CODE row belonging to a given source system, ordered by description, for populating a category-code collection.

Classification

Field	Value
SQL ID	SQL-007
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsTaskCategoryCodeCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:TaskSourceCode	Input	String	5	Source system whose categories are loaded

Tables

Table	Access	Columns used
TASK_CATEGORY_CODE	SELECT	CODE, DEFAULT_ACTION_CODE, DEFAULT_PRIORITY_CODE, CODE_DESCR, NOTEPAD_REQUIRED_FLAG, TASK_SOURCE_CODE

Sample SQL

SELECT
  TASK_CATEGORY_CODE.CODE AS Code,
  NVL(TASK_CATEGORY_CODE.DEFAULT_ACTION_CODE, ' ') AS DefaultActionCode,
  NVL(TASK_CATEGORY_CODE.DEFAULT_PRIORITY_CODE, ' ') AS DefaultPriorityCode,
  TASK_CATEGORY_CODE.CODE_DESCR AS Description,
  TASK_CATEGORY_CODE.NOTEPAD_REQUIRED_FLAG AS NotepadRequiredFlag,
  TASK_CATEGORY_CODE.TASK_SOURCE_CODE AS TaskSourceCode
FROM
  TASK_CATEGORY_CODE
WHERE
  TASK_CATEGORY_CODE.TASK_SOURCE_CODE = :TaskSourceCode
ORDER BY
  CODE_DESCR

Operations and processes

• Load a code table

Business rules

• BR-023: Code tables are read-only reference data
• BR-024: A category-code collection is valid only if at least one row was found

Sources

• SAIF.Business.Tasks/clsTaskCategoryCodeCollection.vb