Storable class to XML representation

I recently noticed this application that converts the storable class representation in database to XML format. Its syntax is:

storableclasstoxml [-h] [-r <filename> [-o <objectname>]]

-h : display help information

-r <filename> : retrieves storable class for

      1. /service and its sub classes

      2. /event and its sub classes

      3. /account class

      and write it to <filename>

-r <filename> -o <objectname> : retrieves storable class for

      <objectname> and write it to <filename>.

      <objectname> can be /service, /event, /account,

      /service/xyz or /event/xyz where xyz is a sub class

      of either /service or /event.  Multiple object types

      can be passed with comma(',') separated.

I'm not sure in which all scenarios this XML representation will be useful. If anyone get a chance to explore this or if someone has already explored, please share with me.

Some useful BRM flists

Here are some useful flists which will be useful for the POCs during development of Oracle BRM based applications.
1). Search flist to find the service and account objects (or POIDS) using the login field value when login is available in the the service


0 PIN_FLD_POID                      POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_FLAGS                      INT [0] 256
0 PIN_FLD_INDEX_NAME                 STR [0] "service_login_i"
0 PIN_FLD_PARAMETERS                 STR [0] "telco/voice"
0 PIN_FLD_TEMPLATE                   STR [0] "select X from /service/$1 where F1 = V1 and  F2 = V2 "
0 PIN_FLD_RESULTS                  ARRAY [0] allocated 7, used 7
1     PIN_FLD_POID                  POID [0] NULL
1     PIN_FLD_ACCOUNT_OBJ           POID [0] NULL
1     PIN_FLD_LOGIN                  STR [0] NULL
1     PIN_FLD_PASSWD                 STR [0] NULL
1     PIN_FLD_PASSWD_EXPIRATION_T TSTAMP [0] (0) 01/01/1970 05:30:00:000 AM
1     PIN_FLD_PASSWD_STATUS         ENUM [0] 0
1     PIN_FLD_STATUS                ENUM [0] 0
0 PIN_FLD_ARGS                     ARRAY [1] allocated 1, used 1
1     PIN_FLD_POID                  POID [0] 0.0.0.1 /service/ip -1 0
0 PIN_FLD_ARGS                     ARRAY [2] allocated 1, used 1
1     PIN_FLD_LOGIN                  STR [0] "your-login"

2). Search flist to find the service and account objects (or POIDS) using the alias field value when login is not  available in the the service

0 PIN_FLD_POID                      POID [0] 0.0.0.1 /search -1 0

0 PIN_FLD_FLAGS                      INT [0] 256

0 PIN_FLD_INDEX_NAME                 STR [0] "service_alias_list_name_i"

0 PIN_FLD_PARAMETERS                 STR [0] "telco/voice"

0 PIN_FLD_TEMPLATE                   STR [0] "select X from /service/$1 where F1 = V1 and  F2 = V2 "

0 PIN_FLD_RESULTS                  ARRAY [0] allocated 7, used 7

1     PIN_FLD_POID                  POID [0] NULL

1     PIN_FLD_ACCOUNT_OBJ           POID [0] NULL

1     PIN_FLD_LOGIN                  STR [0] NULL

1     PIN_FLD_PASSWD                 STR [0] NULL

1     PIN_FLD_PASSWD_EXPIRATION_T TSTAMP [0] (0) 01/01/1970 05:30:00:000 AM

1     PIN_FLD_PASSWD_STATUS         ENUM [0] 0

1     PIN_FLD_STATUS                ENUM [0] 0

0 PIN_FLD_ARGS                     ARRAY [1] allocated 1, used 1

1     PIN_FLD_POID                  POID [0] 0.0.0.1 /service/telco/gsm/telephony -1 0

0 PIN_FLD_ARGS                     ARRAY [2] allocated 1, used 1

1     PIN_FLD_ALIAS_LIST           ARRAY [*] allocated 1, used 1

2         PIN_FLD_NAME               STR [0] "9830000011"

3). Flist to create customer without plan/deals/products (PCM_OP_CUST_COMMIT_CUSTOMER)

0 PIN_FLD_POID POID [0]  0.0.0.1 /plan 9198 0

0 PIN_FLD_ACCTINFO ARRAY [0]  

1 PIN_FLD_POID POID [0] 0.0.0.1 /account -1 0

0 PIN_FLD_PAYINFO ARRAY [0]  

1 PIN_FLD_POID POID [0] 0.0.0.1 /payinfo/invoice -1 0

1 PIN_FLD_PAY_TYPE ENUM [0] 10001

1 PIN_FLD_INHERITED_INFO      SUBSTRUCT [0]

2 PIN_FLD_INV_INFO ARRAY [0]

3 PIN_FLD_ADDRESS   STR [0] "Castro Street"

3 PIN_FLD_CITY   STR [0] "San Fransisco"

3 PIN_FLD_COUNTRY   STR [0] "US"

0 PIN_FLD_NAMEINFO ARRAY [1]  

