Question

Calling a custom procedure with a returned value

  • 10 April 2024
  • 4 replies
  • 30 views
R
Rank
Userlevel 2
Badge +9

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

4 replies

EntShehaM
Rank
Userlevel 3
Badge +5

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

Shehan Mendis
R
Rank
Userlevel 2
Badge +9

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?

EntShehaM
Rank
Userlevel 3
Badge +5

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

Shehan Mendis
T
Rank
Userlevel 7
Badge +19

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