D2k Interview Question

1) what is difference between d2k report and oracle apps report

D2k Report : We can't Schedule the Program.

Apps Report: We can Schedule the Program.

D2k Report : We can't use User_Exits.

Apps Report : We can use User_Exits.

D2k Report : We can't give the Security. i.e Any body can

run the report.

Apps Report : We can give the Security by Assigning the

Concurrent Program to Particular Request Group and

Responsibility.

D2k Report : We can't Run more than one Report at a Time.

Apps Report : We can Run more than one Report at a Time by

using Request Set.

The main difference of d2k report and oracle apps report is

that in oracle apps report we use token when we register

the report in oracle apps but in D2k report no token is

required. without giving token in registration process, we

can't run the report, if report is using user parameters.

2) What are the requests groups?

Request groups is a set of concurrent programs and request

Set. Through which u can submit a single at a time

A request set is group of one or more concurrent programs

Which run one after the other.

3) What is lexical parameter?

Substitute multiple values at run time whereas bind

Variable substitute one value at run time

with this u can specify a complete where condition for

example select * from emp where &lex_par; i.e we can

specify where conditions. with bind variables we give values

to columns in query

Lexical references are placeholders for columns or

parameters that you embed in a SELECT statement. You can

use lexical references to replace the clauses appearing

after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING,

CONNECT BY, and START WITH. Use a lexical reference when

you want to substitute multiple values at runtime. You

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

can, however, use a bind reference in PL/SQL to set the

value of a parameter that is then referenced lexically in

SQL, as shown in the example below. You create a lexical

reference by typing an ampersand (&) followed immediately

by the column or parameter name. A default definition is

not provided for lexical references.

Therefore, you must do the following:

Before you create your query, define a column or parameter

in the data model for each lexical reference in the query.

For columns, you must set the Value if Null property, and,

for parameters, you must set the Initial Value property.

Restrictions::::

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

If a column or parameter is used as a lexical reference in

a query, its data type must be character.

If you want to use lexical references in your SELECT

clause, you should create a separate lexical reference for

each column you will substitute. In addition, you should

assign an alias to each lexical reference. This enables you

to use the same layout field and boilerplate label for

whatever value you enter for the lexical reference on the

Runtime Parameter Form.

If you use lexical references in your SELECT clause, you

must specify the same number of items at runtime as were

specified in the report's data model. Each value you

specify for your lexical references at runtime must have

the same datatype as its Initial Value.

A lexical reference cannot be used to create additional

bind variables after the After Form trigger fires. For

example, suppose you have a query like the following (note

that the WHERE clause is replaced by a lexical reference):

SELECT ENAME, SAL FROM EMP

&where_clauseIf the value of the where_clause parameter

contains a reference to a bind variable, you must specify

the value in the After Form trigger or earlier. You would

get an error if you supplied the following value for the

parameter in the Before Report trigger: WHERE SAL

= :new_bind If you supplied this same value in the After

Form trigger, the report would run

Reports Builder uses these values to validate a query with

a lexical reference. Create your query containing lexical

references.

Lexical Parameters are placeholders for text that you

embed in a SELECT statement. You can use lexical

references to replace the clauses appearing after SELECT,

FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and

START WITH.

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

You can, however, use a bind reference in PL/SQL to set the

value of a parameter that is then referenced lexically in

SQL.

example:

we can use (&)lexical parameter in where cluse and inside

from clause and group by clause i enclose the query used in

reports 6i.

select

decode (UPPER(:p_order_by),'CUSTOMER',NULL,

types.cust_trx_type_id ) dummy_id_inv,

decode (UPPER

(:p_order_by),'CUSTOMER',NULL,types.name)

dummy_type_inv,

ps.trx_number

invnum ,

types.name

invoice_type_inv,

ps.amount_adjusted

amount_adjusted_inv,

ps.amount_applied

amount_applied_inv,

ps.amount_credited

amount_credited_inv,

ps.gl_date

gl_date_inv,

&lp_bal_segment

bal_segment_value_inv,

&lp_query_show_bill cons_billing_number,

from ra_cust_trx_types_all types,

hz_cust_accounts cust_acct,

hz_parties party,

&lp_ar_payment_schedules_all ps,

&lp_table_show_bill

&lp_ra_customer_trx_all trx,

hz_cust_site_uses_all site,

hz_cust_acct_sites_all addr,

hz_party_sites party_site,

hz_locations loc,

&lp_ra_cust_trx_gl_dist_all gld,

where TRUNC(ps.gl_date) <= :p_as_of_date

and ps.customer_trx_id+0 = trx.customer_trx_id

and ps.customer_id = cust_acct.cust_account_id

and cust_acct.party_id = party.party_id

&lp_customer_name_low1

&lp_customer_name_high1

&lp_customer_number_low1

&lp_customer_number_high1

and ps.cust_trx_type_id = types.cust_trx_type_id

and nvl(ps.org_id,-99) = nvl(types.org_id,-99)

&lp_invoice_type_low

&lp_invoice_type_high

and ps.customer_site_use_id+0 = site.site_use_id+)

and site.cust_acct_site_id = addr.cust_acct_site_id(+)

and addr.party_site_id = party_site.party_site_id(+)

and loc.location_id (+) = party_site.location_id

and ps.gl_date_closed > :P_AS_OF_DATE

&lp_curr_code

and ps.customer_trx_id+0 = gld.customer_trx_id

and gld.account_class = 'REC'

and gld.latest_rec_flag = 'Y'

and gld.code_combination_id = cc.code_combination_id

&lp_bal_segment_low

&lp_bal_segment_high

&lp_where_show_bill

&P_ORG_WHERE_CTT

&P_ORG_WHERE_PS

&P_ORG_WHERE_ADDR

&P_ORG_WHERE_GLD

4) which parameters passed in report in oracle developer how to pass ?

In Oracle Apps by using Token option we can map

Oracle Reports bind parameters and concurrent program

Parameter names.

5) How many standard interface programs in oracle purchasing module and oracle inventory? What are they?

Oracle Puchasing:

1. Requisitions Open Interface

2. Purchasing Documents Open Interface

3. Receiving Open Interface

Oracle Inventory:

1. Customer Item Interface

2. Open Item Interface

3. Open Replenishment Interface

4. Open Transaction Interface

5. Cycle Count Open Interface

6. Reservations Open Interface

7. Move Orders Open Interface

inv:

import item

po:

requisition_import

import price catelog

import standert purcha order

6)what is the difference between Procedure and Function ?

Procedure perform one or more actions it may return or may

not return values if u want to return procedure will return

multiple values.we can use procedures in oracel apps also

Function perform one or more actions and it must return any

value multiple values will not return function. Functions

we cant use in Oracle Apps. We can perform functions on SQL

prompt itslef

Function returns only one value, whereas procedure returns

multiple values.

Function can be used in SQL queries whereas we can't use

procedures.

I think in procedures values by reference is not possible.

7) Maximum how many parameters we can pass through Pl/Sql stored procedure to Oracle Apps? How many mandatory parameters we can pass through pl/sql program to oracle apps11i?

100 parameters can be passed using FND_REQUEST.SUBMIT_REQUEST

Errorbuf and Retcode should always be passed while creating

pl/sql stored procedure for oracle apps.

8) What is multi org?

Legal entity has more than one operating unit is

called as multi org?

a) Business group --- Human resources information

is secured by Business group

b) Legal entity. --- inter-company and fiscal/tax reporting.Security  responsibility 

operating unit.

c) Operating unit --- secures AR, OE, AP, PA and PO Information.

d) Organizations --- is a specialize unit of work at

particular locations

Under single implimentation, maintaining multiple

organizations like business group,operating units,inventory

organizations is called "Multi Org"

9) WHAT IS USE OF APPS API?
There are many API's in apps each performs a definite task.

we use some API's to load data into apps and use some API;s

to get data from apps into a file...we use then to set

org_id in toad..

Oracel uses many API's to process the apps data.

API's are the Application Programmin Interface ,it means

that these API play a role in transferring the data from

the stagging tables to the base tables once they are fired

10) WHAT IS REFCURSOR IN ORACLE?

Refcursor is nothing but dynamic cursor... it means u can

Change the cursor definition at run time... unless like

Normal cursor where u defines and declares once.

In Oracle RefCursor is a cursor where we can change the

select statement dynamically.

First a Refcursor Variable should be declared in

Declaration Part

Refcursor Variable is like a normal variable, I.e. we can

Change the value of variable at any time.., similarly now

Refcursor Variable can be changed with any sql query.

11) When we run payment batch what are the 3 concurrent programs?

Auto Select Invoices, Build Payments, Format Payments

12) what are the profiles ?

profiles are used to change the way of application running

there are two types profiles

personal profile: we can't change the profile values

ex:userid,username,respname,respid...

system profile: we can change the profile values

ex:mo:operatingunit,mfg:organization_id,hr:user_type........

13) What is the Use of User Exit in Oracle apps and when we are developeing the new report you need to pass one parameter called P_CONC_REQUEST_ID,what is the use of this parameter and is it mandatory.Can't we develop report without this User exit's and P_CONC_REQUEST_ID parameter

User Exit plays very important role in Oracle apps with of

help this we can capture value at runtime with profile option.

And P_CONC_REQUEST_ID is mandatory if we using User Exit

otherwise it gives error in report p_conc_request_id not

found . If we not using User Exit then it’s not mandatory.

Further we can develop report without using User Exit and

P_CONC_REQUEST_ID.

User Exit is a program, which stops the execution of

process and transfer the controls to other Third generation

Language. After that it executes the remaining execution

process.

P_conc_request_id is the system parameter. It is used to

generate the concurrent request id.

If we used user_exits in report builder we should use

p_conc_rquest_id, because it is mandatory

14) what is mutating table and mutating error and how to solve that?

"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating", and raises an error since Oracle should never return inconsistent data.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

when you try to perform an action such as insert,update or

delete on that same table using a trigger then mutating

table error occurs.

if u want to solve this error then use autonomous

transaction.

15) I am insert in data to a table X .Which one is better while

writing commit statement After each row inset or after all row insert to table

Each row will be commit

16) How Can function be overload?

only function in a package can be overloaded and

standalone functions can not be overloaded.

17) HOW CAN U KNOW THE RESPONSIBILITY BELONGS TO OPERATING UNIT LEVEL OR NOT

The MO:Operating Unit Profile will give the details

In Help menu go to Tools then examine

now select $profile$ as block and field AS MO:OPERATING

UNIT.

18) Are there any alternate tools other than sql* loader to load data into oracle apps tables from external systems

Apart from sql*loader you can use UTL_FILE package to

upload the data from external systems to oracle tables

data loder,

utl_file,

with dba link(data should be in table),

Pro*c,

Style sheet conversion,

ora loader(tool)

19) What is responsibility and how u attach

Responsibility is a level of authority. it contains 3

Mandatory fields.

1. Menus

2. Data groups

3. Request group based on the security. We will attach to the

User

Responsibility is the link between menu, data group,

Request group

Here request group is not mandatory but data group and

menu is mandatory

we define responsibility in system adminstrator

system Administrator==>responsibility==>define

(responsibility name

applicationname

respkey

menu name

request group name

data group name)

and save it and attach the responsibility to any user

request group is a collection of concurrent

programs,request set and applications

menu is a hierarchal collection of functions menus of

functions

datagroup- used to pair applicattion and user

First u log in to your user,

next create responcibility

next go to security ->user->define

enter to your user

attach responsibility to user

20) while running a interface if we get 100 records as erors. how to debug it.

We are having to two types the error records.....

1)Rejected by the SQL Loader.

These are stored in the '.BAD' file......

2)Rejected by the condition which have been mention in the

control file.

These are stored in the '.dis' file......

atfer getting the error records in the specified file then

we will correct the errors and proceed them once again...

21) Name the different Cursor Attributes

The Cursor Attributes available are:

%ISOPEN

%NOTFOUND

%FOUND

%ROWCOUNT

22) how to make a parameter optional in the query?

select * from FND_User a

where user_name = nvl(:User_Name, a.user_name)

23) How many types of invoices?

there 10 types of invoices are there

standrd invoice

credit memo

debit memo

expence report

po default

quick match

prepayment

mixed

withholding tax

recurring

24) In a table their 20 records.I had update 6 records???How can see and retrieve particular 6 records.i.e.,Latest updated.

select * from aa

where rownum <= 6

order last_updated_date desc

25) what are the error tables in AP?

AP_INTERFACE_REJECTIONS

26) what is trace file? what is the use of that?

trace file is a log of SQL run in a particular session or

sesesions

focused on selects, inserts, updates, and deletes.

A trace file can be used in many circumstances including

reviewing performance,

finding tables and views referenced, or finding the root of

an error

25) I Have report which is having field at the end of page,,, problem is when i run report data is overflowing to the next page,,i want the data to come on same page can anybody help me out????

try to put page protect is yes, then your report will be fit on same page

27) Give a brief overview on OrderManagement.

order mangement deals with order to cash

first we have to enter the order

order returns----sales order

we have to enter the order in headers and lines information

when we enter the order it shows entered in both headers

and lines

after that we have to book the order

after entering it shows booked in headers and awaiting

shipping in lines

it shows the release status --R means ready for relaese

after that we have to do pick release

when picked it shows booked in headers in headers and

picked in lines

it shows the flow status code changes from R to Y means

Pick Confirmed

in the back ground two concurrent will run

1) Pick Slip Report

2) Shipping Exceptions report

After that we have to do shipping

while shipping the flow status code will change from Y to C

means Ship Confirmed

in the background it runs 5 Concurrent programs

Automatically

1)Bill of Lading

2) Commercial Invoice

3)Packing slip Report

4)Interface trip stop

5)Vehicle load sheet Details

After that we have to run the workflow Background Program

After that we have to do the Invoices

After that we have to do the receipts

After that we will transfer it in to General Ledger

Transform Program

28) difference between 2 way and 4 way matching

2 way : Inv will be generated as per PO.

4 way : Inv will be generated after compare

PO,Receipt and Inspection.

29) what do you mean by value sets

A set of predefined and validated values assigned to a field, that restricts the user from entering junk or invalidate data

30) What is the 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

31) tell me the scenarios of between pages in reports

if u hav 10 pages,between pages fires 9 times,it doesnt fires for the first page

32) Can 2 functions have same name & input parameters but differ only by return datatype ?

Yes, It is possible in the packages (packaged functions).

You can override the function. It works perfect.

But the same if you try on standalone this doesn't work.

Object must be unique

33) Write an SQL Query to Delete Duplicate records from a table using ROWID.

Delete from table_name where ROWID not in ( select max(rowid) from table group by duplicate_values_field_name);

delete from tablename where rowid not in(select min(rowid)

from table name group by duplicate record col)

delete from table (table name.a) where

rowid not in(select min(rowid) from (table name.b)

group by (column name);

34) What is cursor?

Cursor is a private SQL worksheet area where we can execute SQL commands and to store processing information.

35)how can u link a org-id with a responsibility in reports?

profile option Mo_operating_unit is setup at responsibility level,,

we have securing attributes in the reposibility form we set the org id value there

36)how to call report from oracle oracle apps11i?

Registration Steps for reports:

1. First Create the executable using Syatem Admin

Responsibility.

2. Define Concurrent Program for it .

3. Attach request group for that responsility to want to

attach report .

If in XML format , 2 more steps Needed

4. Create Data Definition in XML Publisher responsility.

5. Create Template In XML Publisher responsibility

37) which is the table for storing organization information?

All the below tables give you the organization information.

Its upto you to decide on which one to use... :)

