02/02/96
L. Garden DRAFT
The following is a description of some of the tables in the hospital data model that is available to you for the project. It does not seem to correspond exactly to the current database, but is helpful in trying to understand the content of the data. --Pete Szolovits, March 1997.
PAT_TEST_HIST. PAT_TEST_ID NOT NULL NUMBER(15) PK for PAT_TEST_HIST, generated using the ORACLE sequence, PAT_TEST _HIST_SEQ. Leftmost column in concatenated PK for related patient result tables PAT_TEST_SRC, PAT_TEST_TXT, PAT_ORG_HIST, PAT_ORG_SENSTV_HIST. PAT_NUM NOT NULL NUMBER(9) Patient identifier. CERNER provides for a 13-digit Medical Record Number and a 13-digit Financial Number. Both are transmitted to the Lab interface, the latter with trailing 3-digit pat_fin_enc_num. The interface writes the last 9 digits of the Medical Record Number to PAT_NUM, Children's 7-digit Medical Record Number prefixed by two zero-fill digits. EVENT_START_DT_TM NOT NULL DATE Specimen collected date/time. EVENT_DT_TM_KEY NOT NULL NUMBER(12) 9's complement of EVENT_START_DT_TM. EVENT_START_DT_TM = TO_DATE(:q_event_start_dt_tm, 'YWYMM DDH H24M I') EVENT_DT_TM_KEY = 999999999999 - TO_NUMBER(:q_event_start_dt_tm) Note from review of CERNER data elements: CERNER maintains 9's complement fields, REV_DRAWN_DATE and REV_DRAWN _TIME, for DRAWN_DATE and DRAWN_TIME respectively. TEST_ID NOT NULL NUMBER(7) PK to TEST, FK in PAT_TEST_HIST. TEST_ID is the authoritative institution-wide test identifier, created by the ORACLE sequence TEST_SEQ. Remote test codes, test identifiers assigned by remote systems, are mapped to institution-wide test identifiers. The TEST_ID for a supergroup test record is the TEST_ID that corresponds to the CERNER supergroup test number transmitted by the results interface in the common transaction header. The TEST_ID for a group test record is the TEST_ID that corresponds to the CERNER test number transmitted by the results interface in the common transaction header. The TEST_ID for a detail test record is the TEST_ID that corresponds to the CERNER test number transmitted by the results interface in the subtype 1 detail transaction record. If a record does not exist for the remote_test_cd in both test reference tables (the mapping table, REMOTE_TEST, and the institu-tion table, TEST), TEST_ID is set to 0. For the special case of the supergroup records created by the interface to "root" AP tests, TEST_ID is set to 20. TEST_ABBR VARCHAR2(1 5) TEST_ABBR is the authoritative institution-wide test mnemonic or short name. The TEST_ABBR for a given PAT_TEST_HIST record is the ABBR retrieved from the TEST reference table for PAT_TEST_HIST.TEST_ID. If a record does not exist for the remote_test_cd in both test reference tables (the mapping table, REMOTE_TEST, and the institu-tion table, TEST), TEST_ABBR is set to the untranslatable remote_ test_cd, prefixed by the flag NF-. For the special case of the supergroup records created by the interface to "root" AP tests, TEST_ABBR is set to PAP CASE'. EVENT_STOP_DT_TM DATE The ending date/time for an interval test. Not in current use. NULL. TEST_PRTY_CD VARCHAR2(2) Test priority code. CERNER transmits both collection and reporting priority codes to the Lab interface; the collection priority code is posted to ORACLE. COLL_PRI_CODE (CERNER) is the two-character code that is entered during the order entry process to indicate the priority of the tests being ordered. All collection priority codes are validated against the PathNet System Reference table 0520. The two-character code is then converted to a one digit code (CERNER COLL_PRI) that allows the system to interpret the urgency of the order. This numeric field is used to assign the order to the appropriate Order Explosion Manager so that high priority orders may be pro-ceased ahead of routine orders. Current valid values from SR0520_1: A ROUTINE RT ROUTINE DS DAY SURGERY RU RUSH EM EMERGENCY ST STAT FT FUNCTIONAL TEST TL TIMED LEVEL IC ICU LAB REMOTE_SYSTEM_CD NOT NULL VARCHAR2(5) Abbreviation for the name of the ancillary or remote system that generated the test result. PK component in reference tables REMOTE_TEST, REMOTE_SRC, REMOTE_ORG, REMOTE_MEDCTN. REMOTE_SYSTEM_CD for CERNER is 'C'. REMOTE_EVENT_NUM NOT NULL VARCHAR2(15) Remote system identifier. For CERNER lab results, the accession number. CERNER's accession number (ACC_NBR) is the system assigned number used to identify and track each specimen obtained from a patient from collection through analysis and reporting. -; CERNER accession numbers are generated in two basic formats: Standard accession numbers are in the format W-DDD-MMMMwhere W = the calendar year DDD = the Julian day MMMM = the sequence number which starts over each day. Prefixed accession numbers, used by Genetics and Anatomic Pathology, are in the format M-W-NNNNN-A where AA = an alphabetic prefix denoting a culture type or functional area of Anatomic Pathology YY = the calendar year MMMMM= the sequence number which starts over each year, and which when prefixed with year, is unique within type/functional area. A = optional alphabetic addendum suffix, Anatomic Pathology only. SUPERGRP_NUM NUMBER(1 5) The SUPERGRP_NUM is an identifier which groups all tests together which belong to the same root level test. The value of SUPERGRP_NUM is the PAT_TEST_ID of the related root level test. If the test belongs to a supergroup, the SUPERGRP_NUM is the PAT_TEST _ID of the supergroup record. If the test belongs to a group, the SUPERGRP_NUM is the PAT_TEST_ID of the group record. If the test is not part of a group or supergroup, the SUPERGRP_NUM is the PAT_TEST_ID of the detail record. GRP_NUM NUMBER(9) The GRP_NUM is an identifier which provides an intermediate level of grouping. Supergroup records have GRP_NUM set to 0; group and detail level test records use CERNER order number to construct GRP_NUM. For the general case, the order number manually entered or system assigned at order entry is the GRP_NUM. For AP tests, the last five digits of the order number are concatenated with the last four digits of the remote test number to differentiate GRP_NUM. DETAIL_NUM NUMBER(3) DETAIL_NUM sequences detail level tests within a group for retrieval and display purposes. Sequence numbers are assigned as follows: 1. For all detail level records except culture test type records and AP textual report SNOMED diagnostic records, DETAIL_NUM is the transmitted test sequence number from the CERNER PO file. Note that detail tests are sequenced within groups: a rest for orchard grass ordered as part of RGRASSES is assigned the sequence number 2; a rest for orchard grass ordered as part of RINHALANTS is assigned the sequence number 6. 2. Culture test type detail records are sequenced as they are inserted into PAT_TEST_HIST, beginning with DETAIL_NUM 1. The counter, culture_det_num, is reinitialized for each result transmission. 3. If AP textual reports include SNOMED (Standard Nomenciature Of Medicine) diagnostic codes, each code becomes the RSLT_VAL for a new detail level record, and these detail records are sequenced as they are inserted into PAT_TEST_HIST, beginning with DETAIL_NUM 100. The counter, snomed_seq, is re-initialized for each result transmission. DETAIL_NUM is 0 for supergroup and group level tests. SUPERGRP_TEST_ID NUMBER(7) The TEST_ID from the PAT_TEST_HIST SUPERGRP record if the test is part of a supergroup. 0 if the test is not part of a supergroup. GRP_TEST_ID NUMBER(7) The TEST_ID from the PAT_TEST_HIST GRP record if the test is part of a group. 0 if the test is not part of a gruop. PARENT_ID NUMBER(1 5) The PAT_TEST_ID from the PAT_TEST_HIST record of the immediate parent test if the test has a parent. 0 if the test is a root-level test. CHILD_LEVEL_VAL NUMBER(2) Level indicator stores the number of levels of tests below this test. Supergroup tests have a child_level_val of 2; group level tests have a child_level_val of 1; detail level tests have a child_level_val of 0. ROOT_STATUS VARCHAR2(1 ) Flag to show whether the record is the root test record. Y (Yes) if the record is the root record, NULL if the record has at least one level of parent record. Supergroup records, group records, detail records can be root records. DATA_CLS_CD VARCHAR2(2) S for Standard, D for Document. RSLT_VAL VARCHAR2(8) . H (High) RSLT_VAL > REF_HIGH_VAL numeric results See REF_LOW_VAL and REF_HIGH_VAL. C (Critical) RSLT_VAL <= CRITICAL_LOW_VAL OR numeric results RSLT_VAL >= CRITICAL_HIGH_VALFor numeric results, CRITICAL_LOW_VAL and CRITICAL_HIGH_VAL are the age/sex adjusted critical low and high values that define the critical reference ranges for this test, at the time that the test is resulted, if these values have been defined in CERNER's PDM Numeric Range Segment. Criticai reference values are not trans-mitted to the interface. Examples. 1. ABS LYMPH has a CRITICAL_LOW (1.0) and a CRITICAL_HIGH (15.0) and no normal ranges defined in CERNER's PDM Numeric Range Segment. If the ABS LYMPH is resulted .91, ABN_STATUS ='C', and, since no normal ranges have been defined for ABS LYMPH, REF_LOW_VAL will be NULL and REF_HIGH_VAL will be NULL. 2. ATYP LYMPH has a CRITICAL_HIGH (15), NORMAL_LOW (0) and NORMAL_HIGH (6) defined in CERNER's PDM Numeric Range Segment. If the ATYP LYMPH is resulted 26, ABN_STATUS ='C', REF_LOW_VAL = 0, REF_HIGH_VAL = 6. N (Normal) REF_LOW_VAL <= RSLT_VAL <= REF_HIGH_VAL OR numeric RSLT_VAL= predefined normal result alpha Examples. 1. PROTEIN URINALYSIS has 6 alpha responses defined in CERNER's PDM Alpha Response Segment. 'NEGATIVE', 'TRACE', and 'NOT PERF' are defined normal responses. '+', '++', and '+++' are defined non-normal responses. If the PROTEIN URINALYSIS is resulted'TRACE', ABN_STATUS ='N'. 2. PHOSPHEROUS, SERUM is a numeric test, with NORMAL_LOW 3.5, NORMAL_HIGH 4.5, CRITICAL_LOW 1 and CRITICAL_HIGH 8, defined in CERNER's PDM Numeric Range Segment. Four additional responses are defined in the Alpha Response Segment. '<10','QNS', 'NOT DONE', and 'FOOTNOTE' are all defined normal responses. If the PHOSPHEROUS, SERRUM result is'QNS', ABN_STATUS ='N'. ABN_TYPE_CD VARCHAR2(2) The type of abnormal result (abnormal due to AGE range check, SEX range check). From HL7 spec. Not implemented. NULL. REF_LOW_VAL VARCHAR2(1 0) For numeric results, the age/sex adjusted low value of the normal reference range for this test, at the time that the test is resulted, if this value has been defined in CERNER's PDM Numeric Range Segment. . . Simple results, of type alpha, freetext, numeric, calculated, date, system reference, and alpha interpretation are stored in RSLT_VAL. RSLT_VAL also carries the SNOMED diagnostic code for AP textual reports. RSLT_VAL is NULL for supergroup and group level test records, and for detail level records with textual results. Textual results, and textual interpretation results are inserted into PAT_TEST_TXT. Results for micro susceptiblity testing are in PAT_ORG_HIST and PAT_ORG_SENSTV_HIST. RSLT_UNIT_TXT VARCHAR2(1 0) Textual description of the unit of measure that RSLT_VAL is expressed in. Defined in CERNER's PDM Numeric Map Segment. RSLT_TYPE_CD VARCHAR2(1 ) CERNER result types describe both the format of the test result and the data validation rules for the result. (A) The Alpha result type allows the entry of up to 8 alphanumeric characters to be validated against a predefined list of responses, set up through the PDM Alpha Responses Segment. (S) The System Reference result type allows the entry of up to 8 alphanumeric characters to be validated against a designated field in a System Reference table. (F) The Freetext result type allows the entry of up to 8 alphanumeric characters, unformatted and unvalidated. (T) The Text result type allows the entry of an unlimited number of characters. Templates can be created to format the result. (N) The Numeric result type formats a numeric map with maximum digits, minimum digits, decimal places and units defined for validation, and allows for the definition of normal and critical ranges for status checking. (C) (O) The Calculated/Online Computation types are derivative types. A calculated result type is a function of an equation set up in the PDM Equations Segment. Finally, calculated results are validated against any numeric ranges defined in the PDM Numeric Ranges Segment. (D) The Date result type formats a date map for validation. (I) The Interpretation result type is a derivative type. The system evaluates the performed and verified results of component procedures against predefined hash interpretation patterns. The result type is alpha or textual. RSLT_TYPE_CD describes the RSLT_VAL for the test event inserted into PAT_TEST_HIST and not the default type for the test. CERNER has implemented a type override function, Convert to Alpha, to allow users to enter an alpha result where a numeric is expected. Note that when a numeric test has an alpha result, e.g., at result entry the technician converts to alpha to select the notation 'QNS' (Quantity Not Sufficient), RSLT_TYPE_CD will display as 'A' (Alpha). RSLT_TYPE_CD is NULL for supergroup and group level test records. ABN_STATUS VARCHAR2(2) L (Low) RSLT_VAL < REF_LOW_VAL numeric results For alpha results for which a single default normal response has been defined, that response is stored in REF_LOW_VAL. Examples include 'NORMAL', 'NEGATIVE', 'NON REAC'. NULL for supergroup and group level test records. REF_HIGH_VAL VARCHAR2(1 0) For numeric results, the age/sex adjusted high value of the normal reference range for this test, at the time that the test is resulted, if this value has been defined in CERNER's PDM Numeric Range Segment. NULL for supergroup and group level test records. REF_TYPE_CD VARCHAR2(1 ) DOCMNT_PNTR_ID NUMBER(1 2) In the original design, a pointer to document text stored in external VMS file. For AP tests classified as documents, the textual result records were to have been preprocessed by the interface and inserted temporarily with partition records into the ORACLE table DOCMNT_TXT, and then indexed with the tracking record in DOCMNT_QUEUE. When the Document Handling System wrote the document from DOCMNT_TXT to a VMS file, DHS was to have linked the PAT_TEST_HIST record with the document through DOCMNT_PNTR_ID. Not implemented. NULL. ORD_ID NUMBER Order identifier. FK in PAT_TEST_HIST, PK in ORDER table that was to have been the principal entity in an order entry subsystem. Not implemented. NULL. ORD_PROV_NUM NUMBER Provider number for the clinician who ordered the procedure. FK in PAT_TEST_HIST, PK in ADT.PROVIDER. Not in current use. NULL. ORD_LOCTN VARCHAR2(4) Ordering location entered at order time, validated against CERNER's 0100 table and stored on the CERNER PO record. RSLT_STATUS VARCHAR2(2) Results with statuses V (verged) and C (previously verified corrected) are transmitted from CERNER to the Lab interface. Results that have a status of pending or performed but unverified are not released to the interface. Note that preliminary micro reports must be verified before they are released to the interface, even though they will be superseded later by final reports. CONFID_STATUS VARCHAR2(1 ) Flag to limit access to certain test results (e.g., HIV results). Not implemented, see RSLT_SCR. CMNT_STATUS VARCHAR2(1 ) Flag set to 'Y' if there are related comment records in PAT_TEST_TXT. ARCH_STATUS VARCHAR2(1 ) Flag set to 'Y' if related records have been archived. Not implemented. NULL. RSLT_DT_TM DATE For detail level records only. Performed Date/Time if available; otherwise Specimen Collected Date/Time. DOB_STATUS VARCHAR2(1 ) Indicator to show that change has been posted in dote since procedure was resulted, and that reference ranges and abnormal status may need to be reviewed. Not currently implemented. RSLT_SCR NUMBER(2) Numeric value which values sensitivity or administrative use of certain test results, i.e., HIV results. Note that clinicians access PAT_TEST_HIST through the view PAT_TEST_HISTV, which restricts access to rows with rsit_scr= 0. REMOTE_RPT_DT_TM DATE Transmitted Date/Time for supergroup and group level records; Verified Date/Time for detail level records. UPDT_DT_TM NOT NULL DATE SYSDATE at the time of the last ORACLE insert or update. PAT_TEST_SRC. PAT_TEST_ID NOT NULL NUMBER(15) SRC_ID NOT NULL NUMBER(7) Identifier for the specimen or source. FK in REMOTE_SRC mappping table; PK in institution-wide SOURCE table. For culture sources, if the source has no entry in REMOTE_SRC, src_id = - remote_src_num. For AP specimens, if the source has no entry in REMOTE_SRC, src_id = FREE_TXT_CD = 452 REF_NUM NUMBER(9) For AP specimen tests only, the CERNER order number. TXT VARCHAR2(80) For AP specimen tests only, a detailed description of the specimen for free text specimens. UPDT_DT_TM NOT NULL DATE SYSDATE at the time of the last ORACLE insert or update. PAT_TEST_TXT. PAT_TEST_ID NOT NULL NUMBER(15) PAT_TEST_ID of the related PAT_TEST_HIST detail level record. TXT_SEQID NOT NULL NUMBER(3) Sequential number assigned to the text record by the interface to allow the ordered retrieval of textual result and comment records as transmitted from CERNER. Note that TXT_SEQ_ID is unique within PAT_TEST_ID, across TXT_TYPE_CD. TXT_TYPE_CD NOT NULL VARCHAR2(2) Functional type of textual record. R = Result C = Comment on result or on order TXT NOT NULL VARCHAR2(80) Result text includes notations like NO SALMONELLA OR SHIGELLA ISOLATED FEW GRAM NEGATIVE RODS QUESTION OF HEMOPHILIUS and records, that read sequentially, constitute comprehensive lab reports. Comment text is used to record changes in a clinician's order <TEST MNEMONIC> ORDERED BY <CLINICIAN> AT <DATE/TIME> to record the repetition of tests and calls to floors TEST REPEATED. CALLED. to record problems with contaminated specimens or specimens in insufficient quantity and requests for new specimens CONTAMINATED SPECIMEN. QNS TO REPEAT. RN NOTIFIED. REQUEST NEW SAMPLE. SPECIMEN MODERATELY HEMOLYZED. to identify heading and footing blocks in a textual report and distinguish these from the textual results Report Date: <date> Attending's Signature: <clinician> (electronic signature) CLINICAL GENETICS PROGRAM GENETICS DIVISION and to provide an audit trail of corrections to simple results <old RSLT_VAL> CHANGED TO <new RSLT_VAL> 0N <DATE/TIME>. UPDT_DT_TM NOT NULL DATE SYSDATE at the time of the last ORACLE insert or update. PAT_ORG_HIST. PAT_TEST_ID NOT NULL NUMBER(15) Leftmost column of the composite PK. ORG_SEQ_ID NOT NULL NUMBER(2) Second column of the composite PK. isolate sequence number. For micro processing, the isolate sequence number from CERNER; for culture processing, isolate sequence number is generated by the interface and reset to 1 for each detail level test. ORG_ID NUMBER(7) Identifier for the organism. FK in PAT_ORG_HIST, FK in REMOTE_ORG mapping table, PK in institution-wide ORGANISM table. If there is no entry for the organism in REMOTE_ORG, org_id = - remote_org_num RSLT_DT_TM DATE For records inserted in culture processing, performed date and time if available, otherwise specimen collected date/time. For records inserted in micro processing, specimen collected date/ time. CO LO NY_CNT_TXT VARCHAR2(20) The amount of organism growth in this culture. A text string. Not in current use. NULL. COLONY_CNT_VAL NUMBER(6) The number of colonies of this organism that have grown in this culture. Not in current use. NULL. TXT_STATUS VARCHAR2(1 ) Flag set to 'Y' if there are related textual records in another table. Not in current use. NULL. RPT_TYPE_CD VARCHAR2(1 ) Type of micro report. Valid values are: S = Susceptiblity G = Stain P = Preliminary F = Final UPDT_DT_TM NOT NULL DATE SYSDATE at the time of the last ORACLE insert or update. PAT_ORG_SENSTV_HIST. PAT_TEST_ID NOT NULL NUMBER(15) Leftmost column of the composite PK. ORG_SEQ_ID NOT NULL NUMBER(2) Second column of the composite PK. Isolate sequence number. SENSTV_TYPE_CD NOT NULL VARCHAR2(10) Third column of the composite PK. Code for the methodology used to identify the antibiotics that will be effective in the treatment of the patient infection. Methodologies include: MIC Minimum Inhibitory Concentration KB Kirby Bauer MBC Minimum Bacteriological Concentration MEDCTN_ID NOT NULL NUMBER(7) Fourth (rightmost) column of the composite PK. Identifier for the antibiotic tested against the isolate. FK in PAT_ORG_SENSTV_HIST, FK in REMOTE_MEDCTN mapping table, PK for the authoritative institution-wide MEDICATION table. If there is no entry for the antibiotic in REMOTE_MEDCTN, medctn_id = -remote_medctn_num. SENSTV_TXT_VAL VARCHAR2(20) Textual result value for susceptibility testing. SENSTV_INTERP_CD VARCHAR2(2) Interpretive result value for susceptibility testing. Valid values are: R Resistant S Sensitive I Intermediate (equivalent to Moderately Sensitive, choice of interp code is antibiotic dependent) MS Moderately Senstive (equivalent to Intermediate, choice of interp code is antibiotic dependent) NA Not applicableCannot report out the interp or MIC UPDT_DT_TM NOT NULL DATE SYSDATE at the time of the last ORACLE insert or update. DOCMNT_QUEUE. DOCMNT_QUE_ID NOT NULL NUMBER(5) PK for DOCMNT_QUEUE, generated using Oracle sequence, DOCMNT_QUE_SEQ. This attribute may be widened in the future, because max(docmnt_que_id) = 41475 on 212/96. REQUEST_DT_TM NOT NULL DATE SYSDATE at the time of ORACLE insert. PAT_TEST_ID NOT NULL NUMBER(15) PAT_TEST_ID of the ordered group procedure. DOCMNT_TYPE_CD VARCHAR2(1 3) Mnemonic for the ordered group procedure. This should be the TEST_ABBR of the ordered group procedure, e.g.,autopsies will have DOCMNT_TYPE_CD of 'AUF' or 'PAD'. Note that the PAP CASE' fake supergroup record is a grouping mechanism only, and PAP CASE' is not generally the DOCMNT_TYPE_CD. BEGIN_DT_TM NOT NULL DATE Date and time received in lab. Depending on the workload and on the test type, it may be the time that the patient presented for the procedure (e.g., in Neurophysiology, the time that a patient checks in for an EEG) or the time that the specimen is catalogued in the lab. It is not the time that the specimen is harvested. Because we do not have an order communications system, this time has different meanings depending on lab procedure and workload. PAT_NUM NUMBER(9) Medical record number. CHECKBOX VARCHAR2(1 ) Valued 'N'. The original system design defined a domain with values 'N' and 'Y'. The lab results repository was to have provided temporary storage only for anatomic pathology documents which were to have periodically offloaded from Oracle to an RMS based document handling system. There are no plans at this time to offload anatomic pathology documents to any other file system or database. UPDT_DT_TM DATE SYSDATE at the time of ORACLE insert. DOCMNT_TXT. DOCMNT_QUE_ID NOT NULL NUMBER(5) Leftmost component of the composite PK. See full descriptionn of PK for DOCMNT_QUEUE. TXT_SEQ_ID NOT NULL NUMBER Third component of the composite PK. Detaii result line sequence within a partition. TXT NOT NULL VARCHAR2(80) Cemer free text result record. UPDT_DT_TM NOT NULL DATE SYSDATE at the time of ORACLE insert. PAT_TEST_ID NUMBER(1 5) PATH_TEST_ID of the related PAT_TEST_HIST record for the partition procedure. PARTITION_SEQ_ID NUMBER(5) Second component of the composite PK. Charting sequence number from Cerner which defines the order in which partitions in an anatomic pathology report display and print. This is NOT the sequence in which anatomic pathology results are transmitted.