Integrating with Oracle Advanced Pricing

This chapter covers the following topics:

Overview of Integrating with Oracle Advanced Pricing

Oracle Advanced Pricing is an API-based engine that can be called by any integrating application. This chapter provides certain essential information needed for the successful integration with Advanced Pricing.

For more information, see:

Integration Steps Required for Pricing

The following steps describe the process required to integrate with Oracle Pricing.

Step 1. Determine your end to end pricing business needs

See the Oracle Advanced Pricing User's Guide and the Oracle Advanced Pricing Implementation Guide to determine which pricing features need to be supported by your application.

  1. Evaluate your transaction variations and choose the pricing events to be called at what phase of your transaction cycle. To create new pricing phases or learn more about pricing phases and events, see: Oracle Advanced Pricing User's Guide.

  2. Determine your business need to choose a pricing transaction entity (PTE). If a new PTE is required, see: Creating a New Pricing Transaction Entity to learn about how to create a PTE. When you create a new PTE, the seeded contexts and attributes are not available. New contexts and attributes need to be created for the newly created PTE, or existing attributes need to be linked to the PTE.

  3. The pricing engine needs to be called with a request type. Each request type is linked to a PTE. Request type determines two things: the price lists and modifiers to be searched during pricing engine call and the attribute mapping rules to be run when build_context API is called. Note that each modifier/price list is associated with a source system and hence with a PTE. The pricing engine will look at modifiers or price lists belonging to the source systems linked with the PTE.

  4. Attribute Mapping rules are seeded by individual transaction systems for their respective PTEs. The PTE is linked to a set of request types and source systems. Determine if your transaction data corresponds to the attribute mapping rules defined already. If not, refer to attribute mapping to learn how to define a custom mapping.

Step 2. Determine your pl/sql global structure

Before calling the API to build the contexts, it is necessary to populate a global record structure corresponding to the request type with the information pertaining to the summary line (order header) or the request line (order line) for which the qualifier/pricing attribute information needs to be built. Request type ONT uses a global structure oe_order_pub.g_line for order line and oe_order_pub.g_hdr for order header. The request type ASO uses a global structure aso_pricing_int.g_line_rec and aso_pricing_int.g_header_rec.

For more information about the oe_order_pub.g_line/g_hdr structures and the Order Capture API doc for the aso_pricing_int.g_line_rec and aso_pricing_int.g_header_rec, see the Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide.

Determine if your request can be mapped to one of these structures. Attribute mapping rules need to be defined specific to the structure that will be used by the calling application. If you find that your request structure cannot be mapped to one of these, then you need to define a new structure and write attribute mapping rules using this new structure.

Step 3. Determine the tables to hold the adjustment information

If you need to hold the information returned by pricing engine, for showing the breakup of all the benefits given to your order/quote/pricing request, determine if you can use the price adjustment tables used by Order Management/Order Capture.

OE_PRICE_ADJUSTMENTS/ASO_PRICE_ADJUSTMENTS

Holds the price adjustments

OE_PRICE_ADJ_ATTRIBS/ASO_PRICE_ADJ_ATTRIBS

Holds the qualification conditions applied to give the adjustments

OE__PRICE_ADJ_ASSOCS/ ASO_PRICE_ADJ_RELATIONSHIPS -

Holds relationships between multiple adjustment records for price breaks, promotional goods and other item benefits

If you need to create your own tables, please use the table definition of the above tables as a guideline and create your tables.

Step 4. Populate the pricing request structures

The following is the API structure for QP_PREQ_PUB.PRICE_REQUEST:

(p_control_rec IN
QP_PREQ_GRP.CONTROL_RECORD_TYPE,
x_return_status OUT VARCHAR2,
x_return_status_text OUT VARCHAR2
);

  1. Populate the control record p_control_rec.

    For details on the parameters of the control record, see theOrder Management Suite APIs and Open Interfaces Manual, Pricing APIs. The control record determines the way the pricing engine returns the price.

  2. Call the API: QP_Price_Request_Context.Set_Request_Id();

    Set the pricing request_id to enable the pricing engine to identify the data in the pricing temporary tables that belong to the current pricing engine call. The pricing engine will not delete the temporary table data before each pricing engine call. Instead, the data from the previous pricing engine call may remain in the pricing temporary tables. The calling application needs to set the request_id each time the pricing engine call is made. This will be the first step.

    The API QP_Price_Request_Context.Set_Request_Id() callsets a unique request_id for every pricing engine call made in the same session without commits or rollbacks. This negates the need to delete the data in the pricing temporary tables between calls. Also, if the request_id is not set, then the calling application needs to delete the data in the pricing temporary tables before making the next pricing engine call without a commit or rollback. Remember that a commit or rollback purges the data in the temporary tables so that the records do not need to be deleted.

  3. Populate the global structure used by the attribute mapping. For example if you are using request type ONT, populate the PL/SQL structure OE_ORDER_PUB.G_LINE.

  4. Call the API: QP_ATTR_MAPPING_PUB.Build_contexts

    QP_Attribute_Mapping_PUB.Build_Contexts
(          p_request_type_code    IN      VARCHAR2,
        p_line_index                  IN      NUMBER,
        p_pricing_type_code           IN      VARCHAR2
);

    This API evaluates the public record structures listed in the attribute mapping rule; therefore, it is necessary that the request line information for each line is loaded into the request structure and the API is called for each line. The build context API inserts the mapped attributes into the pricing temporary tables with the passed line_index. Therefore, you must use the same line_index that is used for the request lines.

    If no attribute mapping rules or record structure are found, then the qualifiers and pricing attributes can be inserted into the qp_preq_line_attrs_tmp. For example, if you want the pricing engine to fetch the price from a particular price list, pass the price list in qp_preq_line_attrs_tmp with qualifier_context=MODLIST.

    The qualifier_attribute=QUALIFIER_ATTRIBUTE4 and qualifier_attr_value_from holds the price_list_id.

    Remember that the inventory_item_id is passed in the temporary table qp_preq_line_attrs_tmp with pricing_context=ITEM pricing_attribute=PRICING_ATTRIBUTE1 and pricing_attr_value_from holds the item_id.

    The build context API needs to be called with a p_pricing_type_code of L for the request lines.

  5. For more information about the Copy_Line_to_request to load the request lines, see: Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide, Sample Code using Order Management Structure. Call the API QP_PREQ_GRP.Insert_Lines2 to insert the request lines to the pricing temporary table qp_preq_lines_tmp.

    For more information about API structures and parameter descriptions, see: Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide.

  6. Append any user-entered pricing attributes and Asked for promotions/deals or coupons to the temporary table qp_preq_line_attrs_tmp. Set the validated_flag of qp_preq_line_attrs_tmp to Y for "Asked For" promotions, if you do not want pricing to check for the qualifications, before applying the promotion.

    For more information about the Append_ask_for procedure, see the Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide, Sample Code using Order Management Structure. These attributes can be inserted into the temporary table qp_preq_line_attrs_tmp using the API QP_PREQ_GRP.insert_line_attrs2.

    For more information about API structures and parameter descriptions, see the Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide.

    Note: When passing the keys to the pricing interface and temporary tables, the keys such as line_index, line_detail_index and so on need to be within the range for a pls_integer datatype.

  7. To apply manual adjustments or to have the pricing engine consider any other adjustments, insert those records into the pricing temporary table qp_preq_ldets_tmp with the applied_flag and updated_flag set to Y (Yes). Pass adjustments or modifiers to the pricing engine using the QP_PREQ_GRP.insert_ldets2 API.

  8. Ensure that the Summary Line is populated.

    Every request to the pricing engine must contain a summary record in qp_preq_lines_tmp with information from order header. The pricing engine will apply the order level modifiers against the summary line passed. If the summary line is not passed, the pricing engine will not apply any order level adjustments.

    Set the line_type_code to ORDER for the summary line. For order header, repeat steps # 1 to 6, and use p_pricing_type=H while calling QP_ATTR_MAPPING_PUB.build_context() for the summary line.

    For more information on how to populate the summary record using copy_Header_to_request(), seeOracle Order Management Open Interfaces, API, & Electronic Messaging Guide, Sample Code using Order Management Structure.

  9. For Service Lines with Percentage Price, ensure that the Parent Line is passed. For more information about how to price service items, see: Service Item Pricing.

Step 5. Call Price_request()

Call the API:

QP_PREQ_PUB.PRICE_REQUEST
(p_control_rec IN QP_PREQ_GRP.CONTROL_RECORD_TYPE,
x_return_status OUT VARCHAR2,
x_return_status_text OUT VARCHAR2);

Step 6. Interpreting the results of price request

  1. Handling errors:

    The pricing engine can return hard errors and soft errors. The pricing engine call is a success if the value of x_return_status is FND_API.G_RET_STS_SUCCESS.

    The soft errors can indicates the line level exceptions while pricing. These errors are populated in qp_preq_lines_tmp.pricing_status_code. These are the three success codes for a line:

    G_STATUS_NEW
G_STATUS_UPDATED;
G_STATUS_UNCHANGED

    These codes require some action from the calling application:

    G_STATUS_DELETED
G_STATUS_TRANSIENT
G_STATUS_GROUPING
G_STATUS_INVALID_PRICE_LIST
G_STATUS_GSA_VIOLATIONG_STS_LHS_NOT_FOUND
G_STATUS_FORMULA_ERROR
G_STATUS_OTHER_ERRORSG_STATUS_SYSTEM_GENERATED
G_STATUS_BEST_PRICE_EVAL
G_STATUS_INCOMP_LOGIC
G_STATUS_CALC_ERROR
G_STATUS_UOM_FAILURE
G_STATUS_PRIMARY_UOM_FLAG
G_STATUS_OTHER_ITEM_BENEFITS
G_STATUS_INVALID_UOM
G_STATUS_DUP_PRICE_LIST
G_STATUS_INVALID_UOM_CONV
G_STATUS_INVALID_INCOMP
G_STATUS_BEST_PRICE_EVAL_ERROR
G_STATUS_LIMIT_HOLD
G_STATUS_LIMIT_EXCEEDED
G_STATUS_LIMIT_ADJUSTED
G_STATUS_LIMIT_CONSUMED

The GSA violation G_STATUS_GSA_VIOLATION is a special case. Refer to the GSA Violation topic in the following section detailing the GSA Violation behavior in detail.

For more information about the different status codes, see: Oracle Advanced Pricing Implementation Guide, Troubleshooting.

Price List fetched for a request line.

Pricing Engine Interaction Details

This section provides an overview of the features supported by the pricing engine and how the pricing engine processes them. This information includes what the pricing engine returns for each feature and how the request information needs to be passed on subsequent calls for the same request lines to get the same results back again.

Passing adjustments/modifiers to the pricing engine

If the calling application needs to apply specific modifiers to the pricing engine in order for it to apply them against a request line, it needs to be inserted into qp_preq_ldets_tmp with pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED. You can pass adjustments or modifiers to the pricing engine using the QP_PREQ_GRP.insert_ldets2 API.

Manual adjustments

All the automatic modifiers (automatic_flag = Y) of type Discounts and Surcharges that the user has qualified for, that are deleted as part of incompatibility resolution (due to incompatibility setup rules), are returned as manual discounts to the calling application if the profile QP: Return Manual Adjustments is set to Y. In addition to these discounts, all the qualified manual modifiers of type Discounts. Surcharge discounts are also returned to the calling application, unapplied.

Note: When manual adjustments are applied or automatic adjustments are overridden, then changes in the pricing setup for those modifiers are not applicable anymore. The pricing engine will not do a qualification check for those modifiers. For example, if the qualifications change (for example, a change is made to the modifier eligibility criteria), the manually overridden modifiers will continue to get applied even if the request line does not meet the qualification. For example, If you have applied a manual modifier on an order quote and then make changes so that the quote does not qualify for it, the pricing engine does not remove the modifier. This condition also applies to changing qualifiers on the modifier.

You can try the same with an automatic overrideable modifier. If you override the automatic modifier, and change the UOM, the modifier will not be removed from the quote.

Applying manual adjustments

Manual adjustments can be applied in two ways:

Deleting manual adjustments that are applied

To delete manual adjustments that are applied, they need to be deleted from the transaction table on the calling application's side, which stores the applied adjustments returned by the pricing engine. This means that the calling application also needs to delete the attributes and the associations (relationships) for the adjustment. In case the manual adjustment is a price break, the child lines of the price break, the attributes of the price break and the child lines and the relationships between the price break and the child lines also need to be applied.

Once they are deleted from the transaction tables, they will not be passed to the pricing engine in any consequent calls to the pricing engine and will not get applied.

Deleting automatic overridden adjustments that are applied

In case your business supports deleting automatic overridden adjustments, the calling application needs to insert these adjustments as applied_flag = N and updated_flag = Y and these need to be passed to the pricing engine during any consequent pricing engine calls. The pricing engine will not apply this automatic adjustment even if the request line qualifies for it.

Apply automatic overrideable modifiers

In order to apply automatic overrideable modifiers, pass the modifiers to pricing engine by inserting the modifier into qp_preq_ldets_tmp with updated_flag = Y and applied_flag = Y and pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED. The pricing engine will apply this modifier.

Manual/Overrideable price breaks

To apply manual overrideable price breaks, pass the price break header adjustment and its child lines with the relationships between the price break modifier and the child break lines with the overridden operand. The pricing engine evaluates the break and applies the overridden price breaks. Also, remember to pass the volume attributes that the pricing engine returned previously along with the price break modifier and the child lines. The volume attributes are necessary because the pricing engine does not look at the pricing setup to derive the item quantity/amount information on the price break modifier. To insert relationships, use the QP_PREQ_GRP.insert_rltd_lines2 API.

GSA Violation

Pricing setup allows users to create GSA price lists. A GSA violation occurs when the price of an item goes below the GSA price for the item for a non-GSA customer. The pricing engine checks for GSA violation at the end of the pricing engine call. If a GSA Violation has occurred, then the pricing_status_code on the request line is set to QP_PREQ_GRP.G_STATUS_GSA_VIOLATION. The calling application handles the GSA violation by raising a warning or an error message. For GSA customers, the pricing engine gives the price on the GSA price list. This request line can qualify for further discounts for the GSA customer.

The pricing engine does GSA violation checks only if the GSA_CHECK_FLAG on the control record is passed as Y and if the GSA qualifier (Context = CUSTOMER, Attribute = QUALIFIER_ATTRIBUTE15) is passed on the request line with a value N indicating that the customer is a non-GSA customer. The attribute mapping API returns a GSA qualifier based on the gsa_indicator flag checked on the customer in AR or the invoice location has the gsa_indicator set to Y.

In Order Management, if the GSA Violation profile is set to Warning, a warning message is raised. If the profile is set to Error, the application throws an error message.

Bucketing

The pricing engine applies bucketing rules after getting all modifiers to calculate the unit selling price.

Pricing Formulas

The formulas are processed and the operand is evaluated based on the attributes or factors passed to the pricing engine.

Rounding

Rounding refers to the rounding of the list and selling price. Rounding is controlled by the rounding_flag on the control_record, which is entered into the pricing engine and the value of the profile QP: Selling Price Rounding Options. For more information, see: Oracle Advanced Pricing Implementation Guide, Profile Options and for information on the values for the rounding flag, see Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide.

Freight Charges

The freight charges that the order line qualified for are evaluated and the pricing engine applies the maximum freight charge for each charge type(charge_type_code and charge_subtype_code) for the request line. The freight charge does not affect the selling price. Manual and overridden charges that need to be applied, must be passed to the pricing engine with applied_flag set to Y and updated_flag set to Y, just like any manual adjustment (discount and surcharge).

Coupon Issue

When a coupon is issued, the engine generates a unique coupon number. The pricing engine inserts a record into QP_COUPONS table with this unique coupon number. This number may be displayed in the list to show the available coupons for the user to pick. This number is unique. This is the number that can be used to redeem the discount later. One coupon number equals one redemption. Once the coupon number is used (redeemed) it cannot be used again. The user should know the coupon number they were given in order to redeem the coupon.

The next time an order is placed, if the user enters the coupon number, the engine qualifies the discount the coupon is eligible for and applies the discount. The coupon is deleted once the coupon has been redeemed.

Note: For Coupon Issue modifiers, the Benefit Quantity, Benefit UOM, and Coupon Conversion Rate fields are not supported.

The coupons are stored in QP_COUPONS table and are marked redeemed when they are redeemed.

To redeem a coupon, the coupon needs to be passed as a qualifier to the order line to the pricing engine (context = MODLIST, attribute =QUALIFIER_ATTRIBUTE3, value = <coupon number>). The coupon may be stored as an attribute of the line along with the ask_for_promotions. For the pricing engine to give the coupon discount on consequent reprice calls, the coupon needs to be passed as an attribute.

It is possible that the Coupon Issue modifier has qualifiers in the setup. When the coupon is redeemed, the pricing engine does a qualification check to see if all qualifiers are passed and if the qualifiers are not passed, the pricing engine does not qualify the request line for the modifier associated with the coupon. To prevent the pricing engine from doing this qualification check, pass a value of Y to the validated_flag in the qp_preq_line_attrs_tmp against this coupon qualifier record.

