Warehouse Cash Activity Inbound Interface

In this page:

Overview

Warehouse Cash Activity inbound interface is designed to load and update data in CASHDBO.CASH_ACTIVITY database table.
Incoming message format: EagleML (WarehouseCashActivity complex type).

Stream Logic

EagleML Processing Sequence

Warehouse Cash Activity 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 Cash Activity database tables send an EagleML message into the eagle_ml-2-0_default_in_xml_warehouse_preproc stream.

Click to expand an example of incoming EagleML message

<EagleML
eaglemlVersion="2-0"
eaglemlType="WarehouseTransactionMessage"
xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd"
xsi:type="WarehouseTransactionMessage"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0">
    <warehouseTransaction>
        <header>
            <objectType>WarehouseCashActivity</objectType>
        </header>
        <warehouseCashActivity>
            <objectType>WarehouseCashActivity</objectType>
            <objectId>WarehouseCashActivity</objectId>
            <sourceName>EAGLE PACE</sourceName>
            <updateSource>MCADMIN</updateSource>
            <linkEventId>WHEMFLUN</linkEventId>
            <localCurrency>USD</localCurrency>
            <effectiveDate>2016-12-12</effectiveDate>
            <maturityDate>2020-01-01</maturityDate>
            <longShortIndicator>S</longShortIndicator>
            <entityId>EMLFUNWH</entityId>
            <entityName>EMLFUNWH</entityName>
            <entityType>PORT</entityType>
			<xrefAccountId>EMLFUNWH_XREF01</xrefAccountId>
            <xrefAccountIdType>XREF1</xrefAccountIdType>
            <xrefClassCode>TF1</xrefClassCode>
            <entityXrefs>
                <entityXref>
                    <xrefAccountId>EMLFUNWH_XREF02</xrefAccountId>
                    <xrefAccountIdType>XREF2</xrefAccountIdType>
                    <xrefClassCode>TF2</xrefClassCode>
                </entityXref>
                <entityXref>
                    <xrefAccountId>EMLFUNWH_XREF03</xrefAccountId>
                    <xrefAccountIdType>XREF3</xrefAccountIdType>
                    <xrefClassCode>TF3</xrefClassCode>
                </entityXref>
                <entityXref>
                    <xrefAccountId>EMLFUNWH_XREF04</xrefAccountId>
                    <xrefAccountIdType>XREF4</xrefAccountIdType>
                    <xrefClassCode>TF4</xrefClassCode>
                </entityXref>
            </entityXrefs>
            <accountingBasis>USTAX</accountingBasis>
            <entityIDLookupSwitch>EXACT</entityIDLookupSwitch>
            <entityNameLookupSwitch>EXACT</entityNameLookupSwitch>
			<xrefAccountId>EMLFUNWH_XREF01</xrefAccountId>
            <securityQueryFlag>Y</securityQueryFlag>
            <sourceLookupSwitch>EXACT</sourceLookupSwitch>
            <procedureMode>INSERT</procedureMode>
            <procedureFlag>I</procedureFlag>
			<primaryAssetId>EMLFUN_SMF_WH1</primaryAssetId>
            <primaryAssetType>INTERNAL</primaryAssetType>
            <altAssetIdAndType>
                <instrumentId>ISINEMLFUWH1</instrumentId>
                <instrumentIdType>ISIN</instrumentIdType>
            </altAssetIdAndType>
            <xrefIdentifiers>
                <xId1>
                    <xId>EMLGEWH1</xId>
                    <xIdType>EMLGENXDTYPE</xIdType>
                </xId1>
            </xrefIdentifiers>
            <issueName>WH_EMLFUNSMFGENISSUENAME</issueName>
            <issueDescription>WH_EMLFUNSMFGENISSUENAME</issueDescription>
            <cusip>EML_CUSWH</cusip>
            <primaryExchange>ALL</primaryExchange>
            <primaryExchangeCode>ALL</primaryExchangeCode>
            <assetCurrency>USD</assetCurrency>
            <assetResolutionOption>XREF_EXCHANGE</assetResolutionOption>
            <battingOrder>battingOrder0</battingOrder>
            <baseCurrency>USD</baseCurrency>
            <srcIntfcInst>470</srcIntfcInst>
            <transactionType>BUY</transactionType>
            <account>account0</account>
            <activitySource>CASH</activitySource>
            <bankCode>MELLON</bankCode>
            <basePrincipalFlow>47</basePrincipalFlow>
            <baseTotalFlow>6</baseTotalFlow>
            <cancelAccountingDt>2016-07-26</cancelAccountingDt>
            <cancelFlag>N</cancelFlag>
            <cancelStatus>N</cancelStatus>
            <cancelMeAcctgDate>2016-11-12</cancelMeAcctgDate>
            <calculateReflexiveFlowsSwitch>Y</calculateReflexiveFlowsSwitch>
            <custodyBankSubAccount>330316</custodyBankSubAccount>
            <currFxRate>43</currFxRate>
            <dividendRate>42</dividendRate>
            <gainSwapB>25</gainSwapB>
            <gainSwapL>21</gainSwapL>
            <gainLoss>30</gainLoss>
            <grossAmtL>13</grossAmtL>
            <grossAmtB>18</grossAmtB>
            <lossSwapB>40</lossSwapB>
            <lossSwapL>8</lossSwapL>
            <localIncomeFlow>15</localIncomeFlow>
            <localPrincipalFlow>33</localPrincipalFlow>
            <localTotalFlow>22</localTotalFlow>
            <masterTransactionNum>FI88CT497544</masterTransactionNum>
            <netAmountLocal>21</netAmountLocal>
            <openHldPerDate>2013-11-11</openHldPerDate>
            <originalAcquisitionCostBase>33</originalAcquisitionCostBase>
            <originalAcquisitionCostLocal>18</originalAcquisitionCostLocal>
            <originalTransactionNumber>4504127361</originalTransactionNumber>
            <performanceFlow>5231</performanceFlow>
            <performancePrice>650</performancePrice>
            <performanceCode>M</performanceCode>
            <performanceFlowDesc>Positive</performanceFlowDesc>
            <reflexiveFlows>Y</reflexiveFlows>
            <reclaimAmountBase>48</reclaimAmountBase>
            <reclaimAmountLocal>23</reclaimAmountLocal>
            <reportingReplayInd>N</reportingReplayInd>
            <settlementCurrency>USD</settlementCurrency>
            <sourceLongShortInd>S</sourceLongShortInd>
            <sourceSecurityAlias>54</sourceSecurityAlias>
            <systemControl>systemControl</systemControl>
            <systemCurrencyTotalFlow>19</systemCurrencyTotalFlow>
            <systemCurrencyPrincipalFlow>50</systemCurrencyPrincipalFlow>
            <systemCurrencyIncomeFlow>64</systemCurrencyIncomeFlow>
            <systemCurrency>CAD</systemCurrency>
            <systemCurrExchangeRate>31</systemCurrExchangeRate>
            <systemCurrExchangeRateDate>2013-08-09</systemCurrExchangeRateDate>
            <transactionQuantity>52</transactionQuantity>
            <transactionNum>FKKAD0CT3G634HSL$</transactionNum>
            <withholdingLocal>22</withholdingLocal>
            <withholdingBase>62</withholdingBase>
            <price>60</price>
            <bookCost>240</bookCost>
            <cashEventType>RECPT</cashEventType>
            <cashCategory>TRADE</cashCategory>
            <corporateActionInstance>7</corporateActionInstance>
            <cancelType>C</cancelType>
            <exchangeFeeBase>49</exchangeFeeBase>
            <eventId>FKKAD0CT3G634HSL</eventId>
            <federalTaxWithholdBase>64</federalTaxWithholdBase>
            <mttype>MTOPEN</mttype>
            <netCash>650</netCash>
            <netAmountBase>46</netAmountBase>
            <origAcqDate>2013-05-07</origAcqDate>
            <postDate>2013-09-24</postDate>
            <positionId>50</positionId>
            <scrubType>TR</scrubType>
            <scrubId>320</scrubId>
            <status>TRADED</status>
            <secFeesBase>2</secFeesBase>
            <termLoanFeeExpB>31</termLoanFeeExpB>
            <termLoanFeeExpL>52</termLoanFeeExpL>
            <termLoanFeeIncB>27</termLoanFeeIncB>
            <termLoanFeeIncL>23</termLoanFeeIncL>
            <targetEventId>FKKAD0CT3G634HSL</targetEventId>
            <updateUserId>XBBJC18</updateUserId>
            <updateSwitch>N</updateSwitch>
            <xrateLocalBase>10</xrateLocalBase>
            <accountingDate>2013-09-25</accountingDate>
            <accruedIndicator>Y</accruedIndicator>
            <allocationId>500</allocationId>
            <baseCommission>3</baseCommission>
            <baseStampDutyTax>30</baseStampDutyTax>
            <baseIncomeFlow>39</baseIncomeFlow>
            <cashBalInst>20</cashBalInst>
            <creditAmount>190</creditAmount>
            <cashBalance>42</cashBalance>
            <debitAmount>570</debitAmount>
            <drCrIndicator>Y</drCrIndicator>
            <effExpFlowBase>580</effExpFlowBase>
            <effExpFlowLocal>30</effExpFlowLocal>
            <effExpFlowReporting>440</effExpFlowReporting>
            <estarPosnId>20</estarPosnId>
            <feedResultsInst>130</feedResultsInst>
            <glAccount>INCOME</glAccount>
            <intPurSold>65</intPurSold>
            <localIntPurSold>28</localIntPurSold>
            <localStampDutyTax>36</localStampDutyTax>
            <localPrice>310</localPrice>
            <localTaxesFees>390</localTaxesFees>
            <settleToAssetFxRate>63</settleToAssetFxRate>
            <securityCurrencyLoss>52</securityCurrencyLoss>
            <securityCurrencyGain>39</securityCurrencyGain>
            <strategyId>336228</strategyId>
            <streamSecAlias>62</streamSecAlias>
            <stTaxWithholdBase>57</stTaxWithholdBase>
            <sysControlDate>2016-10-12</sysControlDate>
            <sourceEventId>FKKAD0CT3G634HSL</sourceEventId>
            <spdIncomeRecbl>180</spdIncomeRecbl>
            <notUsed>Y</notUsed>
            <opFlag>N</opFlag>
            <origContSettleDt>2013-09-20</origContSettleDt>
            <perfOnly>N</perfOnly>
            <periodId>610</periodId>
            <performanceFlowValue>4</performanceFlowValue>
            <postCorpActionPrice>70</postCorpActionPrice>
            <preCorpActionPrice>110</preCorpActionPrice>
            <reviseDate>2013-07-13</reviseDate>
            <switch3>LIKE</switch3>
            <targetAcctDate>2016-02-17</targetAcctDate>
            <tradeFee>60</tradeFee>
            <tradeTaxes>19</tradeTaxes>
            <tbaFlag>N</tbaFlag>
            <tradeStatus>C</tradeStatus>
            <tradeTaxesBase>48</tradeTaxesBase>
            <txBaseLongTermGl>420</txBaseLongTermGl>
            <txBaseMktValue>450</txBaseMktValue>
            <txBaseShortTermGl>370</txBaseShortTermGl>
            <txLocalLongTermGl>540</txLocalLongTermGl>
            <txLocalMktValue>450</txLocalMktValue>
            <txLocalShortTermGl>470</txLocalShortTermGl>
            <userGroup1>usrGrp1</userGroup1>
            <userGroup2>usrGrp2</userGroup2>
            <userChar1>userChar10</userChar1>
            <userChar2>userChar20</userChar2>
            <userChar3>userChar30</userChar3>
            <userChar4>userChar40</userChar4>
            <userChar5>userChar50</userChar5>
            <userChar6>userChar60</userChar6>
            <userFloat1>580</userFloat1>
            <userFloat2>490</userFloat2>
            <userFloat3>540</userFloat3>
            <userFloat4>190</userFloat4>
            <userFloat5>650</userFloat5>
            <userFloat6>90</userFloat6>
            <comments1>comments10</comments1>
            <comments2>comments20</comments2>
            <comments3>comments30</comments3>
            <comments4>comments40</comments4>
            <comments5>comments50</comments5>
            <comments6>comments60</comments6>
            <comments7>comments70</comments7>
            <comments8>comments80</comments8>
            <bankAlias>46</bankAlias>
            <batchEventId>WH000001</batchEventId>
            <brokerCode>EAGLE</brokerCode>
            <clearingBrokerCode>BBH</clearingBrokerCode>
            <commissionAmount>33</commissionAmount>
            <lotNumber>38</lotNumber>
            <masterTicketNumber>WH1_EMLFUN</masterTicketNumber>
            <monthEndAccountingDate>2013-09-23</monthEndAccountingDate>
            <secFee>2</secFee>
            <settlementDate>2016-09-24</settlementDate>
            <tradeDate>2016-09-21</tradeDate>
            <transactionCode>SHORTSELL</transactionCode>
            <reasonCode>reasonCode0</reasonCode>
        </warehouseCashActivity>
    </warehouseTransaction>
