A Creative Use of Lookups in EBS 12

Background:

I recently worked on a SOA Middleware project that interfaced our EBS item-related data (including inventory levels) with various third-party websites. Since it was the same inventory being sent to multiple websites, they wanted a way to manage (throttle back) available inventory sent to each site.

Previously, everything was coded using a combination of C#.NET and PL/SQL and any change or adjustment required IT intervention since everything was hard-coded. I was asked to come up with a way to use Standard EBS screens to allow the Users to manage these inventory feeds.


In the following example, I will demonstrate how to use standard lookup functionality within EBS to derive a set of Trading Partner (TP) specific rules to allow the Business Units to manage how inventory is sent at these three levels:

1. Item Level
2. Type Level
3. All Level (this is a generic level)

Here is a sample Lookup screen in EBS:

Since Code and Meaning values must be unique, I used a "_1_" as ways to increment similar values. You would setup this screen for each TP. This is not always pretty but it works.

Here is how the hierarchy is implemented: 

If an item is listed by SKU, then the SKU_<ordered_item> mapping is chosen first. Based on the actual inventory values obtained from the Description column, it uses the multiplier in the Tag column to recalculate what amount of inventory to send. If no match there, it then checks the item's Type (TYP_<n>_<type>). If no match there, it defaults to GEN_<n> values. All that is required is a "GEN_" setup. 

For Tags less than 1, the amount in the screen is used as a multiplier. For numbers greater than 1, it is an absolute number. 

These entries in the above Lookup screen:

Code     Description    Tag

-----    -----------    ---

GEN_1    <=3             1

GEN_2    >3 and <=30    .5

GEN_3    >30            .99


translate to:

If our current inventory is 3 or less, send 1 item. 

If our current inventory is between 4 and 30, send half. So if 10 – send 5. If 20 – send 10.

If our current inventory is more than 30 – send that number. It will either be the original number or one less depending on the rounding used in the Function below.

Here is the Function that was created:

CREATE OR REPLACE FUNCTION APPS.xxoc_tp_inv_send(p_partner IN VARCHAR2, p_sku IN VARCHAR2, p_type IN VARCHAR2, p_quantity IN NUMBER)

    RETURN NUMBER

IS

    v_result NUMBER;

    p_lookup  VARCHAR2(50);

BEGIN

    p_lookup   := 'OC_' || p_partner || '_INVENTORY_RULES';

     

    SELECT CASE WHEN to_number(tag, '999.99') >= 1 THEN to_number(tag, '999.99') ELSE to_number(tag, '999.99') * p_quantity END

      INTO v_result

      FROM (SELECT *

              FROM (SELECT sku,

                           typ,

                           gen,

                           gt,

                           TO_NUMBER(NVL2(gt, REGEXP_SUBSTR(description, '[0-9]+'), NULL)) rmin,

                           lt,

                           TO_NUMBER(NVL2(

                                         lt,

                                         REGEXP_SUBSTR(

                                             description,

                                             '[0-9]+',

                                             1,

                                             NVL2(gt, 2, 1)

                                         ),

                                         NULL

                                     ))

                               rmax,

                           tag

                      FROM (SELECT meaning,

                   description,

                   tag,

                   CASE WHEN meaning LIKE 'SKU%' THEN SUBSTR (meaning, 5) END sku,

                   CASE WHEN meaning LIKE 'TYP%' THEN SUBSTR (meaning, 7) END typ,

                   CASE WHEN meaning LIKE 'GEN%' THEN 'GEN' END gen,

                   REGEXP_SUBSTR (description, '>=?') gt,

                   REGEXP_SUBSTR (description, '<= ?') lt

              FROM FND_LOOKUP_VALUES FLV

             WHERE LOOKUP_TYPE = p_lookup AND ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN flv.start_date_active AND NVL (flv.end_date_active, SYSDATE + 1)))

             WHERE (sku = p_sku OR typ = p_type OR gen = 'GEN')

               AND (CASE

                        WHEN gt = '>' AND p_quantity > rmin THEN 1

                        WHEN gt = '>=' AND p_quantity >= rmin THEN 1

                        WHEN gt IS NULL THEN 1

                    END = 1

                AND CASE

                        WHEN lt = '<' AND p_quantity < rmax THEN 1

                        WHEN lt = '<=' AND p_quantity <= rmax THEN 1

                        WHEN lt IS NULL THEN 1

                    END = 1)

            ORDER BY CASE WHEN sku = p_sku THEN 1 WHEN typ = p_type THEN 2 ELSE 3 END) x

     WHERE ROWNUM = 1;

    -- v_result := CEIL(v_result);  -- Round the result up. If round down - use FLOOR

    v_result := FLOOR(v_result);  -- Round the result down. If round up - use CEIL

    RETURN v_result;

END;

/

Sample uses – test of the Generic translation:

21:16:10 GOLD1> select xxoc_tp_inv_send('BUY','mysku123','mytype321',3) from dual;

XXOC_TP_INV_SEND('BUY','MYSKU123','MYTYPE321',3)

------------------------------------------------

                                               1

21:17:12 GOLD1> select xxoc_tp_inv_send('BUY','mysku123','mytype321',6) from dual;

XXOC_TP_INV_SEND('BUY','MYSKU123','MYTYPE321',6)

------------------------------------------------

                                               3

21:17:51 GOLD1> select xxoc_tp_inv_send('BUY','mysku123','mytype321',32) from dual;

XXOC_TP_INV_SEND('BUY','MYSKU123','MYTYPE321',32)

-------------------------------------------------

                                               31

Test of the Type translation:

21:18:08 GOLD1> select xxoc_tp_inv_send('BUY','mysku123','OC_KIT_AB_OPENBOX',10) from dual;

XXOC_TP_INV_SEND('BUY','MYSKU123','OC_KIT_AB_OPENBOX',10)

---------------------------------------------------------

                                                        5

21:22:54 GOLD1> select xxoc_tp_inv_send('BUY','mysku123','OC_KIT_AB_OPENBOX',40) from dual;

XXOC_TP_INV_SEND('BUY','MYSKU123','OC_KIT_AB_OPENBOX',40)

---------------------------------------------------------

                                                       10

　

Test of the SKU translation:

21:41:23 GOLD1> select xxoc_tp_inv_send('BUY','074101011265','mytype321',100) from dual;

XXOC_TP_INV_SEND('BUY','074101011265','MYTYPE321',100)

------------------------------------------------------

                                                     0

　

21:41:50 GOLD1> select xxoc_tp_inv_send('BUY','074101011265','mytype321',20000) from dual;

XXOC_TP_INV_SEND('BUY','074101011265','MYTYPE321',20000)

--------------------------------------------------------

                                                   19800

　

In the above example, since we went over the SKU Rule's amount of 10,000, then the GEN Rule processed using a multiplier of .99 then rounded down (FLOOR).