Skip to main content

Hi all

I’m trying to run an anonymous block from an event - however, I want to trigger to be weekly, not when a record is inserted/update.

My method is to create a simple Static IAL, which I schedule to run weekly. Then I created an event to trigger on IAL_OBJECT_TAB on update when RowVersion is changed - also tried other updated fields. The event action is set to run if the name of the IAL is that created above is updated. If the action is a simple email output, it runs once each time I update the IAL (Update Now button in IAL configuration). As soon as I introduce an Execute Online SQL action, it seems to run twice as I get double the output (my SQL reads records in Project data - which includes the Project Manager, in a for Loop, then sends out details to each PM individually within the For LOOP).

Any thoughts? Is this a known issue? (this is APPS9)

 

Yes, I know I could create a Package, but as it is hosted, we are reliant on IFS all the time - I want more freedom to develop this.

 

Grateful for any thoughts or other methods to achieve the required output.

 

 

Can you share the online SQL you have for review?  Might help.

If it isn’t the code itself I can only think that it might be triggering on both insert and update, thereby running twice?

Nick

Thanks Nick - I’m convinced it isn’t an insert as it only triggers on “Objects are changed”

 

If I delete the action and add a Send Email - it only sends once, but I get each message sent from the LOOP twice, suggesting it is running the triggered action twice.

 

The Event:

 

The Event Action:

 

The SQL code:

 

DECLARE

--

attachment_      VARCHAR2(2000);

subject_         VARCHAR2(2000);

cc_mail_         VARCHAR2(2000);

body_            VARCHAR2(2000);

email_           VARCHAR2(2000);

email2_          VARCHAR2(2000);

email_tim_       VARCHAR2(2000) := 'blah.blah@blahblah.co.uk';  --TEMP value to test this process

project_id_      VARCHAR2(50);

welcome_names_ VARCHAR2(2000);

--

lpad_approved  VARCHAR2(2000);

lpad_current_yr VARCHAR2(2000);

lpad_lts              VARCHAR2(2000);

lpad_lts_pct       VARCHAR2(2000);

--

CURSOR c_get_projects_affected IS

SELECT * FROM ifsinfo.BAD_PROJECT_SPEND_LAST_WK;

--  

BEGIN

 

  FOR bad_spend IN c_get_projects_affected LOOP

 

  project_id_ := bad_spend.project_id;

  email_  := NULL;

  email2_ := NULL;

  --

  email_  := bad_spend.pm_email;

  email2_ := bad_spend.prjsup_email;

 

  -- Format the numerical output:

  lpad_approved  := LPAD(TO_CHAR(bad_spend.approved_spend,'FM999,999,999,990.00' ),25);

  lpad_current_yr := LPAD(TO_CHAR(bad_spend.current_year,     'FM999,999,999,990.00' ),25);

  lpad_lts              := LPAD(to_char(bad_spend.lts,                          'FM999,999,999,990.00' ),25);

  lpad_lts_pct       := LPAD(to_char(bad_spend.lts_percentage,      'FM999,999,999,990.00' ),25);

 

  CASE WHEN bad_spend.prjsup_fname IS NOT NULL THEN welcome_names_ := bad_spend.pm_fname||' and '||bad_spend.prjsup_fname;

                       ELSE welcome_names_ := bad_spend.pm_fname;

    END CASE;

 

  subject_ :=  'Project ' || project_id_ || ' has spent money today, and the Left To Spend value is less than 10% of this year''s spend. Please action ...TBC';

  body_ := 

 

'Hi '||welcome_names_ || CHR(10) || CHR(13) ||

 

'As Project Manager and/or Project Support, you are being advised that spend occurred last week on the above project, which has less than 10% of this years budget left to spend. Please do something...'||CHR(10) || CHR(13) ||

 

'   o  Approved Spend:                              '||CHR(9)||CHR(9)|| lpad_approved|| CHR(10) ||

'   o  Current Year Approval:                     '||CHR(9)||CHR(9)|| lpad_current_yr|| CHR(10) ||

'   o  Left to Spend (LTS):                          '||CHR(9)||CHR(9)|| lpad_lts || CHR(10) ||

'   o  LTS as % of Current Year Approval: '||CHR(9)|| lpad_lts_pct ||'%'||  CHR(10) ||  CHR(13) ||CHR(10) || CHR(13) ||CHR(10) || CHR(13) ||

             

 

 

'  ** Do Not Reply ** This message has been sent from event WEEKLY_EVENT_TRIGGER';

--

  IF email_ IS NOT NULL THEN

    command_sys.mail( 'IFSAPP',email_tim_,body_,NULL,attachment_,cc_mail_,subject_);

  END IF;

  --

  END LOOP;

 

END;

As a debug test you might want to change the event code to hardcode it as a single email rather than looping based on IF email_ IS NOT NULL.  That will show you if the trigger is really happening twice or if your loop itself is actually causing the duplication.

I suspect it is not a duplicate trigger but a duplicate send from your event action code - this will show where that dupe is coming from.

Nick

Couple of things you could try…

  1. Put a counter variable in the loop and include it in the email.  This would show if the email is being generated multiple times per loop
  2. Check the base query SELECT * FROM ifsinfo.BAD_PROJECT_SPEND_LAST_WK  - make sure you’re not getting duplicates in that IAL.

It does not really sound like a good idea to abuse an IAL schedule for a trigger to send out mails regarding projects. Why don't you use a migration job with the method command_sys.mail. You can use your select statement (SELECT * FROM ifsinfo.BAD_PROJECT_SPEND_LAST_WK) as input for the migration job. Can simply schedule on a weekly basis.

If you want to use this approach;

Guessing it triggers multiple times due to the IAL object getting status updates from the background job which updates the IAL. e.g. When the IAL updates it will get the  state ‘Updating’ when finished it goes to another state. Probably if you add ROWVERSION to your mail output you will notice you will get 2 different rowversions.

You are right - I discovered, by including the status in the output, that it changes status twice when running. One to watch out for in the future!

Reply


Terms & ConditionsCookie settings