SQL-004: US_POLICY_UNIT insert

This SQL statement implements the US_POLICY_UNIT insert data-access path for clsPolicyUnit in SAIF.Business.UnitStat. It participates in the create lifecycle handled by BeforeInsert.

Classification

Field	Value
SQL ID	SQL-004
Operation type	Create
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicyUnit
Criteria property	InsertSQL
Lifecycle method	BeforeInsert

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyUnitSeqNum	Input	Decimal/SmartDecimal	N/A	Policy unit primary key / parent foreign key.
:PolicyNumber	Input	Decimal	N/A	SAIF policy number.
:PolicyPeriodEffectiveDate	Input	Date	N/A	Policy period effective date.
:UnitEffectiveDate	Input	Date	N/A	Unit effective date.
:UnitExpirationDate	Input	Date	N/A	Unit expiration date.
:PolicyPeriodExpirationDate	Input	Date	N/A	Policy period expiration or cancel date snapshot.
:PolicyCancelFlag	Input	String	1	Policy cancellation indicator.
:NcciPolicyNumber	Input	String	18	Derived NCCI-facing policy number.
:UnitValuationNumber	Input	SmartDecimal	N/A	Current unit valuation number.
:LastReportedValuationNumber	Input	SmartDecimal	N/A	Last valuation number already reported to NCCI.
:ActiveUnitFlag	Input	String	1	Active/inactive unit flag.
:ExposureEditFlag	Input	String	1	Exposure edit-required flag.
:UnitCorrectionFlag	Input	String	1	Header/unit correction indicator.
:ExposureCorrectionFlag	Input	String	1	Exposure correction indicator.
:LossCorrectionFlag	Input	String	1	Loss correction indicator.
:UpdateUser	Input	String/SmartString	30	User ID written to audit columns or used as a filter.
:UpdateDate	Input	Date/SmartDate	N/A	Audit timestamp written during insert or update.
:UpdateNumber	Input	Decimal	N/A	Optimistic concurrency/version counter.
:HistoricalFlag	Input	String	1	Historical retention flag.
:PremiumSetID	Input	SmartDecimal	N/A	Premium history set identifier.
:LossEditFlag	Input	String	1	Loss edit-required flag.

Tables

Table	Access	Columns used
US_POLICY_UNIT	CREATE	See sample SQL

Sample SQL

Insert into US_POLICY_UNIT (

    US_POLICY_UNIT_SEQ_NUM,

    POLICY_NUM,

    POLICY_PERIOD_EFF_DATE,

    UNIT_EFF_DATE,

    UNIT_EXP_DATE,

    POLICY_PERIOD_EXP_DATE,

    POLICY_CANCEL_FLAG,

    NCCI_POLICY_NUM,

    UNIT_VALUATION_NUM,

    LAST_REPORTED_VALUATION_NUM,

    ACTIVE_UNIT_FLAG,

    EXPOSURE_EDIT_FLAG,

    UNIT_CORRECTION_FLAG,

    EXPOSURE_CORRECTION_FLAG,

    LOSS_CORRECTION_FLAG,

    UPDATE_USER,

    UPDATE_DATE,

    UPDATE_NUM,

    HISTORICAL_FLAG,

    PREM_HIST_SET_ID,

    LOSS_EDIT_FLAG

) Values (

    :PolicyUnitSeqNum,

    :PolicyNumber,

    :PolicyPeriodEffectiveDate,

    :UnitEffectiveDate,

    :UnitExpirationDate,

    :PolicyPeriodExpirationDate,

    :PolicyCancelFlag,

    :NcciPolicyNumber,

    :UnitValuationNumber,

    RTRIM(:LastReportedValuationNumber),

    :ActiveUnitFlag,

    :ExposureEditFlag,

    :UnitCorrectionFlag,

    :ExposureCorrectionFlag,

    :LossCorrectionFlag,

    :UpdateUser,

    :UpdateDate,

    :UpdateNumber,

    :HistoricalFlag,

    :PremiumSetID,

    :LossEditFlag

)

Operations and processes

• Policy unit CRUD

Business rules

• BR-US-001: Policy unit sequence number must be greater than zero
• BR-US-014: Active unit flag defaults to Y when not set
• BR-US-015: LossEditFlag and ExposureEditFlag default to Y when not set
• BR-US-016: Correction flags default to N
• BR-US-017: HistoricalFlag defaults to N when not set
• BR-US-026: LossEditFlag default is Y

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Business\SAIF.Business.UnitStat\clsPolicyUnit.vb