ALTER PROCEDURE [dbo].[PRPTFIBUKUBESARSLV2]
(
	@FiscalYear VARCHAR(20)
	,@FiscalPeriod VARCHAR(20)
	,@FiscalPeriodTo VARCHAR(20)
	,@CompanyID VARCHAR(20)
	,@GlAccount VARCHAR(20)
	,@BusinessUnit VARCHAR(20)
	,@AccountGroup VARCHAR(20)
	,@SubLedgerType VARCHAR(20)
	,@CostCenter VARCHAR(20)
	,@SubLedgerID VARCHAR(20)
)
AS

--DECLARE @FiscalYear VARCHAR(20) = '2024';
--DECLARE @FiscalPeriod VARCHAR(20) = '01';
--DECLARE @FiscalPeriodTo VARCHAR(20) = '01';
--DECLARE @CompanyID VARCHAR(20) = '1000';
--DECLARE @GlAccount VARCHAR(20) = '1-1410';
--DECLARE @BusinessUnit VARCHAR(20) = '';
--DECLARE @AccountGroup VARCHAR(20) = '';
--DECLARE @SubLedgerType VARCHAR(20) = '01';
--DECLARE @CostCenter VARCHAR(20) = '';
--DECLARE @SubLedgerID VARCHAR(20) = '';

DECLARE @BeforePeriod VARCHAR(20) = SUBSTRING(CONVERT(NVARCHAR(8), DATEADD(d, -1, @FiscalYear + @FiscalPeriod + '01'), 112), 5, 2);
DECLARE @BeforeYear VARCHAR(20) = LEFT(CONVERT(NVARCHAR(8), DATEADD(d, -1, @FiscalYear + @FiscalPeriod + '01'), 112), 4);


DECLARE @TblSaldo TABLE
(
    RowNumber DECIMAL(18, 0),
    GLAccountID VARCHAR(30),
    GLAccounntDesc VARCHAR(250),
    DocNo VARCHAR(30),
    DocDesc VARCHAR(500),
    FiscalYear VARCHAR(20),
    FiscalPeriod VARCHAR(20),
    PostingDate VARCHAR(50),
    Currency VARCHAR(20),
    AmountDebet DECIMAL(30, 2),
    AmountCredit DECIMAL(30, 2),
    AmountBalance DECIMAL(30, 2),
    CompCurr VARCHAR(20),
    AmountDebetCompCurr DECIMAL(30, 2),
    AmountCreditCompCurr DECIMAL(30, 2),
    AmountBalanceCompCurr DECIMAL(30, 2),
    subledgertype VARCHAR(50),
    subledgerid VARCHAR(50),
    subledgerdesc VARCHAR(250),
    AccSubType VARCHAR(20),
    AccClass VARCHAR(20),
	ItemDesc VARCHAR(500)
)

DECLARE @TblSaldoAwal TABLE ---- buat peride initial balance
(
    RowNumber DECIMAL(18, 0),
    GLAccountID VARCHAR(30),
    GLAccounntDesc VARCHAR(250),
    DocNo VARCHAR(30),
    DocDesc VARCHAR(500),
    FiscalYear VARCHAR(20),
    FiscalPeriod VARCHAR(20),
    PostingDate VARCHAR(50),
    Currency VARCHAR(20),
    AmountDebet DECIMAL(30, 2),
    AmountCredit DECIMAL(30, 2),
    AmountBalance DECIMAL(30, 2),
    CompCurr VARCHAR(20),
    AmountDebetCompCurr DECIMAL(30, 2),
    AmountCreditCompCurr DECIMAL(30, 2),
    AmountBalanceCompCurr DECIMAL(30, 2),
    subledgertype VARCHAR(50),
    subledgerid VARCHAR(50),
    subledgerdesc VARCHAR(250),
    AccSubType VARCHAR(20),
    AccClass VARCHAR(20),
	ItemDesc VARCHAR(500)
)

