Important questions to be remembered by a Oracle Consultant Part3

Difference between Conversions and Interfaces?
Conversion is to bring the data from other (non-Ora Apps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on. So conversion itself is a project.

Interfaces are Concurrent Programs/Program sets (pl/sql, pro*C, unix scripts, executables) . Interfaces are basically of two types (Oracle supplied (vanila programs eg: Auto invoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activity interface means periodic activity.

example:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

Which module is not a multi org?
General Ledger and CRM Foundation Modules

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

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?

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

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

What are SQL CODE and SQL ERRM and why are they important for PL/SQL developers? 
SQL CODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they?
There are basically 9 Processes.They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are:

a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is committed.

b) LogWriter(LGWR) :: Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.

c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance start up.This is useful for recovery from system failure.

d) Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.

e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint.

f) Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.

g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network.

h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture.

i)  Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle?
There are basically 6 types of sql statments.They are
a) Data Definition Language(DDL) :: The DDL statments define and maintain objects and drop objects.

b) Data Manipulation Language(DML) :: The DML statements manipulate database data.

c) Transaction Control Statements :: Manage change by DML

d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g, Alter Statements,Set Role

e) System Control Statements :: Change Properties of Oracle Instance .e.g, Alter System.

f) Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What is a Transaction in Oracle?
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.

Key Words Used in Oracle?
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.

b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.

c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.

d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.

e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.

f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.

g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.

g) Database Buffer Cache :: Database Buffer of SGA stores the most recently used blocks of database data.The set of database buffers in an instance is called Database Buffer Cache.

h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.

i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.

j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.

What are Procedure,functions and Packages?
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures?
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.

e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinations

At statement level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.

Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.

Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

How many Integrity Rules are there and what are they?
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null

b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.

c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships?
The various Master and Detail Relationship are

a) Non Isolated :: 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 Various Block Coordination Properties?
The various Block Coordination Properties are

a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.

b) Defferred with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.

c) Defferred with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

What are the Different Optimization Techniques?
The Various Optimization techniques are

a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes

b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)

c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenever they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No

d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Restrict '&' in Sql*Plus
sql> set define off
This will restrict the use of '&' in the sql*plus for that particular session

<<<Previous

Read More »

Oracle Apps Interview Questions-15

211) How do you set profile in oracle applications In Application Developer responsibility?  
Open „Profile‟ Function 

212) What is the syntax for loading data through SQL * Loader from multiple files simultaneously?
Sqlldr scott/tiger@orcl control = ctlfile
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE) 

213)Give the relation between categories and items table?
MTL_SYSTEM_ITEMS
MTL_CATEGORIES_B
Relation INVENTORY_ITEM_ID 

214)In which tables are the categories of an item stored?

• MTL_CATEGORIES_B
• MTL_ITEM_CATEGORIES 

215)Significance of ALL in apps tables?
Tables which are related with Multiorg is suffixed with ALL. 

216) Explain about flexfield in GL.In what way it is useful?
KFF is a set of segments, each segment will identify a unique characterstic of an entity. it can be termed as intelligent key. we can define our business requirements without doing any programming.
A DFF is a set of segments which can be used to capture extra info. without any customisation. 

217) Execution methods?
a. Host 
b. Immediate
c. Java Stored Procedure
d. Java Concurrent Program
e. Multi Language Function
f. Oracle Reports
g. PL/SQL Stored Procedure
h. Request Set Stage Function
i. Spawned
j. SQL*Loader
k. SQL*Plus 

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

219) Difference between Application Developer and System Administrator? 
Role of Technical Consultant:

• Designing New Forms, Programs and Reports
• Forms and Reports customization
• Developing Interfaces
• Developing PL/SQL stored procedures
• Workflow automations

Role of System Administrator:

• Define Logon Users
• Define New/Custom Responsibility
• Define Data Groups
• Define Concurrent Managers
• Define Printers
• Test Network Preferences
• Define/Add new Modules

Role of an Apps DBA:

• Installing of Application
• Upgradation
• Migration
• Patches
• Routing maintenance of QA
• Cloning of OA 

220) Request Set and where do you define it?
Request sets allow you to submit multiple requests together using multiple execution path. A request set is a collection of reports and /or programs that are grouped together. A stage is a component of a request set used to group requests within the set. All of the requests in a given stage are executed in parallel. Advantages of stages are the ability to execute several requests in parallel and then move sequentially to the next stage.
Responsibility: System Administrator
Nav: Concurrent -> Set

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

222)Registration of PL/SQL with parameters?

• Create the procedure in the module specific schema.
• Create a public synonym for that procedure in the Apps schema.
• Create the executable for that procedure.
• Create a concurrent program.
• Attach the concurrent program to that procedure.

