Knowledgebase: FDM
Script:How to Import Balances into FDM as YTD Even Though ERPi Extracts in Periodic
Posted by Priya Doyle on 07 February 2011 12:16 PM

How to Import Balances into FDM as YTD Even Though ERPi Extracts in Periodic

 Modified 30-AUG-2010  

Applies to:

Oracle Financial Data Quality Management ERP Integration Adapter for Oracle - Version: 11.1.1.3.00 to 11.1.1.3.00 - Release: 11.1 to 11.1
Hyperion Financial Data Quality Management - Version: 11.1.1.3.00 to 11.1.1.3.00   [Release: 11.1 to 11.1]
Information in this document applies to any platform.

Purpose

Currently depending on the account type inside of EBS the financial information can be extracted as a YTD or Periodic amount. 

For Balance Sheet accounts (Asset,Liab,Equity) the balance is a roll forward amount (YTD) amount. Every time you open/close ending balances are rolled over to the next period. 

For Income Statement (Rev/Exp) the balance is maintained as period amounts. The total for the Year is sum of the period net of all the periods.

Software Requirements/Prerequisites

Access to the FDM Script Editor either via the Web Interface or the Workbench Win32

Configuring the Script

No Configuration Requirements are Necessary.

Running the Script

To bring in all values as a YTD amount a simple FDM Import Format script can be applied to the amount field to manually do the math.

  1. Log into the FDM Application
  2. Navigate to the Script Editor
  3. Click New
  4. Choose type:  Import (DataPump)
  5. Cut/Paste the script information.  Noting to update the value "TestImport2" to your scripting name to meet VBScripting fundamental requirements.

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

Function TestImport2(strField, strRecord) 
'------------------------------------------------------------------ 
'Oracle Hyperion FDM DataPump Import Script: 

'Created By: admin 
'Date Created: 1/11/2010 8:46:02 AM 

'Purpose: 

'------------------------------------------------------------------ 
Int BEGIN_BALANCE_DR 
Int BEGIN_BALANCE_CR 
Int PERIOD_NET_DR 
Int PERIOD_NET_CR 
Dim Account_Type 

'Check to make sure this isn't the first row 
If DW.Utilities.fParseString(strRecord, 50, 38, "|") = "BEGIN_BALANCE_DR" Then 
     Res.pblnskip = True 
     Exit Function
End If 

'Parse out the different value columns in the import file 
BEGIN_BALANCE_DR = DW.Utilities.fParseString(strRecord, 50, 38, "|") 
BEGIN_BALANCE_CR = DW.Utilities.fParseString(strRecord, 50, 39, "|") 
PERIOD_NET_DR = DW.Utilities.fParseString(strRecord, 50, 40, "|") 
PERIOD_NET_CR = DW.Utilities.fParseString(strRecord, 50, 41, "|") 
Account_Type = DW.Utilities.fParseString(strRecord, 50, 42, "|") 

'Do Some Math 
If LCase(account_type) = "l" Then 
     TestImport2 = (BEGIN_BALANCE_DR - BEGIN_BALANCE_CR + PERIOD_NET_DR - PERIOD_NET_CR) * (-1) 
Else 
     TestImport2 = (BEGIN_BALANCE_DR - BEGIN_BALANCE_CR + PERIOD_NET_DR - PERIOD_NET_CR) 
End If 

End Function


(1 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: