The CREATE TABLE AS statement:
create table rep_ifrs7_disclosure as
WITH ex
AS (SELECT value_date
, posting_date
FROM cfe.execution x
WHERE id_status = 'R'
AND value_date = (SELECT Max(value_date)
FROM cfe.execution
WHERE id_status = 'R')
AND posting_date = (SELECT Max(posting_date)
FROM cfe.execution
WHERE id_status = 'R'
AND value_date = x.value_date)),
cp
AS (SELECT b.foracid id_instrument
, b.cif_id id_counterparty
, CONVERT(a.description, VARCHAR) description
FROM risk.counterparty a
INNER JOIN cfe.ext_gam b
ON a.id_counterparty = b.cif_id
AND a.id_status = 'C')
SELECT COALESCE(cp.id_counterparty, g2.attribute_value) id_counterparty
, COALESCE(cp.description, h2.attribute_value) description
, COALESCE (j.SECTOR_CODE, (SELECT TOP 1 SECTOR_CODE
FROM cfe.EXT_SECTOR_CODE
WHERE cif_id = COALESCE(cp.id_counterparty,
g2.attribute_value))) sector
, COALESCE (j.sub_sector_code, (SELECT TOP 1 sub_sector_code
FROM cfe.EXT_SECTOR_CODE
WHERE cif_id =
COALESCE(cp.id_counterparty, g2.attribute_value))) sub_sector
, b.id_instrument
, a.id_instrument_type
, case
when a.id_instrument_type in ('loan', 'loan_bmr', 'curr_acc')
then case
when (COALESCE(cp.id_counterparty, g2.attribute_value) like
'R0%'
or COALESCE(cp.id_counterparty, g2.attribute_value) is
null)
then 'Loans to Individuals'
when f2.attribute_value IN ('CASH_W/BANKS')
then 'Cash and Balances with Banks'
when f2.attribute_value IN ('placement')
then 'Money Market Placements'
else 'Loans to Corporate Customers'
end
when a.id_instrument_type in ('sec_htm')
then 'Investment Securities Amortised Cost'
when a.id_instrument_type in ('sec_afs')
then 'Investment Securities FVOCI'
when a.id_instrument_type in ('creditline', 'guarantee')
then 'Off Balance Sheet'
end product_type
, f2.attribute_value product
, a.end_date
, case
when DATEDIFF('MONTH', ex.value_date, a.end_date) <= 1
then '1M'
when DATEDIFF('MONTH', ex.value_date, a.end_date) <= 3
then '3M'
when DATEDIFF('MONTH', ex.value_date, a.end_date) <= 6
then '6M'
when DATEDIFF('YEAR', ex.value_date, a.end_date) <= 1
then '1Y'
when DATEDIFF('YEAR', ex.value_date, a.end_date) <= 3
then '3Y'
when DATEDIFF('YEAR', ex.value_date, a.end_date) <= 5
then '5Y'
when DATEDIFF('YEAR', ex.value_date, a.end_date) <= 7
then '7Y'
when DATEDIFF('YEAR', ex.value_date, a.end_date) <= 10
then '10Y'
when DATEDIFF('YEAR', ex.value_date, a.end_date) > 10
then '>10Y'
when a.end_date is null and f2.attribute_value IN ('CASH_W/BANKS')
then '1M'
when a.end_date is null and a.id_instrument_type='curr_acc'
then '3M'
else ''
end maturity_bucket
, k2.attribute_value gl_code
, l2.attribute_value pledged
, c.amortised_cost_dirty
, c.amortised_cost_dirty_bc
, c.nominal_balance + c.RECEIVABLE_PRINCIPAL nominal_balance
, c.nominal_balance_bc
+ c.RECEIVABLE_PRINCIPAL_bc nominal_balance_bc
, c.open_commitment
, c.open_commitment_bc
, d.impairment + d.impairment_spec IMPAIRMENT
, d.impairment_bc + d.impairment_spec_bc IMPAIRMENT_BC
, d.impairment_contingent IMPAIRMENT_CONTINGENT
, d.impairment_contingent_bc IMPAIRMENT_CONTINGENT_BC
, i.overdue_days
, i.impairment_stage
, p.id_portfolio
, case
when p.id_portfolio like 'fitch_%' and impairment_stage<3
then substr(p.id_portfolio,7)
when impairment_stage = 3
then 'D'
when COALESCE(cp.description, h2.attribute_value)='CBN' OR
f2.attribute_value in ('GOVBONDS', 'TBILL')
then 'AAA'
else 'BB'
end rating_class
, decode( case
when p.id_portfolio like 'fitch_%' and impairment_stage<3
then substr(p.id_portfolio,7)
when impairment_stage = 3
then 'D'
when COALESCE(cp.description, h2.attribute_value)='CBN' OR
f2.attribute_value in ('GOVBONDS', 'TBILL')
then 'AAA'
else 'BB'
end
, 'AAA', 'Investment'
, 'AA+', 'Investment'
, 'AA', 'Investment'
, 'AA-', 'Investment'
, 'A+', 'Investment'
, 'A', 'Investment'
, 'A-', 'Investment'
, 'BBB+', 'Standard'
, 'BBB', 'Standard'
, 'BBB-', 'Standard'
, 'BB+', 'Standard'
, 'BB', 'Standard'
, 'BB-', 'Standard'
, 'B+', 'Standard'
, 'B', 'Standard'
, 'B-', 'Standard'
, 'CCC+', 'Sub-Investment'
, 'CCC', 'Sub-Investment'
, 'CCC-', 'Sub-Investment'
, 'CC', 'Sub-Investment'
, 'C', 'Sub-Investment'
, 'D', 'Sub-Investment') INVESTMENT_GRADE
FROM ex
INNER JOIN cfe.instrument_hst a
ON ex.value_date = a.value_date
AND ex.posting_date = a.posting_date
INNER JOIN cfe.instrument_ref b
ON a.id_instrument_ref = b.id_instrument_ref
LEFT JOIN cp
ON b.id_instrument = cp.id_instrument
INNER JOIN cfe.instrument_measure_balance c
ON ex.value_date = c.value_date
AND ex.posting_date = c.posting_date
AND b.id_instrument_ref = c.id_instrument_ref
AND c.asset_liability_flag = 'A'
LEFT JOIN cfe.instrument_measure_impairment d
ON ex.value_date = d.value_date
AND ex.posting_date = d.posting_date
AND b.id_instrument_ref = d.id_instrument_ref
AND d.asset_liability_flag = '9'
LEFT JOIN (cfe.instrument_attribute_hst2 f
INNER JOIN cfe.attribute_ref f1
ON f1.id_attribute_ref = f.id_attribute_ref
AND f1.id_attribute = 'product'
INNER JOIN cfe.attribute_value_ref f2
ON f2.id_attribute_value_ref =
f.id_attribute_value_ref )
ON f.value_date = ex.value_date
AND f.posting_date = ex.posting_date
AND f.id_instrument_ref = d.id_instrument_ref
LEFT JOIN (cfe.instrument_attribute_hst2 g
INNER JOIN cfe.attribute_ref g1
ON g1.id_attribute_ref = g.id_attribute_ref
AND g1.id_attribute = 'cust_id'
INNER JOIN cfe.attribute_value_ref g2
ON g2.id_attribute_value_ref =
g.id_attribute_value_ref )
ON g.value_date = ex.value_date
AND g.posting_date = ex.posting_date
AND g.id_instrument_ref = a.id_instrument_ref
LEFT JOIN (cfe.instrument_attribute_hst2 h
INNER JOIN cfe.attribute_ref h1
ON h1.id_attribute_ref = h.id_attribute_ref
AND h1.id_attribute = 'cust_name'
INNER JOIN cfe.attribute_value_ref h2
ON h2.id_attribute_value_ref =
h.id_attribute_value_ref )
ON h.value_date = ex.value_date
AND h.posting_date = ex.posting_date
AND h.id_instrument_ref = a.id_instrument_ref
LEFT JOIN (cfe.instrument_attribute_hst2 k
INNER JOIN cfe.attribute_ref k1
ON k1.id_attribute_ref = k.id_attribute_ref
AND k1.id_attribute = 'gl_code'
INNER JOIN cfe.attribute_value_ref k2
ON k2.id_attribute_value_ref =
k.id_attribute_value_ref )
ON k.value_date = ex.value_date
AND k.posting_date = ex.posting_date
AND k.id_instrument_ref = a.id_instrument_ref
LEFT JOIN (cfe.instrument_attribute_hst2 l
INNER JOIN cfe.attribute_ref l1
ON l1.id_attribute_ref = l.id_attribute_ref
AND l1.id_attribute = 'pledged'
INNER JOIN cfe.attribute_value_ref l2
ON l2.id_attribute_value_ref =
l.id_attribute_value_ref )
ON l.value_date = ex.value_date
AND l.posting_date = ex.posting_date
AND l.id_instrument_ref = a.id_instrument_ref
LEFT JOIN cfe.EXT_SECTOR_CODE j
ON b.id_instrument = j.foracid
LEFT JOIN (cfe.IMPAIRMENT_HST i
INNER JOIN cfe.portfolio_ref p
ON i.id_portfolio_ref = p.id_portfolio_ref)
ON i.id_instrument_ref = a.id_instrument_ref
AND i.valid_date = ex.value_date
WHERE ( amortised_cost_dirty < 0
OR impairment != 0
OR impairment_spec != 0
OR impairment_contingent != 0 )
AND id_instrument_type IN ( 'curr_acc', 'loan', 'loan_bmr', 'sec_htm',
'sec_afs', 'creditline', 'guarantee')
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/9e2ff513-fef6-4f80-b697-85c56334839e%40googlegroups.com.
