Web Design Applications, Dashboards & Mashboards

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.

Once, at a client site I was asked to do a drill through report from Purchasing to Fixed Assets. I don’t remember the exact details but it had something to do with Purchase orders. I knew little bit about Oracle Purchasing but had not worked enough on Fixed Assets. So I thought maybe I should start reading the FA user guides and the FA technical reference manuals. Most of my knowledge in ERP is gained through reading the User guides and TRMs but having to go through them requires significant patience.

First I had to find out which table stores the relevant PO details and then figure out how the information can be linked back to Purchasing. The client kept asking me on how to get started and I asked for some time to research on the drill link. I started reading the manuals but eventually gave up as I couldn’t locate it(not that it did not have the information).

Suddenly browsing through the table structure in the database, I realized that maybe I can run a query against the Database meta data and find out if there is a PO related item in a FA related table.

So I ran the below query

“select * from dba_tab_cols all_tab_cols where table_name like ‘FA%’ and column_name like ‘PO%’ ”

Bingo! This gave me a list of all the possible Fixed Assets table which contained the Purchasing columns in it. This became the starting point for my research and eventually traced back to the root information, built the queries and finally informed the client.

So what is dba_tab_cols? This is a database meta table maintained by Oracle database itself. It stores every column name for each table name in the database. You can query your Oracle database right now to see how it looks.

Before leaving the site, I showed the trick to the client on how this was accomplished and told him that this way you can find information yourself rather than calling anybody to help. He was really happy to have found this new “Magic wand”

Since then this trick has become my consulting arsenal. You shoulld too make it a habit of using it to stay sharp and ahead of others.

I developed similar queries for Oracle Flexfields and Discoverer which I will post soon.

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.

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.

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

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.