SQL for writing in file format

 USE [CentralDataStoreSDL]

GO

/****** Object:  StoredProcedure [Daily].[SpUFAFinancialTransactionOutput_ScenarioData_REG]    Script Date: 2021/09/29 13:26:25 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [Daily].[SpUFAFinancialTransactionOutput_ScenarioData_REG] @SourceSystem VARCHAR(100) 

AS

BEGIN

SET NOCOUNT ON;

--Initialise Table definition for sp as source in SSIS

IF 1=0

BEGIN

SELECT 

 CAST(NULL AS  varchar(max)) AS StringColumn

END

/* 

select * from daily.UFAFinancialTransactionStage

*/

--Drops Temp tables

IF OBJECT_ID('tempdb.dbo.#FTDELTA', 'U') IS NOT NULL  

  DROP TABLE #FTDELTA 

IF OBJECT_ID('tempdb.dbo.#StringTable', 'U') IS NOT NULL  

  DROP TABLE #StringTable 

-------------------------------------------------------------

--CREATE Temp table for string values

-------------------------------------------------------------

CREATE TABLE #StringTable

(

StringColumn varchar(max)

)

-------------------------------------------------------------

--END

-------------------------------------------------------------

-------------------------------------------------------------

--SELECT DELTAS into Temp table for use in extract

-------------------------------------------------------------

--Get Current and Previous Business Date

DECLARE @CurrentBusinessDate DATETIME

, @PrevBusinessDate DATETIME

--, @SourceSystem VARCHAR = 'IN1034'

, @CurrentBusinessDateBP DATETIME

, @CurrentBusinessDateFMI DATETIME

SELECT @CurrentBusinessDate = MAX(CDSBusinessDate)

FROM Daily.UFAFinancialTransaction_ScenarioData_REG

WHERE CDSStatusFlag = 1

AND SourceSystemID = @SourceSystem

SELECT @PrevBusinessDate = MAX(CDSBusinessDate)

FROM Daily.UFAFinancialTransaction_ScenarioData_REG WHERE CDSBusinessDate < @CurrentBusinessDate

AND SourceSystemID = @SourceSystem

SELECT @CurrentBusinessDateBP = MAX(CDSBusinessDate)

FROM Daily.UFAOSDBusinessPartner 

WHERE CDSStatusFlag = 1

SELECT @CurrentBusinessDateFMI = MAX(CDSBusinessDate)

FROM CreditDataStore.Monthly.TCMFMIProfitCentre 

WHERE CDSStatusFlag = 1

--GET LATEST FMI DATA /*BH 20180216*/

SELECT DISTINCT CompanyCode

INTO #FMICC

FROM CreditDataStore.Monthly.TCMFMIProfitCentre

WHERE CDSBusinessDate = @CurrentBusinessDateFMI

AND ISNUMERIC(ProfitCentre) = 1

SELECT DISTINCT CONVERT(NUMERIC(10,0),ProfitCentre) ProfitCentre

INTO #FMIPC

FROM CreditDataStore.Monthly.TCMFMIProfitCentre

WHERE CDSBusinessDate = @CurrentBusinessDateFMI

AND ISNUMERIC(ProfitCentre) = 1

--GET LATEST BP DATA

IF OBJECT_ID('tempdb.dbo.#BP', 'U') IS NOT NULL  

  DROP TABLE #BP 

CREATE TABLE #BP (

CIFCustN NUMERIC(10) NULL,

MrktSubSegmtC NUMERIC(10) NULL,

SourceSystem VARCHAR(100) NULL

)

IF @SourceSystem <> 'IN1046'

BEGIN 

INSERT INTO #BP

SELECT DISTINCT CIFCustN

, MrktSubSegmtC

, 'ZA' SourceSystem

FROM Daily.UFAOSDBusinessPartner

WHERE CDSBusinessDate = @CurrentBusinessDateBP

AND MasterInd = 'Y'

END

IF @SourceSystem = 'IN1046'

BEGIN

INSERT INTO #BP

SELECT DISTINCT CIFCustN

, MrktSubSegmtC

, SourceSystem

FROM Daily.UFAOSDBusinessPartner

WHERE CDSBusinessDate = @CurrentBusinessDateBP

AND MasterInd = 'Y'

END

---NEW TRADES

SELECT 

 FT.*

,CONVERT(VARCHAR(100),FT.FinancialTransaction + '') AS FinancialTransactionOutput

,FTMap.CDSStatusFlag AS FTMapCDSStatusFlag

,FTMap.Product AS FTMapProduct

,FTMap.BuySellIndicator AS FTMapBuySellIndicator

,FTMap.NodeID

,FTMap.SDLTemplate

,FTMap.BPR_SeqNO

,FTMap.BPR_RLTyp

,FTMap.BAS_IDNum

,FTMap.BAS_ContractType

,FTMap.BAS_ActivePassive

,FTMap.BAS_RiskBasis

,FTMap.FPO_Type 

,FTMap.FPO_OrgUnitPartner

,FTMap.BAS_BuySellFlag 

,FTMap.BAS_SpotForwFlag

--AB CHANGE ADDED 20180228 FOR DINERS BPRIDType

