Transaction Types

Understanding Add, Update, and Replace operations

Transaction Type Overview

Every record in the CoBHRM system must specify a TRANSACTION_TYPE that indicates the intended operation:

Code	Name	Description
A	Add	Create a new record
U	Update	Modify an existing record
R	Replace	Overwrite the most recent record (for error corrections)

Primary Keys

Each schema uses a composite primary key to uniquely identify records. The primary key always includes RECORD_TYPE plus the appropriate identifier:

Component	Description	Used By
RECORD_TYPE	Identifies the schema (e.g., CLI, ADM, DXA)	All schemas
BHASO_CLIENT_IDENTIFIER	Unique client identifier	Client
BHASO_ADMISSION_IDENTIFIER	Unique treatment episode identifier	Admission and all child records
BHASO_BABY_IDENTIFIER	Unique baby identifier	Baby Data

EFFECTIVE_DATE is not part of the primary key. It is used for versioning (SCD Type 2) to determine which record is most recent. See How Most-Recent Records Are Determined below.

Transaction Type Details

Add (A)

Use TRANSACTION_TYPE = 'A' to create a new record.

Behavior:

• Creates a new record with the specified primary key
• Fails if a record with the same primary key already exists
• All required fields must be provided

Example Use Cases:

• New client enrollment
• New treatment episode admission
• Adding a diagnosis to an admission

RECORD_TYPE|TRANSACTION_TYPE|BHASO_CLIENT_IDENTIFIER|EFFECTIVE_DATE|...
CLI|A|CLT-12345|2024-01-15|...

Update (U)

Use TRANSACTION_TYPE = 'U' to modify an existing record.

Behavior:

• Locates the existing record by primary key
• Creates a new version with the updated values
• Preserves the original record for historical purposes (SCD Type 2)
• Fails if no matching record exists

Example Use Cases:

• Updating client demographics (address change, phone number)
• Adding information discovered after initial submission

RECORD_TYPE|TRANSACTION_TYPE|BHASO_CLIENT_IDENTIFIER|EFFECTIVE_DATE|...
CLI|U|CLT-12345|2024-02-01|...

Updates create a new effective-dated record. They do not modify the original record in place. Use the EFFECTIVE_DATE to control when the updated information takes effect.

Replace (R)

Use TRANSACTION_TYPE = 'R' to completely overwrite the most recent record.

Behavior:

• Locates the most recent record for the entity
• Completely replaces it with the new values
• Used for error corrections where the original submission was incorrect
• Fails if no matching record exists

Example Use Cases:

• Correcting a data entry error in the most recent submission
• Fixing an invalid code value
• Replacing a record submitted with incorrect identifiers

RECORD_TYPE|TRANSACTION_TYPE|BHASO_CLIENT_IDENTIFIER|EFFECTIVE_DATE|...
CLI|R|CLT-12345|2024-01-15|...

Use Replace sparingly. It overwrites history. For legitimate changes over time (e.g., address changes), use Update with a new effective date instead.

Slowly Changing Dimensions (SCD Type 2)

The CoBHRM system uses SCD Type 2 for maintaining historical records:

• All historical records are retained - nothing is deleted
• Each record version is effective-dated - determined by EFFECTIVE_DATE
• Current record = record with the most recent EFFECTIVE_DATE
• Updates create new versions rather than modifying in place

Example: Client Address Change

# Initial submission (January)
CLI|A|CLT-12345|2024-01-15|Smith|Jane|123 Main St|...

# Address update (March) - creates new version
CLI|U|CLT-12345|2024-03-01|Smith|Jane|456 Oak Ave|...

After both submissions, the system contains:

1. Record effective 2024-01-15 with address “123 Main St” (historical)
2. Record effective 2024-03-01 with address “456 Oak Ave” (current)

Idempotency

File submissions are idempotent for Add operations. Submitting the same Add record twice will fail on the second submission (duplicate key error), not create a duplicate record.

How Most-Recent Records Are Determined

The CoBHRM system determines the “current” version of a record differently depending on the record level:

Level	How Most-Recent Is Determined
Client	Most recent record is determined by RECORD_TYPE + BHASO_CLIENT_IDENTIFIER + latest EFFECTIVE_DATE.
All other schemas	Most recent record is determined by RECORD_TYPE + BHASO_ADMISSION_IDENTIFIER + latest EFFECTIVE_DATE. For DISCHARGE, only one record per admission is allowed. For Baby Data, BHASO_BABY_IDENTIFIER is also used to determine most recent record.

Because the system is insert-only (SCD Type 2), all historical records are retained. The “most recent” determination is used for querying current state, not for deleting prior versions.

Error Handling

Error	Cause	Resolution
Duplicate key on Add	Record already exists	Use Update or Replace
Record not found on Update	No matching primary key	Verify identifiers; use Add if new
Record not found on Replace	No matching record	Verify identifiers; use Add if new
Invalid transaction type	Value not A, U, or R	Correct the transaction type value

Best Practices

Use Add for New Records

Always use transaction type A when creating records for the first time

Use Update for Changes

Use U with a new effective date for legitimate data changes over time

Reserve Replace for Corrections

Only use R to fix errors in the most recent submission

Include All Fields

All transaction types require all required fields, even Update and Replace

For the operational context behind these transactions — step up/step down in care, transfers, and concurrent services — see Reporting Requirements — Work Flow.