Problem
In Jiwa 7 we no longer include zero balanced transactions in the debtor statement report.
Solution
To re-instateinclude these transactions in the statements, changes have to be made to the stored procedure dbo.usp_Jiwa_Debtors_StatementsAsAt and the Debtor As At Statement report. Anytime you upgrade to another Jiwa 7 version, you will need to edit and run this stored procedure again because the upgrade process will override your changes.
- change Change the line --AND AgedOut = 0 by removing the two dashes before that statement
...
- i.e. AND AgedOut = 0
There are two such lines!
2. Remove the WHERE clause from the end of the stored procedure - to do this change:
SELECT
Debtors.*,
DebtorTransactions.TransID,
DebtorTransactions.InvRemitNo,
DebtorTransactions.TransactionDate,
DebtorTransactions.Reference,
DebtorTransactions.Remark,
DebtorTransactions.TransactionType,
DebtorTransactions.Amount,
DebtorTransactions.DebitAmount,
DebtorTransactions.CreditAmount,
DebtorTransactions.AllocatedAmount,
DebtorTransactions.DebitCredit,
DebtorTransactions.TransactionBalance,
DebtorTransactions.PeriodNo,
@OurCompanyName AS OurCompanyName, @OurCompanyABN AS OurCompanyABN, @OurCompanyAddress1 AS OurCompanyAddress1,
@OurCompanyAddress2 AS OurCompanyAddress2, @OurCompanyAddress3 AS OurCompanyAddress3,
@OurCompanyAddress4 AS OurCompanyAddress4, @OurCompanyPhone AS OurCompanyPhone, @OurCompanyEmail AS OurCompanyEmail,
@OurCompanyWebSite AS OurCompanyWebSite, @OurCompanyFax AS OurCompanyFax,
@MoneyDecimalPlaces AS MoneyDecimalPlaces, @OurCompanyACN as OurCompanyACN
FROM
@DebtorTempTable Debtors
INNER JOIN @DebtorTransactionsTempTable DebtorTransactions ON Debtors.DebtorID = DebtorTransactions.DebtorID
WHERE
(DebtorTransactions.TransactionBalance <> 0 OR DebtorTransactions.TransID = 'DUMMY')
ORDER BY
TransactionDate, InvRemitNo
to:
SELECT
Debtors.*,
DebtorTransactions.TransID,
DebtorTransactions.InvRemitNo,
DebtorTransactions.TransactionDate,
DebtorTransactions.Reference,
DebtorTransactions.Remark,
DebtorTransactions.TransactionType,
DebtorTransactions.Amount,
DebtorTransactions.DebitAmount,
DebtorTransactions.CreditAmount,
DebtorTransactions.AllocatedAmount,
DebtorTransactions.DebitCredit,
DebtorTransactions.TransactionBalance,
DebtorTransactions.PeriodNo,
@OurCompanyName AS OurCompanyName, @OurCompanyABN AS OurCompanyABN, @OurCompanyAddress1 AS OurCompanyAddress1,
@OurCompanyAddress2 AS OurCompanyAddress2, @OurCompanyAddress3 AS OurCompanyAddress3,
@OurCompanyAddress4 AS OurCompanyAddress4, @OurCompanyPhone AS OurCompanyPhone, @OurCompanyEmail AS OurCompanyEmail,
@OurCompanyWebSite AS OurCompanyWebSite, @OurCompanyFax AS OurCompanyFax,
@MoneyDecimalPlaces AS MoneyDecimalPlaces, @OurCompanyACN as OurCompanyACN
FROM
@DebtorTempTable Debtors
INNER JOIN @DebtorTransactionsTempTable DebtorTransactions ON Debtors.DebtorID = DebtorTransactions.DebtorID
--WHERE
--(DebtorTransactions.TransactionBalance <> 0 OR DebtorTransactions.TransID = 'DUMMY')
ORDER BY
TransactionDate, InvRemitNo
To make this change permanent you will need to
- copy the standard stored procedure and assign a new name to the copied stored procedure
- change the name of the report
- change the stored procedure that the report addresses (in the Crystal Report)
- import the new report into the database using Refresh from Files under Reports > Report Configuration > Reports
- attach the new report to the various print groups Form Report Definitions for each print group in the database has