Technical  Design

Enterprise Integration Framework using Intelligent Agents
Version 0.01
31-Oct-07

Authors:
Business Owner – Raj Chudgar - Electricity Council of Texas (ERCOT)
Agent Architecture Review – James Odell Cutter Consortium
Concept & Process Architect – Eamonn McCormick – Dynamic Networks Inc
Technical Architect – Chi Lee – Utility Integration Solutions
Enterprise Architect – Sanjay Mijar – Electricity Council of Texas (ERCOT)
Integration Program Director – Stephen Kerr – PA Consulting

Introduction
Purpose
The purpose of this document is to layout the technical design for an enterprise integration framework using intelligent agents. This document covers the physical mechanisms and components that can be used to solve complex, high volume integrations between multiple end systems. In particular this framework has been used to solve complex commercial system integration (CSI) problems. The framework itself is not limited to commercial system integrations and can be used in a wide variety of integration and applications arenas.

Problem Description
Commercial systems integrations involve integrating the business processes of upstream market and operations systems and back-end settlements, billing and risk management systems. Complex commercial systems integrations must support intricate integration processing, as well as stringent high-volume, scalability, fault-tolerant and load-balancing requirements.  In addition to this, complex enterprise integrations in general have to be able to “react” to business events as they occur as a natural part of the business process.

Design Constraints and Requirements

The design is based on the high level business requirement and system constraints:
Controlled access to source system: Only limited read and write access is allowed on source system databases for performance and security reasons. 
Source data persistence: Source system will only store staging data for a limited period of time.
Performance: Total end-to-end time for loading and computation is subjected to fall into a time window of less than one hour.
High Volume: The data volume is high and expected to increase
High Value data: The data contains high-value finanical information and requires a large amount of auditing and traceability support.  
Incomplete Source System requirement: Staging tables and structures will not be fully available until very late in the SDLC. This results in the requirement of a flexible and dynamic architecture with event-triggered, metadata-driven approach.
Controllable data state:  Interruptible, restart and recoverable from any atomic state of data in the processing sequence.

Overview
This document focuses on the technical solution of integration from upstream market, real time operations and master data systems (source systems) to downstream Settlements, billing and risk management systems (sink systems) to implement the technical requirements of the commercial systems integration (CSI) application. The solution approach leverages a database and a standard application server to implement these technical requirements. The physical architecture is based on an event-triggered, metadata-driven model to achieve the highest flexibility for dynamic business requirement changes.  Additionally, the adaptive state machine agent approach is utilized for maximum performance, scalability, high-availability and reusability.

Solution Approach Overview
The basic process is for the source systems to place raw data into temporary staging tables (Extract Staging) and enter a corresponding entry in the associated control table upon completion of this staging action.  The control table is monitored and, when the staging action is completed, the corresponding data is staged into a designated CSI area (Extract History).  This allows for tracking and auditing of staging event over time.  Once the data has been staged to the Extract History area, validations are performed on the data.  It is recognized that data errors need to be reported and that there may need to be corrections, so it may be necessary to rerun extract and load process. Once extracted data has been validated, the process of computing billing determinants begins, resulting in a set of billing determinants for a given market. Once the determinants have been computed and verified, an export can be generated for the Settlement and Billing system. This is described using the following data flow diagram.

Functional Components
Context Overview
The following high level architecture diagram provides an overview of the business context.

Risk Management

Market
System

Real
Time
Operations

Master
Data

Source Systems

Ctl Tables

Stg Tables

Ctl Tables

Stg Tables

Stg Tables

Ctl Tables

Event Handler

CSI

Design Time
 Tables

Run Time
 Tables

Sink Systems

Common Services

Archive
 Tables

Sys Config
 Tables

1.Load Sys Config
3.Load Design Param
4.Populate Run time Tbl

2. CTL Event detected

Auditing

Notification

Exception

Scheduling

CSI-State Machine

State Machine

1.Load Run Time Config

2. Load STG 
Tables

Verify

Compute

Validate

Extract

Load

House 
Cleaning

DB 
Web Service

1.Archive
2.Timeout detection

UI-Admin
-Design TBL
-Conf Data

UI-Operation
-Replay/Restart
-Update Value
-Report
-Audit

CSI Core System

Settlements

