Warehouse Trade Inbound Interface

Owned by Natalie Gesin (Deactivated)
Last updated: Sept 29, 2020 by Dmitry Donosiyan (Unlicensed)
2 min read

Overview

Warehouse Trade inbound interface is designed to load and update data in the following database tables:

  • TRADESDBO.TRADE
  • TRADESDBO.TRADE_DETAIL

Relationship between DB tables TRADESDBO.TRADE and TRADESDBO.TRADE_DETAIL (1:1):

TRADESDBO.TRADE.TRADE_ID = TRADESDBO.TRADE_DETAIL.TRADE_ID

Format of the incoming message: EagleML (WarehouseTrade complex type).


Note

Warehouse Trade is one of the essential EagleML objects types. This means that some object types cannot be loaded into DB without an already existing TRADE record in it. The list of "dependent" types as follows:

  • Warehouse Broker Trade Quote
  • Warehouse Pending Trade
  • Warehouse Close Lot

Stream Logic

EagleML Processing Sequence

Warehouse Trade is loaded into DB via eagle_ml-2-0_default_in_xml_warehouse_preproc in two steps:

  • XSLT transformation. XSLT rule 'eagle_default/in/xml/xslt/wrh_egl_gen_interface3.xsl' is used.
  • Loading data into the database. The 'eagle_default/in/xml/xml-warehouse_v2.xml' rule is used.

To load or update data in Warehouse Trade database tables send an EagleML message into the eagle_ml-2-0_default_in_xml_warehouse_preproc stream.

Note

<netCash> and <netAmountLocal> are passed into the same DB field TRADESDBO.TRADE.NET_CASH. You should use either of these two nodes in your incoming message, but not both of them at the same time.<toCashType> and <toCashEventType> are passed into the same DB field TRADESDBO.TRADE.TO_CASH_TYPE. You should use either of these two nodes in your incoming message, but not both of them at the same time.
 Click to see an example of incoming EagleML message

