Oracle Faqs

[sankarch]

Question: How to find the current quantity in Oracle Inventory. What is the table and column details?
The short answer is, you can get the current inventory balance by querying the mtl_onhand_quantities view. To get the current balance you would have to write a query like
select sum(transaction_quantity)
from mtl_onhand_quantities
where inventory_item_id=1234
and organization_id=201
Why do we need to do a sum?
Here are some explanations..
MTL_ONHAND_QUANTITIES 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 and location can be
found by summing TRANSACTION_QUANTITY for all records that match the
criteria. Note that any transactions which are committed to the table
MTL_MATERIAL_TRANSACTIONS_TEMP are considered to be played out as far
as quantity on hand is concerned in Inventory transaction forms. All
Inquiry forms and ABC compile are only based on
MTL_ONHAND_QUANTITIES.
.
.
.
MTL_ONHAND_QUANTITIES stores quantity on hand information by control
level and location.
.
.
.
MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID
and UPDATE_TRANSACTION_IDs to join to
MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the transactions that
created the row and the transaction that last updated a row.
For e.g, mtl_onhand_quantities maintains the amount of transacted quantity.
If the item goes out of inventory then it creates a row with negative quantity. If you
manufacture an item and place it in the inventory then it creates a row with positive number. Similarly if you buy an item then it creates a row with positive quantity.
Items going out => -ve
Items coming in => +ve
So to get the exact count you would have to sum all the transactions.
FND:fnd_descriptive_flexs, fnd_descr_flex_column_usages
September 30, 2006 at 6:57 pm ? Filed under Other, ERP Tables, FND
The below tables are used to store the descriptive flexfield definition

FND_DESCRIPTIVE_FLEXS stores setup information about descriptive
flexfields. Each row includes the name of the table that contains the
descriptive flexfield columns, the name and title of the flexfield, the
identifier of the application with which the flexfield is registered,
whether the flexfield is currently frozen, whether this is a protected
descriptive flexfield, the name of the structure defining column for the
flexfield (CONTEXT_COLUMN_NAME), and other information about
how the flexfield is defined. You need one row for each descriptive
flexfield in each application. Oracle Application Object Library uses this
information to generate a compiled definition for a descriptive flexfield.
FND_DESCR_FLEX_COLUMN_USAGES stores the correspondences
between application table columns and the descriptive flexfield
segments. Each row includes an application identifier, the name of a
descriptive flexfield, and a column name. The context field value, also
known as the structure name, is in
DESCRIPTIVE_FLEX_CONTEXT_CODE. Each row also includes the
segment name (END_USER_COLUMN_NAME), the display
information about the segment such as prompts, display size, type of
default value, whether the segment is required or not, whether the
segment is part of a high, low segment pair, whether security is enabled
for the segment, whether to display the segment or not, and the value
set the segment uses. You need one row for each segment for each
context value (structure), including global data element segments, for
each descriptive flexfield of each application. Oracle Application Object
Library uses this information to create a compiled descriptive flexfield
definition to store in the FND_COMPILED_DESCRIPTIVE_FLEXS table.
Oracle Sysadmin: FND_CONCURRENT_PROGRAMS, FND_CONCURRENT_PROCESSES
September 22, 2006 at 6:03 am ? Filed under ERP Tables, FND

FND_CONCURRENT_PROGRAMS stores information about concurrent programs. Each row includes a name and description of the concurrent program. Each row also includes the execution methods for the program (EXECUTION_METHOD_CODE), the argument method (ARGUMENT_METHOD_CODE), and whether the program is constrained (QUEUE_METHOD_CODE).
If the program is a special concurrent program that controls the concurrent managers,
QUEUE_CONTROL_FLAG is set to Y. Each row also includes flags that indicate whether the program is enabled and defined as run?alone, as well as values that specify the print style the concurrent manager should use to print program output, if any. There are also values that identify the executable associated with the concurrent program and the application with which the executable is defined, and flags that specify whether the concurrent program is a parent of a report set, whether to save the output file, and whether a print style is required.
Information such as printer name and number of rows and columns on each page of
the output file for the concurrent program is also included in the table.
You need one row for each concurrent program in each application. Oracle Application Object Library uses this information to run concurrent programs

FND_CONCURRENT_PROCESSES stores information about concurrent managers. Each row includes values that identify the ORACLE process, the operating system process, and the concurrent manager (QUEUE_APPLICATION_ID and CONCURRENT_QUEUE_ID). You
need one row for each instance of a running concurrent manager (each process), as well as one row for the Internal Concurrent Manager.
Oracle Application Object Library uses this table to keep a history of
concurrent managers. You should never update this table manually. .
You can use the Purge Concurrent Request and/or Managers Data
program to delete history information periodically.
Oracle Concurrent Requests : FND_CONCURRENT_REQUESTS
September 21, 2006 at 9:01 pm ? Filed under ERP Tables
FND_CONCURRENT_REQUESTS stores information about individual concurrent requests. Each row includes values that identify the particular request and its parameters, such as who submitted it, the request type, whether the request should run sequentially with other requests in the same logical database (SINGLE_THREAD_FLAG), whether the request is on hold (HOLD_FLAG), whether to display the request in the View Requests form for the request submitter to review, and what status and phase the concurrent request is in.
Each row also includes values that identify the concurrent program, its execution and
argument methods, and whether the program is constrained (QUEUE_METHOD_CODE). Each row also includes flags that indicate the request?s priority relative to other requests, as well as values that specify how the concurrent manager should print program output, if
any. ARGUMENT1 through ARGUMENT25 contain any arguments the application passes to the concurrent program.
If the concurrent program needs more than 25 arguments to run, the first 25 arguments
are stored in this table, ARGUMENT26 through ARGUMENT100 are stored in FND_CONC_REQUEST_ARGUMENTS. ARGUMENT_TEXT contains the concatenation of concurrent request arguments and COMPLETION_TEXT contains a message about how the request completed.
The row also contains dates that the request was submitted, requested to start and actually run. REQ_INFORMATION is used with report sets to remember the status of the request between runs. When the request is set to use automatic resubmission, RESUBMITTED is a flag to indicate whether the request has been resubmitted or not.
RESUBMIT_INTERVAL_TYPE_CODE specifies whether to start interval count down from the requested start time or the completion of the request.
RESUBMIT_INTERVAL_UNIT_CODE indicates whether interval unit is in Days, Hours, Minutes, or Months. RESUBMIT_TIME sets the time of the day to rerun the concurrent request. RESUBMIT_INTERVAL indicates the number of units of time when the
identical request will be resubmitted. RESUBMIT_END_DATE is the date the request stops resubmitting itself. IS_SUB_REQUEST is a flag that identifies a child request and HAS_SUB_REQUEST is a flag that identifies a parent request.
Each child request also needs to have values in PARENT_REQUEST_ID to show what parent request submitted the child request and PRIORITY_REQUEST_ID to tell what priority the parent request has and what priority the child request should have.
Oracle Application Object Library does not use ENFORCE_SERIALITY_FLAG (always Y), CPU_SECONDS, LOGICAL_IOS, or PHYSICAL_IOS.
You need one row for each concurrent request. Though you should occasionally delete from this table, you should not modify any of its data. Oracle Application Object
Library uses this information to run concurrent requests, and automatically updates it as requests change their status.
FND_EXECUTABLES

stores information about concurrent program
executables. Each row includes an application identifier, an executable
identifier, an executable name, and the name of the physical executable
file as it resides on the operating system. The execution method
associated with the executable identifies the tool that is needed to run
the executable. A subroutine name is only applicable to immediate
concurrent programs and spawned concurrent programs that can be run
either as spawned or immediate concurrent program. You need one row
for each executable that you are going to submit as a concurrent
program. Oracle Application Object Library uses this information to
process concurrent requests.

FND_FLEX_VALUE_NORM_HIERARCHY stores information about
multi?level value hierarchies for independent and dependent value sets.
Each row includes a value set name, a parent value, a flag to distinguish
a child value from a parent value (RANGE_ATTRIBUTE), a low value
for the range of child values, and a high value for the range of child
values. Oracle Application Object Library uses this information to
support multi?level hierarchy values.
FND_FLEX_VALUE_SETS stores information about the value sets used
by both key and descriptive flexfields. Each row includes the
application identifier, the name and description of the value set, the
validation type of value set (F for Table, I for Independent, D for
Dependent, N for None, P for Pair, U for Special), the data format type,
the maximum and minimum values and precision for number format
type value set.
Each row also contains flags that determine what size
values can be in this value set, and whether flexfield value security and
LOV?s LongList feature are enabled for this value set.
NUMERIC_MODE_ENABLED_FLAG indicates whether Oracle
Application Object Library should right?justify and zero?fill values that
contain only the characters 0 through 9; it does not indicate that values
in this value set are of type NUMBER. MAXIMUM_VALUE and
MINIMUM_VALUE together do range checks on values.
If the value set is a dependent value set, PARENT_FLEX_VALUE_SET_ID identifies the
independent value set the current dependent value set depends upon.
Also if the value set is a dependent value set, DEPENDANT_DEFAULT_VALUE and
DEPENDANT_DEFAULT_MEANING contain the default value and
description that Oracle Application Object Library should automatically
create in the dependent value set whenever you create a new value in
the independent value set it depends upon. You need one row for each
value set you have for your flexfields.
Oracle Application Object Library uses this information to assign groups of valid values to
flexfield segments
Oracle Workflow tables:WF_ACTIVITIES, WF_ITEMS, WF_NOTIFICATIONS
September 18, 2006 at 6:51 am ? Filed under ERP Tables

WF_ACTIVITIES table stores the definition of an activity. Activities can
be processes, notifications, functions or folders. A process activity is a
modelled workflow process, which can be included as an activity in
other processes to represent a sub?process. A notification activity sends
a message to a performer. A functions activity performs an automated
function that is written as a PL/SQL stored procedure. A folder activity
is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.

The WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which the
attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time a
message is sent

Oracle FND key tables , FND_FLEX_VALUE_HIERARCHIES (4)
September 9, 2006 at 1:23 pm ? Filed under ERP Tables
FND_FLEX_VALUE_HIERARCHIES stores information about child
value ranges for key flexfield segment values. Each row includes an
identification of the parent value the range belongs to, as well as the low
and high values that make up the range of child values.
FLEX_VALUE_SET_ID identifies the value set to which the parent value
belongs. You need one row for each range of child values (you can have
more than one row for each parent value). Oracle Application Object
Library provides this information for applications reporting purposes.
Oracle FND key tables , FND_FLEX_VALUES (3)
September 8, 2006 at 1:16 pm ? Filed under ERP Tables
FND_FLEX_VALUES stores valid values for key and descriptive
flexfield segments. Oracle Application Object Library uses this table
when users define values for independent or dependent type value sets.
Oracle Application Object Library also uses this table when users define
parent values for ranges of child values that exist in a validation table
(Oracle Application Object Library stores the parent values in this table).
Each row includes the value (FLEX_VALUE) and its hierarchy level if
applicable as well as the identifier of the value set the value belongs to.
If the value is a dependent value, PARENT_FLEX_VALUE_LOW
contains the independent value this value depends upon. Oracle
Application Object Library does not use the
PARENT_FLEX_VALUE_HIGH column. If ENABLED_FLAG contains
N, this value is currently invalid, regardless of the start and end dates.
If ENABLED_FLAG contains Y, the start and end dates indicate if this
value is currently valid.
SUMMARY_FLAG indicates if this value is a parent value that has child values, and
STRUCTURED_HIERARCHY_LEVEL contains the rollup group the
parent value belongs to, if any (1 through 9).
COMPILED_VALUE_ATTRIBUTES contains the compiled values of any
segment qualifiers assigned to this value. These values are in a special
Oracle Application Object Library format, and you should never modify
them.
VALUE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE50
are descriptive flexfield columns, where VALUE_CATEGORY is the
context (structure defining) column.
These descriptive flexfield columns do not contain values unless you have defined the descriptive flexfield at your site. You need one row for each independent, dependent or
parent value belonging to a value set.
Oracle Application Object Library uses this information to ensure that users enter valid values in flexfield segments
Oracle FND key tables , FND_ID_FLEX_STRUCTURES (2)
September 7, 2006 at 1:13 pm ? Filed under ERP Tables
FND_ID_FLEX_STRUCTURES stores structure information about key
flexfields. Each row includes the flexfield code and the structure
number (ID_FLEX_NUM), which together identify the structure, and the
name and description of the structure. Each row also includes values
that indicate whether the flexfield structure is currently frozen, whether
rollup groups are frozen (FREEZE_STRUCTURED_HIER_FLAG),
whether users can dynamically insert new combinations of segment
values through the flexfield pop?up window, and whether the flexfield
should use segment cross?validation rules. Each row also contains
information about shorthand flexfield entry for this structure, including
whether shorthand entry is enabled, the prompt for the shorthand
window, and the length of the shorthand alias field in the shorthand
window. You need one row for each structure of each key flexfield.
Oracle Application Object Library uses this information to generate a
compiled key flexfield definition to store in the
FND_COMPILED_ID_FLEXS table
Oracle FND key tables , FND_ID_FLEXS, FND_ID_FLEX_SEGMENTS (1)
September 6, 2006 at 12:56 pm ? Filed under ERP Tables
FND_ID_FLEXS stores registration information about key flexfields.
Each row includes the four?character code that identifies the key
flexfield, the title of the flexfield (by which a user identifies the
flexfield), the name of the combinations table that contains the key
flexfield columns, and the name of the structure defining (MultiFlex)
column for the flexfield (SET_DEFINING_COLUMN_NAME). Each row
also contains values that identify the application that owns the
combination table and the application that owns the key flexfield, a
table?type flag that specifies whether the combinations table is specific
or generic (S or G), whether dynamic inserts are feasible for the flexfield
(Y or N), whether the key flexfield can use ID type value sets, and the
name of the unique ID column in the combinations table. You need one
row for each key flexfield in each application. Oracle Application Object
Library uses this information to generate a compiled key flexfield
definition
FND_ID_FLEX_SEGMENTS stores setup information about key
flexfield segments, as well as the correspondences between application
table columns and the key flexfield segments the columns are used for.
Each row includes a flexfield application identifier, the flexfield code,
which identifies the key flexfield, the structure number
(ID_FLEX_NUM), the value set application identifier, the segment
number (the segment?s sequence in the flexfield window), the name of
the column the segment corresponds to (usually SEGMENTn, where n is
an integer). Each row also includes the segment name, whether security
is enabled for the segment, whether the segment is required, whether
the segment is one of a high, low segment pair, whether the segment is
displayed, whether the segment is enabled (Y or N), type of default
value, display information about the segment such as prompts and
display size, and the value set the segment uses. Each row also includes
a flag for whether the table column is indexed; this value is normally Y.
You need one row for each segment of each structure for each flexfield.
Oracle Application Object Library uses this information to generate a
compiled key flexfield definition to store in the
FND_COMPILED_ID_FLEXS table
Oracle Inventory (INV) key tables - mtl_demand (4)
August 28, 2006 at 6:48 am ? Filed under ERP Tables
MTL_DEMAND