Solution Overview
The basic approach is to use a framework consisting of a standard application server and an intelligent agent framework to orchestrate, manage and monitor the execution of the integration process via a set of specialized agents. The process actions executed by the agents are organized into control processes which have been pre-defined in a series of CSI design tables. The agents perform the required extract and load, validation, calculations and transformations actions obtained from various market system data in order to derive a set of billing determinants and risk for a given market. 

The sequence diagram described in Figure 2 below defines an overview of the CSI intelligent agent framework integration process.

Figure 2 – CSI intelligent agent framework Sequence Diagram

State Management
The physical design of the CSI intelligent agent framework is driven by a loosely-coupled, three-level State Machine concept.  Event and Process State Orchestration Agents obtain Event and Process-level configuration data from the CSI database which are then dispatched to Event or Process Handler Agents which perform the required activities and then store the run time state back to the database. 

State information is persisted in the database so that the implementation logic for agent recovery can be simplified (Re-run and Pending state logic). 

Figure 3 describes the interaction of the various states pertaining to the Event- and Process Handling agents.

Figure 3 – CSI intelligent agent framework State Transition Diagram

Table 1 defines the various states within the CSI State Transition Diagram and restates their relationships with one another.  The state names in black (Ready, Scheduled, Started, In-Process, and Completed) indicate the expected path of successful execution.  All other states indicate an exception has occurred and illustrate the mechanisms within the CSI intelligent agent framework to recover or restore the process based on operator input.

Key Design Exhibits  
The following series of Exhibits define the functionality that is implemented within the CSI intelligent agent framework design.  These are included here to serve as a reference in the following descriptions and diagrams.

The entries defined in Exhibit 1 are common to both the Event Handler and State Machine BusinessWorks components (COMmon SQL).  COM_SQL1 is used to first determine the current status of an Event and update it as appropriate.  COM_SQL2 is used to get the current status of a Process and update it as appropriate. 

Exhibit 1 – CSI intelligent agent framework Common Design Exhibits
Exhibit	Source 	Parameters and Returns	
	Description		
COM_SQL1	JDBC Update Activity: UpdateEventState	Input Parameters:
NextStatus1, NextStatus2, PrevStatus1,
PrevStatus2, EventProcessID1, EventProcessID2, NextStatus3, NextStatus4, NextStatus5, NextStatus6, PrevStatus3, PrevStatus4, EventProcessID3,
EventProcessID4,
PrevStatus5, PrevStatus6, ThresholdInterval, PrevStatus7, Prevstatus8, EventProcessID7, EventProcessID8, EventProcessID9

Returns:
noOfUpdates	
	Sets the CSI_EVENTSTATE status to the correct state value.  Additionally, when the status is READY, inserts the 		
	update  csi_eventstate set
status = 
	(select 
		case 
			when ? is not null then ?
			when h.tempStatus  = 'READY' then     'SCHEDULED'
			when h.tempStatus = 'SCHEDULED' then  'STARTED'
			when h.tempStatus = 'STARTED' then    'INPROCESS'
			when h.tempStatus = 'INPROCESS' then  'COMPLETED'
        		else 'ERROR'
               end
	from csi_eventstate ,
		(select 
			(case 
				when ? is not null then ? 
				else status 
			end) tempStatus 
		from csi_eventstate  
		where  eventProcessid = ?)h
	where  eventProcessid = ? ),
	eventstate =
	(select 
		case 
		when ? is not null then 'Agent '|| upper(substr(?,1,1))|| lower(substr(?,2,length(?))) 
		when h.tempStatus = 'READY' then     'Agent Scheduled'
		when h.tempStatus = 'SCHEDULED' then 'Agent Started'
		when h.tempStatus = 'STARTED' then   'Agent Inprocess'
		when h.tempStatus = 'INPROCESS' then 'Agent Completed'
		else 'Agent Error'
               end 
	from csi_eventstate ,
		(select 
			(case 
				when ? is not null then ? 
				else status 
			end) tempStatus 
		from csi_eventstate 
		where eventProcessid = ?)h
	where eventProcessid = ?),
	eventstarttime = 
		(select 
			case 
				when h.tempStatus = 'READY' then sysdate 
				else h.eventstarttime
			end 
		from 
			(select 
				(case 
					when ? is not null then ? 
					else status 
				end) tempStatus, eventstarttime 
			from csi_eventstate  
			where eventProcessid = ?)h
		where eventProcessid = ?),
	expectedendtime = 
		(select 
			case 
				when h.tempStatus = 'READY' then ( sysdate + ?/86200)
				else h.expectedendtime
			end
		from 
			(select 
				(case 
					when ? is not null then ? 
					else status 
				end) tempStatus,expectedendtime 
			from csi_eventstate  where eventProcessid = ?)h
			where eventProcessid = ?),
		endtimestamp = 
		(select 
			case 
				when ? = 'COMPLETED' then sysdate
				when ? is null and h.tempStatus = 'INPROCESS' then endtimestamp
				else null
			end
		from 
			(select 
				status tempStatus 
				from csi_eventstate 
				where eventprocessid = ?)h
			)
		where eventProcessid = ?		
COM_SQL2	JDBC Update Activity: UpdateProcessState	Input Parameters:
NextStatus1, NextStatus2, PrevStatus1, PrevStatus2, ActionID1, ActionID2, NextStatus3,
NextStatus4,
NextStatus5,
NextStatus6, PrevStatus3, PrevStatus4, ActionID3, ActionID4, ActionID5

Returns:
noOfUpdates	
	Sets the CSI_PROCESSSTATE status to the correct state value.  		
	update  csi_processstate 
set 
status = 
	(select 
		case 
			when ? is not null then ?
			when h.tempStatus  = 'READY' then    'SCHEDULED'
			when h.tempStatus = 'SCHEDULED' then 'STARTED'
			when h.tempStatus = 'STARTED' then   'INPROCESS'
			when h.tempStatus = 'INPROCESS' then 'COMPLETED'
			else 'ERROR'
		end
	from csi_processstate,
	(select 
		(case 
			when ? is not null then ? 
			else status 
		end) tempStatus 
		from csi_processstate  
		where actionprocessid = ?)h
	where actionprocessid = ? ),
message =
	(select 
		case 
			when ? is not null then 'Process '|| upper(substr(?,1,1))|| lower(substr(?,2,length(?))) 
			when h.tempStatus = 'READY' then 'Process Scheduled'
			when h.tempStatus = 'SCHEDULED' then 'Process Started'
			when h.tempStatus = 'STARTED' then 'Process Inprocess'
			when h.tempStatus = 'INPROCESS' then 'Process Completed'
			else 'Process Error'
               end 
	from csi_processstate ,
	(select 
		(case 
			when ? is not null then ? 
			else status 
		end) tempStatus 
		from csi_processstate  
		where actionprocessid = ?)h
	where actionprocessid = ?)        
where actionprocessid = ?		

The entries defined in Exhibit 2 are specific to the Event Handler Orchestration component (Event Handle Orchestrator SQL).  EHO_SQL1 is used to determine if an event agent is scheduled to operate at the current time and verify that it is not currently started, thus ensuring that only one instance of an event agent is running during the appropriate time period.  EHO_SQL2 is used to insert the event record of an event agent into the CSI_EVENTSTATE run-time table.  EHO_SQL3 – 4 are used for enrichment of the event details prior to publishing the event to be retrieved by an Event Agent instance.

Exhibit 2 – Event Handler Orchestrator Design Exhibits
Exhibit	Description	Parameters  and Returns	
	Source		
EHO_SQL1	JDBC Query Activity: Get Configured Events	Input Parameters:
N/A

Returns:
EVENTAGENTID,
EVENTID,
POLLINGINTERVAL,
STARTEFFECTIVEDATE,
ENDEFFECTIVEDATE,
ACTIVEFLAG,
THRESHOLDINTERVAL,
POLLINGRULEID,
EVENTNAME
	
	Gets the configured, ready events that have not been started from the CSI_EVENTAGENT. Returns the event name.		
	SELECT a.*,b.eventname 
FROM 
    CSI_EVENTAGENT a,
     CSI_EVENTDEF b 
WHERE
    a.ACTIVEFLAG = 'Y' 
    AND TO_CHAR(SYSDATE,'mm/dd/yyyy') BETWEEN
        TO_CHAR(a.STARTEFFECTIVEDATE,'mm/dd/yyyy') 
        AND TO_CHAR(a.ENDEFFECTIVEDATE,'mm/dd/yyyy')
    AND TO_CHAR(SYSDATE,'hh24:mi:ss') BETWEEN                               
        TO_CHAR(a.STARTEFFECTIVEDATE,'hh24:mi:ss') 
         AND TO_CHAR(a.ENDEFFECTIVEDATE,'hh24:mi:ss') 
    AND NOT EXISTS ( 
        SELECT 
            EVENTAGENTID FROM CSI_EVENTSTATE b 
        WHERE 
            b.EVENTAGENTID = a.EVENTAGENTID 
            AND b.STATUS IN ('READY', 'SCHEDULED', 'STARTED',  'INPROCESS', 'ERROR', 'TIMEOUT', 'PENDING') 
    )
    AND a.EVENTID = b.EVENTID		
EHO_SQL2	JDBC Query Activity: GetNextEventProcessID 		
	Gets the next EventProcessID value from the sequence.		
			
EHO_SQL3	JDBC Update Activity: Populate Event State Table		
	Inserts the initial event state into the CSI_EVENTSTATE table.		
			
EHO_SQL4	JDBC Query Activity: Get Event Configuration Details		
	Gets the Rule and data source information associated with the configured event.		
			
EHO_SQL5	JDBC Update Activity: DeleteEventStateEntry		
	Deletes the current entry from the CSI_PROCESSSTATE table so that the Event Orchestrator will detect it upon next iteration.		
			
EHO_SQL6	JDBC Update Activity: InsertEventIntoActionLog		
	Inserts the Event Data into the CSI_ACTIONLOG table.		
			
EHO_XML1	JMS Queue Sender Activity: NotifyEventJMSQueueSender		
	Schema used to publish event data from the Event Orchestrator component to be consumed by the Event Agent component.		
	Schema Name: EventNotificationSchema
Schema Namespace: <TBD>

Event Notification Schema
<ordinality>:<type>:<constraint>:<description>

Root Node: EventConfigurationDetailsType: : EventConfigurationDetails

EventConfigurationDetailsType:
Child Element: String: required: EventProcessID
Child Element: String: required: PollingInterval
Child Element: String: required: EventAgentID
Child Element: String: required: EventID
Child Element: String: required: EventName
Child Element: String: required: BPI_ID
Child Element: String: required: StartEffectiveDate
Child Element: String: required: EndEffectiveDate
Child Element: String: required: SQLStatement
Child Element: String: required: URIString
Child Element: String: required: SystemName
Child Element: String: required: UserName
Child Element: String: required: Password
Child Element: String: required: ThresholdInterval

The entries defined in Exhibit 3  are specific to the Event Handler Agent component (Event Handler Agent SQL).  The entries here deal with determining the correct event to handle, managing the event state and building the correct initial process state for each process defined within the Event.

Exhibit 3 – Event Handler Agent Design Exhibits
Exhibit	Description	Parameters  and Returns	
	Source		
EHA_SQL1	JDBC Query Activity: Get BusinessKeys from EventState Table	Input Parameters:
From Start-
EventAgentID

Returns:
BUSINESSKEY (list)	
	Gets the BusinessKeys associated with the Event that have already begun processing.		
	SELECT BUSINESSKEY
FROM CSI_EVENTSTATE
WHERE
    TO_CHAR(EVENTSTARTTIME,'mm/dd/yyyy') =     TO_CHAR(sysdate,'mm/dd/yyyy')
    AND EVENTAGENTID  = ?
    AND STATUS IN ('ERROR', 'TIMEOUT', 'COMPLETED')		
EHA_SQL2	JDBC Update Activity: Update Businesskey into Event State		
	Associates the businesskey to the current event		
			
EHA_SQL3	JDBC Update Activity: Populate Process State Table		
	Inserts the Event’s process state information into the CSI_PROCESSSTATE Table.		
			
EHA_SQL4	JDBC Update Activity: InsertEventDataIntoActionLog		
	Inserts the event data into the CSI_ACTION table.		
			
EHA_SQL5	JDBC Query Activity: Get Unprocessed Actions		
	Gets the remaining actions to be processed for the current EventID by Businesskey.		

The entries defined in Exhibit 4 are specific to the State Machine Orchestration component (State Machine Orchestrator SQL).  SMO_SQL1 is used to determine the correct process state for dispatch to the State Machine agents.

