PRPTFIBUKUBESARSLV2_20240913.sql - MinovaEasy - Redmine

Bug #2883 » PRPTFIBUKUBESARSLV2_20240913.sql

Tri Rizqiaty, 09/13/2024 11:22 AM

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

« Previous
1
…
4
5
6
Next »

(6-6/6)

Project

General

Profile

MinovaEasy

Bug #2883 » PRPTFIBUKUBESARSLV2_20240913.sql