DATA LOADING IN ORACLE APPS 

Data loading or data migration is loading of data of one legacy system( previous system holding data ) to oracle base tables . this can be done by a systematic procedure . first data of legacy system is loaded into excel sheet then its loaded into staging table (custom table ) then some validations are applied in procedure and that procedure is run to transfer specific validated data from staging table to interface table . After storing data in interface table its further transferred in base table through standard API .

1. Excel sheet
2. Staging table ( alternatively called custom table )
3. Interface table ( use of interface table can be omitted )
4. Base table

What is API ?

APIs are stored procedures that enable you to insert and update data in Oracle Applications. For example, by using APIs, you can insert a customer record in Oracle Applications.

Sample usage of open interfaces for conversion purpose:

3-step process:

• Load: Get the data from the flat file into the staging table
• Validate: Assess the quality of the data by calculating and evaluating metrics.
• Transfer: Insert data into interface table / call APIs with values in staging table

Data loading and data migration are different concepts

Data migration may refer to the front end data transfer rather than table data transfer

Data migration can also be done by following methods.

1. Migration to Oracle – using open interface / API
2. Migration to Oracle – Oracle Utilities (FNDLOAD )
3. Migration to Oracle – Third party tools

The Generic Loader (FNDLOAD) is a concurrent program that can move Oracle Applications data between database and text file representations. The loader reads a configuration file to determine what data to access. For information on specific configuration files consult the Open Interfaces Guide for your product group. 

For exa if you want to copy a whole concurrent program from a test instance to production instance along with its all details , like value sets ,parameters etc. then by using FNDLOAD command you can do that . 

WHAT IS DIFFERENCE BETWEEN PROCURE TO PAY CYCLE & ORDER TO CASH CYCLE ?

Procure (to get) To Pay Cycle (buying of items to payment of them here company buy  items from vendors)

Procure to pay cycle is genrally applicable to all the client who give their projects to the companies (like techsignia ) here that client will go through the p2p cycle and all its different steps and in this steps , data generated will be stored in tables of different modules , we developer, just make reports using that data in AOL , every client must go through P2P cycle

Exa: for example  techsignia is taking project of bank HDFC in this case HDFC will have its own oracle e business suite license, now HDFC is our client and it will give its data to our team in the form of excel sheet which we will enter into tables by any method, this data entered  will be related to different modules and there tables and it will depend on the cycle like p2p cycle where in our exa bank will first act as client to buy its necessary goods like bank will want to buy computers in this case bank will have different vendors from whom they can buy computers in this case bank will first place the requisition ( demand) to different vendors , then HDFC will  prepare RFQ (request  for quotation ) and send it to different suppliers to get the best and /or economical price for the product . then supplier will reply by sending their quotation to bank ,all these data will be stored in oracle purchasing , and after comparing hdfc will select the best quotation , later hdfc will prepare a purchase order (PO)and shift the orders goods , and will receive the goods and all this data called goods received note (GRN )  will go in oracle purchasing , if HDFC want to receive goods at inventory then entries will make changes in inventory tables , once goods are moved to the inventory all accounting detail will be stored into table oracle general ledger, finally supplier will send invoice for the purchased goods and HDFC will match invoice against purchase order from oracle purchasing in oracle payables . , finally payment is made

The requirement arises from requirements for items,and company places order for items needed.
The vendor supplies the items .Once items are received payment will be made.That is a invoice will be raised and payment will be made to the vendor.The payment can be of cash,cheque etc.

This is governed under cash management and finally the transaction detalis will be accounted in General Ledger (GL).

In P2P cycle module included are

PO (purchase order )

AR(Account Receivable )

AP(account payable )

CM(cash management)

GL (general ledger )

Requisition
po_requisition_headers_all
po_requisition_lines_all ( segment1 ========== requisition number)
po_req_distributions_all
Note: requisition_header_id is the link between po_requisition_headers_all and po_requisition_lines_all
requisition_line_id is the link between po_requisition_lines_all and po_req_distributions_all

Purchase order
po_headers_all
po_lines_all
po_distributions_all
po_line_locations_all
Note: po_header_id is the link between all these tables

Receipt
rcv_shipment_headers
rcv_shipment_lines
rcv_transactions
quality result stored in qa_results table
Note: shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_lines
po_header_id is the link between rcv_shipment_headers and rcv_transaction

Invoices
ap_invoices_all
ap_invoice_distributions_all-
Note: invoice_id is the link between ap_invoices_all and ap_invoice_distributions_all

Payments
ap_checks_all
ap_invoice_payments_all
ap_payment_schedules_all

Payment Reconcillation
ap_banks
ap_bank_branches

GL Transfer
gl_periods
gl_period_status
gl_set_of_books
glfv_charts_of_accounts

DIFFERENCE BETWEEN ORACLE APPS 11I AND R12

              Oracle Application Architecture

oracle apps uses the internet computing architecture , which is a multitier architecture , create apps uses three tire architecture

there are three seperate tier

1. the desktop tier

2. the application tier

3. the database tier

-----1.THE DESKTOP TIER --

the client interface is provided through the java applet , using java enabled browser

the form client applet must run within the jvm on desktop,  for oracle applcation jvm is supplied by Jinitiator program which works in conjuctoin with browser ,

·    the form client applet is pakaged as java archieve (JAR) files contains all the java classes ,require to run the oracle aplcations forms

·    .jar files one loaded in the browser are stored in the browser cache and need not to be load each time untill update is not available or  untill we dont clear cache .

·    all the automatic downloading and installation is handled by the JInitiator

--------JInitiator --------

·    JInitiator is oracle's java virtual machine (JVM) tha tis implemented on the desktop client as plug in or Active x component (in I.E)

·    when user enter oracle apps url , browser will execute Oracle JInitiator , if its not install brwoser will download it

·    once installed oracle JInitiator will run the forms cleint and start an oralce apps sesson .

--2.THE APPLCATION TIER--

the application tier is location of servers that provide the business logic and code processing , this sometime reffered as middle tier, there are five servers present in application server

1. the web server

2. the forms server

3. the concurrent processing server

4. the reports server

5. the admin server

this application tier provides communication between desktop tier and the database tier . the application tier also controles and handles the load balancing among multiple forms .

1) web server

web server receives  request it receive fom the desktop clients, the web server consist of http listener, load balancer

-it accept incoming HTTP request (URL's) from desktop tier (browser)

- the desktop tier then connect directly to the form server once permited by web server

2)  form server

forms server handles the oracle application forms and the form runtime engine ,

form server displays screen to client side and allow making changes in database records based on user actions

forms server communicate with desktop teir in following ways

--TCP/IP connection

--a standard http connection

--a secure https connection

3)  concurrent processing server most interaction with oracle apps data is done through oracle application forms , bt there are also reporting program and updating program need to be run , to handle these program and to ensure that they do not interfere with interactive operations , they are run on seperate machine called concurrent processing server.

·    processes dat run on concurrent processing server are called concurrent programs, this can operate in the background

3) reports server

the reports server is used to produce reports for oracle business intelligence system

4) Administration server

administration server is machine from which you maintain the data in your oracle application database

---3)THE DATABASE TIER--

·     the database tier holds all the data stored and maintained by the oracle application system

·    in detail the dabase tier contains oracle server files and an oracel applications databse instnace that physically stores the tables , indexes ,

·    database tier does not directly communicate with the desktop tier  but rather it communicate desktop tier by using intermediate tier that is application tier