USE [MinovaES_ERP_2021]
GO
/****** Object:  StoredProcedure [dbo].[PRPTFIBUKUBESAR]    Script Date: 26/06/2024 10.02.00 ******/
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
);

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;