Trade Legs

A summary of the load process for any FX leg trades that exist.

Step 1

Load all live trades from Calypso with keyword attributes of:

Cover Trade ID
ExercisedOption
ExercisedUnder
Cover Trade ID
ExercisedOption
ExercisedUnder
SalesB2BFrom
SalesB2BTo
RolledOverFrom
RolledOverTo

View SQL

Load all trade ids and their keyword attributes

SELECT distinct 
        kw.trade_id,
        cast(keyword_value as numeric) as kw_trade_id,
        keyword_name,
        d.desk_id
FROM calypso.trade_keyword kw
INNER JOIN calypso.trade t1 ON kw.trade_id=t1.trade_id AND t1.trade_status not in ('CANCELED','TERMINATED')
INNER JOIN calypso.trade t2 ON kw.keyword_value=cast(t2.trade_id as char) AND t2.trade_status not in ('CANCELED','TERMINATED')
LEFT JOIN
(
SELECT trade_id,
       cast(keyword_value as numeric) as desk_id
FROM calypso.trade_keyword
WHERE keyword_name = 'OwningDeskBU'
) d ON kw.trade_id=d.trade_id

WHERE keyword_name in ('Cover Trade ID', 'ExercisedOption', 'ExercisedUnder',
						'SalesB2BFrom', 'SalesB2BTo', 'RolledOverFrom', 'RolledOverTo')
AND isnumeric(kw.keyword_value)=1
AND isnumeric(kw.trade_id)=1

UNION

SELECT t.trade_id,
        ft.parent_id as kw_trade_id,
        'TakeUpParent' as keyword_name,
        d.desk_id
FROM calypso.trade t
INNER JOIN calypso.product_desc p ON t.product_id=p.product_id
INNER JOIN calypso.product_fx_takeup ft ON p.product_id=ft.product_id
LEFT JOIN
(
SELECT trade_id,
       cast(keyword_value as numeric) as desk_id
FROM calypso.trade_keyword
WHERE keyword_name = 'OwningDeskBU'
) d ON t.trade_id=d.trade_id

WHERE t.trade_status not in ('CANCELED','TERMINATED')

Step 2

From Calypso load all trade ids with their covering trades into a temporary database table

View SQL

Load Cover Trades

CREATE TABLE #tmp_cover_trades
(
	trade_id numeric(20) not null,
	cov_trade_id numeric(20) null
)
INSERT INTO
	#tmp_cover_trades
	(trade_id, cov_trade_id)
SELECT
	kw_tr.trade_id,
	cov_tr.cov_trade_id
FROM
	(
		SELECT
			distinct case
				when tcc.cpty_id is not null
				OR tr.cpty_ext_yn = 'N' then tk.kw_trade_id
				else tk.trade_id
			end as trade_id
		FROM
			dbo.stage_calypso_trade_keywords tk
			LEFT JOIN dbo.vw_stage_load_trade_legs_trades tr ON tk.trade_id = tr.trade_id
			LEFT JOIN dbo.dw_covering_counterparties tcc ON tr.cpty_id = tcc.cpty_id
			AND tr.desk_id = tcc.desk_id
	) kw_tr
	LEFT JOIN (
		SELECT
			distinct case
				when tcc.cpty_id is not null
				OR tr.cpty_ext_yn = 'N' then tk.kw_trade_id
				else tk.trade_id
			end as trade_id,
			case
				when tcc.cpty_id is not null
				OR tr.cpty_ext_yn = 'N' then tk.trade_id
				else tk.kw_trade_id
			end as cov_trade_id
		FROM
			dbo.stage_calypso_trade_keywords tk
			LEFT JOIN dbo.vw_stage_load_trade_legs_trades tr ON tk.trade_id = tr.trade_id
			LEFT JOIN dbo.dw_covering_counterparties tcc ON tr.cpty_id = tcc.cpty_id
			AND tr.desk_id = tcc.desk_id
		WHERE
			tk.kw_name = 'Cover Trade ID'
		UNION
		SELECT
			distinct case
				when tcc.cpty_id is not null
				OR tr.cpty_ext_yn = 'N' then tk.trade_id
				else tk.kw_trade_id
			end as trade_id,
			case
				when tcc.cpty_id is not null
				OR tr.cpty_ext_yn = 'N' then tk.kw_trade_id
				else tk.trade_id
			end as cov_trade_id
		FROM
			dbo.stage_calypso_trade_keywords tk
			LEFT JOIN dbo.vw_stage_load_trade_legs_trades tr ON tk.trade_id = tr.trade_id
			LEFT JOIN dbo.dw_covering_counterparties tcc ON tr.cpty_id = tcc.cpty_id
			AND tr.desk_id = tcc.desk_id
		WHERE
			tk.kw_name = 'Cover Trade ID'
	) cov_tr ON kw_tr.trade_id = cov_tr.trade_id 
    
    create index idx_tmp_cover_trades_trade_id on #tmp_cover_trades (trade_id)
	create index idx_tmp_cover_trades_cov_trade_id on #tmp_cover_trades (cov_trade_id)

Step 3

Denormalise the data so each keyword has a column

Click this image to view an example

View SQL

Load production table dw_trade_legs_denorm

INSERT INTO
	dbo.dw_trade_legs_denorm (
		trade_id,
		cpty_trade_yn,
		trade_bundle_id,
		trade_desk_id,
		trade_product,
		cov_trade_id,
		cov_trade_desk_id,
		cov_trade_product,
		b2bf_trade_id,
		b2bf_trade_desk_id,
		b2bf_trade_product,
		b2b2_trade_id,
		b2b2_trade_desk_id,
		b2b2_trade_product,
		xopt_trade_id,
		xopt_trade_desk_id,
		xopt_trade_product,
		xund_trade_id,
		xund_trade_desk_id,
		xund_trade_product,
		rof_trade_id,
		rof_trade_desk_id,
		rof_trade_product,
		rot_trade_id,
		rot_trade_desk_id,
		rot_trade_product,
		tup_trade_id,
		tup_trade_desk_id,
		tup_trade_product,
		source_lk_trade_id,
		source_lk_trade_bundle_id,
		source_lk_trade_desk_id,
		source_lk_cov_trade_id,
		source_lk_cov_trade_desk_id,
		source_lk_b2bf_trade_id,
		source_lk_b2bf_trade_desk_id,
		source_lk_b2b2_trade_id,
		source_lk_b2b2_trade_desk_id,
		source_lk_xopt_trade_id,
		source_lk_xopt_trade_desk_id,
		source_lk_xund_trade_id,
		source_lk_xund_trade_desk_id,
		source_lk_rof_trade_id,
		source_lk_rof_trade_desk_id,
		source_lk_rot_trade_id,
		source_lk_rot_trade_desk_id,
		source_lk_tup_trade_id,
		source_lk_tup_trade_desk_id,
		system_id
	)
SELECT
	cov_tr.trade_id,
	cov_tr.cpty_trade_yn,
	cov_tr.trade_bundle_id,
	cov_tr.trade_desk_id,
	cov_tr.trade_product,
	cov_tr.cov_trade_id,
	cov_tr.cov_trade_desk_id,
	cov_tr.cov_trade_product,
	btbf_tr.b2bf_trade_id,
	btbf_tr.b2bf_trade_desk_id,
	btbf_tr.b2bf_trade_product,
	btb2_tr.b2b2_trade_id,
	btb2_tr.b2b2_trade_desk_id,
	btb2_tr.b2b2_trade_product,
	xopt_tr.xopt_trade_id,
	xopt_tr.xopt_trade_desk_id,
	xopt_tr.xopt_trade_product,
	xund_tr.xund_trade_id,
	xund_tr.xund_trade_desk_id,
	xund_tr.xund_trade_product,
	rof_tr.rof_trade_id,
	rof_tr.rof_trade_desk_id,
	rof_tr.rof_trade_product,
	rot_tr.rot_trade_id,
	rot_tr.rot_trade_desk_id,
	rot_tr.rot_trade_product,
	tup_tr.tup_trade_id,
	tup_tr.tup_trade_desk_id,
	tup_tr.tup_trade_product
