Upgrade to Oracle Database 12c – Single Instance

Upgrade to Oracle Database 12c – Single Instance

With every release of the Oracle database there is always an upgrade path that should be followed. For many, they rush out and download the software and attempt an install/upgrade; often before knowing what needs to be done. This can lead to disasters and potentially affect the business if not done successfully.

The upgrade path for going to Oracle Database 12c (12.1.0.1) is pretty straightforward. If you are running an Oracle Database that is a supported direct path upgrade to Oracle Database 12c you will have no problems using any of the supported upgrade methods. If you are not on a version that supports direct path upgrade, you will need to upgrade to a supported version before upgrading to Oracle Database 12c.

Supported Direct Upgrade Paths:

  • Oracle Database 10g (10.2.0.5)
  • Oracle Database 11g (11.1.0.7)
  • Oracle Database 11g (11.2.0.2 or later)

There are three supported upgrade paths/tools. All the upgrade options have their own issues that may be ran into. The upgrade options that are supported with Oracle Database 12c are:

  • Database Upgrade Assistant (DBUA)
  • Manual Upgrade (script based)
  • Export/Import

For the purpose of this article, let’s focus on using the Database Upgrade Assistant (DBUA).

Oracle has improved the DBUA to provide a seamless upgrade. If errors arise we now have options to fix them directly from DBUA. Additionally, the DBUA makes monitoring the upgrade easier. To use the DBUA we have to go to the Oracle Database 12c home and start it by running dbua. From working with Oracle Database 12c through beta testing and now general release, it is a best practice to run the preupgrd.sql to see what we may need to fix before running DBUA.

Run the PREUPGRD.SQL

In order to run the preupgrd.sql file, we first need to install the new binaries into an Oracle home for 12c. Once the binaries are in place, we need to setup our environment to connect to the database we want to upgrade.

In my test environment, my Oracle Database 11g settings are:

ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.3.0/db_1

Next we need to go to the directory where the preupgrd.sql file is located:

#> cd /opt/oracle/product/12.1.0.1/dbhome_1/rdbms/admin

Finally, we need to connect to the 11g database with SQL*Plus and run the preupgrd.sql file:

#> sqlplus / as sysdba
SQL>@preupgrd.sql

When the preupgrd.sql script is done, we will be given the locations of the files that we need to reference for verifying and correcting any issues with our environment.

Results of the checks are located at:

/opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.log

Pre-Upgrade Fixup Script (run in source database environment):

/opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fixup Script (run shortly after upgrade):

/opt/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql

Review these scripts and correct anything that needs to be fixes. Once these corrections are made, running the DBUA will be simpler. If there are any errors listed in the preupgrade.log, these need to be corrected before proceeding.

Running DBUA

Once everything has been corrected after reviewing the preupgrade.log, we can start the Database Upgrade Assistant (DBUA).

To start the DBUA, we need to go to the Oracle Database 12c home and run dbua:

#> cd /opt/oracle/product/12.1.0.1/dbhome_1/bin
#> ./dbua &

This will start the GUI to begin the upgrade. You will notice that I did not change anything in my environment. I’m still pointing to the 11g environment.

ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.3.0/db_1

Once the DBUA starts, you will notice that we are at step 1 of 11.  The number of steps will change depending on the options that are selected. Step 1, we have two options:

  • Upgrade an Oracle Database
  • Move an existing 12c database to a new 12c oracle home

For the purpose of the update, we can just click next and move on.

Screenshot: Oracle 12c database upgrade assistant select operation

Part of the upgrade process we need to identify which Oracle home we want to upgrade. Since we started the DBUA with the Oracle home set to the 11g home, the DBUA will give us all databases associated with that Oracle home.  Select the database to be upgraded, then click next.

Screenshot: Oracle 12c database upgrade assistant select database

In step 3 we see results that look similar to what we looked at in the preupgrade.log. As you can tell, the DBUA is actually running the same preupgrd.sql script and returning the results back to the GUI. Additionally, Oracle has made the DBUA more intelligent by allowing us to select whether we can fix, ignore or revalidate the issues found by the preupgrd.sql. Make the selections you want and continue.

Screenshot: Oracle 12c database upgrade assistant prerequisite checks

Step 4 is one of the most interesting screens in the DBUA. Oracle has made a fundamental change to how upgrades are handled. Upgrades can now be done in parallel! This is accomplished by using a new perl script, catctl.pl. The number of parallelism is calculated based on the number of CPUs in the server. Additionally, we can recompile object now in parallel and have DBUA perform the upgrade of time zones, gather statistics and make tablespaces read only during the upgrade. Click next to continue.

Screenshot: Oracle 12c database upgrade assistant upgrade options

Step 5 allows us to select how we want to manage our Oracle Database 12c environment. We can either select to use EM Express, the new web interface for Oracle Database 12c that replaces the database console in previous versions or we can register that database with Oracle Enterprise Manager 12c (OEM).

Note: If the OEM12c agents are already installed on the server where the upgrade is being done, the DBUA will pick up the need information automatically.

Screenshot: Oracle 12c database upgrade assistant management options

Step 6 allows us to specify where we want to move our data files and setup our Fast Recovery Area (FRA). We can also configure if we want to use Oracle Managed Files (OMF) at this point.

Screenshot: Oracle 12c database upgrade assistant move database files

Step 7 gives us the option to migrate the listener for 11g over to 12c (if not already up). In the image below the 12c binaries are already installed and have a listener running from it. What’s important on this screen is the ‘Migrate’ column. This column will tell you whether or not the listener is going to be migrated.

Screenshot: Oracle 12c database upgrade assistant network configuration

In step 8 we have the option to create a new backup of our database before upgrading. If we are confident in our backup strategies, we can tell the DBUA not to make a backup by selecting the radio button: ‘I have my own backup and restore strategy’.

Screenshot: Oracle 12c database upgrade assistant recovery options

Finally, we reach the summary screen (Step 9). This screen will show us what the DBUA thinks it will be doing. One should always review this screen and make sure everything appears to be in order before clicking ‘Finish’.  Once we click finish, the upgrade will begin and we can monitor it via the progress screen.

Screenshot: Oracle 12c database upgrade assistant summary

On the progress screen, we can watch the progress of the upgrade. The arrows on this screen can be expanded to show the step that the DBUA is currently on. Another nice feature on the progress screen is the ability to see how long something takes to complete; this is found in the time column.

Screenshot: Oracle 12c database upgrade assistant progress

Once the upgrade is complete, the ‘Stop’ button will change it’s wording and say ‘Upgrade Results’. When you click this button, the interface will change and provide you with the results of the upgrade.

Screenshot: Oracle 12c database upgrade assistant results

At this point, the upgrade is done and the DBUA can be closed. Click the ‘Close’ button to exit the GUI.

Verify the Upgrade

There are multiple ways that the upgrade can be verified. The easiest way is the check the /etc/oratab file. Once the upgrade is done, this oratab file should have changed the Oracle home to match the 12c binary location.

Another way to verify is to check the environment variables from the command line:

#> env | grep ORA

ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/12.1.0.1/dbhome_1

Lastly, we can use SQL*Plus to check the version of the database:

#> sqlplus / as sysdba
SQL> select banner from v$version

I hope this article has shown you how easy it is to upgrade an Oracle Database 11g up to the latest release of the Oracle Database 12c.

Advertisements

It’s Time to Upgrade to Oracle Database 12c, Here is Why, and Here is How

db2

When the whole Oracle database community will be dealing with the questions around upgrading to Database 12c from 11g (and some even older releases).   Oracle released the major version upgrade of Database 12c in July 2013, which also triggered the 11g version end-of-life support cycle.  The 11g support clock is ticking, with the first step of Extended Support starting Jan 2015 (in order to get Extended Support you will have to be on Oracle Database 11.2.0.4, the terminal patch set).

