ORACLE Applications 11i Q/A

1. How can you tell your application is multi-org enabled?(Table & Column)

select multi_org_flag from fnd_product_groups is 'Y'

2. What is multi-org? structure of multi org?

A single installation of software which supports the independent operation of your business units (such as sales order booking and invoices0 with key information being shared across the entire corporation (such as on hand inventory balance, item master, customer master and vendor master).

Multiple Org. in a single installation:

We can define multiple org. and the relationship among them in a single installation. These org. can be set of books,Business group, Legl entitiy, Operating unit or Inv. Org.

Organisation Structure Levels

· Business groups

· Accounting Set of Books

· Legal Entity

· Operating Unit

· Inventory Org.

A) Business Group:

Represents the highest level in the org.structure.

HR OrG : Represents the basic work structure of any enterprise. They usually represent the functional management or reporting groups that exists within a Business Group.

B) Accounting Set of Books:

The financial reporting entity for which there is a chart of Account, Currenct and Financial Calendar for securing ledger transaction

c) Legal Entity:

The Org. at whose level fiscal and tax reporting is preparted, each legal entity can have one or more balancing entities.

Balancing Entity: Represents on accounting entity for which you prepare financial statements.

Legal entities post to a set of books: Each Org. classified as a legal entity indentifies a SOB post accounting transaction.

D) Operating Unit:

The Org. which is considered a major ‘division’ or business ‘unit’ at whose level business transactions are segregated sales orders,invoices, cash applications such as OE, AR, AP & Parts of PO are ‘partitioned’ at this level meaning that operating units have visibility only to their own transaction. It may be a sales office, division, dept.

Operating unit is defined as unit that need their payables receivables,cash management and purchasing transactions dat a separated. Sometimes a legal entity can be a ‘OU’ if relationship is one to one.

Operating unit are part of legal entity:

Each Org. classified as an operating unit is associated with a legal entity.

E) Inventory Org:

The org. at which warehousing,manufacturing and/0r planning functions are performed. An Org, which you track inventory transactions and/or an Org. that manufactures or distributes products. It’s a ‘OU’ that needs its own separate data for Bill of material, WIP , Engineering master scheduling, Material requirement planning, capacity and Inventory.

3. What is the function of conflict resolution Manager?

Concurrent managers read request to start concurrent programs running. The Conflict Resolution Manager checks concurrent program definitions for incompatibility rules.

If a program is identified as Run Alone, then the Conflict Resolution Manager prevents the concurrent managers from starting other programs in the same conflict domain.

When a program lists other programs as being incompatible with it, the Conflict Resolution Manager prevents the program from starting until any incompatible programs in the same domain have completed running.

4. What components are attached to responsibility?

Menu

Data Group

Request Group

5. What is the version of database for oracle applications11i?

Present we are using RDBMS version 9.2.0.3.0

6. What is the responsibility?

A responsibility determines if the user accesses Oracle Applications or Oracle Self-Service Web Applications, which applications functions a user can use, which reports and concurrent programs the user can run, and which data those reports and concurrent programs can access.

Note: Responsibilities cannot be deleted. To remove a responsibility from use, set the Effective Date's To field to a past date. You must restart Oracle Applications to see the effect of your change.

7. What is data group?

A data group is a list of Oracle Applications and the ORACLE usernames assigned to each application.

If a custom application is developed with Oracle Application Object Library, it may be assigned an ORACLE username, registered with Oracle Applications, and included in a data group.

8. What is request group and request set?

A request security group is the collection of requests, request sets, and concurrent programs that a user, operating under a given responsibility, can select from the Submit Requests window.

System Administrators:

Assign a request security group to a responsibility when defining that responsibility. A responsibility without a request security group cannot run any requests using the Submit Requests window.

Can add any request set to a request security group. Adding a private request set to a request security group allows other users to run that request set using the Submit Requests window.

9. What is form function and Non-form function?

A form function (form) invokes an Oracle Forms form. Form functions have the unique property that you may navigate to them using the Navigate window.

Subfunction (Non-Form Function)

A non-form function (subfunction) is a securable subset of a form's functionality: in other words, a function executed from within a form.

A developer can write a form to test the availability of a particular subfunction, and then take some action based on whether the subfunction is available in the current responsibility.

Subfunctions are frequently associated with buttons or other graphical elements on forms. For example, when a subfunction is enabled, the corresponding button is enabled.

However, a subfunction may be tested and executed at any time during a form's operation, and it need not have an explicit user interface impact. For example, if a subfunction corresponds to a form procedure not associated with a graphical element, its availability is not obvious to the form's user.

10. What is menu? What are menu exclusions?

A menu is a hierarchical arrangement of functions and menus of functions. Each responsibility has a menu assigned to it.

Define function and menu exclusion rules to restrict the application functionality accessible to a responsibility.

Type

Select either Function or Menu as the type of exclusion rule to apply against this responsibility.

When you exclude a function from a responsibility, all occurrences of that function throughout the responsibility's menu structure are excluded.

When you exclude a menu, all of its menu entries, that is, all the functions and menus of functions that it selects, are excluded.

Name

Select the name of the function or menu you wish to exclude from this responsibility. The function or menu you specify must already be defined in Oracle Applications.

11. How can you register form? explain the steps?

Step 1. Generate the fmx and place that fmx in module specific forms\us directory

Step 2. Then register the form with application developer or system administrator responsibility.

Step 3. Define the function and attach the form to that function.

Step 4. Attach the function to menu

12. How can you register a table in apps?

We can register the table in apps by using the AD_DD pacakge. The available Procedures are

• register_table
• register_column
• delete_table
• delete_column
12. What is AD_DD package? what are the different types of procedures available in it?

AD_DD Package is a PL/SQL routine used to register the custom application tables.

Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert. You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables.

To alter a registration you should first delete the registration, then reregister the table or column. You should delete the column registration first, then the table registration.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.

Procedures in the AD_DD Package

procedure register_table (p_appl_short_name in varchar2,

p_tab_name in varchar2,

p_tab_type in varchar2,

p_next_extent in number default 512,

p_pct_free in number default 10,

p_pct_used in number default 70);

procedure register_column (p_appl_short_name in varchar2,

p_tab_name in varchar2,

p_col_name in varchar2,

p_col_seq in number,

p_col_type in varchar2,

p_col_width in number,

p_nullable in varchar2,

p_translate in varchar2,

p_precision in number default null,

p_scale in number default null);

procedure delete_table (p_appl_short_name in varchar2,

p_tab_name in varchar2);

procedure delete_column (p_appl_short_name in varchar2,

p_tab_name in varchar2,

p_col_name in varchar2);

Example of Using the AD_DD Package

Here is an example of using the AD_DD package to register a flexfield

table and its columns:

EXECUTE ad_dd.register_table(’FND’, ’CUST_FLEX_TEST’, ’T’,8, 10, 90);

EXECUTE ad_dd.register_column(’FND’, ’CUST_FLEX_TEST’, APPLICATION_ID’ , 1, ’NUMBER’, 38, ’N’, ’N’);

EXECUTE ad_dd.register_column(’FND’, ’CUST_FLEX_TEST’,

’ID_FLEX_CODE’,2, ’VARCHAR2’, 30, ’N’, ’N’);

14. Difference between _all tables and without _all tables?

All _ALL tables are multi org partitioned tables and wihtout _ all tables are views.

15. What is org_id and Organization_id?

Org_Id means operating unit id and Organization_id means inventory organization id.

16. How many files will be created when you run the concurrent program(Request)?

When we ran the concurrent program it will create two files.

LOG File

OUT File

17. If you want to get the output in outputfile or logfile. How can you do it?And what are the parameters you are passing it?

We will use FND_FILE Package to get the output in output file or log File.

The FND_FILE package contains procedures to write text to log and output files. In Release 11i, these procedures are supported in all types of concurrent programs.

· FND_FILE.PUT

procedure FND_FILE.PUT

(which IN NUMBER,

buff IN VARCHAR2);

Use this procedure to write text to a file (without a new line character). Multiple calls to FND_FILE.PUT will produce concatenated text. Typically used with FND_FILE.NEW_LINE.

Arguments (input)

Which Log file or output file. Use either FND_FILE.LOG

or FND_FILE.OUTPUT.

buff Text to write.

· FND_FILE.PUT_LINE

Summary procedure FND_FILE.PUT_LINE

(which IN NUMBER,

buff IN VARCHAR2);

Description

Use this procedure to write a line of text to a file (followed by a new line character). You will use this utility most often.

Arguments (input)

Which Log file or output file. Use either FND_FILE.LOG

or FND_FILE.OUTPUT.

buff Text to write.

Example

Using Message Dictionary to retrieve a message already set up on the server and putting it in the log file (allows the log file to contain a translated message):

FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );

Putting a line of text in the log file directly (message cannot be translated because it is hardcoded in English; not recommended):

fnd_file.put_line(FND_FILE.LOG,’Warning: Employee ’||

l_log_employee_name||’ (’||

l_log_employee_num ||

’) does not have a manager.’);

· FND_FILE.NEW_LINE

procedure FND_FILE.NEW_LINE

(which IN NUMBER,

LINES IN NATURAL := 1);

Use this procedure to write line terminators (new line characters) to a file.

18. What are the different procedures used in UTL_FILE and Exceptions also?

UTL_FILE PROCEDURES

Subprogram	Description
FOPEN function	Opens a file for input or output with the default line size.
IS_OPEN function	Determines if a file handle refers to an open file.
FCLOSE procedure	Closes a file.
FCLOSE_ALL procedure	Closes all open file handles.
GET_LINE procedure	Reads a line of text from an open file.
PUT procedure	Writes a line to a file. This does not append a line terminator.
NEW_LINE procedure	Writes one or more OS-specific line terminators to a file.
PUT_LINE procedure	Writes a line to a file. This appends an OS-specific line terminator.
PUTF procedure	A PUT procedure with formatting.
FFLUSH procedure	Physically writes all pending output to a file.
FOPEN function	Opens a file with the maximum line size specified.

1. FOPEN function

This function opens a file. You can have a maximum of 50 files open simultaneously.

Syntax

UTL_FILE.FOPEN (

location IN VARCHAR2,

filename IN VARCHAR2,

open_mode IN VARCHAR2,

max_linesize IN BINARY_INTEGER)

RETURN file_type;

Exceptions

INVALID_PATH

INVALID_MODE

INVALID_OPERATION

2. IS_OPEN function