Note: There are two mandatory parameters 1. Errbuf 2. Retcode. Any parameter which are to be passed should be succeeded with these two parameters and have to be registered. When calling the procedure, these two parameters are not mentioned.

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

224)Value Validation Types?
Dependant
Independent
None
Pair
Special
Table
Translate Independent
Translate Dependent 

225) How to define a value set depending on other value set?
Navigation Path: Login -> AOL -> Application -> Validation -> Set Select the validation type as "Dependant" and click on „Edit Information‟ button. In Dependant value set information screen mention the Independent value set name.

<<<Previous   

 

Read More »

Oracle Apps Interview Questions-14

196) What is parameter in apps and from where u can create it?
Parameters only using in report, you can create in defining the report in
apps (you can create the parameter there only). 

197)What all are the tables used in the modules u have worked on?
*In GL – I have worked on GL_JE_HEADERS(JOURNALS HEADER),GL_JE_LINES(JOURNAL LINES), GL_JE_BACHES(JOURNAL BATCHES), GL_SET_OF_BOOK(SET_OF_BOOK_ID),
*In PO- I have worked on PO_HEADER_ALL….. 

198)What is Profile? Explain different levels of Profile?
A user profile is a set of changeable options that affects the way your applications run. Oracle Application Object Library establishes a value for each option in a user‟s profile when the user logs on or changes responsibility. Your user can change the value of profile options at any time a) To create Profile Option. ( Profile Option can created by developer in application developer area) b)set the value (Values of the profile option , who will have what value at various levels is set by SYSADMIN). Oracle Application Object Library provides many options that. (Edit profile feature for every user is available to set any value to allow the user).your users can set to alter the user interface of your applications to satisfy their individual preferences. Profile Option – set at run time like – User Related, responsibility, Sequence, Printer, Security.

61 Values in 4 Levels(HIEARCHY WISE) :-

• USER
• RESPONSIBILITY
• APPLICATION
• SITE

Application Developer create the profile. System Administrator make profile option. (NOTE:- If any change in value, it will active when you re-login or switch to the responsibility.)
( Usage in the multi-tier, the profile is biggest impact)

199)How to restrict the data for a responsibility as per the ORG-ID?
Through Multi – Org(MO) u can restrict the data for a responsibility as per the ORG-ID. Only in GL- Set of book Id you set the value to restrict the data for a responsibility. 

200) is Flexfield? What is flexfield qualifier and what is segment qualifier? 
A flexfield is made up a segments (Which are actually table columns). Each segment has a name that can be assigned, and set of valid value.
Purpose and Application:-

• Flexibility to implement code structure.
• Flexibility to capture additional information.

Two Types of Flexfields in oracle apps.

• Key Flexfields (KFF)
• Descriptive Flexfields (DFF)

A key flexfield segment has a name you assign, and set of valid values you specify. Each value has a meaning which can be specified. 

Flexfield Qualifier:-A flexfield qualifier identifies a particular segment of a key flexfield.. Usually an application needs some method of identifying a particular
segment for some application purpose such as security or computations. However, since a key flexfield can be customized so that segments appear in any order with any prompts, the application needs a mechanism other than the segment name or segment order to
use for segment identification.
Segment Qualifier :- A segment qualifier identifies a particular type of value in a single segment of a key flexfield.
In the Oracle Applications, only the. Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an "identification tag" for a value. In the Accounting Flexfield, segment qualifiers can identify the account type.

201)Which flexfield qualifiers are mandatory?
"Balancing Segment‟ flex field qualifier is mandatory.

202)Difference Between versions of Apps.(Front end & Database)?
In backend- Client server architecture (old)/ Three tire architecture
In font end- Client Server Application (old)/ Web Based application 

203)What is MULTI-ORG and what is structure of multi-org?
Use a single installation of any oracle applications product to support any number of organizations. if those organizations use different set of books.
Support any number or legal entities with a single installation of oracle applications. 62 Secure access to data so that users can access only the information that is relevant to them. Structure :- Business Unit -HRMS(Employee)
-GL(Set of Books)(Currency, Calendar, Chart of Account)
Balancing Segment(You can do multiple balancing segment)
-Operating Units (Purchase, Selling, Fixed Asset, Payable,Receivables)
-Inventory Organizations (Storing Items, Transaction Happening,Ware Housing)
(Note:- Means if you maintaining GL(set of book id), If u have operating unit, if you
have inventory then its called MULTI-ORG)

204)What is difference between ORG_ID and ORGANIZATION_ID in Multi-Org.
At where we can set ORG_ID and ORGANIZATION_ID level it comes in the
structure?
A Global Variable exists in the oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID(or ORG_ID) for the multiple organization support feature.
Multi-Org views are partitioned by ORG_ID. The ORG_ID value is stored in CLIENT_INFO variable.(It comes in AP,PO,AR,OM level)
ORGANIZATION_ID – Its for Inventory, Mfg, & BOM.

