BRM Errors

Title: Cache entry not found

E Mon Apr  8 16:13:58 2013  <<hostname>>  cm:18233  fm_bill_utils_billing.c(49):1039 1:<<hostname>>:testnap:18231:-136099600:100001:1365405238:1
        Could not find cache entry: "PIN_SEQUENCE_TYPE_PACKAGE_ID"
E Mon Apr  8 16:13:58 2013  <<hostname>>  cm:18233  fm_subscription_purchase_deal.c:805 1:<<hostname>>:testnap:18231:-136099600:100001:1365405238:1
        op_subscription_purchase_deal error
        <location=PIN_ERRLOC_FM:5 class=PIN_ERRCLASS_SYSTEM_DETERMINATE:1 errno=PIN_ERR_NOT_FOUND:3>
        <field num=^Aopsflds:0,0 recid=0 reserved=3 reserved2=0 time(sec:usec)=0:0>
        <facility=0 msg_id=0 version=0>

Resolution: As the cache entry is missing, it means CM could not load few variable, restart cm to fix this.


Title: pricing_admin.pl failed in initializing


-bash-3.2$ ./pricing_admin.pl -init
EBUF: errno=<PIN_ERR_BAD_CRYPT:15> location=<PIN_ERRLOC_DM:4> class=<UNKNOWN:0> field num=<0:0,0> recid=<0> reserved=<0>
0 PIN_FLD_POID                   POID [0] 0.0.0.1 /config/pricing_admin -1 0
0 PIN_FLD_ACCOUNT_OBJ            POID [0] 0.0.0.1 /account 1 0
0 PIN_FLD_HOSTNAME                STR [0] "-"
0 PIN_FLD_NAME                    STR [0] "config_pricing_admin"
0 PIN_FLD_PROGRAM_NAME            STR [0] "-"
0 PIN_FLD_CONFIG_INFO       SUBSTRUCT [0] allocated 6, used 6
1     PIN_FLD_HOSTNAME            STR [0] "sacjoshi-linux"
1     PIN_FLD_LOGIN               STR [0] "INTEGRATE"
1     PIN_FLD_NAME                STR [0] "brm11gdb"
1     PIN_FLD_PASSWD              STR [0] "integrate"
1     PIN_FLD_PORT                INT [0] 1521
1     PIN_FLD_SERVER_TYPE         STR [0] "oracle"
call_opcode: PCM_OP_CREATE_OBJ failed. at ./pricing_admin.pl line 504.
Initializing config object for pricing admin failed.

Resolution: - crypt md5 entry is not enabled, enable this entry in dm_oracle's pin.conf

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.

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.

BRM - GL (General Ledger)

BRM - GL (General Ledger)

GL – An Overview

The general ledger, sometimes known as the nominal ledger, is the main accounting record of a business which uses double-entry bookkeeping. It will usually include accounts for such items as current assets, fixed assets, liabilities, revenue and expense items, gains and losses

The general ledger is a summary of all of the transactions that occur in the company. It is built up by transactions recorded in the general journal.

Billing and Accounting Cycles – An Overview

The accounting cycle compiles all of a customer’s balance impacts and stores them in bill items. The accounting cycle is always monthly.

The billing cycle defines how often to request a payment for the balance impacts contained in the bill items. You can request payments every month, or in any number of complete months, for example, quarterly. Therefore, the accounting cycle and the billing cycle always start on the same date, but can be different lengths.

The length of the billing cycle determines how often to finalize a bill and request payments from customers. The billing cycle length is any whole multiple of the accounting cycle. For example, a monthly billing cycle corresponds to one accounting cycle, and a quarterly billing cycle corresponds to three accounting cycles.

How does GL Cycle behave in conjunction with Billing and Accounting Cycles

GL cycles are quite different from Billing Cycles. They are generally Monthly cycles starting from the 1^st of the month. Billing Cycles may be Bi-Monthly, Quarterly, or Yearly depending on the setting. Accounting Cycles are also monthly.

How does BRM GL work. What all things are it integrated with within the BRM Domain

BRM GL picks up data from the various relevant tables, which gets populated on account of various operations like billing, payments etc. and populate the ledger tables. These records are used for generating reports.

GL code is associated with Balance Impacts. BRM looks at the /journal object which are associated with the events having Balance Impacts, summarizes them and generates the report.

