Customer

Tabelle: customer
Domain: Customer · com.reybex.domain.pms
Modul: SAL

Partner-Hinweise

• Daten liegen mandantengetrennt – bei SELECT fast immer tenant_id = :mandant setzen (Spalte ggf. TenantId in älteren Tabellen).
• Schreibende SQL (INSERT/UPDATE/DELETE) nur in abgestimmten Export-/Report-Jobs (Transaktion BAS23) und nie ohne Freigabe durch reybex.
• Für API-Integrationen bevorzugt die API statt direkter DB-Zugriffe.

→ Beispiel-SQLs (BAS23)

Primärschlüssel

• id

Spalten

Spalte	SQL-Typ	NULL	Default	Beschreibung
id	bigint auto_increment	Ja	—	Primärschlüssel (Domain: id)
is_template	smallint	Nein	0	Boolean: Template
tenant_id	bigint	Nein	—	Mandanten-ID (Multi-Tenant – in Abfragen fast immer filtern) (Domain: tenantId)
name	varchar(200)	Nein	—	Bezeichnung / Name (Domain: name)
code	varchar(100)	Ja	—	Code / Kurzkennzeichen (Domain: code)
street	varchar(255)	Ja	—	Domain-Feld street
zipcode	varchar(10)	Ja	—	Domain-Feld zipcode
province	varchar(50)	Ja	—	Domain-Feld province
city	varchar(150)	Ja	—	Domain-Feld city
po_box	varchar(50)	Ja	—	Domain-Feld poBox
po_zipcode	varchar(50)	Ja	—	Domain-Feld poZipcode
phone	varchar(50)	Ja	—	Domain-Feld phone
fax	varchar(50)	Ja	—	Domain-Feld fax
email	varchar(100)	Ja	—	Domain-Feld email
web	varchar(100)	Ja	—	Domain-Feld web
allow_activity	smallint	Ja	1	allow activity
customer_no	varchar(20)	Ja	—	Domain-Feld customerNo
datev_debitor_no	varchar(10)	Ja	—	Domain-Feld datevDebitorNo
is_delete	smallint	Nein	0	Soft-Delete (0 = aktiv, 1 = gelöscht)
search_keys	varchar(1000)	Ja	—	Domain-Feld searchKeys
profit_center_id	bigint	Ja	—	Fremdschlüssel → profit center
contact_type_id	bigint	Ja	—	Fremdschlüssel → contact type
show_in_pms	smallint	Ja	0	show in pms
changed	date	Ja	—	changed
changed_by	bigint	Ja	—	changed by
created_by	bigint	Ja	—	created by
mobile	varchar(30)	Ja	—	Domain-Feld mobile
created	date	Ja	—	created
referenced_by_id	bigint	Ja	—	Fremdschlüssel → referenced by
employee_count	int	Ja	0	Domain-Feld employeeCount
main_contact_id	bigint	Ja	—	Fremdschlüssel → main contact
country_id	bigint	Ja	—	Fremdschlüssel → country
notes	longtext	Ja	—	Interne Notizen (Domain: notes)
branch	varchar(500)	Ja	—	Domain-Feld branch
customer_type_id	bigint	Ja	—	Fremdschlüssel → customer type
competitor_it	varchar(255)	Ja	—	Domain-Feld competitorIt
source	varchar(255)	Ja	—	Domain-Feld source
lead_status_id	bigint	Ja	—	Fremdschlüssel → lead status
volume_of_assets	double	Ja	0	Domain-Feld volumeOfAssets
status_reason	varchar(255)	Ja	—	Domain-Feld statusReason
file_id	bigint	Ja	—	Fremdschlüssel → file
tax_department	varchar(100)	Ja	—	Domain-Feld taxDepartment
tax_number	varchar(100)	Ja	—	Domain-Feld taxNumber
rating_id	bigint	Ja	—	Fremdschlüssel → rating
is_dunning	smallint	Ja	1	Boolean: Dunning
name2	varchar(400)	Ja	—	Domain-Feld name2
in_holding	smallint	Ja	0	Domain-Feld inHolding
currency_id	bigint	Ja	—	Fremdschlüssel → currency
is_locked	smallint	Ja	0	Boolean: Locked
language_id	bigint	Ja	—	Fremdschlüssel → language
shipping_id	bigint	Ja	—	Fremdschlüssel → shipping
freight_id	bigint	Ja	—	Fremdschlüssel → freight
pick_up_time_from	varchar(5)	Ja	—	Domain-Feld pickUpTimeFrom
pick_up_time_until	varchar(5)	Ja	—	Domain-Feld pickUpTimeUntil
main_address_id	bigint	Ja	—	Fremdschlüssel → main address
ustdid	varchar(50)	Ja	—	Domain-Feld ustdid
vat_key_id	bigint	Ja	—	Fremdschlüssel → vat key
customer_group_id	bigint	Ja	—	Fremdschlüssel → customer group
sales_pricelist_id	bigint	Ja	—	Fremdschlüssel → sales pricelist
sales_department_id	bigint	Ja	—	Fremdschlüssel → sales department
purchase_pricelist_id	bigint	Ja	—	Fremdschlüssel → purchase pricelist
pur_department_id	bigint	Ja	—	Fremdschlüssel → pur department
abc_category	varchar(100)	Ja	—	Domain-Feld abcCategory
credit_limit	decimal(20, 2)	Ja	0.00	Domain-Feld creditLimit
offer_layout	varchar(100)	Ja	—	offer layout
order_layout	varchar(100)	Ja	—	Domain-Feld orderLayout
delivery_layout	varchar(100)	Ja	—	Domain-Feld deliveryLayout
invoice_layout	varchar(100)	Ja	—	Domain-Feld invoiceLayout
distance	decimal(20, 2)	Ja	0.00	Domain-Feld distance
sales_condition_customer_id	bigint	Ja	—	Fremdschlüssel → sales condition customer
sales_person_id	bigint	Ja	—	Fremdschlüssel → sales person
key_account_manager_id	bigint	Ja	—	Fremdschlüssel → key account manager
branch_id	bigint	Ja	—	Fremdschlüssel → branch
offer_layout_id	bigint	Ja	—	Fremdschlüssel → offer layout
order_layout_id	bigint	Ja	—	Fremdschlüssel → order layout
delivery_layout_id	bigint	Ja	—	Fremdschlüssel → delivery layout
invoice_layout_id	bigint	Ja	—	Fremdschlüssel → invoice layout
paymcond_id	bigint	Ja	—	Fremdschlüssel → paymcond
payment_type_id	bigint	Ja	—	Fremdschlüssel → payment type
lower_delivery_tolerance	decimal(20, 2)	Ja	—	Domain-Feld lowerDeliveryTolerance
upper_delivery_tolerance	decimal(20, 2)	Ja	—	Domain-Feld upperDeliveryTolerance
is_delivery_tolerance_ignored	smallint	Ja	0	Boolean: Delivery Tolerance Ignored
partly_delivery	smallint	Ja	1	Teillieferung
blocked_delivery	smallint	Ja	0	Liefersperre
collective_invoice_id	bigint	Ja	—	Fremdschlüssel → collective invoice
invoice_print_quantity	int	Ja	1	invoice print quantity
invoice_copy_quantity	int	Ja	1	invoice copy quantity
remind_confirmation_days	int	Ja	1	remind confirmation days
remind_delivery_days	int	Ja	1	remind delivery days
remind_due_days	int	Ja	1	remind due days
ustdid_Last_Check_Date	date	Ja	—	Domain-Feld ustdidLastCheckDate
ustdid_last_check_result	varchar(100)	Ja	'000'	ustdid last check result
our_customer_number	varchar(25)	Ja	—	Domain-Feld ourCustomerNumber
gln	varchar(13)	Ja	—	Domain-Feld gln
gs1	varchar(9)	Ja	—	Domain-Feld gs1
print_old_ud	smallint	Ja	1	print old ud
account_id	bigint	Ja	—	Fremdschlüssel → account
delivery_tolerance	decimal(20, 2)	Ja	—	delivery tolerance
print_delivery_date_incw	smallint	Ja	0	print delivery date incw
is_neutral_delivery_note	smallint	Ja	0	Boolean: Neutral Delivery Note
acquisition_cost	decimal(12, 4)	Ja	—	Domain-Feld acquisitionCost
is_cash_register	smallint	Ja	0	TODO remove later
dunning_count	int	Ja	0	dunning count
special_terms	longtext	Ja	—	special terms
sales_doc_type_id	bigint	Ja	—	Fremdschlüssel → sales doc type
last_dunning_date	date	Ja	—	last dunning date
discount_type_id	bigint	Ja	—	for sales_head head discounts
discount_type2_id	bigint	Ja	—	Fremdschlüssel → discount type2
discount_type3_id	bigint	Ja	—	Fremdschlüssel → discount type3
item_discount_type_id	bigint	Ja	—	for sales item discount
central_customer_id	bigint	Ja	—	Fremdschlüssel → central customer
employee_amount_id	bigint	Ja	—	Fremdschlüssel → employee amount
key_numbers	varchar(100)	Ja	—	Domain-Feld keyNumbers
date_created	datetime	Ja	—	Anlagezeitpunkt
created_by_id	bigint	Ja	—	Angelegt von (Benutzer-ID)
last_updated	datetime	Ja	CURRENT_TIMESTAMP	Letzte Änderung
updated_by_id	bigint	Ja	—	Geändert von (Benutzer-ID)
unique_name	varchar(20)	Ja	—	unique name
is_customer_specific	smallint	Ja	0	Boolean: Customer Specific
is_packlist	smallint	Ja	—	Domain-Feld isPacklist
support_line_id	bigint	Ja	—	Fremdschlüssel → support line
cover_address	smallint	Ja	—	cover address
first_reminder_days	smallint	Ja	7	for sales orders
second_reminder_days	smallint	Ja	14	for sales orders
external_vendor_number	varchar(25)	Ja	—	external vendor number
is_gross_price	smallint	Nein	0	Boolean: Gross Price
print_comments_on_invoice	smallint	Ja	0	print comments on invoice
is_einvoice_taxpayer	smallint	Nein	0	Boolean: Einvoice Taxpayer
is_rolled_up_invoice_used	smallint	Ja	0	Boolean: Rolled Up Invoice Used
min_order	decimal(12, 2)	Ja	—	Domain-Feld minOrder
order_transmission_type	smallint	Ja	0	order transmission type
barcode	varchar(50)	Ja	—	Domain-Feld barcode
credit_process_type	smallint	Ja	0	credit process type
commission_warehouse_id	bigint	Ja	—	Fremdschlüssel → commission warehouse
is_concern	smallint	Ja	0	Boolean: Concern
is_demo_data	smallint	Ja	0	Boolean: Demo Data
is_send_payment_advice	smallint	Ja	0	Boolean: Send Payment Advice
lat	decimal(20, 8)	Ja	—	Domain-Feld lat
lng	decimal(20, 8)	Ja	—	Domain-Feld lng
pin_code	varchar(16)	Ja	—	Domain-Feld pinCode
waste_rate	decimal(12, 2)	Ja	—	waste rate
gl_prepayment_account_id	bigint	Ja	—	Fremdschlüssel → gl prepayment account
pur_account_id	bigint	Ja	—	Fremdschlüssel → pur account
pur_cost_center_id	bigint	Ja	—	Fremdschlüssel → pur cost center
skip_central_customer_address	smallint	Ja	0	skip central customer address
attributes	longtext	Ja	—	Domain-Feld attributes
bonus_agreement_head_id	bigint	Ja	—	Fremdschlüssel → bonus agreement head
is_free_redelivery	smallint	Ja	0	Boolean: Free Redelivery
cost_center_id	bigint	Ja	—	Fremdschlüssel → cost center
external_cost_center	varchar(300)	Ja	—	Domain-Feld externalCostCenter
is_cost_center_branch	smallint	Ja	0	Boolean: Cost Center Branch
packaging_id	bigint	Ja	—	Fremdschlüssel → packaging
ignore_pre_invoice	smallint	Ja	0	ignore pre invoice
advertising_medium	mediumtext	Ja	—	advertising medium
is_advertising_medium_from_vendor	smallint	Ja	0	Boolean: Advertising Medium From Vendor
is_final_invoice_by_sales_price_list	smallint	Ja	0	Boolean: Final Invoice By Sales Price List
is_main_customer_invoice_creation_ignored	smallint	Ja	0	Boolean: Main Customer Invoice Creation Ignored
customer_competitor_id	bigint	Ja	—	Fremdschlüssel → customer competitor
charge_discount_skonto_to_customer	smallint	Ja	0	Domain-Feld chargeDiscountSkontoToCustomer
charge_discount_ae_to_customer	smallint	Ja	0	Domain-Feld chargeDiscountAeToCustomer
lead_source_id	bigint	Ja	—	Fremdschlüssel → lead source
street2	varchar(255)	Ja	—	Domain-Feld street2
is_calculate_disposal_fee	smallint	Ja	0	Boolean: Calculate Disposal Fee
is_generating_gs1_label	smallint	Ja	0	Boolean: Generating Gs1 Label
e_invoice_type	smallint	Ja	0	e invoice type
e_invoice_email	varchar(250)	Ja	—	Domain-Feld eInvoiceEmail
e_invoice_id	varchar(50)	Ja	—	Domain-Feld eInvoiceId
is_cost_account	smallint	Ja	1	Boolean: Cost Account
fetch_customer_key	varchar(500)	Ja	—	Domain-Feld fetchCustomerKey
custom_fields	longtext	Ja	—	custom fields

