HL7 message and MSCM staging table
| HL7 MESSAGE | MSCM STAGING TABLE (CLOVERLEAF_CASE_HEADER) | ||||||
|---|---|---|---|---|---|---|---|
| FIELD NAME | ADT SEGMENT (ADT SYSTEM) | SIU SEGMENT (SCHEDULING SYSTEM) | CSU SEGMENT (DATA MGT SYSTEM) | SEGMENT SUBCOMPONENT | COLUMN NAME | DATA TYPE | CONSTRAINT / COMMENT |
| CASE_HEADER_ID | NUMBER |
PK, UNIQUE, NOT NULL. Auto-generated sequence number. |
|||||
| Sending Application | MSH-3 | MSH-3 | MSH-3 | Namespace ID | SENDING_APP_ID | VARCHAR2 (20 BYTES) | NOT NULL |
| Facility | MSH-4 | MSH-4 | MSH-4 | Namespace ID | FACILITY_CODE | VARCHAR2 (20 BYTES) | NOT NULL |
| Message Type | MSH-9 | MSH-9 | MSH-9 |
Message Type Trigger Event |
MESSAGE_TYPE TRIGGER_EVENT |
VARCHAR2 (8 BYTES) VARCHAR2 (8 BYTES) |
NOT NULL |
| Patient Identifier List | PID-3 | PID-3 | PID-3 | ID Number | PATIENT_NO | VARCHAR2 (20 BYTES) | NOT NULL |
| Patient Name | PID-5 | PID-5 | PID-5 |
Family Name Given Name Middle Name |
PATIENT_LNAME PATIENT_ FNAME PATIENT_MNAME |
VARCHAR2 (194 BYTES) VARCHAR2 (30 BYTES) VARCHAR2 (26 BYTES) |
NULLABLE |
| Date/Time of Birth | PID-7 | PID-7 | PID-7 | PATIENT_BDATE | VARCHAR2(24 BYTES) | NULLABLE | |
| Patient Account Number | PID-18 | PID-18 | PID-18 | ID Number | PATIENT_ACCT_NO | VARCHAR2(20 BYTES) | NULLABLE |
| Patient Class | PV1-2 | PV1-2 | PV1-2 | PATIENT_CLASS | CHAR (1 BYTE) | NULLABLE | |
| Assigned Patient Location | PV1-3.0 | AIL-3.0 | PV1-3.0 | Point of Care | DEPT_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| PV1-3.1 | AIL-3.1 | PV1-3.1 | Room | ROOM | VARCHAR2 (20 BYTES) | NULLABLE | |
| PV1-3.2 | AIL-3.2 | PV1-3.2 | Bed | BED | VARCHAR2 (20 BYTES) | NULLABLE | |
| PV1-3.3 | AIL-3.3 | PV1-3.2 | Facility | FACILITY_CODE | VARCHAR2 (20 BYTES) | NULLABLE | |
| Visit Number | PV1-19 | PV1-19 | PV1-19 | ID Number | VISIT_NO | VARCHAR2 (20 BYTES) | NULLABLE |
| Admit Date | PV1-44 | -o- | -o- | ADMIT_DATE | VARCHAR2 (24 BYTES) | NULLABLE | |
| Discharge Date | PV1-45 | -o- | -o- | DISCHARGE_DATE | VARCHAR2(24 BYTES) | NULLABLE | |
| Prior Patient ID | MRG-1 | -o- | -o- | ID Number | PRIOR_PATIENT_NO | VARCHAR2(20 BYTES) | NULLABLE |
| Prior Patient Acct Number | MRG-3 | -o- | -o- | ID Number | PRIOR_PATIENT_ACCT_NO | VARCHAR2 (20 BYTES) | NULLABLE |
| Schedule / Study ID | -o- | SCH-5 | CSR-1 | Identifier | CASE_NO | VARCHAR2 (20 BYTES) | NULLABLE |
| Filler Status Code | -o- | SCH-25 | -o- | Identifier | CASE_STATUS | VARCHAR2 (20 BYTES) | NULLABLE |
| Scheduled Date and Time | -o- | TQ1-7 | CSS-2 | SCHEDULE_DATE | VARCHAR2 (24 BYTES) | NULLABLE | |
| Filler Status Code | -o- | AIL-12 | -o- | Identifier | LOCATION_STATUS | VARCHAR2 (20 BYTES)v | NULLABLE |
| Personnel Resource / Attending Doctor | -o- | AIP-3 | PV1-7 | ID Number | DOCTOR _ID | VARCHAR2 (15 BYTES) | NULLABLE |
| Filler Status Code | -o- | AIP-12 | -o- | Identifier | DOCTOR _STATUS | VARCHAR2 (20 BYTES) | NULLABLE |
| PROCESS_FLAG | CHAR (1 BYTE) |
NOT NULL, DEFAULT to 0. State whether:
|
|||||
| PROCESS_DESC | VARCHAR2 (194 BYTES) |
NULLABLE. Description of errors or constraints. |
|||||
| CREATE_DATE | DATE |
NOT NULL. Auto-generated creation date upon insertion of record. |
|||||
| LAST_UPDATE_DATE | DATE |
NOT NULL. Auto-generated modified date upon inserting and updating the PROCESS_FLAG. |
|||||
| HL7 MESSAGE | MSCM STAGING TABLE (CLOVERLEAF_CASE_PROC) | |||||
|---|---|---|---|---|---|---|
| FIELD NAME | SIU SEGMENT (SCHEDULING SYSTEM) | CSU SEGMENT (DATA MGT SYSTEM) | SEGMENT SUBCOMPONENT | COLUMN NAME | DATA TYPE | CONSTRAINT / COMMENT |
| CASE_PROC_ID | NUMBER |
PK, UNIQUE, NOT NULL. Auto-generated sequence number. |
||||
| Sending Application | CASE_HEADER_ID | NUMBER | FK to CASE_HEADER_ID of CLOVERLEAF_CASE_HEADER | |||
| Universal Service ID | AIS-3 | OBR-44.0 | Identifier | PROCEDURE_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| Message Type | AIS-3.1 | OBR-44.1 | Text | PROCEDURE_DESC | VARCHAR2 (199 BYTES) | NULLABLE |
| Patient Identifier List | AIS-3.2 | OBR-44.2 | Alternate Identifier | CHARGE_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| Filler Status Code | AIS-10 | Identifier | PROCEDURE_STATUS | VARCHAR2 (20 BYTES) | NULLABLE | |
| HL7 MESSAGE | MSCM STAGING TABLE (CLOVERLEAF_CASE_PROC_ITEM) | ||||||
|---|---|---|---|---|---|---|---|
| FIELD NAME | ADT SEGMENT (ADT SYSTEM) | SIU SEGMENT (SCHEDULING SYSTEM) | CSU SEGMENT (DATA MGT SYSTEM) | SEGMENT SUBCOMPONENT | COLUMN NAME | DATA TYPE | CONSTRAINT / COMMENT |
| CASE_PROC_ITEM_ID | NUMBER |
PK, UNIQUE, NOT NULL. Auto-generated sequence number. |
|||||
| CASE_PROC_ID | NUMBER | FK to CASE_HEADER_ID of CLOVERLEAF_CASE_HEADER | |||||
| Observation ID | -o- | -o- | OBX-3 | PROCEDURE_CODE | VARCHAR2 (20 BYTES) | NULLABLE | |
| Observation Sub-ID | -o- | -o- | OBX-5.1 | ITEM_NO | VARCHAR2 (20 BYTES) | NULLABLE | |
| Observation Value | -o- | -o- | OBX-5.2 | Channel Identifier | SERIAL_NO | VARCHAR2 (20 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.3 | Channel Identifier | LOT_NO | VARCHAR2 (20 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.4 | Channel Sensitivity and Units | QUANTITY | VARCHAR2 (13 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.5 | Channel Sensitivity and Units | UOM | VARCHAR2 (14 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.6 | Channel Sensitivity and Units | EXP_DATE | VARCHAR2 (24 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.7 | Channel Sensitivity and Units | LOCATION_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.8 | Channel Sensitivity and Units | DEPARTMENT_CODE | VARCHAR2 (20 BYTES) | NULLABLE |