How to register shell script as concurrent program?

Below is the step by step process of registering shell script as a host program or concurrent program in Oracle Applications.
1. Create a shell script ( say sample) and move it to the appropriate BIN directory in Oracle       Application.
2. The parameters in the shell scripts should start with $5 , $6 Onwards.
3. Rename the shell script to *.prog ( sample.prog).
4. Change Permissions to 755 for the *.prog file.
5. Create Link to the shell Script ( ln -s $FND_TOP/bin/fndcpesr/sample.prog) to create sample file.
6. Now Register this executable in the application as a host executable (Using System Administrator Responsibility).

While registering the shell script as a concurrent program make sure that the first parameter in the concurrent program is passed as the Fifth parameter in the shell scripts because the first four parameters are allocated to userid, request_id, resp_id, resp_appl_id.

Deleting of duplicate records based on latest Timestamp

delete from emp t1
where hiredate != (select max(t2.hiredate)
                                    from emp t2
                                   where t2.eno = t1.eno)
and eno = 1

Get Source Code of any SQL Object in Oracle

To get the source code of any SQL object in Oracle we can use a standard API(Package-function) DBMS_METADATA.GET_DDL

Syntax:
  dbms_metadata.get_ddl('object_type','object_name')

Example:
  select dbms_metadata.get_ddl('INDEX','SAM_IDX') from dual;

  select dbms_metadata.get_ddl('INDEX','SAM_IDX') from dual;
 

Steps To Take FRD Trace in 11.5.10 & R12 [ID 867943.1]

Applies to:
Oracle Inventory Management - Version: 11.5.10.0 and later   [Release: 11.5.10 and later ]
Information in this document applies to any platform.
This note includes all versions of Oracle E-Business Suite 11i and Release 12
Goal
Steps to take FRD Trace in 11.5.10 and R12 ?
Solution
In 11i
------
1.   Login to your Instance Unix session as Apps.

2.   Login to System Administrator Responsibility in Oracle Applications.

3.   Add the following profile to the USER LEVEL as follows

(e.g.)  ICX: Forms Launcher = http://celalnx32.us.oracle.com:10403/dev60cgi/f60cgi?&record=collect&log=ABC.log

4.    Logout and Log back in again, for this profile to take effect. When you login again, a message box pops up, which alerts you that Diagnostics is enabled.

5.    Reproduce the Forms-Issue.

6.    Exit the application.

7.    Locate the file in the directory set by variable $FORMS_TRACE_PATH.


In R12
------
1. Login to your Instance Unix session as Apps.

2.Make the USER value for profile option 'ICX: Forms Launcher' the same as the SITE value.

3. Append the USER value of ICX: Forms Launcher with the "?record=collect" and save this at the user level.
(Eg):http://celalnx20.us.oracle.com:10106/forms/frmservlet?record=collect

4. Logout and Log back in again, for this profile to take effect. When you login again, a message box pops up, which alerts you that Diagnostics is enabled.

5. Go to Help->About Oracle Applications, go to the section 'Forms Server Environment Variables'
There you should see a value in FORMS_TRACE_DIR. Note down the value.
 
6. Reproduce the Forms-Issue.
 
7. Locate the file in the directory noted in FORMS_TRACE_DIR.

Note: By default, this directories named as forms_.trc where pid is the process identifier.
Collect _

API to make invalid objects to valid in any instance

I would like to share you one standard API of making invalid objects valid in any instance.  This API will check the inter-dependencies between the objects and make them valid sequentially.  The below code needs to be run in apps schema which makes all the objects valid.  This info. I got from DBA who made 3600+ objects valid within half an hour.

BEGIN
exec sys.utl_recomp.recomp_parallel(20);
END;

