voided invoices/orders 1

[lunchbox88]

I'm writing some Crystal Reports for a company against GP 8. I'm having a hard time excluding voided invoices. I assumed that the field that held the void status was VOIDSTTS, but checking against this field my totals are still incorrect. Is this the proper field? Should I be checking against something else?

 

[Luvsql]

Yes, you want to set the Voidstts=0 (1s are ones that have been voided). What fields are you using for totals and what are you comparing the amounts to? If you use Smartlist to verify and are using Crystal with just the SOP history tables (30000 series), then also restrict Smartlist to be void status=normal, Doc Status=Posted, Sop Type = Invoices and a date range (if using in your report).

Are you including REturns? Are you adding a calculation to make return amounts negative?

 

[lunchbox88]

I have no idea what Smartlist is. I honestly know nothing about Great Plains. I'm using SOP30200, SOP30300, INV00101. I've got most of the totals matching what they were getting from their hand created Excel spreadsheets.

There are 4 products, and they want order subtotal, shipping, and order total (subtotal + shipping). I've got correct amounts on 3 of the 4 products. The 4th product is off, and they claim it's because the totals are including voided invoices. In my selection criteria, I have voidstts = 0, so I can't be including voided invoices. hmm...I'll have to check something else.

 

[Luvsql]

You mention looking at order subtotals etc., but then voided invoices? Are you looking at both orders and invoices? If they do a partial shipment, the one order could have multiple invoices, as well as the order information could be in both the SOP10100 table as well as the SOP30200 table.

 

[lunchbox88]

Where can I find the Voided Date field in the database?

 

[Dukester0122]

I don't have access to our system right now but I think there's no Voided Date in SOP30200 but Modify Date would be the field you need. It starts with MODIFXXX.

 

[lunchbox88]

Wouldn't the modify date pick up other information as well? If it does, this won't work for excluding voided invoices.

The issue I have is that there are at least 2 invoices that say they were voided in Great Plains, but the VOIDSTTS field didn't update with a 1. It still lists as 0. My report is still pulling them in as if they weren't voided, and that's not correct.

Any ideas on how to fix this? I don't want to hard code in the invoice numbers that were voided, but VOIDSTTS=0 because if it happens again, it won't be caught.

Thanks in advance

 

[Luvsql]

Where is it showing as voided ie within Inquiry > Sales > Sales Documents or Inquiry > Sales > TRx by Document. It sounds like they voided the invoice in receivables, which will not void the invoice in sales order processing and is also not recommend as the inventory will NOT get updated.

If this is the case, you will need to reference both the RM20101 and RM30101 to find out if it was voided (could create view on union of the two tables).

 

[barbola]

The void status will show 0 if the invoice was voided after posting. If it was voided before posting it will show a 1. Have your users remove voided documents from history.

We just went through this with our vendor, it's backwards as far as I'm concerned.



Thanks!
Barb E.

 

[jonbarr]

Here's a tip we learnt the hard way.
Make sure your invoice form has provision to print the word "Voided" displayed prominently on any voided invoices.

We printed out and then credited two invoices that had been voided - because the user didn't realise the invoices were voided.

Provision to print "Voided" should be included on all forms where it is a possibility.

 

[lunchbox88]

Where is it showing as voided ie within Inquiry > Sales > Sales Documents or Inquiry > Sales > TRx by Document. It sounds like they voided the invoice in receivables, which will not void the invoice in sales order processing and is also not recommend as the inventory will NOT get updated.

If this is the case, you will need to reference both the RM20101 and RM30101 to find out if it was voided (could create view on union of the two tables).

I believe you're right. I've been looking at the RM20101 and RM30101 tables, and this does appear to be the case.

I asked the client how they are voiding invoices. They are currently going to Transactions > Sales > Posted Transactions to void the invoice.

Is this what's causing the issue? If it is, how should they be doing it? Is it possible to go back in and re-void (void again) the invoice so it updates the SOP30200.VOIDSTTS field?

