Purging Operational Data

Introduction

Data volumes in Oracle Health Insurance applications typically grow when more and more data is processed. This is true for operational data and for technical data that support the processing of claims (see Purging Data from Technical Tables for more information on the purging of technical tables). A purging process for the operational tables is available to limit the size of the operational data.

The integration points Purging Claims Data and Purging Authorization Data also enable the purge of claims and authorization data.

Enabling Data Purging for Operational Tables

The database handles the purging of operational data through two PL/SQL packaged procedures:

cla_purge_operational_data_pkg.purge_claims
cla_purge_operational_data_pkg.purge_authorizations

The owner of the database objects, for example, ohi_claims_owner, must execute the procedures.

Purge Claims

Procedure cla_purge_operational_data_pkg.purge_claims removes Claim data (Claims and all their details) based on the parameters specified below. The procedure purges Claims that match all specified parameters. The procedure purges claims of all process types (claim, reservation, and quotes).
Purging is exempt from Claims that have the Exempt from Purging indicator as Yes (on the related external Claims data records). They do not purge even if they match the specified parameters.

Note that Claims purge regardless of their status. So, purging is also possible for Claims that are not in completed status.

procedure cla_purge_operational_data_pkg.purge_claims
( p_period_uom in varchar2
, p_period_length in integer (1)
, p_include_financial_data in varchar2 (2)
, p_include_limit_consumptions in varchar2 (3)
, p_form_code in varchar2 default null
, p_form_type_code in varchar2 default null
, p_brand_code in varchar2 default null (4)
, p_claim_dyn_usage_name1 in varchar2 default null
, p_claim_dyn_value1 in varchar2 default null
, p_claim_dyn_usage_name2 in varchar2 default null
, p_claim_dyn_value2 in varchar2 default null
, p_claim_dyn_usage_name3 in varchar2 default null
, p_claim_dyn_value3 in varchar2 default null (5)
, p_ext_cla_data_dyn_usage_name in varchar2 default null
, p_ext_cla_data_dyn_value in varchar2 default null (6)
, p_commit_size in binary_double default null (7)
, p_current_date in date default null (8)
)

Parameters

1	`p_period_uom` and `p_period_length` These mandatory parameters specify together the retention period. The reference date for the retention period is the system date. Specify the unit of measure as `D` (days), `M` (months), or `Y` (years). If, for example, the specified retention period is ten years, the procedure purges Claims that are older than ten years, based on the date of the most recent Claim status history record.
2	`p_include_financial_data` This mandatory parameter specifies if the procedure purges the financial data of the Claim. Financial data are base financial objects and their underlying details, such as financial transactions. Note that the procedure purges financial data that belong to Claims that no longer exist (because the application purged them in a previous purging process) regardless of the retention period if this parameter is `Y`.
3	`p_include_limit_consumptions` This mandatory parameter specifies if the procedure purges adjudication limit consumptions and provider limit consumptions of the Claim. Note that the procedure purges consumptions that do not belong to Claims (external consumptions or consumption of previously purged claims) based on the retention period) if this parameter is `Y`.
4	`p_form_code, p_form_type_code`, and `p_brand_code` These optional parameters specifies the Claim form, the Claim form type, and the brand. The procedure only purges Claims with the specified form, form type and brand. The procedure does not purge claims without a brand.
5	`p_claim_dyn_usage_name1/2/3` and `p_claim_dyn_value1/2/3` These optional parameters provide the ability to use dynamic fields that are parameters on the Claims table for the purging process. Only use single value dynamic fields as parameters. Specifying dynamic field usage name and value (specify both) purges only Claims that have the specified value for the specified dynamic field. It is possible to specify a maximum of three Claim dynamic fields as parameters. Use the following format for dynamic fields of type date: `YYYY-MM-DD`.
6	`p_ext_cla_data_dyn_usage_name` and `p_ext_cla_data_dyn_value` These optional parameters provide the ability to use dynamic fields that are available on the external Claims data table as parameters for the purging process. Only use single-value dynamic fields as parameters. Specifying a dynamic field usage name and value (specify both) purges only Claims that have the specified value for the specified dynamic field on their external claims data record. Only specify a single external Claims data dynamic field as a parameter. Use the following format for dynamic fields of type date: `YYYY-MM-DD`.
7	`p_commit_size` A large undo tablespace will have to be available for the duration of the run to purge a large amount of data. For cases where this is impossible or impracticable, (for example, in managed cloud-based configurations where there is no direct access to the database) it is possible to limit the number of records that are deleted before a commit. Setting the commit size parameter, however, comes at the cost of performance. A smaller commit size generates more overhead. Use of this parameter is optional. By default, using a single statement per table followed by a commit per table purges each table.
8	`p_current_date` This is an optional parameter. Use it in test environments only. Calculate the retention period as if today is `p_current_date` instead of `sysdate`. This enables purging recent records in test situations.

Example

The following example purges institutional Claims with product line (dynamic field on the Claims table) HMO that are older than ten years together with their financial data, adjudication limit, and provider limit consumptions.

begin
  cla_purge_operational_data_pkg.purge_claims
  ( p_period_uom => 'Y'
  , p_period_length => 10
  , p_include_financial_data => 'Y'
  , p_include_limit_consumptions => 'Y'
  , p_form_type_code => 'INST'
  , p_claim_dyn_usage_name1 => 'productLine'
  , p_claim_dyn_value1 => 'HMO'
  );
end;

Purge Authorizations

Procedure cla_purge_operational_data_pkg.purge_authorizations removes authorization data (Authorizations and all their details) based on the parameters specified below. Authorizations that match all specified parameters purge successfully.

Note that the purging process excludes Authorizations with consumption. This means Authorizations that have links to Claim Lines (through consumptions) do not purge. It is essential to run the Claims purging process before the authorization purging process for discarding those links. This removes authorization consumptions of Claim Lines. Afterward, the Authorizations purging process removes the Authorizations, including their counters and counter periods.

cla_purge_operational_data_pkg.purge_authorizations
( p_period_uom in varchar2
, p_period_length in integer (1)
, p_form_code in varchar2 default null (2)
, p_auth_dyn_usage_name1 in varchar2 default null
, p_auth_dyn_value1 in varchar2 default null
, p_auth_dyn_usage_name2 in varchar2 default null
, p_auth_dyn_value2 in varchar2 default null
, p_auth_dyn_usage_name3 in varchar2 default null
, p_auth_dyn_value3 in varchar2 default null (3)
, p_commit_size in binary_double default null (4)
, p_current_date in date default null (5)
)

Parameters

1	`p_period_uom` and `p_period_length` These mandatory parameters specify together the retention period. Specify the unit of measure as `D (days), M (months), or Y (years)`. The reference date for the retention period is the system date. So if, for example, specifying a retention period of ten years purges all Authorizations that are older than (based on the date part of the last updated date-time attribute of the authorization record) ten years (from the date that the purging process executes).
2	`p_form_code` This optional parameter specifies the authorization form. Only Authorizations with the specified form purge.
3	`p_auth_dyn_usage_name1/2/3` and `p_auth_dyn_value1/2/3` These optional parameters provide the ability to use dynamic fields on the authorizations table as parameters for the purging process. It is possible to only use single value dynamic fields as parameters. Specifying dynamic field usage name and values (specify both) purges only authorizations that have the specified value for the specified dynamic field. It is possible to specify a maximum of three authorization dynamic fields as parameters. Use the following format for dynamic fields of type date: `YYYY-MM-DD`.
4	`p_commit_size` See the explanation of the parameter in the "Purge Claims" section.
5	`p_current_date` See the explanation of the parameter in the "Purge Claims" section.

Example

The following example purges only the Authorizations with the ONCOLOGY form and product line (dynamic field on the authorization table) HMO that are older than ten years.

begin
  cla_purge_operational_data_pkg.purge_authorizations
  ( p_period_uom => 'Y'
  , p_period_length => 10
  , p_form_code => 'ONCOLOGY'
  , p_auth_dyn_usage_name1 => 'productLine'
  , p_auth_dyn_value1 => 'HMO'
  );
end;

Using DBMS_SCHEDULER for Frequently Purging Data

Oracle recommends frequent and automatic purging of operational data. For example, through the use of a DBMS_SCHEDULER job. See Purging Data from Technical Tables for more information on this subject.