PRPTFIBUKUBESAR_20240626_WithCashActDiTblPosting.sql - MinovaEasy - Redmine

Feature #2818 » PRPTFIBUKUBESAR_20240626_WithCashActDiTblPosting.sql

Tri Rizqiaty, 06/26/2024 03:17 PM

    
      ??USE [MinovaES_Easy]

      GO

      /****** Object:  StoredProcedure [dbo].[PRPTFIBUKUBESAR]    Script Date: 26/06/2024 13.48.50 ******/

      SET ANSI_NULLS ON

      GO

      SET QUOTED_IDENTIFIER ON

      GO

      ALTER PROCEDURE [dbo].[PRPTFIBUKUBESAR]

      (

      	@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)

      )

      AS

      --DECLARE @FiscalYear VARCHAR(20) = '2022';

      --DECLARE @FiscalPeriod VARCHAR(20) = '02';

      --DECLARE @FiscalPeriodTo VARCHAR(20) = '02';

      --DECLARE @CompanyID VARCHAR(20) = '';

      --DECLARE @GlAccount VARCHAR(20) = '';

      --DECLARE @BusinessUnit VARCHAR(20) = '';

      --DECLARE @AccountGroup VARCHAR(20) = '';

      --DECLARE @SubLedgerType VARCHAR(20) = '';

      --DECLARE @CostCenter 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 @TblSaldoSum 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

             ROW_NUMBER() OVER (PARTITION BY post.GLAccountID ORDER BY post.PostingDate, post.GLAccountID ASC),

             *

      FROM dbo.PTRFILOPOSTING AS post

      WHERE post.FiscalYear = @FiscalYear

            --AND (CONVERT(DECIMAL(22,0),post.FiscalPeriod) BETWEEN CONVERT(DECIMAL(22,0),@FiscalPeriod) AND CONVERT(DECIMAL(22,0),@FiscalPeriodTo))

            AND (post.FiscalPeriod

            BETWEEN @FiscalPeriod AND @FiscalPeriodTo

                )

            AND

            (

                post.GLAccountID = @GlAccount

                OR @GlAccount = ''

            )

            AND

            (

                post.CostCenter = @CostCenter

                OR @CostCenter = ''

            )

            AND

            (

                post.CompanyID = @CompanyID

                OR @CompanyID = ''

            );

      INSERT INTO @TblSaldo

      SELECT DISTINCT

             docpost.rowno,

             gl.GlAccount,

             gl.Description,

             docpost.DocNo,

             dochead.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,

             fltyp.Description AS subledgertype,

             fintem.SubLedgerID AS subledgerid,

             SL.[Description] AS subledgerdesc,

             gl.AccountSubTyp,

             gl.AccountClass,   

      	   fintem.Description itemdesc

      FROM dbo.PCMFILOCOAGL AS gl

          LEFT JOIN @TPosting AS docpost

              ON gl.GlAccount = docpost.GLAccountID

          LEFT JOIN dbo.PTRFINANCEHEAD AS dochead

              ON dochead.DocNo = docpost.DocNo

                 AND

                 (

                     docpost.FiscalYear = @FiscalYear

                     OR @FiscalYear = ''

                 )

                 AND (CONVERT(DECIMAL(22, 0), docpost.FiscalPeriod) >= CONVERT(DECIMAL(22, 0), @FiscalPeriod))

      		   --LEFT JOIN PTRFINANCEITEM AS docitem WITH (NOLOCK) ON dochead.DocNo = docitem.DocNo

      		   --	and docitem.GLAccountID =  gl.GlAccount

          LEFT JOIN dbo.PHRPYCURR AS curr

              ON curr.Currency = docpost.Currency

          LEFT JOIN dbo.PHRPYCURR AS compcurr

              ON compcurr.Currency = docpost.CompanyCurrency

          LEFT JOIN dbo.PTRFINANCEITEM AS fintem

              ON fintem.DocNo = dochead.DocNo

                 AND fintem.GLAccountID = gl.GlAccount AND docpost.DocItemID = fintem.DocItemID

          LEFT JOIN PCMFILOSLTYPE AS fltyp

              ON fintem.SubLedgerType = fltyp.SubLedgerType

                 AND fintem.GLAccountID = gl.GlAccount

          LEFT JOIN PMDSL0001 AS SL

              ON SL.SubLedgerID = fintem.SubLedgerID

      WHERE (

                gl.AccountClass = @AccountGroup

                OR @AccountGroup = ''

            )

            AND

            (

                gl.GlAccount = @GlAccount

                OR @GlAccount = ''

            )

            AND

            (

                fintem.SubLedgerType = @SubLedgerType

                OR @SubLedgerType = ''

            )

      --            AND ( docpost.FiscalYear = @FiscalYear OR @FiscalYear = '' )

      --            AND ( docpost.FiscalPeriod = @FiscalPeriod OR @FiscalPeriod = '' )

      --AND (docpost.CostCenter = @CostCenter OR @CostCenter = '')

      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;

      END;

      ELSE

      BEGIN

          INSERT INTO @TableGL

          SELECT DISTINCT

                 GlAccount --, AccountSubTyp, AccountClass

          FROM PCMFILOCOAGL;

      END;

      INSERT INTO @TblSaldo

      SELECT DISTINCT

             '0',

             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 ,

             '',

             '',

             '',

             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 = ''

                 )

      WHERE (

                gl.AccountClass = @AccountGroup

                OR @AccountGroup = ''

            )

            AND

            (

                gl.GlAccount = @GlAccount

                OR @GlAccount = ''

            )

            AND

            (

                saldoawal.CostCenter = @CostCenter

                OR @CostCenter = ''

            );

      INSERT INTO @TblSaldoSum

      SELECT DISTINCT

             ts.AccSubType,

             ts.AccClass,

             ts.GLAccountID, --,ts.FiscalYear ,ts.FiscalPeriod

             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; --,ts.FiscalYear ,ts.FiscalPeriod

      INSERT INTO @TblSaldo

      SELECT DISTINCT

             '9999999999999',

             saldoawal.GLAccountID,

             saldoawal.GLAccounntDesc,

             '',

             'Saldo Akhir',

             @FiscalYear,

             @FiscalPeriodTo,

             '',

             saldoawal.Currency,

             saldosum.AmountDebet,

             saldosum.AmountCredit,

             --saldoawal.AmountBalance ,

             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 ,

             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.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;

      /*

      INSERT  INTO @TblSaldo

              SELECT DISTINCT

                      '9999999999999' ,

                      gl.GlAccount ,

                      gl.Description ,

                      '' ,

                      'Saldo Akhir' ,

                      saldoawal.FiscalYear ,

                      saldoawal.FiscalPeriod ,

                      '' ,

                      saldoawal.Currency ,

                      saldoawal.AmountDebet ,

                      saldoawal.AmountCredit ,

                      saldoawal.AmountBalance ,

                      saldoawal.CompanyCurrency ,

                      saldoawal.AmountDebetCompCurr ,

                      saldoawal.AmountCreditCompCurr ,

                      saldoawal.AmountBalanceCompCurr ,

                      '' ,

                      '' ,

      				''

              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 = ''

                                                                    )

              WHERE  ( gl.AccountClass = @AccountGroup

                        OR @AccountGroup = ''

                      )

                      AND ( gl.GlAccount = @GlAccount

                            OR @GlAccount = ''

                          )

      				AND (saldoawal.CostCenter = @CostCenter OR @CostCenter = '')

      */

      SELECT DISTINCT

          --- *, a.subledgerid

             a.RowNumber,

             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,

             --CASE WHEN a.AccSubType = 'IN' THEN a.AmountBalanceCompCurr  * -1 ELSE a.AmountBalanceCompCurr END AS AmountBalanceCompCurr,

             a.subledgertype,

             --a.subledgerid  ,

             CASE

                 WHEN a.subledgerid IS NULL THEN

                     ''

                 ELSE

                     a.subledgerid

             END AS subledgerid,

             a.subledgerdesc,

             a.AccSubType,

             a.AccClass,

      	   a.ItemDesc

      FROM @TblSaldo a

      ORDER BY a.GLAccountID,

               a.RowNumber;

(2-2/3)

Project

General

Profile

MinovaEasy

Feature #2818 » PRPTFIBUKUBESAR_20240626_WithCashActDiTblPosting.sql