BATCHJOBS

Table structure

Column Name	Data Type	Not null	Key	Description
BATCHID	Varchar2(16)	YES	PK	Batch job identifier (BIC\|\|date\|\|sequence)
USERID	Varchar2(5)	YES	PK	User authorizing the batch identifier
BATCHCOUNT	Number	YES	PK	Number of messages in batch
BATCHAMOUNT	Varchar2(20)	YES	PK	Sum of transaction amounts in batch
COMBATCHID	Varchar2(35)	YES	PK	RE batch job identifier
DEFJOBCOUNT	Number	YES		Number of deferred jobs so far
BATCHSTATUS	Number	YES		0 = new, 10 = in progress, 15 = ready, 20 = failed, 30 = completed
INSERTDATE	Timestamp(6)	YES		Date of job creation
COMBATCHAMT	Number(20,2)	YES		Sum of transaction amounts in batch
ROUTINGPOINT	Varchar2(50)	YES		Authorization queue
REASON	Varchar2(500)	NO		Reason of failure
BATCHTYPE	Varchar2(50)	NO		Batch type
BATCHUID	Varchar2(32)	YES	PK	Internal unique batch identifier
BATCHACKCODE	Varchar2(10)	NO		Batch reply code

Constraints

Name	Column name
PK_BJ_COMBATCH_ID	COMBATCHID;
UK_BJ_CONST	BATCHID; USERID; BATCHCOUNT; BATCHAMOUNT; BATCHUID;
PK_BJ_COMBATCHID	COMBATCHID;

Indexes

Name	Column name

Triggers

Name	Timing point	Event

Notes

>>Stores outgoing message batch details. Messages grouped into batches and authorized by users.
>>Used by: UI, RE, DB

BATCHJOBSINC

Table structure

Column Name	Data Type	Not null	Key	Description
SENDER	Varchar2(12)	YES	PK	Sender bank BIC of the batch
COMBATCHID	Varchar2(35)	YES	PK	Batch identifier provided by the sender
BATCHTYPE	Varchar2(50)	NO		Batch type
INSERTDATE	Timestamp(6)	YES		Date of batch creation

Constraints

Name	Column name
PK_BJI_SENBATCH	SENDER; COMBATCHID;

Indexes

Name	Column name

Triggers

Name	Timing point	Event

Notes

>> It stores incoming batch details. Messages grouped into batches received by the application.

ENTRYQUEUE

Table structure

Column Name	Data Type	Not null	Default	Key	Description
GUID	Varchar2(30)	YES		PK	Identifier generated by the connector
PAYLOAD	Clob	YES			Message content
BATCHID	Varchar2(35)	NO			Batch identifier
CORRELATIONID	Varchar2(30)	YES		PK	Correlation identifier
REQUESTORSERVICE	Varchar2(30)	YES			Requestor service
RESPONDERSERVICE	Varchar2(30)	NO			Requestor service
REQUESTTYPE	Varchar2(30)	YES			Values: SingleMessage, Request, Response
PRIORITY	Integer	NO	5		Message routing priority
HOLDSTATUS	Integer	YES	0		Boolean ( true if the message is waiting for human intervention or external conditions to become available)
SEQUENCE	Integer	YES	0		Current sequence in routing schema
FEEDBACK	Varchar2(40)	NO			Code set by the last operation on the message
SESSIONID	Varchar2(30)	NO			Session identifier
STATUS	Integer	NO	1
QUEUENAME	Varchar2(35)	YES			Application queue name

Constraints

Name	Column name
PK_EQ_GUID	GUID;
UK_EQ_CORRELID	CORRELATIONID;

Indexes

Name	Column name

Triggers

Name	Timing point	Event
TRGAIENTRYQUEUE	AFTER	INSERT

Notes

>>Generic queue table storing messages to be routed and inserted by Connectors. The name of the queue currently holding one message is stored into ROUTEDMESSAGES table. Messages are virtualy routed through queues by changing that value.
>>Used by: RE, Conn, UI, DB

FEEDBACKAGG

Table structure

