BPEL: Purging Instances-1

INTRODUCTION:
This procedure purges instance for a particular domain,for a particular state and for particular specified period from the BPEL dehydration store.

Note: This procedure is calling another procedure 'delete_ci'.
Procedure 'delete_ci' deletes a cube instance and all rows
in other Collaxa tables that reference the cube instance.
***********************************************************************
Guideline for running the code:
1. Pass the date fields in oracle standard format i.e DD-MON-RR format
2. Pass the state code depending upon the requirement.

STATE CODE----------STATE NAME
0----------------------Initiated state
1----------------------Open,Running state
2----------------------Open, Suspended state
3----------------------Open,Faulted state
4----------------------Closed,Pending-Cancel state
5----------------------Closed,Completed state
6----------------------Closed,faulted state
7----------------------Closed,Cancelled state
8----------------------Closed,aborted state
9----------------------Closed,Stale state
***********************************************************************
CODE FOR PURGING:
CREATE OR REPLACE PROCEDURE sp_purge_instances_per_state (
p_domain_name IN VARCHAR2,
p_state IN NUMBER,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2
)
/*
CODING DONE BY: ABHISHEK SAURABH
CREATION DATE: 12-OCTOBER-2007
LAST MODIFIED: 18-OCTOBER-2007

Note:
1. Don't call this procedure until you are too sure of deleting the instances.
2. First stop the BPEL instance via Enterprise manager then run this procedure.
*/

IS
CURSOR c (
v_domain_name IN VARCHAR2,
v_state IN NUMBER,
v_from_date IN VARCHAR2,
v_to_date IN VARCHAR2
)
IS
SELECT cie.cikey cikey, dmn.domain_id domain_id,
cie.state state,cie.creation_date creation_date, cie.domain_ref domain_ref
FROM cube_instance cie, domain dmn
WHERE cie.domain_ref = dmn.domain_ref
---- the name of the domain
AND upper(dmn.domain_id)=upper(v_domain_name)
AND cie.creation_date between v_from_date and v_to_date
AND cie.state=v_state;
BEGIN
FOR r IN c (p_domain_name, p_state, p_from_date,p_to_date)
LOOP
DBMS_OUTPUT.put_line ( 'State code '
|| r.state
|| ' created on '
|| TO_CHAR (r.creation_date,
'YYYY-MM-DD HH24:MI:SS')
|| ' is purged.'
);
collaxa.delete_ci (r.cikey);
-- also workflow tasks --
DELETE FROM wftask wfn
WHERE wfn.instanceid = r.cikey;
END LOOP;
-- invoked messages
DELETE FROM invoke_message ime
WHERE ime.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND ime.state > 1
AND ime.receive_date between p_from_date and p_to_date;
DBMS_OUTPUT.put_line ('-> #invoke msg ' || SQL%ROWCOUNT);
-- callback messages
DELETE FROM dlv_message dme
WHERE dme.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND dme.state > 1
AND dme.receive_date between p_from_date and p_to_date;
DBMS_OUTPUT.put_line ('-> #callback msg ' || SQL%ROWCOUNT);
commit;
END sp_purge_instances_per_state;


Happy Learnings....