11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables

More Create B

Ranjit's Oracle Blogs

Panacea for an Oracle Applications Database Administrator with Real Application Cluster and Data Guard

Oracle database 10g,11g and Oracle Applications(EBS)11i,R12

Wednesday, 2 October 2013 One of the most import

areas we can develop as
professionals is sharing
Oracle R12 Apps Tables knowledge
Oracle R12 Apps Tables

Key Tables in Oracle Inventory

Ranjit Keshari Beura
Here is a brief description of the key tables in Oracle Inventory. Bangalore, Karnataka, India
ORACLE APPS DBA
Table Description View my complete profile
MTL_PARAMETERS It maintains a set of default options like general ledger
accounts; locator, lot, and serial controls, inter-organization
options, costing method, etc. for each organization defined in
Blog Archive
Oracle Inventory. Each organization’s item master ► 2018 (15)
organization (MASTER_ORGANIZATION_ID) and costing
► 2017 (1)
organization (COST_ORGANIZATION_ID) are maintained
here. ► 2016 (5)
MTL_SYSTEM_ITEMS_B This is the definition table for items. This table holds the ► 2015 (3)
definitions for inventory items, engineering items, and
purchasing items. The primary key for an item is the ► 2014 (36)
INVENTORY_ITEM_ID and ORGANIZATION_ID. ▼ 2013 (317)
Therefore, the same item can be defined in more than one
► December (11)
organization. Items now support multilingual description.
MLS is implemented with a pair of tables: ► November (7)
MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. ▼ October (25)
Translations table (MTL_SYSTEM_ITEMS_TL) holds item
SCAN and VIP Concepts in R
Description and Long Description in multiple languages.
MTL_ITEM_STATUS This is the definition table for material status codes. Status Oracle Apps dba interview
code is a required item attribute. It indicates the status of an Oracle Apps dba interview
item, i.e., Active, Pending, Obsolete.
Oracle apps dba interview
MTL_UNITS_OF_MEASURE_TL This is the definition table for both the 25-character and the
3-character units of measure. The base_uom_flag indicates Oracle apps Scenario
if the unit of measure is the primary unit of measure for the Oracle Apps dba interview q
uom_class. Oracle Inventory uses this table to keep track of Q What is form...
the units of measure used to transact an item. Preserving Patch state
MTL_ITEM_LOCATIONS This is the definition table for stock locators. The associated
Imporatant – Concurrent M
attributes describe which subinventory this locator belongs
Issues
to, what the locator physical capacity is, etc.
11g background Process
MTL_ITEM_CATEGORIES This table stores inventory item assignments to categories
within a category set. For each category assignment, this 11g background Process
table stores the item, the category set, and the category.
11g background Process
Items always may be assigned to multiple category sets.
However, depending on the Multiple Assignments Allowed FNDCPASS Concepts
attribute value in a given category set definition, an item can FNDCPASS Utility
be assigned to either many or only one category in that
How do you know which con
category set.
manager is taking...
MTL_CATEGORIES_B This is the code combinations table for item categories.
How to Tune Concurrent Ma
Items are grouped into categories within the context of a
category set to provide flexible grouping schemes. Item Concurrent Manager In Ora
categories now support multilingual category description.
Blackout configuration on O
MLS is implemented with a pair of tables: Enterprise Manage...
MTL_CATEGORIES_B and MTL_CATEGORIES_TL.
Oracle R12 Apps Tables
MTL_CATEGORIES_TL table holds translated Description
for Categories. Oracle R12 Apps Tables
MTL_CATEGORY_SETS_B It contains the entity definition for category sets. A category Oracle R12 Apps Tables
set is a categorization scheme for a group of items. Items
Workflow Structure in R12
may be assigned to different categories in different category
sets to represent the different groupings of items used for How to get Trace File for con
different program in Or...

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 1/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables
purposes. An item may be assigned to only one category How to get Trace File for con
within a category set, however. STRUCTURE_ID identifies program in Or...
the flexfield structure associated with the category set. Sleep Seconds in Concurrent
Category Sets now support multilingual category set name
Oracle Background processe
and description. MLS is implemented with a pair of tables:
MTL_CATEGORY_SETS_B and
► September (41)
MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL
table holds translated Name and Description for Category ► August (91)
Sets. ► July (81)
MTL_DEMAND This table stores demand and reservation information used
► June (61)
in Available To Promise, Planning and other Manufacturing
functions. There are three major row types stored in the
table: Summary Demand rows,
Open Demand Rows, and Reservation Rows. Total Pageviews
MTL_SECONDARY_INVENTORI This is the definition table for the subinventory. A
346,574
ES subinventory is a section of inventory, i.e., raw material,
finished goods, etc. Subinventories are assigned to items (in
a many to one relationship), indicating a list of valid places Search This Popula
where this item will physically exist in inventory. Blog Posts
MTL_ONHAND_QUANTITIES It stores quantity on hand information by control level and Search
location. It is maintained as a stack of receipt records, which
are consumed by issue transactions in FIFO order. The
quantity on hand of an item at any particular control level Followers
and location can be found by summing Followers (18)
RAC
TRANSACTION_QUANTITY for all records that match the
SCAN NA
criteria. concept w
MTL_TRANSACTION_TYPES It contains seeded transaction types and the user defined introduce
Oracle 11
ones. USER_DEFINED_FLAG will distinguish the two. The still unso
table also stores the TRANSACTION_ACTION_ID and mystery f
TRANSACTION_SOURCE_TYPE_ID that is associated with the Data
Administ
each transaction type.
The main
MTL_MATERIAL_TRANSACTIO This table stores a record of every material transaction or
NS cost update performed in Inventory. Records are inserted HOW TO
into this table either through the transaction processor or by SQL_ID
SQL_TEX
the standard cost update program. The columns CONCUR
TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, REQUES
TRANSACTION_SOURCE_TYPE_ID, Follow TROUBL
NG
TRANSACTION_SOURCE_ID and
>> First g
TRANSACTION_SOURCE_NAME describe what the Concurre
transaction is and against what entity it was performed. Request y
facing iss
MTL_ITEM_ATTRIBUTES This table stores information on item attributes. Each
>> Then
row in the table corresponds to an attribute. The table stores SID infor
the attribute name, the corresponding user-friendly name for a runn
seen by the users, and the kind of validation enforced on the request b
b...
attribute.
MTL_ITEM_CATALOG_GROUPS This is the code combinations table for item catalog groups. How to ch
_B An item catalog group consists of items that can be described status of
concurren
by the same set of descriptive elements or item properties.
How to ch
When an item is associated with an item catalog group, the status of
item inherits the descriptive elements for that group which concurren
then behave like additional item attributes. -----------
-----------
MTL_ITEM_REVISIONS_B It stores revision levels for an inventory item. When an item -----------
is defined a starting revision record is written out to this can check
table, so every item will at least have one starting revision. status of

