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).
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.
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
- 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 for 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.
More details about Security Resolution for Warehouse Objects
Security resolution logic for warehouse objects is carried out within the stored procedure PACE_MASTERDBO.ESTAR_STD_INTERFACES.RETURN_XREF_SECALIAS_GEN
The resolution of security is controlled by Batting Order. Batting Order works for Warehouse objects exactly in the same way as it does for for Reference objects.
Batting order determines the order of the reference identifiers for matching.
The default batting order contains all possible identifiers, which can be specified in an incoming message. This default value of batting order is used when batting order is not specified.
By default, the preset value of the of batting order is:
‘cusip,isin,sedol,reuters,bloombergId,sicovmId,valorenId,cedel,internalId,cinsId,ticker,xId1,xId2,xId3,xId4,xId5,xId6,xId7,xId8,xId9,xId10,uniqueProductId,uniqueSwapId’.
If you do not want to use the default batting order, you can change the it for all workflows using the Config Batting Order or for one individual workflow using the Specific Batting Order or to a single record using a Specific Batting Order on Message Node.
Config Batting Order (All Workflows)
You can specify a batting order for all workflows that differs from the default.
It is set in the custom configuration file eagle_ml-2-0_custom_cm/w_config_custom.inc.
To edit config batting order, open the custom configuration file in Message Center Editor and define the ‘CONFIG_BATTING_ORDER’ variable.
Specific Batting Order (Single Workflow)
Specific batting order is used to define the batting order for a single workflow.
It can be set inside the transform solution in IWS.
Specific batting order has higher priority than default and batting order for security resolution, but only for the workflow in which specific batting order is set.
To set a specific batting order, edit the ‘battingOrder’ field in Destination grid.
Click 
button near the function text box to apply changes. Save and Export the solution.
Specific Batting Order on Message Node (Single Message)
The specific batting order is used to define transformations that should occur for one record in the xml in the source file.
The specific batting order will have the higher priority relative to default and batting order for the security transformation, but only for the current record where specific batting order is set.
To use it, you should fill the batting order node of your message (refer to the data map table of the object and make sure the node is placed in agreement with the XSDs).
<battingOrder>sicovmId,valorenId,cedel</battingOrder>
For example:
<EagleML>
<header>
...
</header>
<referenceTransaction><!—this record will use the batting order specified below>
<header>
...
</header>
<genericIssueAnalytic>
...
<battingOrder>sicovmId,valorenId,cedel</battingOrder>
...
</genericIssueAnalytic>
</referenceTransaction>
<referenceTransaction><!—this record will use default batting order>
<header>
...
</header>
<genericIssueAnalytic>
...
</genericIssueAnalytic>
<referenceTransaction>
</EagleML>
Asset Resolution Options
Resolve Primary by Security Alias for Warehouse Trade Interface
RESOLVE_PRIMARY is used to resolve primaryAssetId; this can be useful to validate securityAlias
<assetResolutionOption>RESOLVE_PRIMARY</assetResolutionOption>
|
SKIP is used to use securityAlias passed from incoming data without resolution
<assetResolutionOption>SKIP</assetResolutionOption>
|
Both options allow to load trades via warehouseTrade object using only securityAlias.
Entity Resolution Logic
More details about Entity Resolution for Warehouse Objects
Warehouse Objects use entity resolution logic that is similar to the logic used by Reference and Entity Objects, but simplified.
The lookup used (eagle/star/pan-qentityxreference.htm) contains important elements around the code to implement the entity resolution logic:
There are 2 methods to specify the Entity Id element for Warehouse objects:
- by Entity Xref Identifiers and
- directly in entityId element of incoming EagleML message
Additional Details Around Resolving Entity Directly in entityId Element of Incoming EagleML Message
1. If the elements Xref Account Id and Xref Account Id Type are present in the incoming EagleML message:
In this case the value of Entity Id element is defined in the lookup by input parameters Xref Account Id (mapped Into RULESDBO.ENTITY_XREFERENCE.XREF_ACCOUNT_ID) and Xref Account Id Type (mapped Into RULESDBO.ENTITY_XREFERENCE.XREF_ACCOUNT_ID_TYPE).
The resulting value (mapped from the RULESDBO.ENTITY_XREFERENCE.ENTITY_ID) will be mapped into HOLDINGDBO.POSITION.ENTITY_ID database field.
An example of Entity Xreference Identifiers in the incoming message:
<EagleML>
<header>
...
</header>
<warehouseTransaction>
<header>
...
</header>
<warehousePosition>
...
<xrefAccountId>X</xrefAccountId>
<xrefAccountIdType>X</xrefAccountIdType>
...
</warehousePosition>
</warehouseTransaction>
</EagleML>
2. If the Xref Account Id and Xref Account Id Type are not present in the incoming EagleML message:
The element Entity Id should not be null. The mapping into HOLDINGDBO.POSITION.ENTITY_ID database field will be direct.
Entity Identifiers can be found in the ‘Entity Common Id Model’ XSD Group. Refer to the Entity Common Id Model mapping for more details.
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: