Sales Order Batch Printing error using Credit Note Header  Topic is solved

Find general Jiwa support here.

Sales Order Batch Printing error using Credit Note Header

Postby FP Accounts » Fri Nov 03, 2017 2:30 pm

Hi,

Currently using JIWA 7.0.157.0

As part of our Invoice Design, we use the JIWA systems config. option to have a Credit Note Header as a prefix for our Credit Notes.

However when we use the "Sales Order Batch Printing/ Email" process, we receive the error that states: Conversion failed when converting the varchar value "C0000039751' to data type int. Module: OnError.

We generate a large volume of Invoices daily, and this function to email out bulk invoices to customers is required.

Please advise how this function can be resolved/ re-enabled, while keeping the C Prefix for our CR Notes.

Thanks
Filter Plus
FP Accounts
I'm new here
I'm new here
 
Posts: 5
Joined: Mon May 01, 2017 3:06 pm

Re: Sales Order Batch Printing error using Credit Note Heade

Postby Mike.Sheen » Sat Nov 04, 2017 10:42 am

Hi,

I have tested this with 07.00.157.00 with demo data after setting the credit note header to be "C" and creating a credit note, processing it and then printing from the batch printing / emailing form and had no issue.

I suspect it may be an issue a bit deeper in the configuration. I need to know the stored procedure (if any) that is being used for the print / email filters.

When you open the batch print form, locate the Edit button in the Settings section and press it:
BatchPrintForm.PNG
Batch Print Form


The resultant dialog should look like this:
Settings.PNG
Settings


If the filter is set to something other than "No Filter", then to find the stored procedure name associated with the filter, open the Filters maintenance form located under the Configuration menu folder of sales orders - it looks like this:
Filters.PNG
Filters
Filters.PNG (18.49 KiB) Viewed 5019 times

In that list I'm interested in the Stored Procedure Name associated with the Filter seen in the Settings dialog.

Report back that stored procedure name - and if it's a custom stored procedure you may want to attach it here so I can examine it.

There could also be plugins at work which might be causing your problem - we'll explore that once we've eliminated the filter stored procedure as the cause.

Mike
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Sales Order Batch Printing error using Credit Note Heade

Postby FP Accounts » Mon Nov 06, 2017 2:18 pm

Hi Mike,

Thanks you for your response.

We use a a custom setting called "Bulk Email - Invoices", the linked filter is called "FP Default Filter" and was created for us by our JIWA service provider.

A copy of the stored procedure associated with the filter is attached.

Appreciate the help,
Claire
Filters Plus
Attachments

[The extension docx has been deactivated and can no longer be displayed.]

FP Accounts
I'm new here
I'm new here
 
Posts: 5
Joined: Mon May 01, 2017 3:06 pm

Re: Sales Order Batch Printing error using Credit Note Heade  Topic is solved

Postby Mike.Sheen » Mon Nov 06, 2017 9:33 pm

Hi Claire,

I think I've found the cause.

In the stored procedure you provided, it is attempting to exclude some invoices from the list to email or print:

Code: Select all
InvoiceNo <> 865 and InvoiceNo <> 874 and InvoiceNo <> 1016 and InvoiceNo <> 1023 and InvoiceNo <> 1024 and InvoiceNo <> 1025


Invoice numbers are a string field, and the above logic as implemented would work with situations where the invoice (or credit note) numbers were numbers only, but with a "C" prefix as you have will cause your error.

I don't know why those invoice numbers were singled out to be excluded, but I've provided a modified version of your supplied stored procedure which should solve your issue for now. I'd still like to know why those invoice numbers were excluded so I can be sure this does not cause future issues - you should contact the author and direct them to this post.

Attached is the SQL script to correct the stored procedure.
usp_FP_BatchPrint_DefaultFilter.sql
Updated stored procedure
(6.78 KiB) Downloaded 219 times


The change I made was simply replacing the line quoted above with the following:
Code: Select all
InvoiceNo NOT LIKE '%0000865' and InvoiceNo NOT LIKE '%0000874' and InvoiceNo NOT LIKE '%0001016' and InvoiceNo NOT LIKE '%0001023' and InvoiceNo NOT LIKE '%0001024' and InvoiceNo NOT LIKE '%0001025'


To apply this change all you need to do is download the attachment and run it against your database using SQL Server Management Studio - make sure you select the database first, otherwise you'll get an error (but no harm will be done if you do get an error).

If you still have a problem, then there is another stored procedure which may have been modified - the name of which can be seen in the System Configuration form, under the Invoicing tab - the setting "StoredProcForGettingActionsInSOBatchPrinting" by default is "usp_JIWA_SalesOrders_SOBatchPrintGetActions" - if yours is something different then you should provide that stored procedure also.

Regards,

Mike
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Sales Order Batch Printing error using Credit Note Heade

Postby FP Accounts » Tue Nov 07, 2017 3:35 pm

Hi Mike,

That works! Its resolved.

I have not been able to get an answer so far as to why those invoices were excluded. I will keep trying and will let you know.

Again thank you so much for your help on this one.

Kind regards,
Claire
Filters Plus
FP Accounts
I'm new here
I'm new here
 
Posts: 5
Joined: Mon May 01, 2017 3:06 pm

Re: Sales Order Batch Printing error using Credit Note Heade

Postby Mike.Sheen » Wed Nov 08, 2017 12:21 am

FP Accounts wrote:Hi Mike,

That works! Its resolved.

I have not been able to get an answer so far as to why those invoices were excluded. I will keep trying and will let you know.

Again thank you so much for your help on this one.

Kind regards,
Claire
Filters Plus


Acknowledged - glad to hear it's resolved for you now!

There are also some improvements that can be made to the stored procedure if performance ever becomes an issue - the queries are sub-optimal and would benefit from some small changes.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Core Product Support

Who is online

Users browsing this forum: Google [Bot] and 3 guests