What's New in CEDE 8.0

The following database changes have been introduced in AIR Exposure, AIRReference, and AIRReinsurance databases:

These changes support several new features and enhancements in Touchstone. For more information, see Touchstone Release Notes and the Touchstone online help on AIR's Client Portal.

To facilitate integrations between Touchstone and Touchstone Re, some databases include data for both applications. However, only databases and tables that are relevant to Touchstone are described in this reference. For more information about Touchstone Re-specific databases and tables, see Touchstone Re Database Reference on AIR's Developer Zone.

New Tables

AIR has added the following database tables:

Table	Details
[tUpgradeLog]	Stores information about upgrade scripts. Added for several databases.
[AIRReference].[dbo].[tAggregateDeductibleType]	Stores codes that represent types of aggregate deductibles. During a loss analysis calculation, the system uses the information in the table to fetch the codes of aggregate deductibles applied to a contract, layer or treaty.
[AIRReference].[dbo].[tAggregateLimitType]	Stores codes that represent types of aggregate limits. During a loss analysis calculation, the system uses the information in the table to fetch the codes of aggregate limits applied to a contract, layer or treaty.
[AIRReference].[dbo].[tMinMaxDeductibleType]	Stores codes that represent minimum maximum deductible types for location terms. During a loss analysis calculation, the system uses the information in the table to fetch the codes of minimum maximum deductible policy logic corresponding to each location terms.
[AIRReference].[dbo].[tInjuryType]	Stores codes that represent types of injuries. During the import of Worker's Compensation exposure data in Touchstone, the system uses the information in the table to map injury code for each employee in the location file.
[AIRReference].[dbo].[tMinMaxLogic]	Stores codes for Stores codes for minimum maximum policy logic types used in layer and sublimit terms.
[AIRReference].[dbo].[tSEQUELImpactSubPerilGroupingXRef]	Stores codes, descriptions, and groupings of the peril codes generated in Touchstone. Sequel Impact is a powerful exposure management tool offered by Sequel Business Solutions. AIR Worldwide and Sequel Business Solutions are both Verisk businesses.Sequel Impact users have the flexibility to trigger the transfer manually or with automated scheduling and can do this for individual accounts or entire portfolios. Touchstone provides a broad set of initial setup options to provide users with flexibility in how the data is brought into the application, including, but not limited to, defining where the data is stored and which exposure views it flows through into.
[AIRReference].[dbo].[tTimeofShift]	Stores codes that represent types of shift definitions. Touchstone supports shift definitions for the United States and Japan.
[AIRReference].[dbo].[tVulnerabilitySetFileType]	Stores codes that represent types of vulnerability set files in Touchstone.
[AIRReference].[dbo].[tWetFloodProofing]	Stores codes that represent types of protection systems for flooding in a location.

Modified Tables

AIR has modified the following database tables:

Table

Details

[tDBHistory]

Added columns: IsIgnorable, IsRolledBack, RolledBackScript.

[AIRExposure].[dbo].[tLocFeature]

Added column: WetFloodProofingCode.

[AIRExposure].[dbo].[tLayer]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: Limit3, Limit4, Deductible3, Deductible4, AggregateLimitTypeCode, AggregateLimit, AggregateAttachmentPoint, AggregateDeductibleTypeCode, AggregateDeductible.

[AIRExposure].[dbo].[tLayerCondition]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: Deductible3, Deductible4, AggregateLimitTypeCode, AggregateLimit, AggregateAttachmentPoint, AggregateDeductibleTypeCode, AggregateDeductible, LayerConditionName, LayerConditionTier.

[AIRExposure].[dbo].[tReinsAppliesToExp]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: RiskAggregateRetention, RiskAggregateLimit, RiskLimitReinstatement.

[AIRExposure].[dbo].[tLocation]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: DamageFunctionID, GeocoderAccountId.

[AIRExposure].[dbo].[tLocTerm]

