Multi-Org or multiple organization access (MOAC) in R12


Multi-Org or multiple organization access (MOAC) in R12

What is MOAC?

Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

Why it has been created?

Prior to R12, end users use to toggle / switch / change responsibilities in order to do transactions (like invoice / payment processing in AP) in different operating units. This is a very time consuming and inefficient way of recording transactions when you have 100s of operating units specially Internet based organizations who have worldwide operations in almost all the countries.

To address this, a new feature in R12 has been introduced in which user can switch between operating units within a responsibility something similar to “Change Organization” feature in inventory. Prior to R12, user would have to switch responsibilities in order to enter transactions in respective operating units (tagged to the responsibility).

What are its advantages?

  • Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
  • This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
  • Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.

  • MO: Security Profile– Always evaluated first.
  • MO: Operating Unit– Secondary priority being evaluated after ‘MO: Security Profile’
  • MO: Default Operating Unit– Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?

In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.

In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.

One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

Advertisements

Order to Cash (O2C) Cycle


Order to Cash (O2C) Cycle

Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.

Step 1] Order Entry:

Customer sends details of order or sales dept brings order from customers. After that the order is entered in Order Management (OM)

Navigation: Order Management Super User> Orders Returns >Sales Orders

Here we need to enter the Customer Details (Customer Name , Number, Contact Ship to and Bill to address etc.), Order type. In the Lines tab we need to enter the Item to be ordered and the quantity required. Here we can also check the availability of the order. Here we can save the order. Once saved the Order Status is changed to ‘Entered’.

Key Tables:

  • OE_ORDER_HEADERS_ALL – All header information is stored here.
  • OE_ORDER_LINES_ALL – All the line information is stored here.

Step 2] Order Book :

When we book the order, we are just confirming and freezing our order.

The final step in the Sales Order Entry process is to Book the order. This signifies that the Order Entry process is complete and that the order is eligible for the next stage in the line flow for this order, as defined by its Transaction Type. Select the Book Order button. The Entry Status of the Order will change to Booked.

After Order Booking:

Order Header: Booked

Order Line : Awaiting Shipping

Shipping Transaction form: Ready to release

Table Level :

  • OE_ORDER_HEADERS_ALL : Flow_Status_code –Booked
  • OE_ORDER_LINES_ALL : Flow_Status_code – Awaiting Shipping
  • WSH_DELIVERY_DETAILS : Released_Status – R ( means – Ready to release)

Step 3] Launch Pick Release :

Pick Release is the process in which the items on the sales order are taken out from inventory.

Navigation: Order Management Super User> Shipping > Release sales Orders > Release sales order

Based On rule: Select the Grouping rule the reaming details will default in Order, Shipping and Inventory tab

Order Tab:

  • Order Number: Select the Order Number. Values for the Order Type and Customer fields of this form default to those for the order number you enter here.
  • Ship Set: Select the Ship Set to be released. The Order Number must be selected first.

Shipping Tab:

  • Auto creates Deliveries : Select Yes in this box to automatically create deliveries for  delivery lines once they are released
  • Release Sequence Rule: Select Rule to specify the order in which the picking lines are released.
  • Auto Pick Confirm –Yes/No

Inventory tab:

  • Warehouse: Select the Warehouse
  • Sub inventory: Select the Sub inventory
  • Pick Slip Grouping Rule: To determine how released picking lines are grouped onto pick slips.
  • Default Stage Sub inventory: Select the Default Stage Sub inventory

Click on Execute Now Button to complete the pick release of the order. 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
  • Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

Step 4] Pick Confirm the Order:

If Auto Pick Confirm in the above step is set to NO, then the following should be done.                             

Navigation: Inventory Super User > Move Order> Transact Move Order

In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.

  • Items are transferred from salable to staging Sub inventory.
  • mtl_material_transactions
  • mtl_transaction_accounts
  • wsh_delivery_details (released_status ‘Y’ ‘Released’ )
  • wsh_delivery_assignments

Step 5] Ship Confirm the Order:

The Shipping Transaction window provides a centralized workbench that consolidates three major shipping functions: planning, pick releasing, and ship confirming.

Navigation: Order Management Super User>Shipping >Transactions.

