Oracle apps PLSQL interview questions

1. Difference b/w procedure and function? A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition.

● Function can be called in select statements but procedure can only be called in a pl/sql block. Procedure's parameters can have IN or OUT or INOUT parameters. But function's parameters can only have IN parameters.

2. Difference b/w ROWID and ROWNUM? ROWID: It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored. ROWNUM: It gives a sequence number in which rows are retrieved from the database.

3. Give some examples of pseudo columns? NEXTVAL, CURRVAL, LEVEL, SYSDATE

4. Difference b/w implicit cursor and explicit cursor? Implicit cursors are automatically created by oracle for all its DML stmts. Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN; Explicit cursors are created by the users for multi row select stmts.

5. How to create a table in a procedure or function? See the below piece of code: Since create stmt can be used only at the sql prompt, we have used dynamic sql to create a table.

DECLARE

L_STMT VARCHAR2(100);

BEGIN

DBMS_OUTPUT.PUT_LINE('STARTING ');

L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';

EXECUTE IMMEDIATE L_STMT;

DBMS_OUTPUT.PUT_LINE('end ');

END;

The above piece of code can be written In procedure and function DDL's can be used in function provided that function should be invoked in Begin-End block not from Select statement. Introduction to REF CURSOR

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
end loop;
close c_emp;
end;
Let me explain step by step. The following is the first statement you need to understand:
type r_cursor is REF CURSOR;
The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR. We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows:
c_emp r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
open c_emp for select ename from emp;
To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows:
loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
end loop;
I finally closed the cursor using the following statement:
close c_emp;
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR. Now I would like to retrieve more than one column (or entire row) of information using the same. Let us consider the following example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
In the above example, the only crucial declaration is the following:
er emp%rowtype;
The above declares a variable named "er," which can hold an entire row from the "emp" table. To retrieve the values (of each column) from that variable, we use the dot notation as follows:
      dbms_output.put_line(er.ename || ' - ' || er.sal);

6. Explain the usage of WHERE CURRENT OF clause in cursors ? Look at the following pl/sql code:

DECLARE

CURSOR wip_cur IS

SELECT acct_no, enter_date

FROM wip

WHERE enter_date < SYSDATE -7

FOR UPDATE;

BEGIN

FOR wip_rec IN wip_cur

LOOP

INSERT INTO acct_log (acct_no, order_date)

VALUES (wip_rec.acct_no, wip_rec.enter_date);

DELETE FROM wip

WHERE CURRENT OF wip_cur;

END LOOP;

END;

"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE" in the cursor select stmt.

"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.

7. What is the purpose of FORUPDATE? Selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s). If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

8. What is RAISE_APPLICATION_ERROR? The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error With raise_application_error

9.

DECLARE

   num_tables NUMBER;

BEGIN

   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;

   IF num_tables < 1000 THEN

      /* Issue your own error code (ORA-20101) with your own error message.

         Note that you do not need to qualify raise_application_error with

         DBMS_STANDARD */

      raise_application_error(-20101, 'Expecting at least 1000 tables');

   ELSE

      NULL; -- Do the rest of the processing (for the non-error case).

   END IF;

END;

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and

avoid returning unhandled exceptions.

9. What is mutating error? Mutating error occurs in the following scenario:

WHEN WE ARE UPDATING A TABLE (TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL RESULT INTO MUTATING ERROR.

10. Can we have commit/rollback in DB triggers? Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.

11. Can we make the trigger an autonomous transaction? This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.

12. What is autonomous transaction? Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.

13. What is a REF Cursor? The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.

14. What is the difference between ref cursors and normal pl/sql cursors?

Declare

type rc is ref cursor;

cursor c is

select * from dual;

l_cursor rc;

begin

if ( to_char(sysdate,'dd') = 30 ) then

open l_cursor

for select * from emp;

elsif ( to_char(sysdate,'dd') = 29 ) then

open l_cursor

for select * from dept;

else

open l_cursor

for select * from dual;

end if;

open c;

end;

Given that block of code you see perhaps the most "salient" difference, no matter how many times you run that block The cursor C will always be select * from dual. The ref cursor can be anything.

15. Is Truncate a DDL or DML statement? And why? Truncate is a DDL statement. Check the LAST_DDL_TIME on USER_OBJECTS after truncating your table. TRUNCATE will automatically commit, and it's not rollback able. This changes the storage definition of the object. That's why it is a DDL.

16. What are the actions you have to perform when you drop a package? If you rename a package, the other packages that use it will have to be MODIFIED. A simple compilation of the new renamed package won't do. If you have toad, go to the "used by" tab that will show you the packages that call the package being renamed.

17. What is cascading triggers? When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.

18. What are materialized views? A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.

19. Example

If the materialized view will access remote database objects, we need to start by creating a database link to the remote DB:

CREATE DATABASE LINK remotedb

CONNECT TO scott IDENTIFIED BY tiger

USING 'orcl';

Now we can create the materialized view to pull in data (in this example, across the database link):

CREATE MATERIALIZED VIEW items_summary_mv

ON PREBUILT TABLE

REFRESH FORCE AS

SELECT a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,

sum(a.GMS) GMS,

sum(a.NET_REV) NET_REV,

sum(a.BOLD_FEE) BOLD_FEE,

sum(a.BIN_PRICE) BIN_PRICE,

sum(a.GLRY_FEE) GLRY_FEE,

sum(a.QTY_SOLD) QTY_SOLD,

count(a.ITEM_ID) UNITS

FROM items@remotedb a

GROUP BY a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

Materialized view logs:

Materialized view logs are used to track changes (insert, update and delete) to a table. Remote materialized views can use the log to speed-up data replication by only transferring changed records.

Example:

CREATE MATERIALIZED VIEW LOG ON items;

20. Commonly occurring Errors in Reports?

Some of the errors are defined below

1. There Exists uncompiled unit: When the report is not compiled before loading in the Oracle Applications.

2. Report File not found: When the rdf is not uploaded in proper directory

3. Width or margin is zero: When the repeating frame is not within proper frames

4. Not in proper group: When the repeating frame is not referred to proper group

21. What is the difference between Compile and Incremental Compile in oracle reports?

In Full compile all the PL/SQL within the reports are compiled but in incremental compile only the changed PL/SQL units are compiled.

When compiling the report for the first time, we should do the full compilation and not the Incremental compile.

22. How to compile Procedures and Packages?

ALTER <proc/package> <name>COMPILE;

Oracle apps PLSQL interview questions

1) What is ERP? A packaged business software system that lets a company automate and integrate the majority of its business processes; share common data and practices across the enterprise; [and] produce and access information in a real-time environment.

2) Tell me some thing about SQL-LOADER? Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.

Sql * loader supports various load formats, selective loading, and multi-tables loads.

Conventional: The conventional path loader essentially loads the data by using standard ‘insert’ statement.

Direct: The direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.

EX:-

My data.csv file

1001, “scott tiger”,1000,40

1002,”oracleapps4u”,2345,50

Load data

Infile ‘c:\data\mydata.csv’

Into table emp

Fields terminated by “,” optionally enclosed by ‘”’

(empno, empname,sal,deptno)

>sqlldr scott/tiger@vis

control=loader.ctl log= gvlog.log bad=gvbad.bad discard=gvdis.dsc .

3) How to dump data from pl/sql block to flat files? Using utl_file package, we can dump data from pl/sql block to flat file.

PRE-REQUIREMENTS for UTL_FILE is specify the accessible directories for theUTL_FILE function in the initialization file (INIT.ORA) Using the UTL_FILE_DIR parameters.

Ex: UTL_FILE_DIR = <Directory name>

EX:- remember to update INITSID.ORA, utl_file_dir = ‘c:\oradata’

Declare

Fp utl_file.file_type;

Begin

Fp := utl_file.fopen(c:\oradata’,tab1.txt’,’w’);

Utl_file.putf(fp,’%s %s \n ‘text field’, 55);

Utl_file.fclose(fp);

End;

4) What is SET-OF-BOOKS? Collection of Chat of Accounts and Currency and Calendars is called SOB

5) What is the interface Table? Interface Table is a table which is used as medium for transfer of data between two systems.

6) What is invoice? A request sent for payment

7) What is INBOUND and OUT BOUND? (Different types of interfaces)

Inbound Interface:

For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.

Outbound Interface:

For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programs.

8) What are the Base tables in the AR?
9) What are the interface tables of the customer conversion? Check the following blog post for interface tables in customer Interfaces and Conversions in Oracle Applications

Order Import Interface (Sales Order Conversion)

Pre-requisites:

Order Type

Line Type

Items

Customers

Ship Method/ Freight Carrier

Sales Person

Sales Territories

Customer Order Holds

Sub Inventory/ Locations

On hand Quantity

Interface tables:

OE_HEADERS_IFACE_ALL

OE_LINES_IFACE_ALL

OE_ACTIONS_IFACE_ALL

OE_ORDER_CUST_IFACE_ALL

OE_PRICE_ADJS_IFACE_ALL

OE_PRICE_ATTS_IFACE_ALL

Concurrent Program:

Order Import

Base tables:

OE_ORDER_HEADERS_ALL

OE_ORDER_LINES_ALL

Pricing tables: QP_PRICING_ATTRIBUTES

Order Import API OE_ORDER_PUB.GET_ORDER and

PROCESS_ORDER can also be used to import orders.

Item import (Item conversion)

Pre-requisites:

Creating an Organization

Code Combinations

Templates

Defining Item Status Codes

Defining Item Types

Interface tables:

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)

MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)

MTL_INTERFACE_ERRORS (View errors after import)

Concurrent Program:

Item import

Base Tables:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_CATEGORIES_B

MTL_CATEGORY_SETS_B

MTL_ITEM_STATUS

MTL_ITEM_TEMPLATES

Inventory On-hand quantity Interface

Interface tables:

MTL_TRANSACTIONS_INTERFACE

MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot

Controlled)

MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial

Controlled)

Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.

Base Tables:

MTL_ON_HAND_QUANTITIES

MTL_LOT_NUMBERS

MTL_SERIAL_NUMBERS

Customer conversion

Interface tables:

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILES_INT_ALL

RA_CONTACT_PHONES_INT_ALL

RA_CUSTOMER_BANKS_INT_ALL

RA_CUST_PAY_METHOD_INT_ALL

Base tables:

RA_CUSTOMERS

RA_ADDRESSES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_SITE_USES_ALL

Concurrent program:

Customer Interface

Auto Invoice interface

Pre-requisites:

Set of Books

Code combinations

Items

Sales representatives

Customers

Sales Tax rate

Payment Terms

Transaction Types

Freight Carriers

FOB

Batch Sources

Accounting Rules

Interface tables:

RA_INTERFACE_LINES_ALL

RA_INTERFACE_SALESCREDITS

RA_INTERFACE_DISTRIBUTIONS

RA_INTERFACE_ERRORS (details about the failed records)

Base tables:

RA_BATCHES

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

AR_PAYMENT_SCHEDULES_ALL

RA_CUSTOMER_TRX_LINE_SALESREPS

RA_CUST_TRX_GL_DIST_ALL

RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program:

Auto invoice master program

AR Receipt API

Pre-requisites:

Set of Books

Code combinations

Items

Quick Codes

Sales representatives

Customers

Sales Tax rate

API:

AR_RECEIPT_API_PUB.CREATE_CASH

AR_RECEIPT_API_PUB.CREATE_AND_APPLY

Base tables:

AR_CASH_RECEIPTS

AP invoice interface

Pre-requisites:

Set of Books

Code combinations

Employees

Lookups

Interface tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

Base tables:

AP_INVOICES_ALL – header information

AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program:

Payables Open Interface Import

Vendor conversion/interface

Pre-requisites setup’s required:

Payment terms

Pay Groups

CCID

Supplier classifications

Bank Accounts

Employees (if employees have to set up as vendors)

Interface tables:

AP_SUPPLIERS_INT

AP_SUPPLIER_SITES_INT

AP_SUP_SITE_CONTACT_INT

Base Tables:

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS

Interface programs:

Supplier Open Interface Import

Supplier Sites Open Interface Import

Supplier Site Contacts Open Interface Import

Purchase Order conversion:

Pre-requisites:

Suppliers, sites and contacts

Buyers

Line Types

Items

Charge account setup

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS (Fallouts)

Interface Program:

Import Standard Purchase Orders.

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

Import Blanket Purchase Agreements:

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

Interface program:

Import Price Catalogs

Base tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

Requisition import

Pre-requisites:

Set of Books

Code combinations

Employees

Items

Define a Requisition Import Group-By method in the Options window.

Associate a customer with your deliver-to location using the Customer

Address window for internally sourced requisitions.

Interface tables:

PO_REQUISITIONS_INTERFACE_ALL

PO_REQ_DIST_INTERFACE_ALL

Base tables:

PO_REQUISITIONS_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Concurrent program:

REQUISITION IMPORT

PO Receipts Interface

Pre-requisites:

Set of Books

Code combinations

Employees

Items

Interface tables:

RCV_HEADERS_INTERFACE

RCV_TRANSACTIONS_INTERFACE

PO_INTERFACE_ERRORS

Concurrent program:

RECEIVING OPEN INTERFACE

Base tables:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

RCV_TRANSACTIONS

GL Journal interface

Pre-requisites:

Set of Books

Flex field Value sets

Code Combinations

Currencies

Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

So why is it important to understand this?
When a query is executed, First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given. Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result. As soon as it is completed, the columns mentioned are selected from the corresponding tables. And finally sorted using ORDER BY clause. So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Example:
Recently I came across the following problem.

The original query wasSELECT DISTINCT so.departure_airport, so.airport_name
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170' AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
ORDER BY price ASC
Table data:
departure_airport airport_name price
'MAN' 'Manchester Airport' 344
'MAN' 'Manchester Airport' 288
'MAN' 'Manchester Airport' 316
'BRS' 'Bristol' 289
'BRS' 'Bristol' 345
'BRS' 'Bristol' 317
'BHX' 'Birmingham Airport' 343
'BHX' 'Birmingham Airport' 287
'BHX' 'Birmingham Airport' 315

Here the query is executed as follows:

All the records from both the tables are taken.

Conditions mentioned in the WHERE

20) Form development process?

1. open template form

2. Save as <your form>.fmb

3. Change the form module name as form name.

4. Delete the default blocks, window, and canvas

5. Create a window.

6. Assign the window property class to window

7. Create a canvas (subclass info)

8. Assign canvas property class to the canvas

9. assign the window to the canvas and canvas to the window

10. Create a data block

11. Modify the form level properties. (sub class item à Text item)

12. Modify the app_custom package in the program unit.

13. Modify the pre-form trigger (form level)

14. Modify the module level properties ((console window, First navigation

15. Save and compile the form.

16. Place the .fmx in the server directory.

17. Register in the AOL

APPLICATION -> FORM

APPLICATION -> FUNCTION

APPLICATION -> MENU

21) How to customize the Reports?

· Identify the Short name of the report and the module in which we have to place the customization

§ Ex: - if you want to customize in the AR module, path is

Appl top\ar\11.5.0\reports\US\ .rdf

· FTP back the file to the local system in Binary mode

· Open the .rdf file in Report builder and change the name of the module.

· Open the data model and Layout mode, perform all the required changes.

· Go to report wizard and select the newly created columns.

· Compile it. Save it.

· Then Register in the AOL Concurrent > executable.

Concurrent > program.

· Go to system administrator Security > Responsibility > request

· Add and assign a concurrent program to a request group

22) List some report names in oracle apps?

1) OPEN-DEBIT MEMO REPORT?

This report shows all the open-debit memo transactions, based oncustomer number and dates.

Columns: type, customer_no, trx_no, amt_due, remaining.

Parameter: type, customer, from_date, to_date.

2) GENERATING POSITIVE PAY FILE FOR BANK REPORT?

Basically this report generates a flat file of all the payments in order to send in to the bank.

3) UPDATE POSITIVE PAY CHECKS REPORT?

This report which updates the data into the (AP) account payables system from the plot file, the file which is sent by bank

4) UPDATE POSITIVEPAY OUT STANDING CHECKS?

This report which shows the out standing checks

5) CUSTOMER PAYMENT DETAILS REPORT?

This report shows each customer original amount, amount pay and due amount based on transaction type (books, pens)

Transaction types in AR

Credit memo transaction types

Invoice, debit memo, and charge back transaction types

Commitment transaction types

23) HOW DO YOU RECTIFY THE ERRORS IN INTERFACE TABLES?

Depending on the naming convention used, errors appear in either alphabetical order or by error code number.

24) What are ‘WHO’ Columns in oracle apps tables?

1) Created by

2) Creation date

3) Last _updated by

4) last_update_date

5) last_update_value

25) What are FLEX FIELDS?

Flexfields are used to capture the additional business information.

DFF	KFF
Additional	Unique Info, Mandatory
Captured in attribute prefixed columns	Segment prefixed
Not reported on standard reports	Is reported on standard reports
To provide expansion space on your form With the help of []. [] Represents descriptive Flex field. FLEX FILED : DESCRIPTIVE : REGIGSTER	Used for entering and displaying key information For example Oracle General uses a key Flex field called Accounting Flex field to Uniquely identifies a general account. FLEX FILED : KEY : REGIGSTER

Oracle Applications KEY FLEX FIELDS

1) GL: ACCOUNTING

2) AR: SALES TAX LOCATION, TERRITORY,

3) AP: BANK DETAILS, COST ALLOCATION, PEOPLE GROUP

Oracle Applications DESCRIPTIVE FLEX FIELDS (Partial)

1) GL: daily rates

2) AR: credit history, information

3) PA: bank branch, payment terms, site address,

26) What are different concurrent requests?

a) Single request: this allows you to submit an individual request.

b) Request set: this allows you to submit a pre-defined set of requests.

27) What are the activities in Sys Admin Module?

a) Define Custom Users, b) Define Login Users, c) Register oracle DB users,

d) Define Concurrent Programs, e) Register Concurrent Executable,

f) Setting Profile Option Values, g) Define Request Types.

28) What activities can be performed in AOL?

a) Registering tables. b) Registering views c) Registering db sequences

          d) Registering profile options e) Registering lookups and lookup codes
  f) Registering forms g) Registering Form and Non-Form functions
  i) Registering menus and sub-menus j) Registering DFF and KFF k) Libraries

29) What are the type Models in the system parameters of the report?
1) Bit map 2) Character mode

30) What is SRW Package?(Sql Report Writer): The Report builder Built in package know as SRW Package This package extends reports ,Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exist, to format width of the columns, to page break the column, to set the colors

Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,

SRW.SET_FIELD_NUM

SRW. SET_FILED_CHAR

SRW. SET FILED _DATE

SRW.SET_attributes

Description The SRW.SET_<attributes> procedures are used to set the format attributes. Mainly used to make some change in the appearance of the layout objects.

Format attributes	Value
Text Color	Red
Font Face	Impact
Font Weight	Bold
Font Style	Italic
Font Pattern	Solid diamond

Example

Function age_mask return Boolean is

Begin

If :age < 25 then

Srw.set_text_color(‘Red’);

Srw.set_font_face(‘Impact’) ;

Srw.set_font_weight(srw.bold_weight) ;

Srw.set_font_style(srw.italic_style);

Srw.set_fill_pattern(‘solid diamond’);

End if;

End;

SRW.SET_MAXROW

Description This procedure sets the maximum number of records to be fetched for the specified query. This is useful when your report formats (i.e., displays) fewer records than the query (or queries) that fetch them. Thus, with SRW.SET_MAXROW, you can conditionally restrict data that is fetched for your report, enabling you to improve the report's performance.

Syntax

SRW.SET_MAXROW (query_name CHAR, maxnum PLS_INTEGER);

Parameters

query_name Is the query whose fetched records will be limited.

maxnum Is maximum number of records you want the query to fetch.

Property Palette To define this attribute using the Property Palette, set the Maximum Rows to Fetch property.

Restrictions

· SRW.SET_MAXROW is only meaningful in a Before Report trigger (i.e., after the query is parsed). If SRW.SET_MAXROW is called after the Before Report trigger (i.e., after the queries have been executed), the SRW.MAXROW_UNSET packaged exception is raised.

· Because this procedure causes only the specified number of records to be fetched, the "unfetched" records of the query are not used in computations, etc.

· If you specify that 0 records should be fetched, the query will still be parsed.

Example

