Pricing Qualifiers – How They Work and Creating Your Own

July 21st, 2008 |   Send this article to a friend!

Oracle Advanced Pricing allows you to create discounts, surcharges, and other promotions (called Modifiers) that can be applied against sales orders both manually and automatically. Often there are requirements for Modifiers to only apply in certain situations. For example, promotions on specific products or product lines, discounts on customer types, additional charges on international shipments, etc. So in addition to creating Modifiers, Qualifiers can be used to reference specific attributes of a sales order, product, customer, etc. to determine if the Modifier qualifies for the sales transaction.

Oracle provides a wide array of Qualifiers to evaluate a sales transaction, but situations often arise where Qualifiers are needed for attributes that aren’t available out of the box. However, with a few configurations and little bit of PL/SQL knowledge, you can create your own Qualifier attributes.

But before we begin, it helps to look at some commonly used Qualifiers to see how they’re configured, and ultimately how Oracle is able to source the information. Let’s start by going to the Oracle Pricing Manager responsibility, navigate to Setup > Attribute Management > Contexts and Attributes. From this form, we’ll be able to see the starting point from where the Qualifier attributes are created.

Click the Search (flashlight) icon and select context type of Qualifier Context and code of Order. After you make the selection, you’ll see the seeded qualifying attributes listed under the Attributes block of the form.

As an example, let’s look at the Order Type attribute, which represents the Order Type field of the sales order header. Here you’ll see the basic information that is associated with this Qualifier, most notably the Column Mapped, which indicates where in the Qualifier attribute structure to store the referenced value, and the Value Set, which is used to validate this value. The Value Set is important because when assigning this Qualifier to a Modifier, this is the Value Set that will be used to validate the input accepted when comparing this Qualifier to a value. In this case, when we reference the Order Type qualifier, the “QP_ORDER_TYPES_ALL” value set will only compare this qualifier to valid order types; not line types, random text, numeric inputs, etc.

To see how this attribute is mapped to the actual Order Type field on the sales order, we need to return back to the menu and navigate to Setup > Attribute Management > Attribute Linking and Mapping. Enter Order Fulfillment for Pricing Transaction Entity and Qualifier Context for Context Type. You’ll notice that the same listing of contexts is displayed under the Contexts block.

Select the ORDER code record and click the Link Attributes button. A form will display a listing of all the same attributes we saw from the Contexts and Attributes form, but with information as to where the Attributes are sourced from.

Looking at the Order Type attribute, you’ll see that Level for this attribute is BOTH. This indicates that the Order Type can be referenced from the header or the line details of the corresponding transaction. Other options for Level include LINE, which indicates that the source is at the line detail level, and ORDER, which indicates the attribute can be sourced at header level of the transaction.

Next to the Level is the Attribute Mapping Method, which indicates the method in which the Attribute is sourced. In this case, the method for Order Type is ATTRIBUTE MAPPING, which indicates a more technical reference is required to source this value (i.e. PL/SQL, system variable, etc.). Other Attribute Mapping Methods include RUNTIME SOURCE, indicating that the attribute will be populated by the QP_CUSTOM package, and USER ENTERED, indicating that the value will be populated by the user (this is more common for Pricing Attributes rather than Qualifier Attributes).

To see the kind of Attribute Mapping reference that exists for Order Type, click on the Attribute Mapping button. What you’ll see is a listing of mapping setups for the Order Type attribute broken down by application. Oracle accepts an attribute mapping by application because from a data structure standpoint, the reference to Order Type can be different in Order Management then other modules like Telesales, Service Contracts, etc.

To view the source mapping for Order Management, click on the record indicating ONT for request type and take note of the settings for Header Level and Line Level Attribute Mappings on the bottom half of the form. Here’ you’ll see the actual PL/SQL references that are being made to retrieve the Order Type. Note the Seeded/User Source Type and Seeded/User Value String settings for each:

Reference Type:    PL/SQL API
User Value:        OE_ORDER_PUB.G_HDR.order_type_id

This shows us that this attribute is sourced via a PL/SQL API and calls the PL/SQL string OE_ORDER_PUB.G_HDR.order_type_id. The G_HDR record structure is seeded by Oracle and contains data for the sales order header transaction that is being actively processed and evaluated. It is accessible globally from where the Pricing Engine is executing the PL/SQL code contained in the User Value field. So by accessing this global record structure, Oracle was able to map the Order Type transaction ID to the Order Type attribute – making this information available to Qualifier and Modifier setups in Advanced Pricing.

Other more advanced Qualifier examples exist which show direct calls to seeded PL/SQL functions. Starting again from the Setup > Attribute Management > Attribute Linking and Mapping navigation path, check out the Party ID located under the CUSTOMER context and take a look at the User Value String under the same Link Attributes form we were just in.

Since the customer party ID cannot be directly sourced from sales order transaction, Oracle had to develop a quick function call that references the SOLD_TO_ORG_ID of the sales order to retrieve the Party ID from the HZ table structure (QP_SOURCING_API_PUB. GET_PARTY_ID). Notice that since customer information is stored on both the header and the lines of a sales order transaction that Oracle utilizes the G_HDR and G_LINE global record structures to pass the sold to customer information to the seeded API.

QP_SOURCING_API_PUB.GET_PARTY_ID(OE_ORDER_PUB.G_HDR.sold_to_org_id)

QP_SOURCING_API_PUB.GET_PARTY_ID(OE_ORDER_PUB.G_LINE.sold_to_org_id)

