Price Book in Advance Pricing in Oracle Apps

Price Book is another new feature offered in R12 Advance pricing Module of Oracle Application, it allows customer to generate and publish lists of products with their related prices.
These prices are generated for a full or delta price book. Multiple publishing options are available.  This powerful tool will help provide greater insight to help, manage and maintain overall pricing strategy.

Price Book is a new Pricing report that displays item list and net prices (for a specific customer) based on a specific pricing date.
Within the price book, you can view details of the
    1.List price,
    2.Discounts,
    3.Surcharges,
    4.Price breaks, and
    5.Freight and special charges.
The Price book accepts various inputs for the price calculation.  These inputs include
    1.Pricing effective date.
    2.Pricing attributes.
    3.Qualifiers.


It also taken into consideration
    1.Pricing Perspective (request type – ONT/ASO etc),
    2.Currency,
    3.Customer,
    4.Customer (account) number,
    5.Operating unit,
    6.Pricing effective date,
    7.Quantity, product(s) etc

You can create two types of price books. 
A full price book contains all items and associated prices as of a specific pricing effective date. 
The Delta Price Book is generated as of a new pricing effective date, and displays any deltas in items or prices with respect to the Full price book of the same name, for the same customer and with all other input criteria remaining the same. 
Multiple publishing options are available after the price book is generated , and these are e-mail, print, XML message, View Document and via API.

Price Book Request:
A price book can be requested via the
1.Price book HTML UI,
2.XML Message, or
3.Price Book API. 
Then the user specifies the criteria for generation (such as pricing calculation criteria like quantity, items, currency) because behind the scenes, a pricing engine call is made to search across all your pricing setups to generate the price book.
Once a price book is generated we have lot of output options like view/print/email as shown below.
Advanced Pricing provides the following public APIs for the price book feature:
1.Create Price Book API and
2.Get Price Book API.
Third Party applications integrated with Advanced Pricing can call these APIs to generate or retrieve a price book.

API
QP_PRICE_BOOK_PUB.Create_Publish_Price_Book
QP_PRICE_BOOK_PUB.Get_Price_Book

Advantages –
    1.Increase Insight
    2.Help in monitoring the price changes for a ct. from time to time.

Set Up –
    1.Set the Profiles/parameters/Printer/XML Publisher /Gateway

Please refer user guides for Setup

For More details, or if you want to Integrate your custom application with Advance pricing and want to use Price Book API feel free to contact me eoracleapps@hotmail.com

For additional reference ,please refer documentations at www.oracle.com

BUCKETS IN ORACLE PRICING

Buckets

Pricing buckets control how discounts and other benefits are calculated across phases. Groupingdiscounts and benefits into buckets helps determine the net selling price across all pricing phases. 
Null Bucket.

Modifiers that are assigned to a Null bucket are applied last and always adjust from the list price. Order level modifiers must be in the Null bucket. The pricing engine uses the following steps when calculating the selling price for Null buckets: 

1. Calculates percent discounts using the list price.
2. Sums all bucket modifier values to create a bucket subtotal.
3. Applies the subtotal after the last numbered bucket.

Suppose that the following buckets are set up, each with a different adjustment: 

• Null bucket: a 50% discount is assigned to the Null bucket.
• Bucket 1: a 10% discount is assigned.
• Bucket 2: a 10% surcharge is assigned.

The buckets are used to calculate the price for an SP ATO Model with a list price of $55. The following table shows how the final price is calculated: 


The below are the steps to assign modifiers to different buckets in Oracle Apps R12.

Update Profile Option

Goto:  System Administrator -> Profile Options

Name: QP: Allow Buckets For Manual Modifiers 
Value: Yes 

Update Pricing Modifier Setup to add buckets

Modifier Name: One-Time Discount

Bucket: 4

Once the buckets are assigned, the modifiers are applied on the priority basis.

QUERY TO DERIVE QUALIFIER ATTRIBUTE AND CONTEXT FROM MODIFIER NAME IN ORACLE APPS

SELECT
  c.list_line_no "Modifier Line No",
  b.name "Modifier Name",
  b.description  "Modifier Description",
  a.qualifier_context,
  a.qualifier_attribute,
  a.qualifier_attr_value
FROM qp_qualifiers_v a,
  qp_list_headers b,
  qp_list_lines c
WHERE a.list_header_id=b.list_header_id
and c.list_header_id=b.list_header_id
and c.list_line_id = a.list_line_id
and b.name = &Modifier_Name
ORDER BY a.creation_date DESC;

Oracle Pricing Table List

select * from QP_ADV_MOD_PRODUCTS

select * from QP_ARCH_CRITERIA_HEADERS

select * from QP_ARCH_CRITERIA_LINES

select * from QP_ARCH_LIST_HEADERS_B

select * from QP_ARCH_LIST_HEADERS_TL

select * from QP_ARCH_LIST_LINES

select * from QP_ARCH_PRICING_ATTRIBUTES

select * from QP_ARCH_QUALIFIERS

select * from QP_ARCH_RLTD_MODIFIERS

select * from QP_ARCH_ROW_COUNTS

select * from QP_ATTRIBUTE_DEFNS

select * from QP_ATTRIBUTE_GROUPS

select * from QP_ATTRIBUTE_SOURCING

select * from QP_CACHE_DO_SIZES

select * from QP_CACHE_KEYS_TMP

select * from QP_CACHE_KEY_STATS

select * from QP_CACHE_STATS

select * from QP_COUPONS

select * from QP_CURRENCY_DETAILS

select * from QP_CURRENCY_LISTS_B

select * from QP_CURRENCY_LISTS_TL

select * from QP_DEBUG_FORMULA_STEP_VALUES

select * from QP_DEBUG_REQ

select * from QP_DEBUG_REQ_LDETS

select * from QP_DEBUG_REQ_LINES

select * from QP_DEBUG_REQ_LINE_ATTRS

select * from QP_DEBUG_REQ_RLTD_LINES

select * from QP_DEBUG_TEXT

select * from QP_DISCOUNTS_UPG_TEMP

select * from QP_DISCOUNT_MAPPING

select * from QP_DOCUMENTS

select * from QP_EVENT_PHASES

select * from QP_FACTOR_LIST_ATTRS

select * from QP_FORMULAS_EBS_LOG

select * from QP_FORMULA_LN_EBS_LOG

select * from QP_FORMULA_STEP_VALUES_TMP_T

select * from QP_FWK_DELAYED_REQUESTS

select * from QP_GRANTS

select * from QP_INTERFACE_ERRORS

select * from QP_INTERFACE_LIST_HEADERS

select * from QP_INTERFACE_LIST_LINES

select * from QP_INTERFACE_PRICING_ATTRIBS

select * from QP_INTERFACE_QUALIFIERS

select * from QP_INT_FML_STEP_VALS_T

select * from QP_INT_LDETS_T

select * from QP_INT_LINES_T

select * from QP_INT_LINE_ATTRS_T

select * from QP_INT_PROFS_T

select * from QP_INT_RLTD_LINES_T

select * from QP_JSESSIONS

select * from QP_LIMITS

select * from QP_LIMIT_ATTRIBUTES

select * from QP_LIMIT_BALANCES

select * from QP_LIMIT_TRANSACTIONS

select * from QP_LIST_HEADERS_B

select * from QP_LIST_HEADERS_EBS_LOG

select * from QP_LIST_HEADERS_TL

select * from QP_LIST_HEADER_PHASES

select * from QP_LIST_HEADER_TEMP

select * from QP_LIST_LINES

select * from QP_LIST_LINES_EBS_LOG

select * from QP_LIST_LINES_TEMP

select * from QP_LIST_LINES_TMP

select * from QP_PARAMETERS_B

select * from QP_PARAMETERS_TL

select * from QP_PARAMETER_VALUES

select * from QP_PATTERNS

select * from QP_PATTERN_PHASES

select * from QP_PB_INPUT_HEADERS_B

select * from QP_PB_INPUT_HEADERS_TL

select * from QP_PB_INPUT_LINES

select * from QP_PRC_CONTEXTS_B

select * from QP_PRC_CONTEXTS_TL

select * from QP_PREQ_LDETS_TMP_T

select * from QP_PREQ_LINES_TMP_T

select * from QP_PREQ_LINE_ATTRS_TMP_T

select * from QP_PREQ_LINE_ATTR_FORMUL_TMP_T

select * from QP_PREQ_QUAL_TMP_T

select * from QP_PREQ_RLTD_LINES_TMP_T

select * from QP_PRICE_BOOK_ATTRIBUTES

select * from QP_PRICE_BOOK_BREAK_LINES

select * from QP_PRICE_BOOK_HEADERS_ALL_B

select * from QP_PRICE_BOOK_HEADERS_TL

select * from QP_PRICE_BOOK_LINES

select * from QP_PRICE_BOOK_LINE_DETAILS

select * from QP_PRICE_BOOK_MESSAGES

select * from QP_PRICE_FORMULAS_B

select * from QP_PRICE_FORMULAS_TL

select * from QP_PRICE_FORMULA_LINES

select * from QP_PRICE_REQ_SOURCES

select * from QP_PRICING_ATTRIBUTES

select * from QP_PRICING_ATTRIBUTES_TEMP

select * from QP_PRICING_ATTRIBUTES_TMP

select * from QP_PRICING_ATT_EBS_LOG

select * from QP_PRICING_PHASES

select * from QP_PTE_REQUEST_TYPES_B

select * from QP_PTE_REQUEST_TYPES_TL

select * from QP_PTE_SEGMENTS

select * from QP_PTE_SOURCE_SYSTEMS

select * from QP_QUALIFIERS

select * from QP_QUALIFIERS_EBS_LOG

select * from QP_QUALIFIERS_FWK_DUMMY

select * from QP_QUALIFIERS_TEMP

select * from QP_QUALIFIER_RULES

select * from QP_RLTD_MODIFIERS

select * from QP_RLTD_MOD_EBS_LOG

select * from QP_SEGMENTS_B

select * from QP_SEGMENTS_TL

select * from QP_SOURCESYSTEM_FNAREA_MAP

select * from QP_UI_QUERIES_TL

select * from QP_UI_QUERY_COLUMNS

select * from QP_UPGRADE_ERRORS

select * from QP_UPG_LINES_DISTRIBUTION

Pricing Information Query

SELECT qph.list_header_id,
       qph.name,
       qph.description,
       qphh.start_date_active,
       qphh.currency_code,
       qphh.source_system_code,
       qphh.active_flag,
       qphh.orig_system_header_ref,
       qphh.orig_org_id,
       qphh.global_flag,
       qpl.list_line_id,
       qpl.start_date_active,
       qpl.end_date_active,
       qpl.arithmetic_operator,
       qpl.operand,
       qpl.orig_sys_line_ref,
       qpp.pricing_attribute_id,
       qpp.product_attribute_context,
       qpp.product_attribute,
       qpp.product_attr_value,
       qpp.product_uom_code,
       qpp.comparison_operator_code,
       qpp.orig_sys_pricing_attr_ref,
       mtl.inventory_item_id,
       mtl.segment1,
       mtlc.cross_reference_type,
       mtlc.cross_reference
FROM apps.qp_list_headers_b qphh,
     apps.qp_list_headers_tl qph,
     apps.qp_list_lines qpl,
     apps.qp_pricing_attributes qpp,
     apps.mtl_system_items_b mtl,
     apps.mtl_cross_references_b mtlc
WHERE     qph.list_header_id = qphh.list_header_id
      AND qph.list_header_id = qpl.list_header_id
      AND qph.list_header_id = qpp.list_header_id
      AND qpl.list_line_id = qpp.list_line_id
      AND mtl.inventory_item_id = qpp.product_attr_value
      AND mtl.organization_id = (SELECT UNIQUE master_organization_id
                                 FROM mtl_parameters)
      AND mtl.inventory_item_id = mtlc.inventory_item_id
      AND SYSDATE BETWEEN qpl.start_date_active
                      AND  NVL (qpl.end_date_active, SYSDATE)
      AND SYSDATE BETWEEN qphh.start_date_active
                      AND  NVL (qphh.end_date_active, SYSDATE)
      AND qph.name LIKE '%&priceListName%';

Oracle Pricing Table list

QP_LIST_HEADERS_B
QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.

QP_LIST_LINES
QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B. This table stores all types of list lines; price list lines, all types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Lookup Type, 'LIST_LINE_TYPE_CODE'.

QP_PRICING_ATTRIBUTES
QP_PRICING_ATTRIBUTES stores product information and pricing attributes. The PRODUCT_ATTRIBUTE and PRODUCT_ATTRIBUTE_VALUE columns identify the product or level in the item hierarchy, i.e item context at which the price or modifier is set. The PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE and PRICING_ATTR_VALUE_FROM columns store the pricing attributes which further define what is being priced. If the PRICING_ATTRIBUTE_CONTEXT is VOLUME the pricing attributes column stores the break unit attribute, e.g. item quantity and both the PRICING_ATTR_VALUE_FROM and PRICING_ATTR_VALUE_TO columns can be populated. The Product information is repeated for all pricing attributes. A record is always created for the VOLUME context which may or may not have an attribute defined.
Tables 

Qp_interface_list_header   
qp_interface_list_lines   
qp_interface_qualifiers   
qp_interface_pricing_attribs   
qp_interface_errors   
qp_list_headers_b   
qp_list_headers_tl   
qp_list_lines   
qp_qualifiers   
qp_pricing_attributes   
qp_rltd_modifiers

Relationship

QP_LIST_HEADERS_B  <---LIST_HEADER_ID ---> QP_LIST_LINES

QP_LIST_LINES <---LIST_HEADER_ID,LIST_LINE_ID ---> QP_PRICING_ATTRIBUTES

QP_PRICING_ATTRIBUTES <---PRODUCT_ATTR_VALUE <> TO_CHAR(MSI.INVENTORY_ITEM_ID)---> mtl_system_items_b

Useful Queries

 

    /* Formatted on 7/18/2014 11:02:10 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');           --Price List Name 
     
/* Formatted on 7/18/2014 11:02:04 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name 
     
     
/* Formatted on 7/18/2014 11:01:59 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');--Price List Name 
     
/* Formatted on 7/18/2014 11:01:53 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;
    /* Formatted on 7/18/2014 11:01:45 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');                --Price List Name 
     
/* Formatted on 7/18/2014 11:01:35 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name 
     
     
/* Formatted on 7/18/2014 11:01:29 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');       --Price List Name

  /* Formatted on 7/18/2014 11:01:23 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;

Tables Used in Oracle Pricing

Following Tables used to store all price List Data and used
Frequently in reporting and oracle Internal operations

 OE_PRICE_ADJUSTMENTS
This table is used to store price adjustments that have been applied
to an order or a line. The column automatic flag indicates if the
adjustment was applied automatically or manually. Manual discounts are created with applied_Flag = Y.

/* Formatted on 7/18/2014 10:33:26 AM (QP5 v5.115.810.9015) */
SELECT price_adjustment_id,
       creation_date,
       header_id "Order Header",
       automatic_flag "discount applied automatically",
       line_id "ORDER LINE_ID",
       orig_sys_discount_ref "Original discount reference",
       list_header_id "Header Id of the Modifier",
       list_line_id "Line id of the Modifier",
       list_line_type_code "Line Type of the Modifier",
       accrual_flag "adjustment is accrued",
       benefit_qty "Quantity accrued",
       benefit_uom_code
FROM ont.oe_price_adjustments

 OE_PRICE_ADJ_ATTRIBS
This table stores information on qualifiers and pricing attributes,
 which to that corresponding price adjustment line qualified for
 “price_adjustment_id” is link between this table and ont.oe_price_adjustments

/* Formatted on 7/18/2014 10:33:19 AM (QP5 v5.115.810.9015) */
SELECT price_adjustment_id,
       pricing_attr_value_from "from Value pricing Attribute",
       pricing_attr_value_to "To Value pricing Attribute",
       comparison_operator "Operators",
       flex_title "pricing_context Flex_name",
       price_adj_attrib_id,
       lock_control
FROM ont.oe_price_adj_attribs;


 OE_PRICE_ADJ_ASSOCS
This table stores the association between Order lines and price adjustments
and also between price adjustments. One adjustment may be a result of benefit
on one or more order lines. “price_adjustment_id” is link between this table and ont.oe_price_adjustments

/* Formatted on 7/18/2014 10:33:14 AM (QP5 v5.115.810.9015) */
SELECT line_id "Order Line Id",
       price_adjustment_id,
       rltd_price_adj_id "price_adjustment_id"
FROM ont.oe_price_adj_assocs;

Below queries are for Oracle Applications Order Management and Advance Pricing Modules

SELECT   l.list_line_id,
         q.qualifier_grouping_no,
         q.qualifier_id,
         q.qualifier_context,
         q.qualifier_attr_value,
         q.comparison_operator_code,
         q.qualifier_precedence,
         q.qual_attr_value_from_number,
         q.qualifier_attribute,
         q.end_date_active,
         l.end_date_active,
         h.end_date_active
  FROM   qp_list_headers_all h, qp_list_lines l, qp_qualifiers q
 WHERE       h.list_header_id = l.list_header_id
         AND h.list_header_id = q.list_header_id
         AND h.list_header_id = &list_id
         AND NVL (h.end_date_active, SYSDATE) >= SYSDATE
         AND NVL (l.end_date_active, SYSDATE) >= SYSDATE
         AND NVL (q.end_date_active, SYSDATE) >= SYSDATE;

        =============  ***************************   ===============