FROM
	(
		SELECT
			distinct ct.trade_id,
			tr.cpty_ext_yn as cpty_trade_yn,
			tr.bundle_id as trade_bundle_id,
			tr.desk_id as trade_desk_id,
			tr.product_type as trade_product,
			ct.cov_trade_id,
			ctr.desk_id as cov_trade_desk_id,
			ctr.product_type as cov_trade_product
		FROM
			#tmp_cover_trades ct
			LEFT JOIN dbo.vw_stage_load_trade_legs_trades tr ON ct.trade_id = tr.trade_id
			LEFT JOIN dbo.vw_stage_load_trade_legs_trades ctr ON ct.cov_trade_id = ctr.trade_id
	) cov_tr
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as b2bf_trade_id,
			kt.desk_id as b2bf_trade_desk_id,
			tr.product_type as b2bf_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kt.kw_name = 'SalesB2BFrom'
	) btbf_tr ON cov_tr.trade_id = btbf_tr.trade_id
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as b2b2_trade_id,
			kt.desk_id as b2b2_trade_desk_id,
			tr.product_type as b2b2_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kt.kw_name = 'SalesB2BTo'
	) btb2_tr ON cov_tr.trade_id = btb2_tr.trade_id
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as xopt_trade_id,
			kt.desk_id as xopt_trade_desk_id,
			tr.product_type as xopt_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kt.kw_name = 'ExercisedOption'
	) xopt_tr ON cov_tr.trade_id = xopt_tr.trade_id
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as xund_trade_id,
			kt.desk_id as xund_trade_desk_id,
			tr.product_type as xund_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kt.kw_name = 'ExercisedUnder'
	) xund_tr ON cov_tr.trade_id = xund_tr.trade_id
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as rof_trade_id,
			kt.desk_id as rof_trade_desk_id,
			tr.product_type as rof_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kt.kw_name = 'RolledOverFrom'
	) rof_tr ON cov_tr.trade_id = rof_tr.trade_id
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as rot_trade_id,
			kt.desk_id as rot_trade_desk_id,
			tr.product_type as rot_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kw_name = 'RolledOverTo'
	) rot_tr ON cov_tr.trade_id = rot_tr.trade_id
	LEFT JOIN (
		SELECT
			kt.trade_id,
			kt.kw_trade_id as tup_trade_id,
			kt.desk_id as tup_trade_desk_id,
			tr.product_type as tup_trade_product
		FROM
			dbo.stage_calypso_trade_keywords kt
			INNER JOIN dbo.vw_stage_load_trade_legs_trades tr ON kt.trade_id = tr.trade_id
		WHERE
			kt.kw_name = 'TakeUpParent'
	) tup_tr ON cov_tr.trade_id = tup_tr.trade_id

Step 4

Load production trade legs table

View SQL

Load production table dw_trade_legs

TRUNCATE TABLE dbo.dw_trade_legs
INSERT INTO
	dbo.dw_trade_legs (
		bundle_id,
		trade_id,
		trade_desk_id,
		trade_product,
		trade_leg_id,
		trade_leg_desk_id,
		trade_leg_product
	)
SELECT
	trade_bundle_id,
	trade_id,
	trade_desk_id,
	trade_product,
	cov_trade_id as trade_leg_id,
	cov_trade_desk_id as trade_leg_desk_id,
	cov_trade_product as trade_leg_product
FROM
	dbo.dw_trade_legs_denorm
WHERE
	cpty_trade_yn = 'Y'
	AND cov_trade_id is not null
	AND xopt_trade_id is null
	AND xund_trade_id is null
	AND trade_desk_id = coalesce(cov_trade_desk_id, 0)
