28 About Purging Data

This chapter provides information about purging obsolete database objects and expired account subbalances from your Oracle Communications Billing and Revenue Management (BRM) database. It discusses the impact of purging major event objects and how this affects BRM applications.

Before purging data, you should be familiar with the following:

About Purging Database Objects

You can purge objects that are no longer required for daily business operations from your BRM database as follows:

  • If your tables are partitioned, you can use the partition_utils utility to purge objects. See "Partitioning Tables".

  • To purge objects from any type of database, you can use custom purging scripts. For more information, contact Oracle support.

Purging objects enables you to do the following:

  • Delete obsolete data from your database.

  • Reduce storage space in your database.

  • Reduce the number of objects in your database, making it easier to upgrade to later releases of BRM.

Objects Purged by Default

By default, tables for the objects listed in Table 28-1 are automatically partitioned and purged if partitioning is enabled on your BRM system and if the objects in the partition satisfy the purging criteria.

Table 28-1 Objects That Are Automatically Partitioned and Purged

Object Type Purging Criteria

bill

  • Within specified date range

  • Closed

event

  • Within specified date range

  • Associated items are closed

Note: Event objects in the partition_historic partition cannot be purged even if they meet all the purging criteria.

invoice

  • Within specified date range

item

  • Within specified date range

  • Closed

  • Not referenced by a journal object

Note: Item objects in the partition_historic partition cannot be purged even if they meet all the purging criteria

journal

  • Within specified date range

  • Fully earned by the end date of the date range

Note: In addition, Oracle recommends that the journal object be included in a G/L report.

newsfeed

  • None

sepa

  • Within specified date range

  • Not pending (status is not 1)


About Purging BRM Event Objects

Event objects are grouped into the following categories:

Event Objects That Have a Balance Impact

Event objects that have a balance impact are required for the following functions:

  • Billing

  • Accounts receivable (A/R) operations

  • Tracking session charges

The balance impacts in these objects are stored in the EVENTS_BAL_IMPACTS_T table. These objects are typically created by usage, cycle, and purchase events.

Caution:

Purge these event objects only after you finish all billing, A/R, and session event processing for the current billing cycle.

Event Objects That Do Not Have a Balance Impact

Database objects that do not have a balance impact are not needed by BRM for rating or billing. Your business, however, might need them for auditing. Auditing objects and unused objects are in this category.

If you purge objects that do not generate a balance impact, you cannot view auditing information for those objects. For example, if you purge /event/customer/login objects and then want to check when a user logged in, you will not be able to find the user's login event with Event Browser.

Important:

Before purging objects that do not have a balance impact, verify that the objects are not required by any custom code.

Event and Item Objects Stored in partition_historic

If your event and item tables are partitioned, event and item objects that are required for the product to behave correctly or for auditing purposes (nonpurgeable events and items) are stored in the partition_historic partition.

Note:

  • By default, all item objects are purgeable.

  • If an event is nonpurgeable, its associated items should also be nonpurgeable because the event is incomplete without them.

  • If you make an item storable class nonpurgeable, make sure the item storable class applies only to nonpurgeable events.

See "Associating Items with Nonpurgeable Events".

Important:

If you must purge event objects in your implementation, before purging them, make sure you understand the impact of purging those objects. See "Impact of Purging Event Objects".

By default, the following types of event objects are nonpurgeable and thus are stored in partition_historic when you install BRM:

  • /event/billing/cdc_update

  • /event/billing/cdcd_update

  • /event/billing/cycle/discount

  • /event/billing/cycle/discount/mostcalled

  • /event/billing/cycle/rollover

  • /event/billing/cycle/rollover/monthly

  • /event/billing/cycle/rollover_transfer

  • /event/billing/deal

  • /event/billing/deal/purchase

  • /event/billing/deal/cancel

  • /event/billing/discount/action

  • /event/billing/discount/action/cancel

  • /event/billing/discount/action/modify

  • /event/billing/discount/action/modify/status

  • /event/billing/discount/action/purchase

  • /event/billing/discount/action/set_validity

  • /event/billing/lcupdate

  • /event/billing/mfuc_update

  • /event/billing/ordered_balgrp

  • /event/billing/product/action

  • /event/billing/product/action/cancel

  • /event/billing/product/action/modify

  • /event/billing/product/action/modify/status

  • /event/billing/product/action/purchase

  • /event/billing/product/action/set_validity

  • /event/delayed/tmp_profile_event_ordering

  • /event/group/sharing

  • /event/group/sharing/charges

  • /event/group/sharing/discounts

  • /event/group/sharing/profiles

You can customize this list to meet your business requirements. For more information on partition_historic, see "About Partitioning".

Impact of Purging Event Objects

Before purging event objects, you must know this information:

  • Impact of purging the event objects

  • How long to keep the event objects

The following tables describe the impact of purging event objects that contain data for billing, A/R, opening and closing sessions, and auditing.

Note:

Only major event storable classes and events stored by default in the partition_ historic tables are listed in this chapter.

Billing Event Objects

Table 28-2 lists the impacted Billing Event Objects.

Table 28-2 Billing Event Objects

Event object Impact of purging/when to purge

/event/billing/cdc_update

If you purge these events, the number of contract days for resources will be lost. You cannot apply discounts based on the cumulative number of contract days for resources.

/event/billing/cdcd_update

If you purge these events, the number of contract days for discount resources will be lost. You cannot apply discounts based on the cumulative number of contract days for discount resources.

/event/billing/charge and all of its subclasses

If you purge these event objects:

  • You cannot use the resubmit parameter with the pin_recover utility to resubmit failed credit card transactions.

  • The pin_clean utility cannot report VERIFY checkpoints.

If you have run the pin_recover and pin_clean utilities and successfully recovered all failed credit card transactions, you can purge these event objects.

/event/billing/cycle and all of its subclasses

If you purge these event objects:

  • Invoices cannot display details about subscription charges applied to accounts.

  • Data in default reports generated by the pin_ledger_report utility will not tally correctly because the reports display charges accrued by these objects. For information about the pin_ledger_report utility, see "pin_ledger_report" in BRM Collecting General Ledger Data.

  • You cannot perform A/R activities such as adjustments, write-offs, disputes, and settlements for subscription charges logged by these events.

If you generate invoices and G/L reports, keep these event objects through the current billing cycle.

/event/billing/cycle/discount/mostcalled

If you purge these events, all the information about the total charges, duration, and the number of calls for the most called numbers will be lost. You cannot apply the most-called number discounts.

See also the impact of purging /event/billing/cycle and all of its subclasses.

/event/billing/cycle/rollover/monthly

Purging these objects will have the following impact:

  • Rollover correction during final billing will not work if the rollover events for the current cycle have been purged.

  • Rerating will not work correctly if the rollover events in the rerating period have been purged.

  • The controlled rollover of free resources during plan transition will not work if the rollover events for the current cycle have been purged.

See also the impact of purging /event/billing/cycle and all of its subclasses.

/event/billing/cycle/rollover_transfer

If you purge these events, information about the balance impacts of the original rollover event will be lost.

See also the impact of purging /event/billing/cycle and all of its subclasses.

You may want to keep these objects for auditing purposes.

/event/billing/deal

Abstract class, not persisted in the database.

/event/billing/deal/cancel

If you purge these events:

  • Rerating, which must replay these events, will not work correctly. However, you can purge old events that occurred before your rerating time frame.

  • Best pricing will not work correctly because rerating is called during best pricing.

  • You cannot see the corresponding event history in Customer Center. You can, however, see event history contained in other types of deal event objects that were not purged. Keep these event objects as long as you want to display deal cancellation history.

/event/billing/deal/purchase

If you purge these events:

  • Rerating will not work correctly. However, you can purge old events that occurred before your rerating time frame.

  • Best pricing will not work correctly because rerating is called during best pricing.

