AccountRouteConfigTemplate

V8 Message Definiton

METADATA

Attribute	Value
Topic	5120-srse-gateway
MLink Token	ConfigGateway
Product	SRConfigGateway
accessType	SELECT,UPDATE,INSERT,DELETE
MLink Endpoint	MLink-Live

Table Definition

Field	Type	Key	Default Value	Comment
templateName	VARCHAR(32)	PRI	''
clientFirm	VARCHAR(16)	PRI	''
execBrkrCode	VARCHAR(16)		''	SR execBrkrCode SR Assigned
enabled	enum - YesNo		'None'	yesroute enabled
relationship	enum - RelationshipType		'None'
catDestFirmIMID	VARCHAR(16)		''	Destination Firm IMID supplied by FINRA fbo destination can be BD or Exchange
catDestDeptType	enum - CatDeptType		'None'	CAT destination department type
routingRule	enum - RoutingRule		'RoundRobin'
backupRouteCode	VARCHAR(65)		''	backup route code if above are unavailable
externParams	TINYTEXT		''	pass through params must be understood by end point handler usually tagvaluetagvaluetagvalue
comment	TINYTEXT		''	maintainer commments
modifiedBy	VARCHAR(24)		''	user who last modified this record
modifiedIn	enum - SysEnvironment		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	timestamp of last modification
ClearingFlipList	JSON		'JSON_ARRAY()'
ExchControlList	JSON		'JSON_ARRAY()'
ExecBrkrAccntsList	JSON		'JSON_ARRAY()'
RoutingTableList	JSON		'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
templateName	1
clientFirm	2

JSON Block (ClearingFlipList)

Field	Type	Comment
exDest	enum - exDest	exDest eg CBOEOPT NMSOPT NMSSTK CMXFUT
clrFlipType	enum - FlipType	type of clearing corp delivery
clrFlipFirm	enum - clrFlipFirm	deliverTo clearing member eg OCC NSCC MPID or InstitutionID
clrFlipAccnt	enum - clrFlipAccnt	deliverTo client account eg OCC AID or a DVP FBO code
clrAgent	enum - clrAgent	deliverTo agent eg DVP Agent Bank ID
clrTaxID	enum - clrTaxID	deliverTo taxID eg DVP TaxID
clrBrkrMPID	enum - clrBrkrMPID	clearing broker equity clearing only exDest is an option exch exDestNMSSTK must exist in this list

JSON Block (ExchControlList)

Field	Type	Comment
exDest	enum - exDest	exchange eg NYSESTK CBOEOPT NMSOPT NMSSTK CMXFUT or custom eg INETNIGHTHAWK
disable	enum - YesNo

JSON Block (ExecBrkrAccntsList)

Field	Type	Comment
exchGroup	enum - ExchGroup	eg NMS CMX ICE EUREX NONEANY
secType	enum - SpdrSecType	eg Stock Future Option NONEANY
execBrkrAccnt	enum - execBrkrAccnt	exec broker account supplied by exec broker usually Account1
execBrkrClFirm	enum - execBrkrClFirm	exec broker clientfirm supplied by exec broker usually OnBehalfOfCompId115
execBrkrUserName	enum - execBrkrUserName	exec broker user name supplied by exec broker

JSON Block (RoutingTableList)

