Barclays- Claims Manager- [_Key ] Format in Barclays

Barclays system builds the [_KEY] value of a claim from a combination of columns from the source file. Below is the column combination used for Coupon and Dividend Claims to form the [_KEY] value.

'Product ID'+
'A/C no'+
MID(STR('Pay date'),2,6)+
ALT((LEN('Tref1')<=8)AND (CVAL('Tref1')>999999),'Tref1',VBGS("GETSIG:"+STR('Tref1')))+
DEF(SLOOKUP('Link',"Coupon=A;Paydown=B;ADR Dividend=C;Auction Rate Dividend=D;Basket Dividend=E;Basket Warrant=F;
Canadian Trust Unit Distribution=G;Capital - Payment=H;Cash Dividend=I;Cash/Stock Option=J;Closed End Fund Distribution=K;
Coupon Distribution=L;Coupon Renewal Sheet=M;Dividend - Cash=N;Dividend - Cash/Stock Options=O;Dividend - Mandatory Cash/Stock Outcomes=P;
Dividend - Other=Q;Dividend - Special=R;Dividend - Stock=S;Dividend - Unknown=T;Dividend Arrears=U;Dividend Cancellation=V;
Dividend Omitted=W;Dividend Reinvestment=X;Dividend Rescinded=Y;Exchange Warrant=Z;Fund Distribution=0;Limited Partnership Distribution=1;
Return Of Capital=2;Royalty (LTD Partnership)=3;Special Cash Dividend=4;Unit Investment Trust Payment=5;
Unit Investment Trust Payment (Fractional)=6"),"9")+
DEF(SLOOKUP('Entity',"BBPLC=A;BCEG=B;BCET=C;BCLUX=D;BCSI=E;BCSL=F;BGI=G"),"Z")+
DEF(SLOOKUP('Bus stream',"EDG=A;EFG=B"),"Z")+
DEF(SLOOKUP('Trade type',"Appendix C (Deliver)=1;Appendix C (Receipt)=2;Borrow versus cash=3;Finance Trades Payable=4;
Finance Trades Receivable=5;Lend versus cash=6;Long Book Position=7;Long Depot Position=8;Open Buy Trade=9;
Open Sell Trade=A;Open Stock Borrow=B;Open Stock Loan=C;Repo=D;Reverse Repo=E;Sell / Buyback Receivable=F;
Short Book Position=G;Short Depot Position=H"),"0")+
LEFT('Nominal',6)

Unique Ref is assigned with value in the OUTCOME_REF column in the source file

Below is a sample query to get all values to find the [_KEY] value of a claim if the OUTCOME_REF/Unique Ref is available.

SELECT MRT_KEY, MRTENTITY, MRT_IMPORTDATE, MRT_LASTUPDATE,MRT_VALUEDATE, MRTBUSSTREAM, MRTSDSID,MRTPRODUCTID, MRTTRADEREF1, MRTTRDTYPE
FROM MRT_BCCLAIMS WHERE MRTUNIQUEREF='287018369'

SELECT MRT_KEY,MRTUNIQUEREF,MRT_IMPORTDATE,MRT_LASTUPDATE,
MRTPRODUCTID,MRTACNO,MRT_VALUEDATE,MRTTRADEREF1,MRTLINK,MRTENTITY,MRTBUSSTREAM,MRTTRDTYPE,MRTNOMINAL
FROM MRT_BCCLAIMS WHERE MRTUNIQUEREF='287018369' OR MRTTRADEREF1='287018369'