SOP Documents - Merge copying!!

[Borvik]

OK, here is a real problem.

I've had 3 times now within the past 2 weeks, where an order has printed out wrong information.

It has nothing to do with the report printing, but rather with the information in the database itself (as using Query Analyzer brings up the bad info, as well as GP 7.5).

Two orders have been merging. One order is fine, and has no problems, but the second order has elements of the first order - hence the copy. The ship to address is usually mostly correct, however the ship to is wrong - as is the Customer number.

Any idea as to what's going on?

 

[jymm]

How are you looking at it through QA? Combining the SOPNUMBE or the master number?

 

[Borvik]

Through QA, I'm doing: SELECT * FROM SOP10100 WHERE SOPNUMBE = 'sop number from printed form'.

No the SOPNUMBE or master number has not been affected, so if I do a search by master number I still see just the one (not both of the orders).

 

[jymm]

with QA (if you also to SOPTYPE) do you get the same results?

so - select & from SOP10100 where SOPNUMBE='number from form' AND SOPTYPE = 2
(type 1 is a quote, 2 is order, 3 is invoice...)

I know at one time our numbers for quotes, orders & invoices crossed and one user did not know to use smartlist for Invoices only (and she got the order with that same number).

 

[Borvik]

It's not the numbers that are crossing - it's the data.

Example case:

Two salespeople enter an order for two different customers (correctly as far as I can tell).
By the time the Packing Slip prints off, one order is messed up. The Ship To address by and large stays unaffected (as does the SOPNUMBE and MASTERNUMB), the Bill To Address is no longer correct - and neither is the CUSTNMBR - they get pulled from the order the other salesperson enters (and are actually stored in the database that way, as QA verifies).

 

[jymm]

but what I am saying is that in SOP10100 there can be
SOPTYPE SOPNUMBE
1 100001
2 100001
3 100001

so if you are printing things just looking for 100001 you would get multiple records when in fact you are just looking for soptype #2 (order) and sopnumbe 100001

make sense? Sounds like a customization that is not looking at the whole key for the record (which is type AND number).

 

[Borvik]

Ah gotcha...

When I do a "SELECT * FROM SOP10100 WHERE SOPNUMBE='whatever'" I am only receiving the ONE result the order (SOPTYPE = 2).

 

[jymm]

yup - hence what I meant by 'crossed over' We started our Ordes at one number, Invoices at another, QUotes at another and a year or so ago one of my SQL scripts started pulling two records...instead of one -- we had enough Quotes to catch up with the order number from YEARS ago...

glad that helpped

 

[Borvik]

Ah - no... you misunderstood.

The situation you just gave should never happen, we prefix all of our numbers with a letter indicating the type (except for orders):

Quote: Q0000000001
Order: 0000000001
BO: B0000000001
Return: R0000000001
Invoice: I0000000001

What I meant was - I only see one, so I know I have the correct one.

 

[jymm]

so - let me make sure I understand.

The problem is that the ship to address (and name) is different on the order than on the customer card? If this is the case it is by design from what I understand. I wrote a trigger to update the quote & pending orders for a customer when the ship to on the card was updated.

SO - from an order you can click on 'Ship to Address' and get to the Customer Maintenance screen, BUT (and this was the part that I did not expect) if you click on a line item and then click on the 'expand' arrow (do not know remember what it looked like on 7.5 - sry) you will get the item detail. From there you will see another 'ship to' address. Probably different than the customer maintenance window.

You can tell your users to change the address & name on the quote/order each time they change address info on the 'ship to' for the customer maintenance -or- you can put in a trigger to change it if you want them in sync like we did.

This it?

 

[Borvik]

As far as I can understand, this is all happening after the salesperson enters the order.

The salesperson enters the order (correctly), the order has a customer and the ship to and bill to match the customer. I know the salespeople enough to know they wouldn't enter a different customer's address manually.

Sometime after the order is entered (maybe when another process is run) the customer number changes, the bill to changes - and sometimes elements of the ship to. The bill to most likely completely changes as the address details are not stored with the order details like the ship to is - it's just referenced from the PRBTADCD field.

The whole thing just doesn't make sense. I figure if something in the database is changing - someone is initiating the change.

I suppose it could still be a user thing, if our front girls (that handle releasing back orders, returns and such) did something wrong. In fact, a week ago Wed. the person who usually does it was on medical leave so someone else did it - and again this past week Wed. If it doesn't happen while the usuall girl handles it, then I'll attribute it to lack of training.

 

[jymm]

so that I can show you what I mean - run this (note my update is commented out so that you can make the update if needed - customer name is also in both places, but was not a factor for us when we had this same problem)

select * 
/*Update SOP30200
Set SOP30200.Address1 = RM00102.Address1, SOP30200.Address2 = RM00102.Address2, 
SOP30200.Address3 = SOP30200.Address3, SOP30200.City = RM00102.City, 
SOP30200.State = RM00102.State, SOP30200.ZipCode = RM00102.Zip, 
SOP30200.Country = RM00102.Country*/
from RM00102 inner join SOP30200 on rm00102.custnmbr = SOP30200.custnmbr
Where SOPType = 2 and adrscode = 'Ship to' and 
(RM00102.Address1 <> SOP30200.Address1 or RM00102.Address2 <> SOP30200.Address2 or 
RM00102.Address3 <> SOP30200.Address3 or RM00102.City <> SOP30200.City or 
RM00102.State <> SOP30200.State or RM00102.Zip <> SOP30200.ZipCode or 
RM00102.Country <> SOP30200.Country)

maybe you will not have any differences, we sure did.

Remember this is against the Orders that have already gone through the system (hence the SOPType part) and if you want to run it against current orders, quotes... you would need to go against SOP10100 --- also keep in mind that the ship to info is on the 'top' of the quote (sop10100/SOP30200) as well as each detail item (SOP10200/SOP30300), so the different address stuff could be coming from any one of these.

 

[Borvik]

Thanks for the sql, but that won't actually help us.

I wrote the software we use to print Pack slips (we had special requirements on what to print - the GP couldn't handle), and the different address stuff is definately coming from SOP10100.

Plus the CUSTNMBR field itself is changing - so if I run a modified version of your SQL it would change everything to the wrong customer.

I'm hoping it because of a user error (usual person on leave, and someone else filling in), as that means just making sure that person knows how to do it properly - if it's not, well this could be a very big headache.

 

[jymm]

I too wrote packing slip stuff for here - international shipping documents (and special Domestic FDA forms) are no fun and impossible in Dynamics - especially back in 7.5.

I had to write a trigger that would automatically update the quote & order info's addresses when the address in RM00102 was modified.

I hope your user error theory is correct - my users never make mistakes (WOW that was hard to type and not laugh at).