SELECT
SELECT
concat(c.gfrn,’_’,b.collateral_type_code) as Anchor,
concat(c.gfrn,’_’,b.collateral_type_code) as Anchor,
b.cob_date,
b.cob_date,
c.gfrn,
c.gfrn,
b.collateral_type_code,
b.collateral_type_code,
b.collateral_currency_code,
b.collateral_currency_code,
MAX(
CASE
WHEN b.re_hypothecatable=”Rehypothecatable” THEN ‘Y’
WHEN b.re_hypothecatable=”Non-Rehypothecatable” THEN ‘N’
END) as re_hypotheticable,
END) AS volatility_haircut_amount_ccy_133,
(CASE
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usd
WHEN b.collateral_currency_code=”USD” THEN .
ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
ELSE . / a.tran_to_func_ccy_conv_rate
END) AS volatility_haircut_amount_ccy_133,
END) AS ,
(CASE
(CASE
WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
WHEN b.collateral_currency_code=”USD” THEN .
ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rate
ELSE . / a.tran_to_func_ccy_conv_rate
END) AS net_collateral_value_ccy_134,
END) AS ,
(CASE
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
WHEN b.collateral_currency_code=”USD” THEN a.
ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
ELSE a. / a.tran_to_func_ccy_conv_rate
END) AS ccy_haircut_amount_ccy_156,
END) AS
END) AS correlation_haircut_amount_ccy_157
FROM gfolyrsk_cr_reporting.om_cr_bbk_collateral_native_fact a
FROM gfolyrsk_cr_reporting.om_cr_bbk_collateral_native_fact a
JOIN gfolyrsk_cr_managed.om_cr_bbk_collateral_fact b
JOIN gfolyrsk_cr_managed.om_cr_bbk_collateral_fact b
ON a.cr_banking_collateral_native_sk = b.cr_banking_collateral_sk
ON a.cr_banking_collateral_native_sk = b.cr_banking_collateral_sk
LEFT JOIN gfolyrsk_cr_managed.om_cr_facility_dim c
LEFT JOIN gfolyrsk_cr_managed.om_cr_facility_dim c
ON b.facility_id = c.facility_id
ON b.facility_id = c.facility_id
WHERE b.cob_date=”20250916″
WHERE b.cob_date=”20250916″
AND b.source_system_name=”CitiRisk Collateral”
AND b.source_system_name=”CitiRisk Collateral”
GROUP BY — Group by every item in the SELECT list
concat(c.gfrn,’_’,b.collateral_type_code),
b.cob_date,
c.gfrn,
b.collateral_type_code,
b.collateral_currency_code,
b.pre_haircut_collateral_amt_usd,
b.pre_haircut_collateral_amt_local_ccy,
a.volatility_haircut_amount_usd,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usdlogo
ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END),
b.net_held_amount,
(CASE
WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rate
END),
a.ccy_haircut_amount_usd,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END),
a.correlation_haircut_amount_usd,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.correlation_haircut_amount_usd
ELSE a.correlation_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END);
________________________________________________________________________________–
SELECT
concat(c.gfrn,’_’,b.collateral_type_code) as Anchor,
b.cob_date,
c.gfrn,
b.collateral_type_code,
b.collateral_currency_code,
b.pre_haircut_collateral_amt_usd AS gross_collateral_amount_usd_36,
b.pre_haircut_collateral_amt_local_ccy AS gross_collateral_amount_local_ccy_36,
a.volatility_haircut_amount_usd AS volatility_haircut_amount_usd_133,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usd
ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END) AS volatility_haircut_amount_ccy_133,
b.net_held_amount AS net_collateral_value_usd_134,
(CASE
WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rate
END) AS net_collateral_value_ccy_134,
a.ccy_haircut_amount_usd AS ccy_haircut_amount_usd_156,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END) AS ccy_haircut_amount_ccy_156,
a.correlation_haircut_amount_usd AS correlation_haircut_amount_usd_157,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.correlation_haircut_amount_usd
ELSE a.correlation_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END) AS correlation_haircut_amount_ccy_157
FROM gfolyrsk_cr_reporting.om_cr_bbk_collateral_native_fact a
JOIN gfolyrsk_cr_managed.om_cr_bbk_collateral_fact b
ON a.cr_banking_collateral_native_sk = b.cr_banking_collateral_sk
LEFT JOIN gfolyrsk_cr_managed.om_cr_facility_dim c
ON b.facility_id = c.facility_id
WHERE b.cob_date=”20250916″
AND b.source_system_name=”CitiRisk Collateral”
GROUP BY — Group by every item in the SELECT list
concat(c.gfrn,’_’,b.collateral_type_code),
b.cob_date,
c.gfrn,
b.collateral_type_code,
b.collateral_currency_code,
b.pre_haircut_collateral_amt_usd,
b.pre_haircut_collateral_amt_local_ccy,
a.volatility_haircut_amount_usd,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.volatility_haircut_amount_usdlogo
ELSE a.volatility_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END),
b.net_held_amount,
(CASE
WHEN b.collateral_currency_code=”USD” THEN b.net_held_amount
ELSE b.net_held_amount / a.tran_to_func_ccy_conv_rate
END),
a.ccy_haircut_amount_usd,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.ccy_haircut_amount_usd
ELSE a.ccy_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END),
a.correlation_haircut_amount_usd,
(CASE
WHEN b.collateral_currency_code=”USD” THEN a.correlation_haircut_amount_usd
ELSE a.correlation_haircut_amount_usd / a.tran_to_func_ccy_conv_rate
END);
________________________________________________________________________________–
