Purging Operational Data

Introduction

Data volumes in Oracle Health Insurance Components applications are likely to grow fast. This is true for operational data and for technical data that is used to support processing (see Purging Data from Technical Tables for more information on purging of technical tables). To limit the size of the operational data, a purging process for the operational tables is available.

Enabling Data Purging for Operational Tables

Purging the operational tables is handled entirely in the database. To enable data purging for the applicable tables, two PL/SQL packaged procedures need to be executed:

  • cla_purge_operational_data_pkg.purge_claims

  • cla_purge_operational_data_pkg.purge_authorizations

The procedures must be executed as the owner of the database objects, e.g. ohi_claims_owner.

Purge Claims

Procedure cla_purge_operational_data_pkg.purge_claims removes Claim data (claims and all their details) based on the parameters specified below. Claims that match all specified parameters are purged (applies across process type claim, reservation and quotes). Claims with the 'Exempt from Purging' indicator set to Yes (on the related external claims data records) are exempted from purging (i.e. these are not purged, even if they match the specified parameters).

Note that claims are purged regardless of their status, so also claims that are not in a finalized status are considered for purging.

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. The unit of measure can be specified as D (days), M (months) or Y (years). The reference date for the retention period is the system date, so if for example a retention period of ten years is specified 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 is executed) are considered for purging.

  • p_include_financial_data

    • This mandatory parameter specifies if the financial data (base financial objects and their underlying details such as financial transactions) is purged as a detail of the claim. Note that base financial objects (including their underlying details) that belong to claims that do no longer exist (because they have been purged by a previous purging process) are also purged (regardless of the retention period) if this parameter is set to 'Y'.

  • p_include_limit_consumptions

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

  • p_form_code

    • This optional parameter specifies the claim form. Only claims with the specified form are purged.

  • p_form_type_code

    • This optional parameter specifies the claim form type. Only claims with a form of the specified type are purged.

  • p_brand_code

    • This optional parameter specifies the brand. Only claims with the specified brand are purged (claims without a specified brand are excluded).

  • 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 configured on the claims table as parameters for the purging process. Only single value dynamic fields can be used as parameters. If a dynamic field usage name and value are specified (both need to be specified), only claims that have the specified value for the specified dynamic field are purged. A maximum of three claim dynamic fields can be specified as parameters. The following format has to be used 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 configured on the external claims data table as parameters for the purging process. Only single value dynamic fields can be used as parameters. If a dynamic field usage name and value are specified (both need to be specified), only claims that have on their external claims data record the specified value for the specified dynamic field are purged. Only one external claims data dynamic field can be specified as a parameter. The following format has to be used for dynamic fields of type date: YYYY-MM-DD.

  • p_commit_size

    • If large amounts of data are to be purged, a large undo tablespace will have to be available for the duration of the run. For cases where this is impossible or impracticable (e.g. 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 prior to a commit. Setting the commit size parameter however comes at the cost of performance: the smaller the commit size is set, the more overhead is generated in the process. Use of this parameter is optional, by default each table will be purged using a single statement per table, followed by a commit per table.

  • p_current_date

    • Optional parameter, to be used in test environments only: Calculate the retention period as if today is p_current_date instead of sysdate. Enables purging recent records in test situations.

Example

In the following example institutional claims of product line (dynamic field on the claims table) HMO that are older than ten years are purged together with their financial data and 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 are purged.

Note that authorizations with consumptions are excluded from the purging process. This means that authorizations that are linked to claim lines (through consumptions) are not purged. To ensure that those links are discarded, the claims purging process, which removes authorization consumptions of claim lines, needs to be run before the authorization purging process. Afterwards 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. The unit of measure can be specified as D (days), M (months) or Y (years). The reference date for the retention period is the system date, so if for example a retention period of ten years is specified 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 is executed) are considered for purging.

  • p_form_code

    • This optional parameter specifies the authorization form. If specified, only authorizations with the specified form are purged.

  • p_auth_dyn_usage_name1/2/3 and p_auth_dyn_value1/2/3

    • These optional parameters provide the ability to use dynamic fields that are configured on the authorizations table as parameters for the purging process. Only single value dynamic fields can be used as parameters. If a dynamic field usage name and value are specified (both need to be specified), only authorizations that have the specified value for the specified dynamic field are purged. A maximum of three authorization dynamic fields can be specified as parameters. The following format has to be used for dynamic fields of type date: YYYY-MM-DD.

  • p_commit_size

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

  • p_current_date

    • See explanation of the parameter in the Purge Claims section

Example

In the following example authorizations of ONCOLOGY form and product line (dynamic field on the authorization table) HMO that are older than ten years are purged.

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