ERP NOT-SAP Integration Specification

This document describes the requirements for integrating ERP-not SAP to NeXT Improve System.

The integration between NIS and ERP is implemented using some border tables defined into NIS DB.

ERP must be able to insert information required by NIS into NIS DB using standard SQL language. The access to NIS DB should be executed through ODBC Driver for Postgres. Following a general flow-chart



Working-order data are the source of information; first they are available into ERP database and are accessible by ERP System, but not by NIS. So ERP inserts information required by NIS into working-order border table; border table is provided into NIS DB.

After ERP inserts data into border table, NIS detects and saves them into destination table (operative table).

NeXT's technicians will supply connection parameters, that are following:

• IP Address, Port: Server DBMS IP address and port
• User name, password: User name and password for accessing DBMS
• Database Name: name of NIS DB

NIS requires the following working-order's information:

• WO identifier: unique identifier of the working-order
• product identifier and description: code and description of the product referred by the WO
• quantity to be produced: number of items to be produced

Each WO refers also to following production information:

• production lines: code of the production lines (assembly lines / machines) in which the WO should be produced
• cycle time: the period in seconds, required to complete one cycle of production (production of one item) for each production lines; it corresponds exactly to takt time in case of mechanical line
• number of workers: number of workers assigned to WO (significant only for assembly line for calculating takt time through cycle time)

Furthermore it's necessary to manage some flags that permit the identification of working-order's information status; following:

• time status: time in which the information is generated
• information status: status of the information, distinguishing as following:
  • inserted information: information to be inserted at first time
  • updated information: information to be updated
  • deleted information: information to be deleted

Following a conceptual schema:

NIS DB will provide border tables for ERP integration; two border table are required for implementing integration. Following logical schema of the border tables



The name of the first border table is "workorder", following fields list:

The primary key is defined on the following fields:

• <wo, instm, instype>

It implies that a specific WO (wo) at a specific time (instm) for a specific record type (instype), is related to a specific row of the table workorder.

The name of second shared table is "workdata", following fields list:

The primary key is defined on the following fields:

• <wo, prodline>

A foreign key is defined on field wo (it refers to field wo of table workorder). The relation between table workorder and table workdata is 1 to N respectively (as we can see into conceptual schema and also into logical schema). Therefore one working-order stored into workorder table corresponds to some rows into workdata table, one row for each production line in which working-order could be produced. This relation permits to deduce takt-time for each WO for each production line where WO could be produced.

Following an example of create table query for creating workorder/workdata table using standard SQL language

--working-order table and related constraint
CREATE TABLE workorder (
    wo VARCHAR(51) NOT NULL,
    instm INTEGER NOT NULL,
    instype VARCHAR(2) NOT NULL,
    plantid VARCHAR(51) NOT NULL,
    ruek VARCHAR(51),
    prodid VARCHAR(51) NOT NULL,
    description VARCHAR(201),
    numitems INTEGER NOT NULL,
    prodline VARCHAR(51) NOT NULL,
    cycletime FLOAT NOT NULL,
    numworkers INTEGER
);
ALTER TABLE workorder
    ADD CONSTRAINT ix_workorder_key PRIMARY KEY (wo, instm, instype);
--working-data table and related constraint
CREATE TABLE workdata (
    wo CHARACTER VARYING(51) NOT NULL,
    prodline CHARACTER VARYING(51) NOT NULL,
    cycletime DOUBLE PRECISION NOT NULL,
    setuptime DOUBLE PRECISION NOT NULL DEFAULT 0,
    numworkers INTEGER
);
ALTER TABLE workdata
    ADD CONSTRAINT ix_workdata_key PRIMARY KEY (wo, prodline);

There are three integration scenarios:

1. ERP-User inserts a new working-order into ERP
2. ERP-User updates an existing working-order into ERP
3. ERP-User deletes an existing working-order into ERP

