IFS version 10 (update 
.
Hi I am trying to create an event on "Release DOP" action.
That means, when user releases DOP (on "DOP Head" -> RMB-Release All), than the event should insert data to myself created table "agapp.ag_approve_col_hist" (different schema, not ifsapp) about approval proces on "Customer Order Line" that DOP is connected to.
I have created and tested sql code in sql developer and everything is working fine.
But as soon as i put exactly the same code (only particular dop_id in pure sql is in the event action parameter &NEW.DOP_ID) into the event action, the system gives me error in "Background jobs"
Any suggestions??
Thanks a lot.
Jan
Details;
SQL (ok in sql developer):
DECLARE
key_ref__ VARCHAR2(32000) := NULL;
BEGIN
for i in (
select * from ifsapp.dop_demand_cust_ord
where dop_id = 312169
)
loop
begin
key_ref__ := 'LINE_ITEM_NO='||i.line_item_no||'^LINE_NO='||i.line_no||'^ORDER_NO='||i.order_no||'^REL_NO='||i.rel_no||'^';
for k in (select * from ifsapp.approval_routing
where key_ref = key_ref__
)
loop
begin
insert into agapp.ag_approve_col_hist values (sysdate, ifsapp.dop_head_api.get_contract(i.dop_id), i.dop_id, key_ref__, k.description, k.app_date, k.group_id, k.person_id, k.app_sign, k.approval_status_db);
end;
end loop;
exception when others then
dbms_output.put_line(i.order_no||'-'||i.line_no||'-'||i.rel_no||' : '||sqlerrm);
end;
end loop;
END;
Created event:
Event action:
After trying to release DOP, the DOP stays in Unreleased state and generates error in background jobs:
Error in background jobs:
Error text:
The Event Action "Execute Online SQL" on "AG_DOP_RELEASE" has failed. Please contact your System Administrator to manage the Event Actions.
ORA-00900: invalid SQL statement
ORA-06512: at "IFSAPP.ERROR_SYS", line 138
ORA-06512: at "IFSAPP.ERROR_SYS", line 331
ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2186
ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2192
ORA-06512: at "IFSAPP.EVENT_SYS", line 199
ORA-06512: at "IFSAPP.EVENT_SYS", line 199
ORA-0651 ( ORA-20135: FndEventAction.EVENT_ACTION_ERROR