Smartwiki

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== SMART → Kiloutou Group Data Platform data integration ======

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

----

====== General information ======

=== Access methods ===
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. **This service is already installed and running** at this installation. //SmartWebDoc is installed by Smart Software Solutions.// See the SmartWebDoc documentation: [[https://wiki.smartastur.com/doku.php?id=usu:smartweb:introduccion_a_smartwebdoc|Introducción a SmartWebDoc]].
  * **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.// This option is not set up; SmartWebDoc is the method in use.

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

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

For this installation: http://172.30.0.98:8000/services/ismartwebdoc/?format=json
=== Possible ports ===
  - 8000
=== Possible IP addresses ===
  - 172.30.0.98
=== User credentials ===
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.
=== Other ===
"Postman v8.0.4" was used for the examples.

SmartWebDoc documentation: [[https://wiki.smartastur.com/doku.php?id=usu:smartweb:introduccion_a_smartwebdoc|Introducción a SmartWebDoc]].

=== Response envelope ===
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.

== POST body ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_PRODUCT_FAMILIES"
    }
  ]
}
</code>
== Response (illustrative values) ==
<code>
{
    "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"
                }
            ]
        }
    ]
}
</code>

Notes on the response:
  - ''estado'' is "OK" when the command runs successfully.
  - 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.
  - ''datos'' is the list of rows. Empty fields are returned as ''null''.

----

====== General integration notes ======

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.

----

====== Referential: Branches ======
=== Procedure ===
KIL_REF_BRANCHES
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - AGECOD VARCHAR(10),
  - AGELIB VARCHAR(60),
  - AGELIBADR1 VARCHAR(60),
  - AGELIBADR2 VARCHAR(60),
  - AGELIBVIL VARCHAR(60),
  - AGENUMCP VARCHAR(15),
  - AGENUMDEP VARCHAR(60),
  - AGEDATOUV DATE,
  - AGEDATFER DATE,
  - AGECODGRO VARCHAR(10),
  - AGELIBGRO VARCHAR(60),
  - AGECODZON VARCHAR(10),
  - AGELIBZON VARCHAR(60),
  - AGELIBTEL VARCHAR(60),
  - AGELIBFAX VARCHAR(60),
  - AGELATITUDE NUMERIC(8,6),
  - AGELONGITUDE NUMERIC(9,6),
  - BOOL_STOCK_GERE_KILOTOOLS VARCHAR(3)
=== Behaviour ===
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).
=== Example ===
== POST body ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_BRANCHES ORDER BY AGECOD"
    }
  ]
}
</code>

----

====== Referential: Team members ======
=== Procedure ===
KIL_REF_TEAM_MEMBERS
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - EQUCOD VARCHAR(10),
  - EQUTIT VARCHAR(10),
  - EQULIBNOM VARCHAR(121),
  - EQULIBPRE VARCHAR(60),
  - EQUCODMAT VARCHAR(20)
=== Behaviour ===
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).
=== Example ===
== POST body ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_TEAM_MEMBERS ORDER BY EQUCOD"
    }
  ]
}
</code>

----

====== Referential: Customers ======
=== Procedure ===
KIL_REF_CUSTOMERS
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - CLICOD VARCHAR(10),
  - CLILIBRAISOC VARCHAR(60),
  - CLILIBADR1 VARCHAR(60),
  - CLILIBADR2 VARCHAR(60),
  - CLILIBADR3 VARCHAR(60),
  - CLILIBADR4 VARCHAR(60),
  - CLINUMCP VARCHAR(15),
  - CLILIBVIL VARCHAR(60),
  - DEPCOD VARCHAR(60),
  - CLIPAYCOD VARCHAR(10),
  - CLIFAX VARCHAR(60),
  - CLITEL VARCHAR(60),
  - CLIDATCRE TIMESTAMP,
  - CLITYP VARCHAR(4),
  - CLINUMSIREN VARCHAR(20),
  - CLINUMSIRET VARCHAR(20),
  - CLINUMTVA VARCHAR(20),
  - NAFCOD VARCHAR(10),
  - CLILIBNAF VARCHAR(60),
  - CODE_CLIENT_PARENT VARCHAR(10)