Fremdschlüssel

Spalte(n)	Referenz
country_id	lt_country (id)
central_customer_id	customer (id)
currency_id	ltt_currency (id)
discount_type_id	ltt_discount_type (id)
vat_key_id	ltt_vat_key (id)
pur_account_id	account (id)
pur_cost_center_id	cost_center (id)
bonus_agreement_head_id	ltt_bonus_agreement_head (id)
lead_source_id	customer (id)
tenant_id	company (tenant_id)
language_id	ltt_language (id)
shipping_id	ltt_shipping (id)
freight_id	ltt_freight (id)
purchase_pricelist_id	ltt_price_list (id)
sales_condition_customer_id	customer (id)
contact_type_id	lt_contact_type (id)
branch_id	ltt_branch (id)
collective_invoice_id	ltt_collective_invoice (id)
payment_type_id	ltt_payment_type (id)
customer_type_id	ltt_customer_type (id)
account_id	account (id)
paymcond_id	ltt_paymcond (id)
item_discount_type_id	ltt_discount_type (id)
profit_center_id	ltt_profit_center (id)
discount_type2_id	ltt_discount_type (id)
discount_type3_id	ltt_discount_type (id)
gl_prepayment_account_id	account (id)
cost_center_id	cost_center (id)
packaging_id	ltt_packaging (id)
main_contact_id	customer (id)
lead_status_id	ltt_lead_status (id)
rating_id	lt_rating_type (id)