Download original attachment
(Word document)
This is an HTML version of an attachment to the Freedom of Information request
'User data for NRUC'.
(Word document)
June 2009
Our Ref: FOI/req01 |
59 ½ Southwark Street London SE1 0AL
Tel: 0207 934 9656/9653/9833 Fax: 0207 934 9660 |
|
|
Dear Mr Blair,
Thank you for your e-mail received on 3rd May 2009.
In response to your request - `User data for NRUC', please find below our responses.
1. Which public authorities have at anytime been a subscriber or registered user of NRUC
Please see Annex A, which represents the total list of subscribers of the NRUC system.
1.2 Which public authorities who are, as of 01 05 09, subscribers or registered users of NRUC
Please see Annex A, showing users as at 1st May 2009.
1.3 Which public authorities, as of 01 05 09, subscription or registration remains un-paid or lapsed
NRUC does not currently require public authorities to pay for individual use of NRUC.
2. How many social workers are, as of 01 05 09, active users
NRUC users are nominated by each local authority, and it is the responsibility of the individual local authority to decide which employees will use NRUC.
3. The term 'active users' is used to mean those individuals whose
NRUC account has been approved, activated and they have been
provided with a unique user name and the account has been used in
the last 3 months and or has not been deactivated for whatever
reason. How many active user there are, as of 01 05 09
There are 517 active users as of 1st May 2009.
4. How many active users are from local authorities
There are 462 active users from local authorities.
5. How many active users are from the Home Office or its related agencies such as the UK Borders Agency
There are 47 active users from the Home Office or its related agencies such as the UK Borders Agency.
6. How many active users of NRUC are Lead Officers, as of 01 05 09
There are 106 active Lead Officers as of 1st May 2009.
6.1 How many additional users of NRUC there are, as of 01 05 09
There are 411 additional users as of 1st May 2009.
7. Please email me an electronic copy of the following information: Your Service Level Agreements, as mentioned herein: http://www.nruc.gov.uk/news.htm
Please see Annex B.
8. Details of any non public authority organisations that have user access to NRUC
Refugee Council
9. Which public authorities have access to NRUC
Please see Annex A.
10. Full details of which specific data fields exist for the purpose of inputting data relating to each individual child record
Please see Annex C.
We trust the information provided has met the requirements of your request.
Yours sincerely
Grace Ashaye
LASC Manager
Annex Index
Annex A - Public Authority Subscribers to NRUC page 4
Annex B - Service Level Agreement page 9
Annex C - Data Fields page 12
Annex A
Which public authorities have at anytime been a subscriber or registered user of NRUC
Non Regional Authorities
UKBA
East of England Regional Assembly
Regional Intelligence Unit for the South East
East of England
Bedfordshire County Council
Cambridgeshire County Council
Essex County Council
Hertfordshire County Council
Luton Borough Council
Norfolk County Council
Peterborough City Council
Southend-on-Sea Borough Council
Suffolk County Council
Thurrock Council
West Midlands
Birmingham City Council
Coventry City Council
Dudley Metropolitan Borough Council
Herefordshire County Council
Sandwell Metropolitan Borough Council
Shropshire County Council
Solihull Metropolitan Borough Council
Staffordshire County Council
Stoke-on-Trent City Council
Telford and Wrekin Borough Council
Walsall Metropolitan Borough Council
Warwick District Council
Warwickshire County Council
Wolverhampton City Council
Worcestershire County Council
East Midlands
Derbyshire County Council
Derby City Council
Leicester City Council
Leicestershire County Council
Lincolnshire County Council
Northamptonshire County Council
Nottingham City Council
Nottinghamshire County Council
Rutland County Council
London
Corporation of London
London Borough of Barking & Dagenham
London Borough of Barnet
London Borough of Bexley
London Borough of Brent
London Borough of Bromley
London Borough of Camden
London Borough of Croydon
London Borough of Ealing
London Borough of Enfield
London Borough of Greenwich
London Borough of Hackney
London Borough of Hammersmith & Fulham
London Borough of Haringey
London Borough of Harrow
London Borough of Havering
London Borough of Hillingdon
London Borough of Hounslow
London Borough of Islington
London Borough of Kensington & Chelsea
London Borough of Lambeth
London Borough of Lewisham
London Borough of Merton
London Borough of Newham
London Borough of Redbridge
London Borough of Richmond Upon Thames
London Borough of Southwark
London Borough of Sutton
London Borough of Tower Hamlets
London Borough of Wandsworth
Royal Borough of Kingston
Waltham Forest Council
Westminster City Council
North West
Blackburn & Darwen Borough Council
Blackpool Borough Council
Bolton Metropolitan Borough Council
Bury Metropolitan Borough Council
Cheshire County Council
Halton Borough Council
Knowsley Metropolitan Borough Council
Lancashire County Council
Liverpool City Council
Manchester City Council
Oldham Metropolitan Borough Council
Oldham Metropolitan Borough Council
Rochdale Metropolitan Borough Council
Salford City Council
St Helens Metropolitan Borough Council
Stockport Metropolitan Borough Council
Tameside Metropolitan Borough Council
Trafford Metropolitan Borough Council
Trafford Metropolitan Borough Council
Wigan Metropolitan Borough Council
Wirral Metropolitan Borough Council
North East
Darlington Borough Council
Durham County Council
Gateshead Metropolitan Borough Council
Hartlepool Borough Council
Middlesbrough Borough Council
Newcastle-upon-Tyne City Council
North Tyneside Metropolitan Borough Council
Northumberland County Council
Redcar and Cleveland Borough Council
South Tyneside Metropolitan Borough Council
Stockton on Tees Borough Council
Sunderland City Council
South West
Bath and North East Somerset Council
Bournemouth Borough Council
Bristol City Council
Cornwall County Council
Devon County Council
Dorset County Council
Exeter City Council (covered by Devon County Council)
Gloucestershire County Council
Isles of Scilly Council
North Somerset District Council
Plymouth City Council
Poole, Borough of
Salisbury District Council
Somerset County Council
South Gloucestershire Council
South Hams District Council
Swindon Borough Council
Teignbridge District Council
Torbay Borough Council
Wiltshire County Council
South East
Bracknell Forest Borough Council
Brighton and Hove City Council
Buckinghamshire County Council
East Sussex County Council
Gravesham
Hampshire County Council
Isle of Wight Council
Kent County Council
Medway Council
Milton Keynes Borough Council
Oxfordshire County Council
Portsmouth City Council
Reading Borough Council
Slough Borough Council
Southampton City Council
Surrey County Council
West Berkshire District Council
West Sussex County Council
Windsor and Maidenhead Royal Borough Council
Wokingham District Council
Yorkshire & Humberside
Bradford Metropolitan Borough Council
Calderdale Metropolitan Borough Council
East Riding of Yorkshire Council
Kingston-upon-Hull City Council
Kirklees Metropolitan Borough Council
Leeds City Council
North East Lincolnshire Council
North Lincolnshire Council
Rotherham Metropolitan Borough Council
Sheffield City Council
Wakefield Metropolitan District Council
York City Council
Scotland
Aberdeenshire Council
Angus Council
Clackmannanshire Council
East Lothian Council
City of Edinburgh Council
Fife Council
Glasgow City Council
North Lanarkshire Council
Renfrewshire Council
South Lanarkshire Council
West Lothian Council
Wales
Carmarthenshire County Council
Conwy County Borough Council
Flintshire County Council
Isle of Anglesey County Council
Neath Port Talbot County Borough Council
Powys County Council
Rhondda Cynon Taf County Borough Council
Torfaen County Borough Council
Vale of Glamorgan Council
Caerphilly County Borough Council
Monmouthshire County Council
Newport City Council
|
|
|
|
Annex B
Annex C
3 Table Definitions
This section will provide the detail of each table within the NRUC relational model. If a field requires access to another table, this table is provided within the `Clarification column'.
3.1 NRUC Child and NRUC Child Related Tables
This section provides details of the Child Table, being a core table to be used in many reports. Child related tables are also provided within this section. For example, Child Attachments and fields that require access to pick list values that are held within an associated table.
Child Table
This table enables access to Child Profile demographic information, as input within the Child. Add and Child. Edit screens
Fields |
Clarification |
UID |
The System ID for the Child |
DateCreated |
The date that the Child record was created |
DateLastEdited |
The date that the Child record was last modified |
CreatedBy |
The login ID of the user that created the Child record |
LastEditedBy |
The login ID of the user that last modified the Child record |
ID |
Unique NRUC Child ID |
NumberOfDependents |
The number of dependents that the Child has |
OtherBackgroundInformation |
Other Background Information |
Forename |
Child's Forename |
CIDPersonID |
The CID Child ID |
ImmigrationReferenceNumber |
Child's Immigration Reference Number |
NHSNumber |
Child's NHS Number |
NationalInsuranceNumber |
Child's National Insurance Number |
ASUReferenceNumber |
Child's ASU Reference Number |
HomeOfficeSummaryReport |
Any notes that need to be included within the Home Office Summary Report |
Forename |
Child's Forename |
Surname |
Child's Surname |
DateOfBirth |
Child's Date of Birth |
MaritalStatus |
Child's Marital Status. The numeric values within the MaritalStatus field are foreign keys that map to the Ordinal values held within the `MaritalStatusKind' table |
CountryOfOriginCode |
The unique code of the Child's Country of Birth |
CountryOfOriginDescription |
The corresponding description ot the Child's Country of Birth |
LanguageCode |
The unique code of the Child's Primary Language |
LanguageDescription |
The corresponding description of the Child's Primary Language |
NationalityCode |
The unique code of the Child's Nationality |
NationalityDescription |
The corresponding description of the Child's Nationality |
PlacingDepartment_UID_FK |
This field provides the Foreign Key to link to the Department UID within the Department table |
Child_PortReferenceNumber Table
This table enables access to information about the Child's Port Reference Number, as input within the Child Profile form
Fields |
Clarification |
ID |
The system ID of the Reference Number record |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
Value |
The Port Reference Number of the Child
|
Child_HomeOfficeReferenceNumber Table
This table enables access to information about the Child's Home Office Reference Number, as input within the Child Profile form.
Fields |
Clarification |
ID |
The system ID of the Reference Number record |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
Value |
The Home Office Reference Number of the Child
|
Child_NASSReferenceNumber Table
This table enables access to information about the Child's NASS Reference Number, as input within the Child Profile form.
Fields |
Clarification |
ID |
The system ID of the Reference Number record |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
Value |
The NASS Reference Number of the Child
|
Child_IFBCUFReferenceNumber Table
This table enables access to information about the Child's IFB CUF Reference Number, as input within the Child Profile form.
Fields |
Clarification |
ID |
The system ID of the Reference Number record |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
Value |
The IFB/ CUF Reference Number of the Child
|
3.1.6 ImmigrationDetails Table
This table enables access to Child Immigration Details information, as input within the Child Immigration Details screen.
Fields |
Clarification |
UID |
The unique system ID of the Immigration Details record |
DateCreated |
The date that the Immigration Details record was created |
DateLastEdited |
The date that the Immigration Details record was last modified |
CreatedBy |
The login ID of the user that created the Immigration Details record |
LastEditedBy |
The login ID of the user that last modified the Immigration Details record |
ArrivalDate |
The date that the Child arrived at the port |
PlaceOfEntry |
The place that the Child arrived at upon entry to the UK |
MeansOfTravelFromPlaceOfEntry |
How the Child travelled from the place of entry to their current location |
ImmigrationStatus |
The Home Office Immigration Status of the Child [as identified from the linked CID record]. The numeric values within the ImmigrationStatus field are foreign keys that map to the Ordinal values held within the `CaseOutcomeKind' table |
StatusDate |
The date that the Immigration Status was set [as identified from the linked CID record] |
RemovalOutcome |
The Removal Outcome of the Child, if applicable [as identified from the linked CID record]. The numeric values within the RemovalOutcome field are foreign keys that map to the Ordinal values held within the `RemovalOutcomeKind' table |
RemovalOutcomeDate |
The date that the Removal Outcome was set [as identified from the linked CID record] |
MadeAsylumClaim |
Whether or not the Child has made an asylum claim Pick List Values: True False |
HaveDLR |
Whether or not the Child has DLR Pick List Values: True False |
HaveILR |
Whether or not the Child has ILR Pick List Values: True False |
ArrivedWithOthers |
Whether or not the Child arrived with others Pick List Values: True False |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
3.1.7 ChildAttachment Table
This table enables access to Child Attachment information, as input within the Child Attachment add/ edit screen.
Fields |
Clarification |
UID |
The unique system ID of the Child Attachment record |
DateCreated |
The date that the Child Attachment record was created |
DateLastEdited |
The date that the Child Attachment record was last modified |
CreatedBy |
The login ID of the user that created the Child Attachmemt record |
LastEditedBy |
The login ID of the user that last modified the Child Attachment record |
ID |
Unique ID for the Child Attachment record |
DisplayImage |
Whether or not the attachment is a display image Pick List Values: True False |
Note |
Child Attachment Description |
Privacy |
Child Attachment. The numeric values within the Privacy field are foreign keys that map to the Ordinal values held within the `AttachmentPrivacyKind' table |
Status |
Child Attachment Status. The numeric values within the Status field map to the Ordinal values held within the `AttachmentStatusKind' table |
Title |
Child Attachment Title |
ContentType |
The mime type of the Child attachment |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
Team_UID_FK |
This field provides the Foreign Key to link to the Team UID within the Team table |
3.1.8 ChildContactInformation Table
This table enables the reporting upon details of the Child Contact Group Table, as input within the `Child Contacts' option.
Fields |
Clarification |
UID |
The unique system ID of the Child Contact group record |
DateCreated |
The date that the Child Contact record was created |
DateLastEdited |
The date that the Child Contact record was last modified |
CreatedBy |
The login ID of the user that created the Child Contact record |
LastEditedBy |
The login ID of the user that last modified the Child Contact record |
RelationshipToChild |
The Child contact's relationship to the Child. The numeric values within the RelationshipToChild field are foreign keys that map to the Ordinal values held within the `RelationshipKind' table |
IsResponsible |
Whether or not the contact is responsible for the Child Pick List Values: True False |
IsResident |
Whether or not the Child is resident with this contact Pick List Values: True False |
Title |
The Title of the Child Contact. The numeric values within the Title field are foreign keys that map to the Ordinal values held within the `TitleKind' table |
Surname |
The surname of the Child Contact |
Forename |
The forename of the Child Contact |
Job Title |
The job title of the Child Contact |
Department |
The department of the Child Contact |
Organisation |
The organisation name of the Child Contact |
FurtherInformation |
Further information about the contact group. For example, contact hours |
StartDate |
The date that the contact with this person started |
EndDate |
The date that the contact with this person ended |
IsPrimary |
Primary person to contact flag Pick List Values: True False |
Type |
The type of contact group. The numeric values within the Type field are foreign keys that map to the Ordinal values held within the `ContactGroupKind' table |
AddressLine2 |
The second line of the contact address |
AddressLine3 |
The third line of the contact address |
AdministrativeArea |
The Administrative Area [County] of the contact address |
PostCode |
The Postcode of the contact address |
PostTown |
The Post Town of the contact address |
PrimaryNameOrNumber |
The First Name or Number of the contact address |
Secondary NameOrNumber |
The Second Name or Number of the contact address |
Street |
The Street name of the contact address |
UPRN |
The Unique Property Reference Number of the contact address |
USRN |
The Unique Street Reference Number of the contact address |
EmailAddress |
The e-mail address associated with the contact group |
FaxNumber |
The fax number associated with the contact group |
TelephoneNumber |
The telephone number associated with the contact group |
MobileNumber |
The mobile number associated with the contact group |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
3.1.9 ChildPlacementContactInformation Table
This table enables the reporting upon details of Child Placements, as input within the `Child Placement Addresses' option.
Fields |
Clarification |
UID |
The unique system ID of the Child Placement Address group record |
DateCreated |
The date that the Child Placement Address record was created |
DateLastEdited |
The date that the Child Placement Address record was last modified |
CreatedBy |
The login ID of the user that created the Child Placement Address record |
LastEditedBy |
The login ID of the user that last modified the Child Placement Address record |
PlacementType |
The type of placement. The numeric values within the Placement Type field are foreign keys that map to the Ordinal values held within the `PlacementTypeKind' table |
IsIndependent |
Whether or not the Child lives independently Pick List Values: True False |
ServiceProvider |
The name of the service provider that provided the placement |
MainContact |
The name of the service provider contact |
ContactDetails |
The contact details of the service provider contact |
PlacementCharge |
The cost of the placement [to 2 decimal points] |
ChargeFrequency |
How often the cost is paid. The numeric values within the Frequency field are foreign keys that map to the Ordinal values held within the `FrequencyKind' table |
StartDate |
The date that the contact with this person started |
EndDate |
The date that the contact with this person ended |
IsPrimary |
Primary person to contact flag Pick List Values: True False |
Type |
The type of placement address. The numeric values within the Type field are foreign keys that map to the Ordinal values held within the `ContactGroupKind' table |
AddressLine2 |
The second line of the Placement address |
AddressLine3 |
The third line of the Placement address |
AdministrativeArea |
The Administrative Area [County] of the Placement address |
PostCode |
The Postcode of the Placement address |
PostTown |
The Post Town of the Placement address |
PrimaryNameOrNumber |
The First Name or Number of the Placement address |
Secondary NameOrNumber |
The Second Name or Number of the Placement address |
Street |
The Street name of the Placement address |
UPRN |
The Unique Property Reference Number of the Placement address |
USRN |
The Unique Street Reference Number of the Placement address |
EmailAddress |
The e-mail address associated with the Placement group |
FaxNumber |
The fax number associated with the Placement group |
TelephoneNumber |
The telephone number associated with the Placement group |
MobileNumber |
The mobile number associated with the Placement group |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
ChildContactInformation_UID_FK |
This field provides the Foreign Key to link to the ChildContactInformation. UID within the ChildContactInformation table |
3.1.10 Alias Table
This table enables the reporting upon details of Child Aliases, as input within the `Child Aliases' option.
Fields |
Clarification |
UID |
The unique system ID of the Child Alias record |
DateCreated |
The date that the Child Alias record was created |
DateLastEdited |
The date that the Child Alias record was last modified |
CreatedBy |
The login ID of the user that created the Child Alias record |
LastEditedBy |
The login ID of the user that last modified the Child Alias record |
Type |
The type of Alias. The numeric values within the Type field are foreign keys that map to the Ordinal values held within the `AliasTypeKind' table |
OtherType |
Futher definition of `Other' if this is the selected `Type' value |
Forename |
The Child's alias forename |
Surname |
The Child's alias surname |
DateOfBirthOther |
The Child's alias date of birth [non standard date format] |
PlaceOfBirth |
The Child's alias place of birth |
DateOfBirth |
The Child's alias date of birth |
MaritalStatus |
The Child's alias marital status. The numeric values within the MaritalStatus field are foreign keys that map to the Ordinal values held within the `MaritalStatusKind' table |
NationalityCode |
The Child's alias nationality Code |
NationalityDescription |
The Child's alias nationality Description [this will correspond with a Nationality Code] |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
3.1.11 SpecialCondition Table
This table enables the reporting upon details of Child Alerts and Disputes, as input via the Child `Alerts & Disputes' option:
Fields |
Clarification |
UID |
The unique system ID of the Child Special Condition record |
DateCreated |
The date that the Child Special Condition record was created |
DateLastEdited |
The date that the Child Special Condition record was last modified |
CreatedBy |
The login ID of the user that created the Child Special Condition record |
LastEditedBy |
The login ID of the user that last modified the Child Special Condition record |
SpecialCondition |
The Alert & Dispute type. The numeric values within the Special Condition field are foreign keys that map to the Ordinal values held within the `SpecialConditionKind' table |
Details |
Further details concerning the Alert/ Dispute type |
Severity |
The severity of the Alert/ Dispute. The numeric values within the Severity field are foreign keys that map to the Ordinal values held within the `ConditionSeverityKind' table |
DateLodged |
The date that the Alert/ Dispute was lodged |
ExpectedResolutionDate |
The date that the Alert/ Dispute is expected to be resolved |
DateResolved |
The date that the Alert/ Dispute was actually resolved |
DisputeOutcome |
A description of the Alert/ Dispute outcome |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
3.1.12 Language Table
This table enables the reporting upon other languages that the Child may speak, as input via the Child input form. You will need to use this table in conjunction with the `ChildOtherLanguageRelationship table, if you wish to report upon other languages spoken by a particular Child
Fields |
Clarification |
UID |
The unique system ID of the Child Language record |
DateCreated |
Not applicable |
DateLastEdited |
Not applicable |
CreatedBy |
Not applicable |
LastEditedBy |
Not applicable |
LanguageCode |
The language Code e.g. Eng. Please see Appendix One |
LanguageDescription |
The language description e.g. English. Please see Appendix One |
3.1.13 ChildOtherLanguageRelationship Table
This table enables the reporting upon the relationship between the Child and any languages that the Child may speak, other than their primary language, as input via the Child input form. You will need to use this table in conjunction with the `Child' and the `Language' table, if you wish to report upon the details of other languages spoken by a particular Child [i.e. the related language code and description]
Fields |
Clarification |
UID |
The unique system ID of the relationship record |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
OtherLanguage_UID_FK |
This field provides the Foreign Key to link to the Language UID within the Language table |
DateCreated |
The date that the relationship record was created |
DateLastEdited |
Not applicable |
CreatedBy |
The login ID of the user that created the Relationship record |
LastEditedBy |
Not applicable |
3.1.14 ChildAuthorityRelationship Table
This table enables the reporting upon the relationship between the Child and Authority specific Reference Numbers. You will need to use this table in conjunction with both the `Child' table and the `Authority' table, if you wish to report upon further Child and Authority details:
Fields |
Clarification |
UID |
The unique system ID of the relationship record |
ClientReferenceNumber |
The Child's Authority reference number |
SystemReferenceNumber |
The Child's Authority system reference number |
Child_UID_FK |
This field provides the Foreign Key to link to the Child UID within the Child table |
Authority_UID_FK |
This field provides the Foreign Key to link to the Authority UID within the Authority table |
DateCreated |
The date that the relationship record was created |
DateLastEdited |
Not applicable |
CreatedBy |
The login ID of the user that created the Relationship record |
LastEditedBy |
Not applicable |
3.2 External [CID] Child and Linked External Child Related Tables
This section provides details of the External Child Table and related tables, in terms of NRUC Child links to External CID Child records.
ExternalChild Table
This table enables access to External [CID] Child information, as displayed within the External Child view screen:
Fields |
Clarification |
UID |
The unique system ID of the External CID Child record |
DateCreated |
The date that the External CID Child record was created |
DateLastEdited |
Not applicable |
CreatedBy |
Not applicable |
LastEditedBy |
Not applicable |
CIDPersonID |
The unique Child ID within CID |
Title |
The title of the person within CID |
Gender |
The CID Child's gender |
FullName |
The CID Child's full name [to include forename and surname] |
FamilyName |
The CID Child's surname |
DateOfBirth |
The CID Child's date of birth |
Nationality |
The CID Child's nationality |
PlaceOfBirth |
The CID Child's place of birth |
CountryOfBirthCode |
The standard code of the CID Child's county of birth [this will correspond with the CountryOfBirthDesc] |
MaritalStatus |
The CID Child's marital status |
CountryOfBirthDesc |
The descriptive text of the CID Child's Country of Birth [this will correspond with the CountryOfBirthCode] |
PrimaryLanguageCode |
The standard code of the CID Child's primary language [this will correspond with the PrimaryLanguageDesc] |
PrimaryLanguageDesc |
The descriptive text of the CID Child's primary language [this will correspond with the PrimaryLanguageCode] |
OtherLanguageCode |
The standard code of the CID Child's Other language [this will correspond with the OtherLanguageDesc] |
OtherLanguageDesc |
The descriptive text of the CID Child's Other language [this will correspond with the OtherLanguageCode] |
PostCode |
The CID Child's postcode |
AliasTypeCode1 |
The standard code of the CID Child's first alias group [this will correspond with the AliasTypeDesc1] |
AliasTypeDesc1 |
The descriptive text of the CID Child's first alias group [this will correspond with the AliasTypeCode1] |
AliasName1 |
The alias name associated with the first alias group |
AliasNat1 |
The nationality associated with the first alias group |
AliasDOB1 |
The date of birth associated with the first alias group |
AliasTypeCode2 |
The standard code of the CID Child's second alias group [this will correspond with the AliasTypeDesc2] |
AliasTypeDesc2 |
The descriptive text of the CID Child's second alias group [this will correspond with the AliasTypeCode2] |
AliasName2 |
The alias name associated with the second alias group |
AliasNat2 |
The nationality associated with the second alias group |
AliasDOB2 |
The date of birth associated with the second alias group |
AliasTypeCode3 |
The standard code of the CID Child's third alias group [this will correspond with the AliasTypeDesc3] |
AliasTypeDesc3 |
The descriptive text of the CID Child's third alias group [this will correspond with the AliasTypeCode3] |
AliasName3 |
The alias name associated with the third alias group |
AliasNat3 |
The nationality associated with the third alias group |
AliasDOB3 |
The date of birth associated with the third alias group |
HORef |
The CID Child's Home Office Reference number |
NASSRef |
The CID Child's NASS Reference number |
LARef |
The CID Child's LA Reference number |
CaseID1 |
The CID Child's Case ID |
PortRef1 |
The CID Child's Port Reference number [associated with the first CID Case] |
Association1 |
The CID Child record association [associated with the first CID Case] |
Flavour1 |
The CID flavour of the CID child record [associated with the first CID Case] |
CaseType1 |
The type of the CID Child's first Case [associated with the first CID Case] |
ApplicationDate1 |
The date of application associated with the first CID Case |
CaseOutcome1 |
The outcome of the CID Child's first Case |
OutcomeDate1 |
The date of the outcome of the CID Child's first Case |
CaseID2 |
The CID Child's Case ID |
PortRef2 |
The CID Child's Port Reference number [associated with the second CID Case] |
Association2 |
The CID Child record association [associated with the second CID Case] |
Flavour2 |
The CID flavour of the CID child record [associated with the second CID Case] |
CaseType2 |
The type of the CID Child's second Case [associated with the second CID Case] |
ApplicationDate2 |
The date of application associated with the second CID Case |
CaseOutcome2 |
The outcome of the CID Child's second Case |
OutcomeDate2 |
The date of the outcome of the CID Child's second Case |
CaseID3 |
The CID Child's Case ID |
PortRef3 |
The CID Child's Port Reference number [associated with the third CID Case] |
Association3 |
The CID Child record association [associated with the third CID Case] |
Flavour3 |
The CID flavour of the CID child record [associated with the third CID Case] |
CaseType3 |
The type of the CID Child's third Case [associated with the third CID Case] |
ApplicationDate3 |
The date of application associated with the third CID Case |
CaseOutcome3 |
The outcome of the CID Child's third Case |
OutcomeDate3 |
The date of the outcome of the CID Child's third Case |
Company Name |
The Company name of the CID Child's representative |
Company Address |
The Company address of the CID Child's representative |
Company Postcode |
The Postcode of the CID Child's representative |
AppealJRPresent |
Whether or not there is a Judicial Review or Appeal pending |
SpecialConditionTypeCode1 |
The type of the first special condition [alert/ dispute] group associated with the CID Child |
SpecialConditionTypeDesc1 |
The description of the first special condition [alert/ dispute] group associated with the CID Child |
DateLodged1 |
The date that the first special condition [alert/ dispute] associated with the CID Child was lodged |
DateClosed1 |
The date that the first special condition [alert/ dispute] associated with the CID Child was closed |
SpecialConditionTypeCode2 |
The type of the second special condition [alert/ dispute] group associated with the CID Child |
SpecialConditionTypeDesc2 |
The description of the second special condition [alert/ dispute] group associated with the CID Child |
DateLodged2 |
The date that the second special condition [alert/ dispute] associated with the CID Child was lodged |
DateClosed2 |
The date that the second special condition [alert/ dispute] associated with the CID Child was closed |
SpecialConditionTypeCode3 |
The type of the third special condition [alert/ dispute] group associated with the CID Child |
SpecialConditionTypeDesc3 |
The description of the third special condition [alert/ dispute] group associated with the CID Child |
DateLodged3 |
The date that the third special condition [alert/ dispute] associated with the CID Child was lodged |
DateClosed3 |
The date that the third special condition [alert/ dispute] associated with the CID Child was closed |
AgeDisputeLodged |
The date that any age dispute was lodged against the CID Child |
AgeDisputeClosed |
The date that any age dispute lodged against the CID Child was closed |
ArrivalDateTime |
The date that the CID Child arrived at the port |
RemovalOutcome |
If the CID Child's case outcome was removal, the outcome of this removal is provided here |
OutcomeDate |
The corresponding date of the removal outcome |
BenefitProvider1 |
The name of the first Authority providing Assistance |
BenefitProvider2 |
The name of the second Authority providing Assistance |
BenefitProvider3 |
The name of the third Authority providing Assistance |
SupportType1 |
The type of support being provided by Benefit Provider 1 |
SupportType2 |
The type of support being provided by Benefit Provider 2 |
SupportType3 |
The type of support being provided by Benefit Provider 3 |
StartDate1 |
The date that support commenced with Benefit Provider 1 |
StartDate2 |
The date that support commenced with Benefit Provider 2 |
StartDate3 |
The date that support commenced with Benefit Provider 3 |
EndDate1 |
The date that support ended with Benefit Provider 1 |
EndDate2 |
The date that support ended with Benefit Provider 2 |
EndDate3 |
The date that support ended with Benefit Provider 3 |
OtherDateOfBirth |
The CID Child's date of birth if it is not in the standard format dd/mm/yyyy |
ARCIFBRef |
The CID Child's ARC/ IFB Reference number |
ChildExternalChildLinkRelationship Table
This table enables access to External Child record information where this child has been linked to a NRUC Child record
Fields |
Clarification |
UID |
The unique system ID of the External Child to NRUC Child association |
Child_UID_FK |
The unique system ID for the NRUC Child record [used if wishing to access further NRUC Child information for use within the report, by mapping this foreign key with the Child.UID Primary Key] |
ExternalChildLink_UID_FK |
The unique system ID for the linked External Child record [used if wishing to access further External Child record information for use within the report, by mapping this foreign key with the ExternalChild.UID Primary Key]. |
DateCreated |
The date that the link was created |
DateLastEdited |
Not applicable |
CreatedBy |
The user name of the user that created the link |
LastEditedBy |
Not applicable |
ExternalChildLink Table
This table enables access to External Child Link information, where the link is current
Fields |
Clarification |
UID |
The unique system ID of the current link |
DateCreated |
The date that the External child to NRUC Child link was created |
DateLastEdited |
Not applicable |
CreatedBy |
The user name of the user that created the link |
LastEditedBy |
Not applicable |
DateLastUpdatedWithinAgency |
The date that the Child record was last updated by the Source Agency, within the upload file |
ExternalChildID |
The ID of the External Child that the NRUC Child is linked to [used if wishing to access further External Child information for use within the report, by mapping this key to the ExternalChild.UID Primary Key] |
Status |
The status of the link. The numeric values within the Status field are foreign keys that map to the Ordinal values held within the `LinkStatusKind' table |
DateLastDeleted |
The date that the link between the CID Child record and the NRUC Child record was broken |
3.3 Case Related Tables
This section provides details of the Case Table. Case related tables are also provided within this section. For example, Confidential Case Notes and fields that require access to pick list values that are held within an associated table.
3.3.1 Case Table
The Case Table enables access to Case data i.e. data that is input/ amended within the Case Add/ Edit screens. It does not, however, include data that is input for each Case Status review [e.g. Referral Assessment or Initial Assessment information - these are handled within each Case Status table].
Fields |
Clarification |
UID |
The system ID of the Case record |
DateCreated |
The date that the Case record was created |
DateLastEdited |
The date that the Case record was last modified |
CreatedBy |
The login ID of the user that created the Case record |
LastEditedBy |
The login ID of the user that last modified the Case record |
ID |
The unique ID of the Case record |
Status |
The Case Status. The numeric values within the Status field map to the Ordinal values held within the `CaseStatusKind' |
StatusDate |
The date that the Case status was changed |
AgreementIndicator |
An indicator of agreement. The numeric values within the Status field map to the Ordinal values held within the `Error! Reference source not found.' |
Child_UID_FK |
The unique system ID for the NRUC Child record [used if wishing to access further NRUC Child information for use within the report, by mapping this foreign key with the Child.UID Primary Key] |
Department_UID_FK |
The unique system ID for the current placing Department within the Authority [used if wishing to access further Authority information for use within the report, by mapping this foreign key with the Department. UID Primary key] |
CaseTransferredTo_UID_FK |
The unique system ID for the Case that was created when a case was transferred to another Authority [used if wishing to access transferred to Case information for use within the report, by mapping this foreign key with the Case. UID Primary key] |
CaseTransferredFrom_UID_FK |
The unique system ID for the Case that existed when a case was transferred from another Authority [used if wishing to access transferred from Case information for use within the report, by mapping this foreign key with the Case. UID Primary key] |
CaseNote Table
This table enables access to Case Note information, as input within the Case Notes screen.
Fields |
Clarification |
UID |
The unique system ID of the Case Note record |
DateCreated |
The date that the Case Note record was created |
DateLastEdited |
Not applicable |
CreatedBy |
The login ID of the user that created the Case Note record |
LastEditedBy |
Not applicable |
ID |
Unique ID for the Case Note entry |
IsHOComment |
Indicates if the note is from the Home Office, 1 if true and 0 if false. |
Type |
The type of Case Note entry. The numeric values within the Type field are foreign keys that map to the Ordinal values held within the `ConfidentialCaseNoteKind' table |
Text |
The descriptive text of the Case Note entry |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
ReferralAssessment Table
This table enables access to data that has been input whilst the case was in `Referral Assessment'.
Fields |
Clarification |
UID |
The unique ID of the Referral Assessment case activity record |
DateCreated |
The date that the Referral Assessment case activity record was created |
DateLastEdited |
The date that the Referral Assessment case activity record was last modified |
CreatedBy |
The login ID of the user that created the Referral Assessment case activity record |
LastEditedBy |
The login ID of the user that last modified the Referral Assessment case activity record |
ReferralDate |
The date that the Referral was made |
ReferralReason |
The reason for Referral |
ReferralAssessmentOutcome |
The outcome of the Referral Assessment. The numeric values within the ReferralAssessmentOutcome field map to the Ordinal values held within the `ReferralAssessmentOutcomeKind' Table |
ReferredBy |
Who the case was referred by. The numeric values within the ReferredBy field map to the Ordinal values held within the `ReferredByKind' table |
OutcomeDate |
The date that the Referral Assessment outcome was made |
RejectionReason |
The reason for rejection, if this was the Referral Assessment Outcome. The numeric values within the RejectionReason field map to the Ordinal values held within the `RefusalKind' Table |
RejectionReasonOther |
The reason for rejection, where the RejectionReason value was `Other' |
ApplicationType |
The type of assistance requested by the Child. The numeric values within the ApplicationType field map to the Ordinal values held within the `SupportKind' Table |
ApplicationDate |
The date that the application for assistance was made |
ChildContactName |
The name of the Child contact that referred the Child, if applicable |
ChildContactDetails |
The contact details of the Child contact that referred the Child, if applicable |
OtherReferralSourceDetails |
Details of any other referral source details, if not a value within the `ReferredBy' list |
ActivitySummary |
Description of any activity that took place within the Referral Assessment |
OutcomeSummary |
Description of the outcome of the Referral Assessment |
ID |
The unique ID of the Referral Assessment case activity record |
Status |
The status of the Referral Assessment case activity record. The numeric values within the Status field map to the Ordinal values held within the `CaseActivityStatusKind' Table |
StartDate |
Start Date of the Referral Assessment |
EndDate |
End Date of the Referral Assessment |
MadeByUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that the Referral assessment was made by] |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
InitialAssessment Table
This table enables access to data that has been input whilst a case was in `Initial Assessment':
Fields |
Clarification |
UID |
The unique ID of the Initial Assessment case activity record |
DateCreated |
The date that the Initial Assessment case activity record was created |
DateLastEdited |
The date that the Initial Assessment case activity record was last modified |
CreatedBy |
The login ID of the user that created the Initial Assessment case activity record |
LastEditedBy |
The login ID of the user that last modified the Initial Assessment case activity record |
AcceptedDate |
The date that the Child was accepted for Initial Assessment |
AssessmentDOB |
The date of birth used for assessment purposes |
AssessmentDate |
The date that the assessment took place |
ChildrenActSection |
The Act that assistance has been provided in line with. The numeric values within the ChildrenActSection field map to the Ordinal values held within the `ChildrenActKind' table |
InitialAssessmentOutcome |
The outcome of the Initial Assessment. The numeric values within the InitialAssessmentOutcome field map to the Ordinal values held within the `InitialAssessmentOutcomeKind' Table |
CarePlanAgreed |
Whether or not the Care Plan has been agreed. Pick List Values: True False |
CarePlanAgreedDate |
The date that the Care Plan was agreed |
LeavingCarePlanAgreed |
Whether or not the Leaving Plan has been agreed. Pick List Values: True False |
LeavingCarePlanAgreedDate |
The date that the Leaving Care Plan was agreed |
RefusalReason |
The reason for refusing assistance. The numeric values within the RefusalReason field map to the Ordinal values held within the `RefusalKind' Table |
RefusalReasonOther |
A description of the reason for refusing assistance where the `RefusalReason' value is set to `Other' |
FamilyRelativeContacted |
Whether or not a family relative has been contacted. Pick List Values: True False |
FamilyRelativeName |
The name of the family relative that has been contacted |
OutcomeDate |
The outcome date of the Initial Assessment |
ActivitySummary |
A description of any activity that took place within the Initial Assessment |
OutcomeSummary |
A summary description the outcome of the Initial Assessment |
ID |
The unique ID of the Initial Assessment case activity record |
Status |
The status of the Initial Assessment case activity record. The numeric values within the Status field map to the Ordinal values held within the `CaseActivityStatusKind' Table |
StartDate |
Start Date of the Initial Assessment |
EndDate |
End Date of the Initial Assessment |
MadeByUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that the Initial assessment was made by] |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
ReceivingServices Table
This table enables access to data that has been input whilst a case was in `Receiving Services':
Fields |
Clarification |
UID |
The unique ID of the Receiving Services case activity record |
DateCreated |
The date that the Receiving Services case activity record was created |
DateLastEdited |
The date that the Receiving Services case activity record was last modified |
CreatedBy |
The login ID of the user that created the Receiving Services case activity record |
LastEditedBy |
The login ID of the user that last modified the Receiving Services case acitivity record |
ID |
The unique ID of the Receiving Services case activity record |
Status |
The status of the Receiving Services case activity record. The numeric values within the Status field map to the Ordinal values held within the `CaseActivityStatusKind' Table |
StartDate |
Start Date of the Receiving Services activity |
EndDate |
End Date of the Receiving Services activity |
SupportStartDate |
User assigned Start Date of the Receiving Services activity |
LeadProfessionalUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that is the current Lead Professional] |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
CaseHold Table
This table enables access to data that has been input whilst a case was being placed on `Hold':
Fields |
Clarification |
UID |
The unique ID of the Held Case activity record |
DateCreated |
The date that the Held Case activity record was created |
DateLastEdited |
The date that the Held Case activity record was last modified |
CreatedBy |
The login ID of the user that created the Held Case activity record |
LastEditedBy |
The login ID of the user that last modified the Held Case activity record |
HoldReason |
A description of the reason for the case being on hold |
ActivitySummary |
A description of any activity that took place whilst the case was on hold |
OutcomeSummary |
A decription of the outcome of any assessment that took place whilst the case was on hold |
ID |
The unique ID of the Held case activity record |
Status |
The status of the Held case activity record. The numeric values within the Status field map to the Ordinal values held within the `CaseActivityStatusKind' Table |
StartDate |
Start Date of the Held Case activity |
EndDate |
End Date of the Held Case activity |
MadeByUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that carried out the Held case activity] |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
CaseTransfer Table
This table enables access to data that has been input whilst a case was being `Transferred':
Fields |
Clarification |
UID |
The unique ID of the `In Transit' case activity record |
DateCreated |
The date that the `In Transit' case activity record was created |
DateLastEdited |
The date that the `In Transit' case activity record was last modified |
CreatedBy |
The login ID of the user that created the `In Transit' case activity record |
LastEditedBy |
The login ID of the user that last modified the `In Transit' case activity record |
TransferDate |
The date that the case was transferred |
TransferReason |
A description of the reason for the case being Transferred |
ID |
The unique ID of the `In Transit' case activity record |
Status |
The status of the Tranfer case activity record. The numeric values within the Status field map to the Ordinal values held within the `CaseActivityStatusKind' Table |
StartDate |
Start Date of the Transfer Case activity |
EndDate |
End Date of the Transfer Case activity |
Department_UID_FK |
This field provides the Foreign Key to link to the Department UID within the Department table [this is the Department within an Authority that the case is transferred to] |
OverseeingUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that the case has been transferred to] |
TransferringUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that carried out the Transfer case activity] |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
CaseClosed Table
This table enables access to data that has been input whilst a case was being `Closed':
Fields |
Clarification |
UID |
The unique ID of the `Closed' Case activity record |
DateCreated |
The date that the `Closed Case' activity record was created |
DateLastEdited |
The date that the `Closed Case' activity record was last modified |
CreatedBy |
The login ID of the user that created the `Closed Case' activity record |
LastEditedBy |
The login ID of the user that last modified the `Closed Case' activity record |
ClosureReason |
A description of the reason for the case being Closed |
ActivitySummary |
A description of any activity that took place whilst the case was being closed |
OutcomeSummary |
A decription of the outcome of any assessment that took place whilst the case being closed |
ID |
The unique ID of the `Closed' case activity record |
Status |
The status of the Closed case activity record. The numeric values within the Status field map to the Ordinal values held within the `CaseActivityStatusKind' Table |
StartDate |
Start Date of the Case Closure activity |
EndDate |
End Date of the Case Closure activity |
ClosureDate |
Closure Date of the Case Closure activity |
MadeByUser_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table [the NRUC user that carried out the Case Closure activity] |
Case_UID_FK |
This field provides the Foreign Key to link to the Case UID within the Case table |
Assistance Table
This table enables access to data that has been input within the Assistance form available via the `Case Assistance Costs' option when the case has an instance of a case in `Receiving Services' activity:
Fields |
Clarification |
UID |
The unique ID of the Assistance record |
DateCreated |
The date that the Assistance record was created |
DateLastEdited |
The date that the Assistance record was last modified |
CreatedBy |
The login ID of the user that created the Assistance record |
LastEditedBy |
The login ID of the user that last modified the Assistance record |
AssitanceProvided |
The type of Assistance being provided. The numeric values within the AssistanceProvided field map to the Ordinal values held within the `PersonalCostKind' Table |
OtherAssistanceProvided |
Further definition of Assistance Provided, if the value selected is `Other' |
Cost |
The cost of the assistance type being provided |
PaymentStartDate |
The date that the assistance payment commenced |
PaymentEndDate |
The date that the assistance payment ended |
PaymentFrequency |
The frequency with which the cost is paid. The numeric values within the PaymentFrequency field map to the Ordinal values held within the `FrequencyKind' table |
ReceivingServices_UID_FK |
This field provides the Foreign Key to link to the ReceivingServices UID within the ReceivingServices table |
3.4 User related tables
This section provides details of the User Table. User related tables are also provided within this section. For example, Contact Information and fields that require access to pick list values that are held within an associated table.
User Table
This table enables access to data input within the User. Add and User. Edit screens.
Fields |
Clarification |
UID |
The unique system ID of the User record |
DateCreated |
The date that the User record was created |
DateLastEdited |
The date that the User record was last amended |
CreatedBy |
The login ID of the User that created the User record |
LastEditedBy |
The login ID of the User that last amended the User record |
ID |
The unique ID of the User record |
Status |
The status of the User. The numeric values within the Status field map to the Ordinal values held within the `UserStatusKind' Table |
Login |
The login ID of the User |
LastPasswordChangedDate |
The last date/ time this user last changed their password |
LastStatusAcceptedDate |
The last date/ time that this user was last accepted as a valid user |
HasAccessToDepartmentTransfers |
Whether or not the user has access to cases that have been transferred to the same department as themselves |
Forename |
The user's Forename |
Surname |
The user's surname |
Initials |
The user's Initials |
Title |
The user's title |
ContactInformation Table
This table enables access to contact details input within the User. Add and User. Edit screens.
Fields |
Clarification |
UID |
The unique system ID of the Contact Information record |
DateCreated |
The date that the Contact Information record was created |
DateLastEdited |
The date that the Contact Information record was last amended |
CreatedBy |
The login ID of the User that created the Contact Information record |
LastEditedBy |
The login ID of the User that last amended the Contact Information record |
IsPrimary |
Whether or not the Contact Information is the primary set of contact information for the associated user. Pick List Values: True False |
Type |
A type associated with the set of contact information. The numeric values within the Type field map to the Ordinal values held within the `ContactGroupKind' Table |
AddressLine2 |
The second line of the contact address |
AddressLine3 |
The third line of the contact address |
AdministrativeArea |
The Administrative Area [County] of the contact address |
PostCode |
The Postcode of the contact address |
PostTown |
The Post Town of the contact address |
PrimaryNameOrNumber |
The First Name or Number of the contact address |
Secondary NameOrNumber |
The Second Name or Number of the contact address |
Street |
The Street name of the contact address |
UPRN |
The Unique Property Reference Number of the contact address |
USRN |
The Unique Street Reference Number of the contact address |
EmailAddress |
The e-mail address associated with the contact group |
FaxNumber |
The fax number associated with the contact group |
TelephoneNumber |
The telephone number associated with the contact group |
MobileNumber |
The mobile number associated with the contact group |
User_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table |
Team Table
This table enables access to a user's team details, when used in combination with the TeamUserRelationship Table.
Fields |
Clarification |
UID |
The unique system ID of the Team record |
DateCreated |
The date that the Team record was created |
DateLastEdited |
The date that the Team record was last amended |
CreatedBy |
The login ID of the User that created the Team record |
LastEditedBy |
The login ID of the User that last amended the Team record |
Name |
The name of the user's team |
Code |
Not currently used |
Department_UID_FK |
This field provides the Foreign Key to link to the Department UID within the Department table in order to identify the Department that a team is related to |
TeamUserRelationship Table
This table enables access to a user's Team details, identifying the relationship between a User and their Team and also determining their relationship with a Team Manager.
Fields |
Clarification |
UID |
The unique system ID of the relationship record |
IsTeamManager |
Whether or not the user is the Team Manager. Pick List Values: True False |
Team_UID_FK |
This field provides the Foreign Key to link to the Team UID within the Team table |
User_UID_FK |
This field provides the Foreign Key to link to the User UID within the User table |
DateCreated |
The date that the relationship record was created |
DateLastEdited |
Not applicable |
CreatedBy |
The login ID of the User that created the relationship record |
LastEditedBy |
Not applicable |
Authority and Department Tables
This section provides details of Authority and Department tables.
Department Table
This table enables access to department information [for example, in order to access a user's department details, it should be used in combination with the
`Team' and `TeamUserRelationship' tables]
Fields |
Clarification |
UID |
The unique system ID of the Department record |
DateCreated |
The date that the Department record was created |
DateLastEdited |
The date that the Department record was last amended |
CreatedBy |
The login ID of the User that created the Department record |
LastEditedBy |
The login ID of the User that last amended the Department record |
Name |
The name of the Department |
Code |
Not currently used |
Authority_UID_FK |
This field provides the Foreign Key to link to the Authority UID within the Authority table |
Country_UID_FK |
This field provides the Foreign Key to link to the Country UID within the Country table |
Authority Table
This table enables access to Authority information
Fields |
Clarification |
UID |
The unique system ID of the Authority record |
DateCreated |
The date that the Authority record was created |
DateLastEdited |
The date that the Authority record was last amended |
CreatedBy |
The login ID of the User that created the Authority record |
LastEditedBy |
The login ID of the User that last amended the Authority record |
Name |
The name of the Authority |
Code |
The code of the Authority |
Country_UID_FK |
This field provides the Foreign Key to link to the Country UID within the Country table |
Authority_UID_FK |
This field provides the Foreign Key to link to the Authority UID within the Authority table, where one Authority is associated with another Authority. See the following URL for a list of potential Authority codes and values: http://www.statistics.gov.uk/geography/downloads/31_10_01_County_names_and_codes_12_00.xls |
CountryTable
This table enables access to country information
Fields |
Clarification |
UID |
The unique system ID of the Country record |
DateCreated |
The date that the Country record was created |
DateLastEdited |
The date that the Country record was last amended |
CreatedBy |
The login ID of the User that created the Country record |
LastEditedBy |
The login ID of the User that last amended the Country record |
Name |
The name of the user's Country |
Code |
The Country code |
AuditEvent Table
This table enables access to Audit Event data that is captured throughout the application. The embedded spreadsheet details which fields are applicable to each Event `Type' and so are appropriate to report upon.
Fields |
Clarification |
UID |
The unique system ID of the Audit Event |
Type |
The Audit Event type is always presented as a Code e.g. EVTUSR001. The embedded spreadsheet, above, details what fields are applicable to which Event `Type', the following embedded file details which Type code refers to which event:
|
DateCreated |
The date that an audit event was created |
DateLastEdited |
The date that an event was last edited |
CreatedBy |
Who an audit event was created by |
LastEditedBy |
Who an audit event was last edited by |
NewCaseStatus |
The status that a Case record was changed to. The numeric values within the Type field are foreign keys that map to the Ordinal values held within the `CaseStatusKind' Table |
InstigatingUserAuthorityName |
The name of the Authority of the user that instigated the audit event |
AttachmentName |
The name of the attachment [associated with the attachment audit event] |
SourceDepartmentName |
The name of the Department that was the source of a case transfer |
InstigatingUserTeamName |
The name of the Team of the user that instigated the audit event |
AliasType |
The type of Alias. The numeric values within the Type field are foreign keys that map to the Ordinal values held within the `AliasTypeKind' table |
LeadProfessionalSurname |
The surname of the Lead Professional |
ExternalChildForename |
The forename of the external [CID] child |
ExternalSystemName |
The name of the external system [i.e. CID] |
ChildForename |
The forename of the Child |
InstigatingUserDepartmentName |
The name of the Department of the user that instigated the audit event |
ChildSurname |
The surname of the Child |
DestinationDepartmentName |
The name of the Department that received the case transfer |
UserSurname |
The surname of the User |
NewContactData |
Contact details [i.e. change of primary residence or contact] |
ExternalChildID |
The External Child unique identifier |
DestinationAuthorityName |
The name of the Authority that a case has been transferred to |
ExternalDepartmentName |
The name of the Department that an external [CID] child is associated with, if it is linked to a NRUC child record |
ExternalChildSurname |
The surname of the External [CID] child |
NewUserStatus |
The status the User changed to. The numeric values within the Status field map to the Ordinal values held within the `UserStatusKind' Table |
AuditDate |
The date that the audit event took place |
CaseID |
The unique identifier for the Case |
PreviousCaseStatus |
The status that a Case record was changed from. The numeric values within the Status field are foreign keys that map to the Ordinal values held within the `CaseStatusKind' Table |
SourceAuthorityName |
The name of the Authority that a Case was transferred from |
InstigatingUserSurname |
The surname of the user that instigated the audit event |
SpecialConditionType |
The Special Condition type involved within the Audit Event. The numeric values within the SpecialConditionType field are foreign keys that map to the Ordinal values held within the `SpecialConditionKind' Table |
LeadProfessionalForename |
The forename of the Lead Professional |
InstigatingUserForename |
The forename of the user that instigated the audit event |
PreviousUserStatus |
The status the User changed from. The numeric values within the Status field map to the Ordinal values held within the `UserStatusKind' Table |
UserForename |
The user forename |
TargetUser_UID_FK |
The unique system ID of the User involved within the audit event. This field provides the Foreign Key to link to the User UID within the User table, enabling the reporting upon further User details |
Assistance_UID_FK |
The unique system ID of the Assistance involved within the audit event. This field provides the Foreign Key to link to the Assistance UID within the Assistance table, enabling the reporting upon further Assistance details |
ChildContactInformation_UID_FK |
The unique system ID of the ChildContactInformation involved within the audit event. This field provides the Foreign Key to link to the ChildContactInformation UID within the ChildContactInformation table, enabling the reporting upon further ChildContactInformation details |
Attachment_UID_FK |
The unique system ID of the Attachment involved within the audit event. This field provides the Foreign Key to link to the Attachment UID within the Attachment table, enabling the reporting upon further Attachment details |
ChildPlacementContactInformation_UID_FK |
The unique system ID of the ChildPlacementContactInformation involved within the audit event. This field provides the Foreign Key to link to the ChildPlacementContactInformation UID within the ChildPlacementContactInformation table, enabling the reporting upon further ChildPlacementContactInformation details |
Alias_UID_FK |
The unique system ID of the Alias involved within the audit event. This field provides the Foreign Key to link to the Alias UID within the Alias table, enabling the reporting upon further Alias details |
ImmigrationDetails_UID_FK |
The unique system ID of the ImmigrationDetails involved within the audit event. This field provides the Foreign Key to link to the ImmigrationDetails UID within the ImmigrationDetails table, enabling the reporting upon further ImmigrationDetails details |
InstigatingUser_UID_FK |
The unique system ID of the User involved within the audit event. This field provides the Foreign Key to link to the User UID within the User table, enabling the reporting upon further user details |
TargetCase_UID_FK |
The unique system ID of the Case involved within the audit event. This field provides the Foreign Key to link to the Case UID within the Case table, enabling the reporting upon further Case details |
TargetChild_UID_FK |
The unique system ID of the Child involved within the audit event. This field provides the Foreign Key to link to the Child UID within the Child table, enabling the reporting upon further Child details |
LeadProfessional_UID_FK |
The unique system ID of the User involved within the audit event. This field provides the Foreign Key to link to the User UID within the User table, enabling the reporting upon further user details |
CaseNote_UID_FK |
The unique system ID of the CaseNote involved within the audit event. This field provides the Foreign Key to link to the CaseNote UID within the CaseNote table, enabling the reporting upon further CaseNote details |
SpecialCondition_UID_FK |
The unique system ID of the SpecialCondition involved within the audit event. This field provides the Foreign Key to link to the SpecialCondition UID within the SpecialCondition table, enabling the reporting upon further SpecialCondition details |
ExternalChildLink_UID_FK |
The unique system ID of the ExternalChildLink involved within the audit event. This field provides the Foreign Key to link to the ExternalChildLink UID within the ExternalChildLink table, enabling the reporting upon further ExternalChildLink details |
1