,CASE WHEN FT.SourceSystemID = 'IN1046' THEN FT.SecondTradeID ELSE SourceMap.SDLTemplateBPRIDType END AS BPR_IDType

,CASE WHEN FTMap.BAS_ContractCuKy IS NOT NULL THEN FTMap.BAS_ContractCuKy

ELSE SourceMap.SDLTemplateBASContractCuky 

END AS BAS_ContractCuky 

,BP.MrktSubSegmtC

,FTMap.BAS_ExternalProduct

-- CREATE new Temp table

INTO #FTDELTA

FROM CentralDataStoreSDL.Daily.UFAFinancialTransaction_ScenarioData_REG AS FT

INNER JOIN CreditDataStore.Monthly.UFAEnigmaToBankAnalyserFTMap AS FTMap

ON FT.Product = FTMap.Product

AND (FTMap.BuySellIndicator IS NULL OR ISNULL(FT.BuySellIndicator,'') = ISNULL(FTMap.BuySellIndicator,''))

AND FTMap.CDSStatusFlag = 1

--AB JOIN ADDED 20171204 FOR DINERS

AND ISNULL(FTMap.SourceSystemID,FT.SourceSystemID) = FT.SourceSystemID

INNER JOIN CreditDataStore.Monthly.UFASourceSystemMap AS SourceMap

ON FT.SourceSystemID = SourceMap.SourceSystemCode

AND SourceMap.CDSStatusFlag = 1

LEFT JOIN #BP AS BP 

ON FT.BusinessPartnerID = BP.CIFCustN

AND CASE WHEN @SourceSystem = 'IN1046' THEN FT.SecondTradeID

ELSE BP.SourceSystem 

END = BP.SourceSystem

WHERE FT.CDSBusinessDate = @CurrentBusinessDate

AND FT.SourceSystemID = @SourceSystem

AND NOT EXISTS 

(

SELECT * FROM CentralDataStoreSDL.Daily.UFAFinancialTransaction_ScenarioData_REG AS FTPrev

WHERE FTPrev.CDSBusinessDate = @PrevBusinessDate

AND FT.FinancialTransaction = FTPrev.FinancialTransaction

AND FT.SourceSystemID = @SourceSystem

AND FTPrev.SourceSystemID = @SourceSystem

)

UNION ALL

---UPDATED TRADES------------------------------------------------

SELECT FT.*

,CONVERT(VARCHAR(100),FT.FinancialTransaction + '') AS FinancialTransactionOutput

,FTMap.CDSStatusFlag AS FTMapCDSStatusFlag

,FTMap.Product AS FTMapProduct

,FTMap.BuySellIndicator AS FTMapBuySellIndicator

,FTMap.NodeID

,FTMap.SDLTemplate

,FTMap.BPR_SeqNO

,FTMap.BPR_RLTyp

,FTMap.BAS_IDNum

,FTMap.BAS_ContractType

,FTMap.BAS_ActivePassive

,FTMap.BAS_RiskBasis

,FTMap.FPO_Type 

,FTMap.FPO_OrgUnitPartner

,FTMap.BAS_BuySellFlag 

,FTMap.BAS_SpotForwFlag

--AB CHANGE ADDED 20180228 FOR DINERS BPRIDType

,CASE WHEN FT.SourceSystemID = 'IN1046' THEN FT.SecondTradeID ELSE SourceMap.SDLTemplateBPRIDType END AS BPR_IDType

,CASE WHEN FTMap.BAS_ContractCuKy IS NOT NULL THEN FTMap.BAS_ContractCuKy

ELSE SourceMap.SDLTemplateBASContractCuky 

END AS BAS_ContractCuky 

,BP.MrktSubSegmtC

,FTMap.BAS_ExternalProduct

FROM CentralDataStoreSDL.Daily.UFAFinancialTransaction_ScenarioData_REG AS FT

INNER JOIN CentralDataStoreSDL.Daily.UFAFinancialTransaction_ScenarioData_REG AS FTPrev

ON FT.FinancialTransaction = FTPrev.FinancialTransaction

AND FT.CDSDeltaChecksum <> FTPrev.CDSDeltaChecksum

INNER JOIN CreditDataStore.Monthly.UFAEnigmaToBankAnalyserFTMap AS FTMap

ON FT.Product = FTMap.Product

AND (FTMap.BuySellIndicator IS NULL OR ISNULL(FT.BuySellIndicator,'') = ISNULL(FTMap.BuySellIndicator,''))

AND FTMap.CDSStatusFlag = 1

--AB JOIN ADDED 20171204 FOR DINERS

AND ISNULL(FTMap.SourceSystemID,FT.SourceSystemID) = FT.SourceSystemID

INNER JOIN CreditDataStore.Monthly.UFASourceSystemMap AS SourceMap

ON FT.SourceSystemID = SourceMap.SourceSystemCode

AND SourceMap.CDSStatusFlag = 1

LEFT JOIN #BP AS BP 

ON FT.BusinessPartnerID = BP.CIFCustN

AND CASE WHEN @SourceSystem = 'IN1046' THEN FT.SecondTradeID