If an ERP-User inserts a new working-order into ERP, then ERP should insert data required into workorder/workdata tables (NIS DB); in this case the field instm should be set to the time of inserting record into NIS DB, while instype should be set to I ([I]nsert).



If an ERP-User updates an existing working-order into ERP, then ERP should insert data required into workorder/workdata tables (NIS DB); before inserting data into workdata table the ERP should delete the workdata's rows related to the updated WO; in this case the field instm should be set to the time of inserting record into NIS DB, while instype should be set to U ([U]pdate).



If an ERP-User deletes an existing working-order into ERP, then ERP should insert data required into workorder table (NIS DB); in this case the field instm should be set to the time of inserting record into NIS DB, while instype should be set to D ([D]elete).

We show an example in which ERP-User executes following steps:

1. first creates a new working-order identified by 44621852
2. then updates working-order 44621852 for changing number of items
3. finally deletes working-order 44621852

Naturally updating and deleting should be optionally operations and ERP-User should execute them based on his needs.

We begin considering that ERP-User inserts into ERP the following working-order

This working-order could be produced on production line FAFL1 and FAFL2, with the following information

ERP integration procedure should provide to insert data into related fields of the table workorder / workdata; following relations between data in table above and fields in workorder / workdata tables

Following query in SQL language that the ERP should execute on NIS DB

INSERT INTO workorder (wo, instm, instype, plantid, prodid, description, numitems) 
VALUES ('44621852', 1480712220, 'I', 'FFI', '110.0017.976', 'COOKER HOOD XXXX', 20);
INSERT INTO workdata (wo, prodline, cycletime, numworkers) 
VALUES ('44621852', 'FAFL1', 900, 5);
INSERT INTO workdata (wo, prodline, cycletime, numworkers) 
VALUES ('44621852', 'FAFL2', 900, 4);

For reasons that we don't know, ERP-User updates the number of items from 20 to 30 in the same working-order; after that ERP updates working-order information as following:

ERP integration procedure should provide to insert into NIS DB the change occurred. To do this ERP inserts into table workorder of NIS DB following data:

The query in SQL language that the ERP should execute on NIS DB is defined below:

DELETE FROM workorder WHERE wo='44621852';
DELETE FROM workdata WHERE wo='44621852';
INSERT INTO workorder (wo, instm, instype, plantid, prodid, description, prodline, numitems, cycletime, numworkers) 
VALUES ('44621852', 1480718520, 'M', 'FFI', '110.0017.976', 'COOKER HOOD XXXX', 'FAFL1', 30, 900, 5);
INSERT INTO workdata (wo, prodline, cycletime, numworkers) 
VALUES ('44621852', 'FAFL1', 900, 5);
INSERT INTO workdata (wo, prodline, cycletime, numworkers) 
VALUES ('44621852', 'FAFL2', 900, 4);

Not enough satisfied ERP-User adjudicates to delete the working-order from ERP system. ERP should comunicate this event to NIS, so ERP inserts into workorder table of NIS DB following data:

ERP executes query SQL below on NIS DB:

DELETE FROM workorder WHERE wo='44621852';
DELETE FROM workdata WHERE wo='44621852';
INSERT INTO workorder (wo, instm, instype, plantid, prodid, description, prodline, numitems, cycletime, numworkers) 
VALUES ('44621852', 1480719360, 'D', 'FFI', '110.0017.976', 'COOKER HOOD XXXX', 'FAFL1', 30, 900, 5);

This paragraph describes some additional fields and related types, usage, aims useful for some shared tables generalizations.

Following queries to update database structure

--Time of releasing work-order
ALTER TABLE workorder ADD COLUMN tmrel INTEGER;
--Time of closing work-order
ALTER TABLE workorder ADD COLUMN tmclo INTEGER;

Table with the last import tm

CREATE TABLE wolasttm (
    lasttm INTEGER
);
COMMENT ON COLUMN wolasttm.lasttm IS 'Tm ultima importazione workorder';