Rate Plan Maintenance

NexOSS Rate Plan Maintenance Instructions

How to Find a Rate Plan ID

Every rate plan provisioned to the NexOSS database is assigned a unique rate plan ID. To delete a specific rate plan, the rate plan ID must be known.

Using the Command Line Interface

A list of rate plan IDs for each account may be listed using the command line interface. Please see the following examples.

  • MySQL Example

Login to the MySQL database. Do not include parentheses around username, password and database name

   [ossadmin@linux-lab ~]$ mysql -u(username) -p(password) -D(database name)
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 579644
   Server version: 5.1.66 Source distribution
 
   Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
 
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
   mysql> select ID, CUSTOMERNAME, EFFECTIVEDATE from RATEPLAN order by CUSTOMERNAME, EFFECTIVEDATE;
   ++++-+++++++++++++++++++--+++++++++++++++++++++++
   | ID | CUSTOMERNAME       | EFFECTIVEDATE       |
   |+++-+++++++++++++++++++--+++++++++++++++++++++++
   | 62 | Belgacom_Tier-1    | 2001-01-02 00:00:01 |
   | 66 | Belgacom_Tier-1    | 2001-01-02 00:00:02 |
   | 64 | Belgacom_Tier-1    | 2002-01-04 00:00:03 |
   | 65 | Belgacom_Tier-2    | 2002-01-04 00:00:03 |
   | 27 | BluIP - 360 Data   | 2013-05-01 23:59:59 |
   |  7 | BluIP - GX         | 2013-05-06 23:59:59 |
   | 11 | BluIP - Intelepeer | 2013-05-06 23:59:59 |
   | 54 | BluIP - Level3     | 2013-05-01 23:59:59 |
   | 42 | BluIP - Peerless   | 2013-05-01 23:59:59 |
   |  5 | BluIP - Peerless   | 2013-05-06 23:59:59 |
   | 32 | BluIP - SIPRoutes  | 2013-05-06 23:59:59 |
   |  8 | BluIP - Transcom   | 2013-05-01 23:59:59 |
   | 43 | BluIP - Verizon    | 2013-05-01 23:59:59 |
   | 12 | BluIP - Verizon    | 2013-05-06 23:59:59 |
   | 23 | BluIP Simulation   | 2013-05-01 23:59:59 |
   | 53 | BluIP Simulation   | 2013-05-02 00:00:00 |
   | 67 | NQU1               | 2002-01-04 00:00:00 |
   |  2 | Standard           | 2012-12-05 00:00:00 |
   |  1 | TermCust           | 2012-12-05 00:00:00 |
   ++++-+++++++++++++++++++--+++++++++++++++++++++++
   19 rows in set (0.00 sec)
 
   mysql>
  • Oracle Example

Log into the Oracle database. Do not include parentheses around username, password and database name.

   solaris10$ sqlplus (username)/(password)@(database name)
 
   SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 4 13:39:24 2014
 
   Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
   SQL> select ID, CUSTOMERNAME, EFFECTIVEDATE from RATEPLAN order by CUSTOMERNAME, EFFECTIVEDATE;         ID CUSTOMERNAME   EFFECTIVEDATE       353 Customer-28    01-JAN-00 12.00.00.000000 AM       354 Customer-38    01-JAN-00 12.00.00.000000 AM       355 Customer-41    01-JAN-00 12.00.00.000000 AM       356 Customer-44    01-JAN-00 12.00.00.000000 AM       357 Direct Peering 01-JAN-00 12.00.00.000000 AM       344 PlusTest       07-JAN-12 12.34.59.000000 PM       345 Premium        01-JAN-13 12.00.01.000000 AM       342 Provider-136   04-JAN-02 12.00.01.000000 AM       343 Provider-35    07-JAN-12 12.23.59.000000 PM       340 Provider-39    06-FEB-14 12.00.00.000000 AM       341 Provider-48    04-JAN-02 12.00.00.000000 AM       358 Provider-56    01-JAN-00 12.00.00.000000 AM       359 Provider-60    01-JAN-14 12.00.00.000000 AM 13 rows selected. SQL>

How to Delete Rate Plans

MySQL Examples

Log into the MySQL database. Do not include parentheses around username, password and database name.

[ossadmin@linux-lab ~]$ mysql -u(username) -p(password) -D(database name)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 579644
Server version: 5.1.66 Source distribution
 
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Run the following SQL statements. In the following examples, rate plans are being deleted for the account “XYZ”.

Delete All Rate Plans for an Account
mysql> delete from RATEPLAN where CUSTOMERNAME = 'XYZ';
mysql> commit;
Delete Rate Plans by Account and Rate Plan ID
mysql> delete from RATEPLAN where CUSTOMERNAME = 'XYZ' AND ID < 54;
mysql> commit;
Delete Rate Plans by Account and Effective Date
mysql> delete from RATEPLAN where CUSTOMERNAME='XYZ' and EFFECTIVEDATE <' 2014-12-01';
mysql> commit;
Delete Rate Plan by Rate Plan ID
mysql> delete from RATEPLAN where ID = xxx;
mysql> commit;
Delete All Rate Plans in the Database
mysql> delete from RATEPLAN;
mysql> commit;

Oracle Examples

Log into the Oracle database. Do not include parentheses around username, password and database name

solaris10$ sqlplus (username)/(password)@(database name)
SQLPlus: Release 11.2.0.1.0 Production on Thu Dec 4 13:39:24 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Run the following SQL statements. In the following examples, rate plans are being deleted for the account “XYZ”.

Delete All Rate Plans for an Account
SQL> delete from RATEPLAN where CUSTOMERNAME = 'XYZ';
SQL> commit;
Delete Rate Plans by Account and Rate Plan ID
SQL> delete from RATEPLAN where CUSTOMERNAME = 'XYZ' AND ID < 128;
SQL> commit;
Delete Rate Plans by Account and Effective Date
SQL> delete from RATEPLAN where CUSTOMERNAME='XYZ' and EFFECTIVEDATE < TO_TIMESTAMP('2014-12-01','YYYY-MM-DD');
SQL> commit;
Delete Rate Plan by Rate Plan ID
SQL> delete from RATEPLAN where ID = xxx;
SQL> commit;
Delete All Rate Plans in the Database
SQL> delete from RATEPLAN;SQL> commit;