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 |
Partager