SAIF.Business.Policy.Correspondence database tables

This page catalogs database tables accessed by this library.

Table catalog

Table	Database	Schema/Link	CRUD	Purpose	Primary business object
ENDORSEMENT	Oracle	[unqualified]**	C/R/U	Primary persistence table for correspondence and endorsement rows.	clsCorrespondence
ENDORSE_CODE	Oracle	[unqualified]**	R	Code table for descriptions, classification, and print metadata.	clsCorrespondenceCode
OUTPUT_LOG	Oracle	[unqualified]**	R	Supports image-sequence lookup and the legacy output-log path.*	clsCorrespondence
POLICY_PERIOD	Oracle	[unqualified]**	R	Source-system metadata for policy-period filtering.	clsEndorsementCollection
POLICY_CORRESPOND_DATA	Oracle	[unqualified]**	R	Stores correspondence detail-element values.	clsCorrespondenceDetail
CORRESPOND_DATA_ELEMENT	Oracle	[unqualified]**	R	Provides code-specific detail-element descriptions.	clsCorrespondenceDetail
OUTPUT_DOCUMENT	Oracle	WCIS_DBA	C	Procedure-managed output-document records.	clsOutputDocumentInserter
POLICY_OUTPUT_DOCUMENT	Oracle	WCIS_DBA	C	Policy-period link rows for output documents.	clsOutputDocumentInserter
OUTPUT_DOCUMENT_TYPE_CODE	Oracle	WCIS_DBA	R	Output-document type lookup used for status derivation.	clsOutputDocumentInserter

ENDORSEMENT

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Primary persistence table for correspondence and endorsement business objects.
Access pattern	direct SQL
CRUD operations	Create, Read, Update
Key columns used	POLICY_NUM, POLICY_PERIOD_EFF_DATE, ENDORSE_SEQ_NUM, EFFECTIVE_DATE, EXPIRATION_DATE, ENDORSE_DATE, PRINTED_DATE, ENDORSE_CODE, HOLD_PRINT_FLAG, CORR_JOBID, UPDATE_DATE
Sequence	Endorsement_Seq
Related tables	ENDORSE_CODE, OUTPUT_LOG, POLICY_CORRESPOND_DATA, POLICY_PERIOD
Business objects	clsCorrespondence, clsCorrespondenceCollection, clsCorrespondenceDetail, clsCorrespondenceDetailCollection, clsEndorsement, clsEndorsementCollection

Operations referencing this table

• Create a new correspondence or endorsement
• Retrieve a correspondence item
• Retrieve endorsement collections for a policy or policy period
• Persist a correspondence or endorsement
• Load correspondence detail elements

Business rules referencing this table

• BR-001: Only endorsement-coded correspondence is returned
• BR-003: Policy-period membership depends on source-system rules
• BR-008: Correspondence retrieval switches between two key shapes
• BR-011: Collection save applies edits before framework persistence
• BR-012: Insert and update must affect exactly one row
• BR-013: Updates enforce optimistic concurrency by update date

SQL referencing this table

• SQL-002: Insert correspondence or endorsement row
• SQL-005: Retrieve correspondence by sequence number
• SQL-006: Retrieve correspondence by policy-period key
• SQL-010: Retrieve correspondence collection for a policy
• SQL-011: Retrieve one correspondence data element
• SQL-012: Retrieve all correspondence data elements
• SQL-013: Retrieve one endorsement by sequence number
• SQL-014: Retrieve endorsements for a policy
• SQL-015: Retrieve endorsements for a policy period
• SQL-016: Update correspondence or endorsement row

---

ENDORSE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Lookup table that classifies correspondence codes and supplies descriptions and print metadata.
Access pattern	direct SQL
CRUD operations	Read
Key columns used	CODE, CODE_DESCR, CORRESPOND_TYPE_CODE, NCCI_CODE, IMAGE_DOCUMENT_TYPE_CODE
Sequence	N/A
Related tables	ENDORSEMENT, CORRESPOND_DATA_ELEMENT
Business objects	clsCorrespondenceCode, clsCorrespondenceCodeCollection, clsEndorsement, clsEndorsementCollection

