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
