BDNA Data Platform 5.4 July 2017 (SQL Server)
CAT_GUDID - Global Unique Device Identification Database details table

Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
CAT_GUDID_ID numeric 20  √  null Unique identifier of CAT_GUDID table
CREATE_DATE datetime 16,3  √  null The date when the row was first created in the table and column specified in TABLE_NAME and COLUMN_NAME
LAST_MODIFIED_DATE datetime 16,3  √  null The date when last modification on the row was made in the table and column specified in TABLE_NAME and COLUMN_NAME
DEVICE_RECORD_STATUS varchar 100  √  null Device Record Status
DI_RECORD_PUBLISH_DT datetime 16,3  √  null DI Record Publish Date
COMMERCIAL_DIST_END_DT datetime 16,3  √  null Commercial Distribution End Date
COMMERCIAL_DIST_STATUS varchar 100  √  null Commercial Distribution Status
PRIMARY_DI_NUMBER varchar 100  √  null Primary DI Number
DEVICE_TYPE varchar 100  √  null Device Type
ISSUING_AGENCY varchar 100  √  null Issuing Agency
BRAND_NAME varchar 100  √  null Brand Name
VERSION_MODEL_NUMBER varchar 100  √  null Version or Model
CATALOG_NUMBER varchar 100  √  null Catalog Number
COMPANY_NAME varchar 255  √  null Company Name
DEVICE_COUNT numeric 20  √  null Device Count
DEVICE_DESC varchar 2000  √  null Device Description
DME_EXEMPT varchar 100  √  null Device Subject to Direct Marking (DM) but Exempt
PRE_MKT_EXEMPT varchar 100  √  null Premarket Exempt
DEVICE_HCTP varchar 100  √  null Human Cell Tissue or Cellular or Tissue-Based Product (HCT/P)
DEVICE_KIT varchar 200  √  null Kit
DEVICE_COMBINATION_PRODUCT varchar 200  √  null Combination Product
SINGLE_USE varchar 200  √  null For Single-Use
LOT_BATCH varchar 200  √  null Lot or Batch Number
SERIAL_NUMBER varchar 200  √  null Serial Number
MANUF_DATE varchar 100  √  null Manufacturing Date
EXPIRATION_DATE varchar 100  √  null Expiration Date
DONATION_ID_NUMBER varchar 100  √  null Donation Identification Number
LABELED_CONTAINS_NRL varchar 100  √  null Device required to be labeled as containing natural rubber latex or dry natural rubber (21 CFR 801.437)
LABELED_CONTAINS_NO_NRL varchar 100  √  null Device labeled as Not made with natural rubber latex
MRI_SAFETY_STATUS varchar 200  √  null What MRI safety information does the labeling contain
PRESCRIPTION_RX varchar 100  √  null Prescription Use (Rx)
OTC varchar 100  √  null Over the Counter (OTC)
PHONE varchar 200  √  null Phone
EMAIL varchar 200  √  null Email
STERILE_DEVICE varchar 100  √  null Sterile Device
STERILE_PRIOR_TO_USE varchar 100  √  null Requires Sterilization Prior to Use
URL varchar 4000  √  null URL
STERILE_METHOD varchar 500  √  null Sterilization Method
TO_BE_DELETED varchar 1  √  null Would this record be deleted? eg: Y
TO_BE_DELETED_ON datetime 16,3  √  null The date when this record would be deleted
DELETE_REASON varchar 500  √  null The reason why the entry is marked to be deleted (Values: Duplicate,Inaccurate)
REPLACEMENT_ID numeric 20  √  null Unique identifier of the replacement entry whenever the entry is marked to be deleted for 'Duplicate' reason
PACKAGE_DI_NUM varchar 2000  √  null
CONTAINS_DI_NUM varchar 2000  √  null
PKG_QUANTITY varchar 2000  √  null
PRIVATE_FLAG numeric 20  √  null 0 = Not Modified (i.e. original Technopedia entry),1 = Modified Technopedia object, 2 = Proprietary object (those that have negative ids)
IS_PRIVATE varchar 1  √  null y = a private entry, n = original Technopedia entry
PRIVATE_TYPE varchar 500  √  null PRIVATE_TYPE = NULL if PRIVATE_FLAG = 0,PRIVATE_TYPE = Modified Technopedia entry if PRIVATE_FLAG = 1,PRIVATE_TYPE = Proprietary entry if PRIVATE_FLAG = 2
UNIT varchar 2000  √  null

