ddunn-1115368 (10/1/2009)
Great aticle! I've been trying to get this concept across to the developers of an accounting package that I work with. Managing changes in price lists (never mind promotional items) can be a bear. This process would simplify so many things for the end users. Being able to import "next week's price list" would be a great feature.I would also consider using the date associated with the document header to locate these promo items instead of a system date. There will be cases where you have to back date a transaction and using the document's date vs. the system date will allow that.
I used the Placed (datetime) column of the Orders table to join to the pricing data in effect at the time the order was placed. That way, if you have to print out the order form a year later, you don't get current pricing, you get the price the order actually dealt with, which is important.
I also had a nullable "ContractID" column in the pricing tables that allowed customers to have specific contracts that gave them their own pricing structure, since that was important to the business. That data was also stored in the Orders table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon