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 |
| Sending Facility | MSH-4 | MSH-4 | MSH-4 | Namespace ID | FACILITY_CODE | VARCHAR2 (20) | NOT NULL |
| Message Type | MSH-9 | MSH-9 | MSH-9.0 | Message Type | MESSAGE_TYPE | VARCHAR2 (8) | NOT NULL |
| MSH-9.1 | Trigger Event | TRIGGER_EVENT | VARCHAR2 (8) | ||||
| Patient Identifier List | PID-3 | PID-3 | PID-3 | ID Number | PATIENT_NO | VARCHAR2 (20) | NOT NULL |
| Patient Name | PID-5 | PID-5 | PID-5.0 | Family Name | PATIENT_LNAME | VARCHAR2 (194) | NULLABLE |
| PID-5.1 | Given Name | PATIENT_FNAME | VARCHAR2 (30) | NULLABLE | |||
| PID-5.2 | Middle Name | PATIENT_MNAME | VARCHAR2 (26) | NULLABLE | |||
| Date/Time of Birth | PID-7 | PID-7 | PID-7 | PATIENT_BDATE | VARCHAR2(324) | NULLABLE | |
| Patient Account Number | PID-18 | PID-18 | PID-18 | ID Number | PATIENT_ACCT_NO | VARCHAR2(20) | NULLABLE |
| Patient Class | PV1-2 | PV1-2 | PV1-2 | PATIENT_CLASS | CHAR (1) | NULLABLE | |
| Assigned Patient Location | PV1-3.0 | AIL-3.0 | PV1-3.0 | Point of Care | DEPT_CODE | VARCHAR2 (20) | NULLABLE |
| PV1-3.1 | AIL-3.1 | PV1-3.1 | Room | ROOM | VARCHAR2 (20) | NULLABLE | |
| PV1-3.2 | AIL-3.2 | PV1-3.2 | Bed | BED | VARCHAR2 (20) | NULLABLE | |
| PV1-3.3 | AIL-3.3 | PV1-3.3 | Facility | FACILITY_CODE | VARCHAR2 (20) | NULLABLE | |
| Visit Number | PV1-19 | PV1-19 | PV1-19 | ID Number | VISIT_NO | VARCHAR2 (20) | NULLABLE |
| Admit Date | PV1-44 | -o- | -o- | ADMIT_DATE | VARCHAR2 (24) | NULLABLE | |
| Discharge Date | PV1-45 | -o- | -o- | DISCHARGE_DATE | VARCHAR2(24) | NULLABLE | |
| Prior Patient ID | MRG-1 | -o- | -o- | ID Number | PRIOR_PATIENT_NO | VARCHAR2(20) | NULLABLE |
| Prior Patient Acct Number | MRG-3 | -o- | -o- | ID Number | PRIOR_PATIENT_ACCT_NO | VARCHAR2 (20) | NULLABLE |
| Schedule / Study ID | -o- | SCH-5 | CSR-1 | Identifier | CASE_NO | VARCHAR2 (20) | NULLABLE |
| Filler Status Code | -o- | SCH-25 | -o- | Identifier | CASE_STATUS | VARCHAR2 (20) | NULLABLE |
| Scheduled Date and Time | -o- | TQ1-7 | CSS-2 | SCHEDULE_DATE | VARCHAR2 (24) | NULLABLE | |
| Filler Status Code | -o- | AIL-12 | -o- | Identifier | LOCATION_STATUS | VARCHAR2 (20) | NULLABLE |
| Personnel Resource / Attending Doctor | -o- | AIP-3 | PV1-7 | ID Number | DOCTOR _ID | VARCHAR2 (15) | NULLABLE |
| Filler Status Code | -o- | AIP-12 | -o- | Identifier | DOCTOR _STATUS | VARCHAR2 (20) | NULLABLE |
| PROCESS_FLAG | CHAR (1) |
NOT NULL, DEFAULT to 0. State whether:
|
|||||
| PROCESS_DESC | VARCHAR2 (194) |
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 | ADT SEGMENT (ADT SYSTEM) | 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. |
|||||
| CASE_HEADER_ID | NUMBER | FK to CASE_HEADER_ID of CLOVERLEAF_CASE_HEADER | |||||
| Universal Service ID | -o- | AIS-3.0 | OBR-44.0 | Identifier | PROCEDURE_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| -o- | AIS-3.1 | OBR-44.1 | Text | PROCEDURE_DESC | VARCHAR2 (199 BYTES) | NULLABLE | |
| Patient Identifier List | -o- | AIS-3.2 | OBR-44.2 | Alternate Identifier | CHARGE_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| Filler Status Code | -o- | AIS-10 | -o- | 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-4 | ITEM_NO | VARCHAR2 (20 BYTES) | NULLABLE | |
| Observation Value | -o- | -o- | OBX-5.0 | Channel Identifier | SERIAL_LOT_NO | VARCHAR2 (20 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.1 | Channel Identifier | QUANTITY | VARCHAR2 (13 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.2 | Channel Identifier | UOM | VARCHAR2 (14 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.3 | Channel Identifier | EXP_DATE | VARCHAR2 (24 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.4 | Channel Identifier | LOCATION_CODE | VARCHAR2 (20 BYTES) | NULLABLE |
| Observation Value | -o- | -o- | OBX-5.5 | Channel Identifier | DEPT_CODE | VARCHAR2 (20 BYTES) | NULLABLE |