Evo Online - AP Transactions

DECLARE @fromDate date = '2025-01-01'
		,@toDate date = '2025-06-01'
		,@fromSupplierCode nvarchar(100)
		,@toSupplierCode nvarchar(100)

declare @supplierList table (DCLink bigint)
declare @HC nvarchar(10) = COALESCE((select TOP 1 Value from _as_CP_Settings where name = 'HomeCurrency' and COALESCE(RTRIM(Value),'') <> ''),(SELECT TOP 1 cHomeCurrency FROM Entities))
INSERT INTO @supplierList (DCLink)
	SELECT DCLink FROM Vendor V 
			WHERE (V.Account >= @fromSupplierCode OR @fromSupplierCode IS NULL)
				AND (V.Account <= @toSupplierCode OR @toSupplierCode IS NULL)

;WITH opBal as (	
		
SELECT 
	p.AccountLink as SupplierId
	,ROUND(SUM(p.Debit-p.Credit),2) as OpeningBalance
	,CASE WHEN SUM(p.Debit-p.Credit) > 0 THEN ROUND(SUM(p.Debit-p.Credit),2) ELSE 0 END as OpeningDebit
	,CASE WHEN SUM(p.Debit-p.Credit) < 0 THEN -ROUND(SUM(p.Debit-p.Credit),2) ELSE 0 END as OpeningCredit
	,CASE WHEN V.bForCurAcc = 1 THEN ROUND(SUM(p.Debit-p.Credit),2) ELSE 0 END as OpeningBalanceForeign
	,CASE WHEN V.bForCurAcc = 1 AND SUM(p.fForeignDebit-p.fForeignCredit) > 0 THEN ROUND(SUM(p.fForeignDebit-p.fForeignCredit),2) ELSE 0 END as OpeningDebitForeign
	,CASE WHEN V.bForCurAcc = 1 AND SUM(p.fForeignDebit-p.fForeignCredit) < 0 THEN -ROUND(SUM(p.fForeignDebit-p.fForeignCredit),2) ELSE 0 END as OpeningCreditForeign
	,V.Account as SupplierCode
	,V.Name as SupplierName
	,COALESCE(CU.CurrencyCode ,@HC) as CurrencyCode
	,V.bForCurAcc
	,DATEADD(day,-1,@fromDate) as TxDate
	,'OPBAL' as Reference
	,'OPBAL' as cReference2
	,CONCAT('Opening balance for ',V.Account, ' at ',FORMAT(DATEADD(day,-1,@fromDate),'yyyy-MM-dd')) as Description
	,CONCAT('OPBAL-',V.Account) as cAuditNumber
	,B.cBranchCode as BranchCode

FROM PostAP p
INNER JOIN @supplierList S ON P.AccountLink = S.DCLink
LEFT JOIN Vendor V on p.AccountLink = V.DCLink
LEFT JOIN _etblBranch B on p.iTxBranchID = B.idBranch
LEFT JOIN Currency CU ON V.iCurrencyID = CU.CurrencyLink
/*parameters:*/
WHERE 
	p.TxDate < @fromDate
GROUP BY p.AccountLink, v.bForCurAcc, B.cBranchCode, V.Account, V.Name,CU.CurrencyCode 
HAVING ROUND(SUM(p.Debit-p.Credit),2) <> 0
)
SELECT * FROM opBal
union all
SELECT
	 p.AccountLink as SupplierId
	,-ROUND((p.Debit-p.Credit),2) as Amount /*Note; Creditors: so Negative is positive!*/
	,p.Debit as Debit
	,p.Credit as Credit
	,CASE WHEN V.bForCurAcc = 1 THEN -ROUND((p.Debit-p.Credit),2) ELSE 0 END as ForeignAmount /*Note; Creditors: so Negative is positive!*/
	,CASE WHEN V.bForCurAcc = 1 THEN p.fForeignDebit ELSE 0 END as DebitForeign
	,CASE WHEN V.bForCurAcc = 1 THEN fForeignCredit ELSE 0 END as CreditForeign
	,V.Account as SupplierCode
	,V.Name as SupplierName
	,COALESCE(CU.CurrencyCode ,@HC) as CurrencyCode
	,V.bForCurAcc
	,p.TxDate
	,p.Reference
	,p.cReference2
	,P.Description
	,P.cAuditNumber
	,B.cBranchCode as BranchCode

FROM PostAP p
INNER JOIN @supplierList S ON P.AccountLink = S.DCLink
LEFT JOIN Vendor V on p.AccountLink = V.DCLink
LEFT JOIN _etblBranch B on p.iTxBranchID = B.idBranch
LEFT JOIN Currency CU ON V.iCurrencyID = CU.CurrencyLink
/*parameters:*/
WHERE 
	p.TxDate >= @fromDate
	and p.TxDate <= @toDate