6.92 Schema Changes

Schema changes between CareRight V6.91 and 6.92

New Tables/Views

• external_identifiers
• telehealth_participants
• delayed_job_associations
• mims_v1.itemroa
• mims_v2.itemroa

New Columns

• appointment_types.telehealth
• people.pronouns
• appointment_types.coviu_disable_menu 
• appointment_types.coviu_exit_url
• appointment_types.coviu_return_url
• appointment_types.coviu_return_label
• appointment_types.coviu_auto_return
• appointment_types.coviu_enforce_participant_uniqueness
• appointment_types.coviu_favicon_url
• program_categories.telehealth
• episodes.casemix_code
• episodes.casemix_code_type_code
• admissions.mode_of_episode_start_inpatient
• admissions.mode_of_episode_end_inpatient
• admission_categories.hide_non_drg_morbidities
• custom_drugs.route
• correspondence_settings.prefer_delivery_method

New Indexes

• health_funds(person_id guarantor_id commenced end_date)
• allergies(patient_id allergen_code)

Changed Views

Note: These view changes were inadvertently missed from the original schema change documentation when  originally published.

• v_uninvoiced_admissions - location_description added, minor performance change

• v_uninvoiced_medical_admissions - location_description added, minor performance change

Changed Columns/Indexes

• f_referral.f_ref_number
• f_patient_acc.p_acc_street - rename to address
• f_patient_acc.p_acc_town - rename to city
• f_patient_acc.p_acc_postcode - rename to postcode
• f_patient_acc.p_acc_state -rename to state
• f_patient_acc.p_acc_phone - rename to phone

Deleted Tables/Views

Note: These view changes were inadvertently missed from the original schema change documentation when originally published.

• v_primary_health_fund

Deleted Columns/Indexes

• allergies.idx_allergies_unique

Schema changes between CareRight V6.92 and 6.92.3

Changed Columns/Indexes

• receipts.stripe_payment_id - varchar(255)
• refunds.stripe_refund_id - varchar(255)
• F_TRANSACTION.stripe_payment_id - varchar(255)
• line_allocation_logs.status - varchar(6)
• medicare_batch_mappings.batch_account_id - varchar(40)
• admissions.current_state - varchar(12)
• hi_service_audit_trails.operator_hpii - varchar(16)
• hi_service_audit_trails.message_id - varchar(100)
• hi_service_audit_trails.service_operation - varchar(100)
• mhr_audit_trails.operator_hpii - varchar(16)
• mhr_audit_trails.message_id - varchar(100)
• mhr_audit_trails.service_operation - varchar(100)
• secure_messages.md5 - varchar(32)
• unprocessed_results.md5_hash - varchar(32)
• attachments.md5_hash - varchar(32)
• pictures.md5_hash - varchar(32)
• scaled_pictures.md5_hash - varchar(32)
• sms_messages.transaction_id - varchar(36)
• sms_messages.from_number - varchar(15)
• sms_messages.to_number - varchar(15)
• people.ndis_identifier - varchar(9)

Schema changes between CareRight V6.92.3 and 6.92.4

New Columns/Indexes

• receipts.location_id
• refunds.location_id

Changed views

• v_receipts - addition of location_id
• v_refunds - addition of location_id
• statement_policies - now refers directly to refunds, receipts rather than indirectly looking for statement types with particular flags

Deleted tables/views

• receipt_policies
• refund_policies

Migration

Note; the below migration is SQL Server only. This is automatically executed on deployment and included for reference only.

Migration of Location ID to Receipt

To simplify and remove the requirement for the receipt policies view, the location has been normalised into the Receipt model. This allows for more direct joins when enforcing security policy.

 update a set
            location_id = loc.id
          from receipts a
            join locations as loc on
                         (a.reversed = 0 and loc.id = (select trans_location from F_TRANSACTION where receipt_id = a.id order by 1 OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY))
                         OR
                         (a.reversed = 1 and loc.id = (select F_PATIENT_ACC.p_acc_location
                                                   from F_STATEMENT
                                                   join F_PATIENT_ACC on F_STATEMENT.stat_p_acc_ref = F_PATIENT_ACC.p_acc_ref
                                                   where F_STATEMENT.receipt_id = a.id
                                                   order by F_STATEMENT.stat_rsn desc OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY))

Migration of Location ID to Refund

To simplify and remove the requirement for the refund policies view, the location has been normalised into the Refund model. This allows for more direct joins when enforcing security policy.

update a set
            location_id = loc.id
          from refunds a
            join locations as loc on
                         (a.reversed = 0 and loc.id = (select trans_location from F_TRANSACTION where refund_id = a.id order by 1 OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY))
                         OR
                         (a.reversed = 1 and loc.id = (select F_PATIENT_ACC.p_acc_location
                                                   from F_STATEMENT
                                                   join F_PATIENT_ACC on F_STATEMENT.stat_p_acc_ref = F_PATIENT_ACC.p_acc_ref
                                                   where F_STATEMENT.refund_id = a.id
                                                   order by F_STATEMENT.stat_rsn desc OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY))