Skip to main content

Hello.

On an sql treatment, I had to update a custom procedure to return a value, for that, I added a new parameter with the “out” key, so the new signature is :

PROCEDURE SFD_EV_SC_053(

p_order_no_ varchar2,

p_line_no_ varchar2,

p_rel_no_ varchar2,

p_line_item_no_ varchar2,

p_shipment_id out float

) 


On an old event, this same procedure is called but in a deferred way, so the code is :

 Client_SYS.Clear_Attr(attr_);

  Client_SYS.Add_To_Attr('P_ORDER_NO_', '&NEW:ORDER_NO' , attr_);

  Client_SYS.Add_To_Attr('P_LINE_NO_', '&NEW:LINE_NO' , attr_);

  Client_SYS.Add_To_Attr('P_REL_NO_', '&NEW:REL_NO' , attr_);

  Client_SYS.Add_To_Attr('P_LINE_ITEM_NO_', '&NEW:LINE_ITEM_NO' , attr_);

  Client_SYS.Add_To_Attr('P_SHIPMENT_ID', v_shipment_id , attr_);

  transaction_sys.deferred_call('MYPACKAGE_API.SFD_EV_SC_053','PARAMETER',attr_ ,'#SQL Treatment');

 

But it seems that PL/SQL doesn’t support the call of such a procedure in a deferred way, or maybe the call must be different. The error code that I’m getting is “ORA-20105: Transaction.WRONG_ARGUMENT”.

What’s the way to fix this issue? or maybe I just have to duplicate the procedure to support deferred calls?

Thanks

Hi 

Find the below corrections in passing parameters and calling out in deferred calls,

First change the procedure as below,

PROCEDURE SFD_EV_SC_053(attr_ VARCHAR2)

Assigning values to Attr_ as below,

Client_SYS.Add_To_Attr('P_ORDER_NO', '&NEW:ORDER_NO' , attr_);
Client_SYS.Add_To_Attr('P_LINE_NO', '&NEW:LINE_NO' , attr_);
Client_SYS.Add_To_Attr('P_REL_NO', '&NEW:REL_NO' , attr_);
Client_SYS.Add_To_Attr('P_LINE_ITEM_NO', '&NEW:LINE_ITEM_NO' , attr_);
Client_SYS.Add_To_Attr('P_SHIPMENT_ID', v_shipment_id , attr_);
transaction_sys.deferred_call('MYPACKAGE_API.SFD_EV_SC_053','PARAMETER',attr_ ,'#SQL Treatment');

Inside the stored procedure declare variables for above input parameters, then extract the value from attr_ something like below,

DECLARE

p_order_no_             VARCHAR2(100);

p_line_no_                VARCHAR2(100);

p_rel_no_                  VARCHAR2(100);

p_line_item_            VARCHAR2(100);

p_shipment_id_       FLOAT;

 

BEGIN


p_order_no_           := Client_SYS.Get_Item_Value('P_ORDER_NO', attr_);
p_line_no_              := Client_SYS.Get_Item_Value('P_LINE_NO’, attr_);
p_rel_no_                := Client_SYS.Get_Item_Value('P_REL_NO', attr_);
p_line_item_          := Client_SYS.Get_Item_Value('P_LINE_ITEM_NO', attr_);
p_shipment_id_    := Client_SYS.Get_Item_Value('P_SHIPMENT_ID', attr_);

 

This should pretty much address your issue.

Thanks, and Best Regards,
Shehan

Hello @EntShehaM 

I didn’t know this synthax and sounds interesting, but I still have a question : in the case in which I need to get the value of P_SHIPMENT_ID in a direct call, is that possible to get it?

Hi @romsar 

I’m not 100% clear of your requirement, assume you are trying to get the Customer Order connected Shipment data. As per the understanding, you are in need of getting shipment ID using a function. In standard I don’t think we have a method to retrieve data by passing CO no, line no, rel no and line item no. You can retrieve backwards though, by passing shipment id and line no there’s a possibility of getting the connected customer order info,

SHIPMENT_LINE_API.Get_Source_Ref1(shipment_id_ => , shipment_line_no_ => );

Either you can get the info using a cursor or may have to write a custom logic.

Hope this is what you mean by direct call. 

Let me know if this helps what you looking for.

 

Thanks, and Best Regards,
Shehan

If you do not want to change the parameters you could use a PLSQL block to send into the deferred job in the event instead. Something like this:
 

DECLARE
   attr_                VARCHAR2(32000);
   sql_msg_             VARCHAR2(32000);
   stmt_                VARCHAR2(32000);
BEGIN
   stmt_ :=
'DECLARE
    shipment_id_  NUMBER;
 BEGIN
   MYPACKAGE_API.SFD_EV_SC_053(''&NEW:ORDER_NO'',''&NEW:LINE_NO'',''&NEW:REL_NO'',''&NEW:LINE_ITEM_NO'', shipment_id_);
 END;';

   sql_msg_ := Message_SYS.Construct('CALL_SFD');
   Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
   Client_SYS.Add_To_Attr('MSG_', '', attr_);

   Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_,
                                 Language_SYS.Translate_Constant('Event', 'DESCRIPTION: #SQL Treatment' ));
END;

 

Reply


Terms & ConditionsCookie settings