The Province shall submit the Data Sharing files to Canada by respecting the frequency and the format agreed upon in the Layout Guide described in Section 2.0.

1.0 Canada will inform the Province by February 1st, 2024 on changes to Section 2.0 for the Province consideration to be implemented in the next Operational Document. Changes can be implemented in the April 1st, 2024 Operational Document or otherwise agreed by parties, in case where the Province needs more time to implement such changes considered in Section 2.0.

  • 1.1 Layout Guide described in Section 2.0 is not necessarily final as the Province and Canada are still discussing and finalizing certain items, such as those concerning connecting the data from the Province’s old and new IT systems, reconciliation of the major measures, and resolving merging issues between files. Canada and the Province will continue discussions to finalize these items for the next Operational Document.
  • 1.2 In the case where Canada identifies differences between data submissions from the Province and Section 2.0 below, the province will not be subject to holdbacks on federal contributions. Canada will engage the Province as required using the established process for data services to confirm and validate the data.
  • 1.3 Additional details on file naming convention, processing rules, submission instructions and formatting instructions for Layout Guide in Section 2.0 can be found in the excel file titled Ontario File Layout.

2.0 Data Submission Layout Guide for AgriInsurance

AgriInsurance — Ontario Layouts Guide

Province abbreviation: ON

File transfer details

Software Platform
  • Managed Secure File Transfer (MSFT)
  • All file confirmation and resubmission requests will be communicated through this platform.
  • Province must notify AAFC if there is a change in the account(s) used for the file transmission.
AAFC account(s)
  • aafc.actuaries-actuaires.aac@canada.ca
  • AAFC must notify the province if there is a change in the account(s) used for the file transmission.

Transfer instructions

  • The file set must be zipped into a single file per transmission.
  • The zip file name must be FROM_ON_AGRIINSURANCE_YYYYMMDD.zip

List of Files

Only the files from the list below will be processed.  All other files will not be processed.

NumberNameFrequencyEncoding
1ON_####_PRODUCERDATA_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (Crop Years 2018 to 2020)
  • Complete — Could be resent if agreed through the established change management process
ANSI
2ON_####_PRODUCERDATA_YYYYMMDD.csv
  • Once or Twice a year for 2021 onwards
  • Summer file: send by August 1st of the current year
  • Winter file: send by April 15th of the next year
ANSI
3ON_####_CLAIMS_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (Crop Years 2018 to 2020)
  • Complete — Could be resent if agreed through the established change management process
ANSI
4ON_####_CLAIMS_YYYYMMDD.csv
  • Send once a year by April 15th of the next year (for 2021 onwards)
ANSI
5ON_####_PREMIUMRATES_YYYYMMDD.csv
  • Send once a year by August 1st of the current year
ANSI
6ON_HISTORICAL_PRODUCERDATA_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (Historical years refer to Crop Years 2003 to 2017)
  • Complete — Could be resent if agreed through the established change management process
ANSI
7ON_HISTORICAL_CLAIMS_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (Historical years refer to Crop Years 2003 to 2017)
  • Complete — Could be resent if agreed through the established change management process
ANSI
8ON_HISTORICAL_PREMIUMRATES_YYYYMMDD.csv
  • One time submission but could be resent if need be (Historical years refer to Crop Years 2003 to 2017)
  • Complete — Could be resent if agreed through the established change management process
ANSI
9ON_REFERENCE_PLANTYPES_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (for up to 2020)
  • Complete — Could be resent if agreed through the established change management process
ANSI
10ON_REFERENCE_CROPCODES_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (for up to 2020)
  • Complete — Could be resent if agreed through the established change management process
ANSI
11ON_REFERENCE_CLAIMTYPES_UPTO2020_YYYYMMDD.csv
  • One time submission but could be resent if need be (for up to 2020)
  • Complete — Could be resent if agreed through the established change management process
ANSI
12ON_REFERENCE_PLANTYPES_YYYYMMDD.csv
  • One time submission but must be resent when updates required (for 2021 onwards)
  • Complete — Could be resent if agreed through the established change management process
ANSI
13ON_REFERENCE_CROPCODES_YYYYMMDD.csv
  • One time submission but must be resent when updates required (for 2021 onwards)
  • Complete — Could be resent if agreed through the established change management process
ANSI
14ON_REFERENCE_CLAIMTYPES_YYYYMMDD.csv
  • One time submission but must be resent when updates required (for 2021 onwards)
  • Complete — Could be resent if agreed through the established change management process
ANSI

Producer data at the Geotownship level (up to crop year 2020)

  • ON_####_PRODUCERDATA_UPTO2020_YYYYMMDD.csv
  • ON_HISTORICAL_PRODUCERDATA_UPTO2020_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequiredRequired Historical
1Policy NumberProvincial policy or contract numberENDRS_SERIAL_NOAlpha (8)N/AYesYes
2Producer IDProducer unique identifierCLIENT_ENROL_NOAlpha (8)N/AYesYes
3Processor IDProcessor unique ID associated with the CommodityPROC_NOAlpha (8)0's will be converted to NULLYesYes
4Crop YearYear of the AgriInsurance contractCROP_YRNumber (4)