{Posted on October 16, 2014 by Randy Hardee}

Oracle Database Version Upgrade Matrix

But instead of focusing on the 11g de-support issue as the primary driver of the 12c upgrade, let’s look at some of the reasons why it makes sense to upgrade to 12c sooner rather than later.

Reason 1 – Better Upgrade Experience:  The major release upgrade process has become much better and less risky since the old days.  Oracle has automated much of the process with the Upgrade Assistants.  There are many more options and proven paths for performing the upgrades and data migration between versions if necessary.  There are lots of online resources and a whole series of Oracle technical seminars dedicated to the 12c Upgrade program

Reason 2 – Dollars and Cents:  Besides the additional uplift for Extended Support (after the waive period from Jan 2015-2016, it ramps to 20%).  Think about it this way: you are already paying for the upgraded 12c new features and improvements with your Support dollars, even if you are not adding any new licenses or options.  The vast majority of your 11g Support Renewal went into R&D to develop the innovations and improvements of 12c, so why not upgrade now and get your money’s worth?  If you are planning to patch your older, pre-11.2.0.4 database to the terminal release just to pay the extra Extended Support uplift, why not upgrade to 12c and save yourself lots of time and money?

Reason 3 – Better Support Experience:  The 12c release has been out for over a year now, and support for the latest and greatest release always seems to be better than for older releases.  While the Extended Support programs for 11g provides a lot of runway for the 12c upgrade, Oracle’s support resources will inevitably be more focused on the newer current releases, and will become more so over time.  Once again, according to Oracle, only a small percentage of your annual maintenance fees apply directly to software fixes, so it makes sense to maximize your support dollars by getting off old versions of software as quickly as possible.

Reason 4 – ISV and Packaged Applications: While there are some packaged apps that have been laggards in certification on new Oracle releases, many of the big players have already completed or announced 12c certification.  The “second dot release” in the 12c series (12cR2 aka 12.1.0.2) has been out since July 2014, and the “second release” usually triggers the ISV community’s certification process, since it usually contains the most stable release, and contains any missing new features that didn’t make it into the “first release” (like In-Memory Database).

Besides the reasons for upgrading outlined above, there are many compelling new features and improvements in 12c.

Here are the three big hitters:

  • Multitenant and Pluggable Database – This biggest and most dramatic new feature enables easier consolidation of multiple databases and clouding of databases into IaaS, PaaS, and DBaaS deployments.  It has improved management and reduced time and effort for database upgrades, backup, recovery, etc.  More effective server resource utilization allows for even greater levels of consolidation than 11g.   You can also use the pluggable database feature in a single-tenant architecture without new licenses/options.
  • Automatic Data Optimization – A Heat Map feature monitors database read/write activity enabling DBAs to easily identify the data that is hot/warm/cold in terms of access frequency.   Also smart compression and storage tiering, automatically compress and tier data based on the activity and age of the data.  This applies to OLTP, Data Warehouse, and Archive data.
  • In-Memory Caching, Compression, and Column Store – This isn’t the TimesTen Database cache, but is a new set of features which allow for large amounts of memory-based data caching beyond the traditional buffer caches, and the cached data is stored in a compressed columnar format.  This can dramatically improve performance and optimize server resources, and is surprisingly easy to implement since it is built into the database kernel and transparent to the application.

In summary, there are “My Oracle Support” notes and plenty of online resources on the 11g de-support timeline and details. But every Oracle DBA knows this is always a big factor in the decision of when to make the jump from one major release to the next.  If we look at this “upgrade” decision historically, many Oracle database shops would defer the major release upgrade process as long as possible by patching to the terminal dot release and pay the extra dollars for Extended Support.  But the 12c release has removed many of the obstacles to upgrading, as well as provides many technical benefits and business value that make a really a strong case for upgrading as soon as possible.

managed-services1

If you are exploring the idea of a 12c upgrade and need some help, take a look at a new services bundle we’ve put together BrightStar Oracle Database 12c Upgrade Solutions.  This services bundle led by the BrightStar has been successful with other BrightStar customers, that were looking for expertise and experience in building an enterprise 12c upgrade road map and then provided a cost effective clear plan to prepare, upgrade / migrate, report and even then even manage those new 12c environments.

Contact us at info@bslion.in or visit our business page at http://www.bslion.in to know more

 

BrightStar : value added services provide maximum return

masterclass

BrightStar – advanced oracle training solutions leverage our proven Oracle expertise to help our customers master the Oracle technologies they’ve deployed.  We educate our customers so they can reap the full value of their investment. Our strong relationships with Oracle product and development teams ensure we have access to the newest Oracle technologies before they are released.

The BrightStar team offers both on-site and remote training programs to provide the right solution to fit your business needs.

– See more at: http://www.eudoract.wordpress.com

eudora-ad

Data flow for Order-to-Cash cycle

  1. Order Entry
    This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.
  • Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table OE_ORDER_HEADERS_ALL FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N), Primary key=HEADER_ID
  • No record exist in any other table for this order till now.
  • Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. OE_ORDER_LINES_ALL (FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N, OPEN_FLAG = Y) Primary key= LINE_ID

2.Order Booking
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.

  • OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
  • OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAG updated Y)
  • WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
  • WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

*In shipping transaction form order status remains “Ready to Release”.

At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

  1. Reservation
    This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the MTL_DEMAND and MTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.
  2. Pick Release
    Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from ‘Release Sales Order’ form or ‘Pick release SRS’ program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from ‘Shipping Transaction form. For this case Pick Release is done from ‘Release Sales Order’ form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

  • If step 3 is not done then MTL_RESERVATIONS gets updated now.
  • WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)
  • WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)
  • WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)
  • MTL_TXN_REQUEST_HEADERS
  • MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
  • (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
  • MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)
  • MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
  • MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains “Released to Warehouse” and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5.Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes ‘Picked’ on Sales Order and ‘Staged/Pick Confirmed’ on Shipping Transaction Form.

  • MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
  • OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
  • MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TRX_SOURCE_LINE_ID)
  • MTL_TRANSACTION_ACCOUNTS
  • WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
  • WSH_DELIVERY_ASSIGNMENTS
  • MTL_ONHAND_QUANTITIES
  • MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at ‘Sales order issue’)
  • MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at ‘Sales order issue’)
  • This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6.Ship Confirm
Here ship confirm interface program runs in background.

The items on the delivery gets shipped to customer at this stage.

  • OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
  • WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
  • WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
  • MTL_TRANSACTION_INTERFACE
  • MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
  • MTL_TRANSACTION_ACCOUNTS
  • Data deleted from MTL_DEMAND, MTL_RESERVATIONS
  • Item deducted from MTL_ONHAND_QUANTITIES
  • MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
  • MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , ‘Issued out of store’)

7.Enter Invoice

After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.

RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in

RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8.Complete Line
In this stage order line level table get updated with Flow status and open flag.
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9.Close Order
This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)

OE_ORDER_HEADERS_ALL

How to Calculate Freight Charges in Order Management

How to Calculate Freight Charges in Order Management

Solution and Content contributed by Bill Foster.

This is a workaround solution for one of the most asked question by Clients implementing Order Management “How to Calculate Freight Charges?” Thanks to Bill for sharing this wonderful solution. Oracle doesn’t have solution for this even in R12.

Problem:  Add Freight Charges to a Sales Order in Order Management and have Oracle/Vertex tell how much (if any) Taxes should be charged on the Freight Charges.

