SAP-FOX -> NIS Integration Requirements

This document describes the requirements for integrating SAP-FOX to NeXT Improve System. In particular it tries to answer to the following questions:

1. What are SAP-FOX data required by NIS ?
2. When are SAP-FOX data required by NIS ?
3. How could NIS acquire data from SAP-FOX ?
4. What is already implemented into Berbentina plant ?

NIS's target is to calculate OEE KPI for each production line or each machine that should be monitored by it. OEE calculation requires the following data gathered from the field. Those information are referred to a specific available time and to a specific production line / machine:

1. number of items produced and related takt-time
2. number of scrap items
3. stop machine time

Number of items produced, number of scrap, stop machine should be acquired by NIS directly from the field through NIS's devices. In this way NIS calculates and stores OEE at specific time horizon (each hour, each shift, each day).

Furthermore NIS shows some real-time production information for each production line; following information shown by NIS at real-time :

• WO identifier
• product identifier
• product description
• number of items to be produced (to fulfill WO)

Production information are related to WO; moreover takt-time is related to WO applied to a specific production line.

Production information and takt time should be available at any time.

Based on scenario described at previous paragraph, NIS requires working-order's information below:

• WO identifier: unique identifier of the working-order
• plant identifier: unique identifier of Franke's plant related to WO
• 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 for each production line / machine where WO could be produced:

• production line: code of the production line (assembly line / machine) interested by WO
• cycle time: the period in seconds, required to complete one cycle of production (production of one item); 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)

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

• time status (insert time): time in which the information is generated
• information status (insert type): 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 integration to SAP-FOX should be applied using some shared tables; only two shared tables are sufficient for implementing integration. Following logical schema genereted from previous conceptual schema



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

The primary key is defined on the following fields:

• <wo>

It implies that a specific WO is related to a specific row of the table workorder; as field wo is primary key only last WO's version should be present in the workorder table. Furthermore when a WO is updated/deleted by ERP User, it's necessary to update instm and instype according to their semantic (described in the table of fields).

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,
    numworkers INTEGER
);
ALTER TABLE workdata
    ADD CONSTRAINT ix_workdata_key PRIMARY KEY (wo, prodline);
ALTER TABLE workdata
    ADD CONSTRAINT ix_workdata_forkey FOREIGN KEY (wo) REFERENCES workorder(wo);

The shared tables workorder and workdata should be placed into SAP-FOX database; NIS should query workorder/workdata through RFC/web-service call to SAP-FOX. While NIS queries shared tables then SAP-FOX returns all rows required and removes them from shared tables (or marks them as NIS received).

The integration between NIS and SAP-FOX could be implemented using RFC (or web-services) defined into SAP-FOX.

NIS should be able to query SAP-FOX using RFC/web-service for acquiring required information and then it should insert them into NIS DB. RFC/Webservice should be implemented into SAP-FOX by SAP-FOX developers (SAP-FOX integration procedures). Following a general flow-chart



Working-order data are the source of information; first they are available into SAP-FOX database and are accessible by SAP-FOX System, but not by NIS. So SAP-FOX inserts information required by NIS into working-order shared tables; shared tables are provided from SAP-FOX DB.

Periodically (for example once at hour) NIS queries shared tables using RFC/web-service. Sequence diagram below describes a possible time-line for interacting:



After SAP-FOX inserts data into shared tables, NIS detects and saves them into destination table (operative table). While NIS received data, SAP-FOX could delete/mark them into shared tables (workorder); in this way at next NIS's interrogation deleted/marked data should not returned.

It's sufficient one pair of tables (workorder/workdata) for all managed plants; at this point we could define two integration contexts that could be implemented as following:

1. NIS queries shared tables plant by plant using an RFC/webservice parameter that specifies plant identifier and RFC/webservice returns only specific plant's WOs
2. NIS queries shared tables once for all plants (without specifies plant identifier) and RFC/webservice returns WOs for every plants

Working-order data may be generated, modified, deleted by ERP-User; so from this point of view NIS ↔ SAP-FOX integration should manage following scenarios:

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

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



If an ERP-User updates an existing working-order into SAP-FOX, then SAP-FOX should delete→insert data required into workorder table and into workdata table; in this case the field instm should be set to the time of inserting record into shared table, while instype should be set to U ([U]pdate).



If an ERP-User deletes an existing working-order into SAP-FOX, then SAP-FOX should update data required into workorder table (nothing is required into workdata table); in this case the field instm should be set to the time of updating record into shared table, while instype should be set to D ([D]elete).

We show an example in which ERP-User, from plant identified by FFI-code, 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 SAP-FOX the following working-order

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

SAP-FOX integration procedure should insert data into related fields of the tables workorder / workdata; following relations between data in table above and fields in workorder / workdata tables

Following query in SQL language that SAP-FOX should execute on shared table

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 SAP-FOX updates working-order information as following:

SAP-FOX integration procedure should insert into shared tables the change occurred. To do this SAP-FOX inserts into table workorder following data:

The query in SQL language that SAP-FOX should execute on shared table is defined below (we assumed SAP-FOX deletes and inserts again all data):

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 SAP-FOX system. SAP-FOX should comunicate this event to NIS, so SAP-FOX inserts into workorder table following data:

SAP-FOX executes query SQL below on shared table (nothing is required on workdata):

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);

If NIS queries workorder table using RFC/webservice call, after ERP-User updates number of items and before ERP-User deletes WO and (for hypotesis) nothing other is occurred, then it receives following data (SAP-FOX deletes/marks this row as NIS readed):

After who NIS queries workdata requiring WO number 44621852 and receives following data

But if NIS queries workorder table after deleting data then receives following data:

So NIS doesn't take into consideration WO 44621852.

Actual integration NIS ↔ SAP-FOX at Berbentina plant is based on RFC. NIS queries not only WO's information but also items produced. Items produced may be generated by Factory Kit; this software-tools is available only at Berbentina Plant, so every function depends on it doesn't keep in consideration.

We place our focus only on sharing working-order information. At every time NIS could queries SAP-FOX for getting working-order information, using RFC /FOX/PP_ANDON (already implemented into SAP-FOX at Berbentina plant).

This RFC receives following import parameters:

If I_HEADER_DATA is true then RFC answers with the following table:

If I_OPERATIONS_DATA is true then RFC answers with the following table:

Flow chart that describes NIS ↔ SAP-FOX integration at Berbentina plant is shown below