eai financial integration project - bc hydro · eai financial integration project peoplesoft ......

FINAL

EAI Financial Integration Project

Peoplesoft (FBT Project) and Indus Passport (Portal Project) Integration

Risk and Control Analysis (RACA)Version 4.0

May 14 2003Reference:EAI FI_RACA_v4.0 Final.doc

EAI FINANCIAL INTEGRATION PROJECT

PASSPORT-PEOPLESOFT INTEGRATION - RACA

File: Appendix F EAI FI_RACA_v4.0 Final.docPrepared by: David Priestley Last Saved: 06/03/03 9:49 AM of 24

REVISION RECORD SECTION

Version Author Status Date Description of Change1-2 David Priestley Draft 19 February 2003 Initial Draft

1-3 David Priestley Draft 5 March 2003 2nd Draft following comments from Stan Krogh

1-4 David Priestley Draft 19 March 2003 3rd Draft

2.0 David Priestley Draft 09 May 2003 Incorporate minor factual updates from FBT project. NotCirculated for comment

3.0 M.Wynne Draft March 2003 Updated IPP diagram and additional comments

4.0 David Priestley Draft May 14 2003 Final draft version for comment before Project closure.




CONTENTS

1 BACKGROUND......................................................................................................................................................................................4

2 SCOPE OF CONTROLS........................................................................................................................................................................4

3 ARCHITECTURAL DESIGN CONTROLS............................................................................................................................................4

3.1 CONTROL DATA SYNCHRONISATION .......................................................................................................................................................53.2 ERROR HANDLING.................................................................................................................................................................................83.3 INFRASTRUCTURE FAILURE CONTROLS .................................................................................................................................................14

APPENDIX A – INDUS-SEEBEYOND-PEOPLESOFT APPLICATION INTEGATION SCHEMATIC..................................................18

APPENDIX B: EAI TOUCHPOINT DATA SYNCHRONIZATION............................................................................................................19

APPENDIX C: NON-EAI (MANUAL) DATA SYSNCHRONIZATION.......................................................................................................23




1 BACKGROUND

The Enterprise AppIication Integration (EAI) Financial Integration (FI) Project was re-initiated in July 2001 to design, build and deploy aseries of 22 interfaces (Touchpoints) between the Indus Passport work management and supply chain system and the Peoplesoft HRand Financial suites using the Seebeyond Enterprise Application Integration product. The three application components are part of theBC Hydro Integrated Package Program (IPP) launch planned for April 22 2003.

2 SCOPE OF CONTROLS

Integration does not represent a business process however the effective and correct operation of the interfaces is a key enabler ofintegrated business processes and data between the Indus and Peoplesoft business applications. The interfaces – or ‘touchpoints’are required to facilitate the transfer of control and transaction data. A diagrammatic representation of the interfaces and the datacomponents passing between the two business applications is contained in Appendix 1.

The categories below represent the areas where controls are in place to ensure availability, completeness, accuracy and timelinessof data. The areas covered in this Risk and Control Assessment address the components of integration that have not been includedwithin the scope of the RACA documentation for either the Peoplesoft or Indus Passport business applications. This covers• Peoplesoft messaging API’s.• Passport’s Financial Integration APIs written in Oracle Stored Procedures feeding the Indus database• EAI Touchpoints built using the Seebeyond application.

The majority of data controls have been built into the overall architecture of the interfaces and specifically been built into the commonerror-handling framework (EHF). These control types are generic to all Touchpoints and are enabled through the architecturalframework for the interface covering:• Control data synchronization - code tables such as employee, vendor etc.• Error handling controls – validations within the EAI Touchpoints.• Infrastructure failure controls – fail over and recovery for gateways, e-ways or the network components.

In parallel, the two API layers for each application contain their own process controls to ensure that only transactions that are ‘valid forthat application are passed through to the API to the interface layer.

3 ARCHITECTURAL DESIGN CONTROLS

The ‘Mitigating Actions’ Column describes the action taken by each of the three application interface components in this order:(a) Seebeyond EAI, (b) Passport, (c) Peoplesoft.




Risk Mitigating Actions Audit Comments

3.1 CONTROL DATA SYNCHRONISATION

I. Inconsistencies betweenPeopleSoft and Passportreference tables causemessages to fail at receptioninto either applicationcovering:

a. EAI: Each interface has a mapping spreadsheet, whichconfirms the translation of data from Passport toPeopleSoft.

Due to the technical complexities of EAI,the stored procedures and interfaces,and the time constraints involved, theaudit review focussed on review of thetesting strategy, the “EnterpriseApplication Integration Error Handling:Framework and Implementation”, and theEnd-to-End (E2E) testing conducted andits documentation.





• Country / Province• Currency Code• UOM (Purchasing)• Employee Type• LBR-SAL• PP Project Type• PP Work Order Type• PP Vendor Type• Group Id to GL BU• Sex• Location to Location• PCE/SCE/DCE to RSC type• Buyer Ids

b. Passport: The PP FI API validates inbound messagedata before posting to the appropriate PP table. If control(reference) data is not recognised, an error is recordedagainst the transaction and this is sent as an error returnmessage through EAI back to PeopleSoft for correction.The message is not accepted into Passport. Messagesthat passes PP FI validation will be processed and thePP tables updated including the timestamps to indicatewhen it was updated or created.

The E2E testing was designed to test themost common business scenarios thatrequired a flow through of data throughthe various touchpoints betweenPassport and PeopleSoft. However, dueto limited project time and resources,several scenarios were not completelytested E2E. Those scenarios that werenot tested were omitted because acommon functionality or interface wassuccessfully tested in other scenarios.

The rationale for limiting the testing andthe scenarios chosen (not tested) wasreasonable, but such limitations intesting can be risky. Therefore, after go-live, the Sustainment Team should beprepared to have to deal with possibleerrors involved with those scenarios.

The planned E2E testing was wellcarried out. Outstanding testing orcontrol issues are being tracked andmonitored. Any significant concerns areidentified on the issues list appended tothe EAI Control Advisory Report.






c. PeopleSoft: Has no inbound message validation in thePS messaging. A record with incorrect or invalid datawill pass through the EAI layer to the PeopleSoftmessaging layer. On receipt into the appropriate modulean error will be thrown if the data is invalid.

Refer also to the PeopleSoft Application MessagingRACA_v1.document.

Refer also to the PeopleSoft ApplicationMessaging RACA_v1.document.




3.2 ERROR HANDLING

The primary goal of the EAI Error Handling strategy was to arrive at an extensible, modular design to be able to trap, edit and resend errormessages to the destination systems in a consistent manner, maintaining the sequential nature of the messages. The Error handlingarchitecture implemented provides the means to:• Consistently identify when errors have occurred and provide adequate auditing/logging.• Easily correct errors and resubmit corrected messages.• Queue messages, maintaining any dependencies and sequencing• Process in parallel (i.e. the existence of one erroneous message in error should not stop all other subsequent messages)• Provide Notification (emails/alerts) either using the e*Gate product itself or by interfacing with third party system software like BMC

Patrol.The detailed specification for the EAI Error Handling Framework is in the document “EAI Error Handling_v0.6_2002_12_08.doc”.





I. Data element errors are passedinto the production system forReal-time inbound and outboundevents – includes:

• Duplicate records• Corrupt Data• Invalid / In Error Data• Resent messages (after

correction)

See Batch processing processfor detailed steps (process is thesame).

a. EAI: Based on the validations within each application, thedata mappings, error handling framework and thepublish/receive validations on the PS messaging and PPStored Procedures, the EAI Error Handling Frameworkwill identify, trap and sent notification on all incoming oroutgoing errors according to the Touchpoint specificationsand for each error, will record all key elements required forfault diagnosis, prioritisation and correction including:• Identifying the action required when the error occurs

(e.g. Resend).• Error message as returned by the target application.• Unique error number and time stamp for correction

tracking• Error Severity to prompt the appropriate corrective

response.• Force-Fail flag to identify if the message was failed in

order to maintain the sequential nature of themessages.

• Interface Id – denoting the source and targetapplications affected

• Message Detail• Message Modified by Flag – to identify the individual

who modified or corrected the message.• No of resends and timestamps• Source and target systems ids• Touchpoint number• Unique key – to identify the individual message.

See Audit Comments in Section 3.1covering the general approach in reviewingintegration controls.





b. Passport: The PP FI API validates inbound message databefore posting to Passport. If the Stored Procedure failsor the collaboration fails due to missing, invalid or out ofdate Passport or PeopleSoft data then:• the PP FI API sends an appropriate error code and

description of the error to the Passport e*Way• EAI’s error handling component will store the

PeopleSoft message that failed• the Passport e*Way calls email or paging e*Way to

send email or page with error message/information todesignated support personnel

• resolving the data problem may involve updating datain Passport, changing the XML message

Outbound messages created by the PP FI API are keptfor reference until they are purged from the online system.This enables PP FI API to track the detail of outboundmessages and resend them from PP FI. The messagepassing mechanism designed between EAI and PP APIensures that only complete messages will be sent to PS.






c. PeopleSoft:• PeopleSoft currently has no mechanism for

identifying and preventing the transmission ofduplicate records.

• Data from source is validated against CTF• Data brought into the interface must pass the

component interface validation or will be held with anerror status

• Mapped data is based on tables validated andmaintained by The PeopleSoft systems and thePassport System.

• Data brought into the interface table is validated bythe component interface to check for duplicate keyvalues

• Query of Project and Activity interface table thattotals number of rows received by interface ID,system source.

• Interface message log that counts number of rowsreceived in PS by interface.Control total report of lines extracted from legacymainframe system.







a. EAI:• On entry into the e*Way, the message is checked to

determine if it is an original or a RESEND event.• IF NOT A RESEND, the message sequence is

checked. If there are no prior dependent messagesin the queue, normal event processing applies. Ifthere are dependent messages in the queue, a ForceFail Exception is generated and a USER EXCEPTIONis thrown requiring manual intervention to correct therecord in the IQ Error Stack.

• IF A RESEND, the message payload (content) isextracted, time-stamped, validated and checked forits sequence in the batch. If there are no priordependent messages in the queue, normal eventprocessing applies.

• IF a RESEND error occurs, the error is checked forUSER exceptions (data validation failures) orUNMARSHAL (system validation failures). In bothcases the result is sent to the IQ Error Stack formanual intervention and correction.


II. Data element errors are passedinto the production system forBatch inbound and outboundevents – includes:

• Duplicate records• Corrupt Data• Invalid / In Error Data• Resent messages (after

correction)

b. Passport:(Staging Table INSERT). If the call to insert data into theStaging Table fails or the collaboration fails due to missing,invalid or out of date Passport or PeopleSoft data then:

• the PP FI API sends an appropriate error code anddescription of the error to the Passport e*Way

• EAI’s error handling component will store thePeopleSoft message that failed

• the Passport e*Way calls email or paginge*Way to send email or page with errormessage/information to designated supportpersonnel






§ resolving the data problem may involve updatingdata in Passport, changing the XML message

• batch data received from PS will be kept online for apredetermined period of time until they are archivedoff.

• unique indexes are created in the staging table tocatch duplicate messages received from EAI

(Staging Table PROCESSING). If the call to the storedprocedure fails due to missing, invalid or out-of-date Passportor PeopleSoft data then:

• The stored procedure will mark the event as INVALID• PP FI API returns an appropriate error code and

description to notify the Passport e*Way that one ormore of the messages were not processed.

The Passport e*Way calls email or paging e*Way to sendemail or page with error message/information to designatedsupport personnel

• Control totals are created on a log that identifies thenumber of records processed and the logs are keptfor a predetermined period of time until they arearchived off the server.

• The timestamps for the rows updated or created byPP FI API are updated.

c. PeopleSoft:






3.3 INFRASTRUCTURE FAILURE CONTROLS


a. EAI

• Application messages will queue up on the applicationside, or within eGate. No application messages will belost.

• All critical errors will be sent to NCS 24X7 OperationsCentre via email call out, to be addressed per operationalprocedures (restart, call-out, etc.).

• The disk space for EAI IQ is sized to handle data volumesfor downtime of up to 7 days.

Due to technical complexities of this area,these controls were not tested. However,the proposed actions appear reasonable.

b. Passport

• On recognition that the E-way is not available, all PPmessages remain within the Passport Oracle databaseuntil polled (called for) by the E-way once it is restored.

• Data storage within the Oracle database is unrestrictedand can easily store messages for 7 days.


I. EAI system down for a period oftime, and the other applicationsunable to access it.

