Welcome, Guest
Username: Password: Remember me

TOPIC: SQL Script to Copy Serial Numbers to Esynergy

SQL Script to Copy Serial Numbers to Esynergy #489

  • cmcnally
  • cmcnally's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 12
  • Karma: 0
Has anyone worked on a script to copy serial numbers from ES to Esynergy? I'm think it might go along the lines of adding any new Macola ES serial or lot number to Esynergy if a matching Item number is found.
Any thoughts, perhaps an Event Manager solution, would be appreciated.
The administrator has disabled public write access.

SQL Script to Copy Serial Numbers to Esynergy #490

  • nbrown_ITI
  • nbrown_ITI's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 8
  • Karma: 0
Chris,
We have a client that is doing this. They are using event manager to add the records as soon as the records apear in the imlsmst_sql table.

The one they are using in event manager is a little complicated as it is specific for their needs. However, here is a script to perform an "initial load" for the ItemNumbers table from any current serial numbers in ES. This is a bare bones script really only covering the required fields.

Things that you will probably want to add to this script:
1) Check if the ser lot # already exists
2) Ensure the item number is in the e-Synergy Items table
3) Ensure that it is actually a serialized item (won't explode, but you won't be able to see the records)
4) Add any addition fields such as Description or any free fields, etc.

And of coarse always try this in a test environment prior to running it in production.

/* Create Serial Number Records in e-Synergy */
INSERT INTO ItemNumbers (ItemCode,CountryCode,[Number],
DateStart,Active,OwnerCompany,Quantity,PurchasePriceLocal,
PurchasePrice,PurchaseBasePriceLocal,PurchaseBasePrice,
Type,StatusValid,StatusPrice,StatusBudget,ShadowValid,
MiscellaneousAmount,MiscellaneousAmountFC,PassengerCar,
Resource,ResidualValue,TangibleAsset,CostLastMaintenance,
CostLastMaintenanceFC,syscreator,sysmodifier,Status,Classification)

/* Be sure to set Division Code Here
v */
SELECT item_no, 'US', ser_lot_no, ser_lot_dt, 1, '001'
,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,1,1,'A','B' FROM imlsmst_sql
Nathan Brown
Director, Implementation
Interactive Technology, Inc
nbrown@inttech.com
+1-704-296-2512
The administrator has disabled public write access.

SQL Script to Copy Serial Numbers to Esynergy #492

  • cmcnally
  • cmcnally's Avatar
  • OFFLINE
  • Fresh Boarder
  • Posts: 12
  • Karma: 0
Thank you so much! Will tweak and do a follow up once results are known.
Chris
The administrator has disabled public write access.
Time to create page: 0.033 seconds
Powered by Kunena Forum  Protected by R Antispam