/* Suppose your report has two queries, Q_Stocks and Q_Bonds.

** Suppose also, that you have a user-created parameter, named

** WHICHDATA, that enables users to specify which data they want

** the report to display: either stocks or bonds. In the

** Before Report trigger, you could use the SRW.SET_MAXROW

** procedure to ensure that only one query's data is fetched:

FUNCTION FETCHIT RETURN BOOLEAN IS

BEGIN

if :whichdata != 1 then

srw.set_maxrow ('Q_Stocks', 0);

else

srw.set_maxrow ('Q_Bonds', 0);

end if;

RETURN (TRUE);

END;

SRW.RUN_REPORT

Description This procedure invokes RWRUN60 with the string that you specify.

This procedure is useful for:

· running drill-down reports (i.e., calling a report from a button's action trigger)
· sending parts of a report to different recipients (e.g., to send a report via e-mail to each manager with just his or her group's data)
· sending parts of a report to different printers (e.g., to send each manager's report to his or her printer)
· running multiple reports from a single "driver" report

SRW.RUN_REPORT executes the specified RWRUN60 command.

Syntax

SRW.RUN_REPORT (command_line CHAR);

Parameters

command_line Is a valid RWRUN60 command.

Restrictions

· If you want parameter values that are entered on the Runtime Parameter Form to be passed in the RWRUN60 string, you must call SRW.RUN_REPORT after the before form trigger.

· The string that is specified for or passed to this procedure must follow the syntax and case-sensitivity rules for your operating system.

· No userid should be specified for the SRW.RUN_REPORT procedure. The userid is inherited by the "calling" report.

· If the parent report that invokes SRW.RUN_REPORT is run in batch, then DESTYPE can only be File, Printer, Sysout, or Mail. Otherwise, DESTYPE can be File, Printer, or Mail.

·

If SRW.RUN_REPORT is used in the PL/SQL for a button, the Runtime Parameter Form will not appear by default when the button is selected. If you want the Runtime Parameter Form to appear, you must specify PARAMFORM=YES in the call to SRW.RUN_REPORT.

· If you do not specify a path, Report Builder will use its file path search order to find the report.

Example

/* Suppose you have the following two reports: MGR_RUN, which queries manager names, and invokes a second report named MAIL_IT. MAIL_IT, which queries employee names for the manager that MGR_RUN passes it, and sends the report output to the manager via e-mail. The description of MGR_RUN could be as follows:

** Query: SELECT ENAME, EMPNO FROM EMP WHERE JOB='MANAGER'

** Group Filter:

FUNCTION FOO RETURN BOOLEAN IS

BEGIN

srw.run_report('report=MAIL_IT

desname='||:ename ||' desformat=dflt batch=yes

mgr_no='|| TO_CHAR(:empno) );

RETURN (TRUE);

EXCEPTION

when srw.run_report_failure then

srw.message(30, 'Error mailing reports.');

raise srw.program_abort;

END;

/* This PL/SQL invokes MAIL_IT, specifies that MAIL_IT's output

** should be sent to the manager via Oracle Mail, and passes the

** manager number, so that the MAIL_IT report can query only the

** manager's employees.

** Note: EMPNO's values must be converted to characters

** (TO_CHAR in the PL/SQL above), because SRW.RUN_REPORT

** requires a character string.

** Layout: None is needed, because this report only fetches data,

** then passes it to a second report.

** The description of MAIL_IT could be as follows:

** Query: SELECT DEPTNO, ENAME, SAL FROM EMP WHERE MGR=:MGR_NO

** Layout: Master/Detail

/* Suppose that you have three reports that you almost always run together.

** The reports are named SALARY, COMMISS, and TAXES. To run these reports

** with one RWRUN60 command, you create a driver report named PAYROLL.

** The description of PAYROLL could be as follows:

** Query: SELECT DEPTNO FROM DEPT

** Before Report Trigger:

FUNCTION FOO RETURN BOOLEAN IS

BEGIN

srw.run_report('batch=yes report=SALARY

destype=file desformat=dflt desname=salary.lis');

srw.run_report('batch=yes report=COMMISS

destype=file desformat=dflt desname=comiss.lis');

srw.run_report('batch=yes report=TAXES

destype=file desformat=dflt desname=comiss.lis');

RETURN (TRUE);

END;

/* Layout: Tabular

** When you run PAYROLL from the designer or RWRUN60, the other three

** reports will all be run. (Note that, in this case, the query and

** the layout for Payroll could be anything. They are only used here

** in order to make it possible to run PAYROLL.)

*/
SRW.USER_EXIT

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.

Syntax SRW.USER_EXIT (user_exit_string CHAR);

Parameters

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.

Restrictions

User exits are not portable. If your report must be portable, and you need to add conditional logic to it, use PL/SQL.

If the user exit string passes a column or parameter to the user exit program, SRW.REFERENCE

must be called before this procedure.

Example

/* Suppose you have a user exit named STORE to which you want ** to pass salary values from Report Builder. To do so, you ** could write the following formula. For more information on ** how to call user exits, see Calling a user exit. */

FUNCTION FOO RETURN BOOLEAN IS

BEGIN

IF :SAL >= 0 THEN

SRW.REFERENCE(:SAL);

SRW.USER_EXIT('STORE SAL');

ELSE

