SQL to check Order Line Pricing

When we do a pricing of a Sales Order  on a bulk level we may need to check the Re-Pricing Status to see if all the lines have been re-priced successfully or not.

In order to see the if the line pricing is successful or not we can use the below SQL

    select distinct ooh.org_id,ool.creation_date,ool.line_id,ooh.order_number,ool.line_number,ool.ordered_item,ool.unit_selling_price,qlh.name,qll.operand,qll.start_date_active,ool.flow_status_code
 ,ool.calculate_price_flag,ool.unit_list_price
 from oe_order_headers_all ooh,
      oe_order_lines_all ool,
      wsh_delivery_details wdd,
      qp_list_headers qlh,
       qp_list_lines qll,
       qp_pricing_attributes qpa,
       mtl_system_items_b msib
 where ooh.header_id = ool.header_id
 and ool.line_id = wdd.source_line_id
 and wdd.released_status in ('R','B')
 and qlh.list_header_id = qpa.list_header_id
 and qpa.product_attr_value = to_char(msib.inventory_item_id)
 and msib.organization_id = :warehouse_id
 and msib.segment1 = ool.ordered_item
 and qpa.list_line_id = qll.list_line_id
 and ool.price_list_id = qlh.list_header_id
and ooh.order_number = :order_number
 and ool.unit_selling_price <>qll.operand
;

For the rows returned by the SQL we need to check the value of calculate_price_flag.
If Calculate_price_flag = N , if we see on front end the value will be Freeze which means that the item re-pricing will not be allowed,

If there are some records where calculate_price_flag = Y and no re-pricing is done please check if any manual modifier is applied on order line, If any modifier is applied overrding the Price List Price then re-pricing will not take place

Order Line Pricing Issue , Line Not Getting Priced using a Price List

Many times when Trying to create a Sales Order we enter an item on the Order Line but the Pricing of the line does not happen  , and in some cases when selecting the item on the  Order line for a specific Price List we get and error that " Item XXXX and UOM EA not available on the price list"

In such scenarios we have to check the Setup of the concerned price list to determine the root cause of the issue :

1. Go to the Price List Setup and see if the Item is actually Available on the price list or not by entering the Price List name and the Item details

If the item is not available on the Price list then maintain the item on the price list and re-try Order Line Creation
2. If the Item is available on the Price List , then scroll right on the Item Setup line and check the         Start Date and End Date on the price list line. Ensure that the line is not end dated and the start Date if available is less than the Order Pricing Date

If there is an issue with the pricing dates , correct it and re-try booking the order

3. If the price list Line setup is correct then check for the Qualifier Setup on the Price List by clicking on the Qualifier Tab

   

The Qualifiers are used to specify the conditions which Govern if this price list should be Selected or not for the Order Lines pricing

An important thing to know here is the Grouping number

Records of the Grouping number will be ANDed with each other and records of different Grouping  Number will follow an OR condition

An important thing to note here is about Grouping Number : -1 , a grouping number of -1 will always be included in the condition formation and will be ANDed with the conditions of other Grouping numbers

Many a times the Price List Qualifiers fail and give an issue in Order Line Pricing

Tables storing Price list information :

QP_LIST_HEADERS --> Price List Header information

QP_LIST_LINES --> Price details

QP_PRICING_ATRRIBUTES --> Item details

Below SQL can be used to check item availability on a price-list

select qlh.name,msib.segment1,qll.operand

  from apps.qp_list_headers qlh,

           apps.qp_list_lines qll,

           apps.qp_pricing_attributes qpa,

           apps.mtl_system_items_b msib

 where qlh.name = 'PRICE_LIST_NAME'

 and qlh.list_header_id = qpa.list_header_id

 and qpa.product_attr_value = msib.inventory_item_id

 and msib.organization_id = :WAREHOUSE_ID

 and msib.segment1 = 'ITEM_NUMBER'

 and qpa.list_line_id = qll.list_line_id;