Yes we have tried for it and we need to add Transactions in the Stored Proc given belo atleast give me some Idea. Or sample which explains dos and donts it that will be enough.
Thanks a Lot,
Aleem Mohammad.
Print '<<< Verifying link server connectivity >>>'
IF NOT EXISTS(SELECT 1 FROM @@@LinkSvrname.@@@CatlogName.@@@SchemaName.TAX_DETAIL AS TAX_DETAIL WHERE 1= 2)
BEGIN
Print '<<< link server connectivity verified >>>'
END
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while connecting to the link server >>>'
GoTo ExitProcess
END
ExitProcess:
IF @ErrorCode <> 0
BEGIN
PRINT '<<< Please rerun the script after verifying the link server connectivity >>>'
END
--***********************************************************************************************************************
Print '<<< Drop physical temporary table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT]
Print '<<< Physical temporary table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] has been dropped >>>'
END
else
Print '<<< Physical temporary table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] does not exist to drop >>>'
GO
Print '<<< Create physical temporary table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] (
[Session_Ticket_Id] [varchar] (50) NOT NULL ,
[AccountId] [varchar] (12) NOT NULL ,
[AccountName] [varchar] (36) NULL ,
[Model_Num] [varchar] (12) NULL ,
[Model] [varchar] (50) NULL ,
[AccountantId] [smallint] NULL ,
[AccountantName] [varchar] (30) NULL ,
[Control_Id] [varchar] (4) NULL ,
[Market_Value] [float] NULL ,
[Realized_ShortTerm] [float] NULL ,
[Realized_LongTerm] [float] NULL ,
[Realized_Net] [float] NULL ,
[UnRealized_ShortTerm_Gain] [float] NULL ,
[UnRealized_ShortTerm_Loss] [float] NULL ,
[UnRealized_LongTerm_Gain] [float] NULL ,
[UnRealized_LongTerm_Loss] [float] NULL ,
[UnRealized_Net] [float] NULL ,
[Realized_Unrealized_Net_GL] [float] NULL ,
[RPT_Generation_Dt] [datetime] NULL ,
[Filtered] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] ADD
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_AccountName] DEFAULT ('') FOR [AccountName],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Model_Num] DEFAULT ('') FOR [Model_Num],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Model] DEFAULT ('') FOR [Model],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_AccountantName] DEFAULT ('') FOR [AccountantName],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Control_Id] DEFAULT ('') FOR [Control_Id],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Market_Value] DEFAULT (0) FOR [Market_Value],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Realized_ShortTerm] DEFAULT (0) FOR [Realized_ShortTerm],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Realized_LongTerm] DEFAULT (0) FOR [Realized_LongTerm],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Realized_Net] DEFAULT (0) FOR [Realized_Net],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_UnRealized_ShortTerm_Gain] DEFAULT (0) FOR [UnRealized_ShortTerm_Gain],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_UnRealized_ShortTerm_Loss] DEFAULT (0) FOR [UnRealized_ShortTerm_Loss],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_UnRealized_LongTerm_Gain] DEFAULT (0) FOR [UnRealized_LongTerm_Gain],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_UnRealized_LongTerm_Loss] DEFAULT (0) FOR [UnRealized_LongTerm_Loss],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_UnRealized_Net] DEFAULT (0) FOR [UnRealized_Net],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Realized_Unrealized_Net_GL] DEFAULT (0) FOR [Realized_Unrealized_Net_GL],
CONSTRAINT [DF_tbl_acct_RU_GAINLOSS_RPT_Filter] DEFAULT (0) FOR [Filtered],
CONSTRAINT [PK_tbl_acct_RU_GAINLOSS_RPT] PRIMARY KEY NONCLUSTERED
(
[Session_Ticket_Id],
[AccountId]
) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_acct_RU_GAINLOSS_RPT] ON [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT]([Session_Ticket_Id], [Filtered]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating physical temporary table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT]>>>'
END
Else
Print '<<< Physical temporary table [@@@SchemaName].[tbl_acct_RU_GAINLOSS_RPT] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop physical temporary table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT]
Print '<<< Physical temporary table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] has been dropped >>>'
END
else
Print '<<< Physical temporary table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] does not exist to drop >>>'
GO
Print '<<< Create physical temporary table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] (
[Session_Ticket_Id] [varchar] (50) NOT NULL ,
[AccountId] [varchar] (12) NOT NULL ,
[Property_num] [numeric](18, 0) NOT NULL ,
[Portfolio_Num] [smallint] NULL ,
[AccountName] [varchar] (36) NULL ,
[Model_num] [varchar] (12) NULL ,
[Model] [varchar] (50) NULL ,
[Asset_Short_Nm] [varchar] (36) NULL ,
[Qty_Units_held] [float] NULL ,
[Current_Prc] [float] NULL ,
[Federal_Tax_cost_Amt] [float] NULL ,
[Federal_Tax_Acquisition_Dt] [datetime] NULL ,
[Ticker] [varchar] (12) NULL ,
[Cusip] [varchar] (12) NULL ,
[RPT_Generation_Dt] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] ADD
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Model_num] DEFAULT ('') FOR [Model_num],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Model] DEFAULT ('') FOR [Model],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Asset_Short_Nm] DEFAULT ('') FOR [Asset_Short_Nm],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Qty_Units_held] DEFAULT (0) FOR [Qty_Units_held],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Current_Prc] DEFAULT (0) FOR [Current_Prc],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Federal_Tax_cost_Amt] DEFAULT (0) FOR [Federal_Tax_cost_Amt],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Federal_Tax_Acquisition_Dt] DEFAULT (0) FOR [Federal_Tax_Acquisition_Dt],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Ticker] DEFAULT ('') FOR [Ticker],
CONSTRAINT [DF_tbl_asset_U_GAINLOSS_RPT_Cusip] DEFAULT ('') FOR [Cusip]
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating physical temporary table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT]>>>'
END
Else
Print '<<< Physical temporary table [@@@SchemaName].[tbl_asset_U_GAINLOSS_RPT] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_get_taxlots_session_data] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_get_taxlots_session_data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_get_taxlots_session_data]
Print '<<< Procedure [@@@SchemaName].[p_mir_get_taxlots_session_data] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_get_taxlots_session_data] does not exist to drop >>>'
Print '<<< Create procedure [@@@SchemaName].[p_mir_get_taxlots_session_data] >>>'
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_get_taxlots_session_data
(
@pi_session_id VARCHAR(50) ,
@pi_filteraccount_name VARCHAR(36) = '' ,
@pi_filteraccountant_id VARCHAR(36) = '' ,
@pi_filtermodel_num VARCHAR(12) = '' ,
@pi_URNetAmtFrom NUMERIC(19,4) = -999999999999999 ,
@pi_URNetAmtTo NUMERIC(19,4) = 999999999999999 ,
@pi_AssetCriteria_Nm VARCHAR(50) = '' ,
@pi_AssetCriteria_Value VARCHAR(50) = '' ,
@pi_threshold_val INT ,
@pi_batch_date DATETIME
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @return_status INT
DECLARE @errorcode INT
DECLARE @cusip_id VARCHAR(12)
DECLARE @ticker_id VARCHAR(12)
DECLARE @record_cnt INT
SET @errorcode = 0
SET @return_status = 0
SET @record_cnt = 0
IF UPPER(LTRIM(RTRIM(@pi_AssetCriteria_Nm))) = 'CUSIP'
BEGIN
SET @cusip_id = @pi_AssetCriteria_Value
SET @ticker_id =''
END
ELSE IF UPPER(LTRIM(RTRIM(@pi_AssetCriteria_Nm))) = 'TICKER'
BEGIN
SET @cusip_id = ''
SET @ticker_id = @pi_AssetCriteria_Value
END
IF LTRIM(RTRIM(@pi_filtermodel_num)) =''
SET @pi_filtermodel_num = '%'+LTRIM(RTRIM(@pi_filtermodel_num))+'%'
ELSE
SET @pi_filtermodel_num = LTRIM(RTRIM(@pi_filtermodel_num))
IF LTRIM(RTRIM(@pi_filteraccountant_id)) =''
SET @pi_filteraccountant_id = '%'+LTRIM(RTRIM(@pi_filteraccountant_id))+'%'
ELSE
SET @pi_filteraccountant_id = LTRIM(RTRIM(@pi_filteraccountant_id))
IF @pi_URNetAmtFrom = -99999999999999
SET @pi_URNetAmtFrom = -99999999999999999999999999999999999999
IF @pi_URNetAmtTo = 99999999999999
SET @pi_URNetAmtTo = 99999999999999999999999999999999999999
IF EXISTS
(
SELECT 1
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
)
BEGIN
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET Filtered = 1 ,
Market_Value =0 ,
Realized_ShortTerm = 0,
Realized_LongTerm = 0,
Realized_Net = 0,
UnRealized_ShortTerm_Gain = 0,
UnRealized_ShortTerm_Loss = 0,
UnRealized_LongTerm_Gain = 0,
UnRealized_LongTerm_Loss = 0,
UnRealized_Net=0,
Realized_Unrealized_Net_GL=0
WHERE Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
SET @return_status = 1
print 'exists'
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET Filtered = '0'
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND (((((accountname like '%^' + LTRIM(RTRIM(@pi_filteraccount_name))+'%')
OR (accountname like '%^ ' + LTRIM(RTRIM(@pi_filteraccount_name))+'%')
OR (accountname like LTRIM(RTRIM(@pi_filteraccount_name))+'%' AND CHARINDEX('^', accountname) = 0)
OR accountname like '%'+LTRIM(RTRIM(@pi_filteraccount_name))+'%'))))
AND (IsNull(model_num,'') like @pi_filtermodel_num )
AND (IsNull(AccountantId,'') like @pi_filteraccountant_id)
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
SELECT @record_cnt = COUNT(1)
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND Filtered ='0'
IF @record_cnt > @pi_threshold_val
BEGIN
RETURN 2
print 'Threshold'
END
ELSE
BEGIN
EXEC p_mir_retrieve_acct_Taxlots @pi_session_id,@pi_batch_date,'N'
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET Filtered = '1'
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND NOT
(
(IsNull(UnRealized_Net,0) BETWEEN @pi_URNetAmtFrom AND @pi_URNetAmtTo)
) AND Filtered ='0'
IF LTRIM(RTRIM(@pi_AssetCriteria_Value)) <> ''
BEGIN
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET Filtered = '1'
WHERE Filtered='0' AND tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND NOT EXISTS
(
SELECT 1
FROM @@@LinkSvrname.@@@CatlogName.@@@SchemaName.TAX_DETAIL AS TAX_DETAIL,
@@@LinkSvrname.@@@CatlogName.@@@SchemaName.ASSET AS ASSET
WHERE (ISNULL(Ticker_Symbol_ID,'') LIKE '%'+LTRIM(RTRIM(@ticker_id))+'%'
AND ISNULL(CUSIP_ID,'') LIKE '%'+LTRIM(RTRIM(@cusip_id))+'%')
AND TAX_DETAIL.Property_Num = ASSET.Property_Num
AND tbl_acct_RU_GAINLOSS_RPT.accountid = TAX_DETAIL.account_id
)
END
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
SELECT [AccountId] ,
[AccountName] ,
ACCT_SORT_NAME = CASE CHARINDEX('^', AccountName) WHEN 0 THEN AccountName ELSE LTRIM(SUBSTRING(AccountName, CHARINDEX('^', AccountName)+1, 36)) END,
[Model_Num] ,
[Model],
CAST(ROUND(ISNULL(Market_Value,0),2) as numeric(18,4)) [Market_Value] ,
CAST(ROUND(ISNULL(Realized_ShortTerm,0),2) as numeric(18,4)) [Realized_ShortTerm],
CAST(ROUND(ISNULL(Realized_LongTerm,0),2) as numeric(18,4)) [Realized_LongTerm] ,
Realized_Net = CAST(ROUND(ISNULL(Realized_ShortTerm,0),2) + ROUND(ISNULL(Realized_LongTerm,0),2) as numeric(18,4)),
UnRealized_ShortTerm_Gain = CAST(ISNULL(UnRealized_ShortTerm_Gain,0) as numeric(18,4)) ,
UnRealized_ShortTerm_Loss = CAST(ISNULL(UnRealized_ShortTerm_Loss,0) as numeric(18,4)) ,
UnRealized_LongTerm_Gain = CAST(ISNULL(UnRealized_LongTerm_Gain,0) as numeric(18,4)) ,
UnRealized_LongTerm_Loss = CAST(ISNULL(UnRealized_LongTerm_Loss,0) as numeric(18,4)),
UnRealized_Net = CAST(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2) +ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2) as numeric(18,4)),
Net_GL = CAST(ROUND(ISNULL(Realized_ShortTerm,0),2) + ROUND(ISNULL(Realized_LongTerm,0),2) + ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2) +ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2) as numeric(18,4))
--[RPT_Generation_Dt]
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND Filtered ='0'
ORDER BY accountid asc--ACCT_SORT_NAME ASC
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
SELECT
CAST(SUM(ROUND(ISNULL(Market_Value,0),2))as numeric(18,4)) [Market_Value] ,
CAST(SUM(ROUND(ISNULL(Realized_ShortTerm,0),2))as numeric(18,4)) [Realized_ShortTerm],
CAST(SUM(ROUND(ISNULL(Realized_LongTerm,0),2)) as numeric(18,4)) [Realized_LongTerm] ,
Realized_Net = CAST(SUM(ROUND(ISNULL(Realized_ShortTerm,0),2)) + SUM(ROUND(ISNULL(Realized_LongTerm,0),2)) as numeric(18,4)),
UnRealized_ShortTerm_Gain = CAST(SUM(ISNULL(UnRealized_ShortTerm_Gain,0)) as numeric(18,4)),
UnRealized_ShortTerm_Loss = CAST(SUM(ISNULL(UnRealized_ShortTerm_Loss,0)) as numeric(18,4)),
UnRealized_LongTerm_Gain = CAST(SUM(ISNULL(UnRealized_LongTerm_Gain,0)) as numeric(18,4)),
UnRealized_LongTerm_Loss = CAST(SUM(ISNULL(UnRealized_LongTerm_Loss,0)) as numeric(18,4)),
UnRealized_Net = CAST(SUM(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2)) +SUM(ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2)) + SUM(ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2)) + + SUM(ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2)) as numeric(18,4)),
Net_GL = CAST(SUM(ROUND(ISNULL(Realized_ShortTerm,0),2)) + SUM(ROUND(ISNULL(Realized_LongTerm,0),2)) + SUM(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2)) +SUM(ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2)) + SUM(ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2)) + + SUM(ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2)) as numeric(18,4))
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND Filtered ='0'
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
END
END
ELSE
BEGIN
print 'Not exisits'
SET @return_status = 0
END
RETURN @return_status
errhandler:
RAISERROR('Unable to retrieve taxlot session data', 16, 1)
RETURN @return_status
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating procedure [@@@SchemaName].[p_mir_get_taxlots_session_data]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_get_taxlots_session_data] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_insert_each_acctparams] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_insert_each_acctparams]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_insert_each_acctparams]
Print '<<< Procedure [@@@SchemaName].[p_mir_insert_each_acctparams] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_insert_each_acctparams] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_insert_each_acctparams] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_insert_each_acctparams
(
@pi_session_id VARCHAR(50),
@pi_accountlist VARCHAR(6000)
)
AS
BEGIN
DECLARE @comaloc INT
DECLARE @fromloc INT
DECLARE @substr VARCHAR(50)
DECLARE @flag CHAR(1)
DECLARE @errorcode INT
SET @errorcode =0
SET @fromloc = 1
SET @flag = 'T'
WHILE @flag = 'T'
BEGIN
SELECT @comaloc=CHARINDEX(',', @pi_accountlist, @fromloc)
IF @comaloc > 0
BEGIN
SELECT @substr = SUBSTRING(@pi_accountlist, @fromloc, @comaloc-@fromloc)
SET @fromloc = @comaloc + 1
IF @substr <> ''
BEGIN
INSERT INTO tbl_acct_RU_GAINLOSS_RPT (Session_Ticket_Id,AccountId)
VALUES (@pi_session_id,LTRIM(RTRIM(@substr)))
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO errhandler
END
END
ELSE
BEGIN
SELECT @substr = SUBSTRING(@pi_accountlist, @fromloc, LEN(@pi_accountlist)-@fromloc+1)
IF @substr <> ''
BEGIN
INSERT INTO tbl_acct_RU_GAINLOSS_RPT (Session_Ticket_Id,AccountId)
VALUES (@pi_session_id,LTRIM(RTRIM(@substr)))
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO errhandler
END
SET @flag = 'F'
END
END
return
errhandler:
-- RAISERROR('Unable to INSERT ACCOUNT LIST', 16, 1)
RETURN @ErrorCode
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_insert_each_acctparams]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_insert_each_acctparams] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_insert_acct_list] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_insert_acct_list]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_insert_acct_list]
Print '<<< Procedure [@@@SchemaName].[p_mir_insert_acct_list] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_insert_acct_list] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_insert_acct_list] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_insert_acct_list
(
@pi_session_id VARCHAR(50),
@pi_accountlist_1 VARCHAR(6000),
@pi_accountlist_2 VARCHAR(6000) = '',
@pi_accountlist_3 VARCHAR(6000) = '',
@pi_accountlist_4 VARCHAR(6000) = '',
@pi_accountlist_5 VARCHAR(6000) = ''
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @record_cnt INT
DECLARE @return_status INT
DECLARE @errorcode INT
SET @errorcode = 0
SET @return_status = 0
--Call SP p_mir_insert_each_acctparams to insert the parameters 1
EXECUTE @return_status= p_mir_insert_each_acctparams @pi_session_id,@pi_accountlist_1
IF @return_status <> 0 GOTO errhandler
IF LTRIM(RTRIM(@pi_accountlist_2)) <> ''
BEGIN
--Call SP p_mir_insert_each_acctparams to insert the parameters 2
EXECUTE @return_status = p_mir_insert_each_acctparams @pi_session_id,@pi_accountlist_2
IF @return_status <> 0 GOTO errhandler
END
IF LTRIM(RTRIM(@pi_accountlist_3)) <> ''
BEGIN
--Call SP p_mir_insert_each_acctparams to insert the parameters 3
EXECUTE @return_status = p_mir_insert_each_acctparams @pi_session_id,@pi_accountlist_3
IF @return_status <> 0 GOTO errhandler
END
IF LTRIM(RTRIM(@pi_accountlist_4)) <> ''
BEGIN
--Call SP p_mir_insert_each_acctparams to insert the parameters 4
EXECUTE @return_status= p_mir_insert_each_acctparams @pi_session_id,@pi_accountlist_4
IF @return_status <> 0 GOTO errhandler
END
IF LTRIM(RTRIM(@pi_accountlist_5)) <> ''
BEGIN
--Call SP p_mir_insert_each_acctparams to insert the parameters 5
EXECUTE @return_status= p_mir_insert_each_acctparams @pi_session_id,@pi_accountlist_5
IF @return_status <> 0 GOTO errhandler
END
DELETE
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND EXISTS
(
SELECT 1
FROM accounts
WHERE tbl_acct_RU_GAINLOSS_RPT.accountid = accounts.account_id
AND control_id IN
('0412',
'0361',
'0410',
'0364',
'0441',
'0442',
'0394',
'0395',
'0396',
'0444'
)
)
SELECT @record_cnt = COUNT(1)
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
COMMIT TRANSACTION
RETURN @record_cnt
errhandler:
RAISERROR('Unable to INSERT ACCOUNT LIST', 16, 1)
ROLLBACK TRANSACTION
RETURN @return_status
END
Go
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_insert_acct_list]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_insert_acct_list] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_retrieve_acct_Taxlots]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots]
Print '<<< Procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_retrieve_acct_Taxlots
(
@pi_session_id VARCHAR(50),
@pi_batch_date DATETIME,
@pi_display_mode CHAR(1) = 'Y'
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @return_status INT
DECLARE @errorcode INT
SET @pi_batch_date = CONVERT(char(10),@pi_batch_date,101)
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET market_value = tbl_calculated_values.market_value ,
Realized_ShortTerm = tbl_account_balance.Fiscal_Year_Fed_ST_Gain_Amt + tbl_account_balance.Fiscal_Year_Fed_ST_Loss_Amt ,
Realized_LongTerm = tbl_account_balance.Fiscal_Year_Fed_LT_Gain_Amt + tbl_account_balance.Fiscal_Year_Fed_LT_Loss_Amt ,
UnRealized_ShortTerm_Gain= tbl_calculated_values.UnRealized_ShortTerm_Gain ,
UnRealized_ShortTerm_Loss= tbl_calculated_values.UnRealized_ShortTerm_Loss ,
UnRealized_LongTerm_Gain= tbl_calculated_values.UnRealized_LongTerm_Gain ,
UnRealized_LongTerm_Loss= tbl_calculated_values.UnRealized_LongTerm_Loss ,
UnRealized_Net = tbl_calculated_values.UnRealized_ShortTerm_Gain + tbl_calculated_values.UnRealized_ShortTerm_Loss + tbl_calculated_values.UnRealized_LongTerm_Gain+tbl_calculated_values.UnRealized_LongTerm_Loss
FROM tbl_acct_RU_GAINLOSS_RPT LEFT OUTER JOIN
(
SELECT TAX_DETAIL.Account_ID,
SUM(TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc) as Market_value,
SUM(
CASE when DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) > @pi_batch_date then
CASE when (TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt >= 0 then
(TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt
Else
0
End
else
0
end) as UnRealized_ShortTerm_Gain,
SUM(
CASE when DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) > @pi_batch_date then
CASE When (TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt < 0 then
(TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt
Else
0
End
else
0
end) as UnRealized_ShortTerm_loss,
SUM(
CASE when DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) <= @pi_batch_date then
CASE when (TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt >= 0 then
(TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt
Else
0
End
else
0
end) as UnRealized_LongTerm_Gain,
SUM(
CASE when DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) <= @pi_batch_date then
CASE when (TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt < 0 then
(TAX_DETAIL.Shares_Par_Value_Qty*Current_Prc)-TAX_DETAIL.Federal_Tax_cost_Amt
Else
0
End
else
0
end) as UnRealized_LongTerm_Loss
FROM @@@LinkSvrname.@@@CatlogName.@@@SchemaName.TAX_DETAIL AS TAX_DETAIL,
@@@LinkSvrname.@@@CatlogName.@@@SchemaName.ASSET AS ASSET
WHERE TAX_DETAIL.Property_Num = ASSET.Property_Num
AND TAX_DETAIL.Unique_Asset_fl = 0
Group BY TAX_DETAIL.Account_ID
) AS tbl_calculated_values
ON tbl_acct_RU_GAINLOSS_RPT.AccountId = tbl_calculated_values.account_id
LEFT OUTER JOIN @@@LinkSvrname.@@@CatlogName.@@@SchemaName.ACCOUNT_BALANCE as tbl_account_balance
ON tbl_acct_RU_GAINLOSS_RPT.AccountId = tbl_account_balance.account_id
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND Filtered = 0
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
IF UPPER(LTRIM(RTRIM(@pi_display_mode))) ='Y'
BEGIN
SELECT [AccountId] ,
[AccountName] ,
ACCT_SORT_NAME = CASE CHARINDEX('^', AccountName) WHEN 0 THEN AccountName ELSE LTRIM(SUBSTRING(AccountName, CHARINDEX('^', AccountName)+1, 36)) END,
[Model_Num] ,
[Model],
CAST(ROUND(ISNULL(Market_Value,0),2) as numeric(18,4)) [Market_Value] ,
CAST(ROUND(ISNULL(Realized_ShortTerm,0),2) as numeric(18,4)) [Realized_ShortTerm],
CAST(ROUND(ISNULL(Realized_LongTerm,0),2) as numeric(18,4)) [Realized_LongTerm] ,
Realized_Net = CAST(ROUND(ISNULL(Realized_ShortTerm,0),2) + ROUND(ISNULL(Realized_LongTerm,0),2) as numeric(18,4)) ,
UnRealized_ShortTerm_Gain = CAST(ISNULL(UnRealized_ShortTerm_Gain,0) as numeric(18,4)),
UnRealized_ShortTerm_Loss = CAST(ISNULL(UnRealized_ShortTerm_Loss,0) as numeric(18,4)) ,
UnRealized_LongTerm_Gain = CAST(ISNULL(UnRealized_LongTerm_Gain,0) as numeric(18,4)),
UnRealized_LongTerm_Loss = CAST(ISNULL(UnRealized_LongTerm_Loss,0) as numeric(18,4)),
UnRealized_Net = CAST(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2) +ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2) as numeric(18,4)),
Net_GL = CAST(ROUND(ISNULL(Realized_ShortTerm,0),2) + ROUND(ISNULL(Realized_LongTerm,0),2) + ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2) +ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2) + ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2) as numeric(18,4))--,
--[RPT_Generation_Dt]
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
ORDER BY accountid asc--ACCT_SORT_NAME ASC
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
SELECT
CAST(SUM(ROUND(ISNULL(Market_Value,0),2)) as numeric(18,4)) [Market_Value] ,
CAST(SUM(ROUND(ISNULL(Realized_ShortTerm,0),2)) as numeric(18,4)) [Realized_ShortTerm],
CAST(SUM(ROUND(ISNULL(Realized_LongTerm,0),2)) as numeric(18,4)) [Realized_LongTerm] ,
Realized_Net = CAST(SUM(ROUND(ISNULL(Realized_ShortTerm,0),2)) + SUM(ROUND(ISNULL(Realized_LongTerm,0),2)) as numeric(18,4)),
UnRealized_ShortTerm_Gain = CAST(SUM(ISNULL(UnRealized_ShortTerm_Gain,0)) as numeric(18,4)),
UnRealized_ShortTerm_Loss = CAST(SUM(ISNULL(UnRealized_ShortTerm_Loss,0)) as numeric(18,4)),
UnRealized_LongTerm_Gain = CAST(SUM(ISNULL(UnRealized_LongTerm_Gain,0)) as numeric(18,4)),
UnRealized_LongTerm_Loss = CAST(SUM(ISNULL(UnRealized_LongTerm_Loss,0)) as numeric(18,4)),
UnRealized_Net = CAST(SUM(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2)) +SUM(ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2)) + SUM(ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2)) + + SUM(ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2)) as numeric(18,4)),
Net_GL = CAST(SUM(ROUND(ISNULL(Realized_ShortTerm,0),2)) + SUM(ROUND(ISNULL(Realized_LongTerm,0),2)) + SUM(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2)) +SUM(ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2)) + SUM(ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2)) + + SUM(ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2))as numeric(18,4))
FROM tbl_acct_RU_GAINLOSS_RPT
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
END
RETURN 0
errhandler:
RAISERROR('Unable to retrieve taxlot data', 16, 1)
-- ROLLBACK TRANSACTION
RETURN 1
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_retrieve_acct_Taxlots] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_retrieve_asset_Taxlots] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_retrieve_asset_Taxlots]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_retrieve_asset_Taxlots]
Print '<<< Procedure [@@@SchemaName].[p_mir_retrieve_asset_Taxlots] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_retrieve_asset_Taxlots] does not exist to drop >>>'
GO
CREATE PROCEDURE @@@SchemaName.p_mir_retrieve_asset_Taxlots
(
@pi_session_id VARCHAR(50),
@pi_account_num VARCHAR(12),
@pi_batch_date DATETIME
)
AS
BEGIN
DECLARE @return_status INT
DECLARE @errorcode INT
SET @pi_batch_date = CONVERT(char(10),@pi_batch_date,101)
DELETE
FROM tbl_asset_U_GAINLOSS_RPT
WHERE Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
INSERT INTO tbl_asset_U_GAINLOSS_RPT
SELECT @pi_session_id AS Session_Ticket_Id,
tbl_accounts.account_id,
TAX_DETAIL.Property_Num,
TAX_DETAIL.Portfolio_Num,
ISNULL(tbl_accounts.account_short_nm,'') AccountName,
ISNULL(Model_num,'') AS model_num,
'' Model,
ASSET.Asset_Short_Nm Asset_Short_Nm,
TAX_DETAIL.Shares_Par_Value_Qty Qty_Units_held,
ASSET.Current_Prc Current_Prc,
TAX_DETAIL.Federal_Tax_cost_Amt,
Federal_Tax_Acquisition_Dt,
ASSET.Ticker_Symbol_ID,
ASSET.CUSIP_ID,
getDate() RPT_Generation_Dt
FROM accounts tbl_accounts,
@@@LinkSvrname.@@@CatlogName.@@@SchemaName.TAX_DETAIL AS TAX_DETAIL,
@@@LinkSvrname.@@@CatlogName.@@@SchemaName.ASSET AS ASSET
WHERE tbl_accounts.account_id = LTRIM(RTRIM(@pi_account_num))
AND tbl_accounts.account_id = TAX_DETAIL.account_ID
AND TAX_DETAIL.Property_Num = ASSET.Property_Num
AND TAX_DETAIL.Unique_Asset_fl = 0
UPDATE tbl_asset_U_GAINLOSS_RPT
SET Model=ISNULL(model_description_1,'')
FROM investment_model
WHERE AccountId =LTRIM(RTRIM(@pi_account_num))
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND tbl_asset_U_GAINLOSS_RPT.model_num = investment_model.model_num
--select * from tbl_asset_U_GAINLOSS_RPT where session_ticket_id ='testjitensession1'
DECLARE @tbl_calculated_asset_data TABLE(
[AccountId] [varchar] (12) NULL ,
[Property_num] [numeric](18, 0) NULL ,
[AccountName] [varchar] (36) NULL ,
[Model_num] [varchar] (12) NULL ,
[Model] [varchar] (50) NULL ,
[Asset_Short_Nm] [varchar] (36) NULL ,
[Qty_Units_held] [float] NULL ,
[Market_Value] [float] NULL ,
[Ticker] [varchar] (12) NULL ,
[Cusip] [varchar] (12) NULL ,
[UnRealized_ShortTerm_Gain] [float] NULL ,
[UnRealized_ShortTerm_loss] [float] NULL ,
[UnRealized_LongTerm_gain] [float] NULL ,
[UnRealized_LongTerm_loss] [float] NULL ,
[UnRealized_Net] [float] NULL
)
INSERT INTO @tbl_calculated_asset_data
SELECT distinct select_tbl.AccountId,
select_tbl.Property_num,
ISNULL(AccountName,'') AccountName,
ISNULL(Model_num,'') Model_num,
ISNULL(Model,'') Model,
ISNULL(Asset_Short_Nm,'') Asset_Short_Nm,
tbl_market_val.Qty_Units_held,
tbl_market_val.Market_Value,
ISNULL(Ticker,'') Ticker,
ISNULL(Cusip,'') Cusip,
ISNULL(UnRealized_ShortTerm_Gain,0) UnRealized_ShortTerm_Gain,
ISNULL(UnRealized_ShortTerm_loss,0) UnRealized_ShortTerm_loss,
ISNULL(UnRealized_LongTerm_gain,0) UnRealized_LongTerm_gain,
ISNULL(UnRealized_LongTerm_loss,0) UnRealized_LongTerm_loss,
ISNULL(UnRealized_ShortTerm_Gain,0) + ISNULL(UnRealized_ShortTerm_loss,0) + ISNULL(UnRealized_LongTerm_gain,0) + ISNULL(UnRealized_LongTerm_loss,0) Net_UnRealized
FROM tbl_asset_U_GAINLOSS_RPT AS select_tbl
LEFT OUTER JOIN (SELECT AccountId AS AccountId,
Property_Num AS Property_Num,
SUM(Qty_Units_held) AS Qty_Units_held,
SUM(Qty_Units_held*Current_Prc) AS Market_value
FROM tbl_asset_U_GAINLOSS_RPT
WHERE Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
GROUP BY AccountId,Property_Num
HAVING AccountID =@pi_account_num) tbl_market_val
ON select_tbl.AccountId = tbl_market_val.AccountId
AND select_tbl.Property_Num = tbl_market_val.Property_Num
LEFT OUTER JOIN (SELECT AccountId AS AccountId,
Property_Num Property_Num,
SUM((Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt) AS UnRealized_ShortTerm_Gain
FROM tbl_asset_U_GAINLOSS_RPT
WHERE (Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt >= 0
AND DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) > @pi_batch_date
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
GROUP BY AccountId,Property_Num
HAVING AccountID =@pi_account_num) tbl_short_term_gain
ON select_tbl.AccountId = tbl_short_term_gain.AccountId
AND select_tbl.Property_Num = tbl_short_term_gain.Property_Num
LEFT OUTER JOIN (SELECT AccountId AS AccountId,
Property_Num Property_Num,
SUM((Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt) AS UnRealized_ShortTerm_loss
FROM tbl_asset_U_GAINLOSS_RPT
WHERE (Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt < 0
AND DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) > @pi_batch_date
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
GROUP BY AccountId,Property_Num
HAVING AccountID =@pi_account_num) tbl_short_term_loss
ON select_tbl.AccountId = tbl_short_term_loss.AccountId
AND select_tbl.Property_Num = tbl_short_term_loss.Property_Num
LEFT OUTER JOIN (SELECT AccountId AS AccountId,
Property_Num Property_Num,
SUM((Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt) AS UnRealized_LongTerm_gain
FROM tbl_asset_U_GAINLOSS_RPT
WHERE (Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt >= 0
AND DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) <= @pi_batch_date
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
GROUP BY AccountId,Property_Num
HAVING AccountID =@pi_account_num) tbl_long_term_gain
ON select_tbl.AccountId = tbl_long_term_gain.AccountId
AND select_tbl.Property_Num = tbl_long_term_gain.Property_Num
LEFT OUTER JOIN (SELECT AccountId AS AccountId,
Property_Num Property_Num,
SUM((Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt) AS UnRealized_LongTerm_loss
FROM tbl_asset_U_GAINLOSS_RPT
WHERE (Qty_Units_held*Current_Prc)-Federal_Tax_cost_Amt < 0
AND DATEADD(dd, 365, Federal_Tax_Acquisition_Dt) <= @pi_batch_date
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
GROUP BY AccountId,Property_Num
HAVING AccountID =@pi_account_num) tbl_long_term_loss
ON select_tbl.AccountId = tbl_long_term_loss.AccountId
AND select_tbl.Property_Num = tbl_long_term_loss.Property_Num
WHERE Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
SELECT DISTINCT AccountId,
Property_num,
ISNULL(AccountName,'') AccountName,
ISNULL(Model_num,'') Model_num,
ISNULL(Model,'') Model,
ISNULL(Asset_Short_Nm,'') Asset_Short_Nm,
CAST(ROUND(ISNULL(Qty_Units_held,0) ,4) as numeric(18,4)) Qty_Units_held,
CAST(ROUND(ISNULL(Market_Value,0),2) as numeric(18,4)) Market_Value,
ISNULL(Ticker,'') Ticker,
ISNULL(Cusip,'') Cusip,
CAST(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2) AS numeric (18,4)) UnRealized_ShortTerm_Gain,
CAST(ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2) AS numeric (18,4)) UnRealized_ShortTerm_Loss,
CAST(ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2) AS numeric (18,4)) UnRealized_LongTerm_Gain,
CAST(ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2) AS numeric (18,4)) UnRealized_LongTerm_Loss,
CAST(ROUND(ISNULL(UnRealized_Net,0),2) AS numeric (18,4)) UnRealized_Net
FROM @tbl_calculated_asset_data
ORDER BY Asset_Short_Nm asc
SELECT CAST(ROUND(SUM(ISNULL(Qty_Units_held,0)), 4) as numeric(18,4)) Qty_Units_held,
CAST(SUM(ROUND(ISNULL(Market_Value,0),2)) as numeric(18,4))Market_Value,
CAST(SUM(ROUND(ISNULL(UnRealized_ShortTerm_Gain,0),2)) as numeric(18,4)) UnRealized_ShortTerm_Gain,
CAST(SUM(ROUND(ISNULL(UnRealized_ShortTerm_Loss,0),2)) as numeric(18,4)) UnRealized_ShortTerm_Loss,
CAST(SUM(ROUND(ISNULL(UnRealized_LongTerm_Gain,0),2)) as numeric(18,4)) UnRealized_LongTerm_Gain,
CAST(SUM(ROUND(ISNULL(UnRealized_LongTerm_Loss,0),2)) as numeric(18,4)) UnRealized_LongTerm_Loss,
CAST(SUM(ROUND(ISNULL(UnRealized_Net,0),2)) as numeric(18,4)) UnRealized_Net
FROM @tbl_calculated_asset_data
RETURN 0
errhandler:
RAISERROR('Unable to retrieve asset taxlot data', 16, 1)
-- ROLLBACK TRANSACTION
RETURN 1
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_retrieve_asset_Taxlots]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_retrieve_asset_Taxlots] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_getModelInfo] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_getModelInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_getModelInfo]
Print '<<< Procedure [@@@SchemaName].[p_mir_getModelInfo] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_getModelInfo] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_getModelInfo] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_getModelInfo
(
@pi_session_id VARCHAR(50)
)
AS
BEGIN
DECLARE @errorcode INT
SET @errorcode =0
SELECT
DISTINCT MODEL_NUM,
LTRIM(RTRIM(MODEL)) + ' ('+ LTRIM(RTRIM(MODEL_NUM)) +')' as MODEL
FROM
tbl_acct_RU_GAINLOSS_RPT
WHERE
MODEL IS NOT NULL
AND MODEL <> ''
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
ORDER BY MODEL asc
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO errhandler
RETURN 0
errhandler:
RETURN @ErrorCode
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_getModelInfo]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_getModelInfo] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_getODR_status] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_getODR_status]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_getODR_status]
Print '<<< Procedure [@@@SchemaName].[p_mir_getODR_status] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_getODR_status] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_getODR_status] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_getODR_status
AS
BEGIN
DECLARE @errorcode INT
SELECT Status
FROM @@@LinkSvrname.@@@CatlogName.@@@SchemaName.DB_STATUS
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
RETURN 0
errhandler:
RAISERROR('Unable to retrieve ODR status', 16, 1)
RETURN 1
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_getODR_status]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_getODR_status] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_CleanupTmpTable] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_CleanupTmpTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_CleanupTmpTable]
Print '<<< Procedure [@@@SchemaName].[p_mir_CleanupTmpTable] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_CleanupTmpTable] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_CleanupTmpTable] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_CleanupTmpTable
(
@pi_session_id VARCHAR(50) = ''
)
AS
BEGIN
set nocount on
DECLARE @errorcode INT
SET @errorcode =0
IF LTRIM(RTRIM(@pi_session_id)) <> ''
BEGIN
DELETE FROM tbl_acct_RU_GAINLOSS_RPT WHERE
tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
DELETE FROM tbl_asset_U_GAINLOSS_RPT WHERE
tbl_asset_U_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
END
else
BEGIN
TRUNCATE TABLE tbl_acct_RU_GAINLOSS_RPT
TRUNCATE TABLE tbl_asset_U_GAINLOSS_RPT
END
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO errhandler
errhandler:
return @ErrorCode
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_CleanupTmpTable]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_CleanupTmpTable] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_UpdatePrimaryFields]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields]
Print '<<< Procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE @@@SchemaName.p_mir_UpdatePrimaryFields
(
@pi_session_id VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @return_status INT
DECLARE @errorcode INT
SET @errorcode = 0
SET @return_status = 0
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET AccountName = ISNULL(account_short_nm,''),
tbl_acct_RU_GAINLOSS_RPT.Model_Num = ISNULL(accounts.model_num,''),
Model = ISNULL(model_description_1,''),
Control_Id = ISNULL(ACCOUNTS.control_id,''),
RPT_generation_dt = getDate()
FROM ACCOUNTS LEFT OUTER JOIN investment_model
ON ACCOUNTS.model_num = investment_model.model_num
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
AND tbl_acct_RU_GAINLOSS_RPT.AccountId = accounts.account_id
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
UPDATE tbl_acct_RU_GAINLOSS_RPT
SET AccountantId = accounts.Accountant_Cd,
AccountantName = ISNULL(accountant.Accountant_Nm,'')
FROM tbl_acct_RU_GAINLOSS_RPT INNER JOIN @@@LinkSvrname.@@@CatlogName.@@@SchemaName.ACCOUNT accounts
ON tbl_acct_RU_GAINLOSS_RPT.AccountId = accounts.account_id
INNER JOIN @@@LinkSvrname.@@@CatlogName.@@@SchemaName.ACCOUNTANT accountant
ON accounts.Accountant_Cd = accountant.Accountant_Cd
WHERE tbl_acct_RU_GAINLOSS_RPT.Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
SET @errorcode = @@ERROR
IF @errorcode <> 0 GOTO errhandler
RETURN 1
errhandler:
RAISERROR('Unable to update master fields', 16, 1)
ROLLBACK TRANSACTION
RETURN 0
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_UpdatePrimaryFields] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--***********************************************************************************************************************
Print '<<< Drop procedure [@@@SchemaName].[p_mir_getAccountantInfo] if it exists >>>'
if exists (select 1 from @@@SchemaName.sysobjects where id = object_id(N'[@@@SchemaName].[p_mir_getAccountantInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
drop procedure [@@@SchemaName].[p_mir_getAccountantInfo]
Print '<<< Procedure [@@@SchemaName].[p_mir_getAccountantInfo] has been dropped >>>'
END
else
Print '<<< Procedure [@@@SchemaName].[p_mir_getAccountantInfo] does not exist to drop >>>'
GO
Print '<<< Create procedure [@@@SchemaName].[p_mir_getAccountantInfo] >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [@@@SchemaName].p_mir_getAccountantInfo
(
@pi_session_id VARCHAR(50)
)
AS
BEGIN
DECLARE @errorcode INT
SET @errorcode =0
SELECT
DISTINCT AccountantId,
AccountantName
FROM
tbl_acct_RU_GAINLOSS_RPT
WHERE
AccountantId IS NOT NULL
AND AccountantId <> ''
AND Session_Ticket_Id = LTRIM(RTRIM(@pi_session_id))
ORDER BY AccountantName asc
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0 GOTO errhandler
RETURN 0
errhandler:
RETURN @ErrorCode
END
GO
DECLARE @ErrorCode INTEGER
SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
Print '<<< Error occurred while creating stored procedure [@@@SchemaName].[p_mir_getAccountantInfo]>>>'
END
Else
Print '<<< Procedure [@@@SchemaName].[p_mir_getAccountantInfo] has been created >>>'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
get the ISNULL to work - even though I prefer the look of that syntax as it is easier to read - It kept telling me that '@SalesOrderID: this input parameter cannot be converted. This does have to be defined as an int - so Im assumming the ISNULL cannot be used in this instance, but I will remember for the future.