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.

1 Schema
2 User-Defined Functions

Related Pages

Working with the Entities Stream

Installing Additional Streams

Schema

entities.t_entity_hierarchy_info

Column	Type	Modifiers	Comment
entity_hierarchy_info_id	serial	primary key	Table primary key
entity_id	integer	unique, not null	Entity ID number
uo_entity_id	integer	not null	Ultimate owner entity_id
parent_entity_id	integer	not null	Parent entity_id
entity_name	varchar(300)	not null	Entity name
country	char(2)		Code of the entity's country of incorporation or registration
status	char(1)		Entity status: R=Reviewed V=Validated
category_name	varchar(100)		Category of entity: Cooperative Delisted Education Facility Government Healthcare Joint Venture Nonprofit Private Public Research Trust Undefined
entity_type	char(1)		Type of entity: F=Former Name M=Merged N=Standard Name O=Owner S=Subsidiary
entity_address	varchar(2000)		Address information for the entity
ifi_number	integer		IFI organization number
standard_name_id	integer		IFI standardized name ID
standard_name	varchar(300)		IFI standardized name
last_modified	date		Date the hierarchy was last reviewed
created_load_id	integer	not null	Load id of entity creation
modified_load_id	integer	not null	Load id of last modification
deleted_load_id	integer		Load id when entity was deleted
attributes	xml		Additional attributes for the entity (exchange, ticker, etc.)

entities.t_publication_ifi_numbers

Column	Type	Modifiers	Comment
publication_ifi_numbers_id	serial	primary key	Table primary key
publication_id	integer	not null	Publication ID referencing xml.t_patent_document_values (publication_id), unique to each instance Note: Once a new ucid (document) is loaded, it receives an auto-generated publication_id that never changes. The relationship between publication_id and ucid remains intact even if the document is modified or deleted. In rare situations when a patent office “rekeys” a ucid (e.g., to change a mistaken kind code or change the format of the document number), the original ucid is marked “deleted” and the new ucid is added with a new publication_id.
country	char(2)		Publication country code
family_id	integer	not null	Publication family_id, will differ from xml.t_patent_document_values for unassigned publications
ifi_number	integer		IFI organization number, referencing entities.t_entity_hierarchy_info (ifi_number)
status_code	smallint		Status lookup code, referencing entities.t_status_codes (status_code)
level	char(1)		Publication level/type: A=Application G=Grant U=Unknown
expiration	date		Expiration date
modified_load_id	integer		Load id of last modification

entities.t_status_codes

Column	Type	Modifiers	Comment
status_code	serial	primary key	Table primary key
status_description	text	not null	Status 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(3898);
 exchange | symbol 
----------+--------
 XNAS     | GOOGL

entities.f_id2name(integer)

Returns the entity name from the integer identifier.

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