I'm very new to MFG's tables and I have a very big problem we are running ver 8.6e. I'm hoping someone here can point me in the right direction. He's the problem: When a order is shipped from MFG it hits the tr_hist table and puts in the tr_type of iss-so per tr_part item. But if the user selects do not back order when the product is shipped and the product is not there at all (so a shipped qty of 0) MFG is zeroing out that line item on the sales order so I have no way of knowing we need x amt because the new amt is 0. What my company wants is for me to come up with a solution that will show a cut report of all missed items for a Sales order. so for example: we ship these 3 items:
part qty shipped needed
1101 10 10
1102 0 10
1103 10 10
At shipment MFG will change the type to iss-so on line item 1101 and 1103 but still leave the line item 1102 in the tr_hist as ord-so and change the qty to 0. and in my cut report i can't just say that item 1102 was shorted to the customer because customer service could of gone in before the truck left and changed the order but i have no idea in that history table that customer service did it vs. the shipping program. I was thinking that I might be able to use the tr_date with Iss-so and compare those two values vs. tr_date and ord-so for the same day. but that really will not work either cause I don't know if A. the Sales Order can be changed after it has been shipped, or B. if was changed before because the query would pull only the first result and there would be multiple records. What would be nice is if there was a field that said that the shipping mod put the transaction in the table.
Cheers,
john
part qty shipped needed
1101 10 10
1102 0 10
1103 10 10
At shipment MFG will change the type to iss-so on line item 1101 and 1103 but still leave the line item 1102 in the tr_hist as ord-so and change the qty to 0. and in my cut report i can't just say that item 1102 was shorted to the customer because customer service could of gone in before the truck left and changed the order but i have no idea in that history table that customer service did it vs. the shipping program. I was thinking that I might be able to use the tr_date with Iss-so and compare those two values vs. tr_date and ord-so for the same day. but that really will not work either cause I don't know if A. the Sales Order can be changed after it has been shipped, or B. if was changed before because the query would pull only the first result and there would be multiple records. What would be nice is if there was a field that said that the shipping mod put the transaction in the table.
Cheers,
john