1 PIN_FLD_LAST_NAME STR [0] "LastN"

1 PIN_FLD_FIRST_NAME STR [0] "FirstN"

1 PIN_FLD_ADDRESS STR [0] "113, Castro Street"

1 PIN_FLD_CITY STR [0] "San Fransisco"

1 PIN_FLD_STATE STR [0] "CA"

1 PIN_FLD_COUNTRY STR [0] "US"

1 PIN_FLD_ZIP STR [0] "53038"

1 PIN_FLD_COMPANY STR [0] "ABC"

1 PIN_FLD_EMAIL_ADDR STR [0] "test@test.com"

0 PIN_FLD_SERVICES ARRAY [0]  

1 PIN_FLD_SERVICE_OBJ POID [0] 0.0.0.1 /service/telco/gsm/telephony -1 0

Enabling pin_virtual_time first time after BRM 7.4 installation

During development many times we need pin_virtual_time utility to move the BRM time forward to test many scenarios like billing, invoicing, dunning et.c.
After installing the BRM 7.4 components in the server, generally the pin_virtual_time_file is not created and also the entry is commented in almost all the component's pin.conf file.
Here are some tips to enable the pin_virtual_time (may be useful to novice BRM developers/testers)

1). Uncomment pin_virtual_time entry in all the components' pin.conf file. You may run the following command :
find  $PIN_HOME/ -name "pin.conf" | xargs sed -i 's/# - - pin_virtual_time/- - pin_virtual_time/g'
2). Restart the BRM server
3). Goto the $PIN_HOME/sys/test directory and execute
    $> pin_virtual_time
    This will create the pin_virtual_time_file and from now on you can move the time.

PIN_VIRTUAL_TIME USAGE
usage: pin_virtual_time [-h|-H|-?] [-f filename] [-m mode [value]]|[-i interval]
    -h, -H, -? print this message.
    -m ... set pin_virtual_time to mode, nothing or -i query pin_virtual_time
    mode is 0 (regular), 1 (fixed) or 2 (running)
    value as [mmdd]HHMM[[cc]yy][.SS]
    interval is time between queries in seconds

Search flist to find billinfo and account poid using account_no

Following is a complex search flist for finding BILLINFO poid and ACCOUNT poid using the ACCOUNT_NO field. As such this is used while running the bill utility during test runs but consider it as a sample for searching and extracting field values from two different storable class in single search. Generally, using a simple flist this is not achievable.

0 PIN_FLD_POID                      POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_FLAGS                      INT [0] 768
0 PIN_FLD_TEMPLATE                   STR [0] " select X from /account 1, /billinfo 2 where 1.F1 = 2.F2 and 1.F3 = V3 "
0 PIN_FLD_RESULTS                  ARRAY [*] allocated 2, used 2
1     PIN_FLD_POID                  POID [0] NULL
1     PIN_FLD_LINKED_OBJ           ARRAY [2] allocated 3, used 3
2         PIN_FLD_ACCOUNT_OBJ       POID [0] NULL
2         PIN_FLD_LINK_DIRECTION    ENUM [0] 1
2         PIN_FLD_EXTRA_RESULTS    ARRAY [*] allocated 1, used 1
3             PIN_FLD_POID          POID [0] NULL
0 PIN_FLD_ARGS                     ARRAY [1] allocated 1, used 1
1     PIN_FLD_POID                  POID [0] NULL
0 PIN_FLD_ARGS                     ARRAY [2] allocated 1, used 1
1     PIN_FLD_ACCOUNT_OBJ           POID [0] NULL
0 PIN_FLD_ARGS                     ARRAY [3] allocated 1, used 1
1     PIN_FLD_ACCOUNT_NO             STR [0] "0.0.0.1-90356"

I will provide the explaination shortly.

Utility to move pin_virtual_time(mode 1) and execute pin_bill_day

It sounds like huh, why should I need a script for changing virtual time or executing pin_bill_day but it has utility. Specially for BRM testing, there are scenarios where the movement of time followed by execution of pin_bill_day and pin_ledger_report are required, e.g.
   - Testing of invalid (soft declined/hard declined) credit cards
   - Checking the monthly bill for a certain period of time
   - Accounting validation by looking at General Ledger(GL) reports of certain months
   - Checking the expiration of the non-currency date based resources valid for a year
   - and so on

So this script has the following modes of operation when you will execute it :

$> perl tbill.pl
Current time is : mode 1  1367341200  Tue Apr 30 10:00:00 2013
mode 1  1367341200  Tue Apr 30 10:00:00 2013
Select from the following :
        1. Increment time to next day
        2. Increment time to a date
        3. Move time to a date by incrementing one day at a time
        4. Move time to a date by incrementing one month at a time :
        5. Move time to a date by incrementing 15 days at a time
        Your Selection(1|2|3|4|5) :

 Option1 : Increment time to next day and execute pin_bill_day and pin_ledger_report
Option2 : Increment time to a particular date in future like some date after few months in the same year or next year
Option3 : Increment time to a date in future by executing pin_bill_day and pin_ledger_report on each day of that period
Option4 : Increment time to few months by moving one month at a time and executing pin_bill_day and pin_ledger_report each month
Option5 : Increment time to a date by moving 15 day at once and executing pin_bill_day and pin_ledger_report



Here is the link of the script :

Guidelines to write a new FM in BRM

Creating a new FM

Steps to create a new FM in a CM :

1. Define new opcodes
2. Write a function to implement the new opcode
3. Write a program to map opcodes to functions
4. Create a shared library on UNIX i.e. ".so" file
5. Configure the new FM as part of the CM

1. Defining new opcodes

You must define your custom opcodes, with their numbers, in a header file and execute parse_custom_ops_fields.pl on the file. parse_custom_ops_fields.pl creates an extension to the pcm_tbls.c file. Client applications use pcm_tbls.c to map a field or opcode to its number.

Portal opcodes and their numbers are defined in the ops/*.h files in the <BRM_HOME>/include directory.
To pass new opcodes from a client application to a new FM, use the PCM_OP macro.

To define a new opcode:
  a). Create a header file and define your new opcodes by using this format:
      #define    opcode_name_1    opcode_number_1

      For example, you might create a header file named my_opcodes.h with these definitions :

      #define MY_OP_SET_AGE     100001
      #define MY_OP_SET_LANGUAGE  100002

       Important: Numbers upto 100000 are reserved for use by BRM.


  b). Run the parse_custom_ops_fields.pl perl script by using this syntax:

       parse_custom_ops_fields.pl -L language -I input -O output -P java_package

 c). For the applications written in PCM C or PCM COM, create an entry using the following format in the pin.conf each application :
      - - ops_fields_extension_file ops_flds_ext

      Where ops_flds_ext is the file name and location of the memory-mapped extension file that was created by the parse_custom_ops_fields.pl script.
       Make sure that you included your header file both in the application that is calling the opcode and in the custom FM.


2. Writing a function to implement a new opcode 

     To implement a new opcode in BRM, you have to write a new function that calls the base system opcodes to access the DM. The new function then becomes part of the new FM shared library in UNIX.

     The new function you are implementing should conform to the PCM_OP calling convention.
     Use the PCM_OP_* reference pages as checklists and templates to determine what your custom function must implement. Pay particular attention to the input and output flists.

Important If you are adding a new function in the fm_utils module that can be called outside the module, add a prototype of that function in the fm_utils.h

3. Creating an opcode-to-function mapping file 

    You create a configuration program fm_*_config.c to map an opcode name to the function that implements it. This configuration file is read when a dynamic library is created, and the mapping information is stored in it.  When a parent CM is inititalized, it configures the opcode-function pairs into itself, and each child CM inherits the same configuration as its parent CM.

   The configuration program contains an array of struct cm_fm_config for each opcode and its corresponding function and the config function at the end.
   For a definition of this struct (cm_fm_config), see the cm_fm.h file in the BRM_SDK_HOME/include directory.
   The following example shows an opcode-to-function configuration file.

Important Include you ".h" file with your new custom opcodes

#ifndef lint
static  char    Sccs_id[] = "@(#) fm_custom_config.c 1.0 24 May 2011 %";
#endif
#include <stdio.h> /* for FILE * in pcm.h */
#include "ops/base.h"
#include "pcm.h"
#include "cm_fm.h"
#include "ops/my_header.h"

/*******************************************************************
* Opcodes handled by this FM.
*******************************************************************/
struct cm_fm_config my_config[] = {
/* opcode as a u_int, function name (as a string) */
{ MY_FIRST_OPCODE , "op_my_opcode" },
{ 0, (char *)0 }
};

void * my_config_func()
{
return ((void *) (my_config));
}


When the CM gets a MY_FIRST_OPCODE opcode through the PCM_OP call from a client application, the CM looks up at this table and calls the op_cust_create_acct() function.


4. Adding a new FM module to the CM configuration file 

The configuration file contain the names of the shared libraries that implement the base and custom opcodes. It also contains the names of the corresponding configuration files that contain the opcode-to-function mappings.

Use the entries for the system FMs in the default CM configuration file pin.conf in the BRM_HOME/sys/cm as an example to add your custom FM entries. For example, the typical entries for custom FMs look like:

- cm fm_module BRM_HOME/lib/my_fm.so my_config - pin

In this example, I'm assuming that the new dynamic library that implements the new custom FM my_fm.so is copied to BRM_HOME/lib directory. "my_config" is the name of the configuration implementation that contains the name-to-function mapping of the new custom OpCode.

5. Compiling and linking a custom FM

Use the libraries in the Portal_SDK_home/lib for linking.
Each custom FM must be created as a shared library on UNIX

Important classes in Oracle BRM

