Wednesday, 21 December 2011

Interview Questions on Oracle SCM,AOL,Forms

1. What is order cycle?

Order cycles describe the processing or routing, through which orders progress. Order cycles contain cycle actions such as Enter, Pick Release or Ship Confirm. Each cycle action has at least one result. For example, results for the action ‘Enter’ include ‘Booked’, ‘Partial’ and ‘Entered’. You can customize your order cycle by defining prerequisites for each action so that Oracle Order Entry performs these actions in the sequence you define. For example, the prerequisite for the action Pick Release could be the action Legal Review and the result Pass. You would not be able to pick release the order until it meets this prerequisite. You can define as many order cycles as you want so you can handle different processing requirements for different types of orders or for orders from different sales channels. For example, you may have one order cycle that requires legal approval that you use for international orders and one without legal approval for domestic orders.

If you make any changes to cycle actions in an order cycle you can determine when those changes take effect by setting the profile option OE: Cycle Action Changes Affect Existing Orders


2. List the various actions of a typical order cycle?
Order cycles contain cycle actions such as ‘Enter’, ‘Pick Release’ or ‘Ship Confirm’, ‘Backorder Release’, ‘Receivable Interface’, ‘Inventory Interface’, ‘Complete Line’, ‘Complete order’.

Enetered -> Book -> Demand/ATP (Available to Promise) -> Pick Release -> Shipping -> Inventory Interface -> Receivable Interface -> Close order

3. What is the relation between an order type and an order cycle?

Order Cycle can differ depending on the type of an order For example you may have one order cycle that requires legal approval that you use for international orders and one without legal approval for domestic orders
Order cycle is attached to the order type.

4. What does Inventory Interface do?

Inventory Interface Program populates the interface tables with transactions submitted through Confirm shipment forms. It updates order lines with shipped quantities and updates inventory.

5. What are ATO and PTO?
ATO - Assemble to Order - A configuration you make in response to customer order which includes optional items.

PTO - Pick to Order - A configure to order environment where the option and included items in a model appear on pick slips and order pickers gather the options when they ship the order. Alternative to manufacturing the parent item on a work order and then shipping it.

6. What is RMA?

RMA is permission for a customer to return items. OE allows you to authorize the return of your sales orders as well as sales made by other dealers as long as the item are part of your item master and price list. Receipt of goods previously sold to customer, credit to a customer or replace with identical item.

7. What is Hold?
A feature that prevents an order or order line from processing through the order cycle. You can place hold on any order or order line.

8. What are various types of holds?

Credit check hold, Legal Review hold, Sales Review Hold, Customer request Hold. (so_holds.type_code)

Few of logitechs hold types:
CREDIT This hold is automatically placed on an order invoiced to a customer who fails credit check
GSA This hold is automatically placed on an order which is in violation of GSA
CONFIGURATOR This hold is automatically placed on a Sales Order Line that fails Configurator Validation
Order Management Product on Short Supply
Order Management Product on Quality Stop Shipment
Order Management Awaiting clarification on Order Detail
Order Management Incomplete Ship To
Credit Management Customer/Order on Credit Hold
Order Management EDI order with incorrect pricing - hold until pricing issue resolved
Order Management EDI order with incorrect QTY (Case Pack) - hold until QTY issue resolved
Order Management Customer Requested Hold
Credit Management Manual Credit Hold on Backorder
Order Management EDI order waiting for information
Document Services Pre Release Hold to applied to Prevent Pick Release
Document Services Pre Release Hold to applied to Prevent Backorder Release
Order Management RMA Order Waiting For Information
Credit Management Overdue Payment Hold by Credit Managment
Order Management Product on Short Supply

9. What is a hold Parameter?

A criterion you use to place a hold on an order or order line. Valid hold parameters are customer, customer site, order, and item. (so_order_holds_view_hold.hold_level)

Site Use

10. What are various types of discounts?
Discount is a reduction of list price for an item. Fixed price discounts - Final price is contractually fixed regardless of fluctuations in list price. For example: item A has a list price of 100, a fixed price discount specifies a selling price of 90, results in a selling price of 90 even if the list price changes to 110.

Price adjustments - The difference between list price of an item and its selling price. Price difference can have positive or negative impact on price list. Price adjustments which lower the list price are called as price adjustment discounts. It can be for order or order line.

Earned discounts -

11. What is scheduling of orders?

Order scheduling includes assigning demand or reservations, warehouses, shipment dates, lots and sub-inventories to order line, it can be don’t on booked or un-booked orders.

12. How do you calculate value of an order (from tables)?
sum((sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) * sol.selling_price)
from so_headers_all sh, so_lines_all sol
where sh.order_number = &order_number
and sol.header_id = sh.header_id

13. What is underlying table for discounts?


14. What is auto-accounting?
An Receivable feature that lets you determine how the Accounting Flex-field for your revenue, receivable, freight, tax, unbilled receivables, and unearned revenue account types are created.

15. What is Auto-invoicing?

A program that imports invoices, credit memos, and on account credits from other system into oracle receivables.

16. What does the table ra_cust_trx_line_gl_dist store?
Stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. This table must have one row for each accounting distribution.

17. What are the underlying tables in various transactions in receivables?


18. What are interface tables used during autoinvoicing interface?

19. If invoice, credit memos and debit memos all get stored in the same table then, how do you come to know which transaction is it?
RA_CUSTOMER_TRX table stores cust_trx_type_id, which identifies the user defined transaction type. Which in turn is stored in RA_CUST_TRX_TYPES. The field TYPE defines the type of transaction, which is linked to LOOKUP_CODE of AR_LOOKUPS table. Basic 6 transaction types are hard coded in AR_LOOKUPS table and are not user maintainable.

20. What is a credit memo?

Credit memo is a document, which partially or fully, reverses an original invoice. You can create credit memos through the Oracle Receivables Enter Credit Memos form or through Auto-Invoice.

21. What is a debit memo?
Debits that you assign to your customer for additional charges that you want to collect. You may want to charge your customers for unearned discounts taken, additional freight charges, taxes, and finance charges.

22. What are the underlying tables for credit memos and debit memos?

23. What is an accounting Rule?

Accounting rule is a rule that Oracle receivables Auto-invoice uses to specify revenue recognition schedules for your transactions. You can define your accounting rule where revenue is recognized over a fixed or variable period of time. For example, you can define a fixed duration accounting rule with monthly revenue recognition for a period of 12 months.

24. What is the difference between an accounting rule and an invoicing rule?
Invoicing Rule: Rules the Oracle receivables uses, to determine when you bill your invoices. You can bill in advance or in arrears.

25. What are the various features of Oracle Receivables?

Multiple System Options

Use optional batching
Customize to meet your needs:
Accounting Flex-field
Item Flex-field
Territory Flex-field
Sales Tax Flex-field
Descriptive Flex-field
Define any chart of accounts using up to 30 segments
Define your own accounting calendar
Choose from a variety of payment terms:
Split terms
Proxima terms
Terms by amount or percent
Multiple discounts
Choose from tax options:
By location or product
Compounding tax
Tax exemptions
Group multiple, conditional taxes
Sales Tax or VAT environment
User-definable tax hierarchy


Specify customer relationships
Validate customer addresses
Enter addresses in country-specific formats
Enter an unlimited number of:

Addresses, foreign or domestic
Remit-to addresses
Business purposes
Contact names
Telephone numbers

Create customer credit profiles:

Payment terms
Automatic receipt information
Finance charges

Copy credit profiles
Define sales territories
Default pertinent customer site information to transactions
Query customers using variable criteria


Create sign-insensitive transactions
Number invoices and lines automatically
Recognize revenue over multiple periods using invoice rules
Create recurring invoices
Create consolidated billing invoices based on periods defined by payment
Specify transaction type, including:

Debit memo

Create credit memos, including:

Full and partial credit
Automatic sales credit reversal
On-account credit

Handle write-offs automatically or manually
Set adjustment limits
View transaction balances
View accounting entries on-line
Print invoices at any time


Record customer calls
Print customer statements
Create dunning letters based on days overdue or dunning levels
Define unlimited number of user-defined dunning letter templates
Produce statements based on user-definable cycles
Reapply cash
Perform inquires on-line or on the web
Create flexible agings
Drill down to detail by aging bucket
Inquire using external references such as project and order number
View correspondence history on-line
Create centralized statement sites
Create centralized dunning sites

Cash Application

Record payments from third parties
Create chargebacks
Process receipts automatically:

Lockbox transmission
Bills of exchange
Direct debits

Match receipts to transactions by:

Transaction Number
Order Number
Purchase Order Number
Consolidated Bill number
Custom Number

Use AutoCash rules:

Apply to oldest invoice first
Exact match on invoice
Clear past due invoices
Clear the account
Clear Past Due Invoices grouped by Payment Term

Apply payments according to user-defined rule sets
Use earned and unearned discounts
Discount on specified line types
Track bank remittances
Clear bank statement items automatically or manually
Use flexible lockbox formats
Create bank charges automatically
Reapply cash
Reverse multiple receipts in a single step
Process Notes Receivable

Future-dated checks
Promissory Note

Global Accounting

Record sales and VAT taxes with complete tax reporting
Issue letters of credit
Handle bills of exchange and automatically eliminate risk
Use optional sequential numbering
Enter multiple currencies using user-defined currency formats
Process cross-currency receipts
Calculate currency gains and losses
Enter cross-currency deposits
Default tax code from revenue segment of natural account
Enter and print line amounts that include tax
Report in multiple currencies


View reports on-line
Report by company
Create accounting reports of agings, receipts, cash application, and
Use Report eXchange to customize reports:

Query selected information
Export to other applications

Create flexible agings, including:

Customizable aging buckets
Dispute bucket
Customer and invoice summary
By amount and salesperson

Additional reports:

Transaction registers
Tax reports
Credit and collections reports
Key indicators
Supplier/Customer Netting report
Foreign Exchange Gain/Loss report

26. Does an RMA (order return) automatically generate credit memos?
Yes, Receivable Interface of RMA will generate credit memos. If the RMA Interface results Partially Accepted or Completely Accepted are prerequisites to the Receivables Interface in the order cycle, only quantities of the item that have been received in a sub-inventory are credited. Items, which are received for purposes of inspection, are not eligible to be credited unless they pass inspection and are received into a sub-inventory. Thus if the prerequisite for the Receivables Interface includes RMA Interface - Partially Accepted, then the Receivables Interface creates partial credits corresponding to the accepted quantity that has not already been credited. If the prerequisite for the Receivables Interface is only RMA Interface - Completely Accepted, the Receivables Interface waits until the full quantity is accepted and then creates a full credit. If the RMA Interface is not a prerequisite to the Receivables Interface in the order cycle, the full return quantity entered on the RMA line is eligible to be credited.

27. What does form registration do?
28. Why do you need form registration?
29. If I add certain code to a form and don't register it what will happen?
30. If I add a column to a form and don’t register it what will happen?

After you paint your form, register your form with Oracle Application Object Library. In fact, you must register any form you wish to run under Oracle Application Object Library. By registering your form, you assign it to a particular application and let Oracle Application Object Library add form-level triggers your form needs to support EasyForm and such features as Menus, QuickPick, and help text. You should register your forms again after you make any changes to their structures such as adding or deleting fields or zones. You can reregister your form as many times as you want without harming it.

You can use the Update Form Information form to register any changes you make to the blocks and fields of your form, or to change the name that appears in QuickPicks.

31. What are the various steps in developing a report?

After getting a valid specification, decide on the oracle tool in which report could be developed, .if its SQLreport then -
1. Write SQL statement
2. Build layout
3. Put script in CUSTOM_TOP directory
4. Register it

If its Oracle Reports (6i) then -
1. Build a data model. data model includes query, summary columns, formula columns, placeholders, groupings etc
2. Paint the layout
3. Write triggers to enforce business rules
4. Create parameters
5. Put script in CUSTOM_TOP directory
6. Register it

32. How do you call a user exit?

From Forms: - ?
From Reports: - srw.user_exit ("user_exit_name( parameter1,parameter2,...)");

33. What is anchoring?

Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.


1. An object may be anchored to only one other object.
2. Matrix objects, anchors, and the margin cannot be anchored to anything (i.e., they may not be the parent or child object for an anchor).
3. A repeating frame that is the vertical or horizontal repeating frame for a matrix cannot be anchored to another object, but other objects may be anchored to it (i.e., it can be the parent but not the child object for an anchor).
4. Nothing can be anchored to a hidden object (an object with Hidden checked).
5. Moving an anchor also cause the two objects it anchors together to move.
6. Objects cannot be anchored together in such a way that they have a circular dependency. For example:
· Assume that object A and object B are anchored together and object A is the parent. Object B cannot be the parent for another anchor between object A and object B. In addition, object B cannot be the parent for an anchor to object C, if object C is the parent for an anchor to object A.
· Assume that frame A contains field B and field B is above frame A in the editor. Furthermore, frame A is anchored to field C, which is not inside of frame A, and field C is the parent. Field C cannot be the parent for an anchor to field B. Because it is inside of frame A, field B cannot be anchored to field C, if field C is the parent.
7. To copy an anchor, you must select the anchor and the two objects it anchors together. If you select the anchor by itself, nothing will be copied to the paste buffer. If you select the anchor and one of the objects, only the object is placed in the clipboard.
8. An anchor cannot be resized.
9. An anchor must always be on top of the objects it anchors together (i.e., it must be one or more layers above the parent and child objects). Oracle Reports prevents you from moving the anchor to a layer below its parent and child objects.
10. You cannot use Align, Align Objects, or Size Objects from the Arrange menu on anchors.

34. What are format triggers?

Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.


Caution: The PL/SQL in a Format Trigger is executed each time that Oracle Reports attempts to format the layout object. As a result, format triggers should only contain PL/SQL program units that set formatting attributes (e.g., color and highlighting). You should not perform other actions, such as inserting data in a table, because you cannot predict when or how many times the trigger will fire. For example, if you have Page Protect checked for an object, the object might not be formatted on the logical page where the trigger is fired. In addition, the trigger may be executed more than once.