MTL_ITEM_TEMPLATES_B This is the definition table for item templates. It

contains the user-defined name (TEMPLATE_NAME) and
description (DESCRIPTION) ONLY for backward
compatibility. You can use a template to set certain item
attributes. Oracle R1
MTL_DESCRIPTIVE_ELEMENTS It stores the descriptive element definitions for an item Tables Ke
catalog group. Descriptive elements are defining properties in Oracle
Here is a
used to describe in the catalog group. descriptio
MTL_DESCR_ELEMENT_VALU It stores the descriptive element values for a specific item. key tables
ES When an item is associated with a particular item catalog Inventory
group, one row per descriptive element (for that catalog
importan
group) is inserted into this table. errors
ORG_ACCT_PERIODS It holds the open and closed financial periods for 1. ORA-0
organizations. timeout o
while wai
MTL_CUSTOMER_ITEMS It stores customer item information for a specific customer. lock objec
Each record can be defined at one of the following levels: %s%s%s%
Customer, Address Category, and Address. The customer Errorstac
04021: ti
item definition is organization independent. occurred
MTL_SYSTEM_ITEMS_INTERFA It temporarily stores the definitions for inventory items, waiting t.

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 2/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables
CE engineering items and purchasing items before loading this
information into Oracle Inventory.
MTL_TRANSACTIONS_INTERFA It allows calling applications to post material transactions
CE (movements, issues, receipts etc. to Oracle Inventory
transaction module. iew
MTL_ITEM_REVISIONS_INTER It temporarily stores revision levels for an inventory item Oracle Ap
interview
FACE before loading this information into Oracle Inventory. Q What is
MTL_ITEM_CATEGORIES_INTE This table temporarily stores data about inventory item server? E
RFACE assignments to category sets and categories before loading briefly, h
connectio
this information into Oracle Inventory. forms ser
MTL_DESC_ELEM_VAL_INTER This table temporarily stores descriptive element values for
FACE an item that is associated with an item catalog group before Script to
loading this information into Oracle Inventory. and tune
Database
MTL_DEMAND_INTERFACE It is the interface point between non-Inventory applications Create pr
and the Inventory demand module. Records inserted into the best p
this table are processed by the Demand Manager concurrent alter sess
nls_date_
program. 'DD/MM
MTL_INTERFACE_ERRORS It stores errors that occur during the item interface process HH24:M
reporting where the errors occurred along with the error select
sql_id,pla
messages. ..

ORA-006
Key Tables in Oracle Projects internal e
argument
Oracle su
Here is a brief description of the key tables in Oracle Projects. suggests
Identify t
causing th
Table Description
600 [kds
PA_PROJECTS_ALL It stores the highest units of work defined in Oracle Projects. raised Fo
PA_PROJECT_ASSETS_ALL It contains assets information defined for capital projects. example,
file DW2T
PA_PROJECT_ASSIGNMENTS It stores details of all Assignments for a project.
PA_PROJECT_CLASSES It contains the class codes of class categories that are used to how expd
classify projects. than exp,
oracle int
PA_PROJECT_ROLE_TYPES Implementation-defined responsibilities or positions assigned change to
to employees on projects are stored here. datapum
PA_PROJECT_STATUSES It stores valid project status codes. Data pum
mode and
PA_PROJECT_TYPES_ALL It stores implementation-defined project classifications that byte mod
supply default information and drive some project processing. block mo
always fa
PA_TASKS It contains user-defined subdivisions of project work.
the byte m
PA_TASK_TYPES It stores implementation-defined classifications of task. ORACLE
PA_TRANSACTION_INTERFAC It is an interface table to import transactions from external (exp) vs D
(expd...
E_ALL sources into Oracle Projects.
PA_TRANSACTION_SOURCES It stores implementation-defined sources of imported Oracle Ap
transactions originating in an external system. Forms No
Opening
PA_IMPLEMENTATIONS_ALL It contains information about the configuration of an Oracle
Oracle Ap
Projects installation. Forms No
PA_ACTION_SETS It stores action set templates as well as action sets belonging Opening
for Oracle
to an object, such as projects, requirements, etc.
EBS may
PA_ACTION_SET_LINES It stores action set lines that belong to an action set or an for many
action set template. Follow be
to find ou
PA_ACTION_SET_TYPES It stores attributes of action set types. p...
PA_AGREEMENTS_ALL It has customer contracts that serve as the basis for work
authorization.
PA_AGREEMENT_TYPES Implementation-defined classifications of customer Links r
agreements. helpfu
PA_BILL_RATES_ALL Information about bill rates and markups of standard bill rate
morgansl
schedules.
oracledba
PA_BUDGETS It stores budgets information.
PA_BUDGET_LINES It stores detail lines of project and task budgets. ora error
PA_BUDGET_TYPES It contains implementation-defined classifications of types of personal
budgets used for different business purposes. oracle for
PA_CLASS_CATEGORIES It stores implementation-defined categories for classifying
data disk
projects.
importan
PA_CLASS_CODES It stores implementation-defined values within class
categories that can be used to classify projects. ora faq
PA_EVENTS It stores entries assigned to tasks that generate revenue oracle do
and/or billing but are not directly related to expenditure burleson
items.
PA_EVENT_TYPES It stores implementation-defined classifications of events.
PA_EXPENDITURES_ALL Groups of expenditure items incurred by employees or
organizations for an expenditure period.
PA_EXPENDITURE_CATEGORI Implementation-defined groupings of expenditure types by

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 3/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables
ES type of cost.
PA_EXPENDITURE_ITEMS_AL It contains the smallest units of expenditure charged to
L projects and tasks.
PA_EXPENDITURE_TYPES Implementation-defined classifications of expenditures
charged to projects and tasks.
PA_PERIODS_ALL Implementation-defined periods against which project
performance is measured.
PA_RBS_DENORM This table stores normalized resource breakdown structure
information.
PA_RBS_ELEMENTS This table stores the RBS element information and the parent-
child relationship.
PA_RESOURCES It contains resources used in budgeting and project summary
amounts.
PA_ROLE_LISTS It stores lists of roles defined with the system.
PA_SCHEDULES It displays the schedule details for requirements and
assignments. It also displays calendar schedules.
Filed under Apps Tables, Oracle Projects Tagged with apps tables, Key project tables, Oracle Projects

GL Tables

GL Tables
General Ledger tables can be grossly classified into following 5 categories. Here are few important tables in each
category.
Ledgers Tables:
GL_LEDGERS: Stores information about the ledgers defined in the Accounting Setup Manager and the ledger
sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description,
ledger currency, calendar, period type, chart of accounts, and other information.
GL_CODE_COMBINATIONS: Stores valid account combinations for each Accounting Flexfield structure
within your Oracle General Ledger application.
Period Tables:
GL_PERIODS: Stores information about the accounting periods you define using the Accounting Calendar
form.
GL_PERIOD_SETS: Stores the calendars you define using the Accounting Calendar form.
GL_PERIOD_TYPES: Stores the period types you define using the Period Types form. Each row includes the
period type name, the number of periods per fiscal year, and other information.
Journal Tables:
GL_JE_BATCHES: Stores journal entry batches. Each row includes the batch name, description, status,
running total debits and credits, and other information.
GL_JE_HEADERS: Stores journal entries. There is a one-to-many relationship between journal entry batches
and journal entries. Each row in this table includes the associated batch ID, the journal entry name and
description, and other information about the journal entry.
GL_JE_LINES: Stores the journal entry lines that you enter in the Enter Journals form. There is a one-to-
many relationship between journal entries and journal entry lines. Each row in this table stores the associated
journal entry header ID, the line number, the associated code combination ID, and the debits or credits
associated with the journal line.
GL_JE_SOURCES: Stores journal entry source names and descriptions. Each journal entry in your Oracle
General Ledger application is assigned a source name to indicate how it was created. This table corresponds to
the Journal Sources form.
GL_JE_CATEGORIES: Stores journal entry categories. Each row includes the category name and description.
Conversion and consolidation tables:
GL_CONSOLIDATION: Stores information about your consolidation mappings. Each row includes a
mapping’s ID, name, description, and other information. This table corresponds to the first window of the
Consolidation Mappings form. You need one row for each consolidation mapping you define.
GL_CONSOLIDATION_ACCOUNTS: Stores the account ranges that you enter when you consolidate
balances using the Transfer Consolidation Data form. This table corresponds to the Account Ranges window of
the Transfer Consolidation Data form.
GL_DAILY_RATES: Stores the daily conversion rates for foreign currency transactions. It replaces the
GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a
given conversion date and conversion type.
GL_DAILY_BALANCES: Stores daily aggregate balances for detail and summary balance sheet accounts in
sets of books with average balances enabled.
Budgeting tables:
GL_BUDGET_TYPES: Stores information about budget types. Oracle General Ledger supports only one
budget type, ‘STANDARD’. Therefore, this table always contains only one row.
GL_BUDGET_ASSIGNMENTS: Stores the accounts that are assigned to each budget organization. Each row
includes the currency assigned to the account and the entry code for the account. The entry code is either ‘E’ for
entered or ‘C’ for calculated. This table corresponds to the Account Assignments window of the Define Budget
Organization form.
GL_BUDGET_INTERIM: It is used internally by Oracle General Ledger applications to post budget balances
to the GL_BALANCES table. Rows are added to this table whenever you run the budget posting program. The
budget posting program updates the appropriate budget balances in GL_BALANCES based on the rows in this
table, and then deletes the rows in this table that it used.

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 4/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables

Interface Tables:
GL_INTERFACE: It is used to import journal entry batches through Journal Import. You insert rows in this
table and then use the Import Journals window to create journal batches.
GL_INTERFACE_CONTROL: It is used to control Journal Import execution. Whenever you start Journal
Import from the Import Journals form, a row is inserted into this table for each source and group id that you
specified. When Journal Import completes, it deletes these rows from the table.
GL_BUDGET_INTERFACE: It is used to upload budget data into your Oracle General Ledger application
from a spreadsheet program or other external source. Each row includes one fiscal year’s worth of budget
amounts for an account.
Filed under Apps Tables, General Ledger Tagged with General Ledger, GL, GL Tables, Oracle Apps

Key FND Tables in Oracle Application

Key FND Tables in Oracle Application

Here there are few key FND tables that we use in our AOL queries.
FND_APPLICATION:
Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL:
Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS:
This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS:
Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES:
Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS:
Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS:
Stores information about concurrent programs. Each row includes a name and description of the concurrent
program.
FND_CONCURRENT_PROGRAMS_TL:
Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES:
Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE:
Stores information about the number of requests a concurrent manager can process at once, according to its
work shift.
FND_CONCURRENT_REQUESTS:
Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS:
Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS:
This table stores output files created by Concurrent Request.
FND_CURRENCIES:
Stores information about currencies.
FND_DATABASES:
It tracks the databases employed by the eBusiness suite. This table stores information about the database that is
not instance specific.
FND_DATABASE_INSTANCES:
Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS:
Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL:
Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS:
Stores language-independent information about a document.
FND_EXECUTABLES:
Stores information about concurrent program executables.
FND_FLEX_VALUES:
Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS:
Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES:
Stores information regarding languages and dialects.
FND_MENUS:
It lists the menus that appear in the Navigate Window, as determined by the System Administrator when
defining responsibilities for function security.
FND_MENUS_TL:
Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES:
Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS:
Stores information about user profile options.

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 5/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables
FND_REQUEST_GROUPS:
Stores information about report security groups.
FND_REQUEST_SETS:
Stores information about report sets.
FND_RESPONSIBILITY:
Stores information about responsibilities. Each row includes the name and description of the responsibility, the
application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL:
Stores translated information about responsibilities.
FND_RESP_FUNCTIONS:
Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and
menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS:
Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES:
Stores information about the registered sequences in your applications.
FND_TABLES:
Stores information about the registered tables in your applications.
FND_TERRITORIES:
Stores information for countries, alternatively known as territories.
FND_USER:
Stores information about application users.
FND_VIEWS:
Stores information about the registered views in your applications.

Few Important AP Tables

Few Important AP Tables

AP_SUPPLIERS:
This table replaces the old PO_VENDORS table.
It stores information about your supplier level attributes.
Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
Oracle Purchasing uses this information to determine active suppliers.
The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party
created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.
AP_SUPPLIER_SITES_ALL:
This table replaces the old PO_VENDOR_SITES_ALL table.
It stores information about your supplier site level attributes.
There is a row for unique combination of supplier address, operating unit and the business
relationship that you have with the supplier.
The supplier address information is not maintained in this table and is maintained in TCA. The
reference to the internal identifier of address in TCA will be stored in
AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
Each row includes the supplier reference, purchasing, invoice, and general information.
AP_INVOICES_ALL:
It contains records for invoices you enter.
There is one row for each invoice you enter.
An invoice can have one or more invoice distribution lines and can have one or more scheduled
payments.
AP_INVOICE_LINES_ALL:
It contains records for invoice lines entered manually, generated automatically or imported from the
Open Interface.
An invoice can have one or more invoice lines.
An invoice line represents goods (direct or indirect materials), service(s), and/or associated
tax/freight/miscellaneous charges invoiced from a supplier.
An invoice line should contain all the attributes that are present on the physical or electronic invoice
presented by the supplier.
AP_INVOICE_DISTRIBUTIONS_ALL:
It holds the distribution information that is manually entered or system-generated.
There is one row for each invoice distribution and a distribution must be associated with an invoice.
An invoice can have multiple distributions.
AP_INVOICE_PAYMENTS_ALL:
It contains records of invoice payments that you made to suppliers.
There is one row for each payment you make for each invoice and there is one payment and one
invoice for each payment in this table.

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 6/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables
Oracle Payables application updates this table when you confirm an automatic payment batch, enter a
manual payment, or process a Quick payment.
When you void a payment, your Oracle Payables inserts an additional payment line that is the
negative of the original payment line.
AP_PAYMENT_SCHEDULES_ALL:
This table stores information about scheduled payment information on invoices.
AP_PAYMENT_HISTORY_ALL:
It stores the clearing/unclearing history for payments.
It also stores the maturity history for future dated payments.
The table contains a row for each future dated payment, once the future dated payment matures, i.e.
becomes negotiable.
Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.
AP_BATCHES_ALL:
It contains summary information about invoices you enter in batches if you enable the Batch Control
Payables option.
There is one row for each batch of invoices you enter.
If you enable Batch Control, each invoice must correspond to a record in this table.
Your Oracle Payables application uses this information to group together invoices that one person
entered in a batch.
AP_CHECKS_ALL:
It stores information about payments issued to suppliers or refunds received from suppliers.
There is one row for each payment you issue to a supplier or refund received from a supplier.
Oracle Payables application uses this information to record payments you make to suppliers or
refunds you receive from suppliers.
Oracle Payables application stores the supplier name and bank account name for auditing purposes,
in case either one is changed after you create the payment. Oracle Payables application also stores
address information for all payments.
AP_HOLDS_ALL:
It contains information about holds that you or your Oracle Payables application place on an invoice.
For non-matching holds, there is one row for each hold placed on an invoice. For matching holds,
there is one row for each hold placed on an invoice-shipment match.
An invoice may have one or more corresponding rows in this table.
Your Oracle Payables application does not pay invoices that have one or more unreleased holds
recorded in this table.
AP_BANK_ACCOUNTS_ALL:
It contains information about your bank accounts.
There is one row for each bank account you define and each bank account must be affiliated with one
bank branch.
AP_BANK_ACCOUNT_USES_ALL:
It stores information for the internal and external bank accounts you define in Oracle Payables and
Oracle Receivables applications.
AP_CARDS_ALL:
It stores information about the corporate credit cards issued to your employees by your corporate
credit card providers.
AP_TRIAL_BALANCE:
It contains denormalized information about invoices and payments posted to the accrual set of books.
Filed under Apps Tables, Payables Tagged with account payables, AP, apps tables, Oracle Apps, Oracle E-
Business Suite

AR Tables:A Diagrammatic Relation

AR Tables:A Diagrammatic Relation

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 7/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables

A Diagrammatic Relation between AR Tables

HZ tables in Oracle Receivables

HZ(TCA) tables in Oracle Receivables

This article describes few important HZ tables in AR and their relationships with each other.
HZ_PARTIES:
The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the
party might establish with another party. The primary key for this table is PARTY_ID.
Few Important Columns are
PARTY_ID: Party identifier
PARTY_NUMBER: Unique identification number for this party
PARTY_NAME: Name of the party
PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.
HZ_PARTY_SITES:
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores
location-specific party information. One party can optionally have one or more party sites. One location can
optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.
Few Important Columns are
PARTY_SITE_ID: Party site identifier.
PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
PARTY_SITE_NUMBER: Party site number.
PARTY_SITE_NAME: User-defined name for the site.
ADDRESSEE: Addressee information.
HZ_LOCATIONS:
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street
address, postal code, and directions to a location. This table provides physical location information about parties
(organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.
Few Important Columns are
LOCATION_ID: Unique identifier for this location
COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
ADDRESS1: First line for address
ADDRESS2: Second line for address
ADDRESS3: Third line for address
ADDRESS4: Fourth line for address
CITY: City
POSTAL_CODE: Postal Code
STATE: State
ADDRESS_KEY: Derived key that facilitates fuzzy searches
HZ_CUST_ACCOUNTS:
The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that
the deploying company establishes with a party of type Organization or Person. This table focuses on business
relationships and how transactions are conducted in the relationship. Since a party can have multiple customer
accounts, this table might contain several records for a single party. For example, an individual person can
establish a personal account, family account, and a professional account for a consulting practice. The primary
key for this table is CUST_ACCOUNT_ID.
Few Important Columns are
CUST_ACCOUNT_ID: Customer account identifier
http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 8/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables
PARTY_ID: A foreign key to the HZ_PARTY table.
ACCOUNT_NUMBER: Account Number
CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal
customers, R for revenue generating external customers.
CUSTOMER_CLASS_CODE: Customer class identifier
HZ_CUST_ACCT_SITES_ALL:
The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer
account sites are addresses, for customer accounts, where the deploying company does business with its
customers. One customer account can have multiple customer account sites, and customer account sites for one
customer account can belong to multiple operating units. The primary key for this table is
CUST_ACCT_SITE_ID.
Few Important Columns are
CUST_ACCT_SITE_ID: Customer site identifier
CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS
table
PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
BILL_TO_FLAG: Indicates if this is a Bill-To site.
SHIP_TO_FLAG: Indicates if this is a Ship-To site.
MARKET_FLAG: Indicates if this is a Marketing site.
HZ_CUST_SITE_USES_ALL:
The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for
example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table
is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though
the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary
key for this table is SITE_USE_ID.
Few Important Columns are
SITE_USE_ID: Site use identifier
CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the
HZ_CUST_ACCT_SITES_ALL table
SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and
Statements.
PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary
customer account site. N for other customer account sites.
HZ_CUSTOMER_PROFILES:
The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer
account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table.
The primary key for this table is CUST_ACCOUNT_PROFILE_ID.
Few Important Columns are
CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the
HZ_CUST_ACCOUNTS table.
STATUS: Indicates whether the profile is active or inactive
HZ_CUST_PROFILE_CLASSES:
The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common
across a group of customer accounts. The characteristics specified in this table can be used as default
characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.
HZ_PARTY_RELATIONSHIPS:
The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 9/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables

Posted by Ranjit Keshari Beura at 10/02/2013 12:47:00 pm

Reactions: funny (0) interesting (0) cool (0)

6 comments:
Anonymous said...
THANKS Ranjit for sharing this information on Oracle R12 Apps tables. It is very helpful. Appreciate your
efforts.

Regards
Prasad
24 December 2014 at 12:29

Ranjit Keshari Beura said...

Its My Pleasue..Prasad
25 December 2014 at 07:34

Rajmane Nagesh said...

Nice Information.....it is really helpful
23 January 2015 at 14:08

Ravikumar Singh (RAKS) said...

Bank Accounts
Functionality moved to Cash Management.

CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL -> replaces AP_BANK_ACCOUNT_USES_ALL
CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL
22 April 2016 at 02:00

Ranjit Keshari Beura said...

Thanks Ravi ..
23 April 2016 at 21:47

Anonymous said...
Thanks for the HZ/AR Table diagram! This table structure is making much more sense now.
15 June 2019 at 00:37

Post a Comment

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 10/11
11/10/2019 Ranjit's Oracle Blogs: Oracle R12 Apps Tables

Links to this post

Create a Link

Newer Post Home Older Post

Subscribe to: Post Comments (Atom)

Translate Contact Form

Select Language Name

Powered by Translate

Email *

Message *

Send

As a reference u may follow it.Please share some suggestions over there if u have.Thank You. Watermark theme. Powered by Blogger.

http://dbaranjit.blogspot.com/2013/10/oracle-r12-apps-tables.html 11/11