In Order Management, if an order qualifies for a coupon, the coupon is not deleted when the order is cancelled or when the item is returned. During a reprice, the pricing engine keeps issuing new coupons.

Item Upgrade

If an order line qualifies for an item upgrade, the item upgrade modifier is applied against that line and can be found in the qp_ldets_v temporary table. In the temp table qp_ldets_v, the column related_item_id has the inventory_item_id of the upgraded item and the column inventory_item_id has the inventory_item_id of the original item. The calling application can replace the originally ordered item on the line with the upgraded item. During reprice, the calling application needs to pass back the original item on the line so that the order is repriced the same way. This can be done by loading at the original inventory_item_id from the item upgrade modifier to the public record structure before attribute mapping.

To set up an item upgrade modifier, an item relationship must be defined between the buy item and the upgrade item in Inventory item relationships with a relationship type of Promotional Upgrade.

Promotional Goods

If an order line qualifies for a promotional goods modifier (PRG), the pricing engine creates a new order line for the free good line. The PRG usually is setup as buy item A get item B at x percent off. In this case, if item A is ordered and if it qualifies for the PRG, the pricing engine creates the new order line with item B and it also applies the x percent discount to this new line. In case of buy 1 get 1 free, this discount is 100 percent.

The new line that is created by the pricing engine can be identified by the value of processed_code in the temp table qp_preq_lines_tmp which is set to QP_PREQ_GRP.G_BY_ENGINE.

The calling application needs to create a new order line to represent the free good line. Remember that the free good item is passed as an attribute in the qp_preq_line_attrs_tmp to that line. The discount on the free good line is passed in the temp table qp_ldets_v. The order line that qualified for the PRG modifier is the parent modifier, and the discount on the free goods line is the child line.

The engine creates a parent child relationship in the temp table qp_preq_rltd_lines_tmp with relationship_type_code as QP_PREQ_GRP.G_GENERATED_LINE, the line_detail_index is the line_detail_index of the parent line and related_line_detail_index is the line_detail_index of the child line. The above information returned by the pricing engine needs to be stored by the calling application in its transaction tables.

During a reprice, the pricing engine public API QP_PREQ_PUB compares the existing free goods line with the promotional goods modifier in the pricing setup. If the promotional goods modifier has not changed, it populates a value of QP_PREQ_GRP.G_STATUS_UNCHANGED on the column process_status on qp_preq_lines_tmp. In this case, the calling application need not make any changes to the free good line. If the pricing setup has changed, then the process_status is populated with a value QP_PREQ_GRP.G_STATUS_UPDATED. For a fresh pricing engine call, where one of the request lines qualifies for the promotional goods line, the new line is created in qp_preq_lines_tmp with process_status = QP_PREQ_GRP.G_STATUS_NEW. In this case, the calling application may insert this line into its transaction system.

When the free goods line is created with process_status = QP_PREQ_GRP.G_STATUS_NEW/G_STATUS_UPDATED, the calling application needs to make another call to calculate freight charges for the free goods lines. For more information about the implicit call for freight charges for promotional goods, see: Freight Charges for the free good line.

When users do not want the free goods line, they can remove it from the order. To make the pricing engine recognize that it does not need to create the free goods again, complete the following steps:

  1. Delete the free goods line, its attributes, its adjustments, and the associations between the buy line and the free good line from the system. Retain the PRG adjustment on the buy line, but mark it as updated by setting the updated_flag = QP_PREQ_GRP.G_YES.

  2. If some other free goods line results because of the same PRG (if the promotional goods modifier is set up as buy A, get B and C free) on the order/quote, mark the discounts against the remaining free good lines as updated by setting the updated_flag = Y.

Now, the pricing engine will not re-create the deleted free goods lines. The same approach needs to be followed if the calling application wants to substitute the free goods item with some other item.

If the buy line is deleted, the calling application needs to delete all the free goods associated with this buy line, the adjustments on the buy line, the adjustments on the free goods lines, and the associations between the buy line and the free goods line.

Freight Charges for the free goods line

The pricing engine does not apply freight charges on the free goods line. To get freight changes for the free goods item, a second pricing engine call needs to be made, which will insert just the free goods line with price_flag QP_PREQ_GRP.G_PHASE and the pricing engine will only search for freight charges for the free goods line. The freight charges do not affect the price on the free goods line. The reason the pricing engine cannot apply the freight charges (if any) on the free goods line is that the pricing engine does not have any attributes mapped for the free goods line to look for freight charges. The pricing engine inserts the free goods line with the pricing information, and the product is the only attribute inserted against this line. To get the freight charges for free goods lines, the calling application needs to make another implicit call to the pricing engine. The freight call needs to be made only when there are lines in qp_preq_lines_tmp with process_status QP_PREQ_GRP.G_STATUS_NEW / G_STATUS_UPDATED. In this case, the calling application can make this implicit call to pass all the free goods lines to the pricing engine. If any line group-based modifiers exist, the order or quote can qualify for a free goods line when the order or quote is repriced. For example, if there are two promotional goods modifiers, if the user adds A and then gets B free, then

When freight charges for B are calculated, the pricing engine could have qualified the order or quote for the free good C if A had also been passed. For this to happen, the calling application needs to pass all the lines on the quote or order in the implicit call to evaluate the freight charges. Make sure the QP_UTIL_PUB.Get_Order_Line_status is called and pass all the lines only when the output of Get_Order_Line_Status passes all_lines_flag = QP_PREQ_GRP.G_YES; otherwise, pass the free good lines only. Get_Order_Line_Status passes all_lines_flag = QP_PREQ_GRP.G_YES if there are line group modifiers or other item discount modifiers. By passing all lines, you are making sure that the quote/order will qualify for all the applicable modifiers and that the price will not change on subsequent reprice.

Other Item Discount

Other item discount (OID) is very similar to the free goods or promotional goods as explained previously except that the engine does not create a new order line. Other Item Discount is set up as buy item A and get x percent discount on item B where both item A and item B are ordered.

Therefore, to qualify for the OID, both item A and item B need to be on the request lines (lines with item A and item B need to be passed to the pricing engine). In this case, the pricing engine applies the OID modifier on the request line with item A, and it applies the discount on the line with item B.

The OID modifier can be found in the qp_ldets_v with created_from_list_line_type = QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT with line_index of the request line with item A which is the primary item on the OID modifier. The OID discount is inserted with line_index of request line having item B. The pricing engine also creates a relationship record between the OID modifier and the discount adjustment on the other line in qp_preq_rltd_lines_tmp with relationship_type_code = QP_PREQ_GRP.G_GENERATED_LINE with line_detail_index as the line_detail_index of the OID modifier and the related_line_detail_index as the line_detail_index of the discount adjustment. All of this information needs to be stored in the transaction tables of the calling application.

Pricing from Configurator

The configurator in Order Management (OM) and Order Capture (OC) uses OM and OC APIs to call the pricing engine. For included items and config items, the unit_price is zero by default. In case the pricing engine is called, the order lines with included items or config items are passed to the pricing engine with price_flag as N or P, depending upon the OM profile, OM: Charges for Included Item.

If the profile is set to Y, then freight charges need to be calculated for the included item. In this case, the price_flag is P, otherwise it is N. The pricing engine does not calculate the unit_price in either case.

Service Item Pricing

Requests for pricing service items can be passed from integrating applications such Oracle Order Management, Oracle Quoting, Oracle Istore, and Oracle Service Contracts.

If an item has a price list line set as a percentage of a price of the parent line (for example, the price of a service item called Gold Support is 10 percent of the price of a parent serviceable item called Platinum Services), then the parent line must be passed in qp_preq_lines_tmp and the relationship between the parent and the child must be passed in qp_preq_rltd_lines_tmp. To pass service relationships and other relationships to pricing , use the API QP_PREQ_GRP.insert_rltd_lines2 to set the following parameters:

If the item needs to be priced over a period of time, such as pricing a service item for a duration of 12 months, the duration must be passed as uom_quantity in the structure qp_preq_lines_tmp. If the service start and end dates are known, they can be passed as contract_start_date/contract_end_date in the structure qp_preq_lines_tmp.

The pricing engine retrieves a price list line for the service item and calculates the percent price based on the parent line's list price. Ideally, the parent line can belong to a different order/quote. The service line must be deleted or passed when the parent line is deleted or updated.

Order Management Attributes passed to Pricing Engine

The following order management attributes are passed by the pricing engine application program interface (API) when pricing service items:

Service Duration and Pricing

When pricing service items, Oracle Advanced Pricing evaluates unit of measure (UOM) conversions to determine the service duration using the following criteria:

