Invoice Balance Details View

This view will display Invoices that have a balance due, along with some other info. Merry Xmas PEons.


CREATE VIEW dbo.moa_invoice_balance_details
AS
SELECT ReceiptDeposit.rcdActionType AS Type, ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdPayAmount * - 1 + ISNULL(bdCredit.Credits, 0) AS Balance,
ReceiptDeposit.rcdTransactNum AS Transact, MAX(ISNULL(BalanceCategories.bcCategoryName, 'Undefined .')) AS Expr1,
ReceiptDeposit.rcdGroupCode, ReceiptDeposit.rcdCategory, users.usCode, ReceiptDeposit.rcdDueDate,
ReceiptDeposit.rcdTransactDate AS entry_date, ReceiptDeposit.rcdCode, ReceiptDeposit.rcdReceiptCode, ReceiptDeposit.rcdShiftNum AS shift,
ISNULL(BalanceCategories.bcCategoryName, 'Undefined .') AS balance_category
FROM MOA_PE.dbo.ReceiptDeposit AS ReceiptDeposit WITH (nolock) LEFT OUTER JOIN
MOA_PE.dbo.BalanceCategories AS BalanceCategories WITH (nolock) ON ReceiptDeposit.rcdCategory = BalanceCategories.bcCode LEFT OUTER JOIN
(SELECT bdReceiptDebitNum, SUM(bdAmount) AS Credits
FROM MOA_PE.dbo.BalanceDetails AS BalanceDetails WITH (nolock)
GROUP BY bdReceiptDebitNum) AS bdCredit ON ReceiptDeposit.rcdCode = bdCredit.bdReceiptDebitNum INNER JOIN
MOA_PE.dbo.Users AS users WITH (nolock) ON users.usRecId = ReceiptDeposit.rcdUser
WHERE (ReceiptDeposit.rcdStatus = 1) AND (ReceiptDeposit.rcdActionType = 1) AND (ReceiptDeposit.rcdPayAmount * - 1 + ISNULL(bdCredit.Credits, 0) <> 0)
GROUP BY ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdTransactNum, ReceiptDeposit.rcdActionType, ReceiptDeposit.rcdPayAmount, bdCredit.Credits,
ReceiptDeposit.rcdGroupCode, ReceiptDeposit.rcdCategory, users.usCode, ReceiptDeposit.rcdDueDate, ReceiptDeposit.rcdTransactDate,
ReceiptDeposit.rcdCode, ReceiptDeposit.rcdReceiptCode, ReceiptDeposit.rcdShiftNum, ISNULL(BalanceCategories.bcCategoryName, 'Undefined .')