ALTER PROCEDURE [dbo].[PRPTFILOTRIALBALANCE]
(
	@CompanyID VARCHAR(20)
	,@FiscalYear VARCHAR(20)
	,@FiscalPeriod VARCHAR(20)
	,@BusinessUnit VARCHAR(20)
	,@CostCenter VARCHAR(20)
	,@UserID VARCHAR(20)
)
AS

--DECLARE @CompanyID VARCHAR(20) = '1000'
--DECLARE @FiscalYear VARCHAR(20) = '2024'
--DECLARE @FiscalPeriod VARCHAR(20) = '01'

DECLARE @FiscalYearLast VARCHAR(20) = dbo.fn_formatdatetime((DATEADD(dd, -1, CONVERT(DATETIME,(@FiscalYear+ @FiscalPeriod + '01')))),'yyyy')
DECLARE @FiscalPeriodLast VARCHAR(20) = dbo.fn_formatdatetime((DATEADD(dd, -1, CONVERT(DATETIME,(@FiscalYear+ @FiscalPeriod + '01')))),'mm')
DECLARE @PeriodDesc VARCHAR(200) = dbo.fn_formatdatetime((DATEADD(dd, -1, DATEADD(m, 1,(CONVERT(DATETIME,(@FiscalYear+ @FiscalPeriod + '01')))))),'dd mmmm yyyy')

DECLARE @TBalanceAmount TABLE
(
	AccountSubTyp VARCHAR(20)
	,AccountClass VARCHAR(20)
	,GlAccoundDesc VARCHAR(250)
	,GlAccountID VARCHAR(20)
	,AmountDebet DECIMAL(30,0)
	,AmountCredit DECIMAL(30,0)
	,AmountDebetCompCurr DECIMAL(30,0)
	,AmountCreditCompCurr DECIMAL(30,0)
	,AmountBalanceCompCurr DECIMAL(30,0)
	,AmountBalanceCompCurrReport DECIMAL(30,0)
)

DECLARE @TBalance TABLE
(
	AccountSubTyp VARCHAR(20)
	,AccountClass VARCHAR(20)
	,GlAccoundDesc VARCHAR(250)
	,GlAccountID VARCHAR(20)
	,AmountDebet DECIMAL(30,0)
	,AmountCredit DECIMAL(30,0)
	,AmountDebetCompCurr DECIMAL(30,0)
	,AmountCreditCompCurr DECIMAL(30,0)
	,AmountBalanceCompCurr DECIMAL(30,0)
	,AmountBalanceCompCurrReport DECIMAL(30,0)
)

INSERT INTO @TBalanceAmount
SELECT coagl.AccountSubTyp
		,coagl.AccountClass
		,coagl.Description AS GlAccoundDesc
		,accbal.GlAccount
		,ISNULL(accbal.AmountDebet, 0) AS AmountDebet
		,ISNULL(accbal.AmountCredit, 0) AS AmountCredit
		,ISNULL(accbal.AmountDebetCompCurr, 0) AS AmountDebetCompCurr
		,ISNULL(accbal.AmountCreditCompCurr, 0) AS AmountCreditCompCurr
		,ISNULL(accbal.AmountBalanceCompCurr,0) AS AmountBalanceCompCurr
		,CASE 
			WHEN coagl.AccountSubTyp = 'AS' THEN ISNULL(accbal.AmountBalanceCompCurr,0)
			WHEN coagl.AccountSubTyp = 'LI' THEN CASE WHEN coagl.AccountClass = 'EQX' THEN (ISNULL(accbal.AmountBalanceCompCurr,0)) ELSE (ISNULL(accbal.AmountBalanceCompCurr,0)) * -1 END
			WHEN coagl.AccountSubTyp = 'IN' THEN (ISNULL(accbal.AmountBalanceCompCurr,0)) * -1
			WHEN coagl.AccountSubTyp = 'EX' THEN ISNULL(accbal.AmountBalanceCompCurr,0)
		 END AS AmountBalanceCompCurrReport
FROM dbo.PTRFICOMPACCBALANCE AS accbal
LEFT JOIN dbo.PCMFILOCOAGL AS coagl
	ON coagl.GlAccount = accbal.GlAccount
WHERE (accbal.CompanyID = @CompanyID OR @CompanyID = '')
	AND accbal.FiscalYear = @FiscalYearLast
	AND accbal.FiscalPeriod = @FiscalPeriodLast