Crop Year file: 

  • Must be from 1970 to current calendar year + 1, and
  • This must also match the #### year in the file name

Historical file:

  • This must be from 1970 to maximum historical crop year
YesYes
5Crop CodeCode associated to a specific cropCROP_CDAlpha (3)Look up the Crop Codes up to 2020 tableYesYes
6Crop DescriptionDescription of the crop, this will most of the time be the crop namecrop_nameAlpha (60)Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table.YesYes
7Insurance Status CodeInsurance status of the policyENDRS_STA_CDAlpha (3)

Acceptable values are: 

  • ACT
  • CAN
     
  • NIL
     
  • PND
YesYes
8Business NumberCRA Business Number of the insured producerBUSINESS_NUMBERAlpha (30)N/ANoNo
9CountyCounty where exposure units are insuredCOUNTYAlpha (60)N/ANoNo
10GeotownshipSpecific to Ontario, geotownship is similar to county but does not change over time.GEO_TOWNSHIPAlpha (60)N/ANoNo
11Coverage TypeThis is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.)plan_typeAlpha (60)Lookup to Plan Types up to 2020 tableYesYes
12Unit Price OptionCode associated to the Unit Price Option (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%)PRICE_OPT_TYPE_CDAlpha (3)

Acceptable values are:
 

  • FIX
  • FLF
NoNo
13Unit PriceUnit price/Coverage price selected and expressed in Price Unit (e.g. dollars per pound)priceNumber (19,5)Must be greater than or equal to 0NoNo
14Coverage Level DecimalCoverage level purchased by the producer (in decimal)coverageNumber (3,2)Must be greater than 0 and less than or equal to 1NoNo
15LiabilityTotal crop value based on the guaranteed production quantity and the coverage price option chosen for that year (in dollars)liabilityNumber (18,2)Must be greater than or equal to 0NoNo
16Number of Insured UnitsNumber of exposure units insuredexposureNumber (18,2)Must be greater than or equal to 0NoNo
17Exposure Unit CodeUnit that forms the basis of the insurance coverage (ac, ha, trees, etc.)unit_exposureAlpha (4)

Acceptable values (case insensitive):

  • ACR
  • COL
  • HIV
  • KG
  • LB
  • TREE
  • VINE
  • %
NoNo
18Probable YieldIndividual probable yield expressed in yield unit (e.g. pounds per acre)Probable_YieldNumber (18,2)Must be greater than 0NoNo
19Probable Yield Unit CodeUnit of measure for the probable yield (e.g. lbs/ac)unit_pyAlpha (4)

Acceptable values are:

  • $
  • $/A
  • BG/A
  • BU/A
  • CW/A
  • KG
  • KG/A
  • LB
  • LB/A
  • LB/H
  • TN/A
NoNo
20Harvested productionQuantity harvested expressed in production unit (e.g. pounds)Harvest_YieldNumber (18,2)Must be greater than or equal to 0NoNo
21Production Unit CodeUnit for the production quantity (pounds, kg, etc.)unit_ayAlpha (3)

Acceptable values are:

  • $
  • BAG
  • BU
     
  • COL
     
  • CWT
     
  • KG
     
  • LB
     
  • TON
NoNo
22Seeded DateActual or last date when the crop was seededLAST_SEEDED_DTDateValid date format is:  MM/DD/YYYYNoNo
23Harvested DateActual date producer completed harvesting the cropHARVEST_DTDateValid date format is:  MM/DD/YYYYNoNo
24Reseeding IndicatorIndicates whether or not producer has reseeding benefitsreplantNumber (1)

Valid values:

  • 1
NoNo
25Unseeded Acreage Benefit IndicatorIndicates a benefit for acres which cannot be salvagedUSABNumber (1)

Acceptable values are:

  • 0
  • 1
NoNo
26Nine Percent Rule IndicatorIndicates whether the 9 percent rule is exceededRule9PerNumber (1)

Acceptable values are:

  • 0
  • 1
NoNo
27Risk SplittingIndicator for risk splittingRiskSplitNumber (1)

Acceptable values are:

  • 0 or
  • 1
NoNo
28Comprehensive Producer PremiumsPremium paid by the producer (in dollars) at Comprehensive cost share levelPrem_Comp_ProdNumber (18,2)N/ANoNo
29Comprehensive Federal PremiumsPremium paid by the federal government (in dollars) at Comprehensive cost share levelPrem_Comp_FedNumber (18,2)N/ANoNo
30Comprehensive Provincial PremiumsPremium paid by the provincial government (in dollars) at Comprehensive cost share levelPrem_Comp_ProvNumber (18,2)N/ANoNo
31High Cost Producer PremiumsPremium paid by the producer (in dollars) at High Cost share levelPrem_HC_ProdNumber (18,2)Must be greater than or equal to 0NoNo
32High Cost Federal PremiumsPremium paid by the federal government (in dollars) at High Cost share levelPrem_HC_FedNumber (18,2)Must be greater than or equal to 0NoNo
33High Cost Provincial PremiumsPremium paid by the provincial government (in dollars) at High Cost share levelPrem_HC_ProvNumber (18,2)Must be greater than or equal to 0NoNo
34Catastrophic Producer PremiumsPremium paid by the producer (in dollars) at Catastrophic cost share levelPrem_Cat_ProdNumber (18,2)Must be greater than or equal to 0NoNo
35Catastrophic Federal PremiumsPremium paid by the federal government (in dollars) at Catastrophic cost share levelPrem_Cat_FedNumber (18,2)Must be greater than or equal to 0NoNo
36Catastrophic Provincial PremiumsPremium paid by the provincial government (in dollars) at Catastrophic cost share levelPrem_Cat_ProvNumber (18,2)Must be greater than or equal to 0NoNo
37Unseeded Acreage Benefit Premium - ProducerPremium ($) paid by producer for Unseeded Acreage benefitPrem_USAB_ProdNumber (18,2)Must be greater than or equal to 0NoNo
38Unseeded Acreage Benefit Premium - FederalPremium ($) paid by Federal Government for Unseeded Acreage benefitPrem_USAB_FedNumber (18,2)Must be greater than or equal to 0NoNo
39Unseeded Acreage Benefit Premium - ProvincePremium ($) paid by Province for Unseeded Acreage benefitPrem_USAB_ProvNumber (18,2)Must be greater than or equal to 0NoNo
40Total PremiumsTotal premium paid by federal, provincial and producer (in dollars) including all cost share levelstot_premNumber (20,2)Must be greater than 0NoNo
41Indemnity AmountIndemnity amount paid during the year to the producerPAYABLE_CLAIM_AMTNumber (20,2)Must be greater than or equal to 0NoNo

42

Surcharge/Discount decimalIndividual surcharge or discount offered to the producer based on his past experience (in decimal)Surcharge_DiscountNumber (5,2)

Acceptable range of values:

  • -50.00 to 50.00
NoNo

43

Excess Rainfall ThresholdA rainfall threshold amount selected by the customer to determine if a claim will be paidExcess_Rainfall_ThresholdAlpha (60)N/ANoNo

44

Excess Harvest PeriodTime period during which the grower typically begins the first cut for the excess rainfall optionExcess_Harvest_PeriodAlpha (60)N/ANoNo

45

Insufficient Rainfall Coverage OptionCoverage options for the insufficient rainfall coverageInsufficient_Rainfall_WeightingAlpha (60)N/ANoNo

46

Apple DistrictA field that indicates the apple farmer’s production ‘district’Apple_DistrictAlpha (20)

Valid values are:

  • District 3
  • District Other
NoNo

47

Spot Loss/Hail IndicatorIndicator for Spot Loss/Hail Rider OptionSpot_Loss_HailNumber (1)

Acceptable values are:

  • 0
  • 1
NoNo

Producer data at the Geotownship level for Crop Year 2021+

  • ON_####_PRODUCERDATA_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Producer IDProducer unique identifierProducer IDAlpha (10)N/AY
2Policy NumberProvincial policy or contract numberPolicy NumberAlpha (8)N/AY
3Processor IDProcessor unique ID associated with the CommodityProcessor IDAlpha (10)N/AN
4Crop YearYear of the AgriInsurance contractCrop YearNumber (4)

Crop Year file:

  • Must be from 2021 to current calendar year + 1, and
  • This must also match the #### year in the file name
Y
5Crop CodeCode associated to a specific cropCrop CodeAlpha (30)Look up the Crop Codes tableY
6Crop DescriptionDescription of the crop, this will most of the time be the crop namePlan NameAlpha (60)Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table.Y
7Insurance Status CodeInsurance status of the policyInsuredAlpha (10)

Acceptable values are: 

  • ACTIVE
  • CANCELLED
Y
8Business NumberCRA Business Number of the insured producerBusiness NumberAlpha (30)N/AN
9CountyCounty where exposure units are insuredCountyAlpha (60)N/AN
10GeotownshipSpecific to Ontario, geotownship is similar to county but does not change over time.GEO TownshipAlpha (60)N/AN
11Coverage TypeThis is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.)Plan TypeAlpha (60)Look up to Plan Types tableY
12Unit Price OptionCode associated to the Unit Price Option (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%)Unit Price TypeAlpha (10)

Acceptable values are: 

  • Fixed
  • Float
     
  • Fresh1
     
  • Fresh2
     
  • Fresh3
     
  • Fresh4
     
  • Proc1
     
  • Proc2
     
  • Proc3
N
13Unit PriceUnit price/Coverage price selected and expressed in Price Unit (e.g. dollars per pound)Unit PriceNumber (19,5)N/AN
14Commodity StatusIndicates whether a commodity is currently being produced and whether it is expected to be insured in the next year.Commodity StatusAlpha (60)

Acceptable values are:

  • Not Producing - Do Not Renew
  • Not Producing - Renew
  • Producing
