PWB520 Using Powerbuilder to Integrate the Manufacturing Environment with Business Systems

1. PWB520 Using Powerbuilder to Integrate the Manufacturing Environment with Business Systems Gregg Weight / Alan CampbellSr. Programmer / MIS Supervisorgregg.weight@autolivasp.comAugust 5, 2003

2. WHAT DO WE MEAN BY INTEGRATION Financial Systems Engineering Systems Power Builder Inventory Systems Quality Systems Plant Floor

3. INTEGRATION INCENTIVES • Competitive Advantage • ISO 9000 Benefits • System vs Individual Responsibilities • Rapid Response to Change Possible • Lower System Maintenance Costs • High Degree of Flexibility • Simplified Support of a Diverse Manufacturing Environment

4. THE ENVIRONMENT Autoliv Automotive Safety Products Pyrotechnic Processing Facility • Promontory Utah • 640 acre site that manufactures explosives for use in automobile restraint systems (airbags). • 20+ isolated process buildings, 15+ support buildings. • Multiple processes in each building. • Numerous part numbers and tracking of those part numbers. • 5 different PLC brands with multiple models within each brand. • Multiple databases. • 500 users. • Over 200 Distinct Part Numbers Manufactured

5. REQUIREMENTS Autoliv Automotive Safety Products Pyrotechnic Processing Facility • Support Team • 2 Powerbuilder programs (Generic Application and Data Monitor). • 3 people for development and support. • Generic Application • Generic Application uses 1 datawindow with 106 action ‘verbs’. • Normalized repository • Majority of support is collection of requirements and data setup • Completely Data Driven

6. POWER BUILDER (What makes it possible?) • Leverage Power Builder's Data Window • Multiple Concurrent System Connections • Leverage "Syntax from SQL" • Integration of third party DLL's • OLE for Process Control (OPC) • Machines & Plant Floor Processes PLC Controlled • Coordination of Effort • Imagination • Standardization

7. HISTORY Paperworker. Position of just filling out the paper work and recording required quality and engineering data. 1st Generic Application with generic (flexible) repository. 2nd Gen App integrated with enterprise database using "table transfer." 3rd Gen App using OPC to push requirements to the factory floor, and monitor data. Data Monitor application can monitor data in either discrete or continuous mode. Discrete operations require the data to be captured in a ‘snapshot’. Continuous operations require constant monitoring of the data, but to save all the points would take up too much space on the database.

8. GENERIC APPLICATION Two different variations of the Generic Application.

9. GENERIC APPLICATION Tableting process vs. Canister process • Tableting • Tablets are made by pressing powder into aspirin-like tablets. • 52 different part numbers. • 1 application to rule them all. • 340 lines of code to ‘program’ business application. • Canister • Assembly line. • 13 different part numbers • 1 application to rule them all. • 252 lines of code to ‘program’ business application.

10. GENERIC APPLICATION Screen Shots

11. GENERIC APPLICATION Screen Shots

12. GENERIC APPLICATION Screen Shots

13. GENERIC APPLICATION Screen Shots

14. GENERIC APPLICATION Datawindow Control • DW_CONTROL Table • Event Code – Button Number • Action Code – Function call. • Action String – 1st argument. • DW Presentation – 2nd argument • DW Control Maintenance Screen • 5GL IDE. • By using the 106 action verbs, the application is controlled and maintained.

15. GENERIC APPLICATION DW Control Screen Shot

16. GENERIC APPLICATION Table Transfer • Table Transfer • Promote data from local database to remote repository database. • Synchronize data between databases. • DB2Oracle • SQLServerDB2 • Data Types: • DB2 timestamp – Oracle Date • DB2 integer – Oracle number • Etc… • Used instead of a pipe or customized transfer of data. • Two Non Visual Objects handle the data.

17. TABLE TRANSFER

18. DATA MONITORING APPLICATION OPC Application • Discrete Operations • Desired action is to record data for any given part in the process based upon a signal from that process. • Continuous Operations • Desired action is to monitor a parameter and record an accurate representation of its variation at a low cost (space & performance). • Data Compression • Low interaction with the end user • User starts the application and leaves.

19. DATA COMPRESSION

20. DATA MONITORING APPLICATION Main Window

21. DATA MONITORING APPLICATION OPC Application • Maintenance Screen • OPC Browser functionality. • Database front end • Tagnames are setup for monitoring.