205)ORG_ID can be set at master levels or transaction level?
ORG_ID can be set at transaction Level.

206) Differnet type of execution methods in Conc.Progs. Explain Each Type?
Oracle Reports- You can register your report as executable file type is oracle reports.
PL/SQL Package Procedure - You can register your PL/SQL Package Procedure as executable file type is oracle PL/SQL Package Procedure.
SQL Loader- You can register your SQL Loader SQL Loader is your executable file type.(for data loading)
SQL*Plus :- You can register your SQL script as SQL*Plus executable type.
Host Scripting:- You can write down Unix Host scripting and register here.

207)What is difference between oracle schema and apps schema?
The APPS schema- is an ORACLE schema that has access to the
complete Oracle Applications data model. This schema is maintained
by AutoInstall.

208) What are the objects APPS schema contain?
The APPS schema contains synonyms to all tables and
sequences as well as all server–side code (stored procedures, views,and database triggers).For ERP applications, data partitioning is performed by database
views. These views reside in the APPS Oracle schema and derive the 63 appropriate operating unit context from an RDBMS variable. 

209)What are the names of the parameters u pass to the Procedure which u register in the apps?

• retcode in varchar2
• errbuf in varchar2 

210) What is application short name for General Ledger you specify in FND FLEXSQL user exit? 
SQLGL 

<<<Previous   Continue>>>

 

Read More »

Oracle Apps Interview Questions-13

181)How do I run a shell script as a concurrent program?
1: Write the script and call it <name>.prog Place the script under the bin directory under your applications top directory. For example, call the script CUSTOM.prog and place it under $CUSTOM_TOP/bin
bin
2: Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called CUSTOM.prog use this: ln -s $FND_TOP/bin/fndcpesr CUSTOM This link should be named the same as your script without the .prog extension It should be in the same directory as the script.
3: Register a concurrent program as described above, using an execution method of 'Host' Use the name of your script without the .prog extension as the name of the executable For the example above, you would use CUSTOM CUSTOM
4: Your script will be passed at least 4 parameters, in $1 through $4 These will be: orauser/pwd, userid, username, request_id Any other parameters you define will be passed in $5 and higher. Make sure your script returns an exit status.
define will be passed in $5 and higher. Make sure your script returns an exit status.
5: If your script returns a failure exit status but the concurrent manager does not report the error (shows it as still running normal) apply patch 442824

182)How will u register RDF file and run it? Tell the Sequence?
a. Save the copy of ur reports in rdf file in ur local directory.
b. Transfer or copy the rdf file to cus_top under reports directory through ftp.
C. Then go concurrent program under executable menu where u define executable file and program name
d. Then go to define the program name (which ur executable file name ) and check the srs box and define the parameter and give the parameter name in token
e. Attach the program(request to ur responsibility ) and run the program and view the out put is srs through ur responsibility.

183)What is translatable Independent & Dependent ?
The value set used to support the multilingual value set.

184) How do I submit a concurrent request from PL/SQL?
using fnd_request.submit_request .
begin
v_request_id := fnd_request.submit_request(applicationshortname,
concurrentprogramshortname,
description,
paramers)
end;
commit;
if v_request_id > 0 then
dbms_output.put_line('Successfully submitted')
else
dbms_output.put_line('Not Submitted');
end;
note : to submit a conc program from UNIX/shell scrip we use CONSUB

185) How do I cancel a running concurrent request?
Navigate to the Concurrent Request Summary form Select a request The Sysadmin responsibility can cancel or hold any running request.

186) What is the difference between organization id and org_id ?
Organization_id stores inventory organization id ( like 204 for M1)
Org_id stores the OU id corresponding to a operating unit .

187) What is the difference between conversion and interfaces ?
conversion means one time activity interface means periodic activity
example:- to transfer the data old version to new version it is called conversionto transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

188) What are the different types of value sets and also explain each briefly ?
Different types of Value sets are,
1) Independent- This Value set contains list of values which does not depends on any other value
2) Dependant- It contains values which depends on any one of the Independant value
3) Pair- combines 2 flex field together to specify range of valid values
4) Special- Uses only 1 flex field structure to specify values
5) Table- This Value set contains list of values from 1 or more than 1 table columns
6) Translatable Dependant- Same as Dependant value set, only translated values are present
7) Translatable Independant- Same as Independant value set, only translated values are present.

189) How do you register a table and columns in Oracle Apps?
To register the table and columns in AOL the 
navigation is: Open Application Developer---> Application--->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.

