Overview
The Warehouse Close Lot inbound interface is designed to load and update data in the TRADESDBO.DISPOSAL_LOTS database table.
The format of incoming message: EagleML (WarehouseCloseLot complex type).
Stream Logic
EagleML Processing Sequence
Warehouse Close Lot 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 Close Lot 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
bind
<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^20150306^EAGLE PACE</batchKey>
<objectType>WarehouseCloseLot</objectType>
</header>
<warehouseCloseLot>
<objectType>WarehouseCloseLot</objectType>
<objectId>WarehouseCloseLot</objectId>
<sourceName>EAGLE PACE</sourceName>
<updateSource>MCADMIN</updateSource>
<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>
<transType>TRANSLOTS</transType>
<sweepDate>2018-07-10</sweepDate>
<cpStatus>PD</cpStatus>
<cpReason>CP_REASON</cpReason>
<splitRatio>465.278</splitRatio>
<cpDebt>798.224</cpDebt>
<cpSwapRatio>114.286</cpSwapRatio>
<cpTermFlag>CP_TERM_FLAG</cpTermFlag>
<cpShareTaxability>CP_SHARE_TAXABILITY</cpShareTaxability>
<cpCashTaxability>CP_CASH_TAXABILITY</cpCashTaxability>
<cpCash>760.156</cpCash>
<cpCallPx>738.472</cpCallPx>
<fromFmvPrice>309.597</fromFmvPrice>
<toFmvPrice>396.269</toFmvPrice>
<cpNewPx>874.753</cpNewPx>
<consentRt>523.514</consentRt>
<userGroup1>USER_GROUP1</userGroup1>
<userGroup3>USER_GROUP3</userGroup3>
<userGroup4>USER_GROUP4</userGroup4>
<userGroup5>USER_GROUP5</userGroup5>
<effectiveDate>2015-03-06</effectiveDate>
<closeOpenEventId>TSTR10001</closeOpenEventId>
<closeEventId>TST20A</closeEventId>
<longShortIndicator>L</longShortIndicator>
<lotSelectionMethod>HORIG</lotSelectionMethod>
<localCurrency>EUR</localCurrency>
<tradeDate>2015-03-13</tradeDate>
<ledgerEffectiveDate>2015-03-13</ledgerEffectiveDate>
<settlementDate>2015-03-13</settlementDate>
<transactionCode>BUYCANCEL</transactionCode>
<masterTicketNumber>A4F68JGE54GM1</masterTicketNumber>
<numberofLots>175402</numberofLots>
<exchangeCode>EX CODE</exchangeCode>
<postType>TYPE</postType>
<targetEventId>TSTR821</targetEventId>
<linkEventId>TS21A</linkEventId>
<postDate>2015-03-13</postDate>
<originalFaceValue>901.415</originalFaceValue>
<settlementLocation>LOCATION</settlementLocation>
<taxes>471.065</taxes>
<localCommissionAmount>240.095</localCommissionAmount>
<exchangeFee>872.783</exchangeFee>
<accruedInterest>102.767</accruedInterest>
<netCash>651.662</netCash>
<otherFee>346.971</otherFee>
<tradeStatus>STATUS</tradeStatus>
<tradeMemo2>TRADE_MEMO2</tradeMemo2>
<settlementCurrency>USD</settlementCurrency>
<settlementAmount>343.517</settlementAmount>
<settlement_exchange_rate>556.049</settlement_exchange_rate>
<fxRateOpen>242.189</fxRateOpen>
<fxRate>262.516</fxRate>
<brokerCode>EAGLE</brokerCode>
<unitCostBase>624.726</unitCostBase>
<costLocal>132.631</costLocal>
<costBase>694.041</costBase>
<unitOpenPrice>503.328</unitOpenPrice>
<unitCostLocal>567.779</unitCostLocal>
<price>830.174</price>
<tradeAmount>881.794</tradeAmount>
<settleAmount0>756.941</settleAmount0>
<closeHoldingPeriodDate>2015-03-13</closeHoldingPeriodDate>
<parOrShares>409.858</parOrShares>
<premiumAllocatedLocal>266.994</premiumAllocatedLocal>
<premiumAllocated>648.85</premiumAllocated>
<origAcqDate>2015-03-13</origAcqDate>
<gainLossTerm>G</gainLossTerm>
<openCostBasisType>C</openCostBasisType>
<closeCostBasisType>A</closeCostBasisType>
<tradeBaseAmount>876.885</tradeBaseAmount>
<amortLocal>342.864</amortLocal>
<amortBase>638.219</amortBase>
<clientId>T820A</clientId>
<feeAmt>399.189</feeAmt>
<principalFactor>673.728</principalFactor>
<tipIncomeLtdLocal>402.266</tipIncomeLtdLocal>
<tipIncomeLtdBase>684.22</tipIncomeLtdBase>
<bookValueLocal>727.79</bookValueLocal>
<bookValue>189.016</bookValue>
<monthEndDate>2015-03-13</monthEndDate>
<oidLocal>600.953</oidLocal>
<oidBase>379.453</oidBase>
<totalAmortLocal>435.988</totalAmortLocal>
<totalAmortBase>185.42</totalAmortBase>
<gainCurrency988>622.971</gainCurrency988>
<lossCurrency988>806.003</lossCurrency988>
<originalSourceCode>ORIGINAL_SOURCE_CODE</originalSourceCode>
<currencyGainInt>327.609</currencyGainInt>
<currencyLossInt>699.453</currencyLossInt>
<gainCurrency>919.532</gainCurrency>
<gainSecurityLocal>766.301</gainSecurityLocal>
<gainSecurity>732.085</gainSecurity>
<lossCurrency>613.574</lossCurrency>
<lossSecurityLocal>269.63</lossSecurityLocal>
<lossSecurity>299.865</lossSecurity>
<openCostLocal>357.716</openCostLocal>
<openCostBase>151.426</openCostBase>
<totalGain>870.772</totalGain>
<totalLoss>581.54</totalLoss>
<currBuiltinGlSec>232.386</currBuiltinGlSec>
<currBuiltinGlCurrency>433.59</currBuiltinGlCurrency>
<currBuiltinGlDeferred>372.392</currBuiltinGlDeferred>
<currBuiltinGlOid>475.25</currBuiltinGlOid>
<currBuiltinGlMktDisc>971.809</currBuiltinGlMktDisc>
<acquisitionType>A</acquisitionType>
<allocProceedsBase>369.44</allocProceedsBase>
<allocProceedsLocal>876.1</allocProceedsLocal>
<allUpRate>284.965</allUpRate>
<amortizationBase>857.135</amortizationBase>
<amortizationLocal>192.333</amortizationLocal>
<balance>869.185</balance>
<baseCommissionAmount>584.926</baseCommissionAmount>
<baseCostLot>195.316</baseCostLot>
<baseRealizedGlCurr>470.139</baseRealizedGlCurr>
<baseRealizedGlMkt>778.345</baseRealizedGlMkt>
<baseInterestAdj>445.27</baseInterestAdj>
<bnchYld1>469.525</bnchYld1>
<bnchYld2>401.317</bnchYld2>
<buyLotNbr>BUY LOT</buyLotNbr>
<cancelDueToCorrection>CANC</cancelDueToCorrection>
<closeOutPl0>251.274</closeOutPl0>
<closeoutpl1>611.1</closeoutpl1>
<clsOpenCumExInd>N</clsOpenCumExInd>
<compared>COMPARED</compared>
<covFlag>N</covFlag>
<creditLossB>914.737</creditLossB>
<creditLossL>170.807</creditLossL>
<disallowGainB>377.402</disallowGainB>
<disallowGainL>646.823</disallowGainL>
<disallowLossB>598.347</disallowLossB>
<disallowLossL>547.032</disallowLossL>
<discountRate>846.688</discountRate>
<dispBookValueGaap>856.063</dispBookValueGaap>
<dispBookValueStat>512.424</dispBookValueStat>
<eventSubPriority>351570</eventSubPriority>
<exchangeFeeBase>644.81</exchangeFeeBase>
<fairMarketValueBase>965.018</fairMarketValueBase>
<fairMarketValueFlag>Y</fairMarketValueFlag>
<fairMarketValueLocal>623.962</fairMarketValueLocal>
<fairMarketValuePrice>120.062</fairMarketValuePrice>
<federalTaxWithholdBase>936.828</federalTaxWithholdBase>
<feeamtBase>893.403</feeamtBase>
<gaapCostValue>810.128</gaapCostValue>
<gaapSecType>TYPE</gaapSecType>
<gaapUnitCost>221.794</gaapUnitCost>
<gainCurrencyAvg>664.505</gainCurrencyAvg>
<gainCurrencyId>816.428</gainCurrencyId>
<gainSecurityAvg>904.945</gainSecurityAvg>
<gainSecurityAvgLocal>249.432</gainSecurityAvgLocal>
<gainSecurityId>911.744</gainSecurityId>
<gainSecurityIdLocal>375.085</gainSecurityIdLocal>
<interestSold>927.777</interestSold>
<invertFlag>N</invertFlag>
<letterConfirm>LETTER</letterConfirm>
<lettered>LETTERED</lettered>
<localCostLot>357.015</localCostLot>
<localGaapCostValue>658.576</localGaapCostValue>
<localGaapUnitCost>243.062</localGaapUnitCost>
<localRealizedGl>422.255</localRealizedGl>
<lossCurrencyAvg>269.677</lossCurrencyAvg>
<lossCurrencyId>157.8</lossCurrencyId>
<lossSecurityAvg>407.028</lossSecurityAvg>
<lossSecurityAvgLocal>461.045</lossSecurityAvgLocal>
<lossSecurityId>618.589</lossSecurityId>
<lossSecurityIdLocal>905.375</lossSecurityIdLocal>
<margin>908.077</margin>
<marginPoints>379.244</marginPoints>
<monthlyAccountingDate>2015-03-13</monthlyAccountingDate>
<mtType>MT_TYPE</mtType>
<nonCreditLossB>675.406</nonCreditLossB>
<nonCreditLossL>334.469</nonCreditLossL>
<notionalAmt>910.672</notionalAmt>
<openAcctDate>2015-03-13</openAcctDate>
<openEventId>TSTR821</openEventId>
<openMonthlyAcctDate>2015-03-13</openMonthlyAcctDate>
<originalAcctDate>2015-03-13</originalAcctDate>
<originalEventId>TSTR210A</originalEventId>
<originalmonthlyAcctDate>2015-03-13</originalmonthlyAcctDate>
<otherFeeBase>899.341</otherFeeBase>
<ownershipTransferDate>2015-03-13</ownershipTransferDate>
<paceTradeEventId>TSTR821</paceTradeEventId>
<poolNumber>POOL_NUMBER</poolNumber>
<positionId>762246</positionId>
<priceCurrency>EUR</priceCurrency>
<qtyWashSaleEligible>922.815</qtyWashSaleEligible>
<quantity>439.866</quantity>
<realizedGlBase>667.192</realizedGlBase>
<realizedGlCrB>986.213</realizedGlCrB>
<realizedGlCrL>351.611</realizedGlCrL>
<realizedGlLocal>856.243</realizedGlLocal>
<realizedGlNcrB>827.957</realizedGlNcrB>
<realizedGlNcrL>522.592</realizedGlNcrL>
<recordType>TYPE</recordType>
<settleAmt1>514.82</settleAmt1>
<shortTermId>844847</shortTermId>
<stateTaxWithholdBase>598.463</stateTaxWithholdBase>
<targetPurchaseDate>2015-03-13</targetPurchaseDate>
<targetPurchasePrice>856.751</targetPurchasePrice>
<taxesBase>251.876</taxesBase>
<termLength>LENGTH</termLength>
<totalCurrentLoss>959.508</totalCurrentLoss>
<totalGainAvg>475.341</totalGainAvg>
<totalGainId>157.252</totalGainId>
<totalLossAvg>781.553</totalLossAvg>
<totalLossId>668.552</totalLossId>
<tradeFlat>Y</tradeFlat>
<tradersLogin>LOGIN</tradersLogin>
<unitPriceOpen>646.624</unitPriceOpen>
<updateUserId>TSTR821</updateUserId>
<userDate1>2015-03-13</userDate1>
<userFloat1>573.602</userFloat1>
<userFloat10>895.302</userFloat10>
<userFloat11>368.884</userFloat11>
<userFloat12>938.508</userFloat12>
<userFloat13>708.611</userFloat13>
<userFloat14>995.37</userFloat14>
<userFloat15>210.785</userFloat15>
<userFloat2>332.049</userFloat2>
<userFloat3>757.618</userFloat3>
<userFloat4>133.601</userFloat4>
<userFloat5>671.915</userFloat5>
<userFloat6>424.811</userFloat6>
<userFloat7>933.78</userFloat7>
<userFloat8>837.492</userFloat8>
<userFloat9>195.022</userFloat9>
</warehouseCloseLot>
</warehouseTransaction>
</EagleML>
Refer to the data Map Table for Warehouse Close Lot object.
CSV Processing Sequence
Warehouse Close Lot 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 Close Lot 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/warehouseCloseLot/effectiveDate – mapped to TRADESDBO.DISPOSAL_LOTS.EFFECTIVE_DATE
- Entity Id: warehouseTransaction/warehouseCloseLot/entityId (or warehouseTransaction/warehouseCloseLot/xrefAccountId and warehouseTransaction/warehouseCloseLot/xrefAccountIdType) – mapped to TRADESDBO.DISPOSAL_LOTS.ENTITY_ID
- Source Name: warehouseTransaction/warehouseCloseLot/sourceName
- Number of Lots: warehouseTransaction/warehouseCloseLot/numberofLots – mapped to TRADESDBO.DISPOSAL_LOTS.NBR_OF_LOTS
- Master Ticket Number: warehouseTransaction/warehouseCloseLot/masterTicketNumber – mapped to TRADESDBO.DISPOSAL_LOTS.MASTER_TICKET_NUMBER
- Security Identifiers: to resolve the Security Alias (see the section 'Security Resolution Logic'), Security Alias mapped to TRADESDBO.DISPOSAL_LOTS.SECURITY_ALIAS
Relationship to DB Table TRADESDBO.TRADE, Element Trade Id
For successful loading data into DB table TRADESDBO.DISPOSAL_LOT, the record containing the following elements should exist in the DB table TRADESDBO.TRADE:
Security Alias + Source Name + Entity Id + Master Ticket Number
The set of elements above are mapped into lookup for resolving Trade Id:
SELECT trade_id
INTO L_trade_id
FROM TRADE
WHERE security_alias = L_security_alias
AND src_intfc_inst = L_src_intfc_inst
AND entity_id = in_entity_id
AND effective_date = TO_DATE(in_effective_date,'YYYYMMDD')
AND master_ticket_number = in_master_ticket_number;
- Security Alias: mapped to TRADESDBO.TRADE.SECURITY_ALIAS
- Source Name: the field Src Intfc Inst resolved by Source Name, mapped to TRADESDBO.TRADE.SRC_INTFC_INST
- Entity Id: mapped to TRADESDBO.TRADE.ENTITY_ID
- Master Ticket Number: mapped to TRADESDBO.TRADE.MASTER_TICKET_NUMBER
Security Resolution Logic
<securityAlias> should not be specified in the incoming EagleML message. Refer to Security Resolution for Warehouse Objects
Entity Resolution Logic
Refer to Entity Resolution for Warehouse Objects
Modes of Data Loading
The Warehouse Close Lot inbound interface has two modes for loading data into DB.
Batch Mode
UPSERT mode