ELSE BP.SourceSystem 

END = BP.SourceSystem

WHERE FT.CDSBusinessDate = @CurrentBusinessDate

AND FTPrev.CDSBusinessDate = @PrevBusinessDate

AND FT.SourceSystemID = @SourceSystem

AND FTPrev.SourceSystemID = @SourceSystem

IF @SourceSystem <> 'IN1045' --DON'T APPLY RULE FOR MAS

BEGIN

--REMOVE INTERNAL FT WITH INCORRECT FMI INFORMATION /*BH 20180216*/

DELETE FT

FROM #FTDELTA FT

LEFT JOIN #FMIPC FMIPC ON FMIPC.ProfitCentre = FT.PartnerProfitCentre

WHERE InternalFlag = 'Y' AND FMIPC.ProfitCentre IS NULL

DELETE FT

FROM #FTDELTA FT

LEFT JOIN #FMICC FMICC ON FMICC.CompanyCode = FT.PartnerLegalEntity

WHERE InternalFlag = 'Y' AND FMICC.CompanyCode IS NULL

DELETE FT

FROM #FTDELTA FT

WHERE InternalFlag = 'N' AND (FT.PartnerProfitCentre IS NOT NULL OR FT.PartnerLegalEntity IS NOT NULL)

END

--SELECT * FROM #FTDELTA

-------------------------------------------------------------

--END

-------------------------------------------------------------

--00---------------------------------------------------------

--Only one header row needed (TOP 1)

INSERT INTO #StringTable

(

StringColumn

)

SELECT TOP 1

'00' --Header

 + '|' + 'BA_' + FT.SourceSystemID + '_FT_' + convert(varchar(10),FT.CDSBusinessDate,112) + '.TXT' --[File name]

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) AS StringColumn --[Date]

FROM #FTDELTA AS FT

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40BOND

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + ISNULL(convert(VARCHAR,FT.BPR_SeqNO),'') --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' + CASE WHEN FT.BAS_IDNum = 'FT.IssuerID' THEN RIGHT('0000000000' + ISNULL(convert(varchar,FT.IssuerID),''),10)

WHEN FT.BAS_IDNum = 'FT.BusinessPartnerID' THEN RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10)

END --IDNUM

 + '|' 

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR10--------------------------------------------------------

--Bond ISIN

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/1BA/_BA1C_ISIN' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.ISIN)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR11--------------------------------------------------------

--Issuer for Regulatory Reporting

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISPTRR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50CodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR12--------------------------------------------------------

--Issuer Reserve Bank Code / Sector

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIRBCOD' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR13--------------------------------------------------------

--Issuer Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISSUCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClassIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR14--------------------------------------------------------

--Issuer Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntityIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR15--------------------------------------------------------

--Issuer Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentreIssuer IS NULL THEN '' 

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentreIssuer)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR16--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR17--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

-- START COMMENT - JA CHANGE ADDED ON 2019-02-19 AS PER VERSION 0.42 OF THE STTM2 SPEC

--CHR18--------------------------------------------------------

--NPL status

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNPLSTAT' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.NPLStatus),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

-- END COMMENT - JA CHANGE ADDED ON 2019-02-19 AS PER VERSION 0.42 OF THE STTM2 SPEC

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40BOND'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40BOND

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40CASHFLW

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' + ISNULL(FT.BAS_ActivePassive,'') --ACTIVE_PASSIVE

 + '|' + ISNULL(FT.BAS_RiskBasis,'') --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR12--------------------------------------------------------

--NPL Status

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNPLSTAT' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.NPLStatus),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--CHR13--------------------------------------------------------

--Reporting Market Segment

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCMKTSEG' --FIELDNAME

 + '|' + ISNULL(CONVERT(varchar,FT.MrktSubSegmtC),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CASHFLW'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40CASHFLW

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40CURRACC

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'')--DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' + ISNULL(FT.BAS_ActivePassive,'') --ACTIVE_PASSIVE

 + '|' + ISNULL(FT.BAS_RiskBasis,'') --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(ISNULL(FT.BAS_ExternalProduct,FT.Product),'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR12--------------------------------------------------------

--NPL status

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNPLSTAT' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.NPLStatus),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--CHR13--------------------------------------------------------

--Reporting Market Segment

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCMKTSEG' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.MrktSubSegmtC),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CURRACC'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40CURRACC

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40REPO

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40REPO'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40REPO

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FXTRD

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00002' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(CASE WHEN FT.BAS_ContractCuKy = 'FT.PrimaryCurrency' THEN FT.PrimaryCurrency

WHEN FT.BAS_ContractCuKy = 'FT.SecondaryCurrency' THEN FT.SecondaryCurrency

WHEN FT.BAS_ContractCuky = 'ZAR' THEN 'ZAR'

END,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'0') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR12--------------------------------------------------------

