Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

temp table locking

9 posts in General Discussion Last posting was on 2004-03-19 19:07:25.0Z
Brett Morgan Posted on 2004-03-19 15:28:17.0Z
Reply-To: "Brett Morgan" <bmorgan@imsi.com>
From: "Brett Morgan" <bmorgan@imsi.com>
Newsgroups: ianywhere.public.general
Subject: temp table locking
Lines: 25
Organization: International Fund Services
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: exit.imsi.com
X-Original-NNTP-Posting-Host: exit.imsi.com
Message-ID: <405b1191$1@forums-1-dub>
Date: 19 Mar 2004 07:28:17 -0800
X-Trace: forums-1-dub 1079710097 199.253.174.9 (19 Mar 2004 07:28:17 -0800)
X-Original-Trace: 19 Mar 2004 07:28:17 -0800, exit.imsi.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2803
Article PK: 6274

I've discovered a weird locking problem in a stored procedure we are using
that's locking temp tables across connections. I am running Sybase ASA
8.0.3, build 4322.

The procedure creates some local temp tables, inserts, and updates into the
tables then finally selects data from tables. If I run the procedure in two
different instances of DBISQL (autocommit off) the second execution hangs.
If I commit on the first execution the second execution executes. I ran
sa_conn_info and saw that the first execution was blocking the second. I
couldn't figure out why since all the inserts/updates are done to temp
tables.

I then turned blocking off using, "set option blocking = off." I again
executed the procedure in two instances of DBISQL. I received an error
message for the second execution saying that a temp table is locked, "ASA
Error -210: User 'DBA' has the row in '#report_data' locked."

It was my understanding that temp tables are exclusive to a connection,
therefore why would they be locked across two connections? Any help would
be much appreciated.

Brett Morgan
bmorgan@imsi.com


Breck Carter [TeamSybase] Posted on 2004-03-19 16:55:20.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: temp table locking
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com>
References: <405b1191$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 19 Mar 2004 08:55:20 -0800
X-Trace: forums-1-dub 1079715320 64.7.134.118 (19 Mar 2004 08:55:20 -0800)
X-Original-Trace: 19 Mar 2004 08:55:20 -0800, bcarter.sentex.ca
Lines: 39
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2805
Article PK: 6273

Please show us how you created the temporary tables. If you enclosed
the table name in doublequotes then you may have created a permanent
table that just happens to have a name beginning with #. Look in
SYSTABLE to be sure; if it's there, it's not a local temporary table.

On 19 Mar 2004 07:28:17 -0800, "Brett Morgan" <bmorgan@imsi.com>

wrote:

>I've discovered a weird locking problem in a stored procedure we are using
>that's locking temp tables across connections. I am running Sybase ASA
>8.0.3, build 4322.
>
>The procedure creates some local temp tables, inserts, and updates into the
>tables then finally selects data from tables. If I run the procedure in two
>different instances of DBISQL (autocommit off) the second execution hangs.
>If I commit on the first execution the second execution executes. I ran
>sa_conn_info and saw that the first execution was blocking the second. I
>couldn't figure out why since all the inserts/updates are done to temp
>tables.
>
>I then turned blocking off using, "set option blocking = off." I again
>executed the procedure in two instances of DBISQL. I received an error
>message for the second execution saying that a temp table is locked, "ASA
>Error -210: User 'DBA' has the row in '#report_data' locked."
>
>It was my understanding that temp tables are exclusive to a connection,
>therefore why would they be locked across two connections? Any help would
>be much appreciated.
>
>Brett Morgan
>bmorgan@imsi.com
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Brett Morgan Posted on 2004-03-19 17:20:31.0Z
Reply-To: "Brett Morgan" <bmorgan@imsi.com>
From: "Brett Morgan" <bmorgan@imsi.com>
Newsgroups: ianywhere.public.general
References: <405b1191$1@forums-1-dub> <369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com>
Subject: Re: temp table locking
Lines: 1451
Organization: International Fund Services
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Original-NNTP-Posting-Host: exit.imsi.com
Message-ID: <405b2bdc@forums-2-dub>
X-Original-Trace: 19 Mar 2004 09:20:28 -0800, exit.imsi.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Mar 2004 09:20:29 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Mar 2004 09:20:31 -0800
X-Trace: forums-1-dub 1079716831 10.22.108.75 (19 Mar 2004 09:20:31 -0800)
X-Original-Trace: 19 Mar 2004 09:20:31 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2807
Article PK: 6276

Here is the procs, one main and one sub-proc. It's a beast. The table
names are not in double quote and not appearing in SYSTABLE. Thanks

CREATE PROC p_part_stmt
(
@fund_id NUMERIC(10)
,@year_date CHAR(12) = NULL
,@prev_date CHAR(12) = NULL
,@quarter_date CHAR(12) = NULL
,@stmt_date CHAR(12)
,@from_hid NUMERIC(10)
,@to_hid NUMERIC(10)
,@fund_summary NUMERIC(1)
,@summary_funds VARCHAR(50) = NULL
,@fund_to_date_info NUMERIC(1) = 0
,@sort_order NUMERIC(2) = 0
,@signature_info NUMERIC(1) = 0
,@out_nav_info NUMERIC(1) = 1
,@parmemail NUMERIC(1) = 1
,@parmmail NUMERIC(1) = 1
,@parmfax NUMERIC(1) = 1
,@parmother NUMERIC(1) = 0
,@parmother2 NUMERIC(1) = 0
,@parmregaddr NUMERIC(1) = 0
,@first_name_first NUMERIC(1) = 1
,@calc_incep_ror NUMERIC(1) = 0
,@gp_filter NUMERIC(1) = NULL
,@investor_level NUMERIC(1) = 0
,@no_composite_ror NUMERIC(1) = 1
,@export_output NUMERIC(1) = 0
,@income_stmt NUMERIC(1) = 0
,@show_ytd_ror_t1_redem NUMERIC(1) = 0
,@weekly_estimates NUMERIC(1) = 0
)
AS


DECLARE
@hid NUMERIC(10),
@series_id NUMERIC(10),
@contact_id NUMERIC(10),
@investor_id NUMERIC(10),
@issue_trans_id NUMERIC(10),
@nnav NUMERIC(16,6),
@gnav NUMERIC(16,6),
@cur VARCHAR(10),
@method_id NUMERIC(5),
@iname VARCHAR(150),
@temp_iname VARCHAR(150),
@group_by_class NUMERIC(1),
@r_decval NUMERIC(1)
,@add_todate_redem NUMERIC(1)
,@add_todate_trans_r NUMERIC(1)
,@add_todate_roll_r NUMERIC(1)
,@add_todate_switch_r NUMERIC(1)
,@add_todate_exch_r NUMERIC(1)
,@msg TEXT


CREATE TABLE #out_result(
hid NUMERIC(10) NULL
,series_id NUMERIC(10) NULL
,contact_id NUMERIC(10) NULL
,investor_id NUMERIC(10) NULL
,issue_trans_id NUMERIC(10) NULL
,invnum NUMERIC(10) NULL
,g_beginbal NUMERIC(16,6) NULL
,g_value NUMERIC(16,6) NULL
,nnav NUMERIC(16,6) NULL
,gnav NUMERIC(16,6) NULL
,prev_nnav NUMERIC(16,6) NULL
,prev_gnav NUMERIC(16,6) NULL
,yr_nnav NUMERIC(16,6) NULL
,yr_gnav NUMERIC(16,6) NULL
,yr_shares NUMERIC(16,6) NULL
,pctyear NUMERIC(16,6) NULL
,pctmonth NUMERIC(16,6) NULL
,net_profit NUMERIC(16,6) NULL
,cur VARCHAR(10) NULL
,transshares NUMERIC(16,6) NULL
,method_id NUMERIC(5) NULL
,iname VARCHAR(150) NULL
,series_name VARCHAR(25) NULL
,open_shares NUMERIC(16,6) NULL
,open_shares_val NUMERIC(16,6) NULL
,shares NUMERIC(16,6) NULL
,shares_val NUMERIC(16,6) NULL
,sub_activity NUMERIC(16,6) NULL
,sub_activity_val NUMERIC(16,6) NULL
,rdp_activity NUMERIC(16,6) NULL
,rdp_activity_val NUMERIC(16,6) NULL
,transfer_activity NUMERIC(16,6) NULL
,transfer_activity_val NUMERIC(16,6) NULL
,rollup_activity NUMERIC(16,6) NULL
,rollup_activity_val NUMERIC(16,6) NULL
,switch_activity NUMERIC(16,6) NULL
,switch_activity_val NUMERIC(16,6) NULL
,eq_balance NUMERIC(16,6) NULL
)

CREATE TABLE #nav_info
(series_id NUMERIC(10) NULL
,nav_date DATETIME NULL
,nnav NUMERIC(16,6) NULL
,gnav NUMERIC(16,6) NULL
)

CREATE TABLE #final
(hid NUMERIC(10) NULL
,contact_id NUMERIC(10) NULL
,beginbal NUMERIC(16,6) NULL
,g_beginbal NUMERIC(16,6) NULL
,value NUMERIC(16,6) NULL
,g_value NUMERIC(16,6) NULL
,subsrip NUMERIC(16,6) NULL
,redemp NUMERIC(16,6) NULL
,open_shares NUMERIC(16,6) NULL
,shares NUMERIC(16,6) NULL
,transshares NUMERIC(16,6) NULL
,pctincep NUMERIC(16,6) NULL
,pctyear NUMERIC(16,6) NULL
,pctmonth NUMERIC(16,6) NULL
,net_profit NUMERIC(16,6) NULL
,cur VARCHAR(25) NULL
,iname VARCHAR(255) NULL
,contact VARCHAR(255) NULL
,rep VARCHAR(255) NULL
,addr1 VARCHAR(255) NULL
,addr2 VARCHAR(255) NULL
,addr3 VARCHAR(255) NULL
,addr4 VARCHAR(255) NULL
,addr5 VARCHAR(255) NULL
,phone VARCHAR(255) NULL
,fax VARCHAR(255) NULL
,email VARCHAR(255) NULL
,series_name VARCHAR(50) NULL
,nnav NUMERIC(16,6) NULL
,r_decnav NUMERIC(5) NULL
,r_decsh NUMERIC(5) NULL
,class VARCHAR(50) NULL
,identifier2 VARCHAR(255) NULL
,class_id NUMERIC(10) NULL
,investor_id NUMERIC(10) NULL
,series_id NUMERIC(10) NULL
,eq_balance NUMERIC(16,6) NULL
,prev_nnav NUMERIC(16,6) NULL
)

CREATE TABLE #investor_ror(
investor_id NUMERIC(10)
,class_id NUMERIC(10)
,series_id NUMERIC(10)
,mtd_ror NUMERIC(16,6)
,ytd_ror NUMERIC(16,6)
,gr_mtd_ror NUMERIC(16,6)
,gr_ytd_ror NUMERIC(16,6)
)

IF @to_hid = 99999
SELECT @to_hid = 999999

IF @year_date IS NULL
SELECT @year_date = CONVERT( CHAR(4),(DATEPART(YEAR,@stmt_date)-1) ) +
'-12-31'

SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
SELECT @r_decval = ( SELECT r_decval FROM fund WHERE fund_id = @fund_id )

SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0) FROM
catalogdata
WHERE cdid = 1 AND cd_type = @fund_id AND
cd_charcode = 'REDEM')
,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = 1 AND cd_type = @fund_id AND
cd_charcode = 'TRANSFER_REDEM')
,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = 1 AND cd_type = @fund_id AND
cd_charcode = 'ROLLUP_REDEM')
,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = 1 AND cd_type = @fund_id AND
cd_charcode = 'SWITCH_REDEM')
,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = 1 AND cd_type = @fund_id AND
cd_charcode = 'EXCHANGE_REDEM')

EXEC subp_part_stmt_main @fund_id, @year_date, @prev_date, @quarter_date,
@stmt_date, @from_hid, @to_hid
,@out_nav_info, @parmemail, @parmmail, @parmfax,
@parmother, @parmother2, @parmregaddr, @first_name_first
,@use_ror_logic = @no_composite_ror,
@show_ytd_ror_t1_redem = @show_ytd_ror_t1_redem
,@weekly_estimates=@weekly_estimates

UPDATE #out_result
SET rdp_activity = CASE WHEN transfer_activity < 0 THEN rdp_activity
+ transfer_activity ELSE rdp_activity END
,rdp_activity_val = CASE WHEN transfer_activity_val < 0 THEN
rdp_activity_val + transfer_activity_val ELSE rdp_activity_val END
,sub_activity = CASE WHEN transfer_activity > 0 THEN sub_activity
+ transfer_activity ELSE sub_activity END
,sub_activity_val = CASE WHEN transfer_activity_val > 0 THEN
sub_activity_val + transfer_activity_val ELSE sub_activity_val END

UPDATE #out_result
SET rdp_activity = CASE WHEN rollup_activity < 0 THEN rdp_activity +
rollup_activity ELSE rdp_activity END
,rdp_activity_val = CASE WHEN rollup_activity_val < 0 THEN
rdp_activity_val + rollup_activity_val ELSE rdp_activity_val END
,sub_activity = CASE WHEN rollup_activity > 0 THEN sub_activity +
rollup_activity ELSE sub_activity END
,sub_activity_val = CASE WHEN rollup_activity_val > 0 THEN
sub_activity_val + rollup_activity_val ELSE sub_activity_val END

UPDATE #out_result
SET rdp_activity = CASE WHEN switch_activity < 0 THEN rdp_activity +
switch_activity ELSE rdp_activity END
,rdp_activity_val = CASE WHEN switch_activity_val < 0 THEN
rdp_activity_val + switch_activity_val ELSE rdp_activity_val END
,sub_activity = CASE WHEN switch_activity > 0 THEN sub_activity +
switch_activity ELSE sub_activity END
,sub_activity_val = CASE WHEN switch_activity_val > 0 THEN
sub_activity_val + switch_activity_val ELSE sub_activity_val END



IF @no_composite_ror = 0
BEGIN
INSERT #investor_ror
SELECT DISTINCT o.investor_id, s.class_id, o.series_id, 0, 0, 0, 0
FROM #out_result o, series s
WHERE s.fund_id = @fund_id
AND s.series_id = o.series_id

EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date, @stmt_date
END

/*************************************************************************/

IF EXISTS (SELECT * FROM rate_of_return WHERE fund_id = @fund_id AND
series_id = 0) AND @no_composite_ror = 1
BEGIN
SELECT @group_by_class = 1

INSERT #final
SELECT
hid = rd.hid
,contact_id = rd.contact_id
,beginbal = ISNULL(SUM(open_shares_val) ,0)
,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
,value = ISNULL(SUM(shares_val) ,0)
,g_value = ISNULL(SUM(g_value) ,0)
,subsrip = ISNULL(SUM(sub_activity_val),0)
,redemp = ISNULL(SUM(rdp_activity_val),0)
,open_shares= ISNULL(SUM(open_shares) ,0)
,shares = ISNULL(SUM(rd.shares) ,0)
,transshares= SUM(transshares)
,pctincep = 0
,pctyear = MAX(pctyear)
,pctmonth = MAX(pctmonth)
,net_profit = SUM(ISNULL(rd.net_profit,0))
,cur = MAX(cur)
,iname = iname
,contact = MAX(c.contact)
,rep = MAX(c.rep)
,addr1 = MAX(c.addr1)
,addr2 = MAX(c.addr2)
,addr3 = MAX(c.addr3)
,addr4 = MAX(c.addr4)
,addr5 = MAX(c.addr5)
,phone = MAX(c.phone)
,fax = MAX(c.fax)
,email = MAX(c.email)
,series_name= RTRIM(cl.identifier)
,nnav = MAX(rd.nnav)
,r_decnav = MAX(f.r_decnav)
,r_decsh = MAX(f.r_decsh)
,class = cl.identifier
,identifier2= MAX(cl.identifier2)
,class_id = cl.class_id
,investor_id= rd.investor_id
,series_id = NULL
,0
,prev_nnav = MAX(rd.prev_nnav)
FROM
contact c
,#out_result rd
,series se
,classes cl
,fund f
WHERE c.contact_id = rd.contact_id
AND se.series_id = rd.series_id
AND se.fund_id = @fund_id
AND cl.fund_id = se.fund_id
AND f.fund_id = se.fund_id
AND cl.class_id = se.class_id
GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
cl.class_id, rd.iname
END

ELSE

BEGIN
SELECT @group_by_class = 0

INSERT #final
SELECT
hid = rd.hid
,contact_id = rd.contact_id
,beginbal = ISNULL(SUM(open_shares_val) ,0)
,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
,value = ISNULL(SUM(shares_val) ,0)
,g_value = ISNULL(SUM(g_value) ,0)
,subsrip = ISNULL(SUM(sub_activity_val),0)
,redemp = ISNULL(SUM(rdp_activity_val),0)
,open_shares= ISNULL(SUM(open_shares) ,0)
,shares = ISNULL(SUM(rd.shares) ,0)
,transshares= SUM(transshares)
,pctincep = 0
,pctyear = MAX(pctyear)
,pctmonth = MAX(pctmonth)
,net_profit = SUM(ISNULL(rd.net_profit,0))
,cur = MAX(cur)
,iname = iname
,contact = MAX(c.contact)
,rep = MAX(c.rep)
,addr1 = MAX(c.addr1)
,addr2 = MAX(c.addr2)
,addr3 = MAX(c.addr3)
,addr4 = MAX(c.addr4)
,addr5 = MAX(c.addr5)
,phone = MAX(c.phone)
,fax = MAX(c.fax)
,email = MAX(c.email)
,series_name= RTRIM(cl.identifier) + '_' +RTRIM(se.identifier)
,nnav = MAX(rd.nnav)
,r_decnav = MAX(f.r_decnav)
,r_decsh = MAX(f.r_decsh)
,class = cl.identifier
,identifier2= MAX(cl.identifier2)
,class_id = cl.class_id
,investor_id= rd.investor_id
,series_id = rd.series_id
,0
,prev_nnav = MAX(rd.prev_nnav)
FROM
contact c
,#out_result rd
,series se
,classes cl
,fund f
WHERE c.contact_id = rd.contact_id
AND se.series_id = rd.series_id
AND se.fund_id = @fund_id
AND cl.fund_id = se.fund_id
AND f.fund_id = se.fund_id
AND cl.class_id = se.class_id
GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
cl.class_id, rd.series_id, se.identifier, rd.iname

END
----------------------------------------------------------------------------
----------

UPDATE #final f
SET transshares = ( SELECT SUM(redemp) FROM #final ff
WHERE f.hid = ff.hid
AND f.contact_id = ff.contact_id
)
UPDATE #final f
SET transshares = transshares + ( SELECT SUM(subsrip) FROM #final ff
WHERE f.hid = ff.hid
AND f.contact_id = ff.contact_id
)
UPDATE #final f
SET g_beginbal = ( SELECT SUM(ROUND(beginbal,@r_decval)) FROM #final ff
WHERE f.hid = ff.hid
AND f.contact_id = ff.contact_id
)

UPDATE #final f
SET g_value = ( SELECT SUM(ROUND(value,@r_decval)) FROM #final ff
WHERE f.hid = ff.hid
AND f.contact_id = ff.contact_id
)
/*************************************************************************/

IF NOT EXISTS ( SELECT 'FOUND' FROM fund_addl_info f, additional_info a
WHERE a.info_id = f.info_id AND a.short_code =
'FR_PARTNERSHIP'
AND f.fund_id = @fund_id )
DELETE #final
FROM trans t, redemptions r
WHERE t.fund_id = @fund_id
AND t.transaction_date >= @prev_date -- date range needed for quarterly
stmts
AND t.transaction_date < @stmt_date
AND r.series_id = #final.series_id
AND t.transaction_id = r.transaction_id
AND #final.shares = 0
AND t.investor_id = #final.investor_id
AND ( (@add_todate_redem=1 AND t.activity_type=0 AND
transaction_type=2)
OR (@add_todate_trans_r=1 AND t.activity_type=1 AND
transaction_type=2)
OR (@add_todate_roll_r=1 AND t.activity_type=3 AND
transaction_type=2)
OR (@add_todate_switch_r=1 AND t.switch_transaction=1 AND
transaction_type=2)
OR (@add_todate_exch_r=1 AND t.activity_type=4 AND
transaction_type=2)
)

IF @investor_level = 1
BEGIN
INSERT #final
SELECT DISTINCT
hid ,contact_id=0 ,beginbal ,g_beginbal
,value ,g_value ,subsrip ,redemp
,open_shares ,shares ,transshares ,pctincep
,pctyear ,pctmonth ,net_profit ,cur
,iname ,'','','','','','','','','',''
,series_name ,nnav ,r_decnav ,r_decsh
,class ,identifier2 ,class_id ,investor_id
,series_id ,eq_balance ,prev_nnav
FROM #final

DELETE #final WHERE contact_id <> 0
END
----------------------------------------------------------------------------
----------


IF @calc_incep_ror = 1
EXEC subp_ps_incep_ror @fund_id, @stmt_date, @group_by_class


SELECT @msg = ( SELECT text_val FROM fund_addl_info f, additional_info a
WHERE f.info_id = a.info_id and UPPER(short_code) =
'COMMENT'
AND f.fund_id = @fund_id
AND f.object_id = @fund_id)


----------------------------------------------------------------------------
----------


/***************************************************************************
/
/********************* Check for hardcoded ror values
******************/
/***************************************************************************
/

UPDATE #final
SET pctmonth = r.net_mtd
,pctyear = r.net_ytd
,pctincep = r.net_itd
FROM ror_hardcode r, fundinvestors fi
WHERE r.fund_id = @fund_id
AND fi.fund_id = @fund_id
AND fi.hid = #final.hid
AND fi.investor_id = r.investor_id
AND r.series_id = #final.series_id


/***************************************************************************
/
/***************************************************************************
/
--------------------------------------------------
------------ OUTPUT SELECT STATEMENTS ------------
--------------------------------------------------
IF @export_output = 0
BEGIN
IF @fund_summary=0
EXEC p_ps_fund_info @fund_id, @year_date, @prev_date, @prev_date,
@stmt_date, @fund_to_date_info, @signature_info=@signature_info,
@income_stmt=@income_stmt

ELSE IF @fund_summary=1
BEGIN
SELECT @summary_funds = (SELECT shortname FROM fund WHERE fund_id =
@fund_id)
EXEC p_ps_fund_info @fund_id, @year_date, @prev_date, @prev_date,
@stmt_date, @fund_to_date_info, @summary_funds,
@signature_info=@signature_info, @gp_filter=@gp_filter,
@income_stmt=@income_stmt
END

ELSE IF @fund_summary=2
EXEC p_ps_fund_info @fund_id, @year_date, @prev_date, @prev_date,
@stmt_date, @fund_to_date_info, @summary_funds,
@signature_info=@signature_info, @gp_filter=@gp_filter,
@income_stmt=@income_stmt
END

----------------------------------------------------------------------------
----------
--------------------------- OUTPUT SELECT
ORTS ------------------------------------
----------------------------------------------------------------------------
----------

IF @no_composite_ror = 0 AND @export_output = 0
SELECT DISTINCT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
,i.mtd_ror ,i.ytd_ror ,prev_nnav
FROM #final f, #investor_ror i
WHERE f.investor_id = i.investor_id
AND f.class_id = i.class_id
AND i.series_id = ISNULL(f.series_id,i.series_id)
ORDER BY hid, contact_id, iname, cur, series_name

ELSE IF @sort_order = 0 AND @export_output = 0
SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
,eq_balance ,0,0,@msg
FROM #final
ORDER BY series_name, iname, hid, contact_id, cur

ELSE IF @sort_order = 1 AND @export_output = 0
SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
,0,0,0,@msg
FROM #final
ORDER BY hid, contact_id, iname, cur, series_name

ELSE IF @sort_order = 2 AND @export_output = 0
SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
FROM #final
ORDER BY class, hid, contact_id, iname, cur, series_name

ELSE IF @sort_order = 3 AND @export_output = 0 -- Wellington statement
needs cl.identifier2
SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
,0,0,0,identifier2
FROM #final
ORDER BY hid, contact_id, iname, cur, series_name

ELSE IF @sort_order = 4 AND @export_output = 0
SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
FROM #final
ORDER BY hid, contact_id, iname, cur, series_name
ELSE IF @export_output = 0
SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
,g_value ,subsrip ,redemp ,open_shares ,shares
,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
,cur
,iname ,contact ,rep ,addr1 ,addr2
,addr3 ,addr4 ,addr5 ,phone ,fax
,email ,series_name ,nnav ,r_decnav ,r_decsh
FROM #final
ORDER BY hid, contact_id, iname, cur, series_name

ELSE IF @export_output = 1 AND @no_composite_ror = 1
INSERT #output
SELECT *,0,0 FROM #final
ORDER BY iname, hid, series_name

ELSE IF @export_output = 1 AND @no_composite_ror = 0
INSERT #output
SELECT f.*,i.mtd_ror,i.ytd_ror FROM #final f, #investor_ror i
WHERE f.investor_id = i.investor_id
AND f.class_id = i.class_id
AND i.series_id = ISNULL(f.series_id,i.series_id)
ORDER BY iname, hid, series_name

ELSE IF @export_output = 2
SELECT hid ,contact_id ,beginbal ,value ,subsrip
,redemp ,open_shares ,shares ,pctincep ,pctyear
,pctmonth ,net_profit ,series_name, nnav
FROM #final
ORDER BY hid, contact_id, series_name

DROP TABLE #nav_info
DROP TABLE #out_result
DROP TABLE #final
DROP TABLE #investor_ror
GO


/*********************
* Second proc *
*********************/
CREATE PROC subp_part_stmt_main
(
@fund_id NUMERIC(10)
,@year_date CHAR(12)
,@prev_date CHAR(12) = NULL
,@quarter_date CHAR(12) = NULL
,@stmt_date CHAR(12)
,@from_hid NUMERIC(10)
,@to_hid NUMERIC(10)
,@out_nav_info NUMERIC(1) = 1
,@parmemail NUMERIC(1) = 1
,@parmmail NUMERIC(1) = 1
,@parmfax NUMERIC(1) = 1
,@parmother NUMERIC(1) = 0
,@parmother2 NUMERIC(1) = 0
,@parmregaddr NUMERIC(1) = 0
,@first_name_first NUMERIC(1) = 1
,@temp_tables_exist NUMERIC(1) = 0
,@use_ror_logic NUMERIC(1) = 1
,@show_ytd_ror_t1_redem NUMERIC(1) = 0
,@weekly_estimates NUMERIC(1) = 0
)
AS


DECLARE
@investor_id NUMERIC(10),
@iname VARCHAR(150),
@temp_iname VARCHAR(150),
@next_prev_date DATETIME
,@next_year_date DATETIME
,@add_todate_redem NUMERIC(1)
,@add_todate_subs NUMERIC(1)
,@add_todate_trans_r NUMERIC(1)
,@add_todate_trans_s NUMERIC(1)
,@add_todate_roll_r NUMERIC(1)
,@add_todate_roll_s NUMERIC(1)
,@add_todate_switch_r NUMERIC(1)
,@add_todate_switch_s NUMERIC(1)
,@add_todate_exch_r NUMERIC(1)
,@add_todate_exch_s NUMERIC(1)
,@t_logic_cdid NUMERIC(2)
,@r_decval NUMERIC(1)

CREATE TABLE #report_data(
hid NUMERIC(10) NULL,
series_id NUMERIC(10) NULL,
class_id NUMERIC(10) NULL,
contact_id NUMERIC(10) NULL,
investor_id NUMERIC(10) NULL,
issue_trans_id NUMERIC(10) NULL,
invnum NUMERIC(10) NULL,
g_beginbal NUMERIC(16,6) NULL,
g_value NUMERIC(16,6) NULL,
nnav NUMERIC(16,6) NULL,
gnav NUMERIC(16,6) NULL,
prev_nnav NUMERIC(16,6) NULL,
prev_gnav NUMERIC(16,6) NULL,
yr_nnav NUMERIC(16,6) NULL,
yr_gnav NUMERIC(16,6) NULL,
yr_shares NUMERIC(16,6) NULL,
pctyear NUMERIC(16,6) NULL,
pctmonth NUMERIC(16,6) NULL,
net_profit NUMERIC(16,6) NULL,
cur VARCHAR(10) NULL,
transshares NUMERIC(16,6) NULL,
method_id NUMERIC(5) NULL,
iname VARCHAR(150) NULL,
series_name VARCHAR(25) NULL,
eq_balance NUMERIC(16,6) NULL,
primary key (investor_id,series_id,issue_trans_id,contact_id)
)

CREATE TABLE #single_contact(
hid NUMERIC(10) NULL,
contact_id NUMERIC(10) NULL,
)

CREATE TABLE #issue_transaction_id(
issue_transaction_id NUMERIC(10)
)

CREATE TABLE #fund_data(
issue_transaction_id NUMERIC(10)
,open_shares NUMERIC(16,6) NULL
,open_shares_val NUMERIC(16,6) NULL
,shares NUMERIC(16,6) NULL
,shares_val NUMERIC(16,6) NULL
,sub_activity NUMERIC(16,6) NULL
,sub_activity_val NUMERIC(16,6) NULL
,rdp_activity NUMERIC(16,6) NULL
,rdp_activity_val NUMERIC(16,6) NULL
,transfer_activity NUMERIC(16,6) NULL
,transfer_activity_val NUMERIC(16,6) NULL
,rollup_activity NUMERIC(16,6) NULL
,rollup_activity_val NUMERIC(16,6) NULL
,switch_activity NUMERIC(16,6) NULL
,switch_activity_val NUMERIC(16,6) NULL
,exch_activity NUMERIC(16,6) NULL
,exch_activity_val NUMERIC(16,6) NULL
)