This table stores demand and reservation information used 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.

Summary Demand is direct demand for an item within an organization
on a particular date that originated from a particular source. For hard
reservations there are several columns which further define what the
reservation is for, and where it is being placed. Currently, four sources
of demand are supported, Sales Order, Account, Account Alias, and
User Defined transaction sources.
Five different types of demand, denoted by DEMAND_TYPE column, are used. These five types are Model, Option Class, Option Item, Configuration Item and Derived.
Derived demand rows are inserted by BOM Demand exploder when
demanded item has ATPable components.
Each Summary Demand row may be associated with one or more Reservation rows. Reservation may be placed against a particular inventory control (that is, specific
subinventory, locator, revision and lot) against any sources (that is,
Account Number, Account Alias, Sales Order or even User?Defined
sources).
Each Summary Demand row may be associated with one or
more detailed rows. The detailed rows consist of reservations and open
demand. A reservation row represents a firm promise of a supply
source. Currently, two types of reservation are supported, reservations
to on?hand, and reservations to WIP jobs.
Each summary demand row may be associated with one and only one open demand row. Open Demand rows represent the un?reserved portion of the the Summary
Demand.
Oracle Inventory (INV) key tables - mtl_category_sets_b, mtl_categories_b (3)
August 27, 2006 at 6:45 am ? Filed under ERP Tables
MTL_CATEGORIES_B is the code combinations table for item
categories. Items are grouped into categories within the context of a
category set to provide flexible grouping schemes.
The item category is a key flexfield with a flex code of MCAT. The
flexfield structure identifier is also stored in this table to support
MTL_CATEGORY_SETS_B contains the entity definition for category
sets. A category set is a categorization scheme for a group of items.
Items may be assigned to different categories in different category sets
to represent the different groupings of items used for different purposes.
An item may be assigned to only one category within a category set,
however.
STRUCTURE_ID identifies the flexfield structure associated with the
category set. Only categories with the same flexfield structure may be
grouped into a category set.
CONTROL_LEVEL defines whether the category set is controlled at the
item or the item/organization level. When an item is assigned to an
item level category set within the item master organization, the category
set assignment is propagated to all other organizations to which the item
is assigned.
VALIDATE_FLAG defines whether a list of valid categories is used to
validate category usage within the set. Validated category sets will not
allow item assignment to the category set in categories that are not in a
predefined list of valid categories.
Category Sets now support multilingual category set name and
description. MLS is implemented with a pair of tables:
MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.
Oracle Purchasing (PO) key Vendor tables (5)
August 26, 2006 at 6:44 am ? Filed under ERP Tables
PO_VENDORS stores information about your suppliers. You need one
row for each supplier you define.
Each row includes the supplier name as well as purchasing, receiving, payment, accounting, tax, classification, and general information.
Oracle Purchasing uses this information to determine active suppliers. VENDOR_ID is the unique system?generated receipt header number invisible to the user.
SEGMENT1 is the system?generated or manually assigned number you
use to identify the supplier in forms and reports.
Oracle Purchasing generates SEGMENT1 using the
PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let
Oracle Purchasing generate supplier numbers for you. This table is one
of three tables that store supplier information. PO_VENDORS
corresponds to the Suppliers window.
PO_VENDOR_SITES_ALL stores information about your supplier
sites. You need a row for each supplier site you define. Each row
includes the site address, supplier reference, purchasing, payment,
bank, and general information. Oracle Purchasing uses this
information to store supplier address information.
This table is one of three tables that store supplier information.
PO_VENDOR_SITES_ALL corresponds to the Sites region of the
Suppliers window.
Oracle Fixed Assets (FA) Key Tables fa_categories_b, fa_deprn_detail
August 25, 2006 at 6:42 am ? Filed under ERP Tables
FA_CATEGORIES_B stores information about your asset categories.
This table provides default information when you add an asset. The
depreciation program does not use this information to calculate
depreciation.
The Asset Categories form inserts one row in this table for each asset
category you define.
The Application Object Library table FND_ID_FLEX_SEGMENTS stores
information about which column in this table is used for each segment.
FA_DEPRN_DETAIL contains the depreciation amounts that the
depreciation program charges to the depreciation expense account in
each distribution line.
Oracle Assets uses this information to create
depreciation expense journal entries for your general ledger.
The depreciation program inserts one row per distribution line for an
asset each time you run depreciation.
For example, if you assign an asset to two different cost centers, the depreciation program inserts two rows in this table for the asset.
DEPRN_AMOUNT is the amount of depreciation expense calculated for
this distribution line.
YTD_DEPRN is the year?to?date depreciation allocated to this
distribution line.
When you add an asset, Oracle Assets inserts a row into this table for
the period before the current period. This row has the asset cost in the
ADDITION_COST_TO_CLEAR column and a DEPRN_SOURCE_CODE
of ?B?. This column is used for reporting on new assets. When you run
depreciation, Oracle Assets transfers the cost to the COST column in the
current period row, this row has a DEPRN_SOURCE_CODE of ?D?.
Oracle Fixed Assets (FA) Key Tables fa_books
August 24, 2006 at 6:39 am ? Filed under ERP Tables
FA_BOOKS contains the information that Oracle Assets needs to
calculate depreciation.
When you initially add an asset, Oracle Assets inserts one row into the
table. This becomes the ?active? row for the asset. Whenever you use
the Depreciation Books form to change the asset?s depreciation
information, or if you retire or reinstate it, Oracle Assets inserts another
row into the table, which then becomes the new ?active? row, and
marks the previous row as obsolete.
At any point in time, there is only one ?active? row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a
report for a prior accounting period, Oracle Assets selects the row that
was active during that period. You can identify the active row for an
asset in a book because it is the only one whose DATE_INEFFECTIVE
and TRANSACTION_HEADER_ID_OUT are NULL.
When Oracle Assets terminates a row, the DATE_INEFFECTIVE and
TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and
TRANSACTION_HEADER_IN of the new row, respectively. This
means that you can easily identify rows affected by the same transaction
because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE
and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs.
When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the
DATE_EFFECTIVE is the system date.
When you retire an asset, Oracle Assets inserts a new row to reduce the
COST by the amount retired.
When you reinstate an asset, Oracle Assets inserts a new row to increase
the COST by the COST_RETIRED in the corresponding row in
FA_RETIREMENTS.
RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread
depreciation over the remaining life of an asset after an amortization or
revaluation. If you perform a revaluation or an amortized adjustment,
Oracle Assets resets the Rate Adjustment Factor to prorate the
remaining recoverable net book value over the remaining life.
This fraction is calculated as [Recoverable Cost ? what Depreciation Reserve
would be]/Recoverable Cost. The depreciation program uses this value
to adjust the depreciation rate for an asset.
Oracle Inventory (INV) key tables - mtl_item_categories, mtl_item_sub_inventories (2)
August 23, 2006 at 6:37 am ? Filed under ERP Tables
MTL_ITEM_CATEGORIES stores the item assignments to categories
within a category set. For each category assignment, this table stores the
item, the category set, and the category. Items may be assigned to
multiple categories and category sets but may be assigned to only one
category in a given category set.
This table may be populated through the Master Items and Organization
Items windows. It can also be populated by performing item
assignments when a category set is defined. It is also populated when
an item is transferred from engineering to manufacturing.
MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories
assigned to an inventory or engineering item.
These subinventories make up the list of valid subinventories when
transacting this specific item and the user has specified (in the master
window) that the item must use subinventories restricted to a
pre?defined list.
Oracle Inventory (INV) key tables - mtl_system_items (1)
August 22, 2006 at 6:31 am ? Filed under ERP Tables
MTL_SYSTEM_ITEMS_B is the definition table for items. This table
holds the definitions for inventory items, engineering items, and
purchasing items. You can specify item?related information in fields
such as: Bill of Material, Costing, Purchasing, Receiving, Inventory,
Physical attributes, General Planning, MPS/MRP Planning, Lead times,
Work in Process, Order Management, and Invoicing.
You can set up the item with multiple segments, since it is implemented as a flexfield.
Use the standard ?System Items? flexfield that is shipped with the
product to configure your item flexfield. The flexfield code is MSTK.
The primary key for an item is the INVENTORY_ITEM_ID and
ORGANIZATION_ID. Therefore, the same item can be defined in
more than one organization. Each item is initially defined in an item
master organization. The user then assigns the item to other
organizations that need to recognize this item; a row is inserted for
each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and
BOM_ENABLED_FLAG correspond to item attributes defined in the
MTL_ITEM_ATTRIBUTES table. The attributes that are available to
the user depend on which Oracle applications are installed. The table
MTL_ATTR_APPL_DEPENDENCIES maintains the relationships
between item attributes and Oracle applications.
Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table.
PRIMARY_UOM_CODE is the 3?character unit that is used throughout
Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the
25?character unit that is used throughout Oracle Purchasing.
Items now support multilingual description. MLS is implemented with a pair
of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL.
Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions
in multiple languages. DESCRIPTION column in the base table
(MTL_SYSTEM_ITEMS_B) is for backward compatibility and is main
tained in the installation base language only.
Oracle Purchasing (PO) key requisition tables (4)
August 12, 2006 at 7:43 am ? Filed under ERP Tables
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description.
REQUISITION_HEADER_ID is the unique system?generated requisition number. REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you use to identify the requisition in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let
Oracle Purchasing generate requisition numbers for you.
PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition information. PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window.
SEGMENT1 provides unique values for each row in the table in
addition to REQUISITION_HEADER_ID.
PO_REQUISITION_LINES stores information about requisition lines. You need one row for each requisition line you create. Each row contains the line number, item number, item category, item description, need?by date, deliver?to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition
line.
LINE_LOCATION_ID identifies the purchase order shipment line on
which you placed the requisition. LINE_LOCATION_ID is null if you
have not placed the requisition line on a purchase order.
BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store
the suggested blanket purchase agreement or catalog quotation line
information for the requisition line. PARENT_REQ_LINE_ID contains
the REQUISITION_LINE_ID from the original requisition line if you
exploded or multisourced this requisition line.

