EULANDA® ERP System Head Start thru Technology

Tables (SQL API)

Back | Level back

The individual data records will be saved in the tables. Some tables put on  for example by Solution-Partner must have the prefix custom. 

System fields

Usually all tables contain system fields. These fields are called: 

ID

It is a sequentially assigned number in the table from the server which is absolutely clear. Also with delete or change a data record by the server a new ID will be assigned. All relations in the EULANDA® data base system are over IDs linked with one another. You can 100% abandoned on it that you can respond the data record via its ID, the one reference on another table need the used name like AddressID, HeadID, ArticleID and more. The field AddressID of a offer header data record points to the ID field of the address.

The fields ID may be set in no case by an import, the data record would be repelled by the server with an error message.

CreateDate

Contains the putting on date of the data record. The field won't be used by all tables. The field if it is used will be set automatically by the server.

ChangeDate

Contains the changed date of the data record. The field won't be used by all tables. The field if it is used will be set automatically by the server.

Locked

This field is planned for a long term lock of a data record. Thereby it will be possible for example to page out and lock a complete offer.

Stamp

The field is set exclusively by the server. It is needed for the support of the index server, but also to accomplish replication over time-adjust and the cache. It contains a kind of time stamp, of these is however binary coded and not as time representably. On the field stamp it can be determined whether if a data record was changed.

User tables

The following tables are used in the EULANDA®:

Address (ad)

Contains all addresses, also debtors, account-payable and prospective customer. The distinction of the debtors, account-payable and normal addresses taken place for example via examination for a relation to calculation or orders.

Offer (an)

Here the offer header data are stored. The field addressID points to those assigned address of the offer.

Offer VAT (anm)

The VAT rates 1-3 are copied from the constant of the tables in the offer plant. Changes at the constant are valid only for new offers.

OfferPos (anp)

Offer positions. The connection to the Head data is been made by the field KopfID, which is pointing to the ID of the offer table.

OfferPosAddition (anpz)

Contains the position impacts of the offer positions. Changes at this table release appropriate trigger in the offer positions and update there the trigger fields. The table is only evaluated in the craftsman system.

OfferAddition(anz)

Addition table on the triggered head data price fields of the offer works. The table will only be evaluated in the craftsman system.

Article (ar)

Contains the article data.

ArticleIdent (ari)

Contains the serial numbers of the articles.

ArticleAddition(arz)

Contains addition data which will be transferred to the position addition table. The addition administration is intended for the craftsman system.

Order (af)

The order head data are stored here. The field addressID shows the assigned address of the order.

Order VAT (afm)

The VAT rates 1-3 in the case of the order plant this table are copied from the constants. Changes at the constants are valid thus only for new orders.

OrderPos (afp)

Order positions. The connection to the head data is been made by the field headID, which points to the ID of the table order.

OrderPosExpiration

Planned for a flow control of position events. The table is not evaluated at present.

OrderPosAddition (afpz)

Contains the position addition of the order positions. Changes at this table release appropriate triggers in the order positions and update there the trigger fields. The table is only evaluated in the craftsman system.

OrderAddition (afz)

Addition table on the triggered head data fields of the order works. The table is only evaluated in the craftsman system.

Picture

This table is for the picture data base planned. In one of the later versions of EULANDA® pictures won't be stored no more into the tables like articles or addresses but similarly as the characteristics into their own N:N structure. Pictures can be used in such a way by different data records, although these are only once present. You can assign many pictures to one data record for example to the article. The table will be used presumably  in the version 2.0 of EULANDA®.

Letter (br)

Contains all letters of the system. A link to the address is possible over the field address  ID, but not compelling (e.g. with serial letters).

cnDefaults

Contains the default value, which could be changed under the accessories settings. If the default values will be changed, so the META data of the SQL server are provided with these Default values, too. The saving of data records with foreign programs considers thereby the EULANDA® default values automatically.

cnDefaultsColumns

In this table all references are listed in which the default values are to be converted.

cnJob

Used to reach server steered processing from arbitrary jobs. That could be the processing of  emails which will be processed in the system.

cnJobInfo

Header record to the table cnJob

cnLbJob

Header record to the table cnLbJobItem

cnLbJobItem

Stock movements are realized in EULANDA® by stored procedures. If serial numbers or batch numbers will be administrated, you have to store the data in the table before. The stored procedures process these temporary table.

