Hello,
I want to park an order when a condition in the shop order operation is happening. Execute it directly generates a a mutation issue, so I need to execute the park action n the background.
For that, I have created the following code in a custom event:
declare
p0_ VARCHAR2(32000) := NULL;
p1_ VARCHAR2(32000) := NULL;
p2_ VARCHAR2(32000) := NULL;
p3_ VARCHAR2(32000) := NULL;
attr_ VARCHAR2(32000) := NULL;
begin
p0_ := '&NEW:ORDER_NO';
p1_ := '&NEW:RELEASE_NO';
p2_ := '&NEW:SEQUENCE_NO';
p3_ := 'PARK_REASON'||chr(31)||'QUALITY'||chr(30)||'PARK_MRB_NO'||chr(31)||''||chr(30)||'PARK_CHANGE_ORD_NO'||chr(31)||''||chr(30)||'PARK_ORG_CODE'||chr(31)||''||chr(30)||'PARK_PERSON_ID'||chr(31)|| '#USER_ID#'||chr(30)||'PARK_LOCATION_NO'||chr(31)||''||chr(30)||'PARK_NOTE'||chr(31)||'Nota'||chr(30);
Client_SYS.Clear_Attr(attr_);
CLIENT_SYS.ADD_TO_ATTR('ORDER_NO_', p0_, attr_);
CLIENT_SYS.ADD_TO_ATTR('RELEASE_NO_', p1_, attr_);
CLIENT_SYS.ADD_TO_ATTR('SEQUENCE_NO_', p2_, attr_);
CLIENT_SYS.ADD_TO_ATTR('PARK_ATTR_', p3_, attr_);
transaction_sys.deferred_call('Shop_Ord_API.Park','PARAMETER',attr_,'Park Order No '|| p0_,sysdate);
end;
System is raising the following error:
Field ePARK_REASON] in Shop Order has an invalid value format. The value is: "QUALITYSTATE_BEFORE_PARKStarted". ORA-20124: ShopOrd.FORMAT2
I think I am doing something wrong when I am including all the parameters into “attr_” variable. Instead to take “p3_” variable as a set of attributes, system is taking them as anothers.
Could you please help me to figure out how to perform this background action?
Thanks in advance,
JuanFran
How are you starting this?
All of the attributes and values you have in p3 should be included in the attribute string e.g.
Client_Sys.add_to_attr('PARK_REASON','QUALITY',attr_);
It's a little hard to read and answer on my phone, but if you haven't got it up and running by tomorrow I'll have a look when I'm in front of a computer.
Hi anmise,
Thanks for your reply.
From one side, if I directly include the attributes and values:
begin
p0_ := '&NEW:ORDER_NO';
p1_ := '&NEW:RELEASE_NO';
p2_ := '&NEW:SEQUENCE_NO';
Client_SYS.Clear_Attr(attr_);
CLIENT_SYS.ADD_TO_ATTR('ORDER_NO_', p0_, attr_);
CLIENT_SYS.ADD_TO_ATTR('RELEASE_NO_', p1_, attr_);
CLIENT_SYS.ADD_TO_ATTR('SEQUENCE_NO_', p2_, attr_);
CLIENT_SYS.ADD_TO_ATTR('PARK_REASON', 'QUALITY',attr_);
CLIENT_SYS.ADD_TO_ATTR('PARK_PERSON_ID', '#USER_ID#', attr_);
CLIENT_SYS.ADD_TO_ATTR('PARK_NOTE', 'Nota', attr_);
transaction_sys.deferred_call('Shop_Ord_API.Park','PARAMETER',attr_,'Park Order No '|| p0_,sysdate);
System is raising the following error:
Argument PARK_ATTR_ must have a value, because default value don't exist. ORA-20105: Transaction.NO_DEFAULT_VALUE
So, I need to fill ‘PARK_ATTR’ argument in the procedure Park (order_no_, release_no_, sequence_no_, park_attr_).
On the other hand, based on your approach, I have included the park attributes using add_to_attr sentence:
begin
p0_ := '&NEW:ORDER_NO';
p1_ := '&NEW:RELEASE_NO';
p2_ := '&NEW:SEQUENCE_NO';
Client_SYS.Clear_Attr(p3_);
CLIENT_SYS.ADD_TO_ATTR('PARK_REASON', 'QUALITY',p3_);
CLIENT_SYS.ADD_TO_ATTR('PARK_LOCATION_NO', '', p3_);
CLIENT_SYS.ADD_TO_ATTR('PARK_NOTE', 'Nota', p3_);
Client_SYS.Clear_Attr(attr_);
CLIENT_SYS.ADD_TO_ATTR('ORDER_NO_', p0_, attr_);
CLIENT_SYS.ADD_TO_ATTR('RELEASE_NO_', p1_, attr_);
CLIENT_SYS.ADD_TO_ATTR('SEQUENCE_NO_', p2_, attr_);
CLIENT_SYS.ADD_TO_ATTR('PARK_ATTR_', p3_, attr_);
transaction_sys.deferred_call('Shop_Ord_API.Park','PARAMETER',attr_,'Park Order No '|| p0_,sysdate);
end;
And I get the original error:
Field lPARK_REASON] in Shop Order has an invalid value format. The value is: "QUALITYSTATE_BEFORE_PARKStarted". ORA-20124: ShopOrd.FORMAT2
It’s strange, because if I execute the call directly with this arguments approach (Shop_Ord_API.Park) in a custom menu, it works.
So it seems I am not sending properly the attributes to the deferred_call procedure.
Had a look and I’m not sure that the Shop_Ord_API.Park procedure will work to post as a deferred call. Tried running the below,
DECLARE
order_no_ VARCHAR2(32000) := '&NEW:ORDER_NO';
release_no_ VARCHAR2(32000) := '&NEW:RELEASE_NO';
sequence_no_ VARCHAR2(32000) :='&NEW:SEQUENCE_NO';
park_attr_ VARCHAR2(32000);
BEGIN
Client_SYS.Clear_Attr(park_attr_);
Client_SYS.Add_To_Attr( 'ORDER_NO',order_no_ , park_attr_);
Client_SYS.Add_To_Attr( 'REALEASE_NO', release_no_, park_attr_);
Client_SYS.Add_To_Attr( 'SEQUENCE_NO', sequence_no_, park_attr_);
Client_SYS.Add_To_Attr( 'PARK_REASON', 'QUALITY', park_attr_);
Client_SYS.Add_To_Attr( 'PARK_MRB_NO', '', park_attr_);
Client_SYS.Add_To_Attr( 'PARK_CHANGE_ORD_NO', '', park_attr_);
Client_SYS.Add_To_Attr( 'PARK_ORG_CODE', '', park_attr_);
Client_SYS.Add_To_Attr( 'PARK_PERSON_ID', '', park_attr_);
Client_SYS.Add_To_Attr( 'PARK_LOCATION_NO', '', park_attr_);
Client_SYS.Add_To_Attr( 'PARK_NOTE', 'Deferred Call', park_attr_);
transaction_sys.deferred_call('Shop_Ord_API.Park','PARAMETER',park_attr_,'Park Order No '||order_no_||sysdate);
END;
But it fails due to missing arguments. Not sure if it works when the input requires an attribute string as well as IDs.
Argument ORDER_NO_ must have a value, because default value do not exist. ORA-20105: Transaction.NO_DEFAULT_VALUE
Hopefully someone else can shed some light on this.
In the meantime, maybe you could explain how/when this should be triggered and we can find a work around.
Hi Anmise, the problem you’re geeting is due to the name od the agument(ORDER_NO_ istead ORDER_NO).
This must to be triggered when a shop order operation in a specific work center is closed and the analysis results are of the SO row material+ analysis result of the SO were out of specification.
Do you see any work around?
Thanks,
Hi Anmise, the problem you’re geeting is due to the name od the agument(ORDER_NO_ istead ORDER_NO).
This must to be triggered when a shop order operation in a specific work center is closed and the analysis results are of the SO row material+ analysis result of the SO were out of specification.
Do you see any work around?
Thanks,
I wrote a small custom event based on a custom field on the shop order header to trigger the park action. If you could add some more logic to the event to tick the ‘Park’ checkbox based on your requirements, maybe you could get around it. By using a custom field you’ll hopefully get around the mutating table.
DECLARE
info_ VARCHAR2(32000);
park_attr_ VARCHAR2(32000);
CURSOR get IS
select objid, objversion FROM shop_ord WHERE objkey = '&NEW:ROWKEY';
BEGIN
IF '&NEW:CF$_PARK'='TRUE' THEN
FOR rec_ IN get LOOP
Client_SYS.Clear_Attr(park_attr_);
Client_SYS.Add_To_Attr('PARK_REASON', 'QUALITY', park_attr_);
Client_SYS.Add_To_Attr('PARK_MRB_NO', '', park_attr_);
Client_SYS.Add_To_Attr('PARK_CHANGE_ORD_NO', '', park_attr_);
Client_SYS.Add_To_Attr('PARK_ORG_CODE', '', park_attr_);
Client_SYS.Add_To_Attr('PARK_PERSON_ID', '', park_attr_);
Client_SYS.Add_To_Attr('PARK_LOCATION_NO', '', park_attr_);
Client_SYS.Add_To_Attr('PARK_NOTE', 'Deferred Call', park_attr_);
IFSAPP.Shop_Ord_API.Park__(info_, rec_.objid, rec_.objversion, park_attr_, 'DO');
END LOOP;
END IF;
END;
I see, but the transaction is finished when the last cust event is finished so the mutation issue still persists 
Thanks for your time on this.
I see, but the transaction is finished when the last cust event is finished so the mutation issue still persists
Thanks for your time on this.
Not sure I 100% understand what you meant. Can you specify the requirements in some more detail and I'll see if I can whip something up.
Hi anmise,
I understand know what you meant, I create a custom field, I update the custom field via deferred_call, and the custom event is parking the SO when the custom field is updated. That works!!
Thanks a lot for your help.