Y
15Spot Loss/Hail IndicatorIndicator for Spot Loss/Hail Rider OptionSpot Loss/HailNumber (1)

Acceptable values are:
 

  • 0
  • 1
Y
16Protection Code DescriptionThis is the type of protection for the plan (e.g. Multirisk, single peril, protects against hail only, protects agains frost only, etc.)Peril OptionAlpha (60)

Acceptable values are:
 

  • Excess Rainfall
     
  • Frost Only
     
  • Hail and Frost Only
     
  • Hail Only
     
  • Insufficient and Excess Rainfall
     
  • Insufficient Rainfall
     
  • Multi Peril
Y
17Excess Harvest PeriodTime period during which the grower typically begins the first cut for the excess rainfall optionExcess Harvest PeriodAlpha (60)N/AN
18Excess Rainfall ThresholdA rainfall threshold amount selected by the customer to determine if a claim will be paidExcess Rainfall ThresholdAlpha (60)N/AN
19Insufficient Rainfall Coverage OptionCoverage options for the insufficient rainfall coverageInsufficient Rainfall WeightingAlpha (60)N/AN
20Coverage LevelCoverage level purchased by the producer (in percentage)Coverage Level (%)Number (3)Must be greater than 0 and less than or equal to 100N
21Tree/Vine Loss Coverage LevelPercentage insurance coverage selected by the customer for tree/vine lossTree/Vine Loss (%)Number (5,2)N/AN
22LiabilityTotal crop value based on the guaranteed production quantity and the coverage price option chosen for that year (in dollars)Insured Coverage ValueNumber (18,2)N/AY
23Tree/Vine Loss Guaranteed ValueGuaranteed dollar value of the tree/vine loss coverageTree/Vine Loss Guaranteed ValueNumber (18,2)N/AY
24Number of Insured UnitsNumber of exposure units insuredNumber of Insured Exposure UnitsNumber (18,2)N/AN
25Exposure Unit CodeUnit that forms the basis of the insurance coverage (ac, ha, trees, etc.)Exposure UnitsAlpha (60)

Acceptable values are:

  • Acres
  • Colonies
  • Hives
N
26Probable YieldIndividual probable yield expressed in yield unit (e.g. pounds per acre)Probable YieldNumber (18,2)N/AN
27Probable Yield Unit CodeUnit of measure for the probable yield (e.g. lbs/ac)Probable Yield UnitAlpha (60)

Acceptable values are:

  • 50 lb Bags per Acre
     
  • Bushels per acre
     
  • Dollars
     
  • Dollars per acre
     
  • Hundred Weight per acre
     
  • Kilograms per acre
     
  • Pounds per acre
     
  • Pounds per hive
     
  • Tons per acre
N
28Harvested productionQuantity harvested expressed in production unit (e.g. pounds)Total Harvested YieldNumber (18,2)Must be greater than or equal to 0N
29Production Unit CodeUnit for the production quantity (pounds, kg, etc.)Yield UnitAlpha (60)

Acceptable values are:

  • Acres
     
  • Ames
     
  • Bags (50 lb)
     
  • Bushels
     
  • Colonies
     
  • Dollars
  • Hundred Weight
     
  • Imperial Tons
     
  • Kilograms
     
  • Pounds
N
30Seeded DateActual or last date when the crop was seededSeeding DateDateValid date format is:  MM/DD/YYYY

*This will be converted to NULL when 01/01/1900
N
31Harvested DateActual date producer completed harvesting the cropCombining DateDateValid date format is:  MM/DD/YYYYN
32Reseeding IndicatorIndicates whether or not producer has reseeding benefitsBenefits Covered (Replant)Number (1)

Acceptable values are:

  • 0
  • 1
Y
33Unseeded Acreage Benefit IndicatorIndicates a benefit for acres which cannot be salvagedBenefits Covered (Unseeded)Number (1)

Acceptable values are:

  • 0
  • 1
Y
34Nine Percent Rule IndicatorIndicates whether the 9 percent rule is exceeded9% RuleNumber (1)

Acceptable values are:

  • 0
  • 1
Y
35Risk SplittingIndicator for risk splittingRisk SplittingNumber (1)

Acceptable values are:

  • 0
  • 1
Y
36Surcharge/Discount decimalIndividual surcharge or discount offered to the producer based on his past experience (in decimal)Surcharge DiscountNumber (5,2)

Acceptable range of values:

  • -50.00 to 50.00
