TULISAN

??ALTER PROCEDURE [dbo].[PFILOGETACCCOMPCLOSING]

(

	@CompanyID VARCHAR(20)

	,@FiscalYear VARCHAR(20)

	,@FiscalPeriod VARCHAR(20)

)

AS

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

--DECLARE @FiscalYear VARCHAR(20) = '2025'

--DECLARE @FiscalPeriod VARCHAR(20) = '07'

DECLARE @TblResult TABLE

(

	CompanyID VARCHAR(20)

	,GlAccount VARCHAR(20)

	,Description VARCHAR(250)

	,StartDate VARCHAR(20)

	,EndDate VARCHAR(20)

	,CreateBy VARCHAR(250)

	,CreaeDate VARCHAR(20)

	,ChangeBy VARCHAR(250)

	,ChangeDate VARCHAR(20)

	,CashPlan VARCHAR(20)

	,BusinessUnit VARCHAR(20)

	,CostCenter VARCHAR(20)

	,SLID VARCHAR(20)

	,SLType VARCHAR(20)

)

INSERT INTO @TblResult

SELECT DISTINCT a.*,b.BusinessUnit, b.CostCenter, b.SLID, b.SLType 

FROM dbo.PCMFILOACCCOMPANY AS a 

LEFT JOIN dbo.PTRFICOMPACCBALANCE AS b ON a.GlAccount = b.GlAccount 

WHERE a.CompanyID = b.CompanyID

AND (a.CompanyID = @CompanyID OR @CompanyID = '')

--SELECT DISTINCT GLAccountID, CompanyID, BusinessUnit, CostCenter, a.SLType, SLID 

--FROM dbo.PTRFILOPOSTING AS a

--LEFT JOIN dbo.PMDSL0001 AS b

--	ON a.SLType = b.SLType

--	AND a.SLID = b.SubLedgerID

--WHERE (CompanyID = @CompanyID OR @CompanyID = '')

--	AND a.GLAccountID IN ('1-102103','9-2','4-10101')

DECLARE @TblPost TABLE

(

	GLAccountID VARCHAR(50)

	,CompanyID VARCHAR(20)

	,BusinessUnit VARCHAR(50)

	,CostCenter VARCHAR(50)

	,SLType VARCHAR(20)

	,SLID VARCHAR(20)

)

INSERT INTO @TblPost

SELECT DISTINCT GLAccountID, CompanyID, BusinessUnit, CostCenter, SLType, SLID 

FROM dbo.PTRFILOPOSTING 

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

AND FiscalPeriod = @FiscalPeriod AND FiscalYear = @FiscalYear

INSERT INTO @TblResult

SELECT a.*, b.BusinessUnit, b.CostCenter, b.SLID, b.SLType

FROM dbo.PCMFILOACCCOMPANY  AS a

LEFT JOIN @TblPost AS b

	ON a.GlAccount = b.GLAccountID 

LEFT JOIN @TblResult AS c

	ON a.GlAccount = c.GlAccount

WHERE a.CompanyID = b.CompanyID

	AND (b.SLType <> c.SLType OR b.SLID <> c.SLID)

	AND (a.CompanyID = @CompanyID OR @CompanyID = '')

INSERT INTO @TblResult

SELECT a.*, b.BusinessUnit, b.CostCenter, b.SLID, b.SLType

FROM dbo.PCMFILOACCCOMPANY  AS a

LEFT JOIN @TblPost AS b

	ON a.GlAccount = b.GLAccountID 

WHERE a.CompanyID = b.CompanyID

	AND (a.CompanyID = @CompanyID OR @CompanyID = '')

	AND (a.GlAccount NOT IN (SELECT GlAccount FROM @TblResult))

SELECT * FROM @TblResult

ORDER BY CompanyID, GlAccount, BusinessUnit, CostCenter, SLID, SLType