Skip to main content

I’m trying to check the override Ship Via Code on Customer Order lines.

Because Ship Via is hidden away on a RMB you can’t check it when the order line is added so I am forced to check all lines in one go when the order is released.

So I’ve created an Event on CUSTOMER_ORDER_TAB and an associated custom event action.

What I am trying to do is 

  1. Check all of the Order lines on the order have valid ship to codes.
  2. Report each line with an error as a Streams message to the user who released the order
  3. Stop the Order Release

Here’s my code

declare

  attr_ varchar2(4000);

  cursor get_order_line_ is
    select catalog_no
      from customer_order_line
     where order_no = '&NEW:ORDER_NO'
       and sales_part_api.Get_Catalog_Group(contract, catalog_no) IN
           ('06J', '603G')
       and ship_via_code <> '700';

begin

  for rec_ in get_order_line_ loop

    client_sys.Clear_Attr(attr_);
    client_sys.Add_To_Attr('FROM_', 'IFSAPP', attr_);
    client_sys.Add_To_Attr('TO_NAMES_', '&USER_ID', attr_);
    client_sys.Add_To_Attr('HEADER_', ' Order &ORDER_NO has invalid Ship Via', attr_);
    client_sys.Add_To_Attr('BODY_',
                             'Order No. &ORDER_NO includes ' || rec_.catalog_no || ' that requires Temperature Controlled Shipment',
                             attr_);
     client_sys.Add_To_Attr('URL_', 'ifsapf:frmCustomerOrder?external_search=ORDER_NO%3d&ORDER_NO', attr_);
      
     Transaction_SYS.Deferred_Call('FND_STREAM_API.Create_Event_Action_Streams',
                                    'PARAMETER',
                                    attr_,
                                    'Send Streams Invalid Ship Via');
  
    Error_sys.record_general('',rec_.catalog_no ||' requires Ship Via Code 700');                                 
  
  end loop;

end;

 

It appears that I can run either the red code or the blue code not both.

i.e. I can send the streams message to the user or I can halt the release.

Calling Error_sys.record_general seems to mean the Deferred call is ignored/abandoned.

 

Is this expected behavior?

Any ideas of how to get around this one?

I should also add that the Deferred call creates a duplicate streams message so I get two messages for every order line! 

 

TIA


Andy
 

 

 

It could be that the deferred call requires the transaction to be committed, and that will not happen when you raise the error.

Not sure if you can do some tricks using an autonomous transaction here. Search this site for “mutating table” or “trigger” and you should find some articles that might contains tricks you can use here too.

 

Hi @KIMKIMANDREW 

 

Check this code. I think it’s what you want to achieve

Solutions for the mutating problem | IFS Community

 

Code there is for a more advanced problem where error should generate if posted background job has errors. But you can use the same concept. Isolate your red code inside a function with autonomous transaction and then run the error_sys. So you can take both pills :sunglasses:

Cheers!
Damith

Terms & ConditionsCookie settings