GL Balances Database Queries

GL Balances Database Queries


Troubleshooting and Understanding GL balances is a important part of any Oracle application Implementation, Support or Design Project.

In Oracle Application R12 The GL Balances are stored in the database table GL_BALANCES
Below are few queries which we can leverage to identify, Actual and Budget Balances.


Actual Balances


SELECT gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance"
FROM apps.gl_balances bal,
apps.gl_code_combinations_KFV cc,
apps.gl_ledgers gl
WHERE cc.code_combination_id = bal.code_combination_id
and bal.ledger_id = gl.ledger_id
--AND bal.Ledger_id = <'your SOB id'>
AND bal.period_name LIKE 'JAN%2015'
AND bal.actual_flag = 'A'
and gl.ledger_category_code = 'PRIMARY'
--AND bal.currency_code = <'you SOB currency code'>
--and cc.segment1 = '101'
and cc.segment4 = '113113'
--and cc.concatenated_segments in ('202.0000.000000000.251001.0000.000.000000','202.0000.000000000.299000.0000.101.000000')
GROUP by cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE
order by gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE;


Actual And Budget Balances


SELECT cc.concatenated_segments,cc.segment2,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance",
nvl(sum(balb.begin_balance_dr + balb.period_net_dr - balb.begin_balance_cr - balb.period_net_cr),0) "Budget Balance"
FROM apps.gl_balances bal,
apps.gl_balances balb,
apps.gl_code_combinations_KFV cc,

WHERE cc.code_combination_id = bal.code_combination_id
AND balb.code_combination_id = cc.code_combination_id
AND
--AND bal.Ledger_id = <'your SOB id'>
--AND balb.Ledger_id = <'your SOB id'>
--AND bal.period_name = <'your actual period name'>
--AND balb.period_name = <'your budget period_name'>
AND bal.actual_flag = 'A'
AND balb.actual_flag = 'B'
--AND bal.currency_code = <'you SOB currency code'>
--AND balb.currency_code = <'your SOB currency code'>
and cc.segment1 = '101'
and cc.segment4 = '251011'
GROUP by cc.concatenated_segments,cc.segment2;

Deep Dive in AP/AR Netting In R12

Deep Dive in AP/AR Netting In R12

Prior to Release 12, there were several netting solutions in the eBusiness Suite:

Contra-Charging

This Globalization feature was originally a solution for Germany in Release 11, but then it became a Regional Globalization feature in Release 11i.

US Federal Financials Netting

This solution specifically addresses the needs of US requirements. In Release 12, AP/AP Netting has become "core" functionality and is the only netting solution available, which means Contra-Charging and US Federal Financials Netting are all replaced by this solution. Each of those features has a migration path to the Release 12 Netting

How does it work in R12?

Here is the conceptual diagram behind the Netting Engine.

What is AP/AR Netting

What is AP/AR Netting

Oracle AP/AR Netting allows you to net your Payables invoice balances against your Receivables invoice balances for those Customers who are also your Suppliers.

For example, If you have a customer who is also your supplier, then rather than him paying you and then you paying him, AP/AR netting allows you to pay the net difference between how much you owe the supplier and how much he owes you. After establishing a netting agreement with such trading partners, you set up the agreement and the rules associated with it in eBusiness Suite and you can then start to net AP and AR transactions.

Below is a conceptual diagram of how this works.

AP AR Netting Business Process

Netting Business process

Important Accounts Receivables Database Tables

Important Accounts Receivables Tables

In an Accounts Receivables implementation, Support or trouble Shooting it is very important to have information about relevant Database tables, These details come very handy while any analysis of an issue or even technical architecture / solution designing, sharing some of the AR database Tables for reference

AR invoice Transactions

RA_CUSTOMER_TRX_ALL Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL Distribution for Transaction Lines

Transaction Interface Tables

RA_INTERFACE_LINES_ALL Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL Transaction Distribution information
RA_INTERFACE_ERRORS_ALL Transaction errors table

Receipt Interface Tables

AR_PAYMENTS_INTERFACE_ALL Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL Lockbox transfers the receipts that pass validation to the interim tables


Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL This table stores all of the activity that is contained for the life cycle of a receipt.

• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.


Customer Tables

HZ_PARTIES A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.


Accounts Receivables Setup tables

RA_CUST_TRX_TYPES_ALL This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Procure to Pay (Detailed)

When I started working on the Oracle Applications I always had a desire if I could get a Process Flow of the Business process that I am working on, That could help me Understand how the data and transactions Flow. and I can also get a Good understanding of the Big Picture of what is it that we are trying to achieve.

So as part  of this Blog here is a Process Flow for all new explorers. Below mentioned is a Detailed Process flow of Procure to Pay Process and How are Purchasing, Payables and General Ledger Modules are Used in this Business Process.

This Process Flow also Explains how Different below mentioned Entities in Business play their Roles in this Entire Process

• Requester
• Buyer
• AP Dept
• Supplier

Procure to Pay process Flow (High Level)

When I started working on the Oracle Applications I always had a desire if I could get a Process Flow of the Business process that I am working on, That could help me Understand how the data and transactions Flow. and I can also get a Good understanding of the Big Picture of what is it that we are trying to achieve.

So as part  of this Blog here is a Process Flow for all new explorers. Below mentioned is a Summary Process flow of Procure to Pay Process and How are Purchasing, Payables and General Ledger Modules are Used in this Business Process.

My next post will have a Detailed process Flow Procure to Pay Model

Multi Organization Architecture in R12

Chart of Account

Chart of Accounts is actually an Accounting Key Flexfield within General Ledger Application. As chart of accounts Structure is the most important part of accounting setups it is very important to finalize the correct accounting structure (Chart of accounts) before we start accounting setups. It is important to note that once the chart of accounts structure is defined it is not recommended to change it.

Defining of Chart of accounts consists of following main steps.

• Finalize the chart of account Structure
• Creating value sets which can be assigned to various segments of Chart of accounts Structure.
• Defining Accounting Key Flexfield and its Segments.
• Specifying appropriate qualifiers.
• Compiling the Key flex field Definition.
• Defining Values for the value sets.

Finalize the chart of account Structure

it is very important to finalize the correct accounting structure (Chart of accounts) before we start accounting setups. Because once the chart of accounts structure is defined it is not recommended to change it. Analyze the organizational structure and the dimensions of the business before designing the Chart of accounts by carefully evaluating the business needs, design the chart of accounts to take advantage of General Ledger’s flexible tools for recording and reporting the accounting information in the best possible way.

Sample Structure for a three segment Chart of Accounts.

1. Balancing segment
2. Natural Account
3. Cost center

Defining Value sets

Define the value sets for different Segments of the chart of account structure. Value sets determine the attributes of your account segments such as the segment length, value alignment, and value security. Do note that same value sets can be used for multiple segments of COA Structure.

Define the Value sets for the Accounting Flexfield as independent, table, or dependent-type value sets. Refrain from using value sets with a validation type of None, Pair, or Special for the Accounting Flexfield.

Navigation: 

Defining Accounting Key Flexfield and its Segments.

Define the chart of Accounts structure in oracle applications by defining Accounting Flex fields. There can be up to 30 segments for an Accounting Flex Field Structure. We should assign one of the Chart of account segments as Balancing Segment and one of the segment as Natural Account segment as these two are required Segments.

Application: General Ledger

Flexfield Title: Accounting Flexfield.

Navigation:

• Check the Allow Dynamic Insert check Box if it is desired to create the accounting ode combinations dynamically.
• Check the Cross Validate segments checkbox if it is desired to use Cross Validation rules to prevent/ allow creation of some account code combinations.

Define Chart of Account Segments:

Define the chart of account segments as per the structure decided and attaché the corresponding value sets to the segments. It is very important to decide and setup the column as it specifies which Segment it will correspond to in the accounting code combinations. It Determine the appearance of your key Flexfield window, the number and order of the segments, and the segment descriptions.

Specify Accounting Flexfield qualifiers:

Accounting Flex Filed qualifiers specify how different accounting flex filed segments will be used.  By default there are three accounting Flexfield qualifiers.

• Balancing Segment
• Natural Account

A natural account segment contains values representing account types, such as expense, liability, Revenue Etc. This Account type finally specifies the type of the account code Combination.

• Cost Center
• Intercompany
• Management
• Secondary Tracking

We should designate one of the Chart of account segments as Balancing Segment and Natural Account segment each. We can optionally designate other segment as Cost Center, intercompany, management, Secondary tracking. It is mandatory to define a Cost center segment if Oracle Assets or Oracle Projects is supposed to be installed and used.

Compiling the Key flex field Definition.

       Once the Chart of Accounts Structure is defined and the respective qualifiers and Value sets are attached to the segments appropriately. Freeze the Flexfield definition by checking the “Freeze Flexfield Definition” check box. Once the Accounting Flexfield account structure is Freezed in the Key Flexfield Segments window and we start using account numbers in data entry, we should not modify the Flexfield definition. Changing the existing Flexfield structure after Flexfield data has been created can cause serious data inconsistencies.

• Check the Allow Dynamic Insert check Box if it is desired to create the accounting ode combinations dynamically.

Defining Values for the value sets.

Define the Values in the value sets attached to different segments of Chart of accounts Structure so that the corresponding values can be used for the creation of the accounting Code Combination.

Navigation.

Select Allow Budgeting and Allow Posting as per business requirement.

Allow Posting flag confirms that the segment value can be used by the Posting program.

Oracle Receivables does not use the Allow Budgeting option

Natural account segment contains values representing account types, such as expense, liability, Revenue Etc. This Account type finally specifies the type of the account code Combination.