Fact table of work orders

The fact table of work orders belongs to the production datamart. It contains elements coming from work order headers, released products, components and operations.

Prerequisites

The MFGHEAD, MFGITM, MFGMAT and MFGOPE tables must be parameterized to be audited for the BI if data synchronization should be incremental (update of the elements modified since the last synchronization only ).

One of the objectives of the production-dedicated universe is to facilitate data handling by inexperienced users, it is in no way to duplicate data:

Some information deemed secondary is not transmitted.
Conversely, other information is calculated.
Finally, information is restructured if needed.

Data detail

Field code	Normal title	Menu	Table	Object type
MFGFCY	Production site	0	0	Dimension
MFGSTA	Order status	317	0	Dimension
MFGNUM	Work Order	0	0	Dimension
LINTYP	Line type	2217	0	Dimension
STRDAT	Start date	0	0	Dimension
ENDDAT	End date	0	0	Dimension
ROUNUM	Released routing	0	0	Dimension
ROUALT	Route alternative	0	0	Dimension
MFGPIO	Priority	365	0	Information
ITMLINNBR	Product number	0	0	Information
MATLINNBR	Material number	0	0	Information
OPELINNBR	Operation number	0	0	Information
TRKFIRST	Date of first tracking	0	0	Dimension
TRKLAST	Date of last tracking	0	0	Dimension
CLODAT	Closure date	0	0	Dimension
OBJDAT	Objective date	0	0	Dimension
CPY	Company	0	0	Dimension
FIYNUM	Financial year	0	0	Technical
PERNUM	Period	0	0	Dimension
PJT	Project	0	0	Dimension
MFGLIN	Line	0	0	Information
ITMTYP	Product type	2301	0	Dimension
ITMREF	Released product	0	0	Dimension
MFGDES	Description	0	0	Information
UOM	Unit	0	0	Dimension
UOMSTUCOE	RU/STK factor	0	0	Information
TRKFLG	Tracking flag	339	0	Dimension
CPNITMREF	Component product	0	0	Dimension
EXTQTY	Planned quantity	0	0	Measurement
CPLQTY	Actual quantity	0	0	Measurement
REJQTY	Rejected quantity	0	0	Measurement
QUAQTY	Ctrl qty	0	0	Measurement
TRKQTY	WO planned qty/achieved qty	0	0	Measurement
CPNTYP	Component type	438	0	Information
OPENUM	Operation	0	0	Information
EXTWST	Planned work center (main & second)	0	0	Dimension
CPLWST	Achiev. work center (main & second)	0	0	Dimension
EXTSETTIM	Planned setup time	0	0	Measurement
CPLSETTIM	Achieved setup time	0	0	Measurement
EXTOPETIM	Planned op. time	0	0	Measurement
CPLOPETIM	Achieved op. time	0	0	Measurement
TRKOPETIM	WO planned time/achieved qty	0	0	Measurement

Technical additions

Synchronization rules

The fact table of work orders is populated on the basis of 5 synchronization rules:

MFGHEAD: for the work order headers
MFGITM: for released products
MFGMAT1 and MFGMAT2: for the components (depending on whether released single or multi-product WOs are concerned)
MFGOPE: pour the operations

Only the closed work orders are fed to the datamart.

In this fact table, all the elements (manufactured products, components and operations) are at the same level. The LINTYP field provides the element type stored in the record. Depending on the context, some fields are not entered.

Specific loading formulas

WO planned quantity/achieved quantity - [F:MFM]RETQTY*[F:MFI]CPLQTY/[F:MFI]EXTQTY: This formula is used to express the planned component quantity in order to produce the achieved quantity of finished products
WO planned quantity/achieved quantity - [F:MFO]EXTQTY*[F:MFG]CPLQTY/[F:MFG]EXTQTY: This formula is used to express the quantity planned for the operation to produce the achieved quantity of finished products
WO planned time/achieved time - [F:MFO]EXTOPETIM*[F:MFG]CPLQTY/[F:MFG]EXTQTY: This formula is used to express the time planned for the operation to produce the achieved quantity of finished products

Specific loading functions

TRTX3BI.GETCPY([F:MFG]MFGFCY): This function returns the company of the production site
TRTX3BI.GETEXE([F:MFG]MFGFCY,*): Based on the context, this function is used to return the fiscal year of the WO end date, the fiscal year of the last material issue date or the operation end date.
TRTX3BI.GETPER([F:MFG]MFGFCY,*): Based on the context, this function is used to return the WO end date period, the last material issue date period or the operation end date period.