1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| xmlelement(name "BatchIdentifier",'test25'),
xmlagg(
xmlelement(
name "Contract",xmlelement(name "ContractCode",a.ContractCode),
xmlelement(name "ContractData", xmlforest(a.ConsentCode as "ConsentCode", a.PhaseOfContract as "PhaseOfContract", a.ContractStatus as "ContractStatus",
TypeOfContract "TypeOfContract", case when PurposeOfFinancing is null then 'Other' else PurposeOfFinancing end as "PurposeOfFinancing", CurrencyOfContract "CurrencyOfContract"),
xmlelement(name "TotalAmount",xmlforest(replace(abs(a.TotalAmount),',','.') "Value",CurrencyOfContract "Currency")),
xmlelement(name "OutstandingAmount",xmlforest(replace(case when a.phaseofcontract='Open' then abs(a.OutstandingAmount) else 0 end, ',','.') "Value",CurrencyOfContract "Currency")),
xmlelement(name "PastDueAmount",xmlforest(replace(abs(PastDueAmount),',','.') "Value",CurrencyOfContract "Currency")),
xmlelement(name "PastDueDays", PastDueDays),
xmlelement(name "NumberOfDueInstallments",NumberOfDueInstallments),
xmlelement(name "TotalMonthlyPayment",xmlforest(replace(abs(TotalMonthlyPayment),',','.') "Value",CurrencyOfContract "Currency")),
xmlelement(name "PaymentPeriodicity",PaymentPeriodicity),
xmlelement(name "StartDate",a.StartDate),
case when a.RealEndDate is not null then
(select
xmlelement(name "RealEndDate",aa.RealEndDate)
from bic_credits aa where aa.ContractCode=a.ContractCode
) end
)
)
) |