N
37Comprehensive Producer PremiumsPremium paid by the producer (in dollars) at Comprehensive cost share levelProducer Premium Comprehensive (Excluding USAB)Number (18,2)Must be greater than or equal to 0N
38Comprehensive Federal PremiumsPremium paid by the federal government (in dollars) at Comprehensive cost share levelFederal Premium Comprehensive (Excluding USAB)Number (18,2)Must be greater than or equal to 0N
39Comprehensive Provincial PremiumsPremium paid by the provincial government (in dollars) at Comprehensive cost share levelProvincial Premium Comprehensive (Excluding USAB)Number (18,2)Must be greater than or equal to 0N
40High Cost Producer PremiumsPremium paid by the producer (in dollars) at High Cost share levelProducer Premium High CostNumber (18,2)Must be greater than or equal to 0N
41High Cost Federal PremiumsPremium paid by the federal government (in dollars) at High Cost share levelFederal Premium High CostNumber (18,2)Must be greater than or equal to 0N
42High Cost Provincial PremiumsPremium paid by the provincial government (in dollars) at High Cost share levelProvincial Premium High CostNumber (18,2)Must be greater than or equal to 0N
43Catastrophic Producer PremiumsPremium paid by the producer (in dollars) at Catastrophic cost share levelProducer Premium CatastrophicNumber (18,2)Must be greater than or equal to 0N
44Catastrophic Federal PremiumsPremium paid by the federal government (in dollars) at Catastrophic cost share levelFederal Premium CatastrophicNumber (18,2)Must be greater than or equal to 0N
45Catastrophic Provincial PremiumsPremium paid by the provincial government (in dollars) at Catastrophic cost share levelProvincial Premium CatastrophicNumber (18,2)Must be greater than or equal to 0N
46Unseeded Acreage Benefit Premium - ProducerPremium ($) paid by producer for Unseeded Acreage benefitProducer Premium USABNumber (18,2)Must be greater than or equal to 0N
47Unseeded Acreage Benefit Premium - FederalPremium ($) paid by Federal Government for Unseeded Acreage benefitFederal Premium USABNumber (18,2)Must be greater than or equal to 0N
48Unseeded Acreage Benefit Premium - ProvincePremium ($) paid by Province for Unseeded Acreage benefitProvincial Premium USABNumber (18,2)Must be greater than or equal to 0N
49Tree/Vine Loss Comprehensive Premiums - ProducerComprehensive Premium paid by the producer (in dollars) for tree/vine loss coverageProducer Premium Tree/Vine LossNumber (18,2)Must be greater than or equal to 0N
50Tree/Vine Loss Comprehensive Premiums - FederalComprehensive Premium paid by the federal government (in dollars) for tree/vine loss coverageFederal Premium Tree/Vine LossNumber (18,2)Must be greater than or equal to 0N
51Tree/Vine Loss Comprehensive Premiums - ProvinceComprehensive Premium paid by the province (in dollars) for tree/vine loss coverageProvincial Premium Tree/Vine LossNumber (18,2)Must be greater than or equal to 0N
52Total PremiumsTotal premium paid by federal, provincial and producer (in dollars) including all cost share levelsTotal PremiumNumber (20,2)Must be greater than or equal to 0N
53Apple DistrictA field that indicates the apple farmer’s production ‘district’Apple_DistrictAlpha (20)N/AN

Claim data at the Producer and Crop level (up to Crop Year 2020)

  • ON_####_CLAIMS_UPTO2020_YYYYMMDD.csv
  • ON_HISTORICAL_CLAIMS_UPTO2020_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequiredRequired Historical
1Crop YearYear of the AgriInsurance contractCROP_YRNumber (4)

Crop Year file:

  • Must be from 1970 to current calendar year + 1, and
  • This must also match the #### year in the file name
     

Historical file:

  • This must be from 1970 to maximum historical crop year
YY
2Policy NumberProvincial policy or contract numberENDRS_SERIAL_NOAlpha (8)N/AYY
3Producer IDProducer unique identifierCLIENT_ENROL_NOAlpha (8)N/AYY
4Crop CodeCode associated to a specific cropCROP_CDAlpha (3)Look up to Crop Codes up to 2020 tableYY
5Crop DescriptionDescription of the crop, this will most of the time be the crop namecrop_nameAlpha (30)Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table.YY
6Indemnity AmountIndemnity amount paid during the year to the producerPAYABLE_CLAIM_AMTNumber (18,2)Must be greater than or equal to 0YN
7Number of Indemnity Exposure UnitsNumber of exposure units that were indemnifiedDAMAGE_ACREAGENumber (18,2)Must be greater than or equal to 0YY
8Indemnity Paid DateThe date of the last indemnity paymentClaim_Approved_dtDateValid date format is:  MM/DD/YYYYNN
9Cause of loss descriptionDescription of the Cause of lossClaim_PerilAlpha (60)N/ANN
10Indemnity Type DescriptionDescription of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss)Claim_TypeAlpha (60)Lookup to Claim Types up to 2020 tableNN

Claim data at the Producer and Crop level for Crop Year 2021

  • ON_####_CLAIMS_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Producer IDProducer unique identifierProducer IDAlpha (10)N/AY
2Policy NumberProvincial policy or contract numberPolicy NumberAlpha (8)N/AY
3Processor IDProcessor unique ID associated with the Commodity (used for ON)Processor IDAlpha (10)N/AN
4Crop YearYear of the AgriInsurance contractCrop YearNumber (4)

Crop Year file: 

  • Must be from 2021 to current calendar year + 1, and
  • This must also match the #### year in the file name
Y
5Crop CodeCode associated to a specific cropCrop CodeAlpha (30)Look up the Crop Codes tableY
6Crop DescriptionDescription of the crop, this will most of the time be the crop namePlan NameAlpha (50)Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table.Y
7Indemnity AmountIndemnity amount paid during the year to the producerTotal indemnitiesNumber (18,2)Must be greater than or equal to 0N
7Number of Indemnity Exposure UnitsNumber of exposure units that were indemnifiedAcres for cause of lossNumber (18,2)Must be greater than or equal to 0N
8Indemnity Paid DateThe date of the last indemnity paymentIndemnity payment dateDateValid date format is:  MM/DD/YYYYY
9Cause of loss descriptionDescription of the Cause of lossCause of lossAlpha (60)

Acceptable Values are:

  • Disease
  • Drought
  • Flood
  • Freeze
  • Farm Labour Disruption
  • Frost
  • Hail
  • Heat
     
  • Lightning
     
  • Low Temperature
     
  • Pest Infestation
     
  • Precipitation
     
  • Uninsured
  • Wild Life
     
  • Wind
     
  • Winterkill
Y
10Indemnity Type DescriptionDescription of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss)Number of ClaimsAlpha (60)Lookup to Claim Types tableY
11Producer IDProducer unique identifierProducer IDAlpha (10)N/AY

Premium Rate data at the Crop level (up to 2017)

  • ON_HISTORICAL_PREMIUMRATES_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Crop YearYear of the AgriInsurance contractCrop YearNumber (4)

Historical file:

  • This must be from 1970 to maximum historical crop year
Y
2Crop CodeCode associated to a specific cropCrop CodeAlpha (3)Lookup to Crops tableY
3Plan or Crop Name in CertificationName of the plan or crop as it appears in the actuarial certification or on rate sheets.Crop PlanAlpha (100)N/AY
4Unit Price Option DescriptionDescription of the unit price option selected by the producer (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%)Price OptionAlpha (5)Must be one of the following values:
 Fixed
 Float
Y
5Coverage LevelCoverage level purchased by the producer (in percentage)Coverage LevelNumber (5,2)Must be greater than 0 and less than or equal to 100Y
6Pure Premium RatePure premium (in percentage)Calculated Claim Rate (Projected)Number (4,2)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
7Variable Price FactorFactor applied in the premium rate methodology to account for the additional risk due to variable unit price.Floating Price FactorNumber (4,3)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
8Uncertainty Load FactorUncertainty load factorUncertainty MarginNumber (4,3)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
9Debt Recovery Load DecimalDebt Recovery Load (in decimal)DebtLoadNumber (5,4)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
10SSL DecimalSelf-sustainability load (in decimal)SSLNumber (5,4)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
11Coverage Level Conversion FactorFactor applied in the premium rate methodology to convert premium rate at base coverage level to other coverage levels.Conversion Factor  for Coverage LevelNumber (4,3)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
12BBFBalance Back Factor applied to the cropERBBNumber (5,4)If Crop code = 71 (Grape Vines) then nulls are allowed.
If not null than must be greater than 0
N
13Exposure Base Adjustment FactorFactor applied in the premium rate methodology to keep premium rates neutral due to the impact of using a Brix schedule to determine claim pricesExposure Base Adjustment Factor (Grapes)Number (4,3)N/AN
14Indicated Premium RatePremium rate at the crop level including all loads, but before application of cap and individual surcharge/discount e.g. loyalty discount (in percent)Indicated Premium Rate as Percentage of LiabilityNumber (4,2)Must be greater than 0Y
15Guaranteed ProductionProduction quantity insured based on the probable yield and the exposure unit (in production unit e.g. pounds)Production GuaranteeNumber (7,2)Must be greater than 0N
16Estimated Unit PriceEstimated Price per production unit, used when calculating premium amountPremium PriceNumber (7,4)Must be greater than 0N
17Selected Premium RatePremium rate at the crop level including all loads and cap, before application of individual surcharge/discount e.g. loyalty discount (in percentage)Selected Premium RateNumber (5,2)Must be greater than 0Y
18Selected Premium Rate UnitUnit for the Selected Premium Rate (e.g. $ per acre, $ per hive, % of liability)Selected Premium Rate UnitAlpha (30)

Must be one of the following values:

  • $ per acre
  • $ per colony
     
  • $ per hive
     
  • % of insurable value
     
  • % of liability
     
  • % of liability (dollar value)
