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