PO_REQUISITION_LINES is one of three tables storing requisition
information. This table corresponds to the Lines region of the
Requisitions window.
PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution. You need one row for each requisition distribution you create. Each row includes the Accounting Flexfield ID and requisition line quantity.
PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information. This table corresponds to the requisition Distributions window, accessible through the Requisitions window.
Oracle Purchasing (PO) key table - po_distributions_all (3)
August 11, 2006 at 7:41 am ? Filed under ERP Tables
PO_DISTRIBUTIONS_ALL contains accounting distribution
information fora purchase order shipment line. You need one row for
each distribution line you attach to a purchase order shipment. There
are four types of documents using distributions in Oracle Purchasing:
? Standard Purchase Orders
? Planned Purchase Orders
? Planned Purchase Order Releases
? Blanket Purchase Order Releases
Each row includes the destination type, requestor ID, quantity ordered
and deliver?to location for the distribution. Oracle Purchasing uses
this information to record accounting and requisition information for
purchase orders and releases.
PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order
and release information.
Some columns in PO_DISTRIBUTIONS_ALL contain information only
if certain conditions exist:
? If you autocreate this accounting distribution from a requisition,
REQ_DISTRIBUTION_ID corresponds to the ID of the
requisition distribution you copy on the purchase order.
? If you use a foreign currency on your purchase order,Oracle
Purchasing stores currency conversion information in RATE and
RATE_DATE.
? If you use encumbrance, GL_ENCUMBERED_DATE and
GL_ENCUMBERED_PERIOD_NAME contain encumbrance
information Oracle Purchasing uses to create journal entries in
Oracle General Ledger.
? If you do not autocreate the purchase order from online
requisitions, REQ_LINE_REFERENCE_NUM and
REQ_HEADER_REFERENCE_NUM contain the requisition
number and requisition line number of the corresponding paper
requisition. These two columns are not foreign keys to another
table.
? If the distribution corresponds to a blanket purchase order
release, PO_RELEASE_ID identifies this release.
? If SOURCE_DISTRIBUTION_ID has a value, the distribution is
part of a planned purchase order release.
If you cancel the distribution, Oracle Purchasing automatically
updates QUANTITY_CANCELLED or
GL_CANCELLED_DATE. Oracle Purchasing also enters
UNENCUMBERED_AMOUNT if you use encumbrance.
Oracle Purchasing (PO) key table - po_lines_all (2)
August 9, 2006 at 8:39 am ? Filed under ERP Tables
PO_LINES_ALL stores current information about each purchase order
line. You need one row for each line you attach to a document. There
are five document types that use lines:
? RFQs
? Quotations
? Standard purchase orders
? Blanket purchase orders
? Planned purchase orders
Each row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs.
PO_LINE_ID is the unique system?generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order.
Oracle Purchasing uses CONTRACT_NUM to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for
blanket and planned purchase order lines.
The QUANTITY field stores the total quantity of all purchase order
shipment lines (found in PO_LINE_LOCATIONS_ALL).
Oracle HRMS (human resource management) HR key Tables (2)
August 9, 2006 at 7:28 am ? Filed under ERP Tables
PER_ALL_VACANCIES holds definitions of vacancies within a specific requisition. A vacancy may include components identifying an organization, job, group, grade, position and location. These are used as the defaults for any applicant assignment to that vacancy. NOTE :This table is also part of i-Recruitment module
PER_APPLICATIONS holds the identifying information about applications for employment. Applications are equivalent to the period of service for an employee. An applicant may have only one open application at any time, but within each application they can apply for any number of different assignments. Information for each assignment
applied for, including the current status in that assignment, is held in PER_ALL_ASSIGNMENTS_F. When an applicant is terminated, or is hired as an employee DATE_END is set by the system.
PER_JOBS holds jobs that have been defined for a Business Group. The NAME is a concatenation of key flexfield segments, held in PER_JOB_DEFINITIONS. Jobs define the role that an employee can perform in the business group, and they are independent of specific organizations.
Oracle HRMS key Tables (2)
August 8, 2006 at 7:25 am ? Filed under ERP Tables
PER_ALL_ASSIGNMENTS_F is the DateTracked table that holds information about employee assignments. It also holds details of assignments for which an applicant has applied. The ASSIGNMENT_TYPE is E for an employee assignment, and A for an
applicant assignment. Employees must have at least one employee assignment at all times in a period of service, and each assignment must have a unique number. Employees can have multiple assignments at any time, but there must always be a designated
primary assignment. Applicants must have at least one applicant assignment throughout their application. Each assignment has an assignment status that can change over time.
PER_ALL_PEOPLE_F is the DateTracked table that holds personal information for employees, applicants, ex?employees, ex?applicants, contacts and other people. The columns START_DATE, EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are all
maintained by DateTrack. The START_DATE is the date when the first record for this person was created. The earliest EFFECTIVE_START_DATE for a person is equal to the START_DATE.
Oracle Purchasing (PO) key table - po_headers_all (1)
August 7, 2006 at 7:36 am ? Filed under ERP Tables
PO_HEADERS_ALL contains header information for your purchasing
documents. You need one row for each document you create. There are six types of documents that use PO_HEADERS_ALL:
? RFQs
? Quotations
? Standard purchase orders
? Planned purchase orders
? Blanket purchase orders
? Contracts
Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information, and the status of the document.
Oracle Purchasing uses this information to record information that is related to a complete document.
PO_HEADER_ID is the unique system?generated primary key and is invisible to the user. SEGMENT1 is the system?assigned number you use to identify the document in forms and reports.
Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate document numbers for you. SEGMENT1 is not unique for the entire table.
Different document types can share the same numbers. You can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using
PO_HEADER_ID. If APPROVED_FLAG is ?Y?, the purchase order is approved. If your
document type is a blanket purchase order, contract purchase order,RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE to store the valid date range for the document. Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket
purchase orders or contract purchase orders. If you autocreate a quotation from an RFQ using the Copy Document window, Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID. Oracle Purchasing also uses
FROM_TYPE_LOOKUP_CODE to indicate that you copied the quotation from an RFQ.
Oracle Projects key views and tables PA (2)
August 6, 2006 at 7:19 am ? Filed under ERP Tables
Below are not the tables but some useful views for Projects
PA_PROJ_INVOICES_VIEW displays all project invoices in Oracle Projects. You can use this view to easily report project invoice information. This view displays one record per project invoice.
PA_PROJ_INVOICE_DETAILS_VIEW displays the detailed expenditure items that make up a project invoice. You can use this view to easily report the expenditure item information for an invoice backup report.
PA_PROJ_INVOICE_EVENTS_VIEW displays detailed event information for events included on a project invoice. You can use this view to easily report event details for a project invoice backup report.
PA_PROJ_INVOICE_SUMMARY_VIEW displays summary totals for a project which include baselined budget revenue amount, total revenue amount, total invoice amounts, and amounts on hold. This view displays one record per project. You can use this information when reviewing project status, particularly when reviewing invoices for a
project.
PA_PROJ_MEMBERS_VIEW displays project members for a project. This view displays one record per project key member. You can use this view when reporting projects by project member. You can easily find all projects to which a project member is assigned.
PA_PROJ_RELEASED_INVOICES_VIEW displays released project
invoices. This view displays one record per project invoice. You can easily use this view when reporting released invoices.
PA_PROJ_REVENUE_VIEW is a view of the draft revenue information for a project that can be used for management reporting and online queries. The view displays one record per project revenue. For optimum performance in your reports and online queries, always
include PROJECT_ID in your selection criteria.
PA_PROJ_UNBILLED_EXPEND_VIEW displays billable expenditure items that are unbilled. You can review the unbilled expenditure items for your projects using this view.
PA_PROJ_UNBILLED_SUMMARY_VIEW shows summary unbilled amounts for a project. These amounts include items that are unbilled, items on hold, and events that are unbilled. You can easily review the unbilled summary status of your project
using this view.
PA_PROJ_WBS_VIEW displays project tasks as part of the project work breakdown structure. The task name and numbers are indented to illustrate the task level and position in the work breakdown structure. The view also displays basic task information, including task start and completion dates, task description, and the task manager.
Oracle HRMS key Tables (1)
August 5, 2006 at 1:25 pm ? Filed under ERP Tables
HR_ORGANIZATION_UNITS holds the definitions that identify business groups and the organization units within a single business group. Additional information about classifications and information types for each organization is held in HR_ORGANIZATION_INFORMATION.
HR_ALL_POSITIONS_F is the datetracked table holding position definitions. A position is defined as the unique occurrence of a job in one organization. There can be many different positions that share the same JOB_ID and ORGANIZATION_ID, but each position is uniquely identified by the POSITION_ID and by the NAME. The position name
is a concatenation of flexfield segments stored in the PER_POSITION_DEFINITIONS table.
HR_OPERATING_UNITS is a view based on HR_ORGANIZATION_UNITS and
HR_ORGANIZATION_INFORMATION that returns information about operating units.
Oracle Projects key Tables PA (1)
August 5, 2006 at 7:16 am ? Filed under ERP Tables
PA_PROJECTS_ALL stores the highest units of work defined in Oracle Projects at which funding can be allocated and at which revenue, invoices, unbilled receivables, and unearned revenue are generated and maintained. At the project level, users can specify the methods to be used for revenue accrual and billing, and the invoice formats to be used for invoicing. A project is broken down into one or more tasks. All projects can be funded by one or more agreements.
PA_TASKS PA_TASKS stores user?defined subdivisions of project work. Tasks can be broken down into multiple levels of subtasks. The depth and width of the work breakdown structure (WBS) are unlimited. The WBS is held using the TASK_ID and PARENT_TASK_ID columns. Two columns related to the WBS are denormalized: TOP_TASK_ID and WBS_LEVEL.
PA_PERIODS_ALL stores implementation?defined periods in Oracle Projects by which project performance is measured. PA Periods can be different from your general ledger accounting periods (GL Periods). You can define PA Periods as weekly periods, while GL periods are monthly. In Oracle Projects, costs and revenue can be reported by PA Period as well as by GL Period. You define a PA period or periods in the GL calendar associated with the set of books used in PA_IMPLEMENTATIONS_ALL. You then copy the PA periods from the GL calendar to this table. You maintain the status of the period in this table.
PA_PROJECT_TYPES_ALL stores implementation?defined classifications of projects that specify whether a project burdens labor costs and whether the project is contract, capital or indirect (based on PROJECT_TYPE_CLASS_CODE). Project types are also used during project entry to specify default information such as invoice formats, standard bill rate schedules, and task service types.
FA Key Tables fa_additions_b , fa_adjustments (2)
August 4, 2006 at 4:27 pm ? Filed under ERP Tables
FA_ADDITIONS_B contains descriptive information to help you
identify your assets. Oracle Assets does not use this table to calculate
depreciation.
When you add an asset, Oracle Assets inserts a row into this table and
into FA_ASSET_HISTORY.
When you change the asset information stored in this table, Oracle
Assets updates it in this table. It also creates a new row in
FA_ASSET_HISTORY.
When you perform a unit retirement, Oracle Assets reduces the
CURRENT_UNITS by the units retired.
UNIT_ADJUSTMENT_FLAG is set to YES by the Additions form if you
change the number of units for an asset. The Transfers form resets it to
NO after you reassign the remaining units.
FA_ADJUSTMENTS stores information that Oracle Assets needs to
create journal entries for transactions. The posting program creates
journal entries for regular depreciation expense from information in
FA_DEPRN_DETAIL. Oracle Assets inserts a row in this table for the
debit and credit sides of a financial transaction. All the rows for the
same transaction have the same value in the TRANSACTION_HEADER_ID column.
The SOURCE_TYPE_CODE column tells you which program created
the adjustment:
? ADDITION Depreciation program
? ADJUSTMENT Expensed or Amortized Adjustment User Exit
? CIP ADDITION Depreciation program
? CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit
? CIP RETIREMENT Gain/loss program
? DEPRECIATION Depreciation program (Retroactive transactions and
expensed depreciation adjustments)
? RETIREMENT Gain/loss program
? RECLASS Reclassification user exit
? TRANSFER Transfers form
? TAX Reserve Adjustments form
? REVALUATION Mass revaluation program
The ADJUSTMENT_TYPE column tells you which type of account Oracle Assets adjusts.
DEBIT_CREDIT_FLAG is DR if the amount is a debit and CR if the amount is a credit.
ADJUSTMENT_AMOUNT is the amount debited or credited to the account.
ANNUALIZED_ADJUSTMENT is the adjustment amount for a period times the number of periods in a fiscal year. The depreciation program uses it to calculate the depreciation adjustment for an asset when you perform multiple retroactive transactions on the asset. Oracle Assets calculates ADJUSTMENT_PER_PERIOD by dividing the
ADJUSTMENT_AMOUNT for a retroactive transaction by the number
of periods between the period you entered the transaction and the
period that it was effective. For current period transactions, this column
is zero.
PERIOD_COUNTER_CREATED IS the period that you entered the adjustment into Oracle Assets.
PERIOD_COUNTER_ADJUSTED is the period to which the adjustment applies. It is the same as PERIOD_COUNTER_CREATED, unless you enter a reserve adjustment, in which case PERIOD_COUNTER_ADJUSTED is the last period of the fiscal year to which the adjustment applies.
CODE_COMBINATION_ID indicates the Accounting Flexfield combination Oracle Assets debits or credits for all transactions except reclassifications and intercompany transfers. This CODE_COMBINATION_ID is generated using the Account Generator, and the posting program does not perform any further processing.
Bill of Materials Key tables bom_inventory_components(2)
August 3, 2006 at 4:25 pm ? Filed under ERP Tables
BOM_INVENTORY_COMPONENTS stores information about bills of
material components. This table only stores single?level components.
COMPONENT_SEQUENCE_ID uniquely identifies each row. There is one row per component on an operation within a given date range.
This table is the main child table of BOM_BILL_OF_MATERIALS and
references the parent using BILL_SEQUENCE_ID. It uses EFFECTIVITY_DATE and DISABLE_DATE to time phase components into a bill of material. The Bill of Material window enforces that only one row for a component in an operation is effective at a given time.
REVISED_ITEM_SEQUENCE_ID points to the revised item on an ECO that affects a particular component row. If IMPLEMENTATION_DATE is null, the component is pending on an engineering change that has not yet been implemented. IMPLEMENTATION_DATE is either NULL (pending on ECO) or equals EFFECTIVITY_DATE (implemented).
OLD_COMPONENT_SEQUENCE_ID points to the original component row affected by this component row through an engineering change.
This table also contains certain columns relating to shipping, billing and accounting for revenue on a customer order through Oracle Order Management. CHECK_ATP determines if an ATP check is required for this item when placing an order and overrides the Item level ATP flag.
LOW_QUANTITY and HIGH_QUANTITY define minimum and maximum quantities of this component item that can be placed on an order if the customer chooses to override COMPONENT_QUANTITY.
OPTIONAL decides if a component is a required option class you have to choose from (for Option Class items) or a required item (for Standard items) when taking an order. For Option Class items, MUTUALLY_EXCLUSIVE_OPTIONS decides if one or more of its
components can be placed on an order when you pick this option class.
OPERATION_LEAD_TIME_PERCENT is denormalized for performance from BOM_OPERATION_SEQUENCES when rolling up cumulative lead times.
The ORGANIZATION_ID for the component can be derived from the
BOM_BILL_OF_MATERIALS table using BILL_SEQUENCE_ID.
Bill of Materials Key Tables (1)
August 2, 2006 at 4:21 pm ? Filed under ERP Tables
BOM_BILL_OF_MATERIALS stores information about manufacturing
and engineering bills of material. Each row in this table represents a
unique manufacturing or engineering bill and is identified by
BILL_SEQUENCE_ID.
Oracle Bills of Material stores planning bills, model/option structures
and standard bills in this table. The item?s BOM_ITEM_TYPE attribute
dictates the type of bill of material defined in this table. There is no
identifier in this table that differentiates planning, model, option class,
standard bills or product families.
If a bill shares its structure with another bill in the same or a different
organization, COMMON_BILL_SEQUENCE_ID refers to that common
bill. This is a mandatory column that equals BILL_SEQUENCE_ID if
there is no common bill reference.
If an engineering change order creates a new bill of material for an item,
PENDING_FROM_ECN stores the ECO number that originates this new
bill.

