OM:Defaulting Rules using PLSQL APIs

Defaulting Rules using PLSQL APIs

 

http://blog.oraclecontractors.com/?p=310

 

Order Management provides the ability to default a number of attributes onto the Sales Order Header and Lines based upon user-definable rules. For example, it is possible to default a Shipping Method onto the Order Header using the value assigned to the Order Type. As another example, it is possible to default the Warehouse onto the Order Line using a constant value. The defaulting rules provide a further dimension in allowing these attributes to be defaulted only when certain conditions are met e.g. the Customer is XYZ and the Line Type is ABC etc.

A more advanced feature of these defaulting rules enables one to apply a default to the Order Header and Lines by calling a custom PLSQL API, using several of the existing attributes on the Order Header and Lines as parameters. This is where the documentation gets a little vague and why I am including this article to help others with similar requirements.
 

Requirement

Default the appropriate Shipping Method when generating an Internal Sales Order from a Parts Requirement Order in Oracle Field Service.

For ATP items, Oracle is capable of automatically deriving the correct Shipping Method based upon Delivery Times set-up within the Field Service application and the Need-By date on the Parts Requirement. However, for non-ATP items, the Shipping Method is derived using the seeded Defaulting Rules in Oracle Order Management whereby the value is obtained from the following hierarchy of sources (top down):
 

Customer Ship To
Customer Bill To
Customer
Order Type
Price List

In our scenario, the Shipping Method is dependent upon the Need-By Date on the Parts Requirement and the Customer Party Type so that:

If Need-By Date is today, Shipping Method = Same Day
If Need-By Date is in future and Customer Party Type = Organization, Shipping Method = Express
If Need-By Date is in future and Customer Party Type = Person, Shipping Method = Economy

These rules should only apply for Internal Sales Orders created from Parts Requirements
 

Solution

Deriving the appropriate Shipping Method requires a calculation to be performed only when the Sales Order Source is Internal and the Order Type is Internal Parts Order. The use of a PLSQL API within the Order Management Defaulting Rules would satisfy this requirement quite easily.

The Defaulting Rule will be applied to the Shipping Method attribute on the Order Header.

To begin with, we need to define a Condition Template where we include the Order Source of Internal and the Order Type Internal Parts Order.

Next, we need to apply this Condition Template to a new Defaulting Rule that calls the PLSQL API. This is achieved by selecting a Default Source Type of PLSQL API within the Default Sourcing Rule and then entering the PLSQL API Package Name and Function within the Default Source; this latter field opens up a Flexfield where the Name and Function are entered together with two parameters, Attribute1 and Attribute2. These two Attributes are a little misleading as at first one would think that they are parameters to be passed into your custom Function referencing attributes on the Sales Order. As it turns out they do not and can be ignored.

The way these rules do appear to work is as follows:

There are two seeded packages provided by Oracle, one applying to Order Header Attributes, named ONT_HEADER_DEF_HDLR, and another applying to Order Line Attributes, named ONT_LINE_DEF_HDLR.
Each of these packages is responsible for validating the attributes held within the corresponding entity. So for example, the Package ONT_HEADER_DEF_HDLR contains a Procedure Default_Records that validates and derives values for all Order Header attributes, including our Shipping Method. Furthermore, the values currently held in each of the Order Header attributes are stored in a PLSQL Row Type variable defined in the package, named g_record.
 
When evaluating the Shipping Method, the aforementioned procedure, ONT_HEADER_DEF_HDLR.Default_Records, calls another dedicated package function, ONT_D1_SHIPPING_METHOD_CODE.Get_Default_Value(g_record).
 
After defining the Defaulting Rule, it must be compiled by selecting the option Generate Defaulting Handler Package from the Tools menu. This in turn calls a concurrent program named Default Generator. This program places a call to your custom Package and Function within the function ONT_D1_SHIPPING_METHOD_CODE.Get_Default_Value.

Within this call, the function passes in two parameters, p_database_object_name and p_attrribute_code; these two parameters represent Attribute1 and Attribute2 that appear within the Flexfield behind the Default Source field.
So when building your custom function, it must include the following definition:

FUNCTION xyz (p_database_object_name IN VARCHAR2
                      ,p_attribute_code             IN VARCHAR2)
RETURN VARCHAR2

However, you do not have to use these parameters within your function body to reference values in other attributes on the Order. Remember the PLSQL row g_record already holds these values.

Going back to our requirement then, the custom Function appears as follows: 
 
  – Check that the Internal requisition number (created when ordering the Parts Requirement)
  — is populated on the Sales Order Header attribute orig_sys_document_ref, obtained from g_record
  IF ONT_HEADER_DEF_HDLR.g_record.orig_sys_document_ref IS NOT NULL AND
      ONT_HEADER_DEF_HDLR.g_record.orig_sys_document_ref FND_API.G_MISS_CHAR THEN
    — Retrieve the Need By Date of the Requisition which corresponds with the Need By
    — Date on the Parts Requirement
    BEGIN 
      SELECT  nvl(prl.need_by_date, sysdate)
      INTO     vNeedBy
      FROM   apps.po_requisition_lines_all prl
                ,apps.po_requisition_headers_all prh
      WHERE  prh.segment1 = ONT_HEADER_DEF_HDLR.g_record.orig_sys_document_ref
      AND    prh.requisition_header_id = prl.requisition_header_id
      AND    rownum = 1;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN null;
    END;

   – If Need By Date is today, then return Shipping Method ‘Urgent’
   IF trunc(vNeedBy) = trunc(sysdate) THEN
      RETURN ‘Urgent’;
   – If Need By Date is in the future, then we need to determine whether the part
   – is being shipped to a Customer or Person. Derive the Customer Type using
   – the Customer Id populated within the Sales Order Header attribute, sold_to_org_id,
   — obtained from g_record
   ELSIF (ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id IS NOT NULL) AND
            (ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id FND_API.G_MISS_NUM) THEN
     BEGIN
       SELECT hp.party_type
       INTO   vPartyType
       FROM   apps.hz_parties hp
                  ,apps.ra_customers rc
       WHERE  rc.customer_id = ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id
       AND    rc.party_id = hp.party_id;
 
       IF  vPartyType = ‘PERSON’ THEN
            RETURN ‘Economy’;
         ELSIF vPartyType = ‘ORGANIZATION’ THEN
            RETURN ‘Express’;
         ELSE
            RETURN null;
          END IF;
       EXCEPTION
         WHEN OTHERS THEN
            RETURN null;
        END;
      ELSE
        RETURN null;
      END IF;
   ELSE
         RETURN null;
   END IF; 
 
Additional information about Defaulting Rules in OM can be found in the Oracle White Paper entitled ‘Using Defaulting Rules in Oracle Order Management’, available within Note 122461.1 on Metalink. To save you time, the White Paper mentions another document, ‘Rule Based Defaulting Framework HLD, which contains further details on building such PLSQL API’s; unfortunately it turns out that this document isn’t available for ‘public use’.