We are using ERPi to integrate Hyperion Planning and Oracle GL. One of the question i have is Can you get the one figure either positive or negative value from Oracle GL ( i heard from our internal Oracle EBS team that Oracle stores debit balance and credit balance and it doesn't store the value as a positive or negative figure based on the account type.)
For example we have the following hierarchy and have a huge hierarchy list which are classified as revenue and expense
But Sales Adjustment is classified as revenue account whereas it has to be deducted from product sales to get the actual sales, so can we get from GL the Sales adjustment has a negative figure and in hyperion planning i can just add the figure to product sales to arrive at correct sales figure.
In ERPi admin guide, i found the following, while defining the data load rules and filters one can specify the type of extract as
Periodic — Account balance for the specific period. Typically, income statement accounts (revenues and expenses) are specified with a periodic balance. (The default is PTD.). Does that mean Oracle GL stores the offset value ( debit - credit) in the PTD column as one value?
You can only choose to pull in the YTD or PERIODIC balances for the accounts with the data load rule.
Typically, income statement accounts (revenues and expenses) are specified with a periodic balance. (The default is PTD.).
Does that mean Oracle GL stores the offset value ( debit - credit) in the PTD column as one value?
I beleive that is correct. You would need to look at the GL_BALANCES table in EBS to see the amount that is stored for the account. ERPi is going to pull the balance from this table.
I was going through this link
Here it says
GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR. An account's year-to-date balance is calculated as BEGIN_BALANCE_DR - BEGIN_BALANCE_CR + PERIOD_NET_DR - PERIOD_NET_CR.
I am confused here , does that mean ERPi is doing the logic of offsetting the debit and credit and provide one balance?
Hopefully this helps -
When extracting data from EBS ERPi preforms logic on the fly to determine the YTD or PTD value/balance for an account.
So - to answer your question "I am confused here , does that mean ERPi is doing the logic of offsetting the debit and credit and provide one balance?" - Yes. ERPi is providing you with one balance.
BEGIN_BALANCE_DR - BEGIN_BALANCE_CR + PERIOD_NET_DR - PERIOD_NET_CR.
The above formula would produce your YTD balance for the account you are looking at.
Account Types are also based on the Account Type tag in EBS - ensuring that data is correctly flipped to a value in Hyperion. Assets, shown as negative in EBS (debit balance) would be flipped to a positive number after this.