Compare Technical Terms in Oracle Apps with Real Time Example

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say ram is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers. The farmers are referred to as VENDORS / SUPPLIERS in Oracle Applications.



ram keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.


ram gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 KGS of grapes. In Oracle Apps, bags and KGS are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER.



ram maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.



Say the fruits have been shipped to the customer Fruit Mart. ram now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments.



ram’s customer, Fruit Mart pays him in installments (partial payments). So ram has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when ram receives the payments. This detail is called RECEIPT, which will be compared to the invoice by ram to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.



ram’s fruit business has begun to improve and has attracted more and more customers. As a result, ram decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY.



Due to increase in customers, ram needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, ram pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.



At the end of every month, ram prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.





As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, Oracle has very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.






Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has its own functionality trying to serve a business purpose. Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, and Fixed Assets etc.......

How to Migrate All the Scripts(Table Scripts,Packages,Host Script,Install Script,LDT Files etc) from one instance to the other instance by running the Install script

I am assuming that i have already created all the scripts(table scripts,packages,host script,install script,ldt files etc).
These all above scripts need to move from one instance to the other instance

step 1:
Open winscp,and login with the credentials to what ever the instance you finally want to move.

step 2:
Create any folder or ask the client for the folder where  i should place the scripts initially

step 3:
If dont have folder,create it and place all the scripts under the folder you created

step 4:
After moving all the scripts,connect to the putty,and navigate to the folder where you have placed the scripts

step 5:
Once you navigate to the folder,convert the shell scripts (i.e. sh files)using dos2unix command i.e.
dos2unix XXRAM_INSTALL_SCRIPT

step 6:
Once the files got converted,run the below query i.e we are going to run the install script,in which it contains all the uploaded commands
with below command it will upload into the istance
syntax:
./<install script name> <user name>/<password> <host name> <sid> <port>

Below are the back end details of the new instance to which we are uploading
install script name:this contains all the uploaded scripts
user name:which ever the instance we are loading ,that instance user name
password:which ever the instance we are loading ,that instance password
host name:which ever the instance we are loading ,that instance host name
Sid:which ever the instance we are loading ,that instance service name
port:which ever the instance we are loading ,that instance port number

Example Syntax:
./XXRAM_INSTALL_SCRIPT apps/appsram abc.def.ghi dev 1111

step 7:
Now go and query it in the instance for verification purpose whether we have successfully uploaded or not!!!!!!!

How to execute the Workflow notification Package?and where to check the notification?what is the table effected?

Anonymous Block:

DECLARE
BEGIN
xxram__test_workflow_pkg.send_notification('ram_invnf-1000000');
END;

table effected:
select * from apps.wf_notifications order by begin_date desc

ram_invnf-->lookup_code created and that is used in package
1000000 -->this is request id that should be passed .

What is BULK COLLECT?Create sample PLSQL code Using BULK COLLECT?

BULK COLLECT:

Bulk Collect is used to fetch and load large volumes of data. This makes program to run faster but consume more memory.

Below is an example to load all entries from all_objects view into temp table.

SAMPLE PROCEDURE USING BULK COLLECT:

CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
IS
TYPE sOwner IS TABLE OF VARCHAR2(30);
TYPE sName IS TABLE OF VARCHAR2(30);
TYPE sType IS TABLE OF VARCHAR2(19);
l_sOwner sOwner;
l_sName sName;
l_sType sType;
BEGIN
dbms_output.put_line(‘Before Bulk Collect: ‘ systimestamp);
SELECT owner, object_name, object_type BULK COLLECT INTO l_sOwner, l_sName, l_sType FROM all_objects;
dbms_output.put_line(‘After Bulk Collect: ‘ systimestamp);
----
FORALL indx IN l_sName.FIRST..l_sName.LAST
INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx));
EXIT WHEN l_sName.count = 0;
----
dbms_output.put_line(‘After FORALL: ‘ systimestamp);
COMMIT;

END;

WHEN TO USE:

Below are few tips to keep in mind while using Bulk Collect in a Program

1) It is always safe (that is, you will never raise a NO_DATA_FOUND exception) to iterate through a collection from 1 to collection.COUNT when it has been filled with BULK COLLECT

2) Always check the contents of the collection (with the COUNT method) inside Loop to see if there are more rows to process


3) Never use EXIT WHEN Collection%NOTFOUND, instead you can use EXIT WHEN Cursor%NOTFOUND. Ignore the values returned by the cursor attribute- %NOTFOUND

Note:

If the volume of data is very large, use Bulk collect with limit clause to split the collection into chunks of data 

and thereby consume less memory. Below is an example to bulk collect the data in a batch of 10000 records to avoid memory issues.

DECLARE

 TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

l_tab t_bulk_collect_test_tab;

CURSOR c_data IS

 SELECT * FROM bulk_collect_test;

BEGIN

 OPEN c_data;

 LOOP

 FETCH c_data

 BULK COLLECT INTO l_tab LIMIT 10000;

-- Process contents of collection here.

dbms_output.put_line(l_tab.count || ‘ rows’);

EXIT WHEN c_data%NOTFOUND;

 END LOOP

 CLOSE c_data;

END;

query to display all the tables that contains '_' (underscore) symbol?

select *from all_objects where object_name like '%*_%' escape '*';

How to create DBLink to Access 11i Data in Any R12 in oracle apps

step1:
get the 11i instance TNS names includes user name,password,instance name etc details.

step2:
in TNS file ,there will be HOST,PORT details.

step3:
below is the syntax.


create database link <DB Link Name> connect to <11i user name> identified by 
<<11i password>> 
 using
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =  <host name> )(PORT = <port number>))) 
 (CONNECT_DATA = (SERVICE_NAME = <service name>)))'



example :run this below script,db will be created with name "11itoR12"

create database link  11itoR12  connect to xxxx identified by xxxx123
 using
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hqtsoadb9lx.xxx.com)(PORT = 1111)))
 (CONNECT_DATA = (SERVICE_NAME = 11iinstancename)))'

Note:
Now inorder to access 11i tables in Any R12,the query should be like below
i.e
select *from mtl_system_items_b@11itoR12;

Queries to check the instance details in oracle DB

1)select name from v$database;
Support instance