--ISIN of the instrument

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/1BA/_BA1C_ISIN' --FIELDNAME

 + '|' + ISNULL(FT.ISIN,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR13--------------------------------------------------------

--Option Time to Exercise

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZKOPTTTED' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.OptionTimeToExercise),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR14--------------------------------------------------------

--Original Trade ID

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCOTRADID' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.OriginalTradeID),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR15--------------------------------------------------------

--Package ID

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPACKNO' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.PackageNumber),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR16--------------------------------------------------------

--Underlying Time to End

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZKUNDTTE' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.UnderlyingTimeToEnd),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR17--------------------------------------------------------

--Underlying Time to Start

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZKUNDTTS' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.UnderlyingTimeToStart),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR18--------------------------------------------------------

--Margin Collateral Type

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCMCTYPE' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.MarginCollateralType),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--CHR19--------------------------------------------------------

--Option Price Currency

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCTECID' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.OptionPriceCurrency),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRD'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FXTRD

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FXTRDLE

-----------------------------------------------------------------------------------------------------------------------------------

----HEAD-------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT 

--'HEAD' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

-- + '|' + 'Z_ALL' --AUTHORITYGROUP

-- + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40FXTRDLE'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRDLE'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(CASE WHEN FT.BAS_ContractCuKy = 'FT.PrimaryCurrency' THEN FT.PrimaryCurrency

WHEN FT.BAS_ContractCuKy = 'FT.SecondaryCurrency' THEN FT.SecondaryCurrency

END,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BAS_BuySellFlag = 'FT.BuySellIndicator' THEN 

CASE WHEN FT.BuySellIndicator = 'B' THEN '1'

WHEN FT.BuySellIndicator = 'S' THEN '2'

END

ELSE FT.BAS_BuySellFlag

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'0') --BOOK

 + '|' --GRACE_PERIOD

 + '|' --ORGUNIT_OWNER

 + '|' --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRDLE'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FXTRDLE'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FXTRDLE

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40SWAP

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00002' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZA' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') --PROD_CONTROL

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAP'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40SWAP

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40SWAPLEG

-----------------------------------------------------------------------------------------------------------------------------------

----HEAD-------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT 

--'HEAD' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

-- + '|' + 'Z_ALL' --AUTHORITYGROUP

-- + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40SWAPLEG'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAPLEG'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'')--DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(CASE WHEN FT.BAS_ContractCuKy = 'FT.PrimaryCurrency' THEN FT.PrimaryCurrency

WHEN FT.BAS_ContractCuKy = 'FT.SecondaryCurrency' THEN FT.SecondaryCurrency

END,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BAS_BuySellFlag = 'FT.BuySellIndicator' THEN 

CASE WHEN FT.BuySellIndicator = 'B' THEN '1'

WHEN FT.BuySellIndicator = 'S' THEN '2'

END

ELSE FT.BAS_BuySellFlag

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZA' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' + 'SIRS' --PROD_VARIANT

 + '|' --BOOK

 + '|' --GRACE_PERIOD

 + '|' --ORGUNIT_OWNER

 + '|' --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAPLEG'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40SWAPLEG'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40SWAPLEG

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FRA

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00002' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZA' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') --PROD_CONTROL

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRA'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FRA

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FRALEG

-----------------------------------------------------------------------------------------------------------------------------------

----HEAD-------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT 

--'HEAD' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

-- + '|' + 'Z_ALL' --AUTHORITYGROUP

-- + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40FRALEG'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRALEG'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'')--DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BAS_BuySellFlag = 'FT.BuySellIndicator' THEN 

CASE WHEN FT.BuySellIndicator = 'B' THEN '1'

WHEN FT.BuySellIndicator = 'S' THEN '2'

END

ELSE FT.BAS_BuySellFlag

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZA' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' --BOOK

 + '|' --GRACE_PERIOD

 + '|' --ORGUNIT_OWNER

 + '|' --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRALEG'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FRALEG'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FRALEG

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40CMDFWD

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' + ISNULL(FT.BAS_SpotForwFlag,'') --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') --PROD_CONTROL

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR10--------------------------------------------------------

--Underlying Commodity Type

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCCOMMOD' --FIELDNAME

 + '|' --TBC --FIELDVALUE

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR11--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--CHR12--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CMDFWD'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40CMDFWD

-----------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

----START TEMPLATE: Z40CMDFLEG

-------------------------------------------------------------------------------------------------------------------------------------

----HEAD-------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT 

--'HEAD' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

-- + '|' + 'Z_ALL' --AUTHORITYGROUP

-- + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40CMDFLEG'

----HIER-------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT 

--'HIER' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

-- + '|' + '00000' --NUMBER_OF_SONS

-- + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

-- + '|' + 'ZV1' --VERSION

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40CMDFLEG'

----BAS--------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT

--'BAS' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

-- + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

-- + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

-- + '|' --ASSET_FLAG

-- + '|' --CONTRACT_STATUS

-- + '|' --CONTRACT_DATE

-- + '|' --CONTRACT_TIME

-- + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

-- + '|' --CONTRACT_STARTT

-- + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

-- + '|' --CONTRACT_ENDT

-- + '|' --CONTRACT_APPD

-- + '|' --CONTRACT_APPT

-- + '|' --CONT_CONF_STARTD

-- + '|' --CONT_CONF_STARTT

-- + '|' --CONT_CONF_ENDD

-- + '|' --CONT_CONF_ENDT