1.mtl_parameters

2.oe_order_headers_all

3.HR_ALL_ORGANIZATION_UNITS

4.HR_ORGANIZATION_UNITS

5.HR_OPERATING_UNITS

6.org_organization_definitions

38) What is Pragma Autonomous transaction. It's Real time Example

Pragma Autonomous Transaction is which is executed in the execution of another transaction known as parent transaction which is independent of it

the whole transaction splits in to two parts known as

parent& child will be independent of both but have the same

relationship

39)INBOUND INTERFACE: Suppose I have 100 Records of employees in flat file.When moving the records from flat file to STAGING table using .CTl file, I want to restrict the records whose salary is less than 500.what are the validations(or conditions) you write to achieve this.

we can apply the condotion by using 'when sal<500' before columns name and after the into statemnt

40) What is P2P cycle.

GO TO PURCHASING VISION OPERATION RESPONSIBULITY

REQUISITION

REQ

QUOTATION

PURCHASE ORDER

RECEIPTS

THEN GO TO ACCOUNT PAYABLES

INVOICES

PAYMENTS

|----PAYMENTS REGISTER IN GL

41) what is organization id

Organization ID is the ID that is assigned to each and

every inventory organization. Dont confuse this with the

Operating Unit ID.....Operating unit is the actual place

wheere the monthly yearly fiscal reports are created. The

inventory organization is the palce where the stock is

maintained or manufactured.

42) WHAT IS THE USE OF SKIP WHERE U WILL GIVE?

It is used in SQL Loader

At the begining of control file

(OPTIONS SKIP=10)

U can set it in command line also

SKIP gives the information to SQL Loader not to load first

10 lines

43) yes it skips the first records depending upon size that is

given in skip command

44) IN GL,AP,PO,AR WHICH MODULE DOESNOT HAVE _ALL TABLES THEN WHY?

GL does not have _all tables only sublegders AP,PO,AR have the _all tables. subledgers secure data operating unit wise with org_id column that exits in _all table. GL secure and spreate data with set_of_book_id that exist in gl_sets_of_books table.

45) what is MRC? Why it is needed?

efore knowing MRC you should have a clear picture of

REVALUATION - Is the process of converting funtional

currency into other currency and reporting any gain or loss

in terms of funtional currency.

TRANSLATION - Is the process of just translating balances

into other currency.

Mrc - gives more detail picture of reporting , here we can

assign 8 reporting set of books

once we come across the period ending process for general

ledger,(assuming we have doen transsaction with US)

we do revaluation with USD and see if there is any gain or

loss, Example purchased goods on 1jan exchange rate is 45

now i made payment on 31jan and the exchange rate is 40 ,

in this tpye of circumstances we do revaluation .

translation (assuming our parent company is in US)

In this case all the funtional curency(accounts balances)

will be translated in USD this is for just reporting to our

parent company , Example GE(hyd) GE(usd) it has to report

the balances into usd. it will translate the balances (no

revaluation)

mrc-multiple reporting currency.

it is very essential to perform transctions in diff

currencies or to convert foreign currency in inr.

46) How do u customize the Reports?

Identify the Short name of the standard report in which

module we have to customize

Ex: - if u want to customize in the po module path is

Appl top\po\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.

Concurrent

 program.

g. go to system administrator SecurityResponsibilityrequest

h) Add and assign a concurrent program to

a request group

47) How do u identity its name of report?

System administrator -> concurrent -> program -> define

System administrator -> concurrent -> program ->executable

48) what are the receipt types in Po?

Direct Delivery,

Standard Receipt,

Inspection Required.

You receive the material as the Receipt Routing specified in

Receiving control tab in Purchase Order

49) How can u call a standard interface program from sql or pl/sql code?

Any program (whether a standard or custom ) which is

registered as a concurrent can be called by using

SUBMIT_REQUEST function available in FND_REQUEST package.

The syntax is as under

declare

v_req_id_out number;

begin

v_req_id := FND_REQUEST.SUBMIT_REQUEST

('GEMSAP','GE_AP_IND_BILL_DISCOUNT_MAILER',NULL,Null,False,

NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,

NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,

NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL);

end;

here 'GEMSAP' is the Application Short Name (Can Vary for

ur Program)

'GE_AP_IND_BILL_DISCOUNT_MAILER' is the Program Short Name

and after False there are 100 parameters to pass any value

from parameter.

50) What r the validation types?

oracle supports 8 types of validations, but GL accounting

flexfield supports only 3 types of validation.

1. independent

2. dependent

3. table

coming to rest of validation we have

4. none

5.special

6.pair

7.translatable dependent

8.translatable independent

Independent:this tpye of value set does not depend on value

of other segement, example department is independent where

as sub department is dependent. Independent value set are

stored in application object library table.

dependent:sub department depends on independent value

(department).

Note: u wont see dependent value while defining flexfiled

qualifier.

Table: this tpye of value set is done with the help of

technical consultant. if we are using this type we cant

enter new segment values. it iwlll be predefined by

technicalconsultant.

Example in benz showroom u can see only few types of models

C,S,E classes and if the user creates new segment value by

Dclass it a loss to the show room . so in order to restrict

the user table type of validation comes into picture.

coming to other types

Tranlatable independent: when u install oracle application

in different langauges we use this type of validation .

None:after selecting none type if u enable number only

check box user cannot create values in alphabetic

characters,similarly if u use uppercase it allows only

alphabetic characters.

Special & pair :

They are used in reports design . so dont worry about that

51) What is the Lookup's and what is the use of lookup's?

Lookups are referred in more locations.

Instead of storing the original value, we can store the lookup codes.

this will reduce the space that is occupied in the database.

this is one reason.

the main reason is..

take an example...

in a table xx_emp there is one column gender.

possible values are

male m

female f

other o

define the above values in a lookup.

if u want to change the male to female ..u go and update

the lookups then automatically all the records referring to

that lookup will also change dynamically...

This saves time and complexity.

hope this clears the above question..........

52) can we pass bind variablein lexical parameter?

Bind varible means?Is it the assignment value to some

variable.If so then we can pass that value to the lexical

parameter.

53) why we need to do Validations when the Standard Conc.

Prog or the API will do all the validations

For Business validations or we need to validate data before inserting data in to interface tables.

54) How can we display more then 1 column data in a formula column (Ex displaying Empno, Ename, sal data in one Formula column)

We can display more than one column data in formula by taking one placeholder column. By using the formula column always returns value right, so based on the formula column we can display the data for placeholder column

55) is it possible to give 2 out paramaters in a function

Yes, with one return value, we can give OUT parameters also

56) what is the need of registering table in apps?

If a table is used in flexfilelds then we have to register a table.otherwise it is not necessary to register a table

57) We have to show a logo/image in a report? what are the steps and which format it is?whether in pdf format is applicable or not?

layout model-->header section we will chose file--import

image.which part u kept the image it will come at browser.

58) What are the Import Programs We need to run after creating Interface table for all modules

FOR AP -- PAYABLES OPEN INTERFACE

OM -- ORDER INPORT

AR -- AUTO INVOICE

INV -- ITEM IMPORT

59) What is Place holder Columns?

It is like Global Variable , you can store the value in

the place holder and take whereever you want at run time

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

60) What are different execution methods of executabls?

FlexRpt The execution file is wrnitten using the

FlexReport API.

FlexSql The execution file is written using the FlexSql API.

Host The execution file is a host script.

Oracle Reports The execution file is an Oracle Reports file.

PL/SQL Stored Procedure The execution file is a stored procedure.

SQL*Loader The execution file is a SQL script.

SQL*Plus The execution file is a SQL*Plus script.

SQL*Report The execution file is a SQL*Report script.

Spawned The execution file is a C or Pro*C program.

Immediate The execution file is a program written to

run as a subroutine of the concurrent manager. We recommend

against defining new immediate concurrent programs, and

suggest you use either a PL/SQL Stored Procedure or a

Spawned C Program instead.

61) what do u mean by debit/credit memo invoices?

IN AP MODULE

DEBITMEMO:IT IS THE DOCUMENT ISSUED BY SUPPLIER TO

THAT HE IS REDUCING THE INVOICE AMOUNT

CREDITMEMO:SAME LIKE DEBITMEMO BUT IT IS ISSUED BY

CUSTOMER

Debit memo will be rised by us, credit memo will be rised

by the supplier. Both memos increases the Laibility of the

Suppliers.

we will rise dedit memo when u return the good to supplier.

credit memo will be rised by supplier for any damaging

goods, discounts etc.

In Receivables

Credit memo- will be rised when ruturned goods from the

customer. it derceases the Receivable A/C of the customer

Debit memo- it increses recevable A/C of the customer(Any

additional charges

62) What is an Index , Types od index why we are use index.

An index is a performance-tuning method of allowing faster

retrieval of records. An index creates an entry for each

value that appears in the indexed columns. By default,

Oracle creates B-tree indexes.

Create an Index

The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name

ON table_name (column1, column2, . column_n)

[ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the

indexed columns must be unique.

Index is a performance tuning method by using this we can

retrive records fastly index types are

- unique index

- non unique index

- bitmap index

- B-tree index

63) where is setup of business group done?

Business Groups are defined in Oracle HRMS Module under

Organization Description.

Navigation : US HRMS Super User--> Work Structures -->

Organization --> Description.

Make sure you enable Business group under Organization

Classifications.

64) what is multi org structure?

multi-org in nothing but implementing multiple organizations

under single oracle apps implementation.

Business Group

set of books

legal entity

operating unit

inventory organizations

link between po and ap modules

po_distribution_id in the tables

po_distributions_all and ap_invoice_distributions is the

link btwn ap and po.

65) what is difference between API and Interface?

An API (Application Programming Interface) is inbuilt

program through which data’s can be transferred to Oracle

base tables directly without writing the program for

validating or inserting in Interface tables. But through

User Interface, we have to write codes for validation and

insertion of data’s in Interface tables and then in Oracle

base tables

66) can we use commit in trigger? If yes how?

Generally we cannot use commit in triggers.

But it is possible to use commit in triggers by

mentioning "pragma autonomous_transaction;" in the

declarative section of the trigger.

67) What is the result of this query ? SELECT * FROM EMP WHERE ROWNUM = 1

THE CONDITION ROWNUM = 1 EVALUATE TRUE(i.e 1=1) HENCE

IT DISPLAY THE FIRST RECORD FROM THE EMP TABLE.

68) While importing data into interface tables,if u got number of records are error(like more than 1000 records are error),how to imform user?

For this, It is important to know how you are populating the

data in the Interface table from the flat files.

If you are using the SQL*LOADER then it will create

bad file automatically if there are some bad records

created. And same can be sent to the User/Client.

For the Better understanding about the SQL*Loader, I sugges

you go through the following link.

http://alloracletech.blogspot.com/2008/08/sql-loader-tutorial.html

68) In report we have to show two Layouts? How it is and how we can see from where it?

Use the Additional Layout tool in the tool box of layout editor.

69) What are ad-hoc reports?

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.

70) hat is the data type of lexical parameter?

If a column or parameter is used as a lexical reference in

a query, its data type must be character.

71) What is OM Cycle and What do u mean by Alternate Item

OM Cycle:

Enter --> Booked --> Schedule --> Pick Release --> Pick

Confrim --> Ship Confrim

72) do i need to declare bind parameter before using in the query?

no need to take the bind parameter why because the bind

parameter is used in the query.Before u can't declare.

73) HOW U DISPLAY THE FLEXFIELD DATA IN APPS?

74) what are the functions you are used in utl_file at the time of transferring the data?
We will use following three functions to generate the file.

1)Utl_File.fopen = To open (or) Create the file

2)Utl_File.Put_line = To Transfer the data into the File.

3)Utl_File.fclose = To close the File after Data

transfer.

75) While registering the concurrent program we first do the executable concurrent program and then define it . So why we need to give the application name twice in executable and as well as definitions

We give the Application names twice because the application

in given in the Executable shows where the execution file

is placed & the Application given in the concurrent program

gives the information from which application the program is

going to run.

76) what are the steps to develop a new report?

1)Build the report as for the client requirement

2)Save the report in .rdf format

3)Move the report from local machine to server mahcine

4)Login into Oracle Apps instance

a)Create Executabale

1)Give your own Executable name

2)Give your own Executable short name

3)Give the Application name

4)Give the file type as "Oracle Report"

5)Give the file name(name of the report which u

saved with out the extenction)

b)Create Concurrent Program

1)Give your own Concurrent Program name

2)Give your own Concurrent Program short name

3)Give the Application name(which was given in

the Executable

4)Below to the frame we find Executable frame

there is the lable name in the name give the

"Executable short name"(its very important give

executable short name not executable name)

5)Setup if there is any parameters by pressing the

parameter button

6)Assign those Concurrent Program to the Request Group

7)Submit the Request.

77) In GL module specifing org_id or not?

No.Gl Module is derived by SOB Id and not based on org_id

78) Can We Create a Synonym On Trigger?

Yes We can create a synony for a trigger like this

Create or replace trigger test before insert on emp

begin

dbms_output.put_line('Hi');

end;

Create the trigger in scott schema

and connect to apps schema and create a synonym like this

create public synonym test for scott.test;

create synonym test1 for scott.test;

79) What is Formula Column?

A formula column performs a user-defined computation on

another column(s) data, including placeholder columns.

Is it possible calling from one report to another? if possible means tell me ?

Using srw.run_report('reportname'..........);

we can call a report from another report.In after report trigger, write an api

FND_REQEST.SUBMIT_REQUEST() to submit another report's request.

80) Can we run a package with out specification?If yes how?

No, we cannot create a package with out its specification.

So we cannot runn a package with out specification.

81) What are Mandatory triggers to write in the block, which is created on view?

If any block is created from a view all the transactional

triggers are mandatory.

On-Insert

On-Delete

On-Update

82) Can u define operating units inplace of inventory?

It's all depends on the Business Structure you have in your

Organization.

General flow of the Business Stucture as given below

SOB

Legal entity

Operating Unit

Invenotory Operations

Sub Inventory

If you want to have the security between your Inventory

Organization, then make then as OU Organization.

For the better understanding, I sugges you to go through the

link given below.

83) What is SET-OF-BOOKS?

Set of books is nothing but collection of chart of

accounts,currency and calender in addition of mandatory

accounts.

84) what is the difference between varchar and varchar(2)?

When you use varchar data type say varchar(20),

it will reserve the space of 20 bytes even length of

passing parameter is less than 20 byte. But in varchar2, it

will release the remaining space after reserving actual

passing parameter length

VARCHAR--FIXED LENGTH OF DATA TYPE,MEMORY ALLOCATION WILLNOT TAKE PLACE.

VARCHAR2--IN THIS ORACLE DATA TYPE,MEMORY ALLOCATION WILL HAPPEN

85) How to create a link between One dependent value set to another Dependent value set

As per best my knowledge it can be done by $flex$.

How relation will be made between modules in oracle financial?

set_of_books_id from gl_set_of_books table

and org_id from hr_all_organizations table

organization_id from hr_organization_units table

86) How can u find custom top? where its located? How to find custom top in front end?

In Unix: Use echo $CUS_TOP will show the Path.

Navigation: System administrator responsibility->Application-> register

Except the SRS window in which we can submit the concurrent program.

Yes it is possible by submitting the request through

backend.Example of submitting request through backend-

declare

x_id number(10);

begin

fnd_global.apps_initialize(6729,50156,201);

--- Userid,Responsibilty_id,Application_id

x_id:=fnd_request.SUBMIT_REQUEST