=== Behaviour ===
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''.
=== Example ===
== POST body ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_CUSTOMERS ORDER BY CLICOD"
    }
  ]
}
</code>

----

====== Referential: Construction Sites ======
=== Procedure ===
KIL_REF_CONSTRUCTION_SITES
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - CHACOD VARCHAR(10),
  - CHALIB VARCHAR(60),
  - CHALIBADR1 VARCHAR(60),
  - CHALIBADR2 VARCHAR(60),
  - CHANUMCP VARCHAR(15),
  - CHALIBVIL VARCHAR(60)
=== Behaviour ===
Returns the construction sites / work centres (''OBRA_TERCERO''), both active and inactive.
=== Example ===
== POST body ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_CONSTRUCTION_SITES ORDER BY CHACOD"
    }
  ]
}
</code>

----

====== Referential: Product Families ======
=== Procedure ===
KIL_REF_PRODUCT_FAMILIES
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - FAMCOD1 VARCHAR(10),
  - FAMLIB1 VARCHAR(30),
  - FAMCOD2 VARCHAR(10),
  - FAMLIB2 VARCHAR(30),
  - FAMCOD3 VARCHAR(10),
  - FAMLIB3 VARCHAR(30),
  - LNGCOD VARCHAR(3)
=== Behaviour ===
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...).
=== Example ===
See the full example (with response) in "General information → Response envelope".

----

====== Referential: Services ======
=== Procedure ===
KIL_REF_SERVICES
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - PRECOD VARCHAR(10),
  - PRELIB VARCHAR(60),
  - LNGCOD VARCHAR(3),
  - PRETYPFAM VARCHAR(6),
  - PREFAMCOD VARCHAR(10),
  - PREFAMLIB VARCHAR(30),
  - PRESOUFAMCOD VARCHAR(10),
  - PRESOUFAMLIB VARCHAR(30)
=== Behaviour ===
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.
=== Example ===
== POST body ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_SERVICES WHERE PRETYPFAM=:tipo",
      "params": { "tipo" : "Racine"}
    }
  ]
}
</code>

----

====== Referential: Equipment ======
=== Procedure ===
KIL_REF_EQUIPMENT
=== Parameters ===
None.
=== Results ===
  - PAYCOD VARCHAR(2),
  - RACNUMMAT VARCHAR(10),
  - RACCOD VARCHAR(20),
  - FAMCOD1 VARCHAR(10),
  - FAMCOD2 VARCHAR(10),
  - FAMCOD3 VARCHAR(10),
  - RACLIB VARCHAR(60),
  - RACDATCRE TIMESTAMP,
  - RACPRXACH NUMERIC(17,5),
  - RACPRXACHDEV NUMERIC(17,5),
  - RACCODTYP VARCHAR(5),
  - RACMRQ VARCHAR(60),
  - RACTYP VARCHAR(60),
  - GBV NUMERIC(17,5),
  - GBVA NUMERIC(17,5),
  - RACDATIMMAT DATE,
  - RACDATMISC DATE,
  - RACSTACOD VARCHAR(12),
  - AGECOD VARCHAR(10),
  - CODE_AGENCE_AFFECTATION VARCHAR(10),
  - NUM_MATERIEL_SOCIETE_ORIGINE VARCHAR(10),
  - POOL_NPOOL VARCHAR(5),
  - BOOL_MATERIEL_SOUS_LOC VARCHAR(1)
=== Behaviour ===
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''.
=== Example ===
== All equipment ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_EQUIPMENT ORDER BY RACNUMMAT"
    }
  ]
}
</code>
== Only the currently rented material ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_REF_EQUIPMENT WHERE RACSTACOD=:estado",
      "params": { "estado" : "LOCATION"}
    }
  ]
}
</code>

----

====== Fact table: Invoices ======
=== Procedure ===
KIL_INVOICES
=== Parameters ===
  - P_FECHA_DESDE TIMESTAMP,
  - P_FECHA_HASTA TIMESTAMP
=== Results ===
  - PAYCOD VARCHAR(2),
  - FACNUM VARCHAR(10),
  - FACCODAGECRE VARCHAR(10),
  - FACCODAGECOM VARCHAR(10),
  - FACCODAGENCA VARCHAR(10),
  - FACCODAGEAA VARCHAR(10),
  - FACDAT TIMESTAMP,
  - FACDATPIECE TIMESTAMP,
  - FACDATDEB TIMESTAMP,
  - FACDATFIN TIMESTAMP,
  - FACCODEQUCRE VARCHAR(10),
  - FACCODEQUMAJ VARCHAR(10),
  - FACCODATC VARCHAR(10),
  - FACCODTYPPIE VARCHAR(7),
  - FACNUMCTR VARCHAR(10),
  - CLICOD VARCHAR(10),
  - CHACOD VARCHAR(10),
  - FACLIBENL VARCHAR(60),
  - FACLIBADR1LIV VARCHAR(60),
  - FACLIBADR2LIV VARCHAR(60),
  - FACNUMCPLIV VARCHAR(15),
  - FACCODDEPLIV VARCHAR(60),
  - FACLIBVILLIV VARCHAR(60),
  - FACLIBPAYLIV VARCHAR(60),
  - FACLIBRAI VARCHAR(60),
  - FACLIBADR1 VARCHAR(60),
  - FACLIBADR2 VARCHAR(60),
  - FACNUMCP VARCHAR(15),
  - FACCODDEP VARCHAR(60),
  - FACLIBVIL VARCHAR(60),
  - FACLIBPAY VARCHAR(60),
  - FACLIBBONCOM VARCHAR(60),
  - FACCODCONREG VARCHAR(10),
  - FACCODMODREG VARCHAR(10),
  - FACDEVORI VARCHAR(10),
  - FACTAUCONDEV NUMERIC(17,5),
  - FACDATECH TIMESTAMP,
  - FACNUMRGT VARCHAR(10),
  - FACNUMSEQ VARCHAR(13),
  - PRECOD VARCHAR(10),
  - RACNUMMAT VARCHAR(10),
  - FACCODRACASS VARCHAR(20),
  - FACNBRQUA NUMERIC(17,5),
  - FACNBRJOULOC NUMERIC(17,5),
  - FACPRXUNI NUMERIC(17,5),
  - MONTANT_UNITAIRE_CATALOGUE NUMERIC(17,5),
  - MONTANT_UNITAIRE_CLIENT NUMERIC(17,5),
  - FACMNTHT NUMERIC(17,5),
  - FACMNTTVA NUMERIC(17,5),
  - FACMNTTTC NUMERIC(17,5),
  - FACTAUTVA NUMERIC(17,5),
  - FACLIBRAC VARCHAR(60),
  - FACNUM_ORI VARCHAR(10),
  - LOCAL_ACCOUNT VARCHAR(10),
  - KIL_ACCOUNT VARCHAR(10)
=== Behaviour ===
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.
=== Example ===
== One month's invoices ==
<code>
{
  "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"}
    }
  ]
}
</code>
== One month's invoices for a branch, ordered by number ==
<code>
{
  "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"}
    }
  ]
}
</code>

----

====== Fact table: Contracts ======
=== Procedure ===
KIL_CONTRACTS
=== Parameters ===
  - P_FECHA_DESDE TIMESTAMP,
  - P_FECHA_HASTA TIMESTAMP