-- + '|' --DATE_END_CPTIEUP

-- + '|' --TIME_END_CPTIEUP

-- + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --DATE_STFIXINTER

-- + '|' --TIME_STFIXINTER

-- + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'')--DATE_ENDFIXINTER

-- + '|' --TIME_ENDFIXINTER

-- + '|' + ISNULL(CASE WHEN FT.BAS_ContractCuKy = 'FT.PrimaryCurrency' THEN FT.PrimaryCurrency

-- WHEN FT.BAS_ContractCuKy = 'FT.SecondaryCurrency' THEN FT.SecondaryCurrency

-- END,'') --CONTRACT_CUKY

-- + '|' --CONTRACT_CUKYISO

-- + '|' --CONTRACT_RATE

-- + '|' --CUST_RATE_TYPE

-- + '|' --MARKET_RATE

-- + '|' --MARKET_RATE_TYPE

-- + '|' --CONTRACT_SECCUKY

-- + '|' --CONTRACT_SECCUKY_ISO

-- + '|' --FAC_TYPE

-- + '|' --COLLATERAL_TYPE

-- + '|' --COLLATERAL_PRIO

-- + '|' --INTER_CALC_METH

-- + '|' --INTR_CALC_CAL

-- + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

-- + '|' + ISNULL(CASE WHEN FT.BAS_BuySellFlag = 'FT.BuySellIndicator' THEN FT.BuySellIndicator

-- ELSE FT.BAS_BuySellFlag

-- END,'') --BUY_SELL_FLAG

-- + '|' --SPOT_FORW_FLAG

-- + '|' --NOTICE_PERIOD

-- + '|' --PERIOD_UNIT

-- + '|' --SETTLEMENT_DATE

-- + '|' --MULTIPLICITY

-- + '|' --CALENDAR_I

-- + '|' --CALENDAR_II

-- + '|' --ACTIVE_PASSIVE

-- + '|' --RISK_BASIS

-- + '|' --CREDIT_EXPOSURE

-- + '|' --PROD_VARIANT

--+ '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

-- WHEN FT.AccountingBook = 'B' THEN '1'

-- END,'') --BOOK

-- + '|' --GRACE_PERIOD

-- + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

-- + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

-- + '|' --OPER_PROC_ID

-- + '|' --OPER_PROC_TYPE

-- + '|' --FUNDED_FLAG

-- + '|' + ISNULL(FT.Product,'') --PROD_CONTROL

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40CMDFLEG'

----FPO--------------------------------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

--SELECT

--'FPO' --REGISTRY CODE

-- + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

-- + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

-- + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

-- + '|' + '0000000001' --SEQ_NO

-- + '|' + 'IAS' --ACCOUNTING_STD

-- + '|' + '2' --FPO_CREATE

-- + '|' + 'M' --FPO_COMPONENT_CLASS

-- + '|' + '1' --FPO_RESIDUAL_ACQUISITION

-- + '|' --FPO_RESIDUAL_VALUATION

-- + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

-- + '|' AS StringColumn --FPO_HOLD_INTENTION

--FROM #FTDELTA AS FT

--WHERE FT.SDLTemplate = 'Z40CMDFLEG'

-------------------------------------------------------------------------------------------------------------------------------------

----END TEMPLATE: Z40CMDFLEG

-------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40OPTLIST

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + ISNULL(convert(VARCHAR,FT.BPR_SeqNO),'') --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,CASE WHEN FT.BAS_IDNum = 'FT.BusinessPartnerID' THEN FT.BusinessPartnerID

   WHEN FT.BAS_IDNum = 'FT.IssuerID' THEN FT.IssuerID

   END),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum = 'FT.BusinessPartnerID'

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + ISNULL(convert(VARCHAR,FT.BPR_SeqNO),'') --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,CASE WHEN FT.BAS_IDNum = 'FT.BusinessPartnerID' THEN FT.BusinessPartnerID

   WHEN FT.BAS_IDNum = 'FT.IssuerID' THEN FT.IssuerID

   END),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum = 'FT.IssuerID'

AND FT.IssuerID IS NOT NULL

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR10--------------------------------------------------------

--Bond ISIN

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/1BA/_BA1C_ISIN' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.ISIN)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR11--------------------------------------------------------

--Issuer for Regulatory Reporting

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISPTRR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50CodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR12--------------------------------------------------------

--Issuer Reserve Bank Code / Sector

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIRBCOD' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR13--------------------------------------------------------

--Issuer Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISSUCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClassIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR14--------------------------------------------------------

--Issuer Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntityIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR15--------------------------------------------------------

--Issuer Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentreIssuer IS NULL THEN '' 

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentreIssuer)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR16--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR17--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40OPTLIST'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40OPTLIST

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FUTURE

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + ISNULL(convert(VARCHAR,FT.BPR_SeqNO),'') --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,CASE WHEN FT.BAS_IDNum = 'FT.BusinessPartnerID' THEN FT.BusinessPartnerID

   WHEN FT.BAS_IDNum = 'FT.IssuerID' THEN FT.IssuerID

   END),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum = 'FT.BusinessPartnerID'

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + ISNULL(convert(VARCHAR,FT.BPR_SeqNO),'') --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,CASE WHEN FT.BAS_IDNum = 'FT.BusinessPartnerID' THEN FT.BusinessPartnerID

   WHEN FT.BAS_IDNum = 'FT.IssuerID' THEN FT.IssuerID

   END),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum = 'FT.IssuerID'

AND FT.IssuerID IS NOT NULL

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR10--------------------------------------------------------

--Bond ISIN

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/1BA/_BA1C_ISIN' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.ISIN)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR11--------------------------------------------------------

--Issuer for Regulatory Reporting

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISPTRR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50CodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR12--------------------------------------------------------

--Issuer Reserve Bank Code / Sector

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIRBCOD' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR13--------------------------------------------------------

--Issuer Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISSUCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClassIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR14--------------------------------------------------------

--Issuer Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntityIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR15--------------------------------------------------------

--Issuer Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentreIssuer IS NULL THEN '' 

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentreIssuer)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR16--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR17--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FUTURE'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FUTURE

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40CDS

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZA' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') --PROD_CONTROL

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CDS'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40CDS

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40CLN

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZAR' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' --ACTIVE_PASSIVE

 + '|' --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + ISNULL(convert(VARCHAR,FT.BPR_SeqNO),'') --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,CASE WHEN FT.BAS_IDNum = 'FT.BusinessPartnerID' THEN FT.BusinessPartnerID

   WHEN FT.BAS_IDNum = 'FT.IssuerID' THEN FT.IssuerID

   END),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR10--------------------------------------------------------

--Bond ISIN

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/1BA/_BA1C_ISIN' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.ISIN)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR11--------------------------------------------------------

--Issuer for Regulatory Reporting

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISPTRR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50CodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR12--------------------------------------------------------

--Issuer Reserve Bank Code / Sector

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIRBCOD' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCodeIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR13--------------------------------------------------------

--Issuer Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCISSUCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClassIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR14--------------------------------------------------------

--Issuer Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntityIssuer)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR15--------------------------------------------------------

--Issuer Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCIPARPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentreIssuer IS NULL THEN '' 

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentreIssuer)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR16--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--CHR17--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CLN'

AND FT.BAS_IDNum <> 'FT.IssuerID' --To ensure only one record gets included for this Registry Code

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40CLN

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FEE

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' + ISNULL(FT.BAS_ActivePassive,'') --ACTIVE_PASSIVE

 + '|' + ISNULL(FT.BAS_RiskBasis,'') --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FEE'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FEE

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40FACG

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' --DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' + ISNULL(FT.BAS_ActivePassive,'') --ACTIVE_PASSIVE

 + '|' + ISNULL(FT.BAS_RiskBasis,'') --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(FT.Product,'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.NettingID)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.NettingAssetLiabilityIndicator)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR12--------------------------------------------------------

--Facility Reference

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCFACREF' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.FacilityReference)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--CHR13--------------------------------------------------------

--Reporting Market Segment

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCMKTSEG' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.MrktSubSegmtC)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--AB CHANGE ADDED 2018-05-25

--CHR14--------------------------------------------------------

--Reporting NPL Status

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNPLSTAT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.NPLStatus)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40FACG'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40FACG

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

--START TEMPLATE: Z40CARDCF

-----------------------------------------------------------------------------------------------------------------------------------

--HEAD-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HEAD' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXTERNALCONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --BUSINESSDAY

 + '|' + 'Z_ALL' --AUTHORITYGROUP

 + '|' + ISNULL(FT.SourceSystemID,'') AS StringColumn --ORIGINAL_SYSTEM

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--HIER-------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT 

