| 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.1Hyperion 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 Win32Configuring 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.
- Log into the FDM Application
- Navigate to the Script Editor
- Click New
- Choose type: Import (DataPump)
- 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
GKontos
Comments