A flexfield is a field made up of sub-fields, or segments. A flexfield appears on your form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values. There are two types of flexfields: key flexfields and descriptive flexfields.
BASIC FLEXFIELD CONCEPTS
Segment - A segment is a single sub-field within a flexfield. You define the appearance and meaning of individual segments when customizing a flexfield. A segment is represented in your database as a single table column.
For a key flexfield, a segment usually describes a particular characteristic of the entity identified by the flexfield. For example, you can have a key flexfield that stores part numbers. The key flexfield can contain the part number PAD-YEL-NR-8 1/2x14, which represents a yellow, narrow ruled, 8 1/2" x 14" note pad. Each section in the part number, separated by a hyphen, describes a characteristic of the part. The first segment describes the object, a note pad, the second segment describes the color of the object, yellow, and so on.
Note that we also refer to the fields in a descriptive flexfield pop-up window as segments even though they do not necessarily make up meaningful codes like the segments in key flexfields. However, they do often describe a particular characteristic of the entity identified elsewhere on the form you are using.
Values, Validation and Value Sets - Your end user enters a segment value into a segment while using an application. Generally, the flexfield validates each segment against a set of valid values (a "value set") that are usually predefined. To "validate a segment" means that the flexfield compares the value a user enters in the segment against the values in the value set for that segment.
You can set up your flexfield so that it automatically validates segment values your end user enters against a table of valid values. If your end user enters an invalid segment value, a list of valid values appears automatically so that the user can choose a valid value. You can think of a value set as a "container" for your values. You choose what types of values can fit into your value set: their length, format, and so on.
A segment is usually validated, and usually each segment in a given flexfield uses a different value set. You can assign a single value set to more than one segment, and you can even share value sets among different flexfields. For most value sets, when you enter values into a flexfield segment, you can enter only values that already exist in the value set assigned to the segment.
Structure - A flexfield structure is a specific configuration of segments. If you add or remove segments, or rearrange the order of segments in a flexfield, you get a different structure.
You can define multiple segment structures for the same flexfield (if that flexfield has been built to support more than one structure). Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data. Both key and descriptive flexfields may allow more than one structure.
In some applications, different users may need a different arrangement of the segments in a flexfield (key or descriptive). Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.
Your Oracle General Ledger application, for example, provides different Accounting Flexfield (Chart of Accounts) structures for users of different sets of books. The Oracle General Ledger application determines which flexfield structure to use based on the value of the GL Set of Books Name user profile option.
Key Flexfields - Most organizations use "codes" made up of meaningful segments (intelligent keys) to identify general ledger accounts, part numbers, and other business entities. Each segment of the code can represent a characteristic of the entity. For example, your organization might use the part number PAD-NR-YEL-8 1/2x14" to represent a notepad that is narrow-ruled, yellow, and 8 1/2" by 14". Another organization may identify the same notepad with the part number "PD-8x14-Y-NR". Both of these part numbers are codes whose segments describe a characteristic of the part. Although these codes represent the same part, they each have a different segment structure that is meaningful only to the organization using those codes.
The Oracle Applications store these "codes" in key flexfields. Key flexfields are flexible enough to let any organization use the code scheme they want, without programming.
When your organization initially installs Oracle Applications, you and your organization's implementation team customize the key flexfields to incorporate code segments that are meaningful to your business. You decide what each segment means, what values each segment can have, and what the segment values mean. Your organization can define rules to specify which segment values can be combined to make a valid complete code (also called a combination). You can also define relationships among the segments. The result is that you and your organization can use the codes you want rather than changing your codes to meet Oracle Applications' requirements.
Registration - Register a key flexfield after defining the flexfield combinations table in the database, and after registering your table with the Tables form.
Attention: Do not modify the registration of any key flexfield supplied with Oracle Applications. Doing so can cause serious application errors. To enable an Oracle Applications key flexfield, define and freeze it using the Key Flexfield Segments window.
Attention: Do not attempt to make a copy of an Oracle Applications key flexfield (using the same table, same title, or same flexfield code), since the duplicates may cause errors in forms that call these flexfields.
If you are using segment qualifiers with your flexfield, you should define the QuickCode values for your segment types using the Lookups window. You name your flexfield and associate it with an application and a database table. You also specify which table column you want to use as a unique ID column and which table column you want to use as a structure column.
Find below the Interview questions related to oracle apps technical interview. The Questions are primarily on Sysadmin and AOL (Application Object Library) Responsibilities. The questions are in MCQ style. The answers are written at the end of each question.
1. Which of the following is not associated with a responsibility?
A. Menu
B. Request group
C. Data group
D. Password expiration
Ans:D
2. What do you need to do to allow a different user to see your output file?
A. The user must log on as a system administrator.
B. The user must have the View My Requests form in system administrator mode.
C. The user must be logged on with the same responsibility as the user that generated the output file, and the profile option Concurrent: Report Access Level value must be set to Responsibility.
D. The user must be logged on as the same responsibility as the one that generated the output file and profile option Concurrent: Report Access Level value is User.
Ans:C
3. When will a request group become a request security group?
A. When the request group is assigned to a responsibility
B. When the request group is specified for the Submit Request form
C. When the request group is defined with request sets
D. When the request group is defined with stage functions
Ans:A
4. How can you customize the SRS process?
A. Change the form title
B. Restrict it by request group
C Eliminate the Submit Another Request dialog window
D. All of the above
Ans:D
5. Which is not a component of a request set?
A. Stage
B. Group
C. Request
D. Parameter Ans:B
6. Which of the following operations cannot be performed with the internal concurrent manager?
A. Restart
B. Verify
C. Deactivate
D. Terminate
Ans:A
7. Which one of the following will be the correct outcome with two include rules in a combined rule?
A. Program will be included if the program is in one of the include rules
B. Program will be included if the program is in both of the include rules
C. Program will be included if the program is run by one of the two Oracle IDs in the include rules
D. None of the above
Ans:B
8. When will the number of actual processes be less than the number of target processes?
A. When the concurrent manager is down
B. When the concurrent manager is coming up
C. When there are not enough requests
D. All of the above
Ans:D
9. Which of the following is not a component of parallel concurrent processing?
A. Internal monitor
B. Transaction manager
C. Primary node
D. Secondary node
Ans:B
10. Which of the following is the overriding level for profile option values?
A. Site
B. Application
C. Responsibility
D. User
Ans:D
11. Which of the following is the correct sequence for setting up database change audits? A. Audit installations, audit tables, audit groups, audit trail update tables request
B. Audit installation, audit groups, audit tables, audit trail update tables request
C. Audit trail update tables request, audit installation, audit groups, audit tables
D. Audit groups, audit tables, audit trail update tables request, Audit Installation
Ans:B
12. Which of the following is not a document sequence type?
A. Category
B. Automatic
C. Gapless
D. Manual
Ans:A
13. Which of the following determines the subdirectory for an executable?
A. Application name
B. Execution method
C. Subroutine
D. Execution filename
Ans:B
14. For what is the Token field used?
A. Default value
B. Profile option value
C. Oracle Reports parameter
D. Value set
Ans:C
15. How are output files associated with various printer components?
A. Through the printer type of the selected printer and the selected print style
B. Through the selected print style and the selected printer driver
C. Through the selected printer type and the selected print style
D. Through the printer type of the selected printer and the selected
Ans:A
UTL_FILES:
Qns: What is UTL_FILES?
Qns: What are Exceptions in UTL_FILES
Qns : What do you Mean by File Handler in UTL Files?
Qns : What is the syntax of UTL_FILES?
Qns: Where you Defined Path of UTL_FILE?
INTERFACE DESIGN:
Qns: What is Difference between Inbound Interface and Outbound Interface?
Qns: What is the methodology of Design an Interface?
Qns: What are Validation, Interface table and Concurrent Program for GL_INTERFACE?
Qns: How will you Migrate Vendors?
Qns: How will you Migrate Vendor Site and Contacts?
Qns: How will Load Open Invoices?
Qns : How will You Validate Source During open Invoice conversion?
Qns: How will you Load ON Hand Qty.
Qns: What do you Mean Auto lock box?
Qns: Auto invoice Interface
Qns: How will you Load master Items?
Qns: How will You Migrate Customer Data.?
Qns : Whats is the Difference between Interface and Conversions
Qns : How will you Migrate Reports?
AOL:
Qns: What is Difference between Data Group and Request Groups?
Qns: What is Difference between Request Group and Request Set?
Qns : How will you Register Custom table in Apps?
Qns : What do you Mean by Profile options?
Qns : What do you Mean by Value Sets and Validation Type?
Qns: How will you Make Table type Dependent Value Sets?
Qns : What do you Flex fields?
Qns : What is difference between $FLEX$ and $PROFILES$.
Qns : What is Difference between Key Flex fields and Descriptive Flex field?
Qns : What are WHO Columns?
GL:
Qns : Cycle of GL modules?
Qns : What do you mean by Dynamic Insertions?
Qns : In which Condition You Require More Than One Set Of Books?
Qns : What do you Mean by Flexfield Qualiier?
Qns : After Posting data goes to Which tables?
MULTI ORG:
Qns : We are able to see the data from front end but querying from backend data is not Coming?
Qns: Whats is Hierarchy of MULTI ORG?
Qns : What is difference between ORG_ID and ORGANIZATION_ID
Qns : What is the Difference between AP_INVOICES_ALL and AP_INVOICES
Qns : What is the Importance of MULTI_ORG?
Qns : How will you Design MULTI_ORG Reports?
Qns : In Which Table MULTI_ORG_FLAG Stored:
FORMS:
Qns: How will you Register The Forms?
Qns : How will you generate fmx on UNIX?
ALERTS:
Qns: What is Alerts? What is the business Purpose of Alerts?
Qns : What are Types of Alerts?
Qns : What are the Action Level in Alerts?
Qns : What are Action types in ALERTS?
Qns : How will you Schedule the Alerts?
Qns : How will you Run Periodic Alerts?
Qns : What do you Output Variable and Input Variables in Alerts?
Qns : How will you Design Alerts?
QUESTIONS
---------
1) How many Key Flexfields are there in the HRMS Suite and what are they?
2) Which Key Flexfields are required for setup prior to configuration and use
of the application?
3) Which Descriptive Flexfields are required for setup prior to configuration
and use of the application?
4) What are value sets and are these required?
5) Can you control which values are entered on any segment?
6) Is there anything you have to do after you have completed setup and
configuration of all your flexfields?
7) Should you check 'Allow Dynamic Inserts' on all HRMS flexfields?
8) After you have configured the structure, segments, value sets, etc, what
should you do next to validate the flexfield structure?
9) Entering Assignment form gives error:
APP-FND-00668: The data that defines the flexfield on this field may be inconsistent.
Action: FDFRKS could not find the structure definition for the flexfield specified by
Application = &APPL, Code = GRP and Structure number = 101 (APPID=801)
10) How do you know which descriptive flexfields are available on any form?
11) Can the same flexfield structure have a different set of segments?
12) Which table holds the data for each Key Flexfield structure?
13) Why aren't any of the key or descriptive flexfield windows opening when you click on them?
14) Is there a profile option or functionality to temporarily turn off flexfield validation?
15) You have defined your Job Key Flexfield structure and segments. When you navigate to Assignment form, the Job field has no list of values.
ANSWERS
-------
1) There are 6 Key Flexfields in HRMS application. They are:
A. Job
B. Position
C. Grade
D. People Group
E. Cost Allocation
F. Personal Analysis
2) All Key Flexfields are required for setup.
3) No Descriptive Flexfields are required for setup.
4) Value Sets are a way to define a set of values which can be used to validate
against segments of your flexfields. Value Sets can be shared by different
segments of the same flexfield, or by segments of any other flexfield. You do
not need to use value sets for validation. If no value sets are used, then
users can enter any alphanumeric value up to 150 characters.
5) Yes. You can define cross validation rules in your value sets to control
which combinations of values that can be selected or entered.
6) Yes. After you have completed the definition of a key flexfield, you need
to run the Create Key Flexfield Database Items process concurrent process to
generate Database Items for the individual segments of the Flexfield. This
applies to your Job, Position, Grade and People Group Key Flexfields only. As
with any concurrent process, this is run under the navigation of Process and
Reports > Submit Processes and Reports.
7) Yes. If this is not checked, you will not be able to enter new records in
the respective flexfield windows.
8) You should freeze and compile the flexfield. Check your compilation request
under View > My Requests to ensure the compilation completed without error.
9) This error occurs because at least one segment hasn?t been defined for the
People Group flexfield. You will need to define at least one segment. If you do
not need this 'dummy' segment, you can define the segment as 'Enabled' but
leave 'Displayed' unchecked.
10) Enter into any HR form. From the menu, select Help > Diagnostics > Examine.
For the Block field, click the list of values (LOV) and select value
$DESCRIPTIVE_FLEXFIELD$. Now click on the LOV for the Field field. In this
LOV, you?ll see all descriptive flexfields available on the form. Upon clicking
any value here, this will populate the Value field with the full name of the
descriptive flexfield.
11) Yes. These are context-sensitve segments. These appear only when a
defined context exists. This can manually be where the user selects the
context. Or it can be automatic by referencing another segment and its value.
12) The following are the flexfield/table relationships:
Flexfield: Table:
Job PER_JOB_DEFINITIONS
Position PER_POSITION_DEFINITIONS
Grade PER_GRADE_DEFINITIONS
People Group PAY_PEOPLE_GROUPS
Cost Allocation PAY_COST_ALLOCATION_KEYFLEX
Personal Analysis PER_ANALYSIS_CRITERIA
13) Make sure the following profile options in System Administrator are set to
Yes at the Site level:
Flexfields:Open Descr Window
Flexfields:Open Key Window
14) No. There is no functionality to flag validation one way or another.
15) You need to create the Job flexfield data on the Job form.
Navigation: US HR Manager > Work Structures > Job > Description
After this has been defined, you should now see data from Job field list of
values on Assignment form. The same is applicable for Position and Grade on
their respective forms. Out of all flexfields on Assignment form, the People
Group flexfield you can create your records without having to go to a separate
form.
Below is a list of 25 Important questions that is asked in almost every financials technical interview question. I have also included some of the functional questions that are asked.Answers are included.
Qns: What is Flex field? What are different types of Flex field?
Ans: Flex field is used to capture information of your Organisations.
Qns: Difference between KFF and DFF.
KFF
Unique identifier
Stored in segment Column
DFF
Is used to capture additional information
Stored in attribute Column
Qns: How many KFF are in GL. AP , AR.
Ans:
Module KFF
GL Accounting FF
AP No KFF
AR Sales tax Location FF
Territory Flexfield.
Qns: What is symbol of DFF in the Forms?
Ans: Square Bracket [ ].
Qns: What is structure of KFF in the Accounting Flexfields.
Ans: Company
Cost center
Account
Product
Future use.
Qns: How many segments are in AFF.
Ans: max 30 segments and min two.
Qns: What are flexfield Qualifiers.
Ans: Flexfield Qualifiers is used to identify the segments. Various types of flexfield qualifiers are listed below:
a) Balancing Segment Qualifier.
b) Cost Center segment Qualifier.
c) Natural Account Segment Qualifier.
d) Intercompany Segment Qualifier.
Qns: What is Dynamic Insertions?
Ans: u can create Code Combinations at run time.
Qns: In which table Code Cominations id is stored.
Ans: GL_CODE_COMBINATIONS.
Qns: In which table flex values are stored.
Ans: 1. fnd_ flex_Values
2. fnd_ flex_Values_tl
Qns: What is set of Books and in which table set of book is stored.
Ans : Set of Books is a Financial Reporting entity which Consist of three C.
a) Chart Of Accounts
b) Currency
c) Calendar.
Set of Books is stored in GL_SETS_OF_BOOKS
Qns: In which table Currency and Period Type Name are stored.
Currency - FND_CURRENCIES
Period - GL_PERIOD_STATUSES
Qns: In which table Segment Values are stored and concatenated values are stored.
Ans: 1. GL_CODE_COMBINATIONS
2. GL_CODE_COMBINATIONS_KFV.
Qns: What are different types of Currency.
Ans: Functional Currency
Foreign currency.
Qns: What are different types of Calendars .
Ans: Different types of Calendars are listed below
a) Fiscal
b) Accounting
Qns: How will you attach set of Books to the Responsibility?
Ans: through Profile. GL SETS OF Books Name.
Qns: What is Profile and what are different types of Profiles.
Ans: Profile: Profile is the changeable option that affects the way your application runs. There are two types of profile.
1. System defined
2. User defined
Qns: What are different Profiles Level available in oracle apps.
Ans: Below are the Profiles Level available in oracle apps
1. Site(Lowest level)
2. Application
3. Responsibility
4. User.
Qns: Write Name of some Profile options.
Ans:
1. GL Sets of Books Name
2. GL sets of Books id
3. MO:Operating unit (multi org).
4. HR:User type.
Qns: What is cycle of GL.?
Ans: In simple and layman words-
1. Open the period
2. Create Journal Enteries
3. Post the Journals.
Qns: In Which tables Journal entries created.
Ans: Important tables are-
1. Batch: GL_JE_BATCHES
2. Header: GL_JE_HEADERS
3. Lines : GL_JE_LINES
Qns: After Posting data goes in which tables.
Ans: GL_BALANCES.( Column Period_net_cr, period_net_dr).
Qns: What are Important tables in GL.
Ans:
1. GL_JE_BATCHES
2. GL_JE_HEADERS
3. GL_JE_LINES
4. GL_BALANCES
5. GL_SETS_OF_BOOKS
6. GL_CODE_COMBINATIONS
7. GL_PERIOD_STATUES
8. GL_INTERFACE
Qns: In which table Supplier information’s is stored.
Ans: Supplier information can be found in following tables
1. PO_VENDORS
2. PO_VENDOR_SITES_ALL
3. PO_VENDOR_CONTACTS
Qns: What is difference org_id and Organization_id.
Ans: Org_id is for operating unit and organization_id is for inventory organization.
Please share your views about this Article.For more articles visit About Oracle Apps
Qns1 : What are the important tables in AP (not interface tables):
1. AP_INVOICES_ALL
2. AP_CHECKS_ALL
3. AP_INVOICE_DISRIBUTIONS_ALL
4. AP_BATCES_ALL
5. AP_PAYMENT_DISRIBUTIONS_ALL
Qns2 : What do you mean by _ALL in tables.
Ans : It means data is separated by Operating unit.
Qns3 : What are the important tables in AR (not interface tables):
1. AR_CASH_RECEIPT_ALL
2. RA_CUSTOMERS
3. RA_ADDRESSES_ALL
4. RA_CONTACTS
5. RA_PHONES
Qns4 : What are the important tables in GL (not interface tables):
1. GL_BALANCES
2. GL_JE_LINES
3. GL_JE_HEADERS
4. GL_JE_BATCHES
5. GL_PERIODS
6. GL_SETS_OF_BOOKS
Qns5 : What are the important tables in FA (not interface tables):
1.FA_CATEGORIES
2.FA_MASS_ADDITIONS.
Qns 6: Why SQL Loader is used.
Ans : To transfer data from text file to Temporary table or interface table
Qns 7: How do you design an Interface in Oracle Applications .Explain the various steps in detail.
Step-01
Create a staging table and populate the records received from customer using SQL Loader.
COMMAND LINE: sqlldr userid/user@pass control=CHP_ITEMS.ctl data=CHP_ITEMS.csv
LOAD DATA
INFILE 'C:\CHP_ITEMS.csv'
INSERT
INTO TABLE CHP_MTL_SYSTEM_ITEMS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ITEM_NUMBER
, DESCRIPTION
, TEMPLATE_NAME
, PRIMARY_UNIT_OF_MEASURE
, LIST_PRICE_PER_UNIT
)
Step-02
After that I wrote cursor script to load from staging table to interface table.
Step-03
Assuming that I am designing Customer interface. After that I run standard Customer Interface Program to load the data into transaction table. Customer Interface Program validate the data and transfer into Transaction table in apps and customization to standard programs is not recommended or supported by Oracle.
Qns 8: Tell me any one Validation used in customer interface:
INSERT_UPDATE_FLAG
Enter a value to indicate whether you are inserting a new record or updating an existing record.’I’ for insert, ’U’ for update
Qns9 : What are various interface tables in AR.
1. RA_CUSTOMERS_INTERFACE
2. RA_CONTACT_PHONES_INTERFACE
3. RA_CUST_PAY_METHOD_INTERFACE
4. RA_CUSTOMER_BANKS_INTERFACE
5. RA_CUSTOMER_PROFILES_INTERFACE
Qns10 : What are various base table Updated by Customer Interface:
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
Additional Notes-
To import a customer with multiple telephone numbers you need to enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF values, but different ORIG_SYSTEM_TELEPHONE_REF values.
To import an address with multiple business purposes you need to enter multiple records into RA_CUSTOMERS_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF and
ORIG_SYSTEM_ADDRESS_REF values, but different SITE_USE_CODES values.
To import an address with multiple contacts you need to enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF and
ORIG_SYSTEM_ADDRESS_REF values, but different ORIG_SYSTEM_CONTACT_REF values.
Please share your views about this Article.For more articles visit About Oracle Apps
Oracle Receivables is not an easy module to set-up. To crack the interview is bit more tedious.Below are some of the Functional questions pertaining to Oracle AR module. Just go through them and brush up your skills.
1. How many Address’s can have one Customer?
Primary Address, Bill – To – Address, Ship – To – Address
2. Customer Number Always Automatic / Manual?
Any thing Either Manual or Automatic
3. What are the Mandatory Address’s you should create a Customer for Communicate him?
Remit – To – Address
4. Can U Merge the Customers? If How?
Using the Merging Customer Window
5. What is Accounting Rules?
It is For Generating Revenue Recognition Programs like Monthly, Quarterly
6. What is Invoicing Rules?
The Invoicing Rules Helps you when you receive data from Outside systems like Auto Invoices how the data should insert and It contains 2 rules Advance Invoice, Arrears Invoice.
7. Where should the customers send the payments of Invoices?
Remittance Banks or Vendor Places
8. What is the Transaction Type?
It describes us Whether you want transfer the Receivables data into General Ledger or not. And also when ever you create an invoice what are the accounts should be effected and also the sign of Transaction also.
9. What is a Transaction Source?
It is For Invoice or Invoice Batch numbers whether automatically or manually
10. How many Transactions we have?
Six, Credit Transactions: Invoice: Debit Memo: Charge back: Guarantee: Deposit
11. How can i reduce the Invoice amount?
Using with Credit Transactions
12. What are the Accounts to be use in Transaction Types (Few)?
Revenue, Receivables, Freight, Tax, Clearing, Unearned, Unbilled
13. How can i Assign a Deposit amount to an Invoice?
In the Invoice Window “Commitment” Region
14. What is the Guarantee?
It is agreement between both sides for goods or services in the future , specific range of periods
15. Give the Navigation for Credit Transactions?
Transactions/Credit Transactions
16. How many ways you can apply the Receipt Amount?
Application: Mass Apply
17. How will you know a Customer Balance Amount?
Using with the Customer Account Overview window
18. Can U Define Customer Agreements using with AR?
No, In the Oracle Order Entry Module
19. What are Aging Buckets?
It is for Outstanding Reports purpose the no of days in various ranges
20. How will U View the Outstanding Balance of a Customer?
Generating the Aging Buckets Report
COMMON QUESTIONS OF PL/SQL & CMM
1. What are the features of OOPS used in PL/SQL ?
Ans: Inheritance – Reusability
Abstract Datatype
Method Overloading
2. What are the built in packages available in PL/SQL ?
Ans: DBMS_OUTPUT, DBMS_SQL, DBMS_STANDARDS, DBMS_UTL, DBMS_LOCK, DBMS_LOB, DBMS_JAVA, UTL_FILE,
3. Diff. In Function and Procedures?
Ans: 1. Function must return a value where as procedure does not.
2. Function can be used in SQL statement where as Procedure does not.
4. What is the structure of the PL/SQL block?
Ans: <> (Optional)
Variable & Cursor Declaration
<>
Executable Statements
<> (Optional)
Exception handling
End;
5. Can we use label for anonymous PL/SQL block?
Ans: Yes, use it in “<>” structure
6. What are the exceptions in PL/SQL Block?
Ans: CURSOR_ALREADY_OPEN, DUP_VAL_ON_INDEX, INVALID_CURSOR, INVALID_NUMBER, NO_DATA_FOUND,
PROGRAM_ERROR, VALUE_ERROR, ZERO_DIVIDE
7. What are the types of Variable binding?
Ans: Two types of binding
1. Early Binding (at compile time)
2. Late Binding (Runtime)
8. What are the PL/SQL table and nested table ?
Ans:
9. What are the Scalar type of variables?
Ans: Number, Char, Varchar2, rowed, urowid, raw, long, longraw, blob, clob, bfile, Nchar, nvarchar2, date
10. What are the user defined data types ?
Ans: 1. Structured Types
• Object Types
2. Collection Types
• Varray
• Nested Tables
3. REFS (To object Types)
11. How can we use label in PL/SQL block?
Ans: We can declare a label in “<>” format & can pass control to it using “GOTO” statement.
12. How can we know that proc has passed a value ?
Ans: We can check it using “IN” or “OUT” or “INOUT” parameter.
13. What is the difference between REF Cursor & PL/SQL Table.
Ans: REF Cursor is like Pointer whereas PL/SQL Table is like ARRAY.
REF Cursor can pass to a procedure/function as a parameter directly whereas in PL/SQL table one record has to be passed each time.
14.
15.
=============================================================CMM ACTIVITIES FOR INTERVIEW============================================================= 1. Your contributation to CMM? In which activities u r involved? DS, FS, Defect Preventation, Test Cases, Replying IR 2. How many KPA's are there in each level?? & Describe them? Describe in detail any one KPA 3. Software Configuration Management (SCM) Activities? How to do it? PVCS & Version control activities 4. Diff b/w Level 3 - 4 & 5 5. Various groups like SEPG, DP, SQA, BPAD & their interaction in Project Development Life Cycle 6. What documents are referred in coding ?? DS, FS, CODING STANDARDS, CHECK LIST
KISHOR PATIL INTERVIEW
1. tell me some thing about u!2. how old r u?3. Y r u looking for change?4. Write a query to delete Duplicate rows. (Write using ROWID)
5. Where do u decalare Global Variable in Package? Ans: Package Specification
6. Can u create procedure or function without declaring it in Package specs? Ans: YES, It is called private procedure.
7. what is private function and public functions in package? Ans: If the function is declared in Package Specification then it is called Public Function. Public function can be called outside of Package. If the function is not declared in Package Specification then it is called Private Function. Private function can not be called outside of Package.
8. how do u call private functions in package?Ans: pack spcs p1... func f1(); -- Public function func f2(); -- Public function end;
pack body p1... func f1(){}; -- public func f2(){}; -- public func f3(){}; -- Private func f4(){}; -- Private end; to call private call it in public function and public fun can be called from outside.
9. how do u create tab in forms 4.5 where as in 4.5 there is no tab control provided i? Ans: Will create buttons & programatically make it like Tab Page
10. create a syquence, open a new session and execute first statement as select sequence.currval from dual; what will happene? Ans: It will give an error. First time we have to fire next val & then only we can use currval. 11. I have t1 table in scott .. and same i have scott1 schema with same name... i grant select on scott1.t1 to scott, now i create a synonym for scott1.t1, what happenes when it is created. will it give runtime error or error while creating synonym?Ans: This will give an error, Same name can not be used
12. How many types of Triggers... (24)
13. what do u mean by mutating table? what is work around for the same..?
14. tell me diff between .. 7.x, 8, 8i, 9i ( undo tablespace) Ans: 9i New Features-------------- 1. Rollback Segment is being replaced by Undo Tablespace. (We can use either) 2. Flashback Query - It is used to retrack wrongly committed transaction 3. Online Table Creation (e.g. Create table as select * from ....... will generate updated table) 4. List Partition - Table can be partitioned on a list 5. Buffer catche size is now dynamic (upto 4 different sizes can be specified for buffers)
15. tell me diff between .. forms 3, 4.5, 5, 6i
16. what is view? Ans: View is a virtual table (or logical container of data), which does not physically store data.
17. Why instade of trigger is created? Ans: To Insert/Update/Delete record from base table for any view operation
18. Are views updatable?Ans: Yes (Only if the view is based on one table, but it is NOT if more than one tables)
19. what u know about oracle arch? physical and logical arch!
20. types of tuning?Ans: Application Tuning, Database Tuning, Memory Tuning, O/S Tuning
=========================================================================RAMESH PADHIYAR INTERVIEW WITH MBT=========================================================================
1. what is difference is view & synonyms
2. Cursor/PLSQLtable/REF cursor [Def., Syntax, How to use all.]
3. Difference of procedure & packagesAns:
1. Various Master detail relationshipAns:
2.locking mode property of block(Immediate/Automatic/Delayed)
Automatic (default):- Identical to Immediate if the datasource is anOracle database. For other datasources, Form Builder determines theavailable locking facilities and behaves as much like Immediate aspossible.
Immediate:- Form Builder locks the corresponding row as soon as theend user presses a key to enter or edit the value in a text item.
Delayed:- Form Builder locks the row only while it posts thetransaction to the database, not while the end user is editing therecord. Form Builder prevents the commit action from processing ifvalues of the fields in the block have changed when the user causes acommit action.
3.trigger execution
form level
block level
item level
4.mouse triggers
When-Custom-Item-Event
When-Mouse-Click
When-Mouse-DoubleClick
When-Mouse-Down
When-Mouse-Enter
When-Mouse-Leave
When-Mouse-Move
When-Mouse-Up
5.What is visual property?
6.what is LOV?
ans: lov is List of values. It can be query based or static values.to restrict users with fixed set of value.
7.Diff. b'ween open_form,call_form,new_form?
Open form: It will allow user to navigate between called form andcalling form.
call_form: It will not allow user to navigate on calling form.
New form: It will exit the called form and control goes to new form.
8.
-- Current project/role & responsibility
-- CMM LEVELS AND IT'S IMPLEMENTATIONS
1. cURRENT PROJECT :FUNCTIONALITY
2. ORACLE ARCHITECTURE.
3. DBA - PERFORMANCE RELATED
4. PL/SQL.
5. SQL
6. FORMS
1. CURRENT PRJECT
- FUNCTIONALITY
- ROLE
- RESPONSIBILITY
2. ORACLE ARCHITECTURE
- PROCESSES & WORKING OF EACH PROCESS
- MEMORY STRUCTURE
3. DBA - PERFORMANCE RELATED
- HOW TO TUNE A SYSTEM?
INDEXING, EXPLAIN PLAN, APPLICATION TUNING, O/S TUNING
-
4. PL/SQL
- REF CURSOR (IN DETAIL WITH USAGE)
- DIFF B/W REF CURSOR & PL/SQL TABLE
- VIEWS (IN DETAIL)
- DIFF B/W VIEW & MATERIALIZED VIEW
- PSUEDO COLUMN (Def.)
5. SQL
- SCENARIO BASED (TABLE STRUCTURE GIVEN & QUERY ISDEVELOPED ON THAT BASIS)
- SUM/AVERAGE HOW TO COUNT??
6. FORMS
- MOUSE TRIGGERS & OTHER TRIGGERS WITH THEIR SEQUENCES
- HOW TO CREATE DYNAMIC LOV
RECORD GROUP CAN BE DYNAMICALLY CHANGED/CREATED BUT LOV CAN NOT BE
CREATED DYNAMICALLY
- WHICH TRIGGERS ARE RESTRICTED IN WEB BASED
- FORMS ARCHITECTURE
1. In the Price type quickpick in the Purchase Order Lines zone in enter
Purchase Order form, there is a value called COST PLUS FEE. What is the
COST PLUS FEE price type? How is Purchase Order treating this
price type?
Answer
------
The functionality for COST PLUS FEE price type is not in Oracle Purchasing at
this point of time. PO does not do anything with this price type.
It is an enhancement request that is being looked at for future releases.
2. What is 2-way, 3-way, 4-way matching? How is this set-up?
Answer
------
2-way matching verifies that Purchase order and invoice information match within your tolerances as follows:
Quantity billed <= Quantity Ordered Invoice price <= Purchase order price (<= sign is used because of tolerances) 3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined: Quantity billed <= Quantity received 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined: Quantity billed <= Quantity accepted. (Acceptance is done at the time of Inspecting goods). Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character) Receipt required Inspection required Matching Yes Yes 4-way Yes No 3-way No No 2-way In GUI, Shipments block, alternative region, click on More...this brings up a region with 'Invoice matching' column where you can choose 2-way, 3-way or 4- way match. You can find more detailed information about matching in the Oracle Payables Reference Manual (Volume 3) Topical Essay on Integrating your Payables and Purchasing Information. 3. How do you reflect discount from a supplier on a Purchase orders? Answer ------ The legal document is the Purchase order not the quote and therefore the purchase order should reflect agreed upon price. Secondly if the discount comes after the PO then AP should handle it when invoicing. If you are using a blanket Purchase order then you can use price breaks. 4. How do you change the created date on a Purchase Order? Answer ------ The 'Created' date on the Purchase order is not an updateable field, due to it being a legal document once approved. 5. What is the sorting order for requisition lines in autocreation of a Purchase Order? Answer ------ Requisition lines first meet search criteria determined by "find" screen, and then are grouped together by item and item category, then by need-by date. After you choose an action, enter any search criteria to narrow the selection of requisition lines. Oracle Purchasing chooses requisition lines from the requisition pool that meet your search criteria. All requisition lines for the same item and item category are grouped together, but they are not necessarily in order alphabetically. Within each grouping of item and item category, requisition lines are ordered by need-by date. 6. What does the error 'APP-50022: Oracle Human Resources could not retrieve a value for User Type profile option' mean? Answer ------ The profile HR: User Type needs to be set. Set the profile option 'HR: User Type' at the Responsibility level. 7. In the Enter Receipts (RCVRCERC) form and using different responsibilities produces different values in the action required field. The Purchasing Responsibility shows Direct and Standard while the Inventory Responsibility shows just Direct. Why are the action options for creating receipts different in Inventory compared Purchasing? Answer ------ Set the Profile option RCV: Allow routing override to 'YES' at the application level. 8. How do you purge Cancelled Requisitions? Answer ------ To purge any Purchase orders or Requisitions: - assign the Payables Purge GUI responsibility to the user - have the user choose that responsibility - then choose Purge from the menu - under the Category field do a List of Values and pick Simple Requisitions for cancelled requisitions The choices are listed. 9. On the requisition form in the source details zone, there is a field labeled supplier item. Where does this information on the quickpick come from and how can this information default in? Answer ------ The supplier item field on the requisition can be populated by using either the supplier item catalog or ASL. Take an item from the supplier item catalog that has an associated supplier item, add it to the order pad and then add it to the requisition. After doing this go to the source details zone. The supplier item is defaulted in for that item you choose from the catalog. Navigation: --> Purchasing --> Supplier Item catalog
10. You are entering a requisition and are unable to find the header or line
note fields. Was this replaced in GUI by attachments?
Answer
------
The attachments have replaced notes. The attachments are better because at the
header level, the requisition notes were not coming over to the Purchase Order
whereas the attachment does come over to the Purchase Order.
11. When you select the Notifications form to review approvals and click on
approve button for a Requisition waiting for approval, the 'Reject' box is
greyed out. How does the approver reject a requisition?
Answer
------
You cannot reject Requisitions submitted to yourself. You can only reject those
from others.
12. What are standard Quotations used for?
Answer
------
Standard quote is one that you can tie back to a PO. For example, navigate to
RFQ -> Auto create -> enter a PO and reference it back.
13. When do you see the status of a Purchase order as Archived?
Answer
------
You must approve or print a specific purchase order before you can see it in
the history information.
14. Where are standard notes in GUI?
Answer
------
Go to Setup --> Attachments -->Create attachment. Go to Purchase Order and
attach the attachment to the Purchase Order. In GUI, notes are replaced by
attachments.
15. In Oracle Purchasing, where is the automatic numbering for Purchase Order
defined and maintained?
Answer
------
The navigation path for GUI is:
Setup --> Organizations --> Purchasing Options, Numbering alternate region
The navigation path for Character is:
\ Navigate Setup Purchasing Options Purchasing, Numbering options
16. There is a field for the organization code. What is this code?
Answer
------
This code is a short abbreviation for the organization.
17. Can the original Purchase Order can be viewed in any way without resorting
to SQL, for a revised Purchase Order?
Answer
------
The original version of a revised PO cannot be viewed from the PO form or PO
summary form. Information on the original PO can be obtained from the
PO_HEADERS_ARCHIVE and PO_LINES_ARCHIVE tables using the PO_HEADER_ID column as
a common reference using SQL only.
2.
3. 1. How do you create a Chargeback Invoice ?
A. Using Receipts window
2. How do you adjust the amount of advance already received against an Invoice ?
A. Create an Invoice for Deposit and receive an amount against this deposit Invoice. Then Create the regular invoice and in the column of Commitments enter the number of Deposit Invoice. The Regular Invoice gets matched with the Deposit Invoice for the amount of Regular invoice or deposit invoice which ever is lower
3. How do you write off small amounts while accounting the receipt against an invoice?
A. Using Receipts window. You have the button for write offs.
4. How do you Account for bank charges deducted from amount received against an invoice?
A. Using Quick cash window also you can enter receipts. Here you have the option of accounting the bank charges deducted on receipt. However, this has to be enabled by putting the Value in profile option AR: Create Bank Charges = YES.
5. How do you create a credit note against an invoice ?
A. You have separate window to create such credit note. Navigation Transactions => Credit Transactions.
6. How do you adjust a regular Invoice with a Credit Note ? Enter the amount 0 in receipt window and in invoice matching window select the invoice as well as the credit note. This will knock off the invoice against the credit note.
7. What are different types of Receipt Reversals ? What is the difference between them ? What are the accounting entries ?
A. Standard Reversal and Debit Note Reversal. Standard Reversal reopens the invoice matched in that receipt. You can match a new receipt against this invoice.
Debit Note Reversal does not reopens the earlier matched invoice but it creates a new debit note which can be matched with another receipt.
8. When are the following accounts are used
Unbilled Receivable
Unearned Revenue
A. When you use Invoicing Rule, the receivables are accounted on different dates as defined in the rule. Till such time receivables are accounted, the amount is debited to Unbilled Receivables.
If you use Accounting Rule, the revenue is accounted on different dates. Till such time revenue is accounted, the amount is credited to Unearned Revenue..
9. What is the difference between Unidentified Receipt, Unapplied Receipts and On Account Receipts ? What are the Accounting Entries for each of this ?
A. Unidentified Receipts: The Customer is yet to be identified and so receipt is not matched.
Unapplied Receipts: The Customer is identified and entered but the amount is not matched with any of his invoice.
On Account Receipts: The Customer is entered and instead of matching the amount to any of his invoice it is matched with the On Account option. This option is available as first item in the pick list of invoices.
10. What is the difference between earned discounts and Unearned discounts ? How do you account the Unearned discount? What are the accounting entries involved ?
A. The cash discounts are mentioned in the payment terms. Considering the due date and the receipt date the discount is automatically calculated while entering the receipt. If however you want to increase the amount of invoice you can manually increase it. The amount automatically calculated as per terms is the Earned Discount. The manually added amount is the Unearned Discount. This however, depends upon the option given in System Options in Setup.
11. What are the Key Flexfields in AR.
A. Sales Tax Location Flexfield
Territory Flexfield
AR Setup Related
12. What is difference between transaction type and transaction source ?
A. Transaction type can either be Invoice, Credit Note, Debit Note, Deposit. Etc. This also decides whether to account in GL, Whether to consider in receivables, the accounts to be debited, credited, the tax calculation options, and some other options which are defauled.
Transaction Source decides whether the source of entry is manual or automatic. It also mentions whether transaction and batch numbering is manual or automatic.
If automatic the last entered number is to be mentioned to start the automatic numbering. IF the source is automatic, that means the transactions are to be uploaded through interface either from OE or legacy system. Then some other options are to be set.
13. What is AutoAccounting ?
A. This is the account generator in Accounts receivables. This decides accounts for
Receivables
Revenue
AutoInvoice Clearing
Freight
Tax
Unbilled Receivable
Unearned Revenue
From where the accounts are defaulted ?
Sales reps
Transaction Lines
Transaction Types
Taxes
Or you can have Constant values
14. What is AutoCashRule Set ?
This decides the sequence of the invoice matching rules for unmatched receipts entered though quich cash or AutoLockBox(interface). The rules are already defined in the system. Like “Match Payment with Invoice”, “Apply to the Oldest Invoice First”, “Clear the Account”, etc.
15. Where do you attach the Set Of Books ? Can you attach more than one set of books ? if you have more then one set of books then how Receivables is configured ?
You attach the set of books in System Options in Setup. Only one set of books can be attached. If you have more then one set of books then AR is to be set in multi org environment. By specifying the “MO Operating Unit” and the “GL Set of Books Name”.
16. How do you define document numbering for receipts ? can you have different sequences for each Payment Method.
In system administration. You can have different sequences for different payment methods.
4.
5. ) How much do u rate urself in PL/SQL?
3) What is autonoumous transaction?
4) What are Indexes and types of Indexes?
Ans Simple Index, Binary Index and Function Index.
5) What is difference between Primary Key and Unique Key?
Ans Unique key can be null but primary key cannot be null
6) What is Data Dictionary?
Ans Its the meta data about the objects of the database, its the data about data.
7) How do u eleminate duplicate records from the table? (Concept)
Ans Delete from tablename a where rowid>(select min (rowid) from tablename b where a.colname=b.colname);
8)What are the attributes of a cursor?
Ans %isopen, %found, %notfound, %count.
9) What is difference between Delete and Truncate?
Ans Delete keeps the rollback info but truncate doesn't. So rollback the delete statement but truncate cannot be rollbacked.
10) What are the different types of Exceptions?
Ans User Defined exceptions and predefined exceptions
User Defined exceptions are defined explecitly by the user in the declare section and called in the begin section
and the code is written exception section.
Eg]-
Declare
...
myexception exception;
Begin
...
raise myexception;
Exception
when myexception then
PL/SQL code..
End
Predefined exceptions are oracle defined exceptions like no data found, too many rows, invalid cursor, value_error, dup_val on index
11) What is a transaction? How does it end?
Transaction is a series of SQL statements which can be succeeded and failed as a unit.
It ends with commit or rollback.
12) What is DBMS packages? How many of them have u used?
DBMS packages are used
13) What are post functions?
14) What are the different tyes of canvases?
Ans Content, Tab, Stacked, Horizontal toolbar, Vertical toolbar
15) Difference between pre querry and post querry?
Ans pre querry fires before the querry is executed and post querry is fired after the querry is executed
16) What are the different SRW packages?
Ans SRW.Reference, SRW.Userexit, SRW.Runreport, SRW.SetAttr, SRW.Getpagenum,SRW.Message.....
17) How do u define SRW.message?
Ans SRW.message(msg no, msg string);
18) What is an anchor? What are the different types of anchor?
Ans Anchor is an object which is used to attach two different objects like frames to keep its relative position with respect to each other. Types of anchor are horizontal and vertical.
19) What are the types of Master Detail relation? What is isolated MD relation?
Ans Isolated, Non Isolated and Cascade.
Isolated- Master record can be deleted without deleting the detail record.
Non Isolated- Master record cannot be deleted without deleting the detail record.
Cascade- When the master record is deleted the detail record is deleted automatically.
20) What is object group?
Ans Object group is a container for a grooup of objects like canavas, block and window they can be grouped to create an object group
21) What are various block coordination properties?
Ans Immediate, Deffered with autoquerry, Deffered with No-autoquerry
Immediate- When u querry the master record the detail will be automatically querried
Deffered with autoquerry- Unless and Untill u navigate to the detail block it will not be querried.
Deffered with No-autoquerry- U have to explicitlly querry both master and detail block.
22) How to open form from form?
Ans It can be opened from
Call- Existing form is kept in background.
Open- It opens a new form and u can navigate between them.
New- It replaces the orginal form.
23) What are different item level triggers?
Ans pre-text-item, when-new-item-instance, key-next-item, when-validate-item, post-text-item.
24) What is flex mode?
Ans When the flex mode is on the objects can be moved outside its parent frames( across frames)
25) What is the difference between key-next-item and post-item?
26)How do u used DDL in forms?
Ans Forms_DDL, Exec_SQL
Forms_DDL:- Only database the database u are connected to
Exec_SQL:- Another database, and u can also call non-oracle procedure.
27)What is are record groups?
Ans Its a internal oracle forms datastructure, which can be used in LOV's to populate the querries at runtime.
Types:-
Querry record group:- Querried from the database
Non querry record group:- its created and changed programmatically
Static querry record group:- Harcoded values
28) What is property class and visual attributes?Whats the difference?
Ans Property class is a named object that consist of a list of properties and their setting, it cannnot be changed programatically.
Visual Attributes are properties like fonts, colours, patterns for forms and menu object, it can be changed programatically
29) What are different report triggers and give the order of execution?
Before Parameter trigger
After Parameter trigger
Before report trigger
Between Pages trigger
After report trigger
30) Different types of reports?
Form like
Tabular
Group above
Group left
Maliling lablel
Matrix
Form letter
Matrix with group
31) What is matrix report?
Ans Its a cross tab report.
It contains four groups they are:-
Cross product
Column
Row
Cell value
32) How to call report form a form?
Run_product
33) Different types of parameters in report?
User Parameter and System parameter
System paramters:-
Destype
Desname
Desformat
Decimal
Copies
Currency
Background
Mode
Orientation
Printjob
Thousands
34) What is lexical parameter, bind parameter and user exit?
Ans
35) How do u define paramteres in Concurrent Request?
Ans
36) What are profiles? What is its order?
Ans Its a set of changable options which define a behavior of ur application
Its order in sense of Generic is
Site, Application,Resposibility and User
37) What are different value sets?
Ans Independent, Dependent, Pair, None, Translate Independent, Translate Dependent, Special, Table
38) What is custom.pll? What does ZOOM do?
Ans Its a library used in forms which can be used for customization purpose. It contains the procedure
Style, Event and Zoom
Zoom is used to call another form from a form.
39) What is DFF and KFF?
nice post.
ReplyDeleteoracle sql plsql training