Solution:

  • Use an Item called ‘FREIGHT’ which is basically treated as any other Item where you can establish the tax code on the line and charge tax on freight appropriately. Setup the Item in Inventory and have to pick and ship it for each order.
  • Use a “Promotional Modifier” which is qualified by many different pricing attributes (including Freight Terms, Shipping Method, Order Category, etc.).  So the idea is – “Buy any item and get Freight at ‘X’ price”.  This solution works for the credit card orders, cash / check orders and advance pay.  Net payment term orders typically use actual freight cost. You may also have to setup a new defaulting rule to handle defaulting the line type based on the item being FREIGHT.  This was to ensure the correct COGS account was used to account for the freight.
  • Profile Options to Consider:
    • OM: Charges for Included Item: When this profile option is set to ‘Y’, and the calculate price flag of the order line with an included item is either Calculate Price (Y) or Partial Price (P), then the eligible freight charges are applied to the order line. For backordered lines within Included Items, both the profile options OM: Charges for backorders and OM: Charges for Included Item need to be set to Y to view and apply any freight charges.
    • OM: Charges for backorders: If the profile option is set to ‘Y’, the system will set the calculate price flag to ‘P’ and freight charges are calculated for backorder lines. If the profile option is set to ‘N’, the system will set the calculated price flag to ‘N’ and freight charges are not calculated for backorder lines.
    • QP: Selling Price Rounding Options: Determines if your freight charges are rounded. See: Oracle Advance Pricing Implementation Guide, Profile Options.
    • OM: Interface Freight Tax Code From Line: The default value set at site level is ‘No’, so that existing customers are not impacted due to the change. Tax_code is now interfaced to AR for freight lines that are interfaced as revenue lines when the profile is set to ‘Yes’. Tax code is populated in the same way as the sales order line along with which this freight line is interfaced.
    • Tax: Inventory Item for Freight: This profile option is used only when the freight item is passed as revenue line. If you set the value of this profile option to Inventory Item then the Invoicing module passes this item for freight charges, which will be treated as revenue lines. This profile option can only be set at the Application or Site level.
    • Tax: Invoice Freight as Revenue:  If the Receivables profile option TAX: Allow Tax Code Override is set to ‘YES’, and profile option TAX: Invoice Freight as Revenue is set to ‘YES’, then freight charges are treated as revenue lines, and the Invoicing module will pass VAT tax and associated sales credits for processing.
    • OM: Credit Salesperson for Freight on Sales: This profile option specifies whether to credit the Salesperson on the invoicing line or order header for freight charges when the freight charges are treated as revenue.
    • Debugging Freight and Special ChargesClick Here
    • Metalink Notes to Consider:
      • 391160.1
      • 394055.1
      • 362820.1
      • 428243.1
      • 392938.1
      • 227719.1
      • 258981.1

Hope this helps anyone interested.  Surely this is not the only solution, but this can be achieved with no customizations and use seeded functionality only

How to Setup Oracle inventory – Required Steps

How to Setup Oracle inventory – Required Steps

This post will cover brief overview of steps required to implement the parts of Oracle Applications specific to Oracle Inventory.There are some required, optional steps and for some steps you have to check some pre defined system default values that weather it suits your business needs or change them. Perform optional steps only if you are using that business functions.

Step- 1. Define flexfield of Inventory Items (Required)
You must design and configure your System Items Flexfield before you can start defining items. On basis of your requirement specific no of segments with fixed length are defined. Once you define the structure of your flexfield and any applicable value sets, you must freeze and compile your flexfield definition.

Step-2. Define flexfield of item categories (Required)
Before defining items must design and configure your Item Categories Flexfield as all items need to be assigned with categories. After defining compile the flexfield definition to enable the Categories Flexfield pop-up window. Multiple structures for Item Categories Flexfield can be defined.

Step-3. Define flexfield of item catalog group (Required)
If you do not use catalog group still at least one segment must be enabled. To group items according to certain descriptive elements, Item Catalog Group Flexfield need to be configured. Defining and Compiling the flexfield definition enables the Item Catalog Group Flexfield pop-up

Step-4. Define flexfield of stock locators (Required)
In order to keep track record of locators for inventory items stock locators need to be configured. e.g. bin, row indicators. Must compile stock locator flexfield even locator control is not implemented

