Coding Tips for eB2.1 Steve Lamb - QAD

1. Coding Tips for eB2.1Steve Lamb - QAD

2. Contents • Introduction to MFG/PRO eB2.1 • Batch Processing • Sequence Numbers • Audit Trails • Source Code Transformation • MFG/PRO Browse Transformation

3. Challenges When Currently Running with Different MFG/PRO Databases • Because of having separate databases and hardware, IS cost reductions are difficult to achieve • Ad-hoc reporting is difficult since the data is not in one central location • Sharing certain (financial) business functions is complicated because of a multitude of systems • There is a strong drive to centralize and enforce Standard Practices

4. MFG/PRO eB2.1 Shared Services Domain The QAD Solution

5. What is Domain (eB2.1)? • Goal is to provide adequate functional support for multiple business entities (i.e., organizations or domains) within a single database system • Business Entities are represented by Domains in the database • Provides infrastructure for providing cross-domain functionality for inventory visibility, credit control, cross-domain payments, cross-domain cash receipts, and GL consolidation • Multiple Base Currencies and Multiple Chart of Accounts • Document numbering and Control Files set by Domain • Security defined by Domain

6. What Does the Shared Services Domain Solution Look Like? Plant 1 Plant 2 Plant 3 ERP Database ERP Database ERP Database Plant 1 Logical Partition by Domain Plant 3 Logical Partition by Domain Plant 2 Logical Partition by Domain Single MFG/PRO eB2.1 Shared Services Domain Database

7. Domain 2 GBP Domain 3 USD Entity 3 Entity 4 Site 3 Site 5 Site 4 Site 6 Domains, Entities and Sites Database settings, such as users and infrastructure Domain 1 EUR Entity 1 Entity 2 Site 1 Site 2

8. Domains are Independent • Domain 1 – Base Currency EUR • Sites, Entities, Control Files, Items, Accounts, Customers, Suppliers, Orders, Invoices, inventory, GL Transactions, etc. • Domain 2 – Base Currency GBP • Sites, Entities, Control Files, Items, Accounts, Customers, Suppliers, Orders, Invoices, inventory, GL Transactions, etc. • Domain 3 - Base Currency USD • Sites, Entities, Control Files, Items, Accounts, Customers, Suppliers, Orders, Invoices, Inventory, GL Transactions, etc, GL consolidation, etc.

9. Domain • Defines Base Currency, Chart of Accounts, GL Periods etc. • Every Domain can be configuredindependently(control files by Domain) • Security is by Domain • Nearly all static data and all transaction data is by Domain • Selected Static Data can be shared between Domains using Replication • Sharing of data can be defined up to field level

10. Visibility of Domain

11. Switching Domains

12. Security Enhancements MFG/PRO eB2.1 • Enhanced Password Complexity & Ageing • Enhanced User Administration • Enhanced Intrusion Detection

13. eB2.1 User Interface Changes

14. UI • Display flag(header display mode) on Security Control File • determines what is displayed • (knock on effect from Medical DG Part 11) • Database wide setting for consistent UI • Display domain short name and base currency or not • LHS of screen • Display user ID or not • RHS of screen • For multi-domain functions “All Domains” displayed in place of • Domain short name • Controlled by “Multi Domain” logical flag on pgmi_mstr • Details not displayed can be accessed via CTRL-F including:- • program name, full domain name, userID and system date

16. Desktop 2

17. Program Information

18. eB2.1 Batch Processing

19. Batch Processing • Batch Processing is a Multi-domain function • Can process Batch Details by domain • Batch processor can access Batch ID’s across Domains • Default is current domain • Can Inquire and Report on Batch ID’s across Domains.

21. eB2.1 Sequence Numbers

22. Sequence Numbers • Progress sequence numbers database wide - not by domain • 2 Additional reports • Transaction Numbering Report (3.21.19) for tr_hist • Operations Numbering Report (17.13.22,18.4.16,18.22.4.12) for op_hist • these operate across domains to highlight missing sequence numbers • New reports can be run for a selected range of transaction numbers and effective date range.

24. eB2.1 Code changes

25. How was eB2.1 Implemented? • Domain field added to the majority of tables in the database (and indices), although some tables, such as languages, printers, users, menus, etc., are shared between all domains (system data). • Decision criteria for adding domain to a table includes: • Table holds a cost or financial value which is only stored in base currency • Table holds an exchange rate • Table stores GL account codes, sub accounts or cost centers • Table is directly related to one which holds a cost or financial value in base currency or an exchange rate • Table stores master file data which may be unique to the business • Table is a control file • Table is a history file • Table holds numbered documents, e.g. sales orders, purchase orders, work orders, or detail records associated with such documents