The following table compares how pricing evaluates the extended price when service duration is passed and when it is not. The following terms are used in the table:

-- Price List Found in Request UOM, NO UOM Conversion UOM Conversion Required Between Price List UOM and Request UOM Extended Price Formula1
Service duration is passed (Order Management, Quoting and Istore)

  • Unit Price is for entire Service Duration

  • Pricing Quantity same as Product Quantity

  • Unit Price same as on Price List

  • Pricing Quantity is Product Quantity * Service Duration

 Unit Price * Pricing Quantity
Service duration is not passed (Service Contracts)

  • Unit Price same as on Price List

  • Pricing Quantity same as Service Duration, Product Quantity not included

  • Unit Price same as on Price List

  • Pricing Quantity is Service Duration in Price List UOM, Product Quantity not included

 Unit Price * Pricing Quantity * Product Quantity

Note: 1 The application calling the price request (not the pricing engine) calculates the extended price.

Example of Calculating Service Duration and Extended List Price for a Service Item

The following example shows how pricing calculates service duration to determine the extended price for a service item. The tables display these details: the price list setups, the inputs (such as the dates and service duration that pricing receives from the calling application), and the outputs that pricing calculates (such as the unit price). The following price list setup is used in the example:

The following table shows the price request inputs such as the service dates, UOM, and service duration that the calling application sends to pricing. Pricing evaluates these inputs to calculate the extended selling price.

Price Request Inputs
Service Start Date Service End Date Service UOM Service Duration Product Quantity
01-JAN-2006 31-DEC-2007 Year 2 10

The following tables show how pricing calculates the extended list price, unit price, and pricing quantity (the output) when service duration is passed and when it is not. For both scenarios, the extended price is $2400. (The extended price is calculated by the application calling Oracle Advanced Pricing, for example, Oracle Order Management or Oracle Service Contracts.)

Service Dates and Service Duration are Passed
-- Unit Price Pricing Quantity Extended Price1
(Unit Price * Pricing Quantity)
Using PL1 (No UOM Conversion) $240 (list price * service duration: 120 * 2) 10 (same as product quantity) 240*10 = $2400
Using PL2 (UOM Conversion) $10 (same as list price on price list) 240 (product quantity * service duration in PL UOM - Month: 10 * 12 * 2) 10*240 = $2400
Service Dates are Passed but not Service Duration
-- Unit Price Pricing Quantity Extended Price1
(Unit Price * Pricing Quantity * Product Quantity)
Using PL1 (No UOM Conversion) $120 (same as list price on price list) 2 (service duration in PL UOM - Year) 120*2*10 = $2400
Using PL2 (UOM Conversion) $10 (same as list price on price list) 24 (service duration in PL UOM – Month: 12 * 2) 10*24*10 = $2400

1The calling application, not the pricing engine, calculates the extended price after the price request. Note that the extended price is the same in all scenarios.

Note: Service dates and duration are passed in the following situations:

Ask for promotions

The calling application can request that an ask for promotion be applied to the request line by passing the promotion/modifier as a qualifier to the request line. If the promotion is passed as a qualifier with context = MODLIST, attribute = QUALIFIER_ATTRIBUTE1, value_from = list_header_id of the ask for promotion, the pricing engine applies all the modifiers under the ask for promotion to the request line. The calling application can also ask for a specific modifier line to be applied in which case, the modifier line needs to be passed with context = MODLIST, attribute = QUALIFIER_ATTRIBUTE2, value_from = list_line_id of the modifier line that is asked for. This can be done by inserting the ask for promotion/modifier as an attribute in qp_preq_line_attrs_tmp as a part of append ask for procedure.

Remember that the pricing engine does not look at the qualifiers set up on the asked for promotion/modifier if the validated_flag is passed as Y in the qualifier record in qp_preq_line_attrs_tmp. Make sure the validated_flag is passed appropriately. Also, remember to store the asked for promotion into the transaction tables and pass them during every reprice call so that the pricing engine applies it consistently.

Deleting ask for promotions that are applied:

If the ask for promotion needs to be deleted, it needs to be deleted from the transaction table and a pricing engine call needs to be made so that the pricing engine does not apply it any more. Also, if the ask for promotion has a modifier that has been overridden, the pricing engine will not delete it. The calling application needs to delete it from the transaction table. When the asked for promotion is deleted, the adjustments with the list_header_id of the ask for promotion need to be deleted. In case the calling application asked for a specific modifier line, the adjustment with the list_line_id as the value_from of the asked for modifier line needs to be deleted.

Evaluation of passed in modifier

The calling application can request that a modifier be applied to the request line by passing the modifier line as a qualifier to the request line. If the modifier is passed as a qualifier with context = MODLIST, attribute = QUALIFIER_ATTRIBUTE7, value_from = list_line_id of the modifier, the pricing engine applies only the passed in modifier to the request line. Remember that the pricing engine consider the qualifiers of the passed in modifier.

This functionality is available for both the Pricing Search Engines - the Pattern Engine as well as the Traditional Engine.

For pattern engine you must set the profile QP: Pattern Search at Site level (as it is executed via API) and the QP: Pattern Upgrade concurrent program must be run before executing it.

Deleting ask for promotions that are applied:

If the ask for promotion needs to be deleted, it needs to be deleted from the transaction table and a pricing engine call needs to be made so that the pricing engine does not apply it any more. Also, if the ask for promotion has a modifier that has been overridden, the pricing engine will not delete it. The calling application needs to delete it from the transaction table. When the asked for promotion is deleted, the adjustments with the list_line_id of the ask for promotion need to be deleted. In case the calling application asked for a specific modifier line, the adjustment with the list_line_id as the value_from of the asked for modifier line needs to be deleted.

Term Substitution

If a request line qualifies for a term substitution (TSN) modifier, the pricing engine inserts a line detail record in qp_ldets_v with created_from_list_line_type = QP_PREQ_GRP.G_TERMS_SUBSTITUTION.

The following three types of terms upgrade are supported by pricing:

If qp_ldets_v.substitution_attribute=QUALIFIER_ATTRIBUTE1, substitution_to holds payment_term_id.

If qp__ldets_v.substitution_attribute=QUALIFIER_ATTRIBUTE10,

substitution_to holds Freight Term Code.

qp_ldets_v.substitution_attribute=QUALIFIER_ATTRIBUTE11,

substitution_to holds Shipment Method.

If a request line qualifies for a TSN modifier, based on the substitution_attribute which is the term type, the corresponding term (for example, payment/shipment/freight term on the order/quote/request) needs to be replaced by the value in the qp_ldets_v.substitution_to. Remember to populate the record structure with the old term before any repricing calls so that the old term is returned in the event of an attribute mapping rule. Alternatively, make sure the old term is passed to the pricing engine if no attribute mapping rules exist and if the attributes are passed directly.

Also, if the pricing engine does not pass the TSN modifier on subsequent reprice calls because the request line no longer qualifies for it, make sure to replace the old term before deleting the TSN modifier from the transaction table.

Cross Order Volume Based Modifiers

Oracle Advanced Pricing provides seeded cross-order based volume modifiers that can be set up with pricing or qualifier attributes; for example, pricing attributes such as Period1 Item Quantity and Period1 Item Amount (pricing attributes). These attributes are operating unit specific and are seeded under the Order Fulfillment PTE. The cross order volume amount (such as Period 1 Item Amount) is calculated using list price and not selling price. Therefore, the calculated amounts would differ from order totals displayed in Order Management, which is based on selling price.

To find the cross order volume total for a given operating unit, you need to run the concurrent program Cross Order Volume Loader. This program reviews the Order Management (OM) tables for the order information, and populates the OM tables OE_ITEM_CUST_VOLS_ALL and OE_ITEM_CUST_VOLS_ALL. For more information, see Oracle Advanced Pricing User's Guide, Reports and Concurrent Programs.

Note: Modifier Level: When setting up a modifier with cross order volume attributes, select the Line modifier level rather than the Group of Lines modifier level. This is recommended because the cross order volume attribute value is 1) not based on the lines of the current order (it is based on previously-booked orders) and 2) the sum of lines across previous orders is already considered when using the cross order volume load attribute.

Recurring charges: Cross order volume loader excludes recurring charges in its calculations. Only order lines with charge periodicity of NULL are included in this calculation.

Also, the seeded attribute mapping rules get the cross order volume total for specific attributes from the OM cross order volume tables. The pricing engine qualifies the request lines for modifiers based on the cross-order volume attribute value the build_contexts API returns. If the calling application uses a new request_type_code or a different global structure, then the following steps are required to make the pricing engine process the cross order volume based discounts:

  1. Write a concurrent program to populate the cross order volume total into the above tables or new tables for each cross order volume attribute used in the modifier setup.

  2. Write attribute mapping rules to extract the cross order volume total for each cross order volume attribute used in modifier setup.

For more information about the cross order volume, see Oracle Advanced Pricing User's Guide, Cross Order Volume Load and Cross Order Volume Report.

Promotional Limits

Promotional limits are set up against modifiers. When a pricing engine call is made, the pricing engine qualifies the request line for suitable modifiers. If limits are set up against those modifiers, then the modifiers applied are consumed from this limit. The pricing engine calls the limits engine, which maintains the limits balance details. To use the limits functionality, the user needs to pass a price_request_code to uniquely identify each request line.

For example, the price_request_code may be built by concatenating the request_type_code||'-'||header_id||'-'||line_id. The limits consumption is updated in the pricing limits tables for each request line and the price_request_code is used as the key to identify the request line. After the pricing engine call, make sure to process any errors related to limits. In case errors occur due to limits, the pricing engine populates error status in qp_preq_lines_tmp.pricing_status_code for each request line. Make sure you handle the errors appropriately. In case the pricing engine call is to price an order, if the pricing engine throws a error status on one of the lines due to limits, then the order or that specific line may be placed on hold, or a warning message may be raised depending upon the business requirement.

Limit Consumption

The limit consumption does not evaluate the manual modifiers or overridden automatic/manual modifiers and the out-of-phase modifiers. The out-of-phase modifiers are those applied in previous pricing events, which are in pricing phases that do not belong to the current pricing event. For example, an order line was entered and saved and the price was $80 for that order line. A limit is defined for $20 on a bucketed new price modifier that, for example, consumes only $10. The limits API will apply the newprice modifier with an adjustment amount so that it can consume only $10 limit. If a modifier has been overridden in the previous bucket in such a way that the limit can consume the entire $20, the limits engine will still not look at the overridden modifier and will allow only $10 to be consumed. This issue is specific to limits defined on bucketed modifiers where there are out-of-phase or manual or overridden modifiers exist in the previous buckets.

The limit balances need to be updated when an order line is returned, amended or cancelled. If the calling application makes a pricing engine call, the pricing engine does this against the price_request_code passed. If no pricing engine call is made, pricing provides an API to do this. Ensure that you call the following API to update the limits consumption details:

QP_UTIL_PUB.Reverse_Limits (p_action_code             IN  VARCHAR2,
     p_cons_price_request_code IN  VARCHAR2,
     p_orig_ordered_qty        IN  NUMBER   DEFAULT NULL,
     p_amended_qty             IN  NUMBER   DEFAULT NULL,
     p_ret_price_request_code  IN  VARCHAR2 DEFAULT NULL,
     p_returned_qty            IN  NUMBER   DEFAULT NULL,
     x_return_status           OUT VARCHAR2,
     x_return_message          OUT VARCHAR2);

For more information about this API, see: Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide.

Multi-Currency

In multi-currency functionality, the price list passed must exist in the currency passed and vice versa. In case the currency code passed is not a part of the multi-currency price list, then the pricing engine will not be able to find a price. In order to ensure this, pricing provides an API to validate the passed in currency and price list. This validation needs to be done in the integration code. The API looks like:

QP_UTIL_PUB.Validate_Price_list_Curr_code
(
     l_price_list_id             IN NUMBER
     l_currency_code             IN VARCHAR2
     l_pricing_effective_date    IN DATE
     l_validate_result          OUT VARCHAR2
);

For more information, see Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide. The pricing engine will look for multi currency price lists if the profile QP: Multi-Currency Installed is set to Y to derive a price.

Also, pricing provides an API to return the rounded selling price or adjustment amount and uses the rounding factor based on the multi-currency price list and the order currency. This API can be used if there is a requirement to round the price apart from the rounding that the pricing engine does. Call the API,

QP_UTIL_PUB.Round_price(p_operand        => null
     ,p_rounding_factor         => null
     ,p_use_multi_currency      => p_use_multi_currency
     ,p_price_list_id           => p_price_list_id
     ,p_currency_code           => p_currency_code
     ,p_pricing_effective_date  => p_pricing_effective_date
     ,x_rounded_operand         => l_rounding_factor
     ,x_status_code             => l_status_code
     ,p_operand_type            => 'R'
);

If the price list or the currency changes on the order or quote that was priced earlier, remember to reprice the request because the pricing engine might return a different price based on the different currency.

Sample scripts are available to make pricing engine calls. The sample scripts, which are found in $QP_TOP/patch/115/sql, are

Uptake Requirements for Multi-Currency functionality by other Oracle Applications

In Oracle Advanced Pricing, the multi-currency price lists feature allows you to attach multiple currencies to the same price list or agreement. This reduces the volume of data processed and saves significant maintenance work for users. Additionally, you can set up different ways of deriving the conversion rate such as GL daily rate, fixed rate, user entered, and function call.

The following steps are used by the calling applications to support installations using multi-currency price lists:

  1. Install Order Management Minipack-H or application release 11.5.8 or higher.

  2. Set the Site level profile option QP: Multi-Currency Installed to Yes.

  3. Run the concurrent program: Update Price Lists with Multi-Currency Conversion Criteria. After running the concurrent program, all price lists and agreement price lists are converted to multi-currency price lists.

  4. If a user has converted to multi-currency price lists, applications calling the pricing engine must pass the control record variable use_multi_currency as Yes (Y) in the pricing engine call for the currency conversion to occur. This variable is the deciding factor for the calling application to start using the Multi-currency functionality.

  5. The calling application LOV (list of values) for the price list name at header and lines must be modified to show the multi-currency price lists and currencies.

    • When the user first enters the order currency and clicks the price list, the list of values displays only those price lists whose Currency Conversion's Currency-To is the same as order (transaction) currency. Also, the pricing effective date (if entered) on the sales order must be within the Currency-To effective dates. This is applicable for both the header and line level list of values. Pricing provides a view QP_PRICELISTS_LOV_V for calling applications to display the list of values for price lists for the given transaction.

    • When the user first enters the price list and clicks the Currency, the list of values displays all the Currency-To in its Currency Conversion. Also, the pricing effective date (if entered) on the sales order (transaction) must be within the Currency-To effective dates. This is applicable for both the header and line level list of values. The calling application to call an API provided by pricing is called QP_UTIL_PUB.Get_Currency.

    • The Process Order API currently validates the currency and price list passed to it. Now, the currency will have to be one of the Currency-To of the currency conversion criteria attached to this price list. The calling application needs to call the pricing API called QP_UTIL_PUB.Validate_Price_List_Curr_Code.

  6. Uptake new rounding API for price list. For re-price processing, the calling application needs to call QP_UTIL_PUB.round_price for price list rounding during re-price processing. This will use the Round To value to round the price.

  7. For Conversion Type of Transaction, the calling application integration needs to pass the conversion rate and conversion type entered in the Sales Order header (if any) to the pricing engine.

  8. The calling application integration needs to pass the functional currency to the pricing engine control record variable - function_currency.

Sample Code using Order Management Structure

procedure copy_Line_to_request(

p_Line_rec OE_Order_PUB.Line_Rec_Type
,px_req_line_tbl in out nocopy QP_PREQ_GRP.LINE_TBL_TYPE
,p_pricing_event varchar2
,p_Request_Type_Code varchar2
)
is
l_line_index pls_integer := nvl(px_req_line_tbl.count,0);
l_uom_rate NUMBER;
v_discounting_privilege VARCHAR2(30);
begin
l_line_index := l_line_index+1;
px_req_line_tbl(l_line_index).Line_id := p_Line_rec.line_id;
px_req_line_tbl(l_line_index).REQUEST_TYPE_CODE :=
p_Request_Type_Code;
px_req_line_tbl(l_line_index).LINE_INDEX := l_line_index;
px_req_line_tbl(l_line_index).LINE_TYPE_CODE := 'LINE';
If p_Line_rec.pricing_date is null or
p_Line_rec.pricing_date = fnd_api.g_miss_date then
px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE :=
trunc(sysdate);
Else
px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE :=
p_Line_rec.pricing_date;
End If;
px_req_line_tbl(l_line_index).LINE_QUANTITY :=
p_Line_rec.Ordered_quantity ;
px_req_line_tbl(l_line_index).LINE_UOM_CODE :=
p_Line_rec.Order_quantity_uom;
px_req_line_tbl(l_line_index).CURRENCY_CODE :=

OE_Order_PUB.g_hdr.transactional_curr_code;

