“Who Moved My Cheese” - Enhanced Auditing For JD

2m ago
703.89 KB
59 Pages

“Who Moved My Cheese” Enhanced Auditing forJD Edwards EnterpriseOneColin DawesChief Technology OfficerSyntaxMONTREAL BOSTON TORONTO NEW [email protected]@SyntaxCTOKen YehManager, JDE EnterpriseEnersourcekyeh[email protected]

Presentation Abstract EnterpriseOne supports FDA and Government Regulatory Compliance through the use ofenhanced audit trails and electronic signature capture through the CFR Part 11 tool (aka DataChange Tracker).These tools/features are NOT on by default but can also be used to satisfy internal auditrequirements and increase the visibility into change control.In this session we will review real customer case scenarios to increase auditing on the addressbook, item master, bank files business unit master and other key system areas. We will alsodissect the technology configuration and consider multi-environment configurations andoverall administration including environment refreshes and Software Change Managementimplications.During this session we will actually go through some sample configurations of the enhancedauditing.28/06/2015MONTREAL TORONTO BOSTON NEW YORK2

Agenda About EnersourceAuditing OverviewEnterpriseOne Auditing OptionsEnersource Implementation & PlanningEnersource’s Auditing ImplementationEnersource’s Auditing Next StepsQuestions & Answers28/06/2015MONTREAL TORONTO BOSTON NEW YORK3


About Enersource Founded in 1917A Diversified Energy and Technologies Company Regulated electricity utility serving the City of Mississauga 3rd largest by demand of 80 municipally-owned utilities in Ontario 200,000 customers Leads all Ontario hydro utilities in reliability Non Regulated Business: Energy Services Street Lighting for Mississauga and Brampton 400 Employees90% owned by the City of Mississauga10% owned by Borealis (an OMERS Company)28/06/2015MONTREAL TORONTO BOSTON NEW YORK5

JD Edwards at Enersource Financials G/L, A/P, A/R, Fixed Assets Operations Service Orders, Case Management, Time Entry Distribution and Logistics Inventory, Procurement Human Resources Management28/06/2015MONTREAL TORONTO BOSTON NEW YORK6

JD Edwards at EnersourceDATEACTIVITYOctober 2003Go-Live on OneWorld Xe on AS400July 2005Upgrade to JDE 8.10 (Syntax)Web Client on WAS 6.0 (Intel)Auditing ImplementationUpgrade to EnterpriseOne 9.0.2/ (Syntax)Migration from AS400 to Highly-Available Windows 2008-R2 &Oracle RAC on VMWare & WAS 7.0.11Auditing Implementation ExpansionNov 2011May 201428/06/2015MONTREAL TORONTO BOSTON NEW YORKGo-Live on EnterpriseOne Tools Release (Syntax)Patch WAS to 7.0.297


Auditing Requirement Any company is accountable for the data itstores. So any modification that is done to thedata impacting the company financially orlegally should be traceable to its source.28/06/2015MONTREAL TORONTO BOSTON NEW YORK9

EnterpriseOne Standard Audit Trail Records the following: User Id, Program Id, Workstation Id, Date and Time Change history is not retained by default in most functionalareas ie Add record then change the record – detail of who initially added therecord not retained ie Add record, change then delete the record – no details retained Lacks the ability to capture who did what, when, where, andwhy.28/06/2015MONTREAL TORONTO BOSTON NEW YORK10

EnterpriseOne Enhanced Auditing EnterpriseOne 21 CFR Part 11 Tool EnterpriseOne Data Change Tracker3 Different Names for exactly the same thing28/06/2015MONTREAL TORONTO BOSTON NEW YORK11

EnterpriseOne Enhanced AuditingTable and transaction level auditingGeneral purpose tool for tracking changes to JD Edwards EnterpriseOne data. TheJD Edwards EnterpriseOne auditing tool can be configured to track changes whendesignated columns change on designated JD Edwards EnterpriseOne tables. It willalso correlate changes made to multiple tables as part of a single transaction.Originally designed for JD Edwards EnterpriseOne customers that are required tocomply with the Food and Drug Administration's (FDA) 21CFR part 11 regulation fortracking changes to key business data, this is a general purpose tool used bycustomers in all industries.Change: Added in Release Tools 8.92 (or Xe/8.0 SP21)Notes: Minimum applications release is 8.9 (or Xe/8.0 SP21)28/06/2015MONTREAL TORONTO BOSTON NEW YORK12