Exhibit 4 – State Machine Orchestration Exhibits
Exhibit	Description	Parameters  and Returns	
	Source		
SMO_SQL1	JDBC Query Activity: Get Eligible Process States	Input Parameters:
N/A

Returns:
ACTIONPROCESSID,
BUSINESSKEY,
ACTIONSEQ,
STARTTIMESTAMP,
STATUS,
ENDTIMESTAMP,
EVENTID,
ACTIONID,
AGENTID,
AGENTNAME,
AGENTPARAMETER,
ACTIONPARAMETER,
AGENTRULE,
MESSAGE,
EVENTPROCESSID,
CONTROLPROCESSID,
RERUNCOUNT,
SI_ID
BPI_ID	
	Returns list of process states / actions ready to be processed.		
	select a.*,b.bpi_id
from csi_processstate a, csi_eventstate b
where  
	(a.actionseq = 1 and a.status = 'READY') or  
	(a.actionseq > 1 and a.status = 'READY'and
	not exists (
		select actionprocessid 
		from csi_processstate b 
		where 
			a.eventid = b.eventid and 
			b.actionseq < a.actionseq and
			b.status in ('READY', 'INPROCESS', 'SCHEDULED', 'ABANDONED', 'PENDING','ERROR')
		)
	)
	and a.eventprocessid = b.eventprocessid
order by actionid		
SMO_XML1	JMS Queue Sender Activity: SendToProcessQueue		
	XML schema published by the State Machine Orchestrator component to be consumed by the State Agent component(s)		
	Schema Name: ProcessJMSSchema
Schema Namespace: <TBD>

<ordinality>:<type>:<constraint>:<detail>
Root Node: ProcessStateDetailsType: none : ProcessStateDetails

ProcessStateDetailsType:
Child Element: String: required: ACTIONPROCESSID
Child Element: String: required: BUSINESSKEY
Child Element: String: required: ACTIONSEQ
Child Element: String: optional: STARTTIMESTAMP
Child Element: String: required: STATUS
Child Element: String: optional: ENDTIMESTAMP
Child Element: String: required: EVENTID
Child Element: String: required: BPI_ID
Child Element: String: required: SI_ID
Child Element: String: required: ACTIONID
Child Element: String: required: AGENTID
Child Element: String: required: AGENTNAME
Child Element: String: optional: AGENTPARAMETER
Child Element: String: optional: ACTIONPARAMETER
Child Element: String: required: AGENTRULE
Child Element: String: optional: MESSAGE
Child Element: String: required: EVENTPROCESSID
Child Element: String: required: CONTROLPROCESSID		

The entries defined in Exhibit 5 are specific to the State Machine Agent component (State Machine Agent SQL).

Exhibit 5 – State Machine Agent Design Exhibits
Exhibit	Description	Parameters  and Returns	
	Source		
SMA_SQL1	JDBC Update Activity: Update Process State with SIID	Input Parameters:
From CreateSI-
SIId

From SMO_XML1-
ACTIONSEQ,
BUSINESSKEY,
EVENTID,
EVENTPROCESSID,
ACTIONSEQ

Returns:
noOfUpdates	
	If the current process is a Control Process Begin, insert the current SIId into the CSI_PROCESSSTATE.		
	update csi_processstate 
set SI_ID = ? 
where 
	actionseq = ? or 
	( actionseq = 
		(select min(actionseq) actionseq 
		from csi_processstate 
		where  
			businesskey = ? and 
			eventid = ? 
			and eventprocessid = ? 
			and actionseq > ? and 
			upper(agentname) like '%END%'
		)
	)		
SMA_SQL2	JDBC Update Activity: Insert Action Log Entry		
	Writes the initial entry for an action into the CSI_ACTIONLOG table		
			
SMA_SQL3	JDBC Update Activity: UpdateControlProcessSiblings-1		
	Inserts the value of the current ACTIONPROCESSID into the CSI_PROCESSSTATE entries for both the Control Process Begin and Control Process End entries of the current Control Process block.		
			
SMA_SQL4	JDBC Query Activity: GetCurrentControlProcessID		
	Retrieves the current Control Process ID.		
			
SMA_SQL5	JDBC Update Activity: UpdateActionLogEntry	
	
	Updates the CSI_ACTIONLOG entry to ‘COMPLETED’		
			