You cannot see deal purchase history. Keep these event objects as long as you want to display the history of deal purchases.

/event/billing/debit

If you purge these event objects:

  • The Bill Details panel in Customer Center cannot display debit event objects associated with a particular item.

  • Invoices cannot display event details for debit or credit items completed during the current billing cycle.

  • If you generate any custom reports that use debit events, the reports will be incorrect.

  • If you configure the pin_ledger_report utility to report debits and credits, the reported numbers will be incorrect. For information about the pin_ledger_report utility, see "pin_ledger_report" in BRM Collecting General Ledger Data.

Keep these event objects as long as you want to do the following:

  • Display debit events.

  • Display event details in an invoice.

  • Create custom reports that use debit events.

/event/billing/discount/action/cancel

The PCM_OP_SUBSCRIPTION_READ_ACCT_PRODUCTS and PCM_OP_ SUBSCRIPTION _GET_HISTORY opcodes refer to these events to show the history of the discount.

If these event objects are purged, you cannot see the corresponding discount history. You can, however, see the discount history contained in other types of event objects that are not purged. If these are the only events for the discount, then no history will be shown

/event/billing/discount/action/modify

The PCM_OP_SUBSCRIPTION_READ_ACCT_PRODUCTS and PCM_OP_ SUBSCRIPTION _GET_HISTORY opcodes refer to these events to show the history of the discount.

If these event objects are purged, you cannot see the corresponding discount history. You can, however, see discount history contained in other types of event objects that were not purged.

Keep these event objects if you must display the history of discount attribute and status modification.

/event/billing/discount/action/modify/status

If these event objects are purged, you cannot see the corresponding discount history. You can, however, see discount history contained in other types of event objects that were not purged.

Keep these event objects if you must display the history of discount attribute and status modification.

/event/billing/discount/action/purchase

If you purge these event objects, you cannot see the corresponding discount history.

Keep these event objects as long as you want to display the history of discount purchases.

/event/billing/discount/action/set_validity

If you purge these events:

  • Rating will not consider the validity period setting because the discount validity period settings before first usage and the new calculated validity period after first usage will be lost.

  • Rerating will not work correctly. However, you can purge old events that occurred before your rerating time frame.

/event/billing/lcupdate

If this event is purged, discount amount based on the number of active subscriptions will not be applied.

/event/billing/mfuc_update

If this event is purged, monthly fee and usage discount will not be applied at billing time. You may also want to keep these objects for auditing purposes.

/event/billing/ordered_balgrp

Abstract class, not persisted in the database.

/event/billing/ordered_balgrp/create

If you purge these events, the history of the creation of the object is lost. You may also want to keep these objects for auditing purposes.

/event/billing/ordered_balgrp/delete

If you purge these events, the history of the deletion of the object is lost. You may also want to keep these objects for auditing purposes.

/event/billing/ordered_balgrp/modify

If you purge these events, the history of the modifications of the object is lost. You may also want to keep these objects for auditing purposes.

/event/billing/product

If you purge these event objects:

  • Reports generated by the pin_ledger_report utility cannot display purchase and cancellation fees, and data shown in these reports will be incorrect.

  • Invoices cannot display purchase or cancellation fees.

Keep these event objects if either of the following is true:

  • You use Customer Center to view current or historical information for canceled and item products.

  • Your custom applications use these event objects.

/event/billing/product/action/cancel

If you purge these events:

  • Rerating, which must replay these events, will not work correctly. However, you can purge old events that occurred before your rerating time frame.

  • Best pricing will not work correctly because rerating is called during best pricing.

  • You cannot see the corresponding event history in Customer Center. You can, however, see event history contained in other types of deal event objects that were not purged. Keep these event objects as long as you want to display deal cancellation history.

/event/billing/product/action/modify

/event/billing/product/action/modify/status

