hello Nancy

i don’t believe there’s any way to do this with an application server task.

You need a query on the background job table, something like this:

(SELECT D.*

FROM IFSAPP.DEFERRED_JOB D

WHERE UPPER(STATE) = 'EXECUTING'

AND STARTED < SYSDATE - (2.5/24))

That will return the set of jobs you are looking for.

Question then is how you want to run it.  A fairly easy approach, currently in Apps 10 (using Enterprise Explorer) is to create a FNDMIG migration job based on the Command_sys.mail method.  Use the above query as a data source and then schedule this job to run every hour, using the functionality in the migration area.

This will email you if it finds any jobs that meet the criteria; otherwise you won’t get any email.

Instead of the above you could create some custom code and a scheduled task to do much the same thing, but i like the FNDMIG approach because it avoids deploying any custom packages.

Example Migration job, something like this:

Thank you.  I was able to create the migration job but I had to log in as app owner to do that.  I’m not seeing a way to schedule it though.  I see Scheduled Migration Jobs but I’m not seeing a way to schedule the job I created.

Never mind, I found it.  You have to RMB when looking at the details of the job.  I was trying through the list (Migration Jobs).

You can use that option and then you might want to set it to run every 1 hour

As for access as an enduser, you need to add your user here:

I added myself in the Data Migration Basic Data - User tab but I still can’t add a new Migration Job, nor can I see existing jobs - I have to login as app owner.  I even refreshed the security cache and logged out back in but no go.  If I have to do this as app owner I can live with that.  

But thanks for giving a great and thorough answer originally.  This is all set up.  We have a costing job scheduled early Saturday morning so maybe we’ll see if this actually works.  Is it wrong for me to want a job to hang so I can see if this works?  Have a great weekend!

Hmm. That should have let you create jobs. 
 

In order to see existing jobs you need to add the job to the user on the Pr User tab in basic data. 

You should be able to see the migration job running in background jobs every hour (or whatever schedule you set)

I’ll write you a better version of the query on Monday that sends just one email. As it is you will get 1 email per job

Also Re: hoping for failure…

when I want something daft to work, I find a modicum of pessimism often does the trick. 
 

So logically in this case you should try a modicum of optimism… I think. 🤨

Improvement: please use this query in the source box

(SELECT 

LISTAGG(D.JOB_ID ||'  '||D.DESCRIPTION, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY 1) AS JOBS

FROM IFSAPP.DEFERRED_JOB D

WHERE UPPER(STATE) = 'EXECUTING'

AND STARTED < SYSDATE - (2.5/24))

 Put the word JOBS in the source column for the TEXT_ attribute - this is the email body.

This will lead to a single email with a nice format like the following, in case it finds multiple jobs:

8556356  Schedule id 124 : CustomerPayments
8556357  Schedule id 489 : Touch Apps cServiceEngApp 1] Scheduled Activation
8556358  Schedule id 4077 : Touch Apps uServiceEngApp 1] Reprocess Failed Transactions

Thank you so much for the info. 

One thing I found was that the LISTAGG function always returns one record even if it is null so you have to wrap it inside another select and add a where to avoid sending out a blank email every time the job is ran.

(SELECT JOBS FROM

(SELECT LISTAGG(D.JOB_ID ||' - '|| D.DESCRIPTION, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY 1) AS JOBS FROM IFSAPP.DEFERRED_JOB D WHERE UPPER(STATE) ='EXECUTING' AND STARTED<SYSDATE - (2.5/24))

WHERE JOBS IS NOT NULL )
 

Hi  @CHFFORBESN,

Could you share if this setup proved useful in your company over the past years? Do you have some experiences and improvements that you could share?