DECLARE @TblSaldoTanpaSL TABLE
(
    RowNumber DECIMAL(18, 0),
    GLAccountID VARCHAR(30),
    GLAccounntDesc VARCHAR(250),
    DocNo VARCHAR(30),
    DocDesc VARCHAR(500),
    FiscalYear VARCHAR(20),
    FiscalPeriod VARCHAR(20),
    PostingDate VARCHAR(50),
    Currency VARCHAR(20),
    AmountDebet DECIMAL(30, 2),
    AmountCredit DECIMAL(30, 2),
    AmountBalance DECIMAL(30, 2),
    CompCurr VARCHAR(20),
    AmountDebetCompCurr DECIMAL(30, 2),
    AmountCreditCompCurr DECIMAL(30, 2),
    AmountBalanceCompCurr DECIMAL(30, 2),
    subledgertype VARCHAR(50),
    subledgerid VARCHAR(50),
    subledgerdesc VARCHAR(250),
    AccSubType VARCHAR(20),
    AccClass VARCHAR(20),
	ItemDesc VARCHAR(500)
)

DECLARE @TblSaldoSum TABLE
(
    AccSubType VARCHAR(20),
    AccClass VARCHAR(20),
    GLAccountID VARCHAR(50),
	SLType VARCHAR(20),
	SLID VARCHAR(20),
	SLIDDesc VARCHAR(250),
    AmountDebet DECIMAL(30, 2),
    AmountCredit DECIMAL(30, 2),
    AmountBalance DECIMAL(30, 2),
    AmountDebetCompCurr DECIMAL(30, 2),
    AmountCreditCompCurr DECIMAL(30, 2),
    AmountBalanceCompCurr DECIMAL(30, 2)
)

DECLARE @TblSaldoSumTanpaSL TABLE
(
    AccSubType VARCHAR(20),
    AccClass VARCHAR(20),
    GLAccountID VARCHAR(50),
    AmountDebet DECIMAL(30, 2),
    AmountCredit DECIMAL(30, 2),
    AmountBalance DECIMAL(30, 2),
    AmountDebetCompCurr DECIMAL(30, 2),
    AmountCreditCompCurr DECIMAL(30, 2),
    AmountBalanceCompCurr DECIMAL(30, 2)
)

DECLARE @TPosting TABLE
(
    rowno DECIMAL(18, 0),
    [DocNo] [VARCHAR](15) NOT NULL,
    [DocItemID] [BIGINT] NOT NULL,
    [DocType] [VARCHAR](5) NULL,
    [DocDate] [VARCHAR](8) NULL,
    [DocStatus] [VARCHAR](2) NULL,
    [FiscalYear] [VARCHAR](5) NULL,
    [FiscalPeriod] [VARCHAR](5) NULL,
    [CompanyID] [VARCHAR](5) NULL,
    [BusinessUnit] [VARCHAR](10) NULL,
    [CostCenter] [VARCHAR](10) NULL,
    [SLType] [VARCHAR](30) NULL,
    [SLID] [VARCHAR](30) NULL,
    [PostingDate] [VARCHAR](8) NULL,
    [GLAccountID] [VARCHAR](20) NULL,
    [Currency] [VARCHAR](5) NULL,
    [AmountDebet] [DECIMAL](18, 2) NULL,
    [AmountCredit] [DECIMAL](18, 2) NULL,
    [AmountDebetCompCurr] [DECIMAL](18, 2) NULL,
    [AmountCreditCompCurr] [DECIMAL](18, 2) NULL,
    [CreateBy] [VARCHAR](18) NULL,
    [CreateDate] [VARCHAR](14) NULL,
    [ChangeBy] [VARCHAR](18) NULL,
    [ChangeDate] [VARCHAR](18) NULL,
    [CompanyCurrency] [VARCHAR](5) NULL,
    [ItemStatus] [VARCHAR](2) NULL
	,CashActivityType  [VARCHAR](50) 
	,CashActivityDetail [VARCHAR](50)
);

INSERT INTO @TPosting
SELECT DISTINCT
       CONVERT(DECIMAL(22,0),'2' + CONVERT(VARCHAR(22),ROW_NUMBER() OVER (PARTITION BY post.GLAccountID, post.SLType, post.SLID ORDER BY post.PostingDate, post.GLAccountID, post.SLType, post.SLID ASC))),
       *
FROM dbo.PTRFILOPOSTING AS post
WHERE post.FiscalYear = @FiscalYear
      AND (post.FiscalPeriod >= @FiscalPeriod AND post.FiscalPeriod <= @FiscalPeriodTo)
      AND (post.GLAccountID = @GlAccount OR @GlAccount = '')
      AND (post.CostCenter = @CostCenter OR @CostCenter = '')
      AND (post.CompanyID = @CompanyID OR @CompanyID = '')
	  AND (post.SLType = @SubLedgerType OR @SubLedgerType = '')
	  AND (post.SLID = @SubLedgerID OR @SubLedgerID = '')
	  --AND post.GLAccountID IN ('1-1410','1-1020') --('4-1001','1-1012','3-1000') 

INSERT INTO @TblSaldo
SELECT DISTINCT
       docpost.rowno,
       gl.GlAccount,
       gl.Description,
       docpost.DocNo,
       finhead.Description AS DocDesc,
       docpost.FiscalYear,
       docpost.FiscalPeriod,
       dbo.fn_formatdatetime_indonesia(docpost.PostingDate, 'dd/mm/yyyy') AS PostingDate,
       curr.CurrencyDescription,
       docpost.AmountDebet,
       docpost.AmountCredit,
       0,
       compcurr.CurrencyDescription,
       docpost.AmountDebetCompCurr,
       docpost.AmountCreditCompCurr,
       0,
       ISNULL(fltyp.Description,'') AS subledgertyped,
       docpost.SLID AS subledgerid,
       ISNULL(SL.[Description],'') AS subledgerdesc,
       gl.AccountSubTyp,
       gl.AccountClass,   
	   finitem.Description itemdesc
FROM dbo.PCMFILOCOAGL AS gl
    LEFT JOIN @TPosting AS docpost
        ON gl.GlAccount = docpost.GLAccountID
	LEFT JOIN dbo.PTRFINANCEHEAD AS finhead
		ON docpost.DocNo = finhead.DocNo
	LEFT JOIN dbo.PTRFINANCEITEM AS finitem
		ON docpost.DocNo = finitem.DocNo
    LEFT JOIN dbo.PHRPYCURR AS curr
        ON curr.Currency = docpost.Currency
    LEFT JOIN dbo.PHRPYCURR AS compcurr
        ON compcurr.Currency = docpost.CompanyCurrency
    LEFT JOIN PCMFILOSLTYPE AS fltyp
        ON docpost.SLType = fltyp.SubLedgerType
    LEFT JOIN PMDSL0001 AS SL
        ON SL.SubLedgerID = docpost.SLID
WHERE (gl.AccountClass = @AccountGroup OR @AccountGroup = '')
	AND ( gl.GlAccount = @GlAccount OR @GlAccount = '' )
	AND ( docpost.SLType = @SubLedgerType OR @SubLedgerType = '' )
	AND ( docpost.SLID = @SubLedgerID OR @SubLedgerID = '' )
	AND ( docpost.FiscalYear = @FiscalYear OR @FiscalYear = '' )
	AND ( docpost.FiscalPeriod >= @FiscalPeriod OR docpost.FiscalPeriod <= @FiscalPeriodTo )
	AND ( docpost.CostCenter = @CostCenter OR @CostCenter = '')
	AND ( finhead.FiscalYear = @FiscalYear OR @FiscalYear = '')
	AND ( finhead.FiscalPeriod >= @FiscalPeriod OR finhead.FiscalPeriod <= @FiscalPeriodTo )
	AND ( finitem.FiscalYear = @FiscalYear OR @FiscalYear = '')
	AND ( finitem.FiscalPeriod >= @FiscalPeriod OR finitem.FiscalPeriod <= @FiscalPeriodTo )
ORDER BY gl.GlAccount;


DECLARE @TableGL TABLE
(
    GLAccountID VARCHAR(20) --, AccSubType VARCHAR(20), AccClass VARCHAR(20)
)

IF (@SubLedgerType <> '')
BEGIN
    INSERT INTO @TableGL
    SELECT DISTINCT GLAccountID --, AccSubType, AccClass
    FROM @TblSaldo
	--WHERE GLAccountID IN ('1-1410','1-1020') --('4-1001','1-1012','3-1000') 
END;
ELSE
BEGIN
    INSERT INTO @TableGL
    SELECT DISTINCT GlAccount --, AccountSubTyp, AccountClass
    FROM PCMFILOCOAGL 
	--WHERE GLAccount IN ('1-1410','1-1020') --('4-1001','1-1012','3-1000')  
END


INSERT INTO @TblSaldoAwal ---- buat peride initial balance
SELECT DISTINCT
       '1',
       gl.GlAccount,
       gl.Description,
       '',
       'Saldo Awal',
       ISNULL(saldoawal.FiscalYear, @BeforeYear) AS FiscalYear,
       ISNULL(saldoawal.FiscalPeriod, @BeforePeriod) AS FiscalPeriod,
       '',
       saldoawal.Currency,
	   0,   --saldoawal.AmountDebet ,
	   0,   --saldoawal.AmountCredit ,
       CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0 ELSE ISNULL(saldoawal.AmountBalance, 0) END, --saldoawal.AmountBalance ,
       saldoawal.CompanyCurrency,
	   0,   --saldoawal.AmountDebetCompCurr ,
	   0,   --saldoawal.AmountCreditCompCurr ,
       CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0 ELSE ISNULL(saldoawal.AmountBalanceCompCurr, 0) END, --saldoawal.AmountBalanceCompCurr ,
	   ISNULL(fltyp.Description,'') AS SLType,
       ISNULL(saldoawal.SLID,'') AS SLID,
       ISNULL(SL.Description,'') AS SLIDDesc,
       gl.AccountSubTyp,
       gl.AccountClass,
	   ''
FROM dbo.PCMFILOCOAGL AS gl
    INNER JOIN @TableGL AS tgl
        ON gl.GlAccount = tgl.GLAccountID
    LEFT JOIN dbo.PTRFICOMPACCBALANCE AS saldoawal
        ON gl.GlAccount = saldoawal.GlAccount
        AND saldoawal.FiscalPeriod = @BeforePeriod
        AND saldoawal.FiscalYear = @BeforeYear
        AND ( saldoawal.BusinessUnit = @BusinessUnit OR @BusinessUnit = '' )
        AND ( saldoawal.CompanyID = @CompanyID OR @CompanyID = '' )
		AND ( saldoawal.SLType = @SubLedgerType OR @SubLedgerType = '')
	    AND ( saldoawal.SLID = @SubLedgerID OR @SubLedgerID = '')
	LEFT JOIN PCMFILOSLTYPE AS fltyp
        ON saldoawal.SLType = fltyp.SubLedgerType
    LEFT JOIN PMDSL0001 AS SL
        ON SL.SubLedgerID = saldoawal.SLID
WHERE ( gl.AccountClass = @AccountGroup OR @AccountGroup = '' )
      AND ( gl.GlAccount = @GlAccount OR @GlAccount = '' )
      AND ( saldoawal.CostCenter = @CostCenter OR @CostCenter = '' )
	  