UNION
SELECT
	trade_bundle_id,
	trade_id,
	trade_desk_id,
	trade_product,
	b2bf_trade_id,
	b2bf_trade_desk_id,
	b2bf_trade_product
FROM
	dbo.dw_trade_legs_denorm
WHERE
	cpty_trade_yn = 'Y'
	AND b2bf_trade_id is not null
	AND xopt_trade_id is null
	AND xund_trade_id is null
	AND trade_desk_id = coalesce(b2bf_trade_desk_id, 0)
UNION
SELECT
	trade_bundle_id,
	trade_id,
	trade_desk_id,
	trade_product,
	b2b2_trade_id,
	b2b2_trade_desk_id,
	b2b2_trade_product
FROM
	dbo.dw_trade_legs_denorm
WHERE
	cpty_trade_yn = 'Y'
	AND b2b2_trade_id is not null
	AND xopt_trade_id is null
	AND xund_trade_id is null
	AND trade_desk_id = coalesce(b2b2_trade_desk_id, 0)
UNION
SELECT
	tlf.trade_bundle_id,
	tlf.trade_id,
	tlf.trade_desk_id,
	tlf.trade_product,
	tlf3.xund_trade_id,
	tlf3.xund_trade_desk_id,
	tlf3.xund_trade_product
FROM
	dbo.dw_trade_legs_denorm tlf
	LEFT JOIN dbo.dw_trade_legs_denorm tlf2 ON tlf.xopt_trade_id = tlf2.trade_id
	AND tlf.xopt_trade_desk_id = tlf2.trade_desk_id
	LEFT JOIN dbo.dw_trade_legs_denorm tlf3 ON tlf2.cov_trade_id = tlf3.trade_id
	AND tlf2.cov_trade_desk_id = tlf3.trade_desk_id
WHERE
	tlf.cpty_trade_yn = 'Y'
	AND tlf.xopt_trade_id is not null
	AND tlf.trade_desk_id = coalesce(tlf2.cov_trade_desk_id, 0)
	and tlf3.xund_trade_id is not null
UNION
SELECT
	trade_bundle_id,
	trade_id,
	trade_desk_id,
	trade_product,
	cov_trade_id,
	cov_trade_desk_id,
	cov_trade_product
FROM
	dbo.dw_trade_legs_denorm
WHERE
	cpty_trade_yn = 'Y'
	AND xund_trade_id is not null
	AND trade_desk_id = coalesce(cov_trade_desk_id, 0)
UNION
SELECT
	tr.bundle_id,
	tr.trade_id,
	tr.desk_id,
	tr.product_type,
	tr2.trade_id,
	tr2.desk_id,
	tr2.product_type
FROM
	dbo.vw_stage_load_trade_legs_trades tr
	INNER JOIN dbo.vw_stage_load_trade_legs_trades tr2 ON tr.bundle_id = tr2.bundle_id
	AND tr.desk_id = tr2.desk_id
WHERE
	tr.cpty_ext_yn = 'Y'
	AND tr.bundle_id is not null
UNION
SELECT
	tlf.trade_bundle_id,
	tlf.trade_id,
	tlf.trade_desk_id,
	tlf.trade_product,
	tlf3.xund_trade_id,
	tlf3.xund_trade_desk_id,
	tlf3.xund_trade_product
FROM
	dbo.dw_trade_legs_denorm tlf
	LEFT JOIN dbo.dw_trade_legs_denorm tlf2 ON tlf.xopt_trade_id = tlf2.trade_id
	AND tlf.xopt_trade_desk_id = tlf2.trade_desk_id
	LEFT JOIN dbo.dw_trade_legs_denorm tlf3 ON tlf2.trade_bundle_id = tlf3.trade_bundle_id
	AND tlf2.trade_desk_id = tlf3.trade_desk_id
WHERE
	tlf.cpty_trade_yn = 'Y'
	AND tlf.xopt_trade_id is not null
	AND tlf.trade_id <> tlf3.xund_trade_id