Query for Closed Cases!!

To query the closed cases details in iProcess Engine database - You can run the following sql statement :

Note : If Is_Dead=0 then case is active and if Is_Dead=1 then case is closed.

To Reterive the closed cases:

set pages 1000 lines 100 term off feed off trims on
select pi.proc_name "Procedure Name"
, trunc(ci.next_deadline) "Closed Date"
, count(1) "Count"
from case_information ci
left join proc_index pi
on ( ci.proc_id = pi.proc_id )
where ci.is_dead = 1
and ci.is_subcase = 0
group by pi.proc_name, trunc(ci.next_deadline)
order by trunc(ci.next_deadline);

To Reterive the active cases:

set pages 1000 lines 100 term off feed off trims on
select pi.proc_name "Procedure Name"
, trunc(ci.next_deadline) "Closed Date"
, count(1) "Count"
from case_information ci
left join proc_index pi
on ( ci.proc_id = pi.proc_id )
where ci.is_dead = 0
and ci.is_subcase = 0
group by pi.proc_name, trunc(ci.next_deadline)
order by trunc(ci.next_deadline);

Maintenance Jobs for IPEngine

To make iProcess Engine stable - We should regularly perform the Maintenance jobs:

1. Regularly Deleteing the Old procedure versions (Recommended to keep last 4 versions of procedures)
2. Purging the Closed cases on Periodically basis
3. Regular Index Rebuilding for iProcess Engine database
4. DBMS STATS Gather on database Schemas
5. Archiving the logs from $SWDIR/logs
6. Taking the Regular backups of IPE Engines
7. Weekly Restart of IPEngine

Many more....

Message Jump!!

To Make any application/case jump in iProcess Engine, Please follow the below steps:

Close the Procedure Name:
$SWDIR/bin/swutil CLOSE {Procedure Name} {Case Number}-y

Move the Case to StepName:
$SWDIR/bin/swutil EVENT {Procedure Name} R{Case Nummbert} {Step Name}

Messages jammed in BGQueue!!

There is a typical scenario that when messages are not getting moved from the Background queue and you can see that message count is getting increased.

Queue ID : 1
$SWSIR/util/swadm count_messages 1
Queue ID : 2
$SWSIR/util/swadm count_messages 2

Please follow the steps given below :

1. Stop the iProcess Engine
2. Run the following query on IPE Database - Once this is completed.
3. Start iProcess Engine.

SQL Query:

DECLARE

dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW (16);
MESSAGE swlocalmsg;
STATUS VARCHAR2 (10);
enqueue_options DBMS_AQ.enqueue_options_t;

-- Build cursor of exception messages, from both queues
CURSOR c1 IS
SELECT msgid, user_data, enq_time, q_name FROM BGMBOXTABLE1 WHERE state = 0
UNION ALL
SELECT msgid, user_data, enq_time, q_name FROM BGMBOXTABLE2 WHERE state = 0;

xt swlocalmsg;
v_head VARCHAR2(20);
v_user VARCHAR2(20);
v_inst VARCHAR2(20);
v_in INTEGER;
v_io INTEGER;

BEGIN
-- enable dequeue for exception queues
--DBMS_AQADM.START_QUEUE ('aq$_bgmboxtable1_e', FALSE, TRUE);
--DBMS_AQADM.START_QUEUE ('aq$_bgmboxtable2_e', FALSE, TRUE);

-- Open exception cursor
FOR x_c1 IN c1 LOOP

xt := x_c1.user_data;

-- ################
-- Breakdown the message
-- ################
v_in := INSTR(xt.msginfo,'^');
v_io := v_in;

v_head := SUBSTR(xt.msginfo,1,(v_in - 1));
v_in := INSTR(xt.msginfo,'^',(v_in + 1));
v_user := SUBSTR(xt.msginfo, (v_io + 1), ((v_in - v_io) - 1));

v_io := v_in;
v_in := INSTR(xt.msginfo,'^',(v_in + 1));
v_inst := SUBSTR(xt.msginfo, (v_io + 1), ((v_in - v_io) - 1));


IF v_inst = 'PROCCASE' THEN
dequeue_options.msgid := x_c1.msgid;
dbms_output.put_line( v_inst||' '||dequeue_options.msgid);
dBMS_AQ.dequeue (queue_name => x_c1.q_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => MESSAGE,
msgid => message_handle
);
END IF;
END LOOP;
COMMIT;
END;
/