1
CREATE VIEW PortfolioSelection2
AS3
SELECT DISTINCT pgo.GroupingId, (SELECT 4
(SELECT Id [@id], TradeType, 5
(SELECT 6
ti.EnvelopeCounterParty CounterParty, ti.EnvelopeNettingSetId NettingSetId,7
(SELECT taf.AddFieldsAdditionalId AdditionalId8
FROM PortfolioTrades taf WHERE taf.id = t.id9
FOR XML PATH (''), TYPE) AdditionalFields10
FROM PortfolioTrades ti WHERE ti.id = t.id11
FOR XML PATH (''), TYPE) Envelope,12
(SELECT 13
ta.Type, ta.Owner,14
(SELECT 15
(SELECT 16
s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate17
FROM PortfolioScheduleDataRules s WHERE s.TradeActionId = ta.Id18
FOR XML PATH (''), TYPE) Rules,19
(SELECT s.ScheduleDate [Date]20
FROM PortfolioScheduleDataDates s WHERE s.TradeActionId = ta.Id21
FOR XML PATH (''), TYPE) Dates22
FOR XML PATH (''), TYPE) Schedule23
FROM PortfolioTradeActions ta WHERE ta.TradeId = t.id24
FOR XML PATH ('TradeAction'), TYPE) TradeActions,25
(SELECT 26
(SELECT 27
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,28
CASE WHEN ld.LegType <> 'Cashflow' THEN29
(SELECT30
(SELECT ln.startDate [@startDate], ln.Notional [data()]31
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id ORDER by SeqId32
FOR XML PATH ('Notional'), TYPE),33
CASE WHEN (SELECT COUNT(li.FXresetForeignCurrency) FROM PortfolioLegData li WHERE li.Id =ld.Id) > 0 THEN (SELECT li.FXresetForeignCurrency ForeignCurrency, li.FXresetForeignAmount ForeignAmount, li.FXresetFXIndex FXIndex, li.FXresetFixingDays FixingDays34
FROM PortfolioLegData li WHERE li.Id =ld.Id35
FOR XML PATH ('FXReset'), TYPE) ELSE '' END,36
(SELECT li.NotionalInitialExchange InitialExchange, li.NotionalAmortizingExchange AmortizingExchange, li.NotionalFinalExchange FinalExchange37
FROM PortfolioLegData li WHERE li.Id =ld.Id38
FOR XML PATH (''), TYPE) Exchanges39
FOR XML PATH (''), TYPE) END Notionals,40
CASE WHEN ld.LegType <> 'Cashflow' THEN41
(SELECT 42
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate43
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id44
FOR XML PATH (''), TYPE) Rules,45
(SELECT s.ScheduleDate [Date]46
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id47
FOR XML PATH (''), TYPE) Dates48
FOR XML PATH (''), TYPE) END ScheduleData,49
CASE WHEN ld.LegType = 'Fixed' THEN 50
(SELECT 51
(SELECT r.StartDate [@startDate], r.Rate [data()]52
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId53
FOR XML PATH ('Rate'), TYPE) Rates54
FOR XML PATH (''), TYPE) END FixedLegData,55
CASE WHEN ld.LegType = 'Floating' THEN 56
(SELECT 57
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,58
(SELECT r.StartDate [@startDate], r.Spread [data()]59
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId60
FOR XML PATH ('Spread'), TYPE) Spreads,61
(SELECT r.StartDate [@startDate], r.Cap [data()]62
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId63
FOR XML PATH ('Cap'), TYPE) Caps,64
(SELECT r.StartDate [@startDate], r.[Floor] [data()]65
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId66
FOR XML PATH ('Floor'), TYPE) Floors,67
(SELECT r.StartDate [@startDate], r.Gearing [data()]68
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId69
FOR XML PATH ('Gearing'), TYPE) Gearings70
FROM PortfolioLegData li WHERE li.Id = ld.Id71
FOR XML PATH (''), TYPE) END FloatingLegData,72
CASE WHEN ld.LegType = 'Cashflow' THEN 73
(SELECT 74
(SELECT c.StartDate [@Date], c.Amount [data()]75
FROM PortfolioCashflowDataCashflow c WHERE c.LegDataId = ld.Id ORDER by SeqId76
FOR XML PATH ('Amount'), TYPE) Cashflow77
FOR XML PATH (''), TYPE) END CashflowData,78
CASE WHEN ld.LegType = 'CPI' THEN 79
(SELECT 80
li.CPILegIndexName [Index],81
(SELECT r.StartDate [@startDate], r.Rate [data()]82
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId83
FOR XML PATH ('Rate'), TYPE) Rates,84
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated85
FROM PortfolioLegData li WHERE li.Id = ld.Id86
FOR XML PATH (''), TYPE) END CPILegData,87
CASE WHEN ld.LegType = 'YY' THEN88
(SELECT89
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,90
(SELECT r.StartDate [@startDate], r.Rate [data()]91
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId92
FOR XML PATH ('Rate'), TYPE) Rates93
FROM PortfolioLegData li WHERE li.Id = ld.Id94
FOR XML PATH (''), TYPE) END YYLegData95
FROM PortfolioLegData ld WHERE ld.TradeId = t.id96
FOR XML PATH ('LegData'), TYPE)97
FOR XML PATH ('SwapData'), TYPE),98
(SELECT99
(SELECT sd.OptionDataLongShort LongShort, sd.OptionDataOptionType OptionType, sd.OptionDataStyle Style, sd.OptionDataSettlement Settlement, sd.OptionDataPayOffAtExpiry PayOffAtExpiry,100
(SELECT sd.OptionDataPremiumAmount Amount, sd.OptionDataPremiumCurrency Currency, sd.OptionDataPremiumPayDate [Date]101
FOR XML PATH (''), TYPE) Premium,102
(SELECT ed.ExerciseDate103
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id104
FOR XML PATH (''), TYPE) ExerciseDates,105
(SELECT ed.ExerciseFee106
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id107
FOR XML PATH (''), TYPE) ExerciseFees,108
(SELECT ed.ExercisePrice109
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id110
FOR XML PATH (''), TYPE) ExercisePrices111
FOR XML PATH (''), TYPE) OptionData,112
(SELECT 113
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,114
(SELECT115
(SELECT ln.startDate [@startDate], ln.Notional [data()]116
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id117
FOR XML PATH ('Notional'), TYPE),118
CASE WHEN (SELECT COUNT(li.FXresetForeignCurrency) FROM PortfolioLegData li WHERE li.Id =ld.Id) > 0 THEN (SELECT li.FXresetForeignCurrency ForeignCurrency, li.FXresetForeignAmount ForeignAmount, li.FXresetFXIndex FXIndex, li.FXresetFixingDays FixingDays119
FROM PortfolioLegData li WHERE li.Id =ld.Id120
FOR XML PATH ('FXReset'), TYPE) ELSE '' END,121
(SELECT li.NotionalInitialExchange InitialExchange, li.NotionalAmortizingExchange AmortizingExchange, li.NotionalFinalExchange FinalExchange122
FROM PortfolioLegData li WHERE li.Id =ld.Id123
FOR XML PATH (''), TYPE) Exchanges124
FOR XML PATH (''), TYPE) Notionals,125
(SELECT 126
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate127
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id128
FOR XML PATH (''), TYPE) Rules,129
(SELECT s.ScheduleDate [Date]130
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id131
FOR XML PATH (''), TYPE) Dates132
FOR XML PATH (''), TYPE) ScheduleData,133
CASE WHEN ld.LegType = 'Fixed' THEN 134
(SELECT 135
(SELECT r.StartDate [@startDate], r.Rate [data()]136
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId137
FOR XML PATH ('Rate'), TYPE) Rates138
FOR XML PATH (''), TYPE) END FixedLegData,139
CASE WHEN ld.LegType = 'Floating' THEN 140
(SELECT 141
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,142
(SELECT r.StartDate [@startDate], r.Spread [data()]143
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId144
FOR XML PATH ('Spread'), TYPE) Spreads,145
(SELECT r.StartDate [@startDate], r.Cap [data()]146
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId147
FOR XML PATH ('Cap'), TYPE) Caps,148
(SELECT r.StartDate [@startDate], r.[Floor] [data()]149
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId150
FOR XML PATH ('Floor'), TYPE) Floors,151
(SELECT r.StartDate [@startDate], r.Gearing [data()]152
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId153
FOR XML PATH ('Gearing'), TYPE) Gearings154
FROM PortfolioLegData li WHERE li.Id = ld.Id155
FOR XML PATH (''), TYPE) END FloatingLegData,156
CASE WHEN ld.LegType = 'CPI' THEN 157
(SELECT 158
li.CPILegIndexName [Index],159
(SELECT r.StartDate [@startDate], r.Rate [data()]160
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId161
FOR XML PATH ('Rate'), TYPE) Rates,162
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated163
FROM PortfolioLegData li WHERE li.Id = ld.Id164
FOR XML PATH (''), TYPE) END CPILegData,165
CASE WHEN ld.LegType = 'YY' THEN 166
(SELECT167
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,168
(SELECT r.StartDate [@startDate], r.Rate [data()]169
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId170
FOR XML PATH ('Rate'), TYPE) Rates171
FROM PortfolioLegData li WHERE li.Id = ld.Id172
FOR XML PATH (''), TYPE) END YYLegData173
FROM PortfolioLegData ld WHERE ld.TradeId = t.id174
FOR XML PATH ('LegData'), TYPE)175
FROM PortfolioSwaptionData sd WHERE sd.TradeId = t.id176
FOR XML PATH (''), TYPE) SwaptionData,177
(SELECT fd.ValueDate, fd.BoughtCurrency, fd.BoughtAmount, fd.SoldCurrency, fd.SoldAmount178
FROM PortfolioFxForwardData fd WHERE fd.TradeId = t.id179
FOR XML PATH (''), TYPE) FxForwardData,180
(SELECT181
(SELECT fo.OptionDataLongShort LongShort, fo.OptionDataOptionType OptionType, fo.OptionDataStyle Style, fo.OptionDataSettlement Settlement, fo.OptionDataPayOffAtExpiry PayOffAtExpiry,182
(SELECT fo.OptionDataPremiumAmount Amount, fo.OptionDataPremiumCurrency Currency, fo.OptionDataPremiumPayDate [Date]183
FOR XML PATH (''), TYPE) Premium,184
(SELECT ed.ExerciseDate185
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id186
FOR XML PATH (''), TYPE) ExerciseDates,187
(SELECT ed.ExerciseFee188
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id189
FOR XML PATH (''), TYPE) ExerciseFees,190
(SELECT ed.ExercisePrice191
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id192
FOR XML PATH (''), TYPE) ExercisePrices193
FOR XML PATH (''), TYPE) OptionData, 194
fo.BoughtCurrency, fo.BoughtAmount, fo.SoldCurrency, fo.SoldAmount195
FROM PortfolioFxOptionData fo WHERE fo.TradeId = t.id196
FOR XML PATH (''), TYPE) FxOptionData,197
(SELECT 198
cfd.LongShort,199
(SELECT 200
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,201
(SELECT202
(SELECT ln.startDate [@startDate], ln.Notional [data()]203
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id204
FOR XML PATH ('Notional'), TYPE)205
FOR XML PATH (''), TYPE) Notionals,206
(SELECT 207
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate208
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id209
FOR XML PATH (''), TYPE) Rules,210
(SELECT s.ScheduleDate [Date]211
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id212
FOR XML PATH (''), TYPE) Dates213
FOR XML PATH (''), TYPE) ScheduleData,214
CASE WHEN ld.LegType = 'Fixed' THEN 215
(SELECT 216
(SELECT r.StartDate [@startDate], r.Rate [data()]217
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId218
FOR XML PATH ('Rate'), TYPE) Rates219
FOR XML PATH (''), TYPE) END FixedLegData,220
CASE WHEN ld.LegType = 'Floating' THEN 221
(SELECT 222
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,223
(SELECT r.StartDate [@startDate], r.Spread [data()]224
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId225
FOR XML PATH ('Spread'), TYPE) Spreads,226
(SELECT r.StartDate [@startDate], r.Cap [data()]227
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId228
FOR XML PATH ('Cap'), TYPE) Caps,229
(SELECT r.StartDate [@startDate], r.[Floor] [data()]230
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId231
FOR XML PATH ('Floor'), TYPE) Floors,232
(SELECT r.StartDate [@startDate], r.Gearing [data()]233
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId234
FOR XML PATH ('Gearing'), TYPE) Gearings235
FROM PortfolioLegData li WHERE li.Id = ld.Id236
FOR XML PATH (''), TYPE) END FloatingLegData,237
CASE WHEN ld.LegType = 'CPI' THEN 238
(SELECT 239
li.CPILegIndexName [Index],240
(SELECT r.StartDate [@startDate], r.Rate [data()]241
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId242
FOR XML PATH ('Rate'), TYPE) Rates,243
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated244
FROM PortfolioLegData li WHERE li.Id = ld.Id245
FOR XML PATH (''), TYPE) END CPILegData,246
CASE WHEN ld.LegType = 'YY' THEN 247
(SELECT248
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,249
(SELECT r.StartDate [@startDate], r.Rate [data()]250
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId251
FOR XML PATH ('Rate'), TYPE) Rates252
FROM PortfolioLegData li WHERE li.Id = ld.Id253
FOR XML PATH (''), TYPE) END YYLegData254
FROM PortfolioLegData ld WHERE ld.TradeId = t.id255
FOR XML PATH ('LegData'), TYPE),256
(SELECT r.Rate257
FROM PortfolioCapRates r WHERE r.TradeId = t.Id ORDER by SeqId258
FOR XML PATH (''), TYPE) CapRates,259
(SELECT r.Rate260
FROM PortfolioFloorRates r WHERE r.TradeId = t.Id ORDER by SeqId261
FOR XML PATH (''), TYPE) FloorRates262
FROM PortfolioCapFloorData cfd WHERE cfd.TradeId = t.id263
FOR XML PATH (''), TYPE) CapFloorData,264
(SELECT265
(SELECT eo.OptionDataLongShort LongShort, eo.OptionDataOptionType OptionType, eo.OptionDataStyle Style, eo.OptionDataSettlement Settlement, eo.OptionDataPayOffAtExpiry PayOffAtExpiry,266
(SELECT eo.OptionDataPremiumAmount Amount, eo.OptionDataPremiumCurrency Currency, eo.OptionDataPremiumPayDate [Date]267
FOR XML PATH (''), TYPE) Premium,268
(SELECT ed.ExerciseDate269
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id270
FOR XML PATH (''), TYPE) ExerciseDates,271
(SELECT ed.ExerciseFee272
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id273
FOR XML PATH (''), TYPE) ExerciseFees,274
(SELECT ed.ExercisePrice275
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id276
FOR XML PATH (''), TYPE) ExercisePrices277
FOR XML PATH (''), TYPE) OptionData,278
eo.Name, eo.Currency, eo.Strike, eo.Quantity279
FROM PortfolioEquityOptionData eo WHERE eo.TradeId = t.id280
FOR XML PATH (''), TYPE) EquityOptionData,281
(SELECT ed.LongShort, ed.Maturity, ed.Name, ed.Currency, ed.Strike, ed.Quantity282
FROM PortfolioEquityForwardData ed WHERE ed.TradeId = t.id283
FOR XML PATH (''), TYPE) EquityForwardData,284
(SELECT b.IssuerId, b.CreditCurveId, b.SecurityId, b.ReferenceCurveId, b.SettlementDays, b.Calendar, b.IssueDate,285
(SELECT 286
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,287
(SELECT288
(SELECT ln.startDate [@startDate], ln.Notional [data()]289
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id290
FOR XML PATH ('Notional'), TYPE),291
CASE WHEN (SELECT COUNT(li.FXresetForeignCurrency) FROM PortfolioLegData li WHERE li.Id =ld.Id) > 0 THEN (SELECT li.FXresetForeignCurrency ForeignCurrency, li.FXresetForeignAmount ForeignAmount, li.FXresetFXIndex FXIndex, li.FXresetFixingDays FixingDays292
FROM PortfolioLegData li WHERE li.Id =ld.Id293
FOR XML PATH ('FXReset'), TYPE) ELSE '' END,294
(SELECT li.NotionalInitialExchange InitialExchange, li.NotionalAmortizingExchange AmortizingExchange, li.NotionalFinalExchange FinalExchange295
FROM PortfolioLegData li WHERE li.Id =ld.Id296
FOR XML PATH (''), TYPE) Exchanges297
FOR XML PATH (''), TYPE) Notionals,298
(SELECT 299
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate300
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id301
FOR XML PATH (''), TYPE) Rules,302
(SELECT s.ScheduleDate [Date]303
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id304
FOR XML PATH (''), TYPE) Dates305
FOR XML PATH (''), TYPE) ScheduleData,306
CASE WHEN ld.LegType = 'Fixed' THEN 307
(SELECT 308
(SELECT r.StartDate [@startDate], r.Rate [data()]309
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId310
FOR XML PATH ('Rate'), TYPE) Rates311
FOR XML PATH (''), TYPE) END FixedLegData,312
CASE WHEN ld.LegType = 'Floating' THEN 313
(SELECT 314
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,315
(SELECT r.StartDate [@startDate], r.Spread [data()]316
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId317
FOR XML PATH ('Spread'), TYPE) Spreads,318
(SELECT r.StartDate [@startDate], r.Cap [data()]319
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId320
FOR XML PATH ('Cap'), TYPE) Caps,321
(SELECT r.StartDate [@startDate], r.[Floor] [data()]322
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId323
FOR XML PATH ('Floor'), TYPE) Floors,324
(SELECT r.StartDate [@startDate], r.Gearing [data()]325
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId326
FOR XML PATH ('Gearing'), TYPE) Gearings327
FROM PortfolioLegData li WHERE li.Id = ld.Id328
FOR XML PATH (''), TYPE) END FloatingLegData,329
CASE WHEN ld.LegType = 'CPI' THEN 330
(SELECT 331
li.CPILegIndexName [Index],332
(SELECT r.StartDate [@startDate], r.Rate [data()]333
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId334
FOR XML PATH ('Rate'), TYPE) Rates,335
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated336
FROM PortfolioLegData li WHERE li.Id = ld.Id337
FOR XML PATH (''), TYPE) END CPILegData,338
CASE WHEN ld.LegType = 'YY' THEN 339
(SELECT340
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,341
(SELECT r.StartDate [@startDate], r.Rate [data()]342
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId343
FOR XML PATH ('Rate'), TYPE) Rates344
FROM PortfolioLegData li WHERE li.Id = ld.Id345
FOR XML PATH (''), TYPE) END YYLegData346
FROM PortfolioLegData ld WHERE ld.TradeId = t.id347
FOR XML PATH ('LegData'), TYPE)348
FROM PortfolioBondData b WHERE b.TradeId = t.id349
FOR XML PATH (''), TYPE) BondData,350
(SELECT cd.IssuerId, cd.CreditCurveId, cd.SettlesAccrual, cd.PaysAtDefaultTime, cd.ProtectionStart, cd.UpfrontDate, cd.UpfrontFee,351
(SELECT 352
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,353
(SELECT354
(SELECT ln.startDate [@startDate], ln.Notional [data()]355
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id356
FOR XML PATH ('Notional'), TYPE),357
CASE WHEN (SELECT COUNT(li.FXresetForeignCurrency) FROM PortfolioLegData li WHERE li.Id =ld.Id) > 0 THEN (SELECT li.FXresetForeignCurrency ForeignCurrency, li.FXresetForeignAmount ForeignAmount, li.FXresetFXIndex FXIndex, li.FXresetFixingDays FixingDays358
FROM PortfolioLegData li WHERE li.Id =ld.Id359
FOR XML PATH ('FXReset'), TYPE) ELSE '' END,360
(SELECT li.NotionalInitialExchange InitialExchange, li.NotionalAmortizingExchange AmortizingExchange, li.NotionalFinalExchange FinalExchange361
FROM PortfolioLegData li WHERE li.Id =ld.Id362
FOR XML PATH (''), TYPE) Exchanges363
FOR XML PATH (''), TYPE) Notionals,364
(SELECT 365
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate366
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id367
FOR XML PATH (''), TYPE) Rules,368
(SELECT s.ScheduleDate [Date]369
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id370
FOR XML PATH (''), TYPE) Dates371
FOR XML PATH (''), TYPE) ScheduleData,372
CASE WHEN ld.LegType = 'Fixed' THEN 373
(SELECT 374
(SELECT r.StartDate [@startDate], r.Rate [data()]375
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId376
FOR XML PATH ('Rate'), TYPE) Rates377
FOR XML PATH (''), TYPE) END FixedLegData,378
CASE WHEN ld.LegType = 'Floating' THEN 379
(SELECT 380
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,381
(SELECT r.StartDate [@startDate], r.Spread [data()]382
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId383
FOR XML PATH ('Spread'), TYPE) Spreads,384
(SELECT r.StartDate [@startDate], r.Cap [data()]385
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId386
FOR XML PATH ('Cap'), TYPE) Caps,387
(SELECT r.StartDate [@startDate], r.[Floor] [data()]388
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId389
FOR XML PATH ('Floor'), TYPE) Floors,390
(SELECT r.StartDate [@startDate], r.Gearing [data()]391
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId392
FOR XML PATH ('Gearing'), TYPE) Gearings393
FROM PortfolioLegData li WHERE li.Id = ld.Id394
FOR XML PATH (''), TYPE) END FloatingLegData,395
CASE WHEN ld.LegType = 'CPI' THEN 396
(SELECT 397
li.CPILegIndexName [Index],398
(SELECT r.StartDate [@startDate], r.Rate [data()]399
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId400
FOR XML PATH ('Rate'), TYPE) Rates,401
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated402
FROM PortfolioLegData li WHERE li.Id = ld.Id403
FOR XML PATH (''), TYPE) END CPILegData,404
CASE WHEN ld.LegType = 'YY' THEN 405
(SELECT406
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,407
(SELECT r.StartDate [@startDate], r.Rate [data()]408
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId409
FOR XML PATH ('Rate'), TYPE) Rates410
FROM PortfolioLegData li WHERE li.Id = ld.Id411
FOR XML PATH (''), TYPE) END YYLegData412
FROM PortfolioLegData ld WHERE ld.TradeId = t.id413
FOR XML PATH ('LegData'), TYPE)414
FROM PortfolioCreditDefaultSwapData cd WHERE cd.TradeId = t.id415
FOR XML PATH (''), TYPE) CreditDefaultSwapData,416
(SELECT id.CreditCurveId, id.SettlesAccrual, id.PaysAtDefaultTime, id.ProtectionStart, id.UpfrontDate, id.UpfrontFee,417
(SELECT 418
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,419
(SELECT420
(SELECT ln.startDate [@startDate], ln.Notional [data()]421
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id422
FOR XML PATH ('Notional'), TYPE),423
CASE WHEN (SELECT COUNT(li.FXresetForeignCurrency) FROM PortfolioLegData li WHERE li.Id =ld.Id) > 0 THEN (SELECT li.FXresetForeignCurrency ForeignCurrency, li.FXresetForeignAmount ForeignAmount, li.FXresetFXIndex FXIndex, li.FXresetFixingDays FixingDays424
FROM PortfolioLegData li WHERE li.Id =ld.Id425
FOR XML PATH ('FXReset'), TYPE) ELSE '' END,426
(SELECT li.NotionalInitialExchange InitialExchange, li.NotionalAmortizingExchange AmortizingExchange, li.NotionalFinalExchange FinalExchange427
FROM PortfolioLegData li WHERE li.Id =ld.Id428
FOR XML PATH (''), TYPE) Exchanges429
FOR XML PATH (''), TYPE) Notionals,430
(SELECT 431
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate432
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id433
FOR XML PATH (''), TYPE) Rules,434
(SELECT s.ScheduleDate [Date]435
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id436
FOR XML PATH (''), TYPE) Dates437
FOR XML PATH (''), TYPE) ScheduleData,438
CASE WHEN ld.LegType = 'Fixed' THEN 439
(SELECT 440
(SELECT r.StartDate [@startDate], r.Rate [data()]441
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId442
FOR XML PATH ('Rate'), TYPE) Rates443
FOR XML PATH (''), TYPE) END FixedLegData,444
CASE WHEN ld.LegType = 'Floating' THEN 445
(SELECT 446
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,447
(SELECT r.StartDate [@startDate], r.Spread [data()]448
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId449
FOR XML PATH ('Spread'), TYPE) Spreads,450
(SELECT r.StartDate [@startDate], r.Cap [data()]451
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId452
FOR XML PATH ('Cap'), TYPE) Caps,453
(SELECT r.StartDate [@startDate], r.[Floor] [data()]454
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId455
FOR XML PATH ('Floor'), TYPE) Floors,456
(SELECT r.StartDate [@startDate], r.Gearing [data()]457
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId458
FOR XML PATH ('Gearing'), TYPE) Gearings459
FROM PortfolioLegData li WHERE li.Id = ld.Id460
FOR XML PATH (''), TYPE) END FloatingLegData,461
CASE WHEN ld.LegType = 'CPI' THEN 462
(SELECT 463
li.CPILegIndexName [Index],464
(SELECT r.StartDate [@startDate], r.Rate [data()]465
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId466
FOR XML PATH ('Rate'), TYPE) Rates,467
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated468
FROM PortfolioLegData li WHERE li.Id = ld.Id469
FOR XML PATH (''), TYPE) END CPILegData,470
CASE WHEN ld.LegType = 'YY' THEN 471
(SELECT472
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,473
(SELECT r.StartDate [@startDate], r.Rate [data()]474
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId475
FOR XML PATH ('Rate'), TYPE) Rates476
FROM PortfolioLegData li WHERE li.Id = ld.Id477
FOR XML PATH (''), TYPE) END YYLegData478
FROM PortfolioLegData ld WHERE ld.TradeId = t.id479
FOR XML PATH ('LegData'), TYPE),480
(SELECT ba.IssuerId, ba.CreditCurveId, ba.Notional, ba.Currency481
FROM PortfolioBaskets ba WHERE ba.TradeId = t.id482
FOR XML PATH ('Name'), TYPE) Basket483
FROM PortfolioIndexCreditDefaultSwapData id WHERE id.TradeId = t.id484
FOR XML PATH (''), TYPE) IndexCreditDefaultSwapData,485
(SELECT iod.KnockOut,486
(SELECT iod.OptionDataLongShort LongShort, iod.OptionDataOptionType OptionType, iod.OptionDataStyle Style, iod.OptionDataSettlement Settlement, iod.OptionDataPayOffAtExpiry PayOffAtExpiry,487
(SELECT iod.OptionDataPremiumAmount Amount, iod.OptionDataPremiumCurrency Currency, iod.OptionDataPremiumPayDate [Date]488
FOR XML PATH (''), TYPE) Premium,489
(SELECT ed.ExerciseDate490
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id491
FOR XML PATH (''), TYPE) ExerciseDates,492
(SELECT ed.ExerciseFee493
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id494
FOR XML PATH (''), TYPE) ExerciseFees,495
(SELECT ed.ExercisePrice496
FROM PortfolioOptionExercises ed WHERE ed.TradeId = t.id497
FOR XML PATH (''), TYPE) ExercisePrices498
FOR XML PATH (''), TYPE) OptionData,499
(SELECT id.CreditCurveId, id.SettlesAccrual, id.PaysAtDefaultTime, id.ProtectionStart, id.UpfrontDate, id.UpfrontFee,500
(SELECT 501
ld.LegType, ld.Payer, ld.Currency, ld.DayCounter, ld.PaymentConvention,502
(SELECT503
(SELECT ln.startDate [@startDate], ln.Notional [data()]504
FROM PortfolioLegNotionals ln WHERE ln.LegDataId =ld.Id505
FOR XML PATH ('Notional'), TYPE),506
CASE WHEN (SELECT COUNT(li.FXresetForeignCurrency) FROM PortfolioLegData li WHERE li.Id =ld.Id) > 0 THEN (SELECT li.FXresetForeignCurrency ForeignCurrency, li.FXresetForeignAmount ForeignAmount, li.FXresetFXIndex FXIndex, li.FXresetFixingDays FixingDays507
FROM PortfolioLegData li WHERE li.Id =ld.Id508
FOR XML PATH ('FXReset'), TYPE) ELSE '' END,509
(SELECT li.NotionalInitialExchange InitialExchange, li.NotionalAmortizingExchange AmortizingExchange, li.NotionalFinalExchange FinalExchange510
FROM PortfolioLegData li WHERE li.Id =ld.Id511
FOR XML PATH (''), TYPE) Exchanges512
FOR XML PATH (''), TYPE) Notionals,513
(SELECT 514
(SELECT s.StartDate, s.EndDate, s.Tenor, s.Calendar, s.Convention, s.TermConvention, s.RuleName [Rule], ISNULL(s.EndOfMonth,'') EndOfMonth, ISNULL(convert(varchar,s.FirstDate,112),'') FirstDate, ISNULL(convert(varchar,s.LastDate,112),'') LastDate515
FROM PortfolioScheduleDataRules s WHERE s.LegDataId = ld.Id516
FOR XML PATH (''), TYPE) Rules,517
(SELECT s.ScheduleDate [Date]518
FROM PortfolioScheduleDataDates s WHERE s.LegDataId = ld.Id519
FOR XML PATH (''), TYPE) Dates520
FOR XML PATH (''), TYPE) ScheduleData,521
CASE WHEN ld.LegType = 'Fixed' THEN 522
(SELECT 523
(SELECT r.StartDate [@startDate], r.Rate [data()]524
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId525
FOR XML PATH ('Rate'), TYPE) Rates526
FOR XML PATH (''), TYPE) END FixedLegData,527
CASE WHEN ld.LegType = 'Floating' THEN 528
(SELECT 529
li.FloatingLegFixingDays FixingDays, li.FloatingLegIndexName [Index], li.FloatingLegIsInArrears IsInArrears, li.FloatingLegIsNotResettingXCCY IsNotResettingXCCY,530
(SELECT r.StartDate [@startDate], r.Spread [data()]531
FROM PortfolioFloatingLegSpreads r WHERE r.LegDataId = ld.Id ORDER by SeqId532
FOR XML PATH ('Spread'), TYPE) Spreads,533
(SELECT r.StartDate [@startDate], r.Cap [data()]534
FROM PortfolioFloatingLegCaps r WHERE r.LegDataId = ld.Id ORDER by SeqId535
FOR XML PATH ('Cap'), TYPE) Caps,536
(SELECT r.StartDate [@startDate], r.[Floor] [data()]537
FROM PortfolioFloatingLegFloors r WHERE r.LegDataId = ld.Id ORDER by SeqId538
FOR XML PATH ('Floor'), TYPE) Floors,539
(SELECT r.StartDate [@startDate], r.Gearing [data()]540
FROM PortfolioFloatingLegGearings r WHERE r.LegDataId = ld.Id ORDER by SeqId541
FOR XML PATH ('Gearing'), TYPE) Gearings542
FROM PortfolioLegData li WHERE li.Id = ld.Id543
FOR XML PATH (''), TYPE) END FloatingLegData,544
CASE WHEN ld.LegType = 'CPI' THEN 545
(SELECT 546
li.CPILegIndexName [Index],547
(SELECT r.StartDate [@startDate], r.Rate [data()]548
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId549
FOR XML PATH ('Rate'), TYPE) Rates,550
li.CPILegBaseCPI BaseCPI, li.CPILegObservationLag ObservationLag, li.CPILegInterpolated Interpolated551
FROM PortfolioLegData li WHERE li.Id = ld.Id552
FOR XML PATH (''), TYPE) END CPILegData,553
CASE WHEN ld.LegType = 'YY' THEN 554
(SELECT555
li.YYLegIndexName [Index], li.YYLegFixingDays FixingDays, li.YYLegObservationLag ObservationLag, li.YYLegInterpolated Interpolated,556
(SELECT r.StartDate [@startDate], r.Rate [data()]557
FROM PortfolioFixedLegCPIRates r WHERE r.LegDataId = ld.Id ORDER by SeqId558
FOR XML PATH ('Rate'), TYPE) Rates559
FROM PortfolioLegData li WHERE li.Id = ld.Id560
FOR XML PATH (''), TYPE) END YYLegData561
FROM PortfolioLegData ld WHERE ld.TradeId = t.id562
FOR XML PATH ('LegData'), TYPE),563
(SELECT ba.IssuerId, ba.CreditCurveId, ba.Notional, ba.Currency564
FROM PortfolioBaskets ba WHERE ba.TradeId = t.id ORDER by SeqId565
FOR XML PATH ('Name'), TYPE) Basket566
FROM PortfolioIndexCreditDefaultSwapOptionSwapData id WHERE id.TradeId = t.id567
FOR XML PATH (''), TYPE) IndexCreditDefaultSwapData568
FROM PortfolioIndexCreditDefaultSwapOptionData iod WHERE iod.TradeId = t.id569
FOR XML PATH (''), TYPE) IndexCreditDefaultSwapOptionData570
FROM PortfolioTrades t INNER JOIN PortfolioTradeGroupingIds pg ON pg.TradeId = t.Id AND pgo.GroupingId = pg.GroupingId571
FOR XML PATH ('Trade'), TYPE)572
FOR XML PATH ('Portfolio')) XMLData573
FROM PortfolioTradeGroupingIds pgo- PortfolioFxOptionData
- PortfolioFloatingLegGearings
- PortfolioFxForwardData
- PortfolioLegNotionals
- PortfolioCapRates
- PortfolioBaskets
- PortfolioBondData
- PortfolioTradeGroupingIds
- PortfolioSwaptionData
- PortfolioScheduleDataRules
- PortfolioFloatingLegSpreads
- PortfolioOptionExercises
- PortfolioFloorRates
- PortfolioSelection
- PortfolioLegData
- PortfolioFixedLegCPIRates
- PortfolioTrades
- PortfolioCapFloorData
- PortfolioEquityOptionData
- PortfolioCashflowDataCashflow
- PortfolioTradeActions
- PortfolioIndexCreditDefaultSwapOptionSwapData
- PortfolioScheduleDataDates
- PortfolioFloatingLegCaps
- PortfolioIndexCreditDefaultSwapOptionData
- PortfolioIndexCreditDefaultSwapData
- PortfolioEquityForwardData
- PortfolioFloatingLegFloors
- PortfolioCreditDefaultSwapData