7.6.3 - OELINHST and backorders 1

[ultrav]

I am trying to write a crystal report to get the sales of certain products in the USA only so I wanted to write it using Order History - the problem I am having is that the backorders are looking the same as when the backorder is filled. What field should I be using other that qty to ship to determine what shipped? How do I eliminate the backorders from showing up? I feel like I am missing something obvious.

Thanks for your help.
Celeste

 

[dgillz]

There are no backorders in OELINHST, these remain in the OEORDLIN file. Qty_to_ship is the field to use (although you may need to look at qty_return_to_stk if you are dealing with credit memos).

The field qty_bkord in the OELINHST is what is placed on backorder for that particular line. This qty remains in the OEORDLIN after the invoice is posted.

Please explain your comment "the backorders are looking the same as when the backorder is filled". What do you mean by "looking the same"?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[ultrav]

For the line items that have been backordered and the fufillment of hat backorder - 2 lines show up on my report (order lines that were not backordered at all only show one)- one line shows the qty ship to as 0 the other line shows the qty ship to as 1 (or whatever the qty was) and the qty backorder flag is Y on the lines with qty to ship 0 and N on the qty to ship 1 - I thought at least that flag would be reversed so I could eliminate those lines.

Here is a part of the report - on invoice 122,542.00 and 122,627.00(haven't formatted the fields yet)the item was backordered - on invoices 122,973.00 and 122,974.00 the item was shipped. The last invoice 127,307.00 was shipped without a backorder.

This report was giving me a total of 5 shipped for this item but it should be 3.




Order Date Qty Shipped Item No Qty_Bkord Qty_Bkord_Fg Inv_No Qty_Return_To_Stk

25-8127B

122,542.00
6/30/2004 1.00 7/1/2004 25-8127B 0.00 N 122,542.00 0.00 6/30/2004 0.00 7/1/2004 25-8127B 1.00 Y 122,542.00 0.00

122,627.00
7/1/2004 1.00 7/2/2004 25-8127B 0.00 N 122,627.00 0.00 7/1/2004 0.00 7/2/2004 25-8127B 1.00 Y 122,627.00 0.00

122,973.00
6/30/2004 1.00 7/15/2004 25-8127B 0.00 N 122,973.00 0.00 6/30/2004 0.00 7/15/2004 25-8127B 1.00 Y 122,973.00 0.00

122,974.00
7/1/2004 1.00 7/15/2004 25-8127B 0.00 N 122,974.00 0.00
7/1/2004 0.00 7/15/2004 25-8127B 1.00 Y 122,974.00 0.00

127,307.00
11/17/2004 1.00 11/18/2004 25-8127B 0.00 N 127,307.00 0.00

 

[dgillz]

If you are trying to write a sales report from the OEHDRHST and OELINHST, your linking needs to be:

order type->order type
Order_no->Order_no
Invoice_No->Invoice_no

You could have many more than 2 shipments with multiple backorders, and each shipment would have a different invoice number. Therefore if you do not link in this way, you will not have a good report.

I hope this helps.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[ultrav]

Thank you - that did the trick !!

Celeste

 

[kscheeler]

In addition, if you are looking at credit memos, the orig_ord_type on OEHDRHST_SQL would be a 'C' and you would want to look at the qty_ordered field which is the qty credited for credit memo's. This would be different than what you actually returned to stock. The field is always a positive value so you'll need to multiply it by -1 to change the sign.


Kevin Scheeler