TULISAN

??ALTER PROCEDURE [dbo].[PDSFILOGETDEPOSIT]

(

	@CompanyID VARCHAR(20)

	,@CustomerID VARCHAR(50)

	,@SLID VARCHAR(50)

	,@GLAccountID VARCHAR(50)

)

AS 

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

--DECLARE @CustomerID VARCHAR(50) = '00000005'

--DECLARE @SLID VARCHAR(50) = '' ---- jika ini dikosongkan, customerid wajib isi

--DECLARE @GLAccountID VARCHAR(50) = '' ---- saat ini ambil dr tbl filointegrate dengan paymenttype hardcode = '05'

DECLARE @FiscalPeriod VARCHAR(20)

DECLARE @FiscalYear VARCHAR(20)

SELECT @FiscalPeriod = FiscalPeriod, @FiscalYear = FiscalYear

FROM dbo.PTRACCCONTROL WHERE CompanyID = @CompanyID

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

IF(@SLID = '' OR @SLID IS NULL)

BEGIN

	SELECT @SLID = SubLedgerID FROM dbo.PMDSL0001 WHERE CustomerID = @CustomerID

END

IF(@GLAccountID = '' OR @GLAccountID IS NULL)

BEGIN

	SELECT DISTINCT @GLAccountID = Account FROM dbo.PFILOINTEGRATE WHERE PaymentType = '05'

	--SET @GLAccountID = '2-104'

END

DECLARE @TBalance TABLE

(

	GlAccountID VARCHAR(20)

	,AmountBalanceCompCurr DECIMAL(30,0)

	,SLType VARCHAR(50)

	,SLID VARCHAR(50)

)

INSERT INTO @TBalance

SELECT  accbal.GlAccount

		,ISNULL(accbal.AmountBalanceCompCurr,0) AS AmountBalanceCompCurr

		,accbal.SLType

		,accbal.SLID

FROM dbo.PTRFICOMPACCBALANCE AS accbal

WHERE (accbal.CompanyID = @CompanyID OR @CompanyID = '')

	AND accbal.FiscalYear = @BeforeYear

	AND accbal.FiscalPeriod = @BeforePeriod

	AND accbal.GlAccount = @GLAccountID

	AND accbal.SLType = '01'

	AND accbal.SLID = @SLID

DECLARE @TPosting TABLE

(

	GlAccountID VARCHAR(20)

	,AmountDebet DECIMAL(30,0)

	,AmountCredit DECIMAL(30,0)

	,AmountDebetCompCurr DECIMAL(30,0)

	,AmountCreditCompCurr DECIMAL(30,0)

)

INSERT INTO @TPosting	

SELECT  posting.GlAccountID

		,SUM(posting.AmountDebet)

		,SUM(posting.AmountCredit)

		,SUM(posting.AmountDebetCompCurr)

		,SUM(posting.AmountCreditCompCurr)

FROM dbo.PTRFILOPOSTING AS posting

WHERE posting.FiscalYear = @FiscalYear

	AND posting.FiscalPeriod = @FiscalPeriod

	AND posting.GLAccountID = @GLAccountID

	AND posting.SLID = @SLID

	AND posting.SLType = '01'

GROUP BY posting.GlAccountID

DECLARE @LastSaldo DECIMAL(22,2)

SELECT @LastSaldo = (ISNULL(tb.AmountBalanceCompCurr,0) + ISNULL(tp.AmountCreditCompCurr,0) - ISNULL(tp.AmountDebetCompCurr,0))

FROM @TBalance AS tb

LEFT JOIN @TPosting AS tp

	ON tp.GlAccountID = tb.GlAccountID

SELECT @SLID AS SLID, @LastSaldo AS LastSaldo