Table/Column |
Type |
nulls |
Description/notes |
extract_acct_submit |
account submission |
|
Account submission history for a main registered charity |
regno |
integer |
null |
registered number of a charity |
submit_date |
smalldatetime |
null |
date submitted |
arno |
char(4) |
not null |
annual return mailing cycle code |
fyend |
varchar(4) |
null |
Charity’s financial year end date (may be blank) |
|
|
|
|
extract_aoo_ref |
area of operation reference |
|
Description of an area of operation |
aootype |
varchar(10) |
not null |
A (wide), B (LA), C (GLA/met county), D (country), E (continent) |
aookey |
integer |
not null |
Values if aootype=A are: 1 England/Wales; 2 England; 3 Wales; 4 London. |
aooname |
varchar(255) |
not null |
name of an area of operation |
aoosort |
varchar(100) |
not null |
for searches, “City of” removed from aooname |
welsh |
varchar(1) |
not null |
Flag: “Y” or blank |
master |
integer |
null |
may be blank. If aootype=D then holds continent; if aootype=B then holds GLA/met county |
|
|
|
|
extract_ar_submit |
AR submission |
|
Annual Return submission history for a main registered charity |
regno |
integer |
null |
registered number of a charity |
arno |
char(4) |
not null |
annual return mailing cycle code |
submit_date |
smalldatetime |
null |
date submitted |
|
|
|
|
extract_charity |
Charity |
|
One per registered/removed charity (including subsidiaries) |
regno |
integer |
null |
registered number of a charity |
subno |
integer |
null |
subsidiary number of a charity (may be 0 for main/group charity) |
name |
varchar(150) |
null |
main name of the charity |
orgtype |
varchar(10) |
null |
R (registered) or RM (removed) |
gd |
nvarchar(max) |
null |
Description of Governing Document |
aob |
varchar(max) |
null |
area of benefit – may not be defined |
aob_defined |
interger |
null |
area of benefit defined by Governing Document (T/F) |
nhs |
varchar(1) |
not null |
NHS charity (T/F) |
ha_no |
integer |
null |
Housing Association number |
corr |
varchar(255) |
null |
Charity correspondent name. NULL if charity status is “Removed” |
add1 |
varchar(35) |
null |
address line of charity’s correspondent. NULL if charity status is “Removed” |
add2 |
varchar(35) |
null |
address line of charity’s correspondent. NULL if charity status is “Removed” |
add3 |
varchar(35) |
null |
address line of charity’s correspondent. NULL if charity status is “Removed” |
add4 |
varchar(35) |
null |
address line of charity’s correspondent. NULL if charity status is “Removed” |
add5 |
varchar(35) |
null |
address line of charity’s correspondent. NULL if charity status is “Removed” |
postcode |
varchar(8) |
null |
postcode of charity’s correspondent. NULL if charity status is “Removed” |
phone |
varchar(400) |
null |
telephone of charity’s correspondent. NULL if charity status is “Removed” |
fax |
integer |
null |
fax of charity’s correspondent. NULL if charity status is “Removed” |
|
|
|
|
extract_charity_aoo |
area of operation |
|
Area of operation for a charity |
regno |
integer |
null |
registered number of a charity |
aootype |
varchar(10) |
not null |
A B or D |
aookey |
integer |
not null |
up to three digits |
welsh |
varchar(1) |
not null |
Flag: “Y” or blank |
master |
integer |
null |
may be blank. If aootype=D then holds continent; if aootype=B then holds GLA/met county |
|
|
|
|
extract_class |
classification table |
|
classification details |
regno |
integer |
null |
registered number of a charity |
class |
varchar(10) |
not null |
classification code for a charity (multiple occurrences possible) |
|
|
|
|
extract_class_ref |
reference table |
|
classification reference |
classno |
varchar(10) |
not null |
classification code |
classtext |
varchar(65) |
null |
description of a classification code |
|
|
|
|
extract_financial |
financial history |
|
Financial history for a main registered charity |
regno |
integer |
null |
registered number of a charity |
fystart |
smalldatetime |
null |
Charity’s financial year start date |
fyend |
smalldatetime |
null |
Charity’s financial year end date |
income |
numeric(12,0) |
null |
|
expend |
numeric(12,0) |
null |
|
|
|
|
|
extract_main_charity |
main charity table |
|
One for every main registered charity |
regno |
integer |
null |
registered number of a charity |
coyno |
varchar(50) |
null |
company registration number |
trustees |
varchar(1) |
not null |
trustees incorporated (T/F) |
fyend |
varchar(4) |
null |
Financial year end |
welsh |
varchar(1) |
not null |
requires correspondence in both Welsh & English (T/F) |
incomedate |
smalldatetime |
null |
date for latest gross income (blank if income is an estimate) |
income |
numeric(12,0) |
null |
|
grouptype |
varchar(3) |
null |
may be blank |
email |
varchar(400) |
null |
email address |
web |
varchar(400) |
null |
website address |
|
|
|
|
extract_name |
name table |
|
Main old and working names for a charity |
regno |
integer |
null |
registered number of a charity |
subno |
integer |
null |
subsidiary number of a charity (may be 0 for main/group charity) |
nameno |
integer |
not null |
number identifying a charity name |
name |
varchar(255) |
null |
name of a charity (multiple occurrences possible) |
|
|
|
|
extract_objects |
objects table |
|
charity objects. Records with sequence numbers |
regno |
integer |
null |
registered number of a charity |
subno |
integer |
null |
subsidiary number of a charity (may be 0 for main/group charity) |
seqno |
varchar(4) |
null |
Sequence number (in practice 0-20) |
object |
varchar(max) |
null |
Description of objects of a charity |
|
|
|
|
extract_partb |
Annual Return Detail table |
|
Row for each AR for each registered main charity |
regno |
integer |
null |
registered number of a charity |
artype |
char(4) |
not null |
annual return mailing cycle code |
fystart |
datetime |
not null |
Charity’s financial year start date |
fyend |
datetime |
not null |
Charity’s financial year end date |
inc_leg |
varchar(max) |
null |
Legacies |
inc_end |
varchar(max) |
null |
Endowments |
inc_vol |
varchar(max) |
null |
Voluntary Income |
inc_fr |
varchar(max) |
null |
Activities generating funds |
inc_char |
varchar(max) |
null |
Charitable activities |
inc_invest |
varchar(max) |
null |
Investment income |
inc_other |
varchar(max) |
null |
Other Income |
inc_total |
varchar(max) |
null |
Total Incoming resources |
invest_gain |
varchar(max) |
null |
Gains/loss on investments |
asset_gain |
varchar(max) |
null |
Revaluations of fixed assets |
pension_gain |
varchar(max) |
null |
Gains/loss on Pension Fund |
exp_vol |
varchar(max) |
null |
Voluntary income costs |
exp_trade |
varchar(max) |
null |
Fundraising Trading costs |
exp_invest |
varchar(max) |
null |
Investment Management costs |
exp_grant |
varchar(max) |
null |
Grants to institutions |
exp_charble |
varchar(max) |
null |
Charitable Activities costs |
exp_gov |
varchar(max) |
null |
Governance costs |
exp_other |
varchar(max) |
null |
Other resources expended |
exp_total |
varchar(max) |
null |
Total Resources expended |
exp_support |
varchar(max) |
null |
Support costs |
exp_dep |
varchar(max) |
null |
Depreciation |
reserves |
varchar(max) |
null |
Reserves |
asset_open |
varchar(max) |
null |
Total fixed assets (at start of year) |
asset_close |
varchar(max) |
null |
Total fixed assets |
fixed_assets |
varchar(max) |
null |
Fixed Investments Assets |
open_assets |
varchar(max) |
null |
Fixed Investments Assets (start of year) |
invest_assets |
varchar(max) |
null |
Current Investment Assets |
cash_assets |
varchar(max) |
null |
Cash |
current_assets |
varchar(max) |
null |
Total Current Assets |
credit_1 |
varchar(max) |
null |
Creditors – within one year |
credit_long |
varchar(max) |
null |
Creditors – Long Term/Provision |
pension_assets |
varchar(max) |
null |
Pension Assets/Liabilities |
total_assets |
varchar(max) |
null |
Total Net Assets/Liabilities |
funds_end |
varchar(max) |
null |
Endowment funds |
funds_restrict |
varchar(max) |
null |
Restricted funds |
funds_unrestrict |
varchar(max) |
null |
Unrestricted funds |
funds_total |
varchar(max) |
null |
Total funds |
employees |
varchar(max) |
null |
Employees |
volunteers |
varchar(max) |
null |
Volunteers |
cons_acc |
varchar(max) |
null |
Consolidated accounts (True/False) |
charity_acc |
varchar(max) |
null |
Charity only accounts (True/False) |
|
|
|
|
extract_registration |
registration table |
|
registration details & removals |
regno |
integer |
null |
registered number of a charity |
subno |
integer |
null |
subsidiary number of a charity (may be 0 for main/group charity) |
regdate |
smalldatetime |
null |
date of registration for a charity |
remdate |
smalldatetime |
null |
Removal date of a charity – Blank for Registered Charities |
remcode |
char(3) |
null |
Register removal reason code |
|
|
|
|
extract_remove_ref |
removal reference |
|
Reference for remcode in extract_registration |
code |
char(3) |
null |
Register removal reason code |
text |
char(25) |
null |
Removal reason description |
|
|
|
|
extract_trustee |
trustee table |
|
trustee names |
regno |
integer |
null |
registered number of a charity |
trustee |
varchar(255) |
null |
Name of a charity trustee |
|
|
|
|