'HIER' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '00000' --NUMBER_OF_SONS

 + '|' + ISNULL(FT.SDLTemplate,'') --PRODUCT

 + '|' + 'ZV1' --VERSION

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') AS StringColumn --NODE_NO

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--BAS--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BAS' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + ISNULL(FT.SourceSystemID,'') --ORIGINAL_SYSTEM

 + '|' + ISNULL(FT.BAS_ContractType,'') --CONTRACT_TYPE

 + '|' --ASSET_FLAG

 + '|' + ISNULL(FT.TradeStatus,'') --CONTRACT_STATUS

 + '|' --CONTRACT_DATE

 + '|' --CONTRACT_TIME

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --CONTRACT_STARTD

 + '|' --CONTRACT_STARTT

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'') --CONTRACT_ENDD

 + '|' --CONTRACT_ENDT

 + '|' --CONTRACT_APPD

 + '|' --CONTRACT_APPT

 + '|' --CONT_CONF_STARTD

 + '|' --CONT_CONF_STARTT

 + '|' --CONT_CONF_ENDD

 + '|' --CONT_CONF_ENDT

 + '|' --DATE_END_CPTIEUP

 + '|' --TIME_END_CPTIEUP

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --DATE_STFIXINTER

 + '|' --TIME_STFIXINTER

 + '|' + ISNULL(convert(varchar(10),FT.MaturityDate,112),'')--DATE_ENDFIXINTER

 + '|' --TIME_ENDFIXINTER

 + '|' + ISNULL(FT.PrimaryCurrency,'') --CONTRACT_CUKY

 + '|' --CONTRACT_CUKYISO

 + '|' --CONTRACT_RATE

 + '|' --CUST_RATE_TYPE

 + '|' --MARKET_RATE

 + '|' --MARKET_RATE_TYPE

 + '|' --CONTRACT_SECCUKY

 + '|' --CONTRACT_SECCUKY_ISO

 + '|' --FAC_TYPE

 + '|' --COLLATERAL_TYPE

 + '|' --COLLATERAL_PRIO

 + '|' --INTER_CALC_METH

 + '|' --INTR_CALC_CAL

 + '|' + 'Z_' + ISNULL(FT.SourceSystemID,'') + '_SETTLEMENT_ACCOUNT' --EXT_CONT_NUMBER

 + '|' + ISNULL(CASE WHEN FT.BuySellIndicator = 'B' THEN '1' 

WHEN FT.BuySellIndicator = 'S' THEN '2'

END,'') --BUY_SELL_FLAG

 + '|' --SPOT_FORW_FLAG

 + '|' --NOTICE_PERIOD

 + '|' --PERIOD_UNIT

 + '|' --SETTLEMENT_DATE

 + '|' --MULTIPLICITY

 + '|' + 'ZA' --CALENDAR_I

 + '|' --CALENDAR_II

 + '|' + ISNULL(FT.BAS_ActivePassive,'') --ACTIVE_PASSIVE

 + '|' + ISNULL(FT.BAS_RiskBasis,'') --RISK_BASIS

 + '|' --CREDIT_EXPOSURE

 + '|' --PROD_VARIANT

 + '|' + ISNULL(CASE WHEN FT.AccountingBook = 'T' THEN '0'

WHEN FT.AccountingBook = 'B' THEN '1'

END,'') --BOOK

 + '|' --GRACE_PERIOD

 + '|' + RIGHT('0000000000' + ISNULL(convert(varchar,FT.OrganisationUnit),''),10) --ORGUNIT_OWNER

 + '|' + ISNULL(ISNULL(FT.ProductDescription,FT.Product),'') --EXTERNAL_PRODUCT

 + '|' --OPER_PROC_ID

 + '|' --OPER_PROC_TYPE

 + '|' --FUNDED_FLAG

 + '|' + ISNULL(FT.Product,'') AS StringColumn --PROD_CONTROL

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--BPR--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'BPR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + convert(varchar(10),FT.CDSBusinessDate,112) --DATE0

 + '|' + '1' --SEQ_NO

 + '|' + ISNULL(convert(varchar(10),FT.StartDate,112),'') --VALID_FROM

 + '|' + '99991231' --VALID_TO

 + '|' + ISNULL(FT.BPR_RLTyp,'') --RLTYP

 + '|' --DFVAL

 + '|' + ISNULL(FT.BPR_IDType,'') --IDTYPE

 + '|' +  RIGHT('0000000000' + ISNULL(convert(varchar,FT.BusinessPartnerID),''),10) --IDNUM

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END --ORGUNIT_PARTNER

 AS StringColumn

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR1--------------------------------------------------------

--LegalEntity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55LGENT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.LegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR2--------------------------------------------------------

--Portfolio

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCPORTFL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,upper(FT.Portfolio))),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR3--------------------------------------------------------

--Maturity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/MATURIT' --FIELDNAME

 + '|' AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR4--------------------------------------------------------

--Counterparty for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXCNTP' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.Form50Code)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR5--------------------------------------------------------

--Sector for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCXSECTOR' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.SectorCode)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR6--------------------------------------------------------

--Counterparty Class

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNTPTCL' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.CounterpartyClass)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR7--------------------------------------------------------

--Partner Legal Entity

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTLE' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.PartnerLegalEntity)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR8--------------------------------------------------------

--Partner Prft Center

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/CP0PARTPC' --FIELDNAME

 + '|' + CASE WHEN FT.PartnerProfitCentre IS NULL THEN ''

ELSE RIGHT('0000000000' + RTRIM(convert(varchar,FT.PartnerProfitCentre)),10) 

END AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR9--------------------------------------------------------

