Oracle Applications Global Accounting Engine User Guide
Use the Global Accounting Engine to replace the transfer to General Ledger and create subledger accounting entries that meet additional statutory standards in some countries. The Accounting Engine
provides subledger balances, legal reports, and bi-directional drilldown from General Ledger to the subledger transaction.
Just check whether invoice transaction flexfield is stored in reference column to drill down the AR transaction from GL after record is transferred to GL.
Choose Line Item to generate a report showing REFERENCE_1 from the GL_JE_LINES table. Choose Source Item to generate a report showing REFERENCE_4 from the GL_JE_LINES table. These references appear only if you used Journal Import and included a journal voucher number, an invoice date and number, or some other source document information that helps you to identify the origin of this journal entry.
1. What is overlay in payables
Ans: Over lay is used in payable open interface where we would like to pass and overwrite certain value which is set to appear by default when data is inserted into the production tables.
2. What is rollup group
Ans: rollup group we define and attached with the parent segment in the accounting flexfield for summary total for which summary template is defined. This identify at what level and how the summing up should be computed for the level.
3. What way payment batch is different from other module batches.
Ans: It identifies the invoices for the payment automatically based on the criteria we specify in payment batch for the payments.
4. What is balancing segment in AR.
Ans: The Account Generator ensures that Receivables substitutes the correct balancing segment values during various accounting activities against transactions and receipts.
Receivables uses the Account Generator to update the balancing segment values during various accounting activities against transactions and receipts. By matching the balancing segments for different accounting activities back to the original transaction or receipt, the Account Generator ensures that Receivables uses the correct balancing segment values during this substitution process. For example, if an invoice’s balancing segment that you assess finance charges for has a value of ’01’ and the balancing segment of your finance charges account is ’02’, when Receivables accrues finance charges for this invoice, the Account Generator automatically changes the balancing segment of the finance charges account to ’01’. The Account Generator in Receivables utilizes Oracle Workflow
5. What difficulty you have faced while designing flexfields. What all need to be considered.
6. What is deposit in AR? Have you used it?
Ans: It is a commitment type of transaction where in we take deposit from the customer and get into the agreement that we will make supply of certain goods and services for certain period of time.
1. What is flexfield qualifier in accounting flexfield.
Ans: Balancing Segment, Cost Center Segment, Natural Account Segment & Inter-company segment.
2. Can we use fixed assets code as Cost center.
3. Which SRS is used for AR To GL Interface.
ANS: General ledger option is given in the menu option interfaces in AR.
4. What are required setup for AP.
Ans: Required setup in AP is as under
1. Install or upgrade payables
2. Select primary set of books
3. Use the system administrator responsibility to assign your set of books to a responsibility. (Profile Option)
4. Define financials options.
5. Define payables options.
6. Define payment terms.
7. Define banks, bank transmission details and bank accounts
8. Open payable accounting period
9. Set up Print Styles and Drivers for the supplier Mailing labels report
5. What are required setup for GL.
Ans: Required setup in GL is as under
1. Source (Required step with defaults)
2. Category (Required step with defaults)
3. System Control (Required step with defaults)
4. Profile Options (required)
5. Open Close Accounting Periods (required)
Before that set of books setup needs following:
1. Chart of Accounts (Accounting Flexfields) - (Required)
2. Define Period Types (Required step with defaults)
3. Define Calendar (Required)
4. Currency (Required step with defaults)
5. Set Of Book (Required)
6. Assign set of books to a Responsibility in Profile Option(Required)
7. Daily Conversion Rate Type (Required step with defaults)
6. How you will print user name of the person logged on to see the report.
ANS: SELECT FND_GLOBAL.USER_ID FROM DUAL; STORE IT IN A VARIABLE AND FIND IT IN THE FND_USER
select user_name from fnd_user where user_id = (select fnd_global.user_id from dual)
7. What all interfaces have you done.
8. What is the table name for GL Interface. From here data goes to which tables.
ANS: GL_INTERFACE Table Updates GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES.
9. Can we insert journal name in the GL Interface table
Ans: There is no such column in GL interface table hence we cannot enter it.
10. Where Reference fields are found in GL production tables. What is its use.
Ans: Reference fields are found in GL_JE_LINES it is used to store reference of sub-ledger enables us to drill down from gl to subledger.
11. Where journal name will be stored in GL_INTERFACE TABLE, Name the column.
ANS: REFERENCE4 (Journal Entry name will go in this field)
12. How to set dependent and independent value set and how you will insert values for the segments having these value sets.
ANS: First define independent value set and then while defining dependent value set give reference of independent value set along with default value and description. At time of entering values, enter values for independent first and then while entering values for dependent it will first force you to select value of independent segment.
13. SRS Name for AP TO GL.
ANS: Payable Transfer to General Ledger.
14. Can you delete the records after the interface has uploaded the records in the GL interface table?
ANS: Yes we can delete and correct the records from the front end after importing data into GL_INTERFACE Table. Sub menu options as correct and delete are given under import menu option. We have to specify source name. Menu option is import under journal in GL.
15. Required parameter for PL/SQL Procedure registered in Oracle. What will happen if these are not included?
ANS: Retcode and Errbuf are two out parameters having varchar2 datatype that are required. Use errbuf to return any error messages, and retcode to return completion status. The parameter retcode returns 0 for success, 1 for success with warnings, and 2 for error. After your concurrent program runs, the concurrent manager writes the contents of both errbuf and retcode to the log file associated with your concurrent request. If we do not include these two parameters, it will give run time error.
16. How to judge the number of descriptive fields defined from the front end itself.
ANS: In front end we will find  open close square bracket which indicate the presence of descriptive flexfield. In other words, dff appears on form as a single-character, unnamed field enclosed in brackets
17. What is context field is all about.
ANS: Context field is used to make descriptive flexfield segments context sensitive, so that segment that may or may not appear depending upon what other information is present in your form
18. What is use of custom.pll what triggers are fired to support the customization you do using custom.pll
SPECIALn (1 to 45)
KEY-fn (1 to 8)
19. What are various customer interface tables?
20. Where invoices gets stored in payables
21. Whether ap_invoices_all and ap_invoice_lines_all are tables or views
Ans: these are tables.
22. What will happen if you will type select * from ap_invoices in the multi org setup.
Ans: No row selected because it is a view having where condition for ord_id to match with operating unit context that are extracted from client_info global variable at application run time. From sql prompt run Fnd_client_info.setup.org_context(org_id). It will set the operating unit context or run fnd_clinet_info.setup_client_info(resp_appl_id,resp_id,user_id, security_group_id)
23. Why it does not show records and show records for ap_invoices_all where as both the table has got org_id columns.
Ans: Because operating unit context is not set that is why ord_id can not retrieved to meet the where condition specified in the view. Set it up with followings,
24. What mechanism or logic oracle apps have applied to drill down the records in AP from the GL module after the records are transferred from AP to GL.
ANS: Uses reference column (1-8) to store information about sub-ledger.
25. What all are the standard API in Oracle apps.
26. What is auto accounting?
ANS: It is a required setup before to enter any transaction in AR. We have to define code combinations for different transaction type such as revenue, receivables, bills receivables, charge back, deposit and guarantee to default
27. What is project management?
28. What is major risk you find in Project? How to mitigate it.
ANS: Resource is a major risk in oracle apps projects. To mitigate it we need to have resource in reserve right from the beginning of the project.
29. What are CR.010 all about?
ANS: It is a Project Management Plan Document.
30. How you maintain time sheet.
ANS: Time sheet is maintained in WM.020 In MS Project format.
1. WHICH INTEREFACES U HAVE DONE IN VARIOUS ORACLE FINANCIALS
ANS: AR Auto invoice interface, AP open interface
Customer_interface using API (TCA)
Bank statement (Bank Reconciliation of Cash Management)
2. TELL GENERAL STEPS OF OPEN INTERAFCE
ANS: GL Interface
1. Create on table as stage table with columns varchar2 (50) in respect to the flat file given having information to be updated in Oracle Application tables.
2. Stage table was created in custom schema and created synonyms in apps schema.
3. Created control file with stream type where in file is mentioned of flat file name.
4. Insertion of records into stage table is done through SQL LOADER option through Concurrent Program. Mention control file name while defining executable, select SQL LOADER as execution method.
5. After successful insertion of records from flat file to stage table.
6. Validate data for values and data type through PL/SQL program then the data is loaded into the interface table.
7. Now go to the import option of Journal menu option in GL Responsibility.
3. WHAT VALIDATIONS U HAVE TO MAKE IN VARIOUS INTERFACE, TELL SOME
ANS: Currency, Date, DFF CONTEXT, .
4. WHAT IS DIFFERENCE BETWEEN IN IMPORT & POST
ANS: Import does import of information from an external system and create transactions in any of the Oracle application product. Where as POST is a posting of the entered transaction in the system to freeze the transaction so that it can be considered for final reporting.
In AR If you use AutoInvoice to import information from an external system and
create transactions in Oracle Receivables,
POST means it changes the status from unposted to posted for the entered
Transaction. Table does not change only the status gets updated from ‘U’ to
5. WHAT IS KFF & DFF
ANS: KFF: A flexfield is a field made up of sub-fields, or segments. Each
segment has a name and a set of valid values. The values may also have value
descriptions. Key flexfields are flexible enough to let any organization use the
code scheme without programming.
In other words, Flexfield is a “intelligent fields” that are fields comprised of
one or more segments, where each segment has both a value and a meaning.
DFF: 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. A descriptive flexfield appears on a form as a single-character,
unnamed field enclosed in brackets. Each field or segment in a descriptive
flexfield has a prompt, just like ordinary fields, and can have a set of valid
values. Your organization can define dependencies among the segments or
customize a descriptive flexfield to display context-sensitive segments, so that
different segments or additional pop-up windows appear depending on the
values you enter in other fields or segments.
6. WHAT IS VALUE SETS
ANS: Set of valid values defined for flexfield segment and SRS report arguments. Value Set is used when we define flexfield segment for Key Flexfield and descriptive flexfield segment window. Also for reports that are run from the SRS uses values sets for report arguments.
7. WHAT IS TABLE TYPE VALUE SET.
ANS: VALUE SET where we selecting validation type as table refer table that are registered oracle application table for values to be populated for your parameter. We can use multiple table separating them by ‘,’.
8. DIFF IN VALUE SET & REQUEST SET
ANS: Value set is a Value having list type, security type, format type, validation and size/width used for segments definition and as attribute for reports extraction through SRS where as REQUEST SET is a REQUEST have more than one Stage and each stage have more than one request program.
9. WHAT IS REQUEST SETS
ANS: A request set is a collection of reports and/or programs that you group
together. You can submit the reports and/or programs in a request set all at once
using a single transaction. (REQUEST SET is a set has more than one or more
Stages and each stage have more than one or more request program. Stages
can further be linked to each other).
10. TELL IN BRIEF FOR SEGMENT & FLEXFIELD QUALIFIERS
ANS: Flexfield qualifier: Oracle Applications products use flexfield qualifiers to identify certain segments used for specific purposes.
Some qualifiers must be unique, and you cannot compile your flexfield if you apply that qualifier to two or more segments. Other qualifiers are required, and you cannot compile your flexfield until you apply that qualifier to at least one segment
Segment Qualifier: Some key flexfields use segment qualifiers to hold extra information about individual key segment values. For example, the Accounting Flexfield uses segment qualifiers to determine the account type of an account value or whether detail budgeting and detail posting are allowed for an Accounting Flexfield combination containing a given value.
The Allow Budgeting, Allow Posting, and Account Type fields are segment qualifiers for the Accounting Flexfield
11. WHAT ARE DATA-BASE TRIGGERS
12. WHAT IS DIFFERENCE IN PACKAGE, PROCEDURE, TRIGGER
ANS: PROCEDURE: Procedure is a program units used to store PL/SQL block
under an assigned name so that it can be called repeatedly. It may and may not
return values using out prarameters. It is invoked by its name. It cannot be
called from the SQL statement. Return parameter is not mandatory. In case of
Function Return value is must
PACKAGE is a program unit have several procedures and functions that are
logically related together. It also has PL/SQL constructs such as cursors,
variables, constants and exceptions. Packages brings these various constructs
together in a single program unit. It is invoked by its name. It can be called from
With in any other program unit.
Packages A method of encapsulating and storing related procedures, functions,
and other package constructs together as a unit in the database. While packages
provide the database administrator or application developer organizational benefits,
they also offer increased functionality and database performance.
TRIGGER: Triggers is also a program unit. It is invoked by the event such as
insert, update and delete. It cannot be called from any other program unit.
Trigger can be associated with table, views or system level events.
TCL is not allowed within triggers (COMMIT, SAVEPOINT Or ROLLBACK).
DCL is not allowed within triggers (GRANT & REVOKE).
We cannot use parameter in Triggers.
13. TYPES OF TRIGGERS
a. REPORTS TRIGGERS ARE
i. Before parameter form
ii. After parameter form
iii. Before report
iv. Between pages
v. After report
b. Table /View Triggers
i. Before/After Insert/update/Delete (DML)
c. System Event Triggers
i. DDL on schema and database (DDL) Create/Alter/Drop
d. Form Triggers
Different types of form triggers:
1. Key triggers
2. Navigational Triggers
3. Transactional Triggers
4. Message Triggers
5. Error Triggers
6. Query Based Triggers
Main Events are as under:
xiii. Post-Text-Item called
xix. Value of Radio Button
xxii. Value in List Item PUN
xxiv. Push Button pressed...
14. TYPES OF EXCEPTION:
ANS: USER DEFINED & SYSTEM DEFINED
An error condition or warning in PLSQL is referred as exception. Can be internally defined or user defined.
· No need of multiple checks for issued statements
· Easy to trap the errors
· Improves the readability
a. ACCESS INTO NULL ----> PROGRAM ATTEMPTS TO ASSIGN VALUES TO THE ATTRIBUTES OF AN UNINITIALIZED OBJECT
b. CASE NOT FOUND ---> NONE OF THE CHOICES IN THE WHEN CLAUSE OF CASE ARE FOUND AND THERE IS NO ELSE CLAUSE
c. COLLECTION IS NULL --->
d. CURSOR ALREADY OPEN ---> ATTEMPTS TO OPEN THE ALREADY OPEN CLAUSE
e. DUP VAL ON INDEX -0001 SQLCODE--> -1 WHEN INSERTING DUPLICATE VALUES IN THE UNIQUE CONSTRAINED COLUMN
f. INVALID CURSOR --> EG CLOSIG AN UNOPEN CURSOR.
g. INVALID NUMBER ---> CONVERSION OF CHARACTER STRING TO THE NUMBER FAILS BECAUSE STRING DOES NOT REPRESENT
THE VALID NUMBER.
h. LOGIN DENIED ----> PROGRAM ATTEMTS TO LOG ON TO THE ORACLE WITH AN INVALID USERNAME AND PWD.
i. NO DATA FOUND (-1403) SQLCODE--> 100 ---> SELECT STATEMENT DOES NOT RETURN ANY ROWS.
j. NOTE ---> SELECT WITH AGGREGATE FUNCTIONS NEVER RAISES THIS EXCEPTION BCAUSE THEY ALWAYS RETURN VALUE OR NULL
k. NOT LOGGED ON ---> PROGRAM ISSUES THE DATABASE CALL WITHOUT CONNECTING.
l. PROGRAM ERROR ---> INTERNAL PROBLEM
m. ROWTYPE MISMATCH
n. STORAGE ERROR
o. TOO MANY ROWS (01422) --- > SELECT STATEMENT RETURNS MORE THAN ONE ROW
p. VALUE ERROR ----> ARITHMATIC ,CONVERSION,SIZE_CONSTRAINET ERROR (FETCHING THE VALUE WITH MORE SIZE INTO CHARACTER COLUMN THAN DECLARED)
q. ZERO DIVIDE (01476) ---> PROGRAM ATTEMPTS TO DIVIDE NUMBER BY ZERO.
r. TIME OUT ON RESOURCE ---> TIME OUT OCCURS WHILE ORACLE WAITING FOR RESOURCE.
15. WHAT IS SYNONYMS, TYPES
ANS: A synonym is an alias for a table, view, snapshot, sequence, procedure, function, package, or object type. Synonyms let you refer to objects from other schemas without including the schema qualifier
16. WHAT IS VIEWS
ANS: A view can be thought of as a "stored query" presenting data from one or many tables. A view does not actually contain or store data, but derives data from the base tables on which it is based. Views can be queried, updated, inserted into, and deleted from. Operations on a view affect the view’s base tables.
In general, complex blocks are based on views while simple setup blocks are based on tables. The advantages to using views include:
· Network traffic is minimized because all foreign keys are denormalized on the server
· You do not need to code any POST-QUERY logic to populate non-database fields
· You do not need to code PRE-QUERY logic to implement query-by-example for non-database fields
Whenever performance is an issue and your table has foreign keys, you should define a view to improve performance. Views allow a single SQL statement to process the foreign keys, reducing parses by the server, and reducing network traffic.
17. TYPES OF PESUDOCOLS
ANS: ROWID, ROWNUM, NEXTVAL, CURRVAL, LEVEL (HIRARCHICAL QUERY START WITH AND CONNECT BY)
PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM. Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also, pseudocolumns are allowed in SQL statements, but not in procedural statements.
18. CAN WE USE PESUDOCOLS IN VIEWS AND CURSOR
ANS: YES, We do use
19. CAN WE USE ROWID IN CURSOR( E.G ON PAGE 264 OF PLSQL GUIDE)
ANS: YES, We do use
20. TYPES OF CURSOR
21. WHAT IS REF CURSOR
ANS: REF CURSOR IS A CURSOR OF VARIABLE TYPE CAN BE USED FOR
MULTIPLE SQL QUERIES.
22. CURSOR ATTRIBUTES
ANS: %NOTFOUND %FOUND %ROWCOUNT %ISNULL
23. CAN U DEFINE PROCEDURE IN PROCEDURE
ANS: NO WE CANNOT. WE CAN DEFINE PROCEDURE IN PACKAGE
24. WHAT IS DYNAMIC SQL
ANS: SQL WHICH CAN BE CHANGED IN RUN TIME USING LEXICAL
25. CAN WE USE LEXICAL REFERENCE IN PROCEDURE
ANS: NO (ONLY BIND VARIABLE)
26. CAN WE USE LEXICAL REFERENCE IN DYNAMIC SQL - YES
27. TYPES OF TRIGGER IN REPORTS
ANS: REPORTS TRIGGERS ARE
i. Before parameter form
ii. After parameter form
iii. Before report
iv. Between pages
v. After report
28. SEQUENCE OF FIRING OF REPORT TRIGGERS
i. Before parameter form
ii. After parameter form
iii. Before report
iv. Between pages
v. After report
29. WHAT IS APPLICATION OF LEXICAL REF IN REPORTS
ANS: Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following: Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value. Reports Builder uses these values to validate a query with a lexical reference. Create your query containing lexical references.
30. WHAT IS MATERILISED VIEWS
ANS: A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. For replication purposes, materialized views allow you to maintain copies of remote data on your local node. The copies can be updatable with the Advanced Replication feature and are read-only without this feature. You can select data from a materialized view as you would from a table or view. In replication environments, the materialized views commonly created are primary key, rowid, object, and subquery materialized views.
Note: The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
31. WHAT IS USE OF SRW.MESSAGE
ANS: TO DISPLAY MESSAGE IN RUN TIME. IT IS A IN BUILT PROCEDURE.
This procedure displays a message with the message number and text that
you specify. After the message is raised and you accept it, the report
execution will continue.
You cannot trap nor change Reports Builder error messages.SRW.MESSAGE
does not terminate the report execution; if you want to terminate a report
after raising a message, use SRW.PROGRAM_ABORT.Any extra spaces in the
message string will be displayed in the message; extra spaces are not
removed by Reports Builder.
32. IN WHICH TABLE U GET FLEXFIELD (KFF & DFF) INFORMATION
ANS: fnd_flex_values_tl, fnd_id_flex_segment, Fnd_flex_value_sets
Descriptive flexfield details are stored in
33. WHAT COLS IN FND_ID_FLEX_STRUCTURES AND AP_LOOKUP_CODES
ANS: AP_LOOKUP_CODES - LOOKUP_TYPE, MEANINGS, DESCRIPTION, LOOKUP_CODE
34. WHAT DIFFERENT TABLES GET AFFECTED IN PAYABLE INTERFACE
ANS: AP_INVOICES_ALL, PO_VENDORS, AP_INVOICE_PAYMENTS_ALL
35. WHAT IS CONTEXT
ANS: Different segments or additional pop-up windows appear depending on the values you enter in other fields or segments.
36. WHICH TYPE OF KFF USED IN GL
ANS: Accounting Flexfield
37. TYPES OF USER_EXISTS
a. FND SRWINIT
b. FND FLEXSQL
c. FND FLEXIDVAL
d. FND FORMAT_CURRENCY
e. FND FORMAT_DATE
f. FND SRWEXIT
38. TELL BRIEFLY @ USER_EXISTS
ANS: USER EXISTS Calls external program probably written in Pro ‘C’ etc and sends Parameters from report or forms, external program does the process based on those sent parameters and returns the value back to report or forms.
39. WHAT R DIFFERENT TYPES OF EXECUTION METHODS
a. Oracle Report
b. PLSQL Stored Procedures
d. SQL Loader
g. Multi Language Function
h. JAVA concurrent Program
i. JAVA Stored procedure
k. Request set stage function
40. WHAT IS AUTONOMOUS TRANSACTION
ANS: Autonomous Transaction is a independent transaction started by another main
or parent Transaction create or replace procedure authid current_user as pragma
41. What is Value Set in Apps?
42. What is Dependent type value set?
43. What is Table type value set?
44. What is report customization?
45. Which all Reports you have customized?
46. Which all tables involved?
47. What is GL Interface?
48. How it is done - Steps.
49. What is partitioning in the table? How are they accessed?
50. What is AOL?
51. Which tables come under AOL?
52. How files are attached?
53. How log file is created?
54. What is SQL Loader?
55. How control file is written?
56. If I want to add data in a column of a table what you will do?
57. How to delete distinct rows?
58. What are triggers in Forms?
59. What is record group?
60. What is property class?
61. What are triggers in reports?
62. Which srw programs are used in reports?
63. If in the inner program an exception is raised?
64. What are user_exits?
65. What is external Table?
66. Asked to write a query to display salary of average which is greater than 1000?
67. What is exception propagation?
68. What is nocopy hint?
69. How to set Profile option?
70. What is format trigger in Reports?
71. Tell some of the UNIX commands?
72. How do you write shell script?
73. What are cursors?
74. What are the different ways to declare it?
75. Tell some of the fnd tables?
76. What is the architecture of Apps?
Suggestion: Create indexes on your Transaction Flexfield columns if you want to query Transaction Flexfield information in your invoice headers and lines. Additionally, without indexes the validation portions of the AutoInvoice program can be slow. For more information about defining Transaction Flexfield indexes, see: Importing Invoice Information Using AutoInvoice: page 4 - 204.
Explain Plan of Trace File
What is Concurrent Program:
ANS: A concurrent program is program that does not require continued interaction on your part to perform a specific task. In Oracle Applications, for example, a concurrent program may be a program written to create a report, or to post a batch of general ledger journal entries
What is a Concurrent Process:
ANS: A concurrent process is an instance of a running concurrent program. Each time a concurrent manager receives a request and runs a concurrent program, it creates a new concurrent process. A concurrent process can run simultaneously with other concurrent processes (and other activities on your computer).
What is concurrent request?
ANS: A concurrent request is a request that you submit to run a concurrent program as a concurrent process. You issue a concurrent request when you submit a report or program to run using Standard Request Submission or when you choose an action button in a product-specific submission window
What are PL/SQL Supplied Packages
Package Name Description Documentation
1.DBMS_ALERT Provides support for the asynchronous
notification of database events.
2.DBMS_APPLICATION_INFO Lets you register an application name with the database for auditing or performance tracking
3.DBMS_DDL Provides access to some SQL DDL statements
from stored procedures, and provides special
administration operations not available as DDLs.
4.DBMS_DEBUG Implements server-side debuggers and provides
a way to debug server-side PL/SQL program
5.DBMS_DESCRIBE Describes the arguments of a stored procedure
with full name translation and security checking.
6.DBMS_JOB Lets you schedule administrative procedures that
you want performed at periodic intervals; it is
also the interface for the job queue.
7.DBMS_LOCK Lets you request, convert and release locks
through Oracle Lock Management services.
8.DBMS_METADATA Lets callers easily retrieve complete database
object definitions (metadata) from the dictionary.
9.DBMS_OUTPUT Accumulates information in a buffer so that it can
be retrieved out later.
10.DBMS_PIPE Provides a DBMS pipe service which enables
messages to be sent between sessions.
11.DBMS_REFRESH Lets you create groups of snapshots that can be
refreshed together to a transactionally consistent
point in time. Requires the Distributed Option.
12.DBMS_REPAIR Provides data corruption repair procedures.
13.DBMS_PIPE Provides a DBMS pipe service which enables
messages to be sent between sessions.
14.DBMS_PROFILER Provides a Probe Profiler API to profile existing
PL/SQL applications and identify performance
15.DBMS_RANDOM Provides a built-in random number generator.
16.DBMS_REFRESH Lets you create groups of snapshots that can be
refreshed together to a transactionally consistent
point in time. Requires the Distributed Option.
17.DBMS_SESSION Provides access to SQL ALTER SESSION
statements, and other session information, from
18.DBMS_SHARED_POOL Lets you keep objects in shared memory, so that
they will not be aged out with the normal LRU
19.DBMS_SPACE Provides segment space information not available
through standard SQL.
20.DBMS_SQL Lets you use dynamic SQL to access the database.
DBMS_STATS Provides a mechanism for users to view and
modify optimizer statistics gathered for database
21.DBMS_TRACE Provides routines to start and stop PL/SQL
22.DBMS_TRANSACTION Provides access to SQL transaction statements
from stored procedures and monitors transaction
23.UTL_FILE Enables your PL/SQL programs to read and write operating system text files and provides a
restricted version of standard operating system stream file I/O.
24.UTL_HTTP Enables HTTP callouts from PL/SQL and SQL to
access data on the Internet or to call Oracle Web
Listing Information about schema:
The data dictionary provides many views that provide information about schema objects. The following is a summary of the views associated with schema objects:
· ALL_OBJECTS, USER_OBJECTS
· ALL_CATALOG, USER_CATALOG
· ALL_TABLES, USER_TABLES
· ALL_TAB_COLUMNS, USER_TAB_COLUMNS
· ALL_TAB_COMMENTS, USER_TAB_COMMENTS
· ALL_COL_COMMENTS, USER_COL_COMMENTS
· ALL VIEWS, USER_VIEWS
· ALL MVIEWS, USER_MVIEWS
· ALL_INDEXES, USER_INDEXES
· ALL_IND_COLUMNS, USER_IND_COLUMNS
· ALL_SEQUENCES, USER_SEQUENCES
· ALL_SYNONYMS, USER_SYNONYMS
· ALL_DEPENDENCIES, USER_DEPENDENCIES
What is pragma exception
ASSOCIATES EXCEPTIONS WITH ERROR NUMBERS.
SHOULD BE DECLARED IN DECLARATION SECTION.
IT’S THE COMPILER DIRECTIVE , PROCESSES ERRORS AT THE COMPILE TIME.
· IF EXCEPTION HANDLER IS NOT PRESENT IN LOCAL BLOCK THEN IT’S AUTOMATICALLY PROPAGATRED TO IT’S ENCLOSING BLOCK.
· SCOPE RULES :
EXCEPTION DECLARED IN OUTER BLOCK
BECOMES GLOBAL TO ALL IT’S SUBBLOCKS.
BUT THE SAME IS NOT TRUE FOR SUBBLOCKS.
What are steps to customize forms?
Copy the template.fmb and appstand.fmb from AU_TOP /Forms/US and put it in custom directory. The libraries (FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA, JL, VERT) are automatically attached.
Take one copy of template.fmb and rename it to name of the new form and after customization save it in the respective product top.
How to use flexfields in reports?
There are two ways to use flexfields in report, one way is to use the view (table name + KFV or DFV) created by apps. And use the concatenated segments columns which holds the concatenated segments of the key or descriptive flexfields.
Other way is to use the FND user exits provided by oracle applications.
What is flexfield?
Oracle application uses flexfiled to capture information about your organization. Flexfield have flexible structure for storing key information. Like company, cost center and account. They also give you highly adaptable structure for storing customized information in oracle applications.
How many flexfields are there in AR and what are they.
IN AR THEIR R 2 KFF 1)SALES TAX LOC FF 2)TERRITORY FF
Difference between key flexfield and descriptive flexfield:
Key flexfield are stored in segment columns
For key flexfield there are flexfield qualifier and segment qualifier
Context sensitive is a feature of descriptive flexfield.
Descriptive flexfield are stored in Attributes
Can you call APPCORE Library in CUSTOM library
You cannot attach the APPCORE library to CUSTOM because it would cause a recursion problem because CUSTOM is attached to APPCODE. As of Oracle application release 11i you may attach the APPCORE2 library to CUSTOM. The APPCORE2 library duplicates most APPCORE routines with the following packages:
These packages contain the same routines as the corresponding APPCORE packages. Follow the documentation for the corresponding APPCORE routines, but add a 2 to the package names.
What is MRC and what are its use?
The Multi reporting currency feature allows you to report and maintain records at the transaction level in more than one functional currency. You can do it by defining one or more set of books in additional to the primary set of books.
How many reporting currencies can be attached to the primary set of books?
Ans: 9 Reporting currencies can be attached to the primary set of books.
What are new features in Release 11i
What are ad-hoc reports
Ad-hoc reports are made to meet the one-time reporting needs. Concerned with or formed for a particular purpose. For example, ad hoc tax codes or an ad hoc database query.
FSG is a Ad-Hoc Report.
What is FSG?
FSG is a powerful and flexible report building tool you can use to build your own custom reports without programming. FSG is only available with GL.
What do you mean by HZ_ in customer tables
How can you handle multiple rows without using loops in PL/SQL
What are different types of files used in SQL LOADER
ANS: fixed, variable and stream
What are interface tables in AP, AR and GL?
What does set of books comprised of?
ANS: CHART OF ACCOUNTS, CALENDAR AND CURRENCY
What is Flexfield Structure? (235-256 flexfield guide)
What are the validation types supported by value sets?
ANS: Dependent, Independent, None, Pair, Special, Table, Translatable Independent and Translatable Dependent.
What are format types supported by the value sets?
ANS: Char, date, date time, number, standard date, standard date time, time.
Is there any restriction over the number of flexfield structure that can be defined for the accounting flexfield.
ANS: NO there is no such restrictions.
What are flexfield qualifier that are available for accounting flexfield structure?
ANS: Natural accounting segment, cost center segment, balancing segment and intercompany segment.
Which Flexfield qualifiers are mandatory?
ANS: Balancing segment and Natural Account segment
Which Mandatory account needs to be defined for the set of books definition?
ANS: Retained Earnings - this is undistributed profit of the shareholders.
What are all accounts that can be defined for the set of books form?
ANS: Retained Earnings, Suspense, Rounding Difference, Reserve for Encumbrance
Account and Net Income Accounts.
What is the minimum and maximum no of periods that can be defined for the calendar?
ANS: 1 & 366
What are the period statuses that can be associated with the periods?
ANS: Open, closed, permanent closed, never opened, future entry
Can multiple periods have open status at one time?
How many chart of accounts can be mapped to a set of books.
ANS: Only One
How many set of books can be defined in GL module?
What is responsibility?
ANS: A collection of forms, menu and program that a user can access.
Can multiple responsibilities be assigned to a single user?
Can responsibility be shared by multiple logon users?
From a general ledger responsibility, how many set of books can be accessed?
ANS: Only One.
What are different currency conversion rates?
ANS: General Ledger provides the following predefined daily conversion rate types:
Spot: An exchange rate, which you enter to perform conversion based on the rate on a specific date. It applies to the immediate delivery of a currency.
Corporate: An exchange rate you define to standardize rates for your organization. This rate is generally a standard market rate determined by senior financial management for use throughout the organization.
User: An exchange rate you specify when you enter a foreign currency journal entry.
Which table captures conversion rates information?
Which column distinguishes the type of journal being entered?
ANS: actual_flag (A-Actual, B-Budget, E-Encumbrance)
Dependant and Independent Value sets:
For example, suppose you have an independent value set called "Account" with a dependent value set called "Sub-Account." You may wish to create a new independent value, 99, for "Account" with description "Receivables" without creating any associated sub-account values. Since your flexfield requires a dependent value of some sort to go with the independent value, it uses the default value you enter here, such as 00 with description "No Sub-Account."
List the tables which captures the journals information
Which table captures the balances information of the code combinations?
What is significance of compiling a flexfield structure?
Ans: It reflects the changes made to a flexfield structure during the unfreezed state.
What changes can be enforced on the flexfield structure when It is unfreezed?
Ans: Changing segment names, changing ordering sequence, changing window prompts, adding new segments, changing the enabled and displayed attributes.
What is Autorate program?
Ans: It is a program, which selects all the foreign currency transactions in a module in and applies that days currency conversion rate.
Is It mandatory to freeze the flexfield
How many views are generated when compiling a flexfield structure?
Ans: Two - One is user defined and another is system defined GL_CODE_COMBINATION_KFV
What is the significance of intercompany flexfield qualifier?
Ans: It allows inter company transactions to be recorded.
How do you setup suspense posting?
Ans: Define suspense account code combination in set of books form.
Manual tax journals can be recorded if allow tax journals options is not selected in the set of books form.
Selection of journal approval option makes it mandatory for every journal to be approved before a journal is posted?
Where do you specify journal approval limits in Oracle apps?
Ans: In GL Module only.(Setup - Employee - Limit) where employee wise authorization limit can be entered.
Can previous conversion rates information be used for current transaction?
After the batch is posted and journals within the batch can be updated
To reverse an entire batch what is the option available in GL?
Ans: Reverse batch option in Journal Batch window.
To reverse only journal within the journal what steps have to be performed?
Ans: Journal - Enter - Find - Batch - Query - More action - Reverse Journal
What are the amount types supported by the application.
Can Statistical budget be defined in GL?
What are the modules involved in the encumbrance cycle?
Ans: GL, PO, AP
What is Encumbrance accounting?
Ans: Encumbrance accounting is the cycle of generation of encumbrance entries for the reservation of funds.
What is the name of the system generated view when you compile an accounting flexfield structure?
Which Profile option needs to be set to allow mixed journals?
Ans: Journals: Mix Monetory and Statistical (Yes/No)
Which Profile option should be set to prevent transactions on a non-business day?
Ans: Journals: Allow Non-Business Day Transactions should be set to NO.
What is the purpose of journals? Default Category profile option?
Ans: To default a journal category in case of manually recorded journal.
What are the Journal reversal methods available in GL?
Ans: Switch DR/CR
Which profile option is used to map a responsibility to a general ledger set of books?
Ans: GL Set of books name
What are the different statuses that a budget can have?
Ans: Frozen, Open and Default
Only Ten Budget Organizations can be defined in a gl set of books
Encumbrance balances are maintained for expense accounts?
What is the use of cursors in PL/SQL and what is REF cursor?
Ans: The cursors are used to handle multiple row query in PL/SQL. Oracle uses implicit cursor to handle all its 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 are autonomous transaction? Give a scenario where you have it?
Ans: It is an independent transaction started by the another transaction the main or parent transaction, do SQL operations, commit or rollback those operations the resume back to 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 transaction 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.
Further more 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 message 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 types of triggers available in forms are:
1. Key triggers
2. Navigational triggers
3. Transactional triggers
4. Message triggers
5. Error triggers
6. Query based triggers.
What is the use of TEMP tables in interface programs?
Ans: Temporary tables are used to hold the intermediate data. The data is loaded into a temporary tables first and after validating through the PL/SQL Program the data is loaded into the interface table.
Do you need to register the feeder program of interface to AOL?
Ans: Yes you have to register the feeder program as concurrent program to Apps.
What are the steps to form customization?
Ans: Copy the template.fmb and Appstand.fmb from AU_TOP/Forms/us and put it in the custom directory. The libraries FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA, JL VERT are automatically attached. Create or open new forms then customize it save this form in corresponding module.
How to use flexfield in reports?
Two ways: One is to use the view tables such as _KFV or _DFV created by apps and use the concatenated segments column which holds the concatenated segments of the key or descriptive flexfields.
Or use FND user exits provided by oracle applications.
What is KEY FLEXFIELD?
Ans: Unique identifier storing key information used for entering and displaying key information. For example Oracle general ledger uses a key flexfield called accounting flexfield to uniquely identifies a general account.
Descriptive flexfield to capture additional information.
How many flexfields are there in AR and what are they?
1. Transaction Flexfield
2. Territory Flexfield
3. Salex Tax Location Flexfield
What is MRC and its use?
Ans: Multi Reporting Currency allows you to report and maintain record at the transaction level in more than one functional currency. You can do so by defining one or more set of books in addition to the primary set of book.
What is Format trigger in reports where u uses this trigger and how?
A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.
/* Suppose that you are building a banking report and ** would like it to indicate if a customer is overdrawn.** To do so, you give the repeating frame around the ** customer information a format trigger that causes ** it to have a border only if a customer's account ** balance is less than 0 (or the required minimum **balance). */
Function my_formtrig return BOOLEAN isbegin if :bal < 0 then srw.attr.mask := SRW.BORDERWIDTH_ATTR; srw.attr.borderwidth := 1; srw.set_attr (0, srw.attr); end if; return (true); end;
1. What is Purchase Order functionality
2. Which SRS is run for generating invoice in AP.
3. Types of Flexfields
4. Types of Value sets
5. What is Legal Entity
6. What are other Units that you define for multi organization setup
7. Where you will define SOB for the inventory organization
Ans: Define in Organization option for Inventory Organization (classification) - Other -Accounting Information - Specify SOB-Legal Entity and Operating Unit.
8. Where you will set the HR organization. (At Operating Unit level)
9. What are flexfields in Inventory.
10. What you will do to proceed further from the point where no approval is done for purchase order work flow.
1. How to register report in apps
2. Types of Value sets
3. Where you will store control file for interface which folder (cus/bin)
4. What is Lexical parameter
5. Which all modules worked on.
6. Payable interface tables.
7. Where we will define parameters
1. whats the out put of "select * from emp where rownum=5;"
no rows selected
2. whats the use of Between pages trigger?
Description:The Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level: Report
Displays an error message when you try to go to the page for which the trigger returned FALSE. The pages subsequent to the page that returned FALSE are not formatted. If the trigger returns FALSE on the last page, nothing happens because the report is done formatting. The Between Pages trigger does not fire before the first page. If the trigger returns FALSE on the first page, the first page is displayed, but, if you try to go to the second page, an error message is displayed.
3. For two pages report for how many times between page trigger will fire?
One time , after first page.
4. Multi org structure?
Business Groupà SOB->Legal Entityà Operating Unità Inventory Orgà sub Inventory.
5. Difference between org_id and organization_id?
Org_id indicates operating unit, organization_id indicate Inventory Organization
6. what are the base table for multi org informations?
ANS: HR_LOCATIONS_ALL, HR_ALL_ORGANIZATION_UNITS,
8. How to use one formula column to return two values?
ANS: It always returns one value.
9. How I can have multiple layout in one report?
ANS:Use additional default layout tool in layout model tool bar.
10. Explain how I can use additional layout option?
In layout model, click below your first layout selecting additional layout option from tool bar, which will activate report wizard , choose any other layout you need and finish it, you will get second layout along with first one.
11. what will be output of " select * from po_headers? "
ANS: No row selected. In case of multi org flag is yes. This is a view and filter condition is given for org_id for which we need to initialize ord_id passing user_id, responsibility_id and applicaton_id or need to extract ord_id from client_info global variable.
12. diffrence between po_headers and po_headers_all ?
ANS: po_headers_all is a table to store information for more than one organization and po_headers is a view, which is partitioned by org_id
13. use of retcode?
ANS: It returns status of the pl sql program you are running.
14. how i can fetch the current operating unit in plsql program?
ANS: Use Client_info global variable
15. how i can fetch the request id of the running conc program?
ANS: fnd_global.conc_program_id, fnd_global.conc_request_id
16. whats the user exit ? explain each.
ANS: Explain him FND SRWINIT, SRWEXIT, FLEXSQL, FLEXIDVAL, FORMAT CURRENCY.
17. how i can make sure that one program if running then no other is running in parallel?
ANS: Check the “Run Alone” checkbox while defining your concurrent program.
18. in sql*loader how to insert data in multiple tables?
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
19. How i can make sure that no row of dat file is inserted if even one row fails the validation?
20. How to show error message?
ANS: Use exception handler, raise_application_error or use SQLCODE, SQLERRM.
21. How to use table type? detail steps(registering a table....)
register a table using AD_DD package procedures,
use table type value set to use this registered table.
Enter value, meaning, ID fields.
22. Flow of PO?
Requisition (generate and approve)à PO(generate and approveà send it to customer àget receipt of goodsà use “Pay on Auto Invoice” conc. Program to automatically generate Invoiceà pay the customer.
23. explain plan of correlated subquery?
First outer query will fire, on the basis of the output of the outer one the inner query will fire.
24. How to do interface?
Staging table à use SQL*Loader to load data from dat files (external files in CSV format) in these tablesà use plsql to do the validations so that each row gets inserted into the pre interface tablesà once it goes into the pre tables use simple plsql to send data from pre interface tables to interface table.
25. base tables of GL, AR interfaces.
For import journal à GL_INTERFACE,
For daily rates interfaceà GL_DAILY_RATES_INTERFACE
For customer interfaceà RA_CUSTOMER_INTERFACE
26. Validations of GL, Customer, daily rates interfaces.
27. imp tables of BOM, PO, their imp columns.
28. where the info of KFF, DFF is stored? (base tables of FND?)
29. explain TCA architecture.
Trade Community Architecture (TCA)It is supposed to be the base for storing the complex information about the customer, parties and their relationship. TCA and its DQM (data quality management) system helps to maintain this information by avoiding duplications and easy access to this complex information.
30. diff between KFF, DFF.
KFFà used to store mandatory information.
DFFà used to store non mandatory information.
KFFà stored in base table columns (SEGMENT1..30)
DFFà stored in ATTRIBUTE1..20
KFFà has segment qualifiers and FF qualifiers
DFFà has CONTEXT, a field in which you will enter one of the option available.
Depending on this option entered by you the fields for descriptive information will pop up. The segment which will depend on this CONTEXT are called as context sensitive segments. And the segment which doesn’t depends on context are called GLOBAl segments.
31. where the party info, customer number, item number is stored?
HZ_PARTIES, HZ_PARTY_SITES, MTL_SYSTEM_ITEMS
32. what is the flow of BOM.
33. report triggers sequence?
BEFORE PARAMETER FORM
AFTER PARAMETER FORM
34. diff bet formula, placeholder.
Formula column used to return value based on the user calculations.
Placeholder column is used to hold value set by the user. U can set its value in one of the following à before report trigger, report level formula column, or a formula column in a group or a group below of this placeholder column.
34. explain database triggers.
Ans: Triggers are classified in three forms
Levelàstatement level, row level.
Statement level will fire once for a statement say
delete from emp where deptno=10; -- say it deletes 5 rows of emp
if it’s a statement level then it will fire only once.
Where as if it’s a row level it will fire for each row processed ie for 5 times.
Eventà insert, update, delete
Timingà BEFORE, AFTER, INSTEADE OF
35. what is the instead of trigger?
If you have created a view on the basis of join between two tables. If you try to insert values through this triggers it wont allow but still if you have to insert values then you will write INSTEADE OF trigger on this VIEW. Doing so will allow you to insert through this view into two different underlying tables.
36. i am deleting some rows from table, i want the rows being deleted to be stored in another table, how will i do it?
Use before delete trigger on this table.
37. what is parameterized cursor? and cursor attributes?
You can pass a parameter to cursor depending on which it will fetch rows.
Cursor c1(num number) is
Select * from emp where deptno=num;
While opening this cursor
37. how you do the documentations, before staring a task , and once task is finished.
We have our MD.070 technical documentation. We get a functional requirement MD.050 from functional consultant, we convert it into technical document as exactly how we will achieve the required target. Then we show it to our PM , get some modifications done required by him. Get that document approved from him. Then only we can start the task in the same way mentioned in the technical MD.070 approved document.
38. What is request set ?
it’s a set of concurrent programs. You have multiple stages in one request set, each stage can contain multiple requests; each request in turn can have required parameters declared. Each stage can be connected to other stages depending on its completion status (success, error, warning). Stages run one after another, whereas requests in each stage runs parallel.
39. Diff between functions, procedure?
Function has to return a value, (only one), and it can be executed in a select statement or any other expression. Like Var1 := my_function(10,20);
procedures cant be executed in select statement. And its not mandatory for a procedure to return a value.
40. how procedure can return value?
Using IN OUT, OUT parameter. With the help of out parameter more than one value can be return in procedure.
41. can i have IN OUT parameters in function?
NO. functions doesn’t accept IN OUT, OUT parameters.
42. can i have COMMIT in triggers.
NO. triggers cant have any TCL (Transactional Control Statements)
commit, rollback, savepoint not allowed.
43. whats the output of following code
cursor c1 is
select empno, ename from emp where deptno=20;
cnt1 number default 0;
cnt2 number default 0;
cnt1 := c1%rowcount;
fetch c1 into memp1, memp2;
exit when c1%notfound;
cnt2 := c1%rowcount;
cnt => 0
cnt => 5
44. what is pragma?
Pragma is a compiler directive.
Oracle have four PRAGMAS.
v PRAGMA EXCEPTION_INIT
Used to attach a non defined error with a number.
v PRAGMA AUTONOMOUS_TRANSACTION
To mention that this transaction will contain a call to an autonomous transaction.
v PRAGMA RESTRICT_REFERENCES
To restrict a function from writing to, reading from database.
Options are WNDS, RNDS, WNPS, RNPS.
v PRAGMA SERIALLY_USABLE
45. What do you know about partitioned table?
46. How to find column in a oracle database?
47. How to find current operating unit?
General Report 6i Questions:
1. Why do we call FND SRWINIT from Before Report Trigger
A. FND SRWINIT fetches concurrent request information and sets up the profile options. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)
2. Why do we call FND SRWEXIT from After Report Trigger
A. FND SRWEXIT frees all the memory allocations done in other Oracle Applications user exits. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)
3. Why do we call FND FLEXSQL from the Before Report Trigger?
A. One need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any description and values from switched value sets as needed. One gets this information by calling the AOL user exit FND FLEXSQL from the before report Trigger.
4. If u call the user exit FND FLEXSQL with MODE = " WHERE" from the Before Report Trigger. What will it do?
A. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of the report query. This user exit is called once for each lexical to be changed.
5. If u call the user exit FND FLEXSQL with MODE = " ORDER BY" from the Before Report Trigger. What will it do?
A. This user Exit populates the lexical parameter that one specifies with the appropriate SQL fragment at run time. One includes this lexical parameter in the ORDER BY clause of the report query. This user exit is called once for each lexical to be changed.
6. How can we display flexfield segment values, descriptions, and prompts on the report?
A. Create a formula Column. Call the user exit FND FLEXIDVAL as the formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that one does not has to use complicated table joins to the flex field tables.
7. Name some options of the FND FLEXSQL user exit
A CODE, APP_SHORT_NAME, OUTPUT, MODE, DISPLAY, SHOWDEPSEG, NUM or MULTINUM, TABLEALIAS, OPERATOR, OPERAND1, OPERAND2.
8. Describe CODE option of the FND FLEXSQL user exit
A. Specify the flex field code for the report (for example, GL#, MCAT).
9. Describe the APP_SHORT_NAME option of the FND FLEXSQL user exit
A. Specifies the short name of the application that owns the flex field (for example: SQLGL, INV)
10. Describe the OUTPUT option of the FND FLEXSQL user exit
A. Specify the name of the lexical parameter to store the SQl fragment. One uses this lexical later in the report when defining the SQL statement that selects the flexfield values. the datatype of this parameter should be character.
11. Describe the MODE option of the FND FLEXSQL user exit
A. Specify the mode to use to generate the SQL fragment . valid mode are :
SELECT: Retrieves all segments values in an internal (non- displayable format).
WHERE: Restrict the query by specifying constraints on flexfield columns. The fragment returned includes the correct decode statement if one specifies MULTINUM. One must also specify an OPERATOR and OPERANDS.
HAVING: Same calling procedures and functionality as WHERE.
ORDER BY: Order required information by flexfield columns. The fragment Orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement, one specifies in MULTINUM.
12. Describe the DISPLAY option of the FND FLEXSQL user exit
A. One uses the DISPLAY token with the MODE token . the DISPLAY parameter allows you to specify segments that represent specified flexfield qualifiers or specified segments numbers , where the segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form.
Eg. If your MODE is SELECT and you specify DISPLAY = "ALL" then the SELECT statement includes all the segments of the flexfield. . Similarly, if your MODE is WHERE and you specify DISPLAY = "ALL", then your WHERE clause includes all segments.
13. Describe the SHOWDEPSEG option of the FND FLEXSQL user exit
A. SHOWDEPSEG = "N" disables automatic addition of depended upon segments to the order criteria. The default is "Y". This token is valid only for MODE = "ODER BY" In FLEXSQL.
14. Describe the NUM option of the FND FLEXSQL user exit
A. Specify the name or lexical or source column that contains the flexfield structure information. If the flexfield uses just one structure, specify NUM only and use a lexical parameter to hold the value. If the flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. The default value is 101.
15. Describe the TABLE ALIAS option of the FND FLEXSQL user exit
A. You use TABLE ALIAS if your SELECT joins to other flexfield tables or uses a self - join.
16. Describe the OPERATOR option of the FND FLEXSQL user exit
A. Specify an operator to use in the WHERE clause.
17. Describe the OPERAND1 option of the FND FLEXSQL user exit
A. Specify an operand to use in the WHERE clause,
18. Describe the OPERAND2 option of the FND FLEXSQL user exit
A. Specify a second operand to use with OPERATOR = "BETWEEN"
19. Where is FND FLEXIDVAL user exit used
A. Call this user exit to populate fields for display. You pass the key flex fields data retrieved by the query into this user exit from the formula column. With this exit you can display values, descriptions and prompts by passing appropriate token (any one of VALUE, DECRIPTION<APROMPT or LPROMPT).
21. Name the interface tables used for the customer interface?
A. 1. RA_CUSTOMERS_INTERFACE_ALL
22 What is the name of the column in CUSTOMER_INTERFACE_TABLE that indicates whether you are inserting new or updating existing information?
A: When importing data into the interface tables, the column INSERT_UPDATE_FLAG indicates whether you are inserting new or updating existing information. This column is required in RA_CUSTOMERS_INTERFACE.
23 If the INSERT_UPDATE_FLAG is not set correctly or the required column is missing the value, will CUSTOMER INTERFACE reject the entire record or just the attributes u want to update?
A Reject the entire record.
24 List some of the required columns for the RA_CUSTOMERS_INTERFACE?
CUSTOMER_NUMBER (if you are not using Automatic Customer Numbering)
If you are importing an address and a business purpose, you must also populate the following columns:
PRIMARY_SITE_USE_FLAG (if you are inserting an address)
LOCATION (if you are not using Automatic Site Numbering)
SITE_USE_CODE (if you are inserting an address)
25 List some of the production tables that Customer Interface transfers customer data from the interface tables into?
26. What validation must be given on the customer_number?
A Must be null if you are using Automatic Customer Numbering. Must exist if you are not using Automatic Customer Numbering. This value must be unique within RA_CUSTOMERS.
27 What validation must be given on the CUSTOMER_STATUS?
A Must equal 'A' for Active or 'I' for Inactive.
28 Name some of the Oracle receivables Interfaces?
A a) Auto Invoice
b) Auto Lockbox
c) Customer Interface
d) Sales Tax rate Interface
e) Tax Vendor Extension
29 Give some of the Oracle Payables interface?
A. a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching
30. Name some of the oracle general ledger Interface?
A a) Budget Upload
b) Importing Journals
c) Loading Daily rates
31. What are the names of the parameters u pass to the Procedure which u register in the apps?
A. 1) retcode in varchar2
2) errbuf in varchar2
32. What is the use of Auto lock Box?
A Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing.
33. Auto Lockbox is a three-step process, what are those?
A. a) Import
34. What is the order in which Autolock box searches for the types of the matching number?
A. 1. Transaction Number
2. Sales Order Number
3. Purchase Order Number
4. Consolidated Billing Invoice Number
5. Other, user-defined number.
35. What is application short name for General Ledger you specify in FND FLEXSQL user exit?
36 . What are validations to be done in Journal Import interface.
A. Batch level: Set of Books, Period Name, and Batch Name
Journal Level: Set of books, Period name, Source name, Journal entry name, Currency code, Category name, Actual flag, Encumbrance type ID, User conversion type, Accounting date, Budget version ID
37. What subclass in forms6i
A Specifies module, storage & name information about the source object and source module for a referenced objects.
38. What is the clause in SQL * Loader to program to override data into table
39. How do you set profile in oracle applications In Application Developer responsibility?
A Open 'Profile' Function
40. What is the syntax for loading data through SQL * Loader from multiple files simultaneously
A. Sqlldr scott/tiger@orcl control = ctlfile
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
41 What is the table name for items in Oracle Inventory
A MTL_SYSTEM_ITEMS, MTL_CATEGORIES
42. Tell me names of important production tables & their purpose AP, AR, GL, PO
A AP: AP_INVOICES_ALL, AP_INVOICE_LINES_ALL
To store invoices
AR: RA_SHIPMENT_HEADERS/ _LINES, RA_CUSTOMERS, RA_CONTACTS
PO: PO_VENDORS, PO_VENDOR_SITES - For storing vendor data.
43. Name the interface tables used for the LockBox Interface
A Interface table : AR_PAYMENTS_INTERFACE_ALL
Lockbox transfers the receipts that pass validation to the Receivables interim tables AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL
When you run Post QuickCash, the receipt data is transferred from the QuickCash tables to the following Receipt tables:
44 Name the interface tables used for the AutoInvoice Interface.
A AutoInvoice transfers transaction data from the interface tables
into the following Receivables tables:
• RA_CUSTOMER_TRX _ALL
• RA_CUSTOMER_TRX_LINES _ALL
About User Exits
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:
* ORACLE Pre-compiler user exits
* OCI (ORACLE Call Interface) user exits
* Non-ORACLE user exits
You can also write a user exit that combines both the ORACLE Pre-compiler interface and the OCI. User exits can perform the following tasks:
* Perform complex data manipulation
* Pass data to Report Builder from operating system text files
* Manipulate LONG RAW data
* Support PL/SQL blocks
* Control real time devices, such as a printer or a robot
You can use user exits for other tasks, such as mathematical processing. However, Oracle Corporation recommends that you perform such tasks with PL/SQL within Report Builder.
* Not all types of user exits can perform all of the described tasks. You can accomplish most of these tasks only with ORACLE Pre-compiler user exits.
* Some details of implementing user exits are specific to each operating system.
Writing a user exit call
You can call the user exit from any place in which you can enter PL/SQL within Report Builder. Use the following syntax:
* [SRW.REFERENCE(:object2_name_2); ...];
* SRW.USER_EXIT('user_exit_name [argument_list]');
object_name_n Is the name of an Report Builder parameter or column whose value will be passed to the user exit in the user exit string. This causes Report Builder to build a dependency list: it will ensure that each referenced object will contain the most recently computed or fetched value before it is passed to the user exit. Note: You must reference each parameter or column separately. For details, see "SRW.REFERENCE".
user_exit_name Is the name of the user exit to which you are passing control. The user exit name may be any length. (On some operating systems, the name may be at most 6 characters. Check with your system administrator.)
argument_list Can contain the names of parameters and columns, constants, character strings, or any combination thereof, that you wish to pass to the user exit. The argument list may be any length. Note: The above syntax will need to be embedded in a PL/SQL program unit.
Description : This procedure calls the user exit named in user_exit_string. It is useful when you want to pass control to a 3GL program during a report's execution.
SRW.USER_EXIT (user_exit_string CHAR);
user_exit_string :Is the name of the user exit you want to call and any columns or parameters that you want to pass to the user exit program.
Description : This procedure causes Report Builder to add the referenced object to the PL/SQL construct's dependency list. This causes Report Builder to determine the object's value just before firing the PL/SQL construct. This is useful when you want to ensure that a column value passed to a user exit is the most recently computed or fetched value.
SRW.REFERENCE (:object CHAR|DATE|NUMBER);
object Is the Report Builder parameter or column whose value needs to be ascertained before the construct fires.
Description :This procedure displays a message with the message number and text that you specify. The message is displayed in the format below. After the message is raised and you accept it, the report execution will continue.
SRW.MESSAGE (msg_number NUMBER, msg_text CHAR);
msg_number Is a number from one to ten digits, to be displayed on the message line. Numbers less than five digits will be padded with zeros out to five digits. For example, if you specify 123, it will be displayed as SRW-00123.
msg_text Is at most 190 minus the msg_number alphanumeric characters to be displayed on the message line.
Lexical References :
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.
SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE FROM EMP
SELECT ORDID, TOTAL FROM &ATABLE
SELECT ORDID, TOTAL FROM ORD WHERE &CUST
GROUP BY Clause
SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY &NEWCOMM
SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING &MINTOTAL
ORDER BY Clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY &SORT
CONNECT BY and START WITH Clauses
COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.
Bind References :
Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.
SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLAN FROM ORD
SELECT ORDID, TOTAL FROM ORD WHERE CUSTID = :CUST
GROUP BY Clause
SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY NVL(COMMPLAN, :DFLTCOMM)
SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL
ORDER BY Clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE)
Placeholder Columns :
A placeholder is a column for which you set the data type and value in PL/SQL that you define. You can set the value of a placeholder column in the following places:
* The Before Report Trigger, if the placeholder is a report-level column
* A report-level formula column, if the placeholder is a report-level column
* A formula in the placeholder's group or a group below it (the value is set once for each record of the group)
PROCEDURE RUN_PRODUCT (product NUMBER, module VARCHAR2, commmode NUMBER, execmode NUMBER, location NUMBER, paramlist_id VARCHAR2, display VARCHAR2);
Product :Specifies a numeric constant for the Oracle product you want to invoke: FORMS specifies a Runform session. GRAPHICS specifies Graphics Builder. REPORTS specifies Report Builder. BOOK specifies Oracle Book.
module Specifies the VARCHAR2 name of the module or module to be executed by the called product. Valid values are the name of a form module, report, Graphics Builder display, or Oracle Book module. The application looks for the module or module in the default paths defined for the called product.
commmode Specifies the communication mode to be used when running the called product. Valid numeric constants for this parameter are SYNCHRONOUS and ASYNCHRONOUS.SYNCHRONOUS specifies that control returns to Form Builder only after the called product has been exited. The end user cannot work in the form while the called product is running. ASYNCHRONOUS specifies that control returns to the calling application immediately, even if the called application has not completed its display.
Execmode : Specifies the execution mode to be used when running the called product. Valid numeric constants for this parameter are BATCH and RUNTIME. When you run Report Builder and Graphics Builder, execmode can be either BATCH or RUNTIME. When you run Form Builder, always set execmode to RUNTIME.
Location : Specifies the location of the module or module you want the called product to execute, either the file system or the database. Valid constants for this property are FILESYSTEM and DB.
Paramlist_name or paramlist_ID : Specifies the parameter list to be passed to the called product. Valid values for this parameter are the VARCHAR2 name of the parameter list, the ID of the parameter list, or a null string (''). To specify a parameter list ID, use a variable of type PARAMLIST.
Display : Specifies the VARCHAR2 name of the Form Builder chart item that will contain the display (such as a pie chart, bar chart, or graph) generated by Graphics Builder. The name of the chart item must be specified in the format block_name.item_name
PROCEDURE Run_Emp_Report IS
pl_id := Get_Parameter_List('tmpdata');
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
pl_id := Create_Parameter_List('tmpdata'); Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');
Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME,FILESYSTEM, pl_id, NULL);