c. PeopleSoft

• The PS application will poll every 20 seconds for 3consecutive attempts to connect to the PS E-way afterwhich time it will time out and the message remainsunsent within the PS database (unrestricted capacity).An email notification is automatically sent to NCSSupport requiring the reason for the time out to bedetermined. Once determined and the E-way is restored,the queued messages will require a manual restart toresend them.

Due to technical complexities of this area,some of these controls were not tested(e.g. E Way). However, the proposedactions appear reasonable.

See Application Messaging RACA_V1





II. The other receiving applicationsdown for a period of time, withEAI messages remainingunprocessed in their queues.

a. EAI (only applicable application)

• The EAI error-handling framework retains the sequenceand dependencies of messages within the sending IQ toensure correct sequence processing when the receivingapplication is restored. These records are held within theEAI error IQ and

• When the PS gateway is down a notification is receivedby the EAI PS e*Way and on notification of this error, thee*Way is shut down and the message that wasattempted to be sent is stored in the error database. Allother inbound messages are stored in the e*Way IQ.Once the connection is restored, notification is requiredfrom the systems administrator that the application isrestored and a manual intervention is required by theSupport team to re-start the e*Way and re-send themessages stored in the Error Database.

• With PP messages, when the PP application is down, anattempt will be made to re-connect and if the applicationis still down, the message will be sent to the errordatabase. As the e*Way is not shut down, once theapplication is restored, the inbound messages stored inthe error database will require the Support team to re-startthe e*Way and re-send the messages stored in the ErrorDatabase.


III. Batch Interface SchedulingFailure

• An automated batch-scheduling tool (ESP) has beenimplemented for the integrated batch net for thePeopleSoft, Indus Passport and Seebeyond batchroutines along with the application and server routineback-ups.

• Manual oversight is maintained as part of the ABS ITI24x7 operations and operator intervention is availableshould the net run into processing difficulties.

No Audit testing of permanent solution.However, the proposed actions appearreasonable.





IV. EAI System failure or memory loss • Two servers are run in a clustered configuration providingreal-time fail-over recovery.

• The two servers each have 7Gb of memory and thecurrent peak load during testing (including the use of the‘Load Runner’ performance monitoring tool) has been 3GB giving over 100% peak capacity available.

• In the event of memory leaks and the entire 7Gb on oneserver is consumed, the second server will cut in as a fail-over and the memory loss will be eliminated on thissecond server.

• Daily back-ups are retained for 30 days allowing recoveryfor almost a Calendar month of transactions.




File: Appendix F EAI FI_RACA_v4.0 Final.docPrepared by: David Priestley Last Saved: 06/03/03 9:49 AM of 24APPENDIX A – INDUS-SEEBEYOND-PEOPLESOFT APPLICATION INTEGATION SCHEMATIC




APPENDIX B: EAI TOUCHPOINT DATA SYNCHRONIZATION

TPNo

Interface Data From Data To Synchronization Impact Timing Complexity

1 Account PS GeneralLedger parentaccounts

PP SystemAdministrationaccount.

• Currency Codes Real Time Low

2 Activity PS GeneralLedger product.

PP SystemAdministrationactivity

• Country & Province codes• Currency Codes

Real Time Low

3 Cost Center PS GeneralLedger department

PP SystemAdministrationcost centre.


Real Time Low

5 Project PS Projects andActivities

PP Data Projectand User Def.


Real Time Medium

6 Vendor PS AccountsPayable Vendors

PP Data ModuleVendors


Real Time Medium

7 Employee PS Pay / HumanResourceemployees andtheir job codes

PP Data Modulepersonnel dataand their job class(also known asresource code inPP).


Real Time Medium

8 Labour Rate PS Pay / HumanResource hourlylabour rate byemployee

PP Data Modulehourly rate bypersonnel.


Real Time Medium




TPNo


9 AccountsPayable Actuals

PS AccountsPayable Actualsback

PP to support thesupply chainmanagementprocess andaverage unit priceadjustments.


Batch High

10 Contracts PP Contract data PS to supportsupply chainprocesses bypassing triggerdata to thepurchase orderinterface.


Real Time Medium

11 Purchase Order PP PurchasingPurchase Orderinformation

PS GL andAccounts Payable