<EagleML xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" eaglemlVersion="2-0" eaglemlType="WarehouseTransactionMessage" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" xsi:type="WarehouseTransactionMessage">
    <warehouseTransaction>
        <header>
            <batchKey>entity^20150305^EAGLE PACE</batchKey>
            <objectType>WarehouseTrade</objectType>
        </header>
        <warehouseTrade>
            <objectType>WarehouseTrade</objectType>
            <objectId>WarehouseTrade</objectId>
            <sourceName>EAGLE PACE</sourceName>
            <updateSource>MCADMIN</updateSource>
            <effectiveDate>2015-03-05</effectiveDate>
            <xrefAccountId>A1Q2W3R4U5K</xrefAccountId>
            <xrefAccountIdType>A1Q</xrefAccountIdType>
            <xrefClassCode>TF4</xrefClassCode>
            <accountingBasis>GAAP</accountingBasis>
            <primaryAssetId>CUSIP1A2B3CU</primaryAssetId>
            <primaryAssetType>CUSIP</primaryAssetType>
            <cusip>CUSIP1A2B3CU</cusip>
            <isin>ISIN1A2B3CU</isin>
            <sicovmId>SICOVMID1A2B3CU</sicovmId>
            <assetResolutionOption>MATCH_ALL</assetResolutionOption>
            <battingOrder>cusip,isin,sicovmId</battingOrder>
            <brokerCode>EAGLE</brokerCode>
            <clearingBrokerCode>EAGLE</clearingBrokerCode>
            <status>STATUS</status>
            <taxAmountLocal>892.561</taxAmountLocal>
            <commissionAmount>905.482</commissionAmount>
            <accruedInterest>246.766</accruedInterest>
            <baseCurrency>USD</baseCurrency>
            <monthEndAccountingDate>2015-02-18</monthEndAccountingDate>
            <accruedInterestType>CUM</accruedInterestType>
            <accruedIncomeLocal>885.587</accruedIncomeLocal>
            <cancelAccountingDt>2015-02-18</cancelAccountingDt>
            <cancelMthendAccountingDt>2015-02-18</cancelMthendAccountingDt>
            <cancelFlag>N</cancelFlag>
            <cancelStatus>A</cancelStatus>
            <cancelEffectiveDt>2015-02-18</cancelEffectiveDt>
            <cancelEventId>TSR80A</cancelEventId>
            <cancelLinkEventId>TSR80A</cancelLinkEventId>
            <currBuiltInGlSecurity>663.806</currBuiltInGlSecurity>
            <currBuiltInGlCurrency>515.099</currBuiltInGlCurrency>
            <currBuiltInGlDeferred>605.849</currBuiltInGlDeferred>
            <currBuiltInGlOid>463.521</currBuiltInGlOid>
            <currBuiltInGlMktDisc>208.855</currBuiltInGlMktDisc>
            <gainCurrency>537.996</gainCurrency>
            <gainSecurityLocal>193.128</gainSecurityLocal>
            <totalGain>893.57</totalGain>
            <fromAccount>ENTST001</fromAccount>
            <fromAssetid>24126749</fromAssetid>
            <fromLongShort>Y</fromLongShort>
            <fromSourceSystem>Y</fromSourceSystem>
            <fromCostBasisType>C</fromCostBasisType>
            <fromWashSaleEligInd>N</fromWashSaleEligInd>
            <lossCurrency>185.284</lossCurrency>
            <lossSecurityLocal>768.24</lossSecurityLocal>
            <totalLoss>595.553</totalLoss>
            <originalAcquisitionCostBase>818.366</originalAcquisitionCostBase>
            <originalAcquisitionCostLocal>998.717</originalAcquisitionCostLocal>
            <originalFaceValue>785.64</originalFaceValue>
            <originalBuiltInGlMktDisc>593.93</originalBuiltInGlMktDisc>
            <originalTradePrice>742.018</originalTradePrice>
            <originalBuiltInGlCurrency>787.477</originalBuiltInGlCurrency>
            <originalBuiltInGlDeferred>486.493</originalBuiltInGlDeferred>
            <originalBuiltInGlOid>647.502</originalBuiltInGlOid>
            <originalBuiltInGlSecurity>934.244</originalBuiltInGlSecurity>
            <originalSettlementDate>2015-02-18</originalSettlementDate>
            <originalQuantity>734.445</originalQuantity>
            <openCostBase>589.178</openCostBase>
            <openHldPerDate>2015-02-18</openHldPerDate>
            <performanceFlow>879.44</performanceFlow>
            <performancePrice>481.915</performancePrice>
            <premiumLAllocated>388.732</premiumLAllocated>
            <premiumBAllocated>515.709</premiumBAllocated>
            <settlementAmount>628.796</settlementAmount>
            <toAccount>ENTST002</toAccount>
            <toAssetid>24126750</toAssetid>
            <toLongShort>N</toLongShort>
            <toSourceSystem>N</toSourceSystem>
            <toCostBasisType>P</toCostBasisType>
            <toCashEventType>N</toCashEventType>
            <toWashSaleEligInd>Y</toWashSaleEligInd>
            <userChar1>USER_CHAR1</userChar1>
            <userChar2>USER_CHAR2</userChar2>
            <userChar3>USER_CHAR3</userChar3>
            <userDefinedFloat1>292.604</userDefinedFloat1>
            <userDefinedFloat2>923.46</userDefinedFloat2>
            <userDefinedFloat3>637.912</userDefinedFloat3>
            <costMethod>A</costMethod>
            <lotSelectionMethod>FIFO</lotSelectionMethod>
            <transactionCode>BUY</transactionCode>
            <factor>663.219</factor>
            <netAmountLocal>839.853</netAmountLocal>
            <netAmountBase>720.808</netAmountBase>
            <principalB>260.116</principalB>
            <quantityPre>925.137</quantityPre>
            <tradeYield>403.015</tradeYield>
            <dirtyPrincipalLocal>755.67</dirtyPrincipalLocal>
            <dirtyPrincipalBase>657.47</dirtyPrincipalBase>
            <estimateVsFinal>FIN</estimateVsFinal>
            <reportingReplayInd>N</reportingReplayInd>
            <cashEventType>RECPT</cashEventType>
            <transactionNotes>BUY</transactionNotes>
            <partflag>Y</partflag>
            <scrubType>A</scrubType>
            <glAccount>GL_ACCOUNT</glAccount>
            <custodianBankCode>CODE</custodianBankCode>
            <custodyBankAccountNum>ACCOUNT</custodyBankAccountNum>
            <custodyBankSubAccount>SUB_ACCT</custodyBankSubAccount>
            <corporateActionType>EXCHANGE OFFER</corporateActionType>
            <cashEventSpawned>Y</cashEventSpawned>
            <bankAlias>242756</bankAlias>
            <changedflag>C</changedflag>
            <eventId>TSTR7986G</eventId>
            <corporateActionInstance>108366</corporateActionInstance>
            <sellToBuyFXRate>784.373</sellToBuyFXRate>
            <lotNumber>LOTN</lotNumber>
            <masterTicketNumber>A4F68JGE54GH1</masterTicketNumber>
            <acqTransNumber>397099</acqTransNumber>
            <postType>TYPE</postType>
            <tradeMemo1>TRADE_MEMO1</tradeMemo1>
            <tradeMemo2>TRADE_MEMO2</tradeMemo2>
            <fxRate>214.05</fxRate>
            <checkedDate>2015-02-18</checkedDate>
            <instructedDate>2015-02-25T06:02:47</instructedDate>
            <dispInterestReceived>124.951</dispInterestReceived>
            <userWhoApprovedTrade>USER</userWhoApprovedTrade>
            <prepaySpeed>719.425</prepaySpeed>
            <settlementExchangeRate>246.113</settlementExchangeRate>
            <touchCount>642886</touchCount>
            <authorizedDate>2015-02-25T06:02:47</authorizedDate>
            <localCommissionAmount>784.026</localCommissionAmount>
            <salesPerson>SALES_PERSON</salesPerson>
            <broker>BROKER</broker>
            <settlementCurrency>USD</settlementCurrency>
            <discountRate>859.935</discountRate>
            <comments1>COMMENTS1</comments1>
            <price>429.023</price>
            <principal>438.916</principal>
            <comments>COMMENTS</comments>
            <prepared>PREPARED</prepared>
            <preparedDate>2015-02-25T06:02:47</preparedDate>
            <settlementLocation>LOCATION</settlementLocation>
            <trader>TRADER</trader>
            <yield>268.127</yield>
            <fromQuantity>825.597</fromQuantity>
            <sellToBaseFxRate>113.427</sellToBaseFxRate>
            <toQuantity>549.864</toQuantity>
            <toAmountLocal>804.93</toAmountLocal>
            <toCashType>CASH_TYPE</toCashType>
            <fromAmountLocal>971.145</fromAmountLocal>
            <fromCashType>CASH_TYPE</fromCashType>
            <settlementInstructions1>SETTLEMENT_INSTRUCTIONS1</settlementInstructions1>
            <settlementInstructions2>SETTLEMENT_INSTRUCTIONS2</settlementInstructions2>
            <settlementInstructions3>SETTLEMENT_INSTRUCTIONS3</settlementInstructions3>
            <settlementInstructions4>SETTLEMENT_INSTRUCTIONS4</settlementInstructions4>
            <strategy>STRATEGY</strategy>
            <settlementDate>2015-02-18</settlementDate>
            <tradeExchangeRate>412.665</tradeExchangeRate>
            <postDate>2015-02-18</postDate>
            <custodianBankDesc>CUSTODIAN_BANK_DESC</custodianBankDesc>
            <openCostLocal>323.91</openCostLocal>
            <tradeAmount>792.136</tradeAmount>
            <smInactiveFlag>N</smInactiveFlag>
            <clientId>TSTR210A</clientId>
            <covFlag>N</covFlag>
            <eventSubPriority>800009</eventSubPriority>
            <ioIssuerAlias>IO_ISSUER_ALIAS</ioIssuerAlias>
            <ioIssuerId>TSTR820A</ioIssuerId>
            <origAcctDate>2015-02-18</origAcctDate>
            <origMonthlyAcctDate>2015-02-18</origMonthlyAcctDate>
            <pendingTradeId>841918</pendingTradeId>
            <qtyWashSaleEligible>890.105</qtyWashSaleEligible>
            <washSaleEligibleInd>N</washSaleEligibleInd>
            <aiaNetB>336.63</aiaNetB>
            <aiaNetL>329.022</aiaNetL>
            <buySellFlag>BUY</buySellFlag>
            <bookValue>800.295</bookValue>
            <bookValueLocal>134.357</bookValueLocal>
            <cpNetB>337.575</cpNetB>
            <cpNetL>539.859</cpNetL>
            <creditLossB>147.784</creditLossB>
            <creditLossL>701.405</creditLossL>
            <costBasisType>F</costBasisType>
            <futureFeeNetB>118.93</futureFeeNetB>
            <futureFeeNetL>221.959</futureFeeNetL>
            <impairmentLtdBase>429.515</impairmentLtdBase>
            <impairmentLtdLocal>423.229</impairmentLtdLocal>
            <mbaNetB>547.444</mbaNetB>
            <mbaNetL>700.545</mbaNetL>
            <naiaNetB>649.861</naiaNetB>
            <naiaNetL>657.304</naiaNetL>
            <nonCreditLossB>381.697</nonCreditLossB>
            <nonCreditLossL>187.558</nonCreditLossL>
            <bookCost>795.18</bookCost>
            <cashCategory>CASH_CATEGORY</cashCategory>
            <cancelType>C</cancelType>
            <exchangeFeeBase>430.715</exchangeFeeBase>
            <federalTaxWithholdBase>179.171</federalTaxWithholdBase>
            <mttype>MTCLOSE</mttype>
            <netCash>144.692</netCash>
            <origAcqDate>2015-02-18</origAcqDate>
            <positionId>536730</positionId>
            <scrubId>903071</scrubId>
            <secFeesBase>340.802</secFeesBase>
            <termLoanFeeExpB>880.006</termLoanFeeExpB>
            <termLoanFeeExpL>239.702</termLoanFeeExpL>
            <termLoanFeeIncB>483.79</termLoanFeeIncB>
            <termLoanFeeIncL>594.268</termLoanFeeIncL>
            <targetEventId>TSTR821A</targetEventId>
            <updateUserId>TSTR8210A</updateUserId>
            <accruedInterestOverride>ACCRUED_INTEREST_OVR</accruedInterestOverride>
            <acquisitionType>C</acquisitionType>
            <amortizedCostPrevBase>250.703</amortizedCostPrevBase>
            <amortizedCostPrevLocal>206.852</amortizedCostPrevLocal>
            <alluprate>168.707</alluprate>
            <asOfTradeTime>AS_OF_TRADE_TIME</asOfTradeTime>
            <authorized>AUTHORIZED</authorized>
            <autoexTradeFlag>FLAG</autoexTradeFlag>
            <baseCommissionAmount>372.662</baseCommissionAmount>
            <batchInstance>405902</batchInstance>
            <balance>734.073</balance>
            <bnchYld1>150.879</bnchYld1>
            <bnchYld2>955.763</bnchYld2>
            <bloombergProduct>PRODUCT</bloombergProduct>
            <buyToBaseFxRate>305.344</buyToBaseFxRate>
            <calcYieldIntIndicator>143323</calcYieldIntIndicator>
            <cancelDueToCorrection>CANC</cancelDueToCorrection>
            <cashflowdate>2015-02-18</cashflowdate>
            <checked>CHECKED</checked>
            <clearingBroker>CLEARING_BROKER</clearingBroker>
            <closeoutpl0>100.526</closeoutpl0>
            <closeoutpl1>334.342</closeoutpl1>
            <commentType>TYPE</commentType>
            <compared>COMPARED</compared>
            <compareddate>2015-02-18</compareddate>
            <costPrevBase>388.004</costPrevBase>
            <costPrevLocal>179.697</costPrevLocal>
            <cleanPrice>245.801</cleanPrice>
            <commissionCode>CODE</commissionCode>
            <commissionReasonCode>REASON</commissionReasonCode>
            <commissionTypeCode>NO</commissionTypeCode>
            <customerAcctNumb>CUSTOMER_ACCT_NUMB</customerAcctNumb>
            <dispBookValueGaap>240.85</dispBookValueGaap>
            <dispBookValueStat>596.497</dispBookValueStat>
            <disallowGainB>680.534</disallowGainB>
            <disallowGainL>957.888</disallowGainL>
            <disallowLossB>751.265</disallowLossB>
            <disallowLossL>474.507</disallowLossL>
            <dispAccruedIncome>197.591</dispAccruedIncome>
            <electedShareAmt>149.022</electedShareAmt>
            <electedSharePerc>968.775</electedSharePerc>
            <exchangeCodeTrd>CODE_TRD</exchangeCodeTrd>
            <exchangeCurrency>USD</exchangeCurrency>
            <exchangeTypeCode>TYPE_COD</exchangeTypeCode>
            <exchangeCode>CODE</exchangeCode>
            <exchangeFee>285.617</exchangeFee>
            <fairMarketValueBase>598.319</fairMarketValueBase>
            <fairMarketValueFlag>N</fairMarketValueFlag>
            <fairMarketValueLocal>830.836</fairMarketValueLocal>
            <fairMarketValuePrice>421.428</fairMarketValuePrice>
            <feeamt>749.022</feeamt>
            <feerate>851.654</feerate>
            <fromEffectiveDate>2015-02-18</fromEffectiveDate>
            <fwdCleanPrice>404.101</fwdCleanPrice>
            <gaapRealizedGl>935.651</gaapRealizedGl>
            <gaapSecType>TYP</gaapSecType>
            <goodMillionNum>710003</goodMillionNum>
            <haircutFlag>Y</haircutFlag>
            <haircutPct>669.725</haircutPct>
            <holdTradeStatus>A</holdTradeStatus>
            <identifier2>IDENTIFIER2</identifier2>
            <impairmentAmortOffsetB>352.868</impairmentAmortOffsetB>
            <impairmentAmortOffsetL>579.734</impairmentAmortOffsetL>
            <impairmentCostOffsetB>789.035</impairmentCostOffsetB>
            <impairmentCostOffsetL>513.376</impairmentCostOffsetL>
            <impairmentCostPrevB>537.023</impairmentCostPrevB>
            <impairmentCostPrevL>789.561</impairmentCostPrevL>
            <inceptionamt>747.719</inceptionamt>
            <instructed>INSTRUCTED</instructed>
            <interest>825.027</interest>
            <invertFlag>N</invertFlag>
            <interestadj>783.224</interestadj>
            <interestAdjBase>807.486</interestAdjBase>
            <ledgerEffectiveDate>2015-02-18</ledgerEffectiveDate>
            <ledgerDate>2015-02-18</ledgerDate>
            <letterconfirm>LETTERCONF</letterconfirm>
            <lettered>LETTERED</lettered>
            <lettereddate>2015-02-18</lettereddate>
            <lmtGlElg>N</lmtGlElg>
            <lmtGlInd>N</lmtGlInd>
            <margin>171.691</margin>
            <marginpoints>838.041</marginpoints>
            <mortgageFlag>Y</mortgageFlag>
            <muniFlag>Y</muniFlag>
            <netsettleamt>403.984</netsettleamt>
            <navId>795930</navId>
            <numberOfAccuredDays>969.215</numberOfAccuredDays>
            <netCashLocal>140.699</netCashLocal>
            <notionalamt>807.487</notionalamt>
            <notionalflag>N</notionalflag>
            <otherFee>118.238</otherFee>
            <origTranNum>ORIG_TRAN_NUM</origTranNum>
            <ownershipTransferDate>2015-02-18</ownershipTransferDate>
            <oidYield>923.44</oidYield>
            <otherFeeBase>993.105</otherFeeBase>
            <paceTradeEventId>TS210</paceTradeEventId>
            <positionIdTrd>279546</positionIdTrd>
            <productSubType>SUB_TYPE</productSubType>
            <poolNumber>POOL_NUMBER</poolNumber>
            <priceCurrency>USD</priceCurrency>
            <principalFactor>605.933</principalFactor>
            <realizedGlBase>632.601</realizedGlBase>
            <realizedGlCrB>215.198</realizedGlCrB>
            <realizedGlCrL>807.922</realizedGlCrL>
            <realizedGlLocal>256.571</realizedGlLocal>
            <realizedGlNcrB>698.889</realizedGlNcrB>
            <realizedGlNcrL>974.756</realizedGlNcrL>
            <recdPayAcct1>RECD_PAY_ACCT1</recdPayAcct1>
            <recdPayAcct2>RECD_PAY_ACCT2</recdPayAcct2>
            <recdPayEventType>RECD_PAY_EVENT_TYPE</recdPayEventType>
            <recdPayPostingPlanType>RECD_PAY_POSTING_PLAN_TYPE</recdPayPostingPlanType>
            <refrate>736.305</refrate>
            <repoRate>487.926</repoRate>
            <retailFeed>RETAIL_FEED</retailFeed>
            <salesPersonLogin>SALES_PERSON_LOGIN</salesPersonLogin>
            <secidType>SECID_TYPE</secidType>
            <sequencedate>2015-02-18</sequencedate>
            <settleamt0>488.133</settleamt0>
            <settleamt1>273.329</settleamt1>
            <settled>SETTLED</settled>
            <settleAmtAtMaturity>191.454</settleAmtAtMaturity>
            <settlementMethod>METHOD</settlementMethod>
            <shortTermId>998357</shortTermId>
            <sourceSystem>S</sourceSystem>
            <systemTradeTime>SYSTEM_TRADE_TIME</systemTradeTime>
            <sentTradeStatus>A</sentTradeStatus>
            <starTag151>STAR_TAG151</starTag151>
            <starTag182>STAR_TAG182</starTag182>
            <stateTaxWithholdBase>874.679</stateTaxWithholdBase>
            <starLsInd>L</starLsInd>
            <targetPurchaseDate>2015-02-18</targetPurchaseDate>
            <targetPurchasePrice>984.014</targetPurchasePrice>
            <targetTradeTicketNumber>TARGET_TRADE_TICKET_NUMBER</targetTradeTicketNumber>
            <taxesBase>626.687</taxesBase>
            <timeOfTrade>261.29</timeOfTrade>
            <toFxRate>650.206</toFxRate>
            <toLocalCommissionAmt>422.618</toLocalCommissionAmt>
            <toSettleToBaseFxRate>144.472</toSettleToBaseFxRate>
            <toSettlementCurrency>EUR</toSettlementCurrency>
            <transactionId>TSTR8210A</transactionId>
            <turnaroundIndicator>N</turnaroundIndicator>
            <totalGainLocal>162.71</totalGainLocal>
            <totalAmortLocal>528.312</totalAmortLocal>
            <totalAmortBase>137.178</totalAmortBase>
            <totalLossLocal>593.233</totalLossLocal>
            <tdUserChar1>TD_USER1</tdUserChar1>
            <tdUserChar2>TD_USER_CHAR2</tdUserChar2>
            <tdUserDate1>2015-02-18</tdUserDate1>
            <tdUserDate2>2015-02-18</tdUserDate2>
            <tdUserInt1>279644</tdUserInt1>
            <tdUserInt2>702491</tdUserInt2>
            <tdUserInt3>801944</tdUserInt3>
            <tdUserInt4>882783</tdUserInt4>
            <tdUserInt5>313103</tdUserInt5>
            <tdUserInt6>802727</tdUserInt6>
            <tdUserInt7>706385</tdUserInt7>
            <tdUserInt8>665022</tdUserInt8>
            <tradeMemo3>TRADE_MEMO3</tradeMemo3>
            <tradeMemo4>TRADE_MEMO4</tradeMemo4>
            <tradeMemo5>CANCEL</tradeMemo5>
            <thirdCurrencyFx>768.861</thirdCurrencyFx>
            <timeOfSalesTicket>TIME_OF_SALES_TICKET</timeOfSalesTicket>
            <timeOfSlate>TIME_OF_SLATE</timeOfSlate>
            <tradeChar1>TRADE_CHAR1</tradeChar1>
            <tradeFlat>Y</tradeFlat>
            <tradersLogin>LOGIN</tradersLogin>
            <transcost4Amount>522.328</transcost4Amount>
            <transcost4Code>CODE4</transcost4Code>
            <transcost4Currency>EUR</transcost4Currency>
            <transcost4TypeCode>TYPE5</transcost4TypeCode>
            <transcost5Amount>602.724</transcost5Amount>
            <transcost5Code>CODE5</transcost5Code>
            <transcost5Currency>USD</transcost5Currency>
            <transcost5TypeCode>TYPE5</transcost5TypeCode>
            <updateSourceTrd>MCADMIN</updateSourceTrd>
            <underlyingAccrIntB>395.549</underlyingAccrIntB>
            <underlyingAccrIntL>597.585</underlyingAccrIntL>
            <underlyingAccrIntLMat>166.897</underlyingAccrIntLMat>
            <withholdCode>W_CODE</withholdCode>
            <withholdCurrency>EUR</withholdCurrency>
            <withholdTypeCode>CODE</withholdTypeCode>
            <workoutDate>2015-02-18</workoutDate>
            <workoutPrice>632.133</workoutPrice>
            <withholdingTax>642.057</withholdingTax>
            <batchEventId>TSTR825</batchEventId>
            <secFee>618.734</secFee>
            <tradeDate>2015-02-18</tradeDate>
            <recordType>TYPE</recordType>
            <linkEventId>TSTR820A</linkEventId>
            <orderNumber>ORDER_NUMBER</orderNumber>
            <priceMethod>PRIC</priceMethod>
            <regulatoryIntent>REGULATORY_INTENT</regulatoryIntent>
            <holdingTerm>HOLDING_TERM</holdingTerm>
            <income>376.597</income>
            <incomeAmountBase>609.432</incomeAmountBase>
            <consolidatedIndicator>Y</consolidatedIndicator>
            <fromRegulatoryCategory>FROM</fromRegulatoryCategory>
            <toRegulatoryCategory>TO</toRegulatoryCategory>			
            <deskAka>DESK_AKA</deskAka>
            <deskType>DESK_TYPE</deskType>
            <desk>DESK</desk>
            <exSubBrokerId>TSTR820A</exSubBrokerId>
            <placementNum>PLACEMENT_NUM</placementNum>
            <subBrokerCode>SUB_BROKER_CODE</subBrokerCode>
            <tradeInterfaceInfo>TRADE_INTERFACE_INFO</tradeInterfaceInfo>
            <trdBrokerAka>TRD_BROKER_AKA</trdBrokerAka>
            <trdDirtyPrice>192.4</trdDirtyPrice>
            <trdExBrokerDesk>TRD_EX_BROKER_DESK</trdExBrokerDesk>
            <trdExDeskType>TRD_EX_DESK_TYPE</trdExDeskType>
            <trdExternalAccountLoc>TRD_EXTERNAL_ACCOUNT_LOC</trdExternalAccountLoc>
            <trdLocation>TRD_LOCATION</trdLocation>
            <trdSeriesNum>TRD_SERIES_NUM</trdSeriesNum>
            <trdVersion>TRD_VERSION</trdVersion>
            <trdExtidId1>TRD_EXTID_ID1</trdExtidId1>
            <trdExtidId2>TRD_EXTID_ID2</trdExtidId2>
            <tbaInvnum>668167</tbaInvnum>
            <tbaTrdDate>2018-07-10</tbaTrdDate>
            <collatExposureType>COLLAT_EXPOSURE_TYPE</collatExposureType>
            <effTermDate>2018-07-10</effTermDate>
            <contractRef>CONTRACT_REF</contractRef>
            <trdExternalId1Type>TRD_EXTERNAL_ID1_TYPE</trdExternalId1Type>
            <trdExternalId2Type>TRD_EXTERNAL_ID2_TYPE</trdExternalId2Type>
            <mifidTradeTime>2018-07-10</mifidTradeTime>
            <mifidOrderType>MIFID_ORDER_TYPE</mifidOrderType>
            <mifidTradingVenue>MIFID_TRADING_VENUE</mifidTradingVenue>
            <newIssueIndicator>Y</newIssueIndicator>
            <dealSource>DEAL_SOURCE</dealSource>
            <stGainSecLocal>148.369</stGainSecLocal>
            <stLossSecLocal>456.227</stLossSecLocal>
            <stGainSecBase>646.337</stGainSecBase>
            <stLossSecBase>798.334</stLossSecBase>
            <stGain>451.258</stGain>
            <stLoss>875.318</stLoss>
            <stGainCurrency>468.175</stGainCurrency>
            <stLossCurrency>436.665</stLossCurrency>
            <ltGainSecLocal>693.788</ltGainSecLocal>
            <ltLossSecLocal>864.475</ltLossSecLocal>
            <ltGainSecBase>757.379</ltGainSecBase>
            <ltLossSecBase>661.172</ltLossSecBase>
            <ltGain>147.234</ltGain>
            <ltLoss>536.238</ltLoss>
            <ltGainCurrency>463.378</ltGainCurrency>
            <ltLossCurrency>536.328</ltLossCurrency>
            <clsAmortLocal>559.331</clsAmortLocal>
            <clsAmortBase>116.219</clsAmortBase>
            <clsOidLocal>746.219</clsOidLocal>
            <clsOidBase>448.339</clsOidBase>
            <clsIlbIncLtdLocal>445.559</clsIlbIncLtdLocal>
            <clsIlbIncLtdBase>465.309</clsIlbIncLtdBase>
            <gainCurrency988>102.038</gainCurrency988>
            <lossCurrency988>790.264</lossCurrency988>
            <gainLossCurrency988>450.311</gainLossCurrency988>
            <washsaleDisallowedReversalLocal>145.57</washsaleDisallowedReversalLocal>
            <washsaleDisallowedReversalBase>780.296</washsaleDisallowedReversalBase>
            <impairmentLtdLocalExt>701.279</impairmentLtdLocalExt>
            <impairmentLtdBaseExt>943.342</impairmentLtdBaseExt>
            <impairmentFxLtd>108.209</impairmentFxLtd>
            <recPayAcctSecondary>REC_PAY_ACCT_SECONDARY</recPayAcctSecondary>
            <recPayAcct2Secondary>REC_PAY_ACCT2_SECONDARY</recPayAcct2Secondary>
            <nonCreditLedgerDebit>NON_CREDIT_LEDGER_DEBIT</nonCreditLedgerDebit>
            <nonCreditLedgerCredit>NON_CREDIT_LEDGER_CREDIT</nonCreditLedgerCredit>
            <ociLedgerDebit>OCI_LEDGER_DEBIT</ociLedgerDebit>
            <ociLedgerCredit>OCI_LEDGER_CREDIT</ociLedgerCredit>
            <fxImpairLedgerDebit>FX_IMPAIR_LEDGER_DEBIT</fxImpairLedgerDebit>
            <fxImpairLedgerCredit>FX_IMPAIR_LEDGER_CREDIT</fxImpairLedgerCredit>
            <breakageAmountLocal>476.389</breakageAmountLocal>
            <breakageAmountBase>736.492</breakageAmountBase>
            <trdDetExtUserFloat1>153.756</trdDetExtUserFloat1>
            <trdDetExtUserFloat2>454.186</trdDetExtUserFloat2>
            <trdDetExtUserFloat3>775.312</trdDetExtUserFloat3>
            <trdDetExtUserFloat4>451.228</trdDetExtUserFloat4>
            <trdDetExtUserFloat5>841.279</trdDetExtUserFloat5>
            <trdDetExtUserFloat6>540.314</trdDetExtUserFloat6>
            <trdDetExtUserFloat7>447.105</trdDetExtUserFloat7>
            <trdDetExtUserFloat8>442.338</trdDetExtUserFloat8>
            <trdDetExtUserFloat9>104.289</trdDetExtUserFloat9>
            <trdDetExtUserFloat10>445.316</trdDetExtUserFloat10>
            <trdDetExtUserFloat11>525.348</trdDetExtUserFloat11>
            <trdDetExtUserFloat12>477.229</trdDetExtUserFloat12>
            <trdDetExtUserFloat13>114.236</trdDetExtUserFloat13>
            <trdDetExtUserFloat14>685.236</trdDetExtUserFloat14>
            <trdDetExtUserFloat15>775.124</trdDetExtUserFloat15>
            <trdDetExtUserFloat16>485.236</trdDetExtUserFloat16>
            <trdDetExtUserFloat17>443.997</trdDetExtUserFloat17>
            <trdDetExtUserFloat18>108.34</trdDetExtUserFloat18>
            <trdDetExtUserFloat19>455.234</trdDetExtUserFloat19>
            <trdDetExtUserFloat20>558.364</trdDetExtUserFloat20>
            <trdDetExtUserFloat21>775.216</trdDetExtUserFloat21>
            <trdDetExtUserFloat22>554.375</trdDetExtUserFloat22>
            <trdDetExtUserFloat23>546.345</trdDetExtUserFloat23>
            <trdDetExtUserFloat24>854.326</trdDetExtUserFloat24>
            <trdDetExtUserFloat25>883.194</trdDetExtUserFloat25>
            <trdDetExtUserFloat26>738.234</trdDetExtUserFloat26>
            <trdDetExtUserFloat27>551.379</trdDetExtUserFloat27>
            <trdDetExtUserFloat28>497.364</trdDetExtUserFloat28>
            <trdDetExtUserFloat29>546.33</trdDetExtUserFloat29>
            <trdDetExtUserFloat30>507.068</trdDetExtUserFloat30>
            <trdDetExtUserChar1>TRD_DET_EXT_USER_CHAR1</trdDetExtUserChar1>
            <trdDetExtUserChar2>TRD_DET_EXT_USER_CHAR2</trdDetExtUserChar2>
            <trdDetExtUserChar3>TRD_DET_EXT_USER_CHAR3</trdDetExtUserChar3>
            <trdDetExtUserChar4>TRD_DET_EXT_USER_CHAR4</trdDetExtUserChar4>
            <trdDetExtUserChar5>TRD_DET_EXT_USER_CHAR5</trdDetExtUserChar5>
            <trdDetExtUserChar6>TRD_DET_EXT_USER_CHAR6</trdDetExtUserChar6>
            <trdDetExtUserChar7>TRD_DET_EXT_USER_CHAR7</trdDetExtUserChar7>
            <trdDetExtUserChar8>TRD_DET_EXT_USER_CHAR8</trdDetExtUserChar8>
            <trdDetExtUserChar9>TRD_DET_EXT_USER_CHAR9</trdDetExtUserChar9>
            <trdDetExtUserChar10>TRD_DET_EXT_USER_CHAR10</trdDetExtUserChar10>
            <trdDetExtUserChar11>TRD_DET_EXT_USER_CHAR11</trdDetExtUserChar11>
            <trdDetExtUserChar12>TRD_DET_EXT_USER_CHAR12</trdDetExtUserChar12>
            <trdDetExtUserChar13>TRD_DET_EXT_USER_CHAR13</trdDetExtUserChar13>
            <trdDetExtUserChar14>TRD_DET_EXT_USER_CHAR14</trdDetExtUserChar14>
            <trdDetExtUserChar15>TRD_DET_EXT_USER_CHAR15</trdDetExtUserChar15>
            <trdDetExtUserChar16>TRD_DET_EXT_USER_CHAR16</trdDetExtUserChar16>
            <trdDetExtUserChar17>TRD_DET_EXT_USER_CHAR17</trdDetExtUserChar17>
            <trdDetExtUserChar18>TRD_DET_EXT_USER_CHAR18</trdDetExtUserChar18>
            <trdDetExtUserChar19>TRD_DET_EXT_USER_CHAR19</trdDetExtUserChar19>
            <trdDetExtUserChar20>TRD_DET_EXT_USER_CHAR20</trdDetExtUserChar20>
            <trdDetExtUserChar21>TRD_DET_EXT_USER_CHAR21</trdDetExtUserChar21>
            <trdDetExtUserChar22>TRD_DET_EXT_USER_CHAR22</trdDetExtUserChar22>
            <trdDetExtUserChar23>TRD_DET_EXT_USER_CHAR23</trdDetExtUserChar23>
            <trdDetExtUserChar24>TRD_DET_EXT_USER_CHAR24</trdDetExtUserChar24>
            <trdDetExtUserChar25>TRD_DET_EXT_USER_CHAR25</trdDetExtUserChar25>
            <trdDetExtUserChar26>TRD_DET_EXT_USER_CHAR26</trdDetExtUserChar26>
            <trdDetExtUserChar27>TRD_DET_EXT_USER_CHAR27</trdDetExtUserChar27>
            <trdDetExtUserChar28>TRD_DET_EXT_USER_CHAR28</trdDetExtUserChar28>
            <trdDetExtUserChar29>TRD_DET_EXT_USER_CHAR29</trdDetExtUserChar29>
            <trdDetExtUserChar30>TRD_DET_EXT_USER_CHAR30</trdDetExtUserChar30>
            <trdDetExtUserDate1>2018-07-10</trdDetExtUserDate1>
            <trdDetExtUserDate2>2018-07-10</trdDetExtUserDate2>
            <trdDetExtUserDate3>2018-07-10</trdDetExtUserDate3>
            <trdDetExtUserDate4>2018-07-10</trdDetExtUserDate4>
            <trdDetExtUserDate5>2018-07-10</trdDetExtUserDate5>
            <trdDetExtUserDate6>2018-07-10</trdDetExtUserDate6>
            <trdDetExtUserDate7>2018-07-10</trdDetExtUserDate7>
            <trdDetExtUserDate8>2018-07-10</trdDetExtUserDate8>
            <trdDetExtUserDate9>2018-07-10</trdDetExtUserDate9>
            <trdDetExtUserDate10>2018-07-10</trdDetExtUserDate10>
            <impairReasonCode>IMPAIR_REASON_CODE</impairReasonCode>
            <cancelPostDate>2020-06-18</cancelPostDate>
            <cancelReasonIndicator>CANCEL REASON</cancelReasonIndicator>
            <commitmentAmount>10.1</commitmentAmount>
            <distributedInvestmentIncomeLocal>10.2</distributedInvestmentIncomeLocal>
            <distributedInvestmentIncomeBase>10.3</distributedInvestmentIncomeBase>
            <expectedCreditLossAmountLocal>10.4</expectedCreditLossAmountLocal>
            <expectedCreditLossAmountBase>10.5</expectedCreditLossAmountBase>
            <expectedCreditLossDeltaAmountLocal>10.6</expectedCreditLossDeltaAmountLocal>
            <expectedCreditLossDeltaAmountBase>10.7</expectedCreditLossDeltaAmountBase>
            <expectedCreditLossPreviousAmountLocal>10.8</expectedCreditLossPreviousAmountLocal>
            <expectedCreditLossPreviousAmountBase>10.9</expectedCreditLossPreviousAmountBase>
            <expectedCreditLossStageAmount>11</expectedCreditLossStageAmount>
            <expectedCreditLossPreviousStageAmount>11</expectedCreditLossPreviousStageAmount>
            <equityMethodAcctCostIndicator>EMA</equityMethodAcctCostIndicator>
            <equityMethodAcctTreatmentMethod>C</equityMethodAcctTreatmentMethod>
            <fxImpairmentRate>11.4</fxImpairmentRate>
            <equityMethodAcctCRCCTGainBase>11.5</equityMethodAcctCRCCTGainBase>
            <currentPeriodCurrencyGain>11.6</currentPeriodCurrencyGain>
            <currencyIncomeGain>11.7</currencyIncomeGain>
            <termGainLoss>6</termGainLoss>
            <currentPeriodSecurityGainBase>11.9</currentPeriodSecurityGainBase>
            <currentPeriodSecurityGainLocal>12</currentPeriodSecurityGainLocal>
            <equityMethodAcctSecurityGainBase>12.1</equityMethodAcctSecurityGainBase>
            <equityMethodAcctSecurityGainLocal>12.2</equityMethodAcctSecurityGainLocal>
            <equityMethodAcctCurrencyLossBase>12.4</equityMethodAcctCurrencyLossBase>
            <currentPeriodCurrencyLoss>12.5</currentPeriodCurrencyLoss>
            <currencyIncomeLoss>12.6</currencyIncomeLoss>
            <currentPeriodSecurityLossBase>12.7</currentPeriodSecurityLossBase>
            <currentPeriodSecurityLossLocal>12.8</currentPeriodSecurityLossLocal>
            <equityMethodAcctSecurityLossBase>12.9</equityMethodAcctSecurityLossBase>
            <equityMethodAcctSecurityLossLocal>13</equityMethodAcctSecurityLossLocal>
            <equityMethodAcctMarketValueBase>13.1</equityMethodAcctMarketValueBase>
            <equityMethodAcctMarketValueLocal>13.2</equityMethodAcctMarketValueLocal>
            <notionalOrUnitsIndicator>D</notionalOrUnitsIndicator>
            <ownershipPercentage>13.6</ownershipPercentage>
            <cashFlowEffectiveDate>2020-06-18</cashFlowEffectiveDate>
            <equityMethodAcctPeriodMarketValueLocal>13.8</equityMethodAcctPeriodMarketValueLocal>
            <equityMethodAcctPeriodMarketValueBase>13.9</equityMethodAcctPeriodMarketValueBase>
            <prorataCreditNonCreditLossFlag>Y</prorataCreditNonCreditLossFlag>
            <purchasedImpairedIndicator>F</purchasedImpairedIndicator>
            <researchFeeLocal>14.2</researchFeeLocal>
            <researchFeeBase>14.3</researchFeeBase>
            <sleeveId>L</sleeveId>
            <srcSysC>M</srcSysC>
            <starSourceLevel>G</starSourceLevel>
            <technicalShortLevel>H</technicalShortLevel>
            <undistributedInvestmentIncomeBase>14.4</undistributedInvestmentIncomeBase>
            <undistributedInvestmentIncomeLocal>14.5</undistributedInvestmentIncomeLocal>
            <unrealizedUndistributedInvestmentIncomeBase>14.6</unrealizedUndistributedInvestmentIncomeBase>
            <unrealizedUndistributedInvestmentIncomeLocal>14.7</unrealizedUndistributedInvestmentIncomeLocal>
            <settleAmountBase>312.123</settleAmountBase>
        </warehouseTrade>
    </warehouseTransaction>
