> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.

# TOTVS RM as a data source

> Bring customers, receivables, payment slips, products and sales data from TOTVS RM to Nekt.

TOTVS RM is TOTVS's ERP line for back-office management: finance (accounts receivable and payable, bank slips), billing, purchases and stock. The Nekt connector reads your own RM server through its REST services (RM Host), so you can analyze customers, receivables, payment slips, products and sales movements together with the rest of your data.

## Before you start

The connector talks to **your** RM server, so a few things must be in place on the RM side. Ask your RM administrator to check them:

1. **RM REST server published on the internet over HTTPS.** RM serves its REST APIs from the RM Host (RM.Host or RM.Host.Service, port 8051 by default). Out of the box it answers plain `http`, so it must be published with HTTPS (TOTVS documents this as *Habilitar SSL/TLS no Host*). It must be reachable from the internet with an `https://` address, and your firewall must allow Nekt's IP addresses.
2. **An RM user for the integration.** Create (or choose) an RM user whose access profile can read the data of the streams you want: customers/suppliers, financial entries, payment slips, movements and products. Profiles are managed in RM under *Serviços Globais > Segurança > Perfis*. For the **Movements** stream, the profile also needs the item *Integração com WebService do Movimento (MovMovimentoTBCData)* under *Acesso a Menus*.
3. **Data protection filter (LGPD).** For customers/suppliers, financial entries and payment slips, RM only returns the records that belong to the user itself unless the user's profile has the permission **"Desconsiderar Filtro de Produto em WebService"**, for each company (coligada). Without it, the extraction runs but brings few or no records.

> [!WARNING]
>
> If the customers or receivables tables come back empty or much smaller than in RM, the integration user is missing the **"Desconsiderar Filtro de Produto em WebService"** permission described above.

## Configuring TOTVS RM as a Source

