Problems in Stored procedure..

[Mahesh Kumar]

HI,

I'm novice in Stored Procedure , I have a stored procedure .The stored procedure is pasted in this mail..I have a few quieries about this Stored procedure.
1. When is the value assgned as F and P for cdHoldingtype variable in this stored procdedure. Because for both the filters, cdHoldingtype is assigned. Can somebody please throw some light on this.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


--*****
--* --* Parameters: I :
--* I/O :
--* Select List: idInvestmentObject
--* idInvestmentobjectU
--* tyinvestmentcategory
--* cdISIN
--* cdISINU
--* txDescription
--* txSecurityType
--* tyFeature
--* nuoutstanding
--* nuoutstandinginclass
--* nuNat
--* nuNatSoVR
--* nuNatShVR
--* nuNatNoVR
--* nuNatSoDR
--* nuNatShDR
--* nuNatNoDR
--* nuUnd
--* nuUndSoVR
--* nuUndShVR
--* nuUndNoVR
--* nuUndSoDR
--* nuUndShDR
--* nuUndNoDR
--* txLegalEntity
--* txBookCode
--* cdSEDOL
--* cdSEDOLU
--* cdHoldingType
--* cdCountry
--* txTradingBook
--* Sample:
--* Return Value:
--* Errors:
--* Side Effects:
--*
--*****


ALTER procedure dbo.MPSIAGPosMonitoring
( @dtAsOf UTDateTime = null
)
as

Select @dtAsOf = isnull(@dtAsOf, getdate())


create table #tmpIO
( idInvestmentObject numeric (9) null,
idInvestmentobjectU numeric (9) null,
tyinvestmentcategory int null,
cdISIN varchar(12) null,
cdISINU varchar(12) null,
txDescription varchar(40) null,
txSecurityType varchar(10) null,
tyFeature int null,
nuoutstanding numeric(13) null,
nuoutstandinginclass numeric(13) null,
nuNat numeric(13) null,
nuNatSoVR numeric(13) null,
nuNatShVR numeric(13) null,
nuNatNoVR numeric(13) null,
nuNatSoDR numeric(13) null,
nuNatShDR numeric(13) null,
nuNatNoDR numeric(13) null,
nuUnd numeric(13) null,
nuUndSoVR numeric(13) null,
nuUndShVR numeric(13) null,
nuUndNoVR numeric(13) null,
nuUndSoDR numeric(13) null,
nuUndShDR numeric(13) null,
nuUndNoDR numeric(13) null,
txLegalEntity varchar(4) null,
txBookCode varchar(18) null,
cdSEDOL varchar(7) null,
cdSEDOLU varchar(7) null,
cdHoldingType varchar(1) null,
cdCountry varchar(2) null,
txTradingBook varchar(1) null
)

create table #tmpsummen
( idIO numeric(9) null,
nuShares numeric(13) null
)

insert #tmpIO
select idInvestmentObject,
null,
tyinvestmentcategory,
" ",
" ",
substring(txshortdescription, 1, 40),
null,
null,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
"XXXX",
"XXXXXX",
" ",
" ",
"F",
cdcountry,
"N"
from investmentobject io,
country co,
description de
where tyInvestmentCategory in (10, 40, 90, 100)
and idcountryrisk = co.idcountry
and io.dsinvestmentobject = de.iddescription
and de.idlanguage = 1


insert #tmpIO
select idInvestmentObject,
null,
tyinvestmentcategory,
" ",
" ",
substring(txshortdescription, 1, 40),
null,
null,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
"XXXX",
"XXXXXX",
" ",
" ",
"P",
cdcountry,
"N"
from investmentobject io,
country co,
description de
where tyInvestmentCategory in (10, 40, 90, 100)
and idcountryrisk = co.idcountry
and io.dsinvestmentobject = de.iddescription
and de.idlanguage = 1

update #tmpIO
set idInvestmentObjectU = idIOUnderlying,
tyfeature = FE.tyfeature
from #tmpIO IO,
featuredate FE
where idinvestmentobject = idinvestment
and idIOUnderlying > 0

update #tmpIO
set txSecurityType = "ST"
where tyInvestmentCategory = 10

update #tmpIO
set txSecurityType = "CB"
where tyInvestmentCategory = 40

update #tmpIO
set txSecurityType = "WA"
where tyInvestmentCategory = 90


update #tmpIO
set txSecurityType = "OPC"
where tyInvestmentCategory = 100
and tyfeature = 9

update #tmpIO
set txSecurityType = "OPP"
where tyInvestmentCategory = 100
and tyfeature = 12

update #tmpIO
set cdISIN = left(cdIdentifier,12)
from InvestmentObjectIdentifier ii,
#tmpIO io
where io.idInvestmentObject = ii.idInvestmentobject and
ii.idIdentifierSystem = 17

update #tmpIO
set cdISINU = left(cdIdentifier,12)
from InvestmentObjectIdentifier ii,
#tmpIO io
where io.idInvestmentObjectU = ii.idInvestmentobject and
ii.idIdentifierSystem = 17

update #tmpIO
set cdSEDOL = cdIdentifier
from InvestmentObjectIdentifier ii,
#tmpIO io
where io.idInvestmentObject = ii.idInvestmentobject and
ii.idIdentifierSystem = 6

update #tmpIO
set cdSEDOLU = cdIdentifier
from InvestmentObjectIdentifier ii,
#tmpIO io
where io.idInvestmentObjectU = ii.idInvestmentobject and
ii.idIdentifierSystem = 6