This function tests a file handle to see if it identifies an open file. IS_OPEN reports only whether a file handle represents a file that has been opened, but not yet closed. It does not guarantee that there will be no operating system errors when you attempt to use the file handle.

Syntax

UTL_FILE.IS_OPEN (

file IN FILE_TYPE)

RETURN BOOLEAN;

Exceptions

None

3. FCLOSE procedure

This procedure closes an open file identified by a file handle. If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERRORexception when closing a file.

Syntax

UTL_FILE.FCLOSE (

file IN OUT FILE_TYPE);

Exceptions

WRITE_ERROR

INVALID_FILEHANDLE

4. FCLOSE_ALL procedure

This procedure closes all open file handles for the session. This should be used as an emergency cleanup procedure, for example, when a PL/SQL program exits on an exception.

Note:

FCLOSE_ALL does not alter the state of the open file handles held by the user. This means that an IS_OPEN test on a file handle after an FCLOSE_ALL call still returnsTRUE, even though the file has been closed. No further read or write operations can be performed on a file that was open before an FCLOSE_ALL.

Syntax

UTL_FILE.FCLOSE_ALL;

EXCEPTIONS

WRITE_ERROR

5. GET_LINE procedure

This procedure reads a line of text from the open file identified by the file handle and places the text in the output buffer parameter. Text is read up to but not including the line terminator, or up to the end of the file.

If the line does not fit in the buffer, then a VALUE_ERROR exception is raised. If no text was read due to "end of file," then the NO_DATA_FOUND exception is raised.

Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.

The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN.

Syntax

UTL_FILE.GET_LINE (

file IN FILE_TYPE,

buffer OUT VARCHAR2);

      Exceptions

VALUE_ERROR

INVALID_FILEHANDLE

INVALID_OPERATION

READ_ERROR

NO_DATA_FOUND

6. PUT procedure

PUT writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. No line terminator is appended by PUT; use NEW_LINE to terminate the line or use PUT_LINE to write a complete line with a line terminator.

The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN.

Syntax

UTL_FILE.PUT (

file IN FILE_TYPE,

buffer IN VARCHAR2);

You must have opened the file using mode 'w' or mode 'a'; otherwise, anINVALID_OPERATION exception is raised.

Exceptions

INVALID_FILEHANDLE

INVALID_OPERATION

WRITE_ERROR

7. NEW_LINE procedure

This procedure writes one or more line terminators to the file identified by the input file handle. This procedure is separate from PUT because the line terminator is a platform-specific character or sequence of characters.

Syntax

UTL_FILE.NEW_LINE (

file IN FILE_TYPE,

lines IN NATURAL := 1);

Exceptions

INVALID_FILEHANDLE

INVALID_OPERATION

WRITE_ERROR

8. PUT_LINE procedure

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. PUT_LINEterminates the line with the platform-specific line terminator character or characters.

The maximum size for an output record is 1023 bytes, unless you specify a larger value using the overloaded version of FOPEN.

Syntax

UTL_FILE.PUT_LINE (

file IN FILE_TYPE,

buffer IN VARCHAR2);

Exceptions

INVALID_FILEHANDLE

INVALID_OPERATION

WRITE_ERROR

9. PUTF procedure

This procedure is a formatted PUT procedure. It works like a limited printf(). The format string can contain any text, but the character sequences '%s' and '\n' have special meaning.

%s Substitute this sequence with the string value of the next argument in the argument list.

\n Substitute with the appropriate platform-specific line terminator.

Syntax

UTL_FILE.PUTF (

file IN FILE_TYPE,

format IN VARCHAR2,

[arg1 IN VARCHAR2 DEFAULT NULL,

. . .

arg5 IN VARCHAR2 DEFAULT NULL]);

Exceptions

INVALID_FILEHANDLE

INVALID_OPERATION

WRITE_ERROR

UTL_FILE EXCEPTIONS

Exception Name	Description
INVALID_PATH	File location or filename was invalid.
INVALID_MODE	The open_mode parameter in FOPEN was invalid.
INVALID_FILEHANDLE	File handle was invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error.

19. Difference between Interface and Conversion?

Interface is schedule time process, conversion is only one time process

20. What is staging table?

Staging table is nothing but a temporary table,it is used to perform validations before transfering to interface tables.

21. What is the process for Interface?

Step 1:First create the staging tables and transfer the data from flat file to staging tables by using control file.

Step 2: Write one feeder program to perform validations and then transfer the data from staging table to Interface tables.

Step 3 :Then run the standard open interface program or use the API’s to transfer the data from interface tables to base tables.

22. I want to get the data from po_headers using TOAD. To get the data wht we have to do(setting).

Toget the data from po_headers, we should run the following script.

begin

dbms_application_info.set_client_info(204);

end;

23. How can you load data from flat file to Table?

By using control file , we can load data from flat file to table.

24. What are the different components used in the SQL*Loader?

SQL*Loader loads data from external files into tables in the Oracle database.

SQL*Loader primarily requires two files:

1.Datafile-contains the information to be loaded.

2.Control file-contains information on the format of the data, the records and fields within the file, the order in which they are to be loaded, and also the names of the

multiple files that will be used for data.

We can also combine the control file information into the datafile itself.

The two are usually separated to make it easier to reuse the control file.

When executed, SQL*Loader will automatically create a log file and a bad file.

The log file records the status of the load, such as the number of rows processed and the number of rows committed.