22. DATA MONITORING APPLICATION Maintenance Window

23. OPC OVERVIEW • PLC(s) • PC with OPC Server • OPC Client(s)

24. OPC OVERVIEW Challenges • Documentation • Automation dll • Security (DCOMCNFG) • Server Configuration • Tagname Conventions • Powerbuilder data type of any • Error Recovery

25. COMPONENTS OF OPC • Documentation • The best place for documentation is at www.opcfoundation.org. There is a members section, but most of the information can be had without becoming a member. • Automation dll • This is the main gateway between Powerbuilder and the OPC server. • This will need to be installed on both the server computer and the client computer (remote connections). This is critical and is often supplied by the manufacturer of the OPC server. • If the supplier of the Automation dll has modified the automation dll, then they are supposed to change the name from OPCDAAuto.dll.dll to something else. • If the automation dll has been modified, often enhancements have been added and these should be made available for use and documented by the supplier.

26. COMPUTER SETTINGS FOR OPC Remote Operations • Remote Machine Access • DCOMCNFG (Distributed COM Configuration Properties) • Properties (Default Authentication Level and Default Impersonal Level) • Security • Both server and client need to be set correctly (usually DAL as none and DIL as Identify). • When this is setup correctly, the client can start the server as well as connect. • Local Machine Access • DCOMCNFG doesn’t need to be set up. Client and server on the same machine. • Application • User (some OPC servers require this user to be an administrator). • Group (OPCUsers Group)

27. COMPONENTS OF OPC Continued • OPC Server • OPC servers are available from the manufacturer of the PLC. • OPC servers are also available from third party vendors. • OPC servers can be developed in house. • OPC Client • This session will focus on development of an OPC client using Powerbuilder and an existing OPC server. • Database Interface • Any database interface or connection necessary for the setting of the application and the storage of the data.

28. CODE EXAMPLES Startup • Connecting to the automation dll • PB Function ConnectToNewObject is used to connect to the automation dll. • g_OPCAuto = Create OLEObject • ll_err_code = g_OPCAuto.ConnectToNewObject ( "OPC.Automation.1" ) • This is global because it is necessary for the application to connect to the server. All other OPC function calls are routed through this OLE Object. • Structure of Variables. • Global • g_OPCAuto is the only global variable (OPC server variable) • Instance • OPC Groups – Container for the OPC Group Objects. • OPC Group – Container for the OPC Item Collection. • OPC Items – Container for the OPC Item Objects • OPC Item – Actual Tag Name defined on the server.

29. OPC VARIABLES Hierarchy of Objects OPC Server OPC Groups OPC Group OPC Items OPC Item

30. CODE EXAMPLES Continued • Connecting to the OPC server • TRY • ioo_OPCAuto.Connect ( ls_server, ls_node) // OPC function call • CATCH (runtimeerror erConnect) • MessageBox("Runtime Error", 'Place an 'understandable' error message here.‘ + erConnect.GetMessage()) • cb_close.TriggerEvent(Clicked!) • Return • END TRY

31. CODE EXAMPLES Continued • TRY…CATCH..END TRY • The TRY…CATCH…END TRY method is used because if the OPC function call errors out then the application will crash. This provides for an understandable error message and also a clean exit or continuation of the application. • Otherwise this is the result.

32. CODE EXAMPLES Continued • After OPC Server Connection • After successful connection to the OPC server, you must now define the OPC Groups object, OPC Group object, and OPC Item Collection object: • ioo_OPCGroups = ioo_OPCAuto.OPCGroups • ioo_OPCGroups.DefaultGroupIsActive = True • // Create the OPC Groups • TRY • ioo_OPCSimpleGroup = ioo_OPCGroups.Add ( 'OPCDemo') • CATCH (runtimeerror erAdd) • MessageBox("Runtime Error", erAdd.GetMessage()) • cb_close.TriggerEvent(Clicked!) • Return • END TRY • ioo_OPCSimpleItems = ioo_OPCSimpleGroup.OPCItems

