select xmlelement("executionAdvice",
xmlattributes('http://www.fl.org/L-5/confirmation' as
"xmlns",
'http://www.w3.org/2000/09/xmldsig#' as
"xmlns:dsig",
'http://www.bank.com/CL/FL-5/ext' as
"xmlns:ext",
'http://www.bank.com/messagebus/core/FL-4-5/extension' as
"xmlns:sbml",
'http://www.w3.org/2001/XMLSchema-instance' as
"xmlns:xsi",
'5-12' as "flVersion",
'http://www.fl.org/FL-5/confirmation fl-main-5-12.xsd http://www.bank.com/CL/FL-5/ext cibl-main-5-12.xsd' as
"xsi:schemaLocation"),
xmlelement("header",
xmlelement("messageId",
xmlattributes('http://www.bank.com/MessageBus/MessageIdScheme' as
"messageIdScheme"),
a.seq_no),
xmlelement("sentBy",
xmlattributes('http://www.bank.com/MessageBus/SentByScheme' as
"messageAddressScheme"),
'FD'),
xmlelement("creationTimestamp",
to_char(sysdate,
'YYYY-MM-DD') || 'T' ||
to_char(sysdate,
'HH24:MI:SS'))),
xmlelement("isCorrection", 'false'),
xmlelement("correlationId",
xmlattributes('[http://www.bank.com/MessageBus/ConversationIdScheme'](http://www.bank.com/MessageBus/ConversationIdScheme') as
"correlationIdScheme"),
sys\_guid()),
xmlelement("sequenceNumber", '1'),
xmlelement("trade",
xmlelement("tradeHeader",
xmlattributes('ext:TradeHeader' as
"xsi:type"),
xmlelement("partyTradeIdentifier",
xmlelement("partyReference",
xmlattributes('party1' as
"href") /\*,v\_dpl\_rec.payerpartyreference1\*/),
xmlelement("tradeId",
xmlattributes('ThisTradeId' as
"tradeIdScheme"),
a.trade\_no),
xmlelement("tradeId",
xmlattributes('PreviousTradeId' as
"tradeIdScheme"),
''),
xmlelement("tradeId",
xmlattributes('FirstTradeId' as
"tradeIdScheme"),
''), ---- swaps
xmlelement("tradeId",
xmlattributes('ThisContractId' as
"tradeIdScheme"),
''),
xmlelement("tradeId",
xmlattributes('PreviousContractId' as
"tradeIdScheme"),
''),
xmlelement("tradeId",
xmlattributes('FirstContractId' as
"tradeIdScheme"),
''),
xmlelement("tradeId",
xmlattributes('LinkEventId' as
"tradeIdScheme"),
'0'),
xmlelement("versionedTradeId",
xmlelement("tradeId",
xmlattributes('VersionedTradeId' as
"tradeIdScheme"),
''),
xmlelement("version",
'1')),
xmlelement("tradeId",
xmlattributes('ShariahIndicator' as
"tradeIdScheme"),
'')), --
xmlelement("partyTradeIdentifier",
xmlattributes('ext:PartyTradeIdentifier' as
"xsi:type"),
xmlelement("issuer",
xmlattributes('UTIPrefix' as
"issuerIdScheme"),
substr(a.uti\_value,
1,
10)),
xmlelement("tradeId",
xmlattributes('UTIValue' as
"tradeIdScheme"),
substr(uti\_value,
11)),
xmlelement("ext:generator",
xmlattributes('UTIGenerator' as
"UTIScheme"),
initcap(uti\_generator)),
xmlelement("ext:source",
xmlattributes('UTISource' as
"UTIScheme"),
''),
xmlelement("ext:tiebreakerResult",
xmlattributes('UTITiebreakerResultScheme' as
"UTIScheme"),
decode(a.isda\_tiebreaker\_result,
'WE',
initcap(a.isda\_tiebreaker\_result),
initcap(a.isda\_tiebreaker\_result)))),
xmlelement("partyTradeInformation",
xmlelement("partyReference",
xmlattributes('party1' as
"href")),
xmlelement("trader",
a.trader)),
xmlelement("tradeDate",
to\_char(a.tradedate,
'YYYY-MM-DD')),
xmlelement("ext:typology",
a.typology),
xmlelement("ext:tradeAction",
a.tradeaction),
xmlelement("ext:tradeInsertDate",
to\_char(sysdate,
'YYYY-MM-DD') || 'T' ||
to\_char(sysdate,
'HH24:MI:SS')),
xmlelement("ext:tradeUpdateDate",
to\_char(sysdate,
'YYYY-MM-DD') || 'T' ||
to\_char(sysdate,
'HH24:MI:SS')),
xmlelement("ext:tradeStatus",
a.tradestatus),
xmlelement("ext:sourceSystemStatus",
a.sourcesystemstatus),
xmlelement("ext:srcSystemTradeRefPrefix",
a.srcsystemtraderefprefix),
xmlelement("ext:portalUniqueId",
a.portaluniqueid),
xmlelement("ext:internalTrade",
a.internaltrade),
xmlelement("ext:activeTrade",
a.activetrade),
xmlelement("ext:narrationUser",
a.narrationuser),
xmlelement("ext:comments",
xmlelement("ext:sourceComments",
xmlelement("ext:comment",
a.other\_info),
xmlelement("ext:comment",
'')))),
xmlelement("fxSingleLeg",
xmlelement("productType",
xmlattributes('[http://www.bank.com/MessageBus/ProductType'](http://www.bank.com/MessageBus/ProductType') as
"productTypeScheme"),
a.producttype),
xmlelement("productType",
xmlattributes('[http://www.bank.com/MessageBus/SubProductType'](http://www.bank.com/MessageBus/SubProductType') as
"productTypeScheme"),
a.producttype),
xmlelement("assetClass",
a.assetclass),
xmlelement("exchangedCurrency1",
--B "payerPartyReference"
--S "receiverPartyReference"
xmlelement("payerPartyReference",
xmlattributes(case
when b.trade\_type = 'S' then
'party1'
else
'party2'
end as
"href")),
xmlelement("receiverPartyReference",
xmlattributes(case
when b.trade\_type = 'S' then
'party2'
else
'party1'
end as
"href")),
xmlelement("paymentAmount",
xmlelement("currency",
a.currency1),
xmlelement("amount",
to\_char(a.amount1)))),
xmlelement("exchangedCurrency2",
xmlelement("payerPartyReference",
xmlattributes(case
when b.trade\_type = 'S' then
'party2'
else
'party1'
end as
"href")),
xmlelement("receiverPartyReference",
xmlattributes(case
when b.trade\_type = 'S' then
'party1'
else
'party2'
end as
"href")),
xmlelement("paymentAmount",
xmlelement("currency",
currency2),
xmlelement("amount",
to\_char(amount2)))),
xmlelement("valueDate",
a.valuedate),
xmlelement("exchangeRate",
xmlelement("quotedCurrencyPair",
xmlelement("currency1",
a.ccy1),
xmlelement("currency2",
a.ccy2),
xmlelement("quoteBasis",
a.quotebasis)),
xmlelement("rate",
to\_char(a.rate)),
xmlelement("spotRate",
to\_char(a.spotrate))))),
xmlelement("party",
xmlattributes('party1' as "id"),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/SAPCompanyNumberScheme'](http://www.bank.com/MessageBus/SAPCompanyNumberScheme') as
"partyIdScheme"),
sapcompanynumberscheme),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/SAPCostCentreScheme'](http://www.bank.com/MessageBus/SAPCostCentreScheme') as
"partyIdScheme"),
sapcostcentrescheme1),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/PortfolioIdScheme'](http://www.bank.com/MessageBus/PortfolioIdScheme') as
"partyIdScheme"),
portfolioidscheme1),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/EntityScheme'](http://www.bank.com/MessageBus/EntityScheme') as
"partyIdScheme"),
entityscheme1),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/LegalEntityScheme'](http://www.bank.com/MessageBus/LegalEntityScheme') as
"partyIdScheme"),
legalentityscheme1),
xmlelement("partyName",
partyname1)),
xmlelement("party",
xmlattributes('party2' as "id"),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/SAPCostCentreScheme'](http://www.bank.com/MessageBus/SAPCostCentreScheme') as
"partyIdScheme"),
sapcostcentrescheme2),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/PortfolioIdScheme'](http://www.bank.com/MessageBus/PortfolioIdScheme') as
"partyIdScheme"),
portfolioidscheme2),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/EntityScheme'](http://www.bank.com/MessageBus/EntityScheme') as
"partyIdScheme"),
entityscheme2),
xmlelement("partyId",
xmlattributes('[http://www.bank.com/MessageBus/LegalEntityScheme'](http://www.bank.com/MessageBus/LegalEntityScheme') as
"partyIdScheme"),
a.legalentityscheme2),
xmlelement("partyId",
XMLforest('[http://www.bank.com/MessageBus/CIFNumberScheme'](http://www.bank.com/MessageBus/CIFNumberScheme') as
"partyIdScheme"),
a.cifnumberscheme),
xmlelement("partyId",
xmlattributes('http://www.bank.com/MessageBus/OSDScheme' as
"partyIdScheme"),
a.osdscheme),
xmlelement("partyName",
a.partyname2))
) from dual;