MinovaEasy

??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