PRICING:-
/*Price Lists
Since a price list consists of line item with price against each item
and date of validity against each item.
So you chose a price list at the orderheader level. when you place line items
in a order then each item is looked into the price list for the price for that
unit of measurement.
This looks very obvious because in a b2b environment, there will be hundreds
of thousands of items in the inventory or sub-products, and hence there should
be some place to store the price of each item(as the order entry person cannot
remember the price of every single item). It is the price list which does this.
Another place to store this is probably in the item master(in inventory) along
with all other details of the item.
However, in the price list, we can set the price of an item based on the dates,
so that we have the history of all the prices that particular item has. Also it
is really convenient in the price list than to go to each specific item in the
inventory and make the price change.*/
/****************************************************
While entering an order ,we might get the error "Item and UOM not on Price List"
then ensure that,
1) Price List is defaulting/available on Order Line.
2) Order Currency matches the Price List currency.
3) Price List is active- both by dates and by Active Flag.
4) Price List is appropriately qualified and your order line meets all qualifying conditions.
5) Make sure that the item is attached to the inventory organization, that you
are dealing with.
Run two requests from Oracle Pricing
1. Build Attribute mapping rules
2. QP: Maintains the denormalized data in QP Qualifiers
*/
/* Price Adjustments : The Price Adjustments are Discounts,Surcharges,Freight charges,Taxes which change the
total order price.*/
/* Freight Charges : Let us see how can we create a freight charge for a specific order.
We can associate freight charges either at the header level or the line level.
(setup =>Modifiers)
First create a Modifier list of type 'Freight and Special charge List'. And at the
line level of the Modifier list, create line of Modifier type 'Freight/Special Charge'
(which is the only type you can create). At the line level you can mention,either
this modifier will apply at the header or line level of the Order. Let us create 2
lines one for the line, and one for the order.
Second, pullup the order to which we want to apply the freight charges in the sales order form.
Now to apply the freight charges at the header level, do
Actions => Charges.
Here choose the charge that you have defined in the Modifier (of type Order,of the
freight modifier list).
Third, go to the order lines, pick one order line and do,
Actions => Charges and Freight Costs.
Here choose the charge that you have defined in the Modifier (of type Line,of
the freight modifier list).
So in essence, what we have done is created a Modifier (of type freight) with consisting of two line
(line,order) and applied the freight charges both at the header level and at the level.
*/
--Once these steps are completed, we will have price adjustments like this.
select * from oe_price_adjustments where header_id = 92777
select * from oe_order_headers_all where order_number = 1100032097
--WHERE HEADER_ID = 1662
/* So basically whether we create a Freight Charge List at the header level/line level.
It is not taxed in OM. and when I interfaced it to AR, there also it is not getting charged.
However if I created a Surcharge(surcharge list) either at header/line level,
that charge is included as a part of the line amount and it is taxed.
Similarly when we create a discount (by discount list), say at the line level,
that amount is included as part of the line amount and it is taxed.
It is important to note that whatever lists we are creating here and associating
it with the order/lines,we could do that either to the order in the sales order form
OR at the price list level.(the price list is associated with the order anyway).
/*Yes you should be able to override the price directly on the line. Check the value
of the profile option "OM: Discounting Privilege". Thats the only dependency that
I can think of at the moment.
Check the value of the profile option OM: Negative Pricing*/
/*IMPORTANT POINTS ABOUT PRICE LIST :
1) WITH OUT pricelist we cannot enter any lines,we have to have pricelist
mentioned either at header/line level.
Once an item is entered which is part of that price list, then the price of
the line item will default to the value mentioned in the price list.
SO WE HAVE TO DEFINE A PRICE LIST FOR SURE.
2) Similarly we need to select from a pricing attribute. Usually selecting a
pricing attribute will override the price associated with the pricing attribute.
So in the case, where you want to value entered at the line level of the
order persist, we need to do the following.
We can define a pricing attribute
There are two ways you can create a modifier, either go to actions => view adjustment and create/apply a
modifier. Or We can directly modify the price on the line item and it will automatically
prompt us to choose a modifier, and then we can apply a modifier there.
-- When I create a surcharge modifier, even then I am not able to select and apply that charge to Order/line.
-- To get a list of different kind of lists ,run the following query.
select distinct list_type_code from qp_list_headers
-- Creating Frieght Charges. What are the existing frieght lists available.
To create a frieght charge, we first need to create a Modifier list of type Frieght,using
Pricing => Modifiers*/
select *
from qp_list_headers
where list_type_code ='CHARGES'
-- All the list types are stored as lookups and put the above values in here..
select *
from qp_lookups
where lookup_code in ('AGR','CHARGES','DLT','PML','PRL','PRO','SLT')
and lookup_type ='LIST_TYPE_CODE';
select list_header_id, list_type_code,automatic_flag,name,description,
source_system_code,pte_code,active_flag
from qp_list_headers
order by creation_date desc
select list_line_id, list_header_id, list_line_type_code,modifier_level_code
,arithmetic_operator,operand
from qp_list_lines
where list_header_id = 9359
-- Also the qualifiers go into the qp_qualifiers
select qualifier_id, qualifier_context, qualifier_attribute,qualifier_attr_value,
comparison_operator_code,
list_header_id,list_line_id, active_flag, list_type_code
from qp_qualifiers
select * from qp_modifier_summary_v
select * from qp_pricing_attributes where trunc(creation_date) = trunc(sysdate) -2
select * from wsh_freight_costs
select * from OE_ITEM_GROUP_LINES
begin fnd_client_info.set_org_context(44); end;
-- QP_HDR_PRICING_ATTRIBS_V, qp_pricing_attributes
select * from FND_DESCR_FLEX_COL_USAGE_VL
where descriptive_flex_context_code = 'PRICING ATTRIBUTE'
select * from qp.qp_attribute_defns
select count(*), product_attribute_context
from qp_pricing_attributes group by product_attribute_context
/*Pricing Attributes.
I wanted to know how exactly we can define pricing attributes. I understand
that pricing attributes are nothing but the DFF with a specific context value
defined and its values. So If we want to define an adminstrative cost for an item,
then we can define adminstrative cost defined as a segment and choose that pricing
context when creating an order. But however what is the difference between this
and the following.?
you create an order with a line. then you create a surcharge modifier list as
administrator cost. then you associate this modifier to that particular item. So
in this particular case, you have not created the pricing attribute,but still changed
the price. So the question,I have is what exactly are pricing attributes and what are
they used for.
what is the difference between creating a pricing attribute at the modifier level
and creating a pricing attribute at the price list level ?
I believe there is no difference.
1). What is a pricing attribute and how does it work?
A pricing attribute is an attribute that you associate with an item which has
an effect of its price. A pricing attribute is basically a DFF which is of
title "Pricing contexts" and a context value which you can define. Once you associate
a pricing attribute to an item here is how it works.
Say you have associated a Pricing attribute XYZ, with an attribute "Duty cost" to an
item and specified the value equal to 25. The price of the item is speicified
on the price list as 99. Then when you pick this pricing context on the order
line(in the pricing tab) and enter the value as 23 in the DFF, then the order line
price will change immediately to 99. Even if it does not change right away, you
could do the
Actions => Price Line,
so that price on the line is updated accordingly.
Remember if the pricing attribute is not provided on the order line (and if it is
specified on the price list), then the "All Items" price value will default on the
order line.
2). What is the difference between creating Pricing attribute in the following two ways?
There is a very important point to note here. There are two ways to define a
pricing attribute. Basically you can define a DFF using the regular menu (AOL Method)
Setup => Financials => Flexfields => Descriptive => Segments
When you define a pricing attribute using this method, then it goes into
the fnd_flex_attributes table.
OR
A pricing context can be defined here (QP Method),
Pricing => Setup => Attribute Management => Context and Attributes.
When you define a pricing attribute using this method, it goes into the qp pricing
attributes table i.e basically in the pricing related tables.
It is very important to define the pricing attribute using the QP menu,because then
it will automatically shows up in the DFF. However when you define using the DFF,
it will not be available in the QP tables.
3). What are the steps to create a new Pricing Attribute Context??
a) Using the menu create a context setup
Pricing => Setup => Attribute Management => Context and Attributes.
Ensure that in the detail block, the column mapped is a column more than the
pricing_attribute30 etc.
b) Then using the menu, link the context to a pricing transaction entity. So if you
want the pricing attribute to be associated with Order Fulfilment provide the same
accordingly.
Pricing => Setup => Attribute Management => Attribute Linking and Mapping.
Then click on the link attributes and ensure that in the attribute mapping
method the method is given as "USER ENTERED".
c) Now run the program "Build Attribute Mapping And Rules".
Now this pricing attribute is ready to be associated to a price item as well as
for picking on an order line.
4) What are the different kinds of Lists available in pricing.
A list can be of any type. Examples of Lists are price lists, Modifier Lists.
A Price List is basically a list consisting of the lines with each line corr
to an inventory item and associated price.
Modifier Lists are the lists which are of different types. They
actually modify the total price of the order. So for ex, a discount is a
modifier which modifies the total price of the order.
Different kinds of modifier lists that are available are
Discount Modifier List
Surcharge Modifier List
Freights and Special charges Modifier List.
Right after the Modifier is defined, a qualifier may be defined ,if we need to
restrict the modifier to specific orders.
5). How do you associate a pricing attribute to an item ??
A pricing attribute can be associated to an item either on a price list or in
any kind of list (for ex Modifier list, Qualifier List etc).
6) What does the "Build Attribute Mapping rules" program do??
"Build Attribute Mapping rules" program basically builds a package dynamically
which maps the attributes to the flex tables, i think. To undertand better
from the backend,look at the
qp_build_sourcing_pvt package ,
qp_attribute_mapping_pub.build_contexts package.
It is important to understand the just by adding an item to a price list, we need
not run this program. Thhis program needs to be run only if some attribute mapping
changes have happened.
7) What is the default price for all items in a price list.
In a price list we can also define something called "All Items" and a price for
it. So whenever a price is not found for a particular item, then this price
is defaulted on the order line.
8) What is the difference between Manual Modifier and Automatic Modifier and how
do they work ?
As mentioned above a modifier is a list consisting of header and lines.Each line
will have a flag or check box called Automatic.If Automatic flag is not checked,
then it is a manual modifier and if it is checked, then it is an Automatic modifier.
Ex of Automatic Modifier, let us say we have defined a modifier line on an item "myitem10"
which has a price of $100 and put a discount of 10%. Then when you create an
order line with this item, and then save it, then the price of the order line
changes to $90(for 10% discount).
Ex of Manual Modifier, let us say we have defined a modifier line on an item "myitem11"
which has a price of $100 and put a discount of 12%. Then when you create an
order line with this item, and then save it, then the price of the order line
will not change automatically. You will have to explicitly do the following,
Actions => View Adjustments
Then you pick the manual modifier and say Apply. Then the price of the order
lines changes to $88(for 12% discount)
9) Can pricing attributes be defined only in modifiers and not else where,i mean at
the order line level?
No,pricing attributes can be defined only either at the price list level or at
the modifier level. And while defining at the Modifier level,the pricing attribute
button is enabled only when we choose the product attribute and product attribute
value fields in the modifier line level i.e when we choose a particular item
While defining a pricing attribute at the Price list level, the pricing attribute
is always enabled.
10) What is the functionality of the override flag on the modifier line ?
If override flag is selected, then the system will allow the user to change the price
of the line item on the sales order, otherwise not.
12). Can a price list be accessed across multiple operating units?
By default, when you define a price list, it is a global i.e it is accessible across
the operating units.However we can refine it to include only for a specific OU.
13). what is a price break ?
A price break is basically specifying the price of an item with based on
parameters like quantity,amount etc i.e.
ex if the quantity is between 1 to 100 then the price is $10 ; and if
the quantity is between 101 to 200, the the price is $9; etc
ex if the amount is between $100 to $250 the price is $25 ; and if
the amount is more than $1000, the the price is $19; etc
14). Can we specify a price break any price list and modifiers?
Yes, you can create price breaks either on a price list or on Modifiers.
If you have to create a price break on a Price list line item, then you click
on the price breaks button and can specify any number of breaks. So for each
line (on the price list) you can have any number of details records. however
this would require creating formulas and factors(see details below).
If you have to create a price break on a Modifier, then you just create multiple
lines with each line corresponding to a price break. Basically there are no
formulas and factors here.
In summary,the difference is in the case of price list we need formulas/factors and
in the case of modifiers we dont need them.
15). What are formulas and factors ?
formula and factors : 1 * nvl(2,3); where 1,2 and 3 are steps in the formula,
which could be api’s, numerical constants or factors.
Ex : product 1 for different quantity from 100 to 200 =>adj factor 0.95=> ;
200 to 300 =>adj factor 0.90;
So the actual price if the customer ordered 250 units would be 1 * 0.90;
so if he orders more he will get less price as the adj factor is less;
Ex : product 2 for different amounts from $100 to $500 =>adj factor 0.99 =>;
$500 to $800 =>adj factor 0.80; etc;
16). What is volume type, break type ??
Typically on a modifier,we specify volume type and break types;
Volume type means like based on Quantity, Amount ;
ex if the quantity is between 1 to 110, $10 otherwise $20;
ex if the amount is between $100 to $250, $10 otherwise $20;
break type means Point or Recurring ; point means only one %; recurring means
for each unit it will apply %.
17) What is the significance of the autoamtic flag in the modifier ?
The automatic flag is there at both header and details level in the modifier.
the header flag only helps us in defaulting to all the lines, the line level
is more important and that is waht is taken into consideration.
18) what is pricing phase ?
Ex : this is because sometimes we might have a deal like if you buy a and b
products, then we give 50% off the product c, and this can be possible only
after you enter all the lines.
The three important values for this are
Enter line => pricing happesn as you tab out
list line adjusmtent=> pricing happen as you move out of the line
All line adjustment => pricing happens when you enter all the lines and save the record.
19). What is the main use of the Modifiers ?
Modifiers are very important in that they provide the functionality of pricing phases
or adjustments (like line level, all lines etc). This functionality allows you to
define pricing scenarios like buy 1 get 1 free ,buy 1 get 50% off the other etc.I
believe modifiers are mostly useful when you have multiple line items on the orders
and when they have pricing dependency.
Modifiers lets you use qualifiers which lets which customers can get qualified for these.
(although even at the price list you can do that)
20). Can we define any number of modifier lists ?
Yes. Ex : create two automatic modifier lists on an item and place that item on SO,
and check to see which one is applied ;
The system applies both the modifiers i.e it will apply all the eligible modifiers,so
you can define any number of MOdifiers as you may wish.
21). why is that even though you have chose the discount list as the modifier type,
the surcharge is being appliced at the line level.?
22). How do we implement buy 1 get 1 free in Advanced Pricing?
Any kind of pricing scenarios can be implemented in Oracle Advanced pricing using
the modifiers.For this we need to create a Modifier of list type "Promotional Goods".
So the steps are
Create a Modifier of type "Promotion".
At the line level,create a line type of "Promotional Goods" and enter the details
of the item ,say "myitem" for which you are giving this deal.
Go to the detail of the lines and there you will see the "Get" section,which means
here you enter the details of the items that the customer gets for free.
Since you are giving the same item for free, enter the same item "myitem" and enter
the percent as 100%,which means you are giving another same item 100% free.
If you are giving another item for 50% free (i.e it is buy 1 ,get another 50% off)
then enter the percentage as 50%.
1 comment:
Hey,
I have a requirement to update the modifier lines which are of Discount type to Price break type via script.
Can you please help with a script to do so,as i am new to pricing, dont know how to write the program for it.
Regards,
Anchal
Post a Comment