BoM Import*

From PlcWiki

Contents 1 Cleaning symptoms 2 Import to BoM2 3 OMS client application lock 4 Create DIFF information 5 Redundancy analysis 6 Merge 6.1 Logging 7 Unlock client application OMS 8 Set visibility of BOM items and generate auxiliary data 9 Create notifications 10 Send notifications by e-mail

Cleaning symptoms

 UPDATE bom SET flag = NULL WHERE flag IN (21, 22, 23, 24, 25, 26, 27, 28, 29)

Import to BoM2

The BOM table is twofold: application OMS BoM and replication of the new BoM2 BOM from which data is transferred to the application BOM (BoM).(viz. web.xml).

OMS client application lock

Create a record in the table "app_lock"

Create DIFF information

 setKeyColumnNames(new String[]{"MachineCPN", "Item"});
 setDiffColumnNames(new String[]{"MachineCPN", "Item", "Revision", "UnitOfMeasure", "Quantity"});
 setDisplayColumnNames(new String[]{"id", "ParentItemOrder", "other_id", "MachineCPN", "Item", "Revision", "UnitOfMeasure", "Quantity"});
 setWhereClause("where tag is null and manual_record is null");

Redundancy analysis

 UPDATE diff SET diff.parentitemorder = bom2.parentitemorder, diff.[ORDER] = bom2.[ORDER] FROM bom2 
 WHERE context_id = '-123' AND LHS IS NULL AND RHS IS NOT NULL AND bom_id = bom2.id

 UPDATE diff SET diff.parentitemorder = bom.parentitemorder, diff.[ORDER] = bom.[ORDER] FROM bom 
 WHERE context_id = '-123' AND LHS IS NOT NULL AND RHS IS NULL AND bom_id = bom.id AND bom.tag IS NULL AND bom.manual_record IS NULL

 UPDATE diff SET diff.parentitem = bom2.item FROM bom2 
 WHERE context_id = '-123' AND LHS IS NULL AND RHS IS NOT NULL AND bom2.machinecpn = machine_cpn AND bom2.[ORDER] = diff.parentitemorder

 UPDATE diff SET diff.parentitem = bom.item FROM bom 
 WHERE context_id = '-123' AND LHS IS NOT NULL AND RHS IS NULL AND bom.machinecpn = machine_cpn AND bom.[ORDER] = diff.parentitemorder 
 AND bom.tag IS NULL AND bom.manual_record IS NULL

• analyzeDiffForReorder
• analyzeDiffForReorderAndUpdate
• updateDiffContextEndTime

Merge

Logging

Row_6000, IsINS:31, IsDEL:16, UPD:16, IsUPD:31, BoM2-ID:92946492, INS: 0

Legend (separated by comma):

• Number of processed line from table bom2
• Insert detection

  SELECT TOP 1 ROW_STEP, RESULT FROM DIFF WHERE BOM_ID = ' bom2Id ' AND CONTEXT_ID = '-123' AND RHS = '>' AND KIND = '0'

• Detection of deletes without time marking the record as deleted

  SELECT TOP 1 ROW_STEP, RESULT FROM DIFF WHERE BOM_ID = ' bomId  ' AND CONTEXT_ID = '-123' AND LHS = '<' AND KIND = '0'

• Update time
• Time to determine if it is an update

  SELECT TOP 1 ROW_STEP FROM DIFF WHERE BOM_ID LIKE 'bomId:%' AND CONTEXT_ID = '-123'

• The identifier (key) of the entry from the bom2 table
• Listing the insert result has one of three values as a result: 0, 1 or 2 (DIFF.RESULT = 'N')

Unlock client application OMS

Set visibility of BOM items and generate auxiliary data

Create notifications

SELECT bom_id, machine_cpn, item, [revision], unit, quantity, lhs, kind, parentitem  FROM diff 
WHERE CONTEXT_ID='-123' AND result IS NULL  
UNION 
SELECT bom_id, machine_cpn, item, [revision], unit, quantity, lhs, kind, parentitem FROM diff_report_update
WHERE CONTEXT_ID='" + id + "' AND result IS NULL 
ORDER BY machine_cpn, item, lhs, kind, parentitem

Send notifications by e-mail

SELECT * FROM message AS m WHERE m.id NOT IN (SELECT message_id FROM msg_delivery_status WHERE email_delivered = 1) ORDER BY [FROM]