This chapter describes how to create custom search criteria for the Oracle Communications Billing and Revenue Management (BRM) Account Migration Manager (AMM).
This document is for system administrators, database administrators, and programmers. Modifying AMM requires knowledge of the following:
Java programming language
PL/SQL
BRM database schema
AMM enables you to migrate accounts that meet custom criteria. For example, you can create custom criteria for finding and migrating accounts located in a certain state or belonging to a particular service provider.
To create a custom search criteria, perform these tasks:
AMM searches for accounts in a database schema by using SQL statements generated from an account search template. Before AMM can generate a SQL statement with new search criteria, you must first create a template for it in the custom account search properties file.
To create a template for your search criteria:
Open the custom account search properties file (BRM_home/apps/amt/com/portal/amt/custom_account_search.properties) in a text editor.
Add SQL fragments for your search criteria by using the following syntax:
criteria_name=AND SQL_condition \n
Where:
criteria_name is the name of your selection criteria.
SQL_condition is a valid SQL condition that searches a BRM table and references one or more search variables, as shown below. Search variables must be surrounded by curly braces ”{ }” and match an entry in the account_search.cfg file.
condition_text '{SearchVariable}'...
Important:
SearchVariable must use a unique name and must not match one of the BRM-defined search variable names. For the list of BRM-defined search variables, see "Creating the Account Search Configuration File".For information on the SQL condition, see your Oracle documentation.
Save and exit the file.
The following sample search template enables AMM to search for accounts located in a particular state. It tells AMM to search the ACCOUNT_NAME_INFO_T table for objects with the state field set to a specified value.
# select accounts based on state
cust_acct_search_account_state_constraint=\
AND EXISTS \n\
(SELECT an.obj_id0 FROM account_nameinfo_t an \n\
WHERE an.obj_id0 = a.poid_id0 and an.state = '{account_state}') \n
When building a query, AMM replaces the search variables in your account search template with values from the account search configuration file (BRM_home/apps/amt/account_search.cfg).
To add an entry for your search variable:
Open the BRM_home/apps/amt/account_search.cfg file in a text editor.
Add your new search entry and comments to the file.
Important:
SearchVariable must match the search variable name referenced in the custom_account_search.properties file.# - You should add comments about the new search entry and # valid values. SearchVariable=
Save and exit the file.
Each custom search variable must have a corresponding Java implementation of the Conversion interface.
Run the appropriate profile script for your shell. This script sets your CLASSPATH and PATH environment variables to the appropriate values. For example, for the c shell:
% cd BRM_home/apps/amt % source profile.csh
Create a class that implements the Conversion interface.
Save and compile your SearchVariable.java source file in the BRM_home/apps/amt/com/portal/amt directory.
% cd BRM_home/apps/amt/com/portal/amt % javac SearchVariable.java
This creates a SearchVariable.class file in the same directory.
Important:
For AMM to successfully build a search with your custom search criteria:The class name must match the search variable name used in the custom_account_search.properties and account_search.cfg files.
The class must reside in the BRM_home/apps/amt/com/portal/amt directory.
The following sample class, account_state.class, enables users to search for accounts from California or Oregon.
package com.portal.amt; public class account_state implements Conversion { public String convert(String stateName) throws ConversionException { String stateCode = null; if(stateName.equals("California")) { stateCode = "CA"; } else if(stateName.equals("Oregon")) { stateCode = "OR"; } else { throw new ConversionException("Error: account_state " + stateName + " unknown."); } return(stateCode); } }
Before migrating accounts with the new search criteria, verify its accuracy by:
Use the "pin_amt_test" utility to verify that your custom search template generates a valid SQL statement.
Open your account search configuration file (BRM_home/apps/amt/account_search.cfg) in a text editor.
Enter values for the source and destination database schemas, the batch size, and your custom search criteria.
For example, you might enter the following to test the account_state criteria:
src_database=0.0.0.1 dest_database=0.0.0.2 start_creation_date= end_creation_date= product_name= account_status= bill_day_of_month= max_accounts= batch_size=50 poid_list= account_state=California
Save and exit the file.
Use the pin_amt_test utility to generate a SQL statement with your new search criteria.
% pin_amt_test -c AccountSearchFile
If successful, the utility displays the resulting SQL statement. For example, the sample account_state criteria generates the following:
Compile: account_search.cfg ------ account search SELECT statement: -- acct_search_select: default SELECT DISTINCT a.poid_id0 FROM account_t a WHERE ...
If the compilation failed, the utility returns the file name and line number where the error occurred. For example, the utility returns the following when users enter an invalid state for the sample account_state criteria:
compile: account_search.cfg
--------
account_search.cfg:32: mapping of account_state field value Florida failed
Verify that the resulting SQL statement is correct.
Use the pin_amt_test utility to verify that your search criteria works properly. This utility only displays results on the screen and does not migrate your objects.
To verify your search query:
Create a database schema with a precisely defined set of account data. The database schema should contain a small number of accounts.
Create a list of account POIDs that meet the custom criteria you are testing. For example, write down the POIDs of all accounts created in California.
Open your account search configuration file (BRM_home/apps/amt/account_search.cfg) in a text editor.
Enter values for the source and destination database schemas, the batch size, and your custom search criteria.
For example, you might enter the following to test the account_state criteria:
src_database=0.0.0.1 dest_database=0.0.0.2 start_creation_date= end_creation_date= product_name= account_status= bill_day_of_month= max_accounts= batch_size=50 poid_list= account_state=California
Save and exit the file.
Use the pin_amt_test utility to execute your search query against the source database schema:
% pin_amt_test -e AccountSearchFile
The utility prints to the screen a list of account POIDs that meet your search criteria. Compare this list of POIDs with the list you created in step 2.
If the lists match, your new search criteria works properly and you can start using it to migrate accounts.
If the lists do not match, make sure:
Your search template generates a valid SQL statement.
Your search template, search configuration file, and class all refer to the same variable name.