In BRM, account/customer related data such as names, addresses, profiles, current account balances, group charge sharing, and account hierarchy are stored. Plans/deals/products can be purchased by the customer which gets associated with their account. The customers can purchase one or more services like GSM/GPRS et.c. (in case the subscriber is a telecom subscriber).  Some of the key or most important BRM classes are:

/ACCOUNT :  This is one of the most important and key storable class of BRM that stores information about the customer. The customer information includes their contact info,status, locale and tax related information. Account class is mapped to account_t table and is uniquely identified by POID. Each account has a default balance group and is associated with the instances of billinfo, balance group,payment method, purchased products, purchased discounts and services.

/BALANCE_GROUP :  Stores the balance information for each currency and non-currency resources in an account . A balance group includes one or more sub-balances for each resource. The sub-balance contains the current amount, resource type, validity dates for the resource, rollover data, and sub-balance contributors. This storable class is mapped with the  bal_grp_t table in BRM database.

/BILL : This storable class contains the information for the bills generated for each customer based on the products they purchase or the usage they do. It includes information such as the amount due, amount adjusted, currency and bill number. A /bill object is created for each account that have a due at the beginning of a billing cycle. It contains information for the invoice and the billinfo object with which it is associated. It maps with the  bill_t  table in BRM database.

/BILLINFO: Stores all billing, payment method, accounting cycle, and hierarchy information necessary to bill an account. A default /billinfo object is created for every account. If the billinfo is subordinate, the /billinfo object points to the parent account and the parent account’s /billinfo object. It maps to billinfo_t table in BRM database.


/INVOICE: Stores a customer invoice and information about the invoice, such as the bill it is associated with. Each /bill object can have a corresponding /invoice object. It maps to invoice_t table in BRM database.

/ITEM: Created to bundle events, this table summarizes billable item activity by type. It maps to item_t table in BRM database. Rows in this table are added for each row in the BILL_T table.

/PAYINFO: Stores generic payment method information for an account. It maps to payinfo_t  table in BRM database.


/DEAL: Stores information about a deal.When you use Pricing Center and connect to the database, all /deal objects in the database are shown in Pricing Center. It maps to DEAL_T table in BRM database.


/PRODCUT: Stores the information for a single product. Maps to product_t table.


/DEVICE: Stores information about devices. There is a separate /device object for every device managed by BRM. Generic data applicable to all devices is stored in the parent /device object. Subclasses such as /device/num store information specific to a particular device type. It maps to device_t table in BRM database.


/PROFILE: Abstract class to support custom account information. To use a /profile object, always create a subclass. You can link an account to any number of /profile objects. Maps to profile_t table.


/EVENT : Abstract class to record system-initiated and user-initiated events. Event objects are related to a specific service or to an account. An event includes generic information such as start and end times as well the balance impacts that are incurred by the account due to this event. The /event object points to the account balance group that is impacted.Maps to event_t table in BRM database. The event class stores the event balance data in another table event_bal_impacts_t.


/PURCHASED_PRODUCT : Contains an entry for each product owned by an account at the time of conversion.Maps to purchased_product_t table in the BRM database.



/PURCHASED_DISCOUNT: Contains an entry for each discount owned by an account at the time of conversion.Maps to purchased_discount_t table in the BRM database.

/SERVICE : Stores generic service type information for accounts. There is one row in this table for each applicable service for each entry in ACCOUNT_T. In addition to a row in this table, a row must be created in the service type table, such as IP service or email. Maps to service_t table in the BRM database.


/CONFIG: Base class for configuration objects. Subclasses hold specific configuration information for various features, for example, /config/beid defines currency and non-currency resources. Maps to config_t  table.

Custom fields in BRM

Oracle Communications Billing and Revenue Management offers two methods for creating, editing and deleting custom fields and storable classes. One approach is by using Storable Class Editor, part of the Developer Center application, and the other is by using SDK opcodes.

This post will address manipulating custom fields with SDK opcodes.

Before beginning, one change needs to be made first: making the data dictionary writable. Here’s how to do it:

1. Open the DM Oracle configuration file ($BRM_HOME/sys/dm_oracle/pin.conf) in a text(e.g. vi) editor.

2. Enable field manipulation in the data dictionary, by setting the following entry to 1:-

      dm dd_write_enable_fields 1

Our objectives are as follows:

1. To create a custom field

2. To edit the custom field description

3. To delete the custom field from the database

4. To make the custom field available to BRM

1. Creating a New Custom Field

Creating a new custom field and committing it to the database can be accomplished with SDK opcodes or with a pin deploy utility. As developers know, SDK opcodes provide a more flexible way to create, edit, and delete custom fields at the development stage. The pin deploy utility, on the other hand, uses PODL (Portal Object Definition Language) to export and import  field and storable class definitions. This is more useful, especially at the administrative level, because the process can be streamlined by putting field definitions into source code management and thus lower any possibility of damaging the Oracle BRM production database data dictionary.

The following SDK opcodes can be used to manage field specifications:

i. PCM_OP_SDK_SET_FLD_SPECS – create or modify a field,