What is 21 CFR Part 11?21 CFR Part 11 became law on 20 August 1997. Theregulation outlines its criteria for acceptance ofelectronic records, electronic signatures, andhandwritten signatures. It allows electronic records tobe considered equivalent to paper records andhandwritten signatures.Code of Federal Regulations (CFR)28/06/2015MONTREAL TORONTO BOSTON NEW YORK www.syntax.com13

EnterpriseOne Enhanced AuditingEnhanced auditingThis project enhances existing functionality within JD Edwards EnterpriseOneauditing capabilities, which includes features that help companies comply with theFDA 21 CFR part 11 regulation. First, multiple JD Edwards EnterpriseOne users cansimultaneously view 21 CFR audit information from a web browser-based clientwithin a JD Edwards EnterpriseOne web environment. Second, JD EdwardsEnterpriseOne users who track 21 CFR audit changes can run a batch job to removeuncommitted audit records within the application. Finally, JD EdwardsEnterpriseOne users who use 21CFR auditing can attach media objects within thesignature application.Change: Added in Release Tools 8.96Notes: Minimum applications release is 8.1228/06/2015MONTREAL TORONTO BOSTON NEW YORK14

Enhanced EnterpriseOne Auditingat EnersourceMONTREAL TORONTO BOSTON NEW YORK

EnterpriseOne Auditing at Enersource Initially implementedon EnterpriseOne8.10 on the IBMSystem i due tointernal auditor andother business ownerconcerns.28/06/2015MONTREAL TORONTO BOSTON NEW YORKTable NameTable DescriptionF0401Supplier MasterF060116Employee Master InformationF0901Account MasterF4102Item Branch FileF4801Work Order Master File16

EnterpriseOne Auditing at Enersource Additional tablesadded afterupgrading toEnterpriseOne 9.0.2and migrating to anOracle Database. IFRS Requirements28/06/2015MONTREAL TORONTO BOSTON NEW YORKTable NameTable DescriptionF1201Asset Master FileF12002Default Accounting ConstantsF12003Default Depreciation ConstantsF12851Depreciation RulesF12853Depreciation FormulasF1301Equipment RatesF3111Work Order Parts ListF4104Item Cross Reference FileF554101TInventory Item Master Tag TableF554843Work Order Tag Table17

Enersource use Case #1 Assist with troubleshooting Work Order application. Investigated incidents of some fields not updating onthe Work Order file in certain situations. Who/When changed the start date of the work order?28/06/2015MONTREAL TORONTO BOSTON NEW YORK18

Enersource use Case #2 Some account master configuration changes in F0901 wereunexpected The CFR auditing was used to determine who changed theparticular fields Allows assistance with the troubleshooting, root causeanalysis and corrective actions.28/06/2015MONTREAL TORONTO BOSTON NEW YORK19

Enersource use Case #3 EnterpriseOne does not allow you to go back in timeto track the various changes on a work order partslist. The system only tracks the current state. Thus, it’s not possible to see when things were actually being added tothe parts list. Therefore it’s not possible to do an analysis to gauge the root cause ofa material shortage.28/06/2015MONTREAL TORONTO BOSTON NEW YORK20

Enersource use Case #3 Business inquiry as to why some materials were notready for use at the appropriate time. Investigation as to when certain items were ordered on the work orderparts list to match up as to when they should have been ordered. Using the auditing feature it was possible todetermine the specific material demand at a point intime to see if the Enersouce ordering process waseffective28/06/2015MONTREAL TORONTO BOSTON NEW YORK21

Enhanced Auditing TechnicalOverview and ConfigurationMONTREAL TORONTO BOSTON NEW YORK

Audit Trail Records - Overview Audit records contain: User ID, Address Book Number, Full Name (Signed on user, not the Database proxy) Unique transaction ID to link all table changes Time Stamp (Single source DB where System Table is at) Before and after images (captured as separate records) Identification of source of change Machine, User, Application, Version28/06/2015MONTREAL TORONTO BOSTON NEW YORK23

Audit Trail Records - Overview Can turn on/off by path code Flexible configuration Specify which columns of a table which triggersthe audit (but ALL records in the table are writtento the audit table)28/06/2015MONTREAL TORONTO BOSTON NEW YORK24

Audit Trail – Overview (Shadow table)Implementation of auditing on a business data wF0101ShadowRDBMSTableTableF0101 ADT TriggersA010128/06/2015MONTREAL TORONTO BOSTON NEW YORK25

Audit Trail – Overview (Shadow table)Implementation of auditing on a business data table:AuditingAuditingDisabledEnabledTableF01011. Add AuditColumns28/06/2015MONTREAL TORONTO BOSTON NEW YORKTableF0101 ADT26

Audit Trail – Overview (Shadow table)Implementation of auditing on a business data table:AuditingAuditingDisabledEnabledTableF01012. Create DBMSTriggers (Insert,Update, Delete)ShadowRDBMSTableTableF0101 ADT TriggersA010128/06/2015MONTREAL TORONTO BOSTON NEW YORK27

Audit Trail – Overview (Shadow table)Implementation of auditing on a business data wF01013. Replace theoriginal JDE tablewith a DBMS NativeViewShadowRDBMSTableTableF0101 ADT TriggersA010128/06/2015MONTREAL TORONTO BOSTON NEW YORK28

Audit Trail - Deployment ModelInteractive Applications P9500001 Configuration Application P9500003 Object & Table Configuration P9500005 View Audit Information Batch Applications R9500005 Print Audit Information28/06/2015MONTREAL TORONTO BOSTON NEW YORK29

Audit Trail – Audit Table Config CNC skills required toset up ‘non-Julian’ datasources Set up multiples and mapvia OCM to supportmultiple path codes. Must configure for allenvironments on thepath code28/06/2015MONTREAL TORONTO BOSTON NEW YORK30

Audit Trail – Table Design Aid Developer skills requiredto select columns onwhich to trigger auditingin Table Design Aid (TDA) Updated objects must bepromoted to the pathcode where you intend forauditing to be activated.28/06/2015MONTREAL TORONTO BOSTON NEW YORK31

Audit Trail – Audit Table Config Requires activationfor the ‘J’ and ‘non-J’environments28/06/2015MONTREAL TORONTO BOSTON NEW YORK32

Audit Trail - Inquiry28/06/2015MONTREAL TORONTO BOSTON NEW YORK33

Audit Trail - Report28/06/2015MONTREAL TORONTO BOSTON NEW YORK34

Enhanced AuditingImplementation ConsiderationsMONTREAL TORONTO BOSTON NEW YORK

Implementation Considerations Data Size The more you audit, the more space you need. Can archive off auditing tables since no applications usingthis data. DO NOT Audit Major Transactional Tables Large size increase Shouldn’t be able to delete history through the applicationregardless28/06/2015MONTREAL TORONTO BOSTON NEW YORK36

Implementation Considerations System Performance Native Database Triggers leveraged for best performance Any field being audited that is changed will cause an audit trailwith all records. Cannot specify by value (ie in address book can not auditjust vendor records) Cannot have fields be added to trail that in themselves donot cause a trigger event. UBE performance impacted more than interactive (try not todo this!)28/06/2015MONTREAL TORONTO BOSTON NEW YORK37

Enhanced Auditing aka CFR Part 11aka Data Change Tracker LimitationsMONTREAL TORONTO BOSTON NEW YORK

CFR Challenges Can not audit “boot strap” tables F0092, F0093, F0094, F98DRENV, F98DRPUB, F98DRSUB, F98OWSEC, F00165, F00921, F00922,F00924, F00925, F00926, F00941, F00942, F00945, F00948, F00960, F9200, F9202, F9203,F9207, F9210, F9211, F9312, F9650, F9860, F9861, F9862, F9863, F9865, F9885, F9886, F9887,F9888, F9889, F95921, F98101, F98611, F98613, F98710, F98712, F98713, F98720, F98740, F98741,F98743, F98745, F98750, F98751, F98752, F98753, F98760, F98761, F98762, F98891, F98950,F983051, F986101, F986110, F986111. Some tables can not be audited as they already havea column conflict with a new field that auditing wouldadd (ie duplicate column name)28/06/2015MONTREAL TORONTO BOSTON NEW YORK39

CFR Challenges Software Change Management requires that auditingis disabled Upgrade require auditing off ESUs and ASUs that affect the audited table (rareoccurrence) require auditing to be off Need to save configuration, use tools to deactivate triggers,upgrade or apply the ESU/ASU, and re-configure28/06/2015MONTREAL TORONTO BOSTON NEW YORK40

CFR Challenges Auditing activation does not “flag” the objects asmodified on the system and will not show up on theImpact Analysis report Can manually flag the tables on the system Auditing can only be activated/deactivated when noone is on the system and the tables are not locked byany process28/06/2015MONTREAL TORONTO BOSTON NEW YORK41

CFR Challenges Officially supported in one “path code” only All environments that share a path code are alsoenabled for auditing Special configuration required to support multiplepath codes28/06/2015MONTREAL TORONTO BOSTON NEW YORK42

CFR Challenges Data Refreshes may require custom post refreshscripts Refresh from an environment with auditing on the pathcode to a environment without auditing on the path code Refresh from an environment with auditing on the pathcode to a environment with auditing on the path code doesnot require special scripts Small exception for some possible triggers updates on some DBMS28/06/2015MONTREAL TORONTO BOSTON NEW YORK43

CFR Challenges Implementation of Auditing requires an individual skilled inCNC who also knows basic EnterpriseOne Development Addition of additional tables or additional columns to existingaudited tables requires CNC and Development skills andsystem downtime Implementation can be done in 32 – 48 hours Planning, pre-requisites, CNC config changes, custom refresh scriptsand documentation, training, implementation of up to 10 tables in 2path codes28/06/2015MONTREAL TORONTO BOSTON NEW YORK44

Enersource’s impressions onEnhanced AuditingMONTREAL TORONTO BOSTON NEW YORK

Enersource Impressions Enhanced auditing is not just to discover “who doneit” but can be used to assist in the identifyingbusiness process improvement opportunities. Assist in the analysis of the Enersource material planningprocess No current standard reporting is a limitation28/06/2015MONTREAL TORONTO BOSTON NEW YORK46

Enersource Impressions Most usage of the Enhanced auditing reporting is byrequest only No periodic reporting currently in place Internal Auditors and Finance team like the fact thatthe system is tracking it External Auditors like to see that controls are in placeon the financial process28/06/2015MONTREAL TORONTO BOSTON NEW YORK47


Enersource Next Steps for Auditing Potentially add a few additional tables for auditing: F0006 – Business Unit Master F0010 – Company Constants Develop additional specific auditing reports28/06/2015MONTREAL TORONTO BOSTON NEW YORK49

Questions?28/06/2015MONTREAL TORONTO BOSTON NEW YORK www.syntax.com50


Who we are Founded in 1972 40 years of ERP experience Complete solution provider Over 400 customers across North America 100 Professionals – more than 80 dedicated toJD Edwards Offices in the U.S. & Canada28/06/2015MONTREAL TORONTO BOSTON NEW YORK www.syntax.com5252

Awards and Recognition #1 Cloud provider for Oracle JD EdwardsTop 3 Worldwide Oracle JD Edwards partner.Top 2 North American Oracle JD Edwards partner.Go-to Partner for Oracle JD Edwards.Certified by IBM and Oracle as Managed Services andSoftware as a Service (SaaS) provider.28/06/2015MONTREAL TORONTO BOSTON NEW YORK www.syntax.com5353

What We Do JD Edwards Partner Since 1997Upgrades & MigrationsTechnology & Infrastructure ServicesApplication & Development ServicesOracle Approved Training PartnerEnterpriseCloud and Managed ServicesIBM Premier Business PartnerIBM Server Infrastructure: Hardware &Middleware28/06/2015MONTREAL TORONTO BOSTON NEW YORK 70 Net New Implementations 75 Upgrades 300 JD Edwards Customers54

Oracle Platinum PartnerWholesale DistributionIndustry28/06/2015MONTREAL TORONTO BOSTON NEW YORK www.syntax.com5555

Cloud Choices for JD Edwards EnterpriseOneRange of options for JD Edwards customers on the Syntax Enterprise CloudOn PremiseCustomerManagedOn PremiseSyntax RemoteManagedSyntax EnterpriseCloud (IaaS)Syntax ManagedSyntax EnterpriseCloud (SaaS)Syntax EnterpriseCloud (SaaS)JD Edwards CompleteERP SuiteYesYesYesYes - BPSYes - BPSCNC ManagementCustomerHosting LocationCustomerCustomerSoftware LicenseCustomerCustomerCustomerChange ControlCustomerCustomerCustomerCustomerApplication Managed28/06/2015MONTREAL TORONTO BOSTON NEW YORKHosted & Managed56

EnterpriseOne System Administration Lifecycle ManagementManage JD Edwards EnterpriseOneServer Manager Oracle Enterprise ManagerApplication Management Packfor JD Edwards EnterpriseOne Oracle Real User ExperienceInsight (REUI) with JD EdwardsEnterpriseOne Accelerator Oracle Database Diagnosticsand Tuning Pack 28/06/2015MONTREAL TORONTO BOSTON NEW YORKImplement Oracle Application TestingSuite (OATS) Functional Testing Load Testing Test Management Real Application Testing Data Masking Oracle User Productivity Kit(UPK)Deploy JD Edwards EnterpriseOneChange Assistant Change Management57

The Syntax AdvantageOur People – The best, most experiencedconsultants and project leadersOur Processes – Proven Methodologies totransition Customers from today intotomorrowOur Experience – Over 40 years working withcustomers optimizing their technology andapplicationsOur Reputation – JD Edwards PlatinumPartner with outstanding customerreferences.28/06/2015MONTREAL TORONTO BOSTON NEW YORK www.syntax.com5858