Purging Workflow Obsolete Data

Purging Workflow Data

to reduce the data growth

This document will enable to you purge the data according to the Oracle Recommended method.

You can find purge able records from the below query:

SELECT  
    COUNT (*) ALL_RECORDS,
    WF_PURGE.GETPURGEABLECOUNT (A.ITEM_TYPE) PURGEABLE_RECORDS,
    A.ITEM_TYPE,
    B.DISPLAY_NAME
FROM
    WF_ITEMS            A,
    WF_ITEM_TYPES_VL    B
WHERE
    A.ITEM_TYPE         =   B.NAME
GROUP BY
    A.ITEM_TYPE,
    B.DISPLAY_NAME;

Sample Output:

ITEM_TYPE	DISPLAY_NAME
POC_APPR	POC Approval
OEOH	OM Order Header
OEOL	OM Order Line
XDPWFSTD	SFM Standard
HXCEMP	OTL Workflows for Employees
POERROR	PO Approval Error
OAM_BE	OAM Business Exception
HRSFL	HR Save For Later
WFERROR	System: Error
APINVAPR	AP Invoice Approval
UMXLHELP	UMX Login Help
REQAPPRV	Requisition
GLBATCH	Journal Batch
BOM_APPR	BOM APPROVAL
HRSSA	HR
CREATEPO	PO Create Documents
POAPPRV	PO Approval

 

    Scheduling “Purge Obsolete Workflow Runtime Data” Request:

To purge workflow obsolete data we have weekly scheduled 2 Concurrent Requests "Purge Obsolete Workflow Runtime Data" with the following settings:

Request Number:          1
Item Type        :           NULL
Item Key          :           NULL
Age                  :           3
Persistence Type:         Temporary

Request Number:          2
Item Type        :           NULL
Item Key          :           NULL
Age                  :           3
Persistence Type:         Persistent

Ref: How To Purge Obsolete Workflow Runtime Data For Applications (Doc ID 264191.1)    

  

 Developed and Scheduled a custom Request:

To purge obsolete records from WF_ITEMS table we create a customized request (Purge Obsolete WF Items - Custom – Request) based on customized procedure APPS.MNJ_PURGE_OBSOLETE_WF_ITEMS.   

      Appendix A contains the screen shots of concurrent programs registration and scripts for database objects

Ref: SR 3-12373729715 : Purging Workflow data

Appendix A

Value Set for Item Type:

Value Set Creation for Item Type:

View for Item Type value set:

CREATE VIEW APPS.XXX_GET_WF_PURGEABLE_ITEMS_V

AS

SELECT  

            COUNT (*) ALL_RECORDS,

            WF_PURGE.GETPURGEABLECOUNT (A.ITEM_TYPE) PURGEABLE_RECORDS,

            A.ITEM_TYPE,

            B.DISPLAY_NAME

FROM

            WF_ITEMS            A,

            WF_ITEM_TYPES_VL    B

WHERE

            A.ITEM_TYPE         =   B.NAME

GROUP BY

            A.ITEM_TYPE,

            B.DISPLAY_NAME;

Concurrent Request Registration:

Executable Registration for Purge Obsolete WF Items – Custom – Request:

Program Purge Obsolete WF Items – Custom – Request registration:

Parameters Creation:

Running the Request:

Procedure for purging the data from WF_ITEMS table:

CREATE OR REPLACE PROCEDURE APPS.XXX_PURGE_OBSOLETE_WF_ITEMS

(ERRBUF  OUT VARCHAR2, RETCODE OUT VARCHAR2, P_ITEM_TYPE VARCHAR2, P_AGE NUMBER , P_COMMIT NUMBER) AS

/***********************************************************************

    Name            :   Purge Obsolete WF Items - Custom - Program

    Description     :   Use to purge obsolete data from table WF_ITEMS

   

    Created By      :   Muhammad Burhan

    Creation Date   :   1-Apr-2016

   

    Last Update By  :   

    Last Update Date:    

    Change Log       :  

   

************************************************************************/

V_RECORDS NUMBER;

CURSOR CUR IS

SELECT

    ROWNUM  ROW_NUMBER,

    ITEM_TYPE,

    ITEM_KEY

FROM

    WF_ITEMS

WHERE

    ITEM_TYPE   =   P_ITEM_TYPE

AND END_DATE    <=  TRUNC(SYSDATE) - NVL(P_AGE,0);

BEGIN

    V_RECORDS := 0;

   

   

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,CHR(10) || 'Purging Items for '|| P_ITEM_TYPE || ' where Date less than or equal to ' || TO_CHAR(TRUNC(SYSDATE) - NVL(P_AGE,0),'DD-MON-YYYY') || CHR(10) );

   

    FOR R IN CUR LOOP

        V_RECORDS   :=  V_RECORDS + 1;

       

        WF_PURGE.ITEMS(R.ITEM_TYPE,R.ITEM_KEY,SYSDATE,TRUE,TRUE);

            

       

        IF MOD(R.ROW_NUMBER,NVL(P_COMMIT,500)) = 0   THEN

       

            COMMIT;

           

            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Commit at Record Number : '|| R.ROW_NUMBER);

     

        END IF;    

           

    END LOOP;

   

    COMMIT;

   

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,CHR(10) || 'Total Processed Record(s) : ' || V_RECORDS );

Exception When Others Then

    DBMS_OUTPUT.PUT_LINE('Error Occurred: '|| SQLERRM);

    ROLLBACK;   

   

END;