/****** Object:  StoredProcedure [dbo].[sp_subledgers]    Script Date: 07-02-2024 21:09:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER     PROCEDURE [dbo].[sp_subledgers]
(@Tenant [varchar](50), @Date [date],@depreciationBookCode_value nvarchar(max),@GlAccounts_exclude nvarchar(max))  
AS  
DECLARE @ErrorMessage NVARCHAR(max);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @condition nvarchar(max);
DECLARE @period_end_date DATE;
DECLARE @SQL nvarchar(max);
--DECLARE @Tenant [varchar](50) = '4';
--DECLARE @Date [date] = '2021-02-24';
--DECLARE @depreciationBookCode_value nvarchar(max) = 'GROUP';
--DECLARE @GlAccounts_exclude nvarchar(max) = 'NA' --'1010410,1010440,1010510';
DECLARE @depreciationBookCode_condition nvarchar(max);

BEGIN TRY

select @condition = case when (select count(*) from keyDefinitions where excludeinSubledger = 'True' and clientid = @Tenant) > 0
					then string_Agg(CAST(concat('[',replace(KeyName,' ',''),'] = ''''') as nvarchar(MAX)),' and ') 
					else '1 = 1'
					end
from keyDefinitions where excludeinSubledger = 'True' and clientid = @Tenant
--print @condition

select  @period_end_date = DATEADD(day,-1,PT.period_end_date)
		from
			(select [startingDate], case when r = 1 then 'period_end_date' when r = 2 then 'period_start_date' end as [period]
			from
				(select [startingDate],ROW_NUMBER() over(order by [startingDate] desc) r 
				from dbo.accountingperiods 
				where clientid = @Tenant and [startingDate] <= cast(@Date as date) 
				) A
			where A.r<=2
			) T
			PIVOT (min([startingDate]) FOR [period] in ([period_end_date],[period_start_date])) PT
--print @period_end_date

--fix :remove subledger
SELECT @GlAccounts_exclude = CONCAT('''',REPLACE(@GlAccounts_exclude,',',''','''),'''')
--print @GlAccounts_exclude
select @GlAccounts_exclude =  case when trim(@GlAccounts_exclude) = '''''' or @GlAccounts_exclude = '''NA''' then '1=1' else '[Account Number] not in ('+@GlAccounts_exclude+')' end
--print @GlAccounts_exclude

SELECT @depreciationBookCode_value = CONCAT('''',REPLACE(@depreciationBookCode_value,',',''','''),'''')
--print @depreciationBookCode_value
SELECT @depreciationBookCode_condition = case when @depreciationBookCode_value = '''*''' then '1=1' else 'depreciationBookCode in ('+@depreciationBookCode_value+')' end
--print @depreciationBookCode_condition

IF OBJECT_ID('tempdb..#Temp_faLedgerEntries_query') IS NOT NULL
	DROP TABLE #Temp_faLedgerEntries_query	

CREATE TABLE #Temp_faLedgerEntries_query([id] [uniqueidentifier] NOT NULL,[entryNumber] [int] NULL,[glEntryNumber] [int] NULL,[amount] [decimal](38, 2) NULL,[faNumber] [nvarchar](50) NULL,[depreciationBookCode] [nvarchar](50) NULL,[description] [nvarchar](150) NULL,[faPostingCategory] [nvarchar](20) NULL,[faPostingType] [nvarchar](20) NULL,[globalDimension1Code] [nvarchar](50) NULL,[globalDimension2Code] [nvarchar](50) NULL,[documentType] [nvarchar](150) NULL,[documentNumber] [nvarchar](50) NULL,[postingDate] [date] NULL,[faPostingDate] [date] NULL,[systemCreatedAt] [datetime2](7) NOT NULL,[systemModifiedAt] [datetime2](7) NOT NULL,[companyCodeID] [varchar](50) NOT NULL,[triggerId] [varchar](200) NOT NULL,[clientid] [int] NOT NULL,[createdAt] [datetime2](7) NULL,[updatedAt] [datetime2](7) NULL,[Isdeleted] [varchar](5) NULL,[glEntryNumberReconciled] [int] NULL,[dimensionSetID] [int] NULL,[faPostingGroup] [nvarchar](20) NULL,[resultOnDisposal] [nvarchar](20) NULL)

SELECT @SQL = 'INSERT INTO #Temp_faLedgerEntries_query ([id],[entryNumber],[glEntryNumber],[amount],[faNumber],[depreciationBookCode],[description],[faPostingCategory],[faPostingType],[globalDimension1Code],[globalDimension2Code],[documentType],[documentNumber],[postingDate],[faPostingDate],[systemCreatedAt],[systemModifiedAt],[companyCodeID],[triggerId],[clientid],[createdAt],[updatedAt],[Isdeleted],[glEntryNumberReconciled],[dimensionSetID],[faPostingGroup],[resultOnDisposal]) SELECT [id],[entryNumber],[glEntryNumber],[amount],[faNumber],[depreciationBookCode],[description],[faPostingCategory],[faPostingType],[globalDimension1Code],[globalDimension2Code],[documentType],[documentNumber],[postingDate],[faPostingDate],[systemCreatedAt],[systemModifiedAt],[companyCodeID],[triggerId],[clientid],[createdAt],[updatedAt],[Isdeleted],[glEntryNumberReconciled],[dimensionSetID],[faPostingGroup],[resultOnDisposal] FROM faledgerEntries WHERE ' + @depreciationBookCode_condition +' and clientid = '+@Tenant
--print (@SQL)
EXECUTE sp_executesql @SQL

IF OBJECT_ID('tempdb..#Temp_AR_subledger') IS NOT NULL
	DROP TABLE #Temp_AR_subledger

IF OBJECT_ID('tempdb..#Temp_LedgerEntries') IS NOT NULL
	DROP TABLE #Temp_LedgerEntries	


SELECT	id,glAccountNumber,postingDate,amount,[dimensionSetID],sourceType,sourceNumber,CompanyCodeID
INTO #Temp_LedgerEntries
FROM (
	select BankLedgerEntries.id,GLEntries.glAccountNumber, GLEntries.postingDate,BankLedgerEntries.amountLCY as amount,GLEntries.[dimensionSetID]
	,GLEntries.sourceType,GLEntries.sourceNumber,GLEntries.CompanyCodeID 
	from    
			(select id,postingDate,entryNumber,companyCodeID,amountLCY 
			from [dbo].[bankAccountLedgerEntries]
			where clientid = @Tenant and  Isdeleted is NULL
			) BankLedgerEntries
	inner join
			(select glAccountNumber,postingDate,[amount],[dimensionSetID],entryNumber,sourceType,sourceNumber,CompanyCodeID
			from [dbo].[generalLedgerEntries]
			where clientid = @Tenant and  Isdeleted is NULL
			) GLEntries
		on BankLedgerEntries.entryNumber = GLEntries.entryNumber and BankLedgerEntries.CompanyCodeID = GLEntries.CompanyCodeID
	inner join
			(select [number],CompanyCodeID
			from [dbo].[accounts] 
			where incomeBalance = 'Balance Sheet' and clientid = @Tenant and  Isdeleted is NULL
			) GLAccounts
		on GLAccounts.[number] = GLEntries.glAccountNumber and GLAccounts.CompanyCodeID = GLEntries.CompanyCodeID 

union

	select id,glAccountNumber,postingDate,amount,[dimensionSetID],sourceType,sourceNumber,faGLEntries.CompanyCodeID
	from (
		select FALedgerEntries.id,GLEntries.glAccountNumber, FALedgerEntries.postingDate,FALedgerEntries.amount,GLEntries.[dimensionSetID]						
		,'Fixed Asset' as sourceType,GLEntries.sourceNumber,FALedgerEntries.CompanyCodeID		
		from						
					(select id,postingDate,glEntryNumber,glEntryNumberReconciled,companyCodeID,amount,faNumber,faPostingType			
					,case when glEntryNumber <> 0 then glEntryNumber			
						  when glEntryNumber = 0 and glEntryNumberReconciled <> 0 then glEntryNumberReconciled 		
						  end as glEntryNumber_mod		
					from #Temp_faLedgerEntries_query			
					where clientid = @Tenant and Isdeleted is NULL and (glEntryNumber <> 0 or glEntryNumberReconciled <> 0)	
					--and depreciationBookCode = 'GROUP'
					) faLedgerEntries			
		inner join						
					(select glAccountNumber,postingDate,[dimensionSetID],entryNumber,sourceType,sourceNumber,CompanyCodeID			
					from [dbo].[generalLedgerEntries]			
					where  clientid = @Tenant and Isdeleted is NULL			
					) GLEntries			
		on FALedgerEntries.glEntryNumber_mod = GLEntries.entryNumber and FALedgerEntries.CompanyCodeID = GLEntries.CompanyCodeID	
	--	union
	--	select FALedgerEntries.id
	--	,case when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Acquisition Cost' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[acquisitionCostAccount]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Depreciation' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[accumulatedDepreciationAccount]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Write-Down' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[writeDownAccount]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Appreciation' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[appreciationAccount]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Custom 1' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[custom1Account]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Custom 2' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[custom2Account]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Proceeds on Disposal' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[salesAccountonDisposalGain]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Gain/Loss' and faLedgerEntries.resultonDisposal = 'Gain' then fAPostingGroups.[gainsAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = '' and faLedgerEntries.faPostingType = 'Gain/Loss' and faLedgerEntries.resultonDisposal = 'Loss' then fAPostingGroups.[lossesAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Acquisition Cost' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[acquisitionCostAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Depreciation' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[accumulatedDepreciationAccountOnDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Write-Down' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[writeDownAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Appreciation' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[appreciationAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Custom 1' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[custom1AccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Custom 2' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[custom2AccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Book Value on Disposal' and faLedgerEntries.resultonDisposal = 'Gain' then fAPostingGroups.[bookValueAccountonDisposalGain]
	--		when faLedgerEntries.faPostingCategory = 'Disposal' and faLedgerEntries.faPostingType = 'Book Value on Disposal' and faLedgerEntries.resultonDisposal = 'Loss' then fAPostingGroups.[bookValueAccountonDisposalLoss]
	--		when faLedgerEntries.faPostingCategory = 'Bal. Disposal' and faLedgerEntries.faPostingType = 'Write-Down' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[writeDownBalanceAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Bal. Disposal' and faLedgerEntries.faPostingType = 'Appreciation' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[appreciationBalanceAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Bal. Disposal' and faLedgerEntries.faPostingType = 'Custom 1' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[custom1BalanceAccountonDisposal]
	--		when faLedgerEntries.faPostingCategory = 'Bal. Disposal' and faLedgerEntries.faPostingType = 'Custom 2' and faLedgerEntries.resultonDisposal = '' then fAPostingGroups.[custom2BalanceAccountonDisposal]		
	--		else fAPostingGroups.[acquisitionCostAccount]
	--		end as glAccountNumber
	--	,faLedgerEntries.postingDate,FALedgerEntries.amount,faLedgerEntries.[dimensionSetID],'Fixed Asset' as sourceType,faLedgerEntries.faNumber as sourceNumber,faLedgerEntries.CompanyCodeID		
		
	--	from 						
	--		(select id,postingDate,documentNumber,faNumber,depreciationBookCode,faPostingGroup,faPostingCategory,faPostingType,glEntryNumber						
	--		,glEntryNumberReconciled,companyCodeID,amount,resultonDisposal,dimensionSetID						
	--		from #Temp_faLedgerEntries_query						
	--		where clientid = @Tenant and Isdeleted is NULL and (glEntryNumber = 0 and glEntryNumberReconciled = 0)
	--		--and depreciationBookCode = 'GROUP'
	--		) faLedgerEntries					
	--	inner join 						
	--		(select * 
	--		from dbo.fAPostingGroups
	--		where clientid = @Tenant and Isdeleted is NULL
	--		) fAPostingGroups						
	--	on faLedgerEntries.fAPostingGroup = fAPostingGroups.code and faLedgerEntries.companyCodeID = fAPostingGroups.companyCodeID	
	) faGLEntries
	inner join
	(select [number],CompanyCodeID
	from [dbo].[accounts] 
	where incomeBalance = 'Balance Sheet' and clientid = @Tenant and  Isdeleted is NULL
	) GLAccounts
	on  GLAccounts.[number] = faGLEntries.glAccountNumber and GLAccounts.CompanyCodeID = faGLEntries.CompanyCodeID

union

	select CGLE.id,CGLE.glAccountNumber,CGLE.postingDate,detailedCustomerLedgerEntries.amountLCY as amount
			,CGLE.[dimensionSetID],CGLE.sourceType,CGLE.sourceNumber,CGLE.CompanyCodeID
		from 	
		(select id,glAccountNumber,postingDate,CustomerLedgerEntries_entryNumber as entryNumber
			,[dimensionSetID],sourceType,sourceNumber,CompanyCodeID
		 from (
			select CustomerLedgerEntries.id,GLEntries.glAccountNumber, GLEntries.postingDate,GLEntries.[amount],CustomerLedgerEntries.amountLCY,GLEntries.documentNumber,GLEntries.documentType
			,GLEntries.entryNumber as GLEntries_entryNumber,CustomerLedgerEntries.entryNumber as CustomerLedgerEntries_entryNumber
			,GLEntries.[dimensionSetID],GLEntries.sourceType,GLEntries.sourceNumber,GLEntries.CompanyCodeID
			,ROW_NUMBER() OVER(PARTITION BY CustomerLedgerEntries.id,GLEntries.postingDate,GLEntries.documentNumber,GLEntries.sourceType,GLEntries.sourceNumber,GLEntries.CompanyCodeID ORDER BY GLEntries.entryNumber desc) r
			from
					(select id,postingDate,documentNumber,documentType,customerNumber,companyCodeID,amountLCY,entryNumber,reversed
					from [dbo].[customerLedgerEntries]
					where clientid = @Tenant and  Isdeleted is NULL
					) CustomerLedgerEntries
			inner join
					(select glAccountNumber,postingDate,documentNumber,documentType,[amount],[dimensionSetID],entryNumber,sourceType,sourceNumber,CompanyCodeID,reversed,balancingAccountType
					from [dbo].[generalLedgerEntries]
					where clientid = @Tenant and  Isdeleted is NULL
					and balancingAccountType <> 'Customer' --Issue 1,3 fix
					) GLEntries
				on CustomerLedgerEntries.postingDate = GLEntries.postingDate and CustomerLedgerEntries.documentNumber = GLEntries.documentNumber and GLEntries.documentType=CustomerLedgerEntries.documentType 
					and CustomerLedgerEntries.customerNumber = GLEntries.sourceNumber and GLEntries.sourceType = 'Customer' and CustomerLedgerEntries.CompanyCodeID = GLEntries.CompanyCodeID
					and CustomerLedgerEntries.reversed = GLEntries.reversed --Issue 1,3 fix
			inner join
				   (select [number],CompanyCodeID
				   from [dbo].[accounts] 
				   where incomeBalance = 'Balance Sheet' and clientid = @Tenant and  Isdeleted is NULL
				   ) GLAccounts
			  on GLAccounts.[number] = GLEntries.glAccountNumber and GLAccounts.CompanyCodeID = GLEntries.CompanyCodeID 
			) A where r = 1 
		)CGLE
	inner join
	(select customerLedgerEntryNumber,companyCodeID,sum(amountLCY) amountLCY
	from detailedCustomerLedgerEntries
	where postingDate <= @period_end_date and clientid = @Tenant and  Isdeleted is NULL
	and [ledgerEntryAmount] = 'True' --Issue 2 fix
	group by customerLedgerEntryNumber,companyCodeID
	) detailedCustomerLedgerEntries
	on CGLE.entryNumber = detailedCustomerLedgerEntries.customerLedgerEntryNumber and CGLE.CompanyCodeID = detailedCustomerLedgerEntries.CompanyCodeID

union

	select VGLE.id,VGLE.glAccountNumber,VGLE.postingDate,detailedVendorLedgerEntries.amountLCY as amount
		,VGLE.[dimensionSetID],VGLE.sourceType,VGLE.sourceNumber,VGLE.CompanyCodeID
	from 
		(select id,glAccountNumber,postingDate,VendorLedgerEntries_entryNumber as entryNumber
		,[dimensionSetID],sourceType,sourceNumber,CompanyCodeID
		 from (
			select VendorLedgerEntries.id,GLEntries.glAccountNumber, GLEntries.postingDate,GLEntries.[amount],VendorLedgerEntries.amountLCY,GLEntries.documentNumber,GLEntries.documentType
			,GLEntries.entryNumber as GLEntries_entryNumber,VendorLedgerEntries.entryNumber as VendorLedgerEntries_entryNumber
			,GLEntries.[dimensionSetID],GLEntries.sourceType,GLEntries.sourceNumber,GLEntries.CompanyCodeID
			,ROW_NUMBER() OVER(PARTITION BY VendorLedgerEntries.id,GLEntries.postingDate,GLEntries.documentNumber,GLEntries.sourceType,GLEntries.sourceNumber,GLEntries.CompanyCodeID ORDER BY GLEntries.entryNumber desc) r
			from
					(select id,postingDate,documentNumber,documentType,vendorNumber,companyCodeID,amountLCY,entryNumber,reversed
					from [dbo].[vendorLedgerEntries]
					where clientid = @Tenant and  Isdeleted is NULL
					) VendorLedgerEntries
			inner join
					(select glAccountNumber,postingDate,documentNumber,documentType,[amount],[dimensionSetID],entryNumber,sourceType,sourceNumber,CompanyCodeID,reversed,balancingAccountType
					from [dbo].[generalLedgerEntries]
					where clientid = @Tenant and  Isdeleted is NULL
					and balancingAccountType <> 'Vendor' --Issue 1,3 fix
					) GLEntries
				on VendorLedgerEntries.postingDate = GLEntries.postingDate and VendorLedgerEntries.documentNumber = GLEntries.documentNumber and GLEntries.documentType=VendorLedgerEntries.documentType
					and VendorLedgerEntries.vendorNumber = GLEntries.sourceNumber and GLEntries.sourceType = 'Vendor' and VendorLedgerEntries.CompanyCodeID = GLEntries.CompanyCodeID
					and VendorLedgerEntries.reversed = GLEntries.reversed --Issue 1,3 fix
			inner join
				   (select [number],CompanyCodeID
				   from [dbo].[accounts] 
				   where incomeBalance = 'Balance Sheet' and clientid = @Tenant and  Isdeleted is NULL
				   ) GLAccounts
			  on GLAccounts.[number] = GLEntries.glAccountNumber and GLAccounts.CompanyCodeID = GLEntries.CompanyCodeID 
			) A where r = 1 
		) VGLE
		inner join
		(select vendorLedgerEntryNumber,companyCodeID,sum(amountLCY) amountLCY
		from detailedVendorLedgerEntries
		where postingDate <= @period_end_date and clientid = @Tenant and  Isdeleted is NULL
		and [ledgerEntryAmount] = 'True' --Issue 2 fix
		group by vendorLedgerEntryNumber,companyCodeID
		) detailedVendorLedgerEntries
		on VGLE.entryNumber = detailedVendorLedgerEntries.vendorLedgerEntryNumber and VGLE.CompanyCodeID = detailedVendorLedgerEntries.CompanyCodeID
)A
where glAccountNumber <> ''
order by sourceType,companyCodeID	



SELECT 	[Entity Unique Identifier]
,[Account Number]
,isnull([Key3],'') as [Key3]
,isnull([Key4],'') as [Key4]
,isnull([Key5],'') as [Key5]
,isnull([Key6],'') as [Key6]
,isnull([Key7],'') as [Key7]
,[Key8]
,[Key9]
,[Key10]
,[Period End Date]
,[Subledger Reporting Balance]
,[Subledger  Alternate Balance]
,sum([Subledger Account Balance]) as [Subledger Account Balance]
--,isnull(cast(sum([Subledger Account Balance]) as varchar(4000)),'') as [Subledger Account Balance]
INTO #Temp_AR_subledger
FROM
	(select distinct
	LedgerEntries.CompanyCodeID as [Entity Unique Identifier]
	,LedgerEntries.[glAccountNumber] as [Account Number]
	,LedgerEntries.id as GLEntries_id 
	,case when isnull(K3.valuePosting,'') like (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 3' and clientid = @Tenant) 
	  then K3.dimensionValueCode 
	  else '' 
	  end as [Key3] 
	,case when isnull(K4.valuePosting,'') like (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 4' and clientid = @Tenant) 
	  then K4.dimensionValueCode 
	  else '' 
	  end as [Key4]
	,case when LedgerEntries.[glAccountNumber] in (K5.Account_No) then  LedgerEntries.sourceNumber else '' end as [Key5]
	,case when isnull(K6.valuePosting,'') like (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 6' and clientid = @Tenant) 
	  then K6.dimensionValueCode 
	  else '' 
	  end as [Key6] 
	,case when isnull(K7.valuePosting,'') like (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 7' and clientid = @Tenant) 
	  then K7.dimensionValueCode 
	  else '' 
	  end as [Key7]
	,'' as [Key8]
	,'' as [Key9]
	,'' as [Key10]
	,LedgerEntries.postingDate as [Posting Date]
	,convert(varchar(10),accountingperiods.[period_end_date],101) as [Period End Date]
	,'' as [Subledger Reporting Balance]
	,'' as [Subledger  Alternate Balance]
	,ISNULL(LedgerEntries.[Amount],0) as [Subledger Account Balance]
	--,LedgerEntries.[Amount] as [Subledger Account Balance]
	from	
		(select id,glAccountNumber,postingDate,[amount],[dimensionSetID],sourceType,sourceNumber,CompanyCodeID 
		 from #Temp_LedgerEntries
		) LedgerEntries
	left join
	(select distinct LedgerEntries.id,LedgerEntries.companyCodeID,LedgerEntries.glAccountNumber,DimensionSetEntries.[dimensionSetID],DimensionSetEntries.dimensionCode,DimensionSetEntries.dimensionValueCode,defaultDimensions.valuePosting
		from
				(select id,glAccountNumber,[dimensionSetID],CompanyCodeID 
				from #Temp_LedgerEntries
				) LedgerEntries
		inner join
				(select dimensionSetID, dimensionCode, dimensionValueCode, companyCodeID 
				from [dbo].[DimensionSetEntries] 
				where clientid = @Tenant and Isdeleted is NULL
					and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 3' and clientid = @Tenant) 
				) DimensionSetEntries
		on LedgerEntries.[dimensionSetID] = DimensionSetEntries.dimensionSetID and LedgerEntries.CompanyCodeID = DimensionSetEntries.CompanyCodeID
		left join
				(select distinct number,dimensionCode,companyCodeID,valuePosting 
				from [dbo].[defaultDimensions] 
				where clientid = @Tenant and tableId = 15 and Isdeleted is NULL
				and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 3' and clientid = @Tenant) 
				and isnull(valuePosting,'') LIKE (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 3' and clientid = @Tenant)
				) defaultDimensions
		on LedgerEntries.glAccountNumber = defaultDimensions.number and LedgerEntries.CompanyCodeID = defaultDimensions.CompanyCodeID and  DimensionSetEntries.dimensionCode = defaultDimensions.dimensionCode and  DimensionSetEntries.CompanyCodeID = defaultDimensions.CompanyCodeID 
		) K3
	on LedgerEntries.[dimensionSetID] = K3.dimensionSetID and LedgerEntries.glAccountNumber = K3.glAccountNumber and LedgerEntries.CompanyCodeID = K3.CompanyCodeID and LedgerEntries.id = K3.id 
	left join
		(select distinct LedgerEntries.id,LedgerEntries.companyCodeID,LedgerEntries.glAccountNumber,DimensionSetEntries.[dimensionSetID],DimensionSetEntries.dimensionCode,DimensionSetEntries.dimensionValueCode,defaultDimensions.valuePosting
		from
				(select id,glAccountNumber,[dimensionSetID],CompanyCodeID 
				from #Temp_LedgerEntries
				) LedgerEntries
		inner join
				(select dimensionSetID, dimensionCode, dimensionValueCode, companyCodeID 
				from [dbo].[DimensionSetEntries] 
				where clientid = @Tenant and Isdeleted is NULL
					and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 4' and clientid = @Tenant) 
				) DimensionSetEntries
		on LedgerEntries.[dimensionSetID] = DimensionSetEntries.dimensionSetID and LedgerEntries.CompanyCodeID = DimensionSetEntries.CompanyCodeID
		left join
				(select distinct number,dimensionCode,companyCodeID,valuePosting 
				from [dbo].[defaultDimensions] 
				where clientid = @Tenant and tableId = 15 and Isdeleted is NULL
				and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 4' and clientid = @Tenant) 
				and isnull(valuePosting,'') LIKE (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 4' and clientid = @Tenant)
				) defaultDimensions
		on LedgerEntries.glAccountNumber = defaultDimensions.number and LedgerEntries.CompanyCodeID = defaultDimensions.CompanyCodeID and  DimensionSetEntries.dimensionCode = defaultDimensions.dimensionCode and  DimensionSetEntries.CompanyCodeID = defaultDimensions.CompanyCodeID
		) K4
	on LedgerEntries.[dimensionSetID] = K4.dimensionSetID and LedgerEntries.glAccountNumber = K4.glAccountNumber and LedgerEntries.CompanyCodeID = K4.CompanyCodeID and LedgerEntries.id = K4.id 
	left join
		(select [keyName],[source],f.[value] as [Account_No],f.ordinal 
		from (select keyName,[source],[value] 
			  from dbo.keyDefinitions 
			  where clientid = @Tenant and keyName = 'Key 5'
			 ) A 
			CROSS APPLY STRING_SPLIT(A.[value],'|',1) f
		) K5
	on LedgerEntries.[glAccountNumber] = K5.Account_No
	left join
		(select distinct LedgerEntries.id,LedgerEntries.companyCodeID,LedgerEntries.glAccountNumber,DimensionSetEntries.[dimensionSetID],DimensionSetEntries.dimensionCode,DimensionSetEntries.dimensionValueCode,defaultDimensions.valuePosting
		from
				(select id,glAccountNumber,[dimensionSetID],CompanyCodeID 
				from #Temp_LedgerEntries
				) LedgerEntries
		inner join
				(select dimensionSetID, dimensionCode, dimensionValueCode, companyCodeID 
				from [dbo].[DimensionSetEntries] 
				where clientid = @Tenant and Isdeleted is NULL
					and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 6' and clientid = @Tenant) 
				) DimensionSetEntries
		on LedgerEntries.[dimensionSetID] = DimensionSetEntries.dimensionSetID and LedgerEntries.CompanyCodeID = DimensionSetEntries.CompanyCodeID
		left join
				(select distinct number,dimensionCode,companyCodeID,valuePosting 
				from [dbo].[defaultDimensions] 
				where clientid = @Tenant and tableId = 15 and Isdeleted is NULL
				and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 6' and clientid = @Tenant) 
				and isnull(valuePosting,'') LIKE (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 6' and clientid = @Tenant)
				) defaultDimensions
		on LedgerEntries.glAccountNumber = defaultDimensions.number and LedgerEntries.CompanyCodeID = defaultDimensions.CompanyCodeID and  DimensionSetEntries.dimensionCode = defaultDimensions.dimensionCode and  DimensionSetEntries.CompanyCodeID = defaultDimensions.CompanyCodeID
		) K6
	on LedgerEntries.[dimensionSetID] = K6.dimensionSetID and LedgerEntries.glAccountNumber = K6.glAccountNumber and LedgerEntries.CompanyCodeID = K6.CompanyCodeID and LedgerEntries.id = K6.id 
	left join
		(select distinct LedgerEntries.id,LedgerEntries.companyCodeID,LedgerEntries.glAccountNumber,DimensionSetEntries.[dimensionSetID],DimensionSetEntries.dimensionCode,DimensionSetEntries.dimensionValueCode,defaultDimensions.valuePosting
		from
				(select id,glAccountNumber,[dimensionSetID],CompanyCodeID 
				from #Temp_LedgerEntries
				) LedgerEntries
		inner join
				(select dimensionSetID, dimensionCode, dimensionValueCode, companyCodeID 
				from [dbo].[DimensionSetEntries] 
				where clientid = @Tenant and Isdeleted is NULL
					and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 7' and clientid = @Tenant) 
				) DimensionSetEntries
		on LedgerEntries.[dimensionSetID] = DimensionSetEntries.dimensionSetID and LedgerEntries.CompanyCodeID = DimensionSetEntries.CompanyCodeID
		left join
				(select distinct number,dimensionCode,companyCodeID,valuePosting 
				from [dbo].[defaultDimensions] 
				where clientid = @Tenant and tableId = 15 and Isdeleted is NULL
				and dimensionCode in (select [value] from dbo.keyDefinitions where keyName = 'Key 7' and clientid = @Tenant) 
				and isnull(valuePosting,'') LIKE (select case when [mandatoryDimensionOnly] = 'True' then 'Code Mandatory' else '%' end as [mandatoryDimensionOnly] from dbo.keyDefinitions where keyName = 'Key 7' and clientid = @Tenant)
				) defaultDimensions
		on LedgerEntries.glAccountNumber = defaultDimensions.number and LedgerEntries.CompanyCodeID = defaultDimensions.CompanyCodeID and  DimensionSetEntries.dimensionCode = defaultDimensions.dimensionCode and  DimensionSetEntries.CompanyCodeID = defaultDimensions.CompanyCodeID
		) K7
	on LedgerEntries.[dimensionSetID] = K7.dimensionSetID and LedgerEntries.glAccountNumber = K7.glAccountNumber and LedgerEntries.CompanyCodeID = K7.CompanyCodeID and LedgerEntries.id = K7.id 
	CROSS JOIN
		(select PT.period_start_date, DATEADD(day,-1,PT.period_end_date) as period_end_date
		from
			(select [startingDate], case when r = 1 then 'period_end_date' when r = 2 then 'period_start_date' end as [period]
			from
				(select [startingDate],ROW_NUMBER() over(order by [startingDate] desc) r 
				from dbo.accountingperiods 
				where clientid = @Tenant and [startingDate] <= cast(@Date as date) 
				) A
			where A.r<=2
			) T
			PIVOT (min([startingDate]) FOR [period] in ([period_end_date],[period_start_date])) PT
		)accountingperiods
	where LedgerEntries.postingDate <= accountingperiods.[period_end_date] 
	) A
GROUP BY [Entity Unique Identifier]
,[Account Number]
,[Key3]
,[Key4]
,[Key5]
,[Key6]
,[Key7]
,[Key8]
,[Key9]
,[Key10]
,[Period End Date]
,[Subledger Reporting Balance]
,[Subledger  Alternate Balance]
ORDER BY [Entity Unique Identifier], [Account Number]

--SELECT @SQL = 'SELECT * FROM #Temp_AR_subledger WHERE ' + @condition + ' ORDER BY [Entity Unique Identifier], [Account Number]'
--fix :remove subledger
SELECT @SQL = 'SELECT * FROM #Temp_AR_subledger WHERE (' + @condition + ') and (' +@GlAccounts_exclude+ ') ORDER BY [Entity Unique Identifier], [Account Number]'
EXECUTE (@SQL)
--print (@SQL)


END TRY

BEGIN CATCH

	IF OBJECT_ID('tempdb..#Temp_AR_subledger') IS NOT NULL
	DROP TABLE #Temp_AR_subledger
	
	IF OBJECT_ID('tempdb..#Temp_LedgerEntries') IS NOT NULL
	DROP TABLE #Temp_LedgerEntries	

	IF OBJECT_ID('tempdb..#Temp_faLedgerEntries_query') IS NOT NULL
	DROP TABLE #Temp_faLedgerEntries_query	

	SELECT 
        @ErrorMessage   = 'Error raised from sp: '+ ERROR_PROCEDURE() + ' : ' + ERROR_MESSAGE() + 'Error Number :'+ CAST (ERROR_NUMBER() AS VARCHAR(100))+ 'Error Line :'+ CAST ( ERROR_LINE() AS VARCHAR(100))  ,
        @ErrorSeverity  = ERROR_SEVERITY(),
        @ErrorState     = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH