Document Control



Data dictionary for IR derived data in the IDI Version: 1.0Produced on: 25/09/201417145010829925Producing agency: Statistics NZ (SNZ)Document ControlDocument HistoryNo.DateSection ReferenceSummary and Impact of changeContact1.025/09/2014Entire DocumentVersion 1.0 createdAuren ClarkeContact DetailsData dictionary responsibility_____________________________________________________Name: Auren Clarke Agency: Statistics NZ Email address: auren.clarke@t.nzPhone number: 04 931 4405Address: Statistics House, The Boulevard, Harbour Quays, Wellington_______________________________________________________Statistics New Zealand Data Custodian_____________________________________________________Name: Guido StarkAgency: Statistics New ZealandEmail address: Guido.stark@t.nzPhone number: 04 931 4195Address: Statistics House, The Boulevard, Harbour Quays, Wellington_______________________________________________________BackgroundIntroductionAs at the completion of the September 2014 IDI refresh, the IDI will include 5 IR derived tables. The tables sit under the data schema.Purpose of this documentThis document provides surrounding metadata for the aforementioned data tables, which includes:Population and coverage of tablesData dictionary and data item information of table variablesIntended audience of the documentInternally by IR teams and externally by Statistics NZ.List of datasetsDATA.INCOME_CAL_YRDATA.INCOME_TAX_YRDATA.INCOME_CAL_YR_SUMMARYDATA.INCOME_TAX_YR_SUMMARYDATA.INCOME_PBN_ENTData Dictionary for the DATA.INCOME_CAL_YR tableDataset Description IntroductionThe 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 recordsThe 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 paymentsFrom 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 itSTEP 2 – Identify self-employment relationshipsThe 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:‘C01’ Company director/shareholder receiving PAYE deducted income ‘P01’ Partner receiving PAYE deducted income‘S01’ Sole Trader receiving PAYE deducted income‘C02’ Company director/shareholder receiving WHT deducted income‘P02’Partner receiving withholding tax deducted income ‘S02’ Sole Trader receiving withholding tax deducted incomeWages and salaries self-employment incomeAn 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’‘C01’ - Company director/shareholder receiving PAYE deducted incomeAfter 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:IFreference_type_code in (‘DIR’, ‘SHR’, ‘EOH’) for employee-employer relationship(from ird_cross_reference table)ORIR4S 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 existsBusiness rules part 2:IFsnz_ird_uid <> snz_employer_ird_uid(Payee <> payer)inc_cal_yr_withholding_type_code = ‘P’(PAYE)ir_cus_entity_type_code = ‘I’(payee is an individual, from ird_customers table)THEN SET inc_cal_yr_income_source_code = ‘C01’44005557210ir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir4sdata.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.1SET ‘W&S’ records to ‘C01’ where conditions met Rules C and D.Rule E.IF rules (A or B), C, D and E pass, THEN SET income_source_code = ‘C01’ir_clean.ird_cross_referenceRule A.Rule B.ORANDANDir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir4sdata.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.1SET ‘W&S’ records to ‘C01’ where conditions met Rules C and D.Rule E.IF rules (A or B), C, D and E pass, THEN SET income_source_code = ‘C01’ir_clean.ird_cross_referenceRule A.Rule B.ORANDAND‘P01’ - Partner receiving PAYE deducted incomeNext, 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:IFreference_type_code = ‘PTR’ for employee-employer relationship(from ird_cross_reference table)ORIR20 tax form has been filed for payee-payer relationship for period in question(from ird_rtns_keypoints_ir20 table)THEN evidence of ‘Partner’ relationship existsBusiness rules part 2:IFsnz_ird_uid <> snz_employer_ird_uid(Payee <> payer)inc_cal_yr_withholding_type_code = ‘P’(PAYE)ir_cus_entity_type_code = ‘I’(payee is an individual, from ird_customers table)THEN SET inc_cal_yr_income_source_code = ‘P01’448945132715ir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir20data.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.2SET ‘W&S’ records to ‘P01’ where conditions met Rules C and D.Rule E.IF rules (A or B), C, D and E pass, THEN SET income_source_code = ‘P01’ir_clean.ird_cross_referenceRule A.Rule B.ORANDANDir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir20data.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.2SET ‘W&S’ records to ‘P01’ where conditions met Rules C and D.Rule E.IF rules (A or B), C, D and E pass, THEN SET income_source_code = ‘P01’ir_clean.ird_cross_referenceRule A.Rule B.ORANDANDNote: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.‘S01’ - Sole Trader receiving PAYE deducted incomeIn 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:IFinc_cal_yr_income_source_code = ‘W&S’(Wages and salaries)inc_cal_yr_withholding_type_code = ‘P’(PAYE)snz_ird_uid = snz_employer_ird_uid(Payee = payer)ir_cus_entity_type_code = ‘I’(payee is an individual, from ird_customers table)Andsnz_uid is GST registered for the period (from ird_tax_registrations table)Orsnz_uid non-zero IR3 tax return for the period.(from ird_rtns_keypoints_ir3 table)THEN SET inc_cal_yr_income_source_code = ‘S01’1612265-715645ir_clean.ird_emsdata.income_cal_yrir_clean.ird_customersir_clean.ird_tax_registrationsir_clean.ird_rtns_keypoints_ir3data.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.3 SET ‘W&S’ records to ‘S01’ where conditions met Rule E.Rule F.IF rules A, B, C, D and (E or F) pass, THEN SET income_source_code = ‘S01’data.income_cal_yrRules A, B and C.Rule D.ORir_clean.ird_emsdata.income_cal_yrir_clean.ird_customersir_clean.ird_tax_registrationsir_clean.ird_rtns_keypoints_ir3data.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.3 SET ‘W&S’ records to ‘S01’ where conditions met Rule E.Rule F.IF rules A, B, C, D and (E or F) pass, THEN SET income_source_code = ‘S01’data.income_cal_yrRules A, B and C.Rule D.ORWithholding payment self-employment incomeAn 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’.‘C02’ - Company director/shareholder receiving WHT deducted incomeThe 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:IFreference_type_code in (‘DIR’, ‘SHR’, ‘EOH’) for employee-employer relationship(from ird_cross_reference table)ORIR4S 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 existsBusiness rules part 2:IFinc_cal_yr_withholding_type_code = ‘W’(Withholding payment)ir_cus_entity_type_code = ‘I’(payee is an individual, from ird_customers table)THEN SET inc_cal_yr_income_source_code = ‘C02’396875177165ir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir4sdata.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.4SET ‘WHP’ records to ‘C02’ where conditions met Rule C.Rule D.IF rules (A or B), C, and D pass, THEN SET income_source_code = ‘C02’ir_clean.ird_cross_referenceRule A.Rule B.ORANDANDir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir4sdata.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.4SET ‘WHP’ records to ‘C02’ where conditions met Rule C.Rule D.IF rules (A or B), C, and D pass, THEN SET income_source_code = ‘C02’ir_clean.ird_cross_referenceRule A.Rule B.ORANDAND‘P02’ - Partner receiving withholding tax deducted incomeNext, 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:IFreference_type_code = ‘PTR’ for employee-employer relationship(from ird_cross_reference table)ORIR20 tax form has been filed for payee-payer relationship for period in question(from ird_rtns_keypoints_ir20 table)THEN evidence of ‘Partner’ relationship existsBusiness rules part 2:IFinc_cal_yr_withholding_type_code = ‘W’(Withholding payment)ir_cus_entity_type_code = ‘I’(payee is an individual, from ird_customers table)THEN SET inc_cal_yr_income_source_code = ‘P02’46581520734ir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir20data.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.5SET ‘WHP’ records to ‘P02’ where conditions met Rule C.Rule D.IF rules (A or B), C, and D pass, THEN SET income_source_code = ‘P02’ir_clean.ird_cross_referenceRule A.Rule B.ORANDANDir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir20data.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.5SET ‘WHP’ records to ‘P02’ where conditions met Rule C.Rule D.IF rules (A or B), C, and D pass, THEN SET income_source_code = ‘P02’ir_clean.ird_cross_referenceRule A.Rule B.ORANDANDNote: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.‘S02’ - Sole Trader receiving withholding tax deducted incomeLastly, 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:IFreference_type_code not in (‘PTR’, ‘DIR’, ‘SHR’, ‘EOH’) for payee-payer relationship(from ird_cross_reference table)No IR4S tax form has been filed for payee-payer relationship for period in question(from ird_rtns_keypoints_ir4s table)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 existsBusiness rules part 2:IFinc_cal_yr_withholding_type_code = ‘W’(Withholding payment)ir_cus_entity_type_code = ‘I’(payee is an individual, from ird_customers table)THEN SET inc_cal_yr_income_source_code = ‘S02’517980-224071ir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir20data.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.6SET ‘WHP’ records to ‘S02’ where conditions met Rule D.Rule E.IF rules A, B, C, D, and E pass THEN SET income_source_code = ‘S02’ir_clean.ird_cross_referenceRule A.Rule C.ir_clean.ird_rtns_keypoints_ir4sRule B.00ir_clean.ird_emsdata.income_cal_yrir_clean.ird_rtns_keypoints_ir20data.income_cal_yrir_clean.ird_customersdata.income_cal_yr STEP 1 BUILD income_cal_yr from ird_emsSTEP 2.6SET ‘WHP’ records to ‘S02’ where conditions met Rule D.Rule E.IF rules A, B, C, D, and E pass THEN SET income_source_code = ‘S02’ir_clean.ird_cross_referenceRule A.Rule C.ir_clean.ird_rtns_keypoints_ir4sRule B.Summary table Variable NamePrimary KeyMandatoryFormatinc_cal_yr_year_nbrYNot null4Nsnz_uidYNot null8Nsnz_ird_uidNot null8Ninc_cal_yr_snz_employer_ird_uidYNot null8Ninc_cal_yr_income_source_codeYNot null3Ainc_cal_yr_withholding_type_codeNot null1Ainc_cal_yr_mth_01_amtNot null15Ninc_cal_yr_mth_02_amtNot null15Ninc_cal_yr_mth_03_amtNot null15Ninc_cal_yr_mth_04_amtNot null15Ninc_cal_yr_mth_05_amtNot null15Ninc_cal_yr_mth_06_amtNot null15Ninc_cal_yr_mth_07_amtNot null15Ninc_cal_yr_mth_08_amtNot null15Ninc_cal_yr_mth_09_amtNot null15Ninc_cal_yr_mth_10_amtNot null15Ninc_cal_yr_mth_11_amtNot null15Ninc_cal_yr_mth_12_amtNot null15Ninc_cal_yr_tot_yr_amtNot null15NDetailed information _____________________________________ Table name: DATA.INCOME_CAL_YR_________________________________________ Variable Name: inc_cal_yr_year_nbrDefinition: The calendar year, that is, the year beginning the month of January and ending the month of December.Format: Numeric, 4NName of classification: Notes: _______________________________________ Variable Name: snz_uid Definition: The IDI global unique identifier relating to the individual. If a link has been generated for this individual across collections, then this individual will have the same snz_uid across collections. Each refresh, all links are reprocessed, therefore, the snz_uid is refresh specific, and will change between archives. This differs to snz_ird_uid below.Format: Numeric, 8NName of classification: Notes: _______________________________________ Variable Name: snz_ird_uidDefinition: The encrypted IRD number of the individual. This is consistent across time, and can be used between refreshes. This differs to snz_uid (above), as encryption is consistent over time, whereas snz_uid is not.Format: Numeric, 8NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_snz_employer_ird_uidDefinition: Similar to snz_ird_uid, yet snz_employer_ird_uid is the encrypted IRD number of the employer for the income source in question. In some cases the employer will equal the employee (hence snz_ird_uid = snz_employer_ird_uid) which will be the case for sole traders. Format: Numeric, 8NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_income_source_codeDefinition: Defines the type of income the record represents, e.g.:‘W&S’ Wages and salaries ‘BEN’ Benefit payments from the Ministry of Social Development (MSD)‘CLM’ Accident Compensation Corporation (ACC) payments‘PEN’ Pension payments from MSD‘PPL’ Paid parental leave payments from MSD‘STU’ Student allowance payments from MSD‘IRD’ IRD number used by Inland Revenue‘TPR’ IRD number used by Ministry of Education – teacher payroll‘SNZ’ IRD number used by Statistics NZ. ‘C01’ Company director/shareholder receiving PAYE deducted income ‘C02’ Company director/shareholder receiving WHT deducted income ‘P01’ Partner receiving PAYE deducted income ‘P02’ Partner receiving withholding tax deducted income ‘S01’ Sole Trader receiving PAYE deducted income‘S02’ Sole Trader receiving withholding tax deducted income Format: Character, 3AName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_withholding_type_codeDefinition: P for PAYE deductions, W for withholding tax deductions. This column is defaulted to 'P' when withholding type code in the incoming data is NULL.Format: Character, 1AName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_01_amtDefinition: The amount of income that was recorded in the first month of calendar year corresponding to the year in year_nbr, i.e. JanuaryFormat: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_02_amtDefinition: The amount of income that was recorded in Format: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_03_amtDefinition: The amount of income that was recorded in Format: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_04_amtDefinition: The amount of income that was recorded inFormat: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_05_amtDefinition: The amount of income that was recorded inFormat: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_06_amtDefinition: The amount of income that was recorded in Format: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_07_amtDefinition: The amount of income that was recorded inFormat: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_08_amtDefinition: The amount of income that was recorded in Format: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_09_amtDefinition: The amount of income that was recorded inFormat: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_10_amt Definition: The amount of income that was recorded inFormat: Numeric, 15NName of classification: Notes: Variable Name: inc_cal_yr_mth_11_amtDefinition: The amount of income that was recorded inFormat: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_mth_12_amtDefinition: The amount of income that was recorded inFormat: Numeric, 15N Name of classification: Notes: _______________________________________ Variable Name: inc_cal_yr_tot_yr_amtDefinition: The annual sum of income across all calendar months for the income source for that calendar year, i.e. sum (inc_cal_yr_mth_01_amt – inc_cal_yr_mth_12_amt)Format: Numeric, 15NName of classification: Notes: _______________________________________ Data Dictionary for the DATA.INCOME_TAX_YR tableDataset Description The income_tax_yr table the second of five IR derived tables introduced for the October 2014 IDI refresh. The table is comprised of all records in the Employer Monthly Schedule (EMS), plus additional records from the IR3, IR4S and IR20 tax forms. As the information on the IR3, IR4S and IR20 tax forms relate to the tax year, income sources from these sources that relate to individuals have been incorporated into this table.The records have then been arranged into the granularity of one record per payee/payer relationship, per income source, per year. The income_tax_yr table orders the monthly data into tax years, beginning with the 2000 tax year as the first records of the EMS table begin in April 1999. Thus, when referring to month 1 of this tax year table, this means the month of April.e.g. inc_tax_yr_year_nbr = 2000, refers to the period April 1999 to March 2000. How is the table built?The income_tax_yr table is built from the income_cal_yr table by reordering records into the tax year format from the calendar year format. Then the major difference between the two tables is implemented: the population widens in the income_tax_yr table by:1) Adding Sole Traders receiving self-employed income from the IR3Inserts records from the IR3 table where the IR3 net profit amount <> 0 for the Payer=Payee by year. The IDI assigns an income source code of ‘S00’ for these records on the income_tax_yr table.2) Adding those who received a rental income from the IR3Inserts records from the IR3 table where the IR3 net rents amount <> 0 for the Payer=Payee by year. The IDI assigns an income source code of ‘S03’ for these records on the income_tax_yr table.3) Adding those who received company director/shareholder income from the IR4SInserts records from the IR4S onto the income_tax_yr table where the Payee of the Payee – Payer relationship on the IR4S record is an individual (by looking at the ird_customers table), and where the ir_ir4_tot_sholder_sal_809_amt variable is <>0. The IDI assigns an income source code of ‘S03’ for these records on the income_tax_yr table.4) Adding those who received partnership income from the IR20Inserts records from the IR20, where the payee of the IR20 payee-payer relationship has an IR3 record, and the IR3 record is for the same period and has an IR3 partnership income amount of <>0, and the payee is confirmed as being an individual on the ird_customers table. The IDI assigns an income source code of ‘P00’ for these records on the income_tax_yr table.As the IR3, IR4S and IR20 tax forms relate to the entire tax year, the income amounts are inserted into the inc_tax_yr_tot_yr_amt variable. There are no monthly break downs. Summary table Variable NamePrimary KeyMandatoryFormatinc_tax_yr_year_nbrYNot null4Ninc_tax_yr_snz_uidYNot null8Ninc_tax_yr_snz_ird_uidNot null8Ninc_tax_yr_snz_employer_ird_uidYNot null8Ninc_tax_yr_income_source_codeYNot null3Ainc_tax_yr_withholding_type_codeNot null1Ainc_tax_yr_mth_01_amtNot null15Ninc_tax_yr_mth_02_amtNot null15Ninc_tax_yr_mth_03_amtNot null15Ninc_tax_yr_mth_04_amtNot null15Ninc_tax_yr_mth_05_amtNot null15Ninc_tax_yr_mth_06_amtNot null15Ninc_tax_yr_mth_07_amtNot null15Ninc_tax_yr_mth_08_amtNot null15Ninc_tax_yr_mth_09_amtNot null15Ninc_tax_yr_mth_10_amtNot null15Ninc_tax_yr_mth_11_amtNot null15Ninc_tax_yr_mth_12_amtNot null15Ninc_tax_yr_tot_yr_amtNot null15NDetailed information All variables are similar to the income_cal_yr table from section 4. Outlined here are the differences:_____________________________________ Table name: DATA.INCOME_TAX_YR_________________________________________ Variable Name: inc_tax_yr_year_nbrDefinition: This is now the tax year, therefore relating to the month beginning April and the month ending in March the following year.Format: Numeric, 4NName of classification: Notes: _______________________________________ Variable Name: inc_tax_yr_income_source_codeDefinition: Defines the type of income the record represents. This includes four additional income sources as defined in section 5.1, e.g.:‘W&S’ Wages and salaries ‘BEN’ Benefit payments from the Ministry of Social Development (MSD)‘CLM’ Accident Compensation Corporation (ACC) payments‘PEN’ Pension payments from MSD‘PPL’ Paid parental leave payments from MSD‘STU’ Student allowance payments from MSD‘IRD’ IRD number used by Inland Revenue‘TPR’ IRD number used by Ministry of Education – teacher payroll‘SNZ’ IRD number used by Statistics NZ. ‘C01’ Company director/shareholder receiving PAYE deducted income ‘C02’ Company director/shareholder receiving WHT deducted income ‘P01’ Partner receiving PAYE deducted income ‘P02’ Partner receiving withholding tax deducted income ‘S01’ Sole Trader receiving PAYE deducted income‘S02’ Sole Trader receiving withholding tax deducted income ‘P00’ Partnership income from the IR20 ‘C00’ Director/shareholder income from the IR4S ‘S00’ Sole Trader income from the IR3‘S03’ Rental income from the IR3Format: Character, 3AName of classification: Notes: _______________________________________ Variable Name: inc_tax_yr_mth_01_amtDefinition: These monthly variables now relate to the tax year. Hence this now equals the amount of income that was recorded in the first month of tax year corresponding to the year in year_nbr, i.e. April. Format: Numeric, 15NName of classification: Notes: _______________________________________ Variable Name: inc_tax_yr_tot_yr_amtDefinition: The annual sum of income across all tax months for the income source for that tax year, i.e. sum (inc_tax_yr_mth_01_amt – inc_tax_yr_mth_12_amt). This variable also holds annual totals of income sources ‘S00’, ‘P00’, ‘C00’ and ‘S03’.Format: Numeric, 15NName of classification: Notes: _______________________________________ Data Dictionary for the DATA.INCOME_CAL_YR_SUMMARY table and DATA.INCOME_TAX_YR_SUMMARY table.Dataset Description The income_cal_yr_summary and income_tax_yr_summary tables are effectively the income_cal_yr and income_tax_yr tables transformed into a differently granularity. Instead of there being one record per individual, per income source, per year, there is now one record per individual per year. Therefore, each record contains all income sources that a person may receive. The populations between the income_cal_yr and income_cal_yr_summary tables are identical, as is the case for the tax year tables. Summary table The following table outlines all variables present on the income_cal_yr_summary table with their formats. It also includes the four additional variables present on the income_tax_yr_summary table.Variable NamePrimary KeyMandatoryFormat[inc_cal_yr_sum_year_nbr]YY4N[snz_uid]YY8N[snz_ird_uid]Y8N[inc_cal_yr_sum_WAS_tot_amt]15N[inc_cal_yr_sum_WHP_tot_amt]15N[inc_cal_yr_sum_BEN_tot_amt]15N[inc_cal_yr_sum_ACC_tot_amt]15N[inc_cal_yr_sum_PEN_tot_amt]15N[inc_cal_yr_sum_PPL_tot_amt]15N[inc_cal_yr_sum_STU_tot_amt]15N[inc_tax_yr_sum_C00_tot_amt]Only on income_tax_yr_summary15N[inc_cal_yr_sum_C01_tot_amt]15N[inc_cal_yr_sum_C02_tot_amt]15N[inc_tax_yr_sum_P00_tot_amt]Only on income_tax_yr_summary15N[inc_cal_yr_sum_P01_tot_amt]15N[inc_cal_yr_sum_P02_tot_amt]15N[inc_tax_yr_sum_S00_tot_amt]Only on income_tax_yr_summary15N[inc_cal_yr_sum_S01_tot_amt]15N[inc_cal_yr_sum_S02_tot_amt]15N[inc_tax_yr_sum_S03_tot_amt]Only on income_tax_yr_summary15N[inc_cal_yr_sum_all_srces_tot_amt]15N[inc_cal_yr_sum_WAS_mth_01_amt]15N[inc_cal_yr_sum_WAS_mth_02_amt]15N[inc_cal_yr_sum_WAS_mth_03_amt]15N[inc_cal_yr_sum_WAS_mth_04_amt]15N[inc_cal_yr_sum_WAS_mth_05_amt]15N[inc_cal_yr_sum_WAS_mth_06_amt]15N[inc_cal_yr_sum_WAS_mth_07_amt]15N[inc_cal_yr_sum_WAS_mth_08_amt]15N[inc_cal_yr_sum_WAS_mth_09_amt]15N[inc_cal_yr_sum_WAS_mth_10_amt]15N[inc_cal_yr_sum_WAS_mth_11_amt]15N[inc_cal_yr_sum_WAS_mth_12_amt]15N[inc_cal_yr_sum_WHP_mth_01_amt]15N[inc_cal_yr_sum_WHP_mth_02_amt]15N[inc_cal_yr_sum_WHP_mth_03_amt]15N[inc_cal_yr_sum_WHP_mth_04_amt]15N[inc_cal_yr_sum_WHP_mth_05_amt]15N[inc_cal_yr_sum_WHP_mth_06_amt]15N[inc_cal_yr_sum_WHP_mth_07_amt]15N[inc_cal_yr_sum_WHP_mth_08_amt]15N[inc_cal_yr_sum_WHP_mth_09_amt]15N[inc_cal_yr_sum_WHP_mth_10_amt]15N[inc_cal_yr_sum_WHP_mth_11_amt]15N[inc_cal_yr_sum_WHP_mth_12_amt]15N[inc_cal_yr_sum_BEN_mth_01_amt]15N[inc_cal_yr_sum_BEN_mth_02_amt]15N[inc_cal_yr_sum_BEN_mth_03_amt]15N[inc_cal_yr_sum_BEN_mth_04_amt]15N[inc_cal_yr_sum_BEN_mth_05_amt]15N[inc_cal_yr_sum_BEN_mth_06_amt]15N[inc_cal_yr_sum_BEN_mth_07_amt]15N[inc_cal_yr_sum_BEN_mth_08_amt]15N[inc_cal_yr_sum_BEN_mth_09_amt]15N[inc_cal_yr_sum_BEN_mth_10_amt]15N[inc_cal_yr_sum_BEN_mth_11_amt]15N[inc_cal_yr_sum_BEN_mth_12_amt]15N[inc_cal_yr_sum_ACC_mth_01_amt]15N[inc_cal_yr_sum_ACC_mth_02_amt]15N[inc_cal_yr_sum_ACC_mth_03_amt]15N[inc_cal_yr_sum_ACC_mth_04_amt]15N[inc_cal_yr_sum_ACC_mth_05_amt]15N[inc_cal_yr_sum_ACC_mth_06_amt]15N[inc_cal_yr_sum_ACC_mth_07_amt]15N[inc_cal_yr_sum_ACC_mth_08_amt]15N[inc_cal_yr_sum_ACC_mth_09_amt]15N[inc_cal_yr_sum_ACC_mth_10_amt]15N[inc_cal_yr_sum_ACC_mth_11_amt]15N[inc_cal_yr_sum_ACC_mth_12_amt]15N[inc_cal_yr_sum_PEN_mth_01_amt]15N[inc_cal_yr_sum_PEN_mth_02_amt]15N[inc_cal_yr_sum_PEN_mth_03_amt]15N[inc_cal_yr_sum_PEN_mth_04_amt]15N[inc_cal_yr_sum_PEN_mth_05_amt]15N[inc_cal_yr_sum_PEN_mth_06_amt]15N[inc_cal_yr_sum_PEN_mth_07_amt]15N[inc_cal_yr_sum_PEN_mth_08_amt]15N[inc_cal_yr_sum_PEN_mth_09_amt]15N[inc_cal_yr_sum_PEN_mth_10_amt]15N[inc_cal_yr_sum_PEN_mth_11_amt]15N[inc_cal_yr_sum_PEN_mth_12_amt]15N[inc_cal_yr_sum_PPL_mth_01_amt]15N[inc_cal_yr_sum_PPL_mth_02_amt]15N[inc_cal_yr_sum_PPL_mth_03_amt]15N[inc_cal_yr_sum_PPL_mth_04_amt]15N[inc_cal_yr_sum_PPL_mth_05_amt]15N[inc_cal_yr_sum_PPL_mth_06_amt]15N[inc_cal_yr_sum_PPL_mth_07_amt]15N[inc_cal_yr_sum_PPL_mth_08_amt]15N[inc_cal_yr_sum_PPL_mth_09_amt]15N[inc_cal_yr_sum_PPL_mth_10_amt]15N[inc_cal_yr_sum_PPL_mth_11_amt]15N[inc_cal_yr_sum_PPL_mth_12_amt]15N[inc_cal_yr_sum_STU_mth_01_amt]15N[inc_cal_yr_sum_STU_mth_02_amt]15N[inc_cal_yr_sum_STU_mth_03_amt]15N[inc_cal_yr_sum_STU_mth_04_amt]15N[inc_cal_yr_sum_STU_mth_05_amt]15N[inc_cal_yr_sum_STU_mth_06_amt]15N[inc_cal_yr_sum_STU_mth_07_amt]15N[inc_cal_yr_sum_STU_mth_08_amt]15N[inc_cal_yr_sum_STU_mth_09_amt]15N[inc_cal_yr_sum_STU_mth_10_amt]15N[inc_cal_yr_sum_STU_mth_11_amt]15N[inc_cal_yr_sum_STU_mth_12_amt]15N[inc_cal_yr_sum_C01_mth_01_amt]15N[inc_cal_yr_sum_C01_mth_02_amt]15N[inc_cal_yr_sum_C01_mth_03_amt]15N[inc_cal_yr_sum_C01_mth_04_amt]15N[inc_cal_yr_sum_C01_mth_05_amt]15N[inc_cal_yr_sum_C01_mth_06_amt]15N[inc_cal_yr_sum_C01_mth_07_amt]15N[inc_cal_yr_sum_C01_mth_08_amt]15N[inc_cal_yr_sum_C01_mth_09_amt]15N[inc_cal_yr_sum_C01_mth_10_amt]15N[inc_cal_yr_sum_C01_mth_11_amt]15N[inc_cal_yr_sum_C01_mth_12_amt]15N[inc_cal_yr_sum_C02_mth_01_amt]15N[inc_cal_yr_sum_C02_mth_02_amt]15N[inc_cal_yr_sum_C02_mth_03_amt]15N[inc_cal_yr_sum_C02_mth_04_amt]15N[inc_cal_yr_sum_C02_mth_05_amt]15N[inc_cal_yr_sum_C02_mth_06_amt]15N[inc_cal_yr_sum_C02_mth_07_amt]15N[inc_cal_yr_sum_C02_mth_08_amt]15N[inc_cal_yr_sum_C02_mth_09_amt]15N[inc_cal_yr_sum_C02_mth_10_amt]15N[inc_cal_yr_sum_C02_mth_11_amt]15N[inc_cal_yr_sum_C02_mth_12_amt]15N[inc_cal_yr_sum_P01_mth_01_amt]15N[inc_cal_yr_sum_P01_mth_02_amt]15N[inc_cal_yr_sum_P01_mth_03_amt]15N[inc_cal_yr_sum_P01_mth_04_amt]15N[inc_cal_yr_sum_P01_mth_05_amt]15N[inc_cal_yr_sum_P01_mth_06_amt]15N[inc_cal_yr_sum_P01_mth_07_amt]15N[inc_cal_yr_sum_P01_mth_08_amt]15N[inc_cal_yr_sum_P01_mth_09_amt]15N[inc_cal_yr_sum_P01_mth_10_amt]15N[inc_cal_yr_sum_P01_mth_11_amt]15N[inc_cal_yr_sum_P01_mth_12_amt]15N[inc_cal_yr_sum_P02_mth_01_amt]15N[inc_cal_yr_sum_P02_mth_02_amt]15N[inc_cal_yr_sum_P02_mth_03_amt]15N[inc_cal_yr_sum_P02_mth_04_amt]15N[inc_cal_yr_sum_P02_mth_05_amt]15N[inc_cal_yr_sum_P02_mth_06_amt]15N[inc_cal_yr_sum_P02_mth_07_amt]15N[inc_cal_yr_sum_P02_mth_08_amt]15N[inc_cal_yr_sum_P02_mth_09_amt]15N[inc_cal_yr_sum_P02_mth_10_amt]15N[inc_cal_yr_sum_P02_mth_11_amt]15N[inc_cal_yr_sum_P02_mth_12_amt]15N[inc_cal_yr_sum_S01_mth_01_amt]15N[inc_cal_yr_sum_S01_mth_02_amt]15N[inc_cal_yr_sum_S01_mth_03_amt]15N[inc_cal_yr_sum_S01_mth_04_amt]15N[inc_cal_yr_sum_S01_mth_05_amt]15N[inc_cal_yr_sum_S01_mth_06_amt]15N[inc_cal_yr_sum_S01_mth_07_amt]15N[inc_cal_yr_sum_S01_mth_08_amt]15N[inc_cal_yr_sum_S01_mth_09_amt]15N[inc_cal_yr_sum_S01_mth_10_amt]15N[inc_cal_yr_sum_S01_mth_11_amt]15N[inc_cal_yr_sum_S01_mth_12_amt]15N[inc_cal_yr_sum_S02_mth_01_amt]15N[inc_cal_yr_sum_S02_mth_02_amt]15N[inc_cal_yr_sum_S02_mth_03_amt]15N[inc_cal_yr_sum_S02_mth_04_amt]15N[inc_cal_yr_sum_S02_mth_05_amt]15N[inc_cal_yr_sum_S02_mth_06_amt]15N[inc_cal_yr_sum_S02_mth_07_amt]15N[inc_cal_yr_sum_S02_mth_08_amt]15N[inc_cal_yr_sum_S02_mth_09_amt]15N[inc_cal_yr_sum_S02_mth_10_amt]15N[inc_cal_yr_sum_S02_mth_11_amt]15N[inc_cal_yr_sum_S02_mth_12_amt]15NDetailed information _______________________________________ Variable Name: snz_uid Definition: The IDI global unique identifier relating to the individual. If a link has been generated for this individual across collections, then this individual will have the same snz_uid across collections. Each refresh, all links are reprocessed, therefore, the snz_uid is refresh specific, and will change between archives. This differs to snz_ird_uid below.Format: Numeric, 8NName of classification: Notes: _______________________________________ Variable Name: snz_ird_uidDefinition: The encrypted IRD number of the individual. This is consistent across time, and can be used between refreshes. This differs to snz_uid (above), as encryption is consistent over time, whereas snz_uid is not.Format: Numeric, 8NName of classification: Notes: _______________________________________Variable Name: inc_cal/tax_yr_sum_WAS_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from Wages and Salaries.Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_WHP_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from withholding payments.Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_BEN_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from benefitsFormat: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_ACC_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from ACC paymentsFormat: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_PEN_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from Pension payments.Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_PPL_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from Paid parental leave paymentsFormat: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_STU_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from Student Allowance payments.Format: Numeric, 15A_______________________________________ Variable Name: inc_tax_yr_sum_C00_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from director/shareholders receiving company income. Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_C01_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from company director/shareholders paying themselves a wage and salary.Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_C02_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from company director/shareholders paying themselves withholding paymentsFormat: Numeric, 15A_______________________________________ Variable Name: inc_/tax_yr_sum_P01_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from partners receiving partnership income.Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_P01_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from partners paying themselves a wage and salaryFormat: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_P02_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from partners paying themselves withholding payments.Format: Numeric, 15A_______________________________________ Variable Name: inc_tax_yr_sum_S00_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from sole traders receiving net profits. Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_S01_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from sole traders paying themselves a wage and salaryFormat: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_S02_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from sole traders paying themselves withholding paymentsFormat: Numeric, 15A_______________________________________ Variable Name: inc_tax_yr_sum_S03_tot_amtDefinition: The total amount of income received by the individual for the specific tax or calendar year from sole traders receiving rental income.Format: Numeric, 15A_______________________________________ Variable Name: inc_cal/tax_yr_sum_all_srces_tot_amtDefinition: The total amount of income received by the individual for the tax/calendar year from all sources. Format: Numeric, 15A_______________________________________ The next 150-odd columns have the amount of income receiving from each income source (where applicable), broken down by month. Data Dictionary for the DATA.INCOME_PBN_ENT tableDataset Description This income_pbn_ent table holds all allocation information sourced from the LEED quarterly production system. It has the granularity of one record per individual job relationship, per month per pbn. This table is designed to be used in conjunction with the other tables in this document, as well as the other Inland Revenue tax tables to be able to attach work location data to individuals. As the income_pbn_ent is monthly, it retains the granularity of the LEED allocation system.Summary table Variable NamePrimary KeyMandatoryFormatsnz_uidYNot null8Nsnz_ird_uidNot null8Nsnz_employer_ird_uidYNot null8Ninc_pbn_dim_month_keyYNot null6Ninc_pbn_pbn_nbrYNot null10Ainc_pbn_enterprise_nbrNot null10ADetailed information _______________________________________ Variable Name: snz_uid Definition: The IDI global unique identifier relating to the individual. If a link has been generated for this individual across collections, then this individual will have the same snz_uid across collections. Each refresh, all links are reprocessed, therefore, the snz_uid is refresh specific, and will change between archives. This differs to snz_ird_uid below.Format: Numeric, 8NName of classification: Notes: _______________________________________ Variable Name: snz_ird_uidDefinition: The encrypted IRD number of the individual. This is consistent across time, and can be used between refreshes. This differs to snz_uid (above), as encryption is consistent over time, whereas snz_uid is not.Format: Numeric, 8NName of classification: Notes: _______________________________________Variable Name: snz_employer_ird_uidDefinition: The encrypted IRD number of the employer. This is consistent across time, and can be used between refreshes. Format: Numeric, 15A_______________________________________ Variable Name: inc_pbn_dim_month_keyDefinition: The total amount of income received by the individual for the specific tax or calendar year from withholding payments.Format: Numeric, 15A_______________________________________ Variable Name: inc_pbn_pbn_nbrDefinition: Permanent business numberFormat: Numeric, 15A_______________________________________ Variable Name: inc_pbn_ent_nbrDefinition: enterprise number.Format: Numeric, 15A_______________________________________ ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download