Extraction of the datasets from the SMART ERP into the format of the Kiloutou Group Data Platform generic template (Snowflake), for the company Maquinaria Venta y Alquiler Norte.
Each dataset is obtained by calling a stored procedure through Smart's web service (iSmartWebDoc). The Kiloutou agent runs a SELECT * FROM <procedure>(…) query and receives the result in JSON.
File reference: EX06468
The data can be reached in two ways:
SELECT * FROM <procedure>(…) statements are the same; only the transport and the response format change. Over ODBC the client receives the query result set directly (rows and columns), without the JSON envelope (version / resultados / metaData / datos) described below, which is specific to SmartWebDoc. The ODBC driver installation is the responsibility of the client's system administrator. This option is not set up; SmartWebDoc is the method in use.The connection details and examples below refer to the SmartWebDoc service.
POST
http://#ip:#port/services/ismartwebdoc/?format=json
For this installation: http://172.30.0.98:8000/services/ismartwebdoc/?format=json
No credentials need to be entered on the client side: the SmartWebDoc service is already configured with a dedicated user, created ad-hoc for Kiloutou, through which the Smart administrator sets the access restrictions and privileges on tables and procedures.
“Postman v8.0.4” was used for the examples.
SmartWebDoc documentation: Introducción a SmartWebDoc.
All procedures return the same JSON envelope. With the option “metaData”:true, the response includes, besides the data, the description of each field (type and label). Below is a complete example using the KIL_REF_PRODUCT_FAMILIES procedure; every other procedure follows the same structure, only the fields change.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_PRODUCT_FAMILIES"
}
]
}
{
"version": "1.2",
"resultados": [
{
"estado": "OK",
"metaData": {
"PAYCOD": { "tipo": "ftString", "etiqueta": "PAYCOD" },
"FAMCOD1": { "tipo": "ftString", "etiqueta": "FAMCOD1" },
"FAMLIB1": { "tipo": "ftString", "etiqueta": "FAMLIB1" },
"FAMCOD2": { "tipo": "ftString", "etiqueta": "FAMCOD2" },
"FAMLIB2": { "tipo": "ftString", "etiqueta": "FAMLIB2" },
"FAMCOD3": { "tipo": "ftString", "etiqueta": "FAMCOD3" },
"FAMLIB3": { "tipo": "ftString", "etiqueta": "FAMLIB3" },
"LNGCOD": { "tipo": "ftString", "etiqueta": "LNGCOD" }
},
"datos": [
{
"PAYCOD": "ES",
"FAMCOD1": "A",
"FAMLIB1": "Lifting equipment",
"FAMCOD2": "AA",
"FAMLIB2": "Platforms",
"FAMCOD3": "AAA",
"FAMLIB3": "Scissor lifts",
"LNGCOD": "ESP"
}
]
}
]
}
Notes on the response:
estado is “OK” when the command runs successfully.metaData, tipo is the data type of each field (ftString for text, a numeric type for amounts and quantities, a date type for dates). etiqueta is, by default, the field name itself.datos is the list of rows. Empty fields are returned as null.Aspects common to all datasets:
PAYCOD field is always “ES”.KIL_INVOICES, KIL_CONTRACTS, KIL_REPAIR_ORDERS and KIL_SALES_USED_EQUIPMENTS procedures take two dates, P_FECHA_DESDE and P_FECHA_HASTA. The interval is half-open: it includes P_FECHA_DESDE and excludes P_FECHA_HASTA. To extract June 2026 you would pass from “1/6/2026” to “1/7/2026”.KIL_STOCK_SNAPSHOTS and KIL_VALORISATION procedures take a single date and compute that day's snapshot on the fly (there is no stored history).rate x base = amount and the sum of the lines matches the document total.Dependencies provided by Kiloutou (see also “Dependencies to be loaded by Kiloutou”, at the end):
RACCOD (Kiloutou root), GBV and GBVA: they come from the KIL_RACINE table, which Kiloutou fills in (Fleet Dept. and finance team). While it is empty, these fields are returned as null.KIL_ACCOUNT (FCCS consolidation account): mapped by Kiloutou; returned as null.Fields still to be defined by Kiloutou:
PROCESS_STEP (Stock snapshots): returned as null until Kiloutou defines it.CODE_STATUT_OR (Repair Orders): the current values are provisional, pending Kiloutou's status logic.KIL_REF_BRANCHES
None.
Returns the list of branches (DELEGACION), including inactive ones, since historical invoices and contracts still point to them.
AGENUMDEP corresponds to the province. AGEDATFER (closing date) does not exist as data in SMART: it is derived from the branch's last stock movement when that movement is earlier than a cut-off date. BOOL_STOCK_GERE_KILOTOOLS is always “Non” (SMART does not use Kilotools).
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_BRANCHES ORDER BY AGECOD"
}
]
}
KIL_REF_TEAM_MEMBERS
None.
Returns the employees (TERCERO with third-party type “EM”).
When the employee has the natural-person record filled in, EQULIBNOM holds the surnames and EQULIBPRE the first name; otherwise EQULIBNOM holds the full company name and EQULIBPRE is null. EQUCODMAT is not provided (GDPR-sensitive data).
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_TEAM_MEMBERS ORDER BY EQUCOD"
}
]
}
KIL_REF_CUSTOMERS
None.
Returns the customers (TERCERO with third-party type “CL”), both active and deregistered.
CLITYP is “ENTR” for companies and “PART” for private individuals. CLINUMSIREN is the tax ID (CIF/NIF); CLINUMSIRET is a French concept with no Spanish equivalent, returned as null. CODE_CLIENT_PARENT is the customer to be invoiced if different from the customer itself; otherwise it repeats CLICOD. NAFCOD and CLILIBNAF (NACE/CNAE) are not filled in at this installation, returned as null.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_CUSTOMERS ORDER BY CLICOD"
}
]
}
KIL_REF_CONSTRUCTION_SITES
None.
Returns the construction sites / work centres (OBRA_TERCERO), both active and inactive.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_CONSTRUCTION_SITES ORDER BY CHACOD"
}
]
}
KIL_REF_PRODUCT_FAMILIES
None.
Returns SMART's product taxonomy flattened to the 3 levels required by the template. One row is issued per statistical group, so that FAMCOD3 is unique and any product or equipment always links to a family. LNGCOD is “ESP”.
Warning: this is the taxonomy of the company being integrated, not Kiloutou's taxonomy (family A, B, C, D…).
See the full example (with response) in “General information → Response envelope”.
KIL_REF_SERVICES
None.
Lists the service codes (PRECOD is SMART's product code, which is what appears as a service in the fact tables).
PRETYPFAM is “Racine” when the product is a rental one (time unit) and “ND” when it is a service or a sale. PREFAMCOD/PREFAMLIB and PRESOUFAMCOD/PRESOUFAMLIB are levels 1 and 2 of the family hierarchy.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_SERVICES WHERE PRETYPFAM=:tipo",
"params": { "tipo" : "Racine"}
}
]
}
KIL_REF_EQUIPMENT
None.
Returns the whole fleet (ACTIVO), including out-of-stock material.
RACNUMMAT is the equipment code (registration). RACMRQ (brand) and RACTYP (model) are obtained by splitting the single technical-sheet field. RACPRXACH/RACPRXACHDEV is the inventory value in €. RACSTACOD is the material status (DISPO, LOCATION, CONTROLE, PANNE…). AGECOD is the branch where it is located; CODE_AGENCE_AFFECTATION is the owning branch (in SMART it matches AGECOD). BOOL_MATERIEL_SOUS_LOC is “1” if the material is sub-rented.
RACCOD, GBV and GBVA come from the KIL_RACINE table provided by Kiloutou; until it is loaded, they are returned as null.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_EQUIPMENT ORDER BY RACNUMMAT"
}
]
}
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REF_EQUIPMENT WHERE RACSTACOD=:estado",
"params": { "estado" : "LOCATION"}
}
]
}
KIL_INVOICES
Returns the invoice lines issued within the interval [P_FECHA_DESDE, P_FECHA_HASTA) by issue date. Only real invoices (confirmed, not simulation, not pre-invoice).
Invoice lines come from delivery-note lines, because a delivery note can split its lines across several invoices. The line is identified by FACNUMRGT (delivery-note number) + FACNUMSEQ (delivery-note line number). FACCODTYPPIE distinguishes “Facture” and “Avoir” (credit note). FACNUMCTR is the contract number, or “DIRECT” if the invoice has no contract. FACMNTHT, FACMNTTVA and FACMNTTTC are the line's base, tax and total (5 decimals; the tax includes the equivalence surcharge). FACNBRJOULOC is the number of rental days in daily format.
LOCAL_ACCOUNT is the accounting account; KIL_ACCOUNT (FCCS) is mapped by Kiloutou.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_INVOICES(:desde, :hasta)",
"params": { "desde" : "1/6/2026", "hasta" : "1/7/2026"}
}
]
}
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_INVOICES(:desde, :hasta) WHERE FACCODAGECOM=:sucursal ORDER BY FACNUM",
"params": { "desde" : "1/6/2026", "hasta" : "1/7/2026", "sucursal" : "E031"}
}
]
}
KIL_CONTRACTS
Returns the rental-contract lines created within the interval [P_FECHA_DESDE, P_FECHA_HASTA) by issue date. Only real contracts (confirmed, not simulation, not pre-contract).
CTRCODSTA is the status: “3” (ongoing, if material is still out) or “9” (closed). SMART has no “cancelled” status (Z). CTRMNTHT/CTRMNTTVA/CTRMNTTTC are the contract amount (not the invoiced amount), computed as quantity × price × duration × discounts. CTRNBRJOU is the number of contract days. CTRDATCLO and CTRDATRET are the closing and material-return dates.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_CONTRACTS(:desde, :hasta)",
"params": { "desde" : "1/6/2026", "hasta" : "1/7/2026"}
}
]
}
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_CONTRACTS(:desde, :hasta) WHERE CTRCODSTA=:estado",
"params": { "desde" : "1/6/2026", "hasta" : "1/7/2026", "estado" : "3"}
}
]
}
KIL_REPAIR_ORDERS
Returns the repair orders (equipment maintenance) within the interval [P_FECHA_DESDE, P_FECHA_HASTA). It includes the maintenance delivery-note lines (with their quantity and amount detail) and also the open breakdown reports, which are issued as a header without detail so that response times can be measured.
NUMERO_OR is the order number (Kiloutou's “OR-0000031566” format cannot be reproduced; SMART's number is issued). CODE_STATUT_OR: “1” (open), “TERMINE” (in progress), “CLOS” (closed). These values are provisional pending Kiloutou's status logic.
Shortened column name: the template field PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR (35 characters) exceeds Firebird's 31-character identifier limit, so it is emitted as the output column PRIX_UNIT_VTE_CLIENT_LIGNE_OR. The export process maps it back to the full template name.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_REPAIR_ORDERS(:desde, :hasta)",
"params": { "desde" : "1/6/2026", "hasta" : "1/7/2026"}
}
]
}
KIL_SALES_USED_EQUIPMENTS
Returns the used-machinery sales invoiced within the interval [P_FECHA_DESDE, P_FECHA_HASTA). Invoiced lines with a serial number and unit “UD” are treated as equipment sales.
The TYPE_ and CONDITION_ fields map to the template's “type” and “condition” (the trailing underscore avoids a clash with reserved words). CONDITION_ is always “Used”. AGE_AT_SALE is the equipment's age in years at the time of sale; HOUR_METER_READING is the last real hour-meter reading before the sale.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_SALES_USED_EQUIPMENTS(:desde, :hasta) ORDER BY INVOICE_DATE",
"params": { "desde" : "1/1/2026", "hasta" : "1/1/2027"}
}
]
}
KIL_STOCK_SNAPSHOTS
Returns the fleet snapshot at date P_FECHA, computed on the fly (one row per equipment). It is retroactive: requesting a past date reconstructs the actual stock of that day.
PERJOU is the snapshot date. RACSTACOD is the material status on that day; CTRNUM is the contract if it was rented; AGECOD is the branch where it was located. STKQTETOT is 1 per equipment (identified material). PROCESS_STEP is returned as null (field pending definition by Kiloutou). RACCOD comes from KIL_RACINE.
Performance note: each call scans the whole movement history. Avoid chaining many dates during working hours.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha)",
"params": { "fecha" : "15/6/2026"}
}
]
}
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha) WHERE RACSTACOD=:estado",
"params": { "fecha" : "15/6/2026", "estado" : "DISPO"}
}
]
}
KIL_VALORISATION
Returns the valorisation at date P_FECHA: the expected turnover for the end of that date's month, computed on the fly, at contract-line level.
CTRJOUVAL is the valorisation date. CTRMNTVAL is the expected amount (it includes what has already been invoiced within the month). The last day of the month converges with that month's actual turnover.
Unlike the stock snapshot, valorisation does not reconstruct the past in the strict sense: it uses the current state of the contracts and only moves the horizon to the end of the requested date's month.
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT * FROM KIL_VALORISATION(:fecha)",
"params": { "fecha" : "30/6/2026"}
}
]
}
{
"version" : "1.2",
"metodo" : "Raw",
"opciones" : { "metaData":true },
"comandos" :
[
{
"sql":"SELECT CTRCODAGE, SUM(CTRMNTVAL) FROM KIL_VALORISATION(:fecha) GROUP BY CTRCODAGE",
"params": { "fecha" : "30/6/2026"}
}
]
}
Some fields cannot be computed from SMART and must be provided by Kiloutou. They are loaded into the KIL_RACINE table, which the procedures read with a LEFT JOIN: while it is empty, the dependent fields are returned as null and are filled in automatically as soon as it is loaded.
KIL_RACINE
RACCOD, GBV and GBVA feed the fields of the same name in the referentials and fact tables (Equipment, Stock snapshots, Invoices…). The FCCS consolidation account (KIL_ACCOUNT in Invoices) is mapped by Kiloutou in its platform.
KIL_RACINE is populated through an iterative spreadsheet round-trip:
KIL_RACINE, and exports it to an Excel sheet. The sheet has five columns: COD_ACTIVO and DESCRIPCION (already filled in, for identification only) and RACCOD, GBV, GBVA (empty, to be completed).RACCOD (Kiloutou root) and the finance team fills in GBV/GBVA. COD_ACTIVO and DESCRIPCION must not be changed.KIL_RACINE.Notes:
RACCOD are registered. Rows left without a root are ignored and will appear again in the next extraction, so the loop keeps requesting them until they are resolved.GBV and GBVA may be left empty in a given round and completed later; the amount fields accept decimals.— //Juanma 2026/07/30 // Repair Orders: column PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR shortened to PRIX_UNIT_VTE_CLIENT_LIGNE_OR (Firebird 31-char identifier limit).
— //Juanma 2026/07/30 // SmartWebDoc connection details (IP, port) and documentation link. Credentials configured ad-hoc on the service.
— //Juanma 2026/07/30 // KIL_RACINE loading workflow (spreadsheet round-trip).
— //Juanma 2026/07/30 // Initial document. SMART → Kiloutou extraction (EX06468): referentials, fact tables and snapshots.