CONTA_PACIENTE_RESUMO: why the account summary can count twice

CONTA_PACIENTE_RESUMO is the table most used for billing in a BI on Tasy: it holds each account's items already grouped, with quantity, charge and cost columns. It is also the most common source of a well-known complaint: the month's billing in the BI sits above Tasy's report, and the gap grows over time. Almost always the reason is the same: the summary was loaded as if it were a regular table.

What the account summary is

During the encounter, Tasy records each procedure in PROCEDIMENTO_PACIENTE and each supply or drug in the encounter's materials table. The summary consolidates those entries by account, with the columns management uses:

That is why margin by payer, average revenue per patient and supplies per patient usually come from here: charge and cost sit on the same row.

The point that changes everything: the summary is rebuilt

When an account is recalculated (because an item was added, removed, repriced, or the account moved to another payer), Tasy generates the summary again. The previous summary ceases to exist and a new one appears, with new sequences.

There is a second detail: in Tasy, CONTA_PACIENTE_RESUMO has no DT_ATUALIZACAO of its own that follows these changes. To know whether an account's summary changed, you have to look at the DT_ATUALIZACAO of CONTA_PACIENTE.

The example: a 90 account

Account 50,871 was first calculated at 9 a.m. with four items:

At 11 a.m., the auditor removed the duplicate and the account was recalculated. Tasy rebuilt the summary:

The right amount is 90.00. Here is what different load strategies produce:

In a month with thousands of recalculated accounts, the difference easily exceeds a few percent of billing. And because it depends on how many times each account was recalculated, it is not a constant you can subtract.

The right way to load

1. Find which accounts changed using the DT_ATUALIZACAO of CONTA_PACIENTE, with the same margin as other incremental loads (Tasy incremental loads).
2. Bring the whole summary of those accounts, not just new rows.
3. At the destination, delete those accounts' summary and write what just arrived. The unit of update is the account, not the item.

In PostgreSQL, in an intermediate database, the swap looks like this:

-- accounts that arrived in this run
CREATE TEMP TABLE accounts_run AS
SELECT DISTINCT nr_interno_conta
  FROM raw.conta_paciente_resumo
 WHERE _loaded_at > :last_run;

-- delete the old summary of those accounts
DELETE FROM curated.conta_paciente_resumo t
 USING accounts_run a
 WHERE t.nr_interno_conta = a.nr_interno_conta;

-- write only the latest load of each account
INSERT INTO curated.conta_paciente_resumo
SELECT r.*
  FROM raw.conta_paciente_resumo r
  JOIN accounts_run a ON a.nr_interno_conta = r.nr_interno_conta
 WHERE r._load_id = (SELECT max(r2._load_id)
                       FROM raw.conta_paciente_resumo r2
                      WHERE r2.nr_interno_conta = r.nr_interno_conta);

The swap runs inside one transaction. If it fails halfway, nothing changes and the next run tries again.

The filters behind the number billing recognizes

Even with the right load, the summary brings rows that should not count toward the month's billing. The filters we use in Clic Health, checked against Tasy's report:

• account not cancelled: IE_CANCELAMENTO empty in CONTA_PACIENTE (cancelled and reversed accounts stay out);
• account in a final batch: the account sits in a batch with final status in PROTOCOLO_CONVENIO;
• item not removed: CD_MOTIVO_EXC_CONTA empty in the summary.

A fourth point is the date. The billing month can be the account's reference month (DT_MESANO_REFERENCIA), the batch month or the discharge month. Each answers a different question. Pick one, write the rule down and use the same one on every dashboard.

In Clic Health, the billing table carries every account (cancelled, reversed, provisional and not batched) with its status, and the dashboards apply the filters above when loading. Only the patient account screen shows the whole table, with the filters preset to those values. That way a cancelled account can be investigated without contaminating billing.

How to tell whether your BI has the problem

1. Pick a closed month and add up the summary in the BI by account.
2. Compare with the account total in Tasy (VL_CONTA in CONTA_PACIENTE, or the billing report).
3. Sort by the difference. If the accounts with differences are the ones recalculated most (those that went through audit, those that changed payer), it is an accumulated summary.
4. Count sequences per account in the BI and in Tasy. More sequences in the BI confirms it.

The monthly reconciliation routine is in how to reconcile Tasy figures with the BI. For Clic Health's billing dashboards, see billing and denials with Tasy data.

Who writes this

Domínio has worked for more than 20 years with hospitals that run Tasy, from billing to accounting. We have found this problem in more than one BI we were asked to review. If your BI billing does not match Tasy, talk to us on WhatsApp or at comercial@dominiocontabil.com.br.

Related questions

Why not use PROCEDIMENTO_PACIENTE and the materials table instead of the summary?

You can, and the totals must match. The summary is more practical because it already has charge, cost and payout per item, in the account structure. The entry tables are useful for checking and for analysis by performing physician and time.

Does the problem only happen with incremental loads?

Yes. A full load that replaces the whole table on every run does not accumulate old summaries. It is just too heavy to run often on a table this size.

Doesn't the account total in CONTA_PACIENTE solve it?

It solves the total per account, but not the detail by item, unit, structure or cost. The summary is still needed for margin and mix.

Should items with CD_MOTIVO_EXC_CONTA be deleted from the BI?

There is no need. Keep them flagged as removed and filter on the dashboards. They help measure how much audit takes out of accounts.