Brisanje zastarelih WorkFlow poruka

Purging Obsolete Workflow Runtime Data

Oracle Workflow Procedures and Functions using API

Manuelno brisanje WorkFlow notifikacija

Primeri WorkFlow Setup ekrana

Primer slanja testne poruke

Links

Oracle WorkFlow je jedan od ranije postojecih Oracle proizvoda. Verzija se moze utvrditi npr. korisnjenjem sktipta wfver.sql. Kada je u pitanju Oracle eBS R12 on je integrisan kao deo postojece infrastrukture (u konkretnom primeru Version 2.6.0)

Purging Obsolete Workflow Runtime Data

Generalno brisanje (purging) zastarelih poruka u Oracle eBS R12 se vrsi Concurent programom Purge Obsolete Workflow Runtime Data. Npr. “Your request for Purge Obsolete Workflow Runtime Data has been scheduled. The Request ID is 5938157”.

Oracle Workflow Procedures and Functions using API

Medjutim u mnogim testnim okruzenjima potrebno je obristi poruke koje se nagomilaju u tabelama za notifikaciju korisnika pre nego sto se one stvarno posalju korisniku. Postoji set paketa koji omogucava rad koristeci PL/SQL (Oracle Workflow Procedures and Functions). U primeru koji sledi je data procedura sa paketom wf_notification.close.

declare

cursor c_notif is

select notification_id

from wf_notifications

where status = ‘OPEN’;

begin

for n in c_notif loop

wf_notification.close(n.notification_id,’SYSADMIN’);

end loop;

commit;

end;

Medjutim prilikom izvrsavanja procedure javlja se exception sa porukom koji potvrdjuje da se notifikacije ne mogu zatvoriti bez odgovora na njih npr.:

(ORA-20002: 3209: You may not close Notification 'Salary Change Request For Natalie Kahn Requires Your Approval' because it requires a response. Use the "Find in page" function within the browser to locate and de-select this Notification. You may then again attempt to close the selected Notifications. ORA-06512: at "APPS.WF_NOTIFICATION", line 5440 ORA-06512: at line 9)

Manuelno brisanje WorkFlow notifikacija

Dakle, sledi primer manuelnog zatvaranja notifikacija. Da prebrojimo notifikacije koje nisu danasnje a predvidjene su za mail, ili je slanje ovih notifikacija bilo neuspesno:

Select count(*),message_type from wf_notifications WHERE

TRUNC(begin_date) < TRUNC(SYSDATE)

AND mail_status IN ('MAIL', 'FAILED')

group by message_type

order by count(*) desc

COUNT(*) MESSAGE_

---------- --------

1330 WFERROR

115 POAPPRV

56 OTWF

26 OKSKPRCS

11 PAYUSPWF

8 IBC_WF

6 OKCAUKAP

5 HRSSA

2 OENH

1 FNDCMMSG

1 OKCTPAPP

1 PQHGEN

12 rows selected.

Da prebrojimo npr. One tipa WFERROR

Select count(*),message_type from wf_notifications

WHERE message_type = 'WFERROR' and

TRUNC(begin_date) < TRUNC(SYSDATE)

AND mail_status IN ('MAIL', 'FAILED')

group by message_type

order by count(*) desc

COUNT(*) MESSAGE_

---------- --------

1330 WFERROR

Dakle u sledecem primeru menja se mail_status notifikacija tipa WFERROR i POAPPRV u SENT kao da su poslate:

UPDATE wf_notifications

SET mail_status = 'SENT'

WHERE message_type = 'WFERROR'

AND TRUNC(begin_date) < TRUNC(SYSDATE)

AND mail_status IN ('MAIL', 'FAILED');

UPDATE wf_notifications

SET mail_status = 'SENT'

WHERE message_type = 'POAPPRV'

AND TRUNC(begin_date) < TRUNC(SYSDATE)

AND mail_status IN ('MAIL', 'FAILED');

Ostaje da se proveri da li su neke od notifikacija vec u redu za slanje. U narednom primeru su to samo notifikacije tipa WFERROR:

select distinct wfno.corr_id

FROM applsys.aq$wf_notification_out wfno

WHERE wfno.corr_id LIKE 'APPS:WFERROR%'

AND TRUNC(wfno.enq_time) < TRUNC(SYSDATE)

AND wfno.msg_state = 'READY';

CORR_ID

--------------------------------------------------------------------------------

APPS:WFERROR:RESET_ERROR_MESSAGE

Ostaje da se ove notifikacije npr. WFERROR izbrisu iz reda za slanje (dequeuing).

DECLARE

q_version VARCHAR2(200);

event sys.aq$_jms_text_message;

dequeue_options dbms_aq.dequeue_options_t;

message_properties dbms_aq.message_properties_t;

TYPE msgidtab IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;

msg_id msgidtab;

dq_msg_id RAW(16);

q_name VARCHAR2(60);

CURSOR target_msgids IS

SELECT wfno.msg_id

FROM applsys.aq$wf_notification_out wfno

WHERE wfno.corr_id LIKE 'APPS:WFERROR:%'

AND TRUNC(wfno.enq_time) < TRUNC(SYSDATE)

AND wfno.msg_state = 'READY' ;

BEGIN

q_name := 'APPLSYS.WF_NOTIFICATION_OUT';

dequeue_options.dequeue_mode := dbms_aq.remove_nodata;

dequeue_options.wait := dbms_aq.no_wait;

dequeue_options.navigation := dbms_aq.first_message;

dequeue_options.consumer_name := 'WF_NOTIFICATION_OUT';

OPEN target_msgids;

FETCH target_msgids BULK COLLECT

INTO msg_id;

CLOSE target_msgids;

dbms_output.put_line('Dequeue of '||msg_id.COUNT||' messages.');

IF (msg_id.COUNT > 0) THEN

FOR i IN msg_id.FIRST .. msg_id.LAST LOOP dequeue_options.msgid := msg_id(i);

dbms_aq.dequeue(queue_name => q_name

,dequeue_options => dequeue_options

,message_properties => message_properties

,payload => event

,msgid => dq_msg_id);

dequeue_options.navigation := dbms_aq.next_message;

END LOOP;

COMMIT;

END IF;

EXCEPTION

--timeout will fall to here

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

IF (target_msgids%ISOPEN) THEN

CLOSE target_msgids;

END IF;

END;

http://robertjungerius.blogspot.com/2010/07/making-sure-some-workflow-notifications.html