INSERT INTO @TblSaldoAwal ---- buat peride initial balance
SELECT DISTINCT
       '0',
       gl.GlAccount,
       gl.Description,
       '',
       'Saldo Awal',
       ISNULL(saldoawal.FiscalYear, @BeforeYear) AS FiscalYear,
       ISNULL(saldoawal.FiscalPeriod, @BeforePeriod) AS FiscalPeriod,
       '',
       saldoawal.Currency,
	   0,   --saldoawal.AmountDebet ,
	   0,   --saldoawal.AmountCredit ,
       CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0 ELSE ISNULL(saldoawal.AmountBalance, 0) END, --saldoawal.AmountBalance ,
       saldoawal.CompanyCurrency,
	   0,   --saldoawal.AmountDebetCompCurr ,
	   0,   --saldoawal.AmountCreditCompCurr ,
       CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0 ELSE ISNULL(saldoawal.AmountBalanceCompCurr, 0) END, --saldoawal.AmountBalanceCompCurr ,
	   '' AS SLType,
       '' AS SLID,
       '' AS SLIDDesc,
       gl.AccountSubTyp,
       gl.AccountClass,
	   ''
FROM dbo.PCMFILOCOAGL AS gl
    INNER JOIN @TableGL AS tgl
        ON gl.GlAccount = tgl.GLAccountID
    LEFT JOIN dbo.PTRFICOMPACCBALANCE AS saldoawal
        ON gl.GlAccount = saldoawal.GlAccount
        AND saldoawal.FiscalPeriod = @BeforePeriod
        AND saldoawal.FiscalYear = @BeforeYear
        AND ( saldoawal.BusinessUnit = @BusinessUnit OR @BusinessUnit = '' )
        AND ( saldoawal.CompanyID = @CompanyID OR @CompanyID = '' )
		AND ( saldoawal.SLType = @SubLedgerType OR @SubLedgerType = '')
	    AND ( saldoawal.SLID = @SubLedgerID OR @SubLedgerID = '')
WHERE ( gl.AccountClass = @AccountGroup OR @AccountGroup = '' )
      AND ( gl.GlAccount = @GlAccount OR @GlAccount = '' )
      AND ( saldoawal.CostCenter = @CostCenter OR @CostCenter = '' )


DECLARE @IsBeforePeriodExist DECIMAL(22,0) ---- buat periode initial balance
SELECT DISTINCT @IsBeforePeriodExist = FiscalPeriod FROM @TblSaldoAwal ---- buat periode initial balance

