Data dictionary for IR derived data in the IDI
Version: 1.0
Produced on: 25/09/2014
Producing agency: Statistics NZ (SNZ)
1. Document Control
1.1. Document History
No. / Date / Section Reference / Summary and Impact of change / Contact1.0 / 25/09/2014 / Entire Document / Version 1.0 created / Auren Clarke
1.2. Contact Details
1.2.1. Data dictionary responsibility
______
Name: Auren Clarke
Agency: Statistics NZ
Email address:
Phone number: 04 931 4405
Address: Statistics House, The Boulevard, Harbour Quays, Wellington
______
1.2.2. Statistics New Zealand Data Custodian
______
Name: Guido Stark
Agency: Statistics New Zealand
Email address:
Phone number: 04 931 4195
Address: Statistics House, The Boulevard, Harbour Quays, Wellington
______
2. Background
2.1. Introduction
As at the completion of the September 2014 IDI refresh, the IDI will include 5 IR derived tables. The tables sit under the data schema.
2.2. Purpose of this document
This document provides surrounding metadata for the aforementioned data tables, which includes:
· Population and coverage of tables
· Data dictionary and data item information of table variables
2.3. Intended audience of the document
Internally by IR teams and externally by Statistics NZ.
3. List of datasets
4. DATA.INCOME_CAL_YR
5. DATA.INCOME_TAX_YR
6. DATA.INCOME_CAL_YR_SUMMARY
7. DATA.INCOME_TAX_YR_SUMMARY
8. DATA.INCOME_PBN_ENT
22 | Page
4. Data Dictionary for the DATA.INCOME_CAL_YR table
4.1. Dataset Description
Introduction
The income_cal_yr table is one of five IR derived tables introduced for the September 2014 IDI refresh. The table is comprised of all records in the Employer Monthly Schedule (EMS). The monthly EMS records have then been arranged into the granularity of one record per payee/payer relationship, per income source, per year. The income source variable on the EMS was introduced in the June 2014 IDI refresh and is a primary key of the income_cal_yr table. The income_cal_yr table orders the monthly data into calendar years, beginning with the 1999 calendar year as the first records of the EMS table begin in April 1999. Thus, when referring to month 1 of the calendar year tables, this means the month of January.
STEP 1 – populate with all EMS records
The first step of the build of the income_cal_yr table is to populate it with all EMS records. Therefore, all records will reflect the ird_ems.ir_ems_income_source_code categories of:
- ‘W&S’ Wages and salaries
- ‘WHP’ Withholding payments
- ‘BEN’ Benefit payments
- ‘CLM’ Accident Compensation Corporations payments
- ‘PEN’ Pension payments
- ‘PPL’ Paid Parental Leave payments
- ‘STU’ Student Allowance payments
From here on, this document will now refer to the records as being in the income_cal_yr table, as opposed to EMS input records.
Note: When the variable ird_ems.ir_ems_fstc_amt is non-zero, it means that there is a family tax credit being paid, and hence the EMS record’s ‘employer’ is MSD. Therefore the record is coded as ‘BEN’, that is, a benefit payment. The EMS record’s earnings amount, however, that is then used in the IR income tables is the ird_ems.ir_ems_gross_earnings_amt. Also, the Family Tax Credit is paid both by Inland Revenue and MSD. MSD pays the Family Tax Credit when the family receives a main benefit from MSD and their total income is below a certain threshold, otherwise Inland Revenue pays it
STEP 2 – Identify self-employment relationships
The second step of the build involves relabeling income_cal_yr records using an array of IR source tables to identify when a record is a form of self-employment income (SEI). When there is sufficient evidence that a record is a form of self-employment income, the record is relabelled as a type SEI depending on the source of the evidence, and the income source variable will reflect this change. Therefore, the income source variable on the income_cal_yr table includes a wider array of options that on the EMS table.
Apply the following business rules to income_cal_yr records that have an income_source_code of ‘W&S’ and ‘WHP’, and where applicable, relabel the income source as one of the following self-employed income source categories:
1. ‘C01’ Company director/shareholder receiving PAYE deducted income
2. ‘P01’ Partner receiving PAYE deducted income
3. ‘S01’ Sole Trader receiving PAYE deducted income
4. ‘C02’ Company director/shareholder receiving WHT deducted income
5. ‘P02’ Partner receiving withholding tax deducted income
6. ‘S02’ Sole Trader receiving withholding tax deducted income
Wages and salaries self-employment income
An income_cal_yr record that has a withholding type code of ‘P’ means that the particular record represents a wage or salary. We do not know whether employee-employer uid relationship associated with the wage or salary represents a self-employed payee-payer relationship. By following the business rules outlined below, the income_cal_yr table classifies appropriate income_source_code ‘W&S’ records as either ‘C01’, ‘P01’, or ‘S01’
STEP2.1. ‘C01’ - Company director/shareholder receiving PAYE deducted income
After step 1 is complete, a record that should fall into the ir_inc_income_source_code category ‘C01’ (because the employee-employer relationship is in fact a company director/shareholder relationship) initially looks like a regular ‘W&S’ job with a withholding_type_code = ‘P’ and payer > payee. In the short-term, it is not possible to tell whether the income is sourced from a self-employed source as the annual tax returns that provide evidence of the relationship have not been filed, and hence the individual will be classified under W&S.
When the ird_cross_reference and IR4S tables are filed it becomes possible to identify evidence of a company relationship existing between the payer and payee, and therefore enables the classification of the record as income_source_code = ‘C01’, rather than the regular W&S. As the ability to code this requires tax forms that are filed annually and hence a delay compared to the monthly EMS, there will be a lag in populating the C01 income source fields.
Business rules part 1 – identify self-employment relationship:
IF
Rule A. reference_type_code in (‘DIR’, ‘SHR’, ‘EOH’) for employee-employer relationship (from ird_cross_reference table)
OR
Rule B. IR4S tax form has been filed for payee-payer relationship for period in question (from ird_rtns_keypoints_ir4s table)
THEN evidence of ‘Company director/shareholder’ relationship exists
Business rules part 2:
IF
Rule C. snz_ird_uid > snz_employer_ird_uid (Payee > payer)
Rule D. inc_cal_yr_withholding_type_code = ‘P’ (PAYE)
Rule E. ir_cus_entity_type_code = ‘I’ (payee is an individual, from ird_customers table)
THEN SET inc_cal_yr_income_source_code = ‘C01’
STEP2.2. ‘P01’ - Partner receiving PAYE deducted income
Next, a similar process is conducted to resolve ‘P01’ cases, that is, when a Partner is receiving PAYE deducted income. Instead of searching for a partnership relationship on the IR4S tax form; we instead use the IR20 tax form.
Business rules part 1 – identify self-employment relationship:
IF
Rule A. reference_type_code = ‘PTR’ for employee-employer relationship (from ird_cross_reference table)
OR
Rule B. IR20 tax form has been filed for payee-payer relationship for period in question (from ird_rtns_keypoints_ir20 table)
THEN evidence of ‘Partner’ relationship exists
Business rules part 2:
IF
Rule C. snz_ird_uid > snz_employer_ird_uid (Payee > payer)
Rule D. inc_cal_yr_withholding_type_code = ‘P’ (PAYE)
Rule E. ir_cus_entity_type_code = ‘I’ (payee is an individual, from ird_customers table)
THEN SET inc_cal_yr_income_source_code = ‘P01’
Note:
It is possible for an employer-employee_ird_uid relationship to pass the full list of business rules for both ‘P01’ and ‘C01’ simultaneously. This means that there is evidence that the payee-payer relationship exists in both the context of a ‘partner’ and ‘company director/shareholder/ domain. In this case, the income_cal_yr table has prioritised income_source_code ‘C01’ over ‘P01’, that is, the table will rank company relationships over partnership relationships.
STEP2.3. ‘S01’ - Sole Trader receiving PAYE deducted income
In this case, we are checking for evidence that a wage and salary record is in fact representing a sole trader paying him/herself PAYE deducted income. By checking the IRD numbers match for the payee and payer, the IRD number represents an individual, and that the individual is either GST registered or has a non-zero IR3 net profit amount for the period in question, and then it is possible to classify records as ‘S01’.
Business rules:
IF
Rule A. inc_cal_yr_income_source_code = ‘W&S’ (Wages and salaries)
Rule B. inc_cal_yr_withholding_type_code = ‘P’ (PAYE)
Rule C. snz_ird_uid = snz_employer_ird_uid (Payee = payer)
Rule D. ir_cus_entity_type_code = ‘I’ (payee is an individual, from ird_customers table)
And
Rule E. snz_uid is GST registered for the period (from ird_tax_registrations table)
Or
Rule F. snz_uid non-zero IR3 tax return for the period. (from ird_rtns_keypoints_ir3 table)
THEN SET inc_cal_yr_income_source_code = ‘S01’
Withholding payment self-employment income
An income_cal_yr record that has a withholding type code of ‘W’ means that the particular record represents a withholding payment. We do not know whether employee-employer uid relationship associated with the withholding payment is from a self-employed payee-payer relationship. By following the business rules outlined below, the income_cal_yr table classifies appropriate income_source_code ‘WHP’ records as either ‘C02’, ‘P02’, or ‘S02’.
STEP2.4. ‘C02’ - Company director/shareholder receiving WHT deducted income
The process of identifying ‘C02’ income sources is very similar to that of the aforementioned ‘C01’.
a record that should fall into the ir_inc_income_source_code category ‘C02’ (because the employee-employer relationship is in fact a company director/shareholder relationship) initially looks like a regular ‘WHP’ job with a withholding_type_code = ‘W’ and payer > payee. In the short-term, it is not possible to tell whether the income is sourced from a self-employed source as the annual tax returns that provide evidence of the relationship have not been filed, and hence the individual will be classified under WHP.
When the ird_cross_reference and IR4S tables are filed it becomes possible to identify evidence of a company relationship existing between the payer and payee, and therefore enables the classification of the record as income_source_code = ‘C02’, rather than the regular WHP. As the ability to code this requires tax forms that are filed annually and hence a delay compared to the monthly EMS, there will be a lag in populating the C02 income source fields.
Business rules part 1 – identify self-employment relationship:
IF
Rule A. reference_type_code in (‘DIR’, ‘SHR’, ‘EOH’) for employee-employer relationship (from ird_cross_reference table)
OR
Rule B. IR4S tax form has been filed for payee-payer relationship for period in question (from ird_rtns_keypoints_ir4s table)
THEN evidence of ‘Company director/shareholder’ relationship exists
Business rules part 2:
IF
Rule C. inc_cal_yr_withholding_type_code = ‘W’ (Withholding payment)
Rule D. ir_cus_entity_type_code = ‘I’ (payee is an individual, from ird_customers table)
THEN SET inc_cal_yr_income_source_code = ‘C02’
STEP2.5. ‘P02’ - Partner receiving withholding tax deducted income
Next, a similar process is conducted to resolve ‘P02’ cases, that is, when a Partner is receiving Withholding payment income. Instead of searching for a partnership relationship on the IR4S tax form; we instead use the IR20 tax form.
Business rules part 1 – identify self-employment relationship:
IF
Rule A. reference_type_code = ‘PTR’ for employee-employer relationship (from ird_cross_reference table)
OR
Rule B. IR20 tax form has been filed for payee-payer relationship for period in question (from ird_rtns_keypoints_ir20 table)
THEN evidence of ‘Partner’ relationship exists
Business rules part 2:
IF
Rule C. inc_cal_yr_withholding_type_code = ‘W’ (Withholding payment)
Rule D. ir_cus_entity_type_code = ‘I’ (payee is an individual, from ird_customers table)
THEN SET inc_cal_yr_income_source_code = ‘P02’
Note:
It is possible for an employer-employee_ird_uid relationship to pass the full list of business rules for both ‘P02’ and ‘C02’ simultaneously. This means that there is evidence that the payee-payer relationship exists in both the context of a ‘partner’ and ‘company director/shareholder/ domain. In this case, the income_cal_yr table has prioritised income_source_code ‘C02’ over ‘P02’, that is, the table will rank company relationships over partnership relationships.
STEP2.6. ‘S02’ - Sole Trader receiving withholding tax deducted income
Lastly, a similar process is conducted to resolve ‘S02’ cases, that is, a sole trader receiving withholding tax deducted income. Instead of searching for a partnership relationship or a company relationship using the IR4S, IR20 and ird_cross_reference tables, we search for a lack of partnership relationship or a company relationship. In the situation that a record has a withholding type code of W and there is no evidence that a company or partnership relationship exists between the payee-payer, the income_cal_yr table classifies the record as ‘S02’.
Business rules part 1 – identify non-existent company/partnership relationship:
IF
Rule A. reference_type_code not in (‘PTR’, ‘DIR’, ‘SHR’, ‘EOH’) for payee-payer relationship (from ird_cross_reference table)
Rule B. No IR4S tax form has been filed for payee-payer relationship for period in question (from ird_rtns_keypoints_ir4s table)
Rule C. No IR20 tax form has been filed for payee-payer relationship for period in question (from ird_rtns_keypoints_ir20 table)
THEN sufficient evidence to conclude no ‘Partner’/’Company’ relationship exists
Business rules part 2:
IF
Rule D. inc_cal_yr_withholding_type_code = ‘W’ (Withholding payment)
Rule E. ir_cus_entity_type_code = ‘I’ (payee is an individual, from ird_customers table)
THEN SET inc_cal_yr_income_source_code = ‘S02’
22 | Page
4.2. Summary table
Variable Name / Primary Key / Mandatory / Formatinc_cal_yr_year_nbr / Y / Not null / 4N
snz_uid / Y / Not null / 8N
snz_ird_uid / Not null / 8N
inc_cal_yr_snz_employer_ird_uid / Y / Not null / 8N
inc_cal_yr_income_source_code / Y / Not null / 3A
inc_cal_yr_withholding_type_code / Not null / 1A
inc_cal_yr_mth_01_amt / Not null / 15N
inc_cal_yr_mth_02_amt / Not null / 15N
inc_cal_yr_mth_03_amt / Not null / 15N
inc_cal_yr_mth_04_amt / Not null / 15N
inc_cal_yr_mth_05_amt / Not null / 15N
inc_cal_yr_mth_06_amt / Not null / 15N
inc_cal_yr_mth_07_amt / Not null / 15N
inc_cal_yr_mth_08_amt / Not null / 15N
inc_cal_yr_mth_09_amt / Not null / 15N
inc_cal_yr_mth_10_amt / Not null / 15N
inc_cal_yr_mth_11_amt / Not null / 15N
inc_cal_yr_mth_12_amt / Not null / 15N
inc_cal_yr_tot_yr_amt / Not null / 15N
4.3. Detailed information
______
Table name: DATA.INCOME_CAL_YR
______