SELECT
PostAP.AutoIdx AS id,
BankAccNum AS AccountNumber,
--Master Data
Vendor.Account + ' - ' + Vendor.Name AS AccountName
,BranchCode as Bic_Swift, cBankRefNr as IBAN
,CAST(
CASE
WHEN (ISNULL(BranchCode, '') = '' OR ISNULL(cBankRefNr, '') = '') THEN 0
ELSE 1
END AS bit
) AS IsBankAccountValid
, COALESCE(InvNum.ExtOrderNum, '') as ExtOrderNum, InvNum.InvDate as InvoiceDate,
--TxData
postap.Description as TxDescription, Reference, Order_No,
UserName, cReference2, cAuditNumber, TrCodes.Description as TrCodeDesc,
--Branching
(SELECT cBranchCode
FROM _etblBranch
WHERE idBranch = COALESCE(InvNum.InvNum_iBranchID, 0)) AS Branch,
--Amounts
--Foreign handling:
CASE
WHEN COALESCE(Vendor.iCurrencyID,0) = 0 THEN DCBalance
WHEN Vendor.iCurrencyID > 0 THEN fForeignBalance
ELSE 0 END AS Balance,
CASE
WHEN COALESCE(Vendor.iCurrencyID,0) = 0 THEN (Credit-Debit)
WHEN Vendor.iCurrencyID > 0 THEN (fForeignCredit-fForeignDebit)
ELSE 0 END AS DocTotal,
--DUE DATE SECTIONS
CASE
WHEN Outstanding < 0 THEN TxDate
WHEN Outstanding > 0
AND PostAP.id = 'OGrv' THEN InvNum.DueDate
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 0 THEN TxDate + 0
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 1 THEN TxDate + COALESCE(iInterval1Days,0)
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 2 THEN TxDate + COALESCE(iInterval2Days,0)
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 3 THEN TxDate + COALESCE(iInterval3Days,0)
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 4 THEN TxDate + COALESCE(iInterval4Days,0)
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 5 THEN TxDate + COALESCE(iInterval5Days,0)
WHEN Outstanding > 0
AND PostAP.id <> 'OGrv'
AND Vendor.AccountTerms = 6 THEN TxDate + COALESCE(iInterval6Days,0)
END AS UBD_DueDate,
CASE
WHEN Outstanding < 0 THEN TxDate
WHEN Outstanding > 0
AND Vendor.AccountTerms = 0 THEN TxDate + 0
WHEN Outstanding > 0
AND Vendor.AccountTerms = 1 THEN TxDate + COALESCE(iInterval1Days,0)
WHEN Outstanding > 0
AND Vendor.AccountTerms = 2 THEN TxDate + COALESCE(iInterval2Days,0)
WHEN Outstanding > 0
AND Vendor.AccountTerms = 3 THEN TxDate + COALESCE(iInterval3Days,0)
WHEN Outstanding > 0
AND Vendor.AccountTerms = 4 THEN TxDate + COALESCE(iInterval4Days,0)
WHEN Outstanding > 0
AND Vendor.AccountTerms = 5 THEN TxDate + COALESCE(iInterval5Days,0)
WHEN Outstanding > 0
AND Vendor.AccountTerms = 6 THEN TxDate + COALESCE(iInterval6Days,0)
END AS UBD_Terms,
--not default displayed:
DCBalance,fForeignBalance, fForeignOutstanding, Outstanding as OutstandingHome, PostAp.ID AS Module
, Debit, Credit, DCLink, Account, Name, Tax_Number, InvNum.AutoIndex as PurchaseOrderID
, Vendor.DCLink as VendorID
, Vendor.iCurrencyID as CurrencyID
, vendor.bRemittanceChequeEFTS as RemittanceChequeEFTs
, postap.bTxOnHold as TxOnHold
, vendor.on_hold as OnHold
, CAST(COALESCE(PostAP_iBranchID, 0) AS BIGINT) BranchId
FROM PostAP
/*payment batch lines exclusion which are in process of being paid:*/
LEFT JOIN Vendor ON PostAp.Accountlink = Vendor.DCLink
LEFT JOIN Currency C ON Vendor.iCurrencyID = C.CurrencyLink
LEFT JOIN InvNum ON PostAp.InvNumKey = InvNum.AutoIndex
LEFT JOIN TrCodes ON TrCodeID = idTrCodes
LEFT JOIN _etblTerms ON _etblTerms.iModule = 1 AND _etblTerms.iTermID = Vendor.iAgeingTermID
LEFT JOIN VenClass ON Vendor.iClassID = idVenClass
WHERE round(CASE
WHEN COALESCE(Vendor.iCurrencyID,0) = 0 THEN Outstanding
WHEN Vendor.iCurrencyID > 0 THEN fForeignOutstanding
ELSE 0
END,2) <> 0;