Step-5. Define flexfield of account aliases (Required)
If you want to define logical references to frequently used account number and combinations and use them as transaction source types,you need to configure your account aliases flexfield and define account aliases.

Step-6. Define flexfield of sales orders (Required)
If you want to ship items from inventory to meet customer demand as specified in a sales order, regardless of whether you are using Oracle Order Entry, you must configure your Sales Orders Flexfield. Sales order flexfield must be configured if items will be shipped from inventory.

Step-7. Define Organization Calendar (Required)
If you want to predict needs of your material or to plan material requirement then you can configure workday calendar for this.Work day calendar provide lot of flexibility in terms of shifts, pattern for working days also you can configure exceptions.

Step-8. Define organizations. (Required)
Organization define different entities in company which may have different manufacturing facilities, warehouses, distribution centers, and branch offices.

Step-9. Define organizations Parameters (Required)
You must define the control options and account defaults for your organization before you can define items or perform any transactions. You can assign a unique short code to your organization and use this code to identify the organization with which you want to work. You must also specify the master organization and the costing organization for your organization

Step-10. Setup Change Organizations (Required)
This setup enables you to change organization you define in oracle inventory.using the Change Organization window you can log out and log back in to Oracle Inventory.

Step-11. Define Inventory Relations (Required)
For inter company relations between two operating units (typically the Shipping and Selling organizations) in a multi-organization environment, you must define the relationship in the Intercompany Relations window.

Step-12. Define unit of measure classes (Required)
You need to define unit of measure (UOM) classes and the base unit of measure for each class. UOM classes represent groups of units of measure with similar characteristics, such as Volume or Length. All items having similar characteristics fall under same UOM class like Kilogram or Length.

Step-13. Define Units of Measure (Required)
You need to define units of measure for tracking, moving, storing, and counting items. Each item that you define in Oracle Inventory must have a primary unit of measure and each transaction you perform in Oracle Inventory must have a unit of measure associated with the transaction quantity.

Step-14. Define Sub inventories (Required)
Sub inventory groups inventory logically or physically, at least one sub inventory must be assigned to each organization.

Step-15. Define Item attribute controls (Required)
Attributes are detail information about items.Each attribute is maintained at master level or organization level.If an attribute is defined as master level then it can only be updated at item master level and attributes maintained at item/organization level can only be updated at this level only.

Step-16. Define Categories (Required)
You must define categories to group items that share similar characteristics. You must define the flexfield structure to be used for each category you define. The flexfield structure you select for a category will determine how it may be grouped with other categories. (Similar flexfield structures can be grouped.).

Step-17. Define Category Set (Required)
Sets are defined to further group categories more functionally.Also define at least one default category set.

You need to define category sets to create different category grouping schemes. Category sets group your categories into functional areas, such as inventory, cost, purchasing, order entry, and so on. You can associate different flexfield structures with each category set, thereby introducing different naming structures for your categories. You may only group categories with the same flexfield structure as the category set in a single category set. For example, the catgories metal, rubber, and paper might be members of the Inventory category set, while taxable and non-taxable might be members of the Cost category set. You can also a create category set such as Priority, with members like high, medium, and low and use it as your personal item grouping mechanism for a report.

Step-18. Define default Category Set (Required)
You need to define a default category set for each of the seven predefined functional areas. Oracle Inventory will automatically assign items defined for use by a particular functional area to the category set associated with the functional area. Oracle Inventory defaults the appropriate category set in all the category set fields in the products that correspond to the functional areas. You may choose the same category set for more than one functional area if you have identical ways of grouping your items across those functional areas

Step-19. Define statuses (Required)
Statuses are defined to restrict or enable item for different functional areas.