CREATE TABLE #investors(
hid NUMERIC(10) NULL
,investor_id NUMERIC(10)
,iname VARCHAR(255) NULL
,method_id NUMERIC(5) NULL
,cur VARCHAR(50) NULL
,PRIMARY KEY(investor_id)
)

CREATE TABLE #investor_ror(
investor_id NUMERIC(10)
,class_id NUMERIC(10)
,series_id NUMERIC(10)
,mtd_ror NUMERIC(16,6)
,ytd_ror NUMERIC(16,6)
,gr_mtd_ror NUMERIC(16,6)
,gr_ytd_ror NUMERIC(16,6)
)

CREATE INDEX x_ir ON #investor_ror ( investor_id, class_id, series_id )

CREATE TABLE #contacts(
investor_id NUMERIC(10)
,contact_id NUMERIC(10)
,contact_type NUMERIC(5)
,flagmail NUMERIC(5)
,flagfax NUMERIC(5)
,flagemail NUMERIC(5)
,flagother NUMERIC(5)
,flagother2 NUMERIC(5)
,weekly_estimates NUMERIC(1)
,PRIMARY KEY (investor_id, contact_id)
)

IF @out_nav_info <> 1
CREATE TABLE #nav_info
(series_id NUMERIC(10) NULL
,nav_date DATETIME NULL
,nnav NUMERIC(16,6) NULL
,gnav NUMERIC(16,6) NULL
)


SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
,@parmemail = CASE WHEN @parmemail <> 1 THEN -1 ELSE 1 END
,@parmmail = CASE WHEN @parmmail <> 1 THEN -1 ELSE 1 END
,@parmfax = CASE WHEN @parmfax <> 1 THEN -1 ELSE 1 END
,@parmother = CASE WHEN @parmother <> 1 THEN -1 ELSE 1 END
,@parmother2 = CASE WHEN @parmother2 <> 1 THEN -1 ELSE 1 END
,@parmregaddr = CASE WHEN @parmregaddr <> 1 THEN -1 ELSE 2 END
,@t_logic_cdid = 1 /* t logic for partner statements */


SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'REDEM')
,@add_todate_subs = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'SUBS')
,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'TRANSFER_REDEM')
,@add_todate_trans_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'TRANSFER_SUBS')
,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'ROLLUP_REDEM')
,@add_todate_roll_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'ROLLUP_SUBS')
,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'SWITCH_REDEM')
,@add_todate_switch_s= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'SWITCH_SUBS')
,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'EXCHANGE_REDEM')
,@add_todate_exch_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
catalogdata
WHERE cdid = @t_logic_cdid AND cd_type =
@fund_id AND cd_charcode = 'EXCHANGE_SUBS')

IF NOT EXISTS (SELECT * FROM me_nav WHERE nav_date = @year_date AND fund_id
= @fund_id)
SELECT @next_year_date = (SELECT MIN(nav_date)
FROM me_nav
WHERE fund_id = @fund_id
AND nav_date <= @stmt_date
AND posted = 1)
ELSE
SELECT @next_year_date = @year_date

SELECT @r_decval = r_decval FROM fund WHERE fund_id = @fund_id

INSERT #investors
SELECT hid, investor_id, NULL, f.method_id, fl.description
FROM fundinvestors fi, fund f, flags fl
WHERE fi.fund_id = @fund_id
AND f.fund_id = @fund_id
AND f.ccy = fl.integer_value
AND fl.flag_name = 'CCY'
AND fi.hid BETWEEN @from_hid AND @to_hid

INSERT #contacts
SELECT i.investor_id, c.contact_id, fc.contact_type, c.flagmail, c.flagfax,
c.flagemail, c.flagother, c.flagother2, c.weekly_estimates
FROM #investors i, fundcontacts fc, contact c
WHERE fc.fund_id = @fund_id
AND fc.investor_id = i.investor_id
AND fc.contact_id = c.contact_id
AND c.contact_id = fc.contact_id

CREATE TABLE #bal(
bal_date DATETIME
,issue_transaction_id NUMERIC(10)
,series_id NUMERIC(10) NULL
,investor_id NUMERIC(10) NULL
,class_id NUMERIC(10) NULL
,sh_adj_ps NUMERIC(16,6) NULL
,shares NUMERIC(20,6) NULL
,PRIMARY KEY(issue_transaction_id, bal_date)
)

CREATE INDEX x_bal_2 ON #bal (investor_id, series_id, class_id)

CREATE TABLE #bal_yecalc(
bal_date DATETIME
,issue_transaction_id NUMERIC(10)
,min_yecalc NUMERIC(1)
)

INSERT #bal_yecalc
SELECT bal_date, issue_transaction_id, MIN(yecalc)
FROM balances
WHERE fund_id = @fund_id
AND bal_date <= @stmt_date
AND bal_date >= @prev_date
GROUP BY bal_date, issue_transaction_id

INSERT #bal
SELECT DISTINCT b.bal_date, b.issue_transaction_id, t.series_id,
t.investor_id, s.class_id, b.sh_adj_ps, b.shares
FROM balances b, trans t, series s, #bal_yecalc bb
WHERE b.fund_id = @fund_id
AND t.transaction_id = b.issue_transaction_id
AND b.issue_transaction_id = bb.issue_transaction_id
AND s.series_id = t.series_id
AND b.bal_date <= @stmt_date
AND b.bal_date >= @prev_date
AND b.bal_date = bb.bal_date
AND b.yecalc = bb.min_yecalc
AND t.posted = 1

INSERT #report_data
SELECT
DISTINCT
hid = fi.hid
,series_id = me.series_id
,class_id = b.class_id
,contact_id = c.contact_id
,investor_id = fi.investor_id
,issue_trans_id = b.issue_transaction_id
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,cur = CASE WHEN cl.ccy IS NULL THEN fi.cur
ELSE (SELECT f.description FROM flags f
WHERE f.flag_name = 'CCY'
AND f.integer_value = cl.ccy ) END
,NULL
,method_id = fi.method_id
,NULL,NULL, 0
FROM
me_nav me
,#bal b
,#investors fi
,#contacts c
,classes cl
WHERE me.fund_id = @fund_id
AND cl.fund_id = @fund_id
AND me.series_id = b.series_id
AND cl.class_id = b.class_id
AND fi.investor_id = b.investor_id
AND c.investor_id = fi.investor_id
AND me.nav_date = b.bal_date
AND me.seqnum = 1
AND me.posted = 1
AND ( c.contact_type = @parmregaddr
OR c.flagmail = @parmmail
OR c.flagfax = @parmfax
OR c.flagemail = @parmemail
OR c.flagother = @parmother
-- OR c.flagother2 = @parmother2
)
AND c.weekly_estimates >= @weekly_estimates
ORDER BY fi.hid,me.series_id,c.contact_id

IF @parmother2 = 1
BEGIN
INSERT #single_contact
SELECT hid, MIN(contact_id)
FROM #report_data
GROUP BY hid

DELETE #report_data
FROM #single_contact s
WHERE #report_data.hid = s.hid
AND #report_data.contact_id <> s.contact_id
END


INSERT #issue_transaction_id
SELECT DISTINCT issue_trans_id
FROM #report_data


EXEC subp_get_fund_data @prev_date, @stmt_date,
@temp_tables_exist=@temp_tables_exist

IF EXISTS ( SELECT 'FOUND' FROM v_additional_info WHERE fund_id = @fund_id
AND short_code = 'FR_ROLL_IGNORE' AND object_type = 100 )
BEGIN
UPDATE #fund_data
SET open_shares = open_shares + rollup_activity
,open_shares_val = open_shares_val + rollup_activity_val

DELETE #fund_data WHERE rollup_activity_val < 0
UPDATE #fund_data SET rollup_activity = 0, rollup_activity_val = 0
END

UPDATE #fund_data
SET transfer_activity_val = transfer_activity_val + exch_activity_val
,transfer_activity = transfer_activity + exch_activity

UPDATE #fund_data SET exch_activity_val = 0, exch_activity = 0

DELETE #fund_data WHERE open_shares < 0

DELETE #fund_data
WHERE open_shares = 0
AND shares = 0
AND sub_activity = 0
AND rdp_activity = 0
AND transfer_activity = 0
AND rollup_activity = 0
AND switch_activity = 0
AND exch_activity = 0
AND open_shares_val = 0
AND shares_val = 0
AND sub_activity_val = 0
AND rdp_activity_val = 0
AND transfer_activity_val = 0
AND rollup_activity_val = 0
AND switch_activity_val = 0
AND exch_activity_val = 0

------------------------------------------------
---- FIND NAV INFORMATION FOR YR/PREV DATES ----
------------------------------------------------
CREATE TABLE #date_info
(
series_id NUMERIC(10) NULL
,yr_min_date DATETIME NULL
,yr_nnav NUMERIC(16,6) NULL
,yr_gnav NUMERIC(16,6) NULL
,prev_min_date DATETIME NULL
,prev_nnav NUMERIC(16,6) NULL
,prev_gnav NUMERIC(16,6) NULL
)

CREATE INDEX x_dte ON #date_info( series_id, yr_min_date, prev_min_date )

CREATE TABLE #date_info2
(
series_id NUMERIC(10) NULL
,yr_min_date DATETIME NULL
,yr_nnav NUMERIC(16,6) NULL
,yr_gnav NUMERIC(16,6) NULL
,prev_min_date DATETIME NULL
,prev_nnav NUMERIC(16,6) NULL
,prev_gnav NUMERIC(16,6) NULL
)

CREATE TABLE #seqnum
(nav_date DATETIME NOT NULL
,max_seqnum NUMERIC(1) NULL
)

SELECT @year_date = (SELECT MIN(nav_date) FROM me_nav
WHERE fund_id = @fund_id
AND nav_date >= @year_date
AND nav_date <= @stmt_date)
,@prev_date = (SELECT MIN(nav_date) FROM me_nav
WHERE fund_id = @fund_id
AND nav_date >= @prev_date
AND nav_date <= @stmt_date)

INSERT #seqnum
SELECT nav_date, MAX(seqnum)
FROM me_nav
WHERE fund_id = @fund_id
AND nav_date >= @year_date
AND nav_date <= @stmt_date
GROUP BY nav_date

UPDATE #seqnum SET max_seqnum = 1
WHERE nav_date = @stmt_date

IF (SELECT method_id FROM fund WHERE fund_id = @fund_id) < 50 --EQ fund
BEGIN
DELETE #issue_transaction_id

INSERT #issue_transaction_id
SELECT DISTINCT issue_trans_id FROM #report_data

EXEC subp_eq_ror_logic @fund_id, @stmt_date, @year_date

UPDATE #date_info
SET yr_nnav = me.nnav
,yr_gnav = me.gnav
FROM me_nav me, #seqnum s
WHERE me.fund_id = @fund_id
AND me.nav_date = #date_info.yr_min_date
AND s.nav_date = #date_info.yr_min_date
AND me.seqnum = s.max_seqnum

UPDATE #date_info
SET prev_min_date = (SELECT MIN(bal_date)
FROM balances b, trans t,#report_data r
WHERE b.fund_id = @fund_id
AND t.fund_id = @fund_id
AND r.investor_id = #date_info.series_id
AND r.issue_trans_id =
b.issue_transaction_id
AND r.issue_trans_id = t.transaction_id
AND t.investor_id = #date_info.series_id
AND b.bal_date >= @prev_date
AND b.bal_date <= @stmt_date)

UPDATE #date_info
SET prev_nnav = me.nnav
,prev_gnav = me.gnav
FROM me_nav me, #seqnum s
WHERE me.fund_id = @fund_id
AND me.nav_date = #date_info.prev_min_date
AND s.nav_date = #date_info.prev_min_date
AND me.seqnum = s.max_seqnum


INSERT #date_info2
SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
d1.yr_gnav
, d2.prev_min_date, d2.prev_nnav,
d2.prev_gnav
FROM #date_info d1
,#date_info d2
WHERE d1.series_id = d2.series_id
AND d1.yr_min_date = (SELECT MIN(yr_min_date)
FROM #date_info d3
WHERE d3.series_id = d1.series_id)
AND d2.prev_min_date =(SELECT MIN(prev_min_date)
FROM #date_info d3
WHERE d3.series_id = d1.series_id)

----------------------------------
---- UPDATE MAIN RESULT TABLE ----
----------------------------------
UPDATE #report_data
SET prev_nnav = d.prev_nnav
,yr_nnav = d.yr_nnav
,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav ELSE
d.prev_gnav END
,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav ELSE
d.yr_gnav END
FROM #date_info2 d
WHERE d.series_id = #report_data.investor_id

END

ELSE

BEGIN
INSERT #date_info
SELECT me.series_id, me.nav_date, MAX(me.nnav), MAX(me.gnav), NULL,
NULL, NULL
FROM me_nav me, #seqnum s
WHERE me.fund_id = @fund_id
AND me.seqnum = s.max_seqnum
AND me.nav_date >= @year_date
AND me.nav_date <= @stmt_date
AND me.nav_date = s.nav_date
GROUP BY me.series_id ,me.nav_date

INSERT #date_info
SELECT me.series_id, NULL, NULL, NULL, me.nav_date, MAX(me.nnav),
MAX(me.gnav)
FROM me_nav me, #seqnum s
WHERE me.fund_id = @fund_id
AND me.seqnum = s.max_seqnum
AND me.nav_date >= @prev_date
AND me.nav_date <= @stmt_date
AND me.nav_date = s.nav_date
GROUP BY me.series_id, me.nav_date

INSERT #date_info2
SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
d1.yr_gnav
, d2.prev_min_date, d2.prev_nnav,
d2.prev_gnav
FROM #date_info d1
,#date_info d2
WHERE d1.series_id = d2.series_id
AND d1.yr_min_date = (SELECT MIN(yr_min_date)
FROM #date_info d3
WHERE d3.series_id = d1.series_id)
AND d2.prev_min_date =(SELECT MIN(prev_min_date)
FROM #date_info d3
WHERE d3.series_id = d1.series_id)

----------------------------------
---- UPDATE MAIN RESULT TABLE ----
----------------------------------
UPDATE #report_data
SET prev_nnav = d.prev_nnav
,yr_nnav = d.yr_nnav
,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav ELSE
d.prev_gnav END
,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav ELSE
d.yr_gnav END
FROM #date_info2 d
WHERE d.series_id = #report_data.series_id
END

UPDATE #report_data
SET gnav = me.gnav
,nnav = me.nnav
FROM me_nav me
WHERE me.nav_date = @stmt_date
AND me.series_id = #report_data.series_id
AND me.fund_id = @fund_id
AND me.seqnum = 1 /* always want monthly nav for current date*/

-- nnav is null if investor totally redeemed out at t+1
UPDATE #report_data
SET gnav = me.gnav
,nnav = me.nnav
FROM me_nav me, #fund_data f
WHERE me.nav_date = (SELECT MAX(bal_date) FROM balances b
WHERE b.fund_id = @fund_id
AND b.bal_date < @stmt_date
AND b.bal_date >= @prev_date
AND b.issue_transaction_id =
#report_data.issue_trans_id
AND b.shares = 0 )
AND me.series_id = #report_data.series_id
AND me.fund_id = @fund_id
AND me.seqnum = 1
AND #report_data.nnav IS NULL
AND #report_data.issue_trans_id = f.issue_transaction_id
AND f.shares_val = 0

UPDATE #report_data
SET eq_balance = isnull(b.sh_adj_ps, 0) * isnull(b.shares, 0)
FROM #bal b, #fund_data fd
WHERE b.issue_transaction_id = fd.issue_transaction_id
AND b.issue_transaction_id = #report_data.issue_trans_id
AND b.bal_date = @stmt_date

DROP TABLE #date_info2

UPDATE #report_data
SET pctmonth = ROUND(((nnav - prev_nnav) / prev_nnav)*100,6)
,pctyear = ROUND(((nnav - yr_nnav) / yr_nnav)*100,6)
,g_beginbal = ROUND(ISNULL(prev_gnav*f.open_shares,0),2)
,g_value = ROUND(ISNULL(gnav*f.shares,0),2)
,net_profit = ROUND( ( ISNULL( ((nnav-gnav) * (f.open_shares
+ CASE WHEN
@add_todate_subs = 1 THEN f.sub_activity ELSE 0 END
+ CASE WHEN
@add_todate_redem = 1 THEN f.rdp_activity ELSE 0 END
+ CASE WHEN
(@add_todate_trans_r + @add_todate_trans_s) > 0 THEN f.transfer_activity
ELSE 0 END
+ CASE WHEN
(@add_todate_roll_r + @add_todate_roll_s) > 0 THEN f.rollup_activity ELSE 0
END) ) ,0)
- ISNULL( ((prev_nnav-prev_gnav)
* (f.open_shares + CASE WHEN
(@add_todate_trans_r + @add_todate_trans_s) > 0 THEN f.transfer_activity
ELSE 0 END)),0)

)
,2)
,transshares = (f.open_shares - f.shares) * -1
FROM #fund_data f
WHERE f.issue_transaction_id = #report_data.issue_trans_id
AND prev_nnav <> 0
AND yr_nnav <> 0

----------------------------------------------------------------------------
-----
----------------------------------------------------------------------------
-----
IF @use_ror_logic = 1 AND (SELECT method_id FROM fund WHERE fund_id =
@fund_id) >= 50
BEGIN
INSERT #investor_ror
SELECT DISTINCT investor_id, class_id, series_id, 0, 0, 0, 0
FROM #report_data

EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date, @stmt_date