cnLicence

Contains all serial numbers and offerername of the auxiliary modules, which are installed in the system.

cnMessages

Message output to enumerating types e.g. status printed, booked etc.. In the type 0 stands the designation of the type. In SubType stands the translation of the field values.

cnObjectLog

For arbitrary loggings intended. This table is not evaluated at present yet.

cnObjects

Header record to the table cnObjects.

cnRegBlob

For the later development of the SQL registry intended. Here binary fields are stored, thus pictures, large text or other binary coded data. The structure of the SQL registry have to change so in each case only the stored procedures cn_REGxxx have access of the SQL registry.

cnRegistration

Contains the registry data of the licensee. These cannot be changed later, in this case all serial numbers become invalid and the system works afterwards only as demo version.

cnRegKey

For the later development of the SQL registry intended. Here are the SQL registry nodes stored. The structure of the SQL registry have to change so in each case only the stored procedures cn_REGxxx have access of the SQL registry.

cnRegText

For the later development of the SQL registry intended. Here are the strings stored. The structure of the SQL registry have to change so in each case only the stored procedures cn_REGxxx have access of the SQL registry.

cnRegValue

For the later development of the SQL registry intended. Here are the numerical values stored. The structure of the SQL registry have to change so in each case only the stored procedures cn_REGxxx have access of the SQL registry.

cnTraAfLfJob

To transfer header record of the processing queue around orders into delivery notes.

cnTraAfLfJobItem

Position record of the table cnTraAfLfJob

cnTraLfReJob

To transfer header record of the processing queue around delivery notes in calculations.

cnTraLfReJobItem

Position record of the table cnTraLfReJob

cnUser

Planned around NT users on SQL users to briefcases.

customXX

User tables could begin with the prefix custom. According to standard no custom tables are spent. These can be individually put on however by solution partner.

KonBank

Contain the bank code number and the name of the institutes. The table is not used at present  .

KonRevenueAccount

Here are put down the groups of proceeds. Each group of proceeds For each group of proceeds the revue accounts for inland, foreign country and European Union countries become deposit.

KonKG

Stores the customer groups.

KonKoord

Contains the prepared geo coordinates regarding postal zip code ranges.

konLand

Is intended for the conversion of the country codes in ISO, KFZ-Codes etc. The table will only be used with the multi currency module.

KonQuantitiesEh

The system contains admitted quantity units. In the article stock only articles can be put on, to which also the quantity unit is defined in this table.

KonVATGr

The possible VAT groups would save with the VAT range and the Fibu account. Theoretically as many as desired different VAT rates are possible, in the forms and the indicator modules however only the group of VAT of 2 and 3 are evaluated.

KonNr

Contains the sets of numbers to the procedures such as offers, delivery notes, debtors etc. The table is never headed for directly for storing, but over Stored-Procedures. The stored procedures with those the sets of numbers to be maintained is called: cn_NumGetAll, cn_NumGetNext, cn_NumSetNext.

KonOptions

Steers the behavior of the order which would be booked. It consists at present of a field, which can take the values 0, 1 and 2.

0 means: When converting an order into a delivery note this is set into seizing. Is is no stock movement produced.

1 means: When converting an order into an delivery note this is deduced by the camp and booked on a suspence account. On the customer account remains accordingly one supply commitment.

2 means: When converting an order the camp is deducted directly. If the serial numbers in the position should be needed, then these are between-booked as DIVERSE.

KonRG

Contains the groups of discounts in the constant are defined.

KonRgKg

Connects the customer group with the group of discounts permits this connection to provide with an position based discount.

konModeOfShipment

Contains all modes of shipment, which are defined in the constants.

konCurrency

Contains the currencies, international currency codes and designations.

konWG

Contains all categories of commodities, which were defined in the constants.

KonGoal

Contains all terms of payment, which are defined in the constants.

KrArticle

Store the account payable articles. This table contains supplier-specific data, like prices, groups of discount, article numbers etc. and points directly with the articleID field to the source of the corresponding major article. The field KreditorId points to the account payable of the table account payable.

AccountPayable

This table contains information about the payable account. Essentially here is a pointer, which contain the addressID which shows the pertinent address master data of the address table.

KrRg

Contains the account payable-specific groups of discounts. A care is possible for example over the Datanorm-Module.

