This appendix covers the following topics:
You can define an unlimited number of user-defined attributes for items without programming. These attributes can have different data types, value sets, and can be parametrically searched. This appendix describes how to synchronize user-defined attribute values with descriptive element values. Oracle provides a public API to update Item Descriptive Element values. It also raises a business event when any pre-selected user-defined attribute values change. You can subscribe to this event and then call the Item Descriptive Element API to synchronize the descriptive elements with the attribute values. A business event is raised when any pre-selected item user-defined attribute values change.
See: Event Manager, and Standard API for an Event Rule Function, Oracle Workflow Developer's Guide.
You can configure user-defined attribute groups to raise a Business Event every time a row is added, altered, or deleted The name of the Business Event raised is
oracle.apps.ego.item.attributesChanged
The parameters passed by this Business Event are
INVENTORY_ITEM_ID
ORGANIZATION_ID
EXTENSION_ID
ATTR_GROUP_NAME
The Event is disabled by default and must be explicitly enabled for synchronization to work.
Oracle provides the following API (with accompanying data type) to maintain Descriptive Elements for a given item.
The data type used by the API follows:
ITEM_DESC_ELEMENT_TABLE: this is an associative array (also known as a PL/SQL table or an index-by table) of ITEM_DESC_ELEMENT records: ITEM_DESC_ELEMENT IS RECORD
(
ELEMENT_NAME VARCHAR2(30)
,ELEMENT_VALUE VARCHAR2(30)
,DESCRIPTION_DEFAULT VARCHAR2(1)
);
ELEMENT_NAME: Column ELEMENT_NAME from MTL_DESCRIPTIVE_ELEMENTS
ELEMENT_VALUE: Column ELEMENT_VALUE from MTL_DESCR_ELEMENT_VALUES
DESCRIPTION_DEFAULT: Column DEFAULT_ELEMENT_FLAG from MTL_DESCR_ELEMENT_VALUES (indicates whether this Descriptive Element will be used in the description generation)
The API signature is:
INV_ITEM_CATALOG_ELEM_PUB.Process_Item_Descr_Elements ( p_api_version IN NUMBER ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE ,p_commit_flag IN VARCHAR2 DEFAULT fnd_api.g_FALSE ,p_validation_level IN NUMBER DEFAULT INV_ITEM_CATALOG_ELEM_PUB.g_VALIDATE_ALL ,p_inventory_item_id IN NUMBER DEFAULT -999 ,p_item_number IN VARCHAR2 DEFAULT NULL ,p_item_desc_element_table IN ITEM_DESC_ELEMENT_TABLE ,x_generated_descr OUT VARCHAR2 ,x_return_status OUT VARCHAR2 ,x_msg_count OUT NUMBER ,x_msg_data OUT VARCHAR2 );
p_api_version
Pass the value 1.0 for this parameter.
p_init_msg_list
This parameter can safely be defaulted.
p_commit_flag
Indicates whether or not the API commits the transaction.
p_validation_level
Determines how much validation occurs; can safely be defaulted.
p_inventory_item_id
Pass the value 1.0 for this parameter.
p_item_number
This parameter can safely be defaulted.
p_item_desc_element_table
Data type defined above.
x_generated_descr
Returns the Item Description generated from the updated Descriptive Elements. If you want to update the Item Description with this value, use Item Open Interface.
x_return_status
Returns one of the following:
FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR
x_msg_count
Number of messages logged by the API.
x_msg_data
The message logged if exactly one message was logged.
As briefly mentioned earlier, the Business Event oracle.apps.ego.item.attributesChanged must be explicitly enabled through the graphical user interface in order for synchronization to work. After this step is complete, you must subscribe to the User-Defined Attributes Business Event. See: Event Manager, Oracle Workflow Developer's Guide.
oracle.apps.ego.item.attributesChanged
As part of Event Subscription, define an Event Subscription Rule Function that will be called when the Event is raised. See Standard API for an Event Rule Function, Oracle Workflow Developer's Guide.
This Rule Function should:
Identify the attribute group that raised the Event and determine the correct Descriptive Elements for that attribute group (perhaps through customer-supplied mapping metadata).
Fetch the data altered by the Event (see the following example Rule Function and also the White Paper “Reading Data for Item User-Defined Attributes” for more details) and process it as necessary.
Call the Item Descriptive Elements API that is passing the processed data.
FUNCTION Synch_Attrs_With_Desc_Elems (
p_subscription_guid IN RAW
,p_event IN OUT NOCOPY WF_EVENT_T
)
RETURN VARCHAR2
IS
TYPE ATTR_TO_DESC_ELEM_MAPPING_REC IS RECORD
(
ATTR_NAME VARCHAR2(30)
,DESC_ELEM_NAME VARCHAR2(30)
,DESCRIPTION_DEFAULT VARCHAR2(1)
);
TYPE ATTR_TO_DESC_ELEM_MAPPING_TBL IS TABLE OF ATTR_TO_DESC_ELEM_MAPPING_REC
INDEX BY BINARY_INTEGER;
l_parameter_list WF_PARAMETER_LIST_T;
l_next_parameter WF_PARAMETER_T;
l_dml_type_param WF_PARAMETER_T;
l_attr_group_name_param WF_PARAMETER_T;
l_inventory_item_id_param WF_PARAMETER_T;
l_organization_id_param WF_PARAMETER_T;
l_revision_id_param WF_PARAMETER_T;
l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
l_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
x_return_status VARCHAR2(1);
x_errorcode NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
l_current_mapping_rec ATTR_TO_DESC_ELEM_MAPPING_REC;
l_mapping_rec_table ATTR_TO_DESC_ELEM_MAPPING_TBL;
l_attributes_data_index NUMBER;
l_current_row_obj EGO_USER_ATTR_ROW_OBJ;
l_current_data_obj EGO_USER_ATTR_DATA_OBJ;
l_current_item_desc_elem INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT;
l_item_desc_elem_table INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT_TABLE;
x_generated_descr VARCHAR2(240);
l_return_status VARCHAR2(30) := 'SUCCESS';
BEGIN
l_parameter_list := p_event.getParameterList();
--------------------------------------------------------
-- Sort the parameters that the Business Event passed
--------------------------------------------------------
FOR i IN l_parameter_list.FIRST .. l_parameter_list.LAST
LOOP
l_next_parameter := l_parameter_list(i);
IF (l_next_parameter.getName() = 'DML_TYPE') THEN
l_dml_type_param := l_next_parameter;
ELSIF (l_next_parameter.getName() = 'ATTR_GROUP_NAME') THEN
l_attr_group_name_param := l_next_parameter;
ELSIF (l_next_parameter.getName() = 'INVENTORY_ITEM_ID') THEN
l_inventory_item_id_param := l_next_parameter;
ELSIF (l_next_parameter.getName() = 'ORGANIZATION_ID') THEN
l_organization_id_param := l_next_parameter;
ELSIF (l_next_parameter.getName() = 'REVISION_ID') THEN
l_revision_id_param := l_next_parameter;
END IF;
END LOOP;
---------------------------------------------------------
Copy the PK values into our EGO_COL_NAME_VALUE_PAIR_ARRAY object
---------------------------------------------------------
l_pk_column_values :=
EGO_COL_NAME_VALUE_PAIR_ARRAY(
EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID',
l_inventory_item_id_param.getValue())
,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID',
l_organization_id_param.getValue())
);
---------------------------------------------------------------
Prepare an EGO_ATTR_GROUP_REQUEST_TABLE object with one element, an
EGO_ATTR_GROUP_REQUEST_OBJ for the Attr Group that raised the Event
----------------------------------------------------------------
l_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
l_request_table.EXTEND();
l_request_table(l_request_table.LAST) :=
EGO_ATTR_GROUP_REQUEST_OBJ(
NULL --ATTR_GROUP_ID
,431
,'EGO_ITEMMGMT_GROUP'
,l_attr_group_name_param.getValue()
,l_revision_id_param.getValue()
,NULL --DATA_LEVEL_2
,NULL --DATA_LEVEL_3
,NULL --ATTR_NAME_LIST
);
--------------------------------------------------
Get the data for this Event, if there is any
--------------------------------------------------
IF (l_dml_type_param.getValue() <> 'DELETE') THEN
EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
p_api_version => 1.0
,p_object_name => 'EGO_ITEM'
,p_pk_column_name_value_pairs => l_pk_column_values
,p_attr_group_request_table => l_request_table
,p_user_privileges_on_object => NULL
,p_entity_id => NULL
,p_entity_index => NULL
,p_entity_code => NULL
,p_debug_level => 0
,p_init_error_handler => FND_API.G_FALSE
,p_init_fnd_msg_list => FND_API.G_FALSE
,p_add_errors_to_fnd_stack => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,x_attributes_row_table => x_attributes_row_table
,x_attributes_data_table => x_attributes_data_table
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
------------------------------------------------------------
If the call failed, we report that an error occurred
(in a production situation, we would retrieve and pass
on all errors on the ERROR_HANDLER message stack)
------------------------------------------------------------
IF (x_return_status IS NULL OR
x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
RAISE FND_API.G_EXC_ERROR;
ELSE
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
END IF;
END IF;
------------------------------------------------
Process the Descriptive Elements according to the
Attr Group that raised the Event and the DML type
----------------------------------------------
IF (l_attr_group_name_param.getValue() = 'Pella_Windows') THEN
----------------------------------------------
Find mappings from Attr Group Attrs to Desc Elems; in this example,
hard code the mapping from Attr Group Attrs to Desc Elems, but in a
production situation customers may have some stored mapping between
the Attrs in an Attr Group associated to a Catalog Category and the
Descriptive Elements for that Catalog Category (similarly, in this
example we always set DESCRIPTION_DEFAULT as 'Y' for simplicity)
------------------------------------------------
l_current_mapping_rec.ATTR_NAME := 'Style';
l_current_mapping_rec.DESC_ELEM_NAME := 'Style';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'GB_Options';
l_current_mapping_rec.DESC_ELEM_NAME := 'Grille Bar Options';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'Wood_Type';
l_current_mapping_rec.DESC_ELEM_NAME := 'Wood Type';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'Frame_Area';
l_current_mapping_rec.DESC_ELEM_NAME := 'Frame Area';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'GRADE';
l_current_mapping_rec.DESC_ELEM_NAME := 'Grade';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'PW_Exterior';
l_current_mapping_rec.DESC_ELEM_NAME := 'Primed Wood Exterior';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'DATE';
l_current_mapping_rec.DESC_ELEM_NAME := 'Date';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
ELSIF (l_attr_group_name_param.getValue() = 'WD_Attributes') THEN
l_current_mapping_rec.ATTR_NAME := 'STYLE';
l_current_mapping_rec.DESC_ELEM_NAME := 'WD Style';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'WD_Grade';
l_current_mapping_rec.DESC_ELEM_NAME := 'WD Grade';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'DATETIME';
l_current_mapping_rec.DESC_ELEM_NAME := 'Date';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
l_current_mapping_rec.ATTR_NAME := 'URL_link';
l_current_mapping_rec.DESC_ELEM_NAME := 'URL';
l_current_mapping_rec.DESCRIPTION_DEFAULT := 'Y';
l_mapping_rec_table(l_mapping_rec_table.COUNT + 1) := l_current_mapping_rec;
END IF;
-----------------------------------------------------------------
For every Desc Elem, get its value (a NULL value, or a DML_TYPE of DELETE,
will result in a NULL Elem value), set its DESCRIPTION_DEFAULT flag, and
add it to the ITEM_DESC_ELEMENT_TABLE
-----------------------------------------------------------------
FOR i IN l_mapping_rec_table.FIRST .. l_mapping_rec_table.LAST
LOOP
l_current_mapping_rec := l_mapping_rec_table(i);
l_current_item_desc_elem.ELEMENT_NAME := l_current_mapping_rec.DESC_ELEM_NAME;
l_current_item_desc_elem.DESCRIPTION_DEFAULT := l_current_mapping_rec.DESCRIPTION_DEFAULT;
-------------------------------------------------------------------
This example uses single-row Attr Groups, so we don't need to make use
of the EGO_USER_ATTR_ROW_TABLE object; but for a multi-row Attr Group,
we would use the ROW_IDENTIFIER field of each EGO_USER_ATTR_ROW_OBJ to
find all the EGO_USER_ATTR_DATA_OBJ objects for that particular row
------------------------------------------------------------------
IF (x_attributes_row_table IS NOT NULL AND
x_attributes_row_table.COUNT > 0 AND
x_attributes_data_table IS NOT NULL AND
x_attributes_data_table.COUNT > 0) THEN
l_attributes_data_index := x_attributes_data_table.FIRST;
WHILE l_attributes_data_index <= x_attributes_data_table.LAST
LOOP
EXIT WHEN l_current_item_desc_elem.ELEMENT_VALUE IS NOT NULL;
l_current_data_obj := x_attributes_data_table(l_attributes_data_index);
IF (l_current_data_obj.ATTR_NAME = l_current_mapping_rec.ATTR_NAME) THEN
l_current_item_desc_elem.ELEMENT_VALUE := SUBSTRB(l_current_data_obj.ATTR_DISP_VALUE, 1, 30);
END IF;
l_attributes_data_index := x_attributes_data_table.NEXT(l_attributes_data_index);
END LOOP;
END IF;
--------------------------------------------------------
-- Now we add the ITEM_DESC_ELEMENT record into our table
-------------------------------------------------------
l_item_desc_elem_table(l_item_desc_elem_table.COUNT + 1) := l_current_item_desc_elem;
END LOOP;
----------------------------------------------------
Now that we have all the necessary data and metadata, we
call the API to update the Desc Elems for this Attr Group
(note that the API returns the Item Description generated
from the updated Descriptive Elements; if required, you
can update the Description through Item Open Interface)
---------------------------------------------------
INV_ITEM_CATALOG_ELEM_PUB.Process_Item_Descr_Elements(
p_api_version => 1.0
,p_inventory_item_id => l_inventory_item_id_param.getValue()
,p_item_desc_element_table => l_item_desc_elem_table
,x_generated_descr => x_generated_descr
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
RETURN l_return_status;
EXCEPTION
WHEN OTHERS THEN
WF_CORE.CONTEXT('EGO_EF_QA_PUB', 'Synch_Attrs_With_Desc_Elems',
p_event.getEventName(), p_subscription_guid);
WF_EVENT.setErrorInfo(p_event, 'ERROR');
RETURN 'ERROR';
END Synch_Attrs_With_Desc_Elems;