BoM Import*

From PlcWiki

(Difference between revisions)
Jump to: navigation, search
(Created page with '== Cleaning symptoms == <sql> update bom set flag = NULL where flag in (21, 22, 23, 24, 25, 26, 27, 28, 29) </sql> == Import to BoM2 == The BOM table is twofold: application…')
 
Line 65: Line 65:
* Time to determine if it is an update <sql>select TOP 1 ROW_STEP from DIFF where BOM_ID like 'bomId:%' and CONTEXT_ID = '-123' </sql>
* Time to determine if it is an update <sql>select TOP 1 ROW_STEP from DIFF where BOM_ID like 'bomId:%' and CONTEXT_ID = '-123' </sql>
* The identifier (key) of the entry from the bom2 table
* The identifier (key) of the entry from the bom2 table
-
* Listing the insert result has one of three values as a result: 0, 1 nebo 2 (DIFF.RESULT = 'N')
+
* Listing the insert result has one of three values as a result: 0, 1 or 2 (DIFF.RESULT = 'N')
== Unlock client application OMS ==
== Unlock client application OMS ==

Current revision as of 11:26, 17 April 2020

Contents

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

File:OMS-BoMImp.jpg

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]
Personal tools