Y
19Apple Salvage Load DecimalLoad applied to premium rate to account for Apple salvage claims (in decimal)Salvage/WO Factor (Apples)Number (3,2)Must be 0 or greaterN
20Apple District FactorFactor applied to premium rate to account for historical district experienceDistrict_Factor (Apples)Number (3,2)Must be greater than 0N
21Buffering LoadA load added to premium to account for the buffering of yields.Buffering Load (Apples: 2017 and prior)Number (4,3)Must be greater than 0N
22Juice Floating Price LoadFactor applied in the premium rate methodology for apples to account for the additional risk due to variable unit price.Juice Floating Price Load (Apples: 2017 and prior)Number (4,3)Must be greater than 0N
23Hail Endorsement Load DecimalPremium rate load for hail endorsement program (in decimal)Hail Rider Load (Apples: 2017 and prior)Number (4,3)Must be greater than 0N
24Fresh Apple Only LoadA premium surcharge for clients who insure fresh apples only (instead of both fresh and juice apples).Fresh Only Loads = sum of Fresh Only Load and Excess Juice Load (Apples: 2017 and prior)Number (4,3)Must be greater than 0N
25Forage Rainfall Price Index FactorFactor applied to premium rate methodology to account for additional risk due to forage price indexPrice Index Factor (Forage Rainfall)Number (4,3)Must be greater than 0N
26Cucumbers Exchange Rate FactorFactor applied to premium rate to account for conversion from USD to CAD dollars.Exchange Rate Factor (Cucumbers)Number (4,3)Must be greater than 0N
27Organic FactorFactor applied to premium rate to account for additional risk due to organic growing practices.Organic_LoadNumber (4,3)Must be greater than 0N
28Surcharge/Discount Factor for QualitySurcharge/Discount Factor for Quality ProductsAdjustment Factor for Quality (Natto Soybeans)Number (4,3)Must be greater than 0N
29Grapes and Vines Variety Conversion FactorFactor applied to premium rate to convert different types of grapes and vines varieties (Hybrid, Labrusca, Vinifera).Variety Coversion Factor (Grapes & Vines)Number (4,3)Must be greater than 0N
30Hemp Fiber Only LoadFactor applied to premium rate to reflect reduced risk given that only fiber is insured.Fiber Only Load (Hemp)Number (5,4)Must be greater than 0N
31Total Production Coverage Only FactorFactor applied to premium rate to reflect risk due to producer's selection to insure all production (applies to vegetables).TPC Only Factor (Processing Vegetables)Number (3,2)Must be greater than 0N
32Hemp Fiber Only - Prior Year Premium Effective Base RatePrior Year Premium Effective Base Rate for Hemp Fiber OnlyFiber Only (Prior Year Premium Effective Base Rate)Number (6,4)Must be greater than 0N
33Hemp Fiber Only - Base Rate ChangeBase Rate Change for Hemp Fiber OnlyFiber Only (Base Rate Change)Number (5,4)Must be greater than 0N
34Separate Harvest Coverage LoadLoad applied to premium rate to reflect risk due to producer's selection to insure each harvest individually (applies to Processing Vegetables).SHC Load  (Processing Vegetables)Number (3,2)Must be 0 or greaterN
35Separate Insurance Coverage LoadLoad applied to premium rate to reflect risk due to producer's selection to insure each harvest individually (applies to Seed Corn)SIC Load (Seed Corn)Number (3,2)Must be 0 or greaterN

Premium Rate data at the Crop level for Crop Year 2018+

  • ON_####_PREMIUMRATES_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Crop YearYear of the AgriInsurance contractCrop YearNumber (4)

Crop Year file:

  • Must be from 2018 to current calendar year + 1, and
  • This must also match the #### year in the file name
Y
2Crop CodeCode associated to a specific cropCrop CodeAlpha (30)Lookup to both Crop Codes table   (Note: both Old and New Crop Codes will be contained in table)Y
3Plan or Crop Name in CertificationName of the plan or crop as it appears in the actuarial certification or on rate sheets.Crop PlanAlpha (100)N/AY
4Unit Price Option DescriptionDescription of the unit price option selected by the producer (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%)Price OptionAlpha (5)Must be one of the following values:
 Fixed
 Float
Y
5Coverage LevelCoverage level purchased by the producer (in percentage)Coverage LevelNumber (5,2)Must be greater than 0 and less than or equal to 100Y
6Pure Premium RatePure premium (in percentage)Calculated Claim Rate (Projected)Number (4,2)Must be greater than 0Y
7Variable Price FactorFactor applied in the premium rate methodology to account for the additional risk due to variable unit price.Floating Price FactorNumber (4,3)Must be greater than 0Y
8Uncertainty Load FactorUncertainty load factorUncertainty MarginNumber (4,3)Must be greater than 0Y
9Debt Recovery Load DecimalDebt Recovery Load (in decimal)DebtLoadNumber (5,4)N/AY
10SSL DecimalSelf-sustainability load (in decimal)SSLNumber (5,4)N/AY
11Coverage Level Conversion FactorFactor applied in the premium rate methodology to convert premium rate at base coverage level to other coverage levels.Conversion Factor  for Coverage LevelNumber (4,3)Must be greater than 0Y
12BBFBalance Back Factor applied to the cropERBBNumber (5,4)Must be greater than 0Y
13Exposure Base Adjustment FactorFactor applied in the premium rate methodology to keep premium rates neutral due to the impact of using a Brix schedule to determine claim pricesExposure Base Adjustment Factor (Grapes)Number (4,3)N/AN
14Indicated Premium RatePremium rate at the crop level including all loads, but before application of cap and individual surcharge/discount e.g. loyalty discount (in percent)Indicated Premium Rate as Percentage of LiabilityNumber (4,2)Must be greater than 0Y
15Guaranteed ProductionProduction quantity insured based on the probable yield and the exposure unit (in production unit e.g. pounds)Production GuaranteeNumber (7,2)Must be greater than 0N
16Estimated Unit PriceEstimated Price per production unit, used when calculating premium amountPremium PriceNumber (7,4)Must be greater than 0N
17Selected Premium RatePremium rate at the crop level including all loads and cap, before application of individual surcharge/discount e.g. loyalty discount (in percentage)Selected Premium RateNumber (5,2)Must be greater than 0Y
18Selected Premium Rate UnitUnit for the Selected Premium Rate (e.g. $ per acre, $ per hive, % of liability)Selected Premium Rate UnitAlpha (30)

Must be one of the following values:

  • $ per acre
  • $ per colony
     
  • $ per hive
     
  • % of insurable value
     
  • % of liability
Y
19Apple Salvage Load DecimalLoad applied to premium rate to account for Apple salvage claims (in decimal)Salvage/WO Factor (Apples)Number (3,2)Must be 0 or greaterN
20Apple District FactorFactor applied to premium rate to account for historical district experienceDistrict_Factor (Apples)Number (3,2)Must be greater than 0N
21Forage Rainfall Price Index FactorFactor applied to premium rate methodology to account for additional risk due to forage price indexPrice Index Factor (Forage Rainfall)Number (4,3)Must be greater than 0N
22Cucumbers Exchange Rate FactorFactor applied to premium rate to account for conversion from USD to CAD dollars.Exchange Rate Factor (Cucumbers)Number (4,3)Must be greater than 0N
23Conventional FactorFactor applied to premium rate to account for lower claim rate relativity for the conventional variety (for Corn and Soybeans only) compared to the total (conventional + organic) crop claim rateConventional Factor (Corn, Soybeans)Number (4,3)Must be greater than 0N
24Organic FactorFactor applied to premium rate to account for additional risk due to organic growing practices.Organic_LoadNumber (4,3)Must be greater than 0N
25Surcharge/Discount Factor for QualitySurcharge/Discount Factor for Quality ProductsAdjustment Factor for Quality (Natto Soybeans)Number (4,3)Must be greater than 0N
26Grapes and Vines Variety Conversion FactorFactor applied to premium rate to convert different types of grapes and vines varieties (Hybrid, Labrusca, Vinifera).Variety Coversion Factor (Grapes & Vines)Number (4,3)Must be greater than 0N
27Hemp Fiber Only LoadFactor applied to premium rate to reflect reduced risk given that only fiber is insured.Fiber Only Load (Hemp)Number (5,4)Must be greater than 0N
28Total Production Coverage Only FactorFactor applied to premium rate to reflect risk due to producer's selection to insure all production (applies to vegetables).TPC Only Factor (Processing Vegetables)Number (3,2)Must be greater than 0N
29Separate Harvest Coverage LoadLoad applied to premium rate to reflect risk due to producer's selection to insure each harvest individually (applies to Processing Vegetables).SHC Load  (Processing Vegetables)Number (3,2)Must be 0 or greaterN
30Separate Insurance Coverage LoadLoad applied to premium rate to reflect risk due to producer's selection to insure each harvest individually (applies to Seed Corn)SIC Load (Seed Corn)Number (3,2)Must be 0 or greaterN

Plan Type reference table (up to 2020)

  • ON_REFERENCE_PLANTYPES_UPTO2020_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Coverage TypeThis is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.)plan_typeAlpha (60)N/AYes

Crop Code reference table (up to 2020)

  • ON_REFERENCE_CROPCODES_UPTO2020_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Crop CodeCode associated to a specific cropCROP_CDAlpha (3)N/AYes
2Crop DescriptionDescription of the crop, this will most of the time be the crop namecrop_nameAlpha (60)N/AYes

Claim Type reference table (up to 2020)

  • ON_REFERENCE_CLAIMTYPES_UPTO2020_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Indemnity Type DescriptionDescription of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss)CLAIM_TYPEAlpha (60)N/AYes

 

Plan Type reference table (2021 onwards)

  • ON_REFERENCE_PLANTYPES_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Coverage TypeThis is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.)plan_typeAlpha (60)N/AYes

 

Crop Code reference table (2021 onwards)

  • ON_REFERENCE_CROPCODES_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Crop CodeCode associated to a specific cropCommodity CodeAlpha (30)N/AYes
2Crop DescriptionDescription of the crop, this will most of the time be the crop nameCommodity NameAlpha (60)N/AYes

Claim Type reference table (2021 onwards)

  • ON_REFERENCE_CLAIMTYPES_YYYYMMDD.csv
Field #AAFC Field NameDescriptionON Field NameData TypeValidationRequired
1Indemnity Type DescriptionDescription of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss)CLAIM_TYPEAlpha (60)N/AYes