REF CURSORS

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary
advantage of using cursor variables is their capability to pass result sets between sub programs (like stored
procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  en emp.ename%type;
begin
  open c_emp for select ename from emp;
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
  close c_emp;
end;
Let me explain step by step.  The following is the first statement you need to understand:
  type r_cursor is REF CURSOR;
The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR.  We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows:
  c_emp r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
  open c_emp for select ename from emp;
To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows:
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
I finally closed the cursor using the following statement:
  close c_emp;
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR.  Now I would like to retrieve more than one column (or entire row) of information using the same.  Let us consider the following example:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
In the above example, the only crucial declaration is the following:
  er emp%rowtype;
The above declares a variable named "er," which can hold an entire row from the "emp" table.  To retrieve the
values (of each column) from that variable, we use the dot notation as follows:
      dbms_output.put_line(er.ename || ' - ' || er.sal);
Let us consider that a table contains forty columns and would like to retrieve fifteen columns.  In such
scenarios, it is a bad idea to retrieve all forty columns of information.  At the same time, declaring and
working with fifteen variables would be bit clumsy.
Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value
or one complete record. How do we create our own data type, with our own specified number of values to hold?
This is where TYPE and RECORD come in.  Let us consider the following example:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  open c_emp for select ename,sal from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
The most confusing aspect from the above program is the following:
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
The above defines a new data type named "rec_emp" (just like %ROWTYPE with limited specified fields) which can
hold two fields, namely "name" and "sal."
  er rec_emp;
The above statement declares a variable "er" based on the datatype "rec_emp."  This means that "er" internally
contains the fields "name" and "job."
      fetch c_emp into er;
The above statement pulls out a row of information (in this case "ename" and "sal") and places the same into
the fields "name" and "sal" of the variable "er."  Finally, I display both of those values using the following
statement:
      dbms_output.put_line(er.name || ' - ' || er.sal);
As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time.  Before
associating a new SELECT statement, we need to close the CURSOR.  Let us have an example as follows:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  open c_emp for select ename,sal from emp where deptno = 10;
  dbms_output.put_line('Department: 10');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
  open c_emp for select ename,sal from emp where deptno = 20;
  dbms_output.put_line('Department: 20');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
In the above program, the skeleton looks like the following:
declare
.
.
Begin
.
.
  open c_emp for select ename,sal from emp where deptno = 10;
.
.
      fetch c_emp into er;
.
.
  close c_emp;
.
.
  open c_emp for select ename,sal from emp where deptno = 20;
.
.
      fetch c_emp into er;
.
.
  close c_emp;
.
.
end;
From the above skeleton, you can easily understand that every CURSOR is opened, used and closed before opening
the same with the next SELECT statement.
Working with REF CURSOR inside loops
Sometimes, it may be necessary for us to work with REF CURSOR within loops.  Let us consider the following
example:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
    close c_emp; 
  end loop;
end;
As you can observe from the above program, I implemented a FOR loop as follows:
  for i in (select deptno,dname from dept)
  loop
      .
      .
  end loop;
The above loop iterates continuously for each row of the "dept" table.  The details of each row in "dept" (like deptno, dname etc.) will be available in the variable "i."  Using that variable (as part of the SELECT statement), I am working with REF CURSOR as follows:
    open c_emp for select ename,sal from emp where deptno = i.deptno;
The rest of the program is quite commonplace.
Sub-programs can also be called sub-routines.  These are nothing but the divisions of the main program.  These divisions are named and are executed when they are called by name from the main program.  They will not get executed unless they are called. 
The following is an example:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
  procedure PrintEmployeeDetails is
  begin
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    PrintEmployeeDetails;
    close c_emp; 
  end loop;
end;
In the above program, the sub-routine is named "PrintEmployeeDetails."  You can observe that I am executing (or calling) the sub-routine from within the loop as follows:
  for i in (select deptno,dname from dept)
  loop
      .
      .
      PrintEmployeeDetails;
      .
      .
  end loop;
According to the above loop, the sub-routine gets executed for every iteration, which displays the employee
information for the respective department.  Passing REF CURSOR as parameters to sub-programs
In the previous section, we already started working with sub-programs (or sub-routines).  In this section, I
shall extend the same with the concept of "parameters" (or arguments).  Every sub-program (or sub-routine) can accept values passed to it in the form of "parameters" (or arguments).  Every parameter is very similar to a variable, but gets declared as part of a sub-program.
Let us consider the following program:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  procedure PrintEmployeeDetails(p_emp r_cursor) is
    er rec_emp;
  begin
    loop
      fetch p_emp into er;
      exit when p_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    PrintEmployeeDetails(c_emp);
    close c_emp; 
  end loop;
end;
From the above program, you can observe the following declaration:
  procedure PrintEmployeeDetails(p_emp r_cursor) is
In the above declaration, "PrintEmployeeDetails" is the name of the sub-routine which accepts "p_emp" as a
parameter (of type "r_cursor") and we can use that parameter throughout that sub-routine.

Working with REF CURSOR in PL/SQL

SQL & PLSQL Interview Questions

1.    I want to write a function which inserts a row into a table and I want to get the rowid of the inserted row in the return statement. Is it feasible? Justif.
2.    I have 2 different databases. I don't have db links between them. I want to transfer the data from one database to the other. What will ou do?
3.    What is a materialized view? What is the refresh techniques used?
4.    If I have a MV which contains the data from different tables which are in different databases. Which approach (refresh) will be feasible for this scenario.
5.    Are you saying that MVs will store the local copy of the data?
6.    I have a partition P1 in which two more partitions are there P2 and P3. If I am exchanging data from one partition to the other, what will happen?
7.    In the above scenario, is it feasible to use global indexes or partition indexes?
8.    In a select query, will you write the joins and filter conditions in any specific order or we can write them in any order?
9.    How the sql SELECT will be parsed?
10.    How you will do the debugging in PLSQL
11.    I have a procedure running remotely. I want to see what is happening while its running. What is the method?
12.    What will you do in that log package which will log the errors?(PRAGMA AUTONOMOUS TRANSACTION)
13.    What are the new features of 9i/10g? 
14.    What is the representation of 'g' in 10g?
15.    What is the feature you like most in oracle plsql.
16.    What is explain plan? How you will analze the quer using explain plan?
17.    What is TKPROF? How you will use the TKPROF?
18.    Write down the steps to generate the trace file.
19.    How will You analze the trace file and how You will use the information in the trace file to tune the queries
20.    What are hints? Did you ever used hints? Name some hints you used.
21.    Unix - Learn
22.    Have you ever interacted with clients
23.    Have you been to onsite?
24.    What is an index? Types of indexes
25.    What do you mean b cardinality in bit map index
26.    When you issue the OPEN cursor command, will the data loaded into cursor already. If not when will it be loaded?
27.    What is a cursor? Types of cursors
28.    What is explain plan? 
29.    How you will tune the sql query?
30.    You did any certification? Is it valid now?
31.    What are the advantages and differences between a PACKAGE, PROCEDURE and a FUNCTION?
32.    Explain the oracle architecture.
33.    When will the DBwr writes the data into a DATA FILE?
34.    When will the REDO log buffer will be written b LGwn
35.    Have you done any data modeling? 
36.    What is normalization?
37.    What are the external tables?
38.    What are the aggregate functions you used?
39.    What are the analtical functions you used?
40.     I am writing a trigger in which I am inserting a value into a table and I am issuing SELECT COUNT(*) FROM the same table. What will happen?
41.    If I write the statement level trigger also, will this mutation happens?
42.    What is the possible solution for it?
43.    I have large amount of data. How will you load the data from other database to our database
44.    What is bulk collect and bulk insert? Explain with syntax.
45.    When I am inserting data using bulk insert, I got an exception. How will you continue the loop without aborting?
46.    How you do the exception handling while using bulk insert and bulk collect?
47.    What are plsql collections? 
48.    What are the things you will be having in a trace file? How you will analze and use this  information to tune your query?
49.    What is a trigger? How many no. of triggers we can write on a table?
50.    I have a table called CUSTOMER. Can I create the trigger with the same name
51.    So, If I can create the trigger with the same name means, the two objects are not stored in the same data page? Then where is the trigger stored?
52.    I have a scenario like, whenever I insert into a table with customer id, it should generate a new id and insert when an event occurs. How will you achieve this?
53.    I have a scenario like, I want to restrict the update of salary values like, one can increase the salary but one can't decrease the salary. How will you achieve this?
54.    Can we create triggers on views?
55.    What type of triggers can we create on views?
56.    Can we create a package specification without package body?
57.    Are the variables and other things declared in package spec are global?
58.    If I declare them in package body then are they global?
59.    Can you tell me some new plsql features of Oracle 10g?
60.    What is an inline view?
61.    What is a hierarchical query?
62.    What is dynamic sql?
63.    How you can execute the dynamic sql (Keyword used)
64.    What is a ref cursor?
65.    How many ways you can open a cursor?
66.    What are collections?
67.    How many collections are there in plsql?
68.    What is the difference between variable array and nested table?
69.    The arguments (parameters) in a procedure are of which type? Are they pass by value or pass by reference?
70.    What is bulk collect?
71.    What is returning into clause?
72.    What is NVL2?
73.    What is a foreign key constraint
74.    A: A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
75.    Did you ever worked on ETL
A: ETL stands for extraction, transformation and loading. Etl is a process that involves the following tasks:
extracting data from source operational or archive systems which are the primary source of data for the data warehouse
transforming the data - which may involve cleaning, filtering, validating and applying business rules
loading the data into a data warehouse or any other database or application that houses data
The ETL process is also very often referred to as Data Integration process and ETL tool as a Data Integration platform.
The terms closely related to and managed by ETL processes are: data migration, data management, data cleansing, data synchronization and data consolidation.

The main goal of maintaining an ETL process in an organization is to migrate and transform data from the source OLTP systems to feed a data warehouse and form data marts.
76.    Did you ever involved in database design
77.    Did you ever did import and export in database
78.    What are the tools you use to access the database
79.    What is a materialized view
80.    Difference between simple view and materialized view
81.    Can we update a materialized view?
82.    What is the difference between function and procedure
83.    How do you do performance tuning
84.    What is SQL trace 
85.    What is TKPROF
86.    What is the structure of a procedure
87.    What is a cursor? How many types of cursors are there
88.    What is a constraint? How many constraints are there in oracle
89.    I have two tables crated. I want to define a foreign key constraint on one column of second table from the first table. What is the syntax to add a foreign key constraint to the existing table
90.    What is the maximum size of varchar2 in a table for a column
91.    If I want to store more than 4000 characters data then what should I do
92.    What is the maximum size of LONG data type in oracle
93.    I want to store an Image file which is very big in size. Which data type I need to use
94.    What is LOB? What are the types?
95.    What can I store in a BLOB?

Drop Ship Cycle

Before you create an order, you have to perform some setups in order to drop ship, which are listed in the below mentioned article “Drop Ship Setups”:
Drop Ship Setups

1. Create Sale Order

2. Book Sales Order

3. Check Status

4. Progress Sales Order

5. Check Status

6. Release Purchase Order

7. Import Requisition / Purchase Order

8. Link between sales order and purchase order

9. Receive the material against purchase order

10. Check Order status.

Create Sale Order

Navigation: Order Management Super User >> Order Organizer



Click on ‘New Order’



Enter Order Header Information



Click on ‘Line Items’ tab



Click the ‘Shipping’ tab and enter Item Number , Quantity and Receiving Organization



Click ‘Book Order’ button.



If order is booked successfully then a confirmation message will be displayed as shown in the below picture.



Click on ‘Actions’ Button under ‘Line Items’ tab



Select ‘Additional Line Information’ from the List of values and click OK



Select ‘Drop Ship’ tab.

At this stage we do not have any purchase orders created related to this drop ship order.



Close the ‘Additional Line Information’ form.

Make a note that the line status is ‘Booked’ at this stage.



Let’s see the workflow status.

Click on Tools >> workflow status



Current activity will be ‘ Purchase Release Eligible’ with a status of ‘Notified’.



Close the workflow status page.

Go back to ‘Line Items’ and select the line. Right click on it and select ‘Progress Order’ option as shown below.



Select ‘Purchase Release – Eligible’ option from List of Eligible Activities.



Click OK.

The ‘Line Status’ changes from ‘Booked’ to ‘Awaiting Receipt’.





Click on Actions button

Select ‘Additional Line Information’.



Make a note that we still do not have ‘Purchase Order’ created for this drop ship order.



Close the order form.

Navigation: Order Management Super User >> Purchase Release



A Concurrent request submit screen will pop up.

Click on Parameters and enter the sales order number that we created above. By doing this concurrent request will just process this particular order instead of releasing all pending drop ship order requests.



Click Submit



Close all open forms.

Navigation: Purchasing Super User >> Reports >> Run



Select ‘Requisition Import’ program and click on parameters text box.

Enter parameters as follows

Import Source: ORDER ENTRY

Import Batch ID: Leave it blank

Group By: Item+

About Interfaces

What is a interface?
In terms of oracle applications interface is a communication channel that allows the data to move in and out of the system.

How many types of interfaces are there?
1.INBOUND INTERFACES
2.OUTBOUND INTEFACES

Inbound InterFace:The one which allows data to get into oracle application from outside is called inbound interface.

OutBound Interface:The one which allows data to get data from oracle applications to other systems is called outbound interface.

What are different types of inbound interfaces available?
1.Open interfaces
2.API's(Application Program Interface)
3.EDI(Electronic Data interchange)--Mainly used for automation transactions with third party systems
4.XML GATEWAY --Mainly used for automation transactions with third party systems
5.WEBADI--Used for uploading data from excel with the scope to have some validations--mainly used for one time loading..Just like sqlloader+validation program..
5.PLSQL Packages for Reading XML Data--Use this in case of importing non stadard transactions

What are different types of outbound interfaces available?
1.Custom programs where we pull data to a csv file using UTL_FILE in the required format
2.EDI
3.XMLGATEWAY
4.PLSQL Packages for generating XML

what is the difference between OPEN INTERFACE & API's?
OPEN INTERFACE:
I dont see much difference between the open other than the way we load data.
In case of open interface the data is loaded in to open interfce table like GL_INTERFACE or Sales Order Interface(OE_ORDER_HEADERS_IFACE_ALL).
Run the interface import program.
This will validate the data and put it into oracle applications.All the invalid records are marked as Error.
One thing is there are GUI Screens available for most of these interface where you check the errror message correct it there only and resubmit the interface.
From the technical perspective there are Error tables available for each interface

API:
API's are the oracle built packages where the validation logic is wrapped inside a package and the data is passed as parameters to the API.
Most of these api's use the PLSQL tables as paremeters to take the advantage of bulk binding concepts for faster loading data.
THey will have the two OUT parameterst to throw back the error code and message in case of data validation failure
Apis' are compartively faster than open interfaces.
If a API's and open interface are available it is better to load through API's.If the records are more.

What is the difference between data migration and conversion?
These are essentially the same.

What is the difference between data migration and interfaces?
Data Migration is a one-time activity however interfaces are ongoing processes that run regularly. Hence error handling must be well thought when designing interfaces.

Best practices for interfaces?
1. Usually an interface must have well defined error reporting mechanism.
2. Data errors must be fixed at the source once the transaction gets rejected.
3. If your support team is often modifying transactions in interface tables using sql, then your interface design is flawed.
4. If you log support issues with any interface on a relatively regular basis, then its time to re-visit your design.
5. Interfaces must be designed in a manner such that, once the original errors are fixed, those transactions must get re-processed during the next run (or on demand)
6. If possible an interface mechanism/infrastructure must be in place, so that all interfaces are written in similar manner.
7. There must not be any hard coding for mapping from source system data to EBS. You can either use oracle's lookup screen to define mappings, or use a custom mapping screen.
8. In some cases you will have a generic screen which facilitates one-to-one, one-to-many, many-to-one, many-to-many mappings.
9. There must exist a simple mechanism to identify the duplicate processing of any transaction must exist. One way this can either be achieved is by making your source system specify a unique identifier for each record.
10. Do not store references to rowid in any part of your interfaces.
11. Think upfront, design and write your interface in such a manner that once it goes to production, you never receive an email to fix any stuck records. If at all you do receive data fixing requests, then ensure that interface program is changed in a manner it gets handled without programmers intervention in future.
12. Have proper debugging. You could add a parameter for debug flag, so that debug messages aren’t generated unnecessarily. Surely, don't forget to use FND Logging, which is delivered out of the box by Oracle.
13. Make the concurrent program end with warning in case of errors. This can be done by passing retcode=1. Use the out-of-the-box concurrent program notification facility can be used to inform end users of the errors encountered during processing.
By doing so, you can make the monitoring automatic, i.e. when submitting/scheduling interface concurrent program, you can attach a workflow role.


What tools can I use for point to point interfaces?
Point to Point interface methodology is not ideal for a large Organization. However to keep the costs low, when number of interfaces are very low, then Point to Point Interface approach could be justified. However please note that Organizations do grow in size , specially after implementing Oracle ERP. Hence Point 2 Point Interfaces can not be a long term strategy.

Please find p2p interface approach listed:-
1. File tables..
   --You can map the structure of an ASCII file to table structure.
2. XML Gateway
   --You can use XML Gateway and XML Message designer if you have too many interfaces having XML source. Although keep in mind that there may not be any future for XML Gateway in Fusion applications.
3. Sql*loader
   --Avoid for interfaces as error reporting is not user friendly. Use this for data migration as SQL*Loader is very efficient.
4. DB link for intra company different Oracle systems
  --DBA's often raise questions regarding security of database links. However security concerns can be minimized by making them non-public and make them connect to not specific schemas with minimum privileges.
5. Java concurrent program with jdbc
If your source system is in a database like SQL*Server, or DB2 or any other database which supports JDBC, this approach could be ideal.
The source system can create a specialized schema and expose the necessary views or tables/synonyms, such that you can pull the data from that system, load it into your system using API. All this can happen seamlessly, all within one program.
6. Java concurrent program with FTP, csv parser
All the above steps can be done within one single program.


Usual bad practices:-
1. Each developer writing their own mapping tables and screens.
2. Hard coding in interface
3. No thoughtful process for Error Reporting and Error Correction(deviation from fix at source philosophy)
4. Calling SQL*Plus sessions from Host programs, without due considerations for APPS Password Security
5. No debugging
6. Each interface program using its own File Dequeue mechanism.

Long term interface strategy using Oracle EBS Delivered tools
Oracle Warehouse Builder [Preferably 10g Version]
This strategy means that you move away from point to point interface design. I think a simple tool like Warehouse builder can be used as a simple single point interface control. Some powerful features of Warehouse builder means that you can do mappings, transformation of data structures without writing your own code. Warehouse Builder appears to be a part of Fusion strategy, hence it is a good investment. Warehouse builder can be used to pull the data from various sources using ODBC, JDBC, DB Links etc. Hence you can leverage a Warehouse Builder interface design run interface end-to-end that includes submission of the Interface API.
Limitation:- This is a batch mechanism, and not messaging based.

XML Gateway
If you want an Oracle delivered tool to implement interfaces using messaging, you can use XML Gateway with Message Maps. But investment in this tool may be lost when you move to BPEL based architecture.

Web Services
Wait for Fusion that integrates BPEL, unless you wish to manage Web Services in a standalone manner.

Forms Personalization Vs Custom.pll

Forms Personalization Vs Custom PLL

1. CUSTOM.pll is a single file/entity; hence only one developer can make changes to CUSTOM.pll at any given point in time.

2. If for some reasons CUSTOM.pll causes issues in Production, you will have to re-release the code changes after another round of UAT.
Form Personalization's makes this much simpler by allowing you to disable specific personalizations.

3. You do not need to resort to programming for trivial tasks like changing prompt and more so for changing prompts in Multi-Lang environment

Important: Oracle will keep on improving capabilities in Forms Personalization's.
Hence some of these limitations that we see now in Forms Personalization might disappear over a period of time.

Note-1
Once a menu has been enabled using Forms Personalization, it then cannot be conditionally disabled. However the proposed workaround over this issue is to display a message when MENU is clicked in the scenario\x{2019}s it was meant to be disabled.

Note-2
Let's say we want to change the window title for all the screens within a responsibility. In this case, in CUSTOM.pll you could do so by a pseudo code similar to below
If fnd_global.responsibility like 'XX%CASH%' THEN
Set window title (Note: All transactions are audited);
End if
As you can see, this can be achieved quite easily in CUSTOM.pll and the changes will be visible across multiple screens in the context.
However, for Forms Personalization, you will have to go and personalize each and every screen for which you want this change to be effective.


Note-3
The proposed workaround is to display the message that is returned as a result from SQL Statement.

Question: Should we migrate code from CUSTOM.pll into Forms Personalization?
Not really, there is no value in doing so, given that CUSTOM.pll is still supported and will remain so within the realms of Apps Unlimited.


Where is the metadata for Forms Personalization stored?
These personalization's are stored in FND tables

What is called first? Forms Personalization or CUSTOM.pll
First your form personalization is called, and then CUSTOM.pll is called.

Display the message in the status bar - Oracle Forms

To display the message in the stauts bar in Oracle Forms below mentioned command can be used,
Message('Message..',NO_ACKNOWLEDGE);

GL INTERFACES

Difference between Interface and API
An API (Application Programming Interface) is inbuilt program through which data’s can be transferred to Oracle base tables  directly without writing the program for validating or inserting in Interface tables.
But through User Interface, we have to write codes for validation and insertion of data’s in
Interface tables and then in Oracle base tables
Oracle defines an API as "A set of public programmatic interface that consist of a language and message format to communicate with an operating system or other programmatic environment, such as databases, Web servers, JVMs, and so forth. These messages typically call functions and methods available for application development."
I guess that there is no such thing as an interface to Oracle=2 there are only "open" interfaces.
These are a group of published and supported inbound and outbound Interface objects (talk about them
more in a second) that Oracle has developed for your use=2 these objects may be, a table or group of tables,
a concurrent program that references a PL*SQL or Pro*C/C++ package o library.
Oracle's policy over the years is that if you post data directly to an application table, then you are
invalidating your support agreement, and if you are bored enough, you can see the
clause in the agreement sure enough.
But these published interfaces are quite often not enough to d what you need to do. Take the
case of Payables suppliersfor example. That has been a pain for everyone
Right from the beginning.
So in answer to your question. An open interface is a group of objects that Oracle supports
and licenses with the software that allow inbound and outbound data transactions in Oracle'
Approved format. You put data into the tables and oracle import it or you run a concurrent request
and oracle outputs data into that table. Batch processing. You need to put your data into the right
pre-validated format before inserting into the interface table or the data will be rejected.
An API is programmatic hooks or coding blocks that you can "call that allow you to perform
some function or other to achieve par of your goal. For example, Oracle Projects there is
PA_INTERFACE_UTILS_PUB.CREATE_PROJECT API that takes a list o input variables that you assign
to the called API (these can b in a table or passed directly) for example;
l_project_in.created_from_project_id := '1001';
-- Project id from template
l_project_in.project_name :='C, AMG TEST PROJECT';
l_project_in.pm_project_reference := 'C, AMG TEST PROJECT';
and if the package passes validation on all these data points your project will be inserted as a result.
So finally, you can insert data into an open interface table then use a concurrent program that CALLS
an API to process that information.
In the case of real-time processing or OLTP, once you save record in PeopleSoft HR, you want it to appear in Oracle H
immediately, you would write PeopleSoft code that copies those data items to memory, submit the list of required
variable (PeopleSoft data)to the Oracle API as inputs. The API o completion posts your employee into the
Oracle database=2 (ideally you would provide a function then back to PeopleSoft t confirm that oracle received
the transaction correctly and i wasn't rejected by the API). All this was achieved without the use of an open "interface".
The nail in the coffin however and where you may be confused, i that many people think that any exchange of
data from one table to another is an "interface". A "link".
And I guess that it i in a way.
Just remember that
An interface is the pool,
An open interface table is the lanes and
An API is the swimmer.
The term Open Interfaces actually refers to table driven interfaces, where you put data in
a table that sits between your external application and the Oracle module you are interfacing with.
The term API refers to stored procedure driven interfaces, where you call a stored procedure
to perform an action within an Oracle Module, and the data from your external application is
passed through the stored procedure’s parameters. Historically, there were only table driven
interfaces and they were called Open Interfaces. Later, stored procedures were added and were called APIs.

GL Interface

Interface tables:
GL_INTERFACE

Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program: Journal Import
Journal Posting  --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Validations for the staging table:
Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.

Staging Table:
Create table XX_GL_RY_STG
(status varchar2(50),
set_of_books_id number(15),
User_JE_Source_name varchar2(25),
user_je_category_name varchar2(25),
currency_code varchar2(15),
actual_flag char(1),
ACCOUNTING_DATE date,
DATE_CREATED date,
CREATED_BY number(15),
entered_dr number,
entered_cr number,
accounted_dr number,
accounted_cr number,
segment1 varchar2(25),
segment2 varchar2(25),
segment3 varchar2(25),
segment4 varchar2(25),
segment5 varchar2(25)
);
insert into XX_GL_RY_STG values(
'NEW',1,'Manual' ,'Adjustment','USD','A','20-MAR-2009' ,'20-MAR-2009', 2000,2000,2000,2000,
'01','000','9950','2080','000','0')

Package:
CREATE OR REPLACE PACKAGE XX_GL_INT_RY_PKG
IS
PROCEDURE xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2);
END;
CREATE OR REPLACE Package body XX_GL_INT_RY_PKG
is
Procedure xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2)
is
cursor GL_CUR
IS
SELECT Status,set_of_books_id,User_JE_Source_name,
user_je_category_name,currency_code,actual_flag,
ACCOUNTING_DATE,DATE_CREATED,CREATED_BY,entered_dr,
entered_cr,accounted_dr,accounted_cr,
segment1, segment2, segment3, segment4, segment5
FROM XX_GL_RY_STG;
lv_status varchar2(50);
lv_sob_id Number(15);
lv_user_je_source_name varchar2(25);
lv_user_je_category_name varchar2(25);
lv_cur_code varchar2(15);
lv_actual_flag varchar2(1);
lv_err_flag varchar2(2);
lv_flag varchar2(2);
BEGIN
FOR rec in GL_CUR
LOOP
lv_flag := 'A';
lv_err_flag := 'A';
BEGIN
SELECT distinct Status into lv_status from XX_GL_RY_STG Where status = 'NEW';
EXCEPTION
When no_data_found Then
lv_status := null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The status is not correct so change the status');
FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_status );
END;
BEGIN
SELECT set_of_books_id into lv_sob_id from gl_sets_of_books
where set_of_books_id=rec.set_of_books_id;
Exception
When no_data_found Then
lv_sob_id:=null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The SOB is not correct change SOB ID');
End;
FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_sob_id );
BEGIN
SELECT user_je_source_name into lv_user_je_source_name FROM GL_JE_SOURCES
WHERE user_je_source_name=rec.user_je_source_name;
EXCEPTION
WHEN no_data_found THEN
lv_user_je_source_name := NULL;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The SOURCE NAME is not correct change It');
END;
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_source_name );
BEGIN
SELECT user_je_category_name INTO lv_user_je_category_name FROM GL_JE_CATEGORIES
where user_je_category_name=rec.user_je_category_name;
EXCEPTION
When no_data_found Then
lv_user_je_category_name:=NULL;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Category name is not correct Change it');
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_category_name );
END;
BEGIN
SELECT currency_code into lv_cur_code from FND_CURRENCIES
where currency_code=rec.currency_code;
Exception
When no_data_found Then
lv_cur_code:=null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency code is not correct ');
End;
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_cur_code);
BEGIN
SELECT ACTUAL_FLAG into lv_actual_flag from XX_GL_RY_STG
where actual_flag in ('A','B','E');
Exception
When no_data_found then
lv_actual_flag := null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Flag is not correct');
END;
FND_FILE.PUT_line(FND_FILE.LOG,'The dat inserting is... '|| lv_actual_flag);
IF lv_flag = 'A' THEN
INSERT into GL_INTERFACE (
STATUS, SET_OF_BOOKS_ID, USER_JE_SOURCE_NAME ,USER_JE_CATEGORY_NAME,
CURRENCY_CODE,ACTUAL_FLAG,
ACCOUNTING_DATE, DATE_CREATED,CREATED_BY, ENTERED_DR,ENTERED_CR,
ACCOUNTED_DR,ACCOUNTED_CR,segment1, segment2, segment3, segment4, segment5)
VALUES (
lv_Status, lv_sob_id, lv_User_JE_Source_name, lv_user_je_category_name,
lv_cur_code,lv_actual_flag,rec.ACCOUNTING_DATE, rec.DATE_CREATED,
1318,rec.entered_dr, rec.entered_cr, rec.accounted_dr,rec.accounted_cr,
rec.segment1, rec.segment2, rec.segment3, rec.segment4, rec.segment5);
END IF;
lv_flag :=null;
lv_err_flag:=null;
END LOOP;
COMMIT;
End;
END XX_GL_INT_RY_PKG;
/

