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.
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:
- Item Level
- Type Level
- All Level (this is a generic level)
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.
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:
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);
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:
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).
