My Oracle Support Banner

Business Event - oracle.apps.ego.item.postXrefChange Event Is Not Raised After Item Cross Reference Is Updated (Doc ID 1153437.1)

Last updated on APRIL 17, 2018

Applies to:

Oracle Product Hub - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

The "oracle.apps.ego.item.postXrefChange" business event is not raised after Item cross reference is updated using the public API apps.mtl_cross_references_pub.process_xref.

Business Event

oracle.apps.ego.item.postXrefChange


What is working
Creating item cross references via the front-end DOES fire the business event.

Steps
1. Enable the business event for item cross reference: oracle.apps.ego.item.postXrefChange
2. Create/update Item cross reference using the forms
3. The business event fires.
** This is working as expected.
4. Create/update Item cross reference using the public API.
5. The business event does NOT fire.

A. Detailed Steps: Create/Test Event
Create the event following Note 1074754.1 - How to Subscribe and Use Item Business Events in Advanced Product Catalog (APC).
The event is tested by enabling the business event, then taking an action that should cause the event to fire.
Look into the temporary table created for testing, Xx_Event_Tbl.
It should have a record with the details of the business event firing.

1. Create event

Login as sysadmin
Responsibility: Workflow Administrator Web Applications > Business Events
Query business event: oracle.apps.ego.item.postXrefChange
Create subscription.
Rule Function: XX_EVENT_TEST.PRINT_TEST_MESSAGE
xx_event_test.print_test_message
Enable the event.

2. Test event using WF responsibility:
3. Check if the business event fired. See results in table:

SQL> desc Xx_Event_Tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 MESSAGE                                            VARCHAR2(200)
 EVENT_TIME                                         DATE

Check values:

Select * From Xx_Event_Tbl order by event_time desc;

Example output:

Called from postItemCreate event: XX_EVENT_PKG : EVENTNAME= oracle.apps.ego.item.postXrefChange, EVENTKEY = JBP12334455555555
From postItemCreate event : EVENTNAME= oracle.apps.ego.item.postXrefChange, EVENTKEY = JBP12334455555555 returned EXCEPTION


B. Detailed Steps: Confirm event via front-end
Inventory > Items > Cross references
Create cross reference for an item
Check if the business event fired
See record.

Select * From Xx_Event_Tbl order by event_time desc;

Example output:

Called from postItemCreate event: XX_EVENT_PKG : EVENTNAME= oracle.apps.ego.item.postXrefChange, EVENTKEY = oracle.apps.ego.item.postXrefChange-20629


C. Detailed Steps: Confirm event via API
Run the API to create the item cross reference but see that it does NOT insert a row into the temporary debug table, Xx_Event_Tbl. This replicates the issue and shows the business event does not fire.

Select * From Xx_Event_Tbl order by event_time desc;
no rows

* This replicates the issue.


Here is the SQL call that creates the item cross reference with the API:

set serveroutput on
DECLARE
-- Errors
error_code varchar2(1000);
err_msg varchar2(1000);
-- Variables
p_return_status VARCHAR2(100);
p_msg_count NUMBER;
p_message_list Error_Handler.Error_Tbl_Type;
l_XRef_Tbl MTL_CROSS_REFERENCES_PUB.XRef_Tbl_Type;
l_XRef_Rec MTL_CROSS_REFERENCES_PUB.XRef_Rec_Type; -- Declaring the record type object
BEGIN
dbms_output.put_line('Starting...');
dbms_output.put_line('Declare user - start');
fnd_global.APPS_INITIALIZE(1068, 20634, 401);
dbms_output.put_line('User ID:' || fnd_global.USER_ID);
dbms_output.put_line('User ID:' || fnd_global.USER_NAME);
dbms_output.put_line('Declare user - done');

l_XRef_Rec.transaction_type := 'CREATE';
l_XRef_Rec.Inventory_Item_Id := 162744;
l_XRef_Rec.Organization_Id := NULL;
l_XRef_Rec.Cross_Reference_Type := 'ChargeMaster';
l_XRef_Rec.Cross_Reference := 'Random';
l_XRef_Rec.Org_Independent_Flag := 'Y';

l_XRef_Rec.Last_Update_Date := sysdate;
l_XRef_Rec.Last_Updated_By := -9999;
l_XRef_Rec.Creation_Date := sysdate;
l_XRef_Rec.Created_By := -9999;
l_XRef_Rec.Last_Update_Login := -9999;

l_XRef_Tbl(0) := l_XRef_Rec;

dbms_output.put_line('Calling api...');

fnd_client_info.set_org_context ('207');

MTL_CROSS_REFERENCES_PUB.Process_XRef(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_XRef_Tbl => l_XRef_Tbl,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_message_list => p_message_list);

-- Error Processing
dbms_output.put_line('Status: '||p_return_status|| ', count: '|| p_msg_count||'Raise errors...');
/* For i In 1..p_msg_count
Loop
dbms_output.put_line ( Substr(p_message_list(i).message_text, 1, 240) );
End Loop;
*/

dbms_output.put_line('Commit...');
commit;
dbms_output.put_line('Finishing...');

EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
err_msg := substrb(SQLERRM, 1, 80);
dbms_output.put_line('Error Code: '||error_code||', Error message: ' || err_msg);
END;
/

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.