BDNA Data Platform 5.4 July 2017 (Oracle) CAT_GUDID - Global Unique Device Identification Database details table
|
View Definition:
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 "CAT_GUDID_ID","CREATE_DATE","LAST_MODIFIED_DATE","DEVICE_RECORD_STATUS","DI_RECORD_PUBLISH_DT","COMMERCIAL_DIST_END_DT","COMMERCIAL_DIST_STATUS","PRIMARY_DI_NUMBER","DEVICE_TYPE","ISSUING_AGENCY","BRAND_NAME","VERSION_MODEL_NUMBER","CATALOG_NUMBER","COMPANY_NAME","DEVICE_COUNT","DEVICE_DESC","DME_EXEMPT","PRE_MKT_EXEMPT","DEVICE_HCTP","DEVICE_KIT","DEVICE_COMBINATION_PRODUCT","SINGLE_USE","LOT_BATCH","SERIAL_NUMBER","MANUF_DATE","EXPIRATION_DATE","DONATION_ID_NUMBER","LABELED_CONTAINS_NRL","LABELED_CONTAINS_NO_NRL","MRI_SAFETY_STATUS","PRESCRIPTION_RX","OTC","PHONE","EMAIL","STERILE_DEVICE","STERILE_PRIOR_TO_USE","URL","STERILE_METHOD","TO_BE_DELETED","TO_BE_DELETED_ON","DELETE_REASON","REPLACEMENT_ID","PACKAGE_DI_NUM","CONTAINS_DI_NUM","PKG_QUANTITY","PRIVATE_FLAG","IS_PRIVATE","PRIVATE_TYPE","UNIT" FROM ( SELECT COALESCE(tpc.CAT_GUDID_ID,tcat.CAT_GUDID_ID) CAT_GUDID_ID,COALESCE(tpc.CREATE_DATE,tcat.CREATE_DATE) CREATE_DATE,GREATEST(NVL(tpc.LAST_MODIFIED_DATE,TO_DATE('31-DEC-1900','DD-MON-YYYY')),tcat.LAST_MODIFIED_DATE) 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_BE_DELETED_ON) TO_BE_DELETED_ON,COALESCE(tpc.DELETE_REASON,tcat.DELETE_REASON) DELETE_REASON,COALESCE(tpc.REPLACEMENT_ID,tcat.REPLACEMENT_ID) REPLACEMENT_ID,COALESCE(tpc.PACKAGE_DI_NUM,tcat.PACKAGE_DI_NUM) PACKAGE_DI_NUM,COALESCE(tpc.CONTAINS_DI_NUM,tcat.CONTAINS_DI_NUM) CONTAINS_DI_NUM,COALESCE(tpc.PKG_QUANTITY,tcat.PKG_QUANTITY) PKG_QUANTITY,COALESCE(uuid.PRIVATE_FLAG,tcat.PRIVATE_FLAG) PRIVATE_FLAG, COALESCE(uuid.IS_PRIVATE,tcat.IS_PRIVATE) IS_PRIVATE, COALESCE(uuid.PRIVATE_TYPE,tcat.PRIVATE_TYPE) PRIVATE_TYPE, COALESCE(tpc.UNIT,tcat.UNIT) UNIT FROM TPC_GUDID tpc LEFT JOIN TCAT_GUDID tcat ON tcat.CAT_GUDID_ID = tpc.CAT_GUDID_ID inner JOIN TPC_UUID uuid ON tpc.CAT_GUDID_ID = uuid.CAT_UUID_ID AND SIGN(tpc.CAT_GUDID_ID )!= -1 ) cat_tab UNION ALL SELECT tpc.CAT_GUDID_ID,tpc.CREATE_DATE,tpc.LAST_MODIFIED_DATE,tpc.DEVICE_RECORD_STATUS,tpc.DI_RECORD_PUBLISH_DT,tpc.COMMERCIAL_DIST_END_DT,tpc.COMMERCIAL_DIST_STATUS,tpc.PRIMARY_DI_NUMBER,tpc.DEVICE_TYPE,tpc.ISSUING_AGENCY,tpc.BRAND_NAME,tpc.VERSION_MODEL_NUMBER,tpc.CATALOG_NUMBER,tpc.COMPANY_NAME,tpc.DEVICE_COUNT,tpc.DEVICE_DESC,tpc.DME_EXEMPT,tpc.PRE_MKT_EXEMPT,tpc.DEVICE_HCTP,tpc.DEVICE_KIT,tpc.DEVICE_COMBINATION_PRODUCT,tpc.SINGLE_USE,tpc.LOT_BATCH,tpc.SERIAL_NUMBER,tpc.MANUF_DATE,tpc.EXPIRATION_DATE,tpc.DONATION_ID_NUMBER,tpc.LABELED_CONTAINS_NRL,tpc.LABELED_CONTAINS_NO_NRL,tpc.MRI_SAFETY_STATUS,tpc.PRESCRIPTION_RX,tpc.OTC,tpc.PHONE,tpc.EMAIL,tpc.STERILE_DEVICE,tpc.STERILE_PRIOR_TO_USE,tpc.URL,tpc.STERILE_METHOD,tpc.TO_BE_DELETED,tpc.TO_BE_DELETED_ON,tpc.DELETE_REASON,tpc.REPLACEMENT_ID,tpc.PACKAGE_DI_NUM,tpc.CONTAINS_DI_NUM,tpc.PKG_QUANTITY,tpc.PRIVATE_FLAG,tpc.IS_PRIVATE,tpc.PRIVATE_TYPE,tpc.UNIT FROM TPC_GUDID tpc WHERE SIGN(tpc.CAT_GUDID_ID )= -1