IF EXISTS (SELECT DISTINCT 'FOUND' FROM #investor_ror)
UPDATE #report_data
SET pctmonth = 0
,pctyear = 0
FROM fundinvestors fi
WHERE fi.fund_id = @fund_id
AND fi.investor_id = #report_data.investor_id
AND fi.gp_investor <> 1
--gp investor has only one series and special logic applies

UPDATE #report_data
SET pctmonth = i.mtd_ror
,pctyear = i.ytd_ror
FROM #investor_ror i
WHERE #report_data.investor_id = i.investor_id
AND #report_data.class_id = i.class_id
AND #report_data.series_id = i.series_id
AND #report_data.pctmonth = 0
AND #report_data.pctyear = 0
END
----------------------------------------------------------------------------
-----

SELECT @next_prev_date = ISNULL((SELECT MIN(nav_date) FROM me_nav
WHERE fund_id = @fund_id AND nav_date >
@prev_date),@stmt_date)

UPDATE #report_data
SET pctmonth = 0
, pctyear = CASE WHEN @show_ytd_ror_t1_redem = 1 THEN
ROUND(((prev_nnav - yr_nnav) / yr_nnav)*100,6) ELSE 0 END
FROM trans t, redemptions r, #fund_data f, #bal b
WHERE t.fund_id = @fund_id
AND f.issue_transaction_id = #report_data.issue_trans_id
AND r.issue_transaction_id = #report_data.issue_trans_id
AND b.issue_transaction_id = #report_data.issue_trans_id
AND b.bal_date = t.transaction_date
AND b.shares = 0 -- Check for full redemption
AND t.transaction_id = r.transaction_id
AND t.transaction_date >= @prev_date
AND t.transaction_date < @next_prev_date
AND f.shares = 0
AND f.shares_val = 0
AND f.shares_val = ( SELECT SUM(ff.shares_val) FROM #fund_data
ff, trans tt
WHERE tt.fund_id = @fund_id
AND tt.transaction_id =
ff.issue_transaction_id
AND tt.series_id =
#report_data.series_id
AND tt.investor_id =
#report_data.investor_id)
----------------------------------------------------------------------------
-----
----------------------------------------------------------------------------
-----

DECLARE c_name CURSOR FOR SELECT investor_id FROM #investors
OPEN c_name
FETCH c_name INTO @investor_id
WHILE(@@sqlstatus=0)
BEGIN
SELECT @temp_iname = (SELECT name FROM investor WHERE investor_id =
@investor_id)
EXEC p_strip_name @temp_iname, @iname output, @first_name_first

UPDATE #investors
SET iname = @iname
WHERE investor_id = @investor_id

FETCH c_name INTO @investor_id
END
CLOSE c_name
DEALLOCATE CURSOR c_name

UPDATE #report_data
SET iname = i.iname
FROM #investors i
WHERE i.investor_id = #report_data.investor_id


DELETE #report_data
FROM fund f, trans t
WHERE f.fund_id = @fund_id
AND f.report_statement_id = 603
AND #report_data.issue_trans_id = t.activity_trans_id
AND t.transaction_type = 2
AND t.proceeds_entered > 0

-------------------------------------------
------------ OUTPUT STATEMENTS ------------
-------------------------------------------

INSERT #out_result
SELECT
DISTINCT
r.hid
, r.series_id
, r.contact_id
, r.investor_id
, r.issue_trans_id
, r.invnum
, r.g_beginbal
, r.g_value
, r.nnav
, r.gnav
, r.prev_nnav
, r.prev_gnav
, r.yr_nnav
, r.yr_gnav
, r.yr_shares
, r.pctyear
, r.pctmonth
, r.net_profit
, r.cur
, r.transshares
, r.method_id
, r.iname
, r.series_name
, f.open_shares
, f.open_shares_val
, f.shares
, f.shares_val
, f.sub_activity
, f.sub_activity_val
, f.rdp_activity
, f.rdp_activity_val
, f.transfer_activity
, f.transfer_activity_val
, f.rollup_activity
, f.rollup_activity_val
, f.switch_activity
, f.switch_activity_val
, r.eq_balance
FROM #report_data r
,#fund_data f
WHERE f.issue_transaction_id = r.issue_trans_id


IF @out_nav_info <> 0
INSERT #nav_info
SELECT
series_id = series_id
,nav_date = yr_min_date
,nnav = yr_nnav
,gnav = yr_gnav
FROM #date_info
WHERE yr_min_date IS NOT NULL

DROP TABLE #report_data
DROP TABLE #fund_data
DROP TABLE #date_info

GO

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com...
> Please show us how you created the temporary tables. If you enclosed
> the table name in doublequotes then you may have created a permanent
> table that just happens to have a name beginning with #. Look in
> SYSTABLE to be sure; if it's there, it's not a local temporary table.
>
> On 19 Mar 2004 07:28:17 -0800, "Brett Morgan" <bmorgan@imsi.com>
> wrote:
>
> >I've discovered a weird locking problem in a stored procedure we are
using
> >that's locking temp tables across connections. I am running Sybase ASA
> >8.0.3, build 4322.
> >
> >The procedure creates some local temp tables, inserts, and updates into
the
> >tables then finally selects data from tables. If I run the procedure in
two
> >different instances of DBISQL (autocommit off) the second execution
hangs.
> >If I commit on the first execution the second execution executes. I ran
> >sa_conn_info and saw that the first execution was blocking the second. I
> >couldn't figure out why since all the inserts/updates are done to temp
> >tables.
> >
> >I then turned blocking off using, "set option blocking = off." I again
> >executed the procedure in two instances of DBISQL. I received an error
> >message for the second execution saying that a temp table is locked, "ASA
> >Error -210: User 'DBA' has the row in '#report_data' locked."
> >
> >It was my understanding that temp tables are exclusive to a connection,
> >therefore why would they be locked across two connections? Any help
would
> >be much appreciated.
> >
> >Brett Morgan
> >bmorgan@imsi.com
> >
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com


Peter Bumbulis Posted on 2004-03-19 18:35:27.0Z
Newsgroups: ianywhere.public.general
Subject: Re: temp table locking
References: <405b1191$1@forums-1-dub> <369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com> <405b2bdc@forums-2-dub>
Message-ID: <opr44gldlkgaovig@bumbulis-xp.sybase.com>
From: "Peter Bumbulis" <bumbulis@ianywhere.com>
Organization: iAnywhere Solutions
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
User-Agent: Opera M2/7.50 (Win32, build 3613)
NNTP-Posting-Host: bumbulis-xp.sybase.com
X-Original-NNTP-Posting-Host: bumbulis-xp.sybase.com
Date: 19 Mar 2004 10:35:27 -0800
X-Trace: forums-1-dub 1079721327 10.25.99.196 (19 Mar 2004 10:35:27 -0800)
X-Original-Trace: 19 Mar 2004 10:35:27 -0800, bumbulis-xp.sybase.com
Lines: 1495
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2808
Article PK: 6279

Does it make any difference if you change the "create table #t ..." to
"declare local temporary table #t..."?

Peter

On 19 Mar 2004 09:20:31 -0800, Brett Morgan <bmorgan@imsi.com> wrote:

> Here is the procs, one main and one sub-proc. It's a beast. The table
> names are not in double quote and not appearing in SYSTABLE. Thanks
>
> CREATE PROC p_part_stmt
> (
> @fund_id NUMERIC(10)
> ,@year_date CHAR(12) = NULL
> ,@prev_date CHAR(12) = NULL
> ,@quarter_date CHAR(12) = NULL
> ,@stmt_date CHAR(12)
> ,@from_hid NUMERIC(10)
> ,@to_hid NUMERIC(10)
> ,@fund_summary NUMERIC(1)
> ,@summary_funds VARCHAR(50) = NULL
> ,@fund_to_date_info NUMERIC(1) = 0
> ,@sort_order NUMERIC(2) = 0
> ,@signature_info NUMERIC(1) = 0
> ,@out_nav_info NUMERIC(1) = 1
> ,@parmemail NUMERIC(1) = 1
> ,@parmmail NUMERIC(1) = 1
> ,@parmfax NUMERIC(1) = 1
> ,@parmother NUMERIC(1) = 0
> ,@parmother2 NUMERIC(1) = 0
> ,@parmregaddr NUMERIC(1) = 0
> ,@first_name_first NUMERIC(1) = 1
> ,@calc_incep_ror NUMERIC(1) = 0
> ,@gp_filter NUMERIC(1) = NULL
> ,@investor_level NUMERIC(1) = 0
> ,@no_composite_ror NUMERIC(1) = 1
> ,@export_output NUMERIC(1) = 0
> ,@income_stmt NUMERIC(1) = 0
> ,@show_ytd_ror_t1_redem NUMERIC(1) = 0
> ,@weekly_estimates NUMERIC(1) = 0
> )
> AS
>
>
> DECLARE
> @hid NUMERIC(10),
> @series_id NUMERIC(10),
> @contact_id NUMERIC(10),
> @investor_id NUMERIC(10),
> @issue_trans_id NUMERIC(10),
> @nnav NUMERIC(16,6),
> @gnav NUMERIC(16,6),
> @cur VARCHAR(10),
> @method_id NUMERIC(5),
> @iname VARCHAR(150),
> @temp_iname VARCHAR(150),
> @group_by_class NUMERIC(1),
> @r_decval NUMERIC(1)
> ,@add_todate_redem NUMERIC(1)
> ,@add_todate_trans_r NUMERIC(1)
> ,@add_todate_roll_r NUMERIC(1)
> ,@add_todate_switch_r NUMERIC(1)
> ,@add_todate_exch_r NUMERIC(1)
> ,@msg TEXT
>
>
> CREATE TABLE #out_result(
> hid NUMERIC(10) NULL
> ,series_id NUMERIC(10) NULL
> ,contact_id NUMERIC(10) NULL
> ,investor_id NUMERIC(10) NULL
> ,issue_trans_id NUMERIC(10) NULL
> ,invnum NUMERIC(10) NULL
> ,g_beginbal NUMERIC(16,6) NULL
> ,g_value NUMERIC(16,6) NULL
> ,nnav NUMERIC(16,6) NULL
> ,gnav NUMERIC(16,6) NULL
> ,prev_nnav NUMERIC(16,6) NULL
> ,prev_gnav NUMERIC(16,6) NULL
> ,yr_nnav NUMERIC(16,6) NULL
> ,yr_gnav NUMERIC(16,6) NULL
> ,yr_shares NUMERIC(16,6) NULL
> ,pctyear NUMERIC(16,6) NULL
> ,pctmonth NUMERIC(16,6) NULL
> ,net_profit NUMERIC(16,6) NULL
> ,cur VARCHAR(10) NULL
> ,transshares NUMERIC(16,6) NULL
> ,method_id NUMERIC(5) NULL
> ,iname VARCHAR(150) NULL
> ,series_name VARCHAR(25) NULL
> ,open_shares NUMERIC(16,6) NULL
> ,open_shares_val NUMERIC(16,6) NULL
> ,shares NUMERIC(16,6) NULL
> ,shares_val NUMERIC(16,6) NULL
> ,sub_activity NUMERIC(16,6) NULL
> ,sub_activity_val NUMERIC(16,6) NULL
> ,rdp_activity NUMERIC(16,6) NULL
> ,rdp_activity_val NUMERIC(16,6) NULL
> ,transfer_activity NUMERIC(16,6) NULL
> ,transfer_activity_val NUMERIC(16,6) NULL
> ,rollup_activity NUMERIC(16,6) NULL
> ,rollup_activity_val NUMERIC(16,6) NULL
> ,switch_activity NUMERIC(16,6) NULL
> ,switch_activity_val NUMERIC(16,6) NULL
> ,eq_balance NUMERIC(16,6) NULL
> )
>
> CREATE TABLE #nav_info
> (series_id NUMERIC(10) NULL
> ,nav_date DATETIME NULL
> ,nnav NUMERIC(16,6) NULL
> ,gnav NUMERIC(16,6) NULL
> )
>
> CREATE TABLE #final
> (hid NUMERIC(10) NULL
> ,contact_id NUMERIC(10) NULL
> ,beginbal NUMERIC(16,6) NULL
> ,g_beginbal NUMERIC(16,6) NULL
> ,value NUMERIC(16,6) NULL
> ,g_value NUMERIC(16,6) NULL
> ,subsrip NUMERIC(16,6) NULL
> ,redemp NUMERIC(16,6) NULL
> ,open_shares NUMERIC(16,6) NULL
> ,shares NUMERIC(16,6) NULL
> ,transshares NUMERIC(16,6) NULL
> ,pctincep NUMERIC(16,6) NULL
> ,pctyear NUMERIC(16,6) NULL
> ,pctmonth NUMERIC(16,6) NULL
> ,net_profit NUMERIC(16,6) NULL
> ,cur VARCHAR(25) NULL
> ,iname VARCHAR(255) NULL
> ,contact VARCHAR(255) NULL
> ,rep VARCHAR(255) NULL
> ,addr1 VARCHAR(255) NULL
> ,addr2 VARCHAR(255) NULL
> ,addr3 VARCHAR(255) NULL
> ,addr4 VARCHAR(255) NULL
> ,addr5 VARCHAR(255) NULL
> ,phone VARCHAR(255) NULL
> ,fax VARCHAR(255) NULL
> ,email VARCHAR(255) NULL
> ,series_name VARCHAR(50) NULL
> ,nnav NUMERIC(16,6) NULL
> ,r_decnav NUMERIC(5) NULL
> ,r_decsh NUMERIC(5) NULL
> ,class VARCHAR(50) NULL
> ,identifier2 VARCHAR(255) NULL
> ,class_id NUMERIC(10) NULL
> ,investor_id NUMERIC(10) NULL
> ,series_id NUMERIC(10) NULL
> ,eq_balance NUMERIC(16,6) NULL
> ,prev_nnav NUMERIC(16,6) NULL
> )
>
> CREATE TABLE #investor_ror(
> investor_id NUMERIC(10)
> ,class_id NUMERIC(10)
> ,series_id NUMERIC(10)
> ,mtd_ror NUMERIC(16,6)
> ,ytd_ror NUMERIC(16,6)
> ,gr_mtd_ror NUMERIC(16,6)
> ,gr_ytd_ror NUMERIC(16,6)
> )
>
> IF @to_hid = 99999
> SELECT @to_hid = 999999
>
> IF @year_date IS NULL
> SELECT @year_date = CONVERT( CHAR(4),(DATEPART(YEAR,@stmt_date)-1) ) +
> '-12-31'
>
> SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
> SELECT @r_decval = ( SELECT r_decval FROM fund WHERE fund_id = @fund_id )
>
> SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0) FROM
> catalogdata
> WHERE cdid = 1 AND cd_type = @fund_id AND
> cd_charcode = 'REDEM')
> ,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = 1 AND cd_type = @fund_id AND
> cd_charcode = 'TRANSFER_REDEM')
> ,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = 1 AND cd_type = @fund_id AND
> cd_charcode = 'ROLLUP_REDEM')
> ,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = 1 AND cd_type = @fund_id AND
> cd_charcode = 'SWITCH_REDEM')
> ,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = 1 AND cd_type = @fund_id AND
> cd_charcode = 'EXCHANGE_REDEM')
>
> EXEC subp_part_stmt_main @fund_id, @year_date, @prev_date, @quarter_date,
> @stmt_date, @from_hid, @to_hid
> ,@out_nav_info, @parmemail, @parmmail, @parmfax,
> @parmother, @parmother2, @parmregaddr, @first_name_first
> ,@use_ror_logic = @no_composite_ror,
> @show_ytd_ror_t1_redem = @show_ytd_ror_t1_redem
> ,@weekly_estimates=@weekly_estimates
>
> UPDATE #out_result
> SET rdp_activity = CASE WHEN transfer_activity < 0 THEN
> rdp_activity
> + transfer_activity ELSE rdp_activity END
> ,rdp_activity_val = CASE WHEN transfer_activity_val < 0 THEN
> rdp_activity_val + transfer_activity_val ELSE rdp_activity_val END
> ,sub_activity = CASE WHEN transfer_activity > 0 THEN
> sub_activity
> + transfer_activity ELSE sub_activity END
> ,sub_activity_val = CASE WHEN transfer_activity_val > 0 THEN
> sub_activity_val + transfer_activity_val ELSE sub_activity_val END
>
> UPDATE #out_result
> SET rdp_activity = CASE WHEN rollup_activity < 0 THEN
> rdp_activity +
> rollup_activity ELSE rdp_activity END
> ,rdp_activity_val = CASE WHEN rollup_activity_val < 0 THEN
> rdp_activity_val + rollup_activity_val ELSE rdp_activity_val END
> ,sub_activity = CASE WHEN rollup_activity > 0 THEN
> sub_activity +
> rollup_activity ELSE sub_activity END
> ,sub_activity_val = CASE WHEN rollup_activity_val > 0 THEN
> sub_activity_val + rollup_activity_val ELSE sub_activity_val END
>
> UPDATE #out_result
> SET rdp_activity = CASE WHEN switch_activity < 0 THEN
> rdp_activity +
> switch_activity ELSE rdp_activity END
> ,rdp_activity_val = CASE WHEN switch_activity_val < 0 THEN
> rdp_activity_val + switch_activity_val ELSE rdp_activity_val END
> ,sub_activity = CASE WHEN switch_activity > 0 THEN
> sub_activity +
> switch_activity ELSE sub_activity END
> ,sub_activity_val = CASE WHEN switch_activity_val > 0 THEN
> sub_activity_val + switch_activity_val ELSE sub_activity_val END
>
>
>
> IF @no_composite_ror = 0
> BEGIN
> INSERT #investor_ror
> SELECT DISTINCT o.investor_id, s.class_id, o.series_id, 0, 0, 0, 0
> FROM #out_result o, series s
> WHERE s.fund_id = @fund_id
> AND s.series_id = o.series_id
>
> EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date,
> @stmt_date
> END
>
> /*************************************************************************/
>
> IF EXISTS (SELECT * FROM rate_of_return WHERE fund_id = @fund_id AND
> series_id = 0) AND @no_composite_ror = 1
> BEGIN
> SELECT @group_by_class = 1
>
> INSERT #final
> SELECT
> hid = rd.hid
> ,contact_id = rd.contact_id
> ,beginbal = ISNULL(SUM(open_shares_val) ,0)
> ,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
> ,value = ISNULL(SUM(shares_val) ,0)
> ,g_value = ISNULL(SUM(g_value) ,0)
> ,subsrip = ISNULL(SUM(sub_activity_val),0)
> ,redemp = ISNULL(SUM(rdp_activity_val),0)
> ,open_shares= ISNULL(SUM(open_shares) ,0)
> ,shares = ISNULL(SUM(rd.shares) ,0)
> ,transshares= SUM(transshares)
> ,pctincep = 0
> ,pctyear = MAX(pctyear)
> ,pctmonth = MAX(pctmonth)
> ,net_profit = SUM(ISNULL(rd.net_profit,0))
> ,cur = MAX(cur)
> ,iname = iname
> ,contact = MAX(c.contact)
> ,rep = MAX(c.rep)
> ,addr1 = MAX(c.addr1)
> ,addr2 = MAX(c.addr2)
> ,addr3 = MAX(c.addr3)
> ,addr4 = MAX(c.addr4)
> ,addr5 = MAX(c.addr5)
> ,phone = MAX(c.phone)
> ,fax = MAX(c.fax)
> ,email = MAX(c.email)
> ,series_name= RTRIM(cl.identifier)
> ,nnav = MAX(rd.nnav)
> ,r_decnav = MAX(f.r_decnav)
> ,r_decsh = MAX(f.r_decsh)
> ,class = cl.identifier
> ,identifier2= MAX(cl.identifier2)
> ,class_id = cl.class_id
> ,investor_id= rd.investor_id
> ,series_id = NULL
> ,0
> ,prev_nnav = MAX(rd.prev_nnav)
> FROM
> contact c
> ,#out_result rd
> ,series se
> ,classes cl
> ,fund f
> WHERE c.contact_id = rd.contact_id
> AND se.series_id = rd.series_id
> AND se.fund_id = @fund_id
> AND cl.fund_id = se.fund_id
> AND f.fund_id = se.fund_id
> AND cl.class_id = se.class_id
> GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
> cl.class_id, rd.iname
> END
>
> ELSE
>
> BEGIN
> SELECT @group_by_class = 0
>
> INSERT #final
> SELECT
> hid = rd.hid
> ,contact_id = rd.contact_id
> ,beginbal = ISNULL(SUM(open_shares_val) ,0)
> ,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
> ,value = ISNULL(SUM(shares_val) ,0)
> ,g_value = ISNULL(SUM(g_value) ,0)
> ,subsrip = ISNULL(SUM(sub_activity_val),0)
> ,redemp = ISNULL(SUM(rdp_activity_val),0)
> ,open_shares= ISNULL(SUM(open_shares) ,0)
> ,shares = ISNULL(SUM(rd.shares) ,0)
> ,transshares= SUM(transshares)
> ,pctincep = 0
> ,pctyear = MAX(pctyear)
> ,pctmonth = MAX(pctmonth)
> ,net_profit = SUM(ISNULL(rd.net_profit,0))
> ,cur = MAX(cur)
> ,iname = iname
> ,contact = MAX(c.contact)
> ,rep = MAX(c.rep)
> ,addr1 = MAX(c.addr1)
> ,addr2 = MAX(c.addr2)
> ,addr3 = MAX(c.addr3)
> ,addr4 = MAX(c.addr4)
> ,addr5 = MAX(c.addr5)
> ,phone = MAX(c.phone)
> ,fax = MAX(c.fax)
> ,email = MAX(c.email)
> ,series_name= RTRIM(cl.identifier) + '_' +RTRIM(se.identifier)
> ,nnav = MAX(rd.nnav)
> ,r_decnav = MAX(f.r_decnav)
> ,r_decsh = MAX(f.r_decsh)
> ,class = cl.identifier
> ,identifier2= MAX(cl.identifier2)
> ,class_id = cl.class_id
> ,investor_id= rd.investor_id
> ,series_id = rd.series_id
> ,0
> ,prev_nnav = MAX(rd.prev_nnav)
> FROM
> contact c
> ,#out_result rd
> ,series se
> ,classes cl
> ,fund f
> WHERE c.contact_id = rd.contact_id
> AND se.series_id = rd.series_id
> AND se.fund_id = @fund_id
> AND cl.fund_id = se.fund_id
> AND f.fund_id = se.fund_id
> AND cl.class_id = se.class_id
> GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
> cl.class_id, rd.series_id, se.identifier, rd.iname
>
> END
> ----------------------------------------------------------------------------
> ----------
>
> UPDATE #final f
> SET transshares = ( SELECT SUM(redemp) FROM #final ff
> WHERE f.hid = ff.hid
> AND f.contact_id = ff.contact_id
> )
> UPDATE #final f
> SET transshares = transshares + ( SELECT SUM(subsrip) FROM #final ff
> WHERE f.hid = ff.hid
> AND f.contact_id = ff.contact_id
> )
> UPDATE #final f
> SET g_beginbal = ( SELECT SUM(ROUND(beginbal,@r_decval)) FROM #final
> ff
> WHERE f.hid = ff.hid
> AND f.contact_id = ff.contact_id
> )
>
> UPDATE #final f
> SET g_value = ( SELECT SUM(ROUND(value,@r_decval)) FROM #final ff
> WHERE f.hid = ff.hid
> AND f.contact_id = ff.contact_id
> )
> /*************************************************************************/
>
> IF NOT EXISTS ( SELECT 'FOUND' FROM fund_addl_info f, additional_info a
> WHERE a.info_id = f.info_id AND a.short_code =
> 'FR_PARTNERSHIP'
> AND f.fund_id = @fund_id )
> DELETE #final
> FROM trans t, redemptions r
> WHERE t.fund_id = @fund_id
> AND t.transaction_date >= @prev_date -- date range needed for
> quarterly
> stmts
> AND t.transaction_date < @stmt_date
> AND r.series_id = #final.series_id
> AND t.transaction_id = r.transaction_id
> AND #final.shares = 0
> AND t.investor_id = #final.investor_id
> AND ( (@add_todate_redem=1 AND t.activity_type=0 AND
> transaction_type=2)
> OR (@add_todate_trans_r=1 AND t.activity_type=1 AND
> transaction_type=2)
> OR (@add_todate_roll_r=1 AND t.activity_type=3 AND
> transaction_type=2)
> OR (@add_todate_switch_r=1 AND t.switch_transaction=1 AND
> transaction_type=2)
> OR (@add_todate_exch_r=1 AND t.activity_type=4 AND
> transaction_type=2)
> )
>
> IF @investor_level = 1
> BEGIN
> INSERT #final
> SELECT DISTINCT
> hid ,contact_id=0 ,beginbal ,g_beginbal
> ,value ,g_value ,subsrip ,redemp
> ,open_shares ,shares ,transshares ,pctincep
> ,pctyear ,pctmonth ,net_profit ,cur
> ,iname ,'','','','','','','','','',''
> ,series_name ,nnav ,r_decnav ,r_decsh
> ,class ,identifier2 ,class_id ,investor_id
> ,series_id ,eq_balance ,prev_nnav
> FROM #final
>
> DELETE #final WHERE contact_id <> 0
> END
> ----------------------------------------------------------------------------
> ----------
>
>
> IF @calc_incep_ror = 1
> EXEC subp_ps_incep_ror @fund_id, @stmt_date, @group_by_class
>
>
> SELECT @msg = ( SELECT text_val FROM fund_addl_info f, additional_info a
> WHERE f.info_id = a.info_id and UPPER(short_code) =
> 'COMMENT'
> AND f.fund_id = @fund_id
> AND f.object_id = @fund_id)
>
>
> ----------------------------------------------------------------------------
> ----------
>
>
> /***************************************************************************
> /
> /********************* Check for hardcoded ror values
> ******************/
> /***************************************************************************
> /
>
> UPDATE #final
> SET pctmonth = r.net_mtd
> ,pctyear = r.net_ytd
> ,pctincep = r.net_itd
> FROM ror_hardcode r, fundinvestors fi
> WHERE r.fund_id = @fund_id
> AND fi.fund_id = @fund_id
> AND fi.hid = #final.hid
> AND fi.investor_id = r.investor_id
> AND r.series_id = #final.series_id
>
>
> /***************************************************************************
> /
> /***************************************************************************
> /
> --------------------------------------------------
> ------------ OUTPUT SELECT STATEMENTS ------------
> --------------------------------------------------
> IF @export_output = 0
> BEGIN
> IF @fund_summary=0
> EXEC p_ps_fund_info @fund_id, @year_date, @prev_date, @prev_date,
> @stmt_date, @fund_to_date_info, @signature_info=@signature_info,
> @income_stmt=@income_stmt
>
> ELSE IF @fund_summary=1
> BEGIN
> SELECT @summary_funds = (SELECT shortname FROM fund WHERE
> fund_id =
> @fund_id)
> EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
> @prev_date,
> @stmt_date, @fund_to_date_info, @summary_funds,
> @signature_info=@signature_info, @gp_filter=@gp_filter,
> @income_stmt=@income_stmt
> END
>
> ELSE IF @fund_summary=2
> EXEC p_ps_fund_info @fund_id, @year_date, @prev_date, @prev_date,
> @stmt_date, @fund_to_date_info, @summary_funds,
> @signature_info=@signature_info, @gp_filter=@gp_filter,
> @income_stmt=@income_stmt
> END
>
> ----------------------------------------------------------------------------
> ----------
> --------------------------- OUTPUT SELECT
> ORTS ------------------------------------
> ----------------------------------------------------------------------------
> ----------
>
> IF @no_composite_ror = 0 AND @export_output = 0
> SELECT DISTINCT hid ,contact_id ,beginbal ,g_beginbal
> ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> ,i.mtd_ror ,i.ytd_ror ,prev_nnav
> FROM #final f, #investor_ror i
> WHERE f.investor_id = i.investor_id
> AND f.class_id = i.class_id
> AND i.series_id = ISNULL(f.series_id,i.series_id)
> ORDER BY hid, contact_id, iname, cur, series_name
>
> ELSE IF @sort_order = 0 AND @export_output = 0
> SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> ,eq_balance ,0,0,@msg
> FROM #final
> ORDER BY series_name, iname, hid, contact_id, cur
>
> ELSE IF @sort_order = 1 AND @export_output = 0
> SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> ,0,0,0,@msg
> FROM #final
> ORDER BY hid, contact_id, iname, cur, series_name
>
> ELSE IF @sort_order = 2 AND @export_output = 0
> SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> FROM #final
> ORDER BY class, hid, contact_id, iname, cur, series_name
>
> ELSE IF @sort_order = 3 AND @export_output = 0 -- Wellington statement
> needs cl.identifier2
> SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> ,0,0,0,identifier2
> FROM #final
> ORDER BY hid, contact_id, iname, cur, series_name
>
> ELSE IF @sort_order = 4 AND @export_output = 0
> SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> FROM #final
> ORDER BY hid, contact_id, iname, cur, series_name
> ELSE IF @export_output = 0
> SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> ,g_value ,subsrip ,redemp ,open_shares ,shares
> ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> ,cur
> ,iname ,contact ,rep ,addr1 ,addr2
> ,addr3 ,addr4 ,addr5 ,phone ,fax
> ,email ,series_name ,nnav ,r_decnav ,r_decsh
> FROM #final
> ORDER BY hid, contact_id, iname, cur, series_name
>
> ELSE IF @export_output = 1 AND @no_composite_ror = 1
> INSERT #output
> SELECT *,0,0 FROM #final
> ORDER BY iname, hid, series_name
>
> ELSE IF @export_output = 1 AND @no_composite_ror = 0
> INSERT #output
> SELECT f.*,i.mtd_ror,i.ytd_ror FROM #final f, #investor_ror i
> WHERE f.investor_id = i.investor_id
> AND f.class_id = i.class_id
> AND i.series_id = ISNULL(f.series_id,i.series_id)
> ORDER BY iname, hid, series_name
>
> ELSE IF @export_output = 2
> SELECT hid ,contact_id ,beginbal ,value ,subsrip
> ,redemp ,open_shares ,shares ,pctincep ,pctyear
> ,pctmonth ,net_profit ,series_name, nnav
> FROM #final
> ORDER BY hid, contact_id, series_name
>
> DROP TABLE #nav_info
> DROP TABLE #out_result
> DROP TABLE #final
> DROP TABLE #investor_ror
> GO
>
>
> /*********************
> * Second proc *
> *********************/
> CREATE PROC subp_part_stmt_main
> (
> @fund_id NUMERIC(10)
> ,@year_date CHAR(12)
> ,@prev_date CHAR(12) = NULL
> ,@quarter_date CHAR(12) = NULL
> ,@stmt_date CHAR(12)
> ,@from_hid NUMERIC(10)
> ,@to_hid NUMERIC(10)
> ,@out_nav_info NUMERIC(1) = 1
> ,@parmemail NUMERIC(1) = 1
> ,@parmmail NUMERIC(1) = 1
> ,@parmfax NUMERIC(1) = 1
> ,@parmother NUMERIC(1) = 0
> ,@parmother2 NUMERIC(1) = 0
> ,@parmregaddr NUMERIC(1) = 0
> ,@first_name_first NUMERIC(1) = 1
> ,@temp_tables_exist NUMERIC(1) = 0
> ,@use_ror_logic NUMERIC(1) = 1
> ,@show_ytd_ror_t1_redem NUMERIC(1) = 0
> ,@weekly_estimates NUMERIC(1) = 0
> )
> AS
>
>
> DECLARE
> @investor_id NUMERIC(10),
> @iname VARCHAR(150),
> @temp_iname VARCHAR(150),
> @next_prev_date DATETIME
> ,@next_year_date DATETIME
> ,@add_todate_redem NUMERIC(1)
> ,@add_todate_subs NUMERIC(1)
> ,@add_todate_trans_r NUMERIC(1)
> ,@add_todate_trans_s NUMERIC(1)
> ,@add_todate_roll_r NUMERIC(1)
> ,@add_todate_roll_s NUMERIC(1)
> ,@add_todate_switch_r NUMERIC(1)
> ,@add_todate_switch_s NUMERIC(1)
> ,@add_todate_exch_r NUMERIC(1)
> ,@add_todate_exch_s NUMERIC(1)
> ,@t_logic_cdid NUMERIC(2)
> ,@r_decval NUMERIC(1)
>
> CREATE TABLE #report_data(
> hid NUMERIC(10) NULL,
> series_id NUMERIC(10) NULL,
> class_id NUMERIC(10) NULL,
> contact_id NUMERIC(10) NULL,
> investor_id NUMERIC(10) NULL,
> issue_trans_id NUMERIC(10) NULL,
> invnum NUMERIC(10) NULL,
> g_beginbal NUMERIC(16,6) NULL,
> g_value NUMERIC(16,6) NULL,
> nnav NUMERIC(16,6) NULL,
> gnav NUMERIC(16,6) NULL,
> prev_nnav NUMERIC(16,6) NULL,
> prev_gnav NUMERIC(16,6) NULL,
> yr_nnav NUMERIC(16,6) NULL,
> yr_gnav NUMERIC(16,6) NULL,
> yr_shares NUMERIC(16,6) NULL,
> pctyear NUMERIC(16,6) NULL,
> pctmonth NUMERIC(16,6) NULL,
> net_profit NUMERIC(16,6) NULL,
> cur VARCHAR(10) NULL,
> transshares NUMERIC(16,6) NULL,
> method_id NUMERIC(5) NULL,
> iname VARCHAR(150) NULL,
> series_name VARCHAR(25) NULL,
> eq_balance NUMERIC(16,6) NULL,
> primary key (investor_id,series_id,issue_trans_id,contact_id)
> )
>
> CREATE TABLE #single_contact(
> hid NUMERIC(10) NULL,
> contact_id NUMERIC(10) NULL,
> )
>
> CREATE TABLE #issue_transaction_id(
> issue_transaction_id NUMERIC(10)
> )
>
> CREATE TABLE #fund_data(
> issue_transaction_id NUMERIC(10)
> ,open_shares NUMERIC(16,6) NULL
> ,open_shares_val NUMERIC(16,6) NULL
> ,shares NUMERIC(16,6) NULL
> ,shares_val NUMERIC(16,6) NULL
> ,sub_activity NUMERIC(16,6) NULL
> ,sub_activity_val NUMERIC(16,6) NULL
> ,rdp_activity NUMERIC(16,6) NULL
> ,rdp_activity_val NUMERIC(16,6) NULL
> ,transfer_activity NUMERIC(16,6) NULL
> ,transfer_activity_val NUMERIC(16,6) NULL
> ,rollup_activity NUMERIC(16,6) NULL
> ,rollup_activity_val NUMERIC(16,6) NULL
> ,switch_activity NUMERIC(16,6) NULL
> ,switch_activity_val NUMERIC(16,6) NULL
> ,exch_activity NUMERIC(16,6) NULL
> ,exch_activity_val NUMERIC(16,6) NULL
> )
>
> CREATE TABLE #investors(
> hid NUMERIC(10) NULL
> ,investor_id NUMERIC(10)
> ,iname VARCHAR(255) NULL
> ,method_id NUMERIC(5) NULL
> ,cur VARCHAR(50) NULL
> ,PRIMARY KEY(investor_id)
> )
>
> CREATE TABLE #investor_ror(
> investor_id NUMERIC(10)
> ,class_id NUMERIC(10)
> ,series_id NUMERIC(10)
> ,mtd_ror NUMERIC(16,6)
> ,ytd_ror NUMERIC(16,6)
> ,gr_mtd_ror NUMERIC(16,6)
> ,gr_ytd_ror NUMERIC(16,6)
> )
>
> CREATE INDEX x_ir ON #investor_ror ( investor_id, class_id, series_id )
>
> CREATE TABLE #contacts(
> investor_id NUMERIC(10)
> ,contact_id NUMERIC(10)
> ,contact_type NUMERIC(5)
> ,flagmail NUMERIC(5)
> ,flagfax NUMERIC(5)
> ,flagemail NUMERIC(5)
> ,flagother NUMERIC(5)
> ,flagother2 NUMERIC(5)
> ,weekly_estimates NUMERIC(1)
> ,PRIMARY KEY (investor_id, contact_id)
> )
>
> IF @out_nav_info <> 1
> CREATE TABLE #nav_info
> (series_id NUMERIC(10) NULL
> ,nav_date DATETIME NULL
> ,nnav NUMERIC(16,6) NULL
> ,gnav NUMERIC(16,6) NULL
> )
>
>
> SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
> ,@parmemail = CASE WHEN @parmemail <> 1 THEN -1 ELSE 1 END
> ,@parmmail = CASE WHEN @parmmail <> 1 THEN -1 ELSE 1 END
> ,@parmfax = CASE WHEN @parmfax <> 1 THEN -1 ELSE 1 END
> ,@parmother = CASE WHEN @parmother <> 1 THEN -1 ELSE 1 END
> ,@parmother2 = CASE WHEN @parmother2 <> 1 THEN -1 ELSE 1 END
> ,@parmregaddr = CASE WHEN @parmregaddr <> 1 THEN -1 ELSE 2 END
> ,@t_logic_cdid = 1 /* t logic for partner statements */
>
>
> SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'REDEM')
> ,@add_todate_subs = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'SUBS')
> ,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'TRANSFER_REDEM')
> ,@add_todate_trans_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'TRANSFER_SUBS')
> ,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'ROLLUP_REDEM')
> ,@add_todate_roll_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'ROLLUP_SUBS')
> ,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'SWITCH_REDEM')
> ,@add_todate_switch_s= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'SWITCH_SUBS')
> ,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'EXCHANGE_REDEM')
> ,@add_todate_exch_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> catalogdata
> WHERE cdid = @t_logic_cdid AND cd_type =
> @fund_id AND cd_charcode = 'EXCHANGE_SUBS')
>
> IF NOT EXISTS (SELECT * FROM me_nav WHERE nav_date = @year_date AND
> fund_id
> = @fund_id)
> SELECT @next_year_date = (SELECT MIN(nav_date)
> FROM me_nav
> WHERE fund_id = @fund_id
> AND nav_date <= @stmt_date
> AND posted = 1)
> ELSE
> SELECT @next_year_date = @year_date
>
> SELECT @r_decval = r_decval FROM fund WHERE fund_id = @fund_id
>
> INSERT #investors
> SELECT hid, investor_id, NULL, f.method_id, fl.description
> FROM fundinvestors fi, fund f, flags fl
> WHERE fi.fund_id = @fund_id
> AND f.fund_id = @fund_id
> AND f.ccy = fl.integer_value
> AND fl.flag_name = 'CCY'
> AND fi.hid BETWEEN @from_hid AND @to_hid
>
> INSERT #contacts
> SELECT i.investor_id, c.contact_id, fc.contact_type, c.flagmail,
> c.flagfax,
> c.flagemail, c.flagother, c.flagother2, c.weekly_estimates
> FROM #investors i, fundcontacts fc, contact c
> WHERE fc.fund_id = @fund_id
> AND fc.investor_id = i.investor_id
> AND fc.contact_id = c.contact_id
> AND c.contact_id = fc.contact_id
>
> CREATE TABLE #bal(
> bal_date DATETIME
> ,issue_transaction_id NUMERIC(10)
> ,series_id NUMERIC(10) NULL
> ,investor_id NUMERIC(10) NULL
> ,class_id NUMERIC(10) NULL
> ,sh_adj_ps NUMERIC(16,6) NULL
> ,shares NUMERIC(20,6) NULL
> ,PRIMARY KEY(issue_transaction_id, bal_date)
> )
>
> CREATE INDEX x_bal_2 ON #bal (investor_id, series_id, class_id)
>
> CREATE TABLE #bal_yecalc(
> bal_date DATETIME
> ,issue_transaction_id NUMERIC(10)
> ,min_yecalc NUMERIC(1)
> )
>
> INSERT #bal_yecalc
> SELECT bal_date, issue_transaction_id, MIN(yecalc)
> FROM balances
> WHERE fund_id = @fund_id
> AND bal_date <= @stmt_date
> AND bal_date >= @prev_date
> GROUP BY bal_date, issue_transaction_id
>
> INSERT #bal
> SELECT DISTINCT b.bal_date, b.issue_transaction_id, t.series_id,
> t.investor_id, s.class_id, b.sh_adj_ps, b.shares
> FROM balances b, trans t, series s, #bal_yecalc bb
> WHERE b.fund_id = @fund_id
> AND t.transaction_id = b.issue_transaction_id
> AND b.issue_transaction_id = bb.issue_transaction_id
> AND s.series_id = t.series_id
> AND b.bal_date <= @stmt_date
> AND b.bal_date >= @prev_date
> AND b.bal_date = bb.bal_date
> AND b.yecalc = bb.min_yecalc
> AND t.posted = 1
>
> INSERT #report_data
> SELECT
> DISTINCT
> hid = fi.hid
> ,series_id = me.series_id
> ,class_id = b.class_id
> ,contact_id = c.contact_id
> ,investor_id = fi.investor_id
> ,issue_trans_id = b.issue_transaction_id
> ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> ,cur = CASE WHEN cl.ccy IS NULL THEN fi.cur
> ELSE (SELECT f.description FROM flags f
> WHERE f.flag_name = 'CCY'
> AND f.integer_value = cl.ccy ) END
> ,NULL
> ,method_id = fi.method_id
> ,NULL,NULL, 0
> FROM
> me_nav me
> ,#bal b
> ,#investors fi
> ,#contacts c
> ,classes cl
> WHERE me.fund_id = @fund_id
> AND cl.fund_id = @fund_id
> AND me.series_id = b.series_id
> AND cl.class_id = b.class_id
> AND fi.investor_id = b.investor_id
> AND c.investor_id = fi.investor_id
> AND me.nav_date = b.bal_date
> AND me.seqnum = 1
> AND me.posted = 1
> AND ( c.contact_type = @parmregaddr
> OR c.flagmail = @parmmail
> OR c.flagfax = @parmfax
> OR c.flagemail = @parmemail
> OR c.flagother = @parmother
> -- OR c.flagother2 = @parmother2
> )
> AND c.weekly_estimates >= @weekly_estimates
> ORDER BY fi.hid,me.series_id,c.contact_id
>
> IF @parmother2 = 1
> BEGIN
> INSERT #single_contact
> SELECT hid, MIN(contact_id)
> FROM #report_data
> GROUP BY hid
>
> DELETE #report_data
> FROM #single_contact s
> WHERE #report_data.hid = s.hid
> AND #report_data.contact_id <> s.contact_id
> END
>
>
> INSERT #issue_transaction_id
> SELECT DISTINCT issue_trans_id
> FROM #report_data
>
>
> EXEC subp_get_fund_data @prev_date, @stmt_date,
> @temp_tables_exist=@temp_tables_exist
>
> IF EXISTS ( SELECT 'FOUND' FROM v_additional_info WHERE fund_id =
> @fund_id
> AND short_code = 'FR_ROLL_IGNORE' AND object_type = 100 )
> BEGIN
> UPDATE #fund_data
> SET open_shares = open_shares + rollup_activity
> ,open_shares_val = open_shares_val + rollup_activity_val
>
> DELETE #fund_data WHERE rollup_activity_val < 0
> UPDATE #fund_data SET rollup_activity = 0, rollup_activity_val = 0
> END
>
> UPDATE #fund_data
> SET transfer_activity_val = transfer_activity_val + exch_activity_val
> ,transfer_activity = transfer_activity + exch_activity
>
> UPDATE #fund_data SET exch_activity_val = 0, exch_activity = 0
>
> DELETE #fund_data WHERE open_shares < 0
>
> DELETE #fund_data
> WHERE open_shares = 0
> AND shares = 0
> AND sub_activity = 0
> AND rdp_activity = 0
> AND transfer_activity = 0
> AND rollup_activity = 0
> AND switch_activity = 0
> AND exch_activity = 0
> AND open_shares_val = 0
> AND shares_val = 0
> AND sub_activity_val = 0
> AND rdp_activity_val = 0
> AND transfer_activity_val = 0
> AND rollup_activity_val = 0
> AND switch_activity_val = 0
> AND exch_activity_val = 0
>
> ------------------------------------------------
> ---- FIND NAV INFORMATION FOR YR/PREV DATES ----
> ------------------------------------------------
> CREATE TABLE #date_info
> (
> series_id NUMERIC(10) NULL
> ,yr_min_date DATETIME NULL
> ,yr_nnav NUMERIC(16,6) NULL
> ,yr_gnav NUMERIC(16,6) NULL
> ,prev_min_date DATETIME NULL
> ,prev_nnav NUMERIC(16,6) NULL
> ,prev_gnav NUMERIC(16,6) NULL
> )
>
> CREATE INDEX x_dte ON #date_info( series_id, yr_min_date, prev_min_date )
>
> CREATE TABLE #date_info2
> (
> series_id NUMERIC(10) NULL
> ,yr_min_date DATETIME NULL
> ,yr_nnav NUMERIC(16,6) NULL
> ,yr_gnav NUMERIC(16,6) NULL
> ,prev_min_date DATETIME NULL
> ,prev_nnav NUMERIC(16,6) NULL
> ,prev_gnav NUMERIC(16,6) NULL
> )
>
> CREATE TABLE #seqnum
> (nav_date DATETIME NOT NULL
> ,max_seqnum NUMERIC(1) NULL
> )
>
> SELECT @year_date = (SELECT MIN(nav_date) FROM me_nav
> WHERE fund_id = @fund_id
> AND nav_date >= @year_date
> AND nav_date <= @stmt_date)
> ,@prev_date = (SELECT MIN(nav_date) FROM me_nav
> WHERE fund_id = @fund_id
> AND nav_date >= @prev_date
> AND nav_date <= @stmt_date)
>
> INSERT #seqnum
> SELECT nav_date, MAX(seqnum)
> FROM me_nav
> WHERE fund_id = @fund_id
> AND nav_date >= @year_date
> AND nav_date <= @stmt_date
> GROUP BY nav_date
>
> UPDATE #seqnum SET max_seqnum = 1
> WHERE nav_date = @stmt_date
>
> IF (SELECT method_id FROM fund WHERE fund_id = @fund_id) < 50 --EQ fund
> BEGIN
> DELETE #issue_transaction_id
>
> INSERT #issue_transaction_id
> SELECT DISTINCT issue_trans_id FROM #report_data
>
> EXEC subp_eq_ror_logic @fund_id, @stmt_date, @year_date
>
> UPDATE #date_info
> SET yr_nnav = me.nnav
> ,yr_gnav = me.gnav
> FROM me_nav me, #seqnum s
> WHERE me.fund_id = @fund_id
> AND me.nav_date = #date_info.yr_min_date
> AND s.nav_date = #date_info.yr_min_date
> AND me.seqnum = s.max_seqnum
>
> UPDATE #date_info
> SET prev_min_date = (SELECT MIN(bal_date)
> FROM balances b, trans t,#report_data r
> WHERE b.fund_id = @fund_id
> AND t.fund_id = @fund_id
> AND r.investor_id =
> #date_info.series_id
> AND r.issue_trans_id =
> b.issue_transaction_id
> AND r.issue_trans_id = t.transaction_id
> AND t.investor_id =
> #date_info.series_id
> AND b.bal_date >= @prev_date
> AND b.bal_date <= @stmt_date)
>
> UPDATE #date_info
> SET prev_nnav = me.nnav
> ,prev_gnav = me.gnav
> FROM me_nav me, #seqnum s
> WHERE me.fund_id = @fund_id
> AND me.nav_date = #date_info.prev_min_date
> AND s.nav_date = #date_info.prev_min_date
> AND me.seqnum = s.max_seqnum
>
>
> INSERT #date_info2
> SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
> d1.yr_gnav
> , d2.prev_min_date, d2.prev_nnav,
> d2.prev_gnav
> FROM #date_info d1
> ,#date_info d2
> WHERE d1.series_id = d2.series_id
> AND d1.yr_min_date = (SELECT MIN(yr_min_date)
> FROM #date_info d3
> WHERE d3.series_id = d1.series_id)
> AND d2.prev_min_date =(SELECT MIN(prev_min_date)
> FROM #date_info d3
> WHERE d3.series_id = d1.series_id)
>
> ----------------------------------
> ---- UPDATE MAIN RESULT TABLE ----
> ----------------------------------
> UPDATE #report_data
> SET prev_nnav = d.prev_nnav
> ,yr_nnav = d.yr_nnav
> ,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav ELSE
> d.prev_gnav END
> ,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav ELSE
> d.yr_gnav END
> FROM #date_info2 d
> WHERE d.series_id = #report_data.investor_id
>
> END
>
> ELSE
>
> BEGIN
> INSERT #date_info
> SELECT me.series_id, me.nav_date, MAX(me.nnav), MAX(me.gnav), NULL,
> NULL, NULL
> FROM me_nav me, #seqnum s
> WHERE me.fund_id = @fund_id
> AND me.seqnum = s.max_seqnum
> AND me.nav_date >= @year_date
> AND me.nav_date <= @stmt_date
> AND me.nav_date = s.nav_date
> GROUP BY me.series_id ,me.nav_date
>
> INSERT #date_info
> SELECT me.series_id, NULL, NULL, NULL, me.nav_date, MAX(me.nnav),
> MAX(me.gnav)
> FROM me_nav me, #seqnum s
> WHERE me.fund_id = @fund_id
> AND me.seqnum = s.max_seqnum
> AND me.nav_date >= @prev_date
> AND me.nav_date <= @stmt_date
> AND me.nav_date = s.nav_date
> GROUP BY me.series_id, me.nav_date
>
> INSERT #date_info2
> SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
> d1.yr_gnav
> , d2.prev_min_date, d2.prev_nnav,
> d2.prev_gnav
> FROM #date_info d1
> ,#date_info d2
> WHERE d1.series_id = d2.series_id
> AND d1.yr_min_date = (SELECT MIN(yr_min_date)
> FROM #date_info d3
> WHERE d3.series_id = d1.series_id)
> AND d2.prev_min_date =(SELECT MIN(prev_min_date)
> FROM #date_info d3
> WHERE d3.series_id = d1.series_id)
>
> ----------------------------------
> ---- UPDATE MAIN RESULT TABLE ----
> ----------------------------------
> UPDATE #report_data
> SET prev_nnav = d.prev_nnav
> ,yr_nnav = d.yr_nnav
> ,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav ELSE
> d.prev_gnav END
> ,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav ELSE
> d.yr_gnav END
> FROM #date_info2 d
> WHERE d.series_id = #report_data.series_id
> END
>
> UPDATE #report_data
> SET gnav = me.gnav
> ,nnav = me.nnav
> FROM me_nav me
> WHERE me.nav_date = @stmt_date
> AND me.series_id = #report_data.series_id
> AND me.fund_id = @fund_id
> AND me.seqnum = 1 /* always want monthly nav for current date*/
>
> -- nnav is null if investor totally redeemed out at t+1
> UPDATE #report_data
> SET gnav = me.gnav
> ,nnav = me.nnav
> FROM me_nav me, #fund_data f
> WHERE me.nav_date = (SELECT MAX(bal_date) FROM balances b
> WHERE b.fund_id = @fund_id
> AND b.bal_date < @stmt_date
> AND b.bal_date >= @prev_date
> AND b.issue_transaction_id =
> #report_data.issue_trans_id
> AND b.shares = 0 )
> AND me.series_id = #report_data.series_id
> AND me.fund_id = @fund_id
> AND me.seqnum = 1
> AND #report_data.nnav IS NULL
> AND #report_data.issue_trans_id = f.issue_transaction_id
> AND f.shares_val = 0
>
> UPDATE #report_data
> SET eq_balance = isnull(b.sh_adj_ps, 0) * isnull(b.shares, 0)
> FROM #bal b, #fund_data fd
> WHERE b.issue_transaction_id = fd.issue_transaction_id
> AND b.issue_transaction_id = #report_data.issue_trans_id
> AND b.bal_date = @stmt_date
>
> DROP TABLE #date_info2
>
> UPDATE #report_data
> SET pctmonth = ROUND(((nnav - prev_nnav) / prev_nnav)*100,6)
> ,pctyear = ROUND(((nnav - yr_nnav) / yr_nnav)*100,6)
> ,g_beginbal = ROUND(ISNULL(prev_gnav*f.open_shares,0),2)
> ,g_value = ROUND(ISNULL(gnav*f.shares,0),2)
> ,net_profit = ROUND( ( ISNULL( ((nnav-gnav) * (f.open_shares
> + CASE WHEN
> @add_todate_subs = 1 THEN f.sub_activity ELSE 0 END
> + CASE WHEN
> @add_todate_redem = 1 THEN f.rdp_activity ELSE 0 END
> + CASE WHEN
> (@add_todate_trans_r + @add_todate_trans_s) > 0 THEN f.transfer_activity
> ELSE 0 END
> + CASE WHEN
> (@add_todate_roll_r + @add_todate_roll_s) > 0 THEN f.rollup_activity
> ELSE 0
> END) ) ,0)
> - ISNULL( ((prev_nnav-prev_gnav)
> * (f.open_shares + CASE WHEN
> (@add_todate_trans_r + @add_todate_trans_s) > 0 THEN f.transfer_activity
> ELSE 0 END)),0)
>
> )
> ,2)
> ,transshares = (f.open_shares - f.shares) * -1
> FROM #fund_data f
> WHERE f.issue_transaction_id = #report_data.issue_trans_id
> AND prev_nnav <> 0
> AND yr_nnav <> 0
>
> ----------------------------------------------------------------------------
> -----
> ----------------------------------------------------------------------------
> -----
> IF @use_ror_logic = 1 AND (SELECT method_id FROM fund WHERE fund_id =
> @fund_id) >= 50
> BEGIN
> INSERT #investor_ror
> SELECT DISTINCT investor_id, class_id, series_id, 0, 0, 0, 0
> FROM #report_data
>
> EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date,
> @stmt_date
>
> IF EXISTS (SELECT DISTINCT 'FOUND' FROM #investor_ror)
> UPDATE #report_data
> SET pctmonth = 0
> ,pctyear = 0
> FROM fundinvestors fi
> WHERE fi.fund_id = @fund_id
> AND fi.investor_id = #report_data.investor_id
> AND fi.gp_investor <> 1
> --gp investor has only one series and special logic applies
>
> UPDATE #report_data
> SET pctmonth = i.mtd_ror
> ,pctyear = i.ytd_ror
> FROM #investor_ror i
> WHERE #report_data.investor_id = i.investor_id
> AND #report_data.class_id = i.class_id
> AND #report_data.series_id = i.series_id
> AND #report_data.pctmonth = 0
> AND #report_data.pctyear = 0
> END
> ----------------------------------------------------------------------------
> -----
>
> SELECT @next_prev_date = ISNULL((SELECT MIN(nav_date) FROM me_nav
> WHERE fund_id = @fund_id AND nav_date >
> @prev_date),@stmt_date)
>
> UPDATE #report_data
> SET pctmonth = 0
> , pctyear = CASE WHEN @show_ytd_ror_t1_redem = 1 THEN
> ROUND(((prev_nnav - yr_nnav) / yr_nnav)*100,6) ELSE 0 END
> FROM trans t, redemptions r, #fund_data f, #bal b
> WHERE t.fund_id = @fund_id
> AND f.issue_transaction_id = #report_data.issue_trans_id
> AND r.issue_transaction_id = #report_data.issue_trans_id
> AND b.issue_transaction_id = #report_data.issue_trans_id
> AND b.bal_date = t.transaction_date
> AND b.shares = 0 -- Check for full redemption
> AND t.transaction_id = r.transaction_id
> AND t.transaction_date >= @prev_date
> AND t.transaction_date < @next_prev_date
> AND f.shares = 0
> AND f.shares_val = 0
> AND f.shares_val = ( SELECT SUM(ff.shares_val) FROM
> #fund_data
> ff, trans tt
> WHERE tt.fund_id = @fund_id
> AND tt.transaction_id =
> ff.issue_transaction_id
> AND tt.series_id =
> #report_data.series_id
> AND tt.investor_id =
> #report_data.investor_id)
> ----------------------------------------------------------------------------
> -----
> ----------------------------------------------------------------------------
> -----
>
> DECLARE c_name CURSOR FOR SELECT investor_id FROM #investors
> OPEN c_name
> FETCH c_name INTO @investor_id
> WHILE(@@sqlstatus=0)
> BEGIN
> SELECT @temp_iname = (SELECT name FROM investor WHERE investor_id =
> @investor_id)
> EXEC p_strip_name @temp_iname, @iname output, @first_name_first
>
> UPDATE #investors
> SET iname = @iname
> WHERE investor_id = @investor_id
>
> FETCH c_name INTO @investor_id
> END
> CLOSE c_name
> DEALLOCATE CURSOR c_name
>
> UPDATE #report_data
> SET iname = i.iname
> FROM #investors i
> WHERE i.investor_id = #report_data.investor_id
>
>
> DELETE #report_data
> FROM fund f, trans t
> WHERE f.fund_id = @fund_id
> AND f.report_statement_id = 603
> AND #report_data.issue_trans_id = t.activity_trans_id
> AND t.transaction_type = 2
> AND t.proceeds_entered > 0
>
> -------------------------------------------
> ------------ OUTPUT STATEMENTS ------------
> -------------------------------------------
>
> INSERT #out_result
> SELECT
> DISTINCT
> r.hid
> , r.series_id
> , r.contact_id
> , r.investor_id
> , r.issue_trans_id
> , r.invnum
> , r.g_beginbal
> , r.g_value
> , r.nnav
> , r.gnav
> , r.prev_nnav
> , r.prev_gnav
> , r.yr_nnav
> , r.yr_gnav
> , r.yr_shares
> , r.pctyear
> , r.pctmonth
> , r.net_profit
> , r.cur
> , r.transshares
> , r.method_id
> , r.iname
> , r.series_name
> , f.open_shares
> , f.open_shares_val
> , f.shares
> , f.shares_val
> , f.sub_activity
> , f.sub_activity_val
> , f.rdp_activity
> , f.rdp_activity_val
> , f.transfer_activity
> , f.transfer_activity_val
> , f.rollup_activity
> , f.rollup_activity_val
> , f.switch_activity
> , f.switch_activity_val
> , r.eq_balance
> FROM #report_data r
> ,#fund_data f
> WHERE f.issue_transaction_id = r.issue_trans_id
>
>
> IF @out_nav_info <> 0
> INSERT #nav_info
> SELECT
> series_id = series_id
> ,nav_date = yr_min_date
> ,nnav = yr_nnav
> ,gnav = yr_gnav
> FROM #date_info
> WHERE yr_min_date IS NOT NULL
>
> DROP TABLE #report_data
> DROP TABLE #fund_data
> DROP TABLE #date_info
>
> GO
>
>
> "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
> message news:369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com...
>> Please show us how you created the temporary tables. If you enclosed
>> the table name in doublequotes then you may have created a permanent
>> table that just happens to have a name beginning with #. Look in
>> SYSTABLE to be sure; if it's there, it's not a local temporary table.
>>
>> On 19 Mar 2004 07:28:17 -0800, "Brett Morgan" <bmorgan@imsi.com>
>> wrote:
>>
>> >I've discovered a weird locking problem in a stored procedure we are
> using
>> >that's locking temp tables across connections. I am running Sybase ASA
>> >8.0.3, build 4322.
>> >
>> >The procedure creates some local temp tables, inserts, and updates into
> the
>> >tables then finally selects data from tables. If I run the procedure
>> in
> two
>> >different instances of DBISQL (autocommit off) the second execution
> hangs.
>> >If I commit on the first execution the second execution executes. I
>> ran
>> >sa_conn_info and saw that the first execution was blocking the
>> second. I
>> >couldn't figure out why since all the inserts/updates are done to temp
>> >tables.
>> >
>> >I then turned blocking off using, "set option blocking = off." I again
>> >executed the procedure in two instances of DBISQL. I received an error
>> >message for the second execution saying that a temp table is locked,
>> "ASA
>> >Error -210: User 'DBA' has the row in '#report_data' locked."
>> >
>> >It was my understanding that temp tables are exclusive to a connection,
>> >therefore why would they be locked across two connections? Any help
> would
>> >be much appreciated.
>> >
>> >Brett Morgan
>> >bmorgan@imsi.com
>> >
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book:
> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> Mobile and Distributed Enterprise Database Applications
>> www.risingroad.com
>
>

--
Peter Bumbulis
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Brett Morgan Posted on 2004-03-19 18:51:28.0Z
Reply-To: "Brett Morgan" <bmorgan@imsi.com>
From: "Brett Morgan" <bmorgan@imsi.com>
Newsgroups: ianywhere.public.general
References: <405b1191$1@forums-1-dub> <369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com> <405b2bdc@forums-2-dub> <opr44gldlkgaovig@bumbulis-xp.sybase.com>
Subject: Re: temp table locking
Lines: 1538
Organization: International Fund Services
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: exit.imsi.com
X-Original-NNTP-Posting-Host: exit.imsi.com
Message-ID: <405b4130@forums-1-dub>
Date: 19 Mar 2004 10:51:28 -0800
X-Trace: forums-1-dub 1079722288 199.253.174.9 (19 Mar 2004 10:51:28 -0800)
X-Original-Trace: 19 Mar 2004 10:51:28 -0800, exit.imsi.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2809
Article PK: 6281

I tried that and got a syntax error that made now sense.

"Peter Bumbulis" <bumbulis@ianywhere.com> wrote in message
news:opr44gldlkgaovig@bumbulis-xp.sybase.com...
> Does it make any difference if you change the "create table #t ..." to
> "declare local temporary table #t..."?
>
> Peter
> On 19 Mar 2004 09:20:31 -0800, Brett Morgan <bmorgan@imsi.com> wrote:
>
> > Here is the procs, one main and one sub-proc. It's a beast. The table
> > names are not in double quote and not appearing in SYSTABLE. Thanks
> >
> > CREATE PROC p_part_stmt
> > (
> > @fund_id NUMERIC(10)
> > ,@year_date CHAR(12) = NULL
> > ,@prev_date CHAR(12) = NULL
> > ,@quarter_date CHAR(12) = NULL
> > ,@stmt_date CHAR(12)
> > ,@from_hid NUMERIC(10)
> > ,@to_hid NUMERIC(10)
> > ,@fund_summary NUMERIC(1)
> > ,@summary_funds VARCHAR(50) = NULL
> > ,@fund_to_date_info NUMERIC(1) = 0
> > ,@sort_order NUMERIC(2) = 0
> > ,@signature_info NUMERIC(1) = 0
> > ,@out_nav_info NUMERIC(1) = 1
> > ,@parmemail NUMERIC(1) = 1
> > ,@parmmail NUMERIC(1) = 1
> > ,@parmfax NUMERIC(1) = 1
> > ,@parmother NUMERIC(1) = 0
> > ,@parmother2 NUMERIC(1) = 0
> > ,@parmregaddr NUMERIC(1) = 0
> > ,@first_name_first NUMERIC(1) = 1
> > ,@calc_incep_ror NUMERIC(1) = 0
> > ,@gp_filter NUMERIC(1) = NULL
> > ,@investor_level NUMERIC(1) = 0
> > ,@no_composite_ror NUMERIC(1) = 1
> > ,@export_output NUMERIC(1) = 0
> > ,@income_stmt NUMERIC(1) = 0
> > ,@show_ytd_ror_t1_redem NUMERIC(1) = 0
> > ,@weekly_estimates NUMERIC(1) = 0
> > )
> > AS
> >
> >
> > DECLARE
> > @hid NUMERIC(10),
> > @series_id NUMERIC(10),
> > @contact_id NUMERIC(10),
> > @investor_id NUMERIC(10),
> > @issue_trans_id NUMERIC(10),
> > @nnav NUMERIC(16,6),
> > @gnav NUMERIC(16,6),
> > @cur VARCHAR(10),
> > @method_id NUMERIC(5),
> > @iname VARCHAR(150),
> > @temp_iname VARCHAR(150),
> > @group_by_class NUMERIC(1),
> > @r_decval NUMERIC(1)
> > ,@add_todate_redem NUMERIC(1)
> > ,@add_todate_trans_r NUMERIC(1)
> > ,@add_todate_roll_r NUMERIC(1)
> > ,@add_todate_switch_r NUMERIC(1)
> > ,@add_todate_exch_r NUMERIC(1)
> > ,@msg TEXT
> >
> >
> > CREATE TABLE #out_result(
> > hid NUMERIC(10) NULL
> > ,series_id NUMERIC(10) NULL
> > ,contact_id NUMERIC(10) NULL
> > ,investor_id NUMERIC(10) NULL
> > ,issue_trans_id NUMERIC(10) NULL
> > ,invnum NUMERIC(10) NULL
> > ,g_beginbal NUMERIC(16,6) NULL
> > ,g_value NUMERIC(16,6) NULL
> > ,nnav NUMERIC(16,6) NULL
> > ,gnav NUMERIC(16,6) NULL
> > ,prev_nnav NUMERIC(16,6) NULL
> > ,prev_gnav NUMERIC(16,6) NULL
> > ,yr_nnav NUMERIC(16,6) NULL
> > ,yr_gnav NUMERIC(16,6) NULL
> > ,yr_shares NUMERIC(16,6) NULL
> > ,pctyear NUMERIC(16,6) NULL
> > ,pctmonth NUMERIC(16,6) NULL
> > ,net_profit NUMERIC(16,6) NULL
> > ,cur VARCHAR(10) NULL
> > ,transshares NUMERIC(16,6) NULL
> > ,method_id NUMERIC(5) NULL
> > ,iname VARCHAR(150) NULL
> > ,series_name VARCHAR(25) NULL
> > ,open_shares NUMERIC(16,6) NULL
> > ,open_shares_val NUMERIC(16,6) NULL
> > ,shares NUMERIC(16,6) NULL
> > ,shares_val NUMERIC(16,6) NULL
> > ,sub_activity NUMERIC(16,6) NULL
> > ,sub_activity_val NUMERIC(16,6) NULL
> > ,rdp_activity NUMERIC(16,6) NULL
> > ,rdp_activity_val NUMERIC(16,6) NULL
> > ,transfer_activity NUMERIC(16,6) NULL
> > ,transfer_activity_val NUMERIC(16,6) NULL
> > ,rollup_activity NUMERIC(16,6) NULL
> > ,rollup_activity_val NUMERIC(16,6) NULL
> > ,switch_activity NUMERIC(16,6) NULL
> > ,switch_activity_val NUMERIC(16,6) NULL
> > ,eq_balance NUMERIC(16,6) NULL
> > )
> >
> > CREATE TABLE #nav_info
> > (series_id NUMERIC(10) NULL
> > ,nav_date DATETIME NULL
> > ,nnav NUMERIC(16,6) NULL
> > ,gnav NUMERIC(16,6) NULL
> > )
> >
> > CREATE TABLE #final
> > (hid NUMERIC(10) NULL
> > ,contact_id NUMERIC(10) NULL
> > ,beginbal NUMERIC(16,6) NULL
> > ,g_beginbal NUMERIC(16,6) NULL
> > ,value NUMERIC(16,6) NULL
> > ,g_value NUMERIC(16,6) NULL
> > ,subsrip NUMERIC(16,6) NULL
> > ,redemp NUMERIC(16,6) NULL
> > ,open_shares NUMERIC(16,6) NULL
> > ,shares NUMERIC(16,6) NULL
> > ,transshares NUMERIC(16,6) NULL
> > ,pctincep NUMERIC(16,6) NULL
> > ,pctyear NUMERIC(16,6) NULL
> > ,pctmonth NUMERIC(16,6) NULL
> > ,net_profit NUMERIC(16,6) NULL
> > ,cur VARCHAR(25) NULL
> > ,iname VARCHAR(255) NULL
> > ,contact VARCHAR(255) NULL
> > ,rep VARCHAR(255) NULL
> > ,addr1 VARCHAR(255) NULL
> > ,addr2 VARCHAR(255) NULL
> > ,addr3 VARCHAR(255) NULL
> > ,addr4 VARCHAR(255) NULL
> > ,addr5 VARCHAR(255) NULL
> > ,phone VARCHAR(255) NULL
> > ,fax VARCHAR(255) NULL
> > ,email VARCHAR(255) NULL
> > ,series_name VARCHAR(50) NULL
> > ,nnav NUMERIC(16,6) NULL
> > ,r_decnav NUMERIC(5) NULL
> > ,r_decsh NUMERIC(5) NULL
> > ,class VARCHAR(50) NULL
> > ,identifier2 VARCHAR(255) NULL
> > ,class_id NUMERIC(10) NULL
> > ,investor_id NUMERIC(10) NULL
> > ,series_id NUMERIC(10) NULL
> > ,eq_balance NUMERIC(16,6) NULL
> > ,prev_nnav NUMERIC(16,6) NULL
> > )
> >
> > CREATE TABLE #investor_ror(
> > investor_id NUMERIC(10)
> > ,class_id NUMERIC(10)
> > ,series_id NUMERIC(10)
> > ,mtd_ror NUMERIC(16,6)
> > ,ytd_ror NUMERIC(16,6)
> > ,gr_mtd_ror NUMERIC(16,6)
> > ,gr_ytd_ror NUMERIC(16,6)
> > )
> >
> > IF @to_hid = 99999
> > SELECT @to_hid = 999999
> >
> > IF @year_date IS NULL
> > SELECT @year_date = CONVERT( CHAR(4),(DATEPART(YEAR,@stmt_date)-1) ) +
> > '-12-31'
> >
> > SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
> > SELECT @r_decval = ( SELECT r_decval FROM fund WHERE fund_id =
@fund_id )
> >
> > SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0) FROM
> > catalogdata
> > WHERE cdid = 1 AND cd_type = @fund_id AND
> > cd_charcode = 'REDEM')
> > ,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = 1 AND cd_type = @fund_id AND
> > cd_charcode = 'TRANSFER_REDEM')
> > ,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = 1 AND cd_type = @fund_id AND
> > cd_charcode = 'ROLLUP_REDEM')
> > ,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = 1 AND cd_type = @fund_id AND
> > cd_charcode = 'SWITCH_REDEM')
> > ,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = 1 AND cd_type = @fund_id AND
> > cd_charcode = 'EXCHANGE_REDEM')
> >
> > EXEC subp_part_stmt_main @fund_id, @year_date, @prev_date,
@quarter_date,
> > @stmt_date, @from_hid, @to_hid
> > ,@out_nav_info, @parmemail, @parmmail, @parmfax,
> > @parmother, @parmother2, @parmregaddr, @first_name_first
> > ,@use_ror_logic = @no_composite_ror,
> > @show_ytd_ror_t1_redem = @show_ytd_ror_t1_redem
> > ,@weekly_estimates=@weekly_estimates
> >
> > UPDATE #out_result
> > SET rdp_activity = CASE WHEN transfer_activity < 0 THEN
> > rdp_activity
> > + transfer_activity ELSE rdp_activity END
> > ,rdp_activity_val = CASE WHEN transfer_activity_val < 0 THEN
> > rdp_activity_val + transfer_activity_val ELSE rdp_activity_val END
> > ,sub_activity = CASE WHEN transfer_activity > 0 THEN
> > sub_activity
> > + transfer_activity ELSE sub_activity END
> > ,sub_activity_val = CASE WHEN transfer_activity_val > 0 THEN
> > sub_activity_val + transfer_activity_val ELSE sub_activity_val END
> >
> > UPDATE #out_result
> > SET rdp_activity = CASE WHEN rollup_activity < 0 THEN
> > rdp_activity +
> > rollup_activity ELSE rdp_activity END
> > ,rdp_activity_val = CASE WHEN rollup_activity_val < 0 THEN
> > rdp_activity_val + rollup_activity_val ELSE rdp_activity_val END
> > ,sub_activity = CASE WHEN rollup_activity > 0 THEN
> > sub_activity +
> > rollup_activity ELSE sub_activity END
> > ,sub_activity_val = CASE WHEN rollup_activity_val > 0 THEN
> > sub_activity_val + rollup_activity_val ELSE sub_activity_val END
> >
> > UPDATE #out_result
> > SET rdp_activity = CASE WHEN switch_activity < 0 THEN
> > rdp_activity +
> > switch_activity ELSE rdp_activity END
> > ,rdp_activity_val = CASE WHEN switch_activity_val < 0 THEN
> > rdp_activity_val + switch_activity_val ELSE rdp_activity_val END
> > ,sub_activity = CASE WHEN switch_activity > 0 THEN
> > sub_activity +
> > switch_activity ELSE sub_activity END
> > ,sub_activity_val = CASE WHEN switch_activity_val > 0 THEN
> > sub_activity_val + switch_activity_val ELSE sub_activity_val END
> >
> >
> >
> > IF @no_composite_ror = 0
> > BEGIN
> > INSERT #investor_ror
> > SELECT DISTINCT o.investor_id, s.class_id, o.series_id, 0, 0, 0, 0
> > FROM #out_result o, series s
> > WHERE s.fund_id = @fund_id
> > AND s.series_id = o.series_id
> >
> > EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date,
> > @stmt_date
> > END
> >
> >
/*************************************************************************/
> >
> > IF EXISTS (SELECT * FROM rate_of_return WHERE fund_id = @fund_id AND
> > series_id = 0) AND @no_composite_ror = 1
> > BEGIN
> > SELECT @group_by_class = 1
> >
> > INSERT #final
> > SELECT
> > hid = rd.hid
> > ,contact_id = rd.contact_id
> > ,beginbal = ISNULL(SUM(open_shares_val) ,0)
> > ,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
> > ,value = ISNULL(SUM(shares_val) ,0)
> > ,g_value = ISNULL(SUM(g_value) ,0)
> > ,subsrip = ISNULL(SUM(sub_activity_val),0)
> > ,redemp = ISNULL(SUM(rdp_activity_val),0)
> > ,open_shares= ISNULL(SUM(open_shares) ,0)
> > ,shares = ISNULL(SUM(rd.shares) ,0)
> > ,transshares= SUM(transshares)
> > ,pctincep = 0
> > ,pctyear = MAX(pctyear)
> > ,pctmonth = MAX(pctmonth)
> > ,net_profit = SUM(ISNULL(rd.net_profit,0))
> > ,cur = MAX(cur)
> > ,iname = iname
> > ,contact = MAX(c.contact)
> > ,rep = MAX(c.rep)
> > ,addr1 = MAX(c.addr1)
> > ,addr2 = MAX(c.addr2)
> > ,addr3 = MAX(c.addr3)
> > ,addr4 = MAX(c.addr4)
> > ,addr5 = MAX(c.addr5)
> > ,phone = MAX(c.phone)
> > ,fax = MAX(c.fax)
> > ,email = MAX(c.email)
> > ,series_name= RTRIM(cl.identifier)
> > ,nnav = MAX(rd.nnav)
> > ,r_decnav = MAX(f.r_decnav)
> > ,r_decsh = MAX(f.r_decsh)
> > ,class = cl.identifier
> > ,identifier2= MAX(cl.identifier2)
> > ,class_id = cl.class_id
> > ,investor_id= rd.investor_id
> > ,series_id = NULL
> > ,0
> > ,prev_nnav = MAX(rd.prev_nnav)
> > FROM
> > contact c
> > ,#out_result rd
> > ,series se
> > ,classes cl
> > ,fund f
> > WHERE c.contact_id = rd.contact_id
> > AND se.series_id = rd.series_id
> > AND se.fund_id = @fund_id
> > AND cl.fund_id = se.fund_id
> > AND f.fund_id = se.fund_id
> > AND cl.class_id = se.class_id
> > GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
> > cl.class_id, rd.iname
> > END
> >
> > ELSE
> >
> > BEGIN
> > SELECT @group_by_class = 0
> >
> > INSERT #final
> > SELECT
> > hid = rd.hid
> > ,contact_id = rd.contact_id
> > ,beginbal = ISNULL(SUM(open_shares_val) ,0)
> > ,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
> > ,value = ISNULL(SUM(shares_val) ,0)
> > ,g_value = ISNULL(SUM(g_value) ,0)
> > ,subsrip = ISNULL(SUM(sub_activity_val),0)
> > ,redemp = ISNULL(SUM(rdp_activity_val),0)
> > ,open_shares= ISNULL(SUM(open_shares) ,0)
> > ,shares = ISNULL(SUM(rd.shares) ,0)
> > ,transshares= SUM(transshares)
> > ,pctincep = 0
> > ,pctyear = MAX(pctyear)
> > ,pctmonth = MAX(pctmonth)
> > ,net_profit = SUM(ISNULL(rd.net_profit,0))
> > ,cur = MAX(cur)
> > ,iname = iname
> > ,contact = MAX(c.contact)
> > ,rep = MAX(c.rep)
> > ,addr1 = MAX(c.addr1)
> > ,addr2 = MAX(c.addr2)
> > ,addr3 = MAX(c.addr3)
> > ,addr4 = MAX(c.addr4)
> > ,addr5 = MAX(c.addr5)
> > ,phone = MAX(c.phone)
> > ,fax = MAX(c.fax)
> > ,email = MAX(c.email)
> > ,series_name= RTRIM(cl.identifier) + '_' +RTRIM(se.identifier)
> > ,nnav = MAX(rd.nnav)
> > ,r_decnav = MAX(f.r_decnav)
> > ,r_decsh = MAX(f.r_decsh)
> > ,class = cl.identifier
> > ,identifier2= MAX(cl.identifier2)
> > ,class_id = cl.class_id
> > ,investor_id= rd.investor_id
> > ,series_id = rd.series_id
> > ,0
> > ,prev_nnav = MAX(rd.prev_nnav)
> > FROM
> > contact c
> > ,#out_result rd
> > ,series se
> > ,classes cl
> > ,fund f
> > WHERE c.contact_id = rd.contact_id
> > AND se.series_id = rd.series_id
> > AND se.fund_id = @fund_id
> > AND cl.fund_id = se.fund_id
> > AND f.fund_id = se.fund_id
> > AND cl.class_id = se.class_id
> > GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
> > cl.class_id, rd.series_id, se.identifier, rd.iname
> >
> > END
>
> --------------------------------------------------------------------------
--
> > ----------
> >
> > UPDATE #final f
> > SET transshares = ( SELECT SUM(redemp) FROM #final ff
> > WHERE f.hid = ff.hid
> > AND f.contact_id = ff.contact_id
> > )
> > UPDATE #final f
> > SET transshares = transshares + ( SELECT SUM(subsrip) FROM #final ff
> > WHERE f.hid = ff.hid
> > AND f.contact_id = ff.contact_id
> > )
> > UPDATE #final f
> > SET g_beginbal = ( SELECT SUM(ROUND(beginbal,@r_decval)) FROM #final
> > ff
> > WHERE f.hid = ff.hid
> > AND f.contact_id = ff.contact_id
> > )
> >
> > UPDATE #final f
> > SET g_value = ( SELECT SUM(ROUND(value,@r_decval)) FROM #final ff
> > WHERE f.hid = ff.hid
> > AND f.contact_id = ff.contact_id
> > )
> >
/*************************************************************************/
> >
> > IF NOT EXISTS ( SELECT 'FOUND' FROM fund_addl_info f, additional_info a
> > WHERE a.info_id = f.info_id AND a.short_code =
> > 'FR_PARTNERSHIP'
> > AND f.fund_id = @fund_id )
> > DELETE #final
> > FROM trans t, redemptions r
> > WHERE t.fund_id = @fund_id
> > AND t.transaction_date >= @prev_date -- date range needed for
> > quarterly
> > stmts
> > AND t.transaction_date < @stmt_date
> > AND r.series_id = #final.series_id
> > AND t.transaction_id = r.transaction_id
> > AND #final.shares = 0
> > AND t.investor_id = #final.investor_id
> > AND ( (@add_todate_redem=1 AND t.activity_type=0 AND
> > transaction_type=2)
> > OR (@add_todate_trans_r=1 AND t.activity_type=1 AND
> > transaction_type=2)
> > OR (@add_todate_roll_r=1 AND t.activity_type=3 AND
> > transaction_type=2)
> > OR (@add_todate_switch_r=1 AND t.switch_transaction=1 AND
> > transaction_type=2)
> > OR (@add_todate_exch_r=1 AND t.activity_type=4 AND
> > transaction_type=2)
> > )
> >
> > IF @investor_level = 1
> > BEGIN
> > INSERT #final
> > SELECT DISTINCT
> > hid ,contact_id=0 ,beginbal ,g_beginbal
> > ,value ,g_value ,subsrip ,redemp
> > ,open_shares ,shares ,transshares ,pctincep
> > ,pctyear ,pctmonth ,net_profit ,cur
> > ,iname ,'','','','','','','','','',''
> > ,series_name ,nnav ,r_decnav ,r_decsh
> > ,class ,identifier2 ,class_id ,investor_id
> > ,series_id ,eq_balance ,prev_nnav
> > FROM #final
> >
> > DELETE #final WHERE contact_id <> 0
> > END
>
> --------------------------------------------------------------------------
--
> > ----------
> >
> >
> > IF @calc_incep_ror = 1
> > EXEC subp_ps_incep_ror @fund_id, @stmt_date, @group_by_class
> >
> >
> > SELECT @msg = ( SELECT text_val FROM fund_addl_info f, additional_info a
> > WHERE f.info_id = a.info_id and UPPER(short_code) =
> > 'COMMENT'
> > AND f.fund_id = @fund_id
> > AND f.object_id = @fund_id)
> >
> >
>
> --------------------------------------------------------------------------
--
> > ----------
> >
> >
> >
/***************************************************************************
> > /
> > /********************* Check for hardcoded ror values
> > ******************/
> >
/***************************************************************************
> > /
> >
> > UPDATE #final
> > SET pctmonth = r.net_mtd
> > ,pctyear = r.net_ytd
> > ,pctincep = r.net_itd
> > FROM ror_hardcode r, fundinvestors fi
> > WHERE r.fund_id = @fund_id
> > AND fi.fund_id = @fund_id
> > AND fi.hid = #final.hid
> > AND fi.investor_id = r.investor_id
> > AND r.series_id = #final.series_id
> >
> >
> >
/***************************************************************************
> > /
> >
/***************************************************************************
> > /
> > --------------------------------------------------
> > ------------ OUTPUT SELECT STATEMENTS ------------
> > --------------------------------------------------
> > IF @export_output = 0
> > BEGIN
> > IF @fund_summary=0
> > EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
@prev_date,
> > @stmt_date, @fund_to_date_info, @signature_info=@signature_info,
> > @income_stmt=@income_stmt
> >
> > ELSE IF @fund_summary=1
> > BEGIN
> > SELECT @summary_funds = (SELECT shortname FROM fund WHERE
> > fund_id =
> > @fund_id)
> > EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
> > @prev_date,
> > @stmt_date, @fund_to_date_info, @summary_funds,
> > @signature_info=@signature_info, @gp_filter=@gp_filter,
> > @income_stmt=@income_stmt
> > END
> >
> > ELSE IF @fund_summary=2
> > EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
@prev_date,
> > @stmt_date, @fund_to_date_info, @summary_funds,
> > @signature_info=@signature_info, @gp_filter=@gp_filter,
> > @income_stmt=@income_stmt
> > END
> >
>
> --------------------------------------------------------------------------
--
> > ----------
> > --------------------------- OUTPUT SELECT
> > ORTS ------------------------------------
>
> --------------------------------------------------------------------------
--
> > ----------
> >
> > IF @no_composite_ror = 0 AND @export_output = 0
> > SELECT DISTINCT hid ,contact_id ,beginbal ,g_beginbal
> > ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > ,i.mtd_ror ,i.ytd_ror ,prev_nnav
> > FROM #final f, #investor_ror i
> > WHERE f.investor_id = i.investor_id
> > AND f.class_id = i.class_id
> > AND i.series_id = ISNULL(f.series_id,i.series_id)
> > ORDER BY hid, contact_id, iname, cur, series_name
> >
> > ELSE IF @sort_order = 0 AND @export_output = 0
> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > ,eq_balance ,0,0,@msg
> > FROM #final
> > ORDER BY series_name, iname, hid, contact_id, cur
> >
> > ELSE IF @sort_order = 1 AND @export_output = 0
> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > ,0,0,0,@msg
> > FROM #final
> > ORDER BY hid, contact_id, iname, cur, series_name
> >
> > ELSE IF @sort_order = 2 AND @export_output = 0
> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > FROM #final
> > ORDER BY class, hid, contact_id, iname, cur, series_name
> >
> > ELSE IF @sort_order = 3 AND @export_output = 0 -- Wellington statement
> > needs cl.identifier2
> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > ,0,0,0,identifier2
> > FROM #final
> > ORDER BY hid, contact_id, iname, cur, series_name
> >
> > ELSE IF @sort_order = 4 AND @export_output = 0
> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > FROM #final
> > ORDER BY hid, contact_id, iname, cur, series_name
> > ELSE IF @export_output = 0
> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
> > ,g_value ,subsrip ,redemp ,open_shares ,shares
> > ,transshares ,pctincep ,pctyear ,pctmonth ,net_profit
> > ,cur
> > ,iname ,contact ,rep ,addr1 ,addr2
> > ,addr3 ,addr4 ,addr5 ,phone ,fax
> > ,email ,series_name ,nnav ,r_decnav ,r_decsh
> > FROM #final
> > ORDER BY hid, contact_id, iname, cur, series_name
> >
> > ELSE IF @export_output = 1 AND @no_composite_ror = 1
> > INSERT #output
> > SELECT *,0,0 FROM #final
> > ORDER BY iname, hid, series_name
> >
> > ELSE IF @export_output = 1 AND @no_composite_ror = 0
> > INSERT #output
> > SELECT f.*,i.mtd_ror,i.ytd_ror FROM #final f, #investor_ror i
> > WHERE f.investor_id = i.investor_id
> > AND f.class_id = i.class_id
> > AND i.series_id = ISNULL(f.series_id,i.series_id)
> > ORDER BY iname, hid, series_name
> >
> > ELSE IF @export_output = 2
> > SELECT hid ,contact_id ,beginbal ,value ,subsrip
> > ,redemp ,open_shares ,shares ,pctincep ,pctyear
> > ,pctmonth ,net_profit ,series_name, nnav
> > FROM #final
> > ORDER BY hid, contact_id, series_name
> >
> > DROP TABLE #nav_info
> > DROP TABLE #out_result
> > DROP TABLE #final
> > DROP TABLE #investor_ror
> > GO
> >
> >
> > /*********************
> > * Second proc *
> > *********************/
> > CREATE PROC subp_part_stmt_main
> > (
> > @fund_id NUMERIC(10)
> > ,@year_date CHAR(12)
> > ,@prev_date CHAR(12) = NULL
> > ,@quarter_date CHAR(12) = NULL
> > ,@stmt_date CHAR(12)
> > ,@from_hid NUMERIC(10)
> > ,@to_hid NUMERIC(10)
> > ,@out_nav_info NUMERIC(1) = 1
> > ,@parmemail NUMERIC(1) = 1
> > ,@parmmail NUMERIC(1) = 1
> > ,@parmfax NUMERIC(1) = 1
> > ,@parmother NUMERIC(1) = 0
> > ,@parmother2 NUMERIC(1) = 0
> > ,@parmregaddr NUMERIC(1) = 0
> > ,@first_name_first NUMERIC(1) = 1
> > ,@temp_tables_exist NUMERIC(1) = 0
> > ,@use_ror_logic NUMERIC(1) = 1
> > ,@show_ytd_ror_t1_redem NUMERIC(1) = 0
> > ,@weekly_estimates NUMERIC(1) = 0
> > )
> > AS
> >
> >
> > DECLARE
> > @investor_id NUMERIC(10),
> > @iname VARCHAR(150),
> > @temp_iname VARCHAR(150),
> > @next_prev_date DATETIME
> > ,@next_year_date DATETIME
> > ,@add_todate_redem NUMERIC(1)
> > ,@add_todate_subs NUMERIC(1)
> > ,@add_todate_trans_r NUMERIC(1)
> > ,@add_todate_trans_s NUMERIC(1)
> > ,@add_todate_roll_r NUMERIC(1)
> > ,@add_todate_roll_s NUMERIC(1)
> > ,@add_todate_switch_r NUMERIC(1)
> > ,@add_todate_switch_s NUMERIC(1)
> > ,@add_todate_exch_r NUMERIC(1)
> > ,@add_todate_exch_s NUMERIC(1)
> > ,@t_logic_cdid NUMERIC(2)
> > ,@r_decval NUMERIC(1)
> >
> > CREATE TABLE #report_data(
> > hid NUMERIC(10) NULL,
> > series_id NUMERIC(10) NULL,
> > class_id NUMERIC(10) NULL,
> > contact_id NUMERIC(10) NULL,
> > investor_id NUMERIC(10) NULL,
> > issue_trans_id NUMERIC(10) NULL,
> > invnum NUMERIC(10) NULL,
> > g_beginbal NUMERIC(16,6) NULL,
> > g_value NUMERIC(16,6) NULL,
> > nnav NUMERIC(16,6) NULL,
> > gnav NUMERIC(16,6) NULL,
> > prev_nnav NUMERIC(16,6) NULL,
> > prev_gnav NUMERIC(16,6) NULL,
> > yr_nnav NUMERIC(16,6) NULL,
> > yr_gnav NUMERIC(16,6) NULL,
> > yr_shares NUMERIC(16,6) NULL,
> > pctyear NUMERIC(16,6) NULL,
> > pctmonth NUMERIC(16,6) NULL,
> > net_profit NUMERIC(16,6) NULL,
> > cur VARCHAR(10) NULL,
> > transshares NUMERIC(16,6) NULL,
> > method_id NUMERIC(5) NULL,
> > iname VARCHAR(150) NULL,
> > series_name VARCHAR(25) NULL,
> > eq_balance NUMERIC(16,6) NULL,
> > primary key (investor_id,series_id,issue_trans_id,contact_id)
> > )
> >
> > CREATE TABLE #single_contact(
> > hid NUMERIC(10) NULL,
> > contact_id NUMERIC(10) NULL,
> > )
> >
> > CREATE TABLE #issue_transaction_id(
> > issue_transaction_id NUMERIC(10)
> > )
> >
> > CREATE TABLE #fund_data(
> > issue_transaction_id NUMERIC(10)
> > ,open_shares NUMERIC(16,6) NULL
> > ,open_shares_val NUMERIC(16,6) NULL
> > ,shares NUMERIC(16,6) NULL
> > ,shares_val NUMERIC(16,6) NULL
> > ,sub_activity NUMERIC(16,6) NULL
> > ,sub_activity_val NUMERIC(16,6) NULL
> > ,rdp_activity NUMERIC(16,6) NULL
> > ,rdp_activity_val NUMERIC(16,6) NULL
> > ,transfer_activity NUMERIC(16,6) NULL
> > ,transfer_activity_val NUMERIC(16,6) NULL
> > ,rollup_activity NUMERIC(16,6) NULL
> > ,rollup_activity_val NUMERIC(16,6) NULL
> > ,switch_activity NUMERIC(16,6) NULL
> > ,switch_activity_val NUMERIC(16,6) NULL
> > ,exch_activity NUMERIC(16,6) NULL
> > ,exch_activity_val NUMERIC(16,6) NULL
> > )
> >
> > CREATE TABLE #investors(
> > hid NUMERIC(10) NULL
> > ,investor_id NUMERIC(10)
> > ,iname VARCHAR(255) NULL
> > ,method_id NUMERIC(5) NULL
> > ,cur VARCHAR(50) NULL
> > ,PRIMARY KEY(investor_id)
> > )
> >
> > CREATE TABLE #investor_ror(
> > investor_id NUMERIC(10)
> > ,class_id NUMERIC(10)
> > ,series_id NUMERIC(10)
> > ,mtd_ror NUMERIC(16,6)
> > ,ytd_ror NUMERIC(16,6)
> > ,gr_mtd_ror NUMERIC(16,6)
> > ,gr_ytd_ror NUMERIC(16,6)
> > )
> >
> > CREATE INDEX x_ir ON #investor_ror ( investor_id, class_id, series_id )
> >
> > CREATE TABLE #contacts(
> > investor_id NUMERIC(10)
> > ,contact_id NUMERIC(10)
> > ,contact_type NUMERIC(5)
> > ,flagmail NUMERIC(5)
> > ,flagfax NUMERIC(5)
> > ,flagemail NUMERIC(5)
> > ,flagother NUMERIC(5)
> > ,flagother2 NUMERIC(5)
> > ,weekly_estimates NUMERIC(1)
> > ,PRIMARY KEY (investor_id, contact_id)
> > )
> >
> > IF @out_nav_info <> 1
> > CREATE TABLE #nav_info
> > (series_id NUMERIC(10) NULL
> > ,nav_date DATETIME NULL
> > ,nnav NUMERIC(16,6) NULL
> > ,gnav NUMERIC(16,6) NULL
> > )
> >
> >
> > SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
> > ,@parmemail = CASE WHEN @parmemail <> 1 THEN -1 ELSE 1 END
> > ,@parmmail = CASE WHEN @parmmail <> 1 THEN -1 ELSE 1 END
> > ,@parmfax = CASE WHEN @parmfax <> 1 THEN -1 ELSE 1 END
> > ,@parmother = CASE WHEN @parmother <> 1 THEN -1 ELSE 1 END
> > ,@parmother2 = CASE WHEN @parmother2 <> 1 THEN -1 ELSE 1 END
> > ,@parmregaddr = CASE WHEN @parmregaddr <> 1 THEN -1 ELSE 2 END
> > ,@t_logic_cdid = 1 /* t logic for partner statements */
> >
> >
> > SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'REDEM')
> > ,@add_todate_subs = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'SUBS')
> > ,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'TRANSFER_REDEM')
> > ,@add_todate_trans_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'TRANSFER_SUBS')
> > ,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'ROLLUP_REDEM')
> > ,@add_todate_roll_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'ROLLUP_SUBS')
> > ,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'SWITCH_REDEM')
> > ,@add_todate_switch_s= (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'SWITCH_SUBS')
> > ,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'EXCHANGE_REDEM')
> > ,@add_todate_exch_s = (SELECT ISNULL(MAX(cd_numvalue),1) FROM
> > catalogdata
> > WHERE cdid = @t_logic_cdid AND cd_type =
> > @fund_id AND cd_charcode = 'EXCHANGE_SUBS')
> >
> > IF NOT EXISTS (SELECT * FROM me_nav WHERE nav_date = @year_date AND
> > fund_id
> > = @fund_id)
> > SELECT @next_year_date = (SELECT MIN(nav_date)
> > FROM me_nav
> > WHERE fund_id = @fund_id
> > AND nav_date <= @stmt_date
> > AND posted = 1)
> > ELSE
> > SELECT @next_year_date = @year_date
> >
> > SELECT @r_decval = r_decval FROM fund WHERE fund_id = @fund_id
> >
> > INSERT #investors
> > SELECT hid, investor_id, NULL, f.method_id, fl.description
> > FROM fundinvestors fi, fund f, flags fl
> > WHERE fi.fund_id = @fund_id
> > AND f.fund_id = @fund_id
> > AND f.ccy = fl.integer_value
> > AND fl.flag_name = 'CCY'
> > AND fi.hid BETWEEN @from_hid AND @to_hid
> >
> > INSERT #contacts
> > SELECT i.investor_id, c.contact_id, fc.contact_type, c.flagmail,
> > c.flagfax,
> > c.flagemail, c.flagother, c.flagother2, c.weekly_estimates
> > FROM #investors i, fundcontacts fc, contact c
> > WHERE fc.fund_id = @fund_id
> > AND fc.investor_id = i.investor_id
> > AND fc.contact_id = c.contact_id
> > AND c.contact_id = fc.contact_id
> >
> > CREATE TABLE #bal(
> > bal_date DATETIME
> > ,issue_transaction_id NUMERIC(10)
> > ,series_id NUMERIC(10) NULL
> > ,investor_id NUMERIC(10) NULL
> > ,class_id NUMERIC(10) NULL
> > ,sh_adj_ps NUMERIC(16,6) NULL
> > ,shares NUMERIC(20,6) NULL
> > ,PRIMARY KEY(issue_transaction_id, bal_date)
> > )
> >
> > CREATE INDEX x_bal_2 ON #bal (investor_id, series_id, class_id)
> >
> > CREATE TABLE #bal_yecalc(
> > bal_date DATETIME
> > ,issue_transaction_id NUMERIC(10)
> > ,min_yecalc NUMERIC(1)
> > )
> >
> > INSERT #bal_yecalc
> > SELECT bal_date, issue_transaction_id, MIN(yecalc)
> > FROM balances
> > WHERE fund_id = @fund_id
> > AND bal_date <= @stmt_date
> > AND bal_date >= @prev_date
> > GROUP BY bal_date, issue_transaction_id
> >
> > INSERT #bal
> > SELECT DISTINCT b.bal_date, b.issue_transaction_id, t.series_id,
> > t.investor_id, s.class_id, b.sh_adj_ps, b.shares
> > FROM balances b, trans t, series s, #bal_yecalc bb
> > WHERE b.fund_id = @fund_id
> > AND t.transaction_id = b.issue_transaction_id
> > AND b.issue_transaction_id = bb.issue_transaction_id
> > AND s.series_id = t.series_id
> > AND b.bal_date <= @stmt_date
> > AND b.bal_date >= @prev_date
> > AND b.bal_date = bb.bal_date
> > AND b.yecalc = bb.min_yecalc
> > AND t.posted = 1
> >
> > INSERT #report_data
> > SELECT
> > DISTINCT
> > hid = fi.hid
> > ,series_id = me.series_id
> > ,class_id = b.class_id
> > ,contact_id = c.contact_id
> > ,investor_id = fi.investor_id
> > ,issue_trans_id = b.issue_transaction_id
> > ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> > ,cur = CASE WHEN cl.ccy IS NULL THEN fi.cur
> > ELSE (SELECT f.description FROM flags f
> > WHERE f.flag_name = 'CCY'
> > AND f.integer_value = cl.ccy ) END
> > ,NULL
> > ,method_id = fi.method_id
> > ,NULL,NULL, 0
> > FROM
> > me_nav me
> > ,#bal b
> > ,#investors fi
> > ,#contacts c
> > ,classes cl
> > WHERE me.fund_id = @fund_id
> > AND cl.fund_id = @fund_id
> > AND me.series_id = b.series_id
> > AND cl.class_id = b.class_id
> > AND fi.investor_id = b.investor_id
> > AND c.investor_id = fi.investor_id
> > AND me.nav_date = b.bal_date
> > AND me.seqnum = 1
> > AND me.posted = 1
> > AND ( c.contact_type = @parmregaddr
> > OR c.flagmail = @parmmail
> > OR c.flagfax = @parmfax
> > OR c.flagemail = @parmemail
> > OR c.flagother = @parmother
> > -- OR c.flagother2 = @parmother2
> > )
> > AND c.weekly_estimates >= @weekly_estimates
> > ORDER BY fi.hid,me.series_id,c.contact_id
> >
> > IF @parmother2 = 1
> > BEGIN
> > INSERT #single_contact
> > SELECT hid, MIN(contact_id)
> > FROM #report_data
> > GROUP BY hid
> >
> > DELETE #report_data
> > FROM #single_contact s
> > WHERE #report_data.hid = s.hid
> > AND #report_data.contact_id <> s.contact_id
> > END
> >
> >
> > INSERT #issue_transaction_id
> > SELECT DISTINCT issue_trans_id
> > FROM #report_data
> >
> >
> > EXEC subp_get_fund_data @prev_date, @stmt_date,
> > @temp_tables_exist=@temp_tables_exist
> >
> > IF EXISTS ( SELECT 'FOUND' FROM v_additional_info WHERE fund_id =
> > @fund_id
> > AND short_code = 'FR_ROLL_IGNORE' AND object_type = 100 )
> > BEGIN
> > UPDATE #fund_data
> > SET open_shares = open_shares + rollup_activity
> > ,open_shares_val = open_shares_val + rollup_activity_val
> >
> > DELETE #fund_data WHERE rollup_activity_val < 0
> > UPDATE #fund_data SET rollup_activity = 0, rollup_activity_val = 0
> > END
> >
> > UPDATE #fund_data
> > SET transfer_activity_val = transfer_activity_val + exch_activity_val
> > ,transfer_activity = transfer_activity + exch_activity
> >
> > UPDATE #fund_data SET exch_activity_val = 0, exch_activity = 0
> >
> > DELETE #fund_data WHERE open_shares < 0
> >
> > DELETE #fund_data
> > WHERE open_shares = 0
> > AND shares = 0
> > AND sub_activity = 0
> > AND rdp_activity = 0
> > AND transfer_activity = 0
> > AND rollup_activity = 0
> > AND switch_activity = 0
> > AND exch_activity = 0
> > AND open_shares_val = 0
> > AND shares_val = 0
> > AND sub_activity_val = 0
> > AND rdp_activity_val = 0
> > AND transfer_activity_val = 0
> > AND rollup_activity_val = 0
> > AND switch_activity_val = 0
> > AND exch_activity_val = 0
> >
> > ------------------------------------------------
> > ---- FIND NAV INFORMATION FOR YR/PREV DATES ----
> > ------------------------------------------------
> > CREATE TABLE #date_info
> > (
> > series_id NUMERIC(10) NULL
> > ,yr_min_date DATETIME NULL
> > ,yr_nnav NUMERIC(16,6) NULL
> > ,yr_gnav NUMERIC(16,6) NULL
> > ,prev_min_date DATETIME NULL
> > ,prev_nnav NUMERIC(16,6) NULL
> > ,prev_gnav NUMERIC(16,6) NULL
> > )
> >
> > CREATE INDEX x_dte ON #date_info( series_id, yr_min_date,
prev_min_date )
> >
> > CREATE TABLE #date_info2
> > (
> > series_id NUMERIC(10) NULL
> > ,yr_min_date DATETIME NULL
> > ,yr_nnav NUMERIC(16,6) NULL
> > ,yr_gnav NUMERIC(16,6) NULL
> > ,prev_min_date DATETIME NULL
> > ,prev_nnav NUMERIC(16,6) NULL
> > ,prev_gnav NUMERIC(16,6) NULL
> > )
> >
> > CREATE TABLE #seqnum
> > (nav_date DATETIME NOT NULL
> > ,max_seqnum NUMERIC(1) NULL
> > )
> >
> > SELECT @year_date = (SELECT MIN(nav_date) FROM me_nav
> > WHERE fund_id = @fund_id
> > AND nav_date >= @year_date
> > AND nav_date <= @stmt_date)
> > ,@prev_date = (SELECT MIN(nav_date) FROM me_nav
> > WHERE fund_id = @fund_id
> > AND nav_date >= @prev_date
> > AND nav_date <= @stmt_date)
> >
> > INSERT #seqnum
> > SELECT nav_date, MAX(seqnum)
> > FROM me_nav
> > WHERE fund_id = @fund_id
> > AND nav_date >= @year_date
> > AND nav_date <= @stmt_date
> > GROUP BY nav_date
> >
> > UPDATE #seqnum SET max_seqnum = 1
> > WHERE nav_date = @stmt_date
> >
> > IF (SELECT method_id FROM fund WHERE fund_id = @fund_id) < 50 --EQ fund
> > BEGIN
> > DELETE #issue_transaction_id
> >
> > INSERT #issue_transaction_id
> > SELECT DISTINCT issue_trans_id FROM #report_data
> >
> > EXEC subp_eq_ror_logic @fund_id, @stmt_date, @year_date
> >
> > UPDATE #date_info
> > SET yr_nnav = me.nnav
> > ,yr_gnav = me.gnav
> > FROM me_nav me, #seqnum s
> > WHERE me.fund_id = @fund_id
> > AND me.nav_date = #date_info.yr_min_date
> > AND s.nav_date = #date_info.yr_min_date
> > AND me.seqnum = s.max_seqnum
> >
> > UPDATE #date_info
> > SET prev_min_date = (SELECT MIN(bal_date)
> > FROM balances b, trans t,#report_data r
> > WHERE b.fund_id = @fund_id
> > AND t.fund_id = @fund_id
> > AND r.investor_id =
> > #date_info.series_id
> > AND r.issue_trans_id =
> > b.issue_transaction_id
> > AND r.issue_trans_id = t.transaction_id
> > AND t.investor_id =
> > #date_info.series_id
> > AND b.bal_date >= @prev_date
> > AND b.bal_date <= @stmt_date)
> >
> > UPDATE #date_info
> > SET prev_nnav = me.nnav
> > ,prev_gnav = me.gnav
> > FROM me_nav me, #seqnum s
> > WHERE me.fund_id = @fund_id
> > AND me.nav_date = #date_info.prev_min_date
> > AND s.nav_date = #date_info.prev_min_date
> > AND me.seqnum = s.max_seqnum
> >
> >
> > INSERT #date_info2
> > SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
> > d1.yr_gnav
> > , d2.prev_min_date, d2.prev_nnav,
> > d2.prev_gnav
> > FROM #date_info d1
> > ,#date_info d2
> > WHERE d1.series_id = d2.series_id
> > AND d1.yr_min_date = (SELECT MIN(yr_min_date)
> > FROM #date_info d3
> > WHERE d3.series_id = d1.series_id)
> > AND d2.prev_min_date =(SELECT MIN(prev_min_date)
> > FROM #date_info d3
> > WHERE d3.series_id = d1.series_id)
> >
> > ----------------------------------
> > ---- UPDATE MAIN RESULT TABLE ----
> > ----------------------------------
> > UPDATE #report_data
> > SET prev_nnav = d.prev_nnav
> > ,yr_nnav = d.yr_nnav
> > ,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav ELSE
> > d.prev_gnav END
> > ,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav ELSE
> > d.yr_gnav END
> > FROM #date_info2 d
> > WHERE d.series_id = #report_data.investor_id
> >
> > END
> >
> > ELSE
> >
> > BEGIN
> > INSERT #date_info
> > SELECT me.series_id, me.nav_date, MAX(me.nnav), MAX(me.gnav), NULL,
> > NULL, NULL
> > FROM me_nav me, #seqnum s
> > WHERE me.fund_id = @fund_id
> > AND me.seqnum = s.max_seqnum
> > AND me.nav_date >= @year_date
> > AND me.nav_date <= @stmt_date
> > AND me.nav_date = s.nav_date
> > GROUP BY me.series_id ,me.nav_date
> >
> > INSERT #date_info
> > SELECT me.series_id, NULL, NULL, NULL, me.nav_date, MAX(me.nnav),
> > MAX(me.gnav)
> > FROM me_nav me, #seqnum s
> > WHERE me.fund_id = @fund_id
> > AND me.seqnum = s.max_seqnum
> > AND me.nav_date >= @prev_date
> > AND me.nav_date <= @stmt_date
> > AND me.nav_date = s.nav_date
> > GROUP BY me.series_id, me.nav_date
> >
> > INSERT #date_info2
> > SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
> > d1.yr_gnav
> > , d2.prev_min_date, d2.prev_nnav,
> > d2.prev_gnav
> > FROM #date_info d1
> > ,#date_info d2
> > WHERE d1.series_id = d2.series_id
> > AND d1.yr_min_date = (SELECT MIN(yr_min_date)
> > FROM #date_info d3
> > WHERE d3.series_id = d1.series_id)
> > AND d2.prev_min_date =(SELECT MIN(prev_min_date)
> > FROM #date_info d3
> > WHERE d3.series_id = d1.series_id)
> >
> > ----------------------------------
> > ---- UPDATE MAIN RESULT TABLE ----
> > ----------------------------------
> > UPDATE #report_data
> > SET prev_nnav = d.prev_nnav
> > ,yr_nnav = d.yr_nnav
> > ,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav ELSE
> > d.prev_gnav END
> > ,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav ELSE
> > d.yr_gnav END
> > FROM #date_info2 d
> > WHERE d.series_id = #report_data.series_id
> > END
> >
> > UPDATE #report_data
> > SET gnav = me.gnav
> > ,nnav = me.nnav
> > FROM me_nav me
> > WHERE me.nav_date = @stmt_date
> > AND me.series_id = #report_data.series_id
> > AND me.fund_id = @fund_id
> > AND me.seqnum = 1 /* always want monthly nav for current date*/
> >
> > -- nnav is null if investor totally redeemed out at t+1
> > UPDATE #report_data
> > SET gnav = me.gnav
> > ,nnav = me.nnav
> > FROM me_nav me, #fund_data f
> > WHERE me.nav_date = (SELECT MAX(bal_date) FROM balances b
> > WHERE b.fund_id = @fund_id
> > AND b.bal_date < @stmt_date
> > AND b.bal_date >= @prev_date
> > AND b.issue_transaction_id =
> > #report_data.issue_trans_id
> > AND b.shares = 0 )
> > AND me.series_id = #report_data.series_id
> > AND me.fund_id = @fund_id
> > AND me.seqnum = 1
> > AND #report_data.nnav IS NULL
> > AND #report_data.issue_trans_id = f.issue_transaction_id
> > AND f.shares_val = 0
> >
> > UPDATE #report_data
> > SET eq_balance = isnull(b.sh_adj_ps, 0) * isnull(b.shares, 0)
> > FROM #bal b, #fund_data fd
> > WHERE b.issue_transaction_id = fd.issue_transaction_id
> > AND b.issue_transaction_id = #report_data.issue_trans_id
> > AND b.bal_date = @stmt_date
> >
> > DROP TABLE #date_info2
> >
> > UPDATE #report_data
> > SET pctmonth = ROUND(((nnav - prev_nnav) / prev_nnav)*100,6)
> > ,pctyear = ROUND(((nnav - yr_nnav) / yr_nnav)*100,6)
> > ,g_beginbal = ROUND(ISNULL(prev_gnav*f.open_shares,0),2)
> > ,g_value = ROUND(ISNULL(gnav*f.shares,0),2)
> > ,net_profit = ROUND( ( ISNULL( ((nnav-gnav) * (f.open_shares
> > + CASE WHEN
> > @add_todate_subs = 1 THEN f.sub_activity ELSE 0 END
> > + CASE WHEN
> > @add_todate_redem = 1 THEN f.rdp_activity ELSE 0 END
> > + CASE WHEN
> > (@add_todate_trans_r + @add_todate_trans_s) > 0 THEN f.transfer_activity
> > ELSE 0 END
> > + CASE WHEN
> > (@add_todate_roll_r + @add_todate_roll_s) > 0 THEN f.rollup_activity
> > ELSE 0
> > END) ) ,0)
> > - ISNULL( ((prev_nnav-prev_gnav)
> > * (f.open_shares + CASE WHEN
> > (@add_todate_trans_r + @add_todate_trans_s) > 0 THEN f.transfer_activity
> > ELSE 0 END)),0)
> >
> > )
> > ,2)
> > ,transshares = (f.open_shares - f.shares) * -1
> > FROM #fund_data f
> > WHERE f.issue_transaction_id = #report_data.issue_trans_id
> > AND prev_nnav <> 0
> > AND yr_nnav <> 0
> >
>
> --------------------------------------------------------------------------
--
> > -----
>
> --------------------------------------------------------------------------
--
> > -----
> > IF @use_ror_logic = 1 AND (SELECT method_id FROM fund WHERE fund_id =
> > @fund_id) >= 50
> > BEGIN
> > INSERT #investor_ror
> > SELECT DISTINCT investor_id, class_id, series_id, 0, 0, 0, 0
> > FROM #report_data
> >
> > EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date,
> > @stmt_date
> >
> > IF EXISTS (SELECT DISTINCT 'FOUND' FROM #investor_ror)
> > UPDATE #report_data
> > SET pctmonth = 0
> > ,pctyear = 0
> > FROM fundinvestors fi
> > WHERE fi.fund_id = @fund_id
> > AND fi.investor_id = #report_data.investor_id
> > AND fi.gp_investor <> 1
> > --gp investor has only one series and special logic applies
> >
> > UPDATE #report_data
> > SET pctmonth = i.mtd_ror
> > ,pctyear = i.ytd_ror
> > FROM #investor_ror i
> > WHERE #report_data.investor_id = i.investor_id
> > AND #report_data.class_id = i.class_id
> > AND #report_data.series_id = i.series_id
> > AND #report_data.pctmonth = 0
> > AND #report_data.pctyear = 0
> > END
>
> --------------------------------------------------------------------------
--
> > -----
> >
> > SELECT @next_prev_date = ISNULL((SELECT MIN(nav_date) FROM me_nav
> > WHERE fund_id = @fund_id AND nav_date >
> > @prev_date),@stmt_date)
> >
> > UPDATE #report_data
> > SET pctmonth = 0
> > , pctyear = CASE WHEN @show_ytd_ror_t1_redem = 1 THEN
> > ROUND(((prev_nnav - yr_nnav) / yr_nnav)*100,6) ELSE 0 END
> > FROM trans t, redemptions r, #fund_data f, #bal b
> > WHERE t.fund_id = @fund_id
> > AND f.issue_transaction_id = #report_data.issue_trans_id
> > AND r.issue_transaction_id = #report_data.issue_trans_id
> > AND b.issue_transaction_id = #report_data.issue_trans_id
> > AND b.bal_date = t.transaction_date
> > AND b.shares = 0 -- Check for full redemption
> > AND t.transaction_id = r.transaction_id
> > AND t.transaction_date >= @prev_date
> > AND t.transaction_date < @next_prev_date
> > AND f.shares = 0
> > AND f.shares_val = 0
> > AND f.shares_val = ( SELECT SUM(ff.shares_val) FROM
> > #fund_data
> > ff, trans tt
> > WHERE tt.fund_id = @fund_id
> > AND tt.transaction_id =
> > ff.issue_transaction_id
> > AND tt.series_id =
> > #report_data.series_id
> > AND tt.investor_id =
> > #report_data.investor_id)
>
> --------------------------------------------------------------------------
--
> > -----
>
> --------------------------------------------------------------------------
--
> > -----
> >
> > DECLARE c_name CURSOR FOR SELECT investor_id FROM #investors
> > OPEN c_name
> > FETCH c_name INTO @investor_id
> > WHILE(@@sqlstatus=0)
> > BEGIN
> > SELECT @temp_iname = (SELECT name FROM investor WHERE investor_id =
> > @investor_id)
> > EXEC p_strip_name @temp_iname, @iname output, @first_name_first
> >
> > UPDATE #investors
> > SET iname = @iname
> > WHERE investor_id = @investor_id
> >
> > FETCH c_name INTO @investor_id
> > END
> > CLOSE c_name
> > DEALLOCATE CURSOR c_name
> >
> > UPDATE #report_data
> > SET iname = i.iname
> > FROM #investors i
> > WHERE i.investor_id = #report_data.investor_id
> >
> >
> > DELETE #report_data
> > FROM fund f, trans t
> > WHERE f.fund_id = @fund_id
> > AND f.report_statement_id = 603
> > AND #report_data.issue_trans_id = t.activity_trans_id
> > AND t.transaction_type = 2
> > AND t.proceeds_entered > 0
> >
> > -------------------------------------------
> > ------------ OUTPUT STATEMENTS ------------
> > -------------------------------------------
> >
> > INSERT #out_result
> > SELECT
> > DISTINCT
> > r.hid
> > , r.series_id
> > , r.contact_id
> > , r.investor_id
> > , r.issue_trans_id
> > , r.invnum
> > , r.g_beginbal
> > , r.g_value
> > , r.nnav
> > , r.gnav
> > , r.prev_nnav
> > , r.prev_gnav
> > , r.yr_nnav
> > , r.yr_gnav
> > , r.yr_shares
> > , r.pctyear
> > , r.pctmonth
> > , r.net_profit
> > , r.cur
> > , r.transshares
> > , r.method_id
> > , r.iname
> > , r.series_name
> > , f.open_shares
> > , f.open_shares_val
> > , f.shares
> > , f.shares_val
> > , f.sub_activity
> > , f.sub_activity_val
> > , f.rdp_activity
> > , f.rdp_activity_val
> > , f.transfer_activity
> > , f.transfer_activity_val
> > , f.rollup_activity
> > , f.rollup_activity_val
> > , f.switch_activity
> > , f.switch_activity_val
> > , r.eq_balance
> > FROM #report_data r
> > ,#fund_data f
> > WHERE f.issue_transaction_id = r.issue_trans_id
> >
> >
> > IF @out_nav_info <> 0
> > INSERT #nav_info
> > SELECT
> > series_id = series_id
> > ,nav_date = yr_min_date
> > ,nnav = yr_nnav
> > ,gnav = yr_gnav
> > FROM #date_info
> > WHERE yr_min_date IS NOT NULL
> >
> > DROP TABLE #report_data
> > DROP TABLE #fund_data
> > DROP TABLE #date_info
> >
> > GO
> >
> >
> > "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
> > message news:369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com...
> >> Please show us how you created the temporary tables. If you enclosed
> >> the table name in doublequotes then you may have created a permanent
> >> table that just happens to have a name beginning with #. Look in
> >> SYSTABLE to be sure; if it's there, it's not a local temporary table.
> >>
> >> On 19 Mar 2004 07:28:17 -0800, "Brett Morgan" <bmorgan@imsi.com>
> >> wrote:
> >>
> >> >I've discovered a weird locking problem in a stored procedure we are
> > using
> >> >that's locking temp tables across connections. I am running Sybase
ASA
> >> >8.0.3, build 4322.
> >> >
> >> >The procedure creates some local temp tables, inserts, and updates
into
> > the
> >> >tables then finally selects data from tables. If I run the procedure
> >> in
> > two
> >> >different instances of DBISQL (autocommit off) the second execution
> > hangs.
> >> >If I commit on the first execution the second execution executes. I
> >> ran
> >> >sa_conn_info and saw that the first execution was blocking the
> >> second. I
> >> >couldn't figure out why since all the inserts/updates are done to temp
> >> >tables.
> >> >
> >> >I then turned blocking off using, "set option blocking = off." I
again
> >> >executed the procedure in two instances of DBISQL. I received an
error
> >> >message for the second execution saying that a temp table is locked,
> >> "ASA
> >> >Error -210: User 'DBA' has the row in '#report_data' locked."
> >> >
> >> >It was my understanding that temp tables are exclusive to a
connection,
> >> >therefore why would they be locked across two connections? Any help
> > would
> >> >be much appreciated.
> >> >
> >> >Brett Morgan
> >> >bmorgan@imsi.com
> >> >
> >>
> >> --
> >> SQL Anywhere Studio 9 Developer's Guide
> >> Buy the book:
> > http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> >> bcarter@risingroad.com
> >> Mobile and Distributed Enterprise Database Applications
> >> www.risingroad.com
> >
> >
>
>
>
> --
> Peter Bumbulis
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288


Glenn Paulley Posted on 2004-03-19 19:05:26.0Z
Newsgroups: ianywhere.public.general
Subject: Re: temp table locking
From: Glenn Paulley <paulley@ianywhere.com>
References: <405b1191$1@forums-1-dub> <369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com> <405b2bdc@forums-2-dub> <opr44gldlkgaovig@bumbulis-xp.sybase.com> <405b4130@forums-1-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns94B18F52A42F9paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 19 Mar 2004 11:05:21 -0800, paulley-t41.sybase.com
Lines: 1651
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Mar 2004 11:05:25 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Mar 2004 11:05:26 -0800
X-Trace: forums-1-dub 1079723126 10.22.108.75 (19 Mar 2004 11:05:26 -0800)
X-Original-Trace: 19 Mar 2004 11:05:26 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2810
Article PK: 6280

I suspect that you're being bitten by mixing SQL dialects in some way. I
cannot take the defn of #report_data below and get a permanent table to
be created.

What happens if you eliminate the DROP TABLE #report_data at the end of
the procedure? If you disconnect, reconnect, and re-execute the
procedure, does it still work?

Glenn

"Brett Morgan" <bmorgan@imsi.com> wrote in news:405b4130@forums-1-dub:

> I tried that and got a syntax error that made now sense.
>
>
> "Peter Bumbulis" <bumbulis@ianywhere.com> wrote in message
> news:opr44gldlkgaovig@bumbulis-xp.sybase.com...
>> Does it make any difference if you change the "create table #t ..."
>> to "declare local temporary table #t..."?
>>
>> Peter
>> On 19 Mar 2004 09:20:31 -0800, Brett Morgan <bmorgan@imsi.com> wrote:
>>
>> > Here is the procs, one main and one sub-proc. It's a beast. The
>> > table names are not in double quote and not appearing in SYSTABLE.
>> > Thanks
>> >
>> > CREATE PROC p_part_stmt
>> > (
>> > @fund_id NUMERIC(10)
>> > ,@year_date CHAR(12) = NULL
>> > ,@prev_date CHAR(12) = NULL
>> > ,@quarter_date CHAR(12) = NULL
>> > ,@stmt_date CHAR(12)
>> > ,@from_hid NUMERIC(10)
>> > ,@to_hid NUMERIC(10)
>> > ,@fund_summary NUMERIC(1)
>> > ,@summary_funds VARCHAR(50) = NULL
>> > ,@fund_to_date_info NUMERIC(1) = 0
>> > ,@sort_order NUMERIC(2) = 0
>> > ,@signature_info NUMERIC(1) = 0
>> > ,@out_nav_info NUMERIC(1) = 1
>> > ,@parmemail NUMERIC(1) = 1
>> > ,@parmmail NUMERIC(1) = 1
>> > ,@parmfax NUMERIC(1) = 1
>> > ,@parmother NUMERIC(1) = 0
>> > ,@parmother2 NUMERIC(1) = 0
>> > ,@parmregaddr NUMERIC(1) = 0
>> > ,@first_name_first NUMERIC(1) = 1
>> > ,@calc_incep_ror NUMERIC(1) = 0
>> > ,@gp_filter NUMERIC(1) = NULL
>> > ,@investor_level NUMERIC(1) = 0
>> > ,@no_composite_ror NUMERIC(1) = 1
>> > ,@export_output NUMERIC(1) = 0
>> > ,@income_stmt NUMERIC(1) = 0
>> > ,@show_ytd_ror_t1_redem NUMERIC(1) = 0
>> > ,@weekly_estimates NUMERIC(1) = 0
>> > )
>> > AS
>> >
>> >
>> > DECLARE
>> > @hid NUMERIC(10),
>> > @series_id NUMERIC(10),
>> > @contact_id NUMERIC(10),
>> > @investor_id NUMERIC(10),
>> > @issue_trans_id NUMERIC(10),
>> > @nnav NUMERIC(16,6),
>> > @gnav NUMERIC(16,6),
>> > @cur VARCHAR(10),
>> > @method_id NUMERIC(5),
>> > @iname VARCHAR(150),
>> > @temp_iname VARCHAR(150),
>> > @group_by_class NUMERIC(1),
>> > @r_decval NUMERIC(1)
>> > ,@add_todate_redem NUMERIC(1)
>> > ,@add_todate_trans_r NUMERIC(1)
>> > ,@add_todate_roll_r NUMERIC(1)
>> > ,@add_todate_switch_r NUMERIC(1)
>> > ,@add_todate_exch_r NUMERIC(1)
>> > ,@msg TEXT
>> >
>> >
>> > CREATE TABLE #out_result(
>> > hid NUMERIC(10) NULL
>> > ,series_id NUMERIC(10) NULL
>> > ,contact_id NUMERIC(10) NULL
>> > ,investor_id NUMERIC(10) NULL
>> > ,issue_trans_id NUMERIC(10) NULL
>> > ,invnum NUMERIC(10) NULL
>> > ,g_beginbal NUMERIC(16,6) NULL
>> > ,g_value NUMERIC(16,6) NULL
>> > ,nnav NUMERIC(16,6) NULL
>> > ,gnav NUMERIC(16,6) NULL
>> > ,prev_nnav NUMERIC(16,6) NULL
>> > ,prev_gnav NUMERIC(16,6) NULL
>> > ,yr_nnav NUMERIC(16,6) NULL
>> > ,yr_gnav NUMERIC(16,6) NULL
>> > ,yr_shares NUMERIC(16,6) NULL
>> > ,pctyear NUMERIC(16,6) NULL
>> > ,pctmonth NUMERIC(16,6) NULL
>> > ,net_profit NUMERIC(16,6) NULL
>> > ,cur VARCHAR(10) NULL
>> > ,transshares NUMERIC(16,6) NULL
>> > ,method_id NUMERIC(5) NULL
>> > ,iname VARCHAR(150) NULL
>> > ,series_name VARCHAR(25) NULL
>> > ,open_shares NUMERIC(16,6) NULL
>> > ,open_shares_val NUMERIC(16,6) NULL
>> > ,shares NUMERIC(16,6) NULL
>> > ,shares_val NUMERIC(16,6) NULL
>> > ,sub_activity NUMERIC(16,6) NULL
>> > ,sub_activity_val NUMERIC(16,6) NULL
>> > ,rdp_activity NUMERIC(16,6) NULL
>> > ,rdp_activity_val NUMERIC(16,6) NULL
>> > ,transfer_activity NUMERIC(16,6) NULL
>> > ,transfer_activity_val NUMERIC(16,6) NULL
>> > ,rollup_activity NUMERIC(16,6) NULL
>> > ,rollup_activity_val NUMERIC(16,6) NULL
>> > ,switch_activity NUMERIC(16,6) NULL
>> > ,switch_activity_val NUMERIC(16,6) NULL
>> > ,eq_balance NUMERIC(16,6) NULL
>> > )
>> >
>> > CREATE TABLE #nav_info
>> > (series_id NUMERIC(10) NULL
>> > ,nav_date DATETIME NULL
>> > ,nnav NUMERIC(16,6) NULL
>> > ,gnav NUMERIC(16,6) NULL
>> > )
>> >
>> > CREATE TABLE #final
>> > (hid NUMERIC(10) NULL
>> > ,contact_id NUMERIC(10) NULL
>> > ,beginbal NUMERIC(16,6) NULL
>> > ,g_beginbal NUMERIC(16,6) NULL
>> > ,value NUMERIC(16,6) NULL
>> > ,g_value NUMERIC(16,6) NULL
>> > ,subsrip NUMERIC(16,6) NULL
>> > ,redemp NUMERIC(16,6) NULL
>> > ,open_shares NUMERIC(16,6) NULL
>> > ,shares NUMERIC(16,6) NULL
>> > ,transshares NUMERIC(16,6) NULL
>> > ,pctincep NUMERIC(16,6) NULL
>> > ,pctyear NUMERIC(16,6) NULL
>> > ,pctmonth NUMERIC(16,6) NULL
>> > ,net_profit NUMERIC(16,6) NULL
>> > ,cur VARCHAR(25) NULL
>> > ,iname VARCHAR(255) NULL
>> > ,contact VARCHAR(255) NULL
>> > ,rep VARCHAR(255) NULL
>> > ,addr1 VARCHAR(255) NULL
>> > ,addr2 VARCHAR(255) NULL
>> > ,addr3 VARCHAR(255) NULL
>> > ,addr4 VARCHAR(255) NULL
>> > ,addr5 VARCHAR(255) NULL
>> > ,phone VARCHAR(255) NULL
>> > ,fax VARCHAR(255) NULL
>> > ,email VARCHAR(255) NULL
>> > ,series_name VARCHAR(50) NULL
>> > ,nnav NUMERIC(16,6) NULL
>> > ,r_decnav NUMERIC(5) NULL
>> > ,r_decsh NUMERIC(5) NULL
>> > ,class VARCHAR(50) NULL
>> > ,identifier2 VARCHAR(255) NULL
>> > ,class_id NUMERIC(10) NULL
>> > ,investor_id NUMERIC(10) NULL
>> > ,series_id NUMERIC(10) NULL
>> > ,eq_balance NUMERIC(16,6) NULL
>> > ,prev_nnav NUMERIC(16,6) NULL
>> > )
>> >
>> > CREATE TABLE #investor_ror(
>> > investor_id NUMERIC(10)
>> > ,class_id NUMERIC(10)
>> > ,series_id NUMERIC(10)
>> > ,mtd_ror NUMERIC(16,6)
>> > ,ytd_ror NUMERIC(16,6)
>> > ,gr_mtd_ror NUMERIC(16,6)
>> > ,gr_ytd_ror NUMERIC(16,6)
>> > )
>> >
>> > IF @to_hid = 99999
>> > SELECT @to_hid = 999999
>> >
>> > IF @year_date IS NULL
>> > SELECT @year_date = CONVERT( CHAR(4),(DATEPART(YEAR,@stmt_date)-1)
>> > ) + '-12-31'
>> >
>> > SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
>> > SELECT @r_decval = ( SELECT r_decval FROM fund WHERE fund_id =
> @fund_id )
>> >
>> > SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0)
>> > FROM catalogdata
>> > WHERE cdid = 1 AND cd_type = @fund_id
>> > AND
>> > cd_charcode = 'REDEM')
>> > ,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = 1 AND cd_type = @fund_id
>> > AND
>> > cd_charcode = 'TRANSFER_REDEM')
>> > ,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = 1 AND cd_type = @fund_id
>> > AND
>> > cd_charcode = 'ROLLUP_REDEM')
>> > ,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = 1 AND cd_type = @fund_id
>> > AND
>> > cd_charcode = 'SWITCH_REDEM')
>> > ,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = 1 AND cd_type = @fund_id
>> > AND
>> > cd_charcode = 'EXCHANGE_REDEM')
>> >
>> > EXEC subp_part_stmt_main @fund_id, @year_date, @prev_date,
> @quarter_date,
>> > @stmt_date, @from_hid, @to_hid
>> > ,@out_nav_info, @parmemail, @parmmail,
>> > @parmfax,
>> > @parmother, @parmother2, @parmregaddr, @first_name_first
>> > ,@use_ror_logic = @no_composite_ror,
>> > @show_ytd_ror_t1_redem = @show_ytd_ror_t1_redem
>> > ,@weekly_estimates=@weekly_estimates
>> >
>> > UPDATE #out_result
>> > SET rdp_activity = CASE WHEN transfer_activity < 0 THEN
>> > rdp_activity
>> > + transfer_activity ELSE rdp_activity END
>> > ,rdp_activity_val = CASE WHEN transfer_activity_val < 0 THEN
>> > rdp_activity_val + transfer_activity_val ELSE rdp_activity_val END
>> > ,sub_activity = CASE WHEN transfer_activity > 0 THEN
>> > sub_activity
>> > + transfer_activity ELSE sub_activity END
>> > ,sub_activity_val = CASE WHEN transfer_activity_val > 0 THEN
>> > sub_activity_val + transfer_activity_val ELSE sub_activity_val END
>> >
>> > UPDATE #out_result
>> > SET rdp_activity = CASE WHEN rollup_activity < 0 THEN
>> > rdp_activity +
>> > rollup_activity ELSE rdp_activity END
>> > ,rdp_activity_val = CASE WHEN rollup_activity_val < 0 THEN
>> > rdp_activity_val + rollup_activity_val ELSE rdp_activity_val END
>> > ,sub_activity = CASE WHEN rollup_activity > 0 THEN
>> > sub_activity +
>> > rollup_activity ELSE sub_activity END
>> > ,sub_activity_val = CASE WHEN rollup_activity_val > 0 THEN
>> > sub_activity_val + rollup_activity_val ELSE sub_activity_val END
>> >
>> > UPDATE #out_result
>> > SET rdp_activity = CASE WHEN switch_activity < 0 THEN
>> > rdp_activity +
>> > switch_activity ELSE rdp_activity END
>> > ,rdp_activity_val = CASE WHEN switch_activity_val < 0 THEN
>> > rdp_activity_val + switch_activity_val ELSE rdp_activity_val END
>> > ,sub_activity = CASE WHEN switch_activity > 0 THEN
>> > sub_activity +
>> > switch_activity ELSE sub_activity END
>> > ,sub_activity_val = CASE WHEN switch_activity_val > 0 THEN
>> > sub_activity_val + switch_activity_val ELSE sub_activity_val END
>> >
>> >
>> >
>> > IF @no_composite_ror = 0
>> > BEGIN
>> > INSERT #investor_ror
>> > SELECT DISTINCT o.investor_id, s.class_id, o.series_id, 0, 0,
>> > 0, 0 FROM #out_result o, series s
>> > WHERE s.fund_id = @fund_id
>> > AND s.series_id = o.series_id
>> >
>> > EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date,
>> > @stmt_date
>> > END
>> >
>> >
> /**********************************************************************
> ***/
>> >
>> > IF EXISTS (SELECT * FROM rate_of_return WHERE fund_id = @fund_id
>> > AND series_id = 0) AND @no_composite_ror = 1
>> > BEGIN
>> > SELECT @group_by_class = 1
>> >
>> > INSERT #final
>> > SELECT
>> > hid = rd.hid
>> > ,contact_id = rd.contact_id
>> > ,beginbal = ISNULL(SUM(open_shares_val) ,0)
>> > ,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
>> > ,value = ISNULL(SUM(shares_val) ,0)
>> > ,g_value = ISNULL(SUM(g_value) ,0)
>> > ,subsrip = ISNULL(SUM(sub_activity_val),0)
>> > ,redemp = ISNULL(SUM(rdp_activity_val),0)
>> > ,open_shares= ISNULL(SUM(open_shares) ,0)
>> > ,shares = ISNULL(SUM(rd.shares) ,0)
>> > ,transshares= SUM(transshares)
>> > ,pctincep = 0
>> > ,pctyear = MAX(pctyear)
>> > ,pctmonth = MAX(pctmonth)
>> > ,net_profit = SUM(ISNULL(rd.net_profit,0))
>> > ,cur = MAX(cur)
>> > ,iname = iname
>> > ,contact = MAX(c.contact)
>> > ,rep = MAX(c.rep)
>> > ,addr1 = MAX(c.addr1)
>> > ,addr2 = MAX(c.addr2)
>> > ,addr3 = MAX(c.addr3)
>> > ,addr4 = MAX(c.addr4)
>> > ,addr5 = MAX(c.addr5)
>> > ,phone = MAX(c.phone)
>> > ,fax = MAX(c.fax)
>> > ,email = MAX(c.email)
>> > ,series_name= RTRIM(cl.identifier)
>> > ,nnav = MAX(rd.nnav)
>> > ,r_decnav = MAX(f.r_decnav)
>> > ,r_decsh = MAX(f.r_decsh)
>> > ,class = cl.identifier
>> > ,identifier2= MAX(cl.identifier2)
>> > ,class_id = cl.class_id
>> > ,investor_id= rd.investor_id
>> > ,series_id = NULL
>> > ,0
>> > ,prev_nnav = MAX(rd.prev_nnav)
>> > FROM
>> > contact c
>> > ,#out_result rd
>> > ,series se
>> > ,classes cl
>> > ,fund f
>> > WHERE c.contact_id = rd.contact_id
>> > AND se.series_id = rd.series_id
>> > AND se.fund_id = @fund_id
>> > AND cl.fund_id = se.fund_id
>> > AND f.fund_id = se.fund_id
>> > AND cl.class_id = se.class_id
>> > GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
>> > cl.class_id, rd.iname
>> > END
>> >
>> > ELSE
>> >
>> > BEGIN
>> > SELECT @group_by_class = 0
>> >
>> > INSERT #final
>> > SELECT
>> > hid = rd.hid
>> > ,contact_id = rd.contact_id
>> > ,beginbal = ISNULL(SUM(open_shares_val) ,0)
>> > ,g_beginbal = ISNULL(SUM(g_beginbal) ,0)
>> > ,value = ISNULL(SUM(shares_val) ,0)
>> > ,g_value = ISNULL(SUM(g_value) ,0)
>> > ,subsrip = ISNULL(SUM(sub_activity_val),0)
>> > ,redemp = ISNULL(SUM(rdp_activity_val),0)
>> > ,open_shares= ISNULL(SUM(open_shares) ,0)
>> > ,shares = ISNULL(SUM(rd.shares) ,0)
>> > ,transshares= SUM(transshares)
>> > ,pctincep = 0
>> > ,pctyear = MAX(pctyear)
>> > ,pctmonth = MAX(pctmonth)
>> > ,net_profit = SUM(ISNULL(rd.net_profit,0))
>> > ,cur = MAX(cur)
>> > ,iname = iname
>> > ,contact = MAX(c.contact)
>> > ,rep = MAX(c.rep)
>> > ,addr1 = MAX(c.addr1)
>> > ,addr2 = MAX(c.addr2)
>> > ,addr3 = MAX(c.addr3)
>> > ,addr4 = MAX(c.addr4)
>> > ,addr5 = MAX(c.addr5)
>> > ,phone = MAX(c.phone)
>> > ,fax = MAX(c.fax)
>> > ,email = MAX(c.email)
>> > ,series_name= RTRIM(cl.identifier) + '_'
>> > +RTRIM(se.identifier) ,nnav = MAX(rd.nnav)
>> > ,r_decnav = MAX(f.r_decnav)
>> > ,r_decsh = MAX(f.r_decsh)
>> > ,class = cl.identifier
>> > ,identifier2= MAX(cl.identifier2)
>> > ,class_id = cl.class_id
>> > ,investor_id= rd.investor_id
>> > ,series_id = rd.series_id
>> > ,0
>> > ,prev_nnav = MAX(rd.prev_nnav)
>> > FROM
>> > contact c
>> > ,#out_result rd
>> > ,series se
>> > ,classes cl
>> > ,fund f
>> > WHERE c.contact_id = rd.contact_id
>> > AND se.series_id = rd.series_id
>> > AND se.fund_id = @fund_id
>> > AND cl.fund_id = se.fund_id
>> > AND f.fund_id = se.fund_id
>> > AND cl.class_id = se.class_id
>> > GROUP BY rd.hid, rd.investor_id, rd.contact_id, cl.identifier,
>> > cl.class_id, rd.series_id, se.identifier, rd.iname
>> >
>> > END
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > ----------
>> >
>> > UPDATE #final f
>> > SET transshares = ( SELECT SUM(redemp) FROM #final ff
>> > WHERE f.hid = ff.hid
>> > AND f.contact_id = ff.contact_id
>> > )
>> > UPDATE #final f
>> > SET transshares = transshares + ( SELECT SUM(subsrip) FROM #final
>> > ff
>> > WHERE f.hid = ff.hid
>> > AND f.contact_id = ff.contact_id
>> > )
>> > UPDATE #final f
>> > SET g_beginbal = ( SELECT SUM(ROUND(beginbal,@r_decval)) FROM
>> > #final ff
>> > WHERE f.hid = ff.hid
>> > AND f.contact_id = ff.contact_id
>> > )
>> >
>> > UPDATE #final f
>> > SET g_value = ( SELECT SUM(ROUND(value,@r_decval)) FROM #final
>> > ff
>> > WHERE f.hid = ff.hid
>> > AND f.contact_id = ff.contact_id
>> > )
>> >
> /**********************************************************************
> ***/
>> >
>> > IF NOT EXISTS ( SELECT 'FOUND' FROM fund_addl_info f,
>> > additional_info a
>> > WHERE a.info_id = f.info_id AND a.short_code =
>> > 'FR_PARTNERSHIP'
>> > AND f.fund_id = @fund_id )
>> > DELETE #final
>> > FROM trans t, redemptions r
>> > WHERE t.fund_id = @fund_id
>> > AND t.transaction_date >= @prev_date -- date range needed for
>> > quarterly
>> > stmts
>> > AND t.transaction_date < @stmt_date
>> > AND r.series_id = #final.series_id
>> > AND t.transaction_id = r.transaction_id
>> > AND #final.shares = 0
>> > AND t.investor_id = #final.investor_id
>> > AND ( (@add_todate_redem=1 AND t.activity_type=0 AND
>> > transaction_type=2)
>> > OR (@add_todate_trans_r=1 AND t.activity_type=1 AND
>> > transaction_type=2)
>> > OR (@add_todate_roll_r=1 AND t.activity_type=3 AND
>> > transaction_type=2)
>> > OR (@add_todate_switch_r=1 AND t.switch_transaction=1 AND
>> > transaction_type=2)
>> > OR (@add_todate_exch_r=1 AND t.activity_type=4 AND
>> > transaction_type=2)
>> > )
>> >
>> > IF @investor_level = 1
>> > BEGIN
>> > INSERT #final
>> > SELECT DISTINCT
>> > hid ,contact_id=0 ,beginbal ,g_beginbal
>> > ,value ,g_value ,subsrip ,redemp
>> > ,open_shares ,shares ,transshares ,pctincep
>> > ,pctyear ,pctmonth ,net_profit ,cur
>> > ,iname ,'','','','','','','','','',''
>> > ,series_name ,nnav ,r_decnav ,r_decsh
>> > ,class ,identifier2 ,class_id ,investor_id
>> > ,series_id ,eq_balance ,prev_nnav
>> > FROM #final
>> >
>> > DELETE #final WHERE contact_id <> 0
>> > END
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > ----------
>> >
>> >
>> > IF @calc_incep_ror = 1
>> > EXEC subp_ps_incep_ror @fund_id, @stmt_date, @group_by_class
>> >
>> >
>> > SELECT @msg = ( SELECT text_val FROM fund_addl_info f,
>> > additional_info a
>> > WHERE f.info_id = a.info_id and UPPER(short_code) =
>> > 'COMMENT'
>> > AND f.fund_id = @fund_id
>> > AND f.object_id = @fund_id)
>> >
>> >
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > ----------
>> >
>> >
>> >
> /**********************************************************************
> *****
>> > /
>> > /********************* Check for hardcoded ror values
>> > ******************/
>> >
> /**********************************************************************
> *****
>> > /
>> >
>> > UPDATE #final
>> > SET pctmonth = r.net_mtd
>> > ,pctyear = r.net_ytd
>> > ,pctincep = r.net_itd
>> > FROM ror_hardcode r, fundinvestors fi
>> > WHERE r.fund_id = @fund_id
>> > AND fi.fund_id = @fund_id
>> > AND fi.hid = #final.hid
>> > AND fi.investor_id = r.investor_id
>> > AND r.series_id = #final.series_id
>> >
>> >
>> >
> /**********************************************************************
> *****
>> > /
>> >
> /**********************************************************************
> *****
>> > /
>> > --------------------------------------------------
>> > ------------ OUTPUT SELECT STATEMENTS ------------
>> > --------------------------------------------------
>> > IF @export_output = 0
>> > BEGIN
>> > IF @fund_summary=0
>> > EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
> @prev_date,
>> > @stmt_date, @fund_to_date_info, @signature_info=@signature_info,
>> > @income_stmt=@income_stmt
>> >
>> > ELSE IF @fund_summary=1
>> > BEGIN
>> > SELECT @summary_funds = (SELECT shortname FROM fund WHERE
>> > fund_id =
>> > @fund_id)
>> > EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
>> > @prev_date,
>> > @stmt_date, @fund_to_date_info, @summary_funds,
>> > @signature_info=@signature_info, @gp_filter=@gp_filter,
>> > @income_stmt=@income_stmt
>> > END
>> >
>> > ELSE IF @fund_summary=2
>> > EXEC p_ps_fund_info @fund_id, @year_date, @prev_date,
> @prev_date,
>> > @stmt_date, @fund_to_date_info, @summary_funds,
>> > @signature_info=@signature_info, @gp_filter=@gp_filter,
>> > @income_stmt=@income_stmt
>> > END
>> >
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > ----------
>> > --------------------------- OUTPUT SELECT
>> > ORTS ------------------------------------
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > ----------
>> >
>> > IF @no_composite_ror = 0 AND @export_output = 0
>> > SELECT DISTINCT hid ,contact_id ,beginbal ,g_beginbal
>> > ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh ,i.mtd_ror ,i.ytd_ror ,prev_nnav
>> > FROM #final f, #investor_ror i
>> > WHERE f.investor_id = i.investor_id
>> > AND f.class_id = i.class_id
>> > AND i.series_id = ISNULL(f.series_id,i.series_id)
>> > ORDER BY hid, contact_id, iname, cur, series_name
>> >
>> > ELSE IF @sort_order = 0 AND @export_output = 0
>> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh ,eq_balance ,0,0,@msg
>> > FROM #final
>> > ORDER BY series_name, iname, hid, contact_id, cur
>> >
>> > ELSE IF @sort_order = 1 AND @export_output = 0
>> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh ,0,0,0,@msg
>> > FROM #final
>> > ORDER BY hid, contact_id, iname, cur, series_name
>> >
>> > ELSE IF @sort_order = 2 AND @export_output = 0
>> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh
>> > FROM #final
>> > ORDER BY class, hid, contact_id, iname, cur, series_name
>> >
>> > ELSE IF @sort_order = 3 AND @export_output = 0 -- Wellington
>> > statement needs cl.identifier2
>> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh ,0,0,0,identifier2
>> > FROM #final
>> > ORDER BY hid, contact_id, iname, cur, series_name
>> >
>> > ELSE IF @sort_order = 4 AND @export_output = 0
>> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh
>> > FROM #final
>> > ORDER BY hid, contact_id, iname, cur, series_name
>> > ELSE IF @export_output = 0
>> > SELECT hid ,contact_id ,beginbal ,g_beginbal ,value
>> > ,g_value ,subsrip ,redemp ,open_shares
>> > ,shares ,transshares ,pctincep ,pctyear ,pctmonth
>> > ,net_profit
>> > ,cur
>> > ,iname ,contact ,rep ,addr1 ,addr2
>> > ,addr3 ,addr4 ,addr5 ,phone ,fax
>> > ,email ,series_name ,nnav ,r_decnav
>> > ,r_decsh
>> > FROM #final
>> > ORDER BY hid, contact_id, iname, cur, series_name
>> >
>> > ELSE IF @export_output = 1 AND @no_composite_ror = 1
>> > INSERT #output
>> > SELECT *,0,0 FROM #final
>> > ORDER BY iname, hid, series_name
>> >
>> > ELSE IF @export_output = 1 AND @no_composite_ror = 0
>> > INSERT #output
>> > SELECT f.*,i.mtd_ror,i.ytd_ror FROM #final f, #investor_ror i
>> > WHERE f.investor_id = i.investor_id
>> > AND f.class_id = i.class_id
>> > AND i.series_id = ISNULL(f.series_id,i.series_id)
>> > ORDER BY iname, hid, series_name
>> >
>> > ELSE IF @export_output = 2
>> > SELECT hid ,contact_id ,beginbal ,value
>> > ,subsrip
>> > ,redemp ,open_shares ,shares ,pctincep
>> > ,pctyear ,pctmonth ,net_profit ,series_name, nnav
>> > FROM #final
>> > ORDER BY hid, contact_id, series_name
>> >
>> > DROP TABLE #nav_info
>> > DROP TABLE #out_result
>> > DROP TABLE #final
>> > DROP TABLE #investor_ror
>> > GO
>> >
>> >
>> > /*********************
>> > * Second proc *
>> > *********************/
>> > CREATE PROC subp_part_stmt_main
>> > (
>> > @fund_id NUMERIC(10)
>> > ,@year_date CHAR(12)
>> > ,@prev_date CHAR(12) = NULL
>> > ,@quarter_date CHAR(12) = NULL
>> > ,@stmt_date CHAR(12)
>> > ,@from_hid NUMERIC(10)
>> > ,@to_hid NUMERIC(10)
>> > ,@out_nav_info NUMERIC(1) = 1
>> > ,@parmemail NUMERIC(1) = 1
>> > ,@parmmail NUMERIC(1) = 1
>> > ,@parmfax NUMERIC(1) = 1
>> > ,@parmother NUMERIC(1) = 0
>> > ,@parmother2 NUMERIC(1) = 0
>> > ,@parmregaddr NUMERIC(1) = 0
>> > ,@first_name_first NUMERIC(1) = 1
>> > ,@temp_tables_exist NUMERIC(1) = 0
>> > ,@use_ror_logic NUMERIC(1) = 1
>> > ,@show_ytd_ror_t1_redem NUMERIC(1) = 0
>> > ,@weekly_estimates NUMERIC(1) = 0
>> > )
>> > AS
>> >
>> >
>> > DECLARE
>> > @investor_id NUMERIC(10),
>> > @iname VARCHAR(150),
>> > @temp_iname VARCHAR(150),
>> > @next_prev_date DATETIME
>> > ,@next_year_date DATETIME
>> > ,@add_todate_redem NUMERIC(1)
>> > ,@add_todate_subs NUMERIC(1)
>> > ,@add_todate_trans_r NUMERIC(1)
>> > ,@add_todate_trans_s NUMERIC(1)
>> > ,@add_todate_roll_r NUMERIC(1)
>> > ,@add_todate_roll_s NUMERIC(1)
>> > ,@add_todate_switch_r NUMERIC(1)
>> > ,@add_todate_switch_s NUMERIC(1)
>> > ,@add_todate_exch_r NUMERIC(1)
>> > ,@add_todate_exch_s NUMERIC(1)
>> > ,@t_logic_cdid NUMERIC(2)
>> > ,@r_decval NUMERIC(1)
>> >
>> > CREATE TABLE #report_data(
>> > hid NUMERIC(10) NULL,
>> > series_id NUMERIC(10) NULL,
>> > class_id NUMERIC(10) NULL,
>> > contact_id NUMERIC(10) NULL,
>> > investor_id NUMERIC(10) NULL,
>> > issue_trans_id NUMERIC(10) NULL,
>> > invnum NUMERIC(10) NULL,
>> > g_beginbal NUMERIC(16,6) NULL,
>> > g_value NUMERIC(16,6) NULL,
>> > nnav NUMERIC(16,6) NULL,
>> > gnav NUMERIC(16,6) NULL,
>> > prev_nnav NUMERIC(16,6) NULL,
>> > prev_gnav NUMERIC(16,6) NULL,
>> > yr_nnav NUMERIC(16,6) NULL,
>> > yr_gnav NUMERIC(16,6) NULL,
>> > yr_shares NUMERIC(16,6) NULL,
>> > pctyear NUMERIC(16,6) NULL,
>> > pctmonth NUMERIC(16,6) NULL,
>> > net_profit NUMERIC(16,6) NULL,
>> > cur VARCHAR(10) NULL,
>> > transshares NUMERIC(16,6) NULL,
>> > method_id NUMERIC(5) NULL,
>> > iname VARCHAR(150) NULL,
>> > series_name VARCHAR(25) NULL,
>> > eq_balance NUMERIC(16,6) NULL,
>> > primary key (investor_id,series_id,issue_trans_id,contact_id)
>> > )
>> >
>> > CREATE TABLE #single_contact(
>> > hid NUMERIC(10) NULL,
>> > contact_id NUMERIC(10) NULL,
>> > )
>> >
>> > CREATE TABLE #issue_transaction_id(
>> > issue_transaction_id NUMERIC(10)
>> > )
>> >
>> > CREATE TABLE #fund_data(
>> > issue_transaction_id NUMERIC(10)
>> > ,open_shares NUMERIC(16,6) NULL
>> > ,open_shares_val NUMERIC(16,6) NULL
>> > ,shares NUMERIC(16,6) NULL
>> > ,shares_val NUMERIC(16,6) NULL
>> > ,sub_activity NUMERIC(16,6) NULL
>> > ,sub_activity_val NUMERIC(16,6) NULL
>> > ,rdp_activity NUMERIC(16,6) NULL
>> > ,rdp_activity_val NUMERIC(16,6) NULL
>> > ,transfer_activity NUMERIC(16,6) NULL
>> > ,transfer_activity_val NUMERIC(16,6) NULL
>> > ,rollup_activity NUMERIC(16,6) NULL
>> > ,rollup_activity_val NUMERIC(16,6) NULL
>> > ,switch_activity NUMERIC(16,6) NULL
>> > ,switch_activity_val NUMERIC(16,6) NULL
>> > ,exch_activity NUMERIC(16,6) NULL
>> > ,exch_activity_val NUMERIC(16,6) NULL
>> > )
>> >
>> > CREATE TABLE #investors(
>> > hid NUMERIC(10) NULL
>> > ,investor_id NUMERIC(10)
>> > ,iname VARCHAR(255) NULL
>> > ,method_id NUMERIC(5) NULL
>> > ,cur VARCHAR(50) NULL
>> > ,PRIMARY KEY(investor_id)
>> > )
>> >
>> > CREATE TABLE #investor_ror(
>> > investor_id NUMERIC(10)
>> > ,class_id NUMERIC(10)
>> > ,series_id NUMERIC(10)
>> > ,mtd_ror NUMERIC(16,6)
>> > ,ytd_ror NUMERIC(16,6)
>> > ,gr_mtd_ror NUMERIC(16,6)
>> > ,gr_ytd_ror NUMERIC(16,6)
>> > )
>> >
>> > CREATE INDEX x_ir ON #investor_ror ( investor_id, class_id,
>> > series_id )
>> >
>> > CREATE TABLE #contacts(
>> > investor_id NUMERIC(10)
>> > ,contact_id NUMERIC(10)
>> > ,contact_type NUMERIC(5)
>> > ,flagmail NUMERIC(5)
>> > ,flagfax NUMERIC(5)
>> > ,flagemail NUMERIC(5)
>> > ,flagother NUMERIC(5)
>> > ,flagother2 NUMERIC(5)
>> > ,weekly_estimates NUMERIC(1)
>> > ,PRIMARY KEY (investor_id, contact_id)
>> > )
>> >
>> > IF @out_nav_info <> 1
>> > CREATE TABLE #nav_info
>> > (series_id NUMERIC(10) NULL
>> > ,nav_date DATETIME NULL
>> > ,nnav NUMERIC(16,6) NULL
>> > ,gnav NUMERIC(16,6) NULL
>> > )
>> >
>> >
>> > SELECT @prev_date = ISNULL(@prev_date, @quarter_date)
>> > ,@parmemail = CASE WHEN @parmemail <> 1 THEN -1 ELSE 1
>> > END ,@parmmail = CASE WHEN @parmmail <> 1 THEN -1 ELSE
>> > 1 END ,@parmfax = CASE WHEN @parmfax <> 1 THEN -1
>> > ELSE 1 END ,@parmother = CASE WHEN @parmother <> 1 THEN
>> > -1 ELSE 1 END ,@parmother2 = CASE WHEN @parmother2 <> 1
>> > THEN -1 ELSE 1 END ,@parmregaddr = CASE WHEN @parmregaddr <>
>> > 1 THEN -1 ELSE 2 END ,@t_logic_cdid = 1 /* t logic for
>> > partner statements */
>> >
>> >
>> > SELECT @add_todate_redem = (SELECT ISNULL(MAX(cd_numvalue),0)
>> > FROM catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'REDEM')
>> > ,@add_todate_subs = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'SUBS')
>> > ,@add_todate_trans_r = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'TRANSFER_REDEM')
>> > ,@add_todate_trans_s = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'TRANSFER_SUBS')
>> > ,@add_todate_roll_r = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'ROLLUP_REDEM')
>> > ,@add_todate_roll_s = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'ROLLUP_SUBS')
>> > ,@add_todate_switch_r= (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'SWITCH_REDEM')
>> > ,@add_todate_switch_s= (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'SWITCH_SUBS')
>> > ,@add_todate_exch_r = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'EXCHANGE_REDEM')
>> > ,@add_todate_exch_s = (SELECT ISNULL(MAX(cd_numvalue),1)
>> > FROM
>> > catalogdata
>> > WHERE cdid = @t_logic_cdid AND
>> > cd_type =
>> > @fund_id AND cd_charcode = 'EXCHANGE_SUBS')
>> >
>> > IF NOT EXISTS (SELECT * FROM me_nav WHERE nav_date = @year_date AND
>> > fund_id
>> > = @fund_id)
>> > SELECT @next_year_date = (SELECT MIN(nav_date)
>> > FROM me_nav
>> > WHERE fund_id = @fund_id
>> > AND nav_date <= @stmt_date
>> > AND posted = 1)
>> > ELSE
>> > SELECT @next_year_date = @year_date
>> >
>> > SELECT @r_decval = r_decval FROM fund WHERE fund_id = @fund_id
>> >
>> > INSERT #investors
>> > SELECT hid, investor_id, NULL, f.method_id, fl.description
>> > FROM fundinvestors fi, fund f, flags fl
>> > WHERE fi.fund_id = @fund_id
>> > AND f.fund_id = @fund_id
>> > AND f.ccy = fl.integer_value
>> > AND fl.flag_name = 'CCY'
>> > AND fi.hid BETWEEN @from_hid AND @to_hid
>> >
>> > INSERT #contacts
>> > SELECT i.investor_id, c.contact_id, fc.contact_type, c.flagmail,
>> > c.flagfax,
>> > c.flagemail, c.flagother, c.flagother2, c.weekly_estimates
>> > FROM #investors i, fundcontacts fc, contact c
>> > WHERE fc.fund_id = @fund_id
>> > AND fc.investor_id = i.investor_id
>> > AND fc.contact_id = c.contact_id
>> > AND c.contact_id = fc.contact_id
>> >
>> > CREATE TABLE #bal(
>> > bal_date DATETIME
>> > ,issue_transaction_id NUMERIC(10)
>> > ,series_id NUMERIC(10) NULL
>> > ,investor_id NUMERIC(10) NULL
>> > ,class_id NUMERIC(10) NULL
>> > ,sh_adj_ps NUMERIC(16,6) NULL
>> > ,shares NUMERIC(20,6) NULL
>> > ,PRIMARY KEY(issue_transaction_id, bal_date)
>> > )
>> >
>> > CREATE INDEX x_bal_2 ON #bal (investor_id, series_id, class_id)
>> >
>> > CREATE TABLE #bal_yecalc(
>> > bal_date DATETIME
>> > ,issue_transaction_id NUMERIC(10)
>> > ,min_yecalc NUMERIC(1)
>> > )
>> >
>> > INSERT #bal_yecalc
>> > SELECT bal_date, issue_transaction_id, MIN(yecalc)
>> > FROM balances
>> > WHERE fund_id = @fund_id
>> > AND bal_date <= @stmt_date
>> > AND bal_date >= @prev_date
>> > GROUP BY bal_date, issue_transaction_id
>> >
>> > INSERT #bal
>> > SELECT DISTINCT b.bal_date, b.issue_transaction_id, t.series_id,
>> > t.investor_id, s.class_id, b.sh_adj_ps, b.shares
>> > FROM balances b, trans t, series s, #bal_yecalc bb
>> > WHERE b.fund_id = @fund_id
>> > AND t.transaction_id = b.issue_transaction_id
>> > AND b.issue_transaction_id = bb.issue_transaction_id
>> > AND s.series_id = t.series_id
>> > AND b.bal_date <= @stmt_date
>> > AND b.bal_date >= @prev_date
>> > AND b.bal_date = bb.bal_date
>> > AND b.yecalc = bb.min_yecalc
>> > AND t.posted = 1
>> >
>> > INSERT #report_data
>> > SELECT
>> > DISTINCT
>> > hid = fi.hid
>> > ,series_id = me.series_id
>> > ,class_id = b.class_id
>> > ,contact_id = c.contact_id
>> > ,investor_id = fi.investor_id
>> > ,issue_trans_id = b.issue_transaction_id
>> > ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
>> > L ,cur = CASE WHEN cl.ccy IS NULL THEN fi.cur
>> > ELSE (SELECT f.description FROM flags f
>> > WHERE f.flag_name = 'CCY'
>> > AND f.integer_value = cl.ccy )
>> > END
>> > ,NULL
>> > ,method_id = fi.method_id
>> > ,NULL,NULL, 0
>> > FROM
>> > me_nav me
>> > ,#bal b
>> > ,#investors fi
>> > ,#contacts c
>> > ,classes cl
>> > WHERE me.fund_id = @fund_id
>> > AND cl.fund_id = @fund_id
>> > AND me.series_id = b.series_id
>> > AND cl.class_id = b.class_id
>> > AND fi.investor_id = b.investor_id
>> > AND c.investor_id = fi.investor_id
>> > AND me.nav_date = b.bal_date
>> > AND me.seqnum = 1
>> > AND me.posted = 1
>> > AND ( c.contact_type = @parmregaddr
>> > OR c.flagmail = @parmmail
>> > OR c.flagfax = @parmfax
>> > OR c.flagemail = @parmemail
>> > OR c.flagother = @parmother
>> > -- OR c.flagother2 = @parmother2
>> > )
>> > AND c.weekly_estimates >= @weekly_estimates
>> > ORDER BY fi.hid,me.series_id,c.contact_id
>> >
>> > IF @parmother2 = 1
>> > BEGIN
>> > INSERT #single_contact
>> > SELECT hid, MIN(contact_id)
>> > FROM #report_data
>> > GROUP BY hid
>> >
>> > DELETE #report_data
>> > FROM #single_contact s
>> > WHERE #report_data.hid = s.hid
>> > AND #report_data.contact_id <> s.contact_id
>> > END
>> >
>> >
>> > INSERT #issue_transaction_id
>> > SELECT DISTINCT issue_trans_id
>> > FROM #report_data
>> >
>> >
>> > EXEC subp_get_fund_data @prev_date, @stmt_date,
>> > @temp_tables_exist=@temp_tables_exist
>> >
>> > IF EXISTS ( SELECT 'FOUND' FROM v_additional_info WHERE fund_id =
>> > @fund_id
>> > AND short_code = 'FR_ROLL_IGNORE' AND object_type = 100
>> > )
>> > BEGIN
>> > UPDATE #fund_data
>> > SET open_shares = open_shares + rollup_activity
>> > ,open_shares_val = open_shares_val + rollup_activity_val
>> >
>> > DELETE #fund_data WHERE rollup_activity_val < 0
>> > UPDATE #fund_data SET rollup_activity = 0, rollup_activity_val
>> > = 0
>> > END
>> >
>> > UPDATE #fund_data
>> > SET transfer_activity_val = transfer_activity_val +
>> > exch_activity_val
>> > ,transfer_activity = transfer_activity + exch_activity
>> >
>> > UPDATE #fund_data SET exch_activity_val = 0, exch_activity = 0
>> >
>> > DELETE #fund_data WHERE open_shares < 0
>> >
>> > DELETE #fund_data
>> > WHERE open_shares = 0
>> > AND shares = 0
>> > AND sub_activity = 0
>> > AND rdp_activity = 0
>> > AND transfer_activity = 0
>> > AND rollup_activity = 0
>> > AND switch_activity = 0
>> > AND exch_activity = 0
>> > AND open_shares_val = 0
>> > AND shares_val = 0
>> > AND sub_activity_val = 0
>> > AND rdp_activity_val = 0
>> > AND transfer_activity_val = 0
>> > AND rollup_activity_val = 0
>> > AND switch_activity_val = 0
>> > AND exch_activity_val = 0
>> >
>> > ------------------------------------------------
>> > ---- FIND NAV INFORMATION FOR YR/PREV DATES ----
>> > ------------------------------------------------
>> > CREATE TABLE #date_info
>> > (
>> > series_id NUMERIC(10) NULL
>> > ,yr_min_date DATETIME NULL
>> > ,yr_nnav NUMERIC(16,6) NULL
>> > ,yr_gnav NUMERIC(16,6) NULL
>> > ,prev_min_date DATETIME NULL
>> > ,prev_nnav NUMERIC(16,6) NULL
>> > ,prev_gnav NUMERIC(16,6) NULL
>> > )
>> >
>> > CREATE INDEX x_dte ON #date_info( series_id, yr_min_date,
> prev_min_date )
>> >
>> > CREATE TABLE #date_info2
>> > (
>> > series_id NUMERIC(10) NULL
>> > ,yr_min_date DATETIME NULL
>> > ,yr_nnav NUMERIC(16,6) NULL
>> > ,yr_gnav NUMERIC(16,6) NULL
>> > ,prev_min_date DATETIME NULL
>> > ,prev_nnav NUMERIC(16,6) NULL
>> > ,prev_gnav NUMERIC(16,6) NULL
>> > )
>> >
>> > CREATE TABLE #seqnum
>> > (nav_date DATETIME NOT NULL
>> > ,max_seqnum NUMERIC(1) NULL
>> > )
>> >
>> > SELECT @year_date = (SELECT MIN(nav_date) FROM me_nav
>> > WHERE fund_id = @fund_id
>> > AND nav_date >= @year_date
>> > AND nav_date <= @stmt_date)
>> > ,@prev_date = (SELECT MIN(nav_date) FROM me_nav
>> > WHERE fund_id = @fund_id
>> > AND nav_date >= @prev_date
>> > AND nav_date <= @stmt_date)
>> >
>> > INSERT #seqnum
>> > SELECT nav_date, MAX(seqnum)
>> > FROM me_nav
>> > WHERE fund_id = @fund_id
>> > AND nav_date >= @year_date
>> > AND nav_date <= @stmt_date
>> > GROUP BY nav_date
>> >
>> > UPDATE #seqnum SET max_seqnum = 1
>> > WHERE nav_date = @stmt_date
>> >
>> > IF (SELECT method_id FROM fund WHERE fund_id = @fund_id) < 50 --EQ
>> > fund BEGIN
>> > DELETE #issue_transaction_id
>> >
>> > INSERT #issue_transaction_id
>> > SELECT DISTINCT issue_trans_id FROM #report_data
>> >
>> > EXEC subp_eq_ror_logic @fund_id, @stmt_date, @year_date
>> >
>> > UPDATE #date_info
>> > SET yr_nnav = me.nnav
>> > ,yr_gnav = me.gnav
>> > FROM me_nav me, #seqnum s
>> > WHERE me.fund_id = @fund_id
>> > AND me.nav_date = #date_info.yr_min_date
>> > AND s.nav_date = #date_info.yr_min_date
>> > AND me.seqnum = s.max_seqnum
>> >
>> > UPDATE #date_info
>> > SET prev_min_date = (SELECT MIN(bal_date)
>> > FROM balances b, trans t,#report_data
>> > r WHERE b.fund_id = @fund_id
>> > AND t.fund_id = @fund_id
>> > AND r.investor_id =
>> > #date_info.series_id
>> > AND r.issue_trans_id =
>> > b.issue_transaction_id
>> > AND r.issue_trans_id =
>> > t.transaction_id
>> > AND t.investor_id =
>> > #date_info.series_id
>> > AND b.bal_date >= @prev_date
>> > AND b.bal_date <= @stmt_date)
>> >
>> > UPDATE #date_info
>> > SET prev_nnav = me.nnav
>> > ,prev_gnav = me.gnav
>> > FROM me_nav me, #seqnum s
>> > WHERE me.fund_id = @fund_id
>> > AND me.nav_date = #date_info.prev_min_date
>> > AND s.nav_date = #date_info.prev_min_date
>> > AND me.seqnum = s.max_seqnum
>> >
>> >
>> > INSERT #date_info2
>> > SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
>> > d1.yr_gnav
>> > , d2.prev_min_date, d2.prev_nnav,
>> > d2.prev_gnav
>> > FROM #date_info d1
>> > ,#date_info d2
>> > WHERE d1.series_id = d2.series_id
>> > AND d1.yr_min_date = (SELECT MIN(yr_min_date)
>> > FROM #date_info d3
>> > WHERE d3.series_id = d1.series_id)
>> > AND d2.prev_min_date =(SELECT MIN(prev_min_date)
>> > FROM #date_info d3
>> > WHERE d3.series_id = d1.series_id)
>> >
>> > ----------------------------------
>> > ---- UPDATE MAIN RESULT TABLE ----
>> > ----------------------------------
>> > UPDATE #report_data
>> > SET prev_nnav = d.prev_nnav
>> > ,yr_nnav = d.yr_nnav
>> > ,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav
>> > ELSE
>> > d.prev_gnav END
>> > ,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav
>> > ELSE
>> > d.yr_gnav END
>> > FROM #date_info2 d
>> > WHERE d.series_id = #report_data.investor_id
>> >
>> > END
>> >
>> > ELSE
>> >
>> > BEGIN
>> > INSERT #date_info
>> > SELECT me.series_id, me.nav_date, MAX(me.nnav), MAX(me.gnav),
>> > NULL,
>> > NULL, NULL
>> > FROM me_nav me, #seqnum s
>> > WHERE me.fund_id = @fund_id
>> > AND me.seqnum = s.max_seqnum
>> > AND me.nav_date >= @year_date
>> > AND me.nav_date <= @stmt_date
>> > AND me.nav_date = s.nav_date
>> > GROUP BY me.series_id ,me.nav_date
>> >
>> > INSERT #date_info
>> > SELECT me.series_id, NULL, NULL, NULL, me.nav_date,
>> > MAX(me.nnav),
>> > MAX(me.gnav)
>> > FROM me_nav me, #seqnum s
>> > WHERE me.fund_id = @fund_id
>> > AND me.seqnum = s.max_seqnum
>> > AND me.nav_date >= @prev_date
>> > AND me.nav_date <= @stmt_date
>> > AND me.nav_date = s.nav_date
>> > GROUP BY me.series_id, me.nav_date
>> >
>> > INSERT #date_info2
>> > SELECT DISTINCT d1.series_id, d1.yr_min_date, d1.yr_nnav,
>> > d1.yr_gnav
>> > , d2.prev_min_date, d2.prev_nnav,
>> > d2.prev_gnav
>> > FROM #date_info d1
>> > ,#date_info d2
>> > WHERE d1.series_id = d2.series_id
>> > AND d1.yr_min_date = (SELECT MIN(yr_min_date)
>> > FROM #date_info d3
>> > WHERE d3.series_id = d1.series_id)
>> > AND d2.prev_min_date =(SELECT MIN(prev_min_date)
>> > FROM #date_info d3
>> > WHERE d3.series_id = d1.series_id)
>> >
>> > ----------------------------------
>> > ---- UPDATE MAIN RESULT TABLE ----
>> > ----------------------------------
>> > UPDATE #report_data
>> > SET prev_nnav = d.prev_nnav
>> > ,yr_nnav = d.yr_nnav
>> > ,prev_gnav = CASE WHEN d.prev_gnav = 0 THEN d.prev_nnav
>> > ELSE
>> > d.prev_gnav END
>> > ,yr_gnav = CASE WHEN d.yr_gnav = 0 THEN d.prev_nnav
>> > ELSE
>> > d.yr_gnav END
>> > FROM #date_info2 d
>> > WHERE d.series_id = #report_data.series_id
>> > END
>> >
>> > UPDATE #report_data
>> > SET gnav = me.gnav
>> > ,nnav = me.nnav
>> > FROM me_nav me
>> > WHERE me.nav_date = @stmt_date
>> > AND me.series_id = #report_data.series_id
>> > AND me.fund_id = @fund_id
>> > AND me.seqnum = 1 /* always want monthly nav for current date*/
>> >
>> > -- nnav is null if investor totally redeemed out at t+1
>> > UPDATE #report_data
>> > SET gnav = me.gnav
>> > ,nnav = me.nnav
>> > FROM me_nav me, #fund_data f
>> > WHERE me.nav_date = (SELECT MAX(bal_date) FROM balances b
>> > WHERE b.fund_id = @fund_id
>> > AND b.bal_date < @stmt_date
>> > AND b.bal_date >= @prev_date
>> > AND b.issue_transaction_id =
>> > #report_data.issue_trans_id
>> > AND b.shares = 0 )
>> > AND me.series_id = #report_data.series_id
>> > AND me.fund_id = @fund_id
>> > AND me.seqnum = 1
>> > AND #report_data.nnav IS NULL
>> > AND #report_data.issue_trans_id = f.issue_transaction_id
>> > AND f.shares_val = 0
>> >
>> > UPDATE #report_data
>> > SET eq_balance = isnull(b.sh_adj_ps, 0) * isnull(b.shares, 0)
>> > FROM #bal b, #fund_data fd
>> > WHERE b.issue_transaction_id = fd.issue_transaction_id
>> > AND b.issue_transaction_id = #report_data.issue_trans_id
>> > AND b.bal_date = @stmt_date
>> >
>> > DROP TABLE #date_info2
>> >
>> > UPDATE #report_data
>> > SET pctmonth = ROUND(((nnav - prev_nnav) / prev_nnav)*100,6)
>> > ,pctyear = ROUND(((nnav - yr_nnav) / yr_nnav)*100,6)
>> > ,g_beginbal = ROUND(ISNULL(prev_gnav*f.open_shares,0),2)
>> > ,g_value = ROUND(ISNULL(gnav*f.shares,0),2)
>> > ,net_profit = ROUND( ( ISNULL( ((nnav-gnav) * (f.open_shares
>> > + CASE WHEN
>> > @add_todate_subs = 1 THEN f.sub_activity ELSE 0 END
>> > + CASE WHEN
>> > @add_todate_redem = 1 THEN f.rdp_activity ELSE 0 END
>> > + CASE WHEN
>> > (@add_todate_trans_r + @add_todate_trans_s) > 0 THEN
>> > f.transfer_activity ELSE 0 END
>> > + CASE WHEN
>> > (@add_todate_roll_r + @add_todate_roll_s) > 0 THEN
>> > f.rollup_activity ELSE 0
>> > END) ) ,0)
>> > - ISNULL( ((prev_nnav-prev_gnav)
>> > * (f.open_shares + CASE WHEN
>> > (@add_todate_trans_r + @add_todate_trans_s) > 0 THEN
>> > f.transfer_activity ELSE 0 END)),0)
>> >
>> > )
>> > ,2)
>> > ,transshares = (f.open_shares - f.shares) * -1
>> > FROM #fund_data f
>> > WHERE f.issue_transaction_id = #report_data.issue_trans_id
>> > AND prev_nnav <> 0
>> > AND yr_nnav <> 0
>> >
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > -----
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > -----
>> > IF @use_ror_logic = 1 AND (SELECT method_id FROM fund WHERE fund_id
>> > = @fund_id) >= 50
>> > BEGIN
>> > INSERT #investor_ror
>> > SELECT DISTINCT investor_id, class_id, series_id, 0, 0, 0, 0
>> > FROM #report_data
>> >
>> > EXEC subp_get_investor_ror @fund_id, @year_date, @prev_date,
>> > @stmt_date
>> >
>> > IF EXISTS (SELECT DISTINCT 'FOUND' FROM #investor_ror)
>> > UPDATE #report_data
>> > SET pctmonth = 0
>> > ,pctyear = 0
>> > FROM fundinvestors fi
>> > WHERE fi.fund_id = @fund_id
>> > AND fi.investor_id = #report_data.investor_id
>> > AND fi.gp_investor <> 1
>> > --gp investor has only one series and special logic applies
>> >
>> > UPDATE #report_data
>> > SET pctmonth = i.mtd_ror
>> > ,pctyear = i.ytd_ror
>> > FROM #investor_ror i
>> > WHERE #report_data.investor_id = i.investor_id
>> > AND #report_data.class_id = i.class_id
>> > AND #report_data.series_id = i.series_id
>> > AND #report_data.pctmonth = 0
>> > AND #report_data.pctyear = 0
>> > END
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > -----
>> >
>> > SELECT @next_prev_date = ISNULL((SELECT MIN(nav_date) FROM me_nav
>> > WHERE fund_id = @fund_id AND
>> > nav_date >
>> > @prev_date),@stmt_date)
>> >
>> > UPDATE #report_data
>> > SET pctmonth = 0
>> > , pctyear = CASE WHEN @show_ytd_ror_t1_redem = 1 THEN
>> > ROUND(((prev_nnav - yr_nnav) / yr_nnav)*100,6) ELSE 0 END
>> > FROM trans t, redemptions r, #fund_data f, #bal b
>> > WHERE t.fund_id = @fund_id
>> > AND f.issue_transaction_id = #report_data.issue_trans_id
>> > AND r.issue_transaction_id = #report_data.issue_trans_id
>> > AND b.issue_transaction_id = #report_data.issue_trans_id
>> > AND b.bal_date = t.transaction_date
>> > AND b.shares = 0 -- Check for full redemption
>> > AND t.transaction_id = r.transaction_id
>> > AND t.transaction_date >= @prev_date
>> > AND t.transaction_date < @next_prev_date
>> > AND f.shares = 0
>> > AND f.shares_val = 0
>> > AND f.shares_val = ( SELECT SUM(ff.shares_val) FROM
>> > #fund_data
>> > ff, trans tt
>> > WHERE tt.fund_id =
>> > @fund_id
>> > AND tt.transaction_id =
>> > ff.issue_transaction_id
>> > AND tt.series_id =
>> > #report_data.series_id
>> > AND tt.investor_id =
>> > #report_data.investor_id)
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > -----
>>
>> ----------------------------------------------------------------------
>> ----
> --
>> > -----
>> >
>> > DECLARE c_name CURSOR FOR SELECT investor_id FROM #investors
>> > OPEN c_name
>> > FETCH c_name INTO @investor_id
>> > WHILE(@@sqlstatus=0)
>> > BEGIN
>> > SELECT @temp_iname = (SELECT name FROM investor WHERE
>> > investor_id =
>> > @investor_id)
>> > EXEC p_strip_name @temp_iname, @iname output, @first_name_first
>> >
>> > UPDATE #investors
>> > SET iname = @iname
>> > WHERE investor_id = @investor_id
>> >
>> > FETCH c_name INTO @investor_id
>> > END
>> > CLOSE c_name
>> > DEALLOCATE CURSOR c_name
>> >
>> > UPDATE #report_data
>> > SET iname = i.iname
>> > FROM #investors i
>> > WHERE i.investor_id = #report_data.investor_id
>> >
>> >
>> > DELETE #report_data
>> > FROM fund f, trans t
>> > WHERE f.fund_id = @fund_id
>> > AND f.report_statement_id = 603
>> > AND #report_data.issue_trans_id = t.activity_trans_id
>> > AND t.transaction_type = 2
>> > AND t.proceeds_entered > 0
>> >
>> > -------------------------------------------
>> > ------------ OUTPUT STATEMENTS ------------
>> > -------------------------------------------
>> >
>> > INSERT #out_result
>> > SELECT
>> > DISTINCT
>> > r.hid
>> > , r.series_id
>> > , r.contact_id
>> > , r.investor_id
>> > , r.issue_trans_id
>> > , r.invnum
>> > , r.g_beginbal
>> > , r.g_value
>> > , r.nnav
>> > , r.gnav
>> > , r.prev_nnav
>> > , r.prev_gnav
>> > , r.yr_nnav
>> > , r.yr_gnav
>> > , r.yr_shares
>> > , r.pctyear
>> > , r.pctmonth
>> > , r.net_profit
>> > , r.cur
>> > , r.transshares
>> > , r.method_id
>> > , r.iname
>> > , r.series_name
>> > , f.open_shares
>> > , f.open_shares_val
>> > , f.shares
>> > , f.shares_val
>> > , f.sub_activity
>> > , f.sub_activity_val
>> > , f.rdp_activity
>> > , f.rdp_activity_val
>> > , f.transfer_activity
>> > , f.transfer_activity_val
>> > , f.rollup_activity
>> > , f.rollup_activity_val
>> > , f.switch_activity
>> > , f.switch_activity_val
>> > , r.eq_balance
>> > FROM #report_data r
>> > ,#fund_data f
>> > WHERE f.issue_transaction_id = r.issue_trans_id
>> >
>> >
>> > IF @out_nav_info <> 0
>> > INSERT #nav_info
>> > SELECT
>> > series_id = series_id
>> > ,nav_date = yr_min_date
>> > ,nnav = yr_nnav
>> > ,gnav = yr_gnav
>> > FROM #date_info
>> > WHERE yr_min_date IS NOT NULL
>> >
>> > DROP TABLE #report_data
>> > DROP TABLE #fund_data
>> > DROP TABLE #date_info
>> >
>> > GO
>> >
>> >
>> > "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote
>> > in message news:369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com...
>> >> Please show us how you created the temporary tables. If you
>> >> enclosed the table name in doublequotes then you may have created
>> >> a permanent table that just happens to have a name beginning with
>> >> #. Look in SYSTABLE to be sure; if it's there, it's not a local
>> >> temporary table.
>> >>
>> >> On 19 Mar 2004 07:28:17 -0800, "Brett Morgan" <bmorgan@imsi.com>
>> >> wrote:
>> >>
>> >> >I've discovered a weird locking problem in a stored procedure we
>> >> >are
>> > using
>> >> >that's locking temp tables across connections. I am running
>> >> >Sybase
> ASA
>> >> >8.0.3, build 4322.
>> >> >
>> >> >The procedure creates some local temp tables, inserts, and
>> >> >updates
> into
>> > the
>> >> >tables then finally selects data from tables. If I run the
>> >> >procedure
>> >> in
>> > two
>> >> >different instances of DBISQL (autocommit off) the second
>> >> >execution
>> > hangs.
>> >> >If I commit on the first execution the second execution executes.
>> >> > I
>> >> ran
>> >> >sa_conn_info and saw that the first execution was blocking the
>> >> second. I
>> >> >couldn't figure out why since all the inserts/updates are done to
>> >> >temp tables.
>> >> >
>> >> >I then turned blocking off using, "set option blocking = off." I
> again
>> >> >executed the procedure in two instances of DBISQL. I received an
> error
>> >> >message for the second execution saying that a temp table is
>> >> >locked,
>> >> "ASA
>> >> >Error -210: User 'DBA' has the row in '#report_data' locked."
>> >> >
>> >> >It was my understanding that temp tables are exclusive to a
> connection,
>> >> >therefore why would they be locked across two connections? Any
>> >> >help
>> > would
>> >> >be much appreciated.
>> >> >
>> >> >Brett Morgan
>> >> >bmorgan@imsi.com
>> >> >
>> >>
>> >> --
>> >> SQL Anywhere Studio 9 Developer's Guide
>> >> Buy the book:
>> > http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> >> bcarter@risingroad.com
>> >> Mobile and Distributed Enterprise Database Applications
>> >> www.risingroad.com
>> >
>> >
>>
>>
>>
>> --
>> Peter Bumbulis
>> iAnywhere Solutions Engineering
>>
>> EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>>
>> SQL Anywhere Studio Supported Platforms and Support Status
>> http://my.sybase.com/detail?id=1002288
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Greg Fenton Posted on 2004-03-19 19:07:25.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 MultiZilla/1.6.2.0c
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: temp table locking
References: <405b1191$1@forums-1-dub> <369m501el1qinsmrvn556j2s1fbe3c5h23@4ax.com> <405b2bdc@forums-2-dub> <opr44gldlkgaovig@bumbulis-xp.sybase.com> <405b4130@forums-1-dub>
In-Reply-To: <405b4130@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: gfenton-xp.sybase.com
X-Original-NNTP-Posting-Host: gfenton-xp.sybase.com
Message-ID: <405b44ed$1@forums-1-dub>
Date: 19 Mar 2004 11:07:25 -0800
X-Trace: forums-1-dub 1079723245 10.25.100.120 (19 Mar 2004 11:07:25 -0800)
X-Original-Trace: 19 Mar 2004 11:07:25 -0800, gfenton-xp.sybase.com
Lines: 17
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2811
Article PK: 6283


Brett Morgan wrote:

> I tried that and got a syntax error that made now sense.
>

That may be telling...

Can you tell us what the particular error message is?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Peter Bumbulis Posted on 2004-03-19 16:13:27.0Z
Newsgroups: ianywhere.public.general
Subject: Re: temp table locking
References: <405b1191$1@forums-1-dub>
Message-ID: <opr4390kz8gaovig@bumbulis-xp.sybase.com>
From: "Peter Bumbulis" <bumbulis@ianywhere.com>
Organization: iAnywhere Solutions
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
User-Agent: Opera M2/7.50 (Win32, build 3613)
X-Original-NNTP-Posting-Host: bumbulis-xp.sybase.com
X-Original-Trace: 19 Mar 2004 08:13:23 -0800, bumbulis-xp.sybase.com
Lines: 52
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Mar 2004 08:13:24 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Mar 2004 08:13:27 -0800
X-Trace: forums-1-dub 1079712807 10.22.108.75 (19 Mar 2004 08:13:27 -0800)
X-Original-Trace: 19 Mar 2004 08:13:27 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2804
Article PK: 6275

There should be no locks placed on local temporary tables: if there are,
it's a bug. Submitting a bug report (hopefully with a small repro!)
should get it resolved in short order,

Peter

On 19 Mar 2004 07:28:17 -0800, Brett Morgan <bmorgan@imsi.com> wrote:

> I've discovered a weird locking problem in a stored procedure we are
> using
> that's locking temp tables across connections. I am running Sybase ASA
> 8.0.3, build 4322.
>
> The procedure creates some local temp tables, inserts, and updates into
> the
> tables then finally selects data from tables. If I run the procedure in
> two
> different instances of DBISQL (autocommit off) the second execution
> hangs.
> If I commit on the first execution the second execution executes. I ran
> sa_conn_info and saw that the first execution was blocking the second. I
> couldn't figure out why since all the inserts/updates are done to temp
> tables.
>
> I then turned blocking off using, "set option blocking = off." I again
> executed the procedure in two instances of DBISQL. I received an error
> message for the second execution saying that a temp table is locked, "ASA
> Error -210: User 'DBA' has the row in '#report_data' locked."
>
> It was my understanding that temp tables are exclusive to a connection,
> therefore why would they be locked across two connections? Any help
> would
> be much appreciated.
>
> Brett Morgan
> bmorgan@imsi.com
>
>

--
Peter Bumbulis
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Breck Carter [TeamSybase] Posted on 2004-03-19 17:06:59.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: temp table locking
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <d0am5058vda3e673lukn77l8po0k32veti@4ax.com>
References: <405b1191$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 19 Mar 2004 09:06:59 -0800
X-Trace: forums-1-dub 1079716019 64.7.134.118 (19 Mar 2004 09:06:59 -0800)
X-Original-Trace: 19 Mar 2004 09:06:59 -0800, bcarter.sentex.ca
Lines: 38
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2806
Article PK: 6277

Note also that you may create a temporary table with the same name as
a permanent table, which leads to enormous confusion as to which one
is "visible" at any given point in time.

On 19 Mar 2004 07:28:17 -0800, "Brett Morgan" <bmorgan@imsi.com>

wrote:

>I've discovered a weird locking problem in a stored procedure we are using
>that's locking temp tables across connections. I am running Sybase ASA
>8.0.3, build 4322.
>
>The procedure creates some local temp tables, inserts, and updates into the
>tables then finally selects data from tables. If I run the procedure in two
>different instances of DBISQL (autocommit off) the second execution hangs.
>If I commit on the first execution the second execution executes. I ran
>sa_conn_info and saw that the first execution was blocking the second. I
>couldn't figure out why since all the inserts/updates are done to temp
>tables.
>
>I then turned blocking off using, "set option blocking = off." I again
>executed the procedure in two instances of DBISQL. I received an error
>message for the second execution saying that a temp table is locked, "ASA
>Error -210: User 'DBA' has the row in '#report_data' locked."
>
>It was my understanding that temp tables are exclusive to a connection,
>therefore why would they be locked across two connections? Any help would
>be much appreciated.
>
>Brett Morgan
>bmorgan@imsi.com
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com