Show pageOld revisionsBacklinksExport to PDFODT exportBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== ERP NOT-SAP Integration Specification ====== This document describes the requirements for integrating ERP-not SAP to NeXT Improve System. ===== Definitions ====== ^Word^Definition^ |**ERP**|not-SAP-FOX ERP System used by the plant| |**NIS**|NeXT Improve System for OEE Monitoring| |**ERP-Users**|Plant's staff that uses **ERP**| |**ERP integration procedure**|Procedure implemented into ERP by ERP Developers for integrating NIS| |**WO** / **work-order**|Working-Order referred to one specific kind of product and related quantity to be produced| |**DBMS**|Database Management System| |**NIS DB**|**NIS** database that is a relational database using Postgres **DBMS**| |**ODBC Driver**|Driver ODBC for accessing Postgres DBMS| |**SQL**|Structured query language| |**ruek**|working-order identifier used in general by SAP-FOX; if **ERP** doesn't use this concept, it will be not considered| ===== Integration Criterions ===== 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 \\ \\ {{:cli:franke:integration-flow.png|}} \\ \\ 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 ===== Conceptual Schema ===== 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: \\ \\ {{:cli:franke:wo-conceptual-schema.png|}} ===== Logical 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 \\ \\ {{:cli:franke:logical-schema-sap_new.png|}} \\ \\ The name of the first border table is "workorder", following fields list: ^Field Name^Field Type^Field Description^Primary key^Nullable^ |wo|varchar(51)|Unique identifier of working-order| Yes | No | |instm|integer|Time epoch of inserting the record into database (time in seconds from 1st Gennuary 1970)| Yes | No | |instype|varchar(2)|Record type: "I" = new working-order; "U" = working-order updated; "D" = working-order deleted| Yes | No | |plantid|varchar(51)|Plant unique identifier| |ruek|varchar(51)|Ruek code; if ruek isn't used by the ERP, this field will be empty| No | Yes | |prodid|varchar(51)|Product identifier, referred by working-order| No | No | |description|varchar(201)|Product description| No | Yes | |numitems|integer|Number of items that will be produced, referred to working-order| No | No | 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: ^Field Name^Field Type^Field Description^Primary key^Nullable^ |wo|varchar(51)|Unique identifier of working-order| Yes | No | |prodline|varchar(51)|Production line identifier, in which the items could be produced| Yes | No | |cycletime|double|Cycle time in seconds; for mechanical line, this value corresponds to "takt time"; for assembly line this time divided by "number of workers" corresponds to the "takt time"| No | No | |setuptime|double|Setup time in seconds| No | No | |numworkers|integer|Number of workers, who is referred the cycle time (only for assembly line); if ERP doesn't know number of worker this field should be empty| No | Yes | 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 <code sql> --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); </code> ===== Integration Scenarios ===== There are three integration scenarios: - ERP-User inserts a new working-order into ERP - ERP-User updates an existing working-order into ERP - ERP-User deletes an existing working-order into ERP {{:cli:franke:erp-insert.png|}} \\ \\ 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). \\ \\ {{:cli:franke:erp-update.png|}} \\ \\ 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). \\ \\ {{:cli:franke:erp-delete.png|}} \\ \\ 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). \\ \\ ===== Integration Examples ===== We show an example in which ERP-User executes following steps: - first creates a new working-order identified by 44621852 - then updates working-order 44621852 for changing number of items - 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 ^WO^PRODUCT ID^QTY(UNIT)^ |44621852|110.0017.976| 20 | This working-order could be produced on production line FAFL1 and FAFL2, with the following information ^LINE^CYC TIME(sec)^NUM WORKERS^ |FAFL1| 900 | 5 | |FAFL2| 900 | 4 | 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 ^ workorder ^^^ ^Field Name^Data^Annotation^ |wo|44621852| | |instime|1480712220|Corresponds to 2016-12-02 21:57| |instype|I| | |plantid|FFI| | |ruek|null| | |prodid|110.0017.976| | |description|COOKER HOOD XXXX|ERP extracts product description from internal table| |numitems|20| | ^ workdata ^^^^ ^Num row^Field Name^Data^Annotation^ |1|wo|44621852| | |1|prodline|FAFL1|Improve should know this code| |1|cycletime|900| | |1|numworkers|5| | |2|wo|44621852| | |2|prodline|FAFL2|Improve should know this code| |2|cycletime|900| | |2|numworkers|4| | Following query in SQL language that the ERP should execute on NIS DB <code sql> 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); </code> 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: ^WO^PRODUCT ID^QTY(UNIT)^ |44621852|110.0017.976| //**30**// | 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: ^Field Name^Data^Annotation^ |wo|44621852| | |**//instime//**|**//1480718520//**|**//Corresponds to 2016-12-02 23:42//**| |**//instype//**|**//M//**| | |plantid|FFI| | |ruek|null| | |prodid|110.0017.976| | |description|COOKER HOOD XXXX|ERP extracts product description from internal table| |**//numitems//**|**//30//**| | The query in SQL language that the ERP should execute on NIS DB is defined below: <code sql> 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); </code> 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: ^Field Name^Data^Annotation^ |wo|44621852| | |**//instime//**|**//1480719360//**|**//Corresponds to 2016-12-02 23:56//**| |**//instype//**|**//D//**| | |plantid|FFI| | |ruek|null| | |prodid|110.0017.976| | |description|COOKER HOOD XXXX|ERP extracts product description from internal table| |**//numitems//**|**//30//**| | ERP executes query SQL below on NIS DB: <code sql> 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); </code> ===== Additional Fields and Tables ===== This paragraph describes some additional fields and related types, usage, aims useful for some shared tables generalizations. ^ Sharde Table: workorder ^^^^^ ^Field Name^Field Type^Field Size^Description^Optional (Y/N)^ |tmrel|integer| default |Time Epoch (time in seconds from 1 Jan 1970) referred to the time of releasing related work-order; it is used by NIS for sorting work-orders with the aim of providing the sequence of production| Y | |tmclo|integer| default |Time Epoch (time in seconds from 1 Jan 1970) referred to the time of closing related work-order; it is never used by NIS| Y | Following queries to update database structure <code sql> --Time of releasing work-order alter table workorder add column tmrel integer; --Time of closing work-order alter table workorder add column tmclo integer; </code> Table with the last import tm <code sql> CREATE TABLE wolasttm ( lasttm integer ); COMMENT ON COLUMN wolasttm.lasttm IS 'Tm ultima importazione workorder'; </code> cli/franke/erp_integration.txt Last modified: 2017/08/23 16:04(external edit)