1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285
|
ALTER PROCEDURE [dbo].[lafsp_jde_trans]
AS
BEGIN
SET NOCOUNT ON;
-- Drop temporary tables if already existing:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb..#lafsp_jde_base') IS NOT NULL DROP TABLE #lafsp_jde_base;
IF OBJECT_ID('tempdb..#flowtable') IS NOT NULL DROP TABLE #flowtable;
IF OBJECT_ID('tempdb..#basetable') IS NOT NULL DROP TABLE #basetable;
/* TEMPORARY TABLE 1 - BASETABLE */
SELECT DISTINCT
cf.deal_no,
cf.flow_no,
cf.cflow_dt,
cf.comments,
cf.flag,
cf.nett_no,
gl.amount,
gl.gl_entry_type
INTO #basetable
FROM
cflows AS cf INNER JOIN gl_entry AS gl ON
cf.deal_no = gl.deal_no AND cf.cflow_dt = gl.action_dt AND cf.cflow_no = gl.cflow_id
-- CRITERIA CURRENTLY USED FOR THE INTERFACE :
WHERE
(gl.gl_entry_type LIKE 'face%' OR
gl.gl_entry_type LIKE 'bankacc%' OR
gl.gl_entry_type LIKE 'a_wtax%' OR
gl.gl_entry_type LIKE 'a_int%' OR
gl.gl_entry_type LIKE 'bs_pay_exp' OR
gl.gl_entry_type LIKE 'bs_rec_exp' OR
gl.comment_type='10740057' ) -- UNWIND PRINCIPAL
AND (cf.trans_type IN ('SE', 'SC', 'SI'))
/* TEMPORARY TABLE 1 *CONTINUED* - BASETABLE */
INSERT INTO #basetable
SELECT DISTINCT
gl.deal_no,
MIN(bt.flow_no) AS flow_no,
gl.action_dt AS cflow_dt,
bt.comments,
gl.deal_leg AS flag,
null AS nett_no,
gl.amount,
gl.gl_entry_type
FROM
gl_entry AS gl
INNER JOIN #basetable AS bt ON gl.deal_no = bt.deal_no AND gl.action_dt < bt.cflow_dt
WHERE
(bt.comments = 'INTEREST' AND gl.gl_entry_type LIKE 'accrue INTR%')
OR (bt.comments = 'COUPON' AND gl.gl_entry_type LIKE 'accrue%')
GROUP BY gl.deal_no, gl.action_dt, bt.comments, gl.amount, gl.gl_entry_type, gl.deal_leg
/* TEMPORARY TABLE 2 - FLOWTABLE */
SELECT bt.deal_no, bt.flow_no, bt.cflow_dt, bt.comments, bt.flag, bt.nett_no, bt.amount, bt.gl_entry_type,
(SELECT MIN(v2.flow_no) FROM #basetable v2 WHERE bt.deal_no = v2.deal_no AND ((bt.flag is null AND v2.flag is null) OR bt.flag=v2.flag)) AS first_flow_no,
(SELECT MAX(v2.flow_no) FROM #basetable v2 WHERE bt.deal_no = v2.deal_no AND ((bt.flag is null AND v2.flag is null) OR bt.flag=v2.flag)) AS last_flow_no,
CASE
WHEN bt.gl_entry_type LIKE 'a_wtax%'
THEN (SELECT MAX(v2.flow_no) FROM #basetable v2 WHERE bt.deal_no = v2.deal_no AND bt.flow_no>v2.flow_no AND bt.cflow_dt>v2.cflow_dt AND not v2.gl_entry_type LIKE 'a_wtax%' AND (bt.flag is null OR bt.flag=v2.flag))
ELSE
(SELECT MAX(v2.flow_no) FROM #basetable v2 WHERE bt.deal_no = v2.deal_no AND bt.flow_no>v2.flow_no AND not v2.gl_entry_type LIKE 'a_wtax%' AND (bt.flag is null OR bt.flag=v2.flag))
END prev_flow_no
INTO #flowtable
FROM #basetable bt
/* TEMPORARY TABLE 3 - RESULT TABLE */
SELECT ft.*,
(SELECT MAX(bt.cflow_dt) FROM #basetable bt WHERE ft.deal_no=bt.deal_no AND ((ft.flag is null AND bt.flag is null) or ft.flag=bt.flag) AND ft.prev_flow_no=bt.flow_no) AS prev_flow_dt
--(SELECT MAX(bt.cflow_dt) FROM #basetable bt WHERE ft.deal_no=bt.deal_no AND ft.prev_flow_no=bt.flow_no ) AS interest_prev_flow_dt
INTO #lafsp_jde_base
FROM #flowtable ft
--ORDER BY 1,2
/* ======================= */
SELECT *
,dbo.qsp_laf_fxrate(interface_ccy,int_prev_dt,'FIXING')) AS NomColonneVoulue
FROM (
SELECT DISTINCT
ac_cp02.name AS cparty_analyse_code_jde,
ac_st02.name AS instrument_analyse_code_jde,
-- cflow delivery account's bank ana02
CASE
WHEN (deals.trans_type='AT' and deals.in_use='N') THEN (
select max(ana_inner.name)
from cflowsi cfi_inner
left outer join bankacc ba_inner on cfi_inner.bank_acc=ba_inner.acc_no
left outer join cparty cp_inner on ba_inner.cparty=cp_inner.thekey
left outer join anacode ana_inner on cp_inner.analyse02=ana_inner.thekey
where gl_entry.cflow_id=cfi_inner.cflow_no and cfi_inner.cflow_no=gl_entry.cflow_id and cfi_inner.out_dt>getdate()
)
WHEN cflows.bank_acc IS NULL THEN NULL -- cflow bank account's bank ana02
ELSE (SELECT ac.name FROM anacode ac WHERE bankacc_cparty.analyse02=ac.thekey)
END ac_bankacc_cp02,
CASE
WHEN cflows.vostro_acc IS NULL THEN NULL
ELSE (SELECT ac.name FROM anacode ac INNER JOIN cparty cp ON ac.thekey=cp.analyse02 INNER JOIN cpdelacc ssi ON ssi.cparty=cp.thekey WHERE cflows.vostro_acc=ssi.thekey)
END ac_cpdelacc_cp02,
acrl_item_post.acrl_item_id AS acrl_item_acrl_item_id,
acrl_item_post.posting_date AS acrl_item_posting_date,
bankacc_cparty.code AS cflow_bank_code,
bankacc_cparty.name AS cflow_bank_name,
bustruct.name AS entity_name,
bustruct.thekey AS entity_thekey,
CASE
WHEN deals.trans_type not in ('AC','AT') THEN NULL
ELSE (SELECT bust.name FROM bustruct bust WHERE (bust.bustruct_id=gl_entry.gl_owner_id and glsaprpt.trans_no = gl_entry.gl_entry_id))
END entity_ac_at,
cflows.comments AS cflow_comments,
CASE
WHEN cflows.match_det IS NULL THEN NULL
ELSE (SELECT MAX (cf.deal_no) FROM cflows cf WHERE cf.match_det=cflows.match_det and cf.deal_no<>cflows.deal_no and cf.bank_acc=cflows.bank_acc)
END cflow_matched_deal_no,
CASE
WHEN gl_entry.gl_entry_type LIKE 'accrue%'
THEN (select max(gl.action_dt) from gl_entry gl,cflows cf where ( gl.deal_no=cf.deal_no and gl.action_dt=cf.cflow_dt and gl.deal_leg = gl_entry.deal_leg and gl.deal_no=gl_entry.deal_no and gl.action_dt < gl_entry.action_dt))
ELSE (select max(cflow_dt) from cflows cf where (cf.deal_no=cflows.deal_no and (cf.flag=cflows.flag or (cf.flag is null and cflows.flag is null)) and cf.cflow_dt<cflows.cflow_dt))
END int_prev_dt,
cflows.flow_no AS cflow_flow_no,
chart_acc.account_type AS coa_account_type,
chart_acc.coa_code AS coa_coa_code,
chart_acc.used_by_ba AS coa_used_by_ba,
cparty.code AS cparty_code,
cpdelacc_cparty.name AS cpdelacc_bank_name,
cptype.name AS cptype_name,
deals.act_mat_dt AS deal_act_mat_dt,
deals.ccy AS deal_ccy,
deals.ccy2 AS deal_ccy2,
deals.cur_mat_dt AS deal_cur_mat_dt,
deals.deal_dt AS deal_deal_dt,
deals.deal_no AS deal_deal_no,
deals.eff_mat_dt AS deal_eff_mat_dt,
deals.external_ref_id AS deal_external_ref_id,
deals.in_use AS deal_in_use,
deals.mature_dt AS deal_mature_dt,
deals.settle_dt AS deal_settle_dt,
deals.ticket_no AS deal_ticket_no,
deals.trans_type AS deal_trans_type,
fxdeals.domccy AS fxdeal_domccy,
fxdeals.spot_rate AS fxdeal_spot_rate,
fxdeals.swap_side AS fxdeal_swap_side,
fxdeals.contr_rate AS fxdeal_contr_rate,
CASE
WHEN fxdeals.swap_link>0 THEN (select dd.mature_dt from fxdeals fx INNER JOIN deals dd ON fx.deal_no = dd.deal_no where (fx.deal_no<>fx.swap_link)and fxdeals.deal_no=fx.swap_link)
ELSE NULL
END fxdeals_other_mature_dt,
CASE
WHEN deals.trans_type='AC' THEN (SELECT acd.fx_rate FROM acdeals acd WHERE acd.deal_no=deals.deal_no)
ELSE NULL
END acdeal_fx_rate,
gl_entry.comment_type AS gl_comment_type,
gl_entry.exch_group AS gl_exch_group,
gl_entry.exch_type AS gl_exch_type,
gl_entry.gl_entry_type AS gl_gl_entry_type,
gl_entry.gl_owner_id AS gl_gl_owner_id,
gl_entry.reversal_status AS gl_reversal_status,
gl_entry.trans_type AS gl_trans_type,
glsaprpt.amount AS interface_amount,
glsaprpt.base_amt AS interface_base_amt,
glsaprpt.base_ccy AS interface_base_ccy,
glsaprpt.base_rate AS interface_base_rate,
glsaprpt.ccy AS interface_ccy,
glsaprpt.deal_no AS interface_deal_no,
glsaprpt.gl_entry_type AS interface_gl_entry_type,
glsaprpt.output_coa_code AS interface_output_coa_code,
glsaprpt.ext_accprd AS interface_ext_accprd,
glsaprpt.source_cde AS interface_source_cde,
glsaprpt.narrative AS interface_narrative,
glsaprpt.ref1 AS interface_ref1,
glsaprpt.ref2 AS interface_ref2,
glsaprpt.allocation AS interface_allocation,
glsaprpt.busarea AS interface_busarea,
glsaprpt.compcode AS interface_compcode,
glsaprpt.rptno AS interface_rptno,
glsaprpt.trans_dt AS interface_transaction_date,
glsaprpt.trans_no AS interface_transaction_no,
sectype.code AS instrument_code,
sectype.formula AS instrument_formula,
sectype.name AS instrument_name,
swdeals.exch_rate AS swdeal_exch_rate,
--interface_basetable.nett_no AS cflow_nett_no,
cflows.nett_no AS cflow_nett_no,
-- CASE WHEN interface_basetable.nett_no IS NULL THEN NULL
-- ELSE (SELECT SUM(cflows.amount) FROM cflows WHERE cflows.nett_no=interface_basetable.nett_no)
-- END cflow_total_nett_amount,
CASE
WHEN cflows.nett_no IS NULL THEN NULL
ELSE (SELECT SUM(cfl.amount) FROM cflows cfl WHERE cfl.nett_no=cflows.nett_no)
END cflow_total_nett_amount,
interface_basetable.first_flow_no AS deal_schedule_first_flow_no,
interface_basetable.flow_no AS deal_schedule_flow_no,
interface_basetable.prev_flow_no AS deal_schedule_previous_flow_no,
interface_basetable.prev_flow_dt AS deal_schedule_previous_flow_dt,
--interface_basetable.interest_prev_flow_dt AS deal_schedule_previous_int_flow_dt,
(select dbo.qsp_laf_latestAI(cflows.deal_no,cflows.trans_type)) AS AI_count,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,deals.deal_dt,'FIXING')) AS fxmult_ccy_dealdt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,deals.deal_dt,'FIXING')) AS fxmult_domccy_dealdt,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,deals.settle_dt,'FIXING')) AS fxmult_ccy_settledt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,deals.settle_dt,'FIXING')) AS fxmult_domccy_settledt,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,glsaprpt.trans_dt,'FIXING')) AS fxmult_ccy_transdt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,glsaprpt.trans_dt,'FIXING')) AS fxmult_domccy_transdt,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,interface_basetable.prev_flow_dt,'FIXING')) AS fxmult_ccy_prevflowdt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,interface_basetable.prev_flow_dt,'FIXING')) AS fxmult_domccy_prevflowdt,
--(select dbo.qsp_laf_fxrate(glsaprpt.ccy,interface_basetable.interest_prev_flow_dt,'FIXING')) AS fxmult_ccy_int_prevflowdt,
--(select dbo.qsp_laf_fxrate(fxdeals.domccy,interface_basetable.interest_prev_flow_dt,'FIXING')) AS fxmult_domccy_int_prevflowdt,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,
(select max(gl.action_dt) from gl_entry gl,cflows cf where ( gl.deal_no=cf.deal_no and gl.action_dt=cf.cflow_dt and gl.deal_leg = gl_entry.deal_leg and gl.deal_no=gl_entry.deal_no and gl.action_dt < gl_entry.action_dt and gl_entry.gl_entry_type LIKE 'accrue%')),
'FIXING')) AS fxmult_ccy_gl_prev_dt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,
(select max(gl.action_dt) from gl_entry gl,cflows cf where ( gl.deal_no=cf.deal_no and gl.action_dt=cf.cflow_dt and gl.deal_leg = gl_entry.deal_leg and gl.deal_no=gl_entry.deal_no and gl.action_dt < gl_entry.action_dt and gl_entry.gl_entry_type LIKE 'accrue%')),
'FIXING')) AS fxmult_ccy_dom_gl_prev_dt,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,
(select max(cflow_dt) from cflows cf where (cf.deal_no=cflows.deal_no and (cf.flag=cflows.flag or (cf.flag is null and cflows.flag is null)) and cf.cflow_dt<cflows.cflow_dt and gl_entry.gl_entry_type NOT LIKE 'accrue%')),
'FIXING')) AS fxmult_ccy_int_prev_dt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,
(select max(cflow_dt) from cflows cf where (cf.deal_no=cflows.deal_no and (cf.flag=cflows.flag or (cf.flag is null and cflows.flag is null)) and cf.cflow_dt<cflows.cflow_dt and gl_entry.gl_entry_type NOT LIKE 'accrue%')),
'FIXING')) AS fxmult_ccy_dom_int_prev_dt,
(select dbo.qsp_laf_fxrate(glsaprpt.ccy,deals.mature_dt,'FIXING')) AS fxmult_ccy_maturedt,
(select dbo.qsp_laf_fxrate(fxdeals.domccy,deals.mature_dt,'FIXING')) AS fxmult_domccy_maturedt
FROM
(((((((((((((((((((glsaprpt
LEFT OUTER JOIN deals
ON glsaprpt.deal_no=deals.deal_no)
LEFT OUTER JOIN chart_acc
ON glsaprpt.chart_acc_id=chart_acc.chart_acc_id)
LEFT OUTER JOIN gl_entry
ON glsaprpt.trans_no=gl_entry.gl_entry_id)
-- AND (glsaprpt.deal_no=gl_entry.deal_no) -- > Redundant query
LEFT OUTER JOIN acrl_item_post
ON gl_entry.post_item_id=acrl_item_post.acrl_item_post_id)
LEFT OUTER JOIN cflows
ON gl_entry.cflow_id=cflows.cflow_no)
--AND (gl_entry.deal_no=cflows.deal_no)) -- > Redundant query
LEFT OUTER JOIN bankacc
ON cflows.bank_acc=bankacc.acc_no)
LEFT OUTER JOIN cparty bankacc_cparty
ON bankacc.cparty=bankacc_cparty.thekey)
LEFT OUTER JOIN cpdelacc
ON cflows.vostro_acc=cpdelacc.thekey)
LEFT OUTER JOIN cparty cpdelacc_cparty
ON cpdelacc.cparty=cpdelacc_cparty.thekey)
LEFT OUTER JOIN bustruct
ON deals.entity=bustruct.thekey)
-- ON gl_entry.gl_owner_id=bustruct.bustruct_id --> Relevant if the entity is posting to a seperate business structure
LEFT OUTER JOIN sectype
ON deals.sectype=sectype.thekey)
LEFT OUTER JOIN fxdeals
ON deals.deal_no=fxdeals.deal_no)
LEFT OUTER JOIN swdeals
ON deals.deal_no=swdeals.deal_no)
LEFT OUTER JOIN acdeals
ON cflows.deal_no=acdeals.deal_no)
LEFT OUTER JOIN cparty
ON deals.cparty=cparty.thekey)
LEFT OUTER JOIN cptype
ON cparty.cptype=cptype.thekey)
LEFT OUTER JOIN anacode ac_cp02
ON cparty.analyse02=ac_cp02.thekey)
LEFT OUTER JOIN anacode ac_st02
ON sectype.analyse02=ac_st02.thekey)
LEFT OUTER JOIN #lafsp_jde_base interface_basetable
ON (gl_entry.deal_no=interface_basetable.deal_no)
AND (gl_entry.action_dt=interface_basetable.cflow_dt)
AND (gl_entry.amount=interface_basetable.amount)
AND (gl_entry.gl_entry_type=interface_basetable.gl_entry_type))
WHERE
-- glsaprpt.rptno=0 AND
-- (bustruct.name=N'zzzzzzzz' OR bustruct.name=N'zzzzzzz')
NOT (glsaprpt.output_coa_code IN ('BASE CCY POSITION','BS NOT USED','CCY POSITION','NOT USED'))
AND NOT (sectype.name=N'JDE FLOW' OR sectype.name=N'SUBSIDIARY FORECAST')
AND NOT (deals.trans_type in ('AA','AI','HR'))
) AS TMP
DROP TABLE #lafsp_jde_base
DROP TABLE #flowtable
DROP TABLE #basetable
END |
Partager