You need to define statuses that you can assign to items, denoting the level of activity you allow for them. A status is a set of Yes/No values for the status attributes. Status attributes are flags that exist for each functional area for which you enable an item: stockable, transactable, purchasable, build in WIP, customer orderable, internal orderable, BOM allowed, and invoice enabled. When you define an item, you can use statuses to control the values of or provide default values for the status attributes.

Step-20. Define Cost Types (Required)
Some predefined types are already defined but you can also define your own cost type.

You need to define cost types before you can start entering item costs. A cost type is a set of costs, used for historical, current and future costs, as well as for simulation purposes. You can create as many cost types as you need, but Oracle Inventory is installed with three predefined cost types: Frozen, Average, and Pending. These are costs currently in use for an item and include material and overhead charges.

Step-21. Define Accounting Periods (Required)
Periods are defined in oracle General Ledger and in oracle inventory can be opened using inventory periods.

Before you can use Oracle Inventory to enter transactions, you need to open an accounting period. You must define your accounting periods in Oracle General Ledger, and open them for Oracle Inventory using the Inventory Accounting Periods window. Oracle Inventory allows you to have multiple periods open at any given time.

Step-22. Set profile options (Required)
Profile options specify how Oracle Inventory controls access to and processes data. In general, profile options can be set at one or more of the following levels: site, application, responsibility, and user.

Oracle Inventory users use the Personal Profile Values window to set profile options only at the user level. System administrators use the System Profile Values window to set profile options at the site, application, responsibility, and user levels.

Use of different Purchase order types

Standard Purchase order: This type of PO is used when you know the Item, Price, Delivery Schedule and payment terms. Most of the time Standard PO is used to fulfill sporadic demands or say demand coming once or twice a year. In this type of PO you are committing a purchase of item/s with particular quantity and particular price at particular shipment schedule.For example

  • Purchasing for any specific event happening in Company
  • Where purchasing item/s is one time job.
  • Planned Purchase Order

(PPO): This type of PO is used when you are not sure about the exact delivery schedules but other details are quite clear (like Item, Quantity, Price, approximate Delivery Schedule and Payment Term). For PPO Need-By-Date has to be entered, but this date will be treated as tentative date only. Once you are sure about the delivery schedule you create releases against this PPO with detailed delivery schedule. In this type of PO you are committing a purchase of item/s with particular quantity and particular price but with tentative shipment schedule. When you make a release, you are committing the delivery also.

For example

  • You need 1200 notebooks yearly, so you can raise PPO with quantity 1200 and in shipment details you can have shipment schedule as per your need (Say 12 shipments with 100 quantity each). This will be tentative schedule, you need to generate a release as and when you need the good and supplier will provide you material.
  • Blanket Purchase Order

: This kind of PO is used when are not sure about quantity, price, delivery schedule. As soon as you select PO type as Blanket Purchase Agreement the fields for quantity  gets disabled. Blanket PO can be based on max agreed amount. Exact quantity Delivery Schedule and price will be informed to supplier by creating Blanket releases against blanket PO. You can have different ‘Price Breaks’ and specify the quantity / discount / effectively details. In this type of PO you are not committing your supplier at the time of creating PO, all the commitments are done when release is sent.

For Example:

  • A car manufacturer needs dashboard for each vehicle and it is purchased from selective suppliers only. But demand for dashboard is not clear. In this case Blanket PO is used and whenever demand comes, releases are sent to supplier.
  • Contract Purchase Order

: This type of PO is used when you are not sure even about the item which need to be purchased J. The only information that you provide in a Contract PO is supplier, supplier site, payment terms and agreement control details (header part only). Standard PO are created by referring the Contract PO when some thing is to be purchased against the Contract PO from that supplier.

For example:

    • You need to import many items to run your business, but you don’t have Import/Export license. In this case you create Contract PO with supplier who has Import/Export license and whenever you need something to be imported, you generate standard PO referring the Contract PO for that Item/s.

Out of above 4 types you can add only Blanket and Contract POs in Approved Supplier List (ASL).

This is applicable for Oracle EBS only…

PO Type-Usage