INSERT INTO @TBalance
SELECT coagl.AccountSubTyp
		,coagl.AccountClass		
		,coagl.Description AS GlAccoundDesc
		,coagl.GlAccount
		,ISNULL(SUM(accbal.AmountDebet), 0) AS AmountDebet
		,ISNULL(SUM(accbal.AmountCredit), 0) AS AmountCredit
		,ISNULL(SUM(accbal.AmountDebetCompCurr), 0) AS AmountDebetCompCurr
		,ISNULL(SUM(accbal.AmountCreditCompCurr), 0) AS AmountCreditCompCurr
		,ISNULL(SUM(accbal.AmountBalanceCompCurr),0) AS AmountBalanceCompCurr
		,ISNULL(SUM(accbal.AmountBalanceCompCurrReport), 0) AS AmountBalanceCompCurrReport
FROM dbo.PCMFILOCOAGL AS coagl
LEFT JOIN @TBalanceAmount AS accbal
	ON coagl.GlAccount = accbal.GlAccountID
GROUP BY coagl.AccountSubTyp
		,coagl.AccountClass		
		,coagl.Description
		,coagl.GlAccount


DECLARE @TPosting TABLE
(
	AccountSubTyp VARCHAR(20)
	,GlAccoundDesc VARCHAR(250)
	,GlAccountID VARCHAR(20)
	,AmountDebet DECIMAL(30,0)
	,AmountCredit DECIMAL(30,0)
	,AmountDebetCompCurr DECIMAL(30,0)
	,AmountCreditCompCurr DECIMAL(30,0)
)

INSERT INTO @TPosting	
SELECT coagl.AccountSubTyp
		,coagl.Description
		,posting.GlAccountID
		,SUM(posting.AmountDebet)
		,SUM(posting.AmountCredit)
		,SUM(posting.AmountDebetCompCurr)
		,SUM(posting.AmountCreditCompCurr)
		----,posting.AmountDebet,posting.AmountCredit,posting.AmountDebetCompCurr,posting.AmountCreditCompCurr
FROM dbo.PTRFILOPOSTING AS posting
LEFT JOIN dbo.PCMFILOCOAGL AS coagl
	ON posting.GlAccountID = coagl.GlAccount
WHERE posting.FiscalYear = @FiscalYear
	AND posting.FiscalPeriod = @FiscalPeriod
GROUP BY coagl.AccountSubTyp
		,coagl.Description
		,posting.GlAccountID

SELECT tb.*
		,CASE 
			WHEN tb.AccountSubTyp = 'AS' THEN ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountDebetCompCurr,0) - ISNULL(tp.AmountCreditCompCurr,0)
			WHEN tb.AccountSubTyp = 'LI' THEN CASE WHEN tb.AccountClass = 'EQX' THEN (ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountCreditCompCurr,0) + ISNULL(tp.AmountDebetCompCurr,0)) ELSE (ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountCreditCompCurr,0) - ISNULL(tp.AmountDebetCompCurr,0)) * -1 END
			WHEN tb.AccountSubTyp = 'IN' THEN (ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountCreditCompCurr,0) - ISNULL(tp.AmountDebetCompCurr,0)) * -1
			WHEN tb.AccountSubTyp = 'EX' THEN ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountDebetCompCurr,0) - ISNULL(tp.AmountCreditCompCurr,0)
		 END AmountBalanceNew

		 ,ISNULL(tp.AmountDebetCompCurr,0) AS AmountDebetNew
		 ,ISNULL(tp.AmountCreditCompCurr,0) AS AmountCreditNew
		--,CASE 
		--	WHEN tb.AccountSubTyp = 'AS' THEN ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountDebetCompCurr,0) - ISNULL(tp.AmountCreditCompCurr,0)
		--	WHEN tb.AccountSubTyp = 'EX' THEN ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountDebetCompCurr,0) - ISNULL(tp.AmountCreditCompCurr,0)
		--	ELSE 0
		-- END AmountDebetNew
		--,CASE 
		--	WHEN tb.AccountSubTyp = 'LI' THEN ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountCreditCompCurr,0) - ISNULL(tp.AmountDebetCompCurr,0) 
		--	WHEN tb.AccountSubTyp = 'IN' THEN ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountCreditCompCurr,0) - ISNULL(tp.AmountDebetCompCurr,0)
		--	ELSE 0
		-- END AmountCreditNew
		,@PeriodDesc AS PeriodDesc
		----,tp.AmountDebet,tp.AmountCredit,tp.AmountDebetCompCurr,tp.AmountCreditCompCurr
FROM @TBalance AS tb
LEFT JOIN @TPosting AS tp
	ON tp.AccountSubTyp = tb.AccountSubTyp
	AND tp.GlAccountID = tb.GlAccountID