If (p_Line_rec.service_period = p_Line_rec.Order_quantity_uom) Then
px_req_line_tbl(l_line_index).UOM_QUANTITY :=
p_Line_rec.service_duration;
Else
INV_CONVERT.INV_UM_CONVERSION(From_Unit =>
p_Line_rec.service_period
,To_Unit => p_Line_rec.Order_quantity_uom
,Item_ID => p_Line_rec.Inventory_item_id
,Uom_Rate => l_Uom_rate);
px_req_line_tbl(l_line_index).UOM_QUANTITY :=
p_Line_rec.service_duration * l_uom_rate;
End If;
px_req_line_tbl(l_line_index).Active_date_first_type := 'ORD';
px_req_line_tbl(l_line_index).Active_date_first :=
OE_Order_Pub.G_HDR.Ordered_date;
If p_Line_rec.schedule_ship_date is not null then
px_req_line_tbl(l_line_index).Active_date_Second_type := 'SHIP';
px_req_line_tbl(l_line_index).Active_date_Second :=
p_Line_rec.schedule_ship_date;
End If;
px_req_line_tbl(l_line_index).PRICE_FLAG :=
nvl(p_Line_rec.calculate_Price_flag,'Y');
end copy_Line_to_request;

procedure copy_attributes_to_Request

p_line_index number
,p_pricing_contexts_Tbl
QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
,p_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
,px_Req_line_attr_tbl in out nocopy
QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
,px_Req_qual_tbl in out nocopy
QP_PREQ_GRP.QUAL_TBL_TYPE
)
is
i pls_integer := 0;
l_attr_index pls_integer := nvl(px_Req_line_attr_tbl.last,0);
l_qual_index pls_integer := nvl(px_Req_qual_tbl.last,0);
begin
i := p_pricing_contexts_Tbl.First;
While i is not null loop
l_attr_index := l_attr_index +1;
px_Req_line_attr_tbl(l_attr_index).VALIDATED_FLAG := 'N';
px_Req_line_attr_tbl(l_attr_index).line_index := p_line_index;
-- Product and Pricing Contexts go into pricing contexts...
px_Req_line_attr_tbl(l_attr_index).PRICING_CONTEXT
:=
p_pricing_contexts_Tbl(i).context_name;
px_Req_line_attr_tbl(l_attr_index).PRICING_ATTRIBUTE :=
p_pricing_contexts_Tbl(i).Attribute_Name;
px_Req_line_attr_tbl(l_attr_index).PRICING_ATTR_VALUE_FROM :=
p_pricing_contexts_Tbl(i).attribute_value;
i := p_pricing_contexts_Tbl.Next(i);
end loop;
-- Copy the qualifiers
i := p_qualifier_contexts_Tbl.First;
While i is not null loop
l_qual_index := l_qual_index +1;
If p_qualifier_contexts_Tbl(i).context_name ='MODLIST' and
p_qualifier_contexts_Tbl(i).Attribute_Name
='QUALIFIER_ATTRIBUTE4' then
If OE_Order_PUB.G_Line.agreement_id is not null and
OE_Order_PUB.G_Line.agreement_id <>
fnd_api.g_miss_num then
px_Req_Qual_Tbl(l_qual_index).Validated_Flag := 'Y';
px_Req_Qual_Tbl(l_qual_index).Validated_Flag
:= 'N';
End If;
Else
px_Req_Qual_Tbl(l_qual_index).Validated_Flag := 'N';
End If;
px_Req_qual_tbl(l_qual_index).line_index := p_line_index;
px_Req_qual_tbl(l_qual_index).QUALIFIER_CONTEXT :=
p_qualifier_contexts_Tbl(i).context_name;
px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTRIBUTE :=
p_qualifier_contexts_Tbl(i).Attribute_Name;
px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTR_VALUE_FROM :=
p_qualifier_contexts_Tbl(i).attribute_value;
i := p_qualifier_contexts_Tbl.Next(i);
end loop;
end copy_attributes_to_Request;
procedure copy_Header_to_request(
p_header_rec OE_Order_PUB.Header_Rec_Type
,px_req_line_tbl in out nocopy QP_PREQ_GRP.LINE_TBL_TYPE
--,p_pricing_event varchar2
,p_Request_Type_Code varchar2
,p_calculate_price_flag varchar2
)
is
l_line_index pls_integer := px_req_line_tbl.count;
begin
l_line_index := l_line_index+1;
px_req_line_tbl(l_line_index).REQUEST_TYPE_CODE
:=p_Request_Type_Code;
--px_req_line_tbl(l_line_index).PRICING_EVENT :=p_pricing_event;
--px_req_line_tbl(l_line_index).LIST_LINE_LEVEL_CODE
:=p_Request_Type_Code;
px_req_line_tbl(l_line_index).LINE_INDEX := l_line_index;
px_req_line_tbl(l_line_index).LINE_TYPE_CODE := 'ORDER';
-- Hold the header_id in line_id for 'HEADER' Records
px_req_line_tbl(l_line_index).line_id := p_Header_rec.header_id;
if p_header_rec.pricing_date is null or
p_header_rec.pricing_date = fnd_api.g_miss_date then
ptrunc(sysdate);
x_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE :=
Else
px_req_line_tbl(l_line_index).PRICING_EFFECTIVE_DATE :=
p_header_rec.pricing_date;
End If;
px_req_line_tbl(l_line_index).CURRENCY_CODE :=
p_Header_rec.transactional_curr_code;
px_req_line_tbl(l_line_index).PRICE_FLAG := p_calculate_price_flag;
px_req_line_tbl(l_line_index).Active_date_first_type := 'ORD';
px_req_line_tbl(l_line_index).Active_date_first :=
p_Header_rec.Ordered_date;
end copy_Header_to_request;

Refer to the sample script qp_direct_insert.sql in $QP_TOP/patch/115/sql to call the insert APIs of the pricing engine.

Changed Lines API

This API permits only the modified and related lines to be passed to the pricing engine. This reduces unnecessary pricing processing and improves performance.

In typical pricing, there are multiple order lines or quote lines per order or quote. Calling applications such as Order Management and Order Capture that use Oracle Advanced Pricing for pricing their transactions, make pricing engine calls with one or more order lines in a single pricing request. Instead of passing all the order lines in every order, it is efficient to just pass the modified lines (newly entered lines or existing lines with update) to the pricing engine so that the pricing engine will not have to process the unnecessary lines that will result in the same prices anywhere.

The calling applications make calls to QP_UTIL_PUB.Get order line status to determine what to pass. The output record of this API has three flags:

All_Lines_Flag (Yes or No)

This flag tells the calling applications whether all lines should be passed to the engine. The value of this flag is determined by the pricing setup. For these modifiers, all lines information is required in order for the pricing engine to evaluate the eligibility of discounts.

Changed_Lines_Flag (Yes or No)

When this flag is set to 'Y', only the modified lines should be passed to the engine. This flag is 'Y' when there are only Line Level Modifiers.

Summary_Line_Flag (Yes or No'):

The value of this flag depends on the modifier setup at the order level. For these modifiers, this flag will be set to Y (Yes) so that only the Order Summary Line is passed to the engine.

In addition to the Changed Lines API, the QP_ATTR_MAPPING_PUB.Build_contexts API can be used with the signature Build_Contexts:

(       p_request_type_code,               p_line_index,
     p_check_line_flag,
     p_pricing_event,
     p_pricing_type_code,
     p_org_id,
     x_pass_line)

If the changed lines API passed All_lines_flag as Y (Yes), you can use the build_contexts API for attribute mapping. For the actual lines that have changed, the p_check_line_flag may be passed as N (No) and for the remaining lines that did not change, the p_check_line_flag should be passed as Y (Yes). For the lines for which p_check_line_flag is passed as Y, this API will check if there are any active advanced line group or other item discount type modifier or promotional goods modifiers with additional buy products defined based on the product attributes sourced for that line.

If such modifiers exist, the Build contexts API will source all the product/pricing/qualifier attributes and return the x_pass_line as Y to indicate that the caller needs to pass this line to the pricing engine because it can affect the price on the remaining lines on the order/quote although the line did not undergo any changes. If such active modifiers are not found, the attributes are not sourced for the line and the x_pass_line is returned as No so that the caller need not pass this line to the pricing engine to avoid performance overhead.

Scenarios

Case 1

One hundred-line order with five changed lines. Setup: All_Lines_Flag = 'N', Changed_Lines_Flag = 'Y', Summary_Line_Flag = 'N' Result: Only five lines are passed to the pricing engine.

Case 2

One hundred-line order with all lines changed.

Setup: All_Lines_Flag = 'Y', Changed_Lines_Flag = 'Y/N', Summary_Line_Flag = 'N'

Result: All 100 lines are passed to the pricing engine.

Case 3

One hundred-line order with five changed lines and either Other Item, Promotional Goods, or Group of Lines discount.

Setup: All_Lines_Flag = 'Y', Changed_Lines_Flag = 'Y/N', Summary_Line_Flag = 'N'

Result: All lines are passed to the pricing engine.

Case 4

One hundred-line order with five changed lines and an order level modifier.

Setup: All_Lines_Flag = 'N', Changed_Lines_Flag = 'Y', Summary_Line_Flag = 'Y'

Result: Five lines and a summary line are passed to the pricing engine.

Case 5

One-hundred-line order with no changed lines.

Setup: All_Lines_Flag = 'N', Changed_Lines_Flag = 'N', Summary_Line_Flag = 'N'

Result: No lines are passed to the pricing engine.

Case 6

Two new lines are added to a one hundred-line order.

Setup: All_Lines_Flag = 'N', Changed_Lines_Flag = 'Y', Summary_Line_Flag = 'N'

Result: Only the two new lines are passed to the pricing engine.

Oracle Service Contracts (OKS) Integration: Proration and Price List Locking

This section describes the usage proration and price list locking features available when Oracle Service Contracts (OKS) is integrated with Oracle Advanced Pricing (QP). The OKS API is used for calculating service duration and determining start and end dates. Usage proration is available to any calling application making usage calls. Price list locking is available only for use by OKS. Price list locking refers to:

For example, if Oracle Service Contracts wants to lock the price list line with list_line_id = 201 on the Corporate price list, the following occurs:

The locked price list will be created with the name OKS LOCKED Corporate. The naming convention followed is:

<Source System Code> || 'LOCKED' || <Source Price List>.

The locked list line is copied from the price list line with list_line_id = 201 and will be created under the locked price list OKS LOCKED Corporate. The locked line with, for example, list_line_id = 301, will have a pricing attribute with the following setup:

Locking Price Lists and Price List Lines that are Already Locked

In previous releases, you had to create a locked price list and price list line from the user interface (UI). However, in the current release, an API is called programmatically from the OKS code to lock the price list and price list line without your intervention. You can now also lock an already locked price list and line.

For example, suppose you want to lock an already locked price list that is named: <Source System Code> ||'LOCKED'|| <Source Price List Name>. However, since the Source Price List name already has already been assigned a "locked" name, for example, <Source System Code> ||'LOCKED' prefix then a new name is automatically assigned to the locked price list using the following format <Source System Code> ||' LOCKED'|| <version no> || ' ' <Original Source Price List Name> where the version number starts from 2. For example, if the source price list is named Corporate, then the locked price list will be named QP LOCKED Corporate. However, if the source price list name is QP LOCKED Corporate, then the locked price list name will be QP LOCKED2 Corporate.

Price Line Locking

You can also lock an already locked price list line. Price line locking occurs when the price list line of the source price list is copied (now the locked price list line) as a child of the locked price list. For example, suppose Oracle Service Contracts (OKS) wants to lock the locked price list line with list_line_id = 201 on the OKS LOCKED Corporate price list. Since the list line is already locked, it will have a pricing attribute with context = 'QP Internal' , attribute = 'List Line Id' and value = 201.

Then the following steps occur: the new locked price list line will be a copy of the price list line with list_line_id = 201 and created under the locked price list OKS LOCKED2 Corporate price list. The new locked line with, for example, list_line_id = 301, will have a new pricing attribute with context = 'QP Internal', attribute = 'List Line Id' and value = 301 attached to it (in addition to the other pricing attributes copied over from the source list line with list_line_id 201).

However, the pricing attribute with context = 'QP Internal', attribute = 'List Line Id' and value = 201 that is attached to the source list line with list_line_id = 201 is not copied over from the source to the newly locked list line having list_line_id = 301

Note: The Locked price list is created only once for a given source price list and source system code. For subsequent lock requests, the new locked price list lines are created under the already existing locked price list.

Effective Dates for Copied Price List

No effective dates are copied to the locked price list and price list lines--instead the effective dates can be controlled by OKS using the effectivity dates on the associated Service Contract.

Integration Flow for Price List Locking

The following steps outline the process flow for price list locking:

  1. The application Oracle Service Contracts (OKS) makes an authoring call to the pricing engine with an authoring UOM (unit of measure) which can be an item uom such as Quarter, Month, Year.

  2. The pricing engine returns the price list line, price breaks (no price break calculation as quantity not passed).

  3. OKS displays the price list line/breaks (for example, locked breaks) to OKS users in the OKS user interface, enabling updates. Product should be same for the price list line as returned by the pricing engine in step 2.

  4. OKS launches the price list window to support creation of locked price list, locked price list line and its breaks. It passes the following related values to window parameters to create and query a new price list with name = 'OKS '|| 'LOCKED ' || <Source Price list name>:

    LOCK_MODE (='Y')

    SOURCE_PRICE_LIST_ID

    SOURCE_LIST_LINE_ID

    ORIG_SYS_HEADER_REF ( = Contract Number)

    STARTUP_MODE ( = OKS),

    STARTUP_MODE = OKS

    START_DATE_ACTIVE and END DATE ACTIVE columns set to null

  5. A pricing attribute is created for the locked PBH (price break header) line with the value equal to its list_line_id. This enables the pricing engine to select this specific line. The effective dates (START_DATE_ACTIVE and END_DATE_ACTIVE) on the locked PBH price list line are set to null.

  6. OKS stores the foreign key of the price list line id, and passes the price list id while calling pricing engine at the time of billing.

  7. For further updates to the locked price breaks, OKS will launch the price list form with appropriate parameters (LOCK_MODE= 'N', LOCKED_PRICE_LIST_ID, LOCKED_LIST_LINE_ID, STARTUP_MODE = 'OKS') to query the locked price list and locked list line specified.

  8. OKS calls the pricing engine at the time of billing and provides the UOM for proration. The assumption is that pricing engine returns the exact same price.

  9. The price list created by OKS is prevented from being updated from pricing window if LIST_SOURCE_CODE = 'OKS' and orig_system_header_ref is not null.

  10. A price list with locked price breaks can be deleted using Purge API. Only inactivated price lists should be purged. OKS will use the pricing public API to inactivate the price list.

  11. During billing call, OKS will pass contract id and price list as qualifiers. OKS will also pass price list line id as pricing attribute.

Example of Integration Flow for Price List Locking

The following steps outline the integration flow for price list locking when Oracle Service Contracts (OKS) is integrated with Oracle Advanced Pricing (QP).

  1. Define the contract header and select a price list name (example Corporate).

  2. Enter a contract line, then select an item.

    A pricing engine call is made with the price list at the contract header (for example, Corporate). The pricing engine returns the price break information, and displays the information in the Contracts window.

  3. To lock the price for the item, click the Lock button.

  4. At this point, OKS populates the following window parameters:

    • Startup_mode (OKS)

    • Lock_mode (Y/N)

    • Source_price_list_id (if Lock_mode='Y')

    • source_list_line_id (if Lock_mode='Y')

    • locked_price_list_id (if Lock_mode = 'N')

    • locked_list_line_id (if Lock_mode = 'N').

      Then it calls Price List setup window.

  5. The pricing application then completes the following actions:

    1. Checks if this call is from OKS (if Startup_Mode = 'OKS').

    2. If yes, then it checks if Lock_mode = Y. If no, then it goes to the Update step.

    3. If yes, then it checks if a record exists in qp_list_headers_b where the:

    4. Source_system_code = profile option value of QP: Source System Code

    5. Locked_from_list_header_id = Source_price_list_id

    6. If the record does not exist, that means a new price list needs to be created. Hence, it populates the record structure for the new price list. Ensure that the source system code is OKS and the Start Date Active and End Date Active columns are null.

    7. If the record exists, that means a new line needs to be created for the locked price list, and you need to populate the line structures.

    8. Copy the PBH line, its attributes and price break child lines. Ensure that the:

      • Start Date Active and End Date Active columns for the copied (locked) PBH line record are set to null

      • list_line_id of the newly created PBH line is used to add a pricing attribute to the PBH line. Post the records.

    9. Query the newly inserted/locked price list.

    10. In the pre-query of the list lines block, set the "where" clause to query the newly created/locked PBH line.

    11. Navigate to the List lines tab. Click the Price Breaks tab to update the price breaks.

    12. Set the global variables (set in the .pld files) to pass back the last created/modified price_list_id, list_line_id to OKS.

    13. In the post-query, clear the "where" clause to its original status.

    14. If the mode is Update then perform steps from step g).

  6. If you are querying an existing PBH line which is already locked, then OKS needs to make a pricing engine call with List_line_id as a pricing attribute. To do this, navigate to the Service Contracts Authoring window.

  7. To delete or unlock, OKS needs to directly call the Pricing API to delete the price list line.

  8. Oracle Advanced Pricing (QP) should change seed data to default QP: Source System Code profile to OKS when application is OKS. Also, add OKS to Order Fulfillment PTE.

  9. QP needs to seed List_Line_Id as a pricing attribute. Do not use the PRICING ATTRIBUTE' pricing context for this pricing attribute. Since a generic pricing context is needed to assign the above pricing attribute, a new pricing context called 'QP INTERNAL' will also be created/seeded.

    The prefix <Source System Code> || ' LOCKED ' || <Source Price List Name> should be the name of the locked price list; for example, 'OKS LOCKED Corporate'.