In the [Sources](https://app.nekt.ai/sources) tab, click on the "Add source" button located on the top right of your screen. Then, select the TOTVS RM option from the list of connectors.

Click **Next** and you'll be prompted to add your access.

### 1. Add account access

The following configurations are available:

* **RM server URL**: The address of your RM REST server, including the port, for example `https://rm.yourcompany.com.br:8051`. It must start with `https://`.
* **Username** and **Password**: The RM user for the integration (the same login used to open RM).
* **Company codes (coligadas)**: The code of each RM company (coligada) to extract, separated by commas, for example `1` or `1,2`. Customers and suppliers shared by every company (coligada 0) are always included.
* **Branch codes (filiais)**: Optional. Codes of the branches to extract, separated by commas. Leave it empty to extract every branch. It applies to receivables, payables, payment slips, movements and warehouses. If you add a branch later, run a full sync so its history is extracted. A company added later is extracted from the start automatically.

Advanced settings:

* **RM server timezone**: (Default: `America/Sao_Paulo`) RM stores dates in the server's local time. Change it if your RM server runs in another timezone, such as `America/Manaus`.
* **Lookback (minutes)**: (Default: `120`) Incremental extractions re-read the records changed in this many minutes before the last extraction, to catch changes that RM saved late.
* **Page size**: (Default: `500`) How many records are read per request. Lower it if your RM server times out.
* **Verify SSL certificate**: (Default: on) Turn it off only if your RM server uses a self-signed or incomplete certificate.
* **SQL queries**: Optional. SQL sentences registered in RM (*Consulta SQL*) to extract, one table each. See [Extracting SQL queries](#extracting-sql-queries) below.
* **Additional DataServers**: Optional. Names of other RM DataServers to extract in full, separated by commas, one table each (for example `EstPrdDataBR`). The RM user must be allowed to read them.

Once you're done, click **Next**.

### 2. Select streams

Choose which data streams you want to sync. For faster extractions, select only the streams that are relevant to your analysis.

> Tip: The stream can be found more easily by typing its name.

Select the streams and click **Next**.

### 3. Configure data streams

Customize how you want your data to appear in your catalog. Select the desired layer where the data will be placed, a folder to organize it inside the layer, a name for each table (which will effectively contain the fetched data) and the type of sync.

* **Layer**: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
* **Folder**: a folder can be created inside the selected layer to group all tables being created from this new data source.
* **Table name**: we suggest a name, but feel free to customize it. You have the option to add a **prefix** to all tables at once and make this process faster!
* **Sync Type**: you can choose between INCREMENTAL and FULL\_TABLE.
  * Incremental: every time the extraction happens, we'll get only the new and changed data, which is good if, for example, you want to keep every record ever fetched.
  * Full table: every time the extraction happens, we'll get the current state of the data, which is good if, for example, you don't want to have deleted data in your catalog.

Once you are done configuring, click **Next**.

### 4. Configure data source

Describe your data source for easy identification within your organization, not exceeding 140 characters.

To define your [Trigger](https://docs.nekt.com/runs/scheduling-and-triggers.md), consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times).

Optionally, you can define some additional settings:

* Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource [here](https://docs.nekt.com/get-started/core-concepts/resource-control).
* Determine when to execute an **Additional [Full Sync](https://docs.nekt.com/get-started/core-concepts/types-of-sync#additional-full-sync)**. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while. For TOTVS RM we recommend a weekly full sync: records deleted in RM, and changes RM saves without updating its change dates, only reach your incremental tables on a full sync.

Once you are ready, click **Next** to finalize the setup.

### 5. Check your new source

You can view your new source on the [Sources](https://app.nekt.ai/sources) page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog.

> [!WARNING]
>
> For you to be able to see it on your [Catalog](https://app.nekt.ai/catalog), you need at least one successful source run.

# Streams and Fields

## Available streams

The table below lists every stream, its **slug** (the exact identifier to pass when creating the source via API) and a short description.

| Stream | Slug | Description |
| - | - | - |
| Customers and suppliers | `customers_vendors` | Customers and suppliers: name, trade name, CPF/CNPJ, e-mail, phone and address. |
| Receivables | `receivables` | Accounts receivable entries: amounts, issue and due dates, settlement status. |
| Payables | `payables` | Accounts payable entries. |
| Payment slips (boletos) | `payment_slips` | Bank payment slips: nosso número, barcode, typeable line (linha digitável), due date and amount. |
| Movements | `movements` | Sales orders, invoices, purchases and other stock movements (header). |
| Warehouses | `warehouses` | Stock locations. |
| Products | `products` | Products and services: code, description, prices 1 to 5, units and costs. |
| SQL query | `sql_<name>` | *Optional.* One table for each SQL sentence listed in **SQL queries**. |
| Additional DataServer | `dataserver_<name>` | *Optional.* One table for each DataServer listed in **Additional DataServers**. |

Every table also has a `record` column with the whole record exactly as RM returned it (JSON, with RM's own column names), so fields not listed below can still be used in your queries, for example `json_extract_scalar(record, '$.CAMPOLIVRE')`.

## Fields by stream

> [accordion] Customers and suppliers
>
> Stream name: `customers_vendors`. Customers and suppliers registered in RM, with document (CPF/CNPJ), e-mail, phone and address.
>
> **Source:** DataServer `FinCFODataBR` (table FCFO)
>
> **Sync Strategy:**
>
> * Incremental on `modified_at` (the record's last change in RM)
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM, as returned by RMSRestDataServer (the primary key fields joined by `$_$`, e.g. `1$_$C000123`); primary key of the table. |
> | `codcoligada` | Integer | Company (coligada) code; 0 for customers/suppliers shared by every company. |
> | `codcfo` | String | Customer/supplier code (CODCFO). |
> | `nome` | String | Legal name (razão social) or full name. |
> | `nomefantasia` | String | Trade name (nome fantasia). |
> | `cgccfo` | String | CPF (person) or CNPJ (company) document number, as typed in RM. |
> | `pessoafisoujur` | String | F for a person (CPF), J for a company (CNPJ). |
> | `inscrestadual` | String | State registration (inscrição estadual). |
> | `pagrec` | Integer | Role: 1 = customer, 2 = supplier, 3 = both. |
> | `ativo` | Integer | 1 when the customer/supplier is active, 0 when inactive. |
> | `email` | String | Main e-mail address. |
> | `emailpgto` | String | E-mail address for billing/payment. |
> | `emailentrega` | String | E-mail address for deliveries. |
> | `telefone` | String | Main phone number. |
> | `telefonepgto` | String | Phone number of the billing address. |
> | `telefoneentrega` | String | Phone number of the delivery address. |
> | `fax` | String | Fax number. |
> | `contato` | String | Contact person. |
> | `rua` | String | Street of the main address. |
> | `numero` | String | Number of the main address. |
> | `complemento` | String | Complement of the main address. |
> | `bairro` | String | District (bairro) of the main address. |
> | `cidade` | String | City of the main address. |
> | `codetd` | String | State (UF) code of the main address. |
> | `cep` | String | Postal code (CEP) of the main address. |
> | `pais` | String | Country of the main address. |
> | `codmunicipio` | String | IBGE city code of the main address. |
> | `limitecredito` | Number | Credit limit. |
> | `campolivre` | String | Free-text field (campo livre). |
> | `reccreatedon` | Datetime | When the record was created in RM (RECCREATEDON), in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM (RECMODIFIEDON), in UTC; empty for records RM never stamped. |
> | `modified_at` | Datetime | Incremental replication key: RECMODIFIEDON, or RECCREATEDON when RM never stamped a change, or 1900-01-01 for records older than RM's audit columns (UTC). |
> | `record` | String (JSON) | The whole record exactly as RM returned it (JSON object with RM's own column names, including any detail tables), for columns not listed above. |

> [accordion] Receivables
>
> Stream name: `receivables`. Accounts receivable entries (lançamentos a receber): amounts, due dates and settlement status.
>
> **Source:** DataServer `FinLanDataBR` (table FLAN, receivable entries)
>
> **Sync Strategy:**
>
> * Incremental on `modified_at` (the record's last change in RM). Entries still open are re-read on every run, and an entry settled or cancelled since the last run is read again even when RM did not stamp the change
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM, as returned by RMSRestDataServer (the primary key fields joined by `$_$`, e.g. `1$_$45678`); primary key of the table. |
> | `codcoligada` | Integer | Company (coligada) code. |
> | `idlan` | Integer | Identifier of the financial entry (IDLAN). |
> | `codfilial` | Integer | Branch (filial) code. |
> | `numerodocumento` | String | Document number. |
> | `pagrec` | Integer | 1 = receivable (a receber), 2 = payable (a pagar). |
> | `statuslan` | Integer | Status: 0 = open, 1 = settled (baixado), 2 = cancelled, 3 = settled by agreement, 4 = partially settled. |
> | `codcolcfo` | Integer | Company of the customer/supplier record (0 = global). |
> | `codcfo` | String | Customer/supplier code (join with customers\_vendors.codcfo). |
> | `codtdo` | String | Document type code. |
> | `seriedocumento` | String | Document series. |
> | `historico` | String | Description (histórico) of the entry. |
> | `dataemissao` | Date | Issue date. |
> | `datavencimento` | Date | Due date. |
> | `dataprevbaixa` | Date | Expected settlement date. |
> | `databaixa` | Date | Settlement date (last payment). |
> | `valororiginal` | Number | Original amount. |
> | `valorbaixado` | Number | Amount already settled. |
> | `valorjuros` | Number | Interest amount. |
> | `valormulta` | Number | Fine amount. |
> | `valordesconto` | Number | Discount amount. |
> | `codmoevalororiginal` | String | Currency of the original amount. |
> | `codcxa` | String | Cash/bank account code (conta caixa). |
> | `cnabstatus` | Integer | Bank collection (CNAB) status code. |
> | `datacriacao` | Datetime | When the entry was created (DATACRIACAO), in UTC. |
> | `dataalteracao` | Datetime | When the entry was last changed (DATAALTERACAO), in UTC. |
> | `reccreatedon` | Datetime | When the record was created in RM (RECCREATEDON), in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM (RECMODIFIEDON), in UTC; empty for records RM never stamped. |
> | `modified_at` | Datetime | Incremental replication key: RECMODIFIEDON, or RECCREATEDON when RM never stamped a change, or 1900-01-01 for records older than RM's audit columns (UTC). |
> | `record` | String (JSON) | The whole record exactly as RM returned it (JSON object with RM's own column names, including any detail tables), for columns not listed above. |

> [accordion] Payables
>
> Stream name: `payables`. Accounts payable entries (lançamentos a pagar).
>
> **Source:** DataServer `FinLanDataBR` (table FLAN, payable entries)
>
> **Sync Strategy:**
>
> * Incremental on `modified_at` (the record's last change in RM). Entries still open are re-read on every run, and an entry settled or cancelled since the last run is read again even when RM did not stamp the change
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM, as returned by RMSRestDataServer (the primary key fields joined by `$_$`, e.g. `1$_$45678`); primary key of the table. |
> | `codcoligada` | Integer | Company (coligada) code. |
> | `idlan` | Integer | Identifier of the financial entry (IDLAN). |
> | `codfilial` | Integer | Branch (filial) code. |
> | `numerodocumento` | String | Document number. |
> | `pagrec` | Integer | 1 = receivable (a receber), 2 = payable (a pagar). |
> | `statuslan` | Integer | Status: 0 = open, 1 = settled (baixado), 2 = cancelled, 3 = settled by agreement, 4 = partially settled. |
> | `codcolcfo` | Integer | Company of the customer/supplier record (0 = global). |
> | `codcfo` | String | Customer/supplier code (join with customers\_vendors.codcfo). |
> | `codtdo` | String | Document type code. |
> | `seriedocumento` | String | Document series. |
> | `historico` | String | Description (histórico) of the entry. |
> | `dataemissao` | Date | Issue date. |
> | `datavencimento` | Date | Due date. |
> | `dataprevbaixa` | Date | Expected settlement date. |
> | `databaixa` | Date | Settlement date (last payment). |
> | `valororiginal` | Number | Original amount. |
> | `valorbaixado` | Number | Amount already settled. |
> | `valorjuros` | Number | Interest amount. |
> | `valormulta` | Number | Fine amount. |
> | `valordesconto` | Number | Discount amount. |
> | `codmoevalororiginal` | String | Currency of the original amount. |
> | `codcxa` | String | Cash/bank account code (conta caixa). |
> | `cnabstatus` | Integer | Bank collection (CNAB) status code. |
> | `datacriacao` | Datetime | When the entry was created (DATACRIACAO), in UTC. |
> | `dataalteracao` | Datetime | When the entry was last changed (DATAALTERACAO), in UTC. |
> | `reccreatedon` | Datetime | When the record was created in RM (RECCREATEDON), in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM (RECMODIFIEDON), in UTC; empty for records RM never stamped. |
> | `modified_at` | Datetime | Incremental replication key: RECMODIFIEDON, or RECCREATEDON when RM never stamped a change, or 1900-01-01 for records older than RM's audit columns (UTC). |
> | `record` | String (JSON) | The whole record exactly as RM returned it (JSON object with RM's own column names, including any detail tables), for columns not listed above. |

> [accordion] Payment slips (boletos)
>
> Stream name: `payment_slips`. Bank payment slips with the data needed for a duplicate copy: nosso número, barcode, typeable line, due date and amount.
>
> **Source:** DataServer `FinBoletoData` (table FBOLETO)
>
> **Sync Strategy:**
>
> * Incremental on `modified_at` (the record's last change in RM)
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM, as returned by RMSRestDataServer (the primary key fields joined by `$_$`, e.g. `1$_$987`); primary key of the table. |
> | `codcoligada` | Integer | Company (coligada) code. |
> | `idboleto` | Integer | Identifier of the payment slip (IDBOLETO). |
> | `codfilial` | Integer | Branch (filial) code. |
> | `nossonumero` | String | Bank's identification of the slip (nosso número). |
> | `codigobarra` | String | Barcode number of the slip (código de barras). |
> | `ipte` | String | Typeable line of the slip (linha digitável / IPTE), used for the duplicate copy. |
> | `vencimento` | Date | Due date of the slip. |
> | `valor` | Number | Amount of the slip. |
> | `codcolsacado` | Integer | Company of the payer's customer record (0 = global). |
> | `sacado` | String | Payer (sacado): customer code (join with customers\_vendors.codcfo). |
> | `numerodocumento` | String | Document number. |
> | `tipo` | Integer | Slip type code. |
> | `idconvenio` | Integer | Bank agreement (convênio) identifier. |
> | `formapagamento` | String | Payment method code. |
> | `cnabstatus` | Integer | Bank collection (CNAB) status code. |
> | `cnabbanco` | String | Bank code used for collection. |
> | `cnabnossonumero` | String | Nosso número as sent in the CNAB file. |
> | `jurosaodia` | Number | Daily interest. |
> | `multa` | Number | Fine. |
> | `desconto` | Number | Discount. |
> | `reccreatedon` | Datetime | When the record was created in RM (RECCREATEDON), in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM (RECMODIFIEDON), in UTC; empty for records RM never stamped. |
> | `modified_at` | Datetime | Incremental replication key: RECMODIFIEDON, or RECCREATEDON when RM never stamped a change, or 1900-01-01 for records older than RM's audit columns (UTC). |
> | `record` | String (JSON) | The whole record exactly as RM returned it (JSON object with RM's own column names, including any detail tables), for columns not listed above. |

> [accordion] Movements
>
> Stream name: `movements`. Sales orders, invoices, purchases and other stock movements (header).
>
> **Source:** DataServer `MovMovimentoTBCData` (table TMOV)
>
> **Sync Strategy:**
>
> * Incremental on `modified_at` (the record's last change in RM)
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM, as returned by RMSRestDataServer (the primary key fields joined by `$_$`, e.g. `1$_$5555`); primary key of the table. |
> | `codcoligada` | Integer | Company (coligada) code. |
> | `idmov` | Integer | Identifier of the movement (IDMOV). |
> | `codfilial` | Integer | Branch (filial) code. |
> | `codtmv` | String | Movement type code (e.g. 2.1.01 for a sales order; the meaning is configured per RM installation). |
> | `numeromov` | String | Movement number. |
> | `serie` | String | Series. |
> | `tipo` | String | Movement kind code (TIPO). |
> | `status` | String | Movement status code (e.g. A = pending, F = invoiced, C = cancelled). |
> | `codcfo` | String | Customer/supplier code (join with customers\_vendors.codcfo). |
> | `codloc` | String | Warehouse (local de estoque) code. |
> | `codcpg` | String | Payment terms code. |
> | `codrpr` | String | Sales representative code. |
> | `dataemissao` | Date | Issue date. |
> | `datasaida` | Date | Shipping date. |
> | `valorbruto` | Number | Gross amount. |
> | `valorliquido` | Number | Net amount. |
> | `valoroutros` | Number | Other amounts. |
> | `valorfrete` | Number | Freight amount. |
> | `valordesc` | Number | Discount amount. |
> | `valordesp` | Number | Expenses amount. |
> | `reccreatedon` | Datetime | When the record was created in RM (RECCREATEDON), in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM (RECMODIFIEDON), in UTC; empty for records RM never stamped. |
> | `modified_at` | Datetime | Incremental replication key: RECMODIFIEDON, or RECCREATEDON when RM never stamped a change, or 1900-01-01 for records older than RM's audit columns (UTC). |
> | `record` | String (JSON) | The whole record exactly as RM returned it (JSON object with RM's own column names, including any detail tables), for columns not listed above. |

> [accordion] Warehouses
>
> Stream name: `warehouses`. Stock locations (locais de estoque).
>
> **Source:** DataServer `EstLOCData` (table TLOC)
>
> **Sync Strategy:**
>
> * Full table (the whole list is read on every run)
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM, as returned by RMSRestDataServer (the primary key fields joined by `$_$`, e.g. `1$_$1$_$01`); primary key of the table. |
> | `codcoligada` | Integer | Company (coligada) code. |
> | `codfilial` | Integer | Branch (filial) code. |
> | `codloc` | String | Warehouse (local de estoque) code. |
> | `nome` | String | Warehouse name. |
> | `nivelestoque` | Integer | Stock level of the location in the hierarchy. |
> | `inativo` | Integer | 1 when the location is inactive. |
> | `rua` | String | Street of the main address. |
> | `numero` | String | Number of the main address. |
> | `complemento` | String | Complement of the main address. |
> | `bairro` | String | District (bairro) of the main address. |
> | `cidade` | String | City of the main address. |
> | `codetd` | String | State (UF) code of the main address. |
> | `cep` | String | Postal code (CEP) of the main address. |
> | `pais` | String | Country of the main address. |
> | `telefone` | String | Phone number. |
> | `email` | String | E-mail address. |
> | `reccreatedon` | Datetime | When the record was created in RM (RECCREATEDON), in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM (RECMODIFIEDON), in UTC; empty for records RM never stamped. |
> | `record` | String (JSON) | The whole record exactly as RM returned it (JSON object with RM's own column names, including any detail tables), for columns not listed above. |

> [accordion] Products
>
> Stream name: `products`. Products and services with description, prices 1 to 5, units and costs.
>
> **Source:** API `GET /api/est/v2/Products`
>
> **Sync Strategy:**
>
> * Full table (the whole list is read on every run)
> * Primary key(s): `internal_id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `internal_id` | String | Integration key of the product: company / product id; primary key. |
> | `company_id` | Integer | Company (coligada) code. |
> | `product_id` | Integer | Internal product identifier (IDPRD). |
> | `code` | String | Product code (CODIGOPRD). |
> | `short_code` | String | Reduced product code. |
> | `code_aux` | String | Auxiliary product code. |
> | `description` | String | Product description / title (DESCRICAO). |
> | `description_aux` | String | Auxiliary description. |
> | `trading_name` | String | Trade name of the product (NOMEFANTASIA). |
> | `note` | String | Notes about the product. |
> | `long_note` | String | Long description of the product's history. |
> | `inactive` | Boolean | True when the product is inactive. |
> | `type` | String | Product type (product or service). |
> | `type_code` | String | Product type code. |
> | `last_level` | Boolean | True for last-level (sellable) products; false for grouping levels. |
> | `price1` | Number | Price 1 of the product (the meaning of prices 1-5 is configured in RM). |
> | `price2` | Number | Price 2 of the product. |
> | `price3` | Number | Price 3 of the product. |
> | `price4` | Number | Price 4 of the product. |
> | `price5` | Number | Price 5 of the product. |
> | `price_currency1_code` | String | Currency of price 1. |
> | `price_data_base1` | Date | Base date of price 1. |
> | `average_cost` | Number | Average cost. |
> | `unitary_cost` | Number | Unit cost. |
> | `sales_unit_code` | String | Sales unit of measure. |
> | `purchase_unit_code` | String | Purchase unit of measure. |
> | `control_unit_code` | String | Stock control unit of measure. |
> | `stockable` | Boolean | True when the product is kept in stock. |
> | `minimum_stock1` | Number | Minimum stock 1. |
> | `maximum_stock1` | Number | Maximum stock 1. |
> | `ccf_number` | String | NCM classification number. |
> | `brand_id` | Integer | Default brand of the product. |
> | `gross_weigh` | Number | Gross weight. |
> | `net_weight` | Number | Net weight. |
> | `registration_date` | Date | Date the product was registered. |
> | `last_change_date` | Datetime | When the product was last changed, in UTC. |
> | `record` | String (JSON) | The whole item exactly as the API returned it (JSON object), for fields not listed above. |

> [accordion] SQL query (sql\_name)
>
> Stream name: `sql_<name>`, one for each item of the **SQL queries** setting.
>
> **Source:** ConsultaSQL API (`/api/framework/v1/consultaSQLServer/RealizaConsulta`)
>
> **Sync Strategy:**
>
> * Full table (the sentence runs once per extraction and its whole result replaces the table)
> * Primary key(s): `row_number`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `row_number` | Integer | Position of the row in the query result (1, 2, ...); recomputed on every run. |
> | `query_code` | String | Code of the RM SQL sentence that produced the row. |
> | `company_id` | Integer | Company (coligada) the sentence was run for. |
> | `record` | String (JSON) | The row exactly as the SQL sentence returned it, with the columns of its SELECT. |

> [accordion] Additional DataServer (dataserver\_name)
>
> Stream name: `dataserver_<name>`, one for each name in the **Additional DataServers** setting.
>
> **Sync Strategy:**
>
> * Full table
> * Primary key(s): `id`
>
> | Field | Type | Description |
> | :- | :- | :- |
> | `id` | String | Key of the record in RM (the primary key fields joined by `$_$`). |
> | `codcoligada` | Integer | Company (coligada) code of the record, when the DataServer has one. |
> | `reccreatedon` | Datetime | When the record was created in RM, in UTC. |
> | `recmodifiedon` | Datetime | When the record was last changed in RM, in UTC. |
> | `record` | String (JSON) | The whole record exactly as RM returned it. |

## Extracting SQL queries

Some RM data, such as **stock balances** and **price tables**, has no standard listing in RM's REST services. You can still bring it to Nekt with an SQL sentence registered in RM (*Consulta SQL*, in *Serviços Globais*):

1. In RM, create the sentence, for example with the code `NEKT.001` in the stock application (`T`):

   ```sql theme={null}
   SELECT CODCOLIGADA, CODFILIAL, CODLOC, IDPRD,
          SALDOFISICO2, SALDORESERVA, SALDOFINANCEIRO
   FROM TPRDLOC
   WHERE CODCOLIGADA = :CODCOLIGADA
   ```

2. Allow the integration user to run it. `IDPRD` matches `product_id` in the `products` table.

3. In the source's **SQL queries** setting, list it as JSON. Each item has a `name` (the table becomes `sql_<name>`), the sentence `code`, the `company_id` it belongs to, the `system` (application code, such as `T` for stock or `F` for finance) and, optionally, `parameters` and `context_company_id`:

   ```json theme={null}
   [{"name": "stock_balance", "code": "NEKT.001", "company_id": 1, "system": "T", "parameters": {"CODCOLIGADA": "1"}}]
   ```

The whole result of the sentence is read on every extraction, so keep it focused on the data you need. Each row lands in the `record` column; in queries, read its columns with your warehouse's JSON functions.

## Implementation Notes

### Data Quality Considerations

* Dates and times are converted from the RM server's local time (see **RM server timezone**) to UTC.
* Receivables and payables that are still open or partially settled are re-read on every extraction. An entry that was open in the previous extraction and was settled or cancelled since is read again, even when RM did not update its change date, so the settlement status stays current.
* Records deleted in RM stay in incremental tables until the next full sync (see the weekly full sync recommendation above).
* The follow-up of open entries works for up to 200,000 open receivables (or payables) per company. Above that, the run log says so, and an entry settled or cancelled without RM updating its change dates only shows its new status after a full sync.
* If open entries stop being returned by RM (deleted, or the user lost access to them), the run log tells you how many. When most of them disappear at once, it warns you to check the RM user's permissions.
* A payment slip's settlement shows up in its receivable entry (`receivables.statuslan`, `valorbaixado`, `databaixa`).
* RM does not provide the payment slip's PDF or a link to it through its APIs; slips are printed by RM itself. The data needed for a duplicate copy (typeable line, barcode, nosso número, due date and amount) is in `payment_slips`.
* The meaning of prices 1 to 5 of a product is configured in each RM installation.
* Customers, receivables and payment slips depend on the data protection permission described in [Before you start](#before-you-start). On the first extraction (and on every full sync) the run log shows how many records were read per company: if the numbers look low compared with RM, check that permission.

### API Limits & Performance

* RM recommends paging every read; the connector reads 500 records per request by default. Large first extractions (years of financial entries) can take a while.
* If records are added or removed in RM while a list is being read, the connector reads the list again (up to three times) so that no record is missed.

## Troubleshooting

| Error or symptom | What it means | What to do |
| :- | :- | :- |
| "Could not connect to the RM server" | The URL or port is wrong, the RM Host is stopped, or the firewall blocks Nekt. | Check the **RM server URL** and that the RM REST server is reachable from the internet. |
| "The secure (HTTPS) connection to the RM server failed" | The server's certificate could not be validated. | Install a valid certificate on the RM Host, or turn off **Verify SSL certificate**. |
| "rejected the username or password" | RM refused the login (HTTP 401). | Check the user and password, and that the user is active in RM. |
| "may not read the ... stream" | The user's profile does not allow reading that data. | Give the user access in *Serviços Globais > Segurança > Perfis*, or unselect the stream. |
| "does not offer the ... stream" | That RM service is not available on your server (version or publication). | Check the RM REST services with your RM administrator, or unselect the stream. |
| Customers or receivables come back empty or very small | The data protection (LGPD) filter returns only the user's own records. | Grant the **"Desconsiderar Filtro de Produto em WebService"** permission to the user's profile for each company. |
| "kept changing in RM" | The list changed on every one of three reads. | Run the extraction again later. If it keeps happening, contact support. |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.