Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Business Event not triggering the PLSQL procedure.. What to do?

manu.murMay 7 2013 — edited May 8 2013
We need to call a plsql procedure when the GL Approval workflow has ended with approval. I thought i could do this by customizing the relevant business event.

We are on EBS 12.1.3 with RDBMS : 11.2.0.3.0.

I saw that the business event oracle.apps.gl.Journals.journal.approve was disabled currently. I enabled it and created a subscription for it. Subscription was a PLSQL procedure. Currently, for test purpose only thing it is doing is to insert a row into a table.

Business Event Subscription settings:
System: ORDEBS.SYSTEM.COM

Triggering Event
Source Type:Local
Event Filter: oracle.apps.gl.Journals.journal.approve


Execution Condition
Phase: 100
Status: Enabled
Rule Data: Message

Action Type:Custom
On Error: Stop and Rollback

Action
PL/SQL Rule Function: XX_GL_APPROVE_BE_PKG.Get_Attributes
Priority: Normal

Documentation (Not sure what value to be given for these. I went with the below values.)
Owner: Company Name
Owner Tag: Custom Schema

Using the below query i can see that the business event is getting called when the approval happens (One row added each time approval happened). But I cant see any rows in the table where it should insert a row. What could be going wrong? How can i verify that the procedure has been called?

select * from WF_DEFERRED where corrid ='APPS:oracle.apps.gl.Journals.journal.approve'

Procedure:
CREATE OR REPLACE PACKAGE BODY APPS.XX_GL_APPROVE_BE_PKG
AS
PROCEDURE Get_Attributes
IS
BEGIN
INSERT INTO xx.xx_test_table VALUES ('From BE');
COMMIT;
END Get_Attributes;
END XX_GL_APPROVE_BE_PKG;
This post has been answered by Alejandro Sosa-Oracle on May 8 2013
Jump to Answer

Comments

Jeet_A
Hi,

Can you try giving schema name

Documentation (Not sure what value to be given for these. I went with the below values.)
Owner: Company Name
Owner Tag: Custom Schema


as

Documentation
Owner: APPS
Owner Tag: APPS

It should be Application name as mentioned by Alejandro

Thanks,
Jit

Edited by: appsjit on May 8, 2013 11:43 AM
1 person found this helpful
Answer
Hello,

Ensure OWNER_TAG represents an actual licensed application, like 'FND' no quotes. The business event system will dispatch subscriptions for licensed products.

Also, to detect any other possible errors please add an error type subscription. This way the BES will catch the error and will notify SYSADMIN of such failure, it won't go silent. You can use this link to see how: https://blogs.oracle.com/oracleworkflow/entry/defining_business_events_with_synchronous

Regards,

Alejandro
Marked as Answer by manu.mur · Sep 27 2020
Jeet_A
Hi Alejandro,

Thanks for correcting me. This describes in detail.

Thanks,
Jit
manu.mur
Thanks Alejandro. Now this is working.

Changes i made:
1. Added the WFERROR workflow as a subscription to this event. So i could see that the function i am calling from the event did not have proper signature.
2. Changed the function signature to have standard parameters like:

CREATE OR REPLACE PACKAGE BODY XX_GL_APPROVE_BE_PKG
AS

function subscription(p_subscription_guid in raw,
p_event in out nocopy wf_event_t) return varchar2 is
l_result varchar2(20);
begin

insert into xxvtv.xxvtv_test_table values ('From BE');
commit;

exception
when others then
wf_core.context('XX_GL_APPROVE_BE_PKG','function subscription', p_event.getEventName(), p_event.getEventKey());
wf_event.setErrorInfo(p_event, 'ERROR');
return 'ERROR';
end subscription;

END XX_GL_APPROVE_BE_PKG;

3. Changed the owner name and owner tag both to the custom schema name (XX)
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 5 2013
Added on May 7 2013
#database-key-features, #workflow
4 comments
3,473 views