Deposit Balance Details View

In PE, when you make a payment to a client account, and just post it to that clients account, it makes a deposit. Here is the data structure for deposits:


CREATE VIEW dbo.moa_deposit_balance
AS
SELECT ReceiptDeposit.rcdActionType AS Type, BalanceCategories.bcCategoryName AS CategoryName, ReceiptDeposit.rcdCategory AS Category,
ReceiptDeposit.rcdCode, Clients.cltClientName AS ClientName, ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdPayAmount AS [Original Amount],
ISNULL(bdDebit.Debits, 0) AS Movement, ReceiptDeposit.rcdPayAmount + ISNULL(bdDebit.Debits, 0) AS Balance,
ReceiptDeposit.rcdTransactNum AS Transact, ReceiptDeposit.rcdReceiptCode AS Receipt, ReceiptDeposit.rcdTransactType,
ReceiptDeposit.rcdTransactSource, ReceiptDeposit.rcdDueDate AS Due
FROM dbo.ReceiptDeposit ReceiptDeposit LEFT OUTER JOIN
dbo.BalanceCategories BalanceCategories ON ReceiptDeposit.rcdCategory = BalanceCategories.bcCode LEFT OUTER JOIN
dbo.Clients Clients ON ReceiptDeposit.rcdClientCode = Clients.cltCode LEFT OUTER JOIN
(SELECT bdReceiptCreditNum, SUM(bdAmount * - 1) AS Debits
FROM MOA_PE.dbo.BalanceDetails BalanceDetails
GROUP BY bdReceiptCreditNum) bdDebit ON ReceiptDeposit.rcdCode = bdDebit.bdReceiptCreditNum
WHERE (ReceiptDeposit.rcdActionType = 0) AND (ReceiptDeposit.rcdStatus = 1) AND (ReceiptDeposit.rcdPayAmount + ISNULL(bdDebit.Debits, 0) <> 0)