Update #tmpIO
set nuoutstanding = v.prValue
from TimeSeriesValue v,
DefaultTimeSeries d,
#tmpIO io
where io.tyinvestmentcategory = 10 and
d.tyTimeSeries = 31 and
io.idInvestmentObject = d.idInvestmentObject and
v.idTimeSeries = d.idTimeSeries and
d.dtdefaulttimeseries <= @dtAsOf and
d.dtdefaulttimeseriesEnd >= @dtAsOf and
v.dtValue <= @dtAsOf and
v.dtValueEnd >= @dtAsOf

Update #tmpIO
set nuoutstanding = v.prValue
from TimeSeriesValue v,
DefaultTimeSeries d,
#tmpIO io
where io.tyinvestmentcategory <> 10 and
d.tyTimeSeries = 31 and
io.idInvestmentObjectU = d.idInvestmentObject and
v.idTimeSeries = d.idTimeSeries and
d.dtdefaulttimeseries <= @dtAsOf and
d.dtdefaulttimeseriesEnd >= @dtAsOf and
v.dtValue <= @dtAsOf and
v.dtValueEnd >= @dtAsOf

insert #tmpsummen
select IO.idinvestmentobject,
sum(iv.pstradedated)
from #tmpIO IO,
inventory IV,
portfolio PO
where IO.tyInvestmentcategory = 10
and IO.cdholdingtype = "F"
and IO.idInvestmentobject = IV.idinvestmentobject
and IV.idPortfolio = PO.idPortfolio
and PO.tycontract = 10000
and PO.tyPortfolio = 1
and IV.dtInventory <= @dtAsOf
and IV.dtInventoryEnd >= @dtAsOf
group by IO.idInvestmentobject
order by IO.idInvestmentobject

update #tmpIO
set nuNat = nushares
from #tmpsummen
where #tmpIO.idinvestmentobject = #tmpsummen.idIO
and cdHoldingtype = "P"

truncate table #tmpsummen

insert #tmpsummen
select IO.idinvestmentobject,
sum(iv.pstradedated)
from #tmpIO IO,
inventory IV,
portfolio PO
where IO.tyInvestmentcategory = 10
and IO.cdholdingtype = "F"
and IO.idInvestmentobject = IV.idinvestmentobject
and IV.idPortfolio = PO.idPortfolio
and PO.tycontract <> 10000
and PO.tyPortfolio = 1
and IV.dtInventory <= @dtAsOf
and IV.dtInventoryEnd >= @dtAsOf
group by IO.idInvestmentobject
order by IO.idInvestmentobject

update #tmpIO
set nuNat = nushares
from #tmpsummen
where #tmpIO.idinvestmentobject = #tmpsummen.idIO
and cdHoldingtype = "F"

truncate table #tmpsummen

insert #tmpsummen
select IO.idinvestmentobject,
sum(iv.pstradedated)
from #tmpIO IO,
inventory IV,
portfolio PO
where IO.tyInvestmentcategory <> 10
and IO.cdholdingtype = "F"
and IO.idInvestmentobject = IV.idinvestmentobject
and IV.idPortfolio = PO.idportfolio
and PO.tycontract = 10000
and PO.tyPortfolio = 1
and IV.dtInventory <= @dtAsOf
and IV.dtInventoryEnd >= @dtAsOf
group by IO.idInvestmentobject
order by IO.idInvestmentobject

update #tmpIO
set nuNat = nushares
from #tmpsummen
where #tmpIO.idinvestmentobject = #tmpsummen.idIO
and cdHoldingtype = "P"

truncate table #tmpsummen

insert #tmpsummen
select IO.idinvestmentobject,
sum(iv.pstradedated)
from #tmpIO IO,
inventory IV,
portfolio PO
where IO.tyInvestmentcategory <> 10
and IO.cdholdingtype = "F"
and IO.idInvestmentobject = IV.idinvestmentobject
and IV.idPortfolio = PO.idportfolio
and PO.tycontract <> 10000
and PO.tyPortfolio = 1
and IV.dtInventory <= @dtAsOf
and IV.dtInventoryEnd >= @dtAsOf
group by IO.idInvestmentobject
order by IO.idInvestmentobject

update #tmpIO
set nuNat = nushares
from #tmpsummen
where #tmpIO.idinvestmentobject = #tmpsummen.idIO
and cdHoldingtype = "F"

update #tmpIO
set nuUnd = nuNat * aqconversioninvestment / aqconversionunderlying
from #tmpIO,
featuredate
where tyinvestmentcategory = 100
and idinvestmentobject = idinvestment
and featuredate.tyfeature in (9,12)

update #tmpIO
set nuNatSoVr = nunat,
nuUndSoVr = nuUnd,
nunatSoDr = nuNat,
nuUndSoDr = nuUnd
where cdHoldingType = "P"
and tyinvestmentcategory = 10

update #tmpIO
set nuNatNoVr = nunat,
nuUndNoVr = nuUnd,
nunatSoDr = nuNat,
nuUndSoDr = nuUnd
where cdHoldingType = "F"
and tyinvestmentcategory = 10

update #tmpIO
set nuUndSoVr = nuUnd,
nuUndSoDr = nuUnd
where cdHoldingType = "P"
and tyinvestmentcategory <> 10

update #tmpIO
set nuUndNoVr = nuUnd,
nuUndSoDr = nuUnd
where cdHoldingType = "F"
and tyinvestmentcategory <> 10

select * from #tmpIO
where nunat <> 0 or nuund <> 0
order by tyInvestmentCategory

Return 0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Edited 2 time(s). Last edit at 11/08/2005 03:07AM by Mahesh Kumar.