IF(@IsBeforePeriodExist IS NULL)  ---- buat periode initial balance
BEGIN
	INSERT INTO @TblSaldo
	SELECT DISTINCT
		   '1',
		   gl.GlAccount,
		   gl.Description,
		   '',
		   'Saldo Awal',
		   saldoawal.FiscalYear,
		   saldoawal.FiscalPeriod,
		   '',
		   saldoawal.Currency,
		   0,   --saldoawal.AmountDebet ,
		   0,   --saldoawal.AmountCredit ,
		   CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0
			   ELSE ISNULL(saldoawal.AmountBalance, 0)
		   END, --saldoawal.AmountBalance ,
		   saldoawal.CompanyCurrency,
		   0,   --saldoawal.AmountDebetCompCurr ,
		   0,   --saldoawal.AmountCreditCompCurr ,
		   CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0
			   ELSE ISNULL(saldoawal.AmountBalanceCompCurr, 0)
		   END, --saldoawal.AmountBalanceCompCurr ,
		   ISNULL(fltyp.Description,'') AS SLType,
		   ISNULL(saldoawal.SLID,'') AS SLID,
		   ISNULL(SL.Description,'') AS SLIDDesc,
		   gl.AccountSubTyp,
		   gl.AccountClass,
		   ''
	FROM dbo.PCMFILOCOAGL AS gl
		INNER JOIN @TableGL AS tgl
			ON gl.GlAccount = tgl.GLAccountID
		LEFT JOIN dbo.PTRFICOMPACCBALANCE AS saldoawal
			ON gl.GlAccount = saldoawal.GlAccount
			   AND saldoawal.FiscalPeriod = @FiscalPeriod
			   AND saldoawal.FiscalYear = @FiscalYear
			   AND ( saldoawal.BusinessUnit = @BusinessUnit OR @BusinessUnit = '' )
			   AND ( saldoawal.CompanyID = @CompanyID OR @CompanyID = '' )
			   AND ( saldoawal.SLType = @SubLedgerType OR @SubLedgerType = '')
			   AND ( saldoawal.SLID = @SubLedgerID OR @SubLedgerID = '')
		LEFT JOIN PCMFILOSLTYPE AS fltyp
			ON saldoawal.SLType = fltyp.SubLedgerType
		LEFT JOIN PMDSL0001 AS SL
			ON SL.SubLedgerID = saldoawal.SLID
	WHERE ( gl.AccountClass = @AccountGroup OR @AccountGroup = '' )
		  AND ( gl.GlAccount = @GlAccount OR @GlAccount = '' )
		  AND ( saldoawal.CostCenter = @CostCenter OR @CostCenter = '' )

	INSERT INTO @TblSaldo
	SELECT DISTINCT
		   '0',
		   gl.GlAccount,
		   gl.Description,
		   '',
		   'Saldo Awal',
		   ISNULL(saldoawal.FiscalYear, @BeforeYear) AS FiscalYear,
		   ISNULL(saldoawal.FiscalPeriod, @BeforePeriod) AS FiscalPeriod,
		   '',
		   saldoawal.Currency,
		   0,   --saldoawal.AmountDebet ,
		   0,   --saldoawal.AmountCredit ,
		   CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0
			   ELSE ISNULL(saldoawal.AmountBalance, 0)
		   END, --saldoawal.AmountBalance ,
		   saldoawal.CompanyCurrency,
		   0,   --saldoawal.AmountDebetCompCurr ,
		   0,   --saldoawal.AmountCreditCompCurr ,
		   CASE WHEN gl.AccountType = 'PL' AND @FiscalPeriod = '01' THEN 0
			   ELSE ISNULL(saldoawal.AmountBalanceCompCurr, 0)
		   END, --saldoawal.AmountBalanceCompCurr ,
		   '' AS SLType,
		   '' AS SLID,
		   '' AS SLIDDesc,
		   gl.AccountSubTyp,
		   gl.AccountClass,
		   ''
	FROM dbo.PCMFILOCOAGL AS gl
		INNER JOIN @TableGL AS tgl
			ON gl.GlAccount = tgl.GLAccountID
		LEFT JOIN dbo.PTRFICOMPACCBALANCE AS saldoawal
			ON gl.GlAccount = saldoawal.GlAccount
			   AND saldoawal.FiscalPeriod = @FiscalPeriod
			   AND saldoawal.FiscalYear = @FiscalYear
			   AND ( saldoawal.BusinessUnit = @BusinessUnit OR @BusinessUnit = '' )
			   AND ( saldoawal.CompanyID = @CompanyID OR @CompanyID = '' )
			   AND ( saldoawal.SLType = @SubLedgerType OR @SubLedgerType = '')
		       AND ( saldoawal.SLID = @SubLedgerID OR @SubLedgerID = '')
	WHERE ( gl.AccountClass = @AccountGroup OR @AccountGroup = '' )
		  AND ( gl.GlAccount = @GlAccount OR @GlAccount = '' )
		  AND ( saldoawal.CostCenter = @CostCenter OR @CostCenter = '' )

END
ELSE
BEGIN
	INSERT INTO @TblSaldo
	SELECT * FROM @TblSaldoAwal
END

INSERT INTO @TblSaldoSum
SELECT DISTINCT
       ts.AccSubType,
       ts.AccClass,
       ts.GLAccountID, 
	   ts.subledgertype,
	   ts.subledgerid,
	   ts.subledgerdesc,
       SUM(ts.AmountDebet),
       SUM(ts.AmountCredit),
       SUM(ts.AmountBalance),
       SUM(ts.AmountDebetCompCurr),
       SUM(ts.AmountCreditCompCurr),
       SUM(ts.AmountBalanceCompCurr)
FROM @TblSaldo AS ts
WHERE ts.RowNumber <> '0'
GROUP BY ts.AccSubType,
         ts.AccClass,
         ts.GLAccountID,
		 ts.subledgertype,
		 ts.subledgerid,
		 ts.subledgerdesc

INSERT INTO @TblSaldoSumTanpaSL
SELECT DISTINCT
       ts.AccSubType,
       ts.AccClass,
       ts.GLAccountID,
       SUM(ts.AmountDebet),
       SUM(ts.AmountCredit),
       SUM(ts.AmountBalance),
       SUM(ts.AmountDebetCompCurr),
       SUM(ts.AmountCreditCompCurr),
       SUM(ts.AmountBalanceCompCurr)
FROM @TblSaldo AS ts
GROUP BY ts.AccSubType,
         ts.AccClass,
         ts.GLAccountID

INSERT INTO @TblSaldo
SELECT DISTINCT
       '3999999999',
       saldoawal.GLAccountID,
       saldoawal.GLAccounntDesc,
       '',
       'Saldo Akhir',
       @FiscalYear,
       @FiscalPeriodTo,
       '',
       saldoawal.Currency,
       saldosum.AmountDebet,
       saldosum.AmountCredit,
       --saldoawal.AmountBalance AS saldoawal,
       CASE
           WHEN saldoawal.AccSubType = 'AS' THEN ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountDebet, 0) - ISNULL(saldosum.AmountCredit, 0)
           WHEN saldoawal.AccSubType = 'LI' THEN 
				CASE WHEN saldoawal.AccClass = 'EQX' THEN (ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountCredit, 0) + ISNULL(saldosum.AmountDebet, 0)) 
				ELSE (ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountCredit, 0) - ISNULL(saldosum.AmountDebet, 0)) * -1 END
           WHEN saldoawal.AccSubType = 'IN' THEN (ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountCredit, 0) - ISNULL(saldosum.AmountDebet, 0)) * -1
           WHEN saldoawal.AccSubType = 'EX' THEN ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountDebet, 0) - ISNULL(saldosum.AmountCredit, 0)
       END AmountBalanceNew,
       saldoawal.CompCurr,
       saldosum.AmountDebetCompCurr,
       saldosum.AmountCreditCompCurr,
       --saldoawal.AmountBalanceCompCurr AS saldoawalcompcurr ,
       CASE
           WHEN saldoawal.AccSubType = 'AS' THEN ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountDebetCompCurr, 0) - ISNULL(saldosum.AmountCreditCompCurr, 0)
           WHEN saldoawal.AccSubType = 'LI' THEN 
				CASE WHEN saldoawal.AccClass = 'EQX' THEN (ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountCreditCompCurr, 0) + ISNULL(saldosum.AmountDebetCompCurr, 0))
                ELSE (ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountCreditCompCurr, 0) - ISNULL(saldosum.AmountDebetCompCurr, 0) ) * -1
                END
           WHEN saldoawal.AccSubType = 'IN' THEN (ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountCreditCompCurr, 0) - ISNULL(saldosum.AmountDebetCompCurr, 0) ) * -1
           WHEN saldoawal.AccSubType = 'EX' THEN ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountDebetCompCurr, 0) - ISNULL(saldosum.AmountCreditCompCurr, 0)
       END AmountBalanceCompCurrNew,
       saldoawal.subledgertype,
       saldoawal.subledgerid,
       saldoawal.subledgerdesc,
       saldoawal.AccSubType,
       saldoawal.AccClass,
	   saldoawal.ItemDesc
FROM @TblSaldo AS saldoawal
LEFT JOIN @TblSaldoSum AS saldosum
    ON saldosum.AccSubType = saldoawal.AccSubType
    AND saldosum.AccClass = saldoawal.AccClass
WHERE saldoawal.DocDesc = 'Saldo Awal'
      AND saldoawal.GLAccountID = saldosum.GLAccountID
	  AND saldoawal.subledgerid = saldosum.SLID
	  AND RowNumber <> '0'

