Chris Morris-439714 (4/8/2010)
Ninja's_RGR'us (4/8/2010)
Chris Morris-439714 (4/8/2010)
Backtracking a little...Orders (OrderID)
OrderLines (OrderLineID)
Shipping (ShippingID)
ShippingLines (ShippingLineID)
Invoices (InvoiceID)
InvoiceLines (InvoiceLineID)
Which tables hold fk's of which other tables?
ShippingHeader and InvoiceHeader has FK to OrderID
1 order can have n shippings
You cannot have more than 1 order in a single shipping
1 shipping can have 1 or less invoice... 1 shipping, 2 shipping or more or even all shippings can be merged into a single invoice.
I already filtered what I can from document dates before I get to that stage (an invoice of may 1st cannot be linked to a shipment of may 15th. The invoice needs to be same day of the shipping or later).
Nothing to link Shipping to Invoice except matching row for row using document + rowid (where 100% of the rows match in the details of 1 to many documents... 1 invoice to n shippings), sku, and Sum(qty) of all shippings to the invoice lines
Thanks. It's still horrible!
FYI the number of combinations is 2(number of rows)-1, i.e. for 6 rows, that's 63 combinations.
Thanks for the words of compassion. The really horrible part is that in all those documents the prices don't even match (0.01$ errors on a few details lines). So we tell the client price A on the order. Then price B on the shipping slip (which is our invoice at the moment) and when he comes back on the web site to get the real invoice, he possibly gets a 3rd different price.
A big no-no when the bigger clients refuse to pay for even 0.01$ difference for the whole invoice.
And this is also where I need to come in to fix the problem because on the shipping "invoice" we don't have the real invoice # which is created after the delivery guys come back with the adjusted shipping slip which accounts for returns and refused deliveries.
Which means that the client can only track the invoice with the shipping #... which is even more compunded by the fact that the dates of documents and total amounts DON'T MATCH... and that you can have n shippings on 1 invoice!
A real pleasure to fix!
Oh ya and I don't have access to the source code to correct that little misdesign :crazy:... would be so easy to add 1 column and save the value of the invoice on creation and just fix the issue once for the missing data.
Then the problem would only be to concatenate the shipping # if there's more than 1 on the invoice.
Anyhow, 50 hours later and I should be done right about today with a 99.9% correct solution approved by direction.
Anyhow thanks again for all the help.