SRW.MESSAGE(100, 'FOUND A NEGATIVE SALARY. CHECK THE

EMP TABLE.');

END IF;

EXCEPTION

WHEN SRW.UNKNOWN_USER_EXIT THEN

SRW.MESSAGE(200, 'STORE USER EXIT WAS UNKNOWN.

CHECK IF IT''S LINKED.');

WHEN SRW.USER_EXIT_FAILURE THEN

SRW.MESSAGE(200, 'STORE USER EXIT FAILED.

CHECK ITS CODE.');

RETURN(TRUE);

END;

SRW.REFERENCE

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.

Syntax SRW.REFERENCE (:object CHAR|DATE|NUMBER);

Parameters

object Is the Report Builder parameter or column whose value needs to be ascertained before the

construct fires.

Restrictions

The colon is required before the object name.

SRW.REFERENCE is unnecessary when the object is already referenced in the current PL/SQL construct.

Example

/* Suppose you want to pass the temperature and pressure values ** to a user exit called SUPERHEAT. Suppose, also, that if the ** temperature is too low, you want to raise a customized error message. ** To do so, you could write the following formula:*/

FUNCTION EXIT RETURN BOOLEAN IS

BEGIN

if :temp > 0 then

srw.reference (:temp); -- unnecessary reference

srw.reference (:pressure);

srw.user_exit('superheat temp pressure');

else srw.message(1000, 'Temperature is below

normal. Is machine off?');

raise srw.program_abort;

end if;

RETURN(TRUE);

END;

SRW.PROGRAM_ABORT

Description This exception stops the report execution and raises the following error message:

REP-1419: PL/SQL program aborted.

SRW.PROGRAM_ABORT stops report execution when you raise it.

Syntax SRW.PROGRAM_ABORT;

Usage Notes You must raise the exception from within your PL/SQL.

Example

/* Suppose you want to put a border around the salary if it is greater than 0.

** Suppose, also, that if the report fetches a salary less than 0, you want to

** raise a customized error message (i.e., "FOUND A NEGATIVE SALARY. . ."),

** then terminate the report execution. To do so, you could write the

** following format trigger for F_SAL.

FUNCTION foo return boolean is

BEGIN

if :sal >= 0 then

srw.attr.mask := SRW.BORDERWIDTH_ATTR;

srw.attr.borderwidth := 1;

srw.set_attr (0, srw.attr);

else

srw.message(100, 'FOUND A NEGATIVE SALARY.

CHECK THE EMP TABLE.');

raise srw.program_abort;

end if;

RETURN (TRUE);

END;

SRW.MESSAGE

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.

MSG-msg_number: msg_text.

Syntax

SRW.MESSAGE (msg_number NUMBER, msg_text CHAR);

Parameters

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.

Restrictions

· You cannot trap nor change Report 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 Report Builder.

Example

/* Suppose you have a user exit named MYEXIT to which you want to

** pass the values of the SAL column. Suppose, also, that you want

** to raise your own error if the user exit is not found (e.g., because

** it is not linked, compiled, etc.). To do these things, you could

** write the following PL/SQL in the Format Trigger of the F_SAL field:

/* This trigger will raise your message as follows:

** MSG-1000: User exit MYEXIT failed. Call Karen Smith x3455.

FUNCTION FOO RETURN BOOLEAN IS

BEGIN

srw.reference(:SAL);

srw.user_exit('myexit sal');

EXCEPTION

when srw.unknown_user_exit then

srw.message(1000, 'User exit MYEXIT failed.

Call Karen Smith x3455.');

raise srw.program_abort;

RETURN (TRUE);

END;

SRW.GET_PAGE_NUM

Description This procedure returns the current page number. This is useful when you want to use the page number in the field's Format Trigger property.

Syntax

SRW.GET_PAGE_NUM (page_num);

Parameters

page_num Is the variable in which you want to place the current page number.

Returns The current page number.

Restrictions

SRW.GET_PAGE_NUM is only meaningful in a format trigger. It has no effect when entered in other places.

Example

/* Suppose you want to perform a computation based upon a page number.

** In the field's Format Trigger, you could use SRW.GET_PAGE_NUM function:

BEGIN

DECLARE PAGE_NUM NUMBER;

begin

srw.get_page_num (page_num);

srw.set_field_num (0, page_num + 3);

end;

END;

SRW.GETERR_RUN

Description This function returns an error message if Report Builder detects an error while running the SRW.RUN_REPORT procedure.

Syntax SRW.GETERR_RUN;

Returns An error message.

Example

/* Suppose you are sending parts of a report to users via Oracle*Mail. ** For more information, see "SRW.RUN_REPORT". Also, ** suppose that if SRW.RUN_REPORT fails, you want to display a message ** that explains why it failed. Your PL/SQL could look like this:*/

BEGIN

DECLARE TMP CHAR(100);

begin

srw.run_report('batch=yes report=send.rdf

destype=file desname=send.lis desformat=dflt');

exception when srw.run_report_failure then

tmp := srw.geterr_run;

srw.message(1000, tmp);

end;

END;

SRW.DO_SQL_FAILURE

Description This exception stops the report execution and raises the following error message:

REP-1425: Error running DO_SQL package - REP-msg ORA-msg

where:

REP-msg Is a Report Builder message.

ORA-msg Is an optional ORACLE message, providing more information on the Report Builder message.

Syntax

SRW.DO_SQL_FAILURE;

Usage Notes Report Builder raises this exception when the SRW.DO_SQL packaged procedure fails (e.g., if the user does not have DDL privileges, yet tries to create a table with SRW.DO_SQL).

Example

/* Suppose you want your own error message raised,

** instead of the default error message.

** You could handle this exception in the following way:

EXCEPTION

when SRW.DO_SQL_FAILURE then

srw.message(1000, 'Error occurred while creating

table CHECKS.');

SRW.DO_SQL

Description This procedure executes the specified SQL statement from within Report Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.

Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Report Builder, instead of via a user exit. For more information on DDL or DML statements, see the ORACLE8 Server SQL Language Reference Manual.

Syntax SRW.DO_SQL (sql_statement CHAR);

Parameters

sql_statement Is any valid SQL statement. Remember to precede any Report Builder object names with a colon (:).

Restrictions

· In Report trigger order of execution, notice where the SET TRANSACTION READONLY occurs.

· A bind variable's value can be at most 64,000 bytes. (When the value exceeds that limit, it will be truncated to the left-most 64,000 bytes.)

· If you use a parameter as the destination of a character column for an INTO clause, you should ensure that the parameter is wide enough to contain the selected values. For example, suppose that you have the SRW.DO_SQL statement below: The destination parameter (my_ename) needs a width that is equal to the maximum width of the ENAME column. The reason for this is that the selected value contains trailing spaces up to the assumed size of the value. If the parameter is not large enough, you will get a truncation exception. If you are not sure about the maximum width of the SELECT list item, then you should use 2000 as the width for the parameter.

srw.do_sql('SELECT ENAME INTO :my_ename FROM EMP');

Example

/* Suppose you want your report to create a table named CHECK ** just before the Runtime Parameter Form is displayed. ** Because CREATE TABLE is a SQL DDL statement (and PL/SQL ** cannot perform DDL statements), you need to use SRW.DO_SQL. ** Therefore, your PL/SQL could look like this in the Before Form trigger:*/

/* Additional Information: If you use a table created in this way for your** report output, the table must exist before you create your query in the ** data model. Otherwise, Report Builder would not be able to parse your query.*/

FUNCTION CREATETAB RETURN BOOLEAN IS

BEGIN

SRW.DO_SQL('CREATE TABLE CHECK (EMPNO NUMBER NOT NULL

PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5

PCTUSED 75');

RETURN(TRUE);

EXCEPTION

WHEN SRW.DO_SQL_FAILURE THEN

SRW.MESSAGE(100, 'ERROR WHILE CREATING CHECK TABLE.');

SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE RUNTIME

PARAMETER FORM.');

RAISE SRW.PROGRAM_ABORT;

END;

SRW.CONTEXT_FAILURE

Description This exception stops the report execution and raises the following error message:

REP-1426: Running <construct_name> from incorrect context.

Syntax SRW.CONTEXT_FAILURE;

Usage Notes Report Builder raises this exception when a Report Builder packaged function or procedure is called in the wrong context (see the chart below).

In this chart, NO means that the function or procedure cannot be called in that context; YES means it can.

Name	Parameter Form	Data Model	Format Trigger	Report Trigger
srw.break	NO	YES	YES	NO
srw.do_sql	YES	YES	YES	YES
srw.geterr_run	YES	YES	YES	YES
srw.get_page_num	NO	NO	YES	NO
srw.message	YES	YES	YES	YES
srw.reference	YES	YES	YES	YES
srw.run_report	YES	YES	YES	YES
srw.set_attr	NO	NO	YES	NO
srw.set_field_char	NO	NO	YES	NO
srw.set_field_date	NO	NO	YES	NO
srw.set_field_num	NO	NO	YES	NO
srw.set_maxrow	NO	YES	YES	YES
srw.user_exit	YES	YES	YES	YES

Example

/* Suppose you want your own error message raised,

** instead of the default error message.

** You could handle this exception in the following way:

EXCEPTION

when SRW.CONTEXT_FAILURE then

srw.message(4000, 'Contact the Application

Development group regarding SRW.CONTEXT_FAILURE.');

raise srw.program_abort;

31) Difference between Bind and Lexical parameters?

BIND VARIABLE:

-- are used to replace a single value in sql, pl/sql

-- Bind variable may be used to replace expressions in select, where, group, order

by, having, connect by, start with cause of queries.

-- Bind reference may not be referenced in FROM clause (or) in place of

reserved words or clauses.

LEXICAL REFERENCE:

-- You can use lexical reference to replace the clauses appearing AFTER select,

from, group by, having, connect by, start with.

-- You can’t make lexical reference in a pl/sql statement.

32) Matrix Report: Simple, Group above, Nested

Simple Matrix Report : 4 groups

1. Cross Product Group

2. Row and Column Group

3. Cell Group

4. Cell column is the source of a cross product summary that

Becomes the cell content.

Frames:

1. Repeating frame for rows (down direction)

2. Repeating frame for columns (Across)

3. Matrix object the intersection of the two repeating frames

33) What is Flex mode and Confine mode?

Confine mode:

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders "stretch" when child objects are moved against them.

Off: parent borders remain fixed when child objects are moved against them.

34) What is Place holder Column?

A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger. Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

35) What is Formula Column? A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

36) What is a Summary column?

A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

37) What is cursor?

A Cursor is a pointer, which works on active set, I.e. which points to only one row at a time in the context area’s ACTIVE SET. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.

38) Types of cursors?

1) Implicit: Declared for all DML and pl/sql statements. By default it selects one row only.

2) Explicit: Declared and named by the developer. Use explicit cursor to individually process each row returned by a multiple statements, is called ACTIVE SET.

Allows the programmer to manually control explicit cursor in the pl/sql block

· Declare: create a named sql area

· Open: identify the active set.

· Fetch: load the current row in to variables.

· Close: release the active set.

CURSOR ATTRIBUTES:

· %is open: evaluates to true if the cursor is open.

· %not found: evaluates to true if the most recent fetch does not return a row

· %found: evaluates to true if the most recent fetch returns a row.

· %row count: evaluates to the total number of rows returned to far.

EXAMPLE:

Begin

Open emp_cursor;

Loop

Fetch when emp_cursor % rowcount >10 or

Emp_curor % not found;

dbms_output_put_line(to_char(vno)||’ ‘|| vname);

End loop;

Close emp_cursor;

End;

CURSOR FOR LOOP

A) cursor for loop is a short cut to process explicit cursors

B) it has higher performance

C) cursor for loop requires only the declaration of the cursor, remaining things like opening, fetching and close are automatically take by the cursor for loop

Example:

1) Declare

Cursor emp_cursor is

Select empno,ename

From emp;

Begin

For emp_record in emp_cursor loop

Dbms_output.putline(emp_record.empno);

Dbms_output.putline(emp_record.ename)

End loop

End;

39) Can we create a cursor without declaring it?

Yes – by using cursor for loop using subqueries.

BEGIN

FOR emp_record IN ( SELECT empno, ename

FROM emp) LOOP

-- implicit open and implicit fetch occur

IF emp_record.empno = 7839 THEN

...

END LOOP; -- implicit close occurs

END;

40) Attribute data types?

1) %type 2) %row type.

41) Exception Handling?

Is a mechanism provided by pl/sql to detect runtime errors and process them with out halting the program abnormally

1) pre-defined

2) user-defined.

PRE-DEFINED:

1) cursor_already_open ------ attempted to open an already open cursor.

2) Dup_val_on_index ------ attempted to insert a duplicate values.

3) Invalid_cursor ------ illegal cursor operation occurred.

4) Invalid_number ------ conversion of character string to number fails.

5) Login_denied ------ loging on to oracle with an invalid user name

and password.

6) program_error ------ pl/sql has an internal problem.

7) storage_error ------ pl/sql ran out of memory or memory is corrupted.

8) to_many_row ------ single row select returned more than one row.

9) value_error ------ arithmetic,conversion,truncation or size constraint error

10) zero_devided ------ attempted to divided by zero.

USER-DEFINED:

Declare : name the exception

Raise : explicitly raise the exception by using the raise statements

Reference: exception handing section.

The Raise_Application_Error_Procedure:

n You can use this procedure to issue user-defined error messages from stored sub programs.

n You can report errors to your applications and avoid returning unhandled exceptions.

Raise_Application_Error(error_number,message[,{true/false}]

Error number è between -20000 to -20999

pragma exception_init?

It tells the compiler to associate an exception with an oracle error. To get an error message of a specific oracle error.

Ex: pragma exception_init(exception name, oracle error number)

Example for Exceptions?

1) Check the record is exist or not?

Declare

E emp% rowtype

Begin

e.empno := &empno;

select * into e from emp where empno =e.empno;

Dbms_output.putline(‘empno’ || e.empno);

Exception

When no_data_found then

Dbms_output.putline(e.empno ||’doest exist’);

End;

2) User defined exceptions?

Define p_dept_desc =’Oracleapps4u’

Define p_dept_number =1236

Declare

E_invalid_dept exception;

Begin

Update departments

Set dept_name=’&p_dept_desc’

Where dept_id =’&p_dept_number’;

If sql% not found then

Raise e_invalid_departments;

End if;

Commit;

Exception

When e_invalid_departments then

Dbms_output.putline(‘no such dept’);

End;

42) Can u define exceptions twice in same block?

43) Can you have two functions with the same name in a pl/sql block?

Yes

44) Can you have two stored functions with in the same name?

Yes

45) Can function be overload?

Yes

46) What is the maximum number of statements that can be specified in a trigger statement?

One

47) Can functions be overloaded ?

Yes

48) Can 2 functions have same name & input parameters but differ only by return data type

49) What is a package?

Group logically related pl/sql types, items and subprograms.

1) Package specification

2) Package body

Advantages of a package:

· Modularity

· Easier Application Design

· Information Hiding

· Overloading

You cannot overload:

•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).

•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)

•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)

•Two functions that differ only in return type, even if the types are in different families.

50) What is FORWARD DECLARATION in Packages?

PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:

• Define subprograms in logical or alphabetical order.

• Define mutually recursive subprograms.(both calling each other).

• Group subprograms in a package

Example of forward Declaration:

CREATE OR REPLACE PACKAGE BODY forward_pack

PROCEDURE calc_rating(. . .); -- forward declaration

PROCEDURE award_bonus(. . .)

IS -- subprograms defined

BEGIN -- in alphabetical order

calc_rating(. . .);

. . .

END;

PROCEDURE calc_rating(. . .)

BEGIN

. . .

END;

END

Oracle Apps Interview Questions – 3

Q1: Difference between customization, enhancement and implementation?

Ans: Customization: Customization is the developing of the forms, reports and SQL script from the beginning or changing the existing.

Enhancement: Enhancement is the modification of forms & Other components according to client user requirement.

Implementation: Implementation is the testing of Applications.

Q2: What are the Types of Customizations?

Ans: There are two types of customizations.

1). Customization by extensions

2). Customizations by Modifications.

Customization by extensions: Customization by extension means developing new:

Component for existing Oracle applications and develop new application using the

Development feature of AOL (Application object Library).

Customization by extensions means Copying an Existing Oracle Application Component (Forms, Report, PL/SQL etc.) to a custom application directory and modifying the Copy.

Customizations by Modifications: Modifying existing oracle application Component to meet your specific Requirement.

Q3: What are the most Common Types of Customization?

Ans: TYPE 1: # Changing Forms:

1) . Changing Forms

2) . Validation logic

3) . Behavior

TYPE2: # Changing Report or Program

1) . Appearance

2) . Logic

TYPE3: # Database Customizations:

1) . Adding read only Schema

2) . Augment (add) logic with database Triggers.

TYPE4: # integrating third Party Software

(NOTE: For more Information on customization goes 115devg.pdf Chapter Twenty-Seven)

Q4: What is Legacy system?

Ans: System other than Oracle is legacy System. Like FoxPro, spreadsheet.

Q5: What is ERP?

Ans: Resource Planning with in Enterprise. ERP is a term that covers whole Product line. ERP means integration of different module. Any business will greatly benefits by

adapting this feature because u can customize it or integrate it with other Packages to satisfy unique requirements.

BENEFITS OF ERP: 1). Flow of Information Effectively.

2). Maintaining Standardizations.

Q6: What is Oracle Apps ?

Ans: Oracle-apps is an ERP Package. The Key Feature of all the oracle-Application

module is Data Integration.

Master data is Integrated: All the application share common files of customers, suppliers, employee, items and other entities that are used by multiple applications.

Transaction data is Integrated: Oracle automatically bridge transactions from one system to another.

Financial data is integrated: Financial data is carried in a common format, and financial data is transmitted from one application to another.

Q7: What is ad-hoc Report?

Ans: Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a particular purpose. For example, ad hoc tax codes or an ad hoc database query

Q8: What is Localization?

Ans: Localization is designed to meet the specific needs of certain territories or countries. Most localization is necessary because the local laws or accountings practice differ from country to country.

Region of Localization: Three Region of Localization.

1). EMEA REGION: Europe, Middle East, Asia pacific and Africa.

2). America REGION: Canada plus Latin America.

3). Global REGION: localization that applies territories through the world. For example

Localization used in both Europe and Latin Americaare classified in the Global Region.

Q9: Library used in Localization?

Ans: #Globe: Globe library allows Oracle Application developer to incorporate global

Or regional feature into oracle application forms without modification of

The base Oracle Application forms.

# JA: JA library contains codes specific to Asia\Pacific Region. And is called

Globe Library.

# JE: JA library contains codes specific to EMEA Region. And is called

By Globe Library.

# JL: The JL Library contains code specific to Latin America Region.

And is called by Globe Library.

Q10: How forms are attached.

Ans: STEP- ONE: First put the form in corresponding module like AP, AR, GL

In appropriate server directory.

STEP-TWO: Second step register form with AOL.

STEP-THREE: Attach form with Function.

STEP-FOUR: Attach function with menu.

STEP-FIVE: Attach menu with responsibility.

STEP-SIX: Attach responsibility to user.

Q11: How Report is attached.

Ans11: STEP- ONE: Register the application.

STEP-TWO: Put Report in appropriate server directory.

STEP-THREE: Define Executables. (NavigatoràConcurrentàProgram

àExecutables)

STEP-FOUR: Define Program (Concurrent à Program à Define)

STEP_FIVE: Define Responsibility (Sysadmin responsibility).

(SecurityàResponsibilityà Define).

STEP-SIX: Define Request Group. (Navigatoràsecurity

àResponsibilityàRequest)

STEP-SEVEN: Define Data Group. (Navigatorà oracleàData group).

STEP-EIGHT: Run the request through SRS. A request Id is created

Through which u can view the request.

Q12: What is workflow?

Ans: To automate and continuously increase business process we use workflow.

Workflow processes represent business process flows and information routings.

Main Function:

1). Routing Information’s (sending or receiving information).

2). Defining & modifying Business Rule.

3). Delivering electronic notification. (By emails).

Q13: What is main workflow Component?

Ans13: 1). Workflow Builder. Workflow is the component that provides user interface For creating, reviewing and maintaining workflow Definitions.

2). Workflow Engine.:workflow is the component that executes and enforces The defined workflow Process.

3). Workflow Monitor Workflow is the component of oracle workflow that

Allow you to review the state or status of an item through any particular workflow process.

4). Workflow Definition Loader: allows u to download the text file.

5). Workflow Directory Services: Tells workflow how to find users.

6). Notification System: Send emails and receives responses from the Oracle Workflow notification system.

Q14: What are Interface table in AP, AR & GL?

Ans:

AP INTERFACE TABLE: 1). AP_INTERFACE_CONTROLS.

2). AP_INTERFACE_REJECTIONS

3). AP_INVOICE_INTERFACE

4). AP_INVOICE_LINES_INTERFACE.

AR INTERFACE TABLE:

1). AR_PAYMENTS_INTERFACE_ALL

2). AR_TAX_INTERFACE

3). HZ_PARTY_INTERFACE

4). HZ_PARTY_INTERFACE_ERRORS

5). RA_CUSTOMERS_INTERFACE_ALL

6). RA_INTERFACE_DISTRIBUTIONS_ALL

7). RA_INTERFACE_ERRORS_ALL

8). RA_INTERFACE_LINES_ALL

9). RA_INTERFACE_SALESCREDITS_ALL

GLINTERFACE TABLE:

1). GL_BUDGET_INTERFACE

2). GL_DAILY_RATES_INTERFACE

3). GL_IEA_INTERFACE

4). GL_INTERFACE

5). GL_INTERFACE_CONTROL

6). GL_INTERFACE_HISTORY

Q15 Total numbers of Tables in AP, AR, GL?

Ans; APà 173

ARà 294

GLà 165

FAà 160

POà 132

OEà 109

Q16: How will u customize a form?

Ans: STEP1: Copy the template.fmb and Appstand.fmb from AU_TOP/forms/us.

Then put in custom directory. The libraries (FNDSQF, APPCORE, APPDAYPK, GLOBE, CUSTOM, JE, JA, JL, VERT) are automatically attached.

STEP2: Create or open new Forms. Then customize.

STEP3: Save this Form in Corresponding Modules.

Q17: What are non-financial modules?

Ans: 1). Projects

2). Manufacturing

3). Supply chain management

4). HR

5). Front Office

6). Strategic Enterprise management.

Q18: Explain Order- cycle in OE.

Ans: Step1: Enter sales order.

Step2: Book the sales order.

Step3: Pick release order.

Step4: Ship or confirm order.

Step5: Backorder Release

Step6: Receivable Interface

Step7: Complete line

Step8: Complete order

Q19: What is AU_TOP.

Ans: This is the Application utility contains PL/SQL library used by oracle forms, reports, oracle form source files and a copy of all Java used to generate the desktop Client.

Q20: What is ad_top?

Ans: ad_top (Application DBA). Contain installation and maintenance utility.

Such as Auto upgrade, Auto Patch and Admin Utility.

Q21: Can we make transaction in close Periods?

Ans: No, we can make only reports.

Q22: If Period is closed how we can enter transactions? (Doubt)

Ans: No, we cannot enter transaction.

Q23: what is SQl*Loader?

Ans: This tool is used to move data from a legacy system to oracle database.

In this two type of inputs to be provided to SQL * Loader.

First is data file, containing the actual data.

Second is the control file containing the specification which drive the

SQL* Loader.

Q24: How can u relate order management with AR?

Ans: sales orders are displayed after confirm release of sales in order management.

Q25: What is the Field of GL_interface?

Ans: 1). SET_OF_BOOKS_ID

        2). ACCOUNTING_DATE
          3). CURRENCY_CODE
          4). DATE_CREATED

5). CREATED_BY
6). CURRENCY_CONVERSION_DATE

           7). ENCUMBRANCE_TYPE_ID
           8). BUDGET_VERSION_ID
           9). CURRENCY_CONVERSION_RATE

           10). ACCOUNTED_DR
           11).ACCOUNTED_CR
           12).TRANSACTION_DATE

Q26: In which directory u store your custom form?

Ans:

App_Top is top directory. We have Core directory Adm., ad (application dba),

Au (application utility), fnd (Foundation), Cust-Dem is Custom directory where

Have 11.0.28 version then we have forms directory. Inside the form we have US

Directory. Where we stand forms.

Q27: Who is Holder of Alerts?

Ans: ALERT Manager.

Q28: Steps for upgradation of 11 to 11i?

Ans28: STEP1: Perform category 1,2,3. (Preupgrade steps).

STEP2: Run auto grade

STEP3: Apply database patch to bring your database to the

Current oracle apps release level.

STEP4: Install online help (optional).

STEP5: Perform Category 4, 5, 6 Steps (Post-upgrade steps).

STEP6: Perform product specific implementation steps as listed in your products Users guide.

STEP7: perform upgrade finishing step.

Q28: How interface program is written and for what purpose

Ans28: Interface Program is written through SQL, PL/SQL.

PURPOSE: 1)Basic Integration

2)Imports valid data that is meaningful to Organization 3). Validate the integrity of any data

Before introducing into oracle apps.

4). Imports data from legacy system.

5). Import data from one module to another.

Q29: What is AOL.

Ans: AOL stands for Application Object Library used for customization

And implementation of forms and Reports.

Q30: which Columns are taking care of descriptive flex fields?

Ans: Attribute Columns

Q31: Can u attach two sets of books with single profile?

Ans: yes we can attach.

Q32: How U Can u attaches two sets of books with single profile.

Ans: we can attach different set of Books with different responsibility

In a single profile.

Q33: can we run FSG report other than GL?

Ans: No, we cannot run. Because FSG reports can only run in GL.

Q34: What are the common libraries in AOL.

Ans34: libraries contain reusable client-side code.

Common Libraries in AOL.

FNDSQF: Contain packages for procedures for Message Dictionary,

Flex fields, profiles, and concurrent processing’s.

APPCORE: Contain packages for procedures for Menus and Toolbar.

APPDAYPK: contain packages that control application Calendar.

APPFLDR: packages for Folder.

Qns35: What is Multilanguage support.

Ans35: Oracle Application provides some feature to support multi language support.

Qns36: Can u delete the posted Journals? Can U make Changes in Posted Journals?

Ans36: No, once the posting program in oracle financial has updated accounts balances, you cannot alter the posted journals; you can only post additional entries that negate the original values. These entries contain either the negative values of the original posted amounts or the original values but with the debit amounts and credit amounts reversed.

These approaches are known as reversal method.

Qns68: Name Few Master tables, Set up table I, transaction table in AP, AR, GL.

Ans68:

Module Name	Master table	setup table	Transaction table
GL	1.GL_SET_OF_BOOKS 2.GL_CODE_COMBINATIONS	FND_CURRENCY	GL_JE_LINES GL_JE_HEADRES GL_JE_BATCHES GL_interface GL_CONSOLIDATION GL_SUSPENSE_ACCOUNTS GL_INTERCOMPANY_ACCOUNTS
AP	PO_VENDORS AP_BANK_BRANCHES PO_VENDOR_SITES AP_HOLD_CODES	FND_CURRENCY	AP_BATCHES_ALL AP_INVOICE_ALL AP_DISTRIBUTION_ALL AP_CHECKS_ALL AP_PAYMENTS_HISTOTRY_ALL
AR	HZ_CUST_ACCOUNT	FND_CURRENCY	AR_ADJUSTEMENT_ALL AR_PAYMENTS_SCHEDULE_ALL AR_CASH_RECEIPT_ALL AR_DISTRIDUTION_ALL AR_RECEIVABLE_APPLICATION_ALL.

Qns69: What do u means by FIFO pick and FIFO ship.

Ans69: FIFO pick: First in first out. (Order comes from customer).

FIFO ship: order ship to customer.

Qns70: Difference between SC and NCA.

Ans70:

SC	NCA
1. SMART CLIENT	1. Network computing Architecture
2. No form server in SC. All form is in directory, which is on the client.	2. Forms are in the server. Thus making security higher.

Qns71: What is first step in GL.

Ans71: Creating chart of account.

Qns72: What are standard reports in GL?

Ans72: Trial Balance Report

Journal Report

FSG REPORT

Account Analysis Report.

Qns73: What are standard reports in AP?

Ans73: 1. Supplier Report

2). Payment Report

Qns74: What are standards reports in AR.

Ans74: 1. Tax Report 2. Customer Profile Report

3. Aging Report 4. Dunning Letter Report

Qns75.What are customer table, transaction table, and Receipt table in AR.

Ans

Module	Customer Table	Transaction Table
AR	HZ_CUST_PROFILE_CLASS HZ_CUST_PROF_CLASS_AMTS HZ_CUSTOMERS_PROFILES HZ_CUST_PROFILE_AMTS HZ_CUST_ACCOUNTS HZ_CUST_ACCT_SITES_ALL HZ_CUST_CONTACT_POINTS HZ_CUST_ACCT_RELATES_ALL HZ_CUST_SITES_USES_ALL	RA_CUTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_TRX_TYPES_ALL RA_CUST_TRX_LINE_SALESREPS_ALL

RECEIPT Table

AR_CASH_RECEIPTS_ALL

AR_RECEIPT_METHOD

AR_CASH_RECEIPT_HISTORY_ALL

AR_INTERIM_CASH_RECEIPT_ALL

Qns76: What is Custom-Library.

Ans76: The custom library allows extension of oracle application without modification

of oracle application code. U can use the custom library for customization Such as zoom (moving to another form), enforcing business rule (for example Vendor name must be in uppercase letters) and disabling field that do not apply for your site.

Custom library is placed in AU_TOP / resource directory.

Event Passed to Custom-Library:

1). WHEN_FORM_NAVIGATE

2). WHEN_NEW_FORM_INSTANCE

3). WHEN_NEW_BLOCK_INSTANCE

4). WHEN_NEW_RECORD_INSTANCE

5). WHEN_NEW_ITEM_INSTANCE.

Qns78: What is the Component of alerts.

Ans78: 1. Message

2.SQL SCRIPT

3.Operating system script

4. Concurrent request.

Qns79: What is difference between charge back and adjustment?

Ans79:

CHARGEBACK	ADJUSTMENT
A new debit item that u assign to your customer closing an existing, outstanding debit item.	A receivable feature that allows u to increase or decrease the amount due of your invoice, debit memos, charge back.

Qns80: What are types of invoice?

Ans80:

TYPES OF INVOICES

NINE Type:

Standard

Credit memo

Debit memo

Expense Report

PO default

Prepayment

Quick match

Withholding tax

Mixed

Qns81: What are sub modules in Financials?

Ans81:

Sub module in Financials

GL
AP
AR
FA
CM (cash management)
Financial Analyzer

Qns82: Concept of Multiorganisation, Explain?

Ans82: Multi organization allows u to setup multiple legal entities within a single installation of oracle applications.

ARCHITECTURE OF MULTIPLE ORGANISATIONS

SET OF BOOKS : Within one set of books u may define one or more legal entities.

LEGAL ENTITY: each legal entity has its own employer tax identification number.

And prepare its own tax forms. Each legal entity has its own Tax forms. Each legal entity has its own set of federal tax rule, State tax rule and local tax rule. Legal entities consist of one or More operating units.

OPERATING UNIT: operating units’ represents buying and selling units with in your

Organization. Oracle order Entry, Oracle receivables, Oracle Purchasing, And Oracle Payables.

INVENTORY ORGANIZATION: an Inventory organization is a unit that has inventory

transactions. Possibly manufactures and or distribute products.

Qns83: How will u attach SOB?

Ans83: STEP1: Create a new Responsibility.

(NàsecurityàResponsibilityàDefine).

STEP2: Attach the new responsibility to an existing user.

STEP3: Defining a new Period Type.

STEP4: Defining an accounting calendar.

STEP5: Defining a set of books.

STEP6: Attach the set of books to your responsibility.(NàProfileàSystem)

STEP7: Signing on as new responsibility.

Qns84: What are key functions provided by Oracle General Ledger?

Ans84:

Function Provided by GL

General Accounting

Budgeting

Multiple Currencies

Intercompany Accounting

Cost Accounting

Consolidation

Financial Reporting

Qns85: What do u means by cost center?

Ans85: COST center gives the information about investment and returns on different projects.

Qns86: what is Fiscal Year.

Ans86: Any yearly accounting Period without relationship to a calendar year.

Qns87: What is Credit-memo?

Ans87: A document that partially or reverse an original invoice.

Qns88: How data is transferred from legacy system to Oracleapps table.

Ans88: A system other than oracle apps system is called legacy System.

Qns89: What is Chart of Accounts?

Ans89: The account structure your organization uses to record transaction and maintain account balances.

Qns90: What are different types of budgets?

Ans90:

Types of Budgets

Operating

Capital

Master Production Schedule

Variable

Time-Phased

Qns91: How others modules are integrate through GL.

Ans91: Integration of module With GL

Qns92: Explain Payable Cycles

Ans92: Four steps in AP Cycle

PAYABLE CYCLE

Four steps in Payable Cycles:

STEP1: Enter Invoice (this process may or may not include matching each invoice with PO).

STEP2: Approve invoice payment.

STEP3: Select and pay approval invoices.

STEP4: Reconcile the payment with bank statement

Qns95: AGING BUCKETS?

A. Time periods you define to age your debit items. Aging buckets are used in the Aging reports to see both current and outstanding debit items. For example, you can define an aging bucket that includes all debit items that are 1 to 30 days past due.

Payables uses the aging buckets you define for its Invoice Aging Report

Q96. CREDIT INVOICE?

A. An invoice you receive from a supplier representing a credit amount that the supplier owes to you. A credit invoice can represent a quantity credit or a price reduction.

Q97. CREDIT MEMO?

A document that partially or fully reverses an original invoice.

Q98.CUTOFF DAY?

The day of the month that determines when an invoice with proximate payment terms is due. For example, if it is January and the cutoff day is the 10th, invoices dated before or on January 10 are due in the next billing period; invoices dated after the 10th are due in the following period.

Q99. DEBIT INVOICE?

A. An invoice you generate to send to a supplier representing a credit amount that the supplier owes to you. A debit invoice can represent a quantity credit or a price reduction.

Q100. JOURNAL ENTRY HEADERS?

A. A method used to group journal entries by currency and journal entry category within a journal entry batch. When you initiate the transfer of invoices or payments to your general ledger for posting, Payables transfers the necessary information to create journal entry headers for the information you transfer. Journal Import in General Ledger uses the information to create a journal entry header for each currency and journal entry category in a journal entry batch. A journal entry batch can have multiple journal entry headers.

Q101 What is Oracle Financials?

Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:

Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.

Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.

Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.

Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.

Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.

Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.

Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.

Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.

Oracle Revenue Accounting gives organization timely and accurate revenue and flexible commissions reporting.

Oracle Sales Analysis: Allows for better forecasting, planning and reporting of sales information.

Q102 What is the most important module in Oracle Financials?

The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL system first. GL is relatively easy to implement. You should go live with it first to give your implementation team a chance to be familiar with Oracle Financials.

Q103 What is MultiOrg and what is it used for?

MultiOrg allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit's transaction data separate and secure. Use the following query to determine if MultiOrg is intalled: Select multi_org_flag from fnd_product_groups;

Oracle apps Interview questions – 4

1. Q: How do you make your own query when you are in forms query mode? A: You can use a placeholder to achieve this. If you enter a single colon ( : ) in one of your query fields during the Enter Query mode, Oracle Forms Run Query will prompt you to enter the text of SQL Where clause.

2. Q: What is concurrent processing? A: Concurrent processing is a process that simultaneously runs programs in the background (usually on the server rather than your workstation) while working online.

3. Q: What is a Concurrent Manager? A: A Concurrent Manager is a component of concurrent processing that monitors and runs requests while you work online. Once the user submits a request to run the job, the information is stored in the request table. A concurrent manager gets the information from the request table and executes the specified concurrent job.

4. Q: What is a request set? A request set is a collection of reports or programs grouped together. Once you submit a request set job, it executes all the programs in a report set sequentially or in a parallel manner as defined in the request set.

5. Q: What are the four phases of a concurrent request? The four phases are as follows: inactive, pending, running, and completed.

6. Q: How would you identify the results of the request in the Concurrent View Requests window? Whenever a concurrent job is submitted, Applications creates a Request ID. You can use this Request ID to view the results.

7. Q: What are the profile options? How many levels of profile options are available? Profile options are set to determine how the applications look and feel. There are four levels of profile options available: site level, application level, responsibility level, and user level. You can have various categories of profile options, such as personal options, system options, auditing profile options, currency options, Flexfield options, online reporting options, personal output viewer options, and user profile options.

8. Q: What is a document sequence? A document sequence assigns unique numbers to the documents (transactions) generated by Oracle Applications. For example, each invoice has its own unique invoice number and each purchasing document has its own unique purchase order (PO) number.

9. Q: What are the steps involved in adding a custom program to Oracle Applications?

a) Develop a concurrent program or report.

b) Identify the corresponding executable and register it with the application.

c) Create a concurrent program and its parameters.

d) Add a concurrent program to a request set.

10. Q: How do you register a printer? To add a new printer, go to Install Printer Register.

11. Q: What is a Flexfield? How many types of Flexfields exist? A Flexfield is a field made up of segments. Each segment has an assigned name and a list of valid values. Two types of Flexfields exist: Key Flexfields and Descriptive Flexfields (DFFs).

12. Q: What is a Key Flexfield? A Key Flexfield is a unique identifier that is made up of meaningful segments to identify GL account numbers and item numbers. Key Flexfields are usually stored in SEGMENT1...SEGMENTn database columns.

Some examples would be Item No 34H-AFR-223-112.G and GL Account No:

100-00-1000-324-11100.

For an example GL Account, segments could be identified as Organization,Cost Center, Account, Product, Product Line.

13. Q: What are the Key Flexfields in Oracle Applications? The following table lists some of the Key Flexfields available in Oracle Applications.

Key Flexfields

Using Applications

Accounting

General Ledger

Asset Key

Fixed Assets

Location

Fixed Assets

Oracle Apps Interview questions - 6

What are the various types of Exceptions?
User defined and Predefined Exceptions.

Can we define exceptions twice in same block?
No.

What is the difference between a procedure and a function?
Functions return a single variable by value where as procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

Can you have two functions with the same name in a PL/SQL block ?
Yes.

Can you have two stored functions with the same name ?
Yes.

Can you call a stored function in the constraint of a table ?
No.

What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.

What is Over Loading and what are its restrictions?
Over Loading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.

Can functions be overloaded?
Yes.

10.

Can 2 functions have same name & input parameters but differ only by return data type

No.

ORACLE APPS

What is the Diff between APPS Schema and other Schemas?
Apps schema contains only Synonyms we can't create tables in apps schema, where as other schemas contains tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we will connect to apps schema only.

What is meant by Custom Top and what is the Purpose?
Custom Top is nothing but Customer Top, which is created for customer only. we can have multiple custom
tops based on client requirement. It is used to store developed & customized components. Whenever oracle corp applying patches it will over ride on all the modules except custom top. That’s why we will use custom top.

What is the Significance of US Folder?
It is nothing but language specification by default it is in american language. We can have multiple languages folders based on installed languages. from backend we can get it from
FND_LANGUAGES -- COL --INSTALLED_FLAG I,B,D

              I--INSTALLED,
              B--BASE,
              D--DISABLE
              select language_code,nls_language from fnd_languages where installed_flag like 'B'

Where did U find the Application short name and basepath names?
select basepath,application_short_name from fnd_application from the backend. From the from end we can get it Navigation Application Developer.-----> Application---->Register The application name we will get from FND_APPLICATION_TL

Where can U find the release version from backend?
SELECT release_name from FND_PRODUCT_GROUPS; ---11.5.10.2 .

What are the Folders we will find below the 11.5.0 Folder?
Reports,forms,sql,lib,log,out,bin,admin,html,xml,msg,def, etc

Can we create Tables in the Apps Schema?
No.

Can we have custom schema when it it required?
yes, we can have custom schema, when we want to create a new table we required custom schema.

What is meant by concurrent Program?
It is nothing but Instance of the execution along with parameters & Incompatables. Here Incompatables nothing but if we are submiting cc programs if any one can be execute in those program , which programs r not imp yet this time we will mention those programs in incompatables tab.

10.

What are the steps we will follow to register Reports as Concurrent Program?
First develop the report & save it in local machine. upload into custom_top/11.5.0/reports/us/ go to system administrator open executable form create executable by mentioning executable method as reports ,executable as report name which was created. go to cc program form create ccprogram by attach executable name in executable section. then attach this ccprogram to request group, Request group to Responsibility.Responsibility to User.

11.

What is meant by Request group?
It is nothing but collection of cc programs.

12.

What is Application Top? What are the types and Purpose?

A) When we connect to the server we will find the top called application top. Under application top we have

	Product top.
	Custom top
	Product top is the default top built by the manufacturer. Custom top is used to select the Client for his business purposes. Customizations are done with the Custom top.

13.

What is US folder in the Custom Top?
It is a language specific folder used to store the G.U.I like reports and forms.

14.

What are mandatory parameters of Procedures and what the use of those?

	Errorbuf: It is used to returns the error messages and sent it to the log file.
	Retcode: It is used to show the status of the Procedure with 0, 1, and 2 0 for Completed Normal
	1 for Completed Warning 2 for Completed Error

What is Apps Schema and Schema?

	Schema: Schema is the location in database contains database objects like views, tables, and synonyms.
	Apps Schema: It is used to connect the all schemas to get the information from The database.

16.

What is Token?
a) Use to transfer values to report builder and it is not case sensitive.

17.

Difference between FORM, Function and Menu?

a) A menu is a hierarchical arrangement of functions and menus. Each responsibility has a menu assigned to it. A function is a part of an application that is registered under a unique name for the purpose of assigning it to be including it from a menu.

18.

Tell me something about SQL-LOADER.

Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.
Sql* loader supports various load formats, selective loading, and multi-tables loads.

1) Conventional --The conventional path loader essentially loads the data by using standard ‘insert’ statement.
2) Direct -- The direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.
EX:- My data.csv file
1001, “scott tiger”,1000,40
1002,”gvreddy”,2345,50
Load data
Infile ‘c:\data\mydata.csv’
insert Into table emp Fields terminated by “,” optionally enclosed by‘”’
(empno, empname,sal,deptno)
>sqlldr scott/tiger@vis control=loader.ctl log= gvlog.log bad=gvbad.bad discard=gvdis.dsc .

19.

What is SET-OF-BOOKS?

Collection of Chart of Accounts and Currency and Calendars is called SOB

20.

Tell me what r the Base tables in the AR?

hz_parties (party_id) (store info about org, groups and people)
HZ_PARTIES stores information about parties such as organizations,
people, and groups, including the identifying address information for the party.
hz_cust_accounts (cust_account_id)
HZ_CUST_ACCOUNTS stores information about customer relationships. If a
party becomes a customer, information about the customer account is stored in this table. You can establish multiplecustomer relationships with a single party, so each party can have multiple customer account records in this table.
hz_cust_acct_sites_all (cust_acct_site_id)
HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One
customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.
hz_cust_site_uses_all (site_use_id)
HZ_CUST_SITE_USES_ALL stores information about site uses or business
purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.
hz_party_sites (party_site_id)
HZ_PARTY_SITES stores information about the relationship between Parties
and Locations. The same party can have multiple party sites. Physical addresses are stored in HZ_LOCATIONS.
hz_locations (location_id)
HZ_LOCATIONS stores information about physical locations.
hz_Person_Profiles (person_profile_id)
HZ_PERSON_PROFILES stores detail information about people.
hz_Organization_Profiles (organization_profile_id)
HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics,
socioeconomic and corporate linkage information for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.

21.

FND USER EXITS:-

FND SRWINIT sets your profile option values, multiple organizations and allows
Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.
FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.
FND FLEXIDVAL are used to display flex field information like prompt, value etc
FND FLEXSQL these user exits allow you to use flex fields in your reports
FND FORMAT_CURRENCY is used to print currency in various formats by using formula column

22.

What is Value Set?

The value set is a collection (or) container of values.
Whenever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.

If the list of values needed to be dynamic and ever changing and define a table
based values set.

12) What are the validation types?

1) None -------- validation is minimal.
2) Independent ------input must exist on previously defined list of values
3) Dependent ------input is checked against a subset of values based on a
Prior value.
3) Table ----- input is checked against values in an application table
4) Special ------values set uses a flex field itself.
5) Pair ------ two flex fields together specify a range of valid values.
6) Translatable independent ----- input must exist on previously defined list
of values; translated values can be used.
7) Translatable dependent ------- input is checked against a subset of values
based on a prior values; translated value can be used.

23.

Form development process?

a) Open template form
b) Save as <your form>.fmb
c) Change the form module name as form name.
d) Delete the default blocks, window, and canvas
e) Create a window.
f) Assign the window property class to window
g) Create a canvas (subclass info)
h) Assign canvas property class to the canvas
I) assign the window to the canvas and canvas to the window
j) Create a data block
k) Modify the form level properties. (sub class item Text item)
l) Modify the app_custom package. In the program unit.
m) Modify the pre-form trigger (form level)
n) Modify the module level properties ((console window, First navigation
p) Save and compile the form.
Place the .fmx in the server directory.

24.

How does u customize the Reports?

a. Identify the Short name of the standard report in which module we have
to customize
Ex: - if u wants to customize in the AR module path is
Appl top\ar\11.5.0\reports\US\ .rdf
b. Open the .rdf file in Report builder and change the name of the module.
c. Open the data module and modify the query (what is client requirements)
assign the columns to the attributes.
d. Go to report wizard and select, what r the newly created columns.
e. Then Compile it. Then u will get a .rep file in the specified module. If it is
not in the specified directory then we have to put in the server directory.
f. Then Register in the AOL Concurrent Executable and
Concurrent Program.
g. Go to system administrator Security àResponsibility àrequest.
h. Add and assign a concurrent program to a request group

25.

FLEX FIELDS?

Used to capture the additional business information.

DFF

KFF

Additional	Unique Info, Mandatory
Captured in attribute prefixed columns	Segment prefixed
Not reported on standard reports	Is reported on standard reports
To provide expansion space on your form With the help of []. [] Represents descriptive Flex field. FLEX FILED : DESCRIPTIVE : REGISTER	Used for entering and displaying key information For example Oracle General uses a key Flex field called Accounting Flex field to uniquely identify a general account. FLEX FILED : KEY : REGISTER

26.

Difference between Bind and Lexical parameters?

BIND VARIABLE:
are used to replace a single value in sql, pl/sql
bind variable may be used to replace expressions in select, where, group, order
by, having, connect by, start with cause of queries.
bind reference may not be referenced in FROM clause (or) in place of
reserved words or clauses.
LEXICAL REFERENCE:
You can use lexical reference to replace the clauses appearing AFTER select,
from, group by, having, connect by, start with.
You can’t make lexical reference in pl/sql statements.

27.

what is Flex mode and Confine mode?

Confine mode:

On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders "stretch" when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.

28.

What is Place holder Columns?

A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object.
You can set the value of a placeholder column is in a Before Report trigger.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

29.

What is Formula Column?

A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

30.

What is Summary columns?

A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional
summaries: first, last, standard deviation, variance.

31.

What is TCA (Trading Community Architecture)?

Ans. Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications.

32.

Difference between Application Developer and System Administrator?

Ans.
Role of Technical Consultant:

a. Designing New Forms, Programs and Reports
b. Forms and Reports customization
c. Developing Interfaces
d. Developing PL/SQL stored procedures
e. Workflow automations
Role of System Administrator:
a. Define Logon Users
b. Define New/Custom Responsibility
c. Define Data Groups
d. Define Concurrent Managers
e. Define Printers
f. Test Network Preferences
g. Define/Add new Modules
Role of an Apps DBA:
a. Installing of Application
b. up gradation
c. Migration
d. Patches
e. Routing maintenance of QA
f. Cloning of OA

33.

What are Flex fields?

Ans.

Ans. A Flex field is a customizable field that opens in a window from a regular Oracle Applications window. Defining flex fields enables you to tailor Oracle Applications to your own business needs. By using flex fields, you can:
(a) Structure certain identifiers required by oracle applications according to your own business environment.
(b) Collect and display additional information for your business as needed.
Key Flex fields: You use key flex fields to define your own structure for many of the identifiers required by Oracle Applications. Profile – ‘Flexfields:Open Key Window’ (FND_ID_FLEXS)
Descriptive Flex field: You use descriptive flex fields to gather additional information about your business entities beyond the information required by Oracle Applications. Profile – Flex fields: Open Descr Window’ (FND_DESCRIPTIVE_FLEXS)

34.

Report registration process?

Ans.

1. Create the report using the report builder.
2. Place the report definition file in the module specific reports directory.
3. Create an executable for the report definition file.
4. Create a concurrent program to that executable.
5. Associate the concurrent program to a request group.

35.

Define Request Group?

Ans.

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

36.

Value Sets?

Ans.

Oracle Application Object Library uses values, value sets and validation tables as important components of key flex fields, descriptive flex fields, Flex Builder, and Standard Request Submission.
When you first define your flex fields, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values.
You define your value sets first, either before or while you define your flex field
segment structures. You typically define your individual values only after your flex field has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your flex field.
You can share value sets among segments in different flex fields, segments in
different structures of the same flex field, and even segments within the same flex field structure. You can share value sets across key and descriptive flex fields. You can also use value sets for report parameters for your reports that use the Standard Report Submission feature.
Navigation Path:
Login – Application Developer -> Application -> Validation -> Set

37.

Value Validation Types?

Ans.

1. Dependant
2. Independent
3. None
4. Pair
5. Special
6. Table
7. Translate Independent
8. Translate Dependent

38.

Incompatibility in report registration and Run Alone?

Ans.

Identify programs that should not run simultaneously with your concurrent program because they might interfere with its execution. You can specify your program as being incompatible with itself.
Application: Although the default for this field is the application of your concurrent program, you can enter any valid application name.
Name: The program name and application you specify must uniquely identify a
concurrent program. Your list displays the user-friendly name of the program, the short name, and the description of the program.
Scope: Enter Set or Program Only to specify whether your concurrent program is zincompatible with this program and all its child requests (Set) or only with this program (Program Only).
Run Alone: Indicate whether your program should run alone relative to all other programs in the same logical database. If the execution of your program interferes with the execution of all other programs in the same logical database (in other words, if your program is incompatible with all programs in its logical database, including itself), it should run alone.

Frequently Asked Questions in Oracle Apps Order Management (OM) Drop Ship process

Q1. What is a Drop ship PO?

A: Oracle Order Management and Oracle Purchasing integrate to provide drop shipments. Drop shipments are orders for items that your supplier ships directly to the customer either because you don’t stock or currently don’t have the items in inventory, or because it’s more cost effective for the supplier to ship the item to the customer directly. Drop shipment was introduced in R11.

Q2. How is a Drop Ship PO created?
A: Drop shipments are created as sales orders in Order Management. The Purchase Release concurrent program or workflow in Order Management creates rows in the Requisition Import tables in Purchasing. Then Purchasing’s Requisition Import process creates the requisitions. Drop shipments are marked with the Source Type of External in Order Management and Supplier in Purchasing.

Q3. What is the setup required for Drop ship PO?
A: ITEM ATTRIBUTES:
Navigate: Inventory -> Items - > Organization items
Purchased (PO) Enabled
Purchasable (PO) Enabled
Transactable (INV) Enabled
Stockable (INV) Optional
Reservable (INV) Optional
Inventory Item (INV) Optional
Customer Ordered (OM) Enabled
Customer Orders Enabled (OM) Enabled
Internal Ordered (OM) Disabled
Internal Orders Enabled (OM) Disabled
Shippable (OM) Optional
OE Transactable (OM) Enabled
All Drop Ship items must be defined in the organization entered in the profile option OE: Item Validation Organization and in the Receiving Organization.
All drop ship sub-inventory must have Reservable box checked. If the sub-inventory is not Reservable the sales order issue transaction will not be created in MTL_TRANSACTIONS_INTERFACE. After drop ship inventory organization is created, subinventories should be defined. To create the subinventory, go to an inventory responsibility and navigate to Setup -> Organizations -> Subinventories. Asset subinventories must have the reservable and Asset boxes checked. Expense subinventories must have the Reservable box checked and the Asset box unchecked.
Subinventory Attributes for Asset Subinventory
Reservable/Allow Reservations
Asset Subinventory
Subinventory Attributes for Expense Subinventory
Reservable
Asset-must NOT be enabled.

Q4. How can we avoid the miscounting of supply as logical organization is involved?
A: You must receive drop-ship items in a logical organization. If you use Oracle master Scheduling/MRP and Oracle Supply Chain Planning, to avoid miscounting supply you may not want to include logical organizations in your planning. If you choose to include logical organizations, ensure that doing so does not cause planning and forecasting complications.

Q5. If you make changes to a sales order after the Purchase Order (PO) has been generated, will the order changes automatically be updated on the PO?
A: Order changes will not be automatically updated on the PO. Pulling up the Discrepancy report will allow you to view the differences between the Sales Order and PO. However, you will have to manually update the POs in the Purchasing application.

Q6. If items on a Drop Ship order are cancelled, does the system automatically generate a PO Change to the PO originally sent to the supplier?
A: No, Drop Ship functionality in this regard remains the same as in R11. There is a discrepancy report available that will report differences between the PO and the Sales Order.

Q7. Does Order Management 11i have functionality to do serial number management with Drop Shipments?
A: You are able to receive serial numbered Drop Ship stock. Order Management will receive the serial number noted on the PO.

Q8. Can Configurable Items be drop shipped?
A: Currently only Standard Items can be drop shipped. Functionality for Configurable Items will be added in future releases.

Q9. How do I drop ship across operating units?
A: Release 11i does not currently support this functionality.

Q10. How are over/under shipments handled in drop shipment?
A: If part of a drop-ship line ships, and you do not wish to fulfill the remaining quantity, cancel the line. Over shipments must also be handled manually. If the supplier ships more than the ordered quantity, you can bill your customer for the additional quantity or request that they return the item. Use the Drop Ship Order Discrepancy Report to view differences between your drop-ship sales orders and their associated purchase requisitions and orders.

Q11. Will Blanket PO's work with Drop Shipment?
A: Blanket PO's will not work with Drop shipment because the PO must be created when OM notifies PO that a drop ship order has been created. This PO is linked to the drop ship order so that when the receipt is done (partial or complete) .OM is updated to receiving interface eligible. Drop ship lines do not use the pick release, ship confirm or inv interface order cycles.

Q12. Can we cancel drop shipment after it is received?
A: Drop shipments cannot be cancelled once Oracle Purchasing obtains the receipt. A user who wants to cancel a drop ship sales order line must ensure no receipts have been created against the line and that the requisition and/or purchase order associated with the line is cancelled. Cancellation of a Partial Drop Ship receipt is allowable. But only the portion that has not been received can be cancelled. If you cancel a drop shipment line for which you have not shipped the entire quantity, the order processing splits the line. The first line contains the quantity shipped and the second line contains the non-shipped quantity in backorder. You can cancel the second line the backorder on the sales order. The PO line quantity should be changed to reflect the new quantity.

Q13. What debugging tools are available for Drop shipments?
A: 1. Diagnostic scripts can be used for troubleshooting problems with sales orders.
2. Debugging receipt transaction or the sales order issue transaction, Set the following profile options:
RCV: Processing Mode to Immediate or Batch
RCV: Debug Mode to Yes
OM: Debug Level to 5
INV: Debug Trace to Yes
INV: Debug level to 10
TP: INV Transaction processing mode to Background
-Then go to Sys Admin: Concurrent: Program: Define; query up the Receiving Transaction Processor and check the Enable Trace box.
-Save the receipt for the deliver transaction (destination type will say Inventory for the deliver transaction).
-View the Receiving Transaction Processor log file, the Inventory Transaction Worker log file, as well as, the trace for the errors.

Q14. What is the Import source and status of PO generated from Drop Shipment?
A: Import source is Order Entry.
Status of PO will always be Approved.

Oracle Apps AR Interview Questions

1. What is TCA? Tables?

A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.

HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name.

The parties can be one of four types:

Organization for example, Oracle Corporation
Person for example, Jane Doe
Group for example, World Wide Web Consortium
Relationship for example, Jane Doe at Oracle Corporation.

HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.

HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_CUST_CONTACT_POINTS etc.

2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?

A) Customer Conversion:

Interface Tables :RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL, RA_CONTACT_PHONES_INT_ALL,

RA_CUSTOMER_BANKS_INT_ALL,

RA_CUST_PAY_METHOD_INT_ALL

Base Tables :RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,

RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc

B) Auto Invoice:

Interface Tables :RA_INTERFACE_LINES_ALL,RA_INTERFACE_DISTRIBUTIONS_ALL, RA_INTERFACE_SALESCREDITS_ALL,RA_INTERFACE_ERRORS_ALL

Base Tables :RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL,RA_CUST_TRX_TYPES_ALL

C) AutoLockBox:

Interface Tables :AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)

Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation) : AR_INTERIM_CASH_RCPT_LINES_ALL, AR_INTERIM_POSTING

Base Tables : AR_CASH_RECEIPTS_ALL,AR_RECEIVABLE_APPLICATIONS_ALL,

AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)

3. What are the tables in which Invoices/transactions information is stored?

A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.

RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).

RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.

The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

4. What are the tables In which Receipt information is stored?

A) AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.

Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.

AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.

AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.

5. What are the tables in which Accounts information is stored?

RA_CUST_TRX_LINE_GL_DIST_ALL

6. What are the different statuses for Receipts?

A) Unidentified – Lack of Customer Information

Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)

Applied – When all the required information is provided.

On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.

7. What Customization that you have done for Autolockbox?

8. What is Autolockbox?

A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps

· Import (Formats data from bank file and populates the Interface Table),

· Validation(Validates the data and then Populates data into Interim Tables),

· Post Quick Cash(Applies Receipts and updates Balances in BaseTables).

9. What is Transmission Format?

A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.

10. What is Auto Invoice?

A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.

Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.

11. What are the Mandatory Interface Tables in Auto Invoice?

RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL

RA_INTERFACE_SALESCREDITS_ALL.

12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?

A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.

13. What is AutoAccounting?

A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.

14. What are Autocash rules?

A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.

15. What are Grouping Rules? (Used by Autoinvoice)

A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.

16. What are Line Ordering Rules? (Used by Autoinvoice)

A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.

17. In which table you can see the amount due of a customer?

A) AR_PAYMENT_SCHEDULES_ALL

18. How do you tie Credit Memo to the Invoice?

At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.

19. What are the available Key Flex Fields in Oracle Receivables?

A) Sales Tax Location Flex field, It’s used for sales tax calculations.

Territory Flex field is used for capturing address information.

20. What are Transaction types? Types of Transactions in AR?

A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.

21. What is AutoAssociating?

22. What are the issues you faced in AutoInvoice and Autolockbox?

How to submit a concurrent program from pl sql

How to submit a concurrent program from backend:

Using FND_REQUEST.SUBMIT_REQUEST function & by passing the required parameters to it we can submit a concurrent program from backend.

But before doing so, we have to set the environment of the user submitting the request.

We have to initialize the following parameters using FND_GLOBAL.APPS_INITIALIZE procedure:

· USER_ID

· RESPONSIBILITY_ID

· RESPONSIBILITY_APPLICATION_ID

Syntax:

FND_GLOBAL.APPS_INITIALIZE:

procedure APPS_INITIALIZE(user_id in number,
resp_id in number,
resp_appl_id in number);

FND_REQUEST.SUBMIT_REQUEST:

REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( application => 'Application Name', program => 'Program Name', description => NULL, start_time => NULL, sub_request => FALSE, argument1 => 1 argument2 => ....argument n );

Where, REQ_ID is the concurrent request ID upon successful completion.

And concurrent request ID returns 0 for any submission problems.

Example:

First get the USER_ID and RESPONSIBILITY_ID by which we have to submit the program:

SELECT USER_ID,

RESPONSIBILITY_ID,

RESPONSIBILITY_APPLICATION_ID,
SECURITY_GROUP_ID

FROM FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
                           FROM FND_USER
                             WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
                                      FROM FND_RESPONSIBILITY_VL
                                      WHERE RESPONSIBILITY_NAME = '&resp_name');

Now create this procedure
CREATE OR REPLACE PROCEDURE APPS.CALL_RACUST (p_return_code OUT NUMBER,
                                                                         p_org_id NUMBER, -- This is required in R12
                                                                         p_return_msg OUT VARCHAR2)

v_request_id VARCHAR2(100) ;
p_create_reciprocal_flag varchar2(1) := 'N'; -- This is value of create reciprocal customer

-- Accounts parameter, defaulted to N
BEGIN

-- First set the environment of the user submitting the request by submitting
-- Fnd_global.apps_initialize().
-- The procedure requires three parameters
-- Fnd_Global.apps_initialize(userId, responsibilityId, applicationId)
-- Replace the following code with correct value as get from sql above

Fnd_Global.apps_initialize(10081, 5559, 220);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST('AR','RACUST','',
'',FALSE,p_create_reciprocal_flag,p_org_id,
chr(0) -- End of parameters);

p_return_msg := 'Request submitted. ID = ' || v_request_id;
p_return_code := 0; commit ;
EXCEPTION

when others then

p_return_msg := 'Request set submission failed - unknown error: ' || sqlerrm;
p_return_code := 2;
END;

Output:
DECLARE
V_RETURN_CODE NUMBER;
V_RETURN_MSG VARCHAR2(200);
V_ORG_ID NUMBER := 204;
BEGIN
CALL_RACUST(
P_RETURN_CODE => V_RETURN_CODE,
P_ORG_Id => V_ORG_ID,
P_RETURN_MSG => V_RETURN_MSG
);
DBMS_OUTPUT.PUT_LINE('V_RETURN_CODE = ' || V_RETURN_CODE);
DBMS_OUTPUT.PUT_LINE('V_RETURN_MSG = ' || V_RETURN_MSG);
END;

If Return Code is 0(zero) then it has submitted the Customer Interface program successfully and the request id will appear in Return Message as :
V_RETURN_CODE = 0
V_RETURN_MSG = Request submitted. ID = 455949

Give me your Feedback.......

Koteswar Oracle Apps Techno Functional Blog

Friday 22 April 2016

How to open Periods in Oracle Apps

Sunday 19 July 2015

Oracle apps PLSQL interview questions

Oracle apps PLSQL interview questions

The above piece of code can be written In procedure and function DDL's can be used in function provided that function should be invoked in Begin-End block not from Select statement. Introduction to REF CURSOR

Oracle apps PLSQL interview questions

Sequence of SQL statement processed

SRW.SET_attributes

SRW.REFERENCE

SRW.GETERR_RUN

SRW.DO_SQL

Oracle Apps Interview Questions – 3

STEP2: Create or open new Forms. Then customize.

Qns35: What is Multilanguage support.

Ans35: Oracle Application provides some feature to support multi language support.

GL

AP

AR

Oracle apps Interview questions – 4

Oracle Apps Interview questions - 6

Frequently Asked Questions in Oracle Apps Order Management (OM) Drop Ship process

Oracle Apps AR Interview Questions

How to submit a concurrent program from pl sql

Followers