Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Understanding data flow for “Internal Orders”

Posted on August 6th, 2007 by Sanjit Anand ||Email This Post Email This Post

The process flow for Internal OrdersInternalOrderFlow

Internal orders normally start with creation of requisitions in Oracle Purchasing (which is created as Internal Sales Orders) .

Once the requisitions created from the MRP and Inventory modules, the Requisition Import program must be run in order to move records from the requisition interface tables to the historical PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL.

Now next is to create internal requisitions from Purchasing module.When each requisition is approved, data will be inserted into the MTL_SUPPLY table. When the items on the requisition are stocked in inventory, the supply data can be viewed from the inventory form called Item Supply/Demand.

Once the Internal Requisition is approved, a user should run Create Internal Sales Orders (from a Purchasing responsibility) to load the internal sales orders interface tables called in Order Entry.

After the Creation of Internal Sales Orders has completed, a Import Orders concurrent program need to run from an Order Management.

at this stage, Oracle order Workflow will process the internal sales order using the workflow definition listed for the Transaction Type definition in Order Management. When the workflow completes the Shipping Network is checked to see if the Transfer Type between the source and destination organization is In transit or Direct.

You can use Oracle Order Entry/Shipping to define order cycles, approvals and holds.

Internal Requisitions use the Account Generator, which automatically builds account distributions. You can specify only one accounting distribution per inventory sourced line.

This internal requisition process provides the features needed to define your inter- organization shipping network. For transfers between two organizations, you can specify whether to use in transit or direct shipments. You can also require internal requisitions for transfers between specific organization.

The Data Flow for Internal orders


Posted in Oracle Order Management | 6 Comments »

Understanding data flow for “Standard Order”

Posted on August 5th, 2007 by Sanjit Anand ||Email This Post Email This Post

Last week, got a mail from one of reader who requested for providing data flow of standard orders and Return Orders. So,here are the information for data flow. Normally standard sales order can be split into nine sub steps , which jointly carried out by some module like INV,OM, Pricing, Shipping and AR. Lets take each sub steps with data flow conditions. These are based out of the flow which is available in

1. Order Entry

This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.

  • oe_order_headers_all (Here the flow_status_code as entered)
  • oe_order_lines_all (flow_status_code as entered) ( order number is generated)

2.Order Booking

This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.

  • oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
  • oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated)
  • wsh_new_deliveries (status_code OP open)
  • wsh_delivery_details (released_status ‘R’ ready to release)

Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. 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_reservations table get updated.

4. Pick Release

Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.

Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

  • oe_order_lines_all (flow_status_code ‘PICKED’ )
  • wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
  • mtl_txn_request_headers
  • mtl_txn_request_lines
    (move order tables.Here request is generated to move item from saleble to staging sub inventory)
  • Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

5.Pick Confirm

Items are transferred from saleble to staging Subinventory.

  • mtl_material_transactions
  • mtl_transaction_accounts
  • wsh_delivery_details (released_status ‘Y’‘Released’ )
  • wsh_delivery_assignments

6.Ship Confirm

Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries

  • oe_order_lines_all (flow_status_code ‘shipped’)
  • wsh_delivery_details (released_status ‘C’ ‘Shipped’)
  • 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

7.Enter Invoice

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.
  • ra_interface_lines_all (interface table into which the data is transferred from order management)T
  • Then Autoinvoice program imports data from this
  • Table which get affected into this stage are recievables base table.
    • 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 header_id (or order number) and line_id of the orders)

8.Complete Line

In this stage order line leval 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”)

These are the typically data flow of a order to cash model for a standard order.

Posted in Oracle Order Management | 24 Comments »

AOL : Valueset – Advanced Validation Options

Posted on August 5th, 2007 by Sanjit Anand ||Email This Post Email This Post

In oracle application there are several advanced techniques by which we can use when defining value sets for validating input. Normally such techniques use values obtained from a location other than a list specifically defined for the value set. These locations may includes:

  • Values retrieved from an application table
  • The current value for a particular profile option
  • A value used earlier in a field on the form
  • A value used earlier in another value set

Here are some information with advance validation options.

• Using $PROFILES$

This is used to reference the current value of a profile option in a WHERE clause by prefixing the name of the profile option with $PROFILES$.



A typical example with the use this keyword in a WHERE clause to reference a profile option value.


so what happen when ever the SET_OF_BOOKS_ID need to pass the $PROFILES$ options simply reference the value which is retrived at form level.

A list of available Profile options can be found in one of the last post.

• Using :Block.field

This is used to references the value of an earlier appearing field on the same form

Using :block.field is different from using a descriptive flex field reference field in that the flex field structure does not change based on the
different :block.field values.

By Using this value set only with flex fields on windows that have the same block.field available.

• Using $FLEX$

This is used to references the value from a value set used earlier on the same form

You can refer to the current value of a previously used value set on the same form by using $FLEX$.value_set_name.



Posted in AOL | No Comments »

AOL : Valueset – A Beginner Guide

Posted on August 4th, 2007 by Sanjit Anand ||Email This Post Email This Post

What is value set in Oracle application AOL

  • Value set is primarily a ”container” for your values, you define your value set such that it can control the types of values that are allowed into the value set (either predefined or non–validated). You can specify the format of your values.
  • Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Standard Request Submission (value sets for report parameters for your reports that use the Standard Request Submission feature).

When to defining Values for Value Sets

  • After you register your Flexfields & report parameters, if you are using independent or dependent value sets, you can enter values into each corresponding value set using the Segment Values form.
  • Values for the Value Sets, we are defining will be kept in the Oracle Application Object Library’s tables.

How many Format Types the value set have

  • Character
  • Number
  • Time
  • Standard Date, Standard Date Time
  • Date, Date Time

You should take a note that Date and Date Time value set formats will be obsolete now and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What is Security type in value set?

  • By Security Rules window, we can define value security rules for ranges of flexfield and report parameter values.

There are two levels where you must activate Security, the one at value set level and other at individual segment or parameter level. You make Flex field Value Security available for your value set by choosing Hierarchical Security or Non-Hierarchical Security for the Security Type. When you make security available for a value set, all segments and report parameters that use that value set can use security. You then enable security for a particular segment or parameter.

  • Choose Hierarchical Security, If you want Security on a parent value to “Cascade down” to its child value or else you can choose Non-Hierarchical Security.

How many Character Formatting Options have for value set?

  • Numbers Only (0–9)
    • We cannot prevent users from entering a value that contains the radix character.
    • Cannot be used in Translatable Independent and Translatable Dependent value sets.
  • Uppercase Only
    • Here also we cannot use in Translatable Independent and Translatable Dependent value sets.
  • Right–justify and Zero–fill Numbers
    • If you have selected Numbers Only (0–9) flag, then it wont allow you to affect this flag.
    • We are recommended to use this in Accounting Flex fields.
  • Minimum and Maximum Value Range
    • Your Minimum/maximum value may not be longer than the maximum size you specify for this value set.
    • Once you specify a range of values, you cannot define a new valid value that falls outside this range.
    • The Minimum Value and Maximum Value fields can therefore allow you to create a value set with a validation type of None.

How many validation Type does value set have?

