Understanding MTL_SERIAL_NUMBERS.CURRENT_STATUS

So here I am after a long time with a very short post..don’t mind !!!

We often came across using serial number in oracle applications though its shipping the serial for a sales order, a repair order or receiving a serial into inventory. There would be many cases where we have a device in hand with a serial number ready to be shipping but system didn’t allow its saying some error message.. I am not going deep in what all error message comes but the bottom line is where is the serial and what is its current status.

If we query mtl_serial_numbers.current_status it gives back some number which represents its actual status. So lets find out what those numbers represent.

Serial Status	Meaning
1	Defined but not used
3	Resides in stores
4	Issued out of stores
5	Resides in intransit
7	Resides in receiving
8	Resides in WIP

How To Apply Patch In Oracle Application

Patching is one of the very basic task that DBA perform. Well I am not out here as DBA, though I like stuffs what they do. I believe many out there love to have there own Vision Demo instance to check out seeded functionality and explore new solutions. Vision Demo comes with basic setups and don’t carry latest patches and fixes. So we may end up having very basic functionality and not able to explore new features. That brings us here to understand how can we apply patch.

Lets get started…

There are steps involved in applying patch to Oracle Applications.

• Validate Patch In Oracle Application :- Before proceeding any further first check if the patch is already applied to the instance or not. To check if the patch is already applied follow the this post - Check Patch in Oracle Application
• Download Patch : Get the patch from Oracle Support (Best know as - Metalink) and go through the readme file. This will tell you if any pre-requisite is there and if you need to perform any activity after patching. Following screenshots will tell you how to down and go through readme file.

How To Check Patch Applied In Oracle Applications

In this section we will try to find out if a patch is applied in Oracle Application or not. I believe you all know what a patch and why we use it. Without further delay lets get stared.

There are two ways you can identify if the patch is applied or not.

• Validation From Application

To validate the patch from Oracle Application please follow the below step :-

• Navigate to System Administration / System Administrator –> Oracle Application Manager –> Patching and Utilities.

• Enter the Patch Number in Patch filed in search page that opened.
• Enter additional criteria if you want to.
• Click on GO.

• You can see the result in bottom section.

• Validation From Database

To validate the patch from Database please follow the below steps :-

• Login to database using SQL Plus or any other tool like SQL Developer / Toad etc.
• Run the below query and validate the result.

  select * from ad_bugs
  where bug_number = :pi_patch_number