Shared production data

Database access: technical information

DB Server	10.100.145.20 (fkt20g.fa.franke.world)
SB Server Port	5432
DBMS	Postgresql
ODBC Driver	32bit, 64bit
Database name	andon_FWK
Username	(readonly)
Password

E-R diagram

Data dictionary

Entities

Entity
Name	Description	Attributes
Name	Description	Type	Identifier
prodline	Production resources list	codice	Production resource unique identifier	varchar	Yes
descrizio	Production resource name	varchar
codrep	Department unique identifier	varchar
descrep	Department description	varchar
codstab	Plant identifier	varchar
descstab	Plant description	varchar
unitprod	Unique unit of production, produced items	codice	Produced item unique identifier	varchar	YES
codart	Product code	varchar
dart	Product description	varchar
procord	Work order (from ERP)	varchar
qtaprev	Work order total quantity to be produced	double
unita	Produced items for each work cycle (generally 1)	double
tmcr	Time Epoch of the tracked production unit (seconds since 01/01/1970)	long
datacr	Date of the tracked production unit (YYYYMMGG)	varchar
oracr	Time of the tracked production unit (HH:MM:SS)	varchar
causanc	Non compliant causes list	codice	Cause unique identifier	varchar	YES
descrizio	Cause description	varchar
causafm	Downtime causes list	codice	Downtime unique identifier	varchar	YES
descrizio	Downtime description of the monitored resource	varchar
tipofm	Downtime type: F = unplanned downtime, M = microstop, P = planned downtime	varchar
setup	Downtime for setup reason (flag): 1 = setup kind, 0 = general downtime	integer

Relationships

Relationship
Name	Description	Components	Attributes
Name	Description	Type
regprod	Each row refers to a production event	prodline, unitprod	data	Event date (YYYYMMGG)	varchar
ora	Event time (HH:MM:SS)	varchar
tm	Event Time Epoch	long
qta	Number of produced items	double
regnc	Each row refers to a non compliant production event	prodline, unitprod, causanc	data	Event date (YYYYMMGG)	varchar
ora	Event time (HH:MM:SS)	varchar
tm	Event Time Epoch	long
qta	Number of non compliant produced items	double
regfm	Each row refers to a begin or an end of a downtime	prodline, causafm	data	Event date (YYYYMMGG)	varchar
ora	Event time (HH:MM:SS)	varchar
tm	Event Time Epoch	long
tmdiff	Time difference from end and begin of the downtime events	long
tipoevento	Downtime event type: INFM=downtime start, FIFM=donwtime end	long

Logical scheme

Table	Foreign keys
prodline(codice, descrizio, codrep, descrep, codstab, descstab)
unitprod(codice, codart, dart, procord, qtaprev, unita, tmcr, datacr, oracr)
causanc(codice, descrizio)
causafm(codice, descrizio, tipofm, setup)
regprod(codice, data, ora, tm, qta, codlot, codubi)	- codlot foreign key on unitprod.codice - codubi foreign key on prodline.codice
regnc(codice, data, ora, tm, qta, codlot, codubi, caudef)	- codlot foreign key on unitprod.codice - codubi foreign key on prodline.codice - caudef foreign key on causanc.codice
regfm(codice, data, ora, tm, tmdiff, tipoevento, codubi, caufm)	- codubi foreign key on prodline.codice - caufm foreign key on causafm.codice

Quering samples

Produced items on June 18th 2018 with date, time, unique identifier, production resource description, department description, article code (model) and work order, sorted by event time epoch

SELECT DATA, ora, codlot, prodline.descrizio AS linea, prodline.descrep, unitprod.dart, unitprod.procord FROM regprod INNER JOIN prodline ON prodline.codice=regprod.codubi INNER JOIN unitprod ON unitprod.codice=regprod.codlot WHERE DATA='20180618' ORDER BY tm

Count produced items after June 18th 2018 (included) grouped by day, article code and work order on production resource 821102A (DMG LB)

SELECT DATA, unitprod.dart, unitprod.procord, SUM(qta) AS pezzi FROM regprod INNER JOIN unitprod ON unitprod.codice=regprod.codlot WHERE regprod.codubi='821102A' AND regprod.data>='20180618' GROUP BY DATA, dart, procord

Get the DMG LB (821102A) downtime events list from 06:00 to 14:00 of June 18th 2018 with date, time, duration in minutes, event type, downtime cause description, downtime kind, changeover time flag

SELECT DATA, ora, tmdiff/60 AS dtm, tipoevento, causafm.descrizio AS dfermo, causafm.tipofm, causafm.setup FROM regfm INNER JOIN causafm ON causafm.codice=regfm.caufm WHERE codubi='821102A' AND DATA='20180618' AND ora>='06:00' AND ora<='14:00' ORDER BY tm