There are several validation types that affect the way users enter and use segment or parameter values:

  • None (not validated at all).
    • Allow users to enter any value.
    • Only Format Validations will be done.
  • Independent.
    • Provides a predefined list of values.
    • Independent values are stored in an Oracle Application Object Library table.
      value set1
  • Dependent.
    • Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
    • Must define your independent value set before you define the dependent value set that depends on it.
    • Advisable to create your independent values first.
    • Must create at least one dependent value for each independent value, or else it wont allow you to enter into that segment or field.
  • Table.
    • It use your own application tables as value sets for flex field segments and report parameters instead of the special values tables which Oracle Applications provides.
    • You can also use validation tables with other special arguments to make your segments depend on profile options or field values.
    • You can use any existing application table, view, or synonym as a validation table.
    • If we are using non–registered table for our value set, then we have to Create the necessary grants and synonyms to APPS Schema.
    • The value column and the defined ID column in the table must return a unique row for a given value or ID.
    • If the Hidden Id column is provided the value passed to the report will be Hidden and not the Value column.
    • Similarly, when you specify :$FLEX$.Value_Set_Name, your flex field segment or report parameter defaults to always use the hidden ID column to compare with your WHERE clause .
    • We can use Special BIND variable such as :$PROFILES$.Option_name, :$FLEX$.Value_set_name, :block.field in the WHERE clause.
    • We can also display additional columns.
      Ex: Column_name1 “Column Title” (Width), ……
      org_id “Organization” (30)

  • Special.
    • Special validation value sets allow you to call key flex field user exits to validate a flex field segment or report parameter using a flex field–within–a–flex field mechanism. You can call flex field routines and use a complete flex field as the value passed by this value set.
  • Pair.
    • Pair validation value set allows user to pass a range of concatenated Flex field segments as valueset1parameters to a report.
  • Translatable Independent & Translatable Dependent
    • These value sets are similar to Independent and Dependent value sets except that translated values can be displayed to the user. Translatable Independent and Translatable Dependent value sets allow you to use hidden values and displayed (translated) values in your value sets. In this way your users can see a value in their preferred languages, yet the values will be validated against a hidden value that is not translated.
    • We can convert the Independent value set to a Translatable Independent value set, or a Dependent value set to a Translatable Dependent value set. These are the only types of conversions allowed.

Which Oracle table store Value sets and underline information


Any method to upload flexfield value?

Yes, FNDLOAD is utility which can be used for moving value set across different environment.See the FNDload post.

Do we have any restriction on value set?
Yes, here are some listed one:

  • Table Validated Value Sets
    • We cannot use table-validated id value sets for any accounting flexfield or any other key flexfields.
    • We cannot use :$FLEX$, :$PROFILES$ in table name, value and id of table validated value sets.
    • We cannot use DISTINCT clause in any of the column fields or in the WHERE clause of a table validate value set.
    • In an id value set, the value can be non-unique but id should be unique. In a non-id value set, value should be unique.
    • We can only use columns selected for the table-validated value set must be of type NUMBER, DATE or VARCHAR2.
    • Support for SQL expression in columns of Table Validated value sets will be obsolete in future release.
  • Translatable Independent and Translatable Dependent Valuesets
    • The Numbers Only and Uppercase Only option cannot be used.
    • Must have “Char” format type.
  • Special/Pair valuesets
    • Special/Pair value sets are user-exit value sets . PL/SQL APIs will not be able to validate them.

Posted in AOL | 7 Comments »

AOL : Valueset – Validation Comparison

Posted on August 3rd, 2007 by Sanjit Anand ||Email This Post Email This Post

Here is the comparison of the various validation types.


The various AOL tables name for value set can be referred in one of the last post.

Posted in AOL | No Comments »

Information – Help Menu

Posted on August 2nd, 2007 by Sanjit Anand ||Email This Post Email This Post

For those who are very new to oracle application , you should know the help menu has some great functionality for diagnostics purpose.

  • How to see application version, Form shortname and version of form
    (Help) About Oracle application
  • (Help)Diagnostics ->Examine
    • Pass the apps password and investigate the value for some variable.
  • (Help)Diagnostics ->Examine ….Chose $DESCRIPTIVE_FLEXFIELD$block
  • (Help)Diagnostics ->Examine ….Chose $ENVIRONMENT$block
    • This has all primary environment variables.
  • (Help)Diagnostics ->Examine ….Chose $PROFILE$block
    • this shows all profile options given to user who is accessing the application.
  • (Help)Diagnostics ->Examine ….Chose system block…choose LAST_QUERY field
    • This will shows last query used by the form.
  • (Help) Diagnostics -> Display Database error
    • This shows any database error message.
  • (Help) Diagnostics -> Trace(checkbox)
    • The options are on and off for forms tracing. Tracing file (.trc) is put into user dump folder.

Posted in 11i, Beginner | 1 Comment »

Conversion & AIM’s Deliverables

Posted on August 2nd, 2007 by Sanjit Anand ||Email This Post Email This Post

Do we have any AIM’s deliverables during conversion?

Yes..because data need to moved from old system to newer system thus, it is required to understand how important is during implementation time line. Application implementation methodology (AIM’s) does has categorize conversion and migration as a separate sub process process and it consist of various deliverables

Here are the list with there document number:conversionaims

  • CV 010 – Conversion Scope, Objectives, and Approach
  • CV020 – Conversion Strategy
  • CV030 – Conversion Standards
  • CV040 – Conversion Environment
  • CV050 – Conversion Data Mapping
  • CV055 – Conversion Detailed Data Mapping
  • CV060 – Manual Conversion Strategy
  • CV065 – Design Conversions and Interfaces
  • CV070 – Conversion Program design
  • CV080 – Conversion Test Plans
  • CV090 – Conversion Programs
  • CV095 – Custom Software Programs
  • CV100 – Conversion Unit Test Results
  • CV110 – Conversion Business Objects Test Results
  • CV120 – Conversion Validation Test Results
  • CV130 – Installed Conversion Software
  • CV140 – Converted and Verified Data

A close look on AIMs Tasks and Deliverables in Conversion Process:

The major tasks and corresponding deliverables during conversions are summerzied below. Please take a note , this is for your information purpose based out of AIM’s v3.1.


Posted in Conversion | 1 Comment »

Conversion Methodology

Posted on August 1st, 2007 by Sanjit Anand ||Email This Post Email This Post

In one of previous post , i have discussed the basics of interface ,conversion and migration.Lets take a more detailed life cycle for data conversion/migration activity. This activity can not be ignored in any Oracle ERP transformation project.

A conversion does follow there own methodology , being a typically methodology it consist of certain task and subtask identified at sub activity level . Here are some of them as discussed below.

1. Movement of Data or Transport of data

This is where you have to plan the movement of data from an external system/old system to Oracle Applications which normally consider within a conversion project plan. The more important is developing a detailed conversion plan for each entity, listing all design, development, testing, and conversion tasks. You have also include resource,software, and hardware requirements to successfully convert each entity.

2.Design Processconversion

This is where you have to decide what need to convert. This start with identifying all objects first there corresponding volume.

  • Examine the business objectives and requirements to determine the data to be converted.
  • Specify time constraints for the conversion, especially for transaction data.
  • Determine the appropriate conversion method, it is not recommended to go for manual entry.If data volume is low , try to find alternate product.
  • Then need is performing data mapping.
  • Then its required to install all hardware and software required for the conversion process.
  • Determine the testing requirements. Identify testing method if available in Oracle else design a custom query to compare the result.

3.Developing Programs

This process does consist of :

  • Writing extract and import programs.
  • Scripts to create any interface or translation tables in Oracle RDBMS.
  • Writing validation, translation, and migration programs.
  • Write verification scripts and reports.

4. Performing Conversion

This is process in which major activity is performed, this consist of:

  • Extract and format data.
  • Create temporary interface tables.
  • Upload data to interface tables.
  • Run translation programs & validation programs.
  • Migrate data into production tables.
  • Run verification scripts.
  • Run application reports to verify converted data.

5. Data Verification

In this phase each converted entity, design a conversion process from data extraction through data verification. Main consideration is business objectives and dependencies for each point in the process.

Posted in Conversion | No Comments »

A bit on GL Open Interfaces

Posted on August 1st, 2007 by Sanjit Anand ||Email This Post Email This Post

Do you know, except for Oracle Assets, most of the subledgers integrated with GL through the GL_INTERFACE table. Then the next question why not Oracle asset ?? will discuss in granular details some other day. For now , you should note that oracle Assets transfers information directly into the GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES tables. Hence importance of GL open Interface cann’t be ignored. I have already discussed the API availability in GL where potential usage is already been discussed.

With that extension, here is cheat sheet for open Interfaces.

With Open Interface ..

  • You can integrate with external systems by using published open interfaces
  • You can write import programs to import data from an external system or from your previous accounting system
  • You can initiate a full account validation process during import. The validation process checks for several items including :
    • Account combinations
    • Periods
    • Unbalanced journal entries
    • Foreign currency errors
    • Budget information
    • Encumbrance information
    • Other miscellaneous items
    • Sharing Information with Sub ledgers

Posted in Oracle General Ledger | 1 Comment »

Next Entries »