Connection to the information system

Example of an entry in the IMDMC fieldPalstatCAQ can be directly linked to most ERP systems to share master data and link quality management data. For successful deployment of the interfacing it is necessary to purchase the required interface license (Parts, Contacts, Workers,...) and install the appropriate extension to the MSSQL database, which will be performed by our technicians.

The linking system works without the Trigger and Stored procedure principle. When in the Palstat database for each import there is created its own import table into which any external system writes using the classic MSSQL statement INSERT INTO .... Trigger is active above the table, which passes the data to the Stored procedure, which takes care of further processing of the data and its storage in the appropriate structures of the system. Each connection has always defined key values on the basis of which the data comparison is performed and according to this the system will perform in the database Palstat UPDATE or INSERT depending on whether the record already exists in the DB or not.

Below you can see the links to the basic modules that are connected most often. However, there are more ways of linking and in case of specific requirements, please contact us and we will prepare a possible solution.

Other possible connections:Global8D, Machines, Tools, Defects, Work orders

Deploying the interface always requires a professional approach on the part of the MSSQL server administrator and the supplier of the information system, who must set up the relevant processes on their side that will write to the tables below.

 

Interface Parts

- On the information system side, e.g. Artikl, Components
- Import table DIIMPORT

Import table structure
 

Column Name	Type	Name in PalstatCAQ	Mandatory	Note
Dicislo	nvarchar(100)	Part number	X
Dizmena	nvarchar(100)	Change index	X	for new part “-”
Dinazevcz	nvarchar(100)	Part name CZ
Dinazeven	nvarchar(100)	Part name EN
Dinazevde	nvarchar(100)	Part name DE
Dimaterial	nvarchar(100)	Material
Dipolotovar	nvarchar(100)	Semi-product
Divykres	nvarchar(100)	Drawing
Dikod	nvarchar(100)	Code
Dikategorie	nvarchar(100)	Group
Diklasifikace	nvarchar(100)	Classification
Divyroba	nvarchar(100)	Production
Dijednotka	nvarchar(100)	Unit
Distav	nvarchar(1)	State		empty or A = active, P = new request, X = spare part, V = in draft, N = archive, U = released change
Dipoznamka	nvarchar(1000)	Note
Dicena	float	Price
Disestava	nvarchar(1)	Set		T = Yes F = No
Didatum	datetime	Valid from
Diinfo1	nvarchar(100)	Info1
Diinfo2	nvarchar(100)	Info2
Diinfo3	nvarchar(100)	Info3
Diinfo4	nvarchar(100)	Info4
Diimds	nvarchar(100)	IMDS
Diowner	nvarchar(16)	User		USLOGIN from table USRLIST
DiISID	int	Id of the item in the source system
Diparentcislo	nvarchar(100)	Original part		Part number
Diparentzmena	nvarchar(100)	Original part		Change index
Dikind	int	Kind		empty or 0 = standard, 1 = standard of type, 2 = type
Dikindparcislo	nvarchar(100)			If the Dikind is 1, the type part must be filled in - here the part number
Dikindparzmena	nvarchar(100)			If Dikind is 1, the type part must be filled in - here the change index
Diweight	float	Weight
Diweightunit	nvarchar(100)	Weight unit
Diinfodatum	datetime	Free date field
Diinfocisel1	float	Free numeric field 1
Diinfocisel2	float	Free numeric field 2

 

Interface Contacts

- On the information system side, e.g. Suppliers, Contacts, Partners
- Import table CNTIMPORT

Import table structure

Column Name	Type	Name in PalstatCAQ	Mandatory	Note
COCODE	nvarchar(10)	Company code	X
CONAME	nvarchar(100)	Company name	X
COICO	nvarchar(100)	ID Nr.
CODIC	nvarchar(100)	VAT ID
COLEGALFORM	nvarchar(30)	Legal form
COISCUSTOMER	int	Customer		0/1
COISSUPPLIER	int	Supplier		0/1
COACTIVE	int	Active	X	0/1
COGUARANTEE	nvarchar(16)	Guarantee		USLOGIN from table USRLIST
CODESIGNATION	nvarchar(100)	Designation
COSTRINGFIELD1	nvarchar(100)	Free text field 1
COSTRINGFIELD2	nvarchar(100)	Free text field 2
COSTRINGFIELD3	nvarchar(100)	Free text field 3
COSTRINGFIELD4	nvarchar(100)	Free text field 4
COSTRINGFIELD5	nvarchar(100)	Free text field 5
COSTRINGFIELD6	nvarchar(100)	Free text field 6
COBOOLEANFIELD1	int	Free logical field 1		0/1
COBOOLEANFIELD2	int	Free logical field 2
COBOOLEANFIELD3	int	Free logical field 3
COBOOLEANFIELD4	int	Free logical field 4
COBOOLEANFIELD5	int	Free logical field 5
COBOOLEANFIELD6	int	Free logical field 6
CONOTE	ntext	Note
COWEADRESS	nvarchar(100)	WWW
COPARENTCOCODE	nvarchar(10)	Parent company code

 

Interface Contacts addresses

- The PalstatCAQ system allows you to store multiple types of contact addresses to one master record.Systém PalstatCAQ umožňuje ukládat více druhů kontaktních adres k jednomu hlavnímu záznamu.
- Import table CNTIMPORTADDRESS

Import table structure

Column Name	Type	Name in PalstatCAQ	Mandatory	Note
COCODE	nvarchar(10)	Company code	X
ADDESCRIPTION	nvarchar(100)	Description	X
ATTYPE	int	Type		NULL for the default address type otherwise ATTYPE from the CNTADDRESSTYPE table
ADSTREET1	nvarchar(100)	Street
ADSTREET2	nvarchar(100)	Street 2
ADCITY	nvarchar(100)	City
ADPOSTCODE	nvarchar(30)	Post code
ADSTATE	nvarchar(100)	Country		it is also possible to enter the CRKEY key from the CNTCOUNTRY table
ADPHONE1	nvarchar(100)	Phone
ADPHONE2	nvarchar(100)	Phone 2
ADFAX	nvarchar(100)	Fax
ADEMAIL	nvarchar(100)	Email
ADNOTE	ntext	Poznámka

 

Interface Contacts persons

- The PalstatCAQ system allows you to store a person for each contact.Systém PalstatCAQ umožňuje ukládat ke každému kontaktu osoby.
- Import table CNTIMPORTPERSON

Import table structure

Column Name	Type	Name in PalstatCAQ	Mandatory	Note
NAME	nvarchar(100)	Name	X
SURNAME	nvarchar(100)	Surname	X
TITLEPRE	nvarchar(30)	Title (before)/Degree
TITLEPPOST	nvarchar(30)	Title (after)/Degree
BOOL1	Int	Free logical field 1		1-yes,0 no
BOOL2	Int	Free logical field 2		1-yes,0 no
BOOL3	Int	Free logical field 3		1-yes,0 no
PHONE1	nvarchar(100)	Phone 1
PHONE2	nvarchar(100)	Phone 2
EMAIL	nvarchar(100)	Email
ACTIVE	Int	Active		1-ano,0 ne
COCODE	nvarchar(100)	Company code	X

 

Interface Employees

- On the information system side, e.g. Workers, Staff
- In PalstatCAQ dialer accessible in "System Administration" -> "CodeList" -> "Employees"
- import table TRAPERIMPORT

Import table structure

Column Name	Type	Name in PalstatCAQ	Mandatory	Note
PEID	nvarchar(30)	Worker iD	X
PESTATUS	int	State	X	0=inactive, 1=active
PEPALLOGIN	nvarchar(16)	Palstat user
PEKIND	int	Type		0=Employee, 1=External
PEFIRSTNAME	nvarchar(100)	First name
PESURNAME	nvarchar(100)	Surname	X
PEBIRTHNAME	nvarchar(100)	Maiden name
PESALUTATION	nvarchar(100)	Salutation/nickname
PEPREFIXTITLE	nvarchar(15)	Prefix
PESUFFIXTITLE	nvarchar(15)	Suffix
PESEX	nvarchar(1)	Sex		NULL, M=male, F=female
PESOLDIER	int	Soldier		1=yes, 0=no
PEBIRTHDATE	datetime	Birthday
PEPERSONALNO	nvarchar(15)	Personal No.
PEBIRTHPLACE	nvarchar(100)	Birth place
PENATION	nvarchar(30)	Nation
PENATIONALITY	nvarchar(30)	Nationality
PECHILDREN	int	Children		1=yes, 0=no
PECITY1	nvarchar(100)	Place
PESTREET1	nvarchar(100)	Street
PEHOUSENUMBER1	nvarchar(15)	House number
PEPOSTCODE1	nvarchar(15)	Post Code
PECITY2	nvarchar(100)	Place (transient)
PESTREET2	nvarchar(100)	Street (transient)
PEHOUSENUMBER2	nvarchar(15)	House number (transient)
PEPOSTCODE2	nvarchar(15)	Post Code (transient)
PEPHONEHOME	nvarchar(15)	Phone home
PEPHONEWORK	nvarchar(15)	Phone work
PECELLULAR1	nvarchar(15)	Cellular 1
PECELLULAR2	nvarchar(15)	Cellular 2
PECELLULAR3	nvarchar(15)	Cellular 3
PEEMAILHOME	nvarchar(60)	E-mail home
PEEMAILWORK	nvarchar(60)	E-mail work
PEHEALTH	nvarchar(100)	Health
PEINFO1	nvarchar(100)	Info 1
PEINFO2	nvarchar(100)	Info 2
PEINFO3	nvarchar(100)	Info 3
PEINFO4	nvarchar(100)	Info 4
PEINFO5	nvarchar(100)	Info 5
PEACCESSIONDATE	datetime	Accession date		to tompany
PETAKEOFFDATE	datetime	Take off date		from company
PEWORKABILITY	nvarchar(100)	Employment
PEGRADE	nvarchar(100)	Grade
PETARIFF	nvarchar(100)	Tariff
PEDEPARTMENTCODE	nvarchar(15)	Department code
PEDEPARTMENTNAME	nvarchar(100)	Department name
ENGAGEID	nvarchar(30)	Work place ID
ENGAGENAME	nvarchar(100)	Work place name
ENGAGEACCESS	datetime	Work place starting date
XXCREUSER	nvarchar(16)	Created by, UserName

 

By calling the stored procedure pal_importperson

- the names of the procedure parameters correspond to the names of the import table fields (see paragraph above) and contain the @ sign.
- The stored procedure also contains control parameters that can be used to influence the import behavior:

Procedure parameters	Type	Description
@CLEARPERSONENGAGS	int	Cancels all employee bindings
@ACTIVATEPERSONENGAGE	int	Sets the imported workspace as the default if it exists
@UPDATEENGAGEMENT	int	Updates the job name and date of entry
@UPDATEDEPARTMENT	int	Updates the name of the department
@APPLYDEPARTMENTTOENGAGE	int	Applies the department to the workplace as well
@USEENGAGEHISTORY	int	Records the change of an employee relative to the job in the history (start date, primary job)

 

Interface Input, Output, Intermediate Controls and Product Audit

- Import table IINIMPORT

Import table structure

• IN - Incoming inspection
• OUT - Outgoing inspection
• INP - Inprocess
• PA - Product audit

