Why call it Apps and not Oracle ERP ?
|Ever wondered why is Oracle’s eBusiness Suite nicknamed apps?|
Is it just the short name of Oracle Applications? Possibly yes, however this question is an excuse for me to explain to you the evolution of APPS schema.
I started working in Oracle Financials 9 years ago. Those days each module had its own database schema(which we still have). However, a purchasing user (until version 10.6) used to connect to PO schema (by the virtue of the screen being a PO screen).
Hence, if a report or screen of AR ( Oracle Receivables ) module wanted to access a table named PO_HEADERS_ALL, they would then use notation PO.PO_HEADERS_ALL
However, now we have several database schemas(in most cases one schema per module).
The tables are still owned by their respective schema, but now we have a central schema named APPS. Oracle ERP simply connects to APPS database schema for all its operations(with a couple of exceptions that are best ignored for now).
Hence, if Oracle wants to create anew table named PO_HEADERS_ALL, they will do the following
Step 1. Connect to po/po@XX_DEVDB
Create table PO_HEADERS_ALL ( ...all columns here )
Step2. Grant all on po_headers_all to apps ;
Step 3. connect to apps/apps@XX_DEVDB
Create or replace synonym PO_HEADERS_ALL for PO.PO_HEADERS_ALL
By following the above steps, as you can see, APPS schema is able to access PO_HEADERS_ALL without the notation po.po_headers_all
In Oracle ERP, now we have 100s of schemas, example po, ar, ap, gl etc.
But the screens, reports, workflows etc in Oracle Applications connect to APPS schema only. Just like saying, ALL ROADS LEAD TO ROME. Here, all schema lead to APPS.
Hence if you have a pseudo report that joins ap_invoices_all table( in AP schema) to PO_HEADERS_ALL table( in AP schema), you will simply need to do the below once connected to APPS.
Selelct 'x’ from po_headers_all p, ap_invoices_all a where a.po_Id = p.po_Id
Note, prior to version 10.6(of Oracle ERP –not database version), one had to do
Selelct 'x’ from PO.po_headers_all p, AP.ap_invoices_all a where a.po_Id = p.po_Id
Note: To keep matters simple, I haven’t considered org_Id in this example.
Org_Id will be covered in one of the following chapters ( have a look at index).
Moral of the Story is:-
All the pl/sql packages will be created in APPS Schema
All the views will be created in APPS Schema
For each table in individual schema, there will exist one synonym in APPS schema
Tables are not created in APPS schema.
Every implementation has at least 1 custom schema, where custom tables are created.
For each custom table created by you, you will need to create a Synonym in APPS schema
As a developer, you will either connect to APPS Schema or to the custom schema where you will create new tables.
Custom tables are generally required in Oracle ERP because:-
1. You wish to create a custom screens ( your own screen to capture some info) for a functionality that is not delivered by Oracle
2. Pre-Interface tables ( Interface will certainly be discussed in one of the latter chapters)
3. Temp processing
4. Staging of data for third party extract interfaces….and much more