The bad file will contain all the rows that were rejected during the load due to data errors, such as nonunique values in primary key columns.

Within the control file, we can specify additional commands to govern the load criteria. If these criteria are not met by a row, the row will be written to a discardfile.

The control,log, bad, and discard files will have the extensions .ctl, .log, . bad, and .dsc, respectively.

25. What is Flexfiled?

A flexfield is a field made up of segments. Each segment has a name you or your end users assign, and a set of valid values.

There are two types of flexfields:

Key flexfields

Descriptive flexfields.

26. What is the use DFF, KFF and Range Flex Field?

A flexfield is a field made up of sub–fields, or segments. There are two types of flexfields: key flexfields and descriptive flexfields. A key flexfield appears on your form as a normal text field with an appropriate prompt. A descriptive flexfield appears on your form as a two–character–wide text field with square brackets [ ] as its prompt. When opened, both types of flexfield appear as a pop–up window that contains a separate field and prompt for each segment. Each segment has a name and a set of valid values. The values may also have value descriptions.

Most organizations use ”codes” made up of meaningful segments (intelligent keys) to identify general ledger accounts, part numbers, and other business entities. Each segment of the code can represent a characteristic of the entity. The Oracle Applications store these ”codes” in key flexfields. Key flexfields are flexible enough to let any organization use the code scheme they want, without programming.

Key flexfields appear on three different types of application form:

• Combinations form

• Foreign key form

• Range form

A Combinations form is a form whose only purpose is to maintain key flexfield combinations. The base table of the form is the actual combinations table. This table is the entity table for the object (a part, or an item, an accounting code, and so on).

A Foreign key form is a form whose underlying base table contains only one or two columns that contain key flexfield information, and those columns are foreign key columns to the combinations table.

A Range form displays a range flexfield, which is a special pop–up window that contains two complete sets of key flexfield segments. A range flexfield supports low and high values for each key segment rather than just single values. Ordinarily, a key flexfield range appears on your form as two adjacent flexfields, where the leftmost flexfield contains the low values for a range, and the rightmost flexfield contains the high values. A user would specify a range of low and high values in this pop–up window.

Descriptive flexfields provide customizable ”expansion space” on your forms. You can use descriptive flexfields to track additional information, important and unique to your business, that would not otherwise be captured by the form. Descriptive flexfields can be

context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

27. What is Dynamic Insertion and Cross-validation Rule?

Dynamic insertion is the insertion of a new valid combination into a combinations table from a form other than the combinations form. If you allow dynamic inserts when you set up your key flexfield, a user can enter a new combination of segment values using the flexfield window from a foreign key form. Assuming that the new combination satisfies any existing cross–validation rules, the flexfield inserts the new combination into the combinations table, even though the combinations table is not the underlying table for the foreign key form.

Cross–validation (also known as cross–segment validation) controls the combinations of values you can create when you enter values for key flexfields. A cross–validation rule defines whether a value of a particular segment can be combined with specific values of other segments. Cross–validation is different from segment validation, which controls the values you can enter for a particular segment.

28. What Key Flexfields are used by Oracle Applications?

The number of key flexfields in oracle application is significantly smaller than the number of descriptive flexfileds.

Oracle General Ledger Accountig

Oracle Asset: Asset

Category

Locaton

Oracle Inventory Account aliases

Item catalogs

Item catefories

Sales orders

Stok locators

System Items

Oracle Receivables Sales Tax Location

Terrotory

Oracle Payrole Bank Details

Cost allocation

People Group

Oracle Human Resources

Grade

Job

Personal Analysis

Position soft coded.

29. What are segment qualifier and flexfiled qualifier?

Some key flexfields use segment qualifiers to hold extra information about individual key segment values. A segment qualifier identifies a particular type of value in a single segment of a key flexfield. In the Oracle Applications, only the Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an ”identification tag” for a value. In the Accounting Flexfield, segment qualifiers can identify the account type for a natural account segment value, and determine whether detail posting or budgeting are allowed for a particular value.

A flexfield qualifier identifies a particular segment of a key flexfield. Usually an application needs some method of identifying a particular segment for some application purpose such as security or computations. However, since a key flexfield can becustomized so that segments appear in any order with any prompts, the application needs a mechanism other than the segment name or segment order to use for segment identification. Flexfield qualifiers serve this purpose.

Flexfield qualifier as something the whole flexfield uses to tag its pieces, and segment qualifier as something the segment uses to tag its values.

30. What are value sets ?

Value sets

When you first define your flexfields, you choose how many segments

you want to use and what order you want them to appear. You also

choose how you want to validate each of your segments. The decisions

you make affect how you define your value sets and your values.

You can share value sets among segments in different flexfields,

segments in different structures of the same flexfield, and even

segments within the same flexfield structure. You can share value sets

across key and descriptive flexfields. You can also use value sets for

report parameters for your reports that use the Standard Request

Submission feature.

You cannot change the validation type of an existing value set, since

your changes affect all flexfields and report parameters that use the

same value set.

None

You use a None type value set when you want to allow users to enter

any value so long as that value meets the value set formatting rules.

Independent

An Independent value set provides a predefined list of values for a

segment. These values can have an associated description. For

example, the value 01 could have a description of ”Company 01”. The

meaning of a value in this value set does not depend on the value of

