Oracle Open Interface

Oracle Open Interface

What is Oracle open interfaces?

Open interfaces used to integrate Oracle Applications with the non-oracle systems.
such as
1. Import data from your custom applications or legacy systems.
2. Migrate the old custom application data to the Oracle applications.
3. Import data from the banks, partners, suppliers, customers etc.


What is conversion?

Conversion is a one time Interface to migrate data from legacy application to the Oracle


Inbound Interface
The Interface which transforms and loads the data into the Oracle Applications system is called inbound interface.


Outbound Interface
The Interface which extracts the data from the oracle Application system for the purpose of loading into the external system is called outbound interface.


Common approach to interface is

1. Data would be extracted from the source machine and loaded into the custom table (stagging table) in Oracle apps database. Depending upon the complexity of the data Stagging table can be avoided.
2. The extracted data is then transformed into a form which oracle can recognize and pushed into interface tables.
3. Final step is load the data from interface table to base table of Oracle applications.

This whole process is called extract load and transformation (ETL).


Oracle provides Interface tables for data intensive applications like Invoice, receipts, journals etc and PL/SQL APIs for master data interfaces like people, items, suppliers etc.


Traditional approach used sql Loader to load the data and pl/sql program to validate and extract data.
Modern approach uses BPEL, ESB for online data interfaces and Oracle Data Integrator for data intensive batch processing.


Common Interface technologies
1. XML Gateway
2. SQL*Loader
3. DB Link
4. Java Concurrent Program
5. BPEL or ESB
6. Oracle Data Integrator or ODI
7. PL/SQL package for reading file or XML data
8. Oracle Advanced Queuing

GL Interface Tables

GL Interface Tables

GL_BUDGET_INTERFACE 
Used to upload budget data into your Oracle General Ledger application

GL_DAILY_RATES_INTERFACE
Used to insert, update, or delete daily rates in the GL_DAILY_RATES table.

GL_IEA_INTERFACE
Used to create intercompany transaction data.

GL_INTERFACE
Used to create journal entries, Transfers source information into a target set of books, Creates accounting for Intercompany Transactions etc.

Base Tables
Following are the base table for Journal entry.
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent program
Journal Import
Journal Posting


GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY

FORM PROCESS

FORM PROCESS

1)We Will download the TEMPLETE.fmb  from AU_TOP\Resource Folder
    Application Utilities

2)Open with Forms6i builder and delete the existing Block                             Canvas                Window
3)Create New Block , Canvas , Window attach oracle apps Property classes

4)Develop the form as per client requirement

Note:We will change the Property called "First Navigaction Data Block" as Datablock Name
     in the Module Properties.

II)Customize folloiwng two triggers

1) Pre-Form       -New Window Name 
2) WHEN-NEW-FORM-INSTANCE -New form Name
3) APP_Custom package     -New Window Name  

5)Save the form we will get (.fmb)
6)compile the form we will get(.fmx)
 (File => Administration => Compile File)

7)Move the .fmb into au_top\11.5.0\Forms\US\ .fmb
8)Move the .fmx into CuS_top\11.5.0\Forms\US\ .fmx

9)Goto Applicaton Developer and create Form

10)create Function attach form to the function

11)Create Menu attach function menu

12)Attach Menu to the responsibility , Responsibility will be attached to the user
  so that user can open the form and enter/Query the Data. 




1)Form Createion :
Application Developer=> Application=>Form
Form Name           : .fmx name
Application Name    : Application name where .fmx is located
User Form Name      : We can give any form Name

2)Function Creation: For single form we can create Multiple Functions and we can pass
          the Parameters.
Function Name     : Enter any uniquie Name
User Function Name: Enter any uniquie Name
Goto Properties tab select Form type = FORM
Goto the  form tab attach the Form what ever we have created.
Save and Copy the User function name attach to the Menu.

3)open the Menu 
  Enter menu name : Any valid Name
  User Menu Name  : Any valid Name
Enter the Seqno    : Unique Number
          Prompt   : Any Text
          Function : Attach the User Function NAme whatever we have create     

4)Copy the User menu name and attach to the Responsibility in system administrator.

TEMPLETE.fmb : It is pre-defined .fmb contains the existing functionality which will
suport for all the Menu,Toolbar, and Other Oracle Applciations features.
that is the Reason we have to start form development with TEMPLETE.fmb

