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 |
01/02/2011, 14h01
Problème Procédure stockée et passage de paramètre
00
Frédéric BROUARD
