ORACLE Applications 11i Q& A
How can you tell your application is multi-org enabled?(Table & Column)
select multi_org_flag from fnd_product_groups is 'Y'
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.
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.
What components are attached to responsibility?
Menu
Data Group
Request Group
What is the version of database for oracle applications11i?
Present we are using RDBMS version 9.2.0.3.0
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.
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.
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.
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.
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.
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
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
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
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’);
Difference between _all tables and without _all tables?
All _ALL tables are multi org partitioned tables and wihtout _ all tables are views.
What is org_id and Organization_id?
Org_Id means operating unit id and Organization_id means inventory organization id.
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
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.
FOPEN function
This function opens a file. You can have a maximum of 50 files open s imultaneously.
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
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 byPUT; 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
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
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.
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.
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.
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.
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.
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
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.
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
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.
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.
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.
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.
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.
Difference between Interface and Conversion?
Interface is schedule time process, conversion is only one time process
What is staging table?
Staging table is nothing but a temporary table,it is used to perform validations before transfering to interface tables.
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.
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;
How can you load data from flat file to Table?
By using control file , we can load data from flat file to table.
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 discard file.
The control,log, bad, and discard files will have the extensions .ctl, .log, . bad, and .dsc, respectively.
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.
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.
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.
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 be customized 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.
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
How can you tell your application is multi-org enabled?(Table & Column)
select multi_org_flag from fnd_product_groups is 'Y'
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.
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.
What components are attached to responsibility?
Menu
Data Group
Request Group
What is the version of database for oracle applications11i?
Present we are using RDBMS version 9.2.0.3.0
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.
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.
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.
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.
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.
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
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
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
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’);
Difference between _all tables and without _all tables?
All _ALL tables are multi org partitioned tables and wihtout _ all tables are views.
What is org_id and Organization_id?
Org_Id means operating unit id and Organization_id means inventory organization id.
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
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.
FOPEN function
This function opens a file. You can have a maximum of 50 files open s imultaneously.
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
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 byPUT; 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
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
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.
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.
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.
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.
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.
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
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.
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
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.
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.
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.
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.
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.
Difference between Interface and Conversion?
Interface is schedule time process, conversion is only one time process
What is staging table?
Staging table is nothing but a temporary table,it is used to perform validations before transfering to interface tables.
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.
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;
How can you load data from flat file to Table?
By using control file , we can load data from flat file to table.
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 discard file.
The control,log, bad, and discard files will have the extensions .ctl, .log, . bad, and .dsc, respectively.
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.
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.
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.
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 be customized 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.
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
No comments:
Post a Comment