Analyzed at Thu Jul 20 12:14 PDT 2017

View Definition:
CREATE VIEW CAT_GUDID AS SELECT f_tcat.CAT_GUDID_ID,f_tcat.CREATE_DATE,f_tcat.LAST_MODIFIED_DATE,f_tcat.DEVICE_RECORD_STATUS,f_tcat.DI_RECORD_PUBLISH_DT,f_tcat.COMMERCIAL_DIST_END_DT,f_tcat.COMMERCIAL_DIST_STATUS,f_tcat.PRIMARY_DI_NUMBER,f_tcat.DEVICE_TYPE,f_tcat.ISSUING_AGENCY,f_tcat.BRAND_NAME,f_tcat.VERSION_MODEL_NUMBER,f_tcat.CATALOG_NUMBER,f_tcat.COMPANY_NAME,f_tcat.DEVICE_COUNT,f_tcat.DEVICE_DESC,f_tcat.DME_EXEMPT,f_tcat.PRE_MKT_EXEMPT,f_tcat.DEVICE_HCTP,f_tcat.DEVICE_KIT,f_tcat.DEVICE_COMBINATION_PRODUCT,f_tcat.SINGLE_USE,f_tcat.LOT_BATCH,f_tcat.SERIAL_NUMBER,f_tcat.MANUF_DATE,f_tcat.EXPIRATION_DATE,f_tcat.DONATION_ID_NUMBER,f_tcat.LABELED_CONTAINS_NRL,f_tcat.LABELED_CONTAINS_NO_NRL,f_tcat.MRI_SAFETY_STATUS,f_tcat.PRESCRIPTION_RX,f_tcat.OTC,f_tcat.PHONE,f_tcat.EMAIL,f_tcat.STERILE_DEVICE,f_tcat.STERILE_PRIOR_TO_USE,f_tcat.URL,f_tcat.STERILE_METHOD,f_tcat.TO_BE_DELETED,f_tcat.TO_BE_DELETED_ON,f_tcat.DELETE_REASON,f_tcat.REPLACEMENT_ID,f_tcat.PACKAGE_DI_NUM,f_tcat.CONTAINS_DI_NUM,f_tcat.PKG_QUANTITY,f_tcat.PRIVATE_FLAG,f_tcat.IS_PRIVATE,f_tcat.PRIVATE_TYPE,f_tcat.UNIT from TCAT_GUDID f_tcat where not exists (select 1 from TPC_GUDID tpc_tab where tpc_tab.CAT_GUDID_ID=f_tcat.CAT_GUDID_ID ) union all SELECT * FROM ( SELECT COALESCE(tpc.CAT_GUDID_ID,tcat.CAT_GUDID_ID) CAT_GUDID_ID,COALESCE(tpc.CREATE_DATE,tcat.CREATE_DATE) CREATE_DATE,CASE WHEN ISNULL(tpc.LAST_MODIFIED_DATE,'31-DEC-1900') > tcat.LAST_MODIFIED_DATE THEN tpc.LAST_MODIFIED_DATE WHEN ISNULL(tpc.LAST_MODIFIED_DATE,'31-DEC-1900') < tcat.LAST_MODIFIED_DATE THEN tcat.LAST_MODIFIED_DATE ELSE tcat.LAST_MODIFIED_DATE END LAST_MODIFIED_DATE, COALESCE(tpc.DEVICE_RECORD_STATUS,tcat.DEVICE_RECORD_STATUS) DEVICE_RECORD_STATUS,COALESCE(tpc.DI_RECORD_PUBLISH_DT,tcat.DI_RECORD_PUBLISH_DT) DI_RECORD_PUBLISH_DT,COALESCE(tpc.COMMERCIAL_DIST_END_DT,tcat.COMMERCIAL_DIST_END_DT) COMMERCIAL_DIST_END_DT,COALESCE(tpc.COMMERCIAL_DIST_STATUS,tcat.COMMERCIAL_DIST_STATUS) COMMERCIAL_DIST_STATUS,COALESCE(tpc.PRIMARY_DI_NUMBER,tcat.PRIMARY_DI_NUMBER) PRIMARY_DI_NUMBER,COALESCE(tpc.DEVICE_TYPE,tcat.DEVICE_TYPE) DEVICE_TYPE,COALESCE(tpc.ISSUING_AGENCY,tcat.ISSUING_AGENCY) ISSUING_AGENCY,COALESCE(tpc.BRAND_NAME,tcat.BRAND_NAME) BRAND_NAME,COALESCE(tpc.VERSION_MODEL_NUMBER,tcat.VERSION_MODEL_NUMBER) VERSION_MODEL_NUMBER,COALESCE(tpc.CATALOG_NUMBER,tcat.CATALOG_NUMBER) CATALOG_NUMBER,COALESCE(tpc.COMPANY_NAME,tcat.COMPANY_NAME) COMPANY_NAME,COALESCE(tpc.DEVICE_COUNT,tcat.DEVICE_COUNT) DEVICE_COUNT,COALESCE(tpc.DEVICE_DESC,tcat.DEVICE_DESC) DEVICE_DESC,COALESCE(tpc.DME_EXEMPT,tcat.DME_EXEMPT) DME_EXEMPT,COALESCE(tpc.PRE_MKT_EXEMPT,tcat.PRE_MKT_EXEMPT) PRE_MKT_EXEMPT,COALESCE(tpc.DEVICE_HCTP,tcat.DEVICE_HCTP) DEVICE_HCTP,COALESCE(tpc.DEVICE_KIT,tcat.DEVICE_KIT) DEVICE_KIT,COALESCE(tpc.DEVICE_COMBINATION_PRODUCT,tcat.DEVICE_COMBINATION_PRODUCT) DEVICE_COMBINATION_PRODUCT,COALESCE(tpc.SINGLE_USE,tcat.SINGLE_USE) SINGLE_USE,COALESCE(tpc.LOT_BATCH,tcat.LOT_BATCH) LOT_BATCH,COALESCE(tpc.SERIAL_NUMBER,tcat.SERIAL_NUMBER) SERIAL_NUMBER,COALESCE(tpc.MANUF_DATE,tcat.MANUF_DATE) MANUF_DATE,COALESCE(tpc.EXPIRATION_DATE,tcat.EXPIRATION_DATE) EXPIRATION_DATE,COALESCE(tpc.DONATION_ID_NUMBER,tcat.DONATION_ID_NUMBER) DONATION_ID_NUMBER,COALESCE(tpc.LABELED_CONTAINS_NRL,tcat.LABELED_CONTAINS_NRL) LABELED_CONTAINS_NRL,COALESCE(tpc.LABELED_CONTAINS_NO_NRL,tcat.LABELED_CONTAINS_NO_NRL) LABELED_CONTAINS_NO_NRL,COALESCE(tpc.MRI_SAFETY_STATUS,tcat.MRI_SAFETY_STATUS) MRI_SAFETY_STATUS,COALESCE(tpc.PRESCRIPTION_RX,tcat.PRESCRIPTION_RX) PRESCRIPTION_RX,COALESCE(tpc.OTC,tcat.OTC) OTC,COALESCE(tpc.PHONE,tcat.PHONE) PHONE,COALESCE(tpc.EMAIL,tcat.EMAIL) EMAIL,COALESCE(tpc.STERILE_DEVICE,tcat.STERILE_DEVICE) STERILE_DEVICE,COALESCE(tpc.STERILE_PRIOR_TO_USE,tcat.STERILE_PRIOR_TO_USE) STERILE_PRIOR_TO_USE,COALESCE(tpc.URL,tcat.URL) URL,COALESCE(tpc.STERILE_METHOD,tcat.STERILE_METHOD) STERILE_METHOD,COALESCE(tpc.TO_BE_DELETED,tcat.TO_BE_DELETED) TO_BE_DELETED,COALESCE(tpc.TO_BE_DELETED_ON,tcat.TO_
 
Possibly Referenced Tables/Views:

Confidential and Proprietary to BDNA