RevFlow Transaction Tables: Detailed Summary

Use this detailed summary to understand the different table relationships with ‘SOURCE’: RevFlow (WebPT Billing) and where you can focus efforts for different use cases.

SVW_REVFLOW_D_CHARGE

• PK: charge_id
• is_active = 1
• Use: Primary table for all charges, their changes, and total dollars attributed to the charge from other tables. The most current record for charge keeps the total for adjustments and payments at current. There are fields such as cur_insurance_code to call out the current insurance with a balance.
• Summary: Each charge_id is unique, while a group of charge_id’s can represent the same charge and its changes. Reference to_charge_id to see where the change occurred and from_charge_id to see the previous charge state. The group of charge_id’s will have original_charge_id in common to make it easy to see charge change history. If the charge is active, the most current record is_active = 1 while the previous charge_id’s are all inactive.

SVW_REVFLOW_C_INSURANCE_CODE

• PK: insurance_code_id
• is_active = 1
• Use: reference table for ins_name and bridge table to c_insurance_class
• Summary: This table is a reference table for the insurance_codes from d_charge. It is also a bridge when using the transactional tables to obtain the ins_class_code and/or ins_class_desc. The below example works for pri, sec, and ter_ins_code as well
• Example

select distinct dc.cur_ins_code as "code from charge",
      ic.insurance_code_id as "matches code on charge",
      ic.ins_name as "cur_ins_name",
      ic.ins_class_id as "bridge to insurance class table",
      icl.insurance_class_id as "matches id on insurance_code",
      icl.ins_class_code as "cur_insurance_class",
      icl.ins_class_desc as "cur_ins_class_desc"
FROM SVW_REVFLOW_D_CHARGE dc
LEFT JOIN public.svw_revflow_c_insurance_code ic ON (ic.insurance_code_id = c.cur_ins_code)
LEFT JOIN public.svw_revflow_c_insurance_class icl ON (icl.insurance_class_id = ic.ins_class_id)
	

SVW_REVFLOW_C_INSURANCE_CLASS

• PK: insurance_class_id
• is_active = 1
• company_id specific
• Use: This table is a reference table for the insurance class codes and must be joined on the c_insurance_code table first before the charge.
• Example: (see SVW_REVFLOW_C_INSURANCE_CODE)

SVW_REVFLOW_A_STATUS_CODE

• PK: status_code_id
• Use: This table is a reference table for the status_code found on d_visit. Simply join on status_code.

SVW_REVFLOW_D_PAYMENT_ADJUSTMENT

• PK: payment_adjustment_id
• is_active = 1
• Use: Secondary table to d_charge that keeps a record of all payments and adjustments.
• Summary: Each payment_adjustment_id is either active or inactive. Some changes do happen to these records and therefore a new record is created with a new payment_adjustment_id. The old record is made inactive. There can be many records for the same charge_id. The to_patient_credit_id is designed to note how much money was transferred to the d_patient_credit table to the patient’s tank in cases of overpayment and such. If this occurs, a record for the transferring dollar amount is simply created in d_payment_adjustment first, with a unique payment_adjustment_id, made inactive, and then the amount is transferred to the d_patient_credit table with the designated to_patient_credit_id. (For when tank payments are used see SVW_REVFLOW_D_PATIENT_CREDIT)

SVW_REVFLOW_D_PATIENT_CREDIT

• PK: patient_credit_id
• is_active = 1
• Use: Tertiary table to d_charge that keeps a record of all the tank payments by patient and keeps a record of refunds.
• Summary: Each patient_credit_id has a patient_credit_type. These types can be found below. Overall, each type is a record, by patient, that logs the original_amount and amount (current amount left), while a patient can have multiple records. When a tank amount is set to be applied to a charge the amount must create a d_payment_adjustment record first. The amount field for that patient_credit_id is updated by deducting the dollars transferred to the d_payment_adjustment and new payment_adjustment_id. That new payment_adjustment_id record is then applied to the d_charge table. A special note for patient_credit_type = 7, refunds: When a refund is initiated, a new patient_credit_id record is created with credit type 7 and the original amount field is populated with the refund and is negative (amount field is always 0 here). The patient_credit_id records that the refund is drawn from, simply deduct from their amount field. No link tells us what patient_credit_id records specifically fed the refund record.