If you purge these events:

  • Rerating will not work correctly. However, you can purge old events that occurred before your rerating time frame.

  • Best pricing will not work correctly because rerating is called during best pricing.

  • You cannot see the corresponding product history. You can, however, see product history contained in other types of product event objects that were not purged. Keep these event objects if you must display the history of product attribute and status modification.

/event/billing/product/action/purchase

If you purge these events:

  • Rerating will not work correctly. However, you can purge old events that occurred before your rerating time frame.

  • You cannot see the corresponding product history. Keep these event objects as long as you want to display the history of product purchases.

/event/billing/product/action/set_validity

If you purge these events:

  • Rating will not consider the validity period setting because the product's validity period settings before first usage and the new calculated validity period after first usage will be lost.

Also during the back-out process, rerating uses these events to remove the validity setting.

Note: You can purge old events that occurred before your rerating time frame.

/event/billing/product/fee/cycle/cycle_forward_ annual

/event/billing/product/fee/cycle/cycle_forward_ bimonthly

/event/billing/product/fee/cycle/cycle_forward_ monthly

If you purge the cycle_forward_* events for a particular period, features or operations that involve cancellation will not work correctly. For example, if all the cycle_forward_* events from 01/01/2008 to 06/01/2008 are purged, then operations, such as product or discount cancellations, service inactivations, plan or deal transitions, and change of options, which internally call the cancellations of the product or discount during that time period will not give correct results.

During product and discount cancellations, BRM searches for all the original charged events and refunds the charges for each event individually. If these events are purged, then charges for these events will not be refunded.

/event/billing/validate/cc

You may want to keep these objects for auditing purposes, for example, to keep track of credit card validations.


Accounts Receivable Event Objects

Table 28-3 lists the Accounts Receivable Event Objects.

Table 28-3 Accounts Receivable Event Objects

Event object Impact of purging/when to purge

/event/billing/adjustment/account

/event/billing/adjustment/item

If you purge these event objects, you lose some G/L information.

The pin_ledger_report utility uses these event objects to generate the G/L report for billed_earned and billed revenue types. For information about the pin_ledger_report utility, see "pin_ledger_report" in BRM Collecting General Ledger Data.

Keep these event objects for the specified G/L posting period.

/event/billing/adjustment/event

If you purge these event objects, you lose some G/L information.

The pin_ledger_report utility uses these event objects to generate the G/L report for billed_earned and billed revenue types.

Keep these event objects for the specified G/L posting period.

/event/billing/adjustment/tax_event

If you purge these event objects, you lose some G/L information.

The pin_ledger_report utility uses these event objects to generate the G/L report for billed_earned and billed revenue types.

Keep these event objects for the specified G/L posting period.

/event/billing/batch/payment

If you purge these event objects:

  • You cannot use the pin_recover utility with the resubmit parameter to resend failed transactions.

  • You cannot use the pin_clean utility.

You can purge the original batch payment event objects after you successfully resubmit any failed batch transactions.

/event/billing/batch/reversal

You can purge these event objects if you do not have any custom applications using them.

/event/billing/dispute/item

/event/billing/settlement/item

If you purge these event objects, you lose some past G/L, dispute, and settlement information.

The pin_ledger_report uses these event objects to generate the G/L report for billed_earned and billed revenue types. For information about the pin_ledger_report utility, see "pin_ledger_report" in BRM Collecting General Ledger Data.

Keep these event objects for the specified G/L posting period.

/event/billing/item/transfer

If you purge these event objects:

  • You cannot audit the related bill items.

  • A/R Tool might not show the actions performed on the related bill items.

  • Payments cannot be reversed. You must preserve these event objects if you intend to reverse payments at a future date. To find the items a payment was applied to, the PCM_OP_BILL_REVERSE_PAYMENT opcode searches for all the transfer events in which PIN_FLD_ITEM_OBJECT is equal to the payment item's POID.

Keep transfer event objects as long as you keep their related billable items.