Here ship confirm interface program runs in background . Data are 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 are deleted from mtl_demand, mtl_reservations and Item is deducted from mtl_onhand_quantities.

Step 6] Enter Invoices in Receivables:

Run workflow background Process. Workflow Background Process inserts the records in RA_INTERFACE_LINES_ALL with

  • INTERFACE_LINE_CONTEXT     =  ’ORDER ENTRY’
  • INTERFACE_LINE_ATTRIBUTE1 =   Order_number
  • INTERFACE_LINE_ATTRIBUTE3 =    Delivery_id

Then it spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.

Navigation: Order Management >view >Requests

Underlying tables:

  • RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
  • RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

Step 7] 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”)

Step 8] CLOSE ORDER:

This is last step of Order Processing . In this stage only oe_order_lines_all table get updated .

  • oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)

What’s New in R12 Financials?


What’s New in R12 Financials?

1] Ledgers and Ledger Sets:

The ledger is a new fundamental concept in Release 12.  The ledger replaces the 11i concept of a set of books.  It represents an accounting representation for one or more legal entities or for a business need such as consolidation or management reporting. 

11i & Prior = Sets of Books (3 C’s)

  • Chart of Accounts
  • Accounting Calendar
  • Currency

R12 = Ledgers (4 C’s)

  • Chart of accounts
  • Ledger currency
  • Accounting calendar
  • Accounting method – new 4th

While a set of books is defined by 3 C’s, chart of accounts, functional currency, and accounting calendar, the ledger is defined by a 4th C: the accounting method.  This 4th C allows you to assign and manage a specific accounting method for each ledger.  Therefore, when a legal entity is subject to multiple reporting requirements, separate ledgers can be used to record the accounting information.

Primary Ledger:

  • The main “Activity” Ledger
  • Usually in the local currency
  • For Operational reporting

Secondary Ledger:

  • Differs from Primary Ledger by Chart of Account, Calendar, and/or Accounting Method
  • For Statutory, Tax or Consolidated reporting

Reporting Currency Ledger:

  • Differs from Primary Ledger by Currency ONLY
  • Just a translation of the Primary Ledger – no rules required
  • For Consolidated reporting

LEDGER SETS:

  • Grouping of ledgers with the same chart of accounts and calendar/period type combination
  • Essentially treats multiple ledgers as one

2] Subledger Accounting:

You can consider SLA as a bridge or an Intermediate platform that talks to Subledger products (these are other applications or modules) and the General ledger. All Accounting entries for your modules (like AP, AR, Projects, Inventory, etc) are treated as Sub-Ledgers and they first sent to the SLA engine. The SLA applies its rules (some or these rules are pre-configured and also you can configure as many rules as you want) and then sends the necessary journal entries to the General ledger.

In a nutshell, the following services are provided by Oracle SLA

  • Rule based Generation and  storing of accounting entries
  • Storing subledger balances
  • Subledger or SLA accounting entries
  • Subledger reporting (some examples could be Open Account Balances Listing and Subledger Journal Reports, etc )

3] Multi-Org Access Control (MOAC):

‘Multi-Org Access Control’ popularly known as ‘MOAC’ in short form is an enhanced feature in Release 12. MOAC will enable users to access secured data in one or more Operating Units from a single responsibility.

End-Users can access/transact data within several operating units based on Security Profile attached to a responsibility. i.e. End-Users can access/transact data on multiple Operating units by accessing one operating unit at a time without changing a responsibility. This Provides flexibility for end-users to work conveniently with multiple Operating Units in shared service Environments with single responsibility.

4] Advanced Global Intercompany System (AGIS):

Advanced Global Intercompany System (AGIS) enables you to create, settle and reconcile intercompany transactions. Intercompany transactions are transactions that occur between two related legal entities in an enterprise or between groups in the same legal entity. The balances of the intercompany transactions must be eliminated or adjusted when preparing the consolidated financial statement, or it might result in overstated financial results, which in turn might lead to legal repercussions against the enterprise. Intercompany transactions can be identified and eliminated by the use of specific accounts to book these transactions.

5] Tax Engine:

It Centrally manage tax transactions across entire E-Business Suite.

  • Single Repository of transactions for global business insight
  • Centralized rules applied to transactions to manage globally and reduce risk
  • Automation of tax processes on transactions to improve operational efficiency
  • Improved Reporting
  • Effective Date Setup
  • Extensible architecture that supports additions, e.g. Self-assessed Use Tax

6] Bank Model:

Because of changing business need and high demand of global partners, the R12 release witness great changes ever into the bank model. Bank account is now associated with Legal entity rather than Operating Unit and hence single bank account serves multiple Operating Units. This makes bank with strong capability to pay across operating units. More over banks accounts can be shared by applications and can be designed for use by Payables, Receivables and Payroll.

The new bank account model allow you to define and keep track of all bank accounts in the e-Business Suite in one place and explicitly grant account access to multiple operating units/functions and users. The new model reduces the number of access points to manage bank accounts by providing a centralized user interface where all internal bank accounts can be set up.

Data Migration vs. Data conversion


Data Migration vs. Data conversion

When we need to enter data into oracle Apps, following are the few techniques:

• The Data can be entered using the application Screens (for small amount of data, like creating PO, entering sales orders using Oracle Apps screens).
• The data can be entered using Oracle’s Open System Interface (for regular operations e.g. for moving data from one module to another).
• The data can be stored in the database table directly (Not recommended by oracle and can be very risky, because on any event data is going to be stored in many tables and data should be validated before inserting into tables that may cause data integrity and inconsistency problem, sometimes it may corrupt the data completely.).
• Using third party tools like data loader (It is also can be used when data is relatively small (25-200 records) because it captures the keystrokes and works like manually entering the data into Oracle form but much faster as process is automated).

What is the need of Migration/Conversion?

Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk  of data (sometimes millions or even more than that) that needs to be moved from one system to another  and  before moving the data it should be validated and only valid records should be entered into Oracle Apps.

If both the systems (Target and source) are not having same structure for data (Tables are not same/Table Structure is not same/The data is being stored in database is not same), it needs to be translated (e.g. upgrading from Oracle 11i to R12 where table structures are not same) then we say it as conversion (any kind of translation of data on Source data to make it suitable for Target system) otherwise migration.

 

What is Migration?

 

Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.

Process of Migrating of data:
• Identify the data to be imported to new system (Business requirement).
• Extract the data into flat file/Staging table
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface)

What is Conversion?

Conversion of data means translating the data to suite target system (data should be formatted according to target system )  and then move the translated data using Interface Programs/APIs.
• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data

 

How conversion/Migration and interface differ?

 

There are good numbers of parameter on which they can be categorized. Take few of them:
Frequency
• Conversions/Migration are a one time event
• interfaces are ongoing
Occurrence in the project timeline
• conversions/Migration executed before production
• interfaces executed during production
Manner of execution
• Conversions/Migration are batch
• Interfaces may be batch or real time
Complexity
• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity.
• Coordinating with other systems make interfaces more complex
Maintenance
• Maintenance of interface is bit cost intensive task.

 

Custom.pll in Oracle Application


Custom.pll in Oracle Application

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a new business rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.

CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are:-

1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens
6. Enabling Special Menu

Where is this located?

Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this?

Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?

 Once you make changes you need to compile the pll. Use the F60gen to compile it

f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.

For following Events call will go to CUSTOM Library:

 
WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)

Custom Library contains Custom Package which is having two Functions and one procedure.

1] ZOOM_AVAILABLE:

This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

Sample code1:

function zoom_available return Boolean is

    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');

begin

    if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then
      return TRUE;
    else
      return FALSE;
    end if;

end zoom_available;

Sample code2:

function zoom_available return Boolean is

    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');

begin

   if (form_name = 'APXINWKB' and block_name = 'INV_SUM_FOLDER')
   	 then
      return TRUE;
    elsif (form_name = 'APXINWKB' and block_name = 'LINE_SUM_FOLDER')
   	 then
      return TRUE;
    else
      return FALSE;
    end if;

end zoom_available;


 

2] STYLE:

This function returns a integer value. This function allows to override the execution style of Product specific events, but it doesn’t effect generic events like when-new-form-instance. Possible return values are:
1. custom.before
2. custom.after
3. custom.override
4. custom.standard

