SQL-029: US_POLICY_UNIT update

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

Classification

Field	Value
SQL ID	SQL-029
Operation type	Update
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicyUnit
Criteria property	UpdateSQL
Lifecycle method	BeforeUpdate

Parameters

Parameter	Direction	Type	Max length	Description
: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.
:PolicyUnitSeqNum	Input	Decimal/SmartDecimal	N/A	Policy unit primary key / parent foreign key.

Tables

Table	Access	Columns used
US_POLICY_UNIT	UPDATE	See sample SQL

Sample SQL

Update US_POLICY_UNIT

Set POLICY_NUM                  = :PolicyNumber,

    POLICY_PERIOD_EFF_DATE      = :PolicyPeriodEffectiveDate,

    UNIT_EFF_DATE               = :UnitEffectiveDate,

    UNIT_EXP_DATE               = :UnitExpirationDate,

    POLICY_PERIOD_EXP_DATE      = :PolicyPeriodExpirationDate,

    POLICY_CANCEL_FLAG          = :PolicyCancelFlag,

    NCCI_POLICY_NUM             = :NcciPolicyNumber,

    UNIT_VALUATION_NUM          = :UnitValuationNumber,

    LAST_REPORTED_VALUATION_NUM = :LastReportedValuationNumber,

    ACTIVE_UNIT_FLAG            = :ActiveUnitFlag,

    EXPOSURE_EDIT_FLAG          = :ExposureEditFlag,

    UNIT_CORRECTION_FLAG        = RTRIM(:UnitCorrectionFlag),

    EXPOSURE_CORRECTION_FLAG    = :ExposureCorrectionFlag,

    LOSS_CORRECTION_FLAG        = :LossCorrectionFlag,

    UPDATE_USER                 = :UpdateUser,

    UPDATE_DATE                 = :UpdateDate,

    UPDATE_NUM                  = :UpdateNumber,

    HISTORICAL_FLAG             = :HistoricalFlag,

    PREM_HIST_SET_ID            = :PremiumSetID,

    LOSS_EDIT_FLAG              = :LossEditFlag

Where US_POLICY_UNIT.US_POLICY_UNIT_SEQ_NUM = :PolicyUnitSeqNum

Operations and processes

• Policy unit CRUD

Business rules

• BR-US-001: Policy unit sequence number must be greater than zero
• BR-US-012: Activating a policy unit that has been previously reported must also set correction flags
• BR-US-016: Correction flags default to N
• BR-US-020: Database contention on update or delete is detected by checking RowsAffected <> 1

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

Sources

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