ii. PCM_OP_SDK_GET_FLD_SPECS – retrieve a field specs,

iii. PCM_OP_SDK_DEL_FLD_SPECS – delete a field.

To create a field, it’s necessary to write an input flist for PCM_OP_SDK_SET_FLD_SPECS opcode:

0 PIN_FLD_POID          POID [0] 0.0.0.1 /dd/fields 0 0

0 PIN_FLD_FIELD      ARRAY [0]

1    PIN_FLD_DESCR          STR [0] “custom field for holding a VAT number”

1    PIN_FLD_FIELD_NAME      STR [0] “C_FLD_VAT_NUMBER”

1    PIN_FLD_FIELD_NUM      ENUM [0] 10000

1    PIN_FLD_FIELD_TYPE      INT [0] 5

Now, verify that the field created exists in the database data dictionary by feeding the following input flist to PCM_OP_SDK_GET_FLD_SPECS opcode:

0 PIN_FLD_POID      POID [0] 0.0.0.1 /dd/objects 0 0

0 PIN_FLD_FIELD    ARRAY [0]

1    PIN_FLD_FIELD_NAME    STR [0] “C_FLD_VAT_NUMBER”

2. Editing the Custom Field Description

Now, having confirmed that the new custom field exists, the description can be altered by calling PCM_OP_SDK_SET_FLD_SPECS opcode with the following input flist:

0 PIN_FLD_POID          POID [0] 0.0.0.1 /dd/fields 0 0

0 PIN_FLD_FIELD      ARRAY [0]

1    PIN_FLD_DESCR          STR [0] “custom field – VAT number”

1    PIN_FLD_FIELD_NAME      STR [0] “C_FLD_VAT_NUMBER”

1    PIN_FLD_FIELD_NUM      ENUM [0] 10000

1    PIN_FLD_FIELD_TYPE      INT [0] 5

Again, the field’s specifications can be retrieved so as to verify that the change has been accomplished.

After that, delete the custom field from the database.

Finally, assume a mistake has been made, and it’s necessary to delete a field. Provide the following input flist to PCM_OP_SDK_DEL_FLD_SPECS opcode, deletes the field:

0 PIN_FLD_POID        POID [0] 0.0.0.1 /dd/fields 0 0

0 PIN_FLD_FIELD      ARRAY [0]

1    PIN_FLD_FIELD_NAME      STR [0] “C_FLD_VAT_NUMBER”

If the opcode for retrieving the field’s specifications returns an empty flist (only POID obj),then that signifies the field was not found in the database data dictionary.

3. Making the Custom Fields Available to BRM

Standard practice dictates that everything custom developed goes into the BRM_HOME/custom directory, and since, in this case, BRM_HOME/include/pin_flds.h, is being extended, a new custom header file BRM_HOME/custom/include/custom_flds.h. needs to be created.

Now, the new custom field can be introduced by appending the following line to custom_flds.h:

#define MY_CUSTOM_FLD PIN_MAKE_FLD(PIN_FLDT_STR, 10000)

The line above defines a custom field named “MY_CUSTOM_FLD” with data type “PIN_FLDT_STR” (STR stands for string) and unique identifier “10000″ (in regards to BRM_HOME/include/pin_flds.h).

Data types are static, and defined by the BRM version you are running; please see BRM_HOME/include/pcm.h for definitions.

Next, run the parse_custom_ops_fields.pl perl script on the custom_flds.h using this command:

$BRM_HOME/bin/parse_custom_ops_fields.pl -L pcmc -I custom_flds.h -O $BRM_HOME/custom/include/custom_mapping.m

And finally, include the following line to every pin.conf where it’s desired that these field be seen: –

- ops_fields_extension_file ${BRM_HOME}/custom/include/custom_mapping.m

NOTE: When introducing new functionalities using custom fields, it is necessary to include the custom_flds.h header file in the FMs that use these fields

Some BRM SQLs

Here I'm sharing few SQLs which are very useful for specific BRM scenarios during debugging and testing. Please contribute if you have some SQLs apart from the listed ones :

1). Searching the resources associated with an account with their balances:

SELECT BG.OBJ_ID0,BG.REC_ID,BG.REC_ID2,CB.NAME,CB.SYMBOL, DATE '1970-01-01'+BG.VALID_FROM/86400,DATE '1970-01-01'+BG.VALID_TO/86400,BG.CURRENT_BAL FROM BAL_GRP_SUB_BALS_T BG,CONFIG_BEID_BALANCES_T CB WHERE BG.OBJ_ID0=(SELECT POID_ID0 FROM BAL_GRP_T WHERE ACCOUNT_OBJ_ID0=30651) AND BG.REC_ID2=CB.REC_ID

Change the ACCOUNT_OBJ_ID0 and use this query.


2). Getting discount details with discount model