any other segment. Independent values are stored in an Oracle

Application Object Library table. You define independent values using

an Oracle Applications window, Segment Values.

Table

A table–validated value set provides a predefined list of values like an

independent set, but its values are stored in an application table. You

define which table you want to use, along with a WHERE cause to limit

the values you want to use for your set. Typically, you use a

table–validated set when you have a table whose values are already

maintained in an application table (for example, a table of vendor

names maintained by a Define Vendors form). Table validation also

provides some advanced features such as allowing a segment to

depend upon multiple prior segments in the same structure.

You can use validation tables for flexfield segments or report

parameters whose values depend on the value in a prior segment. You

use flexfield validation tables with a special WHERE clause (and the

$FLEX$ argument) to create value sets where your segments depend on

prior segments. You can make your segments depend on more than

one segment, creating cascading dependencies. You can also use

validation tables with other special arguments to make your segments

depend on profile options or field values.

To implement a validation table:

1. Create or select a validation table in your database. You can use

any existing application table, view, or synonym as a validation

table.

2. Register your table with Oracle Application Object Library (as a

table). You may use a non–registered table for your value set,

however. If your table has not been registered, you must then enter

all your validation table information in this region without using

defaults.

3. Create the necessary grants and synonyms.

4. Define a value set that uses your validation table

5. Define your flexfield structure to use that value set for a segment.

Example of $FLEX$ Syntax

Here is an example of using :$FLEX$.Value_Set_Name to set up value

sets where one segment depends on a prior segment that itself depends

on a prior segment (”cascading dependencies”). Assume you have a

three–segment flexfield where the first segment is car manufacturer, the

second segment is car model, and the third segment is car color. You

could limit your third segment’s values to only include car colors that

are available for the car specified in the first two segments. Your three

value sets might be defined as follows:

Segment Name Manufacturer

Value Set Name Car_Maker_Name_Value_Set

Validation Table CAR_MAKERS

Value Column MANUFACTURER_NAME

Description Column MANUFACTURER_DESCRIPTION

Hidden ID Column MANUFACTURER_ID

SQL Where Clause (none)

Segment Name Model

Value Set Name Car_Model_Name_Value_Set

Validation Table CAR_MODELS

Value Column MODEL_NAME

Description Column MODEL_DESCRIPTION

Hidden ID Column MODEL_ID

SQL Where Clause WHERE MANUFACTURER_ID =

:$FLEX$.Car_Maker_Name_Value_Set

Dependent

A dependent value set is similar to an independent value set, except

that the available values in the list and the meaning of a given value

depend on which independent value was selected in a prior segment of

the flexfield structure. You can think of a dependent value set as a

collection of little value sets, with one little set for each independent

value in the corresponding independent value set. You must define

your independent value set before you define the dependent value set

that depends on it. You define dependent values in the Segment Values

windows, and your values are stored in an Oracle Application Object

Library table.

Special and Pair Value Sets

Special and pair value sets provide a mechanism to allow a

”flexfield–within–a–flexfield”. These value sets are primarily used for

Standard Request Submission parameters. You do not generally use

these value sets for normal flexfield segments.

Special and Pair value sets use special validation routines you define.

For example, you can define validation routines to provide another

flexfield as a value set for a single segment or to provide a range

flexfield as a value set for a pair of segments.

Translatable Independent and Translatable Dependent

A Translatable Independent value set is similar to Independent value

set in that it provides a predefined list of values for a segment.

However, a translated value can be used.

A Translatable Dependent value set is similar to Dependent value set in

that the available values in the list and the meaning of a given value

depend on which independent value was selected in a prior segment of

the flexfield structure. However, a translated value can be used.

You cannot create hierarchies or rollup groups with Translatable

Independent or Translatable Dependent value sets.

Note: The Accounting Flexfield does not support Translatable

Independent and Translatable Dependent value sets.

31.List out some of the fnd_tables?

Ans.

FND_APPLICATION – APPLSYS

FND_CONCURRENT_PROGRAMS

FND_CONCURRENT_PROCESSES

FND_RESPONSIBILITY

FND_PRODUCT_GROUPS

32.What are the tables involved in Flexfileds?

FND_ID_FLEXS

FND_ID_FLEX_SEGMENTS

FND_ID_FLEX_STRUCTURES

FND_DESCRIPTIVE_FLEXS

1. How to attach reports in Oracle Applications ?

Ans: The steps are as follows :

· Design your report.

· Generate the executable file of the report.

· Move the executable as well as source file to the appropriate product’s folder.

· Register the report as concurrent executable.

· Define the concurrent program for the executable registered.

· Add the concurrent program to the request group of the responsibility.

·

2. What are different report triggers and what is their firing sequence ?

Ans. : There are five report trigger :

· Before Report

· After Report

· Before Parameter Form

· After Parameter Form

· Between Pages

The Firing sequence for report triggers is

Before Parameter Form – After Parameter Form – Before Report – Between Pages – After Report.

33. What is the use of cursors in PL/SQL ? What is REF Cursor ?

Ans. : The cursor are used to handle multiple row query in PL/SQL. Oracle uses implicit cursors to handle all it’s queries. Oracle uses unnamed memory spaces to store data used in implicit cursors, with REF cursors you can define a cursor variable which will point to that memory space and can be used like pointers in our 3GLs.

34. What is record group ?

