Pricing Qualifiers – How They Work and Creating Your Own Part II

September 12th, 2008

In my last Qualifier article, I delved into the inner workings of Qualifiers in Advanced Pricing by walking through a quick demo of how to setup a Qualifier to reference Descriptive Flexfield data on a sales order transaction. In this article, I’d like to touch on how some Qualifiers require more advanced PL/SQL techniques to retrieve transactional data. For example, the “Customer Party ID” is tied to QP_SOURCING_API_PUB.GET_PARTY_ID function call because the Party ID isn’t stored directly in the G_HDR record structure. With that said, lets walk-thru the creation of a Qualifier that requires us to create our own PL/SQL function reference.

Let’s say that we have a requirement to qualify Modifier discounts against specific customer account numbers. Oracle provides us with a Qualifier for a customer’s Party ID, but does not provide a Qualifier for account number – we’ll need our own custom API to accomplish this.

Before we start, let’s not forget that a sales order could have multiple customer’s associated with the transaction. Remember that we can have up to three customers on a sales order that represent the selling, billing, and shipping organizations (there are even more when factoring in deliver to and installed at, but for this example let’s focus on the main three). The business requirements behind the discount could determine that we only need one of the three, but let’s assume that discounts could be applied to any of the selling, billing, and shipping customers. Hence, we’ll need three Qualifiers to represent each.

Let’s start 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 customer master record, it’s most appropriate to place our attribute under the Customer context.

Next, let’s add three Attribute records under the Attributes block with the appropriate Codes, Names, and Descriptions that will represent our sold to, bill to, and ship to customer account numbers. I’ve also decided to use a seeded Customer Account Value Set to validate our attributes against a valid list of customer account numbers.

Lastly, we’ll need to assign an available column. As in my previous example, 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 Customer Account attributes. Select Order Fulfillment for Pricing Transaction Entity and Qualifier Context for Context Type, choose Customer as the Context, and click the Link Attributes button.

Once the Link Attributes form is open, we’ll add three new records for our Attributes. Since customer information is stored on the header AND line detail of a sales order transaction, our Level will be Both, followed by Attribute Mapping as our Attribute Mapping Method since we’re going to be referencing a PL/SQL function to retrieve our customer account number values.

After saving your changes, we’ll want to begin mapping all three attributes. Let’s start with the Customer Sold To Account Number by clicking on the corresponding record and clicking the Attribute Mapping button. Once the Attribute Mapping form appears, select the ONT reference record for Order Management.

Since we indicate Both as our reference level, we’re required to provide mapping references for both the Header and Line Source setups. At this point we know that our Source Type will be a PL/SQL API for each, but now here comes the hard point – exactly what PL/SQL will enable us to retrieve the Customer Sold To Account Number?

Going back to our Customer Party ID example, we see that this Qualifier is mapped to an Oracle seeded function called QP_SOURCING_API_PUB.GET_PARTY_ID.

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)

We’ll need to create our own similar function in order to retrieve the sold to customer account. At a table level, since the order header (OE_SALES_ORDER_LINES_ALL) and order lines (OE_SALES_ORDER_LINES_ALL) contain direct references to the sold to customer (SOLD_TO_ORG_ID), and since the customer account number is stored directly in the customer master view (HZ_CUST_ACCOUNTS), we can build an almost identical query compared to what the seeded Customer Party ID function.

So given the SOLD_TO_ORG_ID of the sales order transaction, we can create a function that accepts SOLD_TO_ORG_ID as a parameter and retrieves the customer account number. We’ll need to build our own package which includes this function, so I created a package called XQP_SOURCING_API_PUB. This package will contain all of our custom functions needed to retrieve custom Qualifier values:

             

CREATE OR REPLACE PACKAGE BODY APPS.XQP_SOURCING_API_PUB AS

FUNCTION GET_SOLD_TO_ACCOUNT_NUMBER(p_sold_to_org_id IN NUMBER)
RETURN varchar2
IS

l_sold_to_account_number varchar2(30);

CURSOR get_sold_to_account_number_cur (l_sold_to_org_id NUMBER)
IS
SELECT account_number
FROM hz_cust_accounts
WHERE cust_account_id = l_sold_to_org_id;

BEGIN

OPEN get_sold_to_account_number_cur (p_sold_to_org_id);
FETCH get_sold_to_account_number_cur INTO l_sold_to_account_number;
CLOSE get_sold_to_account_number_cur;
RETURN l_sold_to_account_number;

EXCEPTION
WHEN OTHERS THEN
RETURN NULL;

END GET_SOLD_TO_ACCOUNT_NUMBER;

END XQP_SOURCING_API_PUB;

Let’s compile and confirm that our PL/SQL runs successfully. We need to also be sure to include the package specification before compiling the above source.

Now that we have our function, we need to complete our Attribute Mapping setup by including in the User Value field the PL/SQL reference to this function both in the Header and Line setups. Our function accepts the SOLD_TO_ORG_ID as a parameter, so by using the G_HDR and G_LINE record structures, we can pass this value as our parameter.

XQP_SOURCING_API_PUB.GET_SOLD_TO_ACCOUNT_NUMBER(OE_ORDER_PUB.G_HDR.sold_to_org_id)

XQP_SOURCING_API_PUB.GET_SOLD_TO_ACCOUNT_NUMBER(OE_ORDER_PUB.G_LINE.sold_to_org_id)

Finished! Let’s go to the Tools file menu and select “Build Attribute Mapping Rules” to confirm our setups are correct.

We’ve successfully built our Sold To Customer Account Qualifier, but we still need separate PL/SQL functions for the customer bill to and ship to account Qualifiers. On the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL tables we have a reference to the BILL_TO_ORG_ID and INVOICE_TO_ORG_ID columns, but these ID’s refer to the site usage records in HZ_CUST_SITE_USES. With that said, our queries for these functions will be slightly different than the sold to function.

FUNCTION GET_SHIP_TO_ACCOUNT_NUMBER(p_ship_to_org_id IN NUMBER)
RETURN varchar2 IS

l_ship_to_account_number varchar2(30);

CURSOR get_ship_to_account_number_cur (l_ship_to_org_id NUMBER)
IS
SELECT c.account_number
FROM hz_cust_acct_sites a,
hz_cust_site_uses b,
hz_cust_accounts c
WHERE
a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = l_ship_to_org_id
AND b.site_use_code = ‘SHIP_TO’
and a.cust_account_id = c.cust_account_id;

BEGIN
OPEN get_ship_to_account_number_cur (p_ship_to_org_id);
FETCH get_ship_to_account_number_cur INTO l_ship_to_account_number;
CLOSE get_ship_to_account_number_cur;
RETURN l_ship_to_account_number;
EXCEPTION WHEN OTHERS THEN

             

RETURN NULL;
END GET_SHIP_TO_ACCOUNT_NUMBER;

FUNCTION GET_INVOICE_TO_ACCOUNT_NUMBER(p_invoice_to_org_id IN NUMBER)
RETURN varchar2
IS

l_invoice_to_account_number varchar2(30);

CURSOR get_bill_to_account_number_cur (l_invoice_to_org_id NUMBER)
IS
SELECT c.account_number
FROM hz_cust_acct_sites a,
hz_cust_site_uses b,
hz_cust_accounts c
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = l_invoice_to_org_id
AND b.site_use_code = ‘BILL_TO’
and a.cust_account_id = c.cust_account_id;
BEGIN
OPEN get_bill_to_account_number_cur (p_invoice_to_org_id);
FETCH get_bill_to_account_number_cur INTO l_invoice_to_account_number;
CLOSE get_bill_to_account_number_cur;
RETURN l_invoice_to_account_number;

EXCEPTION WHEN OTHERS THEN

RETURN NULL;

END GET_INVOICE_TO_ACCOUNT_NUMBER;

Let’s also include these functions in our XQP_SOURCING_API_PUB package and compile our functions.

After confirming successful compilation, we can add similar function references in the Attribute Mapping setups for the Bill To Customer Account and Ship To Customer Account Qualifiers.

SCHQP_SOURCING_API_PUB.GET_SHIP_TO_ACCOUNT_NUMBER(OE_ORDER_PUB.G_HDR.ship_to_org_id)

SCHQP_SOURCING_API_PUB.GET_SHIP_TO_ACCOUNT_NUMBER(OE_ORDER_PUB.G_LINE.ship_to_org_id)

SCHQP_SOURCING_API_PUB.GET_INVOICE_TO_ACCOUNT_NUMBER(OE_ORDER_PUB.G_HDR.invoice_to_org_id)

SCHQP_SOURCING_API_PUB.GET_INVOICE_TO_ACCOUNT_NUMBER(OE_ORDER_PUB.G_LINE.invoice_to_org_id)

Once we’ve entered the references into the User Value fields, let’s build the attribute mapping rules for each to confirm the setups are successful.

All finished! Now if you go to Modifier setup form, you should see Sold To Customer Account Number, Bill To Customer Account Number, and Ship To Customer Account Number as available Qualifiers under the Customer context.

Without a doubt, the combination of Qualifier Attribute Mapping setups with PL/SQL creates flexible functionality for establishing how and when pricing Modifiers should be applied – empowering the implementer to meet the most complex of discounting and surcharge requirements.

Entry Filed under: Advanced Pricing, Order Management, Technical