KrWg

Contains the account payable-specific groups of ware-groups. A care is possible for example over the Datanorm-Module.

StockPostingRecord(lbs)

Contains the header record of a stock item series.

StockItemLine(lbz)

Contains the individual accounting lines to a stock item.

GroupOfCamps(lg)

Prepared for different groups of camps such as branches etc.

MatrixOfGroupsOfCamps(lgm)

Allocation which camp types (A,B,C etc.) will be booked on which account.

StockAccount(lk)

Qualified table those already has information to article, serial number, quantity etc.. The table is maintained automatically.

StockLocation(lo)

Contains the stock locations, whereby it could be the stock location here the ware-camp, the commitment camp or the demand camp.

StockPile(lp)

Partitioning of a main warehouse with own existence and min./max. defaults. The table will not be supported in the future.

CampType(lt)

Constant table with the stock types A, B, C etc.

DeliveryNote(lf)

Here are the delivery note data stored. The field addressID shows assigned to the delivery note.

DeliveryNoteHeadSource

This table is mainly used internally and serves for the performance increase (starting from EULANDA® 1.0.13). The table contains a cross reference from which orders this delivery note was produced. The table may be modified in no case manually, since this is administered fully automatic by triggers in the SQL server.

DeliveryNotePosition(lfp)

The table may be modified in no case manually, since this is administered fully automatic by triggers in the SQL server.

DeliveryNoteSource(lfq)

Contains the cross table of the order positions from which the delivery note positions is produced.

Properties

The property nodes contains of all properties to all tables. In these properties among other things also the SQL properties (dynamic properties) become. All jobs are additional stored with serial letters to be put on here.

PropertyElement

All jobs are additional stored with serial letters to be put on here.

Messages

contains plain language error message, which are spent by the SQL server only as numbers. Medium-term this table is removed and shifted into the master data base SQL server, which possesses an appropriate possibility.

Packet

Contains the package to a transmission, thus to a delivery note. For dispatch system a special API is made available, which places prepared delivery notes and packages on the one hand to the order and on the other hand routines for setting status values and tracking numbers as well as feed data permits.

Price (pr)

The prices contain to the articles. The relay  contain the article ID, the number of the relay 1-n and the prices. The name of the price list is borne in the table price and over the field price list which to the ID of the table price list points , indicated.

PriceList (pl)

Contains the names such as currency etc. of the defined price lists and their global properties.

PriceAddition

Are prepared price list-based processing impact.

Calculation(re)

Here the billhead data are stored. The field address ID shows assigned address to those the calculation.

CalculationHeadSource

This table is mainly used internally and serves for the performance increase (starting from EULANDA® 1.0.13). The table contains a cross reference from which delivery note this calculation was produced. The table may be modified in no case manually, since this is administered fully automatic by triggers in the SQL server.

CalculationVAT(rem)

The VAT rates 1-3 are copied with the calculation plant from the constants into this table. Changes at the constants are valid thus only for new calculations.

CalculationPosition (rep)

Calculation positions. The connection to the head data is made by the field head ID, which points to the ID of the table calculation.

CalculationPosAddition

Contains the position-addition of the calculation-addition. Changes at this table release appropriate triggers in the invoice positions and update the trigger fields. The table is only evaluated in the craftsman system.

CalculationSource

Contains the cross table from which the invoice position is produced by the delivery note position.

CalculationZe

Contains the receipts of payment to the calculation. The calculation is balanced , if all receipts of payment with the invoice amount adds 0 devoted.

CalculationAddition

Addition table on the triggered head data price fields of the calculation works. The table is only evaluatede in the craftsman system.

Registry

The complete SQL registry contains the inclusive Nodes etc. The table directly are to be accessed no case. Here only the stored procedures should be used, since the structure of the SQL registry constantly extends. The stored procedures with those the registry is called cn_REGxxxx where everything is accessed.

TextModule

The table is intended for the administration of pure text modules and is not used at present.

Representative (vt)

Contain the representatives. The reference ID from the other tables, which shows the representative tables are called representativeIDs.

WebLink

Is intended for the dynamic administration of URLs. Thereby several URLs can be assigned to a data record. The table is not used up-to-date yet.

Addition

Definition for the addition of positions.

©Eulanda Software GmbH Made in Germany, 1989-2010. All rights reserved.