| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How is the design for this complex requirement ?
Hi Daniel,
Thank you for your reply.
There is misunderstanding here because I wrongly describe the problem, I close this thread, and I will open another thread with SIMPLIFIED problem, but once I get the answer it will solve my real issue. Pls response to my new thread.
Thank you,
xtanto
DA Morgan wrote:
> krislioe_at_gmail.com wrote:
> > Hi All,
> > Thanks for your response.
> >
> > Additional info :
> > - Oracle version : 10g
> > - If multiple discounts are applied, e.g: two discount, 10% each, then
> > the total discount is 20%, if gross = 100, then netto is 100 * 80% =
> > 80.
> >
> > The design that comes to my mind is like below, please give me
> > correcttion and recommendation :
> > The Promotions is defined in master-detail :
> > - one master table
> > - three detail tables (one for each possible hierarchy)
> > - one additional Tabel to store detail of discount an Order get,
> > because it can get 2 or more discount.
> >
> > PromoHeader :
> > - PromoNumber
> > - Description
> > - StartDate
> >
> > PromoDetail1 :
> > - PromoNumber
> > - PType (ALL / CustomerGroup / CustomerType / Customer)
> > - PCode (ALL / 'xxxx' )
> > ( note : 'xxxx' can be Customer Group Code, Customer Type Code or
> > Customer Code )
> >
> > PromoDetail2 :
> > - PromoNumber
> > - PType (ALL / Region / District / Area)
> > - PCode (ALL / 'xxxx' )
> > ( note : 'xxxx' can be Region Code, District Code or Area Code )
> >
> > PromoDetail3 :
> > - PromoNumber
> > - PType (ALL / ProductGroup / ProductBrand / Product)
> > - PCode (ALL / 'xxxx' )
> > ( note : 'xxxx' can be ProductGroup Code, ProductBrand Code, Product
> > Code )
> >
> > PromoByOrder
> > - OrderNumber
> > - PromoNumber
> > - PType
> > - PCode
> > - Discount (%)
> > (if an Order get two discount, there will be two records on this table
> > for thet order)
> >
> > The question is :
> > 1) is this a 'good enough' design ?
> > 2) if I do go with this design, how is the efficient query / plsql to
> > calculate the discounts for an Order.
> >
> > Pls help..
> >
> > Thank you very much,
> > xtanto
> >
> >
> > DA Morgan wrote:
> >> krislioe_at_gmail.com wrote:
> >>> Hi All,
> >>>
> >>> We have a Discount scheme that can be given for all possible
> >>> combination of all
> >>> customer, area and product hierarchy (attached below).
> >>>
> >>> for example :
> >>>
> >>> Promotion No.001 =>for sales on : CustomerGroup 'A', Area 'B', and
> >>> ProductBrand 'C' will get discount 2 %
> >>> Promotion No.002 =>for sales on : AllCustomer, District 'D', and
> >>> Product 'E' will get discount 2.5 %
> >>> Promotion No.003 =>for sales on : CustomerType 'F', AllArea, and
> >>> ProductGroup 'G' will get discount 3 %
> >>> ... and any possible combination.
> >>>
> >>> And it is possible for an Order / Invoice to get discount from two or
> >>> more different promotion.
> >>> e.g: Order 001, get 2 % from Promotion No.001 and 3 % from Promotion
> >>> No.003, so totally it gets 5% discount.
> >>>
> >>>
> >>> (1) Customer Hierarchy
> >>> AllCustomer
> >>> CustomerGroup
> >>> CustomerType
> >>> Customer
> >>>
> >>> (2) Area Hierarchy
> >>> AllArea
> >>> Region
> >>> District
> >>> Area
> >>>
> >>> (3) Product Hierarchy
> >>> AllProduct
> >>> ProductGroup
> >>> ProductBrand
> >>> Product
> >>>
> >>> Our Orders Data has following attributes :
> >>> OrderNo.
> >>> Customer
> >>> Area
> >>> Product
> >>> Gross
> >>> Discount
> >>>
> >>> The question is : I am looking for the efficient way to do this,
> >>>
> >>> 1) How should I design the tables to accomodate all possible discount
> >>> combination above ?
> >>> 2) How is the query to determine the total discount that an Order
> >>> should get ?
> >>>
> >>> Thank you for your help,
> >>> xtanto
> >> Not enough information provided to help you.
> >>
> >> 1. What is the Oracle version number?
> >> 2. If multiple discounts are applied how are they applied? For example
> >> lets say there are two 10% discounts.
> >>
> >>
> >> 100 * 0.90 = 90
> >> 90 * 0.90 = 81
> >>
> >> or
> >>
> >> 100 * 0.80 = 80?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan_at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
>
>
 |
 |