Solved

Deferred Call with several parameters

  • 12 February 2020
  • 8 replies
  • 2242 views
J
Rank
Userlevel 1
Badge +3

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 [PARK_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

icon

Best answer by anmise 13 February 2020, 12:49

View original

8 replies

A
Rank
Userlevel 7

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 [PARK_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. 

 

 

J
Rank
Userlevel 1
Badge +3

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 [PARK_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.

A
Rank
Userlevel 7

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. 
 

J
Rank
Userlevel 1
Badge +3

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,

A
Rank
Userlevel 7

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;

 

J
Rank
Userlevel 1
Badge +3

I see, but the transaction is finished when the last cust event is finished so the mutation issue still persists :disappointed_relieved:

 

Thanks for your time on this.

A
Rank
Userlevel 7

I see, but the transaction is finished when the last cust event is finished so the mutation issue still persists :disappointed_relieved:

 

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. 

J
Rank
Userlevel 1
Badge +3

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.

Reply


Terms & ConditionsCookie settings