Base tables for GL Daily Rates are—

Gl_DAILY_RATES

Interface table for GL Daily Rates are—
Gl_DAILY_RATES_INTERFACE

Moving the Data from Flat File to Base Table using SQL * LOADER:
Options (Skip =0)
Load data
infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'
Insert into table GL_daily_rates_stg
fields terminated by ','
optionally enclosed by '"'
Trailing nullcols
(From_currency ,To_currency, From_conversion_date, To_conversion_date,
User_conversion_type, conversion_rate, Mode_flag)
Moving the data from Staging tables to Base Tables using
Standard Interface Programs:
Create a Staging table based on the requirement
CREATE TABLE XXGL_DRATES_STG (
FROM_CURRENCY VARCHAR2(15),
TO_CURRENCY VARCHAR2(15),
FROM_CONVERSION_DATE DATE,
TO_CONVERSION_DATE DATE,
USER_CONVERSION_TYPE VARCHAR2(30),
CONVERSION_RATE NUMBER,
MODE_FLAG CHAR(1));
Inserting Data into Staging Table:
Insert into XXGL_DRATES_STG Values (
'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');

Create a Package with validations to move the data into Interface Tables
CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number,errbuff out varchar2);
END;
CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number, errbuff out varchar2)
Is
Cursor cur_drates is
Select FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE , TO_CONVERSION_DATE ,
USER_CONVERSION_TYPE, CONVERSION_RATE , MODE_FLAG FROM XXGL_DRATES_STG;
LV_FROM_CURRENCY VARCHAR2(15);
LV_TO_CURRENCY VARCHAR2(15);
LV_USER_CONVERSION_TYPE VARCHAR2(30);
LV_CONVERSION_RATE NUMBER;
LV_ERR_FLAG VARCHAR2(1):= 'A';
BEGIN
FOR i IN CUR_DRATES
LOOP
BEGIN
Select CURRENCY_CODE into LV_FROM_CURRENCY FROM
FND_CURRENCIES where CURRENCY_CODE=i.FROM_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_FROM_CURRENCY );
BEGIN
Select CURRENCY_CODE into LV_TO_CURRENCY
FROM FND_CURRENCIES where ENABLED_FLAG='Y'
AND CURRENCY_CODE=i.To_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_TO_CURRENCY );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');