What's New in CEDE 8.0 Databases

AIR has introduced the following changes.

Some of the changes apply to new features and enhancements in Touchstone Re.

New Tables

AIR has added the following database tables in Touchstone 8.0:

Table	Details
[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 MinMaxLogic types used in layer and location 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 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 for Touchstone 8.0:

Table

Details

[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 Caribbean (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 Caribbean (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].[tCLFIndustryLOB]

Added industry line of business for New Zealand.

[AIRReference].[dbo].[tCLFINFVersion]

Added INFVersion for the catalog type code: HIST

[AIRReference].[dbo].[tCLFAreaLOBIndex]

Added the following:

CLFAreaLOBIndex	ModelCode	OldModelVersion	NewModelVersion
1004	15	2.2.0	2.2.2
1004	15	2.2.1	2.2.2
1005	27	19.0.0	19.0.1
1005	27	19.1.0	19.1.1
1005	27	20.0.0	20.0.1
1005	27	20.1.0	20.1.1
1005	27	21.0.0	21.0.1
1005	27	21.1.0	21.1.1

[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].[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].[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].[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].[tOutputTypeTableXref]

Added the following OutputTableType codes to store AIR's Next Generation Detailed Loss Module-specific financial terms: FNGM and RNGM.

[AIRReference].[dbo].[tOutputTable]

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 columns

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