Hi Iskender.

You might want to review the following documentation:

https://docs.ifs.com/techdocs/foundation1/040_administration/240_integration/300_ifs_connect/080_message_archive/default.htm

System administrators can use message archiving functionality to move processed and older application messages to Message Archive for future reference. This might very well be the reason why you see Application Messages disappear after one day or less.

Have a look at the Database Schedule and its parameters:

You will probably want to change the value for parameter HOURS_OLD_ to something greater that 24 (hours)

Please have a look and let us know if that resolves the problem.

Best regards.
Gonzalo.

Also - In IFS Cloud there is a Database Task for cleaning messages “Cleanup Application Message Queues”

Dear @Gonzalo 

dear @hhanse 

thank you both for your response.

I will test it and get back to you.

Hi guys,

the database task “Cleanup Applications Message Queues” seems to be the right one.

We have at least two database tasks.

One of them has the parameter “Any”:

Does it mean that all status are affacted?

I need to exclude “Finished” because there is already one for this status.

Does anyone have the synax for LIST_OF_QUEUES to exclude a single queue?

I have one queue set for 28 days but want 3 days on all other queues. I’ve tried all ways I can think of.

Any suggestions appreciated!

@Link by looking at the code I can say that using ANY for LIST_OF_STATES_ will consider all statuses for deletion. Below is the SQL cursor identifying such records. It doesn’t filter by status. It just considers the Queues and Limit of time provided.

CURSOR c_message_ids_any_state_ (queues_ VARCHAR2,
                                    limit_  NUMBER) IS
      SELECT a.application_message_id,
            a.state_date,
             a.rowid
      FROM   fndcn_application_message_tab a
      WHERE  (a.queue IN (SELECT REGEXP_SUBSTR(queues_, ',^,;]+', 1, LEVEL)
                          FROM   dual
                          CONNECT BY REGEXP_SUBSTR(queues_, ',^,;]+', 1, LEVEL) IS NOT NULL)
         OR   a.queue LIKE queues_)
      AND    (a.tag IS NULL OR a.tag <> 'BATCH')
      AND    a.state_date < SYSDATE - limit_/86400;

@Matthew pls see above the code that is executed when you wanna delete App Messages in ANY state. Regarding inclusion of multiple queues in the LIST_OF_QUEUES parameter, you could just separate the queues with semicolon and the REGEX should be able to split the correctly.

See below an example for LIST_OF_QUEUES = NOTIFICATIONS;OUT1;ERROR

SELECT REGEXP_SUBSTR('NOTIFICATIONS;OUT1;ERROR', ' ^,;]+', 1, LEVEL)
FROM   dual
CONNECT BY REGEXP_SUBSTR('NOTIFICATIONS;OUT1;ERROR', ',^,;]+', 1, LEVEL) IS NOT NULL

CURSOR c_message_ids_any_state_ (queues_ VARCHAR2,
                                    limit_  NUMBER) IS
      SELECT a.application_message_id,
            a.state_date,
             a.rowid
      FROM   fndcn_application_message_tab a
      WHERE  (a.queue IN (SELECT REGEXP_SUBSTR(queues_, ',^,;]+', 1, LEVEL)
                          FROM   dual
                          CONNECT BY REGEXP_SUBSTR(queues_, ',^,;]+', 1, LEVEL) IS NOT NULL)
         OR   a.queue LIKE queues_)
      AND    (a.tag IS NULL OR a.tag <> 'BATCH')
      AND    a.state_date < SYSDATE - limit_/86400;

Dear @Marcel.Ausan 

thank you a lot for the investigation.