Added the following columns to store AIR's Next Generation Detailed Loss Module-specific financial terms: AggregateLimitTypeCode, AggregateLimit, MinMaxDeductibleTypeCode, MinimumDeductible, MaximumDeductible, AggregateDeductibleTypeCode, AggregateDeductible.

[AIRReference].[dbo].[tAIRConstOccRegionConstXref]

Added construction codes for the existing Australia (AUS) region: 204-206, 211-212, 231-238, 241-246, 251-260, 270-276, 2010-2013, 2015-2016, 2021-2022, 2031, 2131-2132, 2141-2142, 2150-2152, 2210, 2211, 2221, 2231-2233, 2241-2243, 2251-2253, 2261-2263, 2270-2276, 2281-2286.

Added construction codes for the existing Central African Republic (CAR) region:138-141, 157, 159, 259, 260, 265- 267, 270- 276, 2010-2013, 2015-2016, 2021, 2022, 2031, 2131, 2132, 2141-2142, 2150- 2152, 2210-2211, 2221, 2231-2233, 2241-2243, 2251-2253, 2261-2263, 2270-2276, 2281-2286

[AIRReference].[dbo].[tAIRConstOccRegionCountryXref]

Added country codes for the existing CAR region.

[AIRReference].[dbo].[tActivityType]

New activity type codes:

Activity Type Code	Description
CMP_COPY	Copy TSRe Company
CONVRT_ULF	Convert ULF File
EXPMOD	Exposure Modification
IDTEVEXPRT	Geospatial Terrorism Ring to IDT Event Set Export
LOSS_VUL	Vulnerability Modified Detailed Loss
VULMODDEL	Vulnerability Modifications Delete
VULMODEXP	Vulnerability Modifications Export
VULMODIMP	Vulnerability Modifications Import

[AIRReference].[dbo].[tActivityTypeActivityStageXref]

New stages to support new activity types.

[AIRReference[.[dbo].[tAIRConstruction]

Added new construction code:

AIRConstructionCode	ConstructionCategory	AIRConstruction
141	Concrete	Reinforced Concrete Frame with 2nd story Wood Frame or URM Addition

[AIRReference[.[dbo].[tAIRConstOccRegionOccXref]

Added occupancy codes for the existing CAR region: 381-384.

Added occupancy codes for the existing AUS region: 381-384.

[AIRReference].[dbo].[tCatalogModelXref]

New mappings between model codes and catalog model codes:

CatalogModelCode	ModelCode
8	8
15	15
27	25
51	51

[AIRReference].[dbo].[tCatalogType]

Removed a catalog type code: EXT.

[AIRReference].[dbo].[tConvertCL2Country]

Added the following rows:

OldAreaLevel1	OldAreaLevel1Name	CountryCode	CountryName	Description	OldAreaLevel1BriefName
283	Bonaire and Sint Eustatius and Saba	BQ	Bonaire and Sint Eustatius and Saba	Caribbean Netherlands	BQ
284	Curacao	CW	Curacao		CW

[AIRReference].[dbo].[tConvertCL2Currency]

Added the following row:

OldCurrency	ConversionRate	CurrencyCode	OldCurrencyCode	Currency
Cuban Convertible Peso	1	CUC	CUC	Cuban Convertible Peso

[AIRReference].[dbo].[tConvertCTCurrency]

New currencies:

48 = UF
190 = CUC

[AIRReference].[dbo].[tCountry]

Added the following countries:

CountryCode	Description
BQ	Bonaire and Sint Eustatius and Saba
CW	Curacao
INC	India Crop

[AIRReference].[dbo].[tCountryCurrencyXref]

Added new currency codes for countries:

CountryCode	Currency
BQ	USD
CW	ANG

[AIRReference].[dbo].[tCountryIndustryLOBXref]

Removed the existing industry line of business code for New Zealand.

Added the following industry line of business codes:

IndustryLineOfBusinessCode	CountryCode
2	BQ
2	CW
3	BQ
3	CW
4	AT
4	BE
4	BQ
4	CH
4	CW
4	DE
4	DK
4	FR
4	GR
4	IE
4	IL
4	IT
4	LU
4	NL
4	NO
4	PT
4	SE
5	BQ
5	CW
25	NZ

[AIRReference].[dbo].[tCountryPerilXref]

Added the following rows:

CountryCode	MainPerilSetCode	CombinedPerilSetCode
AG	4	8196
AI	4	8196
AN	4	8196
AW	4	8196
BL	4	8196
BQ	1	4097
BQ	4	8196
CU	4	8196
CW	1	4097
CW	4	8196
DM	4	8196
GD	4	8196
GP	4	8196
HT	4	8196
KN	4	8196
LC	4	8196
MQ	4	8196
MS	4	8196
TC	4	8196
VC	4	8196
VG	4	8196

[AIRReference].[dbo].[tCurrency]

Added the following:

CurrencyCode	Currency
CUC	Cuban Convertible Peso

[AIRReference].[dbo].[tEPCurveType]

Added the following rows:

EPCurveTypeCode	EPCurveType	Description
Model	Model	Model EP Curve
Peril	Peril	Peril EP Curve
Zone	Zone	Zone EP Curve
ZonePeril	ZonePeril	ZonePeril EP Curve

[AIRReference].[dbo].[tEPTargetType]

Added the following rows:

EPTarget Type Code	EPTargetType	Description
RITRT	Reinsurance Treaty	Reinsurance Treaty
RPRG	Reinsurance Program	Reinsurance Program

[AIRReference].[dbo].[tExportType]

Added the following row:

ExportTypeCode	ExportType	Description
IDTEVEXPRT	IDT Export	Geospatial Terrorism Ring to IDT Event Set Export

[AIRReference].[dbo].[tFinancialPerspective]

Added several rows.

[AIRReference].[dbo].[tHazardAttribute]

Added the following rows:

HazardAttributeCode	OutputTypeCode	HazardAttributeTypeCode	SubsetHazardAttributeTypeCode	HazardAttribute	HazardFieldName	HazardFieldType
227	HAZTC	VALUE	NULL	Florida Wind Mitigation – Exposure Area 2010	TCFWMExposureAreaCode2010	SMALLINT
228	HAZTC	VALUE	NULL	Florida Wind Mitigation – Exposure Area 2010	TCFWMWindSpeedRegionCode2010	SMALLINT
229	HAZTC	VALUE	NULL	Florida Wind Mitigation – Exposure Area 2010	TCFWMWindborneDebrisRegion2010	BIT
230	HAZTC	VALUE	NULL	Florida Wind Mitigation – Exposure Area 2010	TCFWMHighVelocityWindRegion2010	BIT

[AIRReference].[dbo].[tIndustryLineOfBusiness]

Added the following:

IndustryLineOfBusiness = 25
Residential Land

[AIRReference].[dbo].[tIntensityAttribute]

Added several rows.

[AIRReference].[dbo].[tMainPerilSubPerilXref]

Added several rows.

[AIRReference].[dbo].[tModel]

Removed ModelCode codes: 230-237

[AIRReference].[dbo].[tModelCountryXref]

Added several rows.

[AIRReference].[dbo].[tObjectType]

Added te following row:

ObjectTypeCode	ObjectType	Description
USERGEOMATCH_MAPPINGSET	User Geocode Match Mapping Set	User Geocode Match Mapping Set

[AIRReference].[dbo].[tReinsuranceUNICEDELOB]

Added several rows.

[AIRReference].[dbo].[tReinsuranceUNICEDEUpgradeMapping]

Added several rows.

[AIRReference].[dbo].[tRiskMeasure]

Added the following rows:

RiskMeasureCode	RiskMeasure	Description
ACC	TRV+(Emp * ShiftPercent * InjuryPayout)	Primary Property TRV + (WC Employees Count * Emp percent in the shift selected * Injury Payout as per Injury Type Selected)
WCP	Emp * ShiftPercent * InjuryPayout	WC Employees Count * Emp percent in the shift selected * Injury Payout as per Injury Type Selected

[AIRReference].[dbo].[tServiceEquipmentProtection]

ServiceEquipmentProtectionCode	ServiceEquipmentProtection	Description
3	Medium Protection for flood	Indicator of flood protection of service equipment (mechanical, electrical and plumbing services) located in the floodable parts of the building
4	High Protection for flood	Indicator of flood protection of service equipment (mechanical, electrical and plumbing services) located in the floodable parts of the building

[AIRReference].[dbo].[tReinsuranceUNICEDEUpgradeMapping]

Added column: IsAlternateGeo.

[AIRReference].[dbo].[tUNICEDE2CountryLOBXref]

Added the following rows:

UNICEDE2LineOfBusiness	CountryCode
6	BQ
6	CW
50	BQ
50	CW
51	BQ
51	CW
52	BQ
52	CW
53	BQ
53	CW
54	BQ
54	CW
55	BQ
55	CW

[AIRReference].[dbo].[tValidtationError]

Added the following rows:

ValidationErrorCode	ExposureTargetTypeCode	ExposureField	ValidationRule	ValidationErrorMessage	NameSpace	ValidationErrorTypeCode
20066	LOC	Geography Match Level Code	Geography Match Level Code must be a valid code	Geography Match Level Code must be a valid code	AIR.EntityDataModel.Exposure	BUS
20067	LOC	Enhanced GeoMatch Level Code	Enhanced GeoMatch Level Code must be a valid code	Enhanced GeoMatch Level Code must be a valid code	AIR.EntityDataModel.Exposure	BUS
20068	LOC	Geocoder Code	Geocoder Code must be a valid code	Geocoder Code must be a valid code	AIR.EntityDataModel.Exposure	BUS
20069	LOC	Geocoder AccountId	Geocoder AccountId cannot be greater than 50 characters long	Geocoder AccountId cannot be greater than 50 characters long	AIR.EntityDataModel.Exposure	BUS

[AIRReference].[dbo].[tGeocoder]

Added the following column:

GeocoderCode	Geocoder	Description
SYNCSORT	Syncsort	Geocoded through Syncsort, using international street-level geocoding data.

[AIRReference].[dbo].[tOutputTable]

Added the following OutputTable values to support the integration of AIR's Next Generation Detailed Loss Module in Touchstone 8.0:

OutputTableSID	OutputTable	OutputTableTypeCode	OutputDetailedAALCode	Description
68	LOSS_ByLocationByFacultative	FNGM	NULL	Added part of NGM Facultative by location
69	LOSS_ByLayerByFacultative	FNGM	NULL	Added part of NGM Facultative by layer
70	LOSS_ByTreaty	RNGM	NULL	Added part of NGM Facultative by location
71	LOSS_ByLocationByTreaty	RNGM	NULL	Added part of NGM location by Treaty
73	LOSS_ByTreatyExposureAttribute	RNGM	NULL	Added part of NGM LOB by Treaty
74	LOSS_ByLayerByTreaty	RNGM	NULL	Added part of NGM Layer by Treaty
75	LOSS_ByContractByTreaty	RNGM	NULL	Added part of NGM Contract by Treaty
76	LOSS_ByTreatyExposureAttributeGeo	RNGM	NULL	Added part of NGM LOB by Treaty Exposure Attribute Geo

[AIRReinsurance].[dbo].[tReinsurance]

Added the following columns to support the integration of AIR's Next Generation Detailed Loss Module in Touchstone 8.0: RiskAggregateRetention, RiskAggregateLimit, RiskLimitReinstatement.

Datatype Changes

AIR has modified the following column:

Table	Column	Previous Datatype	Current Datatype
[AIRReference].[dbo].[tAIRConstruction]	AIRConstruction	VARCHAR(40)	VARCHAR(70)