SQL-004: Output-document stored-procedure call

This stored-procedure call delegates output-document persistence to the Oracle package that inserts the base document row, derives document status, and creates the policy-period link row.

Classification

Field	Value
SQL ID	SQL-004
Operation type	Create
Execution method	Stored procedure
Database	Oracle
Schema	WCIS_DBA
Command type	StoredProcedure
Business object	clsOutputDocumentInserter
Criteria property	N/A
Lifecycle method	InsertOutputDocument / InsertAgencyDeliveredOutputDocument

Parameters

Parameter	Direction	Type	Max length	Description
:in_Policy_Num	Input	NUMBER	—	Policy number.
:in_Policy_Period_Eff_Date	Input	DATE	—	Policy-period effective date.
:in_Agency_Seq_Num	Input	NUMBER	—	Agency sequence number.
:in_Agent_num	Input	NUMBER	—	Agent number.
:in_Output_Document_Type_code	Input	VARCHAR2	value length	Output-document type code.
:in_Image_Seq_num	Input	NUMBER	—	Imaging sequence number.
:in_Hold_Flag	Input	VARCHAR2	1	Hold indicator.
:in_agency_delivery_flag	Input	VARCHAR2	1	Agency-delivery flag, bound only by the agency-delivered overload.
:in_Create_User	Input	VARCHAR2	value length	Create-user audit value.
:in_Description	Input	VARCHAR2	value length	Document description.
:in_source_key_text	Input	VARCHAR2	value length	Source-key text.
:in_release_date	Input	DATE	—	Optional explicit release date.

Tables

Table	Access	Columns used
OUTPUT_DOCUMENT	INSERT	OUTPUT_DOCUMENT_SEQ_NUM, OUTPUT_DOCUMENT_TYPE_CODE, DOCUMENT_CREATE_DATE, IMAGE_SEQ_NUM, ENDORSE_SEQ_NUM, DESCRIPTION
OUTPUT_DOCUMENT_TYPE_CODE	SELECT	CODE, AGENCY_VIEW_FLAG
POLICY_OUTPUT_DOCUMENT	INSERT	POLICY_NUM, POLICY_PERIOD_EFF_DATE, OUTPUT_DOCUMENT_SEQ_NUM, AGENCY_SEQ_NUM, AGENT_NUM, DOCUMENT_STATUS_CODE, HOLD_FLAG, RELEASE_DATE, AGENCY_DELIVERY_FLAG

Sample SQL

CALL pk73343_output_document.insert_policy_output_document(
  :in_Policy_Num,
  :in_Policy_Period_Eff_Date,
  :in_Agency_Seq_Num,
  :in_Agent_num,
  :in_Output_Document_Type_code,
  :in_Image_Seq_num,
  :in_Hold_Flag,
  :in_agency_delivery_flag,
  :in_Create_User,
  :in_Description,
  :in_source_key_text,
  :in_release_date)
-- Procedure body resides in Oracle database

Operations and processes

• Insert a policy output document

Business rules

• BR-005: Agency-delivered output documents add an agency-delivery flag
• 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
• BR-028: Zero agency and agent numbers are normalized to NULL
• BR-029: Release dates are set only for released policy-output rows
• BR-030: The standard output-document overload defaults to non-agency delivery
• BR-031: The agency-delivered overload passes the delivery flag through unchanged
• BR-032: The output-document procedure raises a standard SAIF error on exception

Sources

• Business\SAIF.Business.Policy.Correspondence\clsOutputDocumentInserter.vb