Closing the STP gap with SQL

A common question asked to the support teams is “Where is my trade?”. When a trade is booked on a external platform and is STP-ed to Murex, if strategically placed field overwrites with clever values have not been setup ahead of time, trying to find exactly which inbound message produces which trade becomes very time consuming.

But thanks to the tools described in the Zips in the DB post, we can now do all that in one SQL. The query below works like this: For a given date (the SQL+ style parameter :REPORTING_DATE, it checks all flows that came out of all the importMxml tasks it can find. Then it parses all the messages with grammar name ‘evs_ans_ok.dtd’ on flows coming out of these tasks, extract the trade id that might be in there thanks to a very lazy XQuery expression in XMLTABLE. From there, the functional can be closed between the ‘Workflow Tables’ and the ‘Financial Tables’ and a join can be made.

WITH
WKF_TASK AS(
SELECT T.CODE TASK,T.TYPE_CODE, TB.CODE NODE_NAME, TB.FILTER_CODE NODE_ID, TB.SYSTEM_ERROR
FROM MUREX.MXMLEX_TASK_TABLE T
INNER JOIN MUREX.MXMLEX_TASK_TABLEBODY TB ON TB.REFERENCE_ID=T.REFERENCE_ID
WHERE T.STATUS_TAKEN=’N’ AND DATASOURCE_CODE=’STPDOC_ENTRY_SPACE’
)
,WKF_STRUCTURE AS(
SELECT WKF_TASK_FROM.TASK TASK_FROM, WKF_TASK_FROM.NODE_ID NODE_FROM,
WKF_TASK_TO.TASK TASK_TO, WKF_TASK_TO.NODE_ID NODE_TO,
XMLCast(XMLQuery(‘/EvsTaskConfig/FilterFields/Column[Name=”XMLFLOW_GRAMMAR_NAME”]/Value’ PASSING SAFEXMLTYPE(MxBlobToClob(taskCfg.XML,taskCfg.XML_LZ)) RETURNING CONTENT) AS VARCHAR2(50)) XMLFLOW_GRAMMAR_NAME
FROM MUREX.MXMLEX_LINK_TABLE MXMLLINK
INNER JOIN WKF_TASK WKF_TASK_FROM ON WKF_TASK_FROM.TASK = MXMLLINK.FROM_TASK AND WKF_TASK_FROM.NODE_NAME = MXMLLINK.FROM_NODE
INNER JOIN WKF_TASK WKF_TASK_TO ON WKF_TASK_TO.TASK = MXMLLINK.TO_TASK AND WKF_TASK_TO.NODE_NAME = MXMLLINK.TO_NODE
LEFT OUTER JOIN MUREX.MXMLEX_TASK_CFG_TABLE taskCfg on taskCfg.STM_CODE = WKF_TASK_FROM.TASK AND taskCfg.STATUS_TAKEN = ‘N’
WHERE MXMLLINK.STATUS_TAKEN =’N’ AND
WKF_TASK_FROM.TYPE_CODE =’ImportMxML’
)
,stpDoc AS(
SELECT
stpDocEntry.JOB_DATE STATUS_DATE
,TO_CHAR(stpDocEntry.JOB_DATE,’yyyy/mm/dd ‘)||’ ‘||TO_CHAR(stpDocEntry.JOB_TIME,’hh24:mi:ss’) INSERTION_DATETIME
,stpDocEntry.XMLFLOW_UID
,COALESCE(TRIM(stpDocEntry.STPDOC_UNDERLYING_REF_TYPE),TRIM(stpDocEntry.SOURCE_EVENT_CLASS_ID),TRIM(stpDocEntry.STPDOC_DATA_TYPE2),TRIM(stpDocEntry.STPDOC_REF_TYPE)) SOURCE_SYSTEM
,stpDocEntry.XMLFLOW_STATUS
,ws.XMLFLOW_GRAMMAR_NAME
,stpDocEntry.REFERENCE_ID
FROM WKF_STRUCTURE ws
INNER JOIN MUREX.STPDOC_ENTRY_TABLE stpDocEntry ON stpDocEntry.XMLFLOW_STATUS = ws.NODE_FROM
WHERE stpDocEntry.JOB_DATE=TO_DATE(:REPORTING_DATE,’DD-MM-YYYY’)
)
,StpDocBods AS (
SELECT stpDoc.REFERENCE_ID
,max(stpDocBodyOk.BODY_DOCUMENT_ID) OK_BODY_DOCUMENT_ID
,max(stpDocBodyErr.BODY_DOCUMENT_ID) ERR_BODY_DOCUMENT_ID
FROM STPDOC
LEFT OUTER JOIN STPDOC_ENTRY_TABLEBODY stpDocBodyOk ON stpDocBodyOk.REFERENCE_ID=stpDoc.REFERENCE_ID AND stpDocBodyOk.XMLFLOW_GRAMMAR_NAME=’evs_ans_ok.dtd’
LEFT OUTER JOIN STPDOC_ENTRY_TABLEBODY stpDocBodyErr ON stpDocBodyErr.REFERENCE_ID=stpDoc.REFERENCE_ID AND stpDocBodyErr.XMLFLOW_GRAMMAR_NAME=’evs_ans_err.dtd’
GROUP BY stpDoc.REFERENCE_ID
)
SELECT
stpDoc.INSERTION_DATETIME
,stpDoc.XMLFLOW_UID
,stpDoc.SOURCE_SYSTEM
,E.EXCEPTION_LEVEL
,contract.M_PACK_REF
,trnHdr.M_CONTRACT
,E.TRADE_ID
,trnHdr.M_GID GLOBAL_ID
,DECODE(E.TRADE_ID,NULL,’’,TRIM(trnHdr.M_TRN_FMLY)||’|’||TRIM(trnHdr.M_TRN_GRP)||’|’||TRIM(trnHdr.M_TRN_TYPE)) AS “FMLY|GRP|TYPE”
,TRIM(typoTrn.M_LABEL) TRADE_TYPO
,TRIM(DECODE(trnHdr.M_BINTERNAL,’Y’,trnHdr.M_BTRADER,trnHdr.M_STRADER)) trader
,TRIM(trnHdr.M_SALES) sales
,TRIM(DECODE(trnHdr.M_BINTERNAL,’N’,TRIM(trnHdr.M_BPFOLIO) || ‘ ‘ , ‘’) || DECODE(trnHdr.M_SINTERNAL,’N’,TRIM(trnHdr.M_SPFOLIO) || ‘ ‘ , ‘’)) ExternalCpt_trnhdr
,TRIM(DECODE(trnHdr.M_BINTERNAL,’Y’,TRIM(trnHdr.M_BPFOLIO) || ‘ ‘ , ‘’) || DECODE(trnHdr.M_SINTERNAL,’Y’,TRIM(trnHdr.M_SPFOLIO) || ‘ ‘ , ‘’)) MurexPtf_trnhdr
,contract.M_STP_STATUS
,trnHdr.M_TRN_STATUS
,REPLACE(XMLQuery(q’[string-join(//Description[not(../MXExceptions)],’,CRLF’)]’ PASSING SAFEXMLTYPE(MxBlobToClob(mxmlexRepoErr.XML,mxmlexRepoErr.XML_LZ)) RETURNING CONTENT).GETCLOBVAL(),’,CRLF’,(chr(13)||chr(10))) ERROR_DESCRIPTION
,stpDoc.XMLFLOW_STATUS
FROM stpDoc
LEFT OUTER JOIN StpDocBods on StpDocBods.REFERENCE_ID = stpDoc.REFERENCE_ID
LEFT OUTER JOIN MUREX.MXMLEX_REPOSITORY_SPACE mxmlexRepoOk ON mxmlexRepoOk.XML_DOCUMENT_ID=StpDocBods.OK_BODY_DOCUMENT_ID
LEFT OUTER JOIN XMLTABLE(‘
for $i in /GuiRoot
let $exceptionLevel := $i/requestAnswer/@exceptionLevel
for $j in distinct-values($i//tradeInternalId)
return (<r>
<tradeInternalId>{data($j)}</tradeInternalId>
<exceptionLevel>{data($exceptionLevel)}</exceptionLevel>
</r>)’
PASSING SAFEXMLTYPE(REGEXP_REPLACE(MxBlobToClob(mxmlexRepoOk.XML,mxmlexRepoOk.XML_LZ), ‘<!DOCTYPE[^<]*>’, ‘’))
COLUMNS TRADE_ID NUMBER(10) PATH ‘/r/tradeInternalId’
,EXCEPTION_LEVEL VARCHAR2(50) PATH ‘/r/exceptionLevel’
)E ON 1=1
LEFT OUTER JOIN MUREX.MXMLEX_REPOSITORY_SPACE mxmlexRepoErr ON mxmlexRepoErr.XML_DOCUMENT_ID=StpDocBods.ERR_BODY_DOCUMENT_ID
LEFT OUTER JOIN MUREX.TRN_HDR_DBF trnHdr ON trnHdr.M_NB=E.TRADE_ID AND E.TRADE_ID IS NOT NULL
LEFT OUTER JOIN MUREX.TRN_EXT_DBF tExt ON trnHdr.M_LEXTREF = tExt.M_REFERENCE
LEFT OUTER JOIN MUREX.TYPOLOGY_DBF typoTrn ON typoTrn.M_REFERENCE=trnHdr.M_TYPOLOGY AND trnHdr.M_TYPOLOGY IS NOT NULL
LEFT OUTER JOIN MUREX.CONTRACT_DBF contract ON contract.M_REFERENCE=trnHdr.M_CONTRACT
WHERE stpDoc.STATUS_DATE=TO_DATE(:REPORTING_DATE,’DD-MM-YYYY’)
ORDER BY stpDoc.REFERENCE_ID DESC