/event/billing/payment and all of its subclasses

If you purge these event objects:

  • You cannot perform payment reversals.

  • If you generate G/L reports with the pin_ledger_report utility, the reports do not show that payments recorded in the purged events have been made. By default, G/L reports show payment data.

  • Invoices for the current billing cycle cannot show event details for payments.

  • Payment Tool cannot display payment information.

  • Custom reports that use payment objects will be incorrect.

If you have already reversed payments or your company is bound by corporate policies prohibiting payment reversal after a specified period, you can purge these events.

/event/billing/reversal and all of its subclasses

If you purge these event objects:

  • If the pin_ledger_report utility is configured to report reversals, G/L reports will be incorrect for periods whose events were purged. For information about the pin_ledger_report utility, see "pin_ledger_report" in BRM Collecting General Ledger Data.

  • Invoices for the current billing cycle cannot display event details for the purged payment reversals.

  • Any custom reports that use the purged reversal objects will be incorrect. They cannot display details of event reversals.

Your business requirements determine how long you keep these event objects.

/event/billing/writeoff/account

/event/billing/writeoff/bill

/event/billing/writeoff/item

/event/billing/writeoff/tax_account

/event/billing/writeoff/tax_bill

/event/billing/writeoff/tax_item

If you purge these event objects, you lose some G/L and tax information.

The pin_ledger_report utility uses these event objects to generate the G/L report for billed_earned and billed revenue types.

Keep these event objects for the specified G/L posting period.


Delayed Event Objects

Table 28-4 lists the Delayed Event Objects.

Table 28-4 Delayed Event Objects

Event object Impact of purging/when to purge

event/delayed/tmp_profile_event_ordering

If you purge these objects, out-of-order events will not be loaded into the /profile/event_ordering object; therefore, out-of-order events will not be detected or processed.


Group Event Objects

Table 28-5 lists the Group Event Objects.

Table 28-5 Group Event Objects

Event object Impact of purging/when to purge

/event/group/member

Purge these event objects if you do not need to track the addition or deletion of member accounts.

/event/group/parent

Purge these event objects if you do not need to track the creation of parent accounts.


Sharing Group Event Objects

Table 28-6 lists the Sharing Group Event Objects.

Table 28-6 Sharing Group Event Objects

Event object Impact of purging/when to purge

/event/group/sharing/charges/create

/event/group/sharing/charges/delete

/event/group/sharing/charges/modify

Purging these objects does not affect the behavior of the product. You may want to keep these objects for auditing purposes, because if you purge these events, the history of creation, deletion, and modification of the /group/sharing/charges object is lost.

/event/group/sharing/discounts/create

/event/group/sharing/discounts/delete

/event/group/sharing/discounts/modify

Purging these objects does not affect the behavior of the product. You may want to keep these objects for auditing purposes, because if you purge these events, the history of creation, deletion, and modification of the /group/sharing/charges object is lost.


Session Event Objects

Table 28-7 lists the Session Event Objects.

Table 28-7 Session Event Objects

Event object Impact of purging/when to purge

/event/session and all of its subclasses

If you purge these event objects:

  • And if you do not purge the corresponding item objects, the item objects will be inaccurate.

  • Different event objects point to session event objects (session_obj_* in EVENT_T) for audit purposes. You lose auditing information if you purge session event objects.

  • You cannot make adjustments or settle disputes on bills containing the corresponding items.

  • You cannot use invoicing, taxation, bill adjustments, or G/L functionality.

  • If you purge /event/session/dialup objects, batch accounting operations might be performed more than once, which could result in customers being overcharged.

Note: For dialup events, the CM pin.conf file contains an entry called trans_id_window. Purging event objects older than now - trans_id_window does not affect whether batch accounting occurs more than once.

Most session event objects have balance impacts and are required for billing, A/R, and opening and closing session activity. Therefore, these objects should be kept for at least one accounting cycle.


Auditing Event Objects

Table 28-8 lists the Auditing Event Objects.

Table 28-8 Auditing Event Objects

Event object Impact of purging/when to purge

/event/audit/price

/event/audit/price/deal

/event/audit/price/discount

/event/audit/price/plan

/event/audit/price/product

/event/audit/price/rate

/event/audit/price/rate_plan

/event/audit/price/rate_plan_selector

Purging these objects does not affect the behavior of the product.

These event objects are generated by BRM auditing features. Keep these event objects as long as you need the auditing information they contain.


Enabling Open Items to Be Purged

BRM purges item objects and their associated event objects only when the item objects are closed. In some accounts, such as prepaid, items are rarely closed. Hence, events associated with the open items also cannot be purged. The unpurgeable items and events take up storage space. To free up storage space, you can make such items purgeable and then purge them.

To enable open item objects to be purged:

  1. Open the pin_business_profile.xml file in an XML editor or a text editor.

    By default, the file is in the BRM_home/sys/data/config directory.

  2. In the business profile associated with bill units whose open items you want to close, add the following line:

    <NameValue key="AutoClose_Billed_Items" value="Yes" />
    

    The status of all open items associated with the bill units in future billing cycles will now be set to closed, and their due will be set to 0 at the end of each billing cycle.

    To close open item objects processed in past billing cycles, see "Closing Open Item Objects Processed in Past Billing Cycles".

  3. Save and close the file.

  4. Run the following command, which loads the updated contents of the pin_business_profile.xml file into the BRM database:

    load_pin_business_profile -v pin_business_profile.xml
    
  5. Read the object with the testnap utility or Object Browser and verify your changes.

    For general instructions on using testnap, see "Using testnap" in BRM Developer's Guide.

    For information on how to use Object Browser, see "Reading Objects by Using Object Browser" in BRM Developer's Guide.

  6. Stop and restart the CM. For more information, see "Starting and Stopping the BRM System".

For more information about business profiles, see "Managing Business Profiles" in BRM Managing Customers.

Closing Open Item Objects Processed in Past Billing Cycles

Setting the AutoClose_Billed_Items key-value pair in a bill unit's business profile to Yes closes all items associated with the bill unit in future billing cycles at the end of each cycle.

To close open item objects processed in past billing cycles:

  1. Verify that the AutoClose_Billed_Items key-value pair in the business profile associated with the bill units whose open items you want to close is set to Yes:

    <NameValue key="AutoClose_Billed_Items" value="Yes" />
    

    If that line is not in the business profile, add it. See "Enabling Open Items to Be Purged".

  2. Open the BRM_home/apps/partition_utils/partition_utils.values file in a text editor.

  3. Do the following:

    • Set the COMMIT_BUNDLE_SIZE parameter to the number of items closed before the changes are committed to the database.

      Higher numbers reduce the frequency of commit calls but increase the time that table rows are locked. Oracle recommends using a high number (such as the default 10000) if the number of items to close is high (for example, billions of items must be closed).

    • Configure the database connection parameters. See "Configuring a Database Connection".

  4. Save and close the file.

  5. In the BRM_home/apps/partition_utils directory, run the following command, which closes all open items of the bill units whose business profile contains the AutoClose_Billed_Items tag set to Yes:

    pin_close_items
    

For more information, see "pin_close_items".

About Purging Account Subbalances

You can purge expired account subbalances that are no longer required for daily business operations from your BRM database. Expired subbalances are the validity-based balances whose valid end dates are in the past.

Caution:

When you delete subbalances from the database, events that impacted those subbalances cannot be rerated. Make sure you no longer need the expired subbalances before deleting them.

You delete expired subbalances by running the pin_sub_balance_cleanup utility. Run this utility from the BRM_home/apps/pin_subscription directory, which contains the appropriate configuration file. Use the following command to run the pin_sub_balance_cleanup utility:

pin_sub_balance_cleanup -n | -d 

For more information, see "pin_sub_balance_cleanup".