--BP Indicator Internal

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCBPINT' --FIELDNAME

 + '|' + ISNULL(RTRIM(convert(varchar,FT.InternalFlag)),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR10--------------------------------------------------------

--Netting ID for FT

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNETTID' --FIELDNAME

 + '|' + ISNULL(FT.NettingID,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR11--------------------------------------------------------

--AL Indicator Netting Group

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCALNETT' --FIELDNAME

 + '|' + ISNULL(FT.NettingAssetLiabilityIndicator,'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR12--------------------------------------------------------

--NPL status

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCNPLSTAT' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.NPLStatus),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR13--------------------------------------------------------

--FT Status

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/B20C/S_HDRACCST' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.TradeStatus),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR14--------------------------------------------------------

--Reference Account

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCREFACC' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.OriginalContractNumber),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR15--------------------------------------------------------

--Transfer Date

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C55POSTD' --FIELDNAME

 + '|' + ISNULL(convert(varchar(10),FT.TransferDate,112),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR16--------------------------------------------------------

--Hold Code

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BIC/ZCHOLCODE' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.HoldCode),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--CHR17--------------------------------------------------------

--Deliquency

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'CHR' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '/BA1/C58DELBND' --FIELDNAME

 + '|' + ISNULL(convert(varchar,FT.DelinquencyStatus),'') AS StringColumn --FIELDVALUE

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

--FPO--------------------------------------------------------

INSERT INTO #StringTable

(

StringColumn

)

SELECT

'FPO' --REGISTRY CODE

 + '|' + ISNULL(convert(VARCHAR(100),FT.FinancialTransactionOutput),'') --EXT_CONTRACT

 + '|' + ISNULL(convert(VARCHAR,FT.NodeID),'') --NODE_ID

 + '|' + ISNULL(convert(varchar(10),FT.CDSBusinessDate,112),'') --DATE0

 + '|' + '0000000001' --SEQ_NO

 + '|' + 'IAS' --ACCOUNTING_STD

 + '|' + '2' --FPO_CREATE

 + '|' + 'M' --FPO_COMPONENT_CLASS

 + '|' + '1' --FPO_RESIDUAL_ACQUISITION

 + '|' --FPO_RESIDUAL_VALUATION

 + '|' + ISNULL(FT.FPO_Type,'') --FPO_TYPE

 + '|' AS StringColumn --FPO_HOLD_INTENTION

FROM #FTDELTA AS FT

WHERE FT.SDLTemplate = 'Z40CARDCF'

-----------------------------------------------------------------------------------------------------------------------------------

--END TEMPLATE: Z40CARDCF

-----------------------------------------------------------------------------------------------------------------------------------

--TRAILER INSERT

--99----------------------------------

--INSERT INTO #StringTable

--(

--StringColumn

--)

SELECT * FROM #StringTable

UNION ALL

SELECT 

'99' --Footer

 + '|'--SUM(KFGA.AMOUNT)

 + '|'--SUM(LIM.LIMIT_AMOUNT)

 + '|'--SUM(OPH.VALUE_PT )

 + '|'--SUM(OPK.STRIKE_AMOUNT )

 + '|'--SUM(OPK.REBATE)

 + '|'--SUM(OPK.BARRIER1_AMOUNT)

 + '|'--SUM(OPK.BARRIER2_AMOUNT)

 + '|'--SUM(OPK.AMOUNT)

 + '|'--SUM(CFI.NOMINAL_AMOUNT)

 + '|'--SUM(CFI.BALANCE_AMOUNT)

 + '|'--SUM(CRL.NOMINAL_AMOUNT)

 + '|'--SUM(CRLKFGA.AMOUNT )

 + '|' + convert(varchar,(SELECT COUNT(*) FROM #StringTable WHERE SUBSTRING(StringColumn,1,5) = 'HEAD|'))--COUNT(HEAD)

 + '|' + convert(varchar,(SELECT COUNT(*) FROM #StringTable WHERE SUBSTRING(StringColumn,1,5) = 'HIER|'))--COUNT(HIER)

 + '|' + convert(varchar,(SELECT COUNT(*) FROM #StringTable WHERE SUBSTRING(StringColumn,1,4) = 'BAS|'))--COUNT(BAS)

 + '|' + convert(varchar,(SELECT COUNT(*) FROM #StringTable WHERE SUBSTRING(StringColumn,1,4) = 'BPR|'))--COUNT(BPR)

 + '|' + convert(varchar,(SELECT COUNT(*) FROM #StringTable WHERE SUBSTRING(StringColumn,1,4) = 'CHR|'))--COUNT(CHR)

 + '|' + convert(varchar,(SELECT COUNT(*) FROM #StringTable WHERE SUBSTRING(StringColumn,1,4) = 'FPO|'))--COUNT(FPO)

 + '|'--COUNT(KFGA)

 + '|'--COUNT(KFGP)

 + '|'--COUNT(KFGQ)

 + '|'--COUNT(LIM)

 + '|'--COUNT(OPD)

 + '|'--COUNT(OPH)

 + '|'--COUNT(OPK)

 + '|'--COUNT(OPE)

 + '|'--COUNT(CFH)

 + '|'--COUNT(CFI)

 + '|'--COUNT(CFF)

 + '|'--COUNT(CRL)

 + '|'--COUNT(CRLKFGA)

 + '|'--COUNT(CRLKFGQ)

 + '|'--COUNT(CRLKFGR)

 + '|'--COUNT(CUR)

 + '|'--COUNT(RAT)

 + '|'--COUNT(FCD)

 + '|'--COUNT(FCC)

 + '|'--COUNT(COND)

 + '|'--COUNT(AMT)

 + '|'--COUNT(AMTPOS)

 + '|'--COUNT(AMTPOSEXP)

 + '|'--COUNT(AMTPOSREL)

 + '|'--COUNT(DATE)

 + '|'--COUNT(DATEPOS)

 + '|'--COUNT(CRE)

 + '|'--COUNT(CRV)

 + '|' + convert(varchar,(SELECT COUNT(*)+1 FROM #StringTable))--COUNT(RECORD)

 END