Debtor Statement sp error

[Atronics]

I have a custom table that holds text strings to be printed on debtor statements when there is a balance in 60d or 90d (different warning message for each. I have modified the standard jiwa debtor statement sp to include these extra fields, but am receiving the following error message when the sp is run. “Msg 245, Level 16, State 1, Procedure usp_Atronics_Debtor_Statements, Line 147
Conversion failed when converting the varchar value 'JiwaFinancials.Jiwa.JiwaDebtorInvoicesUI.MainForm' to data type int.”

The sp is attached.

[Scott.Pearce]

DB_Trans.Source is no longer of type INT - it is of type VARCHAR and it points to an SY_Forms entry. This tells us exactly which form created the transaction (instead of relying on a hard-coded digit that maps to a module as in earlier versions of Jiwa). If you compare your stored procedure to the new v7 standard one, the first difference you will see (at line 147) is that your code:

Code: Select all

         CASE DB_Trans.Source
            WHEN 0 THEN 'Sales Orders'
            WHEN 1 THEN 'Debtor Sourced Transactions'
            WHEN 2 THEN
                     CASE DB_Trans.SubType
                        WHEN 0 THEN 'Cash Book Receipts'
                        ELSE 'Cash Book Payments'
                     END
            WHEN 3 THEN 'Bank Reconciliation'
            ELSE 'Unknown'
         END,


Has been replaced with:

Code: Select all

         CASE SY_Forms.Description
            WHEN NULL THEN 'Unknown'            
            ELSE SY_Forms.Description
         END,


This implies that we need to join in the "SY_Forms" table. If we look a little bit further down, we see in the FROM part of the query that the line:

Code: Select all

LEFT JOIN SY_Forms ON SY_Forms.ClassName = DB_Trans.Source


has been inserted above the "WHERE" line.

The same is also true for line 179 and it's associated FROM clause.

[Atronics]

Thanks, Scott. All is now working correctly. I thought that I had cloned the sp from a V7 sp but must have picked it from V6.

[Scott.Pearce]

That change to the stored proc may have been made in one of the more recent v7 builds, so it could be that you had an "earlier" v7 version of it.