Warehouse Automation (II) - LEI As The Data Pump

IBM Lotus Enterprise Integration (LEI) 7.01 is used to act as the data pump, it retrieves scan data (CSV file) on File Server into MySQL database at application server. Even the whole process is nothing to do with Lotus Notes database, the LEI is still my best choice(if performance meets) since no programming(Java, LotusScript or PHP) needed and easy to schedule the tasks.

LEI connection document for Text ODBC Connection :

this is to connect the S/N text file uploaded by GoodSync

 

LEI connection document for MySQL ODBC Connection :

LEI activity document which is a "Direct Transfer" process :

this copy the S/N data into MySQL SNOUT2 table

 

If success, it will run an additional job - "UPDATE SNOUT WITH NEW SN FROM SNOUT2"

  

This is a schedule job, however, it also can be called by other process

 

Command Activity document - UPDATE SNOUT WITH NEW SN FROM SNOUT2

It execute the following SQL command which is to update the SN database with new SN on SNOUT2 table

INSERT INTO champ.SNOUT SELECT * FROM champ.SNOUT2 WHERE CONCAT(INVNO, SN) NOT IN (SELECT CONCAT(INVNO, SN) FROM champ.SNOUT) GROUP BY INVNO, PN, SN;