GL Basic Configurations

   GL Chart of Accounts

     GL Chart Of Accounts is a List of GL Accounts which are there in any External GL System.This is necessary to associate an External GL system to BRM GL System by Mapping its GL Accounts to Portal  GL/Ids.

         This is a customizable file. To edit the pin_glchartaccts file, then run the load_pin_glchartaccts utility to load the contents of the file into the /config/gl_chartaccts object in the BRM database. [load_pin_glchartaccts pin_glchartaccts_file ]

                   Location : $PIN_HOME/sys/data/pricing/example/

                   Db object : config_gl_chartaccts_t  (main table), 
                                        config_gl_coa_accts_t (maps GL COA to AR Accounts).

* The COA verifies that the G/L accounts you enter in the G/L IDs are valid

GL Ids

   This is an Unique Identifier in the BRM system which maps the Balance Impacts to an unique Number for the GL system to consider while populating into Journal. It is done to track GL data and use it for generating GL reports and associate it with the type of GL revenue.

Edit the pin_glid file and use the load_pin_glid utility to load the G/L IDs into the BRM database.

                      Location : $PIN_HOME/sys/data/pricing/example/pin_glid

                      DB Object: config_glid_t (Main table), 
                                           config_glid_accts_t (maps GL Ids to AR Accounts)

GLIDs will indicate which GL entries to be recorded in the /journal object and which will be omitted and which will be considered for reports.

    Each G/L ID definition contains the following information:

           •  An identification number
     •  A description, which is displayed in Pricing Center
     •  The tax code used by your tax calculation software(Optional)
     •  The G/L account that collects the revenue data, including the revenue type, for example billed, unbilled, and unearned

You can use one pin_glid file for multiple segments, or you can load multiple pin_glid files, one for each G/L segment.

Account Pair Attributes associated with GLIDs

            GROSS – reports the total of net and discounted revenue.
            DISC – reports the balance impacts of discounted revenue.
            NET – reports the amount of revenue that remains after applying discounts
            TAX – reports the amount of taxes calculated. This data is used for collecting G/L data based on tax codes.
                                 NET = GROSS – (DISC + TAX)
 

GL Segments

   What is it? Why is it necessary?

    GL segments are generally a mechanism/umbrella for mapping accounts. All accounts falling under a particular segment will be picked up while generating ledger and posting.

    Where can this be configured?    

The GL Segment can be included into the BRM system. The pin_glid file under  “$PIN_HOME/sys/data/pricing/example” needs to be configured for the GL segment

                        gl_segment  root segment.child_segment   [no_rollup]

        The File is then loaded by running load_pin_glid utility.

DB Table: config_gl_segment_t is the main table that contains a list of the root segment and the child segment. 

   How does it map into functioning of GL?

    This is used by the GL Functionality to consider the accounts for the ledger and how would they be generated.

You can specify if the data for a segment should be included when you run a report against the parent segment. To do so, you use the no_rollup entry in the pin_glid file to specify that the segment should not be included in the parent report.

Revenue Types

 Billed and Unbilled revenue

o   Usage Fees can occur as both Billed or Unbilled Revenue

o   Purchase and Cancellation of Products and the associated Fees can account for both Billed and Unbilled depending upon their occurrence date

o   Cycle arrears fees are always billed

o   Cycle forward arrear fees may be billed or unbilled depending upon their dates which maybe after billing has been done but before GL cycle end date

o   Non-Rated Events like Payments and Refunds always appear as billed.If a payment or refund event occurs after a G/L reporting date, it is not included in the report

o   Cycle forward fees are always billed except when the cycle forward fee is for a new account

Earned and Unearned revenue

o   Revenue that is earned at the time that a G/L report is run is called earned revenue. Fees applied to usage, purchase, and cancellation of items are always accounted for as earned revenue.

o   Unearned revenue is revenue that is not earned at the time the G/L report is run. Unearned revenue only applies to revenue from cycle forward fees.

o   Previously–billed revenue is revenue that was billed in the previous billing cycle, but recognized in the current G/L cycle.

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

A Bit Deeper into GL Functionality

Journal Object [/journal in BRM]

What is it and Why is it necessary?

          /Journal  object in BRM stores relevant balance impacts related data [like usage events, cycle fees, remittance etc…] which is used by the GL in order to summarize the revenue and write to the DB and publish report.

This is introduced from 7.0 PortalBase onwards.

               

What is stored in this object? Description of the relevant fields.

This object stores the Account information of the user with Revenue Impacting Items [Balance Impacting Events] and the relevant Debit and Credit Information for that Item.

How is it populated and under what conditions.

   Its Populated by using the utility create_journal utility. [for legacy systems]

                            1) create_tmp_journals

                            2) move_into_partitions

