Presidents of USA and their Birth Signs – Sankey Visualization

In this analysis, we will visualize the relation between the Age at Presidency, State of Birth and birth sign.

Read more at:

Comments Off on Presidents of USA and their Birth Signs – Sankey Visualization

US Consumer Law Attorney Rates

The hourly rate in any consulting business or practice increases by the years of experience in the field.

Read more at:

Comments Off on US Consumer Law Attorney Rates

Bigfoot vs UFO analytics

Bigfoot and UFO remain elusive but know their ways to make news from time to time.

Read more at:

Comments Off on Bigfoot vs UFO analytics

What is the most dangerous food at Olive Garden?

Olive Garden is one of the favorite destination for Italian food and today we got hold of the entire Olive Garden menu along with their nutrition data.

A typical meal at Olive Garden starts with a drink, appetizers [free bread sticks], main dish and finally the desert.

So going in the same sequence let see what the data menu for Wine and Beer has to offer.

Amount of Carbs per serving in your favorite Wine at Olive Garden

Read more at:

Comments Off on What is the most dangerous food at Olive Garden?

List of acquisitions by Microsoft a data journey

If we look into the SEC data for Microsoft and other tech companies, Microsoft spends the most in Research and Development from [by dollar]


Read more at:

Comments Off on List of acquisitions by Microsoft a data journey

Reddit 10 year growth analysis and visualization

In this article we will analyze the 10 year growth history of Reddit using simple visualizations.

Read more

Comments Off on Reddit 10 year growth analysis and visualization

NFL Play by play analysis using Cloudera Impala

Who won the most games against which losing team?


Read More at:

Comments Off on NFL Play by play analysis using Cloudera Impala

Q&A: How to find current Inventory Quantity?

Categories: ERP,ERP Tables

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 stores quantity on hand information by control
level and location.

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.

How to find a column in database

Categories: ERP,ERP Tables

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.