(application=>'PO', --Application_Name

program=>'ELPOITEM', --Short Name

description=>'Purchase

Order History Impact - AMG', --Description

start_time=>to_char

(sysdate,'dd-mon-yy hh24:mi:ss'),

sub_request=>NULL,

ARGUMENT1=>'01-MAR-

2007', --Argument which we are passing while running

ARGUMENT2=>'31-MAR-2007');

the report through fronthand---

dbms_output.put_line(x_id);

commit;

END;

Assume i have three Procedures defined in a Package Spec. I have created the Package Specification. now i create the Package Body by including the three procedures in addition to that i include a fourth procedure which is not included in my Package Spec. I compile the Package Body. Will the Package body Compile ? What will the end result ? what will happen if the execute the package by calling the 4th procedure ?

Yes the Package body will be compiled.

The end result is you have a package with 3 Public

procedures and 1 Private Procedure.

You cannot call the 4th procedure as it is a Private

Procedure available within the Package only

87) we have clients in different countries, we can run report in different places, if i want to display particular language,country,currency everything displays for that country how?

WE have to go for profile values ,in before report trigger

we have to specify fnd_profile.value(org_id)

and fnd_profile.get('set of bks name) and org_name.

88) in procedure how to return a value

In a procedure, we can return a value using OUT PARAMETER

or EVEN USING IN OUT PARAMETER.

For Eg:

CREATE OR REPLACE PROCEDURE add_num(a IN NUMBER, b IN OUT

NUMBER) IS

BEGIN

b:= a + b;

END add_num;

The IN OUT / OUT parameters can be modified within PLSQL

block and the updated values of this parameter can be

refered using a actual paramter once the execution of this

procedure is completed.

89) How to print invoice from APPS(front end).if we have invoice number in hand with us???

If this is first time that you are trying to print the

invoice report for this particlaur invoice number,you can

use 'Print New Invoices' program and pass the Invoice

number to this program to get the output.

If you already had done printing the invoice

before,use 'Print Selected Invoices' program and pass the

invoice number as a paramter to this program.

90) what is the difference between po_vendors & po_headers_all? does po_vendors relate to opeating unit?

po_vendors table will have information about the

vendors/suppliers we define in PAYABLES/PURCHASING.

po_headers_all is a multi org table and hold Purchase

orders and quotations information.

Po_vendors will not relate to a particular operating unit.

91) what is the difference between %_all and %_f tables

_ALL-->These are multi-org tables(contains all

oraganizations data)

_F--->These are Frequency tables(date range tables)

92) In interface how u find errors and how to solve. How to fix it?

MTL_INTERFACE_ERRORS used to find INV Interface errors

RA_INTERFACE_ERRORS used to find AR Interface errors.. etc

will have for every interface

93) how to avoid duplicate data while uploading data from flatfile to staging table? some validations

What Nagaraju suggest is right. But instead of creating

temp and then moving those to staging table.

Directrly load the data into staging table and write the

query to eliminate the duplicate rows.

94) If the flat file is having the duplicate records in the interface how can you restrict while transferring the flat file data in to interface tables?

AFTER LOADING THE DATA INTO STAGE TABLE USING SQL LOADER,

WE WILL WRITE THE VALIDATION PROGRAM.

IN THIS VALIDATION PROGRAM WE WILL WRITE CODE TO RESTRICT

DUPLICATE RECORDS.

SO ONLY VALID RECORDS ONLY TRANSFERRED INTO INTERFACE TABLE.

95) While running the concurrent program we will get the status, we have to change the status. How it will be done.

make RETCODE=0 --->SUCCESS

RETCODE=1 --->WORNING

RETCODE=2 ---> ERROR

in the code

96) in ap_terms table what data will be stored

it store the data repalted to payment terms

e.g.

1. 2/10 Net 30 this means if the payment is made with in 2

days there will be 10% discount on the payment amount.

after that the payment can be made by the customer upto 30

days without any extra cost.

97) wht is the use of Xml reports over reports 6i?

Multiple Language purpose we r going to develope XML Reports

& Once we develope XML Report we can Display in

different format like word,excel.

he main use of XML reports over oracle reports is that the

data model and the layout model are separated. Changes in

layout model(look and feel) does not need to modify the

rdf . It would be sufficient to make changes to the RTF

template and upload back the template.

98) HOW CAN U KNOW THE STAUS OF PO?

we can see the status of PO in po_line_locations_all

where closed_code = (open,closed for receiving,closed for

invoice,closed)

99)What is workflow? Explain with an example?

Workflow is Predefined Business flow functionality.

Ex: For any approval requires or any business flow requires

we use workflow. (For PO, If u required an approval

hierarchy for PO amounts level.)

We define the workflow in the workflow builder.

100) What is FORWARD DECLARATION in Packages?

if you want define the procedure in existing local procedure

on that time you will define the procedures,along with the

parameters is called forward declaration

101) 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 forward_pack;

102) Could any one tell me the steps of conversions and what kind of validations and exceptions do we use during this conversion? Please do answer in detail. what program do we write in user exit in reports?

Hi,

In Oracle apps if you want to load data from legacy

system to Oracle application we go for conversion, if it is

one time process.

The following steps must be followed.

Load data from flat file to staging tables ( temp tables )

then write your own package or procedure to populate the

interface table.

Then finally you can run oracle seeded program to populate

the base table.

----

Interface tables

MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

Base tables

MTL_SYSTEM_ITEMS_B

MTL_CATEGORIES_B

MTL_ITEM_REVISIONS_B

Concurrent program name is -> import items

Error table

------------

MTL_INTERFACE_ERRORS

Validations

-------------

Organization Code

SELECT organization_code

INTO l_organization_code

FROM org_organization_definitions

WHERE organization_code =

cur_rec.organization_code;

Inventory Org Id

SELECT organization_id

INTO l_organization_id

FROM mtl_parameters

WHERE organization_code = l_organization_code;

To check whether item is already exits

SELECT COUNT (*)

INTO l_item_count

FROM mtl_system_items_b

WHERE segment1 = cur_rec.segment1

AND organization_id = l_organization_id;

Item Type

IF cur_rec.item_type IS NOT NULL

THEN

SELECT lookup_code

INTO l_item_type

FROM fnd_common_lookups

WHERE lookup_type = 'ITEM_TYPE' AND meaning =

cur_rec.item_type;

very mandatory

mtl_system_items_interface

(

process_flag,

transaction_type,

item_number,

description,

organization_id,

set_process_id)

VALUES

(1,

'CREATE', or update

'Rev_hist_test', ----item_number

'IOI Revision Part', ----description

204,

);

I think the explanation could help you.

103) import standard purchase orders Conversion Process

Conversion is one of the important RICE components.It is

one time process.

The main use of conversion transfer the data from legecy

systems to oracle base tables,To do this we need to follow

some steps.

Example take Purchasing(import standard purchase orders):

1.First take the legecy data(.csv or .dat or .txt etc) file

2.create the stagging table/temparary table based on the

interface table.

3. Create the package and run the package based on the

functional document and insert any errors into the errors

table(i.e.custom validation errors)

4.After running the package data transfered from stagging

table to respective interface tables,In purchasing module

interface tables arepo_headers_interface,po_lines_interface

and po_distributuions_interface.

5.Once data inserted into interface tables then run the

standard concurrent program which is already provided by

oracle apps.

6.Once we run the import standard purchase orders then data

is transferred into respective base tables,if any errors

occured in processing time those errors are stored into

po_interface_errors table.

Oder of firing triggers in form

The order of firing triggers will be as follows

Form -> Block -> Item level..

If the same trigger exist in all the level then only it

will start from the lowest level to highest level.

104) How You Will display the key flexifields in your report

BY CALLING THE USEREXIT FND FLEXSQL IN BEFORE REPORT TRIGGER

AND FND FLEXIDVAL FROM FORMULA COLUM

ALL CONATENATED SEGMENTS DATA WILL BE IN THE LEXICAL PARAMETER

P_FLEX_DATA DEFAULTLY

FLEXSQL RETRIVES THE DATA FROM THE SEGMENTS AND POPULATE THE

SEGMENTS DATA INTO THE P_FLEX_DATA

FLEXIDVAL POPULATE THE DATA INTO THE LAYOUT FROM THE P_FLEX_DATA

105) Please let me know the differance between Master Item and organization Item in Inventory.

master item will avaliable all inv organizations under the

operating unit organition item is avaliable only a

particular inv org

in master item their is no transactions

106) what is item status? name the related table.

mtl_system_item_b is the table and the column which stores

the data is inventory_item_status_code

107) in which table sub inventory related information for item is stored?

mtl_secondary_inventories

108) Can u define exceptions twice in same block?

109) What are the User PARAMETERS in the Reports?

Whenever you need to change the query value at runtime then

create a parameter which reads the value during run time.

In this scenario which you are creating the parameters are

known as user parameters.

eg: create a sql query as below:

select * from emp where deptno=:p_deptno

Then system creates a parameter named p_deptno. This bind

parameter is a user parameter.

Run the report, During run time system expect the deptno

value from the user.

110) where do u specify in org_id?

Org_id will define at OPerating uint level

Organization_id will define at Inventory level

111) why r u transer the data base tables to flat file and what is use

There were somany instances as depicted below:

1.If the customer was using some old version of oracle

Aps,If he wants to switch over to the new version.If the old

version structure is no compatable to the new version.Then

we have to transfer the data from old version tables to

flat files again from there we have to upload into the new

tables.

2.If the customer is using more than one application for his

total enterprise,since every module is interlinked we have

to transfer the data from one module to to other.In this

case we cannot transfer the data from one application to the

other directly.In this case we have to get the data into

flat files and tranfer into the next application..

when the client is using some other reporting tool. then he

need the data in the base tables so that he can load the

data in to the database on which he is generating reports.

for this purpose we transfer the data from base tables to

flat files.

the client will then load the data from the flat files to

his own data base and develop reports.

112) i have 3 parameters like p1,p2,p3.In p1 i have 2 parameters like a,b.if pass parameter 'a' p2 should be enable and p3 should be disable then if pass parameter 'b' the vice versa.

you can create two more dummy parameters(like

p2_dummy,p3_dummmy ) and set display property to no.

now set the default value for p2_dummy as "decode

(p1,'a','Y',null) in the same way for p3_dummmy.

now in the valueset for p2 use where $FLEX$.p2_dummy ='Y'

and similarly for p3.

113) How many typres of Purchase order and what are those.

there are 4 types of purchaseorders

1.standard

2.planned

3.blanket

4.contract

114) where the accounting related data is stored ? all type of accounting related data costing etc...

if you basically ask for accounting related information .

genreally these datas are stored in Gl tables ..search out

tables starting with Gl_% in all_tab_cols .you will get

these tables ....

tables like

gl_code_combinations

gl_je_headers

gl_je_lines etc

115) What is invoice?
an invoice is the commericial document raised by the compony and send to customers with the details of items

AR Invoice is a document raised by the company and sent to the customer with the details of items sold, qty sold, price, tax and other details. Based on this invoice, the customer will send the payment in case of credit sales.

AP invoice is a document raised by the customer and sent to the company with the details of the items sent, qty sent, price and other details. The company will enter this invoice details in the Payables module and then pay the

customer accoring to the credit terms. This invoice may come along with the consignment or may be sent to the company seperately.

116) In which directory your flatfile is given?if u connected different instance where it is find?
Specifically talking about lockbox process in AR, your flat

file can be placed any where but your control file is

placed in the bin.Even if your connected to different

instance you can find it from the server searching for the

path in the bin of your so called instance.

117) how many sections are there in report?
Header Section,

Main Section,

Trailer Section.

118) an we write sql statement in Format trigger?

Yes,We can write SQL statements in format triggers.

119) WHAT IS THE FORWARDREFERENCE IN PL/SQL?

PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements.

120) 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.

121) how many groups we can create for query?

We can create mutltiple groups (groups-subgroups) for a

query.

122) what could be the problem if invoice_id is same in ap_invoices_all and ap_invoices_interface. while the Ord_id , invoice_num, and other things is diffrent .

Invoice_id has to be same in interface and base tables as

you are first populating invoice_id in interface table

through a sequence.

It is not possible that the data in interface and base

table can mismatch for the same invoice_id.

123) In Per_all_people_f table what is the significance of '_f ' ?

_F these are date tracked tables, which occur in HR and

Payroll. For these there are two date columns

EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together

with the PK identifies a row uniquely. The date intervals

cannot overlap.

124) How will U link GL with OU in Multi-org environment.

you need to define the legal entity, gl set of books and

while setting up the operating unit, do the following:

in the organization classification area select GRE/ Legal

Entity, Enabled the check box and press others button.

Select Legal Entity Accounting from the LOV shown.

Then select the Set of Books and press ok and finally save

your work.

Similary you can link Asset Organization.

125) what is the use of placeholer column?
placeholder column is used to hold the value of certain

calculation or a variable that is being carried out in a

formula column. A place holder can be defined as anumber

character or date type depending upon the type of value

which will be stored into it.

126) In developer Reports::there are 3 parameters.can you pass the paramter1+paratmeter2 value to the third parameter??

YES. We can assign parameter1+parameter2 into parameter3 into the Before trigger

127) Is SOB is attached with Business grp ?

Set of books is attached at 3 phases,

GRE/LEGAL ENTITY level

OPERATING UNIT LEVEL LEVEL

INVENTORY LEVEL.

AND give profile gl set of books to all responsibilites.

128) What is INBOUND and OUT BOUND? (Different types of interfaces) Answer
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

Inbound

If the data is to be uploaded from any legacy system to

Oracle apps the interface is called inbound.

Outbound

If any data is supposed to be send from erp to any other

system is called outbound..

Inbound Interface: The Data that is being brought in to

oracle application from markets

Eg: Purchase Requisitions (From Market-->Oracle)

Out Bound Interface: The data that is being sent to markets

from oracle application.

Eg: Purchase Order(From Oracle-->Market)

Here Markets are nothing but legacy systems which are

running with different software

129) What is MO operating units. Why it need to to set this and where .

This the profile which holds the value of operating unit

orgid when ever user login into system his org id is

value is transfered to profile value base on this profile

we get data and put data from database

In multi-org env.

MO: Operating Unit = {the users Operating Unit name}

Used primarily in a multiorg environment.

This points the responsibility to the appropriate

Operating Unit.

Set the site level to the desired default operating

unit.

If there is more than 1 Operating Unit Defined,

this profile option must be set at the responsibility

level

for each responsibility.

130) In interface process we have 1000 records i want to commit every 100 records how? How i can commit every 100 records?

there is an option in sqlldr scott/tiger rows=100

rows menas it will commit the rows

rows=100 meands it commits the 100 rows after the insertion

131) 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 statmetns.

132) what are the tables of auto invoice.

RA_INTERFACE_LINES_ALL ,

RA_INTERFACE_DISTRIBUTIONS_ALL ARE THE TWO INTERFACE TABLES

MAINLY USED BT AUTOINVOICE.

FURTHER DATA READ FROM THESE TABLES WILL BE INSERTED INTO

RA_CUSTOMER_TRX_ALL , TA_CUSTOMER_TRX_LINES_ALL ,

RA_CUST_TRX_LINE_GL_DIST_ALL , AR_PAYMENT_SCHEDULES_ALL

AFTER VALIDATING.

133) In Oracle Apps Some tables ends with TL,VL,what is the significance of the TL and VL? AnswerTL STANDS FOR TRANSLATION LANGUAGE . IT CONTAINS lANGUAGE

AND DESCRIPTION . ITS A SYNONYM .

VL STANDS FOR VIEW.

IT ALSO SUPPORTS MULTI LANGUAGE. IT ALSO CONTAINS LANGUAGE

AND DESCRIPTION

134) what is meant by boilerplates?

Boilerplate consists of text (label of the column) and

graphics that appear in a report each time it is run.

135) How to link between inventory and OrderManagement.

we can link using inventory_item_id , ship_from_org_id from

oe_order_ines_all table to mtl_system_items_b table.

We can using move_order_line_id from wsh_new_deliveries

table mtl_material_transactions or

mtl_material_transactions_temp tables.

135) What are basic steps for AIM?
there are six basic steps of AIM

i. Definition Phase

ii. operation analysis

iii.Solution design

iv Build phase

v. transition Phase

vi. Production Phase

136) Sys Admin Module?

a) Define Custom Users

b) Define Login Users

c) Register oracle DB users

d) Define Concurrent Programs

e) Register Concurrent Executables

f) Setting Profile Option Values

g) Define Request Types.

137) What are the validations you don in .CTL file in inbound interface process?

1) we can eliminate the records by using the WEHN condition

in CTL File

2) we can use the SQL Built-in Functions in CTL File.

3) we can skip the particular column -- Filler

4) we can Skip the top number of records – Skip

138) Exception Handilings? Answer
# 1

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

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:

 You can use this procedure to issue user-defined error

messages from stored sub programs.

 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

139) 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 =?gvreddy?

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;

140) how to find the custom directory in front end.

From Application Developer responsibility, navigate to

Application --> Register. Query for the custom application

name. The value in the field Basepath, is the OS system

variable that stores the actual directory info.

141) How to create multi-layout reports ? If we have 5 layouts , then how to get into the 4th one ? Answer
# 3

first create the 5 layouts.Create each layout in a separate

Main frame.You can create 5 main frames one below the

other.No layout should cross x-axis and Y-axis.After that

create one user parameter. and u will write format trigger

for each Main Frame format trigger.

after that u will run the report.

--In format trigger of first Main frame you will mention

-- for first layout to display

if :p_parameter = 1 then

return true;

end if;

--In format trigger of Second Main frame you will mention

-- for Second layout to display

if :p_parameter = 2 then

return true;

end if;

perform above task for remaining 3 main frames.

when You run report.

If You enter p_parameter value as 1 then first layout will

be displayed as output.

If You enter p_parameter value as 2 then second layout will

be displayed as output.

142) tell me the scenarios of between pages in reports

these trigger fire each couple of pages. it is fire two

Consecutive pages except first page

143) wheather discard file is automatically created or explicitly created?can u mention it?

Automatically created but we have to give the path where the

Rejected records to be sent by giving the key word 'DISCARD'

FILE PATH after condition

144) how can we get odd numbered pages in output of a report

This page property can be put in reports in 2 ways:

1. u have a text field(from rdf's insert > page number)

here u can't make any changes for odd number pages.

2. Use a field which has a source page number, the property

of the field has page numbering. The change has to be done

here (start at 1; increment by 2) and (reset at report

level).

This will make odd numbers appear in the report

u need to write stuff in before report trigger

suppose u fetch page number value into field or variable

pagenum

now in trigger u h to write

if ((:pagenum/2=1) or :pagenum=1) then

return(true);

else

return(false);

end if;

145) Can u attach Request set with in the Request set?

we cant,attach request set with in the request set

146) If the valueset if of type –TABLE then how many tables can we attach in the valueset ?

We can attach more than one table, but i donot know maximum size

147) what is user exit and wat program do we write in user exit?

User exit is a program to pass control from Report Builder

to a program you have written, which performs some

function, and then returns controlto Report Builder.

We can write UE in before report trigger to populate

the value.

148) what is back order in OM Answer
# 2

There are few reasons that a scheduled order could be

backordered during pick release process.

1. In-sufficient onhand

2. Order might be put as 'Pick release hold'

3. If it's a lot controlled item being involved then some

of the factors will be checking at picking rule setup (like

Lot- expiration date, etc) and tured to 'Backordered'

incase the criteria does not meet.

back order mens the required quntity for the sales order is

not availble in the inventory thats why it si not shipped

and is back ordered.

149) In which table or database definition we can find the link of organization and operation units.

The view ORG_ORGANIZATION_DEFINITIONS contents link of

organization and operation units.

ORGANIZATION_ID column IS FOR organization_id AND

OPERATING_UNIT IS FOR org_id

150) what is count(*) from po_vendors(any table)? what does it do and what is count(1) from po_vendors(any table)? what is count(0) from po_vendors(any table) the out put is same what is the difference

count(*) give the column count including null vaues

count(1),count(0) both will give the same result they will

count with out nulls

ow to fine How many coloumns in the APPs TABLES ?

select count(*) from dba_tab_columns

where table_name ='give apps table name';

151) mention the interface tables for item open interface and open transaction interface and also their respective production tables.

Interface Tables:

MTL_SYSTEMS_ITEM_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE

MTL_ITEM_CATEGORIES

MTL_INTERFACE_ERRORS

Main Tables:

MTL_SYSTEM_ITEMS_B

MTL_ITEM_REVISIONS_B

MTL_ITEM_CATEGORIES

152) How to write the no data found in XML Publiser Report in apps?

if count(*)=o then

it will displays the no dtata found

153) what is the back ground program run while transferring data from OM to AR? Workflow background process

154) What is implicity cursor . When it fires . For which statement it will fire(Insert/update/delte). What is the attribute of implicity and explicity cursor oracle 9i. What is the Rec cursor.

EVERY SQL STMT IMPLICITY OPENS ONE CURSOR AND PARSE AND

EXECUTE QUERY FOR EVER STMT IT WILL FIRE

IMPLIST SQL%

155) WHAT IS THE VIEW IN ORACLE APPS?HOW IS IT CREATED?

view is one of the object, it is like as a table ,it will

created based on table,it has no data,using this we can

retrieve particular organization information

syntax

create view<view name> as select *from <table name> where

org_id=userenv('client_info')

156) how to get the on hand quantity of an item across all organizations? name the underlying tables.

mtl_onhand_quantities

157) IN WHICH MODULE THE LEGAL ENTITY COMES?

Legal Entity is defined in HR Module only.

Nav: HRMS-->WORK STRUCTURES-->ORGANIZATION-->DESCRIPTION

FIND WINDOW OPENS.

NOW GIVE TYPE AS '%Legal Entity'

CLICK ON FIND

U CAN SEE ALL THE LEGAL ENTITIES DEFINED.

158) What is ERP? Architecture of apps?

ERP is Enterprise Resource Planning. Companies developed

ERP Sofware packages to cater the needs of business.

Some of ERP Software packages are Oracle Applications, SAP,

Peoplesoft, Siebel, JDEdwards, Ramco Marshall.

In Oralce Apps, the architecture is Internet Computing

Architecture. Now Oracle is developeing Fusion Architecture.

Refer Oracle site to know more about Internet Computing

Architecture.

Simple Archietecture of Oralcle Apps.

contains 3 parts

1.database

2.Application

3.Desktop tier(people screen)

159) How we will receive the work from the client? what type of documents we will develop in the entire implementation project? plz tell me the total scinario?

The Clients Submits the reference document. Depending on

this the Function Consultant will develop the

MD050(Functional document). Then The Technical consultant

Refer the MD070 & start Coding & simultaneously develop the

MD070 document.

The document which are developed during implementation are

MD070

TE020

MD120

CM040

160) What r the validation done in ap inbound ?

They are lot of validation done.

Validations are something which depends on the Client Requirment as well. And they are some general validation like Vendor, Vendor site, Buyer, Employee. type of the Invoice etc.

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

162) what are the different types of parameters avialable in reports?

by using system parameter we can send the data to out put

divises.where as user parameter are used to pass parameter

values.

default parameters are system parameters user parameters are created by user

163) Difference between triggers and procedures?

Triggers:

--------

1)Defined with create trigger

2)The data dictionary contains source code in the user_triggers.

3)Implicitly invoked

4)Commit, save point and rollback are not allowed(TCL)

Procedures:

-----------

1)Defined with create procedure

2)Data dictionary contains source code in user_source

3)Explicitly invoked

4)Those are allowed

Note: compare both ones with numbers mentioned..

Triggers are implicitly invoked but

procedures are explicitly invoked.

2. in Triggers TCL commands are not allowed.

TCL commands are allowed in Procedures

164) What is set function in sql .E.g.What is difference between union and union all

set operators are used to combine the out put of more than

one query.

union: used to combine the output Query1 and query2

and display them uniquely

union all : it would combine the two query and displays then

as its duplicates.

165) is it possible to run the interface without using oracle apps?

Yes it is very much possible to run the interface without

using Oracle application's front end, you can do that from

back end. but for the same, one needs to be well aware of

minimum parameters of the interface program.

166) What is the difference between base table and multi-org table?

multi_org tables have the colums org_id and end's with

_all but not in base tables

167) You are working with interfaces if client doesnot give any flat then what will u do? Answer
# 6

In practical scenario, we usually get the interface data in

flat file during the QA testing phase of the project.

During the Initial Development the Developer will create a

sample data flat file based on the technical design

document data mapping layout for unit testing of the code.

This code will be fine tuned based on the actual flat file

received from the client for testing.

There will be less code changes if the client communicates

the exact columns/lengths and their positions in the flat

file to the functional/technical designer.

Lot of interface data/code errors are due to

miscommunication between the

client/Business representative

==> Functional Design Document

==> Technical Design Document

==> Final Interface Code

168) LOCKS?

-- Is to reduce concurrency

1) share lock

---it allows the other users for only reading not to

insert or update or delete.

2) exclusive lock

--- only one user can have the privileges of insert

or update and delete of particular object

--- others can only read.

3) update lock

----multiple user can read, update delete

Lock levels :

1) table level 2) table space 3) data base level.

169) wht is difference between is and as varibales,while declaring procedure which is better?

there is no difference in using is or as while creating a

procedure.from Oracle 7i this facility is given for programmers

170) how to know the number of invoices generated for one PO from front end (navigation)?

Purchasing Super User > Purchase Order Summary

Give the purchase order number and press Find (J) button.

Select the PO in the list and then go to Inquire > View

Invoices.

171) How to insert data into two tables using single control file? Answer
# 1

LOAD DATA

APPEND

DISCARDMAX 9999999

INTO TABLE UPLOAD_HDR

WHEN agreementno='~'

FIELDS TERMINATED BY ","

(

AGREEMENTNO CHAR(20),

FILENAME CHAR(30),

BATCHID constant 112)

INTO TABLE UPLOAD_TMP

WHEN agreementno <>'~'

FIELDS TERMINATED BY ","

TRAILING NULLCOLS

(

AGREEMENTNO POSITION(1) CHAR(20),

EMI_AMT INTEGER EXTERNAL(16),

BATCHID constant 112)

A Small changes in above control file

Add below insert stmt to above CTL File

Insert into Table Tab_name_1 -- This is for one table

Insert into Table Tab_name_2 -- This is for Second Table

First mention first table column and mention second table

columns

172) How to generate XML Report with out using rdf?

There are multiple ways of doing the same

1. Create a Data template

2. Create a procedure using dbms_xmlgen

173) what is the error you will get if field is referenced in wrong repeating frame?

Frequecy error..Means the error is field is above/below its

frequency.

174) why we can use synonym?

We use synonym generally to hide the base schema name in

which the object lies, as well as it is easy to use.

USING SYNONYM WE CAN EASILY ACCESS TO A TABLE OWNED

BY ANOTHER USER.IT SHORTENS THE LENGTHY OBJECT NAMES

175) what is pl/sql table and pl/sql records? what is the use of that?
Pl/Sql table comes under collections.

Pl/Sql table ,Pl/Sql datatypes comes under User Defined

Datatypes.

Pl/Sql table is used for storing and processing the values

in a Pl/Sql procedures.

Pl/Sql records are used to hold the row structed values

we can use pl/ql records with pl/sql tables to form a table

like structed to hold the values in a Pl/Sql program.

The usage of plsql table and plsql records is high

performance.It enhance the performance.

Rather than using the cursor variable we can make use of

plsql records which is less time consuming.

For ex:

CURSOR emp_cur IS

SELECT trunc(sysdate) AS hire_date

,202 AS business_group_id

,'Raz' AS last_name

,'M' AS SEX

,13 AS person_type_id

,'Test Employee Creation3' AS per_comments

from dual;

TYPE tab_emp IS TABLE OF emp_cur%rowtype INDEX BY

BINARY_INTEGER;

emp_rec tab_emp;

176) what is instead of trigger. what is the use of that?

Instead of trigger are used mainly in case of views.Suppose

we want to perform update in a table instead of inserting

data into that table.or to solve mutating table error we

used instead of trigger

Instead of trigger basically used to update the complex view.

177) for report i have two parameters those are from_date and to_date ,so to_date should be greater when compare to from_date ,if we are giving to_date is less then it must shows some error how we willmake

while defining Parameters you can set range option(in

concurrent prog parameters window).

Select low for from_Date and high for to_Date.Then it will

not allow for from_date > to_date.

178) why r u move in the cus_top only why not move another top
After development of .rdf of fmx we can move these files to

the server.because these are belongs to which custom_top and

the run time we can get where ur insert these files we can get.

179) Where we can write a PL/SQL code in report triggers?

Before Pramater form,

After Parameter Form,

Before Report,

Between Pages,

After Report,

Format Trigger

179) can u explain which situation automonus transaction is used

1 Commit in Trigger

2 Avoid Table Mutation Trigger

3 Call of function in select stmt with DML

4 Two program units ( caller and called) with two states of

commit nature

180) What is the Exception. Types of exception, Difference between those. And example

Exception is an error caused which leads to abnormal

termination of the program...

To handle these exceptions oracle provides 2 types of

exceptions.

1. oracle server exceptions

2. user-defined exceptions.

oracle server exceptions are the exceptions pre-defined by

the oracle server.

for example,

zero_divide with some error code. like -01400

array_out_of_bounds etc...

for all thse exceptions sqlcode is given(assigned by the

oracle sever)

there are pragma exceptions for which oracle has not given

the names.to assign the exception name with some oracle

defined code ...we go for pragma exceptions.

user-defined exceptions are those defined by the user.

for which the names as wel as code is given by the user.

there is some specified code range to assign to user-

defined exceptions.

181) What will happen If I am not giveing the Suspense accout while defineing the Set of Books in GL?

Nothing happens if u dont enable suspence account in

journal region,

when u create a journal with wrong amounts the system will

post but it show an error#6 in posting status as suspence

unbalance.

so when u enable the suspence account,even though u post

the system will correct the balance and ost with an error.

rent 5000

suspence 1000

to cash 6000.

depending on the nature of account suspence will be adjusting.

182) Tell me some interface tables on ordermangement.

oe_headers_iface_all

oe_lines_iface_all

oe_lotserials_iface_all

oe_credits_iface_all

oe_actions_iface_all

oe_price_adjs_iface_all

oe_reservtns_iface_all

183) what type of testing u r doing?

As a application developer.we have do unit testing.

183) what is repeting frame?

RepeatingFrame: display the contents no of times

normal Frame : display the contents only one time

184) 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.

185) how to move the one file from one instance to another instance?and ur scripts also?

Using FNDLOAD commands, which creates.ldt files and import

those .ldt files in new instance using same command.

using FNDLOAD

FNDLOAD apps/apps O Y DOWNLOAD

$FND_TOP/patch/115/import/afcpprog.lct file_name.ldt

PROGRAM APPLICATION_SHORT_NAME=”FND”

CONCURRENT_PROGRAM_NAME=”concurrent name”

186) There are set of records to be processed from staging table into interface table. If one record fails the entire set of data should be rejected.

we can use rollback operation in exception block

187) how to set org context?

by using api fnd_client_info.set_org_context('org_name') In 11i

mo_global.set_policy_context('S or M','ORGANIZATION_ID'),to

pass multible resposibility at a time in R12

188)  how to initialize applications?

fnd_global.apps_initialize api is used in oracle apps

we need to pass user_id ,resp_id,resp_appl_id,we can get this values from front end by $profile$ of application

189)  in reports token is casesensitive or not?

Yes, its a case esnsitive.

You have to place the same value which you specified in the

Report Builder

190) what is the difference between credit memo and debit memo?

Debit and credit Memo

Debit Memo:-. A transaction that reduces Amounts Receivable

from a customer is a credit memo. For eg. The customer

could return damaged goods. A debit memo is a transaction

that reduces Amounts Payable to a vendor because, you send

damaged goods back to your vendor.

Credit memo:-A request is a sales document used in

complaints processing to request a credit memo for a

customer. If the price calculated for the customer is too

high, for example, because the wrong scale prices were used

or a discount was forgotten, you can create a credit memo

request. The credit memo request is blocked for further

processing so that it can be checked. If the request is

approved, you can remove the block. The system uses the

credit memo request to create a credit memo

191) Hoe to skip middle of the record by using SQL*Loader?

By using FILLER Command, it will not accept the data and it inserts the NULL values.

Ex:

(Empno,

Ename,

Date FILLER);

192) In 11i we didn't have AP_Invoice_Lines Table, but in R12 this table is available, what is reason, advantage?

Using AP_INVOICE_LINES table in R12, we can manage distributions against each line as against the distributions for the entire invoice in 11i. This gives more control on how much amount is paid against each line of the invoice. Payments made against one of the multiple lines of an invoice can be discarded which will automatically reverse the distribution lines of the line discarded. In 11i, it may have been required to cancel the whole AP invoice and create a new one.

193) what is global tempory table?

Global temporary tables are temporary table for perticular session.

Global temporary tables are two types,

1.Transaction wise temporary table

2.Session wise temporary tables.

In transaction wise temporary tables we will use on commit delete rows keyword, which delete the data whenever we do commit.

In session wise temporary table we will use on commit preserve rows keyword, which preserve the data till the end of that session.

194) How to migrate .rdf file from dev instance to test instance?

Normal procedure is download that .rdf from dev

instance server (appldev/ap/12.0/Reports/US)

and FTP it to testing instance (appltest/ap/12.0/Reports/US).

this procedure will be done normally after applying

ldts in testing instance.

195) is it is necessary to create P_CONC_REQUEST_ID parameter in the development of oracle apps reports

Yes, the concurrent manager assigns the request id to the report parameter P_CONC_REQUEST_ID .

Using this request id the SRW.USER_EXIT('FND SRWINIT') will try to access the context details from the fnd_concurrent_request table and using which these context value the report can gain access to views, profile options , values etc.....

If SRWINIT is used without the P_CONC_REQUEST_ID then the user exit will fail

196) what are the difference between oracle discoverer and report6i

DISCOVERER:

1.More features available.

2.Pivot Analysis and Drill Down Options given to User.

3.Graph is Dynamic and can be designed by End User itself.

4.Data can be exported to MS Excel and other formats.

5.Group Sorting can be changed any time.

6.Layouts are Dynamic and

7.Parameters Values can be changed at any time.

8.so it takes very less effort to do changes.

REPORTS 6i;

1.Less features available.

2.Pivot,drill down option not available.

3.Graph is changed by developer only not by end user.

4.Exported to PDF only.

5.Sorting can be changed beofore invoking

6.Layouts are static.

7.Parameters Values can be changed before invoking report only.

8.Takes more efforts to do changes.

197) what is the standard program for blanket po to process data from interface table to bace table

Import Standard Purchase Orders

198) What are the files we submit while your program move to testing instance?

Concurent program name and error file if any erros

199) How to make Parameter as Mandatory?

In SRS window-->parameters button

select which parameter u want to make mandatory and enable

the required check box

200) what are the pl/sql tables

PL/SQL tables are PL/SQL’s way of providing arrays. Arrays

are like temporary tables in memory and thus are processed

very quickly.

201) Is PlaceHolderColumn Function or Procedure? Why we don't write any code in that?

Place holder column is a local function, used to set the value dynamically depending on parameter.

or

used to substitute the values during the generation of reports

By default it

returns the value assigned to it. For eg

:CP_TEST_PH := 'INDIA' will return the string 'INDIA'. You

can write code in the place holder formula by just writing

what you want to return (return 'INDIA'). In that case you

dont have to assign anything to :CP_TEST_PH  anywhere. Just

make sure while creating your Place Holder column, it

returns correct datatype of adequate size.

202) about TCA architecture

TCA Stands for Trading Community Architecture and it is a

data model that supports the entry and management of

entities that you interact with. Key features of TCA

1. Provides a foundation for a single source for customer

information.

2. Ability to represent all business entities as a “Party”

(organizations, people, groups, relationships) and to

handle them the same way.

3. Many-to-many relationships between Parties and

Locations, that allows for less duplication and easier

updating.

4. Capability for advanced relationship modeling between

entities within the trading community. Any party can figure

in any number of Party Relationships even within matrix

hierarchies (relationship networks).

5. Ability to setup and maintain any number of party

classifications which can be used for reporting and

assignment purposes.

6. Extensible data model to enable various business data

requirements.

7. In reality , three entities Drive in the TCA model ,

which are Party, Account, and Relationships.

203) What is the autonomous transaction?give me example?

Autonomous transaction can be devided into two parts as

parent&child,both(parent&child) are run simultaniously and

individually run.

EX:

create table R1(B1 number);

Table created.

create table R2(B2 number);

Table created.

1  create or replace procedure p1

2  as

3  pragma autonomous_transaction;

4  begin

5  insert into R1 values(101);

6  commit;

7* end;

Procedure created.

 select * from R1;

B1

---

> create or replace procedure p2

2  as

3  begin

4  insert into R2 values(102);

5  p1;

6  rollback;

7  end;

8  /

Procedure created.

SQL> select * from R2;

no rows selected.

204) What is the difference between buyer&employee in PO module?

In PO only buyer can create purchase order whereas employee

cannot create purchase order.

Employee should be defined as buyer to create Purchase

orders in PO.

205) why do we validate the records while developing interface?

if we write validations progrmme if it is valid data then

only insert into base table otherwise insert into error table

206) In xml report, how to print the headings in every page?

Define it as header section.

it will be printed in all pages.

207) I want to print output in portrait style?where i have to change the settings?

Go to printer properties finishing tab and Choose portrait

option button in orientation frame.

Navigation: Print->properties->finishing->portrait

Click ok to print in portrait style.

208) i have 100 pages output. i want to print 10 records per page in Template?

set no of records 10 in  property of repeating frame so

that it will display 10 records on eahc page.

You can also use for-each and page-break XML statement in

the template to acheive this.

209) The report output is 10 pages then how many times between pages report trigger will fire?

9 times between pages report trigger will fire.

Between pages report trigger will fires n-1 times.

210) How will you get the concurrent program output as email?

Using Shell script we can attach the output of a concurrent

program.

mutt -s <subject> -a <attachment> <mail_id> < <mail body>

Eg:mutt -s "Output-conc prgm" -a XXCUST_TOP/bin/xx_pr.txt

abcd@gmail.com < "Hello, PFA output of conc.program."

211) how to change the logo in template dynamically?

This article discusses about how to display images dynamically using XML Publisher.

COMPANY NAME
ADDRESS
REPORT TITLE
CF_PRINTED
1.	2.	3.
Customer No..	Customer Name	Address	Tax Category
Number	Name	Address	Tax Category

Note :- We have to place Dummy Images on template like in above template.

1) In this option we placed image path Directly like as follows

By right clicking on Dummy image select SIZE option (office 2007) and click on Alt text Button the following screen will display.

2) In this option we get the image file name in CF.

a) By right click on Dummy image select SIZE option (office 2007) and click on Alt text Button the following screen will display

3) In this option we get the Image File path From CF.

b) By right click on Dummy image select SIZE option (office 2007) and click on Alt text Button the following screen will display

OUTPUT:-

212) explain p2p cycle? what are the tables used for this?

REQUISITION-->RFQ-->QUATATION-->PO-->RECEIPTS-->INVOICE-->PAYMENTS-->GL.

PO_REQUISITIONS_HEADERS_ALL,PO_REQUISITIONS_LINES_ALL.PO_DISTRIBUTIONS_ALL,PO_HEADERS_ALL,PO_LINES_ALL,PO_DISTRIBUTIONS_ALL,PO_LINE_LOCATIONS_ALL,RCV_SHIPMENT_HEADERS,RCV_SHIPMENT_LINES,RCV_TRANSACTIONS,

213) can we execute one concurrent program through another concurrent program?

Yes. We can execute one concurrent program through another

concurrent program.

using FND_REQUEST.SUBMIT_REQUEST(<appln shortname>,

<conc.prgm shortname to be called> [,arguments]);

What are Interface table in AP, AR,GL ? 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

How to take care of null values in SQL Loader ?

By using the following statement in Control file.

TRAILING NULLCOLS

What are the setup steps for testing ?

If its for PVCS testing instance then, step -1 : developers

develop or customize the form and is stored in the PVCS patches with the lateast version .step -2: this patches or developing packets are then compiled and tested at the end user place step 3: the developed PVCS packets are tested at the testing instance .if any compilation errors exist then they are again send to the developing insatnce or it is subimmitted to DBA to for production instance.

What is an Invoice ? How many types of invoices are there in AP and AR ?

A invoice is a Document created whenever a product or service are sold. And if the sale is made on credit the invoice remains open in the Receivables Module utill the Funds for it are received . And once the Funds are received the invoice is closed with the receipt .

There are 9 types of Invoices in AP : Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and QuickMatch.

There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What id recurring invoices ?

Recurring invoices are those invoices which re occur after a specific time interval eg. Monthly Rent

What are AP setup steps ?

setup---->suppliers....>invoices..>payments.....>reports......>periods.....>transfer to GL

If the valueset is of type –TABLE then how many tables can we attach in the valueset ?

WE CAN use multiple tables in table value set in table field we can use the tables and must give the alias names for the tables and in where clause write the join conditions.

Can you add a descriptive FlexField to a table which already have data in it ?

yes,,u can add descriptive flex fields to the table that is already existing..but it should be registered using ad_dd package.and after table registeration,,ur columns must also be registered.and once thisdatabase

registeration is over,,u need to login as appdevelop responsibility and then dff steps is to be done,,,like giving the name of the attribute category to hold the dff columns..

If any conflict occurs in FSG the who will override Column Set or Row Set ?

FSG (Financial Statement Generator) is a tool for reporting on account balance

by period.
Accounting flexfield, accounting periods and type of balance these are the details which make the rowset and column set. Column set overrides rowset.

What is ICA (Internet Computing Architecture)?

In ICA we have desktop Client,Application Server

and Database Server.

What is Set of Books ? What are the four conditions when you change your SOBs?

In R12 Set Of Books(SOB) means combination of
1)Chart of Accounts
2)currency
3)calender
4) Sub Ledger Account(SLA).
The above 4 conditions used to change the SOBs

What is Multiple Organization technical architecture?

Multi Organization :Using a single installation of any oracle

application product , to support any number of an organization even it has different Set Of Books
Bussiness Level(It Secures Human TransactionInformation)
||
Set Of Books(It secures all Transaction Information In Oracle Genral Ledger)
||
Leagel Entry(All leagal Information in the Organization)
||
OPerating Unit(It Uses Order management, Cash managment,AR,AP...it may be Sales Office , Division,Department)
||
Inventry Organization(Inventry Details)

before multi org existed, we could have only one bussiness group,set of books, legal entity, operating unit in one installation of oracle E-bussiness suite, and now with the multi organisation structure in place, we can have multiple bussiness groups, set of books, legal entity and operating unit and the best part is once these are set up intercompany accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.

and all transactions are taken care of, courtesy multi organisation structure.

hope that is clear.

What can we find TEMPLATE.FMB file ?

Template.fmb file can be found in AU_TOP resource directory. This file contains all the Common characterstics all the forms. And also Contains Diffrent libraries. like CUSTOM.pll,APPCORE,APPCOREE2,FNDSQF, JE,JL,JA,VERT,GLOBE etc.. And Template.fmb cotains Diffrent propery classes for all the objects. This Template.fmb can be used for developing the new form.

APPCORE: APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc. APPCORE contains the procedures to support this standard runtime behavior. APPCORE also contains various other utilities for exceptional handling, message levels, and so on.

GLOBE: The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms
without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can
take effect based on these events. The GLOBE library calls routines in
the JA, JE, and JL libraries.

JE: The JE library contains code specific to the EMEA (Europe/ MiddleEast/ Africa) region and is called by the GLOBE library.
FNDSQF: FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing. It also has various other utilities for navigation, multicurrency, WHO, etc.

JL: The JL library contains code specific to the Latin America region and is called by the GLOBE library.

JA: The JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.

VERT: The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library. Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.

GHR: The GHR library contains code specific to Event Dispatcher for HRMS Localization

APPCORE2: There is no basic difference between appcore.pll and appcore2.pll. Appcore2.pll is a replica of appcore.pll. While using custom.pll we can't reference appcore.pll as it cause cross reference problem so to use appcore.pll in custom.pll we make use of appcore2.pll

HRKPI: This HRKPI library contains packages for humary resource knowledge provider. If the HR_STAND is attached with the form, then this may be must.
PQH_GEN.pll The PQH_GEN library contains code specific to Public Sector Human Resources
PSAC.pll The PSAC library contains code specific to Public Sector Applications
PSB.pll The PSB library contains code specific to Public Sector Budgeting
PSA.pll The PSA library contains code specific to Public Sector Applications
GMS.pll The GMS library contains code specific to Encumbrances
FV.pll The FV library contains code specific to Federal Financials
IGILUTIL.pll The IGILUTIL library contains code specific to International Public Sector
IGILUTIL2.pll The IGILUTIL2 library contains code specific to International Public Sector
OPM.pll The OPM library contains code specific to Process Manufacturing

What is the difference between cross-validation rules and security-rules?

Security rules are segment specific and enforce predefined restrictions of a particular segment. After defining a security rule, for the security rule to take affect it should be assigned to a responsibility.

Cross validation rules prohibit invalid account combinations being created, thus they are applicable to a combination of segment values. Cross validation rule once created, would immediately take affect with all the responsibilities that are using the accounting structure(chart of accounts) for which the rule is defined.

In how many ways can you enter a journal in GL?

We can enter journal in GL in Three Ways.
1) Entering Journal by Journal Entry Screen on Application Side.
2) Importing Journals to GL by Interface.
3) Using Application Desktop Integrator ( ADI).

What are the setup steps for AP, AR, GL?

Below are some of the Main Step in GL, AP, AR Module

GL: Account Flex field

Account Combinations

Period Types

Define Calendar

Define Currency

Define Set of Books

Assign Set of Books to Responsibility

Daily Conversion Rate Types

Rates

JE Sources

Categories

System Controls

Profile Options

Open and Close Accounting Period

AP: Define Payables Lookups.

Define Purchasing Lookups.

Define payment programs.

Define your payment terms.

Define bank accounts.

Define Suppliers.

Define invoice approval codes.

Open your Payables accounting period.

AR: Define Payment Terms

Open and Close Period

Auto Accounting

Transaction Types

Transaction Source

Collectors

Approval Limit

Remittance Bank

Payment Method

Receipt Source

Aging Bucket

Customer Profile Classes

Customers

Customer Sites

Uses of Customer Sites (Ship to /Bill to)

Remit-to Address

How to register tables and columns in AOL?

To register the table and columns in AOL the navigation is:
Open Appliaction Developer---> Appliaction--->Database--->table.(In table mention the table name(which you want to register), user table name,columns,user column name).

The table & columns which you are going to register should be present in your module specific schema.

No Sandeep we cant register a table or its column like that. the window you hav mentioned doesnt allow us to enter nething. we can just see all the registered tables and their columns here.

one way which i know to register a table and their columns is thru backend .

eg.SQL> EXEC AD_DD.REGISTER_TABLE (SQLGL, EXAM,T)

where SQLGL is aplication short name and EXAM is the table name and T stands for Transaction Data

SQL> EXEC AD_DD.REGISTER_COLUMN (SQLGL, EXAM,SNO, 1, NUMBER, 5, N, Y);

EXAM Table Name

SNO Column Name

1- Sequence one or column one

NUMBER- Number Data type

5-Size

N Null able

Y Translate (Y/N)

What are cycles of GL, AP, and AR?

What are Summary Accounts and Roll up groups ?

Summary Account: An account whose balance represents the sum of other account balances. We can use summary accounts for faster reporting and inquiry as well as in formulas and allocations.

Rollup Groups: A collection of parent segment values for a given segment. We use rollup groups to define summary accounts based on parents in the group. We can use letters as well as numbers to name the rollup groups.

What is the use of CUSTOM Library in Oracle Apps?

hi,CUSTOM.PLL is an library which exists in TEMPLATE.fmb ,it is an oracle form devloper PL/SQL library , which alows the developers to attach thier own custom code to the oracle applications without modifiying the orginal application code....it consists of two 2 function 1 procedure2 functions are 1 ZOOM_AVAILABLEthis is a function which returns boolean value ...this function always test on form_name and block_name , if the test is sucessful then this function returns TRUE else FALSEby default it returns FALSE2.STYLE: this is also function which determines the execution style of the formit contains three package variablescustom.strandardcustom.beforecustom.aftercustom.overide( i am not sure wehter it is over ride or in place..or some other name to it)by default it returns custom.strandard, which does not have any effect or modification on the form3. EVENT(PROCEDURE)This is a proceure

Can you call APPCORE library in CUSTOM library ?

you cannot attach the APPCORE library to CUSTOM because it would cause a recursion problem (because CUSTOM is attached to APPCORE). As of Oracle

Applications Release 11i, you may attach the APPCORE2 library to CUSTOM. The APPCORE2 library duplicates most APPCORE routines with the following packages: APP_ITEM_PROPERTY2
APP_DATE2
APP_SPECIAL2
These packages contain the same routines as the corresponding APPCORE packages. Follow the documentation for the corresponding APPCORE routines, but add a 2 to the package names.

Can you use REF cursors in CUSTOM library?

How many Flexfields are there in AR and what are they ?

There are 2 flexfields in AR

1.Sales Location

2.Teritory

Of which sales location is mandatory.A sales tax is calculated by considering the ship-to information.

Teritory flexfield is optional.

What are User Exits and what are different types of User Exits in Oracle Apps ?

User_Exit is used to pass control from one oracle products to another. In Oracle Apps we use user_exit in before_report trigger to establish link between the report and apps environment that is we use 'FND SRWINIT' for the apps initialization for the report.

User exit are programs thats enables you to execute external procedure.

5 types of user exit are there in APPS.

FND SRWINIT--Used to set the profile option and environment vallue.

FND SRWEXIT--Used to release the profile option and environmental value

FND FORMAT_CURRENCY--Used to format the currency dynamically.

FND FLEXIDVAL--Used to populate the fields for display.

FND FLEXSQL--this user exit is called to create a SQL fragment usable by your report to tailer your SELECT statement that retrives FLEX FIELD value.

What is MRC and what are its use?

The Multi Reporting Currency Feature allows you to report and maintain records at the transaction level in more than one functional currency. You can do by defining one or more set of books in addition to primary set of books

MRC ( Multi Reporting Currencies ):- A Unique set of features embedded in oracle

applications that allows u to maintain and report accounting records in your functional currency.

To use Multiple Reporting currencies you must create a primary set of books and seperate reporting sets of books for each functional Currency.

What are ad-hoc reports ?

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

Ad-hoc report means temporary report.these reports are useful for a certain period and they are not useful for a long time purpose.

What are value sets

what do you mean by HZ_ in customer tables

All tables that starts with HZ_ are related to the Trading Community Architecture (TCA). In these table we can find all the information about the Customers, Organization, Person's.
From release 11i TCA came into picture in Accounts Receivable module, where oracle has grouped all the customer information at one place. Most important tables in TCA are HZ_PARTIES, HZ_CUST_ACCOUNTS_ALL, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_PARTY_SITES, HZ_PARTY_SITE_USES, HZ_CONTACT_POINTS. few to name.

How can you handle multiple rows without using loops in PL/SQL ?

Using cursors

What are the different types of files used in SQL Loader ?

Different types of files are Data File,Control File,Discard File,Bad file

What is integration between modules?What modules in Order management can be integrated with Finance modules ?How this integration is done?

orader mangemnet is integrated with finance AR (accounts receivables)

for creating the invoice for the products delivered.

order mangement is also integrated with Inventory for inventory transations of the item.

and Purchasing in case of drop shippment.

How can I incorporate a lexical parameter in my report for having the FROM DATE and TO DATE parameter?. Can you Pls let me know of the steps involved in doing the same?

Can anybody suggest me how can the multi org. be set up?

Multi-Org can be setup/installed by adadmin utility provided by the Oracle

. adadmin will create the _ALL table and appropriate views (single org views) which can then be used to view the data for specific operating unit to which your user/responsibility has access to!

What does US mean in appl_top/au/11.5.0/reports/US?

US is the language directory specifying that the source files to be placed under this dir is for English/American Language

This is the standard of apps directory structure that for very language you implement oracle

apps there should be a language specific folder

So if you are implementing oracle apps lets say for UK as well as France then your source directories for oracle reports, Forms and htmls will have subfolder US and F for English and French source files respectively .. something like --

What are the types of Concurrent Managers?
Can we delete a Concurrent Manager?

There are many concurrent managers, each monitoring the flow within each apps area.

but there are 3 MASTER CONCURRENT MANAGERS:

1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs

2. Standard Manager (SM) : This takes care of report running and batch jobs

3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.

We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is a request Sets? How to create a request Set?

What are the different types of Value sets and expalin them briefly which value set will use when?

he valueset are like how we define constraints in Oracle Database

. In oracle Apps there are six value sets

1. Independent - The Value which we provide to the Segments are independent (Ex. Company Segment - 01, 02, 03) (Ex. Primary key)

2. Dependent - The Value which we provide to the dependent segments are depends upon some other value set. (Foreign Key). Ex. Major Segment is Independent, Minor Segment is dependent segment which depends upon Major Segment. (Ex. Computer - Major Segment)(Mouse, Keyboard - Minor segment (Depends upon Computer). To provide value to dependent segments always depends upon some other values.

3. Table - The value for these segments will be retrived from some tables in the database.

4. Special - These value set are used for reports paramaters. The Special is always used for Report Parameters, where the parameters are the values from Key Flexfield.

5. Pair - Same has special but have low and high values.

How to submit concurrent program through pl/sql?

The below procedure will submit the request from the back end where

application is Short name of application under which the program is registered.

program is concurrent program name for which the request has to be submitted

declare
conc_id number(10);

begin
FND_GLOBAL.APPS_INITIALIZE (user_id,resp_id, resp_appl_id);
conc_id:=submit_request('application','program');
dbms_output.put_line(conc_id);
end;

Complete Auto invoice interface information?

AutoInvoice is the interface where Customer Invoices are generated. Source of Invoices is Order Management, PA.

Interface tables: ra_interface_lines_all. Table stores Invoice Line level details.

Ra_interface_distributions_all. Accounting distribution of each invoice.

Once records are inserted into ra_interface_lines_all, run the Autoinvoice interface from AR. If any errors occur, it can be found in ra_interface_errors_all table. The concurrent has 2 stages. First the Master program fires which intern kicks of Import Program. Once this is completed, data is inserted into the following tables.

1) ra_customer_trx_all (Invoice Header Info)

2) ra_customer_trx_lines_all (Invoice Line Level Info)

3) ra_cust_trx_line_gl_dist_all (Accounting Info. One record for each Account Type is inserted into this.. ex. Receivable, Revenue, Tax, Freight etc)

4) ar_payment_schedules_all (All Payment related info)

this is the standard program through which the data from interface table RA_interface_lines_all will be pulled into oracle

receivables on making the validations on various feilds.batch source will play major role in this transferring of records. the pulled records will be reflected in
1. ra_customer_trx_all(invoice header)
2. ra_customer_trx_lines_all(invoice line)
3. ra_cust_trx_line_gl_dist_all (Accounting Info)
4. ar_payment_schedules_all (payment)

Xml Publisher Basics

1) How wil u set sl.no in xml reports?
  we wil use postion attribute here
syntax: <?position()?>
2) How wil u give pagebreaks after specific records(rows)?
we wil use <?split-by-page-break?>
syntax: <?if:position() mod 5=0 ?><?split-by-page-break:?><?end if?>
    ------ we should not use split-by -page-break in table.
3) How 2 Avoid the page break inbetween the table
           Navg: In the table properties we wil select row tab
in that we we wil select check box Allow row 2 break across the page
4) How wil give Headers to all pages?
by using start body and end body
syntax: <?start-body?>
  <?end body?>
5) Xml publisher can have Only one top level elements
6) How 2 give count of elemnts in last page of the report?
syntax: <?start@last-page:body?>
               count attrbute1
               <?end body?>
7) How to appy colours alternatively?
syntax: <?if@row:position() mod 2=0?> <xsl:attribute name="background-color" xdofo:ctx="incontext">silver</xsl:attribute><?end if?>
8)   How wil u set Heading per each page?
           Navg: In the table properties we wil select row tab ->

in that we we wil select check box repeat header for each page

9) Navigation for landscape
           Navg: pagelayout ->page setup ->landscape(to the text----only 4 tat page)
10) How 2 apply colour in Background
           Navg: borders and shading -> shading -> fill
11) How 2 remove Boarders
           Navg: borders and shading -> borders -> (remove all sides except down)
12) Check box (check box) <?ename>kasi?>
14) Drop dowlist select drop dronlist give list of elements then go for add help text
15. If we want to hilight one cell then we wil write syntax like
  <?if:debit>1000?><xsl:
  attribute xdofo:ctx=
  "block" name="backgroundcolor">
  red</xsl:
  attribute><?end if?>
16).we wil do running totals like this
<?xdoxslt:set_variable($_
XDOCTX, ’RTotalVar’, xdoxslt:
get_variable($_XDOCTX,
’RTotalVar’) + INVAMT)?>
17) if i want to calclulate totals -------- <?add-page-total:variablename;'column_name'?>
       to display those columns               <?show-page-total:ct;’$#,##0.00’;’ ($#,##0.00)’?>
18.we can do sorting like this
syn:-<?sort:element name?>
ex:--<?sort:vendor_name?><?sort:vendor_num?>
18. set autotrace traceonly -to trace the query without data
19.select to_char(to_date('10/25/2008','MM/DD/YYYY'),'dd-mon-yyyy') from dual
20. we can execute pl/sql procedures with EXEC or CALL command;

21) to remove extra 0's in xml
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CP_COST?></fo:bidi-override>
22) sum of sal
<?sum(SAL[text()])?>

Personalization vs Extension vs Customization

In Oracle EBS development, the terms Personalization, Customizations & Extensions are often used interchangeably. It often creates confusion among developers regarding the meaning of these terms. These terms are critically important terms that developers must understand and use properly. Let’s discuss them briefly here to simply understand what they are.

What is Personalization?

Personalization is the process of making changes to the User Interface (UI) from within an Oracle E-Business Suite Form/Page. It is possible to make personalization to both Form-based and OA Framework based pages.

What is Extension?

Extension is the process of making changes to the programmatic (i.e., PL/SQL or Java) elements of an E-Business Suite form/page. It is possible to extend both Forms based and OA Framework-based pages.

What is Customization?

Customization is the process of creating new forms/pages. While Oracle does provide tools to do this (i.e., Oracle Forms and JDeveloper 10g with OA Extension), this is the least supported option.

What is the difference between Pre-Form and When-New-Form-Instance trigger?

Pre-Form trigger will be fired before entering into the form. It is the first trigger that fires when a form is run; fires before the form is visible. It is useful for setting access to form items, initializing global variables, and assigning unique primary key from an Oracle sequence.

When-New-Form-Instance trigger will be fired whenever form is ready to accept the data from the user.

Another main difference between the two is that you cannot navigate in a pre-from trigger (restricted) whereas you can navigate in a when-new-form-instance trigger. For example : go_block and execute-query will only work in when-new-form-instance trigger not in pre-from trigger.

What are the triggers fired while creating Master Detail form?

On-Clear Details (Form Level)
On-Populate-Details (Block Level)
On-Check-delete-Master (Block Level)

How will you get the block Name in a form?

By using SYSTEM.CURRENT_BLOCK

What is the difference between Pre-insert and On-insert trigger?

Pre-insert trigger fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert. On-insert trigger fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.

What is the difference between Pre-Query and Post-Query trigger?

Pre-Query trigger validates the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database. Post-Query trigger perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. It fires once for each record fetched into the block.

What is the trigger sequence while opening a form?

Pre-Form,
Pre-Block,
Pre-Record,
Pre-Item,
When-new-form-Instance,
When-new-block-Instance,
When-new-Record-instance,
When-new-Item-instance,
Post-item,
post-record,
Post-block,
Post-form

What is the difference between new form, open form & call form?

New_form:-Once we move into the destination automatically source will be closed.

Open_form:- It is a two way connection between source and destination. Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.

Call_form:- Call_form() runs an indicated form while keeping the parent form active. Without closing the destination one cannot come back to the source.

How to call a Report from a form?

By using RUN_PRODUCT Built-in.

Can we write Commit statement in forms triggers?

Yes

What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?

These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively.

A query fetched 10 records. How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?

PRE-QUERY fires once. POST-QUERY fires 10 times.

How can you execute the user defined triggers in forms?

To execute a user-named trigger, you must call the EXECUTE_TRIGGER built-in procedure, as shown here: Execute_Trigger(‘my_user_named_trigger’);

What are Restricted Built-in Subprograms in forms?

Any built-in subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another, and those that involve database transactions. Restricted built-ins are not allowed in triggers that fire in response to navigation.

For example, the GO_ITEM and NEXT_SET built-ins are both restricted procedures. GO_ITEM moves the input focus from a source item to a target item, which requires navigation. Similarly, the NEXT_SET procedure causes Oracle Forms to navigate internally to the block level, fetch a set of records, and then navigate to the first item in the first record. (Note that this navigation happens internally as a result of default processing, and may not be apparent to the form operator.) Because GO_ITEM and NEXT_SET both initiate navigation, they cannot be called from triggers that fire in response to internal navigational events; that is, triggers that fire while navigation is already occurring. Thus, a restricted procedure cannot be called from a Pre-Block trigger, because the Pre-Block trigger fires during internal navigation. In fact, restricted subprograms are not allowed from any PRE- or POST- navigational triggers. You can, however, call a restricted built-in subprogram from a When-New-Instance trigger. For example, the When-New-Item-Instance trigger fires after navigation to an item has succeeded, when the form is waiting for input.

