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:

• SmartWebDoc (web service): the procedures are called over HTTP with a POST request, as described below and used throughout the examples of this document. SmartWebDoc can be installed by Smart Software Solutions.
• ODBC: alternatively, the same procedures can be queried directly through an ODBC connection to the Firebird database. The procedure and field names are identical, and the 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.

At present the company has neither of the two services installed; one of them must be set up before the integration can run.

The connection details and examples below refer to the SmartWebDoc service.

POST

http://#ip:#port/services/ismartwebdoc/?format=json

1. (to be defined)

1. (to be defined)

These credentials are not required by the interface, but are stated here so that the Smart administrator can set access restrictions or privileges on tables and procedures.

1. Id: (to be provided by the Smart administrator)
2. Password: (to be provided by the Smart administrator)

“Postman v8.0.4” was used for the examples.

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:

1. estado is “OK” when the command runs successfully.
2. In 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.
3. datos is the list of rows. Empty fields are returned as null.

Aspects common to all datasets:

• Scope: 14 datasets (7 fact tables and 8 referentials, except Telephony, which does not exist in SMART).
• Country: the PAYCOD field is always “ES”.
• Third-party codes: customer, employee, etc. codes are issued without a type prefix (e.g. “027893”, not “CL-027893”).
• Date range of the fact tables: the 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”.
• Snapshots: the 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).
• VAT (Invoices, Contracts): in SMART, VAT is computed at invoice closing, not line by line. In the extraction it is split per line with 5 decimals, and the tax amount includes the equivalence surcharge when the customer is subject to it; this way 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.

1. PAYCOD VARCHAR(2),
2. AGECOD VARCHAR(10),
3. AGELIB VARCHAR(60),
4. AGELIBADR1 VARCHAR(60),
5. AGELIBADR2 VARCHAR(60),
6. AGELIBVIL VARCHAR(60),
7. AGENUMCP VARCHAR(15),
8. AGENUMDEP VARCHAR(60),
9. AGEDATOUV DATE,
10. AGEDATFER DATE,
11. AGECODGRO VARCHAR(10),
12. AGELIBGRO VARCHAR(60),
13. AGECODZON VARCHAR(10),
14. AGELIBZON VARCHAR(60),
15. AGELIBTEL VARCHAR(60),
16. AGELIBFAX VARCHAR(60),
17. AGELATITUDE NUMERIC(8,6),
18. AGELONGITUDE NUMERIC(9,6),
19. BOOL_STOCK_GERE_KILOTOOLS VARCHAR(3)

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.

1. PAYCOD VARCHAR(2),
2. EQUCOD VARCHAR(10),
3. EQUTIT VARCHAR(10),
4. EQULIBNOM VARCHAR(121),
5. EQULIBPRE VARCHAR(60),
6. EQUCODMAT VARCHAR(20)

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.

1. PAYCOD VARCHAR(2),
2. CLICOD VARCHAR(10),
3. CLILIBRAISOC VARCHAR(60),
4. CLILIBADR1 VARCHAR(60),
5. CLILIBADR2 VARCHAR(60),
6. CLILIBADR3 VARCHAR(60),
7. CLILIBADR4 VARCHAR(60),
8. CLINUMCP VARCHAR(15),
9. CLILIBVIL VARCHAR(60),
10. DEPCOD VARCHAR(60),
11. CLIPAYCOD VARCHAR(10),
12. CLIFAX VARCHAR(60),
13. CLITEL VARCHAR(60),
14. CLIDATCRE TIMESTAMP,
15. CLITYP VARCHAR(4),
16. CLINUMSIREN VARCHAR(20),
17. CLINUMSIRET VARCHAR(20),
18. CLINUMTVA VARCHAR(20),
19. NAFCOD VARCHAR(10),
20. CLILIBNAF VARCHAR(60),
21. CODE_CLIENT_PARENT VARCHAR(10)

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.

1. PAYCOD VARCHAR(2),
2. CHACOD VARCHAR(10),
3. CHALIB VARCHAR(60),
4. CHALIBADR1 VARCHAR(60),
5. CHALIBADR2 VARCHAR(60),
6. CHANUMCP VARCHAR(15),
7. CHALIBVIL VARCHAR(60)

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.

1. PAYCOD VARCHAR(2),
2. FAMCOD1 VARCHAR(10),
3. FAMLIB1 VARCHAR(30),
4. FAMCOD2 VARCHAR(10),
5. FAMLIB2 VARCHAR(30),
6. FAMCOD3 VARCHAR(10),
7. FAMLIB3 VARCHAR(30),
8. LNGCOD VARCHAR(3)

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.

1. PAYCOD VARCHAR(2),
2. PRECOD VARCHAR(10),
3. PRELIB VARCHAR(60),
4. LNGCOD VARCHAR(3),
5. PRETYPFAM VARCHAR(6),
6. PREFAMCOD VARCHAR(10),
7. PREFAMLIB VARCHAR(30),
8. PRESOUFAMCOD VARCHAR(10),
9. PRESOUFAMLIB VARCHAR(30)

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.