By default it returns custom.standard.

Sample code:
function custom.style(event_name varchar2) return integer is
begin
     if event_name = ’MY_CUSTOM_EVENT’ then
     return custom.override;
     else
     return custom.standard;
     end if;
end style;


 

3] EVENT: 
This procedure allows you to execute your code at specific events including:

  —    ZOOM

  —    WHEN-NEW-FORM-INSTANCE

  —    WHEN-NEW-BLOCK-INSTANCE

  —    WHEN-NEW-RECORD-INSTANCE

  —    WHEN-NEW-ITEM-INSTANCE

  —    WHEN-VALIDATE-RECORD

By default this routine must perform ‘null;’

Sample code:
procedure event(event_name varchar2) is

form_name varchar2(30) := name_in(’system.current_form’);
block_name varchar2(30) := name_in(’system.cursor_block’);

begin

     if (form_name = 'XXBI' and block_name = 'xxcc') Then
        if(event_name = 'WHEN-NEW-FORM-INSTNACE')
           --Write your code here
     elsif(event_name = 'WHEN-VALIDATE-RECORD')THEN
           --Write your code here
     else
          null
       end if;
     end if;
end;


 

How to make the changes get affected?


Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.

Forms Personalization: an alternative of custom.pll

In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.

Advantages of Forms Personalization over Custom.PLL:

  • Forms personalization can be used by an user with limited PL/SQL knowledge. 
  • Changes take place immediately on reopening the form.
  • Anything which can be done using Custom.PLL can be done using Forms Personalization also.
  • Personalizations are stored in base tables related to Form Personalization.
  • CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
  • Easy to disable/enable with click of a button.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

MULTI ORG in Oracle Application


MULTI ORG in Oracle Application

The ability to define multiple organizations and the relationships among them within a single installation of Oracle Applications is called multi organization or Multi-org. Multi Org is the future used to store the data of multiple organizations in a single Database instance.

Basic Business Needs:

  • Use a single installation of any Oracle Applications product to support any number of organizations, even if those organizations use different sets of books.
  • Define different organization models.
  • Support any number of legal entities within a single installation of Oracle Applications.
  • Secure access to data so that users can access only the information that is relevant to them.
  • Sell products from a legal entity that uses one set of books and ship them from another legal entity using a different set of books, and automatically record the appropriate intercompany sales by posting intercompany accounts payable and accounts receivable invoices.
  • Purchase products through one legal entity and receive them in another legal entity.

Basically the different entities in multi-org are:

  • Business Group (BG)
  • Sets of Books (SOB)
  • Legal entities (LE)
  • Operating units (OU)
  • Inventory organizations (IO)

Organization Structure Example:

Business Group (BG):

The business group represents the highest level in the organization structure, such as the consolidated enterprise, a major division, or an Operation Company. A BG is used to secure human resources information like generation of employee numbers, generation of applicants, position flex fields, Job flexfields, Grade Flex field, Fiscal year, etc.

Set of Books (SOB):

A SOB is a collection of Currency, Calendar and Chart of Accounts (COA). Oracle General Ledger is used to secure Journal transactions (such as journal entries and balances) of a company by set of books. For each organization of the Business Group we need to define a set of Book. A company which operates in separate cities or separate line of businesses may separate their accounting transactions across units through separate Set of Books. A Business Group can have one or more set of Books.

Legal entities (LE):

A legal entity represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other legal entity information to these types of organizations. Separate Legal Entities may share same set of Books.

Operation Unit (OU):

An operating unit is a division or a Business unit of the legal entity. At this level we are going to maintain the information of sub‐ledgers. We are going to maintain the ledgers at Legal Entity level. Receivable, Payables, Assets, etc. are comes under Operation Unit level. Each user sees information only for their operating unit. Responsibilities are linked to a specific operating unit by the MO: Operating Unit profile option.

Inventory organizations (IO):

An inventory organization represents an organization for which you track inventory transactions and balances, and manufactures or distributes products. Examples include manufacturing plants, warehouses, distribution centers, and sales offices. The following products and functions secure information by inventory organization: Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and purchasing receiving functions. To run any of these products or functions, you must choose an organization that is classified as an inventory organization.

