Integrating TM1 With Oracle General Ledger 12

[CiskoWalt]

Hello,

I am interested in Integrating TM1 version 9.5.0 64-Bit with the Oracle Financials release 12, running on Oracle 11G.

Is there a technical document on this? I have contacted IBM, they do not have one.

I am interested in the Oracle table/view names that will be the source of data.

Requirement: Ability to refresh balances summared by segement (codes), Year and Month.

1. Oracle Segment values and attributes
Segment Segment Name Segment Type
11 One Revenue

2. Balances on by Year and Month, by Segement'

Segment 1 Segment 2 Segment 3 Segment 4 Segment 5 Segment 6 Year Month Balance
11 22 333 4444 55555 666666 2011 Jan 100

Is there a view/ table already defined by the Oracle FinApps that contains this data?

Requirement: Ability to Drill-through to Transaction detail.

Is there a view/ table already defined by the Oracle FinApps that contains this data?

3. Net change reports. Provide information on changes made after the most recent synchronization between the Oracle FinApps and TM1

This is an item I found in an IBM Software marketing document named 'Integratng IBM Cognso TM1 with Oracle General Ledger".

Thanks,

Walt

[tomok]

I hope you are also posting this request on an Oracle Financials support forum too because that's probably going to be your best bet to getting any help. This really has nothing to do with TM1 since it's entirely agnostic when it comes to databases. As long as there is an ODBC connection into it and you know the database structure your good to go in TM1.

[jim wood]

Tomok has a point. When I was with Shop Direct we did this but we were connecting to Business Objects where the mapping had already happened. The how to get the information out needs somebody with knowledge of the underlying table / view structure. I would as Tomok suggested post on the oracle forum. Saying that if you do go there I would just ask them how to get information out via ODBC. Adding TM1 to the mix may confuse some of the folks over there.

[Andy Key]

If you are connecting to Oracle using the 11g ODBC driver, be aware that you may have issues with it as per the following link:

https://www-304.ibm.com/support/docview ... wg21440596

Using the 11g driver without specifying the SQLFetch type in the tm1s.cfg can cause the TM1 server to crash without showing any errors in the tm1server.log file. Even specifying the parameter can still result in exceedingly slow performance occaisionally (but, frustratingly, not consistently) leading to TI Processes timing out attempting to establish a connection.

[CiskoWalt]

Thanks Andy.

I'll let you know what I find.

Walt

[CiskoWalt]

Here is the SQL I used to pull Opening Balances, 12 Months and YTD data from our Oracle ERP application. You will have to change the WHERE clause. Hope this helps someone.

Best,

Walt

SELECT
to_char(gb.period_year) as vsPlanYear
,gcc.segment1 as vsEntity
,gcc.segment2 as vsDept
,gcc.segment3 as vsMajAccount
,gcc.segment4 as vsEvent
,gcc.segment5 as vsPerf
,gcc.segment6 as vsProg
,gcc.segment7 as vsInerco
,gb.currency_code as vsCurrency
,SUM(DECODE(period_num,'1', NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0),0)) as vnOpenBal
,SUM(DECODE(period_num,'1', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnJAN
,SUM(DECODE(period_num,'2', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnFEB
,SUM(DECODE(period_num,'3', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnMAR
,SUM(DECODE(period_num,'4', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnAPR
,SUM(DECODE(period_num,'5', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnMAY
,SUM(DECODE(period_num,'6', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnJUN
,SUM(DECODE(period_num,'7', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnJUL
,SUM(DECODE(period_num,'8', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnAUG
,SUM(DECODE(period_num,'9', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnSEP
,SUM(DECODE(period_num,'10', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnOCT
,SUM(DECODE(period_num,'11', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnNOV
,SUM(DECODE(period_num,'12', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) as vnDEC
,SUM(DECODE(period_num,'12',NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0) + NVL(period_net_dr, 0) - NVL(period_net_cr, 0) ,0)) as vnYTD
FROM GL.GL_CODE_COMBINATIONS gcc,
GL.GL_BALANCES gb,
GL.GL_LEDGERS gl
WHERE gb.code_combination_id = gcc.code_combination_id
AND gb.ledger_id = gl.ledger_id
AND gcc.chart_of_accounts_id = 101
AND gb.currency_code IN ('USD', 'STAT')
AND (gb.ledger_id = 2021 )
AND NVL(gb.translated_flag, 'x') IN ('Y','N','x')
AND gb.actual_flag = 'A'
AND gb.period_year = '?sPlanYear?'
AND gcc.template_id IS NULL
AND TO_CHAR(gcc.segment1) NOT LIKE '9%'
GROUP BY gb.period_year,gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gb.currency_code
HAVING SUM(DECODE(period_num,'1', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'2', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'3', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'4', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'5', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'6', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'7', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'8', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'9', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'10', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'11', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'12', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) <> 0
OR SUM(DECODE(period_num,'12',NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0) + NVL(period_net_dr, 0) - NVL(period_net_cr, 0) ,0)) <> 0
ORDER BY 2,3,4,5,6,7,8