Oracle Apps Inventory Tables

Following are important tables in Oracle Apps Inventory

MTL_SYSTEM_ITEMS_B
 This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID.

MTL_ITEM_STATUS
This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.

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 if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.

MTL_ITEM_LOCATIONS
This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.

MTL_ITEM_CATEGORIES
This table stores inventory item assignments to categories within a category set.

MTL_CATEGORIES_B
This is the code combinations table for item categories.

MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.

MTL_CATEGORY_SETS_B
It contains the entity definition for category sets.

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.

MTL_SECONDARY_INVENTORIES
This is the definition table for the subinventory.

MTL_ONHAND_QUANTITIES 
It stores quantity on hand information by control level and location.

MTL_TRANSACTION_TYPES
It contains seeded transaction types and the user defined ones.

MTL_MATERIAL_TRANSACTIONS
This table stores a record of every material transaction or cost update performed in Inventory.

MTL_ITEM_ATTRIBUTES
This table stores information on item attributes.

MTL_ITEM_CATALOG_GROUPS_B
This is the code combinations table for item catalog groups.

MTL_ITEM_REVISIONS_B
It stores revision levels for an inventory item.

MTL_CUSTOMER_ITEMS
It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.

MTL_SYSTEM_ITEMS_INTERFACE
It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.

MTL_TRANSACTIONS_INTERFACE
It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.

MTL_ITEM_REVISIONS_INTERFACE
It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.

MTL_ITEM_CATEGORIES_INTERFACE
This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.

MTL_DEMAND_INTERFACE
It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.

MTL_INTERFACE_ERRORS
It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

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 Oracle Inventory.

Create, Modify and Migrate Tables in 12c

Creating a table :-

Below script will create a table in XX schema.

create table XX.XX_ORACLE_DEMO
(
TRX_ID NUMBER(15) not null,
TRX_NUMBER VARCHAR2(30),
TRX_TYPE VARCHAR2(30) not null,
AMOUNT   NUMBER
);


This will create one object in DB.

Use below query to verify :-

select * from dba_objects where object_name like 'XX_ORACLE_DEMO%';

This will give you one record i.e. only table info.

-- Upgrade the Table .  ( to create Editioning View)

begin
ad_zd_table.upgrade('XX', 'XX_ORACLE_DEMO');     --- Object owner  , table name are paremeters.
end;
/

select * from dba_objects where object_name like 'XXG_AR_DEMO%'

Now it wil give you 3 records. Once you run upgrade script it will create Editioned view and synonym in apps for the table.


Altering  a table :- 


 alter table  XX.XX_ORACLE_DEMO add BU_CODE VARCHAR2(30);

Table Shows New Column

select * from XX.XX_ORACLE_DEMO

But Editioning view is not showing new column

select * from XX.XX_ORACLE_DEMO#


Run the below script to Refresh the Editioning View

begin
ad_zd_table.patch('XX', 'XX_ORACLE_DEMO');     --- Object owner  , table name are paremeters.
end;
/

Migrating Tables to Other instances :-  In 12.2 oracle provides new feature to migrate tables using XDF file which can be downloaded from one instance and uploaded to another instance. Below are the commands which can be used to download and upload XDF files.


XDF Generation:- 

Download :-
$FND_TOP/bin/xdfgen.pl apps/apps_pwd XX_ORACLE_DEMO owner_app_shortname='XX'

Upload :-
$FND_TOP/bin/xdfcmp.pl xx/&xx_pwd XX_ORACLE_DEMO.xdf apps/&apps_pwd

Oracle EBS R12.2 New Features and Guidelines

Introduction:-

Oracle E-Business Suite Release 12.2 includes new Online Patching feature that allows for Oracle E-Business Suite patches to be applied while users are still online and using the Oracle E-Business Suite.

The implementation of Oracle E-Business Suite Online Patching relies on the new Edition-based Redefinition technology from the database and the new Online Patching infrastructure, including the new file system.

Database Editions:-

Online Patching Interacts with 3 Edition Types

Run Edition :- 
                > The Edition Currently in use by running application
                > This is always the default Database edition.

Patch Edition :- 
              > The edition currently in use by the the patching tools.
              > The edition is only present when patching is in progress.
              > Always the direct child of the run edition.