Column Name	Data Type	Not null	Default	Key	Description
REQUESTOR	Varchar2(50)	NO			Requestor service
WMQID	Varchar2(32)	NO			WMQ message identifier
CORRELID	Varchar2(30)	YES		FK	Correlation identifier
INTERFACECODE	Varchar2(10)	NO			Interface app reply code
NETWORKCODE	Varchar2(10)	NO			Network reply code
CORRESPCODE	Varchar2(10)	NO			Corresopondent Bank / Market Infrastructure reply code
APPCODE	Varchar2(10)	NO			Operator reply code
PAYLOAD	Clob	NO			Message content (xml format)
SWIFTMIR	Varchar2(30)	NO			Message Input Reference ( SAA unique id )
INSERTDATE	Timestamp(6)	YES	SYSTIMESTAMP		Date of insertion
BATCHID	Varchar2(35)	NO			Batch identifier
BATCHSEQ	Varchar2(10)	NO			Message sequence in batch
TRN	Varchar2(35)	NO			Transaction reference
OSESSION	Varchar2(10)	NO			Output session
ISESSION	Varchar2(10)	NO			Input session
ISSUER	Varchar2(12)	NO			Sender bank BIC
OBATCHID	Varchar2(35)	NO			Output batch id

Constraints

Name	Column name

Indexes

Name	Column name
IDX_FB_CORRELID
IDX_FB_TRN	TRN;

Triggers

Name	Timing point	Event

Notes

>> Stores feedbacks and additional details for every incoming or outgoing transaction / message.
>> Used by: UI, RE, DB

HISTORY

Table structure

Column Name	Data Type	Not null	Default	Key	Description
GUID	Varchar2(30)	YES		PK	Generated by the connector
PAYLOAD	Clob	YES			Original message content – encrypted format
BATCHID	Varchar2(35)	NO			Batch identifier
CORRELATIONID	Varchar2(30)	YES			Correlation identifier
SESSIONID	Varchar2(30)	NO			Session identifier
REQUESTORSERVICE	Varchar2(30)	YES			Requestor service
RESPONDERSERVICE	Varchar2(30)	NO			Responder service
REQUESTTYPE	Varchar2(30)	YES			Values: SingleMessage, Request, Response
PRIORITY	Varchar2(1)	YES	5		Message routing priority
HOLDSTATUS	Integer	YES	1		Boolean ( true if the message is waiting for human intervention or external conditions to become available)
SEQUENCE	Number(10,0)	NO	0		Current sequence in routing schema
INSERTDATE	Timestamp(6)	NO			Date of insertion
FEEDBACK	Varchar2(40)	NO			Code set by the last operations on the message

Constraints

Name	Column name
PK_HIST_GUID	GUID;

Indexes

Name	Column name
IDX_HIST_CORRELID	CORRELATIONID;

Triggers

Name	Timing point	Event

Notes

>>Internally used queue table; stores a copy of all routed messages.
>>Used by: RE, Conn, UI, DB

MTXXXTAB

Table structure

Column Name	Data Type	Not null	Default	Key	Description

Constraints

Name	Column name

Indexes

Name	Column name

Triggers

Name	Timing point	Event

Notes

>>Sample table; dynamically created according to keywords defined for each message type and storing specific details.

ROUTEDMESSAGES

Table structure

Column Name	Data Type	Not null	Default	Key	Description
GUID	Varchar2(30)	YES		PK	Message identifier
CORRELATIONID	Varchar2(30)	NO		PK; FK
INSERTDATE	Timestamp(6)	NO	systimestamp		Local time on server
ACK	Integer	NO	0		Replies counter
MSGTYPE	Varchar2(50)	NO			Message type
CURRENTQUEUE	Integer	NO			Indicates whether the message is hold by any queue (values 1/0)
SENDER	Varchar2(35)	NO			Sender bank BIC
RECEIVER	Varchar2(35)	NO			Receiver bank BIC
TRN	Varchar2(35)	NO			Transaction reference
SENDERAPP	Varchar2(35)	NO			Sender service application
RECEIVERAPP	Varchar2(35)	NO			Receiver service application
USERID	Integer	NO			User identifier
AMOUNT	Varchar2(50)	NO

Constraints