select d.name,dum.EVENT_TYPE, -- Discount Name
idm.CODE, -- discount model
idt.CODE "Trigger", idcndn.COND_EXPRESSION ||' '||idcndn.COND_OPERATOR||' '||idcndn.CONDITION_VALUE "Trigger value", -- Trigger details
idmstr.CODE "DiscountMaster", iddtl.ZONEMODEL, iddtl.IMPACT_CATEGORY, iddtl.SERVICECODE, iddtl.SERVICECLASS, -- Master details
iddtl.TIMEMODEL, iddtl.TIMEZONE, iddtl.PRICEMODEL, iddtl.RESSOURCE, iddtl.RUM, iddtl.RECORDTYPE, -- Master details cont
iddtl.USAGETYPE,iddtl.USAGECLASS, iddtl.RATE_PLAN, iddtl.RATE_TAG, iddtl.GLID, -- Master details cont
idrl.CODE "DISC RULE", idrl.DRUM_EXPRESSION, idrl.RULE_TYPE, idrl.DRUM_TYPE, -- Discount rule
idstp.CODE, idstp.RANK, idstp.THRESHOLD_FROM, idstp.THRESHOLD_TO, -- Discount step
idblimpct.RESOURCE_ID, idblimpct.DISCOUNT_OWNER_FLAG, idblimpct.DISCOUNT_TYPE, idblimpct.BASE_EXPRESSION, -- discount balance impact
idblimpct.GRANT_BEAT, idblimpct.GRANT_PRORATEDBEAT, idblimpct.GRANT_UNITS, idblimpct.EVENT_BALANCE_ID -- discount balance impact
from discount_t d, discount_usage_map_t dum, IFW_DISCOUNTMODEL idm,
IFW_DSCMDL_CNF idmc, IFW_DSCTRIGGER idt, IFW_DISCOUNTRULE idrl, IFW_DSCCONDITION idcndn, IFW_DISCOUNTMASTER idmstr, IFW_DISCOUNTDETAIL iddtl,
IFW_DISCOUNTSTEP idstp, IFW_DSCBALIMPACT idblimpct--, IFW_DSCMDL_CNF idscnf
where d.POID_ID0=dum.OBJ_ID0 and dum.DISCOUNT_MODEL=idm.CODE and idmc.DISCOUNTMODEL=idm.DISCOUNTMODEL
and idmc.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmc.DISCOUNTRULE=idrl.DISCOUNTRULE
and idcndn.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmstr.DISCOUNTMASTER=idrl.DISCOUNTMASTER
and idmstr.DISCOUNTMASTER=iddtl.DISCOUNTMASTER and idstp.DISCOUNTRULE=idrl.DISCOUNTRULE
and idblimpct.DISCOUNTSTEP=idstp.DISCOUNTSTEP ;






3). Discount details with discount model selector

select d.name,dum.EVENT_TYPE, -- Discount Name
idm.CODE, -- discount model
idt.CODE "Trigger", idcndn.COND_EXPRESSION ||' '||idcndn.COND_OPERATOR||' '||idcndn.CONDITION_VALUE "Trigger value", -- Trigger details
idmstr.CODE "DiscountMaster", iddtl.ZONEMODEL, iddtl.IMPACT_CATEGORY, iddtl.SERVICECODE, iddtl.SERVICECLASS, -- Master details
iddtl.TIMEMODEL, iddtl.TIMEZONE, iddtl.PRICEMODEL, iddtl.RESSOURCE, iddtl.RUM, iddtl.RECORDTYPE, -- Master details cont
iddtl.USAGETYPE,iddtl.USAGECLASS, iddtl.RATE_PLAN, iddtl.RATE_TAG, iddtl.GLID, -- Master details cont
idrl.CODE "DISC RULE", idrl.DRUM_EXPRESSION, idrl.RULE_TYPE, idrl.DRUM_TYPE, -- Discount rule
idstp.CODE, idstp.RANK, idstp.THRESHOLD_FROM, idstp.THRESHOLD_TO, -- Discount step
idblimpct.RESOURCE_ID, idblimpct.DISCOUNT_OWNER_FLAG, idblimpct.DISCOUNT_TYPE, idblimpct.BASE_EXPRESSION, -- discount balance impact
idblimpct.GRANT_BEAT, idblimpct.GRANT_PRORATEDBEAT, idblimpct.GRANT_UNITS, idblimpct.EVENT_BALANCE_ID -- discount balance impact
from discount_t d, discount_usage_map_t dum, IFW_MODEL_SELECTOR ims, IFW_SELECTOR_RULESET isrlst, IFW_DISCOUNTMODEL idm,
IFW_DSCMDL_CNF idmc, IFW_DSCTRIGGER idt, IFW_DISCOUNTRULE idrl, IFW_DSCCONDITION idcndn, IFW_DISCOUNTMASTER idmstr, IFW_DISCOUNTDETAIL iddtl,
IFW_DISCOUNTSTEP idstp, IFW_DSCBALIMPACT idblimpct,
IFW_SELECTOR_RULE_LNK isrl, IFW_SELECTOR_RULE isl, IFW_SELECTOR_DETAIL isd
where d.POID_ID0=dum.OBJ_ID0 and dum.MODEL_SELECTOR=ims.NAME and idmc.DISCOUNTMODEL=idm.DISCOUNTMODEL
and idmc.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmc.DISCOUNTRULE=idrl.DISCOUNTRULE
and idcndn.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmstr.DISCOUNTMASTER=idrl.DISCOUNTMASTER
and idmstr.DISCOUNTMASTER=iddtl.DISCOUNTMASTER and idstp.DISCOUNTRULE=idrl.DISCOUNTRULE
and idblimpct.DISCOUNTSTEP=idstp.DISCOUNTSTEP
and dum.MODEL_SELECTOR=ims.NAME
and isrlst.MODEL_SELECTOR=ims.MODEL_SELECTOR
and isrlst.MODEL=idm.DISCOUNTMODEL
and isrlst.SELECTOR_RULE=isrl.SELECTOR_RULE
and isrl.SELECTOR_RULE=isl.SELECTOR_RULE and isrl.SELECTOR_DETAIL=isd.SELECTOR_DETAIL

