Payables and receivables by company and SPE

Source

Each company's ERP: bills, installments, settlements and bank movements

Rule

Operation type, allocation and the right grain for each report

Outcome

Payable, receivable, paid and received by company and month

Activation

Close, group dashboard and AI agent

What it solves

A single view of payables and receivables for every company, without opening the ERP company by company.
The numbers match the ERP's official reports, line by line.
Cost paid by cost center stops being inflated by bill substitutions and cancellations.

Business questions it answers

Ask your agent these questions once the case is live.

What is the payable and receivable balance of each company in the group today?

How much was paid by cost center and by category this month?

How much cash actually came in, excluding renegotiations and substitutions?

Which bills fall due in the next 30 days, by company?

What is the administrative cost over revenue received in the last 12 months?

What you need

The ERP connected to Nekt, with every company in the group
The ERP's official reports for a closed period, in a spreadsheet, for validation
The list of companies, SPEs and cost centers with the project each one belongs to

What you get

Four tables: payable, receivable, paid and received, with the report's column names
An automatic validation against the official report that blocks use if it diverges
A group dashboard by company, cost center and month
An AI agent that answers about finance without mixing grains

Where to activate

Month-end close

The four tables exported to the finance spreadsheet, already validated against the ERP.

AI agent via MCP

Answers balance, paid and received by company and cost center, at the right grain.

Validation alert

Notifies finance when a company drops out of OK, before the dashboard is used.

Ready to build?

The rules are known, but each column has to be checked against the official report. Plan for one validation round per report. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

The problem

Each SPE or CNPJ is a separate company in the ERP, and the group wants a single view that matches the official report. Bills come with nested arrays of settlements, categories and construction sites, and with operation types that are not cash. Summing without filtering the operation type inflates payments by 30% to 50%, and joining installments with settlements without aggregating duplicates rows.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Summing everything directly and losing which SPE owes or is owed.

Segment by company and consolidate at the end

Every table carries the company id (companyId in Sienge, the source connection in Omie, the realm in QuickBooks). The group's management company is a company like any other. Consolidate only when the question is about the group, and show the breakdown by company alongside.

Joining accounts payable with payments row by row.

Two grains, four tables

Payables and receivables have bill installment grain (billId + installmentId), open or settled. Payments and receipts have settlement grain: one installment can have several settlements. To know how much of a bill was paid, aggregate settlements by installment before joining.

Summing the whole payments array.

Paid means only payment and advance

In Sienge's payments array, count only operationTypeName 'Pagamento' and 'Adiantamento'. 'Substituição' and 'Cancelamento' are bill movements, not cost, and inflate the sum by 30% to 50%. With this rule, the warehouse matched the previous BI with a deviation below 0.5%.

Summing all receipts as cash in.

Received means only receipt

In the receipts array, revenue received is operationTypeName 'Recebimento', at net value (netAmount). Renegotiation, cancellation (distrato) and substitution are portfolio reclassifications and stay out. Interest, inflation adjustment, penalties and discounts go in their own columns.

Using originalAmount and discountAmount because the names look right.

Each report column has one field

In Sienge, the payables report's 'Valor no vencimento' (amount at due date) is correctedBalanceAmount (balance already adjusted by the index). The report's 'Desconto' (discount) is taxAmount, which is the ISS, INSS and IR withholdings. The receivables report's 'Valor original' (original amount) is balanceAmount (unadjusted balance), not the bill's originalAmount.

Unnesting the array and summing the full amount on each row.

Allocate by the bill's percentages

Unnest paymentsCategories and multiply by financialCategoryRate / 100 (null means 100). For construction sites, unnest buildingsCosts and apply the site's rate. In Omie, unnest departments and use n_distr_percentual. Without applying the percentage, each category gets the full amount.

Counting each bank movement row as a settlement.

A settlement is not a bank movement

A settlement with interest generates two or more rows in sienge_bank_movement (principal and additions). The grain of payments and receipts is the settlement. Standalone bank movements (fees, intercompany transfers, partner withdrawals, yields) come in with an origin column, because they are cash, but stay out of the comparison with the bills report.

Using the full balanceAmount as the payable balance.

Open balance without the withholding

Open committed amount = (balanceAmount - taxAmount - discountAmount) x rate / 100, dated by due date. Open service invoices with tax withholding inflated the balance when the tax was not deducted.

Copying the query per company with small differences, or adding cash and categorized together.

One source per company, unioned with origin

In Omie, each company is a connection. Apply the same transformation to each and union them with an origin column. Omie dates arrive as dd/mm/yyyy text and need explicit conversion. In QuickBooks, exclude Transfer and Journal Entry from cash flow and never add the pure cash view to the categorized one: paying the credit card bill is the same money.

Publishing the dashboard and finding the gap in the closing meeting.

Publish only what passed validation

Compare each table with the official report by company and month, with a 0.01 tolerance. Classify as OK, OK explained (a known, named difference, item by item) or Difference. Any Difference blocks the dashboard and the export until it is explained.

ERP report to field

Map validated against Sienge's official reports.

Payables: Amount at due dateBalance adjusted by the indexcorrectedBalanceAmount

Payables: DiscountISS, INSS and IR withholdingstaxAmount

Receivables: Original amountUnadjusted outstanding balancebalanceAmount

PaymentsPayment and advance settlementspayments.operationTypeName IN ('Pagamento','Adiantamento')

Substitution, CancellationNot costpayments.operationTypeName

ReceiptsCash receipt, net amountreceipts.operationTypeName = 'Recebimento'

Renegotiation, Cancellation, SubstitutionPortfolio reclassification, excludedreceipts.operationTypeName

Allocation by categoryCategory percentage, null means 100financialCategoryRate / 100

Allocation by construction siteSite percentagebuildingsCosts.rate / 100

Definitions

Payable balance

sum of the open balance of installments due in the period, by company, allocated by category

Receivable balance

sum of balanceAmount of open installments, by company and due date

Amount paid

sum of payments.grossAmount x financialCategoryRate / 100 with type Pagamento or Adiantamento, by settlement date

Revenue received

sum of receipts.netAmount with type Recebimento, by receipt date

Overdue payables

open balance of installments due before the cutoff date

Administrative cost over revenue

cost paid by the head office cost center / revenue received from all projects, monthly and trailing 12 months, both on a cash basis

Validation difference

table amount minus official report amount, by company and month; OK when below 0.01

Pitfalls that already happened

Payments 40% higher than reality

The sum of the whole payments array included bill substitutions and cancellations.

Filter Pagamento and Adiantamento before summing.

A join that duplicates

Joining the installments table with the settlements table multiplied the balance of paid bills several times over.

Aggregate settlements by installment before the join.

A discount that was not a discount

The report's Discount column did not match discountAmount because, in the report, discount means the withholdings.

Use taxAmount and validate column by column.

Old bills missing

Bills older than a certain year did not come through the standard accounts payable endpoint.

Fill in with the per-bill endpoint and check the total for an old year.

API quota exceeded

The ERP's historical load stopped halfway because the API has a small daily quota.

Load history in 12-month blocks with a load state table.

A company left out of the consolidation

A new SPE was registered in the ERP and stayed out of the group view for weeks.

Compare the ERP's company list with the table's on every load and alert when a new one appears.

Acceptance test

The case is done when every item passes.

For a closed period, accounts payable by due date matches the ERP's official report in row count and amount, by company (difference below 0.01).
Payments for the period match the payments-by-date report line by line.
Accounts receivable matches the official report in total balance and number of installments.
The sum by company equals the group consolidation.
No amount paid has an operation type other than Pagamento or Adiantamento.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

Multi-company finance (ERP)
- Every analysis is by company (companyId). Consolidate only when the question is about the group, and show the breakdown by company.
- Grains: payables and receivables = bill installment (billId + installmentId). Payments and receipts = settlement. Never join the two without aggregating settlements by installment.
- Amount paid = payments.grossAmount x financialCategoryRate / 100, only with operationTypeName 'Pagamento' or 'Adiantamento'. Substituição and Cancelamento are not cost.
- Revenue received = receipts.netAmount with operationTypeName 'Recebimento'. Renegotiation, cancellation (distrato) and substitution stay out.
- Null financialCategoryRate means 100. Allocation by construction site uses buildingsCosts.rate.
- Payables report: Amount at due date (Valor no vencimento) = correctedBalanceAmount; Discount (Desconto) = taxAmount (withholdings). Receivables report: Original amount (Valor original) = balanceAmount.
- Standalone bank movements (fees, transfers, withdrawals, yields) are cash, but do not appear in the bills report: they come flagged by origin.
- Only use the tables when validation against the official report is OK or OK explained.
Client-specific exceptions:
- <client>: company <id> is the management company; SPEs <ids> correspond to <projects>.
- <client>: head office cost center = <id>.
- <client>: known and accepted validation differences: <named list>.

Build the "Payables and receivables by company and SPE" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/imobiliario/financeiro-erp-por-spe.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.

Variations

Administrative cost over revenue

Cost paid by the head office cost center divided by revenue received from all projects, with and without profit distributions.

Omie and Conta Azul

Omie: one connection per company, departments by n_distr_percentual. Conta Azul: unnest the allocation by category and then by the installment's cost center.

QuickBooks with P&L by SPE

P&L by company and month validated against the native report, intercompany balances within the group and a release traffic light per company.