SMA_SQL6	JDBC Update Activity: UpdateActionLogEntryOnError		
	Updates the CSI_ACTIONLOG entry to ‘ERROR’		
	 		
SMA_SQL7	JDBC Query Activity: CheckProcessID		
	Returns the maximum action id from the CSI_PROCESSSTATE table associated with the parent Event		
			
SMA_SQL8	JDBC Query Activity: GetUnprocessedActions		
	Returns all actionprocessids within the parent Event that have a actionseq value greater than that of the current action.		
			
SMA_SQL9	JDBC Update Activity: InsertCompletedEventIntoActionLog		
	Inserts an entry into the CSI_ACTIONLOG when the last action of the parent event is completed.		
			
SMA_XML1	Parse XML Activity: Parse Process Parameters		
	Schema used to parse the Process Parameters of XMLType from the CSI_PROCSSSTATE table.		
	DBParameters Schema
<ordinality>:<type>:<constraint>:<description>

Root Node:parameters::EventConfigurationDetails
Child Element: String: repeating: parameter		

The entries defined in Exhibit 6 are the Business Rules stored in the CSI_Rule table.  Included are rules for polling the various source system control tables (CT_SQL##) used by the Event Handler to detect the initial Event data as well as the business rules (BR_SQL##) used by the State Machine to execute the necessary data processing associated with each process in an event.

Detailed Design
The following components are implemented using Standard application server and Ajax. All components in this section are dependent upon Common Services and the database.
CSI Common
The processes defined as CSI Common are available for use by components of both the CSI Event Handler and State Machine.  These are not standalone components, but rather they are constituents of both the Event Handler and State Machine.  The activities within the processes below reference the exhibits defined in Exhibit 1 – CSI intelligent agent framework Common Design Exhibits.

CSI Event Handler 
The CSI Event Handler is composed of two types of components or agents; the Event Orchestrator Agent and the Event Worker Agents.  

CSI Event Handler:  Orchestrator Agent Detailed Design
The activities within the processes below reference the exhibits defined in Exhibit 2 – Event Handler Orchestrator Design Exhibits.

Functional Overview 
Timer event trigger elapses
Execute join on CSI_EVENTSTATE and CSI_EVENTAGENT to discover defined events in a ready state
For Each Event discovered
   Insert CSI_EVENTSTATE with Status = ‘READY’
Get Current Event Process ID 
   Get Event Configuration information 
Notify Event Agent process 
   Update CSI_EVENTSTATE Status = ‘SCHEDULED’ 

CSI-Event Handler:  Event Agent Detailed Design
The activities within the processes below reference the exhibits defined in Exhibit 3 – Event Handler Agent Design Exhibits.

Functional Overview 
Receives Event Notification from the CSI – Event Handler: Orchestration Agent
Update CSI_EVENTSTATE Status = ‘STARTED’ 
Polls the Target Source table for Event Data by executing Business Rules 
If Event Data is present
Update CSI_EVENTSTATE Status = ‘INPROCESS’ 
Populate CSI_PROCESSSTATE with Status = ‘READY’

CSI-House Keeping Agents 
The BusinessWorks House Keeping component performs multiple roles in maintaining the resolution of exceptions and state-based maintenance (Timeout and Re-Run agents) and persistence and retrieval (Archive and Re-Load agents) of historical data.
Timeout Agent
Functional Overview
The Timeout Agent performs the following activities at set times.

 Select the current Agent ID and next sequence ID for the housecleaning audit log.
Inserts a log entry into the CSI_HOUSECLEANINGAUDITLOG table.
Updates CSI_PROCESSSTATE entry’s Status value to TIMEOUT where the following conditions are met:
CSI_EVENTSTATE EndTimeStamp value is NULL 
CSI_EVENTSTATE ExpectedEndTime is less than the current time
CSI_EVENTSTATE Status is not one of “COMPLETED”, “ABANDONED”, or “ERROR”
Updates CSI_EVENTSTATE entry’s Status value to TIMEOUT and sets the EndTimeStamp value to the current time where one of the following sets of conditions are met:
CSI_EVENTSTATE EndTimeStamp value is NULL
CSI_EVENTSTATE ExpectedEndTime is less than the current time
CSI_EVENTSTATE Status is not one of “COMPLETED”, “ABANDONED”, or “ERROR”
Or
CSI_EVENTSTATE EndTimeStamp value is not NULL
CSI_EVENTSTATE EndTimeStamp value is greater than CSI_EVENTSTATE ExpectedEndTime value
CSI_EVENTSTATE EndTimeStamp value is less than the current time
Update CSI_HOUSECLEANINGAUDITLOG entry with the time of completion and the number of both the Events and Processes modified.

Figure 6 describes the interaction of House Keeping Archive Agent in relation to the various database structures interfaced.

Figure 6 – House Keeping Timeout Agent

Re-run Agent
Functional Overview
The Timeout Agent performs the following activities at set times.

Select maximum rerun count, Agent ID and next sequence ID for the housecleaning audit log.
Inserts a log entry into the CSI_HOUSECLEANINGAUDITLOG table.
Updates the CSI_PROCESSSTATE Status value to “SCHEDULED” where the following conditions are met:
CSI_EVENTSTATE Status value = “RERUN”
CSI_EVENTSTATE RerunCount value < the value of the maximum rerun count.
CSI_PROCESSSTATE RerunCount value < the value of the maximum rerun count.
If there are processes associated with the current event:
Updates the CSI_EVENTSTATE Status value to “INPROCESS” where the following set of conditions are met:
CSI_EVENTSTATE Status value is “RERUN”
CSI_EVENTSTATE RerunCount value < the value of the maximum rerun count
Otherwise:
Updates the CSI_EVENTSTATE Status value to “SCHEDULED”
Update CSI_HOUSECLEANINGAUDITLOG entry with the time of completion and the number of both the Events and Processes modified.

Figure 7 describes the interaction of House Keeping Archive Agent in relation to the various database structures interfaced.

Figure 7 – House Keeping Re-run Agent

Archive Agent
Functional Overview
The Archive Agent performs the following activities at set times or upon a request from the end user:

Run-Time table maintenance (as needed):

Select archive interval , Agent ID and next sequence ID for the housecleaning audit log
Insert a log entry into CSI_HOUSECLEANINGAUDITLOG table
Select all records from the CSI_RPOCESSSTATE table associated with EVENTS where CSI_EVENTSTATE Status value is one of “COMPLETED”, “ERROR” or “ABANDONED” and that have elapsed the archive interval period value and insert into CSI_PROCESSSTATE Online Archive table
Delete the archived records from CSI_PROCESSSTATE.
Select all records from the CSI_EVENTSTATE table where CSI_EVENTSTATE Status value is one of “COMPLETED”, “ERROR” or “ABANDONED” and that have passed the archival interval and insert them into the CSI_EVENTSTATE Online Archive table
Delete the archived records from CSI_EVENTSTATE.
Update CSI_HOUSECLEANINGAUDITLOG entry with the time of completion and the number of both the Events and Processes modified

Staging table maintenance (2-3 days):

Select archive interval , Agent ID and next sequence ID for the housecleaning audit log
Insert a log entry into CSI_HOUSECLEANINGAUDITLOG table
Select all records from the CSI1 Control Table staging tables that has expired (2 -3 day expiry / archival interval) and insert into CSI1 Staging Online Archive Tables.
Delete the archived records from CSI1 Control Tables.
Update CSI_HOUSECLEANINGAUDITLOG entry with the time of completion and the number of both the Events and Processes modified

Figure 8 describes the interaction of House Keeping Archive Agent in relation to the various database structures interfaced.

Figure 8 – House Keeping: Archive Agent

Reload Agent

Functional Overview
The Archive Agent performs the following activities in response to a user-issued request:

Figure 9 describes the interaction of House Keeping Reload Agent in relation to the various database structures interfaced.

Figure 9 – House Keeping Reload Agent

CSI-State Machine 
The BusinessWorks State Machine is composed of two types of components or agents; the State Orchestrator Agent and the State Worker Agents.  

CSI State Machine: Orchestrator Agent Detailed Design
The activities within the processes below reference the exhibits defined in Exhibit 4 – State Machine Orchestration Exhibits. 

Functional Overview
Timer event trigger elapses
Execute select on CSI_PROCESSSTATE Table to get the next eligible Process Sequences 
For Each Process Sequence:
   Update CSI_PROCESSSTATE Status = ‘SCHEDULED’ 
Publish State data to a named JMS queue to be retrieved by State Agent 

CSI State Machine: State Agent Detailed Design
The activities within the processes below reference the exhibits defined in Exhibit 5 – State Machine Agent Design Exhibits.

Functional Overview
Subscribes to messages from named JMS queue
Update CSI_PROCESSSTATE Status = ‘STARTED’
Execute State Management Process associated with agent type (Control Process and associated activities in Sequence Diagram) – See below 
Update CSI_PROCESSSTATE Status = ‘COMPLETED’ 
When all activities for the current Batch ID are completed, Update CSI_EVENTSTATE Status = ‘COMPLETED’

State Management Processes
Update CSI_PROCESSSTATE Status = ‘INPROCESS’ 
Perform 1 of the following (Defined below)
Pull and Load
Validate
Compute Bill Determinant
Compute Risk Determinant
Verify
Load Sink
Insert data into CSI Run Time CSI_ACTIONLOG table.

Detailed Description
The CSI State Machine: State Agents subscribe to messages published to the named JMS queue destinations.  Upon receiving a message on a queue, the State Agent manages the processing corresponding to the Agent Type associated with the queue.  When the Agent Name contains the value CP, indicating either a start or end Control Process, the CSI_ACTIONLOG table is updated but no further action is taken, otherwise, one of the following actions are executed.
Pull and Load
When the Agent Type is Pull & Load, the State Agent executes the steps necessary to insert the data from the Target System staging table(s) to the corresponding CSI Staging table(s).
Validate
When the Agent Type is Validate, the State Agent executes the steps necessary to validate the checksums associated with the data copied from the Target System and compare with copied data to insure data integrity.  The Validate CP also will perform the cross-referencing of the staged data with the values required for Bill Determinant computations performed in the Compute Control Process.  
Compute
When the Agent Type is Compute, the State Agent executes the steps necessary to calculate the defined Bill Determinants or Risk Determinants from the CSI Staging table(s) and insert them into the CS2 – Bill Determinants table or CSI3 Risk Determinant table as required.
Verify
When the Agent Type is Verify, the State Agent executes the steps necessary to determine that the calculations are correct.  Logic currently defined is that each QSE involved in Bill Determinant calculations must have at least one Bill Determinant by Market.
Load Sink
When the Agent Type is Load, the state Agent executes the steps necessary to move the values from the CS2 – Bill Determinants table to the LodeStar Repository Tables.

Once the current Agent execution is completed, the CSI Run Time: CSI_ACTIONLOG is updated by setting the EndTimeStamp value equal to the current time and Status of CSI Run Time: CSI_PROCESSSTATE is updated to ‘COMPLETED’.

Once all actions defined for a given Business Key are completed by the various Agent Types, the Status of the event corresponding to the Business Key in the CSI Run Time: CSI_EVENTSTATE is updated to ‘COMPLETED’.

Data Models
CSI Data Models

Messaging
A standard JMS server will be used for the message communication between some of functional components.

The CSI intelligent agent framework requires the following JMS queues be in place prior to execution.

Queue Name	Published By	Description	
ENTERPRISE.<ENV>.CSI.NOTIFY.EVENT	Event Orchestrator 	Queue used by the Event Orchestrator component to dispatch Event Notification data to the Event Agent components.	
ENTERPRISE.<ENV>.CSI.PROCESS.RULE.PULL_AND_LOAD	State Machine Orchestrator	Queue used by the State Machine Orchestrator component to dispatch PULL_AND_LOAD processes.	
ENTERPRISE.<ENV>.CSI.PROCESS.RULE.VALIDATE	State Machine Orchestrator	Queue used by the State Machine Orchestrator component to dispatch VALIDATE processes.	
ENTERPRISE.<ENV>.CSI.PROCESS.RULE.COMPUTE	State Machine Orchestrator	Queue used by the State Machine Orchestrator component to dispatch COMPUTE processes.	
ENTERPRISE.<ENV>.CSI.PROCESS.RULE.VERIFY	State Machine Orchestrator	Queue used by the State Machine Orchestrator component to dispatch VERIFY processes.	
ENTERPRISE.<ENV>.CSI.PROCESS.RULE.LOAD	State Machine Orchestrator	Queue used by the State Machine Orchestrator component to dispatch LOAD processes.	
Table 2 – Required JMS Queues