Operations referencing this table

• Retrieve a correspondence item
• Retrieve endorsement collections for a policy or policy period

Business rules referencing this table

• BR-001: Only endorsement-coded correspondence is returned
• BR-019: Correspondence codes must exist in ENDORSE_CODE
• BR-023: Correspondence descriptions resolve through the code collection cache

SQL referencing this table

• SQL-008: Retrieve one correspondence code
• SQL-009: Retrieve all correspondence codes
• SQL-013: Retrieve one endorsement by sequence number
• SQL-014: Retrieve endorsements for a policy
• SQL-015: Retrieve endorsements for a policy period

---

OUTPUT_LOG

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Supports image-sequence lookup for retrieved correspondence records and the legacy output-log insert path.
Access pattern	direct SQL / legacy stored procedure call*
CRUD operations	Read
Key columns used	POLICY_NUM, CORRESPOND_SEQ_NUM, IMAGE_SEQ_NUM
Sequence	N/A
Related tables	ENDORSEMENT
Business objects	clsCorrespondence

Operations referencing this table

• Retrieve a correspondence item
• Look up a correspondence image sequence number
• Insert an output-log entry

Business rules referencing this table

• BR-008: Correspondence retrieval switches between two key shapes

SQL referencing this table

• SQL-003: Legacy output-log stored-procedure call
• SQL-005: Retrieve correspondence by sequence number
• SQL-006: Retrieve correspondence by policy-period key
• SQL-007: Look up image sequence number
• SQL-010: Retrieve correspondence collection for a policy
• SQL-013: Retrieve one endorsement by sequence number
• SQL-014: Retrieve endorsements for a policy
• SQL-015: Retrieve endorsements for a policy period

---

POLICY_PERIOD

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Provides source-system metadata used by policy-period endorsement filtering.
Access pattern	direct SQL
CRUD operations	Read
Key columns used	POLICY_NUM, POLICY_PERIOD_EFF_DATE, SOURCE_SYSTEM_CODE
Sequence	N/A
Related tables	ENDORSEMENT
Business objects	clsEndorsementCollection

Operations referencing this table

• Retrieve endorsement collections for a policy or policy period

Business rules referencing this table

• BR-002: Policy-period filters bind only for period-scoped retrieval
• BR-003: Policy-period membership depends on source-system rules

SQL referencing this table

• SQL-015: Retrieve endorsements for a policy period

---

POLICY_CORRESPOND_DATA

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores correspondence detail elements keyed by correspondence sequence number.
Access pattern	direct SQL
CRUD operations	Read
Key columns used	CORRESPOND_SEQ_NUM, DATA_ELEMENT, DATA_VALUE, DATA_ORDER
Sequence	N/A
Related tables	ENDORSEMENT, CORRESPOND_DATA_ELEMENT
Business objects	clsCorrespondenceDetail, clsCorrespondenceDetailCollection

Operations referencing this table

• Load correspondence detail elements

Business rules referencing this table

• BR-017: Correspondence details are read-only through this library

SQL referencing this table

• SQL-011: Retrieve one correspondence data element
• SQL-012: Retrieve all correspondence data elements

---

CORRESPOND_DATA_ELEMENT

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Supplies human-readable descriptions for code-specific correspondence detail elements.
Access pattern	direct SQL
CRUD operations	Read
Key columns used	CORRESPOND_CODE, DATA_ELEMENT, DATA_ELEMENT_DESCR
Sequence	N/A
Related tables	ENDORSEMENT, POLICY_CORRESPOND_DATA, ENDORSE_CODE
Business objects	clsCorrespondenceDetail, clsCorrespondenceDetailCollection

Operations referencing this table

• Load correspondence detail elements

Business rules referencing this table

• BR-017: Correspondence details are read-only through this library

SQL referencing this table

• SQL-011: Retrieve one correspondence data element
• SQL-012: Retrieve all correspondence data elements

---

OUTPUT_DOCUMENT

Field	Value
Database	Oracle
Schema/Link	WCIS_DBA
Purpose	Procedure-managed output-document table populated by pk73343_output_document.
Access pattern	stored procedure
CRUD operations	Create
Key columns used	OUTPUT_DOCUMENT_SEQ_NUM, OUTPUT_DOCUMENT_TYPE_CODE, DOCUMENT_CREATE_DATE, IMAGE_SEQ_NUM, ENDORSE_SEQ_NUM, DESCRIPTION
Sequence	output_document_seq
Related tables	POLICY_OUTPUT_DOCUMENT, OUTPUT_DOCUMENT_TYPE_CODE, ENDORSEMENT
Business objects	clsOutputDocumentInserter

Operations referencing this table

• Insert a policy output document

Business rules referencing this table

• BR-024: Explicit release dates override the computed document date
• BR-025: Non-agency-viewable output documents release immediately
• BR-026: Held output documents require both agency visibility and hold flag
• BR-027: Agency-viewable non-held documents start pending

SQL referencing this table

• SQL-004: Output-document stored-procedure call

---

POLICY_OUTPUT_DOCUMENT

Field	Value
Database	Oracle
Schema/Link	WCIS_DBA
Purpose	Procedure-managed policy-period link table for output documents.
Access pattern	stored procedure
CRUD operations	Create
Key columns used	POLICY_NUM, POLICY_PERIOD_EFF_DATE, OUTPUT_DOCUMENT_SEQ_NUM, AGENCY_SEQ_NUM, AGENT_NUM, DOCUMENT_STATUS_CODE, HOLD_FLAG, RELEASE_DATE
Sequence	N/A
Related tables	OUTPUT_DOCUMENT
Business objects	clsOutputDocumentInserter

Operations referencing this table

• Insert a policy output document

Business rules referencing this table

• BR-028: Zero agency and agent numbers are normalized to NULL
• BR-029: Release dates are set only for released policy-output rows

SQL referencing this table

• SQL-004: Output-document stored-procedure call

---

OUTPUT_DOCUMENT_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	WCIS_DBA
Purpose	Procedure lookup table that determines whether an output document is agency-viewable.
Access pattern	stored procedure
CRUD operations	Read
Key columns used	CODE, AGENCY_VIEW_FLAG
Sequence	N/A
Related tables	OUTPUT_DOCUMENT
Business objects	clsOutputDocumentInserter

Operations referencing this table

• Insert a policy output document

Business rules referencing this table

• BR-025: Non-agency-viewable output documents release immediately
• BR-026: Held output documents require both agency visibility and hold flag
• BR-027: Agency-viewable non-held documents start pending

SQL referencing this table

• SQL-004: Output-document stored-procedure call

---

Table relationship diagram

The following diagram shows the relationships identified in this library's SQL.

erDiagram
    ENDORSE_CODE ||--o{ ENDORSEMENT : classifies
    ENDORSEMENT ||--o{ POLICY_CORRESPOND_DATA : contains
    ENDORSE_CODE ||--o{ CORRESPOND_DATA_ELEMENT : defines
    POLICY_CORRESPOND_DATA }o--|| CORRESPOND_DATA_ELEMENT : describes
    POLICY_PERIOD ||--o{ ENDORSEMENT : filters
    ENDORSEMENT ||--o{ OUTPUT_LOG : images
    OUTPUT_DOCUMENT_TYPE_CODE ||--o{ OUTPUT_DOCUMENT : types
    OUTPUT_DOCUMENT ||--o{ POLICY_OUTPUT_DOCUMENT : links

Related documentation

• Index
• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• SQL catalog
• Configuration
• API reference

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\clsCorrespondence.vb
• Business\SAIF.Business.Policy.Correspondence\clsCorrespondenceCode.vb
• Business\SAIF.Business.Policy.Correspondence\clsCorrespondenceCollection.vb
• Business\SAIF.Business.Policy.Correspondence\clsCorrespondenceDetail.vb
• Business\SAIF.Business.Policy.Correspondence\clsCorrespondenceDetailCollection.vb
• Business\SAIF.Business.Policy.Correspondence\clsEndorsement.vb
• Business\SAIF.Business.Policy.Correspondence\clsEndorsementCollection.vb
• Business\SAIF.Business.Policy.Correspondence\clsOutputDocumentInserter.vb