If they're ARE voiding the right way, what else can I look at? If I bring in RM20101, I've found that other invoices that SHOULDN'T be voided have a void status of 1, so this throws the data off as well.

Any help is greatly appreciated.

Thanks again

 

[lunchbox88]

bump..

Anyone? Any help would be appreciated!

Thanks!

 

[barbola]

They should not be voiding posted invoices, but rather entering a Return. If there are inventory items on the invoice, and they void it, they need to enter an inventory adjustment, and also go into Remove Trx History and remove the void invoice from history.

VOIDSTTS is only "1" to me, means nada cuz it doesn't show up anywhere, therefore it is more of a procedural issue.

A posted invoice should not be voided if a copy was sent to a customer. The customer should get a return or an adjusting invoice.

Make sure that "Delete document" is checked for that SOP Type in SOP setup so they can delete the document if it hasn't been posted, and they won't have to void it.

 

[lunchbox88]

what do you mean that voidstts=1 doesn't show up anywhere? You have to remember that I'm trying to create a Crystal report for the customer, and they don't want voided invoices on them.

Any other thoughts?

 

[barbola]

SOP30200.VOIDSTTS does not become 1 if the transaction was voided using Trx > Sales > Posted Trx and Void. It is reversed in Recv Mgmt and GL only, not SOP. When a historical invoice is voided, the user must also remove the invoice from SOP using Utilities > Sales > Remove Trx History in order for it to not show up on your report.

SOP30200.VOIDSTTS will be "1" only if you have an unposted invoice, and click VOID. The document will be put into SOP history with a VOIDSTTS = 1.

I entered 4 invoices in SOP as follows:

SLS044850 not saved/voided
SLS044851 saved/not voided
SLS044852 saved/printed/voided
SLS044853 saved/voided

I posted my Sales Batch wish showed 1 transaction only.

I ran SELECT * FROM SOP30200 WHERE SOPNUMBE BETWEEN 'SLS044850' AND 'SLS044853' and here are some of the resulting field values:

SOP SOP BCH BACH PSTG VOID
TYPE NUMBE GLPOSTDT SOURC NUMB STUS STTS

3 SLS044850 2005-10-19 Sales Void BARBE 0 1
3 SLS044851 2005-10-19 Sales Entry TESTBATCH 2 0
3 SLS044852 2005-10-19 Sales Void BARBE 0 1
3 SLS044853 2005-10-19 Sales Void BARBE 0 1

Remember these invoices were voided BEFORE posting.

Here are the same results for a historical invoice that was voided:
3 SLS021916 2004-01-13 Sales Entry STORE 2 0

So, what I am trying to say is you cannot use the VOIDSTTS field from SOP30200 to determine if an invoice was voided.

You need to join the tables RM20101.DOCNUMBR = SOP30200.SOPNUMBE where RM20101.VOIDSTTS<>1

Here are the results of SELECT docnumbr,voidstts,voiddate from RM20101 where docnumbr = 'sls021916':

DOCNUMBR VOIDSTTS VOIDDATE
SLS021916 1 2005-10-19

I hope this helps....it was a nightmare for us last year when we had to query hundreds of invoices for an audit and did not realize we had included voided ones.

 

[lunchbox88]

Thanks so much! I'm going to the client site tomorrow to try and get this resolved. I'll try linking in RM20101 and using the voidstts there.

Thanks again, and I'll update tomorrow with how it went.

 

[lunchbox88]

Ok, linking in RM20101 and checking against the VOIDSTTS there worked! Thanks a million!

Now, I have another question. For orders that haven't been invoiced yet, is their information going to be stored in SOP30200, or in a different table?

Thanks again!

 

[Dukester0122]

Unposted sales documents tables are using SOP30200 and SOP30300

 

[Dukester0122]

What was I thinking...it's SOP10100(header) and SOP10200(lines)