Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wfprgs.pls
REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=pls \ REM dbdrv: checkfile:~PROD:~PATH:~FILE /*=======================================================================+ | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA| | All rights reserved. | +=======================================================================+ | FILENAME | wfprgs.pls | | DESCRIPTION | PL/SQL spec for package: WF_PURGE | | NOTES | Routines to purge obsolete runtime data | | KNOWN PROBLEM(s) | MODIFIED (MM/DD/YY) *=======================================================================*/ SET VERIFY OFF; WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; create or replace package WF_PURGE as /* $Header: wfprgs.pls 26.20 2004/12/13 06:07:32 averma ship $ */ /*# * This public interface provides APIs that can be * called by an application program or workflow * function in the runtime phase to purge obsolete * runtime data for completed items and processes, * and to purge information for obsolete activity * versions that are no longer in use. Periodically * purging obsolete data from your system to increases * performance. * @rep:scope public * @rep:product OWF * @rep:displayname Workflow Purge * @rep:lifecycle active * @rep:compatibility S * @rep:category BUSINESS_ENTITY WF_ENGINE * @rep:category BUSINESS_ENTITY WF_NOTIFICATION * @rep:category BUSINESS_ENTITY WF_USER * @rep:ihelp FND/@wfpurge See the related online help */ -- -- Persistence Type Mode -- Setting this variable to 'PERM' or 'TEMP' will affect the purging -- routines to purge different persistence type objects. -- persistence_type varchar2(8) := 'TEMP'; -- -- Commit Frequency: Default - commit every 500 records. -- This variable can be changed as needed to control rollback segment -- growth against performance. -- commit_frequency number := 500; -- procedure Move_To_History -- Move wf_item_activity_status rows for particular itemtype/key from -- main table to history table. -- IN: -- itemtype - Item type to move, or null for all itemtypes -- itemkey - Item key to move, or null for all itemkeys -- procedure Move_To_History( itemtype in varchar2 default null, itemkey in varchar2 default null); -- procedure Item_Activity_Statuses -- Delete from wf_item_activity_statuses and wf_item_activity_statuses_h -- where end_date before argument. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- itemkey - Item key to delete, or null for all itemkeys -- enddate - Date to obsolete to -- procedure Item_Activity_Statuses( itemtype in varchar2 default null, itemkey in varchar2 default null, enddate in date default sysdate); -- -- procedure Items -- Delete items with end_time before argument. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- itemkey - Item key to delete, or null for all itemkeys -- enddate - Date to obsolete to -- docommit- Do not commit if set to false -- force - delete child records even if parent is not end dated -- /*# * This API purge all runtime data associated with * completed items, their processes, and notifications * sent by them. * @param itemtype Item Type * @param itemkey Item Key * @param enddate End Date * @param docommit Commit Data * @param force Forec Mode * @rep:scope public * @rep:lifecycle active * @rep:displayname Purge Items * @rep:ihelp FND/@wfpurge#a_items See the related online help */ procedure Items( itemtype in varchar2 default null, itemkey in varchar2 default null, enddate in date default sysdate, docommit in boolean default true, force in boolean default false); -- -- procedure Activities -- Delete old activity versions with end_time before argument, -- and that are not referenced by an existing item. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- name - Activity to delete, or null for all activities -- enddate - Date to obsolete to -- NOTE: -- It is recommended to purge Items before purging Activities to avoid -- obsolete item references preventing obsolete activities from being -- deleted. -- /*# * This API deletes old versions of eligible * activities from the workflow tables that * are associated with the specified item type, * have an END_DATE less than or equal to * the specified end date and are not referenced * by an existing item as either a process or activity. * @param itemtype Item Type * @param name Activity Internal Name * @param enddate End Date * @rep:scope public * @rep:lifecycle active * @rep:displayname Purge Activity * @rep:ihelp FND/@wfpurge#a_act See the related online help */ procedure Activities( itemtype in varchar2 default null, name in varchar2 default null, enddate in date default sysdate); -- -- procedure Notifications -- Delete old notifications with end_time before argument, -- and that are not referenced by an existing item. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- enddate - Date to obsolete to -- docommit- Do not commit if set to false -- NOTE: -- It is recommended to purge Items before purging Notifications to avoid -- obsolete item references preventing obsolete notifications from being -- deleted. -- /*# * This API deletes old eligible notifications from * the tables WF_NOTIFICATION_ATTRIBUTES * and WF_NOTIFICATIONS that are associated * with the specified item type, have an END_DATE * less than or equal to the specified end date and * are not referenced by an existing item. * @param itemtype Item Type * @param enddate End Date * @param docommit Commit Data * @rep:scope public * @rep:lifecycle active * @rep:displayname Purge Notifications * @rep:ihelp FND/@wfpurge#a_notif See the related online help */ procedure Notifications( itemtype in varchar2 default null, enddate in date default sysdate, docommit in boolean default true); -- -- procedure Item_Notifications -- Delete notifications sent by a particular item with end_time -- before argument. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- itemkey - Item key to delete, or null for all itemkeys -- enddate - Date to obsolete to -- docommit- Do not commit if set to false -- procedure Item_Notifications( itemtype in varchar2 default null, itemkey in varchar2 default null, enddate in date default sysdate, docommit in boolean default true); -- -- Total -- Delete all obsolete runtime data with end_time before argument. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- itemkey - Item key to delete, or null for all itemkeys -- enddate - Date to obsolete to -- docommit- Commit or no commit after each purge of entitiy -- runtimeonly - Delete runtime data alone if set to true -- else delete both design and runtime data -- /*# * This API deletes all eligible obsolete * runtime item type and activity data that * is associated with the specified item type * and has an END_DATE less than or equal * to the specified end date. The API also deletes * ad hoc roles and users that are no longer in use * @param itemtype Item Type * @param itemkey Item Key * @param enddate End Date * @param docommit Commit Data * @param runtimeonly Purge RuntimeData Only * @rep:scope public * @rep:lifecycle active * @rep:displayname Total Purge * @rep:ihelp FND/@wfpurge#a_total See the related online help */ procedure Total( itemtype in varchar2 default null, itemkey in varchar2 default null, enddate in date default sysdate, docommit in boolean default true, runtimeonly in boolean default false ); -- -- TotalPERM -- Delete all obsolete runtime data that is of persistence type 'PERM' -- and with end_time before argument. -- IN: -- itemtype - Item type to delete, or null for all itemtypes -- itemkey - Item key to delete, or null for all itemkeys -- enddate - Date to obsolete to -- docommit- Commit or no commit after each purge of entitiy -- runtimeonly - Delete runtime data alone if set to true -- else delete both design and runtime data -- -- /*# * This API deletes all eligible obsolete runtime * data that is of persistence type 'PERM' (Permanent) * and that is associated with the specified item type * and has an END_DATE less than or equal to the * specified end date. * @param itemtype Item Type * @param itemkey Item Key * @param enddate End Date * @param docommit Commit Data * @param runtimeonly Purge RuntimeData Only * @rep:scope public * @rep:lifecycle active * @rep:displayname Total Purge * @rep:ihelp FND/@wfpurge#a_totprm See the related online help */ procedure TotalPERM( itemtype in varchar2 default null, itemkey in varchar2 default null, enddate in date default sysdate, docommit in boolean default true, runtimeonly in boolean default false); -- -- TotalConcurrent -- Concurrent Program version of Total -- IN: -- errbuf - CPM error message -- retcode - CPM return code (0 = success, 1 = warning, 2 = error) -- itemtype - Item type to delete, or null for all itemtypes -- itemkey - Item key to delete, or null for all itemkeys -- age - Minimum age of data to purge (in days) -- x_persistence_type - Persistence Type to be purged: 'TEMP' or 'PERM' -- procedure TotalConcurrent( errbuf out NOCOPY varchar2, retcode out NOCOPY varchar2, itemtype in varchar2 default null, itemkey in varchar2 default null, age in varchar2 default '0', x_persistence_type in varchar2 default 'TEMP', runtimeonly in varchar2 default 'N', x_commit_frequency in number default 500 ); -- -- Directory -- Purge all WF_LOCAL_* tables based on expiration date -- IN: -- end_date - Date to purge to -- /*# * This API Purges all users and roles in * the WF_LOCAL_* tables whose expiration * date is less than or equal to the specified * end_date and that are not referenced in any * notification. * @param end_date End Date * @rep:scope public * @rep:lifecycle active * @rep:displayname Purge Roles * @rep:ihelp FND/@wfpurge#a_prgdir See the related online help */ procedure Directory( end_date in date default sysdate); -- -- AdHocDirectory -- Purge all WF_LOCAL_* tables based on expiration date -- Call procedure Directory instead -- IN: -- end_date - Date to purge to -- procedure AdHocDirectory( end_date in date default sysdate); -- -- GetPurgeableCount -- Returns the count of purgeable items for a specific itemType. -- IN: -- p_itemType in VARCHAR2 -- function GetPurgeableCount (p_itemType in varchar2) return number; -- -- AbortErrorProcess -- Abort the WFERROR process if activity is COMPLETE -- IN: -- itemtype - Item type to move, or null for all itemtypes -- itemkey - Item key to move, or null for all itemkeys procedure AbortErrorProcess(itemtype in varchar2 default null, itemkey in varchar2 default null); end WF_PURGE; / -- show errors package WF_PURGE --select to_date('SQLERROR') from user_errors --where type = 'PACKAGE' --and name = 'WF_PURGE' --/ REM ================================================================ commit; exit;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de