=== Results ===
  - PAYCOD VARCHAR(2),
  - CTRNUM VARCHAR(10),
  - CTRNUMRGT VARCHAR(11),
  - CTRNUMSEQ VARCHAR(11),
  - CTRDATCRE TIMESTAMP,
  - CTRDATCLO TIMESTAMP,
  - CTRCODSTA VARCHAR(1),
  - PRECOD VARCHAR(10),
  - RACNUMMAT VARCHAR(10),
  - CTRCODAGECRE VARCHAR(10),
  - CTRCODAGECA VARCHAR(10),
  - CTRCODAGEAA VARCHAR(10),
  - CLICOD VARCHAR(10),
  - CTRCODTAR VARCHAR(10),
  - CTRCODEQU VARCHAR(10),
  - CTRNBRJOU NUMERIC(17,5),
  - CTRNBRQUA NUMERIC(17,5),
  - CTRPRXUNI NUMERIC(17,5),
  - MONTANT_UNITAIRE_CATALOGUE NUMERIC(17,5),
  - MONTANT_UNITAIRE_CLIENT NUMERIC(17,5),
  - CTRMNTHT NUMERIC(17,5),
  - CTRMNTTVA NUMERIC(17,5),
  - CTRMNTTTC NUMERIC(17,5),
  - CTRDATRET TIMESTAMP,
  - CTRNBRRET NUMERIC(17,5),
  - CTRDATDEBFAC TIMESTAMP,
  - CTRDATFINFAC TIMESTAMP,
  - CTRDATDERFAC TIMESTAMP,
  - CTRDATDEP TIMESTAMP,
  - CTRTXCONV NUMERIC(17,5),
  - CTR_TAUX_REMISE NUMERIC(17,5),
  - TAUX_ASSURANCE NUMERIC(17,5),
  - EMAIL_ENVOI_CONTRAT_SOURCE VARCHAR(254),
  - EMAIL_ENVOI_CONTRAT_DESTINATION VARCHAR(254),
  - DATE_ENVOI_EMAIL_CONTRAT TIMESTAMP
=== Behaviour ===
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.
=== Example ===
== One month's contracts ==
<code>
{
  "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"}
    }
  ]
}
</code>
== Only ongoing contracts ==
<code>
{
  "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"}
    }
  ]
}
</code>

----

====== Fact table: Repair Orders ======
=== Procedure ===
KIL_REPAIR_ORDERS
=== Parameters ===
  - P_FECHA_DESDE TIMESTAMP,
  - P_FECHA_HASTA TIMESTAMP
=== Results ===
  - PAYCOD VARCHAR(2),
  - NUMERO_OR VARCHAR(10),
  - CLE_UNIQUE_LIGNE_OR VARCHAR(22),
  - DATE_CREATION_OR TIMESTAMP,
  - DATE_CLOTURE_OR TIMESTAMP,
  - CODE_STATUT_OR VARCHAR(7),
  - CODE_NATURE_OR VARCHAR(10),
  - CODE_CAUSE_CLIENT VARCHAR(11),
  - RACNUMMAT VARCHAR(10),
  - COMPTEUR_OR NUMERIC(17,5),
  - RESPONSABLE VARCHAR(60),
  - DIAGNOSTIQUE VARCHAR(60),
  - CTRNUM_OR VARCHAR(10),
  - CLICOD VARCHAR(10),
  - CHACOD VARCHAR(10),
  - NUM_BDC VARCHAR(10),
  - DESIGNATION_MATERIEL_CLIENT VARCHAR(60),
  - AGECOD VARCHAR(10),
  - PRECOD VARCHAR(10),
  - QUANTITE_LIGNE_OR NUMERIC(17,5),
  - COUT_UNITAIRE_LIGNE_OR NUMERIC(17,5),
  - BOOL_LIGNE_OR_A_REFACTURER VARCHAR(1),
  - PRIX_UNIT_VTE_CLIENT_LIGNE_OR NUMERIC(17,5),   //(template field: PRIX_UNITAIRE_VENTE_CLIENT_LIGNE_OR)//
  - TAUX_REMISE_CLIENT_LIGNE_OR NUMERIC(17,5),
  - MONTANT_A_REFACTURER_LIGNE_OR NUMERIC(17,5),
  - INTERVENANT_LIGNE_OR VARCHAR(60),
  - BOOL_LIGNE_OR_REALISE VARCHAR(1)
=== Behaviour ===
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.
=== Example ===
== One month's repair orders ==
<code>
{
  "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"}
    }
  ]
}
</code>

----

====== Fact table: Sales used equipments ======
=== Procedure ===
KIL_SALES_USED_EQUIPMENTS
=== Parameters ===
  - P_FECHA_DESDE TIMESTAMP,
  - P_FECHA_HASTA TIMESTAMP
=== Results ===
  - INVOICE_DATE TIMESTAMP,
  - INVOICE_NUMBER VARCHAR(10),
  - INVOICE_LINES VARCHAR(22),
  - RACNUMMAT VARCHAR(10),
  - BRAND VARCHAR(60),
  - TYPE_ VARCHAR(60),
  - CONDITION_ VARCHAR(4),
  - UNIT_SELLING_PRICE NUMERIC(17,5),
  - QUANTITY_SOLD NUMERIC(17,5),
  - CUSTOMER_NAME VARCHAR(60),
  - AGE_AT_SALE NUMERIC(17,5),
  - HOUR_METER_READING NUMERIC(17,5)
=== Behaviour ===
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.
=== Example ===
== One year's used-equipment sales ==
<code>
{
  "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"}
    }
  ]
}
</code>

----

====== Fact table: Stock snapshots ======
=== Procedure ===
KIL_STOCK_SNAPSHOTS
=== Parameters ===
  - P_FECHA DATE
=== Results ===
  - PAYCOD VARCHAR(2),
  - PERJOU DATE,
  - AGECOD VARCHAR(10),
  - RACCOD VARCHAR(20),
  - RACNUMMAT VARCHAR(10),
  - CTRNUM VARCHAR(10),
  - PROCESS_STEP VARCHAR(20),
  - RACSTACOD VARCHAR(12),
  - STKQTETOT NUMERIC(17,5)
=== Behaviour ===
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.//
=== Example ===
== One day's stock snapshot ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_STOCK_SNAPSHOTS(:fecha)",
      "params": { "fecha" : "15/6/2026"}
    }
  ]
}
</code>
== One day's stock snapshot, available material only ==
<code>
{
  "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"}
    }
  ]
}
</code>

----

====== Fact table: Valorisation ======
=== Procedure ===
KIL_VALORISATION
=== Parameters ===
  - P_FECHA DATE
=== Results ===
  - PAYCOD VARCHAR(2),
  - CTRJOUVAL DATE,
  - CTRNUM VARCHAR(10),
  - CTRNUMRGT VARCHAR(11),
  - PRECOD VARCHAR(10),
  - CTRCODAGE VARCHAR(10),
  - CTRMNTVAL NUMERIC(17,5)
=== Behaviour ===
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.//
=== Example ===
== One day's valorisation ==
<code>
{
  "version"  : "1.2",
  "metodo"   : "Raw",
  "opciones" : { "metaData":true },
  "comandos" :
  [
    {
      "sql":"SELECT * FROM KIL_VALORISATION(:fecha)",
      "params": { "fecha" : "30/6/2026"}
    }
  ]
}
</code>
== One day's valorisation, grouped by branch ==
<code>
{
  "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"}
    }
  ]
}
</code>

----

====== Dependencies to be loaded by Kiloutou ======

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.

=== Table ===
KIL_RACINE
=== Columns ===
  - COD_ACTIVO VARCHAR(10)   //(key; equipment code in SMART, = RACNUMMAT)//
  - RACCOD VARCHAR(20)       //(Kiloutou root of the equipment; Fleet Dept.)//
  - GBV NUMERIC(17,5)        //(gross book value; finance team)//
  - GBVA NUMERIC(17,5)       //(adjusted gross book value; finance team)//
=== Usage ===
''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.

=== Loading workflow ===
''KIL_RACINE'' is populated through an iterative spreadsheet round-trip:

  - **Extract the pending equipment.** Smart runs a procedure that returns every asset not yet present in ''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).
  - **Send the sheet to Kiloutou.** The Fleet Dept. fills in ''RACCOD'' (Kiloutou root) and the finance team fills in ''GBV''/''GBVA''. ''COD_ACTIVO'' and ''DESCRIPCION'' must not be changed.
  - **Return and import.** Smart loads the completed sheet back into SmartOffice and processes it into ''KIL_RACINE''.
  - **Repeat** until step 1 returns no rows, i.e. every asset has a Kiloutou root.

Notes:
  - Only rows that come back **with a ''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.
  - New equipment added after a round is automatically picked up by the next extraction.

----

== Document change log ==
 --- //[[juanma@smartastur.com|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@smartastur.com|Juanma]] 2026/07/30 // SmartWebDoc connection details (IP, port) and documentation link. Credentials configured ad-hoc on the service.

 --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // KIL_RACINE loading workflow (spreadsheet round-trip).

 --- //[[juanma@smartastur.com|Juanma]] 2026/07/30 // Initial document. SMART → Kiloutou extraction (EX06468): referentials, fact tables and snapshots.