1. Comments inserted directly into the PL/SQL code must use the PL/SQL comment delimiters.
2. In a Format Trigger, you can read the values of Oracle Reports columns and parameters of the correct frequency (look at the rule below), but you cannot directly set their values. For example, you can use the value of a parameter called COUNT1 in a condition (e.g., IF :COUNT1 = 10), but you cannot directly set its value in an assignment statement (e.g., :COUNT1 = 10). (This restriction also applies to user exits called from the Format Trigger.)
3. Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not supported. If you do this, you may get unpredictable results.
4. You cannot reference columns or variables in the Format Trigger of an object that have a different frequency than the object. For example, if you create a master/detail report, the parent group's repeating frame cannot have a Format Trigger that relies on a value in the child group. For each parent, there may be multiple children. Therefore, at the parent record level, Oracle Reports cannot determine which of the child records to use. You also cannot reference any page-dependent columns (i.e., Reset At of Page) or columns that rely on page-dependent columns in a Format Trigger. The reason for this is that it would result in a circular dependency. That is, the value of a page-dependent column cannot be computed until the report is formatted, but the report cannot be formatted until the Format Trigger is executed.
5. If a Format Trigger returns false and the object does not format, this can cause other objects not to print. For example, if a repeating frame does not format, any objects (fields, boilerplate, frames, or other repeating frames) it encloses would not format either.
6. For repeating frames, the Format Trigger is executed for each instance of the repeating frame. To create a Format Trigger that acts upon all instances of a repeating frame at once, create a frame around the repeating frame and enter a Format Trigger for the frame. If the Format Trigger returns FALSE for every instance of a repeating frame on a logical page, the repeating frame will occupy no space on the logical page and anchors to other objects will collapse (if specified).
7. The PL/SQL in a Format Trigger must return consistent results for the same object. For example, say you have a frame whose Format Trigger returns FALSE when a certain condition is met. If the frame spans two pages, the Format Trigger actually fires twice (once for each page on which the frame formats). The condition in your PL/SQL must return the same result both times the Format Trigger fires. Otherwise, only part of the frame will be formatted (e.g., the part of the frame on the first page formats, but the part on the second page does not).
8. If the Format Trigger on a repeating frame in a matrix report returns FALSE, an entire row or column of the matrix will not format. For example, if an instance of the across dimension repeating frame does not format, the entire column will not format in the matrix.
9. If you want to conditionally change the cell of a matrix, you should put a frame around the field inside the matrix and use the Format Trigger for the frame.

35. What is a user Exit?

A user exit is a routine your form calls to perform application processing. Occasionally, you may need to build your own user exit to perform application processing that is beyond the scope of SQL or SQL*Forms commands. Oracle Application Object Library helps you build a user exit in a third generation language (such as Pro*C) and integrate it into a standard user exit library.

36. How can you increase the performance of reports?
1. Writing straight forward and plain data fetching Queries.
2. Utilizing a correct combination of indices on tables to fetch data.
3. In case of Oracle reports (6i) reduce the number of User exits, format triggers, formula columns, summary columns etc.
4. In case of Oracle reports (6i), instead of writing a complex query, break it up into multiple simple queries and link them up with data-links.

38. What is Explain Plan?
Explain Plan analyzes the query and breaks it up in to the steps which oracle follows to fetch the data.


To determine the execution plan Oracle follows to execute a specified SQL statement. This command inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this command also determines the cost of executing the statement.

39. How did you optimize your SQL after using Explain Plan?
0. Eliminate FULL table scans.
1. Forcing appropriate indices.
2. Rearranging the table names in from clause.
3. Doing away with functions in where clause.
4. Use EXISTS where ever possible instead of sub-queries.

40. What is TKPROF?

TKPROF is a toll by Oracle to format the trace file output. By passing different parameters you can control the contents of the output file.

Use the following arguments with TKPROF:

filename1 specifies the input file, a trace file containing statistics produced by the SQL trace facility. This file can be either a trace file produced for a single session or a file produced by appending together individual trace files from multiple sessions.

filename2 specifies the file to which TKPROF writes its formatted output.

EXPLAIN determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. TKPROF determines execution plans by issuing the EXPLAIN PLAN command after connecting to ORACLE with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges.

TABLE specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table already exists, TKPROF deletes its rows, uses it for the EXPLAIN PLAN command, and then deletes its rows. If this table does not exist, TKPROF creates it, uses it, and then drops it.

The specified user must be able to issue INSERT, SELECT, and DELETE statements against the table. If the table does not already exist, the user must also be able to issue CREATE TABLE and DROP TABLE statements.

For these privileges to issue statement, see the ORACLE7 Server SQL Language Reference Manual.

This option allows multiple individuals to concurrently run TKPROF with the same user in the EXPLAIN value. These individuals can specify different TABLE values and avoid destructively interfering with each other's processing on the temporary plan table.

If you use the EXPLAIN parameter without the TABLE parameter, TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter. If you use the TABLE parameter without the EXPLAIN parameter, TKPROF ignores the TABLE parameter.

INSERT creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.

SYS enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements into the output file. The default value of YES causes TKPROF to list these statements. The value of NO causes TKPROF to omit them.

Note that this parameter does not affect the optional SQL script. The SQL script always inserts statistics for all traced SQL statements, including recursive SQL statements.

SORT sorts the traced SQL statements in descending order of the specified sort option before listing them into the output file. If more than one option is specified, the output is sorted in descending order by the sum of the values specified in the sort options. If you omit this parameter, TKPROF lists statements into the output file in ascending order of when each was first issued.

The sort options are:

PRSCNT number of times parsed
PRSCPU CPU time spent parsing
PRSELA elapsed time spent parsing
PRSDSK number of physical reads from disk during parse
PRSQRY number of consistent mode block reads during parse
PRSCU number of current mode block reads during parse
PRSMIS number of library cache misses during parse
EXECNT number of executes
EXECPU CPU time spent executing
EXEELA elapsed time spent executing
EXEDSK number of physical reads from disk during execute
EXEQRY number of consistent mode block reads during execute
EXECU number of current mode block reads during execute
EXEROW number of rows processed during execute
EXEMIS number of library cache misses during execute
FCHCNT number of fetches
FCHCPU CPU time spent fetching
FCHELA elapsed time spent fetching
FCHDSK number of physical reads from disk during fetch
FCHQRY number of consistent mode block reads during fetch
FCHCU number of current mode block reads during fetch
FCHROW number of rows fetched
PRINT lists only the first integer sorted SQL statements into the output file. If you omit this parameter, TKPROF lists all traced SQL statements. Note that this parameter does not affect the optional SQL script. The SQL script always inserts statistics for all traced SQL statements.

Example This example runs TKPROF, accepts a trace file named KERVMS_RPK2_FG_SQLDBA_007.TRC and writes a formatted output file named OUTPUTA.PRF:



41. What is SQL trace?

The SQL trace facility provides performance information on individual SQL statements. The SQL trace facility generates the following statistics for each statement:

· Parse, execute, and fetch counts
· CPU and elapsed times
· Physical reads and logical reads
· Number of rows processed
· Misses on the library cache

42. What does so_picking_batches contain?

A user-defined group of pickslips for orders that you release for picking all at once. You create picking batches when you release your orders for shipping. For example, a batch can contain all shipments for a specific warehouse, or all priority shipments regardless of warehouse.

SO_picking batches contains picking batch name and other information depends on the values entered on pick release screen from the following fields like warehouse, customer, sub-inventory, order etc. user may enter combination of these values and create a picking batch. Which in turn will fire an concurrent program which populates the so_picking_headers, so_picking_lines, so_picking_line_details tables and prints picking slips for the batch.

43. How do you call a flex-field from a report?

44. What all reports have you customized in OE?

1. Printing sales orders in European languages.

45. Mention any report customization you have done?

46. How do you register a report?

1. Create the Executable
2. Create concurrent program
2.1 Assign the executable, which you have created.
2.2 Create the parameters to be passed to the report.
2.3 Attach value set to each and every parameter. If value set not there you have to create one.
2.4 List incompatible program if any.

47. What is a responsibility?

Use this form to define a responsibility. A responsibility determines how much of an application's functionality a user can use, what reports and concurrent programs the user can run, and which applications' data those reports and concurrent programs can access.

48. How do you create a responsibility?
Navigate Security Responsibility Define

1. Specify the application in which you want to create responsibility.
2. Specify the data group by which user will login to database.
3. Specify the main Menu which user will see on the screen.
4. Specify the first screen user would see after the successful login.
5. Assign the report security group which decides which reports user has access to.(Optional)

49. What tables does demand interface update?


50. What is canvas?
Canvas-views are the background objects on which you place the interface items (text items, check boxes, radio groups, etc.) and boilerplate objects (boxes, lines, images, etc.) that operators interact with as they run your form. Each canvas-view is displayed in a window.

51. How do you transfer data from legacy system to oracle financials?
· Transfer the data into ASCII files from Legacy system.
· Upload the data from the ASCII files into Interface Tables using SQL*loader.
· Import the data, which is existing in the interface tables to the Oracle's base tables after necessary validations.

53. What are lexical parameters?
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.


1. You cannot make lexical references in a PL/SQL statement.

2. If a column or parameter is used as a lexical reference in a query, its data type must be Character.

3. If you want to use lexical references in your SELECT clause, you should create a separate lexical for each column you will substitute. In addition, you should assign an alias to each lexical reference. This enables you to use the same layout field and boilerplate label for whatever value you enter for the lexical on the Runtime Parameter Form. See the example below.

4. If you use lexical references in your SELECT clause, you must specify the same number of items at runtime as were specified in the report's data model. In addition, each value you specify for your lexical references at runtime must have the same data type as its Initial Value. Look at the example below.

5. An Oracle Reports link should not depend upon a lexical reference. That is, neither the child column of a link or its table name should be determined by a lexical reference. To achieve this functionality, you need to create a link with no columns specified and then enter the SQL clause (e.g., WHERE) for the link directly in the query. For example, your parent and child queries might be written as follows:


Child Query: SELECT &PARM_1 COL_1, &PARM2 COL_2

Note how the WHERE clause makes a bind reference to DEPTNO, which was selected in the parent query. Also, this example assumes that you have created a link between the queries in the Data Model editor with no columns specified.

6. A lexical reference cannot be used to create additional bind variables after the After Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):



If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger:

WHERE SAL = :new_bind

If you supplied this same value in the After Form trigger, the report would run.

Following are some examples of lexical references:



P_ENAME, P_EMPNO, and P_JOB can be used to change the columns selected at runtime. For example, you could enter DEPTNO as the value for P_EMPNO on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT list will not match the Oracle Reports columns and the report will not run.

FROM Clause


ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime. If you dynamically change the table name in this way, you may also want to use lexical references for the SELECT clause (look at the previous example) in case the column names differ between tables.

WHERE Clause


CUST can be used to restrict records retrieved from ORD. Any form of the WHERE clause can be specified at run-time.



The value of NEWCOMM can be used to define the GROUP BY clause.



The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.



The value of SORT can be used to select SHIPDATE, ORDERDATE, ORDID, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY and START WITH clause.


Parameters in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.

Multiple 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.


WHEREORD could be used to change both the WHERE and ORDER BY clauses at runtime. For example, you could enter WHERE SAL > 1000 ORDER BY DEPTNO for &WHEREORD at runtime.



BREAK_COL is used to change both the SELECT list and the GROUP BY clause at runtime. The Initial Value of the parameter &BREAK_COL is JOB. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN (of Data type Character). In the Validation Trigger for GROUP_BY_COLUMN, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN:

procedure conv_param (in_var IN char) is

if upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then
:break_col := 'to_char('in_var')' ;
:break_col := in_var;
end if;

This PL/SQL ensures that, if necessary, a TO_CHAR is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference to BREAK_COL. In PL/SQL, you must make a bind reference to BREAK_COL because lexical references are not allowed in PL/SQL.

54. How do I populate a POPlist with a record group at runtime?
POPULATE_LIST(list_id, recgrp_id);

POPULATE_LIST(list_id, recgrp_name);
POPULATE_LIST(list_name, recgrp_id);
POPULATE_LIST(list_name, recgrp_name);

Built-in Type

Unrestricted procedure

Enter Query Mode: yes

Removes the contents of the current list and populates the list with the values from a record group. The record group must be created at runtime and it must have the following two column (CHAR) structure:

Column 1: Column 2:

The list label the list value


List_id Specifies the unique ID that Oracle Forms assigns when it creates the list item. Use the FIND_ITEM built-in to return the ID to an appropriately typed variable. The data type of the ID is ITEM.

list_name The name you gave to the list item when you created it. The data type of the name is CHAR.
recgrp_id Specifies the unique ID that Oracle Forms assigns when it creates the record group. The data type of the ID is RecordGroup.
recgrp_name The CHAR name you gave to the record group when you created it.
Usage Notes:

· Do not use the POPULATE_LIST built-in if the Other Values property is defined and there are queried records in the block. Doing so may cause Oracle Forms to be unable to display records that have already been fetched.

For example, assume that a list item contains the values A, B, and C and the Other Values property is defined. Assume also that these values have been fetched from the database (a query is open). At this point, if you populate the list using POPULATE_LIST, an error will occur because Oracle Forms will attempt to display the previously fetched values (A, B, and C), but will be unable to because these values were removed from the list and replaced with new values.

· Before populating a list, close any open queries. Use the ABORT_QUERY built-in to close an open query.

POPULATE_LIST returns error FRM-41337: Cannot populate the list from the record group if:

· The record group does not contain either the default value element or the other values element and the list does not meet the criteria specified for deleting these elements with DELETE_LIST_ELEMENT. Refer to the restrictions on DELETE_LIST_ELEMENT for more information.
· The record group contains an other value element but the list does not meet the criteria specified for adding an other value element with ADD_LIST_ELEMENT. Refer to the restrictions on ADD_LIST_ELEMENT for more information.


** Built-in: POPULATE_LIST
** Example: Retrieves the values from the current list item
** Into record group one, clears the list, and
** Populates the list with values from record group
** Two when a button is pressed.
** Trigger: When-Button-Pressed
Retrieve_List(list_id, 'RECGRP_ONE');
Populate_List(list_id, 'RECGRP_TWO');

55. How do I call reports from forms?
56. What are various parameters of Run_product built-in?

RUN_PRODUCT(product, document, commmode, execmode, location,
list, display);

RUN_PRODUCT(product, document, commmode, execmode, location,
name, display);

product Specifies a numeric constant for the Oracle product you want to invoke: FORMS specifies a Runform session. GRAPHICS specifies Oracle Graphics. REPORT specifies Oracle Reports. BOOK specifies Oracle Book.

Document Specifies the CHAR name of the document or module to be executed by the called product. Valid values are the name of a form module, report, Oracle Graphics display, or Oracle Book document. The application looks for the module or document 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 Oracle Forms only after the called product has been exited. The operator 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 Oracle Reports and Oracle Graphics, execmode can be either BATCH or RUNTIME. When you run Oracle Forms, always set execmode to RUNTIME.
Location Specifies the location of the document 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.
List or name Specifies the parameter list to be passed to the called product. Valid values for this parameter are the CHAR name of the parameter list, the ID of the parameter list, or NULL. To specify a parameter list ID, use a variable of type PARAMLIST.
Note: You can pass text parameters to called products in both SYNCHRONOUS and ASYNCHRONOUS mode. However, parameter lists that contain parameters of type DATA_PARAMETER (pointers to record groups) can only be passed to Oracle Reports and Oracle Graphics in SYNCHRONOUS mode. (SYNCHRONOUS mode is required when invoking Oracle Graphics to return an Oracle Graphics display that will be displayed in a form chart item.)

Note: You can prevent Oracle Graphics from logging on by passing a parameter list that includes a parameter with key set to LOGON and value set to NO.

Note: You cannot pass a DATA_PARAMETER to a child query in Oracle Reports. Data passing is supported only for master queries.

display Specifies the CHAR name of the Oracle Forms chart item that will contain the display (such as a pie chart, bar chart, or graph) generated by Oracle Graphics. The name of the chart item must be specified in the format block_name.item_name. (This parameter is only required when you are using an Oracle Graphics chart item in a form.)

** Run the report synchronously, passing the parameter list
Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME,

57. If the first field of a form is required, and I want to tab out without entering any value, how do I do that?

For user:
Press Shift + Tab, it allows to navigate out of the required current field.
For developer:

Built-in Type: unrestricted procedure

Enter Query Mode: yes

VALIDATE forces Oracle Forms to immediately execute validation processing for the indicated validation scope.


validation scope Specify one of the following scopes:

DEFAULT_SCOPE Perform normal validation for the default scope, determined by the runtime platform.

Note: If you change the scope via SET_FORM_PROPERTY(VALIDATION UNIT) and then call VALIDATE(DEFAULT_SCOPE), you will override the default scope as defined in the form module. In this case, Oracle Forms will not validate at the default scope but at the scope defined by SET_FORM_PROPERTY.

FORM_SCOPE Perform normal validation for the current form.

BLOCK_SCOPE Perform normal validation for the current block.

RECORD_SCOPE Perform normal validation for the current record.

ITEM_SCOPE Perform normal validation for the current item.

58. What is a complex report you have done?

59. What are the prerequisites of a Matrix report?

Data Model Requirements You must have a matrix, nested matrix, or matrix break data model. The data model must have at least four groups, one of which must be a cross product group. For more information, see Building Reports Manual.

Note: For data models that require a break group, you should create the break group before creating the cross-product group. If the cross-product group already exists, delete it, create the break group(s), and then recreate the cross-product group.

Matrices and Summaries # When creating summaries that will appear within the matrix, the summary must be owned by the cross product group and must have Product Order specified. Oracle Reports uses the Product Order to determine where to place the fields for the summaries in the layout. (Note that summaries owned by the cross-product group cannot have a Reset At or Compute at of Page or rely on columns that do.)

Default Layout Requirements You must select the cross product group, and at least two of the groups inside of it.

Matrix Report Defaulting

This section is divided into six sections:

· Matrix defaulting without summaries
· Matrix defaulting with summaries
· Nested matrix defaulting without summaries
· Nested matrix defaulting with summaries
· Matrix break defaulting without summaries
· Matrix break defaulting with summaries

Matrix Defaulting without Summaries

Assume you only have one group tree, you selected the cross product group, two groups inside of it, and one group below it. Assume that each group contains one column.

Generated Objects: Your Layout editor will contain the following objects:

B_columnname For the label of each selected column in the Default Layout dialog box. (B_ implies it is a boilerplate object.)

F_columnname For each column that you selected in the Default Layout dialog box. (F_ implies it is a field.)

R_groupname For each group that you selected in the Default Layout dialog box. (R_ implies it is a repeating frame.)

M_groupname_HDR For each group that you selected in the Default Layout dialog box. (M_ implies it is a frame; HDR implies it is a header frame. M_groupname_HDR is used to protect field labels from being overwritten by other objects in the report.)

X_groupname For each pair of repeating frames that intersect to form the matrix. By multiplying the number of across dimensions by the number of down dimensions, you can calculate the number of matrix objects created. In this case, one dimension going down multiplied by one dimension going across yields one matrix object.

60. How do I call a backend stored procedure from forms?

Just call the stored procedure in forms by its name and passing required parameters.

61. How do I handle stored Procedure errors from forms?
If error not handled in SP then error will propagate in forms.
1. If u know the error then display appropriate message in on-error.
2. If u don’t know the error, then get error code and text which oracle gives DBMS_ERROR_CODE, DBMS_ERROR_TEXT and display the contents.

62. If I am calling a report from forms & if the report query is ‘select * from emp’ and dynamically at runtime I want to change it to ‘select * from dept’ can I do it? How?

Yes, it is possible.
1) The data model should be prepared in the most generic format possible i.e. using lexical parameters like &QUERY. For ex. Select maximum number of dummy columns with fixed column aliases from dual should be the default value for the lexical parameter “QUERY”.
2) After accepting the report query from the user through a user parameter (say RQ).
3) Write a code in either After parameter form OR Before report triggers to extract table name (say TN) from RQ. Query up the columns from the data dictionary (table ‘COL’)for the table name TN.
4) Build the report query using the column names that are queried up and the table name that is extracted. Don’t forget to add the fixed column aliases that were used to form the data model. Apply the to_char function to all those non character fields that are part of the report query.
5) Pass the newly built report query to the data model via the lexical parameter “QUERY”.
6) The following are the limitations:
a) The number of columns in the report layout is limited by the maximum number of columns for which the data model is designed and the report lay out is painted.
b) The tedious hard coding part involved in building the new query for the report in either of the 2 After parameter form OR Before report triggers.

63. If we have same triggers at multiple levels i.e. Form, Block and Item then what is the default order of firing the triggers?
1. Item
2. Block
3. Form

64. What triggers get fired first? When-new-form-instance or when-new-item-instance?
1. When-new-form-instance
2. When-new-item-instance

65. What all AOL features have you used?

66. What all steps have to be done if I have to add a Quick Pick to a form, register it and run it in Oracle financials Application?
1) Get the registered form from the top directory and add a couple of steps in the FND_STARTUP trigger of the form. The steps are as follows:
a) Define an LOV. E.g.
COLUMN="\" Unit of Measure \"(15)"
b) attach it to the Field as one of the steps in FND_STARTUP trigger.
a) Add a new trigger defining a customized LOV.
XXX_QP (or an unnamed step of your form-level EasyForm declaration trigger at form level)

where XXX are three or more characters you choose

Create user defined trigger as follows :
Syntax, Step 1
SQL="SQL select statement"
[QVALIDATION="SQL logic for query validation"]
[EVALIDATION="SQL logic for entry validation"]
COLUMN="column1(length), column2(length),..."
[TITLE="{title text*application shortname:message name}"]
[HEADING="{heading1(length), heading2(length),...
*application shortname:message nameN}"]
[WINDOW="xorigin, yorigin, width, height"]

Add a FND_LISTVAL trigger specifying your LOVNAME with the field to which it has to be attached to, at item level as follows :
FLDNAME=":first_block.ename" LOVNAME="LOV1";

c) Add FND_FKEY trigger calling #EXEMACRO EXETRG “user trigger name” this trigger is called from a form level standard PRE_FIELD trigger. This FND_FKEY trigger queries and fetches data based on the LOV definition and lights up the PICK lamp in the message line. This FND_FKEY should be at item level.
d) Register the form. Responsibility “Application Developer” and the navigation path is \Navigate Application Form Register

67. How do I attach a form to my own menu?
\Navigate Application Menu
Specify application name, menu name and move to next zone. For a menu item attach a object, specify TYPE as FORM. Specify under which application the form is registered into and Save changes.

68. Tell any complex report you have developed?

69. What all features of reports have you incorporated in your report?
70. Tell any complex form you have developed?

71. What all features of forms you have incorporated in your form?
72. What all are steps required to pass a flex field as a parameter to a report?

A value set of validation type special or pair has to be created, in which we have to actually invoke the flex field. Then when you specify report parameters in concurrent program definition, specify the value set which is to be used for parameter validation. Depending on the value set you use for validation, flex field popup will appear. Parameters are passed to the report as a string. APPS in-built user exit will interpret the string and will return a formatted WHERE CLAUSE in form of a lexical parameter which we have to attach to the query.

73. What technical features (non-functional) have you practically used in Oracle Applications?
Context sensitive help, zoom, quickpick, quickcode, descriptive flex-field, report sets, menu creation, responsibility, concurrent program, registering tables, columns, forms, report, executable

74. What is pick release? Which tables are used in pick release?

Pick Release is an order cycle action to notify warehouse that orders are ready for picking. Picking is a process of withdrawing items from inventory to be shipped to the customer.

Tables used are

75. How do you release back ordered items?
Backorder is unfulfilled customer order or commitment. When you pick release an order there is a field for backorder if you specify backorder to INCLUDED or ONLY the back order quantity will be picked for shipment.

This value can be either of following :

Include Releases all backordered order lines that meet your criteria and then all unreleased order lines that meet your criteria.

Exclude Releases all unreleased order lines that meet your criteria (excluding backorders).

Only Releases only backordered lines that meet your criteria.
Unfulfilled order line details which have failed to be released at least once by Pick Release or have been backordered by Ship Confirm.

76. What is stored in SO_PICKING_BATCHES?

Stores batch information for the orders, which are due for pick release. It stores item id, warehouse, sub-inventory, shipment method, shipment priority, ship set no, order type, etc.

77. Which tables stored order holds information?
Tables storing hold information are

78. How do you modify descriptive flex field?

1. For changing segments of descriptive flex-field
From AOL select
\Flex-field descriptive segments

You have to UN-FREEZ flex-field and then change the segments. Once you are through with modifications, set it back to FREEZ. This will fire a concurrent request which will in turn change the descriptive flex field to reflect the changes.

2. For changing value sets attached to flex-field
\Flex-field descriptive values.

Specify the application, form, zone, flex-field name and change value sets in the next zone. Save the screen to reflect the new value set.

79. How does explain plan inform that a particular index be used for query execution?
You can pass hint for the optimizer to use the specified index. Hint is passed in the SQL as /* hint */ where hint is the index name to be used.

80. Name the table where demand interface data is stored?
Please refer to question No 48

81. How to register a form, reports?

Refer to question 46 and 27 to 30 for answer.

82. Where will you keep host source code?

83. What is flex field?

Flex-field An Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flex-fields to capture information about your organization. There are two types of flex-fields: key flex-fields and descriptive flex-fields.

84. What is segment qualifier?
Segment Qualifier

A segment qualifier identifies a particular type of value in a single segment of a key flex-field. In the Oracle Applications, only the Accounting Flex-field uses segment qualifiers. You can think of a segment qualifier as an "identification tag" for a value. In the Accounting Flex-field, segment qualifiers can identify the account type for an account segment value, and determine whether detail posting or budgeting are allowed for a particular value.

It is easy to confuse the two types of qualifiers. You should think of a flex-field qualifier as something the whole flex-field uses to tag its pieces, and you can think of a segment qualifier as something the segment uses to tag its values.

85. What are global segments?

Descriptive flex fields have two different types of segments, global and context-sensitive, that you can decide to use in a descriptive flex field structure. A global segment is a segment that always appears in the descriptive flex field pop-up window, regardless of context (any other information in your form).

86. What are context sensitive segments?
Descriptive flex fields have two different types of segments, global and context-sensitive, that you can decide to use in a descriptive flex field structure. A context-sensitive segment is a segment that may or may not appear depending upon what other information is present in your form.

87. How to implement flex fields in forms, name the triggers and user exits?
For inserting data for a Flex-Field
a) Trigger KEY_PREFIELD calls POPID user exit for invoking flex field. This is written at the field level.
b) Triggers FND_PRE_INSERT and /or FND_PRE_UPDATE calls VALID user-exit for validating the user entered flex field. These are written at the block level.
For queried data for a Flex-field
a) Trigger FND_PRE_QUERY calls POPID user exit for invoking flex field window and FND_POST_QUERY calls LOADID user exit for loading segment descriptions when user Queries.

88. Why is FND CURRENCY used in reports?
It is FND FORMAT_CURRENCY user exit for a report, which displays mixed currency values. Information about the radix character and thousands separator are determined from the location of the user. The special display for negative and positive currency values is specified by two profile options. Hence, a report can appear differently depending upon the location of the user and the profile options values set.

In Germany the radix character and thousand separator are switched from the US counterpart. The negative numbers display in parentheses () or angle brackets <\> depending upon the user's preference.

NOTE: It does not convert one currency into another but just formats the amounts as per location.

89. What is the difference between quick pick and quick code?

Overview of QuickPick and QuickCodes

QuickPick is a powerful Oracle Application Object Library feature that makes your users more productive by letting them enter data in shorthand form and by ensuring that they enter valid data. QuickPick also makes it easy for you to create foreign key relationships in your forms. You can design more intuitive forms by displaying descriptive names and hiding internal identifiers. For example, when entering the sales representative responsible for a client, your user can enter the representative's name, instead of the representative's employee ID or some other identifying code.

You can also use QuickPick to enforce separate validation rules for data entry and data query. For example, you can specify that when entering a currency name, your user can only chose from currency names that are enabled and currently active. When querying data, you users sees all enabled and disabled currency names.

If you define a QuickPick on a field, your form displays a PICK lamp to show your user that QuickPick is available.

QuickCodes let you define general purpose, static lists of values for form fields. QuickCodes allow you to base your program logic on codes while displaying user-friendly names in a QuickPick window. QuickCodes simplify name and language changes by letting you alter the names your users see, while the codes in your underlying forms and programs remain unchanged.

QuickCodes share all the powerful features of QuickPick, such as AutoSelection and AutoReduction. QuickCodes also use separate validation criteria for data entry and data query, although for QuickCodes this validation happens automatically, based on start and end dates and an enabled flag.

From your end user's point of view, a QuickCodes field looks and acts just like a QuickPick field. However, due to the static nature of QuickCodes lists of values, Oracle Application Object Library processes them up to ten times faster than ordinary QuickPick lists, resulting in improved performance for your users.

For example, suppose your form contains several fields that accept the values Yes and No. Instead of writing triggers that explicitly check for each of these values (and their partial values like Y or N), you can write QuickCodes triggers that refer to the QuickCodes type YES_NO. If your end user enters Yes or No (or their partial values), QuickCodes returns a code to your form to indicate the value entered.

You can also use QuickCodes on fields that accept longer lists of values, such as U.S. state names. Your end users can add values to lists you define. Your end users can also specify a starting date, an ending date, and whether a particular QuickCodes value is enabled.

There are two types of QuickCodes. Some QuickCodes are shared among several applications, and are always pre-seeded. We recommend you do not create new QuickCode types for the pre-seeded QuickCodes. You can maintain these QuickCodes using the Define QuickCodes form.

Special QuickCodes, which are maintained from the Define Special QuickCodes form, allow you to create new QuickCode types. In this chapter, we discuss maintaining and developing Special QuickCodes, usually referred to simply as QuickCodes.

90. What is auto reduction?
AutoReduction automatically shortens the list to only those values beginning with the character(s) your users type.

91. What are special triggers created in the form when you register it?

Refer question 27-30.

92. How will you display a message in the form that you have modified for testing the form?

93. How can you see the global parameter values in the form without coding?
You should have developer menu to do this. You have a Examine option which lists all form level, system level and global variables. You can see values of all of them without coding for it.

94. How to debug the form?

You should have developer menu to do this. There is an option to set Debugger On and Off. It will show you which trigger is getting fired and you can debug the relevant trigger.

95. What was the structure of accounting flex field in your previous project?
Cost Center

96. What is balancing segment?

Balancing segment An Accounting Flex-field segment that you define so that Oracle General Ledger automatically balances all journal entries for each value of this segment. For example, if your company segment is a balancing segment, Oracle General Ledger ensures that, within every journal entry, the total debits to company 01 equal the total credits to company 01.

97. What is cost center segment?
It is a segment qualifier. A segment qualifier identifies a particular type of value in a single segment of a key flex-field. In the Oracle Applications, only the Accounting Flex-field uses segment qualifiers. You can think of a segment qualifier as an "identification tag" for a value. In the Accounting Flex-field, segment qualifiers can identify the account type for an account segment value, and determine whether detail posting or budgeting, are allowed for a particular value.

98. Can you have two accounting flex fields in AR?

Yes, you can have as many accounting flex fields as you wish. But, then you have to define one set of books per accounting flex field.

99. What are on-account sales credits?
On account: Payments where you intentionally apply all or part of the payment amount to a customer without reference to an debit item. On-account examples include prepayments and deposits.

On account credits: Credits that you assign to your customer's account that are not related to a specific invoice. You can create on account credits in the Enter Credit Memos form or through AutoInvoice.

100. What is customer merging?

You can merge the business purposes of two different customers in order to assign all transactions to a new customer, or you can merge business purposes for the same customer. Oracle Receivables automatically generates a report after you merge customer information to allow you to review the details of your merge.

101. What happens when you complete an invoice?
You complete an invoice by entering yes in this field. If you only have partial information for the invoice you can always go back to enter more information to complete your invoice.

Oracle Receivables performs several validation tests before it allows you to set the Complete field to Yes for an invoice:

_ Invoice contains at least one line or has freight at the header level.

_ Invoice date is within active date ranges of parameters

_ Each invoice line amount must equal the sum of it's distributions

_ GL date must fall within an open or future-enterable accounting period

_ Invoice Amount sign adheres to the value that you specified for the Creation Sign field of the transaction type for this invoice

If you are entering an invoice against a commitment:

_ Invoice date is equal to or later than the transaction date

_ Invoice's GL date is equal to or later than the commitment's GL date

If you have assigned an automatic payment method to an invoice:

_ Specified a customer bank

_ Specified a customer bank branch

_ Specified a customer bank account

If you assign an invoicing rule to an invoice:

_ Each invoice line has an accounting rule

_ Each invoice line has a rule start date

_ Invoice and tax lines of an invoice have account sets

If you do not assign an invoicing rule to an invoice:

_ Revenue accounts exist for all of the invoice and tax lines

_ Accounts on the Account Assignments are valid Flexfield combinations

If Tax Calculation is set to Yes:

_ Each line has a tax line except charges lines

When you complete an invoice whose transaction type has Open Receivables set to Yes, Oracle Receivables displays the invoice on your agings. If you change the transaction type of a completed invoice where Open Receivables is set to No, Oracle Receivables no longer displays the invoice on your agings.

If you update a completed invoice by changing values on which AutoAccounting depends, and AutoAccounting fails, Oracle Receivables displays a warning message and changes this field back to No.

If you update a completed invoice by changing values on which tax calculation depends, and tax calculation fails, Oracle Receivables displays a warning message and changes this field back to No.

Oracle Receivables also lets you delete incomplete invoices if you enter Yes in the Allow Delete field of the System Options form.

102. What all things are stored in RA_CUSTOMER_TRX?

The RA_CUSTOMER_TRX table stores invoice, debit memo, commitment and credit memo header information. Each of these transactions is stored as a unique record, based on the primary key, customer_trx_id. The transaction number, transaction date and billing customer are stored in the trx_number, trx_date and bill_to_customer_id columns, respectively. Additional information stored in this table includes ship-to customer, document sequence number, currency code and a transaction complete flag. The transaction type for the invoice is stored in the RA_CUST_TRX_TYPES table, but can be referenced via the foreign key cust_trx_type_id.

103. What are charge backs?
Charge-back is a new debit item that you assign to your customer when you close an existing, outstanding debit item.

104. What is lock box?

105. What is address validation?
For each address style you can define country specific validation which controls the information you enter in certain address elements. For example, you may want to restrict the entry of cities for French addresses to a predefined list, or you may want to restrict a postal code to a certain range of numbers. Oracle Applications allow you to define your own country specific validation. Validation can take the form of a simple quickpick or a complex parent-child relationship between address elements.

106. What is finance charge?

Finance charges are additional charges that you assign to your customers for past due items. You specify whether you want to charge your customers finance charges through their customer profiles. Finance charges can be included on your customer's statements and dunning letters.

107. How AR and OE related?
Orders are entered through OE module. Once order is Ship Confirmed i.e. material is delivered to customer, receivable interface is run. This interface ports data from OE to AR tables. AR enables to chase customer for payments. Various kinds of reports are produced. Data remains in AR till Invoice is paid off and then data is passed on to GL.

108. What is credit memo and debit memo?

Ref. Question 20, 21.

109. What is the link between CM and invoice (column name)?
Invoice and CM both go in ra_customer_trx table. CM record will have customer_trx_type_id of credit memo (in training database its set to 1002). This CM record will store the trx_number of Invoice record in previous_customer_trx_number.

Invoice record trx_number = 1000
CM record trx_number = 1020086 previuos_customer_trx_number = 1000

110. What is set of books?

Set of books A company or group of companies within Oracle General Ledger that shares a common Accounting Flex-field structure, calendar and functional currency.

111. How to find overdue invoices?
You can find out overdue invoices by firing an aging report. Define aging buckets to list invoices in different buckets like 30 days overdue, 60 days overdue, 90days overdue etc.

112. List the key flex fields in AR?

Sales tax location

113. Which is the parameter set to validate key flex field value?Syntax

CODE="flexfield code"
SEG="block.concatenated values field name"
[DERIVED=":block.field\nSegment qualifier"]
[ID="block.unique ID field"]
[DISPLAY="{ALL flexfield qualifier
segment number}"]
[UPDATE="{ALL flexfield qualifier
segment number}"]
[INSERT="{ALL flexfield qualifier
segment number}"]
[DATA_FIELD="concatenated hidden IDs field"]
[DESC="block.concatenated description field name"]
[TITLE="window title"]
[NUM=":structure defining field"]
[COPY=":block.field\n{ALL flexfield qualifier}"]
[VRULE="flexfield qualifier\n
segment qualifier\n
NAME=Message Dictionary message name\n
validation value1\n
validation value2..."]
flexfield qualifier\n
segment qualifier"]
[COLUMN="{column1(n) column1 alias(n)
[, column2(n), ...]}]
[WHERE="where clause"]
[SET="set number"]

114. What is dunning letter?

A letter that you send to your customers to inform them of past due debit items. Oracle Receivables lets you specify the text and format of each letter. You can choose to include unapplied and on-account payments.

115. What is profile?
Profile is a set of optional/mandatory parameters, which a user can set, which decide the way Oracle applications work.

116. What is concurrent program?

It is a unique facility that manages many time-consuming, non-interactive tasks within Oracle Applications for you, so you do not have to wait for their completion. When you submit a request in Oracle Applications that does not require your interaction, such as releasing shipments or running a report, the Concurrent Manager does the work for you, enabling you to complete multiple tasks simultaneously.

117. What are the inputs for pick release?
‘Backorders’ & ‘Prior Reservations Only’ are mandatory fields, rest of them are optional, which include the following:
a) Order Type
b) Order Number
c) Customer Number
d) Between ‘Request Dates’ and or ‘Schedule Dates’.
e) Ware house
f) Sub-inventory
g) Item
h) Shipment priority
i) Freight Carrier

118. After which stage you cannot cancel the order?

The Cancel Orders form enforces some security rules that are not predefined using the Define Security Rules form. For example, the Cancel Orders form prevents you from cancelling order line quantities that have been shipped or invoiced, and from cancelling return line quantities that have been received or credited. The Cancel Orders form honors security rules that you define for the Cancel operation that are stricter than these rules, but if you define any that conflict with these rules, they will be ignored.

119. Name important tables from OE/AR?Following are the list of some tables in OE module:
Following are the list of some tables in AR module:

120. Give complete process cycle for OE/AR?

The process cycle for a general OE is as follows:

The process cycle for a general OE is as follows:
Import Invoice information from legacy system or from OE module (Receivable Interface) to Autoinvoice the Orders.
Enter Receipt
Apply the Invoices against the receipt received or deposits held or credit memos or prepayments.
Complete invoice.
121. Name interface tables in OE/AR?
Oracle Order Entry inserts information into two of the three AutoInvoice interface tables (RA_INTERFACE_LINES and RA_INTERFACE_SALES_CREDITS). RA_INTERFACE_DISTRIBUTIONS

Oracle Receivables and Oracle Government Receivables uses three tables to temporarily store data you transfer from other systems. These tables are called RA_INTERFACE_LINES_ALL, RA_INTERFACE_SALESCREDITS_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL. The fourth table, RA_INTERFACE_ERRORS_ALL is used only by AutoInvoice to store information about interface data that failed validation. For a detailed description of each column in these tables, please refer to the Table and Column Description section.

122. What tables are hit when an Invoice is entered?


123. Name important tables of various modules?Following are some of the important tables in AP module:

Following are some of the important tables in GL module:

Following are some of the important tables in INV module:

125. What you know about SQL*Loader?
SQL*Loader is a 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 filters, and multi-table loads.

One load data into the Oracle database by using the sqlldr (sqlload on some platforms)
utility. Look at the following example:

sqlldr orauser/passwd control=loader.ctl

This is the control file, loader.ctl:

load data
infile *
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)

Load variable and fix length data records
look at the following control file examples. In the first we will load delimited data
(variable length):

INTO TABLE load_delimited_data
( data1,

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

INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)

Modify data as it loads into the database:

look at the following examples:

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)"

INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
INTO TABLE mailing_list

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

Load data into multiple tables at once:
Look at the following control file:

WHEN empno != ' '
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
WHEN projno != ' '
Selectively load only the data that you need :
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:

INTO TABLE db_trace_19980517
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19980517'
region CONSTANT '31',
call_b_no POSITION(12:29) CHAR

You can not get SQL*Loader to commit at the end of the load file, 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

To Improve performance of SQL*Loader :
1.A very simple but easily overlooked hint, do not have any indexes and/or constraints
(primary key) on your load tables during the load process. This will significantly
slowdown load times even with ROWS= set to a high value.

2.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.

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

4.Run multiple load jobs concurrently.

126. Name some important interface programs

a) Inventory
b) Customer
c) Item
d) Receivable
e) GL
f) Demand
g) Tax

127. What are object groups?
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.

Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas-view, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.

You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.

Program units cannot be placed in an object group.

· Objects owned by blocks cannot be placed in an object group. This includes items, item-level triggers, block-level triggers, and relations. These objects cannot exist independently of the block in which they were defined. To include these objects in an object group, you must include the block itself by dragging the block object into the object group in the Navigator.

· The objects in a group must all be defined in the same module; you cannot place objects from two different forms in the same object group.

· An object group cannot contain another object group.

· When an object in an object group is deleted from a module, it is removed from the object group automatically.

· Deleting an object group from a module does not delete the objects it contains from the module.

· Object groups do not store copies of the objects they contain, but rather pointers to the objects; defining object groups does not increase module size significantly.

128. Difference between packages and procedure/function


A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms and so implements the specification.

Unlike subprograms, packages cannot be called, passed parameters, or nested. Still, the format of a package is similar to that of a subprogram:

The specification holds public declarations, which are visible to your application. The body holds implementation details and private declarations, which are hidden from your application.

You can debug, enhance, or replace a package body without changing the interface (package specification) to the package body.

Packages can be created interactively with SQL*Plus or SQL*DBA using the CREATE PACKAGE and CREATE PACKAGE BODY commands.

Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible. So, you can change the body without having to recompile calling programs.

Package Body

The package body implements the package specification. That is, the package body contains the definition of every cursor and subprogram declared in the package specification. Keep in mind that subprograms defined in a package body are accessible outside the package only if their specifications also appear in the package specification. The package body can also contain private declarations, which define types and objects necessary for the internal workings of the package. The scope of these declarations is local to the package body. Therefore, the declared types and objects are inaccessible except from within the package body. Unlike a package specification, the declarative part of a package body can contain subprogram bodies.

A procedure has two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends

with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional exception-handling part. The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE, which introduces declarations in an anonymous PL/SQL block, is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

· Function
A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause.

Like a procedure, a function has two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the result value. Parameter declarations are optional. Functions that take no parameters are written without parentheses. The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). One or more RETURN statements must appear in the executable part of a function. The exception- handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

129. Various ways to call a form from another form and their difference· NEW_FORM
Built-in Type: restricted procedure

Enter Query Mode: no

Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Oracle Forms keeps the higher call active and treats it as a call to the new form. Oracle Forms releases memory (such as database cursors) that the terminated form was using.

Oracle Forms runs the new form with the same Runform options as the parent form. If the parent form was a called form, Oracle Forms runs the new form with the same options as the parent form.

Built-in Type: unrestricted procedure

Enter Query Mode: yes

Runs an indicated form while keeping the parent form active. Oracle Forms runs the called form with the same Runform preferences as the parent form. When the called form is exited Oracle Forms processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.

Built-in Type: restricted procedure (cannot be called in Enter Query mode)

Enter Query Mode: no

Opens the indicated form. Call OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.

130. Can you base a block in a form on a view?

Yes, You can. If the view is non-updatable or non-insertable then you can not base a block on view.

closed order : An order and its order lines that have completed all activities in its process flow and for which the close activity has been completed.

Sales channel is always associated with the customer. We can define the sales person for the customer also. If sales person not defined at the customer level, we can associate a sales person with an order also. Account specialist and sales manager are always associated with bill to site address of the customer.

1 comment:

Bose said...

Thanks a ton for all the question, this will surely be a big help for my interview!Debit Memo Format