190) What can we find TEMPLATE.FMB file ?
$AU_TOP/forms/US
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. 

191) What are the libraries attached to TEMPLATE form ?
The Template form required 19 .pll in 11i version. Those pll names are :
APPCORE.pll APPCORE2.pll FNDSQF.pll APPDAYPK.pll GLOBE.pll JE.pll JL.pll JA.pll VERT.pll GHR.pll PQH_GEN.pll PSAC.pll PSB.pll PSA.pll IGILUTIL.pll
IGILUTIL2.pll CUSTOM.pll GMS.pll FV.pll OPM.pll

192)What is Concurrent Programming?
Concurrent Processing in Oracle Apps simultaneously executes programs running in the Background with on line operations to fully utilize your hardware capacity.
Use Concurrent Programming for
Long Running – Data intensive tasks such as Posting a Journal or generating a report.

193)What is the Role of Concurrent Managers?
A Concurrent Manager is a component of Concurrent processing that monitors and runs tasks without tying up your computer.

194)What is AOL?
Oracle Applications are constructed and maintained using the Application Object Library (AOL).
The Three main areas of AOL are

• Applications Security
• Operating Profile
• Concurrent Processing

195) What are the tables related to flex field?

• FND_FLEX_VALUES
• FND_FLEX_VALUE_SETS
• FND_FLEX_VALUES_TL  

 <<<Previous    Continue>>>

Read More »

Oracle Apps Interview Questions-12

166) What is parameter in apps and from where u can create it? 
Parameters only using in report, you can create in defining the report in apps (you can create the parameter there only).

167)What all are the tables used in the modules u have worked on?
*In GL – I have worked on GL_JE_HEADERS(JOURNALS HEADER),GL_JE_LINES(JOURNAL LINES), GL_JE_BACHES(JOURNAL BATCHES), GL_SET_OF_BOOK(SET_OF_BOOK_ID),
*In PO- I have worked on PO_HEADER_ALL…..

168)What is Profile? Explain different levels of Profile?
A user profile is a set of changeable options that affects the way your applications run. Oracle Application Object Library establishes a value for each option in a user‟s profile when the user logs on or changes responsibility. Your user can change the value of profile options at any time.
a) To create Profile Option. ( Profile Option can created by developer in application developer area) 
b) set the value (Values of the profile option , who will have what value at various levels is set by SYSADMIN). Oracle Application Object Library provides many options that. (Edit profile feature for every user is available to set any value to allow the user).your users can set to alter the user interface of your applications to satisfy their individual preferences.
Profile Option – set at run time like – User Related, responsibility, Sequence, Printer, Security.
Values in 4 Levels(HIEARCHY WISE) :-

• USER
• RESPONSIBILITY
• APPLICATION
• SITE

Application Developer create the profile.
System Administrator make profile option.
(NOTE:- If any change in value, it will active when you re-login or switch to the responsibility.)
( Usage in the multi-tier, the profile is biggest impact)

169)How to restrict the data for a responsibility as per the ORG-ID?
Through Multi – Org(MO) u can restrict the data for a responsibility as per the ORG-ID. Only in GL- Set of book Id you set the value to restrict the data for a responsibility.

170) What is Flexfield? What is flexfield qualifier and what is segment qualifier?
A flexfield is made up a segments (Which are actually table columns). Each segment has a name that can be assigned, and set of valid value.
Purpose and Application:-

• Flexibility to implement code structure.
• Flexibility to capture additional information.

Two Types of Flexfields in oracle apps.

• Key Flexfields (KFF)
• Descriptive Flexfields (DFF)

A key flexfield segment has a name you assign, and set of valid values you specify. Each value has a meaning which can be specified.

Flexfield Qualifier:-A flexfield qualifier identifies a particular segment of a key flexfield..
Usually an application needs some method of identifying a particular
segment for some application purpose such as security or computations. However, since a key flexfield can be customized so that segments appear in any order with any prompts, the application needs a mechanism other than the segment name or segment order to
use for segment identification.

Segment Qualifier :- A segment qualifier identifies a particular type of value in a single
segment of a key flexfield.
In the Oracle Applications, only the. Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an ”identification tag” for a value. In the Accounting Flexfield, segment qualifiers can identify the account type.

171) Which flexfield qualifiers are mandatory?
"Balancing Segment‟ flexfield qualifier is mandatory. 

172) Difference Between versions of Apps.(Front end & Database)?
In backend- Client server architecture (old)/ Three tire architecture
In font end- Client Server Application (old)/ Web Based application

