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.
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. |
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. |
3 | p_include_limit_consumptions
This mandatory parameter specifies if the procedure purges adjudication limit consumptions and provider limit consumptions of the Claim. |
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. |
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. |
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 |
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 |
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. |
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.