How does it map its fields to the Basic Settings of GL?

            Journal is used to populate tables of ledger reports. Ledger Reports gives an idea about the financial health of the company.

Ledger Reports

What are GL reports? Their Importance w.r.t. to Billing

    There are 7 types of GL Reports supported by BRM  based on the revenue types.

• Billed   [all billed earned, billed unearned and prev billed earned] 
• Unbilled  [all unbilled earned and unbilled unearned items] 
• Billed earned 
• Unbilled earned 
• Billed unearned 
• Unbilled unearned 
• Previously billed earned

Billed = billed earned + billed unearned + previously billed earned  

(Revenue for events associated with a pending item is unbilled revenue)

Unbilled = unbilled earned + unbilled unearned 

Revenue for events associated with an open or closed item is billed revenue.

What is the importance of each report and what Balance Impacts and Items are associated with each report.  How is it generated?

Each Report gives us different kind of Information. All revenue earning items which also impacts balances like monthly cycle forward fees, cycle arrears fees, payment items, refund items etc.
This is generated using pin_ledger_report functionality which is there in $PIN_HOME/bin. But generally it is run from $PIN_HOME/apps/pin_billd directory for this requires pin.conf settings.

What are the Input Parameters? What is the importance of each of these parameters.

Basic Input parameters are the Start and End Dates in dd/mm/yyyy formats. These parameters serve as limits to which relevant items should be considered within each period of the reports.

To collect G/L data, you assign a G/L code to each type of balance impact for which you need to track revenue, for example:

• The balance impact for a monthly subscription fee. 
• The balance impact of a GPRS usage event.

You run a report to collect the G/L data, typically once a month.

When you run the report, BRM looks at the /journal objects that contain summaries of revenue from the events for the last month and finds the G/L impact for each event that has a balance impact. The report summarizes the revenue for each type of balance impact.

Setting the A/R Account for the GL-REPORT

i). Edit the pin.conf entry in $PIN_HOME/apps/pin_billd

                 pin_ledger_report     transaction_grouping     100000

ii). Restart services. 
iii).  General PIN_LEDGER_REPORT running Params

a. Run_report

                pin_ledger_report -mode run_report -start start_date  [-end end_date] [-segment gl_segment] [-type billed | unbilled | billed_earned |

 billed_unearned |unbilled_earned |unbilled_unearned|prev_billed_earned] [-report file_name] [-summary | -detail] [-noncurrency] [-verbose] [-test] [-posted] [-help]

b. Post only: This mode writes G/L report data to the Portal database

pin_ledger_report -mode post_only [-verbose][-segment gl_segment] [-posted posted_date | -unpost] [-help]

c. List_previous: To display a list of previously run G/L reports

