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.2. Explanation on the database tables 

sql_connect SYSADM,SYSADM

ERPTABLE 

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 single fields::

  • erp_pdm_number: Foreign key (ERPTABLE)

  • all fields/columns added by you yourself become part of the ERPTABLE.

  • e.g. Description, Material, Surface.

LINKTABLE 

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 single fields::

  • erp_pdm_number_linktable: Foreign key (LINKTABLE)

  • prj_path: Project path

  • varset: All variables are concatenated to a string

  • varset_update:

    In order to save loading time in PARTdataManager, VARSET has been split into VARSET and VARSET_UPDATE with as of version 9.04.

    Now VARSET only contains the information which is needed by PARTdataManager. That means, value range values and if it's about a variant, variant values, which differ form the original value of 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, when changing over from VARSET to VARSET and VARSET_UPDATE during an update. (Not absolutely needed, because PARTdataManager notes, if VARSET_UPDATE is not filled, so for this dataset the old methods have to be used.)

  • line_id: Line identifier

  • subline_id: subline to increment variants

  • version: number of versions

  • variant: value 0 or 1 / variant yes or no

  • active_state:

  • requested_state:

LINKTABLE and ERPTABLE are connected by primary key and foreign key.

Additional functionality

  • ERPADDTABLE

    This table is only necessary for connection of ERP systems.

    When transferring values, a field with a specific variable value is added.

  • MAPPING

    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.

    Adjustment is customized.

  • NBTABLE

    If the originally formed file name is too long, a replacement is given.

    The table secures the unique allocation of the changed file names.

    Adjustment is customized.

  • REPLACE_TABLE

    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.

    Optionally, concerned parts may be identified with a symbol in the PARTdataManager directory tree.

    		Old standard cancelled without replacement

    		Old standard replaced by at least one other parts family

    		Old standard replaced by at least one other parts family and old standard locked

    Symbols - directory tree

    Symbols - directory tree

    Detailed information can be found under Section 1.7.3.3, “ replace_std.cfg ”.

Prev Up Next
4.2.4.1. Installation scripts  Home 4.2.5. Set up ODBC data source