ALTER PROC [dbo].[usp_Jiwa_Debtor_Transactions] @DebtorID CHAR(20), @AsAtDate DATETIME, @AgedOrUnagedOrAll TINYINT
AS
SET NOCOUNT ON

-- Because of Parameter Sniffing, we take the parameters and put them into local variables and use those instead.
DECLARE @local_DebtorID CHAR(20) = @DebtorID
DECLARE @local_AsAtDate DATETIME = @AsAtDate
DECLARE @local_AgedOrUnagedOrAll TINYINT = @AgedOrUnagedOrAll

SET NOCOUNT ON

--@local_AgedOrUnagedOrAll
--Aged = 0
--Unaged=1
--All=2

DECLARE @Results TABLE
(
	TransID CHAR(20) COLLATE DATABASE_DEFAULT NOT NULL,
	DebitCredit BIT NOT NULL,
	Source VARCHAR(255) NULL,
	Ref VARCHAR(200) NULL,
	Remark VARCHAR(200) NULL,
	TranDate DATETIME NOT NULL,
	InvRemitNo VARCHAR(50) NOT NULL,
	Amount DECIMAL(19,6) NOT NULL,
	AgedOut BIT NOT NULL,
	SourceID CHAR(36) COLLATE DATABASE_DEFAULT NULL,
	AllocatedAmount DECIMAL(19,6) NOT NULL,
	GSTAmount DECIMAL(19,6) NOT NULL,
	DueDate DATETIME NOT NULL,
	DebtorID CHAR(20) COLLATE DATABASE_DEFAULT NOT NULL,
	AccountNo VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL,
	SubType SMALLINT NOT NULL,
	FXAmount DECIMAL(19,6) NOT NULL,
	FXAllocAmount DECIMAL(19,6) NOT NULL,
	CurrencyID CHAR(36) COLLATE DATABASE_DEFAULT NULL,
	CurrencyRateUsed DECIMAL(19,6) NOT NULL,
	RealisedGainLoss DECIMAL(19,6) NOT NULL,
	UnRealisedGainLoss DECIMAL(19,6) NOT NULL,
	DecimalPlaces SMALLINT NOT NULL,
	FXDecimalPlaces SMALLINT NULL,
	ShortName VARCHAR(50) NULL,
	Note VARCHAR(MAX) NULL,
	Parent_DebtorID CHAR(36) COLLATE DATABASE_DEFAULT NULL,
	Parent_AccountNo VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL,
	PeriodNo INT NOT NULL
)

DECLARE @DebtorIsBranchAccount BIT

SELECT @DebtorIsBranchAccount = CASE DB_Main.DebtorID WHEN DB_Main.ParentDebtorID THEN 0 ELSE 1 END FROM DB_Main WHERE DebtorID = @local_DebtorID

PRINT @DebtorIsBranchAccount

IF @DebtorIsBranchAccount = 0
	INSERT INTO @Results
	SELECT DB_Trans.TransID, DebitCredit, Source, Ref, Remark, TranDate, InvRemitNo, Amount, AgedOut, SourceID, AllocatedAmount, 
		GSTAmount, DueDate, DB_Main.DebtorID, DB_Main.AccountNo, SubType, FXAmount, FXAllocAmount, DB_Trans.CurrencyID, DB_Trans.CurrencyRateUsed, RealisedGainLoss, 
		UnRealisedGainLoss, DB_Trans.DecimalPlaces, FXDecimalPlaces, FX_Currency.ShortName, Note, DB_Trans.DebtorID, DB_Main.AccountNo,
		dbo.ufn_JIWA_Debtors_TransactionPeriodNo(TranDate, DB_Main.DebtorID, @local_AsAtDate) [PeriodNo] 
	FROM DB_Trans 
	JOIN DB_Main ON (DB_Main.DebtorID = DB_Trans.ChildDebtorID) 
	LEFT JOIN FX_Currency ON (FX_Currency.RecID = DB_Trans.CurrencyID) 
	WHERE DB_Trans.DebtorID = @local_DebtorID
	AND (@local_AgedOrUnagedOrAll = 2 OR (@local_AgedOrUnagedOrAll = 0 AND AgedOut = 1) OR (@local_AgedOrUnagedOrAll = 1 AND AgedOut = 0))
ELSE
	INSERT INTO @Results
	SELECT DB_Trans.TransID, DebitCredit, Source, Ref, Remark, TranDate, InvRemitNo, Amount, AgedOut, SourceID, AllocatedAmount, 
		GSTAmount, DueDate, DB_Main.DebtorID, DB_Main.AccountNo, SubType, FXAmount, FXAllocAmount, DB_Trans.CurrencyID, DB_Trans.CurrencyRateUsed, RealisedGainLoss, 
		UnRealisedGainLoss, DB_Trans.DecimalPlaces, FXDecimalPlaces, FX_Currency.ShortName, Note, DB_Trans.DebtorID, DB_Main.AccountNo,
		dbo.ufn_JIWA_Debtors_TransactionPeriodNo(TranDate, DB_Main.DebtorID, @local_AsAtDate) [PeriodNo] 
	FROM DB_Trans 
	JOIN DB_Main ON (DB_Main.DebtorID = DB_Trans.ChildDebtorID) 
	LEFT JOIN FX_Currency ON (FX_Currency.RecID = DB_Trans.CurrencyID) 
	WHERE (DB_Trans.ChildDebtorID = @local_DebtorID OR DB_Trans.DebtorID = @local_DebtorID)
	OR DB_Trans.TransID IN (SELECT DebitTransID FROM DB_TransAlloc WHERE CreditTransID IN (SELECT TransID FROM DB_Trans WHERE (DB_Trans.ChildDebtorID = @local_DebtorID OR DB_Trans.DebtorID = @local_DebtorID)))
	OR DB_Trans.TransID IN (SELECT CreditTransID FROM DB_TransAlloc WHERE DebitTransID IN (SELECT TransID FROM DB_Trans WHERE (DB_Trans.ChildDebtorID = @local_DebtorID OR DB_Trans.DebtorID = @local_DebtorID)))
	AND (@local_AgedOrUnagedOrAll = 2 OR (@local_AgedOrUnagedOrAll = 0 AND AgedOut = 1) OR (@local_AgedOrUnagedOrAll = 1 AND AgedOut = 0))

SELECT * FROM @Results ORDER BY TranDate
GO