4). SQL to find pipeline rate plan details with product poid
select p.name,pum.EVENT_TYPE, isrl.*, ipm.*, ims.*
from product_t p, PRODUCT_USAGE_MAP_T pum, RATE_PLAN_T rp, IFW_RATEPLAN irp,
IFW_RATEPLAN_VER irpv, IFW_RATEPLAN_CNF irpc, IFW_MODEL_SELECTOR ims, IFW_SELECTOR_RULESET isr,
IFW_SELECTOR_RULE isrl, IFW_PRICEMODEL ipm
where p.POID_ID0=pum.OBJ_ID0 and pum.RATE_PLAN_CODE=rp.CODE and rp.CODE=irp.CODE
and irp.RATEPLAN=irpv.RATEPLAN and irpv.RATEPLAN=irpc.RATEPLAN and irpc.MODEL_SELECTOR=ims.MODEL_SELECTOR
and isr.MODEL_SELECTOR=ims.MODEL_SELECTOR
and isrl.SELECTOR_RULE=isr.SELECTOR_RULE
and isr.MODEL=ipm.PRICEMODEL
and p.poid = <<PRODUCT_POID>>;


5). SQL to find discount details

select dsc.poid_id0, dsc.NAME,
decode(idb.discount_type,'P',idb.GRANT_UNITS*100||'%', idb.GRANT_UNITS||' Fixed') discount
, idd.*
from
DISCOUNT_T dsc, DISCOUNT_USAGE_MAP_T dum
,IFW_DISCOUNTMODEL idm
,IFW_DSCMDL_CNF idmc
,IFW_DISCOUNTRULE idr
,IFW_DISCOUNTSTEP ids
,IFW_DSCBALIMPACT idb
,IFW_DISCOUNTDETAIL idd
where dsc.POID_ID0=dum.OBJ_ID0
and idm.code=dum.DISCOUNT_MODEL
and idmc.discountmodel = idm.discountmodel
and idr.DISCOUNTRULE = idmc.DISCOUNTRULE
and ids.discountrule = idr.discountrule
and idb.discountstep = ids.discountstep
and idr.DISCOUNTMASTER = idd.DISCOUNTMASTER
--and dsc.name like '%<<DISCOUNT_PATTERN%'
and dum.EVENT_TYPE='<<EVENT_TYPE>>'
;

BRM - GL (General Ledger)

Billed and unbilled usage fees

Billed and unbilled purchase and cancellation fees

If a purchase or cancel event occurs after the billing date but before the G/L reporting date, BRM G/L reports the revenue as unbilled.

Cycle arrears and Forward Arrears

1.     Billed cycle arrears fees

2.     Billed and unbilled cycle forward arrears fees

Sample Ledger Report

DD Objects/Fields queries in BRM

This is a simple but very useful search to find the table name for a storable class in BRM. It is many times a difficult job specially in highly customized environments. Execute the following query by connecting to the BRM database :

SQL> select dof.SM_ITEM_NAME from dd_objects_t do,dd_objects_fields_t dofwhere do.OBJ_ID0=dof.OBJ_ID0 and do.NAME like '<CLASSNAME>' and dof.SM_ITEM_NAME not like ':%' and dof.SM_ITEM_NAME not like 'au_%'

 Replace the <CLASSNAME> with the exact class for which you want to search the table, e.g.

SQL> select dof.SM_ITEM_NAMEfrom dd_objects_t do,dd_objects_fields_t dof where do.OBJ_ID0=dof.OBJ_ID0 and do.NAME like '/service' and dof.SM_ITEM_NAME not like ':%' and dof.SM_ITEM_NAME not like 'au_%'

Similarly, we can search the storable class name from the table name. Use the following query :