Name	Column name
PK_RM_GUID	GUID;
UK_FB_CORRELID
UK_RM_CORRELID	CORRELATIONID;

Indexes

Name	Column name
IDX_RM_CURRQ	CURRENTQUEUE;

Triggers

Name	Timing point	Event

Notes

>>Stores routed messages basic information.
>>USed by: RE, UI, DB

ROUTINGJOBS

Table structure

Column Name	Data Type	Not null	Default	Key	Description
GUID	Varchar2(30)	YES		PK	Job identifier
STATUS	Integer	YES	0		Job processing state
BACKOUT	Integer	YES	0		Number of times this job was tried
PRIORITY	Integer	YES	10		Priority of the job ( 0-99 )
ROUTINGPOINT	Varchar2(50)	NO			Current queue name
FUNCTION	Varchar2(200)	YES			Job action description
USERID	Varchar2(5)	NO			User identifier (generating the job)

Constraints

Name	Column name
PK_RJ_GUID	GUID;

Indexes

Name	Column name

Triggers

Name	Timing point	Event

Notes

>>Stores the jobs assigned to RE at a given moment. After job completion, the assigned record is deleted; in case of failure, record is updated with an error state.
>>Used by: UI, RE, DB

SERVICEPERFORMANCE

Table structure

Column Name	Data Type	Not null	Key	Description
SERVICEID	Number(10,0)	YES	PK	Service identifier [SERVICEMAP.serviceid]
INSERTDATE	Timestamp(6)	YES		Date of creation
MINTRANSACTIONTIME	Number	YES		Shortest interval (time) it took to process a message (in ms)
MAXTRANSACTIONTIME	Number	YES		Longest interval (time) it took to process a message (in ms)
MEANTRANSACTIONTIME	Number	YES		Average transaction time
SEQUENCENO	Number(10,0)	YES		Used when building the batch identifier
IOIDENTIFIER	Number(5,0)	YES		I/O service type
SESSIONID	Number(10,0)	YES		Session identifier
COMMITEDTRNS	Number(10,0)	NO		Number of successful transactions performed by the service
ABORTEDTRNS	Number(10,0)	NO		Number of failed transactions performed by the service

Constraints

Name	Column name
PK_SERVPERF_SERVID	SERVICEID;

Indexes

Name	Column name

Triggers

Name	Timing point	Event

Notes

>>Stores the defined service parameters status.
>>Used by: RE, DB

STATUS

Table structure

Column Name	Data Type	Not null	Key	Description
GUID	Varchar2(30)	YES	PK	UI event identifier
SERVICE	Number(10,0)	YES		Service reporting the event
CORRELATIONID	Varchar2(30)	YES
TYPE	Varchar2(20)	YES		Event type
MACHINE	Varchar2(30)	YES		Host machine
EVENTDATE	Timestamp(6)	YES		Date of event
INSERTDATE	Timestamp(6)	YES		Date of event insertion
MESSAGE	Varchar2(256)	YES		Event message
CLASS	Varchar2(20)	NO		Not used ( always = “no class” )
INNEREXCEPTION	Varchar2(3500)	NO		Description of subevents related to this event
ADDITIONALINFO	Varchar2(3000)	NO		Event description
SESSIONID	Varchar2(30)	NO		Session identifier

Constraints

Name	Column name
PK_STS_GUID	GUID;

Indexes

Name	Column name

Triggers

Name	Timing point	Event

Notes

>>Stores application events - errors, warnings and infos.
>>Used by: RE, UI, DB

TEMPBATCHJOBS

Table structure

Column Name	Data Type	Not null	Key	Description
JOBID	Char(30)	YES		RE job identifier
SEQUENCE	Number	YES		Message sequence ( order in batch )
COMBATCHID	Varchar2(35)	YES	FK	Batch identifier
XFORMITEM	Varchar2(4000)	YES		Transformed form of the message ( XSLT applied )
CORRELATIONID	Char(30)	YES		Correlation identifier
FEEDBACK	Char(40)	YES		Code set by the last operations on the batch

Constraints

Name	Column name

Indexes

Name	Column name
IDX_TMPBATCH_CBID	COMBATCHID;

Triggers

Name	Timing point	Event

Notes

>>Used by: RE