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
|
CREATE PROCEDURE dbo.insertTrade(
@toto varchar(50),
@titi varchar(100),
@code varchar(33),
@date smalldatetime,
@amount numeric(15,5)
)
AS
declare @idFund numeric(7)
declare @idtoto numeric(7)
declare @idtiti numeric(7)
declare @idcompo numeric(7)
declare @idnewcompo numeric(7)
declare @localtran integer
declare @localerr integer
BEGIN
select @idtoto=IdTransferAgent from TRetroTransferAgent where Name=@toto
select @idtiti=IdSubAccount from TRetroSubAccount where Name=@titi and IdTransferAgent=@idtoto
select @idFund=u.idFund from TUdl u, TCountry c where c.IdCountry=u.IdCountry and c.CountryCode = SUBSTRING(@code,1,2) and u.UdlMnemo = SUBSTRING(@code,4,CHAR_LENGTH(@code))
if @idtoto is null or @idtiti is null or @idFund is null
begin
select "toto or titi or Fund incorrect" as Result
return
end
print 'Idtoto=%1!,Idtiti=%2!,idFund=%3!', @idtoto,@idtiti,@idFund
select @localtran = @@trancount
if @localtran =0
begin tran
select @idcompo=IdCompo from TRetroSubAccountCompo where IdSubAccount=@idtiti and date=@date
if @idcompo is null
begin
print 'No compo at the trade date'
--ajout d'une nouvelle compo en date de trade
print 'Creating a new compo at the trade date'
insert into TRetroSubAccountCompo(IdSubAccount,date) values (@idtiti,@date)
select @localerr = @@error
if @localerr != 0
begin
goto FIN
end
select @idnewcompo=IdCompo from TRetroSubAccountCompo where IdSubAccount=@idtiti and date=@date
-- ajout des fonds de la derniere compo (si elle existe) a la nouvelle compo
print 'Adding the underlyings in the new compo'
insert into TRetroHierarchy select idFund, @idnewcompo, SharesNb from TRetroHierarchy
where IdCompo in (
select IdCompo
from TRetroSubAccountCompo
where IdSubAccount=@idtiti
and date in (select max(date) from TRetroSubAccountCompo where IdSubAccount=@idtiti and date<@date)
)
select @localerr = @@error
if @localerr != 0
begin
goto FIN
end
select @idcompo=IdCompo from TRetroSubAccountCompo where IdSubAccount=@idtiti and date=@date
end
print 'IdCompo=%1!',@idcompo
-- pour toutes les compos de date >= date, ajout de @amout au SharesNb pour le fond en question. Cette étotope se fait en 2 parties
-- 1) ajout du fond dans toutes ces compos (avec un SharesNb=0) s'il n'en fait pas parti
print 'Adding the fund with ShareNb=0 to all the compo where the fund is not'
insert into TRetroHierarchy( IdCompo, idFund, SharesNb)
select distinct c.IdCompo, @idFund, 0
from TRetroSubAccountCompo c
where c.IdSubAccount=@idtiti and c.date>=@date
and not exists(
select h.idFund from TRetroHierarchy h where h.IdCompo=c.IdCompo and h.idFund=@idFund
)
select @localerr = @@error
if @localerr != 0
begin
goto FIN
end
-- 2) pour toutes ces compos et pour le fond en question faire SharesNb+=@amount
print 'Setting SharesNb=SharesNb+ the amount of the trade to the fund in compos where date>=date'
update TRetroHierarchy set SharesNb=SharesNb+@amount
where IdCompo in (
select c.IdCompo from TRetroSubAccountCompo c where c.IdSubAccount=@idtiti and c.date>=@date
)
and idFund=@idFund
select @localerr = @@error
FIN:
if @localerr = 0
begin
if @localtran =0
begin
commit tran
print 'Done'
select "Done" as Result
end
end
else
begin
if @localtran =0
begin
rollback tran
select "Failed" as Result
print 'Failed'
end
end
return @localerr
END
GO |