Column name	Type	Mandatory	Description	IN	OUT	INP	PA
IMINSPECTNUM	nvarchar(16)	X	Inspection number	X	X	X	X
IMINSPECTINDEX	nvarchar(5)	X	Inspection Index, default 1	X	X	X	X
IMINDEX	int	X	Target module: 1 – Incomming inspection 2 – Outgoing inspection 3 – Inprocess inspection 4 – Product audit	X	X	X	X
IMPANUMBER	nvarchar(50)	X	Part number	X	X	X	X
IMPACHANGE	nvarchar(50)		Part number Index	X	X	X	X
IMTPNUMBER	nvarchar(50)		Number of control plan	X	X	X	X
IMTPCHANGE	nvarchar(50)		Index of control plan	X	X	X	X
IMOPNUMBER	nvarchar(50)		Operation number			X
IMCOCODE	nvarchar(10)		Supplier/customer code	X	X
IMDATE	datetime	X	Date and time of the check entry	X	X	X	X
IMORDERDATE	datetime		Term delivery date	X
IMSUPPLYDATE	datetime		Date of delivery	X
IMCOUNT	float	X	Quantity in delivery	X	X	X	X
IMORDERCOUNT	float		Ordered quantity	X
IMUNIT	nvarchar(50)		Unit name	X	X	X	X
IMORDER	nvarchar(100)		Order	X	X	X	X
IMBATCH	nvarchar(100)		Batch	X	X	X	X
IMCOMMISION	nvarchar(100)		Commision	X	X	X	X
IMDELNOTE	nvarchar(100)		Delivery note	X	X	X	X
IMINFO1	nvarchar(100)		Info 1	X	X	X	X
IMINFO2	nvarchar(100)		Info 2	X	X	X	X
IMINFO3	nvarchar(100)		Info 3	X	X	X	X
IMINFO4	nvarchar(100)		Info 4	X	X	X	X
IMDATE1	datetime		Free date field 1	X	X	X	X
IMLOGIN	nvarchar(16)		Took over (user login)	X	X	X	X
IMSTATE	int		Internal field, not to be filled in
IMISID	int		Binding to transaction/movement in IS	X	X	X	X
IMDESCRIPTION	ntext		Note	X	X	X	X
IMMULTIPLICITY	int		Multiplicity			X
IMOPCODE	nvarchar(9)		Operation code			X
IMDMC	ntext		DMC code (form JSON – viz. *1))			X

*1) Example of an entry in the IMDMC field:
[{"DMC":"1001","Cavity":"1"},{"DMC":"1002","Cavity":"1"},{"DMC":"1003","Cavity":"1"}]

 

Data Export

Export method: the status of controls is available in the IINEXPORT view for information system purposes is implemented using SQL VIEW.
If you prefer to create your own outputs and a general approach, it is advisable to use the CAQapi/swagger component for data output

Column name	Type	Description
EXINSPCTNUM	nvarchar(16)	Inspection number
EXINSPECTINDEX	nvarchar(5)	Inspection Index, default 1
EXINDEX	int	Target module: 1 – Incomming inspection 2 – Outgoing inspection 3 – Inprocess inspection 4 – Product audit
EXPANUMBER	nvarchar(50)	Part number
EXPACHANGE	nvarchar(50)	Part number Index
EXTPNUMBER	nvarchar(50)	Number of control plan
EXTPCHANGE	nvarchar(50)	Index of control plan
EXOPNUMBER	nvarchar(50)	Operation number
EXCOCODE	nvarchar(10)	Supplier/customer code
EXORDER	nvarchar(100)	Order
EXBATCH	nvarchar(100)	Batch
EXCOMMISION	nvarchar(100)	Commision
EXDELNOTE	nvarchar(100)	Delivery note
EXINFO1	nvarchar(100)	Info 1
EXINFO2	nvarchar(100)	Info 2
EXINFO3	nvarchar(100)	Info 3
EXINFO4	nvarchar(100)	Info 4
EXDATE1	datetime	Free date field 1
EXISID	int	Binding to transaction/movement in IS
EXRATEQUALITY	float	Quality assessment
EXRATEQUANTITY	float	Assessment of quantity
EXRATETERM	float	Assessment of timeliness
EXACCEPTCOUNT	float	Quantity received
EXREJECTCOUNT	float	Quantity returned
EXDESCRIPTION	ntext	Note (control)