Oracle Application Directory Structure


Oracle Application Directory Structure

Oracle Application has a file system as shown in the below picture for the APPL_TOP Directory.

GL_TOP: (APPL_TOP/GL/11.5.0) is one of the Module Directory of Oracle Applications. It consists of a release directory (i.e. 11.5.0) under which Forms, Reports, BIN, LIB, SQL, etc.,

Forms/US: Forms directory to store all .FMX (Compiled) Form files of a specific module.

Reports/US: Reports directory to capture all the .RDF (Compiled) Report files of a specific module directory. US is a language specific directory.

BIN: Contains executable code of concurrent programs written in a programming language such as C, Pro*C, Fortran, SQL *LOADER or an operating system script.

LIB: Contains compiled object code (.OBJ files) of your concurrent programs.

SQL: Contains concurrent programs written in SQL*Plus and PL/SQL scripts.

HTML: Contains all .HTML, .HTM web files.

LOG: Contains all .LOG files of concurrent programs.

OUT: Contains output files from concurrent program.

Message: Holds your application message files for Message dictionary.

Oracle Application:Data Model


Oracle Application:Data Model

When we install Oracle Database by default system will creates SYS and SYSTEM schemas. These consist of all Data Dictionary Tables. Like this if we install Oracle Applications System will automatically creates schemas of all Modules (i.e. GL, AR, AP, etc.) with the respective module name as User and Password. Along with these schemas some special Schemas i.e. APPS, APPLSYS, APPLSYSPUB will be created for special purpose.

APPS Schema:

  • It is Public Schema.
  • The APPS schema is an ORACLE schema that has access to the complete Oracle Applications data model. It is analogous to the SYSTEM schema, which has access to the entire database.
  • AutoInstall creates the necessary grants and synonyms between the schemas.
  • Oracle Applications responsibilities connect to an APPS schema.
  • There is one APPS schema for every product installation group.
  • It consists of a collection of public synonym of all the objects of all the schemas in the Application database. All the Procedures, Functions and Packages created must be stored in this Schema.

APPLSYS Schema:

This is a special Schema consists of the files starts with FND, ALR, WF and AD.

APPLSYSPUB Schema:

This schema is a collection of public synonyms of all FND Tables, which are used for User verification. This is the Gate Way User ID of Oracle Applications.

Few Other Base Product Schemas:

  • GL ( General Ledger )
  • INV ( Inventory)
  • AP ( Accounts Payables)
  • APPLSYS ( Application Object Library)
  • ALR ( Alerts)

Oracle Applications R12 Architecture


Oracle Applications R12 Architecture

The Oracle E-Business Suite Release 12 Architecture is a framework for multi-tiered, distributed computing that supports Oracle Applications products.

Architecture-R11i vs R12

In EBS R12, various servers or services are distributed among the following three levels, or tiers.

  • The Desktop Tier
  • The Application Tier
  • The Database Tier

Oracle E-Business Suite Release 12 Architecture

1] The Desktop Tier

The client interface is provided through HTML for HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based applications.

In Oracle Applications Release 12, each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser. The E-Business Suite Home Page provides a single point of access to HTML-based applications, Forms-based applications, and Business Intelligence applications.

Oracle JInitiator will no longer be required to run Oracle Forms in E-Business Suite Release 12.  Oracle Forms in Release 12 will run directly in the native Sun Java2 Standard Edition plug-in.

The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customizations and extensions. The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.

2] The Application Tier

The application tier has a dual role: hosting the various servers and service groups that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier.

Four servers or service groups comprise the basic application tier for Oracle Applications:

  • Web services
  • Forms services
  • Concurrent Processing server
  • Admin server

3] The Database Tier

The database tier contains the Oracle database server, which stores all the data maintained by Oracle Applications. The database also stores the Oracle Applications online help information. More specifically, the database tier contains the Oracle data server files and Oracle Applications database executables that physically store the tables, indexes, and other database objects for your system. The database server does not communicate directly with the desktop clients, but rather with the servers on the application tier, which mediate the communications between the database server and the clients.