</EagleML>

CSV Processing Sequence

Warehouse Cash Activity 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 XMLDBAPI request
- csv-wrhs_cash_activity_xmldbapi.xml rule
eagle_ml-2-0_default_in_xml_warehouse
- Loading data into the database. The 'eagle_default/in/xml/xml-dbdirect_invar.xml' rule is used

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

Master/Original Transaction Number Logic

Single Mode Load

Values in the incoming file

Values loaded to DB

Both values are not set or missing

originalTransactionNumber is NULL
masterTransactionNum is NULL

You will get the following error:

Original Transaction Number cannot be null

Only original transaction number set

originalTransactionNumber = FILLED
masterTransactionNum is NULL

- masterTransactionNum copies the originalTransactionNumber value:

- if you send the same message again, the values will remain the same (as from the file):

- change originalTransactionNumber value to FILLED_B, once again masterTransactionNum copies this value and a new record will be created:

Both values are set:

originalTransactionNumber = FILLED_C
masterTransactionNum = FILLED_D

- first load: values from file loaded into DB:

- if you change originalTransactionNumber to FILLED_Е, the record will be updated updated with the new originalTransactionNumber value:

- if you change masterTransactionNum to FILLED_F, the record will be updated with the new masterTransactionNum value:

- if you change both: originalTransactionNumber to FILLED_G and masterTransactionNum to FILLED_H, a new record will be created:

Batch Mode Load

If no masterTransactionNum value is provided in the file for load, masterTransactionNum value will reflect the originalTransactionNumber value from the record:

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

The Reflexive flow processing

is depended on the System Control value, systemControl is required element and should have the same value as cashdbo.cash_activity.SPD_PERF_FLOW_RULE field

Based on CSA_EGL_GEN_INTERFACE stored procedure calculateReflexiveFlowsSwitch flag works with systemControl node that can be equaled 0,1,2,3,5,6,7.

Additional details:

If systemControl equals 3 or 7 then BASE_INCOME_FLOW, BASE_PRINCIPAL_FLOW, BASE_TOTAL_FLOW, LOCAL_INCOME_FLOW, LOCAL_PRINCIPAL_FLOW, LOCAL_TOTAL_FLOW, SYS_TOTAL_FLOW, SYS_PRINCIPAL_FLOW, SYS_INCOME_FLOW fields set as 0 in cash_activity table.

If systemControl equals 0,1,2,5,6 then spawn record is inserted:

if systemControl equals 1,5 then

BASE_INCOME_FLOW, BASE_PRINCIPAL_FLOW, BASE_TOTAL_FLOW, LOCAL_INCOME_FLOW, LOCAL_PRINCIPAL_FLOW, LOCAL_TOTAL_FLOW, SYS_TOTAL_FLOW, SYS_PRINCIPAL_FLOW, SYS_INCOME_FLOW fields set as 0;

if systemControl equals 0,2,6 then

BASE_INCOME_FLOW, BASE_PRINCIPAL_FLOW, BASE_TOTAL_FLOW, LOCAL_INCOME_FLOW, LOCAL_PRINCIPAL_FLOW, LOCAL_TOTAL_FLOW, SYS_TOTAL_FLOW, SYS_PRINCIPAL_FLOW, SYS_INCOME_FLOW fields insert with inverted value with minus sign.