SELECT   q.qualifier_id,
         q.qualifier_context,
         q.qualifier_attribute,
         qualifier_attr_value,
         ct.prc_context_id,
         qs.segment_code
  FROM   qp_qualifiers q, qp_prc_contexts_b ct, qp_segments_b qs
 WHERE       q.list_header_id = &ListHeaderID            
         AND ct.prc_context_type = 'QUALIFIER'
         AND q.qualifier_context = ct.prc_context_code
         AND qs.prc_context_id = ct.prc_context_id
         AND qs.segment_mapping_column = q.qualifier_attribute

SQL Query to Extract Oracle Pricing Agreements

/* Formatted on 7/18/2014 9:28:01 AM (QP5 v5.115.810.9015) */
SELECT oat.name "Agreement name",
       oab.revision_date "Revision Date",
       hca.account_number "Bill to customer number",
       hp.party_name "Bill to customer name",
       rt.name "Agreement terms",
       oab.start_date_active "Effective date from",
       oab.end_date_active "Effective date to",
       oab.attribute1 "Meter reading digits",
       qp_price_list_line_util.get_product_value ('QP_ATTR_DEFNS_PRICING',
                                                  qppr.product_attribute_context,
                                                  qppr.product_attribute,
                                                  qppr.product_attr_value
       )
          "Product Value",
       msi.description "Product description",
       qppr.product_uom_code "Unit of measure",
       flv_line_type.meaning "Line Type",
       flv_price_brk.meaning "Price Break Type",
       flv_arth_oprtr.meaning "Application Meansure",
       qpll.start_date_active "Start Date",
       qpll.end_date_active "End Date",
       qppr.pricing_attribute_context "Pricing Context",
       (SELECT qpst.seeded_segment_name
        FROM apps.qp_prc_contexts_b qpcb,
             apps.qp_segments_b qpsb,
             apps.qp_segments_tl qpst
        WHERE     1 = 1
              AND qppr.pricing_attribute_context = qpcb.prc_context_code
              AND qpcb.prc_context_type = 'PRICING_ATTRIBUTE'
              AND qpcb.prc_context_id = qpsb.prc_context_id
              AND qpsb.segment_id = qpst.segment_id
              AND qpsb.segment_mapping_column = qppr.pricing_attribute)
          "Pricing Value",
       qppr.pricing_attr_value_from_number "From Value",
       qppr.pricing_attr_value_to_number "To Value",
       qpll1.operand price
FROM apps.oe_agreements_tl oat,
     apps.oe_agreements_b oab,
     apps.hz_cust_accounts hca,
     apps.hz_parties hp,
     apps.ra_terms_tl rt,
     apps.qp_pricing_attributes qppr,
     apps.qp_list_lines qpll,
     apps.qp_list_lines qpll1,
     apps.mtl_system_items_b msi,
     apps.fnd_lookup_values flv_line_type,
     apps.fnd_lookup_values flv_price_brk,
     apps.fnd_lookup_values flv_arth_oprtr,
     apps.qp_list_headers_b qplh
WHERE     1 = 1
      AND oat.agreement_id = oab.agreement_id
      AND oab.sold_to_org_id = hca.cust_account_id
      AND hca.party_id = hp.party_id
      AND oab.term_id = rt.term_id
      AND oab.price_list_id = qplh.list_header_id
      AND qplh.list_header_id = qpll.list_header_id
      AND qplh.list_header_id = qpll1.list_header_id
      AND qpll.list_line_type_code = 'PBH'
      AND qpll1.list_line_id = qppr.list_line_id
      AND qpll.revision_date = qpll1.revision_date
      AND qpll1.list_line_type_code = 'PLL'
      AND msi.inventory_item_id = qppr.product_attr_value
      AND flv_line_type.lookup_code = qpll.list_line_type_code
      AND flv_line_type.lookup_type = 'LIST_LINE_TYPE_CODE'
      AND flv_price_brk.lookup_code = qpll.price_break_type_code
      AND flv_price_brk.lookup_type = 'PRICE_BREAK_TYPE_CODE'
      AND flv_arth_oprtr.lookup_code = qpll.arithmetic_operator
      AND flv_arth_oprtr.lookup_type = 'ARITHMETIC_OPERATOR'
      AND msi.organization_id = 141;