• Unit of Measure impacts AP(TP9), Contracts (TP10), & PO(TP11)

• Currency Codes

Real Time Medium

12 General LedgerTransactions

PP source systemtransactions

PS GeneralLedger.


Batch High

13 ProjectAccountingTransactions

PP source systemtransactions

PS ProjectCosting


Batch High

14 Property Unit In-Service and AsBuilt

PP WorkManagementProperty Unitinformation relatedto work ordertasks

PS ProjectCosting.


Real Time Medium




TPNo


15 Work Order /Task

PP WorkManagement workorder, work orderstatus(summarised),work order taskand work ordertask status(summarised) andwork order taskestimates bydetailed costelement.

PS ProjectCosting project,project status(summarised),project activityand projectactivity status(summarised)and createsproject resourcerows for eachestimate byresource sub-category.


Real Time Medium

19 PP Job Type andPS Activity Type

PP SystemAdministration jobtype (must containthe same valuesas…)

PS ProjectCosting activitytype.

If these are not synchronised, workorder tasks passed from PP to PSwill not be available for use in PSProject Costing.

Manual Low

20 PP Vendor Typeand PS VendorClass

PP SystemAdministrationvendor type mustcontain the samevalues as …)

PS ProjectCosting vendorclass.

If these are not synchronised,vendors passed from PP to PS willnot be available for use in PSAccounts Payable.

Manual Low

21 PP Catalogue Idand PS Inv ItemId

PP Data Modulecatalogue-ids

PS Items Idsmust contain thesame values.

If these are not synchronised,catalogue-ids passed from PP toPS will not be available for use inPS. This will impact both PSAccounts Payable and PS ProjectCosting (proj_resource table).

Real Time Low

23 Client Ids PS Customer andrelated information

PP client Ids Synchronises revisions. Real Time Medium




TPNo


26 MRCommitments

PP commitmentinformation.

PS ProjectCosting.

Batch High

30 Labour Hoursand Dollars

PS Labour hoursand labour billingsrecorded inProjects

PP PBOOK tosupport PP repairversus replacefunctionality andviewing labourhours in PP.

This interface will pass labour hourtransactions to the PP PBOOK(Accounting Transaction Table).Once in PBOOK the informationwill be available to supportdelivered PP processes andpanels.

Batch High

33 PS Currency-Code to PPCurrency Code

PS currencycodes and rates

PP Labour Hours PP must use the same currencycodes and rates as PS.

Real Time Low

36 PR, PO, CR andCOcommitmentsfrom PBOOK

To passcommitment linesthat relate to PRand CR toPeopleSoft

Batch Medium




APPENDIX C: NON-EAI (MANUAL) DATA SYSNCHRONIZATION

Ref. Interface Data From Data To Synchronisation Impact Timing Complexity

16 PP CostElements & PSResourceCodes

PP SystemAdministrationModule detail,secondary andprimary costelements mustcontain the samevalues as Ý

PS ProjectCosting resourcesub-category.category and type


Manual Low

17 PP Project Typeand PS ProjectType

PP SystemAdministrationproject type (mustcontain the samevalues as …)

PS ProjectCosting projecttype.

If these are not synchronized,projects passed from PS to PP willnot be available for use in PP forreference on work orders and thusthe work order / task interface willnot be supported.

Manual Low

18 PP Work OrderType and PSProject Type

PP SystemAdministrationwork order type(must contain thesame values as…)

PS ProjectCosting projecttype.

If these are not synchronized, workorders passed from PP to PS willnot be available for use in PSProject Costing.

Manual Low

19 PP Job Typeand PS ActivityType

PP SystemAdministration jobtype (must containthe same valuesas…)

PS ProjectCosting activitytype.

If these are not synchronised, workorder tasks passed from PP to PSwill not be available for use in PSProject Costing.

Manual Low

20 PP Vendor Typeand PS VendorClass

PP SystemAdministrationvendor type mustcontain the samevalues as …)

PS ProjectCosting vendorclass.

If these are not synchronised,vendors passed from PP to PS willnot be available for use in PSAccounts Payable.

Manual Low




4 Property Unit /Profile

PS AssetManagementProfile ID

PP Data ModuleProperty Unit


NA Low

eai financial integration project - bc hydro · eai financial integration project peoplesoft ......

Documents