Tuesday Jul 29, 2008

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.

Wednesday Sep 12, 2007

Problems connecting to distributed databases using Oracle10g.

Question Recieved: We have encountered issues connecting to distributed databases using the internal CC&B subsystems (and TNSping) resulting in the following errors:
***********************************************************************
Fatal NI connect error 12557, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CCBTEST)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CCBCONVT)(CID=(PROGRAM=C:\spl\CCBCONVT\runtime\CLBDSYNC.exe)(HOST=CCBTEST)(USER=s_cissys))))
VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
Time: 04-SEP-2007 16:26:20
Tracing not turned on.
Tns error struct:
ns main err code: 12557
TNS-12557: TNS:protocol adapter not loadable
ns secondary err code: 12560
nt main err code: 527
TNS-00527: Protocol Adapter not loadable
nt secondary err code: 0
nt OS err code: 0
and
--------------------------------------------------------------------------
--Executing Data Synchronization Process at 09-05-2007 08:50:51
--------------------------------------------------------------------------

--Connecting to the Database
--User Name : CISUSER
--Database Name : CCBCONVT

Couldn't connect to the database Oracle CCBCONVT CISUSER - ORA-12557: TNS:protocol adapter not loadable (DBD ERROR: OCIServerAttach)

Answer: After ensuring that all of the Oracle connections were making use of TNS to perform connections (via the netca oracle command) we investigated the PATH settings and discovered that the original PATH setting
Path=D:\oracle\agent10g\jlib;D:\oracle\agent10g\bin;C:\spl\3rdparty\Perl64\site\bin;C:\spl\3rdparty\Perl64bin;D:\oracle\product\10.2.0\db_1\perl\site\5.8.3\lib\MSWin32-X64-multi-thread\auto\DBD\Oracle;D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem
caused tnsping to return
TNS-12557: TNS:protocol adapter not loadable
but changing the path by moving the $ORACLE_HOME\bin directory to the beginning (as below)



Path=D:\oracle\product\10.2.0\db_1\bin;D:\oracle\agent10g\jlib;D:\oracle\agent10g\bin;C:\spl\3rdparty\Perl64site\bin;C:\spl\3rdparty\Perl64\bin;D:\oracle\product\10.2.0\db_1\perl\site\5.8.3\lib\MSWin32-X64-multi-thread\auto\DBD\Oracle;D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
corrected this problem.
Further experimentation highlighted the fact that D:\oracle\agent10g\bin was the problematic directory in the PATH. Examining the contents of this directory revealed older versions of tnsping.exe and oranjni10.dll that are present in the D:\oracle\product\10.2.0\db_1\bin directory as shown below.
D:\oracle\agent10g\bin
tnsping.exe 10.2.0.2.0
oranjni10.dll 10.2.0.1.0
D:\oracle\product\10.2.0.db_1\bin

tnsping.exe 10.2.0.3.0
oranjni10.dll 10.2.0.3.0
As a result we have changed our path settings and restarted our CC&B instances to resolve our connection problems.

Thursday Aug 30, 2007

Initial Post

Welcome to my Blog.. This site will contain handy hints, workarounds and findings relating to Oracle Utilities CC&B installations and associated tools.

I intend to build upon the content in the next couple of days, but no guarantees...
About

Stuart Ramage

I am a Consulting Technical Director for Oracle Corporation, and a member of the OU Black Belt Team, based in Hobart Tasmania.
I have worked in the Utility arena since 1999 on the Oracle UGBU product line, in a variety of roles including Conversion, Technical and Functional Architect.

Contact me on:

Search

Categories
Archives
« September 2015
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today