1. PAYCOD VARCHAR(2),
2. RACNUMMAT VARCHAR(10),
3. RACCOD VARCHAR(20),
4. FAMCOD1 VARCHAR(10),
5. FAMCOD2 VARCHAR(10),
6. FAMCOD3 VARCHAR(10),
7. RACLIB VARCHAR(60),
8. RACDATCRE TIMESTAMP,
9. RACPRXACH NUMERIC(17,5),
10. RACPRXACHDEV NUMERIC(17,5),
11. RACCODTYP VARCHAR(5),
12. RACMRQ VARCHAR(60),
13. RACTYP VARCHAR(60),
14. GBV NUMERIC(17,5),
15. GBVA NUMERIC(17,5),
16. RACDATIMMAT DATE,
17. RACDATMISC DATE,
18. RACSTACOD VARCHAR(12),
19. AGECOD VARCHAR(10),
20. CODE_AGENCE_AFFECTATION VARCHAR(10),
21. NUM_MATERIEL_SOCIETE_ORIGINE VARCHAR(10),
22. POOL_NPOOL VARCHAR(5),
23. BOOL_MATERIEL_SOUS_LOC VARCHAR(1)

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

1. P_FECHA_DESDE TIMESTAMP,
2. P_FECHA_HASTA TIMESTAMP

1. PAYCOD VARCHAR(2),
2. FACNUM VARCHAR(10),
3. FACCODAGECRE VARCHAR(10),
4. FACCODAGECOM VARCHAR(10),
5. FACCODAGENCA VARCHAR(10),
6. FACCODAGEAA VARCHAR(10),
7. FACDAT TIMESTAMP,
8. FACDATPIECE TIMESTAMP,
9. FACDATDEB TIMESTAMP,
10. FACDATFIN TIMESTAMP,
11. FACCODEQUCRE VARCHAR(10),
12. FACCODEQUMAJ VARCHAR(10),
13. FACCODATC VARCHAR(10),
14. FACCODTYPPIE VARCHAR(7),
15. FACNUMCTR VARCHAR(10),
16. CLICOD VARCHAR(10),
17. CHACOD VARCHAR(10),
18. FACLIBENL VARCHAR(60),
19. FACLIBADR1LIV VARCHAR(60),
20. FACLIBADR2LIV VARCHAR(60),
21. FACNUMCPLIV VARCHAR(15),
22. FACCODDEPLIV VARCHAR(60),
23. FACLIBVILLIV VARCHAR(60),
24. FACLIBPAYLIV VARCHAR(60),
25. FACLIBRAI VARCHAR(60),
26. FACLIBADR1 VARCHAR(60),
27. FACLIBADR2 VARCHAR(60),
28. FACNUMCP VARCHAR(15),
29. FACCODDEP VARCHAR(60),
30. FACLIBVIL VARCHAR(60),
31. FACLIBPAY VARCHAR(60),
32. FACLIBBONCOM VARCHAR(60),
33. FACCODCONREG VARCHAR(10),
34. FACCODMODREG VARCHAR(10),
35. FACDEVORI VARCHAR(10),
36. FACTAUCONDEV NUMERIC(17,5),
37. FACDATECH TIMESTAMP,
38. FACNUMRGT VARCHAR(10),
39. FACNUMSEQ VARCHAR(13),
40. PRECOD VARCHAR(10),
41. RACNUMMAT VARCHAR(10),
42. FACCODRACASS VARCHAR(20),
43. FACNBRQUA NUMERIC(17,5),
44. FACNBRJOULOC NUMERIC(17,5),
45. FACPRXUNI NUMERIC(17,5),
46. MONTANT_UNITAIRE_CATALOGUE NUMERIC(17,5),
47. MONTANT_UNITAIRE_CLIENT NUMERIC(17,5),
48. FACMNTHT NUMERIC(17,5),
49. FACMNTTVA NUMERIC(17,5),
50. FACMNTTTC NUMERIC(17,5),
51. FACTAUTVA NUMERIC(17,5),
52. FACLIBRAC VARCHAR(60),
53. FACNUM_ORI VARCHAR(10),
54. LOCAL_ACCOUNT VARCHAR(10),
55. KIL_ACCOUNT VARCHAR(10)

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

1. P_FECHA_DESDE TIMESTAMP,
2. P_FECHA_HASTA TIMESTAMP