What are System Variables in forms?

A system variable is a Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.

List system variables available in forms 10g?

SYSTEM.CURRENT_FORM

SYSTEM.CURRENT_ITEM

SYSTEM.CURRENT_VALUE

SYSTEM.CURSOR_BLOCK

SYSTEM.CURSOR_ITEM

SYSTEM.CURSOR_RECORD

SYSTEM.CURSOR_VALUE

SYSTEM.FORM_STATUS

SYSTEM.LAST_FORM

SYSTEM.LAST_QUERY

SYSTEM.LAST_RECORD

SYSTEM.MASTER_BLOCK

SYSTEM.MESSAGE_LEVEL

SYSTEM.SUPPRESS_WORKING…..etc.

What are the triggers associated with the image item?

When-Image-activated(fires when the operator double clicks on an image Items)

When-image-pressed(fires when the operator selects or deselects the image item)

What are the built-in routines available in forms to create and manipulate a parameter list?

Add_parameter

Create_Parameter_list

Delete_parameter

Destroy_parameter_list

Get_parameter_attr

Get_parameter_list

set_parameter_attr

What are the built-ins used to trapping errors in forms?

Error_type : Returns the error message type(character)

Error_code : Returns the error number

Error_text : Returns the message text of the Oracle Forms error

Dbms_error_code : Returns the error number of the last database error that was detected.

Dbms_error_text : Returns the message number (such as ORA-01438) and message text of the database error

What is the predefined exception available in forms ?

The FORM_TRIGGER_FAILURE exception is a predefined PL/SQL exception available only in Oracle Forms.

What are the Built-ins used for sending Parameters to forms?

You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

How do you reference a Parameter?

In pl/sql, You can reference and set the values of form parameters using bind variables syntax.

How do you reference a parameter indirectly?

To indirectly reference a parameter use the NAME_IN or COPY built-ins.

What is forms_DDL?

It issues dynamic sql statements at run time, including server side pl/sql and DDL

What is Text_io Package?

It allows you to read and write information to a file in the file system.

What is When-Database-Record trigger?

It fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. It generally occurs only when the operator modifies the first item in the record, and after the operator attempts to navigate out of the item.

What is the difference between $$DATE$$ & $$DBDATE$$?

$$DBDATE$$ retrieves the current database date

$$DATE$$ retrieves the current operating system date.

What is a timer?

Timer is a “internal time clock” that you can programmatically create to perform an action each time the timer expires.

What are built-ins associated with timers?

find_timer

create_timer

delete_timer

What is the difference between post database commit and post-form commit?

Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.

What is the 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

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_cusom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties

o) Save and compile the form.

p) Place the .fmx in the server directory.

q) Register in the AOL

APPLICATION à FORMà FUNCTIONà MENU

What is template?

The TEMPLATE form is the required starting point for all development of new Forms. The TEMPLATE form includes platform–independent attachments of several Libraries.

APPSCORE :- It contains package and procedures that are required of all forms to support the MENUS ,TOOLBARS.

APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.

FNDSQF :- It contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.

CUSTOM :- It allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom ( such as moving to another form and querying up specific records)

Where exactly you place your forms in APPS environment?

/apps/visappl/au/11.5.0/forms/US

What are the Different PLL’s used in Forms?

CUSTOM.pll

FNDSQF.pll

APPCORE.pll

APPCORE2.pll

appdaypk.pll

APPSTAND.pll

What are the triggers that can be modified during Forms Customization?

Pre-Forms

When-New-Form-Instance

Query_Find

Post-Form

Key-Clrfrm

What are the triggers that cannot be modified during Forms Customization?

STANDARD_ATTACHMENT

ZOOM

FOLDER_ACTION

KEY-HELP

KEY-EXIT

KEY-COMMIT

WHEN-WINDOW_CLOSED

CLOSE_WINDOW

What is a display item?

Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

How many maximum number of radio buttons can you assign to a radio group?

Unlimited no of radio buttons can be assigned to a radio group.

Can you change the default value of the radio button group at run time?

No.

What triggers are associated with the radio group?

Only when-radio-changed trigger associated with radio group.

What are the various states of a form?

CHANGED: Indicates that the form contains at least one block with a Changed record.

NEW: Indicates that the form contains only New records.

QUERY: Indicates that a query is open.

What are the different objects that you cannot copy or reference in object groups?

objects of different modules

another object groups

individual block dependent items

program units

What are the different modals of a window?

Modeless windows

Modal windows

What are Modeless windows?

More than one Modeless window can be displayed at the same time, and operators can navigate among them if your application allows them to do so . On most GUI platforms, Modeless windows can also be layered to appear either in front of or behind other windows.

What are modal windows?

Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

How do you display console on a window ?

The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.

Can you have more than one content canvas attached with a window?

Yes. Each window you create must have at least one content canvas assigned to it. You can also create a window that has manipulate content canvas. At run time only one of the content canvas assign to a window is displayed at a time.

How many windows in a form can have console?

Only one window in a form can display the console, and you cannot change the console assignment at runtime.

What are the different window events activated at runtimes?

When_window_activated

When_window_closed

When_window_deactivated

When_window_resized

When a form call a pl/sql routine if there is an error in the pl/sql routine how do you place custom message in the form?

FND_MESSAGE.SHOW displays an informational message in a forms modal window or in a concurrent program log file only.
fnd_message.set_string(‘Message Text’);
fnd_message.show;
FND_MESSAGE.HINT to display a message in the forms status line and FND_MESSAGE.ERASE to clear the forms status line. FND_MESSAGE.HINT takes its message from the stack, displays the message, and then clears that message from the message stack.

List the built-in routines for controlling a window during run-time?

Find_window,

get_window_property,

hide_window,

move_window,

resize_window,

set_window_property,

show_View

What built-in is used for changing the properties of a window dynamically?

Set_window_property

What built-in is used for showing an alert during run-time?

Show_alert

Can you change alert messages at run-time?

Yes. By Set_alert_property.

What is the built-in function used for finding the alert?

Find_alert

What built-in routines are used to display editor dynamically?

Edit_text item

show_editor

What is the difference between COPY and NAME_IN ?

Copy is package procedure that writes values into a field. Name_in is a package function that returns the contents of the variable to which you apply.

Can you attach an lov to a field at run-time?

Yes. By Set_item_proprety

What is the built-in used to get and set lov properties during run-time?

Get_lov_property

Set_lov_property

Give built-in routines related to a record groups?

Create_group (Function)

Create_group_from_query(Function)

Delete_group(Procedure)

Add_group_column(Function)

Add_group_row(Procedure)

Delete_group_row(Procedure)

Populate_group(Function)

Populate_group_with_query(Function)

Set_group_Char_cell(procedure)

What is the built-in routine used to count the no of rows in a group?

Get_group_row_count

What are the built-ins that are used to attach an LOV programmatically to an item?

set_item_property

get_item_property

How many number of columns a record group can have?

A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does not exceed 64K.

What is the Maximum allowed length of Record group Column?

Record group column names cannot exceed 30 characters.

What are the built-ins used for Creating and deleting groups?

CREATE-GROUP (function)

CREATE_GROUP_FROM_QUERY(function)

DELETE_GROUP(procedure

What are the difference between Lov & List Item?

Lov is a property where as list item is an item. A list item can have only one column while a lov can have one or more columns.

What are the different display styles of list items?

Pop List

Text List

Combo box

What is Pop List?

The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.

What is a Text List?

The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.

What is a Combo Box?

A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.

What are different types of modules available in oracle form?

Form module – a collection of objects and code routines.

Menu module – a collection of menus and menu item commands that together make up an application menu.

Library module – a collection of user named procedures, functions and packages that can be called from other modules in the application.

What are the default extensions of the files created by forms modules?

.fmb – form module binary

.fmx – form module executable

What are the default extensions of the files created by menu module?

.mmb, .mmx

What is data block & control block?

Data Block:-It is a Logical Collection of Items.
Control Block:-It is a data block which is totally independent of Database Table or View.

Note: – A data block can have control block items but not vice versa.

What is property class & visual attributes?

Property Class:

A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the setting of any property in the class that makes sense for that object. Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent.

Visual Attributes:

Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application’s interface. Visual attributes can include the following properties: Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight Color and pattern properties: Foreground Color, Background Color, Fill Pattern. Every interface object has a Visual Attribute Group property that determines how the object’s individual visual attribute settings (Font Size, Foreground Color, etc.) are derived. The Visual Attribute Group property can be set to Default, NULL, or the name of a named visual attribute defined in the same module.

What is the difference between property class and visual attribute?

We can change Visual Attribute properties dynamically at runtime, but we cannot change Property class properties. When you inherit the both Visual Attribute properties and Property class properties to an item Visual Attribute properties overrides the Property class properties.

What is object group?

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

What is Record Group?

It is an internal memory data structure and a separate object in the form module. It’s main usage is to provide data to the LOV and dynamically to the list item and to perform client level validations.

What are the types of Record-Groups?

Static Record Group:- A static record group is not associated with a query; instead, you define its structure and row values at design time, and they remain fixed at runtime. Static record groups can be created and modified only at design time.

Query Based Record Group: – A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement. The records in a query record group are the rows retrieved by the query associated with that record group.

Non Query Record Group:- A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime. Non-query record groups can be created and modified only at runtime.

How to change Record Group dynamically?

By Using Non-Query Record Group.

What is Library?

A library is a collection of subprograms, including user-named procedures, functions, and packages. We can attach a library to any other form, menu or library module. It provides an easy method of reusing objects and enforcing standards across the entire development organization.

What are the types of canvases available in forms & what is the default canvas?

Content:-it is the base view of window which occupies the entire surface of window. It can have any no of canvases but at a time only one is visible.

Stacked:-It is always displayed above the content canvas because the content Canvas is the base view. It can have any no of stacked canvases and more than one stacked canvas can be displayed at a time.

Tool bar:- A toolbar canvas often is used to create toolbars for individual windows. There are two types of tool bars Horizontal and Vertical Tool bar Canvas. Horizontal tool bar canvases are displayed at the top of window and only one horizontal tool bar can be attached to a form module. Vertical tool bar is used to display top to bottom on the left side of the window.

Tab:-It is a collection of one or more tab pages. It is mainly used to display a large amount of related information a single dynamic form builder canvas object.

Content Canvas is the default canvas.

What are Alerts?

An alert is a modal window that displays a message notifying the operator of some application condition. There are three styles of alerts: Stop, Caution, and Note.
To display an alert, your application must execute the SHOW_ALERT built-in subprogram from a trigger or user-named subprogram. SHOW_ALERT is a function that returns a numeric constant.

Show_Alert(alert_name) Return NUMBER;

What are LOVs?

An LOV is a scrollable popup window that provides the end user with either a single or multi-column selection list. LOV values are derived from record groups. The LOVs in the Form Builder interface have the same auto-reduction and searching functionality as the LOVs you create for your own applications.

There are two built-in subprograms that can be used to display an LOV:
1) LIST_VALUES
2) SHOW_LOV

List the system variables related in Block and Field?

1. System.block_status

2. System.current_block

3. System.current_field

4. System.current_value

5. System.cursor_block

6. System.cursor_field

7. System.field_status

What is a master detail relationship?

A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.

What are the Various Master and Detail Relationships?

The various Master and Detail Relationship are:

a) NonIsolated :: The Master cannot be deleted when a child is existing.

b) Isolated :: The Master can be deleted when the child is existing.

c) Cascading :: The child gets deleted when the Master is deleted.

What are the procedures that will be created when a master details form created?

CHECK_PACKAGE_FAILURE
CHECK_ALL_MASTER_DETAILS
QUERY_MASTER_DETAILS

What is mouse navigate property of button?

When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus to the item when the operator activates the item with the mouse. When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.

What is difference between PL/SQL library and object library in Forms?

PL/SQL library contains only pl/sql codes which can be used for standardizing the validations etc. in forms whereas Object Library is used to create, store, maintain, and distribute standard and reusable Form objects.

What are the different default triggers created when Master Deletes Property is set to Nonisolated?

On-Check-Delete-Master

On-Clear-Details

On-Populate-Details

What are the different default triggers created when Master Deletes Property is set to Cascade?

On-Clear-Details

On-Populate-Details

Pre-delete

What are the different default triggers created when Master Deletes Property is set to isolated?

On-Clear-Details

On-Populate-Details

What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?

Show_editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

What is the “LOV of Validation” Property of an item? What is the use of it?

When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.

Calling a Report from a Form

Using RUN_PRODUCT Built-in we can call a Report from a Form.

It will take 7 Parameters as follows

1) Product name :: REPORT/GRAPHICS

2) Module name :: Report name

3) Communication Mode :: Synchronous /Asynchronous

SYN :: we cannot work with the Form until and unless you closes the Report.

ASYN:: We can work with Form & Reprot (Navigate)

4) Exec :: Batch/Run time

Batch :: it will fetch all records at a time

Run time :: It will fetch only one Record at a time

5) File name :: database/Filesystem

6) Parameters :: Report Parameters

7) Display :: For Graphics (Mostly null)

Ex :: If we have :p_deptno parameter then we can call as Follows