Field	Type	Comment
routingCode	enum - routingCode	routing session code is a RouteDefinitionpkeyroutingCode RouteDefinitionexecBrkrCode must match execBrkrCode
featureDisable	enum - FeatureDisable	used to disable certain route features before reaching the routingCoderouteDefinition

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRConfigGateway`.`MsgAccountRouteConfigTemplate` (
    `templateName` VARCHAR(32) NOT NULL DEFAULT '',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `execBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR execBrkrCode (SR Assigned)',
    `enabled` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'yes=route enabled',
    `relationship` ENUM('None','EXSMember','EXSRouter','EXSTech','Platform','SRConnect','Advisor') NOT NULL DEFAULT 'None',
    `catDestFirmIMID` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Destination Firm IMID (supplied by FINRA fbo destination; can be B/D or Exchange)',
    `catDestDeptType` ENUM('None','Exchange','Agency','ATS','DMA','Sponsored','Trading','Other') NOT NULL DEFAULT 'None' COMMENT 'CAT destination department type',
    `routingRule` ENUM('None','RoundRobin','InOrder') NOT NULL DEFAULT 'RoundRobin',
    `backupRouteCode` VARCHAR(65) NOT NULL DEFAULT '' COMMENT 'backup route code (if above are unavailable)',
    `externParams` TINYTEXT NOT NULL DEFAULT '' COMMENT 'pass through params (must be understood by end point handler) usually [tag=value;tag=value;tag=value]',
    `comment` TINYTEXT NOT NULL DEFAULT '' COMMENT 'maintainer commments',
    `modifiedBy` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user who last modified this record',
    `modifiedIn` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of last modification',
    `ClearingFlipList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ClearingFlipList)),
    `ExchControlList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ExchControlList)),
    `ExecBrkrAccntsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ExecBrkrAccntsList)),
    `RoutingTableList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(RoutingTableList)),
    PRIMARY KEY USING HASH (`templateName`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `templateName`,
    `clientFirm`,
    `execBrkrCode`,
    `enabled`,
    `relationship`,
    `catDestFirmIMID`,
    `catDestDeptType`,
    `routingRule`,
    `backupRouteCode`,
    `externParams`,
    `comment`,
    `timestamp`,
    `ClearingFlipList`,
    `ExchControlList`,
    `ExecBrkrAccntsList`,
    `RoutingTableList`
FROM `SRConfigGateway`.`MsgAccountRouteConfigTemplate`
WHERE 
    /* Replace with a VARCHAR(32) */ 
    `templateName` = 'Example_templateName'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRConfigGateway`.`MsgAccountRouteConfigTemplate` 
SET
    /* Replace with a VARCHAR(16) */ 
    `execBrkrCode` = 'Example_execBrkrCode',
    /* Replace with a ENUM('None','Yes','No') */ 
    `enabled` = 'None',
    /* Replace with a ENUM('None','EXSMember','EXSRouter','EXSTech','Platform','SRConnect','Advisor') */ 
    `relationship` = 'None',
    /* Replace with a VARCHAR(16) */ 
    `catDestFirmIMID` = 'Example_catDestFirmIMID',
    /* Replace with a ENUM('None','Exchange','Agency','ATS','DMA','Sponsored','Trading','Other') */ 
    `catDestDeptType` = 'None',
    /* Replace with a ENUM('None','RoundRobin','InOrder') */
    `routingRule` = 'RoundRobin',
    /* Replace with a VARCHAR(65) */ 
    `backupRouteCode` = 'Example_backupRouteCode',
    /* Replace with a TINYTEXT */ 
    `externParams` = 'dummy tiny text',
    /* Replace with a TINYTEXT */ 
    `comment` = 'dummy tiny text',
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000',
    /* Replace with a JSON */
    `ClearingFlipList` = '{"key": "value"}',
    /* Replace with a JSON */
    `ExchControlList` = '{"key": "value"}',
    /* Replace with a JSON */
    `ExecBrkrAccntsList` = '{"key": "value"}',
    /* Replace with a JSON */
    `RoutingTableList` = '{"key": "value"}'
WHERE
    /* Replace with a VARCHAR(32) */ 
    `templateName` = 'Example_templateName'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRConfigGateway`.`MsgAccountRouteConfigTemplate`(
    /* Replace with a VARCHAR(32) */ 
    `templateName`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a VARCHAR(16) */ 
    `execBrkrCode`,
    /* Replace with a ENUM('None','Yes','No') */ 
    `enabled`,
    /* Replace with a ENUM('None','EXSMember','EXSRouter','EXSTech','Platform','SRConnect','Advisor') */ 
    `relationship`,
    /* Replace with a VARCHAR(16) */ 
    `catDestFirmIMID`,
    /* Replace with a ENUM('None','Exchange','Agency','ATS','DMA','Sponsored','Trading','Other') */ 
    `catDestDeptType`,
    /* Replace with a ENUM('None','RoundRobin','InOrder') */
    `routingRule`,
    /* Replace with a VARCHAR(65) */ 
    `backupRouteCode`,
    /* Replace with a TINYTEXT */ 
    `externParams`,
    /* Replace with a TINYTEXT */ 
    `comment`,
    /* Replace with a DATETIME(6) */
    `timestamp`,
    /* Replace with a JSON */
    `ClearingFlipList`,
    /* Replace with a JSON */
    `ExchControlList`,
    /* Replace with a JSON */
    `ExecBrkrAccntsList`,
    /* Replace with a JSON */
    `RoutingTableList`
) 
VALUES(
    'Example_templateName',
    'Example_clientFirm',
    'Example_execBrkrCode',
    'None',
    'None',
    'Example_catDestFirmIMID',
    'None',
    'RoundRobin',
    'Example_backupRouteCode',
    'dummy tiny text',
    'dummy tiny text',
    '2022-01-01 12:34:56.000000',
    '{"key": "value"}',
    '{"key": "value"}',
    '{"key": "value"}',
    '{"key": "value"}'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRConfigGateway`.`MsgAccountRouteConfigTemplate` 
WHERE
    /* Replace with a VARCHAR(32) */ 
    `templateName` = 'Example_templateName'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRConfigGateway.doccolumns WHERE TABLE_NAME='AccountRouteConfigTemplate' ORDER BY ordinal_position ASC;