1. PAYCOD VARCHAR(2),
2. CTRNUM VARCHAR(10),
3. CTRNUMRGT VARCHAR(11),
4. CTRNUMSEQ VARCHAR(11),
5. CTRDATCRE TIMESTAMP,
6. CTRDATCLO TIMESTAMP,
7. CTRCODSTA VARCHAR(1),
8. PRECOD VARCHAR(10),
9. RACNUMMAT VARCHAR(10),
10. CTRCODAGECRE VARCHAR(10),
11. CTRCODAGECA VARCHAR(10),
12. CTRCODAGEAA VARCHAR(10),
13. CLICOD VARCHAR(10),
14. CTRCODTAR VARCHAR(10),
15. CTRCODEQU VARCHAR(10),
16. CTRNBRJOU NUMERIC(17,5),
17. CTRNBRQUA NUMERIC(17,5),
18. CTRPRXUNI NUMERIC(17,5),
19. MONTANT_UNITAIRE_CATALOGUE NUMERIC(17,5),
20. MONTANT_UNITAIRE_CLIENT NUMERIC(17,5),
21. CTRMNTHT NUMERIC(17,5),
22. CTRMNTTVA NUMERIC(17,5),
23. CTRMNTTTC NUMERIC(17,5),
24. CTRDATRET TIMESTAMP,
25. CTRNBRRET NUMERIC(17,5),
26. CTRDATDEBFAC TIMESTAMP,
27. CTRDATFINFAC TIMESTAMP,
28. CTRDATDERFAC TIMESTAMP,
29. CTRDATDEP TIMESTAMP,
30. CTRTXCONV NUMERIC(17,5),
31. CTR_TAUX_REMISE NUMERIC(17,5),
32. TAUX_ASSURANCE NUMERIC(17,5),
33. EMAIL_ENVOI_CONTRAT_SOURCE VARCHAR(254),
34. EMAIL_ENVOI_CONTRAT_DESTINATION VARCHAR(254),
35. DATE_ENVOI_EMAIL_CONTRAT TIMESTAMP

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

1. P_FECHA_DESDE TIMESTAMP,
2. P_FECHA_HASTA TIMESTAMP

1. PAYCOD VARCHAR(2),
2. NUMERO_OR VARCHAR(10),
3. CLE_UNIQUE_LIGNE_OR VARCHAR(22),
4. DATE_CREATION_OR TIMESTAMP,
5. DATE_CLOTURE_OR TIMESTAMP,
6. CODE_STATUT_OR VARCHAR(7),
7. CODE_NATURE_OR VARCHAR(10),
8. CODE_CAUSE_CLIENT VARCHAR(11),
9. RACNUMMAT VARCHAR(10),
10. COMPTEUR_OR NUMERIC(17,5),
11. RESPONSABLE VARCHAR(60),
12. DIAGNOSTIQUE VARCHAR(60),
13. CTRNUM_OR VARCHAR(10),
14. CLICOD VARCHAR(10),
15. CHACOD VARCHAR(10),
16. NUM_BDC VARCHAR(10),
17. DESIGNATION_MATERIEL_CLIENT VARCHAR(60),
18. AGECOD VARCHAR(10),
19. PRECOD VARCHAR(10),
20. QUANTITE_LIGNE_OR NUMERIC(17,5),
21. COUT_UNITAIRE_LIGNE_OR NUMERIC(17,5),
22. BOOL_LIGNE_OR_A_REFACTURER VARCHAR(1),
23. PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR NUMERIC(17,5),
24. TAUX_REMISE_CLIENT_LIGNE_OR NUMERIC(17,5),
25. MONTANT_A_REFACTURER_LIGNE_OR NUMERIC(17,5),
26. INTERVENANT_LIGNE_OR VARCHAR(60),
27. BOOL_LIGNE_OR_REALISE VARCHAR(1)

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.

{
  "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

1. P_FECHA_DESDE TIMESTAMP,
2. P_FECHA_HASTA TIMESTAMP

1. INVOICE_DATE TIMESTAMP,
2. INVOICE_NUMBER VARCHAR(10),
3. INVOICE_LINES VARCHAR(22),
4. RACNUMMAT VARCHAR(10),
5. BRAND VARCHAR(60),
6. TYPE_ VARCHAR(60),
7. CONDITION_ VARCHAR(4),
8. UNIT_SELLING_PRICE NUMERIC(17,5),
9. QUANTITY_SOLD NUMERIC(17,5),
10. CUSTOMER_NAME VARCHAR(60),
11. AGE_AT_SALE NUMERIC(17,5),
12. HOUR_METER_READING NUMERIC(17,5)

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

1. P_FECHA DATE

1. PAYCOD VARCHAR(2),
2. PERJOU DATE,
3. AGECOD VARCHAR(10),
4. RACCOD VARCHAR(20),
5. RACNUMMAT VARCHAR(10),
6. CTRNUM VARCHAR(10),
7. PROCESS_STEP VARCHAR(20),
8. RACSTACOD VARCHAR(12),
9. STKQTETOT NUMERIC(17,5)

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

1. P_FECHA DATE

1. PAYCOD VARCHAR(2),
2. CTRJOUVAL DATE,
3. CTRNUM VARCHAR(10),
4. CTRNUMRGT VARCHAR(11),
5. PRECOD VARCHAR(10),
6. CTRCODAGE VARCHAR(10),
7. CTRMNTVAL NUMERIC(17,5)

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

1. COD_ACTIVO VARCHAR(10) (key; equipment code in SMART, = RACNUMMAT)
2. RACCOD VARCHAR(20) (Kiloutou root of the equipment; Fleet Dept.)
3. GBV NUMERIC(17,5) (gross book value; finance team)
4. GBVA NUMERIC(17,5) (adjusted gross book value; finance team)

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.

— //Juanma 2026/07/30 // Initial document. SMART → Kiloutou extraction (EX06468): referentials, fact tables and snapshots.