SQL-006 Non-employer account lookup

Retrieve a non-employer account by account number.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:AccountNumber	Input	Decimal	N/A	Non-employer account number

Tables

Table	Access	Columns used
NON_EMPR_ACCT	SELECT	ACCT_NUM, ACCT_NAM, UPDT_SEQ_NUM, UPDR_ID, UPDT_TS, ADDR_LINE1, ADDR_LINE2, CITY_NAM, STATE_CD, ZIP_CD, ZIP_CD_EXT, `PHONE_NUM

Sample SQL

SELECT
  NON_EMPR_ACCT.ACCT_NUM AS AccountNumber,
  NON_EMPR_ACCT.ACCT_NAM AS Name,
  NON_EMPR_ACCT.UPDT_SEQ_NUM AS UpdateSeqNum,
  NON_EMPR_ACCT.UPDR_ID AS UpdateUser,
  NON_EMPR_ACCT.UPDT_TS AS UpdateDate,
  NON_EMPR_ACCT.ADDR_LINE1 AS AddressLine1,
  NON_EMPR_ACCT.ADDR_LINE2 AS AddressLine2,
  NON_EMPR_ACCT.CITY_NAM AS CityName,
  NON_EMPR_ACCT.STATE_CD AS StateCode,
  NON_EMPR_ACCT.ZIP_CD AS ZipCode,
  NON_EMPR_ACCT.ZIP_CD_EXT AS ZipCodeExtension,
  NON_EMPR_ACCT.PHONE_NUM AS PhoneNumber
FROM NON_EMPR_ACCT
WHERE NON_EMPR_ACCT.ACCT_NUM = :AccountNumber

Business rules

• BR-VP-002: Non-employer account number must not be zero
• BR-VP-027: Create/Insert/Update/Delete SQL are invalid

Sources

• SAIF.Business.View.Policy/clsNonEmployerAccount.vb