HL7 message and MSCM staging table

MSCM STAGING TABLE

(CLOVERLEAF_CHARGE_HEADER)

HL7 MESSAGE
FIELD NAME COLUMN NAME DATA TYPE CONSTRAINTS / COMMENTS

CSU SEGMENT

(EMR SYSTEM)

SEGMENT

SUBCOMPONENT

CASE_HEADER_ID NUMBER PK, UNIQUE, NOT NULL. Auto-generated sequence number.
Sending Application SENDING_APP VARCHAR (20) NULLABLE. The sending application is SDI MSH-3
Facility FACILITY_CODE VARCHAR (80) NULLABLE MSH-4
Message MESSAGE_TYPE VARCHAR(8) NOT NULL. Message type from POUDI: CSU MSH-9.1 Message Type
Message TRIGGER_EVENT VARCHAR(8) NOT NULL. Message type from POUDI: CSU MSH-9.2 Trigger Event
Case Number CASE_NO VARCHAR(250) NOT NULL CSR-1
Schedule Date SCHEDULE_DATE VARCHAR(24) NOT NULL CSS-2
Patient ID PATIENT_NO VARCHAR(20) NULLABLE PID-3
Patient's Last Name PATIENT_LNAME VARCHAR(250) NULLABLE PID-5.1
Patient's First Name PATIENT_FNAME VARCHAR(250) NULLABLE PID-5.2
Patient's Middle Name PATIENT_MNAME VARCHAR(250) NULLABLE PID-5.3
Date/Time of Birth PATIENT_BDATE VARCHAR(24) NULLABLE PID-7
Patient Account Number PATIENT_ACCT_NO VARCHAR(20) NULLABLE PID-18
Patient Class PATIENT_CLASS CHAR(1) NULLABLE
Patient’s Class values:
  • I – Inpatient
  • O – Outpatient
PV1-2
Patient Assigned Location DEPT_CODE VARCHAR(20) NOT NULL PV1-3.1 Department
Patient Assigned Location ROOM VARCHAR(20) NOT NULL PV1-3.2 Procedure Room
Patient Visit Number VISIT_NO VARCHAR(20) NULLABLE PV1-19
Admit Date ADMIT_DATE VARCHAR(24) NULLABLE PV1-44
Discharge Date DISCHARGE_DATE VARCHAR(24) NULLABLE PV1-45
Practitioner PRACTITIONER_ID VARCHAR(15) NOT NULL OBR-32.1 Employee ID
Practitioner PRACTITIONER_FNAME VARCHAR(30) NULLABLE. OBR-32.2 Practitioner

First Name

Practitioner PRACTITIONER_FNAME VARCHAR(50) NULLABLE. OBR-32.3 Practitioner

Last Name

PROCESS_FLAG CHAR (1)

0 – New record

1 – Processed successfully

2 – Processed with errors

PROCESS_DESC VARCHAR(194) NULLABLE. Updated by CloverLeaf after processing depending on the PROCESS_FLAG. Description of errors or constraints. NOT NULL. DEFAULT to 0. Updated to 1 or 2 by CloverLeaf after processing. State whether:
CREATE_DATE DATETIME NOT NULL. DEFAULT to 0. Updated to 1 or 2 by CloverLeaf after NOT NULL. Auto generated system date upon creation of record.
LAST_UPDATE_DATE DATETIME NOT NULL. DEFAULT to CREATE_DATE. Updated by CloverLeaf after processing.
MSCM STAGING TABLE

(CLOVERLEAF_SUPPLY_PROCEDURE)

HL7 MESSAGE
FIELD NAME COLUMN NAME DATA TYPE CONSTRAINTS/ COMMENTS

CSU SEGMENT

(EMR SYSTEM)

SEGMENT

SUBCOMPONENT

CASE_PROC

_ITEM_ID

NUMBER PK, UNIQUE, NOT NULL. Auto-generated sequence number.
Procedure PROCEDURE_CODE VARCHAR(20) NULLABLE OBR-44.1 Procedure Code
Procedure PROCEDURE_DESC VARCHAR(199) NULLABLE OBR-44.2 Procedure Description
Procedure CHARGE_CODE VARCHAR(250) NULLABLE OBR-44.3 Alternative Charge Code
CPT Modifier PROCEDURE_MOD

_CODE

VARCHAR(250) NULLABLE OBR-45.1 CPT Modifier Code
CPT Modifier PROCEDURE_MOD

_DESC

VARCHAR(250) NULLABLE OBR-45.2 CPT Modifier Description
Procedure Status PROCEDURE_STATUS VARCHAR(1) NOT NULL

Status is F - Final

OBR-25
MSCM STAGING TABLE

(CLOVERLEAF_SUPPLY_PROCEDURE_ITEM)

HL7 MESSAGE
FIELD NAME COLUMN NAME DATA TYPE CONSTRAINTS/ COMMENTS

CSU SEGMENT

(EMR SYSTEM)

SEGMENT

SUBCOMPONENT

CASE_PROC

_ITEM_ID

NUMBER PK, UNIQUE, NOT NULL. Auto-generated sequence number.
Observation Identifier PROCEDURE_CODE VARCHAR(20) NULLABLE OBX-3
Observation Value ITEM_NO VARCHAR(32) NOT NULL

Item Code from MSCM

OBX-5.1 Item Code
Observation Value LOT_NO VARCHAR(30) NULLABLE OBX-5.2 Lot Number
Observation Value SERIAL_NO VARCHAR(30) NULLABLE OBX-5.3 Serial Number
Observation Value QUANTITY VARCHAR(13) NOT NULL OBX-5.4 Quantity
Observation Value UOM VARCHAR(4) NOT NULL OBX-5.5 Unit of Measure
Observation Value EXP_DATE VARCHAR(30) NULLABLE OBX-5.6 Expiration Date
Observation Value LOCATION_CODE VARCHAR(30) NOT NULL

Location Code where the item was pulled from: Supply Location, Procedure Room, or Bone & Tissue Storage.

Format: <DeptCode> - <Location Code>

OBX-5.7 Location
Observation Value CONSIGNMENT_FL VARCHAR(1) NOT NULL

Consignment flag of the item when the case is released.

Values:

Y - Consigned item

N - Not consigned item

OBX-5.8 Consignment Flag
Observation Value IMPLANT_FL VARCHAR(1) NOT NULL

Implant flag of the item when the case is released.

Values:

Y – Implanted Item

N – Not Implanted item

OBX-5.9 Implant Flag
Observation Value IMPLANT TYPE VARCHAR(1) NULLABLE

Type of the item when the case is released.

Values:

N – None

I – Internal Implant

E - External Implant

OBX-5.10 Implant Type
Observation Value ITEM_STATUS VARCHAR(1) NOT NULL

Status is either:

F - Final

C - Correction

OBX-11 Item Status