Tidy Balances.. I don't really hate it
I have been asked why I tend to avoid running the Tidy Balances Conversion process, and the short answer is that 'I have never found a need to..'.
The Tidy Balances process has been designed to accept an input file of Accounts and balance 'buckets' and will produce adjustments of a specific type to ensure that the account has aged balances of these values. This could be handy in situations where minimal transaction data is being converted into CC&B via the traditional Extract-transform-and-load means (ie. convert all source data into CC&B Adjustments and Financial Transactions and run the required Validation and Keygen processes), it does not fit well with my experience of bringing 2-5 years of history over (which seems to be a common requirement on CC&B projects) and needing to create an opening balance for the Service agreements which is essentially just a placeholder transaction which is no longer subject to any aging or collection processing .
The effort required to create the Tidy Balances input table (i.e. summarise all historic transactions older than x days/months and generate a record on the work file for this account), can easily be serviced via the ConversionCentral data sources and some careful SQL, resulting in less effort required by the extraction team. The selection criteria for extracted data included in the standard mapping into Adjustments is normally in the format:
Select Source_Account, Adjustment_type, Adjustment_Amount,....
From Source System Tables
Where Transaction Date >= Conversion_Cutoff_Date
My approach to ensuring that all transactional data is processed in the run is to include a separate ControlCentral data source which generates a single 'Opening Balance' Adjustment record as:
Select Source_Account, 'Opening Balance Adjustment Code', SUM(Adjustment_Amount),....
From Source System Tables
Where Transaction Date < Conversion_Cutoff_Date
With a bit of manipulation to ensure that this is attached to the earliest converted bill, we ensure that these transactions are not reprocessed by the first CC&B bill, whilst still ensuring that the Account and SA balances conform to expected values.
Given that these adjustments are older than 120 days, they are normally not subject to traditional aging and as such this solution proves to be easier (and quicker) than running a full Tidy Balances process.
The Tidy Balances process has been designed to accept an input file of Accounts and balance 'buckets' and will produce adjustments of a specific type to ensure that the account has aged balances of these values. This could be handy in situations where minimal transaction data is being converted into CC&B via the traditional Extract-transform-and-load means (ie. convert all source data into CC&B Adjustments and Financial Transactions and run the required Validation and Keygen processes), it does not fit well with my experience of bringing 2-5 years of history over (which seems to be a common requirement on CC&B projects) and needing to create an opening balance for the Service agreements which is essentially just a placeholder transaction which is no longer subject to any aging or collection processing .
The effort required to create the Tidy Balances input table (i.e. summarise all historic transactions older than x days/months and generate a record on the work file for this account), can easily be serviced via the ConversionCentral data sources and some careful SQL, resulting in less effort required by the extraction team. The selection criteria for extracted data included in the standard mapping into Adjustments is normally in the format:
Select Source_Account, Adjustment_type, Adjustment_Amount,....
From Source System Tables
Where Transaction Date >= Conversion_Cutoff_Date
My approach to ensuring that all transactional data is processed in the run is to include a separate ControlCentral data source which generates a single 'Opening Balance' Adjustment record as:
Select Source_Account, 'Opening Balance Adjustment Code', SUM(Adjustment_Amount),....
From Source System Tables
Where Transaction Date < Conversion_Cutoff_Date
With a bit of manipulation to ensure that this is attached to the earliest converted bill, we ensure that these transactions are not reprocessed by the first CC&B bill, whilst still ensuring that the Account and SA balances conform to expected values.
Given that these adjustments are older than 120 days, they are normally not subject to traditional aging and as such this solution proves to be easier (and quicker) than running a full Tidy Balances process.
Comments
This is more a question then a comment.
Currently I'm working on an implementation of CC&B and as you mentioned in the blog "Tidy Balances.. I don't really hate it" clients nowadays want to have historical bills in their new system.
In this case the requirement is that we need to have the total bill and be able to rebill.
If I'm correct, to be able to forfill this requirement we have to rebuild their old rates and map their old bills accordingly.
Also do we have to make sure that our reads are correctly in the system, or at least that our SQ have the right consumption.
This all leads to an enourmous task for some historical bills.
Do you have an advice regarding this requirment. How we can easier copy the historical bills and still be able to rebill.
Please let me know your comments.
Regards Iwan
you are correct in your assumption that this can be an enormous task, but there are a number of workarounds.
1. copy the bill over from your legacy system as-is (i.e. not a valid CC&B bill, but rather just a number of bill lines which emulate the original bill but may look nothing like the current rate structure), just be sure that you bring across all of the associated GL entries associated with the FTs.
2. Build your rates back-dated to the date against which the customer wants to be able to cancel/re-bill (i.e. 2 years), given that most rates do not change too much in terms of the actual structure from year to year, this may be as simple as defining new versions with the associated bill factors.
3. Generate an empty rate version for the period prior to this defined period (i.e. with an effective date of 01/01/1850).
4. Ensure that you have some way of exceptioning zero bills to trap erroneous cancel/re-bills against the zero rate defined in 3 above (either via a custom bill completion algorithm or rate configuration).
You should not need to populate an SQ values on the converted bills, but you will need to ensure that the correct reading details are attached to these historic instances (since subsequent bills will use the end reads associated with the latest bill as start reads).