BDNA Data Platform 5.4 July 2017 (SQL Server) CAT_GUDID - Global Unique Device Identification Database details table
|
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: