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.

Defining Organization Structure

Defining Organization Structure:

It is Very important to define the Multi Organization Structure in most effective and Optimum manner. The Success of the Multi Organization Support in Oracle applications depends on how different Business Entities in an enterprise are modeled in a Multi Organization hierarchy in Oracle Applications.
The Organization Structure should not only be compliant from legal perspective but should also be effective from operational management and accounting perspective.
Below Mentioned are some points which can be used while planning the Organization Structure.

• Business group is the highest level in the Organization Structure and is used for managing human resource information and does not have an accounting impact. It determines the availability of the employees to the ledger, Operating Units Attached to it.
• Ledger is the highest level which impacts and controls accounting, Multiple Ledgers can be associated with a single Business group but a Ledger can be associated to only One Business group.
• A legal Entity can be associated to Single Ledger, Multiple Legal Entities can be Associated to a Ledger.
• Multiple Operating units can be associated with a Single Legal Entity, but One Operating Unit can be Associated to only one Legal Entity.
• An inventory Organization points to only one Operating Unit but can be referenced by any Operating unit within the same Ledger.

Multi Organization Architecture in R12 (WHAT is WHAT)

What Is Multi organization Architecture:

       Multi Organization is a feature of oracle applications which Provides Implementation, Maintenance and operations of Multiple Business Units in an Enterprise within a single installation of Oracle Applications, taking Appropriate care of Data security and data separation. Below mentioned are certain salient features of Multi Organization functionality.

• Any number of Business Units in an Enterprise can be supported within a single installation of Oracle Application even if they have different Ledger, Legal Entity, HR data.
• User can access the data corresponding to and limited to the operating unit of the user.
• Transactions like Procurement, Receiving, Selling, Shipping Etc. with the same Party Can be Performed through Different Organization and can be managed internally through intercompany postings.
• Reporting can be managed at different organization levels like, Business Group, Ledger, Operating unit etc.

The Multi Organization structure should not only be compliant from legal perspective but should also be effective from operational management and accounting perspective. Multi organization Architecture provides support for multiple organizations in a single Installation of Oracle E-Business Suite on the basis of the relationships defined between multiple organizations entities in an Enterprise. It provides a hierarchy that specifies how different organization entities interact, and how transactions flow between them. Various types of organizations supported in the Multi organization Architecture are mentioned below.

Business Group:

Business group is the highest level in the Organization Structure and is used for managing human resource information. It can be setup to represent a consolidated Enterprise, A major Division or an Operating Company. There is no impact of Business group on Accounting. It is required to have At least one business group defined for an oracle applications Installation. Multiple Legal Entities, Ledger, operating Units Can relate to a Single Business group.

Ledger:

Ledger is a financial reporting entity, which represents an accounting representation for an organization that is accountable in a self contained way. Ledger is the highest level which impacts accounting. Ledger uses below mentioned components to secure and Manage the transaction information such as Journal entries and Balances) by ledger

• Chart of accounts
• Functional currency
• Accounting calendar
• Subledger accounting method.

Ledger represents the core of a Company’s financial records where every transaction flows through.  It provides balanced ledger accounting for the accounting entity and serves as a repository of financial information. Multiple Legal entities can be assigned to a Ledger. A Ledger can be associated with multiple Legal Entities and operating Units.

Legal Entity:

Legal Entity represents a legal company for which Fiscal and Tax reports are prepared. A Legal Entity can Model to an Enterprise, a Designated Legal unit which is recognized by legal authorities in a country. Legal entity is a business entity that is known to exist to the outside world. Internal organizations or divisions are not legal entities themselves, but are just part of it. In the Multi Organization structure various legal entities could share the same ledger. Both legal entities and operating units are associated with the ledger and the relationship between the legal entity and operating unit is derived based on the ledger.

Operating Unit:

An operating Unit represents a business unit in an Enterprise which has a business need to secure transaction data, Setups and Seed data. It can be a major division, sales office, a department, or a separate company within an enterprise. Since the transaction Information is secured at operating unit level for the various modules within Oracle applications, A single application responsibility can access, process, and report on data for one or more operating units that is assigned to its Security profile. An Operating unit can be associated to a Single Legal Entity and Ledger. But multiple operating units can be Associated to a Single Legal entity and Ledger.

Balancing Entity:

Balancing Entity is a business entity in a operating unit for which the Balance sheets are prepared. There can be Multiple Balancing entities within an Operating unit and each of them must balance within themselves. It could be a company or a division in an Enterprise. A Balancing entity is represented by Balancing Segment value in the Accounting Flexfield Structure (Chart of Accounts Structure). If there is intercompany transaction within an operating unit, appropriate intercompany entries will be automatically created in the Ledger so that the balancing Entities are in Balance.

Inventory Organization:

An Inventory organization represents a business entity of an enterprise for which the inventory transactions and inventory balances are tracked. It could be modeled to a Manufacturing plant, warehouse, Distribution center, Sales Office Etc. All the Oracle Application Products which include inventory transactions and inventory balances secure information at Inventory Organization Level. An inventory Organization points to only one Operating Unit but can be referenced by any Operating unit within the same Ledger.

What is Multi Organization Architecture in R12

What Is Multi organization Architecture:

       Multi Organization is a feature of oracle applications which Provides Implementation, Maintenance and operations of Multiple Business Units in an Enterprise within a single installation of Oracle Applications, taking Appropriate care of Data security and data separation. Below mentioned are certain salient features of Multi Organization functionality.

• Any number of Business Units in an Enterprise can be supported within a single installation of Oracle Application even if they have different Ledger, Legal Entity, HR data.
• User can access the data corresponding to and limited to the operating unit of the user.
• Transactions like Procurement, Receiving, Selling, Shipping Etc. with the same Party Can be Performed through Different Organization and can be managed internally through intercompany postings.
• Reporting can be managed at different organization levels like, Business Group, Ledger, Operating unit etc.

The Multi Organization structure should not only be compliant from legal perspective but should also be effective from operational management and accounting perspective. Multi organization Architecture provides support for multiple organizations in a single Installation of Oracle E-Business Suite on the basis of the relationships defined between multiple organizations entities in an Enterprise. It provides a hierarchy that specifies how different organization entities interact, and how transactions flow between them. Various types of organizations supported in the Multi organization Architecture are mentioned below.