Opening up a TOAD session and running the describe command on the QP_SOURCING_API_PUB package shows the details of this function in addition to other functions developed by Oracle to retrieve other data mapping sources used by other seeded Qualifiers.

Given what we’ve seen so far, the configuration aspect of a Qualifier is quite simple, but it takes just a bit of PL/SQL knowledge to actually map your Qualifier to a source. You don’t quite need to be an expert per say, but have just enough technical know-how to be “dangerous”.

Now that we have a decent idea of the inner workings of a Qualifer, let’s walk through the steps again and try to create our own. Say for example that a business requirement has come up which calls for a discount to be applied based on a value stored in a Descriptive Flexfield segment (we’ll pretend it’s ATTRIBUTE10 of the sales order header).

We first begin by navigating to Setup > Attribute Management > Contexts and Attributes to choose our Context and create our core Attribute information. Since this Attribute will be mapped to a value stored on the sales order header, it’s most appropriate to place our attribute under the Order context.

Next, let’s add a record under the Attributes block and provide our Attribute with a Code, Name, and Description.

After identifying our Attribute, we’ll need to choose our Value Set. Let’s assume that this is a Yes/No field that will be validated against the “QP: Yes/No” value set. We’ll also need to assign an available column. I’d recommend selecting a column that is a distance away from the last column that is being used to avoid any conflicts (in case Oracle decides to introduce new qualifying attributes in the future).

Now let’s navigate to Setup > Attribute Management > Attribute Linking and Mapping to map our newly created Attribute to a source. Let’s again select Order Fulfillment for Pricing Transaction Entity and Qualifier Context for Context Type, choose ORDER as the Context, and click the Link Attributes button.

Once the Link Attributes form is open, we’ll add a new record to map our Attribute. By selecting the list of values for the Code, our new attribute should appear in the list. If our attribute is the only attribute under the Order context that is not mapped, it should default to our Attribute automatically.

Assuming that we’ll want this Qualifier accessible from the header and line details of a sales order transaction, we’ll select BOTH as our Level, followed by Attribute Mapping as our Attribute Mapping Method.

Save your changes, and click on the Attribute Mapping button. Since our Qualifier is referencing the Flexfield segment within the Order Management module, let’s again select the ONT reference record, but this time populate “Advanced Pricing” for the Application Name.

Moving along to the Header and Line Level setups, we want to indicate that our User Source Type will be a PL/SQL API for both, followed by the appropriate PL/SQL string in the User Value String field.

You might be asking, “How do we determine what PL/SQL string to use?”

If we refer back to the Order Type attribute example, the G_HDR record structure is the global structure that is populated with the current sales order header transaction data. If you’re familiar with the OE_ORDER_HEADERS_ALL table, you’ll know that all we need to do is reference G_HDR.ATTRIBUTE10 to retrieve our Flexfield segment. Since we’re referring to the Flexfield segment stored on the order header, the User Value String values for both the Header and Line Levels will be the same.

OE_ORDER_PUB.G_HDR.attribute10

This example was fairly straight forward since the Flexfield segments are easily identifiable and stored directly in the G_HDR record structure. However, if in other situations you’re unsure of which column actually stores the data you wish to reference, you may have to query data against the OE_ORDER_HEADERS_ALL or OE_ORDER_LINES_ALL tables to determine where your data is stored. And in cases where the value isn’t found in these tables, then you may have to develop a custom API similar to how the Party ID Qualifier is implemented (I’ll post an example of how to do this in a future article).

Now that we’re finished with our setups, we can save our changes.

After saving your work, let’s navigate to the Tools file menu and select Build Attribute Mapping rules. This will compile our Attribute Mapping setups with our PL/SQL reference in place. If any typos or incorrect syntax is made in the User Value, you’ll find out from the results of the compilation.

Assuming that you’ve enter the PL/SQL string correctly, you should receive a message indicating the Attribute Mapping Rules have been built successfully.

Upon clicking OK closing the Attribute Mapping form you may receive the following warning message:

Oracle is screaming at us because we chose to only map our attribute for the Order Management application and not the other applications that are listed under the Application Types. For the purposes of this demonstration, we can click OK and be done with hit. However, if you’re organization is utilizing Advanced Pricing from elsewhere (Service Contracts, Telesales, etc.), then I would recommend that you also provide source mappings for those dependent applications as well.

Sweet! Now you have a Qualifier that references a sales order header Flexfield attribute! You should now be able to view this Qualifier when setting up List or Line Qualifiers from the Modifier setup form as shown below.

Now even though we’ve built and compiled our attribute mapping, Oracle may still warn you that the Qualifier you’ve reference hasn’t been mapped. If this is the case, you can run the Build Attribute Mapping Rules concurrent request and this should take care of the issue.

As I mentioned, this Qualifier serves as a good example for when the data is readily accessible via the G_HDR or G_LINE global record structures, but stay tuned for another example which will show how we can create our own custom PL/SQL function to source an Attribute.

---

Mr. Thompson is a Senior Oracle Applications Consultant with Lexerd Group Consulting. Visit www.LexerdGroup.com to find out more about our firm.

Interested in hosting your own blog? Lexerd Group’s Internet Services division provides specialized blog hosting services that utilizes the Wordpress blogging platform. Visit http://www.lexerdgroup.com/blog-yourself/ for more details.

Entry Filed under: Order Management, Technical, Advanced Pricing