RUN_PRODUCT (‘REPORT’,'C:\TEST1.rep’,SYN,BATCH,DATABASE,p_deptno,’ ‘);

Commands for Form Compilation

Command in 11i :

f60gen module=CUSTOM.pll userid=apps/(appspwd) module_type=LIBRARY batch=NO compile_all=special output_file=$AU_TOP/resource/CUSTOM.plx

f60gen module=XXPOCF.fmb userid=apps/(appspwd) module_type=form batch=no compile_all=special output_file=$XXPO_TOP/forms/US/XXPOCF.fmx

Command in R12 :

$ORACLE_HOME/bin/frmcmp_batch module_type=LIBRARY module=$AU_TOP/resource/CUSTOM.pll userid=apps/(appspwd) output_file=$AU_TOP/resource/CUSTOM.plx compile_all=special

$ORACLE_HOME/bin/frmcmp_batch module=$XXFND_TOP/forms/US/XXFND_FHLOG.fmb userid=apps/(appspwd) output_file=$XXFND_TOP/forms/US/XXFND_FHLOG.fmx module_type=form compile_all=special

Oracle Forms Personalization

With the Oracle E-Business Suite release 11.5.10, the Oracle has introduced a mechanism which revolutionizes the way the forms can be customized to fulfill the customer needs. For many years, Oracle Applications has provided a custom library using which the look and behavior of the standard forms can be altered, but the custom library modifications require extensive work on SQL and PL/SQL. In the release 11.5.10, Oracle has provided a simple and easy feature to implement the customer specific requirements without modifying the underlying forms code or CUSTOM library. Although CUSTOM library still can be used for forms customization to implement the complex business logic, the personalization feature provided in the latest release is easy, faster and requires minimum development effort.

Why Forms Personalization?

§ Oracle Supports personalization unlike customization.

§ Personalizations are stored in tables rather than files.

§ Will not have a bigger impact when you upgrade or apply patches to the environment.

§ Can be moved easily through FNDLOAD from one instance to other.

§ Can be restricted at site/responsibility/user level.

§ Easy to disable/enable with click of a button.

§ Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

§ Can be applied to new responsibilities/users easily.

§ Can be restricted to a function or form.

What can be implemented through Forms Personalization?

The below can be done using Personalization:

§ Zoom from one form to another.

§ Pass data from one form to another through global variables.

§ Change LOV values dynamically.

§ Enable/Disable/Hide fields dynamically

§ Display user friendly messages when required

§ Launch URL directly from oracle form

§ Execute PL/SQL programs through FORM_DDL package

§ Call custom libraries dynamically

Personalization Tables:

FND_FORM_CUSTOM_RULES

FND_FORM_CUSTOM_ACTIONS

FND_FORM_CUSTOM_SCOPES

FND_FORM_CUSTOM_PARAMS

FND_FORM_CUSTOM_PROP_LIST

FND_FORM_CUSTOM_PROP_VALUES

Invoking the Personalization screen:

The personalization form should be used to implement the custom rules on a specific form. The specific form refers to the desired form on which you want to apply the custom business logic or modify the form behavior.

The personalization form is invoked by…

Menu Navigation: Help > Diagnostics > Custom Code > Personalize

Disable the personalization feature:

It is possible that a change you make completely breaks a form, to the point that it will not even run! Here’s how to recover:

§ On the pulldown menu, choose Help > Diagnostics > Custom Code > Off

§ This will disable all callouts to Forms Personalization

§ Run the form of interest

§ It should run now, because your changes were skipped

§ Invoke the Personalization screen and correct the problem

§ On the pulldown menu, choose Help > Diagnostics > Custom Code > Normal to re-enable processing of Personalizations

Limitations:

Although it is faster than a speeding bullet, it is not able to leap over tall buildings:

§ You can only change what Forms allows at runtime:

§ Cannot create new items

§ Cannot move items between canvases

§ Cannot display an item which is not on a canvas

§ Cannot set certain properties

§ Cannot change frames, graphics, boilerplate

§ You can only respond to certain Trigger Events:

§ WHEN-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-RECORD-INSTANCE, WHEN-NEW-ITEM-INSTANCE

§ WHEN-VALIDATE-RECORD (not in all forms)

§ Product-specific events

§ May interfere with, or be overridden by, base product code

§ Expected user is an Admin/Developer

§ Knowledge of Oracle Developer is extremely desirable

§ Knowledge of PL/SQL, Coding Standards and/or APIs required in some cases

§ Normal rules for customizations apply

§ Extensive testing in a Test environment is required!

Relationship with CUSTOM library:

§ CUSTOM is a stub library Oracle ships that receives Trigger Events. Customers are free to add any code they like to it.

§ CUSTOM and Form Personalizations drive off the same Trigger Events.

§ Form Personalizations are processed first, then the event is sent to CUSTOM

§ CUSTOM can do more because it has complete access to all PL/SQL and SQL.

§ But for most changes, Form Personalizations is adequate and is significantly simpler.

Custom.pll in Oracle Application

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a new business rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.

CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are:-

1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens
6. Enabling Special Menu

Where is this located?

Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this?

Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?

Once you make changes you need to compile the pll. Use the F60gen to compile it

f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.

For following Events call will go to CUSTOM Library:

WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)

Custom Library contains Custom Package which is having two Functions and one procedure.

1] ZOOM_AVAILABLE:

This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

`01`	`Sample code1:`
`02`

`03`	`function` `zoom_available return` `Boolean is`
`04`

`05`	`form_name varchar2(30) := name_in('system.current_form');`
`06`	`block_name varchar2(30) := name_in('system.cursor_block');`

`07`
`08`	`begin`

`09`
`10`	`if (form_name = 'DEMXXEOR'` `and` `block_name = 'ORDERS') then`

`11`	`return` `TRUE;`
`12`	`else`

`13`	`return` `FALSE;`
`14`	`end` `if;`

`15`
`16`	`end` `zoom_available;`

`17`
`18`	`Sample code2:`

`19`
`20`	`function` `zoom_available return` `Boolean is`

`21`
`22`	`form_name varchar2(30) := name_in('system.current_form');`

`23`	`block_name varchar2(30) := name_in('system.cursor_block');`
`24`

`25`	`begin`
`26`

`27`	`if (form_name = 'APXINWKB'` `and` `block_name = 'INV_SUM_FOLDER')`
`28`	`then`

`29`	`return` `TRUE;`
`30`	`elsif (form_name = 'APXINWKB'` `and` `block_name = 'LINE_SUM_FOLDER')`

`31`	`then`
`32`	`return` `TRUE;`

`33`	`else`
`34`	`return` `FALSE;`

`35`	`end` `if;`
`36`

`37`	`end` `zoom_available;`

2] STYLE:

This function returns a integer value. This function allows to override the execution style of Product specific events, but it doesn’t effect generic events like when-new-form-instance. Possible return values are:
1. custom.before
2. custom.after
3. custom.override
4. custom.standard

By default it returns custom.standard.

`1`	`Sample code:`
`2`	`function` `custom.style(event_name varchar2) return` `integer` `is`

`3`	`begin`
`4`	`if event_name = ’MY_CUSTOM_EVENT’ then`

`5`	`return` `custom.override;`
`6`	`else`

`7`	`return` `custom.standard;`
`8`	`end` `if;`

`9`	`end` `style;`

3] EVENT:
This procedure allows you to execute your code at specific events including:

– ZOOM

– WHEN-NEW-FORM-INSTANCE

– WHEN-NEW-BLOCK-INSTANCE

– WHEN-NEW-RECORD-INSTANCE

– WHEN-NEW-ITEM-INSTANCE

– WHEN-VALIDATE-RECORD

By default this routine must perform ‘null;’

`01`	`Sample code:`
`02`	`procedure` `event(event_name varchar2) is`

`03`
`04`	`form_name varchar2(30) := name_in(’system.current_form’);`

`05`	`block_name varchar2(30) := name_in(’system.cursor_block’);`
`06`

`07`	`begin`
`08`

`09`	`if (form_name = 'XXBI'` `and` `block_name = 'xxcc') Then`
`10`	`if(event_name = 'WHEN-NEW-FORM-INSTNACE')`

`11`	`--Write your code here`
`12`	`elsif(event_name = 'WHEN-VALIDATE-RECORD')THEN`

`13`	`--Write your code here`
`14`	`Else`

`15`	`Null`
`16`	`end` `if;`

`17`	`end` `if;`
`18`	`end;`

How to make the changes get affected?

Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.

Forms Personalization: an alternative of custom.pll

In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.

Advantages of Forms Personalization over Custom.PLL:

§ Forms personalization can be used by an user with limited PL/SQL knowledge.

§ Changes take place immediately on reopening the form.

§ Anything which can be done using Custom.PLL can be done using Forms Personalization also.

§ Personalizations are stored in base tables related to Form Personalization.

§ CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.

§ Easy to disable/enable with click of a button.

§ Can be moved easily through FNDLOAD from one instance to other.

§ Can be restricted at site/responsibility/user level.

§ Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

Triggers in Oracle Forms

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.

Block Processing Triggers:

Block processing triggers fire in response to events related to record management in a block.

§ When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.

§ When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.

§ When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.

Interface Event Triggers:

Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.

§ When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.

§ When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.

§ When-Image-Activated Initiate an action whenever the operator double-clicks an image item.

§ When-Image-Pressed Initiate an action whenever an operator clicks on an image item.

§ When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.

§ When-Window-Activated Initiate an action whenever an operator or the application activates a window.

§ When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.

§ When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.

Master/Detail Triggers:

Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.

§ On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.

§ On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.

§ On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.

Message-Handling Triggers:

Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.

§ On-Error Replace a default error message with a custom error message, or to trap and recover from an error.

§ On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.

Validation Triggers:

Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.

§ When-Validate-Item

§ When-Validate-Record

Navigational Triggers:

Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.

§ Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.

§ Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.

§ Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.

§ Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.

§ Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.

§ Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.

§ Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.

§ Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.

§ When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).

§ When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.

§ When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.

§ When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.

Transactional Triggers:

Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.

§ On-Delete

§ On-Insert

§ On-Update

§ On-Logon

§ On-Logout

§ Post-Database-Commit

§ Post-Delete

§ Post-Insert

§ Post-Update

§ Pre-Commit

§ Pre-Delete

§ Pre-Insert

§ Pre-Update

Query-Time Triggers:

Query-time triggers fire just before and just after the operator or the application executes a query in a block.

§ Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.

§ Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.

Modal & Modeless Windows in Oracle Forms

A window in oracle forms is a container for all visual objects that make up a Forms application. You can create two different type of windows in oracle forms. Lets have a brief comparisons between these two types of windows.

Modal Window:

A modal window is a restricted window that the user must respond to before moving the input focus to another window. Modal windows:

§ Must be dismissed before control can be returned to a modeless window

§ Become active as soon as they display

§ Require a means of exit or dismissal

Modeless Window:

A modeless window is an unrestricted window that the user can exit freely. Modeless windows:

§ Can display many at once

§ Are not necessarily active when displayed

§ Are the default window type

AP INVOICE INTERFACE PROCEDURE

AP_INVOICES_INTERFACE
-----------------------

1)INVOICE_ID not null Required, Primary key. This value is assigned in
the Invoice Gateway by the AP_INVOICES_INTERFACE_S sequence.

2) INVOICE_NUM Required if there is more than one invoice for the
supplier during import

3)VENDOR_ID or VENDOR_NAME or VENDOR_NUM is reqd if not matched to PO

4)VENDOR_SITE_ID or VENDOR_SITE_CODE is reqd if not matched to PO

5)INVOICE_AMOUNT

6)PO_NUMBER

6)INVOICE_CURRENCY_CODE:if not eneterd defaulted to functional currency

7)EXCHANGE_RATE_TYPE :User,Spot, Corporate, EMU Fixed, and user–defined.for Spot, Corporate, or any user–defined rate type, the value you enter here is validated against
the GL Daily Rates table.If you use EMU Fixed,Payables will provide the exchange rate during import. If you use User as the exchange rate type,you must enter a value for EXCHANGE_RATE orthe record will be rejected during import.

8)TERMS_NAME or TERMS_ID.:Payables searches1)invoice record header 2)purchase order terms if invoice is matched to PO 3)supplier site.

9)SOURCE
10)DOC_CATEGORY_CODE :
If you are using automatic sequential numbering, then Payables Open
Interface Import uses this column to assign a document category to the
invoice it creates.If the Sequential Numbering profile value is ”Always” and you do not
enter a value in this column, then during import Payables will use STANDARD as the category if the invoice amount is zero or positive,and CREDIT if the invoice amount is negative.
If you enable the Allow Document Category Override Payables option,you can enter the document category you want Open Interface Import to assign to the invoice created from this record

10)VOUCHER_NUM:If you use manual sequential numbering, then enter a unique value

11)PAYMENT_CURRENCY_CODE:Currency code for the payment. If you do not provide a value, then
during import PAYMENT_CURRENCY_CODE will be set to the same value as the INVOICE_CURRENCY_CODE,

12)PAYMENT_METHOD_LOOKUP_CODE:Method that will be used to pay the invoice.
The value must be a valid value for the PAYMENT_METHOD lookup code: Validation:

13)ACCTS_PAY_CODE_COMBINATION_ID:Liability account.
14)ORG_ID

AP_INVOICE_LINES_INTERFACE
----------------------------

1)INVOICE_ID :required
2)INVOICE_LINE_ID :not reqd,use AP_INVOICE_LINES_INTERFACE_S
3)LINE_NUMBER:unique ,reqd
4)LINE_TYPE_LOOKUP_CODE:valid values ITEM, TAX,MISCELLANEOUS, or FREIGHT.
5)AMOUNT :Invoice distribution amount
6)ACCOUNTING_DATE:The GL Date for the invoice distributions. The date must be in an open
or future period.
7)PO_HEADER_ID or PO_NUMBER
8)PO_LINE_ID or PO_LINE_NUMBER
9)dist_code_combination_id or DIST_CODE_CONCATENATED
10)ORG_ID

Document Category Assignment and Document Sequence Setup:
from fnd_doc_sequence_assignment,fnd_document_sequences

Now create both headers and line in one package:

Suppliers:
New R12 tables -> Old 11i Tables

AP_SUPPLIERS - replaces PO_VENDORS

AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY (Payments) and HZ (TCA):

IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.

HZ_PARTIES - Party data for the suppliers.

HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices:

Additional table in R12: AP_INVOICE_LINES_ALL

Allocations - AP_CHRG_ALLOCATIONS_ALL is obsolete in R12

Taxes:

Functionality provided by E-Business Tax

New tables in R12

ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)

ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)

ZX_REC_NREC_DIST - Tax distributions for the invoice (trx_id = invoice_id)

ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments:

Functionality moved to central Payments (IBY)

New IBY tables in R12:

IBY_PAY_SERVICE_REQUESTS - Payment Process Request information

Accounting:

Functionality moved to SubLedger Accounting (SLA)

New R12 tables:

XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL

XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL

XLA_AE_LINES-> replaces AP_AE_LINES_ALL

XLA_DISTRIBUTION_LINKS

Trial Balance:

New R12 Table

XLA_TRIAL_BALANCES

AP_LIABILITY_BALANCE-> not used in new R12 transactions

AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts:

Functionality moved to Cash Management.

CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL -> replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

D2k Interview Question

How to take care of null values in SQL Loader ?

What are the setup steps for testing ?

What is an Invoice ? How many types of invoices are there in AP and AR ?

What id recurring invoices ?

What are AP setup steps ?

If the valueset is of type –TABLE then how many tables can we attach in the valueset ?

Can you add a descriptive FlexField to a table which already have data in it ?

If any conflict occurs in FSG the who will override Column Set or Row Set ?

What is ICA (Internet Computing Architecture)?

What is Set of Books ? What are the four conditions when you change your SOBs?

What is Multiple Organization technical architecture?

What can we find TEMPLATE.FMB file ?

What is the difference between cross-validation rules and security-rules?

In how many ways can you enter a journal in GL?

What are the setup steps for AP, AR, GL?

How to register tables and columns in AOL?

What are cycles of GL, AP, and AR?

What are Summary Accounts and Roll up groups ?

What is the use of CUSTOM Library in Oracle Apps?

Can you use REF cursors in CUSTOM library?

How many Flexfields are there in AR and what are they ?

What are User Exits and what are different types of User Exits in Oracle Apps ?

What are value sets

what do you mean by HZ_ in customer tables

How can you handle multiple rows without using loops in PL/SQL ?

What are the different types of files used in SQL Loader ?

What is integration between modules?What modules in Order management can be integrated with Finance modules ?How this integration is done?

How can I incorporate a lexical parameter in my report for having the FROM DATE and TO DATE parameter?. Can you Pls let me know of the steps involved in doing the same?

Can anybody suggest me how can the multi org. be set up?

What does US mean in appl_top/au/11.5.0/reports/US?

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?

What is a request Sets? How to create a request Set?

What are the different types of Value sets and expalin them briefly which value set will use when?

How to submit concurrent program through pl/sql?

Xml Publisher Basics

Calling a Report from a Form

Commands for Form Compilation

Oracle Forms Personalization

Why Forms Personalization?

What can be implemented through Forms Personalization?

Personalization Tables:

Invoking the Personalization screen:

Disable the personalization feature:

Limitations:

Relationship with CUSTOM library:

Custom.pll in Oracle Application

Triggers in Oracle Forms

Block Processing Triggers:

Interface Event Triggers:

Master/Detail Triggers:

Message-Handling Triggers:

Validation Triggers:

Navigational Triggers:

Transactional Triggers:

Query-Time Triggers:

Modal & Modeless Windows in Oracle Forms

AP INVOICE INTERFACE PROCEDURE

No comments:

Post a Comment

Types of animations

What are the types of Concurrent Managers?
Can we delete a Concurrent Manager?