Ne plus appeler le script de transfert de taux de change
[auf_coda_additions.git] / db / procedures / auf_p_taux.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8
9
10
11ALTER PROCEDURE [dbo].[auf_p_taux] @cExercice int
12
13
14AS
15
16
17DECLARE @cPeriode nvarchar(30)
18
19/*
20IF NOT exists( SELECT table_name
21 FROM information_schema.tables
22 WHERE table_name = 'auf_ti_taux'
23 )
24
25 CREATE TABLE auf_ti_taux(
26 code_devise varchar(12),
27 nom varchar(36),
28 nom_court varchar(20),
29 symbole varchar(4),
30 seqno smallint,
31 date datetime,
32 taux decimal(19,5),
33 periode nvarchar(30),
34 periode_num int,
35 exercice int,
36 date_prod datetime,
37 periode_cour nvarchar(30)
38 )
39
40*/
41DELETE FROM auf_ti_taux
42
43
44------------- recuperer l'exercice courant ----------------------
45
46
47
48INSERT INTO auf_ti_taux
49
50SELECT
51 cur1.code AS code_devise,
52 cur1.name AS nom,
53 cur1.sname AS nom_court,
54 cur1.symunit AS symbole,
55
56 clst1.lstseqno AS seqno,
57 clst1.curdate AS "date",
58 clst1.rate AS taux,
59
60 CASE
61 WHEN day (clst1.curdate)= 31 AND month(clst1.curdate) = 12 THEN 'Taux de clôture'
62 ELSE datename( mm, clst1.curdate)
63 END AS periode,
64 CASE
65 WHEN day (clst1.curdate)= 31 AND month(clst1.curdate) = 12 THEN 13
66 ELSE month(clst1.curdate)
67 END AS periode_num,
68 year(clst1.curdate) AS exercice, getdate(), @cPeriode
69FROM
70 oas_currency cur1,
71 oas_curlist clst1,
72 ( SELECT curcode,
73 max(curdate) curdate,
74 month(curdate) periode,
75 year(curdate) exercice
76 FROM oas_curlist
77 WHERE cmpcode = 'AUF' AND year(curdate) = @cExercice
78 GROUP BY curcode,year(curdate),month(curdate)
79 union
80 SELECT curcode,
81 max(curdate) curdate,
82 month(curdate) periode,
83 year(curdate) exercice
84 FROM oas_curlist
85 WHERE cmpcode = 'AUF' AND year(curdate) = @cExercice
86 AND month(curdate) = 12 AND day(curdate) <> 31
87 GROUP BY curcode,year(curdate),month(curdate)
88
89 ) dern_taux
90
91WHERE year(clst1.curdate) = @cExercice AND
92 cur1.cmpcode = 'AUF' AND
93 clst1.curcode = dern_taux.curcode AND
94 clst1.curdate = dern_taux.curdate
95 AND cur1.cmpcode = clst1.cmpcode
96 AND cur1.code = clst1.curcode
97 AND deldate IS NULL
98-- AND NOT ( year(clst1.curdate) = 2003 AND month(clst1.curdate) =1 AND day (clst1.curdate) = 1 AND cur1.code = 'ALL')
99-- AND NOT ( year(clst1.curdate) = 2003 AND month(clst1.curdate) =1 AND day (clst1.curdate) = 1 AND cur1.code = 'USD')
100-- AND NOT ( year(clst1.curdate) = 2003 AND month(clst1.curdate) =1 AND day (clst1.curdate) = 1 AND cur1.code = 'SYP')
101 AND cur1.code NOT IN ('KMF')
102
103
104
105SELECT top 1 @cPeriode=periode
106FROM auf_ti_taux WHERE
107periode_num <> 13 ORDER BY periode_num desc
108
109
110UPDATE auf_ti_taux
111set periode_cour = @cPeriode, taux = taux / 10000000