pin_ledger_report -mode list_previous -start start_date [-end end_date [-segment gl_segment] [-type billed | unbilled |billed_earned |   billed_unearned |unbilled_earned |unbilled_unearned | prev_billed_earned] [-verbose] [-help]

d. Create_journal: This mode creates /journal objects that store G/L data not previously stored in /journal objects.

For Legacy PortalBase System [Before 7.0]

Tables used to pick up Data :  

• ledger_report_gl_segments_t, 
• item_t ,
• event_bal_impacts_t , 
• event_t

Grouping

Summary : gl_id,resource_id 
Detailed : account poid, gl_id, resource_id

         Billed : All items and event which constitute the Billed Cases like cycle forward items, arrears, cancellation and purchasing fees, refunds and payments.

          Unbilled: All items and events which constitue the Unbilled cases like cycle forward items.

From 7.0 PortalBase Onwards

       Tables used to pick up Data :

•  ledger_report_gl_segments_t , 
•  item_t ,
•  journal_t

       Grouping

Summary : gl_id,resource_id 
Detailed : account poid, journal poid, gl_id, resource_id

Billed and Unbilled Item selection criteria remains the same.

Sample Ledger Report

How to Enable and Disable GL Collection in BRM System

By Default the GL Collection is enabled in the system

The user can customize the functionality by modifying the Billing Configuration file
This file is present at the following location

        $PIN_HOME/sys/data/config/

        File name : bus_params_billing.xml

There is a configuration in this xml file. 
This needs to be changed. By default the  value is “enabled”

          <GeneralLedgerReporting>disabled</GeneralLedgerReporting>

Once this is done, the param value needs to be loaded into the Database.  
This is done by running pin_bus_params utility.

            pin_bus_params bus_params_billing.xml

Once this is run, then the services need to be re-started for this change to be effected.

 New Journal Mode [business configuration Parameter]

What is New Journal Mode?

      This is a Business Param which is newly introduced under the Parent Business param “Billing”. This param takes two values 0 and 1.  

              Db Table: config_business_params_t

                                    - 0 indicates legacy while 1 indicates new

Why was it introduced? [Functionality Overview]

     This was introduced to take care of how the data in loaded into the /journal object. It is just like a switch which enables the user to decide whether he wants to use the old system of loading data into the /journal object or the new system. 

Where is it used? [Functionality Overview]

      This is used in to load data into /journal object through the use of the utility for legacy systems. create_journal utility is used to achieve the creation of /journal objects.

What are the settings done to enable this mode?

• By Default the NEW_JOURNAL_MODE in the system has a value 0 indicating the usage of legacy journal loading 
• The user can customize the functionality by modifying the Billing Configuration file . This file is present at the following location

                         $PIN_HOME/sys/data/config/

                         File name : bus_params_billing.xml

• There is a configuration in this xml file. This needs to be changed. By default the  value is “legacy”

              <NewJournalMode>legacy</NewJournalMode>

              <NewJournalMode>new</NewJournalMode>   [This is for using the new journal mode for loading the data into the /journal object.

• Once this is done, the param value needs to be loaded into the Database. This is done by running pin_bus_params utility.      
• pin_bus_params bus_params_billing.xml

• Once this is run, then services needs to be rer-started for this change to be effected.

What are the advantages of using GL functionalities through this mode?

• Performance Aspect 
• Loading of History Data into /journal object is much faster. 
• When we do Batch Loading through REL, this parameter being enabled loads journal tables faster.

Importance of Data Classes [DB Perspspective]

GL related Tables

        Journal and related Tables :  Journal_t, event_bal_impacts_t, item_t, event_t and account_t

         Reporting tables: ledger_report_t, ledger_report_accts_t, ledger_report_account_groups_t, ledger_report_gl_segments_t and ledger_report_total_groups_t   

What is the significance of each table? What data it stores and how does each table map to the other dependant tables?

Journal_T table stores all the journal related information like user account information, GLID, Resource_id, Usage Items information and Debit and Credit Entries of Account pairs attributes of GLID like NET, DISC and TAX. [AR and offset]

Ledger Report Tables are populated from Journal_T tables. The data included in these tables are used for generating ledger reports. These includes balance impacts events, items and account information, GL Segment Information apart from the Debit and Credit Information.

What Data is picked from what Tables w.r.t to other dependent Modules like Billing?

Items, events, balances which are associated with revenue are picked up and a summarized form of the revenues is kept in /journal object.

What are the balance impacts effected by the GL tables?

All Events and Items which are affecting balances are used by GL tables to generate ledger. Summarization and Detailed reports generated and the revenue summed up on that basis.

Generate Journal Epsilon

What we need to do for this?

 This parameter is used to record any differences between rounded bill items and total amount in BRM G/L.

How do we do this ?

Configure the PIN_GLID file for rounding Location :

                           $BRM_HOME/sys/data/pricing/example/pin_glid

#=================================================================

# G/L ID for rounding adjustments #=================================================================

glid

          id      1512

          descr          Rounding Epsilon

          gl_acct       billed         gross    rounding.debit    rounding.credit

          gl_acct       billed         net      rounding.debit    rounding.credit

Use the Utility load_pin_glid to load the configuration in the database .

                         load_pin_glid pin_glid_file

If this configuration is not done, then the rounding entry into the /journal object will be assigned a GL_ID of 0.

        Configuring BRM to record rounding differences:

By default, rounding differences are not recorded in G/L reports. One can enable this feature by modifying a field in the billing class /config/business_params object created during installation. This can be done through pin_bus_params utility. 

How to enable this parameter:

1) Go to the following directory : $BRM_HOME/sys/data/config

2) Change the following parameter to “enabled” from “disabled”.

           <GenerateJournalEpsilon>enabled</GenerateJournalEpsilon>

3) Now load the configuration in the Database.

                pin_bus_params bus_params_billing.xml

4) Restart the services [CM and DM]

5) Now check the Database for whether the configuration is loaded properly.

     “select param_value from config_business_params_t where param_name = ‘generate_jounal_epsilon’;”

Now when this configuration is enabled, there will be a extra entry into the /journal object whenever any transaction is made impacting balances. This is due to the fact that the a record highlighting the difference in rounded bill items’ amount and the GL amounts, needs to be recorded in the Database.

This will be impacting the Billed Reports [Billed and Billed Earned].