Defining Oracle OM Defaulting Rule using PL/SQL (PLSQL) API
Standard Oracle has provided option to extend OM defaulting rules by defining your own pl/sql defaulting rules. Steps below provide you detailed insight of defining and using your own pl/sql based defaulting rule.
Setup in ERP Environment
TBP
Sample Custom Package code
Below given is sample code for plsql package which you can call from your custom defaulting rule.
Initializing variable l_line_rec gives you access to all the values of the line which is calling this package.
CREATE OR REPLACE
PACKAGE xx_aps_test
AS
FUNCTION custom_default_rule(
p_database_object_name IN VARCHAR2,
p_attribute_code IN VARCHAR2)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE
PACKAGE BODY xx_aps_test
AS
FUNCTION custom_default_rule(
p_database_object_name IN VARCHAR2,
p_attribute_code IN VARCHAR2)
RETURN VARCHAR2
AS
l_line_rec OE_AK_ORDER_LINES_V%ROWTYPE;
BEGIN
l_line_rec := ONT_LINE_DEF_HDLR.g_record;
BEGIN
INSERT INTO aps_test VALUES
(aps_seq.nextval,
TO_CHAR(sysdate,'DD-MON-RRRR HH24:MI:SS')
);
INSERT INTO aps_test VALUES
(aps_seq.nextval, l_line_rec.line_id
);
INSERT INTO aps_test VALUES
(aps_seq.nextval, l_line_rec.inventory_item_id
);
EXCEPTION
WHEN OTHERS THEN
RETURN ('000001_DHL_A_WPX');
END;
IF l_line_rec.inventory_item_id = 746 then
RETURN ('000001_DHL_A_WPX');
elsif l_line_rec.inventory_item_id = 231 then
RETURN('000001_FEDEX_A_2 DAY');
else
RETURN('000001_FEDEX_A_STANDARD O');
end if;
EXCEPTION
WHEN OTHERS THEN
IF oe_msg_pub.check_msg_level
(oe_msg_pub.g_msg_lvl_unexp_error) THEN
oe_msg_pub.add_exc_msg ('OE_Default_PVT',
'CUSTOM_DEFAULT_RULE');
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END custom_default_rule;
END;
/
Enabling and Disabling Default Defaulting Dependency
Standard Oracle has limited pre-defined defaulting rule field dependency. Like if you change item then Ship Method will not change because that is not part of default dependency pre-defined by Oracle. Oracle has provided custom hook OEXEDEPB.pls which can be used to enable or disable defaulting dependency.
You just need to code this package and compile in your environment. Below code shows how dependency between item and ship method code can be enabled.
create or replace
PACKAGE BODY OE_Dependencies_Extn AS
/* $Header: OEXEDEPB.pls 120.999 2006/04/03 11:33:12 chhung
noship $ */
-- Global constant holding the package name
G_PKG_NAME
CONSTANT
VARCHAR2(30):='OE_Dependencies_Extn';
PROCEDURE Load_Entity_Attributes
(
p_entity_code
IN VARCHAR2
, x_extn_dep_tbl OUT NOCOPY Dep_Tbl_Type)
IS
l_index
NUMBER;
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
--
BEGIN
null;
-- In order to start using the package:
-- 1)Increase the version number in the
header line to a high value
-- => Header: OEXEDEPB.pls 115.1000. This
would prevent patches
-- from over-writing this package in the
future.
-- 2)Included are some examples on how to
enable/disable dependencies
-- Please use these guidelines to edit
dependencies as per your
-- defaulting rules. Please note that:
-- i) List of
attributes is restricted to those in the earlier
--
comments in this file.
-- ii) Source
attribute and dependent attribute should belong
--
to the same entity!
--
This API does not support dependencies across entities i.e.
--
changing an attribute on order header will not result in
--
a change to attributes on order line.
-- 3)Uncomment this code and compile.
oe_debug_pub.add('Enter
OE_Dependencies_Extn.LOAD_ENTITY_ATTRIBUTES', 1);
-- Initializing index value for pl/sql table.
Ensure that the index
-- value is incremented after setting each
dependency record.
l_index := 1;
-- Dependencies for Order Header Entity
IF p_entity_code =
OE_GLOBALS.G_ENTITY_HEADER THEN
null;
-- Sample Code for
Disabling dependency of Invoice To on Ship To
--
x_extn_dep_tbl(l_index).source_attribute := OE_HEADER_UTIL.G_SHIP_TO_ORG;
--
x_extn_dep_tbl(l_index).dependent_attribute := OE_HEADER_UTIL.G_INVOICE_TO_ORG;
--
x_extn_dep_tbl(l_index).enabled_flag := 'N';
-- l_index := l_index + 1;
-- Dependencies for Order Line Entity
ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE
THEN
null;
-- Sample Code for
Disabling dependency of Invoice To on Ship To
--
x_extn_dep_tbl(l_index).source_attribute := OE_LINE_UTIL.G_SHIP_TO_ORG;
--
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_INVOICE_TO_ORG;
--
x_extn_dep_tbl(l_index).enabled_flag := 'N';
-- l_index := l_index + 1;
-- Sample Code for adding
dependency of Source Type on Item
--
x_extn_dep_tbl(l_index).source_attribute := OE_LINE_UTIL.G_INVENTORY_ITEM;
--
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_SOURCE_TYPE;
--
x_extn_dep_tbl(l_index).enabled_flag := 'Y';
-- l_index := l_index + 1;
x_extn_dep_tbl(l_index).source_attribute
:= OE_LINE_UTIL.G_ORDERED_ITEM;
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_SHIPPING_METHOD;
x_extn_dep_tbl(l_index).enabled_flag := 'Y';
l_index := l_index + 1;
x_extn_dep_tbl(l_index).source_attribute
:= OE_LINE_UTIL.G_INVENTORY_ITEM;
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_SHIPPING_METHOD;
x_extn_dep_tbl(l_index).enabled_flag := 'Y';
l_index := l_index + 1;
END IF;
oe_debug_pub.add('Exit
OE_Dependencies_Extn.LOAD_ENTITY_ATTRIBUTES', 1) ;
EXCEPTION
WHEN OTHERS THEN
IF
OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
THEN
OE_MSG_PUB.Add_Exc_Msg
( G_PKG_NAME
, 'Load_Entity_Attributes'
);
END IF;
RAISE
FND_API.G_EXC_UNEXPECTED_ERROR;
END Load_Entity_Attributes;
END OE_Dependencies_Extn;