SQL> select dof.SM_ITEM_NAME,do.name from dd_objects_t do,dd_objects_fields_t dof where do.OBJ_ID0=dof.OBJ_ID0 and dof.SM_ITEM_NAME like 'account_t'

Following query can be used to find the storable classes where a BRM field is used:



select do.name from dd_objects_t do,dd_objects_fields_t dof where dof.field_name ='<FIELD_NAME>' and do.obj_id0=dof.obj_id0;


e.g.,


select do.name from dd_objects_t do,dd_objects_fields_t dof where dof.field_name ='PIN_FLD_ITEM_NO' and do.obj_id0=dof.obj_id0;

Adding new element in a storable class

To add a new element in a storable class, you need to use PCM_OP_WRITE_FLDS with the PCM_OPFLG_ADD_ENTRY as flag. Provide the element in the input flist with the new element id and execute PCM_OP_WRITE_FLDS with the flist. e.g., using testnap

testnap>r << xx 1
0 PIN_FLD_POID           POID [0] 0.0.0.1 /some_class 1111
0 NEW_ARRAY_NAME ARRAY [10]
1   ARRAY_FIELD1   STR [0] "VALUE1"
1   ARRAY_FIELD2   STR [0] "VALUE2"
...
...
...
xx
testnap>xop PCM_OP_WRITE_FLDS 32 1
0 PIN_FLD_POID           POID [0] 0.0.0.1 /some_class 1111

For your reference, the value of PCM_OPFLG_ADD_ENTRY is 0x20 (hexadecimal) which is equal to 32 in decimal.

Billing Applications vs Billing Opcodes

Here is a rare mapping between the Billing Applications and Billing Opcodes :

Billing Applications	Billing Opcodes
pin_deferred_act	PCM_OP_ACT_SCHEDULE_EXECUTE
pin_bill_accts	PCM_OP_BILL_MAKE_BILL
pin_cycle_fees	PCM_OP_SUBSCRIPTION_CYCLE_FORWARD
	PCM_OP_SUBSCRIPTION_PURCHASE_FEES
	PCM_OP_SUBSCRIPTION_CANCEL_PRODUCT
pin_collect	PCM_OP_PYMT_COLLECT
	PCM_OP_PYMT_CHARGE_CC
	PCM_OP_PYMT_CHARGE_DD
pin_refund	PCM_OP_PYMT_COLLECT
	PCM_OP_PYMT_CHARGE_CC
	PCM_OP_PYMT_CHARGE_DD
pin_deposit	PCM_OP_PYMT_COLLECT
	PCM_OP_PYMT_CHARGE_CC
	PCM_OP_PYMT_CHARGE_DD
pin_inv_accts	PCM_OP_INV_MAKE_INVOICE
	PCM_OP_INV_VIEW_INVOICE
	PCM_OP_INV_POL_FORMAT_VIEW_INVOICE

Partial (Short and Long) Accounting Cycles !

When the accounting cycle date is changed in the middle of an accounting cycle, the new date does not take effect until after the current accounting cycle is over. This results in a gap of time between the end of the old accounting cycle and the start of the new accounting cycle. For example, for a 30-day month, if the current accounting cycle ends on the 15th and the new cycle starts on the 1st, there is a gap of 15 days between the end of the old cycle and the start of the new cycle.

Short Accounting Cycle
By default, the BRM system treats extra days,between the old and new accounting cycle, of 15 days as a short, but complete accounting cycle. At the end of that short cycle, the accounting cycle resumes its normal monthly cycle.

Long Accounting Cycle
If the short cycle is less than 15 days, a long cycle is created instead. In that case, the extra days are added to the next one-month accounting cycle. This results in a long cycle with the start date of the old cycle and the end date of the new cycle.

 

Monthly charges are prorated for accounting cycles less than or greater than one month.

Short and long cycles with new accounts

A short or long cycle can also occur when a customer registers and the billing DOM is different from the day of month when they register. For example, your company might require that all customers be billed on the first day of the month. If a customer registers on January 26, by default the first bill is created on March 1. To bill the customer on February 1, you need to change the default partial billing cycle to short.

How BRM calculates long billing cycles

By default, BRM uses the following formula to calculate long billing cycles:
Use a short cycle unless one of the following is true:

• Future billing day of month > current billing day of month
                                                 AND
  (Future billing day of month - current billing day of month) < 15
• Future billing day of month < current billing day of month
                                                 AND
  (Current billing day of month - future billing day of month) > 15

Examples:

• If the current billing DOM is 1 and the future billing DOM is 10:

10 > 1 10 - 1 = 9 Use a long cycle.

• If the current billing DOM is 1 and the future billing DOM is 20:

20 > 1 20 - 1 = 19 Use a short cycle.

 

• If the current billing DOM is 10 and the future billing DOM is 1:

1 < 10 10 - 1 = 9 Use a short cycle.

 

• If the current billing DOM is 20 and the future billing DOM is 1:

1 < 20 20 - 1 = 19 Use a long cycle.

 

Implementing a new service (CDMA) in BRM