173)What is MULTI-ORG and what is structure of multi-org?
Use a single installation of any oracle applications product to support any number of organizations. if those organizations use different set of books.
Support any number or legal entities with a single installation of oracle applications.
Secure access to data so that users can access only the information that is relevant to them.
Structure :- Business Unit
-HRMS(Employee)
-GL(Set of Books)(Currency, Calendar, Chart of Account)
Balancing Segment(You can do multiple balancing segment)
-Operating Units (Purchase, Selling, Fixed Asset, Payable, Receivables)
-Inventory Organizations (Storing Items, Transaction Happening,Ware Housing)

(Note:- Means if you maintaining GL(set of book id), If u have operating unit, if you
have inventory then its called MULTI-ORG)

174)What is difference between ORG_ID and ORGANIZATION_ID in Multi-Org?
At where we can set ORG_ID and ORGANIZATION_ID level it comes in the structure?
A Global Variable exists in the oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID(or ORG_ID) for the multiple organization support feature.
Multi-Org views are partitioned by ORG_ID. The ORG_ID value is stored in CLIENT_INFO variable.(It comes in AP,PO,AR,OM level)
ORGANIZATION_ID – Its for Inventory, Mfg, & BOM.

175)ORG_ID can be set at master levels or transaction level?
ORG_ID can be set at transaction Level.

176)Differnet type of execution methods in Conc.Progs. Explain Each Type?
Oracle Reports- You can register your report as executable file type is oracle reports.
PL/SQL Package Procedure - You can register your PL/SQL Package Procedure as executable file type is oracle PL/SQL Package Procedure.
SQL Loader- You can register your SQL Loader SQL Loader is your executable file type.(for data loading)
SQL*Plus :- You can register your SQL script as SQL*Plus executable type.
Host Scripting:- You can write down Unix Host scripting and register here.

177) What is difference between oracle schema and apps schema?
Database Schema-
The APPS schema- is an ORACLE schema that has access to the
complete Oracle Applications data model. This schema is maintained
by Auto Install .

178)What are the objects APPS schema contain?
The APPS schema contains synonyms to all tables and
sequences as well as all server–side code (stored procedures, views,
and database triggers).
For ERP applications, data partitioning is performed by database
views. These views reside in the APPS Oracle schema and derive the
appropriate operating unit context from an RDBMS variable.

179)When will a New version of flint60 be released?
flint60 is a developer/development tool. Since flint60 can change at any time, the most current flint60 will always obsolete all prior releases. In other words, the development standards implemented in the most current flint60 are the standards to which everyone using flint60 must adhere.


180)How do I compile a custom C program?
Spawned programs:
Step 1: Write the code Self-explanatory
Step 2: Compile the source You must use the makefile under $FND_TOP/usrxit Use: make -f $FND_TOP/usrxit/Makefile program.o We do not support using any other makefile
Step 3: Link the program This part is a little tricky. You need to create a custom makefile for this step. Use $FND_TOP/lib/sample.mk as a starting point. Copy this file to the lib directory under your applications top directory. Rename it <short name>.mk (ie fnd.mk, gl.mk etc) Modify this file according to the directions in it. Basically you need to 53
add a target and build commands for your executable. Next, use adrelink to link the executable: adrelink force=y ranlib=y "shortname programname"
" Step 4: Register the program as in the above question
Immediate programs: Just don't do it. 

<<<Previous   Continue>>>

 

Read More »

Oracle Apps Interview Questions-11

151). What is the use of Auto lock Box?
Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing.

152). Auto Lockbox is a three-step process, what are those?

• Import
• Validation
• PostQuickCash

153). What is the order in which Autolock box searches for the types of the matching number?

• Transaction Number
• Sales Order Number
• Purchase Order Number
• Consolidated Billing Invoice Number
• Other, user-defined number.

154. What is application short name for General Ledger you specify in FND FLEXSQL user exit?SQLGL

155) . What are validations to be done in Journal Import interface?
Batch level: Set of Books, Period Name, and Batch Name
Journal Level: Set of books, Period name, Source name, Journal entry name, Currency code, Category name, Actual flag, Encumbrance type ID, User conversion type, Accounting date, Budget version ID

156) What subclass in forms6i ?
Specifies module, storage & name information about the source object and source module for a referenced objects.

157) What is the clause in SQL * Loader to program to override data into table?
REPLACE

158). How do you set profile in oracle applications In Application Developer responsibility?
Open „Profile‟ Function

159). What is the syntax for loading data through SQL * Loader from multiple files simultaneously?
Sqlldr scott/tiger@orcl control = ctlfile
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)

160) What is the table name for items in Oracle Inventory?
MTL_SYSTEM_ITEMS, MTL_CATEGORIES

161). Tell me names of important production tables & their purpose AP, AR, GL, PO?
AP: AP_INVOICES_ALL, AP_INVOICE_LINES_ALL To store invoices
AR: RA_SHIPMENT_HEADERS/ _LINES, RA_CUSTOMERS, RA_CONTACTS
PO: PO_VENDORS, PO_VENDOR_SITES - For storing vendor data.

162). Name the interface tables used for the LockBox Interface?
Interface table : AR_PAYMENTS_INTERFACE_ALL
Lockbox transfers the receipts that pass validation to the Receivables interim tables AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL
When you run Post QuickCash, the receipt data is transferred from the QuickCash tables to the following Receipt tables:

• AR_CASH_RECEIPTS_ALL
• AR_RECEIVABLES_APPLICATIONS_ALL
• AR_CASH_RECEIPT_HISTORY_ALL

163) Name the interface tables used for the Auto Invoice Interface?
Auto Invoice transfers transaction data from the interface tables

• RA_INTERFACE_LINES_ALL,
• RA_INTERFACE_SALESCREDITS_ALL, and
• RA_INTERFACE_DISTRIBUTIONS_ALL

into the following Receivables tables:

• RA_BATCHES_ALL
• RA_CUSTOMER_TRX _ALL
• RA_CUSTOMER_TRX_LINES _ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• RA_CUST_TRX_LINE_SALESREPS_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL 49
• AR_ADJUSTMENTS_ALL

164). Different Type of Value Sets. What is exactly Translatable independent and Translatable Dependent Value Sets (Introduced in latest version of 11i)?
There are 8 types of Values Sets.
a. None (Non Validate at all) (Validation is Minimal)
b. Independent (Input must exist on previous defined list of values)
c. Dependent (Input is checked against a subset of values based on prior Value)
d. Table (Input is checked against a subset of values in an application table)
e. Special (advanced) (Value set uses a flexfield itself)
f. Pair (advanced) (Two Flexfields together specify a range of valid values)
g. Translatable Independent (Input must exist on previous defined list of values. Translated value can be used)
h. Translatable Dependent. (Input is checked against a subset of values based on a prior value; translated value can be used)

(Note:- When you first define your flexfields, you choose how many segments
You want to use and what order you want them to appear. You also
Choose how you want to validate each of your segments. The decisions
You make affect how you define your value sets and your values.)

165) How to run a concurrent program. What all concurrent programs u have
created.?
Definition :- A concurrent program is an instance of an execution file, along with
parameter definitions and incompatibilities. Concurrent programs use concurrent program executables to locate the correct execution file.
Oracle Tool Concurrent Program * A concurrent program written in
Oracle Reports, PL/SQL package procedures,
SQL*Loader, SQL*Plus, Host Scripting.

How to Run : * Write a execution file and place in correct directory.
Establish executables in Oracle apps specify execution file and method.
Define Concurrent Program (Program, Parameters and Incompatibilities)
Call your Program (- Thu application form, from other concurrent program.
OR through standard request submission, you must check the „USE in SRS check box‟ and register your program parameters when you define your concurrent program. Add your program into the request security group for your custom application.)
I have created reports through concurrent program, load(sql*loader/pl-sql pkg-proc) the file through concurrent program. 

<<<Previous    Continue>>>

 

Read More »

Oracle Apps Interview Questions-10

136. Describe the OPERATOR option of the FND FLEXSQL user exit?
Specify an operator to use in the WHERE clause.

137. Describe the OPERAND1 option of the FND FLEXSQL user exit?
Specify an operand to use in the WHERE clause

138. Describe the OPERAND2 option of the FND FLEXSQL user exit?
Specify a second operand to use with OPERATOR = “BETWEEN”

139. Where is FND FLEXIDVAL user exit used?
 Call this user exit to populate fields for display. You pass the key flex fields data retrieved by the query into this user exit from the formula column. With this exit you can display values, descriptions and prompts by passing appropriate token (any one of VALUE, DECRIPTION<APROMPT or LPROMPT).

140) Name the interface tables used for the customer interface?

• RA_CUSTOMERS_INTERFACE_ALL
• RA_CUSTOMER_BANKS_INT_ALL
• RA_CUST_PAY_METHOD_INT_ALL
• RA_CUSTOMER_PROFILES_INT_ALL
• RA_CONTACT_PHONES_INT_ALL

141) What is the name of the column in CUSTOMER_INTERFACE_TABLE that indicates whether you are inserting new or updating existing information?When importing data into the interface tables, the column INSERT_UPDATE_FLAG indicates whether you are inserting new or updating existing information. This column is required in RA_CUSTOMERS_INTERFACE.

142) If the INSERT_UPDATE_FLAG is not set correctly or the required column is missing the value, will CUSTOMER INTERFACE reject the entire record or just the attributes u want to update?Reject the entire record.

143) List some of the required columns for the RA_CUSTOMERS_INTERFACE?

• ORIG_SYSTEM_CUSTOMER_REF
• INSERT_UPDATE_FLAG
• CUSTOMER_NAME
• CUSTOMER_NUMBER (if you are not using Automatic Customer Numbering)
• CUSTOMER_STATUS
• LAST_UPDATED_BY
• LAST_UPDATE_DATE
• CREATED_BY
• CREATION_DATE

If you are importing an address and a business purpose, you must also populate the following columns:

• PRIMARY_SITE_USE_FLAG (if you are inserting an address)
• LOCATION (if you are not using Automatic Site Numbering)
• SITE_USE_CODE (if you are inserting an address)
• ADDRESS1

144) List some of the production tables that Customer Interface transfers customer data from the interface tables into?

• AR_CUSTOMER_PROFILES
• AR_CUSTOMER_PROFILE_AMOUNTS
• RA_ADDRESSES
• RA_CONTACTS
• RA_CUSTOMERS
• RA_CUSTOMER_RELATIONSHIPS
• RA_CUST_RECEIPT_METHODS
• RA_PHONES
• RA_SITE_USES
• AP_BANK_ACCOUNT_USES
• AP_BANK_ACCOUNTS
• AP_BANK_BRANCHES

145). What validation must be given on the customer_number?
Must be null if you are using Automatic Customer Numbering. Must exist if you are not using Automatic Customer Numbering. This value must be unique within RA_CUSTOMERS.

146) What validation must be given on the CUSTOMER_STATUS?
Must equal ‟A‟ for Active or ‟I‟ for Inactive.

147) Name some of the Oracle receivables Interfaces?

•  Auto Invoice
•  Auto Lockbox
•  Customer Interface 47
•  Sales Tax rate Interface
•  Tax Vendor Extension

148) Give some of the Oracle Payables interface?

• Credit Card Transaction Interface
• Invoice Import Interface
• Payables Open Interface
• Purchase Order Matching

149). Name some of the oracle general ledger Interface?

• Budget Upload
• Importing Journals
• Loading Daily rates

150). What are the names of the parameters u pass to the Procedure which u register in the apps?

retcode in varchar2
errbuf in varchar2  

<<<Previous     Continue>>>

Read More »

Oracle Apps Interview Questions-9

121)Why do we call FND SRWINIT from Before Report Trigger?
FND SRWINIT fetches concurrent request information and sets up the profile options. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

122)Why do we call FND SRWEXIT from After Report Trigger?
FND SRWEXIT frees all the memory allocations done in other Oracle Applications user exits. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

123) Why do we call FND FLEXSQL from the Before Report Trigger?
One need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any description and values from switched value sets as needed. One gets this information by calling the AOL user exit FND FLEXSQL from the before report Trigger. 

124. If u call the user exit FND FLEXSQL with MODE = “ WHERE” from the Before Report Trigger. What will it do?This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of the report query. This user exit is called once for each lexical to be changed.

125. If u call the user exit FND FLEXSQL with MODE = “ ORDER BY” from the Before Report Trigger. What will it do?This user Exit populates the lexical parameter that one specifies with the appropriate SQL fragment at run time. One includes this lexical parameter in the ORDER BY clause of the report query. This user exit is called once for each lexical to be changed.

126. How can we display flexfield segment values, descriptions, and prompts on the report?Create a formula Column. Call the user exit FND FLEXIDVAL as the formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that one does not has to use complicated table joins to the flex field tables.

127. Name some options of the FND FLEXSQL user exit?
CODE, APP_SHORT_NAME, OUTPUT, MODE, DISPLAY, SHOWDEPSEG, NUM or MULTINUM, TABLEALIAS, OPERATOR, OPERAND1, OPERAND2.

