HL7 message and MSCM staging table

MSCM STAGING TABLE (CLOVERLEAF_BILLING_HEADER) HL7 MESSAGE
FIELD NAME COLUMN NAME DATA TYPE CONSTRAINT/ COMMENT DFT SEGMENT (BILLING SYSTEM)

SEGMENT

SUBCOMPONENT

BILLING_HEADER_ID NUMBER PK, UNIQUE, NOT NULL. Auto-generated sequence number. Will also serve as the batch number. (Depends on the customer if they will use this batch id). FT1-3
Sending Application SENDING_APP_ID VARCHAR2 (20) NOT NULL MSH-3 Sending Application
Facility FACILITY_CODE VARCHAR2 (20) NOT NULL MSH-4 Sending Facility
Patient Identifier List PATIENT_NO VARCHAR2 (20) NOT NULL PID-3 Patient Identifier List
Patient Name PATIENT_LNAME VARCHAR2 (194) NOT NULL PID-5.0 Family Name
PATIENT_FNAME VARCHAR2 (30) NOT NULL PID-5.1 Given Name
PATIENT_MNAME VARCHAR2 (26) NULLABLE PID-5.2 Middle Name
Date/Time of Birth PATIENT_BDATE DATE NULLABLE PID-7 Date/Time Of Birth
Patient Account Number PATIENT_ACCT_NO VARCHAR2 (20) NULLABLE PID-18 Patient Account Number
Patient Class PATIENT_CLASS CHAR (1) NOT NULL PV1-2 Patient Class
Visit Number VISIT_NO VARCHAR2 (20) NULLABLE PV1-19 Visit Number
PROCESS_FLAG CHAR (1)

NOT NULLDEFAULT to 0. Updated to 1 or 2 by CloverLeaf after processing. State whether:

  • 0 – New record

  • 1 – Processed successfully

  • 2 – Processed with errors

PROCESS_DESC VARCHAR2 (194) NULLABLE. Updated by CloverLeaf after processing depending on the PROCESS_FLAG. Description of errors or constraints.
CREATE_DATE DATE NON-NULLABLE. Auto generated system date upon creation of record.
LAST_UPDATE_DATE DATE NON-NULLABLE. DEFAULT to CREATE_DATE. Updated by CloverLeaf after processing.
MSCM STAGING TABLE (CLOVERLEAF_BILLING_TRNSCTN) HL7 MESSAGE
FIELD NAME COLUMN NAME DATA TYPE CONSTRAINTS / COMMENTS

DFT SEGMENT

(BILLING SYSTEM)

SEGMENT

SUBCOMPONENT

BILLING_TRNSCTN_ID NUMBER PK, UNIQUE, NOT NULL. Auto-generated sequence number.
Transaction Batch ID BATCH_ID NUMBER FK to CLOVERLEAF_BILLING_HEADER which will also serve as the batch number. (Depends on the customer if they will use this batch id). FT1-3
Transaction Date / Procedure Date and Time SCHEDULED_DATE TIMESTAMP NOT NULL FT1-4 and PR1-5
Transaction Type TRANS_TYPE VARCHAR2 (8 BYTES)

NOT NULL

States whether:

  • CG – Charge

  • CD – Credit

FT1-6
Transaction Code TRANS_CODE VARCHAR2(80) NOT NULL. Use FT1-7 if for ITEM CDM Number or HCPC. Use FT1-25 if for Procedure Code (CPT) or Charge Code FT1-7 or FT1-25
Transaction Quantity QUANTITY NUMBER (13, 0) NULLABLE. Null if transaction is for procedure. FT1-10
Department Code DEPT_CODE VARCHAR2 (20) NULLABLE FT1-13
Procedure Code PROCEDURE_CODE VARCHAR2 (20) NOT NULL PR1-3.0 Identifier
Procedure Description PROCEDURE_DESC VARCHAR2 (199) NULLABLE PR1-3.1 Description
Charge Code CHARGE_CODE VARCHAR2 (20) NOT NULL PR1-3.3 Charge Code
Doctor ID EMPLOYEE_ID VARCHAR2 (15) NULLABLE PR1-12 ID Number
Doctor Lastname EMPLOYEE_LNAME VARCHAR2 (50) NULLABLE PR1-12 Family Name
Doctor Firstname EMPLOYEE_FNAME VARCHAR2 (30) NULLABLE PR1-12 Given Name
CPT Modifier PROCEDURE_MOD_CODE VARCHAR2(250) NULLABLE PR1-16