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 supports processing (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.

Enabling Data Purging for Operational Tables

The database handles the operational tables entirely. Execute two PL/SQL packaged procedures to enable data purging for the applicable tables:

  • 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 (applies across process type 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) (that is, they do not purge even if they match the specified parameters).

Note that Claims purge regardless of their status. So, purging is also possible 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
, p_include_financial_data in varchar2
, p_include_limit_consumptions in varchar2
, p_form_code in varchar2 default null
, p_form_type_code in varchar2 default null
, p_brand_code in varchar2 default null
, 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
, p_ext_cla_data_dyn_usage_name in varchar2 default null
, p_ext_cla_data_dyn_value in varchar2 default null
, p_commit_size in binary_double default null
, p_current_date in date default null
)

Parameters

  • p_period_uom and p_period_length

    • These mandatory parameters specify together the retention period. It is important to 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, the specified retention period is ten years, then the purging is possible for all Claims that are older than (based on the date part of the date-time attribute of the most recent Claim status history record) ten years (from the date that the purging process executes).

  • p_include_financial_data

    • This mandatory parameter specifies if the procedure purges the financial data (base financial objects and their underlying details, such as financial transactions) as a detail of the Claim. Note that the base financial objects (including their underlying details) that belong to Claims that no longer exist (because the application purges them in by a previous purging process) also purge (regardless of the retention period) if this parameter is Y.

  • p_include_limit_consumptions

    • This mandatory parameter specifies if the adjudication limit consumptions and Provider limit consumptions purge as details of the Claim. Note that the consumptions that do not belong to Claims (external consumptions or consumption of previously purged claims) also purge (based on the retention period) if this parameter is Y.

  • p_form_code

    • This optional parameter specifies the Claim form. Only Claims with the specified form purge.

  • p_form_type_code

    • This optional parameter specifies the Claim form type. Only Claims with the specified form purge.

  • p_brand_code

    • This optional parameter specifies the brand. Only Claims with the specified brand (excludes Claims without a specified brand) purge.

  • 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.

  • 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.

  • 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.

  • 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
, p_form_code in varchar2 default null
, 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
, p_commit_size in binary_double default null
, p_current_date in date default null
)

Parameters

  • 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).

  • p_form_code

    • This optional parameter specifies the authorization form. Only Authorizations with the specified form purge.

  • 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.

  • p_commit_size

    • See the explanation of the parameter in the "Purge Claims" section.

  • 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.