</EagleML>


Refer to the data map table for Warehouse Trade object

CSV Processing Sequence

Warehouse Trade is loaded to DB with the following streams:

  • eagle_default_in_csv_warehouse - this is PRF stream which consist of two steps transform and load
  • eagle_default_in_csv_warehouse_t - transformation CSV to EagleML
    • trade.xml ​ rule
  • eagle_ml-2-0_default_in_xml_warehouse_preproc
    • XSLT transformation. XSLT rule 'eagle_default/in/xml/xslt/wrh_egl_gen_interface3.xsl' is used.
    • Loading data into the database. The 'eagle_default/in/xml/xml-warehouse_v2.xml' rule is used

To load or update data in Warehouse Trade tables, send your CSV message to the eagle_default_in_csv_warehouse stream.

Required Elements

The following elements are required in the EagleML message: Effective Date, Entity Id (or Entity Identifiers), Source Name, Lot Number, identifiers to resolve Security Alias.

  • Effective Date: warehouseTransaction/warehouseTrade/effectiveDate – mapped to TRADESDBO.TRADE.EFFECTIVE_DATE
  • Entity Id: warehouseTransaction/warehouseTrade/entityId (or warehouseTransaction/warehouseTrade/xrefAccountId and warehouseTransaction/warehouseTrade/xrefAccountIdType) – mapped to TRADESDBO.TRADE.ENTITY_ID
  • Source Name: warehouseTransaction/warehouseTrade/sourceName
  • Lot Number: warehouseTransaction/warehouseTrade/lotNumber – mapped to TRADESDBO.TRADE.LOT_NUMBER
  • Master Ticket Number: warehouseTransaction/warehouseTrade/masterTicketNumber – mapped to TRADESDBO.TRADE.MASTER_TICKET_NUMBER
  • Security Identifiers for resolve the Security Alias (see the section 'Security Resolution Logic'), Security Alias mapped to TRADESDBO.TRADE.SECURITY_ALIAS

