OEORDLIN/OEORDHDR sales amount discrepancy 1

[kbieber]

Version 7.5.103f MS SQL - comparing the OEORDHDR tot_sls_amt and tot_dollars fields against the corresponding OEORDLIN qty_ordered * unit_price revealed discrepancies where either/both the tot_sls_amt or tot_dollars field = 0 on about 5% of our open sales orders. There is nothing unique about these orders that I can discern. Can anyone explain the use of the tot_sls_amt and tot_dollars fields and what can cause this discrepancy?

 

[stravis]

In the usual environment these order totals have the following equations.

oeordhdr_sql.tot_sls_amt = SUM(OEORDLIN_SQL.qty_to_ship * unit_price).

oeordhdr_sql.tot_dollars = oeordhdr_sql.tot_sls_amt + oeordhdr_sql.misc_amt + oeordhdr_sql.frt_amt

These equations can be affected, as I recall, by UOM conversions at the line level and line level discounts. If you are using either of these let me know and I will test these values.

Scott Travis

http://www.infospring.net

 

[NEmacGuy]

Stravis is correct :

select qty_to_ship*unit_price*(1-discount_pct/100)* uom_ratio*case ord_type when 'C' then -1 else 1 end as isCredit from oeordlin_Sql where ord_no = '?'

 

[kbieber]

The formulas fit with the majority of my orders. Here's an example of the problem:

OEORDHDR.tot_sls_amt = 0
OEORDHDR.tot_dollars = 60.43
OEORDHDR.uom_ratio = 1
OEORDHDR.discount_pct = 0
OEORDHDR.ord_type = O

OEORDLIN.qty_ordered = 1
OEORDLIN.unit_price = 60.43
OEORDLIN.uom_ratio = 1
OEORDLIN.discount_pct = 0
OEORDLIN.ord_type = O

 

[kbieber]

My fault - I didn't notice qty_to_ship instead of qty_ordered in the formula. I believe that accounts for the discrepancies. Thank you.