Old Edition(s) :-
             > There maybe Zero or More Old editions.
             > When the patch edition is promote to productions the previous run edition is                               now regarded as an old Edition.
             > Only retained until a full cleanup operation is run in no effect in the running                                 application.

Editioned Database Objects:-

Editioned objects may have different definitions in each database edition. This means that such objects can be easily patched (in the patch edition) without affecting the running application (in the run edition).

All code type objects support editioning 

The Database automatically manages multiple copies of these objects during patching

–Package Spec

–Package Body

–Function

–Procedure

–Views

–Editioning Views

–Triggers

–Types

– Synonyms

Non-Editioned Database Objects :-

 Non-editioned objects have the same definition shared across application editions. Such objects must be patched carefully to avoid affecting the running application

        Storage objects do not support editioning

        Online Patching uses advanced features of EBR to manage these objects during patching

–      Tables

–       Indexes

–      Materialized Views

–       Sequences

All code must access the data model via the APPS 

 > synonym

> APPS synonym points to the Editioning View (Logical Model)

> Any code accessing the physical model risks accessing obsolete columns.

MOAC in Oracle Apps (Multi Org Access Control)

Introduction

Multi-Org architecture was first introduced in Oracle Applications Release 10.6.   Its primary objective was to secure data from unauthorized access by individual in different Operating Units in enterprise. Although security by Operating Units has been widely used as a reliable method to protect from unauthorized access to information, many customers have requested to increase flexibility to enable user to access one or more Operating Units per user responsibility.  Multi-Org Access Control feature allows reduction in operating costs, but more importantly, it lays a more flexible software foundation to allow Oracle Applications to support complex business model such as Shared Services without compromising data security.

Access Control

The Multi-Org Access Control feature, also known as "Security by Operating Unit", will enable users to access to secured data in one or more Operating Units within one responsibility.  The feature uses Security Profile concept introduced in Release 11i Oracle Human Resources Management System, which allows system administrator to predefine the scope of access privilege as a profile option.  A security profile may be defined in hierarchical or listing mode, which may consist one or more Operating Units.

A profile option, "MO: Security Profile", is used to associate predefined security profile to a user responsibility.

The following two process flows illustrate current and new models for defining Multi-Org.

Select Operating Unit

With the ability to access multiple Multi-Org Operating Units from a single application responsibility, users are able to enter setup and transaction data and run concurrent programs for multiple Operating Units without having to switch the responsibility.  Except in a few cases, all Multi-Org enabled setup and transaction user interface will have "Operating Unit" field.  Users will be able to select the Operating Unit from a list of values assigned to the user via the security profile and responsibility.   Operating Unit context can also be defaulted or derived from other operating unit sensitive attribute.   Detail on these variations is covered in subsequent sections.

Process for enabling MOAC:

Creating the Security Profile :

Use the screen below to create a security profile:

Run Security List Maintenance Program

This concurrent program must be run from the Standard Report Submission screen after creation of security profile.  It populates the PER_ORGANIZATION_LIST table with the list of organizations included in the security profile.  The organizations included in the "View All" security profiles are not stored in this table.

The "Security List Maintenance Program" could be preferably run for one named security profile to prevent disturbing other security profile setup. 

Assign MO: Security Profile to Application Responsibility

The last step is to assign a security profile to user responsibility via System Profile Values window.  "MO: Security Profile" can be set at Site and Responsibility level.

What is DUAL table in Oracle ?

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user sys but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table.

The various operations which can be done using DUAL.

1. SYSDATE
2. Calculations
3. Basic Oracle functions etc..

Examples :-

System Date :- 

Calculations :- 

Oracle Function i.e. Length :- 

Oracle AR Lookups Navigation

Navigation :

1. Log in to Oracle Apps
2. Switch to AR XX Superuser or similar responsibility (as per your organization norms)
3. Go to Setup -> System -> Quickcodes  -> Receivables
4. Query for your lookup and retrieve the info.

Status code and Phase code in fnd_concurrent_requests

We always keep getting confused about the meanings of status_code and phase_code columns in fnd_concurrent_requests table. Here is the brief descriptions of statuses columns in the table.

Table name :-
FND_CONCURRENT_REQUESTS

Column name:- 
STATUS_CODE

A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting.

Column Name :-
PHASE_CODE.

C              Completed
I                Inactive
P              Pending
R              Running.


Query :-

SELECT status_code, phase_code
FROM fnd_concurrent_requests
WHERE request_id = :p_request_id