Ans: Record group are used with LOVs to hold sql query for your list of values. The record group can contain static data as well it can access data from database tables thru sql queries.

35. What is a FlexField ? What are Descriptive and Key Flexfields?

Ans: An Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization.

36. What are Autonomous transactions ? Give a scenario where you have used Autonomous transaction in your reports ?

Ans: An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.

More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use.

Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION.

Scenario : You can use autonomous transaction in your report for writing error messages in your database tables.

37. What is the use of triggers in Forms ?

Ans : Triggers are used in forms for event handling. You can write PL/SQL code in triggers to respond to a particular event occurred in your forms like when user presses a button or when he commits the form.

The different type of triggers available in forms are :

· Key-triggers

· Navigational-triggers

· Transaction-triggers

· Message-triggers

· Error-triggers

· Query based-triggers

38. What is the use of Temp tables in Interface programs ?

Ans : Temporary tables are used in Interface programs to hold the intermediate data. The data is loaded into temporary tables first and then, after validating through the PL/SQL programs, the data is loaded into the interface tables.

39. What are the steps to register concurrent programs in Apps?

Ans : The steps to register concurrent programs in apps are as follows :

· Register the program as concurrent executable.

· Define the concurrent program for the executable registered.

· Add the concurrent program to the request group of the responsibility

40. How to pass parameters to a report? Do you have to register them with AOL ?

Ans: You can define parameters in the define concurrent program form. There is no need to register the parameters with AOL. But you may have to register the value sets for those parameters.

41. Do you have to register feeder programs of interface to AOL ?

Ans : Yes ! you have to register the feeder programs as concurrent programs to Apps.

42. What are forms customization steps ?

Ans: The steps are as follows :

· Copy the template.fmb and Appstand.fmb from AU_TOP/forms/us.Put it in custom directory. The libraries (FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA, JL, VERT) are automatically attached .

· Create or open new Forms. Then customize.

· Save this Form in Corresponding Modules.

43. How to use Flexfields in reports?

Ans : There are two ways to use Flexfields in report. One way is to use the views (table name + ‘_KFV’ or ’_DFV’) created by apps, and use the concatenated_segments column which holds the concatenated segments of the key or descriptive flexfields.

Or the other way is to use the FND user exits provided by oracle applications.

44. What is Key and Descriptive Flexfield.

Ans : Key Flexfield: #unique identifier, storing key information

# Used for entering and displaying key information.

For example Oracle General uses a key Flexfield called Accounting

Flexfield to uniquely identifies a general account.

Descriptive Flexfield: # To Capture additional information.

# to provide expansion space on your form

With the help of [] . [] Represents descriptive

Flexfield.

45. Difference between Key and Descriptive Flexfield?

Ans :

Key Flexfield	Descriptive Flefield
1. Unique Identifier	1.To capture extra information
2. Key Flexfield are stored in segment	2.Stored in attributes
3.For key flexfield there are flexfield Qualifier and segment Qualifier	3. Context-sensitive flexfield is a feature of DFF. (descriptive flexfield)

What is SQL*Loader and what is it used for?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

How does one use the SQL*Loader utility?

One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:

sqlldr scott/tiger control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:

load data

infile 'c:\data\mydata.csv'

into table emp

fields terminated by "," optionally enclosed by '"'

( empno, empname, sal, deptno )

The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.

load data

infile *

replace

into table departments

( dept position (02:05) char(4),

deptname position (08:27) char(20)

)

begindata

COSC COMPUTER SCIENCE

ENGL ENGLISH LITERATURE

MATH MATHEMATICS

POLY POLITICAL SCIENCE

Is there a SQL*Unloader to download data to a flat file?

Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool oradata.txt

select col1 || ',' || col2 || ',' || col3

from tab1

where col2 = 'XYZ';

spool off

Alternatively use the UTL_FILE PL/SQL package:

rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter

declare

fp utl_file.file_type;

begin

fp := utl_file.fopen('c:\oradata','tab1.txt','w');

utl_file.putf(fp, '%s, %s\n', 'TextField', 55);

utl_file.fclose(fp);

end;

/

You might also want to investigate third party tools like SQLWays from Ispirer Systems, TOAD from Quest, or ManageIT Fast Unloader from CA to help you unload data from Oracle.

Can one load variable and fix length data records?

Yes, look at the following control file examples. In the first we will load delimited data (variable length):

LOAD DATA

INFILE *

INTO TABLE load_delimited_data

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

( data1,

data2

)

BEGINDATA

11111,AAAAAAAAAA

22222,"A,B,C,D,"

If you need to load positional data (fixed length), look at the following control file example:

LOAD DATA

INFILE *

INTO TABLE load_positional_data

