Entities Schema and User-Defined Functions
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 |
| business_unit | varchar(300) | Entity business unit | |
| country | char(2) | Entity country code | |
| status | char(1) | Entity status: B=Blocked | |
| category_name | varchar(100) | Category of entity: Public Unlisted | |
| entity_type | char(1) | Type of entity: F=Former (name) | |
| 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 entity was last modified | |
| 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 ) |
| 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 | |
| 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(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