ProfileS

Profile :
Profile is one of the changable option it will change the way of
application execution.

When User Log in to the application and select the the resp or Appl 
system will automatically captures all the profile value as per the
profile values application will run.

Ex: If client have three Organizations 1)Hyd
           2)Ban
           3)Chn
If "hyd" users connect to the Application system will retrive the
data from database which is related to the Hyderabad branch.
If user is working for 'CHN' brnach then chennai branch setups or data
will be retrieved.

For every user we will assign the Profile value

Ex: Operation  

Position - Profile Name
Profile Values
--------------
Manager
Supervisior
Clerk
Operator
Trainess

When we want assign any profile value we have four levels 
we have to select any one of the level.


Profile Level   Profile  Profile Value
-------------   -------  --------------
User  - OPERATIONS -  Print  - 10(This is for for Operations)  
Responsibility  - 22Responsi -  Print  -  5(This is for 22resp users)
Application     - GL Applica -  Print  -  4(This is for GL App Users)
Site  - ---        -  Print  -  2(This is for ALL Users)

Site : this is lowest level to assign the Profile values site values
are applicable for all the users.when we install Application by default
site level values will be assigned.

Application: These values are applicable for the users who are having 
the access for the application. If user is eligible for both 
application and site level values then application level value will
override the site level value.

Responsibility:We will select the responsibility name assign the value
which is applicalbe only for the users who are having the access for 
specified responsibility.

Responsibility level value will override both application and site
level values.

User: This is highest level in the profile option.
we will select the user name and assign the profile value which is
applicable only for this user.
User level value will override all other profile level values.

Diff between Application and Responsibility:
============================================
Both are Group of Forms(Menu)
         Group of ConcurrentPrograms(Request Group)
         Group of Users (Data group)
But Application as per the Business functionality requirement
Responsibility will group as per the position requirement.

Some of the Imp Profile Names:

GL:Set Of Books
MO:Operating Unit
Hr:Business Groups
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
RESP_NAME and so on..........

Multiorg (11i)

Multiorg (11i): ============= 

Use a single installation of any Oracle Applications product to support any number of organizations, even if those organizations use different sets of books.  

BusinessGroup (HRMS=>Workstructure=>Organization=Description)  

Set of Books  (GL=>Setup=>Financials=>Books=>Define) 

Legal Entity    

Operating Unit  

Inventory Organization   

Sub Inventory     (Inventory=.Setup=>organizations=>SubInventories)   

Stock Locations   (Inventory=.Setup=>organizations=>Stock locatiors)    

Items  

Major Features

 =============== 

1)Multiple Organizations in a Single Installation

  2)Secure Access      You can assign users to particular organizations. This ensures accurate     transactions in the correct operating unit. 

 3)Multiple Organizations Reporting  You can set up your Oracle Applications implementation to allow reporting across operating units by setting up the top reporting level. You can run your reports at the set of books level, legal entity level, or operating unit level 

 Business Group: 

=============== 

The business group represents the highest level in the organization structure, such as the consolidated enterprise, a major division, or an operation company. The business group secures human resources information. For example, when you request a list of employees, you see all employees assigned to the business group of which your organization is a part.  

Set Of Books:

 ============ 

A financial reporting entity that uses a particular chart of accounts, functional currency, and accounting calendar. Oracle General Ledger secures transaction information (such as journal entries and balances) by set of books. When you use Oracle General Ledger, you choose a responsibility that specifies a set of books. You then see information for that set of books only.  

 Legal Entity:

 ============= 

A legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other legal entity information to this type of organization.  

Operating unit: 

=============== 

An organization that uses Oracle Cash Management, Order Management and Shipping Execution, Oracle Payables, Oracle Purchasing, and Oracle Receivables. It may be a sales office, a division, or a department. An operating unit is associated with a legal entity. Information is secured by operating unit for these applications. Each user sees information only for their operating unit. 

 Inventory Organization:

 ======================= 

An organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products. Examples include (but are not limited to) manufacturing plants, warehouses, distribution centers, and sales offices. The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and Purchasing receiving functions.  

Subinventory:

 ============= Which is another organization inside of the Inventory oganization will  be used to define the locations under these location items will be placed.   

