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