SQL-008: Retrieve one correspondence code

This code-table lookup loads the metadata that validates correspondence codes and supplies descriptions, print behavior, and imaging metadata used elsewhere in the library.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:Code	Input	String (VARCHAR2)	5	ENDORSE_CODE primary key.

Tables

Table	Access	Columns used
ENDORSE_CODE	SELECT	CODE, CODE_DESCR, NCCI_CODE, PRINT_ENDORSE_FLAG, CORRESPOND_TYPE_CODE, IMAGE_DOCUMENT_TYPE_CODE

Sample SQL

SELECT
  ENDORSE_CODE.CODE AS Code,
  ENDORSE_CODE.CODE_DESCR AS Description,
  ENDORSE_CODE.NCCI_CODE AS NCCIEndorsementCode,
  ENDORSE_CODE.PRINT_ENDORSE_FLAG AS PrintCorrespondenceFlag,
  ENDORSE_CODE.PICS_REPORT_FLAG AS PICSReportFlag,
  ENDORSE_CODE.MANUAL_ADD_FLAG AS ManualAddFlag,
  ENDORSE_CODE.REVISION_DATE AS RevisionDate,
  ENDORSE_CODE.CORRESPOND_TYPE_CODE AS CorrespondenceTypeCode,
  ENDORSE_CODE.PROGRAM_ID AS ProgramID,
  ENDORSE_CODE.AUTO_ADD_FLAG AS AutoAddFlag,
  ENDORSE_CODE.PRINT_PRIORITY_NUM AS PrintPriorityNumber,
  ENDORSE_CODE.INFOPAGE_PRINT_FLAG AS InformationPagePrintFlag,
  ENDORSE_CODE.CANCEL_ENDORSE_CODE AS CancelEndorsementCode,
  ENDORSE_CODE.CANCELLATION_DOC_FLAG AS CancellationDocumentFlag,
  ENDORSE_CODE.SVC_SIGNATURE_FLAG AS ServiceCenterSignatureFlag,
  ENDORSE_CODE.SIGNATURE_TEAM_FUNC_CODE AS SignatureTeamFunctionCode,
  ENDORSE_CODE.DEFAULT_DUPLEX_FLAG AS DefaultDuplexFlag,
  ENDORSE_CODE.PRINT_BEFORE_INFO_PAGE_FLAG AS PrintBeforeInformationPageFlag,
  ENDORSE_CODE.RNW_AUTO_ENDORSE_FLAG AS RenewalAutoEndorsementFlag,
  ENDORSE_CODE.IMAGE_CATEGORY_CODE AS ImageCategoryCode,
  ENDORSE_CODE.IMAGE_DOCUMENT_TYPE_CODE AS ImageDocumentTypeCode,
  ENDORSE_CODE.IMAGE_DOCUMENT_DATE_RULE_CODE AS ImageDocumentDateRuleCode
FROM ENDORSE_CODE
WHERE ENDORSE_CODE.CODE = :Code

Operations and processes

• Retrieve a correspondence item

Business rules

• BR-019: Correspondence codes must exist in ENDORSE_CODE
• BR-023: Correspondence descriptions resolve through the code collection cache

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.Policy.Correspondence\clsCorrespondenceCode.vb