INSERT INTO @TblSaldo
SELECT DISTINCT
       '9999999999',
       saldoawal.GLAccountID,
       saldoawal.GLAccounntDesc,
       '',
       'Saldo Akhir',
       @FiscalYear,
       @FiscalPeriodTo,
       '',
       saldoawal.Currency,
       saldosum.AmountDebet,
       saldosum.AmountCredit,
       --saldoawal.AmountBalance AS saldoawal,
       CASE
           WHEN saldoawal.AccSubType = 'AS' THEN ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountDebet, 0) - ISNULL(saldosum.AmountCredit, 0)
           WHEN saldoawal.AccSubType = 'LI' THEN 
				CASE WHEN saldoawal.AccClass = 'EQX' THEN (ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountCredit, 0) + ISNULL(saldosum.AmountDebet, 0)) 
				ELSE (ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountCredit, 0) - ISNULL(saldosum.AmountDebet, 0)) * -1 END
           WHEN saldoawal.AccSubType = 'IN' THEN (ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountCredit, 0) - ISNULL(saldosum.AmountDebet, 0)) * -1
           WHEN saldoawal.AccSubType = 'EX' THEN ISNULL(saldoawal.AmountBalance, 0) + ISNULL(saldosum.AmountDebet, 0) - ISNULL(saldosum.AmountCredit, 0)
       END AmountBalanceNew,
       saldoawal.CompCurr,
       saldosum.AmountDebetCompCurr,
       saldosum.AmountCreditCompCurr,
       --saldoawal.AmountBalanceCompCurr AS saldoawalcompcurr ,
       CASE
           WHEN saldoawal.AccSubType = 'AS' THEN ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountDebetCompCurr, 0) - ISNULL(saldosum.AmountCreditCompCurr, 0)
           WHEN saldoawal.AccSubType = 'LI' THEN 
				CASE WHEN saldoawal.AccClass = 'EQX' THEN (ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountCreditCompCurr, 0) + ISNULL(saldosum.AmountDebetCompCurr, 0))
                ELSE (ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountCreditCompCurr, 0) - ISNULL(saldosum.AmountDebetCompCurr, 0) ) * -1
                END
           WHEN saldoawal.AccSubType = 'IN' THEN (ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountCreditCompCurr, 0) - ISNULL(saldosum.AmountDebetCompCurr, 0) ) * -1
           WHEN saldoawal.AccSubType = 'EX' THEN ISNULL(saldoawal.AmountBalanceCompCurr, 0) + ISNULL(saldosum.AmountDebetCompCurr, 0) - ISNULL(saldosum.AmountCreditCompCurr, 0)
       END AmountBalanceCompCurrNew,
       '999' AS subledgertype,
       '999' AS subledgerid,
       '999' AS subledgerdesc,
       saldoawal.AccSubType,
       saldoawal.AccClass,
	   saldoawal.ItemDesc
FROM @TblSaldo AS saldoawal
LEFT JOIN @TblSaldoSumTanpaSL AS saldosum
    ON saldosum.AccSubType = saldoawal.AccSubType
    AND saldosum.AccClass = saldoawal.AccClass
WHERE saldoawal.DocDesc = 'Saldo Awal'
      AND saldoawal.GLAccountID = saldosum.GLAccountID
	  AND RowNumber NOT IN ('1','1999999999')


SELECT DISTINCT
       a.RowNumber,
	   a.subledgertype,
	   a.subledgerid,
       a.subledgerdesc,
       a.GLAccountID,
       a.GLAccounntDesc,
       a.DocNo,
       a.DocDesc,
       a.FiscalYear,
       a.FiscalPeriod,
       a.PostingDate,
       a.Currency,
       a.AmountDebet,
       a.AmountCredit,
       a.AmountBalance,
       a.CompCurr,
       a.AmountDebetCompCurr,
       a.AmountCreditCompCurr,
	   a.AmountBalanceCompCurr,
       a.AccSubType,
       a.AccClass,
	   a.ItemDesc
FROM @TblSaldo a
ORDER BY a.GLAccountID
		,a.subledgerid
		,a.RowNumber