Entities Schema and User-Defined Functions

The entities schema is used to retrieve company hierarchies and summary statistics. It is the foundation of our IP profiles. This data is available through the CLAIMS Direct platform as a separate subscription.

Schema

entities.t_entity_hierarchy_info

ColumnTypeModifiersComment
entity_hierarchy_info_idserialprimary keyTable primary key
entity_idintegerunique, not nullEntity ID number
uo_entity_idintegernot nullUltimate owner entity_id
parent_entity_idintegernot nullParent entity_id
entity_namevarchar(300)not nullEntity name
business_unitvarchar(300)
Entity business unit
countrychar(2)
Entity country code
statuschar(1)

Entity status:

B=Blocked
R=Reviewed
V=Validated
P=Pending

category_namevarchar(100)

Category of entity:

Public Unlisted
Cooperative
Joint Venture
Research
Private
Government
Academic
Public
Delisted

entity_typechar(1)

Type of entity:

F=Former (name)
M=Merged
O=Owner
S=Subsidiary

entity_addressvarchar(2000)
Address information for the entity
ifi_numberinteger
IFI organization number
standard_name_idinteger
IFI standardized name ID
standard_namevarchar(300)
IFI standardized name
last_modifieddate
Date the entity was last modified
created_load_idintegernot nullLoad id of entity creation
modified_load_idintegernot nullLoad id of last modification
deleted_load_idinteger
Load id when entity was deleted
attributesxml
Additional attributes for the entity (exchange, ticker, etc.)


entities.t_publication_ifi_numbers

ColumnTypeModifiersComment
publication_ifi_numbers_idserialprimary keyTable primary key
publication_idintegernot nullPublication ID referencing xml.t_patent_document_values (publication_id )
countrychar(2)
Publication country code
family_idintegernot nullPublication family_id, will differ from xml.t_patent_document_values for unassigned publications
ifi_numberinteger
IFI organization number, referencing entities.t_entity_hierarchy_info (ifi_number)
status_codesmallint
Status lookup code, referencing entities.t_status_codes (status_code)
levelchar(1)

Publication level/type:

A=Application
G=Grant
U=Unknown

expirationdate
Expiration date
modified_load_idinteger
Load id of last modification


entities.t_status_codes

ColumnTypeModifiersComment
status_codeserialprimary keyTable primary key
status_descriptiontextnot nullStatus description

User-Defined Functions

entities.f_cdws_subsidiaries(text)

Returns a table of subsidiaries as TABLE(entity_id integer, parent_entity_id integer, country character, name text, extract_status character, parent_relationship character, depth integer).

select * from entities.f_cdws_subsidiaries( 'Alphabet Inc' ) limit 5;
 entity_id | parent_entity_id | country |           name           | extract_status | parent_relationship | depth 
-----------+------------------+---------+--------------------------+----------------+---------------------+-------
     69315 |             3898 | US      | Google LLC               | V              | S                   |     1
    170175 |             3898 | US      | Sidewalk Labs LLC        | V              | S                   |     1
    170176 |             3898 | US      | Verily Life Sciences LLC | V              | S                   |     1
    170177 |             3898 | US      | Waymo LLC                | V              | S                   |     1
    170178 |             3898 | US      | X Development LLC        | V              | S                   |     1


entities.f_cdws_ultimate(text)

Returns the ultimate owner of a given subsidiary.

select * from entities.f_cdws_ultimate( 'Waymo LLC' );
 entity_id | country |     name     | extract_status |  updated   | parent_relationship |                         address                         
-----------+---------+--------------+----------------+------------+---------------------+---------------------------------------------------------
      3898 | US      | Alphabet Inc | V              | 2020-11-16 | O                   | 1600 Amphitheatre Parkway, Mountain View, CA, 94043, US


entities.f_attributes_ticker(integer)

Returns exchange and ticker symbol information for an entity if available.

select * from entities.f_attributes_ticker(1);
 exchange | symbol 
----------+--------
 XNAS     | ABMD


entities.f_id2name(integer)

Returns the entity name from the integer identifier.

select entities.f_id2name( 3898 );
  f_id2name   
--------------
 Alphabet Inc