SKU Selling Price by Price Levels using the Percent of List Pricing Method in GP

SKU Selling Price by Price Levels using the Percent of List Pricing Method in GP

A few things to note about this query:

– this focuses on the percent of list method for pricing SKU’s.
– we use this report when doing pricing updates per vendor or for a group of SKU families.
– a third party VAR table from the Act Now Series is included. It is the hm00500 table. Think of this table holding extra data like the Extender products hold. We use it to signify which branches stock the SKU on a regular basis.
– there are some commented out lines of code based on how you need to review pricing.

Variables in the query:
– XXX for itemnmbr to be replaced by the SKU or range of SKU’s needed to be reviewed.
– XXX for locncode to be replaced by the branch or branches to be reviewed.

select a.ITEMNMBR, a.prclevel, a.uofm, a.toqty, a.fromqty, a.uomprice [Percent of List],

((select max(x.uomprice) from iv00108 x where  x.itemnmbr = a.itemnmbr and x.prclevel = a.prclevel and x.fromqty =’1′)*(select y.listprce from iv00105 y where y.itemnmbr = a.itemnmbr)/100) [SKU Selling Price],

c.LISTPRCE [List Price], b.ITEMDESC, b.uscatvls_6 [Category],

d.hm_string_10 [Stocking Branch],

case

when b.itemtype = ‘1’ then ‘Sales Inventory’

when b.itemtype = ‘2’ then ‘Discontinued’

when b.itemtype = ‘ ‘ then ‘Inactive’

end [Inventory Status], (e.QTYONHND – e.ATYALLOC) [QTY Available], e.QTYONORD [QTY on Order]

from iv00108 a

left outer join iv00101 b

on a.ITEMNMBR = b.ITEMNMBR

left outer join iv00105 c

on a.ITEMNMBR = c.ITEMNMBR

left outer join hm00500 d

on a.ITEMNMBR = d.ITEMNMBR

left outer join iv00102 e

on a.ITEMNMBR = e.ITEMNMBR

where a.ITEMNMBR = ‘XXX’

— where a.ITEMNMBR between  ‘XXX’ and ‘XXX’

and e.LOCNCODE = ‘XXX’

— and e.locncode in (‘XXX’ , ‘XXX’)