26. Index example ============================= INDEX SUMMARY ============================= ============================= Table: cm_mstr ============================ Flags: <p>rimary, <u>nique, <w>ord, <a>bbreviated, <i>nactive, + asc, - desc Flags Index Name Cnt Field Name ----- -------------------------------- --- --------------------------------- pu cm_addr 2 + cm_domain + cm_addr cm_sort 2 + cm_domain + cm_sort

27. How was eB2.1 Implemented? • MFG/PRO user login modified so that the user must select a current domain to work with, in that session. • The users current domain is held in a globally scoped variable “global_domain”. • All record selection statements on domained tables must match their domain field value against this global_domain. • All record creation statements on domained tables must set the table’s domain field to the global_domain value. • This ensures partitioning of data by domain, as though it were in a separate “virtual” database. All business logic can then operate as normal.

28. Code example (adcsbkmt.p) find ad_mstr using ad_addr where ad_mstr.ad_domain = global_domain and ad_type = "c/s_bank" no-error. if not available ad_mstr then do: find first adc_ctrl where adc_ctrl.adc_domain = global_domain no-lock no-error. if not available adc_ctrl then do: create adc_ctrl. adc_ctrl.adc_domain = global_domain. end. create ad_mstr. ad_mstr.ad_domain = global_domain. assign ad_addr = input ad_addr ad_type = "c/s_bank" ad_date = today ad_format = adc_format.

29. Source Code TransformationRecord Selection For each ad_mstr where ad_domain = global_domain and ad_addr = cm_addr: ... ... ... For first pt_mstr using pt_part where pt_domain = global_domain: ... ... ... Find first gl_ctrl where gl_domain = global_domain. ...

30. Source Code TransformationRecord Creation create ad_mstr. ad_domain = global_domain. ... ... ... if not available so_ctrl then do: create so_ctrl. so_domain = global_domain. end. ... ... ... buffer-copy t_poddet to pod_det. pod_domain = global_domain.

31. Source Code TransformationRecord Assignment Display dsdet with frame nul. assign ds_det except ds_domain. ds_domain = global_domain. ... ... ...

32. Source Code TransformationInclude File Arguments {mfnctrl.i "arc_ctrl.arc_domain = global_domain" "arc_ctrl.arc_domain" "ar_mstr.ar_domain = global_domain” arc_ctrl arc_memo ar_mstr ar_nbr arnbr} {mfnp.i vo_mstr vo_ref " vo_mstr.vo_domain = global_domain and vo_ref " vo_ref vo_ref vo_ref} {mfdel.i qad_wkfl " where qad_wkfl.qad_domain = global_domain and qad_key1 = qadkey1 + mfguser"} {swindar.i &domain="ard_det.ard_domain = global_domain and " &domain2="armstr.ar_domain = global_domain and "

33. Source Code TransformationVariable Definition /* SomeProgram.p - Example Transformation */ /* Revision: 1.0 BY: Paul Donnelly ECO: 1 */ {mgdomain.i} Define myvar as character no-undo. ... ... ...

34. MFG/PRO Browse TransformationSteps • The database must be domained first (transform schema and load data, or apply an incremental definition). • Run utdombrw.p and enter the path to the file which lists the domained tables (table.lst). • The utdombrw.p will run and transform the browse and view 4GL expressions. • Inspect the browse expressions for correctness. • Generate the domained browse programs (use utbrgnmt.p) to generate all the browses/lookups). • Check for correctness using the validation tool.

35. How About Performance? • Integrating business operations in one database means that instead of a number of smaller databases there will be one large database • When, in case of an upgrade, prior databases already shared the same hardware, a small impact on performance is to be expected for large runs (MRP) • New Audit Trail functionality has additional impact on performance • In case of a new implementation, selection of hardware has to take into account the combined number of users and data

36. How About Record Locking? • When a large number of users try to update the same data at the same time, users can be forced to wait for the data to become available for update. This is called a Record Lock. In case more users at the same time use the system, chances of a Record Lock might increase. • Record Locking only happens in case users have to access the same data at the same time. Since each Domain has a separate set of control files, master and transaction data, chances of Record Locking are the same as when the users would operate in independent databases.

37. Steve Lambsfl@qad.com610-429-1758