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



  • No labels