128. Describe CODE option of the FND FLEXSQL user exit?
Specify the flex field code for the report (for example, GL#, MCAT).

129. Describe the APP_SHORT_NAME option of the FND FLEXSQL user exit?
Specifies the short name of the application that owns the flex field (for example: SQLGL, INV).

130. Describe the OUTPUT option of the FND FLEXSQL user exit?
Specify the name of the lexical parameter to store the SQl fragment. One uses this lexical later in the report when defining the SQL statement that selects the flexfield values. the datatype of this parameter should be character.

131. Describe the MODE option of the FND FLEXSQL user exit?
Specify the mode to use to generate the SQL fragment .
valid mode are :
SELECT: Retrieves all segments values in an internal (non- displayable format).
WHERE: Restrict the query by specifying constraints on flexfield columns. The fragment returned includes the correct decode statement if one specifies MULTINUM. One must also specify an OPERATOR and OPERANDS.
HAVING: Same calling procedures and functionality as WHERE.
ORDER BY: Order required information by flexfield columns. The fragment Orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement, one specifies in MULTINUM.

132. Describe the DISPLAY option of the FND FLEXSQL user exit?
One uses the DISPLAY token with the MODE token . the DISPLAY parameter allows you to specify segments that represent specified flexfield qualifiers or specified segments numbers ,where the segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form.

Eg. If your MODE is SELECT and you specify DISPLAY = “ALL” then the SELECT statement includes all the segments of the flexfield. . Similarly, if your MODE is WHERE and you specify DISPLAY = “ALL”, then your WHERE clause includes all segments.

133. Describe the SHOWDEPSEG option of the FND FLEXSQL user exit?
SHOWDEPSEG = “N” disables automatic addition of depended upon segments to the order criteria. The default is “Y”. This token is valid only for MODE = “ODER BY” In FLEXSQL.

134. Describe the NUM option of the FND FLEXSQL user exit?
Specify the name or lexical or source column that contains the flexfield structure information. If the flexfield uses just one structure, specify NUM only and use a lexical parameter to hold the value. If the flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. The default value is 101.

135. Describe the TABLE ALIAS option of the FND FLEXSQL user exit?
You use TABLE ALIAS if your SELECT joins to other flexfield tables or uses a self – join. 

<<<Previous     Continue>>>

 

Read More »

Oracle Apps Interview Questions-8

106)Why we use token field for Concurrent program?

The Token is used as for binding purpose. The parameter value is passed to the .rdf/procedure through this token. The input (user) parameter value passes to the report / stored procedure after binding with this token. The concurrent program won‟t get impact even the user parameter names get changed, but got impacted when the token name changed.

107) What are the mandatory parameters in concurrent programs?
errbuf
errcode.

108)Those are IN or OUT parameters?
Out Parameters

109)What is Request group?
Responsibility is used for security reason like which Responsibility can do what type of jobs etc.
Set of Responsibility is attached with a Request group. When we attach the request group to a concurrent program, that can be perform using all the Responsibilities those are attached with Request group.

110)What is MultiOrg? 
It is data security functionality in Oracle 10.6 and above. Applicable User responsibilities are created and attached to specific Operating Unit. User can access the data that belongs to the Operating unit they login under.
The benefit is it enables multiple operating units to use a single installation of various modules, while keeping transaction data separate and secure by operating unit.

It has an effect on the following modules:

• Order Entry
• Receivable
• Payable
• Purchasing
• Project Accounting

111)There is an Object type “Spawned” in concurrent program. What is the use?
It is used for Proc*C in executable field

112)How can we call a Report from Form in Apps?
Thru concurrent prog

113) what are the setup we need to do before running into Oracle Apps?
Go to Oracle Apps > System Administraror (Responsibility) > Create a User using SSO > Add responsibility
Switch to COE Dev Sys Administrator (Responsibility) > Add the Responsibility that is attached to the newly created User > menue > exclude / include the function according to the requirement.

114) What is multi_org?
It is data security functionality in Oracle 10.6 and above. Applicable User responsibilities are created and attached to specific Operating Unit. User can access the data that belongs to the Operating unit they login under.
The benefit is it enables multiple operating units to use a single installation of various modules, while keeping transaction data separate and secure by operating unit.
It has an effect on the following modules:
Order Entry
Receivable
Payable
Purchasing
Project Accounting

115) What is the difference between po_headers & po_headers_all?
Here comes the concept of multi_org. po_headers contains data that is irrespective of multi_org i.e any supplier can view all the records. In case of po_headers_all 

116) What is the basic requirement that we need to set before starting a form in Oracle Apps?
a) FTP the templet.fmb From $au_top/bin To Local m/c
b) Rename the templet.fmb as User defined name
c) Trigger Level Change: Pre_Form : app_window.set_window_position('BLK_ORG', 'FIRST_WINDOW'); -- 1st Window Name
set_window_property('BLK_ORG',title,'Form name') -- 1st Window Name, caption
Block Level Change: Rename Default_block
Form Level Change: Property Panel > Navigation > First Navigation Data Block > (Set A Block Name)
Program Unit Level: App_custom > (set the 1st window name)

118) What is the API we use to see the message from log file?
When we use an API, it automatically stores the error message in a pl/sql table i.e creating a log of the errors.
We can see those error messages thougt fnd_message_pub API

119)There is a check box called Use in SRS. What is its use?
SRS stands for Standard Request Submit. We can execute concurrent program by directly passing parameters. On checking this „Use In SRS‟ check box, we can independently use the concurrent program.

120)Do you know Extension table?
Unlike database table, it store outside the database like BFile (content store outside the DB but the location store in DB like pointer). It contains metadata only. So no DML operation (except SELECT, Group by, Order By) can possible.

<<<Previous      Continue>>>

 

Read More »