Multiorg Table: 

=============== 

It is a table contains the data which is relted to multiple operating units all the multiorg table names will be end with '_ALL'.  like  PO_HEADER_ALL        PO_LINES_ALL       AP_CHECKS_ALL  and so on  Note: In all these tables we will find one common column called "ORG_ID"   This column will be populated internally by the system as per the User Operating    Unit ID  

Client_Info: 

=========== 

It is one  the RDBMS vaiabel which contains the User Operating Unit value (ORG_ID)  

Multiorg View:

 =================

 It is a view which is created based on the Multiorg table which contains the WHERE  clause WHERE Org_ID = :Client_Info.  Note: While development of RICE Components we are suppose to Use Multiorg Views       not Multi Org Tables.  Because if we use Multiorg tables we will get all the operating units data if we use multiorg view we will get the operating units data which is related for that perticular user .  

 1)What is the Diff between ORG_ID and ORGANIZATION_ID? 

Ans)ORG_ID              is at Operating Unit Level     ORGANIZATION_ID     is at Inventory Organization 

level 

 2)Why the PO Receipt functionality will come at Inventory organization level? Ans)user is creating receipt means indirectley he will be receiving the materials from     suppliers.materials will be received at Gowdons ,Warehouses,manufcaturing plants     all these organizations will come at Inventory Organization level.  

3)how the System Will Identify user is working for so and so operating Unit? Ans ) By using following Profile called        MO:Operating Unit

4)What is Multiorg? 

5)What is Client_info?

 6)how to Implement Multiorg in Reports and at SQL prompt? 

7)What is Business group, Legal Entity,Operating Unit,Inventory Organizations?

 8)What are the Modules will come at operating Unit level? 

9)What is the flow of Multiorg? 

10)How to Identify the Multiorg Table? 

11)Wat is the Diff between Multiorg Table and Multiorg View? 

12)While Developing RICE Components we will use Multiorg Table or Multiorg View? 

13)Why there is no _ALL for PO_VENDORS    and there is    _ALL for PO_VENDOR_SITES_ALL? Ans)Supplier is global     Supplier site is Org Specific. 

14)How will you findout Multiorg Succesfully Implemented? 

Ans)  SELECT MULTI_ORG_FLAG ,RELEASE_NAME FROM FND_PRODUCT_GROUPS  Y - Succesfully Implemented N - Not Implemented Succesfully.                                                

--
Prasanth Reddy

DFF CUSTOMIZATION STEPS

DFF CUSTOMIZATION STEPS:

1)Open the form where DFF is Implemented. and Query the Data 

 2)find the DFF name from Help Menu =>Diagnastcs =>Examine     Select  $DESCRIPTIVE_FLEXFIELD$ in the Block field     select  DFF name in the "Field"     we will get the DFF Title in the Value field.  Copy the DFF title

 3)goto Application Developer Responsibility.  flexfield=>Descriptive=>register  

4)Query the records by using the  DFF Title.  Select columns button and find wether Attribute   columns are enabled or disabled. 

 5)Goto Segments form and Query the records by using Title.  

6)Uncheck the Check box called "freeze Flexfield Defination" 

 7)Select Segents button and enter the new fields and save transactions.  

8)Check the check box called "freeze Flexfield Defination"  and click the Compile Button. 

 9)System will change the DFF structure and creates  a descriptive flexfield View. 

--
Prasanth Reddy

DEFAULT VALUES AND TYPES IN CONCURRENT PROGRAM

DEFAULT VALUES AND TYPES IN CONCURRENT PROGRAM:-

Current Date : Will be used to pass the Sysdate as default value Current Time : Will be used to pass the Systime as default value Constant     : Will be used to pass the constant number or date or                string as default value Segment      : will be used to get the Previous parameter value as             default to the next parameter then we will use this.  Default type:  segment         Default Value :ParameterName  SQL Statement: We can pass the SELECT statement result as default              value  Default type : SQl Statement  Default Value : SELECT MIN(USERID) FROM  Profile : This will be used to pass the user profile value as default like userid ,username,respid,respname and so on.   Required ChekBox : This will be used to make the parameter as mandatory                   or optional Enabled  CheckBox: will be used to enable or disable the parameter Display  CheckBox: will be used to hide or display the parameter. 

--
Prasanth Reddy