( data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Can one skip header records load while loading?

Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example:

LOAD DATA

INFILE *

INTO TABLE load_positional_data

SKIP 5

( data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Can one modify data as it loads into the database?

Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.

LOAD DATA

INFILE *

INTO TABLE modified_data

( rec_no "my_db_sequence.nextval",

region CONSTANT '31',

time_loaded "to_char(SYSDATE, 'HH24:MI')",

data1 POSITION(1:5) ":data1/100",

data2 POSITION(6:15) "upper(:data2)",

data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"

)

BEGINDATA

11111AAAAAAAAAA991201

22222BBBBBBBBBB990112

LOAD DATA

INFILE 'mail_orders.txt'

BADFILE 'bad_orders.txt'

APPEND

INTO TABLE mailing_list

FIELDS TERMINATED BY ","

( addr,

city,

state,

zipcode,

mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",

mailing_city "decode(:mailing_city, null, :city, :mailing_city)",

mailing_state

)

Can one load data into multiple tables at once?

Look at the following control file:

LOAD DATA

INFILE *

REPLACE

INTO TABLE emp

WHEN empno != ' '

( empno POSITION(1:4) INTEGER EXTERNAL,

ename POSITION(6:15) CHAR,

deptno POSITION(17:18) CHAR,

mgr POSITION(20:23) INTEGER EXTERNAL

)

INTO TABLE proj

WHEN projno != ' '

( projno POSITION(25:27) INTEGER EXTERNAL,

empno POSITION(1:4) INTEGER EXTERNAL

)

Can one selectively load only the records that one need?

Look at this example, (01) is the first character, (30:37) are characters 30 to 37:

LOAD DATA

INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'

APPEND

INTO TABLE my_selective_table

WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'

(

region CONSTANT '31',

service_key POSITION(01:11) INTEGER EXTERNAL,

call_b_no POSITION(12:29) CHAR

)

Can one skip certain columns while loading data?

One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:

LOAD DATA

TRUNCATE INTO TABLE T1

FIELDS TERMINATED BY ','

( field1,

field2 FILLER,

field3

)

How does one load multi-line records?

One can create one logical record from multiple physical records using one of the following two clauses:

· CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.

· CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

How can get SQL*Loader to COMMIT only at the end of the load file?

One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.

Can one improve the performance of SQL*Loader?

A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.

Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.

Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.

Run multiple load jobs concurrently.

How does one use SQL*Loader to load images, sound clips and documents?

SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOGFILE. The LOBFILE method provides and easy way to load documents, images and audio clips into BLOB and CLOB columns. Look at this example:

Given the following table:

CREATE TABLE image_table (

image_id NUMBER(5),

file_name VARCHAR2(30),

image_data BLOB);

Control File:

LOAD DATA

INFILE *

INTO TABLE image_table

REPLACE

FIELDS TERMINATED BY ','

(

image_id INTEGER(5),

file_name CHAR(30),

image_data LOBFILE (file_name) TERMINATED BY EOF

)

BEGINDATA

001,image1.gif

002,image2.jpg

What is the difference between the conventional and direct path loader?

The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.

In Oracle Apps Reports the commonly used USER EXITS are :-

FND SRWINIT

FND SRWINIT sets your profile option values and allows Oracle Application Object Library user exits to detect that they have been called by a Oracle Reports program.

FND SRWEXIT

FND SRWEXIT ensures that all the memory allocated for Application Object Library user exits has been freed up properly.

Note: To use FND_SRWINIT and FND_SRWEXIT create a lexical parameter P_CONC_REQUEST_ID with the datatype Number. The concurrent manager passes the concurrent request ID to the report using this parameter.Then Call FND SRWINIT in the "Before Report Trigger." and FND SRWEXIT in the "After Report Trigger."

FND_GETPROFILE

These user exits let you retrieve and change the value of a profile option.

FND_FLEXSQL

Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype

FND_FORMAT_CURRENCY

This user exit formats the currency amount dynamically depending upon the precision of the actual currency value, the standard precision, whether the value is in a mixed currency region, the user's positive and negative format profile options,and the location (country) of the site. The location of the site determines the thousands separator and radix to use when displaying currency values.

Questions asked in Oracle Corp & USIT & GE.

1.How will you attach reports in Apps?

A1. create executable,(concurrent-> program-> executable)

define program(concurrent,program,define)

create request group (security>responsibility>request group)

(type = program,name = custom application)

add request group in responsibility(security> responsibility> define)

link your value set to the program

2.How will you attach forms in Apps.

appl developer > application > form

create function ( sy Adm/ or app developer > application > function)

3.What is use of Token in Reports

4.What are various Execution method in reports.

(Host, immediate,java stored procedures,java concurrent procedures,

pl/sql stored procedures,multilanguage functions, oracle report,Oracle report stage function)

[1] [2] [3] [4] [5]Spawned	Your concurrent program is a stand-alone program in C or Pro*C.[6] [7]
Host	Your concurrent program is written in a script for your operating system.[8] [9]
Immediate	Your concurrent program is a subroutine written in C or Pro*C. Immediate programs are linked in with your concurrent manage and must be included in the manager's program library.[10] [11]
Oracle Reports	Your concurrent program is an Oracle Reports script.[12] [13]
PL/SQL Stored Procedure	Your concurrent program is a stored procedure written in PL/SQL.[14][15]
Java Stored Procedure	Your concurrent program is a Java stored procedure.[16] [17]
Java Concurrent Program	Your concurrent program is a program written in Java.[18] [19]
Multi Language Function	A multi-language support function (MLS function) is a function that supports running concurrent programs in multiple languages. You should not choose a multi-language function in the Executable: Name field. If you have an MLS function for your program (in addition to an appropriate concurrent program executable), you specify it in the MLS Function field.[20] [21]
SQL*Loader	Your concurrent program is a SQL*Loader program.[22] [23]
SQL*Plus	Your concurrent program is a SQL*Plus or PL/SQL script.[24] [25]
Request Set Stage Function	PL/SQL Stored Function that can be used to calculate the completion statuses of request set stages.[26] [27]

5.How will you get Set of Books Id Dynamically in reports.

Using user exits

6.How will you Capture AFF in reports.

Using user exits ( fnd flexsql and fnd flexidval)

7.What is dynamic insertions.

When enabled u can add new segments in existing FF .

8.Whats is Code Comination ID.

To idendify a particular FF stored in GL_CODECOMBINATION

9.CUSTOM.PLL. various event in CUStom,pll.

New form instance ,new block instance , new item instance,

new item instance, new record instance, when validate record

10.When u defined Concurrent Program u defined incompatibilities what is Meaning of incompatibilities

??

[28] [29] [30] [31] [32] Identify programs that should not run simultaneously with your concurrent program because they might interfere with its execution. You can specify your program as being incompatible with itself.[33]

11.What is hirerachy of multi_org..

BusinessGroup

Legal Entity/Chart of Accounting

Opertaing Unit

Inventory Organization

SubInventory Organization

Loactor

R/R/B

12.What is difference between org_id and organization id.

org_id is operating unit and organization id is operating unit as well as inventoryOrganization

13.What is Profile options.

By which application setting can be modified .

14.Value set. And Validation types.

Value set are Lovs (long list )

None,dependent,indepndent,table,special ,pair,

15.What is Flexfield Qualifier.

To match the segments (natural,balancing,intercompay, cost center)

16.What is your structure of AFF.

Eg :Company :department: accconts: sub accounts : products

17.What is flexfield . Difference between KFF and DFF.

Flexfield is a flexible data field that your organization can customize your application needs without progamming.

KFF- comination of values called segments which represent key information of a business: (part no :- P 343-485748-549875, account no )

DFF- field to get the additional information ( email address)

18.How will u enable DFF.

Flexfield>Descriptive>Segments

Goto segment button, there uncheck the checkbox.

And switch from current resposibility.

19.How many segments are in Accounting Flexfields.

Max 30 min 2

20.What is user exits.

-------Pro C progams called from apps

A user exit is a program that you write and then link into the Report Builder executable or user exit DLL files. You build user exits when you want to pass control from Report Builder to a program you have written, which performs some function, and then returns control to Report Builder.

You can write the following types of user exits:

n ORACLE Precompiler user exits

n OCI (ORACLE Call Interface) user exits

n non-ORACLE user exits

You can also write a user exit that combines both the ORACLE Precompiler

interface and the OCI. User exits can perform the following tasks:

21.When u defined Concurrent Program there is one Checkbox use in SRS what is meaning of this. Suppose I do now want to call report through SRS How I ll call report then.

Ans : SRS – Standard Request Submission

[34] [35] [36] [37] [38] Check this box to indicate that users can submit a request to run this program from a Standard Request Submission window.[39]

[40] [41] If you check this box, you must register your program parameters, if any, in theParameters window accessed from the button at the bottom of this window.

[42]

22.What are REPORT Trigger. What are their Firing Sequences.

23.What is difference between Request Group and Data Group.

Request Group : Collection of concurrent program.

Data Group :

24.What is CUSTOM _TOP.

Ans : Top level directory for customized files.

25.What is meaning of $FLEX$ Dollar.

And : It is used to fetch values from one value set to another.

26.How will you registered SQL LOADER in apps.

Ans : Res - Sysadmin

Concurrent > Program > Executable

(Execution Method = 'SQL *LOADER')

27.What is difference between Formula Column, Placeholder Column and Summary Column?

28.What is difference between bind Variable and Lexical variable?

29.What is Starting point for developing a form in APPS.

Ans : Use temalate.fmb

30.Syntax of SQL Loader.

Sqlldr control = file.ctl

31.Where Control file of SQL Loader Placed.

32.Where the TEMPLATE.FMB resides and all PLL files stored.

33.What is diff between function and Procedures?

Ans :

34.Where the Query is written in Oracle Reports.

Ans : Data Model

35.How will you Print Conditionally Printing in Layout.

Using format trigger.

36.How will u get the Report from server

37.Whats is methodology for designing a Interface.

38.Question on Various Interface like GL_INTERFACE, Payable invoice import,

Customer Inteface, AUTOLOCKBOX, AUTOINBVOICE.

Ans : Autolockbox : Box of the organization which is kept in bank

will keep the track of all the cheques and will give the file. With

autolock updates the organization account.

Autoinvoice : Invoice generated after shipping automatically.

39.What are interface table in GL ,AP and AR Interfaces.

40. What are different database Trigger.

41. Whats are various built in FORMS.

42. Whats is set of Books. How wiil u assign set of books to responsibility.

Ans :[43] [44] [45] [46] A set of books determines the functional currency, account structure, and

accounting calendar for each organization or group of organizations.[47]

43. What is FSG reports.

Ans : Financial Statement Generator.

It is a powerful and flexible tool available with GL, you can use to biuld

your custom reports without programming.

44. how will u register custom table in APPS>

Ans : Using package AD_DD.Register_Table(application_shortname, tablename,

table_type, next_extent, pct_increase, pct_used);

45.How will u register custom table's columns in apps?

Ans : Ad_dd.register_column(Application_name, table_name, column_name,

data_type, length);

46.Which version of 11i u r working Persently.

11.5.9