Debtors statements to print un-aged fully allocated transactions

Owned by Beth Blok (Unlicensed)
Last updated: Mar 13, 2018 by Scott Pearce
2 min read

 Problem

In Jiwa 7 we no longer include zero balanced transactions in the debtor statement report.

Solution

To include these transactions in the statements, changes have to be made to the stored procedure dbo.usp_Jiwa_Debtors_Statement.  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.

  1. 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

  1. copy the standard stored procedure and assign a new name to the copied stored procedure 

  2. change the name of the report 

  3. change the stored procedure that the report addresses (in the Crystal Report)

  4. import the new report into the database using Refresh from Files under Reports > Report Configuration > Reports

  5. attach the new report to the Form Report Definitions for each print group in the database