BOM_RESOURCES stores information about resources, overheads,
material overheads, and material sub?elements. COST_ELEMENT_ID
determines the type of the resource. Resources are additionally
classified as either material resources or outside processing resources
If the resource represents a currency unit, UNIT_OF_MEASURE stores
the currency code associated with the set of books the organization
points to.
BOM_OPERATIONAL_ROUTINGS stores information about
manufacturing and engineering routings.
If a routing shares its operation steps with another routing in the same
organization, COMMON_ROUTING_SEQUENCE_ID is a reference to
that common routing This is a mandatory column that equals
ROUTING_SEQUENCE_ID if there is no common reference If the
routing references a common routing, Oracle Bills of Materials populates
the INVENTORY_ITEM_ID of the common into
COMMON_ASSEMBLY_ITEM_ID
CST (Cost) Key tables cst_cost_types, cst_standard_costs (2)
August 1, 2006 at 4:18 pm ? Filed under ERP Tables
CST_COST_TYPES stores cost type definitions. The table is seeded with
three cost types: Frozen, Average, and Pending. The Frozen cost type is
used in standard costing organizations. The average cost type is used in
average costing organizations. All costs reference a cost type.
PL_ELEMENT_FLAG, PL_RESOURCE_FLAG, PL_OPERATION_FLAG, PL_ACTIVITY_FLAG values are used by the Cost Rollup to determine whether to group rollup costs by cost element, sub element, operation sequence number, or activity.
BOM_SNAPSHOT_FLAG is used by the Cost Rollup to determine
whether to save the bill structure when rolling up costs for a cost type.
CST_STANDARD_COSTS stores item cost and standard cost adjustment
history for every item for every cost update. Records are inserted
during the final phase of the Standard Cost Update process for every
item being updated or when the item costs are initially defined. The
adjustment columns for WIP may not include adjustments for resource
or overhead rates. They include standard cost adjustments for the items
issued and completed from WIP.
CST_COST_ELEMENTS stores the five seeded cost elements: Material,
Material Overhead, Resource, Outside Processing, and Overhead.
CST (Cost) Key Tables cst_item_costs , cst_item_cost_details (1)
July 31, 2006 at 4:16 pm ? Filed under ERP Tables
CST_ITEM_COSTS stores item cost control information by cost type.
For standard costing organizations, the item cost control information for
the Frozen cost type is created when you enter a new item. For average
cost organizations, item cost control information is created when you
transact the item for the first time.
CST_ITEM_COST_DETAILS stores detailed cost information for an item
for a cost type. It is the child table of CST_ITEM_COSTS.
There are three types of detailed item cost: User?defined, Defaulted,
and Rolled up. User?defined costs can be entered in the Item Costs
window. Defaulted and Rolled up costs are generated by Cost Rollup.
If the item does not have cost control information for the cost type you
are rolling up, Cost Rollup copies this level detailed cost information
from the default cost type and marks the rows as Defaulted. Previous
level costs are rolled up costs.
WIP Key tables wip_lines , wip_operations (1)
July 30, 2006 at 4:12 pm ? Filed under ERP Tables
WIP_LINES stores production line information. Each row includes a
line name, maximum and minimum rate information, throughput for
rate based lines (lead time), and the start and stop time information that
determines the amount of time per day that the line is available. Oracle
Work in Process uses this information when you associate a line with a
repetitive assembly and when you define a repetitive schedule on the
line. Production line information is optional for discrete jobs.
WIP_OPERATIONS stores information about job and repetitive
schedule operations. Each row represents a specific operation and
includes an operation sequence number, which orders the operations for
the job or repetitive schedule. Each row also includes the assembly
quantity completed at an operation, the quantity at each intraoperation
step, the department associated with an operation, the scheduled start
and completion dates for an operation, the operation?s count point and
backflush types and other operation attributes. In general, Oracle Work
in Process uses this information to control and monitor assembly
production on the shop floor.
WIP Key tables wip_entities , wip_discrete_jobs, wip_transactions (1)
July 29, 2006 at 8:07 pm ? Filed under ERP Tables
WIP_ENTITIES stores information about jobs, repetitive assemblies, and
flow schedules. Each row includes a unique entity name, the entity type,
and the assembly being built. Oracle Work in Process uses this information to control production activities and to ensure that entities with duplicate names are not created.
WIP_DISCRETE_JOBS stores discrete job information. Each row
represents a discrete job, and contains information about the assembly
being built, the revision of the assembly, the job quantity, the status of
the job, the material control method, accounting information, and job
schedule dates. Oracle Work in Process uses this information to control
discrete production.
WIP_TRANSACTIONS stores information about WIP resource
transactions. Each row represents a single resource transaction and
includes a unique transaction Identifier, a transaction date, the job or
repetitive schedule charged, the WIP operation and resource charges,
and the number of units of measure applied. Oracle Work in Process
uses this information to track resource charges and to calculate the
values stored in WIP_TRANSACTION_ACCOUNTS.
FA Key Tables fa_deprn_summary, fa_deprn_periods (1)
July 29, 2006 at 4:07 pm ? Filed under ERP Tables
FA_DEPRN_PERIODS contains information about your depreciation
periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book.
PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when
you opened and closed each book?s depreciation period.
Each time you run the depreciation program, it closes the current period
by setting PERIOD_CLOSE_DATE to the system date. It also opens the
next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row.
CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in
FA_CALENDAR_PERIODS.
FA_DEPRN_SUMMARY contains depreciation information for your assets.
Each time you run the depreciation program, it inserts one row into this
table for each asset.
PERIOD_COUNTER is the period for which you ran the depreciation program.
DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period.
YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period.
DEPRN_RESERVE is the total accumulated depreciation for this asset.
DEPRN_SOURCE_CODE tells you what program created the row
BOOKS Created by the Depreciation Books form, QuickAdditions
form, or the post mass additions program when you enter a new asset.
DEPRN Created by the depreciation program when you run depreciation.
ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset?s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.
For assets that use a diminishing value method, the ADJUSTED_COST is the
beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset?s net book value at the time of the adjustment or revaluation.
BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted
rate to give you the flat rate for the fiscal year. The depreciation
program uses this rate to calculate depreciation for an asset. This only
applies to assets that use both a flat?rate depreciation method and bonus
depreciation.
Can you count the number of products Oracle has? (2)
July 28, 2006 at 3:59 pm ? Filed under ERP Tables
Continued from previous list



HRI - Human Resources Intelligence
HXC - Oracle Time and Labor
HXT - Oracle Time and Labor Rules
IBA - iMarketing
IBC - Oracle iContent
IBE - iStore
IBP - Bill Presentment & Payment
IBU - iSupport
IBY - iPayment
ICX - Oracle Self-Service Web Applications
IEB - Oracle Interaction Blending
IEC - Oracle Campaign Plus
IEM - Oracle eMail Center
IEO - Call Center Technology
IES - Scripting
IEU - Oracle Universal Work Queue
IEX - Oracle Collections
IGC - Commitment Administration
IGF - Student Systems Fin Aid
IGI - Oracle International Public Sector Financials
IGS - Oracle Student Sytems
IGW - Oracle Grants Proposal
IMC - Oracle Customers Online
IMT - Oracle iMeeting
INV - Oracle Inventory
IPA - CRL Financials Projects
IPD - Oracle Product Development Exchange
ISC - Supply Chain Intelligence
ITG - Oracle Internet Procurement Enterprise Connector
JA - Asia/Pacific Localizations
JE - European Localizations
JG - Regional Localizations
JL - Latin America Localizations
JTF - CRM Foundation
JTM - Oracle CRM Mobile Foundation
JTS - Oracle CRM Self Service Administration
ME - Maintenance, Repair & Overhaul
MFG - Oracle Manufacturing
MRP - Oracle Master Scheduling/MRP
MSC - Oracle Advanced Supply Chain Planning
MSD - Oracle Demand Planning
MSO - Oracle Constraint Based Optimization
MSR - Oracle Risk Optimization
MWA - Mobile Applications
ODQ - Oracle Data Query
OE - Oracle Order Entry
OFA - Oracle Assets
OKB - Oracle Contracts for Subscriptions
OKC - Oracle Contracts Core
OKE - Oracle Project Contracts
OKI - Oracle Contracts Intelligence
OKL - Oracle Lease Management
OKO - Oracle Contracts for Sales
OKR - Oracle Contracts for Rights
OKS - Oracle Contracts Service Module
OKX - Oracle Contracts Integration
ONT - Oracle Order Management
OPI - Operations Intelligence
OTA - Oracle Training Administration
OZF - Funds & Budgets
OZP - Trade Planning
OZS - Oracle iClaims
PA - Oracle Projects
PAY - Oracle Payroll
PER - Oracle Human Resources
PJI - Oracle Project Intelligence
PJM - Oracle Project Manufacturing
PMI - Process Mfg Intelligence
PN - Oracle Property Manager
PO - Oracle Purchasing
POA - Purchasing Intelligence
POM - Oracle Exchange
PON - Oracle Sourcing
POS - Internet Supplier Portal
PQH - Oracle Public Sector HR
PQP - Oracle Public Sector Payroll
PRP - Oracle Proposals
PSA - Public Sector Applications
PSB - Oracle Public Sector Budgeting
PSP - Oracle Labor Distribution
PV - Partner Relationship Management
QA - Oracle Quality
QOT - Oracle Quoting
QP - Oracle Pricing
QRM - Oracle Risk Management
RG - Application Report Generator
RHX - Oracle Advanced Planning Foundation
RLA - Oracle Release Management Integration Kit
RLM - Oracle Release Management
SHT - Applications Shared Technology
SSP - Oracle SSP
SYSADMIN - System Administration
VEA - Oracle Automotive
VEH - Oracle Automotive Integration Kit
WIP - Oracle Work in Process
WMS - Oracle Warehouse Management System
WPS - Oracle Manufacturing Scheduling
WSH - Oracle Shipping
WSM - Shop Floor Management
XDP - Oracle Provisioning
XLA - Oracle Common Accounting Modules
XNB - Oracle eBusiness Billing
XNC - Oracle Sales for Communications
XNI - Oracle Install Base Intelligence
XNM - Marketing for Communications
XNP - Oracle Number Portability
XNS - Oracle Service for Communications
XTR - Oracle Treasury
ZFA - Oracle Financial Analyzer
ZSA - Oracle Sales Analyzer
Can you count the number of products Oracle has? (1)
July 28, 2006 at 6:52 am ? Filed under ERP Tables
This is a useful list covering most of the Oracle e-Business Suite products.
I bet there must be quite a number of products still in research and development and some will never get to see the light
out of the development labs.
Oracle Product Codes




ABM - Activity Based Management
AD - Applications DBA
AHL - Oracle Advanced Service Online
AHM - Oracle Hosting Manager
AK - Oracle Common Modules-AK
ALR - Oracle Alert
AMF - Oracle Fulfillment Services
AMS - Oracle Marketing
AMV - Oracle Market