Changes Related to the Price List Locking feature

The following parameters are added to the Price List window. They are not visible to the end-user but are required for integration with the Price List Locking feature.

Parameter Datatype Meaning/Values
LOCK_MODE CHAR(1) Valid values are Y/N. Indicates if Lock Mode is Yes or No. Appropriate value to be passed by OKS while launching the price list window.
LOCKED_PRICE_LIST_ID NUMBER If Lock_Mode = N then list_header_id of a locked price list to be passed by OKS while launching price list window.
LOCKED_LIST_LINE_ID NUMBER If Lock_Mode = N then list_line_id of a locked price list line to be passed by OKS while launching price list window.
SOURCE_PRICE_LIST_ID NUMBER If Lock_Mode = Y then list_header_id of source price list to be passed by OKS while launching price list window.
SOURCE_LIST_LINE_ID NUMBER If Lock_Mode = Y then list_line_id of a source list line to be passed by OKS while launching price list window.

The following global variables pass information back to OKS:

Parameter Meaning/Values
LOCKED_PRICE_LIST_ID Has the LIST_HEADER_ID of the last locked price list created or modified in the price list window launched by OKS.
LOCKED_LIST_LINE_ID Has the LIST_LINE_ID of the last Locked list line created or modified in the price list window launched by OKS.

The following changes are also related to the price list locking feature:

Integration Flow for Proration

Oracle Service Contracts (OKS) supports prorated billing for Oracle Advanced Pricing customers. The following steps outline the changes required to the calling application and the behavior of the pricing engine:

  1. The calling application submits a billing call to the pricing engine with a Volume-Quantity (for example, 500) and (usage_break_UOM) such as MONTH. If the calling application does not pass usage_break_UOM attribute then the conversion is not needed and the engine proceeds with normal price break evaluation.

  2. The pricing engine evaluates the following:

    • PBH (price break header) line

    • Break UOM context (BREAK_UOM)

    • Break_UOM_attribute (USAGE_UOM)

      If Break UOM is not set up then the engine proceeds with normal break evaluation.

  3. Pricing calls the Contracts API for unit-of-measure conversion. The service_start_date and end_date from qp_preq_lines_temp as well as setup Break UOM and passed_Break_UOM is passed to the Contracts API.

  4. The API returns the conversion factor for proration. If the contracts profile is not set, then the standard UOM conversion results. For example, if the conversion = 1/3, then the Break From/To values will be converted as follows:

    Original From/To Modified From Modified To
    0 - 1000 0 333.333.....3
    1000 - 2000 333.333...3 666.666...6
    2000 - 3000 666.666...6 1000

    Note: The values shown in the table will not be truncated.

  5. The pricing engine evaluates the preceding Break From/To, and returns the setup Break UOM to the calling application.

  6. OKS needs to pass the following values to the pricing engine to calculate the proration: Context = BreakUOM, Attribute = Pricing_Attribute1, Value = Billing UOM.

Note: New continuous prorated price breaks that are billed based on fixed or actual usage may have a different monetary value than pre-R12 prorated price breaks. Prorated continuous price break tiers will no longer be “floored” in calculation (that is, set to previous whole number).

Changes Related to the Proration feature

The Price List window displays two additional columns for entering price breaks in the List Lines tab:

To display these columns, the profile option QP: Break UOM Proration Allowed, must be set to Yes. The valid values are Yes or No. This can be set at both the Site and Application levels.

Duration and Partial Period Pricing of Service Items

Duration defines the time period for a particular service. Partial period pricing is required to change the price when the contract duration changes. For example, your customer, ABC Applications Software (currently, on a 2 year service program), wants to update their service contract by adding another 10 months to their Extended Notebook PC Service Program.

You can use Oracle Advanced Pricing with Oracle Service Contracts to calculate duration and partial period pricing. With Service Contracts, the service duration of a contract is calculated from the contract start date/end date by calling the Service Contracts conversion routines (with/without UOM Conversion) to derive line quantity.

Note: The inventory conversion routine is used if dates are not passed.

To ensure the consistency of partial period pricing across all calling applications, Oracle Advanced Pricing calculates partial period pricing for a services line depending on whether the uom quantity or contract start date is passed to the pricing engine:

Examples of Usage Proration with Oracle Service Contracts (OKS)

The following are some examples of usage proration with Oracle Service Contracts (OKS).

Example 1

Profile QP: Break UOM Proration Allowed = Y

Set up a price break price list line for an item, for example, AS999:

Call pricing engine for the same item AS999 with quantity 500, usage_pricing_type = REGULAR passing the Price List as qualifier and also with pricing context = BREAK_UOM, attribute = PRICING_ATTRIBUTE1 and value = MTH (Month)

Expected Results:

The unit_price returned from pricing engine should be 90 (for line quantity 500) based on following usage proration for price break child lines:

Example 2

Profile QP: Break UOM Proration Allowed = Y

Set up a price break price list line for an item, for example, AS999:

Call pricing engine for the same item AS999 with quantity 910, usage_pricing_type = REGULAR passing the Price List as qualifier and also with pricing context = BREAK_UOM, attribute = PRICING_ATTRIBUTE1 and value = MTH (Month)

Expected Results:

The unit_price returned from the pricing engine should be 90.98 (for line quantity 910) based on following usage proration for price break child lines:

Example 3

Profile QP: Break UOM Proration Allowed = Y

Set up a price break price list line for an item, for example, AS999:

Call the pricing engine for the same item AS999 with quantity 500, usage_pricing_type <> REGULAR passing the price list as qualifier and also with the following:

Expected Results:

The unit_price returned from pricing engine should be 100 (for line quantity 500) with no proration as usage_pricing_type passed is not REGULAR.

Example 4

Profile QP: Break UOM Proration Allowed = Y

Set up a price break price list line for an item, for example, AS999:

Call the pricing engine for the same item AS999 with quantity 500, usage_pricing_type = REGULAR passing the Price List as qualifier but without passing the following pricing attributes for the line:

Expected Results:

The unit_price returned from pricing engine should be 100 (for line quantity 500) with no proration as Break UOM attribute is not passed to the engine.

Example 5

Profile QP: Break UOM Proration Allowed = Y

Set up a price break price list line for an item, for example, AS999:

Call the pricing engine for the same item AS999 with quantity 500, usage_pricing_type = REGULAR, passing the price list as qualifier and the following:

Expected Results:

The unit_price returned from pricing engine should be 100 (for line quantity 500) with no proration as Break UOM is null for the price break header line.

Pricing Features to Support Telecommunications Industry Flows [Oracle Telecommunications Service Ordering (TSO)]

Some services, such as wireless phone services, charge their customers a fixed recurring service charge for certain services; for example, a monthly charge for a Local or Long Distance Calling Plan or other services such as Caller ID or Call Waiting. The billing frequency of these charges, such as every month or quarter, is the charge periodicity. Service providers may choose to offer customers the same service in different bundles (plans) with each plan having different charge periodicities. For example, a wireless phone company may offer the same phone service with either a monthly price or a quarterly price.

These recurring charges are typically set up when the customer signs up for the service. When a customer orders an item with a recurring charge, the periodicity such as a month or year must be specified on the order, quote, or "shopping cart" line to get the correct price for the service.

Note: Order Amount qualifier value and Cross Order Volume computations aggregate only one-time charge lines; recurring charge lines are excluded.

Charge Periodicity attribute: Select this seeded context/attribute when setting up charge periodicities for recurring charges for either a price list line or modifier line.

Note: Charge Periodicity values are not seeded in pricing. The list of values for Charge Periodicity pricing attribute is based on the unit of measure (UOM) class registered in the profile OM: Charge Periodicity UOM Class. This UOM class can have units of measure such as MONTH, QUARTER, and YEAR.

Related Topics

For more information on setting up recurring charges for telecommunications services, see the Oracle Telecommunications Service Ordering Process Guide.