Annex M: Data sharing agreement — file layout
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.
Number | Name | Frequency | Encoding |
---|---|---|---|
1 | ON_####_PRODUCERDATA_UPTO2020_YYYYMMDD.csv |
| ANSI |
2 | ON_####_PRODUCERDATA_YYYYMMDD.csv |
| ANSI |
3 | ON_####_CLAIMS_UPTO2020_YYYYMMDD.csv |
| ANSI |
4 | ON_####_CLAIMS_YYYYMMDD.csv |
| ANSI |
5 | ON_####_PREMIUMRATES_YYYYMMDD.csv |
| ANSI |
6 | ON_HISTORICAL_PRODUCERDATA_UPTO2020_YYYYMMDD.csv |
| ANSI |
7 | ON_HISTORICAL_CLAIMS_UPTO2020_YYYYMMDD.csv |
| ANSI |
8 | ON_HISTORICAL_PREMIUMRATES_YYYYMMDD.csv |
| ANSI |
9 | ON_REFERENCE_PLANTYPES_UPTO2020_YYYYMMDD.csv |
| ANSI |
10 | ON_REFERENCE_CROPCODES_UPTO2020_YYYYMMDD.csv |
| ANSI |
11 | ON_REFERENCE_CLAIMTYPES_UPTO2020_YYYYMMDD.csv |
| ANSI |
12 | ON_REFERENCE_PLANTYPES_YYYYMMDD.csv |
| ANSI |
13 | ON_REFERENCE_CROPCODES_YYYYMMDD.csv |
| ANSI |
14 | ON_REFERENCE_CLAIMTYPES_YYYYMMDD.csv |
| 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 Name | Description | ON Field Name | Data Type | Validation | Required | Required Historical |
---|---|---|---|---|---|---|---|
1 | Policy Number | Provincial policy or contract number | ENDRS_SERIAL_NO | Alpha (8) | N/A | Yes | Yes |
2 | Producer ID | Producer unique identifier | CLIENT_ENROL_NO | Alpha (8) | N/A | Yes | Yes |
3 | Processor ID | Processor unique ID associated with the Commodity | PROC_NO | Alpha (8) | 0's will be converted to NULL | Yes | Yes |
4 | Crop Year | Year of the AgriInsurance contract | CROP_YR | Number (4) | Crop Year file:
Historical file:
| Yes | Yes |
5 | Crop Code | Code associated to a specific crop | CROP_CD | Alpha (3) | Look up the Crop Codes up to 2020 table | Yes | Yes |
6 | Crop Description | Description of the crop, this will most of the time be the crop name | crop_name | Alpha (60) | Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table. | Yes | Yes |
7 | Insurance Status Code | Insurance status of the policy | ENDRS_STA_CD | Alpha (3) | Acceptable values are:
| Yes | Yes |
8 | Business Number | CRA Business Number of the insured producer | BUSINESS_NUMBER | Alpha (30) | N/A | No | No |
9 | County | County where exposure units are insured | COUNTY | Alpha (60) | N/A | No | No |
10 | Geotownship | Specific to Ontario, geotownship is similar to county but does not change over time. | GEO_TOWNSHIP | Alpha (60) | N/A | No | No |
11 | Coverage Type | This is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.) | plan_type | Alpha (60) | Lookup to Plan Types up to 2020 table | Yes | Yes |
12 | Unit Price Option | Code associated to the Unit Price Option (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%) | PRICE_OPT_TYPE_CD | Alpha (3) | Acceptable values are:
| No | No |
13 | Unit Price | Unit price/Coverage price selected and expressed in Price Unit (e.g. dollars per pound) | price | Number (19,5) | Must be greater than or equal to 0 | No | No |
14 | Coverage Level Decimal | Coverage level purchased by the producer (in decimal) | coverage | Number (3,2) | Must be greater than 0 and less than or equal to 1 | No | No |
15 | Liability | Total crop value based on the guaranteed production quantity and the coverage price option chosen for that year (in dollars) | liability | Number (18,2) | Must be greater than or equal to 0 | No | No |
16 | Number of Insured Units | Number of exposure units insured | exposure | Number (18,2) | Must be greater than or equal to 0 | No | No |
17 | Exposure Unit Code | Unit that forms the basis of the insurance coverage (ac, ha, trees, etc.) | unit_exposure | Alpha (4) | Acceptable values (case insensitive):
| No | No |
18 | Probable Yield | Individual probable yield expressed in yield unit (e.g. pounds per acre) | Probable_Yield | Number (18,2) | Must be greater than 0 | No | No |
19 | Probable Yield Unit Code | Unit of measure for the probable yield (e.g. lbs/ac) | unit_py | Alpha (4) | Acceptable values are:
| No | No |
20 | Harvested production | Quantity harvested expressed in production unit (e.g. pounds) | Harvest_Yield | Number (18,2) | Must be greater than or equal to 0 | No | No |
21 | Production Unit Code | Unit for the production quantity (pounds, kg, etc.) | unit_ay | Alpha (3) | Acceptable values are:
| No | No |
22 | Seeded Date | Actual or last date when the crop was seeded | LAST_SEEDED_DT | Date | Valid date format is: MM/DD/YYYY | No | No |
23 | Harvested Date | Actual date producer completed harvesting the crop | HARVEST_DT | Date | Valid date format is: MM/DD/YYYY | No | No |
24 | Reseeding Indicator | Indicates whether or not producer has reseeding benefits | replant | Number (1) | Valid values:
| No | No |
25 | Unseeded Acreage Benefit Indicator | Indicates a benefit for acres which cannot be salvaged | USAB | Number (1) | Acceptable values are:
| No | No |
26 | Nine Percent Rule Indicator | Indicates whether the 9 percent rule is exceeded | Rule9Per | Number (1) | Acceptable values are:
| No | No |
27 | Risk Splitting | Indicator for risk splitting | RiskSplit | Number (1) | Acceptable values are:
| No | No |
28 | Comprehensive Producer Premiums | Premium paid by the producer (in dollars) at Comprehensive cost share level | Prem_Comp_Prod | Number (18,2) | N/A | No | No |
29 | Comprehensive Federal Premiums | Premium paid by the federal government (in dollars) at Comprehensive cost share level | Prem_Comp_Fed | Number (18,2) | N/A | No | No |
30 | Comprehensive Provincial Premiums | Premium paid by the provincial government (in dollars) at Comprehensive cost share level | Prem_Comp_Prov | Number (18,2) | N/A | No | No |
31 | High Cost Producer Premiums | Premium paid by the producer (in dollars) at High Cost share level | Prem_HC_Prod | Number (18,2) | Must be greater than or equal to 0 | No | No |
32 | High Cost Federal Premiums | Premium paid by the federal government (in dollars) at High Cost share level | Prem_HC_Fed | Number (18,2) | Must be greater than or equal to 0 | No | No |
33 | High Cost Provincial Premiums | Premium paid by the provincial government (in dollars) at High Cost share level | Prem_HC_Prov | Number (18,2) | Must be greater than or equal to 0 | No | No |
34 | Catastrophic Producer Premiums | Premium paid by the producer (in dollars) at Catastrophic cost share level | Prem_Cat_Prod | Number (18,2) | Must be greater than or equal to 0 | No | No |
35 | Catastrophic Federal Premiums | Premium paid by the federal government (in dollars) at Catastrophic cost share level | Prem_Cat_Fed | Number (18,2) | Must be greater than or equal to 0 | No | No |
36 | Catastrophic Provincial Premiums | Premium paid by the provincial government (in dollars) at Catastrophic cost share level | Prem_Cat_Prov | Number (18,2) | Must be greater than or equal to 0 | No | No |
37 | Unseeded Acreage Benefit Premium - Producer | Premium ($) paid by producer for Unseeded Acreage benefit | Prem_USAB_Prod | Number (18,2) | Must be greater than or equal to 0 | No | No |
38 | Unseeded Acreage Benefit Premium - Federal | Premium ($) paid by Federal Government for Unseeded Acreage benefit | Prem_USAB_Fed | Number (18,2) | Must be greater than or equal to 0 | No | No |
39 | Unseeded Acreage Benefit Premium - Province | Premium ($) paid by Province for Unseeded Acreage benefit | Prem_USAB_Prov | Number (18,2) | Must be greater than or equal to 0 | No | No |
40 | Total Premiums | Total premium paid by federal, provincial and producer (in dollars) including all cost share levels | tot_prem | Number (20,2) | Must be greater than 0 | No | No |
41 | Indemnity Amount | Indemnity amount paid during the year to the producer | PAYABLE_CLAIM_AMT | Number (20,2) | Must be greater than or equal to 0 | No | No |
42 | Surcharge/Discount decimal | Individual surcharge or discount offered to the producer based on his past experience (in decimal) | Surcharge_Discount | Number (5,2) | Acceptable range of values:
| No | No |
43 | Excess Rainfall Threshold | A rainfall threshold amount selected by the customer to determine if a claim will be paid | Excess_Rainfall_Threshold | Alpha (60) | N/A | No | No |
44 | Excess Harvest Period | Time period during which the grower typically begins the first cut for the excess rainfall option | Excess_Harvest_Period | Alpha (60) | N/A | No | No |
45 | Insufficient Rainfall Coverage Option | Coverage options for the insufficient rainfall coverage | Insufficient_Rainfall_Weighting | Alpha (60) | N/A | No | No |
46 | Apple District | A field that indicates the apple farmer’s production ‘district’ | Apple_District | Alpha (20) | Valid values are:
| No | No |
47 | Spot Loss/Hail Indicator | Indicator for Spot Loss/Hail Rider Option | Spot_Loss_Hail | Number (1) | Acceptable values are:
| No | No |
Producer data at the Geotownship level for Crop Year 2021+
- ON_####_PRODUCERDATA_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Producer ID | Producer unique identifier | Producer ID | Alpha (10) | N/A | Y |
2 | Policy Number | Provincial policy or contract number | Policy Number | Alpha (8) | N/A | Y |
3 | Processor ID | Processor unique ID associated with the Commodity | Processor ID | Alpha (10) | N/A | N |
4 | Crop Year | Year of the AgriInsurance contract | Crop Year | Number (4) | Crop Year file:
| Y |
5 | Crop Code | Code associated to a specific crop | Crop Code | Alpha (30) | Look up the Crop Codes table | Y |
6 | Crop Description | Description of the crop, this will most of the time be the crop name | Plan Name | Alpha (60) | Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table. | Y |
7 | Insurance Status Code | Insurance status of the policy | Insured | Alpha (10) | Acceptable values are:
| Y |
8 | Business Number | CRA Business Number of the insured producer | Business Number | Alpha (30) | N/A | N |
9 | County | County where exposure units are insured | County | Alpha (60) | N/A | N |
10 | Geotownship | Specific to Ontario, geotownship is similar to county but does not change over time. | GEO Township | Alpha (60) | N/A | N |
11 | Coverage Type | This is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.) | Plan Type | Alpha (60) | Look up to Plan Types table | Y |
12 | Unit Price Option | Code associated to the Unit Price Option (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%) | Unit Price Type | Alpha (10) | Acceptable values are:
| N |
13 | Unit Price | Unit price/Coverage price selected and expressed in Price Unit (e.g. dollars per pound) | Unit Price | Number (19,5) | N/A | N |
14 | Commodity Status | Indicates whether a commodity is currently being produced and whether it is expected to be insured in the next year. | Commodity Status | Alpha (60) | Acceptable values are:
| Y |
15 | Spot Loss/Hail Indicator | Indicator for Spot Loss/Hail Rider Option | Spot Loss/Hail | Number (1) | Acceptable values are:
| Y |
16 | Protection Code Description | This is the type of protection for the plan (e.g. Multirisk, single peril, protects against hail only, protects agains frost only, etc.) | Peril Option | Alpha (60) | Acceptable values are:
| Y |
17 | Excess Harvest Period | Time period during which the grower typically begins the first cut for the excess rainfall option | Excess Harvest Period | Alpha (60) | N/A | N |
18 | Excess Rainfall Threshold | A rainfall threshold amount selected by the customer to determine if a claim will be paid | Excess Rainfall Threshold | Alpha (60) | N/A | N |
19 | Insufficient Rainfall Coverage Option | Coverage options for the insufficient rainfall coverage | Insufficient Rainfall Weighting | Alpha (60) | N/A | N |
20 | Coverage Level | Coverage level purchased by the producer (in percentage) | Coverage Level (%) | Number (3) | Must be greater than 0 and less than or equal to 100 | N |
21 | Tree/Vine Loss Coverage Level | Percentage insurance coverage selected by the customer for tree/vine loss | Tree/Vine Loss (%) | Number (5,2) | N/A | N |
22 | Liability | Total crop value based on the guaranteed production quantity and the coverage price option chosen for that year (in dollars) | Insured Coverage Value | Number (18,2) | N/A | Y |
23 | Tree/Vine Loss Guaranteed Value | Guaranteed dollar value of the tree/vine loss coverage | Tree/Vine Loss Guaranteed Value | Number (18,2) | N/A | Y |
24 | Number of Insured Units | Number of exposure units insured | Number of Insured Exposure Units | Number (18,2) | N/A | N |
25 | Exposure Unit Code | Unit that forms the basis of the insurance coverage (ac, ha, trees, etc.) | Exposure Units | Alpha (60) | Acceptable values are:
| N |
26 | Probable Yield | Individual probable yield expressed in yield unit (e.g. pounds per acre) | Probable Yield | Number (18,2) | N/A | N |
27 | Probable Yield Unit Code | Unit of measure for the probable yield (e.g. lbs/ac) | Probable Yield Unit | Alpha (60) | Acceptable values are:
| N |
28 | Harvested production | Quantity harvested expressed in production unit (e.g. pounds) | Total Harvested Yield | Number (18,2) | Must be greater than or equal to 0 | N |
29 | Production Unit Code | Unit for the production quantity (pounds, kg, etc.) | Yield Unit | Alpha (60) | Acceptable values are:
| N |
30 | Seeded Date | Actual or last date when the crop was seeded | Seeding Date | Date | Valid date format is: MM/DD/YYYY *This will be converted to NULL when 01/01/1900 | N |
31 | Harvested Date | Actual date producer completed harvesting the crop | Combining Date | Date | Valid date format is: MM/DD/YYYY | N |
32 | Reseeding Indicator | Indicates whether or not producer has reseeding benefits | Benefits Covered (Replant) | Number (1) | Acceptable values are:
| Y |
33 | Unseeded Acreage Benefit Indicator | Indicates a benefit for acres which cannot be salvaged | Benefits Covered (Unseeded) | Number (1) | Acceptable values are:
| Y |
34 | Nine Percent Rule Indicator | Indicates whether the 9 percent rule is exceeded | 9% Rule | Number (1) | Acceptable values are:
| Y |
35 | Risk Splitting | Indicator for risk splitting | Risk Splitting | Number (1) | Acceptable values are:
| Y |
36 | Surcharge/Discount decimal | Individual surcharge or discount offered to the producer based on his past experience (in decimal) | Surcharge Discount | Number (5,2) | Acceptable range of values:
| N |
37 | Comprehensive Producer Premiums | Premium paid by the producer (in dollars) at Comprehensive cost share level | Producer Premium Comprehensive (Excluding USAB) | Number (18,2) | Must be greater than or equal to 0 | N |
38 | Comprehensive Federal Premiums | Premium paid by the federal government (in dollars) at Comprehensive cost share level | Federal Premium Comprehensive (Excluding USAB) | Number (18,2) | Must be greater than or equal to 0 | N |
39 | Comprehensive Provincial Premiums | Premium paid by the provincial government (in dollars) at Comprehensive cost share level | Provincial Premium Comprehensive (Excluding USAB) | Number (18,2) | Must be greater than or equal to 0 | N |
40 | High Cost Producer Premiums | Premium paid by the producer (in dollars) at High Cost share level | Producer Premium High Cost | Number (18,2) | Must be greater than or equal to 0 | N |
41 | High Cost Federal Premiums | Premium paid by the federal government (in dollars) at High Cost share level | Federal Premium High Cost | Number (18,2) | Must be greater than or equal to 0 | N |
42 | High Cost Provincial Premiums | Premium paid by the provincial government (in dollars) at High Cost share level | Provincial Premium High Cost | Number (18,2) | Must be greater than or equal to 0 | N |
43 | Catastrophic Producer Premiums | Premium paid by the producer (in dollars) at Catastrophic cost share level | Producer Premium Catastrophic | Number (18,2) | Must be greater than or equal to 0 | N |
44 | Catastrophic Federal Premiums | Premium paid by the federal government (in dollars) at Catastrophic cost share level | Federal Premium Catastrophic | Number (18,2) | Must be greater than or equal to 0 | N |
45 | Catastrophic Provincial Premiums | Premium paid by the provincial government (in dollars) at Catastrophic cost share level | Provincial Premium Catastrophic | Number (18,2) | Must be greater than or equal to 0 | N |
46 | Unseeded Acreage Benefit Premium - Producer | Premium ($) paid by producer for Unseeded Acreage benefit | Producer Premium USAB | Number (18,2) | Must be greater than or equal to 0 | N |
47 | Unseeded Acreage Benefit Premium - Federal | Premium ($) paid by Federal Government for Unseeded Acreage benefit | Federal Premium USAB | Number (18,2) | Must be greater than or equal to 0 | N |
48 | Unseeded Acreage Benefit Premium - Province | Premium ($) paid by Province for Unseeded Acreage benefit | Provincial Premium USAB | Number (18,2) | Must be greater than or equal to 0 | N |
49 | Tree/Vine Loss Comprehensive Premiums - Producer | Comprehensive Premium paid by the producer (in dollars) for tree/vine loss coverage | Producer Premium Tree/Vine Loss | Number (18,2) | Must be greater than or equal to 0 | N |
50 | Tree/Vine Loss Comprehensive Premiums - Federal | Comprehensive Premium paid by the federal government (in dollars) for tree/vine loss coverage | Federal Premium Tree/Vine Loss | Number (18,2) | Must be greater than or equal to 0 | N |
51 | Tree/Vine Loss Comprehensive Premiums - Province | Comprehensive Premium paid by the province (in dollars) for tree/vine loss coverage | Provincial Premium Tree/Vine Loss | Number (18,2) | Must be greater than or equal to 0 | N |
52 | Total Premiums | Total premium paid by federal, provincial and producer (in dollars) including all cost share levels | Total Premium | Number (20,2) | Must be greater than or equal to 0 | N |
53 | Apple District | A field that indicates the apple farmer’s production ‘district’ | Apple_District | Alpha (20) | N/A | N |
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 Name | Description | ON Field Name | Data Type | Validation | Required | Required Historical |
---|---|---|---|---|---|---|---|
1 | Crop Year | Year of the AgriInsurance contract | CROP_YR | Number (4) | Crop Year file:
Historical file:
| Y | Y |
2 | Policy Number | Provincial policy or contract number | ENDRS_SERIAL_NO | Alpha (8) | N/A | Y | Y |
3 | Producer ID | Producer unique identifier | CLIENT_ENROL_NO | Alpha (8) | N/A | Y | Y |
4 | Crop Code | Code associated to a specific crop | CROP_CD | Alpha (3) | Look up to Crop Codes up to 2020 table | Y | Y |
5 | Crop Description | Description of the crop, this will most of the time be the crop name | crop_name | Alpha (30) | Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table. | Y | Y |
6 | Indemnity Amount | Indemnity amount paid during the year to the producer | PAYABLE_CLAIM_AMT | Number (18,2) | Must be greater than or equal to 0 | Y | N |
7 | Number of Indemnity Exposure Units | Number of exposure units that were indemnified | DAMAGE_ACREAGE | Number (18,2) | Must be greater than or equal to 0 | Y | Y |
8 | Indemnity Paid Date | The date of the last indemnity payment | Claim_Approved_dt | Date | Valid date format is: MM/DD/YYYY | N | N |
9 | Cause of loss description | Description of the Cause of loss | Claim_Peril | Alpha (60) | N/A | N | N |
10 | Indemnity Type Description | Description of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss) | Claim_Type | Alpha (60) | Lookup to Claim Types up to 2020 table | N | N |
Claim data at the Producer and Crop level for Crop Year 2021
- ON_####_CLAIMS_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Producer ID | Producer unique identifier | Producer ID | Alpha (10) | N/A | Y |
2 | Policy Number | Provincial policy or contract number | Policy Number | Alpha (8) | N/A | Y |
3 | Processor ID | Processor unique ID associated with the Commodity (used for ON) | Processor ID | Alpha (10) | N/A | N |
4 | Crop Year | Year of the AgriInsurance contract | Crop Year | Number (4) | Crop Year file:
| Y |
5 | Crop Code | Code associated to a specific crop | Crop Code | Alpha (30) | Look up the Crop Codes table | Y |
6 | Crop Description | Description of the crop, this will most of the time be the crop name | Plan Name | Alpha (50) | Not to be loaded in National Dataset. This info will be merged in from the Crop Codes table. | Y |
7 | Indemnity Amount | Indemnity amount paid during the year to the producer | Total indemnities | Number (18,2) | Must be greater than or equal to 0 | N |
7 | Number of Indemnity Exposure Units | Number of exposure units that were indemnified | Acres for cause of loss | Number (18,2) | Must be greater than or equal to 0 | N |
8 | Indemnity Paid Date | The date of the last indemnity payment | Indemnity payment date | Date | Valid date format is: MM/DD/YYYY | Y |
9 | Cause of loss description | Description of the Cause of loss | Cause of loss | Alpha (60) | Acceptable Values are:
| Y |
10 | Indemnity Type Description | Description of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss) | Number of Claims | Alpha (60) | Lookup to Claim Types table | Y |
11 | Producer ID | Producer unique identifier | Producer ID | Alpha (10) | N/A | Y |
Premium Rate data at the Crop level (up to 2017)
- ON_HISTORICAL_PREMIUMRATES_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Crop Year | Year of the AgriInsurance contract | Crop Year | Number (4) | Historical file:
| Y |
2 | Crop Code | Code associated to a specific crop | Crop Code | Alpha (3) | Lookup to Crops table | Y |
3 | Plan or Crop Name in Certification | Name of the plan or crop as it appears in the actuarial certification or on rate sheets. | Crop Plan | Alpha (100) | N/A | Y |
4 | Unit Price Option Description | Description of the unit price option selected by the producer (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%) | Price Option | Alpha (5) | Must be one of the following values: Fixed Float | Y |
5 | Coverage Level | Coverage level purchased by the producer (in percentage) | Coverage Level | Number (5,2) | Must be greater than 0 and less than or equal to 100 | Y |
6 | Pure Premium Rate | Pure 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 |
7 | Variable Price Factor | Factor applied in the premium rate methodology to account for the additional risk due to variable unit price. | Floating Price Factor | Number (4,3) | If Crop code = 71 (Grape Vines) then nulls are allowed. If not null than must be greater than 0 | N |
8 | Uncertainty Load Factor | Uncertainty load factor | Uncertainty Margin | Number (4,3) | If Crop code = 71 (Grape Vines) then nulls are allowed. If not null than must be greater than 0 | N |
9 | Debt Recovery Load Decimal | Debt Recovery Load (in decimal) | DebtLoad | Number (5,4) | If Crop code = 71 (Grape Vines) then nulls are allowed. If not null than must be greater than 0 | N |
10 | SSL Decimal | Self-sustainability load (in decimal) | SSL | Number (5,4) | If Crop code = 71 (Grape Vines) then nulls are allowed. If not null than must be greater than 0 | N |
11 | Coverage Level Conversion Factor | Factor applied in the premium rate methodology to convert premium rate at base coverage level to other coverage levels. | Conversion Factor for Coverage Level | Number (4,3) | If Crop code = 71 (Grape Vines) then nulls are allowed. If not null than must be greater than 0 | N |
12 | BBF | Balance Back Factor applied to the crop | ERBB | Number (5,4) | If Crop code = 71 (Grape Vines) then nulls are allowed. If not null than must be greater than 0 | N |
13 | Exposure Base Adjustment Factor | Factor applied in the premium rate methodology to keep premium rates neutral due to the impact of using a Brix schedule to determine claim prices | Exposure Base Adjustment Factor (Grapes) | Number (4,3) | N/A | N |
14 | Indicated Premium Rate | Premium 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 Liability | Number (4,2) | Must be greater than 0 | Y |
15 | Guaranteed Production | Production quantity insured based on the probable yield and the exposure unit (in production unit e.g. pounds) | Production Guarantee | Number (7,2) | Must be greater than 0 | N |
16 | Estimated Unit Price | Estimated Price per production unit, used when calculating premium amount | Premium Price | Number (7,4) | Must be greater than 0 | N |
17 | Selected Premium Rate | Premium rate at the crop level including all loads and cap, before application of individual surcharge/discount e.g. loyalty discount (in percentage) | Selected Premium Rate | Number (5,2) | Must be greater than 0 | Y |
18 | Selected Premium Rate Unit | Unit for the Selected Premium Rate (e.g. $ per acre, $ per hive, % of liability) | Selected Premium Rate Unit | Alpha (30) | Must be one of the following values:
| Y |
19 | Apple Salvage Load Decimal | Load applied to premium rate to account for Apple salvage claims (in decimal) | Salvage/WO Factor (Apples) | Number (3,2) | Must be 0 or greater | N |
20 | Apple District Factor | Factor applied to premium rate to account for historical district experience | District_Factor (Apples) | Number (3,2) | Must be greater than 0 | N |
21 | Buffering Load | A load added to premium to account for the buffering of yields. | Buffering Load (Apples: 2017 and prior) | Number (4,3) | Must be greater than 0 | N |
22 | Juice Floating Price Load | Factor 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 0 | N |
23 | Hail Endorsement Load Decimal | Premium rate load for hail endorsement program (in decimal) | Hail Rider Load (Apples: 2017 and prior) | Number (4,3) | Must be greater than 0 | N |
24 | Fresh Apple Only Load | A 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 0 | N |
25 | Forage Rainfall Price Index Factor | Factor applied to premium rate methodology to account for additional risk due to forage price index | Price Index Factor (Forage Rainfall) | Number (4,3) | Must be greater than 0 | N |
26 | Cucumbers Exchange Rate Factor | Factor applied to premium rate to account for conversion from USD to CAD dollars. | Exchange Rate Factor (Cucumbers) | Number (4,3) | Must be greater than 0 | N |
27 | Organic Factor | Factor applied to premium rate to account for additional risk due to organic growing practices. | Organic_Load | Number (4,3) | Must be greater than 0 | N |
28 | Surcharge/Discount Factor for Quality | Surcharge/Discount Factor for Quality Products | Adjustment Factor for Quality (Natto Soybeans) | Number (4,3) | Must be greater than 0 | N |
29 | Grapes and Vines Variety Conversion Factor | Factor 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 0 | N |
30 | Hemp Fiber Only Load | Factor 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 0 | N |
31 | Total Production Coverage Only Factor | Factor 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 0 | N |
32 | Hemp Fiber Only - Prior Year Premium Effective Base Rate | Prior Year Premium Effective Base Rate for Hemp Fiber Only | Fiber Only (Prior Year Premium Effective Base Rate) | Number (6,4) | Must be greater than 0 | N |
33 | Hemp Fiber Only - Base Rate Change | Base Rate Change for Hemp Fiber Only | Fiber Only (Base Rate Change) | Number (5,4) | Must be greater than 0 | N |
34 | Separate Harvest Coverage Load | Load 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 greater | N |
35 | Separate Insurance Coverage Load | Load 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 greater | N |
Premium Rate data at the Crop level for Crop Year 2018+
- ON_####_PREMIUMRATES_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Crop Year | Year of the AgriInsurance contract | Crop Year | Number (4) | Crop Year file:
| Y |
2 | Crop Code | Code associated to a specific crop | Crop Code | Alpha (30) | Lookup to both Crop Codes table (Note: both Old and New Crop Codes will be contained in table) | Y |
3 | Plan or Crop Name in Certification | Name of the plan or crop as it appears in the actuarial certification or on rate sheets. | Crop Plan | Alpha (100) | N/A | Y |
4 | Unit Price Option Description | Description of the unit price option selected by the producer (e.g. High, Low, Variable, Fixed or 100%, 80%, 60%) | Price Option | Alpha (5) | Must be one of the following values: Fixed Float | Y |
5 | Coverage Level | Coverage level purchased by the producer (in percentage) | Coverage Level | Number (5,2) | Must be greater than 0 and less than or equal to 100 | Y |
6 | Pure Premium Rate | Pure premium (in percentage) | Calculated Claim Rate (Projected) | Number (4,2) | Must be greater than 0 | Y |
7 | Variable Price Factor | Factor applied in the premium rate methodology to account for the additional risk due to variable unit price. | Floating Price Factor | Number (4,3) | Must be greater than 0 | Y |
8 | Uncertainty Load Factor | Uncertainty load factor | Uncertainty Margin | Number (4,3) | Must be greater than 0 | Y |
9 | Debt Recovery Load Decimal | Debt Recovery Load (in decimal) | DebtLoad | Number (5,4) | N/A | Y |
10 | SSL Decimal | Self-sustainability load (in decimal) | SSL | Number (5,4) | N/A | Y |
11 | Coverage Level Conversion Factor | Factor applied in the premium rate methodology to convert premium rate at base coverage level to other coverage levels. | Conversion Factor for Coverage Level | Number (4,3) | Must be greater than 0 | Y |
12 | BBF | Balance Back Factor applied to the crop | ERBB | Number (5,4) | Must be greater than 0 | Y |
13 | Exposure Base Adjustment Factor | Factor applied in the premium rate methodology to keep premium rates neutral due to the impact of using a Brix schedule to determine claim prices | Exposure Base Adjustment Factor (Grapes) | Number (4,3) | N/A | N |
14 | Indicated Premium Rate | Premium 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 Liability | Number (4,2) | Must be greater than 0 | Y |
15 | Guaranteed Production | Production quantity insured based on the probable yield and the exposure unit (in production unit e.g. pounds) | Production Guarantee | Number (7,2) | Must be greater than 0 | N |
16 | Estimated Unit Price | Estimated Price per production unit, used when calculating premium amount | Premium Price | Number (7,4) | Must be greater than 0 | N |
17 | Selected Premium Rate | Premium rate at the crop level including all loads and cap, before application of individual surcharge/discount e.g. loyalty discount (in percentage) | Selected Premium Rate | Number (5,2) | Must be greater than 0 | Y |
18 | Selected Premium Rate Unit | Unit for the Selected Premium Rate (e.g. $ per acre, $ per hive, % of liability) | Selected Premium Rate Unit | Alpha (30) | Must be one of the following values:
| Y |
19 | Apple Salvage Load Decimal | Load applied to premium rate to account for Apple salvage claims (in decimal) | Salvage/WO Factor (Apples) | Number (3,2) | Must be 0 or greater | N |
20 | Apple District Factor | Factor applied to premium rate to account for historical district experience | District_Factor (Apples) | Number (3,2) | Must be greater than 0 | N |
21 | Forage Rainfall Price Index Factor | Factor applied to premium rate methodology to account for additional risk due to forage price index | Price Index Factor (Forage Rainfall) | Number (4,3) | Must be greater than 0 | N |
22 | Cucumbers Exchange Rate Factor | Factor applied to premium rate to account for conversion from USD to CAD dollars. | Exchange Rate Factor (Cucumbers) | Number (4,3) | Must be greater than 0 | N |
23 | Conventional Factor | Factor 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 rate | Conventional Factor (Corn, Soybeans) | Number (4,3) | Must be greater than 0 | N |
24 | Organic Factor | Factor applied to premium rate to account for additional risk due to organic growing practices. | Organic_Load | Number (4,3) | Must be greater than 0 | N |
25 | Surcharge/Discount Factor for Quality | Surcharge/Discount Factor for Quality Products | Adjustment Factor for Quality (Natto Soybeans) | Number (4,3) | Must be greater than 0 | N |
26 | Grapes and Vines Variety Conversion Factor | Factor 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 0 | N |
27 | Hemp Fiber Only Load | Factor 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 0 | N |
28 | Total Production Coverage Only Factor | Factor 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 0 | N |
29 | Separate Harvest Coverage Load | Load 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 greater | N |
30 | Separate Insurance Coverage Load | Load 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 greater | N |
Plan Type reference table (up to 2020)
- ON_REFERENCE_PLANTYPES_UPTO2020_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Coverage Type | This is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.) | plan_type | Alpha (60) | N/A | Yes |
Crop Code reference table (up to 2020)
- ON_REFERENCE_CROPCODES_UPTO2020_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Crop Code | Code associated to a specific crop | CROP_CD | Alpha (3) | N/A | Yes |
2 | Crop Description | Description of the crop, this will most of the time be the crop name | crop_name | Alpha (60) | N/A | Yes |
Claim Type reference table (up to 2020)
- ON_REFERENCE_CLAIMTYPES_UPTO2020_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Indemnity Type Description | Description of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss) | CLAIM_TYPE | Alpha (60) | N/A | Yes |
Plan Type reference table (2021 onwards)
- ON_REFERENCE_PLANTYPES_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Coverage Type | This is the type of coverage the plan is insured under (e.g. Quantity, Hail, Acreage Loss, Spot Loss, etc.) | plan_type | Alpha (60) | N/A | Yes |
Crop Code reference table (2021 onwards)
- ON_REFERENCE_CROPCODES_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Crop Code | Code associated to a specific crop | Commodity Code | Alpha (30) | N/A | Yes |
2 | Crop Description | Description of the crop, this will most of the time be the crop name | Commodity Name | Alpha (60) | N/A | Yes |
Claim Type reference table (2021 onwards)
- ON_REFERENCE_CLAIMTYPES_YYYYMMDD.csv
Field # | AAFC Field Name | Description | ON Field Name | Data Type | Validation | Required |
---|---|---|---|---|---|---|
1 | Indemnity Type Description | Description of the Type of indemnity (e.g. Abandonment, Production, Hail spot loss) | CLAIM_TYPE | Alpha (60) | N/A | Yes |