-- if SP is existed, drop it.
if (object_id('InvHoldToDPS', 'P') is not null)
drop proc InvHoldToDPS
GO -- craete new SP.
CREATE PROCEDURE dbo.InvHoldToDPS
AS
BEGIN
SET NOCOUNT ON -- delete target table.
--delete from inv1_holdsForPrice -- delte temp table.
--delete from inv1_holdsForPriceTemp declare @headLine char(2000)
-- get the first record which is title info.
set @headLine = (select top 1 feedValue from inv1_holdsForPriceTemp) IF LEFT(@headLine, 6) <> 'IMAHDR'
BEGIN
print 'Error'
-- wirte the info to the log table.
-- exit (return)
END declare @priceDate char(8)
select @priceDate = SUBSTRING(@headLine, 26,8) -- store Price Date "YYYYMMDD" declare @j int
select @j = 0 declare curFV_cur cursor for
select feedValue from inv1_holdsForPriceTemp for read only declare @feedValue char(2000)
select @feedValue = '' open curFV_cur
fetch next from curFV_cur into @feedValue while (@@fetch_status = 0)
begin
if(LEFT(@feedValue, 6) <> 'IMAHDR' And Left(@feedValue, 6) <> 'IMATRL') -- Head and Feet record
begin
if(LEN(ltrim(rtrim(SUBSTRING(@feedValue, 39, 9))))) = 9
begin
declare @inv1_acct_num decimal
declare @inv1_grp_acct_num varchar
declare @fmr_fund_num varchar
declare @fmr_subport_num char
declare @fmr_cusip varchar
declare @acct varchar
declare @scty_cusip varchar
declare @scty_date varchar
declare @scty_qual varchar
declare @hold_date varchar
declare @shares decimal
declare @cost decimal
declare @amort decimal
declare @accr_incom decimal
declare @market decimal
declare @price decimal
declare @fx_rate decimal
declare @price_date varchar select @inv1_acct_num = LTRIM(RTRIM(SUBSTRING(@feedValue, 1, 15)))
select @inv1_grp_acct_num = LTRIM(RTRIM(SUBSTRING(@feedValue, 16, 15)))
select @fmr_fund_num = LTRIM(RTRIM(SUBSTRING(@feedValue, 31, 6)))
select @fmr_subport_num = LTRIM(RTRIM(SUBSTRING(@feedValue, 37, 2)))
select @fmr_cusip = LTRIM(RTRIM(SUBSTRING(@feedValue, 39, 9)))
select @acct = LTRIM(RTRIM(SUBSTRING(@feedValue, 59, 15)))
select @scty_cusip = LTRIM(RTRIM(SUBSTRING(@feedValue, 74, 9)))
select @scty_date = LTRIM(RTRIM(SUBSTRING(@feedValue, 83, 8)))
select @scty_qual = LTRIM(RTRIM(SUBSTRING(@feedValue, 91, 5)))
select @hold_date = LTRIM(RTRIM(SUBSTRING(@feedValue, 96, 8)))
select @shares = SUBSTRING(@feedValue, 104, 15) + '.' + SUBSTRING(@feedValue, 109, 4)
select @cost = SUBSTRING(@feedValue, 123, 16) + '.' + SUBSTRING(@feedValue, 139, 2)
select @amort = SUBSTRING(@feedValue, 141, 16) + '.' + SUBSTRING(@feedValue, 157, 2)
select @accr_incom = SUBSTRING(@feedValue, 159, 16) + '.' + SUBSTRING(@feedValue, 175, 2)
select @market = SUBSTRING(@feedValue, 177, 16) + '.' + SUBSTRING(@feedValue, 193, 2)
select @price = SUBSTRING(@feedValue, 195, 77) + '.' + SUBSTRING(@feedValue, 206, 8)
select @fx_rate = SUBSTRING(@feedValue, 214, 7) + '.' + SUBSTRING(@feedValue, 211, 9)
select @price_date = @priceDate if(SUBSTRING(@feedValue, 48, 1)) = ''
begin
exec dbo.spUpd_inv1_holdsForPrice
@inv1_acct_num,
@inv1_grp_acct_num,
@fmr_fund_num,
@fmr_subport_num,
@fmr_cusip,
@acct,
@scty_cusip,
@scty_date,
@scty_qual,
@hold_date,
@shares,
@cost,
@amort,
@accr_incom,
@market,
@price,
@fx_rate,
@price_date end
else if(SUBSTRING(@feedValue, 48, 1)) = ''
begin
declare @inv1_acct_num2 decimal
declare @inv1_grp_acct_num2 varchar
declare @fmr_fund_num2 varchar
declare @fmr_subport_num2 char
declare @fmr_cusip2 varchar
declare @sec_desc varchar
declare @sec_country_code varchar
declare @traded_ccy varchar
declare @income_ccy varchar
declare @unit_of_calc decimal select @inv1_acct_num2 = LTRIM(RTRIM(SUBSTRING(@feedValue, 1, 15)))
select @inv1_grp_acct_num2 = LTRIM(RTRIM(SUBSTRING(@feedValue, 16, 15)))
select @fmr_fund_num2 = LTRIM(RTRIM(SUBSTRING(@feedValue, 31, 6)))
select @fmr_subport_num2 = LTRIM(RTRIM(SUBSTRING(@feedValue, 37, 2)))
select @fmr_cusip2 = LTRIM(RTRIM(SUBSTRING(@feedValue, 39, 9))) select @sec_desc = LTRIM(RTRIM(SUBSTRING(@feedValue, 59, 30)))
select @sec_country_code = LTRIM(RTRIM(SUBSTRING(@feedValue, 89, 4)))
select @traded_ccy = LTRIM(RTRIM(SUBSTRING(@feedValue, 93, 4)))
select @income_ccy = LTRIM(RTRIM(SUBSTRING(@feedValue, 97, 4)))
select @unit_of_calc = LTRIM(RTRIM(SUBSTRING(@feedValue, 158, 10))) + '.' + LTRIM(RTRIM(SUBSTRING(@feedValue, 168, 4))) exec spUpd_inv1_holdsForPrice2
@inv1_acct_num2,
@inv1_grp_acct_num2,
@fmr_fund_num2,
@fmr_subport_num2,
@fmr_cusip2,
@sec_desc,
@sec_country_code,
@traded_ccy,
@income_ccy,
@unit_of_calc
end
end
end select @j = @j + 1
fetch next from curFV_cur into @feedValue
end
close curFV_cur
deallocate curFV_cur END
SET NOCOUNT OFF --exec spUpdateBDAatInv1 --exec spUpdateEquitiesMasterInv1 GO