33. CODE EXAMPLES Continued • Adding Items to the Item Collection • ioo_OPCSimpleItems.AddItem ( sle_read_param.Text, ll_clienthandle ) • TRY • ioo_ReadOPCItem=ioo_OPCSimpleItems.Item(sle_read_param.Text ) • CATCH (runtimeerror erConnect) • MessageBox("Runtime Error", erConnect.GetMessage()) • Return • END TRY • Read the Tag Name Value • TRY • ioo_ReadOPCItem.Read ( ll_source, ref la_values, ref la_quality, • ref la_timestamp ) • CATCH(runtimeerror erRead) • MessageBox('Runtime Error', erRead.GetMessage()) • END TRY

34. CODE EXAMPLES Continued • Write to the Tag Name Value TRY ioo_WriteOPCItem.Write ( sle_write_value.text) CATCH(runtimeerror erWrite) MessageBox('Runtime Error', erWrite.GetMessage() Return END TRY • Disconnect from the OPC Server • RemoveAll() // Remove all the groups before disconnecting • ioo_OPCAuto.Disconnect () • DESTROY(ioo_OPCAuto)

35. CODE EXAMPLES Continued • Other Useful OPC Functions • Syncread() Reads the value, quality, and timestamp information for one or more items in a group. This function runs to completion before returning. • Syncwrite() Writes values to one or more items in a group. This function runs to completion before returning. • Clienthandle (property). Clienthandle is a long that uniquely identifies the item. This is assigned by the client program (PB application). • Serverhandle (property). Serverhandle is a long that uniquely identifies the group. This is assigned by the server. • Asynchronous functions require the group to be declared with events (VB example: Dim WithEvents anOPCGroup as OPCGroup). As far as I was able to discover, this isn’t possible with PB.

36. OPC BROWSER Components of OPC • OPC Browser • The OPC Browser is useful in that the tag names need to be exact and in the exact format. • This will need to be coded to encapsulate any OPC servers you will be using. For example, some OPC servers use the format: [module]PLCAddress and others will use the module.PLCAddress format. • This is not usually documented and will need to be discovered by trial and error. This will cause an error when the Items OPC function is used.

37. OPC BROWSER Screen Shot

38. CODE EXAMPLES Code Examples for OPC Browser • Connect to OPC Server • Still need to connect to the Automation dll object as defined earlier. • Still need to connect to the OPC server as defined earlier. • Define the OPC Browser OLE Object • Define OLE object, then use the OPC Server function CreateBrowser to populate the OLE object. • OPC Browser Navigation • ShowBranches function first to populate the OLE Object with the root level. • Count returns the number of direct children. • For ll_ctr = 1 to ioo_OPCBrowser.Count • ls_branch = ioo_OPCBrowser.Item ( ll_ctr ) • ltvi_tagname.Label = ls_branch • tv_opcbrowser.InsertItemLast ( 0, ltvi_tagname ) • Next

39. CODE EXAMPLES Code Examples for OPC Browser (continued…) • Browser Navigation (Level 1) • ioo_OPCBrowser.MoveToRoot() // Move to the first level • ioo_OPCBrowser.MoveDown ( ltvi_parent.Label ) // Move to next level to see if there are leafs or branches below. • ioo_OPCBrowser.ShowLeafs // Get any leafs here and count them • If leafs exist, then display them. • If branches exist, then display them with the symbol to expand and get the leafs below.

40. CODE EXAMPLES Code Examples for OPC Browser (continued…) • Browser Navigation (Subsequent Levels) • ioo_OPCBrowser.MoveTo(Branch) // Move to the current branch • ioo_OPCBrowser.ShowLeafs • If ShowLeafs returns 0 then use ioo_OPCBrowser.ShowBranches to expand the branches. • Continue down until all possible leafs are exposed (usually not more than three levels). • Format tag name • Capture the tag name and format it for use. ls_currentposition = ioo_OPCBrowser.CurrentPosition() ls_tagname = ls_currentposition + ltvi_current.Label

41. CODE EXAMPLES Close up • Disconnect from OPC Server • Disconnect and Clean up If IsValid(ioo_OPCAuto) Then ioo_opcAuto.Disconnect() DESTROY(ioo_OPCAuto) End If

42. QUESTIONS Questions and references • References • www.opcfoundation.org • www.matrikon.com • www.microsoft.com

43. CREDITS • Dian Pickarts • Lynn Martineau • Louisa Mi • Alan Campbell • Gregg Weight • Management at Autoliv Promontory Pyrotechnic Processing Facility