/****** Object:  StoredProcedure [dbo].[spGetGLbyAccount]    Script Date: 07/10/2009 11:09:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- MFA Application Development Created Stored Procedure
-- Author:		James Stewart
-- Create date: 1/12/2009
-- Description:	GL by Account.  Returns transactions by year 
--              and period.  If a transaction has no activity the beginning and 
--              ending balance is still reported.  We create a temporary table. 
--              on the first pass the beginning balance is inserted.  On the 
--              second pass the detail records are inserted.
-- Dependancies:
--		Databases:
--			DBSglep, DBSjepc
--		Tables:
--			dbo.ldr_acct_bal, dbo.posted_jrnl_line, dbo.ldr_acct, dbo.ldr_entity_policy 
--		Views:
--			
--		Functions:
--			
--
-- ====================================================
-- Consumers
--		Reports: General Ledger by Account
--			
--		Application \ Form:
-- 
-- ====================================================
-- Modifications:
-- Date: /Developer: / Mod: 
-- 4/21/2009 James S. changed ldr_entity_id to line_ldr_entity_id
-- 6/8/2009 James s. Added Macro4 and year function	
-- 7/8/2009 James S.  Added end of month date
-- 1-25-2013  Brett Phipps.  Refactored queries to make the sql dynamic to improve performance
-- ====================================================
ALTER PROCEDURE [dbo].[spGetGLbyAccount] 
	@Date smallint,
	@per tinyint,
	@entity char(200) = null,
	@account char(500) = null,
	@div char(200) = null
AS
BEGIN
	SET NOCOUNT ON;

If @div = ' '
	begin
		set @div = NULL
	end
If @entity = ' '
	begin
		set @entity = NULL
	end
If @account = ' '
	begin
		set @account = NULL
	end
--  Get end of month date
--  If period is greater than 4 we subtract 4 from the period to get the 
--  month else we add 8 to get the month.  I have set the day to 20 because 
--  each month has 20 days.  I then use the EOM function to get the last day
--  of the month.

Declare @asOfDate as DATETIME
,@sql VARCHAR(max)
If @per > 4
	begin
	 set @asOfDate = cast((@per -4) as varchar(2)) + '/20/' + cast(@Date as varchar(4))
	 set @asOfDate = dbo.EOMDate(@asOfDate)
	end
else
	begin
	set @asOfDate = cast((@per +8) as varchar(2)) + '/20/' + cast((@Date -1) as varchar(4))
	set @asOfDate = dbo.EOMDate(@asOfDate)
	end


CREATE TABLE dbo.#balanceAcct
(
	mfa_division char(3) null,
	mfa_account char(5) null,
	mfa_center char(4) null,
	entity_descp char(35) null, 
	acct_descp_1 char(35) null,
	acct_descp_2 char(35) null,
	eff_date datetime null,
	user_alpha_fld_1 varchar(10) null,
	user_alpha_fld_2 varchar(15) null,
	user_alpha_fld_3 varchar(20) null,
	trans_amt money null,
	prim_dr_cr_code char(1) null,
	descp varchar(80) null,
	jrnl_id char(20) null,
	Bal money null,
	f_year char (4) null,
	f_per varchar(2) null,
	entity char(5) null,
	end_of_month_date datetime null
)

INSERT INTO dbo.#balanceAcct (
	mfa_division,
	mfa_account,
	mfa_center,
	Bal,
	acct_descp_1,
	acct_descp_2,
	f_year,
	f_per,
	entity,
	end_of_month_date)

    -- Insert statements for procedure here
	-- Get beginning balance

SELECT 
		 LAB.mfa_division, 
		 LAB.mfa_account, 
		 LAB.mfa_center, 
        CASE 
			WHEN @per = 1 THEN LAB.ldr_amt_0 
			WHEN @per = 2 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 
			WHEN @per = 3 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 
			WHEN @per = 4 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 
			WHEN @per = 5 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 
			WHEN @per = 6 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 
			WHEN @per = 7 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 
			WHEN @per = 8 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 
			WHEN @per = 9 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 
			WHEN @per = 10 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4  + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 
			WHEN @per = 11 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10 
			WHEN @per = 12 THEN LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10 + LAB.ldr_amt_11 
		ELSE 0 
		END AS Bal, LA.acct_descp_1, LA.acct_descp_2
		, @Date
		, @per
		, LAB.ldr_entity_id
		, @asOfDate
FROM        
 DBSglep.dbo.ldr_acct_bal AS LAB 
 RIGHT OUTER JOIN
                      DBSglep.dbo.ldr_acct AS LA ON LAB.ldr_entity_id = LA.ldr_entity_id AND LAB.mfa_division = LA.mfa_division AND LAB.mfa_account = LA.mfa_account AND 
                      LAB.mfa_center = LA.mfa_center 
WHERE   
	(LAB.processing_yr = @Date) AND 
	(LAB.amt_class_type = 'ACTUAL')  AND 
	((LAB.mfa_division <> '999') and (LAB.mfa_division <> '207')) AND
	((LAB.mfa_division in 
		(select par_values 
		from dbo.fn_parse_multi_params_to_table(@div)) 
		OR @div is NULL))  
and ((LAB.mfa_account in 
		(select par_values 
		from dbo.fn_parse_multi_params_to_table(@account)) 
		or @account is Null)) 
and ((LAB.ldr_entity_id in 
		(select par_values 
		from dbo.fn_parse_multi_params_to_table(@entity)) 
		or @entity is Null)) 	
 AND Not
	((@per = 1 and LAB.ldr_amt_0 = 0) or 
	(@per = 2 and (LAB.ldr_amt_0 + LAB.ldr_amt_1) = 0) or
	(@per = 3 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2) = 0) or
	(@per = 4 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3) = 0) or
	(@per = 5 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4) = 0) or
	(@per = 6 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5) = 0) or
	(@per = 7 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6) = 0) or
	(@per = 8 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7) = 0) or
	(@per = 9 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8) = 0) or
	(@per = 10 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4  + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9) = 0) or 
	(@per = 11 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10) = 0) or 
	(@per = 12 and (LAB.ldr_amt_0 + LAB.ldr_amt_1 + LAB.ldr_amt_2 + LAB.ldr_amt_3 + LAB.ldr_amt_4 + LAB.ldr_amt_5 + LAB.ldr_amt_6 + LAB.ldr_amt_7 + LAB.ldr_amt_8 + LAB.ldr_amt_9 + LAB.ldr_amt_10 + LAB.ldr_amt_11) = 0))

--Get detail
SET @sql = 
'INSERT INTO dbo.#balanceAcct (
	mfa_division,
	mfa_account,
	mfa_center,
	entity_descp, 
	acct_descp_1,
	acct_descp_2,
	eff_date,
	user_alpha_fld_1,
	user_alpha_fld_2,
	user_alpha_fld_3,
	trans_amt,
	prim_dr_cr_code,
	descp,
	jrnl_id,
	entity,
	end_of_month_date)

SELECT     LAB.mfa_division
, LAB.mfa_account
, LAB.mfa_center
, LEP.entity_descp
, LA.acct_descp_1
, LA.acct_descp_2
, CONVERT(varchar, PJL.eff_date, 101) AS Edate
, PJL.jrnl_user_alpha_fld_1
, PJL.jrnl_user_alpha_fld_2
, PJL.jrnl_user_alpha_fld_3
, PJL.trans_amt
, PJL.prim_dr_cr_code
, PJL.descp
, PJL.jrnl_id
, LAB.ldr_entity_id
, ' + CONVERT(VARCHAR(20), @asOfDate, 101) + '
FROM         DBSglep.dbo.ldr_acct_bal AS LAB RIGHT OUTER JOIN
                      DBSjepc.dbo.posted_jrnl_line AS PJL ON LAB.ldr_entity_id = PJL.line_ldr_entity_id 
				AND LAB.mfa_division = PJL.mfa_division 
				AND LAB.mfa_account = PJL.mfa_account 
				AND LAB.mfa_center = PJL.mfa_center 
				AND LAB.processing_yr = PJL.posting_yr 
				AND LAB.amt_class_type = PJL.amt_class_1_type 
			right OUTER JOIN
                      DBSglep.dbo.ldr_entity_policy AS LEP ON LAB.ldr_entity_id = LEP.ldr_entity_id 
			RIGHT OUTER JOIN
                      DBSglep.dbo.ldr_acct AS LA ON LAB.ldr_entity_id = LA.ldr_entity_id 
				AND LAB.mfa_division = LA.mfa_division 
				AND LAB.mfa_account = LA.mfa_account 
				AND LAB.mfa_center = LA.mfa_center
WHERE     (LAB.processing_yr = ' + CAST(@Date AS CHAR(4)) + ') AND 
	(LAB.amt_class_type = ''ACTUAL'') AND 
	(PJL.posting_pd = ' + CAST(@per AS CHAR(1)) + ') AND 
	(PJL.posting_yr = LAB.processing_yr) AND 
    ((LAB.mfa_division <> ''999'') and (LAB.mfa_division <> ''207''))
'
IF @div	IS NOT NULL
BEGIN 
SET @sql = @sql + 
'and LAB.mfa_division in 
		(select par_values 
		from dbo.fn_parse_multi_params_to_table(''' + @div + '''))'
END 

IF @account IS NOT NULL 
BEGIN 
SET @sql = @sql +
'and LAB.mfa_account in 
		(select par_values 
		from dbo.fn_parse_multi_params_to_table(''' + @account + '''))'
END

IF @entity IS NOT NULL 
BEGIN 
SET @sql = @sql +
'and LAB.ldr_entity_id in 
		(select par_values 
		from dbo.fn_parse_multi_params_to_table(''' + @entity + '''))'
END 

EXEC (@sql)

SELECT
	mfa_division,
	mfa_account,
	mfa_center,
	entity_descp, 
	acct_descp_1,
	acct_descp_2,
    eff_date,
	user_alpha_fld_1,
	user_alpha_fld_2,
	user_alpha_fld_3,
	trans_amt,
	prim_dr_cr_code,
	descp,
	jrnl_id,
	Bal,
	f_year,
	f_per,
	entity,
	end_of_month_date

FROM dbo.#balanceAcct
order by entity
	, mfa_account
	, mfa_division
	, mfa_center
	, user_alpha_fld_1

DROP TABLE dbo.#balanceAcct

-- ====================================================
-- Example to execute the stored procedure
-- ====================================================
/*
EXECUTE dbo.spGetGLbyAccount '2009', '10', '70100'
*/
END

USE [database]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_parse_multi_params_to_table]    Script Date: 01/28/2013 10:40:49 ******/
CREATE FUNCTION [dbo].[fn_parse_multi_params_to_table](@RawParameter [nvarchar](4000))
RETURNS  TABLE (
	[par_values] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [ParseParamList].[ParseParamList.UserDefinedFunctions].[fn_ParseMultiParametersToTable]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_parse_multi_params_to_table'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'fn_ParseMultiParametersToTable.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_parse_multi_params_to_table'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=15 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_parse_multi_params_to_table'
GO

...
IF @div	IS NOT NULL
BEGIN 
SET @sql = @sql + 
'and EXISTS (select 1 from dbo.fn_parse_multi_params_to_table(''' + @div + 
''') where par_values=LAB.mfa_division)'
END 
...

Msg 6513, Level 16, State 27, Procedure spGetGLbyAccount, Line 99
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.