4.2.4. Create database
scheme and users for ERP (exemplified by MSSQL) 4.2.4.2. Explanation on the database tables
|  |
| Prev | Next |
4.2.4. Create database
scheme and users for ERP (exemplified by MSSQL) 4.2.4.2. Explanation on the database tables
| |
| Prev | Next |
sql_connect SYSADM,SYSADM
sql_execute create table ERPTABLE (/ ERP_PDM_NUMBER varchar (50) not null,/ DESCRIPTION varchar (200),/ MAT_NAME varchar (100),/ primary key (ERP_PDM_NUMBER)) sql_execute create public synonym ERPTABLE for SYSADM.ERPTABLE sql_execute grant all on ERPTABLE to PUBLIC
Meaning of the individual fields:
sql_execute create table LINKTABLE (/ PRJ_PATH varchar (500) not null,/ LINE_ID integer not null,/ LINE_SUBID integer not null,/ VERSION varchar(5) not null,/ ACTIVE_STATE smallint default 0 not null,/ REQUESTED_STATE smallint default 0 not null,/ VARSET nvarchar(4000), VARSET_UPDATE nvarchar(4000), VARIANT integer default 0, ERP_PDM_NUMBER varchar(50), DATA_ID integer default 0 not null, foreign key(ERP_PDM_NUMBER) references ERPTABLE, primary key(PRJ_PATH,LINE_ID,LINE_SUBID,VERSION)); sql_execute create public synonym LINKTABLE for SYSADM.LINKTABLE sql_execute grant all on LINKTABLE to PUBLIC
Meaning of the individual fields:
To save loading time in the PARTdataManager version 9.04, the VARSET was split into a VARSET and a VARSET_UPDATE.
The VARSET only contains the information that the PARTdataManager requires. In other words, the values of value ranges and, if it is a variant, the variant values that differ from the original value of the line.
VARSET_UPDATE is only required for a catalog update. There, the original values are found, which are required to retrieve lines during the update. Value range values are not contained because they are not needed to retrieve lines.
Filling happens when a dataset is added to database (by erpcustom script).
The command padmin.exe –migrateVarset can be called up when switching from VARSET to VARSET and VARSET_UPDATE during an update. (Not absolutely necessary, as PARTdataManager registers, if VARSET_UPDATE is not filled, that the old methods are still to be used for this data set)
LINKTABLE and ERPTABLE are connected by primary key and foreign key.
This table is only necessary for connection of ERP systems.
When transferring values, a field with a specific variable value is added.
This table is only necessary for connection of ERP systems.
Here a description between the descriptions in PARTsolutions and those in the ERP system is created.
If the originally formed file name is too long, a replacement is given.
The table secures the unique allocation of the changed file names.
When opening certain standard or supplier parts, a window shows up in which references to other parts are listed, which can be loaded instead of the original selection. The table serves as allocation table.
Detailed information on this can be found under Section 1.7.3.2, “ replace_std.cfg ”.