Security Resolution Logic

<securityAlias> should not be specified in the incoming EagleML message.

Read more on Security Resolution - Warehouse Objects

Entity Resolution Logic

You will find more details on Entity Resolution for Warehouse Objects

Modes of Data Loading 

The Warehouse Trade inbound interface has 2 modes for loading data into the DB table:

 Batch Mode

In the batch mode the multiple rows could be loaded into the database from the one EagleML message. In this mode the element warehouseTransaction should be contained several complex elements warehouseTrade. Data from every element will be loaded into the database row.

See the example of incoming EagleML message to load data in Batch Mode:

Batch Key Element

When using the batch mode, the data from the complex elements warehouseTrade should contain the same set of of element’s values: Entity Id + Effective Date + Source Name. The set of values for grouping should be specified in the element warehouseTransaction/batchKey.

The format of Batch Key (warehouseTransaction/batchKey element): entityId^effectiveDate^sourceName

  • entityId – the element warehouseTransaction/warehouseTrade/entityId. It should be specified if it mapped from the EagleML message directly (in the warehouseTransaction/warehouseTrade/entityId). If the value of entityId is defined by the warehouseTransaction/warehouseTrade/xrefAccountId and warehouseTransaction/warehouseTrade/xrefAccountIdType elements, the first part of warehouseTransaction/batchKey could be any. The batch key will be updated by calculated entityId element.
  • effectiveDate – the element warehouseTransaction/warehouseTrade/effectiveDate.  It should be specified in data format YYYYMMDD.
  • sourceName – the element warehouseTransaction/warehouseTrade/sourceName.

Examples of a valid Batch Key:

<batchKey>COMPTST1^20140120^EAGLE PACE</batchKey>
<batchKey>entity^20140120^EAGLE PACE</batchKey>

Insert/Update

The stored procedure for the batch mode supports only the inserting data into the database.

Use the single row mode for updating every database row.


 UPSERT Mode

The UPSERT mode is used if the element Batch Key (warehouseTransaction/batchKey element) is not present or null in the incoming EagleML message.

For the single row mode, the warehouseTransaction node can contains only one warehouseTrade complex element.

The set of values – key of record:

Security Alias + Source Name + Entity Id + Master Ticket Number + Event ID (star_tag25)

To insert new data the database set of specified above values should not exist. To update a data record with your message, you should make it up with the full database set of values determined.

Examples of use: