Topic
1 reply Latest Post - ‏2012-07-19T14:33:45Z by CiskoWalt
CiskoWalt
CiskoWalt
2 Posts
ACCEPTED ANSWER

Pinned topic Documented Best Practice - Integrating TM1 9.5.x with Oracle FinApps 12.x

‏2012-04-12T19:41:42Z |
Hello,

Is there a document that outlines a proven method (best practice) of loading data from the Oracle Financials version 12, applicaion to a TM1 cube?

We currently use the following Oracle segments:
Entity,
Department,
Major Account,
Sub Account,
Inrercompany code.

We currently zero out and then load the Actual balances for the current Year using files that were extracted from the previous night.

Am interested in replacing the files with a direct connection to the Oracle Finalcials application.

The Oracle Financials application is shared by serveral business units, so we are only interested in loading data for a subset of the entities.

Ideally the document would;

1. Provide the source table named in Oracle.
2 How to connect, do we install Oracle client on TM1 server and
use OBDC or is there a better way to connect.
3. Would it be better to connect to the tables directly or connect
to views that restrict the data to only the entities of interest.

4. Some sample TI processes?

Thanks,

Walt
Updated on 2012-07-19T14:33:45Z at 2012-07-19T14:33:45Z by CiskoWalt
  • CiskoWalt
    CiskoWalt
    2 Posts
    ACCEPTED ANSWER

    Re: Documented Best Practice - Integrating TM1 9.5.x with Oracle FinApps 12.x

    ‏2012-07-19T14:33:45Z  in response to CiskoWalt
    Group,

    I developed some SQL to pull balances from the Oracle FinApps, version R12

    The SQL returns the 14 columns of balance data: Open Balance, Jan through Dec and YTD.

    Hope this helps.

    Walt

    SELECT
    to_char(gb.period_year) period_year ,gcc.segment1 entity
    ,gcc.segment2 dept,gcc.segment3 account
    ,gcc.segment4 event,gcc.segment5 perf
    ,gcc.segment6 prog
    ,gcc.segment7 interco
    ,gb.currency_code
    ,SUM(DECODE(period_num,'1', NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0),0)) OPEN_BAL
    ,SUM(DECODE(period_num,'1', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) JAN
    ,SUM(DECODE(period_num,'2', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) FEB
    ,SUM(DECODE(period_num,'3', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) MAR
    ,SUM(DECODE(period_num,'4', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) APR
    ,SUM(DECODE(period_num,'5', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) MAY
    ,SUM(DECODE(period_num,'6', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) JUN
    ,SUM(DECODE(period_num,'7', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) JUL
    ,SUM(DECODE(period_num,'8', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) AUG
    ,SUM(DECODE(period_num,'9', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) SEP
    ,SUM(DECODE(period_num,'10', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) OCT
    ,SUM(DECODE(period_num,'11', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) NOV
    ,SUM(DECODE(period_num,'12', NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0),0)) DEC
    ,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)) YTD
    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 = '2011'
    AND gcc.template_id IS NULL
    AND gcc.segment4 '000000'
    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